VIEW: APPS.OKL_BPD_RECEIPT_APPLIC_UV

Source

View Text - Preformatted

SELECT HD.IXX_ID CUSTOMER_ID ,SUBSTRB (PY.PARTY_NAME, 1, 50) CUSTOMER_NAME ,CA.ACCOUNT_NUMBER CUSTOMER_NUMBER ,HD.ID CONSOLIDATED_INVOICE_ID ,HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER ,HD.CURRENCY_CODE RCPT_CURRENCY ,ST.KHR_ID CONTRACT_ID ,ST.KLE_ID CONTRACT_LINE_ID ,CNT.CONTRACT_NUMBER CONTRACT_NUMBER ,EXT.CHECK_NUMBER RECEIPT_NUMBER ,EXT.ICR_ID CASH_RECEIPT_ID ,EXT.RECEIPT_DATE RECEIPT_DATE ,EXT.ID EXT_CASH_APPS_ID ,XTL.ID XTL_CASH_APPS_ID ,ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID ,PS.TRX_NUMBER AR_INVOICE_NUMBER ,FA.ASSET_ID ASSET_ID ,FA.ASSET_NUMBER ASSET_NUMBER ,FA.DESCRIPTION ASSET_NAME ,ST.ID LSM_ID ,SM.ID STREAM_ID ,SM.NAME STREAM_NAME ,PS.AMOUNT_DUE_REMAINING OUTSTANDING_STREAM_AMOUNT ,XTL.AMOUNT_APPLIED APPLIED_STREAM_AMOUNT ,EXT.GL_DATE GL_DATE ,RCT.IRM_ID IRM_ID ,EXT.REMITTANCE_AMOUNT RECEIPT_AMOUNT ,EXT.EXCHANGE_RATE EXCHANGE_RATE ,EXT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE ,EXT.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE ,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE',SM.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING FROM FA_ADDITIONS_V FA,OKL_STRM_TYPE_V SM,OKL_EXT_CSH_RCPTS_ALL_B EXT,OKL_XTL_CSH_APPS_ALL_B XTL,OKL_TRX_CSH_RCPT_ALL_B RCT,AR_PAYMENT_SCHEDULES_ALL PS,OKL_CNSLD_AR_STRMS_B ST,OKL_CNSLD_AR_LINES_B LN,OKL_CNSLD_AR_HDRS_B HD,HZ_CUST_ACCOUNTS CA,HZ_PARTIES PY,OKC_K_HEADERS_ALL_B CNT,OKC_K_LINES_B CLE,OKC_LINE_STYLES_B LSE,OKC_K_ITEMS CIM WHERE XTL.XCR_ID_DETAILS = EXT.ID AND EXT.RCT_ID = RCT.ID AND ST.ID = XTL.LSM_ID AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID AND SM.ID = ST.STY_ID AND LN.ID = ST.LLN_ID AND HD.ID = LN.CNR_ID AND CA.CUST_ACCOUNT_ID = HD.IXX_ID AND PY.PARTY_ID = CA.PARTY_ID AND CNT.ID = ST.KHR_ID AND CLE.CLE_ID = ST.KLE_ID AND CLE.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET' AND CIM.CLE_ID = CLE.ID AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET' AND CIM.OBJECT1_ID2 = '#' AND FA.ASSET_ID = CIM.OBJECT1_ID1 UNION SELECT HD.IXX_ID , SUBSTRB (PY.PARTY_NAME, 1, 50) , CA.ACCOUNT_NUMBER , HD.ID , HD.CONSOLIDATED_INVOICE_NUMBER , HD.CURRENCY_CODE , ST.KHR_ID , ST.KLE_ID , CNT.CONTRACT_NUMBER , EXT.CHECK_NUMBER , EXT.ICR_ID CASH_RECEIPT_ID, EXT.RECEIPT_DATE , EXT.ID ,XTL.ID , ST.RECEIVABLES_INVOICE_ID , PS.TRX_NUMBER , to_number (NULL) , '' , '' , ST.ID , SM.ID , SM.NAME , PS.AMOUNT_DUE_REMAINING , XTL.AMOUNT_APPLIED , EXT.GL_DATE , RCT.IRM_ID , EXT.REMITTANCE_AMOUNT , EXT.EXCHANGE_RATE, EXT.EXCHANGE_RATE_TYPE, EXT.EXCHANGE_RATE_DATE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE',SM.STREAM_TYPE_PURPOSE) FROM OKL_EXT_CSH_RCPTS_ALL_B EXT , OKL_XTL_CSH_APPS_ALL_B XTL , OKL_TRX_CSH_RCPT_ALL_B RCT , AR_PAYMENT_SCHEDULES_ALL PS , OKL_STRM_TYPE_V SM , OKL_CNSLD_AR_STRMS_B ST , OKL_CNSLD_AR_LINES_B LN , OKL_CNSLD_AR_HDRS_B HD , HZ_CUST_ACCOUNTS CA , HZ_PARTIES PY , OKC_K_HEADERS_ALL_B CNT WHERE XTL.XCR_ID_DETAILS = EXT.ID AND EXT.RCT_ID = RCT.ID AND ST.ID = XTL.LSM_ID AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID AND SM.ID = ST.STY_ID AND LN.ID = ST.LLN_ID AND HD.ID = LN.CNR_ID AND CA.CUST_ACCOUNT_ID = HD.IXX_ID AND PY.PARTY_ID = CA.PARTY_ID AND CNT.ID (+) = ST.KHR_ID AND ( ST.KLE_ID IS NULL OR ( ST.KLE_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM OKC_K_LINES_B KLE ,OKC_LINE_STYLES_B LSE WHERE KLE.CLE_ID = ST.KLE_ID AND KLE.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET'))) UNION SELECT HD.IXX_ID CUSTOMER_ID , SUBSTRB (PY.PARTY_NAME, 1, 50) CUSTOMER_NAME , CA.ACCOUNT_NUMBER CUSTOMER_NUMBER , HD.ID CONSOLIDATED_INVOICE_ID , HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER , HD.CURRENCY_CODE RCPT_CURRENCY , ST.KHR_ID CONTRACT_ID , ST.KLE_ID CONTRACT_LINE_ID , CNT.CONTRACT_NUMBER CONTRACT_NUMBER , EXT.CHECK_NUMBER RECEIPT_NUMBER , EXT.ICR_ID CASH_RECEIPT_ID, EXT.RECEIPT_DATE RECEIPT_DATE , EXT.ID EXT_CASH_APPS_ID , XTL.ID XTL_CASH_APPS_ID , ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID , PS.TRX_NUMBER AR_INVOICE_NUMBER , to_number (NULL) , '' , '' , ST.ID LSM_ID , SM.ID STREAM_ID , SM.NAME STREAM_NAME , PS.AMOUNT_DUE_REMAINING OUTSTANDING_STREAM_AMOUNT , XTL.AMOUNT_APPLIED APPLIED_STREAM_AMOUNT , EXT.GL_DATE GL_DATE , RCT.IRM_ID IRM_ID , EXT.REMITTANCE_AMOUNT RECEIPT_AMOUNT , EXT.EXCHANGE_RATE EXCHANGE_RATE , EXT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE , EXT.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE , OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE',SM.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING FROM OKL_STRM_TYPE_V SM, OKL_EXT_CSH_RCPTS_ALL_B EXT, OKL_XTL_CSH_APPS_ALL_B XTL, OKL_TRX_CSH_RCPT_ALL_B RCT, AR_PAYMENT_SCHEDULES_ALL PS, OKL_CNSLD_AR_STRMS_B ST, OKL_CNSLD_AR_LINES_B LN, OKL_CNSLD_AR_HDRS_B HD, HZ_CUST_ACCOUNTS CA, HZ_PARTIES PY, OKC_K_HEADERS_ALL_B CNT, OKC_K_LINES_B CLE, OKC_LINE_STYLES_B LSE, OKC_K_ITEMS CIM WHERE XTL.XCR_ID_DETAILS = EXT.ID AND EXT.RCT_ID = RCT.ID AND ST.ID = XTL.LSM_ID AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID AND SM.ID = ST.STY_ID AND LN.ID = ST.LLN_ID AND HD.ID = LN.CNR_ID AND CA.CUST_ACCOUNT_ID = HD.IXX_ID AND PY.PARTY_ID = CA.PARTY_ID AND CNT.ID = ST.KHR_ID AND CLE.CLE_ID = ST.KLE_ID AND CLE.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET' AND CIM.CLE_ID = CLE.ID AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET' AND CIM.OBJECT1_ID1 is null

