[Home] [Help]
PACKAGE BODY: APPS.MSC_REL_WF
Source
1 PACKAGE BODY msc_rel_wf AS
2 /*$Header: MSCRLWFB.pls 120.22 2012/03/22 07:23:25 giyer ship $ */
3
4 g_item_type varchar2(10) := 'MSCRELWF';
5
6
7 PROCEDURE release_supplies
8 (
9 errbuf OUT NOCOPY VARCHAR2
10 ,retcode OUT NOCOPY NUMBER
11 , arg_plan_id IN NUMBER
12 , arg_org_id IN NUMBER
13 , arg_instance IN NUMBER
14 , arg_owning_org_id IN NUMBER
15 , arg_owning_instance IN NUMBER) IS
16
17
18 CURSOR po_res IS
19 SELECT s.transaction_id, s.sr_instance_id
20 FROM msc_supplies s,
21 msc_plan_organizations_v orgs
22 where s.release_errors is NULL
23 AND s.po_line_id is not null
24 AND s.plan_id = orgs.plan_id
25 and s.load_type = PURCHASE_ORDER_RESCHEDULE
26 and s.order_type = PURCHASE_ORDER
27 AND s.organization_id = orgs.planned_organization
28 AND s.sr_instance_id = orgs.sr_instance_id
29 AND orgs.organization_id = arg_owning_org_id
30 AND orgs.owning_sr_instance = arg_owning_instance
31 AND orgs.plan_id = arg_plan_id
32 AND orgs.planned_organization = decode(arg_org_id,
33 arg_owning_org_id, orgs.planned_organization,
34 arg_org_id)
35 AND orgs.sr_instance_id = decode(arg_instance,
36 arg_owning_instance, orgs.sr_instance_id,
37 arg_instance);
38
39 CURSOR req_res IS
40 SELECT s.transaction_id, s.sr_instance_id
41 FROM msc_supplies s,
42 msc_plan_organizations_v orgs
43 where s.release_errors is NULL
44 AND s.plan_id = orgs.plan_id
45 and s.load_type = PURCHASE_REQ_RESCHEDULE
46 and s.order_type = PURCHASE_REQ
47 AND s.po_line_id IS NOT NULL
48 AND s.organization_id = orgs.planned_organization
49 AND s.sr_instance_id = orgs.sr_instance_id
50 AND orgs.organization_id = arg_owning_org_id
51 AND orgs.owning_sr_instance = arg_owning_instance
52 AND orgs.plan_id = arg_plan_id
53 AND orgs.planned_organization = decode(arg_org_id,
54 arg_owning_org_id, orgs.planned_organization,
55 arg_org_id)
56 AND orgs.sr_instance_id = decode(arg_instance,
57 arg_owning_instance, orgs.sr_instance_id,
58 arg_instance);
59
60 CURSOR instances_cur IS
61 SELECT distinct mai.instance_id,
62 decode(mai.m2a_dblink, null,' ','@'||m2a_dblink),
63 mai.instance_code
64 FROM msc_apps_instances mai,
65 msc_plan_organizations_v orgs
66 where orgs.organization_id = arg_owning_org_id
67 AND orgs.owning_sr_instance = arg_owning_instance
68 AND orgs.plan_id = arg_plan_id
69 AND orgs.planned_organization = decode(arg_org_id,
70 arg_owning_org_id, orgs.planned_organization,
71 arg_org_id)
72 AND orgs.sr_instance_id = decode(arg_instance,
73 arg_owning_instance, orgs.sr_instance_id,
74 arg_instance)
75 AND orgs.sr_instance_id = mai.instance_id;
76
77 v_transaction_id number;
78 v_instance_id number;
79 v_load_type number;
80 v_dblink varchar2(128);
81 v_instance_code varchar2(5);
82
83 cursor batch_cur IS
84 SELECT distinct load_type
85 from msc_supplies
86 where plan_id = arg_plan_id
87 and sr_instance_id = v_instance_id
88 and load_type in (WIP_DIS_MASS_LOAD,WIP_REP_MASS_LOAD,
89 LOT_BASED_JOB_LOAD,LOT_BASED_JOB_RESCHEDULE,
90 WIP_DIS_MASS_RESCHEDULE,PURCHASE_REQ_MASS_LOAD,
91 EAM_DIS_MASS_RESCHEDULE);
92 BEGIN
93 retcode :=0;
94 msc_util.msc_debug('****** Start of Program ******');
95 -- launch wf for purchase order reschedule
96 OPEN po_res;
97 LOOP
98 FETCH po_res INTO v_transaction_id, v_instance_id;
99 EXIT WHEN po_res%NOTFOUND;
100 msc_util.msc_debug('start workflow to reschedule Purchase Order, transaction_id ='||v_transaction_id);
101 start_reschedule_po_wf(arg_plan_id, v_transaction_id,
102 v_instance_id, PURCHASE_ORDER_RESCHEDULE);
103 END LOOP;
104 CLOSE po_res;
105
106 -- launch wf for purchase req reschedule
107 OPEN req_res;
108 LOOP
109 FETCH req_res INTO v_transaction_id, v_instance_id;
110 EXIT WHEN req_res%NOTFOUND;
111 msc_util.msc_debug('start workflow to reschedule Purchase Req, transaction_id='||v_transaction_id);
112 start_reschedule_po_wf(arg_plan_id, v_transaction_id,
113 v_instance_id, PURCHASE_REQ_RESCHEDULE);
114 END LOOP;
115 CLOSE req_res;
116
117 -- lauch workflow for releasing supplies in batch process
118 open instances_cur; -- loop thru each instance
119 LOOP
120 FETCH instances_cur INTO v_instance_id, v_dblink, v_instance_code;
121 EXIT WHEN instances_cur%NOTFOUND;
122 OPEN batch_cur; -- launch one workflow for each load type
123 LOOP
124 FETCH batch_cur INTO v_load_type;
125 EXIT WHEN batch_cur%NOTFOUND;
126 msc_util.msc_debug('start workflow for batch update');
127 start_release_batch_wf(arg_plan_id, arg_org_id, v_instance_id,
128 arg_owning_org_id, arg_owning_instance,v_dblink,
129 v_load_type, v_instance_code);
130 END LOOP;
131 close batch_cur;
132
133 END LOOP;
134 close instances_cur;
135
136 exception when others then
137 retcode :=2;
138 raise;
139 END release_supplies;
140
141 Procedure start_reschedule_po_wf(p_plan_id number,
142 p_transaction_id number,
143 p_instance_id number,
144 p_load_type number) IS
145
146 p_item_key varchar2(30) := to_char(p_plan_id)||'-'||
147 to_char(p_transaction_id);
148 p_process varchar2(30);
149 p_dblink varchar2(128);
150 p_instance_code varchar2(5);
151
152 CURSOR instance_cur IS
153 SELECT decode(mai.m2a_dblink, null,' ','@'||m2a_dblink),
154 mai.instance_code
155 FROM msc_apps_instances mai
156 WHERE mai.instance_id = p_instance_id;
157 BEGIN
158 deleteActivities(p_item_key);
159
160 OPEN instance_cur;
161 FETCH instance_cur INTO p_dblink, p_instance_code;
162 CLOSE instance_cur;
163
164 if p_dblink is not null then
165 deleteActivities(p_item_key,p_dblink);
166 end if;
167
168 p_process := 'RES_PO';
169
170 wf_engine.CreateProcess( itemtype => g_item_type,
171 itemkey => p_item_key,
172 process => p_process);
173 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
174 itemkey => p_item_key,
175 aname => 'PLAN_ID',
176 avalue => p_plan_id);
177 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
178 itemkey => p_item_key,
179 aname => 'TRANSACTION_ID',
180 avalue => p_transaction_id);
181 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
182 itemkey => p_item_key,
183 aname => 'SR_INSTANCE_ID',
184 avalue => p_instance_id);
185 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
186 itemkey => p_item_key,
187 aname => 'LOAD_TYPE',
188 avalue => p_load_type);
189 wf_engine.SetItemAttrText( itemtype => g_item_type,
190 itemkey => p_item_key,
191 aname => 'DBLINK',
192 avalue => p_dblink);
193 wf_engine.SetItemAttrText( itemtype => g_item_type,
194 itemkey => p_item_key,
195 aname => 'INSTANCE_CODE',
196 avalue => p_instance_code);
197
198 wf_engine.StartProcess( itemtype => g_item_type,
199 itemkey => p_item_key);
200
201 reset_load_type(p_plan_id, p_transaction_id);
202 exception when others then
203 raise;
204 END start_reschedule_po_wf;
205
206 Procedure notify_planner_program(p_plan_id number,
207 p_transaction_id number,
208 p_planner varchar2,
209 p_process varchar2) IS
210 p_result boolean;
211 p_request_id number;
212 Begin
213 msc_rel_wf.init_db(p_planner);
214 p_result := fnd_request.set_mode(true);
215 -- this will call mrp_rel_wf.reschedule_po_wf
216
217 p_request_id := fnd_request.submit_request(
218 'MSC',
219 'MSCNTFPN',
220 null,
221 null,
222 false,
223 p_plan_id,
224 p_transaction_id,
225 p_planner,
226 p_process);
227
228 exception when others then
229 p_request_id :=0;
230 raise;
231 End notify_planner_program;
232
233 Procedure notify_planner_decline(
234 errbuf OUT NOCOPY VARCHAR2,
235 retcode OUT NOCOPY NUMBER,
236 p_plan_id number,
237 p_transaction_id number,
238 p_planner varchar2,
239 p_process varchar2) IS
240
241 p_item_key varchar2(100) := to_char(p_plan_id)
242 ||'-'||to_char(p_transaction_id)
243 ||'-'||p_process;
244
245 Cursor po_attri IS
246 select mp.compile_designator,
247 msi.item_name,
248 msc_get_name.org_code(ms.organization_id,ms.sr_instance_id),
249 msc_get_name.supplier(ms.supplier_id),
250 DECODE(ms.order_type,5,to_char(ms.transaction_id),ms.order_number),
251 msc_get_name.lookup_meaning('MRP_ORDER_TYPE',ms.order_type),
252 ms.new_schedule_date,
253 ms.implement_date,
254 ms.new_order_quantity,
255 msi.buyer_name
256 from msc_plans mp,
257 msc_system_items msi,
258 msc_supplies ms
259 where ms.plan_id = p_plan_id
260 and ms.transaction_id = p_transaction_id
261 and mp.plan_id = ms.plan_id
262 and msi.plan_id = ms.plan_id
263 and msi.organization_id = ms.organization_id
264 and msi.sr_instance_id = ms.sr_instance_id
265 and msi.inventory_item_id = ms.inventory_item_id
266 ;
267
268 l_plan_name varchar2(20);
269 l_item_name varchar2(40);
270 l_org_code varchar2(7);
271 l_supplier varchar2(80);
272 l_buyer varchar2(80);
273 l_order varchar2(80);
274 l_order_type varchar2(80);
275 l_old_date date;
276 l_new_date date;
277 l_qty number;
278 BEGIN
279
280 OPEN po_attri;
281 FETCH po_attri INTO l_plan_name,
282 l_item_name,
283 l_org_code,
284 l_supplier,
285 l_order,
286 l_order_type,
287 l_old_date,
288 l_new_date,
289 l_qty,
290 l_buyer;
291 wf_engine.CreateProcess( itemtype => g_item_type,
292 itemkey => p_item_key,
293 process => p_process);
294
295 wf_engine.SetItemAttrText( itemtype => g_item_type,
296 itemkey => p_item_key,
297 aname => 'PLANNER',
298 avalue => p_planner);
299
300 wf_engine.SetItemAttrText( itemtype => g_item_type,
301 itemkey => p_item_key,
302 aname => 'BUYER',
303 avalue => l_buyer);
304
305 wf_engine.SetItemAttrText( itemtype => g_item_type,
306 itemkey => p_item_key,
307 aname => 'PLAN_NAME',
308 avalue => l_plan_name);
309
310 wf_engine.SetItemAttrText( itemtype => g_item_type,
311 itemkey => p_item_key,
312 aname => 'ITEM_NAME',
313 avalue => l_item_name);
314 wf_engine.SetItemAttrText( itemtype => g_item_type,
315 itemkey => p_item_key,
316 aname => 'ORG_CODE',
317 avalue => l_org_code);
318 wf_engine.SetItemAttrText( itemtype => g_item_type,
319 itemkey => p_item_key,
320 aname => 'SUPPLIER',
321 avalue => l_supplier);
322
323 wf_engine.SetItemAttrText( itemtype => g_item_type,
324 itemkey => p_item_key,
325 aname => 'ORDER_NAME',
326 avalue => l_order);
327 wf_engine.SetItemAttrText( itemtype => g_item_type,
328 itemkey => p_item_key,
329 aname => 'ORDER_TYPE',
330 avalue => l_order_type);
331 wf_engine.SetItemAttrDate( itemtype => g_item_type,
332 itemkey => p_item_key,
333 aname => 'ORDER_DATE',
334 avalue => l_old_date);
335 wf_engine.SetItemAttrDate( itemtype => g_item_type,
336 itemkey => p_item_key,
337 aname => 'NEW_ORDER_DATE',
338 avalue => l_new_date);
339 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
340 itemkey => p_item_key,
341 aname => 'ORDER_QTY',
342 avalue => l_qty);
343
344 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
345 itemkey => p_item_key,
346 aname => 'PLAN_ID',
347 avalue => p_plan_id);
348
349 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
350 itemkey => p_item_key,
351 aname => 'TRANSACTION_ID',
352 avalue => p_transaction_id);
353
354 wf_engine.StartProcess( itemtype => g_item_type,
355 itemkey => p_item_key);
356 exception when others then
357 wf_core.context('MSC_REL_WF', 'notify planner', p_item_key,g_item_type);
358 raise;
359 END notify_planner_decline;
360
361 PROCEDURE Select_buyer_supplier( itemtype in varchar2,
362 itemkey in varchar2,
363 actid in number,
364 funcmode in varchar2,
365 resultout out NOCOPY varchar2 ) is
366 p_plan_id number;
367 p_transaction_id number;
368 p_load_type number;
369 lv_sql_stmt varchar2(2000);
370 l_supplier varchar2(100);
371 p_dblink varchar2(30);
372 p_process varchar2(30);
373 l_buyer varchar2(50);
374 p_request_id number;
375 p_query_id number;
376 l_plan_name varchar2(20);
377 l_load_type number;
378 p_instance_code varchar2(20);
379 l_item_name varchar2(40);
380 l_org_code varchar2(7);
381 l_order varchar2(80);
382 l_order_type varchar2(80);
383 l_old_date date;
384 l_new_date date;
385 l_qty number;
386 l_planner varchar2(40) := FND_PROFILE.VALUE('USERNAME');
387 l_resp_name varchar2(80) :=FND_GLOBAL.RESP_NAME;
388 TYPE PoCurTyp IS REF CURSOR;
389 poCur PoCurTyp;
390
391 CURSOR buyer_c IS
392 select mpc.name
393 from msc_supplies ms,
394 msc_system_items msi,
395 msc_partner_contacts mpc
396 where ms.plan_id = p_plan_id
397 and ms.transaction_id = p_transaction_id
398 and msi.plan_id = ms.plan_id
399 and msi.organization_id = ms.organization_id
400 and msi.sr_instance_id = ms.sr_instance_id
401 and msi.inventory_item_id = ms.inventory_item_id
402 and msi.sr_instance_id = mpc.sr_instance_id
403 and msi.buyer_id = mpc.partner_id
404 and mpc.partner_type =4;
405
406 CURSOR supplier_c IS
407 select mpc.name
408 from msc_supplies ms,
409 msc_partner_contacts mpc
410 where ms.plan_id = p_plan_id
411 and ms.transaction_id = p_transaction_id
412 and ms.sr_instance_id = mpc.sr_instance_id
413 and ms.supplier_id = mpc.partner_id
414 and mpc.partner_type =1;
415
416 Cursor detail_attri IS
417 select mp.compile_designator,
418 msc_get_name.item_name(ms.inventory_item_id, null,null,null),
419 msc_get_name.org_code(ms.organization_id,ms.sr_instance_id),
420 DECODE(ms.order_type,5,to_char(ms.transaction_id),ms.order_number),
421 msc_get_name.lookup_meaning('MRP_ORDER_TYPE',ms.order_type),
422 ms.new_schedule_date,
423 ms.implement_date,
424 ms.new_order_quantity
425 from msc_plans mp,
426 msc_supplies ms
427 where ms.plan_id = p_plan_id
428 and ms.transaction_id = p_transaction_id
429 and mp.plan_id = ms.plan_id
430 ;
431
432 BEGIN
433
434 if (funcmode = 'RUN') then
435 p_plan_id :=
436 wf_engine.GetItemAttrNumber( itemtype => itemtype,
437 itemkey => itemkey,
438 aname => 'PLAN_ID');
439
440 p_transaction_id :=
441 wf_engine.GetItemAttrNumber( itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'TRANSACTION_ID');
444
445 p_load_type :=
446 wf_engine.GetItemAttrNumber( itemtype => itemtype,
447 itemkey => itemkey,
448 aname => 'LOAD_TYPE');
449
450 p_dblink :=
451 wf_engine.GetItemAttrText( itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'DBLINK');
454
455 p_instance_code :=
456 wf_engine.GetItemAttrText( itemtype => itemtype,
457 itemkey => itemkey,
458 aname => 'INSTANCE_CODE');
459
460 OPEN buyer_c;
461 FETCH buyer_c INTO l_buyer;
462 CLOSE buyer_c;
463
464
465
466
467 if p_load_type = PURCHASE_ORDER_RESCHEDULE then
468 OPEN supplier_c;
469 FETCH supplier_c INTO l_supplier;
470 CLOSE supplier_c;
471 msc_util.msc_debug('supplier is '||l_supplier);
472 end if;
473 -- l_buyer := 'MFG';
474 -- l_buyer := null;
475 -- l_supplier := 'MFG';
476 -- l_supplier :=null;
477 msc_util.msc_debug('buyer is '||l_buyer);
478 if (p_load_type = PURCHASE_ORDER_RESCHEDULE and
479 l_supplier is not null) or
480 (p_load_type = PURCHASE_REQ_RESCHEDULE and
481 l_buyer is not null) then
482 lv_sql_stmt:=
483 'select mrp_form_query_s.nextval'||p_dblink||
484 ' from dual';
485
486 OPEN poCur FOR lv_sql_stmt;
487 FETCH poCur INTO p_query_id;
488 CLOSE poCur;
489
490 get_supply_data(p_plan_id, p_transaction_id, p_query_id, p_dblink);
491 commit;
492 -- start the workflow in the source instance thru concurrent program
493 lv_sql_stmt:=
494 'BEGIN'
495 ||' mrp_rel_wf.start_workflow_program'||p_dblink||'('
496 ||' :p_process,'
497 ||' :p_resp,'
498 ||' :p_plan_id,'
499 ||' :p_transaction_id,'
500 ||' :p_buyer, '
501 ||' :p_supplier, '
502 ||' :p_query_id, '
503 ||' :p_request_id);'
504 ||' END;';
505
506 if p_load_type = PURCHASE_ORDER_RESCHEDULE then
507 p_process := 'RES_PO_IN_SOURCE';
508 else
509 p_process := 'RES_REQ_IN_SOURCE';
510 end if;
511
512 EXECUTE IMMEDIATE lv_sql_stmt
513 USING
514 IN p_process,
515 IN l_planner,
516 IN p_plan_id,
517 IN p_transaction_id,
518 IN l_buyer,
519 IN l_supplier,
520 IN p_query_id,
521 OUT p_request_id;
522 commit;
523 msc_util.msc_debug('launch concurrent program in the source '||p_instance_code);
524 msc_util.msc_debug('request_id ='||p_request_id);
525 resultout := 'COMPLETE:FOUND';
526
527 else
528
529 open detail_attri;
530 FETCH detail_attri INTO l_plan_name,
531 l_item_name,
532 l_org_code,
533 l_order,
534 l_order_type,
535 l_old_date,
536 l_new_date,
537 l_qty;
538 CLOSE detail_attri;
539
540 msc_util.msc_debug('notify planner no buyer/supplier found');
541 wf_engine.SetItemAttrText( itemtype => itemtype,
542 itemkey => itemkey,
543 aname => 'PLAN_NAME',
544 avalue => l_plan_name);
545
546 wf_engine.SetItemAttrText( itemtype => itemtype,
547 itemkey => itemkey,
548 aname => 'ITEM_NAME',
549 avalue => l_item_name);
550 wf_engine.SetItemAttrText( itemtype => itemtype,
551 itemkey => itemkey,
552 aname => 'PLANNER',
553 avalue => l_planner);
554 wf_engine.SetItemAttrText( itemtype => itemtype,
555 itemkey => itemkey,
556 aname => 'ORG_CODE',
557 avalue => l_org_code);
558
559 wf_engine.SetItemAttrText( itemtype => itemtype,
560 itemkey => itemkey,
561 aname => 'ORDER_NAME',
562 avalue => l_order);
563
564 wf_engine.SetItemAttrText( itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'ORDER_TYPE',
567 avalue => l_order_type);
568
569 wf_engine.SetItemAttrDate( itemtype => itemtype,
570 itemkey => itemkey,
571 aname => 'ORDER_DATE',
572 avalue => l_old_date);
573
574 wf_engine.SetItemAttrDate( itemtype => itemtype,
575 itemkey => itemkey,
576 aname => 'NEW_ORDER_DATE',
577 avalue => l_new_date);
578
579 wf_engine.SetItemAttrNumber( itemtype => itemtype,
580 itemkey => itemkey,
581 aname => 'ORDER_QTY',
582 avalue => l_qty);
583
584 resultout := 'COMPLETE:NOT_FOUND';
585 end if;
586 end if;
587 if (funcmode = 'CANCEL') then
588 resultout := 'COMPLETE:';
589 end if;
590
591 if (funcmode = 'TIMEOUT') then
592 resultout := 'COMPLETE:';
593 end if;
594 END select_buyer_supplier;
595
596 PROCEDURE DeleteActivities( p_item_key varchar2,
597 p_dblink varchar2 default null) IS
598
599 TYPE DelExpType is REF CURSOR;
600 delete_activities_c DelExpType;
601
602 l_item_key varchar2(240);
603 sql_stmt varchar2(500);
604 a number;
605 BEGIN
606
607 sql_stmt := ' SELECT item_key ' ||
608 ' FROM wf_items' || p_dblink ||
609 ' WHERE item_type = :item_type' ||
610 ' AND item_key like '''|| p_item_key || '%''';
611 OPEN delete_activities_c for sql_stmt USING g_item_type;
612 LOOP
613
614 FETCH DELETE_ACTIVITIES_C INTO l_item_key;
615 EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND;
616
617 sql_stmt :=
618 'update wf_notifications' || p_dblink ||
619 ' set end_date = sysdate - 450' ||
620 ' where group_id in ' ||
621 ' (select notification_id' ||
622 ' from wf_item_activity_statuses' ||p_dblink ||
623 ' where item_type = :item_type' ||
624 ' and item_key = :l_item_key' ||
625 ' union' ||
626 ' select notification_id' ||
627 ' from wf_item_activity_statuses_h' || p_dblink ||
628 ' where item_type = :item_type' ||
629 ' and item_key = :l_item_key)';
630
631 EXECUTE IMMEDIATE sql_stmt USING g_item_type,l_item_key,
632 g_item_type,l_item_key;
633
634 sql_stmt :=
635 ' update wf_items' || p_dblink ||
636 ' set end_date = sysdate - 450' ||
637 ' where item_type = :item_type'||
638 ' and item_key = :l_item_key';
639
640 EXECUTE IMMEDIATE sql_stmt USING g_item_type,l_item_key;
641
642 sql_stmt :=
643 ' update wf_item_activity_statuses'|| p_dblink ||
644 ' set end_date = sysdate - 450' ||
645 ' where item_type = :item_type'||
646 ' and item_key = :l_item_key';
647 EXECUTE IMMEDIATE sql_stmt USING g_item_type,l_item_key;
648
649 sql_stmt :=
650 ' update wf_item_activity_statuses_h'|| p_dblink ||
651 ' set end_date = sysdate - 450' ||
652 ' where item_type = :item_type'||
653 ' and item_key = :l_item_key';
654
655 EXECUTE IMMEDIATE sql_stmt USING g_item_type,l_item_key;
656
657 sql_stmt :=
658 'begin wf_purge.total'|| p_dblink||
659 '( :item_type,:l_item_key,sysdate - 450);end;';
660 EXECUTE IMMEDIATE sql_stmt USING g_item_type,l_item_key;
661
662 END LOOP; -- for the itemkey loop
663 CLOSE delete_activities_c;
664
665
666 EXCEPTION
667 when others then
668 msc_util.msc_debug('Error in delete activities:'|| to_char(sqlcode) || ':' || substr(sqlerrm,1,100));
669 return;
670 END DeleteActivities;
671
672 Procedure reset_load_type (p_plan_id number, p_transaction_id number) IS
673 BEGIN
674 UPDATE MSC_SUPPLIES
675 SET implement_date = NULL,
676 release_status = NULL,
677 load_type = NULL
678 WHERE transaction_id= p_transaction_id
679 AND plan_id= p_plan_id;
680 commit;
681 end reset_load_type;
682
683 FUNCTION GET_DOCK_DATE(p_instance_id NUMBER,
684 p_receiving_calendar VARCHAR2,
685 p_delivery_calendar VARCHAR2,
686 p_implement_date DATE,
687 p_lead_time NUMBER,
688 p_plan_id NUMBER,
689 p_organization_id NUMBER) RETURN date IS
690
691
692 dock_date Date;
693 cursor mfg_calendar_code(p_organization_id NUMBER,
694 p_sr_instance_id NUMBER) IS
695 select mtp.calendar_code
696 from msc_trading_partners mtp
697 where mtp.sr_tp_id = p_organization_id
698 and mtp.sr_instance_id = p_sr_instance_id
699 and mtp.partner_type = 3;
700
701 l_mfg_calendar VARCHAR2(30);
702
703 BEGIN
704 -- bug # 10179685
705 -- manufacturing calendar should be used to offset by postporcessing
706 -- lead time
707 open mfg_calendar_code(p_organization_id,
708 p_instance_id);
709 fetch mfg_calendar_code into l_mfg_calendar;
710 close mfg_calendar_code;
711
712 -- dbms_output.put_line(' p_implement_date ' || to_char(p_implement_date, 'MM/DD/YYYY HH24:MI:SS'));
713 --dbms_output.put_line (' l_mfg_calendar = ' || l_mfg_calendar);
714 dock_date := msc_rel_wf.get_offset_date(l_mfg_calendar,
715 p_instance_id,
716 -1*p_lead_time, p_implement_date);
717
718 dock_date := msc_drp_util.get_work_day('PREV',p_receiving_calendar,
719 p_instance_id,dock_date);
720
721 -- then find the working date using receiving calendar,
722 if p_delivery_calendar is not null then
723 dock_date := msc_drp_util.get_work_day('PREV',p_delivery_calendar,
724 p_instance_id,dock_date);
725
726 end if;
727 if dock_date < sysdate then
728 dock_date := sysdate;
729 end if;
730 RETURN(dock_date);
731 END GET_DOCK_DATE;
732
733 PROCEDURE reschedule_purchase_orders
734 ( arg_plan_id IN NUMBER
735 , arg_org_id IN NUMBER
736 , arg_instance IN NUMBER
737 , arg_owning_org IN NUMBER
738 , arg_owning_instance IN NUMBER
739 , arg_count OUT NOCOPY NUMBER
740 , arg_released_instance IN OUT NOCOPY NumTblTyp
741 , arg_po_res_id IN OUT NOCOPY NumTblTyp
742 , arg_po_res_count IN OUT NOCOPY NumTblTyp
743 , arg_po_pwb_count IN OUT NOCOPY NumTblTyp) IS
744
745 p_user_id number := FND_PROFILE.value('USER_ID');
746 p_release_by_user varchar2(3) :=
747 nvl(FND_PROFILE.value('MSC_RELEASED_BY_USER_ONLY'),'N');
748
749 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
750 TYPE DateTab IS TABLE OF Date INDEX BY BINARY_INTEGER;
751 TYPE CharTab IS TABLE OF varchar2(240) INDEX BY BINARY_INTEGER;
752
753 p_new_need_by_date DateTab;
754 p_old_need_by_date DateTab;
755 p_po_line_id NumTab;
756 p_po_header_id NumTab;
757 p_po_number CharTab;
758 p_po_quantity NumTab;
759 p_po_instance_id NumTab;
760 p_plan_id NumTab;
761 p_action NumTab;
762 p_shipment_id NumTab;
763 p_distribution_id NumTab;
764 p_operating_unit numtab;
765 p_dest_dblink varchar2(128);
766
767 -- xml fix
768 p_source_line_id NumTab;
769 p_uom_code CharTab;
770
771 p_timestamp number:=1439/1440;
772 CURSOR instance_cur IS
773 select distinct mp.sr_instance_id,
774 decode(mai.M2A_dblink,null,' ','@'||mai.M2A_dblink),
775 nvl(mai.A2M_dblink, '-1'),
776 mai.instance_code
777 from msc_plan_organizations mp,
778 msc_apps_instances mai
779 where plan_id = arg_plan_id
780 and mp.sr_instance_id = mai.instance_id
781 and mai.instance_type <> 3 -- xml fix
782 and nvl(mai.apps_ver,1) <> 1; -- not back port to 107 yet
783
784 CURSOR leg_instance_cur IS
785 select distinct mp.sr_instance_id
786 from msc_plan_organizations mp,
787 msc_apps_instances mai
788 where plan_id = arg_plan_id
789 and mp.sr_instance_id = mai.instance_id
790 and mai.instance_type = 3; -- xml fix
791
792 p_user_name varchar2(100); -- :=FND_GLOBAL.USER_NAME; --FND_PROFILE.VALUE('USERNAME');
793 p_resp_name varchar2(80); -- :=FND_GLOBAL.RESP_NAME;
794 p_dblink varchar2(128);
795 p_instance_id number;
796 p_instance_code varchar2(3);
797 lv_sql_stmt varchar2(2000);
798 p_request_id number:=0;
799 v_batch_id number;
800 v_temp number;
801 v_temp2 number;
802 v_autorelease number;
803 null_date date := null;
804
805 CURSOR c_plan_type(p_plan_id number) IS
806 select plan_type
807 from msc_plans a
808 where
809 plan_id = p_plan_id;
810
811 p_plan_type NUMBER;
812 BEGIN
813
814 arg_count :=0;
815 p_user_name :=FND_GLOBAL.USER_NAME;
816 p_resp_name :=FND_GLOBAL.RESP_NAME;
817
818 OPEN c_plan_type(arg_plan_id);
819 FETCH c_plan_type INTO p_plan_type;
820 CLOSE c_plan_type;
821 begin -- xml fix
822
823 if (p_plan_type>100) then -- rp plan
824 SELECT s.sr_instance_id,
825 --trunc(nvl(s.promised_date,s.need_by_date)) +p_timestamp old_need_by_date,
826 -- bug 8979681
827 --trunc(nvl(s.promised_date,s.original_need_by_date)) ,
828 nvl(s.promised_date,s.old_need_by_date) ,--bug#13615378
829 trunc(min(get_dock_date(s.sr_instance_id,
830 s.receiving_calendar,
831 s.intransit_calendar,
832 NVL(s.implement_date,s.new_schedule_date),
833 NVL(msi.postprocessing_lead_time,0),
834 arg_plan_id, s.organization_id )))+ p_timestamp new_need_by_date,
835 s.disposition_id po_header_id,
836 s.po_line_id po_line_id,
837 s.order_number po_number,
838 min(s.implement_quantity) qty,
839 s.po_line_location_id shipment_id,
840 s.po_distribution_id distribution_id,
841 nvl(s.implement_uom_code,msi.uom_code) uom,
842 mp.operating_unit operating_unit,
843 s.disposition_status_type action
844 BULK COLLECT INTO
845 p_po_instance_id,
846 p_old_need_by_date,
847 p_new_need_by_date,
848 p_po_header_id,
849 p_po_line_id,
850 p_po_number,
851 p_po_quantity,
852 p_shipment_id,
853 p_distribution_id,
854 p_uom_code,
855 p_operating_unit,
856 p_action
857 FROM msc_apps_instances mai, -- xml fix
858 msc_system_items msi,
859 msc_trading_partners mp,
860 msc_supplies s
861 WHERE msi.inventory_item_id = s.inventory_item_id
862 AND msi.plan_id = s.plan_id
863 AND msi.organization_id = s.organization_id
864 and msi.sr_instance_id = s.sr_instance_id
865 AND mp.sr_tp_id = msi.organization_id
866 AND mp.sr_instance_id = msi.sr_instance_id
867 AND mp.partner_type= 3
868 and mai.instance_id = s.sr_instance_id -- xml fix
869 and mai.instance_type <> 3 -- xml fix- only for non legacy
870 AND s.plan_id = arg_plan_id
871 AND s.release_errors is NULL
872 and s.load_type = 20
873 and s.order_type = 1
874 and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
875 s.last_updated_by)
876 group by s.sr_instance_id,
877 --trunc(nvl(s.promised_date,s.original_need_by_date)) ,
878 nvl(s.promised_date,s.old_need_by_date) ,
879 s.disposition_id,
880 s.po_line_id, s.order_number, s.disposition_status_type,
881 s.po_line_location_id, po_distribution_id,
882 nvl(s.implement_uom_code,msi.uom_code), mp.operating_unit;
883
884
885
886 else
887
888 SELECT s.sr_instance_id,
889 nvl(s.promised_date,s.need_by_date) old_need_by_date,
890 min(get_dock_date(s.sr_instance_id,
891 s.receiving_calendar,
892 s.intransit_calendar,
893 NVL(s.implement_date,s.new_schedule_date),
894 NVL(msi.postprocessing_lead_time,0),
895 arg_plan_id, s.organization_id)) new_need_by_date,
896 s.disposition_id po_header_id,
897 s.po_line_id po_line_id,
898 s.order_number po_number,
899 min(s.implement_quantity) qty,
900 s.po_line_location_id shipment_id,
901 s.po_distribution_id distribution_id,
902 nvl(s.implement_uom_code,msi.uom_code) uom,
903 mp.operating_unit operating_unit,
904 s.disposition_status_type action
905 BULK COLLECT INTO
906 p_po_instance_id,
907 p_old_need_by_date,
908 p_new_need_by_date,
909 p_po_header_id,
910 p_po_line_id,
911 p_po_number,
912 p_po_quantity,
913 p_shipment_id,
914 p_distribution_id,
915 p_uom_code,
916 p_operating_unit,
917 p_action
918 FROM msc_apps_instances mai, -- xml fix
919 msc_system_items msi,
920 msc_trading_partners mp,
921 msc_supplies s
922 WHERE msi.inventory_item_id = s.inventory_item_id
923 AND msi.plan_id = s.plan_id
924 AND msi.organization_id = s.organization_id
925 and msi.sr_instance_id = s.sr_instance_id
926 AND mp.sr_tp_id = msi.organization_id
927 AND mp.sr_instance_id = msi.sr_instance_id
928 AND mp.partner_type= 3
929 and mai.instance_id = s.sr_instance_id -- xml fix
930 and mai.instance_type <> 3 -- xml fix- only for non legacy
931 AND s.plan_id = arg_plan_id
932 AND s.release_errors is NULL
933 and s.load_type = 20
934 and s.order_type = 1
935 and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
936 s.last_updated_by)
937 group by s.sr_instance_id,nvl(s.promised_date,s.need_by_date),
938 s.disposition_id,
939 s.po_line_id, s.order_number, s.disposition_status_type,
940 s.po_line_location_id, po_distribution_id,
941 nvl(s.implement_uom_code,msi.uom_code), mp.operating_unit;
942
943
944 end if;
945 select msc_form_query_s.nextval
946 into v_batch_id
947 from dual;
948 forall a in 1..p_po_instance_id.count
949 insert into msc_purchase_order_interface
950 (last_update_date,
951 last_updated_by,
952 creation_date,
953 created_by,
954 batch_id,
955 sr_instance_id,
956 old_need_by_date,
957 new_need_by_date,
958 po_header_id,
959 po_line_id,
960 po_number,
961 po_quantity,
962 action,
963 po_line_location_id,
964 po_distribution_id,
965 uom,
966 operating_unit)
967 values
968 (sysdate,
969 p_user_id,
970 sysdate,
971 p_user_id,
972 v_batch_id,
973 p_po_instance_id(a),
974 p_old_need_by_date(a),
975 p_new_need_by_date(a),
976 p_po_header_id(a),
977 p_po_line_id(a),
978 p_po_number(a),
979 p_po_quantity(a),
980 p_action(a),
981 p_shipment_id(a),
982 p_distribution_id(a),
983 p_uom_code(a),
984 p_operating_unit(a));
985
986 commit;
987 exception
988 when VALUE_ERROR then
989 null;
990 when COLLECTION_IS_NULL then
991 null;
992 when NO_DATA_FOUND then
993 null;
994 end;
995
996 -- xml fix : for legacy instances insert into msc_po_reschedule_interface
997
998 begin
999 if (p_plan_type>100) then --- rp plan
1000 SELECT s.sr_instance_id,
1001 s.transaction_id, -- xml fix
1002 nvl(s.implement_uom_code,msi.uom_code), -- xml fix
1003 trunc(nvl(s.promised_date,s.need_by_date))+p_timestamp old_need_by_date,
1004 trunc(get_dock_date(s.sr_instance_id,
1005 s.receiving_calendar,
1006 s.intransit_calendar,
1007 NVL(s.implement_date,
1008 s.new_schedule_date),
1009 NVL(msi.postprocessing_lead_time, 0),
1010 arg_plan_id, s.organization_id))+p_timestamp new_need_by_date,
1011 s.disposition_id po_header_id,
1012 s.po_line_id po_line_id,
1013 s.order_number po_number,
1014 min(s.implement_quantity) qty,
1015 s.plan_id
1016 BULK COLLECT INTO
1017 p_po_instance_id,
1018 p_source_line_id, -- xml fix
1019 p_uom_code, -- xml fix
1020 p_old_need_by_date,
1021 p_new_need_by_date,
1022 p_po_header_id,
1023 p_po_line_id,
1024 p_po_number,
1025 p_po_quantity,
1026 p_plan_id
1027 FROM msc_apps_instances mai,
1028 msc_system_items msi,
1029 msc_supplies s
1030 WHERE msi.inventory_item_id = s.inventory_item_id
1031 AND msi.plan_id = s.plan_id
1032 AND msi.organization_id = s.organization_id
1033 and msi.sr_instance_id = s.sr_instance_id
1034 and mai.instance_id = s.sr_instance_id -- xml fix
1035 and mai.instance_type = 3 -- xml fix
1036 AND s.plan_id = arg_plan_id
1037 AND s.release_errors is NULL
1038 and s.load_type = 20
1039 and s.order_type = 1
1040 and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1041 s.last_updated_by)
1042 group by s.sr_instance_id, s.transaction_id,
1043 nvl(s.implement_uom_code,msi.uom_code),
1044 trunc(nvl(s.promised_date,s.need_by_date))+p_timestamp,
1045 trunc(get_dock_date(s.sr_instance_id,
1046 s.receiving_calendar,
1047 s.intransit_calendar,
1048 NVL(s.implement_date,
1049 s.new_schedule_date),
1050 NVL(msi.postprocessing_lead_time, 0),
1051 arg_plan_id, s.organization_id))+p_timestamp,
1052 s.disposition_id,s.po_line_id, s.order_number,s.plan_id;
1053
1054
1055
1056 else
1057
1058 SELECT s.sr_instance_id,
1059 s.transaction_id, -- xml fix
1060 nvl(s.implement_uom_code,msi.uom_code), -- xml fix
1061 nvl(s.promised_date,s.need_by_date) old_need_by_date,
1062 get_dock_date(s.sr_instance_id,
1063 s.receiving_calendar,
1064 s.intransit_calendar,
1065 NVL(s.implement_date,
1066 s.new_schedule_date),
1067 NVL(msi.postprocessing_lead_time, 0),
1068 arg_plan_id, s.organization_id) new_need_by_date,
1069 s.disposition_id po_header_id,
1070 s.po_line_id po_line_id,
1071 s.order_number po_number,
1072 min(s.implement_quantity) qty,
1073 s.plan_id
1074 BULK COLLECT INTO
1075 p_po_instance_id,
1076 p_source_line_id, -- xml fix
1077 p_uom_code, -- xml fix
1078 p_old_need_by_date,
1079 p_new_need_by_date,
1080 p_po_header_id,
1081 p_po_line_id,
1082 p_po_number,
1083 p_po_quantity,
1084 p_plan_id
1085 FROM msc_apps_instances mai,
1086 msc_system_items msi,
1087 msc_supplies s
1088 WHERE msi.inventory_item_id = s.inventory_item_id
1089 AND msi.plan_id = s.plan_id
1090 AND msi.organization_id = s.organization_id
1091 and msi.sr_instance_id = s.sr_instance_id
1092 and mai.instance_id = s.sr_instance_id -- xml fix
1093 and mai.instance_type = 3 -- xml fix
1094 AND s.plan_id = arg_plan_id
1095 AND s.release_errors is NULL
1096 and s.load_type = 20
1097 and s.order_type = 1
1098 and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1099 s.last_updated_by)
1100 group by s.sr_instance_id, s.transaction_id,
1101 nvl(s.implement_uom_code,msi.uom_code),
1102 nvl(s.promised_date,s.need_by_date),
1103 get_dock_date(s.sr_instance_id,
1104 s.receiving_calendar,
1105 s.intransit_calendar,
1106 NVL(s.implement_date,
1107 s.new_schedule_date),
1108 NVL(msi.postprocessing_lead_time, 0),
1109 arg_plan_id, s.organization_id),
1110 s.disposition_id,s.po_line_id, s.order_number,s.plan_id;
1111 end if;
1112
1113 forall a in 1..p_po_instance_id.count
1114 INSERT INTO msc_po_reschedule_interface
1115 (process_id,
1116 quantity,
1117 need_by_date,
1118 line_id,
1119 last_update_date,
1120 last_updated_by,
1121 creation_date,
1122 created_by,
1123 purchase_order_id,
1124 po_number,
1125 source_line_id,
1126 uom,
1127 SR_INSTANCE_ID,
1128 plan_id)
1129 VALUES (
1130 NULL,
1131 p_po_quantity(a),
1132 p_new_need_by_date(a),
1133 p_po_line_id(a),
1134 SYSDATE,
1135 p_user_id,
1136 SYSDATE,
1137 p_user_id,
1138 p_po_header_id(a),
1139 p_po_number(a),
1140 p_source_line_id(a),
1141 p_uom_code(a),
1142 p_po_instance_id(a),
1143 p_plan_id(a));
1144
1145 commit;
1146
1147 -- send xml
1148 for cur in leg_instance_cur loop
1149
1150 v_temp :=0;
1151
1152 select count(*)
1153 into v_temp
1154 from msc_po_reschedule_interface
1155 where sr_instance_id = cur.sr_instance_id;
1156
1157 if v_temp > 0 then
1158 arg_po_res_id.extend(1);
1159 arg_released_instance.extend(1);
1160 arg_po_res_count.extend(1);
1161 arg_po_pwb_count.extend(1);
1162 arg_count := arg_count+1;
1163
1164 lv_sql_stmt :=
1165 ' BEGIN'
1166 ||' MSC_A2A_XML_WF.LEGACY_RELEASE (:p_arg_org_instance);'
1167 ||' END;';
1168
1169 EXECUTE IMMEDIATE lv_sql_stmt USING cur.sr_instance_id;
1170
1171 arg_po_res_id(arg_count) := 0;
1172 arg_released_instance(arg_count) := cur.sr_instance_id;
1173 arg_po_res_count(arg_count) := v_temp;
1174
1175 select count(*)
1176 into v_temp2
1177 from msc_supplies
1178 where plan_id = arg_plan_id
1179 anD release_errors is NULL
1180 and load_type = 20
1181 and order_type = 1
1182 and sr_instance_id = cur.sr_instance_id
1183 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1184 last_updated_by);
1185
1186 arg_po_pwb_count(arg_count) := v_temp2;
1187
1188 end if;
1189 end loop;
1190
1191 exception
1192 when VALUE_ERROR then
1193 null;
1194 when COLLECTION_IS_NULL then
1195 null;
1196 when NO_DATA_FOUND then
1197 null;
1198 end;
1199 OPEN instance_cur;
1200 LOOP
1201 FETCH instance_cur INTO p_instance_id, p_dblink, p_dest_dblink,p_instance_code;
1202 EXIT WHEN instance_cur%NOTFOUND;
1203 v_temp :=0;
1204 select count(*)
1205 into v_temp
1206 from msc_purchase_order_interface
1207 where sr_instance_id = p_instance_id
1208 and batch_id = v_batch_id;
1209 if v_temp > 0 then
1210 arg_po_res_id.extend(1);
1211 arg_released_instance.extend(1);
1212 arg_po_res_count.extend(1);
1213 arg_po_pwb_count.extend(1);
1214 arg_count := arg_count+1;
1215
1216 IF p_plan_type = 5 THEN
1217 begin
1218 lv_sql_stmt :=
1219 'BEGIN ' ||
1220 'MRP_PO_RESCHEDULE.LAUNCH_RESCHEDULE_PO'||p_dblink||
1221 '(:lv_user, :lv_resp, :v_batch_id,:p_instance_id,:p_instance_code,:p_dest_dblink, :arg_req_resched_id); ' ||
1222 'END;';
1223 EXECUTE IMMEDIATE lv_sql_stmt
1224 USING
1225 IN p_user_name,
1226 IN 'Advanced Supply Chain Planner',
1227 --IN p_resp_name,
1228 IN v_batch_id,
1229 IN p_instance_id,
1230 IN p_instance_code,
1231 IN p_dest_dblink,
1232 OUT p_request_id;
1233 EXCEPTION WHEN OTHERS THEN
1234 p_request_id := 0;
1235 END;
1236 ELSE
1237 begin
1238 lv_sql_stmt:=
1239 'BEGIN'
1240 ||' mrp_rel_wf.launch_po_program'||p_dblink||'('
1241 ||' :lv_old_need_by_date,'
1242 ||' :lv_new_need_by_date,'
1243 ||' :lv_po_header_id,'
1244 ||' :lv_po_line_id,'
1245 ||' :lv_po_number,'
1246 ||' :lv_user,'
1247 ||' :lv_resp,'
1248 ||' :lv_qty,'
1249 ||' :out);'
1250 ||' END;';
1251 EXECUTE IMMEDIATE lv_sql_stmt
1252 USING
1253 IN null_date,
1254 IN null_date,
1255 IN v_batch_id,
1256 IN p_instance_id,
1257 IN p_dest_dblink,
1258 IN p_user_name,
1259 IN p_resp_name,
1260 IN 1,
1261 OUT p_request_id;
1262 -- dbms_output.put_line(p_dest_dblink||','||p_instance_id||','||v_batch_id);
1263 exception when others then
1264 EXECUTE IMMEDIATE lv_sql_stmt
1265 USING
1266 IN sysdate,
1267 IN sysdate,
1268 IN v_batch_id,
1269 IN p_instance_id,
1270 IN p_dest_dblink,
1271 IN p_user_name,
1272 IN p_resp_name,
1273 IN 1,
1274 OUT p_request_id;
1275 end;
1276 END IF;
1277 if p_request_id <> 0 then
1278 commit;
1279 end if;
1280
1281 arg_po_res_id(arg_count) := p_request_id;
1282 arg_released_instance(arg_count) := p_instance_id;
1283 arg_po_res_count(arg_count) := v_temp;
1284
1285 select count(*)
1286 into v_temp2
1287 from msc_supplies
1288 where plan_id = arg_plan_id
1289 AND release_errors is NULL
1290 and load_type = 20
1291 and order_type = 1
1292 and sr_instance_id = p_instance_id
1293 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1294 last_updated_by);
1295
1296 arg_po_pwb_count(arg_count) := v_temp2;
1297
1298 end if;
1299 END LOOP;
1300 CLOSE instance_cur;
1301
1302 /* for auto-release we do not want applied and status to be updated */
1303
1304 SELECT release_reschedules
1305 INTO v_autorelease
1306 FROM msc_plans
1307 WHERE plan_id=arg_plan_id;
1308 UPDATE MSC_SUPPLIES
1309 SET implement_date = NULL,
1310 release_status = decode(sign(p_plan_type-100),1,
1311 decode(release_status,11,21,
1312 12,22,
1313 13,23),
1314 NULL),
1315 load_type = NULL,
1316 applied = decode(v_autorelease,1,applied,2),
1317 status = decode(v_autorelease,1,status,0)
1318 WHERE plan_id= arg_plan_id
1319 and release_errors is NULL
1320 and load_type = 20
1321 and order_type =1
1322 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1323 last_updated_by);
1324
1325 commit;
1326
1327 END reschedule_purchase_orders;
1328
1329 PROCEDURE release_sales_orders
1330 ( arg_plan_id IN NUMBER
1331 , arg_org_id IN NUMBER
1332 , arg_instance IN NUMBER
1333 , arg_owning_org IN NUMBER
1334 , arg_owning_instance IN NUMBER
1335 , arg_released_instance IN OUT NOCOPY NumTblTyp
1336 , arg_so_rel_id IN OUT NOCOPY NumTblTyp
1337 , arg_so_rel_count IN OUT NOCOPY NumTblTyp
1338 , arg_so_pwb_count IN OUT NOCOPY NumTblTyp) IS
1339
1340 -- p_user_id number := FND_PROFILE.value('USER_ID');
1341 p_user_id number := FND_GLOBAL.USER_ID;
1342 p_resp_id number := FND_GLOBAL.RESP_ID;
1343 p_release_by_user varchar2(3) :=
1344 nvl(FND_PROFILE.value('MSC_RELEASED_BY_USER_ONLY'),'N');
1345
1346 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1347 TYPE DateTab IS TABLE OF Date INDEX BY BINARY_INTEGER;
1348 TYPE CharTab IS TABLE OF varchar2(240) INDEX BY BINARY_INTEGER;
1349
1350 p_earliest_ship_date DateTab;
1351 p_ship_date DateTab;
1352 p_arrival_date datetab;
1353 p_ship_method CharTab;
1354 p_lead_time numTab;
1355 p_operating_unit NumTab;
1356 p_so_line_id NumTab;
1357 p_so_org_id NumTab;
1358 p_so_instance_id NumTab;
1359 p_instance_id NumTab;
1360 p_so_header_id NumTab;
1361 p_demand_id NumTab;
1362 p_order_number CharTab;
1363 p_source_type NumTab;
1364 p_orig_ship_date DateTab;
1365 p_orig_arrival_date datetab;
1366 p_orig_ship_method CharTab;
1367 p_orig_lead_time numTab;
1368 p_orig_org_id NumTab;
1369 p_qty NumTab;
1370 p_implement_firm NumTab;
1371 p_original_item_id NumTab;
1372 p_substitute_item_id NumTab;
1373 p_org_id NumTab;
1374 a number;
1375
1376 CURSOR instance_cur IS
1377 select distinct mp.sr_instance_id,
1378 decode(mai.M2A_dblink,null,' ','@'||mai.M2A_dblink),
1379 decode(mai.A2M_dblink,null,' ','@'||mai.A2M_dblink)
1380 from msc_plan_organizations mp,
1381 msc_apps_instances mai
1382 where plan_id = arg_plan_id
1383 and mp.sr_instance_id = mai.instance_id
1384 -- and mai.instance_type <> 3 -- xml fix
1385 and nvl(mai.apps_ver,1) <> 1; -- not back port to 107 yet
1386
1387 -- p_user_name varchar2(30) :=FND_PROFILE.VALUE('USERNAME');
1388 p_user_name varchar2(80) :=FND_GLOBAL.USER_NAME;
1389 p_resp_name varchar2(80) :=FND_GLOBAL.RESP_NAME;
1390 p_dblink varchar2(128);
1391 p_aps_dblink varchar2(128);
1392 p_inst_id number;
1393 lv_sql_stmt varchar2(2000);
1394 p_request_id number:=0;
1395 v_batch_id number;
1396 v_temp number;
1397 v_temp2 number;
1398 crm_char varchar2(100);
1399 TYPE CRMCurTyp IS REF CURSOR;
1400 crm_cur CRMCurTyp;
1401 p_count number;
1402 v_item_id number;
1403 p_timestamp number:=1439/1440; --- (1-1/(24*60))
1404
1405 CURSOR source_item_c(l_inst_id number, l_org_id number,
1406 l_item_id number) IS
1407 select sr_inventory_item_id
1408 from msc_system_items msi
1409 where msi.plan_id = arg_plan_id
1410 and msi.organization_id =l_org_id
1411 and msi.sr_instance_id = l_inst_id
1412 and msi.inventory_item_id = l_item_id;
1413
1414 CURSOR c_plan_type(p_plan_id number) IS
1415 select plan_type
1416 from msc_plans a
1417 where
1418 plan_id = p_plan_id;
1419 p_plan_type NUMBER;
1420 BEGIN
1421 OPEN c_plan_type(arg_plan_id);
1422 FETCH c_plan_type INTO p_plan_type;
1423 CLOSE c_plan_type;
1424 p_count :=0;
1425
1426 begin
1427 if (p_plan_type >100) then -- this is rp plans
1428 SELECT md.sr_instance_id,
1429 NVL(md.sales_order_line_id,0),
1430 md.implement_org_id,
1431 md.implement_instance_id,
1432 trunc(md.implement_earliest_date) + p_timestamp, -- Earliest ship date
1433 trunc(md.implement_ship_date)+p_timestamp,
1434 trunc(nvl(md.implement_arrival_date,md.schedule_ship_date)) +p_timestamp,
1435 mtp.operating_unit,
1436 md.demand_id,
1437 md.ship_method,
1438 NVL(md.intransit_lead_time,0),
1439 nvl(md.implement_firm,2),
1440 NVL(nvl(md.prev_subst_org,md.original_org_id),
1441 md.organization_id),
1442 trunc(md.schedule_arrival_date) + p_timestamp,
1443 trunc(md.schedule_ship_date)+p_timestamp,
1444 md.orig_shipping_method_code,
1445 NVL(md.orig_intransit_lead_time,0),
1446 md.order_number,
1447 decode(md.customer_id, null, 100, to_number(null)),
1448 decode(md.customer_id, null,
1449 nvl(md.quantity_by_due_date,
1450 md.using_requirement_quantity),
1451 md.using_requirement_quantity),
1452 nvl(nvl(md.prev_subst_item,md.original_item_id),
1453 md.inventory_item_id),
1454 md.inventory_item_id,
1455 md.organization_id
1456 BULK COLLECT INTO
1457 p_instance_id,
1458 p_so_line_id,
1459 p_so_org_id,
1460 p_so_instance_id,
1461 p_earliest_ship_date,
1462 p_ship_date,
1463 p_arrival_date,
1464 p_operating_unit,
1465 p_demand_id,
1466 p_ship_method,
1467 p_lead_time,
1468 p_implement_firm,
1469 p_orig_org_id,
1470 p_orig_arrival_date,
1471 p_orig_ship_date,
1472 p_orig_ship_method,
1473 p_orig_lead_time,
1474 p_order_number,
1475 p_source_type,
1476 p_qty,
1477 p_original_item_id,
1478 p_substitute_item_id,
1479 p_org_id
1480 FROM msc_demands md,
1481 msc_trading_partners mtp
1482 WHERE md.plan_id = arg_plan_id
1483 AND md.release_errors is NULL
1484 and md.load_type = 30
1485 and md.origination_type = 30
1486 and md.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1487 md.last_updated_by)
1488 AND mtp.sr_tp_id = md.organization_id
1489 AND mtp.sr_instance_id = md.sr_instance_id
1490 AND mtp.partner_type= 3
1491 order by mtp.operating_unit, md.order_number, md.arrival_set_id,md.ship_set_id;
1492
1493 else
1494
1495 SELECT md.sr_instance_id,
1496 NVL(md.sales_order_line_id,0),
1497 md.implement_org_id,
1498 md.implement_instance_id,
1499 md.implement_earliest_date, -- Earliest ship date
1500 md.implement_ship_date,
1501 nvl(md.implement_arrival_date,md.schedule_ship_date),
1502 mtp.operating_unit,
1503 md.demand_id,
1504 md.ship_method,
1505 NVL(md.intransit_lead_time,0),
1506 nvl(md.implement_firm,2),
1507 NVL(nvl(md.prev_subst_org,md.original_org_id),
1508 md.organization_id),
1509 md.schedule_arrival_date,
1510 md.schedule_ship_date,
1511 md.orig_shipping_method_code,
1512 NVL(md.orig_intransit_lead_time,0),
1513 md.order_number,
1514 decode(md.customer_id, null, 100, to_number(null)),
1515 decode(md.customer_id, null,
1516 nvl(md.quantity_by_due_date,
1517 md.using_requirement_quantity),
1518 md.using_requirement_quantity),
1519 nvl(nvl(md.prev_subst_item,md.original_item_id),
1520 md.inventory_item_id),
1521 md.inventory_item_id,
1522 md.organization_id
1523 BULK COLLECT INTO
1524 p_instance_id,
1525 p_so_line_id,
1526 p_so_org_id,
1527 p_so_instance_id,
1528 p_earliest_ship_date,
1529 p_ship_date,
1530 p_arrival_date,
1531 p_operating_unit,
1532 p_demand_id,
1533 p_ship_method,
1534 p_lead_time,
1535 p_implement_firm,
1536 p_orig_org_id,
1537 p_orig_arrival_date,
1538 p_orig_ship_date,
1539 p_orig_ship_method,
1540 p_orig_lead_time,
1541 p_order_number,
1542 p_source_type,
1543 p_qty,
1544 p_original_item_id,
1545 p_substitute_item_id,
1546 p_org_id
1547 FROM msc_demands md,
1548 msc_trading_partners mtp
1549 WHERE md.plan_id = arg_plan_id
1550 AND md.release_errors is NULL
1551 and md.load_type = 30
1552 and md.origination_type = 30
1553 and md.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1554 md.last_updated_by)
1555 AND mtp.sr_tp_id = md.organization_id
1556 AND mtp.sr_instance_id = md.sr_instance_id
1557 AND mtp.partner_type= 3
1558 order by mtp.operating_unit, md.order_number, md.arrival_set_id,md.ship_set_id;
1559 end if;
1560 select msc_form_query_s.nextval
1561 into v_batch_id
1562 from dual;
1563
1564 for a in 1..p_so_instance_id.count loop
1565
1566 v_item_id := p_substitute_item_id(a);
1567 OPEN source_item_c(p_instance_id(a), p_org_id(a),v_item_id);
1568 FETCH source_item_c INTO p_substitute_item_id(a);
1569 CLOSE source_item_c;
1570
1571 IF p_original_item_id(a) <> v_item_id THEN
1572 v_item_id := p_original_item_id(a);
1573 OPEN source_item_c(p_instance_id(a), p_orig_org_id(a),v_item_id);
1574 FETCH source_item_c INTO p_original_item_id(a);
1575 CLOSE source_item_c;
1576 ELSE
1577 p_original_item_id(a) := p_substitute_item_id(a);
1578 END IF;
1579
1580 insert into msc_sales_order_interface
1581 (last_update_date,
1582 last_updated_by,
1583 creation_date,
1584 created_by,
1585 batch_id,
1586 sr_instance_id,
1587 line_id,
1588 operating_unit,
1589 header_id,
1590 org_id,
1591 schedule_ship_date,
1592 schedule_arrival_date,
1593 earliest_ship_date,
1594 delivery_lead_time,
1595 ship_method,
1596 orig_org_id,
1597 orig_schedule_ship_date,
1598 orig_schedule_arrival_date,
1599 orig_lead_time,
1600 orig_ship_method,
1601 quantity,
1602 firm_flag,
1603 source_type,
1604 order_number,
1605 demand_id,
1606 plan_id,
1607 orig_item_id,
1608 inventory_item_id)
1609 values
1610 (sysdate,
1611 p_user_id,
1612 sysdate,
1613 p_user_id,
1614 v_batch_id,
1615 p_instance_id(a),
1616 p_so_line_id(a),
1617 p_operating_unit(a),
1618 null, -- so_header_id: we don't hv this in destination
1619 p_so_org_id(a),
1620 p_ship_date(a),
1621 p_arrival_date(a),
1622 p_earliest_ship_date(a),
1623 p_lead_time(a),
1624 p_ship_method(a),
1625 p_orig_org_id(a),
1626 p_orig_ship_date(a),
1627 p_orig_arrival_date(a),
1628 p_orig_lead_time(a),
1629 p_orig_ship_method(a),
1630 p_qty(a),
1631 p_implement_firm(a),
1632 p_source_type(a),
1633 p_order_number(a),
1634 p_demand_id(a),
1635 arg_plan_id,
1636 p_original_item_id(a),
1637 p_substitute_item_id(a));
1638
1639 end loop;
1640
1641 commit;
1642 exception
1643 when VALUE_ERROR then
1644 null;
1645 when COLLECTION_IS_NULL then
1646 null;
1647 when NO_DATA_FOUND then
1648 null;
1649 end;
1650
1651 OPEN instance_cur;
1652 LOOP
1653 FETCH instance_cur INTO p_inst_id, p_dblink, p_aps_dblink;
1654 EXIT WHEN instance_cur%NOTFOUND;
1655 v_temp :=0;
1656 select count(*)
1657 into v_temp
1658 from msc_sales_order_interface
1659 where sr_instance_id = p_inst_id
1660 and batch_id = v_batch_id;
1661 if v_temp > 0 then
1662 arg_so_rel_id.extend(1);
1663 arg_released_instance.extend(1);
1664 arg_so_rel_count.extend(1);
1665 arg_so_pwb_count.extend(1);
1666 p_count := p_count+1;
1667
1668 begin
1669 -- customer_id could be null for reqular so,
1670 -- but order_number for CRMO always ends with 'CMRO'
1671 lv_sql_stmt:= 'select meaning from fnd_lookups'||p_dblink||
1672 ' where lookup_type = :p_type '||
1673 ' and lookup_code = :p_code ';
1674
1675 OPEN crm_cur FOR lv_sql_stmt USING 'AHL_APS_APPLICATION', 'CMRO';
1676 FETCH crm_cur INTO crm_char;
1677 CLOSE crm_cur;
1678
1679 -- if customer_id is null but order_number not ends with 'CMRO',
1680 -- it is not a CMRO
1681 UPDATE msc_sales_order_interface
1682 set source_type = null,
1683 quantity = null
1684 where source_type =100 -- customer_id is null
1685 and order_number not like '%'||crm_char
1686 and sr_instance_id = p_inst_id
1687 and batch_id = v_batch_id;
1688 commit;
1689
1690 lv_sql_stmt:=
1691 'BEGIN'
1692 ||' mrp_rel_wf.launch_so_program'||p_dblink||'('
1693 ||' :batch_id,'
1694 ||' :db_link,'
1695 ||' :instance_id,'
1696 ||' :lv_user,'
1697 ||' :lv_resp,'
1698 ||' :out);'
1699 ||' END;';
1700
1701 MSC_LOG.string ( FND_LOG.LEVEL_ERROR,
1702 'msc_rel_wf.release_sales_orders',
1703 'sql statement is : '||lv_sql_stmt);
1704
1705
1706 EXECUTE IMMEDIATE lv_sql_stmt
1707 USING
1708 IN v_batch_id,
1709 IN p_aps_dblink,
1710 IN p_inst_id,
1711 IN p_user_name,
1712 IN p_resp_name,
1713 OUT p_request_id;
1714 exception when others then
1715 raise;
1716 end;
1717 if p_request_id <> 0 then
1718 commit;
1719 end if;
1720
1721 arg_so_rel_id(p_count) := p_request_id;
1722 arg_released_instance(p_count) := p_inst_id;
1723 arg_so_rel_count(p_count) := v_temp;
1724
1725 v_temp2 :=0;
1726
1727 select count(*)
1728 into v_temp2
1729 from msc_demands
1730 where plan_id = arg_plan_id
1731 anD release_errors is NULL
1732 and load_type = 30
1733 and sr_instance_id = p_inst_id
1734 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1735 last_updated_by);
1736
1737 arg_so_pwb_count(p_count) := v_temp2;
1738
1739 end if; -- if v_temp >0 then
1740 END LOOP;
1741 CLOSE instance_cur;
1742
1743 UPDATE MSC_DEMANDS
1744 SET /* implement_date = NULL,
1745 implement_ship_date = NULL,
1746 implement_earliest_date = NULL,
1747 implement_arrival_date = NULL,
1748 implement_org_id = NULL,
1749 implement_instance_id = NULL,
1750 implement_firm = null, */
1751 release_status = decode(sign(p_plan_type-100),1,
1752 decode(release_status,11,21,
1753 12,22,
1754 13,23),
1755 NULL),
1756 --- release_status = NULL,
1757 load_type = NULL,
1758 applied = 2,
1759 status =0
1760 WHERE plan_id= arg_plan_id
1761 and release_errors is NULL
1762 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1763 last_updated_by)
1764 and load_type = 30;
1765 commit;
1766
1767 END release_sales_orders;
1768
1769 Function get_job_seq_from_source(p_instance_id number) RETURN number IS
1770 CURSOR db_cur IS
1771 select decode(M2A_dblink,null,' ','@'||M2A_dblink)
1772 from msc_apps_instances
1773 where instance_id = p_instance_id;
1774
1775 db_link varchar2(128);
1776 seq_num number;
1777 TYPE JobCurTyp IS REF CURSOR;
1778 JobCur JobCurTyp;
1779
1780 sql_stmt varchar2(200);
1781 BEGIN
1782 OPEN db_cur;
1783 FETCH db_cur INTO db_link;
1784 CLOSE db_cur;
1785
1786 sql_stmt := 'SELECT wip_job_number_s.nextval'||db_link||' from dual';
1787
1788 OPEN JobCur FOR sql_stmt;
1789 FETCH JobCur INTO seq_num;
1790 CLOSE JobCur;
1791
1792 return seq_num;
1793
1794 END get_job_seq_from_source;
1795
1796 FUNCTION is_pjm_valid(p_org_id NUMBER,
1797 p_project_id NUMBER,
1798 p_task_id NUMBER,
1799 p_start_date DATE,
1800 p_completion_date DATE,
1801 p_instance_id NUMBER) RETURN NUMBER IS
1802
1803 l_valid varchar2(80):= 'S';
1804 l_error varchar2(1000) := NULL;
1805
1806
1807 db_link varchar2(128);
1808
1809 sql_stmt varchar2(500);
1810
1811
1812 BEGIN
1813 msc_rel_wf.validate_proj_in_source( p_org_id,
1814 p_project_id,
1815 p_task_id,
1816 p_start_date,
1817 null, -- no date for completion date
1818 p_instance_id,
1819 l_valid,
1820 l_error);
1821
1822
1823
1824 IF l_valid = 'S' then
1825 return 1;
1826 ELSE
1827 return 0;
1828 END IF;
1829
1830 END is_pjm_valid;
1831
1832
1833
1834
1835 -- cnazarma
1836 PROCEDURE validate_proj_in_source(
1837 p_org_id NUMBER,
1838 p_project_id NUMBER,
1839 p_task_id NUMBER,
1840 p_start_date DATE,
1841 p_completion_date DATE,
1842 p_instance_id NUMBER,
1843 p_valid OUT NOCOPY VARCHAR2,
1844 p_error OUT NOCOPY VARCHAR2) IS
1845
1846
1847
1848 --l_user_name varchar2(30) :=FND_PROFILE.VALUE('USERNAME');
1849 l_user_name varchar2(30) := FND_GLOBAL.USER_NAME;
1850
1851
1852 CURSOR db_cur IS
1853 select decode(M2A_dblink,null,' ','@'||M2A_dblink),
1854 apps_ver
1855 from msc_apps_instances
1856 where instance_id = p_instance_id;
1857
1858 db_link varchar2(128);
1859 apps_version number;
1860
1861 sql_stmt varchar2(500);
1862 v_valid VARCHAR2(80);
1863
1864 l_user_id number := FND_GLOBAL.USER_ID;
1865 l_resp_id number := FND_GLOBAL.RESP_ID;
1866 l_application_id number;
1867
1868 BEGIN
1869
1870
1871
1872 OPEN db_cur;
1873 FETCH db_cur INTO db_link, apps_version;
1874 CLOSE db_cur;
1875
1876 -- remember the initial context
1877 -- because validate_pjm will change the context to PJM oper unit
1878 SELECT APPLICATION_ID
1879 INTO l_application_id
1880 FROM FND_APPLICATION_VL
1881 WHERE APPLICATION_SHORT_NAME = 'MSC'
1882 and rownum =1 ;
1883
1884 -- calling validate_pjm for 11.5 sources only
1885 if apps_version > 2 then
1886 sql_stmt :=
1887 'BEGIN mrp_rel_wf.validate_pjm'||db_link||
1888 '( :p_org,'||
1889 ' :p_project_id,'||
1890 ' :p_task_id,' ||
1891 ' :p_start_date,'||
1892 ' :p_completion_date,' ||
1893 ' :p_user_name,'||
1894 ' :p_valid,'||
1895 ' :p_error ); END; ';
1896 EXECUTE IMMEDIATE sql_stmt USING
1897 IN p_org_id,
1898 IN p_project_id,
1899 IN p_task_Id,
1900 IN p_start_date,
1901 IN p_completion_date,
1902 IN l_user_name,
1903 IN OUT p_valid,
1904 IN OUT p_error;
1905
1906 else
1907 p_valid := 'S';
1908 end if;
1909
1910 -- initialize context back to what it was initially
1911 fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
1912
1913
1914 exception when others then
1915 raise;
1916
1917 END validate_proj_in_source;
1918
1919
1920 FUNCTION get_acc_class_from_source(p_org_id number, p_item_id number,
1921 p_project_id number, p_instance_id number) RETURN varchar2 IS
1922 CURSOR db_cur IS
1923 select decode(M2A_dblink,null,' ','@'||M2A_dblink),apps_ver
1924
1925 from msc_apps_instances
1926 where instance_id = p_instance_id;
1927
1928 db_link varchar2(128);
1929
1930 v_err_mesg1 VARCHAR2(200);
1931 v_err_class1 VARCHAR2(200);
1932 v_err_mesg2 VARCHAR2(200);
1933 v_err_class2 VARCHAR2(200);
1934 v_default_acc_class varchar2(200);
1935 sql_stmt varchar2(500);
1936 v_apps_ver number;
1937 BEGIN
1938
1939 OPEN db_cur;
1940 FETCH db_cur INTO db_link,v_apps_ver;
1941 CLOSE db_cur;
1942
1943 IF v_apps_ver <> 1 THEN
1944 sql_stmt :=
1945 'BEGIN :v_class := wip_common.default_acc_class'||db_link||
1946 '( :p_org, :p_item, 1, :p_project,' ||
1947 ' :p_err_msg_1, :p_err_class_1,' ||
1948 ' :p_err_msg_2, :p_err_class_2); END; ';
1949
1950 EXECUTE IMMEDIATE sql_stmt USING
1951 OUT v_default_acc_class,
1952 IN p_org_id,
1953 IN p_item_id,
1954 IN p_project_Id,
1955 OUT v_err_mesg1,
1956 OUT v_err_class1,
1957 OUT v_err_mesg2,
1958 OUT v_err_class2;
1959 ELSE
1960 sql_stmt := 'SELECT DEFAULT_DISCRETE_CLASS
1961 FROM WIP_PARAMETERS'||db_link||
1962 ' WHERE ORGANIZATION_ID = :p_org_id';
1963
1964 EXECUTE IMMEDIATE sql_stmt INTO v_default_acc_class USING p_org_id ;
1965 END IF;
1966
1967 return v_default_acc_class;
1968 exception when others then
1969 return null;
1970 END get_acc_class_from_source;
1971
1972 Function is_source_db_up(p_instance_id number) RETURN boolean IS
1973 CURSOR db_cur IS
1974 select decode(M2A_dblink,null,' ','@'||M2A_dblink)
1975 from msc_apps_instances
1976 where instance_id = p_instance_id;
1977
1978 db_link varchar2(128);
1979 seq_num number;
1980 TYPE JobCurTyp IS REF CURSOR;
1981 JobCur JobCurTyp;
1982
1983 sql_stmt varchar2(200);
1984 BEGIN
1985 OPEN db_cur;
1986 FETCH db_cur INTO db_link;
1987 CLOSE db_cur;
1988
1989 sql_stmt := 'SELECT 1 from dual'||db_link;
1990
1991 OPEN JobCur FOR sql_stmt;
1992 FETCH JobCur INTO seq_num;
1993 CLOSE JobCur;
1994
1995 return true;
1996 exception when others then
1997 --attempt to run the same query over db link again.
1998 begin
1999 OPEN db_cur;
2000 FETCH db_cur INTO db_link;
2001 CLOSE db_cur;
2002
2003 sql_stmt := 'SELECT 1 from dual'||db_link;
2004
2005 OPEN JobCur FOR sql_stmt;
2006 FETCH JobCur INTO seq_num;
2007 CLOSE JobCur;
2008
2009 return true;
2010
2011 exception when others then
2012 return false;
2013 end;
2014 --return false;
2015 END is_source_db_up;
2016
2017 Procedure get_load_type(itemtype in varchar2,
2018 itemkey in varchar2,
2019 actid in number,
2020 funcmode in varchar2,
2021 resultout out NOCOPY varchar2 ) IS
2022 p_load_type number;
2023 BEGIN
2024 if (funcmode = 'RUN') then
2025 p_load_type :=
2026 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2027 itemkey => itemkey,
2028 aname => 'LOAD_TYPE');
2029 if p_load_type = PURCHASE_ORDER_RESCHEDULE then
2030 resultout := 'COMPLETE:PO';
2031 elsif p_load_type = PURCHASE_REQ_RESCHEDULE then
2032 resultout := 'COMPLETE:REQ';
2033 /*
2034 elsif p_load_type = WIP_DIS_MASS_LOAD then
2035 resultout := 'COMPLETE:LOAD_JOB';
2036 elsif p_load_type = WIP_REP_MASS_LOAD then
2037 resultout := 'COMPLETE:LOAD_REP';
2038 elsif p_load_type = WIP_DIS_MASS_RESCHEDULE then
2039 resultout := 'COMPLETE:RES_JOB';
2040 elsif p_load_type = PURCHASE_REQ_MASS_LOAD then
2041 resultout := 'COMPLETE:LOAD_REQ';
2042 elsif p_load_type = LOT_BASED_JOB_LOAD then
2043 resultout := 'COMPLETE:LOAD_LOT';
2044 elsif p_load_type = LOT_BASED_JOB_RESCHEDULE then
2045 resultout := 'COMPLETE:RES_LOT';
2046 */
2047 end if;
2048 end if;
2049 if (funcmode = 'CANCEL') then
2050 resultout := 'COMPLETE:';
2051 end if;
2052
2053 if (funcmode = 'TIMEOUT') then
2054 resultout := 'COMPLETE:';
2055 end if;
2056 END get_load_type;
2057
2058 Procedure start_release_batch_wf(p_plan_id number,
2059 p_org_id number,
2060 p_instance_id number,
2061 p_owning_org number,
2062 p_owning_instance number,
2063 p_dblink varchar2,
2064 p_load_type number,
2065 p_instance_code varchar2) IS
2066
2067 p_item_key varchar2(50) := to_char(p_plan_id)||'-'||
2068 to_char(p_org_id) ||'-'||
2069 to_char(p_instance_id)||'-'||
2070 to_char(p_load_type);
2071 p_process varchar2(30);
2072
2073 BEGIN
2074 deleteActivities(p_item_key);
2075
2076 if p_dblink is not null then
2077 deleteActivities(p_item_key,p_dblink);
2078 end if;
2079
2080 p_process := 'BATCH_UPDATE';
2081
2082 wf_engine.CreateProcess( itemtype => g_item_type,
2083 itemkey => p_item_key,
2084 process => p_process);
2085 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
2086 itemkey => p_item_key,
2087 aname => 'PLAN_ID',
2088 avalue => p_plan_id);
2089 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
2090 itemkey => p_item_key,
2091 aname => 'ORG_ID',
2092 avalue => p_org_id);
2093 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
2094 itemkey => p_item_key,
2095 aname => 'SR_INSTANCE_ID',
2096 avalue => p_instance_id);
2097 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
2098 itemkey => p_item_key,
2099 aname => 'OWNING_ORG_ID',
2100 avalue => p_owning_org);
2101 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
2102 itemkey => p_item_key,
2103 aname => 'OWNING_INSTANCE_ID',
2104 avalue => p_instance_id);
2105 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
2106 itemkey => p_item_key,
2107 aname => 'LOAD_TYPE',
2108 avalue => p_load_type);
2109 wf_engine.SetItemAttrText( itemtype => g_item_type,
2110 itemkey => p_item_key,
2111 aname => 'DBLINK',
2112 avalue => p_dblink);
2113 wf_engine.SetItemAttrText( itemtype => g_item_type,
2114 itemkey => p_item_key,
2115 aname => 'INSTANCE_CODE',
2116 avalue => p_instance_code);
2117
2118 --dbms_output.put_line('item_key='||p_item_key);
2119 wf_engine.StartProcess( itemtype => g_item_type,
2120 itemkey => p_item_key);
2121
2122 update msc_supplies
2123 SET implement_demand_class = NULL,
2124 implement_date = NULL,
2125 implement_quantity = NULL,
2126 implement_firm = NULL,
2127 implement_wip_class_code = NULL,
2128 implement_job_name = NULL,
2129 implement_status_code = NULL,
2130 implement_location_id = NULL,
2131 implement_source_org_id = NULL,
2132 implement_supplier_id = NULL,
2133 implement_supplier_site_id = NULL,
2134 implement_project_id = NULL,
2135 implement_task_id = NULL,
2136 release_status = NULL,
2137 load_type = NULL,
2138 implement_as = NULL,
2139 implement_unit_number = NULL,
2140 implement_schedule_group_id = NULL,
2141 implement_build_sequence = NULL,
2142 implement_line_id = NULL,
2143 implement_alternate_bom = NULL,
2144 implement_alternate_routing = NULL
2145 WHERE organization_id IN
2146 (select planned_organization
2147 from msc_plan_organizations_v
2148 where organization_id = p_owning_org
2149 and owning_sr_instance = p_owning_instance
2150 and plan_id = p_plan_id
2151 AND planned_organization = decode(p_org_id,
2152 p_owning_org, planned_organization,
2153 p_org_id)
2154 AND sr_instance_id = p_instance_id )
2155 AND sr_instance_id= p_instance_id
2156 AND plan_id = p_plan_id
2157 AND release_errors IS NULL
2158 AND load_type = p_load_type;
2159
2160 exception when others then
2161 raise;
2162 END start_release_batch_wf;
2163
2164 Procedure insert_temp_table(itemtype in varchar2,
2165 itemkey in varchar2,
2166 actid in number,
2167 funcmode in varchar2,
2168 resultout out NOCOPY varchar2 ) IS
2169 p_plan_id number;
2170 p_org_id number;
2171 p_instance_id number;
2172 p_owning_org number;
2173 p_owning_instance number;
2174 p_load_type number;
2175 p_count number :=0;
2176 p_apps_ver varchar2(10);
2177 p_wip_group_id number;
2178 p_po_group_by number;
2179 p_po_batch_number number;
2180 p_instance_code varchar2(10);
2181
2182 cursor apps_ver_cur IS
2183 select apps_ver
2184 from msc_apps_instances
2185 where instance_id = p_instance_id;
2186 BEGIN
2187 if (funcmode = 'RUN') then
2188 p_load_type :=
2189 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2190 itemkey => itemkey,
2191 aname => 'LOAD_TYPE');
2192 p_plan_id :=
2193 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2194 itemkey => itemkey,
2195 aname => 'PLAN_ID');
2196 p_org_id :=
2197 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2198 itemkey => itemkey,
2199 aname => 'ORG_ID');
2200 p_instance_id :=
2201 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2202 itemkey => itemkey,
2203 aname => 'SR_INSTANCE_ID');
2204 p_owning_org :=
2205 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2206 itemkey => itemkey,
2207 aname => 'OWNING_ORG_ID');
2208 p_owning_instance :=
2209 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2210 itemkey => itemkey,
2211 aname => 'OWNING_INSTANCE_ID');
2212 open apps_ver_cur;
2213 fetch apps_ver_cur into p_apps_ver;
2214 close apps_ver_cur;
2215
2216 msc_util.msc_debug('insert temp table now');
2217 msc_util.msc_debug('load type='||p_load_type);
2218 msc_util.msc_debug('org id='||p_org_id);
2219 msc_util.msc_debug('instance id='||p_instance_id);
2220 msc_util.msc_debug('owning org id='||p_owning_org);
2221 msc_util.msc_debug('owning instance id='||p_owning_instance);
2222
2223 if p_load_type = WIP_DIS_MASS_LOAD then
2224
2225 p_count := msc_rel_plan_pub.load_wip_discrete_jobs(
2226 p_plan_id,
2227 p_org_id,
2228 p_instance_id,
2229 p_owning_org,
2230 p_owning_instance,
2231 fnd_global.user_id,
2232 p_wip_group_id,
2233 null,
2234 null,
2235 p_apps_ver);
2236
2237 elsif p_load_type = WIP_REP_MASS_LOAD then
2238 p_count := msc_rel_plan_pub.load_repetitive_schedules(
2239 p_plan_id,
2240 p_org_id,
2241 p_instance_id,
2242 p_owning_org,
2243 p_owning_instance,
2244 fnd_global.user_id,
2245 p_wip_group_id,
2246 null,
2247 null);
2248
2249 elsif p_load_type = WIP_DIS_MASS_RESCHEDULE then
2250 p_count := msc_rel_plan_pub.reschedule_wip_discrete_jobs(
2251 p_plan_id,
2252 p_org_id,
2253 p_instance_id,
2254 p_owning_org,
2255 p_owning_instance,
2256 fnd_global.user_id,
2257 p_wip_group_id,
2258 null,
2259 null,
2260 p_apps_ver,
2261 p_load_type);
2262 elsif p_load_type = PURCHASE_REQ_MASS_LOAD then
2263
2264 --fix for the bug#2539212
2265 get_profile_value(p_profile_name => 'MRP_LOAD_REQ_GROUP_BY',
2266 p_instance_id => p_instance_id,
2267 p_calling_source => 'PACKAGE',
2268 p_profile_value => p_po_group_by);
2269
2270 p_count := msc_rel_plan_pub.load_po_requisitions(
2271 p_plan_id,
2272 p_org_id,
2273 p_instance_id,
2274 p_owning_org,
2275 p_owning_instance,
2276 fnd_global.user_id,
2277 p_po_group_by,
2278 p_po_batch_number,
2279 null,
2280 null);
2281 elsif p_load_type = LOT_BASED_JOB_LOAD then
2282 p_count := msc_rel_plan_pub.load_osfm_lot_jobs(
2283 p_plan_id,
2284 p_org_id,
2285 p_instance_id,
2286 p_owning_org,
2287 p_owning_instance,
2288 fnd_global.user_id,
2289 p_wip_group_id,
2290 null,
2291 null,
2292 p_apps_ver);
2293 elsif p_load_type = LOT_BASED_JOB_RESCHEDULE then
2294 p_count := msc_rel_plan_pub.reschedule_osfm_lot_jobs(
2295 p_plan_id,
2296 p_org_id,
2297 p_instance_id,
2298 p_owning_org,
2299 p_owning_instance,
2300 fnd_global.user_id,
2301 p_wip_group_id,
2302 null,
2303 null);
2304 end if;
2305 if p_count >0 then
2306 msc_util.msc_debug('# of rows updated:'|| p_count);
2307 resultout := 'COMPLETE:FOUND';
2308 else
2309 msc_util.msc_debug('no rows are inserted');
2310 resultout := 'COMPLETE:NOT_FOUND';
2311 end if;
2312 end if;
2313 if (funcmode = 'CANCEL') then
2314 resultout := 'COMPLETE:';
2315 end if;
2316
2317 if (funcmode = 'TIMEOUT') then
2318 resultout := 'COMPLETE:';
2319 end if;
2320 END insert_temp_table;
2321
2322 Procedure start_source_program(itemtype in varchar2,
2323 itemkey in varchar2,
2324 actid in number,
2325 funcmode in varchar2,
2326 resultout out NOCOPY varchar2 ) IS
2327 p_load_type number;
2328 p_instance number;
2329 p_request_id number;
2330 p_dblink varchar2(128);
2331 p_po_group_by number;
2332 po_group_by_name varchar2(20);
2333 lv_sql_stmt varchar2(2000);
2334 p_instance_code varchar2(10);
2335 Begin
2336 if (funcmode = 'RUN') then
2337 p_load_type :=
2338 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2339 itemkey => itemkey,
2340 aname => 'LOAD_TYPE');
2341 p_instance :=
2342 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2343 itemkey => itemkey,
2344 aname => 'SR_INSTANCE_ID');
2345 p_instance_code :=
2346 wf_engine.GetItemAttrText( itemtype => itemtype,
2347 itemkey => itemkey,
2348 aname => 'INSTANCE_CODE');
2349 p_dblink :=
2350 wf_engine.GetItemAttrText( itemtype => itemtype,
2351 itemkey => itemkey,
2352 aname => 'DBLINK');
2353
2354 --commenting out the foll code as this procedure is not being used anywhere.
2355 /*
2356 lv_sql_stmt:=
2357 'BEGIN'
2358 ||' MRP_AP_REL_PLAN_PUB.INITIALIZE'
2359 ||p_dblink
2360 ||'( :p_user, :p_resp, :p_app );'
2361 ||' END;';
2362 EXECUTE IMMEDIATE lv_sql_stmt
2363 USING FND_GLOBAL.USER_NAME,
2364 FND_GLOBAL.RESP_NAME,
2365 FND_GLOBAL.APPLICATION_NAME;
2366
2367 msc_util.msc_debug('start reschedule in instance '||p_instance_code);
2368 if p_load_type in (WIP_DIS_MASS_LOAD,WIP_REP_MASS_LOAD,
2369 WIP_DIS_MASS_RESCHEDULE) then
2370
2371 lv_sql_stmt:=
2372 'BEGIN'
2373 ||' MRP_AP_REL_PLAN_PUB.LD_WIP_JOB_SCHEDULE_INTERFACE'
2374 ||p_dblink
2375 ||'( :arg_wip_req_id );'
2376 ||' END;';
2377
2378 EXECUTE IMMEDIATE lv_sql_stmt
2379 USING OUT p_request_id;
2380
2381 DELETE msc_wip_job_schedule_interface
2382 WHERE sr_instance_id= p_instance;
2383
2384 DELETE MSC_WIP_JOB_DTLS_INTERFACE
2385 WHERE sr_instance_id= p_instance;
2386
2387 elsif p_load_type in (LOT_BASED_JOB_LOAD,LOT_BASED_JOB_RESCHEDULE) then
2388 lv_sql_stmt:=
2389 'BEGIN'
2390 ||' MRP_AP_REL_PLAN_PUB.LD_LOT_JOB_SCHEDULE_INTERFACE'
2391 ||p_dblink
2392 ||'( :arg_wip_req_id );'
2393 ||' END;';
2394
2395 EXECUTE IMMEDIATE lv_sql_stmt
2396 USING OUT p_request_id;
2397
2398 DELETE msc_wip_job_schedule_interface
2399 WHERE sr_instance_id= p_instance;
2400
2401 DELETE MSC_WIP_JOB_DTLS_INTERFACE
2402 WHERE sr_instance_id= p_instance;
2403 elsif p_load_type = PURCHASE_REQ_MASS_LOAD then
2404 IF p_po_group_by = 1 THEN
2405 po_group_by_name := 'ALL';
2406 ELSIF p_po_group_by = 2 THEN
2407 po_group_by_name := 'ITEM';
2408 ELSIF p_po_group_by = 3 THEN
2409 po_group_by_name := 'BUYER';
2410 ELSIF p_po_group_by = 4 THEN
2411 po_group_by_name := 'PLANNER';
2412 ELSIF p_po_group_by = 5 THEN
2413 po_group_by_name := 'VENDOR';
2414 ELSIF p_po_group_by = 6 THEN
2415 po_group_by_name := 'ONE-EACH';
2416 ELSIF p_po_group_by = 7 THEN
2417 po_group_by_name := 'CATEGORY';
2418 END IF;
2419
2420 lv_sql_stmt:=
2421 'BEGIN'
2422 ||' MRP_AP_REL_PLAN_PUB.LD_PO_REQUISITIONS_INTERFACE'||p_dblink
2423 ||'( :po_group_by_name,'
2424 ||' :arg_req_load_id );'
2425 ||' END;';
2426
2427 EXECUTE IMMEDIATE lv_sql_stmt
2428 USING IN po_group_by_name,
2429 OUT p_request_id;
2430
2431 DELETE MSC_PO_REQUISITIONS_INTERFACE
2432 WHERE sr_instance_id= p_instance;
2433
2434 end if;
2435
2436 msc_util.msc_debug('request id is '||p_request_id
2437 ||', in instance '||p_instance_code);
2438
2439 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2440 itemkey => itemkey,
2441 aname => 'REQUEST_ID',
2442 avalue => p_request_id);
2443 commit;
2444 */
2445 end if;
2446 if (funcmode = 'CANCEL') then
2447 resultout := 'COMPLETE:';
2448 end if;
2449
2450 if (funcmode = 'TIMEOUT') then
2451 resultout := 'COMPLETE:';
2452 end if;
2453 END start_source_program;
2454
2455 Procedure get_supply_data(p_plan_id in number,
2456 p_transaction_id in number,
2457 p_query_id in number,
2458 p_dblink in varchar2) IS
2459 cursor supply_cur IS
2460 select msc_get_name.org_code(s.organization_id,s.sr_instance_id),
2461 msc_get_name.supplier(s.supplier_id),
2462 msc_get_name.supplier_site(s.supplier_site_id),
2463 msc_get_name.lookup_meaning('MRP_ORDER_TYPE',s.order_type),
2464 DECODE(s.order_type,5,to_char(s.transaction_id),s.order_number),
2465 s.new_schedule_date,
2466 s.new_order_quantity,
2467 mp.compile_designator,
2468 msi.item_name,
2469 msi.buyer_name,
2470 decode(s.order_type, 1, s.disposition_id, null),
2471 s.po_line_id,
2472 s.implement_quantity,
2473 cal2.calendar_date
2474 from msc_supplies s,
2475 msc_system_items msi,
2476 msc_plans mp,
2477 msc_calendar_dates cal1,
2478 msc_calendar_dates cal2,
2479 msc_trading_partners mtp
2480 where s.plan_id = p_plan_id
2481 and s.transaction_id =p_transaction_id
2482 and s.plan_id = mp.plan_id
2483 and s.plan_id = msi.plan_id
2484 and s.organization_id = msi.organization_id
2485 and s.sr_instance_id = msi.sr_instance_id
2486 and s.inventory_item_id = msi.inventory_item_id
2487 and cal1.sr_instance_id = mtp.sr_instance_id
2488 AND cal1.calendar_code = mtp.calendar_code
2489 AND cal1.exception_set_id = mtp.calendar_exception_set_id
2490 AND cal1.calendar_date = trunc(NVL(s.implement_date,s.new_schedule_date))
2491 AND cal2.sr_instance_id = cal1.sr_instance_id
2492 AND cal2.calendar_code = cal1.calendar_code
2493 AND cal2.exception_set_id = cal1.exception_set_id
2494 AND cal2.seq_num = GREATEST(1,NVL(cal1.seq_num, cal1.prior_seq_num) -
2495 NVL(msi.postprocessing_lead_time, 0))
2496 AND mtp.sr_tp_id = msi.organization_id
2497 AND mtp.sr_instance_id = msi.sr_instance_id
2498 AND mtp.partner_type= 3;
2499
2500 CURSOR need_by_date_cur IS
2501 SELECT new_dock_date
2502 FROM msc_supplies
2503 WHERE plan_id = -1
2504 AND transaction_id = p_transaction_id;
2505
2506 l_plan_name varchar2(20);
2507 l_item_name varchar2(40);
2508 l_org_code varchar2(20);
2509 l_supplier varchar2(80);
2510 l_supplier_site varchar2(15);
2511 l_order varchar2(80);
2512 l_order_type varchar2(80);
2513 l_buyer varchar2(80);
2514 l_old_need_by_date date;
2515 l_new_need_by_date date;
2516 l_new_due_date date;
2517 l_qty number;
2518 l_impl_qty number;
2519 l_po_header_id number;
2520 l_po_line_id number;
2521
2522 lv_sql_stmt varchar2(3000);
2523 BEGIN
2524 OPEN supply_cur;
2525 FETCH supply_cur INTO l_org_code,
2526 l_supplier,
2527 l_supplier_site,
2528 l_order_type,
2529 l_order,
2530 l_new_due_date,
2531 l_qty,
2532 l_plan_name,
2533 l_item_name,
2534 l_buyer,
2535 l_po_header_id,
2536 l_po_line_id,
2537 l_impl_qty,
2538 l_new_need_by_date;
2539 CLOSE supply_cur;
2540
2541 OPEN need_by_date_cur;
2542 FETCH need_by_date_cur INTO l_old_need_by_date;
2543 CLOSE need_by_date_cur;
2544
2545 lv_sql_stmt:=
2546 'insert into mrp_form_query'||p_dblink||
2547 ' (query_id,'||
2548 ' last_update_date,'||
2549 ' last_updated_by,'||
2550 ' creation_date,'||
2551 ' created_by,'||
2552 ' char1,'||
2553 ' char2,'||
2554 ' char3,'||
2555 ' char4,'||
2556 ' char5,'||
2557 ' char6,'||
2558 ' char7,'||
2559 ' char8,'||
2560 ' date1,'||
2561 ' date2,'||
2562 ' date3,'||
2563 ' number1,'||
2564 ' number2,'||
2565 ' number3,'||
2566 ' number4)'||
2567 ' VALUES('||
2568 ' :p_query_id,'||
2569 ' sysdate,'||
2570 ' -1,'||
2571 ' sysdate,'||
2572 ' -1,'||
2573 ' :l_org_code,'||
2574 ' :l_supplier,'||
2575 ' :l_supplier_site,'||
2576 ' :l_order_type,'||
2577 ' :l_order,'||
2578 ' :l_plan_name,'||
2579 ' :l_item_name,'||
2580 ' :l_buyer,'||
2581 ' :l_new_due_date,'||
2582 ' :l_new_need_by_date,'||
2583 ' :l_old_need_by_date,'||
2584 ' :l_qty,'||
2585 ' :l_impl_qty,'||
2586 ' :l_po_header_id,'||
2587 ' :l_po_line_id)';
2588
2589 EXECUTE IMMEDIATE lv_sql_stmt
2590 USING p_query_id,
2591 l_org_code,
2592 l_supplier,
2593 l_supplier_site,
2594 l_order_type,
2595 l_order,
2596 l_plan_name,
2597 l_item_name,
2598 l_buyer,
2599 l_new_due_date,
2600 l_new_need_by_date,
2601 l_old_need_by_date,
2602 l_qty,
2603 l_impl_qty,
2604 l_po_header_id,
2605 l_po_line_id;
2606
2607 exception when others then
2608 raise;
2609 END get_supply_data;
2610
2611 PROCEDURE init_db(p_user_name varchar2) IS
2612 l_user_id number;
2613 l_resp_id number;
2614 l_application_id number;
2615 BEGIN
2616 select user_id
2617 into l_user_id
2618 from fnd_user
2619 where user_name = p_user_name;
2620
2621 begin
2622 SELECT APPLICATION_ID
2623 INTO l_application_id
2624 FROM FND_APPLICATION_VL
2625 WHERE APPLICATION_SHORT_NAME = 'MSC'
2626 and rownum =1 ;
2627
2628 SELECT responsibility_id
2629 INTO l_resp_id
2630 FROM FND_responsibility_vl
2631 where application_Id = l_application_id
2632 and rownum =1 ;
2633
2634 exception when no_data_found then
2635
2636 SELECT APPLICATION_ID
2637 INTO l_application_id
2638 FROM FND_APPLICATION_VL
2639 WHERE APPLICATION_SHORT_NAME = 'MRP'
2640 and rownum = 1;
2641
2642 SELECT responsibility_id
2643 INTO l_resp_id
2644 FROM FND_responsibility_vl
2645 where application_Id = l_application_id
2646 and rownum =1 ;
2647 end;
2648
2649 fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
2650 END init_db;
2651
2652 PROCEDURE close_dblink(p_dblink varchar2) IS
2653 lv_sql_stmt VARCHAR2(2000);
2654 DBLINK_NOT_OPEN EXCEPTION;
2655 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
2656
2657 BEGIN
2658 IF p_dblink <> ' ' then
2659 -- mark distributed transaction boundary
2660 -- will need to do a manual clean up (commit) of the distributed
2661 -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
2662 commit;
2663
2664 lv_sql_stmt := 'alter session close database link ' ||p_dblink;
2665
2666 EXECUTE IMMEDIATE lv_sql_stmt;
2667
2668 END IF;
2669
2670 EXCEPTION
2671 WHEN DBLINK_NOT_OPEN THEN
2672 NULL;
2673 END close_dblink;
2674
2675
2676 --This procedure is added to fix the issue#2539212
2677 PROCEDURE get_profile_value(p_profile_name IN varchar2,
2678 p_instance_id IN number,
2679 p_calling_source IN varchar2 default 'FORM',
2680 p_profile_value OUT NOCOPY varchar2
2681 ) IS
2682 lv_user_name VARCHAR2(100):= NULL;
2683 lv_resp_name VARCHAR2(100):= NULL;
2684 lv_application_name VARCHAR2(240):= NULL;
2685 lv_appl_short_name VARCHAR2(10):= NULL;
2686 lv_dblink VARCHAR2(128);
2687 lv_dblink2 VARCHAR2(128);
2688 lv_sql_stmt VARCHAR2(2000);
2689
2690 cursor appl_short_name (p_appl_name IN VARCHAR2) IS
2691 select application_short_name
2692 from fnd_application_vl
2693 where application_name = p_appl_name;
2694
2695 BEGIN
2696
2697 SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK),
2698 DECODE( M2A_DBLINK, NULL, ' ', M2A_DBLINK)
2699 INTO lv_dblink, lv_dblink2
2700 FROM msc_apps_instances
2701 WHERE instance_id = p_instance_id;
2702
2703 SELECT FND_GLOBAL.USER_NAME,
2704 FND_GLOBAL.RESP_NAME,
2705 FND_GLOBAL.APPLICATION_NAME
2706 INTO lv_user_name,
2707 lv_resp_name,
2708 lv_application_name
2709 FROM dual;
2710
2711 open appl_short_name(lv_application_name);
2712 fetch appl_short_name into lv_appl_short_name;
2713 close appl_short_name;
2714
2715
2716
2717
2718 lv_sql_stmt:= 'SELECT mrp_rel_wf.get_profile_value'||lv_dblink||'('||
2719 ':1, :2, :3, :4) from dual';
2720
2721 EXECUTE IMMEDIATE lv_sql_stmt
2722 INTO p_profile_value
2723 USING p_profile_name,
2724 lv_user_name,
2725 lv_resp_name,
2726 lv_appl_short_name;
2727
2728 close_dblink(lv_dblink2);
2729
2730 EXCEPTION
2731 WHEN NO_DATA_FOUND THEN
2732 close_dblink(lv_dblink2);
2733 p_profile_value := NULL;
2734
2735 WHEN others THEN
2736 IF p_calling_source = 'FORM' THEN
2737 fnd_message.set_name('MSC',sqlerrm);
2738 APP_EXCEPTION.RAISE_EXCEPTION;
2739 END IF;
2740 END get_profile_value;
2741
2742 FUNCTION get_offset_date(p_calendar_code in varchar2,
2743 p_inst_id in number,
2744 p_lead_time in number,
2745 p_date in date) return date is
2746 p_return_date date;
2747
2748 begin
2749
2750
2751 if P_calendar_code is null or p_calendar_code = MSC_CALENDAR.FOC then
2752
2753 -- shipping/receiving calendar hierarchy [if no CRC, then 24x7]
2754 p_return_date := p_date + nvl(p_lead_time,0);
2755
2756 else
2757
2758 p_return_date := msc_calendar.date_offset
2759 ( p_calendar_code
2760 , p_inst_id
2761 , p_date
2762 , nvl(p_lead_time,0)
2763 , null -- association_type
2764 );
2765
2766 -- msc_calendar.date_offset will remove the timestamp
2767 if to_char(p_return_date,'HH24:MI:SS') = '00:00:00' and
2768 to_char(p_date,'HH24:MI:SS') <> '00:00:00' then
2769 p_return_date := to_date(to_char(p_return_date, 'MM/DD/RR')||' '||
2770 to_char(p_date,'HH24:MI:SS'),
2771 'MM/DD/RR HH24:MI:SS');
2772
2773 end if;
2774 end if;
2775 return p_return_date;
2776 end get_offset_date;
2777
2778 PROCEDURE update_so_dates(p_plan_id number, p_demand_id number,
2779 p_inst_id number, p_implement_date date,
2780 p_ship_date out nocopy date,
2781 p_arrival_date out nocopy date,
2782 p_earliest_date out nocopy date) IS
2783
2784 TYPE NumArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2785 TYPE DateArr IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2786
2787 v_set_demand_id NumArr;
2788 v_lead_time NumArr;
2789 v_org_id NumArr;
2790 v_inst_id NumArr;
2791 v_ship_date DateArr;
2792 v_arrival_date DateArr;
2793 v_planned_ship_date DateArr;
2794 v_planned_arrival_date DateArr;
2795 v_earliest_date DateArr;
2796 v_request_date DateArr;
2797 v_schedule_date DateArr;
2798 v_vic_cal_code msc_calendars.calendar_code%type;
2799 v_association_type number;
2800
2801 cursor so_c is
2802 select order_number,
2803 organization_id org_id,
2804 sr_instance_id inst_id,
2805 origination_type demand_type,
2806 ship_set_id, arrival_set_id,
2807 dmd_satisfied_date earliest_ship_date,
2808 nvl(planned_ship_date,dmd_satisfied_date) planned_ship_date,
2809 planned_arrival_date,
2810 nvl(p_implement_date,nvl(firm_date, nvl(planned_ship_date,
2811 dmd_satisfied_date))) ship_date,
2812 order_date_type_code order_type,
2813 decode(order_date_type_code, 1, request_ship_date,
2814 request_date) request_date,
2815 decode(order_date_type_code, 1, schedule_ship_date,
2816 schedule_arrival_date) schedule_date,
2817 intransit_lead_time lead_time,
2818 inventory_item_id,
2819 ship_method,
2820 customer_id,
2821 customer_site_id
2822 from msc_demands
2823 where plan_id = p_plan_id
2824 and demand_id = p_demand_id
2825 and sr_instance_id = p_inst_id;
2826
2827 so_rec so_c%ROWTYPE;
2828
2829 -- release all lines in a ship/arrival set and use max date
2830
2831 cursor ship_set(p_ship_set_id number) is
2832 select
2833 md.demand_id,
2834 md.dmd_satisfied_date, --earliest ship date,
2835 decode(md.demand_id, p_demand_id,
2836 nvl(p_implement_date, --plan ship date
2837 nvl(firm_date, nvl(md.planned_ship_date,
2838 md.dmd_satisfied_date))),
2839 nvl(implement_date,
2840 nvl(firm_date, nvl(md.planned_ship_date,
2841 md.dmd_satisfied_date)))),
2842 md.request_ship_date,
2843 md.schedule_ship_date,
2844 nvl(md.planned_ship_date,md.dmd_satisfied_date),
2845 md.planned_arrival_date,
2846 md.intransit_lead_time
2847 from msc_demands md,
2848 msc_system_items msi
2849 where md.plan_id = p_plan_id
2850 and md.ship_set_id = p_ship_set_id
2851 and msi.plan_id = md.plan_id
2852 and msi.organization_id = md.organization_id
2853 and msi.sr_instance_id = md.sr_instance_id
2854 and msi.inventory_item_id = md.inventory_item_id
2855 and nvl(msi.bom_item_type,4) <> 5; -- not a product family
2856
2857 cursor arrival_set(p_arrival_set_id number) is
2858 select
2859 md.demand_id,
2860 md.dmd_satisfied_date, --earliest ship date
2861 decode(md.demand_id, p_demand_id, --plan ship date
2862 nvl(p_implement_date,
2863 nvl(firm_date, nvl(md.planned_ship_date,
2864 md.dmd_satisfied_date))),
2865 nvl(implement_date,
2866 nvl(firm_date, nvl(md.planned_ship_date,
2867 md.dmd_satisfied_date)))),
2868 md.request_date,
2869 md.schedule_arrival_date,
2870 md.intransit_lead_time,
2871 md.organization_id,
2872 md.sr_instance_id,
2873 nvl(md.planned_ship_date,md.dmd_satisfied_date),
2874 md.planned_arrival_date
2875 from msc_demands md,
2876 msc_system_items msi
2877 where md.plan_id = p_plan_id
2878 and md.arrival_set_id = p_arrival_set_id
2879 and msi.plan_id = md.plan_id
2880 and msi.organization_id = md.organization_id
2881 and msi.sr_instance_id = md.sr_instance_id
2882 and msi.inventory_item_id = md.inventory_item_id
2883 and nvl(msi.bom_item_type,4) <> 5; -- not a product family
2884
2885 v_temp number;
2886 p_new_earliest_date date;
2887
2888 CURSOR c_plan_type(p_plan_id number) IS
2889 select plan_type
2890 from msc_plans a
2891 where
2892 plan_id = p_plan_id;
2893
2894 p_plan_type NUMBER;
2895 BEGIN
2896
2897 OPEN c_plan_type(p_plan_id);
2898 FETCH c_plan_type INTO p_plan_type;
2899 CLOSE c_plan_type;
2900
2901
2902 OPEN so_c;
2903 FETCH so_c INTO so_rec;
2904 CLOSE so_c;
2905
2906 begin
2907 v_vic_cal_code := msc_calendar.get_calendar_code
2908 ( p_inst_id
2909 , null
2910 , null
2911 , null
2912 , null
2913 , 4 -- partner type [in transit]
2914 , null
2915 , so_rec.ship_method
2916 , MSC_CALENDAR.VIC
2917 , v_association_type
2918 );
2919 exception when others then
2920 v_vic_cal_code := null;
2921 end;
2922
2923 if so_rec.ship_set_id is not null then
2924 OPEN ship_set(so_rec.ship_set_id);
2925 FETCH ship_set BULK COLLECT INTO
2926 v_set_demand_id, v_earliest_date,
2927 v_ship_date,
2928 v_request_date, v_schedule_date,
2929 v_planned_ship_date,
2930 v_planned_arrival_date, v_lead_time;
2931 CLOSE ship_set;
2932
2933 for a in 1..v_set_demand_id.count loop
2934
2935 -- find the max earliest ship date
2936 if a = 1 then
2937 p_earliest_date := v_earliest_date(a);
2938 else
2939 p_earliest_date := greatest(p_earliest_date,v_earliest_date(a));
2940 end if;
2941
2942 v_ship_date(a) := verify_so_dates(v_schedule_date(a),
2943 v_request_date(a),v_ship_date(a));
2944
2945 -- find the max ship date
2946 if a = 1 then
2947 p_ship_date := v_ship_date(a);
2948 else
2949 p_ship_date := greatest(p_ship_date,v_ship_date(a));
2950 end if;
2951
2952 end loop;
2953
2954 -- will use max date for the whole set
2955
2956 for a in 1..v_set_demand_id.count loop
2957 if p_ship_date <> v_planned_ship_date(a) or
2958 v_planned_arrival_date(a) is null then
2959 -- recalculate arrival date
2960 v_planned_arrival_date(a) := get_offset_date ( v_vic_cal_code
2961 , p_inst_id
2962 , v_lead_time(a)
2963 , p_ship_date);
2964 end if;
2965
2966 if v_set_demand_id(a) <> p_demand_id then
2967 update msc_demands
2968 set implement_ship_date = p_ship_date, -- sche ship date
2969 implement_date = nvl(implement_date, nvl(firm_date,
2970 nvl(planned_ship_date,dmd_satisfied_date))),
2971 implement_earliest_date = p_earliest_date, -- earliest ship date
2972 implement_arrival_date = v_planned_arrival_date(a),
2973 implement_org_id = organization_id,
2974 implement_instance_id = sr_instance_id,
2975 implement_firm = nvl(implement_firm, org_firm_flag),
2976 load_type = 30,
2977 reschedule_flag = 1,
2978 release_status = decode(sign(p_plan_type-100),1,13,1),
2979 --- for rp, 13= mark for release
2980 status = 0,
2981 applied =2,
2982 last_updated_by = fnd_global.user_id
2983 where plan_id = p_plan_id
2984 and demand_id = v_set_demand_id(a);
2985 else -- if v_set_demand_id(a) = p_demand_id then
2986 p_arrival_date := v_planned_arrival_date(a);
2987 end if;
2988 end loop;
2989
2990 end if; -- end of if p_ship_set_id is not null then
2991
2992 if so_rec.arrival_set_id is not null then
2993 OPEN arrival_set(so_rec.arrival_set_id);
2994 FETCH arrival_set BULK COLLECT INTO
2995 v_set_demand_id, v_earliest_date,
2996 v_ship_date,
2997 v_request_date, v_schedule_date,
2998 v_lead_time,
2999 v_org_id, v_inst_id,
3000 v_planned_ship_date,
3001 v_planned_arrival_date;
3002 CLOSE arrival_set;
3003
3004 for a in 1..v_set_demand_id.count loop
3005 -- if user does not change implement_date(ie. = planned_ship_date)
3006 -- no need to recalculate arrival date, just use planned_arrival_date
3007 if v_ship_date(a) <> v_planned_ship_date(a) then
3008 -- offset sch_ship_date with lead time to get sch_arrival_date
3009
3010 v_arrival_date(a) := get_offset_date
3011 ( v_vic_cal_code
3012 , v_inst_id(a)
3013 , v_lead_time(a)
3014 , v_ship_date(a)
3015 );
3016 else
3017 v_arrival_date(a) := v_planned_arrival_date(a);
3018 end if;
3019 -- find the max date for earliest_date
3020 if a = 1 then
3021 p_earliest_date := get_offset_date
3022 ( v_vic_cal_code
3023 , v_inst_id(a)
3024 , v_lead_time(a)
3025 , v_earliest_date(a)
3026 );
3027 else
3028 p_earliest_date := greatest(p_earliest_date,
3029 get_offset_date
3030 ( v_vic_cal_code
3031 , v_inst_id(a)
3032 , v_lead_time(a)
3033 , v_earliest_date(a)
3034 ));
3035 end if;
3036
3037 v_arrival_date(a) := verify_so_dates(v_schedule_date(a),
3038 v_request_date(a),
3039 v_arrival_date(a));
3040 -- find the max date for schedule_arrival_date
3041 if a = 1 then
3042 p_arrival_date := v_arrival_date(a);
3043 else
3044 p_arrival_date := greatest(p_arrival_date,v_arrival_date(a));
3045 end if;
3046
3047 end loop;
3048
3049 -- will use max date for the whole set
3050 for a in 1..v_set_demand_id.count loop
3051 if p_arrival_date <> v_planned_arrival_date(a) then
3052 -- offset max(sch_arrival_date) with lead time to get sch_ship_date
3053 v_ship_date(a) := msc_rel_wf.get_offset_date
3054 ( v_vic_cal_code
3055 , v_inst_id(a)
3056 , v_lead_time(a)*-1
3057 , p_arrival_date);
3058 else
3059 v_ship_date(a) := v_planned_ship_date(a);
3060
3061 end if;
3062 v_earliest_date(a) := msc_rel_wf.get_offset_date
3063 ( v_vic_cal_code
3064 , v_inst_id(a)
3065 , v_lead_time(a)*-1
3066 , p_earliest_date);
3067 if v_set_demand_id(a) <> p_demand_id then
3068 update msc_demands
3069 set implement_arrival_date = p_arrival_date,
3070 implement_earliest_date = v_earliest_date(a),
3071 implement_ship_date = v_ship_date(a), -- sche ship date
3072 implement_date = nvl(implement_date, nvl(firm_date,
3073 nvl(planned_ship_date,dmd_satisfied_date))),
3074 implement_org_id = organization_id,
3075 implement_instance_id = sr_instance_id,
3076 implement_firm = nvl(implement_firm, org_firm_flag),
3077 load_type = 30,
3078 reschedule_flag = 1,
3079 release_status = decode(sign(p_plan_type-100),1,13,1),
3080 status = 0,
3081 applied =2,
3082 last_updated_by = fnd_global.user_id
3083 where plan_id = p_plan_id
3084 and demand_id = v_set_demand_id(a);
3085 else -- if v_set_demand_id(a) = p_demand_id then
3086 p_ship_date := v_ship_date(a);
3087 p_new_earliest_date := v_earliest_date(a);
3088 end if;
3089 end loop;
3090
3091 p_earliest_date := p_new_earliest_date;
3092
3093 end if; -- end of if p_arrival_set_id is not null then
3094
3095 if so_rec.ship_set_id is null and so_rec.arrival_set_id is null then
3096 if so_rec.order_type = 1 then -- ship
3097 p_earliest_date := so_rec.earliest_ship_date;
3098 p_ship_date := verify_so_dates(so_rec.schedule_date,
3099 so_rec.request_date,
3100 so_rec.ship_date);
3101 if p_ship_date <> so_rec.planned_ship_date then
3102 p_arrival_date := msc_rel_wf.get_offset_date
3103 ( v_vic_cal_code
3104 , so_rec.inst_id
3105 , so_rec.lead_time
3106 , p_ship_date);
3107 else
3108 p_arrival_date := so_rec.planned_arrival_date;
3109 end if;
3110 else -- arrival
3111 if so_rec.ship_date <> so_rec.planned_ship_date then
3112 p_arrival_date := msc_rel_wf.get_offset_date
3113 ( v_vic_cal_code
3114 , so_rec.inst_id
3115 , so_rec.lead_time
3116 , so_rec.ship_date);
3117 else
3118 p_arrival_date := so_rec.planned_arrival_date;
3119 end if;
3120 p_arrival_date := verify_so_dates(so_rec.schedule_date,
3121 so_rec.request_date,
3122 p_arrival_date);
3123 p_earliest_date := so_rec.earliest_ship_date;
3124 if p_arrival_date <> so_rec.planned_arrival_date then
3125 p_ship_date := msc_rel_wf.get_offset_date
3126 ( v_vic_cal_code
3127 , so_rec.inst_id
3128 , so_rec.lead_time*-1
3129 , p_arrival_date);
3130 else
3131 p_ship_date := so_rec.planned_ship_date;
3132 end if;
3133
3134 end if;
3135 end if; --if so_rec.ship_set_id is null and so_rec.arrival_set_id is null
3136 update msc_demands
3137 set implement_earliest_date = p_earliest_date
3138 where plan_id = p_plan_id
3139 and demand_id = p_demand_id;
3140
3141 END update_so_dates;
3142
3143 PROCEDURE unrelease_so_set(p_plan_id number, p_demand_id number,
3144 p_instance_id number) IS
3145 cursor set_id is
3146 select ship_set_id, arrival_set_id
3147 from msc_demands
3148 where plan_id = p_plan_id
3149 and demand_id = p_demand_id
3150 and sr_instance_id = p_instance_id;
3151 p_ship_set_id number;
3152 p_arrival_set_id number;
3153 BEGIN
3154 OPEN set_id;
3155 FETCH set_id into p_ship_set_id, p_arrival_set_id;
3156 CLOSE set_id;
3157
3158 if p_ship_set_id is not null then
3159 UPDATE MSC_DEMANDS
3160 SET implement_date = NULL,
3161 implement_ship_date = NULL,
3162 implement_earliest_date = NULL,
3163 implement_arrival_date = NULL,
3164 implement_org_id = NULL,
3165 implement_instance_id = NULL,
3166 implement_firm = null,
3167 release_status = NULL,
3168 reschedule_flag = null,
3169 load_type = NULL,
3170 applied = 2,
3171 status =0
3172 WHERE plan_id= p_plan_id
3173 AND origination_type = 30
3174 AND ship_set_id = p_ship_set_id
3175 AND demand_id <> p_demand_id;
3176 end if;
3177
3178 if p_arrival_set_id is not null then
3179 UPDATE MSC_DEMANDS
3180 SET implement_date = NULL,
3181 implement_ship_date = NULL,
3182 implement_earliest_date = NULL,
3183 implement_arrival_date = NULL,
3184 implement_org_id = NULL,
3185 implement_instance_id = NULL,
3186 implement_firm = null,
3187 reschedule_flag = null,
3188 release_status = NULL,
3189 load_type = NULL,
3190 applied = 2,
3191 status =0
3192 WHERE plan_id= p_plan_id
3193 AND origination_type = 30
3194 AND arrival_set_id = p_arrival_set_id
3195 AND demand_id <> p_demand_id;
3196 end if;
3197
3198 END unrelease_so_set;
3199
3200 FUNCTION verify_so_release(p_plan_id number, p_demand_id number,
3201 p_inst_id number)
3202 RETURN varchar2 IS
3203
3204 TYPE NumArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3205
3206 v_subst_item NumArr;
3207
3208 cursor so_c is
3209 select order_number,
3210 organization_id org_id,
3211 origination_type demand_type,
3212 ship_set_id, arrival_set_id,
3213 decode(nvl(prev_subst_item,0),
3214 inventory_item_id, 0,0,0,1) subst_item
3215 from msc_demands
3216 where plan_id = p_plan_id
3217 and demand_id = p_demand_id
3218 and sr_instance_id = p_inst_id;
3219
3220 so_rec so_c%ROWTYPE;
3221
3222 -- can not have multiple sources for one sales order
3223 cursor check_source(p_order_number varchar2,
3224 p_order_type number,
3225 p_org_id number) is
3226 select 1
3227 from msc_demands
3228 where plan_id = p_plan_id
3229 and order_number = p_order_number
3230 and origination_type = p_order_type
3231 and organization_id <> p_org_id
3232 and using_requirement_quantity <> 0;
3233
3234 -- can not have multiple sources for ship set
3235
3236 cursor check_ship_source(p_ship_set_id number,
3237 p_org_id number) is
3238 select 1
3239 from msc_demands
3240 where plan_id = p_plan_id
3241 and ship_set_id = p_ship_set_id
3242 and organization_id <> p_org_id ;
3243
3244 -- release all lines in a ship/arrival set and use max date
3245
3246 cursor ship_set(p_ship_set_id number) is
3247 select
3248 decode(nvl(original_item_id,0), inventory_item_id, 0,0,0,1)
3249 from msc_demands
3250 where plan_id = p_plan_id
3251 and ship_set_id = p_ship_set_id;
3252
3253 cursor arrival_set(p_arrival_set_id number) is
3254 select
3255 decode(nvl(original_item_id,0), inventory_item_id, 0,0,0,1)
3256 from msc_demands
3257 where plan_id = p_plan_id
3258 and arrival_set_id = p_arrival_set_id;
3259
3260 v_error_msg varchar2(80);
3261 v_temp number;
3262 BEGIN
3263
3264 OPEN so_c;
3265 FETCH so_c INTO so_rec;
3266 CLOSE so_c;
3267
3268 /* GE enhancement, will allow release Sales Order with item substitution
3269 if so_rec.subst_item =1 then
3270 v_error_msg := 'MSC_REL_SO_SUBST_ITEM';
3271 return v_error_msg;
3272 end if;
3273 */
3274 v_temp :=0;
3275 OPEN check_source(so_rec.order_number, so_rec.demand_type,so_rec.org_id);
3276 FETCH check_source INTO v_temp;
3277 CLOSE check_source;
3278
3279 if v_temp = 1 then
3280 v_error_msg := 'MSC_REL_SO_MULTI_SOURCES';
3281 return v_error_msg;
3282 end if;
3283
3284 if so_rec.ship_set_id is not null then
3285
3286 v_temp :=0;
3287 OPEN check_ship_source(so_rec.ship_set_id, so_rec.org_id);
3288 FETCH check_ship_source INTO v_temp;
3289 CLOSE check_ship_source;
3290
3291 if v_temp = 1 then
3292 v_error_msg := 'MSC_REL_SHIP_SET_MULTI_SOURCES';
3293 return v_error_msg;
3294 end if;
3295 /*
3296 OPEN ship_set(so_rec.ship_set_id);
3297 FETCH ship_set BULK COLLECT INTO v_subst_item;
3298 CLOSE ship_set;
3299
3300 for a in 1..v_subst_item.count loop
3301
3302 if v_subst_item(a) = 1 then
3303 v_error_msg := 'MSC_REL_SO_SUBST_ITEM_IN_A_SET';
3304 return v_error_msg;
3305 end if;
3306
3307 end loop;
3308 */
3309 end if; -- end of if p_ship_set_id is not null then
3310 /*
3311 if so_rec.arrival_set_id is not null then
3312 OPEN arrival_set(so_rec.arrival_set_id);
3313 FETCH arrival_set BULK COLLECT INTO v_subst_item;
3314 CLOSE arrival_set;
3315
3316 for a in 1..v_subst_item.count loop
3317
3318 if v_subst_item(a) = 1 then
3319 v_error_msg := 'MSC_REL_SO_SUBST_ITEM_IN_A_SET';
3320 return v_error_msg;
3321 end if;
3322
3323 end loop;
3324
3325 end if; -- end of if p_arrival_set_id is not null then
3326 */
3327 return v_error_msg;
3328
3329 END verify_so_release;
3330
3331 FUNCTION verify_so_dates(p_old_schedule_date date,
3332 p_request_date date,
3333 p_new_schedule_date date) RETURN date IS
3334 p_new_date date;
3335 BEGIN
3336 -- if new schedule date >= old schedule date, new date = new schedule date
3337 -- else
3338 -- if old scheduld date < request date, new date = old schedule date
3339 -- else
3340 -- if new schedule date > request date, new date = new schedule date
3341 -- else new date = request date
3342
3343 if p_old_schedule_date is null or p_request_date is null then
3344 return p_new_schedule_date;
3345 end if;
3346 if p_new_schedule_date >= p_old_schedule_date then
3347 return p_new_schedule_date;
3348 else -- if p_new_schedule_date < p_old_schedule_date then
3349 if p_old_schedule_date < p_request_date then
3350 return p_old_schedule_date;
3351 else -- if p_old_schedule_date >= p_request_date then
3352 if p_new_schedule_date > p_request_date then
3353 return p_new_schedule_date;
3354 else
3355 return p_request_date;
3356 end if;
3357 end if; -- if p_old_schedule_date < p_request_date then
3358 end if; -- if p_new_schedule_date >= p_old_schedule_date then
3359
3360 END verify_so_dates;
3361
3362 PROCEDURE so_release_workflow_program(p_batch_id in number,
3363 p_instance_id in number,
3364 p_planner in varchar2,
3365 p_request_id out nocopy number) IS
3366 p_result boolean;
3367 begin
3368 msc_rel_wf.init_db(p_planner);
3369 p_result := fnd_request.set_mode(true);
3370 -- this will call msc_rel_wf.start_so_release_workflow
3371
3372 p_request_id := fnd_request.submit_request(
3373 'MSC',
3374 'MSCRLSOWF',
3375 null,
3376 null,
3377 false,
3378 p_batch_id,
3379 p_instance_id);
3380
3381 exception when others then
3382 p_request_id :=0;
3383 raise;
3384 end so_release_workflow_program;
3385
3386 PROCEDURE start_so_release_workflow(
3387 errbuf OUT NOCOPY VARCHAR2
3388 ,retcode OUT NOCOPY NUMBER,
3389 p_batch_id number,
3390 p_instance_id number) IS
3391
3392 l_process varchar2(50) := 'EXCEPTION_PROCESS3';
3393 item_type varchar2(50) :='MSCEXPWF';
3394 item_key varchar2(50);
3395 l_planner varchar2(100);
3396
3397 CURSOR PLANNER_C( p_plan_id in number, p_inventory_item_id in number,
3398 p_organization_id in number, p_instance_id in number) IS
3399 SELECT distinct pl.user_name
3400 FROM msc_planners pl,
3401 msc_system_items sys
3402 WHERE sys.plan_id = p_plan_id
3403 AND sys.organization_id = p_organization_id
3404 AND sys.sr_instance_id = p_instance_id
3405 AND sys.inventory_item_id = p_inventory_item_id
3406 AND pl.organization_id = sys.organization_id
3407 AND pl.sr_instance_id = sys.sr_instance_id
3408 AND pl.planner_code = sys.planner_code;
3409
3410 cursor all_c is
3411 select msi.item_name,
3412 md.order_number,
3413 msi.description item_desc,
3414 msc_get_name.customer(md.customer_id) customer_name,
3415 msc_get_name.customer_site(md.customer_site_id) customer_site,
3416 msc_get_name.org_code(nvl(md.prev_subst_org, md.original_org_id),
3417 md.original_inst_id) org_code,
3418 msc_get_name.org_code(md.organization_id,md.sr_instance_id) to_org,
3419 msoi.schedule_ship_date new_ship_date,
3420 md.schedule_ship_date old_ship_date,
3421 msoi.schedule_arrival_date new_arrival_date,
3422 md.schedule_arrival_date old_arrival_date,
3423 msoi.ship_method new_ship_method,
3424 md.orig_shipping_method_code old_ship_method,
3425 md.orig_intransit_lead_time old_lead_time,
3426 msoi.delivery_lead_time new_lead_time,
3427 msoi.earliest_ship_date earliest_ship_date,
3428 msoi.return_status,
3429 md.demand_id,
3430 md.inventory_item_id,
3431 md.organization_id,
3432 md.sr_instance_id,
3433 md.plan_id,
3434 mp.compile_designator plan_name,
3435 msoi.line_number line_number,
3436 msc_get_name.lookup_meaning('SYS_YES_NO',msoi.return_status) atp_override_flag,
3437 msc_get_name.item_name(nvl(md.prev_subst_item,md.original_item_id),
3438 null,null,null)
3439 orig_item_name,
3440 msc_get_name.item_desc(nvl(md.prev_subst_item,md.original_item_id),
3441 nvl(md.prev_subst_org, md.original_org_id),
3442 md.plan_id,md.original_inst_id)
3443 orig_item_desc
3444 from msc_system_items msi,
3445 msc_plans mp,
3446 msc_demands md,
3447 msc_sales_order_interface msoi
3448 where msoi.batch_id = p_batch_id
3449 and msoi.sr_instance_id = p_instance_id
3450 and msoi.plan_id = md.plan_id
3451 and msoi.demand_id = md.demand_id
3452 and msoi.return_status is not null
3453 and msi.plan_id = md.plan_id
3454 and msi.organization_id = md.organization_id
3455 and msi.sr_instance_id = md.sr_instance_id
3456 and msi.inventory_item_id = md.inventory_item_id
3457 and mp.plan_id = msoi.plan_id;
3458
3459 all_rec all_c%ROWTYPE;
3460
3461 BEGIN
3462
3463 OPEN all_c;
3464 LOOP
3465 FETCH all_c INTO all_rec;
3466 EXIT WHEN all_c%NOTFOUND;
3467
3468 select to_char(mrp_form_query_s.nextval)
3469 into item_key
3470 from dual;
3471
3472 OPEN PLANNER_C(all_rec.plan_id,
3473 all_rec.inventory_item_id,
3474 all_rec.organization_id,
3475 all_rec.sr_instance_id);
3476 FETCH PLANNER_C INTO l_planner;
3477 CLOSE PLANNER_C;
3478
3479 if l_planner is not null then
3480 FND_FILE.PUT_LINE(FND_FILE.LOG,'notification will sent to planner '||l_planner);
3481 end if;
3482
3483 wf_engine.CreateProcess( itemtype => item_type,
3484 itemkey => item_key,
3485 process => l_process);
3486
3487 wf_engine.SetItemAttrNumber( itemtype => item_type,
3488 itemkey => item_key,
3489 aname => 'EXCEPTION_TYPE_ID',
3490 avalue => 70); -- new exception type
3491
3492 wf_engine.SetItemAttrNumber( itemtype => item_type,
3493 itemkey => item_key,
3494 aname => 'ORDER_TYPE_CODE',
3495 avalue => -30); -- from release so
3496
3497 wf_engine.SetItemAttrNumber( itemtype => item_type,
3498 itemkey => item_key,
3499 aname => 'TRANSACTION_ID',
3500 avalue => all_rec.demand_id);
3501
3502 wf_engine.SetItemAttrNumber( itemtype => item_type,
3503 itemkey => item_key,
3504 aname => 'INVENTORY_ITEM_ID',
3505 avalue => all_rec.inventory_item_id);
3506
3507 wf_engine.SetItemAttrNumber( itemtype => item_type,
3508 itemkey => item_key,
3509 aname => 'ORGANIZATION_ID',
3510 avalue => all_rec.organization_id);
3511
3512 wf_engine.SetItemAttrNumber( itemtype => item_type,
3513 itemkey => item_key,
3514 aname => 'INSTANCE_ID',
3515 avalue => all_rec.sr_instance_id);
3516
3517 wf_engine.SetItemAttrNumber( itemtype => item_type,
3518 itemkey => item_key,
3519 aname => 'SUPPLIER_ID',
3520 avalue => p_batch_id);
3521
3522 wf_engine.SetItemAttrNumber( itemtype => item_type,
3523 itemkey => item_key,
3524 aname => 'PLAN_ID',
3525 avalue => all_rec.plan_id);
3526
3527 wf_engine.SetItemAttrNumber( itemtype => item_type,
3528 itemkey => item_key,
3529 aname => 'PRE_PRSNG_LEAD_TIME',
3530 avalue => all_rec.old_lead_time);
3531
3532 wf_engine.SetItemAttrNumber( itemtype => item_type,
3533 itemkey => item_key,
3534 aname => 'POST_PRSNG_LEAD_TIME',
3535 avalue => all_rec.new_lead_time);
3536
3537 wf_engine.SetItemAttrText( itemtype => item_type,
3538 itemkey => item_key,
3539 aname => 'ORGANIZATION_CODE',
3540 avalue => all_rec.org_code);
3541
3542 wf_engine.SetItemAttrText( itemtype => item_type,
3543 itemkey => item_key,
3544 aname => 'PLAN_NAME',
3545 avalue => all_rec.plan_name);
3546
3547 wf_engine.SetItemAttrText( itemtype => item_type,
3548 itemkey => item_key,
3549 aname => 'DEPARTMENT_LINE_CODE',
3550 avalue => all_rec.to_org);
3551
3552 wf_engine.SetItemAttrText( itemtype => item_type,
3553 itemkey => item_key,
3554 aname => 'ITEM_DISPLAY_NAME',
3555 avalue => all_rec.item_name);
3556
3557 wf_engine.SetItemAttrText( itemtype => item_type,
3558 itemkey => item_key,
3559 aname => 'ITEM_DESCRIPTION',
3560 avalue => all_rec.item_desc);
3561
3562 wf_engine.SetItemAttrDate( itemtype => item_type,
3563 itemkey => item_key,
3564 aname => 'FROM_DATE',
3565 avalue => all_rec.old_ship_date);
3566
3567 wf_engine.SetItemAttrDate( itemtype => item_type,
3568 itemkey => item_key,
3569 aname => 'TO_DATE',
3570 avalue => all_rec.new_ship_date);
3571
3572 wf_engine.SetItemAttrDate( itemtype => item_type,
3573 itemkey => item_key,
3574 aname => 'DATE1',
3575 avalue => all_rec.old_arrival_date);
3576
3577 wf_engine.SetItemAttrDate( itemtype => item_type,
3578 itemkey => item_key,
3579 aname => 'DATE2',
3580 avalue => all_rec.new_arrival_date);
3581
3582 wf_engine.SetItemAttrDate( itemtype => item_type,
3583 itemkey => item_key,
3584 aname => 'DATE3',
3585 avalue => all_rec.earliest_ship_date);
3586
3587 wf_engine.SetItemAttrText( itemtype => item_type,
3588 itemkey => item_key,
3589 aname => 'CUSTOMER_NAME',
3590 avalue => all_rec.customer_name);
3591
3592 wf_engine.SetItemAttrText( itemtype => item_type,
3593 itemkey => item_key,
3594 aname => 'RESOURCE_CODE',
3595 avalue => all_rec.customer_site);
3596
3597 wf_engine.SetItemAttrText( itemtype => item_type,
3598 itemkey => item_key,
3599 aname => 'SUPPLIER_NAME',
3600 avalue => all_rec.old_ship_method);
3601
3602 wf_engine.SetItemAttrText( itemtype => item_type,
3603 itemkey => item_key,
3604 aname => 'SUPPLIER_SITE_CODE',
3605 avalue => all_rec.new_ship_method);
3606
3607 wf_engine.SetItemAttrText( itemtype => item_type,
3608 itemkey => item_key,
3609 aname => 'ORDER_NUMBER',
3610 avalue => all_rec.order_number);
3611
3612 wf_engine.SetItemAttrText( itemtype => item_type,
3613 itemkey => item_key,
3614 aname => 'LOT_NUMBER',
3615 avalue => all_rec.line_number);
3616
3617 wf_engine.SetItemAttrText( itemtype => item_type,
3618 itemkey => item_key,
3619 aname => 'PLANNING_GROUP',
3620 avalue => all_rec.atp_override_flag);
3621
3622 wf_engine.SetItemAttrText( itemtype => item_type,
3623 itemkey => item_key,
3624 aname => 'END_ITEM_DISPLAY_NAME',
3625 avalue => all_rec.orig_item_name);
3626
3627 wf_engine.SetItemAttrText( itemtype => item_type,
3628 itemkey => item_key,
3629 aname => 'END_ITEM_DESCRIPTION',
3630 avalue => all_rec.orig_item_desc);
3631
3632 wf_engine.StartProcess( itemtype => item_type,
3633 itemkey => item_key);
3634 FND_FILE.PUT_LINE(FND_FILE.LOG,'item_key='||item_key||', item_type='||item_type);
3635
3636 END LOOP;
3637 CLOSE all_c;
3638
3639 END start_so_release_workflow;
3640
3641 FUNCTION date_offset(p_org_id number, p_instance_id number,
3642 p_bucket_type number,
3643 p_date date, p_offset_days number) return date is
3644 p_new_date date;
3645 p_new_offset number;
3646 p_minutes number := 0;
3647 BEGIN
3648 -- 6142627, msc_calendar.date_offset will round up offset_days
3649 if ceil(p_offset_days) <> p_offset_days then
3650 p_new_offset := floor(p_offset_days);
3651 else
3652 p_new_offset := p_offset_days;
3653 end if;
3654
3655 p_new_date := msc_calendar.date_offset(
3656 p_org_id,
3657 p_instance_id,
3658 p_bucket_type,
3659 p_date,
3660 p_new_offset);
3661
3662 --dbms_output.put_line('p_new_date='||p_new_date);
3663
3664 -- msc_calendar.date_offset will remove the timestamp
3665 if to_char(p_date,'HH24:MI:SS') <> '00:00:00' then
3666 p_minutes := (p_date - trunc(p_date)) *24*60;
3667 --dbms_output.put_line('timestamp: p_minutes='||p_minutes);
3668 end if;
3669
3670 if p_new_offset <> p_offset_days then
3671 -- need to calculate the partial day offset in minutes
3672 p_minutes := p_minutes +
3673 (p_offset_days - p_new_offset) *24*60;
3674 --dbms_output.put_line('partial offset: p_minutes='||p_minutes);
3675 end if;
3676
3677 if p_minutes > 0 then
3678 if p_minutes >= 24*60 then
3679 -- greater than one day, should find the next working day
3680 p_new_date :=
3681 msc_calendar.date_offset(
3682 p_org_id,
3683 p_instance_id,
3684 p_bucket_type,
3685 p_new_date,
3686 1);
3687 p_minutes := p_minutes - 24*60;
3688 --dbms_output.put_line('partial more than a day: p_minutes='||p_minutes||',p_new_date='||p_new_date);
3689 end if; -- if p_minutes >= 24*60
3690 p_new_date := p_new_date + ceil(p_minutes)/(24*60);
3691 --dbms_output.put_line('p_minutes='||p_minutes||',p_new_date='||p_new_date);
3692 end if; -- if p_minutes > 0 then
3693
3694 return p_new_date;
3695 END date_offset;
3696
3697 END msc_rel_wf;