[Home] [Help]
PACKAGE BODY: APPS.MSC_DRP_HORI_PLAN
Source
1 PACKAGE BODY MSC_DRP_HORI_PLAN AS
2 /* $Header: MSCDRPHB.pls 120.19.12020000.2 2012/08/10 16:17:49 snilagir ship $ */
3
4 PURCHASE_ORDER CONSTANT INTEGER := 1; /* supply type lookup */
5 PURCH_REQ CONSTANT INTEGER := 2;
6 WORK_ORDER CONSTANT INTEGER := 3;
7 PLANNED_ORDER CONSTANT INTEGER := 5;
8 NONSTD_JOB CONSTANT INTEGER := 7;
9 RECEIPT_PURCH_ORDER CONSTANT INTEGER := 8;
10 INTRANSIT_SHIPMENT CONSTANT INTEGER := 11;
11 INTRANSIT_RECEIPT CONSTANT INTEGER := 12;
12 BEG_ON_HAND CONSTANT INTEGER := 18;
13 PLANNED_ARRIVAL CONSTANT INTEGER := 51;
14
15 PLANNED_ORDER_DEMAND CONSTANT INTEGER := 1; /* demand type lookup */
16 NONSTD_JOB_DEMAND CONSTANT INTEGER := 2;
17 WORK_ORDER_DEMAND CONSTANT INTEGER := 3;
18 EXPIRE_LOT_DEMAND CONSTANT INTEGER := 5;
19 OTHER_INDP_DEMAND CONSTANT INTEGER := 9;
20 HARD_RESERVATION CONSTANT INTEGER := 10;
21 MPS_DEMAND CONSTANT INTEGER := 12;
22 COPIED_SCHED_DEMAND CONSTANT INTEGER := 15;
23 PLANNED_ORDER_SCRAP CONSTANT INTEGER := 16;
24 DISCRETE_JOB_SCRAP CONSTANT INTEGER := 17;
25 PURCHASE_ORDER_SCRAP CONSTANT INTEGER := 18;
26 PURCH_REQ_SCRAP CONSTANT INTEGER := 19;
27 RECEIPT_PO_SCRAP CONSTANT INTEGER := 20;
28 INTRANSIT_SHIPMENT_SCRAP CONSTANT INTEGER := 23;
29 INTER_ORG_DEMAND CONSTANT INTEGER := 24;
30 AGGREGATE_DEMAND CONSTANT INTEGER := 28;
31 FORECAST CONSTANT INTEGER := 29;
32 SALES_ORDER CONSTANT INTEGER := 30;
33 CONS_KIT_DEMAND CONSTANT INTEGER := 47;
34 /* horizontal plan type lookup */
35
36 HZ_EXT_DEMAND CONSTANT INTEGER := 10; -- calculated
37 HZ_EXT_SALES_ORDER CONSTANT INTEGER := 20;
38 HZ_FORECAST CONSTANT INTEGER := 30;
39 HZ_KIT_DEMAND CONSTANT INTEGER := 40;
40 HZ_SHIPMENT CONSTANT INTEGER := 50; -- calculated
41 HZ_INT_SALES_ORDER CONSTANT INTEGER := -1; -- hidden
42 HZ_PLANNED_SHIPMENT CONSTANT INTEGER := -1; -- hidden
43 HZ_EXPIRE_LOT CONSTANT INTEGER := 370;
44 HZ_SCRAP_DEMAND CONSTANT INTEGER := -1; -- hidden
45 HZ_OTHER_DEMAND CONSTANT INTEGER := 60; -- calculated
46 HZ_TOTAL_DEMAND CONSTANT INTEGER := 70; -- calculated
47 HZ_REQUEST_SHIPMENT CONSTANT INTEGER := 80;
48 HZ_UNC_KIT_DEMAND CONSTANT INTEGER := 90;
49 HZ_UNC_SCRAP_DEMAND CONSTANT INTEGER := -1; -- hidden
50 HZ_UNC_OTHER_DEMAND CONSTANT INTEGER := 100;
51 HZ_TOTAL_UNC_DEMAND CONSTANT INTEGER := 110; -- calculated
52 HZ_TOTAL_INT_SUPPLY CONSTANT INTEGER := 120; -- calculated
53 HZ_BEG_ON_HAND CONSTANT INTEGER := 130;
54 HZ_WIP CONSTANT INTEGER := 140;
55 HZ_RECEIVING CONSTANT INTEGER := 150;
56 HZ_PLANNED_MAKE CONSTANT INTEGER := 160;
57 HZ_TOTAL_EXT_SUPPLY CONSTANT INTEGER := 170; -- calculated
58 HZ_EXT_TRANSIT CONSTANT INTEGER := 180;
59 HZ_PURCHASE_ORDER CONSTANT INTEGER := 190;
60 HZ_EXT_PURCH_REQ CONSTANT INTEGER := 200;
61 HZ_PLANNED_BUY CONSTANT INTEGER := 210;
62 HZ_ARRIVAL CONSTANT INTEGER := 220; -- calculated
63 HZ_INT_TRANSIT CONSTANT INTEGER := -1; -- hidden
64 HZ_INT_PURCH_REQ CONSTANT INTEGER := -1; -- hidden
65 HZ_PLANNED_ARRIVAL CONSTANT INTEGER := -1; -- hidden
66 HZ_TOTAL_TRANSIT CONSTANT INTEGER := 230; -- calculated
67 HZ_TOTAL_PURCH_REQ CONSTANT INTEGER := 240; -- calculated
68 HZ_TOTAL_PLANNED CONSTANT INTEGER := 250; -- calculated
69 HZ_TOTAL_SUPPLY CONSTANT INTEGER := 260; -- calculated
70 HZ_CURRENT_S_RECEIPT CONSTANT INTEGER := 270; -- calculated
71 HZ_POH CONSTANT INTEGER := 280; -- calculated
72 HZ_PAB CONSTANT INTEGER := 290; -- calculated
73 HZ_UNC_PAB CONSTANT INTEGER := 300; -- calculated
74 HZ_MAX_QTY CONSTANT INTEGER := 310;
75 HZ_TARGET_QTY CONSTANT INTEGER := 320;
76 HZ_SAFETY_STOCK CONSTANT INTEGER := 330;
77 HZ_INBOUND CONSTANT INTEGER := 340;
78 HZ_OUTBOUND CONSTANT INTEGER := 350;
79 HZ_ATP CONSTANT INTEGER := 360;
80 HZ_REQUEST_ARRIVAL CONSTANT INTEGER := 380;
81 HZ_EXP_DEMAND CONSTANT INTEGER := 390;
82
83 /* offset */
84
85 EXT_DEMAND_OFF CONSTANT INTEGER := 1;
86 EXT_SALES_ORDER_OFF CONSTANT INTEGER := 2;
87 FORECAST_OFF CONSTANT INTEGER := 3;
88 KIT_DEMAND_OFF CONSTANT INTEGER := 4;
89 SHIPMENT_OFF CONSTANT INTEGER := 5;
90 INT_SALES_ORDER_OFF CONSTANT INTEGER := 6; -- hidden
91 PLANNED_SHIPMENT_OFF CONSTANT INTEGER := 7; -- hidden
92 EXPIRE_LOT_OFF CONSTANT INTEGER := 8; -- hidden
93 SCRAP_DEMAND_OFF CONSTANT INTEGER := 9; -- hidden
94 OTHER_DEMAND_OFF CONSTANT INTEGER := 10;
95 TOTAL_DEMAND_OFF CONSTANT INTEGER := 11;
96 REQUEST_SHIPMENT_OFF CONSTANT INTEGER := 12;
97 UNC_KIT_DEMAND_OFF CONSTANT INTEGER := 13;
98 UNC_SCRAP_DEMAND_OFF CONSTANT INTEGER := 14; -- hidden
99 UNC_OTHER_DEMAND_OFF CONSTANT INTEGER := 15;
100 TOTAL_UNC_DEMAND_OFF CONSTANT INTEGER := 16;
101 TOTAL_INT_SUPPLY_OFF CONSTANT INTEGER := 17;
102 BEG_ON_HAND_OFF CONSTANT INTEGER := 18;
103 WIP_OFF CONSTANT INTEGER := 19;
104 RECEIVING_OFF CONSTANT INTEGER := 20;
105 PLANNED_MAKE_OFF CONSTANT INTEGER := 21;
106 TOTAL_EXT_SUPPLY_OFF CONSTANT INTEGER := 22;
107 EXT_TRANSIT_OFF CONSTANT INTEGER := 23;
108 PURCHASE_ORDER_OFF CONSTANT INTEGER := 24;
109 EXT_PURCH_REQ_OFF CONSTANT INTEGER := 25;
110 PLANNED_BUY_OFF CONSTANT INTEGER := 26;
111 ARRIVAL_OFF CONSTANT INTEGER := 27;
112 INT_TRANSIT_OFF CONSTANT INTEGER := 28;
113 INT_PURCH_REQ_OFF CONSTANT INTEGER := 29; -- hidden
114 PLANNED_ARRIVAL_OFF CONSTANT INTEGER := 30; -- hidden
115 TOTAL_TRANSIT_OFF CONSTANT INTEGER := 31;
116 TOTAL_PURCH_REQ_OFF CONSTANT INTEGER := 32;
117 TOTAL_PLANNED_OFF CONSTANT INTEGER := 33;
118 TOTAL_SUPPLY_OFF CONSTANT INTEGER := 34;
119 CURRENT_S_RECEIPT_OFF CONSTANT INTEGER := 35;
120 POH_OFF CONSTANT INTEGER := 36;
121 PAB_OFF CONSTANT INTEGER := 37;
122 UNC_PAB_OFF CONSTANT INTEGER := 38;
123 MAX_QTY_OFF CONSTANT INTEGER := 39;
124 TARGET_QTY_OFF CONSTANT INTEGER := 40;
125 SAFETY_STOCK_OFF CONSTANT INTEGER := 41;
126 INBOUND_OFF CONSTANT INTEGER := 42;
127 OUTBOUND_OFF CONSTANT INTEGER := 43;
128 ATP_OFF CONSTANT INTEGER := 44;
129 REQUEST_ARRIVAL_OFF CONSTANT INTEGER := 45;
130 EXP_DEMAND_OFF CONSTANT INTEGER := 46;
131
132 NUM_OF_TYPES CONSTANT INTEGER := 46;
133
134 NO_INT_SHIPMENT CONSTANT NUMBER := 0;
135 NODE_GL_FORECAST_ITEM CONSTANT NUMBER := 6;
136
137 /* global variable for number of buckets to display for the plan */
138 g_num_of_buckets NUMBER;
139 g_error_stmt VARCHAR2(200);
140
141 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
142 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
143 TYPE column_char IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
144
145 Procedure populate_horizontal_plan (
146 arg_query_id IN NUMBER,
147 arg_plan_id IN NUMBER,
148 arg_plan_organization_id IN NUMBER,
149 arg_plan_instance_id IN NUMBER,
150 arg_cutoff_date IN DATE,
151 arg_query_type IN NUMBER) IS
152
153 -- -------------------------------------------------
154 -- This cursor select number of buckets in the plan.
155 -- -------------------------------------------------
156 CURSOR plan_buckets IS
157 SELECT DECODE(arg_plan_id, -1, sysdate, trunc(plan_start_date)),
158 DECODE(arg_plan_id, -1, sysdate+365, trunc(curr_cutoff_date))
159 FROM msc_plans
160 WHERE plan_id = arg_plan_id;
161
162 -- -------------------------------------------------
163 -- This cursor selects the dates for the buckets.
164 -- -------------------------------------------------
165 CURSOR bucket_dates(p_start_date DATE, p_end_date DATE) IS
166 SELECT cal.calendar_date
167 FROM msc_calendar_dates cal,
168 msc_trading_partners tp
169 WHERE tp.sr_tp_id = arg_plan_organization_id
170 AND tp.sr_instance_id = arg_plan_instance_id
171 AND tp.calendar_exception_set_id = cal.exception_set_id
172 AND tp.partner_type = 3
173 AND tp.calendar_code = cal.calendar_code
174 AND tp.sr_instance_id = cal.sr_instance_id
175 AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
176 ORDER BY cal.calendar_date;
177
178 l_plan_start_date DATE;
179 l_plan_end_date DATE;
180
181 l_bucket_number NUMBER := 0;
182 l_bucket_date DATE;
183
184 last_date DATE;
185
186 CURSOR drp_snapshot_activity IS
187 SELECT
188 mfq.number5 item_id,
189 mfq.number6 org_id,
190 mfq.number3 inst_id,
191 DECODE(ms.order_type,
192 PURCHASE_ORDER, PURCHASE_ORDER_OFF,
193 PURCH_REQ,
194 decode(ms.source_organization_id, null, EXT_PURCH_REQ_OFF,
195 INT_PURCH_REQ_OFF),
196 WORK_ORDER, WIP_OFF,
197 PLANNED_ARRIVAL, PLANNED_ARRIVAL_OFF,
198 NONSTD_JOB, WIP_OFF,
199 RECEIPT_PURCH_ORDER, RECEIVING_OFF,
200 INTRANSIT_SHIPMENT,
201 decode(ms.source_organization_id, null, EXT_TRANSIT_OFF,
202 INT_TRANSIT_OFF),
203 INTRANSIT_RECEIPT, RECEIVING_OFF,
204 BEG_ON_HAND, BEG_ON_HAND_OFF,
205 PLANNED_ORDER,
206 decode(nvl(ms.source_organization_id, ms.organization_id),
207 ms.organization_id,
208 PLANNED_MAKE_OFF,
209 PLANNED_BUY_OFF)
210 ) offset,
211 DECODE(ms.order_type,
212 PURCHASE_ORDER, CURRENT_S_RECEIPT_OFF,
213 PURCH_REQ, CURRENT_S_RECEIPT_OFF,
214 WORK_ORDER, CURRENT_S_RECEIPT_OFF,
215 RECEIPT_PURCH_ORDER, CURRENT_S_RECEIPT_OFF,
216 INTRANSIT_SHIPMENT, CURRENT_S_RECEIPT_OFF,
217 INTRANSIT_RECEIPT, CURRENT_S_RECEIPT_OFF,
218 0 ) offset2,
219 decode(ms.order_type,
220 PLANNED_ARRIVAL, ms.source_organization_id,
221 PURCH_REQ, nvl(ms.source_organization_id, -1),
222 INTRANSIT_SHIPMENT, nvl(ms.source_organization_id,-2),
223 -1) sub_org_id,
224 nvl(ms.firm_date,ms.new_schedule_date) new_date,
225 ms.old_schedule_date old_date,
226 SUM(DECODE(msi.base_item_id,NULL,
227 DECODE(ms.disposition_status_type,2, 0,
228 nvl(ms.firm_quantity,ms.new_order_quantity)),
229 nvl(ms.firm_quantity,ms.new_order_quantity))) new_quantity,
230 SUM(NVL(ms.old_order_quantity,0)) quantity1,
231 SUM(DECODE(msi.base_item_id,NULL,
232 DECODE(ms.disposition_status_type,2, 0,
233 nvl(ms.firm_quantity,ms.new_order_quantity)),
234 nvl(ms.firm_quantity,ms.new_order_quantity)) *
235 nvl(msi.unit_weight,0)) weight,
236 SUM(DECODE(msi.base_item_id,NULL,
237 DECODE(ms.disposition_status_type,2, 0,
238 nvl(ms.firm_quantity,ms.new_order_quantity)),
239 nvl(ms.firm_quantity,ms.new_order_quantity)) *
240 nvl(msi.unit_volume,0)) volume,
241 sum(NVL(ms.old_order_quantity,0)*nvl(msi.unit_weight,0)) quantity2,
242 sum(NVL(ms.old_order_quantity,0)*nvl(msi.unit_volume,0)) quantity3
243 FROM msc_form_query mfq,
244 msc_system_items msi,
245 msc_supplies ms
246 WHERE ms.plan_id = msi.plan_id
247 AND ms.inventory_item_id = msi.inventory_item_id
248 AND ms.organization_id = msi.organization_id
249 AND ms.sr_instance_id = msi.sr_instance_id
250 AND msi.plan_id = mfq.number4
251 AND msi.inventory_item_id = mfq.number1
252 AND msi.organization_id = mfq.number2
253 AND msi.sr_instance_id = mfq.number3
254 AND mfq.query_id = arg_query_id
255 AND mfq.number7 <> NODE_GL_FORECAST_ITEM
256 AND (arg_query_type <> NO_INT_SHIPMENT or
257 (arg_query_type = NO_INT_SHIPMENT and
258 ms.order_type <> PLANNED_ARRIVAL and
259 not(ms.order_type = PURCH_REQ and ms.source_organization_id is not null)))
260 GROUP BY
261 mfq.number5,
262 mfq.number6,
263 mfq.number3,
264 DECODE(ms.order_type,
265 PURCHASE_ORDER, PURCHASE_ORDER_OFF,
266 PURCH_REQ,
267 decode(ms.source_organization_id, null, EXT_PURCH_REQ_OFF,
268 INT_PURCH_REQ_OFF),
269 WORK_ORDER, WIP_OFF,
270 PLANNED_ARRIVAL, PLANNED_ARRIVAL_OFF,
271 NONSTD_JOB, WIP_OFF,
272 RECEIPT_PURCH_ORDER, RECEIVING_OFF,
273 INTRANSIT_SHIPMENT,
274 decode(ms.source_organization_id, null, EXT_TRANSIT_OFF,
275 INT_TRANSIT_OFF),
276 INTRANSIT_RECEIPT, RECEIVING_OFF,
277 BEG_ON_HAND, BEG_ON_HAND_OFF,
278 PLANNED_ORDER,
279 decode(nvl(ms.source_organization_id, ms.organization_id),
280 ms.organization_id,
281 PLANNED_MAKE_OFF,
282 PLANNED_BUY_OFF)
283 ),
284 DECODE(ms.order_type,
285 PURCHASE_ORDER, CURRENT_S_RECEIPT_OFF,
286 PURCH_REQ, CURRENT_S_RECEIPT_OFF,
287 WORK_ORDER, CURRENT_S_RECEIPT_OFF,
288 RECEIPT_PURCH_ORDER, CURRENT_S_RECEIPT_OFF,
289 INTRANSIT_SHIPMENT, CURRENT_S_RECEIPT_OFF,
290 INTRANSIT_RECEIPT, CURRENT_S_RECEIPT_OFF,
291 0),
292 decode(ms.order_type,
293 PLANNED_ARRIVAL, ms.source_organization_id,
294 PURCH_REQ, nvl(ms.source_organization_id, -1),
295 INTRANSIT_SHIPMENT, nvl(ms.source_organization_id,-2), -1),
296 nvl(ms.firm_date,ms.new_schedule_date),
297 ms.old_schedule_date
298 UNION ALL
299 SELECT mfq.number5 item_id,
300 mfq.number6 org_id,
301 mfq.number3 inst_id,
302 DECODE(md.origination_type,
303 CONS_KIT_DEMAND, KIT_DEMAND_OFF,
304 WORK_ORDER_DEMAND, UNC_KIT_DEMAND_OFF,
305 FORECAST, FORECAST_OFF,
306 SALES_ORDER,
307 decode(nvl(md.demand_source_type,2), 2, EXT_SALES_ORDER_OFF,
308 INT_SALES_ORDER_OFF),
309 PLANNED_ORDER_DEMAND,
310 decode(nvl(md.source_organization_id,md.organization_id),
311 md.organization_id,
312 UNC_KIT_DEMAND_OFF,
313 REQUEST_SHIPMENT_OFF),
314 EXPIRE_LOT_DEMAND, EXPIRE_LOT_OFF,
315 INTER_ORG_DEMAND, UNC_OTHER_DEMAND_OFF,
316 PLANNED_ORDER_SCRAP, SCRAP_DEMAND_OFF,
317 DISCRETE_JOB_SCRAP, SCRAP_DEMAND_OFF,
318 PURCHASE_ORDER_SCRAP, SCRAP_DEMAND_OFF,
319 PURCH_REQ_SCRAP, SCRAP_DEMAND_OFF,
320 RECEIPT_PO_SCRAP, SCRAP_DEMAND_OFF,
321 INTRANSIT_SHIPMENT_SCRAP, SCRAP_DEMAND_OFF,
322 OTHER_DEMAND_OFF) offset,
323 DECODE(md.origination_type,
324 WORK_ORDER_DEMAND, KIT_DEMAND_OFF,
325 SALES_ORDER, decode(md.demand_source_type, 8,
326 REQUEST_SHIPMENT_OFF,EXP_DEMAND_OFF),
327 FORECAST, EXP_DEMAND_OFF,
328 INTER_ORG_DEMAND, OTHER_DEMAND_OFF,
329 0) offset2,
330 decode(md.origination_type, SALES_ORDER,
331 decode(nvl(md.demand_source_type,2), 8, md.source_organization_id, -1),
332 PLANNED_ORDER_DEMAND,
333 decode(nvl(md.source_organization_id,
334 md.organization_id), md.organization_id,
335 -1,
336 md.source_organization_id),
337 -1) sub_org_id,
338 nvl(md.firm_date,md.using_assembly_demand_date) new_date,
339 nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date) old_date, -- unconstr date
340 SUM(DECODE(md.origination_type,
341 29,nvl(md.probability,1)*
342 nvl(md.firm_quantity,md.using_requirement_quantity),
343 nvl(md.firm_quantity,md.using_requirement_quantity))) new_quantity,
344 SUM(decode(md.origination_type,
345 29, nvl(md.unmet_quantity,0),
346 30, decode(md.demand_source_type, 8,
347 nvl(old_using_requirement_quantity,0),
348 nvl(md.unmet_quantity,0)),
349 using_requirement_quantity)) quantity1, -- unconstrain qty
350 SUM(DECODE(md.origination_type,
351 29,nvl(md.probability,1)*
352 nvl(md.firm_quantity,md.using_requirement_quantity),
353 nvl(md.firm_quantity,md.using_requirement_quantity)) *
354 nvl(msi.unit_weight,0)) weight,
355 SUM(DECODE(md.origination_type,
356 29,nvl(md.probability,1)*
357 nvl(md.firm_quantity,md.using_requirement_quantity),
358 nvl(md.firm_quantity,md.using_requirement_quantity)) *
359 nvl(msi.unit_volume,0)) volume,
360 SUM(decode(md.origination_type,
361 29, nvl(md.unmet_quantity,0),
362 30, decode(md.demand_source_type, 8,
363 nvl(old_using_requirement_quantity,0),
364 nvl(md.unmet_quantity,0)),
365 using_requirement_quantity) *
366 nvl(msi.unit_weight,0)) quantity2, -- unconstr weight
367 SUM(decode(md.origination_type,
368 29, nvl(md.unmet_quantity,0),
369 30, decode(md.demand_source_type, 8,
370 nvl(old_using_requirement_quantity,0),
371 nvl(md.unmet_quantity,0)),
372 using_requirement_quantity) *
373 nvl(msi.unit_volume,0)) quantity3 -- unconstr volume
374 FROM msc_form_query mfq,
375 msc_system_items msi,
376 msc_demands md
377 WHERE md.plan_id = mfq.number4
378 AND md.inventory_item_id = mfq.number1
379 AND md.organization_id = mfq.number2
380 AND md.sr_instance_id = mfq.number3
381 AND msi.plan_id = md.plan_id
382 AND msi.inventory_item_id = md.inventory_item_id
383 AND msi.organization_id = md.organization_id
384 AND msi.sr_instance_id = md.sr_instance_id
385 AND mfq.query_id = arg_query_id
386 AND mfq.number7 <> NODE_GL_FORECAST_ITEM
387 AND md.organization_id <> -1 -- no global forecast rows
388 AND (arg_query_type <> NO_INT_SHIPMENT or
389 (arg_query_type = NO_INT_SHIPMENT and
390 not(md.origination_type = PLANNED_ORDER_DEMAND and
391 nvl(md.source_organization_id,md.organization_id) <> md.organization_id) and
392 not(md.origination_type = SALES_ORDER and
393 nvl(md.demand_source_type,2) = 8)))
394 AND not exists (
395 select 'cancelled IR'
396 from msc_supplies mr
397 where md.origination_type = 30
398 and md.disposition_id = mr.transaction_id
399 and md.plan_id = mr.plan_id
400 and md.sr_instance_id = mr.sr_instance_id
401 and mr.disposition_status_type = 2)
402 GROUP BY
403 mfq.number5,
404 mfq.number6,
405 mfq.number3,
406 DECODE(md.origination_type,
407 CONS_KIT_DEMAND, KIT_DEMAND_OFF,
408 WORK_ORDER_DEMAND, UNC_KIT_DEMAND_OFF,
409 FORECAST, FORECAST_OFF,
410 SALES_ORDER,
411 decode(nvl(md.demand_source_type,2), 2, EXT_SALES_ORDER_OFF,
412 INT_SALES_ORDER_OFF),
413 PLANNED_ORDER_DEMAND,
414 decode(nvl(md.source_organization_id,md.organization_id),
415 md.organization_id,
416 UNC_KIT_DEMAND_OFF,
417 REQUEST_SHIPMENT_OFF),
418 EXPIRE_LOT_DEMAND, EXPIRE_LOT_OFF,
419 INTER_ORG_DEMAND, UNC_OTHER_DEMAND_OFF,
420 PLANNED_ORDER_SCRAP, SCRAP_DEMAND_OFF,
421 DISCRETE_JOB_SCRAP, SCRAP_DEMAND_OFF,
422 PURCHASE_ORDER_SCRAP, SCRAP_DEMAND_OFF,
423 PURCH_REQ_SCRAP, SCRAP_DEMAND_OFF,
424 RECEIPT_PO_SCRAP, SCRAP_DEMAND_OFF,
425 INTRANSIT_SHIPMENT_SCRAP, SCRAP_DEMAND_OFF,
426 OTHER_DEMAND_OFF),
427 DECODE(md.origination_type,
428 WORK_ORDER_DEMAND, KIT_DEMAND_OFF,
429 SALES_ORDER, decode(md.demand_source_type, 8,
430 REQUEST_SHIPMENT_OFF,EXP_DEMAND_OFF),
431 FORECAST, EXP_DEMAND_OFF,
432 INTER_ORG_DEMAND, OTHER_DEMAND_OFF,
433 0),
434 decode(md.origination_type, SALES_ORDER,
435 decode(nvl(md.demand_source_type,2), 8, md.source_organization_id, -1),
436 PLANNED_ORDER_DEMAND,
437 decode(nvl(md.source_organization_id,md.organization_id),
438 md.organization_id,
439 -1,
440 md.source_organization_id),
441 -1),
442 nvl(md.old_using_assembly_demand_date,md.using_assembly_demand_date),
443 nvl(md.firm_date,md.using_assembly_demand_date)
444 UNION ALL -- for planned shipments and outbound in transit
445 --5084210, pos from purchase order/purchase req with supplier modeled as org
446 SELECT mfq.number5 item_id,
447 mfq.number6 org_id,
448 mfq.number3 inst_id,
449 decode(ms.order_type, PLANNED_ARRIVAL, PLANNED_SHIPMENT_OFF,
450 PURCHASE_ORDER,PLANNED_SHIPMENT_OFF,
451 PURCH_REQ,PLANNED_SHIPMENT_OFF,
452 INTRANSIT_SHIPMENT, OUTBOUND_OFF) offset,
453 0 offset2,
454 ms.organization_id sub_org_id,
455 ms.new_ship_date new_date,
456 ms.new_ship_date old_date,
457 sum(nvl(ms.firm_quantity,ms.new_order_quantity)) new_quantity,
458 0 quantity1,
459 sum(nvl(ms.firm_quantity,ms.new_order_quantity) *
460 nvl(msi.unit_weight,0)) weight,
461 sum(nvl(ms.firm_quantity,ms.new_order_quantity) *
462 nvl(msi.unit_volume,0)) volume,
463 0 quantity2,
464 0 quantity3
465 FROM msc_system_items msi,
466 msc_supplies ms,
467 msc_form_query mfq
468 WHERE msi.plan_id = ms.plan_id
469 AND msi.inventory_item_id = ms.inventory_item_id
470 AND msi.organization_id = ms.source_organization_id
471 AND msi.sr_instance_id = ms.source_sr_instance_id
472 AND ms.order_type in (PLANNED_ARRIVAL,INTRANSIT_SHIPMENT, PURCHASE_ORDER,PURCH_REQ)
473 AND ms.plan_id = mfq.number4
474 AND ms.inventory_item_id = mfq.number1
475 AND ms.source_organization_id = mfq.number2
476 AND ms.source_sr_instance_id = mfq.number3
477 AND mfq.query_id = arg_query_id
478 AND mfq.number7 <> NODE_GL_FORECAST_ITEM
479 AND arg_query_type <> NO_INT_SHIPMENT
480 and ms.source_organization_id <> ms.organization_id
481 and (ms.order_type <> PURCH_REQ or
482 (ms.order_type = PURCH_REQ and ms.supplier_id is not null))
483 GROUP BY
484 mfq.number5,
485 mfq.number6,
486 mfq.number3,
487 decode(ms.order_type, PLANNED_ARRIVAL, PLANNED_SHIPMENT_OFF,
488 PURCHASE_ORDER,PLANNED_SHIPMENT_OFF,
489 PURCH_REQ,PLANNED_SHIPMENT_OFF,
490 INTRANSIT_SHIPMENT, OUTBOUND_OFF),
491 ms.organization_id,
492 ms.new_ship_date
493 UNION ALL -- for requested arrival. in msc_demands, for request shipment
494 SELECT mfq.number5 item_id, -- source_org_id actually store dest org id
495 mfq.number6 org_id, -- while org_id store source org id
496 mfq.number3 inst_id,
497 REQUEST_ARRIVAL_OFF offset,
498 0 offset2,
499 md.organization_id sub_org_id,
500 nvl(md.firm_date,md.planned_inbound_due_date) new_date,
501 nvl(md.firm_date,md.planned_inbound_due_date) old_date,
502 sum(nvl(md.firm_quantity,
503 nvl(md.old_using_requirement_quantity,md.using_requirement_quantity))) new_quantity,
504 0 quantity1,
505 sum(nvl(md.firm_quantity,
506 nvl(md.old_using_requirement_quantity,md.using_requirement_quantity)) *
507 nvl(msi.unit_weight,0)) weight,
508 sum(nvl(md.firm_quantity,nvl(md.old_using_requirement_quantity,md.using_requirement_quantity)) *
509 nvl(msi.unit_volume,0)) volume,
510 0 quantity2,
511 0 quantity3
512 FROM msc_form_query mfq,
513 msc_system_items msi,
514 msc_demands md
515 WHERE md.plan_id = mfq.number4
516 AND md.inventory_item_id = mfq.number1
517 AND md.source_organization_id = mfq.number2
518 AND md.sr_instance_id = mfq.number3
519 AND ((md.origination_type = PLANNED_ORDER_DEMAND and
520 md.source_organization_id <> md.organization_id ) or
521 (md.origination_type = 30 and md.demand_source_type =8 ))
522 AND msi.plan_id = md.plan_id
523 AND msi.inventory_item_id = md.inventory_item_id
524 AND msi.organization_id = md.source_organization_id
525 AND msi.sr_instance_id = md.sr_instance_id
526 AND mfq.query_id = arg_query_id
527 AND mfq.number7 <> NODE_GL_FORECAST_ITEM
528 AND arg_query_type <> NO_INT_SHIPMENT
529 GROUP BY
530 mfq.number5,
531 mfq.number6,
532 mfq.number3,
533 REQUEST_ARRIVAL_OFF,
534 md.organization_id,
535 nvl(md.firm_date,md.planned_inbound_due_date)
536 UNION ALL
537 SELECT mfq.number5 item_id,
538 mfq.number6 org_id,
539 mfq.number3 inst_id,
540 SAFETY_STOCK_OFF offset,
541 0 offset2,
542 safety.organization_id sub_org_id,
543 safety.period_start_date new_date,
544 safety.period_start_date old_date,
545 sum(safety.safety_stock_quantity) new_quantity,
546 0 quantity1,
547 sum(safety.safety_stock_quantity * nvl(msi.unit_weight,0)) weight,
548 sum(safety.safety_stock_quantity * nvl(msi.unit_volume,0)) volume,
549 0 quantity2,
550 0 quantity3
551 FROM msc_safety_stocks safety,
552 msc_system_items msi,
553 msc_form_query mfq
554 WHERE trunc(safety.period_start_date) <= last_date
555 AND safety.organization_id = mfq.number2
556 AND safety.sr_instance_id = mfq.number3
557 AND safety.plan_id = mfq.number4
558 AND safety.inventory_item_id = mfq.number1
559 AND mfq.query_id = arg_query_id
560 AND mfq.number7 <> NODE_GL_FORECAST_ITEM
561 AND msi.plan_id = safety.plan_id
562 AND msi.inventory_item_id = safety.inventory_item_id
563 AND msi.organization_id = safety.organization_id
564 AND msi.sr_instance_id = safety.sr_instance_id
565 GROUP BY mfq.number5,
566 mfq.number6,
567 mfq.number3,
568 safety.period_start_date,
569 safety.organization_id
570 UNION ALL -- for target and max qty
571 SELECT mfq.number5 item_id,
572 mfq.number6 org_id,
573 mfq.number3 inst_id,
574 MAX_QTY_OFF offset,
575 TARGET_QTY_OFF offset2,
576 mil.organization_id sub_org_id,
577 mil.inventory_date new_date,
578 mil.inventory_date old_date,
579 sum(nvl(mil.max_quantity,0)) new_quantity,
580 sum(nvl(mil.target_quantity,0)) quantity1,
581 sum(nvl(mil.max_quantity,0) * nvl(msi.unit_weight,0)) weight,
582 sum(nvl(mil.max_quantity,0) * nvl(msi.unit_volume,0)) volume,
583 sum(nvl(mil.target_quantity,0) * nvl(msi.unit_weight,0)) quantity2,
584 sum(nvl(mil.target_quantity,0) * nvl(msi.unit_volume,0)) quantity3
585 FROM msc_inventory_levels mil,
586 msc_system_items msi,
587 msc_form_query mfq
588 WHERE trunc(mil.inventory_date) <= last_date
589 AND mil.organization_id = mfq.number2
590 AND mil.sr_instance_id = mfq.number3
591 AND mil.plan_id = mfq.number4
592 AND mil.inventory_item_id = mfq.number1
593 AND mfq.query_id = arg_query_id
594 AND mfq.number7 <> NODE_GL_FORECAST_ITEM
595 AND msi.plan_id = mil.plan_id
596 AND msi.inventory_item_id = mil.inventory_item_id
597 AND msi.organization_id = mil.organization_id
598 AND msi.sr_instance_id = mil.sr_instance_id
599 GROUP BY mfq.number5,mfq.number6,mfq.number3,
600 mil.inventory_date, mil.organization_id
601 UNION ALL
602 --------------------------------------------------------------------
603 -- This select will ensure that all selected items get into cursor
604 -- even though they do not have any activity
605 ---------------------------------------------------------------------
606 SELECT mfq.number5 item_id,
607 mfq.number6 org_id,
608 mfq.number3 inst_id,
609 BEG_ON_HAND_OFF offset,
610 0 offset2,
611 -1 sub_org_id,
612 to_date(1, 'J') new_date,
613 to_date(1, 'J') old_date,
614 0 new_quantity,
615 0 quantity1,
616 0 weight,
617 0 volume,
618 0 quantity2,
619 0 quantity3
620 FROM msc_form_query mfq
621 WHERE mfq.query_id = arg_query_id
622 AND mfq.number7 <> NODE_GL_FORECAST_ITEM
623 ORDER BY
624 1, 2, 3,7,8,4,5,6;
625
626 TYPE drp_activity IS RECORD
627 (item_id NUMBER,
628 org_id NUMBER,
629 inst_id NUMBER,
630 offset NUMBER,
631 offset2 NUMBER,
632 sub_org_id NUMBER,
633 new_date DATE,
634 old_date DATE,
635 new_quantity NUMBER,
636 quantity1 NUMBER,
637 weight NUMBER,
638 volume NUMBER,
639 quantity2 NUMBER,
640 quantity3 NUMBER);
641
642 activity_rec drp_activity;
643
644 var_dates calendar_date; -- Holds the start dates of buckets
645 last_item_id NUMBER := -10;
646 last_org_id NUMBER := -10;
647 last_inst_id NUMBER := -10;
648
649 TYPE bucket_rec_type is RECORD (
650 quantity number,
651 weight number,
652 volume number
653 );
654
655 TYPE row_rec IS TABLE OF bucket_rec_type INDEX BY BINARY_INTEGER;
656
657 TYPE header_rec_type is RECORD (
658 row_type number,
659 sub_org_id number
660 );
661
662 TYPE header_rec IS TABLE OF header_rec_type INDEX BY BINARY_INTEGER;
663
664 row_detail row_rec; -- non enterprise view
665 row_header_type column_number;
666 sub_row_detail row_rec;
667 sub_row_header header_rec;
668
669 bucket_counter BINARY_INTEGER := 0; -- Id of the current bucket
670 old_bucket_counter BINARY_INTEGER := 0;
671 counter BINARY_INTEGER := 0;
672
673 PROCEDURE sum_up_ss(offset IN NUMBER,
674 bucket IN NUMBER) IS
675 location number;
676 v_ss_qty number :=0;
677 v_ss_wt number :=0;
678 v_ss_vl number :=0;
679 i number;
680 BEGIN
681 g_error_stmt := 'Debug - sum_up_ss, bkt='||bucket||',offset='||offset;
682
683 location := (offset -1) * (g_num_of_buckets +1) + bucket;
684 i := nvl(sub_row_header.last,0);
685 FOR a in 1 .. i LOOP
686 if sub_row_header(a).row_type= row_header_type(offset) and
687 sub_row_header(a).sub_org_id <> -1 then
688 /*
689 if sub_row_detail((bucket+(a-1)*g_num_of_buckets)).quantity <> 0 then
690 dbms_output.put_line(sub_row_header(a).row_type||','||
691 sub_row_detail(bucket+(a-1)*g_num_of_buckets).quantity);
692 end if;
693 */
694 v_ss_qty := v_ss_qty +
695 sub_row_detail(bucket+(a-1)*g_num_of_buckets).quantity;
696 v_ss_wt := v_ss_wt +
697 sub_row_detail(bucket+(a-1)*g_num_of_buckets).weight;
698 v_ss_vl := v_ss_vl +
699 sub_row_detail(bucket+(a-1)*g_num_of_buckets).volume;
700 end if;
701 END LOOP;
702
703 row_detail(location).quantity := v_ss_qty;
704 row_detail(location).weight := v_ss_wt;
705 row_detail(location).volume := v_ss_vl;
706
707 -- also modify enterprise view
708
709 location := offset * (g_num_of_buckets +1);
710 row_detail(location).quantity := v_ss_qty;
711 row_detail(location).weight := v_ss_wt;
712 row_detail(location).volume := v_ss_vl;
713
714 /*
715 if v_ss_qty <> 0 then
716 dbms_output.put_line('sum= '||offset||','||location||','||v_ss_qty);
717 end if;
718 */
719 END sum_up_ss;
720
721 FUNCTION get_row_detail(offset IN NUMBER,
722 bucket IN NUMBER,
723 data_type IN NUMBER) RETURN NUMBER IS
724 location number;
725 BEGIN
726 location := (offset -1) * (g_num_of_buckets +1) + bucket;
727 if data_type = 1 then
728 return row_detail(location).quantity;
729 elsif data_type = 2 then
730 return row_detail(location).weight;
731 else
732 return row_detail(location).volume;
733 end if;
734
735 END get_row_detail;
736
737 PROCEDURE add_sub_rows(bucket IN NUMBER,
738 offset IN NUMBER,
739 sub_org_id IN NUMBER,
740 quantity IN NUMBER,
741 weight IN NUMBER,
742 volume IN NUMBER) IS
743 row_exist boolean :=false;
744 i number;
745 new_offset number;
746 row_type number;
747 location number;
748 BEGIN
749 g_error_stmt := 'Debug - add_sub_rows - 10';
750
751 if offset in (INT_SALES_ORDER_OFF, PLANNED_SHIPMENT_OFF) then
752 new_offset := SHIPMENT_OFF;
753 elsif offset in (INT_TRANSIT_OFF,INT_PURCH_REQ_OFF,PLANNED_ARRIVAL_OFF) then
754 new_offset :=ARRIVAL_OFF;
755 else
756 new_offset := offset;
757 end if;
758 row_type := row_header_type(new_offset);
759
760 for a in 1..nvl(sub_row_header.last,0) loop
761 if sub_row_header(a).row_type = row_type and
762 sub_row_header(a).sub_org_id = sub_org_id then
763 row_exist := true;
764 i := a;
765 exit;
766 end if;
767 end loop;
768
769 if not(row_exist) then
770 -- initialize sub rows first
771 i := nvl(sub_row_header.last,0)+1;
772 sub_row_header(i).row_type := row_type;
773 sub_row_header(i).sub_org_id := sub_org_id;
774 for a in 1..g_num_of_buckets loop
775 sub_row_detail(a+(i-1)*g_num_of_buckets).quantity :=0;
776 sub_row_detail(a+(i-1)*g_num_of_buckets).weight :=0;
777 sub_row_detail(a+(i-1)*g_num_of_buckets).volume :=0;
778 end loop;
779 end if;
780
781 location := (i-1)*g_num_of_buckets+bucket;
782 if offset in (MAX_QTY_OFF, TARGET_QTY_OFF, SAFETY_STOCK_OFF) then
783 -- set the qty for all the buckets after bucket date
784 --dbms_output.put_line('add: '|| location||','||quantity);
785 for a in 0..g_num_of_buckets-bucket loop
786 sub_row_detail(a+location).quantity := quantity;
787 sub_row_detail(a+location).weight := weight ;
788 sub_row_detail(a+location).volume := volume;
789 end loop;
790
791 else
792 sub_row_detail(location).quantity :=
793 sub_row_detail(location).quantity + quantity;
794 sub_row_detail(location).weight :=
795 sub_row_detail(location).weight + weight;
796 sub_row_detail(location).volume :=
797 sub_row_detail(location).volume + volume;
798 end if;
799
800 END add_sub_rows;
801
802
803 PROCEDURE add_to_plan(bucket IN NUMBER,
804 offset IN NUMBER,
805 quantity IN NUMBER,
806 weight IN NUMBER,
807 volume IN NUMBER) IS
808 location number;
809 BEGIN
810 g_error_stmt := 'Debug - add_to_plan, bkt='||bucket||',offset='||offset;
811 if quantity = 0 or offset is null or
812 offset in (MAX_QTY_OFF, TARGET_QTY_OFF, SAFETY_STOCK_OFF) then
813 return;
814 end if;
815
816 location := (offset -1) * (g_num_of_buckets +1) + bucket;
817
818 /*
819 if offset in (MAX_QTY_OFF, TARGET_QTY_OFF, SAFETY_STOCK_OFF) then
820 -- set the qty for all the buckets after bucket date
821 for a in 0..g_num_of_buckets-bucket loop
822 row_detail(a+location).quantity := quantity;
823 row_detail(a+location).weight := weight ;
824 row_detail(a+location).volume := volume;
825 end loop;
826
827 else
828 */
829 row_detail(location).quantity :=
830 row_detail(location).quantity + quantity;
831 row_detail(location).weight :=
832 row_detail(location).weight + weight;
833 row_detail(location).volume :=
834 row_detail(location).volume + volume;
835 -- end if;
836
837 -- to store enterprise view to the last column
838 location := offset * (g_num_of_buckets +1);
839
840 if offset in (UNC_PAB_OFF, PAB_OFF, POH_OFF) then
841 -- MAX_QTY_OFF, TARGET_QTY_OFF, SAFETY_STOCK_OFF) then
842 row_detail(location).quantity := quantity;
843 row_detail(location).weight := weight;
844 row_detail(location).volume := volume;
845 else
846 row_detail(location).quantity :=
847 row_detail(location).quantity + quantity;
848 row_detail(location).weight :=
849 row_detail(location).weight + weight;
850 row_detail(location).volume :=
851 row_detail(location).volume + volume;
852 end if;
853 END add_to_plan;
854
855 PROCEDURE flush_item_plan(p_item_id IN NUMBER,
856 p_org_id IN NUMBER,
857 p_inst_id IN NUMBER) IS
858 total_reqs NUMBER := 0;
859 lot_quantity NUMBER := 0;
860 expired_qty NUMBER := 0;
861 expired_weight NUMBER := 0;
862 expired_volume NUMBER := 0;
863
864 atp_flag NUMBER :=2;
865 l_atp_qty_net MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
866 l_atp_weight_net MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
867 l_atp_volume_net MRP_ATP_PUB.number_arr := MRP_ATP_PUB.number_arr();
868 total_supply number;
869 committed_demand number;
870
871 cursor check_atp is
872 SELECT msi.calculate_atp
873 FROM msc_system_items msi,
874 msc_form_query mfq
875 WHERE msi.inventory_item_id = mfq.number1
876 AND msi.organization_id = mfq.number2
877 AND msi.plan_id = arg_plan_id
878 AND msi.sr_instance_id = mfq.number3
879 AND mfq.query_id = arg_query_id
880 and mfq.number5 = p_item_id
881 and mfq.number6 = p_org_id
882 and mfq.number3 = p_inst_id;
883
884 bkt_quantity column_number;
885 bkt_weight column_number;
886 bkt_volume column_number;
887 etp_bkt_quantity column_number;
888 etp_bkt_weight column_number;
889 etp_bkt_volume column_number;
890 etp_bkt_row_type column_number;
891 b number := 0;
892
893 BEGIN
894
895 g_error_stmt := 'Debug - flush_item_plan - 10';
896
897 OPEN check_atp;
898 FETCH check_atp INTO atp_flag;
899 CLOSE check_atp;
900
901 FOR a IN 1..g_num_of_buckets LOOP
902 g_error_stmt := 'Debug - flush_item_plan - 20 - loop'||a;
903 -- ----------------------------
904 -- Projected available balance.
905 -- ----------------------------
906 g_error_stmt := 'Debug - flush_item_plan - 40 - loop'||a;
907
908 IF a = 1 THEN
909 add_to_plan(a,
910 UNC_PAB_OFF,
911 get_row_detail(BEG_ON_HAND_OFF,a,1)+
912 get_row_detail(TOTAL_SUPPLY_OFF,a,1)-
913 get_row_detail(TOTAL_UNC_DEMAND_OFF,a,1),
914 get_row_detail(BEG_ON_HAND_OFF,a,2)+
915 get_row_detail(TOTAL_SUPPLY_OFF,a,2)-
916 get_row_detail(TOTAL_UNC_DEMAND_OFF,a,2),
917 get_row_detail(BEG_ON_HAND_OFF,a,3)+
918 get_row_detail(TOTAL_SUPPLY_OFF,a,3)-
919 get_row_detail(TOTAL_UNC_DEMAND_OFF,a,3));
920
921 add_to_plan(a,
922 PAB_OFF,
923 get_row_detail(BEG_ON_HAND_OFF,a,1)+
924 get_row_detail(TOTAL_SUPPLY_OFF,a,1)-
925 get_row_detail(TOTAL_DEMAND_OFF,a,1),
926 get_row_detail(BEG_ON_HAND_OFF,a,2)+
927 get_row_detail(TOTAL_SUPPLY_OFF,a,2)-
928 get_row_detail(TOTAL_DEMAND_OFF,a,2),
929 get_row_detail(BEG_ON_HAND_OFF,a,3)+
930 get_row_detail(TOTAL_SUPPLY_OFF,a,3)-
931 get_row_detail(TOTAL_DEMAND_OFF,a,3));
932
933 ELSE
934
935 add_to_plan(a,
936 UNC_PAB_OFF,
937 get_row_detail(UNC_PAB_OFF,a-1,1)+
938 get_row_detail(BEG_ON_HAND_OFF,a,1)+
939 get_row_detail(TOTAL_SUPPLY_OFF,a,1)-
940 get_row_detail(TOTAL_UNC_DEMAND_OFF,a,1),
941 get_row_detail(UNC_PAB_OFF,a-1,2)+
942 get_row_detail(BEG_ON_HAND_OFF,a,2)+
943 get_row_detail(TOTAL_SUPPLY_OFF,a,2)-
944 get_row_detail(TOTAL_UNC_DEMAND_OFF,a,2),
945 get_row_detail(UNC_PAB_OFF,a-1,3)+
946 get_row_detail(BEG_ON_HAND_OFF,a,3)+
947 get_row_detail(TOTAL_SUPPLY_OFF,a,3)-
948 get_row_detail(TOTAL_UNC_DEMAND_OFF,a,3));
949 add_to_plan(a,
950 PAB_OFF,
951 get_row_detail(PAB_OFF,a-1,1)+
952 get_row_detail(BEG_ON_HAND_OFF,a,1)+
953 get_row_detail(TOTAL_SUPPLY_OFF,a,1)-
954 get_row_detail(TOTAL_DEMAND_OFF,a,1),
955 get_row_detail(PAB_OFF,a-1,2)+
956 get_row_detail(BEG_ON_HAND_OFF,a,2)+
957 get_row_detail(TOTAL_SUPPLY_OFF,a,2)-
958 get_row_detail(TOTAL_DEMAND_OFF,a,2),
959 get_row_detail(PAB_OFF,a-1,3)+
960 get_row_detail(BEG_ON_HAND_OFF,a,3)+
961 get_row_detail(TOTAL_SUPPLY_OFF,a,3)-
962 get_row_detail(TOTAL_DEMAND_OFF,a,3));
963 END IF;
964
965 -- ------------------
966 -- Projected on hand.
967 -- ------------------
968 g_error_stmt := 'Debug - flush_item_plan - 50 - loop'||a;
969
970 IF a = 1 THEN
971 add_to_plan(a,
972 POH_OFF,
973 get_row_detail(BEG_ON_HAND_OFF,a,1)+
974 get_row_detail(CURRENT_S_RECEIPT_OFF,a,1)-
975 get_row_detail(TOTAL_DEMAND_OFF,a,1),
976 get_row_detail(BEG_ON_HAND_OFF,a,2)+
977 get_row_detail(CURRENT_S_RECEIPT_OFF,a,2)-
978 get_row_detail(TOTAL_DEMAND_OFF,a,2),
979 get_row_detail(BEG_ON_HAND_OFF,a,3)+
980 get_row_detail(CURRENT_S_RECEIPT_OFF,a,3)-
981 get_row_detail(TOTAL_DEMAND_OFF,a,3));
982 ELSIF a = 2 THEN
983 add_to_plan(a,
984 POH_OFF,
985 get_row_detail(POH_OFF,a-1,1)+
986 get_row_detail(BEG_ON_HAND_OFF,a,1)+
987 get_row_detail(CURRENT_S_RECEIPT_OFF,a,1)-
988 get_row_detail(TOTAL_DEMAND_OFF,a,1),
989 get_row_detail(POH_OFF,a-1,2)+
990 get_row_detail(BEG_ON_HAND_OFF,a,2)+
991 get_row_detail(CURRENT_S_RECEIPT_OFF,a,2)-
992 get_row_detail(TOTAL_DEMAND_OFF,a,2),
993 get_row_detail(POH_OFF,a-1,3)+
994 get_row_detail(BEG_ON_HAND_OFF,a,3)+
995 get_row_detail(CURRENT_S_RECEIPT_OFF,a,3)-
996 get_row_detail(TOTAL_DEMAND_OFF,a,3));
997 ELSE
998 add_to_plan(a,
999 POH_OFF,
1000 get_row_detail(POH_OFF,a-1,1)+
1001 get_row_detail(CURRENT_S_RECEIPT_OFF,a,1)-
1002 get_row_detail(TOTAL_DEMAND_OFF,a,1),
1003 get_row_detail(POH_OFF,a-1,2)+
1004 get_row_detail(CURRENT_S_RECEIPT_OFF,a,2)-
1005 get_row_detail(TOTAL_DEMAND_OFF,a,2),
1006 get_row_detail(POH_OFF,a-1,3)+
1007 get_row_detail(CURRENT_S_RECEIPT_OFF,a,3)-
1008 get_row_detail(TOTAL_DEMAND_OFF,a,3));
1009 END IF;
1010
1011 -- sum up safety stock from each org
1012 sum_up_ss(SAFETY_STOCK_OFF, a);
1013 sum_up_ss(MAX_QTY_OFF, a);
1014 sum_up_ss(TARGET_QTY_OFF, a);
1015
1016 END LOOP; -- FOR a IN 1..g_num_of_buckets LOOP
1017
1018 g_error_stmt := 'Debug - flush_item_plan - 60';
1019
1020 if atp_flag = 1 then -- only calculate atp when atp_flag is 1
1021
1022 IF l_atp_qty_net.count = 0 THEN
1023 l_atp_qty_net.Extend(g_num_of_buckets);
1024 l_atp_weight_net.Extend(g_num_of_buckets);
1025 l_atp_volume_net.Extend(g_num_of_buckets);
1026 END IF;
1027
1028 FOR a IN 1..g_num_of_buckets LOOP
1029
1030 total_supply := get_row_detail(TOTAL_SUPPLY_OFF,a,1)+
1031 get_row_detail(BEG_ON_HAND_OFF,a,1);
1032
1033 committed_demand := get_row_detail(TOTAL_UNC_DEMAND_OFF,a,1) -
1034 get_row_detail(FORECAST_OFF,a,1);
1035
1036 l_atp_qty_net(a) := total_supply - committed_demand;
1037 l_atp_weight_net(a) :=
1038 (get_row_detail(TOTAL_SUPPLY_OFF,a,2)+
1039 get_row_detail(BEG_ON_HAND_OFF,a,2)) -
1040 (get_row_detail(TOTAL_UNC_DEMAND_OFF,a,2) -
1041 get_row_detail(FORECAST_OFF,a,2));
1042 l_atp_volume_net(a) :=
1043 (get_row_detail(TOTAL_SUPPLY_OFF,a,3)+
1044 get_row_detail(BEG_ON_HAND_OFF,a,3)) -
1045 (get_row_detail(TOTAL_UNC_DEMAND_OFF,a,3) -
1046 get_row_detail(FORECAST_OFF,a,3));
1047
1048 END LOOP;
1049
1050 msc_atp_proc.atp_consume(l_atp_qty_net, g_num_of_buckets);
1051 msc_atp_proc.atp_consume(l_atp_weight_net, g_num_of_buckets);
1052 msc_atp_proc.atp_consume(l_atp_volume_net, g_num_of_buckets);
1053
1054 FOR a IN 1..g_num_of_buckets LOOP
1055 add_to_plan(a, ATP_OFF, l_atp_qty_net(a),
1056 l_atp_weight_net(a),
1057 l_atp_volume_net(a));
1058 END LOOP;
1059
1060 END IF;
1061
1062 g_error_stmt := 'Debug - flush_item_plan - 65';
1063
1064 FOR a in 1 .. NUM_OF_TYPES LOOP
1065 if row_header_type(a) <> -1 then
1066 -- only insert the row types which are to be shown in hp
1067 FOR bkt IN 1..g_num_of_buckets LOOP
1068 bkt_quantity(bkt) :=
1069 row_detail(bkt + (a-1)*(g_num_of_buckets+1)).quantity;
1070 bkt_weight(bkt) :=
1071 row_detail(bkt + (a-1)*(g_num_of_buckets+1)).weight;
1072 bkt_volume(bkt) :=
1073 row_detail(bkt + (a-1)*(g_num_of_buckets+1)).volume;
1074 END LOOP;
1075
1076 FORALL bkt IN 1..g_num_of_buckets
1077 INSERT INTO msc_drp_hori_plans(
1078 query_id,
1079 organization_id,
1080 sr_instance_id,
1081 inventory_item_id,
1082 row_type,
1083 sub_org_id,
1084 horizontal_plan_type,
1085 bucket_date,
1086 last_update_date,
1087 last_updated_by,
1088 creation_date,
1089 created_by,
1090 quantity,
1091 weight,
1092 volume)
1093 VALUES (
1094 arg_query_id,
1095 p_org_id,
1096 p_inst_id,
1097 p_item_id,
1098 row_header_type(a), -- row_type
1099 -1, -- sub org id
1100 1, -- non enterprise view
1101 var_dates(bkt),
1102 SYSDATE,
1103 -1,
1104 SYSDATE,
1105 -1,
1106 bkt_quantity(bkt),
1107 bkt_weight(bkt),
1108 bkt_volume(bkt));
1109
1110 -- construct enterprise data
1111 b := b+1;
1112 etp_bkt_quantity(b):=
1113 row_detail(a*(g_num_of_buckets+1)).quantity;
1114 etp_bkt_weight(b) :=
1115 row_detail(a*(g_num_of_buckets+1)).weight;
1116 etp_bkt_volume(b) :=
1117 row_detail(a*(g_num_of_buckets+1)).volume;
1118 etp_bkt_row_type(b) := row_header_type(a);
1119
1120 end if; -- if row_header_type(a) <> -1 then
1121 END LOOP; -- FOR a in 1 .. NUM_OF_TYPES LOOP
1122
1123 g_error_stmt := 'Debug - flush_item_plan - 70 ';
1124
1125 FOR a in 1 .. nvl(sub_row_header.last,0) LOOP
1126 FOR bkt IN 1..g_num_of_buckets LOOP
1127 bkt_quantity(bkt) :=
1128 sub_row_detail(bkt +(a-1)*(g_num_of_buckets)).quantity;
1129 bkt_weight(bkt) :=
1130 sub_row_detail(bkt +(a-1)*(g_num_of_buckets)).weight;
1131 bkt_volume(bkt) :=
1132 sub_row_detail(bkt +(a-1)*(g_num_of_buckets)).volume;
1133 END LOOP;
1134
1135 FORALL bkt IN 1..g_num_of_buckets
1136 INSERT INTO msc_drp_hori_plans(
1137 query_id,
1138 organization_id,
1139 sr_instance_id,
1140 inventory_item_id,
1141 row_type,
1142 sub_org_id,
1143 horizontal_plan_type,
1144 bucket_date,
1145 last_update_date,
1146 last_updated_by,
1147 creation_date,
1148 created_by,
1149 quantity,
1150 weight,
1151 volume)
1152 VALUES (
1153 arg_query_id,
1154 p_org_id,
1155 p_inst_id,
1156 p_item_id,
1157 sub_row_header(a).row_type,
1158 sub_row_header(a).sub_org_id,
1159 1, -- non enterprise view
1160 var_dates(bkt),
1161 SYSDATE,
1162 -1,
1163 SYSDATE,
1164 -1,
1165 bkt_quantity(bkt),
1166 bkt_weight(bkt),
1167 bkt_volume(bkt));
1168 END LOOP; -- FOR a in 1 .. NUM_OF_TYPES LOOP
1169
1170 g_error_stmt := 'Debug - flush_item_plan - 80';
1171
1172 -- insert enterprise data
1173 FORALL a in 1 .. nvl(etp_bkt_quantity.last, 0)
1174 INSERT INTO msc_drp_hori_plans(
1175 query_id,
1176 organization_id,
1177 sr_instance_id,
1178 inventory_item_id,
1179 row_type,
1180 sub_org_id,
1181 horizontal_plan_type,
1182 bucket_date,
1183 last_update_date,
1184 last_updated_by,
1185 creation_date,
1186 created_by,
1187 quantity,
1188 weight,
1189 volume)
1190 VALUES (
1191 arg_query_id,
1192 p_org_id,
1193 p_inst_id,
1194 p_item_id,
1195 etp_bkt_row_type(a), -- row_type
1196 -1, -- sub org id
1197 10, -- enterprise view
1198 SYSDATE, -- bucket date
1199 SYSDATE,
1200 -1,
1201 SYSDATE,
1202 -1,
1203 etp_bkt_quantity(a),
1204 etp_bkt_weight(a),
1205 etp_bkt_volume(a));
1206
1207 END flush_item_plan;
1208
1209
1210 BEGIN
1211
1212 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
1213
1214 OPEN plan_buckets;
1215 FETCH plan_buckets into l_plan_start_date, l_plan_end_date;
1216 CLOSE plan_buckets;
1217
1218 g_num_of_buckets := (l_plan_end_date + 1) - (l_plan_start_date - 1);
1219
1220 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
1221
1222 -- Initialize the bucket cells to 0.
1223
1224 FOR a IN 1..(NUM_OF_TYPES*(g_num_of_buckets +1)) LOOP
1225 row_detail(a).quantity := 0;
1226 row_detail(a).weight := 0;
1227 row_detail(a).volume := 0;
1228 END LOOP;
1229
1230 -- associate offset with rowtype
1231
1232 row_header_type(EXT_DEMAND_OFF) := HZ_EXT_DEMAND;
1233 row_header_type(EXT_SALES_ORDER_OFF) := HZ_EXT_SALES_ORDER;
1234 row_header_type(FORECAST_OFF) := HZ_FORECAST;
1235 row_header_type(KIT_DEMAND_OFF) := HZ_KIT_DEMAND;
1236 row_header_type(SHIPMENT_OFF) := HZ_SHIPMENT;
1237 row_header_type(INT_SALES_ORDER_OFF) := HZ_INT_SALES_ORDER;
1238 row_header_type(PLANNED_SHIPMENT_OFF) := HZ_PLANNED_SHIPMENT;
1239 row_header_type(EXPIRE_LOT_OFF) := HZ_EXPIRE_LOT;
1240 row_header_type(SCRAP_DEMAND_OFF) := HZ_SCRAP_DEMAND;
1241 row_header_type(OTHER_DEMAND_OFF) := HZ_OTHER_DEMAND;
1242 row_header_type(TOTAL_DEMAND_OFF) := HZ_TOTAL_DEMAND;
1243 row_header_type(REQUEST_SHIPMENT_OFF) := HZ_REQUEST_SHIPMENT;
1244 row_header_type(UNC_KIT_DEMAND_OFF) := HZ_UNC_KIT_DEMAND;
1245 row_header_type(UNC_SCRAP_DEMAND_OFF) := HZ_UNC_SCRAP_DEMAND;
1246 row_header_type(UNC_OTHER_DEMAND_OFF) := HZ_UNC_OTHER_DEMAND;
1247 row_header_type(TOTAL_UNC_DEMAND_OFF) := HZ_TOTAL_UNC_DEMAND;
1248 row_header_type(TOTAL_INT_SUPPLY_OFF) := HZ_TOTAL_INT_SUPPLY;
1249 row_header_type(BEG_ON_HAND_OFF) := HZ_BEG_ON_HAND;
1250 row_header_type(WIP_OFF) := HZ_WIP;
1251 row_header_type(RECEIVING_OFF) := HZ_RECEIVING;
1252 row_header_type(PLANNED_MAKE_OFF) := HZ_PLANNED_MAKE;
1253 row_header_type(TOTAL_EXT_SUPPLY_OFF) := HZ_TOTAL_EXT_SUPPLY;
1254 row_header_type(EXT_TRANSIT_OFF) := HZ_EXT_TRANSIT;
1255 row_header_type(PURCHASE_ORDER_OFF) := HZ_PURCHASE_ORDER;
1256 row_header_type(EXT_PURCH_REQ_OFF) := HZ_EXT_PURCH_REQ;
1257 row_header_type(PLANNED_BUY_OFF) := HZ_PLANNED_BUY;
1258 row_header_type(ARRIVAL_OFF) := HZ_ARRIVAL;
1259 row_header_type(INT_TRANSIT_OFF) := HZ_INT_TRANSIT;
1260 row_header_type(INT_PURCH_REQ_OFF) := HZ_INT_PURCH_REQ;
1261 row_header_type(PLANNED_ARRIVAL_OFF) := HZ_PLANNED_ARRIVAL;
1262 row_header_type(TOTAL_TRANSIT_OFF) := HZ_TOTAL_TRANSIT;
1263 row_header_type(TOTAL_PURCH_REQ_OFF) := HZ_TOTAL_PURCH_REQ;
1264 row_header_type(TOTAL_PLANNED_OFF) := HZ_TOTAL_PLANNED;
1265 row_header_type(TOTAL_SUPPLY_OFF) := HZ_TOTAL_SUPPLY;
1266 row_header_type(CURRENT_S_RECEIPT_OFF) := HZ_CURRENT_S_RECEIPT;
1267 row_header_type(POH_OFF) := HZ_POH;
1268 row_header_type(PAB_OFF) := HZ_PAB;
1269 row_header_type(UNC_PAB_OFF) := HZ_UNC_PAB;
1270 row_header_type(MAX_QTY_OFF) := HZ_MAX_QTY;
1271 row_header_type(TARGET_QTY_OFF) := HZ_TARGET_QTY;
1272 row_header_type(SAFETY_STOCK_OFF) := HZ_SAFETY_STOCK;
1273 row_header_type(INBOUND_OFF) := HZ_INBOUND;
1274 row_header_type(OUTBOUND_OFF) := HZ_OUTBOUND;
1275 row_header_type(ATP_OFF) := HZ_ATP;
1276 row_header_type(REQUEST_ARRIVAL_OFF) := HZ_REQUEST_ARRIVAL;
1277 row_header_type(EXP_DEMAND_OFF) := HZ_EXP_DEMAND;
1278
1279 last_date := arg_cutoff_date;
1280
1281
1282 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
1283 -- --------------------
1284 -- Get the bucket dates
1285 -- --------------------
1286 OPEN bucket_dates(l_plan_start_date-1, l_plan_end_date+1);
1287 LOOP
1288 FETCH bucket_dates INTO l_bucket_date;
1289 EXIT WHEN BUCKET_DATES%NOTFOUND;
1290 l_bucket_number := l_bucket_number + 1;
1291 var_dates(l_bucket_number) := l_bucket_date;
1292 END LOOP;
1293 CLOSE bucket_dates;
1294
1295 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
1296 bucket_counter := 2;
1297 old_bucket_counter := 2;
1298 activity_rec.item_id := 0;
1299 activity_rec.org_id := 0;
1300 activity_rec.inst_id := 0;
1301 activity_rec.offset := 0;
1302 activity_rec.offset2 := 0;
1303 activity_rec.sub_org_id := 0;
1304 activity_rec.new_date := sysdate;
1305 activity_rec.old_date := sysdate;
1306 activity_rec.new_quantity := 0;
1307 activity_rec.quantity1 := 0;
1308 activity_rec.weight :=0;
1309 activity_rec.volume :=0;
1310 activity_rec.quantity2 := 0;
1311 activity_rec.quantity3 := 0;
1312
1313 OPEN drp_snapshot_activity;
1314 LOOP
1315 FETCH drp_snapshot_activity INTO activity_rec;
1316 /*
1317 if activity_rec.offset2 in (EXP_DEMAND_OFF) then
1318 dbms_output.put_line(activity_rec.offset||','||activity_rec.sub_org_id||','||
1319 activity_rec.new_date||','||activity_rec.new_quantity||','||activity_rec.quantity1||','||activity_rec.old_date);
1320 end if;
1321 */
1322 IF ((drp_snapshot_activity%NOTFOUND) OR
1323 (activity_rec.item_id <> last_item_id) OR
1324 (activity_rec.org_id <> last_org_id) OR
1325 (activity_rec.inst_id <> last_inst_id)) AND
1326 last_item_id <> -10 THEN
1327
1328 flush_item_plan(last_item_id,
1329 last_org_id,
1330 last_inst_id);
1331
1332 bucket_counter := 2;
1333 old_bucket_counter := 2;
1334
1335 -- ------------------------------------
1336 -- Initialize the bucket cells to 0.
1337 -- ------------------------------------
1338
1339 FOR a IN 1..(NUM_OF_TYPES*(g_num_of_buckets +1)) LOOP
1340 row_detail(a).quantity := 0;
1341 row_detail(a).weight := 0;
1342 row_detail(a).volume := 0;
1343 END LOOP;
1344
1345 sub_row_header.delete;
1346 sub_row_detail.delete;
1347
1348 END IF; -- end of activity_rec.item_id <> last_item_id
1349
1350 EXIT WHEN drp_snapshot_activity%NOTFOUND;
1351
1352 -- find the correct bucket
1353 IF activity_rec.new_date >= var_dates(bucket_counter) THEN
1354 WHILE activity_rec.new_date >= var_dates(bucket_counter) AND
1355 bucket_counter <= g_num_of_buckets LOOP
1356 bucket_counter := bucket_counter + 1;
1357 END LOOP;
1358 END IF;
1359
1360 add_to_plan(bucket_counter -1,
1361 activity_rec.offset,
1362 activity_rec.new_quantity,
1363 activity_rec.weight,
1364 activity_rec.volume);
1365
1366 if activity_rec.offset in (EXT_SALES_ORDER_OFF, FORECAST_OFF) then
1367 add_to_plan(bucket_counter -1,
1368 EXT_DEMAND_OFF,
1369 activity_rec.new_quantity,
1370 activity_rec.weight,
1371 activity_rec.volume);
1372
1373 elsif activity_rec.offset in (INT_SALES_ORDER_OFF,
1374 PLANNED_SHIPMENT_OFF) then
1375 add_to_plan(bucket_counter -1,
1376 SHIPMENT_OFF,
1377 activity_rec.new_quantity,
1378 activity_rec.weight,
1379 activity_rec.volume);
1380
1381 elsif activity_rec.offset in (SCRAP_DEMAND_OFF, EXPIRE_LOT_OFF) then
1382 add_to_plan(bucket_counter -1,
1383 OTHER_DEMAND_OFF,
1384 activity_rec.new_quantity,
1385 activity_rec.weight,
1386 activity_rec.volume);
1387 if activity_rec.offset = SCRAP_DEMAND_OFF then
1388 add_to_plan(bucket_counter -1,
1389 UNC_OTHER_DEMAND_OFF,
1390 activity_rec.new_quantity,
1391 activity_rec.weight,
1392 activity_rec.volume);
1393 end if;
1394 end if;
1395
1396 if activity_rec.offset in (EXT_SALES_ORDER_OFF, FORECAST_OFF,
1397 INT_SALES_ORDER_OFF,PLANNED_SHIPMENT_OFF,
1398 SCRAP_DEMAND_OFF, EXPIRE_LOT_OFF,
1399 KIT_DEMAND_OFF,OTHER_DEMAND_OFF) then
1400
1401 add_to_plan(bucket_counter -1,
1402 TOTAL_DEMAND_OFF,
1403 activity_rec.new_quantity,
1404 activity_rec.weight,
1405 activity_rec.volume);
1406 end if;
1407
1408 if activity_rec.offset in (EXT_SALES_ORDER_OFF, FORECAST_OFF,
1409 REQUEST_SHIPMENT_OFF,
1410 SCRAP_DEMAND_OFF,
1411 UNC_KIT_DEMAND_OFF,
1412 UNC_OTHER_DEMAND_OFF,
1413 EXP_DEMAND_OFF) then
1414
1415 add_to_plan(bucket_counter -1,
1416 TOTAL_UNC_DEMAND_OFF,
1417 activity_rec.new_quantity,
1418 activity_rec.weight,
1419 activity_rec.volume);
1420 end if;
1421
1422 if activity_rec.offset in (BEG_ON_HAND_OFF, WIP_OFF,
1423 RECEIVING_OFF, PLANNED_MAKE_OFF) then
1424 add_to_plan(bucket_counter -1,
1425 TOTAL_INT_SUPPLY_OFF,
1426 activity_rec.new_quantity,
1427 activity_rec.weight,
1428 activity_rec.volume);
1429 elsif activity_rec.offset in (EXT_TRANSIT_OFF,PURCHASE_ORDER_OFF,
1430 EXT_PURCH_REQ_OFF, PLANNED_BUY_OFF) then
1431 add_to_plan(bucket_counter -1,
1432 TOTAL_EXT_SUPPLY_OFF,
1433 activity_rec.new_quantity,
1434 activity_rec.weight,
1435 activity_rec.volume);
1436 elsif activity_rec.offset in (INT_TRANSIT_OFF,
1437 INT_PURCH_REQ_OFF, PLANNED_ARRIVAL_OFF) then
1438 add_to_plan(bucket_counter -1,
1439 ARRIVAL_OFF,
1440 activity_rec.new_quantity,
1441 activity_rec.weight,
1442 activity_rec.volume);
1443 end if;
1444
1445 if activity_rec.offset in (INT_TRANSIT_OFF,EXT_TRANSIT_OFF ) then
1446 add_to_plan(bucket_counter -1,
1447 TOTAL_TRANSIT_OFF,
1448 activity_rec.new_quantity,
1449 activity_rec.weight,
1450 activity_rec.volume);
1451 elsif activity_rec.offset in (INT_PURCH_REQ_OFF,EXT_PURCH_REQ_OFF ) then
1452 add_to_plan(bucket_counter -1,
1453 TOTAL_PURCH_REQ_OFF,
1454 activity_rec.new_quantity,
1455 activity_rec.weight,
1456 activity_rec.volume);
1457 elsif activity_rec.offset in (PLANNED_BUY_OFF,PLANNED_MAKE_OFF,
1458 PLANNED_ARRIVAL_OFF ) then
1459 add_to_plan(bucket_counter -1,
1460 TOTAL_PLANNED_OFF,
1461 activity_rec.new_quantity,
1462 activity_rec.weight,
1463 activity_rec.volume);
1464 end if;
1465
1466 if activity_rec.offset in (WIP_OFF,RECEIVING_OFF,PLANNED_MAKE_OFF,
1467 EXT_TRANSIT_OFF,PURCHASE_ORDER_OFF,
1468 EXT_PURCH_REQ_OFF,PLANNED_BUY_OFF,
1469 INT_TRANSIT_OFF,INT_PURCH_REQ_OFF,
1470 PLANNED_ARRIVAL_OFF ) then
1471 add_to_plan(bucket_counter -1,
1472 TOTAL_SUPPLY_OFF,
1473 activity_rec.new_quantity,
1474 activity_rec.weight,
1475 activity_rec.volume);
1476 end if;
1477
1478 if activity_rec.sub_org_id <> -1 then
1479 add_sub_rows(bucket_counter -1,
1480 activity_rec.offset,
1481 activity_rec.sub_org_id,
1482 activity_rec.new_quantity,
1483 activity_rec.weight,
1484 activity_rec.volume);
1485
1486 if activity_rec.offset in (INT_TRANSIT_OFF, EXT_TRANSIT_OFF) then
1487 add_sub_rows(bucket_counter -1,
1488 INBOUND_OFF,
1489 activity_rec.sub_org_id,
1490 activity_rec.new_quantity,
1491 activity_rec.weight,
1492 activity_rec.volume);
1493 end if;
1494 end if;
1495
1496 IF activity_rec.offset2 <> 0 then
1497 WHILE activity_rec.old_date >= var_dates(old_bucket_counter) AND
1498 old_bucket_counter <= g_num_of_buckets LOOP
1499 -- ----------
1500 -- move back.
1501 -- ----------
1502 old_bucket_counter := old_bucket_counter + 1;
1503
1504 END LOOP;
1505
1506 WHILE activity_rec.old_date < var_dates(old_bucket_counter - 1) AND
1507 old_bucket_counter > 2 LOOP
1508 -- -------------
1509 -- move forward.
1510 -- -------------
1511 old_bucket_counter := old_bucket_counter - 1;
1512 END LOOP;
1513 IF activity_rec.old_date < var_dates(old_bucket_counter) THEN
1514 add_to_plan(old_bucket_counter - 1,
1515 activity_rec.offset2,
1516 activity_rec.quantity1,
1517 activity_rec.quantity2,
1518 activity_rec.quantity3);
1519 if activity_rec.offset2 in (REQUEST_SHIPMENT_OFF,
1520 EXP_DEMAND_OFF) then
1521 add_to_plan(old_bucket_counter - 1,
1522 TOTAL_UNC_DEMAND_OFF,
1523 activity_rec.quantity1,
1524 activity_rec.quantity2,
1525 activity_rec.quantity3);
1526 elsif activity_rec.offset2 in (KIT_DEMAND_OFF,OTHER_DEMAND_OFF) then
1527 add_to_plan(old_bucket_counter - 1,
1528 TOTAL_DEMAND_OFF,
1529 activity_rec.quantity1,
1530 activity_rec.quantity2,
1531 activity_rec.quantity3);
1532 end if;
1533 if activity_rec.sub_org_id <> -1 then
1534 add_sub_rows(old_bucket_counter - 1,
1535 activity_rec.offset2,
1536 activity_rec.sub_org_id,
1537 activity_rec.quantity1,
1538 activity_rec.quantity2,
1539 activity_rec.quantity3);
1540 end if;
1541 END IF;
1542 END IF;
1543
1544 last_item_id := activity_rec.item_id;
1545 last_org_id := activity_rec.org_id;
1546 last_inst_id := activity_rec.inst_id;
1547
1548 END LOOP;
1549
1550 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
1551 CLOSE drp_snapshot_activity;
1552
1553 DECLARE
1554 l_customer_prg varchar2(500);
1555 l_statement varchar2(1000);
1556
1557 BEGIN
1558 l_customer_prg := FND_PROFILE.value('MSC_HP_EXTENSION_PROGRAM');
1559 if l_customer_prg is not null then
1560 l_statement :=
1561 ' begin ' || l_customer_prg|| '(' || arg_query_id || '); end;';
1562 EXECUTE IMMEDIATE l_statement;
1563 end if;
1564 EXCEPTION WHEN others then
1565 null;
1566 END ;
1567
1568 EXCEPTION
1569
1570 WHEN OTHERS THEN
1571 -- dbms_output.put_line(g_error_stmt);
1572 raise;
1573
1574 END populate_horizontal_plan;
1575
1576 PROCEDURE dem_priority(p_plan_id number, p_org_id number,
1577 p_inst_id number, p_item_id number,
1578 p_plan_end_date date,
1579 p_query_id number,
1580 p_date_string varchar2,
1581 p_out_string OUT NOCOPY varchar2) IS
1582
1583 var_dates calendar_date;
1584 var_pri column_number;
1585 var_qty column_number;
1586 v_number_of_bkt number;
1587 v_pri number;
1588 v_qty number;
1589 p_bkt number := nvl(fnd_profile.value('MSC_DRP_DMD_PRIORITY_BK'), 3);
1590 i number := 0;
1591 v_date varchar2(20);
1592 p_delimeter varchar2(1) := '|';
1593
1594 CURSOR name_c IS
1595 select char1, char2
1596 from msc_form_query
1597 where query_id = p_query_id
1598 and number5 = p_item_id
1599 and number6 = p_org_id
1600 and number3 = p_inst_id;
1601
1602 CURSOR priority_c(p_start_date date, p_end_date date) IS
1603 select distinct md.demand_priority
1604 from msc_demands md,
1605 msc_form_query mfq_item,
1606 msc_plans mp
1607 where md.plan_id = p_plan_id
1608 and md.inventory_item_id = mfq_item.number1
1609 and md.organization_id = mfq_item.number2
1610 and md.sr_instance_id = mfq_item.number3
1611 and mfq_item.query_id = p_query_id
1612 and mfq_item.number5 = p_item_id
1613 and mfq_item.number6 = p_org_id
1614 and mfq_item.number3 = p_inst_id
1615 and mfq_item.number7 <> 6 -- NODE_GL_FORECAST_ITEM
1616 and ((p_start_date <> trunc(mp.curr_start_date) and
1617 trunc(md.using_assembly_demand_date) between p_start_date
1618 and p_end_date) or
1619 (p_start_date = trunc(mp.curr_start_date) and
1620 trunc(md.using_assembly_demand_date) <= p_end_date))
1621 and mp.plan_id = md.plan_id
1622 order by md.demand_priority ;
1623
1624 CURSOR demand_c(p_start_date date, p_end_date date) IS
1625 select md.demand_priority,
1626 sum(nvl(md.firm_quantity,md.using_requirement_quantity))
1627 from msc_demands md,
1628 msc_form_query mfq_item,
1629 msc_plans mp
1630 where md.plan_id = p_plan_id
1631 and md.inventory_item_id = mfq_item.number1
1632 and md.organization_id = mfq_item.number2
1633 and md.sr_instance_id = mfq_item.number3
1634 and mfq_item.number7 <> 6 -- NODE_GL_FORECAST_ITEM
1635 and mfq_item.query_id = p_query_id
1636 and mfq_item.number5 = p_item_id
1637 and mfq_item.number6 = p_org_id
1638 and mfq_item.number3 = p_inst_id
1639 and ((p_start_date <> trunc(mp.curr_start_date) and
1640 trunc(md.using_assembly_demand_date) between p_start_date
1641 and p_end_date) or
1642 (p_start_date = trunc(mp.curr_start_date) and
1643 trunc(md.using_assembly_demand_date) <= p_end_date))
1644 and mp.plan_id = md.plan_id
1645 group by md.demand_priority
1646 order by md.demand_priority;
1647 p_org varchar2(100);
1648 p_item varchar2(255);
1649 p_last_bkt boolean := false;
1650 BEGIN
1651
1652 OPEN name_c;
1653 FETCH name_c INTO p_org, p_item;
1654 CLOSE name_c;
1655
1656 -- init the dates cells
1657 for a in 1..(p_bkt*2+2) loop
1658 v_date := substr(p_date_string, instr(p_date_string, ':', 1, a)+1,
1659 instr(p_date_string, ':', 1, a+1) -
1660 instr(p_date_string, ':', 1, a)-1);
1661 if v_date <> '-1' then
1662 i := i +1;
1663 var_dates(i) := to_date(v_date, 'MM/DD/RRRR');
1664 else
1665 if a > p_bkt+1 and not(p_last_bkt) then -- last bucket
1666 i := i +1;
1667 var_dates(i) := p_plan_end_date;
1668 p_last_bkt := true;
1669 end if;
1670 end if;
1671 end loop;
1672
1673 OPEN priority_c(var_dates(1), var_dates(var_dates.last));
1674 FETCH priority_c BULK COLLECT INTO var_pri;
1675 CLOSE priority_c;
1676
1677 -- init all qty cells with 0
1678
1679 v_number_of_bkt := var_dates.count -1;
1680 for a in 1.. v_number_of_bkt loop
1681 for b in 1.. var_pri.count loop
1682 var_qty((a-1)*v_number_of_bkt + b) := 0;
1683 end loop;
1684 end loop;
1685 for a in 1..v_number_of_bkt loop
1686 OPEN demand_c(var_dates(a), var_dates(a+1)-1);
1687 LOOP
1688 FETCH demand_c INTO v_pri, v_qty;
1689 EXIT WHEN demand_c%NOTFOUND;
1690 for b in 1..var_pri.count loop
1691 if (v_pri is null and var_pri(b) is null ) or
1692 v_pri = var_pri(b) then
1693 var_qty((a-1)*v_number_of_bkt + b) := v_qty;
1694 end if;
1695 end loop;
1696 END LOOP;
1697 CLOSE demand_c;
1698 end loop;
1699 -- construct the list
1700
1701 if var_pri.count = 0 then
1702 p_out_string := null;
1703 return;
1704 end if;
1705
1706 -- column counts
1707 p_out_string := p_out_string ||(v_number_of_bkt+3);
1708 --
1709 -- first third column header
1710 p_out_string := p_out_string ||p_delimeter||'Org'||
1711 p_delimeter||'Item'||p_delimeter||'Priority';
1712
1713 -- column headers -- date fields
1714 for a in 1.. v_number_of_bkt loop
1715 p_out_string := p_out_string ||p_delimeter||
1716 fnd_date.date_to_displaydate(var_dates(a));
1717 end loop;
1718
1719 -- row counts
1720 p_out_string := p_out_string ||p_delimeter||var_pri.count;
1721 -- item/org/priority cells
1722 for b in 1.. var_pri.count loop
1723 if b = 1 then
1724 p_out_string := p_out_string ||p_delimeter||p_org;
1725 p_out_string := p_out_string ||p_delimeter||p_item;
1726 else
1727 p_out_string := p_out_string ||p_delimeter||' ';
1728 p_out_string := p_out_string ||p_delimeter||' ';
1729 end if;
1730 if var_pri(b) is null then
1731 p_out_string := p_out_string ||p_delimeter||' ';
1732 else
1733 p_out_string := p_out_string ||p_delimeter||var_pri(b);
1734 end if;
1735 end loop;
1736 -- qty in date cells
1737 for a in 1.. v_number_of_bkt loop
1738 for b in 1.. var_pri.count loop
1739 p_out_string := p_out_string ||p_delimeter||
1740 fnd_number.number_to_canonical(var_qty((a-1)*v_number_of_bkt + b));
1741 end loop;
1742 end loop;
1743
1744 END dem_priority;
1745
1746 END MSC_DRP_HORI_PLAN;