eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POS_UPDATE_CAPACITY_PKG

Source


1 PACKAGE BODY POS_UPDATE_CAPACITY_PKG AS
2 /* $Header: POSUPDNB.pls 115.10 2004/09/10 20:32:20 jacheung 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,
131     tolerance,
132     /*
133     supplier_item_number,
134     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 
242   CURSOR old_mfg_capacity(id number) is
243          SELECT from_date, to_date, capacity_per_day
244          FROM   pos_supplier_item_capacity_v
245          WHERE  asl_id=id
246          order by from_date asc;
247 
248 begin
249   l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>';
250 
251   l_document := l_document || '<TR>';
252 
253   l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
254                      fnd_message.get_string('POS', 'POS_FROM') ||
255                      '</TH> ' || NL;
256   l_document := l_document || '<TH align=left ' || L_TABLE_HEADER_STYLE || '>' ||
257                      fnd_message.get_string('POS', 'POS_TO') ||
258                      '</TH> ' || NL;
259    l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
260 	                     fnd_message.get_string('POS', 'POS_CAPACITY_PER_DAY') ||
261                      '</TH> ' || NL;
262 
263   l_document := l_document || '</TR>';
264 
265   open old_mfg_capacity(asl_id);
266 
267   LOOP
268     FETCH old_mfg_capacity INTO l_from, l_to, l_cap_per_day;
269     EXIT WHEN old_mfg_capacity%NOTFOUND;
270  l_document := l_document || '<TD '||  L_TABLE_CELL_STYLE ||'>' || to_char(l_from)||'</TD> ' || NL;
271     l_document := l_document || '<TD ' ||  L_TABLE_CELL_STYLE ||'>' ||to_char(l_to)||'</TD> ' || NL;
272     l_document := l_document || '<TD ' ||  L_TABLE_CELL_STYLE ||'>' ||to_char(l_cap_per_day)||'</TD> ' || NL;
273     l_document := l_document || '</TR>' || NL;
274 
275 
276   end loop;
277 
278   l_document := l_document || '</TABLE>';
279 
280   wf_engine.SetItemAttrText ( itemtype => itemtype,
281                                   itemkey  => itemkey,
282                                   aname    => 'OLD_MFG_CAPACITY_TABLE',
283                                   avalue   => l_document);
284 
285 end OLD_MFG_CAPACITY_TABLE;
286 
287 procedure NEW_MFG_CAPACITY_TABLE( itemtype in varchar2,
288            itemkey in varchar2,
289            asl_id in number) is
290 
291 
292   l_document      VARCHAR2(32000) := '';
293   NL              VARCHAR2(1) := fnd_global.newline;
294   l_from DATE;
295   l_to DATE;
296   l_cap_per_day NUMBER;
297 
298   CURSOR new_mfg_capacity(id number) is
299          SELECT from_date, to_date, capacity_per_day
300          FROM   pos_mfg_capacity_temp
301          WHERE  asl_id=id and status in ('NEW', 'OLD', 'MOD')
302          order by from_date asc;
303 
304 begin
305   l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>';
306 
307   l_document := l_document || '<TR>';
308 
309   l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
310                      fnd_message.get_string('POS', 'POS_FROM') ||
311                      '</TH> ' || NL;
312   l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
313                      fnd_message.get_string('POS', 'POS_TO') ||
314                      '</TH> ' || NL;
315    l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
316 	                     fnd_message.get_string('POS', 'POS_CAPACITY_PER_DAY') ||
317                      '</TH> ' || NL;
318 
319   l_document := l_document || '</TR>';
320 
321   open new_mfg_capacity(asl_id);
322 
323   LOOP
324     FETCH new_mfg_capacity INTO l_from, l_to, l_cap_per_day;
325     EXIT WHEN new_mfg_capacity%NOTFOUND;
326     l_document := l_document || '<TR>';
327 
328     l_document := l_document || '<TD '||  L_TABLE_CELL_STYLE ||'>' || to_char(l_from)||'</TD> ' || NL;
329     l_document := l_document || '<TD' ||  L_TABLE_CELL_STYLE ||'>' ||to_char(l_to)||'</TD> ' || NL;
330     l_document := l_document || '<TD' ||  L_TABLE_CELL_STYLE ||'>' ||to_char(l_cap_per_day)||'</TD> ' || NL;
331     l_document := l_document || '</TR>' || NL;
332   end loop;
333 
334   l_document := l_document || '</TABLE>' || NL;
335 
336   wf_engine.SetItemAttrText ( itemtype => itemtype,
337                                   itemkey  => itemkey,
338                                   aname    => 'NEW_MFG_CAPACITY_TABLE',
339                                   avalue   => l_document);
340 
341 end NEW_MFG_CAPACITY_TABLE;
342 
343 
344 procedure OLD_CAPACITY_TOLERANCE_TABLE( itemtype in varchar2,
345            itemkey in varchar2,
346            asl_id in number) is
347 
348 
349   l_document      VARCHAR2(32000) := '';
350   NL              VARCHAR2(1) := fnd_global.newline;
351   l_days_in_advance NUMBER;
352   l_tolerance NUMBEr;
353 
354   CURSOR OLD_CAPACITY_TOLERANCE(id number) is
355          SELECT number_of_days, tolerance
356          FROM   po_supplier_item_tolerance
357          WHERE  asl_id=id
358          order by number_of_days asc;
359 
360 begin
361 
362 l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>' || NL;
363 
364   l_document := l_document || '<TR>' || NL;
365 
366   l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
367                      fnd_message.get_string('POS', 'POS_DAYS_IN_ADVANCE') ||
368                      '</TH> ' || NL;
369   l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
370                      fnd_message.get_string('POS', 'POS_TOLERANCE') ||
371                      '</TH> ' || NL;
372 
373   l_document := l_document || '</TR>' || NL;
374 
375 
376   open OLD_CAPACITY_TOLERANCE(asl_id);
377 
378   LOOP
379     FETCH OLD_CAPACITY_TOLERANCE INTO l_days_in_advance, l_tolerance;
380     EXIT WHEN OLD_CAPACITY_TOLERANCE%NOTFOUND;
381 
382 
383     l_document := l_document || '<TR>';
384 
385     l_document := l_document || '<TD '||  L_TABLE_CELL_STYLE ||'>' || to_char(l_days_in_advance)||'</TD> ';
386     l_document := l_document || '<TD ' ||  L_TABLE_CELL_STYLE ||'>' ||to_char(l_tolerance)||'</TD> ';
387 
388     l_document := l_document || '</TR>';
389   end loop;
390 
391   l_document := l_document || '</TABLE>';
392 
393   wf_engine.SetItemAttrText ( itemtype => itemtype,
394                                   itemkey  => itemkey,
395                                   aname    => 'OLD_CAPACITY_TOLERANCE_TABLE',
396                                   avalue   => l_document);
397 
398 end OLD_CAPACITY_TOLERANCE_TABLE;
399 
400 procedure NEW_CAPACITY_TOLERANCE_TABLE( itemtype in varchar2,
401            itemkey in varchar2,
402            asl_id in number) is
403 
404   l_document      VARCHAR2(32000) := '';
405   NL              VARCHAR2(1) := fnd_global.newline;
406   l_days_in_advance NUMBER;
407   l_tolerance NUMBEr;
408 
409   CURSOR NEW_CAPACITY_TOLERANCE(id number) is
410          SELECT days_in_advance, tolerance
411          FROM   POS_CAPACITY_TOLERANCE_TEMP
412          WHERE  asl_id=id and status='NEW'
413          order by days_in_advance asc;
414 
415 begin
416 
417 
418 l_document := '<TABLE' || L_TABLE_STYLE || 'cellpadding=2 cellspacing=1>' || NL;
419 
420   l_document := l_document || '<TR>' || NL;
421 
422   l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
423                      fnd_message.get_string('POS', 'POS_DAYS_IN_ADVANCE') ||
424                      '</TH> ' || NL;
425   l_document := l_document || '<TH align=left' || L_TABLE_HEADER_STYLE || '>' ||
426                      fnd_message.get_string('POS', 'POS_TOLERANCE') ||
427                      '</TH> ' || NL;
428 
429   l_document := l_document || '</TR>' || NL;
430 
431 
432   open NEW_CAPACITY_TOLERANCE(asl_id);
433 
434   LOOP
435     FETCH NEW_CAPACITY_TOLERANCE INTO l_days_in_advance, l_tolerance;
436     EXIT WHEN NEW_CAPACITY_TOLERANCE%NOTFOUND;
437 
438     l_document := l_document || '<TR>';
439 
440     l_document := l_document || '<TD '||  L_TABLE_CELL_STYLE ||'>' || to_char(l_days_in_advance)||'</TD> ';
441     l_document := l_document || '<TD '||  L_TABLE_CELL_STYLE ||'>' ||to_char(l_tolerance)||'</TD> ';
442 
443     l_document := l_document || '</TR>';
444 
445   end loop;
446 
447   l_document := l_document || '</TABLE>';
448 
449   wf_engine.SetItemAttrText ( itemtype => itemtype,
450                                   itemkey  => itemkey,
451                                   aname    => 'NEW_CAPACITY_TOLERANCE_TABLE',
452                                   avalue   => l_document);
453 
454 end NEW_CAPACITY_TOLERANCE_TABLE;
455 
456 
457 procedure INIT_ATTRIBUTES(  itemtype        in  varchar2,
458     itemkey         in  varchar2,
459     actid           in number,
460     funcmode        in  varchar2,
461     resultout          out NOCOPY varchar2    ) is
462 
463   l_supplier_item_number varchar2(25);
464   l_item_number varchar2(25);
465   l_item_description varchar2(240);
466   l_uom varchar2(25);
467   l_vendor_id number;
468   l_buyer_id number;
469   l_planner_id number;
470   l_asl_id number;
471   l_buyer_username varchar2(80):=null;
472   l_planner_username varchar2(80):=null;
473   l_supplier_username varchar2(240):=null;
474   l_buyer_displayname varchar2(80):=null;
475   l_planner_displayname varchar2(80):=null;
476   l_supplier_displayname varchar2(80):=null;
477   l_approval_required_by varchar2(20);
478   l_progress varchar2(3):='0';
479 
480 begin
481   if (funcmode <> wf_engine.eng_run) then
482       resultout := wf_engine.eng_null;
483       return;
484   end if;
485   l_progress:='1';
486   l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
487                                           itemkey  => itemkey,
488                                           aname    => 'ASL_ID');
489   select  DESCRIPTION,
490           BUYER_ID,
491           PLANNER_ID,
492           UOM,
493           SUPPLIER_ITEM_NUMBER,
494           ITEM_NUMBER,
495           VENDOR_ID
496   into    l_item_description,
497           l_buyer_id,
498           l_planner_id,
499           l_uom,
500           l_supplier_item_number,
501           l_item_number,
502           l_vendor_id
503   from POS_ORD_MODIFIERS_V
504   where asl_id=l_asl_id;
505 
506   l_progress:='2';
507   wf_engine.SetItemAttrText ( itemtype => itemtype,
508                                   itemkey  => itemkey,
509                                   aname    => 'ITEM_DESCRIPTION',
510                                   avalue   => l_item_description);
511   wf_engine.SetItemAttrText ( itemtype => itemtype,
512                                   itemkey  => itemkey,
513                                   aname    => 'ITEM_NUM',
514                                   avalue   => l_item_number);
515   wf_engine.SetItemAttrText ( itemtype => itemtype,
516                                   itemkey  => itemkey,
517                                   aname    => 'SUPPLIER_ITEM',
518                                   avalue   => l_supplier_item_number);
519   wf_engine.SetItemAttrText ( itemtype => itemtype,
520                                   itemkey  => itemkey,
521                                   aname    => 'SUPPLIER_ITEM_NVL',
522                                   avalue   => nvl(l_supplier_item_number,
523                                                  l_item_number));
524   wf_engine.SetItemAttrText ( itemtype => itemtype,
525                                   itemkey  => itemkey,
526                                   aname    => 'PURCHASING_UOM',
527                                   avalue   => l_uom);
528 
529   l_progress:='3';
530   if(l_buyer_id is not null) then
531     wf_directory.GetUserName('PER', l_buyer_id, l_buyer_username, l_buyer_displayname);
532   end if;
533   l_progress:='4';
534   if(l_planner_id is not null) then
535     wf_directory.GetUserName('PER', l_planner_id, l_planner_username, l_planner_displayname);
536   end if;
537   l_progress:='5';
538   if(l_vendor_id is not null) then
539     select vendor_name
540       into l_supplier_username
541       from po_vendors
542      where vendor_id=l_vendor_id;
543   end if;
544 
545   l_progress:='6';
546   wf_engine.SetItemAttrText ( itemtype => itemtype,
547                                   itemkey  => itemkey,
548                                   aname    => 'SUPPLIER_ORG_NAME',
549                                   avalue   => l_supplier_username);
550 
551   l_progress:='7';
552   select count(*)
553   into l_vendor_id
554   from POS_MFG_CAPACITY_TEMP
555   where asl_id=l_asl_id and status in ('NEW', 'OLD', 'DEL', 'MOD');
556 
557   l_progress:='8';
558   if(l_vendor_id>0) then
559     l_progress:='9';
560     select last_updated_by
561       into l_vendor_id
562       from POS_MFG_CAPACITY_TEMP
563       where mfg_capacity_id=
564              (select min(mfg_capacity_id)
565                 from POS_MFG_CAPACITY_TEMP
566                where asl_id=l_asl_id and
567                      status in ('NEW', 'OLD', 'DEL', 'MOD'));
568   else
569     l_progress:='10';
570     select last_updated_by
571       into l_vendor_id
572       from POS_CAPACITY_TOLERANCE_TEMP
573       where capacity_tolerance_id=
574              (select min(capacity_tolerance_id)
575                 from POS_CAPACITY_TOLERANCE_TEMP
576                where asl_id=l_asl_id and status='NEW');
577   end if;
578 
579   l_progress:='11';
580   if(l_vendor_id is not null) then
581     wf_directory.GetUserName('FND_USR', l_vendor_id, l_supplier_username, l_supplier_displayname);
582   end if;
583   l_progress:='12';
584   wf_engine.SetItemAttrText ( itemtype => itemtype,
585                                   itemkey  => itemkey,
586                                   aname    => 'BUYER_NAME',
587                                   avalue   => l_buyer_username);
588   wf_engine.SetItemAttrText ( itemtype => itemtype,
589                                   itemkey  => itemkey,
590                                   aname    => 'BUYER_DISPLAY_NAME',
591                                   avalue   => l_buyer_displayname);
592   wf_engine.SetItemAttrText ( itemtype => itemtype,
593                                   itemkey  => itemkey,
594                                   aname    => 'PLANNER_NAME',
595                                   avalue   => l_planner_username);
596   wf_engine.SetItemAttrText ( itemtype => itemtype,
597                                   itemkey  => itemkey,
598                                   aname    => 'PLANNER_DISPLAY_NAME',
599                                   avalue   => l_planner_displayname);
600   wf_engine.SetItemAttrText ( itemtype => itemtype,
601                                   itemkey  => itemkey,
602                                   aname    => 'SUPPLIER_NAME',
603                                   avalue   => l_supplier_username);
604   wf_engine.SetItemAttrText ( itemtype => itemtype,
605                                   itemkey  => itemkey,
606                                   aname    => 'SUPPLIER_DISPLAY_NAME',
607                                   avalue   => l_supplier_displayname);
608 
609   l_progress:='13';
610   FND_PROFILE.get('POS_ASL_MOD_APPR_REQD_BY', l_approval_required_by);
611   l_progress:='14';
612   if(upper(l_approval_required_by)='BUYER') then
613     wf_engine.SetItemAttrText ( itemtype => itemtype,
614                                   itemkey  => itemkey,
615                                   aname    => 'APPROVAL_REQUIRED_BY',
616                                   avalue   => 'BUYER');
617   elsif(upper(l_approval_required_by)='PLANNER') then
618     wf_engine.SetItemAttrText ( itemtype => itemtype,
619                                   itemkey  => itemkey,
620                                   aname    => 'APPROVAL_REQUIRED_BY',
621                                   avalue   => 'PLANNER');
622   else
623     wf_engine.SetItemAttrText ( itemtype => itemtype,
624                                   itemkey  => itemkey,
625                                   aname    => 'APPROVAL_REQUIRED_BY',
626                                   avalue   => 'NONE');
627   end if;
628 
629   l_progress:='15';
630 
631  wf_engine.SetItemAttrText (itemtype        => itemtype,
632 			     itemkey         => itemkey,
633 			     aname           => 'POS_NOTIFY_APPROVER',
634 			     avalue  	 => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_CAP_APP_NOTIF/'|| itemtype || ':' || itemkey);
635 
636    wf_engine.SetItemAttrText (itemtype        => itemtype,
637 			     itemkey         => itemkey,
638 			     aname           => 'POS_SUPP_NOTIF_APPR',
639 			     avalue => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_SUPPL_CAP_NOTIF_APPR/'|| itemtype || ':' || itemkey);
640 
641   wf_engine.SetItemAttrText (itemtype        => itemtype,
642 			     itemkey         => itemkey,
643 			     aname           => 'POS_SUPP_NOTIF_REJ',
644 			     avalue  	 => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_SUPPL_CAP_NOTIF_REJ/'|| itemtype || ':' || itemkey);
645 
646   OLD_MFG_CAPACITY_TABLE(itemtype, itemkey, l_asl_id);
647   l_progress:='16';
648   NEW_MFG_CAPACITY_TABLE(itemtype, itemkey, l_asl_id);
649   l_progress:='17';
650   OLD_CAPACITY_TOLERANCE_TABLE(itemtype, itemkey, l_asl_id);
651   l_progress:='18';
652   NEW_CAPACITY_TOLERANCE_TABLE(itemtype, itemkey, l_asl_id);
653 
654   l_progress:='19';
655 EXCEPTION
656 
657 
658   WHEN OTHERS THEN
659        wf_core.context('POS_UPDATE_CAPACITY_PKG','INIT_ATTRIBUTES',l_progress);
660        raise;
661 
662 end;
663 
664 procedure GET_BUYER_NAME(  itemtype        in  varchar2,
665   itemkey         in  varchar2,
666   actid           in number,
667   funcmode        in  varchar2,
668   resultout          out NOCOPY varchar2    ) is
669 begin
670   if (funcmode <> wf_engine.eng_run) then
671       resultout := wf_engine.eng_null;
672       return;
673   end if;
674 end;
675 
676 procedure GET_PLANNER_NAME(  itemtype        in  varchar2,
677   itemkey         in  varchar2,
678   actid           in number,
679   funcmode        in  varchar2,
680   resultout          out NOCOPY varchar2    ) is
681 begin
682   if (funcmode <> wf_engine.eng_run) then
683       resultout := wf_engine.eng_null;
684       return;
685   end if;
686 end;
687 
688 procedure BUYER_APPROVAL_REQUIRED(  itemtype        in  varchar2,
689   itemkey         in  varchar2,
690   actid           in number,
691   funcmode        in  varchar2,
692   resultout          out NOCOPY varchar2    ) is
693 
694 l_approval_required_by varchar2(20);
695 l_buyer_name varchar2(180);
696 begin
697   if (funcmode <> wf_engine.eng_run) then
698       resultout := wf_engine.eng_null;
699       return;
700   end if;
701   l_approval_required_by:=wf_engine.GetItemAttrText ( itemtype => itemtype,
702                                   itemkey  => itemkey,
703                                   aname    => 'APPROVAL_REQUIRED_BY');
704   if(l_approval_required_by='BUYER') then
705     l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
706                                           itemkey  => itemkey,
707                                           aname    => 'BUYER_NAME');
708     wf_engine.SetItemAttrText ( itemtype => itemtype,
709                                   itemkey  => itemkey,
710                                   aname    => 'RESPONSE_FROM_ROLE',
711                                   avalue   => l_buyer_name);
712     resultout := wf_engine.eng_completed || ':' || 'Y';
713   else
714     resultout := wf_engine.eng_completed || ':' || 'N';
715   end if;
716 
717 end;
718 
719 procedure BUYER_EXIST(  itemtype        in  varchar2,
720   itemkey         in  varchar2,
721   actid           in number,
722   funcmode        in  varchar2,
723   resultout          out NOCOPY varchar2    ) is
724 
725   l_buyer_name varchar2(100);
726 begin
727   if (funcmode <> wf_engine.eng_run) then
728       resultout := wf_engine.eng_null;
729       return;
730   end if;
731   l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
732                                           itemkey  => itemkey,
733                                           aname    => 'BUYER_NAME');
734   if(l_buyer_name is not null) then
735     resultout := wf_engine.eng_completed || ':' || 'Y';
736   else
737     resultout := wf_engine.eng_completed || ':' || 'N';
738   end if;
739 end;
740 
741 procedure PLANNER_APPROVAL_REQUIRED(  itemtype        in  varchar2,
742   itemkey         in  varchar2,
743   actid           in number,
744   funcmode        in  varchar2,
745   resultout          out NOCOPY varchar2    ) is
746 
747 l_approval_required_by varchar2(20);
748 l_planner_name varchar2(180);
749 l_supplier_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='PLANNER') then
759     l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
760                                           itemkey  => itemkey,
761                                           aname    => 'PLANNER_NAME');
762     wf_engine.SetItemAttrText ( itemtype => itemtype,
763                                   itemkey  => itemkey,
764                                   aname    => 'RESPONSE_FROM_ROLE',
765                                   avalue   => l_planner_name);
766     resultout := wf_engine.eng_completed || ':' || 'Y';
767   else
768     l_supplier_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
769                                           itemkey  => itemkey,
770                                           aname    => 'SUPPLIER_NAME');
771     wf_engine.SetItemAttrText ( itemtype => itemtype,
772                                   itemkey  => itemkey,
773                                   aname    => 'RESPONSE_FROM_ROLE',
774                                   avalue   => l_supplier_name);
775     resultout := wf_engine.eng_completed || ':' || 'N';
776   end if;
777 
778 end;
779 
780 procedure PLANNER_EXIST(  itemtype        in  varchar2,
781   itemkey         in  varchar2,
782   actid           in number,
783   funcmode        in  varchar2,
784   resultout          out NOCOPY varchar2    ) is
785 
786   l_planner_name varchar2(100);
787 begin
788   if (funcmode <> wf_engine.eng_run) then
789       resultout := wf_engine.eng_null;
790       return;
791   end if;
792   l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
793                                           itemkey  => itemkey,
794                                           aname    => 'PLANNER_NAME');
795   if(l_planner_name is not null) then
796     resultout := wf_engine.eng_completed || ':' || 'Y';
797   else
798     resultout := wf_engine.eng_completed || ':' || 'N';
799   end if;
800 end;
801 
802 procedure UPDATE_ASL(  itemtype        in  varchar2,
803   itemkey         in  varchar2,
804   actid           in number,
805   funcmode        in  varchar2,
806   resultout          out NOCOPY varchar2    ) is
807 
808   l_asl_id number;
809   l_num_of_days number;
810   l_tolerance number;
811   l_created_by number;
812 
813   l_from_date DATE;
814   l_to_date DATE;
815   l_cap_per_day number;
816   l_status varchar2(10);
817   l_capacity_id number;
818   x_progress varchar2(3):='0';
819   l_progress number:=0;
820   CURSOR tol_updates(id number) is
821     SELECT
822       days_in_advance, tolerance, created_by
823     FROM  POS_CAPACITY_TOLERANCE_TEMP
824     WHERE asl_id=id and status='NEW';
825 
826   CURSOR cap_updates(id number) is
827     SELECT
828       from_date, to_date, capacity_per_day, capacity_id, created_by, status
829     FROM  POS_MFG_CAPACITY_TEMP
830     WHERE asl_id=id and status in ('NEW', 'OLD', 'DEL', 'MOD');
831 begin
832   if (funcmode <> wf_engine.eng_run) then
833       resultout := wf_engine.eng_null;
834       return;
835   end if;
836   l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
837                                           itemkey  => itemkey,
838                                           aname    => 'ASL_ID');
839 
840   pos_supplier_item_tol_pkg.delete(l_asl_id);
841 
842   x_progress:='a1';
843   open tol_updates(l_asl_id);
844   LOOP
845     FETCH tol_updates INTO l_num_of_days, l_tolerance, l_created_by;
846     EXIT WHEN tol_updates%NOTFOUND;
847     pos_supplier_item_tol_pkg.store_line(l_asl_id, l_num_of_days, l_tolerance, l_created_by);
848   end loop;
849 
850   x_progress:='a2';
851   update POS_CAPACITY_TOLERANCE_TEMP
852      set status='ACE'
853    where asl_id=l_asl_id
854          and status='NEW';
855 
856   x_progress:='a3';
857   l_progress:=0;
858   open cap_updates(l_asl_id);
859   LOOP
860     FETCH cap_updates INTO l_from_date, l_to_date, l_cap_per_day, l_capacity_id, l_created_by, l_status;
861     EXIT WHEN cap_updates%NOTFOUND;
862 
863     x_progress:='b'||to_char(l_progress);
864     l_progress:=l_progress+1;
865     if(l_status='NEW') then
866       insert into po_supplier_item_capacity
867        (CAPACITY_ID,
868         ASL_ID,
869         USING_ORGANIZATION_ID,
870         FROM_DATE,
871         TO_DATE,
872         CAPACITY_PER_DAY,
873         LAST_UPDATE_DATE,
874         LAST_UPDATED_BY,
875         LAST_UPDATE_LOGIN,
876         CREATION_DATE,
877         CREATED_BY)
878       values (
879 	     po_supplier_item_capacity_s.nextval,
880 	     l_asl_id,
881 	     -1,
882 	     l_from_date,
883 	     l_to_date,
884 	     l_cap_per_day,
885 	     sysdate,
886 	     l_created_by,
887 	     l_created_by,
888 	     sysdate,
889 	     l_created_by);
890     elsif(l_status='DEL') then
891       DELETE from po_supplier_item_capacity
892       WHERE
893         asl_id = l_asl_id AND capacity_id = l_capacity_id;
894     elsif(l_status='MOD') then
895       UPDATE po_supplier_item_capacity
896       SET
897         FROM_DATE = l_from_date,
898         TO_DATE = l_to_date,
899         CAPACITY_PER_DAY = l_cap_per_day,
900         last_update_date = Sysdate,
901         last_updated_by = l_created_by,
902         last_update_login = l_created_by
903       WHERE
904         asl_id = l_asl_id AND capacity_id = l_capacity_id;
905     end if;
906   end loop;
907   x_progress:='a4';
908 
909   update POS_MFG_CAPACITY_TEMP
910      set status='ACE'
911    where asl_id=l_asl_id
912          and status in ('NEW', 'OLD', 'DEL', 'MOD');
913   x_progress:='a5';
914   EXCEPTION
915 
916     WHEN OTHERS THEN
917          wf_core.context('POS_UPDATE_CAPACITY_PKG','UPDATE_ASL',x_progress);
918        raise;
919 
920 end;
921 
922 procedure DEFAULT_APPROVAL_MODE(  itemtype        in  varchar2,
923   itemkey         in  varchar2,
924   actid           in number,
925   funcmode        in  varchar2,
926   resultout          out NOCOPY varchar2    ) is
927 
928 l_default_mode varchar2(20);
929 begin
930   if (funcmode <> wf_engine.eng_run) then
931       resultout := wf_engine.eng_null;
932       return;
933   end if;
934   l_default_mode:=wf_engine.GetItemAttrText ( itemtype => itemtype,
935                                           itemkey  => itemkey,
936                                           aname    => 'DEFAULT_MODE');
937   if(upper(l_default_mode)='APPROVE') then
938     resultout := wf_engine.eng_completed || ':' || 'APPROVED';
939   else
940     resultout := wf_engine.eng_completed || ':' || 'REJECTED';
941   end if;
942 
943 end;
944 
945 procedure UPDATE_STATUS(  itemtype        in  varchar2,
946   itemkey         in  varchar2,
947   actid           in number,
948   funcmode        in  varchar2,
949   resultout          out NOCOPY varchar2    ) is
950 
951   l_asl_id number;
952 begin
953   if (funcmode <> wf_engine.eng_run) then
954       resultout := wf_engine.eng_null;
955       return;
956   end if;
957 
958   l_asl_id:=wf_engine.GetItemAttrNumber ( itemtype => itemtype,
959                                           itemkey  => itemkey,
960                                           aname    => 'ASL_ID');
961 
962   update POS_MFG_CAPACITY_TEMP
963      set status='REJ'
964    where asl_id=l_asl_id
965          and status in ('NEW', 'OLD', 'DEL', 'MOD');
966 
967   update POS_CAPACITY_TOLERANCE_TEMP
968      set status='REJ'
969    where asl_id=l_asl_id
970          and status='NEW';
971 
972 end;
973 
974 procedure BUYER_SAME_AS_PLANNER(  itemtype        in  varchar2,
975   itemkey         in  varchar2,
976   actid           in number,
977   funcmode        in  varchar2,
978   resultout          out NOCOPY varchar2    ) is
979 
980   l_buyer_name varchar2(100);
981   l_planner_name varchar2(100);
982 begin
983   if (funcmode <> wf_engine.eng_run) then
984       resultout := wf_engine.eng_null;
985       return;
986   end if;
987   l_buyer_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
988                                           itemkey  => itemkey,
989                                           aname    => 'BUYER_NAME');
990   l_planner_name:=wf_engine.GetItemAttrText ( itemtype => itemtype,
991                                           itemkey  => itemkey,
992                                           aname    => 'PLANNER_NAME');
993 
994   if(l_planner_name is not null) then
995     if(l_planner_name=l_buyer_name) then
996       resultout := wf_engine.eng_completed || ':' || 'Y';
997     else
998       resultout := wf_engine.eng_completed || ':' || 'N';
999     end if;
1000   else
1001     resultout := wf_engine.eng_completed || ':' || 'Y';
1002   end if;
1003 end;
1004 
1005 
1006 PROCEDURE GENERATE_CAP_APP_NOTIF(document_id in  varchar2,
1007 			    display_type   in      varchar2,
1008 			    document in OUT NOCOPY varchar2,
1009 			    document_type  in OUT NOCOPY  varchar2)
1010 IS
1011 
1012 NL              VARCHAR2(1) := fnd_global.newline;
1013 l_document      VARCHAR2(32000) := '';
1014 
1015 x_old_capacity_tolerance varchar2(32000);
1016 x_new_capacity_tolerance varchar2(32000);
1017 x_new_mfg_capacity_table varchar2(32000);
1018 x_old_mfg_capacity_table varchar2(32000);
1019 
1020 
1021 l_item_type varchar2(300) := '';
1022 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
1023 
1024 l_base_href       VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1025 
1026 BEGIN
1027 
1028  l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1029  l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
1030 
1031 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
1032 
1033 generate_header(l_document,l_item_type,l_item_key);
1034 
1035 
1036   x_old_capacity_tolerance := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1037                                   itemkey  => l_item_key,
1038                                   aname    => 'OLD_CAPACITY_TOLERANCE_TABLE');
1039 
1040   x_new_capacity_tolerance := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1041                                   itemkey  => l_item_key,
1042                                   aname    => 'NEW_CAPACITY_TOLERANCE_TABLE');
1043 
1044   x_new_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1045                                   itemkey  => l_item_key,
1046                                   aname    => 'NEW_MFG_CAPACITY_TABLE');
1047 
1048   x_old_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1049                                   itemkey  => l_item_key,
1050                                   aname    => 'OLD_MFG_CAPACITY_TABLE');
1051 
1052 
1053 
1054 
1055 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_MANUF_CAPACITY') || '</B></font><HR> ' || NL;
1056 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1057 l_document := l_document || '<tr>' || NL;
1058 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_PREVIOUS_VALUES') || '</TH>' || NL;
1059 
1060 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_REQUESTED_UPDATES') || '</TH>' || NL;
1061 l_document := l_document || '</tr>' || NL;
1062 l_document := l_document || '<tr><td valign=top>' || X_OLD_MFG_CAPACITY_TABLE || '</td><td valign=top>' || X_NEW_MFG_CAPACITY_TABLE || '</td>' || NL;
1063 l_document := l_document || '</tr> ' || NL;
1064 l_document := l_document || '</table>' || NL;
1065 
1066 l_document := l_document || '<br>' || NL;
1067 
1068 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_CAPACITY_TOLERANCE') || '</B></font><HR> ' || NL;
1069 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1070 l_document := l_document || '<tr>' || NL;
1071 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_PREVIOUS_VALUES') || '</TH>' || NL;
1072 
1073 l_document := l_document || '<TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_REQUESTED_UPDATES') || '</TH>' || NL;
1074 l_document := l_document || '</tr>' || NL;
1075 l_document := l_document || '<tr><td valign=top>' || X_OLD_CAPACITY_TOLERANCE|| '</td><td valign=top>' || X_NEW_CAPACITY_TOLERANCE || '</td>' || NL;
1076 l_document := l_document || '</tr> ' || NL;
1077 l_document := l_document || '</table>' || NL;
1078 
1079 l_document := l_document || '</td> </tr> </table>' || NL;
1080 
1081 
1082 document := l_document;
1083 
1084 EXCEPTION
1085   WHEN OTHERS  THEN
1086 	NULL;
1087 END;
1088 
1089 
1090 procedure generate_header(document in out nocopy varchar2,
1091 			          itemtype in varchar2,
1092 				  itemkey in varchar2)
1093  is
1094 
1095 NL              VARCHAR2(1) := fnd_global.newline;
1096 l_document      VARCHAR2(32000) := '';
1097 
1098 x_supp_item varchar2(25);
1099 x_item_num varchar2(25);
1100 x_item_desc varchar2(240);
1101 x_uom varchar2(25);
1102 
1103 begin
1104 
1105   x_item_desc :=  wf_engine.GetItemAttrText ( itemtype => itemtype,
1106                                   itemkey  => itemkey,
1107                                   aname    => 'ITEM_DESCRIPTION');
1108 
1109   x_item_num :=   wf_engine.GetItemAttrText ( itemtype => itemtype,
1110                                   itemkey  => itemkey,
1111                                   aname    => 'ITEM_NUM');
1112 
1113   x_supp_item :=  wf_engine.GetItemAttrText ( itemtype => itemtype,
1114                                   itemkey  => itemkey,
1115                                   aname    => 'SUPPLIER_ITEM');
1116 
1117   x_uom := wf_engine.GetItemAttrText ( itemtype => itemtype,
1118                                   itemkey  => itemkey,
1119                                   aname    => 'PURCHASING_UOM');
1120 
1121 
1122 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;
1123 
1124 l_document := l_document || '<table width=100%><tr><td width=2>' || ' ' || '</td><td>' || NL;
1125 
1126 l_document := l_document || '<table  cellpadding=2 cellspacing=1> ' || NL;
1127 
1128 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;
1129 
1130 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;
1131 
1132 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;
1133 
1134 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;
1135 
1136 
1137 l_document := l_document || '</table>' || NL;
1138 
1139 document := l_document;
1140 
1141 exception
1142 when others then
1143   null;
1144 end;
1145 
1146 
1147 PROCEDURE GENERATE_SUPPL_CAP_NOTIF_APPR(document_id in  varchar2,
1148 			    display_type   in      varchar2,
1149 			    document in OUT NOCOPY varchar2,
1150 			    document_type  in OUT NOCOPY  varchar2)
1151 IS
1152 
1153 NL              VARCHAR2(1) := fnd_global.newline;
1154 l_document      VARCHAR2(32000) := '';
1155 
1156 x_new_capacity_tolerance_table varchar2(32000) := '';
1157 x_new_mfg_capacity_table varchar2(32000) := '';
1158 
1159 l_item_type varchar2(300) := '';
1160 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
1161 
1162 l_base_url       VARCHAR(2000) := '';
1163 
1164 BEGIN
1165 
1166 
1167  l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1168  l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
1169 
1170 
1171 l_base_url := POS_URL_PKG.get_external_url;
1172 
1173 
1174 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_url || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
1175 
1176 generate_header(l_document,l_item_type,l_item_key);
1177 
1178 x_new_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1179                                   itemkey  => l_item_key,
1180                                   aname    => 'NEW_MFG_CAPACITY_TABLE');
1181 
1182 x_new_capacity_tolerance_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1183                                   itemkey  => l_item_key,
1184                                   aname    => 'NEW_CAPACITY_TOLERANCE_TABLE');
1185 
1186 
1187 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;
1188 
1189 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1190 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1191 
1192 l_document := l_document || '<tr><td>' || X_NEW_MFG_CAPACITY_TABLE || '</td></tr></table>' || NL;
1193 
1194 l_document := l_document || '<br>' || NL;
1195 
1196 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_CAPACITY_TOLERANCE') || '</b></font><HR> ' || NL;
1197 
1198 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1199 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1200 
1201 l_document := l_document || '<tr><td>' || X_NEW_CAPACITY_TOLERANCE_TABLE || '</td></tr></table>' || NL;
1202 
1203 
1204 l_document := l_document || '</td></tr></table>'|| NL;
1205 
1206 document := l_document;
1207 
1208 
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211  NULL;
1212 END;
1213 
1214 
1215 PROCEDURE GENERATE_SUPPL_CAP_NOTIF_REJ(document_id in  varchar2,
1216 			    display_type   in      varchar2,
1217 			    document in OUT NOCOPY varchar2,
1218 			    document_type  in OUT NOCOPY  varchar2)
1219 IS
1220 
1221 NL              VARCHAR2(1) := fnd_global.newline;
1222 l_document      VARCHAR2(32000) := '';
1223 
1224 x_new_capacity_tolerance_table varchar2(32000) := '';
1225 x_new_mfg_capacity_table varchar2(32000) := '';
1226 
1227 l_item_type varchar2(300) := '';
1228 l_item_key WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE := '';
1229 
1230 l_base_url       VARCHAR(2000) := '';
1231 
1232 BEGIN
1233 
1234 
1235 l_base_url := POS_URL_PKG.get_external_url;
1236 
1237 
1238  l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1239  l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
1240 
1241 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_url || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
1242 
1243 generate_header(l_document,l_item_type,l_item_key);
1244 
1245 x_new_mfg_capacity_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1246                                   itemkey  => l_item_key,
1247                                   aname    => 'NEW_MFG_CAPACITY_TABLE');
1248 
1249 x_new_capacity_tolerance_table := wf_engine.GetItemAttrText ( itemtype => l_item_type,
1250                                   itemkey  => l_item_key,
1251                                   aname    => 'NEW_CAPACITY_TOLERANCE_TABLE');
1252 
1253 
1254 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;
1255 
1256 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1257 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1258 
1259 l_document := l_document || '<tr><td>' || X_NEW_MFG_CAPACITY_TABLE || '</td></tr></table>' || NL;
1260 
1261 l_document := l_document || '<br>' || NL;
1262 
1263 l_document := l_document || '<font size=3 color=#336699 face=arial><b>' || fnd_message.get_string('POS','POS_CAPACITY_TOLERANCE') || '</b></font><HR> ' || NL;
1264 
1265 l_document := l_document || '<table width=100% cellpadding=2 cellspacing=1>' || NL;
1266 l_document := l_document || '<tr> <TH align=left><font color=#336699>' || fnd_message.get_string('POS','POS_VALUES') || '</TH></tr>' || NL;
1267 
1268 l_document := l_document || '<tr><td>' || X_NEW_CAPACITY_TOLERANCE_TABLE || '</td></tr></table>' || NL;
1269 
1270 
1271 l_document := l_document || '</td></tr></table>'|| NL;
1272 
1273 document := l_document;
1274 
1275 
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278  NULL;
1279 END;
1280 
1281 END POS_UPDATE_CAPACITY_PKG;
1282