OKL Billing Inquiry Data

Description
Categories: BI Publisher, Financials
Application: Lease and Finance Management
Source:
Short Name: OKLBILINQDAT
DB package:
  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')
Ask a question