[Home] [Help]
PACKAGE BODY: APPS.EC_OUTBOUND
Source
1 PACKAGE BODY ec_outbound as
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
11 select ece_document_id_s.NEXTVAL
12 from dual;
13 c_local_chr_10 varchar2(1) := fnd_global.local_chr(10);
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
32 if ec_debug.G_debug_level >= 2 then
33 ec_debug.push('EC_OUTBOUND.PARSE_INSERT_STATEMENT');
34 ec_debug.pl(3,'i_level',i_level);
35 end if;
36
37 --- Add Mandatory Columns for the Record
38 cInsert_stmt := cInsert_stmt||' Stage_id, Document_Id , Transaction_type , Transaction_Level ,';
39 cInsert_stmt := cInsert_stmt||' Line_Number , Parent_Stage_Id , Run_Id , Document_Number ,Status ,';
40
41 --- Add Who Columns for the Staging Table
42 cInsert_stmt := cInsert_stmt||' creation_date , created_by , last_update_date , last_updated_by ,';
43
44 cValue_stmt := cValue_stmt||':a1 ,:a2 ,:a3 ,:a4 ,:a5,:a6,:a7,:a8,:a9 ,';
45 cValue_stmt := cValue_stmt||':w1 ,:w2 ,:w3 ,:w4 ,';
46
47 --- Add Variable Columns for the Record
48 for i in ec_utils.g_int_levels(i_level).file_start_pos..ec_utils.g_int_levels(i_level).file_end_pos
49 loop
50 if ec_utils.g_file_tbl(i).staging_column is not null
51 then
52 --- Build Insert Statement
53 cInsert_stmt := cInsert_stmt||' '||ec_utils.g_file_tbl(i).staging_column|| ',';
54 cValue_stmt := cvalue_stmt || ':b'||i||',';
55 end if;
56 end loop;
57
58 cInsert_stmt := RTRIM(cInsert_stmt,',')||')';
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 /**
68 Open the cursor and parse the SQL Statement. Trap any parsing error and report
69 the Error Position in the SQL Statement
70 **/
71 i_Insert_Cursor := dbms_sql.Open_Cursor;
72
73 if ec_debug.G_debug_level = 3 then
74 ec_debug.pl(3,'i_insert_cursor',i_insert_cursor);
75 end if;
76
77 ec_utils.g_ext_levels(i_level).cursor_handle := i_insert_cursor;
78
79 begin
80 dbms_sql.parse(i_Insert_Cursor,cInsert_stmt,dbms_sql.native);
81 exception
82 when others then
83 error_position := dbms_sql.last_error_position;
84 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.PARSE_INSERT_STATEMENT');
85 ece_error_handling_pvt.print_parse_error (error_position,cInsert_stmt);
86 ec_utils.i_ret_code :=2;
87 raise EC_UTILS.PROGRAM_EXIT;
88 end;
89 if ec_debug.G_debug_level >= 2 then
90 ec_debug.pop('EC_OUTBOUND.PARSE_INSERT_STATEMENT');
91 end if;
92
93 EXCEPTION
94 WHEN EC_UTILS.PROGRAM_EXIT then
95 raise;
96 WHEN OTHERS THEN
97 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.PARSE_INSERT_STATEMENT');
98 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
99 ec_utils.i_ret_code :=2;
100 raise EC_UTILS.PROGRAM_EXIT;
101 END parse_insert_statement;
102
103 /**
104 Prepares the Select statement for the ec_views on the base Oracle Applications tables.
105 **/
106 procedure select_clause
107 (
108 i_level IN pls_integer,
109 i_Where_string OUT NOCOPY VARCHAR2
110 ) IS
111 cSelect_stmt VARCHAR2(32000) := 'SELECT ';
112 cFrom_stmt VARCHAR2(100) := ' FROM ';
113 cWhere_stmt VARCHAR2(80) := ' WHERE 1=1 ';
114
115 cTO_CHAR VARCHAR2(20) := 'TO_CHAR(';
116 cDATE VARCHAR2(40) := ',''YYYYMMDD HH24MISS'')';
117 cWord1 VARCHAR2(20) := ' ';
118 cWord2 VARCHAR2(40) := ' ';
119
120 iRow_count pls_integer := ec_utils.g_file_tbl.COUNT;
121
122 BEGIN
123 if ec_debug.G_debug_level >= 2 then
124 EC_DEBUG.PUSH('EC_OUTBOUND.SELECT_CLAUSE');
125 EC_DEBUG.PL(3, 'i_level',i_level);
126 end if;
127
128 For i in ec_utils.g_int_levels(i_level).file_start_pos..ec_utils.g_int_levels(i_level).file_end_pos
129 loop
130 -- **************************************
131 -- apply appropriate data conversion
132 -- convert everything to VARCHAR
133 -- **************************************
134
135 if 'DATE' = ec_utils.g_file_tbl(i).data_type Then
136 cWord1 := cTO_CHAR;
137 cWord2 := cDATE;
138
139 elsif 'NUMBER' = ec_utils.g_file_tbl(i).data_type Then
140 cWord1 := cTO_CHAR;
141 cWord2 := ')';
142 else
143 cWord1 := NULL;
144 cWord2 := NULL;
145 END if;
146
147 -- build SELECT statement
148 cSelect_stmt := cSelect_stmt || ' ' || cWord1 ||
149 nvl(ec_utils.g_file_tbl(i).base_column_Name,'NULL') || cWord2 || ',';
150 End Loop;
151
152 -- build FROM, WHERE statements
153
154 cFrom_stmt := cFrom_Stmt||' '||ec_utils.g_int_levels(i_level).base_table_name;
155
156 cSelect_stmt := RTRIM(cSelect_stmt, ',');
157 i_Where_string := cSelect_stmt||' '||cFrom_stmt||' '||cWhere_Stmt;
158
159 if ec_debug.G_debug_level >= 2 then
160 ec_debug.pl(3,'i_Where_String',i_Where_String);
161 EC_DEBUG.POP('EC_OUTBOUND.SELECT_CLAUSE');
162 end if;
163
164 exception
165 when others then
166 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.SELECT_CLAUSE');
167 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
168 ec_utils.i_ret_code :=2;
169 raise EC_UTILS.PROGRAM_EXIT;
170 END select_clause;
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
180 is
181 i_counter pls_integer :=0;
182 begin
183 if ec_debug.G_debug_level >= 2 then
184 ec_debug.push('EC_OUTBOUND.LOAD_OBJECTS');
185 end if;
186
187 for i in 1..ec_utils.g_int_levels.COUNT
188 LOOP
189 select_clause
190 (
191 i,
192 ec_utils.g_int_levels(i).sql_stmt
193 );
194
195 ec_utils.execute_stage_data
196 (
197 10,
198 i
199 );
200
201
202 -- Open Cursor For Each level and store the handles in the PL/SQL table.
203 ec_utils.g_int_levels(i).Cursor_handle := dbms_sql.open_cursor;
204 if ec_debug.G_debug_level = 3 then
205 ec_debug.pl(3,'Cursor handle',ec_utils.g_int_levels(i).Cursor_handle);
206 end if;
207
208 -- Parse the Select Statement for Each level
209 BEGIN
210 dbms_sql.parse (
211 ec_utils.g_int_levels(i).cursor_handle,
212 ec_utils.g_int_levels(i).sql_stmt,
213 dbms_sql.native
214 );
215 EXCEPTION
216 WHEN OTHERS THEN
217 ece_error_handling_pvt.print_parse_error
218 (
219 dbms_sql.last_error_position,
220 ec_utils.g_int_levels(i).sql_stmt
221 );
222 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.LOAD_OBJECTS');
223 ec_debug.pl(0,'EC','ECE_PARSE_VIEW_ERROR','LEVEL',i);
224 raise EC_UTILS.PROGRAM_EXIT;
225 END;
226
227 i_counter :=0;
228 -- Define Columns for Each Level
229 FOR k in ec_utils.g_int_levels(i).file_start_pos..ec_utils.g_int_levels(i).file_end_pos
230 LOOP
231 i_counter := i_counter + 1;
232 dbms_sql.define_column
233 (
234 ec_utils.g_int_levels(i).Cursor_Handle,
235 i_counter,
236 ec_utils.g_int_levels(i).sql_stmt,
237 ece_extract_utils_PUB.G_MaxColWidth
238 );
239 END LOOP;
240
241 END LOOP;
242
243 ec_utils.i_stage_data := ec_utils.i_tmp2_stage_data; -- 2920679
244
245 for i in 1..ec_utils.g_ext_levels.COUNT
246 LOOP
247 -- Parse the Insert Statement for Staging table.
248 parse_insert_statement
249 (
250 i
251 );
252 end loop;
253 if ec_debug.G_debug_level >= 2 then
254 ec_debug.pop('EC_OUTBOUND.LOAD_OBJECTS');
255 end if;
256
257 EXCEPTION
258 WHEN EC_UTILS.PROGRAM_EXIT then
259 raise;
260 WHEN OTHERS THEN
261 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.LOAD_OBJECTS');
262 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
263 ec_utils.i_ret_code :=2;
264 raise EC_UTILS.PROGRAM_EXIT;
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
274 )
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');
284 ec_debug.pl(3,'i_level',i_level);
285 end if;
286
287 begin
288 -- Bind values for Mandatory Columns
289 dbms_sql.bind_variable (i_Insert_Cursor,'a1',to_number(ec_utils.g_ext_levels(i_level).Stage_Id));
290 dbms_sql.bind_variable (i_Insert_Cursor,'a2',to_number(ec_utils.g_ext_levels(i_level).Document_Id));
291 dbms_sql.bind_variable (i_Insert_Cursor,'a3',ec_utils.g_transaction_type);
292 dbms_sql.bind_variable (i_Insert_Cursor,'a4',to_number(i_level));
293 dbms_sql.bind_variable (i_Insert_Cursor,'a5',to_number(ec_utils.g_ext_levels(i_level).Line_Number));
294 dbms_sql.bind_variable (i_Insert_Cursor,'a6',ec_utils.g_ext_levels(i_level).Parent_Stage_Id);
295 dbms_sql.bind_variable (i_Insert_Cursor,'a7',ec_utils.g_run_id);
296 dbms_sql.bind_variable (i_Insert_Cursor,'a8',ec_utils.g_ext_levels(i_level).Document_Number);
297 dbms_sql.bind_variable (i_Insert_Cursor,'a9',i_status);
298
299 -- Bind values for Mandatory Columns
300 dbms_sql.bind_variable (i_Insert_Cursor,'w1',sysdate);
301 dbms_sql.bind_variable (i_Insert_Cursor,'w2',fnd_global.user_id);
302 dbms_sql.bind_variable (i_Insert_Cursor,'w3',sysdate);
303 dbms_sql.bind_variable (i_Insert_Cursor,'w4',fnd_global.user_id);
304
305 if ec_debug.G_debug_level = 3 then
306 ec_debug.pl(3,'STAGE_ID',ec_utils.g_ext_levels(i_level).Stage_Id);
307 ec_debug.pl(3,'DOCUMENT_ID',ec_utils.g_ext_levels(i_level).Document_Id);
308 ec_debug.pl(3,'TRANSACTION_TYPE',ec_utils.g_transaction_type);
309 ec_debug.pl(3,'TRANSACTION_LEVEL',i_level);
310 ec_debug.pl(3,'LINE_NUMBER',ec_utils.g_ext_levels(i_level).Line_Number);
311 ec_debug.pl(3,'PARENT_STAGE_ID',ec_utils.g_ext_levels(i_level).Parent_Stage_Id);
312 ec_debug.pl(3,'RUN_ID',ec_utils.g_run_id);
313 ec_debug.pl(3,'DOCUMENT_NUMBER',ec_utils.g_ext_levels(i_level).Document_Number);
314 ec_debug.pl(3,'CREATION_DATE',sysdate);
315 ec_debug.pl(3,'CREATED_BY',fnd_global.user_id);
316 ec_debug.pl(3,'LAST_UPDATE_DATE',sysdate);
317 ec_debug.pl(3,'LAST_UPDATED_BY',fnd_global.user_id);
318 end if;
319
320 -- Bind values for Staging Columns mapped to the Flat File
321 for k in ec_utils.g_int_levels(i_level).file_start_pos..ec_utils.g_int_levels(i_level).file_end_pos
322 loop
323 if ( ec_utils.g_file_tbl(k).staging_column is not null)
324 then
325 ins_value := replace(replace(replace(ec_utils.g_file_tbl(k).value,c_local_chr_10,''),c_local_chr_9,''),c_local_chr_13,'');
326 dbms_sql.bind_variable (
327 i_Insert_Cursor,
328 'b'||k,ins_value,
329 500
330 );
331 if ec_debug.G_debug_level = 3 then
332 ec_debug.pl(3,upper(ec_utils.g_file_tbl(k).staging_column)||'-'||
333 upper(ec_utils.g_file_tbl(k).interface_column_name),
334 ec_utils.g_file_tbl(k).value);
335 end if;
336 end if;
337 end loop;
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');
347 ec_utils.i_ret_code :=2;
348 raise EC_UTILS.PROGRAM_EXIT;
349 end if;
350
351 exception
352 when others then
353 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.BIND_INSERT_STATEMENT');
354 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
355 ec_debug.pl(0,'EC','ECE_ERROR_SQL',null);
356
357 ec_utils.i_ret_code :=2;
358 raise EC_UTILS.PROGRAM_EXIT;
359 end;
360 if ec_debug.G_debug_level >= 2 then
361 ec_debug.pop('EC_OUTBOUND.BIND_INSERT_STATEMENT');
362 end if;
363 EXCEPTION
364 WHEN EC_UTILS.PROGRAM_EXIT then
365 raise;
366 WHEN OTHERS THEN
367 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.BIND_INSERT_STATEMENT');
368 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
369 ec_utils.i_ret_code :=2;
370 raise EC_UTILS.PROGRAM_EXIT;
371 END bind_insert_statement;
372
373
374 /**
375 Inserts the data into the staging table.
376 **/
377 procedure insert_into_stage
378 (
379 i_level in pls_integer
380 )
381 is
382 i_parent_stage_id pls_integer;
383 i_stage_id pls_integer;
384 i_document_id pls_integer;
385 i_line_number pls_integer :=0;
386 begin
387 if ec_debug.G_debug_level >= 2 then
388 ec_debug.push('EC_OUTBOUND.INSERT_INTO_STAGE');
389 ec_debug.pl(3,'i_level',i_level);
390 end if;
391
392 -- Generate Stage Id anyways
393 open seq_stage_id;
394 fetch seq_stage_id
395 into i_stage_id;
396 close seq_stage_id;
397
398 -- Insert data into Stage table
399 if i_level = 1
400 then
401 --Generate Document Id
402 open seq_document_id;
403 fetch seq_document_id
404 into i_document_id;
405 close seq_document_id;
406
407 ec_utils.g_ext_levels(1).document_id := i_document_id;
408 ec_utils.g_ext_levels(1).parent_stage_id := 0;
409 ec_utils.g_ext_levels(1).Line_Number := 1;
410 ec_utils.g_ext_levels(1).stage_id := i_stage_id;
411
412 /**
413 Initialize all the Down Levels
414 **/
415 for j in 2..ec_utils.g_ext_levels.COUNT
416 loop
417 ec_utils.g_ext_levels(j).document_id := i_document_id;
418 ec_utils.g_ext_levels(j).parent_stage_id := null;
419 ec_utils.g_ext_levels(j).Line_Number := 0;
420 ec_utils.g_ext_levels(j).stage_id := null;
421 end loop;
422
423
424 if g_key_column_pos is null
425 then
426 if ec_debug.G_debug_level >= 1 then
427 ec_debug.pl(1,'EC','ECE_DOCUMENT_ID','DOCUMENT_ID',i_document_id);
428 end if;
429 else
430 if ec_debug.G_debug_level >= 1 then
431 ec_debug.pl(1,'EC','ECE_DOCUMENT_ID','DOCUMENT_ID',i_document_id,
432 'DOCUMENT_NUMBER',ec_utils.g_file_tbl(g_key_column_pos).value);
433 end if;
434 ec_utils.g_ext_levels(1).Document_Number :=
435 ec_utils.g_file_tbl(g_key_column_pos).value;
436 end if;
437 else
438 ec_utils.g_ext_levels(i_level).Line_Number :=
439 ec_utils.g_ext_levels(i_level).Line_Number + 1;
440 ec_utils.g_ext_levels(i_level).stage_id := i_stage_id;
441 /**
442 If the previous Level Stage Id is null , go all the way up till you find
443 a not null stage_id.
444 **/
445 for j in REVERSE 1..i_level-1
446 loop
447 if ec_utils.g_ext_levels(j).stage_id is not null
448 then
449 ec_utils.g_ext_levels(i_level).parent_stage_id :=
450 ec_utils.g_ext_levels(j).stage_id;
451 exit;
452 end if;
453 end loop;
454
455 /**
456 Initialize all Down Levels
457 **/
458 for j in i_level+1..ec_utils.g_ext_Levels.COUNT
459 loop
460 ec_utils.g_ext_levels(j).parent_stage_id := null;
461 ec_utils.g_ext_levels(j).Line_Number := 0;
462 end loop;
463 end if;
464 if ec_debug.G_debug_level = 3 then
465 ec_debug.pl(3,'Stage Id',ec_utils.g_ext_levels(i_level).Stage_Id);
466 ec_debug.pl(3,'Document Id',ec_utils.g_ext_levels(i_level).Document_Id);
467 ec_debug.pl(3,'Document Number',ec_utils.g_ext_levels(i_level).Document_Number);
468 ec_debug.pl(3,'Line Number',ec_utils.g_ext_levels(i_level).Line_Number);
469 ec_debug.pl(3,'Parent Stage Id',ec_utils.g_ext_levels(i_level).Parent_Stage_Id);
470 ec_debug.pl(3,'Transaction Level',ec_utils.g_ext_levels(i_level).external_level);
471 end if;
472
473 bind_insert_statement
474 (
475 i_level
476 );
477 if ec_debug.G_debug_level >= 2 then
478 ec_debug.pop('EC_OUTBOUND.INSERT_INTO_STAGE');
479 end if;
480
481 EXCEPTION
482 WHEN EC_UTILS.PROGRAM_EXIT then
483 raise;
484 WHEN OTHERS THEN
485 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.INSERT_INTO_STAGE');
486 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
487 ec_utils.i_ret_code :=2;
488 raise EC_UTILS.PROGRAM_EXIT;
489 END insert_into_stage;
490
491
492 /**
493 Fetches data from the ec_views recurrsively for a given document.
494 **/
495 procedure fetch_data_from_view
496 (
497 i_level IN pls_integer
498 )
499 is
500
501 i_column_counter pls_integer :=0;
502 i_rows_processed pls_integer ;
503 i_init_msg_list varchar2(20);
504 i_simulate varchar2(20);
505 i_validation_level varchar2(20);
506 i_commit varchar2(20);
507 i_return_status varchar2(20);
508 i_msg_count varchar2(20);
509 i_msg_data varchar2(2000);
510
511
512 BEGIN
513 if ec_debug.G_debug_level >= 2 then
514 ec_debug.push('EC_OUTBOUND.FETCH_DATA_FROM_VIEW');
515 ec_debug.pl(3,'i_level',i_level);
516 end if;
517
518 for i in 1..ec_utils.g_int_levels.COUNT
519 loop
520 IF ec_utils.g_int_levels(i).parent_level = i_level
521 THEN
522 -- Set the Global Variable for Current Level
523 ec_utils.g_current_level := i;
524 ec_utils.execute_stage_data
525 (
526 20,
527 i
528 );
529
530 i_rows_processed := dbms_sql.execute (ec_utils.g_int_levels(i).Cursor_Handle);
531 if ec_debug.G_debug_level = 3 then
532 ec_debug.pl(3,'Cursor Handle',ec_utils.g_int_levels(i).Cursor_handle);
533 end if;
534 while dbms_sql.fetch_rows( ec_utils.g_int_levels(i).Cursor_handle) > 0
535 LOOP
536 if ec_debug.G_debug_level = 3 then
537 ec_debug.pl(3,'Processing Row: '||dbms_sql.last_row_count||' for Level '||
538 ec_utils.g_int_levels(i).interface_level);
539 end if;
540 -- Get Values from the View
541 -- Initialize the Column Counter
542 i_column_counter :=0;
543 for j in ec_utils.g_int_levels(i).file_start_pos..ec_utils.g_int_levels(i).file_end_pos
544 loop
545 i_column_counter := i_column_counter + 1;
546
547 dbms_sql.column_value
548 (
549 ec_utils.g_int_levels(i).Cursor_handle,
550 i_column_counter,
551 ec_utils.g_file_tbl(j).value
552 );
553 if ec_debug.G_debug_level = 3 then
554 if ec_utils.g_file_tbl(j).base_column_name is not null
555 then
556 ec_debug.pl(
557 3,
558 ec_utils.g_file_tbl(j).base_column_name,
559 ec_utils.g_file_tbl(j).value
560 );
561 end if;
562 end if;
563 end loop;
564
565 -- Stage 30 Actions
566 ec_utils.execute_stage_data
567 (
568 30,
569 i
570 );
571
572
573 for k in 1..ec_utils.g_int_ext_levels.COUNT
574 loop
575 if ec_utils.g_int_ext_levels(k).interface_level = i
576 then
577 if k < ec_utils.g_int_ext_levels.COUNT
578 then
579 if ec_utils.g_int_ext_levels(k+1).external_level <>
580 ec_utils.g_int_ext_levels(k).external_level
581 then
582
583 /**
584 Perform Code Conversion
585 **/
586 ec_code_conversion_pvt.populate_plsql_tbl_with_extval
587 (
588 p_api_version_number => 1.0,
589 p_init_msg_list => i_init_msg_list,
590 p_simulate => i_simulate,
591 p_commit => i_commit,
592 p_validation_level => i_validation_level,
593 p_return_status => i_return_status,
594 p_msg_count => i_msg_count,
595 p_msg_data => i_msg_data,
596 p_level => ec_utils.g_int_ext_levels(k).external_level,
597 p_tbl => ec_utils.g_file_tbl
598 );
599
600 /**
601 Check the Status of the Code Conversion API
602 and take appropriate action.
603 **/
604 IF (
605 i_return_status = FND_API.G_RET_STS_ERROR OR
606 i_return_status is NULL OR
607 i_return_status = FND_API.G_RET_STS_UNEXP_ERROR
608 )
609 THEN
610 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
611 'EC_OUTBOUND.FETCH_DATA_FROM_VIEW');
612 ec_debug.pl(0,'EC','EC_CODE_CONVERSION_FAILED','LEVEL',i);
613 ec_utils.i_ret_code := 2;
614 RAISE EC_UTILS.PROGRAM_EXIT;
615 END IF;
616
617 -- Stage 40 Actions
618 ec_utils.execute_stage_data
619 (
620 40,
621 i
622 );
623
624 /**
625 Write to Flat File , if staging is not used.
626 **/
627 --Insert into Staging
628 insert_into_stage
629 (
630 ec_utils.g_int_ext_levels(k).external_level
631 );
632
633 end if;
634 else
635 /**
636 Perform Code Conversion
637 **/
638 ec_code_conversion_pvt.populate_plsql_tbl_with_extval
639 (
640 p_api_version_number => 1.0,
641 p_init_msg_list => i_init_msg_list,
642 p_simulate => i_simulate,
643 p_commit => i_commit,
644 p_validation_level => i_validation_level,
645 p_return_status => i_return_status,
646 p_msg_count => i_msg_count,
647 p_msg_data => i_msg_data,
648 p_level => ec_utils.g_int_ext_levels(k).external_level,
649 p_tbl => ec_utils.g_file_tbl
650 );
651
652 /**
653 Check the Status of the Code Conversion API
654 and take appropriate action.
655 **/
656 IF (
657 i_return_status = FND_API.G_RET_STS_ERROR OR
658 i_return_status is NULL OR
659 i_return_status = FND_API.G_RET_STS_UNEXP_ERROR
660 )
661 THEN
662 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
663 'EC_OUTBOUND.FETCH_DATA_FROM_VIEW');
664 ec_debug.pl(0,'EC','EC_CODE_CONVERSION_FAILED','LEVEL',i);
665 ec_utils.i_ret_code := 2;
666 RAISE EC_UTILS.PROGRAM_EXIT;
667 END IF;
668
669 -- Stage 40 Actions
670 ec_utils.execute_stage_data
671 (
672 40,
673 i
674 );
675
676 /**
677 Write to Flat File , if staging is not used.
678 **/
679 --Insert into Staging
680 insert_into_stage
681 (
682 ec_utils.g_int_ext_levels(k).external_level
683 );
684 end if;
685 end if;
686 end loop;
687
688 -- Stage 50 Actions
689 ec_utils.execute_stage_data
690 (
691 50,
692 i
693 );
694
695 -- Fetch Child records recursively
696 fetch_data_from_view (i);
697
698 END LOOP;
699 if i = 1
700 then
701 ec_utils.g_int_levels(i).rows_processed := dbms_sql.last_row_count;
702 else
703 ec_utils.g_int_levels(i).rows_processed :=
704 ec_utils.g_int_levels(i).rows_processed + dbms_sql.last_row_count;
705 end if;
706
707 END IF;
708 end loop;
709 if ec_debug.G_debug_level >= 2 then
710 ec_debug.pop('EC_OUTBOUND.FETCH_DATA_FROM_VIEW');
711 end if;
712
713 EXCEPTION
714 WHEN EC_UTILS.PROGRAM_EXIT then
715 raise;
716 WHEN OTHERS THEN
717 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.FETCH_DATA_FROM_VIEW');
718 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
719 ec_utils.i_ret_code :=2;
720 raise EC_UTILS.PROGRAM_EXIT;
721 END fetch_data_from_view;
722
723 /**
724 Closes all the cursor handles .
725 **/
726 procedure close_outbound
727 is
728 begin
729 if ec_debug.G_debug_level >= 2 then
730 ec_debug.push('EC_OUTBOUND.CLOSE_OUTBOUND');
731 end if;
732 /**
733 Successful execution of the transaction. Close the Cursor handles,
734 Disbale the Debug.
735 **/
736
737 for i in 1..ec_utils.g_procedure_stack.COUNT
738 loop
739 if dbms_sql.IS_OPEN(ec_utils.g_procedure_stack(i).Cursor_Handle)
740 then
741 dbms_sql.close_cursor(ec_utils.g_procedure_stack(i).Cursor_Handle);
742 end if;
743 end loop;
744
745 for i in 1..ec_utils.g_int_levels.COUNT
746 loop
747 if dbms_sql.IS_OPEN(ec_utils.g_int_levels(i).Cursor_Handle)
748 then
749 dbms_sql.close_cursor(ec_utils.g_int_levels(i).Cursor_Handle);
750 end if;
751 end loop;
752
753 for i in 1..ec_utils.g_ext_levels.COUNT
754 loop
755
756 if dbms_sql.IS_OPEN(ec_utils.g_ext_levels(i).cursor_handle)
757 then
758 dbms_sql.close_cursor(ec_utils.g_ext_levels(i).cursor_handle);
759 end if;
760 end loop;
761 if ec_debug.G_debug_level >= 2 then
762 ec_debug.pop('EC_OUTBOUND.CLOSE_OUTBOUND');
763 end if;
764
765 exception
766 WHEN OTHERS THEN
767 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.CLOSE_OUTBOUND');
768 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
769 ec_utils.i_ret_code :=2;
770 raise EC_UTILS.PROGRAM_EXIT;
771 end close_outbound;
772
773 /**
774 Main Call for Processing Outbound Documents
775 **/
776 procedure process_outbound_documents
777 (
778 i_transaction_type IN varchar2,
779 i_map_id IN pls_integer,
780 i_run_id OUT NOCOPY pls_integer
781 )
782 is
783 i_plsql_pos pls_integer;
784 begin
785 ec_debug.pl(0,'EC','ECE_START_OUTBOUND','TRANSACTION_TYPE',i_transaction_type,'MAP_ID',i_map_id);
786 if ec_debug.G_debug_level >= 2 then
787 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
788 ec_debug.pl(3,'i_map_id',i_map_id);
789 ec_debug.push('EC_OUTBOUND.PROCESS_OUTBOUND_DOCUMENTS');
790 end if;
791
792 /** Initialize Memory Structures**/
793 ec_utils.g_file_tbl.DELETE;
794 ec_utils.g_int_levels.DELETE;
795 ec_utils.g_ext_levels.DELETE;
796 ec_utils.g_int_ext_levels.DELETE;
797 ec_utils.g_stage_data.DELETE;
798 ec_utils.g_parameter_stack.DELETE;
799 ec_utils.g_procedure_stack.DELETE;
800 ec_utils.g_procedure_mappings.DELETE;
801 ec_utils.g_stack_pointer.DELETE;
802 ec_utils.g_transaction_type := i_transaction_type;
803 ec_utils.g_direction := substrb(i_transaction_type,length(i_transaction_type),1);
804 ec_utils.g_map_id := i_map_id;
805
806 if ec_debug.G_debug_level = 3 then
807 ec_debug.pl(3,'g_direction',ec_utils.g_direction);
808 end if;
809 /**
810 If the program is run from SQLplus , the Concurrent Request id is
811 < 0. In this case , get the run id from ECE_OUTPUT_RUNS_S.NEXTVAL.
812 **/
813 i_run_id := fnd_global.conc_request_id;
814 if i_run_id <= 0
815 then
816 select ece_output_runs_s.NEXTVAL
817 into i_run_id
818 from dual;
819 end if;
820 ec_utils.g_run_id := i_run_id;
821 if ec_debug.G_debug_level = 3 then
822 ec_debug.pl(3,'Run Id for the Transaction',ec_utils.g_run_id);
823 end if;
824
825 ec_utils.get_tran_stage_data
826 (
827 i_transaction_type,
828 i_map_id
829 );
830
831 ec_execution_utils.load_mappings
832 (
833 i_transaction_type,
834 i_map_id
835 );
836
837 ec_utils.sort_stage_data;
838
839 ec_utils.find_pos
840 (
841 1,
842 ec_utils.g_int_levels(1).key_column_name,
843 i_plsql_pos,
844 TRUE
845 );
846
847 g_key_column_pos := i_plsql_pos;
848
849 ec_utils.i_stage_data := ec_utils.i_tmp_stage_data; -- 2920679
850
851 ec_utils.execute_stage_data
852 (
853 10,
854 0
855 );
856
857 load_objects;
858
859 fetch_data_from_view (0);
860 if ec_debug.G_debug_level >= 1 then
861 for i in 1..ec_utils.g_int_levels.COUNT
862 loop
863 ec_debug.pl(1,ec_utils.g_int_levels(i).rows_processed||' row(s) processed for Level '||i);
864 end loop;
865 end if;
866 ec_debug.pl(0,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',ec_utils.g_int_levels(1).rows_processed);
867
868 close_outbound;
869 if ec_debug.G_debug_level >= 2 then
870 ec_debug.pop('EC_OUTBOUND.PROCESS_OUTBOUND_DOCUMENTS');
871 end if;
872 ec_debug.pl(0,'EC','ECE_FINISH_OUTBOUND','TRANSACTION_TYPE',i_transaction_type,'MAP_ID',i_map_id);
873 EXCEPTION
874 WHEN EC_UTILS.PROGRAM_EXIT then
875 raise;
876 WHEN OTHERS THEN
877 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.PROCESS_OUTBOUND_DOCUMENTS');
878 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
879 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
880 close_outbound;
881 ec_debug.pop('EC_OUTBOUND.PROCESS_OUTBOUND_DOCUMENTS');
882 raise EC_UTILS.PROGRAM_EXIT;
883 end process_outbound_documents;
884
885 /**
886 This file will delete all records in a staging table without using the
887 expense parsing of dbms_sql package. The RUN_ID parameter is the only required parameter.
888 The DOCUMENT_ID parameter can be optionally used to delete one document from the staging table
889 at a time.
890 **/
891 procedure delete_stage_data
892 (
893 i_run_id IN number,
894 i_document_id IN number DEFAULT NULL
895 ) IS
896 BEGIN
897 if ec_debug.G_debug_level >= 2 then
898 ec_debug.push('EC_OUTBOUND.DELETE_STAGE_DATA');
899 ec_debug.pl(3,'i_run_id',i_run_id);
900 ec_debug.pl(3,'i_document_id',i_document_id);
901 end if;
902 IF i_run_id IS NULL THEN
903 ec_debug.pl(0,'EC','ECE_PARAM_MISSING','PARAMETER','I_RUN_ID', 'PROCEDURE','EC_OUTBOUND.DELETE_STAGE_DATA');
904 /**
905 Set the FAILURE Retcode for the Concurrent Manager
906 **/
907 EC_UTILS.i_ret_code := 2;
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);
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);
928
929 ec_debug.pop('EC_OUTBOUND.DELETE_STAGE_DATA');
930 end if;
931 EXCEPTION
932 WHEN EC_UTILS.PROGRAM_EXIT then
933 raise;
934 WHEN OTHERS THEN
935 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.DELETE_STAGE_DATA');
936 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
937 ec_utils.i_ret_code:=2;
938 raise EC_UTILS.PROGRAM_EXIT;
939 END delete_stage_data;
940
941 end ec_outbound;