AR Applied and Unapplied Receipts Register Report

Select 
	  hra.Name Business_Unit
	, ccid.segment1 Company 
	, com_d.Description Company_Name	
	, hca.account_number Customer_No , hp.Party_Name Customer_Name
	, rec.Receipt_Number Receipt_No	
	, to_char(his.GL_Date,'MM/DD/YYYY') GL_Date
    , to_char(rec.Deposit_Date,'MM/DD/YYYY') Deposit_Date
    , to_char(rec.Receipt_Date,'MM/DD/YYYY') Receipt_Date
    , Case when his.Status = 'REVERSED' 	
	            then Round((his.Amount *(-1)), 2)	
	            else Round((his.Amount *(1)), 2) end Receipt_Amount
    , rec.Currency_Code Receipt_Currency
	, Case when his.Status = 'REVERSED' 	
	            then Round(((his.Amount *(-1)) * (Case when his.Exchange_Rate is null then 1 else his.Exchange_Rate end)), 2)	
	            else Round(((his.Amount *(1)) * (Case when his.Exchange_Rate is null then 1 else his.Exchange_Rate end)), 2) end Accounted_Receipt_Amount
    , sob.Currency_Code SoB_Currency	
    , nvl(abs(nvl(ar.amount_applied,0)+ar.amount_other_account),0) applied_amount
    , nvl(abs(ar.amount_on_account),0) on_account_amount
	, Case when his.Status = 'REVERSED' 
		        then 0
                  else nvl(round(his.Amount+nvl(ar.amount_applied,0)+ar.amount_other_account+ar.amount_on_account,2)*1,0) end un_app_amount	
--                                3000+				  
	, ar.Status AR_Schedule_Status 
	, his.Status History_Status
	, meth.Name Receipt_Method
	, rec.Type Receipt_Type	
	, rec.Status Receipt_Status
	, aba.name Batch_Number
	, fvvb.Attribute5 HRC_ORG_ID     	
	, ar.payment_schedule_id 
	, his.Cash_Receipt_History_ID
	, rec.Cash_Receipt_ID
	, his.Org_ID	
	, sob.CHART_OF_ACCOUNTS_ID
	, info.org_Information3
		
		
	From AR_Cash_Receipts_All rec
		
	     inner join HR_All_Organization_Units hra
	     on rec.Org_ID = hra.Organization_ID
		
	     left outer join AR_Cash_Receipt_History_All his
	     on his.Cash_Receipt_ID = rec.Cash_Receipt_ID
	     and his.Cash_Receipt_History_ID in (Select his_R.Cash_Receipt_History_ID	
	                                         from AR_Cash_Receipt_History_All his_R 	
	                                         where 1=1	
	                                           and his_R.Status = 'REVERSED'	
											  and his_R.GL_Date >=:p_from_date
											  and his_R.GL_Date <=:p_to_date
											  
	                                          and his_R.Cash_Receipt_ID = rec.Cash_Receipt_ID
		
	                                         Union All	
		
	                                         Select his_O.Cash_Receipt_History_ID	
	                                         from AR_Cash_Receipt_History_All his_O 	
	                                         where 1=1	
	                                         and (Select GL_Date 	
	                                              from AR_Cash_Receipt_History_All his_F 	
	                                              where 1=1 	
	                                                and First_Posted_Record_Flag = 'Y'	
	                                                and his_F.Cash_Receipt_ID = rec.Cash_Receipt_ID
                                              ) between	:p_from_date and :p_to_date 										 
	                                         and his_O.Cash_Receipt_History_ID = (Select Max(his_max.Cash_Receipt_History_ID)	
	                                                                              from AR_Cash_Receipt_History_All his_max 	
	                                                                              where 1=1	
	                                                                                and his_max.Status <> 'REVERSED'	
	                                                                                and his_max.GL_Date >=:p_from_date
                                                                                      and his_max.GL_Date <=:p_to_date																					  
	                                                                                and his_max.Cash_Receipt_ID = rec.Cash_Receipt_ID
	                                                                             )	
	                                          ) 	
	     	
	     left outer join AR_Payment_Schedules_All ar	
	     on rec.Cash_Receipt_ID = ar.Cash_Receipt_ID
		 and ar.org_id =rec.org_id
	     	
	     left outer join HZ_Cust_Accounts hca	
		   on ar.Customer_ID = hca.Cust_Account_ID
	     	
	     left outer join HZ_Parties hp	
	  	 on hp.Party_ID = hca.Party_ID
	     	
	     left outer join GL_Code_Combinations ccid	
	     on his.Account_Code_Combination_ID = ccid.Code_Combination_ID	
	     	
	    /* left outer join fnd_flex_values_vl com_d	
	     on com_d.Flex_Value_Set_ID = 1005441	
	     and ccid.segment1 = com_d.Flex_Value	*/
	     	
	     left outer join HR_Organization_Information info	
		   on info.organization_id = rec.Org_id
	     and info.Org_information_Context = 'FUN_BUSINESS_UNIT' --'Operating Unit Information' 	
		
	     left outer join GL_Sets_of_Books sob	
		   on sob.Set_of_Books_ID = info.org_Information3
	     	
	     left outer join AR_Receipt_Methods meth	
		   on meth.Receipt_Method_ID = rec.Receipt_Method_ID 
		
		left outer join  fnd_id_flex_segments_vl ffs
		on sob.CHART_OF_ACCOUNTS_ID= ffs.ID_FLEX_NUM and ffs.segment_name ='Entity' and ffs.enabled_flag='Y'
		
		 left outer join fnd_flex_values_vl com_d	  
	     on com_d.flex_value_set_id = ffs.flex_value_set_id	 and com_d.enabled_flag ='Y'	
	     and ccid.segment1 = com_d.Flex_Value

         left outer join ar_batches_all aba
         on aba.batch_id = rec.receipt_batch_id
         and aba.org_id	= rec.org_id
		 
		 left outer join hz_cust_site_uses_all hcsua
		 on hcsua.site_use_id = rec.CUSTOMER_SITE_USE_ID
		 
		 left outer join hz_cust_acct_sites_all   hcasa
		 on hcasa.CUST_ACCT_SITE_ID= hcsua.CUST_ACCT_SITE_ID
         
         left outer join FND_VS_VALUES_B fvvb
         on fvvb.VALUE = hcasa.ATTRIBUTE3				 
		 and value_set_id = ( select value_set_id from FND_VS_VALUE_SETS where VALUE_SET_CODE ='XXC_OMS_COMPANY_CODE_MAPPING')
		 	
		
	Where 1=1	
	and his.GL_Date >=:p_from_date
	and his.GL_Date <=:p_to_date
	and (rec.org_id IN (:p_organization_id)OR  1 IN (:p_organization_id||'1'))
	and (ccid.segment1  in ( :p_company ) or 'All' in ( :p_company||'All') )
	and rec.Org_ID in (
							SELECT distinct bu.bu_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 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'
												,'XXC Billing Manager'
												,'XXC Billing Analyst'
												)
						and (bu.bu_id IN (:p_organization_id)OR  '1' IN (:p_organization_id||'1'))
	)
	--and rec.Receipt_Number in ('503368588','US01','61387648')
	Order by hra.Name, ccid.segment1, com_d.Description , hca.account_number, hp.Party_Name, rec.Receipt_Number