VIEW: APPS.OKE_DTS_RECEIVABLES_V

Source

View Text - Preformatted

SELECT KE.K_HEADER_ID , KE.BILL_LINE_ID , KE.DELIVERABLE_ID , PE.PROJECT_ID , PE.TASK_ID , P.SEGMENT1 , T.TASK_NUMBER , PE.EVENT_NUM , DII.DRAFT_INVOICE_NUM , DII.LINE_NUM , CT.CUSTOMER_TRX_ID , CT.TRX_NUMBER , CT.TRX_DATE , CT.INVOICE_CURRENCY_CODE , TE.NAME , PS.AMOUNT_DUE_ORIGINAL , PS.AMOUNT_DUE_REMAINING , PS.AMOUNT_APPLIED , PS.AMOUNT_CREDITED , PS.AMOUNT_ADJUSTED , CTL.CUSTOMER_TRX_LINE_ID , CTL.LINE_NUMBER , CTL.EXTENDED_AMOUNT , CTL.DESCRIPTION FROM OKE_K_BILLING_EVENTS KE , PA_EVENTS PE , PA_PROJECTS_ALL P , PA_TASKS T , PA_DRAFT_INVOICE_ITEMS DII , PA_DRAFT_INVOICES_ALL DI , RA_CUSTOMER_TRX_LINES_ALL CTL , RA_CUSTOMER_TRX_ALL CT , ( SELECT CUSTOMER_TRX_ID , INVOICE_CURRENCY_CODE , SUM(AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL , SUM(AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING , SUM(AMOUNT_APPLIED) AMOUNT_APPLIED , SUM(AMOUNT_CREDITED) AMOUNT_CREDITED , SUM(AMOUNT_ADJUSTED) AMOUNT_ADJUSTED FROM AR_PAYMENT_SCHEDULES_ALL GROUP BY CUSTOMER_TRX_ID , INVOICE_CURRENCY_CODE ) PS , RA_TERMS_TL TE WHERE PE.EVENT_ID = KE.PA_EVENT_ID AND P.PROJECT_ID = PE.PROJECT_ID AND T.TASK_ID (+) = PE.TASK_ID AND DII.PROJECT_ID = PE.PROJECT_ID AND NVL( DII.EVENT_TASK_ID , -1 ) = NVL( PE.TASK_ID , -1 ) AND DII.EVENT_NUM = PE.EVENT_NUM AND DI.PROJECT_ID = DII.PROJECT_ID AND DI.DRAFT_INVOICE_NUM = DII.DRAFT_INVOICE_NUM AND CTL.CUSTOMER_TRX_ID = DI.SYSTEM_REFERENCE AND CTL.LINE_TYPE = 'LINE' AND CTL.INTERFACE_LINE_ATTRIBUTE1 = P.SEGMENT1 AND CTL.INTERFACE_LINE_ATTRIBUTE2 = DII.DRAFT_INVOICE_NUM AND CTL.INTERFACE_LINE_ATTRIBUTE6 = DII.LINE_NUM AND NVL( CTL.ORG_ID , -99 ) = NVL( P.ORG_ID , -99 ) AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID AND TE.TERM_ID = CT.TERM_ID AND TE.LANGUAGE = USERENV('LANG') AND PS.CUSTOMER_TRX_ID (+) = CT.CUSTOMER_TRX_ID AND PS.INVOICE_CURRENCY_CODE (+) = CT.INVOICE_CURRENCY_CODE

View Text - HTML Formatted

SELECT KE.K_HEADER_ID
, KE.BILL_LINE_ID
, KE.DELIVERABLE_ID
, PE.PROJECT_ID
, PE.TASK_ID
, P.SEGMENT1
, T.TASK_NUMBER
, PE.EVENT_NUM
, DII.DRAFT_INVOICE_NUM
, DII.LINE_NUM
, CT.CUSTOMER_TRX_ID
, CT.TRX_NUMBER
, CT.TRX_DATE
, CT.INVOICE_CURRENCY_CODE
, TE.NAME
, PS.AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING
, PS.AMOUNT_APPLIED
, PS.AMOUNT_CREDITED
, PS.AMOUNT_ADJUSTED
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.LINE_NUMBER
, CTL.EXTENDED_AMOUNT
, CTL.DESCRIPTION
FROM OKE_K_BILLING_EVENTS KE
, PA_EVENTS PE
, PA_PROJECTS_ALL P
, PA_TASKS T
, PA_DRAFT_INVOICE_ITEMS DII
, PA_DRAFT_INVOICES_ALL DI
, RA_CUSTOMER_TRX_LINES_ALL CTL
, RA_CUSTOMER_TRX_ALL CT
, ( SELECT CUSTOMER_TRX_ID
, INVOICE_CURRENCY_CODE
, SUM(AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL
, SUM(AMOUNT_DUE_REMAINING) AMOUNT_DUE_REMAINING
, SUM(AMOUNT_APPLIED) AMOUNT_APPLIED
, SUM(AMOUNT_CREDITED) AMOUNT_CREDITED
, SUM(AMOUNT_ADJUSTED) AMOUNT_ADJUSTED
FROM AR_PAYMENT_SCHEDULES_ALL GROUP BY CUSTOMER_TRX_ID
, INVOICE_CURRENCY_CODE ) PS
, RA_TERMS_TL TE
WHERE PE.EVENT_ID = KE.PA_EVENT_ID
AND P.PROJECT_ID = PE.PROJECT_ID
AND T.TASK_ID (+) = PE.TASK_ID
AND DII.PROJECT_ID = PE.PROJECT_ID
AND NVL( DII.EVENT_TASK_ID
, -1 ) = NVL( PE.TASK_ID
, -1 )
AND DII.EVENT_NUM = PE.EVENT_NUM
AND DI.PROJECT_ID = DII.PROJECT_ID
AND DI.DRAFT_INVOICE_NUM = DII.DRAFT_INVOICE_NUM
AND CTL.CUSTOMER_TRX_ID = DI.SYSTEM_REFERENCE
AND CTL.LINE_TYPE = 'LINE'
AND CTL.INTERFACE_LINE_ATTRIBUTE1 = P.SEGMENT1
AND CTL.INTERFACE_LINE_ATTRIBUTE2 = DII.DRAFT_INVOICE_NUM
AND CTL.INTERFACE_LINE_ATTRIBUTE6 = DII.LINE_NUM
AND NVL( CTL.ORG_ID
, -99 ) = NVL( P.ORG_ID
, -99 )
AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
AND TE.TERM_ID = CT.TERM_ID
AND TE.LANGUAGE = USERENV('LANG')
AND PS.CUSTOMER_TRX_ID (+) = CT.CUSTOMER_TRX_ID
AND PS.INVOICE_CURRENCY_CODE (+) = CT.INVOICE_CURRENCY_CODE