1 PACKAGE BODY ece_inbound as
2 -- $Header: ECEINBB.pls 120.2 2005/09/28 11:26:03 arsriniv ship $
3
4 /* Bug 2422787 */
5 g_count number:=0;
6 m_orig_stack ec_utils.pl_stack;
7 m_orig_int_levels ec_utils.interface_level_tbl;
8 m_orig_ext_levels ec_utils.external_level_tbl;
9 m_orig_int_ext_levels ec_utils.interface_external_tbl;
10 m_orig_stage_data ec_utils.stage_data;
11 m_orig_stack_pointer ec_utils.stack_pointer;
12 m_tmp1_stage_data ec_utils.stage_data;
13 m_tmp2_stage_data ec_utils.stage_data;
14 m_tmp3_stage_data ec_utils.stage_data;
15 stage_20_flag varchar2(1):='N'; --bug 2500898
16 stage_30_flag varchar2(1):='N';
17 stage_40_flag varchar2(1):='N';
18 stage_50_flag varchar2(1):='N';
19
20
21 procedure process_inbound_documents
22 (
23 i_transaction_type IN varchar2,
24 i_document_id IN number
25 )
26 is
27 i_select_cursor INTEGER;
28 BEGIN
29 if ec_debug.G_debug_level >= 2 then
30 ec_debug.PUSH('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
31 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
32 ec_debug.pl(3,'i_document_id',i_document_id);
33 end if;
34
35 ec_utils.g_stack.DELETE;
36 ec_utils.g_documents_skipped := 0;
37 ec_utils.g_insert_failed := 0;
38 g_previous_map_id := -99;
39
40 select_stage ( i_select_cursor );
41
42 process_documents
43 (
44 i_document_id,
45 i_transaction_type,
46 i_select_cursor
47 );
48
49 /**
50 The Documents is processed. Save the changes now.
51 **/
52 commit;
53
54 ec_utils.g_file_tbl := m_file_tbl_empty;
55
56 close_inbound;
57
58 IF dbms_sql.IS_OPEN(i_select_cursor)
59 then
60 dbms_sql.close_cursor(i_select_cursor);
61 end if;
62 if ec_debug.G_debug_level >= 2 then
63 ec_debug.POP('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
64 end if;
65
66 EXCEPTION
67 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
68 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
69 raise EC_UTILS.PROGRAM_EXIT;
70 WHEN EC_UTILS.PROGRAM_EXIT then
71 raise;
72 WHEN OTHERS THEN
73 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
74 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
75 ec_utils.i_ret_code := 2;
76 raise EC_UTILS.PROGRAM_EXIT;
77 END process_inbound_documents;
78
79 procedure process_inbound_documents
80 (
81 i_transaction_type IN varchar2,
82 i_run_id IN number
83 )
84 IS
85 cursor documents
86 (
87 p_run_id in number,
88 p_transaction_type IN varchar2
89 )is
90 select document_id
91 from ece_stage
92 where run_id = p_run_id
93 and transaction_type = p_transaction_type
94 and transaction_level = 1
95 and line_number = 1
96 for update of Document_Id NOWAIT;
97
98 i_select_cursor INTEGER;
99 i_count number:=0;
100
101 begin
102 if ec_debug.G_debug_level >= 2 then
103 ec_debug.PUSH('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
104 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
105 ec_debug.pl(3,'i_run_id',i_run_id);
106 end if;
107
108 ec_utils.g_stack.DELETE;
109 ec_utils.g_documents_skipped := 0;
110 ec_utils.g_insert_failed := 0;
111 g_previous_map_id := -99;
112
113 select_stage ( i_select_cursor );
114
115 for c1 in documents
116 (
117 p_run_id => i_run_id ,
118 p_transaction_type => i_transaction_type
119 )
120 loop
121
122 process_documents
123 (
124 c1.document_id,
125 i_transaction_type,
126 i_select_cursor
127 );
128
129 /* Bug 2019253 Re-initializing the global map_id and moved the commit out of the loop */
130 g_previous_map_id := -99;
131
132 -- commit;
133
134 /* Bug 2422787
135 i_count := i_count + 1;
136 ec_utils.g_file_tbl := m_file_tbl_empty;
137 */
138 g_count := g_count + 1;
139 end loop;
140 commit;
141 ec_debug.pl(0,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',g_count); --Bug 2422787
142
143 /**
144 Close the Cursors and print the information.
145 **/
146 close_inbound;
147
148 IF dbms_sql.IS_OPEN(i_select_cursor)
149 then
150 dbms_sql.close_cursor(i_select_cursor);
151 end if;
152 if ec_debug.G_debug_level >= 2 then
153 ec_debug.POP('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
154 end if;
155
156 EXCEPTION
157 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
158 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
159 raise EC_UTILS.PROGRAM_EXIT;
160 WHEN EC_UTILS.PROGRAM_EXIT then
161 raise;
162 WHEN OTHERS THEN
163 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
164 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
165 ec_utils.i_ret_code := 2;
166 raise EC_UTILS.PROGRAM_EXIT;
167 end process_inbound_documents;
168
169 procedure process_run_inbound
170 (
171 i_transaction_type IN varchar2,
172 i_run_id IN number
173 )
174 IS
175 cursor documents
176 (
177 p_run_id in number,
178 p_transaction_type IN varchar2
179 )is
180 select document_id
181 from ece_stage
182 where run_id = p_run_id
183 and transaction_type = p_transaction_type
184 and transaction_level = 1
185 and line_number = 1
186 for update of Document_Id NOWAIT;
187
188 i_select_cursor number;
189 i_count number:=0;
190
191 begin
192 if ec_debug.G_debug_level >= 2 then
193 ec_debug.PUSH('ECE_INBOUND.PROCESS_RUN_INBOUND');
194 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
195 ec_debug.pl(3,'i_run_id',i_run_id);
196 end if;
197 /**
198 Gather table Statistics for CBO ( ece_stage , ece_rule_violations ).
199 **/
200 /**
201 fnd_stats.gather_table_stats
202 (
203 'EC',
204 'ECE_STAGE',
205 20,
206 null,
207 null,
208 null,
209 TRUE
210 );
211 fnd_stats.gather_table_stats
212 (
213 'EC',
214 'ECE_RULE_VIOLATIONS',
215 20,
216 null,
217 null,
218 null,
219 TRUE
220 );
221 **/
222
223 ec_utils.g_documents_skipped := 0;
224 ec_utils.g_insert_failed := 0;
225 g_previous_map_id := -99;
226
227 select_stage ( i_select_cursor );
228
229 for c1 in documents
230 (
231 p_run_id => i_run_id ,
232 p_transaction_type => i_transaction_type
233 )
234 loop
235 /**
236 Savepoint for the document. If any error encountered during processing of the
237 document , the whole document will be rolled back to this savepoint.
238 Partial processing of the document is not allowed.
239 **/
240 savepoint document_start;
241
242 run_inbound
243 (
244 c1.document_id,
245 i_transaction_type,
246 i_select_cursor
247 );
248
249 /* Bug 2019253 Re-initializing the global map_id */
250 g_previous_map_id := -99;
251
252 /**
253 If the Status is ABORT then stop the program execution.
254 Rollback the Staging Data , Violations etc.
255 **/
256 if ec_utils.g_ext_levels(1).Status = 'ABORT'
257 then
258 rollback work;
259 ec_utils.i_ret_code := 2;
260 raise EC_UTILS.PROGRAM_EXIT;
261 elsif (ec_utils.g_ext_levels(1).Status = 'SKIP_DOCUMENT')
262 or (ec_utils.g_ext_levels(1).Status = 'INSERT_FAILED')
263 then
264 rollback to Document_start;
265 update_document_status;
266 end if;
267
268 /**
269 Make sure that the all the records for a document are successfully
270 inserted into the production open Interface tables. Check whether
271 the document is ready for Insert.
272 If yes then save the Document and delete from the Staging table.
273 **/
274
275 if ( ec_utils.g_ext_levels(1).Status = 'INSERT'
276 or ec_utils.g_ext_levels(1).Status = 'NEW'
277 or ec_utils.g_ext_levels(1).Status = 'RE_PROCESS'
278 )
279 then
280 delete from ece_rule_violations
281 where document_id = c1.document_id;
282
283 delete from ece_stage
284 where document_id = c1.document_id;
285
286 if sql%notfound
287 then
288 ec_debug.pl(1,'EC','ECE_DELETE_FAILED_STAGING','DOCUMENT_ID',c1.document_id);
289 ec_utils.i_ret_code :=1;
290 rollback to Document_Start;
291 end if;
292 end if;
293
294 /** Save the Violations. **/
295 Insert_Into_Violations(c1.Document_Id);
296
297
298 /* Bug 2422787
299 i_count := i_count + 1;
300 ec_utils.g_file_tbl := m_file_tbl_empty;
301 */
302 g_count := g_count + 1;
303
304 end loop;
305 ec_debug.pl(0,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',g_count); --Bug 2422787
306 /**
307 Close the Cursors and print the information.
308 **/
309 close_inbound;
310
311 IF dbms_sql.IS_OPEN(i_select_cursor)
312 then
313 dbms_sql.close_cursor(i_select_cursor);
314 end if;
315 if ec_debug.G_debug_level >= 2 then
316 ec_debug.POP('ECE_INBOUND.PROCESS_RUN_INBOUND');
317 end if;
318 EXCEPTION
319 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
320 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
321 raise EC_UTILS.PROGRAM_EXIT;
322 WHEN EC_UTILS.PROGRAM_EXIT then
323 raise;
324 WHEN OTHERS THEN
325 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_RUN_INBOUND');
326 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
327 ec_utils.i_ret_code := 2;
328 raise EC_UTILS.PROGRAM_EXIT;
329 end process_run_inbound;
330
331 procedure process_inbound_documents
332 (
333 i_transaction_type IN varchar2,
334 i_status IN varchar2
335 )
336 IS
337 cursor documents
338 (
339 p_transaction_type in varchar2,
340 p_status IN varchar2
341 )is
342 select document_id
343 from ece_stage
344 where transaction_type = p_transaction_type
345 and status = p_status
346 and transaction_level = 1
347 and line_number = 1
348 for update of Document_Id NOWAIT;
349
350 i_select_cursor number;
351 i_count number:=0;
352
353 begin
354 if ec_debug.G_debug_level >= 2 then
355 ec_debug.PUSH('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
356 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
357 ec_debug.pl(3,'i_status',i_status);
358 end if;
359 ec_utils.g_stack.DELETE;
360 ec_utils.g_documents_skipped := 0;
361 ec_utils.g_insert_failed := 0;
362 g_previous_map_id := -99;
363
364 select_stage ( i_select_cursor );
365
366 for c1 in documents
367 (
368 p_status => i_status ,
369 p_transaction_type => i_transaction_type
370 )
371 loop
372 process_documents
373 (
374 c1.document_id,
375 i_transaction_type,
376 i_select_cursor
377 );
378
379 /* Bug 2019253 Re-initializing the global map_id and moved the commit out of the loop */
380 g_previous_map_id := -99;
381
382 /** The Documents are processed. Save the changes now. **/
383 -- commit;
384
385 /* Bug 2422787
386 i_count := i_count + 1;
387 ec_utils.g_file_tbl := m_file_tbl_empty;
388 */
389 g_count := g_count + 1;
390
391 end loop;
392 ec_debug.pl(0,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',g_count); --Bug 2422787
393
394 /**
395 Close the Cursors and print the information.
396 **/
397 close_inbound;
398
399 IF dbms_sql.IS_OPEN(i_select_cursor)
400 then
401 dbms_sql.close_cursor(i_select_cursor);
402 end if;
403 if ec_debug.G_debug_level >= 2 then
404 ec_debug.POP('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
405 end if;
406 EXCEPTION
407 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
408 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
409 raise EC_UTILS.PROGRAM_EXIT;
410 WHEN EC_UTILS.PROGRAM_EXIT then
411 raise;
412 WHEN OTHERS THEN
413 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
414 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
415 ec_utils.i_ret_code := 2;
416 raise EC_UTILS.PROGRAM_EXIT;
417 end process_inbound_documents;
418
419 procedure process_inbound_documents
420 (
421 i_transaction_type IN varchar2
422 )
423 IS
424 cursor documents
425 (
426 p_transaction_type in varchar2
427 )is
428 select document_id
429 from ece_stage
430 where transaction_type = p_transaction_type
431 and transaction_level = 1
432 and line_number = 1
433 for update of Document_Id NOWAIT;
434
435 i_select_cursor number;
436 i_count number:=0;
437
438 begin
439 if ec_debug.G_debug_level >= 2 then
440 ec_debug.PUSH('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
441 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
442 end if;
443 ec_utils.g_stack.DELETE;
444 ec_utils.g_documents_skipped := 0;
445 ec_utils.g_insert_failed := 0;
446 g_previous_map_id := -99;
447
448 select_stage ( i_select_cursor );
449
450 for c1 in documents
451 (
452 p_transaction_type => i_transaction_type
453 )
454 loop
455 process_documents
456 (
457 c1.document_id,
458 i_transaction_type,
459 i_select_cursor
460 );
461
462 /* Bug 2019253 Re-initializing the global map_id and moved the commit out of the loop */
463 g_previous_map_id := -99;
464
465 /** The Documents are processed. Save the changes now. **/
466 -- commit;
467
468 /* Bug 2422787
469 i_count := i_count + 1;
470 ec_utils.g_file_tbl := m_file_tbl_empty;
471 */
472 g_count := g_count + 1;
473
474 end loop;
475
476 commit;
477
478 ec_debug.pl(0,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',g_count);
479
480 /**
481 Close the Cursors and print the information.
482 **/
483 close_inbound;
484
485 IF dbms_sql.IS_OPEN(i_select_cursor)
486 then
487 dbms_sql.close_cursor(i_select_cursor);
488 end if;
489 if ec_debug.G_debug_level >= 2 then
490 ec_debug.POP('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
491 end if;
492 EXCEPTION
493 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
494 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
495 raise EC_UTILS.PROGRAM_EXIT;
496 WHEN EC_UTILS.PROGRAM_EXIT then
497 raise;
498 WHEN OTHERS THEN
499 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
500 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
501 ec_utils.i_ret_code := 2;
502 raise EC_UTILS.PROGRAM_EXIT;
503 end process_inbound_documents;
504
505 procedure process_inbound_documents
506 (
507 i_transaction_type IN varchar2,
508 i_tp_code IN varchar2,
509 i_status IN varchar2
510 )
511 IS
512 cursor documents
513 (
514 p_transaction_type in varchar2,
515 p_tp_code in varchar2,
516 p_status IN varchar2
520 where transaction_type = p_transaction_type
517 )is
518 select document_id
519 from ece_stage
521 and tp_code = p_tp_code
522 and status = p_status
523 and transaction_level = 1
524 and line_number = 1
525 for update of Document_Id NOWAIT;
526
527 cursor documents_fornulltp
528 (
529 p_transaction_type in varchar2,
530 p_status IN varchar2
531 )is
532 select document_id
533 from ece_stage
534 where transaction_type = p_transaction_type
535 and tp_code is null
536 and status = p_status
537 and transaction_level = 1
538 and line_number = 1
539 for update of Document_Id NOWAIT;
540
541 i_select_cursor number;
542 i_count number:=0;
543
544 begin
545 if ec_debug.G_debug_level >= 2 then
546 ec_debug.PUSH('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
547 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
548 ec_debug.pl(3,'i_tp_code',i_tp_code);
549 ec_debug.pl(3,'i_status',i_status);
550 end if;
551 ec_utils.g_stack.DELETE;
552 ec_utils.g_documents_skipped := 0;
553 ec_utils.g_insert_failed := 0;
554 g_previous_map_id := -99;
555
556 select_stage ( i_select_cursor );
557
558 if i_tp_code is not null
559 then
560 if ec_debug.G_debug_level = 3 then
561 ec_debug.pl(3,'i_tp_code',i_tp_code);
562 end if;
563 for c1 in documents
564 (
565 p_status => i_status ,
566 p_tp_code => i_tp_code ,
567 p_transaction_type => i_transaction_type
568 )
569 loop
570 process_documents
571 (
572 c1.document_id,
573 i_transaction_type,
574 i_select_cursor
575 );
576
577 /* Bug 2019253 Re-initializing the global map_id and moved the commit out of the loop */
578 g_previous_map_id := -99;
579
580 /** The Documents are processed. Save the changes now. **/
581
582 -- commit;
583
584 /* Bug 2422787
585 i_count := i_count + 1;
586 ec_utils.g_file_tbl := m_file_tbl_empty;
587 */
588 g_count := g_count + 1;
589 end loop;
590 elsif i_tp_code is null
591 then
592 if ec_debug.G_debug_level = 3 then
593 ec_debug.pl(3,'i_tp_code','NULL');
594 end if;
595 for c1 in documents_fornulltp
596 (
597 p_status => i_status ,
598 p_transaction_type => i_transaction_type
599 )
600 loop
601 process_documents
602 (
603 c1.document_id,
604 i_transaction_type,
605 i_select_cursor
606 );
607
608 /* Bug 2019253 Re-initializing the global map_id and moved the commit out of the loop */
609 g_previous_map_id := -99;
610
611 /** The Documents are processed. Save the changes now. **/
612
613 -- commit;
614
615 /* Bug 2422787
616 i_count := i_count + 1;
617 ec_utils.g_file_tbl := m_file_tbl_empty;
618 */
619 g_count := g_count + 1;
620
621 end loop;
622 else
623 ec_debug.pl(3,'iii','invalid tp code');
624 end if;
625
626 commit;
627
628 ec_debug.pl(1,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',g_count); --Bug 2422787
629
630 /**
631 Close the Cursors and print the information.
632 **/
633 close_inbound;
634
635 IF dbms_sql.IS_OPEN(i_select_cursor)
636 then
637 dbms_sql.close_cursor(i_select_cursor);
638 end if;
639 if ec_debug.G_debug_level >= 2 then
640 ec_debug.POP('ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
641 end if;
642 EXCEPTION
643 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
644 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
645 raise EC_UTILS.PROGRAM_EXIT;
646 WHEN EC_UTILS.PROGRAM_EXIT then
647 raise;
648 WHEN OTHERS THEN
649 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_INBOUND_DOCUMENTS');
650 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
651 ec_utils.i_ret_code := 2;
652 raise EC_UTILS.PROGRAM_EXIT;
653 end process_inbound_documents;
654
655 procedure run_inbound
656 (
657 i_document_id IN NUMBER,
658 i_transaction_type IN varchar2,
659 i_select_cursor IN integer
660 )
661 IS
662 l_return_status VARCHAR2(1);
663 l_msg_count NUMBER;
664 l_msg_data VARCHAR2(2000);
665 i_level ece_stage.transaction_level%TYPE;
666 i_line_number ece_stage.line_number%TYPE;
667 i_field_number number;
668 dummy number;
669 i_total_records number;
670 i_status ece_stage.status%TYPE;
671 i_map_id ece_stage.map_id%TYPE;
672 i_document_number ece_stage.document_number%TYPE;
673 i_stage_id ece_stage.stage_id%TYPE;
674 i_insert_ok BOOLEAN := FALSE;
675 i_insert BOOLEAN := FALSE;
676 i_document_failed BOOLEAN := FALSE;
677 i_level_found BOOLEAN := FALSE;
678 i_plsql_pos number;
679 i_stack_pos number;
680 m_var_found BOOLEAN := FALSE;
681 i_interface_level number;
682 i_previous_level number := 0;
686 c_rule_id number;
683 i_last_insert_level number := 0;
684
685 c_stage_id number;
687 c_interface_col_id number;
688 i_count pls_integer := 1;
689
690 CURSOR c_ignore_rule (
691 p_document_id IN NUMBER
692 ) IS
693 select stage_id,rule_id,interface_column_id
694 from ece_rule_violations
695 where document_id = p_document_id and
696 violation_level = 'COLUMN' and
697 nvl(ignore_flag,'N') = 'Y';
698
699
700 BEGIN
701 if ec_debug.G_debug_level >= 2 then
702 ec_debug.push('ECE_INBOUND.RUN_INBOUND');
703 ec_debug.pl(3,'i_document_id',i_document_id);
704 ec_debug.pl(3,'i_select_cursor',i_select_cursor);
705 end if;
706 --Set the Document Id package variable
707 ec_utils.g_document_id := i_document_id;
708
709 /**
710 Bind the Value of Document Id
711 **/
712
713 FOR i_ignore_rule in c_ignore_rule(i_document_id)
714 loop
715 g_col_rule_viol_tbl(i_count).stage_id := i_ignore_rule.stage_id;
716 g_col_rule_viol_tbl(i_count).rule_id := i_ignore_rule.rule_id;
717 g_col_rule_viol_tbl(i_count).interface_col_id := i_ignore_rule.interface_column_id;
718 i_count := i_count + 1;
719 END LOOP;
720
721 dbms_sql.bind_variable(i_select_cursor,'i_document_id',i_document_id);
722
723 dummy := dbms_sql.execute(i_select_cursor);
724 while dbms_sql.fetch_rows(i_select_cursor) > 0
725 loop
726
727 dbms_sql.column_value(i_select_cursor,1,i_stage_id);
728
729 dbms_sql.column_value(i_select_cursor,2,i_document_number);
730
731 dbms_sql.column_value(i_select_cursor,3,i_level);
732
733 dbms_sql.column_value(i_select_cursor,4,i_line_number);
734
735 dbms_sql.column_value(i_select_cursor,5,i_status);
736
737 dbms_sql.column_value(i_select_cursor,6,i_map_id);
738
739 if ec_debug.G_debug_level = 3 then
740 ec_debug.pl(3,'i_stage_id',i_stage_id);
741 ec_debug.pl(3,'i_document_number',i_document_number);
742 ec_debug.pl(3,'i_previous_level',i_previous_level);
743 ec_debug.pl(3,'i_level',i_level);
744 ec_debug.pl(3,'i_line_number',i_line_number);
745 ec_debug.pl(3,'i_status',i_status);
746 ec_debug.pl(3,'i_map_id',i_map_id);
747 ec_debug.pl(3,'g_previous_map_id',g_previous_map_id);
748 end if;
749 if (i_level = 1) and (i_map_id <> g_previous_map_id) then
750 initialize_inbound (i_transaction_type, i_map_id);
751 g_previous_map_id := i_map_id;
752 end if;
753
754 /**
755 In the case that the current external level is less than the
756 previous level and the previous data wasn't inserted into the
757 open interface table, then we have to insert the previous data
758 before continue processing the new fetched data.
759 **/
760
761 if (i_level <= i_previous_level) and NOT (i_insert) then
762 if ec_debug.G_debug_level = 3 then
763 ec_debug.pl(3,'Ready to insert into open interface table');
764 ec_debug.pl(3,'i_interface_level',i_interface_level);
765 end if;
766 /**
767 If the last_insert_level is a lower level, then we have to
768 make sure we clean up all the lower level data so that it would
769 not carry over data from previous insert.
770 **/
771
772 if (i_last_insert_level > i_previous_level) then
773 for k in ec_utils.g_ext_levels(i_previous_level+1).file_start_pos..ec_utils.g_ext_levels(i_last_insert_level).file_end_pos
774 loop
775 ec_utils.g_file_tbl(k).value := m_file_tbl_empty(k).value;
776 end loop;
777 end if;
778
779 i_last_insert_level := i_previous_level;
780
781 --Insert_into_prod_interface;
782 i_insert_ok := Insert_Into_prod_Interface
783 (
784 ec_utils.g_int_levels(i_interface_level).Cursor_handle,
785 i_interface_level
786 );
787
788 -- if Insert Failed then
789 if NOT ( i_insert_ok) then
790 ec_utils.g_ext_levels(1).Status := 'INSERT_FAILED';
791 ec_utils.g_ext_levels(i_level).Status := ec_utils.g_ext_levels(1).status;
792 ec_utils.g_insert_failed := ec_utils.g_insert_failed + 1;
793 ec_debug.pl(1,'EC','ECE_INSERT_SKIPPED','DOCUMENT_ID',i_document_id);
794
795 /**
796 Exit the processing for the Document
797 **/
798 exit;
799
800 end if; --- Insert Check
801 end if;
802
803 /**
804 Update the Level Info table for Latest Document Number , Stage Id and Level
805 **/
806
807 ec_utils.g_ext_levels(i_level).Stage_Id := i_stage_id;
808 ec_utils.g_ext_levels(i_level).Document_Id := i_Document_Id;
809 ec_utils.g_ext_levels(i_level).Document_Number := i_Document_Number;
810 ec_utils.g_ext_levels(i_level).Line_Number := i_Line_Number;
811 ec_utils.g_ext_levels(i_level).Status := i_Status;
812
813 --Bug 2164672
814 if ec_utils.g_ext_levels(i_level).status = 'LOSSY_CONVERSION' then
815 ec_utils.g_ext_levels(i_level).Status := 'SKIP_DOCUMENT';
816 ec_debug.pl(0,'This Line has Lossy Conversion Exception ');
817 goto skip_document;
818 end if;
819
820 /**
821 Update Global variable to hold the Current level of the Record.
822 **/
823 ec_utils.g_current_level := i_level;
827 Populate Transaction Attribute table for Error Handling.
824 i_previous_level := i_level;
825
826 /**
828 **/
829 if i_level = 1
830 then
831 ece_flatfile_pvt.t_tran_attribute_tbl(1).value := i_Document_Number;
832 end if;
833
834 /**
835 Initialize values for the Level , copy the values from empty table.
836 **/
837 for k in ec_utils.g_ext_levels(i_level).file_start_pos..ec_utils.g_ext_levels(i_level).file_end_pos
838 loop
839 ec_utils.g_file_tbl(k).value := m_file_tbl_empty(k).value;
840 end loop;
841
842 /**
843 Extracting a given Column from Staging table.
844 From the PL/SQL table , we are concerned about only those fields which
845 are mapped to the Staging Columns in the Stage Table. The Data from the
846 Stage table is extracted only for the mapped fields.
847 e.g. select statement build for Extract is
848 select Stage_Id,Document_Number,transaction_level,Line_number,Status,map_id,
849 Field1,Field2,Field3........Field500 from ece_stage;
850 To extract Field3 , first Find out the relative position of the Column
851 in the Select Statement which is 6 + 3 ( 3 for Field3 , 5 for Field5).
852 Pass this to the DBMS_SQL call to get the Column Value.
853 **/
854 if ec_debug.G_debug_level = 3 then
855 ec_debug.pl(3,'EC','ECE_FIELDS_EXTRACTED_STAGING',null);
856 end if;
857 for i in ec_utils.g_ext_levels(i_level).file_start_pos..ec_utils.g_ext_levels(i_level).file_end_pos
858 loop
859 if ec_utils.g_file_tbl(i).Staging_Column is not null
860 then
861 /* Bug 2500898
862 i_field_number := to_number( substrb(
863 ec_utils.g_file_tbl(i).Staging_Column,
864 6,
865 length(ec_utils.g_file_tbl(i).Staging_Column)-5));
866
867 dbms_sql.column_value(
868 i_select_cursor,
869 i_field_number+6,
870 ec_utils.g_file_tbl(i).value
871 );
872 */
873
874 dbms_sql.column_value(
875 i_select_cursor,
876 ec_utils.g_file_tbl(i).staging_column_no+6,
877 ec_utils.g_file_tbl(i).value
878 );
879
880 -- Check the value extracted from the Staging table. If null then assign the default
881 -- values from the backup PL/SQL table from stage 10.
882 -- Bug 2708573/2808880
883 if ec_utils.g_file_tbl(i).value is null
884 then
885 ec_utils.g_file_tbl(i).value := m_file_tbl_empty(i).value;
886 end if;
887
888 if ec_debug.G_debug_level = 3 then --bug 2500898
889 if ec_utils.g_file_tbl(i).value is not null
890 then
891 ec_debug.pl(3,'i_Interface_Column_Name',ec_utils.g_file_tbl(i).Interface_Column_Name);
892 ec_debug.pl(3,'i_field_name',ec_utils.g_file_tbl(i).Staging_Column);
893 ec_debug.pl(3,'i_field_value',ec_utils.g_file_tbl(i).value);
894 end if;
895 end if;
896
897 end if; --- Staging Column mapped to the Interface Column
898 end loop; -- end loop for Column values
899
900 if stage_20_flag = 'Y' then --Bug 2500898
901 /**
902 Get Stage Data for Stage = 20
903 **/
904 ec_utils.execute_stage_data ( 20, i_level);
905 end if;
906
907 if i_level = 1
908 then
909 m_var_found := ec_utils.find_variable
910 (
911 0,
912 'P_ADDRESS_TYPE',
913 i_stack_pos,
914 i_plsql_pos
915 );
916 if not (m_var_found)
917 then
918 ec_debug.pl(0,'EC','ECE_VARIABLE_NOT_ON_STACK','VARIABLE_NAME','P_ADDRESS_TYPE');
919 ec_utils.i_ret_code :=2;
920 raise ec_utils.program_exit;
921 end if;
922
923 ECE_RULES_PKG.Validate_Process_Rules
924 (
925 1.0,
926 NULL,
927 null,
928 null,
929 null,
930 l_return_status,
931 l_msg_count,
932 l_msg_data,
933 ec_utils.g_transaction_type,
934 ec_utils.g_stack(i_stack_pos).variable_value,
935 i_stage_id,
936 i_document_id,
937 i_document_number,
938 i_level,
939 i_map_id,
940 ec_utils.g_file_tbl
941 );
942
943 --Check the Status of the Process Rule Exception API
944 --and take appropriate action.
945 if l_return_status = fnd_api.g_ret_sts_success then
946 if ec_utils.g_ext_levels(i_level).Status = 'SKIP_DOCUMENT'
947 or ec_utils.g_ext_levels(i_level).Status = 'ABORT'
948 then
949 goto skip_document;
950 end if;
951 elsif ( l_return_status = FND_API.G_RET_STS_ERROR
952 OR l_return_status is NULL
953 OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
954 ec_utils.i_ret_code := 2;
955 RAISE EC_UTILS.PROGRAM_EXIT;
956 END IF;
957
958 end if; --- For Header execute Process Rules
959
960 if stage_30_flag = 'Y' then --Bug 2500898
961 --Get Stage Data for Stage = 30
962 ec_utils.execute_stage_data ( 30, i_level);
963 end if;
964
965 ec_code_conversion_pvt.populate_plsql_tbl_with_intval
966 (
967 p_api_version_number => 1.0,
968 p_return_status => l_return_status,
969 p_msg_count => l_msg_count,
970 p_msg_data => l_msg_data,
971 p_apps_tbl => ec_utils.g_file_tbl,
975 /**
972 p_level => i_level
973 );
974
976 Check the Status of the Code Conversion API
977 and take appropriate action.
978 **/
979 IF ( l_return_status = FND_API.G_RET_STS_ERROR OR l_return_status is NULL
980 OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
981 ec_utils.i_ret_code := 2;
982 RAISE EC_UTILS.PROGRAM_EXIT;
983 END IF;
984
985 if stage_40_flag = 'Y' then --Bug 2500898
986 --Get Stage Data for Stage = 40
987 ec_utils.execute_stage_data ( 40, i_level);
988 end if;
989
990 --Perform Column Exception Processing
991 ECE_RULES_PKG.Validate_Column_Rules
992 (
993 1.0,
994 NULL,
995 null,
996 null,
997 null,
998 l_return_status,
999 l_msg_count,
1000 l_msg_data,
1001 ec_utils.g_transaction_type,
1002 i_stage_id,
1003 i_document_id,
1004 i_document_number,
1005 i_level,
1006 ec_utils.g_file_tbl
1007 );
1008
1009 ---
1010 ---Check the Status of the Column Rule Exception API
1011 ---and take appropriate action.
1012 ---
1013 if l_return_status = fnd_api.g_ret_sts_success then
1014 if ec_utils.g_ext_levels(i_level).Status = 'SKIP_DOCUMENT'
1015 or ec_utils.g_ext_levels(i_level).Status = 'ABORT'
1016 then
1017 goto skip_document;
1018 end if;
1019 elsif ( l_return_status = FND_API.G_RET_STS_ERROR OR l_return_status is NULL
1020 OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
1021 ec_utils.i_ret_code := 2;
1022 RAISE EC_UTILS.PROGRAM_EXIT;
1023 END IF;
1024
1025 g_col_rule_viol_tbl.delete;
1026
1027 if stage_50_flag = 'Y' then --Bug 2500898
1028 --Get Stage Data for Stage = 50
1029 ec_utils.execute_stage_data (50, i_level);
1030 end if;
1031
1032
1033 <<skip_document>>
1034
1035 if ec_utils.g_ext_levels(i_level).Status = 'ABORT' or
1036 ec_utils.g_ext_levels(i_level).Status = 'SKIP_DOCUMENT'
1037 then
1038 /**
1039 If Abort or Skip Document, then Mark the Control Record ( 1st Record )
1040 or the header as Abort. This will be checked in the calling program
1041 to rollback and abort the program.
1042 **/
1043 ec_utils.g_ext_levels(1).Status := ec_utils.g_ext_levels(i_level).Status;
1044
1045 /**
1046 Keep Track of how many documents have been skipped in this
1047 run of the transaction.
1048 **/
1049 if ec_utils.g_ext_levels(i_level).Status = 'SKIP_DOCUMENT'
1050 then
1051 ec_debug.pl(1,'EC','ECE_DOCUMENT_SKIPPED','DOCUMENT_ID',i_document_id);
1052 ec_utils.g_documents_skipped := ec_utils.g_documents_skipped + 1;
1053 end if;
1054
1055 /**
1056 Exit the processing for the Document
1057 **/
1058 exit;
1059
1060 elsif
1061 /**
1062 The Exception processing does not update the Status of a record
1063 if there are no Process rules or Column Rules defined .The Status
1064 remains New, or Re-Process.
1065 **/
1066 (
1067 ec_utils.g_ext_levels(i_level).Status = 'INSERT'
1068 or ec_utils.g_ext_levels(i_level).Status = 'NEW'
1069 or ec_utils.g_ext_levels(i_level).Status = 'RE_PROCESS'
1070 )
1071 then
1072 /**
1073 This is the new flexible hierarchy feature. It loops thru the
1074 g_int_ext_levels and make sure all the data is completed
1075 before it writes to the open interface table.
1076 **/
1077
1078 for i in 1..ec_utils.g_int_ext_levels.COUNT
1079 loop
1080 if ec_utils.g_int_ext_levels(i).external_level = i_level then
1081 i_interface_level := ec_utils.g_int_ext_levels(i).interface_level;
1082 i_insert := FALSE;
1083 ec_debug.pl (3, 'i', i);
1084 if i < ec_utils.g_int_ext_levels.COUNT then
1085 if ec_utils.g_int_ext_levels(i+1).interface_level <>
1086 ec_utils.g_int_ext_levels(i).interface_level then
1087 i_insert := TRUE;
1088 end if;
1089 else
1090 i_insert := TRUE;
1091 end if;
1092
1093 if i_insert then
1094 i_last_insert_level := i_level;
1095 if ec_debug.G_debug_level = 3 then
1096 ec_debug.pl(3,'Ready to insert into open interface table');
1097 ec_debug.pl(3,'i_interface_level',i_interface_level);
1098 end if;
1099 --Insert_into_prod_interface;
1100 i_insert_ok := Insert_Into_prod_Interface
1101 (
1102 ec_utils.g_int_levels(i_interface_level).Cursor_handle,
1103 i_interface_level
1104 );
1105
1106 -- if Insert Failed then
1107 if NOT ( i_insert_ok)
1108 then
1109 ec_utils.g_ext_levels(1).Status := 'INSERT_FAILED';
1110 ec_utils.g_ext_levels(i_level).Status := ec_utils.g_ext_levels(1).status;
1111 ec_utils.g_insert_failed := ec_utils.g_insert_failed + 1;
1112 ec_debug.pl(1,'EC','ECE_INSERT_SKIPPED','DOCUMENT_ID',i_document_id);
1113
1114 /**
1115 Exit the processing for the Document
1116 **/
1117 exit;
1118
1119 end if; --- Insert Check
1120 end if; -- i_insert
1121 end if;
1122 end loop;
1123 else
1124 /**
1125 Invalid Status , Skipping the Document.
1126 **/
1130 Keep Track of how many documents have been skipped in this
1127 ec_utils.g_ext_levels(1).Status := 'SKIP_DOCUMENT';
1128
1129 /**
1131 run of the transaction.
1132 **/
1133 ec_debug.pl(1,'EC','ECE_DOCUMENT_SKIPPED','DOCUMENT_ID',i_document_id);
1134 ec_utils.g_documents_skipped := ec_utils.g_documents_skipped + 1;
1135
1136 /**
1137 Exit the processing for the Document
1138 **/
1139 exit;
1140 end if;
1141
1142 if (i_insert) and not (i_insert_ok) then
1143 -- Exit the processing for the document.
1144 exit;
1145 end if;
1146
1147 end loop; --- ( End of Fetch )
1148
1149 if (ec_utils.g_ext_levels(i_level).Status = 'INSERT'
1150 or ec_utils.g_ext_levels(i_level).Status = 'NEW'
1151 or ec_utils.g_ext_levels(i_level).Status = 'RE_PROCESS') and
1152 Not (i_insert) then
1153
1154 if (i_last_insert_level > i_level) then
1155 for k in ec_utils.g_ext_levels(i_level+1).file_start_pos..ec_utils.g_ext_levels(i_last_insert_level).file_end_pos
1156 loop
1157 ec_utils.g_file_tbl(k).value := m_file_tbl_empty(k).value;
1158 end loop;
1159 end if;
1160
1161 if ec_debug.G_debug_level = 3 then
1162 ec_debug.pl(3,'Ready to insert into open interface table');
1163 ec_debug.pl(3,'i_interface_level',i_interface_level);
1164 end if ;
1165
1166 --Insert_into_prod_interface;
1167 i_insert_ok := Insert_Into_prod_Interface
1168 (
1169 ec_utils.g_int_levels(i_interface_level).Cursor_handle,
1170 i_interface_level
1171 );
1172
1173 -- if Insert Failed then
1174 if NOT ( i_insert_ok) then
1175 ec_utils.g_ext_levels(1).Status := 'INSERT_FAILED';
1176 ec_utils.g_ext_levels(i_level).Status := ec_utils.g_ext_levels(1).status;
1177 ec_utils.g_insert_failed := ec_utils.g_insert_failed + 1;
1178 ec_debug.pl(1,'EC','ECE_INSERT_SKIPPED','DOCUMENT_ID',i_document_id);
1179 end if;
1180
1181 end if;
1182
1183 i_total_records := dbms_sql.last_row_count;
1184 if ec_debug.G_debug_level >= 1 then
1185 ec_debug.pl(1,'EC','ECE_TOTAL_RECORDS_PROCESSED','TOTAL_RECORDS',i_total_records,'DOCUMENT_ID',i_document_id);
1186
1187 ec_debug.POP('ECE_INBOUND.RUN_INBOUND');
1188 end if;
1189
1190 EXCEPTION
1191 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
1192 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
1193 ec_utils.i_ret_code :=0;
1194 raise EC_UTILS.PROGRAM_EXIT;
1195 WHEN EC_UTILS.PROGRAM_EXIT then
1196 raise;
1197 WHEN OTHERS THEN
1198 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.RUN_INBOUND');
1199 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1200 ec_utils.i_ret_code := 2;
1201 raise EC_UTILS.PROGRAM_EXIT;
1202 END run_inbound;
1203
1204 procedure update_document_status
1205 is
1206 BEGIN
1207
1208 if ec_debug.G_debug_level >= 2 then
1209 ec_debug.push('ECE_INBOUND.UPDATE_DOCUMENT_STATUS');
1210 end if;
1211 /**
1212 Update Header record in the Staging Table.
1213 **/
1214 update ece_stage
1215 set status = ec_utils.g_ext_levels(1).Status
1216 where document_id = ec_utils.g_ext_levels(1).Document_id
1217 and transaction_level = 1
1218 and line_number = 1;
1219
1220 if sql%notfound
1221 then
1222 ec_debug.pl(0,'EC','ECE_STATUS_UPDATE_FAILED','DOCUMENT_ID',
1223 ec_utils.g_ext_levels(ec_utils.g_current_level).Document_Id);
1224 ec_utils.i_ret_code := 2;
1225 raise EC_UTILS.PROGRAM_EXIT;
1226 end if;
1227
1228 if ec_utils.g_current_Level > 1
1229 then
1230
1231 /**
1232 Need to update all the previous processed line to have status
1233 'INSERT' so that it will show the 'GREEN' icon in View Staged
1234 Document form.
1235 **/
1236
1237 update ece_stage
1238 set status = 'INSERT'
1239 where (stage_id > ec_utils.g_ext_levels(1).stage_id) and
1240 (stage_id < ec_utils.g_ext_levels(ec_utils.g_current_level).stage_id);
1241
1242 /**
1243 Update the Status of the Line where error encountered in
1244 the Staging Table.
1245 **/
1246 update ece_stage
1247 set status = ec_utils.g_ext_levels(ec_utils.g_current_level).Status
1248 where stage_id = ec_utils.g_ext_levels(ec_utils.g_current_level).stage_id;
1249
1250 if sql%notfound
1251 then
1252 ec_debug.pl(0,'EC','ECE_STATUS_UPDATE_FAILED','DOCUMENT_ID',
1253 ec_utils.g_ext_levels(ec_utils.g_current_level).Document_Id);
1254 ec_utils.i_ret_code := 2;
1255 raise EC_UTILS.PROGRAM_EXIT;
1256 end if;
1257
1258 end if;
1259 if ec_debug.G_debug_level >= 2 then
1260 ec_debug.pop('ECE_INBOUND.UPDATE_DOCUMENT_STATUS');
1261 end if;
1262 EXCEPTION
1263 WHEN EC_UTILS.PROGRAM_EXIT then
1264 raise;
1265 WHEN OTHERS THEN
1266 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.UPDATE_DOCUMENT_STATUS');
1267 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1268 ec_utils.i_ret_code := 2;
1269 raise EC_UTILS.PROGRAM_EXIT;
1270 END update_document_status;
1271
1272 procedure initialize_inbound
1273 (
1274 i_transaction_type IN varchar2,
1275 i_map_id IN number
1276 )
1277 is
1278 begin
1279 if ec_debug.G_debug_level >= 2 then
1283
1280 ec_debug.push('ECE_INBOUND.INITIALIZE_INBOUND');
1281 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
1282 end if;
1284 if g_count=0 then -- Bug 2422787
1285
1286 /**
1287 Initialize the PL/SQL tables.Stack Table will be initialized
1288 from where it is called.
1289 **/
1290 ec_utils.g_file_tbl.DELETE;
1291 ec_utils.g_int_levels.DELETE;
1292 ec_utils.g_ext_levels.DELETE;
1293 ec_utils.g_int_ext_levels.DELETE;
1294 ec_utils.g_stack_pointer.DELETE;
1295 ec_utils.g_stage_data.DELETE;
1296 /* Bug 2019253 cleared the global stack table. */
1297 ec_utils.g_stack.DELETE;
1298 ec_utils.g_direction := 'I';
1299 ec_utils.g_transaction_type := i_transaction_type;
1300 ec_utils.g_map_id := i_map_id;
1301 ece_rules_pkg.g_rule_violation_tbl.DELETE;
1302 ece_flatfile_pvt.get_tran_attributes(i_transaction_type);
1303
1304 /**
1305 Get all the Dynamic Inbound Staging data. The data is retrieved from the
1306 table ( ece_tran_stage_data ) and kept in the Local Pl/SQL table. Since the
1307 data is in PL/SQL memory , no further lookups in the table are required.
1308 This helps in improving the perfromance , as un-necessary selects are saved.
1309 **/
1310 ec_utils.get_tran_stage_data ( i_transaction_type, i_map_id);
1311
1312 ec_execution_utils.load_mappings ( i_transaction_type, i_map_id);
1313
1314 ec_utils.sort_stage_data;
1315
1316 /**
1317 Execute the Dynamic Inbound Stage data for Stage = 10.
1318 After Level 0 execution , the Stack Pointers are built.
1319 **/
1320
1321 ec_utils.i_stage_data := ec_utils.i_tmp_stage_data; -- 2708573
1322
1323 for i in 0..ec_utils.g_ext_levels.COUNT
1324 loop
1325 ec_utils.execute_stage_data (10,i);
1326 end loop;
1327
1328 ec_utils.i_stage_data := ec_utils.i_tmp2_stage_data; -- 2708573
1329
1330 /** Bug 2422787
1331 Save the PL/SQL table with default values. This will be used
1332 by all the documents.
1333 **/
1334 m_file_tbl_empty := ec_utils.g_file_tbl;
1335 -- m_orig_int_levels := ec_utils.g_int_levels;
1336 m_orig_ext_levels := ec_utils.g_ext_levels;
1337 -- m_orig_int_ext_levels := ec_utils.g_int_ext_levels;
1338 m_orig_stage_data := ec_utils.g_stage_data;
1339 -- m_orig_stack_pointer := ec_utils.g_stack_pointer;
1340 m_tmp1_stage_data := ec_utils.i_tmp_stage_data;
1341 m_tmp2_stage_data := ec_utils.i_tmp2_stage_data;
1342 m_tmp3_stage_data := ec_utils.i_stage_data;
1343
1344 -- Searching the staging table to ensure if the stage is present
1345 -- in the Seed Data. This is for avoiding the execution of the
1346 -- staging procedure unnecessarly. Bug 2500898
1347 for k in 1..ec_utils.g_stage_data.COUNT
1348 loop
1349 if ec_utils.g_stage_data(k).stage=20 then
1350 stage_20_flag:='Y';
1351 elsif ec_utils.g_stage_data(k).stage=30 then
1352 stage_30_flag:='Y';
1353 elsif ec_utils.g_stage_data(k).stage=40 then
1354 stage_40_flag:='Y';
1355 elsif ec_utils.g_stage_data(k).stage=50 then
1356 stage_50_flag:='Y';
1357 end if;
1358 end loop;
1359
1360 -- Extracting the no. from the Staging_column as this will
1361 -- be used to determine the position of staging column in
1362 -- the dynamic select stmt on ece_stage table. Bug 2500898
1363 for k in 1..ec_utils.g_file_tbl.COUNT
1364 loop
1365 ec_utils.g_file_tbl(k).staging_column_no :=
1366 to_number( substrb(
1367 ec_utils.g_file_tbl(k).Staging_Column,
1368 6,
1369 length(ec_utils.g_file_tbl(k).Staging_Column)-5));
1370 end loop;
1371
1372 else
1373
1374 for i in 1..ec_utils.g_file_tbl.COUNT
1375 loop
1376 ec_utils.g_file_tbl(i).value :=NULL;
1377 ec_utils.g_file_tbl(i).ext_val1 :=NULL;
1378 ec_utils.g_file_tbl(i).ext_val2 :=NULL;
1379 ec_utils.g_file_tbl(i).ext_val3 :=NULL;
1380 ec_utils.g_file_tbl(i).ext_val4 :=NULL;
1381 ec_utils.g_file_tbl(i).ext_val5 :=NULL;
1382 end loop;
1383
1384 ec_utils.g_ext_levels := m_orig_ext_levels;
1385 ec_utils.g_stage_data := m_orig_stage_data;
1386 ec_utils.i_tmp_stage_data:= m_tmp1_stage_data;
1387 ec_utils.i_tmp2_stage_data:= m_tmp2_stage_data;
1388 ec_utils.i_stage_data := m_tmp3_stage_data;
1389
1390 -- Bug 2708573
1391 -- Initialize the g_stack instead of performing a delete.
1392 -- ec_utils.g_stack.DELETE;
1393 for i in 1..ec_utils.g_stack.COUNT
1394 loop
1395 ec_utils.g_stack(i).variable_value := NULL;
1396 end loop;
1397
1398 ec_utils.g_stack_pointer.DELETE;
1399 ece_rules_pkg.g_rule_violation_tbl.DELETE;
1400
1401 ec_utils.g_stack_pointer(0).start_pos :=1;
1402 ec_utils.g_stack_pointer(0).end_pos :=0;
1403 for i in 1..ec_utils.g_ext_levels.COUNT
1404 loop
1405 ec_utils.g_stack_pointer(i).start_pos :=1;
1409 /**
1406 ec_utils.g_stack_pointer(i).end_pos :=0;
1407 end loop;
1408
1410 Execute the Dynamic Inbound Stage data for Stage = 10.
1411 After Level 0 execution , the Stack Pointers are built.
1412 **/
1413
1414 ec_utils.i_stage_data := ec_utils.i_tmp_stage_data; -- 2708573
1415
1416 for i in 0..ec_utils.g_ext_levels.COUNT
1417 loop
1418 ec_utils.execute_stage_data (10,i);
1419 end loop;
1420
1421 ec_utils.i_stage_data := ec_utils.i_tmp2_stage_data; -- 2708573
1422
1423 end if;
1424
1425
1426 if ec_debug.G_debug_level>= 2 then
1427 ec_debug.pop('ECE_INBOUND.INITIALIZE_INBOUND');
1428 end if;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.INITIALIZE_INBOUND');
1432 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1433 ec_utils.i_ret_code := 2;
1434 raise EC_UTILS.PROGRAM_EXIT;
1435 end initialize_inbound;
1436
1437 procedure close_inbound
1438 is
1439 begin
1440 if ec_debug.G_debug_level >= 2 then
1441 ec_debug.push('ECE_INBOUND.CLOSE_INBOUND');
1442 end if;
1443
1444 g_count:=0; --Bug 2422787
1445
1446 if ec_utils.g_documents_skipped > 0
1447 then
1448 ec_debug.pl(0,'EC','ECE_TOTAL_SKIPPED','SKIPPED',ec_utils.g_documents_skipped);
1449 ec_utils.i_ret_code :=1;
1450 end if;
1451
1452 if ec_utils.g_insert_failed > 0
1453 then
1454 ec_debug.pl(0,'EC','ECE_TOTAL_FAILED','FAILED',ec_utils.g_insert_failed);
1455 ec_utils.i_ret_code :=1;
1456 end if;
1457
1458
1459 /**
1460 Close all open Cursors.
1461 The Cursors for the Insert into Open Interface table are not closed
1462 in the Insert_Into_Prod_Interface function call. Since the Cursor
1463 handles are maintained in the I_LEVEL_INFO PL/SQL table ,
1464 Cursors for the all the Level are closed using these Cursor handles.
1465 **/
1466 For i in 1..ec_utils.g_ext_levels.COUNT
1467 loop
1468 IF dbms_sql.IS_OPEN(ec_utils.g_ext_levels(i).Cursor_Handle)
1469 then
1470 dbms_sql.Close_cursor(ec_utils.g_ext_levels(i).Cursor_Handle);
1471 end if;
1472 end loop;
1473
1474 if ec_debug.G_debug_level >= 2 then
1475 ec_debug.pop('ECE_INBOUND.CLOSE_INBOUND');
1476 end if;
1477 EXCEPTION
1478 WHEN OTHERS THEN
1479 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.CLOSE_INBOUND');
1480 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1481 ec_utils.i_ret_code := 2;
1482 raise EC_UTILS.PROGRAM_EXIT;
1483 end close_inbound;
1484
1485 procedure Insert_into_violations
1486 (
1487 i_document_id IN number
1488 )
1489 is
1490 begin
1491 if ec_debug.G_debug_level >= 2 then
1492 ec_debug.push('ECE_INBOUND.INSERT_INTO_VIOLATIONS');
1493 ec_debug.pl(3,'i_document_id',i_document_id);
1494 end if;
1495 /**
1496 Delete the Old violations for this Document
1497 **/
1498 Delete from ece_rule_violations
1499 where document_id = i_document_id
1500 and ignore_flag = 'N';
1501
1502
1503 for i in 1..ece_rules_pkg.g_rule_violation_tbl.COUNT
1504 loop
1505 INSERT into ece_rule_violations
1506 (
1507 violation_id,
1508 document_id,
1509 stage_id,
1510 interface_column_id,
1511 rule_id,
1512 transaction_type,
1513 document_number,
1514 violation_level,
1515 ignore_flag,
1516 message_text,
1517 creation_date,
1518 created_by,
1519 last_update_date,
1520 last_updated_by,
1521 last_update_login
1522 )
1523 VALUES
1524 (
1525 ece_rules_pkg.g_rule_violation_tbl(i).violation_id,
1526 ece_rules_pkg.g_rule_violation_tbl(i).document_id,
1527 ece_rules_pkg.g_rule_violation_tbl(i).stage_id,
1528 ece_rules_pkg.g_rule_violation_tbl(i).interface_column_id,
1529 ece_rules_pkg.g_rule_violation_tbl(i).rule_id,
1530 ece_rules_pkg.g_rule_violation_tbl(i).transaction_type,
1531 ece_rules_pkg.g_rule_violation_tbl(i).document_number,
1532 ece_rules_pkg.g_rule_violation_tbl(i).violation_level,
1533 ece_rules_pkg.g_rule_violation_tbl(i).ignore_flag,
1534 ece_rules_pkg.g_rule_violation_tbl(i).message_text,
1535 sysdate,
1536 fnd_global.user_id,
1537 sysdate,
1538 fnd_global.user_id,
1539 fnd_global.login_id
1540 );
1541 end loop;
1542 /** Clean the PL/SQL table for next set of violations.
1543 **/
1544 ece_rules_pkg.g_rule_violation_tbl.DELETE;
1545 if ec_debug.G_debug_level >= 2 then
1546 ec_debug.pop('ECE_INBOUND.INSERT_INTO_VIOLATIONS');
1547 end if;
1548 EXCEPTION
1549 WHEN OTHERS THEN
1550 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.INSERT_INTO_VIOLATIONS');
1551 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1552 ec_utils.i_ret_code := 2;
1553 raise EC_UTILS.PROGRAM_EXIT;
1554 end Insert_Into_Violations;
1555
1556 procedure process_for_reqid
1557 (
1558 errbuf OUT NOCOPY varchar2,
1559 retcode OUT NOCOPY varchar2,
1560 i_transaction_type IN varchar2,
1561 i_reqid IN number,
1565 begin
1562 i_debug_mode in number
1563 )
1564 is
1566 ec_debug.enable_debug(i_debug_mode);
1567 if ec_debug.G_debug_level >= 2 then
1568 ec_debug.push('ECE_INBOUND.PROCESS_FOR_REQID');
1569 ec_debug.pl(0,'EC','ECE_START_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1570 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
1571 ec_debug.pl(3,'i_reqid',i_reqid);
1572 ec_debug.pl(3,'i_debug_mode',i_debug_mode);
1573 end if;
1574 ece_inbound.process_inbound_documents
1575 (
1576 i_transaction_type => i_transaction_type,
1577 i_run_id => i_reqid
1578 );
1579 retcode := ec_utils.i_ret_code;
1580
1581 if ec_debug.G_debug_level >= 2 then
1582 ec_debug.pl(3,'errbuf',errbuf);
1583 ec_debug.pl(3,'retcode',retcode);
1584 ec_debug.pl(2,'EC','ECE_END_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1585 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_REQID');
1586 end if;
1587 ec_debug.disable_debug;
1588 EXCEPTION
1589 WHEN EC_UTILS.PROGRAM_EXIT then
1590 ece_flatfile_pvt.print_attributes;
1591 retcode := ec_utils.i_ret_code;
1592 ec_debug.disable_debug;
1593 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_REQID');
1594 WHEN OTHERS THEN
1595 retcode :=2;
1596 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_FOR_REQID');
1597 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1598 ece_flatfile_pvt.print_attributes;
1599 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_REQID');
1600 ec_debug.disable_debug;
1601 end process_for_reqid;
1602
1603 procedure process_for_document_id
1604 (
1605 errbuf OUT NOCOPY varchar2,
1606 retcode OUT NOCOPY varchar2,
1607 i_transaction_type IN varchar2,
1608 i_document_id IN number,
1609 i_debug_mode IN number
1610 )
1611 is
1612 begin
1613 ec_debug.enable_debug(i_debug_mode);
1614 if ec_debug.G_debug_level >= 2 then
1615 ec_debug.push('ECE_INBOUND.PROCESS_FOR_DOCUMENT_ID');
1616 ec_debug.pl(2,'EC','ECE_START_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1617 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
1618 ec_debug.pl(3,'i_document_id',i_document_id);
1619 ec_debug.pl(3,'i_debug_mode',i_debug_mode);
1620 end if;
1621 ece_inbound.process_inbound_documents
1622 (
1623 i_transaction_type => i_transaction_type,
1624 i_document_id => i_document_id
1625 );
1626 retcode := ec_utils.i_ret_code;
1627 if ec_debug.G_debug_level >= 2 then
1628 ec_debug.pl(3,'errbuf',errbuf);
1629 ec_debug.pl(3,'retcode',retcode);
1630 ec_debug.pl(2,'EC','ECE_END_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1631 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_DOCUMENT_ID');
1632 end if;
1633 ec_debug.disable_debug;
1634 EXCEPTION
1635 WHEN EC_UTILS.PROGRAM_EXIT then
1636 retcode := ec_utils.i_ret_code;
1637 ece_flatfile_pvt.print_attributes;
1638 ec_debug.disable_debug;
1639 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_DOCUMENT_ID');
1640 WHEN OTHERS THEN
1641 retcode :=2;
1642 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_FOR_DOCUMENT_ID');
1643 ec_debug.pl(1,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1644 ece_flatfile_pvt.print_attributes;
1645 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_DOCUMENT_ID');
1646 ec_debug.disable_debug;
1647 end process_for_document_id;
1648
1649 procedure process_for_status
1650 (
1651 errbuf OUT NOCOPY varchar2,
1652 retcode OUT NOCOPY varchar2,
1653 i_transaction_type IN varchar2,
1654 i_status IN varchar2,
1655 i_debug_mode in number
1656 )
1657 is
1658 begin
1659 ec_debug.enable_debug(i_debug_mode);
1660 if ec_debug.G_debug_level >= 2 then
1661 ec_debug.push('ECE_INBOUND.PROCESS_FOR_STATUS');
1662 ec_debug.pl(2,'EC','ECE_START_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1663 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
1664 ec_debug.pl(3,'i_status',i_status);
1665 ec_debug.pl(3,'i_debug_mode',i_debug_mode);
1666 end if;
1667
1668 ece_inbound.process_inbound_documents
1669 (
1670 i_transaction_type => i_transaction_type,
1671 i_status => i_status
1672 );
1673 retcode := ec_utils.i_ret_code;
1674 if ec_debug.G_debug_level >= 2 then
1675 ec_debug.pl(3,'errbuf',errbuf);
1676 ec_debug.pl(3,'retcode',retcode);
1677 ec_debug.pl(2,'EC','ECE_END_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1678 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_STATUS');
1679 end if;
1680 ec_debug.disable_debug;
1681 EXCEPTION
1682 WHEN EC_UTILS.PROGRAM_EXIT then
1683 retcode := ec_utils.i_ret_code;
1684 ece_flatfile_pvt.print_attributes;
1685 ec_debug.disable_debug;
1686 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_STATUS');
1687 WHEN OTHERS THEN
1688 retcode :=2;
1689 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_FOR_STATUS');
1690 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1691 ece_flatfile_pvt.print_attributes;
1692 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_STATUS');
1693 ec_debug.disable_debug;
1694 end process_for_status;
1695
1696 procedure process_for_transaction
1697 (
1698 errbuf OUT NOCOPY varchar2,
1699 retcode OUT NOCOPY varchar2,
1700 i_transaction_type IN varchar2,
1701 i_debug_mode in number
1702 )
1703 is
1704 begin
1708 ec_debug.pl(2,'EC','ECE_START_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1705 ec_debug.enable_debug(i_debug_mode);
1706 if ec_debug.G_debug_level >= 2 then
1707 ec_debug.push('ECE_INBOUND.PROCESS_FOR_TRANSACTION');
1709 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
1710 ec_debug.pl(3,'i_debug_mode',i_debug_mode);
1711 end if;
1712 ece_inbound.process_inbound_documents
1713 (
1714 i_transaction_type => i_transaction_type
1715 );
1716 retcode := ec_utils.i_ret_code;
1717
1718 if ec_debug.G_debug_level >= 2 then
1719 ec_debug.pl(3,'errbuf',errbuf);
1720 ec_debug.pl(3,'retcode',retcode);
1721 ec_debug.pl(2,'EC','ECE_END_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1722 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_TRANSACTION');
1723 end if;
1724 ec_debug.disable_debug;
1725 EXCEPTION
1726 WHEN EC_UTILS.PROGRAM_EXIT then
1727 retcode := ec_utils.i_ret_code;
1728 ece_flatfile_pvt.print_attributes;
1729 ec_debug.disable_debug;
1730 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_TRANSACTION');
1731 WHEN OTHERS THEN
1732 retcode :=2;
1733 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_FOR_TRANSACTION');
1734 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1735 ece_flatfile_pvt.print_attributes;
1736 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_TRANSACTION');
1737 ec_debug.disable_debug;
1738 end process_for_transaction;
1739
1740 procedure process_for_tpstatus
1741 (
1742 errbuf OUT NOCOPY varchar2,
1743 retcode OUT NOCOPY varchar2,
1744 i_transaction_type IN varchar2,
1745 i_tp_code IN varchar2,
1746 i_status IN varchar2,
1747 i_debug_mode in number
1748 )
1749 is
1750 begin
1751 ec_debug.enable_debug(i_debug_mode);
1752 if ec_debug.G_debug_level >= 2 then
1753 ec_debug.push('ECE_INBOUND.PROCESS_FOR_TPSTATUS');
1754 ec_debug.pl(2,'EC','ECE_START_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1755 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
1756 ec_debug.pl(3,'i_tp_code',i_tp_code);
1757 ec_debug.pl(3,'i_status',i_status);
1758 ec_debug.pl(3,'i_debug_mode',i_debug_mode);
1759 end if;
1760 ece_inbound.process_inbound_documents
1761 (
1762 i_transaction_type => i_transaction_type,
1763 i_tp_code => i_tp_code,
1764 i_status => i_status
1765 );
1766 retcode := ec_utils.i_ret_code;
1767
1768 if ec_debug.G_debug_level >=2 then
1769 ec_debug.pl(3,'errbuf',errbuf);
1770 ec_debug.pl(3,'retcode',retcode);
1771 ec_debug.pl(2,'EC','ECE_END_INBOUND','TRANSACTION_TYPE',i_transaction_type);
1772 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_TPSTATUS');
1773 end if;
1774 ec_debug.disable_debug;
1775 EXCEPTION
1776 WHEN EC_UTILS.PROGRAM_EXIT then
1777 retcode := ec_utils.i_ret_code;
1778 ece_flatfile_pvt.print_attributes;
1779 ec_debug.disable_debug;
1780 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_TPSTATUS');
1781 WHEN OTHERS THEN
1782 retcode :=2;
1783 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_FOR_TPSTATUS');
1784 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1785 ece_flatfile_pvt.print_attributes;
1786 ec_debug.pop('ECE_INBOUND.PROCESS_FOR_TPSTATUS');
1787 ec_debug.disable_debug;
1788 end process_for_tpstatus;
1789
1790 procedure process_documents
1791 (
1792 i_document_id IN number,
1793 i_transaction_type IN varchar2,
1794 i_select_cursor IN INTEGER
1795 )
1796 is
1797 BEGIN
1798 if ec_debug.G_debug_level >= 2 then
1799 ec_debug.push('ECE_INBOUND.PROCESS_DOCUMENTS');
1800 ec_debug.pl(3,'i_document_id',i_document_id);
1801 ec_debug.pl(3,'i_select_cursor',i_select_cursor);
1802 end if;
1803 /**
1804 Savepoint for the document. If any error encountered during processing of the
1805 document , the whole document will be rolled back to this savepoint.
1806 Partial processing of the document is not allowed.
1807 **/
1808 savepoint document_start;
1809
1810 run_inbound
1811 (
1812 i_document_id,
1813 i_transaction_type,
1814 i_select_cursor
1815 );
1816
1817 /**
1818 If the Status is ABORT then stop the program execution.
1819 Rollback the Staging Data , Violations etc.
1820 **/
1821 if ec_utils.g_ext_levels(1).Status = 'ABORT'
1822 then
1823 rollback work;
1824 ec_utils.i_ret_code := 2;
1825 raise EC_UTILS.PROGRAM_EXIT;
1826 elsif (ec_utils.g_ext_levels(1).Status = 'SKIP_DOCUMENT')
1827 or (ec_utils.g_ext_levels(1).Status = 'INSERT_FAILED')
1828 then
1829 rollback to Document_start;
1830 update_document_status;
1831 end if;
1832
1833 /**
1834 Make sure that the all the records for a document are successfully
1835 inserted into the production open Interface tables. Check whether
1836 the document is ready for Insert.
1837 If yes then save the Document and delete from the Staging table.
1838 **/
1839
1840 if (
1841 ec_utils.g_ext_levels(1).Status = 'INSERT'
1842 or ec_utils.g_ext_levels(1).Status = 'NEW'
1843 or ec_utils.g_ext_levels(1).Status = 'RE_PROCESS'
1844 )
1845 then
1846 delete from ece_rule_violations
1847 where document_id = i_document_id;
1848
1849 delete from ece_stage
1850 where document_id = i_document_id;
1854 ec_debug.pl(1,'EC','ECE_DELETE_FAILED_STAGING','DOCUMENT_ID',i_document_id);
1851
1852 if sql%notfound
1853 then
1855 ec_utils.i_ret_code :=1;
1856 rollback to Document_Start;
1857 end if;
1858 end if;
1859
1860 /** Save the Violations. **/
1861 Insert_Into_Violations(i_Document_Id);
1862 if ec_debug.G_debug_level >=2 then
1863 ec_debug.pop('ECE_INBOUND.PROCESS_DOCUMENTS');
1864 end if;
1865 EXCEPTION
1866 WHEN EC_UTILS.DOCUMENTS_UNDER_PROCESS then
1867 ec_debug.pl(0,'EC','ECE_DOCUMENTS_UNDER_PROCESS',null);
1868 raise EC_UTILS.PROGRAM_EXIT;
1869 WHEN EC_UTILS.PROGRAM_EXIT then
1870 raise;
1871 WHEN OTHERS THEN
1872 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.PROCESS_DOCUMENTS');
1873 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1874 ec_utils.i_ret_code :=2;
1875 raise EC_UTILS.PROGRAM_EXIT;
1876 end process_documents;
1877
1878 procedure select_stage
1879 (
1880 i_select_cursor OUT NOCOPY integer
1881 )
1882 is
1883 i_Select_Stmt varchar2(32000);
1884 i_cursor_handle pls_integer;
1885 error_position pls_integer;
1886 i_level pls_integer;
1887 i_line_number pls_integer;
1888 i_stage_id pls_integer;
1889 i_document_number ece_stage.document_number%TYPE;
1890 i_status ece_stage.status%TYPE;
1891 i_map_id ece_stage.map_id%TYPE;
1892 i_columns ece_stage.field1%TYPE;
1893
1894 begin
1895 if ec_debug.G_debug_level >=2 then
1896 ec_debug.push('ECE_INBOUND.SELECT_STAGE');
1897 end if;
1898
1899 i_Select_Stmt := 'select Stage_Id ,Document_Number ,transaction_level ,line_number ,Status , map_id, ';
1900
1901 /**
1902 Include all the 500 Columns in the Select Clause.
1903 **/
1904 for i in 1..500
1905 loop
1906 i_Select_Stmt := i_Select_Stmt ||'FIELD'||i||',';
1907 end loop;
1908
1909 i_Select_Stmt := RTRIM(i_Select_Stmt,',');
1910 i_Select_Stmt := i_Select_Stmt ||' from ECE_STAGE where Document_Id = :i_document_id order by stage_id for update of Document_id NOWAIT';
1911
1912 /**
1913 Open the cursor and parse the SQL Statement. Trap any parsing error and
1914 report the Error Position in the SQL Statement
1915 **/
1916
1917 i_Cursor_handle := dbms_sql.Open_Cursor;
1918
1919 BEGIN
1920 dbms_sql.parse(i_Cursor_handle,i_Select_Stmt,dbms_sql.native);
1921 EXCEPTION
1922 WHEN OTHERS THEN
1923 error_position := dbms_sql.last_error_position;
1924 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.SELECT_STAGE');
1925 ece_error_handling_pvt.print_parse_error (error_position,i_Select_Stmt);
1926 ec_utils.i_ret_code :=2;
1927 raise EC_UTILS.PROGRAM_EXIT;
1928 END;
1929
1930 /**
1931 Define Columns used in the Select Clause
1932 **/
1933 dbms_sql.define_column(i_Cursor_Handle,1,i_stage_id);
1934 dbms_sql.define_column(i_Cursor_Handle,2,i_document_number,500);
1935 dbms_sql.define_column(i_Cursor_Handle,3,i_level);
1936 dbms_sql.define_column(i_Cursor_Handle,4,i_line_number);
1937 dbms_sql.define_column(i_Cursor_Handle,5,i_status,20);
1938 dbms_sql.define_column(i_Cursor_Handle,6,i_map_id);
1939 for i in 7..506
1940 loop
1941 dbms_sql.define_column(i_Cursor_Handle,i,i_columns,500);
1942 end loop;
1943
1944 ec_debug.pl(3,'Select Statement',i_select_stmt);
1945
1946 i_Select_Cursor := i_Cursor_Handle;
1947 if ec_debug.G_debug_level >=2 then
1948 ec_debug.pl(3,'i_select_cursor',i_select_cursor);
1949 ec_debug.pop('ECE_INBOUND.SELECT_STAGE');
1950 end if;
1951 EXCEPTION
1952 WHEN EC_UTILS.PROGRAM_EXIT then
1953 raise;
1954 WHEN OTHERS then
1955 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_INBOUND.SELECT_STAGE');
1956 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1957 ec_utils.i_ret_code :=2;
1958 raise EC_UTILS.PROGRAM_EXIT;
1959 end select_stage;
1960
1961
1962 function insert_into_prod_interface
1963 (
1964 i_Insert_cursor IN OUT NOCOPY INTEGER,
1965 i_level IN NUMBER
1966 )
1967 return boolean
1968 IS
1969
1970 cInsert_stmt VARCHAR2(32000) := 'INSERT INTO ';
1971 cValue_stmt VARCHAR2(32000) := 'VALUES (';
1972
1973 c_Insert_cur pls_INTEGER ;
1974 dummy pls_INTEGER;
1975 d_date DATE;
1976 n_number number;
1977 c_count pls_integer;
1978 i_success BOOLEAN := TRUE;
1979 error_position pls_integer;
1980
1981 BEGIN
1982 if ec_debug.G_debug_level >=2 then
1983 ec_debug.push('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
1984 ec_debug.pl(3,'i_Insert_Cursor',i_Insert_Cursor);
1985 ec_debug.pl(3,'i_level',i_level);
1986 end if;
1987
1988 if i_Insert_Cursor = 0
1989 then
1990 i_Insert_Cursor := -911;
1991 ec_debug.pl(3,'i_Insert_Cursor',i_Insert_Cursor);
1992 end if;
1993
1994 if i_Insert_Cursor < 0
1995 then
1996 cInsert_Stmt := cInsert_Stmt||' '||ec_utils.g_int_levels(i_level).Base_Table_Name||' (';
1997
1998 For i in ec_utils.g_int_levels(i_level).file_start_pos..ec_utils.g_int_levels(i_level).file_end_pos
1999 loop
2000 If ( ec_utils.g_file_tbl(i).base_column_name is not null )
2001 then
2002 cInsert_stmt :=cInsert_stmt||' '||ec_utils.g_file_tbl(i).base_column_name || ',';
2006
2003 cValue_stmt := cValue_stmt || ':b' || i ||',';
2004 end if;
2005 end loop;
2007 cInsert_stmt := RTRIM (cInsert_stmt, ',') || ') ';
2008 cValue_stmt := RTRIM (cValue_stmt, ',') || ')';
2009 cInsert_stmt := cInsert_stmt || cValue_stmt;
2010
2011 if ec_debug.G_debug_level = 3 then
2012 ec_debug.pl(3,'Insert_Statement',cInsert_stmt);
2013 end if;
2014
2015 i_Insert_Cursor := dbms_sql.open_cursor;
2016
2017 begin
2018 dbms_sql.parse(i_Insert_Cursor, cInsert_stmt, dbms_sql.native);
2019 exception
2020 when others then
2021 error_position := dbms_sql.last_error_position;
2022 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
2023 'ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
2024 ece_error_handling_pvt.print_parse_error (error_position,cInsert_stmt);
2025 ec_utils.i_ret_code :=2;
2026 ec_debug.pop('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
2027 raise EC_UTILS.PROGRAM_EXIT;
2028 end;
2029 end if;
2030
2031 if i_Insert_Cursor > 0
2032 then
2033
2034 begin
2035 for k in ec_utils.g_int_levels(i_level).file_start_pos..ec_utils.g_int_levels(i_level).file_end_pos
2036 loop
2037 if ec_utils.g_file_tbl(k).base_column_name is not null
2038 then
2039 BEGIN
2040 -- This Begin is to trap the Data Type Conversion problem on a field.
2041
2042 if 'DATE' = ec_utils.g_file_tbl(k).data_type
2043 Then
2044 if ec_utils.g_file_tbl(k).value is not NULL
2045 then
2046 d_date := to_date(ec_utils.g_file_tbl(k).value,'YYYYMMDD HH24MISS');
2047 else
2048 d_date := NULL;
2049 end if;
2050 if ec_debug.G_debug_level = 3 then
2051 ec_debug.pl(3,ec_utils.g_file_tbl(k).Base_Column_Name,d_date);
2052 end if;
2053 dbms_sql.bind_variable(i_Insert_Cursor, 'b'||k, d_date);
2054
2055 elsif 'NUMBER' = ec_utils.g_file_tbl(k).data_type
2056 then
2057 if ec_utils.g_file_tbl(k).value is not NULL
2058 then
2059 n_number := to_number(ec_utils.g_file_tbl(k).value);
2060 else
2061 n_number := NULL;
2062 end if;
2063 if ec_debug.G_debug_level = 3 then
2064 ec_debug.pl(3,ec_utils.g_file_tbl(k).Base_Column_Name,n_number);
2065 end if;
2066 dbms_sql.bind_variable(i_Insert_Cursor, 'b'||k, n_number);
2067
2068 else
2069 if ec_debug.G_debug_level = 3 then
2070 ec_debug.pl(3,ec_utils.g_file_tbl(k).Base_Column_Name,ec_utils.g_file_tbl(k).value);
2071 end if;
2072 dbms_sql.bind_variable(i_Insert_Cursor, 'b'||k,ec_utils.g_file_tbl(k).value);
2073 end if; -- End If for Data Type
2074
2075 EXCEPTION
2076 WHEN OTHERS then
2077 ec_debug.pl(0,'EC','ECE_DATATYPE_CONVERSION_FAILED',
2078 'DATATYPE',ec_utils.g_file_tbl(k).data_type);
2079 ec_debug.pl(0,ec_utils.g_file_tbl(k).Base_Column_Name,ec_utils.g_file_tbl(k).value);
2080 raise;
2081 END; --- Data Type Conversion Trap.
2082
2083 end if; -- End if for i_level and Base Table Name
2084
2085 end loop;
2086 dummy := dbms_sql.execute(i_Insert_Cursor);
2087
2088 end;
2089
2090 end if;
2091
2092 if ec_debug.G_debug_level >= 2 then
2093 ec_debug.pl(3,'i_success',i_success);
2094 ec_debug.pop('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
2095 end if;
2096 return i_success;
2097 EXCEPTION
2098 WHEN EC_UTILS.PROGRAM_EXIT then
2099 raise;
2100 WHEN OTHERS THEN
2101 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
2102 'ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
2103 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
2104 i_success := FALSE;
2105 ec_utils.i_ret_code :=1;
2106 ec_debug.pl(0,'EC','ECE_INSERT_FAILED',null);
2107 ec_debug.pl(3,'i_success',i_success);
2108 ec_debug.pop('ECE_INBOUND.INSERT_INTO_PROD_INTERFACE');
2109 return i_success;
2110 END insert_into_prod_interface;
2111
2112
2113 end ece_inbound;
[Home] [Help]