OKL Billing Inquiry Data

  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')

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')