VIEW: APPS.OKL_BILLING_TRX_ALL_UV

Source

View Text - Preformatted

SELECT LSM.ID LSM_ID, CHR.CONTRACT_NUMBER CONTRACT_NUMBER, STYT.NAME TRANSACTION_TYPE, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT, APS.TRX_NUMBER AR_INVOICE_NUMBER, FAA.ASSET_NUMBER ASSET_NUMBER, APS.TRX_DATE INVOICE_DATE, CNR.DUE_DATE DUE_DATE, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT, APS.AMOUNT_APPLIED AMOUNT_APPLIED, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING, CNR.IXX_ID CUSTOMER_ACCOUNT_ID, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID, LSM.KHR_ID KHR_ID, LSM.KLE_ID KLE_ID, FAA.ASSET_ID ASSET_ID, CNR.ID CNR_ID, STYT.ID STY_ID, APS.INVOICE_CURRENCY_CODE 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, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING, APS.TAX_ORIGINAL TAX_ORIGINAL, APS.TAX_REMAINING TAX_REMAINING, APS.AMOUNT_CREDITED AMOUNT_CREDITED, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM AR_PAYMENT_SCHEDULES_ALL APS, OKL_CNSLD_AR_STRMS_B LSM, OKL_CNSLD_AR_LINES_B LLN, OKL_CNSLD_AR_HDRS_ALL_B CNR, OKL_STRM_TYPE_TL STYT, FA_ADDITIONS_B FAA, OKC_K_ITEMS CIM, OKC_LINE_STYLES_B LSE, OKC_K_LINES_B CLE, OKC_K_HEADERS_ALL_B CHR , OKL_K_HEADERS CHL WHERE 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 LSM.KHR_ID = CHR.ID AND CHR.ID = CHL.ID AND CHL.DEAL_TYPE <> 'LOAN' AND LSM.KLE_ID IS NOT NULL AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID AND LSM.LLN_ID = LLN.ID AND LLN.CNR_ID = CNR.ID AND LSM.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') AND LSM.KLE_ID = CLE.CLE_ID UNION SELECT LSM.ID LSM_ID, CHR.CONTRACT_NUMBER CONTRACT_NUMBER, STYT.NAME TRANSACTION_TYPE, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT, APS.TRX_NUMBER AR_INVOICE_NUMBER, OKL_CS_LC_CONTRACT_PVT.get_asset_number(LSM.KLE_ID) ASSET_NUMBER, APS.TRX_DATE INVOICE_DATE, CNR.DUE_DATE DUE_DATE, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT, APS.AMOUNT_APPLIED AMOUNT_APPLIED, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING, CNR.IXX_ID CUSTOMER_ACCOUNT_ID, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID, LSM.KHR_ID KHR_ID, LSM.KLE_ID KLE_ID, TO_NUMBER(NULL) ASSET_ID, CNR.ID CNR_ID, STYT.ID STY_ID, APS.INVOICE_CURRENCY_CODE 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, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING, APS.TAX_ORIGINAL TAX_ORIGINAL, APS.TAX_REMAINING TAX_REMAINING, APS.AMOUNT_CREDITED AMOUNT_CREDITED, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM AR_PAYMENT_SCHEDULES_ALL APS, OKL_CNSLD_AR_STRMS_B LSM, OKL_CNSLD_AR_LINES_B LLN, OKL_CNSLD_AR_HDRS_ALL_B CNR, OKL_STRM_TYPE_TL STYT, OKC_K_HEADERS_ALL_B CHR WHERE LSM.KLE_ID IS NOT NULL AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID AND LSM.LLN_ID = LLN.ID AND LLN.CNR_ID = CNR.ID AND LSM.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') AND LSM.KHR_ID = CHR.ID AND NOT EXISTS (SELECT 1 FROM OKC_K_LINES_B CLE2, OKC_LINE_STYLES_B LSE WHERE CLE2.CLE_ID = LSM.KLE_ID AND CLE2.LSE_ID = LSE.ID AND LSE.LTY_CODE = 'FIXED_ASSET') UNION SELECT LSM.ID LSM_ID, CHR.CONTRACT_NUMBER CONTRACT_NUMBER, STYT.NAME TRANSACTION_TYPE, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT, APS.TRX_NUMBER AR_INVOICE_NUMBER, OKL_CS_LC_CONTRACT_PVT.get_asset_number(LSM.KLE_ID) ASSET_NUMBER, APS.TRX_DATE INVOICE_DATE, CNR.DUE_DATE DUE_DATE, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT, APS.AMOUNT_APPLIED AMOUNT_APPLIED, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING, CNR.IXX_ID CUSTOMER_ACCOUNT_ID, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID, LSM.KHR_ID KHR_ID, TO_NUMBER(NULL) KLE_ID, TO_NUMBER(NULL) ASSET_ID, CNR.ID CNR_ID, STYT.ID STY_ID, APS.INVOICE_CURRENCY_CODE 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, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING, APS.TAX_ORIGINAL TAX_ORIGINAL, APS.TAX_REMAINING TAX_REMAINING, APS.AMOUNT_CREDITED AMOUNT_CREDITED, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM AR_PAYMENT_SCHEDULES_ALL APS, OKL_CNSLD_AR_STRMS_B LSM, OKL_CNSLD_AR_LINES_B LLN, OKL_CNSLD_AR_HDRS_ALL_B CNR, OKC_K_HEADERS_ALL_B CHR, OKL_STRM_TYPE_TL STYT WHERE LSM.KLE_ID IS NULL AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID AND LSM.LLN_ID = LLN.ID AND LSM.KHR_ID = CHR.ID AND LLN.CNR_ID = CNR.ID AND LSM.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') UNION SELECT LSM.ID LSM_ID, NULL CONTRACT_NUMBER, STYT.NAME TRANSACTION_TYPE, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT, APS.TRX_NUMBER AR_INVOICE_NUMBER, NULL ASSET_NUMBER, APS.TRX_DATE INVOICE_DATE, CNR.DUE_DATE DUE_DATE, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT, APS.AMOUNT_APPLIED AMOUNT_APPLIED, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING, CNR.IXX_ID CUSTOMER_ACCOUNT_ID, TO_NUMBER(NULL) CONTRACT_AUTHORING_ORG_ID, TO_NUMBER(NULL) KHR_ID, TO_NUMBER(NULL) KLE_ID, TO_NUMBER(NULL) ASSET_ID, CNR.ID CNR_ID, STYT.ID STY_ID, APS.INVOICE_CURRENCY_CODE 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, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING, APS.TAX_ORIGINAL TAX_ORIGINAL, APS.TAX_REMAINING TAX_REMAINING, APS.AMOUNT_CREDITED AMOUNT_CREDITED, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM AR_PAYMENT_SCHEDULES_ALL APS, OKL_CNSLD_AR_STRMS_B LSM, OKL_CNSLD_AR_LINES_B LLN, OKL_CNSLD_AR_HDRS_ALL_B CNR, OKL_STRM_TYPE_TL STYT WHERE LSM.KHR_ID IS NULL AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID AND LSM.LLN_ID = LLN.ID AND LLN.CNR_ID = CNR.ID AND LSM.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') UNION SELECT LSM.ID LSM_ID, CHR.CONTRACT_NUMBER CONTRACT_NUMBER, STYT.NAME TRANSACTION_TYPE, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT, APS.TRX_NUMBER AR_INVOICE_NUMBER, CLT.NAME ASSET_NUMBER, APS.TRX_DATE INVOICE_DATE, CNR.DUE_DATE DUE_DATE, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT, APS.AMOUNT_APPLIED AMOUNT_APPLIED, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING, CNR.IXX_ID CUSTOMER_ACCOUNT_ID, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID, LSM.KHR_ID KHR_ID, LSM.KLE_ID KLE_ID, TO_NUMBER(NULL) ASSET_ID, CNR.ID CNR_ID, STYT.ID STY_ID, APS.INVOICE_CURRENCY_CODE 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, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING, APS.TAX_ORIGINAL TAX_ORIGINAL, APS.TAX_REMAINING TAX_REMAINING, APS.AMOUNT_CREDITED AMOUNT_CREDITED, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM AR_PAYMENT_SCHEDULES_ALL APS, OKL_CNSLD_AR_STRMS_B LSM, OKL_CNSLD_AR_LINES_B LLN, OKL_CNSLD_AR_HDRS_ALL_B CNR, OKL_STRM_TYPE_TL STYT, OKC_K_HEADERS_ALL_B CHR, OKL_K_HEADERS CHL, OKC_K_LINES_B CLE, OKC_LINE_STYLES_B LSE, OKC_K_LINES_TL CLT WHERE LSM.KLE_ID IS NOT NULL AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID AND LSM.LLN_ID = LLN.ID AND LLN.CNR_ID = CNR.ID AND LSM.STY_ID = STYT.ID AND STYT.LANGUAGE = USERENV('LANG') AND LSM.KHR_ID = CHR.ID AND CHR.ID = CHL.ID AND CHL.DEAL_TYPE = 'LOAN' AND LSM.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')

View Text - HTML Formatted

SELECT LSM.ID LSM_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT
, APS.TRX_NUMBER AR_INVOICE_NUMBER
, FAA.ASSET_NUMBER ASSET_NUMBER
, APS.TRX_DATE INVOICE_DATE
, CNR.DUE_DATE DUE_DATE
, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT
, APS.AMOUNT_APPLIED AMOUNT_APPLIED
, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING
, CNR.IXX_ID CUSTOMER_ACCOUNT_ID
, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID
, LSM.KHR_ID KHR_ID
, LSM.KLE_ID KLE_ID
, FAA.ASSET_ID ASSET_ID
, CNR.ID CNR_ID
, STYT.ID STY_ID
, APS.INVOICE_CURRENCY_CODE 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
, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, APS.TAX_ORIGINAL TAX_ORIGINAL
, APS.TAX_REMAINING TAX_REMAINING
, APS.AMOUNT_CREDITED AMOUNT_CREDITED
, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_CNSLD_AR_STRMS_B LSM
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_HDRS_ALL_B CNR
, OKL_STRM_TYPE_TL STYT
, FA_ADDITIONS_B FAA
, OKC_K_ITEMS CIM
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_B CLE
, OKC_K_HEADERS_ALL_B CHR
, OKL_K_HEADERS CHL
WHERE 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 LSM.KHR_ID = CHR.ID
AND CHR.ID = CHL.ID
AND CHL.DEAL_TYPE <> 'LOAN'
AND LSM.KLE_ID IS NOT NULL
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
AND LSM.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND LSM.KLE_ID = CLE.CLE_ID UNION SELECT LSM.ID LSM_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT
, APS.TRX_NUMBER AR_INVOICE_NUMBER
, OKL_CS_LC_CONTRACT_PVT.GET_ASSET_NUMBER(LSM.KLE_ID) ASSET_NUMBER
, APS.TRX_DATE INVOICE_DATE
, CNR.DUE_DATE DUE_DATE
, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT
, APS.AMOUNT_APPLIED AMOUNT_APPLIED
, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING
, CNR.IXX_ID CUSTOMER_ACCOUNT_ID
, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID
, LSM.KHR_ID KHR_ID
, LSM.KLE_ID KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, CNR.ID CNR_ID
, STYT.ID STY_ID
, APS.INVOICE_CURRENCY_CODE 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
, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, APS.TAX_ORIGINAL TAX_ORIGINAL
, APS.TAX_REMAINING TAX_REMAINING
, APS.AMOUNT_CREDITED AMOUNT_CREDITED
, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_CNSLD_AR_STRMS_B LSM
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_HDRS_ALL_B CNR
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_ALL_B CHR
WHERE LSM.KLE_ID IS NOT NULL
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
AND LSM.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND LSM.KHR_ID = CHR.ID
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE
WHERE CLE2.CLE_ID = LSM.KLE_ID
AND CLE2.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET') UNION SELECT LSM.ID LSM_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT
, APS.TRX_NUMBER AR_INVOICE_NUMBER
, OKL_CS_LC_CONTRACT_PVT.GET_ASSET_NUMBER(LSM.KLE_ID) ASSET_NUMBER
, APS.TRX_DATE INVOICE_DATE
, CNR.DUE_DATE DUE_DATE
, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT
, APS.AMOUNT_APPLIED AMOUNT_APPLIED
, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING
, CNR.IXX_ID CUSTOMER_ACCOUNT_ID
, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID
, LSM.KHR_ID KHR_ID
, TO_NUMBER(NULL) KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, CNR.ID CNR_ID
, STYT.ID STY_ID
, APS.INVOICE_CURRENCY_CODE 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
, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, APS.TAX_ORIGINAL TAX_ORIGINAL
, APS.TAX_REMAINING TAX_REMAINING
, APS.AMOUNT_CREDITED AMOUNT_CREDITED
, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_CNSLD_AR_STRMS_B LSM
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_HDRS_ALL_B CNR
, OKC_K_HEADERS_ALL_B CHR
, OKL_STRM_TYPE_TL STYT
WHERE LSM.KLE_ID IS NULL
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LSM.KHR_ID = CHR.ID
AND LLN.CNR_ID = CNR.ID
AND LSM.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG') UNION SELECT LSM.ID LSM_ID
, NULL CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT
, APS.TRX_NUMBER AR_INVOICE_NUMBER
, NULL ASSET_NUMBER
, APS.TRX_DATE INVOICE_DATE
, CNR.DUE_DATE DUE_DATE
, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT
, APS.AMOUNT_APPLIED AMOUNT_APPLIED
, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING
, CNR.IXX_ID CUSTOMER_ACCOUNT_ID
, TO_NUMBER(NULL) CONTRACT_AUTHORING_ORG_ID
, TO_NUMBER(NULL) KHR_ID
, TO_NUMBER(NULL) KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, CNR.ID CNR_ID
, STYT.ID STY_ID
, APS.INVOICE_CURRENCY_CODE 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
, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, APS.TAX_ORIGINAL TAX_ORIGINAL
, APS.TAX_REMAINING TAX_REMAINING
, APS.AMOUNT_CREDITED AMOUNT_CREDITED
, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_CNSLD_AR_STRMS_B LSM
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_HDRS_ALL_B CNR
, OKL_STRM_TYPE_TL STYT
WHERE LSM.KHR_ID IS NULL
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
AND LSM.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG') UNION SELECT LSM.ID LSM_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, CNR.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, CNR.AMOUNT CONSOLIDATED_INVOICE_AMOUNT
, APS.TRX_NUMBER AR_INVOICE_NUMBER
, CLT.NAME ASSET_NUMBER
, APS.TRX_DATE INVOICE_DATE
, CNR.DUE_DATE DUE_DATE
, APS.AMOUNT_DUE_ORIGINAL TRANSACTION_AMOUNT
, APS.AMOUNT_APPLIED AMOUNT_APPLIED
, APS.AMOUNT_DUE_REMAINING AMOUNT_REMAINING
, CNR.IXX_ID CUSTOMER_ACCOUNT_ID
, CHR.AUTHORING_ORG_ID CONTRACT_AUTHORING_ORG_ID
, LSM.KHR_ID KHR_ID
, LSM.KLE_ID KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, CNR.ID CNR_ID
, STYT.ID STY_ID
, APS.INVOICE_CURRENCY_CODE 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
, APS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, APS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, APS.TAX_ORIGINAL TAX_ORIGINAL
, APS.TAX_REMAINING TAX_REMAINING
, APS.AMOUNT_CREDITED AMOUNT_CREDITED
, APS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_CNSLD_AR_STRMS_B LSM
, OKL_CNSLD_AR_LINES_B LLN
, OKL_CNSLD_AR_HDRS_ALL_B CNR
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_ALL_B CHR
, OKL_K_HEADERS CHL
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
WHERE LSM.KLE_ID IS NOT NULL
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
AND LSM.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND LSM.KHR_ID = CHR.ID
AND CHR.ID = CHL.ID
AND CHL.DEAL_TYPE = 'LOAN'
AND LSM.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')