CCR_PAYMANT_QUERY

SELECT VENDOR_TYPE_LOOKUP_CODE
,       ORG_ID
,       VENDOR_NUM
,       VENDOR_NAME
,       VENDOR_SITE_CODE
,       CODE_COMBINATION
,       NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'CREDIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'DEBIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'STANDARD',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT))),0)+
        NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',(INVOICE_AMOUNT+(PAID_AMOUNT+ADJUSTED_AMOUNT)))),0) "AMOUNT"
,       NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'CREDIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'DEBIT',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT),'STANDARD',INVOICE_AMOUNT+(PAID_AMOUNT+ADUSTED_AMOUNT))),0) "LIABILITY"
,       NVL(SUM(DECODE(INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',(INVOICE_AMOUNT+(PAID_AMOUNT+ADJUSTED_AMOUNT)))),0) "PREPAYMENT"
FROM
(SELECT DISTINCT AI.VENDOR_ID
,       APS.VENDOR_NAME
,       APS.SEGMENT1 "VENDOR_NUM"
,       APS.VENDOR_TYPE_LOOKUP_CODE
,       APSS1.VENDOR_SITE_CODE
,       AI.ORG_ID
,       AI.INVOICE_ID
,       AI.INVOICE_NUM
,       AI.INVOICE_DATE
,       AI.INVOICE_TYPE_LOOKUP_CODE
,       ROUND(DECODE(AI.INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',AI.INVOICE_AMOUNT*NVL(AI.EXCHANGE_RATE,1),AI.INVOICE_AMOUNT*NVL(AI.EXCHANGE_RATE,1)*-1),2) INVOICE_AMOUNT
,       AI.PAYMENT_STATUS_FLAG
,       NVL((SELECT ROUND(SUM(NVL(AIP.AMOUNT,0)*NVL(AI.EXCHANGE_RATE,1)),2) AMOUNT_PAID FROM APPS.AP_INVOICE_PAYMENTS_ALL AIP WHERE AIP.INVOICE_ID=AI.INVOICE_ID AND TRUNC(AIP.ACCOUNTING_DATE) <= :P89_DATE
        AND AI.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' ),0) PAID_AMOUNT
,       NVL(CC.CODE_COMBINATION,(SELECT GK.CONCATENATED_SEGMENTS  FROM APPS.GL_CODE_COMBINATIONS_KFV GK                        WHERE GK.CODE_COMBINATION_ID=AI.ACCTS_PAY_CODE_COMBINATION_ID)) CODE_COMBINATION
,          ROUND((SELECT  NVL(SUM(AMOUNT)*-1,0) FROM   APPS.AP_INVOICE_LINES_ALL AIA WHERE AIA.INVOICE_ID=AI.INVOICE_ID
                AND AIA.ORG_ID=AI.ORG_ID AND  AIA.LINE_TYPE_LOOKUP_CODE='PREPAY' AND TRUNC(AIA.ACCOUNTING_DATE)<= :P89_DATE)*NVL(AI.EXCHANGE_RATE,1),2) ADUSTED_AMOUNT
,        ROUND((SELECT NVL(SUM(AMOUNT),0) FROM   APPS.AP_INVOICE_LINES_ALL AIM WHERE AIM.PREPAY_INVOICE_ID=AI.INVOICE_ID
                AND AIM.ORG_ID=AI.ORG_ID AND TRUNC(AIM.ACCOUNTING_DATE)<= :P89_DATE)*NVL(AI.EXCHANGE_RATE,1),2) ADJUSTED_AMOUNT
FROM APPS.AP_INVOICES_ALL AI
,     APPS.AP_SUPPLIERS APS
,     APPS.AP_SUPPLIER_SITES_ALL APSS1
,     APPS.AP_INVOICE_DISTRIBUTIONS_ALL AD
,     APPS.AP_INVOICE_LINES_ALL AIL
,     (SELECT DISTINCT XD.APPLIED_TO_SOURCE_ID_NUM_1,DECODE(XAL.ACCOUNTING_CLASS_CODE,'PREPAID_EXPENSE','PREPAYMENT',XAL.ACCOUNTING_CLASS_CODE) "CODE",GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6||'.'||GCC.SEGMENT7 CODE_COMBINATION
                  FROM APPS.XLA_AE_LINES XAL
               ,     APPS.XLA_DISTRIBUTION_LINKS XD
               ,     APPS.GL_CODE_COMBINATIONS GCC
               WHERE XD.AE_HEADER_ID=XAL.AE_HEADER_ID
               AND XD.AE_LINE_NUM=XAL.AE_LINE_NUM
               AND XAL.ACCOUNTING_CLASS_CODE IN('LIABILITY','PREPAID_EXPENSE')
               AND XAL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID)CC
WHERE AI.INVOICE_ID=AD.INVOICE_ID
AND AD.MATCH_STATUS_FLAG='A'
AND AI.VENDOR_ID=APS.VENDOR_ID
AND AI.VENDOR_ID=APSS1.VENDOR_ID
AND AI.VENDOR_SITE_ID=APSS1.VENDOR_SITE_ID
AND AI.ORG_ID=APSS1.ORG_ID
AND AI.INVOICE_ID=CC.APPLIED_TO_SOURCE_ID_NUM_1(+)
AND AI.INVOICE_ID=AIL.INVOICE_ID
AND AI.ORG_ID=AIL.ORG_ID
AND DECODE(AI.INVOICE_TYPE_LOOKUP_CODE,'PREPAYMENT',AI.INVOICE_TYPE_LOOKUP_CODE,'LIABILITY')=CC.CODE(+)
AND AD.LINE_TYPE_LOOKUP_CODE <> 'AWT'
AND AI.ORG_ID= :P89_ORG_ID
and NOT (ai.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' AND PAYMENT_STATUS_FLAG='N')
AND TRUNC(AD.ACCOUNTING_DATE) <= :P89_DATE
ORDER BY APS.SEGMENT1)
GROUP BY VENDOR_TYPE_LOOKUP_CODE
,       ORG_ID
,       VENDOR_NUM
,       VENDOR_NAME
,       VENDOR_SITE_CODE
,       CODE_COMBINATION