Monday 22 January 2018

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

4 comments:

arif said...

Thanks and Regards. Oracle Apps R12 & Fusion Training Videos at affordable cost.
please check oracleappstechnical.com for details.

shaik shah said...

Thanks for sharing this informative article ,It would be helpful to all
Oracle Fusion HCM Online Training

Anonymous said...

Thanks for Sharing This informative article. It would be helpful if you write a all.
Oracle Fusion HCM Online Training

shaik shah said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Oracle Fusion HCM Online Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *