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.PO_WF_REQ_NOTIFICATION_R11

Source


1 PACKAGE BODY PO_WF_REQ_NOTIFICATION_R11 AS
2 /* $Header: POXWPA8B.pls 120.0 2005/06/01 14:15:15 appldev noship $ */
3 
4 TYPE line_record IS RECORD (
5 
6   line_num         po_requisition_lines.line_num%TYPE,
7   item_num         mtl_system_items_kfv.concatenated_segments%TYPE,
8   item_revision    po_requisition_lines.item_revision%TYPE,
9   item_desc        po_requisition_lines.item_description%TYPE,
10   uom              po_requisition_lines.unit_meas_lookup_code%TYPE,
11   quantity         po_requisition_lines.quantity%TYPE,
12   unit_price       po_requisition_lines.unit_price%TYPE,
13   line_amount      NUMBER,
14   need_by_date     po_requisition_lines.need_by_date%TYPE,
15   location         hr_locations.location_code%TYPE,
16   requestor        per_people_f.full_name%TYPE,
17   sugg_supplier    po_requisition_lines.suggested_vendor_name%TYPE,
18   sugg_site        po_requisition_lines.suggested_vendor_location%TYPE);
19 
20 TYPE history_record IS RECORD (
21 
22   seq_num          po_action_history_v.sequence_num%TYPE,
23   employee_name    po_action_history_v.employee_name%TYPE,
24   action           po_action_history_v.action_code_dsp%TYPE,
25   action_date      po_action_history_v.action_date%TYPE,
26   note             po_action_history_v.note%TYPE,
27   revision         po_action_history_v.object_revision_num%TYPE);
28 
29 
30 PROCEDURE get_po_req_approve_msg(document_id	in	varchar2,
31                                  display_type	in	varchar2,
32                                  document	in out	NOCOPY varchar2,
33                                  document_type	in out	NOCOPY varchar2) IS
34 
35   l_item_type    wf_items.item_type%TYPE;
36   l_item_key     wf_items.item_key%TYPE;
37 
38   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
39   l_org_id           po_requisition_headers.org_id%TYPE;
40   l_document_subtype po_lookup_codes.displayed_field%TYPE;
41   l_document_type    po_lookup_codes.displayed_field%TYPE;
42   l_document_number  po_requisition_headers.segment1%TYPE;
43   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
44   l_header_msg       VARCHAR2(200);
45   l_req_amount       VARCHAR2(30);
46   l_description      po_requisition_headers.description%TYPE;
47   l_approver         per_people_f.full_name%TYPE;
48   l_preparer         per_people_f.full_name%TYPE;
49   l_note             VARCHAR2(240);
50 
51   l_document         VARCHAR2(32000) := '';
52 
53   l_document_1         VARCHAR2(32000) := '';
54   l_document_2         VARCHAR2(32000) := '';
55 
56   NL1                 VARCHAR2(1) := PO_WF_REQ_NOTIFICATION_R11.newline;
57   NL                 VARCHAR2(1) := '';
58 
59 BEGIN
60 
61 
62   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
63   l_item_key := substr(document_id, instr(document_id, ':') + 1,
64                        length(document_id) - 2);
65 
66   l_document_id := wf_engine.GetItemAttrNumber
67                                         (itemtype   => l_item_type,
68                                          itemkey    => l_item_key,
69                                          aname      => 'DOCUMENT_ID');
70 
71   l_org_id := wf_engine.GetItemAttrNumber
72                                         (itemtype   => l_item_type,
73                                          itemkey    => l_item_key,
74                                          aname      => 'ORG_ID');
75 
76   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;       -- <R12.MOAC>
77 
78   l_document_subtype := wf_engine.GetItemAttrText
79                                         (itemtype   => l_item_type,
80                                          itemkey    => l_item_key,
81                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
82 
83   l_document_type := wf_engine.GetItemAttrText
84                                         (itemtype   => l_item_type,
85                                          itemkey    => l_item_key,
86                                          aname      => 'DOCUMENT_TYPE_DISP');
87 
88   l_document_number := wf_engine.GetItemAttrText
89                                         (itemtype   => l_item_type,
90                                          itemkey    => l_item_key,
91                                          aname      => 'DOCUMENT_NUMBER');
92 
93   l_currency_code := wf_engine.GetItemAttrText
94                                         (itemtype   => l_item_type,
95                                          itemkey    => l_item_key,
96                                          aname      => 'FUNCTIONAL_CURRENCY');
97 
98   l_req_amount := wf_engine.GetItemAttrText
99                                         (itemtype   => l_item_type,
100                                          itemkey    => l_item_key,
101                                          aname      => 'REQ_AMOUNT_DSP');
102 
103   l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVED');
104 
105 
106   l_description := wf_engine.GetItemAttrText
107                                         (itemtype   => l_item_type,
108                                          itemkey    => l_item_key,
109                                          aname      => 'REQ_DESCRIPTION');
110 
111   l_approver  := wf_engine.GetItemAttrText
112                                         (itemtype   => l_item_type,
113                                          itemkey    => l_item_key,
114                                          aname      => 'APPROVER_DISPLAY_NAME');
115 
116   l_preparer := wf_engine.GetItemAttrText
117                                         (itemtype   => l_item_type,
118                                          itemkey    => l_item_key,
119                                          aname      => 'PREPARER_DISPLAY_NAME');
120 
121   l_note := wf_engine.GetItemAttrText
122                                         (itemtype   => l_item_type,
123                                          itemkey    => l_item_key,
124                                          aname      => 'NOTE');
125 
126 /*
127   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
128   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
129   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
130   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
131   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_req_amount);
132 */
133 
134   if (display_type = 'text/html') then
135 
136 --    l_document := '<LINK REL=STYLESHEET HREF=/OA_HTML/PORSTYL2.css TYPE=text/css>';
137 
138     l_document := l_document || '<TABLE width="90%" border=0 cellpadding=0 cellspacing=0><TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') || '  </TD>' || NL;
139 
140     l_document := l_document || '<TD class="fielddatabold" align=left nowrap>' || '(' || l_currency_code || ')' || ' ' || l_req_amount || '</TD></TR>' || NL;
141 
142     l_document := l_document || '<TR><TD colspan=2 height=5 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
143 
144       l_document := l_document || '<P>' || NL;
145       l_document := l_document || '<TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || '  </TD>' || NL || '<BR>';
146       l_document := l_document || '<TD class="fielddatabold" align=left nowrap>' || l_description || '</TD>';
147 
148       l_document := l_document || '<BR></TR></P>' || NL;
149 
150     l_document := l_document || '<TR><TD colspan=2 height=5 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
151 
152     l_document := l_document || '<P><TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
153                   '  </TD>' || NL;
154     l_document := l_document || '<TD class="fielddatabold" align=left nowrap>' || l_preparer || '</TD></TR></P>' || NL;
155 
156     l_document := l_document || '<TR><TD colspan=2 height=20 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
157 
158       l_document := l_document || '<P><TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') ||  '  </TD>' || NL;
159       l_document := l_document || '<TD class="fielddatabold" align=left>' || l_note || '</TD></TR></P>' || NL;
160 
161     l_document := l_document || '<TR><TD colspan=2 height=20 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
162 
163   else
164 
165     l_document := NL1 ||
166          fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DTLS_DESP') ||
167           NL1 || NL1;
168 /*
169     l_document := document || l_document || l_header_msg || NL1 || NL1;
170 
171     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
172     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL1;
173 
174     if l_description is not null then
175       l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL1;
176       l_document := l_document || l_description || NL1;
177     end if;
178 
179     l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVER');
180     l_document := l_document || ' ' || l_approver || NL1;
181 
182     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
183     l_document := l_document || ' ' || l_preparer || NL1;
184 
185     if l_note is not null then
186       l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL1;
187       l_document := l_document || l_note || NL1;
188     end if;
189 
190 */
191   end if;
192 
193 
194   l_document_2 := NULL;
195 
196   get_req_lines_details_link(document_id, display_type, l_document_2, document_type);
197 
198   l_document_1 := NULL;
199 
200   get_action_history(document_id, display_type, l_document_1, document_type);
201 
202   document := l_document || l_document_1 || l_document_2;
203 
204 END get_po_req_approve_msg;
205 
206 PROCEDURE get_po_req_approved_msg(document_id	in	varchar2,
207                                  display_type	in	varchar2,
208                                  document	in out	NOCOPY varchar2,
209                                  document_type	in out	NOCOPY varchar2) IS
210 
211   l_item_type    wf_items.item_type%TYPE;
212   l_item_key     wf_items.item_key%TYPE;
213 
214   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
215   l_org_id           po_requisition_headers.org_id%TYPE;
216   l_document_subtype po_lookup_codes.displayed_field%TYPE;
217   l_document_type    po_lookup_codes.displayed_field%TYPE;
218   l_document_number  po_requisition_headers.segment1%TYPE;
219   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
220   l_header_msg       VARCHAR2(200);
221   l_req_amount       VARCHAR2(30);
222   l_description      po_requisition_headers.description%TYPE;
223   l_approver         per_people_f.full_name%TYPE;
224   l_preparer         per_people_f.full_name%TYPE;
225   l_note             VARCHAR2(240);
226 
227   l_document         VARCHAR2(32000) := '';
228 
229   l_document_1         VARCHAR2(32000) := '';
230   l_document_2         VARCHAR2(32000) := '';
231 
232   NL1                 VARCHAR2(1) := PO_WF_REQ_NOTIFICATION_R11.newline;
233   NL                 VARCHAR2(1) := '';
234 
235 BEGIN
236 
237 
238 
239   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
240   l_item_key := substr(document_id, instr(document_id, ':') + 1,
241                        length(document_id) - 2);
242 
243   l_document_id := wf_engine.GetItemAttrNumber
244                                         (itemtype   => l_item_type,
245                                          itemkey    => l_item_key,
246                                          aname      => 'DOCUMENT_ID');
247 
248   l_org_id := wf_engine.GetItemAttrNumber
249                                         (itemtype   => l_item_type,
250                                          itemkey    => l_item_key,
251                                          aname      => 'ORG_ID');
252 
253   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;       -- <R12.MOAC>
254 
255   l_document_subtype := wf_engine.GetItemAttrText
256                                         (itemtype   => l_item_type,
257                                          itemkey    => l_item_key,
258                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
259 
260   l_document_type := wf_engine.GetItemAttrText
261                                         (itemtype   => l_item_type,
262                                          itemkey    => l_item_key,
263                                          aname      => 'DOCUMENT_TYPE_DISP');
264 
265   l_document_number := wf_engine.GetItemAttrText
266                                         (itemtype   => l_item_type,
267                                          itemkey    => l_item_key,
268                                          aname      => 'DOCUMENT_NUMBER');
269 
270   l_currency_code := wf_engine.GetItemAttrText
271                                         (itemtype   => l_item_type,
272                                          itemkey    => l_item_key,
273                                          aname      => 'FUNCTIONAL_CURRENCY');
274 
275   l_req_amount := wf_engine.GetItemAttrText
276                                         (itemtype   => l_item_type,
277                                          itemkey    => l_item_key,
278                                          aname      => 'REQ_AMOUNT_DSP');
279 
280   l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVED');
281 
282 
283   l_description := wf_engine.GetItemAttrText
284                                         (itemtype   => l_item_type,
285                                          itemkey    => l_item_key,
286                                          aname      => 'REQ_DESCRIPTION');
287 
288   l_approver  := wf_engine.GetItemAttrText
289                                         (itemtype   => l_item_type,
290                                          itemkey    => l_item_key,
291                                          aname      => 'APPROVER_DISPLAY_NAME');
292 
293   l_preparer := wf_engine.GetItemAttrText
294                                         (itemtype   => l_item_type,
295                                          itemkey    => l_item_key,
296                                          aname      => 'PREPARER_DISPLAY_NAME');
297 
298   l_note := wf_engine.GetItemAttrText
299                                         (itemtype   => l_item_type,
300                                          itemkey    => l_item_key,
301                                          aname      => 'NOTE');
302 
303 /*
304   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
305   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
306   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
307   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
308   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_req_amount);
309 */
310 
311   if (display_type = 'text/html') then
312 
313 --    l_document := '<LINK REL=STYLESHEET HREF=/OA_HTML/PORSTYL2.css TYPE=text/css>';
314 
315     l_document := l_document || '<TABLE width="100%" border=0 cellpadding=0 cellspacing=0 align=left><TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') || '  </TD>' || NL;
316 
317     l_document := l_document || '<TD class="fielddatabold" align=left nowrap>' || '(' || l_currency_code || ')' || ' ' || l_req_amount || '</TD></TR>' || NL;
318 
319      l_document := l_document || '<TR><TD colspan=2 height=5 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
320 
321       l_document := l_document || '<P>' || NL;
322       l_document := l_document || '<TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || '  </TD>' || NL || '<BR>';
323       l_document := l_document || '<TD class="fielddatabold" align=left nowrap>' || l_description || '</TD>';
324       l_document := l_document || '<BR></TR></P>' || NL;
325 
326      l_document := l_document || '<TR><TD colspan=2 height=5 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
327 
328     l_document := l_document || '<P><TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
329                   '  </TD>' || NL;
330     l_document := l_document || '<TD class="fielddatabold" align=left nowrap>' || l_preparer || '</TD></TR></P>' || NL;
331 
332     l_document := l_document || '<TR><TD colspan=2 height=20 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
333 
334       l_document := l_document || '<P><TR><TD class="fieldtitle" align=right nowrap>' || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') ||  '  </TD>' || NL;
335       l_document := l_document || '<TD class="fielddatabold" align=left>' || l_note || '</TD></TR></P>' || NL;
336 
337    l_document := l_document || '<TR><TD colspan=2 height=20 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
338 
339 
340   else
341 
342 /*
343     l_document := NL1 || 'The first five requisition lines are summarized ' ||
344                   'below. For additional information, please go to the URL ' ||
345                   'specified next to Requisition Details. ' ||  NL1 || NL1;
346 */
347 
348     l_document := NL1 ||
349          fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DTLS_DESP') ||
350           NL1 || NL1;
351 /*
352     l_document := document || l_document || l_header_msg || NL1 || NL1;
353 
354     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
355     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL1;
356 
357     if l_description is not null then
358       l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL1;
359       l_document := l_document || l_description || NL1;
360     end if;
361 
362     l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVER');
363     l_document := l_document || ' ' || l_approver || NL1;
364 
365     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
366     l_document := l_document || ' ' || l_preparer || NL1;
367 
368     if l_note is not null then
369       l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL1;
370       l_document := l_document || l_note || NL1;
371     end if;
372 
373  */
374   end if;
375 
376   l_document_2 := NULL;
377 
378   get_req_lines_details(document_id, display_type, l_document_2, document_type);
379 
380   l_document_1 := NULL;
381 
382   get_action_history(document_id, display_type, l_document_1, document_type);
383 
384   document := l_document || l_document_1 || l_document_2;
385 
386 END get_po_req_approved_msg;
387 
388 PROCEDURE get_po_req_no_approver_msg(document_id	in	varchar2,
389                                  display_type	in	varchar2,
390                                  document	in out	NOCOPY varchar2,
391                                  document_type	in out	NOCOPY varchar2) IS
392 
393   l_item_type    wf_items.item_type%TYPE;
394   l_item_key     wf_items.item_key%TYPE;
395 
396   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
397   l_org_id           po_requisition_headers.org_id%TYPE;
398   l_document_subtype po_lookup_codes.displayed_field%TYPE;
399   l_document_type    po_lookup_codes.displayed_field%TYPE;
400   l_document_number  po_requisition_headers.segment1%TYPE;
401   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
402   l_req_amount     VARCHAR2(30);
403   l_header_msg       VARCHAR2(200);
404   l_description      po_requisition_headers.description%TYPE;
405   l_approver         per_people_f.full_name%TYPE;
406   l_preparer         per_people_f.full_name%TYPE;
407   l_note             VARCHAR2(240);
408 
409   l_document         VARCHAR2(32000) := '';
410 
411   l_document_1         VARCHAR2(32000) := '';
412   l_document_2         VARCHAR2(32000) := '';
413 
414   NL                 VARCHAR2(1) := PO_WF_REQ_NOTIFICATION_R11.newline;
415 
416 BEGIN
417 
418   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
419   l_item_key := substr(document_id, instr(document_id, ':') + 1,
420                        length(document_id) - 2);
421 
422   l_document_id := wf_engine.GetItemAttrNumber
423                                         (itemtype   => l_item_type,
424                                          itemkey    => l_item_key,
425                                          aname      => 'DOCUMENT_ID');
426 
427   l_org_id := wf_engine.GetItemAttrNumber
428                                         (itemtype   => l_item_type,
429                                          itemkey    => l_item_key,
430                                          aname      => 'ORG_ID');
431 
432   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;       -- <R12.MOAC>
433 
434   l_document_subtype := wf_engine.GetItemAttrText
435                                         (itemtype   => l_item_type,
436                                          itemkey    => l_item_key,
437                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
438 
439   l_document_type := wf_engine.GetItemAttrText
440                                         (itemtype   => l_item_type,
441                                          itemkey    => l_item_key,
442                                          aname      => 'DOCUMENT_TYPE_DISP');
443 
444   l_document_number := wf_engine.GetItemAttrText
445                                         (itemtype   => l_item_type,
446                                          itemkey    => l_item_key,
447                                          aname      => 'DOCUMENT_NUMBER');
448 
449   l_currency_code := wf_engine.GetItemAttrText
450                                         (itemtype   => l_item_type,
451                                          itemkey    => l_item_key,
452                                          aname      => 'FUNCTIONAL_CURRENCY');
453 
454   l_req_amount := wf_engine.GetItemAttrText
455                                         (itemtype   => l_item_type,
456                                          itemkey    => l_item_key,
457                                          aname      => 'REQ_AMOUNT_DSP');
458 
459   l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_NO_APPROVER');
460 
461   l_req_amount := wf_engine.GetItemAttrText
462                                         (itemtype   => l_item_type,
463                                          itemkey    => l_item_key,
464                                          aname      => 'REQ_AMOUNT_DSP');
465 
466   l_description := wf_engine.GetItemAttrText
467                                         (itemtype   => l_item_type,
468                                          itemkey    => l_item_key,
469                                          aname      => 'REQ_DESCRIPTION');
470 
471   l_approver := wf_engine.GetItemAttrText
472                                         (itemtype   => l_item_type,
473                                          itemkey    => l_item_key,
474                                          aname      => 'APPROVER_DISPLAY_NAME');
475 
476   l_preparer := wf_engine.GetItemAttrText
477                                         (itemtype   => l_item_type,
478                                          itemkey    => l_item_key,
479                                          aname      => 'PREPARER_DISPLAY_NAME');
480 
481   l_note := wf_engine.GetItemAttrText
482                                         (itemtype   => l_item_type,
483                                          itemkey    => l_item_key,
484                                          aname      => 'NOTE');
485 
486   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
487   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
488   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
489   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
490   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_req_amount);
491 
492   if (display_type = 'text/html') then
493 
494     l_document := NL || NL || '<!-- PO_REQ_APPROVE_MSG -->'|| NL || NL || '<P>';
495 
496     l_document := l_document || l_header_msg;
497 
498     l_document := l_document || '</P>' || NL;
499 
500     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' || NL ||
501                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') ||
502                   '  </TD>' || NL;
503 
504     l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_req_amount || '</TD></TR>' || NL;
505 
506     if l_description is not null then
507       l_document := l_document || '<P>' || NL;
508       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
509       l_document := l_document || l_description;
510       l_document := l_document || '<BR></P>' || NL;
511     end if;
512 
513     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' ||
514                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LAST_APPROVER') ||
515                   '  </TD>' || NL;
516 
517     l_document := l_document || '<TD align=left>' || l_approver || '</TD></TR>' || NL;
518 
519     l_document := l_document || '<TR><TD align=right>' ||
520                   fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
521                   '  </TD>' || NL;
522 
523     l_document := l_document || '<TD align=left>' || l_preparer || '</TD></TR></TABLE></P>' || NL;
524 
525     if l_note is not null then
526       l_document := l_document || '<P>' || NL;
527       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL || '<BR>' || NL;
528       l_document := l_document || l_note;
529       l_document := l_document || '<BR></P>' || NL;
530     end if;
531 
532   else
533 
534     l_document := l_document || l_header_msg || NL || NL;
535 
536     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
537     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
538 
539     if l_description is not null then
540       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
541       l_document := l_document || l_description || NL;
542     end if;
543 
544     l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_LAST_APPROVER');
545     l_document := l_document || ' ' || l_approver || NL;
546 
547     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
548     l_document := l_document || ' ' || l_preparer || NL;
549 
550     if l_note is not null then
551       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
552       l_document := l_document || l_note || NL;
553     end if;
554 
555   end if;
556 
557   l_document_1 := NULL;
558 
559   get_action_history(document_id, display_type, l_document_1, document_type);
560 
561   l_document_2 := NULL;
562 
563   get_req_lines_details(document_id, display_type, l_document_2, document_type);
564 
565   document := l_document || l_document_1 || l_document_2;
566 
567 END get_po_req_no_approver_msg;
568 
569 PROCEDURE get_po_req_reject_msg(document_id	in	varchar2,
570                                  display_type	in	varchar2,
571                                  document	in out	NOCOPY varchar2,
572                                  document_type	in out	NOCOPY varchar2) IS
573 
574   l_item_type    wf_items.item_type%TYPE;
575   l_item_key     wf_items.item_key%TYPE;
576 
577   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
578   l_org_id           po_requisition_headers.org_id%TYPE;
579   l_document_subtype po_lookup_codes.displayed_field%TYPE;
580   l_document_type    po_lookup_codes.displayed_field%TYPE;
581   l_document_number  po_requisition_headers.segment1%TYPE;
582   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
583   l_req_amount     VARCHAR2(30);
584   l_header_msg       VARCHAR2(200);
585   l_description      po_requisition_headers.description%TYPE;
586   l_rejected_by      per_people_f.full_name%TYPE;
587   l_preparer         per_people_f.full_name%TYPE;
588   l_note             VARCHAR2(240);
589 
590   l_document         VARCHAR2(32000) := '';
591 
592   l_document_1         VARCHAR2(32000) := '';
593   l_document_2         VARCHAR2(32000) := '';
594 
595   NL                 VARCHAR2(1) := PO_WF_REQ_NOTIFICATION_R11.newline;
596 
597 BEGIN
598 
599   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
600   l_item_key := substr(document_id, instr(document_id, ':') + 1,
601                        length(document_id) - 2);
602 
603   l_document_id := wf_engine.GetItemAttrNumber
604                                         (itemtype   => l_item_type,
605                                          itemkey    => l_item_key,
606                                          aname      => 'DOCUMENT_ID');
607 
608   l_org_id := wf_engine.GetItemAttrNumber
609                                         (itemtype   => l_item_type,
610                                          itemkey    => l_item_key,
611                                          aname      => 'ORG_ID');
612 
613   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;       -- <R12.MOAC>
614 
615   l_document_subtype := wf_engine.GetItemAttrText
616                                         (itemtype   => l_item_type,
617                                          itemkey    => l_item_key,
618                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
619 
620   l_document_type := wf_engine.GetItemAttrText
621                                         (itemtype   => l_item_type,
622                                          itemkey    => l_item_key,
623                                          aname      => 'DOCUMENT_TYPE_DISP');
624 
625   l_document_number := wf_engine.GetItemAttrText
626                                         (itemtype   => l_item_type,
627                                          itemkey    => l_item_key,
628                                          aname      => 'DOCUMENT_NUMBER');
629 
630   l_currency_code := wf_engine.GetItemAttrText
631                                         (itemtype   => l_item_type,
632                                          itemkey    => l_item_key,
633                                          aname      => 'FUNCTIONAL_CURRENCY');
634 
635   l_req_amount := wf_engine.GetItemAttrText
636                                         (itemtype   => l_item_type,
637                                          itemkey    => l_item_key,
638                                          aname      => 'REQ_AMOUNT_DSP');
639 
640   l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_REJECTED_WEB');
641 
642   l_req_amount := wf_engine.GetItemAttrText
643                                         (itemtype   => l_item_type,
644                                          itemkey    => l_item_key,
645                                          aname      => 'REQ_AMOUNT_DSP');
646 
647   l_description := wf_engine.GetItemAttrText
648                                         (itemtype   => l_item_type,
649                                          itemkey    => l_item_key,
650                                          aname      => 'REQ_DESCRIPTION');
651 
652   l_rejected_by := wf_engine.GetItemAttrText
653                                         (itemtype   => l_item_type,
654                                          itemkey    => l_item_key,
655                                          aname      => 'APPROVER_DISPLAY_NAME');
656 
657   l_preparer := wf_engine.GetItemAttrText
658                                         (itemtype   => l_item_type,
659                                          itemkey    => l_item_key,
660                                          aname      => 'PREPARER_DISPLAY_NAME');
661 
662   l_note := wf_engine.GetItemAttrText
663                                         (itemtype   => l_item_type,
664                                          itemkey    => l_item_key,
665                                          aname      => 'NOTE');
666 
667   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
668   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
669   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
670   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
671   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_req_amount);
672 
673   if (display_type = 'text/html') then
674 
675     l_document := NL || NL || '<!-- PO_REQ_REJECT_MSG -->'|| NL || NL || '<P>';
676 
677     l_document := l_document || l_header_msg;
678 
679     l_document := l_document || '</P>' || NL;
680 
681     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' || NL ||
682                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') ||
683                   '  </TD>' || NL;
684 
685     l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_req_amount || '</TD></TR>' || NL;
686 
687     if l_description is not null then
688       l_document := l_document || '<P>' || NL;
689       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
690       l_document := l_document || l_description;
691       l_document := l_document || '<BR></P>' || NL;
692     end if;
693 
694     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' ||
695                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REJECTED_BY') ||
696                   '  </TD>' || NL;
697 
698     l_document := l_document || '<TD align=left>' || l_rejected_by || '</TD></TR>' || NL;
699 
700     l_document := l_document || '<TR><TD align=right>' ||
701                   fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
702                   '  </TD>' || NL;
703 
704     l_document := l_document || '<TD align=left>' || l_preparer || '</TD></TR></TABLE></P>' || NL;
705 
706     if l_note is not null then
707       l_document := l_document || '<P>' || NL;
708       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL || '<BR>' || NL;
709       l_document := l_document || l_note;
710       l_document := l_document || '<BR></P>' || NL;
711     end if;
712 
713   else
714 
715     l_document := l_document || l_header_msg || NL || NL;
716 
717     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
718     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
719 
720 
721     if l_description is not null then
722       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
723       l_document := l_document || l_description || NL;
724     end if;
725 
726     l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_REJECTED_BY');
727     l_document := l_document || ' ' || l_rejected_by || NL;
728 
729     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
730     l_document := l_document || ' ' || l_preparer || NL;
731 
732     if l_note is not null then
733       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
734       l_document := l_document || l_note || NL;
735     end if;
736 
737   end if;
738 
739   l_document_2 := NULL;
740 
741   get_req_lines_details(document_id, display_type, l_document_2, document_type);
742 
743   l_document_1 := NULL;
744 
745   get_action_history(document_id, display_type, l_document_1, document_type);
746 
747   document := l_document || l_document_2 || l_document_1;
748 
749 END get_po_req_reject_msg;
750 
751 PROCEDURE get_req_lines_details_link(document_id	in	varchar2,
752                                  display_type	in	varchar2,
753                                  document	in out	NOCOPY varchar2,
754                                  document_type	in out	NOCOPY varchar2) IS
755 
756   l_item_type    wf_items.item_type%TYPE;
757   l_item_key     wf_items.item_key%TYPE;
758 
759   l_document_id      po_requisition_lines.requisition_header_id%TYPE;
760   l_org_id           po_requisition_lines.org_id%TYPE;
761 
762   l_line             line_record;
763 
764   l_document         VARCHAR2(32000) := '';
765   l_req_details_url  VARCHAR2(2000) := '';
766   l_req_line_msg  VARCHAR2(2000) := '';
767   l_req_updates_url  VARCHAR2(2000) := '';
768 
769   l_currency_code    fnd_currencies.currency_code%TYPE;
770 
771   NL                 VARCHAR2(1) := '';
772   NL1                 VARCHAR2(1) := PO_WF_REQ_NOTIFICATION_R11.newline;
773   i      number   := 0;
774 
775 CURSOR line_csr(v_document_id NUMBER) IS
776 SELECT rql.line_num,
777        msi.concatenated_segments,
778        rql.item_revision,
779        rql.item_description,
780        rql.unit_meas_lookup_code,
781        rql.quantity,
782        rql.unit_price,
783        rql.quantity * rql.unit_price,
784        rql.need_by_date,
785        hrt.location_code,
786        per.full_name,
787        rql.suggested_vendor_name,
788        rql.suggested_vendor_location
789   FROM po_requisition_lines   rql,
790        mtl_system_items_kfv   msi,
791        hr_locations	      hrt,
792        per_people_f           per
793  WHERE rql.requisition_header_id = v_document_id
794    AND NVL(rql.cancel_flag,'N') = 'N'
795    AND hrt.location_id (+) = rql.deliver_to_location_id
796    AND rql.item_id is not null
797    AND rql.item_id = msi.inventory_item_id
798    AND nvl(msi.organization_id, rql.destination_organization_id) =
799        rql.destination_organization_id
800    AND rql.to_person_id = per.person_id(+)
801    AND per.effective_start_date(+) <= trunc(sysdate)
802    AND per.effective_end_date(+) >= trunc(sysdate)
803  UNION
804   SELECT rql.line_num,
805        NULL,
806        rql.item_revision,
807        rql.item_description,
808        rql.unit_meas_lookup_code,
809        rql.quantity,
810        rql.unit_price,
811        rql.quantity * rql.unit_price,
812        rql.need_by_date,
813        hrt.location_code,
814        per.full_name,
815        rql.suggested_vendor_name,
816        rql.suggested_vendor_location
817   FROM po_requisition_lines   rql,
818        hr_locations       hrt,
819        per_people_f           per
820  WHERE rql.requisition_header_id = v_document_id
821    AND NVL(rql.cancel_flag,'N') = 'N'
822    AND hrt.location_id (+) = rql.deliver_to_location_id
823    AND rql.item_id is NULL
824    AND rql.to_person_id = per.person_id(+)
825    AND per.effective_start_date(+) <= trunc(sysdate)
826    AND per.effective_end_date(+) >= trunc(sysdate)
827  ORDER BY 1;
828 
829 BEGIN
830 
831   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
832   l_item_key := substr(document_id, instr(document_id, ':') + 1,
833                        length(document_id) - 2);
834 
835   l_document_id := wf_engine.GetItemAttrNumber
836                                         (itemtype   => l_item_type,
837                                          itemkey    => l_item_key,
838                                          aname      => 'DOCUMENT_ID');
839 
840   l_org_id := wf_engine.GetItemAttrNumber
841                                         (itemtype   => l_item_type,
842                                          itemkey    => l_item_key,
843                                          aname      => 'ORG_ID');
844 
845   l_req_details_url := wf_engine.GetItemAttrText
846                                         (itemtype   => l_item_type,
847                                          itemkey    => l_item_key,
848                                          aname      => 'REQ_URL');
849 
850   l_req_updates_url := wf_engine.GetItemAttrText
851                                         (itemtype   => l_item_type,
852                                          itemkey    => l_item_key,
853                                          aname      => 'REQ_UPDATE_URL');
854 
855   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;       -- <R12.MOAC>
856 
857   l_currency_code := PO_CORE_S2.get_base_currency;
858 
859   if (display_type = 'text/html') then
860 
861 --    l_document := '<LINK REL=STYLESHEET HREF=/OA_HTML/PORSTYL2.css TYPE=text/css>';
862     l_document := l_document || NL || NL || '<!-- REQ_LINE_DETAILS -->'|| NL || NL || '<P>';
863 
864 	l_document := l_document ||'<TR><TD></TD>'||NL;
865     l_document := l_document || '<TD class=subheader1>'|| fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS');
866     l_document := l_document || '</TD></TR>';
867 
868 	/* horizontal line */
869   l_document := l_document || '<TR><TD></TD>' || NL;
870     l_document := l_document || '<TD colspan=2 height=1 bgcolor=#cccc99><img src=/OA_MEDIA/FNDITPNT.gif></TD></TR>';
871 
872      l_document := l_document || '<TR><TD colspan=2 height=15 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
873 
874 	/* Message about five req lines and link */
875     l_document := l_document || '<TR><TD></TD>'||NL;
876 
877 	/* View req details link */
878     l_req_line_msg := '<TD class=instructiontext>'
879      ||fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS_DESP') || ' '
880      || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS_DESP1');
881 
882     l_req_details_url := '<a href="'|| l_req_details_url || '">' ||
883 			   fnd_message.get_string('PO', 'PO_WF_NOTIF_VIEW_REQ_URL') || '</a>';
884 /*    l_req_line_msg := replace(l_req_line_msg, '&REQ_DTL_LINK', l_req_details_url);
885 */
886 
887 
888 	l_document := l_document || l_req_line_msg || NL ;
889 
890     l_req_updates_url := '<a href="'|| l_req_updates_url || '">' ||
891 			   fnd_message.get_string('PO', 'PO_WF_NOTIF_EDIT_REQ_URL') || '</a>';
892 
893     l_document := l_document || '</TD>' || NL;
894     l_document := l_document || '<TR><TD colspan=2 height=20 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
895 
896     l_document := l_document || '<TR><TD></TD>'||NL;
897     l_document := l_document || '<TD>'||l_req_details_url ;
898     l_document := l_document || ' ' || '|' || ' ' || l_req_updates_url;
899     l_document := l_document || '</TD></TR>' || NL;
900 
901      l_document := l_document || '<TR><TD colspan=2 height=15 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
902 
903     l_document := l_document || '<TR><TD></TD>';
904 
905     l_document := l_document || '<TD align=left><TABLE border=0 width=100% cellpadding=5 cellspacing=1>';
906 
907     l_document := l_document || '<TR>' || NL;
908 
909     l_document := l_document || '<TD class="tableheader" width=3% nowrap>' ||
910                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TD>' || NL;
911 
912     l_document := l_document || '<TD class="tableheader" width=30% nowrap>' ||
913                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TD>' || NL;
914 
915     l_document := l_document || '<TD class="tableheader" width=5%>' ||
916                   fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TD>' || NL;
917 
918     l_document := l_document || '<TD class="tableheader" width=8%>' ||
919                   fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TD>' || NL;
920 
921     l_document := l_document || '<TD class="tableheader" width=10%>' ||
922                   fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TD>' || NL;
923 
924     l_document := l_document || '<TD class="tableheader" width=20%>' ||
925                   fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER') || '</TD>' || NL;
926 
927     l_document := l_document || '<TD class="tableheader" width=10% nowrap>' ||
928                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') ||
929              ' (' || l_currency_code || ')' || '</TD>' || NL;
930 
931     l_document := l_document || '</TR>' || NL;
932 
933     open line_csr(l_document_id);
934 
935     loop
936 
937       fetch line_csr into l_line;
938       i := i + 1;
939 
940       exit when line_csr%notfound;
941 
942       l_document := l_document || '<TR>' || NL;
943 
944       l_document := l_document || '<TD class=tabledata width=3% nowrap align=left>' || nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
945       l_document := l_document || '<TD class=tabledata width=30% align=left>' || nvl(l_line.item_desc, ' ') || '</TD>' || NL;
946       l_document := l_document || '<TD class=tabledata nowrap width=5% align=left>' || nvl(l_line.uom, ' ') || '</TD>' || NL;
947       l_document := l_document || '<TD class=tabledata nowrap width=8% align=left>' || nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
948 
949 /*
950       l_document := l_document || '<TD class=tabledata nowrap align=left>' ||
951                TO_CHAR(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(
952                                    l_currency_code, 30)) || '</TD>' || NL;
953 */
954       l_document := l_document || '<TD class=tabledata nowrap width=10% align=left>' ||
955                TO_CHAR(l_line.unit_price) || '</TD>' || NL;
956 
957 
958       l_document := l_document || '<TD class=tabledata width=20% align=left>' || nvl(l_line.sugg_supplier, ' ') || '</TD>' || NL;
959 
960       l_document := l_document || '<TD class=tabledata nowrap width=10% align=right>' || TO_CHAR(l_line.line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || '</TD>' || NL;
961 
962       l_document := l_document || '</TR>' || NL;
963 
964       exit when i = 5;
965     end loop;
966 
967     close line_csr;
968 
969     l_document := l_document || '</TABLE></P></TD></TR></TABLE></P></TD></TR></P></TABLE>' || NL;
970 
971   else
972 
973     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS') || NL1 || NL1;
974 
975     open line_csr(l_document_id);
976 
977     loop
978 
979       fetch line_csr into l_line;
980       i := i + 1;
981 
982       exit when line_csr%notfound;
983 
984       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ':' || to_char(l_line.line_num) || NL1;
985       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || ': ' || l_line.item_num || NL1;
986       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || ': ' || l_line.item_revision || NL1;
987       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || ': ' || l_line.item_desc || NL1;
988       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' || l_line.uom || NL1;
989       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' || to_char(l_line.quantity) || NL1;
990       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
991 					|| to_char(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL1;
992       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || ': '
993 					|| to_char(l_line.line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL1 || NL1;
994 
995       exit when i = 5;
996       end loop;
997 
998     l_req_details_url := substr(l_req_details_url,
999          instr(l_req_details_url,'''',1,1)+1,
1000          instr(l_req_details_url,'''',1,2)- instr(l_req_details_url,'''',1)-1);
1001 
1002     l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_DTLS') || ': ' ||
1003                   l_req_details_url || NL1;
1004 
1005     l_req_updates_url := substr(l_req_updates_url,
1006          instr(l_req_updates_url,'''',1,1)+1,
1007          instr(l_req_updates_url,'''',1,2)- instr(l_req_updates_url,'''',1)-1);
1008 
1009     l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_EDIT_REQ') || ': ' ||
1010                   l_req_updates_url || NL1;
1011 
1012   end if;
1013 
1014   document := l_document;
1015 
1016 END get_req_lines_details_link;
1017 
1018 
1019 PROCEDURE get_req_lines_details(document_id	in	varchar2,
1020                                  display_type	in	varchar2,
1021                                  document	in out	NOCOPY varchar2,
1022                                  document_type	in out	NOCOPY varchar2) IS
1023 
1024   l_item_type    wf_items.item_type%TYPE;
1025   l_item_key     wf_items.item_key%TYPE;
1026 
1027   l_document_id      po_requisition_lines.requisition_header_id%TYPE;
1028   l_org_id           po_requisition_lines.org_id%TYPE;
1029 
1030   l_line             line_record;
1031 
1032   l_document         VARCHAR2(32000) := '';
1033   l_req_details_url  VARCHAR2(2000) := '';
1034   l_req_line_msg  VARCHAR2(2000) := '';
1035 
1036   l_currency_code    fnd_currencies.currency_code%TYPE;
1037 
1038   NL                 VARCHAR2(1) := '';
1039   NL1                 VARCHAR2(1) := PO_WF_REQ_NOTIFICATION_R11.newline;
1040   i               number := 0;
1041 
1042 
1043 CURSOR line_csr(v_document_id NUMBER) IS
1044 SELECT rql.line_num,
1045        msi.concatenated_segments,
1046        rql.item_revision,
1047        rql.item_description,
1048        rql.unit_meas_lookup_code,
1049        rql.quantity,
1050        rql.unit_price,
1051        rql.quantity * rql.unit_price,
1052        rql.need_by_date,
1053        hrt.location_code,
1054        per.full_name,
1055        rql.suggested_vendor_name,
1056        rql.suggested_vendor_location
1057   FROM po_requisition_lines   rql,
1058        mtl_system_items_kfv   msi,
1059        hr_locations	      hrt,
1060        per_people_f           per
1061  WHERE rql.requisition_header_id = v_document_id
1062    AND NVL(rql.cancel_flag,'N') = 'N'
1063    AND hrt.location_id (+) = rql.deliver_to_location_id
1064    AND rql.item_id is not null
1065    AND rql.item_id = msi.inventory_item_id
1066    AND nvl(msi.organization_id, rql.destination_organization_id) =
1067        rql.destination_organization_id
1068    AND rql.to_person_id = per.person_id(+)
1069    AND per.effective_start_date(+) <= trunc(sysdate)
1070    AND per.effective_end_date(+) >= trunc(sysdate)
1071  UNION
1072   SELECT rql.line_num,
1073        NULL,
1074        rql.item_revision,
1075        rql.item_description,
1076        rql.unit_meas_lookup_code,
1077        rql.quantity,
1078        rql.unit_price,
1079        rql.quantity * rql.unit_price,
1080        rql.need_by_date,
1081        hrt.location_code,
1082        per.full_name,
1083        rql.suggested_vendor_name,
1084        rql.suggested_vendor_location
1085   FROM po_requisition_lines   rql,
1086        hr_locations       hrt,
1087        per_people_f           per
1088  WHERE rql.requisition_header_id = v_document_id
1089    AND NVL(rql.cancel_flag,'N') = 'N'
1090    AND hrt.location_id (+) = rql.deliver_to_location_id
1091    AND rql.item_id is NULL
1092    AND rql.to_person_id = per.person_id(+)
1093    AND per.effective_start_date(+) <= trunc(sysdate)
1094    AND per.effective_end_date(+) >= trunc(sysdate)
1095  ORDER BY 1;
1096 
1097 BEGIN
1098 
1099   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1100   l_item_key := substr(document_id, instr(document_id, ':') + 1,
1101                        length(document_id) - 2);
1102 
1103   l_document_id := wf_engine.GetItemAttrNumber
1104                                         (itemtype   => l_item_type,
1105                                          itemkey    => l_item_key,
1106                                          aname      => 'DOCUMENT_ID');
1107 
1108   l_org_id := wf_engine.GetItemAttrNumber
1109                                         (itemtype   => l_item_type,
1110                                          itemkey    => l_item_key,
1111                                          aname      => 'ORG_ID');
1112 
1113   l_req_details_url := wf_engine.GetItemAttrText
1114                                         (itemtype   => l_item_type,
1115                                          itemkey    => l_item_key,
1116                                          aname      => 'REQ_URL');
1117 
1118   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;       -- <R12.MOAC>
1119 
1120   l_currency_code := PO_CORE_S2.get_base_currency;
1121 
1122   if (display_type = 'text/html') then
1123 
1124 --    l_document := '<LINK REL=STYLESHEET HREF=/OA_HTML/PORSTYL2.css TYPE=text/css>';
1125     l_document := l_document || NL || NL || '<!-- REQ_LINE_DETAILS -->'|| NL || NL || '<P>';
1126 
1127 	l_document := l_document ||'<TR><TD></TD>'||NL;
1128     l_document := l_document || '<TD class=subheader1>'|| fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS');
1129     l_document := l_document || '</TD></TR>';
1130 
1131 	/* horizontal line */
1132   l_document := l_document || '<TR><TD></TD>' || NL;
1133     l_document := l_document || '<TD colspan=2 height=1 bgcolor=#cccc99><img src=/OA_MEDIA/FNDITPNT.gif></TD></TR>';
1134 
1135     l_document := l_document || '<TR><TD colspan=2 height=15 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
1136 
1137 
1138 	/* Blank table row */
1139 
1140 	/* Message about five req lines and link */
1141     l_document := l_document || '<TR><TD></TD>'||NL;
1142 
1143 	/* View req details link */
1144     l_req_line_msg := '<TD class="instructiontext">'
1145      ||fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS_DESP') || ' ' ||
1146      fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS_DESP1');
1147     l_req_details_url := '<a href="'|| l_req_details_url || '">View Requisition Details</a>';
1148 --    l_req_line_msg := replace(l_req_line_msg, '&REQ_DTL_LINK', l_req_details_url);
1149 
1150 	l_document := l_document || l_req_line_msg || NL ;
1151 
1152     l_document := l_document || '</TD>' || NL;
1153 
1154      l_document := l_document || '<TR><TD colspan=2 height=20 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
1155 
1156     l_document := l_document || '<TR><TD></TD>'||NL;
1157     l_document := l_document || '<TD>'||l_req_details_url ;
1158 --    l_document := l_document || ' | ' || l_req_details_url;
1159     l_document := l_document || '</TD></TR>' || NL;
1160 
1161 
1162      l_document := l_document || '<TR><TD colspan=2 height=15 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
1163 
1164 
1165 	l_document := l_document ||'<TR><TD></TD>'||NL;
1166     l_document := l_document || '<TD align=left><TABLE border=0 width=100% cellpadding=5 cellspacing=1>';
1167 
1168     l_document := l_document || '<TR>' || NL;
1169 
1170     l_document := l_document || '<TD class="tableheader" nowrap width=3%>' ||
1171                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TD>' || NL;
1172 
1173     l_document := l_document || '<TD class="tableheader" nowrap width=30%>' ||
1174                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TD>' || NL;
1175 
1176     l_document := l_document || '<TD class="tableheader" width=5%>' ||
1177                   fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TD>' || NL;
1178 
1179     l_document := l_document || '<TD class="tableheader" width=8%>' ||
1180                   fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TD>' || NL;
1181 
1182     l_document := l_document || '<TD class="tableheader" width=10%>' ||
1183                   fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TD>' || NL;
1184 
1185     l_document := l_document || '<TD class="tableheader" width=20%>' ||
1186                   fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER') || '</TD>' || NL;
1187 
1188     l_document := l_document || '<TD class="tableheader" width=10%>' ||
1189                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT')||  ' (' || l_currency_code || ')'   || '</TD>' || NL;
1190 
1191 
1192     l_document := l_document || '</TR>' || NL;
1193 
1194     open line_csr(l_document_id);
1195 
1196     loop
1197 
1198       fetch line_csr into l_line;
1199 
1200       i := i + 1;
1201       exit when line_csr%notfound;
1202 
1203       l_document := l_document || '<TR>' || NL;
1204 
1205       l_document := l_document || '<TD class=tabledata nowrap width=3% align=left>' || nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
1206       l_document := l_document || '<TD class=tabledata width=30% align=left>' || nvl(l_line.item_desc, ' ') || '</TD>' || NL;
1207       l_document := l_document || '<TD class=tabledata nowrap width=5% align=left>' || nvl(l_line.uom, ' ') || '</TD>' || NL;
1208       l_document := l_document || '<TD class=tabledata nowrap width=8% align=left>' || nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
1209 /*
1210       l_document := l_document || '<TD class=tabledata nowrap align=left>' ||
1211                TO_CHAR(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(
1212                                           l_currency_code, 30)) || '</TD>' || NL;
1213 */
1214       l_document := l_document || '<TD class=tabledata nowrap width=10% align=left>' || TO_CHAR(l_line.unit_price) || '</TD>' || NL;
1215 
1216       l_document := l_document || '<TD class=tabledata width=20% align=left>' || nvl(l_line.sugg_supplier, ' ') || '</TD>' || NL;
1217 
1218 
1219       l_document := l_document || '<TD class=tabledata nowrap width=10% align=right>' || TO_CHAR(l_line.line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || '</TD>' || NL;
1220 
1221 
1222       l_document := l_document || '</TR>' || NL;
1223 
1224       exit when i = 5;
1225     end loop;
1226 
1227     close line_csr;
1228 
1229     l_document := l_document || '</TABLE></P></TD></TR></TABLE></P></TD></TR></P></TABLE>' || NL;
1230 
1231   else
1232 
1233     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS') || NL1 || NL1;
1234 
1235     open line_csr(l_document_id);
1236 
1237     loop
1238 
1239       fetch line_csr into l_line;
1240 
1241       i := i + 1;
1242       exit when line_csr%notfound;
1243 
1244       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ':' || to_char(l_line.line_num) || NL1;
1245       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || ': ' || l_line.item_num || NL1;
1246       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || ': ' || l_line.item_revision || NL1;
1247       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || ': ' || l_line.item_desc || NL1;
1248       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' || l_line.uom || NL1;
1249       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' || to_char(l_line.quantity) || NL1;
1250       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
1251 					|| to_char(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL1;
1252       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || ': '
1253 					|| to_char(l_line.line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL1 || NL1;
1254 
1255       exit when i = 5;
1256       end loop;
1257 
1258 
1259     l_req_details_url := substr(l_req_details_url,
1260          instr(l_req_details_url,'''',1,1)+1,
1261          instr(l_req_details_url,'''',1,2)- instr(l_req_details_url,'''',1)-1);
1262 
1263     l_document := l_document || NL1 || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_DTLS') || ': ' ||
1264                   l_req_details_url || NL1;
1265   end if;
1266 
1267   document := l_document;
1268 
1269 END get_req_lines_details;
1270 
1271 
1272 
1273 PROCEDURE get_action_history(document_id	in	varchar2,
1274                                  display_type	in	varchar2,
1275                                  document	in out	NOCOPY varchar2,
1276                                  document_type	in out	NOCOPY varchar2) IS
1277 
1278   l_item_type    wf_items.item_type%TYPE;
1279   l_item_key     wf_items.item_key%TYPE;
1280 
1281   l_document_id      po_requisition_lines.requisition_header_id%TYPE;
1282   l_object_type      po_action_history.object_type_code%TYPE;
1283   l_org_id           po_requisition_lines.org_id%TYPE;
1284 
1285   l_document         VARCHAR2(32000) := '';
1286 
1287   l_history          history_record;
1288   l_history_seq      number;
1289 
1290   NL                 VARCHAR2(1) := '';
1291   NL1                 VARCHAR2(1) := PO_WF_REQ_NOTIFICATION_R11.newline;
1292 
1293   CURSOR history_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
1294 
1295     SELECT poh.SEQUENCE_NUM,
1296            per.FULL_NAME,
1297            polc.DISPLAYED_FIELD,
1298            poh.ACTION_DATE,
1299            poh.NOTE,
1300            poh.OBJECT_REVISION_NUM
1301       from po_action_history  poh,
1302            per_people_f       per,
1303            po_lookup_codes    polc
1304      where OBJECT_TYPE_CODE = v_object_type
1305        and poh.action_code = polc.lookup_code
1306        and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
1307        and per.person_id = poh.employee_id
1308        and trunc(sysdate) between per.effective_start_date
1309                               and per.effective_end_date
1310        and OBJECT_ID = v_document_id
1311     UNION ALL
1312     SELECT poh.SEQUENCE_NUM,
1313            per.FULL_NAME,
1314            NULL,
1315            poh.ACTION_DATE,
1316            poh.NOTE,
1317            poh.OBJECT_REVISION_NUM
1318       from po_action_history  poh,
1319            per_people_f       per
1320      where OBJECT_TYPE_CODE = v_object_type
1321        and poh.action_code is null
1322        and per.person_id = poh.employee_id
1323        and trunc(sysdate) between per.effective_start_date
1324                               and per.effective_end_date
1325        and OBJECT_ID = v_document_id
1326    order by 1;
1327 
1328 BEGIN
1329 
1330   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1331   l_item_key := substr(document_id, instr(document_id, ':') + 1,
1332                        length(document_id) - 2);
1333 
1334   l_document_id := wf_engine.GetItemAttrNumber
1335                                         (itemtype   => l_item_type,
1336                                          itemkey    => l_item_key,
1337                                          aname      => 'DOCUMENT_ID');
1338 
1339   l_org_id := wf_engine.GetItemAttrNumber
1340                                         (itemtype   => l_item_type,
1341                                          itemkey    => l_item_key,
1342                                          aname      => 'ORG_ID');
1343 
1344   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;       -- <R12.MOAC>
1345 
1346 
1347   if l_item_type = 'REQAPPRV' then
1348 
1349     l_object_type := 'REQUISITION';
1350 
1351   elsif l_item_type = 'POAPPRV' then
1352 
1353     null;
1354 
1355   end if;
1356 
1357   if (display_type = 'text/html') then
1358 
1359 --    l_document := '<LINK REL=STYLESHEET HREF=/OA_HTML/PORSTYL2.css TYPE=text/css>';
1360     l_document := NL || NL || '<!-- ACTION_HISTORY -->'|| NL || NL || '<P>';
1361     l_document := l_document || '<TR><TD></TD>'||NL;
1362     l_document := l_document || '<TD class=subheader1>'||fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY') || NL;
1363     l_document := l_document || '</TD></TR>' || NL;
1364 
1365      l_document := l_document || '<TR><TD colspan=2 height=20 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
1366 
1367 
1368     l_document := l_document || '<TR><TD></TD>'||NL;
1369     l_document := l_document || '<TD><TABLE border=0 width=100% cellpadding=5 cellspacing=1>' || NL;
1370 
1371     l_document := l_document || '<TD class="tableheader" width=5%>' ||
1372                   fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') || '</TD>' || NL;
1373 
1374     l_document := l_document || '<TD class="tableheader" width=20%>' ||
1375                   fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || '</TD>' || NL;
1376 
1377     l_document := l_document || '<TD class="tableheader" width=12%>' ||
1378                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || '</TD>' || NL;
1379 
1380     l_document := l_document || '<TD class="tableheader" width=35%>' ||
1381                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || '</TD>' || NL;
1382 
1383     l_document := l_document || '</TR>' || NL;
1384 
1385     open history_csr(l_document_id, l_object_type);
1386 
1387     loop
1388 
1389       fetch history_csr into l_history;
1390 
1391       exit when history_csr%notfound;
1392 
1393       l_history_seq := l_history.seq_num + 1;
1394       l_document := l_document || '<TR>' || NL;
1395 
1396       l_document := l_document || '<TD class="tabledata" width=12% nowrap align=left>' || nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
1397       l_document := l_document || '<TD class="tabledata" width=27% nowrap align=left>' || nvl(l_history.employee_name, ' ') || '</TD>' || NL;
1398       l_document := l_document || '<TD class="tabledata" nowrap width=16% align=left>' || nvl(l_history.action, ' ') || '</TD>' || NL;
1399       l_document := l_document || '<TD class="tabledata" width=45% align=left>' || nvl(l_history.note, ' ') || '</TD>' || NL;
1400 
1401       l_document := l_document || '</TR>' || NL;
1402 
1403     end loop;
1404 
1405     close history_csr;
1406 
1407 
1408     l_document := l_document || '</TABLE></P></TR>' || NL;
1409 
1410      l_document := l_document || '<TR><TD colspan=2 height=40 <img src=/OA_MEDIA/PORTRANS.gif></TD></TR>';
1411 
1412     document := l_document;
1413 
1414   elsif (display_type = 'text/plain') then
1415 
1416     document := '';
1417 
1418   end if;
1419 
1420 END get_action_history;
1421 
1422 PROCEDURE post_approval_notif(itemtype   in varchar2,
1423                               itemkey    in varchar2,
1424                               actid      in number,
1425                               funcmode   in varchar2,
1426                               resultout  in out NOCOPY varchar2) is
1427 
1428 begin
1429 
1430   resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1431 
1432   -- Don't allow transfer
1433   if (funcmode = 'TRANSFER') then
1434 
1435     fnd_message.set_name('PO', 'PO_WF_NOTIF_NO_TRANSFER');
1436     app_exception.raise_exception;
1437 
1438   end if; -- end if for funcmode = 'TRANSFER'
1439 
1440   return;
1441 
1442 end post_approval_notif;
1443 
1444 --
1445 -- Newline
1446 --   Return newline character in current codeset
1447 --
1448 function Newline
1449 return varchar2
1450 is
1451 begin
1452   return(fnd_global.Local_Chr(10));
1453 end Newline;
1454 
1455 END PO_WF_REQ_NOTIFICATION_R11;