[Home] [Help]
PACKAGE BODY: APPS.MRP_MSC_EXP_WF
Source
1 PACKAGE BODY mrp_msc_exp_wf AS
2 /*$Header: MRPAPWFB.pls 120.0.12010000.1 2008/07/28 04:46:56 appldev ship $ */
3
4 PROCEDURE CheckUser(itemtype in varchar2,
5 itemkey in varchar2,
6 actid in number,
7 funcmode in varchar2,
8 resultout out NOCOPY varchar2) is
9
10 l_user_type varchar2(20) :=
11 wf_engine.GetActivityAttrText( itemtype => itemtype,
12 itemkey => itemkey,
13 actid => actid,
14 aname => 'USER_TYPE');
15 l_planner varchar2(50) :=
16 wf_engine.GetItemAttrText( itemtype => itemtype,
17 itemkey => itemkey,
18 aname => 'PLANNER');
19
20 -- skanta
21 l_salesrep varchar2(30) :=
22 wf_engine.GetItemAttrText( itemtype => itemtype,
23 itemkey => itemkey,
24 aname => 'SALESREP');
25 l_order_type number :=
26 wf_engine.GetItemAttrNumber( itemtype => itemtype,
27 itemkey => itemkey,
28 aname => 'ORDER_TYPE_CODE');
29
30 l_exception_type number :=
31 wf_engine.GetItemAttrNumber( itemtype => itemtype,
32 itemkey => itemkey,
33 aname => 'EXCEPTION_TYPE_ID');
34
35 l_salesrep_name varchar2(320);
36 l_user_name varchar2(50);
37 l_msg varchar2(30);
38
39 CURSOR c_salesrep is
40 select a.name
41 from wf_roles a,
42 jtf_rs_salesreps b
43 where a.orig_system = 'PER'
44 and a.orig_system_id = b.person_id
45 and b.salesrep_id = to_number(l_salesrep)
46 and a.status = 'ACTIVE'
47 and rownum = 1;
48 --
49 BEGIN
50 if (funcmode = 'RUN') then
51 l_user_name :=
52 wf_engine.GetItemAttrText( itemtype => itemtype,
53 itemkey => itemkey,
54 aname => l_user_type);
55
56 if (l_user_name is null) then
57 resultout := 'COMPLETE:NOT_FOUND';
58 return;
59 else
60 -- skanta
61 IF (l_exception_type in (13,15,24,25,49,70)) then
62 IF (l_salesrep is not null) then
63 OPEN c_salesrep;
64 FETCH c_salesrep INTO l_salesrep_name;
65 CLOSE c_salesrep;
66
67 IF l_salesrep_name is NOT NULL THEN
68 wf_engine.SetItemAttrText( itemtype => itemtype,
69 itemkey => itemkey,
70 aname => 'SALESREP',
71 avalue => l_salesrep_name);
72 END IF;
73 END IF;
74 END IF;
75 l_msg := GetMessageName(l_exception_type,
76 l_order_type,
77 l_user_type);
78 wf_engine.SetItemAttrText( itemtype => itemtype,
79 itemkey => itemkey,
80 aname => 'MESSAGE_NAME',
81 avalue => l_msg);
82
83 resultout := 'COMPLETE:FOUND';
84 return;
85 end if;
86 end if;
87
88 if (funcmode = 'CANCEL') then
89 resultout := 'COMPLETE:';
90 return;
91 end if;
92
93 if (funcmode = 'TIMEOUT') then
94 resultout := 'COMPLETE:';
95 return;
96 end if;
97 EXCEPTION
98 when others then
99 wf_core.context('MRP_MSC_EXP_WF', 'CheckUser', itemtype, itemkey, actid, funcmode);
100 raise;
101 END CheckUser;
102
103 PROCEDURE CheckPartner(itemtype in varchar2,
104 itemkey in varchar2,
105 actid in number,
106 funcmode in varchar2,
107 resultout out NOCOPY varchar2) is
108
109 l_partner_type varchar2(20) :=
110 wf_engine.GetActivityAttrText( itemtype => itemtype,
111 itemkey => itemkey,
112 actid => actid,
113 aname => 'PARTNER_TYPE');
114
115 l_order_type number :=
116 wf_engine.GetItemAttrNumber( itemtype => itemtype,
117 itemkey => itemkey,
118 aname => 'ORDER_TYPE_CODE');
119
120 l_exception_type number :=
121 wf_engine.GetItemAttrNumber( itemtype => itemtype,
122 itemkey => itemkey,
123 aname => 'EXCEPTION_TYPE_ID');
124
125 l_msg varchar2(30);
126 l_partner_name varchar2(50);
127 BEGIN
128 if (funcmode = 'RUN') then
129 l_partner_name :=
130 wf_engine.GetItemAttrText( itemtype => itemtype,
131 itemkey => itemkey,
132 aname => l_partner_type);
133
134 if (l_partner_name is null) then
135 resultout := 'COMPLETE:NOT_FOUND';
136 return;
137 else
138 l_msg := GetMessageName(l_exception_type,
139 l_order_type,
140 l_partner_type);
141 wf_engine.SetItemAttrText( itemtype => itemtype,
142 itemkey => itemkey,
143 aname => 'MESSAGE_NAME',
144 avalue => l_msg);
145 resultout := 'COMPLETE:FOUND';
146 return;
147 end if;
148 end if;
149
150 if (funcmode = 'CANCEL') then
151 resultout := 'COMPLETE:';
152 return;
153 end if;
154
155 if (funcmode = 'TIMEOUT') then
156 resultout := 'COMPLETE:';
157 return;
158 end if;
159 EXCEPTION
160 when others then
161 wf_core.context('MRP_MSC_EXP_WF', 'CheckPartner', itemtype, itemkey, actid, funcmode);
162 raise;
163 END CheckPartner;
164
165 PROCEDURE IsType19( itemtype in varchar2,
166 itemkey in varchar2,
167 actid in number,
168 funcmode in varchar2,
169 resultout out NOCOPY varchar2) is
170
171 l_exception_type number :=
172 wf_engine.GetItemAttrNumber( itemtype => itemtype,
173 itemkey => itemkey,
174 aname => 'EXCEPTION_TYPE_ID');
175
176 BEGIN
177 if (funcmode = 'RUN') then
178 if (l_exception_type = 19) then
179 resultout := 'COMPLETE:Y';
180 else
181 resultout := 'COMPLETE:N';
182 end if;
183 return;
184 end if;
185
186 if (funcmode = 'CANCEL') then
187 resultout := 'COMPLETE:';
188 return;
189 end if;
190
191 if (funcmode = 'TIMEOUT') then
192 resultout := 'COMPLETE:';
193 return;
194 end if;
195 EXCEPTION
196 when others then
197 wf_core.context('MRP_MSC_EXP_WF', 'IsType19', itemtype, itemkey, actid, funcmode);
198 raise;
199 END IsType19;
200
201
202 -- call back a wf process at destition instance for completion
203 PROCEDURE CallbackDestWF(itemtype in varchar2,
204 itemkey in varchar2,
205 actid in number,
206 funcmode in varchar2,
207 resultout out NOCOPY varchar2) is
208
209 l_result varchar2(20) :=
210 wf_engine.GetActivityAttrText( itemtype => itemtype,
211 itemkey => itemkey,
212 actid => actid,
213 aname => 'SR_RESULT');
214
215 l_db_link varchar2(30) :=
216 wf_engine.GetItemAttrText( itemtype => itemtype,
217 itemkey => itemkey,
218 aname => 'APPS_PS_DBLINK');
219
220 l_exception_type number :=
221 wf_engine.GetItemAttrNumber( itemtype => itemtype,
222 itemkey => itemkey,
223 aname => 'EXCEPTION_TYPE_ID');
224
225 l_transaction_id number :=
226 wf_engine.GetItemAttrNumber( itemtype => itemtype,
227 itemkey => itemkey,
228 aname => 'TRANSACTION_ID');
229
230 l_dest_item_type varchar2(50) := 'MSCEXPWF';
231 l_dest_item_key varchar2(100);
232 l_dest_process varchar2(50);
233 l_text varchar2(200);
234 l_numb number;
235 l_date Date;
236 sql_stmt varchar2(2000);
237 p_request_id number :=0;
238 BEGIN
239 if (funcmode = 'RUN') then
240 l_dest_item_key := substr(itemkey,1,instr(itemkey,'-',-2)-1)
241 || '-CALLBACK';
242
243 -- now find out call back process, and start it.
244 if (l_exception_type in (1, 2, 3, 12, 14, 16, 20, 26, 27)) then
245 l_dest_process := 'EXCEPTION_PROCESS1';
246 elsif (l_exception_type in (28, 37)) then
247 l_dest_process := 'EXCEPTION_PROCESS5';
248 elsif (l_exception_type in (6, 7, 8, 9, 10)) then
249 l_dest_process := 'EXCEPTION_PROCESS2';
250 elsif (l_exception_type in (13, 15, 24, 25)) then
251 l_dest_process := 'EXCEPTION_PROCESS3';
252 elsif (l_exception_type in (17, 18, 19)) then
253 l_dest_process := 'EXCEPTION_PROCESS4';
254 end if;
255 sql_stmt := 'begin wf_engine.CreateProcess' || l_db_link ||
256 '( itemtype => :l_itemtype,' ||
257 'itemkey => :l_itemkey, ' ||
258 'process => :l_process);end;';
259 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type, l_dest_item_key,
260 l_dest_process;
261
262 -- now copy attributes to destination wf process
263 -- we could only copy those insterested attributes,
264 -- but we copy all for debug purpose.
265
266 -- SR_RESULT.
267 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
268 '(itemtype => :item_type,' ||
269 ' itemkey => :item_key,' ||
270 ' aname => ''SR_RESULT'',' ||
271 ' avalue => :l_text);end;';
272 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_result;
273
274 -- EXCEPTION_TYPE_ID.
275 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
276 '(itemtype => :item_type,' ||
277 ' itemkey => :item_key,' ||
278 ' aname => ''EXCEPTION_TYPE_ID'',' ||
279 ' avalue => :l_numb);end;';
280 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_exception_type;
281
282 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
283 '(itemtype => :item_type,' ||
284 ' itemkey => :item_key,' ||
285 ' aname => ''TRANSACTION_ID'',' ||
286 ' avalue => :l_numb);end;';
287 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_transaction_id;
288
289 -- APPS_PS_DBLINK
290 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
291 '(itemtype => :item_type,' ||
292 ' itemkey => :item_key,' ||
293 ' aname => ''APPS_PS_DBLINK'',' ||
294 ' avalue => :l_text);end;';
295 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_db_link;
296
297 --BUYER. we don't need to set back BUYER, set it for debug
298 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
299 itemkey => itemkey,
300 aname => 'BUYER');
301 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
302 '(itemtype => :item_type,' ||
303 ' itemkey => :item_key,' ||
304 ' aname => ''BUYER'',' ||
305 ' avalue => :l_text);end;';
306 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
307
308 -- we don't need to set back CUSTCNT, for debug only
309 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
310 itemkey => itemkey,
311 aname => 'CUSTCNT');
312 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
313 '(itemtype => :item_type,' ||
314 ' itemkey => :item_key,' ||
315 ' aname => ''CUSTCNT'',' ||
316 ' avalue => :l_text);end;';
317 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
318
319 -- customer_name
320 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
321 itemkey => itemkey,
322 aname => 'CUSTOMER_NAME');
323 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
324 '(itemtype => :item_type,' ||
325 ' itemkey => :item_key,' ||
326 ' aname => ''CUSTOMER_NAME'',' ||
327 ' avalue => :l_text);end;';
328 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
329
330 -- customer_ID.
331 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
332 itemkey => itemkey,
333 aname => 'CUSTOMER_ID');
334 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
335 '(itemtype => :item_type,' ||
336 ' itemkey => :item_key,' ||
337 ' aname => ''CUSTOMER_ID'',' ||
338 ' avalue => :l_numb);end;';
339 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
340
341 -- Days_compressed
342 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
343 itemkey => itemkey,
344 aname => 'DAYS_COMPRESSED');
345 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
346 '(itemtype => :item_type,' ||
347 ' itemkey => :item_key,' ||
348 ' aname => ''DAYS_COMPRESSED'',' ||
349 ' avalue => :l_numb);end;';
350 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
351
352 -- DB_LINK. we may not need.
353 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
354 itemkey => itemkey,
355 aname => 'DB_LINK');
356 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
357 '(itemtype => :item_type,' ||
358 ' itemkey => :item_key,' ||
359 ' aname => ''DB_LINK'',' ||
360 ' avalue => :l_text);end;';
361 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
362
363 -- DEPARTMENT_LINE_CODE.
364 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
365 itemkey => itemkey,
366 aname => 'DEPARTMENT_LINE_CODE');
367 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
368 '(itemtype => :item_type,' ||
369 ' itemkey => :item_key,' ||
370 ' aname => ''DEPARTMENT_LINE_CODE'',' ||
371 ' avalue => :l_text);end;';
372 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
373
374 -- due_date.
375 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
376 itemkey => itemkey,
377 aname => 'DUE_DATE');
378 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
379 '(itemtype => :item_type,' ||
380 ' itemkey => :item_key,' ||
381 ' aname => ''DUE_DATE'',' ||
382 ' avalue => :l_date);end;';
383 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_date;
384
385 -- end_item_display_name.
386 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
387 itemkey => itemkey,
388 aname => 'END_ITEM_DISPLAY_NAME');
389 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
390 '(itemtype => :item_type,' ||
391 ' itemkey => :item_key,' ||
392 ' aname => ''END_ITEM_DISPLAY_NAME'',' ||
393 ' avalue => :l_text);end;';
394 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
395
396 -- end_item_description
397
398 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
399 itemkey => itemkey,
400 aname => 'END_ITEM_DESCRIPTION');
401 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
402 '(itemtype => :item_type,' ||
403 ' itemkey => :item_key,' ||
404 ' aname => ''END_ITEM_DESCRIPTION'',' ||
405 ' avalue => :l_text);end;';
406 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
407
408
409 --END_ORDER_NUMBER
410 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
411 itemkey => itemkey,
412 aname => 'END_ORDER_NUMBER');
413 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
414 '(itemtype => :item_type,' ||
415 ' itemkey => :item_key,' ||
416 ' aname => ''END_ORDER_NUMBER'',' ||
417 ' avalue => :l_text);end;';
418 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
419
420 -- EXCEPTION_DESCRIPTION
421 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
422 itemkey => itemkey,
423 aname => 'EXCEPTION_DESCRIPTION');
424 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
425 '(itemtype => :item_type,' ||
426 ' itemkey => :item_key,' ||
427 ' aname => ''EXCEPTION_DESCRIPTION'',' ||
428 ' avalue => :l_text);end;';
429 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
430
431 --EXCEPTION_ID
432 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
433 itemkey => itemkey,
434 aname => 'EXCEPTION_ID');
435 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
436 '(itemtype => :item_type,' ||
437 ' itemkey => :item_key,' ||
438 ' aname => ''EXCEPTION_ID'',' ||
439 ' avalue => :l_numb);end;';
440 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
441
442 -- FROM_DATE
443 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
444 itemkey => itemkey,
445 aname => 'FROM_DATE');
446 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
447 '(itemtype => :item_type,' ||
448 ' itemkey => :item_key,' ||
449 ' aname => ''FROM_DATE'',' ||
450 ' avalue => :l_date);end;';
451 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_date;
452
453 -- FROM_PRJ_MGR
454 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
455 itemkey => itemkey,
456 aname => 'FROM_PRJ_MGR');
457 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
458 '(itemtype => :item_type,' ||
459 ' itemkey => :item_key,' ||
460 ' aname => ''FROM_PRJ_MGR'',' ||
461 ' avalue => :l_text);end;';
462 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
463
464 --INSTANCE_ID
465 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
466 itemkey => itemkey,
467 aname => 'INSTANCE_ID');
468 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
469 '(itemtype => :item_type,' ||
470 ' itemkey => :item_key,' ||
471 ' aname => ''INSTANCE_ID'',' ||
472 ' avalue => :l_numb);end;';
473 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
474
475 --INVENTORY_ITEM_ID
476 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
477 itemkey => itemkey,
478 aname => 'INVENTORY_ITEM_ID');
479 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
480 '(itemtype => :item_type,' ||
481 ' itemkey => :item_key,' ||
482 ' aname => ''INVENTORY_ITEM_ID'',' ||
483 ' avalue => :l_numb);end;';
484 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
485
486 --IS_CALL_BACK
487 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
488 '(itemtype => :item_type,' ||
489 ' itemkey => :item_key,' ||
490 ' aname => ''IS_CALL_BACK'',' ||
491 ' avalue => ''Y'');end;';
492 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key;
493
494 -- ITEM_DISPLAY_NAME
495 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
496 itemkey => itemkey,
497 aname => 'ITEM_DISPLAY_NAME');
498 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
499 '(itemtype => :item_type,' ||
500 ' itemkey => :item_key,' ||
501 ' aname => ''ITEM_DISPLAY_NAME'',' ||
502 ' avalue => :l_text);end;';
503 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
504
505 -- ITEM_DESCRIPTION
506 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
507 itemkey => itemkey,
508 aname => 'ITEM_DESCRIPTION');
509 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
510 '(itemtype => :item_type,' ||
511 ' itemkey => :item_key,' ||
512 ' aname => ''ITEM_DESCRIPTION'',' ||
513 ' avalue => :l_text);end;';
514 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
515
516 -- LOT_NUMBER
517 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
518 itemkey => itemkey,
519 aname => 'LOT_NUMBER');
520 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
521 '(itemtype => :item_type,' ||
522 ' itemkey => :item_key,' ||
523 ' aname => ''LOT_NUMBER'',' ||
524 ' avalue => :l_text);end;';
525 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
526
527 -- ORDER_NUMBER
528 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
529 itemkey => itemkey,
530 aname => 'ORDER_NUMBER');
531 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
532 '(itemtype => :item_type,' ||
533 ' itemkey => :item_key,' ||
534 ' aname => ''ORDER_NUMBER'',' ||
535 ' avalue => :l_text);end;';
536 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
537
538 -- ORDER_TYPE_CODE
539 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
540 itemkey => itemkey,
541 aname => 'ORDER_TYPE_CODE');
542 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
543 '(itemtype => :item_type,' ||
544 ' itemkey => :item_key,' ||
545 ' aname => ''ORDER_TYPE_CODE'',' ||
546 ' avalue => :l_numb);end;';
547 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
548
549 -- ORGANIZATION_CODE
550 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
551 itemkey => itemkey,
552 aname => 'ORGANIZATION_CODE');
553 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
554 '(itemtype => :item_type,' ||
555 ' itemkey => :item_key,' ||
556 ' aname => ''ORGANIZATION_CODE'',' ||
557 ' avalue => :l_text);end;';
558 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
559
560 --ORGANIZATION_ID
561 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
562 itemkey => itemkey,
563 aname => 'ORGANIZATION_ID');
564 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
565 '(itemtype => :item_type,' ||
566 ' itemkey => :item_key,' ||
567 ' aname => ''ORGANIZATION_ID'',' ||
568 ' avalue => :l_numb);end;';
569 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
570
571 --PLAN_ID
572 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'PLAN_ID');
575 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
576 '(itemtype => :item_type,' ||
577 ' itemkey => :item_key,' ||
578 ' aname => ''PLAN_ID'',' ||
579 ' avalue => :l_numb);end;';
580 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
581
582 -- PLAN_NAME
583 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
584 itemkey => itemkey,
585 aname => 'PLAN_NAME');
586 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
587 '(itemtype => :item_type,' ||
588 ' itemkey => :item_key,' ||
589 ' aname => ''PLAN_NAME'',' ||
590 ' avalue => :l_text);end;';
591 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
592
593 -- PLANNER
594 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
595 itemkey => itemkey,
596 aname => 'PLANNER');
597 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
598 '(itemtype => :item_type,' ||
599 ' itemkey => :item_key,' ||
600 ' aname => ''PLANNER'',' ||
601 ' avalue => :l_text);end;';
602 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
603
604 -- PLANNING_GROUP
605 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
606 itemkey => itemkey,
607 aname => 'PLANNING_GROUP');
608 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
609 '(itemtype => :item_type,' ||
610 ' itemkey => :item_key,' ||
611 ' aname => ''PLANNING_GROUP'',' ||
612 ' avalue => :l_text);end;';
613 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
614
615 -- PROJECT_NUMBER
616 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
617 itemkey => itemkey,
618 aname => 'PROJECT_NUMBER');
619 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
620 '(itemtype => :item_type,' ||
621 ' itemkey => :item_key,' ||
622 ' aname => ''PROJECT_NUMBER'',' ||
623 ' avalue => :l_text);end;';
624 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
625
626 --PRE_PROCESSING_LEAD_TIME
627 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
628 itemkey => itemkey,
629 aname => 'PRE_PRSNG_LEAD_TIME');
630 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
631 '(itemtype => :item_type,' ||
632 ' itemkey => :item_key,' ||
633 ' aname => ''PRE_PRSNG_LEAD_TIME'',' ||
634 ' avalue => :l_text);end;';
635 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
636
637 -- PROCESSING_LEAD_TIME
638 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
639 itemkey => itemkey,
640 aname => 'PRSNG_LEAD_TIME');
641 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
642 '(itemtype => :item_type,' ||
643 ' itemkey => :item_key,' ||
644 ' aname => ''PRSNG_LEAD_TIME'',' ||
645 ' avalue => :l_text);end;';
646 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
647
648 -- POST_PROCESSING_LEAD_TIME
649 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
650 itemkey => itemkey,
651 aname => 'POST_PRSNG_LEAD_TIME');
652 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
653 '(itemtype => :item_type,' ||
654 ' itemkey => :item_key,' ||
655 ' aname => ''POST_PRSNG_LEAD_TIME'',' ||
656 ' avalue => :l_text);end;';
657 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
658
659
660 -- QUANTITY
661 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
662 itemkey => itemkey,
663 aname => 'QUANTITY');
664 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
665 '(itemtype => :item_type,' ||
666 ' itemkey => :item_key,' ||
667 ' aname => ''QUANTITY'',' ||
668 ' avalue => :l_text);end;';
669 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
670
671 -- RESOURCE_CODE
672 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
673 itemkey => itemkey,
674 aname => 'RESOURCE_CODE');
675 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
676 '(itemtype => :item_type,' ||
677 ' itemkey => :item_key,' ||
678 ' aname => ''RESOURCE_CODE'',' ||
679 ' avalue => :l_text);end;';
680 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
681
682 -- SUPCNT
683 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
684 itemkey => itemkey,
685 aname => 'SUPCNT');
686 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
687 '(itemtype => :item_type,' ||
688 ' itemkey => :item_key,' ||
689 ' aname => ''SUPCNT'',' ||
690 ' avalue => :l_text);end;';
691 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
692
693 --SUPPLIER_ID
694 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
695 itemkey => itemkey,
696 aname => 'SUPPLIER_ID');
697 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
698 '(itemtype => :item_type,' ||
699 ' itemkey => :item_key,' ||
700 ' aname => ''SUPPLIER_ID'',' ||
701 ' avalue => :l_numb);end;';
702 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
703
704 -- SUPPLIER_NAME
705 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
706 itemkey => itemkey,
707 aname => 'SUPPLIER_NAME');
708 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
709 '(itemtype => :item_type,' ||
710 ' itemkey => :item_key,' ||
711 ' aname => ''SUPPLIER_NAME'',' ||
712 ' avalue => :l_text);end;';
713 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
714
715 -- SUPPLIER_SITE_CODE
716 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
717 itemkey => itemkey,
718 aname => 'SUPPLIER_SITE_CODE');
719 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
720 '(itemtype => :item_type,' ||
721 ' itemkey => :item_key,' ||
722 ' aname => ''SUPPLIER_SITE_CODE'',' ||
723 ' avalue => :l_text);end;';
724 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
725
726 --SUPPLIER_SITE_ID
727 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
728 itemkey => itemkey,
729 aname => 'SUPPLIER_SITE_ID');
730 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
731 '(itemtype => :item_type,' ||
732 ' itemkey => :item_key,' ||
733 ' aname => ''SUPPLIER_SITE_ID'',' ||
734 ' avalue => :l_numb);end;';
735 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
736
737 -- SUPPLY_TYPE
738 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
739 itemkey => itemkey,
740 aname => 'SUPPLY_TYPE');
741 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
742 '(itemtype => :item_type,' ||
743 ' itemkey => :item_key,' ||
744 ' aname => ''SUPPLY_TYPE'',' ||
745 ' avalue => :l_text);end;';
746 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
747
748 -- TASK_NUMBER
749 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
750 itemkey => itemkey,
751 aname => 'TASK_NUMBER');
752 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
753 '(itemtype => :item_type,' ||
754 ' itemkey => :item_key,' ||
755 ' aname => ''TASK_NUMBER'',' ||
756 ' avalue => :l_text);end;';
757 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
758
759 -- TO_DATE
760 l_date := wf_engine.GetItemAttrDate( itemtype => itemtype,
761 itemkey => itemkey,
762 aname => 'TO_DATE');
763 sql_stmt := 'begin wf_engine.SetItemAttrDate' || l_db_link ||
764 '(itemtype => :item_type,' ||
765 ' itemkey => :item_key,' ||
766 ' aname => ''TO_DATE'',' ||
767 ' avalue => :l_date);end;';
768 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_date;
769
770 -- TO_PRJ_MGR
771 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
772 itemkey => itemkey,
773 aname => 'TO_PRJ_MGR');
774 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
775 '(itemtype => :item_type,' ||
776 ' itemkey => :item_key,' ||
777 ' aname => ''TO_PRJ_MGR'',' ||
778 ' avalue => :l_text);end;';
779 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
780
781 -- TO_PROJECT_NUMBER
782 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
783 itemkey => itemkey,
784 aname => 'TO_PROJECT_NUMBER');
785 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
786 '(itemtype => :item_type,' ||
787 ' itemkey => :item_key,' ||
788 ' aname => ''TO_PROJECT_NUMBER'',' ||
789 ' avalue => :l_text);end;';
790 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
791
792 -- TO_TASK_NUMBER
793 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
794 itemkey => itemkey,
795 aname => 'TO_TASK_NUMBER');
796 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
797 '(itemtype => :item_type,' ||
798 ' itemkey => :item_key,' ||
799 ' aname => ''TO_TASK_NUMBER'',' ||
800 ' avalue => :l_text);end;';
801 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
802
803 -- URL1
804 l_text := wf_engine.GetItemAttrText( itemtype => itemtype,
805 itemkey => itemkey,
806 aname => 'URL1');
807 sql_stmt := 'begin wf_engine.SetItemAttrText' || l_db_link ||
808 '(itemtype => :item_type,' ||
809 ' itemkey => :item_key,' ||
810 ' aname => ''URL1'',' ||
811 ' avalue => :l_text);end;';
812 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_text;
813
814 -- UTILIZATION_RATE
815 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
816 itemkey => itemkey,
817 aname => 'UTILIZATION_RATE');
818 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
819 '(itemtype => :item_type,' ||
820 ' itemkey => :item_key,' ||
821 ' aname => ''UTILIZATION_RATE'',' ||
822 ' avalue => :l_numb);end;';
823 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
824
825 -- CAPACITY_REQUIREMENT
826 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
827 itemkey => itemkey,
828 aname => 'CAPACITY_REQUIREMENT');
829 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
830 '(itemtype => :item_type,' ||
831 ' itemkey => :item_key,' ||
832 ' aname => ''CAPACITY_REQUIREMENT'',' ||
833 ' avalue => :l_numb);end;';
834 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
835
836 -- REQUIRED_QUANTITY
837 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
838 itemkey => itemkey,
839 aname => 'REQUIRED_QUANTITY');
840 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
841 '(itemtype => :item_type,' ||
842 ' itemkey => :item_key,' ||
843 ' aname => ''REQUIRED_QUANTITY'',' ||
844 ' avalue => :l_numb);end;';
845 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
846
847 -- PROJECTED_AVAILABLE_BALANCE
848 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
849 itemkey => itemkey,
850 aname => 'PROJECTED_AVAILABLE_BALANCE');
851 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
852 '(itemtype => :item_type,' ||
853 ' itemkey => :item_key,' ||
854 ' aname => ''PROJECTED_AVAILABLE_BALANCE'',' ||
855 ' avalue => :l_numb);end;';
856 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
857
858 -- AVAILABLE_QUANTITY
859 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
860 itemkey => itemkey,
861 aname => 'AVAILABLE_QUANTITY');
862 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
863 '(itemtype => :item_type,' ||
864 ' itemkey => :item_key,' ||
865 ' aname => ''AVAILABLE_QUANTITY'',' ||
866 ' avalue => :l_numb);end;';
867 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
868
869 -- QTY_RELATED_VALUES
870 l_numb := wf_engine.GetItemAttrNumber( itemtype => itemtype,
871 itemkey => itemkey,
872 aname => 'QTY_RELATED_VALUES');
873 sql_stmt := 'begin wf_engine.SetItemAttrNumber' || l_db_link ||
874 '(itemtype => :item_type,' ||
875 ' itemkey => :item_key,' ||
876 ' aname => ''QTY_RELATED_VALUES'',' ||
877 ' avalue => :l_numb);end;';
878 EXECUTE IMMEDIATE sql_stmt USING l_dest_item_type,l_dest_item_key,l_numb;
879
880 wf_engine.SetItemAttrNumber( itemtype => itemtype,
881 itemkey => itemkey,
882 aname => 'REQUEST_ID',
883 avalue => p_request_id);
884
885 resultout := 'COMPLETE:';
886 RETURN;
887 END IF;
888
889 IF (funcmode = 'CANCEL') THEN
890 resultout := 'COMPLETE:';
891 RETURN;
892 END IF;
893
894 IF (funcmode = 'TIMEOUT') THEN
895 resultout := 'COMPLETE:';
896 RETURN;
897 END IF;
898 EXCEPTION
899 when others then
900 wf_core.context('MSC_EXP_WF', 'StartSrWF', itemtype, itemkey, actid, funcmode);
901 raise;
902 END CallbackDestWF;
903
904 FUNCTION GetMessageName(p_exception_type in number,
905 p_order_type in number,
906 p_recipient in varchar2) RETURN varchar2 IS
907 BEGIN
908 if (p_recipient = 'BUYER') then
909 if (p_exception_type = 6) then
910 if (p_order_type = 1) then
911 return 'MSG_6_PO';
912 elsif (p_order_type = 2) then
913 return 'MSG_6_REQ';
914 end if;
915 elsif (p_exception_type = 7) then
916 if (p_order_type = 1) then
917 return 'MSG_7_PO';
918 elsif (p_order_type = 2) then
919 return 'MSG_7_REQ';
920 end if;
921 elsif (p_exception_type = 8) then
922 if (p_order_type = 1) then
923 return 'MSG_8_PO';
924 elsif (p_order_type = 2) then
925 return 'MSG_8_REQ';
926 end if;
927 elsif (p_exception_type =10) then
928 return 'MSG_10';
929 elsif (p_exception_type =37) then
930 return 'MSG_37';
931 elsif (p_exception_type =28) then
932 return 'MSG_28';
933 elsif (p_exception_type = 9) then
934 if (p_order_type = 1) then
935 return 'MSG_9_PO';
936 elsif (p_order_type = 2) then
937 return 'MSG_9_REQ';
938 end if;
939 end if;
940 elsif (p_recipient = 'SUPCNT') then
941 if (p_exception_type = 6) then
942 return 'MSG_RESCHEDULE_6_PO';
943 elsif (p_exception_type = 7) then
944 return 'MSG_RESCHEDULE_7_PO';
945 elsif (p_exception_type = 8) then
946 return 'MSG_RESCHEDULE_8_PO';
947 elsif (p_exception_type = 9) then
948 return 'MSG_RESCHEDULE_9_PO';
949 elsif (p_exception_type = 10) then
950 return 'MSG_RESCHEDULE_10';
951 elsif (p_exception_type in (28, 37)) then
952 return 'MSG_37_CHANGE';
953 end if;
954 elsif (p_recipient = 'SALESREP' or p_recipient = 'CUSTCNT') then
955 if (p_exception_type = 13) then
956 return 'MSG_13';
957 elsif (p_exception_type in (15,24,25)) then
958 return 'MSG_15';
959 elsif (p_exception_type = 49) then
960 if (p_order_type=30) then
961 return 'MSG_49_SO';
962 elsif (p_order_type=29) then
963 return 'MSG_49_FORECAST';
964 end if;
965 elsif (p_exception_type = 70) then
966 if (p_order_type=-30) then -- release sales order
967 return 'MSG_RL_SO';
968 else
969 return 'MSG_70';
970 end if;
971 end if;
972 elsif (p_recipient = 'FROM_PRJ_MGR' or p_recipient = 'TO_PRJ_MGR') then
973 if (p_exception_type = 17) then
974 return 'MSG_17';
975 elsif (p_exception_type = 18) then
976 return 'MSG_18';
977 elsif (p_exception_type = 19) then
978 return 'MSG_19';
979 end if;
980 end if;
981 EXCEPTION
982
983 when others then
984 wf_core.context('MSC_EXP_WF', 'GetMessageName', to_char(p_exception_type),
985 to_char(p_order_type));
986 raise;
987
988 END GetMessageName;
989
990 PROCEDURE DeleteActivities( arg_plan_id in number) IS
991
992 TYPE DelExpType is REF CURSOR;
993 delete_activities_c DelExpType;
994 l_item_key varchar2(240);
995 sql_stmt varchar2(500);
996 l_item_type varchar2(20);
997 BEGIN
998
999 l_item_type := 'MRPEXWFS';
1000 sql_stmt := ' SELECT item_key ' ||
1001 ' FROM wf_items' ||
1002 ' WHERE item_type = :l_item_type' ||
1003 ' AND item_key like '''|| to_char(arg_plan_id) || '-%''';
1004
1005 OPEN delete_activities_c for sql_stmt using l_item_type;
1006 LOOP
1007
1008 FETCH DELETE_ACTIVITIES_C INTO l_item_key;
1009 EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND;
1010
1011 -- Later on, add logic to first check if the exception is on
1012 -- other instances before doing this, by exception type or
1013 -- other api
1014 update wf_notifications
1015 set end_date = sysdate
1016 where group_id in
1017 (select notification_id
1018 from wf_item_activity_statuses
1019 where item_type = 'MRPEXWFS'
1020 and item_key = l_item_key
1021 union
1022 select notification_id
1023 from wf_item_activity_statuses_h
1024 where item_type = 'MRPEXWFS'
1025 and item_key = l_item_key);
1026
1027 update wf_items
1028 set end_date = sysdate
1029 where item_type = 'MRPEXWFS'
1030 and item_key = l_item_key;
1031
1032 update wf_item_activity_statuses
1033 set end_date = sysdate
1034 where item_type = 'MRPEXWFS'
1035 and item_key = l_item_key;
1036
1037 update wf_item_activity_statuses_h
1038 set end_date = sysdate
1039 where item_type = 'MRPEXWFS'
1040 and item_key = l_item_key;
1041
1042 wf_purge.total('MRPEXWFS',l_item_key,sysdate);
1043
1044 END LOOP;
1045 CLOSE delete_activities_c;
1046 return;
1047
1048 EXCEPTION
1049 when others then
1050 msc_util.msc_debug('Error in delete activities:'|| to_char(sqlcode) || ':'
1051 || substr(sqlerrm,1,100));
1052
1053 return;
1054 END DeleteActivities;
1055
1056 Procedure launch_background_program(p_planner in varchar2,
1057 p_item_type in varchar2,
1058 p_item_key in varchar2,
1059 p_request_id out NOCOPY number) IS
1060 p_result boolean;
1061 p_user_id number;
1062 p_resp_id number;
1063 p_app_id number;
1064
1065 Begin
1066
1067 select user_id
1068 into p_user_id
1069 from fnd_user
1070 where user_name = p_planner;
1071
1072 SELECT APPLICATION_ID
1073 INTO p_app_id
1074 FROM FND_APPLICATION_VL
1075 WHERE APPLICATION_NAME = 'Oracle Manufacturing' ;
1076
1077 SELECT responsibility_id
1078 INTO p_resp_id
1079 FROM FND_responsibility_vl
1080 where application_Id = p_app_id
1081 and rownum = 1;
1082 fnd_global.apps_initialize(p_user_id, p_resp_id, p_app_id);
1083
1084 p_result := fnd_request.set_mode(true);
1085
1086 -- this will call start_deferred_activity
1087 p_request_id := fnd_request.submit_request(
1088 'MSC',
1089 'MSCWFBG',
1090 null,
1091 null,
1092 false,
1093 p_item_type,
1094 p_item_key);
1095
1096 exception when others then
1097 p_request_id :=0;
1098 raise;
1099 End launch_background_program;
1100
1101 Procedure start_deferred_activity(
1102 errbuf OUT NOCOPY VARCHAR2,
1103 retcode OUT NOCOPY NUMBER,
1104 p_item_type varchar2,
1105 p_item_key varchar2) IS
1106 BEGIN
1107 FND_FILE.PUT_LINE(FND_FILE.LOG,
1108 'start workflow process for '||p_item_type);
1109
1110 FND_FILE.PUT_LINE(FND_FILE.LOG,
1111 'key='||p_item_key);
1112
1113 wf_engine.StartProcess( itemtype => p_item_type,
1114 itemkey => p_item_key);
1115
1116 FND_FILE.PUT_LINE(FND_FILE.LOG,
1117 'done for'||p_item_type);
1118 END start_deferred_activity;
1119 PROCEDURE start_substitute_workflow(from_item varchar2,
1120 substitute_item varchar2,
1121 order_number varchar2,
1122 line_number varchar2,
1123 org_code varchar2,
1124 substitute_org varchar2,
1125 quantity number,
1126 substitute_qty number,
1127 sales_rep varchar2,
1128 customer_contact varchar2) IS
1129 l_process varchar2(50) := 'MSC_SO_SR_PROCESS';
1130 item_type varchar2(50) :='MRPEXWFS';
1131 item_key varchar2(50);
1132 p_text varchar2(80) := 'ATP:Demand satisfied by substituting end items';
1133 BEGIN
1134
1135 select to_char(mrp_form_query_s.nextval)
1136 into item_key
1137 from dual;
1138
1139 wf_engine.CreateProcess( itemtype => item_type,
1140 itemkey => item_key,
1141 process => l_process);
1142
1143 wf_engine.SetItemAttrNumber( itemtype => item_type,
1144 itemkey => item_key,
1145 aname => 'EXCEPTION_TYPE_ID',
1146 avalue => 49);
1147
1148 wf_engine.SetItemAttrText( itemtype => item_type,
1149 itemkey => item_key,
1150 aname => 'ORGANIZATION_CODE',
1151 avalue => org_code);
1152 wf_engine.SetItemAttrText( itemtype => item_type,
1153 itemkey => item_key,
1154 aname => 'ITEM_DISPLAY_NAME',
1155 avalue => from_item);
1156 wf_engine.SetItemAttrText( itemtype => item_type,
1157 itemkey => item_key,
1158 aname => 'EXCEPTION_DESCRIPTION',
1159 avalue => p_text);
1160
1161 wf_engine.SetItemAttrText( itemtype => item_type,
1162 itemkey => item_key,
1163 aname => 'QUANTITY',
1164 avalue => quantity);
1165
1166 wf_engine.SetItemAttrText( itemtype => item_type,
1167 itemkey => item_key,
1168 aname => 'END_ITEM_DISPLAY_NAME',
1169 avalue => substitute_item);
1170
1171 wf_engine.SetItemAttrText( itemtype => item_type,
1172 itemkey => item_key,
1173 aname => 'END_ORDER_NUMBER',
1174 avalue => order_number);
1175
1176 wf_engine.SetItemAttrText( itemtype => item_type,
1177 itemkey => item_key,
1178 aname => 'RESOURCE_CODE',
1179 avalue => substitute_org);
1180
1181 wf_engine.SetItemAttrNumber( itemtype => item_type,
1182 itemkey => item_key,
1183 aname => 'UTILIZATION_RATE',
1184 avalue => substitute_qty);
1185
1186 wf_engine.setItemAttrText( itemtype => item_type,
1187 itemkey => item_key,
1188 aname => 'CUSTCNT',
1189 avalue => customer_contact);
1190
1191 wf_engine.setItemAttrText( itemtype => item_type,
1192 itemkey => item_key,
1193 aname => 'SALESREP',
1194 avalue => sales_rep);
1195
1196 wf_engine.StartProcess( itemtype => item_type,
1197 itemkey => item_key);
1198
1199
1200 END start_substitute_workflow;
1201
1202
1203 END mrp_msc_exp_wf;