OKL Billing Inquiry Data
Description
- Categories
- BI Publisher
- Application
- Lease and Finance Management
- Source
- Short Name
- OKLBILINQDAT
DB package:
SQL 280 lines
SELECT DTLS.LSM_ID LSM_ID , DTLS.CONTRACT_NUMBER CONTRACT_NUMBER , CHR.STS_CODE CONTRACT_STATUS , CHR.START_DATE CONTRACT_EFFECTIVE_FROM , CHR.END_DATE CONTRACT_EFFECTIVE_TO , CHR.CURRENCY_CODE CONTRACT_CURRENCY_CODE , STYT.NAME TRANSACTION_TYPE , DTLS.INVOICE_NUMBER INVOICE_NUMBER , DTLS.INVOICE_AMOUNT INVOICE_AMOUNT , DTLS.LINE_IDENTIFIER LINE_IDENTIFIER , FAA.ASSET_NUMBER ASSET_NUMBER , DTLS.INVOICE_DATE INVOICE_DATE , DTLS.DUE_DATE DUE_DATE , DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT , DTLS.AMOUNT_APPLIED AMOUNT_APPLIED , DTLS.AMOUNT_REMAINING AMOUNT_REMAINING , DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCT_ID , DTLS.ORG_ID ORG_ID , DTLS.CHR_ID KHR_ID , DTLS.KLE_ID KLE_ID , FAA.ASSET_ID ASSET_ID , DTLS.INVOICE_ID INVOICE_ID , STYT.ID STY_ID , DTLS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE , APS.EXCHANGE_RATE AR_EXCHANGE_RATE , APS.EXCHANGE_DATE AR_EXCHANGE_DATE , APS.TRX_DATE AR_TRX_DATE , DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL , DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING, DTLS.TAX_AMOUNT TAX_ORIGINAL , DTLS.TAX_REMAINING TAX_REMAINING , DTLS.AMOUNT_CREDITED AMOUNT_CREDITED , DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID , DTLS.INVOICE_LINE_ID INVOICE_LINE_ID , DTLS.PAYMENT_TERM PAYMENT_TERM , DTLS.INVOICE_TYPE INVOICE_TYPE FROM OKL_CS_BPD_INV_DTL_V DTLS , AR_PAYMENT_SCHEDULES_ALL APS, OKL_STRM_TYPE_TL STYT , FA_ADDITIONS_B FAA , OKC_K_ITEMS CIM , OKC_LINE_STYLES_B LSE , OKC_K_LINES_B CLE , OKL_K_HEADERS CHL , OKC_K_HEADERS_B CHR , OKL_XMLP_PARAMS XMLP WHERE XMLP.BATCH_ID = :BATCH_ID AND DTLS.LSM_ID = XMLP.PARAM_VALUE AND CLE.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET' AND CLE.ID = CIM.CLE_ID AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET' AND CIM.OBJECT1_ID1 = FAA.ASSET_ID AND DTLS.CHR_ID = CHL.ID AND CHL.ID = CHR.ID AND CHL.DEAL_TYPE <> 'LOAN' AND DTLS.KLE_ID IS NOT NULL AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID AND DTLS.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') AND DTLS.KLE_ID = CLE.CLE_ID UNION SELECT DTLS.LSM_ID LSM_ID , DTLS.CONTRACT_NUMBER CONTRACT_NUMBER , CHR.STS_CODE CONTRACT_STATUS , CHR.START_DATE CONTRACT_EFFECTIVE_FROM , CHR.END_DATE CONTRACT_EFFECTIVE_TO , CHR.CURRENCY_CODE CONTRACT_CURRENCY_CODE , STYT.NAME TRANSACTION_TYPE , DTLS.INVOICE_NUMBER INVOICE_NUMBER , DTLS.INVOICE_AMOUNT INVOICE_AMOUNT , DTLS.LINE_IDENTIFIER LINE_IDENTIFIER , OKL_CS_LC_CONTRACT_PVT.GET_ASSET_NUMBER(DTLS.KLE_ID) ASSET_NUMBER, DTLS.INVOICE_DATE INVOICE_DATE , DTLS.DUE_DATE DUE_DATE , DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT , DTLS.AMOUNT_APPLIED AMOUNT_APPLIED , DTLS.AMOUNT_REMAINING AMOUNT_REMAINING , DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID , DTLS.ORG_ID ORG_ID , DTLS.CHR_ID KHR_ID , DTLS.KLE_ID KLE_ID , TO_NUMBER(NULL) ASSET_ID , DTLS.INVOICE_ID INVOICE_ID , STYT.ID STY_ID , DTLS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE , APS.EXCHANGE_RATE AR_EXCHANGE_RATE , APS.EXCHANGE_DATE AR_EXCHANGE_DATE , APS.TRX_DATE AR_TRX_DATE , DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL , DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING , DTLS.TAX_AMOUNT TAX_ORIGINAL , DTLS.TAX_REMAINING TAX_REMAINING , DTLS.AMOUNT_CREDITED AMOUNT_CREDITED , DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID , DTLS.INVOICE_LINE_ID INVOICE_LINE_ID , DTLS.PAYMENT_TERM PAYMENT_TERM , DTLS.INVOICE_TYPE INVOICE_TYPE FROM AR_PAYMENT_SCHEDULES_ALL APS, OKL_STRM_TYPE_TL STYT , OKL_CS_BPD_INV_DTL_V DTLS , OKC_K_HEADERS_B CHR, OKL_XMLP_PARAMS XMLP WHERE XMLP.BATCH_ID = :BATCH_ID AND DTLS.LSM_ID = XMLP.PARAM_VALUE AND DTLS.KLE_ID IS NOT NULL AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID AND DTLS.CHR_ID = CHR.ID AND DTLS.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') AND NOT EXISTS (SELECT 1 FROM OKC_K_LINES_B CLE2, OKC_LINE_STYLES_B LSE WHERE CLE2.CLE_ID = DTLS.KLE_ID AND CLE2.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET' ) UNION SELECT DTLS.LSM_ID LSM_ID , DTLS.CONTRACT_NUMBER CONTRACT_NUMBER , CHR.STS_CODE CONTRACT_STATUS , CHR.START_DATE CONTRACT_EFFECTIVE_FROM , CHR.END_DATE CONTRACT_EFFECTIVE_TO , CHR.CURRENCY_CODE CONTRACT_CURRENCY_CODE , STYT.NAME TRANSACTION_TYPE , DTLS.INVOICE_NUMBER INVOICE_NUMBER , DTLS.INVOICE_AMOUNT INVOICE_AMOUNT , DTLS.LINE_IDENTIFIER LINE_IDENTIFIER , NULL ASSET_NUMBER , DTLS.INVOICE_DATE INVOICE_DATE , DTLS.DUE_DATE DUE_DATE , DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT , DTLS.AMOUNT_APPLIED AMOUNT_APPLIED , DTLS.AMOUNT_REMAINING AMOUNT_REMAINING , DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID , DTLS.ORG_ID ORG_ID , DTLS.CHR_ID KHR_ID , TO_NUMBER(NULL) KLE_ID , TO_NUMBER(NULL) ASSET_ID , DTLS.INVOICE_ID INVOICE_ID , STYT.ID STY_ID , DTLS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE , APS.EXCHANGE_RATE AR_EXCHANGE_RATE , APS.EXCHANGE_DATE AR_EXCHANGE_DATE , APS.TRX_DATE AR_TRX_DATE , DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL , DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING, DTLS.TAX_AMOUNT TAX_ORIGINAL , DTLS.TAX_REMAINING TAX_REMAINING , DTLS.AMOUNT_CREDITED AMOUNT_CREDITED , DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID , DTLS.INVOICE_LINE_ID INVOICE_LINE_ID , DTLS.PAYMENT_TERM PAYMENT_TERM , DTLS.INVOICE_TYPE INVOICE_TYPE FROM OKL_CS_BPD_INV_DTL_V DTLS , AR_PAYMENT_SCHEDULES_ALL APS, OKL_STRM_TYPE_TL STYT, OKC_K_HEADERS_B CHR, OKL_XMLP_PARAMS XMLP WHERE XMLP.BATCH_ID = :BATCH_ID AND DTLS.LSM_ID = XMLP.PARAM_VALUE AND DTLS.KLE_ID IS NULL AND DTLS.CHR_ID = CHR.ID AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID AND DTLS.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') UNION SELECT DTLS.LSM_ID LSM_ID , NULL CONTRACT_NUMBER , NULL CONTRACT_STATUS , NULL CONTRACT_EFFECTIVE_FROM , NULL CONTRACT_EFFECTIVE_TO , NULL CONTRACT_CURRENCY_CODE , STYT.NAME TRANSACTION_TYPE , DTLS.INVOICE_NUMBER INVOICE_NUMBER , DTLS.INVOICE_AMOUNT INVOICE_AMOUNT , DTLS.LINE_IDENTIFIER LINE_IDENTIFIER , NULL ASSET_NUMBER , DTLS.INVOICE_DATE INVOICE_DATE , DTLS.DUE_DATE DUE_DATE , DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT , DTLS.AMOUNT_APPLIED AMOUNT_APPLIED , DTLS.AMOUNT_REMAINING AMOUNT_REMAINING , DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID , TO_NUMBER(NULL) ORG_ID , TO_NUMBER(NULL) KHR_ID , TO_NUMBER(NULL) KLE_ID , TO_NUMBER(NULL) ASSET_ID , DTLS.INVOICE_ID INVOICE_ID , STYT.ID STY_ID , DTLS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE , APS.EXCHANGE_RATE AR_EXCHANGE_RATE , APS.EXCHANGE_DATE AR_EXCHANGE_DATE , APS.TRX_DATE AR_TRX_DATE , DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL , DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING, DTLS.TAX_AMOUNT TAX_ORIGINAL , DTLS.TAX_REMAINING TAX_REMAINING , DTLS.AMOUNT_CREDITED AMOUNT_CREDITED , DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID , DTLS.INVOICE_LINE_ID INVOICE_LINE_ID , DTLS.PAYMENT_TERM PAYMENT_TERM , DTLS.INVOICE_TYPE INVOICE_TYPE FROM OKL_CS_BPD_INV_DTL_V DTLS , AR_PAYMENT_SCHEDULES_ALL APS, OKL_STRM_TYPE_TL STYT, OKL_XMLP_PARAMS XMLP WHERE XMLP.BATCH_ID = :BATCH_ID AND DTLS.LSM_ID = XMLP.PARAM_VALUE AND DTLS.CHR_ID IS NULL AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID AND DTLS.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') UNION SELECT DTLS.LSM_ID LSM_ID , DTLS.CONTRACT_NUMBER CONTRACT_NUMBER , CHR.STS_CODE CONTRACT_STATUS , CHR.START_DATE CONTRACT_EFFECTIVE_FROM , CHR.END_DATE CONTRACT_EFFECTIVE_TO , CHR.CURRENCY_CODE CONTRACT_CURRENCY_CODE , STYT.NAME TRANSACTION_TYPE , DTLS.INVOICE_NUMBER INVOICE_NUMBER , DTLS.INVOICE_AMOUNT INVOICE_AMOUNT , DTLS.LINE_IDENTIFIER LINE_IDENTIFIER , CLT.NAME ASSET_NUMBER , DTLS.INVOICE_DATE INVOICE_DATE , DTLS.DUE_DATE DUE_DATE , DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT , DTLS.AMOUNT_APPLIED AMOUNT_APPLIED , DTLS.AMOUNT_REMAINING AMOUNT_REMAINING , DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID , DTLS.ORG_ID ORG_ID , DTLS.CHR_ID KHR_ID , DTLS.KLE_ID KLE_ID , TO_NUMBER(NULL) ASSET_ID , DTLS.INVOICE_ID INVOICE_ID , STYT.ID STY_ID , DTLS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE , APS.EXCHANGE_RATE AR_EXCHANGE_RATE , APS.EXCHANGE_DATE AR_EXCHANGE_DATE , APS.TRX_DATE AR_TRX_DATE , DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL , DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING, DTLS.TAX_AMOUNT TAX_ORIGINAL , DTLS.TAX_REMAINING TAX_REMAINING , DTLS.AMOUNT_CREDITED AMOUNT_CREDITED , DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID , DTLS.INVOICE_LINE_ID INVOICE_LINE_ID , DTLS.PAYMENT_TERM PAYMENT_TERM , DTLS.INVOICE_TYPE INVOICE_TYPE FROM OKL_CS_BPD_INV_DTL_V DTLS , AR_PAYMENT_SCHEDULES_ALL APS, OKL_STRM_TYPE_TL STYT , OKL_K_HEADERS CHL , OKC_K_LINES_B CLE , OKC_LINE_STYLES_B LSE , OKC_K_LINES_TL CLT , OKC_K_HEADERS_B CHR , OKL_XMLP_PARAMS XMLP WHERE XMLP.BATCH_ID = :BATCH_ID AND DTLS.LSM_ID = XMLP.PARAM_VALUE AND DTLS.KLE_ID IS NOT NULL AND DTLS.CHR_ID = CHR.ID AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID AND DTLS.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') AND DTLS.CHR_ID = CHL.ID AND CHL.DEAL_TYPE = 'LOAN' AND DTLS.KLE_ID = CLE.ID AND CLE.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FREE_FORM1' AND CLE.ID = CLT.ID AND CLT.LANGUAGE = USERENV('LANG') |