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