1 PACKAGE BODY EC_OUTBOUND_STAGE AS
2 -- $Header: ECOSTGB.pls 120.2.12000000.2 2007/03/09 14:41:15 cpeixoto ship $
3 --bug 3133379
4 TYPE STAGE_ID_TYPE is table of ece_stage.stage_id%TYPE index by binary_integer;
5 TYPE TRANSACTION_LEVEL_TYPE is table of ece_stage.transaction_level%TYPE index by binary_integer;
6 B_STAGE_ID STAGE_ID_TYPE;
7 B_TRANSACTION_LEVEL TRANSACTION_LEVEL_TYPE;
8 --bug 3133379
9 --- Local PL/SQL table variables.
10 i_stage_record_type ec_utils.mapping_tbl;
11 i_record_info Record_Info;
12 i_level_info Level_Info;
13
14 vPath varchar2(1000);
15 vFileName varchar2(1000);
16
17 /**
18 This is the Main Staging Program.
19 For a given transaction, and the Outbound File information i.e. File name
20 and File Path , it extracts the Staging table data into the Flat File. There is no
21 checking done for the data, and it is extracted according to the Mapping
22 information seeded for a transaction.
23 **/
24 PROCEDURE Get_Data
25 (
26 i_transaction_type IN varchar2,
27 i_file_name IN varchar2,
28 i_file_path IN varchar2,
29 i_map_id IN number,
30 i_run_id IN number
31 )
32 is
33
34 cursor c_level_info
35 (
36 p_transaction_type varchar2,
37 p_map_id number
38 )
39 is
40 select eel.start_element,
41 eit.interface_table_id,
42 eit.key_column_name,
43 eit.primary_address_type
44 from ece_interface_tables eit,
45 ece_level_matrices elm,
46 ece_external_levels eel
47 where eit.transaction_type = p_transaction_type
48 and eit.interface_table_id = elm.interface_table_id
49 and elm.external_level_id = eel.external_level_id
50 and eel.map_id = p_map_id
51 order by to_number(external_level);
52
53 cursor c_record_info
54 (
55 p_transaction_type varchar2,
56 p_map_id number
57 )
58 is
59 select DISTINCT(eic.record_number) record_number
60 , eel.external_level external_level
61 , eel.start_element start_element
62 , COUNT(*) counter
63 from ece_interface_tables eit,
64 ece_interface_columns eic,
65 ece_level_matrices elm,
66 ece_external_levels eel
67 where eit.transaction_type = p_transaction_type
68 and eic.interface_table_id = eit.interface_table_id
69 and eit.interface_table_id = elm.interface_table_id
70 and elm.external_level_id = eel.external_level_id
71 and eic.record_number IS NOT NULL
72 and eic.position IS NOT NULL
73 and eel.map_id = p_map_id
74 group by eel.external_level, eic.record_number, eel.start_element
75 order by eel.external_level, eic.record_number;
76
77 i_level number :=0;
78 i_record number :=0;
79 l_next_file_pos number :=0;
80
81 i_stage_cursor number :=0;
82 l_common_key varchar2(2000);
83
84 i_empty_tbl ec_utils.mapping_tbl;
85
86 BEGIN
87 if ec_debug.G_debug_level >= 2 then
88 ec_debug.push('EC_OUTBOUND_STAGE.GET_DATA');
89 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
90 ec_debug.pl(3,'i_file_name',i_file_name);
91 ec_debug.pl(3,'i_file_path',i_file_path);
92 ec_debug.pl(3,'i_map_id',i_map_id);
93 ec_debug.pl(3,'i_run_id',i_run_id);
94 end if;
95
96 -- Initialize PL/SQL tables.
97 i_level_info.DELETE;
98 i_stage_record_type.DELETE;
99 -- ece_flatfile_pvt.t_tran_attribute_tbl.DELETE;
100
101 -- Load the Output Definition for all Record_Number's of a Transaction
102 FOR transaction_level in c_level_info
103 (
104 p_transaction_type => i_transaction_type,
105 p_map_id => i_map_id
106 )
107 LOOP
108 i_level := i_level + 1;
109 i_level_info(i_level).start_record_number := TO_NUMBER(transaction_level.start_element);
110 i_level_info(i_level).Key_Column_Name := transaction_level.key_column_name;
111 i_level_info(i_level).Key_Column_Staging := transaction_level.primary_address_type;
112 i_level_info(i_level).primary_address_type := transaction_level.primary_address_type;
113 if ec_debug.G_debug_level = 3 then
114 ec_debug.pl(3,'Key Column Name',i_level_info(i_level).Key_Column_Name);
115 ec_debug.pl(3,'Key Column Name',i_level_info(i_level).Key_Column_Name);
116 end if;
117
118 /**
119 Set the Initial Values for each Level
120 i_level_info(i_level).Select_Cursor := 0;
121 i_level_info(i_level).Document_Number := NULL;
122 i_level_info(i_level).Status := 'NEW';
123 **/
124 i_level_info(i_level).Transaction_Type := i_transaction_type;
125 i_level_info(i_level).Run_Id := i_run_id;
126 i_level_info(i_level).Key_Column_Position := NULL;
127 i_level_info(i_level).Key_Column_Staging := NULL;
128 i_level_info(i_level).tp_code_staging := NULL;
129
130 if ec_debug.G_debug_level = 3 then
131 ec_debug.pl(3,'EC','ECE_STAGE_START_RECORD_NUMBER','LEVEL',i_level,'START_RECORD_NUMBER',
132 i_level_info(i_level).start_record_number);
133 end if;
134 /**
135 Load the Mapping Information between Flat File and Staging Fields
136 **/
137 populate_flatfile_mapping
138 (
139 i_transaction_type,
140 i_level,
141 i_map_id
142 );
143
144 END LOOP;
145
146 /**
147 Make a copy of the PL/SQL table and save it. After Inserting the Data into the
148 staging table , initialize the PL/SQL table with values from saved PL/SQL table.
149 **/
150 i_empty_tbl := i_stage_record_type;
151
152 /**
153 Check for Seed Data. If not Found then , then do not process.
154 **/
155 if i_level = 0
156 then
157 ec_debug.pl(0,'EC','ECE_SEED_DATA_MISSING','TRANSACTION_TYPE',i_transaction_type);
158 /**
159 Set the Retcode for the Concurrent Manager
160 **/
161 ec_utils.i_ret_code := 2;
162 raise ec_utils.program_exit;
163 end if;
164
165 /**
166 Build each record's SELECT statement
167 **/
168 l_next_file_pos :=1;
169 FOR record_level in c_record_info
170 (
171 p_transaction_type => i_transaction_type,
172 p_map_id => i_map_id
173 )
174 LOOP
175 i_record := i_record + 1;
176 i_record_info(i_record).record_number := record_level.record_number;
177 i_record_info(i_record).external_level := record_level.external_level;
178 i_record_info(i_record).start_record_number := TO_NUMBER(record_level.external_level);
179 i_record_info(i_record).counter := record_level.counter;
180
181 get_select_stmt(record_level.external_level,
182 i_record_info(i_record).record_number,
183 i_record,
184 l_next_file_pos,
185 i_record_info(i_record).counter);
186
187
188 END LOOP;
189
190 --- operation moved to inside the Select_From_Stage_Table
191 --- Open the Outbound Transaction File in the Write Mode
192 --- u_file_handle := utl_file.fopen(i_file_path,i_file_name,'w', 3000);
193
194 vPath := i_file_path;
195 vFileName:= i_file_name;
196
197
198 /**
199 Extract staging information to flat file starting with the top external level
200 **/
201 Fetch_Stage_Data
202 (
203 i_transaction_type,
204 i_run_id,
205 0,
206 i_stage_cursor,
207 l_common_key
208 );
209
210 -- Close the Outbound Transaction File
211 if (utl_file.is_open(u_file_handle)) then
212 utl_file.fclose(u_file_handle);
213 end if;
214
215 /**
216 The Cursors for the Select From Stage table are not closed in the Select_From_Stage_table
217 procedure call. Since the Cursor handles are maintained in the I_LEVEL_INFO PL/SQL table,
218 Cursors for the all Levels are closed using these Cursor handles.
219 **/
220 FOR i in 1..i_level_info.COUNT
221 LOOP
222 IF dbms_sql.IS_OPEN(i_level_info(i).Select_Cursor)
223 THEN
224 dbms_sql.Close_cursor(i_level_info(i).Select_Cursor);
225 END IF;
226 END LOOP;
227
228 /**
229 Delete all records from the staging table
230 **/
231 ec_outbound.delete_stage_data
232 (
233 i_run_id,
234 NULL
235 );
236
237 ec_debug.pl(0,'EC','ECE_NO_LINES_READ','NO_OF_LINES',counter);
238 <<stage_over>>
239 ec_debug.pl(0,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',i_document_number);
240 if ec_debug.G_debug_level >= 2 then
241 ec_debug.pop('EC_OUTBOUND_STAGE.GET_DATA');
242 end if;
243 EXCEPTION
244 WHEN UTL_FILE.write_error THEN
245 ec_utils.i_ret_code :=2;
246 ec_debug.pl(0,'EC','ECE_UTL_WRITE_ERROR',null);
247 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
248 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
249 utl_file.fclose(u_file_handle);
250 raise EC_UTILS.PROGRAM_EXIT;
251 WHEN UTL_FILE.read_error THEN
252 ec_utils.i_ret_code :=2;
253 ec_debug.pl(0,'EC','ECE_UTL_READ_ERROR',null);
254 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
255 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
256 utl_file.fclose(u_file_handle);
257 raise EC_UTILS.PROGRAM_EXIT;
258 WHEN UTL_FILE.invalid_path THEN
259 ec_utils.i_ret_code :=2;
260 ec_debug.pl(0,'EC','ECE_UTL_INVALID_PATH',null);
261 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
262 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
263 utl_file.fclose(u_file_handle);
264 raise EC_UTILS.PROGRAM_EXIT;
265 WHEN UTL_FILE.invalid_mode THEN
266 ec_utils.i_ret_code :=2;
267 ec_debug.pl(0,'EC','ECE_UTL_INVALID_MODE',null);
268 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
269 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
270 utl_file.fclose(u_file_handle);
271 raise EC_UTILS.PROGRAM_EXIT;
272 WHEN UTL_FILE.invalid_operation THEN
273 ec_utils.i_ret_code :=2;
274 ec_debug.pl(0,'EC','ECE_UTL_INVALID_OPERATION',null);
275 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
276 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
277 utl_file.fclose(u_file_handle);
278 raise EC_UTILS.PROGRAM_EXIT;
279 WHEN UTL_FILE.invalid_filehandle THEN
280 ec_utils.i_ret_code :=2;
281 ec_debug.pl(0,'EC','ECE_UTL_INVALID_FILEHANDLE',null);
282 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
283 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
284 utl_file.fclose(u_file_handle);
285 raise EC_UTILS.PROGRAM_EXIT;
286 WHEN UTL_FILE.internal_error THEN
287 ec_utils.i_ret_code :=2;
288 ec_debug.pl(0,'EC','ECE_UTL_INTERNAL_ERROR',null);
289 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
290 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
291 utl_file.fclose(u_file_handle);
292 raise EC_UTILS.PROGRAM_EXIT;
293 WHEN EC_UTILS.PROGRAM_EXIT THEN
294 raise;
295 WHEN OTHERS THEN
296 ec_utils.i_ret_code :=2;
297 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_DATA');
298 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
299 utl_file.fclose(u_file_handle);
300 raise EC_UTILS.PROGRAM_EXIT;
301 END Get_Data;
302
303 /**
304 This procedures fetches the staging data in the proper hierarchecal order by recursively
305 calling itself using the current records STAGE_ID = PARENT_STAGE_ID. It also calls the
306 procedures to format the common key and populate the flat file with the stage data.
307 Calling this procedure recursively guarantees that the flat file will be formatted in order
308 as long as the relationship between the STAGE_ID and the PARENT_STAGE_ID is populated
309 correctly by the OUTBOUND ENGINE regardless of the order they were populated.
310 **/
311 PROCEDURE Fetch_Stage_Data
312 (
313 i_transaction_type IN varchar2,
314 i_run_id IN number,
315 i_parent_stage_id IN number,
316 i_stage_cursor IN OUT NOCOPY number,
317 i_common_key IN OUT NOCOPY varchar2
318 ) AS
319 Cursor cur_stage(p_transaction_type IN varchar2,
320 p_run_id IN number
321 )
322 IS
323 SELECT STAGE_ID,TRANSACTION_LEVEL
324 FROM ECE_STAGE
325 WHERE TRANSACTION_TYPE = p_transaction_type
326 AND RUN_ID = p_run_id
327 ORDER BY STAGE_ID; -- bug 3133379
328
329 --cSelect_stmt varchar2(1000) := 'SELECT STAGE_ID, TRANSACTION_LEVEL';
330 --cFrom_stmt varchar2(1000) := ' FROM ECE_STAGE';
331 --cWhere_stmt varchar2(1000) := ' WHERE TRANSACTION_TYPE = :a1 AND RUN_ID = :a2 order by stage_id'; --2457262
332 i_select_cursor INTEGER := 0;
333 dummy INTEGER;
334 error_position INTEGER;
335 v_parent_stage_id number;
336 v_stage_id number;
337 v_transaction_level ece_stage.transaction_level%TYPE;
338 i_new_stage_cursor number := 0;
339 BEGIN
340 if ec_debug.G_debug_level >= 2 then
341 ec_debug.push('EC_OUTBOUND_STAGE.FETCH_STAGE_DATA');
342 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
343 ec_debug.pl(3,'i_run_id',i_run_id);
344 ec_debug.pl(3,'i_parent_stage_id',i_parent_stage_id);
345 ec_debug.pl(3,'i_stage_cursor',i_stage_cursor);
346 end if;
347 /* Implemented bulk collect for performance improvement. bug 3133379 */
348 OPEN cur_stage(i_transaction_type,i_run_id);
349 loop
350 FETCH cur_stage BULK COLLECT INTO B_STAGE_ID,B_TRANSACTION_LEVEL limit 1000;
351 EXIT WHEN B_STAGE_ID.COUNT =0;
352 FOR i IN B_STAGE_ID.FIRST .. B_STAGE_ID.LAST
353 LOOP
354
355 IF b_transaction_level(i) = 1
356 THEN
357 i_document_number := i_document_number + 1;
358
359 END IF;
360 counter := counter + 1;
361 i_select_cursor := NVL(i_level_info(b_transaction_level(i)).select_cursor,0);
362
363 Select_From_Stage_Table(
364 B_TRANSACTION_LEVEL(i),
365 B_STAGE_ID(i),
366 i_select_cursor,
367 i_common_key
368 );
369 if ec_debug.G_debug_level = 3 then
370 ec_debug.pl(3,'b_stage_id',b_stage_id(i));
371 ec_debug.pl(3,'b_transaction_level',b_transaction_level(i));
372 end if;
373
374 END LOOP;
375 b_stage_id.delete;
376 b_transaction_level.delete;
377 --EXIT WHEN cur_stage%NOTFOUND;
378 END LOOP;
379 CLOSE cur_stage;
380 --cSelect_stmt := cSelect_stmt||cFrom_stmt||cWhere_stmt;
381
382 /**
383 Open the cursor and parse the SQL Statement. Trap any parsing error and report
384 the Error Position in the SQL Statement
385 **/
386 --i_stage_cursor := dbms_sql.Open_Cursor;
387 -- BEGIN
388 -- dbms_sql.parse(i_stage_cursor,cSelect_stmt,dbms_sql.native);
389 -- EXCEPTION
390 -- WHEN OTHERS THEN
391 -- error_position := dbms_sql.last_error_position;
392 -- ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.FETCH_STAGE_DATA');
393 -- ece_error_handling_pvt.print_parse_error (error_position,cSelect_stmt);
394 -- EC_UTILS.i_ret_code :=2;
395 -- raise EC_UTILS.PROGRAM_EXIT;
396 -- END;
397
398 /**
399 Bind values
400 **/
401 --dbms_sql.bind_variable(i_stage_cursor,'a1',i_transaction_type);
402 --dbms_sql.bind_variable(i_stage_cursor,'a2',i_run_id);
403 --dbms_sql.bind_variable(i_stage_cursor,'a3',i_parent_stage_id);
404
405 /**
406 Define the column for return string
407 **/
408 /** dbms_sql.define_column(i_stage_cursor,1,v_stage_id);
409 dbms_sql.define_column(i_stage_cursor,2,v_parent_stage_id);
410 dbms_sql.define_column(i_stage_cursor,3,v_transaction_level);
411 **/
412 /**
413 Execute the cursor; debug on the number of rows returned
414 **/
415 /** BEGIN
416 dummy := dbms_sql.execute(i_stage_cursor);
417 if ec_debug.G_debug_level = 3 then
418 ec_debug.pl(3,'EC','ECE_STAGE_SELECTED',NULL);
419 ec_debug.pl(3,'i_stage_cursor', i_stage_cursor);
420 ec_debug.pl(3,cSelect_stmt);
421 end if;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL', 'EC_OUTBOUND_STAGE.FETCH_STAGE_DATA');
426 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
427 ec_debug.pl(0,'EC','ECE_ERROR_SQL',null);
428 ec_debug.pl(0,cSelect_stmt);
429 EC_UTILS.i_ret_code :=2;
430 raise EC_UTILS.PROGRAM_EXIT;
431 END; **/
432
433 /** BEGIN
434 WHILE dbms_sql.fetch_rows(i_stage_cursor) > 0
435 LOOP
436 dbms_sql.column_value(i_stage_cursor,1,v_stage_id);
437 dbms_sql.column_value(i_stage_cursor,2,v_parent_stage_id);
438 dbms_sql.column_value(i_stage_cursor,3,v_transaction_level);
439 if ec_debug.G_debug_level = 3 then
440 ec_debug.pl(3,'v_stage_id',v_stage_id);
441 ec_debug.pl(3,'v_parent_stage_id',v_parent_stage_id);
442 ec_debug.pl(3,'v_transaction_level',v_transaction_level);
443 end if;
444
445
446 if ec_debug.G_debug_level = 3 then
447 ec_debug.pl(3,'i_level_info(v_transaction_level).select_cursor',i_level_info(v_transaction_level).select_cursor);
448 end if;
449
450 Select_From_Stage_Table(
451 v_transaction_level,
452 v_stage_id,
453 i_select_cursor,
454 i_common_key
455 );
456
457 Fetch_Stage_Data(
458 i_transaction_type,
459 i_run_id,
460 v_stage_id,
461 i_new_stage_cursor,
462 i_common_key);
463 END LOOP;
464 EXCEPTION
465 WHEN OTHERS THEN
466 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL', 'EC_OUTBOUND_STAGE.FETCH_STAGE_DATA');
467 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
468 EC_UTILS.i_ret_code :=2;
469 raise EC_UTILS.PROGRAM_EXIT;
470 END;
471 **/
472 /**
473 Close Cursor
474 **/
475 -- dbms_sql.close_cursor(i_stage_cursor);
476 if ec_debug.G_debug_level >= 2 then
477 ec_debug.pop('EC_OUTBOUND_STAGE.FETCH_STAGE_DATA');
478 end if;
479
480 EXCEPTION
481 WHEN EC_UTILS.PROGRAM_EXIT THEN
482 raise;
483 WHEN OTHERS THEN
484 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.FETCH_STAGE_DATA');
485 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
486 EC_UTILS.i_ret_code:=2;
487 raise EC_UTILS.PROGRAM_EXIT;
488 END Fetch_Stage_Data;
489
490 /**
491 This procedures loads the mapping information between the Flat File
492 and the Staging table. This information is seeded in the ECE_INTERFACE_TABLES
493 and ECE_INTERFACE_COLUMNS. The mapping information is loaded into the Local Body
494 PL/SQL table variable for a given transaction Type, record_number and level.
495 This PL/SQL table loaded with Mapping information is visible only to the functions
496 and procedures defined within this package.
497 **/
498 procedure populate_flatfile_mapping
499 (
500 i_transaction_type in varchar2,
501 i_level in number,
502 i_map_id IN number
503 )
504 is
505 cursor c_file_mapping
506 (
507 p_transaction_type varchar2,
508 p_level number,
509 p_map_id number
510 ) is
511 SELECT eic.interface_table_id,
512 eic.interface_column_name,
513 eic.staging_column,
514 eic.record_number,
515 eic.record_layout_code,
516 eic.record_layout_qualifier,
517 eic.data_type,
518 eic.position,
519 eic.width
520 FROM ece_interface_tables eit,
521 ece_level_matrices elm,
522 ece_external_levels eel,
523 ece_interface_columns eic
524 WHERE eit.interface_table_id = eic.interface_table_id
525 AND eit.transaction_type = p_transaction_type
526 AND eic.external_level = p_level
527 AND eit.interface_table_id = elm.interface_table_id
528 AND elm.external_level_id = eel.external_level_id
529 AND eel.map_id = p_map_id
530 and eic.record_number IS NOT NULL
531 and eic.position IS NOT NULL
532 ORDER BY eic.record_number, eic.position;
533 i_counter NUMBER :=i_stage_record_type.COUNT;
534 m_counter number := i_counter;
535 b_tp_found BOOLEAN := FALSE;
536 BEGIN
537 if ec_debug.G_debug_level >= 2 then
538 ec_debug.push('EC_OUTBOUND_STAGE.POPULATE_FLATFILE_MAPPING');
539 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
540 ec_debug.pl(3,'i_Level',i_level);
541 ec_debug.pl(3,'EC','ECE_INTERFACE_MAPPING','TRANSACTION_TYPE',i_transaction_type,'LEVEL',i_level);
542 end if;
543
544 FOR transaction_mapping in c_file_mapping
545 (
546 p_transaction_type => i_transaction_type,
547 p_level => i_level,
548 p_map_id => i_map_id
549 )
550 LOOP
551 i_counter := i_counter + 1;
552 i_stage_record_type(i_counter).interface_level := i_level;
553 i_stage_record_type(i_counter).interface_column_name := transaction_mapping.interface_column_name;
554 i_stage_record_type(i_counter).staging_column := transaction_mapping.staging_column;
555 i_stage_record_type(i_counter).record_number := transaction_mapping.record_number;
556 i_stage_record_type(i_counter).record_layout_code := transaction_mapping.record_layout_code;
557 i_stage_record_type(i_counter).record_layout_qualifier := transaction_mapping.record_layout_qualifier;
558 i_stage_record_type(i_counter).data_type := transaction_mapping.data_type;
559 i_stage_record_type(i_counter).position := transaction_mapping.position;
560 i_stage_record_type(i_counter).width := transaction_mapping.width;
561 if ec_debug.G_debug_level = 3 then
562 ec_debug.pl
563 (
564 3,
565 i_counter||'|'||
566 i_level||'|'||
567 transaction_mapping.interface_column_name||'|'||
568 transaction_mapping.staging_column||'|'||
569 transaction_mapping.record_number||'|'||
570 transaction_mapping.record_layout_code||'|'||
571 transaction_mapping.record_layout_qualifier||'|'||
572 transaction_mapping.position||'|'||
573 transaction_mapping.width||'|'||
574 transaction_mapping.data_type
575 );
576 end if;
577 IF upper(i_stage_record_type(i_counter).interface_column_name) =
578 i_level_info(i_level).key_column_name
579 THEN
580 i_level_info(i_level).key_column_position := i_counter;
581 i_level_info(i_level).key_column_staging := i_stage_record_type(i_counter).staging_column;
582 if ec_debug.G_debug_level = 3 then
583 ec_debug.pl
584 (3,
585 'Key_Column',
586 i_level_info(i_level).Key_Column_position||'|'||
587 i_level_info(i_level).Key_Column_position
588 );
589 end if;
590 END IF;
591
592 IF i_level = 1 AND
593 b_tp_found = FALSE AND
594 UPPER(i_stage_record_type(i_counter).interface_column_name) = 'TP_TRANSLATOR_CODE'
595 THEN
596 -- ece_flatfile_pvt.t_tran_attribute_tbl(1).key_column_name
597 -- := i_level_info(1).Key_column_name;
598 -- ece_flatfile_pvt.t_tran_attribute_tbl(1).position
599 -- := i_counter;
600 i_level_info(1).tp_code_staging := i_stage_record_type(i_counter).staging_column;
601 if ec_debug.G_debug_level = 3 then
602 ec_debug.pl
603 (3,
604 'tp_code_staging',
605 i_stage_record_type(i_counter).staging_column
606 );
607 end if;
608 b_tp_found := TRUE;
609 END IF;
610
611 END LOOP;
612
613 if i_counter = m_counter then
614 ec_debug.pl(0,'EC','ECE_SEED_NOT_LEVEL','TRANSACTION_TYPE',i_transaction_type,'LEVEL',i_level);
615 /**
616 Set the Retcode for the Concurrent Manager to Error.
617 **/
618 ec_utils.i_ret_code := 2;
619 raise EC_UTILS.PROGRAM_EXIT;
620 end if;
621 if ec_debug.G_debug_level >= 2 then
622 ec_debug.pop('EC_OUTBOUND_STAGE.POPULATE_FLATFILE_MAPPING');
623 end if;
624 EXCEPTION
625 WHEN EC_UTILS.PROGRAM_EXIT then
626 raise;
627 WHEN OTHERS then
628 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.POPULATE_FLATFILE_MAPPING');
629 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
630 ec_utils.i_ret_code:=2;
631 raise ec_utils.program_exit;
632 END populate_flatfile_mapping;
633
634 /**
635 This procedure formats the main body of a SELECT statement for each record number of
636 a given transaction and saves the result in a local PL/SQL table for later parsing.
637 This procedure is called once for each record number regardless of the number of
638 columns in the staging table in order to save on the number of PL/SQL string operations
639 required
640 **/
641 PROCEDURE get_select_stmt
642 (
643 i_current_level IN NUMBER,
644 i_record_num IN number,
645 i_file_pos IN number,
646 i_next_file_pos IN OUT NOCOPY number,
647 i_total_rec_unit IN number
648 )
649 IS
650 i_rec_cd ece_interface_columns.record_layout_code%TYPE;
651 i_rec_ql ece_interface_columns.record_layout_qualifier%TYPE;
652 c_local_chr_39 VARCHAR2(1) := fnd_global.local_chr(39);
653 BEGIN
654 if ec_debug.G_debug_level >= 2 then
655 ec_debug.push('EC_OUTBOUND_STAGE.GET_SELECT_STMT');
656 ec_debug.pl(3,'i_current_level',i_current_level);
657 ec_debug.pl(3,'record_number',i_record_num);
658 ec_debug.pl(3,'i_file_pos',i_file_pos);
659 ec_debug.pl(3,'counter',i_total_rec_unit);
660 ec_debug.pl(3,'i_next_file_pos',i_next_file_pos);
661 end if;
662
663 /**
664 Build Application Data SELECT statement
665 **/
666 FOR k IN i_next_file_pos..i_next_file_pos+i_total_rec_unit
667 LOOP
668 IF i_next_file_pos <= i_stage_record_type.count
669 THEN
670 -- ec_debug.pl(3,'k:interface_level',k||'|'||i_stage_record_type(k).interface_level);
671 -- ec_debug.pl(3,'k:external_level',k||'|'||i_record_info(i_file_pos).external_level);
672 IF i_stage_record_type(k).interface_level = i_record_info(i_file_pos).external_level AND
673 i_stage_record_type(k).record_number = i_record_info(i_file_pos).record_number
674 THEN
675 -- ec_debug.pl(3,'i_next_file_pos',i_next_file_pos);
676 -- ec_debug.pl(3,'staging_column',i_stage_record_type(k).staging_column);
677 i_rec_cd := i_stage_record_type(k).record_layout_code;
678 i_rec_ql := i_stage_record_type(k).record_layout_qualifier;
679 i_record_info(i_file_pos).select_stmt := i_record_info(i_file_pos).select_stmt||
680 '||RPAD(NVL('||
681 NVL(i_stage_record_type(k).staging_column,'NULL')||
682 ','||
683 c_local_chr_39 ||g_rec_appd_fl||c_local_chr_39||
684 '),'||
685 i_stage_record_type(k).width||
686 ','||
687 c_local_chr_39||g_rec_appd_fl||c_local_chr_39||
688 ')';
689 i_next_file_pos := i_next_file_pos + 1;
690 ELSE
691 exit;
692 END IF;
693 ELSE
694 exit;
695 END IF;
696 END LOOP;
697
698 /**
699 Add LAST record number/code/qualifier found - should be seeded consistently or
700 the code/qualier values will not match up exactly with what is in the data repository
701 **/
702 i_record_info(i_file_pos).select_stmt :=
703 c_local_chr_39||
704 LPAD(NVL(TO_CHAR(i_record_info(i_file_pos).record_number),g_rec_num_fl),
705 g_rec_num_ln, g_rec_num_fl)||c_local_chr_39||'||'||
706 c_local_chr_39||
707 RPAD(NVL(i_rec_cd, g_rec_lcd_fl),g_rec_lcd_ln, g_rec_lcd_fl)||
708 c_local_chr_39||'||'||
709 c_local_chr_39||RPAD(NVL(i_rec_ql, g_rec_lql_fl),
710 g_rec_lql_ln, g_rec_lql_fl)||c_local_chr_39||
711 i_record_info(i_file_pos).select_stmt;
712 if ec_debug.G_debug_level >= 2 then
713 ec_debug.pl(3,i_file_pos||'|'||
714 i_record_info(i_file_pos).record_number||'|'||
715 i_record_info(i_file_pos).select_stmt);
716 ec_debug.pop('EC_OUTBOUND_STAGE.GET_SELECT_STMT');
717 end if;
718 EXCEPTION
719 WHEN OTHERS then
720 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.GET_SELECT_STMT');
721 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
722 ec_utils.i_ret_code:=2;
723 raise EC_UTILS.PROGRAM_EXIT;
724 END get_select_stmt;
725
726 /**
727 The Data is extracted from the Staging table using loaded in the Local PL/SQL table.
728 This procedures uses Transaction Level and cursor handle as parameters to parse a SQL
729 statement.
730 The Cursor handle is passed as 0 in the First call , and the subsequent calls
731 use the Cursor Handle returned by the Procedure. This helps in avoiding the
732 expensive parsing of the SQL Statement again and again for the Same level.
733 **/
734 procedure Select_From_Stage_Table
735 (
736 i_level IN NUMBER,
737 i_stage_id IN NUMBER,
738 i_select_cursor IN OUT NOCOPY NUMBER,
739 i_common_key IN OUT NOCOPY VARCHAR2
740 )
741 is
742 cSelect_stmt varchar2(32000) := 'SELECT ';
743 cFrom_stmt varchar2(100) := ' FROM ECE_STAGE';
744 cWhere_stmt varchar2(100) := ' WHERE STAGE_ID = :a1';
745
746 TYPE t_dummy IS TABLE OF varchar(2000) INDEX BY BINARY_INTEGER;
747 v_dummy t_dummy;
748 v_dummy_tp_code varchar2(2000);
749 v_dummy_key_staging varchar2(2000);
750
751 i_select_count INTEGER := 0;
752 i_next_common_key varchar2(2000) := NULL;
753 i_current_common_key varchar2(2000) := NULL;
754 dummy INTEGER;
755 error_position integer;
756
757 BEGIN
758 if ec_debug.G_debug_level >= 2 then
759 ec_debug.push('EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
760 ec_debug.pl(3,'i_level',i_level);
761 ec_debug.pl(3,'i_stage_id',i_stage_id);
762 ec_debug.pl(3,'i_select_cursor',i_select_cursor);
763 end if;
764
765 if i_select_cursor = 0
766 then
767 i_select_cursor := -911;
768 end if;
769
770 if i_select_cursor < 0
771 then
772 cSelect_stmt := cSelect_stmt||
773 NVL(i_level_info(i_level).tp_code_staging,'NULL')||
774 ','||
775 NVL(i_level_info(i_level).Key_Column_Staging,'NULL')||
776 ',';
777
778 FOR k IN 1..i_record_info.count
779 LOOP
780 -- ec_debug.pl(3,'k:external_level',k||'|'||i_record_info(k).external_level);
781 IF i_record_info(k).external_level = i_level
782 THEN
783 /**
784 Get Select Statement from PLSQL table
785 **/
786 i_select_count := i_select_count + 1;
787 cSelect_stmt := cSelect_stmt||
788 i_record_info(k).select_stmt||
789 ',';
790 END IF;
791 END LOOP;
792
793 cSelect_stmt := RTRIM(cSelect_stmt,',');
794 cSelect_stmt := cSelect_stmt||cFrom_stmt||cWhere_stmt;
795 if ec_debug.G_debug_level = 3 then
796 ec_debug.pl(3,'cSelect_stmt',cSelect_stmt);
797 end if;
798 /**
799 Open the cursor and parse the SQL Statement. Trap any parsing error and report
800 the Error Position in the SQL Statement. Store cursor handle in PL/SQL table for
801 later use.
802 **/
803 i_select_cursor := dbms_sql.Open_Cursor;
804 BEGIN
805 dbms_sql.parse(i_select_cursor,cSelect_stmt,dbms_sql.native);
806 i_level_info(i_level).select_cursor := i_select_cursor;
807 i_level_info(i_level).total_records := i_select_count;
808 EXCEPTION
809 WHEN OTHERS THEN
810 error_position := dbms_sql.last_error_position;
811 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
812 ece_error_handling_pvt.print_parse_error (error_position,cSelect_stmt);
813 EC_UTILS.i_ret_code :=2;
814 raise EC_UTILS.PROGRAM_EXIT;
815 END;
816 if ec_debug.G_debug_level = 3 then
817 ec_debug.pl(3,'EC','ECE_STAGE_SELECT_LEVEL','LEVEL',i_level,null);
818 end if;
819 end if;
820
821 if i_select_cursor > 0
822 then
823
824 /**
825 Bind values for Primary Key
826 **/
827 dbms_sql.bind_variable(i_select_cursor,'a1',i_stage_id);
828 if ec_debug.G_debug_level = 3 then
829 ec_debug.pl(3,'STAGE_ID',i_stage_id);
830 end if;
831 /**
832 Define the columns for return string
833 **/
834 dbms_sql.define_column(i_select_cursor,1,v_dummy_tp_code,2000);
835 dbms_sql.define_column(i_select_cursor,2,v_dummy_key_staging,2000);
836 FOR n IN 3..i_level_info(i_level).total_records + 2
837 LOOP
838 v_dummy(n) := '';
839 dbms_sql.define_column(i_select_cursor,n,v_dummy(n),2000);
840 END LOOP;
841
842 /**
843 Execute the cursor; debug on the number of rows returned
844 **/
845 BEGIN
846 dummy := dbms_sql.execute(i_select_cursor);
847 if ec_debug.G_debug_level = 3 then
848 ec_debug.pl(3,'EC','ECE_STAGE_SELECTED',NULL);
849 ec_debug.pl(3,'i_select_cursor', i_select_cursor);
850 end if;
851 EXCEPTION
852 WHEN OTHERS THEN
853 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL', 'EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
854 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
855 ec_debug.pl(0,'EC','ECE_ERROR_SQL',null);
856 ec_debug.pl(0,cSelect_stmt);
857 EC_UTILS.i_ret_code :=2;
858 raise EC_UTILS.PROGRAM_EXIT;
859 END;
860
861 BEGIN
862 WHILE dbms_sql.fetch_rows(i_select_cursor) > 0
863 LOOP
864
865 /** You can comment out this call if you don't want the Common Key to be formatted on the flat file.
866 This is provides a performance boost due to the slow PL/SQL string operations required for the common key
867 **/
868 dbms_sql.column_value(i_select_cursor,1,v_dummy_tp_code);
869 dbms_sql.column_value(i_select_cursor,2,v_dummy_key_staging);
870 Select_Common_key(
871 i_level,
872 v_dummy_tp_code,
873 v_dummy_key_staging,
874 i_common_key
875 );
876 if (NOT utl_file.is_open(u_file_handle)) then
877 u_file_handle := utl_file.fopen(vPath,vFileName,'w', 3000);
878 end if;
879
880 FOR m IN 3..i_level_info(i_level).total_records + 2
881 LOOP
882 dbms_sql.column_value(i_select_cursor,m,v_dummy(m));
883 UTL_FILE.PUT_LINE(u_file_handle,i_common_key||v_dummy(m));
884 END LOOP;
885 END LOOP;
886 EXCEPTION
887 WHEN OTHERS THEN
888 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL', 'EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
889 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
890 EC_UTILS.i_ret_code :=2;
891 raise EC_UTILS.PROGRAM_EXIT;
892 END;
893
894 end if;
895 if ec_debug.G_debug_level >= 2 then
896 ec_debug.pl(3,'i_select_cursor',i_select_cursor);
897
898 ec_debug.pop('EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
899 end if;
900 EXCEPTION
901 WHEN EC_UTILS.PROGRAM_EXIT THEN
902 raise;
903 WHEN OTHERS THEN
904 IF dbms_sql.IS_OPEN(i_select_cursor)
905 then
906 dbms_sql.close_cursor(i_select_cursor);
907 end if;
908 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.SELECT_FROM_STAGE_TABLE');
909 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
910 EC_UTILS.i_ret_code:=2;
911 raise EC_UTILS.PROGRAM_EXIT;
912 END Select_From_Stage_Table;
913
914 /**
915 This procedure formats the common key for each level of a given transaction. It takes the previous common key
916 string and formats it according to the level before concatenting the new KEY COLUMN on to the end of it.
917 NOTE: all common key variables (eg: length, fill character) are defined as global variables and can be
918 changed if the common key specifications change or become parameters in future versions.
919 Additionally, the call to this procedure can be commented out if NO common key is desired. This provides a
920 modest increase in perfomance and a decrease in flat file size.
921 **/
922 procedure Select_Common_Key
923 (
924 i_level IN NUMBER,
925 i_tp_code IN VARCHAR2,
926 i_key_column IN VARCHAR2,
927 i_common_key IN OUT NOCOPY VARCHAR2
928 )
929 is
930 i_common_key_ln INTEGER := 0;
931 BEGIN
932 if ec_debug.G_debug_level >= 2 then
933 ec_debug.push('EC_OUTBOUND_STAGE.SELECT_COMMON_KEY');
934 ec_debug.pl(3,'i_level',i_level);
935 ec_debug.pl(3,'i_tp_code',i_tp_code);
936 ec_debug.pl(3,'i_key_column',i_key_column);
937 ec_debug.pl(3,'i_common_key',i_common_key);
938 end if;
939 IF i_level = 1
940 THEN
941 i_common_key := NULL;
942 /**
943 Build Common Key TP CODE SELECT
944 **/
945 i_common_key := RPAD(SUBSTRB(NVL(i_tp_code,g_tp_ckey_fl),1,g_tp_ckey_ln),g_tp_ckey_ln,g_tp_ckey_fl);
946 ELSE
947 /**
948 Trim off the value of the first common key in the level for later use
949 **/
950 i_common_key_ln := g_tp_ckey_ln + (i_level)*(g_ref_ckey_ln) - g_ref_ckey_ln;
951 i_common_key := SUBSTRB(RPAD(i_common_key,g_rec_ckey_ln,g_rec_ckey_fl),1,i_common_key_ln);
952 END IF;
953
954 i_common_key := i_common_key||RPAD(SUBSTRB(NVL(i_key_column,g_ref_ckey_fl),1,g_ref_ckey_ln),g_ref_ckey_ln,g_ref_ckey_fl);
955
956 i_common_key := RPAD(SUBSTRB(NVL(i_common_key,g_rec_ckey_fl),1,g_rec_ckey_ln),g_rec_ckey_ln,g_rec_ckey_fl);
957 if ec_debug.G_debug_level >= 2 then
958 ec_debug.pop('EC_OUTBOUND_STAGE.SELECT_COMMON_KEY');
959 end if;
960 EXCEPTION
961 WHEN OTHERS then
962 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND_STAGE.SELECT_COMMON_KEY');
963 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
964 ec_utils.i_ret_code:=2;
965 raise EC_UTILS.PROGRAM_EXIT;
966 END Select_Common_Key;
967
968 END EC_OUTBOUND_STAGE;
[Home] [Help]