[Home] [Help]
PACKAGE BODY: APPS.MSC_CRP_HORIZONTAL_PLAN
Source
1 PACKAGE BODY msc_crp_horizontal_plan AS
2 /* $Header: MSCHCPLB.pls 120.22 2012/03/01 11:38:38 skathera ship $ */
3
4 SYS_YES CONSTANT INTEGER := 1;
5 SYS_NO CONSTANT INTEGER := 2;
6 SYS_TRANS CONSTANT INTEGER := 3;
7
8 SCHEDULE_FLAG_YES CONSTANT INTEGER := 1;
9
10 v_agg_flag NUMBER := 2;
11
12 NUM_OF_TYPES CONSTANT INTEGER := 42; /*Modified for bug 12683590 */
13
14 ROUTING_BASED CONSTANT INTEGER := 2;
15 RATE_BASED CONSTANT INTEGER := 3;
16
17 DELETE_WORK_DAY CONSTANT INTEGER := 1;
18 MODIFY_WORK_DAY CONSTANT INTEGER := 2;
19 ADD_WORK_DAY CONSTANT INTEGER := 3;
20
21 /* -- Commented out, keep it for refrence purpose, 11510 row types
22 AVAILABLE_HOURS CONSTANT INTEGER := 1;
23 REQUIRED_HOURS CONSTANT INTEGER := 2;
24 NET_AVAILABLE CONSTANT INTEGER := 3;
25 CUM_AVAILABLE CONSTANT INTEGER := 4;
26 UTILIZATION CONSTANT INTEGER := 5;
27 CUM_UTILIZATION CONSTANT INTEGER := 6;
28 DAILY_REQUIRED CONSTANT INTEGER := 7;
29 DAILY_AVAILABLE CONSTANT INTEGER := 8;
30 RESOURCE_COST CONSTANT INTEGER := 9;
31 CAP_CHANGES CONSTANT INTEGER := 10;
32 CUM_CAP_CHANGES CONSTANT INTEGER := 11;
33 PLANNED_ORDER CONSTANT INTEGER := 12;
34 NONSTD_JOBS CONSTANT INTEGER := 13;
35 DISCRETE_JOBS CONSTANT INTEGER := 14;
36 REPETITIVE CONSTANT INTEGER := 15;
37 WEIGHTVOL_AVA CONSTANT INTEGER := 17;
38 WEIGHTVOL_REQ CONSTANT INTEGER := 18;
39 ATP_ADJUSTMENT CONSTANT INTEGER := 19;
40 ATP_REQUIRED_HOURS CONSTANT INTEGER := 20;
41 ATP_NET CONSTANT INTEGER := 21; -- Net ATP, the last row type in Res. HP.
42 */
43
44 AVAILABLE_HOURS CONSTANT INTEGER := 1;
45 SETUP_HOURS CONSTANT INTEGER := 2;
46 SETUP_HOUR_RATIO CONSTANT INTEGER := 3;
47 RUN_HOURS CONSTANT INTEGER := 4;
48 RUN_HOUR_RATIO CONSTANT INTEGER := 5;
49 REQUIRED_HOURS CONSTANT INTEGER := 6;
50 NET_AVAILABLE CONSTANT INTEGER := 7;
51 CUM_AVAILABLE CONSTANT INTEGER := 8;
52 UTILIZATION CONSTANT INTEGER := 9;
53 CUM_UTILIZATION CONSTANT INTEGER := 10;
54 DAILY_REQUIRED CONSTANT INTEGER := 11;
55 DAILY_AVAILABLE CONSTANT INTEGER := 12;
56 RESOURCE_COST CONSTANT INTEGER := 13;
57 CAP_CHANGES CONSTANT INTEGER := 14;
58 CUM_CAP_CHANGES CONSTANT INTEGER := 15;
59 PLANNED_ORDER CONSTANT INTEGER := 16;
60 NONSTD_JOBS CONSTANT INTEGER := 17;
61 DISCRETE_JOBS CONSTANT INTEGER := 18;
62 REPETITIVE CONSTANT INTEGER := 19;
63 FLOW_SCHEDULE CONSTANT INTEGER := 20;
64 ATP_NET CONSTANT INTEGER := 21; -- Net ATP.
65
66 -- Internal Row types, never displayed
67
68 PLANNED_ORDER_SETUP CONSTANT INTEGER := 22;
69 NONSTD_JOBS_SETUP CONSTANT INTEGER := 23;
70 DISCRETE_JOBS_SETUP CONSTANT INTEGER := 24;
71 REPETITIVE_SETUP CONSTANT INTEGER := 25;
72 ATP_ADJUSTMENT_SETUP CONSTANT INTEGER := 26;
73
74
75 PLANNED_ORDER_RUN CONSTANT INTEGER := 27;
76 NONSTD_JOBS_RUN CONSTANT INTEGER := 28;
77 DISCRETE_JOBS_RUN CONSTANT INTEGER := 29;
78 REPETITIVE_RUN CONSTANT INTEGER := 30;
79 ATP_ADJUSTMENT_RUN CONSTANT INTEGER := 31;
80
81 ATP_REQUIRED_HOURS CONSTANT INTEGER := 32;
82
83 ATP_ADJUSTMENT CONSTANT INTEGER := 33;
84
85 -- End of Internal Row types
86
87 --USAF Row types
88 PLANNED_REPAIR_WO_RUN CONSTANT INTEGER := 34;
89 MAINTENANCE_WO_RUN CONSTANT INTEGER := 35;
90 PLANNED_REPAIR_WO_SETUP CONSTANT INTEGER := 36;
91 MAINTENANCE_WO_SETUP CONSTANT INTEGER := 37;
92 PLANNED_REPAIR_WO CONSTANT INTEGER := 39;
93 MAINTENANCE_WO CONSTANT INTEGER := 38; /* Corrected 38,39 assingment for bug 12593242 */
94
95 --End USAF Row types
96
97 --USAF Supplemental Row types
98 EXTERNAL_RO_RUN CONSTANT INTEGER := 40;
99 EXTERNAL_RO_SETUP CONSTANT INTEGER := 41;
100 EXTERNAL_RO CONSTANT INTEGER := 42;
101 --End USAF Supplemental Row types
102
103 WT_AVAILABLE_HOURS CONSTANT INTEGER := 1;
104 VL_AVAILABLE_HOURS CONSTANT INTEGER := 2;
105 WT_REQUIRED_HOURS CONSTANT INTEGER := 3;
106 VL_REQUIRED_HOURS CONSTANT INTEGER := 4;
107 WT_LOAD_RATIO CONSTANT INTEGER := 5;
108 VL_LOAD_RATIO CONSTANT INTEGER := 6;
109
110 M_PLANNED_ORDER CONSTANT INTEGER := 5;
111 M_NONSTD_JOBS CONSTANT INTEGER := 7;
112 M_DISCRETE_JOBS CONSTANT INTEGER := 3;
113 M_REPETITIVE CONSTANT INTEGER := 4;
114 M_FLOW_SCHEDULES CONSTANT INTEGER := 27;
115 M_ATP_ADJUSTMENT CONSTANT INTEGER := 60;
116 M_PLANNED_REPAIR_WO CONSTANT INTEGER := 79;/*Added for bug 12683590 */
117 M_MAINTENANCE_WO CONSTANT INTEGER := 70;
118 M_EXTERNAL_RO CONSTANT INTEGER := 74;
119 M_MAINTENANCE_WO_AGG CONSTANT INTEGER := 92;/*USAF Supplemental*/
120
121 WIP_DISCRETE CONSTANT INTEGER := 1; /* WIP_DISCRETE_JOB */
122 WIP_NONSTANDARD CONSTANT INTEGER := 3;
123
124
125 JOB_UNRELEASED CONSTANT INTEGER := 1;/* job status code*/
126 JOB_RELEASED CONSTANT INTEGER := 3;
127 JOB_COMPLETE CONSTANT INTEGER := 4;
128 COMPLETE_NO_CHARGES CONSTANT INTEGER := 5;
129 JOB_HOLD CONSTANT INTEGER := 6;
130 JOB_CANCELLED CONSTANT INTEGER := 7;
131
132 BASIS_PER_ITEM CONSTANT INTEGER := 1;
133 BASIS_PER_LOT CONSTANT INTEGER := 2;
134
135 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
136 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
137 TYPE column_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
138 TYPE crp_activity IS RECORD
139 (org_id NUMBER,
140 instance_id NUMBER,
141 assembly_item_id NUMBER,
142 department_id NUMBER,
143 resource_id NUMBER,
144 type NUMBER,
145 start_date DATE,
146 end_date DATE,
147 quantity NUMBER,
148 resource_instance_id NUMBER,
149 serial_number VARCHAR2(30));
150
151
152 TYPE opt_flags IS RECORD
153 (flag1 NUMBER,
154 flag2 NUMBER,
155 flag3 NUMBER,
156 flag4 NUMBER,
157 flag5 NUMBER,
158 flag6 NUMBER);
159
160 g_optimized_plan NUMBER := 2;
161 g_first_week DATE;
162 g_daily_counts NUMBER;
163
164 opt_flag_status opt_flags;
165 g_bucket_count NUMBER := 2;
166 g_num_of_buckets NUMBER;
167 g_calendar_code VARCHAR2(14);
168 g_hour_uom VARCHAR2(10);
169 g_exc_set_id NUMBER;
170 g_item_list_id NUMBER;
171 g_query_id NUMBER;
172 g_org_id NUMBER;
173 g_inst_id NUMBER;
174 g_spread_load NUMBER;
175 g_plan_start_seq NUMBER;
176 g_plan_start_date DATE;
177 g_plan_end_date DATE;
178 g_bucket_date DATE;
179 g_cutoff_date DATE;
180 g_designator NUMBER;
181 g_current_data NUMBER;
182 g_bucket_type NUMBER;
183 g_error_stmt VARCHAR2(50);
184
185 g_res_instance_case boolean := false;
186
187 g_dates calendar_date;
188 g_date_seq column_number;
189 bucket_cells column_number;
190 activity_rec crp_activity;
191
192 CURSOR c_flags (p_plan_id NUMBER) IS
193 SELECT daily_material_constraints,
194 daily_resource_constraints,
195 weekly_material_constraints,
196 weekly_resource_constraints,
197 period_material_constraints,
198 period_resource_constraints
199 FROM msc_plans
200 WHERE plan_id = p_plan_id;
201
202 CURSOR seqnum_cursor(p_date DATE) IS
203 SELECT next_seq_num
204 FROM msc_calendar_dates
205 WHERE calendar_code = g_calendar_code
206 AND exception_set_id = g_exc_set_id
207 AND sr_instance_id = g_inst_id
208 AND calendar_date = p_date;
209
210 CURSOR prior_seqnum_cursor(p_date DATE) IS
211 SELECT prior_seq_num
212 FROM msc_calendar_dates
213 WHERE calendar_code = g_calendar_code
214 AND exception_set_id = g_exc_set_id
215 AND sr_instance_id = g_inst_id
216 AND calendar_date = p_date;
217
218 CURSOR crp_snapshot_activity IS
219 -- ======================================================================
220 -- Dept/Resource requirements for batch resource, only on daily buckets.
221 -- Calculation checked at calculate_cum procedure.
222 -- ======================================================================
223 /* bug6729620, batch res will behave the same as regular res
224 SELECT
225 crp.organization_id,
226 crp.sr_instance_id,
227 to_number(0),
228 list.number2,
229 crp.resource_id,
230 decode(crp.supply_type,
231 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
232 M_REPETITIVE, REPETITIVE_RUN,
233 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
234 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
235 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
236 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
237 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
238 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
239 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
240 trunc(crp.start_date),
241 trunc(crp.end_date),
242 avg(crp.resource_hours),
243 to_number(-1),
244 to_char('-1')
245 FROM
246 msc_resource_requirements crp,
247 msc_department_resources cpr,
248 msc_resource_batches mrb,
249 msc_supplies mss,
250 msc_form_query list
251 WHERE
252 nvl(cpr.owning_department_id, cpr.department_id) = list.number5
253 AND cpr.resource_id = list.number3
254 and nvl(cpr.batchable_flag,2) =1
255 AND cpr.plan_id = g_designator
256 AND cpr.organization_id = list.number1
257 AND cpr.sr_instance_id = list.number4
258 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
259 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
260 M_ATP_ADJUSTMENT,M_PLANNED_REPAIR_WO,M_MAINTENANCE_WO,M_MAINTENANCE_WO_AGG)
261 AND crp.plan_id = cpr.plan_id
262 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
263 AND crp.resource_id = cpr.resource_id
264 AND crp.department_id = cpr.department_id
265 AND crp.organization_id = cpr.organization_id
266 AND crp.sr_instance_id = cpr.sr_instance_id
267 AND crp.schedule_flag = SCHEDULE_FLAG_YES -- to get Run Time
268 AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
269 AND list.query_id = g_item_list_id
270 AND crp.plan_id = mss.plan_id
271 AND crp.supply_id = mss.transaction_id
272 and mrb.plan_id = crp.plan_id
273 and mrb.sr_instance_id = crp.sr_instance_id
274 and mrb.organization_id= crp.organization_id
275 and mrb.department_id = crp.department_id
276 and mrb.resource_id = crp.resource_id
277 and mrb.batch_number = crp.batch_number
278 GROUP BY crp.organization_id,
279 crp.sr_instance_id,
280 list.number2,
281 crp.resource_id,
282 trunc(crp.start_date),
283 trunc(crp.end_date),
284 decode(crp.supply_type,
285 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
286 M_REPETITIVE, REPETITIVE_RUN,
287 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
288 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
289 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
290 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
291 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
292 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
293 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
294 trunc(crp.start_date),
295 trunc(crp.end_date),
296 cpr.line_flag
297 union all
298 */
299 -- =============================
300 -- Batchable Resource requirements RUN TIME /**Added for bug# 13423808 */
301 -- =============================
302 SELECT
303 crp.organization_id,
304 crp.sr_instance_id,
305 -- crp.assembly_item_id,
306 to_number(0),
307 list.number2,
308 crp.resource_id,
309 decode(crp.supply_type,
310 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
311 M_REPETITIVE, REPETITIVE_RUN,
312 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
313 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
314 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
315 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
316 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
317 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
318 M_EXTERNAL_RO,EXTERNAL_RO_RUN,
319 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
320 trunc(crp.start_date),
321 trunc(crp.end_date),
322 avg(crp.resource_hours),
323 to_number(-1),
324 to_char('-1')
325 FROM
326 msc_resource_requirements crp,
327 msc_department_resources cpr,
328 msc_resource_batches mrb,
329 msc_supplies mss,
330 msc_form_query list
331 WHERE
332 nvl(cpr.owning_department_id, cpr.department_id) = list.number5
333 AND cpr.resource_id = list.number3
334 --and nvl(cpr.batchable_flag,2) =1
335 AND crp.batch_number is not null
336 AND cpr.plan_id = g_designator
337 AND cpr.organization_id = list.number1
338 AND cpr.sr_instance_id = list.number4
339 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
340 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
341 M_ATP_ADJUSTMENT,M_PLANNED_REPAIR_WO,M_MAINTENANCE_WO,M_EXTERNAL_RO,M_MAINTENANCE_WO_AGG)
342 AND crp.plan_id = cpr.plan_id
343 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
344 AND crp.resource_id = cpr.resource_id
345 AND crp.department_id = cpr.department_id
346 AND crp.organization_id = cpr.organization_id
347 AND crp.sr_instance_id = cpr.sr_instance_id
348 AND crp.schedule_flag = SCHEDULE_FLAG_YES -- to get RUN Time
349 AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
350 AND list.query_id = g_item_list_id
351 AND crp.plan_id = mss.plan_id
352 AND crp.supply_id = mss.transaction_id
353 and mrb.plan_id = crp.plan_id
354 and mrb.sr_instance_id = crp.sr_instance_id
355 and mrb.organization_id= crp.organization_id
356 and mrb.department_id = crp.department_id
357 and mrb.resource_id = crp.resource_id
358 and mrb.batch_number = crp.batch_number
359 GROUP BY crp.organization_id,
360 crp.sr_instance_id,
361 -- crp.assembly_item_id,
362 list.number2,
363 crp.resource_id,
364 trunc(crp.start_date),
365 trunc(crp.end_date),
366 decode(crp.supply_type,
367 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
368 M_REPETITIVE, REPETITIVE_RUN,
369 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
370 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
371 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
372 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
373 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
374 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
375 M_EXTERNAL_RO,EXTERNAL_RO_RUN,
376 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
377 trunc(crp.start_date),
378 trunc(crp.end_date),
379 cpr.line_flag
380 union all /**End for bug# 13423808 */
381 -- =============================
382 -- Batchable Resource requirements SETUP TIME
383 -- =============================
384 SELECT
385 crp.organization_id,
386 crp.sr_instance_id,
387 -- crp.assembly_item_id,
388 to_number(0),
389 list.number2,
390 crp.resource_id,
391 decode(crp.supply_type,
392 M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
393 M_REPETITIVE, REPETITIVE_SETUP,
394 M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
395 M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
396 M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
397 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP,
398 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_SETUP,
399 M_MAINTENANCE_WO,MAINTENANCE_WO_SETUP,
400 M_EXTERNAL_RO,EXTERNAL_RO_SETUP,
401 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_SETUP),
402 trunc(crp.start_date),
403 trunc(crp.end_date),
404 avg(crp.resource_hours),
405 to_number(-1),
406 to_char('-1')
407 FROM
408 msc_resource_requirements crp,
409 msc_department_resources cpr,
410 msc_resource_batches mrb,
411 msc_supplies mss,
412 msc_form_query list
413 WHERE
414 nvl(cpr.owning_department_id, cpr.department_id) = list.number5
415 AND cpr.resource_id = list.number3
416 --and nvl(cpr.batchable_flag,2) =1
417 AND crp.batch_number is not null
418 AND cpr.plan_id = g_designator
419 AND cpr.organization_id = list.number1
420 AND cpr.sr_instance_id = list.number4
421 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
422 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
423 M_ATP_ADJUSTMENT,M_PLANNED_REPAIR_WO,M_MAINTENANCE_WO,M_EXTERNAL_RO,M_MAINTENANCE_WO_AGG)
424 AND crp.plan_id = cpr.plan_id
425 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
426 AND crp.resource_id = cpr.resource_id
427 AND crp.department_id = cpr.department_id
428 AND crp.organization_id = cpr.organization_id
429 AND crp.sr_instance_id = cpr.sr_instance_id
430 AND crp.schedule_flag <> SCHEDULE_FLAG_YES -- to get SETUP Time
431 AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
432 AND list.query_id = g_item_list_id
433 AND crp.plan_id = mss.plan_id
434 AND crp.supply_id = mss.transaction_id
435 and mrb.plan_id = crp.plan_id
436 and mrb.sr_instance_id = crp.sr_instance_id
437 and mrb.organization_id= crp.organization_id
438 and mrb.department_id = crp.department_id
439 and mrb.resource_id = crp.resource_id
440 and mrb.batch_number = crp.batch_number
441 GROUP BY crp.organization_id,
442 crp.sr_instance_id,
443 -- crp.assembly_item_id,
444 list.number2,
445 crp.resource_id,
446 trunc(crp.start_date),
447 trunc(crp.end_date),
448 decode(crp.supply_type,
449 M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
450 M_REPETITIVE, REPETITIVE_SETUP,
451 M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
452 M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
453 M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
454 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP,
455 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_SETUP,
456 M_MAINTENANCE_WO,MAINTENANCE_WO_SETUP,
457 M_EXTERNAL_RO,EXTERNAL_RO_SETUP,
458 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_SETUP),
459 trunc(crp.start_date),
460 trunc(crp.end_date),
461 cpr.line_flag
462 union all
463 -- =============================
464 -- Dept/Resource requirements RUN TIME
465 -- =============================
466 SELECT
467 crp.organization_id,
468 crp.sr_instance_id,
469 crp.assembly_item_id,
470 list.number2,
471 crp.resource_id,
472 decode(crp.supply_type,
473 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
474 M_REPETITIVE, REPETITIVE_RUN,
475 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
476 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
477 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
478 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
479 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
480 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
481 M_EXTERNAL_RO,EXTERNAL_RO_RUN,
482 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
483 trunc(crp.start_date),
484 trunc(crp.end_date),
485 decode (cpr.line_flag,
486 2, sum(decode(crp.end_date,
487 NULL, crp.resource_hours,
488 crp.daily_resource_hours)),
489 sum((cpr.max_rate*crp.daily_resource_hours))),
490 to_number(-1),
491 to_char('-1')
492 FROM
493 msc_resource_requirements crp,
494 msc_department_resources cpr,
495 msc_form_query list
496 WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
497 AND cpr.resource_id = list.number3
498 AND cpr.plan_id = g_designator
499 AND cpr.organization_id = list.number1
500 AND cpr.sr_instance_id = list.number4
501 -- and nvl(cpr.batchable_flag,2) =2
502 AND crp.batch_number is null
503 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
504 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
505 M_ATP_ADJUSTMENT,M_PLANNED_REPAIR_WO,M_MAINTENANCE_WO,M_EXTERNAL_RO,M_MAINTENANCE_WO_AGG)
506 AND crp.plan_id = cpr.plan_id
507 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
508 AND crp.resource_id = cpr.resource_id
509 AND crp.department_id = cpr.department_id
510 AND crp.organization_id = cpr.organization_id
511 AND crp.sr_instance_id = cpr.sr_instance_id
512 AND nvl(crp.schedule_flag,SCHEDULE_FLAG_YES) = SCHEDULE_FLAG_YES -- to get Run Time
513 AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
514 AND list.query_id = g_item_list_id
515 GROUP BY crp.organization_id,
516 crp.sr_instance_id,
517 crp.assembly_item_id,
518 list.number2,
519 crp.resource_id,
520 decode(crp.supply_type,
521 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
522 M_REPETITIVE, REPETITIVE_RUN,
523 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
524 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
525 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
526 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
527 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
528 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
529 M_EXTERNAL_RO,EXTERNAL_RO_RUN,
530 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
531 trunc(crp.start_date),
532 trunc(crp.end_date),
533 cpr.line_flag
534 UNION ALL
535 -- -----------------------------------
536 -- To get Dept / Res Setup Time
537 -- -----------------------------------
538 SELECT
539 crp.organization_id,
540 crp.sr_instance_id,
541 crp.assembly_item_id,
542 list.number2,
543 crp.resource_id,
544 decode(crp.supply_type,
545 M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
546 M_REPETITIVE, REPETITIVE_SETUP,
547 M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
548 M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
549 M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
550 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP,
551 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_SETUP,
552 M_MAINTENANCE_WO,MAINTENANCE_WO_SETUP,
553 M_EXTERNAL_RO,EXTERNAL_RO_SETUP,
554 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_SETUP),
555 trunc(crp.start_date),
556 trunc(crp.end_date),
557 decode (cpr.line_flag,
558 2, sum(decode(crp.end_date,
559 NULL, crp.resource_hours,
560 crp.daily_resource_hours)),
561 sum((cpr.max_rate*crp.daily_resource_hours))),
562 to_number(-1),
563 to_char('-1')
564 FROM
565 msc_resource_requirements crp,
566 msc_department_resources cpr,
567 msc_form_query list
568 WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
569 AND cpr.resource_id = list.number3
570 AND cpr.plan_id = g_designator
571 AND cpr.organization_id = list.number1
572 AND cpr.sr_instance_id = list.number4
573 --and nvl(cpr.batchable_flag,2) =2
574 AND crp.batch_number is null
575 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
576 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
577 M_ATP_ADJUSTMENT,M_PLANNED_REPAIR_WO,M_MAINTENANCE_WO,M_EXTERNAL_RO,M_MAINTENANCE_WO_AGG)
578 AND crp.plan_id = cpr.plan_id
579 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
580 AND crp.resource_id = cpr.resource_id
581 AND crp.department_id = cpr.department_id
582 AND crp.organization_id = cpr.organization_id
583 AND crp.sr_instance_id = cpr.sr_instance_id
584 AND crp.schedule_flag <> SCHEDULE_FLAG_YES -- to get SETUP Time
585 AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
586 AND list.query_id = g_item_list_id
587 GROUP BY crp.organization_id,
588 crp.sr_instance_id,
589 crp.assembly_item_id,
590 list.number2,
591 crp.resource_id,
592 decode(crp.supply_type,
593 M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
594 M_REPETITIVE, REPETITIVE_SETUP,
595 M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
596 M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
597 M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
598 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP,
599 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_SETUP,
600 M_MAINTENANCE_WO,MAINTENANCE_WO_SETUP,
601 M_EXTERNAL_RO,EXTERNAL_RO_SETUP,
602 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_SETUP),
603 trunc(crp.start_date),
604 trunc(crp.end_date),
605 cpr.line_flag
606
607 UNION ALL
608 -- ===================================
609 -- Repetitive schedule line requirements
610 -- ===================================
611 SELECT
612 msrs.organization_id,
613 msrs.sr_instance_id,
614 msrs.inventory_item_id,
615 msrs.line_id,
616 -1,
617 REPETITIVE,
618 trunc(msrs.first_unit_start_date),
619 trunc(least(msrs.last_unit_start_date,g_cutoff_date-1)),
620 msrs.daily_rate, -- this might not be right, used to be load_factor_rate
621 to_number(-1),
622 to_char('-1')
623 FROM msc_form_query list,
624 msc_supplies msrs
625 WHERE trunc(msrs.first_unit_start_date) <= trunc(g_cutoff_date)
626 AND msrs.plan_id = g_designator
627 AND msrs.organization_id = list.number1
628 AND msrs.sr_instance_id = list.number4
629 AND msrs.line_id = list.number2
630 AND list.number3 = -1
631 AND list.query_id = g_item_list_id
632 UNION ALL
633 -- ===================================
634 -- Availability for Dept/Res
635 -- ===================================
636 SELECT
637 avail.organization_id,
638 avail.sr_instance_id,
639 TO_NUMBER(NULL),
640 NVL(avail.department_id,-1),
641 NVL(avail.resource_id,-1),
642 AVAILABLE_HOURS,
643 trunc(avail.shift_date),
644 to_date(NULL),
645
646 -- bug 10416086
647 sum(avail.capacity_units * decode(sign(shift_date - nvl(mp.plan_start_date,shift_date-1)),
648 1,decode(from_time,NULL,1, ((DECODE(sign(avail.to_time-avail.from_time), -1,
649 avail.to_time+86400, avail.to_time) - avail.from_time)/3600)),
650 (DECODE(sign(avail.to_time-avail.from_time), -1,
651 avail.to_time+86400, avail.to_time) -
652 greatest(to_number(to_char(mp.plan_start_date,'SSSSS')),avail.from_time))/3600
653 )),
654
655
656 --sum(avail.capacity_units * decode(from_time,NULL,1,(( DECODE(sign(avail.to_time-avail.from_time), -1, avail.to_time+86400, avail.to_time) - avail.from_time)/3600))),
657 to_number(-1),
658 to_char('-1')
659 FROM msc_net_resource_avail avail,
660 msc_plans mp,
661 msc_form_query list
662 WHERE trunc(avail.shift_date) <= trunc(g_cutoff_date)
663 AND avail.shift_date >= g_dates(1)+1
664 AND NVL(avail.parent_id,0) <> -1
665 AND avail.plan_id = g_designator
666 AND avail.organization_id = list.number1
667 AND avail.sr_instance_id = list.number4
668 AND avail.department_id = list.number2
669 AND avail.resource_id = list.number3
670 AND avail.capacity_units >= 0
671 AND list.query_id = g_item_list_id
672 AND mp.plan_id=avail.plan_id
673 /*
674 AND not exists (select 'aggregate' from msc_net_resource_avail b
675 where b.shift_date = decode(v_agg_flag,1,null,avail.shift_date)
676 and b.resource_id = decode(v_agg_flag,1,null,avail.aggregate_resource_id)
677 and b.department_id = decode(v_agg_flag,1,null,avail.department_id)
678 and b.plan_id = avail.plan_id
679 and b.organization_id = decode(v_agg_flag,1,null,avail.organization_id)
680 and b.sr_instance_id = decode(v_agg_flag,1,null,avail.sr_instance_id))
681 */
682 GROUP BY avail.organization_id, avail.sr_instance_id,
683 NVL(avail.department_id,-1), NVL(avail.resource_id,-1),
684 trunc(avail.shift_date)
685 UNION ALL
686 -------------------------------------------------------------------
687 -- This is to intoduce a new row in capacity HP 'ATP'
688 -- this row will reflect the net availability from ATP perspective
689 -- the cursor will select requirement from atp perspecive and
690 -- in calculate_cum we will calculate atp net
691 -------------------------------------------------------------------
692 SELECT
693 crp.organization_id,
694 crp.sr_instance_id,
695 crp.assembly_item_id,
696 list.number2,
697 crp.resource_id,
698 ATP_REQUIRED_HOURS,
699 decode(g_optimized_plan, 1, trunc(crp.start_date),
700 nvl(trunc(crp.end_date),trunc(crp.start_date))),
701 trunc(crp.end_date),
702 sum(decode( nvl(cpr.batchable_flag,2), 1 ,
703 s.new_order_quantity * decode(cpr.uom_class_type,1,
704 i.unit_weight, i.unit_volume)
705 * nvl(decode(crp.end_date,NULL, crp.resource_hours,
706 crp.daily_resource_hours),0),
707 decode(cpr.line_flag,
708 2, decode(crp.end_date,
709 NULL, crp.resource_hours,
710 crp.daily_resource_hours),
711 cpr.max_rate*crp.daily_resource_hours))),
712 TO_NUMBER(-1),
713 to_char('-1')
714 FROM msc_resource_requirements crp,
715 msc_department_resources cpr,
716 msc_form_query list,
717 msc_supplies s,
718 msc_system_items i
719 WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
720 AND cpr.resource_id = list.number3
721 AND cpr.plan_id = g_designator
722 AND cpr.organization_id = list.number1
723 AND cpr.sr_instance_id = list.number4
724 AND crp.plan_id = cpr.plan_id
725 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
726 AND crp.resource_id = cpr.resource_id
727 AND crp.department_id = cpr.department_id
728 AND crp.organization_id = cpr.organization_id
729 AND crp.sr_instance_id = cpr.sr_instance_id
730 AND NVL(crp.parent_id, g_optimized_plan) =
731 decode(g_optimized_plan, 1,1,2,2,1)
732 AND list.query_id = g_item_list_id
733 AND crp.supply_id = s.transaction_id
734 AND crp.assembly_item_id = i.inventory_item_id
735 AND crp.sr_instance_id = i.sr_instance_id
736 AND crp.organization_id = i.organization_id
737 AND crp.plan_id = i.plan_id
738 AND i.inventory_item_id = s.inventory_item_id
739 AND i.organization_id = s.organization_id
740 AND i.sr_instance_id = s.sr_instance_id
741 AND i.plan_id = s.plan_id
742 AND ((i.bom_item_type <> 1 and i.bom_item_type <> 2) OR
743 (i.atp_flag ='Y') OR
744 (i.bom_item_type in (1, 2) AND s.record_source = 2) )
745 GROUP BY crp.organization_id,
746 crp.sr_instance_id,
747 crp.assembly_item_id,
748 list.number2,
749 crp.resource_id,
750 trunc(crp.start_date),
751 trunc(crp.end_date)
752 union all
753 --------------------------------------------------------------------
754 -- This select will ensure that all selected items get into cursor
755 -- even though they do not have any activity
756 ---------------------------------------------------------------------
757 SELECT list.number1,
758 list.number4,
759 list.number5,
760 list.number2,
761 list.number3,
762 PLANNED_ORDER,
763 to_date(1, 'J'),
764 to_date(1, 'J'),
765 0,
766 to_NUMBER(-1),
767 to_char('-1')
768 FROM msc_form_query list
769 WHERE list.query_id = g_item_list_id
770 ORDER BY 1,2,4,5,7,6;
771
772 CURSOR crp_res_inst_snapshot_activity IS
773 -- ======================================================================
774 -- Dept/Resource requirements for batch resource INSTANCES, only on daily buckets.
775 -- Calculation checked at calculate_cum procedure.
776 -- ======================================================================
777
778
779 -- =============================
780 -- Dept/Resource INSTANCE requirements RUN TIME
781 -- =============================
782 SELECT
783 crp.organization_id,
784 crp.sr_instance_id,
785 crp.assembly_item_id,
786 list.number2,
787 crp.resource_id,
788 decode(crp.supply_type,
789 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
790 M_REPETITIVE, REPETITIVE_RUN,
791 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
792 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
793 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
794 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
795 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
796 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
797 M_EXTERNAL_RO,EXTERNAL_RO_RUN,
798 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
799 trunc(mrir.start_date),
800 trunc(mrir.end_date),
801 decode (cpr.line_flag,
802 2, sum(decode(mrir.end_date,
803 NULL, mrir.resource_instance_hours,
804 mrir.daily_res_instance_hours)),
805 sum((cpr.max_rate*mrir.daily_res_instance_hours))),
806 mrir.res_instance_id,
807 mrir.serial_number
808 FROM
809 msc_resource_requirements crp,
810 msc_department_resources cpr,
811 msc_dept_res_instances mdri,
812 msc_resource_instance_reqs mrir,
813 msc_form_query list
814 WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
815 AND cpr.resource_id = list.number3
816 AND cpr.plan_id = g_designator
817 AND cpr.organization_id = list.number1
818 AND cpr.sr_instance_id = list.number4
819 AND mrir.res_instance_id = list.number8
820 AND mrir.serial_number = list.char3
821 and nvl(cpr.batchable_flag,2) =2
822 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
823 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
824 M_ATP_ADJUSTMENT,M_PLANNED_REPAIR_WO,M_MAINTENANCE_WO,
825 M_EXTERNAL_RO,M_MAINTENANCE_WO_AGG)
826 AND crp.plan_id = cpr.plan_id
827 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
828 AND crp.resource_id = cpr.resource_id
829 AND crp.department_id = cpr.department_id
830 AND crp.organization_id = cpr.organization_id
831 AND crp.sr_instance_id = cpr.sr_instance_id
832 AND crp.schedule_flag = SCHEDULE_FLAG_YES -- to get RUN Time
833 AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
834 AND list.query_id = g_item_list_id
835 and mrir.plan_id = crp.plan_id
836 and mrir.sr_instance_id = crp.sr_instance_id
837 and mrir.organization_id = crp.organization_id
838 and mrir.supply_id = crp.supply_id
839 and mrir.resource_seq_num = crp.resource_seq_num
840 and mrir.operation_seq_num = crp.operation_seq_num
841 and mdri.plan_id = mrir.plan_id
842 and mdri.sr_instance_id = mrir.sr_instance_id
843 and mdri.organization_id = mrir.organization_id
844 and mdri.department_id = mrir.department_id
845 and mdri.resource_id = mrir.resource_id
846 and mdri.res_instance_id = mrir.res_instance_id
847 and mdri.serial_number = mrir.serial_number
848 GROUP BY crp.organization_id,
849 crp.sr_instance_id,
850 crp.assembly_item_id,
851 list.number2,
852 crp.resource_id,
853 decode(crp.supply_type,
854 M_PLANNED_ORDER, PLANNED_ORDER_RUN,
855 M_REPETITIVE, REPETITIVE_RUN,
856 M_DISCRETE_JOBS, DISCRETE_JOBS_RUN,
857 M_NONSTD_JOBS, NONSTD_JOBS_RUN,
858 M_FLOW_SCHEDULES, DISCRETE_JOBS_RUN,
859 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_RUN,
860 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_RUN,
861 M_MAINTENANCE_WO,MAINTENANCE_WO_RUN,
862 M_EXTERNAL_RO,EXTERNAL_RO_RUN,
863 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_RUN),
864 trunc(mrir.start_date),
865 trunc(mrir.end_date),
866 cpr.line_flag,
867 mrir.res_instance_id,
868 mrir.serial_number
869 UNION ALL
870 -- -----------------------------------
871 -- To get Dept / Res INSTANCE Setup Time
872 -- -----------------------------------
873 SELECT
874 crp.organization_id,
875 crp.sr_instance_id,
876 crp.assembly_item_id,
877 list.number2,
878 crp.resource_id,
879 decode(crp.supply_type,
880 M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
881 M_REPETITIVE, REPETITIVE_SETUP,
882 M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
883 M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
884 M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
885 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP,
886 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_SETUP,
887 M_MAINTENANCE_WO,MAINTENANCE_WO_SETUP,
888 M_EXTERNAL_RO,EXTERNAL_RO_SETUP,
889 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_SETUP),
890 trunc(mrir.start_date),
891 trunc(mrir.end_date),
892 decode (cpr.line_flag,
893 2, sum(decode(mrir.end_date,
894 NULL, mrir.resource_instance_hours,
895 mrir.daily_res_instance_hours)),
896 sum((cpr.max_rate*mrir.daily_res_instance_hours))),
897 mrir.res_instance_id,
898 mrir.serial_number
899 FROM
900 msc_resource_requirements crp,
901 msc_department_resources cpr,
902 msc_dept_res_instances mdri,
903 msc_resource_instance_reqs mrir,
904 msc_form_query list
905 WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
906 AND cpr.resource_id = list.number3
907 AND cpr.plan_id = g_designator
908 AND cpr.organization_id = list.number1
909 AND cpr.sr_instance_id = list.number4
910 AND mrir.res_instance_id = list.number8
911 AND mrir.serial_number = list.char3
912 and nvl(cpr.batchable_flag,2) =2
913 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
914 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES,
915 M_ATP_ADJUSTMENT,M_PLANNED_REPAIR_WO,M_MAINTENANCE_WO,M_EXTERNAL_RO,M_MAINTENANCE_WO_AGG)
916 AND crp.plan_id = cpr.plan_id
917 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
918 AND crp.resource_id = cpr.resource_id
919 AND crp.department_id = cpr.department_id
920 AND crp.organization_id = cpr.organization_id
921 AND crp.sr_instance_id = cpr.sr_instance_id
922 AND crp.schedule_flag <> SCHEDULE_FLAG_YES -- to get SETUP Time
923 AND NVL(crp.parent_id, g_optimized_plan) = decode(g_optimized_plan, 1,1,2,2,1)
924 AND list.query_id = g_item_list_id
925 and mrir.plan_id = crp.plan_id
926 and mrir.sr_instance_id = crp.sr_instance_id
927 and mrir.organization_id = crp.organization_id
928 and mrir.supply_id = crp.supply_id
929 and mrir.resource_seq_num = crp.resource_seq_num
930 and mrir.operation_seq_num = crp.operation_seq_num
931 and mdri.plan_id = mrir.plan_id
932 and mdri.sr_instance_id = mrir.sr_instance_id
933 and mdri.organization_id = mrir.organization_id
934 and mdri.department_id = mrir.department_id
935 and mdri.resource_id = mrir.resource_id
936 and mdri.res_instance_id = mrir.res_instance_id
937 and mdri.serial_number = mrir.serial_number
938 GROUP BY crp.organization_id,
939 crp.sr_instance_id,
940 crp.assembly_item_id,
941 list.number2,
942 crp.resource_id,
943 decode(crp.supply_type,
944 M_PLANNED_ORDER, PLANNED_ORDER_SETUP,
945 M_REPETITIVE, REPETITIVE_SETUP,
946 M_DISCRETE_JOBS, DISCRETE_JOBS_SETUP,
947 M_NONSTD_JOBS, NONSTD_JOBS_SETUP,
948 M_FLOW_SCHEDULES, DISCRETE_JOBS_SETUP,
949 M_ATP_ADJUSTMENT, ATP_ADJUSTMENT_SETUP,
950 M_PLANNED_REPAIR_WO,PLANNED_REPAIR_WO_SETUP,
951 M_MAINTENANCE_WO,MAINTENANCE_WO_SETUP,
952 M_EXTERNAL_RO,EXTERNAL_RO_SETUP,
953 M_MAINTENANCE_WO_AGG,MAINTENANCE_WO_SETUP),
954 trunc(mrir.start_date),
955 trunc(mrir.end_date),
956 cpr.line_flag,
957 mrir.res_instance_id,
958 mrir.serial_number
959 UNION ALL
960
961 -- ===================================
962 -- Repetitive schedule line requirements
963 -- ===================================
964
965 SELECT
966 msrs.organization_id,
967 msrs.sr_instance_id,
968 msrs.inventory_item_id,
969 msrs.line_id,
970 -1,
971 REPETITIVE,
972 trunc(msrs.first_unit_start_date),
973 trunc(least(msrs.last_unit_start_date,g_cutoff_date-1)),
974 msrs.daily_rate, -- this might not be right, used to be load_factor_rate
975 to_number(null),
976 to_char(null)
977 FROM msc_form_query list,
978 msc_supplies msrs
979 WHERE trunc(msrs.first_unit_start_date) <= trunc(g_cutoff_date)
980 AND msrs.plan_id = g_designator
981 AND msrs.organization_id = list.number1
982 AND msrs.sr_instance_id = list.number4
983 AND msrs.line_id = list.number2
984 AND list.number3 = -1
985 AND list.query_id = g_item_list_id
986 UNION ALL
987
988 -- ===================================
989 -- Availability for Dept/Res INSTANCES
990 -- ===================================
991
992 SELECT
993 avail.organization_id,
994 avail.sr_instance_id,
995 TO_NUMBER(NULL),
996 NVL(avail.department_id,-1),
997 NVL(avail.resource_id,-1),
998 AVAILABLE_HOURS,
999 trunc(avail.shift_date),
1000 to_date(NULL),
1001 -- bug 10416086
1002
1003 sum(avail.capacity_units * decode(sign(shift_date - nvl(mp.plan_start_date,shift_date-1)),
1004 1,decode(from_time,NULL,1, ((DECODE(sign(avail.to_time-avail.from_time), -1,
1005 avail.to_time+86400, avail.to_time) - avail.from_time)/3600)),
1006 (DECODE(sign(avail.to_time-avail.from_time), -1,
1007 avail.to_time+86400, avail.to_time) -
1008 greatest(to_number(to_char(mp.plan_start_date,'SSSSS')),avail.from_time))/3600
1009 )),
1010
1011 --sum(nvl(avail.capacity_units,1) * decode(from_time,NULL,1,(( DECODE(sign(avail.to_time-avail.from_time), -1, avail.to_time+86400, avail.to_time) - avail.from_time)/3600))),
1012 avail.res_instance_id,
1013 avail.serial_number
1014 FROM msc_net_res_inst_avail avail, msc_plans mp,
1015 msc_form_query list
1016 WHERE trunc(avail.shift_date) <= trunc(g_cutoff_date)
1017 AND avail.shift_date >= g_dates(1)+1
1018 AND NVL(avail.parent_id,0) <> -1
1019 AND avail.plan_id = g_designator
1020 AND avail.organization_id = list.number1
1021 AND avail.sr_instance_id = list.number4
1022 AND avail.department_id = list.number2
1023 AND avail.resource_id = list.number3
1024 AND avail.res_instance_id = list.number8
1025 AND avail.serial_number = list.char3
1026 AND nvl(avail.capacity_units,1) <> 0
1027 AND list.query_id = g_item_list_id
1028 AND mp.plan_id= avail.plan_id
1029 -- AND not exists (select 'aggregate' from msc_net_resource_avail b
1030 -- where b.shift_date = decode(v_agg_flag,1,null,avail.shift_date)
1031 -- and b.resource_id = decode(v_agg_flag,1,null,avail.aggregate_resource_id)
1032 -- and b.department_id = decode(v_agg_flag,1,null,avail.department_id)
1033 -- and b.plan_id = avail.plan_id
1034 -- and b.organization_id = decode(v_agg_flag,1,null,avail.organization_id)
1035 -- and b.sr_instance_id = decode(v_agg_flag,1,null,avail.sr_instance_id))
1036 GROUP BY avail.organization_id, avail.sr_instance_id,
1037 NVL(avail.department_id,-1), NVL(avail.resource_id,-1),
1038 trunc(avail.shift_date),
1039 avail.res_instance_id,
1040 avail.serial_number
1041 UNION ALL
1042
1043 -------------------------------------------------------------------
1044 -- This is to intoduce a new row in capacity HP 'ATP'
1045 -- this row will reflect the net availability from ATP perspective
1046 -- the cursor will select requirement from atp perspecive and
1047 -- in calculate_cum we will calculate atp net
1048 -------------------------------------------------------------------
1049 /*
1050
1051 SELECT
1052 crp.organization_id,
1053 crp.sr_instance_id,
1054 crp.assembly_item_id,
1055 list.number2,
1056 crp.resource_id,
1057 ATP_REQUIRED_HOURS,
1058 decode(g_optimized_plan, 1, trunc(crp.start_date),
1059 nvl(trunc(crp.end_date),trunc(crp.start_date))),
1060 trunc(crp.end_date),
1061 sum(decode( nvl(cpr.batchable_flag,2), 1 ,
1062 s.new_order_quantity * decode(cpr.uom_class_type,1,
1063 i.unit_weight, i.unit_volume)
1064 * nvl(decode(crp.end_date,NULL, crp.resource_hours,
1065 crp.daily_resource_hours),0),
1066 decode(cpr.line_flag,
1067 2, decode(crp.end_date,
1068 NULL, crp.resource_hours,
1069 crp.daily_resource_hours),
1070 cpr.max_rate*crp.daily_resource_hours))),
1071 to_number(null),
1072 to_char(null)
1073 FROM msc_resource_requirements crp,
1074 msc_department_resources cpr,
1075 msc_form_query list,
1076 msc_supplies s,
1077 msc_system_items i
1078 WHERE nvl(cpr.owning_department_id, cpr.department_id) = list.number5
1079 AND cpr.resource_id = list.number3
1080 AND cpr.plan_id = g_designator
1081 AND cpr.organization_id = list.number1
1082 AND cpr.sr_instance_id = list.number4
1083 AND crp.plan_id = cpr.plan_id
1084 AND trunc(crp.start_date) <= trunc(g_cutoff_date)
1085 AND crp.resource_id = cpr.resource_id
1086 AND crp.department_id = cpr.department_id
1087 AND crp.organization_id = cpr.organization_id
1088 AND crp.sr_instance_id = cpr.sr_instance_id
1089 AND NVL(crp.parent_id, g_optimized_plan) =
1090 decode(g_optimized_plan, 1,1,2,2,1)
1091 AND list.query_id = g_item_list_id
1092 AND crp.supply_id = s.transaction_id
1093 AND crp.assembly_item_id = i.inventory_item_id
1094 AND crp.sr_instance_id = i.sr_instance_id
1095 AND crp.organization_id = i.organization_id
1096 AND crp.plan_id = i.plan_id
1097 AND i.inventory_item_id = s.inventory_item_id
1098 AND i.organization_id = s.organization_id
1099 AND i.sr_instance_id = s.sr_instance_id
1100 AND i.plan_id = s.plan_id
1101 AND ((i.bom_item_type <> 1 and i.bom_item_type <> 2) OR
1102 (i.atp_flag ='Y') OR
1103 (i.bom_item_type in (1, 2) AND s.record_source = 2) )
1104 GROUP BY crp.organization_id,
1105 crp.sr_instance_id,
1106 crp.assembly_item_id,
1107 list.number2,
1108 crp.resource_id,
1109 trunc(crp.start_date),
1110 trunc(crp.end_date)
1111 union all
1112 */
1113 --------------------------------------------------------------------
1114 -- This select will ensure that all selected items get into cursor
1115 -- even though they do not have any activity
1116 ---------------------------------------------------------------------
1117 SELECT list.number1,
1118 list.number4,
1119 list.number5,
1120 list.number2,
1121 list.number3,
1122 PLANNED_ORDER,
1123 to_date(1, 'J'),
1124 to_date(1, 'J'),
1125 0,
1126 to_number(null),
1127 to_char(null)
1128 FROM msc_form_query list
1129 WHERE list.query_id = g_item_list_id
1130 ORDER BY 1,2,4,5,10,11,7,6;
1131
1132
1133
1134
1135 CURSOR crp_trans_activity IS
1136 SELECT
1137 sm.to_organization_id,
1138 sm.sr_instance_id,
1139 to_number(null),
1140 sm.from_organization_id,
1141 sm.transaction_id,
1142 WT_AVAILABLE_HOURS,
1143 cal.calendar_date,
1144 to_date(NULL),
1145 NVL(sm.weight_capacity,0),
1146 to_number(null),
1147 to_char(NULL)
1148 FROM msc_interorg_ship_methods sm,
1149 msc_form_query list,
1150 msc_trading_partners tp,
1151 msc_calendar_dates cal
1152 WHERE cal.calendar_date BETWEEN g_plan_start_date+1 AND g_cutoff_date
1153 AND cal.exception_set_id = tp.calendar_exception_set_id
1154 AND cal.calendar_code = tp.calendar_code
1155 AND cal.sr_instance_id = tp.sr_instance_id
1156 AND tp.sr_instance_id = sm.sr_instance_id
1157 AND tp.sr_tp_id = sm.to_organization_id
1158 AND sm.plan_id = g_designator
1159 AND sm.to_organization_id = list.number1
1160 AND sm.sr_instance_id = list.number4
1161 AND sm.transaction_id = list.number3
1162 AND list.query_id = g_item_list_id
1163 UNION ALL
1164 SELECT
1165 sm.to_organization_id,
1166 sm.sr_instance_id,
1167 to_number(null),
1168 sm.from_organization_id,
1169 sm.transaction_id,
1170 VL_AVAILABLE_HOURS,
1171 cal.calendar_date,
1172 to_date(NULL),
1173 NVL(sm.volume_capacity,0)
1174 , to_number(null),
1175 to_char(NULL)
1176 FROM msc_interorg_ship_methods sm,
1177 msc_form_query list,
1178 msc_trading_partners tp,
1179 msc_calendar_dates cal
1180 WHERE cal.calendar_date BETWEEN g_plan_start_date+1 AND g_cutoff_date
1181 AND cal.exception_set_id = tp.calendar_exception_set_id
1182 AND cal.calendar_code = tp.calendar_code
1183 AND cal.sr_instance_id = tp.sr_instance_id
1184 AND tp.sr_tp_id = sm.to_organization_id
1185 AND tp.sr_instance_id = sm.sr_instance_id
1186 AND sm.plan_id = g_designator
1187 AND sm.to_organization_id = list.number1
1188 AND sm.sr_instance_id = list.number4
1189 AND sm.transaction_id = list.number3
1190 AND list.query_id = g_item_list_id
1191 UNION ALL
1192 SELECT
1193 sm.to_organization_id,
1194 sm.sr_instance_id,
1195 to_number(null),
1196 sm.from_organization_id,
1197 sm.transaction_id,
1198 WT_REQUIRED_HOURS,
1199 sup.new_wip_start_date,
1200 to_date(NULL),
1201 NVL(sup.weight_capacity_used,0)
1202 , to_number(null),
1203 to_char(NULL)
1204 FROM msc_interorg_ship_methods sm,
1205 msc_supplies sup,
1206 msc_form_query list
1207 WHERE sm.plan_id = g_designator
1208 AND sm.plan_id = sup.plan_id
1209 AND sm.to_organization_id = sup.organization_id
1210 AND sm.from_organization_id = sup.source_organization_id
1211 AND sm.ship_method = sup.ship_method
1212 AND sm.sr_instance_id = sup.sr_instance_id
1213 AND sm.to_organization_id = list.number1
1214 AND sm.sr_instance_id = list.number4
1215 AND sm.transaction_id = list.number3
1216 AND list.query_id = g_item_list_id
1217 UNION ALL
1218 SELECT
1219 sm.to_organization_id,
1220 sm.sr_instance_id,
1221 to_number(null),
1222 sm.from_organization_id,
1223 sm.transaction_id,
1224 VL_REQUIRED_HOURS,
1225 sup.new_wip_start_date,
1226 to_date(NULL),
1227 NVL(sup.volume_capacity_used,0)
1228 , to_number(null),
1229 to_char(NULL)
1230 FROM msc_interorg_ship_methods sm,
1231 msc_supplies sup,
1232 msc_form_query list
1233 WHERE sm.plan_id = g_designator
1234 AND sm.plan_id = sup.plan_id
1235 AND sm.to_organization_id = sup.organization_id
1236 AND sm.from_organization_id = sup.source_organization_id
1237 AND sm.ship_method = sup.ship_method
1238 AND sm.sr_instance_id = sup.sr_instance_id
1239 AND sm.to_organization_id = list.number1
1240 AND sm.sr_instance_id = list.number4
1241 AND sm.transaction_id = list.number3
1242 AND list.query_id = g_item_list_id
1243 ORDER BY 1,2,4,5,7,6;
1244
1245 CURSOR agg_resource IS
1246 SELECT nvl(mdr.aggregate_resource_flag,2)
1247 from msc_department_resources mdr,
1248 msc_form_query list
1249 where mdr.plan_id = g_designator
1250 AND mdr.sr_instance_id = list.number4
1251 AND mdr.organization_id = list.number1
1252 AND mdr.department_id = list.number2
1253 AND mdr.resource_id = list.number3
1254 AND list.query_id = g_item_list_id;
1255
1256 -- =============================================================================
1257
1258 -- =============================================================================
1259 -- Name: initialize
1260
1261 -- =============================================================================
1262
1263 -- =============================================================================
1264 -- Name: initialize
1265 -- Desc: initializes most of the global variables in the package
1266 -- g_spread_load - indicates if we want to spread capacity load or not
1267 -- g_hour_uom - stores the hour uom at this site
1268 -- g_date() - is the structure that holds the beginning of each bucket
1269 -- g_date_seq() - Holds the date seq of for each date in g_date. Note
1270 -- The date seq is for the calendar of the current org
1271 -- =============================================================================
1272 PROCEDURE initialize IS
1273 -- -----------------------------------------
1274 -- This cursor selects row type information.
1275 -- -----------------------------------------
1276 v_sid NUMBER;
1277 v_counter NUMBER;
1278
1279 CURSOR plan_buckets IS
1280 SELECT DECODE(g_designator, -1, trunc(sysdate), trunc(curr_start_date)) - 1,
1281 DECODE(g_designator, -1, trunc(sysdate+365), trunc(curr_cutoff_date))
1282 FROM msc_plans
1283 WHERE plan_id = g_designator;
1284
1285 CURSOR bucket_dates(p_start_date DATE, p_end_date DATE) IS
1286 SELECT cal.calendar_date
1287 FROM msc_trading_partners tp,
1288 msc_calendar_dates cal
1289 WHERE tp.sr_tp_id = g_org_id
1290 AND tp.sr_instance_id = g_inst_id
1291 AND tp.calendar_exception_set_id = cal.exception_set_id
1292 AND tp.partner_type = 3
1293 AND tp.calendar_code = cal.calendar_code
1294 AND tp.sr_instance_id = cal.sr_instance_id
1295 AND cal.calendar_date BETWEEN p_start_date AND p_end_date
1296 ORDER BY cal.calendar_date;
1297
1298 l_bucket_date DATE;
1299 l_bucket_number NUMBER := 0;
1300
1301 BEGIN
1302
1303 --dbms_output.put_line('in init');
1304
1305 -- --------------------------
1306 -- initialize profile value
1307 -- --------------------------
1308 g_spread_load := NVL(FND_PROFILE.VALUE('CRP_SPREAD_LOAD'), SYS_NO);
1309 g_hour_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
1310
1311 -- --------------------------
1312 -- initialize query id
1313 -- --------------------------
1314 g_error_stmt := 'Debug - initialize - 10';
1315 SELECT msc_capacity_plans_s.nextval
1316 INTO g_query_id
1317 FROM dual;
1318
1319 -- --------------------------------------------------------
1320 -- Start and End date of plan, find total number of buckets
1321 -- --------------------------------------------------------
1322 g_error_stmt := 'Debug - initialize - 20';
1323 --dbms_output.put_line('b4 planning buckets');
1324 OPEN plan_buckets;
1325 FETCH plan_buckets into g_plan_start_date, g_plan_end_date;
1326 CLOSE plan_buckets;
1327
1328 g_num_of_buckets := (g_plan_end_date + 1) - g_plan_start_date;
1329
1330 --dbms_output.put_line(' no of buckets' || g_num_of_buckets);
1331
1332 -- --------------------
1333 -- Get the bucket dates
1334 -- --------------------
1335 g_error_stmt := 'Debug - initialize - 30';
1336 OPEN bucket_dates(g_plan_start_date, g_plan_end_date+1);
1337 LOOP
1338 FETCH bucket_dates INTO l_bucket_date;
1339 EXIT WHEN BUCKET_DATES%NOTFOUND;
1340 l_bucket_number := l_bucket_number + 1;
1341 g_dates(l_bucket_number) := l_bucket_date;
1342 END LOOP;
1343 CLOSE bucket_dates;
1344
1345 --dbms_output.put_line(' bucket number' || l_bucket_number);
1346
1347 -- --------------------------
1348 -- initialize calendar code
1349 -- --------------------------
1350 SELECT calendar_code, calendar_exception_set_id
1351 INTO g_calendar_code, g_exc_set_id
1352 FROM msc_trading_partners
1353 WHERE sr_tp_id = g_org_id
1354 AND sr_instance_id = g_inst_id
1355 AND partner_type = 3;
1356
1357 -- dbms_output.put_line(' after calendar code');
1358
1359 OPEN seqnum_cursor(g_plan_start_date);
1360 FETCH seqnum_cursor INTO g_plan_start_seq;
1361 CLOSE seqnum_cursor;
1362
1363 -- dbms_output.put_line(' after seqnum code');
1364 -- ----------------------------------
1365 -- Initialize the bucket cells to 0.
1366 -- ----------------------------------
1367 g_error_stmt := 'Debug - initialize - 50';
1368
1369 FOR v_counter IN 1..(NUM_OF_TYPES * g_num_of_buckets) LOOP
1370 bucket_cells(v_counter) := 0;
1371 END LOOP;
1372
1373 --dbms_output.put_line(' after seeting to 0');
1374
1375 -- ----------------------------
1376 -- populate the g_date_seq
1377 -- memory structure.
1378 -- ----------------------------
1379 g_error_stmt := 'Debug - initialize - 70';
1380 --dbms_output.put_line(' before for loop');
1381 FOR v_counter IN 1..g_num_of_buckets LOOP
1382 -- dbms_output.put_line(' inside for loop');
1383 -- dbms_output.put_line(' date is ' || g_dates(v_counter));
1384 OPEN seqnum_cursor(g_dates(v_counter));
1385 FETCH seqnum_cursor INTO g_date_seq(v_counter);
1386 -- dbms_output.put_line(' seq is ' || g_date_seq(v_counter));
1387 CLOSE seqnum_cursor;
1388 -- if g_date_seq(v_counter) is null, raise an error
1389 END LOOP;
1390 g_error_stmt := 'Debug - initialize - 80';
1391
1392 --dbms_output.put_line(' end of init');
1393
1394 END initialize;
1395
1396 FUNCTION isWorkDay(p_org_id number, p_inst_id number,
1397 p_date Date) return boolean IS
1398 CURSOR work_c IS
1399 select mca.seq_num
1400 from msc_calendar_dates mca,
1401 msc_trading_partners mtp
1402 where mtp.sr_tp_id = p_org_id
1403 and mtp.sr_instance_id = p_inst_id
1404 and mtp.partner_type = 3
1405 and mca.calendar_code = mtp.calendar_code
1406 and mca.exception_set_id = mtp.calendar_exception_set_id
1407 and mca.calendar_date = trunc(p_date);
1408
1409 p_seq_number number;
1410 BEGIN
1411 OPEN work_c;
1412 FETCH work_c INTO p_seq_number;
1413 CLOSE work_c;
1414
1415 if p_seq_number is null then
1416 return false;
1417 else
1418 return true;
1419 end if;
1420 END isWorkDay;
1421
1422 -- =============================================================================
1423 -- Name: get_number_work_days
1424 -- Desc: returns the number of workdays between start and end date, inclusive
1425 -- =============================================================================
1426 FUNCTION get_number_work_days(
1427 start_date DATE,
1428 end_date DATE) RETURN NUMBER IS
1429 v_start_seq NUMBER;
1430 v_end_seq NUMBER;
1431 l_end_date DATE;
1432 BEGIN
1433 IF ((trunc(start_date) <> trunc(end_date) AND
1434 (trunc(end_date) - end_date) = 0)) THEN
1435 l_end_date := end_date - 1/3600;
1436 ELSE
1437 l_end_date := end_date;
1438 END IF;
1439 IF (trunc(start_date) <= trunc(l_end_date)) THEN
1440 OPEN seqnum_cursor(trunc(start_date));
1441 FETCH seqnum_cursor INTO v_start_seq;
1442 CLOSE seqnum_cursor;
1443 OPEN prior_seqnum_cursor(trunc(l_end_date));
1444 FETCH prior_seqnum_cursor INTO v_end_seq;
1445 CLOSE prior_seqnum_cursor;
1446 g_error_stmt := 'Debug - get_number_work_days - 10 - sdates ';
1447 if ( v_end_seq - v_start_seq + 1) <= 0 then return 1; end if;
1448 return (v_end_seq - v_start_seq + 1);
1449 ELSE
1450 return(0);
1451 END IF;
1452 END;
1453
1454 -- =============================================================================
1455 -- Name: compute_days_between
1456 -- Desc: returns the number of workdays between start and end date, inclusive
1457 -- =============================================================================
1458 FUNCTION compute_days_between(
1459 spread_load NUMBER,
1460 start_date DATE,
1461 end_date DATE) RETURN NUMBER IS
1462 v_days_between NUMBER;
1463 BEGIN
1464 if (spread_load = SYS_YES) then
1465 v_days_between := get_number_work_days (start_date,end_date) ;
1466 else
1467 v_days_between := 1 ;
1468 end if ;
1469 if (v_days_between = 0) then v_days_between := 1 ; end if ;
1470 return(v_days_between) ;
1471 END ;
1472
1473
1474 -- =============================================================================
1475 -- Name: add_to_plan
1476 -- Desc: adds 'quantity' to the correct type and correct bucket cell.
1477 -- If the end_date of the record is populated, then the qty is assumed
1478 -- to have daily rather than total qty. We calculate # of workdays in each
1479 -- bucket in the range of start_date-end_date, and populate each
1480 -- bucket accordingly
1481 -- =============================================================================
1482 PROCEDURE add_to_plan IS
1483 v_location NUMBER;
1484 v_bucket_start DATE;
1485 v_counter NUMBER;
1486 v_bucket_size NUMBER;
1487 v_res_cost NUMBER := 0;
1488 cursor res_cost is
1489 SELECT NVL(cst.standard_cost, 0)
1490 FROM msc_system_items cst
1491 WHERE cst.inventory_item_id = activity_rec.assembly_item_id
1492 AND cst.organization_id = activity_rec.org_id
1493 AND cst.sr_instance_id = activity_rec.instance_id
1494 AND cst.plan_id = -1;
1495 BEGIN
1496
1497 g_error_stmt := 'Debug - add_to_plan - 0';
1498 -- --------------------------------------------------
1499 -- Find cost information, note, only for lines we
1500 -- calculate the line cost in add_to_plan. For
1501 -- resources, v_res_cost is set to 0 and will
1502 -- not contribute to the resource cost. Resource
1503 -- cost is calculated in calculate_cum instead.
1504 -- dbms_output.put_line(g_error_stmt);
1505 -- --------------------------------------------------
1506 IF (activity_rec.resource_id = -1 AND
1507 activity_rec.type in (PLANNED_ORDER,NONSTD_JOBS,
1508 DISCRETE_JOBS,REPETITIVE,PLANNED_REPAIR_WO,MAINTENANCE_WO)) THEN
1509 OPEN res_cost;
1510 FETCH res_cost INTO v_res_cost;
1511 CLOSE res_cost;
1512 END IF;
1513
1514 g_error_stmt := 'Debug - add_to_plan - 10';
1515 IF (activity_rec.start_date >= g_dates(g_bucket_count)) THEN
1516 -- -------------------------------------------------------
1517 -- We got an activity which falls after the current bucket. So we
1518 -- will move the bucket counter forward until we find the
1519 -- bucket where this activity falls. Note that we should
1520 -- not advance the counter beyond g_num_of_buckets.
1521 -- --------------------------------------------------------
1522 WHILE ((activity_rec.start_date >= g_dates(g_bucket_count)) AND
1523 (g_bucket_count <= g_num_of_buckets))
1524 LOOP
1525 g_bucket_count := g_bucket_count + 1;
1526 END LOOP;
1527
1528 ---------------------------------------------------------------
1529 --- If the activity start date is outside the last bucket there
1530 --- is no need to add this activity to any bucket.
1531 ---------------------------------------------------------------
1532 if(activity_rec.start_date >= g_dates(g_bucket_count)) THEN
1533 return;
1534 end if;
1535
1536 END IF;
1537
1538 IF (activity_rec.end_date is null) THEN
1539 -- -------------------------------------------------------
1540 -- end date is null, we assume that the quantity
1541 -- stands for total quantity and we dump the total
1542 -- quantity on the first bucket
1543 -- --------------------------------------------------------
1544 g_error_stmt := 'Debug - add_to_plan - 20';
1545 v_location := ((activity_rec.type-1) * g_num_of_buckets) +
1546 g_bucket_count - 1;
1547 bucket_cells(v_location) := bucket_cells(v_location) +
1548 activity_rec.quantity;
1549 v_location := ((RESOURCE_COST-1) * g_num_of_buckets) +
1550 g_bucket_count - 1;
1551 bucket_cells(v_location) := bucket_cells(v_location) +
1552 activity_rec.quantity * v_res_cost;
1553
1554 ELSE -- IF (activity_rec.end_date is not null) THEN
1555 -- -------------------------------------------------------
1556 -- If end date is not null, we assume that the quantity
1557 -- stands for daily quantity. We multiply the daily qty
1558 -- by the # of workdays in each bucket to find the bucketed
1559 -- quantity
1560 -- --------------------------------------------------------
1561 g_error_stmt := 'Debug - add_to_plan - 30';
1562 v_counter := g_bucket_count;
1563
1564 -- --------------------------------------------------------
1565 -- We only count availability starting from the start
1566 -- date of the first bucket; however, we count pass due
1567 -- resource requirements
1568 -- --------------------------------------------------------
1569 IF (activity_rec.type in (AVAILABLE_HOURS,CAP_CHANGES)) THEN
1570 v_bucket_start := greatest(activity_rec.start_date,g_dates(1),
1571 g_plan_start_date);
1572 ELSE
1573 v_bucket_start := activity_rec.start_date;
1574 END IF;
1575
1576 -- -------------------------------------------------------
1577 -- This loop loads data from the first bucket until
1578 -- the bucket before last. Last bucket needs special logic
1579 -- --------------------------------------------------------
1580 -- Looping inside an activity_rec is not required for
1581 -- optimized/constrained plans as they generated daily req
1582 -- --------------------------------------------------------
1583 WHILE((v_counter <= g_num_of_buckets) AND
1584 (activity_rec.end_date >= g_dates(v_counter)) AND
1585 (g_optimized_plan = SYS_NO))
1586 LOOP
1587 g_error_stmt := 'Debug - add_to_plan - 40 - loop'||to_char(v_counter);
1588 /*Calculating bucket size for a special case of past due bucket bug#7195688
1589
1590 If resource start_date is 23-Dec 2008, resource end_date is 02-Jan 2009 and plan start date is 31-Dec 2008 then
1591 past due bucket in HP should be shown with (no.of work days b/w 23-Dec, 30-Dec)*daily_resource_hours.
1592 But earlier, we were adding daily_resource_hours only once for the entire period 23-Dec to 30-Dec.
1593 */
1594 if v_counter=2 then -- only while populating past due bucket.
1595 v_bucket_size := get_number_work_days(v_bucket_start,g_dates(v_counter-1)); --bug#7195688
1596 else
1597 v_bucket_size := 1;
1598 end if;
1599
1600 if isWorkDay(activity_rec.org_id, activity_rec.instance_id,
1601 v_bucket_start) then
1602 v_location := ((activity_rec.type-1) * g_num_of_buckets) +
1603 v_counter - 1;
1604 bucket_cells(v_location) := bucket_cells(v_location) +
1605 activity_rec.quantity*v_bucket_size;
1606 v_location := ((RESOURCE_COST-1) * g_num_of_buckets) +
1607 v_counter - 1;
1608 bucket_cells(v_location) := bucket_cells(v_location) +
1609 activity_rec.quantity * v_res_cost*v_bucket_size;
1610 end if;
1611 v_bucket_start := g_dates(v_counter);
1612 -- ------------------------------------------------------
1613 -- For Debuging, uncomment following
1614 --dbms_output.put_line('Ay_id'||to_char(activity_rec.assembly_item_id));
1615 --dbms_output.put_line('D_id'||to_char(activity_rec.department_id));
1616 --dbms_output.put_line('R_id'||to_char(activity_rec.resource_id));
1617 --dbms_output.put_line('Type - '||to_char(activity_rec.type));
1618 --dbms_output.put_line('Start - '||to_char(activity_rec.start_date));
1619 --dbms_output.put_line('End - '||to_char(activity_rec.end_date));
1620 --dbms_output.put_line('Qty - '||to_char(activity_rec.quantity));
1621 -- dbms_output.put_line('Buc - '||to_char(v_counter-1));
1622 -- dbms_output.put_line('Buc size - '||to_char(v_bucket_size));
1623 -- dbms_output.put_line('Buc Start - '||to_char(g_dates(v_counter-1)));
1624 -- dbms_output.put_line('=========');
1625 ------------------------------------------------------
1626 v_counter := v_counter + 1;
1627 END LOOP;
1628 -- ----------------------------------------------------
1629 -- Load the last bucket. We first find the number
1630 -- of workdays in the last time bucket.
1631 -- ----------------------------------------------------
1632 g_error_stmt := 'Debug - add_to_plan - 50';
1633 IF (activity_rec.end_date = activity_rec.start_date AND
1634 activity_rec.type in (AVAILABLE_HOURS,CAP_CHANGES)) THEN
1635 -- -----------------------------------------------
1636 -- The special case: If user has added a
1637 -- workday on a non-workday, we need to include it.
1638 -- We know a workday has been added if the
1639 -- start_date = end_date
1640 -- ------------------------------------------------
1641 v_bucket_size := 1;
1642 ELSIF g_optimized_plan = SYS_YES and
1643 activity_rec.type in (PLANNED_ORDER_RUN, NONSTD_JOBS_RUN, DISCRETE_JOBS_RUN,PLANNED_REPAIR_WO_RUN,MAINTENANCE_WO_RUN) THEN -- bug10030476, for parent_id 1, res req should have bkt size=1
1644 v_bucket_size := 1;
1645 ELSE
1646 IF (activity_rec.end_date <= g_dates(v_counter)) THEN
1647 v_bucket_size := get_number_work_days(v_bucket_start,
1648 activity_rec.end_date);
1649 ELSE
1650 v_bucket_size := get_number_work_days(v_bucket_start,
1651 g_dates(v_counter-1));
1652 END IF;
1653
1654 END IF;
1655
1656 /*
1657 if activity_rec.type in (PLANNED_ORDER_RUN, NONSTD_JOBS_RUN, DISCRETE_JOBS_RUN) THEN
1658 dbms_output.put_line('going to add to plan:'||to_char(activity_rec.type)||','
1659 ||to_char(activity_rec.start_date)||','||to_char(activity_rec.end_date)||','
1660 ||to_char(activity_rec.quantity)||','||to_char(v_counter-1)||','
1661 ||to_char(v_bucket_size)||','||to_char(v_res_cost)||','
1662 ||to_char(g_dates(v_counter-1)));
1663 end if;
1664 */
1665 v_location := ((activity_rec.type-1) * g_num_of_buckets) +
1666 v_counter - 1;
1667 bucket_cells(v_location) := bucket_cells(v_location) +
1668 v_bucket_size * activity_rec.quantity;
1669 v_location := ((RESOURCE_COST-1) * g_num_of_buckets) +
1670 v_counter - 1;
1671 bucket_cells(v_location) := bucket_cells(v_location) +
1672 v_bucket_size * activity_rec.quantity * v_res_cost;
1673 v_counter := v_counter + 1;
1674 -- ------------------------------------------------------
1675 -- For Debuging, uncomment following
1676 -- dbms_output.put_line('Ay_id'||to_char(activity_rec.assembly_item_id));
1677 -- dbms_output.put_line('D_id'||to_char(activity_rec.department_id));
1678 -- dbms_output.put_line('R_id'||to_char(activity_rec.resource_id));
1679 -- dbms_output.put_line('Type - '||to_char(activity_rec.type));
1680 -- dbms_output.put_line('Start - '||to_char(activity_rec.start_date));
1681 -- dbms_output.put_line('End - '||to_char(activity_rec.end_date));
1682 -- dbms_output.put_line('Qty - '||to_char(activity_rec.quantity));
1683 -- dbms_output.put_line('Buc - '||to_char(v_counter-1));
1684 -- dbms_output.put_line('Buc size - '||to_char(v_bucket_size));
1685 -- dbms_output.put_line('Buc Start - '||to_char(g_dates(v_counter-1)));
1686 -- dbms_output.put_line('=========');
1687 ------------------------------------------------------
1688 END IF;
1689
1690 END add_to_plan;
1691
1692
1693
1694 -- =============================================================================
1695 -- Name: calculate_cum
1696 -- Desc: Some types of data need to be calculated or cumulated across dates. This
1697 -- procedure takes care of that
1698 -- =============================================================================
1699 PROCEDURE calculate_cum(
1700 p_org_id NUMBER,
1701 p_inst_id NUMBER,
1702 p_dept_id NUMBER,
1703 p_res_id NUMBER) IS
1704
1705 v_loop BINARY_INTEGER := 1;
1706 v_cum_net_available NUMBER := 0;
1707 v_cum_available NUMBER := 0;
1708 v_cum_required NUMBER := 0;
1709 v_cum_changes NUMBER := 0;
1710 v_overhead NUMBER := 0;
1711 v_res_cost NUMBER := 0;
1712 v_num_days NUMBER := 0;
1713 v_batchable number :=0;
1714
1715 cursor isBatchAble is
1716 select nvl(Batchable_flag,2)
1717 from msc_department_resources
1718 where department_id = p_dept_id
1719 and resource_id = p_res_id
1720 and plan_id = g_designator
1721 and organization_id = p_org_id
1722 and sr_instance_id = p_inst_id;
1723
1724 BEGIN
1725
1726 g_error_stmt := 'Debug - calculate_cum - 5';
1727 OPEN isBatchAble;
1728 FETCH isBatchAble into v_batchable;
1729 CLOSE isBatchAble;
1730
1731 -- ---------------------------------
1732 -- The following will be calculated:
1733 -- REQUIRED_HOURS
1734 -- NET_AVAILABLE
1735 -- CUM_AVAILABLE
1736 -- UTILIZATION
1737 -- CUM_UTILIZATION
1738 -- DAILY_REQUIRED
1739 -- DAILY_AVAILABLE
1740 -- RESOURCE_COST
1741 -- ATP_NET
1742 -- -----------------------------
1743 IF (p_res_id <> -1) AND g_current_data <> SYS_TRANS THEN
1744
1745 Begin
1746 SELECT DISTINCT NVL(dept_overhead_cost,0)
1747 INTO v_overhead
1748 FROM msc_department_resources
1749 WHERE organization_id = p_org_id
1750 AND sr_instance_id = p_inst_id
1751 AND department_id = p_dept_id
1752 AND plan_id = -1
1753 AND line_flag <> 1;
1754 EXCEPTION when no_data_found THEN
1755 v_overhead := 0;
1756
1757 END;
1758 IF v_overhead is NULL THEN
1759 v_overhead := 0;
1760 END IF;
1761
1762 BEGIN
1763 SELECT (1 + v_overhead)*NVL(res.resource_cost,0)
1764 INTO v_res_cost
1765 FROM msc_department_resources res
1766 WHERE res.organization_id = p_org_id
1767 AND res.sr_instance_id = p_inst_id
1768 AND res.department_id = p_dept_id
1769 AND res.resource_id = p_res_id
1770 AND res.plan_id = -1;
1771 EXCEPTION when no_data_found THEN
1772 v_res_cost := 0;
1773 NULL;
1774 END;
1775 END IF;
1776
1777 g_error_stmt := 'Debug - calculate_cum - 10';
1778 FOR v_loop IN 1..g_num_of_buckets LOOP
1779 IF g_current_data = SYS_TRANS THEN
1780
1781 IF (bucket_cells((WT_AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) <= 0) THEN
1782 bucket_cells((WT_LOAD_RATIO-1)*g_num_of_buckets+v_loop) := NULL;
1783 ELSE
1784 bucket_cells((WT_LOAD_RATIO-1)*g_num_of_buckets+v_loop) := 100 *
1785 bucket_cells((WT_REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) /
1786 bucket_cells((WT_AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop);
1787 END IF;
1788 IF (bucket_cells((VL_AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) <= 0) THEN
1789 bucket_cells((VL_LOAD_RATIO-1)*g_num_of_buckets+v_loop) := NULL;
1790 ELSE
1791 bucket_cells((VL_LOAD_RATIO-1)*g_num_of_buckets+v_loop) := 100 *
1792 bucket_cells((VL_REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) /
1793 bucket_cells((VL_AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop);
1794 END IF;
1795 ELSE
1796
1797 -- -------------------
1798 -- Setup Hours
1799 -- -------------------
1800 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1801 /*
1802 if v_loop > 498 then
1803 --dbms_output.put_line(g_error_stmt);
1804 end if;
1805 */
1806 --dbms_output.put_line('batchable is ' || v_batchable);
1807 v_batchable := 0;
1808
1809 -- if v_batchable <> 1 then
1810 bucket_cells((SETUP_HOURS-1)*g_num_of_buckets+v_loop) :=
1811 bucket_cells((PLANNED_ORDER_SETUP-1)*g_num_of_buckets+v_loop) +
1812 bucket_cells((NONSTD_JOBS_SETUP-1)*g_num_of_buckets+v_loop) +
1813 bucket_cells((DISCRETE_JOBS_SETUP-1)*g_num_of_buckets+v_loop) +
1814 bucket_cells((ATP_ADJUSTMENT_SETUP-1)*g_num_of_buckets+v_loop) +
1815 bucket_cells((PLANNED_REPAIR_WO_SETUP-1)*g_num_of_buckets+v_loop) +
1816 bucket_cells((MAINTENANCE_WO_SETUP-1)*g_num_of_buckets+v_loop) +
1817 bucket_cells((EXTERNAL_RO_SETUP-1)*g_num_of_buckets+v_loop)+
1818 bucket_cells((REPETITIVE_SETUP-1)*g_num_of_buckets+v_loop);
1819 -- end if;
1820
1821
1822 -- dbms_output.put_line( ' dscrete jobs ' || bucket_cells((DISCRETE_JOBS_RUN-1)*g_num_of_buckets+v_loop));
1823 -- -------------------
1824 -- Run Hours
1825 -- -------------------
1826 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1827
1828 bucket_cells((RUN_HOURS-1)*g_num_of_buckets+v_loop) :=
1829 bucket_cells((PLANNED_ORDER_RUN-1)*g_num_of_buckets+v_loop) +
1830 bucket_cells((NONSTD_JOBS_RUN-1)*g_num_of_buckets+v_loop) +
1831 bucket_cells((DISCRETE_JOBS_RUN-1)*g_num_of_buckets+v_loop) +
1832 bucket_cells((ATP_ADJUSTMENT_RUN-1)*g_num_of_buckets+v_loop) +
1833 bucket_cells((PLANNED_REPAIR_WO_RUN-1)*g_num_of_buckets+v_loop) +
1834 bucket_cells((MAINTENANCE_WO_RUN-1)*g_num_of_buckets+v_loop) +
1835 bucket_cells((EXTERNAL_RO_RUN-1)*g_num_of_buckets+v_loop)+
1836 bucket_cells((REPETITIVE_RUN-1)*g_num_of_buckets+v_loop);
1837
1838 /*
1839 if v_loop > 498 then
1840 --dbms_output.put_line('after run hours');
1841 end if;
1842 */
1843 -- -------------------
1844 -- Planned Ordres
1845 -- -------------------
1846 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1847 if v_batchable <> 1 then
1848 bucket_cells((PLANNED_ORDER -1)*g_num_of_buckets+v_loop) :=
1849 bucket_cells((PLANNED_ORDER_RUN-1)*g_num_of_buckets+v_loop) +
1850 bucket_cells((PLANNED_ORDER_SETUP-1)*g_num_of_buckets+v_loop) ;
1851 end if;
1852
1853 -- -------------------
1854 -- Planned Repair Work Orders
1855 -- -------------------
1856 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1857 -- P_RETURN:=INSERT_PTS('RWO'||g_error_stmt);
1858 if v_batchable <> 1 then
1859 bucket_cells((PLANNED_REPAIR_WO-1)*g_num_of_buckets+v_loop) :=
1860 bucket_cells((PLANNED_REPAIR_WO_RUN-1)*g_num_of_buckets+v_loop) +
1861 bucket_cells((PLANNED_REPAIR_WO_SETUP-1)*g_num_of_buckets+v_loop) ;
1862 end if;
1863
1864 -- -------------------
1865 -- Maintenance Work Orders
1866 -- -------------------
1867 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1868 -- P_RETURN:=INSERT_PTS('MWO'||g_error_stmt);
1869 if v_batchable <> 1 then
1870 bucket_cells((MAINTENANCE_WO -1)*g_num_of_buckets+v_loop) :=
1871 bucket_cells((MAINTENANCE_WO_RUN-1)*g_num_of_buckets+v_loop) +
1872 bucket_cells((MAINTENANCE_WO_SETUP-1)*g_num_of_buckets+v_loop) ;
1873 end if;
1874
1875 -- -------------------
1876 -- External Repair Orders
1877 -- -------------------
1878 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1879 -- P_RETURN:=INSERT_PTS('ERO'||g_error_stmt);
1880 if v_batchable <> 1 then
1881 bucket_cells((EXTERNAL_RO-1)*g_num_of_buckets+v_loop) :=
1882 bucket_cells((EXTERNAL_RO_RUN-1)*g_num_of_buckets+v_loop) +
1883 bucket_cells((EXTERNAL_RO_SETUP-1)*g_num_of_buckets+v_loop) ;
1884 end if;
1885
1886 -- -------------------
1887 -- Non Std Jobs
1888 -- -------------------
1889 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1890 if v_batchable <> 1 then
1891 bucket_cells((NONSTD_JOBS-1)*g_num_of_buckets+v_loop) :=
1892 bucket_cells((NONSTD_JOBS_RUN-1)*g_num_of_buckets+v_loop) +
1893 bucket_cells((NONSTD_JOBS_SETUP-1)*g_num_of_buckets+v_loop) ;
1894 end if;
1895
1896 -- dbms_output.put_line( ' dscrete jobs 2 ----' || bucket_cells((DISCRETE_JOBS_RUN-1)*g_num_of_buckets+v_loop));
1897 -- -------------------
1898 -- Discrete Jobs
1899 -- -------------------
1900 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1901 if v_batchable <> 1 then
1902 bucket_cells((DISCRETE_JOBS-1)*g_num_of_buckets+v_loop) :=
1903 bucket_cells((DISCRETE_JOBS_RUN-1)*g_num_of_buckets+v_loop) +
1904 bucket_cells((DISCRETE_JOBS_SETUP-1)*g_num_of_buckets+v_loop) ;
1905 end if;
1906
1907
1908 -- -------------------
1909 -- Required Hours
1910 -- -------------------
1911 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1912 -- if v_batchable <> 1 then
1913 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) :=
1914 bucket_cells((SETUP_HOURS-1)*g_num_of_buckets+v_loop) +
1915 bucket_cells((RUN_HOURS-1)*g_num_of_buckets+v_loop) ;
1916 -- end if;
1917 /*
1918 if v_loop > 498 then
1919 --dbms_output.put_line(' after req hours');
1920 end if;
1921 */
1922
1923
1924 -- -------------------
1925 -- Available hours
1926 -- -------------------
1927 g_error_stmt := 'Debug - calculate_cum - 25 - loop'||to_char(v_loop);
1928
1929 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) :=
1930 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) +
1931 bucket_cells((CAP_CHANGES-1)*g_num_of_buckets+v_loop);
1932
1933
1934 -- -------------------
1935 -- Setup Hour Ratio
1936 -- -------------------
1937 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1938 IF (bucket_cells((SETUP_HOURS-1)*g_num_of_buckets+v_loop) <= 0) THEN
1939 bucket_cells((SETUP_HOUR_RATIO-1)*g_num_of_buckets+v_loop) := NULL;
1940 ELSE
1941 /*
1942 bucket_cells((SETUP_HOUR_RATIO-1)*g_num_of_buckets+v_loop) :=
1943 bucket_cells((SETUP_HOURS-1)*g_num_of_buckets+v_loop) /
1944 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) ;
1945 */
1946
1947 -- Bug #4207855
1948
1949 IF (bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) <= 0)THEN
1950 bucket_cells((SETUP_HOUR_RATIO-1)*g_num_of_buckets+v_loop) := NULL;
1951 ELSE
1952 bucket_cells((SETUP_HOUR_RATIO-1)*g_num_of_buckets+v_loop) :=
1953 bucket_cells((SETUP_HOURS-1)*g_num_of_buckets+v_loop) /
1954 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) ;
1955 END IF;
1956
1957
1958 end if;
1959
1960 -- -------------------
1961 -- RUN Hour Ratio
1962 -- -------------------
1963 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1964
1965 IF (bucket_cells((RUN_HOURS-1)*g_num_of_buckets+v_loop) <= 0) THEN
1966 bucket_cells((RUN_HOUR_RATIO-1)*g_num_of_buckets+v_loop) := NULL;
1967 ELSE
1968 /*
1969 bucket_cells((RUN_HOUR_RATIO-1)*g_num_of_buckets+v_loop) :=
1970 bucket_cells((RUN_HOURS-1)*g_num_of_buckets+v_loop) /
1971 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) ;
1972 */
1973 -- Bug #4207855
1974
1975 IF (bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) <= 0)THEN
1976 bucket_cells((RUN_HOUR_RATIO-1)*g_num_of_buckets+v_loop) := NULL;
1977 ELSE
1978 bucket_cells((RUN_HOUR_RATIO-1)*g_num_of_buckets+v_loop) :=
1979 bucket_cells((RUN_HOURS-1)*g_num_of_buckets+v_loop) /
1980 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) ;
1981 END IF;
1982
1983 end if;
1984 /*
1985 if v_loop > 498 then
1986 dbms_output.put_line(' after req hours ratio');
1987 end if;
1988 */
1989
1990 -- -------------------
1991 -- Net Available Hours
1992 -- -------------------
1993 g_error_stmt := 'Debug - calculate_cum - 30 - loop'||to_char(v_loop);
1994
1995 bucket_cells((NET_AVAILABLE-1)*g_num_of_buckets+v_loop) :=
1996 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) -
1997 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop);
1998
1999 -- ----------------------------
2000 -- Cumulatitive Available Hours
2001 -- ----------------------------
2002
2003 v_cum_net_available := v_cum_net_available +
2004 nvl(bucket_cells((NET_AVAILABLE-1)*g_num_of_buckets+v_loop),0);
2005 bucket_cells((CUM_AVAILABLE-1)*g_num_of_buckets+v_loop) := v_cum_net_available;
2006
2007 -- ----------------------------
2008 -- Capacity Utilization
2009 -- ----------------------------
2010 g_error_stmt := 'Debug - calculate_cum - 40 - loop'||to_char(v_loop);
2011
2012 IF (bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) <= 0) THEN
2013 bucket_cells((UTILIZATION-1)*g_num_of_buckets+v_loop) := NULL;
2014 ELSE
2015 bucket_cells((UTILIZATION-1)*g_num_of_buckets+v_loop) :=
2016 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) /
2017 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop);
2018 END IF;
2019
2020 -- ----------------------------
2021 -- Cum Capacity Utilization
2022 -- ----------------------------
2023 g_error_stmt := 'Debug - calculate_cum - 50 - loop'||to_char(v_loop);
2024 v_cum_required := v_cum_required +
2025 nvl(bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop),0);
2026 v_cum_available := v_cum_available +
2027 nvl(bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop),0);
2028 IF (v_cum_available <= 0) THEN
2029 bucket_cells((CUM_UTILIZATION-1)*g_num_of_buckets+v_loop) := NULL;
2030 ELSE
2031 bucket_cells((CUM_UTILIZATION-1)*g_num_of_buckets+v_loop) :=
2032 v_cum_required / v_cum_available;
2033 END IF;
2034
2035 -- ----------------------------
2036 -- Daily Required Hours and
2037 -- Daily Available Hours
2038 -- ----------------------------
2039 /*
2040 if v_loop > 498 then
2041 --dbms_output.put_line(' before daily hours');
2042 end if;
2043 */
2044 g_error_stmt := 'Debug - calculate_cum - 60 - loop'||to_char(v_loop);
2045 IF (v_loop = 1) THEN
2046 v_num_days := g_date_seq(v_loop+1) -
2047 greatest(g_date_seq(v_loop), g_plan_start_seq);
2048 ELSIF (v_loop < g_num_of_buckets) then
2049 v_num_days := g_date_seq(v_loop+1) - g_date_seq(v_loop);
2050 ELSE
2051 v_num_days := 1;
2052 END IF;
2053 if (v_num_days <> 0) then
2054 bucket_cells((DAILY_REQUIRED-1)*g_num_of_buckets+v_loop) :=
2055 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) /
2056 v_num_days;
2057 bucket_cells((DAILY_AVAILABLE-1)*g_num_of_buckets+v_loop) :=
2058 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) /
2059 v_num_days;
2060 end if;
2061 /*
2062 if v_loop > 498 then
2063 --dbms_output.put_line(' after daily hours');
2064 end if;
2065 */
2066 ---------------------------------------------
2067 --ATP_NET is calculated as
2068 --available hours - atp_required_hours
2069 ---------------------------------------------
2070 g_error_stmt := 'Debug - calculate_cum - 70 - loop'||to_char(v_loop);
2071 bucket_cells((ATP_NET-1)*g_num_of_buckets+v_loop) :=
2072 bucket_cells((AVAILABLE_HOURS-1)*g_num_of_buckets+v_loop) -
2073 bucket_cells((ATP_REQUIRED_HOURS-1)*g_num_of_buckets+v_loop);
2074 /*
2075 IF bucket_cells((ATP_REQUIRED_HOURS-1)*g_num_of_buckets+v_loop) <> 0 THEN
2076 --dbms_output.put_line('ATP_REQUIRED_HOURS '||
2077 bucket_cells((ATP_REQUIRED_HOURS-1)*g_num_of_buckets+v_loop));
2078 END IF;
2079 */
2080 -- --------------------------
2081 -- Cost for lines are already
2082 -- populated in add_plan
2083 -- --------------------------
2084 IF (p_res_id <> -1) THEN
2085 bucket_cells((RESOURCE_COST-1)*g_num_of_buckets+v_loop) := v_res_cost *
2086 bucket_cells((REQUIRED_HOURS-1)*g_num_of_buckets+v_loop);
2087 END IF;
2088
2089 END IF;
2090 END LOOP;
2091 --dbms_output.put_line(' leaving calculate cum');
2092
2093 END calculate_cum;
2094
2095
2096
2097
2098 -- =============================================================================
2099 -- Name: flush_crp_plan
2100 -- Desc: It inserts the date for 1 dept/res or line into CRP_CAPACITY_PLANS
2101 -- =============================================================================
2102 PROCEDURE flush_crp_plan(
2103 p_org_id NUMBER,
2104 p_inst_id NUMBER,
2105 p_dept_id NUMBER,
2106 p_res_id NUMBER,
2107 p_res_instance_id NUMBER,
2108 p_serial_number VARCHAR2) IS
2109 v_dept_code VARCHAR2(100) := '';
2110 v_line_code VARCHAR2(100) := '';
2111 v_dept_class_code VARCHAR2(100) := '';
2112 v_res_code VARCHAR2(100) := '';
2113 v_res_grp_name VARCHAR2(300) := '';
2114 v_resource_type_code VARCHAR2(80) := '';
2115 v_loop BINARY_INTEGER := 1;
2116 BEGIN
2117
2118 IF g_current_data <> SYS_TRANS THEN
2119 g_error_stmt := 'Debug - flush_crp_plan - 5';
2120 SELECT dept_res.department_code,
2121 dept_res.department_class,
2122 dept_res.resource_code,
2123 lkps.meaning,
2124 dept_res.resource_group_name
2125 INTO v_dept_code,
2126 v_dept_class_code,
2127 v_res_code,
2128 v_resource_type_code,
2129 v_res_grp_name
2130 FROM mfg_lookups lkps,
2131 msc_department_resources dept_res
2132 WHERE lkps.lookup_type(+) = 'BOM_RESOURCE_TYPE'
2133 AND lkps.lookup_code(+) = dept_res.resource_type
2134 AND dept_res.plan_id = -1
2135 AND dept_res.organization_id = p_org_id
2136 AND dept_res.sr_instance_id = p_inst_id
2137 AND dept_res.department_id = p_dept_id
2138 AND dept_res.resource_id = p_res_id;
2139 END IF;
2140
2141
2142 g_error_stmt := 'Debug - flush_crp_plan - 10';
2143 FOR v_loop IN 1..g_num_of_buckets LOOP
2144
2145 g_error_stmt := 'Debug - flush_crp_plan - 30 - loop'||to_char(v_loop);
2146 INSERT INTO msc_capacity_plans(
2147 query_id,
2148 last_update_date,
2149 last_updated_by,
2150 creation_date,
2151 created_by,
2152 last_update_login,
2153 organization_id,
2154 sr_instance_id,
2155 department_id,
2156 resource_id,
2157 department_name,
2158 department_class,
2159 resource_name,
2160 resource_type,
2161 resource_group_name, -- Will store the serial number
2162 quantity36, -- Will store the res_instance_id
2163 bucket_type,
2164 bucket_date,
2165 quantity1, quantity2, quantity3, quantity4,
2166 quantity5, quantity6, quantity7, quantity8,
2167 quantity9, quantity10, quantity11, quantity12,
2168 quantity13, quantity14, quantity15, quantity16,
2169 quantity17, quantity18, quantity19, quantity20,
2170 quantity21,
2171 quantity38,
2172 quantity39,
2173 quantity42)
2174 VALUES (
2175 g_query_id,
2176 SYSDATE,
2177 -1,
2178 SYSDATE,
2179 -1,
2180 -1,
2181 p_org_id,
2182 p_inst_id,
2183 DECODE(g_current_data,3,-1,p_dept_id),
2184 p_res_id,
2185 v_dept_code,
2186 v_dept_class_code,
2187 v_res_code,
2188 v_resource_type_code,
2189 p_serial_number,
2190 p_res_instance_id,
2191 g_bucket_type,
2192 g_dates(v_loop),
2193 bucket_cells(v_loop+g_num_of_buckets*0), -- available hours
2194 bucket_cells(v_loop+g_num_of_buckets*1), -- required hours
2195 bucket_cells(v_loop+g_num_of_buckets*2), -- NET_AVAILABLE
2196 bucket_cells(v_loop+g_num_of_buckets*3), -- CUM_AVAILABLE
2197 bucket_cells(v_loop+g_num_of_buckets*4), -- UTILIZATION
2198 bucket_cells(v_loop+g_num_of_buckets*5), -- CUM_UTILIZATION
2199 bucket_cells(v_loop+g_num_of_buckets*6), -- DAILY_REQUIRED
2200 bucket_cells(v_loop+g_num_of_buckets*7), -- DAILY_AVAILABLE
2201 bucket_cells(v_loop+g_num_of_buckets*8), -- RESOURCE_COST
2202 bucket_cells(v_loop+g_num_of_buckets*9), -- CAP_CHANGES
2203 bucket_cells(v_loop+g_num_of_buckets*10),
2204 bucket_cells(v_loop+g_num_of_buckets*11),
2205 bucket_cells(v_loop+g_num_of_buckets*12),
2206 bucket_cells(v_loop+g_num_of_buckets*13),
2207 bucket_cells(v_loop+g_num_of_buckets*14),
2208 bucket_cells(v_loop+g_num_of_buckets*15),
2209 bucket_cells(v_loop+g_num_of_buckets*16),
2210 bucket_cells(v_loop+g_num_of_buckets*17),
2211 bucket_cells(v_loop+g_num_of_buckets*18),
2212 bucket_cells(v_loop+g_num_of_buckets*19),
2213 bucket_cells(v_loop+g_num_of_buckets*20), -- atp net
2214 bucket_cells(v_loop+g_num_of_buckets*37),
2215 bucket_cells(v_loop+g_num_of_buckets*38),
2216 bucket_cells(v_loop+g_num_of_buckets*41)
2217 );
2218 END LOOP;
2219 END flush_crp_plan;
2220
2221
2222 -- =============================================================================
2223 -- Name: re_initialize
2224 -- Desc: This is called everytime we work on a new dept/resource
2225 -- Initializes cost information as well as calendar code
2226 -- =============================================================================
2227 PROCEDURE re_initialize IS
2228 v_overhead NUMBER;
2229 BEGIN
2230
2231 g_bucket_count := 2;
2232
2233 g_error_stmt := 'Debug - re_initialize - 10';
2234 -- ----------------------------------
2235 -- Initialize the bucket cells to 0.
2236 -- dbms_output.put_line(g_error_stmt);
2237 -- ----------------------------------
2238 FOR v_cnt IN 1..NUM_OF_TYPES*g_num_of_buckets LOOP
2239 bucket_cells(v_cnt) := 0;
2240 END LOOP;
2241
2242 g_error_stmt := 'Debug - re_initialize - 20';
2243 -- ----------------------------
2244 -- Find the exception_set_id
2245 -- and calendar_code for this
2246 -- organization
2247 -- dbms_output.put_line(g_error_stmt);
2248 -- ----------------------------
2249 SELECT calendar_code, calendar_exception_set_id
2250 INTO g_calendar_code, g_exc_set_id
2251 FROM msc_trading_partners
2252 WHERE sr_tp_id = activity_rec.org_id
2253 AND sr_instance_id = activity_rec.instance_id
2254 AND partner_type = 3;
2255
2256 END re_initialize;
2257
2258
2259 -- =============================================================================
2260 -- Name:populate_horizontal_plan
2261 -- This is the main procedure. It retrieves data from database and calls
2262 -- private procedures to summarize them into user defined buckets.
2263 -- The argument p_current_data tells us whether to use current data
2264 -- or snapshoted data for bucketing.
2265 -- The p_bucket_type tells us what kind of buckets to use for summarization.
2266 -- p_bucket_type 1 - ??daily buckets??
2267 -- 2 - ??weekly buckets??
2268 -- 3 - ??periodic buckets??
2269 -- p_current_data 1 - Use current data
2270 -- 2 - Use snapshot data
2271 --
2272 -- =============================================================================
2273 FUNCTION populate_horizontal_plan(
2274 p_batchable IN NUMBER, -- can be removed, no use now
2275 p_item_list_id IN NUMBER,
2276 p_org_id IN NUMBER,
2277 p_inst_id IN NUMBER,
2278 p_plan_id IN NUMBER,
2279 p_bucket_type IN NUMBER,
2280 p_cutoff_date IN DATE,
2281 p_current_data IN NUMBER DEFAULT 2) RETURN NUMBER IS
2282
2283 v_no_rows BOOLEAN;
2284 v_last_dept_id NUMBER := -2;
2285 v_last_org_id NUMBER := -2;
2286 v_last_inst_id NUMBER := -2;
2287 v_last_res_id NUMBER := -2;
2288 v_last_res_instance_id NUMBER := -2;
2289 v_last_serial_number VARCHAR2(30) := null;
2290 v_cnt NUMBER;
2291 l_plan_type NUMBER := 1;
2292
2293 BEGIN
2294
2295 -- ----------------------------
2296 -- Initialize Global variables
2297 -- ----------------------------
2298 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
2299 g_item_list_id := p_item_list_id;
2300 g_org_id := p_org_id;
2301 g_inst_id := p_inst_id;
2302 g_designator :=p_plan_id;
2303 g_bucket_type := p_bucket_type;
2304 g_cutoff_date := p_cutoff_date;
2305 g_current_data := p_current_data;
2306
2307 --dbms_output.put_line(' in populate');
2308
2309 OPEN agg_resource;
2310 FETCH agg_resource INTO v_agg_flag;
2311 CLOSE agg_resource;
2312
2313 initialize;
2314 --dbms_output.put_line(g_error_stmt);
2315
2316 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
2317 -- dbms_output.put_line(g_error_stmt);
2318
2319 IF g_current_data = SYS_YES THEN
2320 null;
2321 ELSIF g_current_data = SYS_TRANS THEN
2322 OPEN crp_trans_activity;
2323 ELSE
2324 OPEN c_flags(p_plan_id);
2325 FETCH c_flags INTO opt_flag_status;
2326 CLOSE c_flags;
2327
2328 select count(*) into g_daily_counts
2329 from msc_plan_buckets
2330 where PLAN_ID = g_designator
2331 and SR_INSTANCE_ID = g_inst_id
2332 and ORGANIZATION_ID = g_org_id
2333 and BUCKET_TYPE = 1;
2334
2335 IF ( (opt_flag_status.flag1 = SYS_YES ) OR
2336 (opt_flag_status.flag2 = SYS_YES ) OR
2337 (opt_flag_status.flag3 = SYS_YES ) OR
2338 (opt_flag_status.flag4 = SYS_YES ) OR
2339 (opt_flag_status.flag5 = SYS_YES ) OR
2340 (opt_flag_status.flag6 = SYS_YES ) ) THEN
2341
2342 g_optimized_plan := SYS_YES;
2343 ELSE
2344 g_optimized_plan := SYS_NO;
2345 END IF;
2346
2347 select plan_type into l_plan_type
2348 from msc_plans
2349 where plan_id = p_plan_id;
2350 if ( l_plan_type = 4 ) then -- sro plan
2351 g_optimized_plan := SYS_NO;
2352 end if;
2353
2354 IF g_res_instance_case = TRUE THEN
2355 OPEN crp_res_inst_snapshot_activity;
2356 ELSE
2357 OPEN crp_snapshot_activity;
2358 END IF;
2359
2360 END IF;
2361
2362
2363 -- ----------------------------
2364 -- Fetch rows from cursor
2365 -- and process them one by one
2366 -- ----------------------------
2367 LOOP
2368 v_no_rows := FALSE;
2369 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
2370 -- dbms_output.put_line(g_error_stmt);
2371
2372 IF g_current_data = SYS_YES THEN
2373 null;
2374 ELSIF g_current_data = SYS_TRANS THEN
2375 FETCH crp_trans_activity INTO activity_rec;
2376 IF (crp_trans_activity%NOTFOUND) THEN
2377 v_no_rows := TRUE;
2378 END IF;
2379
2380 ELSIF g_res_instance_case = TRUE THEN
2381 -- dbms_output.put_line(' INSTANCE CASE, going to open cursor ');
2382 FETCH crp_res_inst_snapshot_activity INTO activity_rec;
2383 IF (crp_res_inst_snapshot_activity%NOTFOUND) THEN
2384 v_no_rows := TRUE;
2385 --dbms_output.put_line('IN pls nothing is found in the cursor');
2386 END IF;
2387 ELSE
2388 FETCH crp_snapshot_activity INTO activity_rec;
2389 IF (crp_snapshot_activity%NOTFOUND) THEN
2390 v_no_rows := TRUE;
2391 --dbms_output.put_line('IN pls nothing is found in the cursor');
2392 END IF;
2393
2394
2395 END IF;
2396
2397 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
2398 -- dbms_output.put_line(g_error_stmt);
2399
2400 IF ((v_no_rows OR
2401 v_last_org_id <> activity_rec.org_id OR
2402 v_last_inst_id <> activity_rec.instance_id OR
2403 v_last_dept_id <> activity_rec.department_id OR
2404 v_last_res_instance_id <> activity_rec.resource_instance_id OR
2405 v_last_serial_number <> activity_rec.serial_number OR
2406 v_last_res_id <> activity_rec.resource_id) AND
2407 v_last_dept_id <> -2) THEN
2408 -- ==================================================
2409 -- snapshoting for the last dept/res has finished
2410 -- We therefore calculate cumulative information,
2411 -- flush the previous set of data and then
2412 -- re-initialized for the current dept/res
2413 -- ==================================================
2414 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
2415 --dbms_output.put_line('just b4 calling calculate_cum ' || g_num_of_buckets);
2416 calculate_cum(v_last_org_id,v_last_inst_id,v_last_dept_id,v_last_res_id);
2417
2418 g_error_stmt := 'Debug - populate_horizontal_plan - 60';
2419 --dbms_output.put_line(g_error_stmt);
2420 flush_crp_plan(v_last_org_id,v_last_inst_id,v_last_dept_id,v_last_res_id,
2421 v_last_res_instance_id, v_last_serial_number);
2422
2423 g_error_stmt := 'Debug - populate_horizontal_plan - 70';
2424 --dbms_output.put_line(g_error_stmt);
2425 re_initialize;
2426 END IF;
2427
2428 EXIT WHEN v_no_rows;
2429
2430 g_error_stmt := 'Debug - populate_horizontal_plan - 85';
2431 -- ---------------------------------------------------------
2432 -- Add the retrieved activity to the plan
2433 -- dbms_output.put_line(g_error_stmt);
2434 -- ---------------------------------------------------------
2435 add_to_plan;
2436
2437 v_last_org_id := activity_rec.org_id;
2438 v_last_inst_id := activity_rec.instance_id;
2439 v_last_res_id := activity_rec.resource_id;
2440 v_last_res_instance_id := activity_rec.resource_instance_id;
2441 v_last_dept_id := activity_rec.department_id;
2442 v_last_serial_number := activity_rec.serial_number;
2443 END LOOP;
2444
2445 g_error_stmt := 'Debug - populate_horizontal_plan - 90';
2446 -- dbms_output.put_line(g_error_stmt);
2447 IF g_current_data = SYS_YES THEN
2448 null;
2449 ELSIF g_current_data = SYS_TRANS THEN
2450 CLOSE crp_trans_activity;
2451 ELSIF g_res_instance_case = TRUE THEN
2452 CLOSE crp_res_inst_snapshot_activity;
2453 ELSE
2454 CLOSE crp_snapshot_activity;
2455 END IF;
2456
2457 return g_query_id;
2458
2459 EXCEPTION WHEN others THEN
2460 -- dbms_output.put_line(g_error_stmt);
2461 IF (seqnum_cursor%ISOPEN) THEN
2462 close seqnum_cursor;
2463 END IF;
2464 IF (crp_trans_activity%ISOPEN) THEN
2465 close crp_trans_activity;
2466 END IF;
2467 IF (crp_snapshot_activity%ISOPEN) THEN
2468 close crp_snapshot_activity;
2469 END IF;
2470 IF (crp_res_inst_snapshot_activity%ISOPEN) THEN
2471 close crp_res_inst_snapshot_activity;
2472 END IF;
2473 raise;
2474 END populate_horizontal_plan;
2475
2476 PROCEDURE query_list(p_query_id IN NUMBER,
2477 p_plan_id IN NUMBER,
2478 p_org_list IN VARCHAR2,
2479 p_dept_list IN VARCHAR2,
2480 p_res_list IN VARCHAR2,
2481 p_data IN NUMBER,
2482 p_inst_list IN VARCHAR2 DEFAULT NULL,
2483 p_serial_num_list IN VARCHAR2 DEFAULT NULL) IS
2484
2485 sql_stmt VARCHAR2(4000);
2486 NODE_REGULAR_ITEM CONSTANT NUMBER :=0;
2487 NODE_ITEM_SUPPLIER CONSTANT NUMBER := 1;
2488 NODE_DEPT_RES CONSTANT NUMBER := 2;
2489 NODE_LINE CONSTANT NUMBER := 3;
2490 NODE_TRANS_RES CONSTANT NUMBER := 4;
2491 NODE_PF_ITEM CONSTANT NUMBER := 5;
2492 NODE_GL_FORECAST_ITEM CONSTANT NUMBER := 6;
2493 NODE_RES_INSTANCE CONSTANT NUMBER := 7;
2494
2495 BEGIN
2496
2497 --dbms_output.put_line(' in query list ');
2498 --dbms_output.put_line(' value of p_inst_list is ' || p_inst_list);
2499 --dbms_output.put_line(' value of sreial number is ' || p_serial_num_list);
2500
2501 sql_stmt := 'INSERT INTO msc_form_query ( '||
2502 'query_id, '||
2503 'last_update_date, '||
2504 'last_updated_by, '||
2505 'creation_date, '||
2506 'created_by, '||
2507 'last_update_login, '||
2508 'number1, '|| -- org id
2509 'number2, '|| -- dept id
2510 'number3, '|| -- res id
2511 'number4, '|| -- sr_instance
2512 'number5, '|| -- owning dept id
2513 'number7, '|| -- node_type
2514 'number8, '|| -- RES INSTANCE ID
2515 'char1, '||
2516 'char2, '||
2517 'char3) '|| -- SERIAL NUMBER
2518 ' SELECT DISTINCT '|| p_query_id || ', '||
2519 'sysdate, '||
2520 '1, '||
2521 'sysdate, '||
2522 '1, '||
2523 '1, ';
2524
2525 IF p_data = 3 THEN
2526
2527 g_res_instance_case := false;
2528 sql_stmt := sql_stmt ||
2529 'to_organization_id, '||
2530 '-1, '||
2531 'transaction_id, '||
2532 'sr_instance_id, '||
2533 'from_organization_id, '||
2534 NODE_TRANS_RES ||
2535 ',-1 ' ||
2536 ',msc_get_name.org_code(to_organization_id,sr_instance_id), '||
2537 'ship_method, '||
2538 ''' ''' || -- insert an empty space, otherwise will error out in java
2539 ' FROM msc_interorg_ship_methods '||
2540 'WHERE transaction_id in ('||p_res_list||') ' ||
2541 'AND (sr_instance_id,to_organization_id) in ('||p_org_list||') ';
2542
2543 ELSE
2544 if (p_inst_list = 'RES') then -- Resource Case
2545
2546 g_res_instance_case := false;
2547 --dbms_output.put_line(' in RESOURCE CASE ');
2548 sql_stmt := sql_stmt ||
2549 'organization_id, '||
2550 'department_id, '||
2551 'resource_id, '||
2552 'sr_instance_id, '||
2553 'nvl(owning_department_id, department_id), '||
2554 'decode(resource_id,-1,'||NODE_LINE||','||NODE_DEPT_RES||'),'||
2555 '-1, ' ||
2556 'decode(resource_id,-1, msc_get_name.org_code(organization_id,sr_instance_id), department_code), '||
2557 'decode(resource_id,-1,department_code, resource_code), '||
2558 '-1 ' ||
2559 'FROM msc_department_resources '||
2560 'WHERE department_id in ('||p_dept_list||') ' ||
2561 'AND resource_id in ('||p_res_list||') ' ||
2562 'AND (sr_instance_id,organization_id) in ('||p_org_list||') ' ||
2563 'AND plan_id = '||p_plan_id;
2564 else
2565
2566 --dbms_output.put_line(' in INSTANCE CASE ');
2567 g_res_instance_case := true;
2568
2569 sql_stmt := sql_stmt ||
2570 'organization_id, '||
2571 'department_id, '||
2572 'resource_id, '||
2573 'sr_instance_id, '||
2574 'department_id, '||
2575 'decode(resource_id,-1,'||NODE_LINE||','||NODE_RES_INSTANCE||'),'||
2576 'res_instance_id, ' ||
2577 '-1, '||
2578 'serial_number, '||
2579 'serial_number '||
2580 'FROM msc_dept_res_instances '||
2581 'WHERE department_id in ('||p_dept_list||') ' ||
2582 'AND resource_id in ('||p_res_list||') ' ||
2583 'AND res_instance_id in ('||p_inst_list||') ' ||
2584 -- 'AND serial_number in ('''||p_serial_num_list||''' ) ' ||
2585 'AND serial_number in (' || p_serial_num_list|| ' ) ' ||
2586 'AND (sr_instance_id,organization_id) in ('||p_org_list||') ' ||
2587 'AND plan_id = '||p_plan_id;
2588
2589 end if;
2590
2591 END IF;
2592 /*
2593 dbms_output.put_line(' before query list ');
2594 dbms_output.put_line(substr(sql_stmt,1,200));
2595 dbms_output.put_line(substr(sql_stmt,201,200));
2596 dbms_output.put_line(substr(sql_stmt,401,200));
2597 */
2598
2599 EXECUTE IMMEDIATE sql_stmt;
2600
2601
2602 END query_list;
2603
2604 END msc_crp_horizontal_plan;