[Home] [Help]
2: -- $Header: ECOUBB.pls 120.3 2005/09/29 11:18:59 arsriniv ship $
3:
4: cursor seq_stage_id
5: is
6: Select ece_stage_id_s.NEXTVAL
7: from dual;
8:
9: cursor seq_document_id
10: is
14: c_local_chr_9 varchar2(1) := fnd_global.local_chr(9);
15: c_local_chr_13 varchar2(1) := fnd_global.local_chr(13);
16:
17: /**
18: Build and Parses the Insert Statement for insert into ece_stage for each Level.
19: The Cursor handles are stored in the ec_utils.g_int_levels(i).cursor_handle.
20: **/
21: procedure parse_insert_statement
22: (
23: i_level in pls_integer
24: )
25: is
26: i_Insert_Cursor pls_integer;
27: cInsert_stmt varchar2(32000) := 'INSERT INTO ECE_STAGE ( ';
28: cValue_stmt varchar2(32000) := 'VALUES (';
29: dummy pls_integer;
30: error_position pls_integer;
31: begin
59: cValue_stmt := RTRIM(cValue_stmt,',')||')';
60: cInsert_stmt := cInsert_stmt||cValue_stmt;
61:
62: if ec_Debug.G_debug_level = 3 then
63: ec_debug.pl(3,'EC','ECE_STAGE_INSERT_LEVEL','LEVEL',i_level,null);
64: ec_debug.pl(3,cInsert_stmt);
65: end if;
66:
67: /**
171:
172: /**
173: Loads the Objects required by the Outbound transaction. This includes
174: 1. Select statement on the ec_views.
175: 2. Insert statement for ece_stage table.
176: 3. Parses and loads Custom Procedures into memory table.
177: 4. Loads mappings required by these procedures into memory tables.
178: **/
179: procedure load_objects
265: end load_objects;
266:
267:
268: /**
269: Bind the values to the Insert statement for the ece_stage table.
270: **/
271: procedure bind_insert_statement
272: (
273: i_level in pls_integer
275: is
276: i_Insert_Cursor pls_integer := ec_utils.g_ext_levels(i_level).cursor_handle;
277: dummy pls_integer;
278: error_position pls_integer;
279: i_status ece_stage.status%TYPE := 'NEW';
280: ins_value varchar2(32000);
281: begin
282: if ec_debug.G_debug_level >= 2 then
283: ec_debug.push('EC_OUTBOUND.BIND_INSERT_STATEMENT');
338:
339: dummy := dbms_sql.execute(i_Insert_Cursor);
340: if dummy = 1 then
341: if ec_debug.G_debug_level = 3 then
342: ec_debug.pl(3,'EC','ECE_STAGE_INSERTED',null);
343: end if;
344: else
345: ec_debug.pl(0,'EC','ECE_STAGE_INSERT_FAILED','LEVEL',i_level);
346: ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.BIND_INSERT_STATEMENT');
341: if ec_debug.G_debug_level = 3 then
342: ec_debug.pl(3,'EC','ECE_STAGE_INSERTED',null);
343: end if;
344: else
345: ec_debug.pl(0,'EC','ECE_STAGE_INSERT_FAILED','LEVEL',i_level);
346: ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.BIND_INSERT_STATEMENT');
347: ec_utils.i_ret_code :=2;
348: raise EC_UTILS.PROGRAM_EXIT;
349: end if;
908: raise EC_UTILS.PROGRAM_EXIT;
909: END IF;
910:
911: /**
912: Delete all indicated records from ECE_STAGE
913: **/
914: DELETE FROM ece_stage
915: WHERE run_id = i_run_id
916: AND (document_id = i_document_id OR i_document_id IS NULL);
910:
911: /**
912: Delete all indicated records from ECE_STAGE
913: **/
914: DELETE FROM ece_stage
915: WHERE run_id = i_run_id
916: AND (document_id = i_document_id OR i_document_id IS NULL);
917:
918: IF SQL%ROWCOUNT = 0 THEN
919: /**
920: Output a warning message if no rows are deleted
921: **/
922: if ec_debug.G_debug_level >= 1 then
923: ec_debug.pl(1,'NO rows deleted from ECE_STAGE');
924: end if;
925: END IF;
926: if ec_debug.G_debug_level >= 2 then
927: ec_debug.pl(3,'Number of rows deleted from ECE_STAGE',SQL%ROWCOUNT);
923: ec_debug.pl(1,'NO rows deleted from ECE_STAGE');
924: end if;
925: END IF;
926: if ec_debug.G_debug_level >= 2 then
927: ec_debug.pl(3,'Number of rows deleted from ECE_STAGE',SQL%ROWCOUNT);
928:
929: ec_debug.pop('EC_OUTBOUND.DELETE_STAGE_DATA');
930: end if;
931: EXCEPTION