[Home] [Help]
PACKAGE BODY: APPS.PO_WF_REQ_NOTIFICATION
Source
1 PACKAGE BODY PO_WF_REQ_NOTIFICATION AS
2 /* $Header: POXWPA6B.pls 120.27.12020000.2 2013/05/09 15:26:56 rparise 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 End if;
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 l_date_text varchar2(150) := '';
1895
1896 l_history history_record;
1897 l_history_seq number;
1898
1899 MAX_SEQNO number := 0;
1900 l_rcount number := 0;
1901 NL VARCHAR2(1) := fnd_global.newline;
1902
1903 --SQL What: Query action history which is updated by both buyer and vendor
1904 --SQL Why: Since vendor doesn't have employee id, added outer join;
1905 CURSOR history_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
1906
1907 SELECT poh.SEQUENCE_NUM,
1908 per.FULL_NAME,
1909 polc.DISPLAYED_FIELD,
1910 poh.ACTION_DATE,
1911 poh.NOTE,
1912 poh.OBJECT_REVISION_NUM,
1913 poh.employee_id, /* bug 2788683 */
1914 poh.created_by, /* bug 2788683 */
1915 poh.action_code /* bug 3090563 */
1916 from po_action_history poh,
1917 per_all_people_f per, -- Bug 3404451
1918 po_lookup_codes polc
1919 where OBJECT_TYPE_CODE = v_object_type
1920 and nvl(poh.action_code, 'PENDING') = polc.lookup_code
1921 and POLC.LOOKUP_TYPE = 'APPR_HIST_ACTIONS'
1922 and per.person_id(+) = poh.employee_id /* bug 2788683 */
1923 and trunc(sysdate) between per.effective_start_date(+)
1924 and per.effective_end_date(+)
1925 and OBJECT_ID = v_document_id
1926 order by 1 asc; /* bug 3090563 reverse display order */
1927 l_notification_id number;
1928
1929 /* Bug 2788683 start */
1930 l_user_name fnd_user.user_name%TYPE;
1931 l_vendor_name hz_parties.party_name%TYPE;
1932 l_party_name hz_parties.party_name%TYPE;
1933 /* Bug 2788683 end */
1934
1935 CURSOR count_rows(v_document_id NUMBER, v_object_type VARCHAR2) IS
1936 SELECT count(*)
1937 from po_action_history poh,
1938 per_all_people_f per,
1939 po_lookup_codes polc
1940 where OBJECT_TYPE_CODE = v_object_type
1941 and nvl(poh.action_code, 'PENDING') = polc.lookup_code
1942 and POLC.LOOKUP_TYPE = 'APPR_HIST_ACTIONS'
1943 and per.person_id(+) = poh.employee_id
1944 and trunc(sysdate) between per.effective_start_date(+)
1945 and per.effective_end_date(+)
1946 and OBJECT_ID = v_document_id;
1947 BEGIN
1948
1949 /* Bug 2480327
1950 ** notification UI enhancement
1951 */
1952 get_item_info(document_id, l_item_type, l_item_key, l_notification_id);
1953
1954 l_document_id := wf_engine.GetItemAttrNumber
1955 (itemtype => l_item_type,
1956 itemkey => l_item_key,
1957 aname => 'DOCUMENT_ID');
1958
1959 l_org_id := wf_engine.GetItemAttrNumber
1960 (itemtype => l_item_type,
1961 itemkey => l_item_key,
1962 aname => 'ORG_ID');
1963
1964 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
1965
1966 l_object_type := 'REQUISITION';
1967
1968 if (display_type = 'text/html') then
1969
1970 l_document := NL || NL || '<!-- ACTION_HISTORY -->'|| NL || NL || '<P>';
1971
1972 l_document := l_document || print_heading(fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY'));
1973
1974 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || ' summary="' || fnd_message.get_string('ICX', 'ICX_POR_TBL_OF_APPROVERS') || '">' || NL;
1975
1976 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=5% id="seqNum_3"> </TH>' || NL;
1977
1978 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=20% id="employee_3">' ||
1979 fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || '</TH>' || NL;
1980
1981 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=12% id="action_3">' ||
1982 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || '</TH>' || NL;
1983
1984 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=12% id="date_3">' ||
1985 fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || '</TH>' || NL;
1986
1987 l_document := l_document || '<TH ' || L_TABLE_HEADER_STYLE || ' width=35% id="actionNote_3">' ||
1988 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || '</TH>' || NL;
1989
1990 l_document := l_document || '</TR>' || NL;
1991 open count_rows(l_document_id, l_object_type);
1992 loop
1993 fetch count_rows into l_rcount;
1994 exit when count_rows%notfound;
1995 end loop;
1996
1997 get_pending_action_html(l_item_type, l_item_key, l_rcount, l_document_pend);
1998
1999 l_history_seq := l_rcount;
2000 open history_csr(l_document_id, l_object_type);
2001 loop
2002
2003 fetch history_csr into l_history;
2004
2005 exit when history_csr%notfound;
2006
2007
2008 /* bug 3090563 change check to action_code */
2009 IF (l_history.action_code is not NULL) THEN
2010
2011 l_document_hist := l_document_hist || NL || '<TR>' || NL;
2012
2013 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="seqNum_3">' ||
2014 nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
2015
2016 /* Bug 2788683 start */
2017 /* if action history is updated by vendor
2018 * show vendor true name(vendor name)
2019 * else action history is updated by buyer
2020 * show buyer's true name
2021 */
2022 IF l_history.employee_id IS NULL THEN
2023 SELECT fu.user_name, hp.party_name
2024 INTO l_user_name, l_party_name
2025 FROM fnd_user fu,
2026 hz_parties hp
2027 WHERE hp.party_id = fu.customer_id
2028 AND fu.user_id = l_history.created_by;
2029
2030 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
2031
2032 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' || l_party_name || '(' || l_vendor_name || ')' || '</TD>' || NL;
2033 ELSE
2034 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' ||
2035 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
2036 END IF;
2037 /* Bug 2788683 end */
2038
2039 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="action_3">' ||
2040 nvl(l_history.action, ' ') || '</TD>' || NL;
2041 /*Modified as part of bug 7554321 changing date format*/
2042 if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
2043 or (FND_RELEASE.MAJOR_VERSION > 12) then
2044 l_date_text := nvl(to_char(l_history.action_date,
2045 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
2046 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),' ') ;
2047 else
2048 l_date_text := nvl(to_char(l_history.action_date), ' ') ;
2049 end if;
2050
2051
2052 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_STYLE || ' headers="date_3">' ||
2053 l_date_text || '</TD>' || NL;
2054
2055 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_WRAP_STYLE || ' headers="actionNote_3">' ||
2056 nvl(l_history.note, ' ') || '</TD>' || NL;
2057
2058 l_document_hist := l_document_hist || '</TR>' || NL;
2059
2060 ELSE
2061
2062 l_document_hist := l_document_hist || NL || '<TR>' || NL;
2063
2064 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="seqNum_3">' ||
2065 nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
2066
2067 /* Bug 2788683 start */
2068 /* if action history is updated by vendor
2069 * show vendor true name(vendor name)
2070 * else action history is updated by buyer
2071 * show buyer's true name
2072 */
2073 IF l_history.employee_id IS NULL THEN
2074 SELECT fu.user_name, hp.party_name
2075 INTO l_user_name, l_party_name
2076 FROM fnd_user fu,
2077 hz_parties hp
2078 WHERE hp.party_id = fu.customer_id
2079 AND fu.user_id = l_history.created_by;
2080
2081 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
2082
2083 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="employee_3">' || l_party_name || '(' || l_vendor_name || ')' || '</TD>' || NL;
2084 ELSE
2085 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="employee_3">' ||
2086 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
2087 END IF;
2088 /* Bug 2788683 end */
2089
2090 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="action_3">' ||
2091 nvl(l_history.action, ' ') || '</TD>' || NL;
2092
2093 /*Modified as part of bug 7554321 changing date format*/
2094 if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and FND_RELEASE.POINT_VERSION >= 1 )
2095 or (FND_RELEASE.MAJOR_VERSION > 12) then
2096 l_date_text := nvl(to_char(l_history.action_date,
2097 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
2098 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''),' ') ;
2099 else
2100 l_date_text := nvl(to_char(l_history.action_date), ' ') ;
2101 end if;
2102
2103
2104
2105 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="date_3">' ||
2106 l_date_text || '</TD>' || NL;
2107 /*Modified as part of bug 7554321 changing date format*/
2108
2109 l_document_hist := l_document_hist || '<TD ' || L_TABLE_CELL_HIGH_STYLE || ' headers="actionNote_3">' ||
2110 nvl(l_history.note, ' ') || '</TD>' || NL;
2111
2112 l_document_hist := l_document_hist || '</TR>' || NL;
2113
2114 END IF;
2115
2116 end loop;
2117
2118 close history_csr;
2119
2120 l_document := l_document || l_document_pend || l_document_hist || '</TABLE>';
2121
2122 document := l_document;
2123
2124 elsif (display_type = 'text/plain') then
2125
2126 document := '';
2127
2128 end if;
2129 END get_action_history_html;
2130
2131 /*
2132 ** This procedure will get the list of pending approvers for the requisition
2133 */
2134 PROCEDURE get_pending_action_html(p_item_type in varchar2,
2135 p_item_key in varchar2,
2136 max_seqno in number,
2137 p_document out NOCOPY varchar2) IS
2138
2139 l_document_id po_requisition_lines.requisition_header_id%TYPE;
2140 l_object_type po_action_history.object_type_code%TYPE;
2141 l_org_id po_requisition_lines.org_id%TYPE;
2142
2143 l_document VARCHAR2(32000) := '';
2144 l_sub_document VARCHAR2(32000) := '';
2145 l_one_row VARCHAR2(32000) := '';
2146
2147 l_history history_record;
2148 l_history_seq number;
2149 noPendAppr number := 0;
2150 l_rcount number :=0;
2151 l_is_po_approval boolean := true;
2152 approverList ame_util.approversTable;
2153 upperLimit integer;
2154 fullName varchar2(240);
2155
2156 NL VARCHAR2(1) := fnd_global.newline;
2157
2158 --SQL What: Select NULL to the last two columns of pending_csr
2159 --SQL Why: Be consistent to the change of history_record without changing
2160 -- the existing functionality of get_pending_action_html
2161 CURSOR pending_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
2162
2163 SELECT pal.SEQUENCE_NUM,per.FULL_NAME,null,null,null,null,
2164 NULL, NULL, NULL /* bug 2788683*/
2165 FROM per_all_people_f per, -- Bug 3404451
2166 po_approval_list_lines pal,
2167 po_approval_list_headers pah
2168 WHERE pah.document_id = v_document_id
2169 and pah.document_type = v_object_type
2170 and pah.latest_revision = 'Y'
2171 and pal.APPROVAL_LIST_HEADER_ID = pah.APPROVAL_LIST_HEADER_ID
2172 and pal.STATUS IS NULL
2173 and per.PERSON_ID = pal.APPROVER_ID
2174 and trunc(sysdate) between per.EFFECTIVE_START_DATE
2175 and per.EFFECTIVE_END_DATE
2176 ORDER BY 1 desc;
2177
2178 CURSOR count_rows(v_document_id NUMBER, v_object_type VARCHAR2) IS
2179 SELECT count(*)
2180 FROM per_all_people_f per,
2181 po_approval_list_lines pal,
2182 po_approval_list_headers pah
2183 WHERE pah.document_id = v_document_id
2184 and pah.document_type = v_object_type
2185 and pah.latest_revision = 'Y'
2186 and pal.APPROVAL_LIST_HEADER_ID = pah.APPROVAL_LIST_HEADER_ID
2187 and pal.STATUS IS NULL
2188 and per.PERSON_ID = pal.APPROVER_ID
2189 and trunc(sysdate) between per.EFFECTIVE_START_DATE
2190 and per.EFFECTIVE_END_DATE;
2191 BEGIN
2192
2193 l_document_id := wf_engine.GetItemAttrNumber
2194 (itemtype => p_item_type,
2195 itemkey => p_item_key,
2196 aname => 'DOCUMENT_ID');
2197
2198 l_org_id := wf_engine.GetItemAttrNumber
2199 (itemtype => p_item_type,
2200 itemkey => p_item_key,
2201 aname => 'ORG_ID');
2202
2203 l_object_type := 'REQUISITION';
2204
2205 l_document := NL || NL || '<!-- PENDING APPROVER -->'|| NL || NL;
2206
2207 l_document := l_document || '<!-- the value of maxseqno in pending' || max_seqno || '-->' || NL;
2208
2209
2210 l_is_po_approval := is_po_approval_type(p_item_type, p_item_key);
2211
2212 if(l_is_po_approval = true) then
2213 open count_rows(l_document_id, l_object_type);
2214 loop
2215 fetch count_rows into l_rcount;
2216 exit when count_rows%notfound;
2217 end loop;
2218
2219 l_history_seq := max_seqno + l_rcount;
2220
2221 open pending_csr(l_document_id, l_object_type);
2222
2223 loop
2224
2225 fetch pending_csr into l_history;
2226
2227 exit when pending_csr%notfound;
2228
2229
2230 l_history_seq := l_history_seq - 1;
2231
2232 noPendAppr := noPendAppr + 1;
2233 l_one_row := '<TR>' || NL;
2234
2235 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="seqNum_3">'
2236 || nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
2237
2238 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="employee_3">' ||
2239 nvl(l_history.employee_name, ' ') || '</TD>' || NL;
2240
2241 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="action_3">' ||
2242 nvl(l_history.action, ' ') || '</TD>' || NL;
2243
2244 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="date_3">' ||
2245 nvl(to_char(l_history.action_date), ' ') || '</TD>' || NL;
2246
2247 l_one_row := l_one_row || '<TD ' || L_TABLE_CELL_STYLE || ' headers="actionNote_3">' ||
2248 nvl(l_history.note, ' ') || '</TD>' || NL;
2249 l_one_row := l_one_row || '</TR>' || NL;
2250
2251 if noPendAppr <> l_rcount THEN
2252 l_sub_document := l_sub_document || l_one_row;
2253 END IF;
2254
2255 end loop;
2256 close pending_csr;
2257 else
2258 /* use ame approval */
2259 l_document := l_document || '<!-- Calling AME -->' || NL;
2260 ame_api.getOldApprovers(applicationIdIn=>por_ame_approval_list.applicationId,
2261 transactionIdIn=>l_document_id,
2262 transactionTypeIn=>por_ame_approval_list.transactionType,
2263 oldApproversOut=>approverList);
2264
2265 upperLimit := approverList.count;
2266 l_history_seq := max_seqno + upperLimit;
2267 for i in reverse 1 .. upperLimit loop
2268
2269 if(approverList(i).person_id is not null and approverList(i).approval_status is null) then
2270 select full_name
2271 into fullName from per_all_people_f
2272 where person_id = approverList(i).person_id
2273 and trunc(sysdate) between effective_start_date and effective_end_date;
2274
2275 l_history_seq := l_history_seq + 1;
2276 noPendAppr := noPendAppr + 1;
2277 l_one_row := '<TR>' || NL;
2278
2279 l_one_row := l_one_row || '<TD class="tabledata" width=5% nowrap align=left headers="seqNum_3">'
2280 || nvl(to_char(l_history_seq), ' ') || '</TD>' || NL;
2281 l_one_row := l_one_row || '<TD class="tabledata" width=27% nowrap align=left headers="employee_3">' ||
2282 nvl(fullName, ' ') || '</TD>' || NL;
2283 l_one_row := l_one_row || '<TD class="tabledata" nowrap width=15% align=left headers="action_3">' ||
2284 ' ' || '</TD>' || NL;
2285 l_one_row := l_one_row || '<TD class="tabledata" nowrap width=12% align=left headers="date_3">' ||
2286 ' ' || '</TD>' || NL;
2287
2288 l_one_row := l_one_row || '<TD class="tabledata" width=41% align=left headers="actionNote_3">' ||
2289 ' ' || '</TD>' || NL;
2290 l_one_row := l_one_row || '</TR>' || NL;
2291
2292 if noPendAppr <> upperLimit THEN
2293 l_sub_document := l_sub_document || l_one_row;
2294 END IF;
2295
2296 end if; -- person id
2297 end loop;
2298 end if; -- po or ame
2299
2300 l_document := l_document || l_sub_document;
2301
2302 if noPendAppr > 1 then
2303 p_document := l_document;
2304 else
2305 p_document := '';
2306 end if;
2307
2308 END get_pending_action_html;
2309
2310
2311 /* Bug #1581410 :kagarwal
2312 ** Desc: This procedure is not being used now. Added return to
2313 ** for backward compatibility.
2314 */
2315
2316 PROCEDURE get_action_history(document_id in varchar2,
2317 display_type in varchar2,
2318 document in out NOCOPY varchar2,
2319 document_type in out NOCOPY varchar2) IS
2320
2321 l_item_type wf_items.item_type%TYPE;
2322 l_item_key wf_items.item_key%TYPE;
2323
2324 l_document_id po_requisition_lines.requisition_header_id%TYPE;
2325 l_object_type po_action_history.object_type_code%TYPE;
2326 l_org_id po_requisition_lines.org_id%TYPE;
2327
2328 l_document VARCHAR2(32000) := '';
2329
2330 l_history history_record;
2331
2332 NL VARCHAR2(1) := fnd_global.newline;
2333
2334 --SQL What: Query action history which is updated by both buyer and vendor
2335 --SQL Why: Since vendor doesn't have employee id, added outer join;
2336 CURSOR history_csr(v_document_id NUMBER, v_object_type VARCHAR2) IS
2337
2338 SELECT poh.SEQUENCE_NUM,
2339 per.FULL_NAME,
2340 polc.DISPLAYED_FIELD,
2341 poh.ACTION_DATE,
2342 poh.NOTE,
2343 poh.OBJECT_REVISION_NUM,
2344 poh.employee_id, /* bug 2788683 */
2345 poh.created_by /* bug 2788683 */
2346 from po_action_history poh,
2347 per_all_people_f per, -- Bug 3404451
2348 po_lookup_codes polc
2349 where OBJECT_TYPE_CODE = v_object_type
2350 and poh.action_code = polc.lookup_code
2351 and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
2352 and per.person_id(+) = poh.employee_id /* bug 2788683 */
2353 and trunc(sysdate) between per.effective_start_date(+)
2354 and per.effective_end_date(+)
2355 and OBJECT_ID = v_document_id
2356 UNION ALL
2357 SELECT poh.SEQUENCE_NUM,
2358 per.FULL_NAME,
2359 NULL,
2360 poh.ACTION_DATE,
2361 poh.NOTE,
2362 poh.OBJECT_REVISION_NUM,
2363 poh.employee_id, /* bug 2788683 */
2364 poh.created_by /* bug 2788683 */
2365 from po_action_history poh,
2366 per_all_people_f per -- Bug 3404451
2367 where OBJECT_TYPE_CODE = v_object_type
2368 and poh.action_code is null
2369 and per.person_id(+) = poh.employee_id /* bug 2788683 */
2370 and trunc(sysdate) between per.effective_start_date(+)
2371 and per.effective_end_date(+)
2372 and OBJECT_ID = v_document_id
2373 order by 1 desc;
2374
2375 BEGIN
2376
2377 return;
2378
2379 END get_action_history;
2380
2381 function ConstructHeaderInfo(l_req_amount in varchar2,
2382 l_currency_code in varchar2,
2383 l_tax_amt in number,
2384 l_tax_amount in varchar2,
2385 l_description in varchar2,
2386 l_forwarded_from in varchar2,
2387 l_preparer in varchar2,
2388 l_note in varchar2,
2389 l_notification_id in number) return varchar2 is
2390
2391 l_document VARCHAR2(32000) := '';
2392
2393 NL VARCHAR2(1) := fnd_global.newline;
2394
2395 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
2396 l_type WF_MESSAGE_ATTRIBUTES.TYPE%TYPE;
2397 l_subtype WF_MESSAGE_ATTRIBUTES.SUBTYPE%TYPE;
2398 l_format WF_MESSAGE_ATTRIBUTES.FORMAT%TYPE;
2399
2400 BEGIN
2401
2402 -- style sheet
2403
2404 l_document := l_document || '<LINK REL=STYLESHEET HREF="' || l_base_href || '/OA_HTML/PORSTYL2.css" TYPE=text/css>' || NL;
2405
2406 /* Bug 2480327
2407 ** notification UI enhancement
2408 when wf patch G is installed,
2409 hide notification header summary for new notification
2410 */
2411 if (wf_core.translate('WF_HEADER_ATTR') = 'Y') then
2412 begin
2413 wf_notification.GetAttrInfo(nid => l_notification_id,
2414 aname => '#HDR_1',
2415 atype => l_type,
2416 subtype => l_subtype,
2417 format => l_format);
2418 if (l_type is not null) then
2419 return l_document;
2420 end if;
2421 exception
2422 when others then
2423 null;
2424 end;
2425 end if;
2426
2427 l_document := l_document || NL || '<!-- REQ SUMMARY -->'|| NL || NL || '<P>';
2428
2429 l_document := l_document || print_heading(fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_SUMMARY'));
2430
2431 -- New Table Style
2432
2433 l_document := l_document || '<TABLE ' || L_TABLE_STYLE || 'SUMMARY=""><TR>
2434 <TD ' || L_TABLE_LABEL_STYLE || ' width="15%">' ||
2435 fnd_message.get_string('PO', 'PO_WF_NOTIF_REQ_AMOUNT')
2436 || ' </TD>' || NL;
2437
2438 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || ' >'
2439 || l_req_amount || ' ' || l_currency_code || '</TD></TR>' || NL;
2440
2441 if l_tax_amt > 0 then
2442
2443 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2444 fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') || ' </TD>' || NL;
2445
2446 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>'
2447 || l_tax_amount || ' ' || l_currency_code || '</TD></TR>' || NL;
2448
2449 end if;
2450
2451 l_document := l_document || NL;
2452
2453 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2454 fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION')
2455 || ' </TD>' || NL;
2456
2457 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_description || '<BR></TD></TR>' || NL;
2458
2459 if l_forwarded_from is not null then
2460
2461 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2462 fnd_message.get_string('PO', 'PO_WF_NOTIF_FROM') ||' </TD>' || NL;
2463
2464 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_forwarded_from || '<BR></TD></TR>' || NL;
2465
2466 end if;
2467
2468 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2469 fnd_message.get_string('PO', 'PO_WF_NOTIF_PREPARER') ||' </TD>' || NL;
2470
2471 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_preparer || '<BR></TD></TR>' || NL;
2472
2473 l_document := l_document || '<TR><TD ' || L_TABLE_LABEL_STYLE || '>' ||
2474 fnd_message.get_string('PO', 'PO_WF_NOTIF_NOTE') || ' </TD>' || NL;
2475
2476 l_document := l_document || '<TD ' || L_TABLE_CELL_STYLE || '>' || l_note || '<BR></TD></TR>' || NL;
2477
2478 l_document := l_document || '</TABLE>' || NL;
2479
2480 return l_document;
2481
2482 END ConstructHeaderInfo;
2483
2484
2485
2486 function print_heading(l_text in varchar2) return varchar2 is
2487
2488 l_document varchar2(1000) := '';
2489
2490 NL VARCHAR2(1) := fnd_global.newline;
2491 l_base_href VARCHAR(2000) := fnd_profile.value('APPS_FRAMEWORK_AGENT');
2492
2493 begin
2494
2495 l_document := '<TABLE width="100%" border=0 cellpadding=0 cellspacing=0 SUMMARY="">';
2496 l_document := l_document || '<TR>'||NL;
2497 l_document := l_document || '<TD class=subheader1>'|| l_text;
2498 l_document := l_document || '</TD></TR>';
2499
2500 -- horizontal line
2501 l_document := l_document || '<TR>' || NL;
2502 l_document := l_document || '<TD colspan=2 height=1 bgcolor=#cccc99>
2503 <img src=' || l_base_href || '/OA_MEDIA/FNDITPNT.gif ALT=""></TD></TR>';
2504
2505 l_document := l_document || '<TR><TD colspan=2 height=5> </TR></TABLE>' || NL;
2506
2507 return l_document;
2508
2509 end;
2510
2511 PROCEDURE update_action_history (p_action_code IN VARCHAR2,
2512 p_recipient_id IN NUMBER,
2513 p_note IN VARCHAR2,
2514 p_req_header_id IN NUMBER,
2515 p_current_id IN NUMBER,
2516 p_doc_type IN po_action_history.OBJECT_TYPE_CODE%TYPE)
2517 IS
2518 pragma AUTONOMOUS_TRANSACTION;
2519
2520 l_progress VARCHAR2(100) := '000';
2521
2522 l_object_sub_type_code PO_ACTION_HISTORY.OBJECT_SUB_TYPE_CODE%TYPE;
2523 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
2524 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
2525 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
2526 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
2527 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
2528 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
2529 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
2530 l_approval_group_id PO_ACTION_HISTORY.APPROVAL_GROUP_ID%TYPE;
2531
2532 begin
2533
2534 SELECT max(sequence_num)
2535 INTO l_sequence_num
2536 FROM PO_ACTION_HISTORY
2537 WHERE object_type_code = p_doc_type
2538 AND object_id = p_req_header_id;
2539
2540 -- a person can be in more than one approval groups
2541 -- we add one row for this person after he requests information
2542
2543 -- Bug 8343188: Removed action_code IS NULL from the where clause.
2544 begin
2545 SELECT object_sub_type_code,
2546 object_revision_num, approval_path_id, request_id,
2547 program_application_id, program_date, program_id
2548 INTO l_object_sub_type_code,
2549 l_object_revision_num, l_approval_path_id, l_request_id,
2550 l_program_application_id, l_program_date, l_program_id
2551 FROM PO_ACTION_HISTORY
2552 WHERE object_type_code = p_doc_type
2553 AND object_id = p_req_header_id
2554 AND employee_id = p_current_id
2555 AND action_code IS NULL
2556 AND rownum=1;
2557 exception
2558 WHEN no_data_found THEN
2559 SELECT object_sub_type_code,
2560 object_revision_num, approval_path_id, request_id,
2561 program_application_id, program_date, program_id
2562 INTO l_object_sub_type_code,
2563 l_object_revision_num, l_approval_path_id, l_request_id,
2564 l_program_application_id, l_program_date, l_program_id
2565 FROM PO_ACTION_HISTORY
2566 WHERE object_type_code = p_doc_type
2567 AND object_id = p_req_header_id
2568 AND employee_id = p_current_id
2569 AND rownum=1;
2570 end;
2571
2572 begin
2573 SELECT distinct approval_group_id
2574 INTO l_approval_group_id
2575 FROM PO_ACTION_HISTORY
2576 WHERE object_type_code = p_doc_type
2577 AND object_id = p_req_header_id
2578 AND employee_id = p_recipient_id;
2579
2580 -- If a person is not in approval group or is in more than one approval groups,
2581 -- we don't show group name.
2582 exception
2583 when others then
2584 l_approval_group_id := null;
2585
2586 end;
2587
2588 l_progress := '010';
2589
2590 -- If an approver belongs to n groups, he will receive n notifications.
2591 -- After he takes action with one of the notifications, only ONE record in
2592 -- action_history table should be updated.
2593
2594
2595 UPDATE PO_ACTION_HISTORY
2596 SET last_update_date = sysdate,
2597 last_updated_by = fnd_global.user_id,
2598 last_update_login = fnd_global.login_id ,
2599 action_date = sysdate,
2600 action_code = p_action_code,
2601 note = p_note,
2602 offline_code = decode(offline_code,
2603 'PRINTED', 'PRINTED', NULL)
2604 WHERE employee_id = p_current_id
2605 AND object_id = p_req_header_id
2606 AND object_type_code = p_doc_type
2607 AND action_code IS NULL
2608 AND rownum=1;
2609
2610
2611
2612 l_progress := '020';
2613
2614
2615 po_forward_sv1.insert_action_history (
2616 p_req_header_id,
2617 p_doc_type,
2618 l_object_sub_type_code,
2619 l_sequence_num + 1,
2620 NULL,
2621 NULL,
2622 p_recipient_id,
2623 l_approval_path_id,
2624 NULL,
2625 l_object_revision_num,
2626 NULL, /* offline_code */
2627 l_request_id,
2628 l_program_application_id,
2629 l_program_id,
2630 l_program_date,
2631 fnd_global.user_id,
2632 fnd_global.login_id,
2633 l_approval_group_id);
2634
2635 l_progress := '030';
2636
2637 commit;
2638 EXCEPTION
2639 WHEN OTHERS THEN
2640 wf_core.context('PO_WF_REQ_NOTIFICATION','update_action_history',l_progress,sqlerrm);
2641 RAISE;
2642 end;
2643
2644
2645 PROCEDURE post_approval_notif(itemtype in varchar2,
2646 itemkey in varchar2,
2647 actid in number,
2648 funcmode in varchar2,
2649 resultout out NOCOPY varchar2) is
2650 l_nid number;
2651 l_forwardTo varchar2(240);
2652 l_result varchar2(100);
2653 l_forward_to_username_response varchar2(240) :='';
2654 l_req_header_id po_requisition_headers.requisition_header_id%TYPE;
2655 l_action po_action_history.action_code%TYPE;
2656 l_new_recipient_id wf_roles.orig_system_id%TYPE;
2657 l_current_recipient_id wf_roles.orig_system_id%TYPE;
2658 l_origsys wf_roles.orig_system%TYPE;
2659 l_is_ame_approval varchar2(10);
2660 p_itemtype varchar2(100);
2661 p_itemkey varchar2(100);
2662 l_doc_string varchar2(200);
2663 l_preparer_user_name varchar2(100);
2664
2665 -- Context setting revamp <variable addition start>
2666 l_responder_id fnd_user.user_id%TYPE;
2667 l_session_user_id NUMBER;
2668 l_session_resp_id NUMBER;
2669 l_session_appl_id NUMBER;
2670 l_preparer_resp_id NUMBER;
2671 l_preparer_appl_id NUMBER;
2672 l_progress VARCHAR2(1000);
2673 l_preserved_ctx VARCHAR2(5);
2674 -- Context setting revamp <variable addition end>
2675 l_doc_type po_action_history.OBJECT_TYPE_CODE%TYPE;
2676
2677 --Bug 11664961
2678 l_original_recipient wf_notifications.original_recipient%TYPE;
2679 l_current_recipient_role wf_notifications.recipient_role%TYPE;
2680
2681 -- MIPR Changes
2682 l_doc_id NUMBER;
2683 l_mipr_type_disp VARCHAR2(100);
2684
2685
2686 -- MIPR Changes
2687 CURSOR mipr_csr(p_doc_id NUMBER) IS
2688 SELECT flv.MEANING
2689 FROM po_requisition_headers_all prh, FND_LOOKUP_VALUES FLV
2690 WHERE prh.requisition_header_id = p_doc_id
2691 AND prh.clm_mipr_type = flv.LOOKUP_CODE
2692 AND NVL(prh.cancel_flag,'N') = 'N'
2693 AND FLV.LOOKUP_TYPE='MIPR_TYPE'
2694 AND FLV.VIEW_APPLICATION_ID = 201;
2695
2696 begin
2697
2698 l_progress := '001';
2699
2700 l_is_ame_approval := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2701 itemkey => itemkey,
2702 aname => 'IS_AME_APPROVAL'
2703 );
2704 --Bug 11664961 Adding timeout
2705 if (funcmode IN ('FORWARD', 'QUESTION', 'ANSWER','TIMEOUT')) then
2706
2707 if (funcmode = 'FORWARD') then
2708 l_action := 'DELEGATE';
2709 elsif (funcmode = 'QUESTION') then
2710 l_action := 'QUESTION';
2711 elsif (funcmode = 'ANSWER') then
2712 l_action := 'ANSWER';
2713 elsif (funcmode = 'TIMEOUT') then --Bug 11664961
2714 l_action := 'NO ACTION';
2715 end if;
2716
2717 l_req_header_id := wf_engine.GetItemAttrNumber
2718 (itemtype => itemtype,
2719 itemkey => itemkey,
2720 aname => 'DOCUMENT_ID');
2721 l_doc_type := wf_engine.GetItemAttrText
2722 (itemtype => itemtype,
2723 itemkey => itemkey,
2724 aname => 'DOCUMENT_TYPE');
2725 -- MIPR changes
2726 OPEN mipr_csr(l_req_header_id);
2727 FETCH mipr_csr INTO l_mipr_type_disp;
2728 CLOSE mipr_csr;
2729
2730 IF l_mipr_type_disp IS NOT NULL THEN
2731 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
2732 itemkey => itemkey,
2733 aname => 'DOCUMENT_TYPE_DISP',
2734 avalue => l_mipr_type_disp);
2735 END IF;
2736
2737 --Bug 11664961 (IF condition and the code in ELSE portion added)
2738 IF (l_action <> 'NO ACTION') THEN
2739
2740 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_NEW_ROLE, l_origsys, l_new_recipient_id);
2741
2742 ELSE
2743 BEGIN
2744 SELECT original_recipient, Decode(MORE_INFO_ROLE,
2745 NULL, RECIPIENT_ROLE,
2746 MORE_INFO_ROLE)
2747
2748 INTO l_original_recipient, l_current_recipient_role
2749 FROM wf_notifications
2750 WHERE notification_id = WF_ENGINE.context_nid
2751 AND ( MORE_INFO_ROLE IS NOT NULL OR
2752 RECIPIENT_ROLE <> ORIGINAL_RECIPIENT );
2753 EXCEPTION
2754 WHEN OTHERS THEN
2755 l_original_recipient := NULL;
2756 END;
2757
2758 IF l_original_recipient IS NOT NULL THEN
2759 Wf_Directory.GetRoleOrigSysInfo(l_original_recipient, l_origsys, l_new_recipient_id);
2760 END IF;
2761
2762 END IF;
2763
2764 /* bug 4667656 : We should not be allowing the delegation of a notication
2765 to a user who is not an employee. */
2766
2767 if((funcmode = 'FORWARD') AND (l_origsys <> 'PER')) then
2768 fnd_message.set_name('PO', 'PO_INVALID_USER_FOR_REASSIGN');
2769 app_exception.raise_exception;
2770 end if;
2771
2772 l_progress := '002';
2773
2774 if (funcmode = 'ANSWER') then
2775 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_MORE_INFO_ROLE, l_origsys, l_current_recipient_id);
2776
2777 ELSIF (funcmode = 'TIMEOUT') THEN --Bug 11664961
2778 Wf_Directory.GetRoleOrigSysInfo(l_current_recipient_role, l_origsys, l_current_recipient_id);
2779
2780 else
2781 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_RECIPIENT_ROLE, l_origsys, l_current_recipient_id);
2782
2783 end if;
2784
2785 l_progress := '003';
2786
2787 l_is_ame_approval := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2788 itemkey => itemkey,
2789 aname => 'IS_AME_APPROVAL'
2790 );
2791
2792 if ( funcmode = 'FORWARD' AND l_is_ame_approval = 'Y' ) then
2793 po_wf_util_pkg.SetItemAttrNumber( itemtype => itemtype,
2794 itemkey => itemkey,
2795 aname => 'APPROVER_EMPID',
2796 avalue => l_new_recipient_id
2797 );
2798 end if;
2799
2800 l_progress := '004';
2801
2802 IF l_new_recipient_id IS NOT NULL THEN --Bug 11664961(IF condition added)
2803 update_action_history(p_action_code => l_action,
2804 p_recipient_id => l_new_recipient_id,
2805 p_note => WF_ENGINE.CONTEXT_USER_COMMENT,
2806 p_req_header_id => l_req_header_id,
2807 p_current_id => l_current_recipient_id,
2808 p_doc_type=> l_doc_type);
2809 END IF;
2810
2811 l_progress := '005';
2812
2813 IF (funcmode <> 'TIMEOUT') THEN --Bug 11664961(IF condition added)
2814 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
2815 END IF;
2816
2817 return;
2818 end if;
2819
2820
2821 if (funcmode = 'RESPOND') then
2822
2823 l_nid := WF_ENGINE.context_nid;
2824
2825 l_result := wf_notification.GetAttrText(l_nid, 'RESULT');
2826
2827 l_progress := '006';
2828
2829 if((l_result = 'FORWARD') or (l_result = 'APPROVE_AND_FORWARD')) then
2830
2831 l_forwardTo := wf_notification.GetAttrText(l_nid, 'FORWARD_TO_USERNAME_RESPONSE');
2832
2833 l_forward_to_username_response := wf_engine.GetItemAttrText (itemtype => itemtype,
2834 itemkey => itemkey,
2835 aname => 'FORWARD_TO_USERNAME_RESPONSE');
2836
2837 if(l_forwardTo is null) then
2838 fnd_message.set_name('ICX', 'ICX_POR_WF_NOTIF_NO_USER');
2839 app_exception.raise_exception;
2840 end if;
2841 end if;
2842
2843 l_progress := '007';
2844
2845 -- Context setting revamp <start>
2846 -- <debug start>
2847 if (wf_engine.preserved_context = TRUE) then
2848 l_preserved_ctx := 'TRUE';
2849 else
2850 l_preserved_ctx := 'FALSE';
2851 end if;
2852 l_progress := 'notif callback l_is_ame_approval preserved_ctx : '||l_preserved_ctx || 'l_is_ame_approval :' || l_is_ame_approval ;
2853 IF (g_po_wf_debug = 'Y') THEN
2854 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2855 END IF;
2856 -- <debug end>
2857
2858
2859 SELECT fu.USER_ID
2860 INTO l_responder_id
2861 FROM fnd_user fu,
2862 wf_notifications wfn
2863 WHERE wfn.notification_id = l_nid
2864 AND wfn.original_recipient = fu.user_name;
2865
2866 -- <debug start>
2867 l_progress := '010 notif callback -responder id : '||l_responder_id;
2868 IF (g_po_wf_debug = 'Y') THEN
2869 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2870 END IF;
2871 -- <debug end>
2872
2873 --Bug 5389914
2874 --Fnd_Profile.Get('USER_ID',l_session_user_id);
2875 --Fnd_Profile.Get('RESP_ID',l_session_resp_id);
2876 --Fnd_Profile.Get('RESP_APPL_ID',l_session_appl_id);
2877 l_session_user_id := fnd_global.user_id;
2878 l_session_resp_id := fnd_global.resp_id;
2879 l_session_appl_id := fnd_global.resp_appl_id;
2880
2881 select PARENT_ITEM_TYPE, PARENT_ITEM_KEY
2882 into p_itemtype,p_itemkey
2883 from wf_items
2884 where item_type = itemtype and item_key =itemkey;
2885
2886 if ( l_is_ame_approval = 'Y' ) then
2887 l_progress := 'notif callback watch for l_is_ame_approval p_itemtype,'||p_itemtype ||' p_itemkey ' || p_itemkey ;
2888 IF (g_po_wf_debug = 'Y') THEN
2889 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2890 END IF;
2891 else
2892 l_is_ame_approval := po_wf_util_pkg.GetItemAttrText( itemtype => p_itemtype,
2893 itemkey => p_itemkey,
2894 aname => 'IS_AME_APPROVAL'
2895 );
2896 l_progress := 'notif callback l_is_ame_approval was not Y hence get this from parent wf now p_itemtype,'
2897 ||p_itemtype || 'p_itemkey ' || p_itemkey || 'l_is_ame_approval :'|| l_is_ame_approval ;
2898
2899 IF (g_po_wf_debug = 'Y') THEN
2900 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2901 END IF;
2902 end if;
2903
2904
2905 IF (l_session_user_id = -1) THEN
2906 l_session_user_id := NULL;
2907 END IF;
2908
2909 IF (l_session_resp_id = -1) THEN
2910 l_session_resp_id := NULL;
2911 END IF;
2912
2913 IF (l_session_appl_id = -1) THEN
2914 l_session_appl_id := NULL;
2915 END IF;
2916
2917 -- <debug start>
2918 l_progress :='020 notification callback ses_userid: '||l_session_user_id
2919 ||' sess_resp_id '||l_session_resp_id||' sess_appl_id '
2920 ||l_session_appl_id;
2921 IF (g_po_wf_debug = 'Y') THEN
2922 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2923 END IF;
2924 -- <debug end>
2925
2926 -- bug 4901406 <start> : need to shift the setting of the preparer resp and appl id
2927 -- to here, it was not initialized inside the if condition if the control went to the
2928 -- else part.
2929
2930 l_preparer_resp_id :=
2931 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
2932 itemkey => itemkey,
2933 aname => 'RESPONSIBILITY_ID');
2934 l_preparer_appl_id :=
2935 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
2936 itemkey => itemkey,
2937 aname => 'APPLICATION_ID');
2938
2939
2940 l_progress := '030 notif callback prep resp_id:'||l_preparer_resp_id
2941 ||' prep appl id '||l_preparer_appl_id;
2942 IF (g_po_wf_debug = 'Y') THEN
2943 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2944 END IF;
2945
2946 -- bug 4901406 <end>
2947
2948 if (l_responder_id is not null) then
2949 if (l_responder_id <> l_session_user_id) then
2950 /* possible in 2 scenarios :
2951 1. when the response is made from email using guest user feature
2952 2. When the response is made from sysadmin login
2953 */
2954
2955 -- <debug start>
2956 l_progress := '050 notif setting RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
2957 IF (g_po_wf_debug = 'Y') THEN
2958 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2959 END IF;
2960 -- <debug end>
2961
2962 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2963 itemkey => itemkey,
2964 aname => 'RESPONDER_USER_ID',
2965 avalue => l_responder_id);
2966 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2967 itemkey => itemkey,
2968 aname => 'RESPONDER_RESP_ID',
2969 avalue => l_preparer_resp_id);
2970 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2971 itemkey => itemkey,
2972 aname => 'RESPONDER_APPL_ID',
2973 avalue => l_preparer_appl_id);
2974
2975 if( l_is_ame_approval = 'Y' ) then
2976 l_progress := '05A notif setting for parent wf RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
2977 IF (g_po_wf_debug = 'Y') THEN
2978 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
2979 END IF;
2980
2981 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
2982 itemkey => p_itemkey,
2983 aname => 'RESPONDER_USER_ID',
2984 avalue => l_responder_id);
2985 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
2986 itemkey => p_itemkey,
2987 aname => 'RESPONDER_RESP_ID',
2988 avalue => l_preparer_resp_id);
2989 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
2990 itemkey => p_itemkey,
2991 aname => 'RESPONDER_APPL_ID',
2992 avalue => l_preparer_appl_id);
2993
2994 end if;
2995 else
2996 if (l_session_resp_id is null) THEN
2997 /* possible when the response is made from the default worklist
2998 without choosing a valid responsibility */
2999
3000
3001 l_progress := '055 notif setting l_session_resp_id is null RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
3002 IF (g_po_wf_debug = 'Y') THEN
3003 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3004 END IF;
3005
3006 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3007 itemkey => itemkey,
3008 aname => 'RESPONDER_USER_ID',
3009 avalue => l_responder_id);
3010 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3011 itemkey => itemkey,
3012 aname => 'RESPONDER_RESP_ID',
3013 avalue => l_preparer_resp_id);
3014 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3015 itemkey => itemkey,
3016 aname => 'RESPONDER_APPL_ID',
3017 avalue => l_preparer_appl_id);
3018 if( l_is_ame_approval = 'Y' ) then
3019 l_progress := '05B notif setting for parent wf RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
3020 IF (g_po_wf_debug = 'Y') THEN
3021 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3022 END IF;
3023
3024 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3025 itemkey => p_itemkey,
3026 aname => 'RESPONDER_USER_ID',
3027 avalue => l_responder_id);
3028 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3029 itemkey => p_itemkey,
3030 aname => 'RESPONDER_RESP_ID',
3031 avalue => l_preparer_resp_id);
3032 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3033 itemkey => p_itemkey,
3034 aname => 'RESPONDER_APPL_ID',
3035 avalue => l_preparer_appl_id);
3036 end if;
3037 else
3038 /* all values available - possible when the response is made
3039 after choosing a correct responsibility */
3040 /* bug 5333226 : If the values of responsibility_id and application
3041 id are available but are incorrect - i.e. not conforming to say the
3042 sls (subledger security). This may happen when a response is made
3043 through the email or the background process picks the wf up.
3044 This may happen due to the fact that the mailer / background process
3045 carries the context set by the notification/wf it processed last*/
3046
3047 -- <debug start>
3048 l_progress := '060 notif setting l_session_resp_id is not null RESPONDER_USER_ID l_responder_id:'|| l_responder_id || 'l_preserved_ctx :' || l_preserved_ctx ;
3049 IF (g_po_wf_debug = 'Y') THEN
3050 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3051 END IF;
3052 -- <debug end>
3053 if ( l_preserved_ctx = 'TRUE') then
3054
3055
3056 l_progress := '070 notif setting l_session_resp_id is null RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
3057 IF (g_po_wf_debug = 'Y') THEN
3058 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3059 END IF;
3060
3061 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3062 itemkey => itemkey,
3063 aname => 'RESPONDER_USER_ID',
3064 avalue => l_responder_id);
3065 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3066 itemkey => itemkey,
3067 aname => 'RESPONDER_RESP_ID',
3068 avalue => l_session_resp_id);
3069 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3070 itemkey => itemkey,
3071 aname => 'RESPONDER_APPL_ID',
3072 avalue => l_session_appl_id);
3073 if( l_is_ame_approval = 'Y' ) then
3074 l_progress := '05C notif setting for parent wf RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
3075 IF (g_po_wf_debug = 'Y') THEN
3076 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3077 END IF;
3078
3079
3080 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3081 itemkey => p_itemkey,
3082 aname => 'RESPONDER_USER_ID',
3083 avalue => l_responder_id);
3084 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3085 itemkey => p_itemkey,
3086 aname => 'RESPONDER_RESP_ID',
3087 avalue => l_session_resp_id);
3088 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3089 itemkey => p_itemkey,
3090 aname => 'RESPONDER_APPL_ID',
3091 avalue => l_session_appl_id);
3092
3093 end if;
3094 else
3095 -- <debug start>
3096 l_progress := '080 notif setting l_session_resp_id is null RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
3097 IF (g_po_wf_debug = 'Y') THEN
3098 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3099 END IF;
3100 -- <debug end>
3101
3102 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3103 itemkey => itemkey,
3104 aname => 'RESPONDER_USER_ID',
3105 avalue => l_responder_id);
3106 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3107 itemkey => itemkey,
3108 aname => 'RESPONDER_RESP_ID',
3109 avalue => l_preparer_resp_id);
3110 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
3111 itemkey => itemkey,
3112 aname => 'RESPONDER_APPL_ID',
3113 avalue => l_preparer_appl_id);
3114 if( l_is_ame_approval = 'Y' ) then
3115 l_progress := '05D notif setting for parent wf RESPONDER_USER_ID l_responder_id:'|| l_responder_id;
3116 IF (g_po_wf_debug = 'Y') THEN
3117 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
3118 END IF;
3119
3120
3121 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3122 itemkey => p_itemkey,
3123 aname => 'RESPONDER_USER_ID',
3124 avalue => l_responder_id);
3125 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3126 itemkey => p_itemkey,
3127 aname => 'RESPONDER_RESP_ID',
3128 avalue => l_preparer_resp_id);
3129 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>p_itemtype,
3130 itemkey => p_itemkey,
3131 aname => 'RESPONDER_APPL_ID',
3132 avalue => l_preparer_appl_id);
3133
3134 end if;
3135 end if;
3136 end if;
3137 end if;
3138 end if;
3139
3140 -- context setting revamp <end>
3141
3142
3143
3144
3145 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
3146 return;
3147 end if;
3148
3149 -- Don't allow transfer
3150 if (funcmode = 'TRANSFER') then
3151 fnd_message.set_name('PO', 'PO_WF_NOTIF_NO_TRANSFER');
3152 app_exception.raise_exception;
3153 resultout := wf_engine.eng_completed;
3154 return;
3155 end if; -- end if for funcmode = 'TRANSFER'
3156
3157 exception
3158 when others then
3159 raise;
3160
3161 end post_approval_notif;
3162
3163
3164 PROCEDURE set_doc_mgr_context (itemtype VARCHAR2, itemkey VARCHAR2) is
3165
3166 l_user_id number;
3167 l_responsibility_id number;
3168 l_application_id number;
3169
3170 l_progress varchar2(200);
3171
3172 BEGIN
3173
3174 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3175 itemkey => itemkey,
3176 aname => 'USER_ID');
3177 --
3178 l_application_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3179 itemkey => itemkey,
3180 aname => 'APPLICATION_ID');
3181 --
3182 l_responsibility_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
3183 itemkey => itemkey,
3184 aname => 'RESPONSIBILITY_ID');
3185
3186 /* Set the context for the doc manager */
3187 -- Bug 4290541, replaced apps init call with set doc mgr context
3188
3189 PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(itemtype, itemkey);
3190
3191 l_progress := 'set_doc_mgr_context. USER_ID= ' || to_char(l_user_id)
3192 || ' APPLICATION_ID= ' || to_char(l_application_id) ||
3193 'RESPONSIBILITY_ID= ' || to_char(l_responsibility_id);
3194
3195 EXCEPTION
3196
3197 WHEN OTHERS THEN
3198 wf_core.context('PO_WF_REQ_NOTIFICATION','set_doc_mgr_context',l_progress);
3199 raise;
3200
3201 END set_doc_mgr_context;
3202
3203 /* Bug# 2616355: kagarwal
3204 function get_document_subtype_display (l_subtype_code in varchar2)
3205 return varchar2 is
3206
3207 l_doc_subtype_disp varchar2(80);
3208
3209 cursor c_doc_subtype(p_doc_subtype varchar2) is
3210 select DISPLAYED_FIELD
3211 from po_lookup_codes
3212 where lookup_type='REQUISITION TYPE'
3213 and lookup_code = p_doc_subtype;
3214
3215 begin
3216
3217 OPEN c_doc_subtype(l_subtype_code);
3218 FETCH c_doc_subtype into l_doc_subtype_disp;
3219 CLOSE c_doc_subtype;
3220
3221 return l_doc_subtype_disp;
3222
3223 end;
3224 */
3225
3226 /* Bug# 2616355: kagarwal
3227 function get_document_type_display (l_type_code in varchar2)
3228 return varchar2 is
3229
3230 l_doc_type_disp varchar2(80);
3231
3232 cursor c_doc_type(p_doc_type varchar2) is
3233 select DISPLAYED_FIELD
3234 from po_lookup_codes
3235 where lookup_type='DOCUMENT TYPE'
3236 and lookup_code = p_doc_type;
3237
3238 begin
3239
3240 OPEN c_doc_type(l_type_code);
3241 FETCH c_doc_type into l_doc_type_disp;
3242 CLOSE c_doc_type;
3243
3244 return l_doc_type_disp;
3245
3246 end;
3247 */
3248
3249 function is_po_approval_type(p_itemtype in varchar2, p_itemkey in varchar2)
3250 return boolean is
3251
3252 l_authority_type VARCHAR2(30);
3253
3254 BEGIN
3255
3256 l_authority_type := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => p_itemtype,
3257 itemkey => p_itemkey,
3258 aname => 'AME_AUTHORITY_TYPE');
3259 if(l_authority_type is null) then
3260 return true;
3261 end if;
3262
3263 return false;
3264
3265 EXCEPTION
3266 WHEN OTHERS THEN
3267 RETURN TRUE;
3268 END;
3269
3270 /* Bug# 2469882
3271 ** Desc: Added new private procedure to set doc subtype display according to the default language of approver or preparer.
3272 This is a workaround suggested by workflow team to support translatable token within msg subject, while avoid fixed language issue within subject.
3273 */
3274
3275 /* Bug# 2616355: kagarwal
3276 ** Desc: Set doc type display according to the default language of the user
3277 **
3278 ** The username is a mandatory IN parameter.
3279 */
3280
3281 procedure GetDisplayValue(itemtype in varchar2,
3282 itemkey in varchar2,
3283 username in varchar2) IS
3284
3285 l_progress VARCHAR2(400) := '000';
3286 l_doc_subtype varchar2(25);
3287 l_doc_disp varchar2(240);
3288
3289 l_display_name varchar2(240);
3290 l_email_address varchar2(240);
3291 l_notification_preference varchar2(240);
3292 l_language varchar2(240);
3293 l_territory varchar2(240);
3294
3295 -- MIPR Changes
3296 l_doc_id NUMBER;
3297 l_mipr_type_disp VARCHAR2(100);
3298
3299 /* Bug# 2616355: kagarwal
3300 ** Desc: We will get the document type display value from
3301 ** po document types tl table.
3302 */
3303
3304 cursor c_lookup_value(p_doc_subtype varchar2, p_language varchar2) is
3305 select type_name
3306 from po_document_types_tl tl, FND_LANGUAGES fl
3307 where fl.nls_language = p_language
3308 and tl.LANGUAGE = fl.language_code
3309 and tl.document_type_code = 'REQUISITION'
3310 and tl.document_subtype = p_doc_subtype;
3311
3312 /*
3313 cursor c_lookup_value(p_doc_subtype varchar2, p_language varchar2) is
3314 select MEANING
3315 from FND_LOOKUP_VALUES flv, FND_LANGUAGES fl
3316 where
3317 fl.nls_language = p_language
3318 and flv.LANGUAGE = fl.language_code
3319 and flv.lookup_type='REQUISITION TYPE'
3320 and flv.lookup_code = p_doc_subtype
3321 and VIEW_APPLICATION_ID = 201
3322 and SECURITY_GROUP_ID = fnd_global.lookup_security_group('REQUISITION TYPE',201);
3323 */
3324
3325 -- MIPR Changes
3326 CURSOR mipr_csr(p_doc_id NUMBER, p_language varchar2) IS
3327 SELECT flv.MEANING
3328 FROM po_requisition_headers_all prh, FND_LOOKUP_VALUES FLV, FND_LANGUAGES FL
3329 WHERE prh.requisition_header_id = p_doc_id
3330 AND prh.clm_mipr_type = flv.LOOKUP_CODE
3331 AND NVL(cancel_flag,'N') = 'N'
3332 AND FL.NLS_LANGUAGE = p_language
3333 AND FLV.LANGUAGE = FL.LANGUAGE_CODE
3334 AND FLV.LOOKUP_TYPE='MIPR_TYPE'
3335 AND VIEW_APPLICATION_ID = 201;
3336
3337 BEGIN
3338 l_progress := 'GetDisplayValue: 001, user name: ' || username;
3339 IF (g_po_wf_debug = 'Y') THEN
3340 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3341 END IF;
3342
3343 l_doc_subtype := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3344 itemkey => itemkey,
3345 aname => 'DOCUMENT_SUBTYPE');
3346
3347 Wf_Directory.GetRoleInfo(
3348 username,
3349 l_display_name,
3350 l_email_address,
3351 l_notification_preference,
3352 l_language,
3353 l_territory);
3354
3355 l_progress := 'GetDisplayValue: 002, language: ' || l_language;
3356 IF (g_po_wf_debug = 'Y') THEN
3357 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3358 END IF;
3359
3360 -- MIPR changes
3361 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype, itemkey => itemkey, aname => 'DOCUMENT_ID');
3362
3363 OPEN mipr_csr(l_doc_id, l_language);
3364 FETCH mipr_csr INTO l_mipr_type_disp;
3365 CLOSE mipr_csr;
3366
3367 IF l_mipr_type_disp IS NOT NULL THEN
3368 l_doc_disp := l_mipr_type_disp;
3369 ELSE
3370 OPEN c_lookup_value(l_doc_subtype, l_language);
3371 FETCH c_lookup_value into l_doc_disp;
3372 CLOSE c_lookup_value;
3373 END IF;
3374
3375 l_progress := 'GetDisplayValue: 003, subtype disp: ' || l_doc_disp;
3376 IF (g_po_wf_debug = 'Y') THEN
3377 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3378 END IF;
3379
3380 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
3381 itemkey => itemkey,
3382 aname => 'DOCUMENT_TYPE_DISP',
3383 avalue => l_doc_disp);
3384 EXCEPTION
3385 WHEN OTHERS THEN
3386 l_progress := 'GetDisplayValue: sql err: ' || sqlerrm;
3387 IF (g_po_wf_debug = 'Y') THEN
3388 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3389 END IF;
3390 IF (c_lookup_value%ISOPEN) THEN
3391 CLOSE c_lookup_value;
3392 END IF;
3393
3394 END GetDisplayValue;
3395
3396 /* Bug# 2469882
3397 ** Desc: Added new procedure to set notification subject token.
3398 */
3399 procedure Get_req_approver_msg_attribute(itemtype in varchar2,
3400 itemkey in varchar2,
3401 actid in number,
3402 funcmode in varchar2,
3403 resultout out NOCOPY varchar2) IS
3404
3405 l_progress VARCHAR2(100) := '000';
3406 l_doc_string varchar2(200);
3407 l_approver_user_name varchar2(100);
3408 l_preparer_user_name varchar2(100);
3409 l_orgid number;
3410
3411 BEGIN
3412
3413 -- Do nothing in cancel or timeout mode
3414 --
3415 if (funcmode <> wf_engine.eng_run) then
3416
3417 resultout := wf_engine.eng_null;
3418 return;
3419
3420 end if;
3421
3422 l_progress := 'Get_req_approver_msg_attribute: 001';
3423 IF (g_po_wf_debug = 'Y') THEN
3424 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3425 END IF;
3426
3427 l_approver_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3428 itemkey => itemkey,
3429 aname => 'APPROVER_USER_NAME');
3430 /* Bug# 2616355: kagarwal
3431 ** Desc Need to set the org context
3432 */
3433
3434 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3435 itemkey => itemkey,
3436 aname => 'ORG_ID');
3437
3438 IF l_orgid is NOT NULL THEN
3439 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
3440 END IF;
3441
3442 GetDisplayValue(itemtype, itemkey, l_approver_user_name);
3443
3444 l_progress := 'Get_req_approver_msg_attribute: 002';
3445 IF (g_po_wf_debug = 'Y') THEN
3446 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3447 END IF;
3448
3449 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3450
3451
3452 EXCEPTION
3453 WHEN OTHERS THEN
3454 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3455 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
3456 wf_core.context('PO_WF_REQ_NOTIFICATION','Get_req_approval_msg_attribute',l_progress);
3457 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');
3458 raise;
3459
3460 END Get_req_approver_msg_attribute;
3461
3462 procedure Get_req_preparer_msg_attribute(itemtype in varchar2,
3463 itemkey in varchar2,
3464 actid in number,
3465 funcmode in varchar2,
3466 resultout out NOCOPY varchar2) IS
3467
3468 l_progress VARCHAR2(100) := '000';
3469 l_doc_string varchar2(200);
3470 l_preparer_user_name varchar2(100);
3471 l_orgid number;
3472
3473 BEGIN
3474
3475 -- Do nothing in cancel or timeout mode
3476 --
3477 if (funcmode <> wf_engine.eng_run) then
3478
3479 resultout := wf_engine.eng_null;
3480 return;
3481
3482 end if;
3483
3484 l_progress := 'Get_req_preparer_msg_attribute: 001';
3485 IF (g_po_wf_debug = 'Y') THEN
3486 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3487 END IF;
3488
3489 l_preparer_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3490 itemkey => itemkey,
3491 aname => 'PREPARER_USER_NAME');
3492
3493 /* Bug# 2616355: kagarwal
3494 ** Desc Need to set the org context
3495 */
3496
3497 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3498 itemkey => itemkey,
3499 aname => 'ORG_ID');
3500
3501 IF l_orgid is NOT NULL THEN
3502 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
3503 END IF;
3504
3505 GetDisplayValue(itemtype, itemkey, l_preparer_user_name);
3506
3507 l_progress := 'Get_req_preparer_msg_attribute: 002';
3508 IF (g_po_wf_debug = 'Y') THEN
3509 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3510 END IF;
3511
3512 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3513
3514
3515 EXCEPTION
3516 WHEN OTHERS THEN
3517 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3518 wf_core.context('PO_WF_REQ_NOTIFICATION','Get_req_preparer_msg_attribute',l_progress);
3519 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');
3520 raise;
3521
3522 END Get_req_preparer_msg_attribute;
3523
3524 /* Procedure to check whether Forward Action is allowed. */
3525
3526 procedure Is_Forward_Action_Allowed(itemtype in varchar2,
3527 itemkey in varchar2,
3528 actid in number,
3529 funcmode in varchar2,
3530 resultout out NOCOPY varchar2 ) is
3531
3532 l_allowed VARCHAR2(1) := 'Y';
3533
3534 begin
3535
3536 FND_PROFILE.GET('PO_ALLOW_REQ_APPRV_FORWARD', l_allowed);
3537
3538 resultout := wf_engine.eng_completed || ':' || l_allowed;
3539
3540 exception
3541 when others then
3542 resultout := wf_engine.eng_completed || ':' || 'Y';
3543
3544 end Is_Forward_Action_Allowed;
3545
3546 /* Bug# 2616255: kagarwal
3547 ** Desc: Added new procedure to set notification subject token
3548 ** for the notifications sent to forward from person
3549 */
3550 procedure Get_req_fwdfrom_msg_attribute(itemtype in varchar2,
3551 itemkey in varchar2,
3552 actid in number,
3553 funcmode in varchar2,
3554 resultout out NOCOPY varchar2) IS
3555
3556 l_progress VARCHAR2(100) := '000';
3557 l_doc_string varchar2(200);
3558 l_fwdfrom_user_name varchar2(100);
3559 l_preparer_user_name varchar2(100);
3560 l_orgid number;
3561
3562 BEGIN
3563
3564 -- Do nothing in cancel or timeout mode
3565 --
3566 if (funcmode <> wf_engine.eng_run) then
3567
3568 resultout := wf_engine.eng_null;
3569 return;
3570
3571 end if;
3572
3573 l_progress := 'Get_req_fwdfrom_msg_attribute: 001';
3574 IF (g_po_wf_debug = 'Y') THEN
3575 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3576 END IF;
3577
3578 l_fwdfrom_user_name := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3579 itemkey => itemkey,
3580 aname => 'FORWARD_FROM_USER_NAME');
3581
3582 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3583 itemkey => itemkey,
3584 aname => 'ORG_ID');
3585
3586 IF l_orgid is NOT NULL THEN
3587 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
3588 END IF;
3589
3590 GetDisplayValue(itemtype, itemkey, l_fwdfrom_user_name);
3591
3592 l_progress := 'Get_req_fwdfrom_msg_attribute: 002';
3593 IF (g_po_wf_debug = 'Y') THEN
3594 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3595 END IF;
3596
3597 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3598
3599 EXCEPTION
3600 WHEN OTHERS THEN
3601 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3602 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
3603 wf_core.context('PO_WF_REQ_NOTIFICATION','Get_req_fwdfrom_msg_attribute',l_progress);
3604 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
3605 l_doc_string, sqlerrm, 'PO_WF_REQ_NOTIFICATION.GET_REQ_FWDFROM_MSG_ATTRIBUTE');
3606 raise;
3607
3608 END Get_req_fwdfrom_msg_attribute;
3609
3610 /* Bug 2480327
3611 ** notification UI enhancement
3612 */
3613
3614 function is_foreign_currency_displayed (p_document_id in number, p_func_currency_code in varchar2) return boolean IS
3615
3616 l_max_lines number := 0;
3617 l_currency_code po_requisition_lines.currency_code%TYPE;
3618
3619 begin
3620 l_max_lines := to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
3621
3622 -- SQL What: checking for any requisition line that has foreign currency
3623 -- SQL Why: need to check if need to display foregin currency column
3624 select currency_code into l_currency_code from
3625 (select currency_code from
3626 (SELECT currency_code
3627 FROM po_requisition_lines
3628 WHERE requisition_header_id = p_document_id
3629 AND NVL(cancel_flag,'N') = 'N'
3630 AND NVL(modified_by_agent_flag, 'N') = 'N'
3631 order by line_num) a
3632 where rownum <= l_max_lines ) b
3633 where b.currency_code <> p_func_currency_code;
3634 return true;
3635 exception
3636 when no_data_found then
3637 return false;
3638 when too_many_rows then
3639 return true;
3640 when others then
3641 return false;
3642 end;
3643
3644 /* Bug 2480327
3645 ** notification UI enhancement
3646 */
3647
3648 procedure get_item_info(document_id in varchar2,
3649 itemtype out nocopy varchar2,
3650 itemkey out nocopy varchar2,
3651 nid out nocopy number) is
3652
3653 firstcolon pls_integer;
3654 secondcolon pls_integer;
3655
3656 begin
3657
3658 /* format like REQAPPRV:12719-23684:67694*/
3659 firstcolon := instr(document_id, ':', 1,1);
3660 secondcolon := instr(document_id, ':', 1,2);
3661
3662 itemtype := substr(document_id, 1, firstcolon - 1);
3663
3664 if (secondcolon = 0) then
3665 itemkey := substr(document_id, firstcolon + 1,
3666 length(document_id) - 2);
3667 nid := null;
3668 else
3669 itemkey := substr(document_id, firstcolon + 1, secondcolon - firstcolon - 1);
3670 begin
3671 nid := to_number(substr(document_id, secondcolon+1,
3672 length(document_id) - secondcolon));
3673 exception
3674 when others then nid := null;
3675 end;
3676 end if;
3677
3678 IF (g_po_wf_debug = 'Y') THEN
3679 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'PO_WF_REQ_NOTIFICATION.get_item_info nid='||nid);
3680 END IF;
3681
3682 end;
3683
3684 -- Bug 3419861
3685 -- Added the new function to format the currency.
3686
3687 Function FORMAT_CURRENCY_NO_PRECESION(p_currency_code IN varchar2,
3688 p_amount IN number) return varchar2 is
3689 l_precision number := 0;
3690 l_precision_amt number := 0;
3691 l_ext_precision number := 0;
3692 l_min_acct_unit number := 0;
3693 l_field_length number := 80;
3694 l_mask varchar2(100);
3695 l_amount number;
3696
3697
3698 begin
3699 -- Get the Currency info
3700 fnd_currency.get_info(p_currency_code, l_precision,
3701 l_ext_precision, l_min_acct_unit);
3702
3703 -- Find the field width
3704 -- Bug#8373802 - Round p_amount to 15 precision as unit_price and currency_unit_price
3705 -- values are not rounded in the Requisition created from iProcurement. It is temporary fix
3706 -- to take care of formatting price columns display in PL/SQL notification in 11.5.10.
3707
3708 l_amount := round(p_amount, 15);
3709 l_field_length := length(l_amount) + 25;
3710
3711 -- l_precision_amt := length(l_amount) - length(round(l_amount,0)) - 1;
3712 -- bug 9745418 : Length of number having only decimal does not consider leading 0, hence while calculating precision
3713 -- dont sunbtract the length of digit before decimal. Also for numbers like 9.999, after rounding it upto 0 precision,
3714 -- rounds off the number to proper digits (10.00) giving wrong precision further. Using floor insteda of round(number,0)
3715 -- resolves the issue.
3716
3717
3718 if floor(l_amount) > 0 then
3719 l_precision_amt := length(l_amount) - length(floor(l_amount)) - 1;
3720 else
3721 l_precision_amt := length(l_amount) - 1;
3722 end if;
3723
3724 if l_precision_amt > l_precision then
3725 l_precision := l_precision_amt;
3726 end if;
3727
3728
3729 -- Build custom format mask
3730 fnd_currency.build_format_mask(l_mask, l_field_length,
3731 l_precision, l_min_acct_unit);
3732
3733 -- Convert the Amount
3734 return to_char(l_amount,l_mask);
3735
3736 end FORMAT_CURRENCY_NO_PRECESION;
3737
3738 END PO_WF_REQ_NOTIFICATION;