[Home] [Help]
PACKAGE BODY: APPS.MSC_REL_WF
Source
1 PACKAGE BODY msc_rel_wf AS
2 /*$Header: MSCRLWFB.pls 120.11 2008/01/07 18:45:19 eychen 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 ) RETURN date IS
688
689 dock_date Date;
690
691 BEGIN
692
693 -- first use receiving calendar to offset the post processing lead time
694
695 dock_date := msc_rel_wf.get_offset_date(p_receiving_calendar,
696 p_instance_id,
697 -1*p_lead_time, p_implement_date);
698
699 dock_date := msc_drp_util.get_work_day('PREV',p_receiving_calendar,
700 p_instance_id,dock_date);
701
702 -- then find the working date using receiving calendar,
703 if p_delivery_calendar is not null then
704 dock_date := msc_drp_util.get_work_day('PREV',p_delivery_calendar,
705 p_instance_id,dock_date);
706
707 end if;
708 if dock_date < sysdate then
709 dock_date := sysdate;
710 end if;
711 RETURN(dock_date);
712 END GET_DOCK_DATE;
713
714 PROCEDURE reschedule_purchase_orders
715 ( arg_plan_id IN NUMBER
716 , arg_org_id IN NUMBER
717 , arg_instance IN NUMBER
718 , arg_owning_org IN NUMBER
719 , arg_owning_instance IN NUMBER
720 , arg_count OUT NOCOPY NUMBER
721 , arg_released_instance IN OUT NOCOPY NumTblTyp
722 , arg_po_res_id IN OUT NOCOPY NumTblTyp
723 , arg_po_res_count IN OUT NOCOPY NumTblTyp
724 , arg_po_pwb_count IN OUT NOCOPY NumTblTyp) IS
725
726 p_user_id number := FND_PROFILE.value('USER_ID');
727 p_release_by_user varchar2(3) :=
728 nvl(FND_PROFILE.value('MSC_RELEASED_BY_USER_ONLY'),'N');
729
730 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
731 TYPE DateTab IS TABLE OF Date INDEX BY BINARY_INTEGER;
732 TYPE CharTab IS TABLE OF varchar2(240) INDEX BY BINARY_INTEGER;
733
734 p_new_need_by_date DateTab;
735 p_old_need_by_date DateTab;
736 p_po_line_id NumTab;
737 p_po_header_id NumTab;
738 p_po_number CharTab;
739 p_po_quantity NumTab;
740 p_po_instance_id NumTab;
741 p_plan_id NumTab;
742 p_action NumTab;
743 p_shipment_id NumTab;
744 p_distribution_id NumTab;
745 p_operating_unit numtab;
746 p_dest_dblink varchar2(128);
747
748 -- xml fix
749 p_source_line_id NumTab;
750 p_uom_code CharTab;
751
752 CURSOR instance_cur IS
753 select distinct mp.sr_instance_id,
754 decode(mai.M2A_dblink,null,' ','@'||mai.M2A_dblink),
755 nvl(mai.A2M_dblink, '-1'),
756 mai.instance_code
757 from msc_plan_organizations mp,
758 msc_apps_instances mai
759 where plan_id = arg_plan_id
760 and mp.sr_instance_id = mai.instance_id
761 and mai.instance_type <> 3 -- xml fix
762 and nvl(mai.apps_ver,1) <> 1; -- not back port to 107 yet
763
764 CURSOR leg_instance_cur IS
765 select distinct mp.sr_instance_id
766 from msc_plan_organizations mp,
767 msc_apps_instances mai
768 where plan_id = arg_plan_id
769 and mp.sr_instance_id = mai.instance_id
770 and mai.instance_type = 3; -- xml fix
771
772 p_user_name varchar2(30) :=FND_GLOBAL.USER_NAME; --FND_PROFILE.VALUE('USERNAME');
773 p_resp_name varchar2(80) :=FND_GLOBAL.RESP_NAME;
774 p_dblink varchar2(128);
775 p_instance_id number;
776 p_instance_code varchar2(3);
777 lv_sql_stmt varchar2(2000);
778 p_request_id number:=0;
779 v_batch_id number;
780 v_temp number;
781 v_temp2 number;
782 v_autorelease number;
783
784 CURSOR c_plan_type(p_plan_id number) IS
785 select plan_type
786 from msc_plans a
787 where
788 plan_id = p_plan_id;
789
790 p_plan_type NUMBER;
791 BEGIN
792
793 arg_count :=0;
794
795 OPEN c_plan_type(arg_plan_id);
796 FETCH c_plan_type INTO p_plan_type;
797 CLOSE c_plan_type;
798
799 begin -- xml fix
800
801 SELECT s.sr_instance_id,
802 nvl(s.promised_date,s.need_by_date) old_need_by_date,
803 min(get_dock_date(s.sr_instance_id,
804 s.receiving_calendar,
805 s.intransit_calendar,
806 NVL(s.implement_date,s.new_schedule_date),
807 NVL(msi.postprocessing_lead_time,0))) new_need_by_date,
808 s.disposition_id po_header_id,
809 s.po_line_id po_line_id,
810 s.order_number po_number,
811 min(s.implement_quantity) qty,
812 s.po_line_location_id shipment_id,
813 s.po_distribution_id distribution_id,
814 nvl(s.implement_uom_code,msi.uom_code) uom,
815 mp.operating_unit operating_unit,
816 s.disposition_status_type action
817 BULK COLLECT INTO
818 p_po_instance_id,
819 p_old_need_by_date,
820 p_new_need_by_date,
821 p_po_header_id,
822 p_po_line_id,
823 p_po_number,
824 p_po_quantity,
825 p_shipment_id,
826 p_distribution_id,
827 p_uom_code,
828 p_operating_unit,
829 p_action
830 FROM msc_apps_instances mai, -- xml fix
831 msc_system_items msi,
832 msc_trading_partners mp,
833 msc_supplies s
834 WHERE msi.inventory_item_id = s.inventory_item_id
835 AND msi.plan_id = s.plan_id
836 AND msi.organization_id = s.organization_id
837 and msi.sr_instance_id = s.sr_instance_id
838 AND mp.sr_tp_id = msi.organization_id
839 AND mp.sr_instance_id = msi.sr_instance_id
840 AND mp.partner_type= 3
841 and mai.instance_id = s.sr_instance_id -- xml fix
842 and mai.instance_type <> 3 -- xml fix- only for non legacy
843 AND s.plan_id = arg_plan_id
844 AND s.release_errors is NULL
845 and s.load_type = 20
846 and s.order_type = 1
847 and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
848 s.last_updated_by)
849 group by s.sr_instance_id,nvl(s.promised_date,s.need_by_date),
850 s.disposition_id,
851 s.po_line_id, s.order_number, s.disposition_status_type,
852 s.po_line_location_id, po_distribution_id,
853 nvl(s.implement_uom_code,msi.uom_code), mp.operating_unit;
854
855 select msc_form_query_s.nextval
856 into v_batch_id
857 from dual;
858
859 forall a in 1..p_po_instance_id.count
860 insert into msc_purchase_order_interface
861 (last_update_date,
862 last_updated_by,
863 creation_date,
864 created_by,
865 batch_id,
866 sr_instance_id,
867 old_need_by_date,
868 new_need_by_date,
869 po_header_id,
870 po_line_id,
871 po_number,
872 po_quantity,
873 action,
874 po_line_location_id,
875 po_distribution_id,
876 uom,
877 operating_unit)
878 values
879 (sysdate,
880 p_user_id,
881 sysdate,
882 p_user_id,
883 v_batch_id,
884 p_po_instance_id(a),
885 p_old_need_by_date(a),
886 p_new_need_by_date(a),
887 p_po_header_id(a),
888 p_po_line_id(a),
889 p_po_number(a),
890 p_po_quantity(a),
891 p_action(a),
892 p_shipment_id(a),
893 p_distribution_id(a),
894 p_uom_code(a),
895 p_operating_unit(a));
896
897 commit;
898 exception
899 when VALUE_ERROR then
900 null;
901 when COLLECTION_IS_NULL then
902 null;
903 when NO_DATA_FOUND then
904 null;
905 end;
906
907 -- xml fix : for legacy instances insert into msc_po_reschedule_interface
908
909 begin
910
911 SELECT s.sr_instance_id,
912 s.transaction_id, -- xml fix
913 nvl(s.implement_uom_code,msi.uom_code), -- xml fix
914 nvl(s.promised_date,s.need_by_date) old_need_by_date,
915 get_dock_date(s.sr_instance_id,
916 s.receiving_calendar,
917 s.intransit_calendar,
918 NVL(s.implement_date,
919 s.new_schedule_date),
920 NVL(msi.postprocessing_lead_time, 0)) new_need_by_date,
921 s.disposition_id po_header_id,
922 s.po_line_id po_line_id,
923 s.order_number po_number,
924 min(s.implement_quantity) qty,
925 s.plan_id
926 BULK COLLECT INTO
927 p_po_instance_id,
928 p_source_line_id, -- xml fix
929 p_uom_code, -- xml fix
930 p_old_need_by_date,
931 p_new_need_by_date,
932 p_po_header_id,
933 p_po_line_id,
934 p_po_number,
935 p_po_quantity,
936 p_plan_id
937 FROM msc_apps_instances mai,
938 msc_system_items msi,
939 msc_supplies s
940 WHERE msi.inventory_item_id = s.inventory_item_id
941 AND msi.plan_id = s.plan_id
942 AND msi.organization_id = s.organization_id
943 and msi.sr_instance_id = s.sr_instance_id
944 and mai.instance_id = s.sr_instance_id -- xml fix
945 and mai.instance_type = 3 -- xml fix
946 AND s.plan_id = arg_plan_id
947 AND s.release_errors is NULL
948 and s.load_type = 20
949 and s.order_type = 1
950 and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
951 s.last_updated_by)
952 group by s.sr_instance_id, s.transaction_id,
953 nvl(s.implement_uom_code,msi.uom_code),
954 nvl(s.promised_date,s.need_by_date),
955 get_dock_date(s.sr_instance_id,
956 s.receiving_calendar,
957 s.intransit_calendar,
958 NVL(s.implement_date,
959 s.new_schedule_date),
960 NVL(msi.postprocessing_lead_time, 0)),
961 s.disposition_id,s.po_line_id, s.order_number,s.plan_id;
962
963 forall a in 1..p_po_instance_id.count
964 INSERT INTO msc_po_reschedule_interface
965 (process_id,
966 quantity,
967 need_by_date,
968 line_id,
969 last_update_date,
970 last_updated_by,
971 creation_date,
972 created_by,
973 purchase_order_id,
974 po_number,
975 source_line_id,
976 uom,
977 SR_INSTANCE_ID,
978 plan_id)
979 VALUES (
980 NULL,
981 p_po_quantity(a),
982 p_new_need_by_date(a),
983 p_po_line_id(a),
984 SYSDATE,
985 p_user_id,
986 SYSDATE,
987 p_user_id,
988 p_po_header_id(a),
989 p_po_number(a),
990 p_source_line_id(a),
991 p_uom_code(a),
992 p_po_instance_id(a),
993 p_plan_id(a));
994
995 commit;
996
997 -- send xml
998 for cur in leg_instance_cur loop
999
1000 v_temp :=0;
1001
1002 select count(*)
1003 into v_temp
1004 from msc_po_reschedule_interface
1005 where sr_instance_id = cur.sr_instance_id;
1006
1007 if v_temp > 0 then
1008 arg_po_res_id.extend(1);
1009 arg_released_instance.extend(1);
1010 arg_po_res_count.extend(1);
1011 arg_po_pwb_count.extend(1);
1012 arg_count := arg_count+1;
1013
1014 lv_sql_stmt :=
1015 ' BEGIN'
1016 ||' MSC_A2A_XML_WF.LEGACY_RELEASE (:p_arg_org_instance);'
1017 ||' END;';
1018
1019 EXECUTE IMMEDIATE lv_sql_stmt USING cur.sr_instance_id;
1020
1021 arg_po_res_id(arg_count) := 0;
1022 arg_released_instance(arg_count) := cur.sr_instance_id;
1023 arg_po_res_count(arg_count) := v_temp;
1024
1025 select count(*)
1026 into v_temp2
1027 from msc_supplies
1028 where plan_id = arg_plan_id
1029 anD release_errors is NULL
1030 and load_type = 20
1031 and order_type = 1
1032 and sr_instance_id = cur.sr_instance_id
1033 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1034 last_updated_by);
1035
1036 arg_po_pwb_count(arg_count) := v_temp2;
1037
1038 end if;
1039 end loop;
1040
1041 exception
1042 when VALUE_ERROR then
1043 null;
1044 when COLLECTION_IS_NULL then
1045 null;
1046 when NO_DATA_FOUND then
1047 null;
1048 end;
1049
1050 OPEN instance_cur;
1051 LOOP
1052 FETCH instance_cur INTO p_instance_id, p_dblink, p_dest_dblink,p_instance_code;
1053 EXIT WHEN instance_cur%NOTFOUND;
1054 v_temp :=0;
1055 select count(*)
1056 into v_temp
1057 from msc_purchase_order_interface
1058 where sr_instance_id = p_instance_id
1059 and batch_id = v_batch_id;
1060 if v_temp > 0 then
1061 arg_po_res_id.extend(1);
1062 arg_released_instance.extend(1);
1063 arg_po_res_count.extend(1);
1064 arg_po_pwb_count.extend(1);
1065 arg_count := arg_count+1;
1066
1067 IF p_plan_type = 5 THEN
1068 begin
1069 lv_sql_stmt :=
1070 'BEGIN ' ||
1071 'MRP_PO_RESCHEDULE.LAUNCH_RESCHEDULE_PO'||p_dblink||
1072 '(:lv_user, :lv_resp, :v_batch_id,:p_instance_id,:p_instance_code,:p_dest_dblink, :arg_req_resched_id); ' ||
1073 'END;';
1074 EXECUTE IMMEDIATE lv_sql_stmt
1075 USING
1076 IN p_user_name,
1077 IN 'Advanced Supply Chain Planner',
1078 --IN p_resp_name,
1079 IN v_batch_id,
1080 IN p_instance_id,
1081 IN p_instance_code,
1082 IN p_dest_dblink,
1083 OUT p_request_id;
1084 EXCEPTION WHEN OTHERS THEN
1085 p_request_id := 0;
1086 END;
1087 ELSE
1088 begin
1089 lv_sql_stmt:=
1090 'BEGIN'
1091 ||' mrp_rel_wf.launch_po_program'||p_dblink||'('
1092 ||' :lv_old_need_by_date,'
1093 ||' :lv_new_need_by_date,'
1094 ||' :lv_po_header_id,'
1095 ||' :lv_po_line_id,'
1096 ||' :lv_po_number,'
1097 ||' :lv_user,'
1098 ||' :lv_resp,'
1099 ||' :lv_qty,'
1100 ||' :out);'
1101 ||' END;';
1102 EXECUTE IMMEDIATE lv_sql_stmt
1103 USING
1104 IN sysdate,
1105 IN sysdate,
1106 IN v_batch_id,
1107 IN p_instance_id,
1108 IN p_dest_dblink,
1109 IN p_user_name,
1110 IN p_resp_name,
1111 IN 1,
1112 OUT p_request_id;
1113 -- dbms_output.put_line(p_dest_dblink||','||p_instance_id||','||v_batch_id);
1114 exception when others then
1115 EXECUTE IMMEDIATE lv_sql_stmt
1116 USING
1117 IN sysdate,
1118 IN sysdate,
1119 IN v_batch_id,
1120 IN p_instance_id,
1121 IN p_dest_dblink,
1122 IN p_user_name,
1123 IN p_resp_name,
1124 IN 1,
1125 OUT p_request_id;
1126 end;
1127 END IF;
1128 if p_request_id <> 0 then
1129 commit;
1130 end if;
1131
1132 arg_po_res_id(arg_count) := p_request_id;
1133 arg_released_instance(arg_count) := p_instance_id;
1134 arg_po_res_count(arg_count) := v_temp;
1135
1136 select count(*)
1137 into v_temp2
1138 from msc_supplies
1139 where plan_id = arg_plan_id
1140 AND release_errors is NULL
1141 and load_type = 20
1142 and order_type = 1
1143 and sr_instance_id = p_instance_id
1144 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1145 last_updated_by);
1146
1147 arg_po_pwb_count(arg_count) := v_temp2;
1148
1149 end if;
1150 END LOOP;
1151 CLOSE instance_cur;
1152
1153 /* for auto-release we do not want applied and status to be updated */
1154
1155 SELECT release_reschedules
1156 INTO v_autorelease
1157 FROM msc_plans
1158 WHERE plan_id=arg_plan_id;
1159
1160 UPDATE MSC_SUPPLIES
1161 SET implement_date = NULL,
1162 release_status = NULL,
1163 load_type = NULL,
1164 applied = decode(v_autorelease,1,applied,2),
1165 status = decode(v_autorelease,1,status,0)
1166 WHERE plan_id= arg_plan_id
1167 and release_errors is NULL
1168 and load_type = 20
1169 and order_type =1
1170 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1171 last_updated_by);
1172
1173 commit;
1174
1175 END reschedule_purchase_orders;
1176
1177 PROCEDURE release_sales_orders
1178 ( arg_plan_id IN NUMBER
1179 , arg_org_id IN NUMBER
1180 , arg_instance IN NUMBER
1181 , arg_owning_org IN NUMBER
1182 , arg_owning_instance IN NUMBER
1183 , arg_released_instance IN OUT NOCOPY NumTblTyp
1184 , arg_so_rel_id IN OUT NOCOPY NumTblTyp
1185 , arg_so_rel_count IN OUT NOCOPY NumTblTyp
1186 , arg_so_pwb_count IN OUT NOCOPY NumTblTyp) IS
1187
1188 -- p_user_id number := FND_PROFILE.value('USER_ID');
1189 p_user_id number := FND_GLOBAL.USER_ID;
1190 p_resp_id number := FND_GLOBAL.RESP_ID;
1191 p_release_by_user varchar2(3) :=
1192 nvl(FND_PROFILE.value('MSC_RELEASED_BY_USER_ONLY'),'N');
1193
1194 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1195 TYPE DateTab IS TABLE OF Date INDEX BY BINARY_INTEGER;
1196 TYPE CharTab IS TABLE OF varchar2(240) INDEX BY BINARY_INTEGER;
1197
1198 p_earliest_ship_date DateTab;
1199 p_ship_date DateTab;
1200 p_arrival_date datetab;
1201 p_ship_method CharTab;
1202 p_lead_time numTab;
1203 p_operating_unit NumTab;
1204 p_so_line_id NumTab;
1205 p_so_org_id NumTab;
1206 p_so_instance_id NumTab;
1207 p_instance_id NumTab;
1208 p_so_header_id NumTab;
1209 p_demand_id NumTab;
1210 p_order_number CharTab;
1211 p_source_type NumTab;
1212 p_orig_ship_date DateTab;
1213 p_orig_arrival_date datetab;
1214 p_orig_ship_method CharTab;
1215 p_orig_lead_time numTab;
1216 p_orig_org_id NumTab;
1217 p_qty NumTab;
1218 p_implement_firm NumTab;
1219 p_original_item_id NumTab;
1220 p_substitute_item_id NumTab;
1221 p_org_id NumTab;
1222 a number;
1223
1224 CURSOR instance_cur IS
1225 select distinct mp.sr_instance_id,
1226 decode(mai.M2A_dblink,null,' ','@'||mai.M2A_dblink),
1227 decode(mai.A2M_dblink,null,' ','@'||mai.A2M_dblink)
1228 from msc_plan_organizations mp,
1229 msc_apps_instances mai
1230 where plan_id = arg_plan_id
1231 and mp.sr_instance_id = mai.instance_id
1232 -- and mai.instance_type <> 3 -- xml fix
1233 and nvl(mai.apps_ver,1) <> 1; -- not back port to 107 yet
1234
1235 -- p_user_name varchar2(30) :=FND_PROFILE.VALUE('USERNAME');
1236 p_user_name varchar2(80) :=FND_GLOBAL.USER_NAME;
1237 p_resp_name varchar2(80) :=FND_GLOBAL.RESP_NAME;
1238 p_dblink varchar2(128);
1239 p_aps_dblink varchar2(128);
1240 p_inst_id number;
1241 lv_sql_stmt varchar2(2000);
1242 p_request_id number:=0;
1243 v_batch_id number;
1244 v_temp number;
1245 v_temp2 number;
1246 crm_char varchar2(100);
1247 TYPE CRMCurTyp IS REF CURSOR;
1248 crm_cur CRMCurTyp;
1249 p_count number;
1250 v_item_id number;
1251
1252 CURSOR source_item_c(l_inst_id number, l_org_id number,
1253 l_item_id number) IS
1254 select sr_inventory_item_id
1255 from msc_system_items msi
1256 where msi.plan_id = arg_plan_id
1257 and msi.organization_id =l_org_id
1258 and msi.sr_instance_id = l_inst_id
1259 and msi.inventory_item_id = l_item_id;
1260 BEGIN
1261
1262 p_count :=0;
1263
1264 begin
1265 SELECT md.sr_instance_id,
1266 NVL(md.sales_order_line_id,0),
1267 md.implement_org_id,
1268 md.implement_instance_id,
1269 md.implement_earliest_date, -- Earliest ship date
1270 md.implement_ship_date,
1271 nvl(md.implement_arrival_date,md.schedule_ship_date),
1272 mtp.operating_unit,
1273 md.demand_id,
1274 md.ship_method,
1275 NVL(md.intransit_lead_time,0),
1276 nvl(md.implement_firm,2),
1277 NVL(nvl(md.prev_subst_org,md.original_org_id),
1278 md.organization_id),
1279 md.schedule_arrival_date,
1280 md.schedule_ship_date,
1281 md.orig_shipping_method_code,
1282 NVL(md.orig_intransit_lead_time,0),
1283 md.order_number,
1284 decode(md.customer_id, null, 100, to_number(null)),
1285 decode(md.customer_id, null,
1286 nvl(md.quantity_by_due_date,
1287 md.using_requirement_quantity),
1288 md.using_requirement_quantity),
1289 nvl(nvl(md.prev_subst_item,md.original_item_id),
1290 md.inventory_item_id),
1291 md.inventory_item_id,
1292 md.organization_id
1293 BULK COLLECT INTO
1294 p_instance_id,
1295 p_so_line_id,
1296 p_so_org_id,
1297 p_so_instance_id,
1298 p_earliest_ship_date,
1299 p_ship_date,
1300 p_arrival_date,
1301 p_operating_unit,
1302 p_demand_id,
1303 p_ship_method,
1304 p_lead_time,
1305 p_implement_firm,
1306 p_orig_org_id,
1307 p_orig_arrival_date,
1308 p_orig_ship_date,
1309 p_orig_ship_method,
1310 p_orig_lead_time,
1311 p_order_number,
1312 p_source_type,
1313 p_qty,
1314 p_original_item_id,
1315 p_substitute_item_id,
1316 p_org_id
1317 FROM msc_demands md,
1318 msc_trading_partners mtp
1319 WHERE md.plan_id = arg_plan_id
1320 AND md.release_errors is NULL
1321 and md.load_type = 30
1322 and md.origination_type = 30
1323 and md.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1324 md.last_updated_by)
1325 AND mtp.sr_tp_id = md.organization_id
1326 AND mtp.sr_instance_id = md.sr_instance_id
1327 AND mtp.partner_type= 3
1328 order by mtp.operating_unit, md.order_number, md.arrival_set_id,md.ship_set_id;
1329
1330 select msc_form_query_s.nextval
1331 into v_batch_id
1332 from dual;
1333
1334 for a in 1..p_so_instance_id.count loop
1335
1336 v_item_id := p_substitute_item_id(a);
1337 OPEN source_item_c(p_instance_id(a), p_org_id(a),v_item_id);
1338 FETCH source_item_c INTO p_substitute_item_id(a);
1339 CLOSE source_item_c;
1340
1341 IF p_original_item_id(a) <> v_item_id THEN
1342 v_item_id := p_original_item_id(a);
1343 OPEN source_item_c(p_instance_id(a), p_orig_org_id(a),v_item_id);
1344 FETCH source_item_c INTO p_original_item_id(a);
1345 CLOSE source_item_c;
1346 ELSE
1347 p_original_item_id(a) := p_substitute_item_id(a);
1348 END IF;
1349
1350 insert into msc_sales_order_interface
1351 (last_update_date,
1352 last_updated_by,
1353 creation_date,
1354 created_by,
1355 batch_id,
1356 sr_instance_id,
1357 line_id,
1358 operating_unit,
1359 header_id,
1360 org_id,
1361 schedule_ship_date,
1362 schedule_arrival_date,
1363 earliest_ship_date,
1364 delivery_lead_time,
1365 ship_method,
1366 orig_org_id,
1367 orig_schedule_ship_date,
1368 orig_schedule_arrival_date,
1369 orig_lead_time,
1370 orig_ship_method,
1371 quantity,
1372 firm_flag,
1373 source_type,
1374 order_number,
1375 demand_id,
1376 plan_id,
1377 orig_item_id,
1378 inventory_item_id)
1379 values
1380 (sysdate,
1381 p_user_id,
1382 sysdate,
1383 p_user_id,
1384 v_batch_id,
1385 p_instance_id(a),
1386 p_so_line_id(a),
1387 p_operating_unit(a),
1388 null, -- so_header_id: we don't hv this in destination
1389 p_so_org_id(a),
1390 p_ship_date(a),
1391 p_arrival_date(a),
1392 p_earliest_ship_date(a),
1393 p_lead_time(a),
1394 p_ship_method(a),
1395 p_orig_org_id(a),
1396 p_orig_ship_date(a),
1397 p_orig_arrival_date(a),
1398 p_orig_lead_time(a),
1399 p_orig_ship_method(a),
1400 p_qty(a),
1401 p_implement_firm(a),
1402 p_source_type(a),
1403 p_order_number(a),
1404 p_demand_id(a),
1405 arg_plan_id,
1406 p_original_item_id(a),
1407 p_substitute_item_id(a));
1408
1409 end loop;
1410
1411 commit;
1412 exception
1413 when VALUE_ERROR then
1414 null;
1415 when COLLECTION_IS_NULL then
1416 null;
1417 when NO_DATA_FOUND then
1418 null;
1419 end;
1420
1421 OPEN instance_cur;
1422 LOOP
1423 FETCH instance_cur INTO p_inst_id, p_dblink, p_aps_dblink;
1424 EXIT WHEN instance_cur%NOTFOUND;
1425 v_temp :=0;
1426 select count(*)
1427 into v_temp
1428 from msc_sales_order_interface
1429 where sr_instance_id = p_inst_id
1430 and batch_id = v_batch_id;
1431 if v_temp > 0 then
1432 arg_so_rel_id.extend(1);
1433 arg_released_instance.extend(1);
1434 arg_so_rel_count.extend(1);
1435 arg_so_pwb_count.extend(1);
1436 p_count := p_count+1;
1437
1438 begin
1439 -- customer_id could be null for reqular so,
1440 -- but order_number for CRMO always ends with 'CMRO'
1441 lv_sql_stmt:= 'select meaning from fnd_lookups'||p_dblink||
1442 ' where lookup_type = :p_type '||
1443 ' and lookup_code = :p_code ';
1444
1445 OPEN crm_cur FOR lv_sql_stmt USING 'AHL_APS_APPLICATION', 'CMRO';
1446 FETCH crm_cur INTO crm_char;
1447 CLOSE crm_cur;
1448
1449 -- if customer_id is null but order_number not ends with 'CMRO',
1450 -- it is not a CMRO
1451 UPDATE msc_sales_order_interface
1452 set source_type = null,
1453 quantity = null
1454 where source_type =100 -- customer_id is null
1455 and order_number not like '%'||crm_char
1456 and sr_instance_id = p_inst_id
1457 and batch_id = v_batch_id;
1458 commit;
1459
1460 lv_sql_stmt:=
1461 'BEGIN'
1462 ||' mrp_rel_wf.launch_so_program'||p_dblink||'('
1463 ||' :batch_id,'
1464 ||' :db_link,'
1465 ||' :instance_id,'
1466 ||' :lv_user,'
1467 ||' :lv_resp,'
1468 ||' :out);'
1469 ||' END;';
1470
1471 MSC_LOG.string ( FND_LOG.LEVEL_ERROR,
1472 'msc_rel_wf.release_sales_orders',
1473 'sql statement is : '||lv_sql_stmt);
1474
1475
1476 EXECUTE IMMEDIATE lv_sql_stmt
1477 USING
1478 IN v_batch_id,
1479 IN p_aps_dblink,
1480 IN p_inst_id,
1481 IN p_user_name,
1482 IN p_resp_name,
1483 OUT p_request_id;
1484 exception when others then
1485 raise;
1486 end;
1487 if p_request_id <> 0 then
1488 commit;
1489 end if;
1490
1491 arg_so_rel_id(p_count) := p_request_id;
1492 arg_released_instance(p_count) := p_inst_id;
1493 arg_so_rel_count(p_count) := v_temp;
1494
1495 v_temp2 :=0;
1496
1497 select count(*)
1498 into v_temp2
1499 from msc_demands
1500 where plan_id = arg_plan_id
1501 anD release_errors is NULL
1502 and load_type = 30
1503 and sr_instance_id = p_inst_id
1504 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1505 last_updated_by);
1506
1507 arg_so_pwb_count(p_count) := v_temp2;
1508
1509 end if; -- if v_temp >0 then
1510 END LOOP;
1511 CLOSE instance_cur;
1512
1513 UPDATE MSC_DEMANDS
1514 SET /* implement_date = NULL,
1515 implement_ship_date = NULL,
1516 implement_earliest_date = NULL,
1517 implement_arrival_date = NULL,
1518 implement_org_id = NULL,
1519 implement_instance_id = NULL,
1520 implement_firm = null, */
1521 release_status = NULL,
1522 load_type = NULL,
1523 applied = 2,
1524 status =0
1525 WHERE plan_id= arg_plan_id
1526 and release_errors is NULL
1527 and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
1528 last_updated_by)
1529 and load_type = 30;
1530 commit;
1531
1532 END release_sales_orders;
1533
1534 Function get_job_seq_from_source(p_instance_id number) RETURN number IS
1535 CURSOR db_cur IS
1536 select decode(M2A_dblink,null,' ','@'||M2A_dblink)
1537 from msc_apps_instances
1538 where instance_id = p_instance_id;
1539
1540 db_link varchar2(128);
1541 seq_num number;
1542 TYPE JobCurTyp IS REF CURSOR;
1543 JobCur JobCurTyp;
1544
1545 sql_stmt varchar2(200);
1546 BEGIN
1547 OPEN db_cur;
1548 FETCH db_cur INTO db_link;
1549 CLOSE db_cur;
1550
1551 sql_stmt := 'SELECT wip_job_number_s.nextval'||db_link||' from dual';
1552
1553 OPEN JobCur FOR sql_stmt;
1554 FETCH JobCur INTO seq_num;
1555 CLOSE JobCur;
1556
1557 return seq_num;
1558
1559 END get_job_seq_from_source;
1560
1561 FUNCTION is_pjm_valid(p_org_id NUMBER,
1562 p_project_id NUMBER,
1563 p_task_id NUMBER,
1564 p_start_date DATE,
1565 p_completion_date DATE,
1566 p_instance_id NUMBER) RETURN NUMBER IS
1567
1568 l_valid varchar2(80):= 'S';
1569 l_error varchar2(1000) := NULL;
1570
1571
1572 db_link varchar2(128);
1573
1574 sql_stmt varchar2(500);
1575
1576
1577 BEGIN
1578 msc_rel_wf.validate_proj_in_source( p_org_id,
1579 p_project_id,
1580 p_task_id,
1581 p_start_date,
1582 null, -- no date for completion date
1583 p_instance_id,
1584 l_valid,
1585 l_error);
1586
1587
1588
1589 IF l_valid = 'S' then
1590 return 1;
1591 ELSE
1592 return 0;
1593 END IF;
1594
1595 END is_pjm_valid;
1596
1597
1598
1599
1600 -- cnazarma
1601 PROCEDURE validate_proj_in_source(
1602 p_org_id NUMBER,
1603 p_project_id NUMBER,
1604 p_task_id NUMBER,
1605 p_start_date DATE,
1606 p_completion_date DATE,
1607 p_instance_id NUMBER,
1608 p_valid OUT NOCOPY VARCHAR2,
1609 p_error OUT NOCOPY VARCHAR2) IS
1610
1611
1612
1613 --l_user_name varchar2(30) :=FND_PROFILE.VALUE('USERNAME');
1614 l_user_name varchar2(30) := FND_GLOBAL.USER_NAME;
1615
1616
1617 CURSOR db_cur IS
1618 select decode(M2A_dblink,null,' ','@'||M2A_dblink),
1619 apps_ver
1620 from msc_apps_instances
1621 where instance_id = p_instance_id;
1622
1623 db_link varchar2(128);
1624 apps_version number;
1625
1626 sql_stmt varchar2(500);
1627 v_valid VARCHAR2(80);
1628
1629 l_user_id number := FND_GLOBAL.USER_ID;
1630 l_resp_id number := FND_GLOBAL.RESP_ID;
1631 l_application_id number;
1632
1633 BEGIN
1634
1635
1636
1637 OPEN db_cur;
1638 FETCH db_cur INTO db_link, apps_version;
1639 CLOSE db_cur;
1640
1641 -- remember the initial context
1642 -- because validate_pjm will change the context to PJM oper unit
1643 SELECT APPLICATION_ID
1644 INTO l_application_id
1645 FROM FND_APPLICATION_VL
1646 WHERE APPLICATION_SHORT_NAME = 'MSC'
1647 and rownum =1 ;
1648
1649 -- calling validate_pjm for 11.5 sources only
1650 if apps_version > 2 then
1651 sql_stmt :=
1652 'BEGIN mrp_rel_wf.validate_pjm'||db_link||
1653 '( :p_org,'||
1654 ' :p_project_id,'||
1655 ' :p_task_id,' ||
1656 ' :p_start_date,'||
1657 ' :p_completion_date,' ||
1658 ' :p_user_name,'||
1659 ' :p_valid,'||
1660 ' :p_error ); END; ';
1661 EXECUTE IMMEDIATE sql_stmt USING
1662 IN p_org_id,
1663 IN p_project_id,
1664 IN p_task_Id,
1665 IN p_start_date,
1666 IN p_completion_date,
1667 IN l_user_name,
1668 IN OUT p_valid,
1669 IN OUT p_error;
1670
1671 else
1672 p_valid := 'S';
1673 end if;
1674
1675 -- initialize context back to what it was initially
1676 fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
1677
1678
1679 exception when others then
1680 raise;
1681
1682 END validate_proj_in_source;
1683
1684
1685 FUNCTION get_acc_class_from_source(p_org_id number, p_item_id number,
1686 p_project_id number, p_instance_id number) RETURN varchar2 IS
1687 CURSOR db_cur IS
1688 select decode(M2A_dblink,null,' ','@'||M2A_dblink),apps_ver
1689
1690 from msc_apps_instances
1691 where instance_id = p_instance_id;
1692
1693 db_link varchar2(128);
1694
1695 v_err_mesg1 VARCHAR2(200);
1696 v_err_class1 VARCHAR2(200);
1697 v_err_mesg2 VARCHAR2(200);
1698 v_err_class2 VARCHAR2(200);
1699 v_default_acc_class varchar2(200);
1700 sql_stmt varchar2(500);
1701 v_apps_ver number;
1702 BEGIN
1703
1704 OPEN db_cur;
1705 FETCH db_cur INTO db_link,v_apps_ver;
1706 CLOSE db_cur;
1707
1708 IF v_apps_ver <> 1 THEN
1709 sql_stmt :=
1710 'BEGIN :v_class := wip_common.default_acc_class'||db_link||
1711 '( :p_org, :p_item, 1, :p_project,' ||
1712 ' :p_err_msg_1, :p_err_class_1,' ||
1713 ' :p_err_msg_2, :p_err_class_2); END; ';
1714
1715 EXECUTE IMMEDIATE sql_stmt USING
1716 OUT v_default_acc_class,
1717 IN p_org_id,
1718 IN p_item_id,
1719 IN p_project_Id,
1720 OUT v_err_mesg1,
1721 OUT v_err_class1,
1722 OUT v_err_mesg2,
1723 OUT v_err_class2;
1724 ELSE
1725 sql_stmt := 'SELECT DEFAULT_DISCRETE_CLASS
1726 FROM WIP_PARAMETERS'||db_link||
1727 ' WHERE ORGANIZATION_ID = :p_org_id';
1728
1729 EXECUTE IMMEDIATE sql_stmt INTO v_default_acc_class USING p_org_id ;
1730 END IF;
1731
1732 return v_default_acc_class;
1733 exception when others then
1734 return null;
1735 END get_acc_class_from_source;
1736
1737 Function is_source_db_up(p_instance_id number) RETURN boolean IS
1738 CURSOR db_cur IS
1739 select decode(M2A_dblink,null,' ','@'||M2A_dblink)
1740 from msc_apps_instances
1741 where instance_id = p_instance_id;
1742
1743 db_link varchar2(128);
1744 seq_num number;
1745 TYPE JobCurTyp IS REF CURSOR;
1746 JobCur JobCurTyp;
1747
1748 sql_stmt varchar2(200);
1749 BEGIN
1750 OPEN db_cur;
1751 FETCH db_cur INTO db_link;
1752 CLOSE db_cur;
1753
1754 sql_stmt := 'SELECT 1 from dual'||db_link;
1755
1756 OPEN JobCur FOR sql_stmt;
1757 FETCH JobCur INTO seq_num;
1758 CLOSE JobCur;
1759
1760 return true;
1761 exception when others then
1762 return false;
1763
1764 END is_source_db_up;
1765
1766 Procedure get_load_type(itemtype in varchar2,
1767 itemkey in varchar2,
1768 actid in number,
1769 funcmode in varchar2,
1770 resultout out NOCOPY varchar2 ) IS
1771 p_load_type number;
1772 BEGIN
1773 if (funcmode = 'RUN') then
1774 p_load_type :=
1775 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
1776 itemkey => itemkey,
1777 aname => 'LOAD_TYPE');
1778 if p_load_type = PURCHASE_ORDER_RESCHEDULE then
1779 resultout := 'COMPLETE:PO';
1780 elsif p_load_type = PURCHASE_REQ_RESCHEDULE then
1781 resultout := 'COMPLETE:REQ';
1782 /*
1783 elsif p_load_type = WIP_DIS_MASS_LOAD then
1784 resultout := 'COMPLETE:LOAD_JOB';
1785 elsif p_load_type = WIP_REP_MASS_LOAD then
1786 resultout := 'COMPLETE:LOAD_REP';
1787 elsif p_load_type = WIP_DIS_MASS_RESCHEDULE then
1788 resultout := 'COMPLETE:RES_JOB';
1789 elsif p_load_type = PURCHASE_REQ_MASS_LOAD then
1790 resultout := 'COMPLETE:LOAD_REQ';
1791 elsif p_load_type = LOT_BASED_JOB_LOAD then
1792 resultout := 'COMPLETE:LOAD_LOT';
1793 elsif p_load_type = LOT_BASED_JOB_RESCHEDULE then
1794 resultout := 'COMPLETE:RES_LOT';
1795 */
1796 end if;
1797 end if;
1798 if (funcmode = 'CANCEL') then
1799 resultout := 'COMPLETE:';
1800 end if;
1801
1802 if (funcmode = 'TIMEOUT') then
1803 resultout := 'COMPLETE:';
1804 end if;
1805 END get_load_type;
1806
1807 Procedure start_release_batch_wf(p_plan_id number,
1808 p_org_id number,
1809 p_instance_id number,
1810 p_owning_org number,
1811 p_owning_instance number,
1812 p_dblink varchar2,
1813 p_load_type number,
1814 p_instance_code varchar2) IS
1815
1816 p_item_key varchar2(50) := to_char(p_plan_id)||'-'||
1817 to_char(p_org_id) ||'-'||
1818 to_char(p_instance_id)||'-'||
1819 to_char(p_load_type);
1820 p_process varchar2(30);
1821
1822 BEGIN
1823 deleteActivities(p_item_key);
1824
1825 if p_dblink is not null then
1826 deleteActivities(p_item_key,p_dblink);
1827 end if;
1828
1829 p_process := 'BATCH_UPDATE';
1830
1831 wf_engine.CreateProcess( itemtype => g_item_type,
1832 itemkey => p_item_key,
1833 process => p_process);
1834 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
1835 itemkey => p_item_key,
1836 aname => 'PLAN_ID',
1837 avalue => p_plan_id);
1838 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
1839 itemkey => p_item_key,
1840 aname => 'ORG_ID',
1841 avalue => p_org_id);
1842 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
1843 itemkey => p_item_key,
1844 aname => 'SR_INSTANCE_ID',
1845 avalue => p_instance_id);
1846 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
1847 itemkey => p_item_key,
1848 aname => 'OWNING_ORG_ID',
1849 avalue => p_owning_org);
1850 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
1851 itemkey => p_item_key,
1852 aname => 'OWNING_INSTANCE_ID',
1853 avalue => p_instance_id);
1854 wf_engine.SetItemAttrNumber( itemtype => g_item_type,
1855 itemkey => p_item_key,
1856 aname => 'LOAD_TYPE',
1857 avalue => p_load_type);
1858 wf_engine.SetItemAttrText( itemtype => g_item_type,
1859 itemkey => p_item_key,
1860 aname => 'DBLINK',
1861 avalue => p_dblink);
1862 wf_engine.SetItemAttrText( itemtype => g_item_type,
1863 itemkey => p_item_key,
1864 aname => 'INSTANCE_CODE',
1865 avalue => p_instance_code);
1866
1867 --dbms_output.put_line('item_key='||p_item_key);
1868 wf_engine.StartProcess( itemtype => g_item_type,
1869 itemkey => p_item_key);
1870
1871 update msc_supplies
1872 SET implement_demand_class = NULL,
1873 implement_date = NULL,
1874 implement_quantity = NULL,
1875 implement_firm = NULL,
1876 implement_wip_class_code = NULL,
1877 implement_job_name = NULL,
1878 implement_status_code = NULL,
1879 implement_location_id = NULL,
1880 implement_source_org_id = NULL,
1881 implement_supplier_id = NULL,
1882 implement_supplier_site_id = NULL,
1883 implement_project_id = NULL,
1884 implement_task_id = NULL,
1885 release_status = NULL,
1886 load_type = NULL,
1887 implement_as = NULL,
1888 implement_unit_number = NULL,
1889 implement_schedule_group_id = NULL,
1890 implement_build_sequence = NULL,
1891 implement_line_id = NULL,
1892 implement_alternate_bom = NULL,
1893 implement_alternate_routing = NULL
1894 WHERE organization_id IN
1895 (select planned_organization
1896 from msc_plan_organizations_v
1897 where organization_id = p_owning_org
1898 and owning_sr_instance = p_owning_instance
1899 and plan_id = p_plan_id
1900 AND planned_organization = decode(p_org_id,
1901 p_owning_org, planned_organization,
1902 p_org_id)
1903 AND sr_instance_id = p_instance_id )
1904 AND sr_instance_id= p_instance_id
1905 AND plan_id = p_plan_id
1906 AND release_errors IS NULL
1907 AND load_type = p_load_type;
1908
1909 exception when others then
1910 raise;
1911 END start_release_batch_wf;
1912
1913 Procedure insert_temp_table(itemtype in varchar2,
1914 itemkey in varchar2,
1915 actid in number,
1916 funcmode in varchar2,
1917 resultout out NOCOPY varchar2 ) IS
1918 p_plan_id number;
1919 p_org_id number;
1920 p_instance_id number;
1921 p_owning_org number;
1922 p_owning_instance number;
1923 p_load_type number;
1924 p_count number :=0;
1925 p_apps_ver varchar2(10);
1926 p_wip_group_id number;
1927 p_po_group_by number;
1928 p_po_batch_number number;
1929 p_instance_code varchar2(10);
1930
1931 cursor apps_ver_cur IS
1932 select apps_ver
1933 from msc_apps_instances
1934 where instance_id = p_instance_id;
1935 BEGIN
1936 if (funcmode = 'RUN') then
1937 p_load_type :=
1938 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
1939 itemkey => itemkey,
1940 aname => 'LOAD_TYPE');
1941 p_plan_id :=
1942 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
1943 itemkey => itemkey,
1944 aname => 'PLAN_ID');
1945 p_org_id :=
1946 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
1947 itemkey => itemkey,
1948 aname => 'ORG_ID');
1949 p_instance_id :=
1950 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
1951 itemkey => itemkey,
1952 aname => 'SR_INSTANCE_ID');
1953 p_owning_org :=
1954 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
1955 itemkey => itemkey,
1956 aname => 'OWNING_ORG_ID');
1957 p_owning_instance :=
1958 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
1959 itemkey => itemkey,
1960 aname => 'OWNING_INSTANCE_ID');
1961 open apps_ver_cur;
1962 fetch apps_ver_cur into p_apps_ver;
1963 close apps_ver_cur;
1964
1965 msc_util.msc_debug('insert temp table now');
1966 msc_util.msc_debug('load type='||p_load_type);
1967 msc_util.msc_debug('org id='||p_org_id);
1968 msc_util.msc_debug('instance id='||p_instance_id);
1969 msc_util.msc_debug('owning org id='||p_owning_org);
1970 msc_util.msc_debug('owning instance id='||p_owning_instance);
1971
1972 if p_load_type = WIP_DIS_MASS_LOAD then
1973
1974 p_count := msc_rel_plan_pub.load_wip_discrete_jobs(
1975 p_plan_id,
1976 p_org_id,
1977 p_instance_id,
1978 p_owning_org,
1979 p_owning_instance,
1980 fnd_global.user_id,
1981 p_wip_group_id,
1982 null,
1983 null,
1984 p_apps_ver);
1985
1986 elsif p_load_type = WIP_REP_MASS_LOAD then
1987 p_count := msc_rel_plan_pub.load_repetitive_schedules(
1988 p_plan_id,
1989 p_org_id,
1990 p_instance_id,
1991 p_owning_org,
1992 p_owning_instance,
1993 fnd_global.user_id,
1994 p_wip_group_id,
1995 null,
1996 null);
1997
1998 elsif p_load_type = WIP_DIS_MASS_RESCHEDULE then
1999 p_count := msc_rel_plan_pub.reschedule_wip_discrete_jobs(
2000 p_plan_id,
2001 p_org_id,
2002 p_instance_id,
2003 p_owning_org,
2004 p_owning_instance,
2005 fnd_global.user_id,
2006 p_wip_group_id,
2007 null,
2008 null,
2009 p_apps_ver,
2010 p_load_type);
2011 elsif p_load_type = PURCHASE_REQ_MASS_LOAD then
2012
2013 --fix for the bug#2539212
2014 get_profile_value(p_profile_name => 'MRP_LOAD_REQ_GROUP_BY',
2015 p_instance_id => p_instance_id,
2016 p_calling_source => 'PACKAGE',
2017 p_profile_value => p_po_group_by);
2018
2019 p_count := msc_rel_plan_pub.load_po_requisitions(
2020 p_plan_id,
2021 p_org_id,
2022 p_instance_id,
2023 p_owning_org,
2024 p_owning_instance,
2025 fnd_global.user_id,
2026 p_po_group_by,
2027 p_po_batch_number,
2028 null,
2029 null);
2030 elsif p_load_type = LOT_BASED_JOB_LOAD then
2031 p_count := msc_rel_plan_pub.load_osfm_lot_jobs(
2032 p_plan_id,
2033 p_org_id,
2034 p_instance_id,
2035 p_owning_org,
2036 p_owning_instance,
2037 fnd_global.user_id,
2038 p_wip_group_id,
2039 null,
2040 null,
2041 p_apps_ver);
2042 elsif p_load_type = LOT_BASED_JOB_RESCHEDULE then
2043 p_count := msc_rel_plan_pub.reschedule_osfm_lot_jobs(
2044 p_plan_id,
2045 p_org_id,
2046 p_instance_id,
2047 p_owning_org,
2048 p_owning_instance,
2049 fnd_global.user_id,
2050 p_wip_group_id,
2051 null,
2052 null);
2053 end if;
2054 if p_count >0 then
2055 msc_util.msc_debug('# of rows updated:'|| p_count);
2056 resultout := 'COMPLETE:FOUND';
2057 else
2058 msc_util.msc_debug('no rows are inserted');
2059 resultout := 'COMPLETE:NOT_FOUND';
2060 end if;
2061 end if;
2062 if (funcmode = 'CANCEL') then
2063 resultout := 'COMPLETE:';
2064 end if;
2065
2066 if (funcmode = 'TIMEOUT') then
2067 resultout := 'COMPLETE:';
2068 end if;
2069 END insert_temp_table;
2070
2071 Procedure start_source_program(itemtype in varchar2,
2072 itemkey in varchar2,
2073 actid in number,
2074 funcmode in varchar2,
2075 resultout out NOCOPY varchar2 ) IS
2076 p_load_type number;
2077 p_instance number;
2078 p_request_id number;
2079 p_dblink varchar2(128);
2080 p_po_group_by number;
2081 po_group_by_name varchar2(20);
2082 lv_sql_stmt varchar2(2000);
2083 p_instance_code varchar2(10);
2084 Begin
2085 if (funcmode = 'RUN') then
2086 p_load_type :=
2087 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2088 itemkey => itemkey,
2089 aname => 'LOAD_TYPE');
2090 p_instance :=
2091 wf_engine.GetItemAttrNUMBER( itemtype => itemtype,
2092 itemkey => itemkey,
2093 aname => 'SR_INSTANCE_ID');
2094 p_instance_code :=
2095 wf_engine.GetItemAttrText( itemtype => itemtype,
2096 itemkey => itemkey,
2097 aname => 'INSTANCE_CODE');
2098 p_dblink :=
2099 wf_engine.GetItemAttrText( itemtype => itemtype,
2100 itemkey => itemkey,
2101 aname => 'DBLINK');
2102
2103 --commenting out the foll code as this procedure is not being used anywhere.
2104 /*
2105 lv_sql_stmt:=
2106 'BEGIN'
2107 ||' MRP_AP_REL_PLAN_PUB.INITIALIZE'
2108 ||p_dblink
2109 ||'( :p_user, :p_resp, :p_app );'
2110 ||' END;';
2111 EXECUTE IMMEDIATE lv_sql_stmt
2112 USING FND_GLOBAL.USER_NAME,
2113 FND_GLOBAL.RESP_NAME,
2114 FND_GLOBAL.APPLICATION_NAME;
2115
2116 msc_util.msc_debug('start reschedule in instance '||p_instance_code);
2117 if p_load_type in (WIP_DIS_MASS_LOAD,WIP_REP_MASS_LOAD,
2118 WIP_DIS_MASS_RESCHEDULE) then
2119
2120 lv_sql_stmt:=
2121 'BEGIN'
2122 ||' MRP_AP_REL_PLAN_PUB.LD_WIP_JOB_SCHEDULE_INTERFACE'
2123 ||p_dblink
2124 ||'( :arg_wip_req_id );'
2125 ||' END;';
2126
2127 EXECUTE IMMEDIATE lv_sql_stmt
2128 USING OUT p_request_id;
2129
2130 DELETE msc_wip_job_schedule_interface
2131 WHERE sr_instance_id= p_instance;
2132
2133 DELETE MSC_WIP_JOB_DTLS_INTERFACE
2134 WHERE sr_instance_id= p_instance;
2135
2136 elsif p_load_type in (LOT_BASED_JOB_LOAD,LOT_BASED_JOB_RESCHEDULE) then
2137 lv_sql_stmt:=
2138 'BEGIN'
2139 ||' MRP_AP_REL_PLAN_PUB.LD_LOT_JOB_SCHEDULE_INTERFACE'
2140 ||p_dblink
2141 ||'( :arg_wip_req_id );'
2142 ||' END;';
2143
2144 EXECUTE IMMEDIATE lv_sql_stmt
2145 USING OUT p_request_id;
2146
2147 DELETE msc_wip_job_schedule_interface
2148 WHERE sr_instance_id= p_instance;
2149
2150 DELETE MSC_WIP_JOB_DTLS_INTERFACE
2151 WHERE sr_instance_id= p_instance;
2152 elsif p_load_type = PURCHASE_REQ_MASS_LOAD then
2153 IF p_po_group_by = 1 THEN
2154 po_group_by_name := 'ALL';
2155 ELSIF p_po_group_by = 2 THEN
2156 po_group_by_name := 'ITEM';
2157 ELSIF p_po_group_by = 3 THEN
2158 po_group_by_name := 'BUYER';
2159 ELSIF p_po_group_by = 4 THEN
2160 po_group_by_name := 'PLANNER';
2161 ELSIF p_po_group_by = 5 THEN
2162 po_group_by_name := 'VENDOR';
2163 ELSIF p_po_group_by = 6 THEN
2164 po_group_by_name := 'ONE-EACH';
2165 ELSIF p_po_group_by = 7 THEN
2166 po_group_by_name := 'CATEGORY';
2167 END IF;
2168
2169 lv_sql_stmt:=
2170 'BEGIN'
2171 ||' MRP_AP_REL_PLAN_PUB.LD_PO_REQUISITIONS_INTERFACE'||p_dblink
2172 ||'( :po_group_by_name,'
2173 ||' :arg_req_load_id );'
2174 ||' END;';
2175
2176 EXECUTE IMMEDIATE lv_sql_stmt
2177 USING IN po_group_by_name,
2178 OUT p_request_id;
2179
2180 DELETE MSC_PO_REQUISITIONS_INTERFACE
2181 WHERE sr_instance_id= p_instance;
2182
2183 end if;
2184
2185 msc_util.msc_debug('request id is '||p_request_id
2186 ||', in instance '||p_instance_code);
2187
2188 wf_engine.SetItemAttrNumber( itemtype => itemtype,
2189 itemkey => itemkey,
2190 aname => 'REQUEST_ID',
2191 avalue => p_request_id);
2192 commit;
2193 */
2194 end if;
2195 if (funcmode = 'CANCEL') then
2196 resultout := 'COMPLETE:';
2197 end if;
2198
2199 if (funcmode = 'TIMEOUT') then
2200 resultout := 'COMPLETE:';
2201 end if;
2202 END start_source_program;
2203
2204 Procedure get_supply_data(p_plan_id in number,
2205 p_transaction_id in number,
2206 p_query_id in number,
2207 p_dblink in varchar2) IS
2208 cursor supply_cur IS
2209 select msc_get_name.org_code(s.organization_id,s.sr_instance_id),
2210 msc_get_name.supplier(s.supplier_id),
2211 msc_get_name.supplier_site(s.supplier_site_id),
2212 msc_get_name.lookup_meaning('MRP_ORDER_TYPE',s.order_type),
2213 DECODE(s.order_type,5,to_char(s.transaction_id),s.order_number),
2214 s.new_schedule_date,
2215 s.new_order_quantity,
2216 mp.compile_designator,
2217 msi.item_name,
2218 msi.buyer_name,
2219 decode(s.order_type, 1, s.disposition_id, null),
2220 s.po_line_id,
2221 s.implement_quantity,
2222 cal2.calendar_date
2223 from msc_supplies s,
2224 msc_system_items msi,
2225 msc_plans mp,
2226 msc_calendar_dates cal1,
2227 msc_calendar_dates cal2,
2228 msc_trading_partners mtp
2229 where s.plan_id = p_plan_id
2230 and s.transaction_id =p_transaction_id
2231 and s.plan_id = mp.plan_id
2232 and s.plan_id = msi.plan_id
2233 and s.organization_id = msi.organization_id
2234 and s.sr_instance_id = msi.sr_instance_id
2235 and s.inventory_item_id = msi.inventory_item_id
2236 and cal1.sr_instance_id = mtp.sr_instance_id
2237 AND cal1.calendar_code = mtp.calendar_code
2238 AND cal1.exception_set_id = mtp.calendar_exception_set_id
2239 AND cal1.calendar_date = trunc(NVL(s.implement_date,s.new_schedule_date))
2240 AND cal2.sr_instance_id = cal1.sr_instance_id
2241 AND cal2.calendar_code = cal1.calendar_code
2242 AND cal2.exception_set_id = cal1.exception_set_id
2243 AND cal2.seq_num = GREATEST(1,NVL(cal1.seq_num, cal1.prior_seq_num) -
2244 NVL(msi.postprocessing_lead_time, 0))
2245 AND mtp.sr_tp_id = msi.organization_id
2246 AND mtp.sr_instance_id = msi.sr_instance_id
2247 AND mtp.partner_type= 3;
2248
2249 CURSOR need_by_date_cur IS
2250 SELECT new_dock_date
2251 FROM msc_supplies
2252 WHERE plan_id = -1
2253 AND transaction_id = p_transaction_id;
2254
2255 l_plan_name varchar2(20);
2256 l_item_name varchar2(40);
2257 l_org_code varchar2(20);
2258 l_supplier varchar2(80);
2259 l_supplier_site varchar2(15);
2260 l_order varchar2(80);
2261 l_order_type varchar2(80);
2262 l_buyer varchar2(80);
2263 l_old_need_by_date date;
2264 l_new_need_by_date date;
2265 l_new_due_date date;
2266 l_qty number;
2267 l_impl_qty number;
2268 l_po_header_id number;
2269 l_po_line_id number;
2270
2271 lv_sql_stmt varchar2(3000);
2272 BEGIN
2273 OPEN supply_cur;
2274 FETCH supply_cur INTO l_org_code,
2275 l_supplier,
2276 l_supplier_site,
2277 l_order_type,
2278 l_order,
2279 l_new_due_date,
2280 l_qty,
2281 l_plan_name,
2282 l_item_name,
2283 l_buyer,
2284 l_po_header_id,
2285 l_po_line_id,
2286 l_impl_qty,
2287 l_new_need_by_date;
2288 CLOSE supply_cur;
2289
2290 OPEN need_by_date_cur;
2291 FETCH need_by_date_cur INTO l_old_need_by_date;
2292 CLOSE need_by_date_cur;
2293
2294 lv_sql_stmt:=
2295 'insert into mrp_form_query'||p_dblink||
2296 ' (query_id,'||
2297 ' last_update_date,'||
2298 ' last_updated_by,'||
2299 ' creation_date,'||
2300 ' created_by,'||
2301 ' char1,'||
2302 ' char2,'||
2303 ' char3,'||
2304 ' char4,'||
2305 ' char5,'||
2306 ' char6,'||
2307 ' char7,'||
2308 ' char8,'||
2309 ' date1,'||
2310 ' date2,'||
2311 ' date3,'||
2312 ' number1,'||
2313 ' number2,'||
2314 ' number3,'||
2315 ' number4)'||
2316 ' VALUES('||
2317 ' :p_query_id,'||
2318 ' sysdate,'||
2319 ' -1,'||
2320 ' sysdate,'||
2321 ' -1,'||
2322 ' :l_org_code,'||
2323 ' :l_supplier,'||
2324 ' :l_supplier_site,'||
2325 ' :l_order_type,'||
2326 ' :l_order,'||
2327 ' :l_plan_name,'||
2328 ' :l_item_name,'||
2329 ' :l_buyer,'||
2330 ' :l_new_due_date,'||
2331 ' :l_new_need_by_date,'||
2332 ' :l_old_need_by_date,'||
2333 ' :l_qty,'||
2334 ' :l_impl_qty,'||
2335 ' :l_po_header_id,'||
2336 ' :l_po_line_id)';
2337
2338 EXECUTE IMMEDIATE lv_sql_stmt
2339 USING p_query_id,
2340 l_org_code,
2341 l_supplier,
2342 l_supplier_site,
2343 l_order_type,
2344 l_order,
2345 l_plan_name,
2346 l_item_name,
2347 l_buyer,
2348 l_new_due_date,
2349 l_new_need_by_date,
2350 l_old_need_by_date,
2351 l_qty,
2352 l_impl_qty,
2353 l_po_header_id,
2354 l_po_line_id;
2355
2356 exception when others then
2357 raise;
2358 END get_supply_data;
2359
2360 PROCEDURE init_db(p_user_name varchar2) IS
2361 l_user_id number;
2362 l_resp_id number;
2363 l_application_id number;
2364 BEGIN
2365 select user_id
2366 into l_user_id
2367 from fnd_user
2368 where user_name = p_user_name;
2369
2370 begin
2371 SELECT APPLICATION_ID
2372 INTO l_application_id
2373 FROM FND_APPLICATION_VL
2374 WHERE APPLICATION_SHORT_NAME = 'MSC'
2375 and rownum =1 ;
2376
2377 SELECT responsibility_id
2378 INTO l_resp_id
2379 FROM FND_responsibility_vl
2380 where application_Id = l_application_id
2381 and rownum =1 ;
2382
2383 exception when no_data_found then
2384
2385 SELECT APPLICATION_ID
2386 INTO l_application_id
2387 FROM FND_APPLICATION_VL
2388 WHERE APPLICATION_SHORT_NAME = 'MRP'
2389 and rownum = 1;
2390
2391 SELECT responsibility_id
2392 INTO l_resp_id
2393 FROM FND_responsibility_vl
2394 where application_Id = l_application_id
2395 and rownum =1 ;
2396 end;
2397
2398 fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);
2399 END init_db;
2400
2401 PROCEDURE close_dblink(p_dblink varchar2) IS
2402 lv_sql_stmt VARCHAR2(2000);
2403 DBLINK_NOT_OPEN EXCEPTION;
2404 PRAGMA EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
2405
2406 BEGIN
2407 IF p_dblink <> ' ' then
2408 -- mark distributed transaction boundary
2409 -- will need to do a manual clean up (commit) of the distributed
2410 -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
2411 commit;
2412
2413 lv_sql_stmt := 'alter session close database link ' ||p_dblink;
2414
2415 EXECUTE IMMEDIATE lv_sql_stmt;
2416
2417 END IF;
2418
2419 EXCEPTION
2420 WHEN DBLINK_NOT_OPEN THEN
2421 NULL;
2422 END close_dblink;
2423
2424
2425 --This procedure is added to fix the issue#2539212
2426 PROCEDURE get_profile_value(p_profile_name IN varchar2,
2427 p_instance_id IN number,
2428 p_calling_source IN varchar2 default 'FORM',
2429 p_profile_value OUT NOCOPY varchar2
2430 ) IS
2431 lv_user_name VARCHAR2(100):= NULL;
2432 lv_resp_name VARCHAR2(100):= NULL;
2433 lv_application_name VARCHAR2(240):= NULL;
2434 lv_appl_short_name VARCHAR2(10):= NULL;
2435 lv_dblink VARCHAR2(128);
2436 lv_dblink2 VARCHAR2(128);
2437 lv_sql_stmt VARCHAR2(2000);
2438
2439 cursor appl_short_name (p_appl_name IN VARCHAR2) IS
2440 select application_short_name
2441 from fnd_application_vl
2442 where application_name = p_appl_name;
2443
2444 BEGIN
2445
2446 SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK),
2447 DECODE( M2A_DBLINK, NULL, ' ', M2A_DBLINK)
2448 INTO lv_dblink, lv_dblink2
2449 FROM msc_apps_instances
2450 WHERE instance_id = p_instance_id;
2451
2452 SELECT FND_GLOBAL.USER_NAME,
2453 FND_GLOBAL.RESP_NAME,
2454 FND_GLOBAL.APPLICATION_NAME
2455 INTO lv_user_name,
2456 lv_resp_name,
2457 lv_application_name
2458 FROM dual;
2459
2460 open appl_short_name(lv_application_name);
2461 fetch appl_short_name into lv_appl_short_name;
2462 close appl_short_name;
2463
2464
2465
2466
2467 lv_sql_stmt:= 'SELECT mrp_rel_wf.get_profile_value'||lv_dblink||'('||
2468 ':1, :2, :3, :4) from dual';
2469
2470 EXECUTE IMMEDIATE lv_sql_stmt
2471 INTO p_profile_value
2472 USING p_profile_name,
2473 lv_user_name,
2474 lv_resp_name,
2475 lv_appl_short_name;
2476
2477 close_dblink(lv_dblink2);
2478
2479 EXCEPTION
2480 WHEN NO_DATA_FOUND THEN
2481 close_dblink(lv_dblink2);
2482 p_profile_value := NULL;
2483
2484 WHEN others THEN
2485 IF p_calling_source = 'FORM' THEN
2486 fnd_message.set_name('MSC',sqlerrm);
2487 APP_EXCEPTION.RAISE_EXCEPTION;
2488 END IF;
2489 END get_profile_value;
2490
2491 FUNCTION get_offset_date(p_calendar_code in varchar2,
2492 p_inst_id in number,
2493 p_lead_time in number,
2494 p_date in date) return date is
2495 p_return_date date;
2496
2497 begin
2498
2499
2500 if P_calendar_code is null or p_calendar_code = MSC_CALENDAR.FOC then
2501
2502 -- shipping/receiving calendar hierarchy [if no CRC, then 24x7]
2503 p_return_date := p_date + nvl(p_lead_time,0);
2504
2505 else
2506
2507 p_return_date := msc_calendar.date_offset
2508 ( p_calendar_code
2509 , p_inst_id
2510 , p_date
2511 , nvl(p_lead_time,0)
2512 , null -- association_type
2513 );
2514
2515 -- msc_calendar.date_offset will remove the timestamp
2516 if to_char(p_return_date,'HH24:MI:SS') = '00:00:00' and
2517 to_char(p_date,'HH24:MI:SS') <> '00:00:00' then
2518 p_return_date := to_date(to_char(p_return_date, 'MM/DD/RR')||' '||
2519 to_char(p_date,'HH24:MI:SS'),
2520 'MM/DD/RR HH24:MI:SS');
2521
2522 end if;
2523 end if;
2524 return p_return_date;
2525 end get_offset_date;
2526
2527 PROCEDURE update_so_dates(p_plan_id number, p_demand_id number,
2528 p_inst_id number, p_implement_date date,
2529 p_ship_date out nocopy date,
2530 p_arrival_date out nocopy date,
2531 p_earliest_date out nocopy date) IS
2532
2533 TYPE NumArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2534 TYPE DateArr IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2535
2536 v_set_demand_id NumArr;
2537 v_lead_time NumArr;
2538 v_org_id NumArr;
2539 v_inst_id NumArr;
2540 v_ship_date DateArr;
2541 v_arrival_date DateArr;
2542 v_planned_ship_date DateArr;
2543 v_planned_arrival_date DateArr;
2544 v_earliest_date DateArr;
2545 v_request_date DateArr;
2546 v_schedule_date DateArr;
2547 v_vic_cal_code msc_calendars.calendar_code%type;
2548 v_association_type number;
2549
2550 cursor so_c is
2551 select order_number,
2552 organization_id org_id,
2553 sr_instance_id inst_id,
2554 origination_type demand_type,
2555 ship_set_id, arrival_set_id,
2556 dmd_satisfied_date earliest_ship_date,
2557 nvl(planned_ship_date,dmd_satisfied_date) planned_ship_date,
2558 planned_arrival_date,
2559 nvl(p_implement_date,nvl(firm_date, nvl(planned_ship_date,
2560 dmd_satisfied_date))) ship_date,
2561 order_date_type_code order_type,
2562 decode(order_date_type_code, 1, request_ship_date,
2563 request_date) request_date,
2564 decode(order_date_type_code, 1, schedule_ship_date,
2565 schedule_arrival_date) schedule_date,
2566 intransit_lead_time lead_time,
2567 inventory_item_id,
2568 ship_method,
2569 customer_id,
2570 customer_site_id
2571 from msc_demands
2572 where plan_id = p_plan_id
2573 and demand_id = p_demand_id
2574 and sr_instance_id = p_inst_id;
2575
2576 so_rec so_c%ROWTYPE;
2577
2578 -- release all lines in a ship/arrival set and use max date
2579
2580 cursor ship_set(p_ship_set_id number) is
2581 select
2582 md.demand_id,
2583 md.dmd_satisfied_date, --earliest ship date,
2584 decode(md.demand_id, p_demand_id,
2585 nvl(p_implement_date, --plan ship date
2586 nvl(firm_date, nvl(md.planned_ship_date,
2587 md.dmd_satisfied_date))),
2588 nvl(implement_date,
2589 nvl(firm_date, nvl(md.planned_ship_date,
2590 md.dmd_satisfied_date)))),
2591 md.request_ship_date,
2592 md.schedule_ship_date,
2593 nvl(md.planned_ship_date,md.dmd_satisfied_date),
2594 md.planned_arrival_date,
2595 md.intransit_lead_time
2596 from msc_demands md,
2597 msc_system_items msi
2598 where md.plan_id = p_plan_id
2599 and md.ship_set_id = p_ship_set_id
2600 and msi.plan_id = md.plan_id
2601 and msi.organization_id = md.organization_id
2602 and msi.sr_instance_id = md.sr_instance_id
2603 and msi.inventory_item_id = md.inventory_item_id
2604 and nvl(msi.bom_item_type,4) <> 5; -- not a product family
2605
2606 cursor arrival_set(p_arrival_set_id number) is
2607 select
2608 md.demand_id,
2609 md.dmd_satisfied_date, --earliest ship date
2610 decode(md.demand_id, p_demand_id, --plan ship date
2611 nvl(p_implement_date,
2612 nvl(firm_date, nvl(md.planned_ship_date,
2613 md.dmd_satisfied_date))),
2614 nvl(implement_date,
2615 nvl(firm_date, nvl(md.planned_ship_date,
2616 md.dmd_satisfied_date)))),
2617 md.request_date,
2618 md.schedule_arrival_date,
2619 md.intransit_lead_time,
2620 md.organization_id,
2621 md.sr_instance_id,
2622 nvl(md.planned_ship_date,md.dmd_satisfied_date),
2623 md.planned_arrival_date
2624 from msc_demands md,
2625 msc_system_items msi
2626 where md.plan_id = p_plan_id
2627 and md.arrival_set_id = p_arrival_set_id
2628 and msi.plan_id = md.plan_id
2629 and msi.organization_id = md.organization_id
2630 and msi.sr_instance_id = md.sr_instance_id
2631 and msi.inventory_item_id = md.inventory_item_id
2632 and nvl(msi.bom_item_type,4) <> 5; -- not a product family
2633
2634 v_temp number;
2635 p_new_earliest_date date;
2636 BEGIN
2637
2638 OPEN so_c;
2639 FETCH so_c INTO so_rec;
2640 CLOSE so_c;
2641
2642 begin
2643 v_vic_cal_code := msc_calendar.get_calendar_code
2644 ( p_inst_id
2645 , null
2646 , null
2647 , null
2648 , null
2649 , 4 -- partner type [in transit]
2650 , null
2651 , so_rec.ship_method
2652 , MSC_CALENDAR.VIC
2653 , v_association_type
2654 );
2655 exception when others then
2656 v_vic_cal_code := null;
2657 end;
2658
2659 if so_rec.ship_set_id is not null then
2660 OPEN ship_set(so_rec.ship_set_id);
2661 FETCH ship_set BULK COLLECT INTO
2662 v_set_demand_id, v_earliest_date,
2663 v_ship_date,
2664 v_request_date, v_schedule_date,
2665 v_planned_ship_date,
2666 v_planned_arrival_date, v_lead_time;
2667 CLOSE ship_set;
2668
2669 for a in 1..v_set_demand_id.count loop
2670
2671 -- find the max earliest ship date
2672 if a = 1 then
2673 p_earliest_date := v_earliest_date(a);
2674 else
2675 p_earliest_date := greatest(p_earliest_date,v_earliest_date(a));
2676 end if;
2677
2678 v_ship_date(a) := verify_so_dates(v_schedule_date(a),
2679 v_request_date(a),v_ship_date(a));
2680
2681 -- find the max ship date
2682 if a = 1 then
2683 p_ship_date := v_ship_date(a);
2684 else
2685 p_ship_date := greatest(p_ship_date,v_ship_date(a));
2686 end if;
2687
2688 end loop;
2689
2690 -- will use max date for the whole set
2691
2692 for a in 1..v_set_demand_id.count loop
2693 if p_ship_date <> v_planned_ship_date(a) or
2694 v_planned_arrival_date(a) is null then
2695 -- recalculate arrival date
2696 v_planned_arrival_date(a) := get_offset_date ( v_vic_cal_code
2697 , p_inst_id
2698 , v_lead_time(a)
2699 , p_ship_date);
2700 end if;
2701
2702 if v_set_demand_id(a) <> p_demand_id then
2703 update msc_demands
2704 set implement_ship_date = p_ship_date, -- sche ship date
2705 implement_date = nvl(implement_date, nvl(firm_date,
2706 nvl(planned_ship_date,dmd_satisfied_date))),
2707 implement_earliest_date = p_earliest_date, -- earliest ship date
2708 implement_arrival_date = v_planned_arrival_date(a),
2709 implement_org_id = organization_id,
2710 implement_instance_id = sr_instance_id,
2711 implement_firm = nvl(implement_firm, org_firm_flag),
2712 load_type = 30,
2713 reschedule_flag = 1,
2714 release_status = 1,
2715 status = 0,
2716 applied =2,
2717 last_updated_by = fnd_global.user_id
2718 where plan_id = p_plan_id
2719 and demand_id = v_set_demand_id(a);
2720 else -- if v_set_demand_id(a) = p_demand_id then
2721 p_arrival_date := v_planned_arrival_date(a);
2722 end if;
2723 end loop;
2724
2725 end if; -- end of if p_ship_set_id is not null then
2726
2727 if so_rec.arrival_set_id is not null then
2728 OPEN arrival_set(so_rec.arrival_set_id);
2729 FETCH arrival_set BULK COLLECT INTO
2730 v_set_demand_id, v_earliest_date,
2731 v_ship_date,
2732 v_request_date, v_schedule_date,
2733 v_lead_time,
2734 v_org_id, v_inst_id,
2735 v_planned_ship_date,
2736 v_planned_arrival_date;
2737 CLOSE arrival_set;
2738
2739 for a in 1..v_set_demand_id.count loop
2740 -- if user does not change implement_date(ie. = planned_ship_date)
2741 -- no need to recalculate arrival date, just use planned_arrival_date
2742 if v_ship_date(a) <> v_planned_ship_date(a) then
2743 -- offset sch_ship_date with lead time to get sch_arrival_date
2744
2745 v_arrival_date(a) := get_offset_date
2746 ( v_vic_cal_code
2747 , v_inst_id(a)
2748 , v_lead_time(a)
2749 , v_ship_date(a)
2750 );
2751 else
2752 v_arrival_date(a) := v_planned_arrival_date(a);
2753 end if;
2754 -- find the max date for earliest_date
2755 if a = 1 then
2756 p_earliest_date := get_offset_date
2757 ( v_vic_cal_code
2758 , v_inst_id(a)
2759 , v_lead_time(a)
2760 , v_earliest_date(a)
2761 );
2762 else
2763 p_earliest_date := greatest(p_earliest_date,
2764 get_offset_date
2765 ( v_vic_cal_code
2766 , v_inst_id(a)
2767 , v_lead_time(a)
2768 , v_earliest_date(a)
2769 ));
2770 end if;
2771
2772 v_arrival_date(a) := verify_so_dates(v_schedule_date(a),
2773 v_request_date(a),
2774 v_arrival_date(a));
2775 -- find the max date for schedule_arrival_date
2776 if a = 1 then
2777 p_arrival_date := v_arrival_date(a);
2778 else
2779 p_arrival_date := greatest(p_arrival_date,v_arrival_date(a));
2780 end if;
2781
2782 end loop;
2783
2784 -- will use max date for the whole set
2785 for a in 1..v_set_demand_id.count loop
2786 if p_arrival_date <> v_planned_arrival_date(a) then
2787 -- offset max(sch_arrival_date) with lead time to get sch_ship_date
2788 v_ship_date(a) := msc_rel_wf.get_offset_date
2789 ( v_vic_cal_code
2790 , v_inst_id(a)
2791 , v_lead_time(a)*-1
2792 , p_arrival_date);
2793 else
2794 v_ship_date(a) := v_planned_ship_date(a);
2795
2796 end if;
2797 v_earliest_date(a) := msc_rel_wf.get_offset_date
2798 ( v_vic_cal_code
2799 , v_inst_id(a)
2800 , v_lead_time(a)*-1
2801 , p_earliest_date);
2802 if v_set_demand_id(a) <> p_demand_id then
2803 update msc_demands
2804 set implement_arrival_date = p_arrival_date,
2805 implement_earliest_date = v_earliest_date(a),
2806 implement_ship_date = v_ship_date(a), -- sche ship date
2807 implement_date = nvl(implement_date, nvl(firm_date,
2808 nvl(planned_ship_date,dmd_satisfied_date))),
2809 implement_org_id = organization_id,
2810 implement_instance_id = sr_instance_id,
2811 implement_firm = nvl(implement_firm, org_firm_flag),
2812 load_type = 30,
2813 reschedule_flag = 1,
2814 release_status = 1,
2815 status = 0,
2816 applied =2,
2817 last_updated_by = fnd_global.user_id
2818 where plan_id = p_plan_id
2819 and demand_id = v_set_demand_id(a);
2820 else -- if v_set_demand_id(a) = p_demand_id then
2821 p_ship_date := v_ship_date(a);
2822 p_new_earliest_date := v_earliest_date(a);
2823 end if;
2824 end loop;
2825
2826 p_earliest_date := p_new_earliest_date;
2827
2828 end if; -- end of if p_arrival_set_id is not null then
2829
2830 if so_rec.ship_set_id is null and so_rec.arrival_set_id is null then
2831 if so_rec.order_type = 1 then -- ship
2832 p_earliest_date := so_rec.earliest_ship_date;
2833 p_ship_date := verify_so_dates(so_rec.schedule_date,
2834 so_rec.request_date,
2835 so_rec.ship_date);
2836 if p_ship_date <> so_rec.planned_ship_date then
2837 p_arrival_date := msc_rel_wf.get_offset_date
2838 ( v_vic_cal_code
2839 , so_rec.inst_id
2840 , so_rec.lead_time
2841 , p_ship_date);
2842 else
2843 p_arrival_date := so_rec.planned_arrival_date;
2844 end if;
2845 else -- arrival
2846 if so_rec.ship_date <> so_rec.planned_ship_date then
2847 p_arrival_date := msc_rel_wf.get_offset_date
2848 ( v_vic_cal_code
2849 , so_rec.inst_id
2850 , so_rec.lead_time
2851 , so_rec.ship_date);
2852 else
2853 p_arrival_date := so_rec.planned_arrival_date;
2854 end if;
2855 p_arrival_date := verify_so_dates(so_rec.schedule_date,
2856 so_rec.request_date,
2857 p_arrival_date);
2858 p_earliest_date := so_rec.earliest_ship_date;
2859 if p_arrival_date <> so_rec.planned_arrival_date then
2860 p_ship_date := msc_rel_wf.get_offset_date
2861 ( v_vic_cal_code
2862 , so_rec.inst_id
2863 , so_rec.lead_time*-1
2864 , p_arrival_date);
2865 else
2866 p_ship_date := so_rec.planned_ship_date;
2867 end if;
2868
2869 end if;
2870 end if; --if so_rec.ship_set_id is null and so_rec.arrival_set_id is null
2871 update msc_demands
2872 set implement_earliest_date = p_earliest_date
2873 where plan_id = p_plan_id
2874 and demand_id = p_demand_id;
2875
2876 END update_so_dates;
2877
2878 PROCEDURE unrelease_so_set(p_plan_id number, p_demand_id number,
2879 p_instance_id number) IS
2880 cursor set_id is
2881 select ship_set_id, arrival_set_id
2882 from msc_demands
2883 where plan_id = p_plan_id
2884 and demand_id = p_demand_id
2885 and sr_instance_id = p_instance_id;
2886 p_ship_set_id number;
2887 p_arrival_set_id number;
2888 BEGIN
2889 OPEN set_id;
2890 FETCH set_id into p_ship_set_id, p_arrival_set_id;
2891 CLOSE set_id;
2892
2893 if p_ship_set_id is not null then
2894 UPDATE MSC_DEMANDS
2895 SET implement_date = NULL,
2896 implement_ship_date = NULL,
2897 implement_earliest_date = NULL,
2898 implement_arrival_date = NULL,
2899 implement_org_id = NULL,
2900 implement_instance_id = NULL,
2901 implement_firm = null,
2902 release_status = NULL,
2903 reschedule_flag = null,
2904 load_type = NULL,
2905 applied = 2,
2906 status =0
2907 WHERE plan_id= p_plan_id
2908 AND origination_type = 30
2909 AND ship_set_id = p_ship_set_id
2910 AND demand_id <> p_demand_id;
2911 end if;
2912
2913 if p_arrival_set_id is not null then
2914 UPDATE MSC_DEMANDS
2915 SET implement_date = NULL,
2916 implement_ship_date = NULL,
2917 implement_earliest_date = NULL,
2918 implement_arrival_date = NULL,
2919 implement_org_id = NULL,
2920 implement_instance_id = NULL,
2921 implement_firm = null,
2922 reschedule_flag = null,
2923 release_status = NULL,
2924 load_type = NULL,
2925 applied = 2,
2926 status =0
2927 WHERE plan_id= p_plan_id
2928 AND origination_type = 30
2929 AND arrival_set_id = p_arrival_set_id
2930 AND demand_id <> p_demand_id;
2931 end if;
2932
2933 END unrelease_so_set;
2934
2935 FUNCTION verify_so_release(p_plan_id number, p_demand_id number,
2936 p_inst_id number)
2937 RETURN varchar2 IS
2938
2939 TYPE NumArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2940
2941 v_subst_item NumArr;
2942
2943 cursor so_c is
2944 select order_number,
2945 organization_id org_id,
2946 origination_type demand_type,
2947 ship_set_id, arrival_set_id,
2948 decode(nvl(prev_subst_item,0),
2949 inventory_item_id, 0,0,0,1) subst_item
2950 from msc_demands
2951 where plan_id = p_plan_id
2952 and demand_id = p_demand_id
2953 and sr_instance_id = p_inst_id;
2954
2955 so_rec so_c%ROWTYPE;
2956
2957 -- can not have multiple sources for one sales order
2958 cursor check_source(p_order_number varchar2,
2959 p_order_type number,
2960 p_org_id number) is
2961 select 1
2962 from msc_demands
2963 where plan_id = p_plan_id
2964 and order_number = p_order_number
2965 and origination_type = p_order_type
2966 and organization_id <> p_org_id
2967 and using_requirement_quantity <> 0;
2968
2969 -- can not have multiple sources for ship set
2970
2971 cursor check_ship_source(p_ship_set_id number,
2972 p_org_id number) is
2973 select 1
2974 from msc_demands
2975 where plan_id = p_plan_id
2976 and ship_set_id = p_ship_set_id
2977 and organization_id <> p_org_id ;
2978
2979 -- release all lines in a ship/arrival set and use max date
2980
2981 cursor ship_set(p_ship_set_id number) is
2982 select
2983 decode(nvl(original_item_id,0), inventory_item_id, 0,0,0,1)
2984 from msc_demands
2985 where plan_id = p_plan_id
2986 and ship_set_id = p_ship_set_id;
2987
2988 cursor arrival_set(p_arrival_set_id number) is
2989 select
2990 decode(nvl(original_item_id,0), inventory_item_id, 0,0,0,1)
2991 from msc_demands
2992 where plan_id = p_plan_id
2993 and arrival_set_id = p_arrival_set_id;
2994
2995 v_error_msg varchar2(80);
2996 v_temp number;
2997 BEGIN
2998
2999 OPEN so_c;
3000 FETCH so_c INTO so_rec;
3001 CLOSE so_c;
3002
3003 /* GE enhancement, will allow release Sales Order with item substitution
3004 if so_rec.subst_item =1 then
3005 v_error_msg := 'MSC_REL_SO_SUBST_ITEM';
3006 return v_error_msg;
3007 end if;
3008 */
3009 v_temp :=0;
3010 OPEN check_source(so_rec.order_number, so_rec.demand_type,so_rec.org_id);
3011 FETCH check_source INTO v_temp;
3012 CLOSE check_source;
3013
3014 if v_temp = 1 then
3015 v_error_msg := 'MSC_REL_SO_MULTI_SOURCES';
3016 return v_error_msg;
3017 end if;
3018
3019 if so_rec.ship_set_id is not null then
3020
3021 v_temp :=0;
3022 OPEN check_ship_source(so_rec.ship_set_id, so_rec.org_id);
3023 FETCH check_ship_source INTO v_temp;
3024 CLOSE check_ship_source;
3025
3026 if v_temp = 1 then
3027 v_error_msg := 'MSC_REL_SHIP_SET_MULTI_SOURCES';
3028 return v_error_msg;
3029 end if;
3030 /*
3031 OPEN ship_set(so_rec.ship_set_id);
3032 FETCH ship_set BULK COLLECT INTO v_subst_item;
3033 CLOSE ship_set;
3034
3035 for a in 1..v_subst_item.count loop
3036
3037 if v_subst_item(a) = 1 then
3038 v_error_msg := 'MSC_REL_SO_SUBST_ITEM_IN_A_SET';
3039 return v_error_msg;
3040 end if;
3041
3042 end loop;
3043 */
3044 end if; -- end of if p_ship_set_id is not null then
3045 /*
3046 if so_rec.arrival_set_id is not null then
3047 OPEN arrival_set(so_rec.arrival_set_id);
3048 FETCH arrival_set BULK COLLECT INTO v_subst_item;
3049 CLOSE arrival_set;
3050
3051 for a in 1..v_subst_item.count loop
3052
3053 if v_subst_item(a) = 1 then
3054 v_error_msg := 'MSC_REL_SO_SUBST_ITEM_IN_A_SET';
3055 return v_error_msg;
3056 end if;
3057
3058 end loop;
3059
3060 end if; -- end of if p_arrival_set_id is not null then
3061 */
3062 return v_error_msg;
3063
3064 END verify_so_release;
3065
3066 FUNCTION verify_so_dates(p_old_schedule_date date,
3067 p_request_date date,
3068 p_new_schedule_date date) RETURN date IS
3069 p_new_date date;
3070 BEGIN
3071 -- if new schedule date >= old schedule date, new date = new schedule date
3072 -- else
3073 -- if old scheduld date < request date, new date = old schedule date
3074 -- else
3075 -- if new schedule date > request date, new date = new schedule date
3076 -- else new date = request date
3077
3078 if p_old_schedule_date is null or p_request_date is null then
3079 return p_new_schedule_date;
3080 end if;
3081 if p_new_schedule_date >= p_old_schedule_date then
3082 return p_new_schedule_date;
3083 else -- if p_new_schedule_date < p_old_schedule_date then
3084 if p_old_schedule_date < p_request_date then
3085 return p_old_schedule_date;
3086 else -- if p_old_schedule_date >= p_request_date then
3087 if p_new_schedule_date > p_request_date then
3088 return p_new_schedule_date;
3089 else
3090 return p_request_date;
3091 end if;
3092 end if; -- if p_old_schedule_date < p_request_date then
3093 end if; -- if p_new_schedule_date >= p_old_schedule_date then
3094
3095 END verify_so_dates;
3096
3097 PROCEDURE so_release_workflow_program(p_batch_id in number,
3098 p_instance_id in number,
3099 p_planner in varchar2,
3100 p_request_id out nocopy number) IS
3101 p_result boolean;
3102 begin
3103 msc_rel_wf.init_db(p_planner);
3104 p_result := fnd_request.set_mode(true);
3105 -- this will call msc_rel_wf.start_so_release_workflow
3106
3107 p_request_id := fnd_request.submit_request(
3108 'MSC',
3109 'MSCRLSOWF',
3110 null,
3111 null,
3112 false,
3113 p_batch_id,
3114 p_instance_id);
3115
3116 exception when others then
3117 p_request_id :=0;
3118 raise;
3119 end so_release_workflow_program;
3120
3121 PROCEDURE start_so_release_workflow(
3122 errbuf OUT NOCOPY VARCHAR2
3123 ,retcode OUT NOCOPY NUMBER,
3124 p_batch_id number,
3125 p_instance_id number) IS
3126
3127 l_process varchar2(50) := 'EXCEPTION_PROCESS3';
3128 item_type varchar2(50) :='MSCEXPWF';
3129 item_key varchar2(50);
3130
3131 cursor all_c is
3132 select msi.item_name,
3133 md.order_number,
3134 msi.description item_desc,
3135 msc_get_name.customer(md.customer_id) customer_name,
3136 msc_get_name.customer_site(md.customer_site_id) customer_site,
3137 msc_get_name.org_code(nvl(md.prev_subst_org, md.original_org_id),
3138 md.original_inst_id) org_code,
3139 msc_get_name.org_code(md.organization_id,md.sr_instance_id) to_org,
3140 msoi.schedule_ship_date new_ship_date,
3141 md.schedule_ship_date old_ship_date,
3142 msoi.schedule_arrival_date new_arrival_date,
3143 md.schedule_arrival_date old_arrival_date,
3144 msoi.ship_method new_ship_method,
3145 md.orig_shipping_method_code old_ship_method,
3146 md.orig_intransit_lead_time old_lead_time,
3147 msoi.delivery_lead_time new_lead_time,
3148 msoi.earliest_ship_date earliest_ship_date,
3149 msoi.return_status,
3150 md.demand_id,
3151 md.inventory_item_id,
3152 md.organization_id,
3153 md.sr_instance_id,
3154 md.plan_id,
3155 mp.compile_designator plan_name,
3156 msoi.line_number line_number,
3157 msc_get_name.lookup_meaning('SYS_YES_NO',msoi.return_status) atp_override_flag,
3158 msc_get_name.item_name(nvl(md.prev_subst_item,md.original_item_id),
3159 null,null,null)
3160 orig_item_name,
3161 msc_get_name.item_desc(nvl(md.prev_subst_item,md.original_item_id),
3162 nvl(md.prev_subst_org, md.original_org_id),
3163 md.plan_id,md.original_inst_id)
3164 orig_item_desc
3165 from msc_system_items msi,
3166 msc_plans mp,
3167 msc_demands md,
3168 msc_sales_order_interface msoi
3169 where msoi.batch_id = p_batch_id
3170 and msoi.sr_instance_id = p_instance_id
3171 and msoi.plan_id = md.plan_id
3172 and msoi.demand_id = md.demand_id
3173 and msoi.return_status is not null
3174 and msi.plan_id = md.plan_id
3175 and msi.organization_id = md.organization_id
3176 and msi.sr_instance_id = md.sr_instance_id
3177 and msi.inventory_item_id = md.inventory_item_id
3178 and mp.plan_id = msoi.plan_id;
3179
3180 all_rec all_c%ROWTYPE;
3181
3182 BEGIN
3183
3184 OPEN all_c;
3185 LOOP
3186 FETCH all_c INTO all_rec;
3187 EXIT WHEN all_c%NOTFOUND;
3188
3189 select to_char(mrp_form_query_s.nextval)
3190 into item_key
3191 from dual;
3192
3193 wf_engine.CreateProcess( itemtype => item_type,
3194 itemkey => item_key,
3195 process => l_process);
3196
3197 wf_engine.SetItemAttrNumber( itemtype => item_type,
3198 itemkey => item_key,
3199 aname => 'EXCEPTION_TYPE_ID',
3200 avalue => 70); -- new exception type
3201
3202 wf_engine.SetItemAttrNumber( itemtype => item_type,
3203 itemkey => item_key,
3204 aname => 'ORDER_TYPE_CODE',
3205 avalue => -30); -- from release so
3206
3207 wf_engine.SetItemAttrNumber( itemtype => item_type,
3208 itemkey => item_key,
3209 aname => 'TRANSACTION_ID',
3210 avalue => all_rec.demand_id);
3211
3212 wf_engine.SetItemAttrNumber( itemtype => item_type,
3213 itemkey => item_key,
3214 aname => 'INVENTORY_ITEM_ID',
3215 avalue => all_rec.inventory_item_id);
3216
3217 wf_engine.SetItemAttrNumber( itemtype => item_type,
3218 itemkey => item_key,
3219 aname => 'ORGANIZATION_ID',
3220 avalue => all_rec.organization_id);
3221
3222 wf_engine.SetItemAttrNumber( itemtype => item_type,
3223 itemkey => item_key,
3224 aname => 'INSTANCE_ID',
3225 avalue => all_rec.sr_instance_id);
3226
3227 wf_engine.SetItemAttrNumber( itemtype => item_type,
3228 itemkey => item_key,
3229 aname => 'SUPPLIER_ID',
3230 avalue => p_batch_id);
3231
3232 wf_engine.SetItemAttrNumber( itemtype => item_type,
3233 itemkey => item_key,
3234 aname => 'PLAN_ID',
3235 avalue => all_rec.plan_id);
3236
3237 wf_engine.SetItemAttrNumber( itemtype => item_type,
3238 itemkey => item_key,
3239 aname => 'PRE_PRSNG_LEAD_TIME',
3240 avalue => all_rec.old_lead_time);
3241
3242 wf_engine.SetItemAttrNumber( itemtype => item_type,
3243 itemkey => item_key,
3244 aname => 'POST_PRSNG_LEAD_TIME',
3245 avalue => all_rec.new_lead_time);
3246
3247 wf_engine.SetItemAttrText( itemtype => item_type,
3248 itemkey => item_key,
3249 aname => 'ORGANIZATION_CODE',
3250 avalue => all_rec.org_code);
3251
3252 wf_engine.SetItemAttrText( itemtype => item_type,
3253 itemkey => item_key,
3254 aname => 'PLAN_NAME',
3255 avalue => all_rec.plan_name);
3256
3257 wf_engine.SetItemAttrText( itemtype => item_type,
3258 itemkey => item_key,
3259 aname => 'DEPARTMENT_LINE_CODE',
3260 avalue => all_rec.to_org);
3261
3262 wf_engine.SetItemAttrText( itemtype => item_type,
3263 itemkey => item_key,
3264 aname => 'ITEM_DISPLAY_NAME',
3265 avalue => all_rec.item_name);
3266
3267 wf_engine.SetItemAttrText( itemtype => item_type,
3268 itemkey => item_key,
3269 aname => 'ITEM_DESCRIPTION',
3270 avalue => all_rec.item_desc);
3271
3272 wf_engine.SetItemAttrDate( itemtype => item_type,
3273 itemkey => item_key,
3274 aname => 'FROM_DATE',
3275 avalue => all_rec.old_ship_date);
3276
3277 wf_engine.SetItemAttrDate( itemtype => item_type,
3278 itemkey => item_key,
3279 aname => 'TO_DATE',
3280 avalue => all_rec.new_ship_date);
3281
3282 wf_engine.SetItemAttrDate( itemtype => item_type,
3283 itemkey => item_key,
3284 aname => 'DATE1',
3285 avalue => all_rec.old_arrival_date);
3286
3287 wf_engine.SetItemAttrDate( itemtype => item_type,
3288 itemkey => item_key,
3289 aname => 'DATE2',
3290 avalue => all_rec.new_arrival_date);
3291
3292 wf_engine.SetItemAttrDate( itemtype => item_type,
3293 itemkey => item_key,
3294 aname => 'DATE3',
3295 avalue => all_rec.earliest_ship_date);
3296
3297 wf_engine.SetItemAttrText( itemtype => item_type,
3298 itemkey => item_key,
3299 aname => 'CUSTOMER_NAME',
3300 avalue => all_rec.customer_name);
3301
3302 wf_engine.SetItemAttrText( itemtype => item_type,
3303 itemkey => item_key,
3304 aname => 'RESOURCE_CODE',
3305 avalue => all_rec.customer_site);
3306
3307 wf_engine.SetItemAttrText( itemtype => item_type,
3308 itemkey => item_key,
3309 aname => 'SUPPLIER_NAME',
3310 avalue => all_rec.old_ship_method);
3311
3312 wf_engine.SetItemAttrText( itemtype => item_type,
3313 itemkey => item_key,
3314 aname => 'SUPPLIER_SITE_CODE',
3315 avalue => all_rec.new_ship_method);
3316
3317 wf_engine.SetItemAttrText( itemtype => item_type,
3318 itemkey => item_key,
3319 aname => 'ORDER_NUMBER',
3320 avalue => all_rec.order_number);
3321
3322 wf_engine.SetItemAttrText( itemtype => item_type,
3323 itemkey => item_key,
3324 aname => 'LOT_NUMBER',
3325 avalue => all_rec.line_number);
3326
3327 wf_engine.SetItemAttrText( itemtype => item_type,
3328 itemkey => item_key,
3329 aname => 'PLANNING_GROUP',
3330 avalue => all_rec.atp_override_flag);
3331
3332 wf_engine.SetItemAttrText( itemtype => item_type,
3333 itemkey => item_key,
3334 aname => 'END_ITEM_DISPLAY_NAME',
3335 avalue => all_rec.orig_item_name);
3336
3337 wf_engine.SetItemAttrText( itemtype => item_type,
3338 itemkey => item_key,
3339 aname => 'END_ITEM_DESCRIPTION',
3340 avalue => all_rec.orig_item_desc);
3341
3342 wf_engine.StartProcess( itemtype => item_type,
3343 itemkey => item_key);
3344 FND_FILE.PUT_LINE(FND_FILE.LOG,'item_key='||item_key||', item_type='||item_type);
3345
3346 END LOOP;
3347 CLOSE all_c;
3348
3349 END start_so_release_workflow;
3350
3351 FUNCTION date_offset(p_org_id number, p_instance_id number,
3352 p_bucket_type number,
3353 p_date date, p_offset_days number) return date is
3354 p_new_date date;
3355 p_new_offset number;
3356 p_minutes number := 0;
3357 BEGIN
3358 -- 6142627, msc_calendar.date_offset will round up offset_days
3359 if ceil(p_offset_days) <> p_offset_days then
3360 p_new_offset := floor(p_offset_days);
3361 else
3362 p_new_offset := p_offset_days;
3363 end if;
3364
3365 p_new_date := msc_calendar.date_offset(
3366 p_org_id,
3367 p_instance_id,
3368 p_bucket_type,
3369 p_date,
3370 p_new_offset);
3371
3372 --dbms_output.put_line('p_new_date='||p_new_date);
3373
3374 -- msc_calendar.date_offset will remove the timestamp
3375 if to_char(p_date,'HH24:MI:SS') <> '00:00:00' then
3376 p_minutes := (p_date - trunc(p_date)) *24*60;
3377 --dbms_output.put_line('timestamp: p_minutes='||p_minutes);
3378 end if;
3379
3380 if p_new_offset <> p_offset_days then
3381 -- need to calculate the partial day offset in minutes
3382 p_minutes := p_minutes +
3383 (p_offset_days - p_new_offset) *24*60;
3384 --dbms_output.put_line('partial offset: p_minutes='||p_minutes);
3385 end if;
3386
3387 if p_minutes > 0 then
3388 if p_minutes >= 24*60 then
3389 -- greater than one day, should find the next working day
3390 p_new_date :=
3391 msc_calendar.date_offset(
3392 p_org_id,
3393 p_instance_id,
3394 p_bucket_type,
3395 p_new_date,
3396 1);
3397 p_minutes := p_minutes - 24*60;
3398 --dbms_output.put_line('partial more than a day: p_minutes='||p_minutes||',p_new_date='||p_new_date);
3399 end if; -- if p_minutes >= 24*60
3400 p_new_date := p_new_date + ceil(p_minutes)/(24*60);
3401 --dbms_output.put_line('p_minutes='||p_minutes||',p_new_date='||p_new_date);
3402 end if; -- if p_minutes > 0 then
3403
3404 return p_new_date;
3405 END date_offset;
3406
3407 END msc_rel_wf;