[Home] [Help]
PACKAGE BODY: APPS.PO_WF_REQ_NOTIFICATION
Source
1 PACKAGE BODY PO_WF_REQ_NOTIFICATION AS
2 /* $Header: POXWPA6B.pls 120.14.12000000.3 2007/05/04 00:01:49 lswamy ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 g_req_line_allowed_length number := 32000; -- Bug 3592883
8 -- Local procedure
9
10 PROCEDURE get_pending_action_html(p_item_type in varchar2,
11 p_item_key in varchar2,
12 max_seqno in number,
13 p_document out NOCOPY varchar2);
14 /* Bug 2480327
15 ** notification UI enhancement, adding l_notification_id in param.
16 */
17 function ConstructHeaderInfo(l_req_amount in varchar2,
18 l_currency_code in varchar2,
19 l_tax_amt in number,
20 l_tax_amount in varchar2,
21 l_description in varchar2,
22 l_forwarded_from in varchar2,
23 l_preparer in varchar2,
24 l_note in varchar2,
25 l_notification_id in number) return varchar2;
26
27 function print_heading(l_text in varchar2) return varchar2;
28
29 -- set context for calls to doc manager
30 procedure set_doc_mgr_context(itemtype VARCHAR2, itemkey VARCHAR2);
31
32 /* Bug# 2616355: kagarwal
33 ** Not using get_document_subtype_display or get_document_type_display
34 */
35 -- function get_document_subtype_display (l_subtype_code in varchar2) return varchar2;
36
37 -- function get_document_type_display (l_type_code in varchar2) return varchar2;
38
39 function is_po_approval_type(p_itemtype in varchar2, p_itemkey in varchar2)
40 return boolean;
41
42 procedure GetDisplayValue(itemtype in varchar2,
43 itemkey in varchar2,
44 username in varchar2);
45
46 TYPE line_record IS RECORD (
47
48 req_line_id po_requisition_lines.requisition_line_id%TYPE,
49 line_num po_requisition_lines.line_num%TYPE,
50 item_num mtl_system_items_kfv.concatenated_segments%TYPE,
51 item_revision po_requisition_lines.item_revision%TYPE,
52 item_desc po_requisition_lines.item_description%TYPE,
53 -- uom po_requisition_lines.unit_meas_lookup_code%TYPE, -- Bug 2401933.remove
54 uom mtl_units_of_measure.unit_of_measure_tl%TYPE, -- Bug 2401933.add
55 quantity po_requisition_lines.quantity%TYPE,
56 unit_price po_requisition_lines.unit_price%TYPE,
57 line_amount NUMBER,
58 need_by_date po_requisition_lines.need_by_date%TYPE,
59 location hr_locations.location_code%TYPE,
60 requestor per_people_f.full_name%TYPE,
61 sugg_supplier po_requisition_lines.suggested_vendor_name%TYPE,
62 sugg_site po_requisition_lines.suggested_vendor_location%TYPE,
63 txn_curr_code po_requisition_lines.currency_code%TYPE,
64 curr_unit_price po_requisition_lines.currency_unit_price%TYPE);
65
66 TYPE history_record IS RECORD (
67
68 seq_num po_action_history_v.sequence_num%TYPE,
69 employee_name po_action_history_v.employee_name%TYPE,
70 action po_action_history_v.action_code_dsp%TYPE,
71 action_date po_action_history_v.action_date%TYPE,
72 note po_action_history_v.note%TYPE,
73 revision po_action_history_v.object_revision_num%TYPE,
74 /* Bug 2788683 start */
75 employee_id po_action_history_v.employee_id%TYPE,
76 created_by po_action_history_v.created_by%TYPE,
77 /* Bug 2788683 end */
78 /* Bug 3090563 */
79 action_code po_action_history_v.action_code%TYPE
80 );
81
82 L_TABLE_STYLE VARCHAR2(100) := ' cellspacing="1" cellpadding="3" border="0" width="100%" ';
83
84 L_TABLE_HEADER_STYLE VARCHAR2(100) := ' class="tableheader" nowrap ';
85
86 L_TABLE_LABEL_STYLE VARCHAR2(100) := ' class="tableheaderright" nowrap align=right ';
87
88 L_TABLE_CELL_STYLE VARCHAR2(100) := ' class="tabledata" nowrap align=left ';
89
90 L_TABLE_CELL_WRAP_STYLE VARCHAR2(100) := ' class="tabledata" align=left ';
91
92 L_TABLE_CELL_RIGHT_STYLE VARCHAR2(100) := ' class="tabledata" nowrap align=right ';
93
94 L_TABLE_CELL_HIGH_STYLE VARCHAR2(100) := ' class="tabledatahighlight" nowrap align=left ';
95
96 /*******************************************************************
97 PROCEDURE NAME: is_foreign_currency_displayed
98
99 DESCRIPTION : This private function returns true if foreign currency
100 column needs to be displayed for Req Approval notifications
101
102 Referenced by : PO_WF_REQ_NOTIFICATION. This is invoked from
103 get_req_lines_details_link
104
105 parameters : p_document_id - This is requisition Header id
106 p_func_currency_code - This is functional currency
107
108 CHANGE History: Created 15-JAN-2003 jizhang
109 *******************************************************************/
110 function is_foreign_currency_displayed (p_document_id in number, p_func_currency_code in varchar2) return boolean;
111
112 /*******************************************************************
113 PROCEDURE NAME: get_item_info
114
115 DESCRIPTION : This procedure retrieves item_type, item_key and
116 notification id(if #nid is present)
117
118 Referenced by : PO_WF_REQ_NOTIFICATION
119 parameters : document_id - Document Identifier
120 itemtype - Workflow item type for Req approval
121 itemkey - Unique workflow item key
122 nid - Workflow id for current notification
123
124 CHANGE History: Created 15-JAN-2003 jizhang
125 *******************************************************************/
126 procedure get_item_info(document_id in varchar2,
127 itemtype out nocopy varchar2,
128 itemkey out nocopy varchar2,
129 nid out nocopy number);
130
131
132 /*******************************************************************
133 PROCEDURE NAME: get_total_for_text_msg
134
135 DESCRIPTION : This function finds the req total and
136 return the value with a displayable format specified by given currency.
137
138 Referenced by : PO_WF_REQ_NOTIFICATION
139 parameters :
140 itemtype - Workflow item type for Req approval
141 itemkey - Unique workflow item key
142 p_document_id - req header id
143 p_currency_code - currency in which format to be displayed
144
145 CHANGE History: Created 25-AUG-2003 jizhang
146 *******************************************************************/
147 function get_total_for_text_msg(itemtype in varchar2,
148 itemkey in varchar2,
149 p_document_id in number,
150 p_currency_code in varchar2)
151 return varchar2 is
152 l_req_amount number;
153 l_total_amount_disp varchar2(30);
154 l_tax_amount number;
155 l_total_amount number;
156 cursor req_total_csr(p_doc_id number) is
157 SELECT nvl(SUM(quantity * unit_price), 0)
158 FROM po_requisition_lines_all
159 WHERE requisition_header_id = p_doc_id
160 AND NVL(cancel_flag,'N') = 'N'
161 AND NVL(modified_by_agent_flag, 'N') = 'N';
162 cursor req_tax_csr(p_doc_id number) is
163 SELECT nvl(sum(nonrecoverable_tax), 0)
164 FROM po_requisition_lines_all rl,
165 po_req_distributions_all rd
166 WHERE rl.requisition_header_id = p_doc_id
167 AND rd.requisition_line_id = rl.requisition_line_id
168 AND NVL(rl.cancel_flag,'N') = 'N'
169 AND NVL(rl.modified_by_agent_flag, 'N') = 'N';
170
171 begin
172 OPEN req_total_csr(p_document_id);
173 FETCH req_total_csr into l_req_amount;
174 CLOSE req_total_csr;
175
176 OPEN req_tax_csr(p_document_id);
177 FETCH req_tax_csr into l_tax_amount;
178 CLOSE req_tax_csr;
179
180 l_total_amount := l_req_amount + l_tax_amount;
181
182 l_total_amount_disp := TO_CHAR(l_total_amount,FND_CURRENCY.GET_FORMAT_MASK(
183 p_currency_code,30));
184 return l_total_amount_disp;
185 end;
186
187 /* Bug #1581410 :kagarwal
188 ** Desc: The old html body code has been changed to use the new UI
189 ** and also added the requisiton_details and action history to the
190 ** this document for the html body.
191 **
192 ** For requisiton details this calls get_req_lines_details_link
193 ** and for action history get_action_history_html.
194 */
195
196 PROCEDURE get_po_req_approve_msg(document_id in varchar2,
197 display_type in varchar2,
198 document in out NOCOPY varchar2,
199 document_type in out NOCOPY varchar2) IS
200 max_seqno number;
201 l_item_type wf_items.item_type%TYPE;
202 l_item_key wf_items.item_key%TYPE;
203
204 l_document_id po_requisition_headers.requisition_header_id%TYPE;
205 l_org_id po_requisition_headers.org_id%TYPE;
206 -- l_document_subtype po_lookup_codes.displayed_field%TYPE;
207 l_document_type po_lookup_codes.displayed_field%TYPE;
208 l_document_number po_requisition_headers.segment1%TYPE;
209 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
210 l_total_amount VARCHAR2(30);
211 l_header_msg VARCHAR2(2225);
212 l_req_amount VARCHAR2(30);
213 l_tax_amount VARCHAR2(30);
214 l_description po_requisition_headers.description%TYPE;
215 l_forwarded_from per_people_f.full_name%TYPE;
216 l_preparer per_people_f.full_name%TYPE;
217 --<UTF-8 FPI START>
218 -- l_note VARCHAR2(480);
219 l_note po_action_history.note%TYPE;
220 --<UTF-8 FPI END>
221 l_document VARCHAR2(32000) := '';
222 l_tax_amt NUMBER;
223
224 l_document_2 VARCHAR2(32000) := '';
225 l_document_3 VARCHAR2(32000) := '';
226
227
228 NL VARCHAR2(1) := fnd_global.newline;
229
230 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
231
232 l_notification_id number;
233
234 BEGIN
235
236 /* Bug 2480327
237 ** notification UI enhancement
238 */
239 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
240
241 l_document_id := wf_engine.GetItemAttrNumber
242 (itemtype => l_item_type,
243 itemkey => l_item_key,
244 aname => 'DOCUMENT_ID');
245
246 l_org_id := wf_engine.GetItemAttrNumber
247 (itemtype => l_item_type,
248 itemkey => l_item_key,
249 aname => 'ORG_ID');
250
251 -- <BUG 3358245> Need to initialize the apps session, so that
252 -- employee details can be selected for the Action History.
253 --
254 -- Context setting Revamp
255 -- PO_REQAPPROVAL_INIT1.Set_doc_mgr_context (l_item_type,l_item_key);
256
257 /*
258 l_document_subtype := get_document_subtype_display(wf_engine.GetItemAttrText
259 (itemtype => l_item_type,
260 itemkey => l_item_key,
261 aname => 'DOCUMENT_SUBTYPE'));
262 */
263
264 /* Bug# 2616355
265 ** Get directly from wf DOCUMENT_TYPE_DISP attribute
266 */
267
268 l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
269 itemkey => l_item_key,
270 aname => 'DOCUMENT_TYPE_DISP');
271
272 /*
273 l_document_type := get_document_type_display(wf_engine.GetItemAttrText
274 (itemtype => l_item_type,
275 itemkey => l_item_key,
276 aname => 'DOCUMENT_TYPE'));
277
278 */
279
280 l_document_number := wf_engine.GetItemAttrText
281 (itemtype => l_item_type,
282 itemkey => l_item_key,
283 aname => 'DOCUMENT_NUMBER');
284
285 l_currency_code := wf_engine.GetItemAttrText
286 (itemtype => l_item_type,
287 itemkey => l_item_key,
288 aname => 'FUNCTIONAL_CURRENCY');
289
290 l_total_amount := wf_engine.GetItemAttrText
291 (itemtype => l_item_type,
292 itemkey => l_item_key,
293 aname => 'TOTAL_AMOUNT_DSP');
294
295 l_req_amount := wf_engine.GetItemAttrText
296 (itemtype => l_item_type,
297 itemkey => l_item_key,
298 aname => 'REQ_AMOUNT_DSP');
299
300 l_tax_amount := wf_engine.GetItemAttrText
301 (itemtype => l_item_type,
302 itemkey => l_item_key,
303 aname => 'TAX_AMOUNT_DSP');
304
305 l_description := wf_engine.GetItemAttrText
306 (itemtype => l_item_type,
307 itemkey => l_item_key,
308 aname => 'REQ_DESCRIPTION');
309
310 l_forwarded_from := wf_engine.GetItemAttrText
311 (itemtype => l_item_type,
312 itemkey => l_item_key,
313 aname => 'FORWARD_FROM_DISP_NAME');
314
315 l_preparer := wf_engine.GetItemAttrText
316 (itemtype => l_item_type,
317 itemkey => l_item_key,
318 aname => 'PREPARER_DISPLAY_NAME');
319
320 l_note := PO_WF_UTIL_PKG.GetItemAttrText
321 (itemtype => l_item_type,
322 itemkey => l_item_key,
323 aname => 'JUSTIFICATION');
324
325 if l_note is null then
326
327 l_note := wf_engine.GetItemAttrText
328 (itemtype => l_item_type,
329 itemkey => l_item_key,
330 aname => 'NOTE');
331
332 end if;
333
334 SELECT nvl(sum(nonrecoverable_tax), 0)
335 INTO l_tax_amt
336 FROM po_requisition_lines rl,
337 po_req_distributions_all rd -- <R12 MOAC>
338 WHERE rl.requisition_header_id = l_document_id
339 AND rd.requisition_line_id = rl.requisition_line_id;
340
341 if (display_type = 'text/html') then
342
343 /* Bug 2480327
344 ** notification UI enhancement
345 */
346 l_document := l_document || ConstructHeaderInfo(l_req_amount,
347 l_currency_code,
348 l_tax_amt,
349 l_tax_amount,
350 l_description,
351 l_forwarded_from,
352 l_preparer,
353 l_note,
354 l_notification_id);
355
356 -- Bug 3592883 Build the action history first and set the allowed length
357 l_document_2 := NULL;
358 get_action_history_html(document_id, display_type, l_document_2, document_type);
359
360 -- bug4502897
361 g_req_line_allowed_length := 32000 - nvl(lengthb(l_document),0) - nvl(lengthb(l_document_2),0);
362
363 l_document_3 := NULL;
364 get_req_lines_details_link(document_id, display_type, l_document_3, document_type);
365
366 l_document := l_document || l_document_3 ||l_document_2 ||NL ;
367
368 else -- Text message
369
370 /* bug 3090552
371 there is no longer a total in functional currency alone,
372 get it in function get_total_for_text_msg
373 */
374 l_total_amount := get_total_for_text_msg(itemtype => l_item_type,
375 itemkey => l_item_key,
376 p_document_id => l_document_id,
377 p_currency_code => l_currency_code);
378
379 if wf_engine.GetItemAttrText(itemtype => l_item_type,
380 itemkey => l_item_key,
381 aname => 'REQUIRES_APPROVAL_MSG') is not null then
382
383 l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVAL_MSG');
384
385 else
386
387 l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_INVALID_PERSON_MSG');
388
389 end if;
390
391 -- l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
392 l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
393 l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
394 l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
395
396 l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
397
398 l_document := l_document || l_header_msg || NL || NL;
399
400 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
401 l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
402
403 if l_tax_amt > 0 then
404
405 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
406 l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
407
408 end if;
409
410 if l_description is not null then
411 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
412 l_document := l_document || l_description || NL;
413 end if;
414
415 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_FORWARDED_FROM');
416 l_document := l_document || ' ' || l_forwarded_from || NL;
417
418 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
419 l_document := l_document || ' ' || l_preparer || NL;
420
421 if l_note is not null then
422 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
423 l_document := l_document || l_note || NL;
424 end if;
425
426 end if;
427
428 document := l_document;
429
430 END get_po_req_approve_msg;
431
432 /* Bug #1581410 :kagarwal
433 ** Desc: The old html body code has been changed to use the new UI
434 ** and also added the requisiton_details and action history to the
435 ** this document for the html body.
436 **
437 ** For requisiton details this calls get_req_lines_details_html
438 ** and for action history get_action_history_html.
439 */
440
441 PROCEDURE get_po_req_approved_msg(document_id in varchar2,
442 display_type in varchar2,
443 document in out NOCOPY varchar2,
444 document_type in out NOCOPY varchar2) IS
445 max_seqno number;
446 l_item_type wf_items.item_type%TYPE;
447 l_item_key wf_items.item_key%TYPE;
448
449 l_document_id po_requisition_headers.requisition_header_id%TYPE;
450 l_org_id po_requisition_headers.org_id%TYPE;
451 -- l_document_subtype po_lookup_codes.displayed_field%TYPE;
452 l_document_type po_lookup_codes.displayed_field%TYPE;
453 l_document_number po_requisition_headers.segment1%TYPE;
454 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
455 l_total_amount VARCHAR2(30);
456 l_header_msg VARCHAR2(200);
457 l_req_amount VARCHAR2(30);
458 l_tax_amount VARCHAR2(30);
459 l_description po_requisition_headers.description%TYPE;
460 l_approver per_people_f.full_name%TYPE;
461 l_preparer per_people_f.full_name%TYPE;
462 --<UTF-8 FPI START>
463 -- l_note VARCHAR2(480);
464 l_note po_action_history.note%TYPE;
465 --<UTF-8 FPI END>
466 l_document VARCHAR2(32000) := '';
467 l_tax_amt NUMBER;
468
469 l_warning_msg VARCHAR2(200);
470 l_attr_exist NUMBER := 0;
471
472 l_document_2 VARCHAR2(32000) := '';
473 l_document_3 VARCHAR2(32000) := '';
474
475 NL VARCHAR2(1) := fnd_global.newline;
476
477 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
478 l_notification_id number;
479
480 BEGIN
481
482 /* Bug 2480327
483 ** notification UI enhancement
484 */
485 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
486
487 l_document_id := wf_engine.GetItemAttrNumber
488 (itemtype => l_item_type,
489 itemkey => l_item_key,
490 aname => 'DOCUMENT_ID');
491
492 l_org_id := wf_engine.GetItemAttrNumber
493 (itemtype => l_item_type,
494 itemkey => l_item_key,
495 aname => 'ORG_ID');
496
497 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
498
499 /*
500 l_document_subtype := get_document_subtype_display(wf_engine.GetItemAttrText
501 (itemtype => l_item_type,
502 itemkey => l_item_key,
503 aname => 'DOCUMENT_SUBTYPE'));
504 */
505
506 /* Bug# 2616355
507 ** Get directly from wf DOCUMENT_TYPE_DISP attribute
508 */
509
510 l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
511 itemkey => l_item_key,
512 aname => 'DOCUMENT_TYPE_DISP');
513
514 /*
515 l_document_type := get_document_type_display(wf_engine.GetItemAttrText
516 (itemtype => l_item_type,
517 itemkey => l_item_key,
518 aname => 'DOCUMENT_TYPE'));
519 */
520
521 l_document_number := wf_engine.GetItemAttrText
522 (itemtype => l_item_type,
523 itemkey => l_item_key,
524 aname => 'DOCUMENT_NUMBER');
525
526 l_currency_code := wf_engine.GetItemAttrText
527 (itemtype => l_item_type,
528 itemkey => l_item_key,
529 aname => 'FUNCTIONAL_CURRENCY');
530
531 l_total_amount := wf_engine.GetItemAttrText
532 (itemtype => l_item_type,
533 itemkey => l_item_key,
534 aname => 'TOTAL_AMOUNT_DSP');
535
536 l_req_amount := wf_engine.GetItemAttrText
537 (itemtype => l_item_type,
538 itemkey => l_item_key,
539 aname => 'REQ_AMOUNT_DSP');
540
541 l_tax_amount := wf_engine.GetItemAttrText
542 (itemtype => l_item_type,
543 itemkey => l_item_key,
544 aname => 'TAX_AMOUNT_DSP');
545
546 l_description := wf_engine.GetItemAttrText
547 (itemtype => l_item_type,
548 itemkey => l_item_key,
549 aname => 'REQ_DESCRIPTION');
550
551 l_approver := wf_engine.GetItemAttrText
552 (itemtype => l_item_type,
553 itemkey => l_item_key,
554 aname => 'APPROVER_DISPLAY_NAME');
555
556 l_preparer := wf_engine.GetItemAttrText
557 (itemtype => l_item_type,
558 itemkey => l_item_key,
559 aname => 'PREPARER_DISPLAY_NAME');
560
561 l_note := PO_WF_UTIL_PKG.GetItemAttrText
562 (itemtype => l_item_type,
563 itemkey => l_item_key,
564 aname => 'JUSTIFICATION');
565
566 if l_note is null then
567
568 l_note := wf_engine.GetItemAttrText
569 (itemtype => l_item_type,
570 itemkey => l_item_key,
571 aname => 'NOTE');
572
573 end if;
574
575 /* Bug# 1666013: kagarwal
576 ** Desc: Display the Advisory warning message in the
577 ** Notification, when funds are reserved with Advisory warning.
578 **
579 ** First check if the attribute exists
580 */
581 begin
582 SELECT count(*) into l_attr_exist
583 FROM WF_ITEM_ATTRIBUTE_VALUES
584 WHERE ITEM_TYPE = l_item_type
585 AND ITEM_KEY = l_item_key
586 AND NAME = 'ADVISORY_WARNING';
587 exception
588 when others then null;
589 end;
590
591 if l_attr_exist > 0 then
592
593 l_warning_msg := wf_engine.GetItemAttrText
594 (itemtype => l_item_type,
595 itemkey => l_item_key,
596 aname => 'ADVISORY_WARNING');
597 end if;
598
599 SELECT nvl(sum(nonrecoverable_tax), 0)
600 INTO l_tax_amt
601 FROM po_requisition_lines rl,
602 po_req_distributions_all rd -- <R12 MOAC>
603 WHERE rl.requisition_header_id = l_document_id
604 AND rd.requisition_line_id = rl.requisition_line_id;
605
606 if (display_type = 'text/html') then
607
608 /* Bug 2480327
609 ** notification UI enhancement
610 */
611
612 l_document := l_document || ConstructHeaderInfo(l_req_amount,
613 l_currency_code,
614 l_tax_amt,
615 l_tax_amount,
616 l_description,
617 '',
618 l_preparer,
619 l_note,
620 l_notification_id);
621
622 /* Bug# 1666013 */
623 IF l_warning_msg is not null THEN
624 l_document := l_document || '<TABLE SUMMARY="">' || NL ||
625 '<TR><TD class="fielddatabold" align=left>' ||
626 l_warning_msg ||
627 '</TD></TR></TABLE>' || NL;
628
629 END IF;
630
631 -- Bug 3592883 Build the action history first and set the allowed length
632 l_document_2 := NULL;
633 get_action_history_html(document_id, display_type, l_document_2, document_type);
634
635 -- bug4502897
636 g_req_line_allowed_length := 32000 - nvl(lengthb(l_document),0) - nvl(lengthb(l_document_2),0);
637
638 l_document_3 := NULL;
639 get_req_lines_details_link(document_id, display_type, l_document_3, document_type);
640
641 l_document := l_document || l_document_3 || l_document_2 || NL ;
642
643 else -- Text message
644 /* bug 3090552
645 there is no longer a total in functional currency alone,
646 get it in function get_total_for_text_msg
647 */
648 l_total_amount := get_total_for_text_msg(itemtype => l_item_type,
649 itemkey => l_item_key,
650 p_document_id => l_document_id,
651 p_currency_code => l_currency_code);
652
653 l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVED');
654
655 -- l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
656 l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
657 l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
658 l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
659 l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
660
661 l_document := l_document || l_header_msg || NL || NL;
662
663 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
664 l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
665
666 if l_tax_amt > 0 then
667
668 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
669 l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
670
671 end if;
672
673 if l_description is not null then
674 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
675 l_document := l_document || l_description || NL;
676 end if;
677
678 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_APPROVER');
679 l_document := l_document || ' ' || l_approver || NL;
680
681 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
682 l_document := l_document || ' ' || l_preparer || NL;
683
684 if l_note is not null then
685 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
686 l_document := l_document || l_note || NL;
687 end if;
688
689 /* Bug# 1666013 */
690
691 if l_warning_msg is not null then
692 l_document := l_document || l_warning_msg || NL;
693 end if;
694
695 end if;
696
697 document := l_document;
698
699 END get_po_req_approved_msg;
700
701 /* Bug #1581410 :kagarwal
702 ** Desc: The old html body code has been changed to use the new UI
703 ** and also added the requisiton_details and action history to the
704 ** this document for the html body.
705 **
706 ** For requisiton details this calls get_req_lines_details_html
707 ** and for action history get_action_history_html.
708 */
709
710
711 PROCEDURE get_po_req_no_approver_msg(document_id in varchar2,
712 display_type in varchar2,
713 document in out NOCOPY varchar2,
714 document_type in out NOCOPY varchar2) IS
715 max_seqno number;
716 l_item_type wf_items.item_type%TYPE;
717 l_item_key wf_items.item_key%TYPE;
718
719 l_document_id po_requisition_headers.requisition_header_id%TYPE;
720 l_org_id po_requisition_headers.org_id%TYPE;
721 -- l_document_subtype po_lookup_codes.displayed_field%TYPE;
722 l_document_type po_lookup_codes.displayed_field%TYPE;
723 l_document_number po_requisition_headers.segment1%TYPE;
724 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
725 l_total_amount VARCHAR2(30);
726 l_header_msg VARCHAR2(200);
727 l_req_amount VARCHAR2(30);
728 l_tax_amount VARCHAR2(30);
729 l_description po_requisition_headers.description%TYPE;
730 l_approver per_people_f.full_name%TYPE;
731 l_preparer per_people_f.full_name%TYPE;
732 --<UTF-8 FPI START>
733 -- l_note VARCHAR2(480);
734 l_note po_action_history.note%TYPE;
735 --<UTF-8 FPI END>
736 l_document VARCHAR2(32000) := '';
737 l_tax_amt NUMBER;
738
739 l_document_2 VARCHAR2(32000) := '';
740 l_document_3 VARCHAR2(32000) := '';
741
742 NL VARCHAR2(1) := fnd_global.newline;
743
744 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
745 l_notification_id number;
746
747 BEGIN
748
749 /* Bug 2480327
750 ** notification UI enhancement
751 */
752 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
753
754 l_document_id := wf_engine.GetItemAttrNumber
755 (itemtype => l_item_type,
756 itemkey => l_item_key,
757 aname => 'DOCUMENT_ID');
758
759 l_org_id := wf_engine.GetItemAttrNumber
760 (itemtype => l_item_type,
761 itemkey => l_item_key,
762 aname => 'ORG_ID');
763
764 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
765
766 /*
767 l_document_subtype := get_document_subtype_display(wf_engine.GetItemAttrText
768 (itemtype => l_item_type,
769 itemkey => l_item_key,
770 aname => 'DOCUMENT_SUBTYPE'));
771 */
772 /* Bug# 2616355
773 ** Get directly from wf DOCUMENT_TYPE_DISP attribute
774 */
775
776 l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
777 itemkey => l_item_key,
778 aname => 'DOCUMENT_TYPE_DISP');
779
780 /*
781 l_document_type := get_document_type_display(wf_engine.GetItemAttrText
782 (itemtype => l_item_type,
783 itemkey => l_item_key,
784 aname => 'DOCUMENT_TYPE'));
785 */
786
787 l_document_number := wf_engine.GetItemAttrText
788 (itemtype => l_item_type,
789 itemkey => l_item_key,
790 aname => 'DOCUMENT_NUMBER');
791
792 l_currency_code := wf_engine.GetItemAttrText
793 (itemtype => l_item_type,
794 itemkey => l_item_key,
795 aname => 'FUNCTIONAL_CURRENCY');
796
797 l_total_amount := wf_engine.GetItemAttrText
798 (itemtype => l_item_type,
799 itemkey => l_item_key,
800 aname => 'TOTAL_AMOUNT_DSP');
801
802 l_req_amount := wf_engine.GetItemAttrText
803 (itemtype => l_item_type,
804 itemkey => l_item_key,
805 aname => 'REQ_AMOUNT_DSP');
806
807 l_tax_amount := wf_engine.GetItemAttrText
808 (itemtype => l_item_type,
809 itemkey => l_item_key,
810 aname => 'TAX_AMOUNT_DSP');
811
812 l_description := wf_engine.GetItemAttrText
813 (itemtype => l_item_type,
814 itemkey => l_item_key,
815 aname => 'REQ_DESCRIPTION');
816
817 l_approver := wf_engine.GetItemAttrText
818 (itemtype => l_item_type,
819 itemkey => l_item_key,
820 aname => 'APPROVER_DISPLAY_NAME');
821
822 l_preparer := wf_engine.GetItemAttrText
823 (itemtype => l_item_type,
824 itemkey => l_item_key,
825 aname => 'PREPARER_DISPLAY_NAME');
826
827 l_note := PO_WF_UTIL_PKG.GetItemAttrText
828 (itemtype => l_item_type,
829 itemkey => l_item_key,
830 aname => 'JUSTIFICATION');
831
832 if l_note is null then
833
834 l_note := wf_engine.GetItemAttrText
835 (itemtype => l_item_type,
836 itemkey => l_item_key,
837 aname => 'NOTE');
838
839 end if;
840
841 SELECT nvl(sum(nonrecoverable_tax), 0)
842 INTO l_tax_amt
843 FROM po_requisition_lines rl,
844 po_req_distributions_all rd -- <R12 MOAC>
845 WHERE rl.requisition_header_id = l_document_id
846 AND rd.requisition_line_id = rl.requisition_line_id;
847
848 if (display_type = 'text/html') then
849
850 /* Bug 2480327
851 ** notification UI enhancement
852 */
853 l_document := l_document || ConstructHeaderInfo(l_req_amount,
854 l_currency_code,
855 l_tax_amt,
856 l_tax_amount,
857 l_description,
858 '',
859 l_preparer,
860 l_note,
861 l_notification_id);
862
863 -- Bug 3592883 Build the action history first and set the allowed length
864 l_document_2 := NULL;
865 get_action_history_html(document_id, display_type, l_document_2, document_type);
866
867 -- bug4502897
868 g_req_line_allowed_length := 32000 - nvl(lengthb(l_document),0) - nvl(lengthb(l_document_2),0);
869
870 l_document_3 := NULL;
871 get_req_lines_details_link(document_id, display_type, l_document_3, document_type);
872
873 l_document := l_document || l_document_3 || l_document_2 || NL ;
874
875 else -- Text message
876 /* bug 3090552
877 there is no longer a total in functional currency alone,
878 get it in function get_total_for_text_msg
879 */
880 l_total_amount := get_total_for_text_msg(itemtype => l_item_type,
881 itemkey => l_item_key,
882 p_document_id => l_document_id,
883 p_currency_code => l_currency_code);
884
885 l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_NO_APPROVER');
886
887 -- l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
888 l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
889 l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
890 l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
891 l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
892
893 l_document := l_document || l_header_msg || NL || NL;
894
895 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
896 l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
897
898 if l_tax_amt > 0 then
899
900 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
901 l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
902
903 end if;
904
905 if l_description is not null then
906 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
907 l_document := l_document || l_description || NL;
908 end if;
909
910 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_LAST_APPROVER');
911 l_document := l_document || ' ' || l_approver || NL;
912
913 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
914 l_document := l_document || ' ' || l_preparer || NL;
915
916 if l_note is not null then
917 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
918 l_document := l_document || l_note || NL;
919 end if;
920
921 end if;
922
923 document := l_document;
924
925 END get_po_req_no_approver_msg;
926
927 /* Bug #1581410 :kagarwal
928 ** Desc: The old html body code has been changed to use the new UI
929 ** and also added the requisiton_details and action history to the
930 ** this document for the html body.
931 **
932 ** For requisiton details this calls get_req_lines_details_link
933 ** and for action history get_action_history_html.
934 */
935
936 PROCEDURE get_po_req_reject_msg(document_id in varchar2,
937 display_type in varchar2,
938 document in out NOCOPY varchar2,
939 document_type in out NOCOPY varchar2) IS
940 max_seqno number;
941 l_item_type wf_items.item_type%TYPE;
942 l_item_key wf_items.item_key%TYPE;
943
944 l_document_id po_requisition_headers.requisition_header_id%TYPE;
945 l_org_id po_requisition_headers.org_id%TYPE;
946 -- l_document_subtype po_lookup_codes.displayed_field%TYPE;
947 l_document_type po_lookup_codes.displayed_field%TYPE;
948 l_document_number po_requisition_headers.segment1%TYPE;
949 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
950 l_total_amount VARCHAR2(30);
951 l_header_msg VARCHAR2(200);
952 l_req_amount VARCHAR2(30);
953 l_tax_amount VARCHAR2(30);
954 l_description po_requisition_headers.description%TYPE;
955 l_rejected_by per_people_f.full_name%TYPE;
956 l_preparer per_people_f.full_name%TYPE;
957 --<UTF-8 FPI START>
958 -- l_note VARCHAR2(480);
959 l_note po_action_history.note%TYPE;
960 --<UTF-8 FPI END>
961 l_document VARCHAR2(32000) := '';
962 l_tax_amt NUMBER;
963
964 l_document_2 VARCHAR2(32000) := '';
965 l_document_3 VARCHAR2(32000) := '';
966
967 NL VARCHAR2(1) := fnd_global.newline;
968
969 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
970 l_notification_id number;
971
972 BEGIN
973
974 /* Bug 2480327
975 ** notification UI enhancement
976 */
977 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
978
979 l_document_id := wf_engine.GetItemAttrNumber
980 (itemtype => l_item_type,
981 itemkey => l_item_key,
982 aname => 'DOCUMENT_ID');
983
984 l_org_id := wf_engine.GetItemAttrNumber
985 (itemtype => l_item_type,
986 itemkey => l_item_key,
987 aname => 'ORG_ID');
988
989 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
990
991 /*
992 l_document_subtype := get_document_subtype_display(wf_engine.GetItemAttrText
993 (itemtype => l_item_type,
994 itemkey => l_item_key,
995 aname => 'DOCUMENT_SUBTYPE'));
996 */
997
998 /* Bug# 2616355
999 ** Get directly from wf DOCUMENT_TYPE_DISP attribute
1000 */
1001
1002 l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
1003 itemkey => l_item_key,
1004 aname => 'DOCUMENT_TYPE_DISP');
1005
1006 /*
1007 l_document_type := get_document_type_display(wf_engine.GetItemAttrText
1008 (itemtype => l_item_type,
1009 itemkey => l_item_key,
1010 aname => 'DOCUMENT_TYPE'));
1011 */
1012
1013 l_document_number := wf_engine.GetItemAttrText
1014 (itemtype => l_item_type,
1015 itemkey => l_item_key,
1016 aname => 'DOCUMENT_NUMBER');
1017
1018 l_currency_code := wf_engine.GetItemAttrText
1019 (itemtype => l_item_type,
1020 itemkey => l_item_key,
1021 aname => 'FUNCTIONAL_CURRENCY');
1022
1023 l_total_amount := wf_engine.GetItemAttrText
1024 (itemtype => l_item_type,
1025 itemkey => l_item_key,
1026 aname => 'TOTAL_AMOUNT_DSP');
1027
1028 l_req_amount := wf_engine.GetItemAttrText
1029 (itemtype => l_item_type,
1030 itemkey => l_item_key,
1031 aname => 'REQ_AMOUNT_DSP');
1032
1033 l_tax_amount := wf_engine.GetItemAttrText
1034 (itemtype => l_item_type,
1035 itemkey => l_item_key,
1036 aname => 'TAX_AMOUNT_DSP');
1037
1038 l_description := wf_engine.GetItemAttrText
1039 (itemtype => l_item_type,
1040 itemkey => l_item_key,
1041 aname => 'REQ_DESCRIPTION');
1042
1043 l_rejected_by := wf_engine.GetItemAttrText
1044 (itemtype => l_item_type,
1045 itemkey => l_item_key,
1046 aname => 'APPROVER_DISPLAY_NAME');
1047
1048 l_preparer := wf_engine.GetItemAttrText
1049 (itemtype => l_item_type,
1050 itemkey => l_item_key,
1051 aname => 'PREPARER_DISPLAY_NAME');
1052
1053 l_note := PO_WF_UTIL_PKG.GetItemAttrText
1054 (itemtype => l_item_type,
1055 itemkey => l_item_key,
1056 aname => 'JUSTIFICATION');
1057
1058 if l_note is null then
1059
1060 l_note := wf_engine.GetItemAttrText
1061 (itemtype => l_item_type,
1062 itemkey => l_item_key,
1063 aname => 'NOTE');
1064
1065 end if;
1066
1067 SELECT nvl(sum(nonrecoverable_tax), 0)
1068 INTO l_tax_amt
1069 FROM po_requisition_lines rl,
1070 po_req_distributions_all rd -- <R12 MOAC>
1071 WHERE rl.requisition_header_id = l_document_id
1072 AND rd.requisition_line_id = rl.requisition_line_id;
1073
1074 if (display_type = 'text/html') then
1075
1076 /* Bug 2480327
1077 ** notification UI enhancement
1078 */
1079 l_document := l_document || ConstructHeaderInfo(l_req_amount,
1080 l_currency_code,
1081 l_tax_amt,
1082 l_tax_amount,
1083 l_description,
1084 '',
1085 l_preparer,
1086 l_note,
1087 l_notification_id);
1088
1089 -- Bug 3592883 Build the action history first and set the allowed length
1090 l_document_2 := NULL;
1091 get_action_history_html(document_id, display_type, l_document_2, document_type);
1092
1093 -- bug4502897
1094 g_req_line_allowed_length := 32000 - nvl(lengthb(l_document),0) - nvl(lengthb(l_document_2),0);
1095
1096 l_document_3 := NULL;
1097 get_req_lines_details_link(document_id, display_type, l_document_3, document_type);
1098
1099 l_document := l_document || l_document_3 || l_document_2 || NL ;
1100
1101 else -- Text message
1102
1103 /* bug 3090552
1104 there is no longer a total in functional currency alone,
1105 get it in function get_total_for_text_msg
1106 */
1107 l_total_amount := get_total_for_text_msg(itemtype => l_item_type,
1108 itemkey => l_item_key,
1109 p_document_id => l_document_id,
1110 p_currency_code => l_currency_code);
1111
1112 l_header_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_REJECTED');
1113
1114 -- l_header_msg := replace(l_header_msg, '&DOCUMENT_SUBTYPE_DISP', l_document_subtype);
1115 l_header_msg := replace(l_header_msg, '&DOCUMENT_TYPE_DISP', l_document_type);
1116 l_header_msg := replace(l_header_msg, '&DOCUMENT_NUMBER', l_document_number);
1117 l_header_msg := replace(l_header_msg, '&FUNCTIONAL_CURRENCY', l_currency_code);
1118 l_header_msg := replace(l_header_msg, '&TOTAL_AMOUNT_DSP', l_total_amount);
1119
1120 l_document := l_document || l_header_msg || NL || NL;
1121
1122 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT');
1123 l_document := l_document || ' ' || l_currency_code || ' ' || l_req_amount || NL;
1124
1125 if l_tax_amt > 0 then
1126
1127 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT');
1128 l_document := l_document || ' ' || l_currency_code || ' ' || l_tax_amount || NL;
1129
1130 end if;
1131
1132 if l_description is not null then
1133 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL;
1134 l_document := l_document || l_description || NL;
1135 end if;
1136
1137 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_REJECTED_BY');
1138 l_document := l_document || ' ' || l_rejected_by || NL;
1139
1140 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER');
1141 l_document := l_document || ' ' || l_preparer || NL;
1142
1143 if l_note is not null then
1144 l_document := l_document || NL || fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || NL;
1145 l_document := l_document || l_note || NL;
1146 end if;
1147
1148 end if;
1149
1150 document := l_document;
1151
1152 END get_po_req_reject_msg;
1153
1154
1155 /* Bug #1581410 :kagarwal
1156 ** Desc: Commented the html body code, added return if display_type
1157 ** is 'text/html'.
1158 **
1159 ** For text body, added supplier information and also restricted
1160 ** the number of requisition lines to the max profile.
1161 */
1162
1163 PROCEDURE get_req_lines_details(document_id in varchar2,
1164 display_type in varchar2,
1165 document in out NOCOPY varchar2,
1166 document_type in out NOCOPY varchar2) IS
1167
1168 l_item_type wf_items.item_type%TYPE;
1169 l_item_key wf_items.item_key%TYPE;
1170
1171 l_document_id po_requisition_lines.requisition_header_id%TYPE;
1172 l_org_id po_requisition_lines.org_id%TYPE;
1173
1174 l_line line_record;
1175
1176 l_document VARCHAR2(32000) := '';
1177
1178 l_currency_code fnd_currencies.currency_code%TYPE;
1179
1180 NL VARCHAR2(1) := fnd_global.newline;
1181
1182 i number := 0;
1183
1184 display_txn_curr VARCHAR2(30);
1185
1186 /* Bug# 1470041: kagarwal
1187 ** Desc: Modified the cursor line_csr to get Req line details for the
1188 ** notifications in procedure get_req_lines_details() to ignore the Req
1189 ** lines modified using the modify option in the autocreate form.
1190 **
1191 ** Added condition:
1192 ** AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
1193 */
1194
1195 /* Bug 2401933: sktiwari
1196 Modifying cursor line_csr to return the translated UOM value
1197 instead of unit_meas_lookup_code.
1198 */
1199
1200 -- bug4963032
1201 -- Modified sql for better performance. Changes include:
1202 -- 1) Use hr_locations_all instead of hr_locations
1203 -- 2) the join between rql.destinatino_organization_id and msi.organization_id
1204 -- becomes an outer join
1205
1206 CURSOR line_csr(v_document_id NUMBER) IS
1207 SELECT rql.requisition_line_id,
1208 rql.line_num,
1209 msi.concatenated_segments,
1210 rql.item_revision,
1211 rql.item_description,
1212 -- rql.unit_meas_lookup_code, -- bug 2401933.remove
1213 nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code), -- bug 2401933.add
1214 rql.quantity,
1215 rql.unit_price,
1216 rql.quantity * rql.unit_price,
1217 rql.need_by_date,
1218 hrt.location_code,
1219 per.full_name,
1220 rql.suggested_vendor_name,
1221 rql.suggested_vendor_location,
1222 rql.currency_code,
1223 rql.currency_unit_price
1224 FROM po_requisition_lines rql,
1225 mtl_system_items_kfv msi,
1226 hr_locations_all hrt,
1227 mtl_units_of_measure muom, -- bug 2401933.add
1228 per_all_people_f per -- Bug 3404451
1229 WHERE rql.requisition_header_id = v_document_id
1230 AND NVL(rql.cancel_flag,'N') = 'N'
1231 AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
1232 AND hrt.location_id (+) = rql.deliver_to_location_id
1233 AND rql.item_id = msi.inventory_item_id(+)
1234 AND rql.destination_organization_id = msi.organization_id(+)
1235 AND rql.to_person_id = per.person_id(+)
1236 AND per.effective_start_date(+) <= trunc(sysdate)
1237 AND per.effective_end_date(+) >= trunc(sysdate)
1238 AND muom.unit_of_measure = rql.unit_meas_lookup_code -- bug 2401933.add
1239 ORDER BY rql.line_num;
1240
1241 l_notification_id number;
1242
1243 l_user_id number;
1244 l_responsibility_id number;
1245 l_application_id number;
1246
1247 BEGIN
1248
1249 /* Bug 2480327
1250 ** notification UI enhancement
1251 */
1252 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
1253
1254 /* Bug# 2377333
1255 ** Setting application context
1256 */
1257
1258 /* Bug 2606838
1259 ** Set the context only if it is not set already
1260 */
1261
1262 --FND_PROFILE.GET('USER_ID', l_user_id);
1263 --FND_PROFILE.GET('RESP_ID', l_responsibility_id);
1264 --FND_PROFILE.GET('RESP_APPL_ID', l_application_id);
1265 l_user_id := fnd_global.user_id;
1266 l_responsibility_id := fnd_global.resp_id;
1267 l_application_id := fnd_global.resp_appl_id;
1268
1269 IF (l_user_id = -1) THEN
1270 l_user_id := NULL;
1271 END IF;
1272
1273 IF (l_responsibility_id = -1) THEN
1274 l_responsibility_id := NULL;
1275 END IF;
1276
1277 IF (l_application_id = -1) THEN
1278 l_application_id := NULL;
1279 END IF;
1280
1281 --Context setting revamp
1282 /* IF ((l_user_id is NULL) OR (l_user_id = -1) OR
1283 (l_application_id is NULL) OR (l_application_id = -1) OR
1284 (l_responsibility_id is NULL) OR (l_responsibility_id = -1)) THEN
1285
1286 set_doc_mgr_context(l_item_type, l_item_key);
1287
1288 END IF; */
1289
1290 l_document_id := wf_engine.GetItemAttrNumber
1291 (itemtype => l_item_type,
1292 itemkey => l_item_key,
1293 aname => 'DOCUMENT_ID');
1294
1295 l_org_id := wf_engine.GetItemAttrNumber
1296 (itemtype => l_item_type,
1297 itemkey => l_item_key,
1298 aname => 'ORG_ID');
1299
1300 display_txn_curr := FND_PROFILE.value('POR_DEFAULT_DISP_TRANS_CURRENCY');
1301
1302 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
1303
1304 l_currency_code := PO_CORE_S2.get_base_currency;
1305
1306 if (display_type = 'text/html') then
1307
1308 return;
1309
1310 else
1311
1312 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS') || NL || NL;
1313
1314 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DTLS_DESP') || NL;
1315
1316 open line_csr(l_document_id);
1317
1318 loop
1319
1320 fetch line_csr into l_line;
1321
1322 /* kagarwal: Limit the number of lines to 5 */
1323
1324 i := i + 1;
1325
1326 exit when line_csr%notfound;
1327
1328 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ':' || to_char(l_line.line_num) || NL;
1329 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || ': ' || l_line.item_num || NL;
1330 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || ': ' || l_line.item_revision || NL;
1331 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || ': ' || l_line.item_desc || NL;
1332 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' || l_line.uom || NL;
1333 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' || to_char(l_line.quantity) || NL;
1334
1335 /* display the transaction currency in the notification if the profile is set */
1336 IF (display_txn_curr = 'Y' AND
1337 l_line.txn_curr_code is not null AND
1338 l_currency_code <> l_line.txn_curr_code) THEN
1339
1340 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
1341 || to_char(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30))
1342 || ' ' || l_currency_code || ' ('
1343 || to_char(l_line.curr_unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_line.txn_curr_code, 30))
1344 || ' ' || l_line.txn_curr_code || ')' || NL;
1345
1346 ELSE
1347 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
1348 || to_char(l_line.unit_price, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL;
1349 END IF;
1350
1351
1352
1353 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || ': '
1354 || to_char(l_line.line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL || NL;
1355
1356 exit when i = 5;
1357
1358 end loop;
1359
1360 close line_csr;
1361
1362 end if;
1363
1364 document := l_document;
1365
1366 END get_req_lines_details;
1367
1368 /* Bug #1581410 :kagarwal
1369 ** Desc: This procedure is added for the new UI and is called only
1370 ** by get_po_req_approve_msg and get_po_req_reject_msg messages for
1371 ** the html body. It also creates 'View Requisition Details' and
1372 ** 'Edit Requisition' links in the message body.
1373 **
1374 ** Note: Please do not call this independently otherwise the layout
1375 ** will not be good.
1376 */
1377
1378 PROCEDURE get_req_lines_details_link(document_id in varchar2,
1379 display_type in varchar2,
1380 document in out NOCOPY varchar2,
1381 document_type in out NOCOPY varchar2) IS
1382 nsegments number;
1383 l_segments fnd_flex_ext.SegmentArray;
1384 l_cost_center VARCHAR2(200);
1385 l_segment_num number;
1386 l_column_name VARCHAR2(20);
1387
1388 cc_Id number;
1389
1390 cost_center_1 VARCHAR2(200);
1391
1392 l_account_id number;
1393 dist_num number;
1394 multiple_cost_center VARCHAR2(100):= '';
1395
1396
1397 l_item_type wf_items.item_type%TYPE;
1398 l_item_key wf_items.item_key%TYPE;
1399
1400 l_document_id po_requisition_lines.requisition_header_id%TYPE;
1401 l_org_id po_requisition_lines.org_id%TYPE;
1402
1403 l_line line_record;
1404 l_disp_item VARCHAR2(1);
1405
1406 l_num_lines NUMBER := 0;
1407
1408 l_max_lines NUMBER := 0;
1409
1410 l_document VARCHAR2(32000) := '';
1411
1412 l_req_status po_requisition_headers.authorization_status%TYPE;
1413
1414 l_req_details_url VARCHAR2(2000) := '';
1415 l_req_line_msg VARCHAR2(2000) := '';
1416 l_req_updates_url VARCHAR2(2000) := '';
1417
1418 -- Bug 3592883
1419 l_document_pre_lmt VARCHAR2(4000) := '';
1420 l_document_post_lmt VARCHAR2(4000) := '';
1421 l_document_Summary VARCHAR2(32000) := '';
1422
1423 l_currency_code fnd_currencies.currency_code%TYPE;
1424
1425 NL VARCHAR2(1) := fnd_global.newline;
1426
1427 i number := 0;
1428
1429 display_txn_curr VARCHAR2(30);
1430 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
1431
1432 /* this variable is true when
1433 1) display_txn_curr is 'Y'
1434 2) there is at least a line has foreign currency
1435 */
1436 l_display_currency_price_cell boolean;
1437
1438 /* Bug# 1470041: kagarwal
1439 ** Desc: Modified the cursor line_csr to get Req line details for the
1440 ** notifications in procedure get_req_lines_details() to ignore the Req
1441 ** lines modified using the modify option in the autocreate form.
1442 **
1443 ** Added condition:
1444 ** AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
1445 */
1446
1447 /* Bug 2401933: sktiwari
1448 Modifying cursor line_csr to return the translated UOM value
1449 instead of unit_meas_lookup_code.
1450 */
1451
1452 -- bug4963032
1453 -- Modified sql for better performance. Changes include:
1454 -- 1) Use hr_locations_all instead of hr_locations
1455 -- 2) the join between rql.destinatino_organization_id and msi.organization_id
1456 -- becomes an outer join
1457
1458
1459 CURSOR line_csr(v_document_id NUMBER) IS
1460 SELECT rql.requisition_line_id,
1461 rql.line_num,
1462 msi.concatenated_segments,
1463 rql.item_revision,
1464 rql.item_description,
1465 -- rql.unit_meas_lookup_code, -- bug 2401933.remove
1466 nvl(muom.unit_of_measure_tl, rql.unit_meas_lookup_code), -- bug 2401933.add
1467 rql.quantity,
1468 rql.unit_price,
1469 rql.quantity * rql.unit_price,
1470 rql.need_by_date,
1471 hrt.location_code,
1472 per.full_name,
1473 decode(rql.source_type_code,'VENDOR', rql.suggested_vendor_name, org.organization_code ||' - '|| org.organization_name),
1474 decode(rql.source_type_code, 'VENDOR',rql.suggested_vendor_location,''),
1475 rql.currency_code,
1476 rql.currency_unit_price
1477 FROM po_requisition_lines rql,
1478 mtl_system_items_kfv msi,
1479 hr_locations_all hrt,
1480 per_all_people_f per, -- Bug 3404451
1481 mtl_units_of_measure muom, -- bug 2401933.add
1482 org_organization_definitions org
1483 WHERE rql.requisition_header_id = v_document_id
1484 AND NVL(rql.cancel_flag,'N') = 'N'
1485 AND NVL(rql.modified_by_agent_flag, 'N') = 'N'
1486 AND hrt.location_id (+) = rql.deliver_to_location_id
1487 AND rql.item_id = msi.inventory_item_id(+)
1488 AND rql.destination_organization_id = msi.organization_id(+)
1489 AND rql.to_person_id = per.person_id(+)
1490 AND per.effective_start_date(+) <= trunc(sysdate)
1491 AND per.effective_end_date(+) >= trunc(sysdate)
1492 AND rql.source_organization_id = org.organization_id (+)
1493 AND muom.unit_of_measure(+) = rql.unit_meas_lookup_code
1494 ORDER BY rql.line_num;
1495
1496 CURSOR ccId_csr(req_line_id NUMBER) IS
1497 SELECT CODE_COMBINATION_ID
1498 FROM PO_REQ_DISTRIBUTIONS_ALL
1499 WHERE REQUISITION_LINE_ID = req_line_id;
1500
1501 l_notification_id number;
1502
1503 l_user_id number;
1504 l_responsibility_id number;
1505 l_application_id number;
1506
1507 BEGIN
1508
1509 /* Bug 2480327
1510 ** notification UI enhancement
1511 */
1512 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
1513
1514 l_document_id := wf_engine.GetItemAttrNumber
1515 (itemtype => l_item_type,
1516 itemkey => l_item_key,
1517 aname => 'DOCUMENT_ID');
1518
1519 l_org_id := wf_engine.GetItemAttrNumber
1520 (itemtype => l_item_type,
1521 itemkey => l_item_key,
1522 aname => 'ORG_ID');
1523
1524 /* Bug# 3107936: kagarwal
1525 ** Desc: If the wf attribute DISPLAY_ITEM is set to 'Y', then
1526 ** we need to display the Item Number and Revision in the
1527 ** Notification details
1528 **
1529 ** If the attribute is not present, it will be treated as 'N'.
1530 */
1531
1532 l_disp_item := PO_WF_UTIL_PKG.GetItemAttrText
1533 (itemtype => l_item_type,
1534 itemkey => l_item_key,
1535 aname => 'DISPLAY_ITEM');
1536
1537 /* Bug 2606838
1538 ** Set the context only if it is not set already
1539 */
1540 --FND_PROFILE.GET('USER_ID', l_user_id);
1541 --FND_PROFILE.GET('RESP_ID', l_responsibility_id);
1542 --FND_PROFILE.GET('RESP_APPL_ID', l_application_id);
1543 l_user_id := fnd_global.user_id;
1544 l_responsibility_id := fnd_global.resp_id;
1545 l_application_id := fnd_global.resp_appl_id;
1546
1547 IF (l_user_id = -1) THEN
1548 l_user_id := NULL;
1549 END IF;
1550
1551 IF (l_responsibility_id = -1) THEN
1552 l_responsibility_id := NULL;
1553 END IF;
1554
1555 IF (l_application_id = -1) THEN
1556 l_application_id := NULL;
1557 END IF;
1558
1559 /* IF ((l_user_id is NULL) OR (l_user_id = -1) OR
1560 (l_application_id is NULL) OR (l_application_id = -1) OR
1561 (l_responsibility_id is NULL) OR (l_responsibility_id = -1)) THEN
1562
1563 set_doc_mgr_context(l_item_type, l_item_key);
1564
1565 END IF; */
1566
1567 display_txn_curr := FND_PROFILE.value('POR_DEFAULT_DISP_TRANS_CURRENCY');
1568
1569 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
1570
1571
1572 multiple_cost_center := fnd_message.get_string('PO', 'PO_WF_NOTIF_MULTIPLE');
1573
1574 l_currency_code := PO_CORE_S2.get_base_currency;
1575
1576 /* Bug 2480327
1577 ** notification UI enhancement
1578 */
1579
1580 if(display_txn_curr = 'Y') then
1581 l_display_currency_price_cell := is_foreign_currency_displayed (l_document_id, l_currency_code);
1582 else
1583 l_display_currency_price_cell := false;
1584 end if;
1585
1586 begin
1587
1588 select fs.segment_num, gls.chart_of_accounts_id
1589 into l_segment_num, l_account_id
1590 from FND_ID_FLEX_SEGMENTS fs,
1591 fnd_segment_attribute_values fsav,
1592 financials_system_parameters fsp,
1593 gl_sets_of_books gls
1594 where fsp.set_of_books_id = gls.set_of_books_id and
1595 fsav.id_flex_num = gls.chart_of_accounts_id and
1596 fsav.id_flex_code = 'GL#' and
1597 fsav.application_id = 101 and
1598 fsav.segment_attribute_type = 'FA_COST_CTR' and
1599 fsav.id_flex_num = fs.id_flex_num and
1600 fsav.id_flex_code = fs.id_flex_code and
1601 fsav.application_id = fs.application_id and
1602 fsav.application_column_name = fs.application_column_name and
1603 fsav.attribute_value='Y';
1604
1605 exception
1606 when others then
1607 l_segment_num := -1;
1608 end;
1609
1610 if (display_type = 'text/html') then
1611
1612 l_document := l_document || NL || NL || '<!-- REQ_LINE_DETAILS -->'|| NL || NL || '<P>';
1613
1614 l_document := l_document || print_heading(fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS'));
1615 -- Bug 3592883
1616 l_document_pre_lmt := l_document;
1617 l_document := null;
1618 g_req_line_allowed_length := g_req_line_allowed_length - nvl(lengthb(l_document_pre_lmt),0);
1619
1620 l_max_lines := to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
1621
1622 /* Bug# 2720551: kagarwal
1623 ** Desc: Modified the select to only count lines that are not cancelled
1624 */
1625
1626 select count(1)
1627 into l_num_lines
1628 from po_requisition_lines
1629 where requisition_header_id = l_document_id
1630 AND NVL(cancel_flag,'N') = 'N'
1631 AND NVL(modified_by_agent_flag, 'N') = 'N';
1632
1633 -- Bug 3592883
1634 -- Construct this message always.
1635 -- if l_num_lines > l_max_lines then
1636
1637 l_document := l_document || '<TABLE width="100%" SUMMARY="">' || NL;
1638
1639 l_document := l_document || '<TR>'|| NL;
1640
1641 /* Bug# 2720551: kagarwal
1642 ** Desc If iProcurement is not installed, the message displayed
1643 ** for line information will be PO_WF_NTF_LINE_DET_NO_SSP_DSP. This
1644 ** message does not refer to View Requisition Link but to Open Document
1645 ** icon for additional line details.
1646 */
1647
1648 l_req_line_msg := fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_DETAILS_DESP');
1649
1650 ELSE
1651 l_req_line_msg := fnd_message.get_string('PO', 'PO_WF_NTF_LINE_DET_NO_SSP_DSP');
1652 END IF;
1653
1654 -- l_req_line_msg := replace(l_req_line_msg, '&LIMIT', to_char(l_max_lines));
1655 -- Bug 3592883 DO NOT replace the limit now.
1656
1657 l_req_line_msg := '<TD class=instructiontext>'||
1658 l_req_line_msg;
1659
1660 l_document := l_document || l_req_line_msg || NL ;
1661
1662
1663 l_document := l_document || '</TD></TR>' || NL;
1664
1665 l_document := l_document || '</TABLE>' || NL;
1666
1667 -- Bug 3592883
1668 l_req_line_msg := l_document;
1669 l_document := null;
1670 g_req_line_allowed_length := g_req_line_allowed_length - nvl(lengthb(l_req_line_msg),0);
1671
1672 -- Now Construct the lines
1673 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || 'summary="' || fnd_message.get_string('ICX','ICX_POR_TBL_REQ_TO_APPROVE_SUM') || '"> '|| NL;
1674
1675 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=3% id="lineNum_1">' ||
1676 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
1677
1678 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=30% id="itemDesc_1">' ||
1679 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TH>' || NL;
1680
1681 If(l_disp_item = 'Y') Then
1682 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE ||
1683 ' width=15% id ="item_1">' ||
1684 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') ||
1685 '</TH>' || NL;
1686
1687 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE ||
1688 ' width=3% id ="itemRev_1">' ||
1689 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') ||
1690 '</TH>' || NL;
1691
1692 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=15% id="supplier_1">' ||
1693 fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER') || '</TH>' || NL;
1694
1695 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="costCenter_1">' ||
1696 fnd_message.get_string('PO', 'PO_WF_NOTIF_COST_CENTER') || '</TH>' || NL;
1697
1698 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="UOM_1">' ||
1699 fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TH>' || NL;
1700
1701 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=8% id="quant_1">' ||
1702 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
1703
1704 /* Bug 2480327
1705 ** notification UI enhancement
1706 */
1707 IF (l_display_currency_price_cell = true) THEN
1708 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="transactionPrice_1">' ||
1709 fnd_message.get_string('PO', 'PO_WF_NOTIF_TRANS_PRICE') || '</TH>' || NL;
1710 END IF;
1711
1712 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% id="unitPrice_1">' ||
1713 fnd_message.get_string('PO', 'PO_WF_NOTIF_PRICE') ||
1714 ' ' || '(' || l_currency_code || ')'|| '</TH>' || NL;
1715
1716 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=10% nowrap id="lineAmt_1">' ||
1717 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') ||
1718 ' ' || '(' || l_currency_code || ')' || '</TH>' || NL;
1719
1720 l_document := l_document || '</TR>' || NL;
1721
1722 open line_csr(l_document_id);
1723
1724 loop
1725
1726 fetch line_csr into l_line;
1727 exit when line_csr%notfound;
1728 -- Bug 3592883 Increase i After the exit stmt.
1729 i := i + 1;
1730
1731 begin
1732
1733 if l_segment_num = -1 then
1734 l_cost_center := '';
1735 else
1736
1737 l_cost_center := 'SINGLE';
1738
1739 dist_num := 1;
1740
1741 open ccId_csr(l_line.req_line_id);
1742 loop
1743 fetch ccId_csr into cc_Id;
1744 exit when ccid_csr%notfound;
1745
1746 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) then
1747 l_cost_center := l_segments(l_segment_num);
1748 else
1749 l_cost_center := '';
1750 end if;
1751
1752 if dist_num = 1 then
1753 cost_center_1 := l_cost_center;
1754 dist_num := 2;
1755 else
1756 if l_cost_center <> cost_center_1 then
1757 l_cost_center := multiple_cost_center;
1758 exit;
1759 end if;
1760 end if;
1761 end loop;
1762 close ccId_csr;
1763
1764 if l_cost_center <> multiple_cost_center then
1765 if fnd_flex_ext.get_segments( 'SQLGL','GL#', l_account_id,cc_id,nsegments,l_segments) then
1766 l_cost_center := l_segments(l_segment_num);
1767 else
1768 l_cost_center := '';
1769 end if;
1770 end if;
1771
1772 end if; --if l_segment_num = -1
1773
1774 exception --any exception while retrieving the cost center
1775 when others then
1776 l_cost_center := '';
1777 end;
1778
1779
1780 l_document := l_document || '<TR>' || NL;
1781
1782 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="lineNum_1">' ||
1783 nvl(to_char(l_line.line_num), ' ') || '</TD>' || NL;
1784
1785 l_document := l_document || '<TD ' || L_TABLE_CELL_WRAP_STYLE || ' headers="itemDesc_1">' ||
1786 nvl(l_line.item_desc, ' ') || '</TD>' || NL;
1787
1788 If(l_disp_item = 'Y') Then
1789 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE ||
1790 ' headers="item_1">' ||nvl(l_line.item_num, ' ')
1791 || '</TD>' || NL;
1792
1793 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE ||
1794 ' headers="itemRev_1">' ||
1795 nvl(l_line.item_revision, ' ') || '</TD>' || NL;
1796 End If;
1797
1798 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="supplier_1">' ||
1799 nvl(l_line.sugg_supplier, ' ') || '</TD>' || NL;
1800
1801 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="costCenter_1">' ||
1802 nvl(l_cost_center, ' ') || '</TD>' || NL;
1803
1804 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="UOM_1">' ||
1805 nvl(l_line.uom, ' ') || '</TD>' || NL;
1806
1807 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' align=left headers="quant_1">' ||
1808 nvl(to_char(l_line.quantity), ' ') || '</TD>' || NL;
1809
1810 /* Bug 2480327
1811 ** notification UI enhancement
1812 */
1813 /* Bug 2784325
1814 Used the currency format mask to get the correct precision and format mask */
1815 /* Bug 2908444 Reverting the fix 2784325. We will not format the unit price */
1816
1817
1818 IF (l_display_currency_price_cell = true) THEN
1819 IF (
1820 l_line.txn_curr_code is not null AND
1821 l_currency_code <> l_line.txn_curr_code) THEN
1822
1823 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="transactionPrice_1">' ||
1824 PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_line.txn_curr_code,l_line.curr_unit_price) || ' ' || l_line.txn_curr_code || '</TD>' || NL;
1825 ELSE
1826 /* this line does not have foreign currency, display a blank cell */
1827 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' headers="transactionPrice_1">' || ' ' || '</TD>' || NL;
1828 END IF;
1829 END IF;
1830
1831 l_document := l_document || '<TD ' || L_TABLE_CELL_RIGHT_STYLE || ' headers="unitPrice_1">' ||
1832 PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_currency_code,l_line.unit_price ) || '</TD>' || NL;
1833
1834 l_document := l_document || '<TD ' || L_TABLE_CELL_RIGHT_STYLE || ' headers="lineAmt_1">' ||
1835 TO_CHAR(l_line.line_amount, FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) ||
1836 '</TD>' || NL;
1837
1838 l_document := l_document || '</TR>' || NL;
1839
1840 -- Bug 3592883
1841 g_req_line_allowed_length := g_req_line_allowed_length - nvl(lengthb(l_document),0);
1842 if (g_req_line_allowed_length > 100 ) then
1843 l_document_Summary := l_document_Summary||l_document;
1844 l_document := null;
1845 exit when i = l_max_lines;
1846 else
1847 i := i-1;
1848 exit;
1849 end if;
1850 -- Bug 3592883
1851 end loop;
1852
1853 close line_csr;
1854
1855 l_document_summary := l_document_summary || '</TABLE>';
1856
1857 -- Construct the links
1858 end if;
1859
1860 -- Bug 3592883
1861 if i < l_num_lines then
1862 l_req_line_msg := replace(l_req_line_msg, '&LIMIT', to_char(i));
1863 document := l_document_pre_lmt||l_req_line_msg||l_document_Summary;
1864 else
1865 document := l_document_pre_lmt||l_document_Summary;
1866 end if;
1867
1868 END get_req_lines_details_link;
1869
1870
1871 /* Bug #1581410 :kagarwal
1872 ** Desc: This procedure has been added for the new UI. This is
1873 ** called by all the messages using the new UI for the html body.
1874 **
1875 ** Note: Please do not call this independently otherwise the layout
1876 ** will not be good.
1877 */
1878 PROCEDURE get_action_history_html(document_id in varchar2,
1879 display_type in varchar2,
1880 document in out NOCOPY varchar2,
1881 document_type in out NOCOPY varchar2) IS
1882
1883
1884 l_item_type wf_items.item_type%TYPE;
1885 l_item_key wf_items.item_key%TYPE;
1886
1887 l_document_id po_requisition_lines.requisition_header_id%TYPE;
1888 l_object_type po_action_history.object_type_code%TYPE;
1889 l_org_id po_requisition_lines.org_id%TYPE;
1890
1891 l_document VARCHAR2(32000) := '';
1892 l_document_hist VARCHAR2(32000) := '';
1893 l_document_pend VARCHAR2(32000) := '';
1894
1895 l_history history_record;
1896 l_history_seq number;
1897
1898 MAX_SEQNO number := 0;
1899
1900 NL VARCHAR2(1) := fnd_global.newline;
1901
1902 --SQL What: Query action history which is updated by both buyer and vendor
1903 --SQL Why: Since vendor doesn't have employee id, added outer join;
1904 CURSOR history_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
1905
1906 SELECT poh.SEQUENCE_NUM,
1907 per.FULL_NAME,
1908 polc.DISPLAYED_FIELD,
1909 poh.ACTION_DATE,
1910 poh.NOTE,
1911 poh.OBJECT_REVISION_NUM,
1912 poh.employee_id, /* bug 2788683 */
1913 poh.created_by, /* bug 2788683 */
1914 poh.action_code /* bug 3090563 */
1915 from po_action_history poh,
1916 per_all_people_f per, -- Bug 3404451
1917 po_lookup_codes polc
1918 where OBJECT_TYPE_CODE = v_object_type
1919 and nvl(poh.action_code, 'PENDING') = polc.lookup_code
1920 and POLC.LOOKUP_TYPE = 'APPR_HIST_ACTIONS'
1921 and per.person_id(+) = poh.employee_id /* bug 2788683 */
1922 and trunc(sysdate) between per.effective_start_date(+)
1923 and per.effective_end_date(+)
1924 and OBJECT_ID = v_document_id
1925 order by 1 asc; /* bug 3090563 reverse display order */
1926 l_notification_id number;
1927
1928 /* Bug 2788683 start */
1929 l_user_name fnd_user.user_name%TYPE;
1930 l_vendor_name hz_parties.party_name%TYPE;
1931 l_party_name hz_parties.party_name%TYPE;
1932 /* Bug 2788683 end */
1933
1934
1935 BEGIN
1936
1937 /* Bug 2480327
1938 ** notification UI enhancement
1939 */
1940 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
1941
1942 l_document_id := wf_engine.GetItemAttrNumber
1943 (itemtype => l_item_type,
1944 itemkey => l_item_key,
1945 aname => 'DOCUMENT_ID');
1946
1947 l_org_id := wf_engine.GetItemAttrNumber
1948 (itemtype => l_item_type,
1949 itemkey => l_item_key,
1950 aname => 'ORG_ID');
1951
1952 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
1953
1954 l_object_type := 'REQUISITION';
1955
1956 if (display_type = 'text/html') then
1957
1958 l_document := NL || NL || '<!-- ACTION_HISTORY -->'|| NL || NL || '<P>';
1959
1960 l_document := l_document || print_heading(fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY'));
1961
1962 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || ' summary="' || fnd_message.get_string('ICX', 'ICX_POR_TBL_OF_APPROVERS') || '">' || NL;
1963
1964 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="seqNum_3"> </TH>' || NL;
1965
1966 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=20% id="employee_3">' ||
1967 fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || '</TH>' || NL;
1968
1969 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=12% id="action_3">' ||
1970 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || '</TH>' || NL;
1971
1972 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=12% id="date_3">' ||
1973 fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || '</TH>' || NL;
1974
1975 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=35% id="actionNote_3">' ||
1976 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || '</TH>' || NL;
1977
1978 l_document := l_document || '</TR>' || NL;
1979
1980 open history_csr(l_document_id, l_object_type);
1981 loop
1982
1983 fetch history_csr into l_history;
1984
1985 exit when history_csr%notfound;
1986
1987 max_seqno := max_seqno + 1;
1988 l_history_seq := l_history.seq_num + 1;
1989
1990 /* bug 3090563 change check to action_code */
1991 IF (l_history.action_code is not NULL) THEN
1992
1993 l_document_hist := l_document_hist || NL || '<TR>' || NL;
1994
1995 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="seqNum_3">' ||
1996 nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
1997
1998 /* Bug 2788683 start */
1999 /* if action history is updated by vendor
2000 * show vendor true name(vendor name)
2001 * else action history is updated by buyer
2002 * show buyer's true name
2003 */
2004 IF l_history.employee_id IS NULL THEN
2005 SELECT fu.user_name, hp.party_name
2006 INTO l_user_name, l_party_name
2007 FROM fnd_user fu,
2008 hz_parties hp
2009 WHERE hp.party_id = fu.customer_id
2010 AND fu.user_id = l_history.created_by;
2011
2012 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
2013
2014 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' || l_party_name || '(' || l_vendor_name || ')' || '</TD>' || NL;
2015 ELSE
2016 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' ||
2017 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
2018 END IF;
2019 /* Bug 2788683 end */
2020
2021 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="action_3">' ||
2022 nvl(l_history.action, ' ') || '</TD>' || NL;
2023
2024 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="date_3">' ||
2025 nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
2026
2027 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_WRAP_STYLE || ' headers="actionNote_3">' ||
2028 nvl(l_history.note, ' ') || '</TD>' || NL;
2029
2030 l_document_hist := l_document_hist || '</TR>' || NL;
2031
2032 ELSE
2033
2034 l_document_hist := l_document_hist || NL || '<TR>' || NL;
2035
2036 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="seqNum_3">' ||
2037 nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
2038
2039 /* Bug 2788683 start */
2040 /* if action history is updated by vendor
2041 * show vendor true name(vendor name)
2042 * else action history is updated by buyer
2043 * show buyer's true name
2044 */
2045 IF l_history.employee_id IS NULL THEN
2046 SELECT fu.user_name, hp.party_name
2047 INTO l_user_name, l_party_name
2048 FROM fnd_user fu,
2049 hz_parties hp
2050 WHERE hp.party_id = fu.customer_id
2051 AND fu.user_id = l_history.created_by;
2052
2053 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
2054
2055 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="employee_3">' || l_party_name || '(' || l_vendor_name || ')' || '</TD>' || NL;
2056 ELSE
2057 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="employee_3">' ||
2058 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
2059 END IF;
2060 /* Bug 2788683 end */
2061
2062 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="action_3">' ||
2063 nvl(l_history.action, ' ') || '</TD>' || NL;
2064
2065 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="date_3">' ||
2066 nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
2067
2068 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="actionNote_3">' ||
2069 nvl(l_history.note, ' ') || '</TD>' || NL;
2070
2071 l_document_hist := l_document_hist || '</TR>' || NL;
2072
2073 END IF;
2074
2075 end loop;
2076
2077 close history_csr;
2078
2079 get_pending_action_html(l_item_type, l_item_key, max_seqno, l_document_pend);
2080 /* bug 3090563 reverse display order */
2081 l_document := l_document || l_document_hist || l_document_pend || '</TABLE>';
2082
2083 document := l_document;
2084
2085 elsif (display_type = 'text/plain') then
2086
2087 document := '';
2088
2089 end if;
2090 END get_action_history_html;
2091
2092 /*
2093 ** This procedure will get the list of pending approvers for the requisition
2094 */
2095 PROCEDURE get_pending_action_html(p_item_type in varchar2,
2096 p_item_key in varchar2,
2097 max_seqno in number,
2098 p_document out NOCOPY varchar2) IS
2099
2100 l_document_id po_requisition_lines.requisition_header_id%TYPE;
2101 l_object_type po_action_history.object_type_code%TYPE;
2102 l_org_id po_requisition_lines.org_id%TYPE;
2103
2104 l_document VARCHAR2(32000) := '';
2105 l_sub_document VARCHAR2(32000) := '';
2106 l_one_row VARCHAR2(32000) := '';
2107
2108 l_history history_record;
2109 l_history_seq number;
2110 noPendAppr number := 0;
2111
2112 l_is_po_approval boolean := true;
2113 approverList ame_util.approversTable;
2114 upperLimit integer;
2115 fullName varchar2(240);
2116
2117 NL VARCHAR2(1) := fnd_global.newline;
2118
2119 --SQL What: Select NULL to the last two columns of pending_csr
2120 --SQL Why: Be consistent to the change of history_record without changing
2121 -- the existing functionality of get_pending_action_html
2122 CURSOR pending_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
2123
2124 SELECT pal.SEQUENCE_NUM,per.FULL_NAME,null,null,null,null,
2125 NULL, NULL, NULL /* bug 2788683*/
2126 FROM per_all_people_f per, -- Bug 3404451
2127 po_approval_list_lines pal,
2128 po_approval_list_headers pah
2129 WHERE pah.document_id = v_document_id
2130 and pah.document_type = v_object_type
2131 and pah.latest_revision = 'Y'
2132 and pal.APPROVAL_LIST_HEADER_ID = pah.APPROVAL_LIST_HEADER_ID
2133 and pal.STATUS IS NULL
2134 and per.PERSON_ID = pal.APPROVER_ID
2135 and trunc(sysdate) between per.EFFECTIVE_START_DATE
2136 and per.EFFECTIVE_END_DATE
2137 ORDER BY 1 asc;
2138
2139 BEGIN
2140
2141 l_document_id := wf_engine.GetItemAttrNumber
2142 (itemtype => p_item_type,
2143 itemkey => p_item_key,
2144 aname => 'DOCUMENT_ID');
2145
2146 l_org_id := wf_engine.GetItemAttrNumber
2147 (itemtype => p_item_type,
2148 itemkey => p_item_key,
2149 aname => 'ORG_ID');
2150
2151 l_object_type := 'REQUISITION';
2152
2153 l_document := NL || NL || '<!-- PENDING APPROVER -->'|| NL || NL;
2154
2155 l_document := l_document || '<!-- the value of maxseqno in pending' || max_seqno || '-->' || NL;
2156 l_history_seq := max_seqno - 1;
2157
2158 l_is_po_approval := is_po_approval_type(p_item_type, p_item_key);
2159
2160 if(l_is_po_approval = true) then
2161
2162 open pending_csr(l_document_id, l_object_type);
2163
2164 loop
2165
2166 fetch pending_csr into l_history;
2167
2168 exit when pending_csr%notfound;
2169
2170
2171 l_history_seq := l_history_seq + 1;
2172
2173 noPendAppr := noPendAppr + 1;
2174 l_one_row := '<TR>' || NL;
2175
2176 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="seqNum_3">'
2177 || nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
2178
2179 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' ||
2180 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
2181
2182 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="action_3">' ||
2183 nvl(l_history.action, ' ') || '</TD>' || NL;
2184
2185 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="date_3">' ||
2186 nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
2187
2188 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="actionNote_3">' ||
2189 nvl(l_history.note, ' ') || '</TD>' || NL;
2190 l_one_row := l_one_row || '</TR>' || NL;
2191
2192 if noPendAppr <> 1 THEN
2193 /* bug 3090563 reverse display order */
2194 l_sub_document := l_sub_document || l_one_row;
2195 END IF;
2196
2197 end loop;
2198 close pending_csr;
2199 else
2200 /* use ame approval */
2201 ame_api.getOldApprovers(applicationIdIn=>por_ame_approval_list.applicationId,
2202 transactionIdIn=>l_document_id,
2203 transactionTypeIn=>por_ame_approval_list.transactionType,
2204 oldApproversOut=>approverList);
2205
2206 upperLimit := approverList.count;
2207
2208 for i in 1 .. upperLimit loop
2209
2210 if(approverList(i).person_id is not null and approverList(i).approval_status is null) then
2211 select full_name
2212 into fullName from per_all_people_f
2213 where person_id = approverList(i).person_id
2214 and trunc(sysdate) between effective_start_date and effective_end_date;
2215
2216 l_history_seq := l_history_seq + 1;
2217 noPendAppr := noPendAppr + 1;
2218 l_one_row := '<TR>' || NL;
2219
2220 l_one_row := l_one_row || '<TD class="tabledata" width=5% nowrap align=left headers="seqNum_3">'
2221 || nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
2222 l_one_row := l_one_row || '<TD class="tabledata" width=27% nowrap align=left headers="employee_3">' ||
2223 nvl(fullName, ' ') || '</TD>' || NL;
2224 l_one_row := l_one_row || '<TD class="tabledata" nowrap width=15% align=left headers="action_3">' ||
2225 ' ' || '</TD>' || NL;
2226 l_one_row := l_one_row || '<TD class="tabledata" nowrap width=12% align=left headers="date_3">' ||
2227 ' ' || '</TD>' || NL;
2228
2229 l_one_row := l_one_row || '<TD class="tabledata" width=41% align=left headers="actionNote_3">' ||
2230 ' ' || '</TD>' || NL;
2231 l_one_row := l_one_row || '</TR>' || NL;
2232
2233 if noPendAppr <> 1 THEN
2234 l_sub_document := l_sub_document || l_one_row;
2235 END IF;
2236
2237 end if; -- person id
2238 end loop;
2239 end if; -- po or ame
2240
2241 l_document := l_document || l_sub_document;
2242
2243 if noPendAppr > 1 then
2244 p_document := l_document;
2245 else
2246 p_document := '';
2247 end if;
2248
2249 END get_pending_action_html;
2250
2251
2252 /* Bug #1581410 :kagarwal
2253 ** Desc: This procedure is not being used now. Added return to
2254 ** for backward compatibility.
2255 */
2256
2257 PROCEDURE get_action_history(document_id in varchar2,
2258 display_type in varchar2,
2259 document in out NOCOPY varchar2,
2260 document_type in out NOCOPY varchar2) IS
2261
2262 l_item_type wf_items.item_type%TYPE;
2263 l_item_key wf_items.item_key%TYPE;
2264
2265 l_document_id po_requisition_lines.requisition_header_id%TYPE;
2266 l_object_type po_action_history.object_type_code%TYPE;
2267 l_org_id po_requisition_lines.org_id%TYPE;
2268
2269 l_document VARCHAR2(32000) := '';
2270
2271 l_history history_record;
2272
2273 NL VARCHAR2(1) := fnd_global.newline;
2274
2275 --SQL What: Query action history which is updated by both buyer and vendor
2276 --SQL Why: Since vendor doesn't have employee id, added outer join;
2277 CURSOR history_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
2278
2279 SELECT poh.SEQUENCE_NUM,
2280 per.FULL_NAME,
2281 polc.DISPLAYED_FIELD,
2282 poh.ACTION_DATE,
2283 poh.NOTE,
2284 poh.OBJECT_REVISION_NUM,
2285 poh.employee_id, /* bug 2788683 */
2286 poh.created_by /* bug 2788683 */
2287 from po_action_history poh,
2288 per_all_people_f per, -- Bug 3404451
2289 po_lookup_codes polc
2290 where OBJECT_TYPE_CODE = v_object_type
2291 and poh.action_code = polc.lookup_code
2292 and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
2293 and per.person_id(+) = poh.employee_id /* bug 2788683 */
2294 and trunc(sysdate) between per.effective_start_date(+)
2295 and per.effective_end_date(+)
2296 and OBJECT_ID = v_document_id
2297 UNION ALL
2298 SELECT poh.SEQUENCE_NUM,
2299 per.FULL_NAME,
2300 NULL,
2301 poh.ACTION_DATE,
2302 poh.NOTE,
2303 poh.OBJECT_REVISION_NUM,
2304 poh.employee_id, /* bug 2788683 */
2305 poh.created_by /* bug 2788683 */
2306 from po_action_history poh,
2307 per_all_people_f per -- Bug 3404451
2308 where OBJECT_TYPE_CODE = v_object_type
2309 and poh.action_code is null
2310 and per.person_id(+) = poh.employee_id /* bug 2788683 */
2311 and trunc(sysdate) between per.effective_start_date(+)
2312 and per.effective_end_date(+)
2313 and OBJECT_ID = v_document_id
2314 order by 1 desc;
2315
2316 BEGIN
2317
2318 return;
2319
2320 END get_action_history;
2321
2322 function ConstructHeaderInfo(l_req_amount in varchar2,
2323 l_currency_code in varchar2,
2324 l_tax_amt in number,
2325 l_tax_amount in varchar2,
2326 l_description in varchar2,
2327 l_forwarded_from in varchar2,
2328 l_preparer in varchar2,
2329 l_note in varchar2,
2330 l_notification_id in number) return varchar2 is
2331
2332 l_document VARCHAR2(32000) := '';
2333
2334 NL VARCHAR2(1) := fnd_global.newline;
2335
2336 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
2337 l_type WF_MESSAGE_ATTRIBUTES.TYPE%TYPE;
2338 l_subtype WF_MESSAGE_ATTRIBUTES.SUBTYPE%TYPE;
2339 l_format WF_MESSAGE_ATTRIBUTES.FORMAT%TYPE;
2340
2341 BEGIN
2342
2343 -- style sheet
2344
2345 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
2346
2347 /* Bug 2480327
2348 ** notification UI enhancement
2349 when wf patch G is installed,
2350 hide notification header summary for new notification
2351 */
2352 if (wf_core.translate('WF_HEADER_ATTR') = 'Y') then
2353 begin
2354 wf_notification.GetAttrInfo(nid => l_notification_id,
2355 aname => '#HDR_1',
2356 atype => l_type,
2357 subtype => l_subtype,
2358 format => l_format);
2359 if (l_type is not null) then
2360 return l_document;
2361 end if;
2362 exception
2363 when others then
2364 null;
2365 end;
2366 end if;
2367
2368 l_document := l_document || NL || '<!-- REQ SUMMARY -->'|| NL || NL || '<P>';
2369
2370 l_document := l_document || print_heading(fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_SUMMARY'));
2371
2372 -- New Table Style
2373
2374 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || 'SUMMARY=""><TR>
2375 <TD ' || L_TABLE_LABEL_STYLE || ' width="15%">' ||
2376 fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT')
2377 || ' </TD>' || NL;
2378
2379 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' >'
2380 || l_req_amount || ' ' || l_currency_code || '</TD></TR>' || NL;
2381
2382 if l_tax_amt > 0 then
2383
2384 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2385 fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') || ' </TD>' || NL;
2386
2387 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>'
2388 || l_tax_amount || ' ' || l_currency_code || '</TD></TR>' || NL;
2389
2390 end if;
2391
2392 l_document := l_document || NL;
2393
2394 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2395 fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION')
2396 || ' </TD>' || NL;
2397
2398 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_description || '<BR></TD></TR>' || NL;
2399
2400 if l_forwarded_from is not null then
2401
2402 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2403 fnd_message.get_string('PO', 'PO_WF_NOTIF_FROM') ||' </TD>' || NL;
2404
2405 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_forwarded_from || '<BR></TD></TR>' || NL;
2406
2407 end if;
2408
2409 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2410 fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||' </TD>' || NL;
2411
2412 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_preparer || '<BR></TD></TR>' || NL;
2413
2414 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2415 fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || ' </TD>' || NL;
2416
2417 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_note || '<BR></TD></TR>' || NL;
2418
2419 l_document := l_document || '</TABLE>' || NL;
2420
2421 return l_document;
2422
2423 END ConstructHeaderInfo;
2424
2425
2426
2427 function print_heading(l_text in varchar2) return varchar2 is
2428
2429 l_document varchar2(1000) := '';
2430
2431 NL VARCHAR2(1) := fnd_global.newline;
2432 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
2433
2434 begin
2435
2436 l_document := '<TABLE width="100%" border=0 cellpadding=0 cellspacing=0 SUMMARY="">';
2437 l_document := l_document || '<TR>'||NL;
2438 l_document := l_document || '<TD class=subheader1>'|| l_text;
2439 l_document := l_document || '</TD></TR>';
2440
2441 -- horizontal line
2442 l_document := l_document || '<TR>' || NL;
2443 l_document := l_document || '<TD colspan=2 height=1 bgcolor=#cccc99>
2444 <img src=' || l_base_href || '/OA_MEDIA/FNDITPNT.gif ALT=""></TD></TR>';
2445
2446 l_document := l_document || '<TR><TD colspan=2 height=5> </TR></TABLE>' || NL;
2447
2448 return l_document;
2449
2450 end;
2451
2452 PROCEDURE update_action_history (p_action_code IN VARCHAR2,
2453 p_recipient_id IN NUMBER,
2454 p_note IN VARCHAR2,
2455 p_req_header_id IN NUMBER,
2456 p_current_id IN NUMBER)
2457 IS
2458 pragma AUTONOMOUS_TRANSACTION;
2459
2460 l_progress VARCHAR2(100) := '000';
2461
2462 l_object_sub_type_code PO_ACTION_HISTORY.OBJECT_SUB_TYPE_CODE%TYPE;
2463 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
2464 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
2465 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
2466 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
2467 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
2468 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
2469 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
2470 l_approval_group_id PO_ACTION_HISTORY.APPROVAL_GROUP_ID%TYPE;
2471
2472 begin
2473
2474 SELECT max(sequence_num)
2475 INTO l_sequence_num
2476 FROM PO_ACTION_HISTORY
2477 WHERE object_type_code = 'REQUISITION'
2478 AND object_id = p_req_header_id;
2479
2480 -- a person can be in more than one approval groups
2481 -- we add one row for this person after he requests information
2482 SELECT object_sub_type_code,
2483 object_revision_num, approval_path_id, request_id,
2484 program_application_id, program_date, program_id
2485 INTO l_object_sub_type_code,
2486 l_object_revision_num, l_approval_path_id, l_request_id,
2487 l_program_application_id, l_program_date, l_program_id
2488 FROM PO_ACTION_HISTORY
2489 WHERE object_type_code = 'REQUISITION'
2490 AND object_id = p_req_header_id
2491 AND employee_id = p_current_id
2492 AND action_code IS NULL
2493 AND rownum=1;
2494
2495 begin
2496 SELECT distinct approval_group_id
2497 INTO l_approval_group_id
2498 FROM PO_ACTION_HISTORY
2499 WHERE object_type_code = 'REQUISITION'
2500 AND object_id = p_req_header_id
2501 AND employee_id = p_recipient_id;
2502
2503 -- If a person is not in approval group or is in more than one approval groups,
2504 -- we don't show group name.
2505 exception
2506 when others then
2507 l_approval_group_id := null;
2508
2509 end;
2510
2511 l_progress := '010';
2512
2513 -- If an approver belongs to n groups, he will receive n notifications.
2514 -- After he takes action with one of the notifications, only ONE record in
2515 -- action_history table should be updated.
2516
2517
2518 UPDATE PO_ACTION_HISTORY
2519 SET last_update_date = sysdate,
2520 last_updated_by = fnd_global.user_id,
2521 last_update_login = fnd_global.login_id ,
2522 action_date = sysdate,
2523 action_code = p_action_code,
2524 note = p_note,
2525 offline_code = decode(offline_code,
2526 'PRINTED', 'PRINTED', NULL)
2527 WHERE employee_id = p_current_id
2528 AND object_id = p_req_header_id
2529 AND object_type_code = 'REQUISITION'
2530 AND action_code IS NULL
2531 AND rownum=1;
2532
2533
2534
2535 l_progress := '020';
2536
2537
2538 po_forward_sv1.insert_action_history (
2539 p_req_header_id,
2540 'REQUISITION',
2541 l_object_sub_type_code,
2542 l_sequence_num + 1,
2543 NULL,
2544 NULL,
2545 p_recipient_id,
2546 l_approval_path_id,
2547 NULL,
2548 l_object_revision_num,
2549 NULL, /* offline_code */
2550 l_request_id,
2551 l_program_application_id,
2552 l_program_id,
2553 l_program_date,
2554 fnd_global.user_id,
2555 fnd_global.login_id,
2556 l_approval_group_id);
2557
2558 l_progress := '030';
2559
2560 commit;
2561 EXCEPTION
2562 WHEN OTHERS THEN
2563 wf_core.context('PO_WF_REQ_NOTIFICATION','update_action_history',l_progress,sqlerrm);
2564 RAISE;
2565 end;
2566
2567
2568 PROCEDURE post_approval_notif(itemtype in varchar2,
2569 itemkey in varchar2,
2570 actid in number,
2571 funcmode in varchar2,
2572 resultout out NOCOPY varchar2) is
2573 l_nid number;
2574 l_forwardTo varchar2(240);
2575 l_result varchar2(100);
2576 l_forward_to_username_response varchar2(240) :='';
2577 l_req_header_id po_requisition_headers.requisition_header_id%TYPE;
2578 l_action po_action_history.action_code%TYPE;
2579 l_new_recipient_id wf_roles.orig_system_id%TYPE;
2580 l_current_recipient_id wf_roles.orig_system_id%TYPE;
2581 l_origsys wf_roles.orig_system%TYPE;
2582 l_is_ame_approval varchar2(10);
2583
2584 l_doc_string varchar2(200);
2585 l_preparer_user_name varchar2(100);
2586
2587 -- Context setting revamp <variable addition start>
2588 l_responder_id fnd_user.user_id%TYPE;
2589 l_session_user_id NUMBER;
2590 l_session_resp_id NUMBER;
2591 l_session_appl_id NUMBER;
2592 l_preparer_resp_id NUMBER;
2593 l_preparer_appl_id NUMBER;
2594 l_progress VARCHAR2(1000);
2595 l_preserved_ctx VARCHAR2(5);
2596 -- Context setting revamp <variable addition end>
2597
2598
2599 begin
2600
2601 l_progress := '001';
2602
2603 if (funcmode IN ('FORWARD', 'QUESTION', 'ANSWER')) then
2604
2605 if (funcmode = 'FORWARD') then
2606 l_action := 'DELEGATE';
2607 elsif (funcmode = 'QUESTION') then
2608 l_action := 'QUESTION';
2609 elsif (funcmode = 'ANSWER') then
2610 l_action := 'ANSWER';
2611 end if;
2612
2613 l_req_header_id := wf_engine.GetItemAttrNumber
2614 (itemtype => itemtype,
2615 itemkey => itemkey,
2616 aname => 'DOCUMENT_ID');
2617
2618 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_NEW_ROLE, l_origsys, l_new_recipient_id);
2619
2620 /* bug 4667656 : We should not be allowing the delegation of a notication
2621 to a user who is not an employee. */
2622
2623 if((funcmode = 'FORWARD') AND (l_origsys <> 'PER')) then
2624 fnd_message.set_name('PO', 'PO_INVALID_USER_FOR_REASSIGN');
2625 app_exception.raise_exception;
2626 end if;
2627
2628 l_progress := '002';
2629
2630 if (funcmode = 'ANSWER') then
2631 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_MORE_INFO_ROLE, l_origsys, l_current_recipient_id);
2632
2633 else
2634 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_RECIPIENT_ROLE, l_origsys, l_current_recipient_id);
2635
2636 end if;
2637
2638 l_progress := '003';
2639
2640 l_is_ame_approval := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2641 itemkey => itemkey,
2642 aname => 'IS_AME_APPROVAL'
2643 );
2644
2645 if ( funcmode = 'FORWARD' AND l_is_ame_approval = 'Y' ) then
2646 po_wf_util_pkg.SetItemAttrNumber( itemtype => itemtype,
2647 itemkey => itemkey,
2648 aname => 'APPROVER_EMPID',
2649 avalue => l_new_recipient_id
2650 );
2651 end if;
2652
2653 l_progress := '004';
2654
2655 update_action_history(p_action_code => l_action,
2656 p_recipient_id => l_new_recipient_id,
2657 p_note => WF_ENGINE.CONTEXT_USER_COMMENT,
2658 p_req_header_id => l_req_header_id,
2659 p_current_id => l_current_recipient_id);
2660
2661 l_progress := '005';
2662
2663 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
2664
2665 return;
2666 end if;
2667
2668
2669 if (funcmode = 'RESPOND') then
2670
2671 l_nid := WF_ENGINE.context_nid;
2672
2673 l_result := wf_notification.GetAttrText(l_nid, 'RESULT');
2674
2675 l_progress := '006';
2676
2677 if((l_result = 'FORWARD') or (l_result = 'APPROVE_AND_FORWARD')) then
2678
2679 l_forwardTo := wf_notification.GetAttrText(l_nid, 'FORWARD_TO_USERNAME_RESPONSE');
2680
2681 l_forward_to_username_response := wf_engine.GetItemAttrText (itemtype => itemtype,
2682 itemkey => itemkey,
2683 aname => 'FORWARD_TO_USERNAME_RESPONSE');
2684
2685 if(l_forwardTo is null) then
2686 fnd_message.set_name('ICX', 'ICX_POR_WF_NOTIF_NO_USER');
2687 app_exception.raise_exception;
2688 end if;
2689 end if;
2690
2691 l_progress := '007';
2692
2693 -- Context setting revamp <start>
2694 -- <debug start>
2695 if (wf_engine.preserved_context = TRUE) then
2696 l_preserved_ctx := 'TRUE';
2697 else
2698 l_preserved_ctx := 'FALSE';
2699 end if;
2700 l_progress := 'notif callback preserved_ctx : '||l_preserved_ctx;
2701 IF (g_po_wf_debug = 'Y') THEN
2702 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2703 END IF;
2704 -- <debug end>
2705
2706
2707 SELECT fu.USER_ID
2708 INTO l_responder_id
2709 FROM fnd_user fu,
2710 wf_notifications wfn
2711 WHERE wfn.notification_id = l_nid
2712 AND wfn.original_recipient = fu.user_name;
2713
2714 -- <debug start>
2715 l_progress := '010 notif callback -responder id : '||l_responder_id;
2716 IF (g_po_wf_debug = 'Y') THEN
2717 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2718 END IF;
2719 -- <debug end>
2720
2721 --Bug 5389914
2722 --Fnd_Profile.Get('USER_ID',l_session_user_id);
2723 --Fnd_Profile.Get('RESP_ID',l_session_resp_id);
2724 --Fnd_Profile.Get('RESP_APPL_ID',l_session_appl_id);
2725 l_session_user_id := fnd_global.user_id;
2726 l_session_resp_id := fnd_global.resp_id;
2727 l_session_appl_id := fnd_global.resp_appl_id;
2728
2729 IF (l_session_user_id = -1) THEN
2730 l_session_user_id := NULL;
2731 END IF;
2732
2733 IF (l_session_resp_id = -1) THEN
2734 l_session_resp_id := NULL;
2735 END IF;
2736
2737 IF (l_session_appl_id = -1) THEN
2738 l_session_appl_id := NULL;
2739 END IF;
2740
2741 -- <debug start>
2742 l_progress :='020 notification callback ses_userid: '||l_session_user_id
2743 ||' sess_resp_id '||l_session_resp_id||' sess_appl_id '
2744 ||l_session_appl_id;
2745 IF (g_po_wf_debug = 'Y') THEN
2746 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2747 END IF;
2748 -- <debug end>
2749
2750 -- bug 4901406 <start> : need to shift the setting of the preparer resp and appl id
2751 -- to here, it was not initialized inside the if condition if the control went to the
2752 -- else part.
2753
2754 l_preparer_resp_id :=
2755 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
2756 itemkey => itemkey,
2757 aname => 'RESPONSIBILITY_ID');
2758 l_preparer_appl_id :=
2759 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
2760 itemkey => itemkey,
2761 aname => 'APPLICATION_ID');
2762
2763 -- <debug start>
2764 l_progress := '030 notif callback prep resp_id:'||l_preparer_resp_id
2765 ||' prep appl id '||l_preparer_appl_id;
2766 IF (g_po_wf_debug = 'Y') THEN
2767 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2768 END IF;
2769 -- <debug end>
2770
2771 -- bug 4901406 <end>
2772
2773
2774
2775 if (l_responder_id is not null) then
2776 if (l_responder_id <> l_session_user_id) then
2777 /* possible in 2 scenarios :
2778 1. when the response is made from email using guest user feature
2779 2. When the response is made from sysadmin login
2780 */
2781
2782
2783
2784 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2785 itemkey => itemkey,
2786 aname => 'RESPONDER_USER_ID',
2787 avalue => l_responder_id);
2788 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2789 itemkey => itemkey,
2790 aname => 'RESPONDER_RESP_ID',
2791 avalue => l_preparer_resp_id);
2792 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2793 itemkey => itemkey,
2794 aname => 'RESPONDER_APPL_ID',
2795 avalue => l_preparer_appl_id);
2796 else
2797 if (l_session_resp_id is null) THEN
2798 /* possible when the response is made from the default worklist
2799 without choosing a valid responsibility */
2800 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2801 itemkey => itemkey,
2802 aname => 'RESPONDER_USER_ID',
2803 avalue => l_responder_id);
2804 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2805 itemkey => itemkey,
2806 aname => 'RESPONDER_RESP_ID',
2807 avalue => l_preparer_resp_id);
2808 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2809 itemkey => itemkey,
2810 aname => 'RESPONDER_APPL_ID',
2811 avalue => l_preparer_appl_id);
2812 else
2813 /* all values available - possible when the response is made
2814 after choosing a correct responsibility */
2815 /* bug 5333226 : If the values of responsibility_id and application
2816 id are available but are incorrect - i.e. not conforming to say the
2817 sls (subledger security). This may happen when a response is made
2818 through the email or the background process picks the wf up.
2819 This may happen due to the fact that the mailer / background process
2820 carries the context set by the notification/wf it processed last*/
2821 if ( l_preserved_ctx = 'TRUE') then
2822 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2823 itemkey => itemkey,
2824 aname => 'RESPONDER_USER_ID',
2825 avalue => l_responder_id);
2826 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2827 itemkey => itemkey,
2828 aname => 'RESPONDER_RESP_ID',
2829 avalue => l_session_resp_id);
2830 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2831 itemkey => itemkey,
2832 aname => 'RESPONDER_APPL_ID',
2833 avalue => l_session_appl_id);
2834 else
2835 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2836 itemkey => itemkey,
2837 aname => 'RESPONDER_USER_ID',
2838 avalue => l_responder_id);
2839 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2840 itemkey => itemkey,
2841 aname => 'RESPONDER_RESP_ID',
2842 avalue => l_preparer_resp_id);
2843 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2844 itemkey => itemkey,
2845 aname => 'RESPONDER_APPL_ID',
2846 avalue => l_preparer_appl_id);
2847 end if;
2848
2849
2850 end if;
2851 end if;
2852 end if;
2853
2854 -- context setting revamp <end>
2855
2856
2857
2858
2859 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
2860 return;
2861 end if;
2862
2863 -- Don't allow transfer
2864 if (funcmode = 'TRANSFER') then
2865 fnd_message.set_name('PO', 'PO_WF_NOTIF_NO_TRANSFER');
2866 app_exception.raise_exception;
2867 resultout := wf_engine.eng_completed;
2868 return;
2869 end if; -- end if for funcmode = 'TRANSFER'
2870
2871 exception
2872 when others then
2873 raise;
2874
2875 end post_approval_notif;
2876
2877
2878 PROCEDURE set_doc_mgr_context (itemtype VARCHAR2, itemkey VARCHAR2) is
2879
2880 l_user_id number;
2881 l_responsibility_id number;
2882 l_application_id number;
2883
2884 l_progress varchar2(200);
2885
2886 BEGIN
2887
2888 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2889 itemkey => itemkey,
2890 aname => 'USER_ID');
2891 --
2892 l_application_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2893 itemkey => itemkey,
2894 aname => 'APPLICATION_ID');
2895 --
2896 l_responsibility_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2897 itemkey => itemkey,
2898 aname => 'RESPONSIBILITY_ID');
2899
2900 /* Set the context for the doc manager */
2901 -- Bug 4290541, replaced apps init call with set doc mgr context
2902
2903 PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(itemtype, itemkey);
2904
2905 l_progress := 'set_doc_mgr_context. USER_ID= ' || to_char(l_user_id)
2906 || ' APPLICATION_ID= ' || to_char(l_application_id) ||
2907 'RESPONSIBILITY_ID= ' || to_char(l_responsibility_id);
2908
2909 EXCEPTION
2910
2911 WHEN OTHERS THEN
2912 wf_core.context('PO_WF_REQ_NOTIFICATION','set_doc_mgr_context',l_progress);
2913 raise;
2914
2915 END set_doc_mgr_context;
2916
2917 /* Bug# 2616355: kagarwal
2918 function get_document_subtype_display (l_subtype_code in varchar2)
2919 return varchar2 is
2920
2921 l_doc_subtype_disp varchar2(80);
2922
2923 cursor c_doc_subtype(p_doc_subtype varchar2) is
2924 select DISPLAYED_FIELD
2925 from po_lookup_codes
2926 where lookup_type='REQUISITION TYPE'
2927 and lookup_code = p_doc_subtype;
2928
2929 begin
2930
2931 OPEN c_doc_subtype(l_subtype_code);
2932 FETCH c_doc_subtype into l_doc_subtype_disp;
2933 CLOSE c_doc_subtype;
2934
2935 return l_doc_subtype_disp;
2936
2937 end;
2938 */
2939
2940 /* Bug# 2616355: kagarwal
2941 function get_document_type_display (l_type_code in varchar2)
2942 return varchar2 is
2943
2944 l_doc_type_disp varchar2(80);
2945
2946 cursor c_doc_type(p_doc_type varchar2) is
2947 select DISPLAYED_FIELD
2948 from po_lookup_codes
2949 where lookup_type='DOCUMENT TYPE'
2950 and lookup_code = p_doc_type;
2951
2952 begin
2953
2954 OPEN c_doc_type(l_type_code);
2955 FETCH c_doc_type into l_doc_type_disp;
2956 CLOSE c_doc_type;
2957
2958 return l_doc_type_disp;
2959
2960 end;
2961 */
2962
2963 function is_po_approval_type(p_itemtype in varchar2, p_itemkey in varchar2)
2964 return boolean is
2965
2966 l_authority_type VARCHAR2(30);
2967
2968 BEGIN
2969
2970 l_authority_type := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => p_itemtype,
2971 itemkey => p_itemkey,
2972 aname => 'AME_AUTHORITY_TYPE');
2973 if(l_authority_type is null) then
2974 return true;
2975 end if;
2976
2977 return false;
2978
2979 EXCEPTION
2980 WHEN OTHERS THEN
2981 RETURN TRUE;
2982 END;
2983
2984 /* Bug# 2469882
2985 ** Desc: Added new private procedure to set doc subtype display according to the default language of approver or preparer.
2986 This is a workaround suggested by workflow team to support translatable token within msg subject, while avoid fixed language issue within subject.
2987 */
2988
2989 /* Bug# 2616355: kagarwal
2990 ** Desc: Set doc type display according to the default language of the user
2991 **
2992 ** The username is a mandatory IN parameter.
2993 */
2994
2995 procedure GetDisplayValue(itemtype in varchar2,
2996 itemkey in varchar2,
2997 username in varchar2) IS
2998
2999 l_progress VARCHAR2(400) := '000';
3000 l_doc_subtype varchar2(25);
3001 l_doc_disp varchar2(240);
3002
3003 l_display_name varchar2(240);
3004 l_email_address varchar2(240);
3005 l_notification_preference varchar2(240);
3006 l_language varchar2(240);
3007 l_territory varchar2(240);
3008
3009 /* Bug# 2616355: kagarwal
3010 ** Desc: We will get the document type display value from
3011 ** po document types tl table.
3012 */
3013
3014 cursor c_lookup_value(p_doc_subtype varchar2, p_language varchar2) is
3015 select type_name
3016 from po_document_types_tl tl, FND_LANGUAGES fl
3017 where fl.nls_language = p_language
3018 and tl.LANGUAGE = fl.language_code
3019 and tl.document_type_code = 'REQUISITION'
3020 and tl.document_subtype = p_doc_subtype;
3021
3022 /*
3023 cursor c_lookup_value(p_doc_subtype varchar2, p_language varchar2) is
3024 select MEANING
3025 from FND_LOOKUP_VALUES flv, FND_LANGUAGES fl
3026 where
3027 fl.nls_language = p_language
3028 and flv.LANGUAGE = fl.language_code
3029 and flv.lookup_type='REQUISITION TYPE'
3030 and flv.lookup_code = p_doc_subtype
3031 and VIEW_APPLICATION_ID = 201
3032 and SECURITY_GROUP_ID = fnd_global.lookup_security_group('REQUISITION TYPE',201);
3033 */
3034
3035 BEGIN
3036 l_progress := 'GetDisplayValue: 001, user name: ' || username;
3037 IF (g_po_wf_debug = 'Y') THEN
3038 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3039 END IF;
3040
3041 l_doc_subtype := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3042 itemkey => itemkey,
3043 aname => 'DOCUMENT_SUBTYPE');
3044
3045 Wf_Directory.GetRoleInfo(
3046 username,
3047 l_display_name,
3048 l_email_address,
3049 l_notification_preference,
3050 l_language,
3051 l_territory);
3052
3053 l_progress := 'GetDisplayValue: 002, language: ' || l_language;
3054 IF (g_po_wf_debug = 'Y') THEN
3055 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3056 END IF;
3057
3058 OPEN c_lookup_value(l_doc_subtype, l_language);
3059 FETCH c_lookup_value into l_doc_disp;
3060 CLOSE c_lookup_value;
3061
3062 l_progress := 'GetDisplayValue: 003, subtype disp: ' || l_doc_disp;
3063 IF (g_po_wf_debug = 'Y') THEN
3064 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3065 END IF;
3066
3067 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
3068 itemkey => itemkey,
3069 aname => 'DOCUMENT_TYPE_DISP',
3070 avalue => l_doc_disp);
3071 EXCEPTION
3072 WHEN OTHERS THEN
3073 l_progress := 'GetDisplayValue: sql err: ' || sqlerrm;
3074 IF (g_po_wf_debug = 'Y') THEN
3075 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3076 END IF;
3077 IF (c_lookup_value%ISOPEN) THEN
3078 CLOSE c_lookup_value;
3079 END IF;
3080
3081 END GetDisplayValue;
3082
3083 /* Bug# 2469882
3084 ** Desc: Added new procedure to set notification subject token.
3085 */
3086 procedure Get_req_approver_msg_attribute(itemtype in varchar2,
3087 itemkey in varchar2,
3088 actid in number,
3089 funcmode in varchar2,
3090 resultout out NOCOPY varchar2) IS
3091
3092 l_progress VARCHAR2(100) := '000';
3093 l_doc_string varchar2(200);
3094 l_approver_user_name varchar2(100);
3095 l_preparer_user_name varchar2(100);
3096 l_orgid number;
3097
3098 BEGIN
3099
3100 -- Do nothing in cancel or timeout mode
3101 --
3102 if (funcmode <> wf_engine.eng_run) then
3103
3104 resultout := wf_engine.eng_null;
3105 return;
3106
3107 end if;
3108
3109 l_progress := 'Get_req_approver_msg_attribute: 001';
3110 IF (g_po_wf_debug = 'Y') THEN
3111 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3112 END IF;
3113
3114 l_approver_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3115 itemkey => itemkey,
3116 aname => 'APPROVER_USER_NAME');
3117 /* Bug# 2616355: kagarwal
3118 ** Desc Need to set the org context
3119 */
3120
3121 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3122 itemkey => itemkey,
3123 aname => 'ORG_ID');
3124
3125 IF l_orgid is NOT NULL THEN
3126 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
3127 END IF;
3128
3129 GetDisplayValue(itemtype, itemkey, l_approver_user_name);
3130
3131 l_progress := 'Get_req_approver_msg_attribute: 002';
3132 IF (g_po_wf_debug = 'Y') THEN
3133 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3134 END IF;
3135
3136 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3137
3138
3139 EXCEPTION
3140 WHEN OTHERS THEN
3141 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3142 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
3143 wf_core.context('PO_WF_REQ_NOTIFICATION','Get_req_approval_msg_attribute',l_progress);
3144 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_WF_REQ_NOTIFICATION.GET_REQ_APPROVER_MSG_ATTRIBUTE');
3145 raise;
3146
3147 END Get_req_approver_msg_attribute;
3148
3149 procedure Get_req_preparer_msg_attribute(itemtype in varchar2,
3150 itemkey in varchar2,
3151 actid in number,
3152 funcmode in varchar2,
3153 resultout out NOCOPY varchar2) IS
3154
3155 l_progress VARCHAR2(100) := '000';
3156 l_doc_string varchar2(200);
3157 l_preparer_user_name varchar2(100);
3158 l_orgid number;
3159
3160 BEGIN
3161
3162 -- Do nothing in cancel or timeout mode
3163 --
3164 if (funcmode <> wf_engine.eng_run) then
3165
3166 resultout := wf_engine.eng_null;
3167 return;
3168
3169 end if;
3170
3171 l_progress := 'Get_req_preparer_msg_attribute: 001';
3172 IF (g_po_wf_debug = 'Y') THEN
3173 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3174 END IF;
3175
3176 l_preparer_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3177 itemkey => itemkey,
3178 aname => 'PREPARER_USER_NAME');
3179
3180 /* Bug# 2616355: kagarwal
3181 ** Desc Need to set the org context
3182 */
3183
3184 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3185 itemkey => itemkey,
3186 aname => 'ORG_ID');
3187
3188 IF l_orgid is NOT NULL THEN
3189 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
3190 END IF;
3191
3192 GetDisplayValue(itemtype, itemkey, l_preparer_user_name);
3193
3194 l_progress := 'Get_req_preparer_msg_attribute: 002';
3195 IF (g_po_wf_debug = 'Y') THEN
3196 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3197 END IF;
3198
3199 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3200
3201
3202 EXCEPTION
3203 WHEN OTHERS THEN
3204 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3205 wf_core.context('PO_WF_REQ_NOTIFICATION','Get_req_preparer_msg_attribute',l_progress);
3206 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_WF_REQ_NOTIFICATION.GET_REQ_PREPARER_MSG_ATTRIBUTE');
3207 raise;
3208
3209 END Get_req_preparer_msg_attribute;
3210
3211 /* Procedure to check whether Forward Action is allowed. */
3212
3213 procedure Is_Forward_Action_Allowed(itemtype in varchar2,
3214 itemkey in varchar2,
3215 actid in number,
3216 funcmode in varchar2,
3217 resultout out NOCOPY varchar2 ) is
3218
3219 l_allowed VARCHAR2(1) := 'Y';
3220
3221 begin
3222
3223 FND_PROFILE.GET('PO_ALLOW_REQ_APPRV_FORWARD', l_allowed);
3224
3225 resultout := wf_engine.eng_completed || ':' || l_allowed;
3226
3227 exception
3228 when others then
3229 resultout := wf_engine.eng_completed || ':' || 'Y';
3230
3231 end Is_Forward_Action_Allowed;
3232
3233 /* Bug# 2616255: kagarwal
3234 ** Desc: Added new procedure to set notification subject token
3235 ** for the notifications sent to forward from person
3236 */
3237 procedure Get_req_fwdfrom_msg_attribute(itemtype in varchar2,
3238 itemkey in varchar2,
3239 actid in number,
3240 funcmode in varchar2,
3241 resultout out NOCOPY varchar2) IS
3242
3243 l_progress VARCHAR2(100) := '000';
3244 l_doc_string varchar2(200);
3245 l_fwdfrom_user_name varchar2(100);
3246 l_preparer_user_name varchar2(100);
3247 l_orgid number;
3248
3249 BEGIN
3250
3251 -- Do nothing in cancel or timeout mode
3252 --
3253 if (funcmode <> wf_engine.eng_run) then
3254
3255 resultout := wf_engine.eng_null;
3256 return;
3257
3258 end if;
3259
3260 l_progress := 'Get_req_fwdfrom_msg_attribute: 001';
3261 IF (g_po_wf_debug = 'Y') THEN
3262 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3263 END IF;
3264
3265 l_fwdfrom_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3266 itemkey => itemkey,
3267 aname => 'FORWARD_FROM_USER_NAME');
3268
3269 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3270 itemkey => itemkey,
3271 aname => 'ORG_ID');
3272
3273 IF l_orgid is NOT NULL THEN
3274 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
3275 END IF;
3276
3277 GetDisplayValue(itemtype, itemkey, l_fwdfrom_user_name);
3278
3279 l_progress := 'Get_req_fwdfrom_msg_attribute: 002';
3280 IF (g_po_wf_debug = 'Y') THEN
3281 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3282 END IF;
3283
3284 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3285
3286 EXCEPTION
3287 WHEN OTHERS THEN
3288 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3289 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
3290 wf_core.context('PO_WF_REQ_NOTIFICATION','Get_req_fwdfrom_msg_attribute',l_progress);
3291 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
3292 l_doc_string, sqlerrm, 'PO_WF_REQ_NOTIFICATION.GET_REQ_FWDFROM_MSG_ATTRIBUTE');
3293 raise;
3294
3295 END Get_req_fwdfrom_msg_attribute;
3296
3297 /* Bug 2480327
3298 ** notification UI enhancement
3299 */
3300
3301 function is_foreign_currency_displayed (p_document_id in number, p_func_currency_code in varchar2) return boolean IS
3302
3303 l_max_lines number := 0;
3304 l_currency_code po_requisition_lines.currency_code%TYPE;
3305
3306 begin
3307 l_max_lines := to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
3308
3309 -- SQL What: checking for any requisition line that has foreign currency
3310 -- SQL Why: need to check if need to display foregin currency column
3311 select currency_code into l_currency_code from
3312 (select currency_code from
3313 (SELECT currency_code
3314 FROM po_requisition_lines
3315 WHERE requisition_header_id = p_document_id
3316 AND NVL(cancel_flag,'N') = 'N'
3317 AND NVL(modified_by_agent_flag, 'N') = 'N'
3318 order by line_num) a
3319 where rownum <= l_max_lines ) b
3320 where b.currency_code <> p_func_currency_code;
3321 return true;
3322 exception
3323 when no_data_found then
3324 return false;
3325 when too_many_rows then
3326 return true;
3327 when others then
3328 return false;
3329 end;
3330
3331 /* Bug 2480327
3332 ** notification UI enhancement
3333 */
3334
3335 procedure get_item_info(document_id in varchar2,
3336 itemtype out nocopy varchar2,
3337 itemkey out nocopy varchar2,
3338 nid out nocopy number) is
3339
3340 firstcolon pls_integer;
3341 secondcolon pls_integer;
3342
3343 begin
3344
3345 /* format like REQAPPRV:12719-23684:67694*/
3346 firstcolon := instr(document_id, ':', 1,1);
3347 secondcolon := instr(document_id, ':', 1,2);
3348
3349 itemtype := substr(document_id, 1, firstcolon - 1);
3350
3351 if (secondcolon = 0) then
3352 itemkey := substr(document_id, firstcolon + 1,
3353 length(document_id) - 2);
3354 nid := null;
3355 else
3356 itemkey := substr(document_id, firstcolon + 1, secondcolon - firstcolon - 1);
3357 begin
3358 nid := to_number(substr(document_id, secondcolon+1,
3359 length(document_id) - secondcolon));
3360 exception
3361 when others then nid := null;
3362 end;
3363 end if;
3364
3365 IF (g_po_wf_debug = 'Y') THEN
3366 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'PO_WF_REQ_NOTIFICATION.get_item_info nid='||nid);
3367 END IF;
3368
3369 end;
3370
3371 -- Bug 3419861
3372 -- Added the new function to format the currency.
3373
3374 Function FORMAT_CURRENCY_NO_PRECESION(p_currency_code IN varchar2,
3375 p_amount IN number) return varchar2 is
3376 l_precision number := 0;
3377 l_precision_amt number := 0;
3378 l_ext_precision number := 0;
3379 l_min_acct_unit number := 0;
3380 l_field_length number := 80;
3381 l_mask varchar2(100);
3382
3383 begin
3384 -- Get the Currency info
3385 fnd_currency.get_info(p_currency_code, l_precision,
3386 l_ext_precision, l_min_acct_unit);
3387
3388 -- Find the field width
3389 l_field_length := length(p_amount) + 40;
3390 l_precision_amt := length(p_amount) - length(round(p_amount,0)) - 1;
3391
3392 if l_precision_amt > l_precision then
3393 l_precision := l_precision_amt;
3394 end if;
3395
3396 -- Build custom format mask
3397 fnd_currency.build_format_mask(l_mask, l_field_length,
3398 l_precision, l_min_acct_unit);
3399
3400 -- Convert the Amount
3401 return to_char(p_amount,l_mask);
3402
3403 end FORMAT_CURRENCY_NO_PRECESION;
3404
3405 END PO_WF_REQ_NOTIFICATION;