DevOps, CI/CD and Automation

Hi ,

in my rdf ..my main query is..
select
rcta.trx_number invoice_number
,hp.party_name party_name
,rcta.trx_date invoice_date
,rcta.term_due_date invoice_due_date
,rcta.invoice_currency_code transaction_currency
,rctt.name transaction_type
,acr.receipt_number Receipt_number
,acr.receipt_date receipt_date
,NVL(acr.amount,0) Received_amount
,SUM(NVL(RCTL.EXTENDED_AMOUNT,0)) INVOICE_AMT
,sum(NVL(rctl.amount_due_remaining,rctl.extended_amount)) due_amt
,sum(NVL(rctl.extended_amount,0)* decode(gb.currency_code,rcta.invoice_currency_code,1,
(select gl.conversion_rate
from gl_daily_rates gl
where gl.from_currency=rcta.invoice_currency_code
and gl.to_currency=gb.currency_code
and gl.conversion_date=rcta.trx_date))) func_inv_amt
,sum(NVL(rctl.amount_due_remaining,rctl.extended_amount)* decode(gb.currency_code,rcta.invoice_currency_code,1,
(select gl.conversion_rate
from gl_daily_rates gl
where gl.from_currency=rcta.invoice_currency_code
and gl.to_currency=gb.currency_code
and gl.conversion_date=rcta.trx_date))) func_amt_due
,NVL(acr.amount*NVL(acr.exchange_rate,1) ,0) func_rcv_amt
,hps.party_site_number site_num
, hca.account_number account_num
,hl.address1,hl.address2,hl.city,hl.postal_code,hl.state,hl.country
,HCA.CUST_ACCOUNT_ID
,xep.NAME legal_entity
,DECODE(ACRHA.STATUS,'CONFIRMED','Yes','REMITTED','Yes') UNCLR_STATUS
from
ra_customer_trx_all rcta, hz_cust_site_uses_all hcsua,hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa, hz_party_sites hps
,ar_cash_receipt_history_all acrha
,hz_customer_profiles hcp,
ra_cust_trx_types_all rctt
,hr_operating_units hou
,ar_collectors ac
,ra_salesreps_all rsa
, ar_lookups al
,ra_customer_trx_lines_all rctl
,xle_entity_profiles xep
,ar_cash_receipts_all acr
,hz_locations hl
,hz_parties hp
,gl_sets_of_books gb
,ar_payment_schedules_all apsa
,ar_payment_schedules_all apsa1
,AR_RECEIVABLE_APPLICATIONS_ALL ARAA
where HCA.CUST_ACCOUNT_ID = RCTA.SOLD_TO_CUSTOMER_ID
and RCTA.BILL_TO_SITE_USE_ID = HCSUA.SITE_USE_ID
and HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID
and rcta.customer_trx_id = rctl.customer_trx_id
and hcasa.party_site_id = hps.party_site_id
and hca.cust_account_id =hcp.cust_account_id(+)
and HCP.COLLECTOR_ID= AC.COLLECTOR_ID (+)
AND hou.organization_id (+)= rcta.org_id
and HCA.PARTY_ID = HP.PARTY_ID
and RCTA.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
and RCTA.ORG_ID = RCTT.ORG_ID
and RCTA.PRIMARY_SALESREP_ID=RSA.SALESREP_ID(+)
and hca.customer_type=al.lookup_code
and al.lookup_type='CUSTOMER_TYPE'
and al.enabled_flag = 'Y'
AND hou.default_legal_context_id=xep.legal_entity_id
and sysdate between NVL(AL.START_DATE_ACTIVE,sysdate) and NVL(AL.END_DATE_ACTIVE,sysdate)
and HL.LOCATION_ID = HPS.LOCATION_ID
and RCTA.SET_OF_BOOKS_ID=GB.SET_OF_BOOKS_ID
/*AND xep.NAME =NVL(:p_legal_entity,xep.NAME)
AND hou.NAME BETWEEN NVL(:p_oper_unit_low,hou.NAME) AND NVL(:p_oper_unit_high,hou.NAME)
AND TRUNC(rcta.trx_date) <= TRUNC(TO_DATE(TO_CHAR(:p_as_of_date,'RRRR/MM/DD HH24:MI:SS'),'RRRR/MM/DD HH24:MI:SS'))
AND DECODE(:p_sales_rep_low,NULL,1,rsa.name) BETWEEN DECODE(:p_sales_rep_low,NULL, 1,:p_sales_rep_low) AND DECODE(:p_sales_rep_high,NULL, 1,:p_sales_rep_high)
and hp.party_name between NVL(:p_cust_name_low, hp.party_name) and NVL(:p_cust_name_high,hp.party_name)
AND hp.party_number between NVL(:p_cust_num_low, hp.party_number) and NVL(:p_cust_num_high, hp.party_number)
AND hca.account_number between NVL(:p_cust_accnt_low, hca.account_number) and NVL(:p_cust_accnt_high, hca.account_number)
AND hps.party_site_number BETWEEN NVL(:p_cust_accnt_site_low, hps.party_site_number) and NVL(:p_cust_accnt_site_high, hps.party_site_number)
AND rctt.name=NVL(:p_transaction_class,rctt.name)
AND hca.customer_class_code = NVL(:p_classification, hca.customer_class_code)
AND ac.name BETWEEN NVL(:p_collector_name_low,ac.name) and NVL(:p_collector_name_high,ac.name)
AND al.meaning=NVL(:p_account_type,al.meaning)*/
and RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID(+)
and APSA1.CASH_RECEIPT_ID(+)=ACR.CASH_RECEIPT_ID
and ACRHA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID(+)
and ACRHA.CASH_RECEIPT_HISTORY_ID(+) = ARAA.CASH_RECEIPT_HISTORY_ID
and ACRHA.CASH_RECEIPT_ID(+) = ARAA.CASH_RECEIPT_ID
and RCTA.CUSTOMER_TRX_ID = ARAA.APPLIED_CUSTOMER_TRX_ID(+)
--&CP_UNC_RECPT
GROUP BY rcta.trx_number
,rcta.trx_date
,rcta.term_due_date
,rcta.invoice_currency_code
,rctt.name
,acr.receipt_number
,acr.receipt_date
,acr.amount
,hps.party_site_number
, hca.account_number
,hl.address1,hl.address2,hl.city,hl.postal_code,hl.state,hl.country,hca.cust_account_id,hp.party_name
,acr.amount*NVL(acr.exchange_rate,1)
,xep.NAME
,DECODE(ACRHA.STATUS,'CONFIRMED','Yes','REMITTED','Yes')
order by RCTA.TRX_NUMBER;

I am using a summary column to sum up the due_amt at currency level.
My requirement is ..i need to sum up due_amt only for distinct values of invoice number