Oracle APPS R12 Creditor Ageing SQL query

Oracle APPS R12 Creditor Ageing SQL query


select  v.vendor_id,hp.party_name,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND  i.invoice_id  NOT IN  (62575)
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
--AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
--AND     i.payment_status_flag in ('N','P')
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-30) and to_date(trunc(sysdate))) ONE,
-----------------------ONE---------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-45) and to_date(trunc(sysdate-31))) TWO,
------------------------------------------TWO-------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND  i.invoice_id  NOT IN  (62575)
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-60) and to_date(trunc(sysdate-46))) THREE,
------------------------------------------------THREE'-------------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
--AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
--AND     i.payment_status_flag in ('N','P')
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-90) and to_date(trunc(sysdate-61))) FOUR,
--------------------------------------FOUR-------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-120) and to_date(trunc(sysdate-91)))  FIVE,
---------------------------FIVE---------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-180) and to_date(trunc(sysdate-121))) SIX,
------------------------SIX----------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-365) and to_date(trunc(sysdate-181))) SEVEN,
------------SEVEV----------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
--and i.vendor_id=1231
--AND     ((to_date(to_char(SYSDATE,'DD-MON-RR'),'DD-MON-RR') - decode(:P_AGING_DATE,'D',ps.due_date,'I',I.INVOICE_DATE)) --pks
          --    between :C_MINDAYS and  :C_MAXDAYS)
--AND     i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
--AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
--AND     i.payment_status_flag in ('N','P')
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) < to_date(trunc(sysdate)-366))  EIGHT
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
group by
v.vendor_id,hp.party_name,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE