[Home] [Help]
PACKAGE BODY: APPS.ECE_ADVO_ADVICE_PKG
Source
1 PACKAGE BODY ece_advo_advice_pkg AS
2 -- $Header: ECADVOB.pls 120.2.12000000.3 2007/03/09 14:37:48 cpeixoto ship $
3
4 /*===========================================================================
5 PROCEDURE NAME: Extract_ADVO_Outbound
6 PURPOSE: This procedure initiates the concurrent process to
7 extract the eligible transactions.
8 ===========================================================================*/
9
10 PROCEDURE Extract_ADVO_Outbound(errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY VARCHAR2,
12 cOutput_Path IN VARCHAR2,
13 cOutput_Filename IN VARCHAR2,
14 p_TP_Group IN VARCHAR2,
15 p_TP IN VARCHAR2,
16 p_Response_to_doc IN VARCHAR2,
17 cDate_From IN VARCHAR2,
18 cDate_To IN VARCHAR2,
19 p_ext_ref1 IN VARCHAR2,
20 p_ext_ref2 IN VARCHAR2,
21 p_ext_ref3 IN VARCHAR2,
22 p_ext_ref4 IN VARCHAR2,
23 p_ext_ref5 IN VARCHAR2,
24 p_ext_ref6 IN VARCHAR2,
25 v_debug_mode IN NUMBER DEFAULT 0) IS
26
27 xProgress VARCHAR2(80);
28 p_communication_method VARCHAR2(120) := 'EDI';
29 p_transaction_type VARCHAR2(120) := 'ADVO';
30 p_document_type VARCHAR2(120) := 'ADV';
31 l_line_text VARCHAR2(2000);
32 uFile_type utl_file.file_type;
33 p_Date_From DATE := TO_DATE(cDate_From,'YYYY/MM/DD HH24:MI:SS');
34 p_Date_To DATE := TO_DATE(cDate_To,'YYYY/MM/DD HH24:MI:SS') + 1;
35 p_output_width INTEGER := 4000;
36 p_run_id NUMBER;
37 p_header_interface VARCHAR2(120) := 'ECE_ADVO_HEADERS_INTERFACE';
38 p_line_interface VARCHAR2(120) := 'ECE_ADVO_DETAILS_INTERFACE';
39 p_transaction_date DATE := SYSDATE;
40 cEnabled VARCHAR2(1) := 'Y';
41 ece_transaction_disabled EXCEPTION;
42
43 CURSOR c_output IS
44 SELECT text
45 FROM ece_output
46 WHERE run_id = p_run_id
47 ORDER BY line_id;
48
49 BEGIN
50
51 ec_debug.enable_debug ( v_debug_mode );
52 ec_debug.push ( 'ECE_ADVO_ADVICE_PKG.Extract_ADVO_Outbound' );
53 ec_debug.pl ( 3, 'cOutput_Path: ',cOutput_Path );
54 ec_debug.pl ( 3, 'cOutput_Filename: ',cOutput_Filename );
55 ec_debug.pl ( 3, 'p_TP_Group: ',p_TP_Group );
56 ec_debug.pl ( 3, 'p_TP: ',p_TP );
57 ec_debug.pl ( 3, 'p_Response_to_doc: ',p_Response_to_doc );
58 ec_debug.pl ( 3, 'cDate_From: ',cDate_From );
59 ec_debug.pl ( 3, 'cDate_To: ',cDate_To );
60 ec_debug.pl ( 3, 'p_ext_ref1: ',p_ext_ref1 );
61 ec_debug.pl ( 3, 'p_ext_ref2: ',p_ext_ref2 );
62 ec_debug.pl ( 3, 'p_ext_ref3: ',p_ext_ref3 );
63 ec_debug.pl ( 3, 'p_ext_ref4: ',p_ext_ref4 );
64 ec_debug.pl ( 3, 'p_ext_ref5: ',p_ext_ref5 );
65 ec_debug.pl ( 3, 'p_ext_ref6: ',p_ext_ref6 );
66 ec_debug.pl ( 3, 'v_debug_mode: ',v_debug_mode );
67
68 /* Check to see if the transaction is enabled. If not, abort */
69 xProgress := 'ADVO-10-1001';
70 fnd_profile.get('ECE_' || p_Transaction_Type || '_ENABLED',cEnabled);
71
72 xProgress := 'ADVO-10-1002';
73 IF cEnabled = 'N' THEN
74 xProgress := 'ADVO-10-1003';
75 RAISE ece_transaction_disabled;
76 END IF;
77
78 xProgress := 'ADVO-10-1005';
79 BEGIN
80 SELECT ece_output_runs_s.NEXTVAL
81 INTO p_run_id
82 FROM sys.dual;
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 ec_debug.pl ( 0,
86 'EC',
87 'ECE_GET_NEXT_SEQ_FAILED',
88 'PROGRESS_LEVEL',
89 xProgress,
90 'SEQ',
91 'ECE_OUTPUT_RUNS_S' );
92 END;
93 ec_debug.pl ( 3, 'p_run_id: ',p_run_id );
94
95 xProgress := 'ADVO-10-1010';
96 ec_debug.pl ( 0, 'EC', 'ECE_ADVO_START', NULL );
97
98 xProgress := 'ADVO-10-1020';
99 ec_debug.pl ( 0, 'EC', 'ECE_RUN_ID', 'RUN_ID', p_run_id );
100
101 xProgress := 'ADVO-10-1030';
102 ECE_ADVO_ADVICE_PKG.EXTRACT_FROM_BASE_APPS ( p_communication_method,
103 p_transaction_type,
104 p_output_width,
105 p_transaction_date,
106 p_run_id,
107 p_header_interface,
108 p_line_interface,
109 p_TP_Group,
110 p_TP,
111 p_Response_to_doc,
112 p_Date_From,
113 p_Date_To,
114 p_ext_ref1,
115 p_ext_ref2,
116 p_ext_ref3,
117 p_ext_ref4,
118 p_ext_ref5,
119 p_ext_ref6 );
120
121 xProgress := 'ADVO-10-1040';
122
123 ece_advo_advice_pkg.Put_Data_To_Output_Table ( p_communication_method,
124 p_transaction_type,
125 p_output_width,
126 p_run_id,
127 p_header_interface,
128 p_line_interface );
129
130 xProgress := 'ADVO-10-1050';
131
132 /*
133 **
134 ** Open the cursor to select the actual file output from ece_output.
135 **
136 */
137
138 xProgress := 'ADVO-10-1060';
139 OPEN c_output;
140 LOOP
141 FETCH c_output
142 INTO l_line_text;
143 if (c_output%ROWCOUNT > 0) then
144 if (NOT utl_file.is_open(uFile_type)) then
145 uFile_type := utl_file.fopen ( cOutput_Path,
146 cOutput_Filename,
147 'W' );
148 end if;
149 end if;
150
151 EXIT WHEN c_output%NOTFOUND;
152
153 ec_debug.pl ( 3, 'l_line_text: ',l_line_text );
154
155 /*
156 **
157 ** Write the data from ece_output to the output file.
158 **
159 */
160
161 xProgress := 'ADVO-10-1070';
162 utl_file.put_line ( uFile_type,
163 l_line_text );
164 END LOOP;
165
166 CLOSE c_output;
167
168 /*
169 **
170 ** Close the output file.
171 **
172 */
173
174 xProgress := 'ADVO-10-1080';
175 if (utl_file.is_open( uFile_type)) then
176 utl_file.fclose ( uFile_type );
177 end if;
178
179 /*
180 **
181 ** Assume everything went ok so delete the records from ece_output.
182 **
183 */
184
185 xProgress := 'ADVO-10-1090';
186 ec_debug.pl ( 0, 'EC', 'ECE_ADVO_COMPLETE', NULL );
187
188 xProgress := 'ADVO-10-1100';
189 DELETE
190 FROM ece_output
191 WHERE run_id = p_run_id;
192
193 IF SQL%NOTFOUND
194 THEN
195 ec_debug.pl ( 0,
196 'EC',
197 'ECE_NO_ROW_DELETED',
198 'PROGRESS_LEVEL',
199 xProgress,
200 'TABLE_NAME',
201 'ECE_OUTPUT' );
202 END IF;
203
204 --- Everything is successful. Commit the Changes.
205 commit;
206
207 IF ec_mapping_utils.ec_get_trans_upgrade_status(p_transaction_type) = 'U' THEN
208 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
209 retcode := 1;
210 END IF;
211
212 ec_debug.pop ( 'ece_advo_advice_pkg.Extract_ADVO_Outbound' );
213 ec_debug.disable_debug;
214
215 EXCEPTION
216 WHEN ece_transaction_disabled THEN
217 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED','TRANSACTION',p_Transaction_type);
218 retcode := 1;
219 ec_debug.disable_debug;
220 ROLLBACK;
221
222 WHEN utl_file.write_error THEN
223
224 ec_debug.pl ( 0,
225 'EC',
226 'ECE_UTL_WRITE_ERROR',
227 NULL );
228
229 ec_debug.pl ( 0,
230 'EC',
231 'ECE_ERROR_MESSAGE',
232 'ERROR_MESSAGE',
233 SQLERRM );
234
235 retcode := 2;
236 ec_debug.disable_debug;
237 ROLLBACK;
238 RAISE;
239
240 WHEN utl_file.invalid_path THEN
241
242 ec_debug.pl ( 0,
243 'EC',
244 'ECE_UTIL_INVALID_PATH',
245 NULL );
246
247 ec_debug.pl ( 0,
248 'EC',
249 'ECE_ERROR_MESSAGE',
250 'ERROR_MESSAGE',
251 SQLERRM );
252
253 retcode := 2;
254 ec_debug.disable_debug;
255 ROLLBACK;
256 RAISE;
257
258 WHEN utl_file.invalid_operation THEN
259
260 ec_debug.pl ( 0,
261 'EC',
262 'ECE_UTIL_INVALID_OPERATION',
263 NULL );
264
265 ec_debug.pl ( 0,
266 'EC',
267 'ECE_ERROR_MESSAGE',
268 'ERROR_MESSAGE',
269 SQLERRM );
270
271 retcode := 2;
272 ec_debug.disable_debug;
273 ROLLBACK;
274 RAISE;
275
276 WHEN OTHERS THEN
277
278 ec_debug.pl ( 0,
279 'EC',
280 'ECE_PROGRAM_ERROR',
281 'PROGRESS_LEVEL',
282 xProgress );
283
284 ec_debug.pl ( 0,
285 'EC',
286 'ECE_ERROR_MESSAGE',
287 'ERROR_MESSAGE',
288 SQLERRM );
289
290 retcode := 2;
291 ec_debug.disable_debug;
292 ROLLBACK;
293 RAISE;
294
295 END Extract_ADVO_Outbound;
296
297 /* --------------------------------------------------------------------------
298 REM PROCEDURE Extract_From_Base_Apps
299 REM This procedure has the following functionalities:
300 REM 1. Build SQL statement dynamically to extract data from
301 REM Base Application Tables.
302 REM 2. Execute the dynamic SQL statement.
303 REM 3. Assign data into 2-dim PL/SQL table
304 REM 4. Pass data to the code conversion mechanism
305 REM 5. Populate the Interface tables with the extracted data.
306 REM --------------------------------------------------------------------------
307 */
308
309 PROCEDURE Extract_From_Base_Apps ( cCommunication_Method IN VARCHAR2,
310 cTransaction_Type IN VARCHAR2,
311 iOutput_width IN INTEGER,
312 dTransaction_date IN DATE,
313 iRun_id IN INTEGER,
314 cHeader_Interface IN VARCHAR2,
315 cLine_Interface IN VARCHAR2,
316 p_TP_Group IN VARCHAR2,
317 p_TP IN VARCHAR2,
318 p_Response_to_doc IN VARCHAR2,
319 p_Date_From IN DATE,
320 p_Date_To IN DATE,
321 p_ext_ref1 IN VARCHAR2,
322 p_ext_ref2 IN VARCHAR2,
323 p_ext_ref3 IN VARCHAR2,
324 p_ext_ref4 IN VARCHAR2,
325 p_ext_ref5 IN VARCHAR2,
326 p_ext_ref6 IN VARCHAR2 )
327 IS
328
329 /*
330 **
331 ** Variable definitions. 'Interface_tbl_type' is a PL/SQL table typedef
332 ** with the following structure:
333 **
334 ** base_table_name VARCHAR2(50)
335 ** base_column_name VARCHAR2(50)
336 ** interface_table_name VARCHAR2(50)
337 ** interface_column_name VARCHAR2(50)
338 ** Record_num NUMBER
339 ** Position NUMBER
340 ** data_type VARCHAR2(50)
341 ** data_length NUMBER
342 ** value VARCHAR2(400)
343 ** layout_code VARCHAR2(2)
344 ** record_qualifier VARCHAR2(3)
345 ** interface_column_id NUMBER
346 ** conversion_seq NUMBER
347 ** xref_category_id NUMBER
348 ** conversion_group_id NUMBER
349 ** xref_key1_source_column VARCHAR2(50)
350 ** xref_key2_source_column VARCHAR2(50)
351 ** xref_key3_source_column VARCHAR2(50)
352 ** xref_key4_source_column VARCHAR2(50)
353 ** xref_key5_source_column VARCHAR2(50)
354 ** ext_val1 VARCHAR2(80)
355 ** ext_val2 VARCHAR2(80)
356 ** ext_val3 VARCHAR2(80)
357 ** ext_val4 VARCHAR2(80)
358 ** ext_val5 VARCHAR2(80)
359 **
360 */
364 cOutput_path VARCHAR2(120);
361
362 xProgress VARCHAR2(30);
363 v_LevelProcessed VARCHAR2(40);
365
366 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
367 l_line_tbl ece_flatfile_pvt.Interface_tbl_type;
368 l_key_tbl ece_flatfile_pvt.Interface_tbl_type;
369
370 Header_sel_c INTEGER;
371 Line_sel_c INTEGER;
372
373 cHeader_select VARCHAR2(32000);
374 cLine_select VARCHAR2(32000);
375
376 cHeader_from VARCHAR2(32000);
377 cLine_from VARCHAR2(32000);
378
379 cHeader_where VARCHAR2(32000);
380 cLine_where VARCHAR2(32000);
381
382 iHeader_count NUMBER := 0;
383 iLine_count NUMBER := 0;
384 iKey_count NUMBER := 0;
385
386 l_header_fkey NUMBER;
387 l_line_fkey NUMBER;
388
389 l_header_row_processed INTEGER;
390 l_line_row_processed INTEGER;
391
392 l_return_status VARCHAR2(10);
393 l_msg_count NUMBER;
394 l_msg_data VARCHAR2(255);
395
396 n_advice_header_id_pos NUMBER;
397 n_trx_date_pos NUMBER;
398 n_runid_pos NUMBER;
399
400 d_dummy_date DATE;
401
402 BEGIN
403
404 /*
405 **
406 ** Debug statements for the parameter values.
407 **
408 */
409
410 ec_debug.push ( 'ece_advo_advice_pkg.Extract_From_Base_Apps' );
411 ec_debug.pl ( 3, 'cCommunication_Method: ', cCommunication_Method );
412 ec_debug.pl ( 3, 'cTransaction_Type: ',cTransaction_Type );
413 ec_debug.pl ( 3, 'iOutput_width: ',iOutput_width );
414 ec_debug.pl ( 3, 'dTransaction_date: ',dTransaction_date );
415 ec_debug.pl ( 3, 'iRun_id: ',iRun_id );
416 ec_debug.pl ( 3, 'cHeader_Interface: ',cHeader_Interface );
417 ec_debug.pl ( 3, 'cLine_Interface: ',cLine_Interface );
418 ec_debug.pl ( 3, 'p_TP_Group: ',p_TP_Group );
419 ec_debug.pl ( 3, 'p_Response_to_doc: ',p_Response_to_doc );
420 ec_debug.pl ( 3, 'p_Date_From: ',p_Date_From );
421 ec_debug.pl ( 3, 'p_Date_To: ',p_Date_To );
422 ec_debug.pl ( 3, 'p_ext_ref1: ',p_ext_ref1 );
423 ec_debug.pl ( 3, 'p_ext_ref2: ',p_ext_ref2 );
424 ec_debug.pl ( 3, 'p_ext_ref3: ',p_ext_ref3 );
425 ec_debug.pl ( 3, 'p_ext_ref4: ',p_ext_ref4 );
426 ec_debug.pl ( 3, 'p_ext_ref5: ',p_ext_ref5 );
427 ec_debug.pl ( 3, 'p_ext_ref6: ',p_ext_ref6 );
428
429 /*
430 **
431 ** The "Init_Table" procedure will build the internal PL/SQL
432 ** table for each level as well as the internal PL/SQL "Key"
433 ** table used by the Cross Reference engine. The "Key" table
434 ** is a concatenation of ALL column values used in this
435 ** transaction, regardless of level.
436 **
437 */
438
439 xProgress := 'ADVOB-10-1000';
440 ece_flatfile_pvt.INIT_TABLE( cTransaction_Type,
441 cHeader_Interface,
442 NULL,
443 FALSE,
444 l_header_tbl,
445 l_key_tbl );
446
447 xProgress := 'ADVOB-10-1010';
448 l_key_tbl := l_header_tbl;
449
450 xProgress := 'ADVOB-10-1030';
451 ece_flatfile_pvt.INIT_TABLE( cTransaction_Type,
452 cLine_Interface,
453 NULL,
454 TRUE,
455 l_Line_tbl,
456 l_key_tbl );
457
458 /*
459 **
460 ** The 'select_clause' procedure will build the SELECT, FROM and WHERE
461 ** clauses in preparation for the dynamic SQL call using the EDI data
462 ** dictionary for the build. Any necessary customizations to these
463 ** clauses need to be made *after* the clause is built, but *before*
464 ** the SQL call.
465 **
466 */
467
468 xProgress := 'ADVOB-10-1040';
469 ece_extract_utils_pub.select_clause ( cTransaction_Type,
470 cCommunication_Method,
471 cHeader_Interface,
472 l_header_tbl,
473 cHeader_select,
474 cHeader_from,
475 cHeader_where );
476
477
478 xProgress := 'ADVOB-10-1050';
479 ece_extract_utils_pub.select_clause ( cTransaction_Type,
480 cCommunication_Method,
481 cLine_Interface,
482 l_line_tbl,
483 cLine_select,
484 cLine_from,
485 cLine_where );
486
487
488 /*
489 **
490 ** Customize the WHERE clauses. The WHERE clause for the Header
491 ** level is conditional, depending on the values of the parameters
492 ** passed to this procedure.
493 **
494 */
495
496 cHeader_where := cHeader_where ||
497 ' 1 = 1 ';
498
499
500 xProgress := 'ADVOB-10-1060';
501 IF p_TP_Group IS NOT NULL
502 THEN
503 cHeader_where := cHeader_where ||
504 ' AND ' ||
508
505 'ece_advo_headers_v.tp_group_code = ' ||
506 ':l_TP_Group';
507 END IF;
509 xProgress := 'ADVOB-10-1070';
510 IF p_TP IS NOT NULL
511 THEN
512 cHeader_where := cHeader_where ||
513 ' AND ' ||
514 'ece_advo_headers_v.tp_location_code_ext = ' ||
515 ':l_TP';
516 END IF;
517
518 xProgress := 'ADVOB-10-1080';
519 IF p_Response_to_doc IS NOT NULL
520 THEN
521 cHeader_where := cHeader_where ||
522 ' AND ' ||
523 'ece_advo_headers_v.related_document_id = ' ||
524 ':l_Response_to_doc';
525 END IF;
526
527 xProgress := 'ADVOB-10-1090';
528 IF p_Date_From IS NOT NULL
529 THEN
530 cHeader_where := cHeader_where ||
531 ' AND ' ||
532 'ece_advo_headers_v.transaction_date >= ' ||
533 ':l_Date_From';
534 END IF;
535
536 xProgress := 'ADVOB-10-1100';
537 IF p_Date_To IS NOT NULL
538 THEN
539 cHeader_where := cHeader_where ||
540 ' AND ' ||
541 'ece_advo_headers_v.transaction_date <= ' ||
542 ':l_Date_To';
543 END IF;
544
545 xProgress := 'ADVOB-10-1110';
546 IF p_ext_ref1 IS NOT NULL
547 THEN
548 cHeader_where := cHeader_where ||
549 ' AND ' ||
550 'ece_advo_headers_v.external_reference1 = ' ||
551 ':l_ext_ref1';
552
553 END IF;
554
555 xProgress := 'ADVOB-10-1120';
556 IF p_ext_ref2 IS NOT NULL
557 THEN
558 cHeader_where := cHeader_where ||
559 ' AND ' ||
560 'ece_advo_headers_v.external_reference2 = ' ||
561 ':l_ext_ref2';
562
563 END IF;
564
565 xProgress := 'ADVOB-10-1130';
566 IF p_ext_ref3 IS NOT NULL
567 THEN
568 cHeader_where := cHeader_where ||
569 ' AND ' ||
570 'ece_advo_headers_v.external_reference3 = ' ||
571 ':l_ext_ref3';
572
573 END IF;
574
575 xProgress := 'ADVOB-10-1140';
576 IF p_ext_ref4 IS NOT NULL
577 THEN
578 cHeader_where := cHeader_where ||
579 ' AND ' ||
580 'ece_advo_headers_v.external_reference4 = ' ||
581 ':l_ext_ref4';
582
583 END IF;
584
585 xProgress := 'ADVOB-10-1150';
586 IF p_ext_ref5 IS NOT NULL
587 THEN
588 cHeader_where := cHeader_where ||
589 ' AND ' ||
590 'ece_advo_headers_v.external_reference5 = ' ||
591 ':l_ext_ref5';
592
593 END IF;
594
595 xProgress := 'ADVOB-10-1160';
596 IF p_ext_ref6 IS NOT NULL
597 THEN
598 cHeader_where := cHeader_where ||
599 ' AND ' ||
600 'ece_advo_headers_v.external_reference6 = ' ||
601 ':l_ext_ref6';
602
603 END IF;
604
605 xProgress := 'ADVOB-10-1170';
606 cLine_where := cLine_where ||
607 'ADVICE_HEADER_ID = :l_advice_header_id';
608
609 /*
610 **
611 ** Build the complete SELECT statement for each level.
612 **
613 */
614
615 xProgress := 'ADVOB-10-1180';
616 cHeader_select := cHeader_select ||
617 cHeader_from ||
618 cHeader_where;
619 ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
620
621 xProgress := 'ADVOB-10-1190';
622 cLine_select := cLine_select ||
623 cLine_from ||
624 cLine_where;
625 ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
626
627 /*
628 **
629 ** Open a cursor for each of the SELECT calls. This tells the
630 ** database to reserve space for the data returned by the SELECT
631 ** statement.
632 **
633 */
634
635 xProgress := 'ADVOB-10-1200';
636 Header_sel_c := dbms_sql.open_cursor;
637
638 xProgress := 'ADVOB-10-1210';
639 Line_sel_c := dbms_sql.open_cursor;
640
641 /*
642 **
643 ** Parse each SELECT statement so the database understands the
644 ** command. If the parse fails, trap and print the point of
645 ** failure and exit the procedure with an error.
646 **
647 */
648
649 xProgress := 'ADVOB-10-1220';
650 BEGIN
651 dbms_sql.parse ( Header_sel_c,
652 cHeader_select,
653 dbms_sql.native );
657 cHeader_select );
654 EXCEPTION
655 WHEN OTHERS THEN
656 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
658 app_exception.raise_exception;
659 END;
660
661 xProgress := 'ADVOB-10-1230';
662 BEGIN
663 dbms_sql.parse ( Line_sel_c,
664 cLine_select,
665 dbms_sql.native );
666 EXCEPTION
667 WHEN OTHERS THEN
668 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
669 cLine_select );
670 app_exception.raise_exception;
671 END;
672
673 /*
674 **
675 ** Initialize counter variables.
676 **
677 */
678
679 xProgress := 'ADVOB-10-1240';
680 iHeader_count := l_header_tbl.count;
681 ec_debug.pl ( 3, 'iHeader_count: ',iHeader_count );
682
683 xProgress := 'ADVOB-10-1250';
684 iLine_count := l_line_tbl.count;
685 ec_debug.pl ( 3, 'iLine_count: ',iLine_count );
686
687 /*
688 **
689 ** Define the data type for every column in each SELECT statement
690 ** so the database understands how to populate it.
691 **
692 */
693
694 xProgress := 'ADVOB-10-1260';
695 FOR k IN 1..iHeader_count
696 LOOP
697 dbms_sql.define_column ( Header_sel_c,
698 k,
699 cHeader_select,
700 ece_extract_utils_PUB.G_MaxColWidth );
701 END LOOP;
702
703 xProgress := 'ADVOB-10-1270';
704 FOR k IN 1..iLine_count
705 LOOP
706 dbms_sql.define_column ( Line_sel_c,
707 k,
708 cLine_select,
709 ece_extract_utils_PUB.G_MaxColWidth );
710 END LOOP;
711
712 /*
713 **
714 ** Find the positions of the Transaction_Date and the
715 ** Advice_Header_ID in the PL/SQL table.
716 **
717 */
718
719 xProgress := 'ADVOB-10-1280';
720 ece_extract_utils_pub.Find_pos ( l_header_tbl,
721 ece_extract_utils_pub.G_Transaction_date,
722 n_trx_date_pos );
723 ec_debug.pl(3, 'n_trx_date_pos: ',n_trx_date_pos );
724
725 xProgress := 'ADVOB-10-1290';
726 ece_extract_utils_pub.Find_pos ( l_header_tbl,
727 'ADVICE_HEADER_ID',
728 n_advice_header_id_pos );
729 ec_debug.pl(3, 'n_advice_header_id_pos: ',n_advice_header_id_pos );
730
731
732 xProgress := 'ADVOB-10-1291';
733 IF p_TP_Group IS NOT NULL
734 THEN
735 dbms_sql.bind_variable ( Header_sel_c,
736 'l_TP_Group',
737 p_TP_Group );
738 END IF;
739
740 xProgress := 'ADVOB-10-1292';
741 IF p_TP IS NOT NULL
742 THEN
743 dbms_sql.bind_variable ( Header_sel_c,
744 'l_TP',
745 p_TP );
746 END IF;
747
748 xProgress := 'ADVOB-10-1293';
749 IF p_Response_to_doc IS NOT NULL
750 THEN
751 dbms_sql.bind_variable ( Header_sel_c,
752 'l_Response_to_doc',
753 p_Response_to_doc);
754 END IF;
755
756 xProgress := 'ADVOB-10-1294';
757 IF p_Date_From IS NOT NULL
758 THEN
759 dbms_sql.bind_variable ( Header_sel_c,
760 'l_Date_From',
761 p_Date_From );
762 END IF;
763
764 xProgress := 'ADVOB-10-1295';
765 IF p_Date_To IS NOT NULL
766 THEN
767 dbms_sql.bind_variable ( Header_sel_c,
768 'l_Date_To',
769 p_Date_To );
770 END IF;
771
772 xProgress := 'ADVOB-10-1296';
773 IF p_ext_ref1 IS NOT NULL
774 THEN
775 dbms_sql.bind_variable ( Header_sel_c,
776 'l_ext_ref1',
777 p_ext_ref1 );
778 END IF;
779
780 xProgress := 'ADVOB-10-1297';
781 IF p_ext_ref2 IS NOT NULL
782 THEN
783 dbms_sql.bind_variable ( Header_sel_c,
784 'l_ext_ref2',
785 p_ext_ref2 );
786 END IF;
787
788 xProgress := 'ADVOB-10-1298';
789 IF p_ext_ref3 IS NOT NULL
790 THEN
791 dbms_sql.bind_variable ( Header_sel_c,
792 'l_ext_ref3',
793 p_ext_ref3 );
794 END IF;
795
796 xProgress := 'ADVOB-10-1299';
797 IF p_ext_ref4 IS NOT NULL
798 THEN
799 dbms_sql.bind_variable ( Header_sel_c,
800 'l_ext_ref4',
801 p_ext_ref4 );
802 END IF;
803
804 xProgress := 'ADVOB-10-1300';
805 IF p_ext_ref5 IS NOT NULL
806 THEN
807 dbms_sql.bind_variable ( Header_sel_c,
808 'l_ext_ref5',
809 p_ext_ref5 );
810 END IF;
811
812 xProgress := 'ADVOB-10-1301';
813 IF p_ext_ref6 IS NOT NULL
814 THEN
815 dbms_sql.bind_variable ( Header_sel_c,
816 'l_ext_ref6',
817 p_ext_ref6 );
818 END IF;
819
823 **
820 /*
821 **
822 ** Execute the Header level SELECT statement.
824 */
825
826 xProgress := 'ADVOB-10-1302';
827 l_header_row_processed := dbms_sql.execute ( Header_sel_c );
828
829 /*
830 **
831 ** Begin the Header level loop.
832 **
833 */
834
835 xProgress := 'ADVOB-10-1310';
836 WHILE dbms_sql.fetch_rows ( Header_sel_c ) > 0
837 LOOP -- Header
838
839 /*
840 **
841 ** Store the returned values in the PL/SQL table.
842 **
843 */
844
845 xProgress := 'ADVOB-10-1320';
846 FOR i IN 1..iHeader_count
847 LOOP
848 dbms_sql.column_value ( Header_sel_c,
849 i,
850 l_header_tbl(i).value );
851 -- fix for 5711134
852
853 dbms_sql.column_value ( Header_sel_c,
854 i,
855 l_key_tbl(i).value );
856
857
858 END LOOP;
859
860 ec_debug.pl ( 3, 'l_header_tbl(n_advice_header_id_pos).value: ',l_header_tbl(n_advice_header_id_pos).value );
861
862 /*
863 **
864 ** Update ECE_ADVO_HEADERS to archive the current Advice header.
865 **
866 */
867
868 UPDATE ece_advo_headers
869 SET edi_processed_flag = 'Y',
870 edi_process_date = SYSDATE
871 WHERE advice_header_id = l_header_tbl(n_advice_header_id_pos).value;
872
873 IF SQL%NOTFOUND
874 THEN
875 ec_debug.pl ( 0,
876 'EC',
877 'ECE_NO_ROW_UPDATED',
878 'PROGRESS_LEVEL',
879 xProgress,
880 'INFO',
881 'EDI_PROCESSED_FLAG',
882 'TABLE_NAME',
883 'ECE_ADVO_HEADERS' );
884 END IF;
885
886 /*
887 **
888 ** Set the value of the Transaction_Date column in the
889 ** PL/SQL table.
890 **
891 */
892
893 xProgress := 'ADVOB-10-1330';
894 l_header_tbl(n_trx_date_pos).value := TO_CHAR(dTransaction_date,'YYYYMMDD HH24MISS' );
895 ec_debug.pl ( 3, 'l_header_tbl(n_trx_date_pos).value: ',l_header_tbl(n_trx_date_pos).value );
896
897 /*
898 **
899 ** Pass the PL/SQL table to the Code Cross Reference engine.
900 **
901 */
902
903 xProgress := 'ADVOB-10-1340';
904 EC_Code_Conversion_PVT.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
905 p_return_status => l_return_status,
906 p_msg_count => l_msg_count,
907 p_msg_data => l_msg_data,
908 p_key_tbl => l_key_tbl,
909 p_tbl => l_header_tbl );
910
911 /*
912 **
913 ** Retrieve the next sequence number for the primary key value, and
914 ** insert this record into the Header interface table.
915 **
916 */
917
918 xProgress := 'ADVOB-10-1350';
919 BEGIN
920 SELECT ece_advo_headers_interface_s.nextval
921 INTO l_header_fkey
922 FROM sys.dual;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 ec_debug.pl ( 0,
926 'EC',
927 'ECE_GET_NEXT_SEQ_FAILED',
928 'PROGRESS_LEVEL',
929 xProgress,
930 'SEQ',
931 'ECE_ADVO_HEADERS_INTERFACE_S' );
932 END;
933
934 ec_debug.pl ( 3, 'l_header_fkey: ',l_header_fkey );
935
936 xProgress := 'ADVOB-10-1360';
937 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
938 cTransaction_Type,
939 cCommunication_Method,
940 cHeader_Interface,
941 l_header_tbl,
942 l_header_fkey );
943
944 /*
945 **
946 ** Call the (customizable) procedure to populate the corresponding
947 ** extension table.
948 **
949 */
950
951 xProgress := 'ADVOB-10-1370';
952 ece_advo_X.populate_extension_headers ( l_header_fkey,
953 l_header_tbl );
954
955 /*
956 **
957 ** Bind the "Advice_Header_ID" variable in
958 ** the SELECT clause of the Line level.
959 **
960 */
961
962 xProgress := 'ADVOB-10-1380';
963 dbms_sql.bind_variable ( Line_sel_c,
964 'l_advice_header_id',
965 l_header_tbl(n_advice_header_id_pos).value );
966
967 /*
968 **
969 ** Execute the Line level SELECT statement.
970 **
971 */
972
973 xProgress := 'ADVOB-10-1390';
974 l_line_row_processed := dbms_sql.execute ( Line_sel_c );
975
976 /*
977 **
978 ** Begin the Line level loop.
979 **
980 */
981
985
982 xProgress := 'ADVOB-10-1400';
983 WHILE dbms_sql.fetch_rows ( Line_sel_c ) > 0
984 LOOP --- Line
986 /*
987 **
988 ** Store the returned values in the PL/SQL table.
989 **
990 */
991
992 xProgress := 'ADVOB-10-1410';
993 FOR j IN 1..iLine_count LOOP
994 dbms_sql.column_value ( Line_sel_c,
995 j,
996 l_line_tbl(j).value );
997
998 -- fix for bug 5711134
999 dbms_sql.column_value ( Line_sel_c,
1000 j,
1001 l_key_tbl(iHeader_count + j).value );
1002
1003 END LOOP;
1004
1005 /*
1006 **
1007 ** Pass the PL/SQL table to the Code Cross Reference engine.
1008 **
1009 */
1010
1011 xProgress := 'ADVOB-10-1420';
1012 EC_Code_Conversion_PVT.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
1013 p_return_status => l_return_status,
1014 p_msg_count => l_msg_count,
1015 p_msg_data => l_msg_data,
1016 p_key_tbl => l_key_tbl,
1017 p_tbl => l_line_tbl );
1018
1019 /*
1020 **
1021 ** Retrieve the next sequence number for the primary key value, and
1022 ** insert this record into the Line interface table.
1023 **
1024 */
1025
1026 xProgress := 'ADVOB-10-1430';
1027 BEGIN
1028 SELECT ece_advo_details_interface_s.nextval
1029 INTO l_line_fkey
1030 FROM sys.dual;
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 ec_debug.pl ( 0,
1034 'EC',
1035 'ECE_GET_NEXT_SEQ_FAILED',
1036 'PROGRESS_LEVEL',
1037 xProgress,
1038 'SEQ',
1039 'ECE_ADVO_DETAILS_INTERFACE_S' );
1040 END;
1041
1042 ec_debug.pl ( 3, 'l_line_fkey: ',l_line_fkey );
1043
1044 xProgress := 'ADVOB-10-1440';
1045 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id,
1046 cTransaction_Type,
1047 cCommunication_Method,
1048 cLine_Interface,
1049 l_line_tbl,
1050 l_line_fkey );
1051
1052 /*
1053 **
1054 ** Call the (customizable) procedure to populate the corresponding
1055 ** extension table.
1056 **
1057 */
1058
1059 xProgress := 'ADVOB-10-1440';
1060 ece_advo_X.populate_extension_details ( l_line_fkey,
1061 l_line_tbl );
1062
1063
1064 END LOOP; /* Line WHILE loop */
1065
1066 xProgress := 'ADVOB-10-1443';
1067 IF ( dbms_sql.last_row_count = 0 )
1068 THEN
1069 v_LevelProcessed := 'DETAIL';
1070 ec_debug.pl ( 0,
1071 'EC',
1072 'ECE_NO_DB_ROW_PROCESSED',
1073 'PROGRESS_LEVEL',
1074 xProgress,
1075 'LEVEL_PROCESSED',
1076 v_LevelProcessed,
1077 'TRANSACTION_TYPE',
1078 cTransaction_Type );
1079 END IF;
1080
1081 END LOOP; /* Header WHILE loop */
1082
1083 xProgress := 'ADVOB-10-1446';
1084 IF ( dbms_sql.last_row_count = 0 )
1085 THEN
1086 v_LevelProcessed := 'HEADER';
1087 ec_debug.pl ( 0,
1088 'EC',
1089 'ECE_NO_DB_ROW_PROCESSED',
1090 'PROGRESS_LEVEL',
1091 xProgress,
1092 'LEVEL_PROCESSED',
1093 v_LevelProcessed,
1094 'TRANSACTION_TYPE',
1095 cTransaction_Type );
1096 END IF;
1097
1098 /*
1099 **
1100 ** Close all open cursors.
1101 **
1102 */
1103
1104 xProgress := 'ADVOB-10-1450';
1105 dbms_sql.close_cursor(Header_sel_c );
1106
1107 xProgress := 'ADVOB-10-1460';
1108 dbms_sql.close_cursor(Line_sel_c );
1109
1110 ec_debug.pop('ece_advo_advice_pkg.Extract_From_Base_Apps' );
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114
1115 ec_debug.pl ( 0,
1116 'EC',
1117 'ECE_PROGRAM_ERROR',
1118 'PROGRESS_LEVEL',
1119 xProgress );
1120
1121 ec_debug.pl ( 0,
1122 'EC',
1123 'ECE_ERROR_MESSAGE',
1124 'ERROR_MESSAGE',
1125 SQLERRM );
1126
1127 app_exception.raise_exception;
1128
1129 END Extract_From_Base_Apps;
1130
1131
1132 -- **************************************************************************
1133 -- PROCEDURE Put_Data_To_Output_Table
1134 -- This procedure has the following functionalities:
1135 -- 1. Build SQL statement dynamically to extract data from
1136 -- Interface Tables.
1140 -- **************************************************************************
1137 -- 2. Execute the dynamic SQL statement.
1138 -- 3. Populate the ECE_OUTPUT table with the extracted data.
1139 -- 4. Delete data from Interface Tables.
1141
1142
1143 PROCEDURE Put_Data_To_Output_Table ( cCommunication_Method IN VARCHAR2,
1144 cTransaction_Type IN VARCHAR2,
1145 iOutput_width IN INTEGER,
1146 iRun_id IN INTEGER,
1147 cHeader_Interface IN VARCHAR2,
1148 cLine_Interface IN VARCHAR2 )
1149 IS
1150
1151 /*
1152 **
1153 ** Variable definitions. 'Interface_tbl_type' is a PL/SQL table typedef
1154 ** with the following structure:
1155 **
1156 ** base_table_name VARCHAR2(50)
1157 ** base_column_name VARCHAR2(50)
1158 ** interface_table_name VARCHAR2(50)
1159 ** interface_column_name VARCHAR2(50)
1160 ** Record_num NUMBER
1161 ** Position NUMBER
1162 ** data_type VARCHAR2(50)
1163 ** data_length NUMBER
1164 ** value VARCHAR2(400)
1165 ** layout_code VARCHAR2(2)
1166 ** record_qualifier VARCHAR2(3)
1167 ** interface_column_id NUMBER
1168 ** conversion_seq NUMBER
1169 ** xref_category_id NUMBER
1170 ** conversion_group_id NUMBER
1171 ** xref_key1_source_column VARCHAR2(50)
1172 ** xref_key2_source_column VARCHAR2(50)
1173 ** xref_key3_source_column VARCHAR2(50)
1174 ** xref_key4_source_column VARCHAR2(50)
1175 ** xref_key5_source_column VARCHAR2(50)
1176 ** ext_val1 VARCHAR2(80)
1177 ** ext_val2 VARCHAR2(80)
1178 ** ext_val3 VARCHAR2(80)
1179 ** ext_val4 VARCHAR2(80)
1180 ** ext_val5 VARCHAR2(80)
1181 **
1182 */
1183
1184 xProgress VARCHAR2(30);
1185 v_LevelProcessed VARCHAR2(40);
1186 cOutput_path VARCHAR2(120);
1187
1188 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
1189 l_line_tbl ece_flatfile_pvt.Interface_tbl_type;
1190
1191 c_header_common_key_name VARCHAR2(40);
1192 c_line_common_key_name VARCHAR2(40);
1193 c_key_3 VARCHAR2(22):= RPAD(' ',22);
1194 c_file_common_key VARCHAR2(255);
1195
1196 nHeader_key_pos NUMBER;
1197 nLine_key_pos NUMBER;
1198 nLine_t_key_pos NUMBER;
1199 nTrans_code_pos NUMBER;
1200
1201 Header_sel_c INTEGER;
1202 Line_sel_c INTEGER;
1203
1204 Header_del_c1 INTEGER;
1205 Line_del_c1 INTEGER;
1206
1207 Header_del_c2 INTEGER;
1208 Line_del_c2 INTEGER;
1209
1210 cHeader_select VARCHAR2(32000);
1211 cLine_select VARCHAR2(32000);
1212
1213 cHeader_from VARCHAR2(32000);
1214 cLine_from VARCHAR2(32000);
1215
1216 cHeader_where VARCHAR2(32000);
1217 cLine_where VARCHAR2(32000);
1218
1219 cHeader_delete1 VARCHAR2(32000);
1220 cLine_delete1 VARCHAR2(32000);
1221
1222 cHeader_delete2 VARCHAR2(32000);
1223 cLine_delete2 VARCHAR2(32000);
1224
1225 iHeader_count NUMBER;
1226 iLine_count NUMBER;
1227
1228 n_advice_header_id_pos NUMBER;
1229
1230 rHeader_rowid ROWID;
1231 rLine_rowid ROWID;
1232
1233 cHeader_X_Interface VARCHAR2(50);
1234 cLine_X_Interface VARCHAR2(50);
1235
1236 rHeader_X_rowid ROWID;
1237 rLine_X_rowid ROWID;
1238
1239 dummy INTEGER;
1240
1241 BEGIN
1242
1243 /*
1244 **
1245 ** Debug statements for the parameter values.
1246 **
1247 */
1248
1249 ec_debug.push ( 'ece_advo_advice_pkg.Put_Data_To_Output_Table' );
1250 ec_debug.pl ( 3, 'cCommunication_Method: ', cCommunication_Method );
1251 ec_debug.pl ( 3, 'cTransaction_Type: ',cTransaction_Type );
1252 ec_debug.pl ( 3, 'iOutput_width: ',iOutput_width );
1253 ec_debug.pl ( 3, 'iRun_id: ',iRun_id );
1254 ec_debug.pl ( 3, 'cHeader_Interface: ',cHeader_Interface );
1255 ec_debug.pl ( 3, 'cLine_Interface: ',cLine_Interface );
1256
1257
1258
1259 /*
1260 **
1261 ** The 'select_clause' procedure will build the SELECT, FROM and WHERE
1262 ** clauses in preparation for the dynamic SQL call using the EDI data
1263 ** dictionary for the build. Any necessary customizations to these
1264 ** clauses need to be made *after* the clause is built, but *before*
1265 ** the SQL call.
1266 **
1267 */
1268
1269
1270 xProgress := 'ADVOB-20-1020';
1271 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1272 cCommunication_Method,
1273 cHeader_Interface,
1274 cHeader_X_Interface,
1275 l_header_tbl,
1276 c_header_common_key_name,
1277 cHeader_select,
1278 cHeader_from,
1279 cHeader_where );
1280
1281
1282 xProgress := 'ADVOB-20-1030';
1286 cLine_X_Interface,
1283 ece_flatfile_pvt.select_clause ( cTransaction_Type,
1284 cCommunication_Method,
1285 cLine_Interface,
1287 l_line_tbl,
1288 c_line_common_key_name,
1289 cLine_select,
1290 cLine_from,
1291 cLine_where );
1292
1293 /*
1294 **
1295 ** Customize the WHERE clauses to insure the proper joins, and
1296 ** customize the SELECT clauses to include the ROWID. Records
1297 ** will be deleted from the interface tables using these ROWID
1298 ** values.
1299 **
1300 */
1301
1302 xProgress := 'ADVOB-20-1040';
1303 cHeader_where := cHeader_where ||
1304 ' AND ' ||
1305 cHeader_Interface ||
1306 '.RUN_ID =' ||
1307 ':l_iRun_id';
1308
1309
1310 xProgress := 'ADVOB-20-1050';
1311 cLine_where := cLine_where ||
1312 ' AND ' ||
1313 cLine_Interface ||
1314 '.RUN_ID =' ||
1315 ':x_iRun_id' ||
1316 ' AND ' ||
1317 cLine_Interface ||
1318 '.ADVICE_HEADER_ID = :x_advice_header_id';
1319
1320 xProgress := 'ADVOB-20-1060';
1321 cHeader_select := cHeader_select ||
1322 ',' ||
1323 cHeader_Interface ||
1324 '.ROWID, ' ||
1325 cHeader_X_Interface ||
1326 '.ROWID';
1327
1328 xProgress := 'ADVOB-20-1070';
1329 cLine_select := cLine_select ||
1330 ',' ||
1331 cLine_Interface ||
1332 '.ROWID,' ||
1333 cLine_X_Interface ||
1334 '.ROWID';
1335
1336 /*
1337 **
1338 ** Build the complete SELECT and DELETE statements
1339 ** for each level.
1340 **
1341 */
1342
1343 xProgress := 'ADVOB-20-1080';
1344 cHeader_select := cHeader_select ||
1345 cHeader_from ||
1346 cHeader_where ||
1347 ' FOR UPDATE';
1348 ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
1349
1350 xProgress := 'ADVOB-20-1090';
1351 cLine_select := cLine_select ||
1352 cLine_from ||
1353 cLine_where ||
1354 ' FOR UPDATE';
1355 ec_debug.pl ( 3, 'cLine_select: ',cLine_select );
1356
1357 xProgress := 'ADVOB-20-1100';
1358 cHeader_delete1 := 'DELETE FROM ' ||
1359 cHeader_Interface ||
1360 ' WHERE ROWID = :col_rowid';
1361 ec_debug.pl ( 3, 'cHeader_delete1: ',cHeader_delete1 );
1362
1363 xProgress := 'ADVOB-20-1110';
1364 cLine_delete1 := 'DELETE FROM ' ||
1365 cLine_Interface ||
1366 ' WHERE ROWID = :col_rowid';
1367 ec_debug.pl ( 3, 'cLine_delete1: ',cLine_delete1 );
1368
1369 xProgress := 'ADVOB-20-1120';
1370 cHeader_delete2 := 'DELETE FROM ' ||
1371 cHeader_X_Interface ||
1372 ' WHERE ROWID = :col_rowid';
1373 ec_debug.pl ( 3, 'cHeader_delete2: ',cHeader_delete2 );
1374
1375 xProgress := 'ADVOB-20-1130';
1376 cLine_delete2 := 'DELETE FROM ' ||
1377 cLine_X_Interface ||
1378 ' WHERE ROWID = :col_rowid';
1379 ec_debug.pl ( 3, 'cLine_delete2: ',cLine_delete2 );
1380
1381 /*
1382 **
1383 ** Open a cursor for each SELECT and DELETE call. This tells
1384 ** the database to reserve space for the data returned by the
1385 ** SELECT and DELETE statements.
1386 **
1387 */
1388
1389 xProgress := 'ADVOB-20-1140';
1390 Header_sel_c := dbms_sql.open_cursor;
1391
1392 xProgress := 'ADVOB-20-1150';
1393 Line_sel_c := dbms_sql.open_cursor;
1394
1395 xProgress := 'ADVOB-20-1160';
1396 Header_del_c1 := dbms_sql.open_cursor;
1397
1398 xProgress := 'ADVOB-20-1170';
1399 Line_del_c1 := dbms_sql.open_cursor;
1400
1401 xProgress := 'ADVOB-20-1180';
1402 Header_del_c2 := dbms_sql.open_cursor;
1403
1404 xProgress := 'ADVOB-20-1190';
1405 Line_del_c2 := dbms_sql.open_cursor;
1406
1407 /*
1408 **
1409 ** Parse each SELECT and DELETE statement so the database understands
1410 ** the command.
1411 **
1412 */
1413
1414 xProgress := 'ADVOB-20-1200';
1418 dbms_sql.native );
1415 BEGIN
1416 dbms_sql.parse ( Header_sel_c,
1417 cHeader_select,
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1422 cHeader_select );
1423 app_exception.raise_exception;
1424 END;
1425
1426 xProgress := 'ADVOB-20-1210';
1427 BEGIN
1428 dbms_sql.parse ( Line_sel_c,
1429 cLine_select,
1430 dbms_sql.native );
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1434 cLine_select );
1435 app_exception.raise_exception;
1436 END;
1437
1438 xProgress := 'ADVOB-20-1220';
1439 BEGIN
1440 dbms_sql.parse ( Header_del_c1,
1441 cHeader_delete1,
1442 dbms_sql.native );
1443 EXCEPTION
1444 WHEN OTHERS THEN
1445 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1446 cHeader_delete1 );
1447 app_exception.raise_exception;
1448 END;
1449
1450 xProgress := 'ADVOB-20-1230';
1451 BEGIN
1452 dbms_sql.parse ( Line_del_c1,
1453 cLine_delete1,
1454 dbms_sql.native );
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1458 cLine_delete1 );
1459 app_exception.raise_exception;
1460 END;
1461
1462 xProgress := 'ADVOB-20-1240';
1463 BEGIN
1464 dbms_sql.parse ( Header_del_c2,
1465 cHeader_delete2,
1466 dbms_sql.native );
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1470 cHeader_delete2 );
1471 app_exception.raise_exception;
1472 END;
1473
1474 xProgress := 'ADVOB-20-1250';
1475 BEGIN
1476 dbms_sql.parse ( Line_del_c2,
1477 cLine_delete2,
1478 dbms_sql.native );
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1482 cLine_delete2 );
1483 app_exception.raise_exception;
1484 END;
1485
1486 /*
1487 **
1488 ** Initialize all counters.
1489 **
1490 */
1491
1492 xProgress := 'ADVOB-20-1260';
1493 iHeader_count := l_header_tbl.count;
1494 ec_debug.pl ( 3, 'iHeader_count: ',iHeader_count );
1495
1496 xProgress := 'ADVOB-20-1270';
1497 iLine_count := l_line_tbl.count;
1498 ec_debug.pl ( 3, 'iLine_count: ',iLine_count );
1499
1500 /*
1501 **
1502 ** Define the data type for every column in the Header
1503 ** SELECT statement so the database understands how to
1504 ** populate it.
1505 **
1506 */
1507
1508 xProgress := 'ADVOB-20-1280';
1509 FOR k IN 1..iHeader_count
1510 LOOP
1511 dbms_sql.define_column ( Header_sel_c,
1512 k,
1513 cHeader_select,
1514 ece_flatfile_pvt.G_MaxColWidth );
1515 END LOOP;
1516
1517 /*
1518 **
1519 ** Define the ROWIDs for the Header
1520 ** DELETE statements.
1521 **
1522 */
1523
1524 xProgress := 'ADVOB-20-1290';
1525 dbms_sql.define_column_rowid ( Header_sel_c,
1526 iHeader_count + 1,
1527 rHeader_rowid );
1528
1529 xProgress := 'ADVOB-20-1300';
1530 dbms_sql.define_column_rowid ( Header_sel_c,
1531 iHeader_count + 2,
1532 rHeader_X_rowid );
1533
1534 /*
1535 **
1536 ** Define the data type for every column in the Line
1537 ** SELECT statement so the database understands how to
1538 ** populate it.
1539 **
1540 */
1541
1542 xProgress := 'ADVOB-20-1310';
1543 FOR k IN 1..iLine_count
1544 LOOP
1545 dbms_sql.define_column ( Line_sel_c,
1546 k,
1547 cLine_select,
1548 ece_flatfile_pvt.G_MaxColWidth );
1549 END LOOP;
1550
1551 /*
1552 **
1553 ** Define the ROWIDs for the Line
1554 ** DELETE statements.
1555 **
1556 */
1557
1558 xProgress := 'ADVOB-20-1320';
1559 dbms_sql.define_column_rowid ( Line_sel_c,
1560 iLine_count + 1,
1561 rLine_rowid );
1562
1563 xProgress := 'ADVOB-20-1330';
1564 dbms_sql.define_column_rowid ( Line_sel_c,
1565 iLine_count + 2,
1566 rLine_X_rowid );
1567
1568 /*
1569 **
1570 ** Find the necessary columns in the PL/SQL tables for the
1571 ** Common Key values.
1572 **
1573 */
1574
1575 xProgress := 'ADVOB-20-1340';
1576 ece_flatfile_pvt.Find_pos ( l_header_tbl,
1577 ece_flatfile_pvt.G_Translator_Code,
1578 nTrans_code_pos );
1582 ece_flatfile_pvt.Find_pos ( l_header_tbl,
1579 ec_debug.pl ( 3, 'nTrans_code_pos: ',nTrans_code_pos );
1580
1581 xProgress := 'ADVOB-20-1350';
1583 c_header_common_key_name,
1584 nHeader_key_pos );
1585 ec_debug.pl ( 3, 'nHeader_key_pos: ',nHeader_key_pos );
1586
1587 xProgress := 'ADVOB-20-1360';
1588 ece_flatfile_pvt.Find_pos ( l_header_tbl,
1589 'ADVICE_HEADER_ID',
1590 n_advice_header_id_pos );
1591 ec_debug.pl ( 3, 'n_advice_header_id_pos: ',n_advice_header_id_pos );
1592
1593
1594 xProgress := 'ADVOB-20-1370';
1595 ece_flatfile_pvt.Find_pos ( l_line_tbl,
1596 c_line_common_key_name,
1597 nLine_key_pos );
1598 ec_debug.pl ( 3, 'nLine_key_pos: ',nLine_key_pos );
1599
1600 xProgress := 'ADVOB-20-1371';
1601 dbms_sql.bind_variable ( Header_sel_c,
1602 'l_iRun_id',
1603 iRun_id );
1604
1605
1606 /*
1607 **
1608 ** Execute the Header level SELECT statement.
1609 **
1610 */
1611
1612 xProgress := 'ADVOB-20-1380';
1613 dummy := dbms_sql.execute ( Header_sel_c );
1614
1615 /*
1616 **
1617 ** Begin the Header level loop.
1618 **
1619 */
1620
1621 xProgress := 'ADVOB-20-1390';
1622 WHILE dbms_sql.fetch_rows ( Header_sel_c ) > 0
1623 LOOP -- Header
1624
1625 /*
1626 **
1627 ** Store the returned values in the PL/SQL table.
1628 **
1629 */
1630
1631 xProgress := 'ADVOB-20-1400';
1632 FOR i IN 1..iHeader_count
1633 LOOP
1634 dbms_sql.column_value ( Header_sel_c,
1635 i,
1636 l_header_tbl(i).value );
1637 END LOOP;
1638
1639 /*
1640 **
1641 ** Store the ROWIDs.
1642 **
1643 */
1644
1645 xProgress := 'ADVOB-20-1410';
1646 dbms_sql.column_value ( Header_sel_c,
1647 iHeader_count + 1,
1648 rHeader_rowid );
1649
1650 xProgress := 'ADVOB-20-1420';
1651 dbms_sql.column_value ( Header_sel_c,
1652 iHeader_count + 2,
1653 rHeader_X_rowid );
1654
1655 /*
1656 **
1657 ** Build the Common Key record for this level.
1658 **
1659 */
1660
1661 xProgress := 'ADVOB-20-1430';
1662 c_file_common_key := RPAD(NVL(SUBSTRB(l_header_tbl(nTrans_code_pos).value, 1, 25),
1663 ' '),
1664 25 );
1665
1666 xProgress := 'ADVOB-20-1440';
1667 c_file_common_key := c_file_common_key ||
1668 RPAD(NVL(SUBSTRB(l_header_tbl(nHeader_key_pos).value, 1, 22),
1669 ' '),
1670 22) ||
1671 c_key_3 ||
1672 c_key_3;
1673 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
1674
1675 /*
1676 **
1677 ** Write the record to the output table.
1678 **
1679 */
1680
1681 xProgress := 'ADVOB-20-1450';
1682 ece_flatfile_pvt.write_to_ece_output ( cTransaction_Type,
1683 cCommunication_Method,
1684 cHeader_Interface,
1685 l_header_tbl,
1686 iOutput_width,
1687 iRun_id,
1688 c_file_common_key );
1689
1690 /*
1691 **
1692 ** Bind the Advice_Header_ID variable in the Line
1693 ** SELECT clause.
1694 **
1695 */
1696
1697 xProgress := 'ADVOB-20-1460';
1698 dbms_sql.bind_variable ( Line_sel_c,
1699 'x_iRun_id',
1700 iRun_id);
1701
1702
1703 xProgress := 'ADVOB-20-1461';
1704 dbms_sql.bind_variable ( Line_sel_c,
1705 'x_advice_header_id',
1706 l_header_tbl(n_advice_header_id_pos).value );
1707
1708 /*
1709 **
1710 ** Execute the Line level SELECT statement.
1711 **
1712 */
1713
1714 xProgress := 'ADVOB-20-1470';
1715 dummy := dbms_sql.execute ( Line_sel_c );
1716
1717 /*
1718 **
1719 ** Begin the Line level loop.
1720 **
1721 */
1722
1723 xProgress := 'ADVOB-20-1480';
1724 WHILE dbms_sql.fetch_rows(Line_sel_c) > 0 LOOP --- Line
1725
1726 /*
1727 **
1728 ** Store the returned values in the PL/SQL table.
1729 **
1730 */
1731
1732 xProgress := 'ADVOB-20-1490';
1733 FOR j IN 1..iLine_count
1734 LOOP
1735 dbms_sql.column_value ( Line_sel_c,
1736 j,
1737 l_line_tbl(j).value );
1738 END LOOP;
1739
1740 /*
1741 **
1742 ** Store the ROWIDs.
1743 **
1744 */
1745
1749 rLine_rowid );
1746 xProgress := 'ADVOB-20-1500';
1747 dbms_sql.column_value ( Line_sel_c,
1748 iLine_count + 1,
1750
1751 xProgress := 'ADVOB-20-1510';
1752 dbms_sql.column_value ( Line_sel_c,
1753 iLine_count + 2,
1754 rLine_X_rowid );
1755
1756 /*
1757 **
1758 ** Build the Common Key record for this level.
1759 **
1760 */
1761
1762 xProgress := 'ADVOB-20-1520';
1763 c_file_common_key := RPAD(NVL(SUBSTRB(l_header_tbl(nTrans_code_pos).value, 1, 25),
1764 ' '),
1765 25) ||
1766 RPAD(NVL(SUBSTRB(l_header_tbl(nHeader_key_pos).value, 1, 22),
1767 ' '),
1768 22) ||
1769 RPAD(NVL(SUBSTRB(l_line_tbl(nLine_key_pos).value, 1, 22),
1770 ' '),
1771 22) ||
1772 c_key_3;
1773 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
1774
1775 /*
1776 **
1777 ** Write the record to the output table.
1778 **
1779 */
1780
1781 xProgress := 'ADVOB-20-1530';
1782 ece_flatfile_pvt.write_to_ece_output ( cTransaction_Type,
1783 cCommunication_Method,
1784 cLine_Interface,
1785 l_line_tbl,
1786 iOutput_width,
1787 iRun_id,
1788 c_file_common_key );
1789
1790 /*
1791 **
1792 ** Bind the variables (ROWIDs) in the DELETE statements.
1793 **
1794 */
1795
1796 xProgress := 'ADVOB-20-1540';
1797 dbms_sql.bind_variable ( Line_del_c1,
1798 'col_rowid',
1799 rLine_rowid );
1800
1801 xProgress := 'ADVOB-20-1550';
1802 dbms_sql.bind_variable ( Line_del_c2,
1803 'col_rowid',
1804 rLine_X_rowid );
1805
1806 /*
1807 **
1808 ** Delete the rows from the interface table.
1809 **
1810 */
1811
1812 xProgress := 'ADVOB-20-1560';
1813 dummy := dbms_sql.execute ( Line_del_c1 );
1814
1815 xProgress := 'ADVOB-20-1570';
1816 dummy := dbms_sql.execute ( Line_del_c2 );
1817
1818 END LOOP; /* Line WHILE loop */
1819
1820 xProgress := 'ADVOB-20-1575';
1821 IF ( dbms_sql.last_row_count = 0 )
1822 THEN
1823 v_LevelProcessed := 'LINE';
1824 ec_debug.pl ( 0,
1825 'EC',
1826 'ECE_NO_DB_ROW_PROCESSED',
1827 'PROGRESS_LEVEL',
1828 xProgress,
1829 'LEVEL_PROCESSED',
1830 v_LevelProcessed,
1831 'TRANSACTION_TYPE',
1832 cTransaction_Type );
1833 END IF;
1834
1835 /*
1836 **
1837 ** Bind the variables (ROWIDs) in the DELETE statements.
1838 **
1839 */
1840
1841 xProgress := 'ADVOB-20-1580';
1842 dbms_sql.bind_variable ( Header_del_c1,
1843 'col_rowid',
1844 rHeader_rowid );
1845
1846 xProgress := 'ADVOB-20-1590';
1847 dbms_sql.bind_variable ( Header_del_c2,
1848 'col_rowid',
1849 rHeader_X_rowid );
1850
1851 /*
1852 **
1853 ** Delete the rows from the interface table.
1854 **
1855 */
1856
1857 xProgress := 'ADVOB-20-1600';
1858 dummy := dbms_sql.execute ( Header_del_c1 );
1859
1860 xProgress := 'ADVOB-20-1610';
1861 dummy := dbms_sql.execute ( Header_del_c2 );
1862
1863
1864 END LOOP; /* Header WHILE loop */
1865
1866 xProgress := 'ADVOB-20-1615';
1867 IF ( dbms_sql.last_row_count = 0 )
1868 THEN
1869 v_LevelProcessed := 'HEADER';
1870 ec_debug.pl ( 0,
1871 'EC',
1872 'ECE_NO_DB_ROW_PROCESSED',
1873 'PROGRESS_LEVEL',
1874 xProgress,
1875 'LEVEL_PROCESSED',
1876 v_LevelProcessed,
1877 'TRANSACTION_TYPE',
1878 cTransaction_Type );
1879 END IF;
1880
1881 /*
1882 **
1883 ** Close all open cursors.
1884 **
1885 */
1886
1887 xProgress := 'ADVOB-20-1620';
1888 dbms_sql.close_cursor ( Header_sel_c );
1889
1890 xProgress := 'ADVOB-20-1630';
1891 dbms_sql.close_cursor ( Line_sel_c );
1892
1893 xProgress := 'ADVOB-20-1640';
1894 dbms_sql.close_cursor ( Header_del_c1 );
1895
1896 xProgress := 'ADVOB-20-1650';
1897 dbms_sql.close_cursor ( Line_del_c1 );
1898
1899 ec_debug.pop ( 'ece_advo_advice_pkg.Put_Data_To_Output_Table' );
1900
1901 EXCEPTION
1902 WHEN OTHERS THEN
1903
1904 ec_debug.pl ( 0,
1905 'EC',
1909
1906 'ECE_PROGRAM_ERROR',
1907 'PROGRESS_LEVEL',
1908 xProgress );
1910 ec_debug.pl ( 0,
1911 'EC',
1912 'ECE_ERROR_MESSAGE',
1913 'ERROR_MESSAGE',
1914 SQLERRM );
1915
1916 app_exception.raise_exception;
1917
1918 END Put_Data_To_Output_Table;
1919
1920 END ece_advo_advice_pkg;