[Home] [Help]
PACKAGE BODY: APPS.POS_ORDER_MODIFIERS_PKG
Source
1 PACKAGE BODY POS_ORDER_MODIFIERS_PKG AS
2 /* $Header: POSORDNB.pls 115.11 2004/09/10 20:29:47 jacheung ship $ */
3
4 /*===========================================================================
5 PROCEDURE NAME: updmodifiers()
6 ===========================================================================*/
7
8 PROCEDURE INSERT_TEMP_MODIFIERS(
9 p_asl_id IN NUMBER,
10 p_proc_lead_time IN NUMBER,
11 p_min_order_qty IN NUMBER,
12 p_fixed_lot_multiple IN NUMBER,
13 p_created_by in number,
14 p_error_code OUT NOCOPY VARCHAR2,
15 p_error_message OUT NOCOPY VARCHAR2) is
16
17
18 l_seq number;
19 BEGIN
20
21 /* Update PO_ASL_ATTRIBUTES form ISP */
22 select POS_ORDER_MODIFIERS_TEMP_ID_S.NEXTVAL
23 into l_seq from sys.dual;
24
25 insert into POS_ORDER_MODIFIERS_TEMP (
26 order_mod_request_id,
27 asl_id,
28 last_update_date,
29 last_updated_by,
30 last_update_login,
31 creation_date,
32 created_by,
33 processing_lead_time,
34 min_order_quantity,
35 fixed_lot_multiple,
36 status)
37 values
38 (
39 l_seq,
40 p_asl_id,
41 sysdate,
42 p_created_by,
43 p_created_by,
44 sysdate,
45 p_created_by,
46 p_proc_lead_time,
47 p_min_order_qty,
48 p_fixed_lot_multiple,
49 'NEW');
50
51
52
53 EXCEPTION
54
55 WHEN OTHERS THEN
56
57 p_ERROR_CODE := 'Y';
58 p_ERROR_MESSAGE := 'exception raised during Update';
59
60 END INSERT_TEMP_MODIFIERS;
61
62 PROCEDURE UPDATE_EXIST(p_asl_id in NUMBER,
63 p_return_code out NOCOPY NUMBER) is
64
65 begin
66
67 select count(*)
68 into p_return_code
69 from POS_ORDER_MODIFIERS_TEMP
70 where asl_id=p_asl_id and status='NEW';
71
72 end UPDATE_EXIST;
73
74 PROCEDURE StartWorkflow(p_asl_id in NUMBER) is
75
76 l_seq varchar2(25);
77 l_itemkey varchar2(40);
78 l_itemtype varchar2(20):='POSORDNT';
79 l_count number;
80
81 BEGIN
82
83 /* Update PO_ASL_ATTRIBUTES form ISP */
84 select count(*)
85 into l_count
86 from POS_ORDER_MODIFIERS_TEMP
87 where asl_id=p_asl_id and status='NEW';
88
89 if(l_count=0) then
90 return;
91 end if;
92
93 select to_char(POS_ASL_UPD_ITEMKEY_S.NEXTVAL)
94 into l_seq from sys.dual;
95
96 l_itemkey:=to_char(p_asl_id)||'-'||l_seq;
97
98 wf_engine.createProcess ( ItemType => l_ItemType,
99 ItemKey => l_ItemKey,
100 Process => 'ORDER_MODIFIERS');
101
102
103 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
104 itemkey => l_itemkey,
105 aname => 'ASL_ID',
106 avalue => p_asl_id);
107 wf_engine.StartProcess ( ItemType => l_ItemType,
108 ItemKey => l_ItemKey );
109
110 end StartWorkflow;
111
112 PROCEDURE getInfo(p_asl_id in NUMBER,
113 p_item_num out NOCOPY varchar2,
114 p_supplier_item_num out NOCOPY varchar2,
115 p_approval_flag out NOCOPY varchar2,
116 p_buyer_name out NOCOPY varchar2,
117 p_planner_name out NOCOPY varchar2) is
118
119 l_buyer_id number;
120 l_planner_id number;
121 l_buyer_username varchar2(80):=null;
122 l_planner_username varchar2(80):=null;
123 l_approval_required_by varchar2(20);
124
125 begin
126 select BUYER_ID,
127 PLANNER_ID,
128 SUPPLIER_ITEM_NUMBER,
129 ITEM_NUMBER
130 into l_buyer_id,
131 l_planner_id,
132 p_supplier_item_num,
133 p_item_num
134 from POS_ORD_MODIFIERS_V
135 where asl_id=p_asl_id;
136 FND_PROFILE.get('POS_ASL_MOD_APPR_REQD_BY', l_approval_required_by);
137 if(upper(l_approval_required_by)='BUYER') then
138 p_approval_flag:='BUYER';
139 elsif(upper(l_approval_required_by)='PLANNER') then
140 p_approval_flag:='PLANNER';
141 else
142 p_approval_flag:='NONE';
143 end if;
144 if(l_buyer_id is not null) then
145 wf_directory.GetUserName('PER', l_buyer_id, l_buyer_username, p_buyer_name);
146 end if;
147 if(l_planner_id is not null) then
148 wf_directory.GetUserName('PER', l_planner_id, l_planner_username, p_planner_name);
149 end if;
150
151 end getInfo;
152
153
154
155 procedure INIT_ATTRIBUTES( itemtype in varchar2,
156 itemkey in varchar2,
157 actid in number,
158 funcmode in varchar2,
159 resultout out NOCOPY varchar2 ) is
160
161 l_supplier_item_number varchar2(25);
162 l_item_number varchar2(25);
163 l_item_description varchar2(240);
164 l_uom varchar2(25);
165 l_vendor_id number;
166 l_buyer_id number;
167 l_planner_id number;
168 l_asl_id number;
169 l_processing_lead_time varchar2(40);
170 l_min_order_qty varchar2(40);
171 l_fixed_lot_multiple varchar2(40);
172 l_buyer_username varchar2(180):=null;
173 l_planner_username varchar2(180):=null;
174 l_supplier_username varchar2(240):=null;
175 l_buyer_displayname varchar2(180):=null;
176 l_planner_displayname varchar2(180):=null;
177 l_supplier_displayname varchar2(180):=null;
178 l_approval_required_by varchar2(20);
179 begin
180 if (funcmode <> wf_engine.eng_run) then
181 resultout := wf_engine.eng_null;
182 return;
183 end if;
184
185 l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
186 itemkey => itemkey,
187 aname => 'ASL_ID');
188 select DESCRIPTION,
189 BUYER_ID,
190 PLANNER_ID,
191 UOM,
192 SUPPLIER_ITEM_NUMBER,
193 ITEM_NUMBER,
194 VENDOR_ID,
195 to_char(PROCESSING_LEAD_TIME),
196 to_char(MIN_ORDER_QTY),
197 to_char(FIXED_LOT_MULTIPLE)
198 into l_item_description,
199 l_buyer_id,
200 l_planner_id,
201 l_uom,
202 l_supplier_item_number,
203 l_item_number,
204 l_vendor_id,
205 l_processing_lead_time,
206 l_min_order_qty,
207 l_fixed_lot_multiple
208 from POS_ORD_MODIFIERS_V
209 where asl_id=l_asl_id;
210
211 wf_engine.SetItemAttrText ( itemtype => itemtype,
212 itemkey => itemkey,
213 aname => 'ITEM_DESCRIPTION',
214 avalue => l_item_description);
215 wf_engine.SetItemAttrText ( itemtype => itemtype,
216 itemkey => itemkey,
217 aname => 'ITEM_NUM',
218 avalue => l_item_number);
219 wf_engine.SetItemAttrText ( itemtype => itemtype,
220 itemkey => itemkey,
221 aname => 'SUPPLIER_ITEM',
222 avalue => l_supplier_item_number);
223 wf_engine.SetItemAttrText ( itemtype => itemtype,
224 itemkey => itemkey,
225 aname => 'SUPPLIER_ITEM_NVL',
226 avalue => nvl(l_supplier_item_number,
227 l_item_number));
228 wf_engine.SetItemAttrText ( itemtype => itemtype,
229 itemkey => itemkey,
230 aname => 'PURCHASING_UOM',
231 avalue => l_uom);
232 wf_engine.SetItemAttrText ( itemtype => itemtype,
233 itemkey => itemkey,
234 aname => 'OLD_PLT',
235 avalue => nvl(l_processing_lead_time, '-'));
236 wf_engine.SetItemAttrText ( itemtype => itemtype,
237 itemkey => itemkey,
238 aname => 'OLD_MOQ',
239 avalue => nvl(l_min_order_qty, '-'));
240 wf_engine.SetItemAttrText ( itemtype => itemtype,
241 itemkey => itemkey,
242 aname => 'OLD_FLM',
243 avalue => nvl(l_fixed_lot_multiple, '-'));
244
245 if(l_buyer_id is not null) then
246 wf_directory.GetUserName('PER', l_buyer_id, l_buyer_username, l_buyer_displayname);
247 end if;
248 if(l_planner_id is not null) then
249 wf_directory.GetUserName('PER', l_planner_id, l_planner_username, l_planner_displayname);
250 end if;
251
252 if(l_vendor_id is not null) then
253 select vendor_name
254 into l_supplier_username
255 from po_vendors
256 where vendor_id=l_vendor_id;
257 end if;
258
259 wf_engine.SetItemAttrText ( itemtype => itemtype,
260 itemkey => itemkey,
261 aname => 'SUPPLIER_ORG_NAME',
262 avalue => l_supplier_username);
263
264
265 select last_updated_by
266 into l_vendor_id
267 from POS_ORDER_MODIFIERS_TEMP
268 where order_mod_request_id=
269 (select min(order_mod_request_id)
270 from POS_ORDER_MODIFIERS_TEMP
271 where asl_id=l_asl_id and status='NEW');
272
273 if(l_vendor_id is not null) then
274 wf_directory.GetUserName('FND_USR', l_vendor_id, l_supplier_username, l_supplier_displayname);
275 end if;
276 wf_engine.SetItemAttrText ( itemtype => itemtype,
277 itemkey => itemkey,
278 aname => 'BUYER_NAME',
279 avalue => l_buyer_username);
280 wf_engine.SetItemAttrText ( itemtype => itemtype,
281 itemkey => itemkey,
282 aname => 'BUYER_DISPLAY_NAME',
283 avalue => l_buyer_displayname);
284 wf_engine.SetItemAttrText ( itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'PLANNER_NAME',
287 avalue => l_planner_username);
288 wf_engine.SetItemAttrText ( itemtype => itemtype,
289 itemkey => itemkey,
290 aname => 'PLANNER_DISPLAY_NAME',
291 avalue => l_planner_displayname);
292 wf_engine.SetItemAttrText ( itemtype => itemtype,
293 itemkey => itemkey,
294 aname => 'SUPPLIER_NAME',
295 avalue => l_supplier_username);
296 wf_engine.SetItemAttrText ( itemtype => itemtype,
297 itemkey => itemkey,
298 aname => 'SUPPLIER_DISPLAY_NAME',
299 avalue => l_supplier_displayname);
300
301 select to_char(PROCESSING_LEAD_TIME),
302 to_char(MIN_ORDER_QUANTITY),
303 to_char(FIXED_LOT_MULTIPLE)
304 into l_processing_lead_time,
305 l_min_order_qty,
306 l_fixed_lot_multiple
307 from POS_ORDER_MODIFIERS_TEMP
308 where asl_id=l_asl_id and status='NEW';
309
310 wf_engine.SetItemAttrText ( itemtype => itemtype,
311 itemkey => itemkey,
312 aname => 'NEW_PLT',
313 avalue => nvl(l_processing_lead_time, '-'));
314 wf_engine.SetItemAttrText ( itemtype => itemtype,
315 itemkey => itemkey,
316 aname => 'NEW_MOQ',
317 avalue => nvl(l_min_order_qty, '-'));
318 wf_engine.SetItemAttrText ( itemtype => itemtype,
319 itemkey => itemkey,
320 aname => 'NEW_FLM',
321 avalue => nvl(l_fixed_lot_multiple, '-'));
322
323 FND_PROFILE.get('POS_ASL_MOD_APPR_REQD_BY', l_approval_required_by);
324 if(upper(l_approval_required_by)='BUYER') then
325 wf_engine.SetItemAttrText ( itemtype => itemtype,
326 itemkey => itemkey,
327 aname => 'APPROVAL_REQUIRED_BY',
328 avalue => 'BUYER');
329 elsif(upper(l_approval_required_by)='PLANNER') then
330 wf_engine.SetItemAttrText ( itemtype => itemtype,
331 itemkey => itemkey,
332 aname => 'APPROVAL_REQUIRED_BY',
333 avalue => 'PLANNER');
334 else
335 wf_engine.SetItemAttrText ( itemtype => itemtype,
336 itemkey => itemkey,
337 aname => 'APPROVAL_REQUIRED_BY',
338 avalue => 'NONE');
339 end if;
340
341 wf_engine.SetItemAttrText (itemtype => itemtype,
342 itemkey => itemkey,
343 aname => 'POS_NOTIFY_APPROVER',
344 avalue => 'PLSQL:POS_ORDER_MODIFIERS_PKG.GENERATE_APPR_NOTIF/'|| itemtype || ':' || itemkey);
345
346 wf_engine.SetItemAttrText (itemtype => itemtype,
347 itemkey => itemkey,
348 aname => 'POS_SUPP_NOTIF_APPR',
349 avalue => 'PLSQL:POS_ORDER_MODIFIERS_PKG.GENERATE_SUPPL_NOTIF_APPR/'|| itemtype || ':' || itemkey);
350
351 wf_engine.SetItemAttrText (itemtype => itemtype,
352 itemkey => itemkey,
353 aname => 'POS_SUPP_NOTIF_REJ',
354 avalue => 'PLSQL:POS_ORDER_MODIFIERS_PKG.GENERATE_SUPPL_NOTIF_REJ/'|| itemtype || ':' || itemkey);
355
356 EXCEPTION
357
358
359 WHEN OTHERS THEN
360 wf_core.context('POS_ORDER_MODIFIERS_PKG','INIT_ATTRIBUTES','0');
361 raise;
362
363 end;
364
365
366 PROCEDURE GENERATE_APPR_NOTIF(document_id in varchar2,
367 display_type in varchar2,
368 document in OUT NOCOPY varchar2,
369 document_type in OUT NOCOPY varchar2)
370 IS
371
372 NL VARCHAR2(1) := fnd_global.newline;
373 l_document VARCHAR2(32000) := '';
374
375 x_old_plt varchar2(40);
376 x_new_plt varchar2(40);
377 x_old_flm varchar2(40);
378 x_new_flm varchar2(40);
379 x_old_moq varchar2(40);
380 x_new_moq varchar2(40);
381
382 l_item_type varchar2(300) := '';
383 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
384
385 BEGIN
386
387 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
388 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
389
390
391 generate_ord_mod_header(l_document,l_item_type,l_item_key);
392
393
394 x_new_plt := wf_engine.GetItemAttrText ( itemtype => l_item_type,
395 itemkey => l_item_key,
396 aname => 'NEW_PLT');
397
398 x_new_moq := wf_engine.GetItemAttrText ( itemtype => l_item_type,
399 itemkey => l_item_key,
400 aname => 'NEW_MOQ');
401
402 x_new_flm := wf_engine.GetItemAttrText ( itemtype => l_item_type,
403 itemkey => l_item_key,
407 x_old_plt := wf_engine.GetItemAttrText ( itemtype => l_item_type,
404 aname => 'NEW_FLM');
405
406
408 itemkey => l_item_key,
409 aname => 'OLD_PLT');
410
411 x_old_moq := wf_engine.GetItemAttrText ( itemtype => l_item_type,
412 itemkey => l_item_key,
413 aname => 'OLD_MOQ');
414
415 x_old_flm := wf_engine.GetItemAttrText ( itemtype => l_item_type,
416 itemkey => l_item_key,
417 aname => 'OLD_FLM');
418
419
420
421 l_document := l_document || '<font size=2 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_ORDER_MODIFIERS') || '</B></font><HR>' || NL;
422
423 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
424 l_document := l_document || '<tr><TH><font color=#336699>' || ' ' || ' </TH>' || NL ;
425 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_CURRENT_VALUES') || '</TH>' || NL;
426
427 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_REQUESTED_UPDATES') || '</TH> </tr> ' || NL;
428
429
430 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_PROCESSING_LEAD_TIME') || '</B></font></td><td>' || x_old_plt || '</td><td>' ||x_new_plt || '</td> </tr>' || NL;
431
432 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_MIN_ORD_QUANTITY') || '</B></font></td><td>' || x_old_moq || '</td><td>' ||x_new_moq || '</td> </tr>' || NL;
433
434 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_FIXED_LOT_MULTIPLE') || '</B></font></td><td>' || x_old_flm || '</td><td>' ||x_new_flm || '</td> </tr>' || NL;
435
436 l_document := l_document || '</table> </td> </tr> </table>' || NL;
437
438 document := l_document;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 NULL;
443 END;
444
445 procedure generate_ord_mod_header(document in out nocopy varchar2,
446 itemtype in varchar2,
447 itemkey in varchar2)
448 is
449
450 NL VARCHAR2(1) := fnd_global.newline;
451 l_document VARCHAR2(32000) := '';
452
453 x_supp_item varchar2(25);
454 x_item_num varchar2(25);
455 x_item_desc varchar2(240);
456 x_uom varchar2(25);
457
458 begin
459
460 x_item_desc := wf_engine.GetItemAttrText ( itemtype => itemtype,
461 itemkey => itemkey,
462 aname => 'ITEM_DESCRIPTION');
463
464 x_item_num := wf_engine.GetItemAttrText ( itemtype => itemtype,
465 itemkey => itemkey,
466 aname => 'ITEM_NUM');
467
468 x_supp_item := wf_engine.GetItemAttrText ( itemtype => itemtype,
469 itemkey => itemkey,
470 aname => 'SUPPLIER_ITEM');
471
472 x_uom := wf_engine.GetItemAttrText ( itemtype => itemtype,
473 itemkey => itemkey,
474 aname => 'PURCHASING_UOM');
475
476
477 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_ASN_NOTIF_DETAILS') || '</B></font><HR>' || NL;
478
479 l_document := l_document || '<table width=100%><tr><td width=2>' || ' ' || '</td><td>' || NL;
480
481 l_document := l_document || '<table cellpadding=2 cellspacing=1> ' || NL;
482
483 l_document := l_document || '<tr ><td nowrap align=right><font color=black><B>' || fnd_message.get_string('POS','POS_SUPPLIER_ITEM_NUM') || '</B></font></td><td width=2> '|| ' ' || ' </td><td>' || x_supp_item || '</td> </tr>' || NL;
484
485 l_document := l_document || '<tr ><td nowrap align=right><font color=black><B>' || fnd_message.get_string('POS','POS_ASN_NOTIF_ITEM') || '</B></font></td><td width=2> ' || ' ' || '</td><td>' || x_item_num || '</td> </tr>' || NL;
486
487 l_document := l_document || '<tr ><td nowrap align=right><font color=black><B>' || fnd_message.get_string('POS','POS_ASN_NOTIF_ITEM_DESC') || '</B></font></td><td width=2>' || ' ' || ' </td><td>' || x_item_desc || '</td> </tr>' || NL;
488
489 l_document := l_document || '<tr ><td nowrap align=right><font color=black><B>' || fnd_message.get_string('POS','POS_ASN_NOTIF_UOM') || '</B></font></td><td width=2>' || ' ' || '</td><td>' || x_uom || '</td> </tr>' || NL;
490
491
492 l_document := l_document || '</table>' || NL;
493
494 document := l_document;
495
496 exception
497 when others then
498 null;
499 end;
500
501
502
503 PROCEDURE GENERATE_SUPPL_NOTIF_APPR(document_id in varchar2,
504 display_type in varchar2,
505 document in OUT NOCOPY varchar2,
506 document_type in OUT NOCOPY varchar2)
507 IS
508
509 NL VARCHAR2(1) := fnd_global.newline;
510 l_document VARCHAR2(32000) := '';
511
512 x_old_plt varchar2(40);
513 x_new_plt varchar2(40);
514 x_old_flm varchar2(40);
515 x_new_flm varchar2(40);
516 x_old_moq varchar2(40);
520 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
517 x_new_moq varchar2(40);
518
519 l_item_type varchar2(300) := '';
521
522 BEGIN
523
524 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
525 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
526
527
528 generate_ord_mod_header(l_document,l_item_type,l_item_key);
529
530
531 x_new_plt := wf_engine.GetItemAttrText ( itemtype => l_item_type,
532 itemkey => l_item_key,
533 aname => 'NEW_PLT');
534
535 x_new_moq := wf_engine.GetItemAttrText ( itemtype => l_item_type,
536 itemkey => l_item_key,
537 aname => 'NEW_MOQ');
538
539 x_new_flm := wf_engine.GetItemAttrText ( itemtype => l_item_type,
540 itemkey => l_item_key,
541 aname => 'NEW_FLM');
542
543 l_document := l_document || '<font size=2 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_ORDER_MODIFIERS') || '</B></font><HR>' || fnd_message.get_string('POS','POS_ORD_MOD_APPROVED') || NL;
544
545 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
546 l_document := l_document || '<tr><TH><font color=#336699>' || ' ' || ' </TH>' || NL ;
547 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH> </tr>' || NL;
548
549
550 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_PROCESSING_LEAD_TIME') || '</B></font></td><td>' || x_new_plt || '</td> </tr>' || NL;
551
552 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_MIN_ORD_QUANTITY') || '</B></font></td><td>' || x_new_moq || '</td></tr>' || NL;
553
554 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_FIXED_LOT_MULTIPLE') || '</B></font></td><td>' || x_new_flm || '</td> </tr>' || NL;
555
556 l_document := l_document || '</table> </td> </tr> </table>' || NL;
557
558 document := l_document;
559
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 NULL;
564 END;
565
566
567 PROCEDURE GENERATE_SUPPL_NOTIF_REJ(document_id in varchar2,
568 display_type in varchar2,
569 document in OUT NOCOPY varchar2,
570 document_type in OUT NOCOPY varchar2)
571 IS
572
573 NL VARCHAR2(1) := fnd_global.newline;
574 l_document VARCHAR2(32000) := '';
575
576 x_old_plt varchar2(40);
577 x_new_plt varchar2(40);
578 x_old_flm varchar2(40);
579 x_new_flm varchar2(40);
580 x_old_moq varchar2(40);
581 x_new_moq varchar2(40);
582
583 l_item_type varchar2(300) := '';
584 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
585
586 BEGIN
587
588
589 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
590 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
591
592
593 generate_ord_mod_header(l_document,l_item_type,l_item_key);
594
595 x_new_plt := wf_engine.GetItemAttrText ( itemtype => l_item_type,
596 itemkey => l_item_key,
597 aname => 'NEW_PLT');
598
599 x_new_moq := wf_engine.GetItemAttrText ( itemtype => l_item_type,
600 itemkey => l_item_key,
601 aname => 'NEW_MOQ');
602
603 x_new_flm := wf_engine.GetItemAttrText ( itemtype => l_item_type,
604 itemkey => l_item_key,
605 aname => 'NEW_FLM');
606
607 l_document := l_document || '<font size=2 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_ORDER_MODIFIERS') || '</B></font><HR>' || fnd_message.get_string('POS','POS_ORD_MOD_REJECTED') || NL;
608
609 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
610 l_document := l_document || '<tr><TH><font color=#336699>' || ' ' || ' </TH>' || NL ;
611 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH> </tr>' || NL;
612
613
614 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_PROCESSING_LEAD_TIME') || '</B></font></td><td>' || x_new_plt || '</td> </tr>' || NL;
615
616 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_MIN_ORD_QUANTITY') || '</B></font></td><td>' || x_new_moq || '</td></tr>' || NL;
617
618 l_document := l_document || '<tr><td><font color=black><B>' || fnd_message.get_string('POS','POS_FIXED_LOT_MULTIPLE') || '</B></font></td><td>' || x_new_flm || '</td> </tr>' || NL;
619
623
620 l_document := l_document || '</table> </td> </tr> </table>' || NL;
621
622 document := l_document;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 NULL;
628 END;
629
630 procedure GET_BUYER_NAME( itemtype in varchar2,
631 itemkey in varchar2,
632 actid in number,
633 funcmode in varchar2,
634 resultout out NOCOPY varchar2 ) is
635 begin
636 if (funcmode <> wf_engine.eng_run) then
637 resultout := wf_engine.eng_null;
638 return;
639 end if;
640 end;
641
642 procedure GET_PLANNER_NAME( itemtype in varchar2,
643 itemkey in varchar2,
644 actid in number,
645 funcmode in varchar2,
646 resultout out NOCOPY varchar2 ) is
647 begin
648 if (funcmode <> wf_engine.eng_run) then
649 resultout := wf_engine.eng_null;
650 return;
651 end if;
652 end;
653
654 procedure BUYER_APPROVAL_REQUIRED( itemtype in varchar2,
655 itemkey in varchar2,
656 actid in number,
657 funcmode in varchar2,
658 resultout out NOCOPY varchar2 ) is
659
660 l_approval_required_by varchar2(20);
661 l_buyer_name varchar2(180);
662 begin
663 if (funcmode <> wf_engine.eng_run) then
664 resultout := wf_engine.eng_null;
665 return;
666 end if;
667 l_approval_required_by:=wf_engine.GetItemAttrText ( itemtype => itemtype,
668 itemkey => itemkey,
669 aname => 'APPROVAL_REQUIRED_BY');
670 if(l_approval_required_by='BUYER') then
671 l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
672 itemkey => itemkey,
673 aname => 'BUYER_NAME');
674 wf_engine.SetItemAttrText ( itemtype => itemtype,
675 itemkey => itemkey,
676 aname => 'RESPONSE_FROM_ROLE',
677 avalue => l_buyer_name);
678
679
680 resultout := wf_engine.eng_completed || ':' || 'Y';
681 else
682 resultout := wf_engine.eng_completed || ':' || 'N';
683 end if;
684
685 end;
686
687 procedure BUYER_EXIST( itemtype in varchar2,
688 itemkey in varchar2,
689 actid in number,
690 funcmode in varchar2,
691 resultout out NOCOPY varchar2 ) is
692
693 l_buyer_name varchar2(100);
694 begin
695 if (funcmode <> wf_engine.eng_run) then
696 resultout := wf_engine.eng_null;
697 return;
698 end if;
699 l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
700 itemkey => itemkey,
701 aname => 'BUYER_NAME');
702 if(l_buyer_name is not null) then
703 resultout := wf_engine.eng_completed || ':' || 'Y';
704 else
705 resultout := wf_engine.eng_completed || ':' || 'N';
706 end if;
707 end;
708
709 procedure PLANNER_APPROVAL_REQUIRED( itemtype in varchar2,
710 itemkey in varchar2,
711 actid in number,
712 funcmode in varchar2,
713 resultout out NOCOPY varchar2 ) is
714
715 l_approval_required_by varchar2(20);
716 l_planner_name varchar2(180);
717 l_supplier_name varchar2(180);
718 begin
719 if (funcmode <> wf_engine.eng_run) then
720 resultout := wf_engine.eng_null;
721 return;
722 end if;
723 l_approval_required_by:=wf_engine.GetItemAttrText ( itemtype => itemtype,
724 itemkey => itemkey,
725 aname => 'APPROVAL_REQUIRED_BY');
726 if(l_approval_required_by='PLANNER') then
727 l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
728 itemkey => itemkey,
729 aname => 'PLANNER_NAME');
730 wf_engine.SetItemAttrText ( itemtype => itemtype,
731 itemkey => itemkey,
732 aname => 'RESPONSE_FROM_ROLE',
733 avalue => l_planner_name);
734 resultout := wf_engine.eng_completed || ':' || 'Y';
735 else
736 l_supplier_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
737 itemkey => itemkey,
738 aname => 'SUPPLIER_NAME');
739 wf_engine.SetItemAttrText ( itemtype => itemtype,
740 itemkey => itemkey,
741 aname => 'RESPONSE_FROM_ROLE',
742 avalue => l_supplier_name);
743 resultout := wf_engine.eng_completed || ':' || 'N';
744 end if;
745
746 end;
747
748 procedure PLANNER_EXIST( itemtype in varchar2,
749 itemkey in varchar2,
750 actid in number,
751 funcmode in varchar2,
752 resultout out NOCOPY varchar2 ) is
753
754 l_planner_name varchar2(100);
755 begin
756 if (funcmode <> wf_engine.eng_run) then
757 resultout := wf_engine.eng_null;
758 return;
759 end if;
760 l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
761 itemkey => itemkey,
762 aname => 'PLANNER_NAME');
763 if(l_planner_name is not null) then
764 resultout := wf_engine.eng_completed || ':' || 'Y';
765 else
766 resultout := wf_engine.eng_completed || ':' || 'N';
770 procedure UPDATE_ASL( itemtype in varchar2,
767 end if;
768 end;
769
771 itemkey in varchar2,
772 actid in number,
773 funcmode in varchar2,
774 resultout out NOCOPY varchar2 ) is
775
776 l_asl_id number;
777 l_proc_lead_time number;
778 l_min_order_qty number;
779 l_fixed_lot_multiple number;
780 begin
781 if (funcmode <> wf_engine.eng_run) then
782 resultout := wf_engine.eng_null;
783 return;
784 end if;
785
786 l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
787 itemkey => itemkey,
788 aname => 'ASL_ID');
789
790 select processing_lead_time, min_order_quantity, fixed_lot_multiple
791 into l_proc_lead_time, l_min_order_qty, l_fixed_lot_multiple
792 from POS_ORDER_MODIFIERS_TEMP
793 where asl_id=l_asl_id
794 and status='NEW';
795
796 UPDATE PO_ASL_ATTRIBUTES
797 SET PROCESSING_LEAD_TIME = l_proc_lead_time,
798 MIN_ORDER_QTY = l_min_order_qty,
799 FIXED_LOT_MULTIPLE = l_fixed_lot_multiple
800 WHERE asl_id = l_asl_id
801 and using_organization_id = -1;
802
803 update POS_ORDER_MODIFIERS_TEMP
804 set status='ACE'
805 where asl_id=l_asl_id
806 and status='NEW';
807
808 end;
809
810 procedure DEFAULT_APPROVAL_MODE( itemtype in varchar2,
811 itemkey in varchar2,
812 actid in number,
813 funcmode in varchar2,
814 resultout out NOCOPY varchar2 ) is
815
816 l_default_mode varchar2(20);
817 begin
818 if (funcmode <> wf_engine.eng_run) then
819 resultout := wf_engine.eng_null;
820 return;
821 end if;
822 l_default_mode:=wf_engine.GetItemAttrText ( itemtype => itemtype,
823 itemkey => itemkey,
824 aname => 'DEFAULT_MODE');
825 if(upper(l_default_mode)='APPROVE') then
826 resultout := wf_engine.eng_completed || ':' || 'APPROVED';
827 else
828 resultout := wf_engine.eng_completed || ':' || 'REJECTED';
829 end if;
830
831 end;
832
833 procedure UPDATE_STATUS( itemtype in varchar2,
834 itemkey in varchar2,
835 actid in number,
836 funcmode in varchar2,
837 resultout out NOCOPY varchar2 ) is
838
839 l_asl_id number;
840 begin
841 if (funcmode <> wf_engine.eng_run) then
842 resultout := wf_engine.eng_null;
843 return;
844 end if;
845
846 l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
847 itemkey => itemkey,
848 aname => 'ASL_ID');
849
850 update POS_ORDER_MODIFIERS_TEMP
851 set status='REJ'
852 where asl_id=l_asl_id
853 and status='NEW';
854
855 end;
856
857 procedure BUYER_SAME_AS_PLANNER( itemtype in varchar2,
858 itemkey in varchar2,
859 actid in number,
860 funcmode in varchar2,
861 resultout out NOCOPY varchar2 ) is
862
863 l_buyer_name varchar2(100);
864 l_planner_name varchar2(100);
865 begin
866 if (funcmode <> wf_engine.eng_run) then
867 resultout := wf_engine.eng_null;
868 return;
869 end if;
870 l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
871 itemkey => itemkey,
872 aname => 'BUYER_NAME');
873 l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
874 itemkey => itemkey,
875 aname => 'PLANNER_NAME');
876
877 if(l_planner_name is not null) then
878 if(l_planner_name=l_buyer_name) then
879 resultout := wf_engine.eng_completed || ':' || 'Y';
880 else
881 resultout := wf_engine.eng_completed || ':' || 'N';
882 end if;
883 else
884 resultout := wf_engine.eng_completed || ':' || 'Y';
885 end if;
886 end;
887
888
889 END POS_ORDER_MODIFIERS_PKG;
890