[Home] [Help]
PACKAGE BODY: APPS.CRP_PLANNER_PK
Source
4 -- ********************** start_plan *************************
1 PACKAGE BODY crp_planner_pk AS
2 /* $Header: CRPPPLNB.pls 115.1 99/07/16 10:31:09 porting ship $ */
3
5 PROCEDURE start_plan(
6 arg_compile_desig IN VARCHAR2,
7 arg_org_id IN NUMBER,
8 arg_user_id IN NUMBER) IS
9 BEGIN
10 UPDATE mrp_plans
11 SET crp_plan_start_date = SYSDATE,
12 last_update_date = SYSDATE,
13 last_updated_by = arg_user_id
14 WHERE organization_id = arg_org_id
15 AND compile_designator = arg_compile_desig;
16
17 COMMIT;
18 END start_plan;
19
20 -- ********************** complete_plan *************************
21 PROCEDURE complete_plan(
22 arg_compile_desig IN VARCHAR2,
23 arg_org_id IN NUMBER,
24 arg_user_id IN NUMBER) IS
25 BEGIN
26 UPDATE mrp_plans
27 SET crp_plan_completion_date = SYSDATE,
28 last_update_date = SYSDATE,
29 last_updated_by = arg_user_id
30 WHERE organization_id = arg_org_id
31 AND compile_designator = arg_compile_desig;
32
33 COMMIT;
34 END complete_plan;
35 -- ********************** plan_jobs *************************
36 PROCEDURE plan_jobs(
37 arg_compile_desig IN VARCHAR2,
38 arg_org_id IN NUMBER,
39 arg_user_id IN NUMBER,
40 arg_cutoff_date IN DATE,
41 arg_request_id IN NUMBER,
42 arg_calendar_code IN VARCHAR2,
43 arg_exception_set_id IN NUMBER) IS
44 var_watch_id NUMBER;
45 var_row_count NUMBER;
46 var_spread_load NUMBER;
47 BEGIN
48
49 var_watch_id := mrp_print_pk.start_watch('CAP-load discrete jobs',
50 arg_request_id,
51 arg_user_id);
52
53 var_spread_load := TO_NUMBER(FND_PROFILE.VALUE('CRP_SPREAD_LOAD'));
54
55
56 /*-------------------------------------------------------------------+
57 | Load the resource plan for discrete jobs into crp_resource_hours |
58 +------------------------------------------------------------------*/
59
60 --- To correctly calculate the resource end date, we find the difference
61 --- between the actual # of days the requiremente should be spread
62 --- and the current amount of days we spread now. We correct the
63 ---
64 INSERT INTO crp_resource_plan
65 (transaction_id,
66 department_id,
67 resource_id,
68 organization_id,
69 designator,
70 source_transaction_id,
71 assembly_item_id,
72 last_update_date,
73 last_updated_by,
74 creation_date,
75 created_by,
76 last_update_login,
77 resource_date,
78 resource_hours,
79 repetitive_type,
80 operation_seq_num,
81 resource_seq_num,
82 resource_end_date,
83 daily_resource_hours)
84 SELECT crp_resource_plan_s.nextval,
85 resources.department_id,
86 resources.resource_id,
87 resources.organization_id,
88 arg_compile_desig,
89 recom.transaction_id,
90 recom.inventory_item_id,
91 SYSDATE,
92 arg_user_id,
93 SYSDATE,
94 arg_user_id,
95 -1,
96 res_date.calendar_date,
97 DECODE(SIGN(resources.operation_hours_required
98 - resources.hours_expended), -1, 0,
99 (resources.operation_hours_required -
103 resources.resource_seq_num,
100 resources.hours_expended)),
101 NOT_REPETITIVE_PLANNED,
102 resources.operation_seq_num,
104 DECODE(var_spread_load,
105 1, greatest(res_end.calendar_date, res_date.calendar_date),
106 NULL),
107 DECODE(SIGN(resources.operation_hours_required
108 - resources.hours_expended), -1, 0,
109 (resources.operation_hours_required -
110 resources.hours_expended))/
111 (greatest(res_end.seq_num, res_date.seq_num) -
112 res_date.seq_num + 1)
113 FROM bom_calendar_dates res_end,
114 bom_calendar_dates old_end_date,
115 bom_calendar_dates res_date,
116 bom_calendar_dates old_start_date,
117 mrp_wip_resources resources,
118 MRP_recommendations recom
119 WHERE res_end.exception_set_id = arg_exception_set_id
120 AND res_end.calendar_code = arg_calendar_code
121 AND res_end.seq_num =
122 old_end_date.prior_seq_num + NVL(recom.reschedule_days, 0) -
123 DECODE(resources.resource_end_date, NULL, 0,
124 ((TRUNC(resources.resource_end_date)-
125 TRUNC(resources.first_unit_start_date)+1) -
126 ceil(resources.resource_end_date-resources.first_unit_start_date)))
127 AND old_end_date.exception_set_id = arg_exception_set_id
128 AND old_end_date.calendar_code = arg_calendar_code
129 AND old_end_date.calendar_date =
130 NVL(TRUNC(resources.resource_end_date),
131 TRUNC(resources.first_unit_start_date))
132 AND res_date.seq_num =
133 old_start_date.prior_seq_num + NVL(recom.reschedule_days, 0)
134 AND res_date.calendar_date <= arg_cutoff_date
135 AND res_date.exception_set_id = arg_exception_set_id
136 AND res_date.calendar_code = arg_calendar_code
137 AND old_start_date.calendar_date =
138 TRUNC(resources.first_unit_start_date)
139 AND old_start_date.exception_set_id = arg_exception_set_id
140 AND old_start_date.calendar_code = arg_calendar_code
141 AND resources.organization_id = recom.organization_id
142 AND resources.compile_designator = recom.compile_designator
143 AND resources.wip_entity_id = recom.disposition_id
147 (select planned_organization
144 AND recom.disposition_status_type <> CANCEL_ORDER
145 AND recom.order_type IN (WORK_ORDER, NONSTD_JOB)
146 AND recom.organization_id IN
148 from mrp_plan_organizations_v
149 where organization_id = arg_org_id
150 and compile_designator = arg_compile_desig)
151 AND resources.operation_hours_required > resources.hours_expended
152 AND recom.compile_designator = arg_compile_desig;
153
154
155 var_row_count := SQL%ROWCOUNT;
156 mrp_print_pk.stop_watch(arg_request_id,
157 var_watch_id,
158 var_row_count);
159 COMMIT;
160 END plan_jobs;
161
162 -- ********************** plan_discrete *************************
163 PROCEDURE plan_discrete(
164 arg_compile_desig IN VARCHAR2,
165 arg_org_id IN NUMBER,
166 arg_user_id IN NUMBER,
167 arg_cutoff_date IN DATE,
168 arg_request_id IN NUMBER,
169 arg_calendar_code IN VARCHAR2,
170 arg_exception_set_id IN NUMBER) IS
171 var_watch_id NUMBER;
172 var_row_count NUMBER;
173 var_spread_load NUMBER;
174 BEGIN
175 var_watch_id := mrp_print_pk.start_watch('CAP-load planned orders',
176 arg_request_id,
177 arg_user_id);
178
179 var_spread_load := TO_NUMBER(FND_PROFILE.VALUE('CRP_SPREAD_LOAD'));
180
181 INSERT INTO crp_resource_plan
182 (transaction_id,
183 department_id,
184 resource_id,
185 organization_id,
186 designator,
190 last_updated_by,
187 source_transaction_id,
188 assembly_item_id,
189 last_update_date,
191 creation_date,
192 created_by,
193 last_update_login,
194 resource_date,
195 resource_hours,
196 repetitive_type,
197 operation_seq_num,
198 resource_seq_num,
199 resource_end_date,
200 daily_resource_hours)
201 SELECT crp_resource_plan_s.nextval,
202 labor.department_id,
203 labor.resource_id,
204 labor.organization_id,
205 arg_compile_desig,
206 recom.transaction_id,
207 recom.inventory_item_id,
208 SYSDATE,
209 arg_user_id,
210 SYSDATE,
211 arg_user_id,
212 -1,
213 res_date.calendar_date,
214 decode(labor.basis, BASIS_PER_ITEM,
215 (labor.runtime_quantity * recom.new_order_quantity),
216 labor.runtime_quantity),
217 NOT_REPETITIVE_PLANNED,
218 labor.operation_seq_num,
219 labor.resource_seq_num,
220 NULL,
221 NULL
222 FROM bom_calendar_dates res_date,
223 bom_calendar_dates sched_date,
224 mrp_system_items items,
225 mrp_planned_resource_reqs labor,
226 mrp_recommendations recom,
227 mrp_plans mp
228 WHERE res_date.seq_num =
229 TRUNC(sched_date.seq_num
230 - CEIL((items.variable_lead_time * new_order_quantity +
231 items.fixed_lead_time) *
232 (1 - NVL(labor.resource_offset_percent, 0))))
233 AND res_date.calendar_date <= arg_cutoff_date
234 AND res_date.exception_set_id = arg_exception_set_id
235 AND res_date.calendar_code = arg_calendar_code
236 AND sched_date.calendar_date =
237 recom.new_schedule_date
238 AND sched_date.exception_set_id = arg_exception_set_id
239 AND sched_date.calendar_code = arg_calendar_code
240 AND items.planning_make_buy_code = decode(
241 NVL(mp.use_new_planner,SYS_NO),
242 SYS_NO, MAKE, items.planning_make_buy_code)
243 AND items.organization_id = recom.organization_id
244 AND items.compile_designator = recom.compile_designator
245 AND items.inventory_item_id = recom.inventory_item_id
246 AND labor.organization_id = recom.organization_id
247 AND labor.compile_designator = recom.compile_designator
248 AND labor.using_assembly_item_id = recom.inventory_item_id
249 AND recom.organization_id = decode(mp.use_new_planner,
250 SYS_YES, nvl(recom.source_organization_id,
251 recom.organization_id),
252 recom.organization_id)
253 AND recom.disposition_status_type <> CANCEL_ORDER
254 AND recom.order_type = PLANNED_ORDER
255 AND recom.organization_id IN
256 (Select planned_organization
257 from mrp_plan_organizations_v
258 where organization_id = arg_org_id
259 and compile_designator =
260 arg_compile_desig)
261 AND recom.compile_designator = mp.compile_designator
262 AND mp.organization_id = arg_org_id
263 AND mp.compile_designator = arg_compile_desig;
264
265 var_row_count := SQL%ROWCOUNT;
266 mrp_print_pk.stop_watch(arg_request_id,
267 var_watch_id,
268 var_row_count);
269
270 if var_spread_load = 1 then
271
272 -- Update the resource end date of a resource requirement to
273 -- the start date of the next resource requirement within the
274 -- same operation.
275
276 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
277 arg_request_id,
278 arg_user_id,
279 'ENTITY',
280 'crp_resource_plan(1)',
281 'N');
282
286 FROM crp_resource_plan plan2
283 update crp_resource_plan plan1
284 set resource_end_date =
285 (SELECT resource_date
287 WHERE (plan2.resource_seq_num =
288 (select min(resource_seq_num)
289 from crp_resource_plan plan3
290 where plan3.source_transaction_id =
291 plan2.source_transaction_id
292 and plan3.operation_seq_num =
293 plan2.operation_seq_num
294 and plan3.resource_seq_num >
295 plan1.resource_seq_num)
296 AND plan2.source_transaction_id =
297 plan1.source_transaction_id
298 AND plan2.operation_seq_num = plan1.operation_seq_num))
299 where source_transaction_id in
300 (select transaction_id
301 from mrp_recommendations
302 where compile_designator = arg_compile_desig
303 and organization_id in
304 (select planned_organization
305 from mrp_plan_organizations_v
306 where compile_designator = arg_compile_desig
307 and organization_id = arg_org_id)
308 and order_type = PLANNED_ORDER);
309
310 var_row_count := SQL%ROWCOUNT;
311
312 mrp_print_pk.stop_watch(arg_request_id,
313 var_watch_id,
314 var_row_count);
315
316 -- Update the resource end date of last resource requirement in
317 -- an operation to the start date of the next operation
318
319 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
320 arg_request_id,
321 arg_user_id,
322 'ENTITY',
323 'crp_resource_plan(2)',
324 'N');
325
326 update crp_resource_plan plan1
327 set resource_end_date =
328 (SELECT resource_date
329 FROM crp_resource_plan plan2
330 WHERE (plan2.resource_seq_num, plan2.operation_seq_num) =
331 (SELECT min(resource_seq_num), min(operation_seq_num)
332 FROM crp_resource_plan plan3
333 where plan3.source_transaction_id =
334 plan2.source_transaction_id
335 and plan3.operation_seq_num >
336 plan1.operation_seq_num)
337 AND plan2.source_transaction_id =
338 plan1.source_transaction_id)
339 where source_transaction_id in
340 (select transaction_id
341 from mrp_recommendations
342 where compile_designator = arg_compile_desig
343 and organization_id in
344 (select planned_organization
345 from mrp_plan_organizations_v
349 and resource_end_date is null;
346 where compile_designator = arg_compile_desig
347 and organization_id = arg_org_id)
348 and order_type = PLANNED_ORDER)
350
351 var_row_count := SQL%ROWCOUNT;
352
353 mrp_print_pk.stop_watch(arg_request_id,
354 var_watch_id,
355 var_row_count);
356
357 -- Update the resource end date of the last resource requirement
358 -- for a planned order to the planned order due date
359 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
360 arg_request_id,
361 arg_user_id,
362 'ENTITY',
363 'crp_resource_plan(3)',
364 'N');
365
366 update crp_resource_plan plan1
367 set resource_end_date =
368 (select new_schedule_date
369 from mrp_recommendations
370 where transaction_id = plan1.source_transaction_id)
371 where source_transaction_id in
372 (select transaction_id
373 from mrp_recommendations
374 where compile_designator = arg_compile_desig
375 and organization_id in
376 (select planned_organization
377 from mrp_plan_organizations_v
378 where compile_designator = arg_compile_desig
379 and organization_id = arg_org_id)
380 and order_type = PLANNED_ORDER)
381 and resource_end_date is null;
382
383 mrp_print_pk.stop_watch(arg_request_id,
384 var_watch_id,
385 var_row_count);
386
387 end if;
388
389 COMMIT;
390 END plan_discrete;
391 -- ********************** plan_repetitive *************************
392 PROCEDURE plan_repetitive(
393 arg_compile_desig IN VARCHAR2,
394 arg_org_id IN NUMBER,
395 arg_user_id IN NUMBER,
396 arg_cutoff_date IN DATE,
397 arg_request_id IN NUMBER,
398 arg_calendar_code IN VARCHAR2,
399 arg_exception_set_id IN NUMBER) IS
400 var_watch_id NUMBER;
401 var_row_count NUMBER;
402
403 BEGIN
404
405 var_watch_id := mrp_print_pk.start_watch('CAP-load repetitive jobs',
406 arg_request_id,
407 arg_user_id);
408
409 INSERT INTO crp_resource_plan
410 (transaction_id,
411 department_id,
412 resource_id,
413 organization_id,
414 designator,
415 source_transaction_id,
416 assembly_item_id,
417 last_update_date,
418 last_updated_by,
419 creation_date,
420 created_by,
421 last_update_login,
422 resource_date,
423 resource_hours,
424 repetitive_type,
425 operation_seq_num,
426 resource_seq_num,
427 resource_end_date,
428 daily_resource_hours)
429 SELECT crp_resource_plan_s.nextval,
430 labor.department_id,
431 labor.resource_id,
432 labor.organization_id,
433 arg_compile_desig,
434 recom.transaction_id,
435 recom.inventory_item_id,
436 sysdate,
437 arg_user_id,
438 sysdate,
439 arg_user_id,
440 -1,
441 first_res_date.calendar_date,
442 decode(labor.basis, BASIS_PER_ITEM,
443 (labor.runtime_quantity * NVL(recom.daily_rate,0) *
444 (last_res_date.seq_num -
445 first_res_date.seq_num + 1)),
446 labor.runtime_quantity),
447 REPETITIVELY_PLANNED,
448 labor.operation_seq_num,
449 labor.resource_seq_num,
450 last_res_date.calendar_date,
451 decode(labor.basis, BASIS_PER_ITEM,
452 labor.runtime_quantity * NVL(recom.daily_rate,0),
453 labor.runtime_quantity /
454 (last_res_date.seq_num -
455 first_res_date.seq_num + 1))
456 FROM bom_calendar_dates last_res_date,
457 bom_calendar_dates first_res_date,
458 bom_calendar_dates last_due,
459 bom_calendar_dates first_due,
460 mrp_system_items items,
461 mrp_planned_resource_reqs labor,
462 mrp_recommendations recom,
463 mrp_plans mp
464 WHERE last_res_date.seq_num =
465 TRUNC(last_due.seq_num
466 - CEIL((items.variable_lead_time *
467 NVL(recom.daily_rate,0) +
468 items.fixed_lead_time) *
469 (1-NVL(labor.resource_offset_percent,0))))
470 AND last_res_date.calendar_date < arg_cutoff_date
474 TRUNC(first_due.seq_num
471 AND last_res_date.exception_set_id = arg_exception_set_id
472 AND last_res_date.calendar_code = arg_calendar_code
473 AND first_res_date.seq_num =
475 - CEIL((items.variable_lead_time *
476 NVL(recom.daily_rate,0) +
477 items.fixed_lead_time) *
478 (1-NVL(labor.resource_offset_percent,0))))
479 AND first_res_date.exception_set_id = arg_exception_set_id
480 AND first_res_date.calendar_code = arg_calendar_code
481 AND last_due.calendar_date =
482 recom.last_unit_completion_date
483 AND last_due.exception_set_id = arg_exception_set_id
484 AND last_due.calendar_code = arg_calendar_code
485 AND first_due.calendar_date = recom.new_schedule_date
486 AND first_due.exception_set_id = arg_exception_set_id
487 AND first_due.calendar_code = arg_calendar_code
488 AND items.organization_id = recom.organization_id
489 AND items.compile_designator = recom.compile_designator
490 AND items.inventory_item_id = recom.inventory_item_id
491 AND items.planning_make_buy_code = decode(
492 NVL(mp.use_new_planner,SYS_NO),
493 SYS_NO, MAKE, items.planning_make_buy_code)
494 AND labor.organization_id = recom.organization_id
495 AND labor.compile_designator = recom.compile_designator
496 AND labor.using_assembly_item_id = recom.inventory_item_id
497 AND recom.organization_id = decode(mp.use_new_planner,
498 1, nvl(recom.source_organization_id,
499 recom.organization_id),
500 recom.organization_id)
501 AND recom.disposition_status_type <> CANCEL_ORDER
502 AND recom.order_type = REPETITVE_SCHEDULE
503 AND recom.organization_id IN
504 (select planned_organization
505 from mrp_plan_organizations_v
506 where organization_id = arg_org_id
507 and compile_designator = arg_compile_desig)
508 AND recom.compile_designator = mp.compile_designator
509 AND mp.organization_id = arg_org_id
510 AND mp.compile_designator = arg_compile_desig;
511
512 var_row_count := SQL%ROWCOUNT;
513 mrp_print_pk.stop_watch(arg_request_id,
514 var_watch_id,
515 var_row_count);
516 COMMIT;
517 END plan_repetitive;
518
519 END; -- crp_planner_pk