[Home] [Help]
PACKAGE BODY: APPS.GML_GPOAO
Source
1 PACKAGE BODY GML_GPOAO as
2 /* $Header: GMLPAOB.pls 115.14 2002/11/08 06:39:15 gmangari ship $ */
3 /*============================= GML_GPOAO =================================*/
4 /*============================================================================
5 PURPOSE: Creates procedures for exporting PO Ack information
6 to a flat file, and the API called to initiate
7 the extract process.
8
9 NOTES: To load the package body, run the script:
10
11 sql> start GMLPAOB.pls
12
13 HISTORY: 02/15/99 dgrailic Created.
14 05/17/99 dgrailic Change to use GML_ prefix for tables.
15 change table names to use full words instad of abbreviations
16 change SAC to DAC to better reflect detail charges
17 08/05/99 mguthrie Added calls to ECE_FLATFILE_PVT.INIT_TABLE
18 08/18/99 siwang Fixed few bugs.
19 26-OCT-2002 Bug#2642152 RajaSekhar Added NOCOPY hint
20
21 ===========================================================================*/
22
23 /*===========================================================================
24
25 PROCEDURE NAME: Extract_GPOAO_Outbound
26
27 PURPOSE: This PLSQL procedure produces an ASCII file containing
28 an OPM PO Ack Outbound
29 This ASCII file may then be processed by
30 third-party EDI translation software to generate and send
31 the EDI Outbound Ship Notice transaction.
32
33 NOTES: This script takes nine parameters:
34 1. The output path
35 2. The output file name
36 3. Required field, OPM organization code
37 4. Optional Order Number from
38 5. Optional Order Number to
39 6. Optional Creation Date from
40 7. Optional Creation Date to
41 8. Optional OF Customer Name
42 9. debug
43
44 If this script exits with an error code, the output file
45 should not be used. Under an error condition, the database
46 may not be in sync with the extracted data.
47
48 HISTORY: 02/12/99 dgrailic Created.
49 05/17/99 dgrailic Modified to use GML_ prefix
50 ============================================================================ */
51 /* Variable declarations. Assign the Run_ID, Output Path, */
52 /* and Temporary Filename to local PL/SQL variables. */
53
54 PROCEDURE Extract_GPOAO_Outbound ( errbuf OUT NOCOPY VARCHAR2,
55 retcode OUT NOCOPY VARCHAR2,
56 v_OutputPath IN VARCHAR2,
57 v_Filename IN VARCHAR2,
58 v_Orgn_Code IN VARCHAR2,
59 v_Order_No_From IN VARCHAR2,
60 v_Order_No_To IN VARCHAR2,
61 v_Creation_Date_From IN VARCHAR2,
62 v_Creation_Date_To IN VARCHAR2,
63 v_Customer_Name IN VARCHAR2,
64 v_debug_mode IN NUMBER default 0 )
65 IS
66 v_RunID NUMBER := 0;
67 v_OutputWidth INTEGER := 4000;
68 v_TransactionType VARCHAR2(120) := 'GPOAO';
69 v_CommunicationMethod VARCHAR2(120) := 'EDI';
70 v_OutputFilePtr utl_file.file_type;
71 v_OutputLine VARCHAR2(2000);
72 v_OutputRecordCount NUMBER;
73 v_Trace VARCHAR2(80);
74 v_industry VARCHAR2(240);
75 v_oracle_schema VARCHAR2(240);
76 v_Org VARCHAR2(1);
77 v_Type VARCHAR2(30) := 'GPOAO';
78 v_RequestID NUMBER := 0;
79
80 v_ORD_Interface VARCHAR2(80) := 'GML_GPOAO_ORDERS';
81 v_OAC_Interface VARCHAR2(80) := 'GML_GPOAO_ORDER_CHARGES';
82 v_OTX_Interface VARCHAR2(80) := 'GML_GPOAO_ORDER_TEXT';
83 v_DTL_Interface VARCHAR2(80) := 'GML_GPOAO_DETAILS';
84 v_DAC_Interface VARCHAR2(80) := 'GML_GPOAO_DETAIL_CHARGES';
85 v_DTX_Interface VARCHAR2(80) := 'GML_GPOAO_DETAIL_TEXT';
86 v_ALL_Interface VARCHAR2(80) := 'GML_GPOAO_DETAIL_ALLOCATIONS';
87 xProgress VARCHAR2(80);
88
89 /* SW 07/09/99, Y2K date issue. */
90 v_Creat_Date_From DATE := TO_DATE(v_Creation_Date_From, 'YYYY/MM/DD HH24:MI:SS');
91 v_Creat_Date_To DATE := TO_DATE(v_Creation_Date_To, 'YYYY/MM/DD HH24:MI:SS');
92 /* end of Y2K issue. */
93
94 CURSOR c_OutputSource IS
95 SELECT text
96 FROM ece_output
97 WHERE run_id = v_RunID
98 ORDER BY line_id;
99
100 BEGIN
101
102 ec_debug.enable_debug ( v_debug_mode );
103 ec_debug.push ( 'GML_GPOAO.Extract_GPOAO_Outbound' );
104 ec_debug.pl ( 3, 'v_Filename: ',v_Filename);
105 ec_debug.pl ( 3, 'v_OutputPath: ',v_OutputPath );
106 ec_debug.pl ( 3, 'v_Orgn_Code: ',v_Orgn_Code);
107 ec_debug.pl ( 3, 'v_Order_No_From: ',v_Order_No_From);
108 ec_debug.pl ( 3, 'v_Order_No_To: ',v_Order_No_To);
109 ec_debug.pl ( 3, 'v_Creation_Date_From: ',v_Creation_Date_From);
110 ec_debug.pl ( 3, 'v_Creation_Date_To: ',v_Creation_Date_To);
111 ec_debug.pl ( 3, 'v_Customer_Name: ',v_Customer_Name);
112 ec_debug.pl ( 3, 'v_debug_mode: ',v_debug_mode );
113
114
115 /* Get a unique ID for the run of this script. This ID is used to group the */
116 /* records in the output table (ECE_OUTPUT) so that multiple processes may use */
117 /* this table concurrently. */
118 xProgress := 'GPOAO-10-1010';
119 BEGIN
120 SELECT ece_output_runs_s.NEXTVAL
121 INTO v_RunID
122 FROM sys.dual;
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 ec_debug.pl ( 0,
126 'EC',
127 'ECE_GET_NEXT_SEQ_FAILED',
128 'PROGRESS_LEVEL',
129 xProgress,
130 'SEQ',
131 'ECE_OUTPUT_RUNS_S' );
132 END;
133 ec_debug.pl(3, 'v_RunID: ',v_RunID);
134
135 xProgress := 'GPOAO-10-1015';
136 ec_debug.pl ( 0, 'EC', 'GML_GPOAO_START', NULL );
137
138 xProgress := 'GPOAO-10-1020';
139 ec_debug.pl ( 0, 'EC', 'ECE_RUN_ID', 'RUN_ID', v_RunID );
140
141 xProgress := 'GPOAO-10-1030';
142
143 GML_GPOAO.populate_interface_tables(
144 v_CommunicationMethod,
145 v_TransactionType,
146 v_Orgn_code,
147 v_Order_No_From,
148 v_Order_No_To,
149 v_Creat_Date_From,
150 v_Creat_Date_To,
151 v_Customer_Name,
152 v_RunID,
153 v_ORD_Interface,
154 v_OAC_Interface,
155 v_OTX_Interface,
156 v_DTL_Interface,
157 v_DAC_Interface,
158 v_DTX_Interface,
159 v_ALL_Interface
160 );
161
162
163 xProgress := 'GPOAO-10-1040';
164
165 GML_GPOAO.put_data_to_output_table(
166 v_CommunicationMethod,
167 v_TransactionType,
168 v_Orgn_code,
169 v_Order_No_From,
170 v_Order_No_To,
171 v_Creat_Date_From,
172 v_Creat_Date_To,
173 v_Customer_Name,
174 v_RunID,
175 v_OutputWidth,
176 v_ORD_Interface,
177 v_OAC_Interface,
178 v_OTX_Interface,
179 v_DTL_Interface,
180 v_DAC_Interface,
181 v_DTX_Interface,
182 v_ALL_Interface
183 );
184
185 xProgress := 'GPOAO-10-1050';
186 BEGIN
187 SELECT COUNT(*)
188 INTO v_OutputRecordCount
189 FROM ece_output
190 WHERE run_id = v_RunID;
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 ec_debug.pl ( 0,
194 'EC',
195 'ECE_GET_COUNT_FAILED',
196 'PROGRESS_LEVEL',
197 xProgress,
198 'TABLE_NAME',
199 'ECE_OUTPUT' );
200 END;
201 ec_debug.pl ( 3, 'v_OutputRecordCount: ',v_OutputRecordCount );
202
203 xProgress := 'GPOAO-10-1060';
204 IF v_OutputRecordCount > 0 THEN
205 xProgress := 'GPOAO-10-1070';
206 v_OutputFilePtr := utl_file.fopen(v_OutputPath,v_FileName,'W');
207
208 xProgress := 'GPOAO-10-1080';
209 OPEN c_OutputSource;
210 xProgress := 'GPOAO-10-1090';
211 LOOP
212 xProgress := 'GPOAO-10-1100';
213 FETCH c_OutputSource INTO v_OutputLine;
214 ec_debug.pl ( 3, 'v_OutputLine: ',v_OutputLine );
215
216 xProgress := 'GPOAO-10-1110';
217 EXIT WHEN c_OutputSource%NOTFOUND;
218
219 xProgress := 'GPOAO-10-1120';
220 utl_file.put_line(v_OutputFilePtr,v_OutputLine);
221 END LOOP;
222
223 xProgress := 'GPOAO-10-1130';
224 CLOSE c_OutputSource;
225
226 xProgress := 'GPOAO-10-1140';
227 utl_file.fclose(v_OutputFilePtr);
228 END IF;
229
230 xProgress := 'GPOAO-10-1170';
231 ec_debug.pl ( 0, 'EC', 'GML_GPOAO_COMPLETE' ,NULL );
232
233 xProgress := 'GPOAO-10-1180';
234 DELETE FROM ece_output
235 WHERE run_id = v_RunID;
236
237 IF SQL%NOTFOUND
238 THEN
239 ec_debug.pl ( 0,
240 'EC',
241 'ECE_NO_ROW_DELETED',
242 'PROGRESS_LEVEL',
243 xProgress,
244 'TABLE_NAME',
245 'ECE_OUTPUT' );
246 END IF;
247
248 ec_debug.pop ( 'GML_GPOAO.Extract_GPOAO_Outbound' );
249 ec_debug.disable_debug;
250 COMMIT;
251
252 EXCEPTION
253 WHEN utl_file.write_error THEN
254 ec_debug.pl ( 0, 'EC', 'ECE_UTL_WRITE_ERROR', NULL );
255
256 ec_debug.pl ( 0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM );
257
258 retcode := 2;
259 ec_debug.disable_debug;
260 ROLLBACK;
261 RAISE;
262
263 WHEN utl_file.invalid_path THEN
264 ec_debug.pl ( 0, 'EC', 'ECE_UTL_INVALID_PATH', NULL );
265
266 ec_debug.pl ( 0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM );
267
268 retcode := 2;
269 ec_debug.disable_debug;
270 ROLLBACK;
271 RAISE;
272
273 WHEN utl_file.invalid_operation THEN
274 ec_debug.pl ( 0,
275 'EC',
276 'ECE_UTL_INVALID_OPERATION',
277 NULL );
278
279 ec_debug.pl ( 0,
280 'EC',
281 'ECE_ERROR_MESSAGE',
282 'ERROR_MESSAGE',
283 SQLERRM );
284
285 retcode := 2;
286 ec_debug.disable_debug;
287 ROLLBACK;
288 RAISE;
289
290
291 WHEN others then
292 ec_debug.pl ( 0,
293 'EC',
294 'ECE_PROGRAM_ERROR',
295 'PROGRESS_LEVEL',
296 xProgress );
297
298 ec_debug.pl ( 0,
299 'EC',
300 'ECE_ERROR_MESSAGE',
301 'ERROR_MESSAGE',
302 SQLERRM );
303
304 retcode := 2;
305 ec_debug.disable_debug;
306 ROLLBACK;
307 RAISE;
308
309 END Extract_GPOAO_Outbound;
310
311 /*===========================================================================
312
313 PROCEDURE NAME: Populate_Interface_Tables
314
315 PURPOSE: This procedure initiates the export process for the
316 PO Ack. For each Gateway
317 interface table in this transaction, a view has been
318 created to facilitate the extract process from the
319 Application tables.
320
321 ===========================================================================*/
322
323 PROCEDURE Populate_Interface_Tables ( p_CommunicationMethod IN VARCHAR2,
324 p_TransactionType IN VARCHAR2,
325 p_Orgn_Code IN VARCHAR2,
326 p_Order_No_From IN VARCHAR2,
327 p_Order_No_To IN VARCHAR2,
328 p_Creation_Date_From IN DATE,
329 p_Creation_Date_To IN DATE,
330 p_Customer_Name IN VARCHAR2,
331 p_RunID IN INTEGER,
332 p_ORD_Interface IN VARCHAR2,
333 p_OAC_Interface IN VARCHAR2,
334 p_OTX_Interface IN VARCHAR2,
335 p_DTL_Interface IN VARCHAR2,
336 p_DAC_Interface IN VARCHAR2,
337 p_DTX_Interface IN VARCHAR2,
338 p_ALL_Interface IN VARCHAR2 )
339
340 IS
341
342 /* Variable definitions. 'Source_tbl_type' is a PL/SQL table typedef */
343 /* with the following structure: */
344 /* data_loc_id NUMBER */
345 /* table_name VARCHAR2(50) */
346 /* column_name VARCHAR2(50) */
347 /* base_table_name VARCHAR2(50) */
348 /* base_column_name VARCHAR2(50) */
349 /* xref_category_id NUMBER */
350 /* xref_key1_source_column VARCHAR2(50) */
351 /* xref_key2_source_column VARCHAR2(50) */
352 /* xref_key3_source_column VARCHAR2(50) */
353 /* xref_key4_source_column VARCHAR2(50) */
354 /* xref_key5_source_column VARCHAR2(50) */
355 /* data_type VARCHAR2(50) */
356 /* data_length NUMBER */
357 /* int_val VARCHAR2(400) */
358 /* ext_val1 VARCHAR2(80) */
359 /* ext_val2 VARCHAR2(80) */
360 /* ext_val3 VARCHAR2(80) */
361 /* ext_val4 VARCHAR2(80) */
362 /* ext_val5 VARCHAR2(80) */
363
364 /* Acronyms used for variables */
365 /* ORD: Order Level */
366 /* OAC: Order Charges Level */
367 /* OTX: Order Text Level */
368 /* DTL: Line Level */
369 /* DAC: Line Charges Level */
370 /* DTX: Line Text Level */
371 /* ALL: Line Allocations Level */
372
373 v_ORD_Table ece_flatfile_pvt.Interface_tbl_type;
374 v_OAC_Table ece_flatfile_pvt.Interface_tbl_type;
375 v_OTX_Table ece_flatfile_pvt.Interface_tbl_type;
376 v_DTL_Table ece_flatfile_pvt.Interface_tbl_type;
377 v_DAC_Table ece_flatfile_pvt.Interface_tbl_type;
378 v_DTX_Table ece_flatfile_pvt.Interface_tbl_type;
379 v_ALL_Table ece_flatfile_pvt.Interface_tbl_type;
380 v_CrossRefTable ece_flatfile_pvt.Interface_tbl_type;
381
382 v_ORD_Cursor INTEGER;
383 v_OAC_Cursor INTEGER;
384 v_OTX_Cursor INTEGER;
385 v_DTL_Cursor INTEGER;
386 v_DAC_Cursor INTEGER;
387 v_DTX_Cursor INTEGER;
388 v_ALL_Cursor INTEGER;
389
390 v_ORD_Select VARCHAR2(32000);
391 v_OAC_Select VARCHAR2(32000);
392 v_OTX_Select VARCHAR2(32000);
393 v_DTL_Select VARCHAR2(32000);
394 v_DAC_Select VARCHAR2(32000);
395 v_DTX_Select VARCHAR2(32000);
396 v_ALL_Select VARCHAR2(32000);
397
398 v_ORD_From VARCHAR2(32000);
399 v_OAC_From VARCHAR2(32000);
400 v_OTX_From VARCHAR2(32000);
401 v_DTL_From VARCHAR2(32000);
402 v_DAC_From VARCHAR2(32000);
403 v_DTX_From VARCHAR2(32000);
404 v_ALL_From VARCHAR2(32000);
405
406 v_ORD_Where VARCHAR2(32000);
407 v_OAC_Where VARCHAR2(32000);
408 v_OTX_Where VARCHAR2(32000);
409 v_DTL_Where VARCHAR2(32000);
410 v_DAC_Where VARCHAR2(32000);
411 v_DTX_Where VARCHAR2(32000);
412 v_ALL_Where VARCHAR2(32000);
413
414 v_ORD_Count INTEGER := 0;
415 v_OAC_Count INTEGER := 0;
416 v_OTX_Count INTEGER := 0;
417 v_DTL_Count INTEGER := 0;
418 v_DAC_Count INTEGER := 0;
419 v_DTX_Count INTEGER := 0;
420 v_ALL_Count INTEGER := 0;
421
422 v_ORD_Key NUMBER;
423 v_OAC_Key NUMBER;
424 v_OTX_Key NUMBER;
425 v_DTL_Key NUMBER;
426 v_DAC_Key NUMBER;
427 v_DTX_Key NUMBER;
428 v_ALL_Key NUMBER;
429
430 v_CrossRefCount INTEGER := 0;
431 xProgress VARCHAR2(30);
432
433 v_Dummy INTEGER;
434 v_Orgn_Code VARCHAR2(4);
435 v_Order_No_From VARCHAR2(32);
436 v_Order_No_To VARCHAR2(32);
437 v_Creation_Date_From DATE;
438 v_Creation_Date_To DATE;
439 v_Customer_Name VARCHAR2(50);
440 v_TimeStampSequence INTEGER;
441 v_RunIDPosition INTEGER;
442 v_TimeStampPosition INTEGER;
443 v_TransactionRefKeyPos INTEGER;
444 v_TimeStampDate DATE;
445 v_ReturnStatus VARCHAR2(10);
446 v_MessageCount NUMBER;
447 v_MessageData VARCHAR2(255);
448 v_OutputLevel VARCHAR2(30);
449
450 v_Order_Id_Position INTEGER;
451 v_Order_Id INTEGER;
452 v_Line_Id_Position INTEGER;
453 v_Line_Id INTEGER;
454 v_assignment_type NUMBER;
455 v_format_size NUMBER;
456 v_pad_char VARCHAR2(1);
457
458 BEGIN
459
460 /* */
461 /* Load each PL/SQL table. The FOR loop implicitly handles all */
462 /* cursor processing. */
463 /* */
464 ec_debug.push ( 'GML_GPOAO.Populate_Interface_Tables' );
465 ec_debug.pl ( 3, 'p_Orgn_Code : ',p_Orgn_Code );
466 ec_debug.pl ( 3, 'p_Order_No_From : ',p_Order_No_From );
467
468 v_Orgn_Code := p_Orgn_Code;
469 v_Order_No_From := p_Order_No_From;
470 v_Order_No_To := p_Order_No_To;
471 v_Creation_Date_From := p_Creation_Date_From;
472 v_Creation_Date_To := p_Creation_Date_To;
473 v_Customer_Name := p_Customer_Name;
474
475 xProgress := 'GPOAOB-10-0010';
476 ec_debug.pl ( 3, 'v_Order_No_From : ',v_Order_No_From );
477 /* Get doc numbering info to properlly format doc numbers entered */
478 SELECT
479 assignment_type,
480 format_size,
481 nvl(pad_char,' ')
482 INTO
483 v_assignment_type,
484 v_format_size,
485 v_pad_char
486 FROM
487 sy_docs_seq
488 WHERE
489 orgn_code=v_Orgn_Code AND
490 doc_type='OPSO'
491 ;
492
493 ec_debug.pl ( 3, 'v_assignment_type: ',v_assignment_type);
494 If ( v_assignment_type = 2 ) Then /* If automatic document numbering */
495 If ( p_Order_No_From is NOT NULL ) Then
496 v_Order_No_From := lpad(p_Order_No_From, v_format_size, v_pad_char);
497 ec_debug.pl ( 3, 'v_Order_No_From : ',v_Order_No_From );
498 SELECT
499 lpad(p_Order_No_From, v_format_size, v_pad_char)
500 INTO
501 v_Order_No_From
502 FROM
503 dual
504 ;
505 End If;
506 If ( p_Order_No_To is NOT NULL ) Then
507 v_Order_No_To := lpad(p_Order_No_To, v_format_size, v_pad_char);
508 ec_debug.pl ( 3, 'v_Order_No_To : ',v_Order_No_To );
509 SELECT
510 lpad(p_Order_No_To, v_format_size, v_pad_char)
511 INTO
512 v_Order_No_To
513 FROM
514 dual
515 ;
516 End If;
517 End If;
518
519
520 ec_debug.pl ( 3, 'v_Order_No_From : ',v_Order_No_From );
521 ec_debug.pl ( 3, 'v_Order_No_To : ',v_Order_No_To );
522
523 /* */
524 /* Initialize the Cross Reference PL/SQL table. This table is a */
525 /* concatenation of all the interface PL/SQL tables. */
526 /* */
527
528 /* ********************************************************* */
529 /* ** Order Level ** */
530 /* ********************************************************* */
531
532 xProgress := 'GPOAOB-10-1000';
533 ece_flatfile_pvt.init_table(p_TransactionType,p_ORD_Interface,NULL,FALSE,v_ORD_Table,v_CrossRefTable);
534
535 v_CrossRefTable := v_ORD_Table;
536 xProgress := 'GPOAOB-10-1020';
537 v_CrossRefCount := v_ORD_Table.COUNT;
538
539
540 /* ********************************************************* */
541 /* ** Order Charges Level ** */
542 /* ********************************************************* */
543
544 xProgress := 'GPOAOB-10-1030';
545 ece_flatfile_pvt.init_table(p_TransactionType,p_OAC_Interface,NULL,TRUE,v_OAC_Table,v_CrossRefTable);
546
547
548 /* ********************************************************* */
549 /* ** Order Text Level ** */
550 /* ********************************************************* */
551
552 xProgress := 'GPOAOB-10-1060';
553 ece_flatfile_pvt.init_table(p_TransactionType,p_OTX_Interface,NULL,TRUE,v_OTX_Table,v_CrossRefTable);
554
555 /* ********************************************************* */
556 /* ** Line Level ** */
557 /* ********************************************************* */
558
559 xProgress := 'GPOAOB-10-1090';
560 ece_flatfile_pvt.init_table(p_TransactionType,p_DTL_Interface,NULL,TRUE,v_DTL_Table,v_CrossRefTable);
561
562
563 /* ********************************************************* */
564 /* ** Line Charges Level ** */
565 /* ********************************************************* */
566
567 xProgress := 'GPOAOB-10-1120';
568 ece_flatfile_pvt.init_table(p_TransactionType,p_DAC_Interface,NULL,TRUE,v_DAC_Table,v_CrossRefTable);
569
570
571 /* ********************************************************* */
572 /* ** Line Text Level ** */
573 /* ********************************************************* */
574
575 xProgress := 'GPOAOB-10-1150';
576 ece_flatfile_pvt.init_table(p_TransactionType,p_DTX_Interface,NULL,TRUE,v_DTX_Table,v_CrossRefTable);
577
578 /* ********************************************************* */
579 /* ** Line Allocations Level ** */
580 /* ********************************************************* */
581
582 xProgress := 'GPOAOB-10-1180';
583 ece_flatfile_pvt.init_table(p_TransactionType,p_ALL_Interface,NULL,TRUE,v_ALL_Table,v_CrossRefTable);
584
585 /* */
586 /* The 'select_clause' procedure will build the SELECT, FROM and WHERE */
587 /* clauses in preparation for the dynamic SQL call using the EDI data */
588 /* dictionary for the build. Any necessary customizations to these */
589 /* clauses need to be made *after* the clause is built, but *before* */
590 /* the SQL call. */
591 /* */
592
593 xProgress := 'GPOAOB-10-1210';
594 ece_extract_utils_pub.select_clause ( p_TransactionType,
595 p_CommunicationMethod,
596 p_ORD_Interface,
597 v_ORD_Table,
598 v_ORD_Select,
599 v_ORD_From,
600 v_ORD_Where );
601
602 xProgress := 'GPOAOB-10-1220';
603 ece_extract_utils_pub.select_clause ( p_TransactionType,
604 p_CommunicationMethod,
605 p_OAC_Interface,
606 v_OAC_Table,
607 v_OAC_Select,
608 v_OAC_From,
609 v_OAC_Where );
610
611 xProgress := 'GPOAOB-10-1230';
612 ece_extract_utils_pub.select_clause ( p_TransactionType,
613 p_CommunicationMethod,
614 p_OTX_Interface,
615 v_OTX_Table,
616 v_OTX_Select,
617 v_OTX_From,
618 v_OTX_Where );
619
620 xProgress := 'GPOAOB-10-1240';
621 ece_extract_utils_pub.select_clause ( p_TransactionType,
622 p_CommunicationMethod,
623 p_DTL_Interface,
624 v_DTL_Table,
625 v_DTL_Select,
626 v_DTL_From,
627 v_DTL_Where );
628
629 xProgress := 'GPOAOB-10-1250';
630 ece_extract_utils_pub.select_clause ( p_TransactionType,
631 p_CommunicationMethod,
632 p_DAC_Interface,
633 v_DAC_Table,
634 v_DAC_Select,
635 v_DAC_From,
636 v_DAC_Where );
637
638 xProgress := 'GPOAOB-10-1260';
639 ece_extract_utils_pub.select_clause ( p_TransactionType,
640 p_CommunicationMethod,
641 p_DTX_Interface,
642 v_DTX_Table,
643 v_DTX_Select,
644 v_DTX_From,
645 v_DTX_Where );
646
647 xProgress := 'GPOAOB-10-1270';
648 ece_extract_utils_pub.select_clause ( p_TransactionType,
649 p_CommunicationMethod,
650 p_ALL_Interface,
651 v_ALL_Table,
652 v_ALL_Select,
653 v_ALL_From,
654 v_ALL_Where );
655
656 /* */
657 /* Customize the WHERE clauses to find the elegible Orders */
658 /* */
659
660 /* ********************************************************* */
661 /* ** Order Level Where Clause ** */
662 /* ********************************************************* */
663
664 xProgress := 'GPOAOB-10-1280';
665 /* orgn code is required, so start with it */
666 v_ORD_Where := v_ORD_Where || p_ORD_Interface || '_V.ORGN_CODE = :Orgn_Code';
667
668 If v_Order_No_From is not NULL Then
669 If v_Order_No_To is not NULL Then
670 /* Specify range in where clause */
671 v_ORD_Where := v_ORD_Where || ' AND ' ||
672 p_ORD_Interface || '_V.ORDER_NO >= :Order_No_From' || ' AND ' ||
673 p_ORD_Interface || '_V.ORDER_NO <= :Order_No_To';
674 Else
675 /* Specify match */
676 v_ORD_Where := v_ORD_Where || ' AND ' ||
677 p_ORD_Interface || '_V.ORDER_NO = :Order_No_From';
678 End If;
679 End If;
680
681 If v_Creation_Date_From is not NULL Then
682 If v_Creation_Date_To is not NULL Then
683 /* Specify range in where clause */
684 v_ORD_Where := v_ORD_Where || ' AND ' ||
685 p_ORD_Interface || '_V.CREATION_DATE >= :Creation_Date_From'
686 || ' AND ' || 'trunc(' ||
687 p_ORD_Interface || '_V.CREATION_DATE) <= :Creation_Date_To';
688 Else
689 /* Specify match */
690 v_ORD_Where := v_ORD_Where || ' AND ' || 'trunc( ' ||
691 p_ORD_Interface || '_V.CREATION_DATE ) = trunc(:Creation_Date_From)';
692 End If;
693 End If;
694
695 If v_Customer_Name is not NULL Then
696 /* Specify match */
697 v_ORD_Where := v_ORD_Where || ' AND ' ||
698 p_ORD_Interface || '_V.SHIPTO_CUST_NAME = :Customer_Name';
699 End If;
700
701 ec_debug.pl ( 3, 'v_ORD_Where: ',v_ORD_Where);
702
703 /* ********************************************************* */
704 /* ** Order Charges Level Where Clause ** */
705 /* ********************************************************* */
706
707 v_OAC_Where := v_OAC_Where ||
708 p_OAC_Interface || '_V.ORDER_ID = :Order_Id';
709
710 /* ********************************************************* */
711 /* ** Order Text Level Where Clause ** */
712 /* ********************************************************* */
713
714 v_OTX_Where := v_OTX_Where ||
715 p_OTX_Interface || '_V.ORDER_ID = :Order_Id' || ' AND ' ||
716 p_OTX_Interface || '_V.LINE_NO > 0';
717
718 /* ********************************************************* */
719 /* ** Line Level Where Clause ** */
720 /* ********************************************************* */
721
722 v_DTL_Where := v_DTL_Where ||
723 p_DTL_Interface || '_V.ORDER_ID = :Order_Id';
724
725 /* ********************************************************* */
726 /* ** Line Charges Level Where Clause ** */
727 /* ********************************************************* */
728
729 v_DAC_Where := v_DAC_Where ||
730 p_DAC_Interface || '_V.LINE_ID = :Line_Id';
731
732 /* ********************************************************* */
733 /* ** Line Text Level Where Clause ** */
734 /* ********************************************************* */
735
736 v_DTX_Where := v_DTX_Where ||
737 p_DTX_Interface || '_V.LINE_ID = :Line_Id' || ' AND ' ||
738 p_DTX_Interface || '_V.LINE_NO > 0';
739
740 /* ********************************************************* */
741 /* ** Line Allocations Level Where Clause ** */
742 /* ********************************************************* */
743
744 v_ALL_Where := v_ALL_Where ||
745 p_ALL_Interface || '_V.LINE_ID = :Line_Id';
746
747 /* */
748 /* Build the complete SELECT statement for each level. */
749 /* */
750
751 xProgress := 'GPOAOB-10-1400';
752 v_ORD_Select := v_ORD_Select ||
753 v_ORD_From ||
754 v_ORD_Where;
755 ec_debug.pl (3, 'v_ORD_Select:', v_ORD_Select);
756
757
758 v_OAC_Select := v_OAC_Select ||
759 v_OAC_From ||
760 v_OAC_Where;
761
762 v_OTX_Select := v_OTX_Select ||
763 v_OTX_From ||
764 v_OTX_Where;
765
766 v_DTL_Select := v_DTL_Select ||
767 v_DTL_From ||
768 v_DTL_Where;
769 ec_debug.pl (3, 'v_DTL_Select:', v_DTL_Select);
770
771 v_DAC_Select := v_DAC_Select ||
772 v_DAC_From ||
773 v_DAC_Where;
774 ec_debug.pl (3, 'v_DAC_Select:', v_DAC_Select);
775
776 v_DTX_Select := v_DTX_Select ||
777 v_DTX_From ||
778 v_DTX_Where;
779
780 v_ALL_Select := v_ALL_Select ||
781 v_ALL_From ||
782 v_ALL_Where;
783
784 /* */
785 /* Open a cursor for each of the SELECT calls. This tells the */
786 /* database to reserve space for the data returned by the SELECT */
787 /* statement. */
788 /* */
789
790 xProgress := 'GPOAOB-10-1410';
791 v_ORD_Cursor := dbms_sql.open_cursor;
792 xProgress := 'GPOAOB-10-1420';
793 v_OAC_Cursor := dbms_sql.open_cursor;
794 xProgress := 'GPOAOB-10-1430';
795 v_OTX_Cursor := dbms_sql.open_cursor;
796 xProgress := 'GPOAOB-10-1440';
797 v_DTL_Cursor := dbms_sql.open_cursor;
798 xProgress := 'GPOAOB-10-1450';
799 v_DAC_Cursor := dbms_sql.open_cursor;
800 xProgress := 'GPOAOB-10-1460';
801 v_DTX_Cursor := dbms_sql.open_cursor;
802 xProgress := 'GPOAOB-10-1470';
803 v_ALL_Cursor := dbms_sql.open_cursor;
804 xProgress := 'GPOAOB-10-1480';
805
806 /* */
807 /* Parse each SELECT statement so the database understands the */
808 /* command. */
809 /* */
810
811 xProgress := 'GPOAOB-10-1500';
812 dbms_sql.parse ( v_ORD_Cursor,
813 v_ORD_Select,
814 dbms_sql.native );
815 ec_debug.pl (3, 'v_ORD_Select:', v_ORD_Select);
816
817 xProgress := 'GPOAOB-10-1510';
818 dbms_sql.parse ( v_OAC_Cursor,
819 v_OAC_Select,
820 dbms_sql.native );
821
822 xProgress := 'GPOAOB-10-1520';
823 dbms_sql.parse ( v_OTX_Cursor,
824 v_OTX_Select,
825 dbms_sql.native );
826
827 xProgress := 'GPOAOB-10-1530';
828 dbms_sql.parse ( v_DTL_Cursor,
829 v_DTL_Select,
830 dbms_sql.native );
831
832 xProgress := 'GPOAOB-10-1540';
833 dbms_sql.parse ( v_DAC_Cursor,
834 v_DAC_Select,
835 dbms_sql.native );
836
837 xProgress := 'GPOAOB-10-1550';
838 dbms_sql.parse ( v_DTX_Cursor,
839 v_DTX_Select,
840 dbms_sql.native );
841
842 xProgress := 'GPOAOB-10-1560';
843 dbms_sql.parse ( v_ALL_Cursor,
844 v_ALL_Select,
845 dbms_sql.native );
846
847 /* */
848 /* Initialize all counters. */
849 /* */
850
851 xProgress := 'GPOAOB-10-1561';
852 v_ORD_Count := v_ORD_Table.COUNT;
853 ec_debug.pl ( 3, 'v_ORD_Count: ',v_ORD_Count);
854
855 xProgress := 'GPOAOB-10-1562';
856 v_OAC_Count := v_OAC_Table.COUNT;
857 ec_debug.pl ( 3, 'v_OAC_Count: ',v_OAC_Count);
858
859 xProgress := 'GPOAOB-10-1563';
860 v_OTX_Count := v_OTX_Table.COUNT;
861 ec_debug.pl ( 3, 'v_OTX_Count: ',v_OTX_Count);
862
863 xProgress := 'GPOAOB-10-1564';
864 v_DTL_Count := v_DTL_Table.COUNT;
865 ec_debug.pl ( 3, 'v_DTL_Count: ',v_DTL_Count);
866
867 xProgress := 'GPOAOB-10-1565';
868 v_DAC_Count := v_DAC_Table.COUNT;
869 ec_debug.pl ( 3, 'v_DAC_Count: ',v_DAC_Count);
870
871 xProgress := 'GPOAOB-10-1566';
872 v_DTX_Count := v_DTX_Table.COUNT;
873 ec_debug.pl ( 3, 'v_DTX_Count: ',v_DTX_Count);
874
875 xProgress := 'GPOAOB-10-1567';
876 v_ALL_Count := v_ALL_Table.COUNT;
877 ec_debug.pl ( 3, 'v_ALL_Count: ',v_ALL_Count);
878
879 /* */
880 /* Define the data type for every column in each SELECT statement */
881 /* so the database understands how to populate it. Using the */
882 /* K.I.S.S. principle, every data type will be converted to */
883 /* VARCHAR2. */
884 /* - */
885
886 xProgress := 'GPOAOB-10-1600';
887 FOR v_LoopCount IN 1..v_ORD_Count
888 LOOP
889 xProgress := 'GPOAOB-10-1605';
890 dbms_sql.define_column ( v_ORD_Cursor,
891 v_LoopCount,
892 v_ORD_Select,
893 ece_extract_utils_pub.G_MaxColWidth );
894 END LOOP;
895
896 xProgress := 'GPOAOB-10-1610';
897 FOR v_LoopCount IN 1..v_OAC_Count
898 LOOP
899 xProgress := 'GPOAOB-10-1615';
900 dbms_sql.define_column ( v_OAC_Cursor,
901 v_LoopCount,
902 v_OAC_Select,
903 ece_extract_utils_pub.G_MaxColWidth );
904 END LOOP;
905
906 xProgress := 'GPOAOB-10-1620';
907 FOR v_LoopCount IN 1..v_OTX_Count
908 LOOP
909 xProgress := 'GPOAOB-10-1625';
910 dbms_sql.define_column ( v_OTX_Cursor,
911 v_LoopCount,
912 v_OTX_Select,
913 ece_extract_utils_pub.G_MaxColWidth );
914 END LOOP;
915
916 xProgress := 'GPOAOB-10-1630';
917 FOR v_LoopCount IN 1..v_DTL_Count
918 LOOP
919 xProgress := 'GPOAOB-10-1635';
920 dbms_sql.define_column ( v_DTL_Cursor,
921 v_LoopCount,
922 v_DTL_Select,
923 ece_extract_utils_pub.G_MaxColWidth );
924 END LOOP;
925
926 xProgress := 'GPOAOB-10-1640';
927 FOR v_LoopCount IN 1..v_DAC_Count
928 LOOP
929 xProgress := 'GPOAOB-10-1645';
930 dbms_sql.define_column ( v_DAC_Cursor,
931 v_LoopCount,
932 v_DAC_Select,
933 ece_extract_utils_pub.G_MaxColWidth );
934 END LOOP;
935
936 xProgress := 'GPOAOB-10-1650';
937 FOR v_LoopCount IN 1..v_DTX_Count
938 LOOP
939 xProgress := 'GPOAOB-10-1655';
940 dbms_sql.define_column ( v_DTX_Cursor,
941 v_LoopCount,
942 v_DTX_Select,
943 ece_extract_utils_pub.G_MaxColWidth );
944 END LOOP;
945
946 xProgress := 'GPOAOB-10-1660';
947 FOR v_LoopCount IN 1..v_ALL_Count
948 LOOP
949 xProgress := 'GPOAOB-10-1665';
950 dbms_sql.define_column ( v_ALL_Cursor,
951 v_LoopCount,
952 v_ALL_Select,
953 ece_extract_utils_pub.G_MaxColWidth );
954 END LOOP;
955
956 /* */
957 /* Bind the variables in the Order level SELECT clause. */
958 /* */
959
960 xProgress := 'GPOAOB-10-1700';
961 dbms_sql.bind_variable ( v_ORD_Cursor,
962 'ORGN_CODE',
963 v_Orgn_Code );
964
965 If v_Order_No_From is not NULL Then
966 xProgress := 'GPOAOB-10-1701';
967 dbms_sql.bind_variable ( v_ORD_Cursor,
968 'Order_No_From',
969 v_Order_No_From );
970 If v_Order_No_To is not NULL Then
971 xProgress := 'GPOAOB-10-1702';
972 dbms_sql.bind_variable ( v_ORD_Cursor,
973 'Order_No_To',
974 v_Order_No_To );
975 End If;
976 End If;
977
978 If v_Creation_Date_From is not NULL Then
979 xProgress := 'GPOAOB-10-1703';
980 dbms_sql.bind_variable ( v_ORD_Cursor,
981 'Creation_Date_From',
982 v_Creation_Date_From );
983 If v_Creation_Date_To is not NULL Then
984 xProgress := 'GPOAOB-10-1704';
985 dbms_sql.bind_variable ( v_ORD_Cursor,
986 'Creation_Date_To',
987 v_Creation_Date_To );
988 End If;
989 End If;
990
991 If v_Customer_Name is not NULL Then
992 xProgress := 'GPOAOB-10-1705';
993 dbms_sql.bind_variable ( v_ORD_Cursor,
994 'Customer_Name',
995 v_Customer_Name );
996 End If;
997
998 /* */
999 /* Execute the Order level SELECT statement. */
1000 /* */
1001
1002 xProgress := 'GPOAOB-10-1710';
1003 v_Dummy := dbms_sql.execute ( v_ORD_Cursor );
1004
1005 /* */
1006 /* Begin the Order level loop. */
1007 /* */
1008
1009 xProgress := 'GPOAOB-10-1720';
1010 WHILE dbms_sql.fetch_rows ( v_ORD_Cursor ) > 0
1011 LOOP
1012
1013 /* */
1014 /* Store the returned values in the PL/SQL table. */
1015 /* */
1016
1017 xProgress := 'GPOAOB-10-1730';
1018 FOR v_LoopCount IN 1..v_ORD_Count
1019 LOOP
1020 xProgress := 'GPOAOB-10-1740';
1021 dbms_sql.column_value ( v_ORD_Cursor,
1022 v_LoopCount,
1023 v_ORD_Table(v_LoopCount).value );
1024 END LOOP;
1025
1026 /* */
1027 /* Find the column position of the Order_ID in the PL/SQL table */
1028 /* and use the value stored in that column to bind the variables in */
1029 /* the SELECT clauses of the other levels. */
1030 /* */
1031
1032 xProgress := 'GPOAOB-10-1750';
1033 ece_extract_utils_pub.find_pos ( v_ORD_Table,
1034 'ORDER_ID',
1035 v_Order_Id_Position );
1036
1037 /*
1038
1039 Everything is stored in the PL/SQL table as VARCHAR2, so convert
1040 the Order_ID value to NUMBER.
1041
1042 */
1043
1044 xProgress := 'GPOAOB-10-1752';
1045 v_Order_Id := TO_NUMBER ( v_ORD_Table(v_Order_Id_Position).value );
1046 ec_debug.pl( 3, 'v_Order_Id:', v_Order_Id);
1047
1048 /* */
1049 /* Cross-reference all necessary columns in the PL/SQL table. */
1050 /* */
1051
1052 xProgress := 'GPOAOB-10-1800';
1053 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1054 p_Return_Status => v_ReturnStatus,
1055 p_Msg_Count => v_MessageCount,
1056 p_Msg_Data => v_MessageData,
1057 p_Key_Tbl => v_CrossRefTable,
1058 p_Tbl => v_ORD_Table );
1059
1060 /* */
1061 /* Retrieve the next sequence number for the primary key value, and */
1062 /* insert this record into the Order interface table. */
1063 /* */
1064
1065 xProgress := 'GPOAOB-10-1810';
1066 SELECT GML_GPOAO_ORDERS_S.nextval
1067 INTO v_ORD_Key
1068 FROM sys.dual;
1069
1070 xProgress := 'GPOAOB-10-1820';
1071 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1072 p_TransactionType,
1073 p_CommunicationMethod,
1074 p_ORD_Interface,
1075 v_ORD_Table,
1076 v_ORD_Key );
1077
1078 /* */
1079 /* Call the (customizable) procedure to populate the corresponding */
1080 /* extension table. */
1081 /* */
1082
1083 xProgress := 'GPOAOB-10-1830';
1084 GML_GPOAO_X.populate_ORD_ext ( v_ORD_Key,
1085 v_ORD_Table );
1086
1087 /* */
1088 /* Execute the Order Charges level SELECT statement. */
1089 /* */
1090
1091 xProgress := 'GPOAOB-10-1754';
1092 dbms_sql.bind_variable ( v_OAC_Cursor,
1093 'ORDER_ID',
1094 v_Order_ID );
1095
1096 xProgress := 'GPOAOB-10-1840';
1097 v_Dummy := dbms_sql.execute ( v_OAC_Cursor );
1098
1099 /* */
1100 /* Begin the Order Charges level loop. */
1101 /* */
1102
1103 xProgress := 'GPOAOB-10-1850';
1104 WHILE dbms_sql.fetch_rows ( v_OAC_Cursor ) > 0
1105 LOOP
1106
1107 /* */
1108 /* Store the returned values in the PL/SQL table. */
1109 /* */
1110
1111 xProgress := 'GPOAOB-10-1860';
1112 FOR v_LoopCount IN 1..v_OAC_Count
1113 LOOP
1114 xProgress := 'GPOAOB-10-1870';
1115 dbms_sql.column_value ( v_OAC_Cursor,
1116 v_LoopCount,
1117 v_OAC_Table(v_LoopCount).value );
1118 END LOOP;
1119
1120 /* */
1121 /* Cross-reference all necessary columns in the PL/SQL table. */
1122 /* */
1123
1124 xProgress := 'GPOAOB-10-1880';
1125 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1126 p_Return_Status => v_ReturnStatus,
1127 p_Msg_Count => v_MessageCount,
1128 p_Msg_Data => v_MessageData,
1129 p_Key_Tbl => v_CrossRefTable,
1130 p_Tbl => v_OAC_Table );
1131
1132 /* */
1133 /* Since this interface table is a logical extension of the Order */
1134 /* level table, use the same key value to insert this record into */
1135 /* the Order Charges table. */
1136 /* */
1137
1138 v_OAC_Key := v_ORD_Key;
1139
1140 xProgress := 'GPOAOB-10-1890';
1141 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1142 p_TransactionType,
1143 p_CommunicationMethod,
1144 p_OAC_Interface,
1145 v_OAC_Table,
1146 v_OAC_Key );
1147
1148 /* */
1149 /* Call the (customizable) procedure to populate the corresponding */
1150 /* extension table. */
1151 /* */
1152
1153 xProgress := 'GPOAOB-10-1890';
1154 GML_GPOAO_X.populate_OAC_ext ( v_OAC_Key,
1155 v_OAC_Table );
1156
1157 END LOOP; /* while oac */
1158
1159 /* */
1160 /* Execute the Order Text level SELECT statement. */
1161 /* */
1162
1163 xProgress := 'GPOAOB-10-1670';
1164 dbms_sql.bind_variable ( v_OTX_Cursor,
1165 'ORDER_ID',
1166 v_Order_ID );
1167
1168 xProgress := 'GPOAOB-10-1900';
1169 v_Dummy := dbms_sql.execute ( v_OTX_Cursor );
1170
1171 /* */
1172 /* Begin the Order Text level loop. */
1173 /* */
1174
1175 xProgress := 'GPOAOB-10-1910';
1176 WHILE dbms_sql.fetch_rows ( v_OTX_Cursor ) > 0
1177 LOOP
1178
1179 /* */
1180 /* Store the returned values in the PL/SQL table. */
1181 /* */
1182
1183 xProgress := 'GPOAOB-10-1920';
1184 FOR v_LoopCount IN 1..v_OTX_Count
1185 LOOP
1186 xProgress := 'GPOAOB-10-1930';
1187 dbms_sql.column_value ( v_OTX_Cursor,
1188 v_LoopCount,
1189 v_OTX_Table(v_LoopCount).value );
1190 END LOOP;
1191
1192 /* */
1193 /* Cross-reference all necessary columns in the PL/SQL table. */
1194 /* */
1195
1196 xProgress := 'GPOAOB-10-1940';
1197 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1198 p_Return_Status => v_ReturnStatus,
1199 p_Msg_Count => v_MessageCount,
1200 p_Msg_Data => v_MessageData,
1201 p_Key_Tbl => v_CrossRefTable,
1202 p_Tbl => v_OTX_Table );
1203
1204 /* */
1205 /* Since this interface table is a logical extension of the Order */
1206 /* level table, use the same key value to insert this record into */
1207 /* the Order Text table. */
1208 /* */
1209
1210 v_OTX_Key := v_ORD_Key;
1211
1212 xProgress := 'GPOAOB-10-1950';
1213 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1214 p_TransactionType,
1215 p_CommunicationMethod,
1216 p_OTX_Interface,
1217 v_OTX_Table,
1218 v_OTX_Key );
1219
1220 /* */
1221 /* Call the (customizable) procedure to populate the corresponding */
1222 /* extension table. */
1223 /* */
1224
1225 xProgress := 'GPOAOB-10-1960';
1226 GML_GPOAO_X.populate_OTX_ext ( v_OTX_Key,
1227 v_OTX_Table );
1228
1229 END LOOP; /* while otx */
1230
1231 xProgress := 'GPOAOB-10-1970';
1232 dbms_sql.bind_variable ( v_DTL_Cursor,
1233 'ORDER_ID',
1234 v_Order_ID );
1235 /* */
1236 /* Execute the Detail level SELECT statement. */
1237 /* */
1238
1239 xProgress := 'GPOAOB-10-1980';
1240 v_Dummy := dbms_sql.execute ( v_DTL_Cursor );
1241
1242 /* */
1243 /* Begin the Detail level loop. */
1244 /* */
1245
1246 xProgress := 'GPOAOB-10-1990';
1247 WHILE dbms_sql.fetch_rows ( v_DTL_Cursor ) > 0
1248 LOOP
1249
1250 /* */
1251 /* Store the returned values in the PL/SQL table. */
1252 /* */
1253
1254 xProgress := 'GPOAOB-10-2000';
1255 FOR v_LoopCount IN 1..v_DTL_Count
1256 LOOP
1257 xProgress := 'GPOAOB-10-2010';
1258 dbms_sql.column_value ( v_DTL_Cursor,
1259 v_LoopCount,
1260 v_DTL_Table(v_LoopCount).value );
1261 END LOOP;
1262
1263 /* */
1264 /* Find the column position of the Line_ID in the PL/SQL table */
1265 /* and use the value stored in that column to bind the variables in */
1266 /* the SELECT clauses of the other levels. */
1267 /* */
1268
1269 xProgress := 'GPOAOB-10-2020';
1270 ece_extract_utils_pub.find_pos ( v_DTL_Table,
1271 'Line_ID',
1272 v_Line_Id_Position );
1273
1274 /*
1275 **
1276 ** Everything is stored in the PL/SQL table as VARCHAR2, so convert
1277 ** the Line_ID value to NUMBER.
1278 **
1279 */
1280
1281 xProgress := 'GPOAOB-10-2030';
1282 v_Line_Id := TO_NUMBER ( v_DTL_Table(v_Line_Id_Position).value );
1283 ec_debug.pl( 3, 'v_Line_Id:', v_Line_Id);
1284
1285 /* */
1286 /* Cross-reference all necessary columns in the PL/SQL table. */
1287 /* */
1288
1289 xProgress := 'GPOAOB-10-2060';
1290 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1291 p_Return_Status => v_ReturnStatus,
1292 p_Msg_Count => v_MessageCount,
1293 p_Msg_Data => v_MessageData,
1294 p_Key_Tbl => v_CrossRefTable,
1295 p_Tbl => v_DTL_Table );
1296
1297 /* */
1298 /* Retrieve the next sequence number for the primary key value, and */
1299 /* insert this record into the Detail interface table. */
1300 /* */
1301
1302 xProgress := 'GPOAOB-10-2070';
1303 SELECT GML_GPOAO_DETAILS_S.nextval
1304 INTO v_DTL_Key
1305 FROM sys.dual;
1306 ec_debug.pl( 3, 'V_DTL_Key:', v_DTL_Key);
1307
1308 xProgress := 'GPOAOB-10-2080';
1309 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1310 p_TransactionType,
1311 p_CommunicationMethod,
1312 p_DTL_Interface,
1313 v_DTL_Table,
1314 v_DTL_Key );
1315
1316 /* */
1317 /* Call the (customizable) procedure to populate the corresponding */
1318 /* extension table. */
1319 /* */
1320
1321 xProgress := 'GPOAOB-10-2090';
1322 GML_GPOAO_X.populate_DTL_ext ( v_DTL_Key,
1323 v_DTL_Table );
1324
1325 /* */
1326 /* Execute the Detail Charges level SELECT statement. */
1327 /* */
1328
1329 xProgress := 'GPOAOB-10-2040';
1330 dbms_sql.bind_variable ( v_DAC_Cursor,
1331 'LINE_ID',
1332 v_Line_ID );
1333
1334 xProgress := 'GPOAOB-10-2100';
1335 v_Dummy := dbms_sql.execute ( v_DAC_Cursor );
1336
1337 /* */
1338 /* Begin the Detail Charges level loop. */
1339 /* */
1340 xProgress := 'GPOAOB-10-2110';
1341 WHILE dbms_sql.fetch_rows ( v_DAC_Cursor ) > 0
1342 LOOP
1343 /* */
1344 /* Store the returned values in the PL/SQL table. */
1345 /* */
1346
1347 xProgress := 'GPOAOB-10-2120';
1348 FOR v_LoopCount IN 1..v_DAC_Count
1349 LOOP
1350 xProgress := 'GPOAOB-10-2130';
1351 dbms_sql.column_value ( v_DAC_Cursor,
1352 v_LoopCount,
1353 v_DAC_Table(v_LoopCount).value );
1354 END LOOP;
1355
1356 /* */
1357 /* Cross-reference all necessary columns in the PL/SQL table. */
1358 /* */
1359
1360 xProgress := 'GPOAOB-10-2130';
1361 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1362 p_Return_Status => v_ReturnStatus,
1363 p_Msg_Count => v_MessageCount,
1364 p_Msg_Data => v_MessageData,
1365 p_Key_Tbl => v_CrossRefTable,
1366 p_Tbl => v_DAC_Table );
1367
1368 /* */
1369 /* Since this interface table is a logical extension of the Detail */
1370 /* level table, use the same key value to insert this record into */
1371 /* the Order Charges table. */
1372 /* */
1373
1374 v_DAC_Key := v_DTL_Key;
1375
1376 xProgress := 'GPOAOB-10-2140';
1377 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1378 p_TransactionType,
1379 p_CommunicationMethod,
1380 p_DAC_Interface,
1381 v_DAC_Table,
1382 v_DAC_Key );
1383
1384 /* */
1385 /* Call the (customizable) procedure to populate the corresponding */
1386 /* extension table. */
1387 /* */
1388
1389 xProgress := 'GPOAOB-10-2150';
1390 GML_GPOAO_X.populate_DAC_ext ( v_DAC_Key, v_DAC_Table );
1391
1392 END LOOP; /* while dac */
1393
1394 /* */
1395 /* Execute the Detail Text level SELECT statement. */
1396 /* */
1397
1398 xProgress := 'GPOAOB-10-2160';
1399 dbms_sql.bind_variable ( v_DTX_Cursor,
1400 'LINE_ID',
1401 v_Line_ID );
1402
1403 xProgress := 'GPOAOB-10-2170';
1404 v_Dummy := dbms_sql.execute ( v_DTX_Cursor );
1405
1406 /* */
1407 /* Begin the Detail Text level loop. */
1408 /* */
1409
1410 xProgress := 'GPOAOB-10-2180';
1411 WHILE dbms_sql.fetch_rows ( v_DTX_Cursor ) > 0
1412 LOOP
1413
1414 /* */
1415 /* Store the returned values in the PL/SQL table. */
1416 /* */
1417
1418 xProgress := 'GPOAOB-10-2190';
1419 FOR v_LoopCount IN 1..v_DTX_Count
1420 LOOP
1421 xProgress := 'GPOAOB-10-2200';
1422 dbms_sql.column_value ( v_DTX_Cursor,
1423 v_LoopCount,
1424 v_DTX_Table(v_LoopCount).value );
1425 END LOOP;
1426
1427 /* */
1428 /* Cross-reference all necessary columns in the PL/SQL table. */
1429 /* */
1430
1431 xProgress := 'GPOAOB-10-2210';
1432 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1433 p_Return_Status => v_ReturnStatus,
1434 p_Msg_Count => v_MessageCount,
1435 p_Msg_Data => v_MessageData,
1436 p_Key_Tbl => v_CrossRefTable,
1437 p_Tbl => v_DTX_Table );
1438
1439 /* */
1440 /* Since this interface table is a logical extension of the Detail */
1441 /* level table, use the same key value to insert this record into */
1442 /* the Order Text table. */
1443 /* */
1444
1445 v_DTX_Key := v_DTL_Key;
1446
1447 xProgress := 'GPOAOB-10-2220';
1448 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1449 p_TransactionType,
1450 p_CommunicationMethod,
1451 p_DTX_Interface,
1452 v_DTX_Table,
1453 v_DTX_Key );
1454
1455 /* */
1456 /* Call the (customizable) procedure to populate the corresponding */
1457 /* extension table. */
1458 /* */
1459
1460 xProgress := 'GPOAOB-10-2230';
1461 GML_GPOAO_X.populate_DTX_ext ( v_DTX_Key, v_DTX_Table );
1462 END LOOP; /* while dtx */
1463
1464 /* */
1465 /* Execute the Allocations level SELECT statement. */
1466 /* */
1467
1468 xProgress := 'GPOAOB-10-2160';
1469 dbms_sql.bind_variable ( v_ALL_Cursor,
1470 'LINE_ID',
1471 v_Line_ID );
1472
1473 xProgress := 'GPOAOB-10-2170';
1474 v_Dummy := dbms_sql.execute ( v_ALL_Cursor );
1475
1476 /* */
1477 /* Begin the Allocations level loop. */
1478 /* */
1479 xProgress := 'GPOAOB-10-2180';
1480 WHILE dbms_sql.fetch_rows ( v_ALL_Cursor ) > 0
1481 LOOP
1482
1483 /* */
1484 /* Store the returned values in the PL/SQL table. */
1485 /* */
1486
1487 xProgress := 'GPOAOB-10-2190';
1488 FOR v_LoopCount IN 1..v_ALL_Count
1489 LOOP
1490 xProgress := 'GPOAOB-10-2200';
1491 dbms_sql.column_value ( v_ALL_Cursor,
1492 v_LoopCount,
1493 v_ALL_Table(v_LoopCount).value );
1494 END LOOP;
1495
1496 /* */
1497 /* Cross-reference all necessary columns in the PL/SQL table. */
1498 /* */
1499
1500 xProgress := 'GPOAOB-10-2210';
1501 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1502 p_Return_Status => v_ReturnStatus,
1503 p_Msg_Count => v_MessageCount,
1504 p_Msg_Data => v_MessageData,
1505 p_Key_Tbl => v_CrossRefTable,
1506 p_Tbl => v_ALL_Table );
1507
1508 /* */
1509 /* Since this interface table is a logical extension of the Allocations */
1510 /* level table, use the same key value to insert this record into */
1511 /* the Order Text table. */
1512 /* */
1513
1514 v_ALL_Key := v_DTL_Key;
1515
1516 xProgress := 'GPOAOB-10-2220';
1517 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1518 p_TransactionType,
1519 p_CommunicationMethod,
1520 p_ALL_Interface,
1521 v_ALL_Table,
1522 v_ALL_Key );
1523
1524 /* */
1525 /* Call the (customizable) procedure to populate the corresponding */
1526 /* extension table. */
1527 /* */
1528
1529 xProgress := 'GPOAOB-10-2230';
1530 GML_GPOAO_X.populate_ALL_ext ( v_ALL_Key, v_ALL_Table );
1531
1532 END LOOP; /* while all */
1533
1534 END LOOP; /* while dtl */
1535 /* */
1536 ec_debug.pl(3, 'Exported order_id: ' , v_order_id);
1537 /* */
1538 /* update edi count in the op_ordr_hdr table */
1539 /* */
1540 xProgress := 'GPOAOB-10-2160';
1541 UPDATE
1542 op_ordr_hdr
1543 SET
1544 edi_trans_count = edi_trans_count+1,
1545 last_update_date = sysdate,
1546 last_updated_by = fnd_global.user_id
1547 WHERE
1548 order_id = v_order_id;
1549
1550 /* */
1551 END LOOP; /* while ord */
1552
1553 /* Commit the interface table inserts. */
1554 /* */
1555
1556
1557 xProgress := 'GASNOB-10-2300';
1558 ec_debug.pop ( 'GML_GPOAO.Populate_Interface_Tables' );
1559
1560 COMMIT;
1561
1562 /* */
1563 /* Close all open cursors. */
1564 /* */
1565
1566 xProgress := 'GPOAOB-10-2310';
1567 dbms_sql.close_cursor ( v_ORD_Cursor );
1568 xProgress := 'GPOAOB-10-2310';
1569 dbms_sql.close_cursor ( v_OAC_Cursor );
1570 xProgress := 'GPOAOB-10-2310';
1571 dbms_sql.close_cursor ( v_OTX_Cursor );
1572 xProgress := 'GPOAOB-10-2310';
1573 dbms_sql.close_cursor ( v_DTL_Cursor );
1574 xProgress := 'GPOAOB-10-2310';
1575 dbms_sql.close_cursor ( v_DAC_Cursor );
1576 xProgress := 'GPOAOB-10-2310';
1577 dbms_sql.close_cursor ( v_DTX_Cursor );
1578 xProgress := 'GPOAOB-10-2310';
1579 dbms_sql.close_cursor ( v_ALL_Cursor );
1580
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 ec_debug.pl ( 0,
1584 'EC',
1585 'ECE_PROGRAM_ERROR',
1586 'PROGRESS_LEVEL',
1587 xProgress );
1588
1589 ec_debug.pl ( 0,
1590 'EC',
1591 'ECE_ERROR_MESSAGE',
1592 'ERROR_MESSAGE',
1593 SQLERRM );
1594
1595 app_exception.raise_exception;
1596
1597 END Populate_Interface_Tables;
1598
1599
1600
1601 /*===========================================================================
1602
1603 PROCEDURE NAME: Put_Data_To_Output_Table
1604
1605 PURPOSE: This procedure extracts and sequences information from
1606 the Gateway interface tables and inserts the sequenced
1607 data into the Gateway output table.
1608
1609 ===========================================================================*/
1610
1611 PROCEDURE Put_Data_To_Output_Table ( p_CommunicationMethod IN VARCHAR2,
1612 p_TransactionType IN VARCHAR2,
1613 p_Orgn_Code IN VARCHAR2,
1614 p_Order_No_From IN VARCHAR2,
1615 p_Order_No_To IN VARCHAR2,
1616 p_Creation_Date_From IN DATE,
1617 p_Creation_Date_To IN DATE,
1618 p_Customer_Name IN VARCHAR2,
1619 p_RunID IN INTEGER,
1620 p_OutputWidth IN INTEGER,
1621 p_ORD_Interface IN VARCHAR2,
1622 p_OAC_Interface IN VARCHAR2,
1623 p_OTX_Interface IN VARCHAR2,
1624 p_DTL_Interface IN VARCHAR2,
1625 p_DAC_Interface IN VARCHAR2,
1626 p_DTX_Interface IN VARCHAR2,
1627 p_ALL_Interface IN VARCHAR2 )
1628
1629 IS
1630
1631 /* */
1632 /* Variable definitions. 'Interface_tbl_type' is a PL/SQL table */
1633 /* typedef with the following structure: */
1634 /* */
1635 /* table_name VARCHAR2(50) */
1636 /* column_name VARCHAR2(50) */
1637 /* record_num NUMBER */
1638 /* position NUMBER */
1639 /* data_type VARCHAR2(50) */
1640 /* data_length NUMBER */
1641 /* value VARCHAR2(400) */
1642 /* layout_code VARCHAR2(2) */
1643 /* record_qualifier VARCHAR2(3) */
1644 /* */
1645 xProgress VARCHAR2(30);
1646
1647
1648 /* Acronyms used for variables */
1649 /* ORD: Order Level */
1650 /* OAC: Order Charges Level */
1651 /* OTX: Order Text Level */
1652 /* DTL: Line Level */
1653 /* DAC: Line Charges Level */
1654 /* DTX: Line Text Level */
1655 /* ALL: Line Allocations Level */
1656
1657 v_ORD_Table ece_flatfile_pvt.Interface_tbl_type;
1658 v_OAC_Table ece_flatfile_pvt.Interface_tbl_type;
1659 v_OTX_Table ece_flatfile_pvt.Interface_tbl_type;
1660 v_DTL_Table ece_flatfile_pvt.Interface_tbl_type;
1661 v_DAC_Table ece_flatfile_pvt.Interface_tbl_type;
1662 v_DTX_Table ece_flatfile_pvt.Interface_tbl_type;
1663 v_ALL_Table ece_flatfile_pvt.Interface_tbl_type;
1664 v_CrossRefTable ece_flatfile_pvt.Interface_tbl_type;
1665
1666 v_ORD_Select_Cursor INTEGER;
1667 v_OAC_Select_Cursor INTEGER;
1668 v_OTX_Select_Cursor INTEGER;
1669 v_DTL_Select_Cursor INTEGER;
1670 v_DAC_Select_Cursor INTEGER;
1671 v_DTX_Select_Cursor INTEGER;
1672 v_ALL_Select_Cursor INTEGER;
1673
1674 v_ORD_Delete_Cursor INTEGER;
1675 v_OAC_Delete_Cursor INTEGER;
1676 v_OTX_Delete_Cursor INTEGER;
1677 v_DTL_Delete_Cursor INTEGER;
1678 v_DAC_Delete_Cursor INTEGER;
1679 v_DTX_Delete_Cursor INTEGER;
1680 v_ALL_Delete_Cursor INTEGER;
1681
1682 v_ORD_Delete_XCursor INTEGER;
1683 v_OAC_Delete_XCursor INTEGER;
1684 v_OTX_Delete_XCursor INTEGER;
1685 v_DTL_Delete_XCursor INTEGER;
1686 v_DAC_Delete_XCursor INTEGER;
1687 v_DTX_Delete_XCursor INTEGER;
1688 v_ALL_Delete_XCursor INTEGER;
1689
1690 v_ORD_Select VARCHAR2(32000);
1691 v_OAC_Select VARCHAR2(32000);
1692 v_OTX_Select VARCHAR2(32000);
1693 v_DTL_Select VARCHAR2(32000);
1694 v_DAC_Select VARCHAR2(32000);
1695 v_DTX_Select VARCHAR2(32000);
1696 v_ALL_Select VARCHAR2(32000);
1697
1698 v_ORD_From VARCHAR2(32000);
1699 v_OAC_From VARCHAR2(32000);
1700 v_OTX_From VARCHAR2(32000);
1701 v_DTL_From VARCHAR2(32000);
1702 v_DAC_From VARCHAR2(32000);
1703 v_DTX_From VARCHAR2(32000);
1704 v_ALL_From VARCHAR2(32000);
1705
1706 v_ORD_Where VARCHAR2(32000);
1707 v_OAC_Where VARCHAR2(32000);
1708 v_OTX_Where VARCHAR2(32000);
1709 v_DTL_Where VARCHAR2(32000);
1710 v_DAC_Where VARCHAR2(32000);
1711 v_DTX_Where VARCHAR2(32000);
1712 v_ALL_Where VARCHAR2(32000);
1713
1714 v_ORD_Delete VARCHAR2(32000);
1715 v_OAC_Delete VARCHAR2(32000);
1716 v_OTX_Delete VARCHAR2(32000);
1717 v_DTL_Delete VARCHAR2(32000);
1718 v_DAC_Delete VARCHAR2(32000);
1719 v_DTX_Delete VARCHAR2(32000);
1720 v_ALL_Delete VARCHAR2(32000);
1721
1722 v_ORD_XDelete VARCHAR2(32000);
1723 v_OAC_XDelete VARCHAR2(32000);
1724 v_OTX_XDelete VARCHAR2(32000);
1725 v_DTL_XDelete VARCHAR2(32000);
1726 v_DAC_XDelete VARCHAR2(32000);
1727 v_DTX_XDelete VARCHAR2(32000);
1728 v_ALL_XDelete VARCHAR2(32000);
1729
1730 v_ORD_Count INTEGER := 0;
1731 v_OAC_Count INTEGER := 0;
1732 v_OTX_Count INTEGER := 0;
1733 v_DTL_Count INTEGER := 0;
1734 v_DAC_Count INTEGER := 0;
1735 v_DTX_Count INTEGER := 0;
1736 v_ALL_Count INTEGER := 0;
1737
1738 v_ORD_RowID ROWID;
1739 v_OAC_RowID ROWID;
1740 v_OTX_RowID ROWID;
1741 v_DTL_RowID ROWID;
1742 v_DAC_RowID ROWID;
1743 v_DTX_RowID ROWID;
1744 v_ALL_RowID ROWID;
1745
1746 v_ORD_XRowID ROWID;
1747 v_OAC_XRowID ROWID;
1748 v_OTX_XRowID ROWID;
1749 v_DTL_XRowID ROWID;
1750 v_DAC_XRowID ROWID;
1751 v_DTX_XRowID ROWID;
1752 v_ALL_XRowID ROWID;
1753
1754 v_ORD_CommonKeyName VARCHAR2(40);
1755 v_OAC_CommonKeyName VARCHAR2(40);
1756 v_OTX_CommonKeyName VARCHAR2(40);
1757 v_DTL_CommonKeyName VARCHAR2(40);
1758 v_DAC_CommonKeyName VARCHAR2(40);
1759 v_DTX_CommonKeyName VARCHAR2(40);
1760 v_ALL_CommonKeyName VARCHAR2(40);
1761
1762 v_KeyPad VARCHAR2(22) := RPAD(' ', 22);
1763 v_FileCommonKey VARCHAR2(255);
1764 v_TranslatorCode VARCHAR2(30);
1765 v_RecordCommonKey0 VARCHAR2(25);
1766 v_RecordCommonKey1 VARCHAR2(22);
1767 v_RecordCommonKey2 VARCHAR2(22);
1768 v_RecordCommonKey3 VARCHAR2(22);
1769
1770 v_ORD_XInterface VARCHAR2(50);
1771 v_OAC_XInterface VARCHAR2(50);
1772 v_OTX_XInterface VARCHAR2(50);
1773 v_DTL_XInterface VARCHAR2(50);
1774 v_DAC_XInterface VARCHAR2(50);
1775 v_DTX_XInterface VARCHAR2(50);
1776 v_ALL_XInterface VARCHAR2(50);
1777
1778 v_ORD_CKNamePosition INTEGER;
1779 v_OAC_CKNamePosition INTEGER;
1780 v_OTX_CKNamePosition INTEGER;
1781 v_DTL_CKNamePosition INTEGER;
1782 v_DAC_CKNamePosition INTEGER;
1783 v_DTX_CKNamePosition INTEGER;
1784 v_ALL_CKNamePosition INTEGER;
1785
1786 v_Dummy INTEGER;
1787 v_Order_ID INTEGER;
1788 v_Order_Id_Position INTEGER;
1789 v_Line_ID INTEGER;
1790 v_Line_Id_Position INTEGER;
1791
1792 v_TranslatorCodePosition INTEGER;
1793 v_DeliveryCKNamePosition INTEGER;
1794 v_AllowChgCKNamePosition INTEGER;
1795 v_ContainerCKNamePosition INTEGER;
1796 v_OrderCKNamePosition INTEGER;
1797 v_ItemCKNamePosition INTEGER;
1798 v_ItemDetailCKNamePosition INTEGER;
1799 v_DeliveryIDPosition INTEGER;
1800 v_TransactionRecordIDPosition INTEGER;
1801 v_OrderHeaderIDPosition INTEGER;
1802 v_ItemIDPosition INTEGER;
1803 v_PickingLineIDPosition INTEGER;
1804 v_ContainerIDPosition INTEGER;
1805 v_SequenceNumberPosition INTEGER;
1806
1807 v_DeliveryID INTEGER;
1808 v_TransactionRecordID INTEGER;
1809 v_ContainerID INTEGER;
1810 v_OrderHeaderID INTEGER;
1811 v_ItemID INTEGER;
1812 v_PickingLineID INTEGER;
1813 v_SequenceNumber INTEGER;
1814
1815 BEGIN
1816
1817 /*
1818 **
1819 ** Debug statements for the parameter values.
1820 **
1821 */
1822
1823 ec_debug.push ( 'GML_GPOAO.Put_Data_To_Output_Table' );
1824 ec_debug.pl ( 3, 'p_CommunicationMethod: ', p_CommunicationMethod );
1825 ec_debug.pl ( 3, 'p_TransactionType: ', p_TransactionType );
1826 ec_debug.pl ( 3, 'p_Orgn_Code: ', p_Orgn_Code );
1827 ec_debug.pl ( 3, 'p_Order_No_From: ', p_Order_No_From );
1828 ec_debug.pl ( 3, 'p_Order_No_To: ', p_Order_No_To );
1829 ec_debug.pl ( 3, 'p_Creation_Date_From: ', p_Creation_Date_From );
1830 ec_debug.pl ( 3, 'p_Creation_Date_To: ', p_Creation_Date_To );
1831 ec_debug.pl ( 3, 'p_Customer_Name: ', p_Customer_Name );
1832 ec_debug.pl ( 3, 'p_RunID: ', p_RunID );
1833 ec_debug.pl ( 3, 'p_OutputWidth: ', p_OutputWidth );
1834 ec_debug.pl ( 3, 'p_ORD_Interface: ', p_ORD_Interface );
1835 ec_debug.pl ( 3, 'p_OAC_Interface: ', p_OAC_Interface );
1836 ec_debug.pl ( 3, 'p_OTX_Interface: ', p_OTX_Interface );
1837 ec_debug.pl ( 3, 'p_DTL_Interface: ', p_DTL_Interface );
1838 ec_debug.pl ( 3, 'p_DAC_Interface: ', p_DAC_Interface );
1839 ec_debug.pl ( 3, 'p_DTX_Interface: ', p_DTX_Interface );
1840 ec_debug.pl ( 3, 'p_ALL_Interface: ', p_ALL_Interface );
1841
1842 /* */
1843 /* */
1844 /* The 'select_clause' procedure will build the SELECT, FROM and WHERE */
1845 /* clauses in preparation for the dynamic SQL call using the EDI data */
1846 /* dictionary for the build. Any necessary customizations to these */
1847 /* the SQL call. */
1848 /* */
1849
1850 xProgress := 'GPOAOB-40-1010';
1851 ece_flatfile_pvt.select_clause ( p_TransactionType,
1852 p_CommunicationMethod,
1853 p_ORD_Interface,
1854 v_ORD_XInterface,
1855 v_ORD_Table,
1856 v_ORD_CommonKeyName,
1857 v_ORD_Select,
1858 v_ORD_From,
1859 v_ORD_Where );
1860
1861 xProgress := 'GPOAOB-40-1020';
1862 ece_flatfile_pvt.select_clause ( p_TransactionType,
1863 p_CommunicationMethod,
1864 p_OAC_Interface,
1865 v_OAC_XInterface,
1866 v_OAC_Table,
1867 v_OAC_CommonKeyName,
1868 v_OAC_Select,
1869 v_OAC_From,
1870 v_OAC_Where );
1871
1872 xProgress := 'GPOAOB-40-1030';
1873 ece_flatfile_pvt.select_clause ( p_TransactionType,
1874 p_CommunicationMethod,
1875 p_OTX_Interface,
1876 v_OTX_XInterface,
1877 v_OTX_Table,
1878 v_OTX_CommonKeyName,
1879 v_OTX_Select,
1880 v_OTX_From,
1881 v_OTX_Where );
1882
1883 xProgress := 'GPOAOB-40-1040';
1884 ece_flatfile_pvt.select_clause ( p_TransactionType,
1885 p_CommunicationMethod,
1886 p_DTL_Interface,
1887 v_DTL_XInterface,
1888 v_DTL_Table,
1889 v_DTL_CommonKeyName,
1890 v_DTL_Select,
1891 v_DTL_From,
1892 v_DTL_Where );
1893
1894 xProgress := 'GPOAOB-40-1050';
1895 ece_flatfile_pvt.select_clause ( p_TransactionType,
1896 p_CommunicationMethod,
1897 p_DAC_Interface,
1898 v_DAC_XInterface,
1899 v_DAC_Table,
1900 v_DAC_CommonKeyName,
1901 v_DAC_Select,
1902 v_DAC_From,
1903 v_DAC_Where );
1904
1905 xProgress := 'GPOAOB-40-1060';
1906 ece_flatfile_pvt.select_clause ( p_TransactionType,
1907 p_CommunicationMethod,
1908 p_DTX_Interface,
1909 v_DTX_XInterface,
1910 v_DTX_Table,
1911 v_DTX_CommonKeyName,
1912 v_DTX_Select,
1913 v_DTX_From,
1914 v_DTX_Where );
1915
1916 xProgress := 'GPOAOB-40-1070';
1917 ece_flatfile_pvt.select_clause ( p_TransactionType,
1918 p_CommunicationMethod,
1919 p_ALL_Interface,
1920 v_ALL_XInterface,
1921 v_ALL_Table,
1922 v_ALL_CommonKeyName,
1923 v_ALL_Select,
1924 v_ALL_From,
1925 v_ALL_Where );
1926
1927 /* */
1928 /* Customize the SELECT clauses to include the ROWID. Records */
1929 /* will be deleted from the interface tables using these values. */
1930 /* Also add any columns that do not appear in the flatfile, but */
1931 /* will be needed for internal processing (i.e. ID values). */
1932 /* */
1933
1934 xProgress := 'GPOAOB-40-1080';
1935 v_ORD_Select := v_ORD_Select ||
1936 ', ' ||
1937 p_ORD_Interface ||
1938 '.ROWID, ' ||
1939 v_ORD_XInterface ||
1940 '.ROWID, ' ||
1941 p_ORD_Interface ||
1942 '.TRANSACTION_RECORD_ID';
1943
1944 v_OAC_Select := v_OAC_Select ||
1945 ', ' ||
1946 p_OAC_Interface ||
1947 '.ROWID, ' ||
1948 v_OAC_XInterface ||
1949 '.ROWID, ' ||
1950 p_OAC_Interface ||
1951 '.TRANSACTION_RECORD_ID';
1952
1953 v_OTX_Select := v_OTX_Select ||
1954 ', ' ||
1955 p_OTX_Interface ||
1956 '.ROWID, ' ||
1957 v_OTX_XInterface ||
1958 '.ROWID, ' ||
1959 p_OTX_Interface ||
1960 '.TRANSACTION_RECORD_ID';
1961
1962 v_DTL_Select := v_DTL_Select ||
1963 ', ' ||
1964 p_DTL_Interface ||
1965 '.ROWID, ' ||
1966 v_DTL_XInterface ||
1967 '.ROWID, ' ||
1968 p_DTL_Interface ||
1969 '.TRANSACTION_RECORD_ID';
1970
1971 v_DAC_Select := v_DAC_Select ||
1972 ', ' ||
1973 p_DAC_Interface ||
1974 '.ROWID, ' ||
1975 v_DAC_XInterface ||
1976 '.ROWID, ' ||
1977 p_DAC_Interface ||
1978 '.TRANSACTION_RECORD_ID';
1979
1980 v_DTX_Select := v_DTX_Select ||
1981 ', ' ||
1982 p_DTX_Interface ||
1983 '.ROWID, ' ||
1984 v_DTX_XInterface ||
1985 '.ROWID, ' ||
1986 p_DTX_Interface ||
1987 '.TRANSACTION_RECORD_ID';
1988
1989 v_ALL_Select := v_ALL_Select ||
1990 ', ' ||
1991 p_ALL_Interface ||
1992 '.ROWID, ' ||
1993 v_ALL_XInterface ||
1994 '.ROWID, ' ||
1995 p_ALL_Interface ||
1996 '.TRANSACTION_RECORD_ID';
1997
1998 /* */
1999 /* Customize the WHERE clauses to: */
2000 /* */
2001
2002 xProgress := 'GPOAOB-40-1090';
2003 v_ORD_Where := v_ORD_Where || ' AND ' ||
2004 p_ORD_Interface || '.RUN_ID = :Run_ID' ||
2005 ' ORDER BY ' ||
2006 p_ORD_Interface || '.ORDER_NO';
2007
2008
2009 xProgress := 'GPOAOB-40-1091';
2010 v_OAC_Where := v_OAC_Where || ' AND ' ||
2011 p_OAC_Interface || '.RUN_ID = :Run_ID' || ' AND ' ||
2012 p_OAC_Interface || '.ORDER_ID = :ORDER_ID' ||
2013 ' ORDER BY ' ||
2014 p_OAC_Interface || '.SAC_CODE_INT';
2015
2016 xProgress := 'GPOAOB-40-1091';
2017 v_OTX_Where := v_OTX_Where || ' AND ' ||
2018 p_OTX_Interface || '.RUN_ID = :Run_ID' || ' AND ' ||
2019 p_OTX_Interface || '.ORDER_ID = :ORDER_ID' ||
2020 ' ORDER BY ' ||
2021 p_OTX_Interface || '.LINE_NO';
2022
2023 xProgress := 'GPOAOB-40-1095';
2024 v_DTL_Where := v_DTL_Where || ' AND ' ||
2025 p_DTL_Interface || '.RUN_ID = :Run_ID' || ' AND ' ||
2026 p_DTL_Interface || '.ORDER_ID = :ORDER_ID' ||
2027 ' ORDER BY ' ||
2028 p_DTL_Interface || '.SO_LINE_NO';
2029
2030 xProgress := 'GPOAOB-40-1096';
2031 v_DAC_Where := v_DAC_Where || ' AND ' ||
2032 p_DAC_Interface || '.RUN_ID = :Run_ID' ||
2033 ' AND ' ||
2034 p_DAC_Interface || '.LINE_ID = :LINE_ID' ||
2035 ' ORDER BY ' ||
2036 p_DAC_Interface || '.SAC_CODE_INT';
2037
2038 xProgress := 'GPOAOB-40-1097';
2039 v_DTX_Where := v_DTX_Where || ' AND ' ||
2040 p_DTX_Interface || '.RUN_ID = :Run_ID' ||
2041 ' AND ' ||
2042 p_DTX_Interface || '.LINE_ID = :LINE_ID' ||
2043 ' ORDER BY ' ||
2044 p_DTX_Interface || '.LINE_NO';
2045
2046 xProgress := 'GPOAOB-40-1098';
2047 v_ALL_Where := v_ALL_Where || ' AND ' ||
2048 p_ALL_Interface || '.RUN_ID = :Run_ID' ||
2049 ' AND ' ||
2050 p_ALL_Interface || '.LINE_ID = :LINE_ID' ||
2051 ' ORDER BY ' ||
2052 p_ALL_Interface || '.LOT_NO';
2053
2054 /* */
2055 /* Build the complete SELECT statement for each level. */
2056 /* */
2057
2058 xProgress := 'GPOAOB-40-1100';
2059 v_ORD_Select := v_ORD_Select ||
2060 v_ORD_From ||
2061 v_ORD_Where ||
2062 ' FOR UPDATE';
2063
2064 v_OAC_Select := v_OAC_Select ||
2065 v_OAC_From ||
2066 v_OAC_Where ||
2067 ' FOR UPDATE';
2068
2069 v_OTX_Select := v_OTX_Select ||
2070 v_OTX_From ||
2071 v_OTX_Where ||
2072 ' FOR UPDATE';
2073
2074 v_DTL_Select := v_DTL_Select ||
2075 v_DTL_From ||
2076 v_DTL_Where ||
2077 ' FOR UPDATE';
2078
2079 v_DAC_Select := v_DAC_Select ||
2080 v_DAC_From ||
2081 v_DAC_Where ||
2082 ' FOR UPDATE';
2083
2084 v_DTX_Select := v_DTX_Select ||
2085 v_DTX_From ||
2086 v_DTX_Where ||
2087 ' FOR UPDATE';
2088
2089 v_ALL_Select := v_ALL_Select ||
2090 v_ALL_From ||
2091 v_ALL_Where ||
2092 ' FOR UPDATE';
2093
2094 /* */
2095 /* Build the DELETE clauses for each interface and extension table. */
2096 /* */
2097
2098 xProgress := 'GPOAOB-40-1110';
2099 v_ORD_Delete := 'DELETE FROM ' ||
2100 p_ORD_Interface ||
2101 ' WHERE ROWID = :Row_ID';
2102
2103 v_OAC_Delete := 'DELETE FROM ' ||
2104 p_OAC_Interface ||
2105 ' WHERE ROWID = :Row_ID';
2106
2107 v_OTX_Delete := 'DELETE FROM ' ||
2108 p_OTX_Interface ||
2109 ' WHERE ROWID = :Row_ID';
2110
2111 v_DTL_Delete := 'DELETE FROM ' ||
2112 p_DTL_Interface ||
2113 ' WHERE ROWID = :Row_ID';
2114
2115 v_DAC_Delete := 'DELETE FROM ' ||
2116 p_DAC_Interface ||
2117 ' WHERE ROWID = :Row_ID';
2118
2119 v_DTX_Delete := 'DELETE FROM ' ||
2120 p_DTX_Interface ||
2121 ' WHERE ROWID = :Row_ID';
2122
2123 v_ALL_Delete := 'DELETE FROM ' ||
2124 p_ALL_Interface ||
2125 ' WHERE ROWID = :Row_ID';
2126
2127 v_ORD_XDelete := 'DELETE FROM ' ||
2128 v_ORD_XInterface ||
2129 ' WHERE ROWID = :Row_ID';
2130
2131 v_OAC_XDelete := 'DELETE FROM ' ||
2132 v_OAC_XInterface ||
2133 ' WHERE ROWID = :Row_ID';
2134
2135 v_OTX_XDelete := 'DELETE FROM ' ||
2136 v_OTX_XInterface ||
2137 ' WHERE ROWID = :Row_ID';
2138
2139 v_DTL_XDelete := 'DELETE FROM ' ||
2140 v_DTL_XInterface ||
2141 ' WHERE ROWID = :Row_ID';
2142
2143 v_DAC_XDelete := 'DELETE FROM ' ||
2144 v_DAC_XInterface ||
2145 ' WHERE ROWID = :Row_ID';
2146
2147 v_DTX_XDelete := 'DELETE FROM ' ||
2148 v_DTX_XInterface ||
2149 ' WHERE ROWID = :Row_ID';
2150
2151 v_ALL_XDelete := 'DELETE FROM ' ||
2152 v_ALL_XInterface ||
2153 ' WHERE ROWID = :Row_ID';
2154
2155 /* */
2156 /* Open a cursor for each SELECT and DELETE call. This tells */
2157 /* the database to reserve space for the data returned by the */
2158 /* SELECT and DELETE statements. */
2159 /* */
2160
2161 xProgress := 'GPOAOB-40-1120';
2162 v_ORD_Select_Cursor := dbms_sql.open_cursor;
2163 xProgress := 'GPOAOB-40-1122';
2164 v_OAC_Select_Cursor := dbms_sql.open_cursor;
2165 xProgress := 'GPOAOB-40-1124';
2166 v_OTX_Select_Cursor := dbms_sql.open_cursor;
2167 xProgress := 'GPOAOB-40-1126';
2168 v_DTL_Select_Cursor := dbms_sql.open_cursor;
2169 xProgress := 'GPOAOB-40-1128';
2170 v_DAC_Select_Cursor := dbms_sql.open_cursor;
2171 xProgress := 'GPOAOB-40-1130';
2172 v_DTX_Select_Cursor := dbms_sql.open_cursor;
2173 xProgress := 'GPOAOB-40-1132';
2174 v_ALL_Select_Cursor := dbms_sql.open_cursor;
2175
2176 xProgress := 'GPOAOB-40-1134';
2177 v_ORD_Delete_Cursor := dbms_sql.open_cursor;
2178 xProgress := 'GPOAOB-40-1136';
2179 v_OAC_Delete_Cursor := dbms_sql.open_cursor;
2180 xProgress := 'GPOAOB-40-1138';
2181 v_OTX_Delete_Cursor := dbms_sql.open_cursor;
2182 xProgress := 'GPOAOB-40-1140';
2183 v_DTL_Delete_Cursor := dbms_sql.open_cursor;
2184 xProgress := 'GPOAOB-40-1142';
2185 v_DAC_Delete_Cursor := dbms_sql.open_cursor;
2186 xProgress := 'GPOAOB-40-1144';
2187 v_DTX_Delete_Cursor := dbms_sql.open_cursor;
2188 xProgress := 'GPOAOB-40-1146';
2189 v_ALL_Delete_Cursor := dbms_sql.open_cursor;
2190
2191 xProgress := 'GPOAOB-40-1148';
2192 v_ORD_Delete_XCursor := dbms_sql.open_cursor;
2193 xProgress := 'GPOAOB-40-1150';
2194 v_OAC_Delete_XCursor := dbms_sql.open_cursor;
2195 xProgress := 'GPOAOB-40-1152';
2196 v_OTX_Delete_XCursor := dbms_sql.open_cursor;
2197 xProgress := 'GPOAOB-40-1154';
2198 v_DTL_Delete_XCursor := dbms_sql.open_cursor;
2199 xProgress := 'GPOAOB-40-1156';
2200 v_DAC_Delete_XCursor := dbms_sql.open_cursor;
2201 xProgress := 'GPOAOB-40-1158';
2202 v_DTX_Delete_XCursor := dbms_sql.open_cursor;
2203 xProgress := 'GPOAOB-40-1160';
2204 v_ALL_Delete_XCursor := dbms_sql.open_cursor;
2205
2206 /* */
2207 /* Parse each SELECT and DELETE statement so the database understands */
2208 /* the command. */
2209 /* */
2210
2211 xProgress := 'GPOAOB-40-1170';
2212 dbms_sql.parse ( v_ORD_Select_Cursor,
2213 v_ORD_Select,
2214 dbms_sql.native );
2215
2216 xProgress := 'GPOAOB-40-1171';
2217 dbms_sql.parse ( v_OAC_Select_Cursor,
2218 v_OAC_Select,
2219 dbms_sql.native );
2220
2221 xProgress := 'GPOAOB-40-1172';
2222 dbms_sql.parse ( v_OTX_Select_Cursor,
2223 v_OTX_Select,
2224 dbms_sql.native );
2225
2226 xProgress := 'GPOAOB-40-1173';
2227 dbms_sql.parse ( v_DTL_Select_Cursor,
2228 v_DTL_Select,
2229 dbms_sql.native );
2230
2231 xProgress := 'GPOAOB-40-1174';
2232 dbms_sql.parse ( v_DAC_Select_Cursor,
2233 v_DAC_Select,
2234 dbms_sql.native );
2235
2236 xProgress := 'GPOAOB-40-1175';
2237 dbms_sql.parse ( v_DTX_Select_Cursor,
2238 v_DTX_Select,
2239 dbms_sql.native );
2240
2241 xProgress := 'GPOAOB-40-1176';
2242 dbms_sql.parse ( v_ALL_Select_Cursor,
2243 v_ALL_Select,
2244 dbms_sql.native );
2245
2246 xProgress := 'GPOAOB-40-1180';
2247 dbms_sql.parse ( v_ORD_Delete_Cursor,
2248 v_ORD_Delete,
2249 dbms_sql.native );
2250
2251 xProgress := 'GPOAOB-40-1181';
2252 dbms_sql.parse ( v_OAC_Delete_Cursor,
2253 v_OAC_Delete,
2254 dbms_sql.native );
2255
2256 xProgress := 'GPOAOB-40-1182';
2257 dbms_sql.parse ( v_OTX_Delete_Cursor,
2258 v_OTX_Delete,
2259 dbms_sql.native );
2260
2261 xProgress := 'GPOAOB-40-1183';
2262 dbms_sql.parse ( v_DTL_Delete_Cursor,
2263 v_DTL_Delete,
2264 dbms_sql.native );
2265
2266 xProgress := 'GPOAOB-40-1184';
2267 dbms_sql.parse ( v_DAC_Delete_Cursor,
2268 v_DAC_Delete,
2269 dbms_sql.native );
2270
2271 xProgress := 'GPOAOB-40-1185';
2272 dbms_sql.parse ( v_DTX_Delete_Cursor,
2273 v_DTX_Delete,
2274 dbms_sql.native );
2275
2276 xProgress := 'GPOAOB-40-1186';
2277 dbms_sql.parse ( v_ALL_Delete_Cursor,
2278 v_ALL_Delete,
2279 dbms_sql.native );
2280
2281 xProgress := 'GPOAOB-40-1190';
2282 dbms_sql.parse ( v_ORD_Delete_XCursor,
2283 v_ORD_Delete,
2284 dbms_sql.native );
2285
2286 xProgress := 'GPOAOB-40-1191';
2287 dbms_sql.parse ( v_OAC_Delete_XCursor,
2288 v_OAC_Delete,
2289 dbms_sql.native );
2290
2291 xProgress := 'GPOAOB-40-1192';
2292 dbms_sql.parse ( v_OTX_Delete_XCursor,
2293 v_OTX_Delete,
2294 dbms_sql.native );
2295
2296 xProgress := 'GPOAOB-40-1193';
2297 dbms_sql.parse ( v_DTL_Delete_XCursor,
2298 v_DTL_Delete,
2299 dbms_sql.native );
2300
2301 xProgress := 'GPOAOB-40-1194';
2302 dbms_sql.parse ( v_DAC_Delete_XCursor,
2303 v_DAC_Delete,
2304 dbms_sql.native );
2305
2306 xProgress := 'GPOAOB-40-1195';
2307 dbms_sql.parse ( v_DTX_Delete_XCursor,
2308 v_DTX_Delete,
2309 dbms_sql.native );
2310
2311 xProgress := 'GPOAOB-40-1196';
2312 dbms_sql.parse ( v_ALL_Delete_XCursor,
2313 v_ALL_Delete,
2314 dbms_sql.native );
2315
2316 /* */
2317 /* Initialize all counters. */
2318 /* */
2319
2320 xProgress := 'GPOAOB-40-1400';
2321 v_ORD_Count := v_ORD_Table.COUNT;
2322 xProgress := 'GPOAOB-40-1402';
2323 v_OAC_Count := v_OAC_Table.COUNT;
2324 xProgress := 'GPOAOB-40-1404';
2325 v_OTX_Count := v_OTX_Table.COUNT;
2326 xProgress := 'GPOAOB-40-1406';
2327 v_DTL_Count := v_DTL_Table.COUNT;
2328 xProgress := 'GPOAOB-40-1408';
2329 v_DAC_Count := v_DAC_Table.COUNT;
2330 xProgress := 'GPOAOB-40-1410';
2331 v_DTX_Count := v_DTX_Table.COUNT;
2332 xProgress := 'GPOAOB-40-1412';
2333 v_ALL_Count := v_ALL_Table.COUNT;
2334
2335 /* */
2336 /* Define the data type for every column in each SELECT statement */
2337 /* so the database understands how to populate it. Using the */
2338 /* K.I.S.S. principle, every data type will be converted to */
2339 /* VARCHAR2. */
2340 /* */
2341
2342 xProgress := 'GPOAOB-40-1500';
2343 FOR v_LoopCount IN 1..v_ORD_Count
2344 LOOP
2345 xProgress := 'GPOAOB-40-1510';
2346 dbms_sql.define_column ( v_ORD_Select_Cursor,
2347 v_LoopCount,
2348 v_ORD_Select,
2349 ece_extract_utils_pub.G_MaxColWidth );
2350 END LOOP;
2351
2352 /* */
2353 /* Define the ROWIDs for the DELETE statements. */
2354 /* */
2355
2356 xProgress := 'GPOAOB-40-1520';
2357 dbms_sql.define_column_rowid ( v_ORD_Select_Cursor,
2358 v_ORD_Count + 1,
2359 v_ORD_RowID );
2360
2361 xProgress := 'GPOAOB-40-1530';
2362 dbms_sql.define_column_rowid ( v_ORD_Select_Cursor,
2363 v_ORD_Count + 2,
2364 v_ORD_XRowID );
2365
2366 /* */
2367 /* Define the internal ID columns. */
2368 /* */
2369 xProgress := 'GPOAOB-40-1550';
2370 dbms_sql.define_column ( v_ORD_Select_Cursor,
2371 v_ORD_Count + 3,
2372 v_TransactionRecordID );
2373
2374 xProgress := 'GPOAOB-40-1600';
2375 FOR v_LoopCount IN 1..v_OAC_Count
2376 LOOP
2377 xProgress := 'GPOAOB-40-1610';
2378 dbms_sql.define_column ( v_OAC_Select_Cursor,
2379 v_LoopCount,
2380 v_OAC_Select,
2381 ece_extract_utils_pub.G_MaxColWidth );
2382 END LOOP;
2383
2384 /* */
2385 /* Define the ROWIDs for the DELETE statements. */
2386 /* */
2387
2388 xProgress := 'GPOAOB-40-1620';
2389 dbms_sql.define_column_rowid ( v_OAC_Select_Cursor,
2390 v_OAC_Count + 1,
2391 v_OAC_RowID );
2392
2393 xProgress := 'GPOAOB-40-1630';
2394 dbms_sql.define_column_rowid ( v_OAC_Select_Cursor,
2395 v_OAC_Count + 2,
2396 v_OAC_XRowID );
2397
2398 /* */
2399 /* Define the internal ID columns. */
2400 /* */
2401
2402 xProgress := 'GPOAOB-40-1640';
2403 dbms_sql.define_column ( v_OAC_Select_Cursor,
2404 v_OAC_Count + 3,
2405 v_TransactionRecordID );
2406
2407 /* */
2408 xProgress := 'GPOAOB-40-1700';
2409 FOR v_LoopCount IN 1..v_OTX_Count
2410 LOOP
2411 xProgress := 'GPOAOB-40-1710';
2412 dbms_sql.define_column ( v_OTX_Select_Cursor,
2413 v_LoopCount,
2414 v_OTX_Select,
2415 ece_extract_utils_pub.G_MaxColWidth );
2416 END LOOP;
2417
2418 /* */
2419 /* Define the ROWIDs for the DELETE statements. */
2420 /* */
2421
2422 xProgress := 'GPOAOB-40-1720';
2423 dbms_sql.define_column_rowid ( v_OTX_Select_Cursor,
2424 v_OTX_Count + 1,
2425 v_OTX_RowID );
2426
2427 xProgress := 'GPOAOB-40-1730';
2428 dbms_sql.define_column_rowid ( v_OTX_Select_Cursor,
2429 v_OTX_Count + 2,
2430 v_OTX_XRowID );
2431
2432 /* */
2433 /* Define the internal ID columns. */
2434 /* */
2435
2436 xProgress := 'GPOAOB-40-1750';
2437 dbms_sql.define_column ( v_OTX_Select_Cursor,
2438 v_OTX_Count + 3,
2439 v_TransactionRecordID );
2440
2441 xProgress := 'GPOAOB-40-1800';
2442 FOR v_LoopCount IN 1..v_DTL_Count
2443 LOOP
2444 xProgress := 'GPOAOB-40-1810';
2445 dbms_sql.define_column ( v_DTL_Select_Cursor,
2446 v_LoopCount,
2447 v_DTL_Select,
2448 ece_extract_utils_pub.G_MaxColWidth );
2449 END LOOP;
2450
2451 /* */
2452 /* Define the ROWIDs for the DELETE statements. */
2453 /* */
2454
2455 xProgress := 'GPOAOB-40-1820';
2456 dbms_sql.define_column_rowid ( v_DTL_Select_Cursor,
2457 v_DTL_Count + 1,
2458 v_DTL_RowID );
2459
2460 xProgress := 'GPOAOB-40-1830';
2461 dbms_sql.define_column_rowid ( v_DTL_Select_Cursor,
2462 v_DTL_Count + 2,
2463 v_DTL_XRowID );
2464
2465 /* */
2466 /* Define the internal ID columns. */
2467 /* */
2468
2469 xProgress := 'GPOAOB-40-1850';
2470 dbms_sql.define_column ( v_DTL_Select_Cursor,
2471 v_DTL_Count + 3,
2472 v_TransactionRecordID );
2473
2474 xProgress := 'GPOAOB-40-1900';
2475 FOR v_LoopCount IN 1..v_DAC_Count
2476 LOOP
2477 xProgress := 'GPOAOB-40-1910';
2478 dbms_sql.define_column ( v_DAC_Select_Cursor,
2479 v_LoopCount,
2480 v_DAC_Select,
2481 ece_extract_utils_pub.G_MaxColWidth );
2482 END LOOP;
2483
2484 /* */
2485 /* Define the ROWIDs for the DELETE statements. */
2486 /* */
2487
2488 xProgress := 'GPOAOB-40-1920';
2489 dbms_sql.define_column_rowid ( v_DAC_Select_Cursor,
2490 v_DAC_Count + 1,
2491 v_DAC_RowID );
2492
2493 xProgress := 'GPOAOB-40-1930';
2494 dbms_sql.define_column_rowid ( v_DAC_Select_Cursor,
2495 v_DAC_Count + 2,
2496 v_DAC_XRowID );
2497
2498 /* */
2499 /* Define the internal ID columns. */
2500 /* */
2501
2502 xProgress := 'GPOAOB-40-1950';
2503 dbms_sql.define_column ( v_DAC_Select_Cursor,
2504 v_DAC_Count + 3,
2505 v_TransactionRecordID );
2506
2507 xProgress := 'GPOAOB-40-2000';
2508 FOR v_LoopCount IN 1..v_DTX_Count
2509 LOOP
2510 xProgress := 'GPOAOB-40-2010';
2511 dbms_sql.define_column ( v_DTX_Select_Cursor,
2512 v_LoopCount,
2513 v_DTX_Select,
2514 ece_extract_utils_pub.G_MaxColWidth );
2515 END LOOP;
2516
2517 /* */
2518 /* Define the ROWIDs for the DELETE statements. */
2519 /* */
2520
2521 xProgress := 'GPOAOB-40-2020';
2522 dbms_sql.define_column_rowid ( v_DTX_Select_Cursor,
2523 v_DTX_Count + 1,
2524 v_DTX_RowID );
2525
2526 xProgress := 'GPOAOB-40-2030';
2527 dbms_sql.define_column_rowid ( v_DTX_Select_Cursor,
2528 v_DTX_Count + 2,
2529 v_DTX_XRowID );
2530
2531 /* */
2532 /* Define the internal ID columns. */
2533 /* */
2534
2535 xProgress := 'GPOAOB-40-2050';
2536 dbms_sql.define_column ( v_DTX_Select_Cursor,
2537 v_DTX_Count + 3,
2538 v_TransactionRecordID );
2539
2540 xProgress := 'GPOAOB-40-2100';
2541 FOR v_LoopCount IN 1..v_ALL_Count
2542 LOOP
2543 xProgress := 'GPOAOB-40-2110';
2544 dbms_sql.define_column ( v_ALL_Select_Cursor,
2545 v_LoopCount,
2546 v_ALL_Select,
2547 ece_extract_utils_pub.G_MaxColWidth );
2548 END LOOP;
2549
2550 /* */
2551 /* Define the ROWIDs for the DELETE statements. */
2552 /* */
2553
2554 xProgress := 'GPOAOB-40-2120';
2555 dbms_sql.define_column_rowid ( v_ALL_Select_Cursor,
2556 v_ALL_Count + 1,
2557 v_ALL_RowID );
2558
2559 xProgress := 'GPOAOB-40-2210';
2560 dbms_sql.define_column_rowid ( v_ALL_Select_Cursor,
2561 v_ALL_Count + 2,
2562 v_ALL_XRowID );
2563
2564 /* */
2565 /* Define the internal ID columns. */
2566 /* */
2567
2568 xProgress := 'GPOAOB-40-2150';
2569 dbms_sql.define_column ( v_ALL_Select_Cursor,
2570 v_ALL_Count + 3,
2571 v_TransactionRecordID );
2572
2573 /* */
2574 /* Bind columns needed for order select */
2575 /* */
2576
2577 xProgress := 'GPOAOB-40-2170';
2578 dbms_sql.bind_variable ( v_ORD_Select_Cursor,
2579 'RUN_ID',
2580 p_RunID );
2581
2582 /* */
2583 /* Execute the Order level SELECT statement. */
2584 /* */
2585
2586 xProgress := 'GPOAOB-40-2200';
2587 v_Dummy := dbms_sql.execute ( v_ORD_Select_Cursor );
2588
2589 /* ********************************************************* */
2590 /* ** Order Level Loop ** */
2591 /* ********************************************************* */
2592
2593 xProgress := 'GPOAOB-40-2210';
2594 WHILE dbms_sql.fetch_rows ( v_ORD_Select_Cursor ) > 0
2595 LOOP
2596
2597 /* */
2598 /* Store the returned values in the PL/SQL table. */
2599 /* */
2600
2601 xProgress := 'GPOAOB-40-2220';
2602 FOR v_LoopCount IN 1..v_ORD_Count
2603 LOOP
2604 xProgress := 'GPOAOB-40-2230';
2605 dbms_sql.column_value ( v_ORD_Select_Cursor,
2606 v_LoopCount,
2607 v_ORD_Table(v_LoopCount).value );
2608 END LOOP;
2609
2610 /* */
2611 /* Store the ROWIDs. */
2612 /* */
2613
2614 xProgress := 'GPOAOB-40-2240';
2615 dbms_sql.column_value ( v_ORD_Select_Cursor,
2616 v_ORD_Count + 1,
2617 v_ORD_RowID );
2618
2619 xProgress := 'GPOAOB-40-2250';
2620 dbms_sql.column_value ( v_ORD_Select_Cursor,
2621 v_ORD_Count + 2,
2622 v_ORD_XRowID );
2623
2624 /* */
2625 /* Locate the necessary data elements and build the common key */
2626 /* record for this level. Common key elements are used for each */
2627 /* record, so save the values. */
2628 /* */
2629 xProgress := 'GPOAOB-40-2260';
2630 ece_flatfile_pvt.find_pos ( v_ORD_Table,
2631 /* ece_flatfile_pvt.G_Translator_Code, */
2632 'TP_CODE',
2633 v_TranslatorCodePosition );
2634
2635 xProgress := 'GPOAOB-40-2270';
2636 ece_flatfile_pvt.find_pos ( v_ORD_Table,
2637 v_ORD_CommonKeyName,
2638 v_ORD_CKNamePosition );
2639
2640 xProgress := 'GPOAOB-40-2280';
2641 v_RecordCommonKey0 := RPAD ( NVL(SUBSTRB ( v_ORD_Table(v_TranslatorCodePosition).value,
2642 1,
2643 25 ),' '),
2644 25 );
2645
2646 xProgress := 'GPOAOB-40-2290';
2647 v_RecordCommonKey1 := RPAD ( NVL(SUBSTRB ( v_ORD_Table(v_ORD_CKNamePosition).value,
2648 1,
2649 22 ),' '),
2650 22 );
2651
2652 xProgress := 'GPOAOB-40-2300';
2653 v_FileCommonKey := v_RecordCommonKey0 ||
2654 v_RecordCommonKey1 ||
2655 v_KeyPad ||
2656 v_KeyPad;
2657
2658 /* */
2659 /* Write the record to the output table. */
2660 /* */
2661 /*
2662 dbms_output.put_line('Writing to ece_output');
2663 dbms_output.put_line('Transaction Type: ' || p_TransactionType);
2664 dbms_output.put_line('Comm Method: ' || p_CommunicationMethod);
2665 dbms_output.put_line('Int: ' || p_ORD_Interface);
2666 dbms_output.put_line('Width: ' || p_OutputWidth);
2667 dbms_output.put_line('RnID: ' || p_RunID);
2668 dbms_output.put_line('CommonKey: ' || v_FileCommonKey || '<-' );
2669 */
2670 xProgress := 'GPOAOB-40-2310';
2671 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
2672 p_CommunicationMethod,
2673 p_ORD_Interface,
2674 v_ORD_Table,
2675 p_OutputWidth,
2676 p_RunID,
2677 v_FileCommonKey );
2678
2679 /* */
2680 /* Store the values of the necessary elements (Order_ID and */
2681 /* and Transaction_Record_ID) in the Order level SELECT clause */
2682 /* into local variables and use the values to bind the variables */
2683 /* in the SELECT clauses of the Order Charges, Text, Detail levels */
2684 /* */
2685
2686 xProgress := 'GPOAOB-40-2320';
2687 dbms_sql.column_value ( v_ORD_Select_Cursor,
2688 v_ORD_Count + 3,
2689 v_TransactionRecordID );
2690
2691 xProgress := 'GPOAOB-40-2323';
2692 ece_flatfile_pvt.find_pos ( v_ORD_Table,
2693 'ORDER_ID',
2694 v_Order_Id_Position );
2695
2696 /*
2697 **
2698 ** Everything is stored in the PL/SQL table as VARCHAR2, so convert
2699 ** the Order_ID value to NUMBER.
2700 **
2701 */
2702
2703 xProgress := 'GPOAOB-40-2324';
2704 v_Order_Id := TO_NUMBER ( v_ORD_Table(v_Order_Id_Position).value );
2705
2706 xProgress := 'GPOAOB-40-2325';
2707 dbms_sql.bind_variable ( v_OAC_Select_Cursor,
2708 'RUN_ID',
2709 p_RunID );
2710
2711 xProgress := 'GPOAOB-40-2326';
2712 dbms_sql.bind_variable ( v_OAC_Select_Cursor,
2713 'Order_ID',
2714 v_Order_ID );
2715
2716 xProgress := 'GPOAOB-40-2327';
2717 dbms_sql.bind_variable ( v_OTX_Select_Cursor,
2718 'RUN_ID',
2719 p_RunID );
2720
2721 xProgress := 'GPOAOB-40-2328';
2722 dbms_sql.bind_variable ( v_OTX_Select_Cursor,
2723 'Order_ID',
2724 v_Order_ID );
2725
2726 /* */
2727 /* Execute the Order Charges SELECT statement. */
2728 /* */
2729
2730 xProgress := 'GPOAOB-40-2330';
2731 v_Dummy := dbms_sql.execute ( v_OAC_Select_Cursor );
2732
2733 /* ********************************************************* */
2734 /* ** Order Charges Level Loop ** */
2735 /* ********************************************************* */
2736
2737 /* */
2738 /* Fetch the rows, and store the returned values in the */
2739 /* PL/SQL table. */
2740 /* */
2741
2742 xProgress := 'GPOAOB-40-2340';
2743 WHILE dbms_sql.fetch_rows ( v_OAC_Select_Cursor ) > 0
2744 LOOP
2745 xProgress := 'GPOAOB-40-2350';
2746 FOR v_LoopCount IN 1..v_OAC_Count
2747 LOOP
2748 xProgress := 'GPOAOB-40-2360';
2749 dbms_sql.column_value ( v_OAC_Select_Cursor,
2750 v_LoopCount,
2751 v_OAC_Table(v_LoopCount).value );
2752 END LOOP;
2753
2754 /* */
2755 /* Get the ROWIDs. */
2756 /* */
2757
2758 xProgress := 'GPOAOB-40-2370';
2759 dbms_sql.column_value ( v_OAC_Select_Cursor,
2760 v_OAC_Count + 1,
2761 v_OAC_RowID );
2762
2763 xProgress := 'GPOAOB-40-2380';
2764 dbms_sql.column_value ( v_OAC_Select_Cursor,
2765 v_OAC_Count + 2,
2766 v_OAC_XRowID );
2767
2768 /* */
2769 /* Update Common Key */
2770 /* */
2771 xProgress := 'GPOAOB-40-2382';
2772 ece_flatfile_pvt.find_pos ( v_OAC_Table,
2773 v_OAC_CommonKeyName,
2774 v_OAC_CKNamePosition );
2775
2776 xProgress := 'GPOAOB-40-2384';
2777 v_RecordCommonKey2 := RPAD ( NVL(SUBSTRB ( v_OAC_Table(v_OAC_CKNamePosition).value,
2778 1,
2779 22 ),' '),
2780 22 );
2781
2782 xProgress := 'GPOAOB-40-2386';
2783 v_FileCommonKey := v_RecordCommonKey0 ||
2784 v_RecordCommonKey1 ||
2785 v_RecordCommonKey2 ||
2786 v_KeyPad;
2787
2788 /* */
2789 /* Write the record to the output table. */
2790 /* */
2791
2792 xProgress := 'GPOAOB-40-2390';
2793 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
2794 p_CommunicationMethod,
2795 p_OAC_Interface,
2796 v_OAC_Table,
2797 p_OutputWidth,
2798 p_RunID,
2799 v_FileCommonKey );
2800
2801 /* */
2802 /* Bind the variables (ROWIDs) in the DELETE statements for the */
2803 /* OAC interface tables. */
2804 /* */
2805
2806 xProgress := 'GPOAOB-40-2391';
2807 dbms_sql.bind_variable ( v_OAC_Delete_Cursor,
2808 'Row_ID',
2809 v_OAC_RowID );
2810
2811 xProgress := 'GPOAOB-40-2392';
2812 dbms_sql.bind_variable ( v_OAC_Delete_XCursor,
2813 'Row_ID',
2814 v_OAC_XRowID );
2815
2816 /* */
2817 /* Delete the rows from the interface tables. */
2818 /* */
2819
2820 xProgress := 'GPOAOB-40-2393';
2821 v_Dummy := dbms_sql.execute ( v_OAC_Delete_Cursor );
2822
2823 xProgress := 'GPOAOB-40-2394';
2824 v_Dummy := dbms_sql.execute ( v_OAC_Delete_XCursor );
2825
2826 END LOOP; /* Order Charge Level */
2827
2828 /* */
2829 /* Execute the Order Text SELECT statement. */
2830 /* */
2831
2832 xProgress := 'GPOAOB-40-2330';
2833 v_Dummy := dbms_sql.execute ( v_OTX_Select_Cursor );
2834
2835 /* ********************************************************* */
2836 /* ** Order Text Level Loop ** */
2837 /* ********************************************************* */
2838
2839
2840 /* */
2841 /* Fetch the rows, and store the returned values in the */
2842 /* PL/SQL table. */
2843 /* */
2844
2845 xProgress := 'GPOAOB-40-2440';
2846 WHILE dbms_sql.fetch_rows ( v_OTX_Select_Cursor ) > 0
2847 LOOP
2848 xProgress := 'GPOAOB-40-2450';
2849 FOR v_LoopCount IN 1..v_OTX_Count
2850 LOOP
2851 xProgress := 'GPOAOB-40-2460';
2852 dbms_sql.column_value ( v_OTX_Select_Cursor,
2853 v_LoopCount,
2854 v_OTX_Table(v_LoopCount).value );
2855 END LOOP;
2856
2857 /* */
2858 /* Get the ROWIDs. */
2859 /* */
2860
2861 xProgress := 'GPOAOB-40-2470';
2862 dbms_sql.column_value ( v_OTX_Select_Cursor,
2863 v_OTX_Count + 1,
2864 v_OTX_RowID );
2865
2866 xProgress := 'GPOAOB-40-2480';
2867 dbms_sql.column_value ( v_OTX_Select_Cursor,
2868 v_OTX_Count + 2,
2869 v_OTX_XRowID );
2870
2871 /* */
2872 /* Update Common Key */
2873 /* */
2874 xProgress := 'GPOAOB-40-2482';
2875 ece_flatfile_pvt.find_pos ( v_OTX_Table,
2876 v_OTX_CommonKeyName,
2877 v_OTX_CKNamePosition );
2878
2879
2880 xProgress := 'GPOAOB-40-2484';
2881 v_RecordCommonKey2 := RPAD ( NVL(SUBSTRB ( v_OTX_Table(v_OTX_CKNamePosition).value,
2882 1,
2883 22 ),' '),
2884 22 );
2885
2886 xProgress := 'GPOAOB-40-2486';
2887 v_FileCommonKey := v_RecordCommonKey0 ||
2888 v_RecordCommonKey1 ||
2889 v_RecordCommonKey2 ||
2890 v_KeyPad;
2891
2892 /* */
2893 /* Write the record to the output table. */
2894 /* */
2895
2896 xProgress := 'GPOAOB-40-2490';
2897 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
2898 p_CommunicationMethod,
2899 p_OTX_Interface,
2900 v_OTX_Table,
2901 p_OutputWidth,
2902 p_RunID,
2903 v_FileCommonKey );
2904
2905 /* */
2906 /* Bind the variables (ROWIDs) in the DELETE statements for the */
2907 /* OTX interface tables. */
2908 /* */
2909
2910 xProgress := 'GPOAOB-40-2491';
2911 dbms_sql.bind_variable ( v_OTX_Delete_Cursor,
2912 'Row_ID',
2913 v_OTX_RowID );
2914
2915 xProgress := 'GPOAOB-40-2492';
2916 dbms_sql.bind_variable ( v_OTX_Delete_XCursor,
2917 'Row_ID',
2918 v_OTX_XRowID );
2919
2920 /* */
2921 /* Delete the rows from the interface tables. */
2922 /* */
2923
2924 xProgress := 'GPOAOB-40-2493';
2925 v_Dummy := dbms_sql.execute ( v_OTX_Delete_Cursor );
2926
2927 xProgress := 'GPOAOB-40-2494';
2928 v_Dummy := dbms_sql.execute ( v_OTX_Delete_XCursor );
2929
2930 END LOOP; /* Order Text Level */
2931
2932 /* */
2933 /* Bind the variables (ROWIDs) in the DELETE statements for the */
2934 /* Delivery and Delivery Attribute interface tables. */
2935 /* */
2936
2937 /* */
2938 /* Execute the Detail level SELECT statement. */
2939 /* */
2940 xProgress := 'GPOAOB-40-2500';
2941 dbms_sql.bind_variable ( v_DTL_Select_Cursor,
2942 'RUN_ID',
2943 p_RunID );
2944
2945 dbms_sql.bind_variable ( v_DTL_Select_Cursor,
2946 'Order_ID',
2947 v_Order_ID );
2948
2949 xProgress := 'GPOAOB-40-2510';
2950 v_Dummy := dbms_sql.execute ( v_DTL_Select_Cursor );
2951
2952 /* */
2953 /* Begin the Detail level loop. */
2954 /* */
2955 xProgress := 'GPOAOB-40-2520';
2956 WHILE dbms_sql.fetch_rows ( v_DTL_Select_Cursor ) > 0
2957 LOOP
2958 xProgress := 'GPOAOB-40-2530';
2959 FOR v_LoopCount IN 1..v_DTL_Count
2960 LOOP
2961 xProgress := 'GPOAOB-40-2540';
2962 dbms_sql.column_value ( v_DTL_Select_Cursor,
2963 v_LoopCount,
2964 v_DTL_Table(v_LoopCount).value);
2965 END LOOP;
2966
2967 /* */
2968 /* Store the ROWIDs. */
2969 /* */
2970
2971 xProgress := 'GPOAOB-40-2550';
2972 dbms_sql.column_value ( v_DTL_Select_Cursor,
2973 v_DTL_Count + 1,
2974 v_DTL_RowID );
2975
2976 xProgress := 'GPOAOB-40-2560';
2977 dbms_sql.column_value ( v_DTL_Select_Cursor,
2978 v_DTL_Count + 2,
2979 v_DTL_XRowID );
2980
2981 /* */
2982 /* Find the Line Number in the PL/SQL table and add this */
2983 /* value to the common key. */
2984 /* */
2985
2986 xProgress := 'GPOAOB-40-2570';
2987 ece_flatfile_pvt.find_pos ( v_DTL_Table,
2988 v_DTL_CommonKeyName,
2989 v_DTL_CKNamePosition);
2990
2991 xProgress := 'GPOAOB-40-2580';
2992 v_RecordCommonKey2 := RPAD ( NVL(SUBSTRB ( v_DTL_Table(v_DTL_CKNamePosition).value,
2993 1, 22 ),' '), 22 );
2994
2995 xProgress := 'GPOAOB-40-2590';
2996 v_FileCommonKey := v_RecordCommonKey0 ||
2997 v_RecordCommonKey1 ||
2998 v_RecordCommonKey2 ||
2999 v_KeyPad;
3000
3001 /* */
3002 /* Write the record to the output table. */
3003 /* */
3004
3005 xProgress := 'GPOAOB-40-2600';
3006 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3007 p_CommunicationMethod,
3008 p_DTL_Interface,
3009 v_DTL_Table,
3010 p_OutputWidth,
3011 p_RunID,
3012 v_FileCommonKey );
3013
3014 xProgress := 'GPOAOB-40-2610';
3015 dbms_sql.bind_variable ( v_DTL_Delete_Cursor,
3016 'Row_ID',
3017 v_DTL_RowID );
3018
3019 xProgress := 'GPOAOB-40-2620';
3020 dbms_sql.bind_variable ( v_DTL_Delete_XCursor,
3021 'Row_ID',
3022 v_DTL_XRowID );
3023
3024 /* */
3025 /* Delete the rows from the interface table. */
3026 /* */
3027
3028 xProgress := 'GPOAOB-40-2630';
3029 v_Dummy := dbms_sql.execute ( v_DTL_Delete_Cursor );
3030
3031 xProgress := 'GPOAOB-40-2640';
3032 v_Dummy := dbms_sql.execute ( v_DTL_Delete_XCursor );
3033
3034 /* */
3035 /* Execute the Detail DAC SELECT statement. */
3036 /* */
3037 xProgress := 'GPOAOB-40-2650';
3038 ece_flatfile_pvt.find_pos( v_DTL_Table,
3039 'Line_ID',
3040 v_Line_ID_Position );
3041
3042 xProgress := 'GPOAOB-40-2660';
3043 v_Line_Id := TO_NUMBER ( v_DTL_Table(v_Line_Id_Position).value );
3044
3045 xProgress := 'GPOAOB-40-2670';
3046 dbms_sql.bind_variable ( v_DAC_Select_Cursor,
3047 'RUN_ID',
3048 p_RunID );
3049
3050 dbms_sql.bind_variable ( v_DAC_Select_Cursor,
3051 'Line_ID',
3052 v_Line_ID );
3053
3054 xProgress := 'GPOAOB-40-2680';
3055 v_Dummy := dbms_sql.execute ( v_DAC_Select_Cursor );
3056
3057 /* */
3058 /* Begin the DETAIL DAC loop. */
3059 /* */
3060
3061 xProgress := 'GPOAOB-40-2690';
3062 WHILE dbms_sql.fetch_rows ( v_DAC_Select_Cursor ) > 0
3063 LOOP
3064 xProgress := 'GPOAOB-40-2700';
3065 FOR v_LoopCount IN 1..v_DAC_Count
3066 LOOP
3067 xProgress := 'GPOAOB-40-2710';
3068 dbms_sql.column_value ( v_DAC_Select_Cursor,
3069 v_LoopCount,
3070 v_DAC_Table(v_LoopCount).value );
3071 END LOOP;
3072
3073 /* */
3074 /* Store the ROWIDs. */
3075 /* */
3076
3077 xProgress := 'GPOAOB-40-2720';
3078 dbms_sql.column_value ( v_DAC_Select_Cursor,
3079 v_DAC_Count + 1,
3080 v_DAC_RowID );
3081
3082 xProgress := 'GPOAOB-40-2730';
3083 dbms_sql.column_value ( v_DAC_Select_Cursor,
3084 v_DAC_Count + 2,
3085 v_DAC_XRowID );
3086
3087 xProgress := 'GPOAOB-40-2735';
3088 ece_flatfile_pvt.find_pos ( v_DAC_Table,
3089 v_DAC_CommonKeyName,
3090 v_DAC_CKNamePosition);
3091
3092 xProgress := 'GPOAOB-40-2736';
3093 v_RecordCommonKey3 := RPAD ( NVL(SUBSTRB ( v_DAC_Table(v_DAC_CKNamePosition).value,
3094 1, 22 ),' '), 22 );
3095
3096 xProgress := 'GPOAOB-40-2740';
3097 v_FileCommonKey := v_RecordCommonKey0 ||
3098 v_RecordCommonKey1 ||
3099 v_RecordCommonKey2 ||
3100 v_RecordCommonKey3;
3101
3102 /* */
3103 /* Write the record to the output table. */
3104 /* */
3105 xProgress := 'GPOAOB-40-2770';
3106 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3107 p_CommunicationMethod,
3108 p_DAC_Interface,
3109 v_DAC_Table,
3110 p_OutputWidth,
3111 p_RunID,
3112 v_FileCommonKey );
3113
3114 xProgress := 'GPOAOB-40-2780';
3115 dbms_sql.bind_variable ( v_DAC_Delete_Cursor,
3116 'Row_ID',
3117 v_DAC_RowID );
3118
3119 xProgress := 'GPOAOB-40-2790';
3120 dbms_sql.bind_variable ( v_DAC_Delete_XCursor,
3121 'Row_ID',
3122 v_DAC_XRowID );
3123
3124 xProgress := 'GPOAOB-40-2800';
3125 v_Dummy := dbms_sql.execute ( v_DAC_Delete_Cursor );
3126
3127 xProgress := 'GPOAOB-40-2340';
3128 v_Dummy := dbms_sql.execute ( v_DAC_Delete_XCursor );
3129 END LOOP; /* while dac */
3130
3131 xProgress := 'GPOAOB-40-2670';
3132 dbms_sql.bind_variable ( v_DTX_Select_Cursor,
3133 'RUN_ID',
3134 p_RunID );
3135
3136 dbms_sql.bind_variable ( v_DTX_Select_Cursor,
3137 'Line_ID',
3138 v_Line_ID );
3139
3140 xProgress := 'GPOAOB-40-2680';
3141 v_Dummy := dbms_sql.execute ( v_DTX_Select_Cursor );
3142
3143 /* */
3144 /* Begin the DETAIL Text loop. */
3145 /* */
3146
3147 xProgress := 'GPOAOB-40-2690';
3148 WHILE dbms_sql.fetch_rows ( v_DTX_Select_Cursor ) > 0
3149 LOOP
3150 xProgress := 'GPOAOB-40-2700';
3151 FOR v_LoopCount IN 1..v_DTX_Count
3152 LOOP
3153 xProgress := 'GPOAOB-40-2710';
3154 dbms_sql.column_value ( v_DTX_Select_Cursor,
3155 v_LoopCount,
3156 v_DTX_Table(v_LoopCount).value );
3157 END LOOP;
3158
3159 /* */
3160 /* Store the ROWIDs. */
3161 /* */
3162
3163 xProgress := 'GPOAOB-40-2720';
3164 dbms_sql.column_value ( v_DTX_Select_Cursor,
3165 v_DTX_Count + 1,
3166 v_DTX_RowID );
3167
3168 xProgress := 'GPOAOB-40-2730';
3169 dbms_sql.column_value ( v_DTX_Select_Cursor,
3170 v_DTX_Count + 2,
3171 v_DTX_XRowID );
3172
3173
3174 xProgress := 'GPOAOB-40-2735';
3175 ece_flatfile_pvt.find_pos ( v_DTX_Table,
3176 v_DTX_CommonKeyName,
3177 v_DTX_CKNamePosition);
3178
3179 xProgress := 'GPOAOB-40-2736';
3180 v_RecordCommonKey3 := RPAD ( NVL(SUBSTRB ( v_DTX_Table(v_DTX_CKNamePosition).value,
3181 1, 22 ),' '), 22 );
3182
3183 xProgress := 'GPOAOB-40-2740';
3184 v_FileCommonKey := v_RecordCommonKey0 ||
3185 v_RecordCommonKey1 ||
3186 v_RecordCommonKey2 ||
3187 v_RecordCommonKey3;
3188
3189 /* */
3190 /* Write the record to the output table. */
3191 /* */
3192 xProgress := 'GPOAOB-40-2770';
3193 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3194 p_CommunicationMethod,
3195 p_DTX_Interface,
3196 v_DTX_Table,
3197 p_OutputWidth,
3198 p_RunID,
3199 v_FileCommonKey );
3200
3201 xProgress := 'GPOAOB-40-2780';
3202 dbms_sql.bind_variable ( v_DTX_Delete_Cursor,
3203 'Row_ID',
3204 v_DTX_RowID );
3205
3206 xProgress := 'GPOAOB-40-2790';
3207 dbms_sql.bind_variable ( v_DTX_Delete_XCursor,
3208 'Row_ID',
3209 v_DTX_XRowID );
3210
3211 xProgress := 'GPOAOB-40-2800';
3212 v_Dummy := dbms_sql.execute ( v_DTX_Delete_Cursor );
3213
3214 xProgress := 'GPOAOB-40-2340';
3215 v_Dummy := dbms_sql.execute ( v_DTX_Delete_XCursor );
3216 END LOOP; /* while dtx */
3217
3218 xProgress := 'GPOAOB-40-2670';
3219 dbms_sql.bind_variable ( v_ALL_Select_Cursor,
3220 'RUN_ID',
3221 p_RunID );
3222
3223 dbms_sql.bind_variable ( v_ALL_Select_Cursor,
3224 'Line_ID',
3225 v_Line_ID );
3226
3227 xProgress := 'GPOAOB-40-2680';
3228 v_Dummy := dbms_sql.execute ( v_ALL_Select_Cursor );
3229
3230 /* */
3231 /* Begin the Allocations loop. */
3232 /* */
3233
3234 xProgress := 'GPOAOB-40-2690';
3235 WHILE dbms_sql.fetch_rows ( v_ALL_Select_Cursor ) > 0
3236 LOOP
3237 xProgress := 'GPOAOB-40-2700';
3238 FOR v_LoopCount IN 1..v_ALL_Count
3239 LOOP
3240 xProgress := 'GPOAOB-40-2710';
3241 dbms_sql.column_value ( v_ALL_Select_Cursor,
3242 v_LoopCount,
3243 v_ALL_Table(v_LoopCount).value );
3244 END LOOP;
3245
3246 /* */
3247 /* Store the ROWIDs. */
3248 /* */
3249
3250 xProgress := 'GPOAOB-40-2720';
3251 dbms_sql.column_value ( v_ALL_Select_Cursor,
3252 v_ALL_Count + 1,
3253 v_ALL_RowID );
3254
3255 xProgress := 'GPOAOB-40-2730';
3256 dbms_sql.column_value ( v_ALL_Select_Cursor,
3257 v_ALL_Count + 2,
3258 v_ALL_XRowID );
3259
3260
3261 xProgress := 'GPOAOB-40-2735';
3262 ece_flatfile_pvt.find_pos ( v_ALL_Table,
3263 v_ALL_CommonKeyName,
3264 v_ALL_CKNamePosition);
3265
3266 xProgress := 'GPOAOB-40-2736';
3267 v_RecordCommonKey3 := RPAD ( NVL(SUBSTRB ( v_ALL_Table(v_ALL_CKNamePosition).value,
3268 1, 22 ),' '), 22 );
3269
3270 xProgress := 'GPOAOB-40-2740';
3271 v_FileCommonKey := v_RecordCommonKey0 ||
3272 v_RecordCommonKey1 ||
3273 v_RecordCommonKey2 ||
3274 v_RecordCommonKey3;
3275
3276 /* */
3277 /* Write the record to the output table. */
3278 /* */
3279 xProgress := 'GPOAOB-40-2770';
3280 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3281 p_CommunicationMethod,
3282 p_ALL_Interface,
3283 v_ALL_Table,
3284 p_OutputWidth,
3285 p_RunID,
3286 v_FileCommonKey );
3287
3288 xProgress := 'GPOAOB-40-2780';
3289 dbms_sql.bind_variable ( v_ALL_Delete_Cursor,
3290 'Row_ID',
3291 v_ALL_RowID );
3292
3293 xProgress := 'GPOAOB-40-2790';
3294 dbms_sql.bind_variable ( v_ALL_Delete_XCursor,
3295 'Row_ID',
3296 v_ALL_XRowID );
3297
3298 xProgress := 'GPOAOB-40-2800';
3299 v_Dummy := dbms_sql.execute ( v_ALL_Delete_Cursor );
3300
3301 xProgress := 'GPOAOB-40-2340';
3302 v_Dummy := dbms_sql.execute ( v_ALL_Delete_XCursor );
3303 END LOOP; /* while all */
3304
3305 END LOOP; /* while dtl */
3306
3307 xProgress := 'GPOAOB-40-2990';
3308 dbms_sql.bind_variable ( v_ORD_Delete_Cursor,
3309 'Row_ID',
3310 v_ORD_RowID );
3311
3312 xProgress := 'GPOAOB-40-3000';
3313 dbms_sql.bind_variable ( v_ORD_Delete_XCursor,
3314 'Row_ID',
3315 v_ORD_XRowID );
3316
3317 /* */
3318 /* Delete the rows from the interface tables. */
3319 /* */
3320
3321 xProgress := 'GPOAOB-40-3030';
3322 v_Dummy := dbms_sql.execute ( v_ORD_Delete_Cursor );
3323
3324 xProgress := 'GPOAOB-40-3040';
3325 v_Dummy := dbms_sql.execute ( v_ORD_Delete_XCursor );
3326
3327 END LOOP; /* Order Level */
3328
3329 /* */
3330 /* Commit the interface table DELETEs. */
3331 /* */
3332
3333 xProgress := 'GASNOB-40-3070';
3334 ec_debug.pop ( 'GML_GPOAO.Put_Data_To_Output_Table' );
3335 COMMIT;
3336
3337 /* */
3338 /* Close all open cursors. */
3339 /* */
3340
3341 xProgress := 'GPOAOB-40-3080';
3342 dbms_sql.close_cursor ( v_ORD_Select_Cursor );
3343 xProgress := 'GPOAOB-40-3081';
3344 dbms_sql.close_cursor ( v_OAC_Select_Cursor );
3345 xProgress := 'GPOAOB-40-3082';
3346 dbms_sql.close_cursor ( v_OTX_Select_Cursor );
3347 xProgress := 'GPOAOB-40-3083';
3348 dbms_sql.close_cursor ( v_DTL_Select_Cursor );
3349 xProgress := 'GPOAOB-40-3084';
3350 dbms_sql.close_cursor ( v_DAC_Select_Cursor );
3351 xProgress := 'GPOAOB-40-3085';
3352 dbms_sql.close_cursor ( v_DTX_Select_Cursor );
3353 xProgress := 'GPOAOB-40-3086';
3354 dbms_sql.close_cursor ( v_ALL_Select_Cursor );
3355
3356 xProgress := 'GPOAOB-40-3088';
3357 dbms_sql.close_cursor ( v_ORD_Delete_Cursor );
3358 xProgress := 'GPOAOB-40-3089';
3359 dbms_sql.close_cursor ( v_OAC_Delete_Cursor );
3360 xProgress := 'GPOAOB-40-3090';
3361 dbms_sql.close_cursor ( v_OTX_Delete_Cursor );
3362 xProgress := 'GPOAOB-40-3091';
3363 dbms_sql.close_cursor ( v_DTL_Delete_Cursor );
3364 xProgress := 'GPOAOB-40-3092';
3365 dbms_sql.close_cursor ( v_DAC_Delete_Cursor );
3366 xProgress := 'GPOAOB-40-3093';
3367 dbms_sql.close_cursor ( v_DTX_Delete_Cursor );
3368 xProgress := 'GPOAOB-40-3094';
3369 dbms_sql.close_cursor ( v_ALL_Delete_Cursor );
3370
3371 xProgress := 'GPOAOB-40-3096';
3372 dbms_sql.close_cursor ( v_ORD_Delete_XCursor );
3373 xProgress := 'GPOAOB-40-3097';
3374 dbms_sql.close_cursor ( v_OAC_Delete_XCursor );
3375 xProgress := 'GPOAOB-40-3098';
3376 dbms_sql.close_cursor ( v_OTX_Delete_XCursor );
3377 xProgress := 'GPOAOB-40-3099';
3378 dbms_sql.close_cursor ( v_DTL_Delete_XCursor );
3379 xProgress := 'GPOAOB-40-3100';
3380 dbms_sql.close_cursor ( v_DAC_Delete_XCursor );
3381 xProgress := 'GPOAOB-40-3101';
3382 dbms_sql.close_cursor ( v_DTX_Delete_XCursor );
3383 xProgress := 'GPOAOB-40-3102';
3384 dbms_sql.close_cursor ( v_ALL_Delete_XCursor );
3385
3386 EXCEPTION
3387 WHEN OTHERS THEN
3388
3389 ec_debug.pl ( 0,
3390 'EC',
3391 'ECE_PROGRAM_ERROR',
3392 'PROGRESS_LEVEL',
3393 xProgress );
3394
3395 ec_debug.pl ( 0,
3396 'EC',
3397 'ECE_ERROR_MESSAGE',
3398 'ERROR_MESSAGE',
3399 SQLERRM );
3400
3401 app_exception.raise_exception;
3402
3403 END Put_Data_To_Output_Table;
3404
3405 END GML_GPOAO;