1 PACKAGE BODY MSC_RP_RELEASE_PUB AS
2 -- $Header: MSCRPRLB.pls 120.8 2010/03/16 23:37:54 hulu noship $
3 PROCEDURE do_release(pid IN Number,psid in number,
4 p_user_id in number,
5 p_resp_id in number,
6 p_appl_id in number) IS
7
8 loaded_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
9 loaded_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
10 loaded_scheds MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
11 resched_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
12 resched_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
13 wip_group_id NUMBER;
14 wip_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
15 po_req_load_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
16 po_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
17 release_instance MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
18 loaded_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
19 resched_lot_jobs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
20 osfm_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
21 eam_resched_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
22 eam_req_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
23 loaded_int_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
24 resched_int_reqs MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
25 int_req_load_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
26 int_req_resched_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
27 loaded_int_repair_orders MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
28 int_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
29 loaded_ext_repair_orders MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
30 ext_repair_orders_id MSC_Rel_Plan_PUB.NumTblTyp := MSC_Rel_Plan_PUB.NumTblTyp(0);
31 p_po_res_id msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
32 p_released_inst msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
33 p_po_res_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
34 p_po_pwb_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
35 p_so_rel_id msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
36 p_so_released_inst msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
37 p_so_rel_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
38 p_so_pwb_count msc_rel_wf.numTblTyp:=msc_rel_wf.numTblTyp(0);
39
40
41 user_id NUMBER :=nvl(p_user_id,1);
42 req_load_group_by NUMBER;
43 req_batch_number NUMBER;
44
45
46 i NUMBER;
47 v_res_po_count number :=0;
48 v_rel_so_count number :=0;
49 v_other_count number :=0;
50 collection_status NUMBER := 0;
51
52
53 request_id number;
54
55
56 p_doc_num NUMBER := 0;
57 p_doc_line_num NUMBER := 0;
58 p_doc_shipment_num NUMBER := 0;
59 x_return_status varchar2(1) := 'E';
60 tmpindex NUMBER := 0;
61 p_ret_msg varchar2(200) := null;
62 p_release_by_user varchar2(3):=null ;
63 -- nvl(FND_PROFILE.value('MSC_RELEASED_BY_USER_ONLY'),'N');
64
65
66 p_plan_id number := pid;
67 p_plan_org_id number;
68 p_plan_inst_id number;
69
70 p_org_id number ;
71 p_inst_id number;
72 p_compile_designator varchar2(20);
73
74 l_sr_instance_id number;
75
76 cursor cur_plan_info is
77 select organization_id,sr_instance_id,compile_designator
78 from msc_plans
79 where plan_id=p_plan_id;
80
81 CURSOR cur_supply_order IS
82 SELECT sum(decode(ms.load_type,20,0,1)),
83 sum(decode(ms.load_type,20,1,0)),
84 sum(decode(mai.lrtype, 'I', 0,
85 decode(nvl(mai.st_status,0),3,1,0)))
86 FROM msc_supplies ms,
87 msc_apps_instances mai
88 where ms.plan_id = pid
89 and ms.release_errors is null
90 and ms.load_type is not null
91 and ms.sr_instance_id = mai.instance_id
92 and ms.last_updated_by = decode(p_release_by_user,'Y', user_id,
93 ms.last_updated_by)
94 and ms.status = 0
95 and ms.applied =2;
96
97 CURSOR cur_demand_order IS
98 -- SELECT 1
99 -- FROM msc_demands md
100 -- where md.plan_id = p_plan_id
101 -- and md.release_errors is null
102 -- and md.load_type =30
103 -- and MD.ORIGINATION_TYPE = 30
104 -- and md.last_updated_by = decode(p_release_by_user,'Y', user_id,
105 -- md.last_updated_by)
106 -- and md.status = 0
107 -- and md.applied =2;
108
109 select 1 from dual
110 where exists ( SELECT /*+ first_rows */ 1
111 FROM msc_demands md
112 where md.plan_id = p_plan_id
113 and md.release_errors is null
114 and md.load_type =30
115 and MD.ORIGINATION_TYPE = 30
116 and md.last_updated_by = decode(p_release_by_user,'Y', user_id,
117 md.last_updated_by)
118 and md.status = 0
119 and md.applied =2
120 );
121
122 CURSOR cur_instance IS
123 SELECT unique sr_instance_id
124 from MSC_PLAN_ORGANIZATIONS
125 where plan_id = p_plan_id;
126
127
128 BEGIN
129 -- before we have SSO enable, we temp hardcode the user id
130 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
131
132
133 -- msc_rel_wf.init_db('OPERATIONS');
134 -- get plan info
135 -----------------------------------------------------
136 open cur_plan_info;
137 fetch cur_plan_info into p_plan_org_id,p_plan_inst_id ,p_compile_designator;
138 if cur_plan_info%notfound then
139 return;
140
141 end if;
142 close cur_plan_info;
143
144
145
146 p_release_by_user :=
147 nvl(GET_RP_PLAN_PROFILE_VALUE(p_plan_id,'MSC_RELEASED_BY_USER_ONLY'),'N');
148
149 req_load_group_by := to_number(GET_RP_PLAN_PROFILE_VALUE(p_plan_id,'MRP_LOAD_REQ_GROUP_BY'));
150 if (req_load_group_by is null) then
151 msc_rel_wf.get_profile_value(p_profile_name => 'MRP_LOAD_REQ_GROUP_BY',
152 p_instance_id => p_inst_id,
153 p_profile_value => req_load_group_by);
154
155 end if;
156 -- print_debug('done getting profile MRP_LOAD_REQ_GROUP_BY='|| req_load_group_by );
157 req_load_group_by :=nvl(req_load_group_by,1);
158 OPEN cur_supply_order;
159 FETCH cur_supply_order INTO
160 v_other_count,
161 v_res_po_count,
162 collection_status;
163 CLOSE cur_supply_order;
164
165 OPEN cur_demand_order;
166 FETCH cur_demand_order INTO v_rel_so_count;
167 CLOSE cur_demand_order;
168
169 v_rel_so_count := nvl(v_rel_so_count,0); --- rescheduled sales order
170 v_other_count := nvl(v_other_count,0); -- planned order, work order, schedule, po requsition
171 v_res_po_count := nvl(v_res_po_count,0); -- rescheduled/cancel po
172
173 -- print_debug('so=' || v_rel_so_count || ' other=' || v_other_count || ' po=' || v_res_po_count);
174
175 --- we should not release supplies while collection is running
176 --- we can check this before we call web service
177 --- move this part of code out
178
179
180
181 -- release planned order, work order,purchasing requision,schedule
182 -- MSC_Rel_Plan_PUB.msc_release_plan_sc needs to be updated to report
183 -- cp request id and progress
184
185 -- insert into msc_rp_release_status table for pcnt
186 insert into msc_rp_release_status (
187 release_session_id,
188 completion_pcnt,
189 status,
190 last_update_date,
191 last_updated_by,
192 creation_date,
193 created_by,
194 last_update_login)
195 values(
196 psid,
197 0,
198 1, --- releasing
199 sysdate,
200 user_id,
201 sysdate,
202 user_id,
203 1);
204
205
206
207 /* defined in MSC_RP_RELEASE_PHASE
208 1, -- load wip work orders
209 2, -- reschedule wip work orders
210 3, -- load lots job
211 4, -- reschedule lots job
212 5, -- load req
213 6, -- reschedule req
214 7, -- load internal req
215 8, -- reschedule internal req
216 9, -- load schedule
217 10 -- schedule purchasing order
218 11 -- schedule sales order
219 */
220
221
222 OPEN cur_instance;
223 LOOP
224 FETCH cur_instance into l_sr_instance_id ;
225
226 EXIT WHEN cur_instance%NOTFOUND;
227 FOR i in 1..11 LOOP
228 insert into msc_rp_release_results(
229 release_session_id,
230 instance_id,
231 release_code,
232 release_order_count,
233 release_request_id,
234 last_update_date,
235 last_updated_by,
236 creation_date,
237 created_by,
238 last_update_login)
239 values(
240 psid,
241 l_sr_instance_id ,
242 i,
243 null ,
244 null,
245 sysdate,
246 user_id,
247 sysdate,
248 user_id,
249 1);
250 END LOOP; -- end for loop
251 END LOOP; -- end instance loop;
252 commit;
253 -- print_debug('done insert initial status into tables');
254
255 IF v_other_count >0 then
256 print_debug('releasing planed orders, etc ...');
257 MSC_Rel_Plan_PUB.msc_release_plan_sc
258 (pid,
259 p_plan_org_id, --- p_org_id,use to release one org only, if it is same as p_plan_org_id, then release all org
260 p_plan_inst_id, -- release by instance. if it is same as plan instance, then release all
261 p_plan_org_id,
262 p_plan_inst_id,
263 p_compile_designator,
264 user_id,
265 req_load_group_by,
266 req_batch_number,
267 wip_group_id,
268 loaded_jobs, --- load wip job
269 loaded_reqs, -- loaded purchasing req count
270 loaded_scheds, -- rescheduled purchasing req count
271 resched_jobs, -- reschedule job
272 resched_reqs, -- reschedule purchasing req
273 wip_req_id, -- wip request id
274 po_req_load_id, -- new purchasing req
275 po_req_resched_id, -- reschedule purchasing req
276 release_instance, -- instance id
277 null,
278 null,
279 loaded_lot_jobs, --loaded lots job
280 resched_lot_jobs, -- reschedule lots job
281 osfm_req_id, -- lots cp id
282 eam_resched_id,
283 eam_req_id,
284 loaded_int_reqs, --loaded internal req count
285 resched_int_reqs, --rescheduled internal req count
286 int_req_load_id, --- new internal requisition
287 int_req_resched_id, --- reschedule interna requision
288 loaded_int_repair_orders, --loaded int repair
289 int_repair_orders_id, -- int repair cp id
290 loaded_ext_repair_orders,
291 ext_repair_orders_id);
292
293
294 -- print_debug('Done releasing planed orders, etc for instance:' || p_plan_inst_id );
295 end if;
296
297
298
299 --- insert result into table so that ws can report progress
300 ------------------------------------------------------------
301 -- print_debug('Updating status after releasing planned orders, etc ...' );
302
303 update msc_rp_release_status set completion_pcnt=40
304 where release_session_id=psid;
305
306
307 if v_other_count > 0 then
308 FOR i in 1..loaded_jobs.COUNT LOOP
309 update msc_rp_release_results
310 set release_order_count=loaded_jobs(i),
311 release_request_id =wip_req_id(i)
312 where release_session_id=psid
313 and release_code=1
314 and instance_id = release_instance(i);
315
316
317 update msc_rp_release_results
318 set release_order_count=resched_jobs(i),
319 release_request_id =wip_req_id(i)
320 where release_session_id=psid
321 and release_code=2
322 and instance_id = release_instance(i);
323
324
325 update msc_rp_release_results
326 set release_order_count=loaded_lot_jobs(i),
327 release_request_id =osfm_req_id(i)
328 where release_session_id=psid
329 and release_code=3
330 and instance_id = release_instance(i);
331
332 update msc_rp_release_results
333 set release_order_count=resched_lot_jobs(i),
334 release_request_id =osfm_req_id(i)
335 where release_session_id=psid
336 and release_code=4
337 and instance_id = release_instance(i);
338
339 update msc_rp_release_results
340 set release_order_count=loaded_reqs(i),
341 release_request_id =po_req_load_id(i)
342 where release_session_id=psid
343 and release_code=5
344 and instance_id = release_instance(i);
345
346
347 update msc_rp_release_results
348 set release_order_count=resched_reqs(i),
349 release_request_id =po_req_resched_id(i)
350 where release_session_id=psid
351 and release_code=6
352 and instance_id = release_instance(i);
353
354 update msc_rp_release_results
355 set release_order_count=loaded_int_reqs(i),
356 release_request_id =int_req_load_id(i)
357 where release_session_id=psid
358 and release_code=7
359 and instance_id = release_instance(i);
360
361
362 update msc_rp_release_results
363 set release_order_count=resched_int_reqs(i),
364 release_request_id =int_req_resched_id(i)
365 where release_session_id=psid
366 and release_code=8
367 and instance_id = release_instance(i);
368
369 update msc_rp_release_results
370 set release_order_count=loaded_scheds(i),
371 release_request_id =wip_req_id(i)
372 where release_session_id=psid
373 and release_code=9
374 and instance_id = release_instance(i);
375
376
377 end loop;
378 end if;
379 commit;
380 --- release purchasing order, which includes cancel and reschdule po
381 --- msc_rel_wf.reschedule_purchase_orders needs to be updated to report
382 --- request id and progress
383
384 if v_res_po_count > 0 then
385
386 print_debug('Releasing purchasing orders ...');
387 msc_rel_wf.reschedule_purchase_orders(
388 p_plan_id,
389 p_plan_org_id, -- p_org_id release all org order,
390 p_plan_inst_id, --p_inst_id release all instance order,
391 p_plan_org_id,
392 p_plan_inst_id,
393 v_res_po_count,
394 p_released_inst,
395 p_po_res_id,
396 p_po_res_count,
397 p_po_pwb_count);
398
399 -- print_debug('Done releasing purchasing orders ...');
400 end if;
401
402
403
404 -- print_debug('Updating release status after releasing purchasing orders ...');
405 update msc_rp_release_status set completion_pcnt=60
406 where release_session_id=psid;
407
408 if v_res_po_count > 0 then
409 FOR i in 1..v_res_po_count LOOP
410
411 update msc_rp_release_results
412 set release_order_count=p_po_res_count(i),
413 release_request_id =p_po_res_id(i)
414 where release_session_id=psid
415 and release_code=10
416 and instance_id = p_released_inst(i);
417
418
419 end loop;
420 end if;
421 commit;
422
423 ------release sales order
424 ---------------------------------------------------------------------
425 if v_rel_so_count > 0 then
426 -- print_debug('Releasing sales orders ...');
427 msc_rel_wf.release_sales_orders(
428 p_plan_id,
429 p_plan_org_id,
430 p_plan_inst_id,
431 p_plan_org_id,
432 p_plan_inst_id,
433 p_so_released_inst, -- instance count
434 p_so_rel_id, -- request id
435 p_so_rel_count, -- release order count
436 p_so_pwb_count); -- total order which reqire release
437
438 -- print_debug('Done release sales orders ...');
439 end if;
440
441
442 -- print_debug('Updating release status after releasing sales orders ...');
443
444 if v_rel_so_count > 0 then
445 FOR i in 1..p_so_rel_id.count -1 LOOP
446
447 update msc_rp_release_results
448 set release_order_count=p_so_rel_count(i),
449 release_request_id =p_so_rel_id(i)
450 where release_session_id=psid
451 and release_code=11
452 and instance_id = p_so_released_inst(i);
453
454 END LOOP;
455 end if;
456
457
458 update msc_rp_release_status set completion_pcnt=100,status=2
459 where release_session_id=psid;
460
461 commit;
462
463 -- print_debug('All done ...');
464 EXCEPTION
465 WHEN OTHERS THEN
466 update msc_rp_release_status set completion_pcnt=100,status=4
467 where release_session_id=psid;
468 commit;
469
470 fnd_message.set_name('MSC', 'MSC_REL_SETUP_ERR');
471 fnd_message.set_token('ERROR_MESSAGE', sqlerrm);
472 raise_application_error(-20001,sqlerrm);
473 -- commit;
474 END do_release;
475
476
477
478
479 FUNCTION get_instance_release_status(p_sr_instance_id in number) return number IS
480
481
482 l_allow_release_flag number;
483 l_instance_code varchar2(10);
484 cursor cur_release_flag is
485 select decode(apps_ver,3,nvl(allow_release_flag ,2)
486 ,4,nvl(allow_release_flag ,2)
487 ,1) allow_release_flag,
488 instance_code
489 from msc_apps_instances
490 where instance_id = nvl(p_sr_instance_id,-1);
491 begin
492 open cur_release_flag;
493 fetch cur_release_flag
494 into l_allow_release_flag,l_instance_code;
495 if cur_release_flag%notfound then
496 l_allow_release_flag := 2;
497 end if;
498 close cur_release_flag;
499 return( l_allow_release_flag) ;
500 end get_instance_release_status;
501
502
503 FUNCTION get_implement_dock_date(p_plan_id in number,
504 p_inst_id in number,
505 p_org_id in number,
506 p_item_id in number,
507 p_receiving_calendar in varchar2,
508 p_implement_date in date) return date IS
509 ln_pp_lead_time NUMBER;
510 lv_date DATE;
511 CURSOR CPPL IS
512 SELECT nvl(postprocessing_lead_time, 0)
513 FROM msc_system_items
514 WHERE plan_id = p_plan_id
515 AND sr_instance_id = p_inst_id
516 AND ORGANIZATION_ID = p_org_id
517 AND INVENTORY_ITEM_ID = p_item_id;
518
519 BEGIN
520
521 OPEN CPPL;
522 FETCH CPPL INTO ln_pp_lead_time;
523 CLOSE CPPL;
524 -- preserve the time stamps and calculate
525 -- the date after offsetting
526 -- the lead time from the implement_date
527 lv_date := p_implement_date -
528 (trunc(p_implement_Date)-
529 trunc(msc_calendar.date_offset(p_org_id,p_inst_id,1,p_implement_Date,
530 -1 * ln_pp_lead_time)));
531
532 --if the Receiving calendar is not null
533 --validate the date against the Receiving Calendar
534 --else validate the date against the
535 -- Org Manufacturing Calendar
536 if p_receiving_calendar is not null then
537 lv_date := lv_date - (trunc(lv_date) - trunc(msc_calendar.PREV_WORK_DAY(p_receiving_calendar,p_inst_id, trunc(lv_date))));
538 else
539 lv_date := msc_calendar.PREV_WORK_DAY(p_org_id,p_inst_id,1,trunc(lv_date));
540 end if;
541 return greatest(trunc(sysdate),lv_date);
542 exception when others then
543 return null;
544 END get_implement_dock_date;
545
546
547
548
549 FUNCTION get_implement_ship_date(p_plan_id in number,
550 p_inst_id in number,
551 p_org_id in number,
552 p_order_type in number,
553 p_source_sr_instance_id in number,
554 p_source_org_id in number,
555 p_sourcre_vendor_site_id in number,
556 p_ship_method in varchar2,
557 p_intransit_calendar in varchar2,
558 p_ship_calendar in varchar2,
559 p_implement_dock_date in date,
560 p_source_table in varchar2) return date
561 IS
562 ln_intransit_lead_time NUMBER := 0;
563 ln_tp_arranged_by NUMBER := 1;
564 lv_date DATE;
565 x_intransit_lead_time NUMBER;
566 x_return_status VARCHAR2(30);
567
568
569 l_session_id NUMBER := 0;
570 l_src_org_id NUMBER := p_source_org_id;
571 l_src_vendor_site_id NUMBER := p_sourcre_vendor_site_id;
572 l_src_sr_inst_id NUMBER := p_source_sr_instance_id;
573 l_org_id NUMBER := p_org_id;
574 l_sr_inst_id NUMBER := p_inst_id;
575 l_ship_method varchar2(30) := p_ship_method;
576
577 BEGIN
578 IF (p_order_type in (1,2,5)
579 AND p_SOURCE_TABLE = 'MSC_SUPPLIES') THEN
580
581 --- get the unique number identifying the current session
582 SELECT MRP.mrp_atp_schedule_temp_s.nextval
583 INTO l_session_id
584 FROM dual;
585
586 -- Call ATP method to get the intransit lead time value in
587 -- the out parameter x_intransit_lead_time
588 MSC_ATP_PROC.ATP_Intransit_LT(
589 2, l_session_id,
590 l_src_org_id,
591 null,
592 l_src_vendor_site_id,
593 l_src_sr_inst_id,
594 l_org_id,
595 null,
596 null,
597 l_sr_inst_id,
598 l_ship_method,
599 x_intransit_lead_time,
600 x_return_status);
601 -- preserve the time stamps and calculate the date
602 -- after offsetting the lead time from the
603 --implement_dock_date
604 lv_date := p_implement_Dock_Date-
605 (trunc(p_implement_dock_date)-
606 trunc(msc_calendar.date_offset(p_org_id,p_inst_id,1,
607 p_implement_dock_date,-1 * x_intransit_lead_time)));
608 -- if intransit calendar is not null then validate the
609 -- date against the intransit calendar
610 -- else validate the date against the shipping calendar.
611 if p_intransit_calendar is not null then
612 lv_date := lv_date - (trunc(lv_date) -
613 trunc(msc_calendar.PREV_WORK_DAY(p_intransit_calendar, p_inst_id, trunc(lv_date))));
614 else
615 lv_date := lv_date - (trunc(lv_date) -
616 trunc(msc_calendar.PREV_WORK_DAY(p_ship_calendar, p_inst_id, trunc(lv_date))));
617 end if;
618 return greatest(trunc(sysdate),lv_date);
619 else
620 return null;
621 END IF;
622 exception when others then
623 return null;
624 END get_implement_ship_date;
625
626
627 FUNCTION GET_WIP_JOB_PREFIX(p_instance_id in number) return varchar2 is
628
629 l_seq_num number;
630 lv_job_prefix varchar2(40);
631
632 begin
633 l_seq_num := msc_rel_wf.get_job_seq_from_source(p_instance_id);
634
635
636
637 msc_rel_wf.get_profile_value(p_profile_name => 'WIP_JOB_PREFIX',
638 p_instance_id =>p_instance_id,
639 p_profile_value => lv_job_prefix);
640 return lv_job_prefix||to_char(l_seq_num);
641 END GET_WIP_JOB_PREFIX;
642
643
644
645
646 Function get_Imp_Employee_id(
647 p_plan_id in number,
648 p_org_id in number,
649 p_inst_id in number,
650 p_item_id in number,
651 p_planner_code in varchar2) return number is
652
653
654
655
656 CURSOR C1 IS
657 SELECT employee_id
658 FROM msc_planners
659 WHERE planner_code = p_planner_code
660 AND organization_id = p_org_id
661 AND sr_instance_id = p_inst_id
662 AND current_employee_flag = 1;
663
664 CURSOR C2 IS
665 SELECT employee_id
666 FROM msc_planners mp,
667 msc_system_items msi
668 WHERE mp.planner_code = msi.planner_code
669 AND mp.organization_id = msi.organization_id
670 AND mp.sr_instance_id = msi.sr_instance_id
671 AND mp.current_employee_flag = 1
672 AND msi.plan_id = p_plan_id
673 AND msi.organization_id = p_org_id
674 AND msi.sr_instance_id = p_inst_id
675 AND msi.inventory_item_id = p_item_id;
676
677 l_employee_id NUMBER;
678
679 BEGIN
680 if p_planner_code is not null then
681
682
683 OPEN C1;
684
685 FETCH C1 INTO l_employee_id;
686 CLOSE C1;
687 else
688 OPEN C2;
689
690 FETCH C2 INTO l_employee_id;
691 CLOSE C2;
692
693 END IF;
694 return l_employee_id;
695
696 end get_Imp_Employee_id;
697
698
699
700 FUNCTION Check_Source_Supp_Org (
701 p_inst_id in number,
702 p_org_id in number) return Number is
703
704 l_count NUMBER;
705
706 CURSOR C1 IS
707 SELECT count(1)
708 FROM msc_trading_partners
709 WHERE sr_tp_id = p_org_id
710 AND sr_instance_id = p_inst_id
711 AND (modeled_customer_id is not null
712 OR modeled_supplier_id is not null);
713
714 BEGIN
715
716 OPEN C1;
717 FETCH C1 INTO l_count;
718 CLOSE C1;
719
720 if (l_count = 0) then
721
722 RETURN 2; -- false;
723 else
724 RETURN 1; --- true;
725 end if;
726
727 End Check_Source_Supp_Org;
728
729
730 FUNCTION validate_order_for_release(
731 p_plan_id in number,
732 p_inst_id in number,
733 p_org_id in number,
734 p_org_code in varchar2,
735 p_item_id in number,
736 p_vmi in number,
737 p_source_Table in varchar2,
738 p_transaction_id in number,
739 p_order_type in number,
740 p_source_org_id in number,
741 P_bom_item_type in number,
742 p_release_time_fence_code in number,
743 p_in_source_plan in number,
744 p_build_in_wip_flag in number,
745 p_purchasing_enabled_flag in number,
746 p_planning_make_buy_code in number,
747 p_planner_code in varchar2,
748 p_implement_alternate_routing in varchar2,
749 p_user_id in number,
750 p_resp_id in number,
751 p_appl_id in number
752 ) return varchar2 IS
753
754
755
756
757 p_valid number;
758 p_rel_schd_OK varchar2(3);
759 p_rel_error varchar2(1024);
760 l_instance_Code varchar2(10);
761 l_def_pref_id number;
762 l_pref_release_vmi varchar2(10);
763 l_include_so varchar2(10);
764 l_temp Number;
765
766 l_user_id number := nvl(FND_PROFILE.VALUE('USER_ID'),1);
767 l_plan_type number :=1 ; -- temp set as ASCP plan
768
769 CURSOR empl_C IS
770 SELECT mp.employee_id
771 FROM msc_planners mp
772 WHERE mp.planner_code = p_planner_code
773 AND mp.organization_id = p_org_id
774 AND mp.sr_instance_id = p_inst_id
775 AND mp.current_employee_flag = 1;
776
777 CURSOR loc_C IS
778 select mtps.sr_tp_site_id
779 from msc_trading_partners mtp,
780 msc_trading_partner_sites mtps
781 where mtp.sr_tp_id = p_org_id
782 AND mtp.sr_instance_id = p_inst_id
783 AND mtp.partner_type =3
784 AND mtps.partner_id = mtp.partner_id;
785
786
787
788 BEGIN
789 /* we should not release vmi items*/
790 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
791 -- msc_rel_wf.init_db('OPERATIONS');
792 l_def_pref_id := msc_get_name.get_default_pref_id(l_user_id);
793 l_pref_release_vmi:= msc_get_name.GET_preference('ORDERS_RELEASE_VMI_ITEMS', l_def_pref_id, l_plan_type);
794 -- l_include_so:= msc_get_name.GET_preference('INCLUDE_SO', l_def_pref_id, l_plan_type);
795 l_include_so:= 'Y';
796
797 IF l_pref_release_vmi = 'N' and nvl(p_vmi,2) = 1 then
798 p_rel_error :='Cannot release this as the preference Release VMI Items is set to No';
799 return p_rel_error;
800 END IF;
801
802
803
804 /* if it is supply and order type not in (1,2,3,5,13) */
805 if (p_source_table='MSC_SUPPLIES' AND
806 p_order_type NOT IN (1, 2, 3, 5, 13) )then
807 p_rel_error :=FND_MESSAGE.GET_STRING('MSC', 'MSC_WB_RELEASE_3');
808 return p_rel_error;
809 END IF;
810
811
812
813 /* if the instance is not release enabled */
814 if (get_instance_release_status(p_inst_id) = 2) then
815 l_instance_code := substr(p_org_code,1,instr(p_org_code,':',-1)-1);
816 fnd_message.set_name('MSC','MSC_ALLOW_RELEASE_INSTANCE');
817 fnd_message.set_token('INSTANCE',l_instance_code);
818 p_rel_error :=fnd_message.get;
819 return p_rel_error;
820 end if;
821
822
823 /* if source db is not running */
824 if not (msc_rel_wf.is_source_db_up(p_inst_id)) then
825 p_rel_error :=FND_MESSAGE.GET_STRING('MSC', 'MSC_SOURCE_IS_INVALID');
826 return p_rel_error;
827 end if;
828
829
830 /* if plan option does not allow to release sales order */
831 if (p_order_type=30) and (l_include_so='N') then
832 p_rel_error :=FND_MESSAGE.GET_STRING('MSC', 'MSC_WB_RELEASE1');
833 return p_rel_error;
834 end if;
835
836
837 /* model/option classes can not be released */
838 if (p_bom_item_type in (1, 2, 3, 5) )then
839 p_rel_error := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_MODEL');
840 return p_rel_error;
841 end if;
842
843
844
845
846
847 /* Kanban item can not be released */
848 if (p_order_type=5 and p_source_table='MSC_SUPPLIES' and p_release_time_fence_code = 6) then
849 p_rel_error := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_KANBAN');
850 return p_rel_error;
851 end if;
852
853 /* can not release planned order as flow schedule */
854 /* what if it is new planned order where
855 /* engine has not set cfm flag yet ??? */
856 if ( p_order_type = 5) then
857
858 l_temp := msc_get_name.check_cfm(
859 p_plan_id,
860 p_org_id,
861 p_inst_id,
862 p_item_id,
863 p_transaction_id,
864 p_implement_alternate_routing);
865 if l_temp = 1 then
866 p_rel_error := FND_MESSAGE.GET_STRING('MSC', 'MSC_NO_FLOW_SCHEDULE');
867 return p_rel_error;
868 end if;
869 end if;
870
871
872
873 /* not able to release sales order */
874 if (p_source_table ='MSC_DEMANDS' and p_order_type=30) then
875 p_rel_error := msc_rel_wf.verify_so_release(p_plan_id,p_transaction_id,p_inst_id);
876 if (p_rel_error is not null) then
877 return p_rel_error;
878 end if;
879
880 end if;
881
882
883
884
885
886 if p_in_source_plan = 1 then
887 p_rel_schd_OK := nvl(fnd_profile.value('MSC_DRP_RELEASE_FROM_MRP'),'N');
888
889 if (p_rel_schd_OK = 'N') then
890
891 p_rel_error := FND_MESSAGE.GET_STRING('MRP', 'MSC_IN_SOURCE_PLAN');
892 return p_rel_error;
893 end if;
894 end if; -- if p_in_source_plan = 1 then
895
896
897
898
899
900 /* -- 4417550, make planned order can be released only when
901 -- child supply are on hand or make planned order
902 */
903
904 p_valid := null;
905 if (nvl(FND_PROFILE.VALUE('MSC_REL_ONLY_ONHAND_SUPPLY'),'N') = 'Y'
906 and p_order_type = 5 and p_org_id = p_source_org_id) then
907 p_valid := MSC_SELECT_ALL_FOR_RELEASE_PUB.child_supplies_onhand(
908 p_plan_id, p_transaction_id);
909 if p_valid > 0 then
910 p_rel_error := FND_MESSAGE.GET_STRING('MSC', 'MSC_REL_ONLY_ONHAND_WARN2');
911 return p_rel_error;
912 end if;
913
914 end if;
915
916
917
918
919 return p_rel_error;
920
921 END validate_order_for_release;
922
923
924 FUNCTION GET_ACTION (arg_source_table IN VARCHAR2,
925 arg_plan_id IN NUMBER ,
926 arg_sr_instance_id in number,
927 arg_org_id in number,
928 arg_item_id in number,
929 arg_bom_item_type IN NUMBER ,
930 arg_base_item_id IN NUMBER,
931 arg_wip_supply_type IN NUMBER ,
932 arg_order_type IN NUMBER ,
933 arg_rescheduled_flag IN NUMBER,
934 arg_disposition_status_type IN NUMBER ,
935 arg_new_due_date IN DATE ,
936 arg_old_due_date IN DATE ,
937 arg_implemented_quantity IN NUMBER ,
938 arg_quantity_in_process IN NUMBER ,
939 arg_quantity_rate IN NUMBER ,
940 arg_release_time_fence_code IN NUMBER ,
941 arg_reschedule_days IN NUMBER ,
942 arg_firm_quantity IN NUMBER ,
943 arg_mrp_planning_code IN NUMBER,
944 arg_lots_exist IN NUMBER
945 ) RETURN varchar2 is
946
947
948 cursor crit_component IS
949 select critical_component_flag
950 from msc_system_items
951 where plan_id = arg_plan_id
952 and inventory_item_id = arg_item_id
953 and organization_id = arg_org_id
954 and sr_instance_id = arg_sr_instance_id;
955
956 l_critical_component number;
957 l_action VARCHAR2(20);
958 BEGIN
959 open crit_component;
960 fetch crit_component into l_critical_component;
961 close crit_component;
962 l_action := msc_get_name.action(arg_source_table,
963 arg_bom_item_type,
964 arg_base_item_id,
965 arg_wip_supply_type,
966 arg_order_type,
967 arg_rescheduled_flag,
968 arg_disposition_status_type,
969 arg_new_due_date,
970 arg_old_due_date,
971 arg_implemented_quantity,
972 arg_quantity_in_process,
973 arg_quantity_rate,
974 arg_release_time_fence_code,
975 arg_reschedule_days,
976 arg_firm_quantity,
977 arg_plan_id,
978 nvl(l_critical_component,2),
979 arg_mrp_planning_code,
980 arg_lots_exist);
981 return l_action;
982
983 END GET_ACTION;
984
985 FUNCTION get_Implement_Location_Id(p_inst_id in number,
986 p_org_id in number) return number is
987
988
989
990 CURSOR C1 IS
991 SELECT partner_id
992 FROM msc_trading_partners
993 WHERE partner_type = 3
994 AND sr_tp_id = p_org_id
995 AND sr_instance_id = p_inst_id;
996
997 l_partner_id NUMBER;
998
999 CURSOR C2 IS
1000 SELECT s.sr_tp_site_id, s.location
1001 FROM msc_trading_partner_sites s
1002 where s.partner_id = l_partner_id;
1003
1004 l_loc_id NUMBER;
1005 l_loc_code varchar2(80);
1006
1007 BEGIN
1008
1009 OPEN C1;
1010 FETCH C1 INTO l_partner_id;
1011 CLOSE C1;
1012
1013 OPEN C2;
1014 FETCH C2 INTO l_loc_id, l_loc_code;
1015
1016 return l_loc_id;
1017
1018 END get_Implement_Location_Id;
1019
1020
1021 FUNCTION GET_IMPLEMENT_WIP_CLASS_CODE(
1022 p_plan_id in number,
1023 p_instance_id in number,
1024 p_org_id in number,
1025 p_item_id in number,
1026 p_transaction_id in number,
1027 p_order_type in number,
1028 p_project_id in number,
1029 p_implement_project_id in number,
1030 p_implement_as in number,
1031 p_implement_alternate_routing in varchar2) return varchar2 is
1032
1033
1034 l_def_pref_id Number;
1035 l_option_job_code varchar2(20):=null;
1036 v_temp number;
1037 v_sr_item number;
1038 l_wip_class_code varchar2(20);
1039 l_plan_type number:=1; --- temp set as ASCP plan
1040 v_project_id number;
1041 CURSOR sr_item_cur IS
1042 select sr_inventory_item_id
1043 from msc_system_items
1044 where plan_id = p_plan_id
1045 and organization_id = p_org_id
1046 and sr_instance_id = p_instance_id
1047 and inventory_item_id = p_item_id;
1048
1049 BEGIN
1050
1051
1052 if (p_order_type=5 and p_implement_as=3) then
1053 -- in rp there is no user preference value
1054 -- l_def_pref_id := msc_get_name.get_default_pref_id(fnd_global.user_id);
1055 --l_option_job_code:= msc_get_name.GET_preference('ORDERS_JOB_CLASS_CODE', l_def_pref_id, l_plan_type);
1056
1057 if (l_option_job_code is not null) then
1058 l_wip_class_code :=l_option_job_code;
1059 else
1060 v_temp := msc_get_name.check_cfm(
1061 p_plan_id,
1062 p_org_id,
1063 p_instance_id,
1064 p_item_id,
1065 p_transaction_id,
1066 p_implement_alternate_routing);
1067 if nvl(v_temp,-1) <> 3 then
1068 if p_implement_project_id is NULL THEN
1069 v_project_id := p_project_id;
1070 ELSE
1071 v_project_id := p_implement_project_id;
1072 END IF;
1073
1074 OPEN sr_item_cur;
1075 FETCH sr_item_cur INTO v_sr_item;
1076 CLOSE sr_item_cur;
1077
1078 l_wip_class_code := msc_rel_wf.get_acc_class_from_source(
1079 p_org_id,
1080 v_sr_item,
1081 v_project_id,
1082 p_instance_id);
1083
1084 END IF;
1085
1086 END IF;
1087 END IF;
1088 return l_wip_class_code ;
1089 END GET_IMPLEMENT_WIP_CLASS_CODE;
1090
1091 PROCEDURE PRINT_DEBUG(MSG IN VARCHAR2) IS
1092
1093 BEGIN
1094 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1095 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE ,
1096 'MSC_RP_RELEASE_PUB', MSG);
1097 ELSE
1098 dbms_output.PUT_LINE(MSG);
1099 dbms_output.new_line();
1100 END If;
1101
1102 END PRINT_DEBUG;
1103 procedure validate_icx_session(p_icx_cookie in varchar2,p_function in varchar2 DEFAULT NULL) is
1104 l_function varchar2(100) :=nvl(p_function,'SIM_WORKBENCH');
1105 SECURITY_CONTEXT_INVALID exception;
1106
1107 begin
1108 app_session.validate_icx_session(p_icx_cookie);
1109 if fnd_function.test(l_function) then --- SIM_WORKBENCH
1110 return;
1111 else
1112 raise SECURITY_CONTEXT_INVALID;
1113 end if;
1114 exception
1115 when others then
1116 raise SECURITY_CONTEXT_INVALID;
1117 end validate_icx_session;
1118
1119 FUNCTION GET_RP_PLAN_PROFILE_VALUE(P_PLAN_ID IN NUMBER,
1120 P_PROFILE_CODE IN VARCHAR2) RETURN VARCHAR2
1121
1122 IS
1123
1124 cursor cur_plan_profile is
1125 select profile_value
1126 from msc_plan_profiles
1127 where plan_id=p_plan_id
1128 and profile_code=p_profile_code;
1129
1130 x_profile_value varchar2(240):=null;
1131
1132 BEGIN
1133
1134 open cur_plan_profile;
1135 fetch cur_plan_profile into x_profile_value;
1136 if cur_plan_profile%notfound then
1137 return null;
1138
1139 end if;
1140 close cur_plan_profile;
1141 return x_profile_value;
1142
1143 END GET_RP_PLAN_PROFILE_VALUE;
1144
1145 Function GET_REQUEST_STATUS (
1146 request_id IN OUT nocopy number,
1147 application IN varchar2 default NULL,
1148 program IN varchar2 default NULL,
1149 phase OUT nocopy varchar2 ,
1150 status OUT nocopy varchar2 ,
1151 dev_phase OUT nocopy varchar2,
1152 dev_status OUT nocopy varchar2,
1153 message OUT nocopy varchar2) return number
1154 IS
1155 l_ret_st Number;
1156 begin
1157 if (FND_CONCURRENT.GET_REQUEST_STATUS(request_id,
1158 application ,
1159 program ,
1160 phase ,
1161 status ,
1162 dev_phase ,
1163 dev_status ,
1164 message ) ) then
1165 return 0;
1166 else
1167 return -1;
1168 end if;
1169 end GET_REQUEST_STATUS;
1170
1171 Function test_permission(pname in varchar2) return number is
1172 SECURITY_CONTEXT_INVALID exception;
1173
1174 begin
1175 if fnd_function.test(pname) then
1176 return 1;
1177 else
1178 return 2 ;
1179 end if;
1180 exception
1181 when others then
1182 return 2;
1183 end test_permission;
1184
1185 Function save_user_profile(name in varchar2, value in varchar2) return number is
1186 begin
1187 if fnd_profile.save_user(name,value) then
1188 return 1;
1189 else
1190 return 2 ;
1191 end if;
1192 exception
1193 when others then
1194 return 2;
1195 end save_user_profile;
1196
1197
1198 END MSC_RP_RELEASE_PUB;
[Home] [Help]