1 PACKAGE BODY EC_INBOUND_STAGE AS
2 -- $Header: ECISTGB.pls 120.4 2005/09/29 10:38:49 arsriniv ship $
3
4 --- Local PL/SQL table variables.
5 --- i_stage_record_type Stage_Record_Type;
6 i_stage_record_type ec_utils.mapping_tbl;
7 i_level_info Level_Info;
8 --bug 2110652
9 i_db_charset varchar2(50);
10 i_db_charset_flag varchar2(1);
11 i_fnd_charset_flag varchar2(1);
12 --bug 2164672
13 i_data_status_flag boolean:= TRUE;
14
15 /**
16 This is the Main Staging Program.
17 For a given transaction , and the Inbound File information i.e. File name
18 and File Path , it loads the Flat File into the Staging table. There is no
19 checking done for the data , and is loaded according to the Mapping
20 information seeded for a transaction.
21 **/
22 PROCEDURE Load_Data
23 (
24 i_transaction_type IN varchar2,
25 i_file_name IN varchar2,
26 i_file_path IN varchar2,
27 i_map_id IN number,
28 i_run_id OUT NOCOPY number
29 )
30 is
31 cursor c_level_info
32 (
33 p_transaction_type varchar2,
34 p_map_id number
35 )
36 is
37 select eel.start_element,
38 eel.external_level,
39 eit.interface_table_id,
40 eit.key_column_name,
41 eit.primary_address_type
42 from ece_interface_tables eit,
43 ece_level_matrices elm,
44 ece_external_levels eel
45 where eit.transaction_type = p_transaction_type
46 and eit.interface_table_id = elm.interface_table_id
47 and elm.external_level_id = eel.external_level_id
48 and eel.map_id = p_map_id
49 order by to_number(external_level);
50
51 cursor seq_stage_id
52 is
53 select ece_stage_id_s.NEXTVAL
54 from dual;
55
56 cursor seq_document_id
57 is
58 select ece_document_id_s.NEXTVAL
59 from dual;
60
61 counter number :=0;
62 i_level number :=0;
63 l_file_pos number :=0;
64 l_next_file_pos NUMBER :=0;
65 l_total_rec_unit number :=0;
66 l_rec_number NUMBER;
67 u_file_handle utl_file.file_type;
68 c_current_line varchar2(2000);
69 v_next_rec_number varchar2(22);
70 next_rec_number number :=0;
71 skip_record_flag BOOLEAN := FALSE;
72 end_of_file BOOLEAN := FALSE;
73 i_valid_record BOOLEAN := FALSE;
74 match_found BOOLEAN := FALSE;
75 i_first_line_flag BOOLEAN := TRUE;
76 Document_Id number :=0;
77 i_current_level number :=1;
78 i_previous_level number :=0;
79 i_stage_id number :=0;
80 i_insert_cursor number :=0;
81 i_document_number number :=0;
82 i_empty_tbl ec_utils.mapping_tbl;
83 i_translator_code varchar2(400);
84 i_location_code varchar2(400);
85 i_tp_code varchar2(400);
86 i_translator_code_pos number;
87 i_location_code_pos number;
88
89 BEGIN
90 if EC_DEBUG.G_debug_level >= 2 then
91 ec_debug.push('EC_INBOUND_STAGE.LOAD_DATA');
92 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
93 ec_debug.pl(3,'i_file_name',i_file_name);
94 ec_debug.pl(3,'i_file_path',i_file_path);
95 ec_debug.pl(3,'i_map_id',i_map_id);
96
97 end if;
98 /**
99 This program uses two PL/SQL tables defined in the Spec of the Package.
100
101 i_level_info Stores the information about a particular level.
102 This table is updated after each record is read i.e.
103 Document Id , Stage Id , Line Number,Parent Stage id etc.
104 and is used while inserting a record in the Staging table for a level.
105
106 i_stage_record_type Stores the Mapping information for the Flat File
107 Level,Record Number,Position,Staging Column,Width etc.
108 **/
109
110 -- Initialize PL/SQL tables.
111 i_level_info.DELETE;
112 i_stage_record_type.DELETE;
113 ece_flatfile_pvt.t_tran_attribute_tbl.DELETE;
114
115 /**
116 If the program is run from SQLplus , the Concurrent Request id is
117 < 0. In this case , get the run id from ECE_OUTPUT_RUNS_S.NEXTVAL.
118 **/
119 i_run_id := fnd_global.conc_request_id;
120 if i_run_id <= 0
121 then
122 select ece_output_runs_s.NEXTVAL
123 into i_run_id
124 from dual;
125 end if;
126 if EC_DEBUG.G_debug_level = 3 then
127 ec_debug.pl(3,'i_run_id',i_run_id);
128 end if;
129 -- Load the Output Definition for all Levels of a Transaction
130
131 FOR transaction_level in c_level_info
132 (
133 p_transaction_type => i_transaction_type,
134 p_map_id => i_map_id
135 )
136 loop
137 i_level := transaction_level.external_level;
138
139 if (i_level <> i_previous_level) then
140 i_previous_level := i_level;
141 i_level_info(i_level).start_record_number := TO_NUMBER(transaction_level.start_element);
142 i_level_info(i_level).Key_Column_Name := transaction_level.key_column_name;
143 i_level_info(i_level).primary_address_type := transaction_level.primary_address_type;
144
145 if EC_DEBUG.G_debug_level >= 3 then
146 ec_debug.pl(3,'Key Column Name',i_level_info(i_level).Key_Column_Name);
147 end if;
148
149 /**
150 Set the Initial Values for each Level
151 **/
152
153 i_level_info(i_level).Document_Id := 0;
154 i_level_info(i_level).Line_Number := 0;
155 i_level_info(i_level).Parent_Stage_Id := 0;
156 i_level_info(i_level).Insert_Cursor := 0;
157 i_level_info(i_level).Stage_Id := 0;
158 i_level_info(i_level).Transaction_Type := i_transaction_type;
159 i_level_info(i_level).Run_Id := i_run_id;
160 i_level_info(i_level).Document_Number := NULL;
161 i_level_info(i_level).Status := 'NEW';
162 i_level_info(i_level).Key_Column_Position := NULL;
163 i_level_info(i_level).tp_code := NULL;
164
165 if EC_DEBUG.G_debug_level = 3 then
166 ec_debug.pl(3,'EC','ECE_STAGE_START_RECORD_NUMBER','LEVEL',i_level,'START_RECORD_NUMBER',
167 i_level_info(i_level).start_record_number);
168 end if;
169
170 /**
171 Load the Mapping Information between Flat File and Staging Fields
172 **/
173 populate_flatfile_mapping
174 (
175 i_transaction_type,
176 i_level,
177 i_map_id
178 );
179 end if;
180 end loop;
181
182 /*
183 Reset the i_previous_level = 0
184 */
185 i_previous_level := 0;
186
187 /**
188 Make a copy of the PL/SQL table and save it. After Inserting the Data into the
189 staging table , initialize the PL/SQL table with values from saved PL/SQL table.
190 **/
191 i_empty_tbl := i_stage_record_type;
192
193 /**
194 Check for Seed Data. If not Found then , then do not process.
195 **/
196 if i_level = 0
197 then
198 ec_debug.pl(0,'EC','ECE_SEED_DATA_MISSING','TRANSACTION_TYPE',i_transaction_type);
199 /**
200 Set the Retcode for the Concurrent Manager
201 **/
202 EC_UTILS.i_ret_code := 2;
203 raise EC_UTILS.PROGRAM_EXIT;
204 end if;
205
206 -- Bug 2162062
207
208 if ec_inbound_stage.g_source_charset IS NULL then
209 select value
210 into ec_inbound_stage.g_source_charset
211 from v$nls_parameters
212 where parameter='NLS_CHARACTERSET';
213 end if;
214
215 -- Get the character set from the profile option and verify it with fnd_lookups,and database settings.
216 -- bug 2110652
217
218
219 select value,decode(value,ec_inbound_stage.g_source_charset,'Y','N')
220 into i_db_charset,i_db_charset_flag
221 from v$nls_parameters
222 where parameter = 'NLS_CHARACTERSET';
223
224 if sql%notfound then
225 ec_debug.pl(0,'Characterset not not same as defined in Database');
226 end if;
227
228 if EC_DEBUG.G_debug_level = 3 then
229 ec_debug.pl(3,'i_db_charset',i_db_charset);
230 end if;
231
232 select 'Y'
233 into i_fnd_charset_flag
234 from fnd_lookups
235 where lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
236 and lookup_code = ec_inbound_stage.g_source_charset;
237
238 if sql%notfound then
239 ec_debug.pl(0,'Invalid Character Set specified in the FND_LOOKUPS');
240 EC_UTILS.i_ret_code := 2;
241 raise EC_UTILS.PROGRAM_EXIT;
242 end if;
243
244
245 --- Open the Inbound Transaction File in the Read Mode
246 u_file_handle := utl_file.fopen(i_file_path,i_file_name,'r');
247
248
249 -- Find Positions for Translator Code and Location Code in the PL/SQL table.
250 find_pos
251 (
252 1,
253 'TP_TRANSLATOR_CODE',
254 i_translator_code_pos
255 );
256
257 find_pos
258 (
259 1,
260 'TP_LOCATION_CODE',
261 i_location_code_pos
262 );
263
264
265 BEGIN
266 LOOP
267
268
269 BEGIN
270 v_next_rec_number := NULL;
271 skip_record_flag := FALSE;
272
273 utl_file.get_line(u_file_handle,c_current_line);
274
275 v_next_rec_number := SUBSTRB(c_current_line,
276 g_record_num_start,
277 g_record_num_length
278 );
279
280 counter := counter + 1;
281
282 next_rec_number := to_number(v_next_rec_number);
283
284 if EC_DEBUG.G_debug_level = 3 then
285 ec_debug.pl(3,'counter',counter);
286 end if;
287
288 EXCEPTION
289 WHEN VALUE_ERROR then
290 ec_debug.pl(3,'counter',counter);
291
292 /**
293 If the record number found in the file cannot be converted to a number,
294 i.e. it is a character value, then skip this record
295 **/
296
297 skip_record_flag := TRUE;
298 ec_debug.pl(0,'EC','ECE_RECORD_NUM_INVALID','RECORD_NUMBER',v_next_rec_number,'LINE_NUMBER',counter);
299
300 /**
301 Set the Retcode for the Concurrent Manager BUT do not RAISE program exit
302 **/
303 EC_UTILS.i_ret_code := 1;
304
305 WHEN NO_DATA_FOUND then
306 ec_debug.pl(3,'counter',counter);
307
308 /**
309 If the End Of File is encountered and the Line Counter
310 is zero , that means the File is empty then Exit the loop
311 and proceed towards the end of program.
312 **/
313 if counter = 0 then
314 exit;
315 end if;
316
317 end_of_file := TRUE;
318 END;
319
320 IF NOT skip_record_flag THEN
321 if EC_DEBUG.G_debug_level >= 3 then
322 ec_debug.pl(3,'EC','ECE_STAGE_LINE_NUMBER','LINE_NUMBER',counter,'RECORD_NUMBER',next_rec_number);
323 ec_debug.pl(3,'c_current_line',c_current_line);
324 end if;
325 /**
326 Match the Record Number of the Line read with the Start Record Number for each level.
327 If matches , then Insert the Data for the previous level.
328 **/
329
330 For i in 1..i_level_info.COUNT
331 loop
332 if ( next_rec_number = i_level_info(i).start_record_number )
333 or ( end_of_file )
334 then
335 i_current_level := i;
336
337 if NOT (i_first_line_flag) or ( end_of_file )
338 then
339
340 --- Generate Stage Id for each Record from Sequence
341 open seq_stage_id;
342 fetch seq_stage_id into i_stage_id;
343 close seq_stage_id;
344 if EC_DEBUG.G_debug_level >= 3 then
345 ec_debug.pl(3,'i_stage_id',i_stage_id);
346 end if;
347
348 /**
349 The Document Number in all the Levels should be populated only
350 when the position of the Key Column is available in the PL/SQL
351 table for Level Information.
352 **/
353 if i_previous_level = 1
354 then
355
356 -- Derive the Trading Partner Code , if possible.
357 -- First Get the Values from the PL/SQL table.
358 i_tp_code := NULL;
359 i_translator_code :=
360 i_stage_record_type(i_translator_code_pos).value;
361 i_location_code := i_stage_record_type(i_location_code_pos).value;
362
363 get_tp_code
364 (
365 i_translator_code,
366 i_location_code,
367 i_level_info(1).primary_address_type,
368 i_transaction_type,
369 i_tp_code
370 );
371
372 /**
373 Populate Key Column Attribute for Error Handling
374 **/
375 ece_flatfile_pvt.t_tran_attribute_tbl(1).value
376 := i_level_info(1).Document_Number;
377
378 for j in 1..i_level_info.COUNT
379 loop
380 if i_level_info(1).Key_Column_Position is not null
381 then
382 i_level_info(j).Document_Number :=
383 i_stage_record_type(i_level_info(1).Key_column_Position).value;
384 i_level_info(j).tp_code := i_tp_code;
385 end if;
386 i_level_info(j).tp_code := i_tp_code;
387 end loop;
388 end if;
389
390
391 i_level_info(i_previous_level).Stage_id := i_stage_id;
392
393
394 /**
395 Insert Data into Staging table for Previous Level of Document .
396 The value of the Insert Cursor for the First Call should be zero
397 , and the rest of the calls can take the returned Cursor handle.
398 This helps avoiding the Expensive Parsing for subsequent calls.
399 **/
400 Insert_into_Stage_table
401 (
402 i_previous_level,
403 i_map_id,
404 i_level_info(i_previous_level).Insert_Cursor
405 );
406 /**
407 Initialize the PL/SQL table which was loaded with values.
408 **/
409 --Bug 2500898,2608899
410 --i_stage_record_type := i_empty_tbl;
411 for k in 1..i_stage_record_type.COUNT
412 loop
413 i_stage_record_type(k).value :=NULL;
414 end loop;
415
416
417 if i_previous_level = 1
418 then
422 'DOCUMENT_NUMBER',
419 if EC_DEBUG.G_debug_level >= 1 then
420 ec_debug.pl(1,'EC','ECE_DOCUMENT_ID','DOCUMENT_ID',
421 i_level_info(i_previous_level).Document_id,
423 i_level_info(i_previous_level).Document_Number);
424 end if;
425 end if;
426
427 /**
428 Un-necessary.
429 ec_debug.pl(2,'EC','ECE_LEVEL','LEVEL',i_previous_level);
430 ec_debug.pl(2,'EC','ECE_SEQUENCE_NUMBER','SEQUENCE_NUMBER',
431 i_level_info(i_previous_level).Line_Number);
432 ec_debug.pl(2,'EC','ECE_STAGE_ID','STAGE_ID',
433 i_level_info(i_previous_level).Stage_Id);
434 ec_debug.pl(2,'EC','ECE_PARENT_STAGE_ID','PARENT_STAGE_ID',
435 i_level_info(i_previous_level).Parent_Stage_Id);
436 **/
437
438 if ( end_of_file )
439 then
440 exit;
441 end if;
442
443 end if; -- First Line Flag
444
445 /**
446 If the Next record Number is a Header Record , initialize the data
447 in the Level Information PL/SQL table and generate the
448 Document Id .Set it for all the Levels because the Document Id will
449 remain same for lower Levels.
450
451 If the Record is a Line , Shipement etc. , then Increment the Line
452 Number , set the Parent Stage Id equal to the Parent Level , and
453 reset Line Number and Parent Stage id for the Down Level.
454 **/
455 if next_rec_number = i_level_info(1).Start_Record_Number
456 then
457 -- Generate Document Id
458 open seq_document_id;
459 fetch seq_document_id into Document_Id;
460 close seq_document_id;
461
462 i_document_number := i_document_number + 1;
463
464 -- Reset file position counter
465 l_next_file_pos := 1;
466
467 -- Initialize the variables
468 i_level_info(1).Document_Id := Document_Id;
469 i_level_info(1).Line_Number := 1;
470 i_level_info(1).Parent_Stage_Id := NULL;
471 i_level_info(1).tp_code := NULL;
472
473 --- Initialize all the Down Levels
474 For j in 2..i_level_info.COUNT
475 loop
476 i_level_info(j).Document_Id := i_level_info(1).Document_Id;
477 i_level_info(j).Line_Number := 0;
478 i_level_info(j).Parent_Stage_Id := NULL;
479 i_level_info(j).tp_code := NULL;
480 end loop;
481 else
482 -- Reset file position counter - no big performance loss if it starts at 1 for every line
483 l_next_file_pos := 1;
484
485 i_level_info(i).Line_Number := i_level_info(i).Line_Number + 1;
486 i_level_info(i).Parent_Stage_Id := i_level_info(i-1).Stage_Id;
487
488 --- Initialize all the Down Levels
489 For j in i+1..i_level_info.COUNT
490 loop
491 i_level_info(j).Line_Number := 0;
492 i_level_info(j).Parent_Stage_Id := NULL;
493 end loop;
494
495 end if;
496
497 exit;
498 end if; --- ( Start record Number )
499 end loop; -- ( For Start record Number )
500
501 if ( end_of_file )
502 then
503 exit;
504 end if;
505
506 /**
507 The record number read from the File is matched against the valid Record number
508 for that Level in the PL/SQL table for Mapping Information. If the record
509 is a valid record , then the line is loaded into the PL/SQL table.
510 **/
511 i_valid_record := match_record_num
512 (
513 i_current_level,
514 next_rec_number,
515 l_file_pos,
516 l_next_file_pos,
517 l_total_rec_unit
518 );
519
520 if ( i_valid_record ) then
521
522 load_data_from_file
523 (
524 l_file_pos,
525 l_total_rec_unit,
526 c_current_line
527 );
528
529 i_valid_record := FALSE;
530
531 end if;
532
533 i_previous_level := i_current_level;
534
535 if (i_first_line_flag)
536 then
537 /**
538 If the Record Number for the First Line is
539 not equal to the Start record Number for Header
540 then the program never encountered a Header record.
541 Serious Error with File.
542 **/
543 if nvl(next_rec_number,0) <> i_level_info(1).Start_Record_Number
544 then
545 ec_debug.pl(0,'EC','ECE_MISSING_HEADER_RECORD',null);
546 /**
547 Set the Retcode for the Concurrent Manager
548 **/
549 EC_UTILS.i_ret_code := 2;
550 raise EC_UTILS.PROGRAM_EXIT;
551 end if;
552 end if;
553
554 i_first_line_flag := FALSE;
555
556 END IF;
557
558 end loop;
559
560 /**
561 The Cursors for the Insert into Stage table are not closed in the Insert_Into_Stage_table
562 procedure call. Since the Cursor handles are maintained in the I_LEVEL_INFO PL/SQL table ,
563 Cursors for the all the Level are closed using these Cursor handles.
564 **/
565 For i in 1..i_level_info.COUNT
569 dbms_sql.Close_cursor(i_level_info(i).Insert_Cursor);
566 loop
567 IF dbms_sql.IS_OPEN(i_level_info(i).Insert_Cursor)
568 then
570 end if;
571 end loop;
572
573 /**
574 If the File is empty , the Line Counter will be zero and the End of File
575 exception will transfer the Control over here.
576 **/
577 if ( nvl(length(c_current_line),0) = 0 )
578 and ( counter = 0 )
579 then
580 ec_debug.pl(0,'EC','ECE_EMPTY_FILE','FILE_NAME',i_file_path||'/'||i_file_name,
581 'TRANSACTION_TYPE',i_transaction_type);
582 /**
583 Set the Retcode for the Concurrent Manager
584 **/
585 EC_UTILS.i_ret_code := 1;
586 raise EC_UTILS.PROGRAM_EXIT;
587 end if;
588
589 end;
590
591 -- Close the Inbound Transaction File
592 utl_file.fclose(u_file_handle);
593
594 if EC_DEBUG.G_debug_level >= 1 then
595 ec_debug.pl(1,'EC','ECE_NO_LINES_READ','NO_OF_LINES',counter);
596 end if;
597 <<stage_over>>
598 if EC_DEBUG.G_debug_level >= 1 then
599 ec_debug.pl(1,'EC','ECE_DOCUMENTS_PROCESSED','NO_OF_DOCS',i_document_number);
600 ec_debug.pop('EC_INBOUND_STAGE.LOAD_DATA');
601 end if;
602 EXCEPTION
603 WHEN UTL_FILE.write_error THEN
604 EC_UTILS.i_ret_code :=2;
605 ec_debug.pl(0,'EC','ECE_UTL_WRITE_ERROR',null);
606 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
607 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
608 utl_file.fclose(u_file_handle);
609 raise EC_UTILS.PROGRAM_EXIT;
610 WHEN UTL_FILE.read_error THEN
611 EC_UTILS.i_ret_code :=2;
612 ec_debug.pl(0,'EC','ECE_UTL_READ_ERROR',null);
613 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
614 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
615 utl_file.fclose(u_file_handle);
616 raise EC_UTILS.PROGRAM_EXIT;
617 WHEN UTL_FILE.invalid_path THEN
618 EC_UTILS.i_ret_code :=2;
619 ec_debug.pl(0,'EC','ECE_UTL_INVALID_PATH',null);
620 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
621 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
622 utl_file.fclose(u_file_handle);
623 raise EC_UTILS.PROGRAM_EXIT;
624 WHEN UTL_FILE.invalid_mode THEN
625 EC_UTILS.i_ret_code :=2;
626 ec_debug.pl(0,'EC','ECE_UTL_INVALID_MODE',null);
627 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
628 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
629 utl_file.fclose(u_file_handle);
630 raise EC_UTILS.PROGRAM_EXIT;
631 WHEN UTL_FILE.invalid_operation THEN
632 EC_UTILS.i_ret_code :=2;
633 ec_debug.pl(0,'EC','ECE_UTL_INVALID_OPERATION',null);
634 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
635 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
636 utl_file.fclose(u_file_handle);
637 raise EC_UTILS.PROGRAM_EXIT;
638 WHEN UTL_FILE.invalid_filehandle THEN
639 EC_UTILS.i_ret_code :=2;
640 ec_debug.pl(0,'EC','ECE_UTL_INVALID_FILEHANDLE',null);
641 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
642 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
643 utl_file.fclose(u_file_handle);
644 raise EC_UTILS.PROGRAM_EXIT;
645 WHEN UTL_FILE.internal_error THEN
646 EC_UTILS.i_ret_code :=2;
647 ec_debug.pl(0,'EC','ECE_UTL_INTERNAL_ERROR',null);
648 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
649 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
650 utl_file.fclose(u_file_handle);
651 raise EC_UTILS.PROGRAM_EXIT;
652 WHEN EC_UTILS.PROGRAM_EXIT THEN
653 raise;
654 WHEN OTHERS THEN
655 EC_UTILS.i_ret_code :=2;
656 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA');
657 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
658 utl_file.fclose(u_file_handle);
659 raise EC_UTILS.PROGRAM_EXIT;
660 END Load_Data;
661
662 /**
663 This Function returns the Boolean True or False for a match between the
664 Record Number read from the File and the Record Number seeded for the
665 transaction. If the match is found , then it returns back the number of
666 Data elements present , and the Cursor Position in the line upto which
667 the Data has been read
668 **/
669 FUNCTION match_record_num
670 (
671 i_current_level IN NUMBER,
672 i_record_num IN number,
673 i_file_pos OUT NOCOPY number,
674 i_next_file_pos IN OUT NOCOPY number,
675 i_total_rec_unit OUT NOCOPY number
676 )
677 return boolean
678 is
679 b_match_found BOOLEAN := FALSE;
680 i_total_unit NUMBER :=0;
681
682 begin
683 if EC_DEBUG.G_debug_level >= 2 then
684 ec_debug.push('EC_INBOUND_STAGE.MATCH_RECORD_NUM');
685 ec_debug.pl(3,'i_current_level',i_current_level);
686 ec_debug.pl(3,'i_record_num',i_record_num);
687 end if;
688 for k in i_next_file_pos..i_stage_record_type.count
689 loop
690 if i_stage_record_type(k).external_level = i_current_level
691 then
692 if i_stage_record_type(k).Record_number = i_record_num
693 and ( not b_match_found )
694 then
695 i_file_pos :=k;
699 then
696 i_total_unit := i_total_unit + 1;
697 b_match_found := TRUE;
698 elsif i_stage_record_type(k).record_number = i_record_num
700 i_total_unit := i_total_unit + 1;
701 elsif b_match_found and i_stage_record_type(k).Record_number <> i_record_num
702 then
703 exit;
704 end if;
705 end if;
706 end loop;
707 i_next_file_pos := NVL(i_file_pos + i_total_unit, i_next_file_pos);
708 i_total_rec_unit := i_total_unit;
709
710 if EC_DEBUG.G_debug_level >= 2 then
711 ec_debug.pl(3,'i_file_pos',i_file_pos);
712 ec_debug.pl(3,'i_next_file_pos',i_next_file_pos);
713 ec_debug.pl(3,'i_total_rec_unit',i_total_rec_unit);
714 ec_debug.pl(3,'b_match_found',b_match_found);
715 ec_debug.pop('EC_INBOUND_STAGE.MATCH_RECORD_NUM');
716 end if;
717 return b_match_found;
718 EXCEPTION
719 WHEN OTHERS then
720 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.MATCH_RECORD_NUM');
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 match_record_num;
725
726 /**
727 After a successful match of record number between the Line Read from
728 FlatFile and the seeded data , the Line is loaded into the PL/SQL
729 table. The PL/SQL table is defined as a Local variable in the Body
730 of the package and is accessible to the Functions and Procedures
731 inside the package body only.
732 **/
733 procedure load_data_from_file
734 (
735 i_file_pos in number,
736 i_total_rec_unit in number,
737 c_current_line in out nocopy varchar2
738 )
739 is
740
741 i_cur_pos number := g_common_key_length;
742 i_data_length number;
743
744 --bug 2164672
745 i_data_file_value Varchar2(500);
746 i_new_file_value Varchar2(500);
747
748 begin
749 if EC_DEBUG.G_debug_level >= 2 then
750 ec_debug.push('EC_INBOUND_STAGE.LOAD_DATA_FROM_FILE');
751 ec_debug.pl(3,'i_file_pos',i_file_pos);
752 ec_debug.pl(3,'i_total_rec_unit',i_total_rec_unit);
753 ec_debug.pl(3,'c_current_line',c_current_line);
754 end if;
755 -- bug 4555935
756 c_current_line := replace(c_current_line,fnd_global.local_chr(13));
757 c_current_line := replace(c_current_line,fnd_global.local_chr(10));
758 c_current_line := replace(c_current_line,fnd_global.local_chr(9));
759 --bug 2110652
760 if i_fnd_charset_flag = 'Y' then
761 if i_db_charset_flag = 'Y' then
762 for i in i_file_pos..(i_file_pos + i_total_rec_unit - 1 )
763 loop
764 i_data_length := nvl(i_stage_record_type(i).width,0);
765 i_stage_record_type(i).value :=
766 rtrim(substrb(c_current_line,i_cur_pos+1,i_data_length));
767
768 if replace(i_stage_record_type(i).value,' ') is null then
769 i_stage_record_type(i).value :=NULL;
770 end if;
771
772 i_cur_pos := i_cur_pos + i_data_length;
773 end loop;
774 else
775 for i in i_file_pos..(i_file_pos + i_total_rec_unit - 1 )
776 loop
777 i_data_length := nvl(i_stage_record_type(i).width,0);
778 --Bug 2164672
779 i_data_file_value := rtrim(substrb(c_current_line,i_cur_pos+1,i_data_length));
780 i_stage_record_type(i).value :=
781 convert(i_data_file_value,i_db_charset,ec_inbound_stage.g_source_charset);
782 i_new_file_value :=
783 convert(i_stage_record_type(i).value,ec_inbound_stage.g_source_charset,i_db_charset);
784
785 if i_new_file_value not like i_data_file_value then
786 i_data_status_flag := FALSE;
787 end if;
788
789 if replace(i_stage_record_type(i).value,' ') is null then
790 i_stage_record_type(i).value :=NULL;
791 end if;
792
793 i_cur_pos := i_cur_pos + i_data_length;
794 end loop;
795 end if;
796 end if;
797
798 if EC_DEBUG.G_debug_level >= 2 then
799 ec_debug.pop('EC_INBOUND_STAGE.LOAD_DATA_FROM_FILE');
800 end if;
801 EXCEPTION
802 WHEN OTHERS then
803 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.LOAD_DATA_FROM_FILE');
804 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
805 EC_UTILS.i_ret_code:=2;
806 raise EC_UTILS.PROGRAM_EXIT;
807 end load_data_from_file;
808
809 /**
810 This procedures loads the mapping information between the Flat File
811 and the Staging table. This information is seeded in the ECE_INTERFACE_TABLES
812 and ECE_INTERFACE_COLUMNS. The mapping information is loaded into the Local Body
813 PL/SQL table variable for a given transaction Type and its level. This PL/SQL table
814 loaded with Mapping information is visible only to the functions and procedures
815 defined within this package.
816 **/
817 procedure populate_flatfile_mapping
818 (
819 i_transaction_type in varchar2,
820 i_level in number,
821 i_map_id IN number
822 )
823 is
824 cursor c_file_mapping
825 (
826 p_level number,
827 p_map_id number
828 ) is
829 SELECT eic.interface_column_name,
830 eic.staging_column,
831 eic.record_number,
832 eic.position,
833 eic.width
834 FROM ece_interface_columns eic
835 WHERE eic.external_level = p_level
836 AND eic.map_id = p_map_id
840 ORDER BY eic.record_number, eic.position;
837 AND eic.record_number IS NOT NULL
838 AND eic.position IS NOT NULL
839 AND eic.staging_column IS NOT NULL
841
842 i_counter NUMBER :=i_stage_record_type.COUNT;
843 m_counter number := i_counter;
844 i_previous_level NUMBER := 0;
845
846 BEGIN
847 if EC_DEBUG.G_debug_level >= 2 then
848 ec_debug.push('EC_INBOUND_STAGE.POPULATE_FLATFILE_MAPPING');
849 ec_debug.pl(3,'i_transaction_type',i_transaction_type);
850 ec_debug.pl(3,'i_Level',i_level);
851 ec_debug.pl(3,'EC','ECE_INTERFACE_MAPPING','TRANSACTION_TYPE',i_transaction_type,'LEVEL',i_level);
852 end if;
853
854 FOR transaction_mapping in c_file_mapping
855 (
856 p_level => i_level,
857 p_map_id => i_map_id
858 )
859 Loop
860
861 i_counter := i_counter + 1;
862 i_stage_record_type(i_counter).external_level := i_level;
863 i_stage_record_type(i_counter).interface_column_name := transaction_mapping.interface_column_name;
864 i_stage_record_type(i_counter).staging_column := transaction_mapping.staging_column;
865 i_stage_record_type(i_counter).record_number := transaction_mapping.record_number;
866 i_stage_record_type(i_counter).position := transaction_mapping.position;
867 i_stage_record_type(i_counter).width := transaction_mapping.width;
868
869 if EC_DEBUG.G_debug_level >= 3 then
870 ec_debug.pl
871 (
872 3,
873 i_counter||'|'||
874 i_level||'|'||
875 transaction_mapping.interface_column_name||'|'||
876 transaction_mapping.staging_column||'|'||
877 transaction_mapping.record_number||'|'||
878 transaction_mapping.position||'|'||
879 transaction_mapping.width
880 );
881 end if;
882 if i_level = 1
883 then
884 if upper(i_stage_record_type(i_counter).interface_column_name) =
885 i_level_info(1).key_column_name
886 then
887 ece_flatfile_pvt.t_tran_attribute_tbl(1).key_column_name
888 := i_level_info(1).Key_column_name;
889 ece_flatfile_pvt.t_tran_attribute_tbl(1).position
890 := i_counter;
891 i_level_info(1).key_column_position := i_counter;
892 if EC_DEBUG.G_debug_level >= 3 then
893 ec_debug.pl(3,'Key_Column_Position',i_level_info(1).Key_Column_position);
894 end if;
895 end if;
896 end if;
897
898 end loop;
899
900 if i_counter = m_counter then
901 ec_debug.pl(0,'EC','ECE_SEED_NOT_LEVEL','TRANSACTION_TYPE',i_transaction_type,'LEVEL',i_level);
902 /**
903 Set the Retcode for the Concurrent Manager to Error.
904 **/
905 EC_UTILS.i_ret_code := 2;
906 raise EC_UTILS.PROGRAM_EXIT;
907 end if;
908
909 if EC_DEBUG.G_debug_level >= 2 then
910 ec_debug.pop('EC_INBOUND_STAGE.POPULATE_FLATFILE_MAPPING');
911 end if;
912 EXCEPTION
913 WHEN EC_UTILS.PROGRAM_EXIT then
914 raise;
915 WHEN OTHERS then
916 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.POPULATE_FLATFILE_MAPPING');
917 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
918 EC_UTILS.i_ret_code:=2;
919 raise EC_UTILS.PROGRAM_EXIT;
920 END populate_flatfile_mapping;
921
922 /**
923 The Data loaded in the Local PL/SQL table is inserted into the Staging table.
924 This procedures takes Transaction Level and the Cursor handle as the parameter.
925 The Cursor handle is passed as 0 in the First call , and the subsequent calls
926 uses the Cursor Handle returned by the Procedure. This helps in avoiding the
927 expensive parsing of the SQL Statement again and again for the Same level.
928 **/
929 procedure Insert_Into_Stage_Table
930 (
931 i_level IN NUMBER,
932 i_map_id IN NUMBER,
933 i_insert_cursor IN OUT NOCOPY NUMBER
934 )
935 is
936 c_Insert_Cursor INTEGER;
937 cInsert_stmt varchar2(32000) := 'INSERT INTO ECE_STAGE ( ';
938 cValue_stmt varchar2(32000) := 'VALUES (';
939 dummy INTEGER;
940 error_position integer;
941
942 BEGIN
943 if EC_DEBUG.G_debug_level >= 2 then
944 ec_debug.push('EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
945 ec_debug.pl(3,'i_level',i_level);
946 ec_debug.pl(3,'i_insert_cursor',i_insert_cursor);
947 end if;
948
949 if i_insert_cursor = 0
950 then
951 i_insert_cursor := -911;
952 end if;
953
954 if i_insert_cursor < 0
955 then
956 --- Add Mandatory Columns for the Record - includes the MAP_ID column
957 cInsert_stmt := cInsert_stmt||' Stage_id ,Document_Id ,Transaction_type ,Transaction_Level ,';
958 cInsert_stmt := cInsert_stmt||' Line_Number ,Parent_Stage_Id ,Run_Id ,Document_Number ,Status ,Tp_Code ,Map_ID ,';
959 --- Bug 2500898
960
961 -- cInsert_stmt := cInsert_stmt||' Parent_Stage_id ,Document_Id ,Transaction_type ,Transaction_Level ,';
962 -- cInsert_stmt := cInsert_stmt||' Line_Number ,Stage_Id ,Run_Id ,Document_Number ,Status ,Tp_Code ,Map_ID ,';
963
964 --- Add Who Columns for the Staging Table
965 cInsert_stmt := cInsert_stmt||' creation_date ,created_by ,last_update_date ,last_updated_by ,';
966
967 cValue_stmt := cValue_stmt||':a1 ,:a2 ,:a3 ,:a4 ,:a5 ,:a6 ,:a7 ,:a8 ,:a9 ,:a10 ,:a11 ,';
968 -- cValue_stmt := cValue_stmt||':w1 ,:w2 ,:w3 ,:w4 ,';
969
970
971 -- cValue_stmt := cValue_stmt||' ece_stage_id_s.CURRVAL ,:a2 ,:a3 ,:a4 ,:a5 ,ece_stage_id_s.NEXTVAL ,:a7 ,:a8 ,:a9 ,:a10 ,:a11 ,';
975 for i in 1..i_stage_record_type.COUNT
972 cValue_stmt := cValue_stmt||' sysdate ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,';
973
974 --- Add Variable Columns for the Record
976 loop
977 if i_stage_record_type(i).external_level = i_level
978 then
979 --- Build Insert Statement
980 cInsert_stmt := cInsert_stmt||' '||i_stage_record_type(i).staging_column|| ',';
981 cValue_stmt := cvalue_stmt || ':b'||i||',';
982 end if;
983 end loop;
984
985 cInsert_stmt := RTRIM(cInsert_stmt,',')||')';
986 cValue_stmt := RTRIM(cValue_stmt,',')||')';
987 cInsert_stmt := cInsert_stmt||cValue_stmt;
988
989 if EC_DEBUG.G_debug_level >= 3 then
990 ec_debug.pl(3,'EC','ECE_STAGE_INSERT_LEVEL','LEVEL',i_level,null);
991 ec_debug.pl(3,cInsert_stmt);
992 end if;
993
994 /**
995 Open the cursor and parse the SQL Statement. Trap any parsing error and report
996 the Error Position in the SQL Statement
997 **/
998 i_Insert_Cursor := dbms_sql.Open_Cursor;
999 begin
1000 dbms_sql.parse(i_Insert_Cursor,cInsert_stmt,dbms_sql.native);
1001 exception
1002 when others then
1003 error_position := dbms_sql.last_error_position;
1004 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
1005 ece_error_handling_pvt.print_parse_error (error_position,cInsert_stmt);
1006 EC_UTILS.i_ret_code :=2;
1007 raise EC_UTILS.PROGRAM_EXIT;
1008 end;
1009 end if;
1010
1011 if i_Insert_Cursor > 0
1012 then
1013 begin
1014 -- Bug 2164672
1015 if i_data_status_flag then
1016 i_level_info(i_level).Status := 'NEW';
1017 else
1018 i_level_info(i_level).Status := 'LOSSY_CONVERSION';
1019 end if;
1020
1021 -- Bind values for Mandatory Columns
1022
1023 dbms_sql.bind_variable (i_Insert_Cursor,'a1',to_number(i_level_info(i_level).Stage_Id));
1024 dbms_sql.bind_variable (i_Insert_Cursor,'a6',i_level_info(i_level).Parent_Stage_Id);
1025 dbms_sql.bind_variable (i_Insert_Cursor,'a2',to_number(i_level_info(i_level).Document_Id));
1026 dbms_sql.bind_variable (i_Insert_Cursor,'a3',i_level_info(i_level).Transaction_Type);
1027 dbms_sql.bind_variable (i_Insert_Cursor,'a4',to_number(i_level));
1028 dbms_sql.bind_variable (i_Insert_Cursor,'a5',to_number(i_level_info(i_level).Line_Number));
1029 dbms_sql.bind_variable (i_Insert_Cursor,'a7',to_number(i_level_info(i_level).Run_Id));
1030 dbms_sql.bind_variable (i_Insert_Cursor,'a8',i_level_info(i_level).Document_Number);
1031 dbms_sql.bind_variable (i_Insert_Cursor,'a9',i_level_info(i_level).Status);
1032 dbms_sql.bind_variable (i_Insert_Cursor,'a10',i_level_info(i_level).Tp_Code);
1033 dbms_sql.bind_variable (i_Insert_Cursor,'a11',i_map_id);
1034
1035 -- Bind values for Mandatory Columns
1036 /* Bug 2500898
1037 dbms_sql.bind_variable (i_Insert_Cursor,'w1',sysdate);
1038 dbms_sql.bind_variable (i_Insert_Cursor,'w2',fnd_global.user_id);
1039 dbms_sql.bind_variable (i_Insert_Cursor,'w3',sysdate);
1040 dbms_sql.bind_variable (i_Insert_Cursor,'w4',fnd_global.user_id);
1041 */
1042
1043 if EC_DEBUG.G_debug_level >= 3 then
1044 ec_debug.pl(3,'STAGE_ID',i_level_info(i_level).Stage_Id);
1045 ec_debug.pl(3,'DOCUMENT_ID',i_level_info(i_level).Document_Id);
1046 ec_debug.pl(3,'TRANSACTION_TYPE',i_level_info(i_level).Transaction_Type);
1047 ec_debug.pl(3,'TRANSACTION_LEVEL',i_level);
1048 ec_debug.pl(3,'LINE_NUMBER',i_level_info(i_level).Line_Number);
1049 ec_debug.pl(3,'PARENT_STAGE_ID',i_level_info(i_level).Parent_Stage_Id);
1050 ec_debug.pl(3,'RUN_ID',i_level_info(i_level).Run_Id);
1051 ec_debug.pl(3,'DOCUMENT_NUMBER',i_level_info(i_level).Document_Number);
1052 ec_debug.pl(3,'TP_CODE',i_level_info(i_level).Tp_Code);
1053 ec_debug.pl(3,'MAP_ID',i_map_id);
1054 ec_debug.pl(3,'CREATION_DATE',sysdate);
1055 ec_debug.pl(3,'CREATED_BY',fnd_global.user_id);
1056 ec_debug.pl(3,'LAST_UPDATE_DATE',sysdate);
1057 ec_debug.pl(3,'LAST_UPDATED_BY',fnd_global.user_id);
1058 ec_debug.pl(3,'STATUS',i_level_info(i_level).Status);
1059 end if;
1060 -- Bind values for Staging Columns mapped to the Flat File
1061 for k in 1..i_stage_record_type.COUNT
1062 loop
1063 if i_stage_record_type(k).external_level = i_level
1064 then
1065 dbms_sql.bind_variable (
1066 i_Insert_Cursor,
1067 'b'||k,
1068 i_stage_record_type(k).value
1069 );
1070
1071 /* Bug 2500898
1072 'b'||k,substrb (
1073 i_stage_record_type(k).value,
1074 1,
1075 i_stage_record_type(k).width
1076 )
1077 );
1078 */
1079
1080 if EC_DEBUG.G_debug_level = 3 then
1081 ec_debug.pl(3,upper(i_stage_record_type(k).staging_column)||'-'||
1082 upper(i_stage_record_type(k).interface_column_name),
1083 i_stage_record_type(k).value);
1084 end if;
1085 end if;
1086 end loop;
1087
1088 dummy := dbms_sql.execute(i_Insert_Cursor);
1089 if dummy = 1 then
1090 if EC_DEBUG.G_debug_level = 3 then
1091 ec_debug.pl(3,'EC','ECE_STAGE_INSERTED',null);
1092 end if;
1096 raise EC_UTILS.PROGRAM_EXIT;
1093 i_data_status_flag := TRUE; --Bug 2164672
1094 else
1095 EC_UTILS.i_ret_code :=2;
1097 end if;
1098
1099 exception
1100 when others then
1101 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',
1102 'EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
1103 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1104 ec_debug.pl(0,'EC','ECE_ERROR_SQL',null);
1105 ec_debug.pl(0,cInsert_stmt);
1106
1107 EC_UTILS.i_ret_code :=2;
1108 raise EC_UTILS.PROGRAM_EXIT;
1109 end;
1110 end if;
1111
1112 if EC_DEBUG.G_debug_level >= 2 then
1113 ec_debug.pl(3,'i_insert_cursor',i_insert_cursor);
1114 ec_debug.pop('EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
1115 end if;
1116 EXCEPTION
1117 WHEN EC_UTILS.PROGRAM_EXIT then
1118 raise;
1119 WHEN OTHERS then
1120 IF dbms_sql.IS_OPEN(i_insert_cursor)
1121 then
1122 dbms_sql.close_cursor(i_insert_cursor);
1123 end if;
1124 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.INSERT_INTO_STAGE_TABLE');
1125 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1126 EC_UTILS.i_ret_code:=2;
1127 raise EC_UTILS.PROGRAM_EXIT;
1128 END Insert_Into_Stage_Table;
1129
1130
1131 procedure get_tp_code
1132 (
1133 i_translator_code in varchar2,
1134 i_location_code in varchar2,
1135 i_address_type in varchar2,
1136 i_transaction_type IN varchar2,
1137 i_tp_code OUT NOCOPY varchar2
1138 )
1139 is
1140 i_translator_code_pos number;
1141 i_location_code_pos number;
1142 i_cur_pos number := g_common_key_length;
1143 i_data_length number;
1144
1145
1146 /* Bug 1966138.
1147 Replaced ra_addresses with hz_cust_acct_sites_all
1148 to improve performance*/
1149 CURSOR c_cust_addr
1150 (
1151 i_translator_code IN varchar2,
1152 i_location_code IN varchar2,
1153 i_transaction_type IN varchar2
1154 )
1155 IS
1156 select tp_code
1157 from ece_tp_details td,
1158 hz_cust_acct_sites_all hcas,
1159 ece_tp_headers th
1160 where td.translator_code = i_translator_code and
1161 hcas.ece_tp_location_code = i_location_code and
1162 hcas.tp_header_id = td.tp_header_id and
1163 td.tp_header_id = th.tp_header_id and
1164 td.document_id = i_transaction_type and
1165 NVL(hcas.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),
1166 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
1167 = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
1168 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) and
1169 rownum = 1;
1170
1171
1172 /* CURSOR c_cust_addr
1173 (
1174 i_translator_code IN varchar2,
1175 i_location_code IN varchar2,
1176 i_transaction_type IN varchar2
1177 )
1178 IS
1179 select tp_code
1180 from ece_tp_details td,
1181 ra_addresses ra,
1182 ece_tp_headers th
1183 where td.translator_code = i_translator_code and
1184 ra.ece_tp_location_code = i_location_code and
1185 ra.tp_header_id = td.tp_header_id and
1186 td.tp_header_id = th.tp_header_id and
1187 td.document_id = i_transaction_type and
1188 rownum = 1;
1189 */
1190
1191 CURSOR c_supplier_addr
1192 (
1193 i_translator_code IN varchar2,
1194 i_location_code IN varchar2,
1195 i_transaction_type IN varchar2
1196 )
1197 IS
1198 select tp_code
1199 from ece_tp_details td,
1200 po_vendor_sites pvs,
1201 ece_tp_headers th
1202 where td.translator_code = i_translator_code and
1203 pvs.ece_tp_location_code = i_location_code and
1204 pvs.tp_header_id = td.tp_header_id and
1205 th.tp_header_id = td.tp_header_id and
1206 td.document_id = i_transaction_type and
1207 rownum = 1;
1208
1209 CURSOR c_bank_addr
1210 (
1211 i_translator_code IN varchar2,
1212 i_location_code IN varchar2,
1213 i_transaction_type IN varchar2
1214 )
1215 IS
1216 select tp_code
1217 from ece_tp_details td,
1218 ap_bank_branches abb,
1219 ece_tp_headers th
1220 where td.translator_code = i_translator_code and
1221 abb.ece_tp_location_code = i_location_code and
1222 abb.tp_header_id = td.tp_header_id and
1223 th.tp_header_id = td.tp_header_id and
1224 td.document_id = i_transaction_type and
1225 rownum = 1;
1226
1227
1228 CURSOR c_hr_addr
1229 (
1230 i_translator_code IN varchar2,
1231 i_location_code IN varchar2,
1232 i_transaction_type IN varchar2
1233 )
1234 IS
1235 select tp_code
1236 from ece_tp_details td,
1237 hr_locations hrl,
1238 ece_tp_headers th
1242 th.tp_header_id = td.tp_header_id and
1239 where td.translator_code = i_translator_code and
1240 hrl.ece_tp_location_code = i_location_code and
1241 hrl.tp_header_id = td.tp_header_id and
1243 td.document_id = i_transaction_type and
1244 rownum = 1;
1245
1246 counter number :=0;
1247 i_level number :=0;
1248 begin
1249 if EC_DEBUG.G_debug_level >= 2 then
1250 ec_debug.push('EC_INBOUND_STAGE.GET_TP_CODE');
1251 ec_debug.pl(3,'i_translator_code',i_translator_code);
1252 ec_debug.pl(3,'i_location_code',i_location_code);
1253 ec_debug.pl(3,'i_address_type',i_address_type);
1254 end if;
1255
1256 if i_address_type = 'CUSTOMER'
1257 then
1258 for c_customer in c_cust_addr
1259 (
1260 i_translator_code => i_translator_code,
1261 i_location_code => i_location_code,
1262 i_transaction_type => i_transaction_type
1263 )
1264 loop
1265 i_tp_code := c_customer.tp_code;
1266 end loop;
1267
1268 elsif i_address_type = 'SUPPLIER'
1269 then
1270 for c_supplier in c_supplier_addr
1271 (
1272 i_translator_code => i_translator_code,
1273 i_location_code => i_location_code,
1274 i_transaction_type => i_transaction_type
1275 )
1276 loop
1277 i_tp_code := c_supplier.tp_code;
1278 end loop;
1279
1280 elsif i_address_type = 'BANK'
1281 then
1282 for c_bank in c_bank_addr
1283 (
1284 i_translator_code => i_translator_code,
1285 i_location_code => i_location_code,
1286 i_transaction_type => i_transaction_type
1287 )
1288 loop
1289 i_tp_code := c_bank.tp_code;
1290 end loop;
1291
1292 elsif i_address_type = 'LOCATIONS'
1293 then
1294 for c_locations in c_hr_addr
1295 (
1296 i_translator_code => i_translator_code,
1297 i_location_code => i_location_code,
1298 i_transaction_type => i_transaction_type
1299 )
1300 loop
1301 i_tp_code := c_locations.tp_code;
1302 end loop;
1303
1304 else
1305 -- Not a Valid Address Type
1306 i_tp_code := null;
1307
1308 end if;
1309 if EC_DEBUG.G_debug_level >= 2 then
1310 ec_debug.pl(3,'i_tp_code',i_tp_code);
1311 ec_debug.pop('EC_INBOUND_STAGE.GET_TP_CODE');
1312 end if;
1313 EXCEPTION
1314 WHEN OTHERS then
1315 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.GET_TP_CODE');
1316 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1317 EC_UTILS.i_ret_code:=2;
1318 raise EC_UTILS.PROGRAM_EXIT;
1319 end get_tp_code;
1320
1321 procedure find_pos
1322 (
1323 i_level IN number,
1324 i_search_text IN varchar2,
1325 o_pos OUT NOCOPY NUMBER,
1326 i_required IN BOOLEAN DEFAULT TRUE
1327 )
1328 IS
1329 cIn_String varchar2(1000) := UPPER(i_search_text);
1330 nColumn_count number := i_stage_record_type.COUNT;
1331 bFound BOOLEAN := FALSE;
1332 POS_NOT_FOUND EXCEPTION;
1333 BEGIN
1334 if EC_DEBUG.G_debug_level >= 2 then
1335 ec_debug.PUSH('EC_INBOUND_STAGE.FIND_POS');
1336 ec_debug.pl(3,'i_level',i_level);
1337 ec_debug.pl(3,'i_search_text',i_search_text);
1338 ec_debug.pl(3,'o_pos',o_pos);
1339 ec_debug.pl(3,'i_required',i_required);
1340 end if;
1341 for k in 1..nColumn_count
1342 loop
1343 if i_stage_record_type(k).external_level = i_level
1344 then
1345 if upper(i_stage_record_type(k).interface_column_name) = cIn_String
1346 then
1347 o_pos := k;
1348 bFound := TRUE;
1349 exit;
1350 end if;
1351 end if;
1352 end loop;
1353
1354 if not bFound
1355 then
1356 if (i_required)
1357 then
1358 raise POS_NOT_FOUND;
1359 else
1360 o_pos := NULL;
1361 end if;
1362 end if;
1363 if EC_DEBUG.G_debug_level >= 2 then
1364 ec_debug.pl(3,'o_pos',o_pos);
1365 ec_debug.POP('EC_INBOUND_STAGE.FIND_POS');
1366 end if;
1367 EXCEPTION
1368 WHEN POS_NOT_FOUND THEN
1369 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME',cIn_String);
1370 ec_debug.POP('EC_INBOUND_STAGE.FIND_POS');
1371 EC_UTILS.i_ret_code := 2;
1372 raise EC_UTILS.PROGRAM_EXIT;
1373 WHEN OTHERS THEN
1374 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_INBOUND_STAGE.FIND_POS');
1375 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1376 EC_UTILS.i_ret_code := 2;
1377 raise EC_UTILS.PROGRAM_EXIT;
1378 END find_pos;
1379
1380 END EC_INBOUND_STAGE;
[Home] [Help]