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