VIEW: APPS.OKL_BPD_AR_INV_LN_DTLS_V

Source

View Text - Preformatted

SELECT CN.CONTRACT_NUMBER CONTRACT_NUMBER, ST.KLE_ID CONTRACT_LINE_ID, LN.SEQUENCE_NUMBER CONSOLIDATED_LINE_NUMBER, ILT.NAME LINE_NAME, SUM(PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL, SUM(PS.AMOUNT_DUE_ORIGINAL) - SUM(PS.AMOUNT_DUE_REMAINING) AMOUNT_PAID, SUM(PS.AMOUNT_DUE_REMAINING) BALANCE, ( SELECT NVL(SUM(app.amount_applied),0) FROM ar_receivable_applications_all app, ar_payment_schedules_all sch WHERE app.status = 'APP' AND app.applied_payment_schedule_id = sch.payment_schedule_id AND sch.class = 'INV' AND sch.customer_trx_id = PS.CUSTOMER_TRX_ID AND app.application_type = 'CASH' ) AMOUNT_APPLIED, ( SELECT NVL(SUM(app.amount_applied),0) FROM ar_receivable_applications_all app, ar_payment_schedules_all sch WHERE app.status = 'APP' AND app.applied_payment_schedule_id = sch.payment_schedule_id AND sch.class = 'INV' AND sch.customer_trx_id = PS.CUSTOMER_TRX_ID AND app.application_type = 'CM' ) AMOUNT_CREDITED, SUM(ST.TAX_AMOUNT) TAX_AMOUNT, PS.trx_number RECEIVABLES_INVOICE_NUMBER , STB.NAME STREAM_TYPE, LKUP.MEANING STREAM_PURPOSE_MEANING, HD.CURRENCY_CODE CURRENCY_CODE, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, RACTRL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, CA.CUST_ACCOUNT_ID IXX_ID, NULL TXN_LINE_TYPE, RACTRL.INTERFACE_LINE_ATTRIBUTE14 TLD_ID, PS.ORG_ID ORG_ID FROM OKL_INVC_LINE_TYPES_V ILT,AR_PAYMENT_SCHEDULES_ALL PS,OKL_CNSLD_AR_STRMS_B ST,OKL_CNSLD_AR_LINES_B LN,OKL_CNSLD_AR_HDRS_B HD,OKC_K_HEADERS_B CN,HZ_CUST_ACCOUNTS CA,OKL_STRM_TYPE_V STB,FND_LOOKUPS LKUP,RA_CUSTOMER_TRX_LINES_ALL RACTRL WHERE PS.CLASS IN ('INV', 'CM') AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID AND LN.ID = ST.LLN_ID AND HD.ID = LN.CNR_ID AND CN.ID (+) = ST.KHR_ID AND LN.ID = ST.LLN_ID AND ILT.ID (+) = LN.ILT_ID AND CA.CUST_ACCOUNT_ID = HD.IXX_ID AND PS.CUSTOMER_ID = CA.CUST_ACCOUNT_ID AND ST.sty_id=STB.id AND LKUP.LOOKUP_CODE = STB.STREAM_TYPE_PURPOSE AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE' AND ST.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID AND RACTRL.LINE_TYPE = 'LINE' GROUP BY HD.CONSOLIDATED_INVOICE_NUMBER, CN.CONTRACT_NUMBER, ST.KLE_ID, LN.SEQUENCE_NUMBER, ILT.NAME, PS.trx_number,STB.NAME, LKUP.MEANING, HD.CURRENCY_CODE, PS.CUSTOMER_TRX_ID, RACTRL.CUSTOMER_TRX_LINE_ID, CA.CUST_ACCOUNT_ID, RACTRL.INTERFACE_LINE_ATTRIBUTE14, PS.ORG_ID UNION ALL SELECT ARL.CONTRACT_NUMBER CONTRACT_NUMBER, ARL.CONTRACT_LINE_ID CONTRACT_LINE_ID, ARL.LINE_NUMBER CONSOLIDATED_LINE_NUMBER, ARL.LINE_TYPE LINE_NAME, ARL.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL, (ARL.AMOUNT_DUE_ORIGINAL - ARL.AMOUNT_DUE_REMAINING) AMOUNT_PAID, /* ARL.AMOUNT_DUE_REMAINING BALANCE, */ OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING( ARL.RECEIVABLES_INVOICE_ID, ARL.RECEIVABLES_INVOICE_LINE_ID) BALANCE, /*( SELECT NVL(SUM(app.amount_applied),0) FROM ar_receivable_applications_all app, ar_payment_schedules_all sch WHERE app.status = 'APP' AND app.applied_payment_schedule_id = sch.payment_schedule_id AND sch.class = 'INV' AND sch.customer_trx_id = ARL.RECEIVABLES_INVOICE_ID AND app.application_type = 'CASH' AND app.applied_customer_trx_id = ARL.RECEIVABLES_INVOICE_ID AND app.applied_customer_trx_line_id = ARL.RECEIVABLES_INVOICE_LINE_ID ) AMOUNT_APPLIED, */ OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED ( ARL.RECEIVABLES_INVOICE_ID, ARL.RECEIVABLES_INVOICE_LINE_ID) AMOUNT_APPLIED, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED ( ARL.RECEIVABLES_INVOICE_ID, ARL.RECEIVABLES_INVOICE_LINE_ID) AMOUNT_CREDITED, ARL.TAX_AMOUNT TAX_AMOUNT, ARL.RECEIVABLES_INVOICE_NUMBER RECEIVABLES_INVOICE_NUMBER, STB.NAME STREAM_TYPE, LKUP.MEANING STREAM_PURPOSE_MEANING, ARL.CURRENCY_CODE CURRENCY_CODE, ARL.RECEIVABLES_INVOICE_ID CUSTOMER_TRX_ID, ARL.RECEIVABLES_INVOICE_LINE_ID CUSTOMER_TRX_LINE_ID, NULL CONSOLIDATED_INVOICE_NUMBER, ARL.IXX_ID IXX_ID, ARL.TXN_LINE_TYPE TXN_LINE_TYPE, to_char(ARL.TXD_ID) TLD_ID, ARL.ORG_ID ORG_ID FROM okl_bpd_ar_inv_lines_v ARL, HZ_CUST_ACCOUNTS CA, OKL_STRM_TYPE_V STB, FND_LOOKUPS LKUP WHERE ARL.CLASS IN ('INV', 'CM') AND CA.CUST_ACCOUNT_ID = ARL.IXX_ID AND ARL.STREAM_TYPE_ID=STB.id AND LKUP.LOOKUP_CODE = STB.STREAM_TYPE_PURPOSE AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE' AND ARL.TXN_LINE_TYPE = 'LINE'

View Text - HTML Formatted

SELECT CN.CONTRACT_NUMBER CONTRACT_NUMBER
, ST.KLE_ID CONTRACT_LINE_ID
, LN.SEQUENCE_NUMBER CONSOLIDATED_LINE_NUMBER
, ILT.NAME LINE_NAME
, SUM(PS.AMOUNT_DUE_ORIGINAL) AMOUNT_DUE_ORIGINAL
, SUM(PS.AMOUNT_DUE_ORIGINAL) - SUM(PS.AMOUNT_DUE_REMAINING) AMOUNT_PAID
, SUM(PS.AMOUNT_DUE_REMAINING) BALANCE
, ( SELECT NVL(SUM(APP.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL SCH
WHERE APP.STATUS = 'APP'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = SCH.PAYMENT_SCHEDULE_ID
AND SCH.CLASS = 'INV'
AND SCH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND APP.APPLICATION_TYPE = 'CASH' ) AMOUNT_APPLIED
, ( SELECT NVL(SUM(APP.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL SCH
WHERE APP.STATUS = 'APP'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = SCH.PAYMENT_SCHEDULE_ID
AND SCH.CLASS = 'INV'
AND SCH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND APP.APPLICATION_TYPE = 'CM' ) AMOUNT_CREDITED
, SUM(ST.TAX_AMOUNT) TAX_AMOUNT
, PS.TRX_NUMBER RECEIVABLES_INVOICE_NUMBER
, STB.NAME STREAM_TYPE
, LKUP.MEANING STREAM_PURPOSE_MEANING
, HD.CURRENCY_CODE CURRENCY_CODE
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, RACTRL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, CA.CUST_ACCOUNT_ID IXX_ID
, NULL TXN_LINE_TYPE
, RACTRL.INTERFACE_LINE_ATTRIBUTE14 TLD_ID
, PS.ORG_ID ORG_ID
FROM OKL_INVC_LINE_TYPES_V ILT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, OKC_K_HEADERS_B CN
, HZ_CUST_ACCOUNTS CA
, OKL_STRM_TYPE_V STB
, FND_LOOKUPS LKUP
, RA_CUSTOMER_TRX_LINES_ALL RACTRL
WHERE PS.CLASS IN ('INV'
, 'CM')
AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CN.ID (+) = ST.KHR_ID
AND LN.ID = ST.LLN_ID
AND ILT.ID (+) = LN.ILT_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PS.CUSTOMER_ID = CA.CUST_ACCOUNT_ID
AND ST.STY_ID=STB.ID
AND LKUP.LOOKUP_CODE = STB.STREAM_TYPE_PURPOSE
AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND ST.RECEIVABLES_INVOICE_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.LINE_TYPE = 'LINE' GROUP BY HD.CONSOLIDATED_INVOICE_NUMBER
, CN.CONTRACT_NUMBER
, ST.KLE_ID
, LN.SEQUENCE_NUMBER
, ILT.NAME
, PS.TRX_NUMBER
, STB.NAME
, LKUP.MEANING
, HD.CURRENCY_CODE
, PS.CUSTOMER_TRX_ID
, RACTRL.CUSTOMER_TRX_LINE_ID
, CA.CUST_ACCOUNT_ID
, RACTRL.INTERFACE_LINE_ATTRIBUTE14
, PS.ORG_ID UNION ALL SELECT ARL.CONTRACT_NUMBER CONTRACT_NUMBER
, ARL.CONTRACT_LINE_ID CONTRACT_LINE_ID
, ARL.LINE_NUMBER CONSOLIDATED_LINE_NUMBER
, ARL.LINE_TYPE LINE_NAME
, ARL.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, (ARL.AMOUNT_DUE_ORIGINAL - ARL.AMOUNT_DUE_REMAINING) AMOUNT_PAID
, /* ARL.AMOUNT_DUE_REMAINING BALANCE
, */ OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_REMAINING( ARL.RECEIVABLES_INVOICE_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID) BALANCE
, /*( SELECT NVL(SUM(APP.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
, AR_PAYMENT_SCHEDULES_ALL SCH
WHERE APP.STATUS = 'APP'
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = SCH.PAYMENT_SCHEDULE_ID
AND SCH.CLASS = 'INV'
AND SCH.CUSTOMER_TRX_ID = ARL.RECEIVABLES_INVOICE_ID
AND APP.APPLICATION_TYPE = 'CASH'
AND APP.APPLIED_CUSTOMER_TRX_ID = ARL.RECEIVABLES_INVOICE_ID
AND APP.APPLIED_CUSTOMER_TRX_LINE_ID = ARL.RECEIVABLES_INVOICE_LINE_ID ) AMOUNT_APPLIED
, */ OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED ( ARL.RECEIVABLES_INVOICE_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID) AMOUNT_APPLIED
, OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_CREDITED ( ARL.RECEIVABLES_INVOICE_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID) AMOUNT_CREDITED
, ARL.TAX_AMOUNT TAX_AMOUNT
, ARL.RECEIVABLES_INVOICE_NUMBER RECEIVABLES_INVOICE_NUMBER
, STB.NAME STREAM_TYPE
, LKUP.MEANING STREAM_PURPOSE_MEANING
, ARL.CURRENCY_CODE CURRENCY_CODE
, ARL.RECEIVABLES_INVOICE_ID CUSTOMER_TRX_ID
, ARL.RECEIVABLES_INVOICE_LINE_ID CUSTOMER_TRX_LINE_ID
, NULL CONSOLIDATED_INVOICE_NUMBER
, ARL.IXX_ID IXX_ID
, ARL.TXN_LINE_TYPE TXN_LINE_TYPE
, TO_CHAR(ARL.TXD_ID) TLD_ID
, ARL.ORG_ID ORG_ID
FROM OKL_BPD_AR_INV_LINES_V ARL
, HZ_CUST_ACCOUNTS CA
, OKL_STRM_TYPE_V STB
, FND_LOOKUPS LKUP
WHERE ARL.CLASS IN ('INV'
, 'CM')
AND CA.CUST_ACCOUNT_ID = ARL.IXX_ID
AND ARL.STREAM_TYPE_ID=STB.ID
AND LKUP.LOOKUP_CODE = STB.STREAM_TYPE_PURPOSE
AND LKUP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND ARL.TXN_LINE_TYPE = 'LINE'