[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_AR_AUTO_INVOICE
Source
1 PACKAGE BODY JG_ZZ_AR_AUTO_INVOICE AS
2 /* $Header: jgzztnub.pls 120.16 2012/02/14 14:01:24 abuissa ship $ */
3
4 Function Is_context_enabled (l_country_code In Varchar2) Return Boolean IS
5 l_exist Varchar2(30);
6 Begin
7
8 /* Checks whether the context is enabled for the country. It checks the contexts
9 only for the JG_RA_CUSTOMER_TRX_LINES gdf. */
10 SELECT 'YES' INTO l_exist
11 FROM fnd_descr_flex_contexts
12 WHERE application_id = 7003
13 AND descriptive_flexfield_name like 'JG_RA_CUSTOMER_TRX_LINES'
14 AND descriptive_flex_context_code like '%ARXTWMAI.REGISTER_INFO%'
15 AND substr(descriptive_flex_context_code, 4, 2) = l_country_code;
16 IF l_exist = 'YES' THEN
17 Return TRUE;
18 ELSE
19 Return FALSE;
20 END IF;
21 Exception
22 When Others Then
23 Return FALSE;
24 End;
25
26
27 Function Is_Reg_Loc_Enabled Return Boolean IS
28 l_country_code Varchar2(30);
29 Begin
30 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
31 -- Start Bug 8982308
32 If (l_country_code = 'TW' or l_country_code = 'AR' or l_country_code = 'BR') THEN
33 -- Start Bug 8982308
34 Return TRUE;
35 Elsif Is_context_enabled(l_country_code) THEN
36 Return TRUE;
37 Else
38 Return FALSE;
39 End If;
40 Exception
41 When Others Then
42 Return FALSE;
43 End;
44
45 Procedure Trx_Num_Upd (p_request_id In Number) Is
46 Cursor C_Trx_Lines (x_request_id Number) Is
47 Select l.trx_number
48 ,l.customer_trx_id
49 From ra_customer_trx_all l
50 Where l.request_id = x_request_id
51 And l.complete_flag = 'Y'
52 And l.customer_trx_id Is Not Null;
53
54 --New Cursor for bug 10038807
55 Cursor C_AR_Trx_Lines (x_request_id Number) Is
56 Select l.trx_number
57 ,l.customer_trx_id
58 ,global_attribute16
59 ,nvl(l.cust_trx_type_id,0)
60 From ra_customer_trx_all l
61 Where l.request_id = x_request_id
62 And l.complete_flag = 'Y'
63 And l.customer_trx_id Is Not Null;
64
65 CURSOR C_AR_Batch_Details (p_batch_source_id In Number, p_org_id In Number) IS
66 SELECT substr(global_attribute2,1,4),
67 substr(global_attribute3,1,1),
68 auto_trx_numbering_flag,
69 global_attribute8,
70 global_attribute9,
71 nvl(decode(batch_source_type, 'INV', to_number(global_attribute1),
72 batch_source_id),0), --bug 10038807
73 nvl(legal_entity_id,0) --bug 10038807
74 FROM ra_batch_sources_all
75 WHERE batch_source_id = p_batch_source_id
76 AND org_id = p_org_id;
77
78 -- Start Bug 8982308
79
80 l_copy_doc_number_flag VARCHAR2(1) := NULL; -- 9090969
81
82 CURSOR C_BR_Batch_Details (l_batch_source_id Number,l_org_id Number) IS
83 SELECT auto_trx_numbering_flag , copy_doc_number_flag -- 9090969
84 FROM ra_batch_sources_all
85 WHERE batch_source_id = l_batch_source_id
86 and org_id = l_org_id;
87 -- End Bug 8982308
88
89 -- Start Bug 8709620
90 CURSOR C_BR_Imp_Batch_Source (p_batch_source_id In Number, p_org_id In Number) IS
91 SELECT rbc_1.global_attribute1 imp_batch_source_id
92 FROM ra_batch_sources_all rbc_1
93 WHERE rbc_1.batch_source_id = p_batch_source_id
94 AND rbc_1.org_id = p_org_id;
95 -- End Bug 8709620
96
97 TYPE trx_id is Table of ra_customer_trx_all.customer_trx_id%Type;
98 TYPE trx_num is Table of ra_customer_trx_all.trx_number%Type;
99 TYPE gdf16 is Table of ra_customer_trx_all.global_attribute16%Type;
100 TYPE cust_trx_typ_id is Table of ra_customer_trx_all.cust_trx_type_id%Type;
101
102 customer_trx_id trx_id;
103 trx_number trx_num;
104 global_attribute16 gdf16;
105 cust_trx_type_id cust_trx_typ_id;
106
107 l_batch_source_id Number;
108 l_imp_batch_src_id Number; --bug 10038807
109 l_legal_entity_id Number; --bug 10038807
110 l_num_bar_code Number; --bug 10038807
111 l_auto_trx_num_flag Varchar2(1);
112 l_copy_doc_num_flag Varchar2(1);
113 l_inv_word Varchar2(2);
114 l_init_trx_num Varchar2(8);
115 l_fin_trx_num Varchar2(8);
116 l_last_trx_date Varchar2(30);
117 l_adv_days Number;
118 l_org_id Number;
119 l_seq_name Varchar2(30);
120 l_seq_number Number;
121 l_err_code Number;
122 l_trx_number Varchar2(30);
123 l_debug_loc Varchar2(100);
124 l_country_code Varchar2(30);
125 FATAL_ERROR Exception;
126 l_branch_number Varchar2(4);
127 l_document_letter Varchar2(1);
128 l_cai_num Varchar2(15);
129 l_cai_due_date Varchar2(20);
130 f_org_id Varchar2(15);
131 temp1 Number;
132 temp2 Number;
133 -- Start Bug 8709620
134 l_imp_batch_source_id Number;
135 -- End Bug 8709620
136
137 Begin
138 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
139 --fnd_profile.get('ORG_ID',l_org_id);
140 l_org_id := MO_GLOBAL.get_current_org_id; -- bug 8304339
141
142 If l_country_code = 'TW' Then
143 l_debug_loc := 'jg_zz_ar_auto_invoice.trx_num_upd, country code TW';
144 Open C_Trx_lines(p_request_id);
145 Fetch C_Trx_lines Bulk Collect Into trx_number, customer_trx_id;
146 Close C_Trx_lines;
147
148 Select To_number(cr.argument3)
149 Into l_batch_source_id
150 From fnd_concurrent_requests cr
151 Where request_id = p_request_id;
152
153 l_debug_loc := 'ja_tw_sh_gui_utils.get_trx_src_info';
154 ja_tw_sh_gui_utils.get_trx_src_info(
155 l_batch_source_id
156 ,l_auto_trx_num_flag
157 ,l_inv_word
158 ,l_init_trx_num
159 ,l_fin_trx_num
160 ,l_last_trx_date
161 ,l_adv_days
162 ,l_org_id);
163
164 l_seq_name := ja_tw_sh_gui_utils.get_seq_name(l_batch_source_id);
165
166 BEGIN
167 SELECT COPY_DOC_NUMBER_FLAG INTO l_copy_doc_num_flag FROM RA_BATCH_SOURCES_ALL
168 WHERE BATCH_SOURCE_ID = l_batch_source_id
169 AND ORG_ID = l_org_id;
170 EXCEPTION
171 WHEN OTHERS THEN
172 null;
173 END;
174
175 If trx_number.count > 0 and l_copy_doc_num_flag <> 'Y' Then
176 For i in customer_trx_id.FIRST .. customer_trx_id.LAST Loop
177 l_debug_loc := 'ja_tw_sh_gui_utils.val_trx_num';
178 If ja_tw_sh_gui_utils.val_trx_num(
179 NULL
180 ,l_batch_source_id
181 ,l_fin_trx_num
182 ,'RAXTRX') = 'FATAL' Then
183 Exit;
184 Else
185 ja_tw_sh_gui_utils.get_next_seq_num(
186 l_seq_name
187 ,l_seq_number
188 ,l_err_code);
189 l_trx_number := l_inv_word || LPAD(l_seq_number,8,'0');
190 trx_number(i) := l_trx_number;
191 End If;
192 End Loop;
193
194 Forall j in customer_trx_id.FIRST .. customer_trx_id.LAST
195 Update ra_customer_trx_all
196 Set trx_number = trx_number(j)
197 Where customer_trx_id = customer_trx_id(j)
198 and request_id = p_request_id;
199 End If;
200
201 Elsif l_country_code = 'AR' Then
202 --fnd_profile.get('ORG_ID',f_org_id);
203 f_org_id := MO_GLOBAL.get_current_org_id;
204
205 l_debug_loc := 'jg_zz_ar_auto_invoice.trx_num_upd, country code AR';
206 Open C_AR_Trx_lines(p_request_id);
207 Fetch C_AR_Trx_lines Bulk Collect Into trx_number, customer_trx_id, global_attribute16,cust_trx_type_id; --bug 10038807
208 Close C_AR_Trx_lines;
209
210 SELECT to_number(cr.argument3)
211 INTO l_batch_source_id
212 FROM fnd_concurrent_requests cr
213 WHERE request_id = p_request_id;
214
215
216 /* Code added for Transaction created by CopyTo Operation */
217 --Start
218 IF l_batch_source_id IS NULL THEN
219
220 SELECT B.batch_source_id INTO temp1
221 FROM ra_customer_trx_all A, ra_customer_trx_all B
222 WHERE A.RECURRED_FROM_TRX_NUMBER = B.trx_number
223 AND A.trx_number = trx_number(1) AND B.ORG_ID = f_org_id and rownum =1;
224 BEGIN
225
226 SELECT GLOBAL_ATTRIBUTE1 INTO temp2
227 FROM RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = temp1;
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 null;
231 END;
232 IF temp2 IS NULL THEN
233 l_batch_source_id := temp1;
234 ELSE
235 l_batch_source_id := temp2;
236 END IF;
237
238 END IF;
239 --End
240
241 IF l_batch_source_id IS NOT NULL THEN
242 l_debug_loc := 'Getting Transaction Source Information';
243
244 OPEN C_AR_Batch_Details(l_batch_source_id, l_org_id);
245 FETCH C_AR_Batch_Details INTO l_branch_number,l_document_letter,l_auto_trx_num_flag,
246 l_cai_num,l_cai_due_date,l_imp_batch_src_id,l_legal_entity_id; --bug 10038807
247 CLOSE C_AR_Batch_Details;
248
249 l_seq_name := 'JL_ZZ_TRX_NUM_'
250 || to_char(l_batch_source_id)
251 || '_'
252 || f_org_id
253 || '_S';
254
255 END IF;
256
257 IF trx_number.count > 0 AND l_auto_trx_num_flag = 'Y' THEN
258 FOR i IN customer_trx_id.FIRST .. customer_trx_id.LAST LOOP
259 l_debug_loc := 'Getting Next Sequence number';
260 JL_ZZ_AR_LIBRARY_1_PKG.get_next_seq_number (l_seq_name, l_seq_number,1,l_err_code);
261 IF l_err_code = 0 THEN
262 l_trx_number := l_document_letter || '-' || l_branch_number || '-'
263 || lpad(l_seq_number,8,'0');
264 trx_number(i) := l_trx_number;
265 END IF;
266 -- bug 10038807 Start
267 l_debug_loc := 'Getting Numeric Bar Code';
268 l_num_bar_code := JL_AR_DOC_NUMBERING_PKG.get_num_bar_code(l_imp_batch_src_id,
269 cust_trx_type_id(i),l_legal_entity_id);
270 global_attribute16(i) := l_num_bar_code;
271 -- bug 10038807 End
272 END LOOP;
273
274 FORALL j IN customer_trx_id.FIRST .. customer_trx_id.LAST
275 UPDATE ra_customer_trx_all
276 SET trx_number = trx_number(j),
277 global_attribute16 = global_attribute16(j), --bug 10038807
278 global_attribute17 = l_cai_num,
279 global_attribute18 = l_cai_due_date
280 WHERE customer_trx_id = customer_trx_id(j)
281 AND request_id = p_request_id;
282 END IF;
283
284 -- Start Bug 8982308
285 Elsif l_country_code = 'BR' Then
286
287 -- fnd_profile.get('ORG_ID',f_org_id);
288 f_org_id := MO_GLOBAL.get_current_org_id;
289 l_debug_loc := 'jg_zz_ar_auto_invoice.trx_num_upd, country code BR';
290 Open C_Trx_lines(p_request_id);
291 Fetch C_Trx_lines Bulk Collect Into trx_number, customer_trx_id;
292 Close C_Trx_lines;
293
294 Select to_number(cr.argument3)
295 Into l_batch_source_id
296 From fnd_concurrent_requests cr
297 Where request_id = p_request_id;
298
299 /* Code added for Transaction created by CopyTo Operation */
300 If l_batch_source_id IS NULL THEN
301
302 /*Select B.batch_source_id
303 into temp1
304 From ra_customer_trx_all A, ra_customer_trx_all B
305 Where A.recurred_from_trx_number = B.trx_number
306 And A.trx_number = trx_number(1) AND B.ORG_ID = f_org_id and rownum =1;*/
307
308 Select batch_source_ID INTO temp1 from ra_customer_trx_all
309 where customer_trx_id = customer_trx_id(1);
310
311 Begin
312 Select GLOBAL_ATTRIBUTE1
313 Into temp2
314 From RA_BATCH_SOURCES_ALL
315 Where BATCH_SOURCE_ID = temp1;
316 Exception
317 When NO_DATA_FOUND THEN
318 null;
319 End;
320 If temp2 IS NULL THEN
321 l_batch_source_id := temp1;
322 Else
323 l_batch_source_id := temp2;
324 End If;
325
326 End If;
327
328 IF l_batch_source_id IS NOT NULL THEN
329 l_debug_loc := 'Getting Transaction Source Information';
330
331 -- Start Bug 8709620
332 Open C_BR_Imp_Batch_Source (l_batch_source_id, f_org_id);
333 Fetch C_BR_Imp_Batch_Source Into l_imp_batch_source_id;
334 Close C_BR_Imp_Batch_Source;
335
336 If l_imp_batch_source_id is null Then
337 -- End Bug 8709620
338 Open C_BR_Batch_Details(l_batch_source_id,f_org_id);
339 Fetch C_BR_Batch_Details Into l_auto_trx_num_flag,l_copy_doc_number_flag ;
340 Close C_BR_Batch_Details;
341
342 l_seq_name := 'JL_BR_TRX_NUM_'
343 || to_char(l_batch_source_id)
344 || '_'
345 || f_org_id
346 || '_S';
347
348 -- Start Bug 8709620
349 Else
350 Open C_BR_Batch_Details(l_imp_batch_source_id,f_org_id);
351 Fetch C_BR_Batch_Details Into l_auto_trx_num_flag,l_copy_doc_number_flag ;
352 Close C_BR_Batch_Details;
353
354 l_seq_name := 'JL_BR_TRX_NUM_'
355 || to_char(l_imp_batch_source_id)
356 || '_'
357 || f_org_id
358 || '_S';
359 End if;
360 -- End Bug 8709620
361 End If;
362
363 If trx_number.count > 0 AND l_auto_trx_num_flag = 'Y' AND NVL(l_copy_doc_number_flag,'N') = 'N' THEN
364
365 FOR i IN customer_trx_id.FIRST .. customer_trx_id.LAST LOOP
366 l_debug_loc := 'Getting Next Sequence number';
367 JL_ZZ_AR_LIBRARY_1_PKG.get_next_seq_number (l_seq_name, l_seq_number,1,l_err_code);
368 IF l_err_code = 0 THEN
369 trx_number(i) := l_seq_number;
370 END IF;
371 END LOOP;
372
373 FORALL j IN customer_trx_id.FIRST .. customer_trx_id.LAST
374 UPDATE ra_customer_trx_all
375 SET trx_number = trx_number(j)
376 WHERE customer_trx_id = customer_trx_id(j)
377 AND request_id = p_request_id;
378 End If;
379 -- End Bug 8982308
380
381 End If;
382
383 Exception
384 When Others Then
385 arp_standard.debug('-- Found an exception at ' || l_debug_loc||'.');
386 arp_standard.debug('-- ' || SQLERRM);
387 End;
388
389 PROCEDURE val_trx_range (p_request_id IN Number, p_flag OUT NOCOPY Number) IS
390 CURSOR C_Trx_Lines IS
391 SELECT l.customer_trx_id
392 FROM ra_customer_trx_all l
393 WHERE l.request_id = p_request_id
394 --And l.complete_flag = 'Y'
395 AND l.customer_trx_id IS NOT NULL;
396
397 CURSOR C_Reject_Entry (p_trx_id NUMBER) IS
398 SELECT A.customer_trx_line_id line_id, B.cust_trx_line_gl_dist_id dist_id
399 FROM ra_customer_trx_lines_all A ,ra_cust_trx_line_gl_dist_all B
400 WHERE A.customer_trx_id = p_trx_id AND
401 A.customer_trx_line_id = B.customer_trx_line_id
402 AND ROWNUM = 1;
403
404 TYPE trx_id IS TABLE OF ra_customer_trx_all.customer_trx_id%Type;
405
406 customer_trx_id trx_id;
407
408 l_batch_source_id Number;
409 l_auto_trx_num_flag Varchar2(1);
410 l_copy_doc_num_flag Varchar2(1);
411 l_inv_word Varchar2(2);
412 l_init_trx_num Varchar2(8);
413 l_fin_trx_num Varchar2(8);
414 l_last_trx_date Varchar2(30);
415 l_adv_days Number;
416 l_seq_name Varchar2(30);
417 l_seq_number Number;
418 l_err_code Number;
419 l_trx_number Varchar2(30);
420 l_debug_loc Varchar2(100);
421 l_country_code Varchar2(30);
422 l_org_id Varchar2(15);
423 l_count Number DEFAULT 0;
424 l_line_id Number;
425 l_dist_id Number;
426 l_last_trx_num Varchar2(8);
427 l_message_text Varchar2(240);
428 l_batch_source_name Varchar2(50);
429 BEGIN
430 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
431 --fnd_profile.get('ORG_ID',l_org_id);
432 l_org_id := MO_GLOBAL.get_current_org_id;
433
434 BEGIN
435 SELECT TO_NUMBER(cr.argument3) INTO l_batch_source_id
436 FROM fnd_concurrent_requests cr
437 WHERE request_id = p_request_id;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 null;
442 END;
443
444 BEGIN
445 SELECT COPY_DOC_NUMBER_FLAG INTO l_copy_doc_num_flag FROM RA_BATCH_SOURCES_ALL
446 WHERE BATCH_SOURCE_ID = l_batch_source_id
447 AND ORG_ID = l_org_id;
448 EXCEPTION
449 WHEN OTHERS THEN
450 null;
451 END;
452
453
454 IF l_country_code = 'TW' and l_copy_doc_num_flag <> 'Y' THEN
455 p_flag := 0;
456 l_debug_loc := 'Country code TW';
457
458 OPEN C_Trx_lines;
459 FETCH C_Trx_lines BULK COLLECT INTO customer_trx_id;
460 CLOSE C_Trx_lines;
461
462
463 SELECT name INTO l_batch_source_name
464 FROM RA_BATCH_SOURCES_ALL
465 WHERE batch_source_id = l_batch_source_id;
466
467 l_debug_loc := 'Getting Transaction Source details';
468 ja_tw_sh_gui_utils.get_trx_src_info(
469 l_batch_source_id
470 ,l_auto_trx_num_flag
471 ,l_inv_word
472 ,l_init_trx_num
473 ,l_fin_trx_num
474 ,l_last_trx_date
475 ,l_adv_days
476 ,l_org_id);
477
478 l_seq_name := ja_tw_sh_gui_utils.get_seq_name(l_batch_source_id);
479 l_last_trx_num := ja_tw_sh_gui_utils.get_last_trx_num(l_seq_name);
480
481 --fnd_message.set_name( 'JA','JA_TW_GUI_NUM_OVERLIMIT_CHQ');
482 fnd_message.set_name( 'JA','JA_TW_AR_GUI_NUM_OUT_OF_RANGE');
483 fnd_message.set_token('BATCH_SOURCE_NAME',l_batch_source_name);
484 l_message_text := fnd_message.GET;
485
486 IF customer_trx_id.count > 0 THEN
487 l_debug_loc := 'Checking for the Sequence maximum limit';
488 FOR i IN customer_trx_id.FIRST .. customer_trx_id.LAST LOOP
489 IF to_number(l_last_trx_num) < to_number(l_fin_trx_num) THEN
490 null;
491 ELSE
492 l_count := i;
493 EXIT;
494 END IF;
495 l_last_trx_num := to_number(l_last_trx_num + 1);
496 END LOOP;
497 END IF;
498
499 IF l_count > 0 THEN
500 l_debug_loc := 'Inserting into Interface Error over limit transactions';
501 FOR i IN l_count .. customer_trx_id.LAST LOOP
502 FOR C_Reject_Entry_Rec IN C_Reject_Entry(customer_trx_id(i)) LOOP
503 INSERT INTO ra_interface_errors(
504 interface_line_id,
505 --interface_distribution_id, --bug 8306469
506 message_text,
507 org_id)
508 VALUES(
509 C_Reject_Entry_Rec.line_id,
510 --C_Reject_Entry_Rec.dist_id,
511 l_message_text,
512 l_org_id);
513 p_flag := p_flag + 1;
514 END LOOP;
515 END LOOP;
516
517 END IF;
518
519 END IF;
520
521 /* To nullify the global attribute columns. This is to avoid populating
522 Global columns while Copying transaction by "CopyTo" function */
523 --IF Is_context_enabled(l_country_code) THEN
524 -- arp_standard.debug('-- Start JE_GLOBAL_PKG.nullify_globalcolumns ');
525 -- JE_COMMON_PKG.nullify_globalcolumns(p_request_id);
526 -- arp_standard.debug('-- End JE_GLOBAL_PKG.nullify_globalcolumns ');
527 --END IF;
528
529 EXCEPTION
530 WHEN OTHERS THEN
531 p_flag := 0;
532 arp_standard.debug('-- Found an exception at :' || l_debug_loc||'.');
533 arp_standard.debug('-- ' || SQLERRM);
534 END;
535
536 -- Bug 12575584 Start
537
538 Procedure Trx_Num_Upd_Single_API (p_trx_id IN Number) Is
539
540 -- Cursor to identify Transaction Number from Trx_ID
541 Cursor C_Trx_Lines (x_trx_id Number) Is
542 Select l.trx_number,l.batch_source_id
543 From ra_customer_trx_all l
544 Where l.customer_trx_id = x_trx_id
545 And l.complete_flag = 'Y'
546 And l.customer_trx_id Is Not Null;
547
548 -- Cursor to identify if automatic transaction numbering is enabled for the batch source
549 Cursor C_BR_Batch_Details (p_batch_source_id Number, p_org_id Number) IS
550 Select auto_trx_numbering_flag,copy_doc_number_flag
551 From ra_batch_sources_all
552 Where batch_source_id = p_batch_source_id
553 And org_id = p_org_id;
554
555 -- Cursor to identify Transaction Batch Source Type
556 -- Bug 12595382 Start Changed the Name of the cursor
557 Cursor C_Batch_Source_Type (p_batch_source_id Number, p_org_id Number) IS
558 -- Bug 12595382 End
559 Select Batch_Source_Type
560 From ra_batch_sources_all
561 Where batch_source_id = p_batch_source_id
562 And org_id = p_org_id;
563
564 -- Bug 12595382 Start
565 Cursor C_AR_Batch_Details (p_batch_source_id In Number, p_org_id In Number) Is
566 Select substr(global_attribute2,1,4),
567 substr(global_attribute3,1,1),
568 auto_trx_numbering_flag,
569 global_attribute8,
570 global_attribute9,
571 nvl(legal_entity_id,0)
572 From ra_batch_sources_all
573 Where batch_source_id = p_batch_source_id
574 And org_id = p_org_id;
575
576 Cursor C_AR_Trx_Lines (x_trx_id Number) Is
577 Select l.trx_number,
578 l.batch_source_id,
579 nvl(l.cust_trx_type_id,0)
580 From ra_customer_trx_all l
581 Where l.customer_trx_id = x_trx_id
582 And l.complete_flag = 'Y';
583 -- Bug 12595382 End
584
585 l_trx_number ra_customer_trx_all.trx_number%Type;
586 l_batch_source_type ra_batch_sources_all.batch_source_type%Type;
587 f_batch_source_id ra_batch_sources_all.batch_source_id%Type;
588 l_batch_source_id ra_batch_sources_all.batch_source_id%Type;
589 l_auto_trx_num_flag ra_batch_sources_all.auto_trx_numbering_flag%Type;
590 l_copy_doc_number_flag ra_batch_sources_all.copy_doc_number_flag%Type;
591 l_seq_number Number;
592 l_err_code Number;
593 l_err_code1 Number;
594 l_seq_name Varchar2(30);
595 l_debug_loc Varchar2(400);
596 l_country_code Varchar2(30);
597 f_org_id Varchar2(15);
598 -- Bug 12595382 Start
599 l_cust_trx_type_id ra_customer_trx_all.cust_trx_type_id%Type;
600 l_legal_entity_id ra_batch_sources_all.legal_entity_id%Type;
601 l_branch_number Varchar2(4);
602 l_document_letter Varchar2(1);
603 l_cai_num ra_batch_sources_all.global_attribute8%Type;
604 l_cai_due_date ra_batch_sources_all.global_attribute9%Type;
605 l_num_bar_code ra_customer_trx_all.global_attribute16%Type;
606 -- Bug 12595382 End
607
608 FATAL_ERROR Exception;
609 GET_BATCH_ID_ERR Exception;
610
611 Begin
612 l_debug_loc := 'Getting the Country Code';
613 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
614
615 -- Bug 12595382 Start
616 l_debug_loc := 'Getting the Org_Id';
617 f_org_id := MO_GLOBAL.get_current_org_id;
618 -- Bug 12595382 End
619
620 If l_country_code = 'BR' Then
621
622 l_debug_loc := 'Open C_Trx_lines';
623 Open C_Trx_lines(p_trx_id);
624 Fetch C_Trx_lines Into l_trx_number,f_batch_source_id;
625 Close C_Trx_lines;
626
627 l_debug_loc := 'Transaction Number'|| l_trx_number ||':'||'BATCH_SOURCE_ID'||f_batch_source_id;
628
629 If (l_trx_number Is Not Null AND f_batch_source_id Is Not Null) Then
630 -- Bug 12595382 Start, using the new cursor name
631 Open C_Batch_Source_Type (f_batch_source_id, f_org_id);
632 Fetch C_Batch_Source_Type into l_batch_source_type;
633 Close C_Batch_Source_Type;
634 -- Bug 12595382 End
635
636 l_debug_loc := 'BATCH_SOURCE_TYPE'||l_BATCH_SOURCE_TYPE;
637
638 If l_batch_source_type = 'INV' THEN-- Manual Source
639 -- Get the Imported batch Source Id
640 l_debug_loc := 'Calling JL_ZZ_AR_LIBRARY_1_PKG.get_batch_id';
641 JL_ZZ_AR_LIBRARY_1_PKG.get_batch_id ( f_batch_source_id, l_batch_source_id, 1, l_err_code1 );
642 l_debug_loc := 'New Batch ID'||l_batch_source_id;
643 l_debug_loc := 'Error Code'|| l_err_code1;
644 If l_err_code1 = 0 Then -- If no errors proceed
645 If l_batch_source_id Is Not Null Then
646 l_debug_loc := 'Getting Transaction Source Information';
647 l_seq_name := 'JL_BR_TRX_NUM_' || to_char(l_batch_source_id) || '_' || f_org_id || '_S';
648 Open C_BR_Batch_Details(l_batch_source_id,f_org_id);
649 Fetch C_BR_Batch_Details Into l_auto_trx_num_flag, l_copy_doc_number_flag;
650 Close C_BR_Batch_Details;
651 End If;
652 Else -- Stop Processing
653 l_debug_loc := 'No Imported Batch Source Associated with Manual Batch Source'||f_batch_source_id;
654 Raise GET_BATCH_ID_ERR;
655 End If;
656 Else -- Imported Source
657 l_debug_loc := 'Imported Batch Source';
658 l_seq_name := 'JL_BR_TRX_NUM_' || to_char(f_batch_source_id) || '_' || f_org_id || '_S';
659 Open C_BR_Batch_Details(f_batch_source_id,f_org_id);
660 Fetch C_BR_Batch_Details Into l_auto_trx_num_flag, l_copy_doc_number_flag;
661 Close C_BR_Batch_Details;
662 End If;
663 If l_auto_trx_num_flag = 'Y' AND NVL(l_copy_doc_number_flag,'N') = 'N' Then -- Automatic Numbering
664 l_debug_loc := 'Getting Next Sequence number';
665 JL_ZZ_AR_LIBRARY_1_PKG.get_next_seq_number (l_seq_name, l_seq_number,1,l_err_code);
666 If l_err_code = 0 Then
667 Update ra_customer_trx_all
668 Set trx_number = l_seq_number
669 Where customer_trx_id = p_trx_id;
670 End If;
671 End If;
672 End If;
673 -- Bug 12595382 Start
674 Elsif l_country_code = 'AR' Then
675 l_debug_loc := 'Open C_AR_Trx_Lines';
676 Open C_AR_Trx_Lines(p_trx_id);
677 Fetch C_AR_Trx_Lines
678 Into l_trx_number,
679 f_batch_source_id,
680 l_cust_trx_type_id;
681 Close C_AR_Trx_Lines;
682 l_debug_loc := 'Transaction Number '|| l_trx_number ||', '||' Batch Source ID '||f_batch_source_id;
683
684 If (l_trx_number Is Not Null AND f_batch_source_id Is Not Null) Then
685
686 Open C_Batch_Source_Type (f_batch_source_id, f_org_id);
687 Fetch C_Batch_Source_Type into l_batch_source_type;
688 Close C_Batch_Source_Type;
689 l_debug_loc := 'Batch_Source_Type '|| l_batch_source_type;
690
691 If l_batch_source_type = 'INV' THEN-- Manual Source
692
693 -- Get the Imported batch Source Id
694 l_debug_loc := 'Manual Batch Source, calling JL_ZZ_AR_LIBRARY_1_PKG.get_batch_id';
695 JL_ZZ_AR_LIBRARY_1_PKG.get_batch_id ( f_batch_source_id, l_batch_source_id, 1, l_err_code1 );
696 l_debug_loc := 'New Batch ID'||l_batch_source_id;
697 l_debug_loc := 'Error Code'|| l_err_code1;
698
699 If l_err_code1 = 0 Then -- If no errors proceed
700 If l_batch_source_id Is Not Null Then
701 l_debug_loc := 'Generating the JL Sequence Name';
702 l_seq_name := 'JL_ZZ_TRX_NUM_' || to_char(l_batch_source_id) || '_' || f_org_id || '_S';
703
704 l_debug_loc := 'Fetching the Manual Transaction Batch Source Information';
705 Open C_AR_Batch_Details(l_batch_source_id, f_org_id);
706 Fetch C_AR_Batch_Details
707 Into l_branch_number,
708 l_document_letter,
709 l_auto_trx_num_flag,
710 l_cai_num,
711 l_cai_due_date,
712 l_legal_entity_id;
713 Close C_AR_Batch_Details;
714
715 l_debug_loc := 'Getting the Bar Code Number';
716 l_num_bar_code := JL_AR_DOC_NUMBERING_PKG.get_num_bar_code(l_batch_source_id, l_cust_trx_type_id,l_legal_entity_id);
717 End If;
718 Else -- Stop Processing
719 l_debug_loc := 'No Imported Batch Source Associated with Manual Batch Source'||f_batch_source_id;
720 Raise GET_BATCH_ID_ERR;
721 End If;
722 Else -- Imported Source
723 l_debug_loc := 'Imported Batch Source, generating the JL Sequence Name';
724 l_seq_name := 'JL_ZZ_TRX_NUM_' || to_char(f_batch_source_id) || '_' || f_org_id || '_S';
725
726 l_debug_loc := 'Fetching the Imported Transaction Batch Source Information';
727 Open C_AR_Batch_Details(f_batch_source_id, f_org_id);
728 Fetch C_AR_Batch_Details
729 Into l_branch_number,
730 l_document_letter,
731 l_auto_trx_num_flag,
732 l_cai_num,
733 l_cai_due_date,
734 l_legal_entity_id;
735 Close C_AR_Batch_Details;
736
737 l_debug_loc := 'Getting the Bar Code Number';
738 l_num_bar_code := JL_AR_DOC_NUMBERING_PKG.get_num_bar_code(f_batch_source_id, l_cust_trx_type_id,l_legal_entity_id);
739 End If;
740 If l_auto_trx_num_flag = 'Y' Then -- Automatic Numbering
741
742 l_debug_loc := 'Getting Next Sequence number';
743 JL_ZZ_AR_LIBRARY_1_PKG.get_next_seq_number (l_seq_name, l_seq_number,1,l_err_code);
744 If l_err_code = 0 THEN
745 l_debug_loc := 'Generating the new AR Transaction Number';
746 l_trx_number := l_document_letter || '-' || l_branch_number || '-' || lpad(l_seq_number,8,'0');
747 Update ra_customer_trx_all
748 Set trx_number = l_trx_number,
749 global_attribute16 = l_num_bar_code,
750 global_attribute17 = l_cai_num,
751 global_attribute18 = l_cai_due_date
752 Where customer_trx_id = p_trx_id;
753 End If;
754 End If;
755 End If;
756 -- Bug 12595382 End
757 End If;
758 Exception
759 When GET_BATCH_ID_ERR Then
760 arp_standard.debug('-- Found an exception at ' || l_debug_loc||'.');
761 When OTHERS Then
762 arp_standard.debug('-- Found an exception at ' || l_debug_loc||'.');
763 arp_standard.debug('-- ' || SQLERRM);
764 End Trx_Num_Upd_Single_API;
765 -- Bug 12575584 End
766
767 --ER 13528443 - start
768 PROCEDURE upd_trx_date (p_request_id IN Number) IS
769 CURSOR C_Batch IS
770 SELECT DISTINCT l.batch_source_name, l.org_id
771 FROM ra_interface_lines l
772 WHERE l.request_id = p_request_id
773 AND l.interface_status IS NULL;
774
775 CURSOR C_Trx_Interface (p_bs_name IN VARCHAR2, p_org_id IN NUMBER) IS
776 SELECT l.interface_line_id, trunc(nvl(l.trx_date,SYSDATE)) trx_date
777 FROM ra_interface_lines l
778 WHERE l.request_id = p_request_id
779 AND l.interface_status IS NULL
780 AND l.batch_source_name = p_bs_name
781 AND l.org_id = p_org_id;
782
783 TYPE bs_name IS TABLE OF ra_interface_lines_all.batch_source_name%Type;
784 TYPE bs_org_id IS TABLE OF ra_interface_lines_all.org_id%Type;
785
786 batch_source_name bs_name;
787 org_id bs_org_id;
788
789 l_batch_source_id number;
790 l_max_trx_date date;
791 l_error_msg varchar2(255);
792
793 BEGIN
794
795 fnd_file.put_line(fnd_file.log, 'Entering JG_ZZ_AR_AUTO_INVOICE.upd_trx_date...');
796
797 OPEN C_Batch;
798 FETCH C_Batch BULK COLLECT INTO batch_source_name, org_id;
799 CLOSE C_Batch;
800
801 IF batch_source_name.count > 0 THEN
802
803 FOR i IN batch_source_name.FIRST .. batch_source_name.LAST LOOP
804
805 l_error_msg := 'Error retrieving Batch_Source_Id...';
806
807 SELECT b.batch_source_id
808 INTO l_batch_source_id
809 FROM RA_BATCH_SOURCES_ALL b
810 WHERE b.name = batch_source_name(i)
811 AND b.org_id = org_id(i);
812
813 l_error_msg := '';
814
815 fnd_file.put_line(fnd_file.log, 'l_batch_source_id='||l_batch_source_id);
816
817 --Bug 13716737: Modified following SQL statement
818
819 SELECT TRUNC(NVL(MAX(r.TRX_DATE),SYSDATE))
820 INTO l_max_trx_date
821 FROM RA_CUSTOMER_TRX_ALL r
822 WHERE r.batch_source_id in (
823 SELECT b.batch_source_id
824 FROM RA_BATCH_SOURCES_ALL b
825 WHERE b.batch_source_id = l_batch_source_id
826 AND b.org_id = org_id(i)
827 UNION
828 SELECT c.batch_source_id
829 FROM RA_BATCH_SOURCES_ALL c
830 WHERE c.global_attribute1 = l_batch_source_id
831 AND c.batch_source_type = 'INV'
832 AND c.org_id = org_id(i))
833 AND r.org_id = org_id(i);
834
835 fnd_file.put_line(fnd_file.log, 'l_max_trx_date='||l_max_trx_date);
836
837 FOR C_Trx_Interface_Rec IN C_Trx_Interface (batch_source_name(i), org_id(i)) LOOP
838
839 fnd_file.put_line(fnd_file.log, 'Interface Line Id='||C_Trx_Interface_Rec.interface_line_id);
840 fnd_file.put_line(fnd_file.log, 'Interface trx_date='||C_Trx_Interface_Rec.Trx_Date);
841
842 -- Bug 13702369 - New proposed solution - Start
843 IF l_max_trx_date <= trunc(SYSDATE) THEN
844 IF (C_Trx_Interface_Rec.Trx_Date < trunc(SYSDATE) AND
845 C_Trx_Interface_Rec.Trx_Date < l_max_trx_date) OR
846 C_Trx_Interface_Rec.Trx_Date > trunc(SYSDATE) THEN
847
848 UPDATE ra_interface_lines_all
849 SET Trx_Date = trunc(SYSDATE)
850 WHERE interface_line_id = C_Trx_Interface_Rec.interface_line_id
851 AND org_id = org_id(i);
852
853 fnd_file.put_line(fnd_file.log, 'Updated TRX_DATE with SYSDATE...');
854
855 ELSE
856
857 fnd_file.put_line(fnd_file.log, 'Keeping TRX_DATE from Interface Table...');
858
859 END IF;
860
861 ELSE
862
863 fnd_file.put_line(fnd_file.log, 'Keeping TRX_DATE from Interface Table...');
864
865 END IF;
866
867 -- Bug 13702369 - New proposed solution - End
868
869 END LOOP;
870 END LOOP;
871 END IF;
872
873 fnd_file.put_line(fnd_file.log, 'Exiting JG_ZZ_AR_AUTO_INVOICE.upd_trx_date...');
874
875 EXCEPTION
876 WHEN OTHERS THEN
877 arp_standard.debug('-- Found an exception at UPD_TRX_DATE.');
878 arp_standard.debug('-- ' || SQLERRM);
879 fnd_file.put_line(fnd_file.log, l_error_msg||'EXCEPTION: Exiting JG_ZZ_AR_AUTO_INVOICE.upd_trx_date...');
880 RAISE_APPLICATION_ERROR(- 20999,'Error during update of TRX_DATE on AI pre-processing - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
881 END upd_trx_date;
882 --ER 13528443 - end
883
884 End JG_ZZ_AR_AUTO_INVOICE;
885