1 PACKAGE ARP_AUTOAPPLY_API AUTHID CURRENT_USER AS
2 /*$Header: ARATAPPS.pls 120.0.12010000.5 2009/05/06 03:55:42 aghoraka noship $*/
3 /* Structure to hold a Record from the payments interface table */
4 TYPE pmt_rec_record IS RECORD(
5 customer_id ar_payments_interface.customer_id%TYPE,
6 customer_site_use_id ar_payments_interface.customer_site_use_id%TYPE,
7 customer_bank_account_id ar_payments_interface.customer_bank_account_id%TYPE,
8 customer_number ar_payments_interface.customer_number%TYPE,
9 transit_routing_number ar_payments_interface.transit_routing_number%TYPE,
10 account ar_payments_interface.account%TYPE,
11 invoice1 ar_payments_interface.invoice1%TYPE,
12 matching1_date ar_payments_interface.matching1_date%TYPE,
13 invoice1_installment ar_payments_interface.invoice1_installment%TYPE,
14 resolved_matching_number1 ar_payments_interface.resolved_matching_number1%TYPE,
15 amount_applied1 ar_payments_interface.amount_applied1%TYPE,
16 amount_applied_from1 ar_payments_interface.amount_applied_from1%TYPE,
17 resolved_matching1_installment ar_payments_interface.resolved_matching1_installment%TYPE,
18 resolved_matching1_date ar_payments_interface.resolved_matching1_date%TYPE,
19 trans_to_receipt_rate1 ar_payments_interface.trans_to_receipt_rate1%TYPE,
20 invoice_currency_code1 ar_payments_interface.invoice_currency_code1%TYPE,
21 ussgl_transaction_code1 ar_payments_interface.ussgl_transaction_code1%TYPE,
22 invoice1_status ar_payments_interface.invoice1_status%TYPE,
23 invoice2 ar_payments_interface.invoice2%TYPE,
24 matching2_date ar_payments_interface.matching2_date%TYPE,
25 invoice2_installment ar_payments_interface.invoice2_installment%TYPE,
26 resolved_matching_number2 ar_payments_interface.resolved_matching_number2%TYPE,
27 amount_applied2 ar_payments_interface.amount_applied2%TYPE,
31 trans_to_receipt_rate2 ar_payments_interface.trans_to_receipt_rate2%TYPE,
28 amount_applied_from2 ar_payments_interface.amount_applied_from2%TYPE,
29 resolved_matching2_installment ar_payments_interface.resolved_matching2_installment%TYPE,
30 resolved_matching2_date ar_payments_interface.resolved_matching2_date%TYPE,
32 invoice_currency_code2 ar_payments_interface.invoice_currency_code2%TYPE,
33 ussgl_transaction_code2 ar_payments_interface.ussgl_transaction_code2%TYPE,
34 invoice2_status ar_payments_interface.invoice2_status%TYPE,
35 invoice3 ar_payments_interface.invoice3%TYPE,
36 matching3_date ar_payments_interface.matching3_date%TYPE,
37 invoice3_installment ar_payments_interface.invoice3_installment%TYPE,
38 resolved_matching_number3 ar_payments_interface.resolved_matching_number3%TYPE,
39 amount_applied3 ar_payments_interface.amount_applied3%TYPE,
40 amount_applied_from3 ar_payments_interface.amount_applied_from3%TYPE,
41 resolved_matching3_installment ar_payments_interface.resolved_matching3_installment%TYPE,
42 resolved_matching3_date ar_payments_interface.resolved_matching1_date%TYPE,
43 trans_to_receipt_rate3 ar_payments_interface.trans_to_receipt_rate3%TYPE,
44 invoice_currency_code3 ar_payments_interface.invoice_currency_code3%TYPE,
45 ussgl_transaction_code3 ar_payments_interface.ussgl_transaction_code3%TYPE,
46 invoice3_status ar_payments_interface.invoice3_status%TYPE,
47 invoice4 ar_payments_interface.invoice4%TYPE,
48 matching4_date ar_payments_interface.matching4_date%TYPE,
49 invoice4_installment ar_payments_interface.invoice4_installment%TYPE,
50 resolved_matching_number4 ar_payments_interface.resolved_matching_number4%TYPE,
51 amount_applied4 ar_payments_interface.amount_applied4%TYPE,
52 amount_applied_from4 ar_payments_interface.amount_applied_from4%TYPE,
53 resolved_matching4_installment ar_payments_interface.resolved_matching4_installment%TYPE,
54 resolved_matching4_date ar_payments_interface.resolved_matching4_date%TYPE,
55 trans_to_receipt_rate4 ar_payments_interface.trans_to_receipt_rate4%TYPE,
56 invoice_currency_code4 ar_payments_interface.invoice_currency_code4%TYPE,
57 ussgl_transaction_code4 ar_payments_interface.ussgl_transaction_code4%TYPE,
58 invoice4_status ar_payments_interface.invoice4_status%TYPE,
59 invoice5 ar_payments_interface.invoice5%TYPE,
60 matching5_date ar_payments_interface.matching5_date%TYPE,
61 invoice5_installment ar_payments_interface.invoice5_installment%TYPE,
62 resolved_matching_number5 ar_payments_interface.resolved_matching_number5%TYPE,
63 amount_applied5 ar_payments_interface.amount_applied5%TYPE,
64 amount_applied_from5 ar_payments_interface.amount_applied_from5%TYPE,
65 resolved_matching5_installment ar_payments_interface.resolved_matching5_installment%TYPE,
66 resolved_matching5_date ar_payments_interface.resolved_matching5_date%TYPE,
67 trans_to_receipt_rate5 ar_payments_interface.trans_to_receipt_rate5%TYPE,
68 invoice_currency_code5 ar_payments_interface.invoice_currency_code5%TYPE,
69 ussgl_transaction_code5 ar_payments_interface.ussgl_transaction_code5%TYPE,
70 invoice5_status ar_payments_interface.invoice5_status%TYPE,
71 invoice6 ar_payments_interface.invoice6%TYPE,
72 matching6_date ar_payments_interface.matching6_date%TYPE,
73 invoice6_installment ar_payments_interface.invoice6_installment%TYPE,
74 resolved_matching_number6 ar_payments_interface.resolved_matching_number6%TYPE,
75 amount_applied6 ar_payments_interface.amount_applied6%TYPE,
76 amount_applied_from6 ar_payments_interface.amount_applied_from6%TYPE,
77 resolved_matching6_installment ar_payments_interface.resolved_matching6_installment%TYPE,
78 resolved_matching6_date ar_payments_interface.resolved_matching6_date%TYPE,
79 trans_to_receipt_rate6 ar_payments_interface.trans_to_receipt_rate6%TYPE,
80 invoice_currency_code6 ar_payments_interface.invoice_currency_code6%TYPE,
81 ussgl_transaction_code6 ar_payments_interface.ussgl_transaction_code6%TYPE,
82 invoice6_status ar_payments_interface.invoice6_status%TYPE,
83 invoice7 ar_payments_interface.invoice7%TYPE,
84 matching7_date ar_payments_interface.matching7_date%TYPE,
85 invoice7_installment ar_payments_interface.invoice7_installment%TYPE,
86 resolved_matching_number7 ar_payments_interface.resolved_matching_number7%TYPE,
87 amount_applied7 ar_payments_interface.amount_applied7%TYPE,
88 amount_applied_from7 ar_payments_interface.amount_applied_from7%TYPE,
89 resolved_matching7_installment ar_payments_interface.resolved_matching7_installment%TYPE,
90 resolved_matching7_date ar_payments_interface.resolved_matching7_date%TYPE,
91 trans_to_receipt_rate7 ar_payments_interface.trans_to_receipt_rate7%TYPE,
92 invoice_currency_code7 ar_payments_interface.invoice_currency_code7%TYPE,
93 ussgl_transaction_code7 ar_payments_interface.ussgl_transaction_code7%TYPE,
94 invoice7_status ar_payments_interface.invoice7_status%TYPE,
95 invoice8 ar_payments_interface.invoice8%TYPE,
96 matching8_date ar_payments_interface.matching8_date%TYPE,
97 invoice8_installment ar_payments_interface.invoice8_installment%TYPE,
98 resolved_matching_number8 ar_payments_interface.resolved_matching_number8%TYPE,
99 amount_applied8 ar_payments_interface.amount_applied8%TYPE,
100 amount_applied_from8 ar_payments_interface.amount_applied_from8%TYPE,
101 resolved_matching8_installment ar_payments_interface.resolved_matching8_installment%TYPE,
102 resolved_matching8_date ar_payments_interface.resolved_matching8_date%TYPE,
103 trans_to_receipt_rate8 ar_payments_interface.trans_to_receipt_rate8%TYPE,
107 );
104 invoice_currency_code8 ar_payments_interface.invoice_currency_code8%TYPE,
105 ussgl_transaction_code8 ar_payments_interface.ussgl_transaction_code8%TYPE,
106 invoice8_status ar_payments_interface.invoice8_status%TYPE
108 TYPE pmt_rec_tab IS TABLE OF pmt_rec_record INDEX BY BINARY_INTEGER;
109
110 TYPE selected_recos IS RECORD(
111 remit_reference_id AR_CASH_REMIT_REFS.remit_reference_id%TYPE,
112 ref_amount_applied AR_CASH_REMIT_REFS.amount_applied%TYPE,
113 ref_amount_applied_from AR_CASH_REMIT_REFS.amount_applied_from%TYPE,
114 ref_trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE,
115 cash_receipt_id AR_CASH_RECEIPTS.cash_receipt_id%TYPE,
116 pay_from_customer AR_CASH_RECEIPTS.pay_from_customer%TYPE,
117 cr_customer_site_use_id AR_CASH_RECEIPTS.customer_site_use_id%TYPE,
118 customer_trx_id AR_PAYMENT_SCHEDULES.customer_trx_id%TYPE,
119 customer_id AR_PAYMENT_SCHEDULES.customer_id%TYPE,
120 customer_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE,
121 resolved_matching_number AR_PAYMENT_SCHEDULES.trx_number%TYPE,
122 terms_sequence_number AR_PAYMENT_SCHEDULES.terms_sequence_number%TYPE,
123 resolved_matching_date AR_PAYMENT_SCHEDULES.trx_date%TYPE,
124 trx_date AR_PAYMENT_SCHEDULES.trx_date%TYPE,
125 resolved_matching_class AR_PAYMENT_SCHEDULES.class%TYPE,
126 resolved_match_currency AR_PAYMENT_SCHEDULES.invoice_currency_code%TYPE,
127 amount_due_original AR_PAYMENT_SCHEDULES.amount_due_original%TYPE,
128 amount_due_remaining AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
129 discount_taken_earned AR_PAYMENT_SCHEDULES.discount_taken_earned%TYPE,
130 discount_taken_unearned AR_PAYMENT_SCHEDULES.discount_taken_unearned%TYPE,
131 amount_applied AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
132 trans_to_receipt_rate AR_CASH_REMIT_REFS.trans_to_receipt_rate%TYPE,
133 amount_applied_from AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE,
134 payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
135 cons_inv_id AR_CASH_RECOS.cons_inv_id%TYPE,
136 match_score_value AR_CASH_RECOS.match_score_value%TYPE,
137 org_id AR_PAYMENT_SCHEDULES.org_id%TYPE,
138 term_id AR_PAYMENT_SCHEDULES.term_id%TYPE,
139 automatch_id AR_CASH_AUTOMATCHES.automatch_id%TYPE,
140 use_matching_date AR_CASH_AUTOMATCHES.use_matching_date%TYPE,
141 use_matching_amount AR_CASH_AUTOMATCHES.use_matching_amount%TYPE,
142 auto_match_threshold AR_CASH_AUTOMATCHES.auto_match_threshold%TYPE,
143 priority AR_CASH_RECOS.priority%TYPE,
144 receipt_currency_code AR_CASH_RECEIPTS.currency_code%TYPE,
145 receipt_date AR_CASH_RECEIPTS.receipt_date%TYPE,
146 allow_overapplication_flag RA_CUST_TRX_TYPES.allow_overapplication_flag%TYPE,
147 partial_discount_flag RA_TERMS.partial_discount_flag%TYPE,
148 reco_num NUMBER
149 );
150
151 TYPE selected_recos_table IS TABLE OF selected_recos INDEX BY BINARY_INTEGER;
152
153 TYPE reco_id_tab IS TABLE OF ar_cash_recos.recommendation_id%TYPE
154 INDEX BY BINARY_INTEGER;
155 TYPE remit_ref_id_tab IS TABLE OF ar_cash_recos.remit_reference_id%TYPE
156 INDEX BY BINARY_INTEGER;
157 TYPE customer_id_tab IS TABLE OF ar_cash_recos.pay_from_customer%TYPE
158 INDEX BY BINARY_INTEGER;
159 TYPE customer_site_use_id_tab IS TABLE OF ar_cash_recos.customer_site_use_id%TYPE
160 INDEX BY BINARY_INTEGER;
161 TYPE resolved_matching_number_tab IS TABLE OF ar_cash_recos.resolved_matching_number%TYPE
162 INDEX BY BINARY_INTEGER;
163 TYPE resolved_matching_date_tab IS TABLE OF ar_cash_recos.resolved_matching_date%TYPE
164 INDEX BY BINARY_INTEGER;
165 TYPE resolved_matching_class_tab IS TABLE OF ar_cash_recos.resolved_matching_class%TYPE
166 INDEX BY BINARY_INTEGER;
167 TYPE resolved_match_currency_tab IS TABLE OF ar_cash_recos.resolved_match_currency%TYPE
168 INDEX BY BINARY_INTEGER;
169 TYPE match_resolved_using_tab IS TABLE OF ar_cash_recos.match_resolved_using%TYPE
170 INDEX BY BINARY_INTEGER;
171 TYPE cons_inv_id_tab IS TABLE OF ar_cash_recos.cons_inv_id%TYPE
172 INDEX BY BINARY_INTEGER;
173 TYPE match_score_value_tab IS TABLE OF ar_cash_recos.match_score_value%TYPE
174 INDEX BY BINARY_INTEGER;
175 TYPE match_reason_code_tab IS TABLE OF ar_cash_recos.match_reason_code%TYPE
176 INDEX BY BINARY_INTEGER;
177 TYPE org_id_tab IS TABLE OF ar_cash_recos.org_id%TYPE
178 INDEX BY BINARY_INTEGER;
179 TYPE automatch_id_tab IS TABLE OF ar_cash_recos.automatch_id%TYPE
180 INDEX BY BINARY_INTEGER;
181 TYPE priority_tab IS TABLE OF ar_cash_recos.priority%TYPE
182 INDEX BY BINARY_INTEGER;
183
184
185 TYPE reco_num_tab IS TABLE OF ar_cash_reco_lines.line_number%TYPE
186 INDEX BY BINARY_INTEGER;
187 TYPE customer_trx_id_tab IS TABLE OF ar_cash_reco_lines.customer_trx_id%TYPE
188 INDEX BY BINARY_INTEGER;
189 TYPE payment_schedule_id_tab IS TABLE OF ar_cash_reco_lines.payment_schedule_id%TYPE
190 INDEX BY BINARY_INTEGER;
191 TYPE amount_applied_tab IS TABLE OF ar_cash_reco_lines.amount_applied%TYPE
192 INDEX BY BINARY_INTEGER;
193 TYPE amount_applied_from_tab IS TABLE OF ar_cash_reco_lines.amount_applied_from%TYPE
194 INDEX BY BINARY_INTEGER;
198 INDEX BY BINARY_INTEGER;
195 TYPE trans_to_receipt_rate_tab IS TABLE OF ar_cash_reco_lines.trans_to_receipt_rate%TYPE
196 INDEX BY BINARY_INTEGER;
197 TYPE receipt_currency_code_tab IS TABLE OF ar_cash_reco_lines.receipt_currency_code%TYPE
199 TYPE receipt_date_tab IS TABLE OF ar_cash_reco_lines.receipt_date%TYPE
200 INDEX BY BINARY_INTEGER;
201 TYPE recommendation_reason_tab IS TABLE OF ar_cash_reco_lines.recommendation_reason%TYPE
202 INDEX BY BINARY_INTEGER;
203 TYPE discount_taken_earned_tab IS TABLE OF ar_cash_reco_lines.discount_taken_earned%TYPE
204 INDEX BY BINARY_INTEGER;
205 TYPE discount_taken_unearned_tab IS TABLE OF ar_cash_reco_lines.discount_taken_unearned%TYPE
206 INDEX BY BINARY_INTEGER;
207
208 TYPE inv_num_tab IS TABLE OF ar_payments_interface.invoice1%TYPE
209 INDEX BY BINARY_INTEGER;
210 TYPE mtch_date_tab IS TABLE OF ar_payments_interface.matching1_date%TYPE
211 INDEX BY BINARY_INTEGER;
212 TYPE inst_num_tab IS TABLE OF ar_payments_interface.invoice1_installment%TYPE
213 INDEX BY BINARY_INTEGER;
214 TYPE res_mtch_num_tab IS TABLE OF ar_payments_interface.resolved_matching_number1%TYPE
215 INDEX BY BINARY_INTEGER;
216 TYPE amt_app_tab IS TABLE OF ar_payments_interface.amount_applied1%TYPE
217 INDEX BY BINARY_INTEGER;
218 TYPE amt_app_frm_tab IS TABLE OF ar_payments_interface.amount_applied_from1%TYPE
219 INDEX BY BINARY_INTEGER;
220 TYPE res_mtch_inst_tab IS TABLE OF ar_payments_interface.resolved_matching1_installment%TYPE
221 INDEX BY BINARY_INTEGER;
222 TYPE res_mtch_date_tab IS TABLE OF ar_payments_interface.resolved_matching1_date%TYPE
223 INDEX BY BINARY_INTEGER;
224 TYPE trns_to_rcpt_rt_tab IS TABLE OF ar_payments_interface.trans_to_receipt_rate1%TYPE
225 INDEX BY BINARY_INTEGER;
226 TYPE inv_cur_code_tab IS TABLE OF ar_payments_interface.invoice_currency_code1%TYPE
227 INDEX BY BINARY_INTEGER;
228 TYPE ussgl_trx_code_tab IS TABLE OF ar_payments_interface.ussgl_transaction_code1%TYPE
229 INDEX BY BINARY_INTEGER;
230 TYPE inv_status_tab IS TABLE OF ar_payments_interface.invoice1_status%TYPE
231 INDEX BY BINARY_INTEGER;
232 TYPE line_num_tab IS TABLE OF ar_cash_remit_refs.line_number%TYPE
233 INDEX BY BINARY_INTEGER;
234
235 /* * This procedure is called from the concurrent request. This in turns *
236 * spawns the child program if submitted with total_workers > 1. * */
237 PROCEDURE auto_apply_master ( P_ERRBUF OUT NOCOPY VARCHAR2
238 , P_RETCODE OUT NOCOPY NUMBER
239 , p_org_id IN NUMBER
240 , p_receipt_no_l IN VARCHAR2
241 , p_receipt_no_h IN VARCHAR2
242 , p_batch_name_l IN VARCHAR2
243 , p_batch_name_h IN VARCHAR2
244 , p_min_unapp_amt IN NUMBER
245 , p_receipt_date_l IN VARCHAR2
246 , p_receipt_date_h IN VARCHAR2
247 , p_receipt_method_l IN VARCHAR2
248 , p_receipt_method_h IN VARCHAR2
249 , p_customer_name_l IN VARCHAR2
250 , p_customer_name_h IN VARCHAR2
251 , p_customer_no_l IN VARCHAR2
252 , p_customer_no_h IN VARCHAR2
253 , p_batch_id IN NUMBER
254 , p_transmission_id IN NUMBER
255 , p_called_from IN VARCHAR2
256 , p_total_workers IN NUMBER);
257 /* * This procedure makes a call to insert recommendations, validate and *
258 * apply recommendations. * */
259 PROCEDURE auto_apply_child( P_ERRBUF OUT NOCOPY VARCHAR2
260 , P_RETCODE OUT NOCOPY NUMBER
261 , p_worker_number IN NUMBER);
262 /* * The procedure to delete records inserted in ar_cash_remit_ref_interim *
263 * for the current run. * */
264 PROCEDURE delete_interim_records;
265
266 /* * This function returns a new sequence number * */
267 FUNCTION get_next_reco_id( p_reco_num IN NUMBER)
268 RETURN NUMBER;
269
270 FUNCTION get_cross_curr_rate(p_amount_applied IN ar_cash_remit_refs.amount_applied%TYPE
271 , p_amount_applied_from IN ar_cash_remit_refs.amount_applied_from%TYPE
272 , p_inv_curr_code IN ar_payment_schedules.invoice_currency_code%TYPE
273 , p_rec_curr_code IN ar_cash_receipts.currency_code%TYPE)
274 RETURN NUMBER;
275
276 END ARP_AUTOAPPLY_API;
[Home] [Help]