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 |