[Home] [Help]
PACKAGE BODY: APPS.MSC_SELECT_ALL_FOR_RELEASE_PUB
Source
1 PACKAGE BODY MSC_SELECT_ALL_FOR_RELEASE_PUB AS
2 /* $Header: MSCSARPB.pls 120.22 2012/03/01 06:31:21 skathera ship $ */
3 TYPE numtab is table of Number index by binary_integer;
4 TYPE char240_tab is table of varchar2(240) index by binary_integer;
5 g_instance_id numtab;
6 g_job_prefix char240_tab;
7
8 FUNCTION get_implement_as(p_order_type number,
9 p_org_id number,
10 p_source_org_id number,
11 p_supplier_id number,
12 p_planning_make_buy_code number,
13 p_build_in_wip_flag number,
14 p_purchasing_enabled_flag number,
15 p_sr_instance_id number,
16 p_plan_type number,
17 p_source_table varchar,
18 p_dest_org_id number,
19 p_dest_inst_id number) return number IS
20 p_impl_as number;
21
22 cursor c_modeled_supplier is
23 select modeled_supplier_id
24 from msc_trading_partners
25 where sr_tp_id = p_dest_org_id
26 and sr_instance_id= p_dest_inst_id
27 and partner_type= 3;
28
29 v_modeled_supplier_id number;
30
31
32 BEGIN
33 p_impl_as := 1; -- none
34 if p_order_type = 5 then
35 if p_org_id = p_source_org_id then
36 if p_build_in_wip_flag = 1 then
37 p_impl_as := 3; -- discrete job
38 end if;
39
40 elsif p_org_id <> p_source_org_id then
41 p_impl_as := 2; -- purchase req
42
43 elsif p_supplier_id is not null then
44 if p_purchasing_enabled_flag =1 then
45 p_impl_as := 2; -- purchase req
46 end if;
47 elsif p_supplier_id is null and p_source_org_id is null then
48 if p_planning_make_buy_code = 1 then
49 if p_build_in_wip_flag = 1 then
50 p_impl_as := 3; -- discrete job
51 end if;
52 else -- if p_planning_make_buy_code = 2 then
53 if p_purchasing_enabled_flag = 1 then
54 p_impl_as := 2; -- purchase req
55 end if;
56 end if;
57 end if; --if p_org_id = p_source_org_id then
58 elsif p_order_type = 13 then
59 p_impl_as := 4;
60 elsif p_order_type = 51 then
61 p_impl_as := 5;
62 elsif p_order_type = 76 then
63 --pabram.srp.changes added 76,77,78
64 p_impl_as := 2;
65 elsif p_order_type = 77 then
66 p_impl_as := 5;
67 elsif p_order_type = 78 then
68 p_impl_as := 2;
69 elsif (p_order_type = 53 and p_plan_type = 5 and
70 p_source_table ='MSC_DEMANDS') then
71 -- Planned Outbound Shipment in DRP plan
72 if p_supplier_id is not null then
73 p_impl_as := 2;
74 elsif p_dest_inst_id <> p_sr_instance_id then
75 p_impl_as := 2;
76 elsif p_dest_org_id is not null then
77 -- check if supplier modeled as org
78 open c_modeled_supplier;
79 fetch c_modeled_supplier INTO v_modeled_supplier_id;
80 close c_modeled_supplier;
81 IF v_modeled_supplier_id is not null THEN
82 -- supplier modeled as org
83 -- Default to Purchase requisition
84 p_impl_as := 2;
85 ELSE
86 -- Default to Internal requisition
87 p_impl_as := 5;
88 END IF;
89 end if;
90 end if; -- if p_order_type = 5 then
91
92 return p_impl_as;
93
94 END get_implement_as;
95
96 function get_alternate_rtg (p_plan_id in number,
97 p_sr_instance_id number,
98 p_proc_seq_id number) return varchar2 is
99 alt_rtg varchar2(40) ; --5338566 bugfix, length changed to 40
100 begin
101 select alternate_routing_designator
102 into alt_rtg
103 from msc_routings b, msc_process_effectivity p
104 where p.plan_id = b.plan_id
105 and p.sr_instance_id= b.sr_instance_id
106 and p.routing_sequence_id = b.routing_sequence_id
107 and p.process_sequence_id = p_proc_seq_id
108 and p.plan_id = p_plan_id
109 and p.sr_instance_id = p_sr_instance_id
110 ;
111 return (alt_rtg);
112 exception when no_data_found then
113 return null;
114
115 end get_alternate_rtg ;
116
117 function get_alternate_bom (p_plan_id in number,
118 p_sr_instance_id number,
119 p_proc_seq_id number) return varchar2 is
120 alt_bom varchar2(40) ; --5338566 bugfix, length changed to 40
121 begin
122
123 select alternate_bom_designator
124 into alt_bom
125 from msc_boms b, msc_process_effectivity p
126 where p.plan_id = b.plan_id
127 and p.sr_instance_id= p.sr_instance_id
128 and p.bill_sequence_id = b.bill_sequence_id
129 and p.process_sequence_id = p_proc_seq_id
130 and p.plan_id = p_plan_id
131 and p.sr_instance_id = p_sr_instance_id
132 ;
133 return (alt_bom);
134 exception when no_data_found then
135 return null;
136
137 end get_alternate_bom ;
138
139 --Bug3273575 create a new function to get job prefix profile from Source.
140 function get_wip_job_prefix(p_sr_instance_id in number)
141 return varchar2 is
142 l_wip_job_prefix VARCHAR2(240) := NULL;
143 begin
144 for i in 1..g_instance_id.COUNT loop
145 IF g_instance_id(i) = p_sr_instance_id then
146 return(g_job_prefix(i));
147 END IF;
148 end loop;
149 return (null);
150 exception
151 when others then
152 return null;
153 end get_wip_job_prefix;
154
155 PROCEDURE Update_Implement_Attrib(p_where_clause IN VARCHAR2,
156 p_employee_id IN NUMBER,
157 p_demand_class IN VARCHAR2,
158 p_def_job_class IN VARCHAR2,
159 p_def_firm_jobs IN VARCHAR2,
160 p_include_so IN VARCHAR2,
161 p_total_rows OUT NOCOPY NUMBER,
162 p_succ_rows OUT NOCOPY NUMBER,
163 p_error_rows OUT NOCOPY NUMBER,
164 p_current_plan_type IN NUMBER DEFAULT NULL,
165 x_plan_id IN NUMBER DEFAULT NULL
166 ) IS
167
168 p_sql_stmt VARCHAR2(32767);
169 p_drp_stmt VARCHAR2(32767);
170
171 TYPE SelCurTyp IS REF CURSOR;
172 SelCur SelCurTyp;
173 p_rel_error varchar2(30000);
174
175 p_plan_id_str varchar2(100) := ' ';
176 p_plan_id number;
177 p_bom_item_type number;
178 p_release_time_fence_code number;
179 p_in_source_plan number;
180 p_action varchar2(250);
181 p_cfm_routing_flag number;
182 p_effectivity_control number;
183 p_unit_number number;
184 p_order_type number;
185 p_project_id number;
186 p_impl_as number;
187 p_org_id number;
188 p_source_org_id number;
189 p_supplier_id number;
190 p_planning_make_buy_code number;
191 p_build_in_wip_flag number;
192 p_purchasing_enabled_flag number;
193 p_loc_id number;
194 p_empl_id number;
195 p_item_id number;
196 p_due_date date;
197 p_planner_code varchar2(20);
198 p_task_id number;
199 p_transaction_id number;
200 p_inst_id number;
201 p_lots_exist number;
202 p_new_order_qty number;
203
204 p_def_job_status number;
205 p_wip_class_code varchar2(300);
206 p_mesg varchar2(80);
207 p_plan_type number := p_current_plan_type;
208 p_source_table varchar(20);
209 p_dest_inst_id number;
210 p_dest_org_id number;
211
212 cursor plan_type_c is
213 select curr_plan_type
214 from msc_plans
215 where plan_id = p_plan_id;
216
217 PROCEDURE reset_record IS
218 BEGIN
219 p_loc_id := null;
220 p_empl_id := null;
221 p_rel_error := null;
222 p_cfm_routing_flag := null;
223 p_effectivity_control := null;
224 p_unit_number := null;
225 p_project_id := null;
226 p_task_id := null;
227 p_source_org_id := null;
228 p_supplier_id := null;
229 p_planner_code := null;
230 p_impl_as := null;
231 p_lots_exist := null;
232
233 END reset_record;
234
235 PROCEDURE verify_release_error IS
236
237 p_valid number;
238 p_rel_schd_OK varchar2(3);
239
240 CURSOR empl_C IS
241 SELECT mp.employee_id
242 FROM msc_planners mp
243 WHERE mp.planner_code = p_planner_code
244 AND mp.organization_id = p_org_id
245 AND mp.sr_instance_id = p_inst_id
246 AND mp.current_employee_flag = 1;
247
248 CURSOR loc_C IS
249 select mtps.sr_tp_site_id
250 from msc_trading_partners mtp,
251 msc_trading_partner_sites mtps
252 where mtp.sr_tp_id = p_org_id
253 AND mtp.sr_instance_id = p_inst_id
254 AND mtp.partner_type =3
255 AND mtps.partner_id = mtp.partner_id;
256
257 CURSOR validate_repair_module IS
258 select i.repair_module
259 from msc_item_attributes i,
260 msc_plans p
261 where i.inventory_item_id = p_item_id
262 and i.sr_instance_id = p_inst_id
263 and i.simulation_set_id = p.item_simulation_set_id
264 and p.sr_instance_id = i.sr_instance_id
265 and p.plan_id = p_plan_id;
266
267 l_repair_module number;
268
269 BEGIN
270 if p_bom_item_type in (1, 2, 3, 5) then
271 -- Models/Option Classes cannot be released
272 p_rel_error := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_MODEL');
273 end if;
274
275 if p_order_type = 79 AND p_plan_type = 1 THEN
276 open validate_repair_module;
277 fetch validate_repair_module into l_repair_module;
278 close validate_repair_module;
279
280 if l_repair_module is null then
281 p_rel_error := FND_MESSAGE.GET_STRING('MSC', 'MSC_RELEASE');
282 end if;
283 end if;
284
285
286 if p_release_time_fence_code = 6 then
287 -- Kanban Items Cannot be Released.
288 p_rel_error := p_rel_error ||' '||
289 FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_KANBAN');
290 end if;
291
292 if p_in_source_plan = 1 then
293 p_rel_schd_OK :=
294 nvl(fnd_profile.value('MSC_DRP_RELEASE_FROM_MRP'),'N');
295 -- Record was generated as part of some other plan/schedule.
296 if (p_lots_exist = 2 and -- can not release supply schedule
297 p_new_order_qty <> 0) or -- which is generated by plan
298 (p_lots_exist = 1 and -- demand schedule
299 p_rel_schd_OK = 'N') or
300 (p_lots_exist = 2 and -- manually created planned order
301 p_new_order_qty =0 and -- for supply schedule
302 p_rel_schd_OK = 'N') then
303
304 p_rel_error := p_rel_error ||' '||
305 FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_IN_SOURCE_PLAN');
306 end if;
307 end if; -- if p_in_source_plan = 1 then
308
309 if p_action = msc_get_name.lookup_meaning('MRP_ACTIONS',6) then -- None
310 if (p_plan_type = 8 and p_order_type = 51) then
311 null;
312 else
313 p_rel_error := p_rel_error ||' '||
314 FND_MESSAGE.GET_STRING('MSC', 'MSC_REL_ACTION_NONE');
315 end if;
316 end if;
317
318
319 if p_cfm_routing_flag = 1 and p_order_type = 5 then
320 p_rel_error := p_rel_error ||' '||
321 FND_MESSAGE.GET_STRING('MSC', 'MSC_NO_FLOW_SCHEDULE');
322 end if;
323
324 if p_effectivity_control=2 and
325 p_unit_number is null and
326 p_order_type = 5 then
327 p_rel_error := p_rel_error || ' '||
328 FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_UNIT_NUMBER');
329 end if;
330
331 if p_project_id is not null then
332 p_valid := msc_rel_wf.is_pjm_valid(p_org_id,
333 p_project_id,
334 p_task_id,
335 p_due_date,
336 null,
337 p_inst_id);
338 if p_valid = 0 then
339 p_rel_error := p_rel_error || ' '||
340 FND_MESSAGE.GET_STRING('MSC', 'MSC_PJM_VALIDATION1');
341
342 end if;
343 end if; -- if p_project_id is not null then
344
345 p_valid := null;
346 if nvl(FND_PROFILE.VALUE('MSC_REL_ONLY_ONHAND_SUPPLY'),'N') = 'Y' and
347 p_order_type = 5 and p_org_id = p_source_org_id then
348
349 -- 4417550, make planned order can be released only when
350 -- child supply are on hand or make planned order
351
352 p_valid := MSC_SELECT_ALL_FOR_RELEASE_PUB.child_supplies_onhand(
353 p_plan_id, p_transaction_id);
354 if p_valid > 0 then
355 p_rel_error := p_rel_error ||' '||
356 FND_MESSAGE.GET_STRING('MSC', 'MSC_REL_ONLY_ONHAND_WARN2');
357 end if;
358
359 end if; -- if nvl(FND_PROFILE.VALUE('MSC_REL_ONLY_ONHAND_SUPPLY'),'N') = 'Y'
360
361 if p_rel_error is null then
362 if p_order_type in (5,13,51,76,77,78,53) then
363 --pabram.srp.changes added 76,77,78
364
365 p_impl_as := msc_select_all_for_release_pub.get_implement_as(
366 p_order_type,
367 p_org_id,
368 p_source_org_id,
369 p_supplier_id,
370 p_planning_make_buy_code,
371 p_build_in_wip_flag,
372 p_purchasing_enabled_flag,
373 p_inst_id ,
374 p_plan_type ,
375 p_source_table ,
376 p_dest_org_id ,
377 p_dest_inst_id );
378 end if;
379
380 if p_impl_as in (2,5) or p_order_type = 2 then
381 -- update impl_location_id
382 OPEN loc_C;
383 FETCH loc_C into p_loc_id;
384 CLOSE loc_c;
385 /*
386 if p_loc_id is null then
387 p_rel_error := p_rel_error ||' '||
388 FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_LOCATION');
389 end if;
390 */
391 end if; -- if p_impl_as in (2,5) or p_order_type = 2 then
392
393 if p_impl_as in (2,5) then
394 /* update employee_id to be the employee_id for the corresponding
395 planner_code in msc_system_items */
396 OPEN empl_C;
397 FETCH empl_C INTO p_empl_id;
398 CLOSE empl_C;
399
400 if p_empl_id is null then
401 p_rel_error := p_rel_error ||' '||
402 FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_EMPLOYEE');
403 end if;
404 end if; -- if p_impl_as in (2,5) then
405
406 end if; -- if p_rel_error is null then
407
408 END verify_release_error;
409
410 PROCEDURE update_success_supplies IS
411 CURSOR sr_item IS
412 SELECT msi.sr_inventory_item_id
413 FROM msc_system_items msi
414 WHERE msi.plan_id=p_plan_id
415 AND msi.organization_id = p_org_id
416 AND msi.sr_instance_id = p_inst_id
417 and msi.inventory_item_id = p_item_id;
418
419 p_sr_item_id number;
420 p_job_name varchar2(3000);
421 p_load_type number;
422 p_item_wip_class varchar2(300);
423
424 BEGIN
425
426 IF p_impl_as = 3 then -- discrete job
427 if p_wip_class_code is null then
428 if nvl(p_cfm_routing_flag,0) <> 3 then
429 OPEN sr_item;
430 FETCH sr_item INTO p_sr_item_id;
431 CLOSE sr_item;
432 p_item_wip_class :=
433 msc_rel_wf.get_acc_class_from_source(
434 p_org_id,
435 p_sr_item_id,
436 p_project_id,
437 p_inst_id);
438 end if; -- if p_cfm_routing_flag <> 3
439 end if; -- if p_wip_class_code is null then
440
441 p_job_name := get_wip_job_prefix(p_inst_id)||
442 msc_rel_wf.get_job_seq_from_source(p_inst_id);
443
444 update msc_supplies mr
445 set implement_wip_class_code =
446 nvl(p_wip_class_code, p_item_wip_class),
447 implement_status_code =
448 nvl(implement_status_code, p_def_job_status),
449 implement_demand_class =
450 nvl(mr.implement_demand_class, p_demand_class),
451 implement_job_name =
452 nvl(mr.implement_job_name, p_job_name),
453 implement_firm = nvl(mr.implement_firm,
454 DECODE(p_def_firm_jobs, 'Y', 1, mr.firm_planned_type)),
455 implement_alternate_routing = nvl(implement_alternate_routing,
456 get_alternate_rtg(mr.plan_id,mr.sr_instance_id,mr.process_seq_id)),
457 implement_alternate_bom = nvl(implement_alternate_bom,
458 get_alternate_bom(mr.plan_id,mr.sr_instance_id,mr.process_seq_id))
459 where transaction_id = p_transaction_id
460 and plan_id = p_plan_id;
461
462 END IF; -- IF p_impl_as = 3 then
463
464 p_load_type := msc_get_name.load_type(
465 p_plan_type,
466 p_plan_id,
467 'MSC_SUPPLIES',
468 p_transaction_id,
469 p_org_id,
470 p_inst_id,
471 p_order_type,
472 p_impl_as,
473 p_source_org_id,
474 p_inst_id,
475 nvl(p_cfm_routing_flag,0),
476 p_item_id,
477 null);
478
479 IF p_order_type in (1, 2, 3, 5, 51, 53,76,77,78) THEN
480 --pabram.srp.changes added 76,77,78
481 update msc_supplies mr
482 SET implement_date = nvl(mr.implement_date,
483 decode(trunc(GREATEST(NVL(mr.firm_date,mr.new_schedule_date),
484 TRUNC(SYSDATE))),
485 trunc(mr.new_schedule_date),
486 mr.new_schedule_date,
487 msc_calendar.next_work_day(
488 mr.organization_id,
489 mr.sr_instance_id,
490 1,
491 greatest(nvl( mr.firm_date, mr.new_schedule_date ),
492 trunc(sysdate))
493 )
494 )
495 ),
496 implement_quantity = nvl(implement_quantity,
497 DECODE(mr.disposition_status_type, 2,
498 decode(mr.order_type,3,
499 decode(nvl(mr.implemented_quantity,0),0,
500 mr.new_order_quantity, mr.implemented_quantity),
501 0),
502 GREATEST(NVL(mr.firm_quantity, mr.new_order_quantity)
503 - NVL(mr.quantity_in_process, 0)
504 - NVL(mr.implemented_quantity, 0), 0))),
505 release_status = 1,
506 implement_as = p_impl_as,
507 release_errors = null,
508 implement_employee_id = p_empl_id,
509 implement_location_id = p_loc_id,
510 implement_supplier_id = nvl(mr.implement_supplier_id,
511 DECODE(p_impl_as,
512 2, nvl(mr.implement_supplier_id, mr.source_supplier_id),
513 mr.implement_supplier_id)),
514 implement_supplier_site_id = DECODE(p_impl_as, 2,
515 nvl(mr.implement_supplier_site_id, mr.source_supplier_site_id),
516 mr.implement_supplier_site_id),
517 implement_source_org_id = DECODE(p_impl_as, 2,
518 DECODE(mr.source_organization_id, mr.organization_id, NULL,
519 mr.source_organization_id),
520 5, nvl(mr.source_organization_id, mr.organization_id), NULL),
521 implement_sr_instance_id = DECODE(p_impl_as, 2,
522 DECODE(mr.source_sr_instance_id, mr.sr_instance_id, NULL,
523 mr.source_sr_instance_id),
524 5, nvl(mr.source_sr_instance_id, mr.sr_instance_id), NULL),
525 reschedule_flag = DECODE(mr.order_type, 5, 2, 51, 2, 53, 2, 1),
526 implement_unit_number = decode(p_effectivity_control,2,
527 nvl(implement_unit_number,unit_number), null),
528 load_type = p_load_type,
529 status = 0,
530 applied = 2,
531 last_updated_by = fnd_global.user_id,
532 implement_status_code =
533 -- bug 4410222, For cancelled discrete jobs, set implement_status_code to 7
534 decode(order_type, 3,
535 decode(disposition_status_type,2, 7,
536 implement_status_code),
537 implement_status_code),
538 implement_dock_date = nvl(implement_dock_date,
539 decode(order_type, 2, new_dock_date ,1, new_dock_date ,3, new_dock_date ,
540 53, new_dock_date ,
541 implement_dock_date))
542 where transaction_id = p_transaction_id
543 and plan_id = p_plan_id;
544
545 if p_order_type in (5,51,53, 76,77,78) then
546 --pabram.srp.changes added 76,77,78
547 update msc_supplies mr
548 set quantity_in_process =
549 DECODE(mr.number1,
550 -9999, mr.quantity_in_process,
551 GREATEST(0,
552 NVL(mr.quantity_in_process, 0) +
553 NVL(mr.implement_quantity, 0) -
554 NVL(mr.number1,0))),
555 number1 = DECODE(mr.order_type,
556 5, mr.implement_quantity,
557 51, mr.implement_quantity,
558 mr.number1),
559 implement_project_id =
560 nvl(mr.implement_project_id,mr.project_id),
561 implement_task_id =
562 nvl(mr.implement_task_id,mr.task_id),
563 implement_ship_date = nvl(implement_ship_date,
564 decode(p_load_type, 32, new_ship_date, -- internal req
565 256, new_ship_date, -- internal repair
566 implement_ship_date)),
567 implement_dock_date = nvl(implement_dock_date,
568 decode(p_load_type, 32, new_dock_date ,
569 256, new_ship_date, -- internal repair
570 implement_dock_date)),
571 implement_firm = nvl(implement_firm,
572 decode(p_load_type, 32, firm_planned_type,
573 256, firm_planned_type, -- internal repair
574 implement_firm))
575 where transaction_id = p_transaction_id
576 and plan_id = p_plan_id;
577 end if; -- if p_order_type in (5,51) then
578 ELSE -- p_order_type = 13
579 update msc_supplies msrs
580 SET implement_date = nvl(msrs.implement_date,
581 msrs.last_unit_completion_date),
582 implement_daily_rate = nvl(msrs.implement_daily_rate, msrs.daily_rate),
583 implement_quantity = nvl(msrs.implement_daily_rate, msrs.daily_rate),
584 implement_demand_class = nvl(msrs.implement_demand_class,
585 p_demand_class),
586 implement_line_id = nvl(msrs.implement_line_id, msrs.line_id),
587 implement_processing_days = nvl(msrs.implement_processing_days,
588 msc_calendar.days_between(msrs.organization_id,
589 msrs.sr_instance_id,
590 1,
591 msrs.last_unit_completion_date,
592 nvl(msrs.first_unit_completion_date,
593 msrs.new_schedule_date)
594 ) +1),
595 load_type = p_load_type,
596 release_errors = null,
597 release_status = 1,
598 implement_as =4,
599 status = 0,
600 applied = 2,
601 last_updated_by = fnd_global.user_id
602 where transaction_id = p_transaction_id
603 and plan_id = p_plan_id;
604
605 END IF; --IF p_order_type in (1, 2, 3, 5, 51, 53)
606
607 END update_success_supplies;
608
609 Procedure update_sup_rel_error IS
610 BEGIN
611 update msc_supplies
612 SET implement_as = NULL,
613 implement_quantity = NULL,
614 implement_date = NULL,
615 release_status = 2,
616 release_errors = p_rel_error
617 where transaction_id = p_transaction_id
618 and plan_id = p_plan_id;
619 END update_sup_rel_error;
620
621 Procedure update_dmd_rel_error IS
622 BEGIN
623 update msc_demands
624 set release_errors = p_rel_error,
625 implement_org_id = null,
626 implement_instance_id = null,
627 implement_date = NULL,
628 implement_ship_date = NULL,
629 implement_arrival_date = NULL,
630 implement_earliest_date = NULL,
631 implement_firm = NULL,
632 reschedule_flag = NULL,
633 load_type = null,
634 release_status = 2
635 where plan_id = p_plan_id
636 and demand_id = p_transaction_id
637 and sr_instance_id = p_inst_id;
638
639 END update_dmd_rel_error;
640
641 PROCEDURE update_dmd_success IS
642 p_impl_date date;
643 v_ship_date date;
644 v_arrival_date date;
645 v_earliest_date date;
646 BEGIN
647 msc_rel_wf.update_so_dates(p_plan_id, p_transaction_id, p_inst_id,
648 p_impl_date, v_ship_date, v_arrival_date,
649 v_earliest_date);
650 update msc_demands
651 set implement_org_id = organization_id,
652 implement_instance_id = sr_instance_id,
653 implement_date = nvl(implement_date,planned_ship_date),
654 implement_ship_date = v_ship_date,
655 implement_arrival_date = v_arrival_date,
656 implement_earliest_date = v_earliest_date,
657 implement_firm = nvl(implement_firm, org_firm_flag),
658 load_type = 30,
659 reschedule_flag = 1,
660 release_status = 1,
661 status = 0,
662 applied =2,
663 last_updated_by = fnd_global.user_id,
664 release_errors = NULL
665 where plan_id = p_plan_id
666 and demand_id = p_transaction_id
667 and sr_instance_id = p_inst_id;
668 END update_dmd_success;
669
670 BEGIN -- main procedure
671
672 --9488060 bug fix
673 if x_plan_id is not null then
674 p_plan_id_str := ' and plan_id = '||x_plan_id||' ';
675 end if;
676
677 -- front port bug 3466661
678 IF g_instance_id.COUNT = 0 THEN
679 SELECT instance_id,null
680 BULK COLLECT INTO g_instance_id,g_job_prefix
681 FROM msc_apps_instances;
682
683 FOR i in 1..g_instance_id.COUNT LOOP
684 BEGIN
685 msc_rel_wf.get_profile_value(
686 p_profile_name => 'WIP_JOB_PREFIX',
687 p_instance_id => g_instance_id(i),
688 p_calling_source => 'PACKAGE',
689 p_profile_value => g_job_prefix(i));
690 EXCEPTION
691 WHEN OTHERS THEN
692 g_job_prefix(i) := null;
693 END;
694 END LOOP;
695 END IF; -- IF g_instance_id.COUNT = 0 THEN
696
697 p_error_rows := 0;
698 p_succ_rows := 0;
699 p_total_rows := 0;
700
701
702 p_sql_stmt :=
703 'select ' ||
704 'plan_id, ' ||
705 'transaction_id, ' ||
706 'action, ' ||
707 'cfm_routing_flag, ' ||
708 'bom_item_type, ' ||
709 'release_time_fence_code, ' ||
710 'in_source_plan, ' ||
711 'inventory_item_id, ' ||
712 'build_in_wip_flag, ' ||
713 'order_type, ' ||
714 'source_organization_id, ' ||
715 'organization_id, ' ||
716 'purchasing_enabled_flag, ' ||
717 'source_vendor_id, ' ||
718 'planning_make_buy_code, ' ||
719 'build_in_wip_flag, '||
720 -- 'effectivity_control, ' ||
721 'planner_code, ' ||
722 'sr_instance_id, ' ||
723 'new_due_date, ' ||
724 'project_id, ' ||
725 'task_id, ' ||
726 'unit_number, ' ||
727 'lots_exist, '||
728 'quantity_rate , '||
729 ' source_table ,'||
730 ' null, '|| -- dest_inst_id in DRP plan
731 ' null ' || -- dest_org_id in DRP plan
732 ' from '||msc_get_name.get_order_view(p_plan_type, p_plan_id) ||
733 ' where ' || p_where_clause ||
734 ' and order_type IN (1, 2, 3, 5, 13, 51, 53, 76,77,78)'||
735 ' and source_table = ''MSC_SUPPLIES''' ||
736 ' and nvl(release_time_fence_code,-1) <> 7 ' ||
737 p_plan_id_str ||
738 -- Shikyu items should not be processed
739 ' and nvl(release_status,2) = 2';
740 --pabram.srp.changes added 76,77,78
741
742 p_drp_stmt :=
743 'select ' ||
744 'plan_id, ' ||
745 'transaction_id, ' ||
746 'action, ' ||
747 'cfm_routing_flag, ' ||
748 'bom_item_type, ' ||
749 'release_time_fence_code, ' ||
750 'in_source_plan, ' ||
751 'inventory_item_id, ' ||
752 'build_in_wip_flag, ' ||
753 'order_type, ' ||
754 'source_organization_id, ' ||
755 'organization_id, ' ||
756 'purchasing_enabled_flag, ' ||
757 'source_vendor_id, ' ||
758 'planning_make_buy_code, ' ||
759 'build_in_wip_flag, '||
760 'planner_code, ' ||
761 'sr_instance_id, ' ||
762 'new_due_date, ' ||
763 'project_id, ' ||
764 'task_id, ' ||
765 'unit_number, ' ||
766 'lots_exist, '||
767 'quantity_rate , '||
768 ' source_table, ' ||
769 ' dest_inst_id ,' ||
770 ' dest_org_id '||
771 ' from MSC_ORDERS_DRP_V'||
772 ' where ' || p_where_clause ||
773 ' and ( (order_type IN (1, 2, 3, 5, 13, 51, 53, 76,77,78)'||
774 ' and source_table = ''MSC_SUPPLIES'' ) ' ||
775 ' OR (order_type = 53 and source_table = ''MSC_DEMANDS'') ) ' ||
776 ' and nvl(release_time_fence_code,-1) <> 7 ' ||
777 p_plan_id_str ||
778 ' and nvl(release_status,2) = 2';
779
780 IF p_current_plan_type = 5 THEN -- DRP
781 OPEN selCur FOR p_drp_stmt;
782 ELSE -- for all other types of plans
783 OPEN selCur FOR p_sql_stmt;
784 END IF;
785
786 LOOP
787 FETCH selCur INTO p_plan_id,p_transaction_id,p_action,
788 p_cfm_routing_flag, p_bom_item_type,
789 p_release_time_fence_code, p_in_source_plan,
790 p_item_id, p_build_in_wip_flag, p_order_type,
791 p_source_org_id, p_org_id, p_purchasing_enabled_flag,
792 p_supplier_id,p_planning_make_buy_code,
793 p_build_in_wip_flag, -- p_effectivity_control,
794 p_planner_code, p_inst_id, p_due_date, p_project_id,
795 p_task_id, p_unit_number, p_lots_exist,
796 p_new_order_qty, p_source_table, p_dest_inst_id,
797 p_dest_org_id;
798 begin
799 select effectivity_control
800 into p_effectivity_control
801 from msc_system_items
802 where plan_id = p_plan_id
803 and sr_instance_id = p_inst_id
804 and organization_id = p_org_id
805 and inventory_item_id = p_item_id;
806 exception
807 when others then
808 null;
809 end;
810 EXIT WHEN selCur%NOTFOUND;
811 if p_plan_type is null then
812 open Plan_type_c;
813 fetch plan_type_c into p_plan_type;
814 close plan_type_c;
815 end if;
816 if p_def_job_status is null then
817 p_def_job_status:= msc_get_name.GET_preference(
818 'ORDERS_DEFAULT_JOB_STATUS',
819 msc_get_name.get_default_pref_id(fnd_global.user_id),
820 p_plan_type);
821 p_def_job_status := nvl(p_def_job_status, 1);
822 p_wip_class_code := msc_get_name.GET_preference(
823 'ORDERS_JOB_CLASS_CODE',
824 msc_get_name.get_default_pref_id(fnd_global.user_id),
825 p_plan_type);
826 end if;
827
828 verify_release_error;
829 if p_rel_error is not null then
830 update_sup_rel_error;
831 p_error_rows := p_error_rows +1;
832 else -- if p_rel_error is null then
833 update_success_supplies;
834 p_succ_rows := p_succ_rows +1;
835 end if; --if p_rel_error is not null then
836 p_total_rows := p_total_rows+1;
837 reset_record;
838 END LOOP;
839 CLOSE selCur;
840
841 if p_include_so = 'Y' then
842 p_sql_stmt :=
843 'SELECT ' ||
844 'plan_id, ' ||
845 'transaction_id, ' ||
846 'sr_instance_id ' ||
847 'from msc_orders_v mo' ||
848 ' where ' || p_where_clause ||
849 ' and nvl(release_status,2) = 2' ||
850 ' and order_type = 30'||
851 ' and source_table = ''MSC_DEMANDS'''||
852 ' and exists (select 1 from msc_exception_details med ' ||
853 ' where med.plan_id = mo.plan_id ' ||
854 ' and med.exception_type = 70 ' ||
855 ' and med.organization_id = mo.organization_id '||
856 ' and med.sr_instance_id = mo.sr_instance_id '||
857 ' and med.inventory_item_id = mo.inventory_item_id '||
858 ' and med.number1 = mo.transaction_id) ';
859
860 OPEN selCur FOR p_sql_stmt;
861 LOOP
862 FETCH selCur INTO p_plan_id,p_transaction_id, p_inst_id;
863 EXIT WHEN selCur%NOTFOUND;
864 p_mesg := null;
865 p_mesg :=
866 msc_rel_wf.verify_so_release(p_plan_id, p_transaction_id, p_inst_id);
867 if p_mesg is not null then
868 p_rel_error := FND_MESSAGE.GET_STRING('MSC',p_mesg);
869 update_dmd_rel_error;
870 p_error_rows := p_error_rows +1;
871 else -- if l_mesg is null then
872 p_succ_rows := p_succ_rows +1;
873 update_dmd_success;
874 end if; -- if l_mesg is not null then
875 p_total_rows := p_total_rows+1;
876 p_rel_error := null;
877 END LOOP;
878 CLOSE selCur;
879
880 end if; -- if p_include_so = 'Y' then
881
882 END Update_Implement_Attrib;
883
884 FUNCTION child_supplies_onhand(p_plan_id number,
885 p_transaction_id number) return number IS
886 CURSOR child_supply_c is
887 SELECT 1
888 from msc_full_pegging mfp1,
889 msc_full_pegging mfp2,
890 msc_supplies ms
891 where mfp1.plan_id = p_plan_id
892 and mfp1.transaction_id = p_transaction_id
893 and mfp2.plan_id = mfp1.plan_id
894 and mfp2.prev_pegging_id = mfp1.pegging_id
895 and ms.plan_id = mfp2.plan_id
896 and ms.transaction_id = mfp2.transaction_id
897 and ms.sr_instance_id = mfp2.sr_instance_id
898 and (ms.order_type in (1,2,8,11,12) or -- purchased/transferred supply
899 (ms.order_type = 5 and -- not make planned order
900 nvl(ms.source_organization_id,-1) <> ms.organization_id));
901 v_temp number;
902 BEGIN
903 v_temp :=0;
904 OPEN child_supply_c;
905 FETCH child_supply_c INTO v_temp;
906 CLOSE child_supply_c;
907
908 return v_temp;
909 END child_supplies_onhand;
910
911 END MSC_SELECT_ALL_FOR_RELEASE_PUB;