[Home] [Help]
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;
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;
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;
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;
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;
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
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';
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';
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';
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:
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 );
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:
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)
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;
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');
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);
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
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: (
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:
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 );
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:
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;
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;
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');
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);
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
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: (
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 );
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:
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;
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
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;
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;
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;
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
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
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;
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:
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;
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;
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');
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);
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
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: (
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:
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 );
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:
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;
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;
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');
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);
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
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: (
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:
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 );
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:
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;
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;
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');
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);
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
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: (
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:
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 );
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:
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
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;
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;
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');
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);
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
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: (
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: **/
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;
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
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:
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: /**
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
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;
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:
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
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
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';
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;
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:
819:
820: /**
821: Update Global variable to hold the Current level of the Record.
822: **/
823: ec_utils.g_current_level := i_level;
824: i_previous_level := i_level;
825:
826: /**
827: Populate Transaction Attribute table for Error Handling.
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:
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.
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
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,
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(
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,
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(
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
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.
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:
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
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);
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;
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:
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
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
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,
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
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: (
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,
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,
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.
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;
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
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
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:
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,
972: p_level => i_level
973: );
974:
975: /**
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
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
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
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,
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
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;
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
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;
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:
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: <
1031:
1032:
1033: <
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 )
1032:
1033: <
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
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.
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;
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
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
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: /**
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;
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
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 <>
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;
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
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;
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
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:
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: /**
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
1123: else
1124: /**
1125: Invalid Status , Skipping the Document.
1126: **/
1127: ec_utils.g_ext_levels(1).Status := 'SKIP_DOCUMENT';
1128:
1129: /**
1130: Keep Track of how many documents have been skipped in this
1131: run of the transaction.
1130: Keep Track of how many documents have been skipped in this
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: **/
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:
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
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
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;
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
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
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;
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:
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;
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
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
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');
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);
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
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
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:
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
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:
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
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
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
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
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.
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
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',
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:
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;
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
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);
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
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: (
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;
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;
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. */
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;
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';
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;
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;
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);
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:
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: /**
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;
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.
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);
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:
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:
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.
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;
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;
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;
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;
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;
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:
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
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
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
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
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
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
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;
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:
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,
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));
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:
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:
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;
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;
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;
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;
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:
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;
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;
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;
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:
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
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.
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;
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;
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:
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;
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;
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;
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;
1406: ec_utils.g_stack_pointer(i).end_pos :=0;
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;
1406: ec_utils.g_stack_pointer(i).end_pos :=0;
1407: end loop;
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;
1406: ec_utils.g_stack_pointer(i).end_pos :=0;
1407: end loop;
1408:
1409: /**
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;
1406: ec_utils.g_stack_pointer(i).end_pos :=0;
1407: end loop;
1408:
1409: /**
1410: Execute the Dynamic Inbound Stage data for Stage = 10.
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);
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:
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:
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:
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
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
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;
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
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
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;
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:
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: /**
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);
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;
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
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
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: (
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
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: (
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);
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');
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;
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');
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
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');
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
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);
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');
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
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);
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');
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
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;
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
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;
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;
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;
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
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
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;
1851:
1852: if sql%notfound
1853: then
1854: ec_debug.pl(1,'EC','ECE_DELETE_FAILED_STAGING','DOCUMENT_ID',i_document_id);
1855: ec_utils.i_ret_code :=1;
1856: rollback to Document_Start;
1857: end if;
1858: end if;
1859:
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;
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');
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);
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
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: (
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: /**
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
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);
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:
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
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 )
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 || ',';
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 || ',';
2003: cValue_stmt := cValue_stmt || ':b' || i ||',';
2004: end if;
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 || ',';
2003: cValue_stmt := cValue_stmt || ':b' || i ||',';
2004: end if;
2005: end loop;
2006:
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;
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
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
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:
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');
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;
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
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
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);
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;
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
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
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:
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
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:
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
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');
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;