[Home] [Help]
PACKAGE BODY: APPS.ECE_FLATFILE_PVT
Source
1 PACKAGE BODY ECE_FLATFILE_PVT AS
2 -- $Header: ECVFILEB.pls 120.3 2005/10/04 02:19:41 arsriniv ship $
3
4 debug_mode_on_select BOOLEAN := FALSE;
5 debug_mode_on_write BOOLEAN := FALSE;
6 pos_not_found EXCEPTION;
7 g_msg VARCHAR2(2000) := NULL;
8
9 --- PROCEDURE select_clause.
10 --- Creation Feb. 14, 1995
11 ---
12 --- Procedure select_clause builds a Select clause and a From
13 --- clause and a Where clause at run time for the dynamic SQL call.
14
15 --- It looks at all the data columns for the EC transaction,
16 --- based on stored info, sort the columns in ascending order
17 --- of which should be written in the output file first, then
18 --- store them in a PL/SQL table.
19 ---
20 --- It then builds a character string of the select clause
21 --- based on these stored columns and returns it. This select
22 --- clause had already add the TO_CHAR function to convert
23 --- data to character type.
24
25 --- It also returns the number and position of columns stored,
26 --- and width of each column. Furthmore, it returns the From
27 --- and Where clauses of the select statement.
28
29 PROCEDURE get_tran_attributes(p_transaction_type IN VARCHAR2) IS
30 n_record_count NUMBER := 0;
31
32 CURSOR c_tran_attribute IS
33 SELECT key_column_name
34 FROM ece_interface_tables
35 WHERE transaction_type = p_transaction_type AND
36 key_column_name IS NOT NULL
37 ORDER BY output_level;
38
39 BEGIN
40 t_tran_attribute_tbl.DELETE;
41
42 FOR r_key_column_rec IN c_tran_attribute LOOP
43 n_record_count := n_record_count + 1;
44 t_tran_attribute_tbl(n_record_count).key_column_name := r_key_column_rec.key_column_name;
45 END LOOP;
46
47 IF n_record_count = 0 THEN
48 NULL;
49 END IF;
50
51 END get_tran_attributes;
52
53 PROCEDURE print_attributes IS
54 n_index NUMBER;
55
56 BEGIN
57 FOR n_index IN 1..t_tran_attribute_tbl.COUNT LOOP
58 if t_tran_attribute_tbl(n_index).value is not null
59 then
60 ec_debug.pl(0,'EC','ECE_ERROR_DOCUMENT',
61 'KEY_COLUMN_NAME',
62 t_tran_attribute_tbl(n_index).key_column_name,
63 'DOCUMENT_NUMBER',
64 t_tran_attribute_tbl(n_index).value);
65 end if;
66 END LOOP;
67
68 END print_attributes;
69
70 PROCEDURE select_clause(
71 cTransaction_Type IN VARCHAR2,
72 cCommunication_Method IN VARCHAR2,
73 cInterface_Table IN VARCHAR2,
74 cExt_Table OUT NOCOPY VARCHAR2,
75 p_Interface_tbl OUT NOCOPY interface_tbl_type,
76 p_common_key_name OUT NOCOPY VARCHAR2,
77 cSelect_string OUT NOCOPY VARCHAR2,
78 cFrom_string OUT NOCOPY VARCHAR2,
79 cWhere_string OUT NOCOPY VARCHAR2,
80 p_output_level IN VARCHAR2 DEFAULT NULL,
81 cMapCode IN VARCHAR2 DEFAULT NULL) IS
82
83 xProgress VARCHAR2(30);
84 cOutput_path VARCHAR2(120);
85
86 cSelect_stmt VARCHAR2(32000) := 'SELECT ';
87 cFrom_stmt VARCHAR2(32000) := ' FROM ';
88 cWhere_stmt VARCHAR2(32000) := ' WHERE ';
89
90 cTo_Char VARCHAR2(20) := 'TO_CHAR(';
91 cDate VARCHAR2(40) := ',''YYYYMMDD HH24MISS'')';
92 cWord1 VARCHAR2(20) := ' ';
93 cWord2 VARCHAR2(40) := ' ';
94 cExtension_Table VARCHAR2(50);
95 cTable_Name VARCHAR2(50);
96 cColumn_Name VARCHAR2(50);
97
98 iColumn_count INTEGER := 0;
99 iMap_ID INTEGER;
100
101 CURSOR c1(cMap_ID INTEGER) IS
102 SELECT eit.interface_table_name table_name,
103 eit.extension_Table_Name ext_table_name,
104 eic.interface_column_name column_name,
105 eic.column_name ext_column_name,
106 eic.record_number record_number,
107 eic.position position,
108 eic.width new_width,
109 eic.record_layout_code,
110 eic.record_layout_qualifier,
111 eic.interface_column_id int_col_id,
112 eic.width width,
113 eic.data_type col_type
114 FROM ece_interface_columns eic,
115 ece_interface_tables eit
116 WHERE eit.transaction_type = cTransaction_Type AND
117 eit.interface_table_name = cInterface_Table AND
118 eit.output_level = NVL(p_output_level,eit.output_level) AND
119 eit.interface_table_id = eic.interface_table_id AND
120 eit.map_id = cMap_ID AND
121 (eic.interface_column_name IS NOT NULL OR
122 eic.column_name IS NOT NULL)
123 ORDER BY eic.record_number,
124 eic.position;
125
126 BEGIN
127 if EC_DEBUG.G_debug_level >= 2 then
128 ec_debug.push('ECE_FLATFILE_PVT.SELECT_CLAUSE');
129 end if;
130
131 IF cMapCode IS NULL THEN
132 SELECT map_id INTO iMap_ID
133 FROM ece_mappings
134 WHERE map_code = 'EC_' || cTransaction_type || '_FF';
135 ELSE
136 SELECT map_id INTO iMap_ID
137 FROM ece_mappings
138 WHERE map_code = cMapCode;
139 END IF;
140 if EC_DEBUG.G_debug_level = 3 then
141 ec_debug.pl(3,'iMap_ID',iMap_ID);
142 end if;
143 xProgress := 'FILEB-10-1020';
144 FOR c1_rec IN c1(iMap_ID) LOOP
145 -- **************************************
146 -- store data in PL/SQL tables
147 -- These tables will be returned to the main program
148 -- **************************************
149 xProgress := 'FILEB-10-1030';
150 iColumn_count := iColumn_count + 1;
151 xProgress := 'FILEB-10-1040';
152 p_Interface_tbl(iColumn_count).interface_column_id := c1_rec.Int_col_id;
153 xProgress := 'FILEB-10-1050';
154 p_Interface_tbl(iColumn_count).interface_table_name := c1_rec.Table_Name;
155 xProgress := 'FILEB-10-1060';
156 p_Interface_tbl(iColumn_count).interface_column_name := c1_rec.Column_Name;
157 xProgress := 'FILEB-10-1070';
158 p_Interface_tbl(iColumn_count).Record_num := c1_rec.Record_Number;
159 xProgress := 'FILEB-10-1080';
160 p_Interface_tbl(iColumn_count).Position := c1_rec.Position;
161 xProgress := 'FILEB-10-1090';
162 p_Interface_tbl(iColumn_count).data_length := NVL(c1_rec.New_width, c1_rec.Width);
163 xProgress := 'FILEB-10-1100';
164 p_Interface_tbl(iColumn_count).layout_code := c1_rec.Record_layout_code;
165 xProgress := 'FILEB-10-1110';
166 p_Interface_tbl(iColumn_count).record_qualifier := c1_rec.record_layout_qualifier;
167 if EC_DEBUG.G_debug_level = 3 then
168 ec_debug.pl(3,
169 iColumn_count||' '||
170 p_Interface_tbl(iColumn_count).interface_column_id||' '||
171 p_Interface_tbl(iColumn_count).interface_table_name||' '||
172 p_Interface_tbl(iColumn_count).interface_column_name||' '||
173 p_Interface_tbl(iColumn_count).Record_Num||' '||
174 p_Interface_tbl(iColumn_count).Position||' '||
175 p_Interface_tbl(iColumn_count).data_length||' '||
176 p_Interface_tbl(iColumn_count).layout_code||' '||
177 p_Interface_tbl(iColumn_count).record_qualifier
178 );
179 end if;
180 -- **************************************
181 -- apply appropriate data conversion
182 -- **************************************
183 xProgress := 'FILEB-10-1120';
184 IF 'DATE' = c1_rec.Col_Type THEN
185 xProgress := 'FILEB-10-1130';
186 cWord1 := cTO_CHAR;
187 xProgress := 'FILEB-10-1140';
188 cWord2 := cDATE;
189 xProgress := 'FILEB-10-1150';
190 ELSIF 'NUMBER' = c1_rec.Col_Type THEN
191 xProgress := 'FILEB-10-1160';
192 cWord1 := cTO_CHAR;
193 cWord2 := ')';
194 ELSE
195 xProgress := 'FILEB-10-1170';
196 cWord1 := NULL;
197 cWord2 := NULL;
198 END IF;
199
200 -- build SELECT statement
201 IF c1_rec.Column_Name IS NOT NULL THEN
202 cTable_Name := c1_rec.Table_Name;
203 cColumn_Name := c1_rec.Column_Name;
204 ELSIF c1_rec.Ext_Column_Name IS NOT NULL THEN
205 cTable_Name := c1_rec.Ext_Table_Name;
206 cColumn_Name := c1_rec.Ext_Column_Name;
207 ELSE
208 cTable_Name := NULL;
209 cColumn_Name := NULL;
210 END IF;
211
212 xProgress := 'FILEB-10-1180';
213 IF ((cTable_Name IS NOT NULL) AND (cColumn_Name IS NOT NULL)) THEN
214 cSelect_stmt := cSelect_stmt || ' ' || cWord1 || cTable_Name || '.' ||
215 cColumn_Name || cWord2 || ',';
216 END IF;
217 END LOOP;
218
219 xProgress := 'FILEB-10-1190';
220 SELECT eit.extension_table_name, -- select extension table name
221 eit.key_column_name
222 INTO cExtension_Table,
223 p_common_key_name
224 FROM ece_interface_tables eit
225 WHERE eit.transaction_type = cTransaction_Type AND
226 eit.interface_table_name = cInterface_Table AND
227 eit.map_id = iMap_ID AND
228 eit.output_level = NVL(p_output_level,eit.output_level);
229
230 -- *************************************
231 -- build FROM, WHERE statements
232 -- *************************************
233 xProgress := 'FILEB-10-1200';
234 cFrom_stmt := cFrom_stmt || cInterface_Table || ', '|| cExtension_Table;
235
236 xProgress := 'FILEB-10-1210';
237 cWhere_stmt := cWhere_stmt || cInterface_Table || '.' || 'TRANSACTION_RECORD_ID' ||
238 ' = ' || cExtension_table || '.'|| 'TRANSACTION_RECORD_ID(+)';
239
240 xProgress := 'FILEB-10-1220';
241 cSelect_string := RTRIM(cSelect_stmt, ',');
242 if EC_DEBUG.G_debug_level = 3 then
243 ec_debug.pl(3,'Select statement : ',cSelect_string);
244 end if;
245 xProgress := 'FILEB-10-1230';
246 cFrom_string := cFrom_stmt;
247 if EC_DEBUG.G_debug_level = 3 then
251 cWhere_string := cWhere_stmt;
248 ec_debug.pl(3,'From statement : ',cFrom_string);
249 end if;
250 xProgress := 'FILEB-10-1240';
252 if EC_DEBUG.G_debug_level = 3 then
253 ec_debug.pl(3,'Where statement : ',cWhere_string);
254 end if;
255 xProgress := 'FILEB-10-1250';
256 cExt_Table := cExtension_Table;
257 if EC_DEBUG.G_debug_level = 3 then
258 ec_debug.pl(3,'cExt_Table : ',cExt_Table);
259 end if;
260
261 if EC_DEBUG.G_debug_level >= 2 then
262 ec_debug.pop('ECE_FLATFILE_PVT.SELECT_CLAUSE');
263 end if;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_FLATFILE_PVT.SELECT_CLAUSE');
268 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
269 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
270 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
271 app_exception.raise_exception;
272
273 END select_clause;
274
275 ---PROCEDURE write_to_ece_output
276 ---Creation Feb. 15, 1995
277 ---This report procedure writes data to the ECE_OUTPUT table.
278 ---It put the appropriate record id at the beginning of each record
279 ---lines of data. The entire record line of data is inserted into the
280 ---TEXT column in the table.
281 ---
282 ---It expects a PL/SQL table of output data (in ASC order)!!!
283
284 PROCEDURE write_to_ece_output(
285 cTransaction_Type IN VARCHAR2,
286 cCommunication_Method IN VARCHAR2,
287 cInterface_Table IN VARCHAR2,
288 p_Interface_tbl IN Interface_tbl_type,
289 iOutput_width IN INTEGER,
290 iRun_id IN INTEGER,
291 p_common_key IN VARCHAR2) IS
292
293 xProgress VARCHAR2(30);
294 cOutput_path VARCHAR2(120);
295 iLine_pos INTEGER;
296 iData_count INTEGER := p_Interface_tbl.COUNT;
297 iStart_num INTEGER;
298 iRow_num INTEGER;
299 cInsert_stmt VARCHAR2(32000);
300 l_common_key VARCHAR2(255) := p_common_key;
301 l_count NUMBER;
302
303 BEGIN
304 if EC_DEBUG.G_debug_level >= 2 then
305 ec_debug.push('ECE_FLATFILE_PVT.WRITE_TO_ECE_OUTPUT');
306 end if;
307 xProgress := 'FILEB-WR-1020';
308 FOR i IN 1..iData_count LOOP
309 xProgress := 'FILEB-WR-1030';
310 l_count := i;
311
312 xProgress := 'FILEB-WR-1040';
313 IF p_Interface_tbl(i).Record_num IS NOT NULL AND
314 p_Interface_tbl(i).position IS NOT NULL AND
315 p_Interface_tbl(i).data_length IS NOT NULL THEN
316 xProgress := 'FILEB-WR-1050';
317 iRow_num := i;
318 if EC_DEBUG.G_debug_level >= 3 then
319 ec_debug.pl(3,'iRow_num : ',iRow_num);
320 end if;
321 xProgress := 'FILEB-WR-1060';
322
323 cInsert_stmt := cInsert_stmt || substr(rpad(nvl(p_Interface_tbl(i).value,' '),
324 TO_CHAR(p_Interface_tbl(i).data_length),' '),1,
325 p_Interface_tbl(i).data_length);
326
327 -- ******************************************************
328 -- the following two lines is for testing/debug purpose
329 -- ******************************************************
330 -- cInsert_stmt := cInsert_stmt || rpad(substrb(p_Interface_tbl(i).interface_column_name,1,p_Interface_tbl(i).data_length-2)||
331 -- substrb(TO_CHAR(p_Interface_tbl(i).data_length),1,2), TO_CHAR(p_Interface_tbl(i).data_length),' ');
332 END IF;
333
334 xProgress := 'FILEB-WR-1070';
335 IF i < iData_count THEN
336 xProgress := 'FILEB-WR-1080';
337 IF p_Interface_tbl(i).Record_num <> p_Interface_tbl(i+1).Record_num THEN
338 xProgress := 'FILEB-WR-1090';
339 cInsert_stmt := l_common_key || LPAD(NVL(p_Interface_tbl(iRow_num).Record_num,0),4,'0') ||
340 RPAD(NVL(p_Interface_tbl(iRow_num).layout_code,' '),2) ||
341 RPAD(NVL(p_Interface_tbl(iRow_num).record_qualifier,' '),3) || cInsert_stmt;
342
343 xProgress := 'FILEB-WR-1100';
344 INSERT INTO ece_output(run_id,line_id,text) VALUES
345 (iRun_id,ece_output_lines_s.NEXTVAL,SUBSTR(cInsert_stmt,1,iOutput_width));
346
347 xProgress := 'FILEB-WR-1110';
348 cInsert_stmt := NULL;
349 -- cInsert_stmt := '*' || TO_CHAR(p_Interface_tbl(i).Record_num);
353 /* Bug# 2108977 :- Added the following codition to prevent NULL records from causing
350 END IF;
351 ELSE
352 xProgress := 'FILEB-WR-1120';
354 erros */
355
356 IF iRow_num IS NOT NULL THEN
357 cInsert_stmt := l_common_key || LPAD(NVL(p_Interface_tbl(iRow_num).Record_num,0),4,'0') ||
358 RPAD(NVL(p_Interface_tbl(iRow_num).layout_code,' '),2) ||
359 RPAD(NVL(p_Interface_tbl(iRow_num).record_qualifier,' '),3) || cInsert_stmt;
360
361 xProgress := 'FILEB-WR-1130';
362 INSERT INTO ece_output(run_id,line_id,text) VALUES
363 (iRun_id,ece_output_lines_s.NEXTVAL,SUBSTR(cInsert_stmt,1,iOutput_width));
364 END IF;
365 END IF;
366 END LOOP;
367
368 if EC_DEBUG.G_debug_level >= 2 then
369 ec_debug.pop('ECE_FLATFILE_PVT.WRITE_TO_ECE_OUTPUT');
370 end if;
371
372 EXCEPTION
373 WHEN OTHERS THEN
374 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_FLATFILE_PVT.WRITE_TO_ECE_OUTPUT');
375 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL',xProgress);
376 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
377 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
378
379 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', 'to_char(l_count)');
380 ec_debug.pl(0,'EC','ECE_PLSQL_VALUE','COLUMN_NAME', 'p_interface_tbl(l_count).value');
381 ec_debug.pl(0,'EC','ECE_PLSQL_DATA_TYPE','COLUMN_NAME', 'p_interface_tbl(l_count).data_type');
382 ec_debug.pl(0,'EC','ECE_PLSQL_COLUMN_NAME','COLUMN_NAME', 'p_interface_tbl(l_count).base_column');
383 app_exception.raise_exception;
384
385 END write_to_ece_output;
386
387 PROCEDURE Find_pos(
388 p_Interface_tbl IN Interface_tbl_type,
389 cSearch_text IN VARCHAR2,
390 nPos IN OUT NOCOPY NUMBER)
391
392 IS
393 cIn_string VARCHAR2(1000) := UPPER(cSearch_text);
394 nColumn_count NUMBER := p_Interface_tbl.Count;
395 b_found BOOLEAN := FALSE;
396 POS_NOT_FOUND EXCEPTION;
397 cOutput_path VARCHAR2(120);
398 BEGIN
399
400 if EC_DEBUG.G_debug_level >= 2 then
401 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.Find_pos');
402 EC_DEBUG.PL(3, 'Search text : ', cSearch_text);
403 EC_DEBUG.PL(3, 'nColumn_count : ', nColumn_count);
404 end if;
405 For k in 1..nColumn_count loop
406 if UPPER(p_Interface_tbl(k).interface_column_name) = cIn_string then
407 nPos := k;
408 if EC_DEBUG.G_debug_level >= 3 then
409 EC_DEBUG.PL(3, 'Position : ', nColumn_count);
410 end if;
411 b_found := TRUE;
412 exit;
413 end if;
414 end loop;
415
416 if NOT b_found then
417 Raise POS_NOT_FOUND;
418 end if;
419 if EC_DEBUG.G_debug_level >= 2 then
420 EC_DEBUG.POP('ECE_FLATFILE_PVT.Find_pos');
421 end if;
422 EXCEPTION
423 WHEN POS_NOT_FOUND THEN
424 EC_DEBUG.PL(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', 'cIn_string');
425 app_exception.raise_exception;
426
427 WHEN OTHERS THEN
428 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_FLATFILE_PVT.FIND_POS');
429 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
430 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
431 app_exception.raise_exception;
432 END Find_pos;
433 /*
434 PROCEDURE match_data_loc_id(
435 p_Interface_tbl IN Interface_tbl_type,
436 p_data_loc_id IN NUMBER,
437 p_Pos OUT NOCOPY NUMBER)
438
439 IS
440
441 BEGIN
442 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.match_data_loc_id');
443 if EC_DEBUG.G_debug_level >= 3 then
444 EC_DEBUG.PL(3, 'Data loc id : ', p_data_loc_id);
445 EC_DEBUG.PL(3, 'Loop count : ', p_Interface_tbl.count);
446 end if;
447 For k in 1..p_Interface_tbl.count
448 loop
449 if p_Interface_tbl(k).data_loc_id = p_data_loc_id
450 then
451 p_Pos := k;
452 if EC_DEBUG.G_debug_level >= 3 then
453 EC_DEBUG.PL(3, 'Position : ', p_Pos);
454 end if;
455 exit;
456 end if;
457 end loop;
458 EC_DEBUG.POP('ECE_FLATFILE_PVT.match_data_loc_id');
459 END match_data_loc_id;
460
461 -- *******************************************
462 */
463 PROCEDURE match_interface_column_id(
464 p_Interface_tbl IN Interface_tbl_type,
465 p_Interface_column_id IN NUMBER,
466 p_Pos OUT NOCOPY NUMBER)
467
468 IS
469
470 BEGIN
471 if EC_DEBUG.G_debug_level >= 2 then
472 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.match_interface_column_id');
473 EC_DEBUG.PL(3, 'Interface_column_id : ', p_Interface_column_id);
474 EC_DEBUG.PL(3, 'Loop count : ', p_Interface_tbl.count);
475 end if;
476 For k in 1..p_Interface_tbl.count
477 loop
478 if p_Interface_tbl(k).interface_column_id = p_Interface_column_id
479 then
483 end if;
480 p_Pos := k;
481 if EC_DEBUG.G_debug_level >= 3 then
482 EC_DEBUG.PL(3, 'Position : ', p_Pos);
484 exit;
485 end if;
486 end loop;
487 if EC_DEBUG.G_debug_level >= 2 then
488 EC_DEBUG.POP('ECE_FLATFILE_PVT.match_interface_column_id');
489 end if;
490 END match_interface_column_id;
491
492 -- *******************************************
493 FUNCTION match_conversion_group_id(
494 p_gateway_tbl IN Interface_tbl_type,
495 p_conversion_id IN NUMBER,
496 p_sequence_num IN NUMBER,
497 p_pos OUT NOCOPY NUMBER)
498 return BOOLEAN
499 IS
500 xProgress varchar2(30);
501 b_found BOOLEAN := FALSE;
502 nCount NUMBER;
503 POS_NOT_FOUND EXCEPTION;
504 cOutput_path VARCHAR2(120);
505
506 BEGIN
507 xProgress := 'FILEB-MA-1000';
508 if EC_DEBUG.G_debug_level >= 2 then
509 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.match_conversion_group_id');
510
511 EC_DEBUG.PL(3, 'Conversion id : ', p_conversion_id);
512 EC_DEBUG.PL(3, 'Seq num : ', p_sequence_num);
513 EC_DEBUG.PL(3, 'Loop count : ', p_gateway_tbl.count);
514 end if;
515 For k in 1..p_gateway_tbl.count
516 loop
517 xProgress := 'FILEB-MA-1010';
518 nCount := k;
519 if p_gateway_tbl(k).conversion_group_id = p_conversion_id
520 and p_gateway_tbl(k).conversion_seq = p_sequence_num
521 then
522 xProgress := 'FILEB-MA-1020';
523 p_pos := k;
524 if EC_DEBUG.G_debug_level >= 2 then
525 EC_DEBUG.PL(3, 'Position : ', p_pos);
526 EC_DEBUG.POP('ECE_FLATFILE_PVT.match_conversion_group_id');
527 end if;
528 b_found := TRUE;
529 return b_found;
530 end if;
531 end loop;
532 xProgress := 'FILEB-MA-1030';
533 IF NOT b_found THEN
534 RAISE POS_NOT_FOUND;
535 END IF;
536 if EC_DEBUG.G_debug_level >= 2 then
537 EC_DEBUG.POP('ECE_FLATFILE_PVT.match_conversion_group_id');
538 end if;
539 EXCEPTION
540 WHEN POS_NOT_FOUND THEN
541 EC_DEBUG.PL(0,'EC','ECE_CONVERSION_ID_NOT_FOUND','CONVERSION_ID', 'p_conversion_id', 'SEQUENCE', 'p_sequence_num');
542 app_exception.raise_exception;
543
544 WHEN OTHERS THEN
545 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','xProgress');
546 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
547 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
548 app_exception.raise_exception;
549
550 END match_conversion_group_id;
551
552 -- *******************************************
553 FUNCTION match_xref_conv_seq(
554 p_gateway_tbl IN Interface_tbl_type,
555 p_conversion_group IN NUMBER,
556 p_sequence_num IN NUMBER,
557 p_Pos OUT NOCOPY NUMBER)
558 return BOOLEAN
559 IS
560 b_found BOOLEAN := FALSE;
561
562 BEGIN
563 if EC_DEBUG.G_debug_level >= 2 then
564 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.match_xref_conv_seq');
565 EC_DEBUG.PL(3, 'Conversion group : ', p_conversion_group);
566 EC_DEBUG.PL(3, 'Seq num : ', p_sequence_num);
567 EC_DEBUG.PL(3, 'Loop count : ', p_gateway_tbl.count);
568 end if;
569 For k in 1..p_gateway_tbl.count
570 loop
571 if p_gateway_tbl(k).conversion_group_id = p_conversion_group
572 and p_gateway_tbl(k).conversion_seq = p_sequence_num
573 then
574 p_Pos := k;
575 if EC_DEBUG.G_debug_level >= 3 then
576 EC_DEBUG.PL(3, 'Position : ', p_Pos);
577 end if;
578 b_found := TRUE;
579 exit;
580 end if;
581 end loop;
582 if EC_DEBUG.G_debug_level >= 2 then
583 EC_DEBUG.POP('ECE_FLATFILE_PVT.match_xref_conv_seq');
584 end if;
585 return b_found;
586
587 END match_xref_conv_seq;
588
589 -- *******************************************
590 FUNCTION match_xref_conv_seq(
591 p_level IN NUMBER,
592 p_conversion_group IN NUMBER,
593 p_sequence_num IN NUMBER,
594 p_Pos OUT NOCOPY NUMBER)
595 return BOOLEAN
596 IS
597 b_found BOOLEAN := FALSE;
598 hash_value pls_integer;
599 hash_string varchar2(3200);
600 tbl_pos pls_integer;
601 BEGIN
602 if EC_DEBUG.G_debug_level >= 2 then
603 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.match_xref_conv_seq');
604 EC_DEBUG.PL(3, 'p_level group : ', p_level);
605 EC_DEBUG.PL(3, 'Conversion group : ', p_conversion_group);
606 EC_DEBUG.PL(3, 'Seq num : ', p_sequence_num);
607 ec_debug.pl(3, 'file_start_pos',ec_utils.g_ext_levels(p_level).file_start_pos);
608 ec_debug.pl(3, 'file_end_pos',ec_utils.g_ext_levels(p_level).file_end_pos);
609 end if;
610
611 /*
612 Bug 2112028 -Reverting this fix back to 115.16 as the change done in 1853627 causes
613 the Generic Outbound process to fail
614
615 Bug 2340691 - The fix made in 1853627 was again added to improve the Performance. This fix
616 is for Inbound programs only. The Outbound programs use the previous logic.
617 */
618
619 /* if ec_utils.g_direction = 'I' --bug 3133379
620 then */
621 /* Bug 2617428
625 then
622 For k in ec_utils.g_ext_levels(p_level).file_start_pos..ec_utils.g_ext_levels(p_level).file_end_pos
623 loop
624 if ec_utils.g_file_tbl(k).external_level = p_level
626 if ec_utils.g_file_tbl(k).conversion_group_id = p_conversion_group
627 and ec_utils.g_file_tbl(k).conversion_sequence = p_sequence_num
628 then
629 p_Pos := k;
630 b_found := TRUE;
631 exit;
632 end if;
633 end if;
634 end loop;
635 */
636 -- Bug 2617428, 2791195
637 hash_string :=to_char(p_conversion_group)||'-'||
638 to_char(p_level)||'-'||
639 to_char(p_sequence_num);
640 hash_value := dbms_utility.get_hash_value(hash_string,1,8192);
641 if ec_utils.g_code_conv_pos_tbl_1.exists(hash_value) then
642 if ec_utils.g_code_conv_pos_tbl_1(hash_value).occr = 1 then
643 p_Pos := ec_utils.g_code_conv_pos_tbl_1(hash_value).value;
644 b_found := TRUE;
645 else
646 tbl_pos := ec_utils.g_code_conv_pos_tbl_1(hash_value).start_pos;
647 while tbl_pos<=ec_utils.g_code_conv_pos_tbl_2.LAST
648 loop
649 if ec_utils.g_code_conv_pos_tbl_2(tbl_pos) = hash_value then
650 if upper(ec_utils.g_file_tbl(tbl_pos).conversion_group_id) = p_conversion_group
651 and ec_utils.g_file_tbl(tbl_pos).conversion_sequence=p_sequence_num then
652 p_Pos := tbl_pos;
653 b_found := TRUE;
654 exit;
655 end if;
656 end if;
657 tbl_pos:=ec_utils.g_code_conv_pos_tbl_2.NEXT(tbl_pos);
658 end loop;
659 end if;
660 end if;
661 /* else --Bug 3133379
662 For k in 1..ec_utils.g_file_tbl.count
663 loop
664 if ec_utils.g_file_tbl(k).external_level = p_level
665 then
666 if ec_utils.g_file_tbl(k).conversion_group_id = p_conversion_group
667 and ec_utils.g_file_tbl(k).conversion_sequence = p_sequence_num
668 then
669 p_Pos := k;
670 b_found := TRUE;
671 exit;
672 end if;
673 end if;
674 end loop;
675 end if;
676 */
677
678 if EC_DEBUG.G_debug_level >= 2 then
679 EC_DEBUG.PL(3, 'p_Pos', p_Pos);
680 EC_DEBUG.PL(3, 'b_found', b_found);
681 EC_DEBUG.POP('ECE_FLATFILE_PVT.match_xref_conv_seq');
682 end if;
683 return b_found;
684
685 END match_xref_conv_seq;
686
687 -- ***********************************
688 -- This function calculates the number
689 -- of data in a record, excluding the
690 -- common key
691 --
692 -- algorithm:
693 -- match the record num in the interface
694 -- table
695 -- and return the number of matches
696 -- ***********************************
697
698 FUNCTION match_record_num(
699 p_gateway_tbl IN Interface_tbl_type,
700 p_Record_num IN NUMBER,
701 p_Pos OUT NOCOPY NUMBER,
702 p_total_unit OUT NOCOPY NUMBER)
703 return BOOLEAN
704 IS
705 b_match_found BOOLEAN := FALSE;
706 l_total_unit NUMBER := 0;
707 cOutput_path varchar2(120);
708 BEGIN
709 if EC_DEBUG.G_debug_level >= 2 then
710 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.match_record_num');
711 EC_DEBUG.PL(3, 'Record num : ', p_Record_num);
712 EC_DEBUG.PL(3, 'Loop count : ', p_gateway_tbl.count);
713 end if;
714 For k in 1..p_gateway_tbl.count
715 loop
716 if p_gateway_tbl(k).Record_num = p_Record_num
717 and (not b_match_found)
718 then
719 p_Pos := k;
720 if EC_DEBUG.G_debug_level >= 3 then
721 EC_DEBUG.PL(3, 'Position : ', p_Pos);
722 end if;
723 l_total_unit := l_total_unit + 1;
724 b_match_found := TRUE;
725
726 elsif p_gateway_tbl(k).Record_num = p_Record_num
727 then
728 l_total_unit := l_total_unit + 1;
729
730 elsif b_match_found
731 and p_gateway_tbl(k).Record_num <> p_Record_num
732 then
733 -- this elsif is for performance reason
734 -- a match found already and now on to a
735 -- new record num, break.
736
737 exit;
738 end if;
739 end loop;
740
741 p_total_unit := l_total_unit;
742 if EC_DEBUG.G_debug_level >= 2 then
743 EC_DEBUG.PL(3, 'Total records found : ', p_total_unit);
744 EC_DEBUG.POP('ECE_FLATFILE_PVT.match_record_num');
745 end if;
746 return b_match_found;
747 EXCEPTION
748 when others
749 then
750 EC_DEBUG.PL(0,'EC','ECE_RECORD_NUM_NOT_FOUND','RECORD_NUM','to_char(p_Record_num)');
751 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
752 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
753 app_exception.raise_exception;
754 END match_record_num;
755
756 PROCEDURE init_table(
757 cTransaction_Type IN VARCHAR2,
758 cInt_tbl_name IN VARCHAR2,
759 cOutput_level IN VARCHAR2,
760 bKey_exist IN BOOLEAN,
761 cInterface_tbl OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
762 cKey_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
766 SELECT eic.conversion_group_id,
763 cMapCode IN VARCHAR2 DEFAULT NULL) IS
764
765 CURSOR c_source_data(cMap_ID INTEGER) IS
767 eic.conversion_sequence,
768 eit.interface_table_name,
769 eic.interface_column_name,
770 eic.base_table_name,
771 eic.base_column_name,
772 eic.xref_category_id,
773 eic.xref_key1_source_column,
774 eic.xref_key2_source_column,
775 eic.xref_key3_source_column,
776 eic.xref_key4_source_column,
777 eic.xref_key5_source_column,
778 eic.data_type,
779 eic.width data_length,
780 eic.record_number, -- bug 2823215
781 eic.position,
782 eic.record_layout_code,
783 eic.record_layout_qualifier,
784 eic.column_name ext_column_name
785 FROM ece_interface_columns eic,
786 ece_interface_tables eit
787 WHERE eit.transaction_type = cTransaction_type AND
788 eit.interface_table_name = cInt_tbl_name AND
789 eic.interface_table_id = eit.interface_table_id AND
790 eit.output_level = NVL(cOutput_level,eit.output_level) AND
791 eit.map_id = cMap_ID
792 ORDER BY eic.record_number,
793 eic.position;
794 -- d_dummy_date DATE;
795 i_count INTEGER := 0;
796 iKey_count INTEGER := ckey_tbl.count;
797 iMap_ID INTEGER;
798 cOutput_path VARCHAR2(120);
799
800 BEGIN
801 if EC_DEBUG.G_debug_level >= 2 then
802 ec_debug.push('ECE_FLATFILE_PVT.INIT_TABLE');
803 end if;
804 IF cMapCode IS NULL THEN
805 SELECT map_id INTO iMap_ID
806 FROM ece_mappings
807 WHERE map_code = 'EC_' || cTransaction_type || '_FF';
808 ELSE
809 SELECT map_id INTO iMap_ID
810 FROM ece_mappings
811 WHERE map_code = cMapCode;
812 END IF;
813 if EC_DEBUG.G_debug_level >= 3 then
814 ec_debug.pl(3,'iMap_ID',iMap_ID);
815 end if;
816
817 FOR c_rec IN c_source_data(iMap_ID) LOOP
818 i_count := i_count + 1;
819 cInterface_tbl(i_count).conversion_group_id := c_rec.conversion_group_id;
820 cInterface_tbl(i_count).conversion_seq := c_rec.conversion_sequence;
821 cInterface_tbl(i_count).interface_table_name := c_rec.interface_table_name;
822 cInterface_tbl(i_count).interface_column_name := c_rec.interface_column_name;
823 cInterface_tbl(i_count).base_table_name := c_rec.base_table_name;
824 cInterface_tbl(i_count).base_column_name := c_rec.base_column_name;
825 cInterface_tbl(i_count).xref_category_id := c_rec.xref_category_id;
826 cInterface_tbl(i_count).xref_key1_source_column := c_rec.xref_key1_source_column;
827 cInterface_tbl(i_count).xref_key2_source_column := c_rec.xref_key2_source_column;
828 cInterface_tbl(i_count).xref_key3_source_column := c_rec.xref_key3_source_column;
829 cInterface_tbl(i_count).xref_key4_source_column := c_rec.xref_key4_source_column;
830 cInterface_tbl(i_count).xref_key5_source_column := c_rec.xref_key5_source_column;
831 cInterface_tbl(i_count).data_type := c_rec.data_type;
832 cInterface_tbl(i_count).data_length := c_rec.data_length;
833 cInterface_tbl(i_count).record_num := c_rec.record_number;
834 cInterface_tbl(i_count).position := c_rec.position;
835 cInterface_tbl(i_count).layout_code := c_rec.record_layout_code;
836 cInterface_tbl(i_count).record_qualifier := c_rec.record_layout_qualifier;
837 cInterface_tbl(i_count).ext_column_name := c_rec.ext_column_name; -- bug 2823215
838 if EC_DEBUG.G_debug_level = 3 then
839 ec_debug.pl(3,
840 i_count||' '||
841 cInterface_tbl(i_count).interface_table_name||' '||
842 cInterface_tbl(i_count).interface_column_name||' '||
843 cInterface_tbl(i_count).base_table_name||' '||
844 cInterface_tbl(i_count).base_column_name||' '||
845 cInterface_tbl(i_count).data_type||' '||
846 cInterface_tbl(i_count).data_length||' '||
847 cInterface_tbl(i_count).conversion_group_id||' '||
848 cInterface_tbl(i_count).conversion_seq||' '||
849 cInterface_tbl(i_count).xref_category_id||' '||
850 cInterface_tbl(i_count).xref_key1_source_column||' '||
851 cInterface_tbl(i_count).xref_key2_source_column||' '||
852 cInterface_tbl(i_count).xref_key3_source_column||' '||
853 cInterface_tbl(i_count).xref_key4_source_column||' '||
854 cInterface_tbl(i_count).xref_key5_source_column
855 );
856 end if;
857 IF bKey_exist THEN
858 iKey_count := iKey_count + 1;
859 ckey_tbl(iKey_count).conversion_group_id := c_rec.conversion_group_id;
860 ckey_tbl(iKey_count).conversion_seq := c_rec.conversion_sequence;
861 ckey_tbl(iKey_count).interface_table_name := c_rec.interface_table_name;
865 ckey_tbl(iKey_count).xref_category_id := c_rec.xref_category_id;
862 ckey_tbl(iKey_count).interface_column_name := c_rec.interface_column_name;
863 ckey_tbl(iKey_count).base_table_name := c_rec.base_table_name;
864 ckey_tbl(iKey_count).base_column_name := c_rec.base_column_name;
866 ckey_tbl(iKey_count).xref_key1_source_column := c_rec.xref_key1_source_column;
867 ckey_tbl(iKey_count).xref_key2_source_column := c_rec.xref_key2_source_column;
868 ckey_tbl(iKey_count).xref_key3_source_column := c_rec.xref_key3_source_column;
869 ckey_tbl(iKey_count).xref_key4_source_column := c_rec.xref_key4_source_column;
870 ckey_tbl(iKey_count).xref_key5_source_column := c_rec.xref_key5_source_column;
871 ckey_tbl(iKey_count).data_type := c_rec.data_type;
872 ckey_tbl(iKey_count).data_length := c_rec.data_length;
873 ckey_tbl(iKey_count).record_num := c_rec.record_number;
874 ckey_tbl(iKey_count).position := c_rec.position;
875 ckey_tbl(iKey_count).layout_code := c_rec.record_layout_code;
876 ckey_tbl(iKey_count).record_qualifier := c_rec.record_layout_qualifier;
877 ckey_tbl(iKey_count).ext_column_name := c_rec.ext_column_name; --bug 2823215
878
879
880
881
882 if EC_DEBUG.G_debug_level = 3 then
883 ec_debug.pl(3,
884 iKey_count||' '||
885 ckey_tbl(iKey_count).interface_table_name||' '||
886 ckey_tbl(iKey_count).interface_column_name||' '||
887 ckey_tbl(iKey_count).base_table_name||' '||
888 ckey_tbl(iKey_count).base_column_name||' '||
889 ckey_tbl(iKey_count).xref_category_id||' '||
890 ckey_tbl(iKey_count).conversion_group_id||' '||
891 ckey_tbl(iKey_count).conversion_seq
892 );
893 end if;
894 END IF;
895
896 END LOOP;
897
898 if EC_DEBUG.G_debug_level >= 2 then
899 ec_debug.pop('ECE_FLATFILE_PVT.INIT_TABLE');
900 end if;
901
902 EXCEPTION
903 WHEN OTHERS THEN
904 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_FLATFILE_PVT.init_table');
905 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
906 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
907 ec_debug.pl(0,'EC','ECE_PLSQL_TABLE_NAME','TABLE_NAME', 'cInterface_tbl(i_count).base_table_name');
908 ec_debug.pl(0,'EC','ECE_PLSQL_COLUMN_NAME','COLUMN_NAME', 'cInterface_tbl(i_count).base_column_name');
909 app_exception.raise_exception;
910
911 END init_table;
912
913 PROCEDURE ADD_TO_WHERE_CLAUSE (cString IN OUT NOCOPY VARCHAR2,
914 cAdd IN VARCHAR2) IS
915 BEGIN
916 if EC_DEBUG.G_debug_level >= 2 then
917 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.ADD_TO_WHERE_CLAUSE');
918 EC_DEBUG.PL(3, 'Add string : ', cAdd);
919 end if;
920
921 cString := cString || ' AND '|| cAdd;
922
923 if EC_DEBUG.G_debug_level >= 2 then
924 EC_DEBUG.PL(3, 'String : ', cString);
925 EC_DEBUG.POP('ECE_FLATFILE_PVT.ADD_TO_WHERE_CLAUSE');
926 end if;
927 END ADD_TO_WHERE_CLAUSE;
928
929 PROCEDURE ADD_TO_FROM_CLAUSE (cString IN OUT NOCOPY VARCHAR2,
930 cAdd IN VARCHAR2) IS
931 BEGIN
932 if EC_DEBUG.G_debug_level >= 2 then
933 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.ADD_TO_FROM_CLAUSE');
934 EC_DEBUG.PL(3, 'Add string : ', cAdd);
935 end if;
936 cString := cString || cAdd || ', ';
937 if EC_DEBUG.G_debug_level >= 3 then
938 EC_DEBUG.PL(3, 'String : ', cString);
939 EC_DEBUG.POP('ECE_FLATFILE_PVT.ADD_TO_FROM_CLAUSE');
940 end if;
941 END ADD_TO_FROM_CLAUSE;
942
943 PROCEDURE DEFINE_INTERFACE_COLUMN_TYPE (
944 c IN INTEGER,
945 cCol IN VARCHAR2,
946 iCol_size IN INTEGER,
947 p_tbl IN ece_flatfile_pvt.Interface_tbl_type)
948 IS
949 BEGIN
950 if EC_DEBUG.G_debug_level >= 2 then
951 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.DEFINE_INTERFACE_COLUMN_TYPE');
952 EC_DEBUG.PL(3, 'Column value: ', c);
953 EC_DEBUG.PL(3, 'Column : ', cCol);
954 EC_DEBUG.PL(3, 'Column size: ', iCol_size);
955 EC_DEBUG.PL(3, 'Table loop count : ', p_tbl.count);
956 end if;
957
958 For k IN 1..p_tbl.count loop
959 dbms_sql.define_column(c, k, cCol, iCol_size);
960 End Loop;
961
962 if EC_DEBUG.G_debug_level >= 2 then
963 EC_DEBUG.POP('ECE_FLATFILE_PVT.DEFINE_INTERFACE_COLUMN_TYPE');
964 end if;
965 EXCEPTION
966 when others then
967 app_exception.raise_exception;
968 END DEFINE_INTERFACE_COLUMN_TYPE;
969
970 PROCEDURE ASSIGN_COLUMN_VALUE_TO_TBL (
971 c IN INTEGER,
972 p_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type)
973 IS
974 BEGIN
975 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.ASSIGN_COLUMN_VALUE_TO_TBL');
976 if EC_DEBUG.G_debug_level >= 3 then
977 EC_DEBUG.PL(3, 'Column value: ', c);
978 EC_DEBUG.PL(3, 'Table loop count : ', p_tbl.count);
979 end if;
980 for k in 1..p_tbl.count loop
981 dbms_sql.column_value(c, k, p_tbl(k).value);
982 end loop;
983
984 EC_DEBUG.POP('ECE_FLATFILE_PVT.ASSIGN_COLUMN_VALUE_TO_TBL');
985 EXCEPTION
986 when others then
987 app_exception.raise_exception;
988 END ASSIGN_COLUMN_VALUE_TO_TBL;
989
993 p_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
990 PROCEDURE ASSIGN_COLUMN_VALUE_TO_TBL (
991 c IN INTEGER,
992 iCount IN INTEGER,
994 p_key_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type)
995 IS
996 BEGIN
997 if EC_DEBUG.G_debug_level >= 2 then
998 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.ASSIGN_COLUMN_VALUE_TO_TBL');
999 EC_DEBUG.PL(3, 'Column : ', c);
1000 EC_DEBUG.PL(3, 'iCount : ', iCount);
1001 EC_DEBUG.PL(3, 'Table loop count : ', p_tbl.count);
1002 end if;
1003 for k in 1..p_tbl.count loop
1004 dbms_sql.column_value(c, k, p_tbl(k).value);
1005 dbms_sql.column_value(c, k, p_key_tbl(k+iCount).value);
1006 end loop;
1007
1008 if EC_DEBUG.G_debug_level >= 2 then
1009 EC_DEBUG.POP('ECE_FLATFILE_PVT.ASSIGN_COLUMN_VALUE_TO_TBL');
1010 end if;
1011 EXCEPTION
1012 when others then
1013 app_exception.raise_exception;
1014 END ASSIGN_COLUMN_VALUE_TO_TBL;
1015
1016 FUNCTION POS_OF (pInterface_tbl ece_flatfile_pvt.Interface_tbl_type,
1017 cCol_name VARCHAR2)
1018 RETURN NUMBER
1019 IS
1020 l_Row_count NUMBER := pInterface_tbl.count;
1021 b_found BOOLEAN := FALSE;
1022 pos_not_found EXCEPTION;
1023 cOutput_path VARCHAR2(120);
1024 BEGIN
1025 if EC_DEBUG.G_debug_level >= 2 then
1026 EC_DEBUG.PUSH('ECE_FLATFILE_PVT.POS_OF');
1027 EC_DEBUG.PL(3, 'Row_count : ', l_Row_count);
1028 end if;
1029 For k in 1..l_Row_count loop
1030 if UPPER(nvl(pInterface_tbl(k).interface_column_name, 'NULL')) = UPPER(cCol_name) then
1031 b_found := TRUE;
1032 if EC_DEBUG.G_debug_level >= 2 then
1033 EC_DEBUG.PL(3, 'Position : ', k);
1034 EC_DEBUG.POP('ECE_FLATFILE_PVT.POS_OF');
1035 end if;
1036 return(k);
1037 exit;
1038 end if;
1039 end loop;
1040
1041 if not b_found
1042 then
1043 raise pos_not_found;
1044 end if;
1045
1046 if EC_DEBUG.G_debug_level >= 2 then
1047 EC_DEBUG.POP('ECE_FLATFILE_PVT.POS_OF');
1048 end if;
1049 EXCEPTION
1050 WHEN pos_not_found THEN
1051 fnd_message.set_name('EC','ECE_PLSQL_POS_NOT_FOUND');
1052 fnd_message.set_token('COLUMN_NAME', upper(cCol_name));
1053 raise;
1054 WHEN OTHERS THEN
1055 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_FLATFILE_PVT.POS_OF');
1056 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1057 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1058 app_exception.raise_exception;
1059
1060 END POS_OF;
1061
1062 END ECE_FLATFILE_PVT;