[Home] [Help]
PACKAGE BODY: APPS.ECE_SPSO_TRANS1
Source
1 PACKAGE BODY ece_spso_trans1 AS
2 -- $Header: ECSPSOB.pls 120.2.12000000.3 2007/03/09 14:40:32 cpeixoto ship $
3
4 /*===========================================================================
5
6 PROCEDURE NAME: Extract_SPSO_Outbound
7
8 PURPOSE: This procedure initiates the concurrent process to
9 extract the eligible transactions.
10
11 ===========================================================================*/
12
13 PROCEDURE Extract_SPSO_Outbound ( errbuf OUT NOCOPY VARCHAR2,
14 retcode OUT NOCOPY VARCHAR2,
15 cOutput_Path IN VARCHAR2,
16 cOutput_Filename IN VARCHAR2,
17 p_schedule_id IN VARCHAR2 default 0,
18 v_debug_mode IN NUMBER default 0,
19 p_batch_id IN NUMBER default 0) -- Bug 2064311
20 IS
21
22 p_communication_method VARCHAR2(120) := 'EDI';
23 p_transaction_type VARCHAR2(120) := 'SPSO';
24 p_document_type VARCHAR2(120) := 'SPS';
25 l_line_text VARCHAR2(2000);
26 uFile_type utl_file.file_type;
27 p_output_width INTEGER := 4000;
28 p_run_id INTEGER;
29 p_header_interface VARCHAR2(120) := 'ECE_SPSO_HEADERS';
30 p_item_interface VARCHAR2(120) := 'ECE_SPSO_ITEMS';
31 p_item_d_interface VARCHAR2(120) := 'ECE_SPSO_ITEM_DET';
32 p_transaction_date DATE := SYSDATE;
33 xProgress VARCHAR2(30);
34 cEnabled VARCHAR2(1) := 'Y';
35 ece_transaction_disabled EXCEPTION;
36
37
38 CURSOR c_output IS
39 SELECT text
40 FROM ece_output
41 WHERE run_id = p_run_id
42 ORDER BY line_id;
43
44 BEGIN
45
46 ec_debug.enable_debug(v_debug_mode);
47 ec_debug.push ( 'ECE_SPSO_Trans1.Extract_SPSO_Outbound' );
48 ec_debug.pl ( 3, 'cOutput_Path: ',cOutput_Path );
49 ec_debug.pl ( 3, 'cOutput_Filename: ',cOutput_Filename );
50 ec_debug.pl ( 3, 'p_schedule_id: ',p_schedule_id );
51 ec_debug.pl ( 3, 'v_debug_mode: ',v_debug_mode );
52 ec_debug.pl(3,'p_batch_id: ',p_batch_id);
53 /* Check to see if the transaction is enabled. If not, abort */
54 xProgress := 'SPSO-10-1001';
55 fnd_profile.get('ECE_' || p_Transaction_Type || '_ENABLED',cEnabled);
56
57 xProgress := 'SPSO-10-1002';
58 IF cEnabled = 'N' THEN
59 xProgress := 'SPSO-10-1003';
60 RAISE ece_transaction_disabled;
61 END IF;
62
63 xProgress := 'SPSO-10-1005';
64 BEGIN
65 SELECT ece_output_runs_s.NEXTVAL
66 INTO p_run_id
67 FROM sys.dual;
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN
70 ec_debug.pl ( 0,
71 'EC',
72 'ECE_GET_NEXT_SEQ_FAILED',
73 'PROGRESS_LEVEL',
74 xProgress,
75 'SEQ',
76 'ECE_OUTPUT_RUNS_S' );
77 END;
78 ec_debug.pl ( 3, 'p_run_id: ',p_run_id );
79
80 xProgress := 'SPSO-10-1010';
81 ec_debug.pl ( 0, 'EC', 'ECE_SPSO_START', NULL );
82
83 xProgress := 'SPSO-10-1020';
84 ec_debug.pl ( 0, 'EC', 'ECE_RUN_ID', 'RUN_ID', p_run_id );
85
86 xProgress := 'SPSO-10-1030';
87 ece_spso_trans1.populate_supplier_sched_api1 ( p_communication_method,
88 p_transaction_type,
89 p_transaction_date,
90 p_run_id,
91 p_document_type,
92 p_schedule_id,
93 p_batch_id,
94 p_header_interface,
95 p_item_interface,
96 p_item_d_interface );
97
98 xProgress := 'SPSO-10-1040';
99 ece_spso_trans2.populate_supplier_sched_api2 ( p_communication_method,
100 p_transaction_type,
101 p_document_type,
102 p_run_id,
103 p_schedule_id,
104 p_batch_id);
105
106 xProgress := 'SPSO-10-1050';
107 ece_spso_trans1.populate_supplier_sched_api3 ( p_communication_method,
108 p_transaction_type,
109 p_document_type,
110 p_run_id,
111 p_schedule_id,
112 p_batch_id);
113
114 xProgress := 'SPSO-10-1060';
115 ece_spso_trans1.put_data_to_output_table ( p_communication_method,
116 p_transaction_type,
117 p_output_width,
118 p_run_id,
119 p_header_interface,
120 p_item_interface,
121 p_item_d_interface );
122
123 xProgress := 'SPSO-10-1070';
124
125 -- Open the cursor to select the actual file output from ece_output.
126
127 xProgress := 'SPSO-10-1080';
128 OPEN c_output;
129 LOOP
130 FETCH c_output
131 INTO l_line_text;
132
133 if (c_output%ROWCOUNT > 0) then
134 if (NOT utl_file.is_open(uFile_type)) then
135 uFile_type := utl_file.fopen ( cOutput_Path,
136 cOutput_Filename,
137 'W' );
138 end if;
139 end if;
140
141 EXIT WHEN c_output%NOTFOUND;
142
143 -- Write the data from ece_output to the output file.
144
145 xProgress := 'SPSO-10-1090';
146 utl_file.put_line ( uFile_type,l_line_text );
147 ec_debug.pl ( 3, 'l_line_text: ',l_line_text );
148
149 END LOOP;
150
151 CLOSE c_output;
152
153 -- Close the output file.
154
155 xProgress := 'SPSO-10-1100';
156 if (utl_file.is_open( uFile_type)) then
157 utl_file.fclose ( uFile_type );
158 end if;
159
160 xProgress := 'SPSO-10-1110';
161 ec_debug.pl ( 0, 'EC', 'ECE_SPSO_COMPLETE ',NULL );
162
163 -- Assume everything went ok so delete the records from ece_output.
164
165 xProgress := 'SPSO-10-1120';
166 DELETE
167 FROM ece_output
168 WHERE run_id = p_run_id;
169
170 IF SQL%NOTFOUND
171 THEN
172 ec_debug.pl ( 0,
173 'EC',
174 'ECE_NO_ROW_PROCESSED',
175 'PROGRESS_LEVEL',
176 xProgress,
177 'TABLE_NAME',
178 'ECE_OUTPUT' );
179 END IF;
180
181 IF ec_mapping_utils.ec_get_trans_upgrade_status(p_transaction_type) = 'U' THEN
182 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
183 retcode := 1;
184 END IF;
185
186 ec_debug.pop ( 'ECE_SPSO_Trans1.Extract_SPSO_Outbound' );
187 ec_debug.disable_debug;
188 COMMIT;
189
190 EXCEPTION
191 WHEN ece_transaction_disabled THEN
192 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED','TRANSACTION',p_Transaction_type);
193 retcode := 1;
194 ec_debug.disable_debug;
195 ROLLBACK;
196
197 WHEN utl_file.write_error THEN
198
199 ec_debug.pl ( 0,
200 'EC',
201 'ECE_UTL_WRITE_ERROR',
202 NULL );
203
204 ec_debug.pl ( 0,
205 'EC',
206 'ECE_ERROR_MESSAGE',
207 'ERROR_MESSAGE',
208 SQLERRM );
209
210 retcode := 2;
211 ec_debug.disable_debug;
212 ROLLBACK;
213 RAISE;
214
215 WHEN utl_file.invalid_path THEN
216
217 ec_debug.pl ( 0,
218 'EC',
219 'ECE_UTIL_INVALID_PATH',
220 NULL );
221
222 ec_debug.pl ( 0,
223 'EC',
224 'ECE_ERROR_MESSAGE',
225 'ERROR_MESSAGE',
226 SQLERRM );
227
228 retcode := 2;
229 ec_debug.disable_debug;
230 ROLLBACK;
231 RAISE;
232
233 WHEN utl_file.invalid_operation THEN
234
235 ec_debug.pl ( 0,
236 'EC',
237 'ECE_UTIL_INVALID_OPERATION',
238 NULL );
239
240 ec_debug.pl ( 0,
241 'EC',
242 'ECE_ERROR_MESSAGE',
243 'ERROR_MESSAGE',
244 SQLERRM );
245
246 retcode := 2;
247 ec_debug.disable_debug;
248 ROLLBACK;
249 RAISE;
250
251 WHEN OTHERS THEN
252
253 ec_debug.pl ( 0,
254 'EC',
255 'ECE_PROGRAM_ERROR',
256 'PROGRESS_LEVEL',
257 xProgress );
258
259 ec_debug.pl ( 0,
260 'EC',
261 'ECE_ERROR_MESSAGE',
262 'ERROR_MESSAGE',
263 SQLERRM );
264
265 retcode := 2;
266 ec_debug.disable_debug;
267 ROLLBACK;
268 RAISE;
269
270 END Extract_SPSO_Outbound;
271
272
273 /*===========================================================================
274
275 PROCEDURE NAME: Extract_SSSO_Outbound
276
277 PURPOSE: This procedure initiates the concurrent process to
278 extract the eligible deliveires on a dparture.
279
280 ===========================================================================*/
281
282 PROCEDURE Extract_SSSO_Outbound ( errbuf OUT NOCOPY VARCHAR2,
283 retcode OUT NOCOPY VARCHAR2,
284 cOutput_Path IN VARCHAR2,
285 cOutput_Filename IN VARCHAR2,
286 p_schedule_id IN VARCHAR2,
287 v_debug_mode IN NUMBER default 0,
288 p_batch_id IN NUMBER default 0) -- Bug 2064311
289 IS
290
291 xBeforeFormat EXCEPTION;
292 xProgress VARCHAR2(80);
293 p_communication_method VARCHAR2(120) := 'EDI';
294 p_transaction_type VARCHAR2(120) := 'SSSO';
295 p_document_type VARCHAR2(120) := 'SSS';
296 l_line_text VARCHAR2(2000);
297 uFile_type utl_file.file_type;
298 p_output_width INTEGER := 4000;
299 p_run_id NUMBER ;
300 p_header_interface VARCHAR2(120) := 'ECE_SPSO_HEADERS';
301 p_item_interface VARCHAR2(120) := 'ECE_SPSO_ITEMS';
302 p_item_d_interface VARCHAR2(120) := 'ECE_SPSO_ITEM_DET';
303 p_transaction_date DATE := SYSDATE;
304 cEnabled VARCHAR2(1) := 'Y';
305 ece_transaction_disabled EXCEPTION;
306
307 CURSOR c_output IS
308 SELECT text
309 FROM ece_output
310 WHERE run_id = p_run_id
311 ORDER BY line_id;
312
313 BEGIN
314
315 ec_debug.enable_debug(v_debug_mode);
316 ec_debug.push ( 'ECE_SPSO_Trans1.Extract_SSSO_Outbound' );
317 ec_debug.pl ( 3, 'cOutput_Path: ',cOutput_Path );
318 ec_debug.pl ( 3, 'cOutput_Filename: ',cOutput_Filename );
319 ec_debug.pl ( 3, 'p_schedule_id: ',p_schedule_id );
320 ec_debug.pl ( 3, 'v_debug_mode: ',v_debug_mode );
321 ec_debug.pl ( 3, 'p_batch_id ',p_batch_id );
322
323 /* Check to see if the transaction is enabled. If not, abort */
324 xProgress := 'SSSO-10-1001';
325 fnd_profile.get('ECE_' || p_Transaction_Type || '_ENABLED',cEnabled);
326
327 xProgress := 'SSSO-10-1002';
328 IF cEnabled = 'N' THEN
329 xProgress := 'SSSO-10-1003';
330 RAISE ece_transaction_disabled;
331 END IF;
332
333 xProgress := 'SSSO-10-1005';
334 BEGIN
335 SELECT ece_output_runs_s.NEXTVAL
336 INTO p_run_id
337 FROM sys.dual;
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 ec_debug.pl ( 0,
341 'EC',
342 'ECE_GET_NEXT_SEQ_FAILED',
343 'PROGRESS_LEVEL',
344 xProgress,
345 'SEQ',
346 'ECE_OUTPUT_RUNS_S' );
347 END;
348 ec_debug.pl(3, 'p_run_id: ',p_run_id);
349
350 xProgress := 'SSSO-10-1010';
351 ec_debug.pl ( 0, 'EC', 'ECE_SSSO_START', NULL );
352
353 xProgress := 'SSSO-10-1020';
354 ec_debug.pl ( 0, 'EC', 'ECE_RUN_ID', 'RUN_ID', p_run_id );
355
356 xProgress := 'SSSO-10-1030';
357 ece_spso_trans1.populate_supplier_sched_api1 ( p_communication_method,
358 p_transaction_type,
359 p_transaction_date,
360 p_run_id,
361 p_document_type,
362 p_schedule_id,
363 p_batch_id,
364 p_header_interface,
365 p_item_interface,
366 p_item_d_interface );
367
368 xProgress := 'SSSO-10-1040';
369 ece_spso_trans2.populate_supplier_sched_api2 ( p_communication_method,
370 p_transaction_type,
371 p_document_type,
372 p_run_id,
373 p_schedule_id,
374 p_batch_id );
375
376 xProgress := 'SSSO-10-1050';
377 ece_spso_trans1.populate_supplier_sched_api3 ( p_communication_method,
378 p_transaction_type,
379 p_document_type,
380 p_run_id,
381 p_schedule_id,
382 p_batch_id );
383
384 xProgress := 'SSSO-10-1060';
385 ece_spso_trans1.put_data_to_output_table ( p_communication_method,
386 p_transaction_type,
387 p_output_width,
388 p_run_id,
389 p_header_interface,
390 p_item_interface,
391 p_item_d_interface );
392
393
394 xProgress := 'SSSO-10-1070';
395
396 -- Open the cursor to select the actual file output from ece_output.
397
398 xProgress := 'SSSO-10-1080';
399 OPEN c_output;
400 LOOP
401 FETCH c_output
402 INTO l_line_text;
403 if (c_output%ROWCOUNT > 0) then
404 if (NOT utl_file.is_open(uFile_type)) then
405 uFile_type := utl_file.fopen ( cOutput_Path,
406 cOutput_Filename,
407 'W' );
408 end if;
409 end if;
410 EXIT WHEN c_output%NOTFOUND;
411
412 -- Write the data from ece_output to the output file.
413
414 xProgress := 'SSSO-10-1090';
415 utl_file.put_line ( uFile_type,
416 l_line_text );
417 ec_debug.pl ( 3, 'l_line_text: ',l_line_text );
418
419 END LOOP;
420
421 CLOSE c_output;
422
423 -- Close the output file.
424
425 xProgress := 'SSSO-10-1100';
426 if (utl_file.is_open( uFile_type)) then
427 utl_file.fclose ( uFile_type );
428 end if;
429
430 xProgress := 'SSSO-10-1110';
431 ec_debug.pl ( 0, 'EC', 'ECE_SSSO_COMPLETE', NULL );
432
433 -- Assume everything went ok so delete the records from ece_output.
434
435 xProgress := 'SSSO-10-1120';
436 DELETE
437 FROM ece_output
438 WHERE run_id = p_run_id;
439
440 IF SQL%NOTFOUND
441 THEN
442 ec_debug.pl ( 0,
443 'EC',
444 'ECE_NO_ROW_PROCESSED',
445 'PROGRESS_LEVEL',
446 xProgress,
447 'TABLE_NAME',
448 'ECE_OUTPUT' );
449 END IF;
450
451
452 IF ec_mapping_utils.ec_get_trans_upgrade_status(p_transaction_type) = 'U' THEN
453 ec_debug.pl(0,'EC','ECE_REC_TRANS_PENDING',NULL);
454 retcode := 1;
455 END IF;
456
457 ec_debug.pop ( 'ECE_SPSO_Trans1.Extract_SSSO_Outbound' );
458 ec_debug.disable_debug;
459 COMMIT;
460
461 EXCEPTION
462 WHEN ece_transaction_disabled THEN
463 ec_debug.pl(0,'EC','ECE_TRANSACTION_DISABLED','TRANSACTION',p_Transaction_type);
464 retcode := 1;
465 ec_debug.disable_debug;
466 ROLLBACK;
467
468 WHEN utl_file.write_error THEN
469
470 ec_debug.pl ( 0,
471 'EC',
472 'ECE_UTL_WRITE_ERROR',
473 NULL );
474
475
476 ec_debug.pl ( 0,
477 'EC',
478 'ECE_ERROR_MESSAGE',
479 'ERROR_MESSAGE',
480 SQLERRM );
481
482 retcode := 2;
483 ec_debug.disable_debug;
484 ROLLBACK;
485 RAISE;
486
487 WHEN utl_file.invalid_path THEN
488
489 ec_debug.pl ( 0,
490 'EC',
491 'ECE_UTIL_INVALID_PATH',
492 NULL );
493
494 ec_debug.pl ( 0,
495 'EC',
496 'ECE_ERROR_MESSAGE',
497 'ERROR_MESSAGE',
498 SQLERRM );
499
500 retcode := 2;
501 ec_debug.disable_debug;
502 ROLLBACK;
503 RAISE;
504
505 WHEN utl_file.invalid_operation THEN
506
507 ec_debug.pl ( 0,
508 'EC', '
509 ECE_UTIL_INVALID_OPERATION',
510 NULL );
511
512 ec_debug.pl ( 0,
513 'EC',
514 'ECE_ERROR_MESSAGE',
515 'ERROR_MESSAGE',
516 SQLERRM );
517
518 retcode := 2;
519 ec_debug.disable_debug;
520 ROLLBACK;
521 RAISE;
522
523 WHEN others then
524
525 ec_debug.pl ( 0,
526 'EC',
527 'ECE_PROGRAM_ERROR',
528 'PROGRESS_LEVEL',
529 xProgress );
530
531 ec_debug.pl ( 0,
532 'EC',
533 'ECE_ERROR_MESSAGE',
534 'ERROR_MESSAGE',
535 SQLERRM );
536
537 retcode := 2;
538 ec_debug.disable_debug;
539 ROLLBACK;
540 RAISE;
541
542 END Extract_SSSO_Outbound;
543
544
545 /* --------------------------------------------------------------------------*/
546
547 -- PROCEDURE Populate_Supplier_Sched_API1
548 -- This procedure has the following functionalities:
549 -- 1. Build SQL statement dynamically to extract data from
550 -- Base Application Tables.
551 -- 2. Execute the dynamic SQL statement.
552 -- 3. Assign data into 2-dim PL/SQL table
553 -- 4. Pass data to the code conversion mechanism
554 -- 5. Populate the Interface tables with the extracted data.
555 -- --------------------------------------------------------------------------
556
557 PROCEDURE Populate_Supplier_Sched_API1 ( cCommunication_Method IN VARCHAR2,
558 cTransaction_Type IN VARCHAR2,
559 dTransaction_date IN DATE,
560 iRun_id IN INTEGER,
561 p_document_type IN VARCHAR2 DEFAULT 'SPS',
562 p_schedule_id IN INTEGER DEFAULT 0,
563 p_batch_id IN NUMBER DEFAULT 0,
564 cHeader_Interface IN VARCHAR2,
565 cItem_Interface IN VARCHAR2,
566 cItem_D_Interface IN VARCHAR2 )
567 IS
568
569 /*
570 **
571 ** Variable definitions. 'Interface_tbl_type' is a PL/SQL table typedef
572 ** with the following structure:
573 **
574 ** base_table_name VARCHAR2(50)
575 ** base_column_name VARCHAR2(50)
576 ** interface_table_name VARCHAR2(50)
577 ** interface_column_name VARCHAR2(50)
578 ** Record_num NUMBER
579 ** Position NUMBER
580 ** data_type VARCHAR2(50)
581 ** data_length NUMBER
582 ** value VARCHAR2(400)
583 ** layout_code VARCHAR2(2)
584 ** record_qualifier VARCHAR2(3)
585 ** interface_column_id NUMBER
586 ** conversion_seq NUMBER
587 ** xref_category_id NUMBER
588 ** conversion_group_id NUMBER
589 ** xref_key1_source_column VARCHAR2(50)
590 ** xref_key2_source_column VARCHAR2(50)
591 ** xref_key3_source_column VARCHAR2(50)
592 ** xref_key4_source_column VARCHAR2(50)
593 ** xref_key5_source_column VARCHAR2(50)
594 ** ext_val1 VARCHAR2(80)
595 ** ext_val2 VARCHAR2(80)
596 ** ext_val3 VARCHAR2(80)
597 ** ext_val4 VARCHAR2(80)
598 ** ext_val5 VARCHAR2(80)
599 **
600 */
601
602 xProgress VARCHAR2(30);
603 v_LevelProcessed VARCHAR2(40);
604 cOutput_path VARCHAR2(120);
605
606 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
607 l_item_tbl ece_flatfile_pvt.Interface_tbl_type;
608 l_key_tbl ece_flatfile_pvt.Interface_tbl_type;
609
610 Header_sel_c INTEGER;
611 Item_sel_c INTEGER;
612 Item_D_sel_c INTEGER;
613
614 cHeader_view VARCHAR2(50);
615 cItem_view VARCHAR2(50);
616
617 cHeader_select VARCHAR2(32000);
618 cItem_select VARCHAR2(32000);
619 cItem_D_select VARCHAR2(32000);
620
621 cHeader_from VARCHAR2(32000);
622 cItem_from VARCHAR2(32000);
623 cItem_D_from VARCHAR2(32000);
624
625 cHeader_where VARCHAR2(32000);
626 cItem_where VARCHAR2(32000);
627 cItem_D_where VARCHAR2(32000);
628
629 iHeader_count NUMBER := 0;
630 iItem_count NUMBER := 0;
631 iItem_D_count NUMBER := 0;
632 iKey_count NUMBER := 0;
633
634 l_header_fkey NUMBER;
635 l_item_fkey NUMBER;
636 l_Item_D_fkey NUMBER;
637
638 nHeader_key_pos NUMBER;
639 nItem_key_pos NUMBER;
640 nItem_D_key_pos NUMBER;
641
642 dummy INTEGER;
643 nPos1 NUMBER;
644 nPos2 NUMBER;
645 nPos3 NUMBER;
646 nPos4 NUMBER;
647 nPos5 NUMBER;
648 nPos6 NUMBER;
649 nPos7 NUMBER;
650 nPos8 NUMBER;
651 nPos9 NUMBER;
652 nTrans_id NUMBER;
653
654 n_trx_date_pos NUMBER;
655 n_vendor_id_pos NUMBER;
656 n_vendor_site_id_pos NUMBER;
657 n_organization_id_pos NUMBER;
658 n_item_id_pos NUMBER;
659
660 n_schedule_type_pos NUMBER;
661 n_schedule_id_pos NUMBER;
662 n_st_org_code_pos NUMBER;
663 n_cum_period_pos NUMBER;
664 n_enable_cum_flag_pos NUMBER;
665 n_st_name_pos NUMBER;
666 n_item_st_org_pos NUMBER;
667 n_st_add_1_pos NUMBER;
668 n_st_add_2_pos NUMBER;
669 n_st_add_3_pos NUMBER;
670 n_st_city_pos NUMBER;
671 n_st_county_pos NUMBER;
672 n_st_state_pos NUMBER;
673 n_st_country_pos NUMBER;
674 n_st_postal_pos NUMBER;
675 x_schedule_order NUMBER;
676 x_item_detail NUMBER;
677 exclude_zero_schedule_from_ff VARCHAR2(1) := 'N'; --bug 2944455
678
679 l_init_msg_list VARCHAR2(20);
680 l_simulate VARCHAR2(20);
681 l_validation_level VARCHAR2(20);
682 l_commit VARCHAR2(20);
683 l_return_status VARCHAR2(20);
684 l_msg_count VARCHAR2(20);
685 l_msg_data VARCHAR2(20);
686
687 fail_convert_to_ext EXCEPTION;
688
689
690 -- ***************************************
691 -- These variables are for the item loop
692 -- ***************************************
693
694 x_item_detail_sequence NUMBER :=0;
695
696 x_asl_id NUMBER;
697 x_enable_authorizations_flag VARCHAR2(1);
698 x_scheduler_id NUMBER;
699 x_asl_attribute_category VARCHAR2(30);
700 x_asl_attribute1 VARCHAR2(150);
701 x_asl_attribute2 VARCHAR2(150);
702 x_asl_attribute3 VARCHAR2(150);
703 x_asl_attribute4 VARCHAR2(150);
704 x_asl_attribute5 VARCHAR2(150);
705 x_asl_attribute6 VARCHAR2(150);
706 x_asl_attribute7 VARCHAR2(150);
707 x_asl_attribute8 VARCHAR2(150);
708 x_asl_attribute9 VARCHAR2(150);
709 x_asl_attribute10 VARCHAR2(150);
710 x_asl_attribute11 VARCHAR2(150);
711 x_asl_attribute12 VARCHAR2(150);
712 x_asl_attribute13 VARCHAR2(150);
713 x_asl_attribute14 VARCHAR2(150);
714 x_asl_attribute15 VARCHAR2(150);
715
716 x_supplier_product_num VARCHAR2(25);
717
718 x_scheduler_first_name VARCHAR2(150); --2507403 UTF8
719 x_scheduler_last_name VARCHAR2(150); --2507403 UTF8
720 x_scheduler_work_telephone VARCHAR2(60);
721
722 x_planner_first_name VARCHAR2(150); --2507403 UTF8
723 x_planner_last_name VARCHAR2(150); --2507403 UTF8
724 x_planner_work_telephone VARCHAR2(60);
725
726 d_dummy_date DATE;
727 g_item_id NUMBER;
728 BEGIN
729
730 ec_debug.push ( 'ece_spso_trans1.Populate_Supplier_Sched_API1' );
731 ec_debug.pl ( 3, 'cCommunication_Method: ', cCommunication_Method );
732 ec_debug.pl ( 3, 'cTransaction_Type: ',cTransaction_Type );
733 ec_debug.pl ( 3, 'dTransaction_date: ',dTransaction_date );
734 ec_debug.pl ( 3, 'iRun_id: ',iRun_id );
735 ec_debug.pl ( 3, 'p_document_type: ',p_document_type );
736 ec_debug.pl ( 3, 'p_schedule_id: ',p_schedule_id );
737 ec_debug.pl ( 3, 'cHeader_Interface: ',cHeader_Interface );
738 ec_debug.pl ( 3, 'cItem_Interface: ',cItem_Interface );
739 ec_debug.pl ( 3, 'cItem_D_Interface: ',cItem_D_Interface );
740
741 -- Retreive the system profile option ECE_OUT_FILE_PATH. This will
742 -- be the directory where the output file will be written.
743 -- NOTE: THIS DIRECTORY MUST BE SPECIFIED IN THE PARAMETER utl_file_dir IN
744 -- THE INIT.ORA FILE. Refer to the Oracle7 documentation for more information
745 -- on the package UTL_FILE.
746
747 xProgress := 'SPSOB-10-0100';
748 fnd_profile.get ( 'ECE_OUT_FILE_PATH',
749 cOutput_path );
750 ec_debug.pl ( 3, 'cOutput_path: ',cOutput_path );
751
752
753 xProgress := 'SPSOB-10-1000';
754 ece_flatfile_pvt.INIT_TABLE ( cTransaction_Type,
755 cHeader_Interface,
756 NULL,
757 FALSE,
758 l_header_tbl,
759 l_key_tbl );
760
761 xProgress := 'SPSOB-10-1020';
762 l_key_tbl := l_header_tbl;
763
764 xProgress := 'SPSOB-10-1030';
765 ece_flatfile_pvt.INIT_TABLE ( cTransaction_Type,
766 cItem_Interface,
767 NULL,
768 TRUE,
769 l_item_tbl,
770 l_key_tbl );
771
772
773 -- ***************************************************************************
774 --
775 -- Here, I am building the SELECT, FROM, and WHERE clauses for the dynamic
776 -- SQL call
777 -- The ece_extract_utils_pub.select_clause uses the EDI data dictionary for the build.
778 --
779 -- **************************************************************************
780
781 xProgress := 'SPSOB-10-1040';
782 ece_extract_utils_pub.select_clause ( cTransaction_Type,
783 cCommunication_Method,
784 cHeader_Interface,
785 l_header_tbl,
786 cHeader_select,
787 cHeader_from,
788 cHeader_where );
789
790 xProgress := 'SPSOB-10-1050';
791 ece_extract_utils_pub.select_clause ( cTransaction_Type,
792 cCommunication_Method,
793 cItem_Interface,
794 l_item_tbl,
795 cItem_select,
796 cItem_from,
797 cItem_where );
798
799 -- **************************************************************************
800 -- Here, I am customizing the WHERE clause to join the Interface tables together.
801 -- i.e. Headers -- Items -- Item Details
802 --
803 -- Select Data1, Data2, Data3...........
804 -- From Header_View
805 -- Where A.Transaction_Record_ID = D.Transaction_Record_ID (+)
806 -- and B.Transaction_Record_ID = E.Transaction_Record_ID (+)
807 -- and C.Transaction_Record_ID = F.Transaction_Record_ID (+)
808 -- ******* (Customization should be added here) ********
809 -- and A.Communication_Method = 'EDI'
810 -- and A.xxx = B.xxx ........
811 -- and B.yyy = C.yyy .......
812 -- **************************************************************************
813 -- **************************************************************************
814 -- :po_header_id is a place holder for foreign key value.
815 -- A PL/SQL table (list of values) will be used to store data.
816 -- Procedure ece_flatfile_pvt.Find_pos will be used to locate the specific
817 -- data value in the PL/SQL table.
818 -- dbms_sql (Native Oracle db functions that come with every Oracle Apps)
819 -- dbms_sql.bind_variable will be used to assign data value to :transaction_id.
820 --
821 -- Let's use the above example:
822 --
823 -- 1. Execute dynamic SQL 1 for headers (A) data
824 -- Get value of A.xxx (foreign key to B)
825 --
826 -- 2. bind value A.xxx to variable B.xxx
827 --
828 -- 3. Execute dynamic SQL 2 for items (B) data
829 -- Get value of B.yyy (foreigh key to C)
830 --
831 -- 4. bind value B.yyy to variable C.yyy
832 --
833 -- 5. Execute dynamic SQL 3 for line_details (C) data
834 -- **************************************************************************
835 -- **************************************************************************
836 -- Change the following few lines as needed
837 -- **************************************************************************
838
839 xProgress := 'SPSOB-10-1060';
840 IF cTransaction_Type = 'SPSO'
841 THEN
842
843 cHeader_view := 'ECE_SPSO_HEADERS_V';
844 cItem_view := 'ECE_SPSO_ITEMS_V';
845
846 ELSIF cTransaction_Type = 'SSSO'
847 THEN
848
849 cHeader_view := 'ECE_SSSO_HEADERS_V';
850 cItem_view := 'ECE_SSSO_ITEMS_V';
851
852 END IF;
853
854 ec_debug.pl ( 3, 'cHeader_view: ',cHeader_view );
855 ec_debug.pl ( 3, 'cItem_view: ',cItem_view );
856
857 -- *****************************
858 -- if user passed in a 0 (zero)
859 -- select everything
860 -- *****************************
861 /* Bug 2064311
862 Appended batch_id to the where condition of header view
863 to improve performance . Batch id is appended when this transaction
864 is launched thru supplier scheduling.
865
866 batch id is defaulted as zero when this transaction is launched
867 thru EDI
868 */
869
870 xProgress := 'SPSOB-10-1070';
871
872 if p_batch_id = 0 then
873 cHeader_where := cHeader_where ||
874 cHeader_view ||
875 '.COMMUNICATION_METHOD IN (''BOTH'',' ||
876 ':l_cCommunication_Method' ||
877 ')' ||
878 ' AND ((' ||
879 cHeader_view ||
880 '.SCHEDULE_ID = :l_p_schedule_id' ||
881 ' AND ' ||
882 ':l_p_schedule_id' ||
883 '<> 0)' ||
884 ' OR ' ||
885 ':l_p_schedule_id' ||
886 ' = 0)';
887
888 else
889
890 cHeader_where := cHeader_where ||
891 cHeader_view ||'.COMMUNICATION_METHOD in (''BOTH'','||
892 ':l_cCommunication_Method'|| ')' ||
893 ' AND (('|| cHeader_view ||'.SCHEDULE_ID = '||':l_p_schedule_id' ||
894 ' and '|| p_schedule_id || '<> 0)' ||
895 ' OR ' || ':l_p_schedule_id' || ' = 0)'|| 'AND ' || cHeader_view||'.BATCH_ID='||':l_p_batch_id';
896
897 end if;
898
899 ec_debug.pl ( 3, 'cHeader_where: ',cHeader_where );
900
901 xProgress := 'SPSOB-10-1080';
902 cItem_where := cItem_where ||
903 cItem_view ||
904 '.SCHEDULE_ID = :schedule_id';
905
906 ec_debug.pl ( 3, 'cItem_where: ',cItem_where );
907
908 xProgress := 'SPSOB-10-1090';
909 cHeader_select := cHeader_select ||
910 cHeader_from ||
911 cHeader_where;
912
913 ec_debug.pl ( 3, 'cHeader_select: ',cHeader_select );
914
915 xProgress := 'SPSOB-10-1100';
916 cItem_select := cItem_select ||
917 cItem_from ||
918 cItem_where;
919
920 ec_debug.pl ( 3, 'cItem_select: ',cItem_select );
921
922 -- ***************************************************
923 -- ***
924 -- *** Get data setup for the dynamic SQL call.
925 -- ***
926 -- *** Open a cursor for each of the SELECT call
927 -- *** This tells the database to reserve spaces
928 -- *** for the data returned by the SQL statement
929 -- ***
930 -- ***************************************************
931
932 xProgress := 'SPSOB-10-1110';
933 Header_sel_c := dbms_sql.open_cursor;
934
935 xProgress := 'SPSOB-10-1120';
936 Item_sel_c := dbms_sql.open_cursor;
937
938 -- ***************************************************
939 --
940 -- Parse each of the SELECT statement
941 -- so the database understands the command
942 --
943 -- ***************************************************
944
945 xProgress := 'SPSOB-10-1130';
946 BEGIN
947 dbms_sql.parse ( Header_sel_c,
948 cHeader_select,
949 dbms_sql.native );
950 EXCEPTION
951 WHEN OTHERS THEN
952 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
953 cHeader_select );
954 app_exception.raise_exception;
955 END;
956
957 xProgress := 'SPSOB-10-1140';
958 BEGIN
959 dbms_sql.parse ( Item_sel_c,
960 cItem_select,
961 dbms_sql.native );
962 EXCEPTION
963 WHEN OTHERS THEN
964 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
965 cItem_select );
966 app_exception.raise_exception;
967 END;
968
969 -- *************************************************
970 -- set counter
971 -- *************************************************
972
973 xProgress := 'SPSOB-10-1150';
974 iHeader_count := l_header_tbl.count;
975
976 xProgress := 'SPSOB-10-1160';
977 iItem_count := l_item_tbl.count;
978
979
980 -- ***************************************************
981 --
982 -- Define TYPE for every columns in the SELECT statement
983 -- For each piece of the data returns, we need to tell
984 -- the database what type of information it will be.
985 --
986 -- e.g. ID is NUMBER, due_date is DATE
987 -- However, for simplicity, we will convert
988 -- everything to varchar2.
989 --
990 -- ***************************************************
991
992 xProgress := 'SPSOB-10-1170';
993 FOR k IN 1..iHeader_count
994 LOOP
995 dbms_sql.define_column ( Header_sel_c,
996 k,
997 cHeader_select,
998 ece_extract_utils_PUB.G_MaxColWidth );
999 END LOOP;
1000
1001
1002 xProgress := 'SPSOB-10-1180';
1003 FOR k IN 1..iItem_count
1004 LOOP
1005 dbms_sql.define_column ( Item_sel_c,
1006 k,
1007 cItem_select,
1008 ece_extract_utils_PUB.G_MaxColWidth );
1009 END LOOP;
1010
1011 -- **************************************************************
1012 -- *** The following is custom tailored for this transaction
1013 -- *** It find the values and use them in the WHERE clause to
1014 -- *** join tables together.
1015 -- **************************************************************
1016
1017 -- ***************************************************
1018 -- To complete the Item SELECT statement,
1019 -- we will need values for the join condition.
1020 --
1021 -- ***************************************************
1022
1023 xProgress := 'SPSOB-10-1190';
1024 n_trx_date_pos := ece_extract_utils_pub.POS_OF ( l_header_tbl,
1025 ece_extract_utils_pub.G_Transaction_date );
1026 ec_debug.pl ( 3, 'n_trx_date_pos: ',n_trx_date_pos );
1027
1028 xProgress := 'SPSOB-10-1200';
1029 n_vendor_id_pos := ece_extract_utils_pub.POS_OF ( l_header_tbl,
1030 'VENDOR_ID' );
1031 ec_debug.pl ( 3, 'n_vendor_id_pos: ',n_vendor_id_pos );
1032
1033 xProgress := 'SPSOB-10-1210';
1034 n_vendor_site_id_pos := ece_extract_utils_pub.POS_OF ( l_header_tbl,
1035 'VENDOR_SITE_ID' );
1036 ec_debug.pl ( 3, 'n_vendor_site_id_pos: ',n_vendor_site_id_pos );
1037
1038 xProgress := 'SPSOB-10-1220';
1039 n_organization_id_pos := ece_extract_utils_pub.POS_OF ( l_header_tbl,
1040 'ORGANIZATION_ID' );
1041 ec_debug.pl ( 3, 'n_organization_id_pos: ',n_organization_id_pos );
1042
1043 xProgress := 'SPSOB-10-1230';
1044 n_st_org_code_pos := ece_extract_utils_pub.POS_OF ( l_header_tbl,
1045 'ST_ORG_CODE' );
1046 ec_debug.pl ( 3, 'n_st_org_code_pos: ',n_st_org_code_pos );
1047
1048 xProgress := 'SPSOB-10-1240';
1049 n_schedule_type_pos := ece_extract_utils_pub.POS_OF ( l_header_tbl,
1050 'SCHEDULE_TYPE' );
1051 ec_debug.pl ( 3, 'n_schedule_type_pos: ',n_schedule_type_pos );
1052
1053 xProgress := 'SPSOB-10-1250';
1054 n_st_name_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1055 'ST_NAME' );
1056 ec_debug.pl ( 3, 'n_st_name_pos: ',n_st_name_pos );
1057
1058 ece_extract_utils_pub.Find_pos ( l_header_tbl,
1059 'SCHEDULE_ID',
1060 n_schedule_id_pos );
1061 ec_debug.pl ( 3, 'n_schedule_id_pos: ',n_schedule_id_pos );
1062
1063 xProgress := 'SPSOB-10-1260';
1064 n_item_id_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1065 'SCHEDULE_ITEM_ID' );
1066 ec_debug.pl ( 3, 'n_item_id_pos: ',n_item_id_pos );
1067
1068 xProgress := 'SPSOB-10-1270';
1069 n_enable_cum_flag_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1070 'ENABLE_CUM_FLAG' );
1071 ec_debug.pl ( 3, 'n_enable_cum_flag_pos: ',n_enable_cum_flag_pos );
1072
1073 xProgress := 'SPSOB-10-1280';
1074 n_cum_period_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1075 'CUM_PERIOD_START_DATE' );
1076 ec_debug.pl ( 3, 'n_cum_period_pos: ',n_cum_period_pos );
1077
1078 xProgress := 'SPSOB-10-1290';
1079 n_item_st_org_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1080 'ST_ORG_CODE' );
1081 ec_debug.pl ( 3, 'n_item_st_org_pos: ',n_item_st_org_pos );
1082
1083 xProgress := 'SPSOB-10-1300';
1084 n_st_add_1_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1085 'ST_ADDRESS_LINE1' );
1086 ec_debug.pl ( 3, 'n_st_add_1_pos: ',n_st_add_1_pos );
1087
1088 xProgress := 'SPSOB-10-1310';
1089 n_st_add_2_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1090 'ST_ADDRESS_LINE2' );
1091 ec_debug.pl ( 3, 'n_st_add_2_pos: ',n_st_add_2_pos );
1092
1093 xProgress := 'SPSOB-10-1320';
1094 n_st_add_3_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1095 'ST_ADDRESS_LINE3' );
1096 ec_debug.pl ( 3, 'n_st_add_3_pos: ',n_st_add_3_pos );
1097
1098 xProgress := 'SPSOB-10-1330';
1099 n_st_city_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1100 'ST_CITY' );
1101 ec_debug.pl ( 3, 'n_st_city_pos: ',n_st_city_pos );
1102
1103 xProgress := 'SPSOB-10-1340';
1104 n_st_county_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1105 'ST_COUNTY' );
1106 ec_debug.pl ( 3, 'n_st_county_pos: ',n_st_county_pos );
1107
1108 xProgress := 'SPSOB-10-1350';
1109 n_st_state_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1110 'ST_STATE' );
1111 ec_debug.pl ( 3, 'n_st_state_pos: ',n_st_state_pos );
1112
1113 xProgress := 'SPSOB-10-1360';
1114 n_st_country_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1115 'ST_COUNTRY' );
1116 ec_debug.pl ( 3, 'n_st_country_pos: ',n_st_country_pos );
1117
1118 xProgress := 'SPSOB-10-1370';
1119 n_st_postal_pos := ece_extract_utils_pub.POS_OF ( l_item_tbl,
1120 'ST_POSTAL_CODE' );
1121 ec_debug.pl ( 3, 'n_st_postal_pos: ',n_st_postal_pos );
1122
1123 xProgress := 'SPSOB-10-1371';
1124 dbms_sql.bind_variable(Header_sel_c,'l_cCommunication_Method',cCommunication_Method);
1125
1126 xProgress := 'SPSOB-10-1372';
1127 dbms_sql.bind_variable(Header_sel_c,'l_p_schedule_id',p_schedule_id);
1128
1129 xProgress := 'SPSOB-10-1373';
1130 if (p_batch_id <>0) then
1131 dbms_sql.bind_variable(Header_sel_c,'l_p_batch_id',p_batch_id);
1132 end if;
1133 -- EXECUTE the SELECT statement
1134
1135 xProgress := 'SPSOB-10-1380';
1136 dummy := dbms_sql.execute ( Header_sel_c );
1137
1138 -- ***************************************************
1139 --
1140 -- The model is:
1141 -- HEADER - ITEM - ITEM_D ...
1142 --
1143 -- With data for each HEADER line, populate the header interface
1144 -- table then get all ITEMS that belongs
1145 -- to the HEADER. Then get all
1146 -- ITEM_DS that belongs to the ITEM.
1147 --
1148 -- ***************************************************
1149
1150
1151 xProgress := 'SPSOB-10-1390';
1152 WHILE dbms_sql.fetch_rows ( Header_sel_c ) > 0
1153 LOOP -- Header
1154
1155 -- ***************************************************
1156 --
1157 -- store internal values in pl/sql table
1158 --
1159 -- ***************************************************
1160
1161 xProgress := 'SPSOB-10-1400';
1162 FOR i IN 1..iHeader_count
1163 LOOP
1164 dbms_sql.column_value ( Header_sel_c,
1165 i,
1166 l_header_tbl(i).value );
1167
1168 dbms_sql.column_value ( Header_sel_c,
1169 i,
1170 l_key_tbl(i).value );
1171 END LOOP;
1172
1173 -- ***************************************************
1174 --
1175 -- also need to populate transaction_date and run_id
1176 --
1177 -- ***************************************************
1178
1179 xProgress := 'SPSOB-10-1410';
1180 l_header_tbl(n_trx_date_pos).value := TO_CHAR(dTransaction_date,'YYYYMMDD HH24MISS');
1181 ec_debug.pl ( 3, 'l_header_tbl(n_trx_date_pos).value: ',l_header_tbl(n_trx_date_pos).value );
1182
1183 -- pass the pl/sql table in for xref
1184
1185 xProgress := 'SPSOB-10-1420';
1186 ec_code_Conversion_pvt.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
1187 p_init_msg_list => l_init_msg_list,
1188 p_simulate => l_simulate,
1189 p_commit => l_commit,
1190 p_validation_level => l_validation_level,
1191 p_return_status => l_return_status,
1192 p_msg_count => l_msg_count,
1193 p_msg_data => l_msg_data,
1194 p_key_tbl => l_key_tbl,
1195 p_tbl => l_header_tbl );
1196
1197 xProgress := 'SPSOB-10-1430';
1198 IF l_return_status = FND_API.G_RET_STS_ERROR
1199 OR l_return_status is NULL
1200 OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1201 THEN
1202 RAISE fail_convert_to_ext;
1203 END IF;
1204
1205 xProgress := 'SPSOB-10-1431';
1206 begin
1207 select count(*) into
1208 x_schedule_order from
1209 chv_item_orders where
1210 schedule_id = l_header_tbl(n_schedule_id_pos).value;
1211 exception
1212 when others then
1213 null;
1214 end;
1215 -- 2944455
1216 fnd_profile.get('ECE_SPSO_EXCLUDE_ZERO_SCHEDULE_FROM_FF',exclude_zero_schedule_from_ff);
1217 If NVL(exclude_zero_schedule_from_ff,'N')<>'Y' then
1218 exclude_zero_schedule_from_ff := 'N';
1219 End If;
1220 -- ******************************************
1221 --
1222 -- insert into interface table
1223 --
1224 -- ******************************************
1225 if ((exclude_zero_schedule_from_ff = 'N') OR
1226 (x_schedule_order > 0)) Then -- 2944455
1227 xProgress := 'SPSOB-10-1440';
1228 BEGIN
1229 SELECT ece_spso_headers_s.nextval
1230 INTO l_header_fkey
1231 FROM sys.dual;
1232 EXCEPTION
1233 WHEN NO_DATA_FOUND THEN
1234 ec_debug.pl ( 0,
1235 'EC',
1236 'ECE_GET_NEXT_SEQ_FAILED',
1237 'PROGRESS_LEVEL',
1238 xProgress,
1239 'SEQ',
1240 'ECE_SPSO_HEADERS_S' );
1241 END;
1242
1243
1244 xProgress := 'SPSOB-10-1450';
1245 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id => iRun_id,
1246 cTransaction_Type => cTransaction_Type,
1247 cCommunication_Method => cCommunication_Method,
1248 cInterface_Table => cHeader_Interface,
1249 p_source_tbl => l_header_tbl,
1250 p_foreign_key => l_header_fkey );
1251
1252 -- Now update the columns values of which have been obtained thru the procedure
1253 -- calls.
1254
1255 -- ******************************************
1256 --
1257 -- Call custom program stub to populate the extension table
1258 --
1259 -- ******************************************
1260
1261 xProgress := 'SPSOB-10-1460';
1262 ece_spso_x.populate_extension_headers ( l_header_fkey,
1263 l_header_tbl );
1264
1265 -- ***************************************************
1266 --
1267 -- From Header data, we can assign values to
1268 -- place holders (foreign keys) in Item_select and
1269 -- Item_detail_Select
1270 --
1271 -- ***************************************************
1272 -- set values into binding variables
1273 --
1274 -- ***************************************************
1275
1276 -- use the following bind_variable feature as you see fit.
1277
1278 dbms_sql.bind_variable ( Item_sel_c,
1279 'schedule_id',
1280 l_header_tbl(n_schedule_id_pos).value );
1281
1282 xProgress := 'SPSOB-10-1470';
1283 dummy := dbms_sql.execute ( Item_sel_c );
1284
1285 -- ***************************************************
1286 --
1287 -- item loop starts here
1288 --
1289 -- ***************************************************
1290
1291 xProgress := 'SPSOB-10-1480';
1292 WHILE dbms_sql.fetch_rows ( Item_sel_c ) > 0
1293 LOOP --- Item
1294
1295 -- ***************************************************
1296 --
1297 -- store values in pl/sql table
1298 --
1299 -- ***************************************************
1300
1301
1302 xProgress := 'SPSOB-10-1490';
1303 FOR j IN 1..iItem_count
1304 LOOP
1305 dbms_sql.column_value ( Item_sel_c,
1306 j,
1307 l_item_tbl(j).value );
1308
1309 dbms_sql.column_value ( Item_sel_c,
1310 j,
1311 l_key_tbl(j+iHeader_count).value );
1312 END LOOP;
1313
1314 xProgress := 'SPSOB-10-1500';
1315 /* Bug 1705597.
1316 Get item_id for the corresponding schedule_item_id
1317 from the view ece_spso_items_v and use this value
1318 in the following query to get asl_id and other data
1319 */
1320
1321 begin
1322
1323 select item_id into g_item_id from chv_schedule_items where
1324 schedule_item_id = l_item_tbl(n_item_id_pos).value;
1325
1326 exception
1327 when no_data_found then null;
1328 when others then null;
1329 end;
1330
1331
1332
1333 BEGIN
1334 SELECT
1335 paa.asl_id,
1336 paa.enable_authorizations_flag,
1337 paa.scheduler_id,
1338 ppf.first_name,
1339 ppf.last_name,
1340 ppf.work_telephone,
1341 paa.attribute_category,
1342 paa.attribute1,
1343 paa.attribute2,
1344 paa.attribute3,
1345 paa.attribute4,
1346 paa.attribute5,
1347 paa.attribute6,
1348 paa.attribute7,
1349 paa.attribute8,
1350 paa.attribute9,
1351 paa.attribute10,
1352 paa.attribute11,
1353 paa.attribute12,
1354 paa.attribute13,
1355 paa.attribute14,
1356 paa.attribute15
1357 INTO
1358 x_asl_id,
1359 x_enable_authorizations_flag,
1360 x_scheduler_id,
1361 x_scheduler_first_name,
1362 x_scheduler_last_name,
1363 x_scheduler_work_telephone,
1364 x_asl_attribute_category,
1365 x_asl_attribute1,
1366 x_asl_attribute2,
1367 x_asl_attribute3,
1368 x_asl_attribute4,
1369 x_asl_attribute5,
1370 x_asl_attribute6,
1371 x_asl_attribute7,
1372 x_asl_attribute8,
1373 x_asl_attribute9,
1374 x_asl_attribute10,
1375 x_asl_attribute11,
1376 x_asl_attribute12,
1377 x_asl_attribute13,
1378 x_asl_attribute14,
1379 x_asl_attribute15
1380 FROM
1381 po_asl_attributes paa,
1382 per_all_people_f ppf
1383 WHERE
1384 paa.vendor_id = l_header_tbl(n_vendor_id_pos).value
1385 AND paa.vendor_site_id = l_header_tbl(n_vendor_site_id_pos).value
1386 AND paa.item_id = g_item_id --Bug 1705597
1387 AND paa.using_organization_id = chv_inq_sv.get_asl_org(
1388 l_header_tbl(n_organization_id_pos).value,
1389 l_header_tbl(n_vendor_id_pos).value,
1390 l_header_tbl(n_vendor_site_id_pos).value,
1391 g_item_id) -- Bug 1705597
1392 AND scheduler_id = ppf.person_id(+)
1393 AND ppf.effective_start_date (+) >= trunc(SYSDATE)
1394 AND ppf.effective_end_date (+) <= trunc(SYSDATE)
1395 AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',
1396 ppf.person_id (+),
1397 ppf.person_type_id (+),
1398 ppf.employee_number (+),
1399 ppf.applicant_number (+) ))= 'TRUE';
1400 EXCEPTION
1401 WHEN NO_DATA_FOUND THEN
1402 ec_debug.pl ( 1,
1403 'EC',
1404 'ECE_NO_ROW_SELECTED',
1405 'PROGRESS_LEVEL',
1406 xProgress,
1407 'INFO',
1408 'ASL_ID',
1409 'TABLE_NAME',
1410 'PO_ASL_ATTRIBUTES' );
1411 END;
1412
1413 ec_debug.pl ( 3, 'x_asl_id: ',x_asl_id );
1414 ec_debug.pl ( 3, 'x_enable_authorizations_flag: ',x_enable_authorizations_flag );
1415 ec_debug.pl ( 3, 'x_scheduler_id: ',x_scheduler_id );
1416 ec_debug.pl ( 3, 'x_scheduler_first_name: ',x_scheduler_first_name );
1417 ec_debug.pl ( 3, 'x_scheduler_last_name: ',x_scheduler_last_name );
1418 ec_debug.pl ( 3, 'x_scheduler_work_telephone: ',x_scheduler_work_telephone );
1419 ec_debug.pl ( 3, 'x_asl_attribute_category: ',x_asl_attribute_category );
1420 ec_debug.pl ( 3, 'x_asl_attribute1: ',x_asl_attribute1 );
1421 ec_debug.pl ( 3, 'x_asl_attribute2: ',x_asl_attribute2 );
1422 ec_debug.pl ( 3, 'x_asl_attribute3: ',x_asl_attribute3 );
1423 ec_debug.pl ( 3, 'x_asl_attribute4: ',x_asl_attribute4 );
1424 ec_debug.pl ( 3, 'x_asl_attribute5: ',x_asl_attribute5 );
1425 ec_debug.pl ( 3, 'x_asl_attribute6: ',x_asl_attribute6 );
1426 ec_debug.pl ( 3, 'x_asl_attribute7: ',x_asl_attribute7 );
1427 ec_debug.pl ( 3, 'x_asl_attribute8: ',x_asl_attribute8 );
1428 ec_debug.pl ( 3, 'x_asl_attribute9: ',x_asl_attribute9 );
1429 ec_debug.pl ( 3, 'x_asl_attribute10: ',x_asl_attribute10 );
1430 ec_debug.pl ( 3, 'x_asl_attribute11: ',x_asl_attribute11 );
1431 ec_debug.pl ( 3, 'x_asl_attribute12: ',x_asl_attribute12 );
1432 ec_debug.pl ( 3, 'x_asl_attribute13: ',x_asl_attribute13 );
1433 ec_debug.pl ( 3, 'x_asl_attribute14: ',x_asl_attribute14 );
1434 ec_debug.pl ( 3, 'x_asl_attribute15: ',x_asl_attribute15 );
1435
1436 BEGIN
1437 xProgress := 'SPSOB-10-1510';
1438 SELECT primary_vendor_item
1439 INTO x_supplier_product_num
1440 FROM po_approved_supplier_list
1441 WHERE asl_id = x_asl_id;
1442 EXCEPTION
1443 WHEN NO_DATA_FOUND THEN
1444 ec_debug.pl ( 1,
1445 'EC',
1446 'ECE_NO_ROW_SELECTED',
1447 'PROGRESS_LEVEL',
1448 xProgress,
1449 'INFO',
1450 'PRIMARY_VENDOR_ITEM',
1451 'TABLE_NAME',
1452 'PO_APPROVED_SUPPLIER_LIST' );
1453 END;
1454
1455 ec_debug.pl ( 3, 'x_supplier_product_num: ',x_supplier_product_num );
1456
1457
1458 BEGIN -- Planner information
1459 xProgress := 'SPSOB-10-1520';
1460 SELECT last_name,
1461 first_name,
1462 work_telephone
1463 INTO x_planner_last_name,
1464 x_planner_first_name,
1465 x_planner_work_telephone
1466 FROM mtl_system_items msi,
1467 mtl_planners mpl,
1468 per_all_people_f ppf
1469 WHERE msi.organization_id = l_header_tbl(n_organization_id_pos).value
1470 AND msi.inventory_item_id = g_item_id -- Bug 1705597
1471 AND mpl.organization_id = l_header_tbl(n_organization_id_pos).value
1472 AND msi.planner_code = mpl.planner_code(+)
1473 AND mpl.employee_id = ppf.person_id(+)
1474 AND ppf.effective_start_date (+) >= trunc(SYSDATE)
1475 AND ppf.effective_end_date (+) <= trunc(SYSDATE)
1476 AND decode(hr_security.view_all,'Y','TRUE',hr_security.show_record('PER_ALL_PEOPLE_F',
1477 ppf.person_id (+),
1478 ppf.person_type_id (+),
1479 ppf.employee_number (+),
1480 ppf.applicant_number (+) ))= 'TRUE';
1481 EXCEPTION
1482 WHEN NO_DATA_FOUND THEN
1483 ec_debug.pl ( 1,
1484 'EC',
1485 'ECE_NO_ROW_SELECTED',
1486 'PROGRESS_LEVEL',
1487 xProgress,
1488 'INFO',
1489 'LAST_NAME',
1490 'TABLE_NAME',
1491 'MTL_SYSTEM_ITEMS' );
1492 END;
1493
1494 ec_debug.pl ( 3, 'x_planner_last_name: ',x_planner_last_name );
1495 ec_debug.pl ( 3, 'x_planner_first_name: ',x_planner_first_name );
1496 ec_debug.pl ( 3, 'x_planner_work_telephone: ',x_planner_work_telephone );
1497
1498 -- pass the pl/sql table in for xref
1499
1500
1501 xProgress := 'SPSOB-10-1530';
1502 ec_code_Conversion_pvt.populate_plsql_tbl_with_extval ( p_api_version_number => 1.0,
1503 p_init_msg_list => l_init_msg_list,
1504 p_simulate => l_simulate,
1505 p_commit => l_commit,
1506 p_validation_level => l_validation_level,
1507 p_return_status => l_return_status,
1508 p_msg_count => l_msg_count,
1509 p_msg_data => l_msg_data,
1510 p_key_tbl => l_key_tbl,
1511 p_tbl => l_item_tbl );
1512
1513 xProgress := 'SPSOB-10-1540';
1514 IF l_return_status = FND_API.G_RET_STS_ERROR
1515 OR l_return_status IS NULL
1516 OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1517 THEN
1518 RAISE fail_convert_to_ext;
1519 END IF;
1520
1521 BEGIN
1522 xProgress := 'SPSOB-10-1550';
1523 SELECT ece_spso_items_s.nextval
1524 INTO l_item_fkey
1525 FROM sys.dual;
1526 EXCEPTION
1527 WHEN NO_DATA_FOUND THEN
1528 ec_debug.pl ( 0,
1529 'EC',
1530 'ECE_GET_NEXT_SEQ_FAILED',
1531 'PROGRESS_LEVEL',
1532 xProgress,
1533 'SEQ',
1534 'ECE_SPSO_ITEMS_S' );
1535 END;
1536
1537 ec_debug.pl ( 3, 'l_item_fkey: ',l_item_fkey );
1538 xProgress := 'SPSOB-10-1551';
1539 Begin
1540 Select count(schedule_id)
1541 Into x_item_detail
1542 From chv_item_orders
1543 Where schedule_id = l_header_tbl(n_schedule_id_pos).value
1544 And schedule_item_id = l_item_tbl(n_item_id_pos).value;
1545 Exception
1546 when others then null;
1547 End;
1548 if ((exclude_zero_schedule_from_ff = 'N') OR
1549 (x_item_detail > 0)) Then --2944455
1550
1551 xProgress := 'SPSOB-10-1560';
1552 ece_Extract_Utils_PUB.insert_into_interface_tbl ( iRun_id => iRun_id,
1553 cTransaction_Type => cTransaction_Type,
1554 cCommunication_Method => cCommunication_Method,
1555 cInterface_Table => cItem_Interface,
1556 p_source_tbl => l_item_tbl,
1557 p_foreign_key => l_item_fkey );
1558
1559 xProgress := 'SPSOB-10-1570';
1560 UPDATE ece_spso_items
1561 SET supplier_product_number = x_supplier_product_num,
1562 item_scheduler_last_name = x_scheduler_last_name,
1563 item_scheduler_first_name = x_scheduler_first_name,
1564 item_scheduler_work_telephone = x_scheduler_work_telephone,
1565 item_planner_last_name = x_planner_last_name,
1566 item_planner_first_name = x_planner_first_name,
1567 item_planner_work_telephone = x_planner_work_telephone,
1568 asl_attribute_category = x_asl_attribute_category,
1569 asl_attribute1 = x_asl_attribute1,
1570 asl_attribute2 = x_asl_attribute2,
1571 asl_attribute3 = x_asl_attribute3,
1572 asl_attribute4 = x_asl_attribute4,
1573 asl_attribute5 = x_asl_attribute5,
1574 asl_attribute6 = x_asl_attribute6,
1575 asl_attribute7 = x_asl_attribute7,
1576 asl_attribute8 = x_asl_attribute8,
1577 asl_attribute9 = x_asl_attribute9,
1578 asl_attribute10 = x_asl_attribute10,
1579 asl_attribute11 = x_asl_attribute11,
1580 asl_attribute12 = x_asl_attribute12,
1581 asl_attribute13 = x_asl_attribute13,
1582 asl_attribute14 = x_asl_attribute14,
1583 asl_attribute15 = x_asl_attribute15,
1584 ship_to_org_enable_cum_flag =
1585 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1586 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1587 'N', NULL,l_item_tbl(n_enable_cum_flag_pos).value),
1588 ship_to_org_cum_start_date =
1589 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1590 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1591 'N', NULL,to_date(l_item_tbl(n_cum_period_pos).value,'YYYYMMDD HH24MISS')),
1592 ship_to_org_name =
1593 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1594 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1595 'N', NULL,l_item_tbl(n_st_name_pos).value),
1596 ship_to_org_code =
1597 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1598 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1599 'N', NULL,l_item_tbl(n_item_st_org_pos).value),
1600 ship_to_org_address_line_1 =
1601 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1602 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1603 'N', NULL,l_item_tbl(n_st_add_1_pos).value),
1604 ship_to_org_address_line_2 =
1605 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1606 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1607 'N', NULL,l_item_tbl(n_st_add_2_pos).value),
1608 ship_to_org_address_line_3 =
1609 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1610 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1611 'N', NULL,l_item_tbl(n_st_add_3_pos).value),
1612 ship_to_org_city =
1613 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1614 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1615 'N', NULL,l_item_tbl(n_st_city_pos).value),
1616 ship_to_org_region_1 =
1617 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1618 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1619 'N', NULL,l_item_tbl(n_st_county_pos).value),
1620 ship_to_org_region_2 =
1621 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1622 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1623 'N', NULL,l_item_tbl(n_st_state_pos).value),
1624 ship_to_org_country =
1625 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1626 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1627 'N', NULL,l_item_tbl(n_st_country_pos).value),
1628 ship_to_org_postal_code =
1629 DECODE(DECODE(l_header_tbl(n_schedule_type_pos).value, 'SHIP_SCHEDULE','N',
1630 DECODE(NVL(l_header_tbl(n_st_org_code_pos).value,'-1'),'-1','Y','N')),
1631 'N', NULL,l_item_tbl(n_st_postal_pos).value)
1632 WHERE
1633 transaction_record_id = l_item_fkey;
1634
1635 IF SQL%NOTFOUND
1636 THEN
1637 ec_debug.pl ( 1,
1638 'EC',
1639 'ECE_NO_ROW_UPDATED',
1640 'PROGRESS_LEVEL',
1641 xProgress,
1642 'INFO',
1643 'SUPPLIER_PRODUCT_NUMBER',
1644 'TABLE_NAME',
1645 'ECE_SPSO_ITEMS' );
1646 END IF;
1647
1648 -- ******************************************
1649 --
1650 -- Call custom program stub to populate the extension table
1651 --
1652 -- ******************************************
1653
1654 xProgress := 'SPSOB-10-1580';
1655 ece_spso_x.populate_extension_items ( l_item_fkey,
1656 l_item_tbl );
1657 END IF;
1658 END LOOP;
1659
1660 xProgress := 'SPSOB-10-1583';
1661 IF ( dbms_sql.last_row_count = 0 )
1662 THEN
1663 v_LevelProcessed := 'ITEM';
1664 ec_debug.pl ( 1,
1665 'EC',
1666 'ECE_NO_DB_ROW_PROCESSED',
1667 'PROGRESS_LEVEL',
1668 xProgress,
1669 'LEVEL_PROCESSED',
1670 v_LevelProcessed,
1671 'TRANSACTION_TYPE',
1672 cTransaction_Type );
1673 END IF;
1674 END IF;
1675 END LOOP;
1676
1677 xProgress := 'SPSOB-10-1586';
1678 IF ( dbms_sql.last_row_count = 0 )
1679 THEN
1680 v_LevelProcessed := 'HEADER';
1681 ec_debug.pl ( 0,
1682 'EC',
1683 'ECE_NO_DB_ROW_PROCESSED',
1684 'PROGRESS_LEVEL',
1685 xProgress,
1686 'LEVEL_PROCESSED',
1687 v_LevelProcessed,
1688 'TRANSACTION_TYPE',
1689 cTransaction_Type );
1690 END IF;
1691
1692 xProgress := 'SPSOB-10-1590';
1693 dbms_sql.close_cursor ( Header_sel_c );
1694
1695 xProgress := 'SPSOB-10-1600';
1696 dbms_sql.close_cursor ( Item_sel_c );
1697
1698 ec_debug.pop ( 'ECE_SPSO_TRANS1.populate_supplier_sched_api1' );
1699
1700 EXCEPTION
1701 WHEN fail_convert_to_ext THEN
1702
1703 ec_debug.pl ( 0,
1704 'EC',
1705 'ECE_XREF_NOT_FOUND',
1706 NULL );
1707
1708 ec_debug.pl ( 0,
1709 'EC',
1710 'ECE_ERROR_MESSAGE',
1711 'ERROR_MESSAGE',
1712 SQLERRM );
1713
1714 app_exception.raise_exception;
1715
1716 WHEN OTHERS THEN
1717
1718 ec_debug.pl ( 0,
1719 'EC',
1720 'ECE_PROGRAM_ERROR',
1721 'PROGRESS_LEVEL',
1722 xProgress );
1723
1724 ec_debug.pl ( 0,
1725 'EC',
1726 'ECE_ERROR_MESSAGE',
1727 'ERROR_MESSAGE',
1728 SQLERRM );
1729
1730 app_exception.raise_exception;
1731
1732 END Populate_Supplier_Sched_API1;
1733
1734
1735 -- ***********************************************
1736 --
1737 -- PROCEDURE Populate_Supplier_Sched_API3
1738 --
1739 -- ***********************************************
1740
1741
1742 PROCEDURE Populate_Supplier_Sched_API3 ( p_communication_method IN VARCHAR2, -- EDI
1743 p_transaction_type IN VARCHAR2, -- plan SPSO, ship SSSO
1744 p_document_type IN VARCHAR2, -- plan SPS, ship SSS
1745 p_run_id IN NUMBER,
1746 p_schedule_id IN INTEGER DEFAULT 0,
1747 p_batch_id IN NUMBER ) -- Bug 2064311
1748 IS
1749
1750 xProgress VARCHAR2(30) := NULL;
1751 v_LevelProcessed VARCHAR2(40);
1752 cOutput_path VARCHAR2(120);
1753 l_transaction_number NUMBER := 0; -- Bug 1742567
1754 exclude_zero_schedule_from_ff VARCHAR2(1) := 'N'; -- 2944455
1755 /****************************
1756 ** SELECT HEADER **
1757 ****************************/
1758
1759 CURSOR sch_hdr_c IS
1760 SELECT
1761 csh.schedule_id SCHEDULE_ID,
1762 CSH.BATCH_ID BATCH_ID, --Bug 2064311
1763 csh.organization_id ORGANIZATION_ID,
1764 csh.vendor_id VENDOR_ID,
1765 csh.vendor_site_id VENDOR_SITE_ID,
1766 csh.schedule_type SCHEDULE_TYPE,
1767 csh.schedule_horizon_start FORECAST_HORIZON_START_DATE,
1768 csh.edi_count EDI_COUNT,
1769 ccp.cum_period_start_date SHIP_TO_ORG_CUM_START,
1770 etd.document_id TRANSACTION_TYPE
1771 FROM
1772 chv_cum_periods ccp,
1773 ece_tp_details etd,
1774 po_vendor_sites pvs,
1775 chv_schedule_headers csh,
1776 chv_org_options coo
1777 WHERE
1778 csh.schedule_status = 'CONFIRMED'
1779 AND etd.edi_flag = 'Y' -- EDI
1780 AND etd.document_id = p_transaction_type --ship SSSO,plan SPSO
1781 AND p_transaction_type = DECODE(schedule_type,
1782 'SHIP_SCHEDULE', 'SSSO',
1783 'SPSO')
1784 AND ((csh.schedule_id = p_schedule_id
1785 AND p_schedule_id <> 0)
1786 OR (p_schedule_id = 0))
1787 AND CSH.BATCH_ID = decode(P_BATCH_ID,0,CSH.BATCH_ID,P_BATCH_ID) -- Bug 2064311
1788 AND NVL(csh.communication_code,'NONE') IN ('BOTH','EDI')
1789 AND csh.vendor_site_id = pvs.vendor_site_id
1790 AND pvs.tp_header_id = etd.tp_header_id
1791 AND csh.organization_id = ccp.organization_id(+)
1792 AND csh.organization_id = coo.organization_id(+)
1793 AND (
1794 ( coo.enable_cum_flag = 'N' )
1795 or
1796 ( ( coo.enable_cum_flag = 'Y')
1797 AND
1798 (
1799 (
1800 ccp.cum_period_end_date IS NULL
1801 AND csh.schedule_horizon_start >= ccp.cum_period_start_date
1802 )
1803 OR
1804 ( csh.schedule_horizon_start BETWEEN ccp.cum_period_start_date
1805 AND ccp.cum_period_end_date
1806 )
1807 )
1808 )
1809 )
1810 AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
1811 WHERE CIO.SCHEDULE_ID = CSH.SCHEDULE_ID)
1812 ORDER BY
1813 csh.schedule_id
1814 FOR UPDATE;
1815
1816 BEGIN -- begin header block
1817
1818 ec_debug.push ( 'ECE_SPSO_TRANS1.populate_supplier_sched_api3' );
1819 ec_debug.pl ( 3, 'p_communication_method: ', p_communication_method );
1820 ec_debug.pl ( 3, 'p_transaction_type: ',p_transaction_type );
1821 ec_debug.pl ( 3, 'p_document_type: ',p_document_type );
1822 ec_debug.pl ( 3, 'p_run_id: ',p_run_id );
1823 ec_debug.pl ( 3, 'p_schedule_id: ',p_schedule_id );
1824
1825
1826 -- Retreive the system profile option ECE_OUT_FILE_PATH. This will
1827 -- be the directory where the output file will be written.
1828 -- NOTE: THIS DIRECTORY MUST BE SPECIFIED IN THE PARAMETER utl_file_dir IN
1829 -- THE INIT.ORA FILE. Refer to the Oracle7 documentation for more information
1830 -- on the package UTL_FILE.
1831
1832 xProgress := 'SPSOB-30-0100';
1833 fnd_profile.get('ECE_OUT_FILE_PATH',
1834 cOutput_path);
1835 ec_debug.pl ( 3, 'cOutput_path: ',cOutput_path );
1836
1837
1838 <<header>>
1839
1840 xProgress := 'SPSOB-30-1000';
1841 FOR rec_hdr IN sch_hdr_c
1842 LOOP
1843
1844 /**************************
1845 ** SELECT ITEM **
1846 **************************/
1847
1848 DECLARE
1849 x_transaction_date DATE;
1850 x_last_quantity NUMBER;
1851 x_shipment_num VARCHAR2(30);
1852
1853 x_item_detail_sequence NUMBER :=0;
1854
1855 x_enable_authorizations_flag VARCHAR2(1);
1856
1857 x_transaction_record_id NUMBER;
1858
1859 CURSOR sch_item_c IS
1860 SELECT
1861 csi.schedule_id SCHEDULE_ID,
1862 csi.schedule_item_id SCHEDULE_ITEM_ID,
1863 csi.item_id ITEM_ID,
1864 csi.starting_auth_quantity STARTING_AUTH_QUANTITY,
1865 csi.starting_cum_quantity STARTING_CUM_QUANTITY,
1866 coo.enable_cum_flag SHIP_TO_ORG_ENABLE_CUM_FLAG,
1867 ccp.cum_period_start_date SHIP_TO_ORG_CUM_PERIOD_START,
1868 csi.last_receipt_transaction_id LAST_RECEIPT_TRANSACTION_ID,
1869 csi.purchasing_unit_of_measure PURCHASING_UNIT_OF_MEASURE
1870 FROM
1871 chv_schedule_headers csh,
1872 chv_schedule_items csi,
1873 chv_org_options coo,
1874 chv_cum_periods ccp,
1875 mtl_item_flexfields mif,
1876 mtl_parameters mtp
1877 WHERE
1878 csi.schedule_id = rec_hdr.schedule_id
1879 AND csi.schedule_id = csh.schedule_id
1880 AND csi.organization_id = coo.organization_id
1881 AND csi.organization_id = mtp.organization_id
1882 AND csi.item_id = mif.item_id
1883 AND csi.organization_id = mif.organization_id
1884 AND csi.organization_id = ccp.organization_id(+)
1885 AND (
1886 (COO.ENABLE_CUM_FLAG = 'N')
1887 OR
1888 (
1889 ( COO.ENABLE_CUM_FLAG = 'Y')
1890 AND
1891 (
1892 (
1893 CCP.CUM_PERIOD_END_DATE IS NULL and csh.schedule_horizon_start >=
1894 ccp.cum_period_start_date
1895 )
1896 OR (
1897 CSH.SCHEDULE_HORIZON_START BETWEEN CCP.CUM_PERIOD_START_DATE
1898 AND CCP.CUM_PERIOD_END_DATE
1899 )
1900 )
1901 )
1902 )
1903 AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
1904 WHERE CIO.SCHEDULE_ITEM_ID = CSI.SCHEDULE_ITEM_ID)
1905 ORDER BY
1906 csi.schedule_id,
1907 csi.schedule_item_id,
1908 mif.item_id,
1909 mtp.organization_code;
1910
1911
1912 BEGIN -- begin item block
1913
1914 <<item>>
1915
1916 xProgress := 'SPSOB-30-1010';
1917 FOR rec_item IN sch_item_c
1918 LOOP
1919
1920 /*********************************************************
1921 ** select the last sequence number assigned to **
1922 ** the detail record of the same schedule item id. **
1923 *********************************************************/
1924
1925 BEGIN
1926 xProgress := 'SPSOB-30-1020';
1927 SELECT MAX(schedule_item_detail_sequence)
1928 INTO x_item_detail_sequence
1929 FROM ece_spso_item_det
1930 WHERE schedule_id = rec_item.schedule_id
1931 AND schedule_item_id = rec_item.schedule_item_id;
1932 EXCEPTION
1933 WHEN NO_DATA_FOUND THEN
1934 ec_debug.pl ( 1,
1935 'EC',
1936 'ECE_NO_ROW_SELECTED',
1937 'PROGRESS_LEVEL',
1938 xProgress,
1939 'INFO',
1940 'MAX(SCHEDULE_ITEM_DETAIL_SEQUENCE)',
1941 'TABLE_NAME',
1942 'ECE_SPSO_ITEM_DET' );
1943 END;
1944
1945 ec_debug.pl ( 3, 'x_item_detail_sequence: ',x_item_detail_sequence );
1946
1947 BEGIN
1948 xProgress := 'SPSOB-30-1030';
1949 SELECT transaction_record_id
1950 INTO x_transaction_record_id
1951 FROM ece_spso_items
1952 WHERE schedule_id = rec_item.schedule_id
1953 AND schedule_item_id = rec_item.schedule_item_id
1954 AND run_id = p_run_id;
1955 EXCEPTION
1956 WHEN NO_DATA_FOUND THEN
1957 ec_debug.pl ( 1,
1958 'EC',
1959 'ECE_NO_ROW_SELECTED',
1960 'PROGRESS_LEVEL',
1961 xProgress,
1962 'INFO',
1963 'TRANSACTION_RECORD_ID',
1964 'TABLE_NAME',
1965 'ECE_SPSO_ITEMS' );
1966 END;
1967
1968 ec_debug.pl ( 3, 'x_transaction_record_id: ',x_transaction_record_id );
1969
1970 /*************************************************
1971 ** SELECT ENABLE_AUTHORIZATION_FLAG **
1972 ** FROM APPROVED SUPPLIER LIST TABLE **
1973 ** FOR THE SPECIFIED VENODR, SITE, ITEM AND **
1974 ** ORGANIZATION. **
1975 *************************************************/
1976
1977 BEGIN -- ASL block
1978 xProgress := 'SPSOB-30-1040';
1979 SELECT
1980 enable_authorizations_flag
1981 INTO
1982 x_enable_authorizations_flag
1983 FROM po_asl_attributes paa
1984 WHERE vendor_id = rec_hdr.vendor_id
1985 AND vendor_site_id = rec_hdr.vendor_site_id
1986 AND item_id = rec_item.item_id
1987 AND using_organization_id = chv_inq_sv.get_asl_org(
1988 rec_hdr.organization_id,
1989 rec_hdr.vendor_id,
1990 rec_hdr.vendor_site_id,
1991 rec_item.item_id);
1992 EXCEPTION
1993 WHEN NO_DATA_FOUND THEN
1994 ec_debug.pl ( 1,
1995 'EC',
1996 'ECE_NO_ROW_SELECTED',
1997 'PROGRESS_LEVEL',
1998 xProgress,
1999 'INFO',
2000 'ENABLE_AUTHORIZATIONS_FLAG',
2001 'TABLE_NAME',
2002 'PO_ASL_ATTRIBUTES' );
2003 END;
2004
2005 ec_debug.pl ( 3, 'x_enable_authorizations_flag: ',x_enable_authorizations_flag );
2006
2007 /**************************************
2008 ** SELECT AND INSERT ITEM DETAIL **
2009 **************************************/
2010
2011 DECLARE
2012 x_start_date DATE;
2013 x_detail_category VARCHAR2(25);
2014 x_item_order NUMBER;
2015 x_item_detail NUMBER;
2016
2017 CURSOR sch_detail_c IS
2018 SELECT authorization_code AUTHORIZATION_CODE,
2019 cutoff_date CUTOFF_DATE,
2020 schedule_quantity SCHEDULE_QUANTITY
2021 FROM chv_authorizations
2022 WHERE reference_id = rec_item.schedule_item_id
2023 AND reference_type = 'SCHEDULE_ITEMS';
2024
2025 BEGIN -- begin item detail block
2026
2027 /***************************************
2028 ** insert prior authorization detail **
2029 ***************************************/
2030
2031 xProgress := 'SPSOB-30-1050';
2032 ece_spso_trans1.update_chv_schedule_headers ( rec_hdr.transaction_type,
2033 rec_hdr.schedule_id,
2034 rec_hdr.batch_id, --Bug 2064311
2035 rec_hdr.edi_count );
2036 Select count(*)
2037 Into x_item_order
2038 From chv_item_orders
2039 Where schedule_id = rec_hdr.schedule_id;
2040
2041 Select count(schedule_id)
2042 Into x_item_detail
2043 From chv_item_orders
2044 Where schedule_id = rec_hdr.schedule_id
2045 And schedule_item_id = rec_item.schedule_item_id;
2046
2047 fnd_profile.get('ECE_SPSO_EXCLUDE_ZERO_SCHEDULE_FROM_FF',exclude_zero_schedule_from_ff);
2048 If NVL(exclude_zero_schedule_from_ff,'N')<>'Y' then
2049 exclude_zero_schedule_from_ff := 'N';
2050 End If; -- 2944455
2051
2052
2053 xProgress := 'SPSOB-30-1060';
2054 IF x_enable_authorizations_flag = 'Y' AND
2055 p_transaction_type = 'SPSO'
2056 THEN
2057 IF ((exclude_zero_schedule_from_ff = 'N') OR
2058 (x_item_order > 0 AND x_item_detail > 0)) Then -- 2944455
2059
2060 xProgress := 'SPSOB-30-1070';
2061 IF rec_item.ship_to_org_enable_cum_flag ='Y'
2062 THEN
2063
2064 -- increment detail record sequence counter
2065
2066 xProgress := 'SPSOB-30-1080';
2067 x_item_detail_sequence := NVL(x_item_detail_sequence,0) + 1;
2068
2069 xProgress := 'SPSOB-30-1090';
2070 INSERT INTO ece_spso_item_det
2071 (
2072 run_id,
2073 schedule_item_detail_sequence,
2074 schedule_id,
2075 schedule_item_id,
2076 detail_category,
2077 detail_descriptor,
2078 starting_date,
2079 forecast_quantity,
2080 release_quantity,
2081 total_quantity,
2082 transaction_record_id
2083 )
2084 VALUES
2085 (
2086 p_run_id,
2087 x_item_detail_sequence,
2088 rec_item.schedule_id,
2089 rec_item.schedule_item_id,
2090 'AUTHORIZATION',
2091 'PRIOR',
2092 rec_hdr.forecast_horizon_start_date,
2093 0,
2094 0,
2095 NVL(rec_item.starting_auth_quantity,0),
2096 ece_spso_item_det_s.nextval
2097 );
2098 -- Bug 1742567
2099 select
2100 ece_spso_item_det_s.currval
2101 into
2102 l_transaction_number
2103 from
2104 dual;
2105 ECE_SPSO_X.populate_extension_item_det(l_transaction_number,
2106 rec_item.schedule_id,
2107 rec_item.schedule_item_id);
2108
2109 END IF;
2110
2111 /****************************************
2112 ** insert current authorization detail **
2113 ****************************************/
2114
2115 -- authorization start date is the cum start date.
2116 -- cum_flag is enabled since cum quantity is included
2117 -- in authorization quantity.
2118
2119
2120 xProgress := 'SPSOB-30-1100';
2121 IF rec_item.ship_to_org_enable_cum_flag = 'Y' THEN
2122
2123 x_start_date := rec_item.ship_to_org_cum_period_start;
2124
2125 ELSE
2126
2127 x_start_date := rec_hdr.forecast_horizon_start_date;
2128
2129 END IF;
2130 ec_debug.pl ( 3, 'x_start_date: ',x_start_date );
2131
2132 xProgress := 'SPSOB-30-1110';
2133
2134 <<authorization>>
2135
2136 FOR rec_detail IN sch_detail_c
2137 LOOP
2138
2139 -- increment detail record sequence counter
2140
2141 x_item_detail_sequence := NVL(x_item_detail_sequence,0) + 1;
2142
2143 xProgress := 'SPSOB-30-1120';
2144
2145 INSERT INTO ece_spso_item_det
2146 (
2147 run_id,
2148 schedule_item_detail_sequence,
2149 schedule_id,
2150 schedule_item_id,
2151 detail_category,
2152 detail_descriptor,
2153 starting_date,
2154 ending_date,
2155 forecast_quantity,
2156 release_quantity,
2157 total_quantity,
2158 transaction_record_id
2159 )
2160 VALUES
2161 (
2162 p_run_id,
2163 x_item_detail_sequence,
2164 rec_item.schedule_id,
2165 rec_item.schedule_item_id,
2166 'AUTHORIZATION',
2167 rec_detail.authorization_code,
2168 x_start_date,
2169 rec_detail.cutoff_date,
2170 0,
2171 0,
2172 NVL(rec_detail.schedule_quantity,0),
2173 ece_spso_item_det_s.nextval
2174 );
2175 -- Bug 1742567
2176 select
2177 ece_spso_item_det_s.currval
2178 into
2179 l_transaction_number
2180 from
2181 dual;
2182 ECE_SPSO_X.populate_extension_item_det(l_transaction_number,
2183 rec_item.schedule_id,
2184 rec_item.schedule_item_id);
2185
2186 END LOOP authorization;
2187 END IF;
2188 END IF;
2189
2190
2191 /********************************
2192 ** insert last receipt detail **
2193 ********************************/
2194
2195 xProgress := 'SPSOB-30-1130';
2196 IF ((exclude_zero_schedule_from_ff = 'N') OR
2197 (x_item_order > 0 AND x_item_detail > 0)) Then -- 2944455
2198 IF rec_item.last_receipt_transaction_id IS NOT NULL
2199 THEN
2200
2201 -- increment detail record sequence counter
2202
2203 x_item_detail_sequence := NVL(x_item_detail_sequence,0) + 1;
2204 ec_debug.pl ( 3, 'x_item_detail_sequence: ',x_item_detail_sequence );
2205
2206 xProgress := 'SPSOB-30-1140';
2207
2208 -- DEBUG Sri's proc package name may chg
2209 -- DEBUG comments
2210
2211 chv_inq_sv.get_receipt_qty ( rec_item.last_receipt_transaction_id,
2212 rec_item.item_id,
2213 rec_item.purchasing_unit_of_measure,
2214 x_last_quantity,
2215 x_shipment_num,
2216 x_transaction_date );
2217
2218 -- ***************************
2219 -- the following UPDATE is added for version 2.0
2220 -- ***************************
2221
2222 xProgress := 'SPSOB-30-1150';
2223 ec_debug.pl ( 3, 'x_shipment_num: ',x_shipment_num );
2224 ec_debug.pl ( 3, 'x_transaction_date: ',x_transaction_date );
2225 ec_debug.pl ( 3, 'x_last_quantity: ',x_last_quantity );
2226
2227 UPDATE ece_spso_items
2228 SET last_receipt_shipment_code = x_shipment_num,
2229 last_receipt_date = x_transaction_date,
2230 last_receipt_quantity = x_last_quantity
2231 WHERE transaction_record_id = x_transaction_record_id;
2232
2233 xProgress := 'SPSOB-30-1160';
2234
2235 INSERT INTO ece_spso_item_det
2236 (
2237 run_id,
2238 schedule_item_detail_sequence,
2239 schedule_id,
2240 schedule_item_id,
2241 detail_category,
2242 detail_descriptor,
2243 starting_date,
2244 forecast_quantity,
2245 release_quantity,
2246 total_quantity,
2247 document_type,
2248 document_number,
2249 transaction_record_id
2250 )
2251 VALUES
2252 (
2253 p_run_id,
2254 x_item_detail_sequence,
2255 rec_item.schedule_id,
2256 rec_item.schedule_item_id,
2257 'RECEIPT',
2258 'LAST',
2259 x_transaction_date,
2260 0,
2261 0,
2262 NVL(x_last_quantity,0),
2263 'SHIPMENT',
2264 x_shipment_num,
2265 ece_spso_item_det_s.nextval
2266 );
2267 -- Bug 1742567
2268 select
2269 ece_spso_item_det_s.currval
2270 into
2271 l_transaction_number
2272 from
2273 dual;
2274 ECE_SPSO_X.populate_extension_item_det(l_transaction_number,
2275 rec_item.schedule_id,
2276 rec_item.schedule_item_id);
2277
2278 END IF;
2279
2280
2281 /********************************
2282 ** insert CUM receipt detail **
2283 ********************************/
2284
2285
2286 xProgress := 'SPSOB-30-1170';
2287 IF rec_item.ship_to_org_enable_cum_flag = 'Y'
2288 THEN
2289
2290 -- increment detail record sequence counter
2291
2292 x_item_detail_sequence := NVL(x_item_detail_sequence,0) + 1;
2293 ec_debug.pl ( 3, 'x_item_detail_sequence: ',x_item_detail_sequence );
2294
2295 xProgress := 'SPSOB-30-1180';
2296
2297 INSERT INTO ece_spso_item_det
2298 (
2299 run_id,
2300 schedule_item_detail_sequence,
2301 schedule_id,
2302 schedule_item_id,
2303 detail_category,
2304 detail_descriptor,
2305 starting_date,
2306 ending_date,
2307 forecast_quantity,
2308 release_quantity,
2309 total_quantity,
2310 transaction_record_id
2311 )
2312 VALUES
2313 (
2314 p_run_id,
2315 x_item_detail_sequence,
2316 rec_item.schedule_id,
2317 rec_item.schedule_item_id,
2318 'RECEIPT',
2319 'CUMULATIVE',
2320 rec_item.ship_to_org_cum_period_start,
2321 rec_hdr.forecast_horizon_start_date,
2322 0,
2323 0,
2324 NVL(rec_item.starting_cum_quantity,0),
2325 ece_spso_item_det_s.nextval
2326 );
2327 -- Bug 1742567
2328 select
2329 ece_spso_item_det_s.currval
2330 into
2331 l_transaction_number
2332 from
2333 dual;
2334 ECE_SPSO_X.populate_extension_item_det(l_transaction_number,
2335 rec_item.schedule_id,
2336 rec_item.schedule_item_id);
2337
2338 END IF;
2339
2340 -- ***************************
2341 -- the following UPDATE is added for version 2.0
2342 -- ***************************
2343
2344 xProgress := 'SPSOB-30-1190';
2345 ec_debug.pl ( 3, 'rec_item.starting_cum_quantity: ',NVL(rec_item.starting_cum_quantity,0) );
2346 UPDATE ece_spso_items
2347 SET last_receipt_cum_qty = NVL(rec_item.starting_cum_quantity,0)
2348 WHERE transaction_record_id = x_transaction_record_id;
2349 END IF;
2350
2351 EXCEPTION
2352 WHEN OTHERS THEN
2353
2354 ec_debug.pl ( 0,
2355 'EC',
2356 'ECE_PROGRAM_ERROR',
2357 'PROGRESS_LEVEL',
2358 xProgress );
2359
2360 ec_debug.pl ( 0,
2361 'EC',
2362 'ECE_ERROR_MESSAGE',
2363 'ERROR_MESSAGE',
2364 SQLERRM );
2365
2366 app_exception.raise_exception;
2367
2368 END; -- item detail block
2369
2370
2371
2372 END LOOP item; -- item for loop
2373
2374 EXCEPTION
2375 WHEN OTHERS THEN
2376
2377 ec_debug.pl ( 0,
2378 'EC',
2379 'ECE_PROGRAM_ERROR',
2380 'PROGRESS_LEVEL',
2381 xProgress );
2382
2383 ec_debug.pl ( 0,
2384 'EC',
2385 'ECE_ERROR_MESSAGE',
2386 'ERROR_MESSAGE',
2387 SQLERRM );
2388
2389 app_exception.raise_exception;
2390
2391 END; -- item block
2392
2393 END LOOP header; -- header for loop
2394
2395 ec_debug.pop ( 'ece_spso_trans1.Populate_Supplier_Sched_API3' );
2396
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399
2400 ec_debug.pl ( 0,
2401 'EC',
2402 'ECE_PROGRAM_ERROR',
2403 'PROGRESS_LEVEL',
2404 xProgress );
2405
2406 ec_debug.pl ( 0,
2407 'EC',
2408 'ECE_ERROR_MESSAGE',
2409 'ERROR_MESSAGE',
2410 SQLERRM );
2411
2412 app_exception.raise_exception;
2413
2414 END Populate_Supplier_Sched_API3; -- end of procedure
2415
2416
2417 /*************************************************************************
2418 ** procedure UPDATE_CHV_SCHEDULE_HEADERS **
2419 ** This procedure will update the records in CHV_SCHEDULE_HEADERS table**
2420 ** which have been extracted for EDI transmission. The communication **
2421 ** code will be set according to their inital value. If the record is **
2422 ** flaged for BOTH print and edi, after performing EDI transaction it **
2423 ** will be reset to print. If the initial vaues is EDI then after **
2424 ** completion of transaction the code will be set to NONE. **
2425 *************************************************************************/
2426
2427 PROCEDURE Update_CHV_Schedule_Headers ( p_transaction_type IN VARCHAR2,
2428 p_schedule_id IN INTEGER := 0,
2429 p_batch_id IN NUMBER,
2430 p_edi_count IN NUMBER := 0 )
2431 IS
2432
2433 xProgress VARCHAR2(30) := NULL;
2434 cOutput_path varchar2(120);
2435
2436 BEGIN
2437
2438 ec_debug.push ( 'ECE_SPSO_TRANS1.UPDATE_CHV_SCHEDULE_HEADERS' );
2439 ec_debug.pl ( 3, 'p_transaction_type: ',p_transaction_type );
2440 ec_debug.pl ( 3, 'p_schedule_id: ',p_schedule_id );
2441 ec_debug.pl ( 3, 'p_edi_count: ',p_edi_count );
2442
2443 -- Retreive the system profile option ECE_OUT_FILE_PATH. This will
2444 -- be the directory where the output file will be written.
2445 -- NOTE: THIS DIRECTORY MUST BE SPECIFIED IN THE PARAMETER utl_file_dir IN
2446 -- THE INIT.ORA FILE. Refer to the Oracle7 documentation for more information
2447 -- on the package UTL_FILE.
2448
2449 xProgress := 'SPSOB-40-0100';
2450 fnd_profile.get ( 'ECE_OUT_FILE_PATH',
2451 cOutput_path );
2452
2453 ec_debug.pl ( 3, 'cOutput_path: ',cOutput_path );
2454
2455 xProgress := 'SPSOB-40-1000';
2456
2457 UPDATE chv_schedule_headers
2458 SET communication_code = DECODE ( communication_code,
2459 'BOTH', 'PRINT',
2460 'EDI', 'NONE',
2461 'NONE', 'NONE',
2462 'PRINT', 'PRINT',
2463 NULL ),
2464 last_update_date = SYSDATE,
2465 last_updated_by = -1,
2466 last_edi_date = SYSDATE,
2467 edi_count = NVL(p_edi_count,0) + 1
2468 WHERE ((schedule_id = p_schedule_id AND
2469 p_schedule_id <> 0) OR
2470 (p_schedule_id = 0 AND
2471 NVL(communication_code, 'NONE') IN ('BOTH','EDI')))
2472 AND p_transaction_type = DECODE ( schedule_type,
2473 'SHIP_SCHEDULE', 'SSSO',
2474 'SPSO' )
2475 AND batch_id = decode(p_batch_id,0,batch_id,p_batch_id); -- Bug 2064311
2476 ec_debug.pop ( 'ECE_SPSO_TRANS1.UPDATE_CHV_SCHEDULE_HEADERS' );
2477
2478 EXCEPTION
2479 WHEN OTHERS THEN
2480 ec_debug.pl ( 0,
2481 'EC',
2482 'ECE_PROGRAM_ERROR',
2483 'PROGRESS_LEVEL',
2484 xProgress );
2485
2486 ec_debug.pl ( 0,
2487 'EC',
2488 'ECE_ERROR_MESSAGE',
2489 'ERROR_MESSAGE',
2490 SQLERRM );
2491
2492 app_exception.raise_exception;
2493
2494 END Update_CHV_Schedule_Headers;
2495
2496
2497 /*************************************************************************
2498 ** procedure PUT_DATA_TO_OUTPUT_TABLE **
2499 ** This procedure has the following functionalities: **
2500 ** 1. Build SQL statement dynamically to extract data from **
2501 ** Interface Tables. **
2502 ** 2. Execute the dynamic SQL statement. **
2503 ** 3. Populate the ECE_OUTPUT table with the extracted data. **
2504 ** 4. Delete data from Interface Tables. **
2505 ** To use this procedure must have access to the procedures in **
2506 ** ECE_FLATFILE package. **
2507 ** HISTORY: **
2508 ** Apr 3, 1995 wlang Created. **
2509 ** **
2510 ** May 15, 1996 mbabaloy **
2511 *************************************************************************/
2512
2513 PROCEDURE Put_Data_To_Output_Table ( p_communication_method IN VARCHAR2,
2514 p_transaction_type IN VARCHAR2, -- plan SPSO, ship SSSO
2515 p_output_width IN INTEGER,
2516 p_run_id IN INTEGER,
2517 p_header_interface IN VARCHAR2 := 'ECE_SPSO_HEADERS',
2518 p_item_interface IN VARCHAR2 := 'ECE_SPSO_ITEMS',
2519 p_item_d_interface IN VARCHAR2 := 'ECE_SPSO_ITEM_DET',
2520 p_ship_d_interface IN VARCHAR2 := 'ECE_SPSO_SHIP_DET')
2521 IS
2522 xProgress VARCHAR2(30);
2523 cOutput_path VARCHAR2(120);
2524
2525 l_header_tbl ece_flatfile_pvt.Interface_tbl_type;
2526 l_item_tbl ece_flatfile_pvt.Interface_tbl_type;
2527 l_item_d_tbl ece_flatfile_pvt.Interface_tbl_type;
2528 l_ship_d_tbl ece_flatfile_pvt.Interface_tbl_type;
2529
2530 c_header_common_key_name VARCHAR2(40);
2531 c_item_common_key_name VARCHAR2(40);
2532 c_item_d_common_key_name VARCHAR2(40);
2533 c_ship_d_common_key_name VARCHAR2(40);
2534 c_file_common_key VARCHAR2(255);
2535
2536 nHeader_key_pos NUMBER;
2537 nItem_key_pos NUMBER;
2538 nItem_D_key_pos NUMBER;
2539 nShip_D_key_pos NUMBER;
2540 nTrans_code_pos NUMBER;
2541
2542 v_header_sel_c INTEGER;
2543 v_item_sel_c INTEGER;
2544 v_item_d_sel_c INTEGER;
2545 v_ship_d_sel_c INTEGER;
2546
2547 v_header_del_c1 INTEGER;
2548 v_item_del_c1 INTEGER;
2549 v_item_d_del_c1 INTEGER;
2550 v_ship_d_del_c1 INTEGER;
2551
2552 v_header_del_c2 INTEGER;
2553 v_item_del_c2 INTEGER;
2554 v_item_d_del_c2 INTEGER;
2555 v_ship_d_del_c2 INTEGER;
2556
2557 x_header_select VARCHAR2(32000);
2558 x_item_select VARCHAR2(32000);
2559 x_item_d_select VARCHAR2(32000);
2560 x_ship_d_select VARCHAR2(32000);
2561
2562 x_header_from VARCHAR2(32000);
2563 x_item_from VARCHAR2(32000);
2564 x_item_d_from VARCHAR2(32000);
2565 x_ship_d_from VARCHAR2(32000);
2566
2567 x_header_where VARCHAR2(32000);
2568 x_item_where VARCHAR2(32000);
2569 x_item_d_where VARCHAR2(32000);
2570 x_ship_d_where VARCHAR2(32000);
2571
2572 x_header_delete1 VARCHAR2(32000);
2573 x_item_delete1 VARCHAR2(32000);
2574 x_item_d_delete1 VARCHAR2(32000);
2575 x_ship_d_delete1 VARCHAR2(32000);
2576
2577 x_header_delete2 VARCHAR2(32000);
2578 x_item_delete2 VARCHAR2(32000);
2579 x_item_d_delete2 VARCHAR2(32000);
2580 x_ship_d_delete2 VARCHAR2(32000);
2581
2582 x_header_count NUMBER;
2583 x_item_count NUMBER;
2584 x_item_d_count NUMBER;
2585 x_ship_d_count NUMBER;
2586
2587 x_header_rowid ROWID;
2588 x_item_rowid ROWID;
2589 x_item_d_rowid ROWID;
2590 x_ship_d_rowid ROWID;
2591
2592 x_header_x_interface VARCHAR2(50);
2593 x_item_x_interface VARCHAR2(50);
2594 x_item_d_x_interface VARCHAR2(50);
2595 x_ship_d_x_interface VARCHAR2(50);
2596
2597 x_header_x_rowid ROWID;
2598 x_item_x_rowid ROWID;
2599 x_item_d_x_rowid ROWID;
2600 x_ship_d_x_rowid ROWID;
2601
2602 x_header_start_num INTEGER;
2603
2604 x_item_start_num INTEGER;
2605 x_item_d_start_num INTEGER;
2606 x_dummy INTEGER;
2607
2608 x_schedule_id NUMBER;
2609 n_schedule_id_pos NUMBER;
2610 x_schedule_item_id NUMBER;
2611 x_schedule_item_id_pos NUMBER;
2612 x_pos1 NUMBER;
2613 x_pos2 NUMBER;
2614 x_sch_item_detail_seq NUMBER;
2615
2616 c_header_select VARCHAR2(100);
2617
2618 BEGIN
2619
2620 ec_debug.push ( 'ECE_SPSO_TRANS1.PUT_DATA_TO_OUTPUT_TABLE' );
2621 ec_debug.pl ( 3, 'p_communication_method: ', p_communication_method );
2622 ec_debug.pl ( 3, 'p_transaction_type: ',p_transaction_type );
2623 ec_debug.pl ( 3, 'p_output_width: ',p_output_width );
2624 ec_debug.pl ( 3, 'p_run_id: ',p_run_id );
2625 ec_debug.pl ( 3, 'p_header_interface: ',p_header_interface );
2626 ec_debug.pl ( 3, 'p_item_interface: ',p_item_interface );
2627 ec_debug.pl ( 3, 'p_item_d_interface: ',p_item_d_interface );
2628 ec_debug.pl ( 3, 'p_ship_d_interface: ',p_ship_d_interface );
2629
2630 -- Retreive the system profile option ECE_OUT_FILE_PATH. This will
2631 -- be the directory where the output file will be written.
2632 -- NOTE: THIS DIRECTORY MUST BE SPECIFIED IN THE PARAMETER utl_file_dir IN
2633 -- THE INIT.ORA FILE. Refer to the Oracle7 documentation for more information
2634 -- on the package UTL_FILE.
2635
2636 xProgress := 'SPSOB-50-0100';
2637 fnd_profile.get ( 'ECE_OUT_FILE_PATH',
2638 cOutput_path );
2639 ec_debug.pl ( 3, 'cOutput_path: ',cOutput_path );
2640
2641 /* --------------------------------------------------------------------------
2642 -- Here, I am building the SELECT, FROM, and WHERE clauses for the dynamic
2643 -- SQL call
2644 -- The ece_flatfile_pvt.select_clause uses the db data dictionary for the build.
2645 -- (The db data dictionary store contains all types of info about Interface
2646 -- tables and Extension tables.)
2647
2648 -- The DELETE clauses will be used to clean up both the interface and extension
2649 -- tables. I am using ROWID to tell me which row in the interface table is
2650 -- being written to the output table, thus, can be deleted.
2651 --------------------------------------------------------------------------*/
2652
2653 xProgress := 'SPSOB-50-1000';
2654 ece_flatfile_pvt.select_clause ( p_transaction_type,
2655 p_communication_method,
2656 p_header_interface,
2657 x_header_x_interface,
2658 l_header_tbl,
2659 c_header_common_key_name,
2660 x_header_select,
2661 x_header_from,
2662 x_header_where );
2663
2664 xProgress := 'SPSOB-50-1010';
2665 ece_flatfile_pvt.select_clause ( p_transaction_type,
2666 p_communication_method,
2667 p_item_interface,
2668 x_item_x_interface,
2669 l_item_tbl,
2670 c_item_common_key_name,
2671 x_item_select,
2672 x_item_from ,
2673 x_item_where );
2674
2675 xProgress := 'SPSOB-50-1020';
2676 ece_flatfile_pvt.select_clause ( p_transaction_type,
2677 p_communication_method,
2678 p_item_d_interface,
2679 x_item_d_x_interface,
2680 l_item_d_tbl,
2681 c_item_d_common_key_name,
2682 x_item_d_select,
2683 x_item_d_from ,
2684 x_item_d_where );
2685
2686 xProgress := 'SPSOB-50-1030';
2687 if (p_transaction_type = 'SSSO') then
2688 ece_flatfile_pvt.select_clause ( p_transaction_type,
2689 p_communication_method,
2690 p_ship_d_interface,
2691 x_ship_d_x_interface,
2692 l_ship_d_tbl,
2693 c_ship_d_common_key_name,
2694 x_ship_d_select,
2695 x_ship_d_from ,
2696 x_ship_d_where );
2697 end if;
2698 /* --------------------------------------------------------------------------
2699 REM Here, I am customizing the WHERE clause to join the Interface
2700 REM tables together. i.e. Headers -- Items -- Item Details
2701 REM Select Data1, Data2, Data3...........
2702 REM From v_header_Interface A, v_item_Interface B,
2703 REM v_item_details_Interface C,
2704 REM v_header_Interface_X D, v_item_Interface_X E,
2705 REM v_item_details_Interface_X F
2706 REM Where A.Transaction_Record_ID = D.Transaction_Record_ID (+)
2707 REM and B.Transaction_Record_ID = E.Transaction_Record_ID (+)
2708 REM and C.Transaction_Record_ID = F.Transaction_Record_ID (+)
2709 REM $$$$$ (Customization should be added here) $$$$$$
2710 REM and A.Communication_Method = 'EDI'
2711 REM and A.xxx = B.xxx ........
2712 REM and B.yyy = C.yyy .......
2713 REM -------------------------------------------------------------------------*/
2714
2715
2716 /* --------------------------------------------------------------------------
2717 :schedule_id is a place holder for foreign key value.
2718 A PL/SQL table (list of values) will be used to store data.
2719 Procedure ece_flatfile_pvt.Find_pos will be used to locate the specific
2720 data value in the PL/SQL table.
2721 dbms_sql (Native Oracle db functions that come with every Oracle Apps)
2722 dbms_sql.bind_variable will be used to assign data value to :schedule_id
2723
2724 Let's use the above example:
2725
2726 1. Execute dynamic SQL 1 for headers (A) data
2727 Get value of A.xxx (foreign key to B)
2728
2729 2. bind value A.xxx to variable B.xxx
2730
2731 3. Execute dynamic SQL 2 for lines (B) data
2732 Get value of B.yyy (foreign key to C)
2733
2734 4. bind value B.yyy to variable C.yyy
2735
2736 5. Execute dynamic SQL 3 for line_details (C) data
2737 --------------------------------------------------------------------------*/
2738
2739
2740 xProgress := 'SPSOB-50-1030';
2741 x_header_where := x_header_where ||
2742 ' AND ' ||
2743 p_header_interface ||
2744 '.RUN_ID =' ||
2745 ':l_p_run_id';
2746
2747 ec_debug.pl ( 3, 'x_header_where: ',x_header_where );
2748
2749 xProgress := 'SPSOB-50-1040';
2750 x_item_where := x_item_where ||
2751 ' AND ' ||
2752 p_item_interface ||
2753 '.RUN_ID =' ||
2754 ':l_p_run_id' ||
2755 ' AND ' ||
2756 p_item_interface ||
2757 '.SCHEDULE_ID = :schedule_id' ||
2758 ' ORDER BY ' ||
2759 p_item_interface ||
2760 '.SCHEDULE_ID, ' ||
2761 p_item_interface ||
2762 '.SCHEDULE_ITEM_ID, ' ||
2763 p_item_interface ||
2764 '.ITEM_NUMBER, ' ||
2765 p_item_interface ||
2766 '.SHIP_TO_ORG_CODE';
2767
2768 ec_debug.pl ( 3, 'x_item_where: ',x_item_where );
2769
2770 xProgress := 'SPSOB-50-1050';
2771 x_item_d_where := x_item_d_where ||
2772 ' AND ' ||
2773 p_item_d_interface ||
2774 '.RUN_ID =' ||
2775 ':l_p_run_id' ||
2776 ' AND ' ||
2777 p_item_d_interface ||
2778 '.SCHEDULE_ID = :schedule_id' ||
2779 ' AND ' ||
2780 p_item_d_interface ||
2781 '.SCHEDULE_ITEM_ID = :schedule_item_id' ||
2782 ' ORDER BY ' ||
2783 p_item_d_interface ||
2784 '.SCHEDULE_ID, ' ||
2785 p_item_d_interface ||
2786 '.SCHEDULE_ITEM_DETAIL_SEQUENCE';
2787
2788 ec_debug.pl ( 3, 'x_item_d_where: ',x_item_d_where );
2789 xProgress := 'SPSOB-50-1055';
2790 if (p_transaction_type = 'SSSO') then
2791 x_ship_d_where := x_ship_d_where ||
2792 ' AND ' ||
2793 p_ship_d_interface ||
2794 '.RUN_ID = :l_p_run_id' ||
2795 ' AND ' ||
2796 p_ship_d_interface ||
2797 '.SCHEDULE_ID = :schedule_id' ||
2798 ' AND ' ||
2799 p_ship_d_interface ||
2800 '.SCHEDULE_ITEM_ID = :schedule_item_id' ||
2801 ' AND ' ||
2802 p_ship_d_interface ||
2803 '.SCHEDULE_ITEM_DETAIL_SEQUENCE = :schedule_item_detail_sequence'
2804 || ' ORDER BY ' ||
2805 p_ship_d_interface ||
2806 '.SCHEDULE_ID, ' ||
2807 p_ship_d_interface ||
2808 '.SCHEDULE_ITEM_DETAIL_SEQUENCE,' ||
2809 p_ship_d_interface ||
2810 '.SCHEDULE_SHIP_ID';
2811
2812
2813 ec_debug.pl ( 3, 'x_ship_d_where: ',x_ship_d_where );
2814
2815 end if;
2816
2817 xProgress := 'SPSOB-50-1060';
2818 x_header_select := x_header_select ||
2819 ',' ||
2820 p_header_interface ||
2821 '.ROWID,' ||
2822 x_header_x_interface ||
2823 '.ROWID,' ||
2824 p_header_interface ||
2825 '.SCHEDULE_ID' ;
2826
2827 ec_debug.pl ( 3, 'x_header_select: ',x_header_select );
2828
2829 xProgress := 'SPSOB-50-1070';
2830 x_item_select := x_item_select ||
2831 ',' ||
2832 p_item_interface ||
2833 '.ROWID,' ||
2834 x_item_x_interface ||
2835 '.ROWID,' ||
2836 p_item_interface ||
2837 '.SCHEDULE_ITEM_ID' ;
2838
2839 ec_debug.pl ( 3, 'x_item_select: ',x_item_select );
2840
2841 xProgress := 'SPSOB-50-1080';
2842 x_item_d_select := x_item_d_select ||
2843 ',' ||
2844 p_item_d_interface ||
2845 '.ROWID,' ||
2846 x_item_d_x_interface ||
2847 '.ROWID, ' ||
2848 p_item_d_interface ||
2849 '.SCHEDULE_ITEM_DETAIL_SEQUENCE';
2850
2851 ec_debug.pl ( 3, 'x_item_d_select: ',x_item_d_select );
2852
2853 xProgress := 'SPSOB-50-1085';
2854 if (p_transaction_type = 'SSSO') then
2855 x_ship_d_select := x_ship_d_select ||
2856 ',' ||
2857 p_ship_d_interface ||
2858 '.ROWID,' ||
2859 x_ship_d_x_interface ||
2860 '.ROWID';
2861
2862 ec_debug.pl ( 3, 'x_ship_d_select: ',x_ship_d_select );
2863 end if;
2864
2865 xProgress := 'SPSOB-50-1090';
2866 x_header_select := x_header_select ||
2867 x_header_from ||
2868 x_header_where ||
2869 ' FOR UPDATE';
2870
2871 ec_debug.pl ( 3, 'x_header_select: ',x_header_select);
2872
2873 xProgress := 'SPSOB-50-1100';
2874 x_item_select := x_item_select || x_item_from || x_item_where;
2875 ec_debug.pl ( 3, 'x_item_select: ',x_item_select);
2876
2877 xProgress := 'SPSOB-50-1110';
2878 x_item_d_select := x_item_d_select ||
2879 x_item_d_from ||
2880 x_item_d_where ;
2881
2882 ec_debug.pl ( 3, 'x_item_d_select: ',x_item_d_select );
2883
2884 xProgress := 'SPSOB-50-1115';
2885 if (p_transaction_type = 'SSSO') then
2886 x_ship_d_select := x_ship_d_select ||
2887 x_ship_d_from ||
2888 x_ship_d_where ;
2889 ec_debug.pl ( 3, 'x_ship_d_select: ',x_ship_d_select );
2890 end if;
2891
2892 xProgress := 'SPSOB-50-1120';
2893 x_header_delete1 := 'DELETE FROM ' ||
2894 p_header_interface ||
2895 ' WHERE ROWID = :col_rowid';
2896
2897 ec_debug.pl ( 3, 'x_header_delete1: ',x_header_delete1 );
2898
2899 xProgress := 'SPSOB-50-1130';
2900 x_item_delete1 := 'DELETE FROM ' ||
2901 p_item_interface ||
2902 ' WHERE ROWID = :col_rowid';
2903
2904 ec_debug.pl ( 3, 'x_item_delete1: ',x_item_delete1 );
2905
2906 xProgress := 'SPSOB-50-1140';
2907 x_item_d_delete1 := 'DELETE FROM ' ||
2908 p_item_d_interface ||
2909 ' WHERE ROWID = :col_rowid';
2910
2911 ec_debug.pl ( 3, 'x_item_d_delete1: ',x_item_d_delete1 );
2912
2913 xProgress := 'SPSOB-50-1145';
2914 if (p_transaction_type = 'SSSO') then
2915 x_ship_d_delete1 := 'DELETE FROM ' ||
2916 p_ship_d_interface ||
2917 ' WHERE ROWID = :col_rowid';
2918 ec_debug.pl ( 3, 'x_ship_d_delete1: ',x_ship_d_delete1 );
2919 end if;
2920
2921 xProgress := 'SPSOB-50-1150';
2922 x_header_delete2 := 'DELETE FROM ' ||
2923 x_header_x_interface ||
2924 ' WHERE ROWID = :col_rowid';
2925
2926 ec_debug.pl ( 3, 'x_header_delete2: ',x_header_delete2 );
2927
2928 xProgress := 'SPSOB-50-1160';
2929 x_item_delete2 := 'DELETE FROM ' ||
2930 x_item_x_interface ||
2931 ' WHERE ROWID = :col_rowid';
2932
2933 ec_debug.pl ( 3, 'x_item_delete2: ',x_item_delete2 );
2934
2935 xProgress := 'SPSOB-50-1170';
2936 x_item_d_delete2 := 'DELETE FROM ' ||
2937 x_item_d_x_interface ||
2938 ' WHERE ROWID = :col_rowid';
2939
2940
2941 ec_debug.pl ( 3, 'x_item_d_delete2: ',x_item_d_delete2 );
2942
2943 xProgress := 'SPSOB-50-1175';
2944 if (p_transaction_type = 'SSSO') then
2945 x_ship_d_delete2 := 'DELETE FROM ' ||
2946 x_ship_d_x_interface ||
2947 ' WHERE ROWID = :col_rowid';
2948 end if;
2949
2950 --***************************************************
2951 --*** Get data setup for the dynamic SQL call. **
2952 --*** and **
2953 --*** Open a cursor for each of the SELECT calls **
2954 --***************************************************
2955
2956 xProgress := 'SPSOB-50-1180';
2957 v_header_sel_c := dbms_sql.open_cursor;
2958
2959 xProgress := 'SPSOB-50-1190';
2960 v_item_sel_c := dbms_sql.open_cursor;
2961
2962 xProgress := 'SPSOB-50-1200';
2963 v_item_d_sel_c := dbms_sql.open_cursor;
2964
2965 xProgress := 'SPSOB-50-1205';
2966 if (p_transaction_type = 'SSSO') then
2967 v_ship_d_sel_c := dbms_sql.open_cursor;
2968 end if;
2969 xProgress := 'SPSOB-50-1210';
2970 v_header_del_c1 := dbms_sql.open_cursor;
2971
2972 xProgress := 'SPSOB-50-1220';
2973 v_item_del_c1 := dbms_sql.open_cursor;
2974
2975 xProgress := 'SPSOB-50-1230';
2976 v_item_d_del_c1 := dbms_sql.open_cursor;
2977
2978 xProgress := 'SPSOB-50-1235';
2979 if (p_transaction_type = 'SSSO') then
2980 v_ship_d_del_c1 := dbms_sql.open_cursor;
2981 end if;
2982
2983 xProgress := 'SPSOB-50-1240';
2984 v_header_del_c2 := dbms_sql.open_cursor;
2985
2986 xProgress := 'SPSOB-50-1250';
2987 v_item_del_c2 := dbms_sql.open_cursor;
2988
2989 xProgress := 'SPSOB-50-1260';
2990 v_item_d_del_c2 := dbms_sql.open_cursor;
2991
2992 xProgress := 'SPSOB-50-1265';
2993 if (p_transaction_type = 'SSSO') then
2994 v_ship_d_del_c2 := dbms_sql.open_cursor;
2995 end if;
2996 --******************************************************
2997 --*** Parse each of the SELECT and DELETE statement **
2998 --******************************************************
2999
3000 xProgress := 'SPSOB-50-1270';
3001 BEGIN
3002 dbms_sql.parse ( v_header_sel_c,
3003 x_header_select,
3004 dbms_sql.native );
3005 EXCEPTION
3006 WHEN OTHERS THEN
3007 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3008 x_header_select );
3009 app_exception.raise_exception;
3010 END;
3011
3012 xProgress := 'SPSOB-50-1280';
3013 BEGIN
3014 dbms_sql.parse ( v_item_sel_c,
3015 x_item_select,
3016 dbms_sql.native );
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3020 x_item_select );
3021 app_exception.raise_exception;
3022 END;
3023
3024 xProgress := 'SPSOB-50-1290';
3025 BEGIN
3026 dbms_sql.parse ( v_item_d_sel_c,
3027 x_item_d_select,
3028 dbms_sql.native );
3029 EXCEPTION
3030 WHEN OTHERS THEN
3031 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3032 x_item_d_select );
3033 app_exception.raise_exception;
3034 END;
3035
3036 xProgress := 'SPSOB-50-1295';
3037 if (p_transaction_type = 'SSSO') then
3038 BEGIN
3039 dbms_sql.parse ( v_ship_d_sel_c,
3040 x_ship_d_select,
3041 dbms_sql.native );
3042 EXCEPTION
3043 WHEN OTHERS THEN
3044 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3045 x_ship_d_select );
3046 app_exception.raise_exception;
3047 END;
3048 end if;
3049 xProgress := 'SPSOB-50-1300';
3050 BEGIN
3051 dbms_sql.parse ( v_header_del_c1,
3052 x_header_delete1,
3053 dbms_sql.native );
3054 EXCEPTION
3055 WHEN OTHERS THEN
3056 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3057 x_header_delete1 );
3058 app_exception.raise_exception;
3059 END;
3060
3061 xProgress := 'SPSOB-50-1310';
3062 BEGIN
3063 dbms_sql.parse ( v_item_del_c1,
3064 x_item_delete1,
3065 dbms_sql.native );
3066 EXCEPTION
3067 WHEN OTHERS THEN
3068 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3069 x_item_delete1 );
3070 app_exception.raise_exception;
3071 END;
3072
3073 xProgress := 'SPSOB-50-1320';
3074 BEGIN
3075 dbms_sql.parse ( v_item_d_del_c1,
3076 x_item_d_delete1,
3077 dbms_sql.native );
3078 EXCEPTION
3079 WHEN OTHERS THEN
3080 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3081 x_item_d_delete1 );
3082 app_exception.raise_exception;
3083 END;
3084
3085 xProgress := 'SPSOB-50-1325';
3086 if (p_transaction_type = 'SSSO') then
3087 BEGIN
3088 dbms_sql.parse ( v_ship_d_del_c1,
3089 x_ship_d_delete1,
3090 dbms_sql.native );
3091 EXCEPTION
3092 WHEN OTHERS THEN
3093 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3094 x_ship_d_delete1 );
3095 app_exception.raise_exception;
3096 END;
3097 end if;
3098
3099 xProgress := 'SPSOB-50-1330';
3100 BEGIN
3101 dbms_sql.parse ( v_header_del_c2,
3102 x_header_delete2,
3103 dbms_sql.native );
3104 EXCEPTION
3105 WHEN OTHERS THEN
3106 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3107 x_header_delete2 );
3108 app_exception.raise_exception;
3109 END;
3110
3111 xProgress := 'SPSOB-50-1340';
3112 BEGIN
3113 dbms_sql.parse ( v_item_del_c2,
3114 x_item_delete2,
3115 dbms_sql.native );
3116 EXCEPTION
3117 WHEN OTHERS THEN
3118 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3119 x_item_delete2 );
3120 app_exception.raise_exception;
3121 END;
3122
3123 xProgress := 'SPSOB-50-1350';
3124 BEGIN
3125 dbms_sql.parse ( v_item_d_del_c2,
3126 x_item_d_delete2,
3127 dbms_sql.native );
3128 EXCEPTION
3129 WHEN OTHERS THEN
3130 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3131 x_item_d_delete2 );
3132 app_exception.raise_exception;
3133 END;
3134
3135 xProgress := 'SPSOB-50-1355';
3136 if (p_transaction_type = 'SSSO') then
3137 BEGIN
3138 dbms_sql.parse ( v_ship_d_del_c2,
3139 x_ship_d_delete2,
3140 dbms_sql.native );
3141 EXCEPTION
3142 WHEN OTHERS THEN
3143 ece_error_handling_pvt.print_parse_error ( dbms_sql.last_error_position,
3144 x_ship_d_delete2 );
3145 app_exception.raise_exception;
3146 END;
3147 end if;
3148
3149 -- *************************************************
3150 -- set counter
3151 -- *************************************************
3152
3153 xProgress := 'SPSOB-50-1360';
3154 x_header_count := l_header_tbl.count;
3155 ec_debug.pl ( 3, 'x_header_count: ',x_header_count );
3156
3157 xProgress := 'SPSOB-50-1370';
3158 x_item_count := l_item_tbl.count;
3159 ec_debug.pl ( 3, 'x_item_count: ',x_item_count );
3160
3161 xProgress := 'SPSOB-50-1380';
3162 x_item_d_count := l_item_d_tbl.count;
3163 ec_debug.pl ( 3, 'x_item_d_count: ',x_item_d_count );
3164
3165 xProgress := 'SPSOB-50-1380';
3166 if (p_transaction_type = 'SSSO') then
3167 x_ship_d_count := l_ship_d_tbl.count;
3168 ec_debug.pl ( 3, 'x_ship_d_count: ',x_ship_d_count );
3169 end if;
3170 --******************************************************************
3171 --*** Define data TYPE for every columns in the SELECT statement **
3172 --******************************************************************
3173
3174 xProgress := 'SPSOB-50-1390';
3175
3176 xProgress := 'SPSOB-50-1400';
3177 FOR k IN 1..x_header_count
3178 LOOP
3179
3180 dbms_sql.define_column ( v_header_sel_c,
3181 k,
3182 x_header_select,
3183 ece_flatfile_pvt.G_MaxColWidth );
3184 END LOOP;
3185
3186 --********************************************
3187 --*** Need rowid for delete (Header Level) **
3188 --********************************************
3189
3190 xProgress := 'SPSOB-50-1410';
3191 dbms_sql.define_column_rowid ( v_header_sel_c,
3192 x_header_count + 1,
3193 x_header_rowid);
3194
3195 xProgress := 'SPSOB-50-1420';
3196 dbms_sql.define_column_rowid ( v_header_sel_c,
3197 x_header_count + 2,
3198 x_header_x_rowid);
3199
3200 xProgress := 'SPSOB-50-1430';
3201 dbms_sql.define_column ( v_header_sel_c,
3202 x_header_count + 3,
3203 x_schedule_id);
3204
3205 xProgress := 'SPSOB-50-1440';
3206 FOR k IN 1..x_item_count
3207 LOOP
3208
3209 dbms_sql.define_column ( v_item_sel_c,
3210 k,
3211 x_item_select,
3212 ece_flatfile_pvt.G_MaxColWidth );
3213 END LOOP;
3214
3215
3216 --******************************************
3217 --*** Need rowid for delete (Item Level) **
3218 --*******************************************
3219
3220 xProgress := 'SPSOB-50-1450';
3221 dbms_sql.define_column_rowid ( v_item_sel_c,
3222 x_item_count + 1,
3223 x_item_rowid );
3224
3225 xProgress := 'SPSOB-50-1460';
3226 dbms_sql.define_column_rowid ( v_item_sel_c,
3227 x_item_count + 2,
3228 x_item_x_rowid );
3229
3230 xProgress := 'SPSOB-50-1470';
3231 dbms_sql.define_column ( v_item_sel_c,
3232 x_item_count + 3,
3233 x_schedule_item_id );
3234
3235 xProgress := 'SPSOB-50-1480';
3236 FOR k IN 1..x_item_d_count
3237 LOOP
3238
3239 dbms_sql.define_column (v_item_d_sel_c,
3240 k,
3241 x_item_d_select,
3242 ece_flatfile_pvt.G_MaxColWidth );
3243 END LOOP;
3244
3245
3246 --**************************************************
3247 --*** Need rowid for delete (Item details Level) **
3248 --**************************************************
3249
3250 xProgress := 'SPSOB-50-1490';
3251 dbms_sql.define_column_rowid ( v_item_d_sel_c,
3252 x_item_d_count + 1,
3253 x_item_d_rowid);
3254
3255 xProgress := 'SPSOB-50-1500';
3256 dbms_sql.define_column_rowid ( v_item_d_sel_c,
3257 x_item_d_count + 2,
3258 x_item_d_x_rowid );
3259
3260 xProgress := 'SPSOB-50-1501';
3261 dbms_sql.define_column ( v_item_d_sel_c,
3262 (x_item_d_count+3),
3263 x_sch_item_detail_seq);
3264
3265
3266 if (p_transaction_type = 'SSSO') then
3267 For k IN 1..x_ship_d_count loop
3268 dbms_sql.define_column ( v_ship_d_sel_c,
3269 k,
3270 x_ship_d_select,
3271 ece_flatfile_pvt.G_MaxColWidth);
3272 End Loop;
3273
3274
3275 xProgress := 'SPSOB-50-1502';
3276 dbms_sql.define_column_rowid ( v_ship_d_sel_c,
3277 (x_ship_d_count+1),
3278 x_ship_d_rowid);
3279 xProgress := 'SPSOB-50-1505';
3280 dbms_sql.define_column_rowid ( v_ship_d_sel_c,
3281 (x_ship_d_count+2),
3282 x_ship_d_x_rowid);
3283 end if;
3284
3285
3286 --***************************************************************
3287 --*** The following is custom tailored for this transaction **
3288 --*** It finds the values and uses them in the WHERE clause to **
3289 --*** join tables together. **
3290 --***************************************************************
3291
3292 --**************************************************
3293 --*** To complete the SELECT statement, **
3294 --*** we will need values for the join condition. **
3295 --**************************************************
3296
3297 -- *** These following commented lines are reserved for Rel11
3298
3299 -- **************************************************
3300 -- *** Perform FIND_POS outside of the LOOP!
3301 -- *** This could improve performance.
3302 -- **************************************************
3303
3304 xProgress := 'SPSOB-50-1510';
3305 nTrans_code_pos := ece_flatfile_pvt.POS_OF ( l_header_tbl,
3306 ece_flatfile_pvt.G_Translator_Code );
3307 ec_debug.pl ( 3, 'nTrans_code_pos: ',nTrans_code_pos );
3308
3309 xProgress := 'SPSOB-50-1520';
3310 nHeader_key_pos := ece_flatfile_pvt.POS_OF ( l_header_tbl,
3311 c_header_common_key_name );
3312 ec_debug.pl ( 3, 'nHeader_key_pos: ',nHeader_key_pos );
3313
3314 xProgress := 'SPSOB-50-1530';
3315 nItem_key_pos := ece_flatfile_pvt.POS_OF ( l_item_tbl,
3316 c_item_common_key_name );
3317 ec_debug.pl ( 3, 'nItem_key_pos: ',nItem_key_pos);
3318
3319 xProgress := 'SPSOB-50-1540';
3320 nItem_d_key_pos := ece_flatfile_pvt.POS_OF ( l_item_d_tbl,
3321 c_item_d_common_key_name );
3322 ec_debug.pl ( 3, 'nItem_d_key_pos: ',nItem_d_key_pos );
3323
3324 xProgress := 'SPSOB-50-1545';
3325 if (p_transaction_type = 'SSSO') then
3326 nShip_d_key_pos := ece_flatfile_pvt.POS_OF( l_ship_d_tbl,
3327 c_ship_d_common_key_name );
3328
3329 ec_debug.pl ( 3, 'nShip_d_key_pos: ',nShip_d_key_pos );
3330 end if;
3331
3332 xProgress := 'SPSOB-50-1541';
3333 dbms_sql.bind_variable(v_header_sel_c,'l_p_run_id',p_run_id);
3334
3335 xProgress := 'SPSOB-50-1542';
3336 dbms_sql.bind_variable(v_item_sel_c,'l_p_run_id',p_run_id);
3337
3338 xProgress := 'SPSOB-50-1543';
3339 dbms_sql.bind_variable(v_item_d_sel_c,'l_p_run_id',p_run_id);
3340
3341 xProgress := 'SPSOB-50-1544';
3342 if (p_transaction_type = 'SSSO') then
3343 dbms_sql.bind_variable(v_ship_d_sel_c,'l_p_run_id',p_run_id);
3344 end if;
3345 --**************************************
3346 --*** EXECUTE the SELECT statement **
3347 --**************************************
3348
3349 xProgress := 'SPSOB-50-1550';
3350 x_dummy := dbms_sql.execute(v_header_sel_c);
3351
3352
3353 --***********************************************************************
3354 --*** With data for each HEADER line, populate the ECE_OUTPUT table **
3355 --*** then populate ECE_OUTPUT with data from all ITEMS that belong **
3356 --*** to the HEADER. Then populate ECE_OUTPUT with data from all **
3357 --*** ITEM DETAILS that belongs to the ITEM. **
3358 --***********************************************************************
3359
3360 xProgress := 'SPSOB-50-1560';
3361 WHILE dbms_sql.fetch_rows ( v_header_sel_c ) > 0
3362 LOOP -- Header
3363
3364 --***********************************
3365 --*** store values in pl/sql table **
3366 --***********************************
3367
3368 xProgress := 'SPSOB-50-1570';
3369 FOR i IN 1..x_header_count
3370 LOOP
3371
3372 dbms_sql.column_value ( v_header_sel_c,
3373 i,
3374 l_header_tbl(i).value );
3375
3376 END LOOP;
3377
3378
3379 xProgress := 'SPSOB-50-1580';
3380 dbms_sql.column_value ( v_header_sel_c,
3381 x_header_count + 1,
3382 x_header_rowid );
3383
3384 xProgress := 'SPSOB-50-1590';
3385 dbms_sql.column_value ( v_header_sel_c,
3386 x_header_count + 2,
3387 x_header_x_rowid );
3388
3389
3390 xProgress := 'SPSOB-50-1600';
3391 dbms_sql.column_value ( v_header_sel_c,
3392 x_header_count + 3,
3393 x_schedule_id );
3394
3395 xProgress := 'SPSOB-50-1610';
3396 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),
3397 1, 25),
3398 25);
3399
3400 xProgress := 'SPSOB-50-1620';
3401 c_file_common_key := c_file_common_key ||
3402 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value,' '),
3403 1, 22),
3404 22) ||
3405 RPAD(' ',22) ||
3406 RPAD(' ',22);
3407 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
3408
3409 xProgress := 'SPSOB-50-1630';
3410 ece_flatfile_pvt.write_to_ece_output ( p_transaction_type,
3411 p_communication_method,
3412 p_header_interface,
3413 l_header_tbl,
3414 p_output_width,
3415 p_run_id,
3416 c_file_common_key );
3417
3418 --*************************************************************
3419 --*** With Header data at hand, we can assign values to **
3420 --*** place holders (foreign keys) in v_item_select and **
3421 --*** v_item_detail_Select **
3422 --*************************************************************
3423
3424 --*****************************************
3425 --** set values into binding variables **
3426 --*****************************************
3427
3428 -- These following commented lines are reserved for Rel11
3429
3430 xProgress := 'SPSOB-50-1640';
3431 dbms_sql.bind_variable ( v_item_sel_c,
3432 'SCHEDULE_ID',
3433 x_schedule_id );
3434
3435 xProgress := 'SPSOB-50-1650';
3436 dbms_sql.bind_variable ( v_item_d_sel_c,
3437 'SCHEDULE_ID',
3438 x_schedule_id );
3439
3440 xProgress := 'SPSOB-50-1655';
3441 if (p_transaction_type = 'SSSO') then
3442 dbms_sql.bind_variable ( v_ship_d_sel_c,
3443 'SCHEDULE_ID',
3444 x_schedule_id );
3445 end if;
3446
3447 xProgress := 'SPSOB-50-1660';
3448 x_dummy := dbms_sql.execute ( v_item_sel_c );
3449
3450
3451 --****************************
3452 --** Item loop starts here **
3453 --****************************
3454
3455 xProgress := 'SPSOB-50-1670';
3456 WHILE dbms_sql.fetch_rows ( v_item_sel_c ) > 0
3457 LOOP --- Line
3458
3459 --***********************************
3460 --** store values in pl/sql table **
3461 --************************************
3462
3463 xProgress := 'SPSOB-50-1680';
3464 FOR j IN 1..x_item_count
3465 LOOP
3466
3467 dbms_sql.column_value ( v_item_sel_c,
3468 j,
3469 l_item_tbl(j).value );
3470
3471 END LOOP;
3472
3473
3474 xProgress := 'SPSOB-50-1690';
3475 dbms_sql.column_value ( v_item_sel_c,
3476 x_item_count + 1,
3477 x_item_rowid );
3478
3479 xProgress := 'SPSOB-50-1700';
3480 dbms_sql.column_value ( v_item_sel_c,
3481 x_item_count + 2,
3482 x_item_x_rowid );
3483
3484 xProgress := 'SPSOB-50-1710';
3485 dbms_sql.column_value ( v_item_sel_c,
3486 x_item_count + 3,
3487 x_schedule_item_id );
3488
3489 xProgress := 'SPSOB-50-1720';
3490 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),
3491 1, 25),
3492 25) ||
3493 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value,' '),
3494 1, 22),
3495 22) ||
3496 RPAD(SUBSTRB(NVL(l_item_tbl(nItem_key_pos).value,' '),
3497 1, 22),
3498 22) ||
3499 RPAD(' ',22);
3500 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
3501
3502 xProgress := 'SPSOB-50-1730';
3503 ece_flatfile_pvt.write_to_ece_output ( p_transaction_type,
3504 p_communication_method,
3505 p_item_interface,
3506 l_item_tbl,
3507 p_output_width,
3508 p_run_id,
3509 c_file_common_key );
3510
3511
3512 --***********************************
3513 --** set SCHEDULE_ITEM_ID values **
3514 --***********************************
3515
3516 xProgress := 'SPSOB-50-1740';
3517 dbms_sql.bind_variable ( v_item_d_sel_c,
3518 'SCHEDULE_ITEM_ID',
3519 x_schedule_item_id);
3520
3521 xProgress := 'SPSOB-50-1745';
3522 if (p_transaction_type = 'SSSO') then
3523 dbms_sql.bind_variable ( v_ship_d_sel_c,
3524 'SCHEDULE_ITEM_ID',
3525 x_schedule_item_id);
3526 end if;
3527
3528 xProgress := 'SPSOB-50-1750';
3529 x_dummy := dbms_sql.execute ( v_item_d_sel_c );
3530
3531
3532 --***********************************
3533 --** item detail loop starts here **
3534 --***********************************
3535
3536 xProgress := 'SPSOB-50-1760';
3537 WHILE dbms_sql.fetch_rows ( v_item_d_sel_c ) > 0
3538 LOOP --- Line Detail
3539
3540
3541 --************************************
3542 --** store values in pl/sql table **
3543 --************************************
3544
3545 xProgress := 'SPSOB-50-1770';
3546 FOR k IN 1..x_item_d_count
3547 LOOP
3548
3549 dbms_sql.column_value ( v_item_d_sel_c,
3550 k,
3551 l_item_d_tbl(k).value );
3552
3553 END LOOP;
3554
3555
3556 xProgress := 'SPSOB-50-1780';
3557 dbms_sql.column_value ( v_item_d_sel_c,
3558 x_item_d_count + 1,
3559 x_item_d_rowid );
3560
3561 xProgress := 'SPSOB-50-1790';
3562 dbms_sql.column_value ( v_item_d_sel_c,
3563 x_item_d_count + 2,
3564 x_item_d_x_rowid );
3565
3566 xProgress := 'SPSOB-50-1795';
3567 dbms_sql.column_value(v_item_d_sel_c,
3568 x_item_d_count+3,
3569 x_sch_item_detail_seq);
3570
3571 xProgress := 'SPSOB-50-1800';
3572 c_file_common_key := RPAD(SUBSTRB(NVL(l_header_tbl(nTrans_code_pos).value,' '),
3573 1, 25),
3574 25) ||
3575 RPAD(SUBSTRB(NVL(l_header_tbl(nHeader_key_pos).value,' '),
3576 1, 22),
3577 22) ||
3578 RPAD(SUBSTRB(NVL(l_item_tbl(nItem_key_pos).value,' '),
3579 1, 22),
3580 22) ||
3581 RPAD(SUBSTRB(NVL(l_item_d_tbl(nItem_d_key_pos).value,' '),
3582 1, 22),
3583 22);
3584 ec_debug.pl ( 3, 'c_file_common_key: ',c_file_common_key );
3585
3586 xProgress := 'SPSOB-50-1810';
3587 ece_flatfile_pvt.write_to_ece_output ( p_transaction_type,
3588 p_communication_method,
3589 p_item_d_interface,
3590 l_item_d_tbl,
3591 p_output_width,
3592 p_run_id,
3593 c_file_common_key );
3594
3595 --***********************************
3596 --** set SCHEDULE_ITEM_DETAIL_SEQUENCE values **
3597 --***********************************
3598
3599 xProgress := 'SPSOB-50-1820';
3600 if (p_transaction_type = 'SSSO') then
3601 dbms_sql.bind_variable(v_ship_d_sel_c, 'SCHEDULE_ITEM_DETAIL_SEQUENCE',
3602 x_sch_item_detail_seq);
3603
3604 xProgress := 'SPSOB-50-1822';
3605 x_dummy := dbms_sql.execute(v_ship_d_sel_c);
3606 --***********************************
3607 --** ship detail loop starts here **
3608 --***********************************
3609
3610 xProgress := 'SPSOB-50-1823';
3611 WHILE dbms_sql.fetch_rows(v_ship_d_sel_c) > 0 LOOP --- Ship Detail
3612
3613 xProgress := 'SPSOB-50-1825';
3614 for k in 1..x_ship_d_count loop
3615
3616 dbms_sql.column_value(v_ship_d_sel_c, k, l_ship_d_tbl(k).value);
3617
3618 end loop;
3619 xProgress := 'SPSOB-50-1830';
3620 dbms_sql.column_value(v_ship_d_sel_c, x_ship_d_count+1,
3621 x_ship_d_rowid);
3622 xProgress := 'SPSOB-50-1835';
3623 dbms_sql.column_value(v_ship_d_sel_c, x_ship_d_count+2,
3624 x_ship_d_x_rowid);
3625
3626 xProgress := 'SPSOB-50-1840';
3627 c_file_common_key := rpad(substr(nvl(l_header_tbl(nTrans_code_pos).value,' '), 1, 25), 25)
3628 || rpad(substr(nvl(l_header_tbl(nHeader_key_pos).value,' '), 1, 22), 22)
3629 || rpad(substr(nvl(l_item_tbl(nItem_key_pos).value,' '), 1, 22), 22)
3630 || rpad(substr(nvl(l_item_d_tbl(nItem_d_key_pos).value,' '), 1, 22), 22);
3631
3632 xProgress := 'SPSOB-50-1845';
3633 ece_flatfile_pvt.write_to_ece_output(
3634 p_transaction_type, p_communication_method, p_ship_d_interface,
3635 l_ship_d_tbl, p_output_width, p_run_id, c_file_common_key);
3636
3637 xProgress := 'SPSOB-50-1850';
3638 dbms_sql.bind_variable(v_ship_d_del_c1, 'col_rowid',x_ship_d_rowid);
3639
3640 xProgress := 'SPSOB-50-1855';
3641 dbms_sql.bind_variable(v_ship_d_del_c2, 'col_rowid',
3642 x_ship_d_x_rowid);
3643 xProgress := 'SPSOB-50-1856';
3644 x_dummy := dbms_sql.execute(v_ship_d_del_c1);
3645
3646 xProgress := 'SPSOB-50-1857';
3647 x_dummy := dbms_sql.execute(v_ship_d_del_c2);
3648
3649 END LOOP;
3650 end if;
3651
3652 --********************************
3653 --** Ship detail loop ends here **
3654 --********************************
3655 xProgress := 'SPSOB-50-1820';
3656 dbms_sql.bind_variable ( v_item_d_del_c1,
3657 'col_rowid',
3658 x_item_d_rowid );
3659
3660 xProgress := 'SPSOB-50-1830';
3661 dbms_sql.bind_variable ( v_item_d_del_c2,
3662 'col_rowid',
3663 x_item_d_x_rowid );
3664
3665 xProgress := 'SPSOB-50-1840';
3666 x_dummy := dbms_sql.execute ( v_item_d_del_c1 );
3667
3668 xProgress := 'SPSOB-50-1850';
3669 x_dummy := dbms_sql.execute ( v_item_d_del_c2 );
3670
3671 END LOOP;
3672
3673 --********************************
3674 --** item detail loop ends here **
3675 --********************************
3676
3677
3678 xProgress := 'SPSOB-50-1860';
3679 dbms_sql.bind_variable ( v_item_del_c1,
3680 'col_rowid',
3681 x_item_rowid );
3682
3683 xProgress := 'SPSOB-50-1870';
3684 dbms_sql.bind_variable ( v_item_del_c2,
3685 'col_rowid',
3686 x_item_x_rowid );
3687
3688 xProgress := 'SPSOB-50-1880';
3689 x_dummy := dbms_sql.execute ( v_item_del_c1 );
3690
3691 xProgress := 'SPSOB-50-1890';
3692 x_dummy := dbms_sql.execute ( v_item_del_c2 );
3693
3694 END LOOP;
3695
3696 --***************************
3697 --** item loop ends here **
3698 --***************************
3699
3700
3701 xProgress := 'SPSOB-50-1900';
3702 dbms_sql.bind_variable ( v_header_del_c1,
3703 'col_rowid',
3704 x_header_rowid );
3705
3706 xProgress := 'SPSOB-50-1910';
3707 dbms_sql.bind_variable ( v_header_del_c2,
3708 'col_rowid',
3709 x_header_x_rowid );
3710
3711 xProgress := 'SPSOB-50-1920';
3712 x_dummy := dbms_sql.execute ( v_header_del_c1 );
3713
3714 xProgress := 'SPSOB-50-1930';
3715 x_dummy := dbms_sql.execute ( v_header_del_c2 );
3716
3717 END LOOP;
3718
3719 --*****************************
3720 --** header loop ends here **
3721 --*****************************
3722
3723 xProgress := 'SPSOB-50-1940';
3724 dbms_sql.close_cursor ( v_header_sel_c );
3725
3726 xProgress := 'SPSOB-50-1950';
3727 dbms_sql.close_cursor ( v_item_sel_c );
3728
3729 xProgress := 'SPSOB-50-1960';
3730 dbms_sql.close_cursor ( v_item_d_sel_c );
3731
3732 xProgress := 'SPSOB-50-1966';
3733 if (p_transaction_type = 'SSSO') then
3734 dbms_sql.close_cursor ( v_ship_d_sel_c );
3735 end if;
3736
3737 xProgress := 'SPSOB-50-1970';
3738 dbms_sql.close_cursor ( v_header_del_c1 );
3739
3740 xProgress := 'SPSOB-50-1980';
3741 dbms_sql.close_cursor ( v_item_del_c1 );
3742
3743 xProgress := 'SPSOB-50-1990';
3744 dbms_sql.close_cursor ( v_item_d_del_c1 );
3745
3746 xProgress := 'SPSOB-50-1990';
3747 if (p_transaction_type = 'SSSO') then
3748 dbms_sql.close_cursor ( v_ship_d_del_c1 );
3749 end if;
3750
3751 ec_debug.pop ( 'ece_spso_trans1.Put_Data_To_Output_Table' );
3752
3753 EXCEPTION
3754 WHEN OTHERS THEN
3755
3756 ec_debug.pl ( 0,
3757 'EC',
3758 'ECE_PROGRAM_ERROR',
3759 'PROGRESS_LEVEL',
3760 xProgress );
3761
3762 ec_debug.pl ( 0,
3763 'EC',
3764 'ECE_ERROR_MESSAGE',
3765 'ERROR_MESSAGE',
3766 SQLERRM );
3767
3768 app_exception.raise_exception;
3769
3770 END Put_Data_To_Output_Table; -- end of procedure
3771
3772 END ECE_SPSO_TRANS1;
3773