Open Closed Claim Detail Report

with business_unit as (
						SELECT distinct bu.bu_id bu_id,bu.bu_name
						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
						WHERE 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 bu.bu_name = nvl(:p_bu,bu.bu_name)
						--and (bu.bu_name IN (:p_bu) OR 'All' in (:p_bu||'All'))
						and (bu.bu_id IN (:p_org_id)OR  '1' IN (:p_org_id||'1'))
) 
SELECT DISTINCT 
	
	--araa.application_ref_num claim_number  --1
	cca.CLAIM_NUMBER claim_number
	--,to_number(araa.amount_applied) acctd_amount, --applied_amount
    ,cca.ACCTD_AMOUNT
	,nvl(cca.acctd_amount_remaining,0) acctd_amount_remaining
	,cca.CURRENCY_CODE
	,bu.bu_name  --5
	,cc.segment1 entity  --6
	--,(select DESCRIPTION from fnd_flex_values_vl c where 1=1 and c.flex_value = cc.segment1 and value_category ='CUSTOM_ENTITY_VALUE_SET' ) Entity_name
	,(SELECT 
		ffv.description
		FROM FND_ID_FLEX_SEGMENTS_VL ffs, fnd_flex_values_vl ffv 
		,gl_ledgers gl 
		,GL_LEDGER_NORM_SEG_VALS leseg
		,hr_operating_units hou
		WHERE 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 hou.organization_id =acra.org_id
		and ffv.flex_value =cc.segment1
	  )Entity_name
	,hca.account_number customer_num  --8
	,hp.party_name customer_name    -- 9
	,hcsu.location bill_to_location
	,arm.name  RECEIPT_METHOD
	,to_char(araa.gl_date,'MM/DD/YYYY')gl_date
	--,to_char(cca.CLAIM_DATE,'MM/DD/YYYY') claim_date  
	,to_char(acra.RECEIPT_DATE,'MM/DD/YYYY') claim_date
	--,to_char(araa.apply_date,'MM/DD/YYYY') claim_date
	--,cca.CLAIM_NUMBER
	,(	select  a.reason_code_name   
		from cjm_claim_reason_codes_tl a ,
		cjm_claim_reason_codes_b b
		where a.language ='US'
		and a.reason_code_id =b.reason_code_id
		and sysdate between b.start_date and nvl(b.end_date, sysdate+1)
		--and b.CLAIM_REASON_CODE =araa.application_ref_reason -- FUT call Dec-27-24
		and b.reason_code_id = cca.reason_code_id
		)application_ref_reason
	--,araa.customer_reference
	,cca.CUSTOMER_REF_NUMBER customer_reference
	,araa.customer_reason claim_reason
	--,to_char(araa.apply_date,'MM/DD/YYYY') Application_Date

	,to_char(cca.SETTLED_DATE,'MM/DD/YYYY') Application_Date

	/*,nvl( (select CLASS from ar_payment_schedules_all 
                    where  TRX_NUMBER not in ('Claim Investigation','Receipt Write-off','On Account')
                     and payment_schedule_id =araa.applied_payment_schedule_id )
    ,apsa.trx_number) APPLICATION_TYPE */  --  fut call 28
	,(select meaning from fnd_lookups where lookup_type ='ORA_CJM_SETTLEMENT_METHOD' and ENABLED_FLAG='Y' and lookup_code= ccpa.PAYMENT_METHOD_CODE
     and sysdate between START_DATE_ACTIVE and nvl(END_DATE_ACTIVE,sysdate))  APPLICATION_TYPE  ----  fut call 28
	/*,nvl(araa.application_ref_num,arta.name) Receivable_Activity*/   --  fut call 28
	--,arta.name Receivable_Activity
	--,araa.AMOUNT_APPLIED
	--,ccpa.PAYMENT_REFERENCE_NUMBER Receivable_Activity
	,( case when ccpa.PAYMENT_METHOD_CODE='WRITE_OFF'
	        then(select arta.name from ar_receivables_trx_all arta where arta.RECEIVABLES_TRX_ID =ccpa.WO_RECEIVABLES_TRX_ID and ccpa.BU_ID=arta.ORG_ID )
			ELSE
			    ccpa.PAYMENT_REFERENCE_NUMBER
		    END
	)Receivable_Activity
	--,cca.ACCTD_AMOUNT_SETTLED AMOUNT_APPLIED
	,csda.SETTLEMENT_ACCTD_AMOUNT AMOUNT_APPLIED
	,(select meaning from fnd_lookups where lookup_type ='ORA_CJM_CLAIM_STATUS' and ENABLED_FLAG='Y' and lookup_code= cca.status_code
     and sysdate between START_DATE_ACTIVE and nvl(END_DATE_ACTIVE,sysdate)) Claim_status
	 ,acra.receipt_number
	
   /* ,cca.acctd_amount_remaining		
	,araa.receivable_application_id
	,decode (csda.PAYMENT_METHOD_CODE, 'WRITE_OFF','Receipt Write-off',csda.SETTLEMENT_NUMBER ) settlement_number
	,decode (csda.SETTLEMENT_TYPE,'CHARGEBACK','Charge Back'
                                ,'WRITEOFF','Receipt write off'
                                ,'ADJUST','Adjustment'
                                ,'INV','Invoice'
								,'RECEIPT','Receipt'
								,'CM','Credit Memo'
								,'DM','Debit Memo'
								,csda.SETTLEMENT_TYPE) SETTLEMENT_TYPE
	,csda.SETTLEMENT_ACCTD_AMOUNT
	,'CLAIM' TRANSACTION_TYPE1 */
	
