[Home] [Help]
PACKAGE BODY: APPS.ECE_EXTRACT_UTILS_PUB
Source
1 PACKAGE BODY ece_extract_utils_pub AS
2 -- $Header: ECPEXTUB.pls 120.2 2005/09/29 11:39:23 arsriniv ship $
3 debug_mode_on_insert BOOLEAN := FALSE;
4 debug_mode_on_select BOOLEAN := FALSE;
5 debug_mode_on_prod BOOLEAN := FALSE;
6 g_error_count NUMBER := 0;
7
8 --- PROCEDURE select_clause.
9 --- Creation Oct. 24, 1996
10 ---
11 --- Procedure select_clause builds a Select clause and a From
12 --- clause and a Where clause at run time for the dynamic SQL call.
13
14 --- It uses the data from the in-parameter p_source_tbl to
15 --- construst the SELECT clause. This select
16 --- clause had already add the TO_CHAR function to convert
17 --- data to character type.
18 -- The reason for converting data to VARCHAR is to simplify
19 -- the dynamic SQL statement.
20
21 PROCEDURE select_clause(
22 cTransaction_Type IN VARCHAR2,
23 cCommunication_Method IN VARCHAR2,
24 cInterface_Table IN VARCHAR2,
25 p_source_tbl IN ece_flatfile_pvt.Interface_tbl_type,
26 cSelect_string OUT NOCOPY VARCHAR2,
27 cFrom_string OUT NOCOPY VARCHAR2,
28 cWhere_string OUT NOCOPY VARCHAR2) IS
29 xProgress VARCHAR2(30);
30 cOutput_path VARCHAR2(120);
31
32 cSelect_stmt VARCHAR2(32000) := 'SELECT ';
33 cFrom_stmt VARCHAR2(32000) := ' FROM ';
34 cWhere_stmt VARCHAR2(32000) := ' WHERE ';
35
36 cTO_CHAR VARCHAR2(20) := 'TO_CHAR(';
37 cDATE VARCHAR2(40) := ',''YYYYMMDD HH24MISS'')';
38 cWord1 VARCHAR2(20) := ' ';
39 cWord2 VARCHAR2(40) := ' ';
40
41 iRow_count NUMBER := p_source_tbl.count;
42 iDebug NUMBER := 0;
43 BEGIN
44 EC_DEBUG.PUSH('ece_extract_utils_pub.select_clause');
45 if EC_DEBUG.G_debug_level >= 2 then
46 EC_DEBUG.PL(3, 'cTransaction_Type : ',cTransaction_Type);
47 EC_DEBUG.PL(3, 'cCommunication_Method: ',cCommunication_Method);
48 end if;
49
50 xProgress := 'EXTUB-10-1020';
51 For i in 1..iRow_count loop
52
53 xProgress := 'EXTUB-10-1030';
54 g_error_count := i;
55 -- **************************************
56 -- apply appropriate data conversion
57 -- convert everything to VARCHAR
58 -- **************************************
59
60 xProgress := 'EXTUB-10-1040';
61 if 'DATE' = p_source_tbl(i).data_type Then
62 xProgress := 'EXTUB-10-1050';
63 cWord1 := cTO_CHAR;
64 cWord2 := cDATE;
65 if EC_DEBUG.G_debug_level >= 2 then
66 EC_DEBUG.PL(3, 'cWord1: ',cWord1);
67 EC_DEBUG.PL(3, 'cWord2: ',cWord2);
68 end if;
69 elsif 'NUMBER' = p_source_tbl(i).data_type Then
70 xProgress := 'EXTUB-10-1060';
71 cWord1 := cTO_CHAR;
72 cWord2 := ')';
73 if EC_DEBUG.G_debug_level >= 2 then
74 EC_DEBUG.PL(3, 'cWord1: ',cWord1);
75 EC_DEBUG.PL(3, 'cWord2: ',cWord2);
76 end if;
77 else
78 xProgress := 'EXTUB-10-1070';
79 cWord1 := NULL;
80 cWord2 := NULL;
81 END if;
82
83 -- build SELECT statement
84 xProgress := 'EXTUB-10-1080';
85 cSelect_stmt := cSelect_stmt || ' ' || cWord1 || nvl(p_source_tbl(i).base_column_Name,'NULL') || cWord2 || ',';
86 if EC_DEBUG.G_debug_level >= 2 then
87 ec_debug.pl(3,'Counter'||i,p_source_tbl(i).interface_column_name);
88 end if;
89 End Loop;
90
91 -- build FROM, WHERE statements
92
93
94 -- Loop through the pl/sql table until we get a base_table_name
95 -- base_table_name will either be null or the view name we are
96 -- using to extract the data.
97 xProgress := 'EXTUB-10-1090';
98 For i in 1..iRow_count loop
99 if p_source_tbl(i).base_table_name is not null then
100 xProgress := 'EXTUB-10-1095';
101 cFrom_stmt := cFrom_stmt || p_source_tbl(i).base_table_name;
102 exit;
103 end if;
104 End Loop;
105
106 xProgress := 'EXTUB-10-1100';
107 cSelect_string := RTRIM (cSelect_stmt, ',');
108 xProgress := 'EXTUB-10-1110';
109 cFrom_string := cFrom_stmt;
110 xProgress := 'EXTUB-10-1120';
111 cWhere_string := cWhere_stmt;
112
113 if (debug_mode_on_select) then
114 declare
115 stmt_1 varchar2(2000) := substrb(RTRIM(cSelect_stmt, ','), 1, 2000);
116 stmt_2 varchar2(2000) := substrb(RTRIM(cSelect_stmt, ','), 2001, 2000);
117 stmt_3 varchar2(2000) := substrb(RTRIM(cSelect_stmt, ','), 4001, 2000);
118 begin
119 if EC_DEBUG.G_debug_level >= 2 then
120 EC_DEBUG.PL(3, 'stmt_1: ',stmt_1);
121 EC_DEBUG.PL(3, 'stmt_2: ',stmt_2);
122 EC_DEBUG.PL(3, 'stmt_3: ',stmt_3);
123 end if;
124 insert into ece_error (creation_date, run_id, line_id, text)
125 values( sysdate, 76451, ece_error_s.nextval, stmt_1);
126 insert into ece_error (creation_date, run_id, line_id, text)
127 values( sysdate, 76451, ece_error_s.nextval, stmt_2);
128 insert into ece_error (creation_date, run_id, line_id, text)
129 values( sysdate, 76451, ece_error_s.nextval, stmt_3);
130 end;
131 end if;
132
133 EC_DEBUG.POP('ece_extract_utils_pub.select_clause');
134 exception
135 when others then
136 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.Select_Clause');
137 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','xProgress');
138 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
139 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
140
141 EC_DEBUG.PL(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', 'to_char(g_error_count)');
142
143 if g_error_count > 0 then
144 EC_DEBUG.PL(0,'EC','ECE_PLSQL_VALUE','VALUE', 'p_apps_tbl(g_error_count).value');
145
146 EC_DEBUG.PL(0,'EC','ECE_PLSQL_DATA_TYPE','DATA_TYPE', 'p_apps_tbl(g_error_count).data_type');
147
148
149 EC_DEBUG.PL(0,'EC','ECE_PLSQL_COLUMN_NAME','COLUMN_NAME', 'to_char(g_error_count).base_column_name');
150
151 end if;
152 app_exception.raise_exception;
153
154 END select_clause;
155
156
157 -- ******************************************************
158 --
159 -- Insert into interface table
160 --
161 -- This procedure insert data into the EDI interface
162 -- table
163 -- The caller pass in the p_source_tbl which contains
164 -- the data.
165 -- p_source_tbl also stores a pointer (foreign key) to
166 -- identify where should the data be placed in the
167 -- interface table
168 --
169 -- ECE_INTERFACE_COLUMNS serves as a data dictionary
170 -- to store which each data goes in the interface
171 -- table.
172 --
173 -- ******************************************************
174
175 PROCEDURE insert_into_interface_tbl(
176 iRun_id IN NUMBER,
177 cTransaction_Type IN VARCHAR2,
178 cCommunication_Method IN VARCHAR2,
179 cInterface_Table IN VARCHAR2,
180 p_source_tbl IN ece_flatfile_pvt.Interface_tbl_type,
181 p_foreign_key IN NUMBER
182 )
183 IS
184
185 xProgress VARCHAR2(30);
186 cOutput_path VARCHAR2(120);
187
188 cInsert_stmt VARCHAR2(32000) := 'INSERT INTO ';
189 -- cValue_stmt VARCHAR2(32000) := 'VALUES ('||iRun_id||',';
190 cValue_stmt VARCHAR2(32000) := 'VALUES (';
191 cSrc_tbl_val_wo_newl VARCHAR2(400);
192 cSrc_tbl_val_wo_frmf VARCHAR2(400);
193 cSrc_tbl_val_wo_tab VARCHAR2(400);
194 cValue VARCHAR2(2000);
195 /* cTo_NUM VARCHAR2(20) := 'TO_NUMBER(''';
196 cTO_CHAR VARCHAR2(20) := 'TO_CHAR(''';
197 cTO_DATE VARCHAR2(20) := 'TO_DATE(''';
198 cDATE VARCHAR2(40) := ''',''YYYYMMDD HH24MISS'')';
199 cQuote VARCHAR2(20) := '''';
200 cNULL VARCHAR2(20) := 'NULL';
201 cBlank VARCHAR2(20) := '';
202 cWord1 VARCHAR2(20) := ' ';*/
203 d_date DATE;
204 n_number NUMBER;
205 cWord2 VARCHAR2(40) := ' ';
206 c_local_chr_10 VARCHAR2(1) := fnd_global.local_chr(10);
207 c_local_chr_13 VARCHAR2(1) := fnd_global.local_chr(13);
208 c_local_chr_9 VARCHAR2(1) := fnd_global.local_chr(9);
209
210 c_Insert_cur INTEGER;
211 dummy INTEGER;
212
213 l_col_name VARCHAR2(40);
214 l_total_rows_of_value NUMBER := 0;
215 l_Row_count NUMBER;
216
217 TYPE CharTable IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
218 cColumn_val CharTable;
219
220 BEGIN
221
222 if EC_DEBUG.G_debug_level >= 2 then
223 EC_DEBUG.PUSH('ece_extract_utils_pub.insert_into_interface_tbl');
224 EC_DEBUG.PL(3, 'iRun_id : ',iRun_id);
225 EC_DEBUG.PL(3, 'cTransaction_Type: ',cTransaction_Type);
226 EC_DEBUG.PL(3, 'cCommunication_Method: ',cCommunication_Method);
227 EC_DEBUG.PL(3, 'cInterface_Table: ',cInterface_Table);
228 EC_DEBUG.PL(3, 'p_foreign_key: ',p_foreign_key);
229 end if;
230 xProgress := 'EXTUB-20-1020';
231 cInsert_stmt := cInsert_stmt || ' ' || cInterface_Table || '( ';
232 if EC_DEBUG.G_debug_level >= 2 then
233 EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);
234 end if;
235
236 xProgress := 'EXTUB-20-1030';
237 l_Row_count := p_source_tbl.count;
238 if EC_DEBUG.G_debug_level >= 2 then
239 EC_DEBUG.PL(3, 'l_Row_count: ',l_Row_count);
240 end if;
241
242 --Bug 2198707
243 xProgress := 'EXTUB-20-1040';
244 For i in 1..l_Row_count loop
245 if p_source_tbl(i).Interface_Column_Name is not null
246 then --Bug 2239977
247 cInsert_stmt := cInsert_stmt || ' ' ||
248 p_source_tbl(i).interface_column_Name || ',';
249
250 cValue_stmt := cValue_stmt || ':b' || i ||',';
251 end if;
252 end loop;
253
254 xProgress := 'EXTUB-20-1050';
255 cInsert_stmt := RTRIM(cInsert_stmt, ',') || ')';
256
257 xProgress := 'EXTUB-20-1060';
258 cValue_stmt := RTRIM(cValue_stmt, ',') ||')';
259
260 xProgress := 'EXTUB-20-1070';
261 cInsert_stmt := cInsert_stmt || cValue_stmt;
262 if EC_DEBUG.G_debug_level = 3 then
263 EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);
264 end if;
265
266 xProgress := 'EXTUB-20-1080';
267 c_Insert_cur := dbms_sql.open_cursor;
268 if EC_DEBUG.G_debug_level = 3 then
269 EC_DEBUG.PL(3, 'c_Insert_cur: ',c_Insert_cur);
270 end if;
271
272 xProgress := 'EXTUB-20-1090';
273 dbms_sql.parse(c_Insert_cur, cInsert_stmt, dbms_sql.native);
274
275 xProgress := 'EXTUB-20-1100';
276 For i in 1..l_Row_count loop
277
278 -- **************************************
279 --
280 -- For each data, find out where
281 -- should it go in the interface table
282 -- We use data_loc_id as key between the
283 -- ece_source_date_loc and the
284 -- ece_interface_columns table
285 --
286 -- **************************************
287
288 xProgress := 'EXTUB-20-1110';
289 g_error_count := i;
290
291 if p_source_tbl(i).Interface_Column_Name = 'RUN_ID' then
292
293 xProgress := 'EXTUB-20-1120';
294 dbms_sql.bind_variable(c_Insert_cur,'b'||i,iRun_id);
295 elsif p_source_tbl(i).Interface_Column_Name
296 = 'TRANSACTION_RECORD_ID' then
297
298 xProgress := 'EXTUB-20-1130';
299 dbms_sql.bind_variable(c_Insert_cur,'b'||i,p_foreign_key);
300 elsif p_source_tbl(i).Interface_Column_Name is not null
301 then
302
303 -- **************************************
304 --
305 -- apply appropriate data conversion
306 -- All data passed in is in VARCHAR,
307 -- we need to convert it because
308 -- the interface table is expecting the
309 -- correct data type
310 --
311 -- **************************************
312 --Bug 2252075
313
314 cSrc_tbl_val_wo_newl :=
315 replace(p_source_tbl(i).value, c_local_chr_10,'');
316 cSrc_tbl_val_wo_frmf :=
317 replace(cSrc_tbl_val_wo_newl, c_local_chr_13,'');
318 cSrc_tbl_val_wo_tab :=
319 replace(cSrc_tbl_val_wo_frmf, c_local_chr_9,'');
320 --Commented the line 'cValue := replace(cSrc_tbl_val_wo_tab,'''','''''');' 2458190.
321 --cValue := replace(cSrc_tbl_val_wo_tab,'''','''''');
322 cValue := cSrc_tbl_val_wo_tab;
323
324 xProgress := 'EXTUB-20-1140';
325 if 'DATE' = p_source_tbl(i).data_type
326 Then
327 xProgress := 'EXTUB-20-1150';
328 if p_source_tbl(i).value is not NULL
329 then
330 xProgress := 'EXTUB-20-1160';
331 d_date := TO_DATE(cValue,'YYYYMMDD HH24MISS');
332 else
333 xProgress := 'EXTUB-20-1170';
334 d_date := NULL;
335 end if;
336 dbms_sql.bind_variable(c_Insert_cur,'b'||i,d_date);
337 elsif 'NUMBER' = p_source_tbl(i).data_type
338 Then
339 xProgress := 'EXTUB-20-1180';
340 if p_source_tbl(i).value is not NULL
341 then
342 xProgress := 'EXTUB-20-1190';
343 n_number:= TO_NUMBER(cValue);
344 else
345 xProgress := 'EXTUB-20-1200';
346 n_number:= NULL;
347 end if;
348 dbms_sql.bind_variable(c_Insert_cur,'b'||i,n_number);
349 else
350 xProgress := 'EXTUB-20-1210';
351
352 dbms_sql.bind_variable(c_Insert_cur,'b'||i,cValue);
353 END if; -- if DATE
354
355 end if;
356 End Loop;
357
358 /* Bug 2198707 - The following code is commented out.
359 if 'DATE' = p_source_tbl(i).data_type
360 Then
361 xProgress := 'EXTUB-20-1080';
362 if p_source_tbl(i).value is not NULL
363 then
364 xProgress := 'EXTUB-20-1090';
365 cWord1 := cTO_DATE;
366 cWord2 := cDATE;
367 if EC_DEBUG.G_debug_level >= 2 then
368 EC_DEBUG.PL(3, 'cWord1: ',cWord1);
369 EC_DEBUG.PL(3, 'cWord2: ',cWord2);
370 end if;
371 else
372 xProgress := 'EXTUB-20-1100';
373 cWord1 := cQuote;
374 cWord2 := cQuote;
375 if EC_DEBUG.G_debug_level >= 2 then
376 EC_DEBUG.PL(3, 'cWord1: ',cWord1);
377 EC_DEBUG.PL(3, 'cWord2: ',cWord2);
378 end if;
379 end if;
380 elsif 'NUMBER' = p_source_tbl(i).data_type
381 Then
382 xProgress := 'EXTUB-20-1110';
383 if p_source_tbl(i).value is not NULL
384 then
385 xProgress := 'EXTUB-20-1120';
386 cWord1 := cTO_NUM;
387 cWord2 := ''')';
388 if EC_DEBUG.G_debug_level >= 2 then
389 EC_DEBUG.PL(3, 'cWord1: ',cWord1);
390 EC_DEBUG.PL(3, 'cWord2: ',cWord2);
391 end if;
392 else
393 xProgress := 'EXTUB-20-1130';
394 cWord1 := cQuote;
395 cWord2 := cQuote;
396 if EC_DEBUG.G_debug_level >= 2 then
397 EC_DEBUG.PL(3, 'cWord1: ',cWord1);
398 EC_DEBUG.PL(3, 'cWord2: ',cWord2);
399 end if;
400 end if;
401
402 else
403 xProgress := 'EXTUB-20-1140';
404 cWord1 := cQuote;
405 cWord2 := cQuote;
406 if EC_DEBUG.G_debug_level >= 2 then
407 EC_DEBUG.PL(3, 'cWord1: ',cWord1);
408 EC_DEBUG.PL(3, 'cWord2: ',cWord2);
409 end if;
410 END if; -- if DATE
411
412 xProgress := 'EXTUB-20-1150';
413 if p_source_tbl(i).Interface_Column_Name = 'RUN_ID' then
414
415 xProgress := 'EXTUB-20-1160';
416 cValue := to_char(iRun_id);
417 if EC_DEBUG.G_debug_level >= 2 then
418 EC_DEBUG.PL(3, 'cValue: ',cValue);
419 end if;
420 elsif p_source_tbl(i).Interface_Column_Name
421 = 'TRANSACTION_RECORD_ID' then
422
423 xProgress := 'EXTUB-20-1170';
424 cValue := to_char(p_foreign_key);
425 if EC_DEBUG.G_debug_level >= 2 then
426 EC_DEBUG.PL(3, 'cValue: ',cValue);
427 end if;
428 else
429
430 xProgress := 'EXTUB-20-1180';
431 cSrc_tbl_val_wo_newl :=
432 replace(p_source_tbl(i).value, c_local_chr_10,'');
433 cSrc_tbl_val_wo_frmf :=
434 replace(cSrc_tbl_val_wo_newl, c_local_chr_13,'');
435 cSrc_tbl_val_wo_tab :=
436 replace(cSrc_tbl_val_wo_frmf, c_local_chr_9,'');
437 cValue := replace(cSrc_tbl_val_wo_tab,'''','''''');
438 if EC_DEBUG.G_debug_level >= 2 then
439 EC_DEBUG.PL(3, 'cValue: ',cValue);
440 end if;
441 end if;
442
443 -- build INSERT statement
444 xProgress := 'EXTUB-20-1190';
445 cInsert_stmt := cInsert_stmt || ' ' ||
446 p_source_tbl(i).interface_column_Name || ',';
447
448 xProgress := 'EXTUB-20-1200';
449 cValue_stmt := cValue_stmt || cWord1 || cValue || cWord2 ||',';
450
451
452 end if;
453 End Loop;
454
455 xProgress := 'EXTUB-20-1210';
456 cInsert_stmt := RTRIM(cInsert_stmt, ',') || ')';
457
458 xProgress := 'EXTUB-20-1220';
459 cValue_stmt := RTRIM(cValue_stmt, ',') ||')';
460
461 xProgress := 'EXTUB-20-1230';
462 cInsert_stmt := cInsert_stmt || cValue_stmt;
463 if EC_DEBUG.G_debug_level >= 2 then
464 EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);
465 end if;
466 xProgress := 'EXTUB-20-1240';
467 c_Insert_cur := dbms_sql.open_cursor;
468 if EC_DEBUG.G_debug_level >= 2 then
469 EC_DEBUG.PL(3, 'c_Insert_cur: ',c_Insert_cur);
470 end if;
471 xProgress := 'EXTUB-20-1250';
472 dbms_sql.parse(c_Insert_cur, cInsert_stmt, dbms_sql.native);
473 */
474
475 xProgress := 'EXTUB-20-1220';
476 dummy := dbms_sql.execute(c_Insert_cur);
477 if EC_DEBUG.G_debug_level >= 2 then
478 EC_DEBUG.PL(3, 'dummy: ',dummy);
479 end if;
480 if (debug_mode_on_insert)
481 then
482 declare
483 stmt_1 varchar2(2000) := substrb(cInsert_stmt, 1, 2000);
484 stmt_2 varchar2(2000) := substrb(cInsert_stmt, 2001, 2000);
485 stmt_3 varchar2(2000) := substrb(cInsert_stmt, 4001, 2000);
486 stmt_4 varchar2(2000) := substrb(cInsert_stmt, 6001, 2000);
487 stmt_5 varchar2(2000) := substrb(cInsert_stmt, 8001, 2000);
488 begin
489 if EC_DEBUG.G_debug_level >= 2 then
490 EC_DEBUG.PL(3, 'stmt_1: ',stmt_1);
491 EC_DEBUG.PL(3, 'stmt_2: ',stmt_2);
492 EC_DEBUG.PL(3, 'stmt_3: ',stmt_3);
493 EC_DEBUG.PL(3, 'stmt_4: ',stmt_4);
494 EC_DEBUG.PL(3, 'stmt_5: ',stmt_5);
495 end if;
496 insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_1);
497 insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_2);
498 insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_3);
499 insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_4);
500 insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_5);
501 end;
502 --commit;
503 end if;
504
505 xProgress := 'EXTUB-20-1230';
506 dbms_sql.close_cursor(c_Insert_cur);
507
508 if EC_DEBUG.G_debug_level >= 2 then
509 EC_DEBUG.POP('ece_extract_utils_pub.insert_into_interface_tbl');
510 end if;
511 exception
512 when others
513 then
514 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.Insert_into_Interface_tbl');
515 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','xProgress');
516 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
517 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
518
519 EC_DEBUG.PL(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', 'to_char(g_error_count)');
520
521 if g_error_count > 0 then
522 EC_DEBUG.PL(0,'EC','ECE_PLSQL_VALUE','VALUE', 'p_apps_tbl(g_error_count).value');
523
524 EC_DEBUG.PL(0,'EC','ECE_PLSQL_DATA_TYPE','DATA_TYPE', 'p_apps_tbl(g_error_count).data_type');
525
526
527 EC_DEBUG.PL(0,'EC','ECE_PLSQL_COLUMN_NAME','COLUMN_NAME', 'to_char(g_error_count).base_column_name');
528
529 end if;
530 app_exception.raise_exception;
531
532 END insert_into_interface_tbl;
533
534 PROCEDURE insert_into_prod_interface(
535 p_Interface_Table IN VARCHAR2,
536 p_Insert_cur IN OUT NOCOPY INTEGER,
537 p_apps_tbl IN ece_flatfile_pvt.Interface_tbl_type)
538
539 IS
540 xProgress VARCHAR2(30);
541 cOutput_path VARCHAR2(120);
542
543 cInsert_stmt VARCHAR2(32000) := 'INSERT INTO ';
544 cValue_stmt VARCHAR2(32000) := 'VALUES (';
545
546 c_Insert_cur INTEGER ;
547 dummy INTEGER;
548 d_date DATE;
549 n_number NUMBER;
550 c_count NUMBER;
551
552 BEGIN
553
554 if p_Insert_cur = 0
555 then
556 xProgress := 'EXTUB-30-1020';
557 p_Insert_cur := -911;
558 end if;
559
560 xProgress := 'EXTUB-30-1030';
561 if p_Insert_cur < 0
562 then
563
564 xProgress := 'EXTUB-30-1040';
565 cInsert_stmt := cInsert_stmt || ' ' || p_Interface_Table || '(';
566
567 xProgress := 'EXTUB-30-1050';
568 For i in 1..p_apps_tbl.count loop
569
570
571 -- **************************************
572 -- Only insert those data which
573 -- are expected in the open interfaces.
574 --
575 -- The incoming flatfile contains many data
576 -- but not all of them have a corresponding
577 -- column in the open interfaces
578 -- **************************************
579
580 xProgress := 'EXTUB-30-1060';
581 if p_apps_tbl(i).base_column_name is not null
582 then
583 -- build INSERT statement
584
585 xProgress := 'EXTUB-30-1070';
586 cInsert_stmt := cInsert_stmt || ' ' || p_apps_tbl(i).base_column_name || ',';
587
588 xProgress := 'EXTUB-30-1080';
589 cValue_stmt := cValue_stmt || ':b' || i ||',';
590
591 end if;
592
593 end loop;
594
595 xProgress := 'EXTUB-30-1090';
596 cInsert_stmt := RTRIM (cInsert_stmt, ',') || ') ';
597
598 xProgress := 'EXTUB-30-1100';
599 cValue_stmt := RTRIM (cValue_stmt, ',') || ')';
600
601 xProgress := 'EXTUB-30-1110';
602 cInsert_stmt := cInsert_stmt || cValue_stmt;
603
604 xProgress := 'EXTUB-30-1110';
605 p_Insert_cur := dbms_sql.open_cursor;
606
607 xProgress := 'EXTUB-30-1120';
608 if (debug_mode_on_prod)
609 then
610 declare
611 stmt_1 varchar2(2000) := substrb(cInsert_stmt, 1, 2000);
612 stmt_2 varchar2(2000) := substrb(cInsert_stmt, 2001, 2000);
613 stmt_3 varchar2(2000) := substrb(cInsert_stmt, 4001, 2000);
614 stmt_4 varchar2(2000) := substrb(cInsert_stmt, 6001, 2000);
615 stmt_5 varchar2(2000) := substrb(cInsert_stmt, 8001, 2000);
616 begin
617 insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_1);
618 insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_2);
619 insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_3);
620 insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_4);
621 insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_5);
622 end;
623 --commit;
624 end if;
625
626
627 xProgress := 'EXTUB-30-1130';
628 dbms_sql.parse(p_Insert_cur, cInsert_stmt, dbms_sql.native);
629 end if;
630
631 --if 1 =0
632 xProgress := 'EXTUB-30-1140';
633 if p_Insert_cur > 0
634 then
635
636 begin
637 xProgress := 'EXTUB-30-1150';
638 for k in 1..p_apps_tbl.count
639 loop
640
641 xProgress := 'EXTUB-30-1160';
642 g_error_count := k;
643
644 xProgress := 'EXTUB-30-1170';
645 if p_apps_tbl(k).base_column_name is not null
646 then
647
648 xProgress := 'EXTUB-30-1180';
649 if 'DATE' = p_apps_tbl(k).data_type
650 Then
651 xProgress := 'EXTUB-30-1190';
652 if p_apps_tbl(k).value is not NULL
653 then
654 xProgress := 'EXTUB-30-1200';
655 d_date := to_date(p_apps_tbl(k).value,'YYYYMMDD HH24MISS');
656 else
657 xProgress := 'EXTUB-30-1210';
658 d_date := NULL;
659 end if;
660 xProgress := 'EXTUB-30-1220';
661 dbms_sql.bind_variable(p_Insert_cur,
662 'b'||k,
663 d_date);
664
665 if (debug_mode_on_prod)
666 then
667 insert into ece_error (run_id, line_id, text) values
668 ( 88, ece_error_s.nextval, 'b' ||k|| ' = '||d_date);
669 end if;
670 elsif 'NUMBER' = p_apps_tbl(k).data_type
671 then
672 xProgress := 'EXTUB-30-1230';
673 if p_apps_tbl(k).value is not NULL
674 then
675 xProgress := 'EXTUB-30-1240';
676 n_number := to_number(p_apps_tbl(k).value);
677 else
678 xProgress := 'EXTUB-30-1250';
679 n_number := NULL;
680 end if;
681 xProgress := 'EXTUB-30-1260';
682 dbms_sql.bind_variable(p_Insert_cur,
683 'b'||k,
684 n_number);
685 if (debug_mode_on_prod)
686 then
687 insert into ece_error (run_id, line_id, text) values
688 ( 88, ece_error_s.nextval, 'b'||k|| ' ='||n_number);
689 end if;
690 else
691 xProgress := 'EXTUB-30-1270';
692 dbms_sql.bind_variable(p_Insert_cur,
693 'b'||k,
694 substrb(p_apps_tbl(k).value,
695 1,
696 p_apps_tbl(k).data_length));
697 if (debug_mode_on_prod)
698 then
699 insert into ece_error (run_id, line_id, text) values
700 ( 88, ece_error_s.nextval, 'b'||k|| ' ='||p_apps_tbl(k).value);
701 end if;
702 end if;
703 --commit;
704 end if;
705
706 end loop;
707
708 xProgress := 'EXTUB-30-1280';
709 dummy := dbms_sql.execute(p_Insert_cur);
710 exception
711 when others then
712 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.insert_into_prod_interface');
713 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
714 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
715 EC_DEBUG.PL(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', g_error_count);
716
717 if g_error_count > 0 then
718 EC_DEBUG.PL(0,'EC','ECE_PLSQL_VALUE','VALUE', p_apps_tbl(g_error_count).value);
719
720 EC_DEBUG.PL(0,'EC','ECE_PLSQL_DATA_TYPE','DATA_TYPE', p_apps_tbl(g_error_count).data_type);
721
722
723 EC_DEBUG.PL(0,'EC','ECE_PLSQL_COLUMN_NAME','COLUMN_NAME', p_apps_tbl(g_error_count).base_column_name);
724 end if;
725 raise;
726 end;
727
728 end if;
729
730 END insert_into_prod_interface;
731
732 -- ******************************************************
733 --
734 -- Insert into product open interface tables
735 --
736 -- This procedure insert data into the product (non-EDI Gateway)
737 -- interface table
738 -- The caller pass in the p_source_tbl which contains
739 -- the data.
740 -- p_source_tbl also stores a pointer (foreign key) to
741 -- identify where should the data be placed in the
742 -- interface table
743 --
744
745
746 -- ECE_INTERFACE_COLUMNS serves as a data dictionary
747 -- to store which each data goes in the interface
748 -- table.
749 --
750 --
751 -- WARNING:
752 -- The first call to this function,
753 -- p_Insert_cur M U S T be zero (0).
754 -- ******************************************************
755 PROCEDURE insert_into_prod_interface_pvt(
756 p_api_version_number IN NUMBER,
757 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
758 p_simulate IN VARCHAR2 := fnd_api.g_false,
759 p_commit IN VARCHAR2 := fnd_api.g_false,
760 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
761 x_return_status OUT NOCOPY VARCHAR2,
762 x_msg_count OUT NOCOPY NUMBER,
763 x_msg_data OUT NOCOPY VARCHAR2,
764 p_interface_table IN VARCHAR2,
765 p_insert_cur IN OUT NOCOPY INTEGER,
766 p_apps_tbl IN ece_flatfile_pvt.Interface_tbl_type) IS
767
768 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_prod_interface_pvt';
769 l_api_version_number CONSTANT NUMBER := 1.0;
770 l_return_status VARCHAR2(10);
771
772 xProgress VARCHAR2(30);
773 cOutput_path VARCHAR2(120);
774
775 cInsert_stmt VARCHAR2(32000) := 'INSERT INTO ';
776 cValue_stmt VARCHAR2(32000) := 'VALUES (';
777
778 c_Insert_cur INTEGER ;
779 dummy INTEGER;
780 d_date DATE;
781 n_number NUMBER;
782 c_count NUMBER;
783
784 BEGIN
785 EC_DEBUG.PUSH('ece_extract_utils_pub.insert_into_prod_interface_pvt');
786 if EC_DEBUG.G_debug_level >= 2 then
787 EC_DEBUG.PL(3, 'p_api_version_number : ',p_api_version_number);
788 EC_DEBUG.PL(3, 'p_init_msg_list: ',p_init_msg_list);
789 EC_DEBUG.PL(3, 'p_simulate: ',p_simulate);
790 EC_DEBUG.PL(3, 'p_commit: ',p_commit);
791 EC_DEBUG.PL(3, 'p_validation_level: ',p_validation_level);
792 EC_DEBUG.PL(3, 'p_interface_table: ',p_interface_table);
793 EC_DEBUG.PL(3, 'p_insert_cur: ',p_insert_cur);
794 end if;
795 -- Standard Start of API savepoint
796 SAVEPOINT insert_into_prod_interface_pvt;
797
798 -- Standard call to check for call compatibility.
799 IF NOT fnd_api.compatible_api_call(
800 l_api_version_number,
801 p_api_version_number,
802 l_api_name,
803 g_pkg_name) THEN
804 RAISE fnd_api.g_exc_unexpected_error;
805 END IF;
806
807 -- Initialize message list if p_init_msg_list is set to TRUE.
808 IF fnd_api.to_boolean(p_init_msg_list) THEN
809 fnd_msg_pub.initialize;
810 END IF;
811
812 -- Initialize API return status to success
813 x_return_status := fnd_api.g_ret_sts_success;
814 if EC_DEBUG.G_debug_level >= 2 then
815 EC_DEBUG.PL(3, 'x_return_status: ',x_return_status);
816 end if;
817 IF p_insert_cur = 0 THEN
818 xProgress := 'EXTUB-30-1020';
819 p_insert_cur := -911;
820 END IF;
821
822 xProgress := 'EXTUB-30-1030';
823 IF p_insert_cur < 0 THEN
824 xProgress := 'EXTUB-30-1040';
825 cInsert_stmt := cInsert_stmt || ' ' || p_Interface_Table || '(';
826 if EC_DEBUG.G_debug_level >= 2 then
827 EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);
828 end if;
829 xProgress := 'EXTUB-30-1050';
830 FOR i IN 1..p_apps_tbl.COUNT LOOP
831
832 -- **************************************
833 -- Only insert those data which
834 -- are expected in the open interfaces.
835 --
836 -- The incoming flatfile contains many data
837 -- but not all of them have a corresponding
838 -- column in the open interfaces
839 -- **************************************
840 xProgress := 'EXTUB-30-1060';
841 IF p_apps_tbl(i).base_column_name IS NOT NULL THEN
842 -- build INSERT statement
843 xProgress := 'EXTUB-30-1070';
844 cInsert_stmt := cInsert_stmt || ' ' || p_apps_tbl(i).base_column_name || ',';
845 if EC_DEBUG.G_debug_level >= 2 then
846 EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);
847 end if;
848 xProgress := 'EXTUB-30-1080';
849 cValue_stmt := cValue_stmt || ':b' || i || ',';
850 if EC_DEBUG.G_debug_level >= 2 then
851 EC_DEBUG.PL(3, 'cValue_stmt: ',cValue_stmt);
852 end if;
853 END IF;
854 END LOOP;
855
856 xProgress := 'EXTUB-30-1090';
857 cInsert_stmt := RTRIM(cInsert_stmt,',') || ') ';
858 if EC_DEBUG.G_debug_level >= 2 then
859 EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);
860 end if;
861 xProgress := 'EXTUB-30-1100';
862 cValue_stmt := RTRIM(cValue_stmt,',') || ')';
863 if EC_DEBUG.G_debug_level >= 2 then
864 EC_DEBUG.PL(3, 'cValue_stmt: ',cValue_stmt);
865 end if;
866 xProgress := 'EXTUB-30-1110';
867 cInsert_stmt := cInsert_stmt || cValue_stmt;
868 if EC_DEBUG.G_debug_level >= 2 then
869 EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);
870 end if;
871 xProgress := 'EXTUB-30-1110';
872 p_Insert_cur := dbms_sql.open_cursor;
873 if EC_DEBUG.G_debug_level >= 2 then
874 EC_DEBUG.PL(3, 'p_Insert_cur: ',p_Insert_cur);
875 end if;
876 xProgress := 'EXTUB-30-1120';
877 IF debug_mode_on_prod THEN
878 DECLARE
879 stmt_1 VARCHAR2(2000) := SUBSTR(cInsert_stmt,1, 2000);
880 stmt_2 VARCHAR2(2000) := SUBSTR(cInsert_stmt,2001,2000);
881 stmt_3 VARCHAR2(2000) := SUBSTR(cInsert_stmt,4001,2000);
882 stmt_4 VARCHAR2(2000) := SUBSTR(cInsert_stmt,6001,2000);
883 stmt_5 VARCHAR2(2000) := SUBSTR(cInsert_stmt,8001,2000);
884
885 BEGIN
886 if EC_DEBUG.G_debug_level >= 2 then
887 EC_DEBUG.PL(3, 'stmt_1: ',stmt_1);
888 EC_DEBUG.PL(3, 'stmt_2: ',stmt_2);
889 EC_DEBUG.PL(3, 'stmt_3: ',stmt_3);
890 EC_DEBUG.PL(3, 'stmt_4: ',stmt_4);
891 EC_DEBUG.PL(3, 'stmt_5: ',stmt_5);
892 end if;
893 INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_1);
894 INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_2);
895 INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_3);
896 INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_4);
897 INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_5);
898 END;
899 -- COMMIT;
900 END IF;
901
902 xProgress := 'EXTUB-30-1130';
903 BEGIN
904 xProgress := 'EXTUB-30-1131';
905 dbms_sql.parse(p_Insert_cur,cInsert_stmt,dbms_sql.native);
906
907 EXCEPTION
908 WHEN OTHERS THEN
909 ROLLBACK TO insert_into_prod_interface_pvt;
910 ece_error_handling_pvt.print_parse_error(
911 dbms_sql.last_error_position,
912 cInsert_stmt);
913 fnd_msg_pub.count_and_get(
914 p_count => x_msg_count,
915 p_data => x_msg_data);
916 RAISE;
917 END;
918 END IF;
919
920 xProgress := 'EXTUB-30-1140';
921 IF p_Insert_cur > 0 THEN
922 BEGIN
923 xProgress := 'EXTUB-30-1150';
924 FOR k IN 1..p_apps_tbl.COUNT LOOP
925 xProgress := 'EXTUB-30-1160';
926 g_error_count := k;
927 if EC_DEBUG.G_debug_level >= 2 then
928 EC_DEBUG.PL(3, 'g_error_count: ',g_error_count);
929 end if;
930 xProgress := 'EXTUB-30-1170';
931 IF p_apps_tbl(k).base_column_name IS NOT NULL THEN
932 xProgress := 'EXTUB-30-1180';
933 IF 'DATE' = p_apps_tbl(k).data_type THEN
934 xProgress := 'EXTUB-30-1190';
935 IF p_apps_tbl(k).value IS NOT NULL THEN
936 xProgress := 'EXTUB-30-1200';
937 d_date := TO_DATE(p_apps_tbl(k).value,'YYYYMMDD HH24MISS');
938 if EC_DEBUG.G_debug_level >= 2 then
939 EC_DEBUG.PL(3, 'd_date: ',d_date);
940 end if;
941 ELSE
942 xProgress := 'EXTUB-30-1210';
943 d_date := NULL;
944 END IF;
945
946 xProgress := 'EXTUB-30-1220';
947 dbms_sql.bind_variable(p_Insert_cur,'b'|| k,d_date);
948
949 IF debug_mode_on_prod THEN
950 INSERT INTO ece_error(run_id,line_id,text) VALUES(88,ece_error_s.NEXTVAL,'b' || k || ' = ' || d_date);
951 END IF;
952 ELSIF 'NUMBER' = p_apps_tbl(k).data_type THEN
953 xProgress := 'EXTUB-30-1230';
954 IF p_apps_tbl(k).value IS NOT NULL THEN
955 xProgress := 'EXTUB-30-1240';
956 n_number := TO_NUMBER(p_apps_tbl(k).value);
957 if EC_DEBUG.G_debug_level >= 2 then
958 EC_DEBUG.PL(3, 'n_number: ',n_number);
959 end if;
960 ELSE
961 xProgress := 'EXTUB-30-1250';
962 n_number := NULL;
963 END IF;
964
965 xProgress := 'EXTUB-30-1260';
966 dbms_sql.bind_variable(p_Insert_cur,'b' || k,n_number);
967
968 IF debug_mode_on_prod THEN
969 INSERT INTO ece_error(run_id,line_id,text) VALUES(88,ece_error_s.NEXTVAL,'b' || k || ' =' || n_number);
970 END IF;
971 ELSE
972 xProgress := 'EXTUB-30-1270';
973 dbms_sql.bind_variable(p_Insert_cur,'b' || k,SUBSTR(p_apps_tbl(k).value,1,p_apps_tbl(k).data_length));
974
975 IF debug_mode_on_prod THEN
976 INSERT INTO ece_error(run_id,line_id,text) VALUES(88,ece_error_s.NEXTVAL,'b' || k || ' =' || p_apps_tbl(k).value);
977 END IF;
978 END IF;
979
980 --commit;
981 END IF;
982 END LOOP;
983
984 xProgress := 'EXTUB-30-1280';
985 dummy := dbms_sql.execute(p_Insert_cur);
986
987 EXCEPTION
988 WHEN OTHERS THEN
989 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.insert_into_prod_interface_pvt');
990 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
991 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
992
993 EC_DEBUG.PL(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', 'to_char(g_error_count)');
994
995 IF g_error_count > 0 THEN
996 EC_DEBUG.PL(0,'EC','ECE_PLSQL_VALUE','VALUE', 'p_apps_tbl(g_error_count).value');
997
998 EC_DEBUG.PL(0,'EC','ECE_PLSQL_DATA_TYPE','DATA_TYPE', 'p_apps_tbl(g_error_count).data_type');
999
1000
1001 EC_DEBUG.PL(0,'EC','ECE_PLSQL_COLUMN_NAME','COLUMN_NAME', 'to_char(g_error_count).base_column_name');
1002 END IF;
1003
1004 app_exception.raise_exception;
1005 RAISE;
1006 END;
1007
1008 END IF;
1009
1010 -- Both G_EXC_ERROR and G_EXC_UNEXPECTED_ERROR are handled in
1011 -- the API exception handler.
1012 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1013 -- Unexpected error, abort processing.
1014 RAISE fnd_api.g_exc_unexpected_error;
1015 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1016 -- Error, abort processing
1017 RAISE fnd_api.g_exc_error;
1018 END IF;
1019
1020 -- Standard check of p_simulate and p_commit parameters
1021 IF fnd_api.to_boolean(p_simulate) THEN
1022 ROLLBACK TO insert_into_prod_interface_pvt;
1023 ELSIF fnd_api.to_boolean(p_commit) THEN
1024 COMMIT WORK;
1025 END IF;
1026
1027 -- Standard call to get message count and if count is 1, get message info.
1028 fnd_msg_pub.count_and_get(
1029 p_count => x_msg_count,
1030 p_data => x_msg_data);
1031 EC_DEBUG.POP('ece_extract_utils_pub.insert_into_prod_interface_pvt');
1032
1033 EXCEPTION
1034 WHEN fnd_api.g_exc_error THEN
1035 ROLLBACK TO insert_into_prod_interface_pvt;
1036 x_return_status := fnd_api.g_ret_sts_error;
1037
1038 fnd_msg_pub.count_and_get(
1039 p_count => x_msg_count,
1040 p_data => x_msg_data);
1041 WHEN fnd_api.g_exc_unexpected_error THEN
1042 ROLLBACK TO insert_into_prod_interface_pvt;
1043 x_return_status := fnd_api.g_ret_sts_error;
1044
1045 fnd_msg_pub.count_and_get(
1046 p_count => x_msg_count,
1047 p_data => x_msg_data);
1048 WHEN OTHERS THEN
1049 ece_error_handling_pvt.print_parse_error(
1050 dbms_sql.last_error_position,
1051 '');
1052 ROLLBACK TO insert_into_prod_interface_pvt;
1053 x_return_status := fnd_api.g_ret_sts_error;
1054
1055 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1056 fnd_msg_pub.add_exc_msg(
1057 g_file_name,
1058 g_pkg_name,
1059 l_api_name);
1060 END IF;
1061
1062 fnd_msg_pub.count_and_get(
1063 p_count => x_msg_count,
1064 p_data => x_msg_data);
1065
1066 END insert_into_prod_interface_pvt;
1067
1068 PROCEDURE find_pos(
1069 p_source_tbl IN ece_flatfile_pvt.Interface_tbl_type,
1070 p_in_text IN VARCHAR2,
1071 p_pos IN OUT NOCOPY NUMBER) IS
1072
1073 cIn_string VARCHAR2(1000) := UPPER(p_in_text);
1074 l_Row_count NUMBER := p_source_tbl.COUNT;
1075 b_found BOOLEAN := FALSE;
1076 pos_not_found EXCEPTION;
1077 cOutput_path VARCHAR2(120);
1078
1079 BEGIN
1080 ec_debug.push('ECE_EXTRACT_UTILS_PUB.FIND_POS');
1081 if EC_DEBUG.G_debug_level >= 2 then
1082 ec_debug.pl(3,'p_in_text: ',p_in_text);
1083 end if;
1084 FOR k IN 1..l_row_count LOOP
1085 IF UPPER(NVL(p_source_tbl(k).base_column_name,'NULL')) = cIn_string THEN
1086 p_Pos := k;
1087 b_found := TRUE;
1088 if EC_DEBUG.G_debug_level >= 2 then
1089 ec_debug.pl(3,'p_pos: ',p_pos);
1090 end if;
1091 EXIT;
1092 END IF;
1093 END LOOP;
1094
1095 IF NOT b_found THEN
1096 RAISE pos_not_found;
1097 END IF;
1098
1099 ec_debug.pop('ECE_EXTRACT_UTILS_PUB.FIND_POS');
1100
1101 EXCEPTION
1102 WHEN pos_not_found THEN
1103 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME',cIn_string);
1104 app_exception.raise_exception;
1105
1106 WHEN OTHERS THEN
1107 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.FIND_POS');
1108 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1109 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1110 app_exception.raise_exception;
1111
1112 END find_pos;
1113
1114 FUNCTION POS_OF (pInterface_tbl IN ece_flatfile_pvt.Interface_tbl_type,
1115 cCol_name IN VARCHAR2)
1116 RETURN NUMBER
1117 IS
1118 l_Row_count NUMBER := pInterface_tbl.count;
1119 b_found BOOLEAN := FALSE;
1120 pos_not_found EXCEPTION;
1121 cOutput_path VARCHAR2(120);
1122 BEGIN
1123 EC_DEBUG.PUSH('ece_extract_utils_pub.POS_OF');
1124 if EC_DEBUG.G_debug_level >= 2 then
1125 EC_DEBUG.PL(3, 'cCol_name: ',cCol_name);
1126 end if;
1127
1128 For k in 1..l_Row_count loop
1129 if UPPER(nvl(pInterface_tbl(k).base_column_name, 'NULL')) = UPPER(cCol_name) then
1130 b_found := TRUE;
1131 if EC_DEBUG.G_debug_level >= 2 then
1132 EC_DEBUG.PL(3, 'k: ',k);
1133 end if;
1134 EC_DEBUG.POP('ece_extract_utils_pub.POS_OF');
1135 return(k);
1136 exit;
1137 end if;
1138 end loop;
1139
1140 if not b_found
1141 then
1142 raise pos_not_found;
1143 end if;
1144
1145 EXCEPTION
1146 WHEN pos_not_found THEN
1147 EC_DEBUG.PL(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME', 'upper(cCol_name)');
1148 app_exception.raise_exception;
1149 WHEN OTHERS THEN
1150 EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.POS_OF');
1151 EC_DEBUG.PL(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1152 EC_DEBUG.PL(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1153 app_exception.raise_exception;
1154 END pos_of;
1155
1156 -- 2823215
1157 PROCEDURE ext_get_value(
1158 l_plsql_tbl IN ece_flatfile_pvt.Interface_tbl_type,
1159 p_in_text IN VARCHAR2,
1160 p_Position IN OUT NOCOPY NUMBER,
1161 o_value OUT NOCOPY varchar2)
1162 IS
1163 cIn_string VARCHAR2(1000) := UPPER(p_in_text);
1164 l_Row_count NUMBER := l_plsql_tbl.COUNT;
1165 b_found BOOLEAN := FALSE;
1166 pos_not_found EXCEPTION;
1167 cOutput_path VARCHAR2(120);
1168
1169 BEGIN
1170 if EC_DEBUG.G_debug_level >= 2 then
1171 ec_debug.push('ECE_EXTRACT_UTILS_PUB.EXT_GET_VALUE');
1172 ec_debug.pl(3,'p_in_text: ',p_in_text);
1173 end if;
1174 FOR k IN 1..l_row_count LOOP
1175 IF UPPER(NVL(l_plsql_tbl(k).ext_column_name,'NULL')) = cIn_string THEN
1176 p_Position := k;
1177 o_value := l_plsql_tbl(k).value;
1178 b_found := TRUE;
1179 EXIT;
1180 END IF;
1181 END LOOP;
1182
1183 IF NOT b_found THEN
1184 RAISE pos_not_found;
1185 END IF;
1186
1187 if EC_DEBUG.G_debug_level >= 2 then
1188 ec_debug.pl(3,'p_position: ',p_position);
1189 ec_debug.pl(3,'o_value: ',o_value);
1190 ec_debug.pop('ECE_EXTRACT_UTILS_PUB.EXT_GET_VALUE');
1191 end if;
1192
1193 EXCEPTION
1194 WHEN pos_not_found THEN
1195 ec_debug.pl(0,'EC','ECE_PLSQL_POS_NOT_FOUND','COLUMN_NAME',cIn_string);
1196 app_exception.raise_exception;
1197
1198 WHEN OTHERS THEN
1199 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.EXT_GET_VALUE');
1200 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1201 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1202 app_exception.raise_exception;
1203 END ext_get_value;
1204
1205 PROCEDURE ext_insert_value(
1206 l_plsql_tbl IN OUT NOCOPY ece_flatfile_pvt.Interface_tbl_type,
1207 p_position IN number,
1208 p_value IN varchar2)
1209 IS
1210 BEGIN
1211 if EC_DEBUG.G_debug_level >= 2 then
1212 ec_debug.push('ECE_EXTRACT_UTILS_PUB.EXT_INSERT_VALUE');
1213 ec_debug.pl(3,'p_position: ',p_position);
1214 ec_debug.pl(3,'p_value: ',p_value);
1215 end if;
1216 l_plsql_tbl(p_position).value := p_value;
1217
1218 if EC_DEBUG.G_debug_level >= 2 then
1219 ec_debug.pop('ECE_EXTRACT_UTILS_PUB.EXT_INSERT_VALUE');
1220 end if;
1221
1222 EXCEPTION
1223 WHEN OTHERS THEN
1224 ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.EXT_INSERT_VALUE');
1225 ec_debug.pl(0,'EC','ECE_ERROR_CODE','ERROR_CODE',SQLCODE);
1226 ec_debug.pl(0,'EC','ECE_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1227 app_exception.raise_exception;
1228 end ext_insert_value;
1229
1230 END ece_extract_utils_pub;
1231