AR Transaction Register Summary and Detail Report

select * from (
with ar_pay_sche
As
( select a.gl_date,a.due_date, a.customer_trx_id , a.EXCHANGE_RATE ,a.org_id
  ,(select sum(amount_due_original) from ar_payment_schedules_all where customer_trx_id =a.customer_trx_id and org_id =a.org_id) amount
from  ar_payment_schedules_all a where a.TERMS_SEQUENCE_NUMBER =1  --w.r.t REF-271549
)
SELECT hou.name bu_name,
       gcc.segment1 legal_entity,
	   ffv.description Legal_Entity_Name,
	   hp.party_name customer_name,
	   rcta.trx_number doc_num,
	   (gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8||'.'||gcc.segment9) code_combinatations, 
	   rbsa.name trx_source,
	   rctt.name trx_type,
	   al.MEANING trx_class,
	   to_char(apsa.gl_date,'MM/DD/YYYY') gl_date,
	   to_char(rcta.trx_date,'MM/DD/YYYY') doc_date,
	   to_char(apsa.due_date,'MM/DD/YYYY')due_date,
	   hca.account_number customer_number,
	   nvl(rcta.exchange_rate,1) exchange_rate,
	   hcsu.location bill_to_location,
	   --apsa.amount_due_original entered_amount,
	   apsa.amount entered_amount,
	   rcta.invoice_currency_code entered_currency,
	  -- apsa.amount_due_original*nvl(rcta.exchange_rate,1) functional_amount,
	   apsa.amount* nvl(apsa.EXCHANGE_RATE,1) functional_amount,
	   sob.currency_code functional_currency,
	   rcta.customer_trx_id,
	   rcta.ATTRIBUTE1 GOVT_INVOICE, 
	   jctha.gta_trx_number GOLDEN_TAX,
       hcasa.ATTRIBUTE5	AKA_NAME,
       rcta.PURCHASE_ORDER PO_NUMBER,
	   rcta.ATTRIBUTE3 SHIP_TO,
	   rcta.ct_reference||(select '|'||ATTRIBUTE3||'|'||DESCRIPTION from FUN_TRX_BATCHES where BATCH_NUMBER=rcta.ct_reference ) KISSFLOW_TRX_NBR, --CR-271568
	   rcta.ATTRIBUTE15 VAT_RULES

from ra_customer_trx_all rcta,
      hr_operating_units  hou,
	 ra_batch_sources_all rbsa,
	 ra_cust_trx_types_all rctt,
	 --ar_payment_schedules_all apsa,
	 ar_pay_sche apsa,
	 hz_cust_accounts hca,
	 hz_parties hp,
	 hz_cust_site_uses_all  hcsu,
	 hz_cust_acct_sites_all  hcasa,
	 ra_cust_trx_line_gl_dist_all rctlg,
	 gl_code_combinations gcc,
	 gl_sets_of_books sob,
	 ar_lookups al,
	 FND_ID_FLEX_SEGMENTS_VL ffs,fnd_flex_values_vl ffv , gl_ledgers gl, GL_LEDGER_NORM_SEG_VALS leseg
	 ,ja_cn_trx_headers_all  jctha
	 
where 1=1
--and rcta.trx_number in ( '1475898','1474761')
and rcta.org_id = hou.ORGANIZATION_ID
and rcta.batch_source_seq_id = rbsa.batch_source_seq_id 
and rcta.cust_trx_type_seq_id = rctt.cust_trx_type_seq_id
and rcta.customer_trx_id = apsa.customer_trx_id
and apsa.org_id = hou.ORGANIZATION_ID
and hca.cust_account_id = rcta.bill_to_customer_id
and hp.party_id = hca.party_id
and rcta.bill_to_site_use_id = hcsu.site_use_id(+)
and hcsu.CUST_ACCT_SITE_ID =hcasa.CUST_ACCT_SITE_ID 
and rcta.customer_trx_id = rctlg.customer_trx_id 
and rctlg.code_combination_id = gcc.code_combination_id
and rctlg.account_class ='REC'
and rcta.org_id = rctlg.org_id
and rcta.set_of_books_id =sob.set_of_books_id
and al.LOOKUP_TYPE ='AR_ALL_DOC_CLASSES'
and al.ENABLED_FLAG='Y'
and al.LOOKUP_CODE = rcta.trx_class

and ffs.segment_name ='Entity' and ffs.enabled_flag='Y'
and ffs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID and ffv.ENABLED_FLAG ='Y'	
and gl.chart_of_accounts_id = ffs.ID_FLEX_NUM
and leseg.ledger_id= gl.ledger_id
and leseg.SEGMENT_VALUE = ffv.flex_value
and hou.SET_OF_BOOKS_ID  = gl.LEDGER_ID	
and ffv.flex_value = gcc.segment1
and  rcta.customer_trx_id = jctha.ra_trx_id(+) 
and rcta.org_id =jctha.org_id(+)

and (rcta.org_id IN (:p_org_id) OR  1 IN (:p_org_id||'1'))
and trunc(apsa.gl_date) between :p_gl_date_from and :p_gl_date_to
and (hca.account_number in (:p_cust_num) or '1' in (:p_cust_num||'1'))
and rctt.name = nvl(:p_name,rctt.name)
and rcta.invoice_currency_code = nvl(:p_invoice_currency_code,rcta.invoice_currency_code)
--and gcc.segment1 = nvl(:p_segment1,gcc.segment1)
-- Data Security
--AND rcta.org_id in (
and EXISTS(
SELECT --distinct bu.bu_id
distinct role.org_id
FROM --fusion.fun_all_business_units_v bu,
fusion.fun_user_role_data_asgnmnts role
,fusion.per_users pu
,fusion.per_roles_dn_vl pr
,fusion.per_roles_dn_tl prtl
WHERE 1=1
--and role.org_id = bu.bu_id
AND pu.user_guid = role.user_guid
AND pr.role_common_name = role.ROLE_NAME
AND role.active_flag ='Y' 
AND pu.username = fnd_global.user_name
and pr.role_id=prtl.role_id
and prtl.LANGUAGE='US'
and prtl.ROLE_NAME  in  ('XXC Accounts Receivable Analyst'  ,'XXC Accounts Receivable Manager'  ,'XXC Receivables Inquiry' )
--and hou.NAME in (select MEANING from  FND_LOOKUP_VALUES_VL where lookup_type = 'BU_REGION_REPORTING' and enabled_flag ='Y' and DESCRIPTION =nvl(:p_region,DESCRIPTION)) -- REF-292555
and hou.NAME in ( select FLEX_VALUE from  fnd_flex_values_vl FLV where FLV.value_category='XXC_CLOUD_BU_TO_REGION_CODE_MAPPING' and  FLV.enabled_flag = 'Y' and FLV.attribute1 =nvl(:p_region,FLV.attribute1)) -- REF-292555
and (role.org_id IN (:p_org_id) OR  1 IN (:p_org_id||'1'))
and rcta.org_id = role.org_id 
)
)
where 1=1
and legal_entity = nvl(:p_segment1,legal_entity)
--order by hou.name ,ffv.description, hp.party_name
order by 1, 3,4,5