AR Automatic Cash Application Master Program

select 
       receipts.receipt_number Receipt_Number,  
       receipts.currency_code  Cur,
       (select p.party_name 
       from hz_cust_accounts ac, hz_parties p 
       where receipts.pay_from_customer = ac.cust_account_id 
       and ac.party_id = p.party_id) Customer_Name,
       receipts.amount Receipt_Amount,
       (select decode(decode(receipts.pay_from_customer,null,ps.amount_due_remaining,null),
       null,ps.amount_due_remaining,null)*-1 from ar_payment_schedules ps 
       where ps.cash_receipt_id = receipts.cash_receipt_id) Unapplied_Balance,
       (select decode(receipts.pay_from_customer,null,ps.amount_due_remaining,null)*-1 
       from ar_payment_schedules ps where ps.cash_receipt_id = receipts.cash_receipt_id)  Unidentified_Balance,
       (select ruleset.automatch_set_name from ar_cash_auto_rule_sets_tl ruleset
       where ruleset.automatch_set_id = refs.automatch_set_id and ruleset.language = userenv('lang'))  Rule_Set,
       (case refs.receipt_reference_status
       	  when 'AR_AA_RULE_SET_INACTIVE' then ar_aratappm_pkg.getmessage('AR_AA_RULE_SET_INACTIVE')         
          else null end) Exe_Message,
       (case refs.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else refs.line_number END) line_number,
       (case refs.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else interim.invoice_reference END) Reference_number,
       (case refs.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else refs.invoice_currency_code END) Cur1, 
       (case refs.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else refs.amount_applied END) Amount,
       (case refs.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else refs.resolved_matching_number END) Applied_Transaction,
       (case refs.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else refs.match_score_value END) Match_Score,
       (case refs.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else (select decode(mat.automatch_name, null, 'Knapsack', mat.automatch_name) from ar_cash_automatches_tl mat 
          where refs.match_resolved_using = mat.automatch_id (+)
	  and mat.language (+) = userenv('lang')) end) Automatic_Matching_Rule,
       (case refs.receipt_reference_status
	  when 'AR_AA_RULE_SET_INACTIVE' then null
	  else ar_aratappm_pkg.getmessage(refs.receipt_reference_status) end) Application_Execution_Message          
from
	ar_cash_receipts receipts,
	ar_cash_remit_refs_interim interim, 
	ar_cash_remit_refs refs  
where
	receipts.cash_receipt_id = interim.cash_receipt_id
	and refs.cash_receipt_id = interim.cash_receipt_id
        and refs.remit_reference_id = interim.remit_reference_id
UNION ALL
select 
       receipts.receipt_number Receipt_Number,  
       receipts.currency_code  Cur,
       (select p.party_name 
       from hz_cust_accounts ac, hz_parties p 
       where receipts.pay_from_customer = ac.cust_account_id 
       and ac.party_id = p.party_id) Customer_Name,
       receipts.amount Receipt_Amount,
       (select decode(decode(receipts.pay_from_customer,null,ps.amount_due_remaining,null),
       null,ps.amount_due_remaining,null)*-1 from ar_payment_schedules ps 
       where ps.cash_receipt_id = receipts.cash_receipt_id) Unapplied_Balance,
       (select decode(receipts.pay_from_customer,null,ps.amount_due_remaining,null)*-1 
       from ar_payment_schedules ps where ps.cash_receipt_id = receipts.cash_receipt_id)  Unidentified_Balance,
       (select ruleset.automatch_set_name from ar_cash_auto_rule_sets_tl ruleset
       where ruleset.automatch_set_id = interim.automatch_set_id and ruleset.language = userenv('lang'))  Rule_Set,
       (case interim.receipt_reference_status
       	  when 'AR_AA_RULE_SET_INACTIVE' then ar_aratappm_pkg.getmessage('AR_AA_RULE_SET_INACTIVE')         
          else null end) Exe_Message,
       (case interim.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else 1 END) line_number,
       (case interim.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else interim.invoice_reference END) Reference_number,
       (case interim.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else interim.invoice_currency_code END) Cur1, 
       (case interim.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else interim.amount_applied END) Amount,
       (case interim.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else NULL END) Applied_Transaction,
       (case interim.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else 100 END) Match_Score,
       (case interim.receipt_reference_status
          when 'AR_AA_RULE_SET_INACTIVE' then null
          else  'Knapsack' end) Automatic_Matching_Rule,
       (case interim.receipt_reference_status
	  when 'AR_AA_RULE_SET_INACTIVE' then null
	  else ar_aratappm_pkg.getmessage(interim.receipt_reference_status) end) Application_Execution_Message          
from
	ar_cash_receipts receipts,
	ar_cash_remit_refs_interim interim
where
	receipts.cash_receipt_id = interim.cash_receipt_id
        and  interim.remit_reference_id <0        
order by receipt_number, line_number asc