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