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