[Home] [Help]
PACKAGE BODY: APPS.MRP_REL_PLAN_PUB
Source
1 PACKAGE BODY MRP_Rel_Plan_PUB AS
2 /* $Header: MRPPRELB.pls 120.5 2006/09/20 13:11:09 rgurugub noship $ */
3
4 -- Start of Comments
5 -- API name MRP_Release_Plan_SC
6 -- Type Public
7 -- Procedure
8 --
9 -- Pre-reqs
10 --
11 -- Parameters
12 --
13 -- Version Current version = 1.0
14 -- Initial version = 1.0
15 --
16 -- Notes
17 --
18 -- OVERVIEW:
19 -- This procedure populates the WIP and purchasing interface tables with
20 -- rows for creating and rescheduling jobs, purchase orders, and repetitive
21 -- schedules
22 --
23 -- ARGUMENTS:
24 -- arg_org_id: The current organization id
25 -- arg_compile_desig: The current plan name
26 -- arg_user_id: The user
27 -- arg_po_group_by: How to group attributes together for po mass load
28 -- arg_wip_group_id: How to group records in wip
29 -- var_launch_process: Which process to launch
30 -- var_calendar_code: Calendar code for current organization
31 -- var_exception_set_id: Exception set id for current organization
32 --
33 -- RETURNS: Nothing
34 --
35 -- Modified by APATANKA. Bug # 371223. A multi-org plan in an org in which
36 -- it is not defined would generate orders in all orgs.
37 --
38 -- Fix includes adding one more parameter -- parameter.org_id which is the
39 -- log in org and using it when it is not same as mrp_plans.organization_id.
40 --
41 -- 8/13/96: Changed the name back to MRP_RELEASE_PLAN_SC
42 --
43 -- 8/26/96: Changing the code.
44 --
45
46 PROCEDURE MRP_RELEASE_PLAN_SC
47 ( arg_log_org_id IN NUMBER
48 , arg_org_id IN NUMBER
49 , arg_compile_desig IN VARCHAR2
50 , arg_user_id IN NUMBER
51 , arg_po_group_by IN NUMBER
52 , arg_po_batch_number IN NUMBER
53 , arg_wip_group_id IN NUMBER
54 , arg_loaded_jobs IN OUT NOCOPY NUMBER
55 , arg_loaded_reqs IN OUT NOCOPY NUMBER
56 , arg_loaded_scheds IN OUT NOCOPY NUMBER
57 , arg_resched_jobs IN OUT NOCOPY NUMBER
58 , arg_resched_reqs IN OUT NOCOPY NUMBER
59 , arg_wip_req_id IN OUT NOCOPY NUMBER
60 , arg_req_load_id IN OUT NOCOPY NUMBER
61 , arg_req_resched_id IN OUT NOCOPY NUMBER
62 , arg_mode IN VARCHAR2
63 , arg_transaction_id IN NUMBER
64 ) IS
65
66 VERSION CONSTANT CHAR(80) :=
67 '$Header: MRPPRELB.pls 120.5 2006/09/20 13:11:09 rgurugub noship $';
68 REQ_GRP_ALL_ON_ONE CONSTANT INTEGER := 1; -- PO group by
69 REQ_GRP_ITEM CONSTANT INTEGER := 2;
70 REQ_GRP_BUYER CONSTANT INTEGER := 3;
71 REQ_GRP_PLANNER CONSTANT INTEGER := 4;
72 REQ_GRP_VENDOR CONSTANT INTEGER := 5;
73 REQ_GRP_ONE_EACH CONSTANT INTEGER := 6;
74 REQ_GRP_CATEGORY CONSTANT INTEGER := 7;
75
76 WIP_DIS_MASS_LOAD CONSTANT INTEGER := 1;
77 WIP_REP_MASS_LOAD CONSTANT INTEGER := 2;
78 WIP_DIS_MASS_RESCHEDULE CONSTANT INTEGER := 4;
79 PO_MASS_LOAD CONSTANT INTEGER := 8;
80 PO_MASS_RESCHEDULE CONSTANT INTEGER := 16;
81
82 PURCHASE_ORDER CONSTANT INTEGER := 1; -- order type lookup
83 PURCH_REQ CONSTANT INTEGER := 2;
84 WORK_ORDER CONSTANT INTEGER := 3;
85 REPETITVE_SCHEDULE CONSTANT INTEGER := 4;
86 PLANNED_ORDER CONSTANT INTEGER := 5;
87 MATERIAL_TRANSFER CONSTANT INTEGER := 6;
88 NONSTD_JOB CONSTANT INTEGER := 7;
89 RECEIPT_PURCH_ORDER CONSTANT INTEGER := 8;
90 REQUIREMENT CONSTANT INTEGER := 9;
91 FPO_SUPPLY CONSTANT INTEGER := 10;
92
93 NOT_UNDER_REV_CONTROL CONSTANT INTEGER := 1;
94 UNDER_REV_CONTROL CONSTANT INTEGER := 2;
95
96 JOB_CANCELLED CONSTANT INTEGER := 7;
97
98 PURCHASING_BY_REV CONSTANT INTEGER := 1;
99 NOT_PURCHASING_BY_REV CONSTANT INTEGER := 2;
100
101 var_launch_process INTEGER;
102 var_handle VARCHAR2(200);
103 var_output NUMBER;
104 var_purchasing_by_rev NUMBER;
105 var_error_stmt VARCHAR2(2000) := NULL;
106
107 -- parameters used in 'Perform PO reschedule' block
108 l_return_code boolean;
109 l_old_need_by_date date;
110 l_new_need_by_date date;
111 l_po_header_id number;
112 l_po_line_id number;
113 l_po_number varchar2(60);
114 lv_result BOOLEAN;
115 var_upd_req_date_rel varchar2(1);
116 var_demand_class VARCHAR(30);
117 BEGIN
118
119 -- if mode is NULL then it means that this procedure is called from PWB
120 -- where we need to do batch processing
121 -- If mode is WF, then we need to do this work only for the
122 -- transaction_id that is passed in
123
124 dbms_lock.allocate_unique(arg_compile_desig||to_char(arg_org_id),
125 var_handle);
126
127 var_output := dbms_lock.request(var_handle, 6, 32767, TRUE);
128 var_upd_req_date_rel := NVL(FND_PROFILE.VALUE('MRP_UPD_REQ_DATE_REL'),'N');
129 if(var_output <> 0) then
130 FND_MESSAGE.SET_NAME('MRP', 'GEN-LOCK-WARNING');
131 FND_MESSAGE.SET_TOKEN('EVENT', 'RELEASE PLANNED ORDERS');
132
133 var_error_stmt := FND_MESSAGE.GET;
134
135 raise_application_error(-20000, var_error_stmt);
136 end if;
137
138 SELECT sched.demand_class
139 INTO var_demand_class
140 FROM mrp_plans mp,
141 mrp_schedule_designators sched
142 WHERE sched.organization_id (+)= mp.organization_id
143 AND sched.schedule_designator (+)= mp.compile_designator
144 AND mp.organization_id = arg_org_id
145 AND mp.compile_designator = arg_compile_desig;
146
147 -- ------------------------------------------------------------------------
148 -- Perform the wip discrete job mass load
149 -- ------------------------------------------------------------------------
150 INSERT INTO wip_job_schedule_interface
151 (last_update_date,
152 last_updated_by,
153 last_update_login,
154 creation_date,
155 created_by,
156 group_id,
157 source_code,
158 source_line_id,
159 organization_id,
160 load_type,
161 status_type,
162 last_unit_completion_date,
163 bom_revision_date,
164 routing_revision_date,
165 primary_item_id,
166 class_code,
167 job_name,
168 firm_planned_flag,
169 start_quantity,
170 net_quantity,
171 demand_class,
172 project_id,
173 task_id,
174 schedule_group_id,
175 build_sequence,
176 line_id,
177 alternate_bom_designator,
178 alternate_routing_designator,
179 end_item_unit_number,
180 process_phase,
181 process_status)
182 SELECT SYSDATE,
183 arg_user_id,
184 mr.last_update_login,
185 SYSDATE,
186 arg_user_id,
187 arg_wip_group_id,
188 'MRP',
189 mr.transaction_id,
190 msi.organization_id,
191 1,
192 mr.implement_status_code,
193 mr.implement_date,
194 NULL,
195 NULL,
196 mr.inventory_item_id,
197 mr.implement_wip_class_code,
198 mr.implement_job_name,
199 mr.implement_firm,
200 mr.implement_quantity,
201 mr.implement_quantity,
202 nvl(mr.implement_demand_class,var_demand_class),
203 mr.implement_project_id,
204 mr.implement_task_id,
205 mr.implement_schedule_group_id,
206 mr.implement_build_sequence,
207 mr.implement_line_id,
208 mr.implement_alternate_bom,
209 mr.implement_alternate_routing,
210 mr.implement_end_item_unit_number,
211 2,
212 1
213 FROM mtl_parameters param,
214 mrp_system_items msi,
215 mrp_recommendations mr,
216 mrp_plan_organizations_v orgs
217 WHERE param.organization_id = msi.organization_id
218 AND msi.inventory_item_id = mr.inventory_item_id
219 AND msi.compile_designator = mr.compile_designator
220 AND msi.organization_id = mr.organization_id
221 AND mr.release_errors is NULL
222 AND mr.implement_quantity > 0
223 AND mr.organization_id = orgs.planned_organization
224 AND mr.compile_designator = orgs.compile_designator
225 AND orgs.compile_designator = arg_compile_desig
226 AND orgs.organization_id = arg_org_id
227 AND orgs.planned_organization = decode(arg_log_org_id,
228 arg_org_id, orgs.planned_organization,
229 arg_log_org_id)
230 /** Bug 2190961
231 AND ((arg_mode is null and mr.load_type = WIP_DIS_MASS_LOAD) or
232 (arg_mode = 'WF' and mr.transaction_id = arg_transaction_id));
233 **/
234 AND arg_mode is null
235 AND mr.load_type = WIP_DIS_MASS_LOAD;
236
237 IF SQL%ROWCOUNT > 0
238 THEN
239 arg_loaded_jobs := SQL%ROWCOUNT;
240 ELSE
241 arg_loaded_jobs := 0;
242 END IF;
243
244 -- ------------------------------------------------------------------------
245 -- Perform the wip discrete job mass reschedule
246 -- ------------------------------------------------------------------------
247 INSERT INTO wip_job_schedule_interface
248 (last_update_date,
249 last_updated_by,
250 last_update_login,
251 creation_date,
252 created_by,
253 group_id,
254 organization_id,
255 status_type,
256 load_type,
257 last_unit_completion_date,
258 bom_revision_date,
259 routing_revision_date,
260 job_name,
261 firm_planned_flag,
262 -- net_quantity,
263 start_quantity,
264 wip_entity_id,
265 demand_class,
266 project_id,
267 task_id,
268 schedule_group_id,
269 build_sequence,
270 line_id,
271 alternate_bom_designator,
272 alternate_routing_designator,
273 end_item_unit_number,
274 process_phase,
275 process_status,
276 due_date)
277 SELECT SYSDATE,
278 arg_user_id,
279 mr.last_update_login,
280 SYSDATE,
281 arg_user_id,
282 arg_wip_group_id,
283 msi.organization_id,
284 DECODE(NVL(mr.implement_status_code, w.status_code),
285 JOB_CANCELLED,JOB_CANCELLED,NULL), -- 2667045
286 3,
287 mr.implement_date,
288 NULL,
289 NULL,
290 mr.implement_job_name,
291 mr.implement_firm,
292 -- mr.implement_quantity,
293 DECODE(NVL(mr.implement_status_code, w.status_code),
294 JOB_CANCELLED,TO_NUMBER(NULL),
295 DECODE(w.job_quantity, mr.implement_quantity,
296 TO_NUMBER(NULL),
297 ((w.job_quantity + NVL(w.quantity_completed, 0) +
298 NVL(w.quantity_scrapped, 0)) -
299 (w.job_quantity - mr.implement_quantity)))),
300 mr.disposition_id,
301 nvl(mr.implement_demand_class,var_demand_class),
302 mr.implement_project_id,
303 mr.implement_task_id,
304 mr.implement_schedule_group_id,
305 mr.implement_build_sequence,
306 mr.implement_line_id,
307 mr.implement_alternate_bom,
308 mr.implement_alternate_routing,
309 mr.implement_end_item_unit_number,
310 2,
311 1,
312 Decode(var_upd_req_date_rel,'Y',mr.implement_date,NULL)
313 FROM mtl_parameters param,
314 mrp_item_wip_entities w,
315 mrp_system_items msi,
316 mrp_recommendations mr,
317 mrp_plan_organizations_v orgs
318 WHERE param.organization_id = msi.organization_id
319 AND msi.inventory_item_id = mr.inventory_item_id
320 AND msi.compile_designator = mr.compile_designator
321 AND msi.organization_id = mr.organization_id
322 AND w.compile_designator = mr.compile_designator
323 AND w.organization_id = mr.organization_id
324 AND w.inventory_item_id = mr.inventory_item_id
325 AND w.wip_entity_id = mr.disposition_id
326 AND mr.release_errors is NULL
327 AND mr.organization_id = orgs.planned_organization
328 AND mr.compile_designator = orgs.compile_designator
329 AND orgs.organization_id = arg_org_id
330 AND orgs.compile_designator = arg_compile_desig
331 AND orgs.planned_organization = decode(arg_log_org_id,
332 arg_org_id, orgs.planned_organization,
333 arg_log_org_id)
334 AND ((arg_mode is null and mr.load_type = WIP_DIS_MASS_RESCHEDULE) or
335 (arg_mode = 'WF' and mr.transaction_id = arg_transaction_id));
336
337
338 IF SQL%ROWCOUNT > 0 THEN
339 arg_resched_jobs := SQL%ROWCOUNT;
340 ELSE
341 arg_resched_jobs := 0;
342 END IF;
343 -- ------------------------------------------------------------------------
344 -- Perform the wip repetitive schedule mass load
345 -- ------------------------------------------------------------------------
346 INSERT INTO wip_job_schedule_interface
347 (last_update_date,
348 last_updated_by,
349 last_update_login,
350 creation_date,
351 created_by,
352 group_id,
353 source_code,
354 source_line_id,
355 organization_id,
356 load_type,
357 last_unit_completion_date,
358 bom_revision_date,
359 routing_revision_date,
360 processing_work_days,
361 daily_production_rate,
362 line_id,
363 primary_item_id,
364 firm_planned_flag,
365 demand_class,
366 process_phase,
367 process_status)
368 SELECT SYSDATE,
369 arg_user_id,
370 msrs.last_update_login,
371 SYSDATE,
372 arg_user_id,
373 arg_wip_group_id,
374 'MRP',
375 msrs.transaction_id,
376 msi.organization_id,
377 2,
378 msrs.implement_date,
379 NULL,
380 NULL,
381 msrs.implement_processing_days,
382 msrs.implement_daily_rate,
383 msrs.implement_line_id,
384 msrs.inventory_item_id,
385 msrs.implement_firm,
386 nvl(msrs.implement_demand_class,var_demand_class),
387 2,
388 1
389 FROM mtl_parameters param,
390 mrp_system_items msi,
391 mrp_sugg_rep_schedules msrs,
392 mrp_plan_organizations_v orgs
393 WHERE param.organization_id = msi.organization_id
394 AND msi.compile_designator = msrs.compile_designator
395 AND msi.organization_id = msrs.organization_id
396 AND msi.inventory_item_id = msrs.inventory_item_id
397 AND msrs.release_errors is NULL
398 AND msrs.implement_daily_rate > 0
399 AND msrs.organization_id = orgs.planned_organization
400 AND msrs.compile_designator = orgs.compile_designator
401 AND orgs.organization_id = arg_org_id
402 AND orgs.compile_designator = arg_compile_desig
403 AND orgs.planned_organization = decode(arg_log_org_id,
404 arg_org_id, orgs.planned_organization,
405 arg_log_org_id)
406 AND ((arg_mode is null and msrs.load_type = WIP_REP_MASS_LOAD) or
407 (arg_mode = 'WF' and msrs.transaction_id = arg_transaction_id));
408
409 IF SQL%ROWCOUNT > 0 THEN
410 arg_loaded_scheds := SQL%ROWCOUNT;
411 ELSE
412 arg_loaded_scheds := 0;
413 END IF;
414
415
416 -- ------------------------------------------------------------------------
417 -- Perform the po mass load
418 -- ------------------------------------------------------------------------
419 -- Check if the profile MRP_PURCHASING_BY_REVISION is set
420 var_purchasing_by_rev :=
421 FND_PROFILE.VALUE('MRP_PURCHASING_BY_REVISION');
422
423
424 -- NOTE: We always pass 'VENDOR' as the group by parameter to the req
425 -- import program. PO will only look at this parameter if it has failed
426 -- to find a value in the group code.
427
428 /* 1284534 - SVAIDYAN: Insert vendor_site_code only if implement_vendor_id
429 is not null. Otherwise, this will insert vendor site code for Internal
430 Req. also.
431 */
432
433 INSERT INTO po_requisitions_interface_all
434 (/*line_type_id, Amount or Quantity based */
435 last_updated_by,
436 last_update_date,
437 last_update_login,
438 creation_date,
439 created_by,
440 item_id,
441 quantity,
442 need_by_date,
443 interface_source_code,
444 deliver_to_location_id,
445 deliver_to_requestor_id,
446 destination_type_code,
447 preparer_id,
448 source_type_code,
449 authorization_status,
450 uom_code,
451 batch_id,
452 charge_account_id,
453 group_code,
454 item_revision,
455 destination_organization_id,
456 autosource_flag,
457 org_id,
458 source_organization_id,
459 suggested_vendor_id,
460 suggested_vendor_site_id,
461 suggested_vendor_site,
462 project_id,
463 task_id,
464 end_item_unit_number,
465 project_accounting_context)
466 SELECT /*+ INDEX(MSI MRP_SYSTEM_ITEMS_U1)*/ /*2448571*/
467 /* 1, Quantity based */
468 mr.last_updated_by,
469 SYSDATE,
470 mr.last_update_login,
471 SYSDATE,
472 mr.created_by,
473 mr.inventory_item_id,
474 mr.implement_quantity,
475 /* cal2.calendar_date, */
476 get_dock_date(arg_compile_desig,
477 orgs.organization_id,/*2448572*/
478 mp.calendar_exception_set_id,
479 mp.calendar_code,
480 mr.implement_date,
481 nvl(mr.implement_vendor_id, mr.source_vendor_id),
482 nvl(mr.implement_vendor_site_id, mr.source_vendor_site_id),
483 msi.inventory_item_id,
484 NVL(msi.postprocessing_lead_time, 0)),
485 'MRP',
486 mr.implement_location_id,
487 mr.implement_employee_id,
488 'INVENTORY',
489 mr.implement_employee_id,
490 DECODE(mr.implement_vendor_id,
491 NULL, DECODE(mr.implement_source_org_id,
492 NULL,NULL,
493 'INVENTORY')
494 ,'VENDOR'), -- PO wants us to pass null now -- spob
495 'APPROVED',
496 msi.uom_code, --mr.implement_uom_code,
497 arg_po_batch_number,
498 nvl(ccga.material_account, decode(mti.inventory_asset_flag,
499 'Y', mp.material_account,
500 nvl(mti.expense_account, mp.expense_account))),
501 decode(arg_po_group_by,
502 REQ_GRP_ALL_ON_ONE, 'ALL-ON-ONE',
503 REQ_GRP_ITEM, to_char(mr.inventory_item_id),
504 REQ_GRP_BUYER, nvl(to_char(msi.buyer_id),NULL),
505 REQ_GRP_PLANNER, nvl(msi.planner_code,'PLANNER'),
506 REQ_GRP_VENDOR, NULL,
507 REQ_GRP_ONE_EACH,
508 to_char(po_requisitions_interface_s.nextval),
509 REQ_GRP_CATEGORY,
510 nvl(to_char(msi.category_id),NULL),
511 NULL),
512 DECODE(var_purchasing_by_rev, NULL,
513 DECODE(mti.REVISION_QTY_CONTROL_CODE,
514 NOT_UNDER_REV_CONTROL, NULL, msi.revision),
515 PURCHASING_BY_REV, msi.revision,
516 NOT_PURCHASING_BY_REV, NULL),
517 mr.organization_id,
518 'P',
519 ood.operating_unit,
520 mr.implement_source_org_id,
521 nvl(mr.implement_vendor_id,
522 mr.source_vendor_id),
523 nvl(mr.implement_vendor_site_id,
524 mr.source_vendor_site_id),
525 decode(mr.implement_vendor_id, NULL, NULL, pos.vendor_site_code),
526 mr.implement_project_id,
527 mr.implement_task_id,
528 mr.implement_end_item_unit_number,
529 DECODE(mr.implement_project_id, NULL,
530 'N', 'Y')
531 FROM po_vendor_sites_all pos,
532 cst_cost_group_accounts ccga,
533 mrp_project_parameters mpp,
534 org_organization_definitions ood,
535 mtl_parameters mp,
536 mtl_system_items mti,
537 mrp_system_items msi,
538 mrp_recommendations mr,
539 mrp_plan_organizations_v orgs
540 WHERE ccga.cost_group_id (+)= nvl(mpp.costing_group_id, -23453)
541 AND ccga.organization_id(+)= mpp.organization_id
542 AND mpp.organization_id (+)= mr.organization_id
543 AND mpp.project_id (+)= nvl(mr.implement_project_id, -23453)
544 AND pos.vendor_id(+) = nvl(mr.implement_vendor_id,mr.source_vendor_id)
545 AND pos.vendor_site_id(+) = nvl(mr.implement_vendor_site_id,mr.source_vendor_site_id)
546 AND ood.organization_id = msi.organization_id
547 AND mp.organization_id = msi.organization_id
548 AND mti.inventory_item_id = msi.inventory_item_id
549 AND mti.organization_id = msi.organization_id
550 AND msi.inventory_item_id = mr.inventory_item_id
551 AND msi.compile_designator = mr.compile_designator
552 AND msi.organization_id = mr.organization_id
553 AND mr.release_errors is NULL
554 AND mr.implement_quantity > 0
555 AND mr.organization_id = orgs.planned_organization
556 AND mr.compile_designator = orgs.compile_designator
557 AND orgs.organization_id = arg_org_id
558 AND orgs.compile_designator = arg_compile_desig
559 AND orgs.planned_organization = decode(arg_log_org_id,
560 arg_org_id, orgs.planned_organization,
561 arg_log_org_id)
562 /*** bug 2190961
563 AND ((arg_mode is null and mr.load_type = PO_MASS_LOAD) or
564 (arg_mode = 'WF' and mr.transaction_id = arg_transaction_id));
565 **/
566 AND arg_mode is null and mr.load_type = PO_MASS_LOAD;
567
568
569
570 IF SQL%ROWCOUNT > 0 THEN
571 arg_loaded_reqs := SQL%ROWCOUNT;
572 ELSE
573 arg_loaded_reqs := 0;
574 END IF;
575 -- ------------------------------------------------------------------------
576 -- Perform the po mass reschedule
577 -- ------------------------------------------------------------------------
578
579 INSERT INTO po_reschedule_interface
580 (quantity,
581 need_by_date,
582 line_id,
583 last_update_date,
584 last_updated_by,
585 creation_date,
586 created_by)
587 SELECT mr.implement_quantity,
588 /* cal2.calendar_date, */
589 get_dock_date(arg_compile_desig,
590 orgs.organization_id,/*2448572*/
591 mp.calendar_exception_set_id,
592 mp.calendar_code,
593 mr.implement_date,
594 nvl(mr.implement_vendor_id, mr.source_vendor_id),
595 nvl(mr.implement_vendor_site_id, mr.source_vendor_site_id),
596 msi.inventory_item_id,
597 NVL(msi.postprocessing_lead_time, 0)),
598 mipo.line_id,
599 SYSDATE,
600 arg_user_id,
601 SYSDATE,
602 arg_user_id
603 FROM
604 mtl_parameters mp,
605 mrp_item_purchase_orders mipo,
606 mrp_system_items msi,
607 mrp_recommendations mr,
608 mrp_plan_organizations_v orgs
609 WHERE mipo.transaction_id = mr.disposition_id
610 AND mipo.line_id IS NOT NULL
611 AND mipo.compile_designator = mr.compile_designator
612 AND mipo.organization_id = mr.organization_id
613 AND mipo.inventory_item_id = mr.inventory_item_id
614 AND mp.organization_id = msi.organization_id
615 AND msi.inventory_item_id = mr.inventory_item_id
616 AND msi.compile_designator = mr.compile_designator
617 AND msi.organization_id = mr.organization_id
618 AND mr.release_errors is NULL
619 AND mr.organization_id = orgs.planned_organization
620 AND mr.compile_designator = orgs.compile_designator
621 AND mr.order_type = PURCH_REQ
622 AND orgs.organization_id = arg_org_id
623 AND orgs.compile_designator = arg_compile_desig
624 AND orgs.planned_organization = decode(arg_log_org_id,
625 arg_org_id, orgs.planned_organization,
626 arg_log_org_id)
627 AND ((arg_mode is null and mr.load_type = PO_MASS_RESCHEDULE) or
628 (arg_mode = 'WF' and mr.transaction_id = arg_transaction_id));
629
630 IF SQL%ROWCOUNT > 0 THEN
631 arg_resched_reqs := SQL%ROWCOUNT;
632 ELSE
633 arg_resched_reqs := 0;
634 END IF;
635
636 -- ------------------------------------------------------------------------
637 -- Perform PO reschedule
638 -- ------------------------------------------------------------------------
639
640 IF (arg_mode = 'WF') THEN
641
642 BEGIN
643
644 SELECT mr.old_schedule_date,
645 /* cal2.calendar_date, */
646 get_dock_date(arg_compile_desig,
647 orgs.organization_id,/*2448572*/
648 mp.calendar_exception_set_id,
649 mp.calendar_code,
650 mr.implement_date,
651 nvl(mr.implement_vendor_id, mr.source_vendor_id),
652 nvl(mr.implement_vendor_site_id, mr.source_vendor_site_id),
653 msi.inventory_item_id,
654 NVL(msi.postprocessing_lead_time, 0)) ,
655 mipo.purchase_order_id,
656 mipo.line_id,
657 mipo.po_number
658 INTO l_old_need_by_date,
659 l_new_need_by_date,
660 l_po_header_id,
661 l_po_line_id,
662 l_po_number
663 FROM
664 mtl_parameters mp,
665 mrp_item_purchase_orders mipo,
666 mrp_system_items msi,
667 mrp_recommendations mr,
668 mrp_plan_organizations_v orgs
669 WHERE mipo.transaction_id = mr.disposition_id
670 AND mipo.line_id IS NOT NULL
671 AND mipo.compile_designator = mr.compile_designator
672 AND mipo.organization_id = mr.organization_id
673 AND mipo.inventory_item_id = mr.inventory_item_id
674 AND mp.organization_id = msi.organization_id
675 AND msi.inventory_item_id = mr.inventory_item_id
676 AND msi.compile_designator = mr.compile_designator
677 AND msi.organization_id = mr.organization_id
678 AND mr.release_errors is NULL
679 AND mr.order_type = PURCHASE_ORDER
680 AND mr.organization_id = orgs.planned_organization
681 AND mr.compile_designator = orgs.compile_designator
682 AND orgs.organization_id = arg_org_id
683 AND orgs.compile_designator = arg_compile_desig
684 AND orgs.planned_organization = decode(arg_log_org_id,
685 arg_org_id, orgs.planned_organization,
686 arg_log_org_id)
687 AND mr.transaction_id = arg_transaction_id;
688
689 l_return_code := po_reschedule_pkg.reschedule(l_old_need_by_date,
690 l_new_need_by_date,
691 l_po_header_id,
692 l_po_line_id,
693 l_po_number);
694
695 EXCEPTION
696
697 WHEN NO_DATA_FOUND THEN
698 null;
699
700 END;
701
702 END IF;
703
704 IF ((arg_loaded_jobs > 0) OR
705 (arg_resched_jobs > 0) OR
706 (arg_loaded_scheds >0)) THEN
707 arg_wip_req_id := NULL;
708 arg_wip_req_id := FND_REQUEST.SUBMIT_REQUEST(
709 'WIP', -- application
710 'WICMLP', -- program
711 NULL, -- description
712 NULL, -- start_time
713 FALSE, -- sub_request
714 arg_wip_group_id, -- group_id
715 1, -- validation_level
716 1); -- print report
717 END IF;
718
719 IF arg_loaded_reqs > 0 THEN
720 DECLARE po_group_by_name VARCHAR2(10);
721 BEGIN
722 IF arg_po_group_by = 1 THEN
723 po_group_by_name := 'ALL';
724 ELSIF arg_po_group_by = 2 THEN
725 po_group_by_name := 'ITEM';
726 ELSIF arg_po_group_by = 3 THEN
727 po_group_by_name := 'BUYER';
728 ELSIF arg_po_group_by = 4 THEN
729 po_group_by_name := 'PLANNER';
730 ELSIF arg_po_group_by = 5 THEN
731 po_group_by_name := 'VENDOR';
732 ELSIF arg_po_group_by = 6 THEN
733 po_group_by_name := 'ONE-EACH';
734 ELSIF arg_po_group_by = 7 THEN
735 po_group_by_name := 'CATEGORY';
736 END IF;
737
738 -- Launching the REQIMPORT in loop for each OU, change for MOAC
739
740 DECLARE
741
742 CURSOR c1 IS
743 SELECT
744 DISTINCT org_id
745 FROM PO_REQUISITIONS_INTERFACE_ALL
746 WHERE
747 batch_id = arg_po_batch_number;
748 BEGIN
749
750 FOR C2 IN C1
751 LOOP
752
753 MO_GLOBAL.INIT ('PO');
754 FND_REQUEST.SET_ORG_ID (c2.org_id);
755
756 -- set to trigger mode to bypass the 'SAVEPOINT'
757 -- and 'ROLLBACK' command.
758 lv_result := FND_REQUEST.SET_MODE(TRUE);
759
760
761 arg_req_load_id := NULL;
762
763 arg_req_load_id :=
764 FND_REQUEST.SUBMIT_REQUEST(
765 'PO', -- application
766 'REQIMPORT',-- program
767 NULL, -- description
768 NULL, -- start_time
769 FALSE, -- sub_request
770 'MRP',
771 arg_po_batch_number,
772 po_group_by_name,
773 0);
774
775 END LOOP;
776 END;
777
778 END;
779 END IF;
780
781 IF arg_resched_reqs > 0 THEN
782 DECLARE
783 CURSOR c1 IS
784 SELECT DISTINCT prla.org_id
785 FROM PO_RESCHEDULE_INTERFACE PRI, PO_REQUISITION_LINES_ALL PRLA
786 WHERE pri.line_id = prla.requisition_line_id;
787
788 BEGIN
789 FOR C2 IN C1
790 LOOP
791
792 MO_GLOBAL.INIT ('PO');
793 FND_REQUEST.SET_ORG_ID (c2.org_id);
794 -- set to trigger mode to bypass the 'SAVEPOINT' and 'ROLLBACK' command.
795 lv_result := FND_REQUEST.SET_MODE(TRUE);
796
797 arg_req_resched_id := NULL;
798 arg_req_resched_id := FND_REQUEST.SUBMIT_REQUEST(
799 'PO', -- application
800 'POXRSR', -- program
801 NULL, -- description
802 NULL, -- start_time
803 FALSE); -- sub_request
804 END LOOP;
805 END;
806
807 END IF;
808
809 IF (arg_loaded_jobs > 0 OR arg_loaded_reqs > 0 OR
810 arg_resched_jobs > 0 OR arg_resched_reqs > 0)
811 THEN
812 UPDATE
813 mrp_recommendations
814 SET implement_demand_class = NULL,
815 implement_date = NULL,
816 implement_quantity = NULL,
817 implement_firm = NULL,
818 implement_wip_class_code = NULL,
819 implement_job_name = NULL,
820 implement_status_code = NULL,
821 implement_location_id = NULL,
822 implement_source_org_id = NULL,
823 implement_vendor_id = NULL,
824 implement_vendor_site_id = NULL,
825 implement_project_id = NULL,
826 implement_task_id = NULL,
827 release_status = NULL,
828 number1 = NULL,
829 load_type = NULL,
830 implement_as = NULL,
831 implement_end_item_unit_number = NULL,
832 implement_schedule_group_id = NULL,
833 implement_build_sequence = NULL,
834 implement_line_id = NULL,
835 implement_alternate_bom = NULL,
836 implement_alternate_routing = NULL
837 WHERE organization_id IN
838 (select planned_organization
839 from mrp_plan_organizations_v
840 where organization_id = arg_org_id
841 and compile_designator = arg_compile_desig
842 AND planned_organization = decode(arg_log_org_id,
843 arg_org_id, planned_organization,
844 arg_log_org_id))
845 AND compile_designator = arg_compile_desig
846 AND release_errors IS NULL
847 AND load_type in (1,2,3,4,5,8,16); /*2448572*/
848
849 END IF;
850
851 IF arg_loaded_scheds > 0
852 THEN
853 UPDATE mrp_sugg_rep_schedules
854 SET implement_demand_class = NULL,
855 implement_date = NULL,
856 implement_daily_rate = NULL,
857 implement_firm = NULL,
858 implement_processing_days = NULL,
859 implement_wip_class_code = NULL,
860 implement_line_id = NULL,
861 release_status = NULL,
862 load_type = NULL,
863 status = 3 -- bug2797945
864 WHERE organization_id IN
865 (select planned_organization
866 from mrp_plan_organizations_v
867 where organization_id = arg_org_id
868 and compile_designator = arg_compile_desig
869 AND planned_organization = decode(arg_log_org_id,
870 arg_org_id, planned_organization,
871 arg_log_org_id))
872 AND compile_designator = arg_compile_desig
873 AND load_type = WIP_REP_MASS_LOAD
874 AND release_errors IS NULL;
875 END IF;
876
877 COMMIT WORK;
878
879
880 END MRP_Release_Plan_Sc;
881
882 /** Bug1519701 : Added this function to derive the NEED_BY_DATE in
883 the PO Interface table by first applying the default calendar and
884 the Item Post Processing Lead Time to the Implement date
885 and then applying to it the delivery calendar if one exists. The
886 dock date is ignored */
887 FUNCTION GET_DOCK_DATE
888 ( arg_compile_desig IN VARCHAR2
889 , arg_plan_owning_org IN NUMBER /*2448572*/
890 , arg_calendar_exception_set_id IN NUMBER
891 , arg_calendar_code IN VARCHAR2
892 , arg_implement_date IN DATE
893 , arg_vendor_id IN NUMBER
894 , arg_vendor_site_id IN NUMBER
895 , arg_item_id IN NUMBER
896 , arg_lead_time IN NUMBER
897 ) RETURN DATE IS
898
899 source_date date;
900 dock_date date;
901
902 BEGIN
903
904 SELECT cal2.calendar_date
905 INTO source_date
906 FROM bom_calendar_dates cal1,
907 bom_calendar_dates cal2
908 WHERE cal1.calendar_code = arg_calendar_code
909 AND cal1.exception_set_id = arg_calendar_exception_set_id
910 AND cal1.calendar_date = arg_implement_date
911 AND cal2.calendar_code = cal1.calendar_code
912 AND cal2.exception_set_id = cal1.exception_set_id
913 AND cal2.seq_num = GREATEST(1,NVL(cal1.seq_num, cal1.prior_seq_num) -
914 NVL(arg_lead_time, 0));
915
916 BEGIN
917 SELECT cal2.calendar_date
918 INTO dock_date
919 FROM bom_calendar_dates cal1,
920 bom_calendar_dates cal2,
921 mrp_item_suppliers mis
922 WHERE mis.organization_id = arg_plan_owning_org /*2448572*/
923 AND mis.compile_designator = arg_compile_desig
924 AND mis.supplier_id = arg_vendor_id
925 AND mis.supplier_site_id = arg_vendor_site_id
926 AND mis.inventory_item_id = arg_item_id
927 AND mis.using_organization_id = -1 /* Global ASL */
928 AND cal1.calendar_code = mis.delivery_calendar_code
929 AND cal1.exception_set_id = arg_calendar_exception_set_id
930 AND cal1.calendar_date = source_date
931 AND cal2.calendar_code = cal1.calendar_code
932 AND cal2.exception_set_id = cal1.exception_set_id
933 AND cal2.seq_num = GREATEST(1,NVL(cal1.seq_num, cal1.prior_seq_num) );
934 EXCEPTION WHEN NO_DATA_FOUND THEN /* No Delivery Calendar */
935 dock_date := source_date;
936 END;
937
938 RETURN(dock_date);
939
940 END get_dock_date;
941
942 END MRP_Rel_Plan_PUB;