OKL Billing Reconciliation

➜ Back to Library
Description
Categories: BI Publisher
Application: Lease and Finance Management
Source: Billing Reconciliation Report
Short Name: OKL_BILLING_RECON_RPT
DB package: 
            SELECT
            NVL(count(*),0) AR_INVS_CNT,
            SUM(NVL(line.EXTENDED_AMOUNT,0)) NUM_AR_INVS_AMT,
            hdr.invoice_currency_code AR_INVS_CURR_CODE,
            hdr.INTERFACE_HEADER_ATTRIBUTE6 AR_INVS_CONTRACT_NUM,
            (CASE
                WHEN :INV_CURR_PRECISION = 0 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,999')
                WHEN :INV_CURR_PRECISION > 2 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'999,999,999,990.999')
                ELSE TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,990.99')
                END) AR_INVS_AMT
            FROM ra_customer_trx_all   hdr,
                 ra_customer_trx_lines_all line,
                 ra_batch_sources_all   batch,
                 RA_CUST_TRX_TYPES_ALL trx_type
            WHERE  line.customer_trx_id = hdr.customer_trx_id
            AND hdr.batch_source_id = batch.batch_source_id
            AND batch.name = 'OKL_CONTRACTS'
            AND line.line_type = 'LINE'
            AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
            AND hdr.INTERFACE_HEADER_ATTRIBUTE6 =
               NVL(rtrim(ltrim( :KHR_CONTRACT_NUMBER)),hdr.INTERFACE_HEADER_ATTRIBUTE6)
            AND hdr.trx_date > = NVL( :P_FROM_BILL_DATE , hdr.trx_date )
            AND hdr.trx_date < = NVL( :P_TO_BILL_DATE , hdr.trx_date)
            AND hdr.invoice_currency_code = :INV_CURRENCY_CODE
	    AND batch.org_id = :ORG_ID
	    AND trx_type.org_id = :ORG_ID
            GROUP BY
                hdr.invoice_currency_code,
                hdr.INTERFACE_HEADER_ATTRIBUTE6
            UNION 
   SELECT nvl(max(AR_INVS_CNT),0) AR_INVS_CNT,
       nvl(max(NUM_AR_INVS_AMT),0) NUM_AR_INVS_AMT, 
       nvl(max(AR_INVS_CURR_CODE),'') AR_INVS_CURR_CODE,
       nvl(max(AR_INVS_CONTRACT_NUM),'') AR_INVS_CONTRACT_NUM,
       nvl(max(AR_INVS_AMT),'') AR_INVS_AMT 
   from
    (SELECT
            NVL(count(*),0) AR_INVS_CNT,
            SUM(NVL(line.EXTENDED_AMOUNT,0)) NUM_AR_INVS_AMT,
            hdr.invoice_currency_code AR_INVS_CURR_CODE,
            hdr.INTERFACE_HEADER_ATTRIBUTE6 AR_INVS_CONTRACT_NUM,
            (CASE
                WHEN :INV_CURR_PRECISION = 0 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,999')
                WHEN :INV_CURR_PRECISION > 2 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'999,999,999,990.999')
                ELSE TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,990.99')
                END) AR_INVS_AMT
            FROM ra_customer_trx_all   hdr,
                 ra_customer_trx_lines_all line,
                 ra_batch_sources_all   batch,
                 RA_CUST_TRX_TYPES_ALL trx_type
            WHERE  line.customer_trx_id = hdr.customer_trx_id
            AND hdr.batch_source_id = batch.batch_source_id
            AND batch.name = 'OKL_CONTRACTS'
            AND line.line_type = 'LINE'
            AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
            AND hdr.INTERFACE_HEADER_ATTRIBUTE6 =
               NVL(rtrim(ltrim( :KHR_CONTRACT_NUMBER)),hdr.INTERFACE_HEADER_ATTRIBUTE6)
            AND hdr.trx_date > = NVL( :P_FROM_BILL_DATE , hdr.trx_date )
            AND hdr.trx_date < = NVL( :P_TO_BILL_DATE , hdr.trx_date)
            AND hdr.invoice_currency_code = :INV_CURRENCY_CODE
			AND batch.org_id = :ORG_ID
			AND trx_type.org_id = :ORG_ID
            GROUP BY
                hdr.invoice_currency_code,
                hdr.INTERFACE_HEADER_ATTRIBUTE6)
            ORDER BY 3
Parameter Name SQL text Validation
From Date 
 
 Date
To Date 
 
 Date
Contract Number 
 
 LOV Oracle
Download
Related Blitz Reports
AR Transactions and Lines RK: AR Transactions and Lines GL Account Analysis (Distributions) 11g GL Account Analysis (Drilldown) GL Account Analysis (Drilldown) (with inventory and WIP) AR Incomplete Transactions Avalara VAT listing EOS GL Account Analysis with Tax AR Transaction Batch Source Listing
Blitz Report™

Blitz Report™ provides multiple benefits: