AR Automatic Cash Application Master Program

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Automatic Cash Application Master Program
Short Name: ARATAPPM
DB package: AR_ARATAPPM_PKG
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
Parameter Name SQL text Validation
No. Of Instances
 
Number
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Receipt Method High
 
LOV Oracle
Receipt Method Low
 
LOV Oracle
Receipt Date To
 
Date
Receipt Date From
 
Date
Minimum Unapplied Amount
 
Number
Receipt Batch Name High
 
LOV Oracle
Receipt Batch Name low
 
LOV Oracle
Receipt Number High
 
LOV Oracle
Receipt Number Low
 
LOV Oracle
Operating Unit
 
LOV Oracle