[Home] [Help]
PACKAGE BODY: APPS.ECE_SPSO_TRANS2
Source
1 PACKAGE BODY ECE_SPSO_TRANS2 AS
2 -- $Header: ECSPSO2B.pls 120.2 2005/06/30 11:24:11 appldev ship $
3 /* Bug 2064311
4 Added the parameter batch id to the proceudre
5 POPULATE_SUPPLIER_SCHED_API2 and cursor sch_hdr_c
6 to improve performance
7 */
8
9
10 PROCEDURE POPULATE_SUPPLIER_SCHED_API2
11 (
12 p_communication_method IN VARCHAR2, -- EDI
13 p_transaction_type IN VARCHAR2, -- plan SPSO, ship SSSO
14 p_document_type IN VARCHAR2, -- plan SPS, ship SSS
15 p_run_id IN NUMBER,
16 p_schedule_id IN INTEGER default 0,
17 p_batch_id IN NUMBER ) -- Bug 2064311
18 IS
19 xProgress VARCHAR2(30) := NULL;
20 cOutput_path varchar2(120);
21 l_transaction_number NUMBER; -- Bug 1742567
22 exclude_zero_schedule_from_ff VARCHAR2(1) := 'N'; -- 2944455
23 /****************************
24 ** SELECT HEADER **
25 ****************************/
26
27 CURSOR sch_hdr_c IS
28 SELECT
29 CSH.SCHEDULE_ID SCHEDULE_ID,
30 CSH.SCHEDULE_TYPE SCHEDULE_TYPE,
31 CSH.BATCH_ID BATCH_ID --Bug 2064311
32 FROM
33 ECE_TP_DETAILS ETD,
34 PO_VENDOR_SITES PVS,
35 CHV_SCHEDULE_HEADERS CSH
36 WHERE
37 CSH.SCHEDULE_STATUS = 'CONFIRMED'
38 AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
39 WHERE CIO.SCHEDULE_ID = CSH.SCHEDULE_ID)
40 AND ETD.EDI_FLAG = 'Y' -- EDI
41 AND ETD.DOCUMENT_ID = P_TRANSACTION_TYPE --ship SSSO,plan SPSO
42 AND P_TRANSACTION_TYPE = DECODE(SCHEDULE_TYPE, 'SHIP_SCHEDULE',
43 'SSSO', 'SPSO')
44 AND ((CSH.SCHEDULE_ID = P_SCHEDULE_ID
45 AND P_SCHEDULE_ID <> 0)
46 OR (P_SCHEDULE_ID = 0
47 AND NVL(CSH.COMMUNICATION_CODE,'NONE') IN ('BOTH','EDI')))
48 AND CSH.BATCH_ID = decode(P_BATCH_ID,0,CSH.BATCH_ID,P_BATCH_ID) --Bug 2064311
49 AND CSH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
50 AND PVS.TP_HEADER_ID = ETD.TP_HEADER_ID
51 AND ETD.DOCUMENT_ID = DECODE(SCHEDULE_TYPE, 'SHIP_SCHEDULE',
52 'SSSO', 'SPSO')
53 FOR UPDATE;
54 /*
55 AND ECC.DIRECTION = 'O' -- outbound
56 AND ECC.ENABLED_FLAG = 'Y' -- outbound transmission enabled
57 AND ECC.DOCUMENT_ID = P_TRANSACTION_TYPE --ship SSSO,plan SPSO
58 AND ECC.TRANSMISSION_METHOD = P_COMMUNICATION_METHOD -- EDI
59 AND ECC.ENTITY_TYPE = 'SUPPLIER' -- destination
60 AND CSH.VENDOR_ID = ECC.ENTITY_ID
61 AND CSH.VENDOR_SITE_ID = ECC.ENTITY_SITE_ID
62 */
63
64
65
66 BEGIN -- begin header block
67
68 EC_DEBUG.PUSH('ECE_SPSO_TRANS2.populate_supplier_sched_api2');
69 EC_DEBUG.PL(3, 'p_communication_method: ', p_communication_method);
70 EC_DEBUG.PL(3, 'p_transaction_type: ',p_transaction_type);
71 EC_DEBUG.PL(3, 'p_document_type: ',p_document_type);
72 EC_DEBUG.PL(3, 'p_run_id: ',p_run_id);
73 EC_DEBUG.PL(3, 'p_schedule_id: ',p_schedule_id);
74
75 -- Retreive the system profile option ECE_OUT_FILE_PATH. This will
76 -- be the directory where the output file will be written.
77 -- NOTE: THIS DIRECTORY MUST BE SPECIFIED IN THE PARAMETER utl_file_dir IN
78 -- THE INIT.ORA FILE. Refer to the Oracle7 documentation for more information
79 -- on the package UTL_FILE.
80
81 xProgress := 'SPSO2B-10-0100';
82 fnd_profile.get('ECE_OUT_FILE_PATH',
83 cOutput_path);
84 EC_DEBUG.PL(3, 'cOutput_path: ',cOutput_path);
85
86
87 -- This sets up temporary files for FND_FILE to write.
88 -- These are only used if the program is run from SQL*Plus.
89 xProgress := 'SPSO2B-10-0110';
90 fnd_file.put_names('spso_log.tmp','spso_out.tmp',cOutput_path);
91
92 <<header>>
93 xProgress := 'SPSO2B-10-1000';
94 FOR rec_hdr IN sch_hdr_c LOOP
95
96 /**************************
97 ** SELECT ITEM **
98 **************************/
99
100 DECLARE
101 x_item_detail_sequence NUMBER :=0;
102 x_item_order NUMBER;
103 x_item_detail NUMBER;
104 CURSOR sch_item_c IS
105 SELECT
106 CSI.SCHEDULE_ID SCHEDULE_ID,
107 CSI.SCHEDULE_ITEM_ID SCHEDULE_ITEM_ID
108 FROM
109 CHV_SCHEDULE_ITEMS CSI
110 WHERE
111 CSI.SCHEDULE_ID = REC_HDR.SCHEDULE_ID
112 AND EXISTS (SELECT 1 FROM CHV_ITEM_ORDERS CIO
113 WHERE CIO.SCHEDULE_ID = CSI.SCHEDULE_ID);
114
115 BEGIN -- begin item block
116 <<item>>
117 xProgress := 'SPSO2B-10-1020';
118 Select count(*)
119 Into x_item_order
120 From chv_item_orders
121 Where schedule_id = rec_hdr.schedule_id;
122 -- 2944455
123 fnd_profile.get('ECE_SPSO_EXCLUDE_ZERO_SCHEDULE_FROM_FF',exclude_zero_schedule_from_ff);
124 If NVL(exclude_zero_schedule_from_ff,'N')<>'Y' then
125 exclude_zero_schedule_from_ff := 'N';
126 End If;
127 If ((exclude_zero_schedule_from_ff = 'N') OR
128 (x_item_order > 0)) Then
129 FOR rec_item IN sch_item_c LOOP
130
131
132 /*********************************************************
133 ** select the last sequence number assigned to **
134 ** the detail record of the same schedule item id. **
135 *********************************************************/
136
137 xProgress := 'SPSO2B-10-1030';
138 Select count(schedule_id)
139 Into x_item_detail
140 From chv_item_orders
141 Where schedule_id = rec_hdr.schedule_id
142 And schedule_item_id = rec_item.schedule_item_id;
143 -- 2944455
144 IF ((exclude_zero_schedule_from_ff = 'N') OR
145 (x_item_order > 0 AND x_item_detail > 0)) Then
146
147 BEGIN
148 SELECT max(schedule_item_detail_sequence)
149 INTO x_item_detail_sequence
150 FROM ece_spso_item_det
151 WHERE schedule_id = rec_item.schedule_id
152 AND schedule_item_id= rec_item.schedule_item_id;
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 x_item_detail_sequence := 0;
156 END; -- select max sequence block
157 EC_DEBUG.PL(3, 'x_item_detail_sequence: ',x_item_detail_sequence);
158
159 /**************************************
160 ** SELECT AND INSERT ITEM DETAIL **
161 **************************************/
162
163 DECLARE
164
165 TYPE tbl_hz_type IS TABLE OF VARCHAR2(25)
166 INDEX BY BINARY_INTEGER;
167 tbl_desc tbl_hz_type;
168 tbl_start tbl_hz_type;
169 tbl_end tbl_hz_type;
170 tbl_for tbl_hz_type;
171 tbl_rel tbl_hz_type;
172 tbl_tot tbl_hz_type;
173 rec_hz chv_horizontal_schedules%ROWTYPE;
174
175 x_max_col number := 60;
176 x_min_col number := 1;
177 x_detail_category varchar2(25);
178 x_bucket_descriptor varchar2(25);
179
180 BEGIN -- begin item detail block
181
182 BEGIN -- begin select detail block
183
184
185 /****************
186 ** Description **
187 ****************/
188
189 -- Get BUCET_DESCRIPTION from CHV_HORIZONTAL_SCHEDULES
190
191 xProgress := 'SPSO2B-10-1040';
192
193 --DBMS_OUTPUT.PUT_LINE('LN1 '||sqlcode);
194 SELECT *
195 INTO rec_hz
196 FROM chv_horizontal_schedules
197 WHERE schedule_item_id = rec_item.schedule_item_id
198 AND schedule_id = rec_item.schedule_id
199 AND row_type = 'BUCKET_DESCRIPTOR';
200
201
202 xProgress := 'SPSO2B-10-1050';
203 -- Copy BUCKET_DESCRIPTOR into PL/SQL table
204
205 tbl_desc(1) := rec_hz.column1;
206 tbl_desc(2) := rec_hz.column2;
207 tbl_desc(3) := rec_hz.column3;
208 tbl_desc(4) := rec_hz.column4;
209 tbl_desc(5) := rec_hz.column5;
210 tbl_desc(6) := rec_hz.column6;
211 tbl_desc(7) := rec_hz.column7;
212 tbl_desc(8) := rec_hz.column8;
213 tbl_desc(9) := rec_hz.column9;
214 tbl_desc(10) := rec_hz.column10;
215 tbl_desc(11) := rec_hz.column11;
216 tbl_desc(12) := rec_hz.column12;
217 tbl_desc(13) := rec_hz.column13;
218 tbl_desc(14) := rec_hz.column14;
219 tbl_desc(15) := rec_hz.column15;
220 tbl_desc(16) := rec_hz.column16;
221 tbl_desc(17) := rec_hz.column17;
222 tbl_desc(18) := rec_hz.column18;
223 tbl_desc(19) := rec_hz.column19;
224 tbl_desc(20) := rec_hz.column20;
225 tbl_desc(21) := rec_hz.column21;
226 tbl_desc(22) := rec_hz.column22;
227 tbl_desc(23) := rec_hz.column23;
228 tbl_desc(24) := rec_hz.column24;
229 tbl_desc(25) := rec_hz.column25;
230 tbl_desc(26) := rec_hz.column26;
231 tbl_desc(27) := rec_hz.column27;
232 tbl_desc(28) := rec_hz.column28;
233 tbl_desc(29) := rec_hz.column29;
234 tbl_desc(30) := rec_hz.column30;
235 tbl_desc(31) := rec_hz.column31;
236 tbl_desc(32) := rec_hz.column32;
237 tbl_desc(33) := rec_hz.column33;
238 tbl_desc(34) := rec_hz.column34;
239 tbl_desc(35) := rec_hz.column35;
240 tbl_desc(36) := rec_hz.column36;
241 tbl_desc(37) := rec_hz.column37;
242 tbl_desc(38) := rec_hz.column38;
243 tbl_desc(39) := rec_hz.column39;
244 tbl_desc(40) := rec_hz.column40;
245 tbl_desc(41) := rec_hz.column41;
246 tbl_desc(42) := rec_hz.column42;
247 tbl_desc(43) := rec_hz.column43;
248 tbl_desc(44) := rec_hz.column44;
249 tbl_desc(45) := rec_hz.column45;
250 tbl_desc(46) := rec_hz.column46;
251 tbl_desc(47) := rec_hz.column47;
252 tbl_desc(48) := rec_hz.column48;
253 tbl_desc(49) := rec_hz.column49;
254 tbl_desc(50) := rec_hz.column50;
255 tbl_desc(51) := rec_hz.column51;
256 tbl_desc(52) := rec_hz.column52;
257 tbl_desc(53) := rec_hz.column53;
258 tbl_desc(54) := rec_hz.column54;
259 tbl_desc(55) := rec_hz.column55;
260 tbl_desc(56) := rec_hz.column56;
261 tbl_desc(57) := rec_hz.column57;
262 tbl_desc(58) := rec_hz.column58;
263 tbl_desc(59) := rec_hz.column59;
264 tbl_desc(60) := rec_hz.column60;
265
266 EC_DEBUG.PL(3, 'tbl_desc(1): ',tbl_desc(1));
267 EC_DEBUG.PL(3, 'tbl_desc(2): ',tbl_desc(2));
268 EC_DEBUG.PL(3, 'tbl_desc(3): ',tbl_desc(3));
269 EC_DEBUG.PL(3, 'tbl_desc(4): ',tbl_desc(4));
270 EC_DEBUG.PL(3, 'tbl_desc(5): ',tbl_desc(5));
271 EC_DEBUG.PL(3, 'tbl_desc(6): ',tbl_desc(6));
272 EC_DEBUG.PL(3, 'tbl_desc(7): ',tbl_desc(7));
273 EC_DEBUG.PL(3, 'tbl_desc(8): ',tbl_desc(8));
274 EC_DEBUG.PL(3, 'tbl_desc(9): ',tbl_desc(9));
275 EC_DEBUG.PL(3, 'tbl_desc(10): ',tbl_desc(10));
276 EC_DEBUG.PL(3, 'tbl_desc(11): ',tbl_desc(11));
277 EC_DEBUG.PL(3, 'tbl_desc(12): ',tbl_desc(12));
278 EC_DEBUG.PL(3, 'tbl_desc(13): ',tbl_desc(13));
279 EC_DEBUG.PL(3, 'tbl_desc(14): ',tbl_desc(14));
280 EC_DEBUG.PL(3, 'tbl_desc(15): ',tbl_desc(15));
281 EC_DEBUG.PL(3, 'tbl_desc(16): ',tbl_desc(16));
282 EC_DEBUG.PL(3, 'tbl_desc(17): ',tbl_desc(17));
283 EC_DEBUG.PL(3, 'tbl_desc(18): ',tbl_desc(18));
284 EC_DEBUG.PL(3, 'tbl_desc(19): ',tbl_desc(19));
285 EC_DEBUG.PL(3, 'tbl_desc(20): ',tbl_desc(20));
286 EC_DEBUG.PL(3, 'tbl_desc(21): ',tbl_desc(21));
287 EC_DEBUG.PL(3, 'tbl_desc(22): ',tbl_desc(22));
288 EC_DEBUG.PL(3, 'tbl_desc(23): ',tbl_desc(23));
289 EC_DEBUG.PL(3, 'tbl_desc(24): ',tbl_desc(24));
290 EC_DEBUG.PL(3, 'tbl_desc(25): ',tbl_desc(25));
291 EC_DEBUG.PL(3, 'tbl_desc(26): ',tbl_desc(26));
292 EC_DEBUG.PL(3, 'tbl_desc(27): ',tbl_desc(27));
293 EC_DEBUG.PL(3, 'tbl_desc(28): ',tbl_desc(28));
294 EC_DEBUG.PL(3, 'tbl_desc(29): ',tbl_desc(29));
295 EC_DEBUG.PL(3, 'tbl_desc(30): ',tbl_desc(30));
296 EC_DEBUG.PL(3, 'tbl_desc(31): ',tbl_desc(31));
297 EC_DEBUG.PL(3, 'tbl_desc(32): ',tbl_desc(32));
298 EC_DEBUG.PL(3, 'tbl_desc(33): ',tbl_desc(33));
299 EC_DEBUG.PL(3, 'tbl_desc(34): ',tbl_desc(34));
300 EC_DEBUG.PL(3, 'tbl_desc(35): ',tbl_desc(35));
301 EC_DEBUG.PL(3, 'tbl_desc(36): ',tbl_desc(36));
302 EC_DEBUG.PL(3, 'tbl_desc(37): ',tbl_desc(37));
303 EC_DEBUG.PL(3, 'tbl_desc(38): ',tbl_desc(38));
304 EC_DEBUG.PL(3, 'tbl_desc(39): ',tbl_desc(39));
305 EC_DEBUG.PL(3, 'tbl_desc(40): ',tbl_desc(40));
306 EC_DEBUG.PL(3, 'tbl_desc(41): ',tbl_desc(41));
307 EC_DEBUG.PL(3, 'tbl_desc(42): ',tbl_desc(42));
308 EC_DEBUG.PL(3, 'tbl_desc(43): ',tbl_desc(43));
309 EC_DEBUG.PL(3, 'tbl_desc(44): ',tbl_desc(44));
310 EC_DEBUG.PL(3, 'tbl_desc(45): ',tbl_desc(45));
311 EC_DEBUG.PL(3, 'tbl_desc(46): ',tbl_desc(46));
312 EC_DEBUG.PL(3, 'tbl_desc(47): ',tbl_desc(47));
313 EC_DEBUG.PL(3, 'tbl_desc(48): ',tbl_desc(48));
314 EC_DEBUG.PL(3, 'tbl_desc(49): ',tbl_desc(49));
315 EC_DEBUG.PL(3, 'tbl_desc(50): ',tbl_desc(50));
316 EC_DEBUG.PL(3, 'tbl_desc(51): ',tbl_desc(51));
317 EC_DEBUG.PL(3, 'tbl_desc(52): ',tbl_desc(52));
318 EC_DEBUG.PL(3, 'tbl_desc(53): ',tbl_desc(53));
319 EC_DEBUG.PL(3, 'tbl_desc(54): ',tbl_desc(54));
320 EC_DEBUG.PL(3, 'tbl_desc(55): ',tbl_desc(55));
321 EC_DEBUG.PL(3, 'tbl_desc(56): ',tbl_desc(56));
322 EC_DEBUG.PL(3, 'tbl_desc(57): ',tbl_desc(57));
323 EC_DEBUG.PL(3, 'tbl_desc(58): ',tbl_desc(58));
324 EC_DEBUG.PL(3, 'tbl_desc(59): ',tbl_desc(59));
325 EC_DEBUG.PL(3, 'tbl_desc(60): ',tbl_desc(60));
326 /*****************
327 ** Start Date **
328 *****************/
329
330 -- Get START_DATE from CHV_HORIZONTAL_SCHEDULES
331
332 xProgress := 'SPSO2B-10-1060';
333
334 BEGIN -- START DATE select block
335 xProgress := 'SPSO2B-10-1070';
336 --DBMS_OUTPUT.PUT_LINE('LN2 '||sqlcode);
337 SELECT *
338 INTO rec_hz
339 FROM chv_horizontal_schedules
340 WHERE schedule_item_id = rec_item.schedule_item_id
341 AND schedule_id = rec_item.schedule_id
342 AND row_type = 'BUCKET_START_DATE';
343
344
345 xProgress := 'SPSO2B-10-1080';
346 -- Copy BUCKET_START_DATE into PL/SQL table
347
348 tbl_start(1) := rec_hz.column1;
349 tbl_start(2) := rec_hz.column2;
350 tbl_start(3) := rec_hz.column3;
351 tbl_start(4) := rec_hz.column4;
352 tbl_start(5) := rec_hz.column5;
353 tbl_start(6) := rec_hz.column6;
354 tbl_start(7) := rec_hz.column7;
355 tbl_start(8) := rec_hz.column8;
356 tbl_start(9) := rec_hz.column9;
357 tbl_start(10) := rec_hz.column10;
358 tbl_start(11) := rec_hz.column11;
359 tbl_start(12) := rec_hz.column12;
360 tbl_start(13) := rec_hz.column13;
361 tbl_start(14) := rec_hz.column14;
362 tbl_start(15) := rec_hz.column15;
363 tbl_start(16) := rec_hz.column16;
364 tbl_start(17) := rec_hz.column17;
365 tbl_start(18) := rec_hz.column18;
366 tbl_start(19) := rec_hz.column19;
367 tbl_start(20) := rec_hz.column20;
368 tbl_start(21) := rec_hz.column21;
369 tbl_start(22) := rec_hz.column22;
370 tbl_start(23) := rec_hz.column23;
371 tbl_start(24) := rec_hz.column24;
372 tbl_start(25) := rec_hz.column25;
373 tbl_start(26) := rec_hz.column26;
374 tbl_start(27) := rec_hz.column27;
375 tbl_start(28) := rec_hz.column28;
376 tbl_start(29) := rec_hz.column29;
377 tbl_start(30) := rec_hz.column30;
378 tbl_start(31) := rec_hz.column31;
379 tbl_start(32) := rec_hz.column32;
380 tbl_start(33) := rec_hz.column33;
381 tbl_start(34) := rec_hz.column34;
382 tbl_start(35) := rec_hz.column35;
383 tbl_start(36) := rec_hz.column36;
384 tbl_start(37) := rec_hz.column37;
385 tbl_start(38) := rec_hz.column38;
386 tbl_start(39) := rec_hz.column39;
387 tbl_start(40) := rec_hz.column40;
388 tbl_start(41) := rec_hz.column41;
389 tbl_start(42) := rec_hz.column42;
390 tbl_start(43) := rec_hz.column43;
391 tbl_start(44) := rec_hz.column44;
392 tbl_start(45) := rec_hz.column45;
393 tbl_start(46) := rec_hz.column46;
394 tbl_start(47) := rec_hz.column47;
395 tbl_start(48) := rec_hz.column48;
396 tbl_start(49) := rec_hz.column49;
397 tbl_start(50) := rec_hz.column50;
398 tbl_start(51) := rec_hz.column51;
399 tbl_start(52) := rec_hz.column52;
400 tbl_start(53) := rec_hz.column53;
401 tbl_start(54) := rec_hz.column54;
402 tbl_start(55) := rec_hz.column55;
403 tbl_start(56) := rec_hz.column56;
404 tbl_start(57) := rec_hz.column57;
405 tbl_start(58) := rec_hz.column58;
406 tbl_start(59) := rec_hz.column59;
407 tbl_start(60) := rec_hz.column60;
408
409 EC_DEBUG.PL(3, 'tbl_start(1): ',tbl_start(1));
410 EC_DEBUG.PL(3, 'tbl_start(2): ',tbl_start(2));
411 EC_DEBUG.PL(3, 'tbl_start(3): ',tbl_start(3));
412 EC_DEBUG.PL(3, 'tbl_start(4): ',tbl_start(4));
413 EC_DEBUG.PL(3, 'tbl_start(5): ',tbl_start(5));
414 EC_DEBUG.PL(3, 'tbl_start(6): ',tbl_start(6));
415 EC_DEBUG.PL(3, 'tbl_start(7): ',tbl_start(7));
416 EC_DEBUG.PL(3, 'tbl_start(8): ',tbl_start(8));
417 EC_DEBUG.PL(3, 'tbl_start(9): ',tbl_start(9));
418 EC_DEBUG.PL(3, 'tbl_start(10): ',tbl_start(10));
419 EC_DEBUG.PL(3, 'tbl_start(11): ',tbl_start(11));
420 EC_DEBUG.PL(3, 'tbl_start(12): ',tbl_start(12));
421 EC_DEBUG.PL(3, 'tbl_start(13): ',tbl_start(13));
422 EC_DEBUG.PL(3, 'tbl_start(14): ',tbl_start(14));
423 EC_DEBUG.PL(3, 'tbl_start(15): ',tbl_start(15));
424 EC_DEBUG.PL(3, 'tbl_start(16): ',tbl_start(16));
425 EC_DEBUG.PL(3, 'tbl_start(17): ',tbl_start(17));
426 EC_DEBUG.PL(3, 'tbl_start(18): ',tbl_start(18));
427 EC_DEBUG.PL(3, 'tbl_start(19): ',tbl_start(19));
428 EC_DEBUG.PL(3, 'tbl_start(20): ',tbl_start(20));
429 EC_DEBUG.PL(3, 'tbl_start(21): ',tbl_start(21));
430 EC_DEBUG.PL(3, 'tbl_start(22): ',tbl_start(22));
431 EC_DEBUG.PL(3, 'tbl_start(23): ',tbl_start(23));
432 EC_DEBUG.PL(3, 'tbl_start(24): ',tbl_start(24));
433 EC_DEBUG.PL(3, 'tbl_start(25): ',tbl_start(25));
434 EC_DEBUG.PL(3, 'tbl_start(26): ',tbl_start(26));
435 EC_DEBUG.PL(3, 'tbl_start(27): ',tbl_start(27));
436 EC_DEBUG.PL(3, 'tbl_start(28): ',tbl_start(28));
437 EC_DEBUG.PL(3, 'tbl_start(29): ',tbl_start(29));
438 EC_DEBUG.PL(3, 'tbl_start(30): ',tbl_start(30));
439 EC_DEBUG.PL(3, 'tbl_start(31): ',tbl_start(31));
440 EC_DEBUG.PL(3, 'tbl_start(32): ',tbl_start(32));
441 EC_DEBUG.PL(3, 'tbl_start(33): ',tbl_start(33));
442 EC_DEBUG.PL(3, 'tbl_start(34): ',tbl_start(34));
443 EC_DEBUG.PL(3, 'tbl_start(35): ',tbl_start(35));
444 EC_DEBUG.PL(3, 'tbl_start(36): ',tbl_start(36));
445 EC_DEBUG.PL(3, 'tbl_start(37): ',tbl_start(37));
446 EC_DEBUG.PL(3, 'tbl_start(38): ',tbl_start(38));
447 EC_DEBUG.PL(3, 'tbl_start(39): ',tbl_start(39));
448 EC_DEBUG.PL(3, 'tbl_start(40): ',tbl_start(40));
449 EC_DEBUG.PL(3, 'tbl_start(41): ',tbl_start(41));
450 EC_DEBUG.PL(3, 'tbl_start(42): ',tbl_start(42));
451 EC_DEBUG.PL(3, 'tbl_start(43): ',tbl_start(43));
452 EC_DEBUG.PL(3, 'tbl_start(44): ',tbl_start(44));
453 EC_DEBUG.PL(3, 'tbl_start(45): ',tbl_start(45));
454 EC_DEBUG.PL(3, 'tbl_start(46): ',tbl_start(46));
455 EC_DEBUG.PL(3, 'tbl_start(47): ',tbl_start(47));
456 EC_DEBUG.PL(3, 'tbl_start(48): ',tbl_start(48));
457 EC_DEBUG.PL(3, 'tbl_start(49): ',tbl_start(49));
458 EC_DEBUG.PL(3, 'tbl_start(50): ',tbl_start(50));
459 EC_DEBUG.PL(3, 'tbl_start(51): ',tbl_start(51));
460 EC_DEBUG.PL(3, 'tbl_start(52): ',tbl_start(52));
461 EC_DEBUG.PL(3, 'tbl_start(53): ',tbl_start(53));
462 EC_DEBUG.PL(3, 'tbl_start(54): ',tbl_start(54));
463 EC_DEBUG.PL(3, 'tbl_start(55): ',tbl_start(55));
464 EC_DEBUG.PL(3, 'tbl_start(56): ',tbl_start(56));
465 EC_DEBUG.PL(3, 'tbl_start(57): ',tbl_start(57));
466 EC_DEBUG.PL(3, 'tbl_start(58): ',tbl_start(58));
467 EC_DEBUG.PL(3, 'tbl_start(59): ',tbl_start(59));
468 EC_DEBUG.PL(3, 'tbl_start(60): ',tbl_start(60));
469
470
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 NULL;
474 END; -- START DATE select block
475
476 /*************
477 ** End Date **
478 *************/
479
480 -- Get END_DATE from CHV_HORIZONTAL_SCHEDULES
481
482 xProgress := 'SPSO2B-10-1090';
483
484 BEGIN -- END DATE select block
485 xProgress := 'SPSO2B-10-1100';
486 --DBMS_OUTPUT.PUT_LINE('LN3 '||sqlcode);
487 SELECT *
488 INTO rec_hz
489 FROM chv_horizontal_schedules
490 WHERE schedule_item_id = rec_item.schedule_item_id
491 AND schedule_id = rec_item.schedule_id
492 AND row_type = 'BUCKET_END_DATE';
493
494
495 xProgress := 'SPSO2B-10-1110';
496 -- Copy BUCKET_END_DATE into PL/SQL table
497
498 tbl_end(1) := rec_hz.column1;
499 tbl_end(2) := rec_hz.column2;
500 tbl_end(3) := rec_hz.column3;
501 tbl_end(4) := rec_hz.column4;
502 tbl_end(5) := rec_hz.column5;
503 tbl_end(6) := rec_hz.column6;
504 tbl_end(7) := rec_hz.column7;
505 tbl_end(8) := rec_hz.column8;
506 tbl_end(9) := rec_hz.column9;
507 tbl_end(10) := rec_hz.column10;
508 tbl_end(11) := rec_hz.column11;
509 tbl_end(12) := rec_hz.column12;
510 tbl_end(13) := rec_hz.column13;
511 tbl_end(14) := rec_hz.column14;
512 tbl_end(15) := rec_hz.column15;
513 tbl_end(16) := rec_hz.column16;
514 tbl_end(17) := rec_hz.column17;
515 tbl_end(18) := rec_hz.column18;
516 tbl_end(19) := rec_hz.column19;
517 tbl_end(20) := rec_hz.column20;
518 tbl_end(21) := rec_hz.column21;
519 tbl_end(22) := rec_hz.column22;
520 tbl_end(23) := rec_hz.column23;
521 tbl_end(24) := rec_hz.column24;
522 tbl_end(25) := rec_hz.column25;
523 tbl_end(26) := rec_hz.column26;
524 tbl_end(27) := rec_hz.column27;
525 tbl_end(28) := rec_hz.column28;
526 tbl_end(29) := rec_hz.column29;
527 tbl_end(30) := rec_hz.column30;
528 tbl_end(31) := rec_hz.column31;
529 tbl_end(32) := rec_hz.column32;
530 tbl_end(33) := rec_hz.column33;
531 tbl_end(34) := rec_hz.column34;
532 tbl_end(35) := rec_hz.column35;
533 tbl_end(36) := rec_hz.column36;
534 tbl_end(37) := rec_hz.column37;
535 tbl_end(38) := rec_hz.column38;
536 tbl_end(39) := rec_hz.column39;
537 tbl_end(40) := rec_hz.column40;
538 tbl_end(41) := rec_hz.column41;
539 tbl_end(42) := rec_hz.column42;
540 tbl_end(43) := rec_hz.column43;
541 tbl_end(44) := rec_hz.column44;
542 tbl_end(45) := rec_hz.column45;
543 tbl_end(46) := rec_hz.column46;
544 tbl_end(47) := rec_hz.column47;
545 tbl_end(48) := rec_hz.column48;
546 tbl_end(49) := rec_hz.column49;
547 tbl_end(50) := rec_hz.column50;
548 tbl_end(51) := rec_hz.column51;
549 tbl_end(52) := rec_hz.column52;
550 tbl_end(53) := rec_hz.column53;
551 tbl_end(54) := rec_hz.column54;
552 tbl_end(55) := rec_hz.column55;
553 tbl_end(56) := rec_hz.column56;
554 tbl_end(57) := rec_hz.column57;
555 tbl_end(58) := rec_hz.column58;
556 tbl_end(59) := rec_hz.column59;
557 tbl_end(60) := rec_hz.column60;
558
559
560 EC_DEBUG.PL(3, 'tbl_end(1): ',tbl_end(1));
561 EC_DEBUG.PL(3, 'tbl_end(2): ',tbl_end(2));
562 EC_DEBUG.PL(3, 'tbl_end(3): ',tbl_end(3));
563 EC_DEBUG.PL(3, 'tbl_end(4): ',tbl_end(4));
564 EC_DEBUG.PL(3, 'tbl_end(5): ',tbl_end(5));
565 EC_DEBUG.PL(3, 'tbl_end(6): ',tbl_end(6));
566 EC_DEBUG.PL(3, 'tbl_end(7): ',tbl_end(7));
567 EC_DEBUG.PL(3, 'tbl_end(8): ',tbl_end(8));
568 EC_DEBUG.PL(3, 'tbl_end(9): ',tbl_end(9));
569 EC_DEBUG.PL(3, 'tbl_end(10): ',tbl_end(10));
570 EC_DEBUG.PL(3, 'tbl_end(11): ',tbl_end(11));
571 EC_DEBUG.PL(3, 'tbl_end(12): ',tbl_end(12));
572 EC_DEBUG.PL(3, 'tbl_end(13): ',tbl_end(13));
573 EC_DEBUG.PL(3, 'tbl_end(14): ',tbl_end(14));
574 EC_DEBUG.PL(3, 'tbl_end(15): ',tbl_end(15));
575 EC_DEBUG.PL(3, 'tbl_end(16): ',tbl_end(16));
576 EC_DEBUG.PL(3, 'tbl_end(17): ',tbl_end(17));
577 EC_DEBUG.PL(3, 'tbl_end(18): ',tbl_end(18));
578 EC_DEBUG.PL(3, 'tbl_end(19): ',tbl_end(19));
579 EC_DEBUG.PL(3, 'tbl_end(20): ',tbl_end(20));
580 EC_DEBUG.PL(3, 'tbl_end(21): ',tbl_end(21));
581 EC_DEBUG.PL(3, 'tbl_end(22): ',tbl_end(22));
582 EC_DEBUG.PL(3, 'tbl_end(23): ',tbl_end(23));
583 EC_DEBUG.PL(3, 'tbl_end(24): ',tbl_end(24));
584 EC_DEBUG.PL(3, 'tbl_end(25): ',tbl_end(25));
585 EC_DEBUG.PL(3, 'tbl_end(26): ',tbl_end(26));
586 EC_DEBUG.PL(3, 'tbl_end(27): ',tbl_end(27));
587 EC_DEBUG.PL(3, 'tbl_end(28): ',tbl_end(28));
588 EC_DEBUG.PL(3, 'tbl_end(29): ',tbl_end(29));
589 EC_DEBUG.PL(3, 'tbl_end(30): ',tbl_end(30));
590 EC_DEBUG.PL(3, 'tbl_end(31): ',tbl_end(31));
591 EC_DEBUG.PL(3, 'tbl_end(32): ',tbl_end(32));
592 EC_DEBUG.PL(3, 'tbl_end(33): ',tbl_end(33));
593 EC_DEBUG.PL(3, 'tbl_end(34): ',tbl_end(34));
594 EC_DEBUG.PL(3, 'tbl_end(35): ',tbl_end(35));
595 EC_DEBUG.PL(3, 'tbl_end(36): ',tbl_end(36));
596 EC_DEBUG.PL(3, 'tbl_end(37): ',tbl_end(37));
597 EC_DEBUG.PL(3, 'tbl_end(38): ',tbl_end(38));
598 EC_DEBUG.PL(3, 'tbl_end(39): ',tbl_end(39));
599 EC_DEBUG.PL(3, 'tbl_end(40): ',tbl_end(40));
600 EC_DEBUG.PL(3, 'tbl_end(41): ',tbl_end(41));
601 EC_DEBUG.PL(3, 'tbl_end(42): ',tbl_end(42));
602 EC_DEBUG.PL(3, 'tbl_end(43): ',tbl_end(43));
603 EC_DEBUG.PL(3, 'tbl_end(44): ',tbl_end(44));
604 EC_DEBUG.PL(3, 'tbl_end(45): ',tbl_end(45));
605 EC_DEBUG.PL(3, 'tbl_end(46): ',tbl_end(46));
606 EC_DEBUG.PL(3, 'tbl_end(47): ',tbl_end(47));
607 EC_DEBUG.PL(3, 'tbl_end(48): ',tbl_end(48));
608 EC_DEBUG.PL(3, 'tbl_end(49): ',tbl_end(49));
609 EC_DEBUG.PL(3, 'tbl_end(50): ',tbl_end(50));
610 EC_DEBUG.PL(3, 'tbl_end(51): ',tbl_end(51));
611 EC_DEBUG.PL(3, 'tbl_end(52): ',tbl_end(52));
612 EC_DEBUG.PL(3, 'tbl_end(53): ',tbl_end(53));
613 EC_DEBUG.PL(3, 'tbl_end(54): ',tbl_end(54));
614 EC_DEBUG.PL(3, 'tbl_end(55): ',tbl_end(55));
615 EC_DEBUG.PL(3, 'tbl_end(56): ',tbl_end(56));
616 EC_DEBUG.PL(3, 'tbl_end(57): ',tbl_end(57));
617 EC_DEBUG.PL(3, 'tbl_end(58): ',tbl_end(58));
618 EC_DEBUG.PL(3, 'tbl_end(59): ',tbl_end(59));
619 EC_DEBUG.PL(3, 'tbl_end(60): ',tbl_end(60));
620
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 NULL;
624 RAISE;
625 END; -- END DATE select block
626 /*********************
627 ** Release Quantity **
628 *********************/
629
630 -- Get RELEASE_QUANTITY from CHV_HORIZONTAL_SCHEDULES
631
632 xProgress := 'SPSO2B-10-1120';
633
634 BEGIN -- RELEASE QUANTITY block
635 xProgress := 'SPSO2B-10-1130';
636 SELECT *
637 INTO rec_hz
638 FROM chv_horizontal_schedules
639 WHERE schedule_item_id = rec_item.schedule_item_id
640 AND schedule_id = rec_item.schedule_id
641 AND row_type = 'RELEASE_QUANTITY';
642
643
644 xProgress := 'SPSO2B-10-1140';
645 -- Copy RELEASE_QUANTITY into PL/SQL table
646
647 tbl_rel(1) := rec_hz.column1;
648 tbl_rel(2) := rec_hz.column2;
649 tbl_rel(3) := rec_hz.column3;
650 tbl_rel(4) := rec_hz.column4;
651 tbl_rel(5) := rec_hz.column5;
652 tbl_rel(6) := rec_hz.column6;
653 tbl_rel(7) := rec_hz.column7;
654 tbl_rel(8) := rec_hz.column8;
655 tbl_rel(9) := rec_hz.column9;
656 tbl_rel(10) := rec_hz.column10;
657 tbl_rel(11) := rec_hz.column11;
658 tbl_rel(12) := rec_hz.column12;
659 tbl_rel(13) := rec_hz.column13;
660 tbl_rel(14) := rec_hz.column14;
661 tbl_rel(15) := rec_hz.column15;
662 tbl_rel(16) := rec_hz.column16;
663 tbl_rel(17) := rec_hz.column17;
664 tbl_rel(18) := rec_hz.column18;
665 tbl_rel(19) := rec_hz.column19;
666 tbl_rel(20) := rec_hz.column20;
667 tbl_rel(21) := rec_hz.column21;
668 tbl_rel(22) := rec_hz.column22;
669 tbl_rel(23) := rec_hz.column23;
670 tbl_rel(24) := rec_hz.column24;
671 tbl_rel(25) := rec_hz.column25;
672 tbl_rel(26) := rec_hz.column26;
673 tbl_rel(27) := rec_hz.column27;
674 tbl_rel(28) := rec_hz.column28;
675 tbl_rel(29) := rec_hz.column29;
676 tbl_rel(30) := rec_hz.column30;
677 tbl_rel(31) := rec_hz.column31;
678 tbl_rel(32) := rec_hz.column32;
679 tbl_rel(33) := rec_hz.column33;
680 tbl_rel(34) := rec_hz.column34;
681 tbl_rel(35) := rec_hz.column35;
682 tbl_rel(36) := rec_hz.column36;
683 tbl_rel(37) := rec_hz.column37;
684 tbl_rel(38) := rec_hz.column38;
685 tbl_rel(39) := rec_hz.column39;
686 tbl_rel(40) := rec_hz.column40;
687 tbl_rel(41) := rec_hz.column41;
688 tbl_rel(42) := rec_hz.column42;
689 tbl_rel(43) := rec_hz.column43;
690 tbl_rel(44) := rec_hz.column44;
691 tbl_rel(45) := rec_hz.column45;
692 tbl_rel(46) := rec_hz.column46;
693 tbl_rel(47) := rec_hz.column47;
694 tbl_rel(48) := rec_hz.column48;
695 tbl_rel(49) := rec_hz.column49;
696 tbl_rel(50) := rec_hz.column50;
697 tbl_rel(51) := rec_hz.column51;
698 tbl_rel(52) := rec_hz.column52;
699 tbl_rel(53) := rec_hz.column53;
700 tbl_rel(54) := rec_hz.column54;
701 tbl_rel(55) := rec_hz.column55;
702 tbl_rel(56) := rec_hz.column56;
703 tbl_rel(57) := rec_hz.column57;
704 tbl_rel(58) := rec_hz.column58;
705 tbl_rel(59) := rec_hz.column59;
706 tbl_rel(60) := rec_hz.column60;
707
708 EC_DEBUG.PL(3, 'tbl_end(1): ',tbl_end(1));
709 EC_DEBUG.PL(3, 'tbl_end(2): ',tbl_end(2));
710 EC_DEBUG.PL(3, 'tbl_end(3): ',tbl_end(3));
711 EC_DEBUG.PL(3, 'tbl_end(4): ',tbl_end(4));
712 EC_DEBUG.PL(3, 'tbl_end(5): ',tbl_end(5));
713 EC_DEBUG.PL(3, 'tbl_end(6): ',tbl_end(6));
714 EC_DEBUG.PL(3, 'tbl_end(7): ',tbl_end(7));
715 EC_DEBUG.PL(3, 'tbl_end(8): ',tbl_end(8));
716 EC_DEBUG.PL(3, 'tbl_end(9): ',tbl_end(9));
717 EC_DEBUG.PL(3, 'tbl_end(10): ',tbl_end(10));
718 EC_DEBUG.PL(3, 'tbl_end(11): ',tbl_end(11));
719 EC_DEBUG.PL(3, 'tbl_end(12): ',tbl_end(12));
720 EC_DEBUG.PL(3, 'tbl_end(13): ',tbl_end(13));
721 EC_DEBUG.PL(3, 'tbl_end(14): ',tbl_end(14));
722 EC_DEBUG.PL(3, 'tbl_end(15): ',tbl_end(15));
723 EC_DEBUG.PL(3, 'tbl_end(16): ',tbl_end(16));
724 EC_DEBUG.PL(3, 'tbl_end(17): ',tbl_end(17));
725 EC_DEBUG.PL(3, 'tbl_end(18): ',tbl_end(18));
726 EC_DEBUG.PL(3, 'tbl_end(19): ',tbl_end(19));
727 EC_DEBUG.PL(3, 'tbl_end(20): ',tbl_end(20));
728 EC_DEBUG.PL(3, 'tbl_end(21): ',tbl_end(21));
729 EC_DEBUG.PL(3, 'tbl_end(22): ',tbl_end(22));
730 EC_DEBUG.PL(3, 'tbl_end(23): ',tbl_end(23));
731 EC_DEBUG.PL(3, 'tbl_end(24): ',tbl_end(24));
732 EC_DEBUG.PL(3, 'tbl_end(25): ',tbl_end(25));
733 EC_DEBUG.PL(3, 'tbl_end(26): ',tbl_end(26));
734 EC_DEBUG.PL(3, 'tbl_end(27): ',tbl_end(27));
735 EC_DEBUG.PL(3, 'tbl_end(28): ',tbl_end(28));
736 EC_DEBUG.PL(3, 'tbl_end(29): ',tbl_end(29));
737 EC_DEBUG.PL(3, 'tbl_end(30): ',tbl_end(30));
738 EC_DEBUG.PL(3, 'tbl_end(31): ',tbl_end(31));
739 EC_DEBUG.PL(3, 'tbl_end(32): ',tbl_end(32));
740 EC_DEBUG.PL(3, 'tbl_end(33): ',tbl_end(33));
741 EC_DEBUG.PL(3, 'tbl_end(34): ',tbl_end(34));
742 EC_DEBUG.PL(3, 'tbl_end(35): ',tbl_end(35));
743 EC_DEBUG.PL(3, 'tbl_end(36): ',tbl_end(36));
744 EC_DEBUG.PL(3, 'tbl_end(37): ',tbl_end(37));
745 EC_DEBUG.PL(3, 'tbl_end(38): ',tbl_end(38));
746 EC_DEBUG.PL(3, 'tbl_end(39): ',tbl_end(39));
747 EC_DEBUG.PL(3, 'tbl_end(40): ',tbl_end(40));
748 EC_DEBUG.PL(3, 'tbl_end(41): ',tbl_end(41));
749 EC_DEBUG.PL(3, 'tbl_end(42): ',tbl_end(42));
750 EC_DEBUG.PL(3, 'tbl_end(43): ',tbl_end(43));
751 EC_DEBUG.PL(3, 'tbl_end(44): ',tbl_end(44));
752 EC_DEBUG.PL(3, 'tbl_end(45): ',tbl_end(45));
753 EC_DEBUG.PL(3, 'tbl_end(46): ',tbl_end(46));
754 EC_DEBUG.PL(3, 'tbl_end(47): ',tbl_end(47));
755 EC_DEBUG.PL(3, 'tbl_end(48): ',tbl_end(48));
756 EC_DEBUG.PL(3, 'tbl_end(49): ',tbl_end(49));
757 EC_DEBUG.PL(3, 'tbl_end(50): ',tbl_end(50));
758 EC_DEBUG.PL(3, 'tbl_end(51): ',tbl_end(51));
759 EC_DEBUG.PL(3, 'tbl_end(52): ',tbl_end(52));
760 EC_DEBUG.PL(3, 'tbl_end(53): ',tbl_end(53));
761 EC_DEBUG.PL(3, 'tbl_end(54): ',tbl_end(54));
762 EC_DEBUG.PL(3, 'tbl_end(55): ',tbl_end(55));
763 EC_DEBUG.PL(3, 'tbl_end(56): ',tbl_end(56));
764 EC_DEBUG.PL(3, 'tbl_end(57): ',tbl_end(57));
765 EC_DEBUG.PL(3, 'tbl_end(58): ',tbl_end(58));
766 EC_DEBUG.PL(3, 'tbl_end(59): ',tbl_end(59));
767 EC_DEBUG.PL(3, 'tbl_end(60): ',tbl_end(60));
768
769 EXCEPTION
770 WHEN NO_DATA_FOUND THEN
771 NULL;
772 RAISE;
773 END; -- RELEASE QUANTITY select block
774 /************************
775 ** Forecast Quantity **
776 ************************/
777
778
779 -- Get FORECAST_QUANTITY from CHV_HORIZONTAL_SCHEDULES
780
781 xProgress := 'SPSO2B-10-1150';
782
783 BEGIN -- FORECAST QUANTITY select block
784 xProgress := 'SPSO2B-10-1160';
785 --DBMS_OUTPUT.PUT_LINE('LN5 '||sqlcode);
786 SELECT *
787 INTO rec_hz
788 FROM chv_horizontal_schedules
789 WHERE schedule_item_id = rec_item.schedule_item_id
790 AND schedule_id = rec_item.schedule_id
791 AND row_type = 'FORECAST_QUANTITY';
792
793
794 xProgress := 'SPSO2B-10-1170';
795 -- Copy FORECAST_QUANTITY into PL/SQL table
796
797 tbl_for(1) := rec_hz.column1;
798 tbl_for(2) := rec_hz.column2;
799 tbl_for(3) := rec_hz.column3;
800 tbl_for(4) := rec_hz.column4;
801 tbl_for(5) := rec_hz.column5;
802 tbl_for(6) := rec_hz.column6;
803 tbl_for(7) := rec_hz.column7;
804 tbl_for(8) := rec_hz.column8;
805 tbl_for(9) := rec_hz.column9;
806 tbl_for(10) := rec_hz.column10;
807 tbl_for(11) := rec_hz.column11;
808 tbl_for(12) := rec_hz.column12;
809 tbl_for(13) := rec_hz.column13;
810 tbl_for(14) := rec_hz.column14;
811 tbl_for(15) := rec_hz.column15;
812 tbl_for(16) := rec_hz.column16;
813 tbl_for(17) := rec_hz.column17;
814 tbl_for(18) := rec_hz.column18;
815 tbl_for(19) := rec_hz.column19;
816 tbl_for(20) := rec_hz.column20;
817 tbl_for(21) := rec_hz.column21;
818 tbl_for(22) := rec_hz.column22;
819 tbl_for(23) := rec_hz.column23;
820 tbl_for(24) := rec_hz.column24;
821 tbl_for(25) := rec_hz.column25;
822 tbl_for(26) := rec_hz.column26;
823 tbl_for(27) := rec_hz.column27;
824 tbl_for(28) := rec_hz.column28;
825 tbl_for(29) := rec_hz.column29;
826 tbl_for(30) := rec_hz.column30;
827 tbl_for(31) := rec_hz.column31;
828 tbl_for(32) := rec_hz.column32;
829 tbl_for(33) := rec_hz.column33;
830 tbl_for(34) := rec_hz.column34;
831 tbl_for(35) := rec_hz.column35;
832 tbl_for(36) := rec_hz.column36;
833 tbl_for(37) := rec_hz.column37;
834 tbl_for(38) := rec_hz.column38;
835 tbl_for(39) := rec_hz.column39;
836 tbl_for(40) := rec_hz.column40;
837 tbl_for(41) := rec_hz.column41;
838 tbl_for(42) := rec_hz.column42;
839 tbl_for(43) := rec_hz.column43;
840 tbl_for(44) := rec_hz.column44;
841 tbl_for(45) := rec_hz.column45;
842 tbl_for(46) := rec_hz.column46;
843 tbl_for(47) := rec_hz.column47;
844 tbl_for(48) := rec_hz.column48;
845 tbl_for(49) := rec_hz.column49;
846 tbl_for(50) := rec_hz.column50;
847 tbl_for(51) := rec_hz.column51;
848 tbl_for(52) := rec_hz.column52;
849 tbl_for(53) := rec_hz.column53;
850 tbl_for(54) := rec_hz.column54;
851 tbl_for(55) := rec_hz.column55;
852 tbl_for(56) := rec_hz.column56;
853 tbl_for(57) := rec_hz.column57;
854 tbl_for(58) := rec_hz.column58;
855 tbl_for(59) := rec_hz.column59;
856 tbl_for(60) := rec_hz.column60;
857
858 EC_DEBUG.PL(3, 'tbl_for(1): ',tbl_for(1));
859 EC_DEBUG.PL(3, 'tbl_for(2): ',tbl_for(2));
860 EC_DEBUG.PL(3, 'tbl_for(3): ',tbl_for(3));
861 EC_DEBUG.PL(3, 'tbl_for(4): ',tbl_for(4));
862 EC_DEBUG.PL(3, 'tbl_for(5): ',tbl_for(5));
863 EC_DEBUG.PL(3, 'tbl_for(6): ',tbl_for(6));
864 EC_DEBUG.PL(3, 'tbl_for(7): ',tbl_for(7));
865 EC_DEBUG.PL(3, 'tbl_for(8): ',tbl_for(8));
866 EC_DEBUG.PL(3, 'tbl_for(9): ',tbl_for(9));
867 EC_DEBUG.PL(3, 'tbl_for(10): ',tbl_for(10));
868 EC_DEBUG.PL(3, 'tbl_for(11): ',tbl_for(11));
869 EC_DEBUG.PL(3, 'tbl_for(12): ',tbl_for(12));
870 EC_DEBUG.PL(3, 'tbl_for(13): ',tbl_for(13));
871 EC_DEBUG.PL(3, 'tbl_for(14): ',tbl_for(14));
872 EC_DEBUG.PL(3, 'tbl_for(15): ',tbl_for(15));
873 EC_DEBUG.PL(3, 'tbl_for(16): ',tbl_for(16));
874 EC_DEBUG.PL(3, 'tbl_for(17): ',tbl_for(17));
875 EC_DEBUG.PL(3, 'tbl_for(18): ',tbl_for(18));
876 EC_DEBUG.PL(3, 'tbl_for(19): ',tbl_for(19));
877 EC_DEBUG.PL(3, 'tbl_for(20): ',tbl_for(20));
878 EC_DEBUG.PL(3, 'tbl_for(21): ',tbl_for(21));
879 EC_DEBUG.PL(3, 'tbl_for(22): ',tbl_for(22));
880 EC_DEBUG.PL(3, 'tbl_for(23): ',tbl_for(23));
881 EC_DEBUG.PL(3, 'tbl_for(24): ',tbl_for(24));
882 EC_DEBUG.PL(3, 'tbl_for(25): ',tbl_for(25));
883 EC_DEBUG.PL(3, 'tbl_for(26): ',tbl_for(26));
884 EC_DEBUG.PL(3, 'tbl_for(27): ',tbl_for(27));
885 EC_DEBUG.PL(3, 'tbl_for(28): ',tbl_for(28));
886 EC_DEBUG.PL(3, 'tbl_for(29): ',tbl_for(29));
887 EC_DEBUG.PL(3, 'tbl_for(30): ',tbl_for(30));
888 EC_DEBUG.PL(3, 'tbl_for(31): ',tbl_for(31));
889 EC_DEBUG.PL(3, 'tbl_for(32): ',tbl_for(32));
890 EC_DEBUG.PL(3, 'tbl_for(33): ',tbl_for(33));
891 EC_DEBUG.PL(3, 'tbl_for(34): ',tbl_for(34));
892 EC_DEBUG.PL(3, 'tbl_for(35): ',tbl_for(35));
893 EC_DEBUG.PL(3, 'tbl_for(36): ',tbl_for(36));
894 EC_DEBUG.PL(3, 'tbl_for(37): ',tbl_for(37));
895 EC_DEBUG.PL(3, 'tbl_for(38): ',tbl_for(38));
896 EC_DEBUG.PL(3, 'tbl_for(39): ',tbl_for(39));
897 EC_DEBUG.PL(3, 'tbl_for(40): ',tbl_for(40));
898 EC_DEBUG.PL(3, 'tbl_for(41): ',tbl_for(41));
899 EC_DEBUG.PL(3, 'tbl_for(42): ',tbl_for(42));
900 EC_DEBUG.PL(3, 'tbl_for(43): ',tbl_for(43));
901 EC_DEBUG.PL(3, 'tbl_for(44): ',tbl_for(44));
902 EC_DEBUG.PL(3, 'tbl_for(45): ',tbl_for(45));
903 EC_DEBUG.PL(3, 'tbl_for(46): ',tbl_for(46));
904 EC_DEBUG.PL(3, 'tbl_for(47): ',tbl_for(47));
905 EC_DEBUG.PL(3, 'tbl_for(48): ',tbl_for(48));
906 EC_DEBUG.PL(3, 'tbl_for(49): ',tbl_for(49));
907 EC_DEBUG.PL(3, 'tbl_for(50): ',tbl_for(50));
908 EC_DEBUG.PL(3, 'tbl_for(51): ',tbl_for(51));
909 EC_DEBUG.PL(3, 'tbl_for(52): ',tbl_for(52));
910 EC_DEBUG.PL(3, 'tbl_for(53): ',tbl_for(53));
911 EC_DEBUG.PL(3, 'tbl_for(54): ',tbl_for(54));
912 EC_DEBUG.PL(3, 'tbl_for(55): ',tbl_for(55));
913 EC_DEBUG.PL(3, 'tbl_for(56): ',tbl_for(56));
914 EC_DEBUG.PL(3, 'tbl_for(57): ',tbl_for(57));
915 EC_DEBUG.PL(3, 'tbl_for(58): ',tbl_for(58));
916 EC_DEBUG.PL(3, 'tbl_for(59): ',tbl_for(59));
917 EC_DEBUG.PL(3, 'tbl_for(60): ',tbl_for(60));
918
919 EXCEPTION
920 WHEN NO_DATA_FOUND THEN
921 NULL;
922 RAISE;
923 END; -- FORECAST QUANTITY select block
924
925 /********************
926 ** Total Quantity **
927 ********************/
928
929 -- Get TOTAL_QUANTITY from CHV_HORIZONTAL_SCHEDULES
930
931 xProgress := 'SPSO2B-10-1180';
932
933 BEGIN -- TOTAL QUANTITY select block
934 xProgress := 'SPSO2B-10-1190';
935 --DBMS_OUTPUT.PUT_LINE('LN6 '||sqlcode);
936 SELECT *
937 INTO rec_hz
938 FROM chv_horizontal_schedules
939 WHERE schedule_item_id = rec_item.schedule_item_id
940 AND schedule_id = rec_item.schedule_id
941 AND row_type = 'TOTAL_QUANTITY';
942
943
944 xProgress := 'SPSO2B-10-1200';
945 -- Copy TOTAL_QUANTITY into PL/SQL table
946
947 tbl_tot(1) := rec_hz.column1;
948 tbl_tot(2) := rec_hz.column2;
949 tbl_tot(3) := rec_hz.column3;
950 tbl_tot(4) := rec_hz.column4;
951 tbl_tot(5) := rec_hz.column5;
952 tbl_tot(6) := rec_hz.column6;
953 tbl_tot(7) := rec_hz.column7;
954 tbl_tot(8) := rec_hz.column8;
955 tbl_tot(9) := rec_hz.column9;
956 tbl_tot(10) := rec_hz.column10;
957 tbl_tot(11) := rec_hz.column11;
958 tbl_tot(12) := rec_hz.column12;
959 tbl_tot(13) := rec_hz.column13;
960 tbl_tot(14) := rec_hz.column14;
961 tbl_tot(15) := rec_hz.column15;
962 tbl_tot(16) := rec_hz.column16;
963 tbl_tot(17) := rec_hz.column17;
964 tbl_tot(18) := rec_hz.column18;
965 tbl_tot(19) := rec_hz.column19;
966 tbl_tot(20) := rec_hz.column20;
967 tbl_tot(21) := rec_hz.column21;
968 tbl_tot(22) := rec_hz.column22;
969 tbl_tot(23) := rec_hz.column23;
970 tbl_tot(24) := rec_hz.column24;
971 tbl_tot(25) := rec_hz.column25;
972 tbl_tot(26) := rec_hz.column26;
973 tbl_tot(27) := rec_hz.column27;
974 tbl_tot(28) := rec_hz.column28;
975 tbl_tot(29) := rec_hz.column29;
976 tbl_tot(30) := rec_hz.column30;
977 tbl_tot(31) := rec_hz.column31;
978 tbl_tot(32) := rec_hz.column32;
979 tbl_tot(33) := rec_hz.column33;
980 tbl_tot(34) := rec_hz.column34;
981 tbl_tot(35) := rec_hz.column35;
982 tbl_tot(36) := rec_hz.column36;
983 tbl_tot(37) := rec_hz.column37;
984 tbl_tot(38) := rec_hz.column38;
985 tbl_tot(39) := rec_hz.column39;
986 tbl_tot(40) := rec_hz.column40;
987 tbl_tot(41) := rec_hz.column41;
988 tbl_tot(42) := rec_hz.column42;
989 tbl_tot(43) := rec_hz.column43;
990 tbl_tot(44) := rec_hz.column44;
991 tbl_tot(45) := rec_hz.column45;
992 tbl_tot(46) := rec_hz.column46;
993 tbl_tot(47) := rec_hz.column47;
994 tbl_tot(48) := rec_hz.column48;
995 tbl_tot(49) := rec_hz.column49;
996 tbl_tot(50) := rec_hz.column50;
997 tbl_tot(51) := rec_hz.column51;
998 tbl_tot(52) := rec_hz.column52;
999 tbl_tot(53) := rec_hz.column53;
1000 tbl_tot(54) := rec_hz.column54;
1001 tbl_tot(55) := rec_hz.column55;
1002 tbl_tot(56) := rec_hz.column56;
1003 tbl_tot(57) := rec_hz.column57;
1004 tbl_tot(58) := rec_hz.column58;
1005 tbl_tot(59) := rec_hz.column59;
1006 tbl_tot(60) := rec_hz.column60;
1007
1008 EC_DEBUG.PL(3, 'tbl_tot(1): ',tbl_tot(1));
1009 EC_DEBUG.PL(3, 'tbl_tot(2): ',tbl_tot(2));
1010 EC_DEBUG.PL(3, 'tbl_tot(3): ',tbl_tot(3));
1011 EC_DEBUG.PL(3, 'tbl_tot(4): ',tbl_tot(4));
1012 EC_DEBUG.PL(3, 'tbl_tot(5): ',tbl_tot(5));
1013 EC_DEBUG.PL(3, 'tbl_tot(6): ',tbl_tot(6));
1014 EC_DEBUG.PL(3, 'tbl_tot(7): ',tbl_tot(7));
1015 EC_DEBUG.PL(3, 'tbl_tot(8): ',tbl_tot(8));
1016 EC_DEBUG.PL(3, 'tbl_tot(9): ',tbl_tot(9));
1017 EC_DEBUG.PL(3, 'tbl_tot(10): ',tbl_tot(10));
1018 EC_DEBUG.PL(3, 'tbl_tot(11): ',tbl_tot(11));
1019 EC_DEBUG.PL(3, 'tbl_tot(12): ',tbl_tot(12));
1020 EC_DEBUG.PL(3, 'tbl_tot(13): ',tbl_tot(13));
1021 EC_DEBUG.PL(3, 'tbl_tot(14): ',tbl_tot(14));
1022 EC_DEBUG.PL(3, 'tbl_tot(15): ',tbl_tot(15));
1023 EC_DEBUG.PL(3, 'tbl_tot(16): ',tbl_tot(16));
1024 EC_DEBUG.PL(3, 'tbl_tot(17): ',tbl_tot(17));
1025 EC_DEBUG.PL(3, 'tbl_tot(18): ',tbl_tot(18));
1026 EC_DEBUG.PL(3, 'tbl_tot(19): ',tbl_tot(19));
1027 EC_DEBUG.PL(3, 'tbl_tot(20): ',tbl_tot(20));
1028 EC_DEBUG.PL(3, 'tbl_tot(21): ',tbl_tot(21));
1029 EC_DEBUG.PL(3, 'tbl_tot(22): ',tbl_tot(22));
1030 EC_DEBUG.PL(3, 'tbl_tot(23): ',tbl_tot(23));
1031 EC_DEBUG.PL(3, 'tbl_tot(24): ',tbl_tot(24));
1032 EC_DEBUG.PL(3, 'tbl_tot(25): ',tbl_tot(25));
1033 EC_DEBUG.PL(3, 'tbl_tot(26): ',tbl_tot(26));
1034 EC_DEBUG.PL(3, 'tbl_tot(27): ',tbl_tot(27));
1035 EC_DEBUG.PL(3, 'tbl_tot(28): ',tbl_tot(28));
1036 EC_DEBUG.PL(3, 'tbl_tot(29): ',tbl_tot(29));
1037 EC_DEBUG.PL(3, 'tbl_tot(30): ',tbl_tot(30));
1038 EC_DEBUG.PL(3, 'tbl_tot(31): ',tbl_tot(31));
1039 EC_DEBUG.PL(3, 'tbl_tot(32): ',tbl_tot(32));
1040 EC_DEBUG.PL(3, 'tbl_tot(33): ',tbl_tot(33));
1041 EC_DEBUG.PL(3, 'tbl_tot(34): ',tbl_tot(34));
1042 EC_DEBUG.PL(3, 'tbl_tot(35): ',tbl_tot(35));
1043 EC_DEBUG.PL(3, 'tbl_tot(36): ',tbl_tot(36));
1044 EC_DEBUG.PL(3, 'tbl_tot(37): ',tbl_tot(37));
1045 EC_DEBUG.PL(3, 'tbl_tot(38): ',tbl_tot(38));
1046 EC_DEBUG.PL(3, 'tbl_tot(39): ',tbl_tot(39));
1047 EC_DEBUG.PL(3, 'tbl_tot(40): ',tbl_tot(40));
1048 EC_DEBUG.PL(3, 'tbl_tot(41): ',tbl_tot(41));
1049 EC_DEBUG.PL(3, 'tbl_tot(42): ',tbl_tot(42));
1050 EC_DEBUG.PL(3, 'tbl_tot(43): ',tbl_tot(43));
1051 EC_DEBUG.PL(3, 'tbl_tot(44): ',tbl_tot(44));
1052 EC_DEBUG.PL(3, 'tbl_tot(45): ',tbl_tot(45));
1053 EC_DEBUG.PL(3, 'tbl_tot(46): ',tbl_tot(46));
1054 EC_DEBUG.PL(3, 'tbl_tot(47): ',tbl_tot(47));
1055 EC_DEBUG.PL(3, 'tbl_tot(48): ',tbl_tot(48));
1056 EC_DEBUG.PL(3, 'tbl_tot(49): ',tbl_tot(49));
1057 EC_DEBUG.PL(3, 'tbl_tot(50): ',tbl_tot(50));
1058 EC_DEBUG.PL(3, 'tbl_tot(51): ',tbl_tot(51));
1059 EC_DEBUG.PL(3, 'tbl_tot(52): ',tbl_tot(52));
1060 EC_DEBUG.PL(3, 'tbl_tot(53): ',tbl_tot(53));
1061 EC_DEBUG.PL(3, 'tbl_tot(54): ',tbl_tot(54));
1062 EC_DEBUG.PL(3, 'tbl_tot(55): ',tbl_tot(55));
1063 EC_DEBUG.PL(3, 'tbl_tot(56): ',tbl_tot(56));
1064 EC_DEBUG.PL(3, 'tbl_tot(57): ',tbl_tot(57));
1065 EC_DEBUG.PL(3, 'tbl_tot(58): ',tbl_tot(58));
1066 EC_DEBUG.PL(3, 'tbl_tot(59): ',tbl_tot(59));
1067 EC_DEBUG.PL(3, 'tbl_tot(60): ',tbl_tot(60));
1068
1069 EXCEPTION
1070 WHEN NO_DATA_FOUND THEN
1071 NULL;
1072 RAISE;
1073 END; -- TOTAL QUANTITY select block
1074
1075 EXCEPTION
1076 WHEN OTHERS THEN
1077 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress);
1078 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1079 app_exception.raise_exception;
1080
1081 END; -- end select detail block
1082
1083
1084 /**************************************************
1085 ** insert PAST DUE bucketed requirments detail **
1086 **************************************************/
1087
1088 /* BEGIN -- PAST DUE insert block
1089 -- incerment detail record sequence counter
1090
1091 x_item_detail_sequence := NVL(x_item_detail_sequence,0) + 1;
1092 xProgress := 'SPSO2B-10-1210';
1093
1094
1095 --DBMS_OUTPUT.PUT_LINE('LN7 '||sqlcode);
1096 INSERT INTO ECE_SPSO_ITEM_DET
1097 (
1098 COMMUNICATION_METHOD ,
1099 TRANSACTION_TYPE ,
1100
1101 RUN_ID ,
1102 SCHEDULE_ITEM_DETAIL_SEQUENCE ,
1103 SCHEDULE_ID ,
1104 SCHEDULE_ITEM_ID ,
1105 DETAIL_CATEGORY , -- bucketed requirments
1106 DETAIL_DESCRIPTOR , -- Past Due
1107 STARTING_DATE ,
1108 ENDING_DATE ,
1109 FORECAST_QUANTITY ,
1110 RELEASE_QUANTITY ,
1111 TOTAL_QUANTITY ,
1112 TRANSACTION_RECORD_ID
1113 )
1114 VALUES
1115 (
1116 p_run_id,
1117 x_item_detail_sequence,
1118 rec_hz.schedule_id,
1119 rec_hz.schedule_item_id,
1120 'REQUIREMENT',
1121 tbl_desc(1),
1122 TO_DATE(NVL(tbl_start(1),'1901/01/01'), 'YYYY/MM/DD'),
1123 TO_DATE(tbl_end(1), 'YYYY/MM/DD'),
1124 NVL(TO_NUMBER(tbl_for(1)),0),
1125 NVL(TO_NUMBER(tbl_rel(1)),0),
1126 NVL(TO_NUMBER(tbl_tot(1)),0),
1127 ece_spso_item_det_s.nextval
1128 );
1129
1130 Bug 1742567
1131 p_communication_method,
1132 p_transaction_type,
1133
1134
1135 SELECT
1136 ece_spso_item_det_s.currval
1137 INTO
1138 l_transaction_number
1139 FROM
1140 dual;
1141 ECE_SPSO_X.populate_extension_item_det(l_transaction_number,
1142 rec_hz.schedule_id,
1143 rec_hz.schedule_item_id);
1144
1145
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress);
1149 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1150 app_exception.raise_exception;
1151 END; -- end select detail block
1152 */
1153 /***************************************************
1154 ** insert ALL OTHER bucketed requirments detail **
1155 ***************************************************/
1156
1157 BEGIN -- ALL OTHER buckets insert block
1158
1159 -- loop until reach NULL columns.
1160
1161 <<buckets>>
1162 xProgress := 'SPSO2B-10-1220';
1163 FOR i IN x_min_col..x_max_col LOOP
1164 EXIT WHEN (tbl_desc(i) IS NULL OR
1165 tbl_desc(i) ='FUTURE');
1166
1167 -- incerment detail record sequence counter
1168 x_item_detail_sequence := NVL(x_item_detail_sequence,0) + 1;
1169 EC_DEBUG.PL(3, 'x_item_detail_sequence: ',x_item_detail_sequence);
1170
1171 xProgress := 'SPSO2B-10-1230';
1172
1173
1174 --DBMS_OUTPUT.PUT_LINE('LN9 '||i||sqlcode);
1175 INSERT INTO ECE_SPSO_ITEM_DET
1176 (
1177 /* COMMUNICATION_METHOD ,
1178 TRANSACTION_TYPE ,
1179 */
1180 RUN_ID ,
1181 SCHEDULE_ITEM_DETAIL_SEQUENCE ,
1182 SCHEDULE_ID ,
1183 SCHEDULE_ITEM_ID ,
1184 DETAIL_CATEGORY , -- Bucketed Requirments
1185 DETAIL_DESCRIPTOR , -- All other buckets
1186 STARTING_DATE ,
1187 ENDING_DATE ,
1188 FORECAST_QUANTITY ,
1189 RELEASE_QUANTITY ,
1190 TOTAL_QUANTITY ,
1191 TRANSACTION_RECORD_ID
1192 )
1193 VALUES
1194 (
1195 p_run_id,
1196 x_item_detail_sequence,
1197 rec_hz.schedule_id,
1198 rec_hz.schedule_item_id,
1199 'REQUIREMENT',
1200 tbl_desc(i),
1201 TO_DATE(tbl_start(i), 'YYYY/MM/DD'),
1202 TO_DATE(tbl_end(i), 'YYYY/MM/DD'),
1203 NVL(TO_NUMBER(tbl_for(i)),0),
1204 NVL(TO_NUMBER(tbl_rel(i)),0),
1205 NVL(TO_NUMBER(tbl_tot(i)),0),
1206 ece_spso_item_det_s.nextval
1207 );
1208
1209 /*Bug 1742567
1210 p_communication_method,
1211 p_transaction_type,
1212 */
1213
1214 SELECT
1215 ECE_SPSO_ITEM_DET_S.currval
1216 INTO
1217 l_transaction_number
1218 FROM
1219 dual;
1220 ECE_SPSO_X.populate_extension_item_det(l_transaction_number,
1221 rec_hz.schedule_id,
1222 rec_hz.schedule_item_id);
1223
1224 DECLARE
1225 CURSOR sch_ship_det_c IS
1226 SELECT POLL.QUANTITY ITEM_DET_SHIP_QUANTITY,
1227 MUM.UOM_CODE ITEM_DET_UOM_CODE,
1228 HRL.LOCATION_CODE ITEM_DET_ST_LOC_CODE,
1229 HRL.ECE_TP_LOCATION_CODE ITEM_DET_ST_LOC_CODE_EXT,
1230 HRL.ADDRESS_LINE_1 ITEM_DET_ST_ADDR_1,
1231 HRL.ADDRESS_LINE_2 ITEM_DET_ST_ADDR_2,
1232 HRL.ADDRESS_LINE_3 ITEM_DET_ST_ADDR_3,
1233 HRL.TOWN_OR_CITY ITEM_DET_ST_CITY,
1234 HRL.POSTAL_CODE ITEM_DET_ST_POSTAL_CODE,
1235 HRL.COUNTRY ITEM_DET_ST_COUNTRY,
1236 HRL.REGION_1 ITEM_DET_ST_COUNTY,
1237 HRL.REGION_2 ITEM_DET_ST_STATE,
1238 HRL.REGION_3 ITEM_DET_ST_REGION_3,
1239 HRL.TELEPHONE_NUMBER_1 ITEM_DET_ST_PHONE,
1240 POH.SEGMENT1||'-'||to_char(POR.RELEASE_NUM) DOCUMENT_RELEASE_NUMBER,
1241 POL.LINE_NUM DOCUMENT_LINE_NUMBER,
1242 POLL.LINE_LOCATION_ID LINE_LOCATION_ID
1243 FROM CHV_ITEM_ORDERS CIO, PO_LINE_LOCATIONS POLL,
1244 HR_LOCATIONS HRL, MTL_UNITS_OF_MEASURE MUM,
1245 PO_HEADERS POH, PO_LINES POL, PO_RELEASES POR
1246 WHERE CIO.SCHEDULE_ID = rec_item.schedule_id
1247 AND CIO.SCHEDULE_ITEM_ID = rec_item.schedule_item_id
1248 AND CIO.DOCUMENT_HEADER_ID = POLL.PO_HEADER_ID
1249 AND CIO.DOCUMENT_LINE_ID = POLL.PO_LINE_ID
1250 AND CIO.DOCUMENT_SHIPMENT_ID = POLL.LINE_LOCATION_ID
1251 AND CIO.PURCHASING_UNIT_OF_MEASURE = MUM.UNIT_OF_MEASURE(+)
1252 AND HRL.LOCATION_ID(+) = POLL.SHIP_TO_LOCATION_ID
1253 AND TRUNC(NVL(POLL.PROMISED_DATE,POLL.NEED_BY_DATE)) between
1254 TO_DATE(NVL(tbl_start(i),'1901/01/01'),'YYYY/MM/DD')
1255 AND TO_DATE(tbl_end(i), 'YYYY/MM/DD')
1256 AND POLL.PO_HEADER_ID = POH.PO_HEADER_ID
1257 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
1258 AND POLL.PO_RELEASE_ID = POR.PO_RELEASE_ID
1259 AND POR.PO_HEADER_ID = POH.PO_HEADER_ID;
1260
1261 x_schedule_ship_id number := 0;
1262
1263 BEGIN
1264 IF NVL(TO_NUMBER(tbl_rel(i)),0) > 0 THEN
1265 FOR rec_ship_det IN sch_ship_det_c LOOP
1266 BEGIN
1267 xProgress := 'SPSO2B-10-1240';
1268 SELECT max(schedule_ship_id)
1269 INTO x_schedule_ship_id
1270 FROM ece_spso_ship_det
1271 WHERE schedule_id = rec_item.schedule_id
1272 AND schedule_item_id= rec_item.schedule_item_id
1273 AND schedule_item_detail_sequence = x_item_detail_sequence;
1274 EXCEPTION
1275 WHEN NO_DATA_FOUND THEN
1276 x_schedule_ship_id := 0;
1277 END;
1278 xProgress := 'SPSO2B-10-1250';
1279 x_schedule_ship_id := NVL(x_schedule_ship_id,0) + 1;
1280
1281 xProgress := 'SPSO2B-10-1260';
1282 INSERT INTO ECE_SPSO_SHIP_DET
1283 (
1284 RUN_ID,
1285 SCHEDULE_SHIP_ID,
1286 SCHEDULE_ID ,
1287 SCHEDULE_ITEM_ID ,
1288 SCHEDULE_ITEM_DETAIL_SEQUENCE,
1289 ITEM_DET_SHIP_QUANTITY,
1290 ITEM_DET_UOM_CODE,
1291 ITEM_DET_ST_LOC_CODE,
1292 ITEM_DET_ST_LOC_CODE_EXT,
1293 ITEM_DET_ST_ADDR_1,
1294 ITEM_DET_ST_ADDR_2,
1295 ITEM_DET_ST_ADDR_3,
1296 ITEM_DET_ST_CITY,
1297 ITEM_DET_ST_POSTAL_CODE,
1298 ITEM_DET_ST_COUNTRY,
1299 ITEM_DET_ST_REGION_1,
1300 ITEM_DET_ST_REGION_2,
1301 ITEM_DET_ST_REGION_3,
1302 ITEM_DET_ST_PHONE,
1303 TRANSACTION_RECORD_ID,
1304 DOCUMENT_RELEASE_NUMBER,
1305 DOCUMENT_LINE_NUMBER,
1306 LINE_LOCATION_ID
1307 )
1308 VALUES
1309 ( p_run_id,
1310 x_schedule_ship_id,
1311 rec_item.schedule_id,
1312 rec_item.schedule_item_id,
1313 x_item_detail_sequence,
1314 rec_ship_det.ITEM_DET_SHIP_QUANTITY,
1315 rec_ship_det.ITEM_DET_UOM_CODE,
1316 rec_ship_det.ITEM_DET_ST_LOC_CODE,
1317 rec_ship_det.ITEM_DET_ST_LOC_CODE_EXT,
1318 rec_ship_det.ITEM_DET_ST_ADDR_1,
1319 rec_ship_det.ITEM_DET_ST_ADDR_2,
1320 rec_ship_det.ITEM_DET_ST_ADDR_3,
1321 rec_ship_det.ITEM_DET_ST_CITY,
1322 rec_ship_det.ITEM_DET_ST_POSTAL_CODE,
1323 rec_ship_det.ITEM_DET_ST_COUNTRY,
1324 rec_ship_det.ITEM_DET_ST_COUNTY,
1325 rec_ship_det.ITEM_DET_ST_STATE,
1326 rec_ship_det.ITEM_DET_ST_REGION_3,
1327 rec_ship_det.ITEM_DET_ST_PHONE,
1328 ece_spso_ship_det_s.nextval,
1329 rec_ship_det.DOCUMENT_RELEASE_NUMBER,
1330 rec_ship_det.DOCUMENT_LINE_NUMBER,
1331 rec_ship_det.LINE_LOCATION_ID
1332 );
1333
1334 xProgress := 'SPSO2B-10-1270';
1335 SELECT
1336 ECE_SPSO_SHIP_DET_S.currval
1337 INTO
1338 l_transaction_number
1339 FROM
1340 dual;
1341 xProgress := 'SPSO2B-10-1280';
1342 ECE_SPSO_X.populate_extension_ship_det(
1343 l_transaction_number,
1344 rec_item.schedule_id,
1345 rec_item.schedule_item_id,
1346 x_item_detail_sequence);
1347
1348 END LOOP ;
1349 END IF;
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress);
1353 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1354 app_exception.raise_exception;
1355 END;
1356
1357 END LOOP buckets;
1358
1359 EXCEPTION
1360 WHEN OTHERS THEN
1361 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress);
1362 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1363 app_exception.raise_exception;
1364 END;
1365
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress);
1370 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1371 app_exception.raise_exception;
1372 END; -- item detail block
1373 END IF;
1374 END LOOP item; -- item for loop
1375 END IF;
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress);
1379 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1380 app_exception.raise_exception;
1381 END; -- item block
1382
1383 END LOOP header; -- header for loop
1384
1385 EC_DEBUG.POP('ECE_SPSO_TRANS2.populate_supplier_sched_api2');
1386 EXCEPTION
1387 WHEN OTHERS THEN
1388 ec_debug.pl (0, 'EC', 'ECE_PROGRAM_ERROR', 'PROGRESS_LEVEL', xProgress);
1389 ec_debug.pl (0, 'EC', 'ECE_ERROR_MESSAGE', 'ERROR_MESSAGE', SQLERRM);
1390 app_exception.raise_exception;
1391 END POPULATE_SUPPLIER_SCHED_API2; -- end of procedure
1392
1393 END ECE_SPSO_TRANS2; -- end of package body