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_DOCUMENT

Source


1 PACKAGE BODY PO_WF_DOCUMENT AS
2 /* $Header: POXWMSGB.pls 120.0 2005/06/01 13:27:09 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 PROCEDURE get_po_req_approve_msg(document_id	in	varchar2,
30                                  display_type	in	varchar2,
31                                  document	in out	NOCOPY varchar2,
32                                  document_type	in out	NOCOPY varchar2) IS
33 
34   l_item_type    wf_items.item_type%TYPE;
35   l_item_key     wf_items.item_key%TYPE;
36 
37   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
38   l_org_id           po_requisition_headers.org_id%TYPE;
39   l_document_subtype po_lookup_codes.displayed_field%TYPE;
40   l_document_type    po_lookup_codes.displayed_field%TYPE;
41   l_document_number  po_requisition_headers.segment1%TYPE;
42   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
43   l_total_amount     VARCHAR2(30);
44   l_header_msg       VARCHAR2(200);
45   l_req_amount       VARCHAR2(30);
46   l_tax_amount       VARCHAR2(30);
47   l_description      po_requisition_headers.description%TYPE;
48   l_forwarded_from   per_people_f.full_name%TYPE;
49   l_preparer         per_people_f.full_name%TYPE;
50   l_note             VARCHAR2(240);
51 
52   l_document         VARCHAR2(32000) := '';
53   l_tax_amt          NUMBER;
54 
55   NL                 VARCHAR2(1) := fnd_global.newline;
56 
57 BEGIN
58 
59   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
60   l_item_key := substr(document_id, instr(document_id, ':') + 1,
61                        length(document_id) - 2);
62 
63   l_document_id := wf_engine.GetItemAttrNumber
64                                         (itemtype   => l_item_type,
65                                          itemkey    => l_item_key,
66                                          aname      => 'DOCUMENT_ID');
67 
68   l_org_id := wf_engine.GetItemAttrNumber
69                                         (itemtype   => l_item_type,
70                                          itemkey    => l_item_key,
71                                          aname      => 'ORG_ID');
72 
73   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;         -- <R12 MOAC>
74 
75   l_document_subtype := wf_engine.GetItemAttrText
76                                         (itemtype   => l_item_type,
77                                          itemkey    => l_item_key,
78                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
79 
80   l_document_type := wf_engine.GetItemAttrText
81                                         (itemtype   => l_item_type,
82                                          itemkey    => l_item_key,
83                                          aname      => 'DOCUMENT_TYPE_DISP');
84 
85   l_document_number := wf_engine.GetItemAttrText
86                                         (itemtype   => l_item_type,
87                                          itemkey    => l_item_key,
88                                          aname      => 'DOCUMENT_NUMBER');
89 
90   l_currency_code := wf_engine.GetItemAttrText
91                                         (itemtype   => l_item_type,
92                                          itemkey    => l_item_key,
93                                          aname      => 'FUNCTIONAL_CURRENCY');
94 
95   l_total_amount := wf_engine.GetItemAttrText
96                                         (itemtype   => l_item_type,
97                                          itemkey    => l_item_key,
98                                          aname      => 'TOTAL_AMOUNT_DSP');
99 
100   if wf_engine.GetItemAttrText(itemtype   => l_item_type,
101                                itemkey    => l_item_key,
102                                aname      => 'REQUIRES_APPROVAL_MSG') is not null then
103 
104     l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVAL_MSG');
105 
106   else
107 
108     l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_INVALID_PERSON_MSG');
109 
110   end if;
111 
112   l_req_amount := wf_engine.GetItemAttrText
113                                         (itemtype   => l_item_type,
114                                          itemkey    => l_item_key,
115                                          aname      => 'REQ_AMOUNT_DSP');
116 
117   l_tax_amount := wf_engine.GetItemAttrText
118                                         (itemtype   => l_item_type,
119                                          itemkey    => l_item_key,
120                                          aname      => 'TAX_AMOUNT_DSP');
121 
122   l_description := wf_engine.GetItemAttrText
123                                         (itemtype   => l_item_type,
124                                          itemkey    => l_item_key,
125                                          aname      => 'REQ_DESCRIPTION');
126 
127   l_forwarded_from := wf_engine.GetItemAttrText
128                                         (itemtype   => l_item_type,
129                                          itemkey    => l_item_key,
130                                          aname      => 'FORWARD_FROM_DISP_NAME');
131 
132   l_preparer := wf_engine.GetItemAttrText
133                                         (itemtype   => l_item_type,
134                                          itemkey    => l_item_key,
135                                          aname      => 'PREPARER_DISPLAY_NAME');
136 
137   l_note := wf_engine.GetItemAttrText
138                                         (itemtype   => l_item_type,
139                                          itemkey    => l_item_key,
140                                          aname      => 'NOTE');
141 
142   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
143   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
144   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
145   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
146   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
147 
148   SELECT nvl(sum(nonrecoverable_tax), 0)
149     INTO l_tax_amt
150     FROM po_requisition_lines rl,
151          po_req_distributions_all rd  -- <R12 MOAC>
152    WHERE rl.requisition_header_id = l_document_id
153      AND rd.requisition_line_id = rl.requisition_line_id;
154 
155   if (display_type = 'text/html') then
156 
157     l_document := NL || NL || '<!-- PO_REQ_APPROVE_MSG -->'|| NL || NL || '<P>';
158 
159     l_document := l_document || l_header_msg;
160 
161     l_document := l_document || '</P>' || NL;
162 
163     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' || NL ||
164                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') ||
165                   '  </TD>' || NL;
166 
167     l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_req_amount || '</TD></TR>' || NL;
168 
169     if l_tax_amt > 0 then
170 
171       l_document := l_document || '<TR><TD align=right>' ||
172                     fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') ||
173                     '  </TD>' || NL;
174       l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_tax_amount ||
175                     '</TD></TR></TABLE></P>' || NL;
176 
177     else
178 
179       l_document := l_document || '</TABLE></P>' || NL || NL;
180 
181     end if;
182 
183     if l_description is not null then
184       l_document := l_document || '<P>' || NL;
185       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
186       l_document := l_document || l_description;
187       l_document := l_document || '<BR></P>' || NL;
188     end if;
189 
190     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' ||
191                   fnd_message.get_string('PO', 'PO_WF_NOTIF_FORWARDED_FROM') ||
192                   '  </TD>' || NL;
193 
194     l_document := l_document || '<TD align=left>' || l_forwarded_from || '</TD></TR>' || NL;
195 
196     l_document := l_document || '<TR><TD align=right>' ||
197                   fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
198                   '  </TD>' || NL;
199     l_document := l_document || '<TD align=left>' || l_preparer || '</TD></TR></TABLE></P>' || NL;
200 
201     if l_note is not null then
202       l_document := l_document || '<P>' || NL;
203       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL || '<BR>' || NL;
204       l_document := l_document || l_note;
205       l_document := l_document || '<BR></P>' || NL;
206     end if;
207 
208   else
209 
210     l_document := l_document || l_header_msg || NL || NL;
211 
212     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
213     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
214 
215     if l_tax_amt > 0 then
216 
217       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
218       l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
219 
220     end if;
221 
222     if l_description is not null then
223       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
224       l_document := l_document || l_description || NL;
225     end if;
226 
227     l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_FORWARDED_FROM');
228     l_document := l_document || ' ' || l_forwarded_from || NL;
229 
230     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
231     l_document := l_document || ' ' || l_preparer || NL;
232 
233     if l_note is not null then
234       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
235       l_document := l_document || l_note || NL;
236     end if;
237 
238   end if;
239 
240   document := l_document;
241 
242 END;
243 
244 PROCEDURE get_po_req_approved_msg(document_id	in	varchar2,
245                                  display_type	in	varchar2,
246                                  document	in out	NOCOPY varchar2,
247                                  document_type	in out	NOCOPY varchar2) IS
248 
249   l_item_type    wf_items.item_type%TYPE;
250   l_item_key     wf_items.item_key%TYPE;
251 
252   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
253   l_org_id           po_requisition_headers.org_id%TYPE;
254   l_document_subtype po_lookup_codes.displayed_field%TYPE;
255   l_document_type    po_lookup_codes.displayed_field%TYPE;
256   l_document_number  po_requisition_headers.segment1%TYPE;
257   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
258   l_total_amount     VARCHAR2(30);
259   l_header_msg       VARCHAR2(200);
260   l_req_amount       VARCHAR2(30);
261   l_tax_amount       VARCHAR2(30);
262   l_description      po_requisition_headers.description%TYPE;
263   l_approver         per_people_f.full_name%TYPE;
264   l_preparer         per_people_f.full_name%TYPE;
265   l_note             VARCHAR2(240);
266 
267   l_document         VARCHAR2(32000) := '';
268   l_tax_amt          NUMBER;
269 
270   NL                 VARCHAR2(1) := fnd_global.newline;
271 
272 BEGIN
273 
274   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
275   l_item_key := substr(document_id, instr(document_id, ':') + 1,
276                        length(document_id) - 2);
277 
278   l_document_id := wf_engine.GetItemAttrNumber
279                                         (itemtype   => l_item_type,
280                                          itemkey    => l_item_key,
281                                          aname      => 'DOCUMENT_ID');
282 
283   l_org_id := wf_engine.GetItemAttrNumber
284                                         (itemtype   => l_item_type,
285                                          itemkey    => l_item_key,
286                                          aname      => 'ORG_ID');
287 
288   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;         -- <R12 MOAC>
289 
290   l_document_subtype := wf_engine.GetItemAttrText
291                                         (itemtype   => l_item_type,
292                                          itemkey    => l_item_key,
293                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
294 
295   l_document_type := wf_engine.GetItemAttrText
296                                         (itemtype   => l_item_type,
297                                          itemkey    => l_item_key,
298                                          aname      => 'DOCUMENT_TYPE_DISP');
299 
300   l_document_number := wf_engine.GetItemAttrText
301                                         (itemtype   => l_item_type,
302                                          itemkey    => l_item_key,
303                                          aname      => 'DOCUMENT_NUMBER');
304 
305   l_currency_code := wf_engine.GetItemAttrText
306                                         (itemtype   => l_item_type,
307                                          itemkey    => l_item_key,
308                                          aname      => 'FUNCTIONAL_CURRENCY');
309 
310   l_total_amount := wf_engine.GetItemAttrText
311                                         (itemtype   => l_item_type,
312                                          itemkey    => l_item_key,
313                                          aname      => 'TOTAL_AMOUNT_DSP');
314 
315   l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVED');
316 
317   l_req_amount := wf_engine.GetItemAttrText
318                                         (itemtype   => l_item_type,
319                                          itemkey    => l_item_key,
320                                          aname      => 'REQ_AMOUNT_DSP');
321 
322   l_tax_amount := wf_engine.GetItemAttrText
323                                         (itemtype   => l_item_type,
324                                          itemkey    => l_item_key,
325                                          aname      => 'TAX_AMOUNT_DSP');
326 
327   l_description := wf_engine.GetItemAttrText
328                                         (itemtype   => l_item_type,
329                                          itemkey    => l_item_key,
330                                          aname      => 'REQ_DESCRIPTION');
331 
332   l_approver  := wf_engine.GetItemAttrText
333                                         (itemtype   => l_item_type,
334                                          itemkey    => l_item_key,
335                                          aname      => 'APPROVER_DISPLAY_NAME');
336 
337   l_preparer := wf_engine.GetItemAttrText
338                                         (itemtype   => l_item_type,
339                                          itemkey    => l_item_key,
340                                          aname      => 'PREPARER_DISPLAY_NAME');
341 
342   l_note := wf_engine.GetItemAttrText
343                                         (itemtype   => l_item_type,
344                                          itemkey    => l_item_key,
345                                          aname      => 'NOTE');
346 
347   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
348   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
349   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
350   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
351   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
352 
353   SELECT nvl(sum(nonrecoverable_tax), 0)
354     INTO l_tax_amt
355     FROM po_requisition_lines rl,
356          po_req_distributions_all rd  -- <R12 MOAC>
357    WHERE rl.requisition_header_id = l_document_id
358      AND rd.requisition_line_id = rl.requisition_line_id;
359 
360   if (display_type = 'text/html') then
361 
362     l_document := NL || NL || '<!-- PO_REQ_APPROVE_MSG -->'|| NL || NL || '<P>';
363 
364     l_document := l_document || l_header_msg;
365 
366     l_document := l_document || '</P>' || NL;
367 
368     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' || NL ||
369                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') ||
370                   '  </TD>' || NL;
371 
372     l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_req_amount || '</TD></TR>' || NL;
373 
374     if l_tax_amt > 0 then
375 
376       l_document := l_document || '<TR><TD align=right>' ||
377                     fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') ||
378                     '  </TD>' || NL;
379       l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_tax_amount ||
380                     '</TD></TR></TABLE></P>' || NL;
381 
382     else
383 
384       l_document := l_document || '</TABLE></P>' || NL || NL;
385 
386     end if;
387 
388     if l_description is not null then
389       l_document := l_document || '<P>' || NL;
390       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
391       l_document := l_document || l_description;
392       l_document := l_document || '<BR></P>' || NL;
393     end if;
394 
395     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' ||
396                   fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVER') ||
397                   '  </TD>' || NL;
398 
399     l_document := l_document || '<TD align=left>' || l_approver || '</TD></TR>' || NL;
400 
401     l_document := l_document || '<TR><TD align=right>' ||
402                   fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
403                   '  </TD>' || NL;
404     l_document := l_document || '<TD align=left>' || l_preparer || '</TD></TR></TABLE></P>' || NL;
405 
406     if l_note is not null then
407       l_document := l_document || '<P>' || NL;
408       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL || '<BR>' || NL;
409       l_document := l_document || l_note;
410       l_document := l_document || '<BR></P>' || NL;
411     end if;
412 
413   else
414 
415     l_document := l_document || l_header_msg || NL || NL;
416 
417     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
418     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
419 
420     if l_tax_amt > 0 then
421 
422       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
423       l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
424 
425     end if;
426 
427     if l_description is not null then
428       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
429       l_document := l_document || l_description || NL;
430     end if;
431 
432     l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVER');
433     l_document := l_document || ' ' || l_approver || NL;
434 
435     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
436     l_document := l_document || ' ' || l_preparer || NL;
437 
438     if l_note is not null then
439       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
440       l_document := l_document || l_note || NL;
441     end if;
442 
443   end if;
444 
445   document := l_document;
446 
447 END;
448 
449 PROCEDURE get_po_req_no_approver_msg(document_id	in	varchar2,
450                                  display_type	in	varchar2,
451                                  document	in out	NOCOPY varchar2,
452                                  document_type	in out	NOCOPY varchar2) IS
453 
454   l_item_type    wf_items.item_type%TYPE;
455   l_item_key     wf_items.item_key%TYPE;
456 
457   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
458   l_org_id           po_requisition_headers.org_id%TYPE;
459   l_document_subtype po_lookup_codes.displayed_field%TYPE;
460   l_document_type    po_lookup_codes.displayed_field%TYPE;
461   l_document_number  po_requisition_headers.segment1%TYPE;
462   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
463   l_total_amount     VARCHAR2(30);
464   l_header_msg       VARCHAR2(200);
465   l_req_amount       VARCHAR2(30);
466   l_tax_amount       VARCHAR2(30);
467   l_description      po_requisition_headers.description%TYPE;
468   l_approver         per_people_f.full_name%TYPE;
469   l_preparer         per_people_f.full_name%TYPE;
470   l_note             VARCHAR2(240);
471 
472   l_document         VARCHAR2(32000) := '';
473   l_tax_amt          NUMBER;
474 
475   NL                 VARCHAR2(1) := fnd_global.newline;
476 
477 BEGIN
478 
479   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
480   l_item_key := substr(document_id, instr(document_id, ':') + 1,
481                        length(document_id) - 2);
482 
483   l_document_id := wf_engine.GetItemAttrNumber
484                                         (itemtype   => l_item_type,
485                                          itemkey    => l_item_key,
486                                          aname      => 'DOCUMENT_ID');
487 
488   l_org_id := wf_engine.GetItemAttrNumber
489                                         (itemtype   => l_item_type,
490                                          itemkey    => l_item_key,
491                                          aname      => 'ORG_ID');
492 
493   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;         -- <R12 MOAC>
494 
495   l_document_subtype := wf_engine.GetItemAttrText
496                                         (itemtype   => l_item_type,
497                                          itemkey    => l_item_key,
498                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
499 
500   l_document_type := wf_engine.GetItemAttrText
501                                         (itemtype   => l_item_type,
502                                          itemkey    => l_item_key,
503                                          aname      => 'DOCUMENT_TYPE_DISP');
504 
505   l_document_number := wf_engine.GetItemAttrText
506                                         (itemtype   => l_item_type,
507                                          itemkey    => l_item_key,
508                                          aname      => 'DOCUMENT_NUMBER');
509 
510   l_currency_code := wf_engine.GetItemAttrText
511                                         (itemtype   => l_item_type,
512                                          itemkey    => l_item_key,
513                                          aname      => 'FUNCTIONAL_CURRENCY');
514 
515   l_total_amount := wf_engine.GetItemAttrText
516                                         (itemtype   => l_item_type,
517                                          itemkey    => l_item_key,
518                                          aname      => 'TOTAL_AMOUNT_DSP');
519 
520   l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_NO_APPROVER');
521 
522   l_req_amount := wf_engine.GetItemAttrText
523                                         (itemtype   => l_item_type,
524                                          itemkey    => l_item_key,
525                                          aname      => 'REQ_AMOUNT_DSP');
526 
527   l_tax_amount := wf_engine.GetItemAttrText
528                                         (itemtype   => l_item_type,
529                                          itemkey    => l_item_key,
530                                          aname      => 'TAX_AMOUNT_DSP');
531 
532   l_description := wf_engine.GetItemAttrText
533                                         (itemtype   => l_item_type,
534                                          itemkey    => l_item_key,
535                                          aname      => 'REQ_DESCRIPTION');
536 
537   l_approver := wf_engine.GetItemAttrText
538                                         (itemtype   => l_item_type,
539                                          itemkey    => l_item_key,
540                                          aname      => 'APPROVER_DISPLAY_NAME');
541 
542   l_preparer := wf_engine.GetItemAttrText
543                                         (itemtype   => l_item_type,
544                                          itemkey    => l_item_key,
545                                          aname      => 'PREPARER_DISPLAY_NAME');
546 
547   l_note := wf_engine.GetItemAttrText
548                                         (itemtype   => l_item_type,
549                                          itemkey    => l_item_key,
550                                          aname      => 'NOTE');
551 
552   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
553   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
554   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
555   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
556   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
557 
558   SELECT nvl(sum(nonrecoverable_tax), 0)
559     INTO l_tax_amt
560     FROM po_requisition_lines rl,
561          po_req_distributions_all rd  -- <R12 MOAC>
562    WHERE rl.requisition_header_id = l_document_id
563      AND rd.requisition_line_id = rl.requisition_line_id;
564 
565   if (display_type = 'text/html') then
566 
567     l_document := NL || NL || '<!-- PO_REQ_APPROVE_MSG -->'|| NL || NL || '<P>';
568 
569     l_document := l_document || l_header_msg;
570 
571     l_document := l_document || '</P>' || NL;
572 
573     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' || NL ||
574                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') ||
575                   '  </TD>' || NL;
576 
577     l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_req_amount || '</TD></TR>' || NL;
578 
579     if l_tax_amt > 0 then
580 
581       l_document := l_document || '<TR><TD align=right>' ||
582                     fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') ||
583                     '  </TD>' || NL;
584       l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_tax_amount ||
585                     '</TD></TR></TABLE></P>' || NL;
586 
587     else
588 
589       l_document := l_document || '</TABLE></P>' || NL || NL;
590 
591     end if;
592 
593     if l_description is not null then
594       l_document := l_document || '<P>' || NL;
595       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
596       l_document := l_document || l_description;
597       l_document := l_document || '<BR></P>' || NL;
598     end if;
599 
600     l_document := l_document || '<TR><TD align=right>' ||
601                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LAST_APPROVER') ||
602                   '  </TD>' || NL;
603     l_document := l_document || '<TD align=left>' || l_approver || '</TD></TR></TABLE></P>' || NL;
604 
605     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' ||
606                   fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
607                   '  </TD>' || NL;
608 
609     l_document := l_document || '<TD align=left>' || l_preparer || '</TD></TR>' || NL;
610 
611     if l_note is not null then
612       l_document := l_document || '<P>' || NL;
613       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL || '<BR>' || NL;
614       l_document := l_document || l_note;
615       l_document := l_document || '<BR></P>' || NL;
616     end if;
617 
618   else
619 
620     l_document := l_document || l_header_msg || NL || NL;
621 
622     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
623     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
624 
625     if l_tax_amt > 0 then
626 
627       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
628       l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
629 
630     end if;
631 
632     if l_description is not null then
633       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
634       l_document := l_document || l_description || NL;
635     end if;
636 
637     l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_LAST_APPROVER');
638     l_document := l_document || ' ' || l_approver || NL;
639 
640     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
641     l_document := l_document || ' ' || l_preparer || NL;
642 
643     if l_note is not null then
644       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
645       l_document := l_document || l_note || NL;
646     end if;
647 
648   end if;
649 
650   document := l_document;
651 
652 END;
653 
654 PROCEDURE get_po_req_reject_msg(document_id	in	varchar2,
655                                  display_type	in	varchar2,
656                                  document	in out	NOCOPY varchar2,
657                                  document_type	in out	NOCOPY varchar2) IS
658 
659   l_item_type    wf_items.item_type%TYPE;
660   l_item_key     wf_items.item_key%TYPE;
661 
662   l_document_id      po_requisition_headers.requisition_header_id%TYPE;
663   l_org_id           po_requisition_headers.org_id%TYPE;
664   l_document_subtype po_lookup_codes.displayed_field%TYPE;
665   l_document_type    po_lookup_codes.displayed_field%TYPE;
666   l_document_number  po_requisition_headers.segment1%TYPE;
667   l_currency_code    fnd_currencies.CURRENCY_CODE%TYPE;
668   l_total_amount     VARCHAR2(30);
669   l_header_msg       VARCHAR2(200);
670   l_req_amount       VARCHAR2(30);
671   l_tax_amount       VARCHAR2(30);
672   l_description      po_requisition_headers.description%TYPE;
673   l_rejected_by      per_people_f.full_name%TYPE;
674   l_preparer         per_people_f.full_name%TYPE;
675   l_note             VARCHAR2(240);
676 
677   l_document         VARCHAR2(32000) := '';
678   l_tax_amt          NUMBER;
679 
680   NL                 VARCHAR2(1) := fnd_global.newline;
681 
682 BEGIN
683 
684   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
685   l_item_key := substr(document_id, instr(document_id, ':') + 1,
686                        length(document_id) - 2);
687 
688   l_document_id := wf_engine.GetItemAttrNumber
689                                         (itemtype   => l_item_type,
690                                          itemkey    => l_item_key,
691                                          aname      => 'DOCUMENT_ID');
692 
693   l_org_id := wf_engine.GetItemAttrNumber
694                                         (itemtype   => l_item_type,
695                                          itemkey    => l_item_key,
696                                          aname      => 'ORG_ID');
697 
698   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;         -- <R12 MOAC>
699 
700   l_document_subtype := wf_engine.GetItemAttrText
701                                         (itemtype   => l_item_type,
702                                          itemkey    => l_item_key,
703                                          aname      => 'DOCUMENT_SUBTYPE_DISP');
704 
705   l_document_type := wf_engine.GetItemAttrText
706                                         (itemtype   => l_item_type,
707                                          itemkey    => l_item_key,
708                                          aname      => 'DOCUMENT_TYPE_DISP');
709 
710   l_document_number := wf_engine.GetItemAttrText
711                                         (itemtype   => l_item_type,
712                                          itemkey    => l_item_key,
713                                          aname      => 'DOCUMENT_NUMBER');
714 
715   l_currency_code := wf_engine.GetItemAttrText
716                                         (itemtype   => l_item_type,
717                                          itemkey    => l_item_key,
718                                          aname      => 'FUNCTIONAL_CURRENCY');
719 
720   l_total_amount := wf_engine.GetItemAttrText
721                                         (itemtype   => l_item_type,
722                                          itemkey    => l_item_key,
723                                          aname      => 'TOTAL_AMOUNT_DSP');
724 
725   l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_NO_APPROVER');
726 
727   l_req_amount := wf_engine.GetItemAttrText
728                                         (itemtype   => l_item_type,
729                                          itemkey    => l_item_key,
730                                          aname      => 'REQ_AMOUNT_DSP');
731 
732   l_tax_amount := wf_engine.GetItemAttrText
733                                         (itemtype   => l_item_type,
734                                          itemkey    => l_item_key,
735                                          aname      => 'TAX_AMOUNT_DSP');
736 
737   l_description := wf_engine.GetItemAttrText
738                                         (itemtype   => l_item_type,
739                                          itemkey    => l_item_key,
740                                          aname      => 'REQ_DESCRIPTION');
741 
742   l_rejected_by := wf_engine.GetItemAttrText
743                                         (itemtype   => l_item_type,
744                                          itemkey    => l_item_key,
745                                          aname      => 'APPROVER_DISPLAY_NAME');
746 
747   l_preparer := wf_engine.GetItemAttrText
748                                         (itemtype   => l_item_type,
749                                          itemkey    => l_item_key,
750                                          aname      => 'PREPARER_DISPLAY_NAME');
751 
752   l_note := wf_engine.GetItemAttrText
753                                         (itemtype   => l_item_type,
754                                          itemkey    => l_item_key,
755                                          aname      => 'NOTE');
756 
757   l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
758   l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
759   l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
760   l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
761   l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
762 
763   SELECT nvl(sum(nonrecoverable_tax), 0)
764     INTO l_tax_amt
765     FROM po_requisition_lines rl,
766          po_req_distributions_all rd  -- <R12 MOAC>
767    WHERE rl.requisition_header_id = l_document_id
768      AND rd.requisition_line_id = rl.requisition_line_id;
769 
770   if (display_type = 'text/html') then
771 
772     l_document := NL || NL || '<!-- PO_REQ_APPROVE_MSG -->'|| NL || NL || '<P>';
773 
774     l_document := l_document || l_header_msg;
775 
776     l_document := l_document || '</P>' || NL;
777 
778     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' || NL ||
779                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT') ||
780                   '  </TD>' || NL;
781 
782     l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_req_amount || '</TD></TR>' || NL;
783 
784     if l_tax_amt > 0 then
785 
786       l_document := l_document || '<TR><TD align=right>' ||
787                     fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') ||
788                     '  </TD>' || NL;
789       l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_tax_amount ||
790                     '</TD></TR></TABLE></P>' || NL;
791 
792     else
793 
794       l_document := l_document || '</TABLE></P>' || NL || NL;
795 
796     end if;
797 
798     if l_description is not null then
799       l_document := l_document || '<P>' || NL;
800       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
801       l_document := l_document || l_description;
802       l_document := l_document || '<BR></P>' || NL;
803     end if;
804 
805     l_document := l_document || '<TR><TD align=right>' ||
806                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REJECTED_BY') ||
807                   '  </TD>' || NL;
808     l_document := l_document || '<TD align=left>' || l_rejected_by || '</TD></TR></TABLE></P>' || NL;
809 
810     l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0><TR><TD align=right>' ||
811                   fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||
812                   '  </TD>' || NL;
813 
814     l_document := l_document || '<TD align=left>' || l_preparer || '</TD></TR>' || NL;
815 
816     if l_note is not null then
817       l_document := l_document || '<P>' || NL;
818       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL || '<BR>' || NL;
819       l_document := l_document || l_note;
820       l_document := l_document || '<BR></P>' || NL;
821     end if;
822 
823   else
824 
825     l_document := l_document || l_header_msg || NL || NL;
826 
827     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
828     l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
829 
830     if l_tax_amt > 0 then
831 
832       l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
833       l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
834 
835     end if;
836 
837     if l_description is not null then
838       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
839       l_document := l_document || l_description || NL;
840     end if;
841 
842     l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_REJECTED_BY');
843     l_document := l_document || ' ' || l_rejected_by || NL;
844 
845     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
846     l_document := l_document || ' ' || l_preparer || NL;
847 
848     if l_note is not null then
849       l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
850       l_document := l_document || l_note || NL;
851     end if;
852 
853   end if;
854 
855   document := l_document;
856 
857 END;
858 
859 PROCEDURE get_req_lines_details(document_id	in	varchar2,
860                                  display_type	in	varchar2,
861                                  document	in out	NOCOPY varchar2,
862                                  document_type	in out	NOCOPY varchar2) IS
863 
864   l_item_type    wf_items.item_type%TYPE;
865   l_item_key     wf_items.item_key%TYPE;
866 
867   l_document_id      po_requisition_lines.requisition_header_id%TYPE;
868   l_org_id           po_requisition_lines.org_id%TYPE;
869 
870   l_line             line_record;
871 
872   l_document         VARCHAR2(32000) := '';
873 
874   l_currency_code    fnd_currencies.currency_code%TYPE := PO_CORE_S2.get_base_currency;
875 
876   NL                 VARCHAR2(1) := fnd_global.newline;
877 
878 CURSOR line_csr(v_document_id NUMBER) IS
879 SELECT rql.line_num,
880        msi.concatenated_segments,
881        rql.item_revision,
882        rql.item_description,
883        rql.unit_meas_lookup_code,
884        rql.quantity,
885        rql.unit_price,
886        rql.quantity * rql.unit_price,
887        rql.need_by_date,
888        hrt.location_code,
889        per.full_name,
890        rql.suggested_vendor_name,
891        rql.suggested_vendor_location
892   FROM po_requisition_lines   rql,
893        mtl_system_items_kfv   msi,
894        hr_locations    	      hrt,
895        per_people_f           per
896  WHERE rql.requisition_header_id = v_document_id
897    AND hrt.location_id (+) = rql.deliver_to_location_id
898    AND rql.item_id = msi.inventory_item_id(+)
899    AND nvl(msi.organization_id, rql.destination_organization_id) =
900        rql.destination_organization_id
901    AND rql.to_person_id = per.person_id(+)
902    AND per.effective_start_date(+) <= trunc(sysdate)
903    AND per.effective_end_date(+) >= trunc(sysdate)
904  ORDER BY rql.line_num;
905 
906 BEGIN
907 
908   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
909   l_item_key := substr(document_id, instr(document_id, ':') + 1,
910                        length(document_id) - 2);
911 
912   l_document_id := wf_engine.GetItemAttrNumber
913                                         (itemtype   => l_item_type,
914                                          itemkey    => l_item_key,
915                                          aname      => 'DOCUMENT_ID');
916 
917   l_org_id := wf_engine.GetItemAttrNumber
918                                         (itemtype   => l_item_type,
919                                          itemkey    => l_item_key,
920                                          aname      => 'ORG_ID');
921 
922   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;         -- <R12 MOAC>
923 
924   if (display_type = 'text/html') then
925 
926     l_document := NL || NL || '<!-- REQ_LINE_DETAILS -->'|| NL || NL || '<P><B>';
927     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS');
928     l_document := l_document || '</B>';
929 
930 
931     l_document := l_document || '<TABLE border=1 cellpadding=2 cellspacing=1>';
932 
933     l_document := l_document || '<TR>' || NL;
934 
935     l_document := l_document || '<TH>' ||
936                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
937 
938     l_document := l_document || '<TH>' ||
939                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || '</TH>' || NL;
940 
941     l_document := l_document || '<TH>' ||
942                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || '</TH>' || NL;
943 
944     l_document := l_document || '<TH>' ||
945                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TH>' || NL;
946 
947     l_document := l_document || '<TH>' ||
948                   fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TH>' || NL;
949 
950     l_document := l_document || '<TH>' ||
951                   fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
952 
953     l_document := l_document || '<TH>' ||
954                   fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TH>' || NL;
955 
956     l_document := l_document || '<TH>' ||
957                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || '</TH>' || NL;
958 
959     l_document := l_document || '<TH>' ||
960                   fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY_DATE') || '</TH>' || NL;
961 
962     l_document := l_document || '<TH>' ||
963                   fnd_message.get_string('PO', 'PO_WF_NOTIF_LOCATION') || '</TH>' || NL;
964 
965     l_document := l_document || '<TH>' ||
966                   fnd_message.get_string('PO', 'PO_WF_NOTIF_REQUESTOR') || '</TH>' || NL;
967 
968     l_document := l_document || '</TR>' || NL;
969 
970     open line_csr(l_document_id);
971 
972     loop
973 
974       fetch line_csr into l_line;
975 
976       exit when line_csr%notfound;
977 
978       l_document := l_document || '<TR>' || NL;
979 
980       l_document := l_document || '<TD nowrap align=center>' || nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
981       l_document := l_document || '<TD nowrap>' || nvl(l_line.item_num, ' ') || '</TD>' || NL;
982       l_document := l_document || '<TD nowrap>' || nvl(l_line.item_revision, ' ') || '</TD>' || NL;
983       l_document := l_document || '<TD nowrap>' || nvl(l_line.item_desc, ' ') || '</TD>' || NL;
984       l_document := l_document || '<TD nowrap>' || nvl(l_line.uom, ' ') || '</TD>' || NL;
985       l_document := l_document || '<TD nowrap align=right>' || nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
986 
987       l_document := l_document || '<TD nowrap align=right>' ||
988                                   TO_CHAR(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(
989                                           l_currency_code, 30)) || '</TD>' || NL;
990 
991       l_document := l_document || '<TD nowrap align=right>' ||
992                                   TO_CHAR(l_line.line_amount, FND_CURRENCY.GET_FORMAT_MASK(
993                                           l_currency_code, 30)) || '</TD>' || NL;
994 
995       l_document := l_document || '<TD nowrap>' || to_char(l_line.need_by_date) || '</TD>' || NL;
996       l_document := l_document || '<TD nowrap>' || nvl(l_line.location, ' ') || '</TD>' || NL;
997       l_document := l_document || '<TD nowrap>' || nvl(l_line.requestor, ' ') || '</TD>' || NL;
998 
999       l_document := l_document || '</TR>' || NL;
1000 
1001     end loop;
1002 
1003     close line_csr;
1004 
1005     l_document := l_document || '</TABLE></P>' || NL;
1006 
1007     document := l_document;
1008 
1009   elsif (display_type = 'text/plain') then
1010     document := 'I am testing plain ' || document_id;
1011   end if;
1012 
1013 END;
1014 
1015 PROCEDURE get_action_history(document_id	in	varchar2,
1016                                  display_type	in	varchar2,
1017                                  document	in out	NOCOPY varchar2,
1018                                  document_type	in out	NOCOPY varchar2) IS
1019 
1020   l_item_type    wf_items.item_type%TYPE;
1021   l_item_key     wf_items.item_key%TYPE;
1022 
1023   l_document_id      po_requisition_lines.requisition_header_id%TYPE;
1024   l_object_type      po_action_history.object_type_code%TYPE;
1025   l_org_id           po_requisition_lines.org_id%TYPE;
1026 
1027   l_document         VARCHAR2(32000) := '';
1028 
1029   l_history          history_record;
1030 
1031   NL                 VARCHAR2(1) := fnd_global.newline;
1032 
1033   CURSOR history_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
1034 
1035     SELECT poh.SEQUENCE_NUM,
1036            per.FULL_NAME,
1037            polc.DISPLAYED_FIELD,
1038            poh.ACTION_DATE,
1039            poh.NOTE,
1040            poh.OBJECT_REVISION_NUM
1041       from po_action_history  poh,
1042            per_people_f       per,
1043            po_lookup_codes    polc
1044      where OBJECT_TYPE_CODE = v_object_type
1045        and poh.action_code = polc.lookup_code
1046        and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
1047        and per.person_id = poh.employee_id
1048        and trunc(sysdate) between per.effective_start_date
1049                               and per.effective_end_date
1050        and OBJECT_ID = v_document_id
1051     UNION ALL
1052     SELECT poh.SEQUENCE_NUM,
1053            per.FULL_NAME,
1054            NULL,
1055            poh.ACTION_DATE,
1056            poh.NOTE,
1057            poh.OBJECT_REVISION_NUM
1058       from po_action_history  poh,
1059            per_people_f       per
1060      where OBJECT_TYPE_CODE = 'REQUISITION'
1061        and poh.action_code is null
1062        and per.person_id = poh.employee_id
1063        and trunc(sysdate) between per.effective_start_date
1064                               and per.effective_end_date
1065        and OBJECT_ID = v_document_id
1066    order by 1 desc;
1067 
1068 BEGIN
1069 
1070   l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1071   l_item_key := substr(document_id, instr(document_id, ':') + 1,
1072                        length(document_id) - 2);
1073 
1074   l_document_id := wf_engine.GetItemAttrNumber
1075                                         (itemtype   => l_item_type,
1076                                          itemkey    => l_item_key,
1077                                          aname      => 'DOCUMENT_ID');
1078 
1079   l_org_id := wf_engine.GetItemAttrNumber
1080                                         (itemtype   => l_item_type,
1081                                          itemkey    => l_item_key,
1082                                          aname      => 'ORG_ID');
1083 
1084   PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ;         -- <R12 MOAC>
1085 
1086 
1087   if l_item_type = 'REQAPPRV' then
1088 
1089     l_object_type := 'REQUISITION';
1090 
1091   elsif l_item_type = 'POAPPRV' then
1092 
1093     null;
1094 
1095   end if;
1096 
1097   if (display_type = 'text/html') then
1098 
1099     l_document := NL || NL || '<!-- ACTION_HISTORY -->'|| NL || NL || '<P><B>';
1100     l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY') || NL;
1101     l_document := l_document || '</B>' || NL;
1102 
1103     l_document := l_document || '<TABLE border=1 cellpadding=2 cellspacing=1>' || NL;
1104 
1105     l_document := l_document || '<TR>';
1106 
1107     l_document := l_document || '<TH>' ||
1108                   fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') || '</TH>' || NL;
1109 
1110     l_document := l_document || '<TH>' ||
1111                   fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || '</TH>' || NL;
1112 
1113     l_document := l_document || '<TH>' ||
1114                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || '</TH>' || NL;
1115 
1116     l_document := l_document || '<TH>' ||
1117                   fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || '</TH>' || NL;
1118 
1119     l_document := l_document || '<TH>' ||
1120                   fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || '</TH>' || NL;
1121 
1122     l_document := l_document || '</TR>' || NL;
1123 
1124     open history_csr(l_document_id, l_object_type);
1125 
1126     loop
1127 
1128       fetch history_csr into l_history;
1129 
1130       exit when history_csr%notfound;
1131 
1132       l_document := l_document || '<TR>' || NL;
1133 
1134       l_document := l_document || '<TD nowrap align=center>' || nvl(to_char(l_history.seq_num), ' ') || '</TD>' || NL;
1135       l_document := l_document || '<TD nowrap>' || nvl(l_history.employee_name, ' ') || '</TD>' || NL;
1136       l_document := l_document || '<TD nowrap>' || nvl(l_history.action, ' ') || '</TD>' || NL;
1137       l_document := l_document || '<TD nowrap>' || nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
1138       l_document := l_document || '<TD nowrap>' || nvl(l_history.note, ' ') || '</TD>' || NL;
1139 
1140       l_document := l_document || '</TR>' || NL;
1141 
1142     end loop;
1143 
1144     close history_csr;
1145 
1146     l_document := l_document || '</TABLE></P>' || NL;
1147 
1148     document := l_document;
1149 
1150   elsif (display_type = 'text/plain') then
1151 
1152     document := '';
1153 
1154   end if;
1155 
1156 END;
1157 
1158 END PO_WF_DOCUMENT;