[Home] [Help]
PACKAGE BODY: APPS.MRP_CRP_HORIZONTAL_PLAN
Source
1 PACKAGE BODY mrp_crp_horizontal_plan AS
2 /* $Header: MRPHCPLB.pls 120.3 2006/09/21 11:55:50 davashia noship $ */
3
4 SYS_YES CONSTANT INTEGER := 1;
5 SYS_NO CONSTANT INTEGER := 2;
6
7 NUM_OF_COLUMNS CONSTANT INTEGER := 36;
8 NUM_OF_TYPES CONSTANT INTEGER := 16;
9
10 ROUTING_BASED CONSTANT INTEGER := 2;
11 RATE_BASED CONSTANT INTEGER := 3;
12
13 DELETE_WORK_DAY CONSTANT INTEGER := 1;
14 MODIFY_WORK_DAY CONSTANT INTEGER := 2;
15 ADD_WORK_DAY CONSTANT INTEGER := 3;
16
17 AVAILABLE_HOURS CONSTANT INTEGER := 1;
18 REQUIRED_HOURS CONSTANT INTEGER := 2;
19 NET_AVAILABLE CONSTANT INTEGER := 3;
20 CUM_AVAILABLE CONSTANT INTEGER := 4;
21 UTILIZATION CONSTANT INTEGER := 5;
22 CUM_UTILIZATION CONSTANT INTEGER := 6;
23 DAILY_REQUIRED CONSTANT INTEGER := 7;
24 DAILY_AVAILABLE CONSTANT INTEGER := 8;
25 RESOURCE_COST CONSTANT INTEGER := 9;
26 CAP_CHANGES CONSTANT INTEGER := 10;
27 CUM_CAP_CHANGES CONSTANT INTEGER := 11;
28 PLANNED_ORDER CONSTANT INTEGER := 12;
29 NONSTD_JOBS CONSTANT INTEGER := 13;
30 DISCRETE_JOBS CONSTANT INTEGER := 14;
31 REPETITIVE CONSTANT INTEGER := 15;
32
33 M_PLANNED_ORDER CONSTANT INTEGER := 5;
34 M_NONSTD_JOBS CONSTANT INTEGER := 7;
35 M_DISCRETE_JOBS CONSTANT INTEGER := 3;
36 M_REPETITIVE CONSTANT INTEGER := 4;
37 M_FLOW_SCHEDULES CONSTANT INTEGER := 27;
38
39 WIP_DISCRETE CONSTANT INTEGER := 1; /* WIP_DISCRETE_JOB */
40 WIP_NONSTANDARD CONSTANT INTEGER := 3;
41
42
43 JOB_UNRELEASED CONSTANT INTEGER := 1;/* job status code*/
44 JOB_RELEASED CONSTANT INTEGER := 3;
45 JOB_COMPLETE CONSTANT INTEGER := 4;
46 COMPLETE_NO_CHARGES CONSTANT INTEGER := 5;
47 JOB_HOLD CONSTANT INTEGER := 6;
48 JOB_CANCELLED CONSTANT INTEGER := 7;
49
50 PEND_BILL CONSTANT INTEGER := 8;
51 FAIL_BILL CONSTANT INTEGER := 9;
52 PEND_ROUT CONSTANT INTEGER := 10;
53 FAIL_ROUT CONSTANT INTEGER := 11;
54 CLSD_NO_CHARGES CONSTANT INTEGER := 12;
55
56 BASIS_PER_ITEM CONSTANT INTEGER := 1;
57 BASIS_PER_LOT CONSTANT INTEGER := 2;
58 /** Bug 1965639 : The following are introduced to evaluate
59 department overheads depending on the OVERHEAD BASIS */
60 BASIS_RESOURCE_UNITS CONSTANT INTEGER := 3;
61 BASIS_RESOURCE_VALUE CONSTANT INTEGER := 4;
62
63 CST_FROZEN CONSTANT INTEGER := 1;
64
65 BOM_EVENTS CONSTANT INTEGER := 1;
66 BOM_PROCESSES CONSTANT INTEGER := 2;
67 BOM_LINE_OPS CONSTANT INTEGER := 3;
68
69 TYPE calendar_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
70 TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
71 TYPE column_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
72 TYPE crp_activity IS RECORD
73 (org_id NUMBER,
74 assembly_item_id NUMBER,
75 department_id NUMBER,
76 resource_id NUMBER,
77 line_id NUMBER,
78 type NUMBER,
79 start_date DATE,
80 end_date DATE,
81 quantity NUMBER);
82
83 g_bucket_count NUMBER := 2;
84 g_calendar_code VARCHAR2(10);
85 g_hour_uom VARCHAR2(10);
86 g_exc_set_id NUMBER;
87 g_item_list_id NUMBER;
88 g_query_id NUMBER;
89 g_org_id NUMBER;
90 g_planned_org NUMBER;
91 g_spread_load NUMBER;
92 g_plan_start_seq NUMBER;
93 g_plan_start_date DATE;
94 g_cutoff_date DATE;
95 g_designator VARCHAR2(10);
96 g_current_data NUMBER;
97 g_bucket_type NUMBER;
98 g_error_stmt VARCHAR2(50);
99
100 g_dates calendar_date;
101 g_date_seq column_number;
102 bucket_cells column_number;
103 activity_rec crp_activity;
104
105 CURSOR seqnum_cursor(p_date DATE) IS
106 SELECT next_seq_num
107 FROM bom_calendar_dates
108 WHERE calendar_code = g_calendar_code
109 AND exception_set_id = g_exc_set_id
110 AND calendar_date = p_date;
111
112 CURSOR prior_seqnum_cursor(p_date DATE) IS
113 SELECT prior_seq_num
114 FROM bom_calendar_dates
115 WHERE calendar_code = g_calendar_code
116 AND exception_set_id = g_exc_set_id
117 AND calendar_date = p_date;
118
119 CURSOR crp_current_activity IS
120 -- ===================================
121 -- Planned Order Dept/Res Requirement - tested
122 -- ??Do not know how to spread load??
123 -- ===================================
124 SELECT
125 recom.organization_id,
126 recom.inventory_item_id,
127 list.number2,
128 routing.resource_id,
129 -1,
130 PLANNED_ORDER,
131 TRUNC(res_start.calendar_date),
132 to_date(NULL),
133 DECODE(routing.basis, BASIS_PER_ITEM,
134 GREATEST(routing.runtime_quantity* (nvl(recom.new_order_quantity,
135 recom.firm_quantity)-
136 NVL(recom.implemented_quantity,0) -
137 NVL(recom.quantity_in_process,0)),0),
138 DECODE(SIGN(nvl(recom.new_order_quantity, recom.firm_quantity) -
139 NVL(recom.implemented_quantity,0) -
140 NVL(recom.quantity_in_process,0)),
141 1, routing.runtime_quantity, 0))
142 FROM bom_calendar_dates res_start,
143 bom_calendar_dates order_date,
144 mrp_recommendations recom,
145 mrp_system_items items,
146 mrp_planned_resource_reqs routing,
147 mtl_parameters org,
148 mrp_form_query list
149 WHERE res_start.exception_set_id = org.calendar_exception_set_id
150 AND res_start.calendar_code = org.calendar_code
151 AND res_start.seq_num = order_date.prior_seq_num -
152 ceil((1 - NVL(routing.resource_offset_percent,0)) *
153 (NVL(items.fixed_lead_time,0) + NVL(items.variable_lead_time,0) *
154 (recom.new_order_quantity - NVL(recom.implemented_quantity,0) +
155 NVL(recom.quantity_in_process,0))))
156 AND TRUNC(res_start.calendar_date) < g_cutoff_date
157 AND order_date.exception_set_id = org.calendar_exception_set_id
158 AND order_date.calendar_code = org.calendar_code
159 AND order_date.calendar_date = recom.new_schedule_date
160 AND recom.order_type = M_PLANNED_ORDER
161 AND recom.compile_designator = items.compile_designator
162 AND recom.organization_id = items.organization_id
163 AND recom.inventory_item_id = items.inventory_item_id
164 AND items.organization_id = routing.organization_id
165 AND items.compile_designator = routing.compile_designator
166 AND items.inventory_item_id = routing.using_assembly_item_id
167 AND routing.alternate_routing_designator is NULL
168 AND routing.compile_designator = g_designator
169 AND routing.organization_id = org.organization_id
170 AND routing.department_id in (select department_id
171 from crp_planned_resources cpr
172 where cpr.compile_designator = g_designator
173 and cpr.organization_id = list.number1
174 and cpr.owning_department_id = list.number2)
175 AND routing.resource_id = list.number3
176 AND org.organization_id = list.number1
177 AND list.number3 is not null
178 AND list.query_id = g_item_list_id
179 UNION ALL
180 -- ==================================
181 -- Discrete Job Dept/Res Requirement - tested
182 -- ==================================
183 SELECT
184 res.organization_id,
185 jobs.primary_item_id,
186 list.number2,
187 res.resource_id,
188 -1,
189 DECODE(jobs.job_type,
190 WIP_NONSTANDARD, NONSTD_JOBS, DISCRETE_JOBS),
191 NVL(res.start_date, jobs.scheduled_start_date),
192 DECODE(g_spread_load,
193 SYS_YES, res.completion_date, to_date(NULL)),
194 DECODE(res.basis_type, BASIS_PER_ITEM,
195 NVL((res.usage_rate_or_amount*(op.scheduled_quantity -
196 NVL(op.cumulative_scrap_quantity,0)-NVL(op.quantity_completed,0))
197 - res.applied_resource_units)*muc2.conversion_rate/
198 muc1.conversion_rate, 0),
199 DECODE(res.applied_resource_units, 0,
200 NVL(res.usage_rate_or_amount*muc2.conversion_rate/
201 muc1.conversion_rate, 0),
202 0))/compute_days_between (g_spread_load,res.start_date,res.completion_date)
203 FROM mtl_uom_conversions muc2,
204 wip_discrete_jobs jobs,
205 wip_operations op,
206 wip_operation_resources res,
207 mrp_form_query list,
208 mtl_uom_conversions muc1
209 WHERE muc1.inventory_item_id = 0
210 AND muc1.uom_code = g_hour_uom
211 AND muc2.uom_code = res.uom_code
212 AND muc2.inventory_item_id = 0
213 AND muc2.uom_class = muc1.uom_class
214 AND nvl(res.start_date,jobs.scheduled_start_date) <g_cutoff_date
215 AND jobs.status_type IN (JOB_UNRELEASED,
216 JOB_COMPLETE, JOB_HOLD, JOB_RELEASED)
217 AND jobs.job_type in (WIP_NONSTANDARD,WIP_DISCRETE)
218 AND jobs.organization_id = op.organization_id
219 AND jobs.wip_entity_id = op.wip_entity_id
220 AND nvl((op.scheduled_quantity * res.usage_rate_or_amount -
221 res.applied_resource_units),1) > 0
222 AND op.scheduled_quantity - NVL(op.quantity_completed, 0) > 0
223 AND op.wip_entity_id = res.wip_entity_id
224 AND op.operation_seq_num = res.operation_seq_num
225 AND op.organization_id = res.organization_id
226 AND op.department_id in (select department_id
227 from crp_planned_resources cpr
228 where cpr.compile_designator = g_designator
229 and cpr.organization_id = list.number1
230 and cpr.owning_department_id = list.number2)
231 AND res.wip_entity_id >= 0
232 AND res.organization_id = list.number1
233 AND res.resource_id = list.number3
234 AND list.number3 is not null
235 AND list.query_id = g_item_list_id
236 UNION ALL
237 ----------------------------------------
238 --- Flow Schedule Dept/Res Reqs - tested
239 ----------------------------------------
240 SELECT
241 fs.organization_id,
242 fs.primary_item_id,
243 seqs.department_id,
244 res.resource_id,
245 -1,
246 DISCRETE_JOBS,
247 trunc(res_start.calendar_date),
248 to_date(NULL),
249 DECODE(res.basis_type, BASIS_PER_ITEM,
250 GREATEST( NVL(res.usage_rate_or_amount*muc2.conversion_rate/
251 muc1.conversion_rate, 0)*
252 (NVL(fs.planned_quantity,0) -
253 NVL(fs.quantity_completed,0)),0),
254 NVL(res.usage_rate_or_amount*muc2.conversion_rate/
255 muc1.conversion_rate, 0))
256 FROM bom_calendar_dates res_start,
257 bom_calendar_dates order_date,
258 mtl_system_items items,
259 mtl_parameters param,
260 wip_flow_schedules fs,
261 bom_operational_routings routing,
262 bom_operation_sequences seqs,
263 bom_operation_resources res,
264 bom_resources bom_res,
265 mtl_uom_conversions muc2,
266 mtl_uom_conversions muc1,
267 mrp_form_query list
268 WHERE res_start.exception_set_id = param.calendar_exception_set_id
269 AND res_start.calendar_code = param.calendar_code
270 AND res_start.seq_num = order_date.prior_seq_num -
271 ceil((1 - NVL(res.resource_offset_percent/100,0)) *
272 (NVL(items.fixed_lead_time,0) + NVL(items.variable_lead_time,0) *
273 (fs.planned_quantity - NVL(fs.quantity_completed,0))))
274 AND TRUNC(res_start.calendar_date) < g_cutoff_date
275 AND order_date.exception_set_id = param.calendar_exception_set_id
276 AND order_date.calendar_code = param.calendar_code
277 AND order_date.calendar_date = fs.scheduled_completion_date
278 AND param.organization_id = fs.organization_id
279 AND items.organization_id = fs.organization_id
280 AND items.inventory_item_id = fs.primary_item_id
281 AND nvl(fs.alternate_routing_designator, '-23453')
282 = nvl(routing.alternate_routing_designator, '-23453')
283 AND fs.organization_id = routing.organization_id
284 AND fs.primary_item_id = routing.assembly_item_id
285 AND fs.scheduled_completion_date >= TRUNC(sysdate)
286 AND routing.common_routing_sequence_id = seqs.routing_sequence_id
287 AND routing.organization_id = bom_res.organization_id
288 AND TRUNC(seqs.effectivity_date) <= TRUNC(g_dates(1))
289 AND nvl(seqs.disable_date, TRUNC(SYSDATE + 1)) > TRUNC(g_dates(1))
290 AND nvl(seqs.operation_type, 1) = 1
291 AND muc2.inventory_item_id = 0
292 AND muc2.uom_class = muc1.uom_class
293 AND muc2.uom_code = bom_res.unit_of_measure
294 AND muc1.inventory_item_id = 0
295 AND muc1.uom_code = g_hour_uom
296 AND seqs.department_id = list.number2
297 AND seqs.operation_sequence_id = res.operation_sequence_id
298 AND res.resource_id = bom_res.resource_id
299 AND bom_res.organization_id = list.number1
300 AND bom_res.resource_id = list.number3
301 AND list.number3 is not null
302 AND list.query_id = g_item_list_id
303 UNION ALL
304 -- ===================================
305 -- Repetitive Dept/Res Reqs - tested
306 -- ===================================
307 SELECT
308 res.organization_id,
309 items.primary_item_id,
310 op.department_id,
311 res.resource_id,
312 -1,
313 REPETITIVE,
314 NVL(res.start_date, rep.first_unit_start_date),
315 NVL(res.completion_date, rep.last_unit_completion_date),
316 DECODE(res.basis_type, BASIS_PER_ITEM,
317 NVL((res.usage_rate_or_amount*op.scheduled_quantity
318 - res.applied_resource_units)*muc2.conversion_rate/
319 muc1.conversion_rate, 0),
320 DECODE(res.applied_resource_units, 0,
321 NVL(res.usage_rate_or_amount*muc2.conversion_rate/
322 muc1.conversion_rate, 0),
323 0))
324 FROM mtl_uom_conversions muc2,
325 wip_repetitive_schedules rep,
326 wip_repetitive_items items,
327 wip_operations op,
328 wip_operation_resources res,
329 mrp_form_query list,
330 mtl_uom_conversions muc1
331 WHERE muc1.inventory_item_id = 0
332 AND muc1.uom_code = g_hour_uom
333 AND muc2.uom_code = res.uom_code
334 AND muc2.inventory_item_id = 0
335 AND muc2.uom_class = muc1.uom_class
336 AND items.wip_entity_id = rep.wip_entity_id
337 AND items.organization_id = rep.organization_id
338 AND items.line_id = rep.line_id
339 AND nvl(res.start_date,rep.first_unit_start_date) < g_cutoff_date
340 AND rep.status_type IN (JOB_RELEASED, JOB_UNRELEASED, JOB_COMPLETE,
341 JOB_HOLD)
342 AND rep.organization_id = op.organization_id
343 AND rep.repetitive_schedule_id = op.repetitive_schedule_id
344 AND nvl((op.scheduled_quantity * res.usage_rate_or_amount -
345 res.applied_resource_units),1) > 0
346 AND op.repetitive_schedule_id = res.repetitive_schedule_id
347 AND op.operation_seq_num = res.operation_seq_num
348 AND op.organization_id = res.organization_id
349 AND op.department_id = list.number2
350 AND res.wip_entity_id >= 0
351 AND res.organization_id = list.number1
352 AND res.resource_id = list.number3
353 AND list.number3 is not null
354 AND list.query_id = g_item_list_id
355 UNION ALL
356 -- ===================================
357 -- Planned Order line Requirement
358 -- ===================================
359 SELECT
360 mr.organization_id,
361 mr.inventory_item_id,
362 -1,
363 -1,
364 mr.line_id,
365 PLANNED_ORDER,
366 trunc(mr.new_wip_start_date),
367 to_date(null),
368 (mr.new_order_quantity - NVL(mr.implemented_quantity,0) +
369 NVL(mr.quantity_in_process,0))
370 FROM mrp_recommendations mr,
371 mrp_form_query list
372 WHERE mr.order_type = M_PLANNED_ORDER
373 AND mr.new_wip_start_date < g_cutoff_date
374 AND mr.compile_designator = g_designator
375 AND mr.line_id = list.number2
376 AND mr.organization_id = list.number1
377 AND list.number3 is null
378 AND list.query_id = g_item_list_id
379 UNION ALL
380 -- ==================================
381 -- Discrete Job line Requirement - tested
382 -- ==================================
383 SELECT
384 jobs.organization_id,
385 jobs.primary_item_id,
386 -1,
387 -1,
388 jobs.line_id,
389 DECODE(jobs.job_type,
390 WIP_NONSTANDARD, NONSTD_JOBS,
391 WIP_DISCRETE, DISCRETE_JOBS),
392 jobs.scheduled_start_date,
393 to_date(NULL),
394 SUM(GREATEST( 0, (jobs.net_quantity - jobs.quantity_completed
395 - jobs.quantity_scrapped)))
396 FROM wip_discrete_jobs jobs,
397 mrp_form_query list
398 WHERE jobs.scheduled_start_date <g_cutoff_date
399 AND jobs.status_type IN (JOB_UNRELEASED,
400 JOB_COMPLETE, JOB_HOLD, JOB_RELEASED)
401 AND jobs.job_type in (WIP_NONSTANDARD,WIP_DISCRETE)
402 AND jobs.net_quantity > 0
403 AND jobs.wip_entity_id >= 0
404 AND jobs.organization_id = list.number1
405 AND jobs.line_id = list.number2
406 AND list.number3 is null
407 AND list.query_id = g_item_list_id
408 GROUP BY
409 jobs.organization_id,
410 jobs.primary_item_id,
411 jobs.line_id,
412 jobs.scheduled_start_date,
413 jobs.job_type
414 UNION ALL
415 -- =====================================
416 -- Flow Schedules line requirement
417 -- =====================================
418 SELECT
419 fs.organization_id,
420 fs.primary_item_id,
421 -1,
422 -1,
423 fs.line_id,
424 DISCRETE_JOBS,
425 fs.scheduled_start_date,
426 to_date(NULL),
427 SUM(GREATEST( 0, (fs.planned_quantity - fs.quantity_completed)))
428 FROM wip_flow_schedules fs,
429 mrp_form_query list
430 WHERE fs.scheduled_start_date <g_cutoff_date
431 AND fs.scheduled_completion_date >= TRUNC(sysdate)
432 AND fs.wip_entity_id >= 0
433 AND fs.organization_id = list.number1
434 AND fs.line_id = list.number2
435 AND list.number3 is null
436 AND list.query_id = g_item_list_id
437 GROUP BY
438 fs.organization_id,
439 fs.primary_item_id,
440 fs.line_id,
441 fs.scheduled_start_date
442 UNION ALL
443 -- =====================================
444 -- Repetitive Schedules line requirement - tested
445 -- =====================================
446 SELECT
447 sched.organization_id,
448 rep_items.primary_item_id,
449 -1,
450 -1,
451 sched.line_id,
452 REPETITIVE,
453 TRUNC(sched.first_unit_start_date),
454 TRUNC(sched.last_unit_start_date),
455 sched.daily_production_rate*lines.maximum_rate/
456 rep_items.PRODUCTION_LINE_RATE
457 FROM wip_repetitive_items rep_items,
458 wip_repetitive_schedules sched,
459 wip_lines lines,
460 mrp_form_query list
461 WHERE rep_items.organization_id = sched.organization_id
462 AND rep_items.line_id = sched.line_id
463 AND rep_items.wip_entity_id = sched.wip_entity_id
464 AND TRUNC(sched.first_unit_start_date) < g_cutoff_date
465 AND TRUNC(sched.last_unit_start_date) >= g_dates(1)
466 AND sched.status_type IN (JOB_UNRELEASED,
467 JOB_RELEASED, JOB_COMPLETE, JOB_HOLD)
468 AND sched.organization_id = lines.organization_id
469 AND sched.line_id = lines.line_id
470 AND lines.organization_id = list.number1
471 AND lines.line_id = list.number2
472 AND list.number3 is null
473 AND list.query_id = g_item_list_id
474 -- =========================
475 -- Line availability
476 -- =========================
477 UNION ALL
478 SELECT
479 line.organization_id,
480 TO_NUMBER(NULL),
481 -1,
482 -1,
483 line.line_id,
484 AVAILABLE_HOURS,
485 g_dates(1),
486 trunc(g_cutoff_date-1),
487 nvl(line.maximum_rate, 0) *
488 (decode(least(line.start_time, line.stop_time),
489 line.stop_time, (line.stop_time + (24 *3600)),
490 line.stop_time) - line.start_time) / 3600
491 FROM wip_lines line,
492 mrp_form_query list
493 WHERE nvl(line.disable_date, sysdate + 1) > sysdate
494 AND line.organization_id = list.number1
495 AND line.line_id = list.number2
496 AND list.number3 is null
497 AND list.query_id = g_item_list_id
498 -- ============================
499 -- Dept/Resource Availability
500 -- ============================
501 UNION ALL
502 SELECT
503 param.organization_id,
504 TO_NUMBER(NULL),
505 dept_res.department_id,
506 dept_res.resource_id,
507 -1,
508 AVAILABLE_HOURS,
509 trunc(cal.calendar_date),
510 trunc(cal.calendar_date),
511 SUM(DECODE(dept_res.available_24_hours_flag,
512 1, dept_res.capacity_units * 24 *
513 NVL(dept_res.utilization, 1.0) *
514 NVL(dept_res.efficiency, 1.0),
515 (DECODE(LEAST(shifts.to_time, shifts.from_time),
516 shifts.to_time, shifts.to_time + 24*3600,
517 shifts.to_time) - shifts.from_time) *
518 (dept_res.capacity_units/3600) *
519 NVL(dept_res.utilization, 1.0) *
520 NVL(dept_res.efficiency, 1.0) ))
521 FROM bom_shift_times shifts,
522 bom_resource_shifts res_shifts,
523 bom_department_resources dept_res,
524 mtl_parameters param,
525 bom_calendar_dates cal,
526 mrp_form_query list
527 WHERE (shifts.calendar_code is NULL OR
528 shifts.calendar_code = param.calendar_code)
529 AND shifts.shift_num (+) = res_shifts.shift_num
530 AND res_shifts.resource_id (+)= dept_res.resource_id
531 AND res_shifts.department_id (+)= dept_res.department_id
532 AND dept_res.share_from_dept_id is NULL
533 AND cal.calendar_code = param.calendar_code
534 AND cal.exception_set_id = param.calendar_exception_set_id
535 AND trunc(cal.calendar_date) >= trunc(sysdate)
536 AND ((cal.seq_num is not null
537 and not exists ( select 1 from CRP_CAL_SHIFT_DELTA delta1
538 where delta1.calendar_code =
539 shifts.calendar_code
540 and delta1.exception_set_id =
541 param.calendar_exception_set_id
542 and delta1.delta_code = 1
543 and delta1.calendar_date = cal.calendar_date
544 and delta1.shift_num = shifts.shift_num))
545 OR
546 (cal.seq_num is null
547 and exists ( select 1 from CRP_CAL_SHIFT_DELTA delta1
548 where delta1.calendar_code =
549 shifts.calendar_code
550 and delta1.exception_set_id =
551 param.calendar_exception_set_id
552 and delta1.delta_code = 2
553 and delta1.calendar_date = cal.calendar_date
554 and delta1.shift_num = shifts.shift_num)))
555 AND dept_res.department_id = list.number2
556 AND dept_res.resource_id = list.number3
557 AND param.organization_id = list.number1
558 AND list.number3 is not null
559 AND list.query_id = g_item_list_id
560 GROUP BY
561 param.organization_id,
562 dept_res.department_id,
563 dept_res.resource_id,
564 trunc(cal.calendar_date)
565 -- ================================
566 -- Dept/Resource modif/add workday
567 -- ================================
568 UNION ALL
569 SELECT
570 orgs.planned_organization,/*2681093*/
571 TO_NUMBER(NULL),
572 brc.department_id,
573 brc.resource_id,
574 -1,
575 CAP_CHANGES,
576 dates.calendar_date,
577 dates.calendar_date,
578 (brc.capacity_change *
579 (DECODE(LEAST(NVL(brc.from_time, 0), NVL(brc.to_time, 1)),
580 NVL(brc.to_time, 1), 24 * 3600 + NVL(brc.to_time, 1),
581 NVL(brc.to_time, 1)) - NVL(brc.from_TIME, 0)))/3600
582 FROM bom_calendar_dates dates,
583 bom_resource_changes brc,
584 mrp_plan_organizations_v orgs,
585 mtl_parameters param,
586 mrp_form_query list
587 WHERE dates.exception_set_id = param.calendar_exception_set_id
588 AND dates.calendar_code = param.calendar_code
589 AND (brc.action_type = ADD_WORK_DAY
590 OR dates.seq_num is not NULL
591 OR (dates.seq_num IS NULL
592 and exists ( select 1
593 from CRP_CAL_SHIFT_DELTA delta1
594 where delta1.calendar_code =
595 dates.calendar_code
596 and delta1.exception_set_id =
597 dates.exception_set_id
598 and delta1.delta_code = 2
599 and delta1.calendar_date =
600 dates.calendar_date
601 and delta1.shift_num = brc.shift_num)))
602 AND dates.calendar_date BETWEEN TRUNC(brc.from_date)
603 AND TRUNC(NVL(brc.to_date, brc.from_date))
604 AND TRUNC(brc.from_date) < g_cutoff_date
605 AND TRUNC(NVL(brc.to_date, brc.from_date)) >= g_dates(1)
606 AND brc.simulation_set = orgs.simulation_set
607 AND brc.department_id = list.number2
608 AND brc.resource_id = list.number3
609 AND brc.action_type in (ADD_WORK_DAY, MODIFY_WORK_DAY)
610 AND orgs.compile_designator = g_designator
611 AND orgs.planned_organization = param.organization_id
612 AND orgs.organization_id = g_org_id
613 AND param.organization_id = list.number1
614 AND list.number3 is not null
615 AND list.query_id = g_item_list_id
616 AND not exists
617 (SELECT 'Exists'
618 FROM bom_resource_changes brc2
619 WHERE brc2.department_id = brc.department_id
620 AND brc2.resource_id = brc.resource_id
621 AND brc2.shift_num = brc.shift_num
622 AND brc2.action_type = DELETE_WORK_DAY
623 AND brc2.to_date = dates.calendar_date
624 AND brc2.from_date is null
625 AND brc2.simulation_set = brc.simulation_set)
626 -- ===============================================================
627 -- Resource delete work days
628 -- Note, we don't have capacity modifications for
629 -- borrowed resources. Therefore, the following query will not
630 -- return any rows for borrowed resources in a department
631 -- ===============================================================
632 UNION ALL
633 SELECT
634 orgs.planned_organization,/*2681093*/
635 TO_NUMBER(NULL),
636 brc.department_id,
637 brc.resource_id,
638 -1,
639 CAP_CHANGES,
640 brc.from_date,
641 brc.from_date,
642 -1 * SUM((DECODE(LEAST(shifts.to_time, shifts.from_time),
643 shifts.to_time, shifts.to_time + 24*3600,
644 shifts.to_time) - shifts.from_time) *
645 dept_res.capacity_units / 3600)
646 FROM bom_shift_times shifts,
647 bom_resource_changes brc,
648 bom_department_resources dept_res,
649 mrp_plan_organizations_v orgs,
650 mtl_parameters param,
651 mrp_form_query list
652 WHERE shifts.calendar_code = param.calendar_code
653 AND shifts.shift_num = brc.shift_num
654 AND TRUNC(brc.from_date) < g_cutoff_date
655 AND TRUNC(brc.from_date) >= g_dates(1)
656 AND brc.action_type = DELETE_WORK_DAY
657 AND brc.simulation_set = orgs.simulation_set
658 AND brc.department_id = dept_res.department_id
659 AND brc.resource_id = dept_res.resource_id
660 AND dept_res.department_id = list.number2
661 AND dept_res.resource_id = list.number3
662 AND orgs.compile_designator = g_designator
663 AND orgs.planned_organization = param.organization_id
664 AND orgs.organization_id = g_org_id
665 AND param.organization_id = list.number1
666 AND list.number3 is not null
667 AND list.query_id = g_item_list_id
668 GROUP BY
669 orgs.planned_organization, /*2681093*/
670 brc.department_id,
671 brc.resource_id,
672 brc.from_date,
673 brc.from_date
674 ORDER BY 1,3,4,5,7,6;
675
676
677
678
679 CURSOR crp_snapshot_activity IS
680 -- ===================================
681 -- Dept/Resource requirements
682 -- ===================================
683 SELECT
684 crp.organization_id,
685 crp.assembly_item_id,
686 cpr.owning_department_id,
687 crp.resource_id,
688 -1,
689 decode(crp.supply_type,
690 M_PLANNED_ORDER, PLANNED_ORDER,
691 M_REPETITIVE, REPETITIVE,
692 M_DISCRETE_JOBS, DISCRETE_JOBS,
693 M_NONSTD_JOBS, NONSTD_JOBS,
694 M_FLOW_SCHEDULES, DISCRETE_JOBS),
695 trunc(crp.resource_date),
696 trunc(crp.resource_end_date),
697 decode(crp.resource_end_date,
698 NULL, crp.resource_hours,
699 crp.daily_resource_hours)
700 FROM
701 crp_resource_plan crp,
702 crp_planned_resources cpr,
703 mrp_form_query list
704 WHERE
705 cpr.owning_department_id = list.number2
706 AND cpr.resource_id = list.number3
707 AND cpr.compile_designator = g_designator
708 AND cpr.organization_id = list.number1
709 AND crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE,
710 M_DISCRETE_JOBS,M_NONSTD_JOBS, M_FLOW_SCHEDULES)
711 AND crp.designator = cpr.compile_designator
712 AND crp.resource_date < g_cutoff_date
713 AND crp.resource_id = cpr.resource_id
714 AND crp.department_id = cpr.department_id
715 AND crp.organization_id = cpr.organization_id
716 AND list.number3 is not null
717 AND list.query_id = g_item_list_id
718 UNION ALL
719 -- ============================================
720 -- Discrete job/Planned order Line Requirement
721 -- ============================================
722 SELECT /*+ ORDERED
723 USE_NL(mr)
724 INDEX(list MRP_FORM_QUERY_N1)
725 INDEX(mr MRP_RECOMMENDATIONS_N2) */
726 mr.organization_id,
727 mr.inventory_item_id,
728 -1,
729 -1,
730 mr.line_id,
731 decode(mr.order_type,
732 M_PLANNED_ORDER, PLANNED_ORDER,
733 M_DISCRETE_JOBS, DISCRETE_JOBS,
734 M_NONSTD_JOBS, NONSTD_JOBS,
735 M_FLOW_SCHEDULES, DISCRETE_JOBS),
736 NVL(mr.new_wip_start_date,mr.new_schedule_date),
737 to_date(null),
738 mr.new_order_quantity
739 FROM mrp_form_query list,
740 mrp_recommendations mr
741 WHERE mr.order_type in (M_PLANNED_ORDER, M_DISCRETE_JOBS,
742 M_NONSTD_JOBS, M_FLOW_SCHEDULES)
743 AND mr.disposition_status_type <> 2
744 AND mr.new_schedule_date < g_cutoff_date
745 AND mr.compile_designator = g_designator
746 AND mr.line_id = list.number2
747 AND mr.organization_id = list.number1
748 AND list.number3 is null
749 AND list.query_id = g_item_list_id
750 UNION ALL
751 -- ============================================
752 -- RCCP Planned order Line Requirement
753 -- Note, line_id is stored in department_id
754 -- and resource_id is set to -1
755 -- ============================================
756 SELECT
757 crp.organization_id,
758 crp.source_item_id,
759 -1,
760 -1,
761 crp.department_id,
762 decode(crp.supply_type,
763 M_PLANNED_ORDER, PLANNED_ORDER,
764 M_REPETITIVE, REPETITIVE),
765 trunc(crp.resource_date),
766 to_date(NULL),
767 crp.load_rate
768 FROM crp_resource_plan crp,
769 mrp_form_query list
770 WHERE crp.supply_type in (M_PLANNED_ORDER, M_REPETITIVE)
771 AND crp.designator = g_designator
772 AND crp.resource_date < g_cutoff_date
773 AND crp.resource_id = -1
774 AND crp.department_id = list.number2
775 AND crp.organization_id = list.number1
776 AND list.number3 is null
777 AND list.query_id = g_item_list_id
778 UNION ALL
779 -- ===================================
780 -- Repetitive schedule line requirements
781 -- ===================================
782 SELECT /*+ORDERED
783 INDEX(list MRP_FORM_QUERY_N1)
784 INDEX(msrs MRP_SUGG_REP_SCHEDULES_N3) */
785 msrs.organization_id,
786 msrs.inventory_item_id,
787 -1,
788 -1,
789 msrs.repetitive_line,
790 REPETITIVE,
791 trunc(msrs.first_unit_start_date),
792 trunc(least(msrs.last_unit_start_date,g_cutoff_date-1)),
793 msrs.load_factor_rate
794 FROM mrp_form_query list,
795 mrp_sugg_rep_schedules msrs
796 WHERE msrs.first_unit_start_date < g_cutoff_date
797 AND msrs.compile_designator = g_designator
798 AND msrs.organization_id = list.number1
799 AND msrs.repetitive_line = list.number2
800 AND list.number3 is null
801 AND list.query_id = g_item_list_id
802 UNION ALL
803 -- ===================================
804 -- Availability for Dept/Res
805 -- ===================================
806 SELECT
807 avail.organization_id,
808 TO_NUMBER(NULL),
809 NVL(avail.department_id,-1),
810 NVL(avail.resource_id,-1),
811 NVL(avail.line_id,-1),
812 AVAILABLE_HOURS,
813 trunc(avail.resource_start_date),
814 trunc(least(nvl(avail.resource_end_date,g_cutoff_date), g_cutoff_date-1)),
815 avail.resource_units * avail.resource_hours
816 FROM crp_available_resources avail,
817 mrp_form_query list
818 WHERE avail.resource_start_date < g_cutoff_date
819 AND nvl(avail.resource_end_date, g_cutoff_date) >= g_dates(1)
820 AND avail.compile_designator = g_designator
821 AND avail.organization_id = list.number1
822 AND avail.department_id = list.number2
823 AND avail.resource_id = list.number3
824 AND list.number3 is not null
825 AND list.query_id = g_item_list_id
826 UNION ALL
827 -- ===================================
828 -- Availability for Lines
829 -- ===================================
830 SELECT
831 avail.organization_id,
832 TO_NUMBER(NULL),
833 NVL(avail.department_id,-1),
834 NVL(avail.resource_id,-1),
835 NVL(avail.line_id,-1),
836 AVAILABLE_HOURS,
837 trunc(avail.resource_start_date),
838 trunc(least(nvl(avail.resource_end_date,g_cutoff_date),g_cutoff_date-1)),
839 avail.max_rate
840 FROM crp_available_resources avail,
841 mrp_form_query list
842 WHERE avail.resource_start_date < g_cutoff_date
843 AND nvl(avail.resource_end_date, g_cutoff_date) >= g_dates(1)
844 AND avail.compile_designator = g_designator
845 AND avail.organization_id = list.number1
846 AND avail.line_id = list.number2
847 AND list.number3 is null
848 AND list.query_id = g_item_list_id
849 ORDER BY
850 1,3,4,5,7,6;
851
852
853 -- =============================================================================
854 -- Name: initialize
855 -- Desc: initializes most of the global variables in the package
856 -- g_spread_load - indicates if we want to spread capacity load or not
857 -- g_hour_uom - stores the hour uom at this site
858 -- g_date() - is the structure that holds the beginning of each bucket
859 -- g_date_seq() - Holds the date seq of for each date in g_date. Note
860 -- The date seq is for the calendar of the current org
861 -- =============================================================================
862 PROCEDURE initialize IS
863 -- -----------------------------------------
864 -- This cursor selects row type information.
865 -- -----------------------------------------
866 v_sid NUMBER;
867 v_counter NUMBER;
868 BEGIN
869
870 -- --------------------------
871 -- initialize profile value
872 -- --------------------------
873 g_spread_load := NVL(FND_PROFILE.VALUE('CRP_SPREAD_LOAD'), SYS_NO);
874 g_hour_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
875
876 -- --------------------------
877 -- initialize query id
878 -- --------------------------
879 g_error_stmt := 'Debug - initialize - 10';
880 SELECT crp_form_query_s.nextval
881 INTO g_query_id
882 FROM dual;
883
884 -- --------------------------
885 -- initialize calendar code
886 -- --------------------------
887 g_error_stmt := 'Debug - initialize - 20';
888 SELECT calendar_code, calendar_exception_set_id
889 INTO g_calendar_code, g_exc_set_id
890 FROM mtl_parameters
891 WHERE organization_id = g_planned_org;
892
893 -- -------------------------------
894 -- initialize plan_start_date
895 -- -------------------------------
896 g_error_stmt := 'Debug - initialize - 15';
897 SELECT trunc(plan_start_date)
898 INTO g_plan_start_date
899 FROM mrp_plans
900 WHERE compile_designator = g_designator
901 AND organization_id = g_org_id;
902
903 OPEN seqnum_cursor(g_plan_start_date);
904 FETCH seqnum_cursor INTO g_plan_start_seq;
905 CLOSE seqnum_cursor;
906
907 -- ----------------------------------
908 -- Initialize the bucket cells to 0.
909 -- ----------------------------------
910 g_error_stmt := 'Debug - initialize - 50';
911 FOR v_counter IN 1..(NUM_OF_TYPES * NUM_OF_COLUMNS) LOOP
912 bucket_cells(v_counter) := 0;
913 END LOOP;
914
915 -- --------------------
916 -- Get the bucket dates
917 -- --------------------
918 g_error_stmt := 'Debug - initialize - 60';
919 SELECT
920 date1, date2, date3, date4,
921 date5, date6, date7, date8,
922 date9, date10, date11, date12,
923 date13, date14, date15, date16,
924 date17, date18, date19, date20,
925 date21, date22, date23, date24,
926 date25, date26, date27, date28,
927 date29, date30, date31, date32,
928 date33, date34, date35, date36,
929 date37
930 INTO
931 g_dates(1), g_dates(2), g_dates(3), g_dates(4),
932 g_dates(5), g_dates(6), g_dates(7), g_dates(8),
933 g_dates(9), g_dates(10), g_dates(11), g_dates(12),
934 g_dates(13), g_dates(14), g_dates(15), g_dates(16),
935 g_dates(17), g_dates(18), g_dates(19), g_dates(20),
936 g_dates(21), g_dates(22), g_dates(23), g_dates(24),
937 g_dates(25), g_dates(26), g_dates(27), g_dates(28),
938 g_dates(29), g_dates(30), g_dates(31), g_dates(32),
939 g_dates(33), g_dates(34), g_dates(35), g_dates(36),
940 g_dates(37)
941 FROM mrp_workbench_bucket_dates
942 WHERE organization_id = g_org_id
943 AND nvl(planned_organization,organization_id) =
944 g_planned_org
945 AND compile_designator = g_designator
946 AND bucket_type = DECODE(g_current_data,
947 SYS_YES, DECODE(g_bucket_type,
948 1, -1,
949 2, -2,
950 3, -3), g_bucket_type);
951
952 -- ----------------------------
953 -- populate the g_date_seq
954 -- memory structure.
955 -- ----------------------------
956 g_error_stmt := 'Debug - initialize - 70';
957 FOR v_counter IN 1..NUM_OF_COLUMNS+1 LOOP
958 OPEN seqnum_cursor(g_dates(v_counter));
959 FETCH seqnum_cursor INTO g_date_seq(v_counter);
960 CLOSE seqnum_cursor;
961 -- if g_date_seq(v_counter) is null, raise an error
962 END LOOP;
963 g_error_stmt := 'Debug - initialize - 80';
964
965 END initialize;
966
967
968
969 -- =============================================================================
970 -- Name: get_number_work_days
971 -- Desc: returns the number of workdays between start and end date, inclusive
972 -- =============================================================================
973 FUNCTION get_number_work_days(
974 start_date DATE,
975 end_date DATE) RETURN NUMBER IS
976 v_start_seq NUMBER;
977 v_end_seq NUMBER;
978 BEGIN
979 IF (trunc(start_date) <= trunc(end_date)) THEN
980 OPEN seqnum_cursor(trunc(start_date));
981 FETCH seqnum_cursor INTO v_start_seq;
982 CLOSE seqnum_cursor;
983 OPEN prior_seqnum_cursor(trunc(end_date));
984 FETCH prior_seqnum_cursor INTO v_end_seq;
985 CLOSE prior_seqnum_cursor;
986 g_error_stmt := 'Debug - get_number_work_days - 10 - sdates ';
987 IF (v_end_seq - v_start_seq + 1) > 0 THEN
988 RETURN(v_end_seq - v_start_seq + 1);
989 ELSE
990 RETURN(1);
991 END IF;
992 ELSE
993 return(0);
994 END IF;
995 END;
996
997 -- =============================================================================
998 -- Name: compute_days_between
999 -- Desc: returns the number of workdays between start and end date, inclusive
1000 -- =============================================================================
1001 FUNCTION compute_days_between(
1002 spread_load NUMBER,
1003 start_date DATE,
1004 end_date DATE) RETURN NUMBER IS
1005 v_days_between NUMBER;
1006 BEGIN
1007 if (spread_load = SYS_YES) then
1008 v_days_between := get_number_work_days (start_date,end_date) ;
1009 else
1010 v_days_between := 1 ;
1011 end if ;
1012 if (v_days_between = 0) then v_days_between := 1 ; end if ;
1013 return(v_days_between) ;
1014 END ;
1015
1016
1017 -- =============================================================================
1018 -- Name: add_to_plan
1019 -- Desc: adds 'quantity' to the correct type and correct bucket cell.
1020 -- If the end_date of the record is populated, then the qty is assumed
1021 -- to have daily rather than total qty. We calculate # of workdays in each
1022 -- bucket in the range of start_date-end_date, and populate each
1023 -- bucket accordingly
1024 -- =============================================================================
1025 PROCEDURE add_to_plan IS
1026 v_location NUMBER;
1027 v_bucket_start DATE;
1028 v_counter NUMBER;
1029 v_bucket_size NUMBER;
1030 v_res_cost NUMBER := 0;
1031 BEGIN
1032
1033 g_error_stmt := 'Debug - add_to_plan - 0';
1034 -- --------------------------------------------------
1035 -- Find cost information, note, only for lines we
1036 -- calculate the line cost in add_to_plan. For
1037 -- resources, v_res_cost is set to 0 and will
1038 -- not contribute to the resource cost. Resource
1039 -- cost is calculated in calculate_cum instead.
1040 -- dbms_output.put_line(g_error_stmt);
1041 -- --------------------------------------------------
1042 IF (activity_rec.line_id <> -1 AND
1043 activity_rec.type in (PLANNED_ORDER,NONSTD_JOBS,
1044 DISCRETE_JOBS,REPETITIVE)) THEN
1045 SELECT NVL(cst.item_cost, 0)
1046 INTO v_res_cost
1047 FROM cst_item_costs cst,
1048 mtl_parameters org
1049 WHERE cst.cost_type_id(+) = org.primary_cost_method
1050 AND cst.organization_id(+) = org.cost_organization_id
1051 AND cst.inventory_item_id(+) = activity_rec.assembly_item_id
1052 AND org.organization_id = activity_rec.org_id;
1053 END IF;
1054
1055 g_error_stmt := 'Debug - add_to_plan - 10';
1056 IF (activity_rec.start_date >= g_dates(g_bucket_count)) THEN
1057 -- -------------------------------------------------------
1058 -- We got an activity which falls after the current bucket. So we
1059 -- will move the bucket counter forward until we find the
1060 -- bucket where this activity falls. Note that we should
1061 -- not advance the counter beyond NUM_OF_COLUMNS.
1062 -- --------------------------------------------------------
1063 WHILE ((activity_rec.start_date >= g_dates(g_bucket_count)) AND
1064 (g_bucket_count <= NUM_OF_COLUMNS))
1065 LOOP
1066 g_bucket_count := g_bucket_count + 1;
1067 END LOOP;
1068
1069 ---------------------------------------------------------------
1070 --- If the activity start date is outside the last bucket there
1071 --- is no need to add this activity to any bucket.
1072 ---------------------------------------------------------------
1073 if(activity_rec.start_date >= g_dates(g_bucket_count)) THEN
1074 return;
1075 end if;
1076
1077 END IF;
1078
1079 ---------------------------------------------------------------
1080 --- BUG # 2402184
1081 --- In case of supply chain plan, if the organization_id of the
1082 --- current record is different from the planned_org, reset the
1083 --- global variable - g_planned_org and g_calendar_code
1084 ---------------------------------------------------------------
1085
1086 IF (activity_rec.org_id <> g_planned_org) THEN
1087
1088 g_planned_org := activity_rec.org_id;
1089
1090 SELECT calendar_code, calendar_exception_set_id
1091 INTO g_calendar_code, g_exc_set_id
1092 FROM mtl_parameters
1093 WHERE organization_id = g_planned_org;
1094
1095 END IF;
1096
1097 IF (activity_rec.end_date is null) THEN
1098 -- -------------------------------------------------------
1099 -- end date is null, we assume that the quantity
1100 -- stands for total quantity and we dump the total
1101 -- quantity on the first bucket
1102 -- --------------------------------------------------------
1103 g_error_stmt := 'Debug - add_to_plan - 20';
1104 v_location := ((activity_rec.type-1) * NUM_OF_COLUMNS) +
1105 g_bucket_count - 1;
1106 bucket_cells(v_location) := bucket_cells(v_location) +
1107 activity_rec.quantity;
1108 v_location := ((RESOURCE_COST-1) * NUM_OF_COLUMNS) +
1109 g_bucket_count - 1;
1110 bucket_cells(v_location) := bucket_cells(v_location) +
1111 activity_rec.quantity * v_res_cost;
1112
1113 ELSE -- IF (activity_rec.end_date is not null) THEN
1114 -- -------------------------------------------------------
1115 -- If end date is not null, we assume that the quantity
1116 -- stands for daily quantity. We multiply the daily qty
1117 -- by the # of workdays in each bucket to find the bucketed
1118 -- quantity
1119 -- --------------------------------------------------------
1120 g_error_stmt := 'Debug - add_to_plan - 30';
1121 v_counter := g_bucket_count;
1122
1123 -- --------------------------------------------------------
1124 -- We only count availability starting from the start
1125 -- date of the first bucket; however, we count pass due
1126 -- resource requirements
1127 -- --------------------------------------------------------
1128 IF (activity_rec.type in (AVAILABLE_HOURS,CAP_CHANGES)) THEN
1129 v_bucket_start := greatest(activity_rec.start_date,g_dates(1),
1130 g_plan_start_date);
1131 ELSE
1132 v_bucket_start := activity_rec.start_date;
1133 END IF;
1134
1135
1136 -- -------------------------------------------------------
1137 -- This loop loads data from the first bucket until
1138 -- the bucket before last. Last bucket needs special
1139 -- logic
1140 -- --------------------------------------------------------
1141 WHILE((v_counter <= NUM_OF_COLUMNS) AND
1142 (activity_rec.end_date >= g_dates(v_counter)))
1143 LOOP
1144 g_error_stmt := 'Debug - add_to_plan - 40 - loop'||to_char(v_counter);
1145 v_bucket_size := get_number_work_days(v_bucket_start, g_dates(v_counter)-1);
1146
1147 v_location := ((activity_rec.type-1) * NUM_OF_COLUMNS) +
1148 v_counter - 1;
1149
1150 bucket_cells(v_location) := bucket_cells(v_location) +
1151 v_bucket_size * activity_rec.quantity;
1152
1153 v_location := ((RESOURCE_COST-1) * NUM_OF_COLUMNS) +
1154 v_counter - 1;
1155 bucket_cells(v_location) := bucket_cells(v_location) +
1156 v_bucket_size * activity_rec.quantity * v_res_cost;
1157 v_bucket_start := g_dates(v_counter);
1158
1159 -- ------------------------------------------------------
1160 -- For Debuging, uncomment following
1161 -- dbms_output.put_line('Ay_id'||to_char(activity_rec.assembly_item_id));
1162 -- dbms_output.put_line('D_id'||to_char(activity_rec.department_id));
1163 -- dbms_output.put_line('R_id'||to_char(activity_rec.resource_id));
1164 -- dbms_output.put_line('L_id'||to_char(activity_rec.line_id));
1165 -- dbms_output.put_line('Type - '||to_char(activity_rec.type));
1166 -- dbms_output.put_line('Start - '||to_char(activity_rec.start_date));
1167 -- dbms_output.put_line('End - '||to_char(activity_rec.end_date));
1168 -- dbms_output.put_line('Qty - '||to_char(activity_rec.quantity));
1169 -- dbms_output.put_line('Buc - '||to_char(v_counter-1));
1170 -- dbms_output.put_line('Buc size - '||to_char(v_bucket_size));
1171 -- dbms_output.put_line('Buc Start - '||to_char(g_dates(v_counter-1)));
1172 -- dbms_output.put_line('=========');
1173 ------------------------------------------------------
1174 v_counter := v_counter + 1;
1175 END LOOP;
1176
1177 -- ----------------------------------------------------
1178 -- Load the last bucket. We first find the number
1179 -- of workdays in the last time bucket.
1180 -- ----------------------------------------------------
1181 g_error_stmt := 'Debug - add_to_plan - 50';
1182 IF (activity_rec.end_date = activity_rec.start_date AND
1183 activity_rec.type in (AVAILABLE_HOURS,CAP_CHANGES)) THEN
1184 -- -----------------------------------------------
1185 -- The special case: If user has added a
1186 -- workday on a non-workday, we need to include it.
1187 -- We know a workday has been added if the
1188 -- start_date = end_date
1189 -- ------------------------------------------------
1190 v_bucket_size := 1;
1191 ELSE
1192 IF (activity_rec.end_date <= g_dates(v_counter)) THEN
1193 v_bucket_size := get_number_work_days(v_bucket_start,
1194 activity_rec.end_date);
1195 ELSE
1196 v_bucket_size := get_number_work_days(v_bucket_start,
1197 g_dates(v_counter-1));
1198 END IF;
1199 END IF;
1200
1201 v_location := ((activity_rec.type-1) * NUM_OF_COLUMNS) +
1202 v_counter - 1;
1203 bucket_cells(v_location) := bucket_cells(v_location) +
1204 v_bucket_size * activity_rec.quantity;
1205
1206 v_location := ((RESOURCE_COST-1) * NUM_OF_COLUMNS) +
1207 v_counter - 1;
1208 bucket_cells(v_location) := bucket_cells(v_location) +
1209 v_bucket_size * activity_rec.quantity * v_res_cost;
1210
1211 -- ------------------------------------------------------
1212 -- For Debuging, uncomment following
1213 -- dbms_output.put_line('Ay_id'||to_char(activity_rec.assembly_item_id));
1214 -- dbms_output.put_line('D_id'||to_char(activity_rec.department_id));
1215 -- dbms_output.put_line('R_id'||to_char(activity_rec.resource_id));
1216 -- dbms_output.put_line('L_id'||to_char(activity_rec.line_id));
1217 -- dbms_output.put_line('Type - '||to_char(activity_rec.type));
1218 -- dbms_output.put_line('Start - '||to_char(activity_rec.start_date));
1219 -- dbms_output.put_line('End - '||to_char(activity_rec.end_date));
1220 -- dbms_output.put_line('Qty - '||to_char(activity_rec.quantity));
1221 -- dbms_output.put_line('Buc - '||to_char(v_counter-1));
1222 -- dbms_output.put_line('Buc size - '||to_char(v_bucket_size));
1223 -- dbms_output.put_line('Buc Start - '||to_char(g_dates(v_counter-1)));
1224 -- dbms_output.put_line('=========');
1225 ------------------------------------------------------
1226 END IF;
1227
1228 END add_to_plan;
1229
1230
1231
1232 -- =============================================================================
1233 -- Name: calculate_cum
1234 -- Desc: Some types of data need to be calculated or cumulated across dates. This
1235 -- procedure takes care of that
1236 -- =============================================================================
1237 PROCEDURE calculate_cum(
1238 p_org_id NUMBER,
1239 p_dept_id NUMBER,
1240 p_line_id NUMBER,
1241 p_res_id NUMBER) IS
1242
1243 v_loop BINARY_INTEGER := 1;
1244 v_cum_net_available NUMBER := 0;
1245 v_cum_available NUMBER := 0;
1246 v_cum_required NUMBER := 0;
1247 v_cum_changes NUMBER := 0;
1248 v_overhead NUMBER := 0;
1249 v_res_cost NUMBER := 0;
1250 v_num_days NUMBER := 0;
1251 BEGIN
1252
1253 -- ---------------------------------
1254 -- The following will be calculated:
1255 -- REQUIRED_HOURS
1256 -- NET_AVAILABLE
1257 -- CUM_AVAILABLE
1258 -- UTILIZATION
1259 -- CUM_UTILIZATION
1260 -- DAILY_REQUIRED
1261 -- DAILY_AVAILABLE
1262 -- RESOURCE_COST
1263 -- -----------------------------
1264 IF (p_line_id = -1) THEN
1265
1266 /***** Bug 1965639
1267 SELECT SUM(NVL(rate_or_amount,0))
1268 INTO v_overhead
1269 FROM cst_department_overheads
1270 WHERE organization_id = p_org_id
1271 AND department_id = p_dept_id
1272 AND cost_type_id = CST_FROZEN;
1273
1274 IF v_overhead is NULL THEN
1275 v_overhead := 0;
1276 END IF;
1277
1278 BEGIN
1279 SELECT (1 + v_overhead)*NVL(res.resource_rate,0)
1280 INTO v_res_cost
1281 FROM cst_resource_costs res
1282 WHERE res.organization_id = p_org_id
1283 AND res.resource_id = p_res_id
1284 AND res.cost_type_id = CST_FROZEN;
1285 EXCEPTION when no_data_found THEN
1286 v_res_cost := 0;
1287 NULL;
1288 END;
1289 ******/
1290
1291 /* Get resource cost */
1292
1293 BEGIN
1294 SELECT NVL(res.resource_rate,0)
1295 INTO v_res_cost
1296 FROM cst_resource_costs res
1297 WHERE res.organization_id = p_org_id
1298 AND res.resource_id = p_res_id
1299 AND res.cost_type_id = CST_FROZEN;
1300 EXCEPTION when no_data_found THEN
1301 v_res_cost := 0;
1302 END;
1303
1304 /* Bug 1965639 : Get the overhead rate. Consider only those overheads
1305 that have a basis of Resource Value or Resource Units.
1306 When the basis is Resource Value, the overhead rate is multiplied
1307 by the resource cost. */
1308
1309 SELECT SUM(decode(basis_type,BASIS_RESOURCE_VALUE, v_res_cost * NVL(rate_or_amount,0),
1310 NVL(rate_or_amount,0)))
1311 INTO v_overhead
1312 FROM cst_department_overheads
1313 WHERE organization_id = p_org_id
1314 AND department_id = p_dept_id
1315 AND cost_type_id = CST_FROZEN
1316 AND basis_type in (BASIS_RESOURCE_VALUE, BASIS_RESOURCE_UNITS)
1317 AND overhead_id IN (SELECT overhead_id
1318 FROM cst_resource_overheads res
1319 WHERE res.organization_id = p_org_id
1320 AND res.resource_id = p_res_id
1321 AND cost_type_id = CST_FROZEN);
1322
1323 v_res_cost := v_res_cost + nvl(v_overhead,0);
1324
1325 END IF;
1326
1327 g_error_stmt := 'Debug - calculate_cum - 10';
1328 FOR v_loop IN 1..NUM_OF_COLUMNS LOOP
1329 -- -------------------
1330 -- Required Hours
1331 -- -------------------
1332 g_error_stmt := 'Debug - calculate_cum - 20 - loop'||to_char(v_loop);
1333 bucket_cells((REQUIRED_HOURS-1)*NUM_OF_COLUMNS+v_loop) :=
1334 bucket_cells((PLANNED_ORDER-1)*NUM_OF_COLUMNS+v_loop) +
1335 bucket_cells((NONSTD_JOBS-1)*NUM_OF_COLUMNS+v_loop) +
1336 bucket_cells((DISCRETE_JOBS-1)*NUM_OF_COLUMNS+v_loop) +
1337 bucket_cells((REPETITIVE-1)*NUM_OF_COLUMNS+v_loop);
1338
1339 -- -------------------
1340 -- Available hours
1341 -- -------------------
1342 g_error_stmt := 'Debug - calculate_cum - 25 - loop'||to_char(v_loop);
1343 bucket_cells((AVAILABLE_HOURS-1)*NUM_OF_COLUMNS+v_loop) :=
1344 bucket_cells((AVAILABLE_HOURS-1)*NUM_OF_COLUMNS+v_loop) +
1345 bucket_cells((CAP_CHANGES-1)*NUM_OF_COLUMNS+v_loop);
1346
1347 -- -------------------
1348 -- Net Available Hours
1349 -- -------------------
1350 g_error_stmt := 'Debug - calculate_cum - 30 - loop'||to_char(v_loop);
1351 bucket_cells((NET_AVAILABLE-1)*NUM_OF_COLUMNS+v_loop) :=
1352 bucket_cells((AVAILABLE_HOURS-1)*NUM_OF_COLUMNS+v_loop) -
1353 bucket_cells((REQUIRED_HOURS-1)*NUM_OF_COLUMNS+v_loop);
1354
1355 -- ----------------------------
1356 -- Cumulatitive Available Hours
1357 -- ----------------------------
1358 v_cum_net_available := v_cum_net_available +
1359 bucket_cells((NET_AVAILABLE-1)*NUM_OF_COLUMNS+v_loop);
1360 bucket_cells((CUM_AVAILABLE-1)*NUM_OF_COLUMNS+v_loop) := v_cum_net_available;
1361
1362 -- ----------------------------
1363 -- Capacity Utilization
1364 -- ----------------------------
1365 g_error_stmt := 'Debug - calculate_cum - 40 - loop'||to_char(v_loop);
1366 IF (bucket_cells((AVAILABLE_HOURS-1)*NUM_OF_COLUMNS+v_loop) <= 0) THEN
1367 bucket_cells((UTILIZATION-1)*NUM_OF_COLUMNS+v_loop) := NULL;
1368 ELSE
1369 bucket_cells((UTILIZATION-1)*NUM_OF_COLUMNS+v_loop) := 100 *
1370 bucket_cells((REQUIRED_HOURS-1)*NUM_OF_COLUMNS+v_loop) /
1371 bucket_cells((AVAILABLE_HOURS-1)*NUM_OF_COLUMNS+v_loop);
1372 END IF;
1373
1374 -- ----------------------------
1375 -- Cum Capacity Utilization
1376 -- ----------------------------
1377 g_error_stmt := 'Debug - calculate_cum - 50 - loop'||to_char(v_loop);
1378 v_cum_required := v_cum_required +
1379 bucket_cells((REQUIRED_HOURS-1)*NUM_OF_COLUMNS+v_loop);
1380 v_cum_available := v_cum_available +
1381 bucket_cells((AVAILABLE_HOURS-1)*NUM_OF_COLUMNS+v_loop);
1382 IF (v_cum_available <= 0) THEN
1383 bucket_cells((CUM_UTILIZATION-1)*NUM_OF_COLUMNS+v_loop) := NULL;
1384 ELSE
1385 bucket_cells((CUM_UTILIZATION-1)*NUM_OF_COLUMNS+v_loop) := 100 *
1386 v_cum_required / v_cum_available;
1387 END IF;
1388
1389 -- ----------------------------
1390 -- Daily Required Hours and
1391 -- Daily Available Hours
1392 -- ----------------------------
1393 g_error_stmt := 'Debug - calculate_cum - 60 - loop'||to_char(v_loop);
1394 IF (v_loop = 1) THEN
1395 v_num_days := g_date_seq(v_loop+1) -
1396 greatest(g_date_seq(v_loop), g_plan_start_seq);
1397 /*2692616*/ ELSIF g_date_seq.EXISTS(v_loop+1) THEN
1398 v_num_days := g_date_seq(v_loop+1) - g_date_seq(v_loop);
1399 END IF;
1400 /*2692616*/IF g_date_seq.EXISTS(v_loop+1) THEN
1401 if (v_num_days <> 0) then
1402 bucket_cells((DAILY_REQUIRED-1)*NUM_OF_COLUMNS+v_loop) :=
1403 bucket_cells((REQUIRED_HOURS-1)*NUM_OF_COLUMNS+v_loop) /
1404 v_num_days;
1405 bucket_cells((DAILY_AVAILABLE-1)*NUM_OF_COLUMNS+v_loop) :=
1406 bucket_cells((AVAILABLE_HOURS-1)*NUM_OF_COLUMNS+v_loop) /
1407 v_num_days;
1408 end if;
1409 /*2692616*/ END IF;
1410
1411 -- --------------------------
1412 -- Cost for lines are already
1413 -- populated in add_plan
1414 -- --------------------------
1415 IF (p_line_id = -1) THEN
1416 bucket_cells((RESOURCE_COST-1)*NUM_OF_COLUMNS+v_loop) := v_res_cost *
1417 bucket_cells((REQUIRED_HOURS-1)*NUM_OF_COLUMNS+v_loop);
1418 END IF;
1419 END LOOP;
1420
1421 END calculate_cum;
1422
1423
1424
1425
1426 -- =============================================================================
1427 -- Name: flush_crp_plan
1428 -- Desc: It inserts the date for 1 dept/res or line into CRP_CAPACITY_PLANS
1429 -- =============================================================================
1430 PROCEDURE flush_crp_plan(
1431 p_org_id NUMBER,
1432 p_dept_id NUMBER,
1433 p_line_id NUMBER,
1434 p_res_id NUMBER) IS
1435 v_dept_code VARCHAR2(10) := '';
1436 v_line_code VARCHAR2(10) := '';
1437 v_dept_class_code VARCHAR2(10) := '';
1438 v_res_code VARCHAR2(10) := '';
1439 v_res_grp_name VARCHAR2(30) := '';
1440 v_resource_type_code VARCHAR2(80) := '';
1441 v_loop BINARY_INTEGER := 1;
1442 BEGIN
1443
1444 g_error_stmt := 'Debug - flush_crp_plan - 5';
1445 IF (p_line_id = -1) THEN
1446 -- NUM_OF_TYPES := 15;
1447 SELECT dept.department_code,
1448 dept.department_class_code,
1449 res.resource_code,
1450 lkps.meaning,
1451 dept_res.resource_group_name
1452 INTO v_dept_code,
1453 v_dept_class_code,
1454 v_res_code,
1455 v_resource_type_code,
1456 v_res_grp_name
1457 FROM mfg_lookups lkps,
1458 bom_resources res,
1459 bom_department_resources dept_res,
1460 bom_departments dept
1461 WHERE lkps.lookup_type(+) = 'BOM_RESOURCE_TYPE'
1462 AND lkps.lookup_code(+) = res.resource_type
1463 AND res.resource_id = p_res_id
1464 AND dept_res.department_id = p_dept_id
1465 AND dept_res.resource_id = p_res_id
1466 AND dept.department_id = p_dept_id;
1467 ELSE
1468 -- NUM_OF_TYPES := 9;
1469 SELECT line.line_code
1470 INTO v_line_code
1471 FROM wip_lines line
1472 WHERE line.line_id = p_line_id;
1473 END IF;
1474
1475 g_error_stmt := 'Debug - flush_crp_plan - 10';
1476 FOR v_loop IN 1..NUM_OF_TYPES LOOP
1477
1478 g_error_stmt := 'Debug - flush_crp_plan - 30 - loop'||to_char(v_loop);
1479 INSERT INTO crp_capacity_plans(
1480 query_id,
1481 last_update_date,
1482 last_updated_by,
1483 creation_date,
1484 created_by,
1485 last_update_login,
1486 organization_id,
1487 department_id,
1488 resource_id,
1489 line_id,
1490 type_id,
1491 department_name,
1492 department_class,
1493 resource_name,
1494 resource_type,
1495 resource_group_name,
1496 line_name,
1497 period1, period2, period3, period4,
1498 period5, period6, period7, period8,
1499 period9, period10, period11, period12,
1500 period13, period14, period15, period16,
1501 period17, period18, period19, period20,
1502 period21, period22, period23, period24,
1503 period25, period26, period27, period28,
1504 period29, period30, period31, period32,
1505 period33, period34, period35, period36)
1506 VALUES (
1507 g_query_id,
1508 SYSDATE,
1509 -1,
1510 SYSDATE,
1511 -1,
1512 -1,
1513 p_org_id,
1514 p_dept_id,
1515 p_res_id,
1516 p_line_id,
1517 v_loop,
1518 NVL(v_dept_code, v_line_code),
1519 v_dept_class_code,
1520 v_res_code,
1521 v_resource_type_code,
1522 v_res_grp_name,
1523 v_line_code,
1524 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+1),
1525 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+2),
1526 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+3),
1527 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+4),
1528 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+5),
1529 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+6),
1530 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+7),
1531 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+8),
1532 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+9),
1533 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+10),
1534 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+11),
1535 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+12),
1536 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+13),
1537 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+14),
1538 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+15),
1539 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+16),
1540 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+17),
1541 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+18),
1542 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+19),
1543 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+20),
1544 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+21),
1545 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+22),
1546 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+23),
1547 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+24),
1548 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+25),
1549 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+26),
1550 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+27),
1551 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+28),
1552 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+29),
1553 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+30),
1554 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+31),
1555 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+32),
1556 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+33),
1557 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+34),
1558 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+35),
1559 bucket_cells((v_loop-1)*NUM_OF_COLUMNS+36));
1560 END LOOP;
1561 END flush_crp_plan;
1562
1563
1564 -- =============================================================================
1565 -- Name: re_initialize
1566 -- Desc: This is called everytime we work on a new dept/resource
1567 -- Initializes cost information as well as calendar code
1568 -- =============================================================================
1569 PROCEDURE re_initialize IS
1570 v_overhead NUMBER;
1571 BEGIN
1572
1573 g_bucket_count := 2;
1574
1575 g_error_stmt := 'Debug - re_initialize - 10';
1576 -- ----------------------------------
1577 -- Initialize the bucket cells to 0.
1578 -- dbms_output.put_line(g_error_stmt);
1579 -- ----------------------------------
1580 FOR v_cnt IN 1..NUM_OF_TYPES*NUM_OF_COLUMNS LOOP
1581 bucket_cells(v_cnt) := 0;
1582 END LOOP;
1583
1584 g_error_stmt := 'Debug - re_initialize - 20';
1585 -- ----------------------------
1586 -- Find the exception_set_id
1587 -- and calendar_code for this
1588 -- organization
1589 -- dbms_output.put_line(g_error_stmt);
1590 -- ----------------------------
1591 SELECT calendar_code, calendar_exception_set_id
1592 INTO g_calendar_code, g_exc_set_id
1593 FROM mtl_parameters
1594 WHERE organization_id = activity_rec.org_id;
1595
1596 END re_initialize;
1597
1598
1599 -- =============================================================================
1600 -- Name:populate_horizontal_plan
1601 -- This is the main procedure. It retrieves data from database and calls
1602 -- private procedures to summarize them into user defined buckets.
1603 -- The argument p_current_data tells us whether to use current data
1604 -- or snapshoted data for bucketing.
1605 -- The p_bucket_type tells us what kind of buckets to use for summarization.
1606 -- p_bucket_type 1 - ??daily buckets??
1607 -- 2 - ??weekly buckets??
1608 -- 3 - ??periodic buckets??
1609 -- p_current_data 1 - Use current data
1610 -- 2 - Use snapshot data
1611 --
1612 -- =============================================================================
1613 /* 2663505 - Removed the defaulting of p_current_data since this parameter
1614 is always passed. */
1615
1616 FUNCTION populate_horizontal_plan(
1617 p_item_list_id IN NUMBER,
1618 p_planned_org IN NUMBER,
1619 p_org_id IN NUMBER,
1620 p_compile_designator IN VARCHAR2,
1621 p_bucket_type IN NUMBER,
1622 p_cutoff_date IN DATE,
1623 p_current_data IN NUMBER) RETURN NUMBER IS
1624 v_no_rows BOOLEAN;
1625 v_last_dept_id NUMBER := -2;
1626 v_last_org_id NUMBER := -2;
1627 v_last_res_id NUMBER := -2;
1628 v_last_line_id NUMBER := -2;
1629 v_cnt NUMBER;
1630 BEGIN
1631
1632 -- ----------------------------
1633 -- Initialize Global variables
1634 -- ----------------------------
1635 g_error_stmt := 'Debug - populate_horizontal_plan - 10';
1636 g_item_list_id := p_item_list_id;
1637 g_org_id := p_org_id;
1638 g_planned_org := p_planned_org;
1639 g_designator :=p_compile_designator;
1640 g_bucket_type := p_bucket_type;
1641 g_cutoff_date := p_cutoff_date;
1642 g_current_data := p_current_data;
1643
1644 initialize;
1645 -- dbms_output.put_line(g_error_stmt);
1646
1647 g_error_stmt := 'Debug - populate_horizontal_plan - 20';
1648 -- dbms_output.put_line(g_error_stmt);
1649
1650 IF g_current_data = SYS_YES THEN
1651 OPEN crp_current_activity;
1652 ELSE
1653 OPEN crp_snapshot_activity;
1654 END IF;
1655
1656
1657 -- ----------------------------
1658 -- Fetch rows from cursor
1659 -- and process them one by one
1660 -- ----------------------------
1661 LOOP
1662 v_no_rows := FALSE;
1663 g_error_stmt := 'Debug - populate_horizontal_plan - 30';
1664 -- dbms_output.put_line(g_error_stmt);
1665
1666 IF g_current_data = SYS_YES THEN
1667 FETCH crp_current_activity INTO activity_rec;
1668 IF (crp_current_activity%NOTFOUND) THEN
1669 v_no_rows := TRUE;
1670 END IF;
1671 ELSE
1672 FETCH crp_snapshot_activity INTO activity_rec;
1673 IF (crp_snapshot_activity%NOTFOUND) THEN
1674 v_no_rows := TRUE;
1675 END IF;
1676 END IF;
1677
1678 g_error_stmt := 'Debug - populate_horizontal_plan - 40';
1679 -- dbms_output.put_line(g_error_stmt);
1680 IF ((v_no_rows OR
1681 v_last_org_id <> activity_rec.org_id OR
1682 v_last_dept_id <> activity_rec.department_id OR
1683 v_last_line_id <> activity_rec.line_id OR
1684 v_last_res_id <> activity_rec.resource_id) AND
1685 v_last_dept_id <> -2) THEN
1686 -- ==================================================
1687 -- snapshoting for the last dept/res has finished
1688 -- We therefore calculate cumulative information,
1689 -- flush the previous set of data and then
1690 -- re-initialized for the current dept/res
1691 -- ==================================================
1692 g_error_stmt := 'Debug - populate_horizontal_plan - 50';
1693 calculate_cum(v_last_org_id,v_last_dept_id,
1694 v_last_line_id,v_last_res_id);
1695
1696 g_error_stmt := 'Debug - populate_horizontal_plan - 60';
1697 flush_crp_plan(v_last_org_id,v_last_dept_id,
1698 v_last_line_id,v_last_res_id);
1699
1700 g_error_stmt := 'Debug - populate_horizontal_plan - 70';
1701 re_initialize;
1702 END IF;
1703
1704 EXIT WHEN v_no_rows;
1705
1706 g_error_stmt := 'Debug - populate_horizontal_plan - 85';
1707 -- ---------------------------------------------------------
1708 -- Add the retrieved activity to the plan
1709 -- dbms_output.put_line(g_error_stmt);
1710 -- ---------------------------------------------------------
1711 add_to_plan;
1712
1713 v_last_org_id := activity_rec.org_id;
1714 v_last_res_id := activity_rec.resource_id;
1715 v_last_dept_id := activity_rec.department_id;
1716 v_last_line_id := activity_rec.line_id;
1717 END LOOP;
1718
1719 g_error_stmt := 'Debug - populate_horizontal_plan - 90';
1720 -- dbms_output.put_line(g_error_stmt);
1721 IF g_current_data = SYS_YES THEN
1722 CLOSE crp_current_activity;
1723 ELSE
1724 CLOSE crp_snapshot_activity;
1725 END IF;
1726
1727 return g_query_id;
1728
1729 EXCEPTION WHEN others THEN
1730 -- dbms_output.put_line(g_error_stmt);
1731 IF (seqnum_cursor%ISOPEN) THEN
1732 close seqnum_cursor;
1733 END IF;
1734 IF (crp_current_activity%ISOPEN) THEN
1735 close crp_current_activity;
1736 END IF;
1737 IF (crp_snapshot_activity%ISOPEN) THEN
1738 close crp_snapshot_activity;
1739 END IF;
1740 raise;
1741 END populate_horizontal_plan;
1742
1743 procedure MrpAppletPage(filename Varchar2,appname Varchar2) is
1744 server_name Varchar2(1000);
1745 server_port Varchar2(1000);
1746 base_url Varchar2(1000);
1747 begin
1748 server_name := owa_util.get_cgi_env('SERVER_NAME');
1749 server_port := owa_util.get_cgi_env('SERVER_PORT');
1750 if (server_port is not null) then
1751 base_url := server_name || ':' || server_port;
1752 else
1753 base_url := server_name;
1754 end if;
1755 htp.htmlOpen;
1756 htp.p('<applet codebase=/OA_JAVA/
1757 code="oracle.apps.mrp.graphs.ChartRenderer.class"
1758 archive=oracle/apps/mrp/jar/mrpjar.jar
1759 width=1200
1760 height=400 >
1761 <param name="File"
1762 value='||replace(filename,' ','+')||'>
1763 <param name="App"
1764 value=' || appname || '>
1765 </applet>');
1766 htp.htmlClose;
1767
1768 end MrpAppletPage;
1769
1770 END mrp_crp_horizontal_plan;