FROM	
    ar_receivable_applications_all araa,
	ar_cash_receipts_all acra,
	ar_receipt_methods arm,
	hz_parties hp,
	hz_cust_accounts hca,
	hz_cust_site_uses_all  hcsu,
	ar_receivables_trx_all arta,
	cjm_claims_all cca,
	gl_code_combinations cc,
	business_unit bu,
	cjm_settlement_docs_all csda,
	CJM_CLAIM_PAYMENTS_ALL ccpa
	--ar_payment_schedules_all apsa
WHERE 	 1=1   
--AND araa.display = 'Y'
AND araa.cash_receipt_id = acra.cash_receipt_id
and acra.receipt_method_id = arm.receipt_method_id
AND hca.party_id = hp.party_id
AND acra.customer_site_use_id = hcsu.site_use_id(+)   -- bill_to 
AND acra.pay_from_customer = hca.cust_account_id(+)
AND araa.RECEIVABLES_TRX_ID = arta.RECEIVABLES_TRX_ID (+)
--and arta.TYPE ='CLAIM_INVESTIGATION'
and acra.org_id=arta.ORG_ID(+)
and acra.org_id  = bu.bu_id 
--and nvl(araa.application_ref_reason,'x') = nvl(:p_clime_reason,nvl(araa.application_ref_reason,'x'))
and nvl(cca.REASON_CODE_ID,1)=nvl(:p_clime_reason,nvl(cca.REASON_CODE_ID,1))
--and cca.claim_id = araa.application_ref_id
--and cca.claim_number =araa.APPLICATION_REF_NUM
and cca.RECEIVABLE_APPLICATION_ID =araa.RECEIVABLE_APPLICATION_ID
and cca.BU_ID = araa.ORG_ID
and (cca.status_code  in (:p_clime_status) or 'All' in (:p_clime_status||'All'))
and cca.claim_class_code = nvl(:p_claim_class,cca.claim_class_code)
and cca.claim_id = csda.claim_id(+)
and cca.BU_ID = csda.BU_ID(+)
and csda.claim_payment_id = ccpa.claim_payment_id(+)
and csda.BU_ID =ccpa.BU_ID(+)
and araa.code_combination_id = to_number(cc.code_combination_id)
and acra.org_id  = bu.bu_id
and (bu.bu_id  in (:p_org_id) or '1' in (:p_org_id||'1') )
and (cc.segment1 in (:p_entity ) or 'All' in ( :p_entity||'All') )
and (((araa.gl_date between NVL(:p_date_from , araa.gl_date) AND NVL(:p_date_to, araa.gl_date)) AND :p_run_by_date='GL Date')
			  or
			 (( acra.RECEIPT_DATE between NVL(:p_date_from , acra.RECEIPT_DATE) AND NVL(:p_date_to, acra.RECEIPT_DATE)) and :p_run_by_date='Claim Date'))

and (hca.account_number in (:p_cust_num ) or 'All' in ( :p_cust_num||'All') )

--and araa.APPLIED_PAYMENT_SCHEDULE_ID =apsa.PAYMENT_SCHEDULE_ID(+) 
--and apsa.CUSTOMER_TRX_ID = csda.SETTLEMENT_ID(+)
--and acra.RECEIPT_NUMBER  in ('11')
order by 5,6,9,8,1,13
-- order by  Business Unit, Legal Entity, Customer Name, Customer Number, Clime Number and Clime Date