View Text - HTML Formatted

SELECT HD.IXX_ID CUSTOMER_ID
, SUBSTRB (PY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HD.ID CONSOLIDATED_INVOICE_ID
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE RCPT_CURRENCY
, ST.KHR_ID CONTRACT_ID
, ST.KLE_ID CONTRACT_LINE_ID
, CNT.CONTRACT_NUMBER CONTRACT_NUMBER
, EXT.CHECK_NUMBER RECEIPT_NUMBER
, EXT.ICR_ID CASH_RECEIPT_ID
, EXT.RECEIPT_DATE RECEIPT_DATE
, EXT.ID EXT_CASH_APPS_ID
, XTL.ID XTL_CASH_APPS_ID
, ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, PS.TRX_NUMBER AR_INVOICE_NUMBER
, FA.ASSET_ID ASSET_ID
, FA.ASSET_NUMBER ASSET_NUMBER
, FA.DESCRIPTION ASSET_NAME
, ST.ID LSM_ID
, SM.ID STREAM_ID
, SM.NAME STREAM_NAME
, PS.AMOUNT_DUE_REMAINING OUTSTANDING_STREAM_AMOUNT
, XTL.AMOUNT_APPLIED APPLIED_STREAM_AMOUNT
, EXT.GL_DATE GL_DATE
, RCT.IRM_ID IRM_ID
, EXT.REMITTANCE_AMOUNT RECEIPT_AMOUNT
, EXT.EXCHANGE_RATE EXCHANGE_RATE
, EXT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, EXT.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, SM.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING
FROM FA_ADDITIONS_V FA
, OKL_STRM_TYPE_V SM
, OKL_EXT_CSH_RCPTS_ALL_B EXT
, OKL_XTL_CSH_APPS_ALL_B XTL
, OKL_TRX_CSH_RCPT_ALL_B RCT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CNT
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
WHERE XTL.XCR_ID_DETAILS = EXT.ID
AND EXT.RCT_ID = RCT.ID
AND ST.ID = XTL.LSM_ID
AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID
AND SM.ID = ST.STY_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND CNT.ID = ST.KHR_ID
AND CLE.CLE_ID = ST.KLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID2 = '#'
AND FA.ASSET_ID = CIM.OBJECT1_ID1 UNION SELECT HD.IXX_ID
, SUBSTRB (PY.PARTY_NAME
, 1
, 50)
, CA.ACCOUNT_NUMBER
, HD.ID
, HD.CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE
, ST.KHR_ID
, ST.KLE_ID
, CNT.CONTRACT_NUMBER
, EXT.CHECK_NUMBER
, EXT.ICR_ID CASH_RECEIPT_ID
, EXT.RECEIPT_DATE
, EXT.ID
, XTL.ID
, ST.RECEIVABLES_INVOICE_ID
, PS.TRX_NUMBER
, TO_NUMBER (NULL)
, ''
, ''
, ST.ID
, SM.ID
, SM.NAME
, PS.AMOUNT_DUE_REMAINING
, XTL.AMOUNT_APPLIED
, EXT.GL_DATE
, RCT.IRM_ID
, EXT.REMITTANCE_AMOUNT
, EXT.EXCHANGE_RATE
, EXT.EXCHANGE_RATE_TYPE
, EXT.EXCHANGE_RATE_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, SM.STREAM_TYPE_PURPOSE)
FROM OKL_EXT_CSH_RCPTS_ALL_B EXT
, OKL_XTL_CSH_APPS_ALL_B XTL
, OKL_TRX_CSH_RCPT_ALL_B RCT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_STRM_TYPE_V SM
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CNT
WHERE XTL.XCR_ID_DETAILS = EXT.ID
AND EXT.RCT_ID = RCT.ID
AND ST.ID = XTL.LSM_ID
AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID
AND SM.ID = ST.STY_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND CNT.ID (+) = ST.KHR_ID
AND ( ST.KLE_ID IS NULL OR ( ST.KLE_ID IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B KLE
, OKC_LINE_STYLES_B LSE
WHERE KLE.CLE_ID = ST.KLE_ID
AND KLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'))) UNION SELECT HD.IXX_ID CUSTOMER_ID
, SUBSTRB (PY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HD.ID CONSOLIDATED_INVOICE_ID
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE RCPT_CURRENCY
, ST.KHR_ID CONTRACT_ID
, ST.KLE_ID CONTRACT_LINE_ID
, CNT.CONTRACT_NUMBER CONTRACT_NUMBER
, EXT.CHECK_NUMBER RECEIPT_NUMBER
, EXT.ICR_ID CASH_RECEIPT_ID
, EXT.RECEIPT_DATE RECEIPT_DATE
, EXT.ID EXT_CASH_APPS_ID
, XTL.ID XTL_CASH_APPS_ID
, ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, PS.TRX_NUMBER AR_INVOICE_NUMBER
, TO_NUMBER (NULL)
, ''
, ''
, ST.ID LSM_ID
, SM.ID STREAM_ID
, SM.NAME STREAM_NAME
, PS.AMOUNT_DUE_REMAINING OUTSTANDING_STREAM_AMOUNT
, XTL.AMOUNT_APPLIED APPLIED_STREAM_AMOUNT
, EXT.GL_DATE GL_DATE
, RCT.IRM_ID IRM_ID
, EXT.REMITTANCE_AMOUNT RECEIPT_AMOUNT
, EXT.EXCHANGE_RATE EXCHANGE_RATE
, EXT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, EXT.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, SM.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING
FROM OKL_STRM_TYPE_V SM
, OKL_EXT_CSH_RCPTS_ALL_B EXT
, OKL_XTL_CSH_APPS_ALL_B XTL
, OKL_TRX_CSH_RCPT_ALL_B RCT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CNT
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
WHERE XTL.XCR_ID_DETAILS = EXT.ID
AND EXT.RCT_ID = RCT.ID
AND ST.ID = XTL.LSM_ID
AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID
AND SM.ID = ST.STY_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND CNT.ID = ST.KHR_ID
AND CLE.CLE_ID = ST.KLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 IS NULL