[Home] [Help]
PACKAGE BODY: APPS.GML_GASNO
Source
1 PACKAGE BODY GML_GASNO as
2 /* $Header: GMLSNOB.pls 115.21 2002/11/08 16:08:16 gmangari ship $ */
3 /*============================= GML_GASNO =================================*/
4 /*============================================================================
5 PURPOSE: Creates procedures for exporting Ship Notice information
6 to a flat file, and the API called by Shipping to initiate
7 the extract process.
8
9 NOTES: To run the script:
10
11 sql> start GMLSNO.pls
12
13 HISTORY: 01/26/99 mmacary created.
14 02/23/99 rlein modified.
15 04/06/99 siwang modified for 11i.
16 05/06/99 dgrailic modified for 11i.
17 Changed ECE_ to GML_ prefix. Ported some 11.0 fixes to 11i
18 Corrected some obsolete references to DSNO to GASNO
19 06/08/99 dgrailic made argumnet list agrree with concurent
20 program parameter list.
21 08/05/99 mguthrie Added calls to ECE_FLATFILE_PVT.INIT_TABLE
22 10/27/99 SFeinstein B911176: changed format of date for Y2K
23 26-OCT-2002 Bug#2642152 RajaSekhar Added NOCOPY hint
24
25 ===========================================================================*/
26
27 /*===========================================================================
28
29 PROCEDURE NAME: Extract_GASNO_Outbound
30
31 PURPOSE: This procedure initiates the concurrent process to
32 extract the eligible Deliveries on a Departure.
33
34 ===========================================================================*/
35
36 PROCEDURE Extract_GASNO_Outbound ( errbuf OUT NOCOPY VARCHAR2,
37 retcode OUT NOCOPY VARCHAR2,
38 p_OutputPath IN VARCHAR2,
39 p_Filename IN VARCHAR2,
40 p_Orgn_Code IN VARCHAR2,
41 p_BOL_No_From IN VARCHAR2,
42 p_BOL_No_To IN VARCHAR2,
43 p_Creation_Date_From IN VARCHAR2,
44 p_Creation_Date_To IN VARCHAR2,
45 p_Customer_Name IN VARCHAR2,
46 p_debug_mode IN NUMBER default 0 )
47 IS
48 p_RunID NUMBER := 0;
49 p_OutputWidth INTEGER := 4000;
50 p_TransactionType VARCHAR2(120) := 'GASNO';
51 p_CommunicationMethod VARCHAR2(120) := 'EDI';
52 p_SHP_Interface VARCHAR2(120) := 'GML_GASNO_SHIPMENTS';
53 p_STX_Interface VARCHAR2(120) := 'GML_GASNO_SHIPMENT_TEXT';
54 p_ORD_Interface VARCHAR2(120) := 'GML_GASNO_ORDERS';
55 p_OAC_Interface VARCHAR2(120) := 'GML_GASNO_ORDER_CHARGES';
56 p_OTX_Interface VARCHAR2(120) := 'GML_GASNO_ORDER_TEXT';
57 p_DTL_Interface VARCHAR2(120) := 'GML_GASNO_DETAILS';
58 p_DAC_Interface VARCHAR2(120) := 'GML_GASNO_DETAIL_CHARGES';
59 p_DTX_Interface VARCHAR2(120) := 'GML_GASNO_DETAIL_TEXT';
60 p_ALL_Interface VARCHAR2(120) := 'GML_GASNO_DETAIL_ALLOCATIONS';
61 v_OutputFilePtr utl_file.file_type;
62 v_OutputLine VARCHAR2(2000);
63 v_OutputRecordCount NUMBER;
64 v_industry VARCHAR2(240);
65 v_oracle_schema VARCHAR2(240);
66 xProgress VARCHAR2(80);
67
68 CURSOR c_OutputSource IS
69 SELECT text
70 FROM ece_output
71 WHERE run_id = p_RunID
72 ORDER BY line_id;
73
74 BEGIN
75
76 ec_debug.enable_debug ( p_debug_mode );
77 ec_debug.push ( 'GML_GASNO.Extract_GASNO_Outbound' );
78 ec_debug.pl ( 3, 'p_Orgn_Code: ', p_Orgn_Code );
79 ec_debug.pl ( 3, 'p_BOL_No_From: ', p_BOL_No_From );
80 ec_debug.pl ( 3, 'p_BOL_No_To: ', p_BOL_No_To );
81 ec_debug.pl ( 3, 'p_Creation_Date_From: ', p_Creation_Date_From );
82 ec_debug.pl ( 3, 'p_Creation_Date_To: ', p_Creation_Date_To );
83 ec_debug.pl ( 3, 'p_Customer_Name: ', p_Customer_Name );
84 ec_debug.pl ( 3, 'p_OutputPath: ', p_OutputPath );
85 ec_debug.pl ( 3, 'p_Filename: ', p_Filename );
86 ec_debug.pl ( 3, 'p_debug_mode: ', p_debug_mode );
87
88 xProgress := 'GASNO-10-1005';
89 BEGIN
90 SELECT ece_output_runs_s.NEXTVAL
91 INTO p_RunID
92 FROM sys.dual;
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 ec_debug.pl ( 0,
96 'EC',
97 'GML_GET_NEXT_SEQ_FAILED',
98 'PROGRESS_LEVEL',
99 xProgress,
100 'SEQ',
101 'ECE_OUTPUT_RUNS_S' );
102 END;
103 ec_debug.pl(3, 'p_RunID: ',p_RunID);
104
105 xProgress := 'GASNO-10-1015';
106 ec_debug.pl ( 0, 'EC', 'GML_GASNO_START', NULL );
107
108 xProgress := 'GASNO-10-1020';
109 ec_debug.pl ( 0, 'EC', 'ECE_RUN_ID', 'RUN_ID', p_RunID );
110
111 xProgress := 'GASNO-10-1030';
112 GML_GASNO.Populate_Interface_Tables( p_CommunicationMethod,
113 p_TransactionType,
114 p_Orgn_Code,
115 p_BOL_No_From,
116 p_BOL_No_To,
117 p_Creation_Date_From,
118 p_Creation_Date_To,
119 p_Customer_Name,
120 p_RunID,
121 p_SHP_Interface,
122 p_STX_Interface,
123 p_ORD_Interface,
124 p_OAC_Interface,
125 p_OTX_Interface,
126 p_DTL_Interface,
127 p_DAC_Interface,
128 p_DTX_Interface,
129 p_ALL_Interface );
130
131 xProgress := 'GASNO-10-1040';
132 GML_GASNO.Put_Data_To_Output_Table( p_CommunicationMethod,
133 p_TransactionType,
134 p_Orgn_Code,
135 p_BOL_No_From,
136 p_BOL_No_To,
137 p_Creation_Date_From,
138 p_Creation_Date_To,
139 p_Customer_Name,
140 p_RunID,
141 p_OutputWidth,
142 p_SHP_Interface,
143 p_STX_Interface,
144 p_ORD_Interface,
145 p_OAC_Interface,
146 p_OTX_Interface,
147 p_DTL_Interface,
148 p_DAC_Interface,
149 p_DTX_Interface,
150 p_ALL_Interface );
151
152 xProgress := 'GASNO-10-1050';
153 BEGIN
154 SELECT COUNT(*)
155 INTO v_OutputRecordCount
156 FROM ece_output
157 WHERE run_id = p_RunID;
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 ec_debug.pl ( 0,
161 'EC',
162 'ECE_GET_COUNT_FAILED',
163 'PROGRESS_LEVEL',
164 xProgress,
165 'TABLE_NAME',
166 'ECE_OUTPUT' );
167 END;
168 ec_debug.pl ( 3, 'v_OutputRecordCount: ', v_OutputRecordCount );
169
170 xProgress := 'GASNO-10-1060';
171 IF v_OutputRecordCount > 0
172 THEN
173 xProgress := 'GASNO-10-1070';
174 v_OutputFilePtr := utl_file.fopen ( p_OutputPath,
175 p_FileName,
176 'W' );
177
178 xProgress := 'GASNO-10-1080';
179 OPEN c_OutputSource;
180 xProgress := 'GASNO-10-1090';
181 LOOP
182 xProgress := 'GASNO-10-1000';
183 FETCH c_OutputSource
184 INTO v_OutputLine;
185 ec_debug.pl ( 3, 'v_OutputLine: ', v_OutputLine );
186
187 xProgress := 'GASNO-10-1100';
188 EXIT WHEN c_OutputSource%NOTFOUND;
189
190 xProgress := 'GASNO-10-1200';
191 utl_file.put_line ( v_OutputFilePtr,
192 v_OutputLine );
193 END LOOP;
194
195 xProgress := 'GASNO-10-1300';
196 CLOSE c_OutputSource;
197
198 xProgress := 'GASNO-10-1400';
199 utl_file.fclose ( v_OutputFilePtr );
200
201 END IF;
202
203 xProgress := 'GASNO-10-1170';
204 ec_debug.pl ( 0, 'EC', 'GML_GASNO_COMPLETE', NULL );
205
206 xProgress := 'GASNO-10-1180';
207 DELETE FROM ece_output
208 WHERE run_id = p_RunID;
209
210 IF SQL%NOTFOUND
211 THEN
212 ec_debug.pl ( 0,
213 'EC',
214 'ECE_NO_ROW_DELETED',
215 'PROGRESS_LEVEL',
216 xProgress,
217 'TABLE_NAME',
218 'ECE_OUTPUT' );
219 END IF;
220
221 ec_debug.pop ( 'GML_GASNO.Extract_GASNO_Outbound' );
222 ec_debug.disable_debug;
223 COMMIT;
224
225 EXCEPTION
226 WHEN utl_file.write_error THEN
227
228 ec_debug.pl ( 0,
229 'EC',
230 'ECE_UTL_WRITE_ERROR',
231 NULL );
232
233 ec_debug.pl ( 0,
234 'EC',
235 'ECE_ERROR_MESSAGE',
236 'ERROR_MESSAGE',
237 SQLERRM );
238
239 retcode := 2;
240 ec_debug.disable_debug;
241 ROLLBACK;
242 RAISE;
243
244 WHEN utl_file.invalid_path THEN
245
246 ec_debug.pl ( 0,
247 'EC',
248 'ECE_UTIL_INVALID_PATH',
249 NULL );
250
251 ec_debug.pl ( 0,
252 'EC',
253 'ECE_ERROR_MESSAGE',
254 'ERROR_MESSAGE',
255 SQLERRM );
256
257 retcode := 2;
258 ec_debug.disable_debug;
259 ROLLBACK;
260 RAISE;
261
262 WHEN utl_file.invalid_operation THEN
263
264 ec_debug.pl ( 0,
265 'EC',
266 'ECE_UTIL_INVALID_OPERATION',
267 NULL );
268
269 ec_debug.pl ( 0,
270 'EC',
271 'ECE_ERROR_MESSAGE',
272 'ERROR_MESSAGE',
273 SQLERRM );
274
275 retcode := 2;
276 ec_debug.disable_debug;
277 ROLLBACK;
278 RAISE;
279
280 WHEN others then
281
282 ec_debug.pl ( 0,
283 'EC',
284 'ECE_PROGRAM_ERROR',
285 'PROGRESS_LEVEL',
286 xProgress );
287
288 ec_debug.pl ( 0,
289 'EC',
290 'ECE_ERROR_MESSAGE',
291 'ERROR_MESSAGE',
292 SQLERRM );
293
294 retcode := 2;
295 ec_debug.disable_debug;
296 ROLLBACK;
297 RAISE;
298
299 END Extract_GASNO_Outbound;
300
301 /*===========================================================================
302
303 PROCEDURE NAME: Populate_Interface_Tables
304
305 PURPOSE: This procedure initiates the export process for all
306 elegible Deliveries on a Departure. For each Gateway
307 interface table in this transaction, a view has been
308 created to facilitate the extract process from the
309 Application tables.
310
311 ===========================================================================*/
312
313 PROCEDURE Populate_Interface_Tables ( p_CommunicationMethod IN VARCHAR2,
314 p_TransactionType IN VARCHAR2,
315 p_Orgn_Code IN VARCHAR2,
316 p_BOL_No_From IN VARCHAR2,
317 p_BOL_No_To IN VARCHAR2,
318 p_Creation_Date_From IN VARCHAR2,
319 p_Creation_Date_To IN VARCHAR2,
320 p_Customer_Name IN VARCHAR2,
321 p_RunID IN INTEGER,
322 p_SHP_Interface IN VARCHAR2,
323 p_STX_Interface IN VARCHAR2,
324 p_ORD_Interface IN VARCHAR2,
325 p_OAC_Interface IN VARCHAR2,
326 p_OTX_Interface IN VARCHAR2,
327 p_DTL_Interface IN VARCHAR2,
328 p_DAC_Interface IN VARCHAR2,
329 P_DTX_Interface IN VARCHAR2,
330 p_ALL_Interface IN VARCHAR2 )
331
332 IS
333
334 /* Variable definitions. 'Source_tbl_type' is a PL/SQL table typedef */
335 /* with the following structure: */
336 /* data_loc_id NUMBER */
337 /* table_name VARCHAR2(50) */
338 /* column_name VARCHAR2(50) */
339 /* base_table_name VARCHAR2(50) */
340 /* base_column_name VARCHAR2(50) */
341 /* xref_category_id NUMBER */
342 /* xref_key1_source_column VARCHAR2(50) */
343 /* xref_key2_source_column VARCHAR2(50) */
344 /* xref_key3_source_column VARCHAR2(50) */
345 /* xref_key4_source_column VARCHAR2(50) */
346 /* xref_key5_source_column VARCHAR2(50) */
347 /* data_type VARCHAR2(50) */
348 /* data_length NUMBER */
349 /* int_val VARCHAR2(400) */
350 /* ext_val1 VARCHAR2(80) */
351 /* ext_val2 VARCHAR2(80) */
352 /* ext_val3 VARCHAR2(80) */
353 /* ext_val4 VARCHAR2(80) */
354 /* ext_val5 VARCHAR2(80) */
355
356
357 xProgress VARCHAR2(30);
358
359 v_SHP_Table ece_flatfile_pvt.Interface_tbl_type;
360 v_STX_Table ece_flatfile_pvt.Interface_tbl_type;
361 v_ORD_Table ece_flatfile_pvt.Interface_tbl_type;
362 v_OAC_Table ece_flatfile_pvt.Interface_tbl_type;
363 v_OTX_Table ece_flatfile_pvt.Interface_tbl_type;
364 v_DTL_Table ece_flatfile_pvt.Interface_tbl_type;
365 v_DAC_Table ece_flatfile_pvt.Interface_tbl_type;
366 v_DTX_Table ece_flatfile_pvt.Interface_tbl_type;
367 v_ALL_Table ece_flatfile_pvt.Interface_tbl_type;
368 v_CrossRefTable ece_flatfile_pvt.Interface_tbl_type;
369
370 v_SHP_Cursor INTEGER;
371 v_STX_Cursor INTEGER;
372 v_ORD_Cursor INTEGER;
373 v_OAC_Cursor INTEGER;
374 v_OTX_Cursor INTEGER;
375 v_DTL_Cursor INTEGER;
376 v_DAC_Cursor INTEGER;
377 v_DTX_Cursor INTEGER;
378 v_ALL_Cursor INTEGER;
379
380 v_SHP_Select VARCHAR2(32000);
381 v_STX_Select VARCHAR2(32000);
382 v_ORD_Select VARCHAR2(32000);
383 v_OAC_Select VARCHAR2(32000);
384 v_OTX_Select VARCHAR2(32000);
385 v_DTL_Select VARCHAR2(32000);
386 v_DAC_Select VARCHAR2(32000);
387 v_DTX_Select VARCHAR2(32000);
388 v_ALL_Select VARCHAR2(32000);
389
390
391 v_SHP_From VARCHAR2(32000);
392 v_STX_From VARCHAR2(32000);
393 v_ORD_From VARCHAR2(32000);
394 v_OAC_From VARCHAR2(32000);
395 v_OTX_From VARCHAR2(32000);
396 v_DTL_From VARCHAR2(32000);
397 v_DAC_From VARCHAR2(32000);
398 v_DTX_From VARCHAR2(32000);
399 v_ALL_From VARCHAR2(32000);
400
401 v_SHP_Where VARCHAR2(32000);
402 v_STX_Where VARCHAR2(32000);
403 v_ORD_Where VARCHAR2(32000);
404 v_OAC_Where VARCHAR2(32000);
405 v_OTX_Where VARCHAR2(32000);
406 v_DTL_Where VARCHAR2(32000);
407 v_DAC_Where VARCHAR2(32000);
408 v_DTX_Where VARCHAR2(32000);
409 v_ALL_Where VARCHAR2(32000);
410
411
412 v_SHP_Count INTEGER := 0;
413 v_STX_Count INTEGER := 0;
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 v_CrossRefCount INTEGER := 0;
422
423 v_SHP_Key NUMBER;
424 v_STX_Key NUMBER;
425 v_ORD_Key NUMBER;
426 v_OAC_Key NUMBER;
427 v_OTX_Key NUMBER;
428 v_DTL_Key NUMBER;
429 v_DAC_Key NUMBER;
430 v_DTX_Key NUMBER;
431 v_ALL_Key NUMBER;
432
433 v_Dummy INTEGER;
434 v_Orgn_Code VARCHAR2(32);
435 v_BOL_No_From VARCHAR2(32);
436 v_BOL_No_To VARCHAR2(32);
437
438 /* SFeinstein 10/27/99 B911176: changed format of date for Y2K
439 v_Creation_Date_From VARCHAR2(15);
440 v_Creation_Date_To VARCHAR2(15);
441 */
442 v_Creation_Date_From DATE := TO_DATE(p_Creation_Date_From,'YYYY/MM/DD HH24:MI:SS');
443 v_Creation_Date_To DATE := TO_DATE(p_Creation_Date_To,'YYYY/MM/DD HH24:MI:SS') + 1;
444 /* end B911176 fix */
445
446 v_Customer_Name VARCHAR2(32);
447 v_BOL_ID_Position INTEGER;
448 V_BOL_ID INTEGER;
449 v_Line_ID_Position INTEGER;
450 v_Line_ID INTEGER;
451 v_Order_ID INTEGER;
452 v_Order_ID_Position INTEGER;
453 v_RunIDPosition INTEGER;
454 v_TimeStampSequence INTEGER;
455 v_TimeStampPosition INTEGER;
456 v_WarehouseCodeIntPos INTEGER;
457 v_TransactionRefKeyPos INTEGER;
458 v_TimeStampDate DATE;
459 v_ReturnStatus VARCHAR2(10);
460 v_MessageCount NUMBER;
461 v_MessageData VARCHAR2(255);
462 v_OutputLevel VARCHAR2(30);
463
464 v_assignment_type NUMBER;
465 v_format_size NUMBER;
466 v_pad_char VARCHAR2(1);
467
468 BEGIN
469
470 /*
471
472 Debug statements for the parameter values.
473
474 */
475
476 ec_debug.push ( 'GML_GASNO.Populate_Interface_Tables' );
477 ec_debug.pl ( 3, 'p_CommunicationMethod: ', p_CommunicationMethod );
478 ec_debug.pl ( 3, 'p_TransactionType: ', p_TransactionType );
479 ec_debug.pl ( 3, 'p_Orgn_Code: ', p_Orgn_Code );
480 ec_debug.pl ( 3, 'p_BOL_No_From: ', p_BOL_No_From );
481 ec_debug.pl ( 3, 'p_BOL_No_To: ', p_BOL_No_To );
482 ec_debug.pl ( 3, 'p_Creation_Date_From: ', p_Creation_Date_From );
483 ec_debug.pl ( 3, 'p_Creation_Date_To: ', p_Creation_Date_To );
484 ec_debug.pl ( 3, 'p_Customer_Name: ', p_Customer_Name );
485 ec_debug.pl ( 3, 'p_RunID: ', p_RunID );
486 ec_debug.pl ( 3, 'p_SHP_Interface: ', p_SHP_Interface );
487 ec_debug.pl ( 3, 'p_STX_Interface: ', p_STX_Interface );
488 ec_debug.pl ( 3, 'p_ORD_Interface: ', p_ORD_Interface );
489 ec_debug.pl ( 3, 'p_OAC_Interface: ', p_OAC_Interface );
490 ec_debug.pl ( 3, 'p_OTX_Interface: ', p_OTX_Interface );
491 ec_debug.pl ( 3, 'p_DTL_Interface: ', p_DTL_Interface );
492 ec_debug.pl ( 3, 'p_DAC_Interface: ', p_DAC_Interface );
493 ec_debug.pl ( 3, 'p_DTX_Interface: ', p_DTX_Interface );
494 ec_debug.pl ( 3, 'p_ALL_Interface: ', p_ALL_Interface );
495
496 /*
497 Load each PL/SQL table. The FOR loop implicitly handles all
498 cursor processing.
499 */
500 v_Orgn_Code := p_Orgn_Code;
501 ec_debug.pl ( 3, 'v_Orgn_Code: ', v_Orgn_Code );
502 v_BOL_No_From := p_BOL_No_From;
503 ec_debug.pl ( 3, 'v_BOL_No_From: ', v_BOL_No_From );
504 v_BOL_No_To := p_BOL_No_To;
505 ec_debug.pl ( 3, 'v_BOL_No_To: ', v_BOL_No_To );
506 /* SFeinstein 10/27/99 B911176: removed code for format change above
507 v_Creation_Date_From := p_Creation_Date_From;
508 v_Creation_Date_To := p_Creation_Date_To;
509 */
510 ec_debug.pl ( 3, 'v_Creation_Date_From: ', v_Creation_Date_From );
511 ec_debug.pl ( 3, 'v_Creation_Date_To: ', v_Creation_Date_To );
512 v_Customer_Name := p_Customer_Name;
513 ec_debug.pl ( 3, 'v_Customer_Name: ', v_Customer_Name );
514
515
516 /*
517 Pad right charactors before querying numbers.
518 */
519 xProgress := 'GPOAOB-10-0010';
520 ec_debug.pl ( 3, 'p_BOL_No_From: ', p_BOL_No_From);
521 /* Get doc numbering info to properlly format doc numbers entered */
522 SELECT
523 assignment_type,
524 format_size,
525 nvl(pad_char,' ')
526 INTO
527 v_assignment_type,
528 v_format_size,
529 v_pad_char
530 FROM
531 sy_docs_seq
532 WHERE
533 orgn_code=v_Orgn_Code AND
534 doc_type='OPSP'
535 ;
536
537 ec_debug.pl ( 3, 'v_assignment_type: ',v_assignment_type);
538 If ( v_assignment_type = 2 ) Then /* If automatic document numbering */
539 If ( p_BOL_No_From is NOT NULL ) Then
540 v_BOL_No_From := lpad(p_BOL_No_From, v_format_size, v_pad_char);
541 ec_debug.pl ( 3, 'v_BOL_No_From : ', v_BOL_No_From);
542 SELECT
543 lpad(p_BOL_No_From, v_format_size, v_pad_char)
544 INTO
545 v_BOL_No_From
546 FROM
547 dual
548 ;
549 End If;
550 If ( p_BOL_No_To is NOT NULL ) Then
551 v_BOL_No_To := lpad(p_BOL_No_To, v_format_size, v_pad_char);
552 ec_debug.pl ( 3, 'v_BOL_No_To : ',v_BOL_No_To );
553 SELECT
554 lpad(p_BOL_No_To, v_format_size, v_pad_char)
555 INTO
556 v_BOL_No_To
557 FROM
558 dual
559 ;
560 End If;
561 End If;
562
563 ec_debug.pl ( 3, 'v_BOL_No_From : ',v_BOL_No_From );
564 ec_debug.pl ( 3, 'v_BOL_No_To : ',v_BOL_No_To );
565
566 xProgress := 'GASNOB-10-1000';
567 ece_flatfile_pvt.init_table(p_TransactionType,p_SHP_Interface,NULL,FALSE,v_SHP_Table,v_CrossRefTable);
568
569 /* */
570 /* Initialize the Cross Reference PL/SQL table. This table is a */
571 /* concatenation of all the interface PL/SQL tables. */
572 /* */
573
574 v_CrossRefTable := v_SHP_Table;
575 xProgress := 'GASNOB-10-1020';
576 v_CrossRefCount := v_SHP_Table.COUNT;
577 ec_debug.pl ( 3, 'v_CrossRefCount: ', v_CrossRefCount );
578
579 xProgress := 'GASNOB-10-1030';
580 ece_flatfile_pvt.init_table(p_TransactionType,p_STX_Interface,NULL,TRUE,v_STX_Table,v_CrossRefTable);
581
582 xProgress := 'GASNOB-10-1070';
583 ece_flatfile_pvt.init_table(p_TransactionType,p_ORD_Interface,NULL,TRUE,v_ORD_Table,v_CrossRefTable);
584
585 xProgress := 'GASNOB-10-1100';
586 ece_flatfile_pvt.init_table(p_TransactionType,p_OAC_Interface,NULL,TRUE,v_OAC_Table,v_CrossRefTable);
587
588 xProgress := 'GASNOB-10-1130';
589 ece_flatfile_pvt.init_table(p_TransactionType,p_OTX_Interface,NULL,TRUE,v_OTX_Table,v_CrossRefTable);
590
591 xProgress := 'GASNOB-10-1160';
592 ece_flatfile_pvt.init_table(p_TransactionType,p_DTL_Interface,NULL,TRUE,v_DTL_Table,v_CrossRefTable);
593
594
595 xProgress := 'GASNOB-10-1190';
596 ece_flatfile_pvt.init_table(p_TransactionType,p_DAC_Interface,NULL,TRUE,v_DAC_Table,v_CrossRefTable);
597
598 xProgress := 'GASNOB-10-1211';
599 ece_flatfile_pvt.init_table(p_TransactionType,p_DTX_Interface,NULL,TRUE,v_DTX_Table,v_CrossRefTable);
600
601 xProgress := 'GASNOB-10-1213';
602 ece_flatfile_pvt.init_table(p_TransactionType,p_ALL_Interface,NULL,TRUE,v_ALL_Table,v_CrossRefTable);
603
604
605 /* */
606 /* The 'select_clause' procedure will build the SELECT, FROM and WHERE */
607 /* clauses in preparation for the dynamic SQL call using the EDI data */
608 /* dictionary for the build. Any necessary customizations to these */
609 /* clauses need to be made *after* the clause is built, but *before* */
610 /* the SQL call. */
611 /* */
612
613 xProgress := 'GASNOB-10-1220';
614 ece_extract_utils_pub.select_clause ( p_TransactionType,
615 p_CommunicationMethod,
616 p_SHP_Interface,
617 v_SHP_Table,
618 v_SHP_Select,
619 v_SHP_From,
620 v_SHP_Where );
621
622 xProgress := 'GASNOB-10-1230';
623 ece_extract_utils_pub.select_clause ( p_TransactionType,
624 p_CommunicationMethod,
625 p_STX_Interface,
626 v_STX_Table,
627 v_STX_Select,
628 v_STX_From,
629 v_STX_Where );
630
631 xProgress := 'GASNOB-10-1240';
632 ece_extract_utils_pub.select_clause ( p_TransactionType,
633 p_CommunicationMethod,
634 p_ORD_Interface,
635 v_ORD_Table,
636 v_ORD_Select,
637 v_ORD_From,
638 v_ORD_Where );
639
640 xProgress := 'GASNOB-10-1250';
641 ece_extract_utils_pub.select_clause ( p_TransactionType,
642 p_CommunicationMethod,
643 p_OAC_Interface,
644 v_OAC_Table,
645 v_OAC_Select,
646 v_OAC_From,
647 v_OAC_Where );
648
649 xProgress := 'GASNOB-10-1260';
650 ece_extract_utils_pub.select_clause ( p_TransactionType,
651 p_CommunicationMethod,
652 p_OTX_Interface,
653 v_OTX_Table,
654 v_OTX_Select,
655 v_OTX_From,
656 v_OTX_Where );
657
658 xProgress := 'GASNOB-10-1270';
659 ece_extract_utils_pub.select_clause ( p_TransactionType,
660 p_CommunicationMethod,
661 p_DTL_Interface,
662 v_DTL_Table,
663 v_DTL_Select,
664 v_DTL_From,
665 v_DTL_Where );
666
667 xProgress := 'GASNOB-10-1280';
668 ece_extract_utils_pub.select_clause ( p_TransactionType,
669 p_CommunicationMethod,
670 p_DAC_Interface,
671 v_DAC_Table,
672 v_DAC_Select,
673 v_DAC_From,
674 v_DAC_Where );
675
676 xProgress := 'GASNOB-10-1281';
677 ece_extract_utils_pub.select_clause ( p_TransactionType,
678 p_CommunicationMethod,
679 P_DTX_Interface,
680 v_DTX_Table,
681 v_DTX_Select,
682 v_DTX_From,
683 v_DTX_Where );
684 xProgress := 'GASNOB-10-1281';
685 ece_extract_utils_pub.select_clause ( p_TransactionType,
686 p_CommunicationMethod,
687 p_ALL_Interface,
688 v_ALL_Table,
689 v_ALL_Select,
690 v_ALL_From,
691 v_ALL_Where );
692
693 /* */
694 /* Customize the WHERE clauses to use the elegible Deliveries for */
695 /* this Departure. */
696 /* */
697
698 xProgress := 'GASNOB-10-1290';
699 v_SHP_Where := v_SHP_Where || p_SHP_Interface || '_V.Orgn_Code = :Orgn_Code';
700
701 If v_BOL_No_From is not NULL Then
702 If v_BOL_No_To is not NULL Then
703 /* Specify range in where clause */
704 v_SHP_Where := v_SHP_Where || ' AND ' ||
705 p_SHP_Interface || '_V.BOL_NO >= :BOL_No_From' || ' AND ' ||
706 p_SHP_Interface || '_V.BOL_NO <= :BOL_No_To';
707 Else
708 /* Specify match */
709 v_SHP_Where := v_SHP_Where || ' AND ' ||
710 p_SHP_Interface || '_V.BOL_NO = :BOL_No_From';
711 End If;
712 End If;
713
714 If v_Creation_Date_From is not NULL Then
715 If v_Creation_Date_To is not NULL Then
716 /* Specify range in where clause */
717 v_SHP_Where := v_SHP_Where || ' AND ' ||
718 p_SHP_Interface || '_V.CREATION_DATE >= :Creation_Date_From' || ' AND ' ||
719 'trunc(' || p_SHP_Interface || '_V.CREATION_DATE) <= :Creation_Date_To';
720 Else
721 /* Specify match */
722 v_SHP_Where := v_SHP_Where || ' AND ' ||
723 'trunc(' || p_SHP_Interface || '_V.CREATION_DATE) = :Creation_Date_From';
724 End If;
725 End If;
726
727 If v_Customer_Name is not NULL Then
728 /* Specify match */
729 v_SHP_Where := v_SHP_Where || ' AND ' ||
730 p_SHP_Interface || '_V.SHIPTO_CUST_NAME = :Customer_Name';
731 End If;
732
733 xProgress := 'GASNOB-10-1300';
734 v_STX_Where := v_STX_Where ||
735 p_STX_Interface || '_V.BOL_ID = :BOL_ID' || ' AND ' ||
736 p_STX_Interface || '_V.Line_No > 0';
737
738 xProgress := 'GASNOB-10-1310';
739 v_ORD_Where := v_ORD_Where ||
740 p_ORD_Interface || '_V.Order_ID IN ' ||
741 '( SELECT DISTINCT Order_ID ' ||
742 ' FROM ' || p_DTL_Interface || '_V ' ||
743 ' WHERE ' || p_DTL_Interface || '_V.BOL_ID = :BOL_ID ) ';
744
745 xProgress := 'GASNOB-10-1320';
746 v_OAC_Where := v_OAC_Where ||
747 p_OAC_Interface || '_V.Order_ID = :Order_ID';
748
749 xProgress := 'GASNOB-10-1330';
750 v_OTX_Where := v_OTX_Where ||
751 p_OTX_Interface || '_V.ORDER_ID = :Order_Id' || ' AND ' ||
752 p_OTX_Interface || '_V.LINE_NO > 0';
753
754 xProgress := 'GASNOB-10-1340';
755 v_DTL_Where := v_DTL_Where ||
756 p_DTL_Interface || '_V.BOL_ID = :BOL_ID' || ' AND ' ||
757 p_DTL_Interface || '_V.ORDER_ID = :Order_Id';
758
759 xProgress := 'GASNOB-10-1350';
760 v_DAC_Where := v_DAC_Where ||
761 p_DAC_Interface || '_V.Line_ID = :Line_ID';
762
763 xProgress := 'GASNOB-10-1351';
764 v_DTX_Where := v_DTX_Where ||
765 p_DTX_Interface || '_V.Line_ID = :Line_ID' || ' AND ' ||
766 p_DTX_Interface || '_V.LINE_NO > 0';
767
768 xProgress := 'GASNOB-10-1352';
769 v_ALL_Where := v_ALL_Where ||
770 p_ALL_Interface || '_V.Line_ID = :Line_ID';
771
772 /* */
773 /* Build the complete SELECT statement for each level. */
774 /* */
775
776 xProgress := 'GASNOB-10-1360';
777 v_SHP_Select := v_SHP_Select ||
778 v_SHP_From ||
779 v_SHP_Where;
780 ec_debug.pl ( 3, 'v_SHP_Select: ', v_SHP_Select );
781
782 v_STX_Select := v_STX_Select ||
783 v_STX_From ||
784 v_STX_Where;
785 ec_debug.pl ( 3, 'v_STX_Select: ', v_STX_Select );
786
787 v_ORD_Select := v_ORD_Select ||
788 v_ORD_From ||
789 v_ORD_Where;
790 ec_debug.pl ( 3, 'v_ORD_Select: ', v_ORD_Select );
791
792 v_OAC_Select := v_OAC_Select ||
793 v_OAC_From ||
794 v_OAC_Where;
795 ec_debug.pl ( 3, 'v_OAC_Select: ', v_OAC_Select );
796
797 v_OTX_Select := v_OTX_Select ||
798 v_OTX_From ||
799 v_OTX_Where;
800 ec_debug.pl ( 3, 'v_OTX_Select: ', v_OTX_Select );
801
802 v_DTL_Select := v_DTL_Select ||
803 v_DTL_From ||
804 v_DTL_Where;
805 ec_debug.pl ( 3, 'v_DTL_Select: ', v_DTL_Select );
806
807 v_DAC_Select := v_DAC_Select ||
808 v_DAC_From ||
809 v_DAC_Where;
810 ec_debug.pl ( 3, 'v_DAC_Select: ', v_DAC_Select );
811
812 v_DTX_Select := v_DTX_Select ||
813 v_DTX_From ||
814 v_DTX_Where;
815 ec_debug.pl ( 3, 'v_DTX_Select: ', v_DTX_Select );
816
817 v_ALL_Select := v_ALL_Select ||
818 v_ALL_From ||
819 v_ALL_Where;
820 /* */
821 /* Open a cursor for each of the SELECT calls. This tells the */
822 /* database to reserve space for the data returned by the SELECT */
823 /* statement. */
824 /* */
825
826 xProgress := 'GASNOB-10-1370';
827 v_SHP_Cursor := dbms_sql.open_cursor;
828
829 xProgress := 'GASNOB-10-1372';
830 v_STX_Cursor := dbms_sql.open_cursor;
831
832 xProgress := 'GASNOB-10-1374';
833 v_ORD_Cursor := dbms_sql.open_cursor;
834
835 xProgress := 'GASNOB-10-1376';
836 v_OAC_Cursor := dbms_sql.open_cursor;
837
838 xProgress := 'GASNOB-10-1378';
839 v_OTX_Cursor := dbms_sql.open_cursor;
840
841 xProgress := 'GASNOB-10-1380';
842 v_DTL_Cursor := dbms_sql.open_cursor;
843
844 xProgress := 'GASNOB-10-1382';
845 v_DAC_Cursor := dbms_sql.open_cursor;
846
847 xProgress := 'GASNOB-10-1384';
848 v_DTX_Cursor := dbms_sql.open_cursor;
849
850 xProgress := 'GASNOB-10-1386';
851 v_ALL_Cursor := dbms_sql.open_cursor;
852
853 /* */
854 /* Parse each SELECT statement so the database understands the */
855 /* command. */
856 /* */
857
858 xProgress := 'GASNOB-10-1390';
859 BEGIN
860 dbms_sql.parse ( v_SHP_Cursor,
861 v_SHP_Select,
862 dbms_sql.native );
863 EXCEPTION
864 WHEN OTHERS THEN
865 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
866 v_SHP_Select );
867 app_exception.raise_exception;
868 END;
869
870 xProgress := 'GASNOB-10-1400';
871 BEGIN
872 dbms_sql.parse ( v_STX_Cursor,
873 v_STX_Select,
874 dbms_sql.native );
875 EXCEPTION
876 WHEN OTHERS THEN
877 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
878 v_STX_Select );
879 app_exception.raise_exception;
880 END;
881
882 /* dbms_output.put_line( ' Length of ORD Select = ' || length( v_ORD_Select)); */
883
884 xProgress := 'GASNOB-10-1410';
885 BEGIN
886 dbms_sql.parse ( v_ORD_Cursor,
887 v_ORD_Select,
888 dbms_sql.native );
889 EXCEPTION
890 WHEN OTHERS THEN
891 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
892 v_ORD_Select );
893 app_exception.raise_exception;
894 END;
895
896 xProgress := 'GASNOB-10-1420';
897 BEGIN
898 dbms_sql.parse ( v_OAC_Cursor,
899 v_OAC_Select,
900 dbms_sql.native );
901 EXCEPTION
902 WHEN OTHERS THEN
903 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
904 v_OAC_Select );
905 app_exception.raise_exception;
906 END;
907
908 xProgress := 'GASNOB-10-1430';
909 BEGIN
910 dbms_sql.parse ( v_OTX_Cursor,
911 v_OTX_Select,
912 dbms_sql.native );
913 EXCEPTION
914 WHEN OTHERS THEN
915 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
916 v_OTX_Select );
917 app_exception.raise_exception;
918 END;
919
920 /*
921 dbms_output.put_line( ' Length of DTL Select = ' || length( v_DTL_Select));
922
923 dbms_output.put_line( substr( v_DTL_Select,1,100));
924 dbms_output.put_line( substr( v_DTL_Select,101,100));
925 dbms_output.put_line( substr( v_DTL_Select,201,100));
926 dbms_output.put_line( substr( v_DTL_Select,301,100));
927 dbms_output.put_line( substr( v_DTL_Select,401,100));
928 dbms_output.put_line( substr( v_DTL_Select,501,100));
929 dbms_output.put_line( substr( v_DTL_Select,601,100));
930 dbms_output.put_line( substr( v_DTL_Select,701,100));
931 dbms_output.put_line( substr( v_DTL_Select,801,100));
932 dbms_output.put_line( substr( v_DTL_Select,901,100));
933 dbms_output.put_line( substr( v_DTL_Select,1001,100));
934 dbms_output.put_line( substr( v_DTL_Select,1101,100));
935 dbms_output.put_line( substr( v_DTL_Select,1201,100));
936 dbms_output.put_line( substr( v_DTL_Select,1301,100));
937 dbms_output.put_line( substr( v_DTL_Select,1401,100));
938 dbms_output.put_line( substr( v_DTL_Select,1501,100));
939 dbms_output.put_line( substr( v_DTL_Select,1601,100));
940 dbms_output.put_line( substr( v_DTL_Select,1701,100));
941 dbms_output.put_line( substr( v_DTL_Select,1801,100));
942 dbms_output.put_line( substr( v_DTL_Select,1901,100));
943 dbms_output.put_line( substr( v_DTL_Select,2001,100));
944 dbms_output.put_line( substr( v_DTL_Select,2101,100));
945 dbms_output.put_line( substr( v_DTL_Select,2201,100));
946 dbms_output.put_line( substr( v_DTL_Select,2301,100));
947 dbms_output.put_line( substr( v_DTL_Select,2401,100));
948 dbms_output.put_line( substr( v_DTL_Select,2501,100));
949 dbms_output.put_line( substr( v_DTL_Select,2601,100));
950 dbms_output.put_line( substr( v_DTL_Select,2701,100));
951 dbms_output.put_line( substr( v_DTL_Select,2801,100));
952 dbms_output.put_line( substr( v_DTL_Select,2901,100));
953 dbms_output.put_line( substr( v_DTL_Select,3001,100));
954 */
955
956 xProgress := 'GASNOB-10-1440';
957 BEGIN
958 dbms_sql.parse ( v_DTL_Cursor,
959 v_DTL_Select,
960 dbms_sql.native );
961 EXCEPTION
962 WHEN OTHERS THEN
963 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
964 v_DTL_Select );
965 app_exception.raise_exception;
966 END;
967
968 xProgress := 'GASNOB-10-1450';
969 BEGIN
970 dbms_sql.parse ( v_DAC_Cursor,
971 v_DAC_Select,
972 dbms_sql.native );
973 EXCEPTION
974 WHEN OTHERS THEN
975 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
976 v_DAC_Select );
977 app_exception.raise_exception;
978 END;
979
980 xProgress := 'GASNOB-10-1451';
981 BEGIN
982 dbms_sql.parse ( v_DTX_Cursor,
983 v_DTX_Select,
984 dbms_sql.native );
985 EXCEPTION
986 WHEN OTHERS THEN
987 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
988 v_DTX_Select );
989 app_exception.raise_exception;
990 END;
991
992 xProgress := 'GASNOB-10-1452';
993 BEGIN
994 dbms_sql.parse ( v_ALL_Cursor,
995 v_ALL_Select,
996 dbms_sql.native );
997 EXCEPTION
998 WHEN OTHERS THEN
999 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
1000 v_ALL_Select );
1001 app_exception.raise_exception;
1002 END;
1003
1004 /* set counters */
1005 xProgress := 'GASNOB-10-1453';
1006 v_SHP_Count := v_SHP_table.COUNT;
1007 ec_debug.pl (3, 'v_SHP_Count: ', v_SHP_Count);
1008
1009 xProgress := 'GASNOB-10-1454';
1010 v_STX_Count := v_STX_table.COUNT;
1011 ec_debug.pl (3, 'v_STX_Count: ', v_STX_Count);
1012
1013 xProgress := 'GASNOB-10-1455';
1014 v_ORD_Count := v_ORD_table.COUNT;
1015 ec_debug.pl (3, 'v_ORD_Count: ', v_ORD_Count);
1016
1017 xProgress := 'GASNOB-10-1456';
1018 v_OAC_Count := v_OAC_table.COUNT;
1019 ec_debug.pl (3, 'v_OAC_Count: ', v_OAC_Count);
1020
1021 xProgress := 'GASNOB-10-1457';
1022 v_OTX_Count := v_OTX_table.COUNT;
1023 ec_debug.pl (3, 'v_OTX_Count: ', v_OTX_Count);
1024
1025 xProgress := 'GASNOB-10-1458';
1026 v_DTL_Count := v_DTL_table.COUNT;
1027 ec_debug.pl (3, 'v_DTL_Count: ', v_DTL_Count);
1028
1029 xProgress := 'GASNOB-10-1459';
1030 v_DAC_Count := v_DAC_table.COUNT;
1031 ec_debug.pl (3, 'v_DAC_Count: ', v_DAC_Count);
1032
1033 xProgress := 'GASNOB-10-1460';
1034 v_DTX_Count := v_DTX_table.COUNT;
1035 ec_debug.pl (3, 'v_DTX_Count: ', v_DTX_Count);
1036
1037 xProgress := 'GASNOB-10-1461';
1038 v_ALL_Count := v_ALL_table.COUNT;
1039 ec_debug.pl (3, 'v_ALL_Count: ', v_ALL_Count);
1040
1041 /* */
1042 /* Define the data type for every column in each SELECT statement */
1043 /* so the database understands how to populate it. Using the */
1044 /* K.I.S.S. principle, every data type will be converted to */
1045 /* VARCHAR2. */
1046 /* */
1047
1048 xProgress := 'GASNOB-10-1466';
1049 FOR v_LoopCount IN 1..v_SHP_Count
1050 LOOP
1051 xProgress := 'GASNOB-10-1470';
1052 dbms_sql.define_column ( v_SHP_Cursor,
1053 v_LoopCount,
1054 v_SHP_Select,
1055 ece_extract_utils_pub.G_MaxColWidth );
1056 END LOOP;
1057
1058 xProgress := 'GASNOB-10-1480';
1059 FOR v_LoopCount IN 1..v_STX_Count
1060 LOOP
1061 xProgress := 'GASNOB-10-1490';
1062 dbms_sql.define_column ( v_STX_Cursor,
1063 v_LoopCount,
1064 v_STX_Select,
1065 ece_extract_utils_pub.G_MaxColWidth );
1066 END LOOP;
1067
1068 xProgress := 'GASNOB-10-1500';
1069 FOR v_LoopCount IN 1..v_ORD_Count
1070 LOOP
1071 xProgress := 'GASNOB-10-1510';
1072 dbms_sql.define_column ( v_ORD_Cursor,
1073 v_LoopCount,
1074 v_ORD_Select,
1075 ece_extract_utils_pub.G_MaxColWidth );
1076 END LOOP;
1077
1078 xProgress := 'GASNOB-10-1520';
1079 FOR v_LoopCount IN 1..v_OAC_Count
1080 LOOP
1081 xProgress := 'GASNOB-10-1530';
1082 dbms_sql.define_column ( v_OAC_Cursor,
1083 v_LoopCount,
1084 v_OAC_Select,
1085 ece_extract_utils_pub.G_MaxColWidth );
1086 END LOOP;
1087
1088 xProgress := 'GASNOB-10-1540';
1089 FOR v_LoopCount IN 1..v_OTX_Count
1090 LOOP
1091 xProgress := 'GASNOB-10-1550';
1092 dbms_sql.define_column ( v_OTX_Cursor,
1093 v_LoopCount,
1094 v_OTX_Select,
1095 ece_extract_utils_pub.G_MaxColWidth );
1096 END LOOP;
1097
1098 xProgress := 'GASNOB-10-1560';
1099 FOR v_LoopCount IN 1..v_DTL_Count
1100 LOOP
1101 xProgress := 'GASNOB-10-1570';
1102 dbms_sql.define_column ( v_DTL_Cursor,
1103 v_LoopCount,
1104 v_DTL_Select,
1105 ece_extract_utils_pub.G_MaxColWidth );
1106 END LOOP;
1107
1108 xProgress := 'GASNOB-10-1580';
1109 FOR v_LoopCount IN 1..v_DAC_Count
1110 LOOP
1111 xProgress := 'GASNOB-10-1590';
1112 dbms_sql.define_column ( v_DAC_Cursor,
1113 v_LoopCount,
1114 v_DAC_Select,
1115 ece_extract_utils_pub.G_MaxColWidth );
1116 END LOOP;
1117
1118 xProgress := 'GASNOB-10-1580';
1119 FOR v_LoopCount IN 1..v_DTX_Count
1120 LOOP
1121 xProgress := 'GASNOB-10-1590';
1122 dbms_sql.define_column ( v_DTX_Cursor,
1123 v_LoopCount,
1124 v_DTX_Select,
1125 ece_extract_utils_pub.G_MaxColWidth );
1126 END LOOP;
1127
1128 xProgress := 'GASNOB-10-1580';
1129 FOR v_LoopCount IN 1..v_ALL_Count
1130 LOOP
1131 xProgress := 'GASNOB-10-1590';
1132 dbms_sql.define_column ( v_ALL_Cursor,
1133 v_LoopCount,
1134 v_ALL_Select,
1135 ece_extract_utils_pub.G_MaxColWidth );
1136 END LOOP;
1137
1138 /* */
1139 /* */
1140 /* Bind the variables in the Delivery level SELECT clause. */
1141 /* */
1142
1143 xProgress := 'GASNOB-10-1600';
1144 dbms_sql.bind_variable ( v_SHP_Cursor,
1145 'Orgn_Code',
1146 v_Orgn_Code );
1147
1148 If v_BOL_No_From is not NULL Then
1149 xProgress := 'GASNOB-10-1601';
1150 dbms_sql.bind_variable ( v_SHP_Cursor,
1151 'BOL_No_From',
1152 v_BOL_No_From );
1153 If v_BOL_No_To is not NULL Then
1154 xProgress := 'GASNOB-10-1602';
1155 dbms_sql.bind_variable ( v_SHP_Cursor,
1156 'BOL_No_To',
1157 v_BOL_No_To );
1158 End If;
1159 End If;
1160
1161 If v_Creation_Date_From is not NULL Then
1162 xProgress := 'GASNOB-10-1603';
1163 dbms_sql.bind_variable ( v_SHP_Cursor,
1164 'Creation_Date_From',
1165 v_Creation_Date_From );
1166 If v_Creation_Date_To is not NULL Then
1167 xProgress := 'GASNOB-10-1604';
1168 dbms_sql.bind_variable ( v_SHP_Cursor,
1169 'Creation_Date_To',
1170 v_Creation_Date_To );
1171 End If;
1172 End If;
1173
1174 If v_Customer_Name is not NULL Then
1175 xProgress := 'GASNOB-10-1705';
1176 dbms_sql.bind_variable ( v_SHP_Cursor,
1177 'Customer_Name',
1178 v_Customer_Name );
1179 End If;
1180
1181 /* */
1182 /* Execute the SHP level SELECT statement. */
1183 /* */
1184
1185 xProgress := 'GASNOB-10-1610';
1186 v_Dummy := dbms_sql.execute ( v_SHP_Cursor );
1187
1188 /* */
1189 /* Begin the SHP level loop. */
1190 /* */
1191
1192 xProgress := 'GASNOB-10-1620';
1193 WHILE dbms_sql.fetch_rows ( v_SHP_Cursor ) > 0
1194 LOOP
1195
1196 /* */
1197 /* Store the returned values in the PL/SQL table. */
1198 /* */
1199
1200 xProgress := 'GASNOB-10-1630';
1201 FOR v_LoopCount IN 1..v_SHP_Count
1202 LOOP
1203 xProgress := 'GASNOB-10-1640';
1204 dbms_sql.column_value ( v_SHP_Cursor,
1205 v_LoopCount,
1206 v_SHP_Table(v_LoopCount).value );
1207 END LOOP;
1208
1209 /* */
1210 /* Find the column position of the BOL_ID in the PL/SQL table */
1211 /* and use the value stored in that column to bind the variables in */
1212 /* the SELECT clauses of the other levels. */
1213 /* */
1214
1215 xProgress := 'GASNOB-10-1650';
1216 ece_extract_utils_pub.find_pos ( v_SHP_Table,
1217 'BOL_ID',
1218 V_BOL_ID_Position );
1219 /* */
1220 /* Everything is stored in the PL/SQL table as VARCHAR2, so convert */
1221 /* the BOL_ID value to NUMBER. */
1222 /* */
1223
1224 xProgress := 'GASNOB-10-1660';
1225 v_BOL_ID := TO_NUMBER ( v_SHP_Table(v_BOL_ID_Position).value );
1226 ec_debug.pl ( 3, 'v_BOL_ID: ', v_BOL_ID );
1227
1228 /* */
1229 /* Cross-reference all necessary columns in the PL/SQL table. */
1230 /* */
1231
1232 xProgress := 'GASNOB-10-1800';
1233 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1234 p_Return_Status => v_ReturnStatus,
1235 p_Msg_Count => v_MessageCount,
1236 p_Msg_Data => v_MessageData,
1237 p_Key_Tbl => v_CrossRefTable,
1238 p_Tbl => v_SHP_Table );
1239
1240 /* */
1241 /* Retrieve the next sequence number for the primary key value, and */
1242 /* insert this record into the Ship interface table. */
1243 /* */
1244
1245 xProgress := 'GASNOB-10-1810';
1246 BEGIN
1247 SELECT GML_GASNO_SHIPMENTS_S.nextval
1248 INTO v_SHP_Key
1249 FROM sys.dual;
1250 EXCEPTION
1251 WHEN NO_DATA_FOUND THEN
1252 ec_debug.pl ( 0,
1253 'EC',
1254 'ECE_GET_NEXT_SEQ_FAILED',
1255 'PROGRESS_LEVEL',
1256 xProgress,
1257 'SEQ',
1258 'GML_GASNO_SHIPMENTS_S' );
1259 END;
1260 ec_debug.pl ( 3, 'v_SHP_Key: ', v_SHP_Key );
1261
1262 xProgress := 'GASNOB-10-1820';
1263 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1264 p_TransactionType,
1265 p_CommunicationMethod,
1266 p_SHP_Interface,
1267 v_SHP_Table,
1268 v_SHP_Key );
1269
1270 /* */
1271 /* Call the (customizable) procedure to populate the corresponding */
1272 /* extension table. */
1273 /* */
1274
1275 xProgress := 'GASNOB-10-1830';
1276 GML_GASNO_X.populate_SHP_ext ( v_SHP_Key, v_SHP_Table );
1277
1278 /* */
1279 /* Execute the Ship Text level SELECT statement. */
1280 /* */
1281
1282 xProgress := 'GASNOB-10-1670';
1283 dbms_sql.bind_variable ( v_STX_Cursor,
1284 'BOL_ID',
1285 v_BOL_ID );
1286
1287 xProgress := 'GASNOB-10-1840';
1288 v_Dummy := dbms_sql.execute ( v_STX_Cursor );
1289
1290 /* */
1291 /* Begin the STX level loop. */
1292 /* */
1293
1294 xProgress := 'GASNOB-10-1850';
1295 WHILE dbms_sql.fetch_rows ( v_STX_Cursor ) > 0
1296 LOOP
1297
1298 /* */
1299 /* Store the returned values in the PL/SQL table. */
1300 /* */
1301
1302 xProgress := 'GASNOB-10-1860';
1303 FOR v_LoopCount IN 1..v_STX_Count
1304 LOOP
1305 xProgress := 'GASNOB-10-1870';
1306 dbms_sql.column_value ( v_STX_Cursor,
1307 v_LoopCount,
1308 v_STX_Table(v_LoopCount).value );
1309 END LOOP;
1310
1311 /* */
1312 /* Cross-reference all necessary columns in the PL/SQL table. */
1313 /* */
1314
1315 xProgress := 'GASNOB-10-1880';
1316 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1317 p_Return_Status => v_ReturnStatus,
1318 p_Msg_Count => v_MessageCount,
1319 p_Msg_Data => v_MessageData,
1320 p_Key_Tbl => v_CrossRefTable,
1321 p_Tbl => v_STX_Table );
1322
1323 /* */
1324 /* Since this interface table is a logical extension of the Ship */
1325 /* level table, use the same key value to insert this record into */
1326 /* the Ship Text table. */
1327 /* */
1328
1329 v_STX_Key := v_SHP_Key;
1330 ec_debug.pl ( 3, 'v_STX_Key: ', v_STX_Key );
1331
1332 xProgress := 'GASNOB-10-1890';
1333 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1334 p_TransactionType,
1335 p_CommunicationMethod,
1336 p_STX_Interface,
1337 v_STX_Table,
1338 v_STX_Key );
1339
1340 /* */
1341 /* Call the (customizable) procedure to populate the corresponding */
1342 /* extension table. */
1343 /* */
1344
1345 xProgress := 'GASNOB-10-1891';
1346 GML_GASNO_X.populate_STX_ext ( v_STX_Key, v_STX_Table );
1347
1348 END LOOP; /* while stx */
1349
1350 xProgress := 'GASNOB-10-1690';
1351 dbms_sql.bind_variable ( v_ORD_Cursor,
1352 'BOL_ID',
1353 v_BOL_ID );
1354
1355 /* */
1356 /* Execute the Order level SELECT statement. */
1357 /* */
1358
1359 xProgress := 'GASNOB-10-1900';
1360 v_Dummy := dbms_sql.execute ( v_ORD_Cursor );
1361
1362 /* */
1363 /* Begin the Order level loop. */
1364 /* */
1365
1366 xProgress := 'GASNOB-10-1910';
1367 WHILE dbms_sql.fetch_rows ( v_ORD_Cursor ) > 0
1368 LOOP
1369
1370 /* */
1371 /* Store the returned values in the PL/SQL table. */
1372 /* */
1373
1374 xProgress := 'GASNOB-10-1920';
1375 FOR v_LoopCount IN 1..v_ORD_Count
1376 LOOP
1377 xProgress := 'GASNOB-10-1930';
1378 dbms_sql.column_value ( v_ORD_Cursor,
1379 v_LoopCount,
1380 v_ORD_Table(v_LoopCount).value );
1381 END LOOP;
1382
1383 /* */
1384 /* Find the column position of the Order_ID in the PL/SQL table */
1385 /* and use the value stored in that column to bind the variables in */
1386 /* the SELECT clauses of the other levels. */
1387 /* */
1388
1389 xProgress := 'GASNOB-10-1935';
1390 ece_extract_utils_pub.find_pos ( v_ORD_Table,
1391 'Order_ID',
1392 V_Order_ID_Position );
1393
1394 v_Order_ID := TO_NUMBER(v_ORD_Table(v_Order_ID_Position).value);
1395 ec_debug.pl ( 3, 'v_Order_ID: ', v_Order_ID );
1396
1397 /* */
1398 /* Cross-reference all necessary columns in the PL/SQL table. */
1399 /* */
1400
1401 xProgress := 'GASNOB-10-1940';
1402 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1403 p_Return_Status => v_ReturnStatus,
1404 p_Msg_Count => v_MessageCount,
1405 p_Msg_Data => v_MessageData,
1406 p_Key_Tbl => v_CrossRefTable,
1407 p_Tbl => v_ORD_Table );
1408
1409 /* */
1410 /* Retrieve the next sequence number for the primary key value, and */
1411 /* insert this record into the Order interface table. */
1412 /* */
1413
1414 xProgress := 'GASNOB-10-1950';
1415 BEGIN
1416 SELECT GML_GASNO_ORDERS_S.nextval
1417 INTO v_ORD_Key
1418 FROM sys.dual;
1419 EXCEPTION
1420 WHEN NO_DATA_FOUND THEN
1421 ec_debug.pl ( 0,
1422 'EC',
1423 'ECE_GET_NEXT_SEQ_FAILED',
1424 'PROGRESS_LEVEL',
1425 xProgress,
1426 'SEQ',
1427 'GML_GASNO_ORDERS_S' );
1428 END;
1429 ec_debug.pl ( 3, 'v_ORD_Key: ', v_ORD_Key );
1430
1431 xProgress := 'GASNOB-10-1960';
1432 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1433 p_TransactionType,
1434 p_CommunicationMethod,
1435 p_ORD_Interface,
1436 v_ORD_Table,
1437 v_ORD_Key );
1438
1439 /* */
1440 /* Call the (customizable) procedure to populate the corresponding */
1441 /* extension table. */
1442 /* */
1443
1444 xProgress := 'GASNOB-10-1970';
1445 GML_GASNO_X.populate_ORD_ext ( v_ORD_Key, v_ORD_Table );
1446
1447 /* */
1448 /* Execute the Charge/Allowance level SELECT statement. */
1449 /* */
1450 xProgress := 'GASNOB-10-1710';
1451 dbms_sql.bind_variable ( v_OAC_Cursor,
1452 'Order_ID',
1453 v_Order_ID );
1454
1455 xProgress := 'GASNOB-10-1980';
1456 v_Dummy := dbms_sql.execute ( v_OAC_Cursor );
1457
1458 /* */
1459 /* Begin the Order DAC level loop. */
1460 /* */
1461
1462 xProgress := 'GASNOB-10-1990';
1463 WHILE dbms_sql.fetch_rows ( v_OAC_Cursor ) > 0
1464 LOOP
1465
1466 /* */
1467 /* Store the returned values in the PL/SQL table. */
1468 /* */
1469
1470 xProgress := 'GASNOB-10-2000';
1471 FOR v_LoopCount IN 1..v_OAC_Count
1472 LOOP
1473 xProgress := 'GASNOB-10-2010';
1474 dbms_sql.column_value ( v_OAC_Cursor,
1475 v_LoopCount,
1476 v_OAC_Table(v_LoopCount).value );
1477 END LOOP;
1478
1479 /* */
1480 /* Cross-reference all necessary columns in the PL/SQL table. */
1481 /* */
1482
1483 xProgress := 'GASNOB-10-2020';
1484 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1485 p_Return_Status => v_ReturnStatus,
1486 p_Msg_Count => v_MessageCount,
1487 p_Msg_Data => v_MessageData,
1488 p_Key_Tbl => v_CrossRefTable,
1489 p_Tbl => v_OAC_Table );
1490
1491 /* */
1492 /* Use the same key value for Order DAC as for the Order table */
1493 /* */
1494
1495 v_OAC_Key := v_ORD_Key;
1496 ec_debug.pl ( 3, 'v_OAC_Key: ', v_OAC_Key );
1497
1498 xProgress := 'GASNOB-10-2040';
1499 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1500 p_TransactionType,
1501 p_CommunicationMethod,
1502 p_OAC_Interface,
1503 v_OAC_Table,
1504 v_OAC_Key );
1505
1506 /* */
1507 /* Call the (customizable) procedure to populate the corresponding */
1508 /* extension table. */
1509 /* */
1510
1511 xProgress := 'GASNOB-10-2050';
1512 GML_GASNO_X.populate_OAC_ext ( v_OAC_Key, v_OAC_Table );
1513
1514 END LOOP; /* while oac */
1515
1516 xProgress := 'GASNOB-10-1730';
1517 dbms_sql.bind_variable ( v_OTX_Cursor,
1518 'Order_ID',
1519 v_Order_ID );
1520
1521 xProgress := 'GASNOB-10-2060';
1522 v_Dummy := dbms_sql.execute ( v_OTX_Cursor );
1523
1524 /* */
1525 /* Begin the Order level loop. */
1526 /* */
1527
1528 xProgress := 'GASNOB-10-2070';
1529 WHILE dbms_sql.fetch_rows ( v_OTX_Cursor ) > 0
1530 LOOP
1531
1532 /* */
1533 /* Store the returned values in the PL/SQL table. */
1534 /* */
1535
1536 xProgress := 'GASNOB-10-2080';
1537 FOR v_LoopCount IN 1..v_OTX_Count
1538 LOOP
1539 xProgress := 'GASNOB-10-2090';
1540 dbms_sql.column_value ( v_OTX_Cursor,
1541 v_LoopCount,
1542 v_OTX_Table(v_LoopCount).value );
1543 END LOOP;
1544
1545 /* */
1546 /* Cross-reference all necessary columns in the PL/SQL table. */
1547 /* */
1548
1549 xProgress := 'GASNOB-10-2100';
1550 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1551 p_Return_Status => v_ReturnStatus,
1552 p_Msg_Count => v_MessageCount,
1553 p_Msg_Data => v_MessageData,
1554 p_Key_Tbl => v_CrossRefTable,
1555 p_Tbl => v_OTX_Table );
1556
1557 /* */
1558 /* Use the same key value for Order Text as for the Order table */
1559 /* */
1560
1561 v_OTX_Key := v_ORD_Key;
1562 ec_debug.pl ( 3, 'v_OTX_Key: ', v_OTX_Key );
1563
1564 xProgress := 'GASNOB-10-2120';
1565 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1566 p_TransactionType,
1567 p_CommunicationMethod,
1568 p_OTX_Interface,
1569 v_OTX_Table,
1570 v_OTX_Key );
1571
1572 /* */
1573 /* Call the (customizable) procedure to populate the corresponding */
1574 /* extension table. */
1575 /* */
1576
1577 xProgress := 'GASNOB-10-2130';
1578 GML_GASNO_X.populate_OTX_ext ( v_OTX_Key, v_OTX_Table );
1579
1580 END LOOP; /* while otx */
1581
1582
1583 xProgress := 'GASNOB-10-1740';
1584 dbms_sql.bind_variable ( v_DTL_Cursor,
1585 'BOL_ID',
1586 v_BOL_ID );
1587
1588 xProgress := 'GASNOB-10-1750';
1589 dbms_sql.bind_variable ( v_DTL_Cursor,
1590 'Order_ID',
1591 v_Order_ID );
1592
1593 /* */
1594 /* Execute the Detail level SELECT statement. */
1595 /* */
1596
1597 xProgress := 'GASNOB-10-2140';
1598 v_Dummy := dbms_sql.execute ( v_DTL_Cursor );
1599
1600 /* */
1601 /* Begin the Item level loop. */
1602 /* */
1603
1604 xProgress := 'GASNOB-10-2150';
1605 WHILE dbms_sql.fetch_rows ( v_DTL_Cursor ) > 0
1606 LOOP
1607
1608 /* */
1609 /* Store the returned values in the PL/SQL table. */
1610 /* */
1611
1612 xProgress := 'GASNOB-10-2160';
1613 FOR v_LoopCount IN 1..v_DTL_Count
1614 LOOP
1615 xProgress := 'GASNOB-10-2170';
1616 dbms_sql.column_value ( v_DTL_Cursor,
1617 v_LoopCount,
1618 v_DTL_Table(v_LoopCount).value );
1619 END LOOP;
1620
1621 xProgress := 'GASNOB-10-2175';
1622 ece_extract_utils_pub.find_pos ( v_DTL_Table,
1623 'Line_ID',
1624 V_Line_ID_Position );
1625
1626 v_Line_ID := TO_NUMBER(v_DTL_Table(v_Line_ID_Position).value);
1627 ec_debug.pl ( 3, 'v_Line_ID: ', v_Line_ID );
1628
1629 /* */
1630 /* Cross-reference all necessary columns in the PL/SQL table. */
1631 /* */
1632
1633 xProgress := 'GASNOB-10-2180';
1634 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1635 p_Return_Status => v_ReturnStatus,
1636 p_Msg_Count => v_MessageCount,
1637 p_Msg_Data => v_MessageData,
1638 p_Key_Tbl => v_CrossRefTable,
1639 p_Tbl => v_DTL_Table );
1640
1641 /* */
1642 /* Retrieve the next sequence number for the primary key value, and */
1643 /* insert this record into the Detail interface table. */
1644 /* */
1645
1646 xProgress := 'GASNOB-10-2190';
1647 BEGIN
1648 SELECT GML_GASNO_DETAILS_S.nextval
1649 INTO v_DTL_Key
1650 FROM sys.dual;
1651 EXCEPTION
1652 WHEN NO_DATA_FOUND THEN
1653 ec_debug.pl ( 0,
1654 'EC',
1655 'ECE_GET_NEXT_SEQ_FAILED',
1656 'PROGRESS_LEVEL',
1657 xProgress,
1658 'SEQ',
1659 'GML_GASNO_DETAILS_S' );
1660 END;
1661 ec_debug.pl ( 3, 'v_DTL_Key: ', v_DTL_Key );
1662
1663 xProgress := 'GASNOB-10-2200';
1664 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1665 p_TransactionType,
1666 p_CommunicationMethod,
1667 p_DTL_Interface,
1668 v_DTL_Table,
1669 v_DTL_Key );
1670
1671 /* */
1672 /* Call the (customizable) procedure to populate the corresponding */
1673 /* extension table. */
1674 /* */
1675
1676 xProgress := 'GASNOB-10-2210';
1677 GML_GASNO_X.populate_DTL_ext ( v_DTL_Key, v_DTL_Table );
1678
1679 /* */
1680 /* Execute the Detail Charge/Allowance level SELECT statement. */
1681 /* */
1682
1683 xProgress := 'GASNOB-10-1760';
1684 dbms_sql.bind_variable ( v_DAC_Cursor,
1685 'Line_ID',
1686 v_Line_ID );
1687
1688 xProgress := 'GASNOB-10-2220';
1689 v_Dummy := dbms_sql.execute ( v_DAC_Cursor );
1690
1691 /* */
1692 /* Begin the Detail DAC level loop. */
1693 /* */
1694
1695 xProgress := 'GASNOB-10-2230';
1696 WHILE dbms_sql.fetch_rows ( v_DAC_Cursor ) > 0
1697 LOOP
1698
1699 /* */
1700 /* Store the returned values in the PL/SQL table. */
1701 /* */
1702
1703 xProgress := 'GASNOB-10-2240';
1704 FOR v_LoopCount IN 1..v_DAC_Count
1705 LOOP
1706 xProgress := 'GASNOB-10-2250';
1707 dbms_sql.column_value ( v_DAC_Cursor,
1708 v_LoopCount,
1709 v_DAC_Table(v_LoopCount).value );
1710 END LOOP;
1711
1712 /* */
1713 /* Cross-reference all necessary columns in the PL/SQL table. */
1714 /* */
1715
1716 xProgress := 'GASNOB-10-2260';
1717 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1718 p_Return_Status => v_ReturnStatus,
1719 p_Msg_Count => v_MessageCount,
1720 p_Msg_Data => v_MessageData,
1721 p_Key_Tbl => v_CrossRefTable,
1722 p_Tbl => v_DAC_Table );
1723
1724
1725 /* */
1726 /* Use the same key value for Detail DAC as for the Detail table */
1727 /* */
1728
1729 v_DAC_Key := v_DTL_Key;
1730 ec_debug.pl ( 3, 'v_DAC_Key: ', v_DAC_Key );
1731
1732
1733 xProgress := 'GASNOB-10-2280';
1734 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1735 p_TransactionType,
1736 p_CommunicationMethod,
1737 p_DAC_Interface,
1738 v_DAC_Table,
1739 v_DAC_Key );
1740
1741 /* */
1742 /* Call the (customizable) procedure to populate the corresponding */
1743 /* extension table. */
1744 /* */
1745
1746 xProgress := 'GASNOB-10-2290';
1747 GML_GASNO_X.populate_DAC_ext ( v_DAC_Key, v_DAC_Table );
1748
1749 END LOOP; /* while Dac */
1750
1751 /* */
1752 /* Execute the Detail Text level SELECT statement. */
1753 /* */
1754
1755 xProgress := 'GASNOB-10-2300';
1756 dbms_sql.bind_variable ( v_DTX_Cursor,
1757 'Line_ID',
1758 v_Line_ID );
1759
1760 xProgress := 'GASNOB-10-2310';
1761 v_Dummy := dbms_sql.execute ( v_DTX_Cursor );
1762
1763 /* */
1764 /* Begin the Detail Text level loop. */
1765 /* */
1766
1767 xProgress := 'GASNOB-10-2320';
1768 WHILE dbms_sql.fetch_rows ( v_DTX_Cursor ) > 0
1769 LOOP
1770
1771 /* */
1772 /* Store the returned values in the PL/SQL table. */
1773 /* */
1774
1775 xProgress := 'GASNOB-10-2330';
1776 FOR v_LoopCount IN 1..v_DTX_Count
1777 LOOP
1778 xProgress := 'GASNOB-10-2340';
1779 dbms_sql.column_value ( v_DTX_Cursor,
1780 v_LoopCount,
1781 v_DTX_Table(v_LoopCount).value );
1782 END LOOP;
1783
1784 /* */
1785 /* Cross-reference all necessary columns in the PL/SQL table. */
1786 /* */
1787
1788 xProgress := 'GASNOB-10-2350';
1789 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1790 p_Return_Status => v_ReturnStatus,
1791 p_Msg_Count => v_MessageCount,
1792 p_Msg_Data => v_MessageData,
1793 p_Key_Tbl => v_CrossRefTable,
1794 p_Tbl => v_DTX_Table );
1795
1796 /* */
1797 /* Use the same key value for Detail Text as for the Detail table */
1798 /* */
1799
1800 v_DTX_Key := v_DTL_Key;
1801 ec_debug.pl ( 3, 'v_DTX_Key: ', v_DTX_Key );
1802
1803
1804 xProgress := 'GASNOB-10-2360';
1805 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1806 p_TransactionType,
1807 p_CommunicationMethod,
1808 p_DTX_Interface,
1809 v_DTX_Table,
1810 v_DTX_Key );
1811
1812 /* */
1813 /* Call the (customizable) procedure to populate the corresponding */
1814 /* extension table. */
1815 /* */
1816
1817 xProgress := 'GASNOB-10-2370';
1818 GML_GASNO_X.populate_DTX_ext ( v_DTX_Key, v_DTX_Table );
1819
1820 END LOOP; /* while dtx */
1821
1822 /* */
1823 /* Execute the Allocations level SELECT statement. */
1824 /* */
1825
1826 xProgress := 'GASNOB-10-2380';
1827 dbms_sql.bind_variable ( v_ALL_Cursor,
1828 'Line_ID',
1829 v_Line_ID );
1830
1831 xProgress := 'GASNOB-10-2390';
1832 v_Dummy := dbms_sql.execute ( v_ALL_Cursor );
1833
1834 /* */
1835 /* Begin the Allocations level loop. */
1836 /* */
1837
1838 xProgress := 'GASNOB-10-2400';
1839 WHILE dbms_sql.fetch_rows ( v_ALL_Cursor ) > 0
1840 LOOP
1841
1842 /* */
1843 /* Store the returned values in the PL/SQL table. */
1844 /* */
1845
1846 xProgress := 'GASNOB-10-2410';
1847 FOR v_LoopCount IN 1..v_ALL_Count
1848 LOOP
1849 xProgress := 'GASNOB-10-2420';
1850 dbms_sql.column_value ( v_ALL_Cursor,
1851 v_LoopCount,
1852 v_ALL_Table(v_LoopCount).value );
1853 END LOOP;
1854
1855 /* */
1856 /* Cross-reference all necessary columns in the PL/SQL table. */
1857 /* */
1858
1859 xProgress := 'GASNOB-10-2430';
1860 ec_code_conversion_pvt.populate_plsql_tbl_with_extval ( p_API_Version_Number => 1.0,
1861 p_Return_Status => v_ReturnStatus,
1862 p_Msg_Count => v_MessageCount,
1863 p_Msg_Data => v_MessageData,
1864 p_Key_Tbl => v_CrossRefTable,
1865 p_Tbl => v_ALL_Table );
1866
1867
1868 /* */
1869 /* Use the same key value for Allocations as for the Detail table */
1870 /* */
1871
1872 v_ALL_Key := v_DTL_Key;
1873 ec_debug.pl ( 3, 'v_ALL_Key: ', v_ALL_Key );
1874
1875
1876 xProgress := 'GASNOB-10-2440';
1877 ece_extract_utils_pub.insert_into_interface_tbl ( p_RunID,
1878 p_TransactionType,
1879 p_CommunicationMethod,
1880 p_ALL_Interface,
1881 v_ALL_Table,
1882 v_ALL_Key );
1883
1884 /* */
1885 /* Call the (customizable) procedure to populate the corresponding */
1886 /* extension table. */
1887 /* */
1888
1889 xProgress := 'GASNOB-10-2450';
1890 GML_GASNO_X.populate_ALL_ext ( v_ALL_Key, v_ALL_Table );
1891
1892 END LOOP; /* while all */
1893
1894 END LOOP; /* while dtl */
1895 END LOOP; /* while ord */
1896 END LOOP; /* while shp */
1897
1898 /* */
1899 /* Commit the interface table inserts. */
1900 /* */
1901
1902
1903 xProgress := 'GASNOB-10-2300';
1904 COMMIT;
1905
1906 /* */
1907 /* Close all open cursors. */
1908 /* */
1909
1910 xProgress := 'GASNOB-10-2310';
1911 dbms_sql.close_cursor ( v_SHP_Cursor );
1912 xProgress := 'GASNOB-10-2312';
1913 dbms_sql.close_cursor ( v_STX_Cursor );
1914 xProgress := 'GASNOB-10-2314';
1915 dbms_sql.close_cursor ( v_ORD_Cursor);
1916 xProgress := 'GASNOB-10-2316';
1917 dbms_sql.close_cursor ( v_OAC_Cursor );
1918 xProgress := 'GASNOB-10-2318';
1919 dbms_sql.close_cursor ( v_OTX_Cursor );
1920 xProgress := 'GASNOB-10-2320';
1921 dbms_sql.close_cursor ( v_DTL_Cursor );
1922 xProgress := 'GASNOB-10-2322';
1923 dbms_sql.close_cursor ( v_DAC_Cursor );
1924 xProgress := 'GASNOB-10-2323';
1925 dbms_sql.close_cursor ( v_DTX_Cursor );
1926 xProgress := 'GASNOB-10-2324';
1927 dbms_sql.close_cursor ( v_ALL_Cursor );
1928
1929 ec_debug.pop ( 'GML_GASNO.Populate_Interface_Tables' );
1930
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933
1934 ec_debug.pl ( 0,
1935 'EC',
1936 'ECE_PROGRAM_ERROR',
1937 'PROGRESS_LEVEL',
1938 xProgress );
1939
1940 ec_debug.pl ( 0,
1941 'EC',
1942 'ECE_ERROR_MESSAGE',
1943 'ERROR_MESSAGE',
1944 SQLERRM );
1945
1946 app_exception.raise_exception;
1947
1948 END Populate_Interface_Tables;
1949
1950
1951
1952 /*===========================================================================
1953
1954 PROCEDURE NAME: Put_Data_To_Output_Table
1955
1956 PURPOSE: This procedure extracts and sequences information from
1957 the Gateway interface tables and inserts the sequenced
1958 data into the Gateway output table.
1959
1960 ===========================================================================*/
1961
1962 PROCEDURE Put_Data_To_Output_Table ( p_CommunicationMethod IN VARCHAR2,
1963 p_TransactionType IN VARCHAR2,
1964 p_Orgn_Code IN VARCHAR2,
1965 p_BOL_No_From IN VARCHAR2,
1966 p_BOL_No_To IN VARCHAR2,
1967 p_Creation_Date_From IN VARCHAR2,
1968 p_Creation_Date_To IN VARCHAR2,
1969 p_Customer_Name IN VARCHAR2,
1970 p_RunID IN INTEGER,
1971 p_OutputWidth IN INTEGER,
1972 p_SHP_Interface IN VARCHAR2,
1973 p_STX_Interface IN VARCHAR2,
1974 p_ORD_Interface IN VARCHAR2,
1975 p_OAC_Interface IN VARCHAR2,
1976 p_OTX_Interface IN VARCHAR2,
1977 p_DTL_Interface IN VARCHAR2,
1978 p_DAC_Interface IN VARCHAR2,
1979 p_DTX_Interface IN VARCHAR2,
1980 p_ALL_Interface IN VARCHAR2 )
1981
1982 IS
1983
1984 /* */
1985 /* Variable definitions. 'Interface_tbl_type' is a PL/SQL table */
1986 /* typedef with the following structure: */
1987 /* */
1988 /* table_name VARCHAR2(50) */
1989 /* column_name VARCHAR2(50) */
1990 /* record_num NUMBER */
1991 /* position NUMBER */
1992 /* data_type VARCHAR2(50) */
1993 /* data_length NUMBER */
1994 /* value VARCHAR2(400) */
1995 /* layout_code VARCHAR2(2) */
1996 /* record_qualifier VARCHAR2(3) */
1997 /* */
1998
1999 xProgress VARCHAR2(30);
2000
2001 v_SHP_Table ece_flatfile_pvt.interface_tbl_type;
2002 v_STX_Table ece_flatfile_pvt.interface_tbl_type;
2003 v_ORD_Table ece_flatfile_pvt.interface_tbl_type;
2004 v_OAC_Table ece_flatfile_pvt.interface_tbl_type;
2005 v_OTX_Table ece_flatfile_pvt.interface_tbl_type;
2006 v_DTL_Table ece_flatfile_pvt.interface_tbl_type;
2007 v_DAC_Table ece_flatfile_pvt.interface_tbl_type;
2008 v_DTX_Table ece_flatfile_pvt.interface_tbl_type;
2009 v_ALL_Table ece_flatfile_pvt.interface_tbl_type;
2010
2011 v_SHP_CommonKeyName VARCHAR2(40);
2012 v_STX_CommonKeyName VARCHAR2(40);
2013 v_ORD_CommonKeyName VARCHAR2(40);
2014 v_OAC_CommonKeyName VARCHAR2(40);
2015 v_OTX_CommonKeyName VARCHAR2(40);
2016 v_DTL_CommonKeyName VARCHAR2(40);
2017 v_DAC_CommonKeyName VARCHAR2(40);
2018 v_DTX_CommonKeyName VARCHAR2(40);
2019 v_ALL_CommonKeyName VARCHAR2(40);
2020
2021 v_KeyPad VARCHAR2(22) := RPAD(' ', 22);
2022 v_FileCommonKey VARCHAR2(255);
2023 v_TranslatorCode VARCHAR2(30);
2024 v_RecordCommonKey0 VARCHAR2(25);
2025 v_RecordCommonKey1 VARCHAR2(22);
2026 v_RecordCommonKey2 VARCHAR2(22);
2027 v_RecordCommonKey3 VARCHAR2(22);
2028
2029 v_SHP_SelectCursor NUMBER;
2030 v_STX_SelectCursor NUMBER;
2031 v_ORD_SelectCursor NUMBER;
2032 v_OAC_SelectCursor NUMBER;
2033 v_OTX_SelectCursor NUMBER;
2034 v_DTL_SelectCursor NUMBER;
2035 v_DAC_SelectCursor NUMBER;
2036 v_DTX_SelectCursor NUMBER;
2037 v_ALL_SelectCursor NUMBER;
2038
2039 v_SHP_DeleteCursor NUMBER;
2040 v_STX_DeleteCursor NUMBER;
2041 v_ORD_DeleteCursor NUMBER;
2042 v_OAC_DeleteCursor NUMBER;
2043 v_OTX_DeleteCursor NUMBER;
2044 v_DTL_DeleteCursor NUMBER;
2045 v_DAC_DeleteCursor NUMBER;
2046 v_DTX_DeleteCursor NUMBER;
2047 v_ALL_DeleteCursor NUMBER;
2048
2049 v_SHP_XDeleteCursor NUMBER;
2050 v_STX_XDeleteCursor NUMBER;
2051 v_ORD_XDeleteCursor NUMBER;
2052 v_OAC_XDeleteCursor NUMBER;
2053 v_OTX_XDeleteCursor NUMBER;
2054 v_DTL_XDeleteCursor NUMBER;
2055 v_DAC_XDeleteCursor NUMBER;
2056 v_DTX_XDeleteCursor NUMBER;
2057 v_ALL_XDeleteCursor NUMBER;
2058
2059 v_SHP_Select VARCHAR2(32000);
2060 v_STX_Select VARCHAR2(32000);
2061 v_ORD_Select VARCHAR2(32000);
2062 v_OAC_Select VARCHAR2(32000);
2063 v_OTX_Select VARCHAR2(32000);
2064 v_DTL_Select VARCHAR2(32000);
2065 v_DAC_Select VARCHAR2(32000);
2066 v_DTX_Select VARCHAR2(32000);
2067 v_ALL_Select VARCHAR2(32000);
2068
2069 v_SHP_From VARCHAR2(32000);
2070 v_STX_From VARCHAR2(32000);
2071 v_ORD_From VARCHAR2(32000);
2072 v_OAC_From VARCHAR2(32000);
2073 v_OTX_From VARCHAR2(32000);
2074 v_DTL_From VARCHAR2(32000);
2075 v_DAC_From VARCHAR2(32000);
2076 v_DTX_From VARCHAR2(32000);
2077 v_ALL_From VARCHAR2(32000);
2078
2079 v_SHP_Where VARCHAR2(32000);
2080 v_STX_Where VARCHAR2(32000);
2081 v_ORD_Where VARCHAR2(32000);
2082 v_OAC_Where VARCHAR2(32000);
2083 v_OTX_Where VARCHAR2(32000);
2084 v_DTL_Where VARCHAR2(32000);
2085 v_DAC_Where VARCHAR2(32000);
2086 v_DTX_Where VARCHAR2(32000);
2087 v_ALL_Where VARCHAR2(32000);
2088
2089 v_SHP_Delete VARCHAR2(32000);
2090 v_STX_Delete VARCHAR2(32000);
2091 v_ORD_Delete VARCHAR2(32000);
2092 v_OAC_Delete VARCHAR2(32000);
2093 v_OTX_Delete VARCHAR2(32000);
2094 v_DTL_Delete VARCHAR2(32000);
2095 v_DAC_Delete VARCHAR2(32000);
2096 v_DTX_Delete VARCHAR2(32000);
2097 v_ALL_Delete VARCHAR2(32000);
2098
2099 v_SHP_XDelete VARCHAR2(32000);
2100 v_STX_XDelete VARCHAR2(32000);
2101 v_ORD_XDelete VARCHAR2(32000);
2102 v_OAC_XDelete VARCHAR2(32000);
2103 v_OTX_XDelete VARCHAR2(32000);
2104 v_DTL_XDelete VARCHAR2(32000);
2105 v_DAC_XDelete VARCHAR2(32000);
2106 v_DTX_XDelete VARCHAR2(32000);
2107 v_ALL_XDelete VARCHAR2(32000);
2108
2109 v_SHP_Count INTEGER;
2110 v_STX_Count INTEGER;
2111 v_ORD_Count INTEGER;
2112 v_OAC_Count INTEGER;
2113 v_OTX_Count INTEGER;
2114 v_DTL_Count INTEGER;
2115 v_DAC_Count INTEGER;
2116 v_DTX_Count INTEGER;
2117 v_ALL_Count INTEGER;
2118
2119 v_SHP_RowID ROWID;
2120 v_STX_RowID ROWID;
2121 v_ORD_RowID ROWID;
2122 v_OAC_RowID ROWID;
2123 v_OTX_RowID ROWID;
2124 v_DTL_RowID ROWID;
2125 v_DAC_RowID ROWID;
2126 v_DTX_RowID ROWID;
2127 v_ALL_RowID ROWID;
2128
2129 v_SHP_XRowID ROWID;
2130 v_STX_XRowID ROWID;
2131 v_ORD_XRowID ROWID;
2132 v_OAC_XRowID ROWID;
2133 v_OTX_XRowID ROWID;
2134 v_DTL_XRowID ROWID;
2135 v_DAC_XRowID ROWID;
2136 v_DTX_XRowID ROWID;
2137 v_ALL_XRowID ROWID;
2138
2139 v_SHP_XInterface VARCHAR2(50);
2140 v_STX_XInterface VARCHAR2(50);
2141 v_ORD_XInterface VARCHAR2(50);
2142 v_OAC_XInterface VARCHAR2(50);
2143 v_OTX_XInterface VARCHAR2(50);
2144 v_DTL_XInterface VARCHAR2(50);
2145 v_DAC_XInterface VARCHAR2(50);
2146 v_DTX_XInterface VARCHAR2(50);
2147 v_ALL_XInterface VARCHAR2(50);
2148
2149 v_Dummy INTEGER;
2150 v_TranslatorCodePosition INTEGER;
2151 v_SHP_CKNamePosition INTEGER;
2152 v_STX_CKNamePosition INTEGER;
2153 v_ORD_CKNamePosition INTEGER;
2154 v_OAC_CKNamePosition INTEGER;
2155 v_OTX_CKNamePosition INTEGER;
2156 v_DTL_CKNamePosition INTEGER;
2157 v_DAC_CKNamePosition INTEGER;
2158 v_DTX_CKNamePosition INTEGER;
2159 v_ALL_CKNamePosition INTEGER;
2160 V_BOL_ID_Position INTEGER;
2161 v_TransactionRecordIDPosition INTEGER;
2162 v_OrderHeaderIDPosition INTEGER;
2163 v_Line_ID_Position INTEGER;
2164 v_PickingLineIDPosition INTEGER;
2165 v_Order_ID INTEGER;
2166 v_Order_ID_Position INTEGER;
2167 v_SequenceNumberPosition INTEGER;
2168
2169 v_RunID INTEGER;
2170 v_BOL_ID INTEGER;
2171 v_TransactionRecordID INTEGER;
2172 v_OrderHeaderID INTEGER;
2173 v_Line_ID INTEGER;
2174 v_PickingLineID INTEGER;
2175 v_SequenceNumber INTEGER;
2176
2177 BEGIN
2178
2179 /*
2180
2181 Debug statements for the parameter values.
2182
2183 */
2184
2185 ec_debug.push ( 'GML_GASNO.Put_Data_To_Output_Table' );
2186 ec_debug.pl ( 3, 'p_CommunicationMethod: ', p_CommunicationMethod );
2187 ec_debug.pl ( 3, 'p_TransactionType: ', p_TransactionType );
2188 ec_debug.pl ( 3, 'p_Orgn_Code: ', p_Orgn_Code );
2189 ec_debug.pl ( 3, 'p_BOL_No_From: ', p_BOL_No_From );
2190 ec_debug.pl ( 3, 'p_BOL_No_To: ', p_BOL_No_To );
2191 ec_debug.pl ( 3, 'p_Creation_Date_From: ', p_Creation_Date_From );
2192 ec_debug.pl ( 3, 'p_Creation_Date_To: ', p_Creation_Date_To );
2193 ec_debug.pl ( 3, 'p_Customer_Name: ', p_Customer_Name );
2194 ec_debug.pl ( 3, 'p_RunID: ', p_RunID );
2195 ec_debug.pl ( 3, 'p_OutputWidth: ', p_OutputWidth );
2196 ec_debug.pl ( 3, 'p_SHP_Interface: ', p_SHP_Interface );
2197 ec_debug.pl ( 3, 'p_STX_Interface: ', p_STX_Interface );
2198 ec_debug.pl ( 3, 'p_ORD_Interface: ', p_ORD_Interface );
2199 ec_debug.pl ( 3, 'p_OAC_Interface: ', p_OAC_Interface );
2200 ec_debug.pl ( 3, 'p_OTX_Interface: ', p_OTX_Interface );
2201 ec_debug.pl ( 3, 'p_DTL_Interface: ', p_DTL_Interface );
2202 ec_debug.pl ( 3, 'p_DAC_Interface: ', p_DAC_Interface );
2203 ec_debug.pl ( 3, 'p_DTX_Interface: ', p_DTX_Interface );
2204 ec_debug.pl ( 3, 'p_ALL_Interface: ', p_ALL_Interface );
2205
2206 /* */
2207 /* The Model/Option solution is handled by only including shippable */
2208 /* items in the transaction (via the view) -- no further processing */
2209 /* is necessary. */
2210 /* */
2211
2212 /* */
2213 /* The 'select_clause' procedure will build the SELECT, FROM and WHERE */
2214 /* clauses in preparation for the dynamic SQL call using the EDI data */
2215 /* dictionary for the build. Any necessary customizations to these */
2216 /* the SQL call. */
2217 /* */
2218
2219 v_RunID := p_RunID;
2220 xProgress := 'GASNOB-40-1000';
2221 ece_flatfile_pvt.select_clause ( p_TransactionType,
2222 p_CommunicationMethod,
2223 p_SHP_Interface,
2224 v_SHP_XInterface,
2225 v_SHP_Table,
2226 v_SHP_CommonKeyName,
2227 v_SHP_Select,
2228 v_SHP_From,
2229 v_SHP_Where );
2230
2231 xProgress := 'GASNOB-40-1010';
2232 ece_flatfile_pvt.select_clause ( p_TransactionType,
2233 p_CommunicationMethod,
2234 p_STX_Interface,
2235 v_STX_XInterface,
2236 v_STX_Table,
2237 v_STX_CommonKeyName,
2238 v_STX_Select,
2239 v_STX_From,
2240 v_STX_Where );
2241
2242 xProgress := 'GASNOB-40-1020';
2243 ece_flatfile_pvt.select_clause ( p_TransactionType,
2244 p_CommunicationMethod,
2245 p_ORD_Interface,
2246 v_ORD_XInterface,
2247 v_ORD_Table,
2248 v_ORD_CommonKeyName,
2249 v_ORD_Select,
2250 v_ORD_From,
2251 v_ORD_Where );
2252
2253 xProgress := 'GASNOB-40-1030';
2254 ece_flatfile_pvt.select_clause ( p_TransactionType,
2255 p_CommunicationMethod,
2256 p_OAC_Interface,
2257 v_OAC_XInterface,
2258 v_OAC_Table,
2259 v_OAC_CommonKeyName,
2260 v_OAC_Select,
2261 v_OAC_From,
2262 v_OAC_Where );
2263
2264 xProgress := 'GASNOB-40-1040';
2265 ece_flatfile_pvt.select_clause ( p_TransactionType,
2266 p_CommunicationMethod,
2267 p_OTX_Interface,
2268 v_OTX_XInterface,
2269 v_OTX_Table,
2270 v_OTX_CommonKeyName,
2271 v_OTX_Select,
2272 v_OTX_From,
2273 v_OTX_Where );
2274
2275
2276 xProgress := 'GASNOB-40-1050';
2277 ece_flatfile_pvt.select_clause ( p_TransactionType,
2278 p_CommunicationMethod,
2279 p_DTL_Interface,
2280 v_DTL_XInterface,
2281 v_DTL_Table,
2282 v_DTL_CommonKeyName,
2283 v_DTL_Select,
2284 v_DTL_From,
2285 v_DTL_Where );
2286
2287 xProgress := 'GASNOB-40-1060';
2288 ece_flatfile_pvt.select_clause ( p_TransactionType,
2289 p_CommunicationMethod,
2290 p_DAC_Interface,
2291 v_DAC_XInterface,
2292 v_DAC_Table,
2293 v_DAC_CommonKeyName,
2294 v_DAC_Select,
2295 v_DAC_From,
2296 v_DAC_Where );
2297
2298 xProgress := 'GASNOB-40-1070';
2299 ece_flatfile_pvt.select_clause ( p_TransactionType,
2300 p_CommunicationMethod,
2301 p_DTX_Interface,
2302 v_DTX_XInterface,
2303 v_DTX_Table,
2304 v_DTX_CommonKeyName,
2305 v_DTX_Select,
2306 v_DTX_From,
2307 v_DTX_Where );
2308
2309 xProgress := 'GASNOB-40-1070';
2310 ece_flatfile_pvt.select_clause ( p_TransactionType,
2311 p_CommunicationMethod,
2312 p_ALL_Interface,
2313 v_ALL_XInterface,
2314 v_ALL_Table,
2315 v_ALL_CommonKeyName,
2316 v_ALL_Select,
2317 v_ALL_From,
2318 v_ALL_Where );
2319
2320 /* */
2321 /* Customize the SELECT clauses to include the ROWID. Records */
2322 /* will be deleted from the interface tables using these values. */
2323 /* Also add any columns that do not appear in the flatfile, but */
2324 /* will be needed for internal processing (i.e. ID values). */
2325 /* */
2326
2327 xProgress := 'GASNOB-40-1080';
2328 v_SHP_Select := v_SHP_Select ||
2329 ', ' ||
2330 p_SHP_Interface ||
2331 '.ROWID, ' ||
2332 v_SHP_XInterface ||
2333 '.ROWID, ' ||
2334 p_SHP_Interface ||
2335 '.TRANSACTION_RECORD_ID';
2336
2337 v_STX_Select := v_STX_Select ||
2338 ', ' ||
2339 p_STX_Interface ||
2340 '.ROWID, ' ||
2341 v_STX_XInterface ||
2342 '.ROWID, ' ||
2343 p_STX_Interface ||
2344 '.TRANSACTION_RECORD_ID';
2345
2346 v_ORD_Select := v_ORD_Select ||
2347 ', ' ||
2348 p_ORD_Interface ||
2349 '.ROWID, ' ||
2350 v_ORD_XInterface ||
2351 '.ROWID, ' ||
2352 p_ORD_Interface ||
2353 '.TRANSACTION_RECORD_ID';
2354
2355 v_OAC_Select := v_OAC_Select ||
2356 ', ' ||
2357 p_OAC_Interface ||
2358 '.ROWID, ' ||
2359 v_OAC_XInterface ||
2360 '.ROWID, ' ||
2361 p_OAC_Interface ||
2362 '.TRANSACTION_RECORD_ID';
2363
2364 v_OTX_Select := v_OTX_Select ||
2365 ', ' ||
2366 p_OTX_Interface ||
2367 '.ROWID, ' ||
2368 v_OTX_XInterface ||
2369 '.ROWID, ' ||
2370 p_OTX_Interface ||
2371 '.TRANSACTION_RECORD_ID';
2372
2373 v_DTL_Select := v_DTL_Select ||
2374 ', ' ||
2375 p_DTL_Interface ||
2376 '.ROWID, ' ||
2377 v_DTL_XInterface ||
2378 '.ROWID, ' ||
2379 p_DTL_Interface ||
2380 '.TRANSACTION_RECORD_ID';
2381
2382 v_DAC_Select := v_DAC_Select ||
2383 ', ' ||
2384 p_DAC_Interface ||
2385 '.ROWID, ' ||
2386 v_DAC_XInterface ||
2387 '.ROWID, ' ||
2388 p_DAC_Interface ||
2389 '.TRANSACTION_RECORD_ID';
2390
2391 v_DTX_Select := v_DTX_Select ||
2392 ', ' ||
2393 p_DTX_Interface ||
2394 '.ROWID, ' ||
2395 v_DTX_XInterface ||
2396 '.ROWID, ' ||
2397 p_DTX_Interface ||
2398 '.TRANSACTION_RECORD_ID';
2399
2400
2401 v_ALL_Select := v_ALL_Select ||
2402 ', ' ||
2403 p_ALL_Interface ||
2404 '.ROWID, ' ||
2405 v_ALL_XInterface ||
2406 '.ROWID, ' ||
2407 p_ALL_Interface ||
2408 '.TRANSACTION_RECORD_ID';
2409
2410 /* */
2411 /* Customize the WHERE clauses to: */
2412 /* */
2413
2414 xProgress := 'GASNOB-40-1090';
2415
2416 xProgress := 'GASNOB-40-1091';
2417 v_SHP_Where := v_SHP_Where || ' AND ' ||
2418 p_SHP_Interface || '.RUN_ID = :Run_ID' ||
2419 ' ORDER BY ' ||
2420 p_SHP_Interface || '.BOL_NO';
2421
2422 xProgress := 'GASNOB-40-1091';
2423 v_STX_Where := v_STX_Where || ' AND ' ||
2424 p_STX_Interface || '.RUN_ID = :Run_ID' ||
2425 ' AND ' ||
2426 p_STX_Interface || '.BOL_ID = :BOL_ID' ||
2427 ' ORDER BY ' ||
2428 p_STX_Interface || '.PARA_CODE_INT, ' ||
2429 p_STX_Interface || '.SUB_PARACODE, ' ||
2430 p_STX_Interface || '.LINE_NO';
2431
2432 xProgress := 'GASNOB-40-1092';
2433 v_ORD_Where := v_ORD_Where || ' AND ' ||
2434 p_ORD_Interface || '.RUN_ID = :Run_ID' ||
2435 ' AND ' ||
2436 p_ORD_Interface || '.ORDER_ID IN ' ||
2437 '( SELECT DISTINCT Order_ID ' ||
2438 ' FROM ' || p_DTL_Interface ||
2439 ' WHERE ' || p_DTL_Interface || '.BOL_ID = :BOL_ID) '||
2440 ' ORDER BY ' ||
2441 p_ORD_Interface || '.ORDER_NO';
2442
2443 xProgress := 'GASNOB-40-1093';
2444 v_OAC_Where := v_OAC_Where || ' AND ' ||
2445 p_OAC_Interface || '.RUN_ID = :Run_ID' ||
2446 ' AND ' ||
2447 p_OAC_Interface || '.ORDER_ID = :ORDER_ID' ||
2448 ' ORDER BY ' ||
2449 p_OAC_Interface || '.SAC_CODE_INT';
2450
2451 xProgress := 'GASNOB-40-1094';
2452 v_OTX_Where := v_OTX_Where || ' AND ' ||
2453 p_OTX_Interface || '.RUN_ID = :Run_ID' ||
2454 ' AND ' ||
2455 p_OTX_Interface || '.ORDER_ID = :ORDER_ID' ||
2456 ' ORDER BY ' ||
2457 p_OTX_Interface || '.PARA_CODE_INT, ' ||
2458 p_OTX_Interface || '.SUB_PARACODE, ' ||
2459 p_OTX_Interface || '.LINE_NO';
2460
2461 xProgress := 'GASNOB-40-1095';
2462 v_DTL_Where := v_DTL_Where || ' AND ' ||
2463 p_DTL_Interface || '.RUN_ID = :Run_ID' ||
2464 ' AND ' ||
2465 p_DTL_Interface || '.BOL_ID = :BOL_ID' ||
2466 ' AND ' ||
2467 p_DTL_Interface || '.ORDER_ID = :ORDER_ID' ||
2468 ' ORDER BY ' ||
2469 p_DTL_Interface || '.SO_LINE_NO';
2470
2471 xProgress := 'GASNOB-40-1096';
2472 v_DAC_Where := v_DAC_Where || ' AND ' ||
2473 p_DAC_Interface || '.RUN_ID = :Run_ID' ||
2474 ' AND ' ||
2475 p_DAC_Interface || '.LINE_ID = :LINE_ID' ||
2476 ' ORDER BY ' ||
2477 p_DAC_Interface || '.SAC_CODE_INT';
2478
2479 xProgress := 'GASNOB-40-1097';
2480 v_DTX_Where := v_DTX_Where || ' AND ' ||
2481 p_DTX_Interface || '.RUN_ID = :Run_ID' ||
2482 ' AND ' ||
2483 p_DTX_Interface || '.LINE_ID = :LINE_ID' ||
2484 ' ORDER BY ' ||
2485 p_DTX_Interface || '.PARA_CODE_INT, ' ||
2486 p_DTX_Interface || '.SUB_PARACODE, ' ||
2487 p_DTX_Interface || '.LINE_NO';
2488
2489 xProgress := 'GASNOB-40-1098';
2490 v_ALL_Where := v_ALL_Where || ' AND ' ||
2491 p_ALL_Interface || '.RUN_ID = :Run_ID' ||
2492 ' AND ' ||
2493 p_ALL_Interface || '.LINE_ID = :LINE_ID';
2494
2495 /* */
2496 /* Build the complete SELECT statement for each level. */
2497 /* */
2498
2499 xProgress := 'GASNOB-40-1100';
2500 v_SHP_Select := v_SHP_Select ||
2501 v_SHP_From ||
2502 v_SHP_Where ||
2503 ' FOR UPDATE';
2504 ec_debug.pl ( 3, 'v_SHP_Select: ', v_SHP_Select );
2505
2506 v_STX_Select := v_STX_Select ||
2507 v_STX_From ||
2508 v_STX_Where ||
2509 ' FOR UPDATE';
2510 ec_debug.pl ( 3, 'v_STX_Select: ', v_STX_Select );
2511
2512
2513 v_ORD_Select := v_ORD_Select ||
2514 v_ORD_From ||
2515 v_ORD_Where ||
2516 ' FOR UPDATE';
2517 ec_debug.pl ( 3, 'v_ORD_Select: ', v_ORD_Select );
2518
2519 v_OAC_Select := v_OAC_Select ||
2520 v_OAC_From ||
2521 v_OAC_Where ||
2522 ' FOR UPDATE';
2523 ec_debug.pl ( 3, 'v_OAC_Select: ', v_OAC_Select );
2524
2525 v_OTX_Select := v_OTX_Select ||
2526 v_OTX_From ||
2527 v_OTX_Where ||
2528 ' FOR UPDATE';
2529 ec_debug.pl ( 3, 'v_OTX_Select: ', v_OTX_Select );
2530
2531 v_DTL_Select := v_DTL_Select ||
2532 v_DTL_From ||
2533 v_DTL_Where ||
2534 ' FOR UPDATE';
2535 ec_debug.pl ( 3, 'v_DTL_Select: ', v_DTL_Select );
2536
2537 v_DAC_Select := v_DAC_Select ||
2538 v_DAC_From ||
2539 v_DAC_Where ||
2540 ' FOR UPDATE';
2541 ec_debug.pl ( 3, 'v_DAC_Select: ', v_DAC_Select );
2542
2543 v_DTX_Select := v_DTX_Select ||
2544 v_DTX_From ||
2545 v_DTX_Where ||
2546 ' FOR UPDATE';
2547 ec_debug.pl ( 3, 'v_DTX_Select: ', v_DTX_Select );
2548
2549 v_ALL_Select := v_ALL_Select ||
2550 v_ALL_From ||
2551 v_ALL_Where ||
2552 ' FOR UPDATE';
2553 ec_debug.pl ( 3, 'v_ALL_Select: ', v_ALL_Select );
2554
2555 /* */
2556 /* Build the DELETE clauses for each interface and extension table. */
2557 /* */
2558
2559 xProgress := 'GASNOB-40-1110';
2560 v_SHP_Delete := 'DELETE FROM ' ||
2561 p_SHP_Interface ||
2562 ' WHERE ROWID = :Row_ID';
2563 ec_debug.pl ( 3, 'v_SHP_Delete: ', v_SHP_Delete );
2564
2565 v_SHP_XDelete := 'DELETE FROM ' ||
2566 v_SHP_XInterface ||
2567 ' WHERE ROWID = :Row_ID';
2568 ec_debug.pl ( 3, 'v_SHP_XDelete: ', v_SHP_XDelete );
2569
2570 v_STX_Delete := 'DELETE FROM ' ||
2571 p_STX_Interface ||
2572 ' WHERE ROWID = :Row_ID';
2573 ec_debug.pl ( 3, 'v_STX_Delete: ', v_STX_Delete );
2574
2575 v_STX_XDelete := 'DELETE FROM ' ||
2576 v_STX_XInterface ||
2577 ' WHERE ROWID = :Row_ID';
2578 ec_debug.pl ( 3, 'v_STX_XDelete: ', v_STX_XDelete );
2579
2580 v_ORD_Delete := 'DELETE FROM ' ||
2581 p_ORD_Interface ||
2582 ' WHERE ROWID = :Row_ID';
2583 ec_debug.pl ( 3, 'v_ORD_Delete: ', v_ORD_Delete );
2584
2585 v_ORD_XDelete := 'DELETE FROM ' ||
2586 v_ORD_XInterface ||
2587 ' WHERE ROWID = :Row_ID';
2588 ec_debug.pl ( 3, 'v_ORD_XDelete: ', v_ORD_XDelete );
2589
2590 v_OAC_Delete := 'DELETE FROM ' ||
2591 p_OAC_Interface ||
2592 ' WHERE ROWID = :Row_ID';
2593 ec_debug.pl ( 3, 'v_OAC_Delete: ', v_OAC_Delete );
2594
2595 v_OAC_XDelete := 'DELETE FROM ' ||
2596 v_OAC_XInterface ||
2597 ' WHERE ROWID = :Row_ID';
2598 ec_debug.pl ( 3, 'v_OAC_XDelete: ', v_OAC_XDelete );
2599
2600 v_OTX_Delete := 'DELETE FROM ' ||
2601 p_OTX_Interface ||
2602 ' WHERE ROWID = :Row_ID';
2603 ec_debug.pl ( 3, 'v_OTX_Delete: ', v_OTX_Delete );
2604
2605 v_OTX_XDelete := 'DELETE FROM ' ||
2606 v_OTX_XInterface ||
2607 ' WHERE ROWID = :Row_ID';
2608 ec_debug.pl ( 3, 'v_OTX_XDelete: ', v_OTX_XDelete );
2609
2610 v_DTL_Delete := 'DELETE FROM ' ||
2611 p_DTL_Interface ||
2612 ' WHERE ROWID = :Row_ID';
2613 ec_debug.pl ( 3, 'v_DTL_Delete: ', v_DTL_Delete );
2614
2615 v_DTL_XDelete := 'DELETE FROM ' ||
2616 v_DTL_XInterface ||
2617 ' WHERE ROWID = :Row_ID';
2618 ec_debug.pl ( 3, 'v_DTL_XDelete: ', v_DTL_XDelete );
2619
2620 v_DAC_Delete := 'DELETE FROM ' ||
2621 p_DAC_Interface ||
2622 ' WHERE ROWID = :Row_ID';
2623 ec_debug.pl ( 3, 'v_DAC_Delete: ', v_DAC_Delete );
2624
2625 v_DAC_XDelete := 'DELETE FROM ' ||
2626 v_DAC_XInterface ||
2627 ' WHERE ROWID = :Row_ID';
2628 ec_debug.pl ( 3, 'v_DAC_XDelete: ', v_DAC_XDelete );
2629
2630 v_DTX_Delete := 'DELETE FROM ' ||
2631 p_DTX_Interface ||
2632 ' WHERE ROWID = :Row_ID';
2633 ec_debug.pl ( 3, 'v_DTX_Delete: ', v_DTX_Delete );
2634
2635 v_DTX_XDelete := 'DELETE FROM ' ||
2636 v_DTX_XInterface ||
2637 ' WHERE ROWID = :Row_ID';
2638 ec_debug.pl ( 3, 'v_DTX_XDelete: ', v_DTX_XDelete );
2639
2640 v_ALL_Delete := 'DELETE FROM ' ||
2641 p_ALL_Interface ||
2642 ' WHERE ROWID = :Row_ID';
2643 ec_debug.pl ( 3, 'v_ALL_Delete: ', v_ALL_Delete );
2644
2645 v_ALL_XDelete := 'DELETE FROM ' ||
2646 v_ALL_XInterface ||
2647 ' WHERE ROWID = :Row_ID';
2648 ec_debug.pl ( 3, 'v_ALL_XDelete: ', v_ALL_XDelete );
2649
2650 /* */
2651 /* Open a cursor for each SELECT and DELETE call. This tells */
2652 /* the database to reserve space for the data returned by the */
2653 /* SELECT and DELETE statements. */
2654 /* */
2655
2656 xProgress := 'GASNOB-40-1120';
2657 v_SHP_SelectCursor := dbms_sql.open_cursor;
2658 xProgress := 'GASNOB-40-1122';
2659 v_STX_SelectCursor := dbms_sql.open_cursor;
2660 xProgress := 'GASNOB-40-1124';
2661 v_ORD_SelectCursor := dbms_sql.open_cursor;
2662 xProgress := 'GASNOB-40-1126';
2663 v_OAC_SelectCursor := dbms_sql.open_cursor;
2664 xProgress := 'GASNOB-40-1128';
2665 v_OTX_SelectCursor := dbms_sql.open_cursor;
2666 xProgress := 'GASNOB-40-1130';
2667 v_DTL_SelectCursor := dbms_sql.open_cursor;
2668 xProgress := 'GASNOB-40-1132';
2669 v_DAC_SelectCursor := dbms_sql.open_cursor;
2670 xProgress := 'GASNOB-40-1134';
2671 v_DTX_SelectCursor := dbms_sql.open_cursor;
2672 xProgress := 'GASNOB-40-1135';
2673 v_ALL_SelectCursor := dbms_sql.open_cursor;
2674
2675 xProgress := 'GASNOB-40-1136';
2676 v_SHP_DeleteCursor := dbms_sql.open_cursor;
2677 xProgress := 'GASNOB-40-1138';
2678 v_STX_DeleteCursor := dbms_sql.open_cursor;
2679 xProgress := 'GASNOB-40-1140';
2680 v_ORD_DeleteCursor := dbms_sql.open_cursor;
2681 xProgress := 'GASNOB-40-1142';
2682 v_OAC_DeleteCursor := dbms_sql.open_cursor;
2683 xProgress := 'GASNOB-40-1144';
2684 v_OTX_DeleteCursor := dbms_sql.open_cursor;
2685 xProgress := 'GASNOB-40-1146';
2686 v_DTL_DeleteCursor := dbms_sql.open_cursor;
2687 xProgress := 'GASNOB-40-1148';
2688 v_DAC_DeleteCursor := dbms_sql.open_cursor;
2689 xProgress := 'GASNOB-40-1150';
2690 v_DTX_DeleteCursor := dbms_sql.open_cursor;
2691 xProgress := 'GASNOB-40-1151';
2692 v_ALL_DeleteCursor := dbms_sql.open_cursor;
2693
2694 xProgress := 'GASNOB-40-1152';
2695 v_SHP_XDeleteCursor := dbms_sql.open_cursor;
2696 xProgress := 'GASNOB-40-1154';
2697 v_STX_XDeleteCursor := dbms_sql.open_cursor;
2698 xProgress := 'GASNOB-40-1156';
2699 v_ORD_XDeleteCursor := dbms_sql.open_cursor;
2700 xProgress := 'GASNOB-40-1158';
2701 v_OAC_XDeleteCursor := dbms_sql.open_cursor;
2702 xProgress := 'GASNOB-40-1160';
2703 v_OTX_XDeleteCursor := dbms_sql.open_cursor;
2704 xProgress := 'GASNOB-40-1162';
2705 v_DTL_XDeleteCursor := dbms_sql.open_cursor;
2706 xProgress := 'GASNOB-40-1164';
2707 v_DAC_XDeleteCursor := dbms_sql.open_cursor;
2708 xProgress := 'GASNOB-40-1166';
2709 v_DTX_XDeleteCursor := dbms_sql.open_cursor;
2710 xProgress := 'GASNOB-40-1166';
2711 v_ALL_XDeleteCursor := dbms_sql.open_cursor;
2712
2713 /* */
2714 /* Parse each SELECT and DELETE statement so the database understands */
2715 /* the command. */
2716 /* */
2717
2718 xProgress := 'GASNOB-40-1170';
2719 BEGIN
2720 dbms_sql.parse ( v_SHP_SelectCursor,
2721 v_SHP_Select,
2722 dbms_sql.native );
2723 EXCEPTION
2724 WHEN OTHERS THEN
2725 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2726 v_SHP_Select) ;
2727 app_exception.raise_exception;
2728 END;
2729
2730 xProgress := 'GASNOB-40-1180';
2731 BEGIN
2732 dbms_sql.parse ( v_STX_SelectCursor,
2733 v_STX_Select,
2734 dbms_sql.native );
2735 EXCEPTION
2736 WHEN OTHERS THEN
2737 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2738 v_STX_Select) ;
2739 app_exception.raise_exception;
2740 END;
2741
2742 xProgress := 'GASNOB-40-1190';
2743 BEGIN
2744 dbms_sql.parse ( v_ORD_SelectCursor,
2745 v_ORD_Select,
2746 dbms_sql.native );
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2750 v_ORD_Select) ;
2751 app_exception.raise_exception;
2752 END;
2753
2754 xProgress := 'GASNOB-40-1200';
2755 BEGIN
2756 dbms_sql.parse ( v_OAC_SelectCursor,
2757 v_OAC_Select,
2758 dbms_sql.native );
2759 EXCEPTION
2760 WHEN OTHERS THEN
2761 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2762 v_OAC_Select) ;
2763 app_exception.raise_exception;
2764 END;
2765
2766 xProgress := 'GASNOB-40-1210';
2767 BEGIN
2768 dbms_sql.parse ( v_OTX_SelectCursor,
2769 v_OTX_Select,
2770 dbms_sql.native );
2771 EXCEPTION
2772 WHEN OTHERS THEN
2773 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2774 v_OTX_Select) ;
2775 app_exception.raise_exception;
2776 END;
2777
2778 xProgress := 'GASNOB-40-1220';
2779 BEGIN
2780 dbms_sql.parse ( v_DTL_SelectCursor,
2781 v_DTL_Select,
2782 dbms_sql.native );
2783 EXCEPTION
2784 WHEN OTHERS THEN
2785 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2786 v_DTL_Select) ;
2787 app_exception.raise_exception;
2788 END;
2789
2790 xProgress := 'GASNOB-40-1230';
2791 BEGIN
2792 dbms_sql.parse ( v_DAC_SelectCursor,
2793 v_DAC_Select,
2794 dbms_sql.native );
2795 EXCEPTION
2796 WHEN OTHERS THEN
2797 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2798 v_DAC_Select) ;
2799 app_exception.raise_exception;
2800 END;
2801
2802 xProgress := 'GASNOB-40-1240';
2803 BEGIN
2804 dbms_sql.parse ( v_DTX_SelectCursor,
2805 v_DTX_Select,
2806 dbms_sql.native );
2807 EXCEPTION
2808 WHEN OTHERS THEN
2809 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2810 v_DTX_Select) ;
2811 app_exception.raise_exception;
2812 END;
2813
2814 xProgress := 'GASNOB-40-1245';
2815 BEGIN
2816 dbms_sql.parse ( v_ALL_SelectCursor,
2817 v_ALL_Select,
2818 dbms_sql.native );
2819 EXCEPTION
2820 WHEN OTHERS THEN
2821 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2822 v_ALL_Select) ;
2823 app_exception.raise_exception;
2824 END;
2825
2826 xProgress := 'GASNOB-40-1250';
2827 BEGIN
2828 dbms_sql.parse ( v_SHP_DeleteCursor,
2829 v_SHP_Delete,
2830 dbms_sql.native );
2831 EXCEPTION
2832 WHEN OTHERS THEN
2833 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2834 v_SHP_Delete) ;
2835 app_exception.raise_exception;
2836 END;
2837
2838 xProgress := 'GASNOB-40-1260';
2839 BEGIN
2840 dbms_sql.parse ( v_STX_DeleteCursor,
2841 v_STX_Delete,
2842 dbms_sql.native );
2843 EXCEPTION
2844 WHEN OTHERS THEN
2845 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2846 v_STX_Delete) ;
2847 app_exception.raise_exception;
2848 END;
2849
2850 xProgress := 'GASNOB-40-1270';
2851 BEGIN
2852 dbms_sql.parse ( v_ORD_DeleteCursor,
2853 v_ORD_Delete,
2854 dbms_sql.native );
2855 EXCEPTION
2856 WHEN OTHERS THEN
2857 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2858 v_ORD_Delete) ;
2859 app_exception.raise_exception;
2860 END;
2861
2862 xProgress := 'GASNOB-40-1280';
2863 BEGIN
2864 dbms_sql.parse ( v_OAC_DeleteCursor,
2865 v_OAC_Delete,
2866 dbms_sql.native );
2867 EXCEPTION
2868 WHEN OTHERS THEN
2869 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2870 v_OAC_Delete) ;
2871 app_exception.raise_exception;
2872 END;
2873
2874 xProgress := 'GASNOB-40-1290';
2875 BEGIN
2876 dbms_sql.parse ( v_OTX_DeleteCursor,
2877 v_OTX_Delete,
2878 dbms_sql.native );
2879 EXCEPTION
2880 WHEN OTHERS THEN
2881 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2882 v_OTX_Delete) ;
2883 app_exception.raise_exception;
2884 END;
2885
2886 xProgress := 'GASNOB-40-1300';
2887 BEGIN
2888 dbms_sql.parse ( v_DTL_DeleteCursor,
2889 v_DTL_Delete,
2890 dbms_sql.native );
2891 EXCEPTION
2892 WHEN OTHERS THEN
2893 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2894 v_DTL_Delete) ;
2895 app_exception.raise_exception;
2896 END;
2897
2898 xProgress := 'GASNOB-40-1310';
2899 BEGIN
2900 dbms_sql.parse ( v_DAC_DeleteCursor,
2901 v_DAC_Delete,
2902 dbms_sql.native );
2903 EXCEPTION
2904 WHEN OTHERS THEN
2905 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2906 v_DAC_Delete) ;
2907 app_exception.raise_exception;
2908 END;
2909
2910 xProgress := 'GASNOB-40-1320';
2911 BEGIN
2912 dbms_sql.parse ( v_DTX_DeleteCursor,
2913 v_DTX_Delete,
2914 dbms_sql.native );
2915 EXCEPTION
2916 WHEN OTHERS THEN
2917 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2918 v_DTX_Delete) ;
2919 app_exception.raise_exception;
2920 END;
2921
2922 xProgress := 'GASNOB-40-1325';
2923 BEGIN
2924 dbms_sql.parse ( v_ALL_DeleteCursor,
2925 v_ALL_Delete,
2926 dbms_sql.native );
2927 EXCEPTION
2928 WHEN OTHERS THEN
2929 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2930 v_ALL_Delete) ;
2931 app_exception.raise_exception;
2932 END;
2933
2934 xProgress := 'GASNOB-40-1330';
2935 BEGIN
2936 dbms_sql.parse ( v_SHP_XDeleteCursor,
2937 v_SHP_XDelete,
2938 dbms_sql.native );
2939 EXCEPTION
2940 WHEN OTHERS THEN
2941 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2942 v_SHP_XDelete) ;
2943 app_exception.raise_exception;
2944 END;
2945
2946 xProgress := 'GASNOB-40-1340';
2947 BEGIN
2948 dbms_sql.parse ( v_STX_XDeleteCursor,
2949 v_STX_XDelete,
2950 dbms_sql.native );
2951 EXCEPTION
2952 WHEN OTHERS THEN
2953 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2954 v_STX_XDelete) ;
2955 app_exception.raise_exception;
2956 END;
2957
2958 xProgress := 'GASNOB-40-1350';
2959 BEGIN
2960 dbms_sql.parse ( v_ORD_XDeleteCursor,
2961 v_ORD_XDelete,
2962 dbms_sql.native );
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2966 v_ORD_XDelete) ;
2967 app_exception.raise_exception;
2968 END;
2969
2970 xProgress := 'GASNOB-40-1360';
2971 BEGIN
2972 dbms_sql.parse ( v_OAC_XDeleteCursor,
2973 v_OAC_XDelete,
2974 dbms_sql.native );
2975 EXCEPTION
2976 WHEN OTHERS THEN
2977 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2978 v_OAC_XDelete) ;
2979 app_exception.raise_exception;
2980 END;
2981
2982 xProgress := 'GASNOB-40-1370';
2983 BEGIN
2984 dbms_sql.parse ( v_OTX_XDeleteCursor,
2985 v_OTX_XDelete,
2986 dbms_sql.native );
2987 EXCEPTION
2988 WHEN OTHERS THEN
2989 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
2990 v_OTX_XDelete) ;
2991 app_exception.raise_exception;
2992 END;
2993
2994 xProgress := 'GASNOB-40-1380';
2995 BEGIN
2996 dbms_sql.parse ( v_DTL_XDeleteCursor,
2997 v_DTL_XDelete,
2998 dbms_sql.native );
2999 EXCEPTION
3000 WHEN OTHERS THEN
3001 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3002 v_DTL_XDelete) ;
3003 app_exception.raise_exception;
3004 END;
3005
3006 xProgress := 'GASNOB-40-1390';
3007 BEGIN
3008 dbms_sql.parse ( v_DAC_XDeleteCursor,
3009 v_DAC_XDelete,
3010 dbms_sql.native );
3011 EXCEPTION
3012 WHEN OTHERS THEN
3013 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3014 v_DAC_XDelete) ;
3015 app_exception.raise_exception;
3016 END;
3017
3018 xProgress := 'GASNOB-40-1400';
3019 BEGIN
3020 dbms_sql.parse ( v_DTX_XDeleteCursor,
3021 v_DTX_XDelete,
3022 dbms_sql.native );
3023 EXCEPTION
3024 WHEN OTHERS THEN
3025 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3026 v_DTX_XDelete) ;
3027 app_exception.raise_exception;
3028 END;
3029
3030 xProgress := 'GASNOB-40-1405';
3031 BEGIN
3032 dbms_sql.parse ( v_ALL_XDeleteCursor,
3033 v_ALL_XDelete,
3034 dbms_sql.native );
3035 EXCEPTION
3036 WHEN OTHERS THEN
3037 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3038 v_SHP_Delete) ;
3039 app_exception.raise_exception;
3040 END;
3041
3042 /* */
3043 /* Initialize all counters. */
3044 /* */
3045
3046 xProgress := 'GASNOB-40-1410';
3047 v_SHP_Count := v_SHP_Table.COUNT;
3048 ec_debug.pl ( 3, 'v_SHP_Count: ', v_SHP_Count );
3049
3050 xProgress := 'GASNOB-40-1412';
3051 v_STX_Count :=v_STX_Table.COUNT;
3052 ec_debug.pl ( 3, 'v_STX_Count: ', v_STX_Count );
3053
3054 xProgress := 'GASNOB-40-1414';
3055 v_ORD_Count := v_ORD_Table.COUNT;
3056 ec_debug.pl ( 3, 'v_ORD_Count: ', v_ORD_Count );
3057
3058 xProgress := 'GASNOB-40-1416';
3059 v_OAC_Count := v_OAC_Table.COUNT;
3060 ec_debug.pl ( 3, 'v_OAC_Count: ', v_OAC_Count );
3061
3062 xProgress := 'GASNOB-40-1418';
3063 v_OTX_Count := v_OTX_Table.COUNT;
3064 ec_debug.pl ( 3, 'v_OTX_Count: ', v_OTX_Count );
3065
3066 xProgress := 'GASNOB-40-1420';
3067 v_DTL_Count := v_DTL_Table.COUNT;
3068 ec_debug.pl ( 3, 'v_DTL_Count: ', v_DTL_Count );
3069
3070 xProgress := 'GASNOB-40-1422';
3071 v_DAC_Count := v_DAC_Table.COUNT;
3072 ec_debug.pl ( 3, 'v_DAC_Count: ', v_DAC_Count );
3073
3074 xProgress := 'GASNOB-40-1424';
3075 v_DTX_Count := v_DTX_Table.COUNT;
3076 ec_debug.pl ( 3, 'v_DTX_Count: ', v_DTX_Count );
3077
3078 xProgress := 'GASNOB-40-1424';
3079 v_ALL_Count := v_ALL_Table.COUNT;
3080 ec_debug.pl ( 3, 'v_ALL_Count: ', v_ALL_Count );
3081
3082 /* */
3083 /* Define the data type for every column in each SELECT statement */
3084 /* so the database understands how to populate it. Using the */
3085 /* K.I.S.S. principle, every data type will be converted to */
3086 /* VARCHAR2. */
3087 /* */
3088
3089 xProgress := 'GASNOB-40-1430';
3090 FOR v_LoopCount IN 1..v_SHP_Count
3091 LOOP
3092 xProgress := 'GASNOB-40-1440';
3093 dbms_sql.define_column ( v_SHP_SelectCursor,
3094 v_LoopCount,
3095 v_SHP_Select,
3096 ece_extract_utils_pub.G_MaxColWidth );
3097 END LOOP;
3098
3099 /* */
3100 /* Define the ROWIDs for the DELETE statements. */
3101 /* */
3102
3103 xProgress := 'GASNOB-40-1450';
3104 dbms_sql.define_column_rowid ( v_SHP_SelectCursor,
3105 v_SHP_Count + 1,
3106 v_SHP_RowID );
3107
3108 xProgress := 'GASNOB-40-1460';
3109 dbms_sql.define_column_rowid ( v_SHP_SelectCursor,
3110 v_SHP_Count + 2,
3111 v_SHP_XRowID );
3112
3113 /* */
3114 /* Define the internal ID columns. */
3115 /* */
3116
3117 xProgress := 'GASNOB-40-1480';
3118 dbms_sql.define_column ( v_SHP_SelectCursor,
3119 v_SHP_Count + 3,
3120 v_TransactionRecordID );
3121
3122 xPRogress := 'GASNOB-40-1490';
3123 FOR v_LoopCount IN 1..v_STX_Count
3124 LOOP
3125 xProgress := 'GASNOB-40-1500';
3126 dbms_sql.define_column ( v_STX_SelectCursor,
3127 v_LoopCount,
3128 v_STX_Select,
3129 ece_extract_utils_pub.G_MaxColWidth );
3130 END LOOP;
3131
3132 /* */
3133 /* Define the ROWID for the DELETE statements. */
3134 /* */
3135
3136 xProgress := 'GASNOB-40-1510';
3137 dbms_sql.define_column_rowid ( v_STX_SelectCursor,
3138 v_STX_Count + 1,
3139 v_STX_RowID );
3140
3141 xProgress := 'GASNOB-40-1520';
3142 dbms_sql.define_column_rowid ( v_STX_SelectCursor,
3143 v_STX_Count + 2,
3144 v_STX_XRowID );
3145
3146 /* */
3147 /* Define the internal ID columns. */
3148 /* */
3149
3150 xProgress := 'GASNOB-40-1525';
3151 dbms_sql.define_column ( v_STX_SelectCursor,
3152 v_STX_Count + 3,
3153 v_TransactionRecordID );
3154
3155 xProgress := 'GASNOB-40-1530';
3156 FOR v_LoopCount IN 1..v_ORD_Count
3157 LOOP
3158 xProgress := 'GASNOB-40-1540';
3159 dbms_sql.define_column ( v_ORD_SelectCursor,
3160 v_LoopCount,
3161 v_ORD_Select,
3162 ece_extract_utils_pub.G_MaxColWidth );
3163 END LOOP;
3164
3165 /* */
3166 /* Define the ROWIDs for the DELETE statements. */
3167 /* */
3168
3169 xProgress := 'GASNOB-40-1550';
3170 dbms_sql.define_column_rowid ( v_ORD_SelectCursor,
3171 v_ORD_Count + 1,
3172 v_ORD_RowID );
3173
3174 xProgress := 'GASNOB-40-1560';
3175 dbms_sql.define_column_rowid ( v_ORD_SelectCursor,
3176 v_ORD_Count + 2,
3177 v_ORD_XRowID );
3178 /* */
3179 /* Define the internal ID columns. */
3180 /* */
3181
3182 xProgress := 'GASNOB-40-1565';
3183 dbms_sql.define_column ( v_ORD_SelectCursor,
3184 v_ORD_Count + 3,
3185 v_TransactionRecordID );
3186
3187 xPRogress := 'GASNOB-40-1570';
3188 FOR v_LoopCount IN 1..v_OAC_Count
3189 LOOP
3190 xProgress := 'GASNOB-40-1580';
3191 dbms_sql.define_column ( v_OAC_SelectCursor,
3192 v_LoopCount,
3193 v_OAC_Select,
3194 ece_extract_utils_pub.G_MaxColWidth );
3195 END LOOP;
3196
3197 /* */
3198 /* Define the ROWIDs for the DELETE statements. */
3199 /* */
3200
3201 xProgress := 'GASNOB-40-1590';
3202 dbms_sql.define_column_rowid ( v_OAC_SelectCursor,
3203 v_OAC_Count + 1,
3204 v_OAC_RowID );
3205
3206 xProgress := 'GASNOB-40-1600';
3207 dbms_sql.define_column_rowid ( v_OAC_SelectCursor,
3208 v_OAC_Count + 2,
3209 v_OAC_XRowID );
3210
3211 /* */
3212 /* Define the internal ID columns. */
3213 /* */
3214
3215 xProgress := 'GASNOB-40-1605';
3216 dbms_sql.define_column ( v_OAC_SelectCursor,
3217 v_OAC_Count + 3,
3218 v_TransactionRecordID );
3219
3220 xProgress := 'GASNOB-40-1610';
3221 FOR v_LoopCount IN 1..v_OTX_Count
3222 LOOP
3223 xPRogress := 'GASNOB-40-1620';
3224 dbms_sql.define_column ( v_OTX_SelectCursor,
3225 v_LoopCount,
3226 v_OTX_Select,
3227 ece_extract_utils_pub.G_MaxColWidth );
3228 END LOOP;
3229
3230 /* */
3231 /* Define the ROWIDs for the DELETE statements. */
3232 /* */
3233
3234 xProgress := 'GASNOB-40-1630';
3235 dbms_sql.define_column_rowid ( v_OTX_SelectCursor,
3236 v_OTX_Count + 1,
3237 v_OTX_RowID );
3238
3239 xProgress := 'GASNOB-40-1640';
3240 dbms_sql.define_column_rowid ( v_OTX_SelectCursor,
3241 v_OTX_Count + 2,
3242 v_OTX_XRowID );
3243
3244 /* */
3245 /* Define the internal ID columns. */
3246 /* */
3247
3248 xProgress := 'GASNOB-40-1650';
3249 dbms_sql.define_column ( v_OTX_SelectCursor,
3250 v_OTX_Count + 3,
3251 v_TransactionRecordID );
3252
3253 xProgress := 'GASNOB-40-1660';
3254 FOR v_LoopCount IN 1..v_DTL_Count
3255 LOOP
3256 xProgress := 'GASNOB-40-1670';
3257 dbms_sql.define_column ( v_DTL_SelectCursor,
3258 v_LoopCount,
3259 v_DTL_Select,
3260 ece_extract_utils_pub.G_MaxColWidth );
3261 END LOOP;
3262
3263 /* */
3264 /* Define the ROWIDs for the DELETE statements. */
3265 /* */
3266
3267 xProgress := 'GASNOB-40-1680';
3268 dbms_sql.define_column_rowid ( v_DTL_SelectCursor,
3269 v_DTL_Count + 1,
3270 v_DTL_RowID );
3271
3272 xProgress := 'GASNOB-40-1690';
3273 dbms_sql.define_column_rowid ( v_DTL_SelectCursor,
3274 v_DTL_Count + 2,
3275 v_DTL_XRowID );
3276
3277 /* */
3278 /* Define the internal ID columns. */
3279 /* */
3280
3281 xProgress := 'GASNOB-40-1700';
3282 dbms_sql.define_column ( v_DTL_SelectCursor,
3283 v_DTL_Count + 3,
3284 v_TransactionRecordID );
3285
3286 xProgress := 'GASNOB-40-1710';
3287 FOR v_LoopCount IN 1..v_DAC_Count
3288 LOOP
3289 xProgress := 'GASNOB-40-1720';
3290 dbms_sql.define_column ( v_DAC_SelectCursor,
3291 v_LoopCount,
3292 v_DAC_Select,
3293 ece_extract_utils_pub.G_MaxColWidth );
3294 END LOOP;
3295
3296 /* */
3297 /* Define the ROWIDs for the DELETE statements. */
3298 /* */
3299
3300 xProgress := 'GASNOB-40-1730';
3301 dbms_sql.define_column_rowid ( v_DAC_SelectCursor,
3302 v_DAC_Count + 1,
3303 v_DAC_RowID );
3304
3305 xProgress := 'GASNOB-40-1740';
3306 dbms_sql.define_column_rowid ( v_DAC_SelectCursor,
3307 v_DAC_Count + 2,
3308 v_DAC_XRowID );
3309
3310 /* */
3311 /* Define the internal ID columns. */
3312 /* */
3313
3314 xProgress := 'GASNO-40-1750';
3315 dbms_sql.define_column ( v_DAC_SelectCursor,
3316 v_DAC_Count + 3,
3317 v_TransactionRecordID );
3318
3319 xProgress := 'GASNOB-40-1770';
3320 FOR v_LoopCount IN 1..v_DTX_Count
3321 LOOP
3322 xProgress := 'GASNOB-40-1780';
3323 dbms_sql.define_column ( v_DTX_SelectCursor,
3324 v_LoopCount,
3325 v_DTX_Select,
3326 ece_extract_utils_pub.G_MaxColWidth );
3327 END LOOP;
3328
3329 /* */
3330 /* Define the ROWIDs for the DELETE statements. */
3331 /* */
3332
3333 xProgress := 'GASNOB-40-1790';
3334 dbms_sql.define_column_rowid ( v_DTX_SelectCursor,
3335 v_DTX_Count + 1,
3336 v_DTX_RowID );
3337
3338 xPRogress := 'GASNOB-40-1800';
3339 dbms_sql.define_column_rowid ( v_DTX_SelectCursor,
3340 v_DTX_Count + 2,
3341 v_DTX_XRowID );
3342
3343 /* */
3344 /* Define the internal ID columns. */
3345 /* */
3346
3347 xProgress := 'GASNOB-40-1805';
3348 dbms_sql.define_column ( v_DTX_SelectCursor,
3349 v_DTX_Count + 3,
3350 v_TransactionRecordID );
3351
3352 xProgress := 'GASNOB-40-1810';
3353 FOR v_LoopCount IN 1..v_ALL_Count
3354 LOOP
3355 xProgress := 'GASNOB-40-1811';
3356 dbms_sql.define_column ( v_ALL_SelectCursor,
3357 v_LoopCount,
3358 v_ALL_Select,
3359 ece_extract_utils_pub.G_MaxColWidth );
3360 END LOOP;
3361
3362 /* */
3363 /* Define the ROWIDs for the DELETE statements. */
3364 /* */
3365
3366 xProgress := 'GASNOB-40-1812';
3367 dbms_sql.define_column_rowid ( v_ALL_SelectCursor,
3368 v_ALL_Count + 1,
3369 v_ALL_RowID );
3370
3371 xProgress := 'GASNOB-40-1813';
3372 dbms_sql.define_column_rowid ( v_ALL_SelectCursor,
3373 v_ALL_Count + 2,
3374 v_ALL_XRowID );
3375
3376 /* */
3377 /* Define the internal ID columns. */
3378 /* */
3379
3380 xProgress := 'GASNOB-40-1814';
3381 dbms_sql.define_column ( v_ALL_SelectCursor,
3382 v_ALL_Count + 3,
3383 v_TransactionRecordID );
3384
3385 /* */
3386 /* Bind the variable in the SHP level SELECT clause. */
3387 /* */
3388
3389 xProgress := 'GASNOB-40-1810';
3390 dbms_sql.bind_variable ( v_SHP_SelectCursor,
3391 'Run_ID',
3392 p_RunID );
3393
3394 /* */
3395 /* Execute the Shipment level SELECT statement. */
3396 /* */
3397
3398 xProgress := 'GASNOB-40-1820';
3399 v_Dummy := dbms_sql.execute ( v_SHP_SelectCursor );
3400
3401 /* */
3402 /* Begin the Shipment level loop. */
3403 /* */
3404
3405 xProgress := 'GASNOB-40-1830';
3406 WHILE dbms_sql.fetch_rows ( v_SHP_SelectCursor ) > 0
3407 LOOP
3408
3409 /* */
3410 /* Store the returned values in the PL/SQL table. */
3411 /* */
3412
3413 xProgress := 'GASNOB-40-1840';
3414 FOR v_LoopCount IN 1..v_SHP_Count
3415 LOOP
3416 xProgress := 'GASNOB-40-1850';
3417 dbms_sql.column_value ( v_SHP_SelectCursor,
3418 v_LoopCount,
3419 v_SHP_Table(v_LoopCount).value );
3420 END LOOP;
3421
3422 /* */
3423 /* Store the ROWIDs. */
3424 /* */
3425
3426 xProgress := 'GASNOB-40-1860';
3427 dbms_sql.column_value ( v_SHP_SelectCursor,
3428 v_SHP_Count + 1,
3429 v_SHP_RowID );
3430
3431 xProgress := 'GASNOB-40-1870';
3432 dbms_sql.column_value ( v_SHP_SelectCursor,
3433 v_SHP_Count + 2,
3434 v_SHP_XRowID );
3435
3436 /* */
3437 /* Locate the necessary data elements and build the common key */
3438 /* record for this level. Common key elements are used for each */
3439 /* record, so save the values. */
3440 /* */
3441
3442 xProgress := 'GASNOB-40-1880';
3443 ece_flatfile_pvt.find_pos ( v_SHP_Table,
3444 /*ece_flatfile_pvt.G_Translator_Code, */
3445 'TP_CODE',
3446 v_TranslatorCodePosition );
3447 /*
3448 dbms_output.put_line(' v_shp_commonkeyname = ' || v_shp_commonkeyname);
3449 dbms_output.put_line(' v_shp_cknameposition = ' || v_shp_cknameposition);
3450 */
3451 xProgress := 'GASNOB-40-1890';
3452 ece_flatfile_pvt.find_pos ( v_SHP_Table,
3453 v_SHP_CommonKeyName,
3454 v_SHP_CKNamePosition );
3455
3456 xProgress := 'GASNOB-40-1900';
3457 v_RecordCommonKey0 := RPAD ( NVL(SUBSTRB ( v_SHP_Table(v_TranslatorCodePosition).value,
3458 1, 25 ),' '), 25 );
3459
3460 xProgress := 'GASNOB-40-1910';
3461 v_RecordCommonKey1 := RPAD ( NVL(SUBSTRB ( v_SHP_Table(v_SHP_CKNamePosition).value,
3462 1, 22 ),' '), 22 );
3463
3464 xProgress := 'GASNOB-40-1920';
3465 v_FileCommonKey := v_RecordCommonKey0 ||
3466 v_RecordCommonKey1 ||
3467 v_KeyPad ||
3468 v_KeyPad;
3469
3470 /* */
3471 /* Write the record to the output table. */
3472 /* */
3473
3474 xProgress := 'GASNOB-40-1930';
3475 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3476 p_CommunicationMethod,
3477 p_SHP_Interface,
3478 v_SHP_Table,
3479 p_OutputWidth,
3480 p_RunID,
3481 v_FileCommonKey );
3482
3483 /* */
3484 /* Store the values of the necessary elements (BOL_ID and */
3485 /* and Transaction_Record_ID) in the Shipment level SELECT clause */
3486 /* into local variables and use the values to bind the variables */
3487 /* in the SELECT clauses of the lower levels to come */
3488 /* */
3489
3490 xProgress := 'GASNOB-40-1940';
3491 dbms_sql.column_value ( v_SHP_SelectCursor,
3492 v_SHP_Count + 3,
3493 v_TransactionRecordID );
3494
3495
3496 xProgress := 'GASNOB-40-2020';
3497 ece_flatfile_pvt.find_pos( v_SHP_Table,
3498 'BOL_ID',
3499 v_BOL_ID_Position );
3500
3501 /* */
3502 /* Execute the Shipment Text SELECT statement. */
3503 /* */
3504 xProgress := 'GASNOB-40-2025';
3505 v_BOL_Id := TO_NUMBER ( v_SHP_Table(v_BOL_Id_Position).value );
3506
3507 xProgress := 'GASNOB-40-2028';
3508 dbms_sql.bind_variable ( v_STX_SelectCursor,
3509 'RUN_ID',
3510 v_RunID );
3511
3512 dbms_sql.bind_variable ( v_STX_SelectCursor,
3513 'BOL_ID',
3514 v_BOL_ID );
3515
3516 xProgress := 'GASNOB-40-2030';
3517 v_Dummy := dbms_sql.execute ( v_STX_SelectCursor );
3518
3519 /* */
3520 /* Fetch the (single) row, and store the returned values in the */
3521 /* PL/SQL table. */
3522 /* */
3523
3524 xProgress := 'GASNOB-40-2050';
3525 WHILE dbms_sql.fetch_rows(v_STX_SelectCursor) > 0
3526 LOOP
3527 FOR v_LoopCount IN 1..v_STX_Count
3528 LOOP
3529 xProgress := 'GASNOB-40-2060';
3530 dbms_sql.column_value ( v_STX_SelectCursor,
3531 v_LoopCount,
3532 v_STX_Table(v_LoopCount).value );
3533 END LOOP;
3534
3535 /* */
3536 /* Store the ROWIDs. */
3537 /* */
3538
3539 xProgress := 'GASNOB-40-2070';
3540 dbms_sql.column_value ( v_STX_SelectCursor,
3541 v_STX_Count + 1,
3542 v_STX_RowID );
3543
3544 xProgress := 'GASNOB-40-2080';
3545 dbms_sql.column_value ( v_STX_SelectCursor,
3546 v_STX_Count + 2,
3547 v_STX_XRowID );
3548
3549 xProgress := 'GASNOB-40-2082';
3550 ece_flatfile_pvt.find_pos ( v_STX_Table,
3551 v_STX_CommonKeyName,
3552 v_STX_CKNamePosition );
3553
3554 xProgress := 'GASNOB-40-2084';
3555 v_RecordCommonKey2 := RPAD ( NVL(SUBSTRB ( v_STX_Table(v_STX_CKNamePosition).value,
3556 1, 22 ),' '), 22 );
3557
3558 xProgress := 'GASNOB-40-2086';
3559 v_FileCommonKey := v_RecordCommonKey0 ||
3560 v_RecordCommonKey1 ||
3561 v_RecordCommonKey2 ||
3562 v_KeyPad;
3563 /* */
3564 /* Write the record to the output table. */
3565 /* */
3566
3567 xProgress := 'GASNOB-40-2090';
3568 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3569 p_CommunicationMethod,
3570 p_STX_Interface,
3571 v_STX_Table,
3572 p_OutputWidth,
3573 p_RunID,
3574 v_FileCommonKey );
3575
3576 xProgress := 'GASNOB-40-2091';
3577 dbms_sql.bind_variable ( v_STX_DeleteCursor,
3578 'Row_ID',
3579 v_STX_RowID );
3580
3581 xProgress := 'GASNOB-40-2092';
3582 dbms_sql.bind_variable ( v_STX_XDeleteCursor,
3583 'Row_ID',
3584 v_STX_XRowID );
3585
3586 xProgress := 'GASNOB-40-2093';
3587 v_Dummy := dbms_sql.execute ( v_STX_DeleteCursor );
3588
3589 xProgress := 'GASNOB-40-2094';
3590 v_Dummy := dbms_sql.execute ( v_STX_XDeleteCursor );
3591
3592 END LOOP; /* while stx */
3593
3594 /* */
3595 /* Execute the Order level SELECT statement. */
3596 /* */
3597 xProgress := 'GASNOB-40-2095';
3598 dbms_sql.bind_variable ( v_ORD_SelectCursor,
3599 'RUN_ID',
3600 v_RunID );
3601
3602 dbms_sql.bind_variable ( v_ORD_SelectCursor,
3603 'BOL_ID',
3604 v_BOL_ID );
3605
3606 xProgress := 'GASNOB-40-2100';
3607 v_Dummy := dbms_sql.execute ( v_ORD_SelectCursor );
3608
3609 /* */
3610 /* Begin the Order level loop. */
3611 /* */
3612 xProgress := 'GASNOB-40-2110';
3613 WHILE dbms_sql.fetch_rows ( v_ORD_SelectCursor ) > 0
3614 LOOP
3615 xProgress := 'GASNOB-40-2120';
3616 FOR v_LoopCount IN 1..v_ORD_Count
3617 LOOP
3618 xPRogress := 'GASNOB-40-2130';
3619 dbms_sql.column_value ( v_ORD_SelectCursor,
3620 v_LoopCount,
3621 v_ORD_Table(v_LoopCount).value );
3622 END LOOP;
3623
3624 /* */
3625 /* Store the ROWIDs. */
3626 /* */
3627
3628 xProgress := 'GASNOB-40-2140';
3629 dbms_sql.column_value ( v_ORD_SelectCursor,
3630 v_ORD_Count + 1,
3631 v_ORD_RowID );
3632
3633 xProgress := 'GASNOB-40-2150';
3634 dbms_sql.column_value ( v_ORD_SelectCursor,
3635 v_ORD_Count + 2,
3636 v_ORD_XRowID );
3637
3638 xProgress := 'GASNOB-40-2151';
3639 ece_flatfile_pvt.find_pos ( v_ORD_Table,
3640 v_ORD_CommonKeyName,
3641 v_ORD_CKNamePosition );
3642
3643 xProgress := 'GASNOB-40-2152';
3644 v_RecordCommonKey2 := RPAD ( NVL(SUBSTRB ( v_ORD_Table(v_ORD_CKNamePosition).value,
3645 1, 22 ),' '), 22 );
3646
3647 xProgress := 'GASNOB-40-2153';
3648 v_FileCommonKey := v_RecordCommonKey0 ||
3649 v_RecordCommonKey1 ||
3650 v_RecordCommonKey2 ||
3651 v_KeyPad;
3652
3653 /* */
3654 /* Write the record to the output table. */
3655 /* */
3656
3657 xProgress := 'GASNOB-40-2160';
3658 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3659 p_CommunicationMethod,
3660 p_ORD_Interface,
3661 v_ORD_Table,
3662 p_OutputWidth,
3663 p_RunID,
3664 v_FileCommonKey );
3665
3666 /* */
3667 /* Bind the variables (ROWIDs) in the DELETE statements. */
3668 /* */
3669
3670 xProgress := 'GASNOB-40-2170';
3671 dbms_sql.bind_variable ( v_ORD_DeleteCursor,
3672 'Row_ID',
3673 v_ORD_RowID );
3674
3675 xProgress := 'GASNOB-40-2180';
3676 dbms_sql.bind_variable ( v_ORD_XDeleteCursor,
3677 'Row_ID',
3678 v_ORD_XRowID );
3679
3680 /* */
3681 /* Delete the rows from the interface table. */
3682 /* */
3683
3684 xProgress := 'GASNOB-40-2190';
3685 v_Dummy := dbms_sql.execute ( v_ORD_DeleteCursor );
3686
3687 xProgress := 'GASNOB-40-2200';
3688 v_Dummy := dbms_sql.execute ( v_ORD_XDeleteCursor );
3689
3690 /* */
3691 /* Execute the Order DAC SELECT statement. */
3692 /* */
3693 xProgress := 'GASNOB-40-2205';
3694 ece_flatfile_pvt.find_pos( v_ORD_Table,
3695 'Order_ID',
3696 v_Order_ID_Position );
3697
3698 xProgress := 'GASNOB-40-2206';
3699 v_Order_Id := TO_NUMBER ( v_ORD_Table(v_Order_Id_Position).value );
3700
3701 xProgress := 'GASNOB-40-2207';
3702 dbms_sql.bind_variable ( v_OAC_SelectCursor,
3703 'RUN_ID',
3704 v_RunID );
3705
3706 dbms_sql.bind_variable ( v_OAC_SelectCursor,
3707 'Order_ID',
3708 v_Order_ID );
3709
3710 xProgress := 'GASNOB-40-2210';
3711 v_Dummy := dbms_sql.execute ( v_OAC_SelectCursor );
3712
3713 /* */
3714 /* Begin the Order DAC loop. */
3715 /* */
3716
3717 xProgress := 'GASNOB-40-2220';
3718 WHILE dbms_sql.fetch_rows ( v_OAC_SelectCursor ) > 0
3719 LOOP
3720 xProgress := 'GASNOB-40-2230';
3721 FOR v_LoopCount IN 1..v_OAC_Count
3722 LOOP
3723 xProgress := 'GASNOB-40-2240';
3724 dbms_sql.column_value ( v_OAC_SelectCursor,
3725 v_LoopCount,
3726 v_OAC_Table(v_LoopCount).value );
3727 END LOOP;
3728
3729 /* */
3730 /* Store the ROWIDs. */
3731 /* */
3732
3733 xProgress := 'GASNOB-40-2250';
3734 dbms_sql.column_value ( v_OAC_SelectCursor,
3735 v_OAC_Count + 1,
3736 v_OAC_RowID );
3737
3738 xProgress := 'GASNOB-40-2260';
3739 dbms_sql.column_value ( v_OAC_SelectCursor,
3740 v_OAC_Count + 2,
3741 v_OAC_XRowID );
3742
3743 xProgress := 'GASNOB-40-2270';
3744 ece_flatfile_pvt.find_pos ( v_OAC_Table,
3745 v_OAC_CommonKeyName,
3746 v_OAC_CKNamePosition);
3747
3748 xProgress := 'GASNOB-40-2280';
3749 v_RecordCommonKey3 := RPAD ( NVL(SUBSTRB ( v_OAC_Table(v_OAC_CKNamePosition).value,
3750 1, 22 ),' '), 22 );
3751
3752 xProgress := 'GASNOB-40-2290';
3753 v_FileCommonKey := v_RecordCommonKey0 ||
3754 v_RecordCommonKey1 ||
3755 v_RecordCommonKey2 ||
3756 v_RecordCommonKey3;
3757 ec_debug.pl ( 3, 'v_FileCommonKey: ',v_FileCommonKey );
3758
3759 /* */
3760 /* Write the record to the output table. */
3761 /* */
3762
3763 xProgress := 'GASNOB-40-2300';
3764 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3765 p_CommunicationMethod,
3766 p_OAC_Interface,
3767 v_OAC_Table,
3768 p_OutputWidth,
3769 p_RunID,
3770 v_FileCommonKey );
3771
3772 xProgress := 'GASNOB-40-2310';
3773 dbms_sql.bind_variable ( v_OAC_DeleteCursor,
3774 'Row_ID',
3775 v_OAC_RowID );
3776
3777 xProgress := 'GASNOB-40-2320';
3778 dbms_sql.bind_variable ( v_OAC_XDeleteCursor,
3779 'Row_ID',
3780 v_OAC_XRowID );
3781
3782 xProgress := 'GASNOB-40-2330';
3783 v_Dummy := dbms_sql.execute ( v_OAC_DeleteCursor );
3784
3785 xProgress := 'GASNOB-40-2340';
3786 v_Dummy := dbms_sql.execute ( v_OAC_XDeleteCursor );
3787 END LOOP; /* while oac */
3788
3789 /* */
3790 /* Execute the Order Text SELECT statement. */
3791 /* */
3792 xProgress := 'GASNOB-40-2350';
3793 dbms_sql.bind_variable ( v_OTX_SelectCursor,
3794 'RUN_ID',
3795 v_RunID );
3796
3797 dbms_sql.bind_variable ( v_OTX_SelectCursor,
3798 'Order_ID',
3799 v_Order_ID );
3800
3801 xProgress := 'GASNOB-40-2360';
3802 v_Dummy := dbms_sql.execute ( v_OTX_SelectCursor );
3803
3804 /* */
3805 /* Begin the Order Text loop. */
3806 /* */
3807
3808 xProgress := 'GASNOB-40-2370';
3809 WHILE dbms_sql.fetch_rows ( v_OTX_SelectCursor ) > 0
3810 LOOP
3811 xProgress := 'GASNOB-40-2380';
3812 FOR v_LoopCount IN 1..v_OTX_Count
3813 LOOP
3814 xProgress := 'GASNOB-40-2390';
3815 dbms_sql.column_value ( v_OTX_SelectCursor,
3816 v_LoopCount,
3817 v_OTX_Table(v_LoopCount).value );
3818 END LOOP;
3819
3820 /* */
3821 /* Store the ROWIDs. */
3822 /* */
3823
3824 xProgress := 'GASNOB-40-2400';
3825 dbms_sql.column_value ( v_OTX_SelectCursor,
3826 v_OTX_Count + 1,
3827 v_OTX_RowID );
3828
3829 xProgress := 'GASNOB-40-2410';
3830 dbms_sql.column_value ( v_OTX_SelectCursor,
3831 v_OTX_Count + 2,
3832 v_OTX_XRowID );
3833
3834 xProgress := 'GASNOB-40-2420';
3835 ece_flatfile_pvt.find_pos ( v_OTX_Table,
3836 v_OTX_CommonKeyName,
3837 v_OTX_CKNamePosition);
3838
3839 xProgress := 'GASNOB-40-2430';
3840 v_RecordCommonKey3 := RPAD ( NVL(SUBSTRB ( v_OTX_Table(v_OTX_CKNamePosition).value,
3841 1, 22 ),' '), 22 );
3842
3843 xProgress := 'GASNOB-40-2440';
3844 v_FileCommonKey := v_RecordCommonKey0 ||
3845 v_RecordCommonKey1 ||
3846 v_RecordCommonKey2 ||
3847 v_RecordCommonKey3;
3848
3849 /* */
3850 /* Write the record to the output table. */
3851 /* */
3852
3853 xProgress := 'GASNOB-40-2450';
3854 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3855 p_CommunicationMethod,
3856 p_OTX_Interface,
3857 v_OTX_Table,
3858 p_OutputWidth,
3859 p_RunID,
3860 v_FileCommonKey );
3861
3862 xProgress := 'GASNOB-40-2460';
3863 dbms_sql.bind_variable ( v_OTX_DeleteCursor,
3864 'Row_ID',
3865 v_OTX_RowID );
3866
3867 xProgress := 'GASNOB-40-2470';
3868 dbms_sql.bind_variable ( v_OTX_XDeleteCursor,
3869 'Row_ID',
3870 v_OTX_XRowID );
3871
3872 xProgress := 'GASNOB-40-2480';
3873 v_Dummy := dbms_sql.execute ( v_OTX_DeleteCursor );
3874
3875 xProgress := 'GASNOB-40-2490';
3876 v_Dummy := dbms_sql.execute ( v_OTX_XDeleteCursor );
3877 END LOOP; /* while otx */
3878
3879 /* */
3880 /* Execute the Detail level SELECT statement. */
3881 /* */
3882 xProgress := 'GASNOB-40-2500';
3883 dbms_sql.bind_variable ( v_DTL_SelectCursor,
3884 'RUN_ID',
3885 v_RunID );
3886
3887 dbms_sql.bind_variable ( v_DTL_SelectCursor,
3888 'BOL_ID',
3889 v_BOL_ID );
3890
3891 dbms_sql.bind_variable ( v_DTL_SelectCursor,
3892 'Order_ID',
3893 v_Order_ID );
3894
3895 xProgress := 'GASNOB-40-2510';
3896 v_Dummy := dbms_sql.execute ( v_DTL_SelectCursor );
3897
3898 /* */
3899 /* Begin the Detail level loop. */
3900 /* */
3901 xProgress := 'GASNOB-40-2520';
3902 WHILE dbms_sql.fetch_rows ( v_DTL_SelectCursor ) > 0
3903 LOOP
3904 xProgress := 'GASNOB-40-2530';
3905 FOR v_LoopCount IN 1..v_DTL_Count
3906 LOOP
3907 xProgress := 'GASNOB-40-2540';
3908 dbms_sql.column_value ( v_DTL_SelectCursor,
3909 v_LoopCount,
3910 v_DTL_Table(v_LoopCount).value);
3911 END LOOP;
3912
3913 /* */
3914 /* Store the ROWIDs. */
3915 /* */
3916
3917 xProgress := 'GASNOB-40-2550';
3918 dbms_sql.column_value ( v_DTL_SelectCursor,
3919 v_DTL_Count + 1,
3920 v_DTL_RowID );
3921
3922 xProgress := 'GASNOB-40-2560';
3923 dbms_sql.column_value ( v_DTL_SelectCursor,
3924 v_DTL_Count + 2,
3925 v_DTL_XRowID );
3926
3927 /* */
3928 /* Find the Line Number in the PL/SQL table and add this */
3929 /* value to the common key. */
3930 /* */
3931
3932 xProgress := 'GASNOB-40-2570';
3933 ece_flatfile_pvt.find_pos ( v_DTL_Table,
3934 v_DTL_CommonKeyName,
3935 v_DTL_CKNamePosition);
3936
3937 xProgress := 'GASNOB-40-2580';
3938 v_RecordCommonKey3 := RPAD ( NVL(SUBSTRB ( v_DTL_Table(v_DTL_CKNamePosition).value,
3939 1, 22 ),' '), 22 );
3940
3941 xProgress := 'GASNOB-40-2590';
3942 v_FileCommonKey := v_RecordCommonKey0 ||
3943 v_RecordCommonKey1 ||
3944 v_RecordCommonKey2 ||
3945 v_RecordCommonKey3;
3946 ec_debug.pl ( 3, 'v_FileCommonKey: ',v_FileCommonKey );
3947
3948 /* */
3949 /* Write the record to the output table. */
3950 /* */
3951
3952 xProgress := 'GASNOB-40-2600';
3953 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
3954 p_CommunicationMethod,
3955 p_DTL_Interface,
3956 v_DTL_Table,
3957 p_OutputWidth,
3958 p_RunID,
3959 v_FileCommonKey );
3960
3961 xProgress := 'GASNOB-40-2610';
3962 dbms_sql.bind_variable ( v_DTL_DeleteCursor,
3963 'Row_ID',
3964 v_DTL_RowID );
3965
3966 xProgress := 'GASNOB-40-2620';
3967 dbms_sql.bind_variable ( v_DTL_XDeleteCursor,
3968 'Row_ID',
3969 v_DTL_XRowID );
3970
3971 /* */
3972 /* Delete the rows from the interface table. */
3973 /* */
3974
3975 xProgress := 'GASNOB-40-2630';
3976 v_Dummy := dbms_sql.execute ( v_DTL_DeleteCursor );
3977
3978 xProgress := 'GASNOB-40-2640';
3979 v_Dummy := dbms_sql.execute ( v_DTL_XDeleteCursor );
3980
3981 /* */
3982 /* Execute the Detail DAC SELECT statement. */
3983 /* */
3984 xProgress := 'GASNOB-40-2650';
3985 ece_flatfile_pvt.find_pos( v_DTL_Table,
3986 'Line_ID',
3987 v_Line_ID_Position );
3988
3989 xProgress := 'GASNOB-40-2660';
3990 v_Line_Id := TO_NUMBER ( v_DTL_Table(v_Line_Id_Position).value );
3991 ec_debug.pl ( 3, 'v_Line_Id: ', v_Line_Id );
3992
3993 xProgress := 'GASNOB-40-2670';
3994 dbms_sql.bind_variable ( v_DAC_SelectCursor,
3995 'RUN_ID',
3996 v_RunID );
3997
3998 dbms_sql.bind_variable ( v_DAC_SelectCursor,
3999 'Line_ID',
4000 v_Line_ID );
4001
4002 xProgress := 'GASNOB-40-2680';
4003 v_Dummy := dbms_sql.execute ( v_DAC_SelectCursor );
4004
4005 /* */
4006 /* Begin the DETAIL DAC loop. */
4007 /* */
4008
4009 xProgress := 'GASNOB-40-2690';
4010 WHILE dbms_sql.fetch_rows ( v_DAC_SelectCursor ) > 0
4011 LOOP
4012 xProgress := 'GASNOB-40-2700';
4013 FOR v_LoopCount IN 1..v_DAC_Count
4014 LOOP
4015 xProgress := 'GASNOB-40-2710';
4016 dbms_sql.column_value ( v_DAC_SelectCursor,
4017 v_LoopCount,
4018 v_DAC_Table(v_LoopCount).value );
4019 END LOOP;
4020
4021 /* */
4022 /* Store the ROWIDs. */
4023 /* */
4024
4025 xProgress := 'GASNOB-40-2720';
4026 dbms_sql.column_value ( v_DAC_SelectCursor,
4027 v_DAC_Count + 1,
4028 v_DAC_RowID );
4029
4030 xProgress := 'GASNOB-40-2730';
4031 dbms_sql.column_value ( v_DAC_SelectCursor,
4032 v_DAC_Count + 2,
4033 v_DAC_XRowID );
4034
4035
4036 xProgress := 'GASNOB-40-2740';
4037 v_FileCommonKey := v_RecordCommonKey0 ||
4038 v_RecordCommonKey1 ||
4039 v_RecordCommonKey2 ||
4040 v_RecordCommonKey3;
4041 ec_debug.pl ( 3, 'v_FileCommonKey: ', v_FileCommonKey );
4042
4043 /* */
4044 /* Write the record to the output table. */
4045 /* */
4046
4047 xProgress := 'GASNOB-40-2770';
4048 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
4049 p_CommunicationMethod,
4050 p_DAC_Interface,
4051 v_DAC_Table,
4052 p_OutputWidth,
4053 p_RunID,
4054 v_FileCommonKey );
4055
4056 xProgress := 'GASNOB-40-2780';
4057 dbms_sql.bind_variable ( v_DAC_DeleteCursor,
4058 'Row_ID',
4059 v_DAC_RowID );
4060
4061 xProgress := 'GASNOB-40-2790';
4062 dbms_sql.bind_variable ( v_DAC_XDeleteCursor,
4063 'Row_ID',
4064 v_DAC_XRowID );
4065
4066 xProgress := 'GASNOB-40-2800';
4067 v_Dummy := dbms_sql.execute ( v_DAC_DeleteCursor );
4068
4069 xProgress := 'GASNOB-40-2810';
4070 v_Dummy := dbms_sql.execute ( v_DAC_XDeleteCursor );
4071 END LOOP; /* while Dac */
4072
4073 xProgress := 'GASNOB-40-2840';
4074 dbms_sql.bind_variable ( v_DTX_SelectCursor,
4075 'RUN_ID',
4076 v_RunID );
4077
4078 dbms_sql.bind_variable ( v_DTX_SelectCursor,
4079 'Line_ID',
4080 v_Line_ID );
4081
4082 xProgress := 'GASNOB-40-2850';
4083 v_Dummy := dbms_sql.execute ( v_DTX_SelectCursor );
4084
4085 /* */
4086 /* Begin the DETAIL TEXT loop. */
4087 /* */
4088
4089 xProgress := 'GASNOB-40-2860';
4090 WHILE dbms_sql.fetch_rows ( v_DTX_SelectCursor ) > 0
4091 LOOP
4092 xProgress := 'GASNOB-40-2870';
4093 FOR v_LoopCount IN 1..v_DTX_Count
4094 LOOP
4095 xProgress := 'GASNOB-40-2880';
4096 dbms_sql.column_value ( v_DTX_SelectCursor,
4097 v_LoopCount,
4098 v_DTX_Table(v_LoopCount).value );
4099 END LOOP;
4100
4101 /* */
4102 /* Store the ROWIDs. */
4103 /* */
4104
4105 xProgress := 'GASNOB-40-2890';
4106 dbms_sql.column_value ( v_DTX_SelectCursor,
4107 v_DTX_Count + 1,
4108 v_DTX_RowID );
4109
4110 xProgress := 'GASNOB-40-2900';
4111 dbms_sql.column_value ( v_DTX_SelectCursor,
4112 v_DTX_Count + 2,
4113 v_DTX_XRowID );
4114
4115
4116 xProgress := 'GASNOB-40-2910';
4117 v_FileCommonKey := v_RecordCommonKey0 ||
4118 v_RecordCommonKey1 ||
4119 v_RecordCommonKey2 ||
4120 v_RecordCommonKey3;
4121 ec_debug.pl ( 3, 'v_FileCommonKey: ', v_FileCommonKey );
4122
4123 /* */
4124 /* Write the record to the output table. */
4125 /* */
4126
4127 xProgress := 'GASNOB-40-2920';
4128 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
4129 p_CommunicationMethod,
4130 p_DTX_Interface,
4131 v_DTX_Table,
4132 p_OutputWidth,
4133 p_RunID,
4134 v_FileCommonKey );
4135
4136 xProgress := 'GASNOB-40-2930';
4137 dbms_sql.bind_variable ( v_DTX_DeleteCursor,
4138 'Row_ID',
4139 v_DTX_RowID );
4140
4141 xProgress := 'GASNOB-40-2940';
4142 dbms_sql.bind_variable ( v_DTX_XDeleteCursor,
4143 'Row_ID',
4144 v_DTX_XRowID );
4145
4146 xProgress := 'GASNOB-40-2950';
4147 v_Dummy := dbms_sql.execute ( v_DTX_DeleteCursor );
4148
4149 xProgress := 'GASNOB-40-2960';
4150 v_Dummy := dbms_sql.execute ( v_DTX_XDeleteCursor );
4151 END LOOP; /* while dtx */
4152
4153 xProgress := 'GASNOB-40-2970';
4154 dbms_sql.bind_variable ( v_ALL_SelectCursor,
4155 'RUN_ID',
4156 v_RunID );
4157
4158 dbms_sql.bind_variable ( v_ALL_SelectCursor,
4159 'Line_ID',
4160 v_Line_ID );
4161
4162 xProgress := 'GASNOB-40-2980';
4163 v_Dummy := dbms_sql.execute ( v_ALL_SelectCursor );
4164
4165 /* */
4166 /* Begin the DETAIL ALOCATIONS loop */
4167 /* */
4168
4169 xProgress := 'GASNOB-40-2990';
4170 WHILE dbms_sql.fetch_rows ( v_ALL_SelectCursor ) > 0
4171 LOOP
4172 xProgress := 'GASNOB-40-2993';
4173 FOR v_LoopCount IN 1..v_ALL_Count
4174 LOOP
4175 xProgress := 'GASNOB-40-2995';
4176 dbms_sql.column_value ( v_ALL_SelectCursor,
4177 v_LoopCount,
4178 v_ALL_Table(v_LoopCount).value );
4179 END LOOP;
4180
4181 /* */
4182 /* Store the ROWIDs. */
4183 /* */
4184
4185 xProgress := 'GASNOB-40-3000';
4186 dbms_sql.column_value ( v_ALL_SelectCursor,
4187 v_ALL_Count + 1,
4188 v_ALL_RowID );
4189
4190 xProgress := 'GASNOB-40-3010';
4191 dbms_sql.column_value ( v_ALL_SelectCursor,
4192 v_ALL_Count + 2,
4193 v_ALL_XRowID );
4194
4195
4196 xProgress := 'GASNOB-40-3020';
4197 v_FileCommonKey := v_RecordCommonKey0 ||
4198 v_RecordCommonKey1 ||
4199 v_RecordCommonKey2 ||
4200 v_RecordCommonKey3;
4201 ec_debug.pl ( 3, 'v_FileCommonKey: ', v_FileCommonKey );
4202
4203 /* */
4204 /* Write the record to the output table. */
4205 /* */
4206
4207 xProgress := 'GASNOB-40-3030';
4208 ece_flatfile_pvt.write_to_ece_output ( p_TransactionType,
4209 p_CommunicationMethod,
4210 p_ALL_Interface,
4211 v_ALL_Table,
4212 p_OutputWidth,
4213 p_RunID,
4214 v_FileCommonKey );
4215
4216 xProgress := 'GASNOB-40-3040';
4217 dbms_sql.bind_variable ( v_ALL_DeleteCursor,
4218 'Row_ID',
4219 v_ALL_RowID );
4220
4221 xProgress := 'GASNOB-40-3050';
4222 dbms_sql.bind_variable ( v_ALL_XDeleteCursor,
4223 'Row_ID',
4224 v_ALL_XRowID );
4225
4226 xProgress := 'GASNOB-40-3060';
4227 v_Dummy := dbms_sql.execute ( v_ALL_DeleteCursor );
4228
4229 xProgress := 'GASNOB-40-3070';
4230 v_Dummy := dbms_sql.execute ( v_ALL_XDeleteCursor );
4231 END LOOP; /* while all */
4232 END LOOP; /* while dtl */
4233 END LOOP; /* while ord */
4234 xProgress := 'GASNOB-40-3140';
4235 dbms_sql.bind_variable ( v_SHP_DeleteCursor,
4236 'Row_ID',
4237 v_SHP_RowID );
4238
4239 xProgress := 'GASNOB-40-3150';
4240 dbms_sql.bind_variable ( v_SHP_XDeleteCursor,
4241 'Row_ID',
4242 v_SHP_XRowID );
4243
4244 xProgress := 'GASNOB-40-3160';
4245 v_Dummy := dbms_sql.execute ( v_SHP_DeleteCursor );
4246
4247 xProgress := 'GASNOB-40-3160';
4248 v_Dummy := dbms_sql.execute ( v_SHP_XDeleteCursor );
4249 END LOOP; /* while shp */
4250
4251 /* */
4252 /* Commit the interface table DELETEs. */
4253 /* */
4254
4255 xProgress := 'GASNOB-40-3071';
4256 COMMIT;
4257
4258 /* */
4259 /* Close all open cursors. */
4260 /* */
4261
4262 xProgress := 'GASNOB-40-3080';
4263 dbms_sql.close_cursor ( v_SHP_SelectCursor );
4264 xProgress := 'GASNOB-40-3081';
4265 dbms_sql.close_cursor ( v_STX_SelectCursor );
4266 xProgress := 'GASNOB-40-3082';
4267 dbms_sql.close_cursor ( v_ORD_SelectCursor );
4268 xProgress := 'GASNOB-40-3083';
4269 dbms_sql.close_cursor ( v_OAC_SelectCursor );
4270 xProgress := 'GASNOB-40-3084';
4271 dbms_sql.close_cursor ( v_OTX_SelectCursor );
4272 xProgress := 'GASNOB-40-3085';
4273 dbms_sql.close_cursor ( v_DTL_SelectCursor );
4274 xProgress := 'GASNOB-40-3086';
4275 dbms_sql.close_cursor ( v_DAC_SelectCursor );
4276 xProgress := 'GASNOB-40-3087';
4277 dbms_sql.close_cursor ( v_DTX_SelectCursor );
4278 xProgress := 'GASNOB-40-3088';
4279 dbms_sql.close_cursor ( v_ALL_SelectCursor );
4280
4281 xProgress := 'GASNOB-40-3088';
4282 dbms_sql.close_cursor ( v_SHP_DeleteCursor );
4283 xProgress := 'GASNOB-40-3089';
4284 dbms_sql.close_cursor ( v_STX_DeleteCursor );
4285 xProgress := 'GASNOB-40-3090';
4286 dbms_sql.close_cursor ( v_ORD_DeleteCursor );
4287 xProgress := 'GASNOB-40-3091';
4288 dbms_sql.close_cursor ( v_OAC_DeleteCursor );
4289 xProgress := 'GASNOB-40-3092';
4290 dbms_sql.close_cursor ( v_OTX_DeleteCursor );
4291 xProgress := 'GASNOB-40-3093';
4292 dbms_sql.close_cursor ( v_DTL_DeleteCursor );
4293 xProgress := 'GASNOB-40-3094';
4294 dbms_sql.close_cursor ( v_DAC_DeleteCursor );
4295 xProgress := 'GASNOB-40-3095';
4296 dbms_sql.close_cursor ( v_DTX_DeleteCursor );
4297 xProgress := 'GASNOB-40-3095';
4298 dbms_sql.close_cursor ( v_ALL_DeleteCursor );
4299
4300 xProgress := 'GASNOB-40-3096';
4301 dbms_sql.close_cursor ( v_SHP_XDeleteCursor );
4302 xProgress := 'GASNOB-40-3097';
4303 dbms_sql.close_cursor ( v_STX_XDeleteCursor );
4304 xProgress := 'GASNOB-40-3098';
4305 dbms_sql.close_cursor ( v_ORD_XDeleteCursor );
4306 xProgress := 'GASNOB-40-3099';
4307 dbms_sql.close_cursor ( v_OAC_XDeleteCursor );
4308 xProgress := 'GASNOB-40-3100';
4309 dbms_sql.close_cursor ( v_OTX_XDeleteCursor );
4310 xProgress := 'GASNOB-40-3101';
4311 dbms_sql.close_cursor ( v_DTL_XDeleteCursor );
4312 xProgress := 'GASNOB-40-3102';
4313 dbms_sql.close_cursor ( v_DAC_XDeleteCursor );
4314 xProgress := 'GASNOB-40-3103';
4315 dbms_sql.close_cursor ( v_DTX_XDeleteCursor );
4316 xProgress := 'GASNOB-40-3104';
4317 dbms_sql.close_cursor ( v_ALL_XDeleteCursor );
4318
4319 EXCEPTION
4320 WHEN OTHERS THEN
4321
4322 ec_debug.pl ( 0,
4323 'EC',
4324 'ECE_PROGRAM_ERROR',
4325 'PROGRESS_LEVEL',
4326 xProgress );
4327
4328 ec_debug.pl ( 0,
4329 'EC',
4330 'ECE_ERROR_MESSAGE',
4331 'ERROR_MESSAGE',
4332 SQLERRM );
4333
4334 app_exception.raise_exception;
4335
4336 END Put_Data_To_Output_Table;
4337
4338 END GML_GASNO;