1 PACKAGE BODY ECE_UTILITIES AS
2 -- $Header: ECEUTILB.pls 120.2 2005/09/29 08:56:02 arsriniv ship $
3
4
5 /************************************************************
6 This procedure tests the code conversion functionality.
7 The procedure will return the five external values
8 for the single Internal value provide for Outbound
9 (cDirection=OUT). It will return the single internal
10 value for the five external values provided for
11 Inbound (cDirection=IN).
12 ************************************************************/
13 PROCEDURE TEST_XREF_API (
14 cDirection IN VARCHAR2, -- Values: OUT or IN
15 cTransaction_code IN VARCHAR2,
16 cView_name IN VARCHAR2,
17 cView_column IN VARCHAR2,
18 cInternal_value IN OUT NOCOPY VARCHAR2,
19 cKey1_value IN VARCHAR2,
20 cKey2_value IN VARCHAR2,
21 cKey3_value IN VARCHAR2,
22 cKey4_value IN VARCHAR2,
23 cKey5_value IN VARCHAR2,
24 cExt1_value IN OUT NOCOPY VARCHAR2,
25 cExt2_value IN OUT NOCOPY VARCHAR2,
26 cExt3_value IN OUT NOCOPY VARCHAR2,
27 cExt4_value IN OUT NOCOPY VARCHAR2,
28 cExt5_value IN OUT NOCOPY VARCHAR2)
29
30 /*
31 This procedure will test the code conversion routines.
32 * Direction must be:
33 OUT - To test outbound xref (Internal to External)
34 IN - To test inbound xref (External to Internal)
35
36 * View Name is the name of the EDI view in outbound transactions
37 and the Base Applications (AP,AR,PO,OE,etc) Open API
38 interface table name in inbound transactions.
39
40 * View Columns is the column in the EDI view for outbound
41 transactions and the Base Applications Open API
42 interface table column for inbound transactions.
43
44 * If testing for an outbound transaction, then this API
45 requires the cInternal_value and optionally
46 cKey1_value through cKey5_value parameters.
47
48 * If testing for an inbound transaction, then this API
49 requires at least cExt1_value and optionally
50 cExt2_value thourgh cExt5_value and also optioanlly
51 Key1_value through cKey5_value parameters.
52
53 * If testing an outbound transaction, cExt1_value
54 through cExt5_value will be the out parameters
55 returned to the calling procedure. If testing
56 for an inbound transaction, then cInternal_value
57 will be the only out parameter.
58 */
59
60 is
61
62 l_return_status VARCHAR2(15);
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(2000);
65 cCategory_code VARCHAR2(50);
66 xProgress VARCHAR2(20);
67
68 begin
69
70 xProgress := 'TEST_XREF-10';
71 /**********************************************
72 Get the category code based on the table and
73 column specified.
74 **********************************************/
75 select distinct cat.xref_category_code
76 into cCategory_code
77 from ece_interface_tables t,
78 ece_interface_columns col,
79 ece_xref_categories cat
80 where t.interface_table_id = col.interface_table_id
81 and cat.xref_category_id = col.xref_category_id
82 and t.transaction_type = cTransaction_code
83 and col.base_table_name = cView_name
84 and col.base_column_name = cView_column;
85
86 xProgress := 'TEST_XREF-20';
87
88 /*************************************************
89 If the direction is OUT (outbound), then test the
90 conversion from internal values to external values.
91 *************************************************/
92 if cDirection = 'OUT' then
93 xProgress := 'TEST_XREF-30';
94 EC_Code_Conversion_PVT.Convert_from_int_to_ext
95 ( p_api_version_number => 1.0,
96 p_return_status => l_return_status,
97 p_msg_count => l_msg_count,
98 p_msg_data => l_msg_data,
99 p_Category => cCategory_code,
100 p_Int_val => cInternal_value,
101 p_Key1 => cKey1_value,
102 p_Key2 => cKey2_value,
103 p_Key3 => cKey3_value,
104 p_Key4 => cKey4_value,
105 p_Key5 => cKey5_value,
106 p_Ext_val1 => cExt1_value,
107 p_Ext_val2 => cExt2_value,
108 p_Ext_val3 => cExt3_value,
109 p_Ext_val4 => cExt4_value,
110 p_Ext_val5 => cExt5_value
111 );
112
113 /*************************************************
114 If the direction is IN (inbound), then test the
115 conversion from external to internal values.
116 *************************************************/
117 elsif cDirection = 'IN' then
118 xProgress := 'TEST_XREF-40';
119 EC_Code_Conversion_PVT.Convert_from_ext_to_int
120 ( p_api_version_number => 1.0,
121 p_return_status => l_return_status,
122 p_msg_count => l_msg_count,
123 p_msg_data => l_msg_data,
124 p_Category => cCategory_code,
125 p_Ext_val1 => cExt1_value,
126 p_Ext_val2 => cExt2_value,
127 p_Ext_val3 => cExt3_value,
128 p_Ext_val4 => cExt4_value,
129 p_Ext_val5 => cExt5_value,
130 p_Key1 => cKey1_value,
131 p_Key2 => cKey2_value,
132 p_Key3 => cKey3_value,
133 p_Key4 => cKey4_value,
134 p_Key5 => cKey5_value,
135 p_Int_val => cInternal_value
136 );
137
138 end if;
139 xProgress := 'TEST_XREF-50';
140
141 EXCEPTION
142 when others then
143 app_exception.raise_exception;
144
145 end TEST_XREF_API;
146
147
148 /**********************************************************
149 This procedure will validate the seed data contained in the
150 EDI Gateway data dictionary against what the database
151 actually has.
152
153 If bErrors_found returns a TRUE, then the calling procedure
154 should so a select from ECE_OUTPUT where run_id = iRun_id
155 to see the errors.
156
157 The calling routine should also clean up the ECE_OUTPUT
158 table when finished by deleting the records where
159 run_id = iRun_id.
160 *********************************************************/
161 PROCEDURE SEED_DATA_CHECK (
162 cTransaction_code IN VARCHAR2,
163 bErrors_found OUT NOCOPY BOOLEAN,
164 iRun_id OUT NOCOPY NUMBER,
165 bCheckLength IN BOOLEAN DEFAULT FALSE,
166 bCheckDatatype IN BOOLEAN DEFAULT FALSE,
167 bInsertErrors IN BOOLEAN DEFAULT FALSE)
168 is
169 cursor c_atc(my_table VARCHAR2, my_column VARCHAR2) is
170 select data_length,
171 data_type
172 from user_tab_columns
173 where column_name = my_column
174 and table_name = my_table;
175
176 cursor c_inc is
177 select
178 eit.interface_table_name,
179 eit.key_column_name,
180 eic.interface_column_name,
181 eic.base_table_name,
182 eic.base_column_nAme,
183 eic.data_type,
184 eic.width
185 from ece_interface_tables eit,
186 ece_interface_columns eic
187 where eit.interface_table_id = eic.interface_table_id
188 and eit.transaction_type = UPPER(cTransaction_code)
189 order by eit.interface_table_name;
190
191 eic_rec c_inc%ROWTYPE;
192
193 cursor c_int_table is
194 select
195 eit.interface_table_name,
196 eit.start_number,
197 eit.output_level,
198 eit.key_column_name
199 from
200 ece_interface_tables eit
201 where
202 eit.transaction_type = UPPER(cTransaction_code);
203
204 eit_rec c_int_table%ROWTYPE;
205
206
207 cursor test_output_level(xTrans VARCHAR2, xLevel VARCHAR2) is
208 select lookup_type
209 from ece_lookup_values
210 where lookup_type = 'OUTPUT_LEVEL_'||xTrans
211 and lookup_code = xLevel;
212
213 x_width NUMBER;
214 x_datatype VARCHAR2(30);
215 x_msg VARCHAR2(300);
216 x_errcol VARCHAR2(60);
217 x_errtbl VARCHAR2(60);
218 x_errdatatype VARCHAR2(30);
219 xTemp VARCHAR2(80);
220 xCurTable VARCHAR2(50) := 'X';
221 xDirection VARCHAR2(10);
222 cDirection VARCHAR2(10);
223
224 col_not_found EXCEPTION;
225 wrong_datatype EXCEPTION;
226 bad_width EXCEPTION;
227 no_output_level EXCEPTION;
228 key_col_not_defined EXCEPTION;
229
230 begin
231 /****************************************************
232 Get the next run id. This is the key for this run
233 in the ece_output table.
234 ****************************************************/
235 select ece_output_runs_s.nextval
236 into iRun_id from dual;
237
238 begin
239 select direction
240 into xDirection
241 from ece_interface_tables
242 where transaction_type = cTransaction_code
243 and output_level = '1';
244
245 if xDirection = 'I' then
246 cDirection := 'IN';
247 else
248 cDirection := 'OUT';
249 end if;
250 end;
251
252
253 /*****************************************************
254 Verify that the transaction code is defined in
255 ece_lookup_values with lookup_type=DOCUMENT
256 *****************************************************/
257 begin
258 select lookup_code
259 into xTemp
260 from ece_lookup_values
261 where lookup_type = 'DOCUMENT'
262 and lookup_code = cTransaction_code;
263
264 EXCEPTION
265 when NO_DATA_FOUND then
266 bErrors_found := TRUE;
267 x_msg := 'SEED: Document '||cTransaction_code||' not defined in ece_lookup_values';
268
269 if bInsertErrors then
270 insert into ece_output (run_id,line_id,text)
271 values (iRun_id,
272 ece_output_lines_s.nextval,
273 x_msg);
274 end if;
275 end;
276
277
278 /*****************************************************
279 Verify that the document types are defined in
280 ece_lookup_values with lookup_type=xxx:DOCUMENT_TYPE
281 *****************************************************/
282 begin
283 select lookup_code
284 into xTemp
285 from ece_lookup_values
286 where lookup_type = cTransaction_code||':DOCUMENT_TYPE';
287
288
289 EXCEPTION
290 when NO_DATA_FOUND then
291 bErrors_found := TRUE;
292 if bInsertErrors then
293 x_msg := 'SEED: Document types for '||cTransaction_code||' not defined in ece_lookup_values';
294 insert into ece_output (run_id,line_id,text)
295 values (iRun_id,
296 ece_output_lines_s.nextval,
297 x_msg);
298 end if;
299
300 when TOO_MANY_ROWS then
301 -- This is because the select could return more than one row causing
302 -- an exception. We want to trap that exception because returning
303 -- more than one row is a good thing.
304 null;
305
306 end;
307
308
309 /******************************************************
310 First verify that all of the output levels defined
311 in the ece_interface_tables table have corresponding
312 lookup records in ece_lookup_values.
313 ******************************************************/
314 OPEN c_int_table;
315 LOOP
316 FETCH c_int_table into eit_rec;
317
318 EXIT WHEN c_int_table%NOTFOUND;
319
320 open test_output_level(cTransaction_code,eit_rec.output_level);
321 fetch test_output_level into xTemp;
322
323 begin
324 if test_output_level%ROWCOUNT = 0 then
325 close test_output_level;
326 RAISE no_output_level;
327 end if;
328
329 close test_output_level;
330
331 EXCEPTION
332 WHEN no_output_level then
333 bErrors_found := TRUE;
334 if bInsertErrors then
335 x_msg := 'SEED: Output level not defined in ECE_LOOKUP_VALUES: '||eit_rec.output_level;
336 insert into ece_output (run_id,line_id,text)
337 values (iRun_id,
338 ece_output_lines_s.nextval,
339 x_msg);
340 end if;
341 WHEN others then
342 bErrors_found := TRUE;
343 if bInsertErrors then
344 x_msg := 'SEED: Err in output_level check: '||SQLERRM;
345 insert into ece_output (run_id,line_id,text)
346 values (iRun_id,
347 ece_output_lines_s.nextval,
348 x_msg);
349 end if;
350 end;
351
352 END LOOP; -- c_int_table loop
353
354 /**********************************************************
355 Start verifying the seeded data in ece_interface_tables,
356 ece_interface_columns, and ece_source_data_loc against
357 the database.
358 **********************************************************/
359 OPEN c_inc;
360 LOOP
361 FETCH c_inc into eic_rec;
362
363 IF c_inc%ROWCOUNT = 0 THEN
364 CLOSE c_inc;
365 RAISE no_data_found;
366 END IF;
367
368 EXIT WHEN c_inc%NOTFOUND;
369
370 /**********************************************************
371 The Interface_column_name is only relevant for outbound
372 transactions. For inbound, interface_column_name is only
373 a name used in the pl/sql table and therefor does not have
374 to be tested.
375 **********************************************************/
376 if cDirection = 'OUT' then
377
378 /**********************************************************
379 Check to ensure all of the view columns seeded actually exist
380 in the database
381 **********************************************************/
382 if eic_rec.base_table_name is NOT NULL and
383 eic_rec.base_column_name is NOT NULL then
384
385 OPEN c_atc(eic_rec.base_table_name, eic_rec.base_column_name);
386 FETCH c_atc into x_width, x_datatype;
387
388 BEGIN
389
390 IF c_atc%ROWCOUNT = 0 THEN
391 CLOSE c_atc;
392 RAISE col_not_found;
393 END IF;
394
395 CLOSE c_atc;
396 EXCEPTION
397 WHEN col_not_found THEN
398 bErrors_found := TRUE;
399 if bInsertErrors then
400 x_errtbl := eic_rec.base_table_name;
401 x_errcol := eic_rec.base_column_name;
402 x_msg := 'SEED: Column not found for '||x_errtbl ||'.'||x_errcol;
403 insert into ece_output (run_id,line_id,text)
404 values (iRun_id,
405 ece_output_lines_s.nextval,
406 x_msg);
407 end if;
408 END;
409 end if;
410
411 /**********************************************************
412 Check to ensure all of the interface table columns seeded
413 actually exist in the database
414 **********************************************************/
415 IF eic_rec.interface_table_name is NOT NULL
416 and eic_rec.interface_column_name is NOT NULL then
417
418 OPEN c_atc(eic_rec.interface_table_name, eic_rec.interface_column_name);
419 FETCH c_atc into x_width, x_datatype;
420
421 BEGIN
422
423 IF c_atc%ROWCOUNT = 0 THEN
424 CLOSE c_atc;
425 RAISE col_not_found;
426 END IF;
427
428 CLOSE c_atc;
429
430 if bCheckDatatype then
431 IF x_datatype <> eic_rec.data_type OR eic_rec.data_type IS NULL THEN
432 RAISE wrong_datatype;
433 END IF;
434 end if;
435
436 if bCheckLength then
437 if x_width < eic_rec.width then
438 RAISE bad_width;
439 END IF;
440 end if;
441
445 end if;
442 if eic_rec.key_column_name is null
443 and xCurTable <> eic_rec.interface_table_name then
444 RAISE key_col_not_defined;
446
447 EXCEPTION
448 WHEN col_not_found THEN
449 bErrors_found := TRUE;
450 if bInsertErrors then
451 x_errtbl := eic_rec.interface_table_name;
452 x_errcol := eic_rec.interface_column_name;
453 x_msg := 'SEED: Column not found for '||x_errtbl ||'.'||x_errcol;
454 insert into ece_output (run_id,line_id,text)
455 values (iRun_id,
456 ece_output_lines_s.nextval,
457 x_msg);
458 end if;
459 WHEN wrong_datatype THEN
460 bErrors_found := TRUE;
461 if bInsertErrors then
462 x_errtbl := eic_rec.interface_table_name;
463 x_errcol := eic_rec.interface_column_name;
464 x_errdatatype := eic_rec.data_type;
465
466 x_msg := 'SEED: Wrong data type for '||x_errtbl ||'.'||
467 x_errcol||' Seeded datatype: '||x_errdatatype||' Database datatype: '||
468 x_datatype;
469
470 insert into ece_output (run_id,line_id,text)
471 values (iRun_id,
472 ece_output_lines_s.nextval,
473 x_msg);
474 end if;
475
476 WHEN bad_width THEN
477 bErrors_found := TRUE;
478 if bInsertErrors then
479 x_errtbl := eic_rec.interface_table_name;
480 x_errcol := eic_rec.interface_column_name;
481
482 x_msg := 'SEED: Interface column '||x_errtbl ||'.'||
483 x_errcol||' too short. Seeded: '||eic_rec.width||' Database: '||x_width;
484
485 insert into ece_output (run_id,line_id,text)
486 values (iRun_id,
487 ece_output_lines_s.nextval,
488 x_msg);
489 end if;
490 WHEN key_col_not_defined then
491 bErrors_found := TRUE;
492 if bInsertErrors then
493 xCurTable := eic_rec.interface_table_name;
494 x_errtbl := eic_rec.interface_table_name;
495 x_msg := 'SEED: Key column not defined for '||x_errtbl;
496 insert into ece_output (run_id,line_id,text)
497 values (iRun_id,
498 ece_output_lines_s.nextval,
499 x_msg);
500 end if;
501
502 END;
503 END IF; -- if eic_rec.interface_table_name is not null
504
505 elsif cDirection = 'IN' then
506
507 IF eic_rec.base_table_name is NOT NULL
508 and eic_rec.base_column_name is NOT NULL then
509
510 OPEN c_atc(eic_rec.base_table_name, eic_rec.base_column_name);
511 FETCH c_atc into x_width, x_datatype;
512
513 BEGIN
514 IF NOT c_atc%FOUND THEN
515 CLOSE c_atc;
516 RAISE col_not_found;
517 END IF;
518
519 CLOSE c_atc;
520
521
522 if bCheckDatatype then
523 IF x_datatype <> eic_rec.data_type THEN
524 x_errdatatype := eic_rec.data_type;
525 RAISE wrong_datatype;
526 END IF;
527 end if;
528
529 if bCheckLength then
530 if x_width < eic_rec.width then
531 RAISE bad_width;
532 END IF;
533 end if;
534
535 EXCEPTION
536 WHEN col_not_found THEN
537 bErrors_found := TRUE;
538 if bInsertErrors then
539 x_errtbl := eic_rec.base_table_name;
540 x_errcol := eic_rec.base_column_name;
541 x_msg := 'SEED: Seeded column '||x_errtbl ||'.'||x_errcol||' not found.';
542 insert into ece_output (run_id,line_id,text)
543 values (iRun_id,
544 ece_output_lines_s.nextval,
545 x_msg);
546 end if;
547
548 WHEN wrong_datatype THEN
549 bErrors_found := TRUE;
550 if bInsertErrors then
551 x_errtbl := eic_rec.base_table_name;
552 x_errcol := eic_rec.base_column_name;
553 x_msg := 'SEED: Wrong data type for '||x_errtbl ||'.'||
554 x_errcol||' Seeded datatype: '||x_errdatatype||' Database datatype: '||
555 x_datatype;
556 insert into ece_output (run_id,line_id,text)
557 values (iRun_id,
558 ece_output_lines_s.nextval,
559 x_msg);
560 end if;
561
562 WHEN bad_width THEN
563 bErrors_found := TRUE;
564 if bInsertErrors then
565 x_errtbl := eic_rec.base_table_name;
566 x_errcol := eic_rec.base_column_name;
567
568 x_msg := 'SEED: Interface column '||x_errtbl ||'.'||
569 x_errcol||' too short. Seeded: '||eic_rec.width||' Database: '||x_width;
570
571 insert into ece_output (run_id,line_id,text)
572 values (iRun_id,
573 ece_output_lines_s.nextval,
574 x_msg);
575 end if;
576 END;
577 END IF; -- if ic_rec.base_table_name is NOT NULL
578 end if; -- if cDirection = 'OUT' elsif cDirection = 'IN'
579 END LOOP;
580
581 EXCEPTION
582
583 WHEN others THEN
584 IF c_inc%ISOPEN THEN
585 CLOSE c_inc;
586 END IF;
587 IF c_atc%ISOPEN THEN
588 CLOSE c_atc;
589 END IF;
590 RAISE;
594
591 end SEED_DATA_CHECK;
592
593
595 /**********************************************************
596 This procedure will return the location code, reference1,
597 and reference2 values for the Entity (Customer, Supplier,
598 or Bank) specified.
599
600 p_Entity_type must be one of the following values:
601 CUSTOMER
602 SUPPLIER
603 BANK
604
605 **********************************************************/
606 PROCEDURE TEST_TP_LOOKUP (
607 p_Entity_site_id IN NUMBER,
608 p_Entity_type IN VARCHAR2,
609 p_location_code OUT NOCOPY VARCHAR2,
610 p_reference_ext1 OUT NOCOPY VARCHAR2,
611 p_reference_ext2 OUT NOCOPY VARCHAR2)
612
613 IS
614 l_return_status VARCHAR2(30);
615 l_msg_count NUMBER;
616 l_msg_data VARCHAR2(240);
617 l_info_type VARCHAR2(30);
618
619 BEGIN
620
621 /**********************************************************
622 The org context must be setup before running this procedure!
623
624 p_Entity_id is the address_id if Customer or
625 vendor_site_id if Supplier.
626 **********************************************************/
627 if upper(p_Entity_type) = 'CUSTOMER' then
628 l_info_type := EC_Trading_Partner_PVT.G_CUSTOMER;
629 elsif upper(p_Entity_type) = 'SUPPLIER' then
630 l_info_type := EC_Trading_Partner_PVT.G_SUPPLIER;
631 elsif upper(p_Entity_type) = 'BANK' then
632 l_info_type := EC_Trading_Partner_PVT.G_BANK;
633 end if;
634
635 ec_trading_Partner_pvt.get_tp_location_code(
636 p_api_version_number => 1.0,
637 p_return_status => l_return_status,
638 p_msg_count => l_msg_count,
639 p_msg_data => l_msg_data,
640 p_location_code_ext => p_location_code,
641 p_info_type => l_info_type,
642 p_reference_ext1 => p_reference_ext1,
643 p_reference_ext2 => p_reference_ext2,
644 p_entity_address_id => p_Entity_site_id);
645
646 end TEST_TP_LOOKUP;
647
648
649
650 /*************************************************************
651 This procedure will return the Entity and Entity Site Id for
652 translator and location code specified.
653
654 Entity_type must be one of the following:
655
656 --------Returned values-------
657 Entity Type Entity_id Entity_site_id
658 -------------------------------------------------
659 CUSTOMER CUSTOMER_ID ADDRESS_ID
660 SUPPLIER VENDOR_ID VENDOR_SITE_ID
661 BANK BANK_BRANCH_ID
662 ************************************************************/
663 PROCEDURE TEST_LOCATION_CODE (
664 p_Translator_code IN VARCHAR2,
665 p_Location_code IN VARCHAR2,
666 p_Entity_type IN VARCHAR2,
667 l_entity_id OUT NOCOPY NUMBER,
668 l_entity_address_id OUT NOCOPY NUMBER)
669
670 IS
671
672 l_return_status VARCHAR2(30);
673 l_msg_count NUMBER;
674 l_msg_data VARCHAR2(240);
675 l_info_type VARCHAR2(30);
676
677 BEGIN
678
679 if upper(p_Entity_type) = 'CUSTOMER' then
680 l_info_type := EC_Trading_Partner_PVT.G_CUSTOMER;
681 elsif upper(p_Entity_type) = 'SUPPLIER' then
682 l_info_type := EC_Trading_Partner_PVT.G_SUPPLIER;
683 elsif upper(p_Entity_type) = 'BANK' then
684 l_info_type := EC_Trading_Partner_PVT.G_BANK;
685 end if;
686
687 ec_trading_Partner_pvt.get_tp_address(
688 p_api_version_number => 1.0,
689 p_return_status => l_return_status,
690 p_msg_count => l_msg_count,
691 p_msg_data => l_msg_data,
692 p_translator_code => p_translator_code,
693 p_location_code_ext => p_location_code,
694 p_info_type => l_info_type,
695 p_entity_id => l_entity_id,
696 p_entity_address_id => l_entity_address_id);
697
698
699 end TEST_LOCATION_CODE;
700
701 /*******************************************************************
702 This procedure will read a flatfile and create records in ECE_OUTPUT
703 with the values of the data based on the EDI Gateway data dictionary.
704 *******************************************************************/
705 PROCEDURE verify_flatfile(
706 p_run_id IN NUMBER,
707 p_map_id IN NUMBER,
708 p_Transaction_Type IN VARCHAR2,
709 p_File_path IN VARCHAR2,
710 p_Filename IN VARCHAR2) IS
711
712 xProgress VARCHAR2(80);
713
714 l_rec_number NUMBER;
715 l_cur_pos NUMBER;
716 l_data_length NUMBER;
717
718 l_data_value VARCHAR2(32000) := NULL;
719 l_insert_stmt VARCHAR2(2000);
720 c_current_line VARCHAR2(5000);
721
722 u_file_handle utl_file.file_type;
723 x_num NUMBER;
724 l_initial_position NUMBER := 100;
725 l_start_position NUMBER;
726 l_end_position NUMBER;
727 l_prv_record_num NUMBER := -1;
728
729 /* Bug 1668518
730 ** In the following cursor,added a check on the external_level
731 ** in ece_interface_columns with the ece_external_levels table.
732 */
733 CURSOR c_file_column(
734 p_trans_type VARCHAR2,
735 p_map_id NUMBER,
736 p_record_num NUMBER
737 ) IS
741 eic.record_number,
738 SELECT eic.interface_column_name,
739 eic.base_table_name,
740 eic.base_column_name,
742 eic.position,
743 eic.conversion_sequence conversion_seq,
744 eic.record_layout_code,
745 eic.record_layout_qualifier,
746 eic.conversion_group_id,
747 eic.data_type,
748 eic.width data_length
749 FROM ece_interface_columns eic,
750 ece_interface_tables eit,
751 ece_level_matrices elm,
752 ece_external_levels eel
753 WHERE eit.interface_table_id = eic.interface_table_id AND
754 eit.transaction_type = p_trans_type AND
755 eit.interface_table_id = elm.interface_table_id AND
756 elm.external_level_id = eel.external_level_id AND
757 eel.external_level = eic.external_level AND
758 eel.map_id = p_map_id AND
759 eic.record_number = p_record_num
760 ORDER BY eic.record_number,eic.position;
761
762
763 -- ***********************************************************************
764 -- This procedure read data from an ASCII based on the data dictionary
765 -- ECE_INTERFACE_COLUMNS table. It will then produce a report of
766 -- the value for each of the EDI element for the transaction.
767 -- ***********************************************************************
768 -- Algorithm:
769 --
770 -- Loop
771 -- Read a line from the flat file
772 --
773 -- Find out what is the record number
774 -- Execute cursor to find all elements on this record.
775 -- Report the value of each of the element
776 --
777 -- End Loop
778 -- ***********************************************************************
779
780 BEGIN
781 xProgress := 'RDATA-10-1000';
782 u_file_handle := utl_file.fopen(p_File_path, p_Filename, 'r',5000);
783
784 LOOP
785 xProgress := 'RDATA-10-1010';
786 x_num := x_num + 1;
787
788 xProgress := 'RDATA-10-1020';
789 if x_num > 1000000000
790 then
791 -- go into a inifinate loop, exit immediately
792 xProgress := 'RDATA-10-1030';
793 EXIT;
794 end if;
795
796 xProgress := 'RDATA-10-1040';
797 utl_file.get_line(u_file_handle, c_current_line);
798
799 xProgress := 'RDATA-10-1050';
800 l_rec_number := TO_NUMBER(SUBSTRB(c_current_line,
801 ece_flatfile_pvt.g_record_num_start,
802 ece_flatfile_pvt.g_record_num_length));
803
804 -- ***************************************
805 -- cursor position should be at the
806 -- end of common key
807 -- ***************************************
808 xProgress := 'RDATA-10-1060';
809 l_cur_pos := ece_flatfile_pvt.g_common_key_length;
810
811 -- ***************************************
812 -- Execute cursor to find all elements for
813 -- this record
814 -- ***************************************
815 xProgress := 'RDATA-10-1070';
816 FOR interface_data_rec IN c_file_column(
817 p_trans_type => p_transaction_type,
818 p_map_id => p_map_id,
819 p_record_num => l_rec_number) LOOP
820 xProgress := 'RDATA-10-1080';
821 l_data_length := interface_data_rec.data_length;
822 xProgress := 'RDATA-10-1090';
823 l_data_value := RTRIM(SUBSTRB(c_current_line, l_cur_pos + 1, l_data_length));
824
825 -- *******************************
826 -- WARNING:
827 -- Since the data is from a file
828 -- NULL data is padded with BLANKS.
829 -- Remove them
830 -- *******************************
831 xProgress := 'RDATA-10-1100';
832 IF REPLACE(l_data_value, ' ') IS NULL THEN
833 xProgress := 'RDATA-10-1110';
834 l_data_value := NULL;
835 END IF;
836
837 xProgress := 'RDATA-10-1120';
838 l_cur_pos := l_cur_pos + l_data_length;
839
840 /* DERIVE the position for the data element */
841 if interface_data_rec.Record_number <> l_prv_record_num
842 then
843 -- bug # 927944/948754 fix
844 l_end_position := l_initial_position;
845 end if;
846 l_start_position := l_end_position + 1;
847 l_end_position := l_end_position + nvl(interface_data_rec.data_length ,0);
848
849 xProgress := 'RDATA-10-1130';
850 l_insert_stmt :=
851 LPAD(interface_data_rec.Record_number,4,'0') ||' '||
852 LPAD(interface_data_rec.Position,4) ||' '||
853 LPAD(interface_data_rec.data_length,4) ||' '||
857 EC_DEBUG.PL(3, 'l_insert_stmt: ', l_insert_stmt);
854 LPAD(l_start_position,5,' ') ||' '|| -- Bug # 948754 fix
855 RPAD(interface_data_rec.interface_column_name,45,' ') ||' '||
856 l_data_value;
858
859 xProgress := 'RDATA-10-1140';
860 INSERT into ece_output( run_id, line_id, text)
861 VALUES(p_run_id, ece_output_runs_s.nextval, l_insert_stmt);
862
863 xProgress := 'RDATA-10-1150';
864 l_insert_stmt := NULL;
865 l_prv_record_num := interface_data_rec.Record_number;
866 END LOOP;
867 END LOOP;
868
869 -- finish utl_file.get_line
870 EXCEPTION
871 -- this exception handler is to handle end of file
872 WHEN NO_DATA_FOUND THEN
873 NULL;
874 WHEN OTHERS THEN
875 FND_MESSAGE.SET_NAME('EC','ECE_PROGRAM_ERROR');
876 FND_MESSAGE.SET_TOKEN('PROGRESS_LEVEL',xProgress);
877 APP_EXCEPTION.RAISE_EXCEPTION;
878
879 END verify_flatfile;
880
881
882 PROCEDURE set_installation(
883 p_transaction IN VARCHAR2,
884 p_short_name IN VARCHAR2,
885 p_status IN VARCHAR2)
886 IS
887
888 /**************************************************************
889 This procedure will enable or disable an EDI transaction
890 so it will/will not be visible from all forms within EDI.
891 It does not delete any records, it simply sets flags.
892 Parameters:
893 p_transaction - The EDI Transaction Code (i.e. POI,INO)
894 p_short_name - The Short Name of the Concurrent program
895 defined for the transaction.
896 p_status - Status you want to set. Valid values
897 are ENABLE or DISABLE
898 **************************************************************/
899
900 /* Application Id for EDI Gateway is 175 */
901 p_application_id NUMBER := 175;
902 xProgress VARCHAR2(20);
903 c_installed_flag VARCHAR2(1);
904 temp_enabled_flag VARCHAR2(1);
905 not_in_main_flag VARCHAR2(1) := 'N';
906 not_in_upg_flag VARCHAR2(1) := 'N';
907 new_status VARCHAR2(1);
908
909 BEGIN
910 xProgress := 'UTILB-10-1000';
911 BEGIN
912 /* Make sure status passed in is valid */
913 xProgress := 'UTILB-10-1010';
914 IF UPPER(p_status) = 'ENABLE' THEN
915 xProgress := 'UTILB-10-1020';
916 new_status := 'Y';
917 ELSIF UPPER(p_status) = 'DISABLE' THEN
918 xProgress := 'UTILB-10-1030';
919 new_status := 'N';
920 ELSE
921 xProgress := 'UTILB-10-1040';
922 -- dbms_output.put_line('ERR: Bad Status passed in. Must be ENABLE or DISABLE');
923 app_exception.raise_exception;
924 END IF;
925
926 /* Make sure transaction type passed in exists */
927 xProgress := 'UTILB-10-1050';
928 SELECT installed_flag INTO c_installed_flag
929 FROM ece_interface_tables
930 WHERE transaction_Type = p_transaction AND
931 output_level = 1 AND
932 ROWNUM = 1;
933
934 EXCEPTION
935 WHEN NO_DATA_FOUND THEN
936 -- dbms_output.put_line('ERR: Transaction Code not found');
937 -- Oops, it looks like the transaction is not found in ECE_INTERFACE_TABLES.
938 not_in_main_flag := 'Y';
939
940 WHEN OTHERS THEN
941 -- dbms_output.put_line(xProgress);
942 -- dbms_output.put_line('ERR: Transaction Check: '||SQLERRM);
943 app_exception.raise_exception;
944 END;
945
946 BEGIN
947 BEGIN
948 IF not_in_main_flag = 'Y' THEN
949 /* Make sure transaction type passed in exists */
950 xProgress := 'UTILB-10-1050';
951 SELECT installed_flag INTO c_installed_flag
952 FROM ece_interface_tbls_upg
953 WHERE transaction_Type = p_transaction AND
954 output_level = 1 AND
955 ROWNUM = 1;
956 END IF;
957
958 EXCEPTION
959 WHEN NO_DATA_FOUND THEN
960 app_exception.raise_exception;
961 END;
962
963 /* Make sure concurrent program exists */
964 xProgress := 'UTILB-10-1060';
965 SELECT enabled_flag INTO temp_enabled_flag
966 FROM fnd_concurrent_programs
967 WHERE application_id = p_application_id AND
968 concurrent_program_name = UPPER(p_short_name);
969
970 EXCEPTION
971 WHEN NO_DATA_FOUND THEN
972 -- dbms_output.put_line(xProgress);
973 -- dbms_output.put_line('ERR: Concurrent Program does not exist');
974 IF not_in_main_flag = 'Y' THEN
975 app_exception.raise_exception;
976 ELSE
977 not_in_upg_flag := 'Y';
978 END IF;
979
980 WHEN OTHERS THEN
981 -- dbms_output.put_line(xProgress);
982 -- dbms_output.put_line('ERR: Conc Program Check: '||SQLERRM);
983 app_exception.raise_exception;
984 END;
985
986 BEGIN
987 /* Now go and update the EDI tables */
988 xProgress := 'UTILB-10-1070';
989 IF not_in_upg_flag = 'N' THEN
990 UPDATE ece_interface_tbls_upg
991 SET installed_flag = new_status
992 WHERE transaction_type = p_transaction;
993 END IF;
994
995 IF not_in_main_flag = 'N' THEN
996 UPDATE ece_interface_tables
997 SET installed_flag = new_status
998 WHERE transaction_type = p_transaction;
999 END IF;
1000
1001 xProgress := 'UTILB-10-1080';
1002 UPDATE ece_lookup_values
1003 SET enabled_flag = new_status
1004 WHERE lookup_type = 'DOCUMENT' AND
1005 lookup_code = p_transaction;
1009 -- dbms_output.put_line(xProgress);
1006
1007 EXCEPTION
1008 WHEN OTHERS THEN
1010 -- dbms_output.put_line('ERR: Error updating EC tables');
1011 app_exception.raise_exception;
1012
1013 END;
1014
1015 xProgress := 'UTILB-10-1100';
1016 fnd_program.enable_program(p_short_name,'EC',new_status);
1017
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 -- dbms_output.put_line('ERR: '||xProgress);
1021 app_exception.raise_exception;
1022
1023 END set_installation;
1024
1025 END ECE_UTILITIES;
1026
[Home] [Help]