[Home] [Help]
PACKAGE BODY: APPS.POS_UPDATE_CAPACITY_PKG
Source
1 PACKAGE BODY POS_UPDATE_CAPACITY_PKG AS
2 /* $Header: POSUPDNB.pls 120.2.12020000.3 2013/02/09 14:09:46 hvutukur ship $ */
3
4
5 L_TABLE_STYLE VARCHAR2(100) := ' style="border-collapse:collapse" cellpadding="1" cellspacing="0" border="0" width="100%" ';
6
7 L_TABLE_HEADER_STYLE VARCHAR2(100) := ' class="tableheader" style="border-left:1px solid #f7f7e7" ';
8
9 L_TABLE_LABEL_STYLE VARCHAR2(100) := ' class="tableheaderright" nowrap align=right style="border:1px solid #f7f7e7" ';
10
11 L_TABLE_CELL_STYLE VARCHAR2(100) := ' class="tabledata" nowrap align=left style="border:1px solid #cccc99" ';
12
13 L_TABLE_CELL_WRAP_STYLE VARCHAR2(100) := ' class="tabledata" align=left style="border:1px solid #cccc99" ';
14
15 L_TABLE_CELL_RIGHT_STYLE VARCHAR2(100) := ' class="tabledata" nowrap align=right style="border:1px solid #cccc99" ';
16
17 /*===========================================================================
18 PROCEDURE NAME: updmodifiers()
19 ===========================================================================*/
20
21 PROCEDURE INSERT_TEMP_MFG_CAPACITY(
22 p_asl_id IN NUMBER,
23 p_from_date IN DATE,
24 p_to_date IN DATE,
25 p_capacity_per_day IN NUMBER,
26 p_created_by in number,
27 p_capacity_id in number,
28 p_status in varchar2,/*
29 p_supplier_item_number in varchar2,
30 p_item_number in varchar2,
31 p_item_description in varchar2,
32 p_uom in varchar2,
33 p_vendor_id in number,
34 p_vendor_name in varchar2,*/
35 p_error_code OUT NOCOPY VARCHAR2,
36 p_error_message OUT NOCOPY VARCHAR2) is
37
38
39 l_seq number;
40 BEGIN
41
42 /* Update PO_ASL_ATTRIBUTES form ISP */
43 select POS_MFG_CAPACITY_TEMP_ID_S.NEXTVAL
44 into l_seq from sys.dual;
45
46 insert into POS_MFG_CAPACITY_TEMP (
47 mfg_capacity_id,
48 asl_id,
49 last_update_date,
50 last_updated_by,
51 last_update_login,
52 creation_date,
53 created_by,
54 from_date,
55 to_date,
56 capacity_per_day,/*
57 supplier_item_number,
58 item_number,
59 item_description,
60 uom,
61 vendor_id,
62 vendor_name,*/
63 CAPACITY_ID,
64 status)
65 values
66 (
67 l_seq,
68 p_asl_id,
69 sysdate,
70 p_created_by,
71 p_created_by,
72 sysdate,
73 p_created_by,
74 p_from_date,
75 p_to_date,
76 p_capacity_per_day,/*
77 p_supplier_item_number,
78 p_item_number,
79 p_item_description,
80 p_uom,
81 p_vendor_id,
82 p_vendor_name,*/
83 p_capacity_id,
84 p_status);
85
86
87
88 EXCEPTION
89
90 WHEN OTHERS THEN
91
92 p_ERROR_CODE := 'Y';
93 p_ERROR_MESSAGE := 'exception raised during Update';
94
95 END INSERT_TEMP_MFG_CAPACITY;
96
97
98 PROCEDURE INSERT_TEMP_CAPACITY_TOLERANCE(
99 p_asl_id IN NUMBER,
100 p_days_in_advance IN NUMBER,
101 p_tolerance IN NUMBER,
102 p_created_by in number,
103 /*
104 p_supplier_item_number in varchar2,
105 p_item_number in varchar2,
106 p_item_description in varchar2,
107 p_uom in varchar2,
108 p_vendor_id in number,
109 p_vendor_name in varchar2,
110 */
111 p_error_code OUT NOCOPY VARCHAR2,
112 p_error_message OUT NOCOPY VARCHAR2) is
113
114
115 l_seq number;
116 BEGIN
117
118 /* Update PO_ASL_ATTRIBUTES form ISP */
119 select POS_MFG_CAPACITY_TEMP_ID_S.NEXTVAL
120 into l_seq from sys.dual;
121
122 insert into POS_CAPACITY_TOLERANCE_TEMP(
123 capacity_tolerance_id,
124 asl_id,
125 last_update_date,
126 last_updated_by,
127 last_update_login,
128 creation_date,
129 created_by,
130 days_in_advance,
134 item_number,
131 tolerance,
132 /*
133 supplier_item_number,
135 item_description,
136 uom,
137 vendor_id,
138 vendor_name,
139 */
140 status)
141 values
142 (
143 l_seq,
144 p_asl_id,
145 sysdate,
146 p_created_by,
147 p_created_by,
148 sysdate,
149 p_created_by,
150 p_days_in_advance,
151 p_tolerance,
152 /*
153 p_supplier_item_number,
154 p_item_number,
155 p_item_description,
156 p_uom,
157 p_vendor_id,
158 p_vendor_name,
159 */
160 'NEW');
161
162
163
164 EXCEPTION
165
166 WHEN OTHERS THEN
167
168 p_ERROR_CODE := 'Y';
169 p_ERROR_MESSAGE := 'exception raised during Update';
170
171 END INSERT_TEMP_CAPACITY_TOLERANCE;
172
173
174
175 PROCEDURE UPDATE_EXIST(p_asl_id in NUMBER,
176 p_return_code out NOCOPY number) is
177
178 begin
179
180 select count(*)
181 into p_return_code
182 from POS_MFG_CAPACITY_TEMP
183 where asl_id=p_asl_id and status in ('NEW', 'OLD', 'DEL', 'MOD');
184
185 if(p_return_code>0) then return;
186 else
187 select count(*)
188 into p_return_code
189 from POS_CAPACITY_TOLERANCE_TEMP
190 where asl_id=p_asl_id and status='NEW';
191 end if;
192
193 end UPDATE_EXIST;
194
195 PROCEDURE StartWorkflow(p_asl_id in NUMBER) is
196
197 l_seq varchar2(25);
198 l_itemkey varchar2(40);
199 l_itemtype varchar2(20):='POSUPDNT';
200 l_count number;
201
202 BEGIN
203
204 /* Update PO_ASL_ATTRIBUTES form ISP */
205
206 UPDATE_EXIST(p_asl_id, l_count);
207
208 if(l_count=0) then
209 return;
210 end if;
211
212 select to_char(POS_ASL_UPD_ITEMKEY_S.NEXTVAL)
213 into l_seq from sys.dual;
214
215 l_itemkey:=to_char(p_asl_id)||'-'||l_seq;
216
217 wf_engine.createProcess ( ItemType => l_ItemType,
218 ItemKey => l_ItemKey,
219 Process => 'UPDATE_CAPACITY');
220
221
222 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
223 itemkey => l_itemkey,
224 aname => 'ASL_ID',
225 avalue => p_asl_id);
226 wf_engine.StartProcess ( ItemType => l_ItemType,
227 ItemKey => l_ItemKey );
228
229 end StartWorkflow;
230
231 procedure OLD_MFG_CAPACITY_TABLE( itemtype in varchar2,
232 itemkey in varchar2,
233 asl_id in number) is
234
235
236 l_document VARCHAR2(32000) := '';
237 NL VARCHAR2(1) := fnd_global.newline;
238 l_from DATE;
239 l_to DATE;
240 l_cap_per_day NUMBER;
241 l_from_date_text VARCHAR2(150) := '';
242 l_to_date_text VARCHAR2(150) := '';
243 CURSOR old_mfg_capacity(id number) is
244 SELECT from_date, to_date, capacity_per_day
245 FROM pos_supplier_item_capacity_v
246 WHERE asl_id=id
247 order by from_date asc;
248
249 begin
250 l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>';
251
252 l_document := l_document || '<TR>';
253
254 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
255 fnd_message.get_string('POS', 'POS_FROM') ||
256 '</TH> ' || NL;
257 l_document := l_document || '<TH align=left ' || L_TABLE_HEADER_STYLE || '>' ||
258 fnd_message.get_string('POS', 'POS_TO') ||
259 '</TH> ' || NL;
260 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
261 fnd_message.get_string('POS', 'POS_CAPACITY_PER_DAY') ||
262 '</TH> ' || NL;
263
264 l_document := l_document || '</TR>';
265
266 open old_mfg_capacity(asl_id);
267
268 LOOP
269 FETCH old_mfg_capacity INTO l_from, l_to, l_cap_per_day;
270 EXIT WHEN old_mfg_capacity%NOTFOUND;
271 /*
272 * Modified as part of bug 7524573 changing date format
273 * if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
274 * or (FND_RELEASE.MAJOR_VERSION > 12) then
275 */
276 /*
277 * Commented above if condition and added below if condition as part of Bug #: 11824514
278 */
279 IF ( fnd_release.major_version = 12
280 AND fnd_release.minor_version = 1
281 AND fnd_release.point_version >= 1 )
282 OR ( fnd_release.major_version = 12
283 AND fnd_release.minor_version >= 2 )
284 OR ( fnd_release.major_version > 12 ) THEN
285 l_from_date_text := to_char(l_from,
286 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
287 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id), 'GREGORIAN') || '''');
288 l_to_date_text := to_char(l_to,
289 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
290 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id), 'GREGORIAN') || '''');
291 else
292 l_from_date_text := to_char(l_from);
296 l_document := l_document || '<TD '|| L_TABLE_CELL_STYLE ||'>' || l_from_date_text ||'</TD> ' || NL;
293 l_to_date_text := to_char(l_to);
294 end if;
295
297 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE ||'>' || l_to_date_text ||'</TD> ' || NL;
298 /*Modified as part of bug 7524573 changing date format*/
299 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE ||'>' ||to_char(l_cap_per_day)||'</TD> ' || NL;
300 l_document := l_document || '</TR>' || NL;
301
302
303 end loop;
304
305 l_document := l_document || '</TABLE>';
306
307 wf_engine.SetItemAttrText ( itemtype => itemtype,
308 itemkey => itemkey,
309 aname => 'OLD_MFG_CAPACITY_TABLE',
310 avalue => l_document);
311
312 end OLD_MFG_CAPACITY_TABLE;
313
314 procedure NEW_MFG_CAPACITY_TABLE( itemtype in varchar2,
315 itemkey in varchar2,
316 asl_id in number) is
317
318
319 l_document VARCHAR2(32000) := '';
320 NL VARCHAR2(1) := fnd_global.newline;
321 l_from DATE;
322 l_to DATE;
323 l_cap_per_day NUMBER;
324 l_from_date_text VARCHAR2(150) := '';
325 l_to_date_text VARCHAR2(150) := '';
326
327 CURSOR new_mfg_capacity(id number) is
328 SELECT from_date, to_date, capacity_per_day
329 FROM pos_mfg_capacity_temp
330 WHERE asl_id=id and status in ('NEW', 'OLD', 'MOD')
331 order by from_date asc;
332
333 begin
334 l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>';
335
336 l_document := l_document || '<TR>';
337
338 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
339 fnd_message.get_string('POS', 'POS_FROM') ||
340 '</TH> ' || NL;
341 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
342 fnd_message.get_string('POS', 'POS_TO') ||
343 '</TH> ' || NL;
344 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
345 fnd_message.get_string('POS', 'POS_CAPACITY_PER_DAY') ||
346 '</TH> ' || NL;
347
348 l_document := l_document || '</TR>';
349
350 open new_mfg_capacity(asl_id);
351
352 LOOP
353 FETCH new_mfg_capacity INTO l_from, l_to, l_cap_per_day;
354 EXIT WHEN new_mfg_capacity%NOTFOUND;
355 l_document := l_document || '<TR>';
356 /*
357 * Modified as part of bug 7524573 changing date format
358 * if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
359 * or (FND_RELEASE.MAJOR_VERSION > 12) then
360 */
361 /*
362 * Commented above if condition and added below condition as part of Bug #: 11824514
363 */
364 IF ( fnd_release.major_version = 12
365 AND fnd_release.minor_version = 1
366 AND fnd_release.point_version >= 1 )
367 OR ( fnd_release.major_version = 12
368 AND fnd_release.minor_version >= 2 )
369 OR ( fnd_release.major_version > 12 ) THEN
370 l_from_date_text := to_char(l_from,
371 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
372 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id), 'GREGORIAN') || '''');
373 l_to_date_text := to_char(l_to,
374 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
375 'NLS_CALENDAR = ''' || nvl(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id), 'GREGORIAN') || '''');
376 else
377 l_from_date_text := to_char(l_from);
378 l_to_date_text := to_char(l_to);
379 end if;
380
381 l_document := l_document || '<TD '|| L_TABLE_CELL_STYLE ||'>' || l_from_date_text ||'</TD> ' || NL;
382 l_document := l_document || '<TD' || L_TABLE_CELL_STYLE ||'>' || l_to_date_text ||'</TD> ' || NL;
383 /*Modified as part of bug 7524573 changing date format*/
384 l_document := l_document || '<TD' || L_TABLE_CELL_STYLE ||'>' ||to_char(l_cap_per_day)||'</TD> ' || NL;
385 l_document := l_document || '</TR>' || NL;
386 end loop;
387
388 l_document := l_document || '</TABLE>' || NL;
389
390 wf_engine.SetItemAttrText ( itemtype => itemtype,
391 itemkey => itemkey,
392 aname => 'NEW_MFG_CAPACITY_TABLE',
393 avalue => l_document);
394
395 end NEW_MFG_CAPACITY_TABLE;
396
397
398 procedure OLD_CAPACITY_TOLERANCE_TABLE( itemtype in varchar2,
399 itemkey in varchar2,
400 asl_id in number) is
401
402
403 l_document VARCHAR2(32000) := '';
404 NL VARCHAR2(1) := fnd_global.newline;
405 l_days_in_advance NUMBER;
406 l_tolerance NUMBEr;
407
408 CURSOR OLD_CAPACITY_TOLERANCE(id number) is
409 SELECT number_of_days, tolerance
410 FROM po_supplier_item_tolerance
411 WHERE asl_id=id
412 order by number_of_days asc;
413
414 begin
415
416 l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>' || NL;
417
418 l_document := l_document || '<TR>' || NL;
419
420 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
421 fnd_message.get_string('POS', 'POS_DAYS_IN_ADVANCE') ||
425 '</TH> ' || NL;
422 '</TH> ' || NL;
423 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
424 fnd_message.get_string('POS', 'POS_TOLERANCE') ||
426
427 l_document := l_document || '</TR>' || NL;
428
429
430 open OLD_CAPACITY_TOLERANCE(asl_id);
431
432 LOOP
433 FETCH OLD_CAPACITY_TOLERANCE INTO l_days_in_advance, l_tolerance;
434 EXIT WHEN OLD_CAPACITY_TOLERANCE%NOTFOUND;
435
436
437 l_document := l_document || '<TR>';
438
439 l_document := l_document || '<TD '|| L_TABLE_CELL_STYLE ||'>' || to_char(l_days_in_advance)||'</TD> ';
440 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE ||'>' ||to_char(l_tolerance)||'</TD> ';
441
442 l_document := l_document || '</TR>';
443 end loop;
444
445 l_document := l_document || '</TABLE>';
446
447 wf_engine.SetItemAttrText ( itemtype => itemtype,
448 itemkey => itemkey,
449 aname => 'OLD_CAPACITY_TOLERANCE_TABLE',
450 avalue => l_document);
451
452 end OLD_CAPACITY_TOLERANCE_TABLE;
453
454 procedure NEW_CAPACITY_TOLERANCE_TABLE( itemtype in varchar2,
455 itemkey in varchar2,
456 asl_id in number) is
457
458 l_document VARCHAR2(32000) := '';
459 NL VARCHAR2(1) := fnd_global.newline;
460 l_days_in_advance NUMBER;
461 l_tolerance NUMBEr;
462
463 CURSOR NEW_CAPACITY_TOLERANCE(id number) is
464 SELECT days_in_advance, tolerance
465 FROM POS_CAPACITY_TOLERANCE_TEMP
466 WHERE asl_id=id and status='NEW'
467 order by days_in_advance asc;
468
469 begin
470
471
472 l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>' || NL;
473
474 l_document := l_document || '<TR>' || NL;
475
476 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
477 fnd_message.get_string('POS', 'POS_DAYS_IN_ADVANCE') ||
478 '</TH> ' || NL;
479 l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
480 fnd_message.get_string('POS', 'POS_TOLERANCE') ||
481 '</TH> ' || NL;
482
483 l_document := l_document || '</TR>' || NL;
484
485
486 open NEW_CAPACITY_TOLERANCE(asl_id);
487
488 LOOP
489 FETCH NEW_CAPACITY_TOLERANCE INTO l_days_in_advance, l_tolerance;
490 EXIT WHEN NEW_CAPACITY_TOLERANCE%NOTFOUND;
491
492 l_document := l_document || '<TR>';
493
494 l_document := l_document || '<TD '|| L_TABLE_CELL_STYLE ||'>' || to_char(l_days_in_advance)||'</TD> ';
495 l_document := l_document || '<TD '|| L_TABLE_CELL_STYLE ||'>' ||to_char(l_tolerance)||'</TD> ';
496
497 l_document := l_document || '</TR>';
498
499 end loop;
500
501 l_document := l_document || '</TABLE>';
502
503 wf_engine.SetItemAttrText ( itemtype => itemtype,
504 itemkey => itemkey,
505 aname => 'NEW_CAPACITY_TOLERANCE_TABLE',
506 avalue => l_document);
507
508 end NEW_CAPACITY_TOLERANCE_TABLE;
509
510
511 procedure INIT_ATTRIBUTES( itemtype in varchar2,
512 itemkey in varchar2,
513 actid in number,
514 funcmode in varchar2,
515 resultout out NOCOPY varchar2 ) is
516
517 l_supplier_item_number varchar2(25);
518 l_item_number varchar2(25);
519 l_item_description varchar2(240);
520 l_uom varchar2(25);
521 l_vendor_id number;
522 l_buyer_id number;
523 l_planner_id number;
524 l_asl_id number;
525 l_buyer_username varchar2(80):=null;
526 l_planner_username varchar2(80):=null;
527 l_supplier_username varchar2(240):=null;
528 l_buyer_displayname varchar2(80):=null;
529 l_planner_displayname varchar2(80):=null;
530 l_supplier_displayname varchar2(80):=null;
531 l_approval_required_by varchar2(20);
532 l_progress varchar2(3):='0';
533
534 begin
535 if (funcmode <> wf_engine.eng_run) then
536 resultout := wf_engine.eng_null;
537 return;
538 end if;
539 l_progress:='1';
540 l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
541 itemkey => itemkey,
542 aname => 'ASL_ID');
543 select DESCRIPTION,
544 BUYER_ID,
545 PLANNER_ID,
546 UOM,
547 SUPPLIER_ITEM_NUMBER,
548 ITEM_NUMBER,
549 VENDOR_ID
550 into l_item_description,
551 l_buyer_id,
552 l_planner_id,
553 l_uom,
554 l_supplier_item_number,
555 l_item_number,
556 l_vendor_id
557 from POS_ORD_MODIFIERS_V
558 where asl_id=l_asl_id;
559
560 l_progress:='2';
561 wf_engine.SetItemAttrText ( itemtype => itemtype,
562 itemkey => itemkey,
563 aname => 'ITEM_DESCRIPTION',
564 avalue => l_item_description);
565 wf_engine.SetItemAttrText ( itemtype => itemtype,
566 itemkey => itemkey,
567 aname => 'ITEM_NUM',
568 avalue => l_item_number);
572 avalue => l_supplier_item_number);
569 wf_engine.SetItemAttrText ( itemtype => itemtype,
570 itemkey => itemkey,
571 aname => 'SUPPLIER_ITEM',
573 wf_engine.SetItemAttrText ( itemtype => itemtype,
574 itemkey => itemkey,
575 aname => 'SUPPLIER_ITEM_NVL',
576 avalue => nvl(l_supplier_item_number,
577 l_item_number));
578 wf_engine.SetItemAttrText ( itemtype => itemtype,
579 itemkey => itemkey,
580 aname => 'PURCHASING_UOM',
581 avalue => l_uom);
582
583 l_progress:='3';
584 if(l_buyer_id is not null) then
585 wf_directory.GetUserName('PER', l_buyer_id, l_buyer_username, l_buyer_displayname);
586 end if;
587 l_progress:='4';
588 if(l_planner_id is not null) then
589 wf_directory.GetUserName('PER', l_planner_id, l_planner_username, l_planner_displayname);
590 end if;
591 l_progress:='5';
592 if(l_vendor_id is not null) then
593 select vendor_name
594 into l_supplier_username
595 from po_vendors
596 where vendor_id=l_vendor_id;
597 end if;
598
599 l_progress:='6';
600 wf_engine.SetItemAttrText ( itemtype => itemtype,
601 itemkey => itemkey,
602 aname => 'SUPPLIER_ORG_NAME',
603 avalue => l_supplier_username);
604
605 l_progress:='7';
606 select count(*)
607 into l_vendor_id
608 from POS_MFG_CAPACITY_TEMP
609 where asl_id=l_asl_id and status in ('NEW', 'OLD', 'DEL', 'MOD');
610
611 l_progress:='8';
612 if(l_vendor_id>0) then
613 l_progress:='9';
614 select last_updated_by
615 into l_vendor_id
616 from POS_MFG_CAPACITY_TEMP
617 where mfg_capacity_id=
618 (select min(mfg_capacity_id)
619 from POS_MFG_CAPACITY_TEMP
620 where asl_id=l_asl_id and
621 status in ('NEW', 'OLD', 'DEL', 'MOD'));
622 else
623 l_progress:='10';
624 select last_updated_by
625 into l_vendor_id
626 from POS_CAPACITY_TOLERANCE_TEMP
627 where capacity_tolerance_id=
628 (select min(capacity_tolerance_id)
629 from POS_CAPACITY_TOLERANCE_TEMP
630 where asl_id=l_asl_id and status='NEW');
631 end if;
632
633 l_progress:='11';
634 if(l_vendor_id is not null) then
635 wf_directory.GetUserName('FND_USR', l_vendor_id, l_supplier_username, l_supplier_displayname);
636 end if;
637 l_progress:='12';
638 wf_engine.SetItemAttrText ( itemtype => itemtype,
639 itemkey => itemkey,
640 aname => 'BUYER_NAME',
641 avalue => l_buyer_username);
642 wf_engine.SetItemAttrText ( itemtype => itemtype,
643 itemkey => itemkey,
644 aname => 'BUYER_DISPLAY_NAME',
645 avalue => l_buyer_displayname);
646 wf_engine.SetItemAttrText ( itemtype => itemtype,
647 itemkey => itemkey,
648 aname => 'PLANNER_NAME',
649 avalue => l_planner_username);
650 wf_engine.SetItemAttrText ( itemtype => itemtype,
651 itemkey => itemkey,
652 aname => 'PLANNER_DISPLAY_NAME',
653 avalue => l_planner_displayname);
654 wf_engine.SetItemAttrText ( itemtype => itemtype,
655 itemkey => itemkey,
656 aname => 'SUPPLIER_NAME',
657 avalue => l_supplier_username);
658 wf_engine.SetItemAttrText ( itemtype => itemtype,
659 itemkey => itemkey,
660 aname => 'SUPPLIER_DISPLAY_NAME',
661 avalue => l_supplier_displayname);
662
663 l_progress:='13';
664 FND_PROFILE.get('POS_ASL_MOD_APPR_REQD_BY', l_approval_required_by);
665 l_progress:='14';
666 if(upper(l_approval_required_by)='BUYER') then
667 wf_engine.SetItemAttrText ( itemtype => itemtype,
668 itemkey => itemkey,
669 aname => 'APPROVAL_REQUIRED_BY',
670 avalue => 'BUYER');
671 elsif(upper(l_approval_required_by)='PLANNER') then
672 wf_engine.SetItemAttrText ( itemtype => itemtype,
673 itemkey => itemkey,
674 aname => 'APPROVAL_REQUIRED_BY',
675 avalue => 'PLANNER');
676 else
677 wf_engine.SetItemAttrText ( itemtype => itemtype,
678 itemkey => itemkey,
679 aname => 'APPROVAL_REQUIRED_BY',
680 avalue => 'NONE');
681 end if;
682
683 l_progress:='15';
684
685 wf_engine.SetItemAttrText (itemtype => itemtype,
686 itemkey => itemkey,
687 aname => 'POS_NOTIFY_APPROVER',
688 avalue => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_CAP_APP_NOTIF/'|| itemtype || ':' || itemkey);
689
690 wf_engine.SetItemAttrText (itemtype => itemtype,
691 itemkey => itemkey,
695 wf_engine.SetItemAttrText (itemtype => itemtype,
692 aname => 'POS_SUPP_NOTIF_APPR',
693 avalue => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_SUPPL_CAP_NOTIF_APPR/'|| itemtype || ':' || itemkey);
694
696 itemkey => itemkey,
697 aname => 'POS_SUPP_NOTIF_REJ',
698 avalue => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_SUPPL_CAP_NOTIF_REJ/'|| itemtype || ':' || itemkey);
699
700 OLD_MFG_CAPACITY_TABLE(itemtype, itemkey, l_asl_id);
701 l_progress:='16';
702 NEW_MFG_CAPACITY_TABLE(itemtype, itemkey, l_asl_id);
703 l_progress:='17';
704 OLD_CAPACITY_TOLERANCE_TABLE(itemtype, itemkey, l_asl_id);
705 l_progress:='18';
706 NEW_CAPACITY_TOLERANCE_TABLE(itemtype, itemkey, l_asl_id);
707
708 l_progress:='19';
709 EXCEPTION
710
711
712 WHEN OTHERS THEN
713 wf_core.context('POS_UPDATE_CAPACITY_PKG','INIT_ATTRIBUTES',l_progress);
714 raise;
715
716 end;
717
718 procedure GET_BUYER_NAME( itemtype in varchar2,
719 itemkey in varchar2,
720 actid in number,
721 funcmode in varchar2,
722 resultout out NOCOPY varchar2 ) is
723 begin
724 if (funcmode <> wf_engine.eng_run) then
725 resultout := wf_engine.eng_null;
726 return;
727 end if;
728 end;
729
730 procedure GET_PLANNER_NAME( itemtype in varchar2,
731 itemkey in varchar2,
732 actid in number,
733 funcmode in varchar2,
734 resultout out NOCOPY varchar2 ) is
735 begin
736 if (funcmode <> wf_engine.eng_run) then
737 resultout := wf_engine.eng_null;
738 return;
739 end if;
740 end;
741
742 procedure BUYER_APPROVAL_REQUIRED( itemtype in varchar2,
743 itemkey in varchar2,
744 actid in number,
745 funcmode in varchar2,
746 resultout out NOCOPY varchar2 ) is
747
748 l_approval_required_by varchar2(20);
749 l_buyer_name varchar2(180);
750 begin
751 if (funcmode <> wf_engine.eng_run) then
752 resultout := wf_engine.eng_null;
753 return;
754 end if;
755 l_approval_required_by:=wf_engine.GetItemAttrText ( itemtype => itemtype,
756 itemkey => itemkey,
757 aname => 'APPROVAL_REQUIRED_BY');
758 if(l_approval_required_by='BUYER') then
759 l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
760 itemkey => itemkey,
761 aname => 'BUYER_NAME');
762 wf_engine.SetItemAttrText ( itemtype => itemtype,
763 itemkey => itemkey,
764 aname => 'RESPONSE_FROM_ROLE',
765 avalue => l_buyer_name);
766 resultout := wf_engine.eng_completed || ':' || 'Y';
767 else
768 resultout := wf_engine.eng_completed || ':' || 'N';
769 end if;
770
771 end;
772
773 procedure BUYER_EXIST( itemtype in varchar2,
774 itemkey in varchar2,
775 actid in number,
776 funcmode in varchar2,
777 resultout out NOCOPY varchar2 ) is
778
779 l_buyer_name varchar2(100);
780 begin
781 if (funcmode <> wf_engine.eng_run) then
782 resultout := wf_engine.eng_null;
783 return;
784 end if;
785 l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
786 itemkey => itemkey,
787 aname => 'BUYER_NAME');
788 if(l_buyer_name is not null) then
789 resultout := wf_engine.eng_completed || ':' || 'Y';
790 else
791 resultout := wf_engine.eng_completed || ':' || 'N';
792 end if;
793 end;
794
795 procedure PLANNER_APPROVAL_REQUIRED( itemtype in varchar2,
796 itemkey in varchar2,
797 actid in number,
798 funcmode in varchar2,
799 resultout out NOCOPY varchar2 ) is
800
801 l_approval_required_by varchar2(20);
802 l_planner_name varchar2(180);
803 l_supplier_name varchar2(180);
804 begin
805 if (funcmode <> wf_engine.eng_run) then
806 resultout := wf_engine.eng_null;
807 return;
808 end if;
809 l_approval_required_by:=wf_engine.GetItemAttrText ( itemtype => itemtype,
810 itemkey => itemkey,
811 aname => 'APPROVAL_REQUIRED_BY');
812 if(l_approval_required_by='PLANNER') then
813 l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
814 itemkey => itemkey,
815 aname => 'PLANNER_NAME');
816 wf_engine.SetItemAttrText ( itemtype => itemtype,
817 itemkey => itemkey,
818 aname => 'RESPONSE_FROM_ROLE',
819 avalue => l_planner_name);
820 resultout := wf_engine.eng_completed || ':' || 'Y';
821 else
822 l_supplier_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
823 itemkey => itemkey,
824 aname => 'SUPPLIER_NAME');
825 wf_engine.SetItemAttrText ( itemtype => itemtype,
826 itemkey => itemkey,
830 end if;
827 aname => 'RESPONSE_FROM_ROLE',
828 avalue => l_supplier_name);
829 resultout := wf_engine.eng_completed || ':' || 'N';
831
832 end;
833
834 procedure PLANNER_EXIST( itemtype in varchar2,
835 itemkey in varchar2,
836 actid in number,
837 funcmode in varchar2,
838 resultout out NOCOPY varchar2 ) is
839
840 l_planner_name varchar2(100);
841 begin
842 if (funcmode <> wf_engine.eng_run) then
843 resultout := wf_engine.eng_null;
844 return;
845 end if;
846 l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
847 itemkey => itemkey,
848 aname => 'PLANNER_NAME');
849 if(l_planner_name is not null) then
850 resultout := wf_engine.eng_completed || ':' || 'Y';
851 else
852 resultout := wf_engine.eng_completed || ':' || 'N';
853 end if;
854 end;
855
856 procedure UPDATE_ASL( itemtype in varchar2,
857 itemkey in varchar2,
858 actid in number,
859 funcmode in varchar2,
860 resultout out NOCOPY varchar2 ) is
861
862 l_asl_id number;
863 l_num_of_days number;
864 l_tolerance number;
865 l_created_by number;
866
867 l_from_date DATE;
868 l_to_date DATE;
869 l_cap_per_day number;
870 l_status varchar2(10);
871 l_capacity_id number;
872 x_progress varchar2(3):='0';
873 l_progress number:=0;
874 CURSOR tol_updates(id number) is
875 SELECT
876 days_in_advance, tolerance, created_by
877 FROM POS_CAPACITY_TOLERANCE_TEMP
878 WHERE asl_id=id and status='NEW';
879
880 CURSOR cap_updates(id number) is
881 SELECT
882 from_date, to_date, capacity_per_day, capacity_id, created_by, status
883 FROM POS_MFG_CAPACITY_TEMP
884 WHERE asl_id=id and status in ('NEW', 'OLD', 'DEL', 'MOD');
885 begin
886 if (funcmode <> wf_engine.eng_run) then
887 resultout := wf_engine.eng_null;
888 return;
889 end if;
890 l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
891 itemkey => itemkey,
892 aname => 'ASL_ID');
893
894 pos_supplier_item_tol_pkg.delete(l_asl_id);
895
896 x_progress:='a1';
897 open tol_updates(l_asl_id);
898 LOOP
899 FETCH tol_updates INTO l_num_of_days, l_tolerance, l_created_by;
900 EXIT WHEN tol_updates%NOTFOUND;
901 pos_supplier_item_tol_pkg.store_line(l_asl_id, l_num_of_days, l_tolerance, l_created_by);
902 end loop;
903
904 x_progress:='a2';
905 update POS_CAPACITY_TOLERANCE_TEMP
906 set status='ACE'
907 where asl_id=l_asl_id
908 and status='NEW';
909
910 x_progress:='a3';
911 l_progress:=0;
912 open cap_updates(l_asl_id);
913 LOOP
914 FETCH cap_updates INTO l_from_date, l_to_date, l_cap_per_day, l_capacity_id, l_created_by, l_status;
915 EXIT WHEN cap_updates%NOTFOUND;
916
917 x_progress:='b'||to_char(l_progress);
918 l_progress:=l_progress+1;
919 if(l_status='NEW') then
920 insert into po_supplier_item_capacity
921 (CAPACITY_ID,
922 ASL_ID,
923 USING_ORGANIZATION_ID,
924 FROM_DATE,
925 TO_DATE,
926 CAPACITY_PER_DAY,
927 LAST_UPDATE_DATE,
928 LAST_UPDATED_BY,
929 LAST_UPDATE_LOGIN,
930 CREATION_DATE,
931 CREATED_BY)
932 values (
933 po_supplier_item_capacity_s.nextval,
934 l_asl_id,
935 -1,
936 l_from_date,
937 l_to_date,
938 l_cap_per_day,
939 sysdate,
940 l_created_by,
941 l_created_by,
942 sysdate,
943 l_created_by);
944 elsif(l_status='DEL') then
945 DELETE from po_supplier_item_capacity
946 WHERE
947 asl_id = l_asl_id AND capacity_id = l_capacity_id;
948 elsif(l_status='MOD') then
949 UPDATE po_supplier_item_capacity
950 SET
951 FROM_DATE = l_from_date,
952 TO_DATE = l_to_date,
953 CAPACITY_PER_DAY = l_cap_per_day,
954 last_update_date = Sysdate,
955 last_updated_by = l_created_by,
956 last_update_login = l_created_by
957 WHERE
958 asl_id = l_asl_id AND capacity_id = l_capacity_id;
959 end if;
960 end loop;
961 x_progress:='a4';
962
963 update POS_MFG_CAPACITY_TEMP
964 set status='ACE'
965 where asl_id=l_asl_id
966 and status in ('NEW', 'OLD', 'DEL', 'MOD');
967 x_progress:='a5';
968 EXCEPTION
969
970 WHEN OTHERS THEN
971 wf_core.context('POS_UPDATE_CAPACITY_PKG','UPDATE_ASL',x_progress);
972 raise;
973
974 end;
975
976 procedure DEFAULT_APPROVAL_MODE( itemtype in varchar2,
977 itemkey in varchar2,
978 actid in number,
979 funcmode in varchar2,
980 resultout out NOCOPY varchar2 ) is
981
982 l_default_mode varchar2(20);
983 begin
984 if (funcmode <> wf_engine.eng_run) then
985 resultout := wf_engine.eng_null;
986 return;
987 end if;
988 l_default_mode:=wf_engine.GetItemAttrText ( itemtype => itemtype,
989 itemkey => itemkey,
993 else
990 aname => 'DEFAULT_MODE');
991 if(upper(l_default_mode)='APPROVE') then
992 resultout := wf_engine.eng_completed || ':' || 'APPROVED';
994 resultout := wf_engine.eng_completed || ':' || 'REJECTED';
995 end if;
996
997 end;
998
999 procedure UPDATE_STATUS( itemtype in varchar2,
1000 itemkey in varchar2,
1001 actid in number,
1002 funcmode in varchar2,
1003 resultout out NOCOPY varchar2 ) is
1004
1005 l_asl_id number;
1006 begin
1007 if (funcmode <> wf_engine.eng_run) then
1008 resultout := wf_engine.eng_null;
1009 return;
1010 end if;
1011
1012 l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
1013 itemkey => itemkey,
1014 aname => 'ASL_ID');
1015
1016 update POS_MFG_CAPACITY_TEMP
1017 set status='REJ'
1018 where asl_id=l_asl_id
1019 and status in ('NEW', 'OLD', 'DEL', 'MOD');
1020
1021 update POS_CAPACITY_TOLERANCE_TEMP
1022 set status='REJ'
1023 where asl_id=l_asl_id
1024 and status='NEW';
1025
1026 end;
1027
1028 procedure BUYER_SAME_AS_PLANNER( itemtype in varchar2,
1029 itemkey in varchar2,
1030 actid in number,
1031 funcmode in varchar2,
1032 resultout out NOCOPY varchar2 ) is
1033
1034 l_buyer_name varchar2(100);
1035 l_planner_name varchar2(100);
1036 begin
1037 if (funcmode <> wf_engine.eng_run) then
1038 resultout := wf_engine.eng_null;
1039 return;
1040 end if;
1041 l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
1042 itemkey => itemkey,
1043 aname => 'BUYER_NAME');
1044 l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
1045 itemkey => itemkey,
1046 aname => 'PLANNER_NAME');
1047
1048 if(l_planner_name is not null) then
1049 if(l_planner_name=l_buyer_name) then
1050 resultout := wf_engine.eng_completed || ':' || 'Y';
1051 else
1052 resultout := wf_engine.eng_completed || ':' || 'N';
1053 end if;
1054 else
1055 resultout := wf_engine.eng_completed || ':' || 'Y';
1056 end if;
1057 end;
1058
1059
1060 PROCEDURE GENERATE_CAP_APP_NOTIF(document_id in varchar2,
1061 display_type in varchar2,
1062 document in OUT NOCOPY varchar2,
1063 document_type in OUT NOCOPY varchar2)
1064 IS
1065
1066 NL VARCHAR2(1) := fnd_global.newline;
1067 l_document VARCHAR2(32000) := '';
1068
1069 x_old_capacity_tolerance varchar2(32000);
1070 x_new_capacity_tolerance varchar2(32000);
1071 x_new_mfg_capacity_table varchar2(32000);
1072 x_old_mfg_capacity_table varchar2(32000);
1073
1074
1075 l_item_type varchar2(300) := '';
1076 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
1077
1078 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1079
1080 BEGIN
1081
1082 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1083 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
1084
1085 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
1086
1087 generate_header(l_document,l_item_type,l_item_key);
1088
1089
1090 x_old_capacity_tolerance := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1091 itemkey => l_item_key,
1092 aname => 'OLD_CAPACITY_TOLERANCE_TABLE');
1093
1094 x_new_capacity_tolerance := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1095 itemkey => l_item_key,
1096 aname => 'NEW_CAPACITY_TOLERANCE_TABLE');
1097
1098 x_new_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1099 itemkey => l_item_key,
1100 aname => 'NEW_MFG_CAPACITY_TABLE');
1101
1102 x_old_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1103 itemkey => l_item_key,
1104 aname => 'OLD_MFG_CAPACITY_TABLE');
1105
1106
1107
1108
1109 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_MANUF_CAPACITY') || '</B></font><HR> ' || NL;
1110 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1111 l_document := l_document || '<tr>' || NL;
1112 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_PREVIOUS_VALUES') || '</TH>' || NL;
1113
1114 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_REQUESTED_UPDATES') || '</TH>' || NL;
1115 l_document := l_document || '</tr>' || NL;
1116 l_document := l_document || '<tr><td valign=top>' || X_OLD_MFG_CAPACITY_TABLE || '</td><td valign=top>' || X_NEW_MFG_CAPACITY_TABLE || '</td>' || NL;
1117 l_document := l_document || '</tr> ' || NL;
1118 l_document := l_document || '</table>' || NL;
1119
1120 l_document := l_document || '<br>' || NL;
1121
1122 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_CAPACITY_TOLERANCE') || '</B></font><HR> ' || NL;
1123 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1127 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_REQUESTED_UPDATES') || '</TH>' || NL;
1124 l_document := l_document || '<tr>' || NL;
1125 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_PREVIOUS_VALUES') || '</TH>' || NL;
1126
1128 l_document := l_document || '</tr>' || NL;
1129 l_document := l_document || '<tr><td valign=top>' || X_OLD_CAPACITY_TOLERANCE|| '</td><td valign=top>' || X_NEW_CAPACITY_TOLERANCE || '</td>' || NL;
1130 l_document := l_document || '</tr> ' || NL;
1131 l_document := l_document || '</table>' || NL;
1132
1133 l_document := l_document || '</td> </tr> </table>' || NL;
1134
1135
1136 document := l_document;
1137
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 NULL;
1141 END;
1142
1143
1144 procedure generate_header(document in out nocopy varchar2,
1145 itemtype in varchar2,
1146 itemkey in varchar2)
1147 is
1148
1149 NL VARCHAR2(1) := fnd_global.newline;
1150 l_document VARCHAR2(32000) := '';
1151
1152 x_supp_item varchar2(25);
1153 x_item_num varchar2(25);
1154 x_item_desc varchar2(240);
1155 x_uom varchar2(25);
1156
1157 begin
1158
1159 x_item_desc := wf_engine.GetItemAttrText ( itemtype => itemtype,
1160 itemkey => itemkey,
1161 aname => 'ITEM_DESCRIPTION');
1162
1163 x_item_num := wf_engine.GetItemAttrText ( itemtype => itemtype,
1164 itemkey => itemkey,
1165 aname => 'ITEM_NUM');
1166
1167 x_supp_item := wf_engine.GetItemAttrText ( itemtype => itemtype,
1168 itemkey => itemkey,
1169 aname => 'SUPPLIER_ITEM');
1170
1171 x_uom := wf_engine.GetItemAttrText ( itemtype => itemtype,
1172 itemkey => itemkey,
1173 aname => 'PURCHASING_UOM');
1174
1175
1176 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;
1177
1178 l_document := l_document || '<table width=100%><tr><td width=2>' || ' ' || '</td><td>' || NL;
1179
1180 l_document := l_document || '<table cellpadding=2 cellspacing=1> ' || NL;
1181
1182 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;
1183
1184 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;
1185
1186 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;
1187
1188 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;
1189
1190
1191 l_document := l_document || '</table>' || NL;
1192
1193 document := l_document;
1194
1195 exception
1196 when others then
1197 null;
1198 end;
1199
1200
1201 PROCEDURE GENERATE_SUPPL_CAP_NOTIF_APPR(document_id in varchar2,
1202 display_type in varchar2,
1203 document in OUT NOCOPY varchar2,
1204 document_type in OUT NOCOPY varchar2)
1205 IS
1206
1207 NL VARCHAR2(1) := fnd_global.newline;
1208 l_document VARCHAR2(32000) := '';
1209
1210 x_new_capacity_tolerance_table varchar2(32000) := '';
1211 x_new_mfg_capacity_table varchar2(32000) := '';
1212
1213 l_item_type varchar2(300) := '';
1214 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
1215
1216 l_base_url VARCHAR(2000) := '';
1217
1218 BEGIN
1219
1220
1221 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1222 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
1223
1224
1225 l_base_url := POS_URL_PKG.get_external_url;
1226
1227
1228 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_url || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
1229
1230 generate_header(l_document,l_item_type,l_item_key);
1231
1232 x_new_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1233 itemkey => l_item_key,
1234 aname => 'NEW_MFG_CAPACITY_TABLE');
1235
1236 x_new_capacity_tolerance_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1237 itemkey => l_item_key,
1238 aname => 'NEW_CAPACITY_TOLERANCE_TABLE');
1239
1240
1241 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_MANUF_CAPACITY') || '</B></font><HR> ' || fnd_message.get_string('POS','POS_ORD_MOD_APPROVED') || NL;
1242
1243 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1244 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1245
1246 l_document := l_document || '<tr><td>' || X_NEW_MFG_CAPACITY_TABLE || '</td></tr></table>' || NL;
1247
1248 l_document := l_document || '<br>' || NL;
1249
1250 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_CAPACITY_TOLERANCE') || '</b></font><HR> ' || NL;
1251
1252 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1253 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1254
1255 l_document := l_document || '<tr><td>' || X_NEW_CAPACITY_TOLERANCE_TABLE || '</td></tr></table>' || NL;
1256
1257
1258 l_document := l_document || '</td></tr></table>'|| NL;
1259
1260 document := l_document;
1261
1262
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 NULL;
1266 END;
1267
1268
1269 PROCEDURE GENERATE_SUPPL_CAP_NOTIF_REJ(document_id in varchar2,
1270 display_type in varchar2,
1271 document in OUT NOCOPY varchar2,
1272 document_type in OUT NOCOPY varchar2)
1273 IS
1274
1275 NL VARCHAR2(1) := fnd_global.newline;
1276 l_document VARCHAR2(32000) := '';
1277
1278 x_new_capacity_tolerance_table varchar2(32000) := '';
1279 x_new_mfg_capacity_table varchar2(32000) := '';
1280
1281 l_item_type varchar2(300) := '';
1282 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
1283
1284 l_base_url VARCHAR(2000) := '';
1285
1286 BEGIN
1287
1288
1289 l_base_url := POS_URL_PKG.get_external_url;
1290
1291
1292 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1293 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
1294
1295 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_url || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
1296
1297 generate_header(l_document,l_item_type,l_item_key);
1298
1299 x_new_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1300 itemkey => l_item_key,
1301 aname => 'NEW_MFG_CAPACITY_TABLE');
1302
1303 x_new_capacity_tolerance_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1304 itemkey => l_item_key,
1305 aname => 'NEW_CAPACITY_TOLERANCE_TABLE');
1306
1307
1308 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_MANUF_CAPACITY') || '</B></font><HR> ' || fnd_message.get_string('POS','POS_ORD_MOD_REJECTED') || NL;
1309
1310 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1311 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1312
1313 l_document := l_document || '<tr><td>' || X_NEW_MFG_CAPACITY_TABLE || '</td></tr></table>' || NL;
1314
1315 l_document := l_document || '<br>' || NL;
1316
1317 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_CAPACITY_TOLERANCE') || '</b></font><HR> ' || NL;
1318
1319 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1320 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1321
1322 l_document := l_document || '<tr><td>' || X_NEW_CAPACITY_TOLERANCE_TABLE || '</td></tr></table>' || NL;
1323
1324
1325 l_document := l_document || '</td></tr></table>'|| NL;
1326
1327 document := l_document;
1328
1329
1330 EXCEPTION
1331 WHEN OTHERS THEN
1332 NULL;
1333 END;
1334
1335 END POS_UPDATE_CAPACITY_PKG;