View: OKL_CS_TRX_PAYMENTS_UV

Product:	OKL - Lease and Finance Management
Description:
Implementation/DBA Data:	APPS.OKL_CS_TRX_PAYMENTS_UV

View Text

SELECT ARAP.RECEIVABLE_APPLICATION_ID
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE9 STREAM_TYPE
, NVL(CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1
, PAY_SCH.TRX_NUMBER) INVOICE_NUMBER
, PAY_SCH.TRX_DATE
, ( NVL(CUST_TRX_LINES.EXTENDED_AMOUNT
, 0) + (SELECT NVL(SUM(EXTENDED_AMOUNT)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL TAX_LINES
WHERE TAX_LINES.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND TAX_LINES.LINE_TYPE = 'TAX')) ORIGINAL_AMOUNT
, (ARL.AMOUNT + ARL.TAX ) APPLIED_AMOUNT
, ( CUST_TRX_LINES.AMOUNT_DUE_REMAINING + (SELECT NVL(SUM(AMOUNT_DUE_REMAINING)
, 0)
FROM RA_CUSTOMER_TRX_LINES_ALL TAX_LINES
WHERE TAX_LINES.LINK_TO_CUST_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND TAX_LINES.LINE_TYPE = 'TAX') ) AMOUNT_REMAINING
, ARM.NAME
, ARCASH.RECEIPT_NUMBER
, ARCASH.AMOUNT
, ARCASH.RECEIPT_DATE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, PAY_SCH.DUE_DATE
, ARAP.APPLY_DATE
, ALKP.MEANING STATUS
, NVL2(CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1
, (PAY_SCH.TRX_NUMBER)
, TO_CHAR(CUST_TRX_LINES.LINE_NUMBER)) LINE_NUMBER
, ARCASH.CURRENCY_CODE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARCASH.RECEIPT_METHOD_ID
, ARCASH.CASH_RECEIPT_ID
, PAY_SCH.CUSTOMER_TRX_ID
, KHR.ID
, ARCASH.PAY_FROM_CUSTOMER
, DECODE((OKLCASH.RECEIPT_TYPE)
, 'ADV'
, 'ADV'
, 'REG') RECEIPT_TYPE
, ARCASH.ORG_ID
, HCA.PARTY_ID
, CRH.STATUS
, RCTSTSLK.MEANING
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
, RTYPLK.MEANING ADVANCED_YN
, HCA.ACCOUNT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIVABLE_APPLICATIONS_ALL ARAP
, AR_ACTIVITY_DETAILS ARL
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL CUST_TRX
, RA_CUSTOMER_TRX_LINES_ALL CUST_TRX_LINES
, AR_RECEIPT_METHODS ARM
, OKC_K_HEADERS_ALL_B KHR
, OKL_TRX_CSH_RCPT_ALL_B OKLCASH
, HZ_CUST_ACCOUNTS HCA
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_LOOKUPS RCTSTSLK
, AR_LOOKUPS ALKP
, FND_LOOKUPS RTYPLK
WHERE ARCASH.CASH_RECEIPT_ID = ARAP.CASH_RECEIPT_ID
AND ARAP.STATUS = 'APP'
AND ARAP.DISPLAY = 'Y'
AND ARAP.RECEIVABLE_APPLICATION_ID = ARL.SOURCE_ID
AND ARL.SOURCE_TABLE = 'RA'
AND ARL.CUSTOMER_TRX_LINE_ID = CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND CUST_TRX_LINES.LINE_TYPE = 'LINE'
AND PAY_SCH.PAYMENT_SCHEDULE_ID = ARAP.APPLIED_PAYMENT_SCHEDULE_ID
AND PAY_SCH.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND ARM.RECEIPT_METHOD_ID = ARCASH.RECEIPT_METHOD_ID
AND CUST_TRX_LINES.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')
AND CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 = KHR.CONTRACT_NUMBER
AND ARCASH.CASH_RECEIPT_ID = OKLCASH.CASH_RECEIPT_ID(+)
AND ARCASH.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND ARCASH.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS = RCTSTSLK.LOOKUP_CODE
AND RCTSTSLK.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
AND ARAP.STATUS = ALKP.LOOKUP_CODE
AND ALKP.LOOKUP_TYPE = 'PAYMENT_TYPE'
AND RTYPLK.LOOKUP_CODE = DECODE((OKLCASH.RECEIPT_TYPE)
, 'ADV'
, 'Y'
, 'N')
AND RTYPLK.LOOKUP_TYPE = 'OKL_YES_NO' UNION ALL SELECT ARAP.RECEIVABLE_APPLICATION_ID
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE9 STREAM_TYPE
, NVL((SELECT LN.INTERFACE_LINE_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_LINES_ALL LN
WHERE LN.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND ROWNUM < 2)
, PAY_SCH.TRX_NUMBER) INVOICE_NUMBER
, PAY_SCH.TRX_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL ORIGINAL_AMOUNT
, ARAP.AMOUNT_APPLIED
, PAY_SCH.AMOUNT_DUE_REMAINING
, ARM.NAME
, ARCASH.RECEIPT_NUMBER
, ARCASH.AMOUNT
, ARCASH.RECEIPT_DATE
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE6 CONTRACT_NUMBER
, PAY_SCH.DUE_DATE
, ARAP.APPLY_DATE
, ALKP.MEANING STATUS
, NVL2((SELECT LN.INTERFACE_LINE_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_LINES_ALL LN
WHERE LN.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND ROWNUM < 2)
, PAY_SCH.TRX_NUMBER
, NULL) LINE_NUMBER
, ARCASH.CURRENCY_CODE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARCASH.RECEIPT_METHOD_ID
, ARCASH.CASH_RECEIPT_ID
, PAY_SCH.CUSTOMER_TRX_ID
, KHR.ID
, ARCASH.PAY_FROM_CUSTOMER
, DECODE((OKLCASH.RECEIPT_TYPE)
, 'ADV'
, 'ADV'
, 'REG') RECEIPT_TYPE
, ARCASH.ORG_ID
, HCA.PARTY_ID
, CRH.STATUS
, RCTSTSLK.MEANING
, NULL INVOICE_LINE_ID
, RTYPLK.MEANING ADVANCED_YN
, HCA.ACCOUNT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIVABLE_APPLICATIONS_ALL ARAP
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, RA_CUSTOMER_TRX_ALL CUST_TRX
, AR_RECEIPT_METHODS ARM
, OKC_K_HEADERS_ALL_B KHR
, OKL_TRX_CSH_RCPT_ALL_B OKLCASH
, HZ_CUST_ACCOUNTS HCA
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_LOOKUPS RCTSTSLK
, AR_LOOKUPS ALKP
, FND_LOOKUPS RTYPLK
WHERE ARCASH.CASH_RECEIPT_ID = ARAP.CASH_RECEIPT_ID
AND ARAP.STATUS = 'APP'
AND ARAP.DISPLAY = 'Y'
AND PAY_SCH.PAYMENT_SCHEDULE_ID = ARAP.APPLIED_PAYMENT_SCHEDULE_ID
AND PAY_SCH.CUSTOMER_TRX_ID = CUST_TRX.CUSTOMER_TRX_ID
AND ARM.RECEIPT_METHOD_ID = ARCASH.RECEIPT_METHOD_ID
AND CUST_TRX.INTERFACE_HEADER_CONTEXT IN ('OKL_CONTRACTS'
, 'OKL_MANUAL')
AND CUST_TRX.INTERFACE_HEADER_ATTRIBUTE6 = KHR.CONTRACT_NUMBER
AND ARCASH.CASH_RECEIPT_ID = OKLCASH.CASH_RECEIPT_ID(+)
AND ARCASH.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND ARCASH.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS = RCTSTSLK.LOOKUP_CODE
AND RCTSTSLK.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
AND ARAP.STATUS = ALKP.LOOKUP_CODE
AND ALKP.LOOKUP_TYPE = 'PAYMENT_TYPE'
AND RTYPLK.LOOKUP_CODE = DECODE((OKLCASH.RECEIPT_TYPE)
, 'ADV'
, 'Y'
, 'N')
AND RTYPLK.LOOKUP_TYPE = 'OKL_YES_NO'
AND NOT EXISTS (SELECT 'X'
FROM AR_ACTIVITY_DETAILS ARL
WHERE ARL.SOURCE_ID = ARAP.RECEIVABLE_APPLICATION_ID
AND ARL.SOURCE_TABLE = 'RA') UNION ALL SELECT DISTINCT NULL RECEIVABLE_APPLICATION_ID
, NULL STREAM_TYPE
, NULL INVOICE_NUMBER
, NULL TRX_DATE
, NULL ORIGINAL_AMOUNT
, NULL APPLIED_AMOUNT
, NULL AMOUNT_REMAINING
, ARM.NAME
, ARCASH.RECEIPT_NUMBER
, ARCASH.AMOUNT
, ARCASH.RECEIPT_DATE
, KHR.CONTRACT_NUMBER
, NULL DUE_DATE
, NULL APPLY_DATE
, NULL STATUS
, NULL LINE_NUMBER
, ARCASH.CURRENCY_CODE
, NULL INVOICE_CURRENCY_CODE
, ARCASH.RECEIPT_METHOD_ID
, ARCASH.CASH_RECEIPT_ID
, NULL CUSTOMER_TRX_ID
, KHR.ID
, ARCASH.PAY_FROM_CUSTOMER
, 'ADV' RECEIPT_TYPE
, ARCASH.ORG_ID
, HCA.PARTY_ID
, CRH.STATUS
, RCTSTSLK.MEANING
, NULL CUSTOMER_TRX_LINE_ID
, FLK.MEANING
, HCA.ACCOUNT_NUMBER
FROM AR_CASH_RECEIPTS_ALL ARCASH
, AR_PAYMENT_SCHEDULES_ALL PAY_SCH
, AR_RECEIPT_METHODS ARM
, OKC_K_HEADERS_ALL_B KHR
, OKL_TRX_CSH_RCPT_ALL_B OKLCASH
, HZ_CUST_ACCOUNTS HCA
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_LOOKUPS RCTSTSLK
, OKL_TXL_RCPT_APPS_ALL_B OKLAPPL
, FND_LOOKUPS FLK
WHERE ARM.RECEIPT_METHOD_ID = ARCASH.RECEIPT_METHOD_ID
AND ARCASH.CASH_RECEIPT_ID = OKLCASH.CASH_RECEIPT_ID
AND OKLCASH.RECEIPT_TYPE = 'ADV'
AND ARCASH.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
AND ARCASH.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND CRH.STATUS = RCTSTSLK.LOOKUP_CODE
AND RCTSTSLK.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
AND OKLCASH.ID = OKLAPPL.RCT_ID_DETAILS
AND OKLAPPL.KHR_ID = KHR.ID
AND FLK.LOOKUP_CODE = 'Y'
AND FLK.LOOKUP_TYPE ='OKL_YES_NO'
AND ( SELECT SUM(AMOUNT_APPLIED)
FROM AR_RECEIVABLE_APPLICATIONS_ALL RA
WHERE ARCASH.CASH_RECEIPT_ID = RA.CASH_RECEIPT_ID
AND RA.STATUS = 'ACC') = ARCASH.AMOUNT

Columns