[Home] [Help]
PACKAGE BODY: APPS.AR_AUTOREM_API
Source
1 PACKAGE BODY AR_AUTOREM_API AS
2 /* $Header: ARATREMB.pls 120.23.12010000.13 2009/01/26 19:40:06 nemani ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5 PG_PARALLEL varchar2(1) := NVL(FND_PROFILE.value('AR_USE_PARALLEL_HINT'), 'N');
6 PROCEDURE SUBMIT_FORMAT ( p_batch_id ar_batches.batch_id%TYPE);
7 PROCEDURE CONTROL_CHECK ( p_batch_id ar_batches.batch_id%TYPE);
8
9 G_ERROR varchar2(1) := 'N';
10 TOTAL_WORKERS NUMBER := 0;
11 WORKER_NUMBER NUMBER := 0;
12 pg_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
13 pg_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
14 pg_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
15
16 /*========================================================================+
17 | PUBLIC PROCEDURE GET_PARAMETERS |
18 | |
19 | DESCRIPTION |
20 | |
21 | This procedure is used to get the parameters from the Conc program |
22 | and convert them to the type reqd for processing. |
23 | |
24 | PSEUDO CODE/LOGIC |
25 | |
26 | PARAMETERS |
27 | |
28 | |
29 | KNOWN ISSUES |
30 | |
31 | NOTES |
32 | |
33 | |
34 | MODIFICATION HISTORY |
35 | Date Author Description of Changes |
36 | 16-JUL-2005 bichatte Created |
37 *=========================================================================*/
38
39
40 PROCEDURE get_parameters(
41 P_ERRBUF OUT NOCOPY VARCHAR2,
42 P_RETCODE OUT NOCOPY NUMBER,
43 p_process_type IN VARCHAR2,
44 p_batch_date IN VARCHAR2,
45 p_batch_gl_date IN VARCHAR2,
46 p_create_flag IN VARCHAR2,
47 p_approve_flag IN VARCHAR2,
48 p_format_flag IN VARCHAR2,
49 p_batch_id IN VARCHAR2,
50 p_debug_mode_on IN VARCHAR2,
51 p_batch_currency IN VARCHAR2,
52 p_exchange_date IN VARCHAR2,
53 p_exchange_rate IN VARCHAR2,
54 p_exchange_rate_type IN VARCHAR2,
55 p_remit_method_code IN VARCHAR2,
56 p_receipt_class_id IN VARCHAR2,
57 p_payment_method_id IN VARCHAR2,
58 p_media_reference IN VARCHAR2,
59 p_remit_bank_branch_id IN VARCHAR2,
60 p_remit_bank_account_id IN VARCHAR2,
61 p_remit_bank_deposit_number IN VARCHAR2,
62 p_comments IN VARCHAR2,
63 p_trx_date_l IN VARCHAR2,
64 p_trx_date_h IN VARCHAR2,
65 p_due_date_l IN VARCHAR2,
66 p_due_date_h IN VARCHAR2,
67 p_trx_num_l IN VARCHAR2,
68 p_trx_num_h IN VARCHAR2,
69 p_doc_num_l IN VARCHAR2,
70 p_doc_num_h IN VARCHAR2,
71 p_customer_number_l IN VARCHAR2,
72 p_customer_number_h IN VARCHAR2,
73 p_customer_name_l IN VARCHAR2,
74 p_customer_name_h IN VARCHAR2,
75 p_customer_id IN VARCHAR2,
76 p_site_l IN VARCHAR2,
77 p_site_h IN VARCHAR2,
78 p_site_id IN VARCHAR2,
79 p_remittance_total_from IN VARCHAR2,
80 p_Remittance_total_to IN VARCHAR2,
81 p_billing_number_l IN VARCHAR2,
82 p_billing_number_h IN VARCHAR2,
83 p_customer_bank_acc_num_l IN VARCHAR2,
84 p_customer_bank_acc_num_h IN VARCHAR2,
85 /* Changes for Parallelization */
86 p_worker_number IN NUMBER DEFAULT 0,
87 p_total_workers IN NUMBER DEFAULT 0
88 ) IS
89
90 l_request_id ar_cash_receipts.request_id%TYPE;
91 l_batch_date ar_cash_receipts.receipt_date%TYPE ;
92 l_gl_date ar_cash_receipt_history.gl_date%TYPE;
93 l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
94 l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
95 l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
96 l_currency_code ar_cash_receipts.currency_code%TYPE;
97 l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
98 l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
99 l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
100 l_remittance_bank_branch_id ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL;
101 l_remittance_bank_account_id ar_receipt_method_accounts.REMIT_BANK_ACCT_USE_ID%TYPE DEFAULT NULL;
102 o_batch_id NUMBER;
103
104 p_create_only VARCHAR2(1);
105 l_flow1 VARCHAR2(1);
106 l_flow2 VARCHAR2(1);
107 l_flow3 VARCHAR2(1);
108 l_count NUMBER;
109
110 /*bug 7352164 Parameter to store values*/
111 l_control_count number;
112 l_control_amount number;
113
114 cr_return_status VARCHAR2(1);
115 l_return_status VARCHAR2(1);
116
117
118 p_payment_type_code ar_receipt_methods.payment_type_code%type;
119 p_sob_id ar_batches.set_of_books_id%type;
120
121 l_last_updated_by NUMBER;
122 l_created_by NUMBER;
123 l_last_update_login NUMBER;
124 l_program_application_id NUMBER;
125 l_program_id NUMBER;
126
127
128 l_batch_applied_status ar_batches.batch_applied_status%type;
129 pl_return_status VARCHAR2(1);
130 pl_msg_count NUMBER;
131 pl_msg_data VARCHAR2(240);
132 BEGIN
133 --arp_standard.enable_debug;
134 --arp_standard.enable_file_debug('/appslog/fin_top/utl/finixud/out','chat67.log');
135 IF PG_DEBUG in ('Y', 'C') THEN
136 fnd_file.put_line(FND_FILE.LOG,'autoremapi start ()+');
137 fnd_file.put_line(FND_FILE.LOG, 'value of p_errbuf ' || P_ERRBUF);
138 fnd_file.put_line(FND_FILE.LOG, 'value of p_retcode ' || (P_RETCODE));
139 fnd_file.put_line(FND_FILE.LOG, 'value of p_process_type ' || p_process_type);
140 fnd_file.put_line(FND_FILE.LOG, 'value of p_create_flag ' || p_create_flag);
141 fnd_file.put_line(FND_FILE.LOG, 'value of p_approve_flag ' || p_approve_flag);
142 fnd_file.put_line(FND_FILE.LOG, 'value of p_format_flag ' || p_format_flag);
143 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_id ' || (p_batch_id));
144 fnd_file.put_line(FND_FILE.LOG, 'value of p_debug_mode_on ' || p_debug_mode_on);
145 fnd_file.put_line(FND_FILE.LOG, 'value of p_receipt_class_id ' || p_receipt_class_id);
146 fnd_file.put_line(FND_FILE.LOG, 'value of p_payment_method_id ' || p_payment_method_id);
147 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_currency ' || p_batch_currency);
148 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_date ' || p_batch_date);
149 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_gl_date ' || p_batch_gl_date);
150 fnd_file.put_line(FND_FILE.LOG, 'value of p_comments ' || p_comments);
151 fnd_file.put_line(FND_FILE.LOG, 'value of p_exchange_date ' || p_exchange_date);
152 fnd_file.put_line(FND_FILE.LOG, 'value of p_exchange_rate ' || p_exchange_rate);
153 fnd_file.put_line(FND_FILE.LOG, 'value of p_exchange_rate_type ' || p_exchange_rate_type);
154 fnd_file.put_line(FND_FILE.LOG, 'value of p_media_reference ' || p_media_reference);
155 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_method_code ' || p_remit_method_code);
156 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_bank_branch_id ' || p_remit_bank_branch_id);
157 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_bank_account_id ' || p_remit_bank_account_id);
158 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_bank_deposit_number ' || p_remit_bank_deposit_number);
159 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_date_l ' || p_trx_date_l);
160 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_date_h ' || p_trx_date_h);
161 fnd_file.put_line(FND_FILE.LOG, 'value of p_due_date_l ' || p_due_date_l);
162 fnd_file.put_line(FND_FILE.LOG, 'value of p_due_date_h ' || p_due_date_h);
163 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_num_l ' || p_trx_num_l);
164 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_num_h ' || p_trx_num_h);
165 fnd_file.put_line(FND_FILE.LOG, 'value of p_doc_num_l ' || p_doc_num_l);
166 fnd_file.put_line(FND_FILE.LOG, 'value of p_doc_num_h ' || p_doc_num_h);
167 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_number_l ' || p_customer_number_l);
168 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_number_h ' || p_customer_number_h);
169 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_name_l ' || p_customer_name_l);
170 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_name_h ' || p_customer_name_h);
171 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_id ' || (p_customer_id));
172 fnd_file.put_line(FND_FILE.LOG, 'value of p_site_l ' || p_site_l);
173 fnd_file.put_line(FND_FILE.LOG, 'value of p_site_h ' || p_site_h);
174 fnd_file.put_line(FND_FILE.LOG, 'value of p_site_id ' || (p_site_id));
175 fnd_file.put_line(FND_FILE.LOG, 'value of p_remittance_total_from ' || p_remittance_total_from);
176 fnd_file.put_line(FND_FILE.LOG, 'value of p_Remittance_total_to ' || p_remittance_total_to);
177 fnd_file.put_line(FND_FILE.LOG, 'value of p_billing_number_l ' || p_billing_number_l);
178 fnd_file.put_line(FND_FILE.LOG, 'value of p_billing_number_h ' || p_billing_number_h);
179 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_bank_acc_num_l ' || p_customer_bank_acc_num_l);
180 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_bank_acc_num_h ' || p_customer_bank_acc_num_h);
181 fnd_file.put_line(FND_FILE.LOG, 'value of p_worker_number ' || p_worker_number);
182 fnd_file.put_line(FND_FILE.LOG, 'value of p_total_workers ' || p_total_workers);
183 END IF;
184 TOTAL_WORKERS := NVL(p_total_workers, 0);
185 WORKER_NUMBER := NVL(p_worker_number, 0);
186
187 l_batch_date := fnd_date.canonical_to_date(p_batch_date);
188 l_gl_date := fnd_date.canonical_to_date(p_batch_gl_date);
189 l_create_flag := p_create_flag;
190 l_approve_flag := p_approve_flag;
191 l_format_flag := p_format_flag;
192 l_currency_code := p_batch_currency;
193 l_remittance_method := p_remit_method_code;
194 l_receipt_class_id := to_number(p_receipt_class_id);
195 l_receipt_method_id := to_number(p_payment_method_id);
196 l_remittance_bank_branch_id := to_number(p_remit_bank_branch_id);
197 l_remittance_bank_account_id := to_number(p_remit_bank_account_id);
198 /* CONVERT INPUT DATE PARAMETERS */
199
200
201 IF PG_DEBUG in ('Y', 'C') THEN
202 fnd_file.put_line(FND_FILE.LOG, 'Values taken in Local variables ');
203 fnd_file.put_line(FND_FILE.LOG, 'value of l_gl_date '||l_gl_date);
204 fnd_file.put_line(FND_FILE.LOG, 'value of l_batch_date '||l_batch_date);
205 fnd_file.put_line(FND_FILE.LOG, 'value of l_create_flag '|| l_create_flag);
206 fnd_file.put_line(FND_FILE.LOG, 'value of l_approve_flag '|| l_approve_flag);
207 fnd_file.put_line(FND_FILE.LOG, 'value of l_format_flag '|| l_format_flag);
208 fnd_file.put_line(FND_FILE.LOG, 'value of l_currency_code '|| l_currency_code);
209 fnd_file.put_line(FND_FILE.LOG, 'value of l_remittance_method '|| l_remittance_method);
210 fnd_file.put_line(FND_FILE.LOG, 'value of l_receipt_class_id '|| l_receipt_class_id);
211 fnd_file.put_line(FND_FILE.LOG, 'value of l_receipt_method_id '|| l_receipt_method_id);
212 fnd_file.put_line(FND_FILE.LOG, 'value of l_remittance_bank_branch_id '|| l_remittance_bank_branch_id);
213 fnd_file.put_line(FND_FILE.LOG, 'value of l_remittance_bank_account_id '|| l_remittance_bank_account_id);
214 END IF;
215
216 l_request_id := arp_standard.profile.request_id;
217 l_last_updated_by := arp_standard.profile.last_update_login ;
218 l_created_by := arp_standard.profile.user_id ;
219 l_last_update_login := arp_standard.profile.last_update_login ;
220 l_program_application_id := arp_standard.application_id ;
221 l_program_id := arp_standard.profile.program_id;
222 pg_create_flag := p_create_flag;
223 pg_approve_flag := p_approve_flag;
224 pg_format_flag := p_format_flag;
225
226
227
228 IF PG_DEBUG in ('Y','C') THEN
229 fnd_file.put_line(FND_FILE.LOG, 'value l_request_id' || l_request_id);
230 fnd_file.put_line(FND_FILE.LOG, 'value l_last_updated_by ' || l_last_updated_by);
231 fnd_file.put_line(FND_FILE.LOG, 'value l_created_by' || l_created_by);
232 fnd_file.put_line(FND_FILE.LOG, 'value l_last_update_login'|| l_last_update_login);
233 fnd_file.put_line(FND_FILE.LOG, 'value l_program_application_id'|| l_program_application_id);
234 fnd_file.put_line(FND_FILE.LOG, 'value l_program_id' || l_program_id);
235
236 END IF;
237
238 /* CHECK RUN AND CALLED FROM */
239 IF TOTAL_WORKERS > 1 THEN
240
241 fnd_file.put_line(FND_FILE.LOG, 'Parallel Processing');
242 o_batch_id := p_batch_id;
243
244
245 IF PG_DEBUG in ('Y','C') THEN
246 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id in FLOW3 '|| to_char(o_batch_id));
247 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
248 END IF;
249
250
251 create_and_update_remit_rec_pa(
252 p_batch_id => o_batch_id,
253 p_return_status => cr_return_status
254 );
255
256 /* CALL TO PROCESS PAY RECEIPTS */
257 IF PG_DEBUG in ('Y','C') THEN
258 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to process_pay ' || to_char(o_batch_id));
259 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
260 END IF;
261
262 process_pay_receipt_parallel(
263 p_batch_id => o_batch_id,
264 p_called_from =>'AUTOREMAPI',
265 x_msg_count => pl_msg_count,
266 x_msg_data => pl_msg_data,
267 x_return_status => pl_return_status
268 );
269
270 ELSE
271 BEGIN
272 select count(*)
273 INTO l_count
274 from AR_CASH_RECEIPTS
275 where selected_remittance_batch_id = p_batch_id;
276
277 EXCEPTION
278 when no_data_found THEN
279 l_count :=0;
280
281 END;
282
283
284 IF p_batch_id is null then
285
286 l_flow1 := 'Y';
287
288 END IF;
289
290 IF ( (l_count=0 ) AND ( p_batch_id is not null)) THEN
291
292 l_flow2 := 'Y';
293
294 END IF;
295
296 IF ( (l_count >0 ) AND ( p_batch_id is not null)) THEN
297
298 l_flow3 := 'Y';
299
300 END IF;
301
302
303 IF ( p_create_flag = 'Y' AND p_approve_flag = 'N' AND p_format_flag = 'N') THEN
304
305 fnd_file.put_line(FND_FILE.LOG, 'This is a Create Only RUN ');
306
307 p_create_only := 'Y';
308
309 END IF;
310
311 /* CHECK RUN AND CALLED FORM END */
312
313 fnd_file.put_line(FND_FILE.LOG, 'value L_FLOW1' || l_flow1);
314 fnd_file.put_line(FND_FILE.LOG, 'value L_FLOW2' || l_flow2);
315 fnd_file.put_line(FND_FILE.LOG, 'value L_FLOW3' || l_flow3);
316
317 IF ( (l_flow2 = 'Y') OR (l_flow3 = 'Y')) THEN
318
319 select batch_date ,gl_date , remit_method_code, currency_code,
320 receipt_method_id, REMITTANCE_BANK_BRANCH_ID,REMIT_BANK_ACCT_USE_ID
321 into l_batch_date,l_gl_date,l_remittance_method,l_currency_code,
322 l_receipt_method_id,l_remittance_bank_branch_id,l_remittance_bank_account_id
323 from AR_BATCHES
324 where batch_id = p_batch_id;
325
326 IF PG_DEBUG in ('Y','C') THEN
327 fnd_file.put_line(FND_FILE.LOG, 'For l_flow2 and l_flow3');
328 fnd_file.put_line(FND_FILE.LOG, 'value batch_date' || l_batch_date);
329 fnd_file.put_line(FND_FILE.LOG, 'value gl_date ' || l_gl_date);
330 fnd_file.put_line(FND_FILE.LOG, 'value remit_method' || l_remittance_method);
331 fnd_file.put_line(FND_FILE.LOG, 'value currency_code' || l_currency_code);
332 fnd_file.put_line(FND_FILE.LOG, 'value receipt_method_id'|| l_receipt_method_id);
333 fnd_file.put_line(FND_FILE.LOG, 'value l_remittance_bank_branch_id'|| l_remittance_bank_branch_id);
334 fnd_file.put_line(FND_FILE.LOG, 'value l_remittance_bank_account_id' || l_remittance_bank_account_id);
335
336 END IF;
337
338
339 END IF;
340
341
342
343 IF l_flow1 = 'Y' THEN
344
345 fnd_file.put_line(FND_FILE.LOG,'In if l_flow1');
346
347 IF p_create_only = 'Y' THEN
348
349 fnd_file.put_line(FND_FILE.LOG,'In if l_flow1: if p_create_only is Y');
350
351 /* insert batch */
352
353 insert_batch(
354 l_batch_date,
355 l_gl_date,
356 l_approve_flag,
357 l_format_flag,
358 l_currency_code,
359 l_remittance_method,
360 l_receipt_class_id,
361 l_receipt_method_id,
362 l_remittance_bank_branch_id,
363 l_remittance_bank_account_id,
364 o_batch_id
365 );
366
367 /* select and update receipt */
368
369 if l_receipt_method_id is not null then /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
370 select rm.payment_channel_code,b.set_of_books_id
371 into p_payment_type_code, p_sob_id
372 from ar_receipt_methods rm,
373 ar_batches b
374 where rm.receipt_method_id = l_receipt_method_id /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
375 and b.receipt_method_id = rm.receipt_method_id
376 and b.batch_id = o_batch_id;
377 else
378 p_payment_type_code := null;
379 select b.set_of_books_id
380 into p_sob_id
381 from ar_batches b
382 where b.batch_id = o_batch_id;
383 end if;
384
385
386
387 select_update_rec(
388 p_customer_number_l=> p_customer_number_l,
389 p_customer_number_h=> p_customer_number_h,
390 p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
391 p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
392 p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
393 p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
394 p_trx_num_l=> p_trx_num_l,
395 p_trx_num_h=> p_trx_num_h,
396 p_remittance_total_to=> p_remittance_total_to,
397 p_remittance_total_from=> p_remittance_total_from,
398 p_batch_id=> o_batch_id ,
399 p_receipt_method_id=> l_receipt_method_id ,
400 p_currency_code=> p_batch_currency,
401 p_payment_type_code=> p_payment_type_code,
402 p_sob_id=> p_sob_id ,
403 p_remit_method_code=> p_remit_method_code,
404 p_remit_bank_account_id=> l_remittance_bank_account_id ,
405 p_return_status=>l_return_status);
406
407 IF PG_DEBUG in ('Y', 'C') THEN
408 fnd_file.put_line(FND_FILE.LOG,' COMMITING WORK - CREATE ONLY ');
409 END IF;
410
411 COMMIT;
412
413
414 ELSE
415
416 fnd_file.put_line(FND_FILE.LOG,'In if l_flow1: else P_create_only is Y');
417
418 /* CALL TO INSERT BATCH FROM MAIN */
419
420 insert_batch(
421 l_batch_date,
422 l_gl_date,
423 l_approve_flag,
424 l_format_flag,
425 l_currency_code,
426 l_remittance_method,
427 l_receipt_class_id,
428 l_receipt_method_id,
429 l_remittance_bank_branch_id,
430 l_remittance_bank_account_id,
431 o_batch_id
432 );
433
434
435
436 /* CALL TO select_and_update receipts */
437
438 if l_receipt_method_id is not null then /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
439 select rm.payment_channel_code,b.set_of_books_id
440 into p_payment_type_code, p_sob_id
441 from ar_receipt_methods rm,
442 ar_batches b
443 where rm.receipt_method_id = l_receipt_method_id /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
444 and b.receipt_method_id = rm.receipt_method_id
445 and b.batch_id = o_batch_id;
446 else
447 p_payment_type_code := null;
448 select b.set_of_books_id
449 into p_sob_id
450 from ar_batches b
451 where b.batch_id = o_batch_id;
452 end if;
453
454
455
456 select_update_rec(
457 p_customer_number_l=> p_customer_number_l,
458 p_customer_number_h=> p_customer_number_h,
459 p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
460 p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
461 p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
462 p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
463 p_trx_num_l=> p_trx_num_l,
464 p_trx_num_h=> p_trx_num_h,
465 p_remittance_total_to=> p_remittance_total_to,
466 p_remittance_total_from=> p_remittance_total_from,
467 p_batch_id=> o_batch_id ,
468 p_receipt_method_id=> l_receipt_method_id ,
469 p_currency_code=> p_batch_currency,
470 p_payment_type_code=> p_payment_type_code,
471 p_sob_id=> p_sob_id ,
472 p_remit_method_code=> p_remit_method_code,
473 p_remit_bank_account_id=> l_remittance_bank_account_id ,
474 p_return_status=>l_return_status);
475
476
477 /* CALL TO CREATE AND UPDATE REM RECEIPTS */
478
479
480 IF PG_DEBUG in ('Y','C') THEN
481 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to create remit rec ' || to_char(o_batch_id));
482 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
483 END IF;
484
485
486 create_and_update_remit_rec(
487 p_batch_id => o_batch_id,
488 p_return_status => cr_return_status
489 );
490
491 /* CALL TO PROCESS PAY RECEIPTS */
492 IF PG_DEBUG in ('Y','C') THEN
493 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to process_pay ' || to_char(o_batch_id));
494 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
495 END IF;
496
497 process_pay_receipt(
498 p_batch_id => o_batch_id,
499 p_called_from =>'AUTOREMAPI',
500 x_msg_count => pl_msg_count,
501 x_msg_data => pl_msg_data,
502 x_return_status => pl_return_status
503 );
504
505
506 END IF ; /* of else part of p_create_only */
507
508 END IF ; /* end if of l_flow1 */
509
510
511 IF l_flow2 = 'Y' THEN
512
513 fnd_file.put_line(FND_FILE.LOG,'In if l_flow2');
514
515 o_batch_id := p_batch_id;
516
517 IF PG_DEBUG in ('Y','C') THEN
518 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id in FLOW2 ' || to_char(o_batch_id));
519 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
520 END IF;
521
522
523 IF p_create_only = 'Y' THEN
524
525 fnd_file.put_line(FND_FILE.LOG,'In if l_flow2: if p_create_only is Y');
526
527 if l_receipt_method_id is not null then /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
528 select rm.payment_channel_code,b.set_of_books_id
529 into p_payment_type_code, p_sob_id
530 from ar_receipt_methods rm,
531 ar_batches b
532 where b.receipt_method_id = rm.receipt_method_id
533 and b.batch_id = o_batch_id;
534 else
535 p_payment_type_code := null;
536 select b.set_of_books_id
537 into p_sob_id
538 from ar_batches b
539 where b.batch_id = o_batch_id;
540 end if;
541
542
543
544 select_update_rec(
545 p_customer_number_l=> p_customer_number_l,
546 p_customer_number_h=> p_customer_number_h,
547 p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
548 p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
549 p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
550 p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
551 p_trx_num_l=> p_trx_num_l,
552 p_trx_num_h=> p_trx_num_h,
553 p_remittance_total_to=> p_remittance_total_to,
554 p_remittance_total_from=> p_remittance_total_from,
555 p_batch_id=> o_batch_id ,
556 p_receipt_method_id=> l_receipt_method_id ,
557 p_currency_code=> l_currency_code,
558 p_payment_type_code=> p_payment_type_code,
559 p_sob_id=> p_sob_id ,
560 p_remit_method_code=> l_remittance_method,
561 p_remit_bank_account_id=> l_remittance_bank_account_id ,
562 p_return_status=>l_return_status);
563
564 IF PG_DEBUG in ('Y', 'C') THEN
565 fnd_file.put_line(FND_FILE.LOG,' COMMITING WORK - CREATE ONLY ');
566 END IF;
567
568 COMMIT;
569
570
571
572 ELSE
573
574 fnd_file.put_line(FND_FILE.LOG,'In if l_flow2: else p_create_only is Y');
575
576 IF PG_DEBUG in ('Y','C') THEN
577 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to select update rec ' || to_char(o_batch_id));
578 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
579 END IF;
580
581 if l_receipt_method_id is not null then /* bug7173199: subsituted p_payment_method_id with l_receipt_method_id*/
582 select rm.payment_channel_code,b.set_of_books_id
583 into p_payment_type_code, p_sob_id
584 from ar_receipt_methods rm,
585 ar_batches b
586 where b.receipt_method_id = rm.receipt_method_id
587 and b.batch_id = o_batch_id;
588 else
589 p_payment_type_code := null;
590 select b.set_of_books_id
591 into p_sob_id
592 from ar_batches b
593 where b.batch_id = o_batch_id;
594 end if;
595
596 IF p_create_flag = 'Y' THEN
597 select_update_rec(
598 p_customer_number_l=> p_customer_number_l,
599 p_customer_number_h=> p_customer_number_h,
600 p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
601 p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
602 p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
603 p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
604 p_trx_num_l=> p_trx_num_l,
605 p_trx_num_h=> p_trx_num_h,
606 p_remittance_total_to=> p_remittance_total_to,
607 p_remittance_total_from=> p_remittance_total_from,
608 p_batch_id=> o_batch_id ,
609 p_receipt_method_id=> l_receipt_method_id ,
610 p_currency_code=> l_currency_code,
611 p_payment_type_code=> p_payment_type_code,
612 p_sob_id=> p_sob_id ,
613 p_remit_method_code=> l_remittance_method,
614 p_remit_bank_account_id=> l_remittance_bank_account_id ,
615 p_return_status=>l_return_status);
616
617
618
619
620 IF PG_DEBUG in ('Y','C') THEN
621 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to create remit rec ' || to_char(o_batch_id));
622 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
623 END IF;
624
625 END IF;
626
627
628 IF p_approve_flag = 'Y' THEN
629 create_and_update_remit_rec(
630 p_batch_id => o_batch_id,
631 p_return_status => cr_return_status
632 );
633
634 /* CALL TO PROCESS PAY RECEIPTS */
635 IF PG_DEBUG in ('Y','C') THEN
636 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to process_pay ' || to_char(o_batch_id));
637 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
638 END IF;
639
640 process_pay_receipt(
641 p_batch_id => o_batch_id,
642 p_called_from =>'AUTOREMAPI',
643 x_msg_count => pl_msg_count,
644 x_msg_data => pl_msg_data,
645 x_return_status => pl_return_status
646 );
647 END IF;
648
649 END IF ; /* end of the else part of create_only */
650
651 END IF ; /* end if of l_flow2 */
652
653
654
655
656 IF l_flow3 = 'Y' THEN
657
658 IF p_approve_flag = 'Y' THEN
659
660 o_batch_id := p_batch_id;
661
662
663 IF PG_DEBUG in ('Y','C') THEN
664 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id in FLOW3 '|| to_char(o_batch_id));
665 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
666 END IF;
667
668
669 create_and_update_remit_rec(
670 p_batch_id => o_batch_id,
671 p_return_status => cr_return_status
672 );
673
674 /* CALL TO PROCESS PAY RECEIPTS */
675 IF PG_DEBUG in ('Y','C') THEN
676 fnd_file.put_line(FND_FILE.LOG, 'value of o_batch_id bef call to process_pay ' || to_char(o_batch_id));
677 fnd_file.put_line(FND_FILE.LOG, ' the error here is ' || SQLERRM );
678 END IF;
679
680 process_pay_receipt(
681 p_batch_id => o_batch_id,
682 p_called_from =>'AUTOREMAPI',
683 x_msg_count => pl_msg_count,
684 x_msg_data => pl_msg_data,
685 x_return_status => pl_return_status
686 );
687
688 END IF;
689
690 END IF; /* end if of l_flow3 */
691 END IF; /* Check if Process is submitted from Master Program */
692
693 IF p_approve_flag = 'Y' THEN
694
695 /* CALL TO CONTROL_CHECK to detect bad receipts */
696
697 control_check ( p_batch_id =>o_batch_id);
698
699 fnd_file.put_line(FND_FILE.LOG,'reset selected_remittance_batch_id for all receipts after approval');
700
701 update ar_cash_receipts
702 set selected_remittance_batch_id = null
703 where selected_remittance_batch_id = o_batch_id
704 and cash_receipt_id in (select cash_receipt_id from
705 ar_cash_receipt_history
706 where request_id = l_request_id
707 and status = 'REMITTED'
708 and current_record_flag = 'Y');
709
710 fnd_file.put_line(FND_FILE.LOG,'selected_remittance_batch_id reset for rows: '||sql%rowcount);
711
712 END IF;
713
714
715 /* CALL TO REC_RESET to delete bad rows */
716
717 IF G_ERROR = 'Y' THEN
718
719 fnd_file.put_line( FND_FILE.LOG, 'CALLING REC_RESET');
720
721 rec_reset (p_request_id => l_request_id);
722
723 END IF;
724
725 /* Bug 5051186 Update the Batch Status */
726 IF TOTAL_WORKERS < 1 THEN
727 IF l_format_flag = 'Y' THEN
728 l_batch_applied_status := 'COMPLETED_FORMAT';
729 ELSIF l_approve_flag = 'Y' then
730 l_batch_applied_status := 'COMPLETED_APPROVAL';
731 ELSIF l_create_flag = 'Y' then
732 l_batch_applied_status := 'COMPLETED_CREATION';
733 END IF;
734
735 /*bug 7352164 update control count and control amount */
736 IF p_create_only = 'Y' THEN
737 SELECT
738 nvl(sum(cr.amount),0),
739 count(*) into
740 l_control_amount,
741 l_control_count
742 FROM ar_cash_receipts cr
743 WHERE cr.selected_remittance_batch_id = o_batch_id;
744
745 update ar_batches
746 SET batch_applied_status = l_batch_applied_status,
747 control_count = l_control_count,
748 control_amount= l_control_amount
749 where batch_id = o_batch_id;
750
751 ELSIF l_approve_flag = 'Y' THEN
752 SELECT
753 nvl(sum(crh.amount),0),
754 count(*) into
755 l_control_amount,
756 l_control_count
757 FROM ar_cash_receipt_history crh
758 WHERE crh.batch_id = o_batch_id
759 AND crh.status = 'REMITTED'
760 AND crh.current_record_flag = 'Y';
761
762 update ar_batches
763 SET batch_applied_status = l_batch_applied_status,
764 control_count = l_control_count,
765 control_amount= l_control_amount
766 where batch_id = o_batch_id;
767 ELSE
768 /* No need to update counts if the batch is submitted for format only */
769 update ar_batches
770 SET batch_applied_status = l_batch_applied_status
771 where batch_id = o_batch_id;
772 END IF;
773 END IF;
774
775 /* CALL TO SUBMIT_REPORT */
776
777 /* SUBMIT THE FINAL REPORT FULL WITH ERRORS AND EXECUTION */
778 fnd_file.put_line(FND_FILE.LOG,'calling the report- batch_id for create only ' || o_batch_id);
779 fnd_file.put_line(FND_FILE.LOG,'calling the report ' || l_request_id);
780
781 submit_report ( p_batch_id =>o_batch_id,
782 p_request_id => l_request_id);
783
784
785 /* START FORMATTING */
786 IF TOTAL_WORKERS < 1 THEN
787 IF l_format_flag = 'Y' THEN
788 fnd_file.put_line(FND_FILE.LOG,
789 'calling the report- batch_id format ' || o_batch_id);
790 submit_format ( p_batch_id =>o_batch_id);
791 END IF;
792 END IF;
793 /* END FORMATTING */
794
795
796 IF PG_DEBUG in ('Y', 'C') THEN
797 fnd_file.put_line(FND_FILE.LOG,' COMMITING WORK - ALL REMIT RECS ');
798 END IF;
799
800 COMMIT;
801
802
803
804
805
806
807 EXCEPTION
808 WHEN others THEN
809 IF PG_DEBUG in ('Y', 'C') THEN
810 fnd_file.put_line(FND_FILE.LOG,'Exception : autoremapi() '|| SQLERRM);
811
812 fnd_file.put_line(FND_FILE.LOG,'Exception : autoremapi() ');
813 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
814 fnd_file.put_line(FND_FILE.LOG, 'value of p_errbuf ' || P_ERRBUF);
815 fnd_file.put_line(FND_FILE.LOG, 'value of p_retcode ' || (P_RETCODE));
816 fnd_file.put_line(FND_FILE.LOG, 'value of p_process_type ' || p_process_type);
817 fnd_file.put_line(FND_FILE.LOG, 'value of p_create_flag ' || p_create_flag);
818 fnd_file.put_line(FND_FILE.LOG, 'value of p_approve_flag ' || p_approve_flag);
819 fnd_file.put_line(FND_FILE.LOG, 'value of p_format_flag ' || p_format_flag);
820 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_id ' || (p_batch_id));
821 fnd_file.put_line(FND_FILE.LOG, 'value of p_debug_mode_on ' || p_debug_mode_on);
822 fnd_file.put_line(FND_FILE.LOG, 'value of p_receipt_class_id ' || p_receipt_class_id);
823 fnd_file.put_line(FND_FILE.LOG, 'value of p_payment_method_id ' || p_payment_method_id);
824 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_currency ' || p_batch_currency);
825 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_date ' || p_batch_date);
826 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_gl_date ' || p_batch_gl_date);
827 fnd_file.put_line(FND_FILE.LOG, 'value of p_comments ' || p_comments);
828 fnd_file.put_line(FND_FILE.LOG, 'value of p_exchange_date ' || p_exchange_date);
829 fnd_file.put_line(FND_FILE.LOG, 'value of p_exchange_rate ' || p_exchange_rate);
830 fnd_file.put_line(FND_FILE.LOG, 'value of p_exchange_rate_type ' || p_exchange_rate_type);
831 fnd_file.put_line(FND_FILE.LOG, 'value of p_media_reference ' || p_media_reference);
832 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_method_code ' || p_remit_method_code);
833 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_bank_branch_id ' || p_remit_bank_branch_id);
834 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_bank_account_id ' || p_remit_bank_account_id);
835 fnd_file.put_line(FND_FILE.LOG, 'value of p_remit_bank_deposit_number ' || p_remit_bank_deposit_number);
836 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_date_l ' || p_trx_date_l);
837 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_date_h ' || p_trx_date_h);
838 fnd_file.put_line(FND_FILE.LOG, 'value of p_due_date_l ' || p_due_date_l);
839 fnd_file.put_line(FND_FILE.LOG, 'value of p_due_date_h ' || p_due_date_h);
840 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_num_l ' || p_trx_num_l);
841 fnd_file.put_line(FND_FILE.LOG, 'value of p_trx_num_h ' || p_trx_num_h);
842 fnd_file.put_line(FND_FILE.LOG, 'value of p_doc_num_l ' || p_doc_num_l);
843 fnd_file.put_line(FND_FILE.LOG, 'value of p_doc_num_h ' || p_doc_num_h);
844 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_number_l ' || p_customer_number_l);
845 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_number_h ' || p_customer_number_h);
846 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_name_l ' || p_customer_name_l);
847 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_name_h ' || p_customer_name_h);
848 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_id ' || (p_customer_id));
849 fnd_file.put_line(FND_FILE.LOG, 'value of p_site_l ' || p_site_l);
850 fnd_file.put_line(FND_FILE.LOG, 'value of p_site_h ' || p_site_h);
851 fnd_file.put_line(FND_FILE.LOG, 'value of p_site_id ' || (p_site_id));
852 fnd_file.put_line(FND_FILE.LOG, 'value of p_remittance_total_from ' || p_remittance_total_from);
853 fnd_file.put_line(FND_FILE.LOG, 'value of p_Remittance_total_to ' || p_remittance_total_to);
854 fnd_file.put_line(FND_FILE.LOG, 'value of p_billing_number_l ' || p_billing_number_l);
855 fnd_file.put_line(FND_FILE.LOG, 'value of p_billing_number_h ' || p_billing_number_h);
856 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_bank_acc_num_l ' || p_customer_bank_acc_num_l);
857 fnd_file.put_line(FND_FILE.LOG, 'value of p_customer_bank_acc_num_h ' || p_customer_bank_acc_num_h);
858 END IF;
859 raise;
860
861 END get_parameters;
862
863 /*========================================================================+
864 | PROCEDURE submit_autorem_parallel |
865 | |
866 | DESCRIPTION |
867 | |
868 | Wraper to parallelize the Automatic Remittances creation program |
869 | PSEUDO CODE/LOGIC |
870 | |
871 | PARAMETERS |
872 | |
873 | |
874 | KNOWN ISSUES |
875 | |
876 | NOTES |
877 | |
878 | |
879 | MODIFICATION HISTORY |
880 | Date Author Description of Changes |
881 | 05-FEB-2008 aghoraka Created |
882 *=========================================================================*/
883 PROCEDURE submit_autorem_parallel(
884 P_ERRBUF OUT NOCOPY VARCHAR2,
885 P_RETCODE OUT NOCOPY NUMBER,
886 p_process_type IN VARCHAR2,
887 p_batch_date IN VARCHAR2,
888 p_batch_gl_date IN VARCHAR2,
889 p_create_flag IN VARCHAR2,
890 p_approve_flag IN VARCHAR2,
891 p_format_flag IN VARCHAR2,
892 p_batch_id IN VARCHAR2,
893 p_debug_mode_on IN VARCHAR2,
894 p_batch_currency IN VARCHAR2,
895 p_exchange_date IN VARCHAR2,
896 p_exchange_rate IN VARCHAR2,
897 p_exchange_rate_type IN VARCHAR2,
898 p_remit_method_code IN VARCHAR2,
899 p_receipt_class_id IN VARCHAR2,
900 p_payment_method_id IN VARCHAR2,
901 p_media_reference IN VARCHAR2,
902 p_remit_bank_branch_id IN VARCHAR2,
903 p_remit_bank_account_id IN VARCHAR2,
904 p_remit_bank_deposit_number IN VARCHAR2,
905 p_comments IN VARCHAR2,
906 p_trx_date_l IN VARCHAR2,
907 p_trx_date_h IN VARCHAR2,
908 p_due_date_l IN VARCHAR2,
909 p_due_date_h IN VARCHAR2,
910 p_trx_num_l IN VARCHAR2,
911 p_trx_num_h IN VARCHAR2,
912 p_doc_num_l IN VARCHAR2,
913 p_doc_num_h IN VARCHAR2,
914 p_customer_number_l IN VARCHAR2,
915 p_customer_number_h IN VARCHAR2,
916 p_customer_name_l IN VARCHAR2,
917 p_customer_name_h IN VARCHAR2,
918 p_customer_id IN VARCHAR2,
919 p_site_l IN VARCHAR2,
920 p_site_h IN VARCHAR2,
921 p_site_id IN VARCHAR2,
922 p_remittance_total_from IN VARCHAR2,
923 p_Remittance_total_to IN VARCHAR2,
924 p_billing_number_l IN VARCHAR2,
925 p_billing_number_h IN VARCHAR2,
926 p_customer_bank_acc_num_l IN VARCHAR2,
927 p_customer_bank_acc_num_h IN VARCHAR2,
928 p_total_workers IN NUMBER default 1 )
929 AS
930
931 l_worker_number NUMBER ;
932 l_complete BOOLEAN := FALSE;
933 l_count NUMBER;
934 l_request_id ar_cash_receipts.request_id%TYPE;
935 l_batch_date ar_cash_receipts.receipt_date%TYPE ;
936 l_gl_date ar_cash_receipt_history.gl_date%TYPE;
937 l_approve_flag ar_cash_receipts.confirmed_flag%TYPE ;
938 l_format_flag ar_cash_receipts.confirmed_flag%TYPE ;
939 l_create_flag ar_cash_receipts.confirmed_flag%TYPE ;
940 l_currency_code ar_cash_receipts.currency_code%TYPE;
941 l_remittance_method ar_receipt_classes.remit_method_code%TYPE;
942 l_receipt_class_id ar_receipt_classes.receipt_class_id%TYPE ;
943 l_receipt_method_id ar_cash_receipts.receipt_method_id%TYPE ;
944 l_remittance_bank_branch_id ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL;
945 l_remittance_bank_account_id ar_receipt_method_accounts.REMIT_BANK_ACCT_USE_ID%TYPE DEFAULT NULL;
946 l_batch_applied_status ar_batches.batch_applied_status%TYPE;
947 l_return_status VARCHAR2(1);
948 o_batch_id NUMBER;
949 l_ins_stmt VARCHAR2(5000);
950 ins_rec INTEGER;
951 ignore INTEGER;
952 /* bug 7352164*/
953 l_control_count number;
954 l_control_amount number;
955
956 p_payment_type_code ar_receipt_methods.payment_type_code%type;
957 p_sob_id ar_batches.set_of_books_id%type;
958
959 TYPE req_status_typ IS RECORD (
960 request_id NUMBER(15),
961 dev_phase VARCHAR2(255),
962 dev_status VARCHAR2(255),
963 message VARCHAR2(2000),
964 phase VARCHAR2(255),
965 status VARCHAR2(255));
966 l_org_id NUMBER;
967
968 TYPE req_status_tab_typ IS TABLE OF req_status_typ INDEX BY BINARY_INTEGER;
969
970 l_req_status_tab req_status_tab_typ;
971
972 PROCEDURE submit_subrequest (p_worker_number IN NUMBER,
973 p_org_id IN NUMBER) IS
974 BEGIN
975 fnd_file.put_line( FND_FILE.LOG, 'submit_subrequest()+' );
976
977 FND_REQUEST.SET_ORG_ID(p_org_id);
978
979 l_request_id := FND_REQUEST.submit_request( 'AR', 'AUTOREMAPI',
980 '',
981 SYSDATE,
982 FALSE,
983 p_process_type ,
984 p_batch_date ,
985 p_batch_gl_date ,
986 p_create_flag ,
987 p_approve_flag ,
988 p_format_flag,
989 to_char(o_batch_id) ,
990 p_debug_mode_on ,
991 p_batch_currency,
992 p_exchange_date,
993 p_exchange_rate,
994 p_exchange_rate_type ,
995 p_remit_method_code,
996 p_receipt_class_id,
997 p_payment_method_id,
998 p_media_reference,
999 p_remit_bank_branch_id,
1000 p_remit_bank_account_id,
1001 p_remit_bank_deposit_number,
1002 p_comments,
1003 p_trx_date_l,
1004 p_trx_date_h,
1005 p_due_date_l,
1006 p_due_date_h,
1007 p_trx_num_l,
1008 p_trx_num_h,
1009 p_doc_num_l,
1010 p_doc_num_h,
1011 p_customer_number_l,
1012 p_customer_number_h,
1013 p_customer_name_l,
1014 p_customer_name_h,
1015 p_customer_id,
1016 p_site_l,
1017 p_site_h,
1018 p_site_id,
1019 p_remittance_total_from,
1020 p_Remittance_total_to,
1021 p_billing_number_l,
1022 p_billing_number_h,
1023 p_customer_bank_acc_num_l,
1024 p_customer_bank_acc_num_h,
1025 p_worker_number,
1026 p_total_workers );
1027
1028 IF (l_request_id = 0) THEN
1029 arp_util.debug('Can not start for worker_id: ' ||p_worker_number );
1030 P_ERRBUF := fnd_Message.get;
1031 P_RETCODE := 2;
1032 return;
1033 ELSE
1034 commit;
1035 arp_util.debug('Child request id: ' ||l_request_id || ' started for
1036 worker_id: ' ||p_worker_number );
1037 END IF;
1038
1039 l_req_status_tab(p_worker_number).request_id := l_request_id;
1040
1041 fnd_file.put_line( FND_FILE.LOG, 'submit_subrequest()-');
1042
1043 END submit_subrequest;
1044
1045 BEGIN
1046 fnd_file.put_line( FND_FILE.LOG, 'submit_autorem_parallel()+');
1047
1048 l_batch_date := fnd_date.canonical_to_date(p_batch_date);
1049 l_gl_date := fnd_date.canonical_to_date(p_batch_gl_date);
1050 l_create_flag := p_create_flag;
1051 l_approve_flag := p_approve_flag;
1052 l_format_flag := p_format_flag;
1053 l_currency_code := p_batch_currency;
1054 l_remittance_method := p_remit_method_code;
1055 l_receipt_class_id := to_number(p_receipt_class_id);
1056 l_receipt_method_id := to_number(p_payment_method_id);
1057 l_remittance_bank_branch_id := to_number(p_remit_bank_branch_id);
1058 l_remittance_bank_account_id := to_number(p_remit_bank_account_id);
1059
1060 -- These validations should ideally be performed at form level.
1061 IF p_batch_id IS NOT NULL THEN
1062 SELECT batch_applied_status
1063 INTO l_batch_applied_status
1064 FROM ar_batches
1065 WHERE batch_id = p_batch_id;
1066
1067 fnd_file.put_line( FND_FILE.LOG, 'Batch_Applied_Status : ' || l_batch_applied_status);
1068
1069 IF l_batch_applied_status = 'COMPLETED_CREATION' THEN
1070 IF l_create_flag = 'Y' THEN
1071 arp_standard.debug( 'This is an error condition');
1072 arp_standard.debug('Batch is already created.');
1073 fnd_file.put_line( FND_FILE.LOG, 'Batch is already Created.');
1074 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1075 APP_EXCEPTION.raise_exception;
1076 ELSIF l_approve_flag = 'N' AND l_format_flag = 'Y' THEN
1077 arp_standard.debug( 'This is an error condition');
1078 arp_standard.debug('Batch should be Approved before Formatting.');
1079 fnd_file.put_line( FND_FILE.LOG, 'Batch should be Approved before Formatting.');
1080 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1081 APP_EXCEPTION.raise_exception;
1082 END IF;
1083 ELSIF l_batch_applied_status = 'COMPLETED_APPROVAL' AND
1084 (l_create_flag = 'Y' OR l_approve_flag = 'Y') THEN
1085 arp_standard.debug( 'This is an error condition');
1086 arp_standard.debug( 'Batch is already Approved.');
1087 fnd_file.put_line( FND_FILE.LOG, 'Batch is already Approved.');
1088 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1089 APP_EXCEPTION.raise_exception;
1090 ELSIF l_batch_applied_status = 'COMPLETED_FORMAT' AND
1091 (l_create_flag = 'Y' OR l_approve_flag = 'Y' )THEN
1092 arp_standard.debug( 'This is an error condition');
1093 arp_standard.debug( 'Batch is already Formatted.');
1094 fnd_file.put_line( FND_FILE.LOG, 'Batch is already Formatted.');
1095 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1096 APP_EXCEPTION.raise_exception;
1097 END IF;
1098
1099 END IF;
1100 --Create a batch if the batch id is null
1101 IF p_batch_id is NULL and l_create_flag = 'Y' THEN
1102 insert_batch( l_batch_date,
1103 l_gl_date,
1104 l_approve_flag,
1105 l_format_flag,
1106 l_currency_code,
1107 l_remittance_method,
1108 l_receipt_class_id,
1109 l_receipt_method_id,
1110 l_remittance_bank_branch_id,
1111 l_remittance_bank_account_id,
1112 o_batch_id
1113 );
1114 if p_payment_method_id is not null then
1115 select rm.payment_channel_code,b.set_of_books_id
1116 into p_payment_type_code, p_sob_id
1117 from ar_receipt_methods rm,
1118 ar_batches b
1119 where rm.receipt_method_id = p_payment_method_id
1120 and b.receipt_method_id = rm.receipt_method_id
1121 and b.batch_id = o_batch_id;
1122 else
1123 p_payment_type_code := null;
1124 select b.set_of_books_id
1125 into p_sob_id
1126 from ar_batches b
1127 where b.batch_id = o_batch_id;
1128 end if;
1129 select_update_rec(
1130 p_customer_number_l=> p_customer_number_l,
1131 p_customer_number_h=> p_customer_number_h,
1132 p_trx_date_l=> fnd_date.canonical_to_date(p_trx_date_l),
1133 p_trx_date_h=> fnd_date.canonical_to_date(p_trx_date_h),
1134 p_due_date_l=> fnd_date.canonical_to_date(p_due_date_l),
1135 p_due_date_h=> fnd_date.canonical_to_date(p_due_date_h),
1136 p_trx_num_l=> p_trx_num_l,
1137 p_trx_num_h=> p_trx_num_h,
1138 p_remittance_total_to=> p_remittance_total_to,
1139 p_remittance_total_from=> p_remittance_total_from,
1140 p_batch_id=> o_batch_id ,
1141 p_receipt_method_id=> l_receipt_method_id ,
1142 p_currency_code=> p_batch_currency,
1143 p_payment_type_code=> p_payment_type_code,
1144 p_sob_id=> p_sob_id ,
1145 p_remit_method_code=> p_remit_method_code,
1146 p_remit_bank_account_id=> l_remittance_bank_account_id ,
1147 p_return_status=>l_return_status);
1148
1149 ELSIF p_batch_id IS NULL and l_create_flag = 'N' THEN
1150 arp_standard.debug( 'This is an error condition');
1151 arp_standard.debug( 'Batch Name is not supplied.');
1152 fnd_file.put_line( FND_FILE.LOG, 'Create is No and No Batch Name is Supplied.');
1153 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
1154 APP_EXCEPTION.raise_exception;
1155 ELSE
1156 o_batch_id := p_batch_id;
1157 END IF;
1158
1159 --Error condition
1160 IF o_batch_id IS NULL THEN
1161 arp_standard.debug( 'This is an error condition');
1162 insert_exceptions( p_batch_id => -333,
1163 p_request_id =>l_request_id,
1164 p_exception_code => 'NO_BATCH',
1165 p_additional_message => 'Error during inserting the
1166 batch' );
1167
1168 arp_standard.debug ( 'calling the report - batch_id ' || -333 );
1169 arp_standard.debug ( 'calling the report ' || l_request_id);
1170
1171 submit_report ( p_batch_id => o_batch_id,
1172 p_request_id => l_request_id);
1173 RETURN;
1174 END IF;
1175 BEGIN
1176 l_count := 0;
1177 select count(*)
1178 INTO l_count
1179 from AR_CASH_RECEIPTS
1180 where selected_remittance_batch_id = o_batch_id;
1181 EXCEPTION
1182 when no_data_found THEN
1183 l_count :=0;
1184 END;
1185 --fetch org id,need to set it for child requests
1186 SELECT org_id
1187 INTO l_org_id
1188 FROM ar_system_parameters;
1189 fnd_file.put_line( FND_FILE.LOG, 'Populating interim table ....'||l_count);
1190 IF l_count > 0 THEN
1191 l_ins_stmt := 'INSERT /*+ append */ INTO ar_autorem_interim
1192 (receipt_number, --cr.receipt_number
1193 cash_receipt_id,
1194 creation_status,--rc.creation_status
1195 org_id, -- cr.org_id
1196 party_id,
1197 payment_channel_code,
1198 merchant_ref,
1199 currency_code,
1200 pay_from_customer,
1201 customer_site_use_id,
1202 cash_receipt_history_id,
1203 exchange_date,
1204 exchange_rate,
1205 exchange_rate_type,
1206 amount1,
1207 acctd_amount,
1208 factor_discount_amount,
1209 remittance_ccid,
1210 bank_charges_ccid,
1211 code_combination_id,
1212 crh_gl_date,
1213 crh_trx_date,
1214 payment_server_order_num,
1215 approval_code,
1216 unique_ref,
1217 customer_bank_account_id,
1218 payment_trxn_extension_id,
1219 amount2,
1220 batch_id,
1221 current_worker
1222 ) ';
1223 IF PG_PARALLEL IN ('Y', 'C') THEN
1224 l_ins_stmt := l_ins_stmt ||
1225 'SELECT /*+ leading(gtt,crh) cardinality(gtt 1000) swap_join_inputs(bat) use_hash(bat) parallel(gtt) parallel(crh) use_nl(crh,cr,hca,party,ps,d) */';
1226 ELSE
1227 l_ins_stmt := l_ins_stmt ||
1228 'SELECT /*+ leading(gtt,crh) cardinality(gtt 1000) swap_join_inputs(bat) use_hash(bat) use_nl(crh,cr,hca,party,ps,d) */';
1229 END IF;
1230 l_ins_stmt := l_ins_stmt || ' cr.receipt_number,
1231 cr.cash_receipt_id,
1232 rc.creation_status,
1233 cr.org_id,
1234 party.party_id,
1235 rm.payment_channel_code,
1236 rm.merchant_ref,
1237 cr.currency_code,
1238 cr.pay_from_customer,
1239 cr.customer_site_use_id,
1240 crh.cash_receipt_history_id,
1241 crh.exchange_date,
1242 crh.exchange_rate,
1243 crh.exchange_rate_type,
1244 crh.amount amount1,
1245 crh.acctd_amount,
1246 nvl(cr.factor_discount_amount,0) factor_discount_amount,
1247 decode( bat.remit_method_code,
1248 ''FACTORING'', rma.factor_ccid,
1249 rma.remittance_ccid) remittance_ccid,
1250 rma.bank_charges_ccid,
1251 d.code_combination_id,
1252 greatest((bat.gl_date), (crh.trx_date)) crh_gl_date,
1253 greatest((bat.batch_date), (crh.trx_date)) crh_trx_date,
1254 cr.payment_server_order_num,
1255 cr.approval_code,
1256 DECODE(cr.unique_reference,
1257 NULL,SYS_GUID(),
1258 cr.unique_reference ) unique_ref,
1259 cr.customer_bank_account_id,
1260 cr.payment_trxn_extension_id,
1261 cr.amount amount2,
1262 cr.selected_remittance_batch_id,
1263 --MOD(cr.cash_receipt_id, p_total_workers) + 1
1264 --MOD(CEIL(cr.cash_receipt_id/10000), p_total_workers) + 1
1265 MOD(CEIL((DENSE_RANK() over(order by crh.cash_receipt_id))/5000), :b_total_workers) + 1
1266 FROM ar_rem_cr_id_gtt gtt,
1267 ar_cash_receipts cr,
1268 ar_receipt_methods rm,
1269 ar_receipt_classes rc,
1270 hz_cust_accounts hca,
1271 hz_parties party,
1272 /*ar_remit_gt g,*/
1273 ar_cash_receipt_history crh,
1274 ar_batches bat,
1275 ar_receipt_method_accounts rma,
1276 ar_payment_schedules ps,
1277 ar_distributions d
1278 WHERE cr.selected_remittance_batch_id = :b_batch_id
1279 AND cr.REMIT_BANK_ACCT_USE_ID = rma.REMIT_BANK_ACCT_USE_ID
1280 AND bat.batch_id = cr.selected_remittance_batch_id
1281 AND rm.receipt_method_id = cr.receipt_method_id
1282 AND rma.receipt_method_id = cr.receipt_method_id
1283 AND cr.cash_receipt_id = crh.cash_receipt_id
1284 AND crh.current_record_flag = ''Y''
1285 AND ps.cash_receipt_id(+) = cr.cash_receipt_id
1286 AND crh.status = ''CONFIRMED''
1287 AND crh.cash_receipt_history_id = d.source_id
1288 AND d.source_type = ''CONFIRMATION''
1289 AND d.source_table = ''CRH''
1290 AND hca.party_id = party.party_id(+)
1291 AND hca.cust_account_id(+) = cr.pay_from_customer
1292 AND cr.receipt_method_id = rm.receipt_method_id
1293 AND rm.receipt_class_id = rc.receipt_class_id
1294 and gtt.CASH_RECEIPT_HISTORY_ID = CRH.CASH_RECEIPT_HISTORY_ID ';
1295
1296 commit;
1297 execute immediate 'alter session enable parallel dml';
1298 ins_rec := dbms_sql.open_cursor();
1299 dbms_sql.parse (ins_rec, l_ins_stmt, dbms_sql.v7);
1300 dbms_sql.bind_variable( ins_rec, ':b_total_workers', p_total_workers);
1301 dbms_sql.bind_variable( ins_rec, ':b_batch_id', o_batch_id);
1302 ignore := dbms_sql.execute(ins_rec);
1303 commit;
1304
1305 --Invoke the child programs
1306 FOR l_worker_number IN 1..p_total_workers LOOP
1307 fnd_file.put_line(FND_FILE.LOG,'worker # : ' || l_worker_number );
1308 submit_subrequest (l_worker_number,l_org_id);
1309 END LOOP;
1310
1311 arp_standard.debug ( 'The Master program waits for child processes');
1312
1313 -- Wait for the completion of the submitted requests
1314 FOR i in 1..p_total_workers LOOP
1315
1316 l_complete := FND_CONCURRENT.WAIT_FOR_REQUEST(
1317 request_id => l_req_status_tab(i).request_id,
1318 interval => 30,
1319 max_wait =>144000,
1320 phase =>l_req_status_tab(i).phase,
1321 status =>l_req_status_tab(i).status,
1322 dev_phase =>l_req_status_tab(i).dev_phase,
1323 dev_status =>l_req_status_tab(i).dev_status,
1324 message =>l_req_status_tab(i).message);
1325
1326 IF l_req_status_tab(i).dev_phase <> 'COMPLETE' THEN
1327 P_RETCODE := 2;
1328 arp_util.debug('Worker # '|| i||' has a phase
1329 '||l_req_status_tab(i).dev_phase);
1330
1331 ELSIF l_req_status_tab(i).dev_phase = 'COMPLETE'
1332 AND l_req_status_tab(i).dev_status <> 'NORMAL' THEN
1333 P_RETCODE := 2;
1334 arp_util.debug('Worker # '|| i||' completed with status
1335 '||l_req_status_tab(i).dev_status);
1336 ELSE
1337 arp_util.debug('Worker # '|| i||' completed successfully');
1338 END IF;
1339
1340 END LOOP;
1341
1342 delete from ar_autorem_interim where batch_id = o_batch_id;
1343 END IF;
1344 /* Bug 5051186 Update the Batch Status */
1345 IF l_format_flag = 'Y' THEN
1346 l_batch_applied_status := 'COMPLETED_FORMAT';
1347 ELSIF l_approve_flag = 'Y' then
1348 l_batch_applied_status := 'COMPLETED_APPROVAL';
1349 ELSIF l_create_flag = 'Y' then
1350 l_batch_applied_status := 'COMPLETED_CREATION';
1351 END IF;
1352
1353 /* bug 7352164 update control count and control amount */
1354 IF ( l_create_flag = 'Y' AND l_approve_flag = 'N' AND l_format_flag = 'N') THEN
1355 SELECT
1356 nvl(sum(cr.amount),0),
1357 count(*) into
1358 l_control_amount,
1359 l_control_count
1360 FROM ar_cash_receipts cr
1361 WHERE cr.selected_remittance_batch_id = o_batch_id;
1362
1363 UPDATE ar_batches
1364 SET batch_applied_status = l_batch_applied_status,
1365 control_count = l_control_count,
1366 control_amount= l_control_amount
1367 WHERE batch_id = o_batch_id;
1368
1369 ELSIF l_approve_flag = 'Y' THEN
1370 SELECT
1371 nvl(sum(crh.amount),0),
1372 count(*) into
1373 l_control_amount,
1374 l_control_count
1375 FROM ar_cash_receipt_history crh
1376 WHERE crh.batch_id = o_batch_id
1377 AND crh.status = 'REMITTED'
1378 AND crh.current_record_flag = 'Y';
1379
1380 UPDATE ar_batches
1381 SET batch_applied_status = l_batch_applied_status,
1382 control_count = l_control_count,
1383 control_amount= l_control_amount
1384 WHERE batch_id = o_batch_id;
1385 ELSE
1386 /* No need to update counts if the batch is submitted for format only */
1387 UPDATE ar_batches
1388 SET batch_applied_status = l_batch_applied_status
1389 WHERE batch_id = o_batch_id;
1390 END IF;
1391
1392 /* START FORMATTING */
1393 IF l_format_flag = 'Y' THEN
1394 fnd_file.put_line(FND_FILE.LOG,'calling the report- batch_id format ' || o_batch_id);
1395 SUBMIT_FORMAT ( p_batch_id =>o_batch_id);
1396 END IF;
1397 /* END FORMATTING */
1398
1399
1400 IF PG_DEBUG in ('Y', 'C') THEN
1401 fnd_file.put_line(FND_FILE.LOG,' COMMITING WORK - ALL REMIT RECS ');
1402 END IF;
1403
1404 COMMIT;
1405
1406 fnd_file.put_line( FND_FILE.LOG, 'submit_autorem_parallel()-');
1407
1408 EXCEPTION
1409
1410 WHEN OTHERS THEN
1411 RAISE ;
1412
1413 END submit_autorem_parallel;
1414
1415 /*========================================================================+
1416 | PROCEDURE insert_batch |
1417 | |
1418 | DESCRIPTION |
1419 | |
1420 | This procedure is used to insert the batch record when called from |
1421 | srs. It also gets the other required parameters from sysparm |
1422 | and conc program |
1423 | PSEUDO CODE/LOGIC |
1424 | |
1425 | PARAMETERS |
1426 | |
1427 | |
1428 | KNOWN ISSUES |
1429 | |
1430 | NOTES |
1431 | |
1432 | |
1433 | MODIFICATION HISTORY |
1434 | Date Author Description of Changes |
1435 | 16-JUL-2005 bichatte Created |
1436 *=========================================================================*/
1437
1438 PROCEDURE insert_batch(
1439 p_batch_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
1440 p_batch_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
1441 p_approve_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1442 p_format_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
1443 p_currency_code IN ar_batches.currency_code%TYPE,
1444 p_remmitance_method IN ar_batches.remit_method_code%TYPE,
1445 p_receipt_class_id IN ar_receipt_classes.receipt_class_id%TYPE,
1446 p_payment_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
1447 p_remmitance_bank_branch_id IN ap_bank_accounts.bank_branch_id%TYPE DEFAULT NULL,
1448 p_remmitance_bank_account_id IN ar_receipt_method_accounts.REMIT_BANK_ACCT_USE_ID%TYPE DEFAULT NULL,
1449 p_batch_id OUT NOCOPY NUMBER
1450 ) IS
1451 l_batch_rec ar_batches%ROWTYPE;
1452 l_row_id VARCHAR2(50);
1453 l_batch_id NUMBER := NULL;
1454 l_batch_name VARCHAR2(30);
1455 l_batch_applied_status VARCHAR2(30);
1456 batch_id NUMBER := NULL;
1457 p_receipt_method_id NUMBER;
1458 p_batch_remit_method_code VARCHAR2(30);
1459 p_batch_remit_bank_br_id NUMBER (18);
1460 p_batch_remit_bank_acc_id NUMBER(18);
1461 p_batch_bank_deposit_num VARCHAR2(30);
1462 p_batch_exchange_date DATE ;
1463 p_batch_exchange_rate NUMBER;
1464 p_batch_exchange_rate_type VARCHAR2(10);
1465
1466 psite_required VARCHAR2(2);
1467 pinvoices_per_commit NUMBER;
1468 preceipts_per_commit NUMBER;
1469 pfunctional_currency VARCHAR2(20);
1470 pacc_method VARCHAR2(20);
1471
1472 l_return_status VARCHAR2(1);
1473 l_msg_count NUMBER;
1474 l_msg_data VARCHAR2(240);
1475 l_count NUMBER;
1476
1477 l_request_id NUMBER;
1478 l_last_updated_by NUMBER;
1479 l_created_by NUMBER;
1480 l_last_update_login NUMBER;
1481 l_program_application_id NUMBER;
1482 l_program_id NUMBER;
1483
1484
1485
1486
1487 BEGIN
1488 IF PG_DEBUG in ('Y', 'C') THEN
1489 fnd_file.put_line(FND_FILE.LOG,'autoremapi start ()+');
1490 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_date ' ||p_batch_date);
1491 fnd_file.put_line(FND_FILE.LOG, 'value of p_gl_date ' ||p_batch_gl_date);
1492 fnd_file.put_line(FND_FILE.LOG, 'value of p_approve_flag ' ||p_approve_flag);
1493 fnd_file.put_line(FND_FILE.LOG, 'value of p_format_flag ' ||p_format_flag);
1494 fnd_file.put_line(FND_FILE.LOG, 'value of p_currency_code ' ||p_currency_code);
1495 fnd_file.put_line(FND_FILE.LOG, 'value of p_remmitance_method ' ||p_remmitance_method);
1496 fnd_file.put_line(FND_FILE.LOG, 'value of p_receipt_class ' ||p_receipt_class_id);
1497 fnd_file.put_line(FND_FILE.LOG, 'value of p_payment_method ' ||p_payment_method_id);
1498 fnd_file.put_line(FND_FILE.LOG, 'value of p_remmitance_bank_branch ' || p_remmitance_bank_branch_id);
1499 fnd_file.put_line(FND_FILE.LOG, 'value of p_remmitance_bank_account ' || p_remmitance_bank_account_id);
1500
1501 END IF;
1502
1503 --p_batch_id := 10;
1504
1505
1506 l_request_id := arp_standard.profile.request_id;
1507 l_last_updated_by := arp_standard.profile.last_update_login ;
1508 l_created_by := arp_standard.profile.user_id ;
1509 l_last_update_login := arp_standard.profile.last_update_login ;
1510 l_program_application_id := arp_standard.application_id ;
1511 l_program_id := arp_standard.profile.program_id;
1512
1513 IF PG_DEBUG in ('Y', 'C') THEN
1514 fnd_file.put_line(FND_FILE.LOG,'autoremapi calling auto_batch ()+');
1515 END IF;
1516
1517
1518
1519
1520 /* here we will have to add something like this */
1521
1522
1523 /*
1524 select bbt.bank_branch_id ,bbt.remit_account_id
1525 from ap_bank_branches bbt
1526 where bbt.bank_branch_id in
1527 (select distinct bb.bank_branch_id
1528 from ap_bank_accounts ba, ap_bank_branches bb, ar_receipt_method_accounts rma, ar_system_parameters asp
1529 where rma.receipt_method_id = nvl(p_receipt_method_id, rma.receipt_method_id )
1530 and rma.bank_account_id = ba.bank_account_id
1531 and ( ba.currency_code = p_currency_code
1532 or ba.receipt_multi_currency_flag = 'Y' )
1533 and ba.set_of_books_id = asp.set_of_books_id
1534 and nvl(ba.inactive_date , fnd_date.canonical_to_date(p_batch_date) + 1 ) > fnd_date.canonical_to_date(p_batch_date)
1535 and fnd_date.canonical_to_date(p_batch_date)
1536 between rma.start_date
1537 and nvl(rma.end_date,fnd_date.canonical_to_date(p_batch_date))
1538 and ( bb.bank_branch_id = ba.bank_branch_id
1539 or ( bb.institution_type = 'CLEARING HOUSE'
1540 and exists
1541 ( select 1
1542 from ar_receipt_method_accounts rma2, ap_bank_accounts ba2, ap_bank_branches bb2
1543 where rma2.receipt_method_id = nvl(p_receipt_method_id, rma2.receipt_method_id)
1544 and rma2.bank_account_id = ba2.bank_account_id
1545 and ba2.set_of_books_id = asp.set_of_books_id
1546 and ba2.currency_code = p_currency_code
1547 and ba2.bank_branch_id = bb2.bank_branch_id
1548 and bb2.clearing_house_id = bb.bank_branch_id) ) ) )
1549 order by bbt.bank_branch_name, bbt.bank_name;
1550
1551 */
1552
1553
1554
1555
1556 p_batch_exchange_date := p_batch_date;
1557 p_batch_exchange_rate := 1;
1558 p_batch_exchange_rate_type := 'User';
1559 p_batch_remit_method_code := p_remmitance_method ;
1560 p_batch_bank_deposit_num := null;
1561
1562 IF PG_DEBUG in ('Y', 'C') THEN
1563 fnd_file.put_line(FND_FILE.LOG,'autoremapi start ()+');
1564 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_date ' ||p_batch_date);
1565 fnd_file.put_line(FND_FILE.LOG, 'value of p_gl_date ' ||p_batch_gl_date);
1566 fnd_file.put_line(FND_FILE.LOG, 'value of p_receipt_class_id ' ||p_receipt_class_id);
1567 fnd_file.put_line(FND_FILE.LOG, 'value of p_receipt_method_id' ||p_payment_method_id);
1568 fnd_file.put_line(FND_FILE.LOG, 'value of p_currency_code ' ||p_currency_code);
1569 fnd_file.put_line(FND_FILE.LOG, 'value of p_remmitance_method ' ||p_batch_remit_method_code);
1570 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_rem_bank_br_id ' ||p_remmitance_bank_branch_id);
1571 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_remit_bank_acc_id ' ||p_remmitance_bank_account_id);
1572 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_bank_deposit_num ' || p_batch_bank_deposit_num);
1573
1574 END IF;
1575
1576
1577 l_batch_rec.receipt_class_id := to_number(p_receipt_class_id);
1578 l_batch_rec.receipt_method_id := to_number(p_payment_method_id);
1579 l_batch_rec.batch_date := to_date(p_batch_date,'dd/mm/yy');
1580 l_batch_rec.gl_date := to_date(p_batch_gl_date,'dd/mm/yy');
1581 l_batch_rec.currency_code := p_currency_code;
1582 l_batch_rec.comments := null;
1583 l_batch_rec.exchange_date := to_date(p_batch_exchange_date,'dd/mm/yy');
1584 l_batch_rec.exchange_rate := to_number(p_batch_exchange_rate);
1585 l_batch_rec.exchange_rate_type := p_batch_exchange_rate_type;
1586
1587 l_batch_rec.remit_method_code := p_batch_remit_method_code;
1588 l_batch_rec.remittance_bank_branch_id := to_number(p_remmitance_bank_branch_id);
1589 l_batch_rec.remittance_bank_account_id := to_number(p_remmitance_bank_account_id);
1590 l_batch_rec.bank_deposit_number := p_batch_bank_deposit_num;
1591
1592
1593 arp_rw_batches_pkg.insert_remit_batch(
1594 l_row_id,
1595 l_batch_id,
1596 l_batch_rec.batch_date,
1597 l_batch_rec.currency_code,
1598 l_batch_name, --out
1599 l_batch_rec.comments,
1600 l_batch_rec.exchange_date,
1601 l_batch_rec.exchange_rate,
1602 l_batch_rec.exchange_rate_type,
1603 l_batch_rec.gl_date,
1604 l_batch_rec.media_reference,
1605 l_batch_rec.remit_method_code,
1606 l_batch_rec.receipt_class_id,
1607 l_batch_rec.receipt_method_id,
1608 l_batch_rec.remittance_bank_account_id,
1609 l_batch_rec.remittance_bank_branch_id,
1610 l_batch_rec.bank_deposit_number,
1611 l_batch_rec.attribute_category,
1612 l_batch_rec.attribute1,
1613 l_batch_rec.attribute2,
1614 l_batch_rec.attribute3,
1615 l_batch_rec.attribute4,
1616 l_batch_rec.attribute5,
1617 l_batch_rec.attribute6,
1618 l_batch_rec.attribute7,
1619 l_batch_rec.attribute8,
1620 l_batch_rec.attribute9,
1621 l_batch_rec.attribute10,
1622 l_batch_rec.attribute11,
1623 l_batch_rec.attribute12,
1624 l_batch_rec.attribute13,
1625 l_batch_rec.attribute14,
1626 l_batch_rec.attribute15,
1627 'Y',
1628 l_batch_applied_status, --Out
1629 'AUTOREMSRS',
1630 '1.0'
1631 );
1632
1633 batch_id := to_char(l_batch_id);
1634
1635 IF batch_id IS NULL THEN
1636 arp_standard.debug ('WAIT HERE THE VALUE OF BATCH_ID IS NULL ERROR');
1637 -- G_ERROR := 'Y';
1638 END IF;
1639
1640 IF PG_DEBUG in ('Y', 'C') THEN
1641 fnd_file.put_line(FND_FILE.LOG, 'value of batch_id '||batch_id);
1642 END IF;
1643
1644
1645 IF PG_DEBUG in ('Y', 'C') THEN
1646 fnd_file.put_line(FND_FILE.LOG,'autoremapi calling auto_batch end ()-');
1647 END IF;
1648
1649 p_batch_id := to_number(l_batch_id);
1650
1651 IF p_batch_id IS NULL THEN
1652 arp_standard.debug ('WAIT HERE THE VALUE OF BATCH_ID IS NULL ERROR');
1653 END IF;
1654
1655 IF PG_DEBUG in ('Y', 'C') THEN
1656 fnd_file.put_line(FND_FILE.LOG, 'value of batch_id '||p_batch_id);
1657 END IF;
1658 /* inserted the batch record end */
1659
1660
1661 /* GET THE VALUES from SYSTEM PARAMETERS */
1662 IF PG_DEBUG in ('Y','C') THEN
1663 fnd_file.put_line(FND_FILE.LOG, 'get info from system parameters');
1664 fnd_file.put_line(FND_FILE.LOG, 'value of batch_id '||p_batch_id);
1665 END IF;
1666
1667 BEGIN
1668 SELECT asp.site_required_flag,
1669 asp.auto_rec_invoices_per_commit,
1670 asp.auto_rec_receipts_per_commit,
1671 gsob.currency_code,
1672 asp.accounting_method
1673 INTO psite_required,
1674 pinvoices_per_commit,
1675 preceipts_per_commit,
1676 pfunctional_currency,
1677 pacc_method
1678 FROM ar_system_parameters asp,
1679 gl_sets_of_books gsob,
1680 ar_batches ab
1681 WHERE ab.batch_id = p_batch_id
1682 AND ab.set_of_books_id = gsob.set_of_books_id
1683 AND gsob.set_of_books_id = asp.set_of_books_id;
1684 EXCEPTION
1685 WHEN no_data_found THEN
1686 fnd_file.put_line(FND_FILE.LOG, 'ERROR NO DATA FOUND IN SYSTEM OPTION');
1687 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
1688 END;
1689
1690
1691 IF PG_DEBUG in ('Y', 'C') THEN
1692 fnd_file.put_line(FND_FILE.LOG,'value of site_req_flag ' || psite_required);
1693 fnd_file.put_line(FND_FILE.LOG,'value of the invoices per commit' || pinvoices_per_commit);
1694 fnd_file.put_line(FND_FILE.LOG,'value of receipts per_commit ' || preceipts_per_commit);
1695 fnd_file.put_line(FND_FILE.LOG,'value of currency code' || pfunctional_currency);
1696 fnd_file.put_line(FND_FILE.LOG,'value of acc_method ' || pacc_method );
1697 END IF;
1698
1699 /* END FROM SYSTEM PARAMETERS*/
1700
1701 IF PG_DEBUG in ('Y', 'C') THEN
1702 fnd_file.put_line(FND_FILE.LOG,'insert_batch ()-');
1703 END IF;
1704
1705
1706 EXCEPTION
1707 WHEN others THEN
1708 IF PG_DEBUG in ('Y', 'C') THEN
1709 fnd_file.put_line(FND_FILE.LOG,'Exception : insert_batch() ');
1710 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
1711 END IF;
1712 raise;
1713
1714
1715 END insert_batch;
1716
1717 /*========================================================================+
1718 | PROCEDURE create_and_update_remit_rec |
1719 | |
1720 | DESCRIPTION |
1721 | |
1722 | This procedure is used to select receipts to be remitted |
1723 | update and insert records into the necessary tables. |
1724 | PSEUDO CODE/LOGIC |
1725 | |
1726 | PARAMETERS |
1727 | |
1728 | |
1729 | KNOWN ISSUES |
1730 | |
1731 | NOTES |
1732 | |
1733 | |
1734 | MODIFICATION HISTORY |
1735 | Date Author Description of Changes |
1736 | 16-JUL-2005 bichatte Created |
1737 *=========================================================================*/
1738 PROCEDURE create_and_update_remit_rec(
1739 p_batch_id IN NUMBER,
1740 p_return_status OUT NOCOPY VARCHAR2
1741 ) IS
1742
1743 l_rows_processed INTEGER;
1744 l_rows_fetched INTEGER;
1745 l_sel_stmt long;
1746 sel_receipts INTEGER;
1747 /* declare the cursor variables */
1748
1749 p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
1750 p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
1751 p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
1752 p_currency_code ar_cash_receipts.currency_code%TYPE;
1753 p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
1754 p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
1755 p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1756 p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
1757 p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
1758 p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
1759 p_cr_amount ar_cash_receipts.amount%TYPE;
1760 p_cr_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
1761 p_cr_factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE;
1762 p_remmitance_ccid ar_receipt_method_accounts.remittance_ccid%TYPE;
1763 p_bank_charges_ccid ar_receipt_method_accounts.bank_charges_ccid%TYPE;
1764 p_code_combination_id ar_distributions.code_combination_id%TYPE;
1765 p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
1766 p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
1767 p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
1768 p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
1769 p_approval_code ar_cash_receipts.approval_code%TYPE;
1770 p_receipt_number ar_cash_receipts.receipt_number%TYPE;
1771 p_unique_ref ar_cash_receipts.unique_reference%TYPE;
1772 p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
1773 p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
1774
1775 /* end declare */
1776
1777 /* declare the insert array elements */
1778 i NUMBER;
1779 dummy NUMBER;
1780 inst_stmt varchar2(2000);
1781 cash_receipt_id_array dbms_sql.Number_Table;
1782 payment_type_CODE_array dbms_sql.varchar2_Table;
1783 merchant_ref_array dbms_sql.varchar2_Table;
1784 currency_code_array dbms_sql.varchar2_Table;
1785 pay_from_customer_array dbms_sql.varchar2_Table;
1786 customer_site_use_id_array dbms_sql.Number_Table;
1787 prv_cash_receipt_hist_id_array dbms_sql.Number_Table;
1788 exchange_date_array dbms_sql.date_Table;
1789 exchange_rate_array dbms_sql.Number_Table;
1790 exchange_type_array dbms_sql.varchar2_Table;
1791 cr_amount_array dbms_sql.Number_Table;
1792 cr_acctd_amount_array dbms_sql.Number_Table;
1793 cr_factor_discount_amt_array dbms_sql.Number_Table;
1794 remmitance_ccid_array dbms_sql.Number_Table;
1795 bank_charges_ccid_array dbms_sql.Number_Table;
1796 code_combination_id_array dbms_sql.Number_Table;
1797 cash_receipt_history_id_array dbms_sql.Number_Table;
1798 crh_gl_date_array dbms_sql.date_Table;
1799 crh_trx_date_array dbms_sql.date_Table;
1800 payment_server_order_num_array dbms_sql.varchar2_Table;
1801 approval_code_array dbms_sql.varchar2_Table;
1802 receipt_number_array dbms_sql.varchar2_Table;
1803 unique_ref_array dbms_sql.varchar2_Table;
1804 customer_bank_account_id_array dbms_sql.Number_Table;
1805 payment_trxn_extn_id_array dbms_sql.Number_Table;
1806 rem_t NUMBER;
1807 /* end declare */
1808
1809
1810 -- update variables
1811 upd_stmt1 varchar2(1000);
1812 rem_t1 number;
1813 dum number;
1814
1815 -- insert CRH variables.
1816 ins_crh1 long;
1817 rem_t3 number;
1818 d1 number;
1819
1820 -- XLA uptake
1821 CURSOR c_rec is
1822 select hist.cash_receipt_id cr_id
1823 from ar_cash_receipt_history hist,
1824 AR_REMIT_GT rec
1825 where hist.STATUS = 'REMITTED'
1826 and hist.cash_receipt_id = rec.cash_receipt_id;
1827
1828 l_xla_ev_rec ARP_XLA_EVENTS.XLA_EVENTS_TYPE;
1829
1830 -- insert DIST variables.
1831 ins_dist1 long;
1832 rem_t4 number;
1833 d2 number;
1834
1835 l_factor_flag varchar2(1);
1836 l_request_id NUMBER;
1837 l_last_updated_by NUMBER;
1838 l_created_by NUMBER;
1839 l_last_update_login NUMBER;
1840 l_program_application_id NUMBER;
1841 l_program_id NUMBER;
1842 l_org_id NUMBER;
1843
1844
1845
1846
1847 BEGIN
1848
1849 IF PG_DEBUG in ('Y', 'C') THEN
1850 fnd_file.put_line(FND_FILE.LOG,'create and upd start ()+');
1851 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_id ' || p_batch_id);
1852 END IF;
1853
1854 select decode(remit_method_code, 'FACTORING', 'Y', 'N')
1855 into l_factor_flag
1856 from ar_batches
1857 where batch_id = p_batch_id;
1858
1859
1860 l_request_id := arp_standard.profile.request_id;
1861 l_last_updated_by := arp_standard.profile.last_update_login ;
1862 l_created_by := arp_standard.profile.user_id ;
1863 l_last_update_login := arp_standard.profile.last_update_login ;
1864 l_program_application_id := arp_standard.application_id ;
1865 l_program_id := arp_standard.profile.program_id;
1866 l_org_id := arp_standard.sysparm.org_id;
1867
1868
1869 l_sel_stmt := ' SELECT cr.cash_receipt_id,
1870 rm.payment_channel_code,
1871 rm.merchant_ref,
1872 cr.currency_code,
1873 cr.pay_from_customer,
1874 cr.customer_site_use_id,
1875 crh.cash_receipt_history_id,
1876 crh.exchange_date,
1877 crh.exchange_rate,
1878 crh.exchange_rate_type,
1879 crh.amount,
1880 crh.acctd_amount,
1881 nvl(cr.factor_discount_amount,0),
1882 decode( bat.remit_method_code,
1883 ''FACTORING'', rma.factor_ccid,
1884 rma.remittance_ccid),
1885 rma.bank_charges_ccid,
1886 d.code_combination_id,
1887 ar_cash_receipt_history_s.nextval,
1888 greatest((bat.gl_date), (crh.trx_date)),
1889 greatest((bat.batch_date), (crh.trx_date)),
1890 cr.payment_server_order_num,
1891 cr.approval_code,
1892 cr.receipt_number,
1893 DECODE(cr.unique_reference,
1894 NULL,SYS_GUID(),
1895 cr.unique_reference ),
1896 cr.customer_bank_account_id,
1897 cr.payment_trxn_extension_id
1898 FROM ar_cash_receipts cr, ar_cash_receipt_history crh,
1899 ar_receipt_methods rm,
1900 ar_batches bat,
1901 ar_receipt_method_accounts rma,
1902 ar_payment_schedules ps,
1903 ar_distributions d
1904 WHERE cr.selected_remittance_batch_id = :ab_batch_id
1905 AND cr.REMIT_BANK_ACCT_USE_ID = rma.REMIT_BANK_ACCT_USE_ID
1906 AND bat.batch_id = cr.selected_remittance_batch_id
1907 AND rm.receipt_method_id = cr.receipt_method_id
1908 AND rma.receipt_method_id = cr.receipt_method_id
1909 AND cr.cash_receipt_id = crh.cash_receipt_id
1910 AND crh.current_record_flag = ''Y''
1911 AND ps.cash_receipt_id(+) = cr.cash_receipt_id
1912 AND crh.status = ''CONFIRMED''
1913 AND crh.cash_receipt_history_id = d.source_id
1914 AND d.source_type = ''CONFIRMATION''
1915 AND d.source_table = ''CRH''
1916 ';
1917
1918 sel_receipts := dbms_sql.open_cursor;
1919
1920 dbms_sql.parse (sel_receipts,l_sel_stmt,dbms_sql.v7);
1921
1922 dbms_sql.bind_variable (sel_receipts,':ab_batch_id',p_batch_id);
1923
1924 dbms_sql.define_column (sel_receipts, 1, p_cash_receipt_id);
1925 dbms_sql.define_column (sel_receipts, 2, p_payment_type_code,30);
1926 dbms_sql.define_column (sel_receipts, 3, p_merchant_ref,80);
1927 dbms_sql.define_column (sel_receipts, 4, p_currency_code,15);
1928 dbms_sql.define_column (sel_receipts, 5, p_pay_from_customer);
1929 dbms_sql.define_column (sel_receipts, 6, p_customer_site_use_id);
1930 dbms_sql.define_column (sel_receipts, 7, p_prv_cash_receipt_hist_id);
1931 dbms_sql.define_column (sel_receipts, 8, p_exchange_date);
1932 dbms_sql.define_column (sel_receipts, 9, p_exchange_rate);
1933 dbms_sql.define_column (sel_receipts, 10, p_exchange_type,30);
1934 dbms_sql.define_column (sel_receipts, 11, p_cr_amount);
1935 dbms_sql.define_column (sel_receipts, 12, p_cr_acctd_amount);
1936 dbms_sql.define_column (sel_receipts, 13, p_cr_factor_discount_amount);
1937 dbms_sql.define_column (sel_receipts, 14, p_remmitance_ccid );
1938 dbms_sql.define_column (sel_receipts, 15, p_bank_charges_ccid);
1939 dbms_sql.define_column (sel_receipts, 16, p_code_combination_id);
1940 dbms_sql.define_column (sel_receipts, 17, p_cash_receipt_history_id);
1941 dbms_sql.define_column (sel_receipts, 18, p_crh_gl_date);
1942 dbms_sql.define_column (sel_receipts, 19, p_crh_trx_date);
1943 dbms_sql.define_column (sel_receipts, 20, p_payment_server_order_num,80);
1944 dbms_sql.define_column (sel_receipts, 21, p_approval_code,80);
1945 dbms_sql.define_column (sel_receipts, 22, p_receipt_number,30);
1946 dbms_sql.define_column (sel_receipts, 23, p_unique_ref,32);
1947 dbms_sql.define_column (sel_receipts, 24, p_customer_bank_account_id);
1948 dbms_sql.define_column (sel_receipts, 25, p_payment_trxn_extension_id);
1949
1950 IF PG_DEBUG in ('Y', 'C') THEN
1951 fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
1952 END IF;
1953
1954 l_rows_processed := dbms_sql.execute( sel_receipts);
1955
1956 i:= 0;
1957
1958 WHILE dbms_sql.fetch_rows( sel_receipts) > 0 LOOP
1959 fnd_file.put_line(FND_FILE.LOG,'the value of i- ent ' || to_char(i));
1960
1961 dbms_sql.column_value (sel_receipts, 1, p_cash_receipt_id);
1962 dbms_sql.column_value (sel_receipts, 2, p_payment_type_code);
1963 dbms_sql.column_value (sel_receipts, 3, p_merchant_ref);
1964 dbms_sql.column_value (sel_receipts, 4, p_currency_code);
1965 dbms_sql.column_value (sel_receipts, 5, p_pay_from_customer);
1966 dbms_sql.column_value (sel_receipts, 6, p_customer_site_use_id);
1967 dbms_sql.column_value (sel_receipts, 7, p_prv_cash_receipt_hist_id);
1968 dbms_sql.column_value (sel_receipts, 8, p_exchange_date);
1969 dbms_sql.column_value (sel_receipts, 9, p_exchange_rate);
1970 dbms_sql.column_value (sel_receipts, 10, p_exchange_type);
1971 dbms_sql.column_value (sel_receipts, 11, p_cr_amount);
1972 dbms_sql.column_value (sel_receipts, 12, p_cr_acctd_amount);
1973 dbms_sql.column_value (sel_receipts, 13, p_cr_factor_discount_amount);
1974 dbms_sql.column_value (sel_receipts, 14, p_remmitance_ccid );
1975 dbms_sql.column_value (sel_receipts, 15, p_bank_charges_ccid);
1976 dbms_sql.column_value (sel_receipts, 16, p_code_combination_id);
1977 dbms_sql.column_value (sel_receipts, 17, p_cash_receipt_history_id);
1978 dbms_sql.column_value (sel_receipts, 18, p_crh_gl_date);
1979 dbms_sql.column_value (sel_receipts, 19, p_crh_trx_date);
1980 dbms_sql.column_value (sel_receipts, 20, p_payment_server_order_num);
1981 dbms_sql.column_value (sel_receipts, 21, p_approval_code);
1982 dbms_sql.column_value (sel_receipts, 22, p_receipt_number);
1983 dbms_sql.column_value (sel_receipts, 23, p_unique_ref);
1984 dbms_sql.column_value (sel_receipts, 24, p_customer_bank_account_id);
1985 dbms_sql.column_value (sel_receipts, 25, p_payment_trxn_extension_id);
1986
1987
1988
1989 IF PG_DEBUG in ( 'Y','C') THEN
1990 fnd_file.put_line(FND_FILE.LOG,'the value of ps_id ' || p_cash_receipt_id);
1991 fnd_file.put_line(FND_FILE.LOG,'the value of i ' || to_char(i));
1992 END IF;
1993
1994
1995 cash_receipt_id_array(i) := p_cash_receipt_id ;
1996 payment_type_code_array(i) := p_payment_type_code;
1997 merchant_ref_array(i) := p_merchant_ref;
1998 currency_code_array(i) := p_currency_code;
1999 pay_from_customer_array(i) := p_pay_from_customer;
2000 customer_site_use_id_array(i) := p_customer_site_use_id;
2001 prv_cash_receipt_hist_id_array(i) := p_prv_cash_receipt_hist_id;
2002 exchange_date_array(i) := p_exchange_date ;
2003 exchange_rate_array(i) := p_exchange_rate;
2004 exchange_type_array(i) :=p_exchange_type;
2005 cr_amount_array(i) :=p_cr_amount;
2006 cr_acctd_amount_array(i) :=p_cr_acctd_amount;
2007 cr_factor_discount_amt_array(i) :=p_cr_factor_discount_amount;
2008 remmitance_ccid_array(i) :=p_remmitance_ccid;
2009 bank_charges_ccid_array(i) :=p_bank_charges_ccid;
2010 code_combination_id_array(i) :=p_code_combination_id;
2011 cash_receipt_history_id_array(i) :=p_cash_receipt_history_id;
2012 crh_gl_date_array(i) :=p_crh_gl_date;
2013 crh_trx_date_array(i) :=p_crh_trx_date;
2014 payment_server_order_num_array(i) :=p_payment_server_order_num;
2015 approval_code_array(i) :=p_approval_code;
2016 receipt_number_array(i) :=p_receipt_number;
2017 unique_ref_array(i) :=p_unique_ref;
2018 customer_bank_account_id_array(i) :=p_customer_bank_account_id;
2019 payment_trxn_extn_id_array(i) :=p_payment_trxn_extension_id;
2020
2021 i := i + 1;
2022
2023 IF PG_DEBUG in ('Y', 'C') THEN
2024 fnd_file.put_line(FND_FILE.LOG,'the value of i- lea ' || to_char(i));
2025 END IF;
2026
2027 END LOOP;
2028
2029 l_rows_fetched := dbms_sql.last_row_count ;
2030
2031 IF PG_DEBUG in ('Y', 'C') THEN
2032 fnd_file.put_line(FND_FILE.LOG,'the no of rows fetched ' || l_rows_fetched);
2033 END IF;
2034
2035 dbms_sql.close_cursor( sel_receipts);
2036
2037 IF PG_DEBUG in ('Y', 'C') THEN
2038 fnd_file.put_line(FND_FILE.LOG,'selrem and create and upd 1()-');
2039 END IF;
2040
2041 -- Bulk Insert into AR_REMIT_GT
2042
2043 /* CHECK AND CORRECT FROM HERE DONE UPTILL HERE */
2044
2045 BEGIN
2046
2047 inst_stmt := 'insert into AR_REMIT_GT values ( :c1_array,:c2_array,:c3_array,:c4_array,:c5_array,
2048 :c6_array,:c7_array,:c8_array,:c9_array,:c10_array,:c11_array,:c12_array,
2049 :c13_array,:c14_array,:c15_array,:c16_array,:c17_array,:c18_array,
2050 :c19_array,:c20_array,:c21_array,:c22_array,:c23_array,:c24_array,:c25_array)';
2051
2052 rem_t := dbms_sql.open_cursor;
2053
2054 dbms_sql.parse (rem_t,inst_stmt,dbms_sql.v7);
2055
2056 dbms_sql.bind_array (rem_t,':c1_array',cash_receipt_id_array);
2057 dbms_sql.bind_array (rem_t,':c2_array',payment_type_CODE_array);
2058 dbms_sql.bind_array (rem_t,':c3_array',merchant_ref_array);
2059 dbms_sql.bind_array (rem_t,':c4_array',currency_code_array);
2060 dbms_sql.bind_array (rem_t,':c5_array',pay_from_customer_array);
2061 dbms_sql.bind_array (rem_t,':c6_array',customer_site_use_id_array);
2062 dbms_sql.bind_array (rem_t,':c7_array',prv_cash_receipt_hist_id_array);
2063 dbms_sql.bind_array (rem_t,':c8_array',exchange_date_array);
2064 dbms_sql.bind_array (rem_t,':c9_array',exchange_rate_array);
2065 dbms_sql.bind_array (rem_t,':c10_array',exchange_type_array);
2066 dbms_sql.bind_array (rem_t,':c11_array',cr_amount_array);
2067 dbms_sql.bind_array (rem_t,':c12_array',cr_acctd_amount_array);
2068 dbms_sql.bind_array (rem_t,':c13_array',cr_factor_discount_amt_array);
2069 dbms_sql.bind_array (rem_t,':c14_array',remmitance_ccid_array);
2070 dbms_sql.bind_array (rem_t,':c15_array',bank_charges_ccid_array);
2071 dbms_sql.bind_array (rem_t,':c16_array',code_combination_id_array);
2072 dbms_sql.bind_array (rem_t,':c17_array',cash_receipt_history_id_array);
2073 dbms_sql.bind_array (rem_t,':c18_array',crh_gl_date_array);
2074 dbms_sql.bind_array (rem_t,':c19_array',crh_trx_date_array);
2075 dbms_sql.bind_array (rem_t,':c20_array',payment_server_order_num_array);
2076 dbms_sql.bind_array (rem_t,':c21_array',approval_code_array);
2077 dbms_sql.bind_array (rem_t,':c22_array',receipt_number_array);
2078 dbms_sql.bind_array (rem_t,':c23_array',unique_ref_array);
2079 dbms_sql.bind_array (rem_t,':c24_array',customer_bank_account_id_array);
2080 dbms_sql.bind_array (rem_t,':c25_array',payment_trxn_extn_id_array);
2081
2082
2083 dummy := dbms_sql.execute(rem_t);
2084
2085 dbms_sql.close_cursor(rem_t);
2086
2087 EXCEPTION WHEN OTHERS THEN
2088 if dbms_sql.is_open(rem_t) then
2089 dbms_sql.close_cursor(rem_t);
2090 end if;
2091 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
2092 raise;
2093
2094 END;
2095
2096 /* the update and inserts */
2097
2098 BEGIN
2099
2100 IF PG_DEBUG in ('Y', 'C') THEN
2101 fnd_file.put_line(FND_FILE.LOG,'upd-crh1 ()+');
2102
2103 END IF;
2104
2105
2106 /* the first update into ar_cash_receipt_history */
2107
2108 upd_stmt1 := ' UPDATE ar_cash_receipt_history
2109 SET reversal_cash_receipt_hist_id = :ucrh_id_array,
2110 reversal_gl_date = :ucrh_gl_date_array,
2111 reversal_created_from = ''ARZARM'',
2112 current_record_flag = NULL,
2113 last_update_date = sysdate,
2114 last_updated_by = :i_last_updated_by,
2115 last_update_login = :i_last_update_login,
2116 request_id = :i_request_id,
2117 program_application_id = :i_program_application_id,
2118 program_id = :i_program_id,
2119 program_update_date = sysdate
2120 WHERE cash_receipt_id = :cr_id_array
2121 AND current_record_flag = ''Y''
2122 AND status = ''CONFIRMED''
2123 ';
2124
2125
2126 rem_t1 := dbms_sql.open_cursor;
2127
2128 dbms_sql.parse (rem_t1,upd_stmt1,dbms_sql.v7);
2129
2130 dbms_sql.bind_array (rem_t1,':ucrh_id_array', cash_receipt_history_id_array);
2131 dbms_sql.bind_array (rem_t1,':ucrh_gl_date_array',crh_gl_date_array );
2132 dbms_sql.bind_array (rem_t1,':cr_id_array', cash_receipt_id_array);
2133 /* who cols*/
2134
2135 dbms_sql.bind_variable (rem_t1,':i_last_updated_by',l_last_updated_by);
2136 dbms_sql.bind_variable (rem_t1,':i_last_update_login',l_last_update_login);
2137 dbms_sql.bind_variable (rem_t1,':i_request_id',l_request_id);
2138 dbms_sql.bind_variable (rem_t1,':i_program_application_id',l_program_application_id);
2139 dbms_sql.bind_variable (rem_t1,':i_program_id',l_program_id);
2140
2141 dummy := dbms_sql.execute(rem_t1);
2142
2143 dbms_sql.close_cursor(rem_t1);
2144
2145 IF PG_DEBUG in ('Y', 'C') THEN
2146 arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
2147 fnd_file.put_line(FND_FILE.LOG,'upd-crh1 ()-');
2148 END IF;
2149
2150 EXCEPTION WHEN OTHERS THEN
2151 if dbms_sql.is_open(rem_t1) then
2152 dbms_sql.close_cursor(rem_t1);
2153 end if;
2154 IF PG_DEBUG in ('Y', 'C') THEN
2155 fnd_file.put_line(FND_FILE.LOG,'failed to update() '|| to_char(SQLCODE));
2156 END IF;
2157 raise;
2158
2159
2160 END;
2161
2162 /* insert into crh */
2163
2164 BEGIN
2165
2166
2167 IF PG_DEBUG in ('Y', 'C') THEN
2168 fnd_file.put_line(FND_FILE.LOG,'insert-crh1 ()+');
2169 fnd_file.put_line(FND_FILE.LOG, 'the batch_id ' || p_batch_id);
2170
2171 END IF;
2172
2173
2174 /* the first update into ar_cash_receipt_history */
2175
2176 ins_crh1 := 'INSERT into ar_cash_receipt_history
2177 (cash_receipt_history_id,
2178 cash_receipt_id,
2179 status,
2180 trx_date,
2181 amount,
2182 acctd_amount,
2183 first_posted_record_flag,
2184 postable_flag,
2185 factor_flag,
2186 gl_date,
2187 current_record_flag,
2188 batch_id,
2189 exchange_date,
2190 exchange_rate,
2191 exchange_rate_type,
2192 account_code_combination_id,
2193 reversal_gl_date,
2194 reversal_cash_receipt_hist_id,
2195 prv_stat_cash_receipt_hist_id,
2196 factor_discount_amount,
2197 acctd_factor_discount_amount,
2198 bank_charge_account_ccid,
2199 posting_control_id,
2200 created_from,
2201 reversal_posting_control_id,
2202 gl_posted_date,
2203 reversal_gl_posted_date,
2204 created_by,
2205 creation_date,
2206 last_updated_by,
2207 last_update_date,
2208 last_update_login,
2209 request_id,
2210 org_id,
2211 program_application_id,
2212 program_id,
2213 program_update_date
2214 )
2215 select
2216 r.CASH_RECEIPT_HISTORY_ID,
2217 r.CASH_RECEIPT_ID,
2218 ''REMITTED'',
2219 r.CRH_TRX_DATE,
2220 r.cr_amount - r.cr_factor_discount_amount,
2221 r.cr_acctd_amount - DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
2222 ''User'', arp_util.functional_amount(
2223 r.cr_factor_discount_amount,
2224 '''||ARP_GLOBAL.functional_currency||''',
2225 nvl(r.exchange_rate,1),
2226 NULL, NULL),
2227 gl_currency_api.convert_amount(
2228 r.currency_code,
2229 '''||ARP_GLOBAL.functional_currency||''',
2230 r.exchange_date,
2231 r.exchange_type,
2232 r.cr_factor_discount_amount)),
2233 ''N'',
2234 ''Y'',
2235 :factor_flag,
2236 r.crh_gl_date,
2237 ''Y'',
2238 :i_batch_id,
2239 r.exchange_date,
2240 r.exchange_rate,
2241 r.exchange_type,
2242 r.remmitance_ccid,
2243 NULL,
2244 NULL,
2245 r.prv_cash_receipt_hist_id,
2246 r.cr_factor_discount_amount,
2247 DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
2248 ''User'', arp_util.functional_amount(
2249 r.cr_factor_discount_amount,
2250 '''||ARP_GLOBAL.functional_currency||''',
2251 nvl(r.exchange_rate,1),
2252 NULL, NULL),
2253 gl_currency_api.convert_amount(
2254 r.currency_code,
2255 '''||ARP_GLOBAL.functional_currency||''',
2256 r.exchange_date,
2257 r.exchange_type,
2258 r.cr_factor_discount_amount)),
2259 r.bank_charges_ccid,
2260 ''-3'',
2261 ''ARZARM'',
2262 NULL,
2263 NULL,
2264 NULL,
2265 :i_created_by,
2266 sysdate,
2267 :i_last_updated_by,
2268 sysdate,
2269 :i_last_update_login,
2270 :i_request_id,
2271 :i_org_id,
2272 :i_program_application_id,
2273 :i_program_id,
2274 sysdate
2275 FROM AR_REMIT_GT r
2276 WHERE r.cash_receipt_history_id is not null';
2277
2278 rem_t3 := dbms_sql.open_cursor;
2279
2280 dbms_sql.parse (rem_t3,ins_crh1,dbms_sql.v7);
2281
2282 IF PG_DEBUG in ('Y', 'C') THEN
2283 fnd_file.put_line(FND_FILE.LOG, 'the batch_id ' || p_batch_id);
2284 END IF;
2285
2286
2287 dbms_sql.bind_variable (rem_t3,':i_batch_id',p_batch_id); -- NOTE THIS ONE
2288 dbms_sql.bind_variable (rem_t3,':factor_flag',l_factor_flag); -- NOTE THIS ONE
2289 /* who cols */
2290 dbms_sql.bind_variable (rem_t3,':i_created_by',l_created_by);
2291 dbms_sql.bind_variable (rem_t3,':i_last_updated_by',l_last_updated_by);
2292 dbms_sql.bind_variable (rem_t3,':i_last_update_login',l_last_update_login);
2293 dbms_sql.bind_variable (rem_t3,':i_request_id',l_request_id);
2294 dbms_sql.bind_variable (rem_t3,':i_org_id',l_org_id);
2295 dbms_sql.bind_variable (rem_t3,':i_program_application_id',l_program_application_id);
2296 dbms_sql.bind_variable (rem_t3,':i_program_id',l_program_id);
2297
2298
2299 IF PG_DEBUG in ('Y', 'C') THEN
2300 fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
2301 END IF;
2302
2303 dummy := dbms_sql.execute(rem_t3);
2304
2305 dbms_sql.close_cursor(rem_t3);
2306
2307
2308 /* BICHATTE after the insert into crh we have to fire the XLA event */
2309
2310 FOR rec in c_rec LOOP
2311
2312 l_xla_ev_rec.xla_from_doc_id := rec.cr_id;
2313 l_xla_ev_rec.xla_to_doc_id := rec.cr_id;
2314 l_xla_ev_rec.xla_doc_table := 'CRH';
2315 l_xla_ev_rec.xla_mode := 'O';
2316 l_xla_ev_rec.xla_call := 'B';
2317
2318 IF PG_DEBUG in ('Y', 'C') THEN
2319 fnd_file.put_line(FND_FILE.LOG,'xla_from_doc_id= '|| to_char(rec.cr_id));
2320 fnd_file.put_line(FND_FILE.LOG,'xla_to_doc_id= '|| to_char(rec.cr_id));
2321 fnd_file.put_line(FND_FILE.LOG,'xla_doc_table= '|| 'CRH');
2322 fnd_file.put_line(FND_FILE.LOG,'xla_from_doc_id= '|| 'O');
2323 fnd_file.put_line(FND_FILE.LOG,'xla_from_doc_id= '|| 'B');
2324 END IF;
2325
2326 arp_xla_events.create_events(l_xla_ev_rec);
2327
2328
2329 IF PG_DEBUG in ('Y', 'C') THEN
2330 fnd_file.put_line(FND_FILE.LOG,'RETURN STATUS FROM XLA () '|| to_char(SQLCODE));
2331 END IF;
2332
2333
2334 END LOOP;
2335
2336
2337
2338
2339 EXCEPTION WHEN OTHERS THEN
2340 fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
2341 if dbms_sql.is_open(rem_t3) then
2342 dbms_sql.close_cursor(rem_t3);
2343 end if;
2344 IF PG_DEBUG in ('Y', 'C') THEN
2345 fnd_file.put_line(FND_FILE.LOG,'failed to insert() '|| to_char(SQLCODE));
2346 fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
2347 END IF;
2348 raise;
2349
2350 END;
2351
2352 /* end insert into crh */
2353
2354 /* insert into dist */
2355 BEGIN
2356
2357
2358 IF PG_DEBUG in ('Y', 'C') THEN
2359 fnd_file.put_line(FND_FILE.LOG,'insert-dist1 ()+');
2360
2361 END IF;
2362
2363
2364 /* the first update into ar_cash_receipt_history */
2365
2366 ins_dist1 := 'INSERT into ar_distributions
2367 (line_id,
2368 source_id,
2369 source_table,
2370 source_type,
2371 code_combination_id,
2372 currency_code,
2373 third_party_id,
2374 third_party_sub_id,
2375 currency_conversion_date,
2376 currency_conversion_rate,
2377 currency_conversion_type,
2378 amount_dr,
2379 amount_cr,
2380 acctd_amount_dr,
2381 acctd_amount_cr,
2382 creation_date,
2383 created_by,
2384 last_update_date,
2385 last_updated_by,
2386 org_id,
2387 last_update_login)
2388 select
2389 ar_distributions_s.nextval,
2390 r.cash_receipt_history_id,
2391 ''CRH'',
2392 decode(:factor_flag,
2393 ''N'',decode(l.lookup_code,
2394 ''1'',''REMITTANCE'',
2395 ''2'',''CONFIRMATION''),
2396 ''Y'',decode(l.lookup_code,
2397 ''1'',''FACTOR'',
2398 ''2'',''CONFIRMATION'')),
2399 decode(l.lookup_code,
2400 ''1'',r.REMMITANCE_CCID,
2401 ''2'',r.code_combination_id), /* its confirmation_ccid */
2402 r.currency_code,
2403 r.pay_from_customer,
2404 r.customer_site_use_id,
2405 r.exchange_date,
2406 r.exchange_rate,
2407 r.exchange_type,
2408 decode(l.lookup_code,
2409 ''1'',decode(sign(to_number(r.cr_amount)),
2410 ''-1'',null,
2411 to_number(r.cr_amount)),
2412 ''2'',decode(sign(to_number(r.cr_amount)),
2413 ''1'',null,
2414 -(to_number(r.cr_amount)))),
2415 decode(l.lookup_code,
2416 ''1'',decode(sign(to_number(r.cr_amount)),
2417 ''1'',null,
2418 -(to_number(r.cr_amount))),
2419 ''2'',decode(sign(to_number(r.cr_amount)),
2420 ''-1'',null,
2421 to_number(r.cr_amount))),
2422 decode(l.lookup_code,
2423 ''1'',decode(sign(to_number(r.cr_acctd_amount)),
2424 ''-1'',null,
2425 to_number(r.cr_acctd_amount)),
2426 ''2'',decode(sign(to_number(r.cr_acctd_amount)),
2427 ''1'',null,
2428 -(to_number(r.cr_acctd_amount)))),
2429 decode(l.lookup_code,
2430 ''1'',decode(sign(to_number(r.cr_acctd_amount)),
2431 ''1'',null,
2432 -(to_number(r.cr_acctd_amount))),
2433 ''2'',decode(sign(to_number(r.cr_acctd_amount)),
2434 ''-1'',null,
2435 to_number(r.cr_acctd_amount))),
2436 sysdate,
2437 :i_created_by,
2438 sysdate,
2439 :i_last_updated_by,
2440 :i_org_id,
2441 :i_last_update_login
2442 FROM ar_cash_receipt_history crh,AR_REMIT_GT r,
2443 ar_lookups l
2444 WHERE crh.cash_receipt_history_id = r.cash_receipt_history_id
2445 AND l.lookup_type = ''AR_CARTESIAN_JOIN''
2446 AND l.lookup_code IN (''1'',''2'')';
2447
2448 rem_t4 := dbms_sql.open_cursor;
2449
2450 dbms_sql.parse (rem_t4,ins_dist1,dbms_sql.v7);
2451
2452 IF PG_DEBUG in ('Y', 'C') THEN
2453 fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
2454 END IF;
2455
2456 dbms_sql.bind_variable (rem_t4,':factor_flag',l_factor_flag); -- NOTE THIS ONE
2457 /* who cols */
2458 dbms_sql.bind_variable (rem_t4,':i_created_by',l_created_by);
2459 dbms_sql.bind_variable (rem_t4,':i_last_updated_by',l_last_updated_by);
2460 dbms_sql.bind_variable (rem_t4,':i_org_id',l_org_id);
2461 dbms_sql.bind_variable (rem_t4,':i_last_update_login',l_last_update_login);
2462
2463
2464 d2 := dbms_sql.execute(rem_t4);
2465
2466 dbms_sql.close_cursor(rem_t4);
2467
2468 IF PG_DEBUG in ('Y', 'C') THEN
2469 fnd_file.put_line(FND_FILE.LOG,'ins_dist1 ()-');
2470 END IF;
2471
2472 EXCEPTION WHEN OTHERS THEN
2473 if dbms_sql.is_open(rem_t4) then
2474 dbms_sql.close_cursor(rem_t4);
2475 end if;
2476 IF PG_DEBUG in ('Y', 'C') THEN
2477 fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
2478 fnd_file.put_line(FND_FILE.LOG,'DIST failed to insert() '|| to_char(SQLCODE));
2479 END IF;
2480 raise;
2481
2482 END;
2483
2484 /* end insert into dist */
2485
2486
2487 EXCEPTION
2488 WHEN others THEN
2489 IF PG_DEBUG in ('Y', 'C') THEN
2490 fnd_file.put_line(FND_FILE.LOG,'Exception : instrem() ');
2491 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
2492 END IF;
2493
2494
2495 END create_and_update_remit_rec ;
2496
2497
2498 /*========================================================================+
2499 | PROCEDURE create_and_update_remit_rec_pa |
2500 | |
2501 | DESCRIPTION |
2502 | |
2503 | This procedure is used to select receipts to be remitted |
2504 | update and insert records into the necessary tables. |
2505 | PSEUDO CODE/LOGIC |
2506 | |
2507 | PARAMETERS |
2508 | |
2509 | |
2510 | KNOWN ISSUES |
2511 | |
2512 | NOTES |
2513 | |
2514 | |
2515 | MODIFICATION HISTORY |
2516 | Date Author Description of Changes |
2517 | 10-JUN-2008 AGHORAKA Created for Parallelization |
2518 *=========================================================================*/
2519 PROCEDURE create_and_update_remit_rec_pa(
2520 p_batch_id IN NUMBER,
2521 p_return_status OUT NOCOPY VARCHAR2
2522 ) IS
2523
2524 l_rows_processed INTEGER;
2525 l_rows_fetched INTEGER;
2526 l_sel_stmt long;
2527 sel_receipts INTEGER;
2528 /* declare the cursor variables */
2529
2530 p_cash_receipt_id ar_cash_receipts.cash_receipt_id%TYPE;
2531 p_payment_type_code ar_receipt_methods.payment_type_code%TYPE;
2532 p_merchant_ref ar_receipt_methods.merchant_ref%TYPE;
2533 p_currency_code ar_cash_receipts.currency_code%TYPE;
2534 p_pay_from_customer ar_cash_receipts.pay_from_customer%TYPE;
2535 p_customer_site_use_id ar_cash_receipts.customer_site_use_id%TYPE;
2536 p_prv_cash_receipt_hist_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2537 p_exchange_date ar_cash_receipt_history.exchange_date%TYPE;
2538 p_exchange_rate ar_cash_receipt_history.exchange_rate%TYPE;
2539 p_exchange_type ar_cash_receipt_history.exchange_rate_type%TYPE;
2540 p_cr_amount ar_cash_receipts.amount%TYPE;
2541 p_cr_acctd_amount ar_cash_receipt_history.acctd_amount%TYPE;
2542 p_cr_factor_discount_amount ar_cash_receipts.factor_discount_amount%TYPE;
2543 p_remmitance_ccid ar_receipt_method_accounts.remittance_ccid%TYPE;
2544 p_bank_charges_ccid ar_receipt_method_accounts.bank_charges_ccid%TYPE;
2545 p_code_combination_id ar_distributions.code_combination_id%TYPE;
2546 p_cash_receipt_history_id ar_cash_receipt_history.cash_receipt_history_id%TYPE;
2547 p_crh_gl_date ar_cash_receipt_history.gl_date%TYPE;
2548 p_crh_trx_date ar_cash_receipt_history.trx_date%TYPE;
2549 p_payment_server_order_num ar_cash_receipts.payment_server_order_num%TYPE;
2550 p_approval_code ar_cash_receipts.approval_code%TYPE;
2551 p_receipt_number ar_cash_receipts.receipt_number%TYPE;
2552 p_unique_ref ar_cash_receipts.unique_reference%TYPE;
2553 p_customer_bank_account_id ar_cash_receipts.customer_bank_account_id%TYPE;
2554 p_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
2555
2556 /* end declare */
2557
2558 /* declare the insert array elements */
2559 i NUMBER;
2560 dummy NUMBER;
2561 inst_stmt varchar2(2000);
2562 cash_receipt_id_array dbms_sql.Number_Table;
2563 payment_type_CODE_array dbms_sql.varchar2_Table;
2564 merchant_ref_array dbms_sql.varchar2_Table;
2565 currency_code_array dbms_sql.varchar2_Table;
2566 pay_from_customer_array dbms_sql.varchar2_Table;
2567 customer_site_use_id_array dbms_sql.Number_Table;
2568 prv_cash_receipt_hist_id_array dbms_sql.Number_Table;
2569 exchange_date_array dbms_sql.date_Table;
2570 exchange_rate_array dbms_sql.Number_Table;
2571 exchange_type_array dbms_sql.varchar2_Table;
2572 cr_amount_array dbms_sql.Number_Table;
2573 cr_acctd_amount_array dbms_sql.Number_Table;
2574 cr_factor_discount_amt_array dbms_sql.Number_Table;
2575 remmitance_ccid_array dbms_sql.Number_Table;
2576 bank_charges_ccid_array dbms_sql.Number_Table;
2577 code_combination_id_array dbms_sql.Number_Table;
2578 cash_receipt_history_id_array dbms_sql.Number_Table;
2579 crh_gl_date_array dbms_sql.date_Table;
2580 crh_trx_date_array dbms_sql.date_Table;
2581 payment_server_order_num_array dbms_sql.varchar2_Table;
2582 approval_code_array dbms_sql.varchar2_Table;
2583 receipt_number_array dbms_sql.varchar2_Table;
2584 unique_ref_array dbms_sql.varchar2_Table;
2585 customer_bank_account_id_array dbms_sql.Number_Table;
2586 payment_trxn_extn_id_array dbms_sql.Number_Table;
2587 rem_t NUMBER;
2588 /* end declare */
2589
2590
2591 -- update variables
2592 upd_stmt1 varchar2(1000);
2593 rem_t1 number;
2594 dum number;
2595
2596 -- insert CRH variables.
2597 ins_crh1 long;
2598 rem_t3 number;
2599 d1 number;
2600
2601 -- XLA uptake
2602 CURSOR c_rec is
2603 select hist.cash_receipt_id cr_id
2604 from ar_cash_receipt_history hist,
2605 AR_REMIT_GT rec
2606 where hist.STATUS = 'REMITTED'
2607 and hist.cash_receipt_id = rec.cash_receipt_id;
2608
2609 l_xla_ev_rec ARP_XLA_EVENTS.XLA_EVENTS_TYPE;
2610
2611 -- insert DIST variables.
2612 ins_dist1 long;
2613 rem_t4 number;
2614 d2 number;
2615
2616 l_factor_flag varchar2(1);
2617 l_request_id NUMBER;
2618 l_last_updated_by NUMBER;
2619 l_created_by NUMBER;
2620 l_last_update_login NUMBER;
2621 l_program_application_id NUMBER;
2622 l_program_id NUMBER;
2623 l_org_id NUMBER;
2624
2625
2626
2627
2628 BEGIN
2629
2630 IF PG_DEBUG in ('Y', 'C') THEN
2631 fnd_file.put_line(FND_FILE.LOG,'create and upd start parallel()+');
2632 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_id ' || p_batch_id);
2633 END IF;
2634
2635 select decode(remit_method_code, 'FACTORING', 'Y', 'N')
2636 into l_factor_flag
2637 from ar_batches
2638 where batch_id = p_batch_id;
2639
2640 l_request_id := arp_standard.profile.request_id;
2641 l_last_updated_by := arp_standard.profile.last_update_login ;
2642 l_created_by := arp_standard.profile.user_id ;
2643 l_last_update_login := arp_standard.profile.last_update_login ;
2644 l_program_application_id := arp_standard.application_id ;
2645 l_program_id := arp_standard.profile.program_id;
2646 l_org_id := arp_standard.sysparm.org_id;
2647
2648
2649 l_sel_stmt := ' SELECT cash_receipt_id,
2650 payment_channel_code,
2651 merchant_ref,
2652 currency_code,
2653 pay_from_customer,
2654 customer_site_use_id,
2655 cash_receipt_history_id,
2656 exchange_date,
2657 exchange_rate,
2658 exchange_rate_type,
2659 amount1,
2660 acctd_amount,
2661 nvl(factor_discount_amount,0),
2662 remittance_ccid,
2663 bank_charges_ccid,
2664 code_combination_id,
2665 ar_cash_receipt_history_s.nextval,
2666 crh_gl_date,
2667 crh_trx_date,
2668 payment_server_order_num,
2669 approval_code,
2670 receipt_number,
2671 unique_ref,
2672 customer_bank_account_id,
2673 payment_trxn_extension_id
2674 FROM ar_autorem_interim
2675 WHERE batch_id = :ab_batch_id
2676 AND current_worker = :h_worker_number
2677 ';
2678
2679 sel_receipts := dbms_sql.open_cursor;
2680
2681 dbms_sql.parse (sel_receipts,l_sel_stmt,dbms_sql.v7);
2682
2683 dbms_sql.bind_variable (sel_receipts,':ab_batch_id',p_batch_id);
2684 dbms_sql.bind_variable (sel_receipts,':h_worker_number', WORKER_NUMBER);
2685
2686
2687 dbms_sql.define_column (sel_receipts, 1, p_cash_receipt_id);
2688 dbms_sql.define_column (sel_receipts, 2, p_payment_type_code,30);
2689 dbms_sql.define_column (sel_receipts, 3, p_merchant_ref,80);
2690 dbms_sql.define_column (sel_receipts, 4, p_currency_code,15);
2691 dbms_sql.define_column (sel_receipts, 5, p_pay_from_customer);
2692 dbms_sql.define_column (sel_receipts, 6, p_customer_site_use_id);
2693 dbms_sql.define_column (sel_receipts, 7, p_prv_cash_receipt_hist_id);
2694 dbms_sql.define_column (sel_receipts, 8, p_exchange_date);
2695 dbms_sql.define_column (sel_receipts, 9, p_exchange_rate);
2696 dbms_sql.define_column (sel_receipts, 10, p_exchange_type,30);
2697 dbms_sql.define_column (sel_receipts, 11, p_cr_amount);
2698 dbms_sql.define_column (sel_receipts, 12, p_cr_acctd_amount);
2699 dbms_sql.define_column (sel_receipts, 13, p_cr_factor_discount_amount);
2700 dbms_sql.define_column (sel_receipts, 14, p_remmitance_ccid );
2701 dbms_sql.define_column (sel_receipts, 15, p_bank_charges_ccid);
2702 dbms_sql.define_column (sel_receipts, 16, p_code_combination_id);
2703 dbms_sql.define_column (sel_receipts, 17, p_cash_receipt_history_id);
2704 dbms_sql.define_column (sel_receipts, 18, p_crh_gl_date);
2705 dbms_sql.define_column (sel_receipts, 19, p_crh_trx_date);
2706 dbms_sql.define_column (sel_receipts, 20, p_payment_server_order_num,80);
2707 dbms_sql.define_column (sel_receipts, 21, p_approval_code,80);
2708 dbms_sql.define_column (sel_receipts, 22, p_receipt_number,30);
2709 dbms_sql.define_column (sel_receipts, 23, p_unique_ref,32);
2710 dbms_sql.define_column (sel_receipts, 24, p_customer_bank_account_id);
2711 dbms_sql.define_column (sel_receipts, 25, p_payment_trxn_extension_id);
2712
2713 IF PG_DEBUG in ('Y', 'C') THEN
2714 fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
2715 END IF;
2716
2717 l_rows_processed := dbms_sql.execute( sel_receipts);
2718
2719 i:= 0;
2720
2721 WHILE dbms_sql.fetch_rows( sel_receipts) > 0 LOOP
2722 dbms_sql.column_value (sel_receipts, 1, p_cash_receipt_id);
2723 dbms_sql.column_value (sel_receipts, 2, p_payment_type_code);
2724 dbms_sql.column_value (sel_receipts, 3, p_merchant_ref);
2725 dbms_sql.column_value (sel_receipts, 4, p_currency_code);
2726 dbms_sql.column_value (sel_receipts, 5, p_pay_from_customer);
2727 dbms_sql.column_value (sel_receipts, 6, p_customer_site_use_id);
2728 dbms_sql.column_value (sel_receipts, 7, p_prv_cash_receipt_hist_id);
2729 dbms_sql.column_value (sel_receipts, 8, p_exchange_date);
2730 dbms_sql.column_value (sel_receipts, 9, p_exchange_rate);
2731 dbms_sql.column_value (sel_receipts, 10, p_exchange_type);
2732 dbms_sql.column_value (sel_receipts, 11, p_cr_amount);
2733 dbms_sql.column_value (sel_receipts, 12, p_cr_acctd_amount);
2734 dbms_sql.column_value (sel_receipts, 13, p_cr_factor_discount_amount);
2735 dbms_sql.column_value (sel_receipts, 14, p_remmitance_ccid );
2736 dbms_sql.column_value (sel_receipts, 15, p_bank_charges_ccid);
2737 dbms_sql.column_value (sel_receipts, 16, p_code_combination_id);
2738 dbms_sql.column_value (sel_receipts, 17, p_cash_receipt_history_id);
2739 dbms_sql.column_value (sel_receipts, 18, p_crh_gl_date);
2740 dbms_sql.column_value (sel_receipts, 19, p_crh_trx_date);
2741 dbms_sql.column_value (sel_receipts, 20, p_payment_server_order_num);
2742 dbms_sql.column_value (sel_receipts, 21, p_approval_code);
2743 dbms_sql.column_value (sel_receipts, 22, p_receipt_number);
2744 dbms_sql.column_value (sel_receipts, 23, p_unique_ref);
2745 dbms_sql.column_value (sel_receipts, 24, p_customer_bank_account_id);
2746 dbms_sql.column_value (sel_receipts, 25, p_payment_trxn_extension_id);
2747
2748 IF PG_DEBUG in ( 'Y','C') THEN
2749 fnd_file.put_line(FND_FILE.LOG,
2750 'the value of ps_id ' || p_cash_receipt_id);
2751 fnd_file.put_line(FND_FILE.LOG,'the value of i ' || to_char(i));
2752 END IF;
2753
2754
2755 cash_receipt_id_array(i) := p_cash_receipt_id ;
2756 payment_type_code_array(i) := p_payment_type_code;
2757 merchant_ref_array(i) := p_merchant_ref;
2758 currency_code_array(i) := p_currency_code;
2759 pay_from_customer_array(i) := p_pay_from_customer;
2760 customer_site_use_id_array(i) := p_customer_site_use_id;
2761 prv_cash_receipt_hist_id_array(i) := p_prv_cash_receipt_hist_id;
2762 exchange_date_array(i) := p_exchange_date ;
2763 exchange_rate_array(i) := p_exchange_rate;
2764 exchange_type_array(i) :=p_exchange_type;
2765 cr_amount_array(i) :=p_cr_amount;
2766 cr_acctd_amount_array(i) :=p_cr_acctd_amount;
2767 cr_factor_discount_amt_array(i) :=p_cr_factor_discount_amount;
2768 remmitance_ccid_array(i) :=p_remmitance_ccid;
2769 bank_charges_ccid_array(i) :=p_bank_charges_ccid;
2770 code_combination_id_array(i) :=p_code_combination_id;
2771 cash_receipt_history_id_array(i) :=p_cash_receipt_history_id;
2772 crh_gl_date_array(i) :=p_crh_gl_date;
2773 crh_trx_date_array(i) :=p_crh_trx_date;
2774 payment_server_order_num_array(i) :=p_payment_server_order_num;
2775 approval_code_array(i) :=p_approval_code;
2776 receipt_number_array(i) :=p_receipt_number;
2777 unique_ref_array(i) :=p_unique_ref;
2778 customer_bank_account_id_array(i) :=p_customer_bank_account_id;
2779 payment_trxn_extn_id_array(i) :=p_payment_trxn_extension_id;
2780
2781 i := i + 1;
2782
2783 IF PG_DEBUG in ('Y', 'C') THEN
2784 fnd_file.put_line(FND_FILE.LOG,'the value of i- lea ' || to_char(i));
2785 END IF;
2786
2787 END LOOP;
2788
2789 l_rows_fetched := dbms_sql.last_row_count ;
2790
2791 IF PG_DEBUG in ('Y', 'C') THEN
2792 fnd_file.put_line(FND_FILE.LOG,'the no of rows fetched ' || l_rows_fetched);
2793 END IF;
2794
2795 dbms_sql.close_cursor( sel_receipts);
2796
2797 IF PG_DEBUG in ('Y', 'C') THEN
2798 fnd_file.put_line(FND_FILE.LOG,'selrem and create and upd 1()-');
2799 END IF;
2800
2801 -- Bulk Insert into AR_REMIT_GT
2802
2803 BEGIN
2804
2805 inst_stmt := 'insert into AR_REMIT_GT values
2806 ( :c1_array,:c2_array,:c3_array,:c4_array,:c5_array,
2807 :c6_array,:c7_array,:c8_array,:c9_array,:c10_array,:c11_array,:c12_array,
2808 :c13_array,:c14_array,:c15_array,:c16_array,:c17_array,:c18_array,
2809 :c19_array,:c20_array,:c21_array,:c22_array,:c23_array,:c24_array,:c25_array)';
2810
2811 rem_t := dbms_sql.open_cursor;
2812
2813 dbms_sql.parse (rem_t,inst_stmt,dbms_sql.v7);
2814
2815 dbms_sql.bind_array (rem_t,':c1_array',cash_receipt_id_array);
2816 dbms_sql.bind_array (rem_t,':c2_array',payment_type_CODE_array);
2817 dbms_sql.bind_array (rem_t,':c3_array',merchant_ref_array);
2818 dbms_sql.bind_array (rem_t,':c4_array',currency_code_array);
2819 dbms_sql.bind_array (rem_t,':c5_array',pay_from_customer_array);
2820 dbms_sql.bind_array (rem_t,':c6_array',customer_site_use_id_array);
2821 dbms_sql.bind_array (rem_t,':c7_array',prv_cash_receipt_hist_id_array);
2822 dbms_sql.bind_array (rem_t,':c8_array',exchange_date_array);
2823 dbms_sql.bind_array (rem_t,':c9_array',exchange_rate_array);
2824 dbms_sql.bind_array (rem_t,':c10_array',exchange_type_array);
2825 dbms_sql.bind_array (rem_t,':c11_array',cr_amount_array);
2826 dbms_sql.bind_array (rem_t,':c12_array',cr_acctd_amount_array);
2827 dbms_sql.bind_array (rem_t,':c13_array',cr_factor_discount_amt_array);
2828 dbms_sql.bind_array (rem_t,':c14_array',remmitance_ccid_array);
2829 dbms_sql.bind_array (rem_t,':c15_array',bank_charges_ccid_array);
2830 dbms_sql.bind_array (rem_t,':c16_array',code_combination_id_array);
2831 dbms_sql.bind_array (rem_t,':c17_array',cash_receipt_history_id_array);
2832 dbms_sql.bind_array (rem_t,':c18_array',crh_gl_date_array);
2833 dbms_sql.bind_array (rem_t,':c19_array',crh_trx_date_array);
2834 dbms_sql.bind_array (rem_t,':c20_array',payment_server_order_num_array);
2835 dbms_sql.bind_array (rem_t,':c21_array',approval_code_array);
2836 dbms_sql.bind_array (rem_t,':c22_array',receipt_number_array);
2837 dbms_sql.bind_array (rem_t,':c23_array',unique_ref_array);
2838 dbms_sql.bind_array (rem_t,':c24_array',customer_bank_account_id_array);
2839 dbms_sql.bind_array (rem_t,':c25_array',payment_trxn_extn_id_array);
2840
2841
2842 dummy := dbms_sql.execute(rem_t);
2843
2844 dbms_sql.close_cursor(rem_t);
2845
2846 EXCEPTION WHEN OTHERS THEN
2847 if dbms_sql.is_open(rem_t) then
2848 dbms_sql.close_cursor(rem_t);
2849 end if;
2850 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
2851 raise;
2852
2853 END;
2854
2855 /* the update and inserts */
2856
2857 BEGIN
2858
2859 IF PG_DEBUG in ('Y', 'C') THEN
2860 fnd_file.put_line(FND_FILE.LOG,'upd-crh1 ()+');
2861
2862 END IF;
2863
2864
2865 /* the first update into ar_cash_receipt_history */
2866
2867 upd_stmt1 := ' UPDATE ar_cash_receipt_history
2868 SET reversal_cash_receipt_hist_id = :ucrh_id_array,
2869 reversal_gl_date = :ucrh_gl_date_array,
2870 reversal_created_from = ''ARZARM'',
2871 current_record_flag = NULL,
2872 last_update_date = sysdate,
2873 last_updated_by = :i_last_updated_by,
2874 last_update_login = :i_last_update_login,
2875 request_id = :i_request_id,
2876 program_application_id = :i_program_application_id,
2877 program_id = :i_program_id,
2878 program_update_date = sysdate
2879 WHERE cash_receipt_id = :cr_id_array
2880 AND current_record_flag = ''Y''
2881 AND status = ''CONFIRMED''
2882 ';
2883
2884
2885 rem_t1 := dbms_sql.open_cursor;
2886
2887 dbms_sql.parse (rem_t1,upd_stmt1,dbms_sql.v7);
2888
2889 dbms_sql.bind_array (rem_t1,':ucrh_id_array', cash_receipt_history_id_array);
2890 dbms_sql.bind_array (rem_t1,':ucrh_gl_date_array',crh_gl_date_array );
2891 dbms_sql.bind_array (rem_t1,':cr_id_array', cash_receipt_id_array);
2892 /* who cols*/
2893
2894 dbms_sql.bind_variable (rem_t1,':i_last_updated_by',l_last_updated_by);
2895 dbms_sql.bind_variable (rem_t1,':i_last_update_login',l_last_update_login);
2896 dbms_sql.bind_variable (rem_t1,':i_request_id',l_request_id);
2897 dbms_sql.bind_variable (rem_t1,':i_program_application_id',l_program_application_id);
2898 dbms_sql.bind_variable (rem_t1,':i_program_id',l_program_id);
2899
2900 dummy := dbms_sql.execute(rem_t1);
2901
2902 dbms_sql.close_cursor(rem_t1);
2903
2904 IF PG_DEBUG in ('Y', 'C') THEN
2905 arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');
2906 fnd_file.put_line(FND_FILE.LOG,'upd-crh1 ()-');
2907 END IF;
2908
2909 EXCEPTION WHEN OTHERS THEN
2910 if dbms_sql.is_open(rem_t1) then
2911 dbms_sql.close_cursor(rem_t1);
2912 end if;
2913 IF PG_DEBUG in ('Y', 'C') THEN
2914 fnd_file.put_line(FND_FILE.LOG,'failed to update() '|| to_char(SQLCODE));
2915 END IF;
2916 raise;
2917
2918
2919 END;
2920
2921 /* insert into crh */
2922
2923 BEGIN
2924
2925
2926 IF PG_DEBUG in ('Y', 'C') THEN
2927 fnd_file.put_line(FND_FILE.LOG,'insert-crh1 ()+');
2928 fnd_file.put_line(FND_FILE.LOG, 'the batch_id ' || p_batch_id);
2929
2930 END IF;
2931
2932
2933 /* the first update into ar_cash_receipt_history */
2934
2935 ins_crh1 := 'INSERT into ar_cash_receipt_history
2936 (cash_receipt_history_id,
2937 cash_receipt_id,
2938 status,
2939 trx_date,
2940 amount,
2941 acctd_amount,
2942 first_posted_record_flag,
2943 postable_flag,
2944 factor_flag,
2945 gl_date,
2946 current_record_flag,
2947 batch_id,
2948 exchange_date,
2949 exchange_rate,
2950 exchange_rate_type,
2951 account_code_combination_id,
2952 reversal_gl_date,
2953 reversal_cash_receipt_hist_id,
2954 prv_stat_cash_receipt_hist_id,
2955 factor_discount_amount,
2956 acctd_factor_discount_amount,
2957 bank_charge_account_ccid,
2958 posting_control_id,
2959 created_from,
2960 reversal_posting_control_id,
2961 gl_posted_date,
2962 reversal_gl_posted_date,
2963 created_by,
2964 creation_date,
2965 last_updated_by,
2966 last_update_date,
2967 last_update_login,
2968 request_id,
2969 org_id,
2970 program_application_id,
2971 program_id,
2972 program_update_date
2973 )
2974 select
2975 r.CASH_RECEIPT_HISTORY_ID,
2976 r.CASH_RECEIPT_ID,
2977 ''REMITTED'',
2978 r.CRH_TRX_DATE,
2979 r.cr_amount - r.cr_factor_discount_amount,
2980 r.cr_acctd_amount - DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
2981 ''User'', arp_util.functional_amount(
2982 r.cr_factor_discount_amount,
2983 '''||ARP_GLOBAL.functional_currency||''',
2984 nvl(r.exchange_rate,1),
2985 NULL, NULL),
2986 gl_currency_api.convert_amount(
2987 r.currency_code,
2988 '''||ARP_GLOBAL.functional_currency||''',
2989 r.exchange_date,
2990 r.exchange_type,
2991 r.cr_factor_discount_amount)),
2992 ''N'',
2993 ''Y'',
2994 :factor_flag,
2995 r.crh_gl_date,
2996 ''Y'',
2997 :i_batch_id,
2998 r.exchange_date,
2999 r.exchange_rate,
3000 r.exchange_type,
3001 r.remmitance_ccid,
3002 NULL,
3003 NULL,
3004 r.prv_cash_receipt_hist_id,
3005 r.cr_factor_discount_amount,
3006 DECODE(r.exchange_type, null, r.cr_factor_discount_amount,
3007 ''User'', arp_util.functional_amount(
3008 r.cr_factor_discount_amount,
3009 '''||ARP_GLOBAL.functional_currency||''',
3010 nvl(r.exchange_rate,1),
3011 NULL, NULL),
3012 gl_currency_api.convert_amount(
3013 r.currency_code,
3014 '''||ARP_GLOBAL.functional_currency||''',
3015 r.exchange_date,
3016 r.exchange_type,
3017 r.cr_factor_discount_amount)),
3018 r.bank_charges_ccid,
3019 ''-3'',
3020 ''ARZARM'',
3021 NULL,
3022 NULL,
3023 NULL,
3024 :i_created_by,
3025 sysdate,
3026 :i_last_updated_by,
3027 sysdate,
3028 :i_last_update_login,
3029 :i_request_id,
3030 :i_org_id,
3031 :i_program_application_id,
3032 :i_program_id,
3033 sysdate
3034 FROM AR_REMIT_GT r
3035 WHERE r.cash_receipt_history_id is not null';
3036
3037 rem_t3 := dbms_sql.open_cursor;
3038
3039 dbms_sql.parse (rem_t3,ins_crh1,dbms_sql.v7);
3040
3041 IF PG_DEBUG in ('Y', 'C') THEN
3042 fnd_file.put_line(FND_FILE.LOG, 'the batch_id ' || p_batch_id);
3043 END IF;
3044
3045
3046 dbms_sql.bind_variable (rem_t3,':i_batch_id',p_batch_id);
3047 dbms_sql.bind_variable (rem_t3,':factor_flag',l_factor_flag);
3048 /* who cols */
3049 dbms_sql.bind_variable (rem_t3,':i_created_by',l_created_by);
3050 dbms_sql.bind_variable (rem_t3,':i_last_updated_by',l_last_updated_by);
3051 dbms_sql.bind_variable (rem_t3,':i_last_update_login',l_last_update_login);
3052 dbms_sql.bind_variable (rem_t3,':i_request_id',l_request_id);
3053 dbms_sql.bind_variable (rem_t3,':i_org_id',l_org_id);
3054 dbms_sql.bind_variable (rem_t3,':i_program_application_id',l_program_application_id);
3055 dbms_sql.bind_variable (rem_t3,':i_program_id',l_program_id);
3056
3057
3058 IF PG_DEBUG in ('Y', 'C') THEN
3059 fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
3060 END IF;
3061
3062 dummy := dbms_sql.execute(rem_t3);
3063
3064 dbms_sql.close_cursor(rem_t3);
3065
3066
3067 /* BICHATTE after the insert into crh we have to fire the XLA event */
3068
3069 FOR rec in c_rec LOOP
3070
3071 l_xla_ev_rec.xla_from_doc_id := rec.cr_id;
3072 l_xla_ev_rec.xla_to_doc_id := rec.cr_id;
3073 l_xla_ev_rec.xla_doc_table := 'CRH';
3074 l_xla_ev_rec.xla_mode := 'O';
3075 l_xla_ev_rec.xla_call := 'B';
3076
3077 IF PG_DEBUG in ('Y', 'C') THEN
3078 fnd_file.put_line(FND_FILE.LOG,'xla_from_doc_id= '|| to_char(rec.cr_id));
3079 fnd_file.put_line(FND_FILE.LOG,'xla_to_doc_id= '|| to_char(rec.cr_id));
3080 fnd_file.put_line(FND_FILE.LOG,'xla_doc_table= '|| 'CRH');
3081 fnd_file.put_line(FND_FILE.LOG,'xla_from_doc_id= '|| 'O');
3082 fnd_file.put_line(FND_FILE.LOG,'xla_from_doc_id= '|| 'B');
3083 END IF;
3084
3085 arp_xla_events.create_events(l_xla_ev_rec);
3086
3087
3088 IF PG_DEBUG in ('Y', 'C') THEN
3089 fnd_file.put_line(FND_FILE.LOG,'RETURN STATUS FROM XLA () '|| to_char(SQLCODE));
3090 END IF;
3091
3092
3093 END LOOP;
3094
3095
3096
3097
3098 EXCEPTION WHEN OTHERS THEN
3099 fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
3100 if dbms_sql.is_open(rem_t3) then
3101 dbms_sql.close_cursor(rem_t3);
3102 end if;
3103 IF PG_DEBUG in ('Y', 'C') THEN
3104 fnd_file.put_line(FND_FILE.LOG,'failed to insert() '|| to_char(SQLCODE));
3105 fnd_file.put_line(FND_FILE.LOG, 'the insert statemnt' || ins_crh1);
3106 END IF;
3107 raise;
3108
3109 END;
3110
3111 /* end insert into crh */
3112
3113 /* insert into dist */
3114 BEGIN
3115
3116
3117 IF PG_DEBUG in ('Y', 'C') THEN
3118 fnd_file.put_line(FND_FILE.LOG,'insert-dist1 ()+');
3119
3120 END IF;
3121
3122
3123 /* the first update into ar_cash_receipt_history */
3124
3125 ins_dist1 := 'INSERT into ar_distributions
3126 (line_id,
3127 source_id,
3128 source_table,
3129 source_type,
3130 code_combination_id,
3131 currency_code,
3132 third_party_id,
3133 third_party_sub_id,
3134 currency_conversion_date,
3135 currency_conversion_rate,
3136 currency_conversion_type,
3137 amount_dr,
3138 amount_cr,
3139 acctd_amount_dr,
3140 acctd_amount_cr,
3141 creation_date,
3142 created_by,
3143 last_update_date,
3144 last_updated_by,
3145 org_id,
3146 last_update_login)
3147 select
3148 ar_distributions_s.nextval,
3149 r.cash_receipt_history_id,
3150 ''CRH'',
3151 decode(:factor_flag,
3152 ''N'',decode(l.lookup_code,
3153 ''1'',''REMITTANCE'',
3154 ''2'',''CONFIRMATION''),
3155 ''Y'',decode(l.lookup_code,
3156 ''1'',''FACTOR'',
3157 ''2'',''CONFIRMATION'')),
3158 decode(l.lookup_code,
3159 ''1'',r.REMMITANCE_CCID,
3160 ''2'',r.code_combination_id), /* its confirmation_ccid */
3161 r.currency_code,
3162 r.pay_from_customer,
3163 r.customer_site_use_id,
3164 r.exchange_date,
3165 r.exchange_rate,
3166 r.exchange_type,
3167 decode(l.lookup_code,
3168 ''1'',decode(sign(to_number(r.cr_amount)),
3169 ''-1'',null,
3170 to_number(r.cr_amount)),
3171 ''2'',decode(sign(to_number(r.cr_amount)),
3172 ''1'',null,
3173 -(to_number(r.cr_amount)))),
3174 decode(l.lookup_code,
3175 ''1'',decode(sign(to_number(r.cr_amount)),
3176 ''1'',null,
3177 -(to_number(r.cr_amount))),
3178 ''2'',decode(sign(to_number(r.cr_amount)),
3179 ''-1'',null,
3180 to_number(r.cr_amount))),
3181 decode(l.lookup_code,
3182 ''1'',decode(sign(to_number(r.cr_acctd_amount)),
3183 ''-1'',null,
3184 to_number(r.cr_acctd_amount)),
3185 ''2'',decode(sign(to_number(r.cr_acctd_amount)),
3186 ''1'',null,
3187 -(to_number(r.cr_acctd_amount)))),
3188 decode(l.lookup_code,
3189 ''1'',decode(sign(to_number(r.cr_acctd_amount)),
3190 ''1'',null,
3191 -(to_number(r.cr_acctd_amount))),
3192 ''2'',decode(sign(to_number(r.cr_acctd_amount)),
3193 ''-1'',null,
3194 to_number(r.cr_acctd_amount))),
3195 sysdate,
3196 :i_created_by,
3197 sysdate,
3198 :i_last_updated_by,
3199 :i_org_id,
3200 :i_last_update_login
3201 FROM ar_cash_receipt_history crh,AR_REMIT_GT r,
3202 ar_lookups l
3203 WHERE crh.cash_receipt_history_id = r.cash_receipt_history_id
3204 AND l.lookup_type = ''AR_CARTESIAN_JOIN''
3205 AND l.lookup_code IN (''1'',''2'')';
3206
3207 rem_t4 := dbms_sql.open_cursor;
3208
3209 dbms_sql.parse (rem_t4,ins_dist1,dbms_sql.v7);
3210
3211 IF PG_DEBUG in ('Y', 'C') THEN
3212 fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
3213 END IF;
3214
3215 dbms_sql.bind_variable (rem_t4,':factor_flag',l_factor_flag);
3216 /* who cols */
3217 dbms_sql.bind_variable (rem_t4,':i_created_by',l_created_by);
3218 dbms_sql.bind_variable (rem_t4,':i_last_updated_by',l_last_updated_by);
3219 dbms_sql.bind_variable (rem_t4,':i_org_id',l_org_id);
3220 dbms_sql.bind_variable (rem_t4,':i_last_update_login',l_last_update_login);
3221
3222
3223 d2 := dbms_sql.execute(rem_t4);
3224
3225 dbms_sql.close_cursor(rem_t4);
3226
3227 IF PG_DEBUG in ('Y', 'C') THEN
3228 fnd_file.put_line(FND_FILE.LOG,'ins_dist1 ()-');
3229 END IF;
3230
3231 EXCEPTION WHEN OTHERS THEN
3232 if dbms_sql.is_open(rem_t4) then
3233 dbms_sql.close_cursor(rem_t4);
3234 end if;
3235 IF PG_DEBUG in ('Y', 'C') THEN
3236 fnd_file.put_line(FND_FILE.LOG, 'the insert dist statement is ' || ins_dist1);
3237 fnd_file.put_line(FND_FILE.LOG,'DIST failed to insert() '|| to_char(SQLCODE));
3238 END IF;
3239 raise;
3240
3241 END;
3242
3243 /* end insert into dist */
3244
3245
3246 EXCEPTION
3247 WHEN others THEN
3248 IF PG_DEBUG in ('Y', 'C') THEN
3249 fnd_file.put_line(FND_FILE.LOG,'Exception : instrem() ');
3250 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
3251 END IF;
3252
3253
3254 END create_and_update_remit_rec_pa ;
3255
3256
3257 /*========================================================================+
3258 | PROCEDURE select_and_update_rec |
3259 | |
3260 | DESCRIPTION |
3261 | |
3262 | This procedure is used to select receipts to be remitted |
3263 | update and insert records into the necessary tables. |
3264 | PSEUDO CODE/LOGIC |
3265 | |
3266 | PARAMETERS |
3267 | |
3268 | |
3269 | KNOWN ISSUES |
3270 | |
3271 | NOTES |
3272 | |
3273 | |
3274 | MODIFICATION HISTORY |
3275 | Date Author Description of Changes |
3276 | 16-JUL-2005 bichatte Created |
3277 *=========================================================================*/
3278
3279 PROCEDURE select_update_rec(
3280 p_customer_number_l IN hz_cust_accounts.account_number%TYPE,
3281 p_customer_number_h IN hz_cust_accounts.account_number%TYPE,
3282 p_trx_date_l IN ar_payment_schedules.trx_date%TYPE,
3283 p_trx_date_h IN ar_payment_schedules.trx_date%TYPE,
3284 p_due_date_l IN ar_payment_schedules.due_date%TYPE,
3285 p_due_date_h IN ar_payment_schedules.due_date%TYPE,
3286 p_trx_num_l IN ar_payment_schedules.trx_number%TYPE,
3287 p_trx_num_h IN ar_payment_schedules.trx_number%TYPE,
3288 p_remittance_total_to IN ar_cash_receipts.amount%TYPE,
3289 p_remittance_total_from IN ar_cash_receipts.amount%TYPE,
3290 p_batch_id IN ar_batches.batch_id%TYPE,
3291 p_receipt_method_id IN ar_receipt_methods.receipt_method_id%TYPE,
3292 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
3293 p_payment_type_code IN ar_receipt_methods.payment_type_code%TYPE,
3294 p_sob_id IN ar_cash_receipts.set_of_books_id%TYPE,
3295 p_remit_method_code IN ar_receipt_classes.remit_method_code%TYPE,
3296 p_remit_bank_account_id IN ar_cash_receipts.remittance_bank_account_id%TYPE,
3297 p_return_status OUT NOCOPY VARCHAR2
3298 ) IS
3299
3300 remit_recs INTEGER;
3301 l_sel_stmt long;
3302 l_rows_processed INTEGER;
3303 l_rows_fetched INTEGER;
3304 p_cash_receipt_id NUMBER(15);
3305 p_amount ar_cash_receipts.amount%TYPE;
3306 p_payment_method_id ar_receipt_methods.receipt_method_id%TYPE;
3307 cash_receipt_id_array dbms_sql.Number_Table;
3308 cr_id_array dbms_sql.Number_Table;
3309 amt_array dbms_sql.Number_Table;
3310 ins_recs INTEGER;
3311
3312 i number;
3313 dummy number;
3314 upd_stmt2 varchar2(1000);
3315 rem_t2 number;
3316 dum number;
3317 ins_stmt varchar2(1000);
3318 ignore INTEGER;
3319
3320 l_request_id NUMBER;
3321 l_last_updated_by NUMBER;
3322 l_created_by NUMBER;
3323 l_last_update_login NUMBER;
3324 l_program_application_id NUMBER;
3325 l_program_id NUMBER;
3326
3327 BEGIN
3328 l_request_id := arp_standard.profile.request_id;
3329 l_last_updated_by := arp_standard.profile.last_update_login ;
3330 l_created_by := arp_standard.profile.user_id ;
3331 l_last_update_login := arp_standard.profile.last_update_login ;
3332 l_program_application_id := arp_standard.application_id ;
3333 l_program_id := arp_standard.profile.program_id;
3334
3335 IF PG_DEBUG in ('Y', 'C') THEN
3336 fnd_file.put_line(FND_FILE.LOG,'sel and upd receipts start ()+');
3337 fnd_file.put_line(FND_FILE.LOG,'the input parameters are ');
3338 fnd_file.put_line(FND_FILE.LOG,' the value of p_customer_number_l '|| p_customer_number_l);
3339 fnd_file.put_line(FND_FILE.LOG,' the value of p_customer_number_h '|| p_customer_number_h );
3340 fnd_file.put_line(FND_FILE.LOG,' the value of p_trx_date_l' || p_trx_date_l);
3341 fnd_file.put_line(FND_FILE.LOG,' the value of p_trx_date_h' || p_trx_date_h);
3342 fnd_file.put_line(FND_FILE.LOG,' the value of p_due_date_l' || p_due_date_l);
3343 fnd_file.put_line(FND_FILE.LOG,' the value of p_due_date_h' || p_due_date_h);
3344 fnd_file.put_line(FND_FILE.LOG,' the value of p_trx_num_l' || p_trx_num_l);
3345 fnd_file.put_line(FND_FILE.LOG,' the value of p_trx_num_h' || p_trx_num_h);
3346 fnd_file.put_line(FND_FILE.LOG,' the value of p_remittance_total_to' || p_remittance_total_to);
3347 fnd_file.put_line(FND_FILE.LOG,' the value of p_remittance_total_from'|| p_remittance_total_from);
3348 fnd_file.put_line(FND_FILE.LOG,' the value of p_batch_id ' || p_batch_id );
3349 fnd_file.put_line(FND_FILE.LOG,' the value of p_receipt_method_id ' || p_receipt_method_id );
3350 fnd_file.put_line(FND_FILE.LOG,' the value of p_currency_code' || p_currency_code);
3351 fnd_file.put_line(FND_FILE.LOG,' the value of p_payment_type_code' || p_payment_type_code);
3352 fnd_file.put_line(FND_FILE.LOG,' the value of p_sob_id ' || p_sob_id );
3353 fnd_file.put_line(FND_FILE.LOG,' the value of p_remit_method_code' || p_remit_method_code);
3354 fnd_file.put_line(FND_FILE.LOG,' the value of p_remit_bank_account_id'|| p_remit_bank_account_id );
3355 END IF;
3356
3357 IF PG_PARALLEL IN ('Y', 'C') THEN
3358 ins_stmt := 'insert /*+ append parallel(gtt) */ into ar_rem_cr_id_gtt gtt ';
3359 ELSE
3360 ins_stmt := 'insert /*+ append */ into ar_rem_cr_id_gtt gtt ';
3361 END IF;
3362 ins_stmt := ins_stmt ||' select /*+ index(crh AR_CASH_RECEIPT_HISTORY_N6) */
3363 CASH_RECEIPT_HISTORY_ID,
3364 cash_receipt_id
3365 from ar_cash_receipt_history crh
3366 where crh.status = ''CONFIRMED'' AND
3367 crh.current_record_flag = ''Y'' ';
3368 commit;
3369 execute immediate 'alter session enable parallel dml';
3370 --move all the receipts in confirmed status to GT table
3371 ins_recs := dbms_sql.open_cursor;
3372 dbms_sql.parse (ins_recs, ins_stmt, dbms_sql.v7);
3373 ignore := dbms_sql.execute(ins_recs);
3374 commit;
3375
3376 IF PG_PARALLEL IN ('Y', 'C') THEN
3377 l_sel_stmt := ' SELECT /*+ unnest LEADING(crh,crh1,cr) parallel(crh) parallel(crh1) parallel(cr) parallel(ps) swap_join_inputs(rm) swap_join_inputs(rclass) use_hash(rm,rclass) use_nl(crh1,cr,ps,rma1,rma2) cardinality(crh,10000) */';
3378 ELSE
3379 l_sel_stmt := ' SELECT /*+ unnest LEADING(crh,crh1,cr) swap_join_inputs(rm) swap_join_inputs(rclass) use_hash(rm,rclass) use_nl(crh1,cr,ps,rma1,rma2) cardinality(crh,10000) */';
3380 END IF;
3381 l_sel_stmt := l_sel_stmt || ' cr.cash_receipt_id
3382 FROM ar_rem_cr_id_gtt crh,
3383 ar_cash_receipt_history crh1,
3384 ar_cash_receipts cr,
3385 ar_payment_schedules ps,
3386 ar_receipt_classes rclass,
3387 ar_receipt_methods rm,
3388 ar_receipt_method_accounts rma1,
3389 ar_receipt_method_accounts rma2
3390 WHERE crh1.status = ''CONFIRMED''
3391 AND crh1.current_record_flag = ''Y''
3392 AND crh1.cash_receipt_history_id = crh.cash_receipt_history_id
3393 AND crh.cash_receipt_id = cr.cash_receipt_id
3394 AND NOT EXISTS
3395 (SELECT 1 FROM ar_lookups l
3396 WHERE NVL(cr.reversal_category,''~'') = l.lookup_code
3397 AND l.lookup_type = ''REVERSAL_CATEGORY_TYPE'')
3398 AND cr.receipt_method_id = nvl(:bs_receipt_method_id,cr.receipt_method_id)
3399 AND cr.currency_code = :bs_currency
3400 AND cr.cash_receipt_id = ps.cash_receipt_id(+)
3401 AND cr.receipt_method_id = rm.receipt_method_id
3402 AND (nvl(rm.payment_channel_code,''~'')<>''CREDIT_CARD'' OR (rm.payment_channel_code=''CREDIT_CARD'' AND cr.cc_error_flag IS NULL))
3403 AND cr.selected_remittance_batch_id is null
3404 AND (( cr.amount >= 0) OR
3405 (cr.type = ''MISC'' and cr.amount < 0))
3406 AND cr.set_of_books_id = :bs_sob_id
3407 AND rm.receipt_class_id = rclass.receipt_class_id
3408 AND (rclass.remit_method_code = :bs_remit_method_code
3409 OR rclass.remit_method_code = ''STANDARD_AND_FACTORING''
3410 )
3411 AND rma1.receipt_method_id = cr.receipt_method_id
3412 AND rma1.REMIT_BANK_ACCT_USE_ID = cr.REMIT_BANK_ACCT_USE_ID
3413 AND rma2.receipt_method_id = rma1.receipt_method_id
3414 AND rma2.REMIT_BANK_ACCT_USE_ID= :bs_remit_account_id
3415 AND ((
3416 (nvl(cr.override_remit_account_flag,''Y'') = ''Y'')
3417 AND rma1.unapplied_ccid = rma2.unapplied_ccid
3418 AND rma1.on_account_ccid = rma2.on_account_ccid
3419 AND rma1.unidentified_ccid = rma2.unidentified_ccid
3420 )
3421 OR
3422 (
3423 (nvl(cr.override_remit_account_flag,''Y'') = ''N'')
3424 and cr.REMIT_BANK_ACCT_USE_ID = :bs_remit_account_id
3425 ))' ;
3426
3427 IF p_receipt_method_id IS NOT NULL THEN
3428 l_sel_stmt := l_sel_stmt || '
3429 AND decode(nvl(rm.payment_channel_code,''~''),''CREDIT_CARD'',''CREDIT_CARD'',''OTHER'')
3430 = decode(nvl(:bs_payment_type_code,''~''),''CREDIT_CARD'',''CREDIT_CARD'',''OTHER'') ';
3431 END IF;
3432
3433 IF p_customer_number_l IS NOT NULL OR p_customer_number_h IS NOT NULL THEN
3434
3435 l_sel_stmt := l_sel_stmt || '
3436 AND EXISTS ( select ''x''
3437 from hz_cust_accounts rc
3438 where rc.cust_account_id = cr.pay_from_customer ' ;
3439
3440 IF p_customer_number_l IS NOT NULL THEN
3441 l_sel_stmt := l_sel_stmt || ' and rc.account_number >= :customer_number_l ';
3442 END IF ;
3443 IF p_customer_number_h IS NOT NULL THEN
3444 l_sel_stmt := l_sel_stmt || ' and rc.account_number <= :customer_number_h ';
3445 END IF;
3446
3447 l_sel_stmt := l_sel_stmt || ' ) ';
3448
3449 END IF ;
3450
3451 IF p_due_date_l IS NOT NULL THEN
3452 l_sel_stmt := l_sel_stmt || ' AND DECODE(cr.type,''CASH'',ps.due_date,''MISC'',cr.deposit_date,NULL) >= trunc(:due_date_l) ';
3453 END IF;
3454
3455 IF p_due_date_h IS NOT NULL THEN
3456 l_sel_stmt := l_sel_stmt || ' AND DECODE(cr.type,''CASH'',ps.due_date,''MISC'',cr.deposit_date,NULL) <= trunc(:due_date_h) ';
3457 END IF;
3458
3459 IF p_trx_date_l IS NOT NULL THEN
3460 l_sel_stmt := l_sel_stmt || ' AND cr.receipt_date >= trunc(:trx_date_l)';
3461 END IF;
3462
3463 IF p_trx_date_h IS NOT NULL THEN
3464 l_sel_stmt := l_sel_stmt || ' AND cr.receipt_date <= trunc(:trx_date_h)';
3465 END IF;
3466
3467 IF p_trx_num_l IS NOT NULL THEN
3468 l_sel_stmt := l_sel_stmt || ' AND cr.receipt_number >= :rcpt_number_l';
3469 END IF;
3470
3471
3472 IF p_trx_num_h IS NOT NULL THEN
3473 l_sel_stmt := l_sel_stmt || ' AND cr.receipt_number <= :rcpt_number_h';
3474 END IF;
3475
3476 IF p_remittance_total_to IS NOT NULL THEN
3477 l_sel_stmt := l_sel_stmt || ' AND cr.amount >= to_number(:remittance_total_to)';
3478 END IF;
3479
3480
3481 IF p_remittance_total_from IS NOT NULL THEN
3482 l_sel_stmt := l_sel_stmt || ' AND cr.amount <= to_number(:remittance_total_from)';
3483 END IF;
3484 IF PG_PARALLEL IN ('Y', 'C') THEN
3485 upd_stmt2 := ' UPDATE /*+ parallel(R) index(R) */ ar_cash_receipts R';
3486 ELSE
3487 upd_stmt2 := ' UPDATE /*+ index(R) */ ar_cash_receipts R';
3488 END IF;
3489 upd_stmt2 := upd_stmt2 ||' SET selected_remittance_batch_id = :u_batch_id,
3490 REMIT_BANK_ACCT_USE_ID = :u_remit_bank_account_id,
3491 last_update_date = sysdate,
3492 last_updated_by = :i_last_updated_by,
3493 last_update_login = :i_last_update_login,
3494 request_id = :i_request_id,
3495 program_application_id = :i_program_application_id,
3496 program_id = :i_program_id,
3497 program_update_date = sysdate
3498 WHERE selected_remittance_batch_id is null ';
3499
3500 l_sel_stmt := upd_stmt2 ||' AND cash_receipt_id IN ( '|| l_sel_stmt || ')';
3501
3502 remit_recs := dbms_sql.open_cursor;
3503
3504 dbms_sql.parse (remit_recs,l_sel_stmt,dbms_sql.v7);
3505
3506 /* bind the variables used in update statement */
3507 dbms_sql.bind_variable (remit_recs,':u_batch_id',p_batch_id);
3508 dbms_sql.bind_variable (remit_recs,':u_remit_bank_account_id',p_remit_bank_account_id);
3509
3510 /* who cols */
3511 dbms_sql.bind_variable (remit_recs,':i_last_updated_by',l_last_updated_by);
3512 dbms_sql.bind_variable (remit_recs,':i_last_update_login',l_last_update_login);
3513 dbms_sql.bind_variable (remit_recs,':i_request_id',l_request_id);
3514 dbms_sql.bind_variable (remit_recs,':i_program_application_id',l_program_application_id);
3515 dbms_sql.bind_variable (remit_recs,':i_program_id',l_program_id);
3516
3517 /* bind the variables */
3518 dbms_sql.bind_variable (remit_recs,':bs_receipt_method_id',p_receipt_method_id);
3519 dbms_sql.bind_variable (remit_recs,':bs_currency',p_currency_code);
3520
3521 IF p_receipt_method_id IS NOT NULL THEN
3522 dbms_sql.bind_variable (remit_recs,':bs_payment_type_code', p_payment_type_code);
3523 END IF;
3524
3525 dbms_sql.bind_variable (remit_recs,':bs_sob_id', p_sob_id);
3526 dbms_sql.bind_variable (remit_recs,':bs_remit_method_code',p_remit_method_code);
3527 dbms_sql.bind_variable (remit_recs,':bs_remit_account_id', p_remit_bank_account_id);
3528
3529 IF p_customer_number_l IS NOT NULL THEN
3530 dbms_sql.bind_variable (remit_recs,':customer_number_l',p_customer_number_l);
3531 END IF;
3532
3533 IF p_customer_number_h IS NOT NULL THEN
3534 dbms_sql.bind_variable (remit_recs,':customer_number_h',p_customer_number_h);
3535 END IF;
3536
3537 IF p_due_date_l IS NOT NULL THEN
3538 dbms_sql.bind_variable (remit_recs,':due_date_l',p_due_date_l);
3539 END IF;
3540
3541 IF p_due_date_h IS NOT NULL THEN
3542 dbms_sql.bind_variable (remit_recs,':due_date_h',p_due_date_h);
3543 END IF;
3544
3545 IF p_trx_date_l IS NOT NULL THEN
3546 dbms_sql.bind_variable (remit_recs,':trx_date_l',p_trx_date_l);
3547 END IF;
3548
3549 IF p_trx_date_h IS NOT NULL THEN
3550 dbms_sql.bind_variable (remit_recs,':trx_date_h',p_trx_date_h);
3551 END IF;
3552
3553 IF p_trx_num_l IS NOT NULL THEN
3554 dbms_sql.bind_variable (remit_recs,':rcpt_number_l',p_trx_num_l);
3555 END IF;
3556
3557 IF p_trx_num_h IS NOT NULL THEN
3558 dbms_sql.bind_variable (remit_recs,':rcpt_number_h',p_trx_num_h);
3559 END IF;
3560
3561 IF p_remittance_total_to IS NOT NULL THEN
3562 dbms_sql.bind_variable (remit_recs,':remittance_total_to',p_remittance_total_to );
3563 END IF;
3564
3565 IF p_remittance_total_from IS NOT NULL THEN
3566 dbms_sql.bind_variable (remit_recs,':remittance_total_from',p_remittance_total_from );
3567 END IF;
3568
3569 IF PG_DEBUG in ('Y', 'C') THEN
3570 fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
3571 END IF;
3572
3573 commit;
3574 execute immediate 'alter session enable parallel dml';
3575 l_rows_processed := dbms_sql.execute( remit_recs );
3576 commit;
3577
3578 l_rows_fetched := dbms_sql.last_row_count ;
3579
3580 IF PG_DEBUG in ('Y', 'C') THEN
3581 fnd_file.put_line(FND_FILE.LOG,'the no of rows fetched ' || l_rows_fetched);
3582 END IF;
3583
3584 dbms_sql.close_cursor( remit_recs );
3585
3586 IF PG_DEBUG in ('Y', 'C') THEN
3587 fnd_file.put_line(FND_FILE.LOG,'sel_and_update recs ()-');
3588 END IF;
3589
3590 EXCEPTION
3591 WHEN others THEN
3592 fnd_file.put_line(FND_FILE.LOG,'Exception : select and upd err () ');
3593 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
3594 fnd_file.put_line(FND_FILE.LOG, 'the select statemnt' || l_sel_stmt);
3595 raise;
3596 END select_update_rec;
3597
3598 /*========================================================================+
3599 | PROCEDURE process_pay_receipt |
3600 | |
3601 | DESCRIPTION |
3602 | |
3603 | This procedure is used to select receipts to be remitted |
3604 | update and insert records into the necessary tables. |
3605 | PSEUDO CODE/LOGIC |
3606 | |
3607 | PARAMETERS |
3608 | |
3609 | |
3610 | KNOWN ISSUES |
3611 | |
3612 | NOTES |
3613 | |
3614 | |
3615 | MODIFICATION HISTORY |
3616 | Date Author Description of Changes |
3617 | 16-JUL-2005 bichatte Created |
3618 *=========================================================================*/
3619
3620 PROCEDURE process_pay_receipt(
3621 p_batch_id IN NUMBER,
3622 p_called_from IN VARCHAR2,
3623 x_msg_count OUT NOCOPY NUMBER,
3624 x_msg_data OUT NOCOPY VARCHAR2,
3625 x_return_status OUT NOCOPY VARCHAR2
3626 ) IS
3627
3628 CURSOR rct_info_cur IS
3629 SELECT cr.receipt_number,
3630 cr.amount,
3631 cr.cash_receipt_id,
3632 cr.currency_code,
3633 rm.PAYMENT_CHANNEL_CODE, /* NEW ADDED */
3634 rc.creation_status, /* AR USE */
3635 cr.org_id,
3636 party.party_id,
3637 cr.pay_from_customer,
3638 cr.customer_site_use_id,
3639 cr.payment_trxn_extension_id,
3640 cr.selected_remittance_batch_id,
3641 cr.receipt_date
3642 FROM ar_cash_receipts cr,
3643 ar_receipt_methods rm,
3644 ar_receipt_classes rc,
3645 hz_cust_accounts hca,
3646 hz_parties party,
3647 ar_remit_gt g
3648 WHERE cr.selected_remittance_batch_id = p_batch_id
3649 AND g.cash_receipt_id = cr.cash_receipt_id
3650 AND hca.party_id = party.party_id
3651 AND hca.cust_account_id = cr.pay_from_customer
3652 AND cr.receipt_method_id = rm.receipt_method_id
3653 AND rm.receipt_class_id = rc.receipt_class_id;
3654
3655 -- rct_info rct_info_cur%ROWTYPE;
3656 l_cr_rec ar_cash_receipts_all%ROWTYPE;
3657 l_org_type HR_ALL_ORGANIZATION_UNITS.TYPE%TYPE;
3658 l_action VARCHAR2(80);
3659 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3660 l_msg_count NUMBER;
3661 l_msg_data VARCHAR2(2000);
3662
3663 l_payment_trxn_extension_id NUMBER;
3664 l_amount NUMBER;
3665 l_calling_app_req_code NUMBER;
3666 l_iby_msg_data VARCHAR2(2000);
3667 l_amount_rec IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
3668 l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
3669 l_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type; /* OUT RESPONSE STRUCTURE */
3670
3671
3672 /* DECLARE the variables required for the payment engine (AUTH) all the REC TYPES */
3673
3674 l_payee_rec IBY_FNDCPT_TRXN_PUB.PayeeContext_rec_type;
3675 l_auth_attribs_rec IBY_FNDCPT_TRXN_PUB.AuthAttribs_rec_type;
3676 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
3677 l_authresult_rec IBY_FNDCPT_TRXN_PUB.AuthResult_rec_type; /* OUT AUTH RESULT STRUCTURE */
3678 l_auth_flag VARCHAR2(1);
3679 l_auth_id NUMBER;
3680 l_vend_msg_data VARCHAR2(2000);
3681
3682 /* END DECLARE the variables required for the payment engine (AUTH) all the REC TYPES */
3683
3684
3685 /* declare variables for settlement */
3686
3687 ls_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3688 ls_msg_count NUMBER;
3689 ls_msg_data VARCHAR2(2000);
3690 ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
3691 ls_iby_msg_data VARCHAR2(2000);
3692 l_cr_id ar_cash_receipts.cash_receipt_id%type;
3693 l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
3694 l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
3695 l_count1 NUMBER;
3696
3697 /* end declare varaibles for settlement */
3698
3699
3700
3701
3702 lc_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
3703 lc_amount_rec IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
3704 lc_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3705 lc_msg_count NUMBER;
3706 lc_msg_data VARCHAR2(2000);
3707 lc_iby_msg_data VARCHAR2(2000);
3708 lc_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
3709
3710
3711
3712 l_request_id NUMBER;
3713 l_last_updated_by NUMBER;
3714 l_created_by NUMBER;
3715 l_last_update_login NUMBER;
3716 l_program_application_id NUMBER;
3717 l_program_id NUMBER;
3718 l_call_settlement VARCHAR2(1) := 'N';
3719
3720 /* 7666285 - for passing settlement_date on returns */
3721 lcr_receipt_attr IBY_FNDCPT_TRXN_PUB.receiptattribs_rec_type;
3722
3723 BEGIN
3724
3725
3726 IF PG_DEBUG in ('Y', 'C') THEN
3727 fnd_file.put_line(FND_FILE.LOG, 'Entering payment processing...');
3728 END IF;
3729
3730
3731 l_request_id := arp_standard.profile.request_id;
3732 l_last_updated_by := arp_standard.profile.last_update_login ;
3733 l_created_by := arp_standard.profile.user_id ;
3734 l_last_update_login := arp_standard.profile.last_update_login ;
3735 l_program_application_id := arp_standard.application_id ;
3736 l_program_id := arp_standard.profile.program_id;
3737
3738
3739
3740
3741 FOR rct_info in rct_info_cur LOOP
3742
3743 l_payment_trxn_extension_id := rct_info.payment_trxn_extension_id;
3744 l_amount := rct_info.amount;
3745
3746 /* CHECK for AUTH here and do it if necessary start */
3747
3748
3749 IF ((l_payment_trxn_extension_id is not null ) AND (l_amount >0) ) THEN
3750
3751
3752 l_call_settlement := 'Y'; /* set the flag for calling settlement */
3753
3754
3755 IF PG_DEBUG in ('Y', 'C') THEN
3756 fnd_file.put_line(FND_FILE.LOG, 'check and then call Auth');
3757 END IF;
3758
3759 -- Step 1: (always performed):
3760
3761 -- set up payee record:
3762
3763 l_payee_rec.org_id := rct_info.org_id; -- receipt's org_id
3764 l_payee_rec.org_type := 'OPERATING_UNIT' ; -- ( HR_ORGANIZATION_UNITS )
3765
3766
3767 -- set up payer (=customer) record:
3768
3769 l_payer_rec.Payment_Function := 'CUSTOMER_PAYMENT';
3770 l_payer_rec.Party_Id := rct_info.party_id; -- receipt customer party id mandatory
3771 l_payer_rec.org_id := rct_info.org_id ;
3772 l_payer_rec.org_type := 'OPERATING_UNIT';
3773 l_payer_rec.Cust_Account_Id :=rct_info.pay_from_customer; -- receipt customer account_id
3774 l_payer_rec.Account_Site_Id :=rct_info.customer_site_use_id; -- receipt customer site_id
3775
3776
3777 if rct_info.customer_site_use_id is NULL THEN
3778
3779 l_payer_rec.org_id := NULL;
3780 l_payer_rec.org_type := NULL;
3781
3782 end if;
3783
3784 -- set up auth_attribs record:
3785 l_auth_attribs_rec.RiskEval_Enable_Flag := 'N';
3786 -- set up trxn_attribs record:
3787 l_trxn_attribs_rec.Originating_Application_Id := arp_standard.application_id;
3788 l_trxn_attribs_rec.order_id := rct_info.receipt_number;
3789 l_trxn_attribs_rec.Trxn_Ref_Number1 := 'RECEIPT';
3790 l_trxn_attribs_rec.Trxn_Ref_Number2 := rct_info.cash_receipt_id;
3791
3792 -- set up amounts
3793
3794
3795 l_amount_rec.value := rct_info.amount;
3796 l_amount_rec.currency_code := rct_info.currency_code;
3797
3798
3799
3800
3801
3802 -- determine whether to AUTHORIZE
3803
3804 IF PG_DEBUG in ('Y', 'C') THEN
3805 fnd_file.put_line(FND_FILE.LOG, 'Calling get auth for pmt_trxn_extn_id ');
3806 fnd_file.put_line(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_payment_trxn_extension_id) );
3807 END IF;
3808
3809 BEGIN
3810
3811 IBY_AR_UTILS.get_authorization_status(l_payment_trxn_extension_id,l_auth_flag);
3812
3813 /* SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
3814 into l_auth_flag
3815 FROM iby_trxn_summaries_all summ,
3816 iby_fndcpt_tx_operations op
3817 WHERE summ.transactionid = op.transactionid
3818 AND reqtype = 'ORAPMTREQ'
3819 AND status IN(0, 100)
3820 AND trxntypeid IN(2, 3, 20)
3821 AND op.trxn_extension_id = l_payment_trxn_extension_id
3822 AND summ.trxnmid =
3823 (SELECT MAX(trxnmid)
3824 FROM iby_trxn_summaries_all
3825 WHERE transactionid = summ.transactionid
3826 AND reqtype = 'ORAPMTREQ'
3827 AND status IN(0, 100)
3828 AND trxntypeid IN(2, 3, 20)); */
3829 EXCEPTION
3830 WHEN OTHERS THEN
3831 fnd_file.put_line(FND_FILE.LOG,'Exception in IBY_AR_UTILS.get_authorization_status');
3832 l_auth_flag := 'N';
3833 END;
3834
3835 fnd_file.put_line(FND_FILE.LOG,'the value of auth_flag is = ' || l_auth_flag);
3836 /* Commenting out as per bug 6996205:
3837 IF l_auth_flag = 'Y' then
3838
3839 select AUTHORIZATION_ID
3840 into l_auth_id
3841 from IBY_TRXN_EXT_AUTHS_V
3842 where TRXN_EXTENSION_ID = l_payment_trxn_extension_id;
3843
3844 fnd_file.put_line(FND_FILE.LOG, 'the value of auth_id is = ' || l_auth_id);
3845
3846 END IF;
3847 */
3848
3849 IF nvl(l_auth_flag,'N') <> 'Y' then
3850 fnd_file.put_line(FND_FILE.LOG,'auth needs to called');
3851
3852 IF PG_DEBUG in ('Y', 'C') THEN
3853 fnd_file.put_line(FND_FILE.LOG, 'Calling get auth for pmt_trxn_extn_id ');
3854 fnd_file.put_line(FND_FILE.LOG, ' l_payee_rec.org_id ' || to_char( l_payee_rec.org_id) );
3855 fnd_file.put_line(FND_FILE.LOG, ' l_payee_rec.org_type ' || to_char( l_payee_rec.org_type) );
3856 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Payment_Function '|| to_char( l_payer_rec.Payment_Function));
3857 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.Party_Id ' || to_char( l_payer_rec.Party_Id) );
3858 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_id ' || to_char( l_payer_rec.org_id) );
3859 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_type ' || to_char( l_payer_rec.org_type) );
3860 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Cust_Account_Id ' || to_char(l_payer_rec.Cust_Account_Id) );
3861 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Account_Site_Id ' || to_char(l_payer_rec.Account_Site_Id) );
3862 fnd_file.put_line(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_payment_trxn_extension_id) );
3863 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.value: ' || to_char(l_amount_rec.value) );
3864 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.currency_code: ' || l_amount_rec.currency_code );
3865
3866 fnd_file.put_line(FND_FILE.LOG, 'Calling get_auth for pmt_trxn_extn_id ');
3867 END IF;
3868
3869 BEGIN
3870 IBY_FNDCPT_TRXN_PUB.Create_Authorization(
3871 p_api_version => 1.0,
3872 p_init_msg_list => FND_API.G_TRUE,
3873 x_return_status => l_return_status,
3874 x_msg_count => l_msg_count,
3875 x_msg_data => l_msg_data,
3876 p_payer => l_payer_rec,
3877 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
3878 p_payee => l_payee_rec,
3879 p_trxn_entity_id => l_payment_trxn_extension_id,
3880 p_auth_attribs => l_auth_attribs_rec,
3881 p_amount => l_amount_rec,
3882 x_auth_result => l_authresult_rec, -- out auth result struct
3883 x_response => l_response_rec ); -- out response struct
3884
3885
3886 x_msg_count := l_msg_count;
3887 x_msg_data := l_msg_data;
3888
3889 fnd_file.put_line(FND_FILE.LOG,'x_return_status :<' || l_return_status || '>');
3890 fnd_file.put_line(FND_FILE.LOG,'x_msg_count :<' || l_msg_count || '>');
3891
3892 FOR i IN 1..l_msg_count LOOP
3893 fnd_file.put_line(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
3894 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
3895 END LOOP;
3896
3897 IF PG_DEBUG in ('Y', 'C') THEN
3898 fnd_file.put_line(FND_FILE.LOG, '-------------------------------------');
3899 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Code: ' || l_response_rec.Result_Code);
3900 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Category: '|| l_response_rec.Result_Category);
3901 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_message :'|| l_response_rec.Result_message );
3902 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.Auth_Id: ' || l_authresult_rec.Auth_Id);
3903 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.Auth_Date: ' || l_authresult_rec.Auth_Date);
3904 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.Auth_Code: ' || l_authresult_rec.Auth_Code);
3905 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.AVS_Code: ' || l_authresult_rec.AVS_Code);
3906 fnd_file.put_line(FND_FILE.LOG,'l_authresult_rec.Instr_SecCode_Check:'||l_authresult_rec.Instr_SecCode_Check);
3907 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.PaymentSys_Code: ' || l_authresult_rec.PaymentSys_Code);
3908 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.PaymentSys_Msg: ' || l_authresult_rec.PaymentSys_Msg);
3909
3910 END IF;
3911
3912 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3913
3914 fnd_file.put_line(FND_FILE.LOG,'the value of auth_id is = ' || (l_authresult_rec.Auth_Id));
3915
3916 END IF;
3917
3918
3919 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3920 G_ERROR := 'Y' ;
3921
3922 FND_MESSAGE.set_name('AR', 'AR_CC_AUTH_FAILED');
3923 FND_MSG_PUB.Add;
3924
3925 IF l_response_rec.Result_Code is NOT NULL THEN
3926
3927 ---Raise the PAYMENT error code concatenated with the message
3928
3929 l_iby_msg_data := substrb( l_response_rec.Result_Code || ': '||
3930 l_response_rec.Result_Message , 1, 240);
3931
3932 fnd_file.put_line(FND_FILE.LOG, 'l_iby_msg_data: ' || l_iby_msg_data);
3933
3934 UPDATE ar_cash_receipts
3935 SET cc_error_flag = 'Y',
3936 cc_error_code = l_response_rec.Result_Code,
3937 cc_error_text = l_response_rec.Result_Message,
3938 last_updated_by = l_last_updated_by,
3939 last_update_date = sysdate,
3940 last_update_login = l_last_update_login,
3941 request_id = l_request_id,
3942 program_application_id= l_program_application_id,
3943 program_id = l_program_id,
3944 program_update_date = sysdate
3945 WHERE cash_receipt_id = rct_info.cash_receipt_id;
3946
3947 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
3948
3949
3950
3951 END IF;
3952
3953 IF l_authresult_rec.PaymentSys_Code is not null THEN
3954
3955 ---Raise the VENDOR error code concatenated with the message
3956
3957 l_vend_msg_data := substrb(l_authresult_rec.PaymentSys_Code || ': '||
3958 l_authresult_rec.PaymentSys_Msg , 1, 240 );
3959
3960 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
3961 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_vend_msg_data);
3962
3963 UPDATE ar_cash_receipts
3964 SET cc_error_flag = 'Y',
3965 cc_error_code = l_authresult_rec.PaymentSys_Code,
3966 cc_error_text = l_authresult_rec.PaymentSys_Msg,
3967 last_updated_by = l_last_updated_by,
3968 last_update_date = sysdate,
3969 last_update_login = l_last_update_login,
3970 request_id = l_request_id,
3971 program_application_id= l_program_application_id,
3972 program_id = l_program_id,
3973 program_update_date = sysdate
3974 WHERE cash_receipt_id = rct_info.cash_receipt_id;
3975
3976 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
3977
3978 END IF;
3979
3980 insert_exceptions(
3981 p_batch_id =>p_batch_id,
3982 p_request_id =>l_request_id,
3983 p_paying_customer_id =>l_payer_rec.cust_account_id,
3984 p_cash_receipt_id => rct_info.cash_receipt_id,
3985 p_exception_code => 'AR_CC_AUTH_FAILED',
3986 p_additional_message => substrb(l_iby_msg_data||l_vend_msg_data,1,240)
3987 );
3988
3989 x_return_status := l_return_status;
3990
3991 END IF; /* End the error handling CREATE */
3992 EXCEPTION
3993 WHEN OTHERS THEN
3994 G_ERROR := 'Y' ;
3995
3996 fnd_file.put_line(FND_FILE.LOG,'Exception : Create_Authorization () ');
3997 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
3998
3999 insert_exceptions(
4000 p_batch_id =>p_batch_id,
4001 p_request_id =>l_request_id,
4002 p_paying_customer_id =>l_payer_rec.cust_account_id,
4003 p_cash_receipt_id => rct_info.cash_receipt_id,
4004 p_exception_code => 'AR_CC_AUTH_FAILED',
4005 p_additional_message => l_iby_msg_data
4006 );
4007
4008 UPDATE ar_cash_receipts
4009 SET cc_error_flag = 'Y',
4010 last_updated_by = l_last_updated_by,
4011 last_update_date = sysdate,
4012 last_update_login = l_last_update_login,
4013 request_id = l_request_id,
4014 program_application_id= l_program_application_id,
4015 program_id = l_program_id,
4016 program_update_date = sysdate
4017 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4018
4019 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
4020 END; /* END of BEGIN */
4021
4022
4023 END IF; /* END if of auth flag <>'Y' */
4024
4025 END IF ; /* end if of pmt_etx is not null and amt >0 */
4026 /* CHECK for AUTH here and do it if necessary end */
4027
4028 IF ((l_payment_trxn_extension_id is not null ) AND (l_amount <0) ) THEN
4029
4030 /* HERE WE HAVE TO CALL RETURN */
4031
4032 -- set up payer (=customer) record:
4033
4034 lc_payer_rec.Payment_Function := 'CUSTOMER_PAYMENT';
4035 lc_payer_rec.Party_Id := rct_info.party_id; -- receipt customer party id mandatory
4036 lc_payer_rec.org_id := rct_info.org_id ;
4037 lc_payer_rec.org_type := 'OPERATING_UNIT';
4038 lc_payer_rec.Cust_Account_Id :=rct_info.pay_from_customer; -- receipt customer account_id
4039 lc_payer_rec.Account_Site_Id :=rct_info.customer_site_use_id; -- receipt customer site_id
4040
4041 -- set up amounts
4042
4043 lc_amount_rec.value := ABS(rct_info.amount);
4044 lc_amount_rec.currency_code := rct_info.currency_code;
4045
4046 /* 7666285 - settlement_date */
4047 lcr_receipt_attr.settlement_date := rct_info.receipt_date;
4048
4049
4050 if rct_info.customer_site_use_id is NULL THEN
4051
4052 lc_payer_rec.org_id := NULL;
4053 lc_payer_rec.org_type := NULL;
4054
4055 end if;
4056
4057 IF PG_DEBUG in ('Y', 'C') THEN
4058 fnd_file.put_line(FND_FILE.LOG, 'Calling return for pmt_trxn_extn_id ');
4059 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.Payment_Function ' || to_char( lc_payer_rec.Payment_Function) );
4060 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.Party_Id ' || to_char( lc_payer_rec.Party_Id) );
4061 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_id ' || to_char(lc_payer_rec.org_id) );
4062 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_type ' || to_char( lc_payer_rec.org_type) );
4063 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Cust_Account_Id ' || to_char(lc_payer_rec.Cust_Account_Id) );
4064 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Account_Site_Id ' || to_char(lc_payer_rec.Account_Site_Id) );
4065 fnd_file.put_line(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_payment_trxn_extension_id ) );
4066 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.value: ' || to_char(lc_amount_rec.value) );
4067 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.currency_code: ' || lc_amount_rec.currency_code );
4068 fnd_file.put_line(FND_FILE.LOG, 'settlement_date: ' || lcr_receipt_attr.settlement_date);
4069 END IF;
4070
4071
4072 BEGIN
4073 IBY_FNDCPT_TRXN_PUB.Create_Return(
4074 p_api_version => 1.0,
4075 p_init_msg_list => FND_API.G_TRUE,
4076 x_return_status => lc_return_status,
4077 x_msg_count => lc_msg_count,
4078 x_msg_data => lc_msg_data,
4079 p_payer => lc_payer_rec,
4080 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
4081 p_trxn_entity_id => l_payment_trxn_extension_id,
4082 p_amount => lc_amount_rec,
4083 p_receipt_attribs => lcr_receipt_attr,
4084 x_response => lc_response_rec ); -- out response struct
4085
4086 x_msg_count := lc_msg_count;
4087 x_msg_data := lc_msg_data;
4088
4089 fnd_file.put_line(FND_FILE.LOG,'x_return_status :<' || lc_return_status || '>');
4090 fnd_file.put_line(FND_FILE.LOG,'x_msg_count :<' || lc_msg_count || '>');
4091
4092 FOR i IN 1..lc_msg_count LOOP
4093 fnd_file.put_line(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
4094 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
4095 END LOOP;
4096
4097 IF PG_DEBUG in ('Y', 'C') THEN
4098 fnd_file.put_line(FND_FILE.LOG, '-------------------------------------');
4099 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Code: ' || lc_response_rec.Result_Code);
4100 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Category: ' || lc_response_rec.Result_Category);
4101 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_message : ' || lc_response_rec.Result_message );
4102 END IF;
4103
4104 IF (lc_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4105
4106
4107 G_ERROR := 'Y' ;
4108
4109 fnd_file.put_line(FND_FILE.LOG,'create_cash_126');
4110 FND_MESSAGE.set_name('AR', 'AR_CC_AUTH_FAILED');
4111 FND_MSG_PUB.Add;
4112
4113 IF lc_response_rec.Result_Code is NOT NULL THEN
4114
4115 ---Raise the PAYMENT error code concatenated with the message
4116
4117 lc_iby_msg_data := substrb( lc_response_rec.Result_Code || ': '|| lc_response_rec.Result_Message , 1, 240);
4118
4119 fnd_file.put_line(FND_FILE.LOG, 'lc_iby_msg_data: ' || lc_iby_msg_data);
4120 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
4121 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',lc_iby_msg_data);
4122
4123 FND_MSG_PUB.Add;
4124
4125 END IF;
4126
4127 insert_exceptions(
4128 p_batch_id =>p_batch_id,
4129 p_request_id =>l_request_id,
4130 p_paying_customer_id =>l_payer_rec.cust_account_id,
4131 p_cash_receipt_id => rct_info.cash_receipt_id,
4132 p_exception_code => 'AR_CC_AUTH_FAILED',
4133 p_additional_message => lc_iby_msg_data
4134 );
4135
4136 UPDATE ar_cash_receipts
4137 SET cc_error_flag = 'Y',
4138 cc_error_code = lc_response_rec.Result_Code,
4139 cc_error_text = lc_response_rec.Result_Message,
4140 last_updated_by = l_last_updated_by,
4141 last_update_date = sysdate,
4142 last_update_login = l_last_update_login,
4143 request_id = l_request_id,
4144 program_application_id= l_program_application_id,
4145 program_id = l_program_id,
4146 program_update_date = sysdate
4147 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4148
4149 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
4150 p_count => x_msg_count,
4151 p_data => x_msg_data );
4152
4153 x_return_status := lc_return_status;
4154
4155 END IF;
4156 EXCEPTION
4157 WHEN OTHERS THEN
4158 G_ERROR := 'Y' ;
4159
4160 fnd_file.put_line(FND_FILE.LOG,'Exception : Create_Return () ');
4161 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
4162
4163 insert_exceptions(
4164 p_batch_id =>p_batch_id,
4165 p_request_id =>l_request_id,
4166 p_paying_customer_id =>l_payer_rec.cust_account_id,
4167 p_cash_receipt_id => rct_info.cash_receipt_id,
4168 p_exception_code => 'AR_CC_AUTH_FAILED',
4169 p_additional_message => l_iby_msg_data
4170 );
4171
4172 UPDATE ar_cash_receipts
4173 SET cc_error_flag = 'Y',
4174 last_updated_by = l_last_updated_by,
4175 last_update_date = sysdate,
4176 last_update_login = l_last_update_login,
4177 request_id = l_request_id,
4178 program_application_id= l_program_application_id,
4179 program_id = l_program_id,
4180 program_update_date = sysdate
4181 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4182
4183 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
4184 END; /* END of BEGIN */
4185 END IF;
4186
4187
4188 END LOOP ; /* for the rct_cur */
4189
4190
4191
4192 /* HERE WE WILL CALL THE BULK SETTLEMENT PROCESS START */
4193
4194 IF l_call_settlement = 'Y' THEN
4195
4196 IF PG_DEBUG in ( 'Y','C') THEN
4197
4198 fnd_file.put_line(FND_FILE.LOG, 'Calling bulk settlement');
4199
4200
4201 select count(*)
4202 into l_count1
4203 from AR_FUNDS_CAPTURE_ORDERS_V
4204 where CALL_APP_SERVICE_REQ_CODE = 'AR_'||p_batch_id;
4205
4206 fnd_file.put_line(FND_FILE.LOG, ' No of records in AR_FUNDS_CAPTURE_ORDERS_V ' || l_count1 );
4207 fnd_file.put_line(FND_FILE.LOG, 'p_calling_app_id ' || to_char(l_program_application_id) );
4208 fnd_file.put_line(FND_FILE.LOG, ' p_calling_app_request_code ' || 'AR_'||p_batch_id);
4209 fnd_file.put_line(FND_FILE.LOG, 'p_order_view_name ' || 'AR_FUNDS_CAPTURE_ORDERS_V' );
4210 END IF;
4211
4212 IBY_FNDCPT_TRXN_PUB.Create_Settlements(
4213 p_api_version => 1.0,
4214 p_init_msg_list => FND_API.G_TRUE,
4215 p_calling_app_id => l_program_application_id,
4216 p_calling_app_request_code => 'AR_'||p_batch_id||'_'||WORKER_NUMBER,
4217 p_order_view_name => 'AR_FUNDS_CAPTURE_ORDERS_V',
4218 x_return_status => ls_return_status,
4219 x_msg_count => ls_msg_count,
4220 x_msg_data => ls_msg_data,
4221 x_responses => ls_response_rec_tab );
4222
4223
4224 fnd_file.put_line(FND_FILE.LOG,'x_return_status :<' || ls_return_status || '>');
4225 fnd_file.put_line(FND_FILE.LOG,'x_msg_count :<' || ls_msg_count || '>');
4226
4227 FOR i IN 1..ls_msg_count LOOP
4228 fnd_file.put_line(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
4229 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
4230 END LOOP;
4231
4232 IF PG_DEBUG in ('Y', 'C') THEN
4233
4234 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
4235
4236 fnd_file.put_line(FND_FILE.LOG, '--------- START -----------------');
4237 fnd_file.put_line(FND_FILE.LOG, 'ls_response_rec.Trxn_Extension_Id : ' || ls_response_rec_tab(i).Trxn_extension_id);
4238 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_code : ' || ls_response_rec_tab(i).Result.Result_code);
4239 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Category : ' || ls_response_rec_tab(i).Result.Result_Category);
4240 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Message : ' || ls_response_rec_tab(i).Result.Result_Message);
4241 fnd_file.put_line(FND_FILE.LOG, '--------- END -----------------');
4242
4243 END LOOP;
4244
4245 END IF;
4246
4247
4248 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4249
4250 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
4251
4252 fnd_file.put_line(FND_FILE.LOG,'the value of ls_response_rec.Trxn_Extension_Id = ' || (ls_response_rec_tab(i).Trxn_Extension_Id ));
4253 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_code : ' || ls_response_rec_tab(i).Result.Result_code);
4254 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Category : ' || ls_response_rec_tab(i).Result.Result_Category);
4255 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Message : ' || ls_response_rec_tab(i).Result.Result_Message);
4256
4257 END LOOP;
4258
4259 END IF;
4260
4261
4262
4263 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
4264
4265
4266 IF ls_response_rec_tab(i).Result.Result_code in ( 'SETTLEMENT_SUCCESS','SETTLEMENT_PENDING') THEN
4267
4268 fnd_file.put_line(FND_FILE.LOG,'SETTLEMENT SUCCESS FOR Trxn_Extension_Id = '
4269 || (ls_response_rec_tab(i).Trxn_Extension_Id ));
4270
4271
4272 ELSE
4273
4274 G_ERROR := 'Y';
4275
4276 ls_iby_msg_data := null; /* initialize here */
4277
4278 FND_MESSAGE.set_name('AR', 'AR_CC_CAPTURE_FAILED');
4279 FND_MSG_PUB.Add;
4280 ---Raise the PAYMENT error code concatenated with the message
4281
4282 ls_iby_msg_data := substrb( ls_response_rec_tab(i).Result.Result_Code || ': '||
4283 ls_response_rec_tab(i).Result.Result_Message , 1, 240);
4284
4285 fnd_file.put_line(FND_FILE.LOG, 'ls_iby_msg_data: ' || ls_iby_msg_data);
4286
4287 Begin
4288 select cash_receipt_id,pay_from_customer,customer_site_use_id
4289 into l_cr_id,l_paying_customer_id,l_cust_site_id
4290 from ar_cash_receipts
4291 where selected_remittance_batch_id = p_batch_id and
4292 payment_trxn_extension_id = ls_response_rec_tab(i).Trxn_Extension_Id;
4293
4294
4295
4296 insert_exceptions(
4297 p_batch_id =>p_batch_id,
4298 p_request_id =>l_request_id,
4299 p_cash_receipt_id => l_cr_id,
4300 p_paying_customer_id =>l_paying_customer_id,
4301 p_paying_site_use_id => l_cust_site_id,
4302 p_exception_code => 'AR_CC_CAPTURE_FAILED',
4303 p_additional_message => ls_iby_msg_data
4304 );
4305
4306
4307 UPDATE ar_cash_receipts
4308 SET cc_error_flag = 'Y',
4309 cc_error_code = ls_response_rec_tab(i).Result.Result_Code,
4310 cc_error_text = ls_response_rec_tab(i).Result.Result_Message,
4311 last_updated_by = l_last_updated_by,
4312 last_update_date = sysdate,
4313 last_update_login = l_last_update_login,
4314 request_id = l_request_id,
4315 program_application_id= l_program_application_id,
4316 program_id = l_program_id,
4317 program_update_date = sysdate
4318 WHERE cash_receipt_id = l_cr_id;
4319 Exception
4320 when others then
4321 fnd_file.put_line(FND_FILE.LOG,'Exception : no data for ls_response_rec.Trxn_Extension_Id = ' || (ls_response_rec_tab(i).Trxn_Extension_Id ));
4322 End;
4323
4324
4325 END IF;
4326 END LOOP;
4327
4328 END IF; /* l_call_settlement is yes */
4329
4330
4331
4332
4333 /* END CALL TO THE BULK SETTLEMENT PROCESS */
4334
4335
4336 IF PG_DEBUG in ('Y', 'C') THEN
4337 fnd_file.put_line(FND_FILE.LOG,' end process_pay_receipt ');
4338 END IF;
4339
4340
4341 EXCEPTION
4342 WHEN others THEN
4343
4344 G_ERROR := 'Y';
4345 IF PG_DEBUG in ('Y', 'C') THEN
4346 fnd_file.put_line(FND_FILE.LOG,'Exception : process_pay_receipt err () ');
4347 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
4348 END IF;
4349
4350
4351 END process_pay_receipt;
4352 /*========================================================================+
4353 | PROCEDURE process_pay_receipt_parallel |
4354 | |
4355 | DESCRIPTION |
4356 | |
4357 | This procedure is used to select receipts to be remitted |
4358 | update and insert records into the necessary tables. |
4359 | PSEUDO CODE/LOGIC |
4360 | |
4361 | PARAMETERS |
4362 | |
4363 | |
4364 | KNOWN ISSUES |
4365 | |
4366 | NOTES |
4367 | |
4368 | |
4369 | MODIFICATION HISTORY |
4370 | Date Author Description of Changes |
4371 | 10-JUN-2008 AGHORAKA Created for Parallelization |
4372 *=========================================================================*/
4373
4374 PROCEDURE process_pay_receipt_parallel(
4375 p_batch_id IN NUMBER,
4376 p_called_from IN VARCHAR2,
4377 x_msg_count OUT NOCOPY NUMBER,
4378 x_msg_data OUT NOCOPY VARCHAR2,
4379 x_return_status OUT NOCOPY VARCHAR2
4380 ) IS
4381
4382 CURSOR rct_info_cur IS
4383 SELECT a.receipt_number,
4384 a.amount2 amount,
4385 a.cash_receipt_id,
4386 a.currency_code,
4387 a.PAYMENT_CHANNEL_CODE, /* NEW ADDED */
4388 a.creation_status, /* AR USE */
4389 a.org_id,
4390 a.party_id,
4391 a.pay_from_customer,
4392 a.customer_site_use_id,
4393 a.payment_trxn_extension_id,
4394 a.batch_id
4395 FROM ar_autorem_interim a
4396 WHERE a.batch_id = p_batch_id
4397 AND a.current_worker = WORKER_NUMBER;
4398
4399
4400 /*Bug 7666285*/
4401 CURSOR rct_date_cur (c_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE)
4402 IS
4403 SELECT receipt_date
4404 FROM ar_cash_receipts
4405 WHERE cash_receipt_id = c_cash_receipt_id;
4406
4407
4408
4409 -- rct_info rct_info_cur%ROWTYPE;
4410 l_cr_rec ar_cash_receipts_all%ROWTYPE;
4411 l_org_type HR_ALL_ORGANIZATION_UNITS.TYPE%TYPE;
4412 l_action VARCHAR2(80);
4413 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4414 l_msg_count NUMBER;
4415 l_msg_data VARCHAR2(2000);
4416
4417 l_payment_trxn_extension_id NUMBER;
4418 l_amount NUMBER;
4419 l_calling_app_req_code NUMBER;
4420 l_iby_msg_data VARCHAR2(2000);
4421 l_amount_rec IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
4422 l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
4423 l_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type; /* OUT RESPONSE STRUCTURE */
4424
4425 l_receipt_date ar_cash_receipts_all.receipt_date%TYPE; /*Bug7666285*/
4426
4427 /* DECLARE the variables required for the payment engine (AUTH) all the REC TYPES */
4428
4429 l_payee_rec IBY_FNDCPT_TRXN_PUB.PayeeContext_rec_type;
4430 l_auth_attribs_rec IBY_FNDCPT_TRXN_PUB.AuthAttribs_rec_type;
4431 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
4432 l_authresult_rec IBY_FNDCPT_TRXN_PUB.AuthResult_rec_type; /* OUT AUTH RESULT STRUCTURE */
4433 l_auth_flag VARCHAR2(1);
4434 l_auth_id NUMBER;
4435 l_vend_msg_data VARCHAR2(2000);
4436
4437 /* END DECLARE the variables required for the payment engine (AUTH) all the REC TYPES */
4438
4439
4440 /* declare variables for settlement */
4441
4442 ls_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4443 ls_msg_count NUMBER;
4444 ls_msg_data VARCHAR2(2000);
4445 ls_response_rec_tab IBY_FNDCPT_TRXN_PUB.SettlementResult_tbl_type;
4446 ls_iby_msg_data VARCHAR2(2000);
4447 l_cr_id ar_cash_receipts.cash_receipt_id%type;
4448 l_paying_customer_id ar_cash_receipts.pay_from_customer%type;
4449 l_cust_site_id ar_cash_receipts.customer_site_use_id%type;
4450 l_count1 NUMBER;
4451
4452 /* end declare varaibles for settlement */
4453
4454
4455 /* 7666285 - for passing settlement_date on returns */
4456 lcr_receipt_attr IBY_FNDCPT_TRXN_PUB.receiptattribs_rec_type;
4457
4458 lc_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
4459 lc_amount_rec IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
4460 lc_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4461 lc_msg_count NUMBER;
4462 lc_msg_data VARCHAR2(2000);
4463 lc_iby_msg_data VARCHAR2(2000);
4464 lc_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
4465
4466
4467
4468 l_request_id NUMBER;
4469 l_last_updated_by NUMBER;
4470 l_created_by NUMBER;
4471 l_last_update_login NUMBER;
4472 l_program_application_id NUMBER;
4473 l_program_id NUMBER;
4474 l_call_settlement VARCHAR2(1) := 'N';
4475
4476 BEGIN
4477
4478
4479 IF PG_DEBUG in ('Y', 'C') THEN
4480 fnd_file.put_line(FND_FILE.LOG, 'Entering payment processing parallel...');
4481 END IF;
4482
4483
4484 l_request_id := arp_standard.profile.request_id;
4485 l_last_updated_by := arp_standard.profile.last_update_login ;
4486 l_created_by := arp_standard.profile.user_id ;
4487 l_last_update_login := arp_standard.profile.last_update_login ;
4488 l_program_application_id := arp_standard.application_id ;
4489 l_program_id := arp_standard.profile.program_id;
4490
4491
4492
4493
4494 FOR rct_info in rct_info_cur LOOP
4495
4496 l_payment_trxn_extension_id := rct_info.payment_trxn_extension_id;
4497 l_amount := rct_info.amount;
4498
4499 /* CHECK for AUTH here and do it if necessary start */
4500
4501
4502 IF ((l_payment_trxn_extension_id is not null ) AND (l_amount >0) ) THEN
4503
4504
4505 l_call_settlement := 'Y'; /* set the flag for calling settlement */
4506
4507
4508 IF PG_DEBUG in ('Y', 'C') THEN
4509 fnd_file.put_line(FND_FILE.LOG, 'check and then call Auth');
4510 END IF;
4511
4512 -- Step 1: (always performed):
4513
4514 -- set up payee record:
4515
4516 l_payee_rec.org_id := rct_info.org_id; -- receipt's org_id
4517 l_payee_rec.org_type := 'OPERATING_UNIT' ; -- ( HR_ORGANIZATION_UNITS )
4518
4519
4520 -- set up payer (=customer) record:
4521
4522 l_payer_rec.Payment_Function := 'CUSTOMER_PAYMENT';
4523 l_payer_rec.Party_Id := rct_info.party_id; -- receipt customer party id mandatory
4524 l_payer_rec.org_id := rct_info.org_id ;
4525 l_payer_rec.org_type := 'OPERATING_UNIT';
4526 l_payer_rec.Cust_Account_Id :=rct_info.pay_from_customer; -- receipt customer account_id
4527 l_payer_rec.Account_Site_Id :=rct_info.customer_site_use_id; -- receipt customer site_id
4528
4529
4530 if rct_info.customer_site_use_id is NULL THEN
4531
4532 l_payer_rec.org_id := NULL;
4533 l_payer_rec.org_type := NULL;
4534
4535 end if;
4536
4537 -- set up auth_attribs record:
4538 l_auth_attribs_rec.RiskEval_Enable_Flag := 'N';
4539 -- set up trxn_attribs record:
4540 l_trxn_attribs_rec.Originating_Application_Id := arp_standard.application_id;
4541 l_trxn_attribs_rec.order_id := rct_info.receipt_number;
4542 l_trxn_attribs_rec.Trxn_Ref_Number1 := 'RECEIPT';
4543 l_trxn_attribs_rec.Trxn_Ref_Number2 := rct_info.cash_receipt_id;
4544
4545 -- set up amounts
4546 l_amount_rec.value := rct_info.amount;
4547 l_amount_rec.currency_code := rct_info.currency_code;
4548
4549 -- determine whether to AUTHORIZE
4550 IF PG_DEBUG in ('Y', 'C') THEN
4551 fnd_file.put_line(FND_FILE.LOG, 'Calling get auth for pmt_trxn_extn_id ');
4552 fnd_file.put_line(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_payment_trxn_extension_id) );
4553
4554 END IF;
4555
4556 BEGIN
4557 IBY_AR_UTILS.get_authorization_status(l_payment_trxn_extension_id,l_auth_flag);
4558
4559 /* SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
4560 into l_auth_flag
4561 FROM iby_trxn_summaries_all summ,
4562 iby_fndcpt_tx_operations op
4563 WHERE summ.transactionid = op.transactionid
4564 AND reqtype = 'ORAPMTREQ'
4565 AND status IN(0, 100)
4566 AND trxntypeid IN(2, 3, 20)
4567 AND op.trxn_extension_id = l_payment_trxn_extension_id
4568 AND summ.trxnmid =
4569 (SELECT MAX(trxnmid)
4570 FROM iby_trxn_summaries_all
4571 WHERE transactionid = summ.transactionid
4572 AND reqtype = 'ORAPMTREQ'
4573 AND status IN(0, 100)
4574 AND trxntypeid IN(2, 3, 20)); */
4575 EXCEPTION
4576 WHEN OTHERS THEN
4577 fnd_file.put_line(FND_FILE.LOG,'Exception in IBY_AR_UTILS.get_authorization_status');
4578 l_auth_flag := 'N';
4579 END;
4580 fnd_file.put_line(FND_FILE.LOG,'the value of auth_flag is = ' || l_auth_flag);
4581 /* Commenting out as per bug 6996205:
4582 IF l_auth_flag = 'Y' then
4583
4584 select AUTHORIZATION_ID
4585 into l_auth_id
4586 from IBY_TRXN_EXT_AUTHS_V
4587 where TRXN_EXTENSION_ID = l_payment_trxn_extension_id;
4588
4589 IF PG_DEBUG in ('Y', 'C') THEN
4590 fnd_file.put_line(FND_FILE.LOG, 'the value of auth_id is = ' || l_auth_id);
4591 END IF;
4592 END IF;
4593 */
4594
4595 IF nvl(l_auth_flag,'N') <> 'Y' then
4596 fnd_file.put_line(FND_FILE.LOG,'auth needs to called');
4597
4598 IF PG_DEBUG in ('Y', 'C') THEN
4599 fnd_file.put_line(FND_FILE.LOG, 'Calling get auth for pmt_trxn_extn_id ');
4600 fnd_file.put_line(FND_FILE.LOG, ' l_payee_rec.org_id ' || to_char( l_payee_rec.org_id) );
4601 fnd_file.put_line(FND_FILE.LOG, ' l_payee_rec.org_type ' || to_char( l_payee_rec.org_type) );
4602 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Payment_Function '|| to_char( l_payer_rec.Payment_Function));
4603 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.Party_Id ' || to_char( l_payer_rec.Party_Id) );
4604 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_id ' || to_char( l_payer_rec.org_id) );
4605 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_type ' || to_char( l_payer_rec.org_type) );
4606 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Cust_Account_Id ' || to_char(l_payer_rec.Cust_Account_Id) );
4607 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Account_Site_Id ' || to_char(l_payer_rec.Account_Site_Id) );
4608 fnd_file.put_line(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_payment_trxn_extension_id) );
4609 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.value: ' || to_char(l_amount_rec.value) );
4610 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.currency_code: ' || l_amount_rec.currency_code );
4611
4612 fnd_file.put_line(FND_FILE.LOG, 'Calling get_auth for pmt_trxn_extn_id ');
4613 END IF;
4614
4615 BEGIN
4616 IBY_FNDCPT_TRXN_PUB.Create_Authorization(
4617 p_api_version => 1.0,
4618 p_init_msg_list => FND_API.G_TRUE,
4619 x_return_status => l_return_status,
4620 x_msg_count => l_msg_count,
4621 x_msg_data => l_msg_data,
4622 p_payer => l_payer_rec,
4623 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
4624 p_payee => l_payee_rec,
4625 p_trxn_entity_id => l_payment_trxn_extension_id,
4626 p_auth_attribs => l_auth_attribs_rec,
4627 p_amount => l_amount_rec,
4628 x_auth_result => l_authresult_rec, -- out auth result struct
4629 x_response => l_response_rec ); -- out response struct
4630
4631
4632 x_msg_count := l_msg_count;
4633 x_msg_data := l_msg_data;
4634
4635 IF PG_DEBUG in ('Y', 'C') THEN
4636 fnd_file.put_line(FND_FILE.LOG,'x_return_status :<' || l_return_status || '>');
4637 fnd_file.put_line(FND_FILE.LOG,'x_msg_count :<' || l_msg_count || '>');
4638 END IF;
4639
4640 FOR i IN 1..l_msg_count LOOP
4641 fnd_file.put_line(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
4642 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
4643 END LOOP;
4644
4645 IF PG_DEBUG in ('Y', 'C') THEN
4646 fnd_file.put_line(FND_FILE.LOG, '-------------------------------------');
4647 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Code: ' || l_response_rec.Result_Code);
4648 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Category: '|| l_response_rec.Result_Category);
4649 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_message :'|| l_response_rec.Result_message );
4650 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.Auth_Id: ' || l_authresult_rec.Auth_Id);
4651 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.Auth_Date: ' || l_authresult_rec.Auth_Date);
4652 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.Auth_Code: ' || l_authresult_rec.Auth_Code);
4653 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.AVS_Code: ' || l_authresult_rec.AVS_Code);
4654 fnd_file.put_line(FND_FILE.LOG,'l_authresult_rec.Instr_SecCode_Check:'||l_authresult_rec.Instr_SecCode_Check);
4655 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.PaymentSys_Code: ' || l_authresult_rec.PaymentSys_Code);
4656 fnd_file.put_line(FND_FILE.LOG, 'l_authresult_rec.PaymentSys_Msg: ' || l_authresult_rec.PaymentSys_Msg);
4657
4658 END IF;
4659
4660 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4661
4662 IF PG_DEBUG in ('Y', 'C') THEN
4663 fnd_file.put_line(FND_FILE.LOG,'the value of auth_id is = ' || (l_authresult_rec.Auth_Id));
4664 END IF;
4665
4666 END IF;
4667
4668
4669 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4670 G_ERROR := 'Y' ;
4671
4672 FND_MESSAGE.set_name('AR', 'AR_CC_AUTH_FAILED');
4673 FND_MSG_PUB.Add;
4674
4675 IF l_response_rec.Result_Code is NOT NULL THEN
4676
4677 ---Raise the PAYMENT error code concatenated with the message
4678
4679 l_iby_msg_data := substrb( l_response_rec.Result_Code || ': '||
4680 l_response_rec.Result_Message , 1, 240);
4681
4682 fnd_file.put_line(FND_FILE.LOG, 'l_iby_msg_data: ' || l_iby_msg_data);
4683
4684 UPDATE ar_cash_receipts
4685 SET cc_error_flag = 'Y',
4686 cc_error_code = l_response_rec.Result_Code,
4687 cc_error_text = l_response_rec.Result_Message,
4688 last_updated_by = l_last_updated_by,
4689 last_update_date = sysdate,
4690 last_update_login = l_last_update_login,
4691 request_id = l_request_id,
4692 program_application_id= l_program_application_id,
4693 program_id = l_program_id,
4694 program_update_date = sysdate
4695 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4696
4697 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
4698
4699 END IF;
4700
4701 IF l_authresult_rec.PaymentSys_Code is not null THEN
4702
4703 ---Raise the VENDOR error code concatenated with the message
4704
4705 l_vend_msg_data := substrb(l_authresult_rec.PaymentSys_Code || ': '||
4706 l_authresult_rec.PaymentSys_Msg , 1, 240 );
4707
4708 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
4709 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_vend_msg_data);
4710
4711 UPDATE ar_cash_receipts
4712 SET cc_error_flag = 'Y',
4713 cc_error_code = l_authresult_rec.PaymentSys_Code,
4714 cc_error_text = l_authresult_rec.PaymentSys_Msg,
4715 last_updated_by = l_last_updated_by,
4716 last_update_date = sysdate,
4717 last_update_login = l_last_update_login,
4718 request_id = l_request_id,
4719 program_application_id= l_program_application_id,
4720 program_id = l_program_id,
4721 program_update_date = sysdate
4722 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4723
4724 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
4725
4726 END IF;
4727
4728 insert_exceptions(
4729 p_batch_id =>p_batch_id,
4730 p_request_id =>l_request_id,
4731 p_paying_customer_id =>l_payer_rec.cust_account_id,
4732 p_cash_receipt_id => rct_info.cash_receipt_id,
4733 p_exception_code => 'AR_CC_AUTH_FAILED',
4734 p_additional_message => substrb(l_iby_msg_data||l_vend_msg_data,1,240)
4735 );
4736
4737 x_return_status := l_return_status;
4738
4739 END IF; /* End the error handling CREATE */
4740 EXCEPTION
4741 WHEN OTHERS THEN
4742 G_ERROR := 'Y' ;
4743
4744 fnd_file.put_line(FND_FILE.LOG,'Exception : Create_Authorization () ');
4745 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
4746
4747 insert_exceptions(
4748 p_batch_id =>p_batch_id,
4749 p_request_id =>l_request_id,
4750 p_paying_customer_id =>l_payer_rec.cust_account_id,
4751 p_cash_receipt_id => rct_info.cash_receipt_id,
4752 p_exception_code => 'AR_CC_AUTH_FAILED',
4753 p_additional_message => l_iby_msg_data
4754 );
4755
4756 UPDATE ar_cash_receipts
4757 SET cc_error_flag = 'Y',
4758 last_updated_by = l_last_updated_by,
4759 last_update_date = sysdate,
4760 last_update_login = l_last_update_login,
4761 request_id = l_request_id,
4762 program_application_id= l_program_application_id,
4763 program_id = l_program_id,
4764 program_update_date = sysdate
4765 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4766
4767 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
4768 END; /* END of BEGIN */
4769
4770
4771 END IF; /* END if of auth flag <>'Y' */
4772
4773 END IF ; /* end if of pmt_etx is not null and amt >0 */
4774 /* CHECK for AUTH here and do it if necessary end */
4775
4776 IF ((l_payment_trxn_extension_id is not null ) AND (l_amount <0) ) THEN
4777
4778 /* HERE WE HAVE TO CALL RETURN */
4779
4780 -- set up payer (=customer) record:
4781
4782 lc_payer_rec.Payment_Function := 'CUSTOMER_PAYMENT';
4783 lc_payer_rec.Party_Id := rct_info.party_id; -- receipt customer party id mandatory
4784 lc_payer_rec.org_id := rct_info.org_id ;
4785 lc_payer_rec.org_type := 'OPERATING_UNIT';
4786 lc_payer_rec.Cust_Account_Id :=rct_info.pay_from_customer; -- receipt customer account_id
4787 lc_payer_rec.Account_Site_Id :=rct_info.customer_site_use_id; -- receipt customer site_id
4788
4789 -- set up amounts
4790
4791 lc_amount_rec.value := ABS(rct_info.amount);
4792 lc_amount_rec.currency_code := rct_info.currency_code;
4793
4794 /* 7666285 - Selecting settlement_date*/
4795 IF (rct_info.cash_receipt_id IS NOT NULL) THEN
4796 OPEN rct_date_cur(rct_info.cash_receipt_id);
4797 FETCH rct_date_cur INTO l_receipt_date;
4798 CLOSE rct_date_cur;
4799 IF PG_DEBUG in ('Y', 'C') THEN
4800 fnd_file.put_line(FND_FILE.LOG,'Receipt Date:'||l_receipt_date||' cash_receipt_id: '||rct_info.cash_receipt_id);
4801 END IF;
4802 ELSE
4803 IF PG_DEBUG in ('Y', 'C') THEN
4804 fnd_file.put_line(FND_FILE.LOG,'rct_info.cash_receipt_id is NULL');
4805 END IF;
4806 END IF;
4807
4808
4809
4810 /*7666285-Assigning the Receipt Date*/
4811 lcr_receipt_attr.settlement_date := l_receipt_date;
4812
4813
4814 if rct_info.customer_site_use_id is NULL THEN
4815
4816 lc_payer_rec.org_id := NULL;
4817 lc_payer_rec.org_type := NULL;
4818
4819 end if;
4820
4821 IF PG_DEBUG in ('Y', 'C') THEN
4822 fnd_file.put_line(FND_FILE.LOG, 'Calling return for pmt_trxn_extn_id ');
4823 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.Payment_Function ' || to_char( lc_payer_rec.Payment_Function) );
4824 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.Party_Id ' || to_char( lc_payer_rec.Party_Id) );
4825 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_id ' || to_char(lc_payer_rec.org_id) );
4826 fnd_file.put_line(FND_FILE.LOG, ' l_payer_rec.org_type ' || to_char( lc_payer_rec.org_type) );
4827 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Cust_Account_Id ' || to_char(lc_payer_rec.Cust_Account_Id) );
4828 fnd_file.put_line(FND_FILE.LOG, 'l_payer_rec.Account_Site_Id ' || to_char(lc_payer_rec.Account_Site_Id) );
4829 fnd_file.put_line(FND_FILE.LOG, 'l_trxn_entity_id ' || to_char(l_payment_trxn_extension_id ) );
4830 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.value: ' || to_char(lc_amount_rec.value) );
4831 fnd_file.put_line(FND_FILE.LOG, 'l_amount_rec.currency_code: ' || lc_amount_rec.currency_code );
4832 fnd_file.put_line(FND_FILE.LOG, 'settlement_date: ' || lcr_receipt_attr.settlement_date);
4833
4834 END IF;
4835
4836 BEGIN
4837 IBY_FNDCPT_TRXN_PUB.Create_Return(
4838 p_api_version => 1.0,
4839 p_init_msg_list => FND_API.G_TRUE,
4840 x_return_status => lc_return_status,
4841 x_msg_count => lc_msg_count,
4842 x_msg_data => lc_msg_data,
4843 p_payer => lc_payer_rec,
4844 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
4845 p_trxn_entity_id => l_payment_trxn_extension_id,
4846 p_amount => lc_amount_rec,
4847 p_receipt_attribs => lcr_receipt_attr,
4848 x_response => lc_response_rec ); -- out response struct
4849
4850 x_msg_count := lc_msg_count;
4851 x_msg_data := lc_msg_data;
4852
4853 IF PG_DEBUG in ('Y', 'C') THEN
4854 fnd_file.put_line(FND_FILE.LOG,'x_return_status :<' || lc_return_status || '>');
4855 fnd_file.put_line(FND_FILE.LOG,'x_msg_count :<' || lc_msg_count || '>');
4856
4857 FOR i IN 1..lc_msg_count LOOP
4858 fnd_file.put_line(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
4859 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
4860 END LOOP;
4861 END IF;
4862
4863 IF PG_DEBUG in ('Y', 'C') THEN
4864 fnd_file.put_line(FND_FILE.LOG, '-------------------------------------');
4865 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Code: ' || lc_response_rec.Result_Code);
4866 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_Category: ' || lc_response_rec.Result_Category);
4867 fnd_file.put_line(FND_FILE.LOG, 'l_response_rec.Result_message : ' || lc_response_rec.Result_message );
4868 END IF;
4869
4870 IF (lc_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4871
4872
4873 G_ERROR := 'Y' ;
4874
4875 IF PG_DEBUG in ('Y', 'C') THEN
4876 fnd_file.put_line(FND_FILE.LOG,'create_cash_126');
4877 END IF;
4878
4879 FND_MESSAGE.set_name('AR', 'AR_CC_AUTH_FAILED');
4880 FND_MSG_PUB.Add;
4881
4882 IF lc_response_rec.Result_Code is NOT NULL THEN
4883
4884 ---Raise the PAYMENT error code concatenated with the message
4885
4886 lc_iby_msg_data := substrb( lc_response_rec.Result_Code || ': '|| lc_response_rec.Result_Message , 1, 240);
4887
4888 IF PG_DEBUG in ('Y', 'C') THEN
4889 fnd_file.put_line(FND_FILE.LOG, 'lc_iby_msg_data: ' || lc_iby_msg_data);
4890 END IF;
4891
4892 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
4893 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',lc_iby_msg_data);
4894
4895 FND_MSG_PUB.Add;
4896
4897 END IF;
4898
4899 insert_exceptions(
4900 p_batch_id =>p_batch_id,
4901 p_request_id =>l_request_id,
4902 p_paying_customer_id =>l_payer_rec.cust_account_id,
4903 p_cash_receipt_id => rct_info.cash_receipt_id,
4904 p_exception_code => 'AR_CC_AUTH_FAILED',
4905 p_additional_message => lc_iby_msg_data
4906 );
4907
4908 UPDATE ar_cash_receipts
4909 SET cc_error_flag = 'Y',
4910 cc_error_code = lc_response_rec.Result_Code,
4911 cc_error_text = lc_response_rec.Result_Message,
4912 last_updated_by = l_last_updated_by,
4913 last_update_date = sysdate,
4914 last_update_login = l_last_update_login,
4915 request_id = l_request_id,
4916 program_application_id= l_program_application_id,
4917 program_id = l_program_id,
4918 program_update_date = sysdate
4919 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4920
4921 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
4922 p_count => x_msg_count,
4923 p_data => x_msg_data );
4924
4925 x_return_status := lc_return_status;
4926
4927 END IF;
4928 EXCEPTION
4929 WHEN OTHERS THEN
4930 G_ERROR := 'Y' ;
4931
4932 fnd_file.put_line(FND_FILE.LOG,'Exception : Create_Return () ');
4933 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
4934
4935 insert_exceptions(
4936 p_batch_id =>p_batch_id,
4937 p_request_id =>l_request_id,
4938 p_paying_customer_id =>l_payer_rec.cust_account_id,
4939 p_cash_receipt_id => rct_info.cash_receipt_id,
4940 p_exception_code => 'AR_CC_AUTH_FAILED',
4941 p_additional_message => l_iby_msg_data
4942 );
4943
4944 UPDATE ar_cash_receipts
4945 SET cc_error_flag = 'Y',
4946 last_updated_by = l_last_updated_by,
4947 last_update_date = sysdate,
4948 last_update_login = l_last_update_login,
4949 request_id = l_request_id,
4950 program_application_id= l_program_application_id,
4951 program_id = l_program_id,
4952 program_update_date = sysdate
4953 WHERE cash_receipt_id = rct_info.cash_receipt_id;
4954
4955 fnd_file.put_line(FND_FILE.LOG,'UPDATE CR with cc_err_flag ' || SQL%ROWCOUNT );
4956 END; /* END of BEGIN */
4957 END IF;
4958
4959
4960 END LOOP ; /* for the rct_cur */
4961
4962
4963
4964 /* HERE WE WILL CALL THE BULK SETTLEMENT PROCESS START */
4965
4966 IF l_call_settlement = 'Y' THEN
4967
4968 IF PG_DEBUG in ( 'Y','C') THEN
4969
4970 IF PG_DEBUG in ('Y', 'C') THEN
4971 fnd_file.put_line(FND_FILE.LOG, 'Calling bulk settlement');
4972 END IF;
4973
4974
4975 select count(*)
4976 into l_count1
4977 from AR_FUNDS_CAPTURE_ORDERS_V
4978 where CALL_APP_SERVICE_REQ_CODE = 'AR_'||p_batch_id;
4979
4980 IF PG_DEBUG in ('Y', 'C') THEN
4981 fnd_file.put_line(FND_FILE.LOG, ' No of records in AR_FUNDS_CAPTURE_ORDERS_V ' || l_count1 );
4982 fnd_file.put_line(FND_FILE.LOG, 'p_calling_app_id ' || to_char(l_program_application_id) );
4983 fnd_file.put_line(FND_FILE.LOG, ' p_calling_app_request_code ' || 'AR_'||p_batch_id);
4984 fnd_file.put_line(FND_FILE.LOG, 'p_order_view_name ' || 'AR_FUNDS_CAPTURE_ORDERS_V' );
4985 END IF;
4986 END IF;
4987
4988 IBY_FNDCPT_TRXN_PUB.Create_Settlements(
4989 p_api_version => 1.0,
4990 p_init_msg_list => FND_API.G_TRUE,
4991 p_calling_app_id => l_program_application_id,
4992 p_calling_app_request_code => 'AR_'||p_batch_id||'_'||WORKER_NUMBER,
4993 p_order_view_name => 'AR_FUNDS_CAPTURE_ORDERS_V',
4994 x_return_status => ls_return_status,
4995 x_msg_count => ls_msg_count,
4996 x_msg_data => ls_msg_data,
4997 x_responses => ls_response_rec_tab );
4998
4999
5000 IF PG_DEBUG in ('Y', 'C') THEN
5001 fnd_file.put_line(FND_FILE.LOG,'x_return_status :<' || ls_return_status || '>');
5002 fnd_file.put_line(FND_FILE.LOG,'x_msg_count :<' || ls_msg_count || '>');
5003
5004 FOR i IN 1..ls_msg_count LOOP
5005 fnd_file.put_line(FND_FILE.LOG,'x_msg #' || TO_CHAR(i) || ' = <' ||
5006 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
5007 END LOOP;
5008 END IF;
5009
5010 IF PG_DEBUG in ('Y', 'C') THEN
5011
5012 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
5013
5014 fnd_file.put_line(FND_FILE.LOG, '--------- START -----------------');
5015 fnd_file.put_line(FND_FILE.LOG, 'ls_response_rec.Trxn_Extension_Id : ' || ls_response_rec_tab(i).Trxn_extension_id);
5016 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_code : ' || ls_response_rec_tab(i).Result.Result_code);
5017 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Category : ' || ls_response_rec_tab(i).Result.Result_Category);
5018 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Message : ' || ls_response_rec_tab(i).Result.Result_Message);
5019 fnd_file.put_line(FND_FILE.LOG, '--------- END -----------------');
5020
5021 END LOOP;
5022
5023 END IF;
5024
5025
5026 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5027
5028 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
5029 IF PG_DEBUG in ('Y', 'C') THEN
5030 fnd_file.put_line(FND_FILE.LOG,'the value of ls_response_rec.Trxn_Extension_Id = ' || (ls_response_rec_tab(i).Trxn_Extension_Id ));
5031 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_code : ' || ls_response_rec_tab(i).Result.Result_code);
5032 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Category : ' || ls_response_rec_tab(i).Result.Result_Category);
5033 fnd_file.put_line (FND_FILE.LOG, 'ls_response_rec.Result.Result_Message : ' || ls_response_rec_tab(i).Result.Result_Message);
5034 END IF;
5035 END LOOP;
5036
5037 END IF;
5038
5039
5040
5041 FOR i IN ls_response_rec_tab.FIRST..ls_response_rec_tab.LAST LOOP
5042
5043
5044 IF ls_response_rec_tab(i).Result.Result_code in ( 'SETTLEMENT_SUCCESS','SETTLEMENT_PENDING') THEN
5045 IF PG_DEBUG in ('Y', 'C') THEN
5046 fnd_file.put_line(FND_FILE.LOG,'SETTLEMENT SUCCESS FOR Trxn_Extension_Id = '
5047 || (ls_response_rec_tab(i).Trxn_Extension_Id ));
5048 END IF;
5049
5050 ELSE
5051
5052 G_ERROR := 'Y';
5053
5054 ls_iby_msg_data := null; /* initialize here */
5055
5056 FND_MESSAGE.set_name('AR', 'AR_CC_CAPTURE_FAILED');
5057 FND_MSG_PUB.Add;
5058 ---Raise the PAYMENT error code concatenated with the message
5059
5060 ls_iby_msg_data := substrb( ls_response_rec_tab(i).Result.Result_Code || ': '||
5061 ls_response_rec_tab(i).Result.Result_Message , 1, 240);
5062
5063 IF PG_DEBUG in ('Y', 'C') THEN
5064 fnd_file.put_line(FND_FILE.LOG, 'ls_iby_msg_data: ' || ls_iby_msg_data);
5065 END IF;
5066
5067 Begin
5068 select cash_receipt_id,pay_from_customer,customer_site_use_id
5069 into l_cr_id,l_paying_customer_id,l_cust_site_id
5070 from ar_cash_receipts
5071 where selected_remittance_batch_id = p_batch_id and
5072 payment_trxn_extension_id = ls_response_rec_tab(i).Trxn_Extension_Id;
5073
5074
5075
5076 insert_exceptions(
5077 p_batch_id =>p_batch_id,
5078 p_request_id =>l_request_id,
5079 p_cash_receipt_id => l_cr_id,
5080 p_paying_customer_id =>l_paying_customer_id,
5081 p_paying_site_use_id => l_cust_site_id,
5082 p_exception_code => 'AR_CC_CAPTURE_FAILED',
5083 p_additional_message => ls_iby_msg_data
5084 );
5085
5086
5087 UPDATE ar_cash_receipts
5088 SET cc_error_flag = 'Y',
5089 cc_error_code = ls_response_rec_tab(i).Result.Result_Code,
5090 cc_error_text = ls_response_rec_tab(i).Result.Result_Message,
5091 last_updated_by = l_last_updated_by,
5092 last_update_date = sysdate,
5093 last_update_login = l_last_update_login,
5094 request_id = l_request_id,
5095 program_application_id= l_program_application_id,
5096 program_id = l_program_id,
5097 program_update_date = sysdate
5098 WHERE cash_receipt_id = l_cr_id;
5099 Exception
5100 when others then
5101 fnd_file.put_line(FND_FILE.LOG,'Exception : no data for ls_response_rec.Trxn_Extension_Id = ' || (ls_response_rec_tab(i).Trxn_Extension_Id ));
5102 End;
5103
5104
5105 END IF;
5106 END LOOP;
5107
5108 END IF; /* l_call_settlement is yes */
5109
5110
5111
5112
5113 /* END CALL TO THE BULK SETTLEMENT PROCESS */
5114
5115
5116 IF PG_DEBUG in ('Y', 'C') THEN
5117 fnd_file.put_line(FND_FILE.LOG,' end process_pay_receipt ');
5118 END IF;
5119
5120
5121 EXCEPTION
5122 WHEN others THEN
5123
5124 G_ERROR := 'Y';
5125 IF PG_DEBUG in ('Y', 'C') THEN
5126 fnd_file.put_line(FND_FILE.LOG,'Exception : process_pay_receipt err () ');
5127 fnd_file.put_line(FND_FILE.LOG,'error code() '|| to_char(SQLCODE));
5128 END IF;
5129
5130
5131 END process_pay_receipt_parallel;
5132
5133
5134
5135 /*========================================================================+
5136 | PROCEDURE insert_exceptions |
5137 | |
5138 | DESCRIPTION |
5139 | |
5140 | This procedure is used to insert the exception record when |
5141 | |
5142 | PSEUDO CODE/LOGIC |
5143 | |
5144 | PARAMETERS |
5145 | |
5146 | |
5147 | KNOWN ISSUES |
5148 | |
5149 | NOTES |
5150 | |
5151 | |
5152 | MODIFICATION HISTORY |
5153 | Date Author Description of Changes |
5154 | 16-JUL-2005 bichatte Created |
5155 *=========================================================================*/
5156 PROCEDURE insert_exceptions(
5157 p_batch_id IN ar_batches.batch_id%TYPE DEFAULT NULL,
5158 p_request_id IN ar_cash_receipts.request_id%TYPE DEFAULT NULL,
5159 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
5160 p_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
5161 p_paying_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
5162 p_paying_site_use_id IN ar_cash_receipts.customer_site_use_id%TYPE DEFAULT NULL,
5163 p_due_date IN ar_payment_schedules.due_date%TYPE DEFAULT NULL,
5164 p_cust_min_rec_amount IN NUMBER DEFAULT NULL,
5165 p_bank_min_rec_amount IN NUMBER DEFAULT NULL,
5166 p_exception_code IN VARCHAR2,
5167 p_additional_message IN VARCHAR2
5168 ) IS
5169
5170
5171
5172 l_request_id NUMBER;
5173 l_last_updated_by NUMBER;
5174 l_created_by NUMBER;
5175 l_last_update_login NUMBER;
5176 l_program_application_id NUMBER;
5177 l_program_id NUMBER;
5178
5179 l_reqid NUMBER;
5180 BEGIN
5181
5182 IF PG_DEBUG in ('Y','C') THEN
5183
5184 fnd_file.put_line(FND_FILE.LOG, 'enter insert exceptions');
5185 fnd_file.put_line(FND_FILE.LOG, 'value of p_batch_id' || p_batch_id);
5186 fnd_file.put_line(FND_FILE.LOG, 'value of p_request_id' || p_request_id);
5187 fnd_file.put_line(FND_FILE.LOG, 'value of p_cash_receipt_id' || p_cash_receipt_id);
5188 fnd_file.put_line(FND_FILE.LOG, 'value of p_payment_schedule_id' || p_payment_schedule_id);
5189 fnd_file.put_line(FND_FILE.LOG, 'value of p_paying_customer_id' || p_paying_customer_id);
5190 fnd_file.put_line(FND_FILE.LOG, 'value of p_paying_site_use_id' || p_paying_site_use_id);
5191 fnd_file.put_line(FND_FILE.LOG, 'value of p_due_date' || p_due_date);
5192 fnd_file.put_line(FND_FILE.LOG, 'value of p_cust_min_rec_amount' || p_cust_min_rec_amount);
5193 fnd_file.put_line(FND_FILE.LOG, 'value of p_bank_min_rec_amount' || p_bank_min_rec_amount);
5194 fnd_file.put_line(FND_FILE.LOG, 'value of p_exception_code' ||p_exception_code);
5195 fnd_file.put_line(FND_FILE.LOG, 'value of p_additional_message' ||p_additional_message);
5196
5197 END IF;
5198
5199
5200
5201
5202 l_request_id := arp_standard.profile.request_id;
5203 l_last_updated_by := arp_standard.profile.last_update_login ;
5204 l_created_by := arp_standard.profile.user_id ;
5205 l_last_update_login := arp_standard.profile.last_update_login ;
5206 l_program_application_id := arp_standard.application_id ;
5207 l_program_id := arp_standard.profile.program_id;
5208
5209 IF PG_DEBUG in ('Y','C') THEN
5210
5211 fnd_file.put_line(FND_FILE.LOG, 'value of l_request_id ' || l_request_id );
5212 fnd_file.put_line(FND_FILE.LOG, 'value of l_last_updated_by ' || l_last_updated_by );
5213 fnd_file.put_line(FND_FILE.LOG, 'value of l_created_by ' || l_created_by );
5214 fnd_file.put_line(FND_FILE.LOG, 'value of l_last_update_login ' || l_last_update_login );
5215 fnd_file.put_line(FND_FILE.LOG, 'value of l_program_application_id '|| to_char(l_program_application_id) );
5216 fnd_file.put_line(FND_FILE.LOG, 'value of l_program_id ' || to_char(l_program_id) );
5217
5218 END IF;
5219
5220
5221 INSERT
5222 INTO ar_autorec_exceptions
5223 (batch_id,
5224 request_id,
5225 cash_receipt_id,
5226 payment_schedule_id,
5227 paying_customer_id,
5228 paying_site_use_id,
5229 due_date,
5230 cust_min_rec_amount,
5231 bank_min_rec_amount,
5232 exception_code,
5233 additional_message,
5234 last_update_date,
5235 last_updated_by,
5236 creation_date,
5237 created_by,
5238 last_update_login,
5239 program_application_id,
5240 program_id,
5241 program_update_date)
5242 SELECT
5243 p_batch_id,
5244 l_request_id,
5245 p_cash_receipt_id,
5246 p_payment_schedule_id,
5247 p_paying_customer_id,
5248 p_paying_site_use_id,
5249 p_due_date,
5250 p_cust_min_rec_amount,
5251 p_bank_min_rec_amount,
5252 p_exception_code,
5253 p_additional_message,
5254 sysdate,
5255 l_last_updated_by,
5256 sysdate,
5257 l_created_by,
5258 l_last_update_login,
5259 l_program_application_id,
5260 l_program_id,
5261 sysdate FROM DUAL;
5262
5263 IF PG_DEBUG in ('Y','C') THEN
5264 fnd_file.put_line(FND_FILE.LOG,'the rows in exceptions = ' || SQL%ROWCOUNT );
5265 END IF;
5266
5267
5268
5269 EXCEPTION
5270 WHEN OTHERS THEN
5271
5272 IF PG_DEBUG in ('Y','C') THEN
5273 fnd_file.put_line(FND_FILE.LOG,'ERROR IN INSERT_AUTOREC_EXCEPTIONS' );
5274 END IF;
5275
5276
5277 END insert_exceptions;
5278
5279
5280
5281
5282
5283 /*========================================================================+
5284 | PUBLIC PROCEDURE SUBMIT_REPORT |
5285 | |
5286 | DESCRIPTION |
5287 | |
5288 | This procedure is used to get the parameters from the Conc program |
5289 | and convert them to the type reqd for processing. |
5290 | |
5291 | PSEUDO CODE/LOGIC |
5292 | |
5293 | PARAMETERS |
5294 | |
5295 | |
5296 | KNOWN ISSUES |
5297 | |
5298 | NOTES |
5299 | |
5300 | |
5301 | MODIFICATION HISTORY |
5302 | Date Author Description of Changes |
5303 | 16-JUL-2005 bichatte Created |
5304 *=========================================================================*/
5305
5306 PROCEDURE SUBMIT_REPORT (
5307 p_batch_id ar_batches.batch_id%TYPE,
5308 p_request_id ar_cash_receipts.request_id%TYPE
5309 ) IS
5310
5311 l_reqid NUMBER(15);
5312 l_org_id NUMBEr;
5313
5314 BEGIN
5315 IF PG_DEBUG in ('Y', 'C') THEN
5316 fnd_file.put_line(FND_FILE.LOG,'Submitting the report..');
5317 END IF;
5318
5319 --Start of Bug 5519913
5320
5321 --l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
5322 l_org_id := mo_global.get_current_org_id;
5323
5324 if l_org_id is null then
5325
5326 BEGIN
5327 select org_id into l_org_id
5328 from ar_batches_all
5329 where batch_id = p_batch_id;
5330 EXCEPTION
5331 when others then
5332 arp_util.debug('ar_autorem_api.submit Report ...OTHERS');
5333 l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
5334 end;
5335
5336 end if;
5337 -- end of Bug 5519913
5338
5339 fnd_request.set_org_id(l_org_id);
5340
5341 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
5342 application=>'AR',
5343 program=>'ARZCARPO',
5344 sub_request=>FALSE,
5345 argument1=>'P_PROCESS_TYPE=REMIT',
5346 argument2=>'P_BATCH_ID='|| p_batch_id,
5347 argument3=>'P_CREATE_FLAG='||pg_create_flag,
5348 argument4=>'P_APPROVE_FLAG='||pg_approve_flag,
5349 argument5=>'P_FORMAT_FLAG='||pg_format_flag,
5350 argument6=>'P_REQUEST_ID_MAIN=' || p_request_id
5351 ) ;
5352
5353
5354 IF PG_DEBUG in ('Y', 'C') THEN
5355 fnd_file.put_line(FND_FILE.LOG,'Request Id :' || l_reqid);
5356 END IF;
5357
5358
5359 commit; -- This is there to commit the conc request.
5360
5361 EXCEPTION
5362 WHEN OTHERS THEN
5363 IF PG_DEBUG in ('Y', 'C') THEN
5364 fnd_file.put_line(FND_FILE.LOG,'Submitting the report.iN ERROR.');
5365 END IF;
5366
5367 END SUBMIT_REPORT;
5368
5369
5370 /* START SUBMIT_FORMAT */
5371 PROCEDURE SUBMIT_FORMAT ( p_batch_id ar_batches.batch_id%TYPE
5372 ) IS
5373
5374 l_org_id NUMBER;
5375 l_reqid NUMBER;
5376
5377
5378 BEGIN
5379 IF PG_DEBUG in ('Y', 'C') THEN
5380 fnd_file.put_line(FND_FILE.LOG,'Submitting the report..');
5381 END IF;
5382
5383 --Start of Bug 5519913
5384 --l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
5385 l_org_id := mo_global.get_current_org_id;
5386
5387 if l_org_id is null then
5388 BEGIN
5389
5390 select org_id into l_org_id
5391 from ar_batches_all
5392 where batch_id = p_batch_id;
5393 EXCEPTION
5394 when others then
5395 arp_util.debug('ar_autorem_api.submit Report ...OTHERS');
5396 l_org_id := TO_NUMBER(FND_PROFILE.value('ORG_ID'));
5397 end;
5398 end if;
5399 --End of Bug fix 5519913
5400
5401 fnd_request.set_org_id(l_org_id);
5402
5403 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
5404 application=>'AR',
5405 program=>'ARXAPFRM',
5406 sub_request=>FALSE,
5407 argument1=>'P_BATCH_ID='|| p_batch_id
5408 ) ;
5409
5410
5411 IF PG_DEBUG in ('Y', 'C') THEN
5412 fnd_file.put_line(FND_FILE.LOG,'Request Id :' || l_reqid);
5413 END IF;
5414
5415
5416 commit; -- This is there to commit the conc request.
5417
5418 EXCEPTION
5419 WHEN OTHERS THEN
5420 IF PG_DEBUG in ('Y', 'C') THEN
5421 fnd_file.put_line(FND_FILE.LOG,'Submitting the report.iN ERROR.');
5422 END IF;
5423
5424 END SUBMIT_FORMAT;
5425
5426 /* END SUBMIT_FORMAT */
5427
5428
5429 /* START CONTROL_CHECK */
5430 PROCEDURE CONTROL_CHECK ( p_batch_id ar_batches.batch_id%TYPE
5431 ) IS
5432 l_request_id NUMBER;
5433 l_last_updated_by NUMBER;
5434 l_created_by NUMBER;
5435 l_last_update_login NUMBER;
5436 l_program_application_id NUMBER;
5437 l_program_id NUMBER;
5438 BEGIN
5439 IF PG_DEBUG in ('Y', 'C') THEN
5440 fnd_file.put_line(FND_FILE.LOG,'control_check()+');
5441 END IF;
5442
5443 l_request_id := arp_standard.profile.request_id;
5444 l_last_updated_by := arp_standard.profile.last_update_login ;
5445 l_created_by := arp_standard.profile.user_id ;
5446 l_last_update_login := arp_standard.profile.last_update_login ;
5447 l_program_application_id := arp_standard.application_id ;
5448 l_program_id := arp_standard.profile.program_id;
5449
5450 UPDATE ar_cash_receipts
5451 SET cc_error_flag = 'Y',
5452 last_updated_by = l_last_updated_by,
5453 last_update_date = sysdate,
5454 last_update_login = l_last_update_login,
5455 request_id = l_request_id,
5456 program_application_id = l_program_application_id,
5457 program_id = l_program_id,
5458 program_update_date = sysdate
5459 WHERE cash_receipt_id in (
5460 SELECT cr.cash_receipt_id
5461 FROM ar_cash_receipts cr,
5462 ar_cash_receipt_history crh,
5463 iby_trxn_extensions_v trxn_ext
5464 WHERE crh.request_id = l_request_id
5465 AND crh.status = 'REMITTED'
5466 AND crh.current_record_flag = 'Y'
5467 AND crh.cash_receipt_id = cr.cash_receipt_id
5468 AND cr.type = 'CASH'
5469 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
5470 AND trxn_ext.settled_flag = 'N'
5471 AND NOT EXISTS (SELECT 'x'
5472 FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
5473 WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
5474 AND op.transactionid = summ.transactionid
5475 AND summ.reqtype in ('ORAPMTCAPTURE','ORAPMTBATCHREQ')
5476 AND summ.status IN(0, 11, 100))
5477 ) AND selected_remittance_batch_id = p_batch_id;
5478
5479 fnd_file.put_line(FND_FILE.LOG,'Bad CASH receipt rows detected : '||sql%rowcount);
5480
5481 if sql%rowcount > 0 then
5482 G_ERROR := 'Y' ;
5483 INSERT INTO ar_autorec_exceptions
5484 (batch_id,
5485 request_id,
5486 cash_receipt_id,
5487 paying_customer_id,
5488 exception_code,
5489 additional_message,
5490 last_update_date,
5491 last_updated_by,
5492 creation_date,
5493 created_by,
5494 last_update_login,
5495 program_application_id,
5496 program_id,
5497 program_update_date)
5498 SELECT
5499 p_batch_id,
5500 l_request_id,
5501 cr.cash_receipt_id,
5502 cr.pay_from_customer,
5503 'AR_CC_AUTH_FAILED',
5504 'Failure in settlements',
5505 sysdate,
5506 l_last_updated_by,
5507 sysdate,
5508 l_created_by,
5509 l_last_update_login,
5510 l_program_application_id,
5511 l_program_id,
5512 sysdate
5513 FROM ar_cash_receipts cr,
5514 ar_cash_receipt_history crh,
5515 iby_trxn_extensions_v trxn_ext
5516 WHERE crh.request_id = l_request_id
5517 AND crh.status = 'REMITTED'
5518 AND crh.current_record_flag = 'Y'
5519 AND crh.cash_receipt_id = cr.cash_receipt_id
5520 AND cr.type = 'CASH'
5521 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
5522 AND trxn_ext.settled_flag = 'N'
5523 AND NOT EXISTS (SELECT 'x'
5524 FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
5525 WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
5526 AND op.transactionid = summ.transactionid
5527 AND summ.reqtype in ('ORAPMTCAPTURE','ORAPMTBATCHREQ')
5528 AND summ.status IN(0, 11, 100));
5529 end if;
5530
5531 UPDATE ar_cash_receipts
5532 SET cc_error_flag = 'Y',
5533 last_updated_by = l_last_updated_by,
5534 last_update_date = sysdate,
5535 last_update_login = l_last_update_login,
5536 request_id = l_request_id,
5537 program_application_id = l_program_application_id,
5538 program_id = l_program_id,
5539 program_update_date = sysdate
5540 WHERE cash_receipt_id in (
5541 SELECT cr.cash_receipt_id
5542 FROM ar_cash_receipts cr,
5543 ar_cash_receipt_history crh,
5544 iby_trxn_extensions_v trxn_ext
5545 WHERE crh.request_id = l_request_id
5546 AND crh.status = 'REMITTED'
5547 AND crh.current_record_flag = 'Y'
5548 AND crh.cash_receipt_id = cr.cash_receipt_id
5549 AND cr.type = 'MISC'
5550 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
5551 AND trxn_ext.returned_flag = 'N'
5552 AND NOT EXISTS (SELECT 'x'
5553 FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
5554 WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
5555 AND op.transactionid = summ.transactionid
5556 AND summ.reqtype in ('ORAPMTRETURN','ORAPMTCREDIT')
5557 AND status IN(0, 11, 100))
5558 ) AND selected_remittance_batch_id = p_batch_id;
5559
5560 fnd_file.put_line(FND_FILE.LOG,'Bad MISC receipt rows detected : '||sql%rowcount);
5561
5562 if sql%rowcount > 0 then
5563 G_ERROR := 'Y' ;
5564 INSERT INTO ar_autorec_exceptions
5565 (batch_id,
5566 request_id,
5567 cash_receipt_id,
5568 paying_customer_id,
5569 exception_code,
5570 additional_message,
5571 last_update_date,
5572 last_updated_by,
5573 creation_date,
5574 created_by,
5575 last_update_login,
5576 program_application_id,
5577 program_id,
5578 program_update_date)
5579 SELECT
5580 p_batch_id,
5581 l_request_id,
5582 cr.cash_receipt_id,
5583 cr.pay_from_customer,
5584 'AR_CC_AUTH_FAILED',
5585 'Failure in settlements',
5586 sysdate,
5587 l_last_updated_by,
5588 sysdate,
5589 l_created_by,
5590 l_last_update_login,
5591 l_program_application_id,
5592 l_program_id,
5593 sysdate
5594 FROM ar_cash_receipts cr,
5595 ar_cash_receipt_history crh,
5596 iby_trxn_extensions_v trxn_ext
5597 WHERE crh.request_id = l_request_id
5598 AND crh.status = 'REMITTED'
5599 AND crh.current_record_flag = 'Y'
5600 AND crh.cash_receipt_id = cr.cash_receipt_id
5601 AND cr.type = 'MISC'
5602 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
5603 AND trxn_ext.returned_flag = 'N'
5604 AND NOT EXISTS (SELECT 'x'
5605 FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
5606 WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
5607 AND op.transactionid = summ.transactionid
5608 AND summ.reqtype in ('ORAPMTRETURN','ORAPMTCREDIT')
5609 AND status IN(0, 11, 100));
5610 end if;
5611
5612 IF PG_DEBUG in ('Y', 'C') THEN
5613 fnd_file.put_line(FND_FILE.LOG,'control_check()-');
5614 END IF;
5615
5616 EXCEPTION
5617 WHEN OTHERS THEN
5618 IF PG_DEBUG in ('Y', 'C') THEN
5619 fnd_file.put_line(FND_FILE.LOG,'Error in Control check routine.');
5620 END IF;
5621
5622 END CONTROL_CHECK;
5623
5624 /* END CONTROL_CHECK */
5625
5626 PROCEDURE rec_reset ( p_request_id NUMBER
5627 ) IS
5628
5629
5630 BEGIN
5631 IF PG_DEBUG in ('Y', 'C') THEN
5632 fnd_file.put_line(FND_FILE.LOG,'inside rec reset. value of G_ERROR = '|| G_ERROR );
5633 END IF;
5634
5635 IF G_ERROR = 'Y' THEN
5636
5637 UPDATE ar_cash_receipts
5638 SET cc_error_flag = null
5639 WHERE cash_receipt_id in (
5640 SELECT cr.cash_receipt_id
5641 FROM ar_cash_receipts cr,
5642 ar_cash_receipt_history crh,
5643 iby_trxn_extensions_v trxn_ext
5644 WHERE cr.request_id = p_request_id
5645 AND cr.cc_error_flag = 'Y'
5646 AND cr.type = 'CASH'
5647 AND crh.cash_receipt_id = cr.cash_receipt_id
5648 AND crh.status = 'REMITTED'
5649 AND crh.current_record_flag = 'Y'
5650 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
5651 AND trxn_ext.settled_flag = 'Y'
5652 AND EXISTS (SELECT 'x'
5653 FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
5654 WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
5655 AND op.transactionid = summ.transactionid
5656 AND summ.reqtype in ('ORAPMTCAPTURE','ORAPMTBATCHREQ')
5657 AND summ.status IN(0, 11, 100))
5658 ) AND request_id = p_request_id
5659 AND cc_error_flag = 'Y';
5660
5661 fnd_file.put_line(FND_FILE.LOG,'CASH receipt rows updated : '||sql%rowcount);
5662
5663
5664 UPDATE ar_cash_receipts
5665 SET cc_error_flag = null
5666 WHERE cash_receipt_id in (
5667 SELECT cr.cash_receipt_id
5668 FROM ar_cash_receipts cr,
5669 ar_cash_receipt_history crh,
5670 iby_trxn_extensions_v trxn_ext
5671 WHERE cr.request_id = p_request_id
5672 AND cr.cc_error_flag = 'Y'
5673 AND cr.type = 'MISC'
5674 AND crh.cash_receipt_id = cr.cash_receipt_id
5675 AND crh.status = 'REMITTED'
5676 AND crh.current_record_flag = 'Y'
5677 AND cr.payment_trxn_extension_id = trxn_ext.trxn_extension_id
5678 AND trxn_ext.returned_flag = 'Y'
5679 AND EXISTS (SELECT 'x'
5680 FROM iby_fndcpt_tx_operations op, iby_trxn_summaries_all summ
5681 WHERE cr.payment_trxn_extension_id = op.trxn_extension_id
5682 AND op.transactionid = summ.transactionid
5683 AND summ.reqtype in ('ORAPMTRETURN','ORAPMTCREDIT')
5684 AND status IN(0, 11, 100))
5685 ) AND request_id = p_request_id
5686 AND cc_error_flag = 'Y';
5687
5688 fnd_file.put_line(FND_FILE.LOG,'MISC receipt rows updated : '||sql%rowcount);
5689
5690 fnd_file.put_line(FND_FILE.LOG,'delete the bad receipts');
5691
5692 update ar_cash_receipts
5693 set selected_remittance_batch_id = null
5694 where request_id = p_request_id
5695 and cc_error_flag = 'Y';
5696
5697 IF PG_DEBUG in ('Y','C') THEN
5698 fnd_file.put_line(FND_FILE.LOG,' rows updated CR = ' || SQL%ROWCOUNT );
5699 END IF;
5700
5701
5702 UPDATE ar_cash_receipt_history
5703 SET reversal_cash_receipt_hist_id = null,
5704 reversal_gl_date = null,
5705 reversal_created_from = null,
5706 current_record_flag = 'Y'
5707 where request_id = p_request_id
5708 and status = 'CONFIRMED'
5709 and cash_receipt_id in ( select cash_receipt_id
5710 from ar_cash_receipts
5711 where request_id = p_request_id
5712 and cc_error_flag = 'Y');
5713
5714 IF PG_DEBUG in ('Y','C') THEN
5715 fnd_file.put_line(FND_FILE.LOG,' rows UPDATED CRH = ' || SQL%ROWCOUNT );
5716 END IF;
5717
5718 delete from ar_distributions
5719 where source_table = 'CRH'
5720 and source_id in ( select crh.cash_receipt_history_id
5721 from ar_cash_receipt_history crh,
5722 ar_cash_receipts cr
5723 where crh.STATUS = 'REMITTED'
5724 and crh.request_id = p_request_id
5725 and cr.cash_receipt_id = crh.cash_receipt_id
5726 and cr.request_id = p_request_id
5727 and cr.cc_error_flag = 'Y' );
5728
5729 IF PG_DEBUG in ('Y','C') THEN
5730 fnd_file.put_line(FND_FILE.LOG,' rows DELETED AR_DIST = ' || SQL%ROWCOUNT );
5731 END IF;
5732
5733 delete from ar_cash_receipt_history
5734 where STATUS = 'REMITTED'
5735 and request_id = p_request_id
5736 and cash_receipt_id in ( select cash_receipt_id
5737 from ar_cash_receipts
5738 where request_id = p_request_id
5739 and cc_error_flag = 'Y');
5740
5741
5742 IF PG_DEBUG in ('Y','C') THEN
5743 fnd_file.put_line(FND_FILE.LOG,' rows DELETED CRH = ' || SQL%ROWCOUNT );
5744 END IF;
5745
5746
5747 END IF;
5748
5749 EXCEPTION
5750 WHEN OTHERS THEN
5751 IF PG_DEBUG in ('Y', 'C') THEN
5752 arp_standard.debug('rec_reset ERROR.'|| SQLERRM );
5753 END IF;
5754
5755 END rec_reset;
5756
5757 END AR_AUTOREM_API;