[Home] [Help]
PACKAGE BODY: APPS.PO_WF_PO_NOTIFICATION
Source
1 PACKAGE BODY PO_WF_PO_NOTIFICATION AS
2 /* $Header: POXWPA7B.pls 120.9.12010000.2 2008/08/04 08:36:10 rramasam ship $ */
3
4 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
5 g_pkg_name VARCHAR2(30) := 'PO_WF_PO_NOTIFICATION'; -- <BUG 3607009>
6
7 PROCEDURE get_po_approve_msg ( document_id in varchar2,
8 display_type in varchar2,
9 document in out NOCOPY varchar2,
10 document_type in out NOCOPY varchar2) IS
11
12 l_item_type wf_items.item_type%TYPE;
13 l_item_key wf_items.item_key%TYPE;
14
15 l_document_id po_headers.po_header_id%TYPE;
16 l_org_id po_headers.org_id%TYPE;
17 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
18 l_header_msg VARCHAR2(500);
19 l_po_amount VARCHAR2(30);
20 l_tax_amount VARCHAR2(30);
21 l_description po_headers.comments%TYPE;
22 l_forwarded_from per_all_people_f.full_name%TYPE;
23 l_preparer per_all_people_f.full_name%TYPE;
24 --<UTF-8 FPI START>
25 -- l_note VARCHAR2(480); /* < UTF8 FPI - changed from VARCHAR2(240) > */
26 l_note po_action_history.note%TYPE;
27 --<UTF-8 FPI END>
28 l_document VARCHAR2(32000) := '';
29 l_tax_amt NUMBER;
30
31 /* Start Bug# 3972475 */
32 X_precision number;
33 X_ext_precision number;
34 X_min_acct_unit number;
35 /* End Bug# 3972475*/
36 l_supplier po_vendors.vendor_name%type; --Bug 4254468
37 l_supplier_site po_vendor_sites_all.vendor_site_code%type; --Bug 4254468
38 NL VARCHAR2(1) := fnd_global.newline;
39
40 BEGIN
41
42 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
43 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
44
45 l_document_id := wf_engine.GetItemAttrNumber
46 (itemtype => l_item_type,
47 itemkey => l_item_key,
48 aname => 'DOCUMENT_ID');
49
50 l_org_id := wf_engine.GetItemAttrNumber
51 (itemtype => l_item_type,
52 itemkey => l_item_key,
53 aname => 'ORG_ID');
54
55 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
56
57
58 l_currency_code := wf_engine.GetItemAttrText
59 (itemtype => l_item_type,
60 itemkey => l_item_key,
61 aname => 'FUNCTIONAL_CURRENCY');
62
63 l_po_amount := wf_engine.GetItemAttrText
64 (itemtype => l_item_type,
65 itemkey => l_item_key,
66 aname => 'PO_AMOUNT_DSP');
67
68 l_tax_amount := wf_engine.GetItemAttrText
69 (itemtype => l_item_type,
70 itemkey => l_item_key,
71 aname => 'TAX_AMOUNT_DSP');
72
73 l_description := wf_engine.GetItemAttrText
74 (itemtype => l_item_type,
75 itemkey => l_item_key,
76 aname => 'PO_DESCRIPTION');
77
78 l_forwarded_from := wf_engine.GetItemAttrText
79 (itemtype => l_item_type,
80 itemkey => l_item_key,
81 aname => 'FORWARD_FROM_DISP_NAME');
82
83 l_preparer := wf_engine.GetItemAttrText
84 (itemtype => l_item_type,
85 itemkey => l_item_key,
86 aname => 'PREPARER_DISPLAY_NAME');
87
88 l_note := wf_engine.GetItemAttrText
89 (itemtype => l_item_type,
90 itemkey => l_item_key,
91 aname => 'NOTE');
92
93 --<Bug 4254468 Start> Show supplier and supplier site for
94 -- approval notifications
95 l_supplier := PO_WF_UTIL_PKG.GetItemAttrText
96 (itemtype => l_item_type,
97 itemkey => l_item_key,
98 aname => 'SUPPLIER');
99
100 l_supplier_site := PO_WF_UTIL_PKG.GetItemAttrText
101 (itemtype => l_item_type,
102 itemkey => l_item_key,
103 aname => 'SUPPLIER_SITE');
104 --<Bug 4254468 End>
105
106 /*Start Bug# 3972475 - replaced the below sql to get the tax amount
107 to account for canceled QTY. Also accounted for new order types introduced
108 in 11i10 that use amount instead of quantity (where quantity_ordered is null).
109
110 Since we are performing divide and multiply by operations we need rounding
111 logic based on the currency.
112
113 If we are using minimum accountable unit we apply:
114 rounded tax = round(tax/mau)*mau, otherwise
115 rounded tax = round(tax, precision)
116
117 Old tax select:
118 SELECT nvl(sum(nonrecoverable_tax), 0)
119 INTO l_tax_amt
120 FROM po_lines pol,
121 po_distributions pod
122 WHERE pol.po_header_id = l_document_id
123 AND pod.po_line_id = pol.po_line_id;
124 */
125
126 fnd_currency.get_info( l_currency_code,
127 X_precision,
128 X_ext_precision,
129 X_min_acct_unit);
130
131 IF (x_min_acct_unit IS NOT NULL) AND
132 (x_min_acct_unit <> 0)
133 THEN
134 SELECT sum( round (POD.nonrecoverable_tax *
135 decode(quantity_ordered,
136 NULL,
137 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / nvl(POD.amount_ordered, 1),
138 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / nvl(POD.quantity_ordered, 1)
139 ) / X_min_acct_unit
140 ) * X_min_acct_unit
141 )
142 INTO l_tax_amt
143 FROM po_lines pol,
144 po_distributions pod
145 WHERE pol.po_header_id = l_document_id
146 AND pod.po_line_id = pol.po_line_id;
147 ELSE
148 SELECT sum( round (POD.nonrecoverable_tax *
149 decode(quantity_ordered,
150 NULL,
151 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / nvl(POD.amount_ordered, 1),
152 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / nvl(POD.quantity_ordered, 1)
153 ),
154 X_precision
155 )
156 )
157 INTO l_tax_amt
158 FROM po_lines pol,
159 po_distributions pod
160 WHERE pol.po_header_id = l_document_id
161 AND pod.po_line_id = pol.po_line_id;
162 END IF;
163
164 if (display_type = 'text/html') then
165
166 l_document := NL || NL || '<!-- PO_APPROVE_MSG -->'|| NL || NL || '<P>';
167
168 l_document := l_document || '</P>' || NL;
169
170 l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0 SUMMARY=""><TR><TD align=right >' || NL ||
171 fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_AMOUNT') ||
172 ' </TD>' || NL;
173
174 l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_po_amount || '</TD></TR>' || NL;
175
176 if l_tax_amt > 0 then
177
178 l_document := l_document || '<TR><TD align=right>' ||
179 fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') ||
180 ' </TD>' || NL;
181 l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || l_tax_amount ||
182 '</TD></TR></TABLE></P>' || NL;
183
184 else
185
186 l_document := l_document || '</TABLE></P>' || NL || NL;
187
188 end if;
189
190 --<Bug 4254468 Start> Show supplier and supplier site for
191 -- approval notifications
192 l_document := l_document || '<P>' || NL;
193 l_document := l_document || fnd_message.get_string('PO', 'PO_FO_VENDOR') ||
194 ' '|| l_supplier || NL;
195 l_document := l_document || '<BR>' || NL;
196 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER_SITE') ||
197 ' '|| l_supplier_site || NL;
198 l_document := l_document || '</P>' || NL;
199 --<Bug 4254468 End>
200
201 if l_description is not null then
202 l_document := l_document || '<P>' || NL;
203 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
204 l_document := l_document || l_description;
205 l_document := l_document || '<BR></P>' || NL;
206 end if;
207
208 else -- plain text notification is defined in the WF.
209
210 null;
211
212 end if;
213
214 document := l_document;
215
216 END;
217
218
219 PROCEDURE get_po_lines_details ( document_id in varchar2,
220 display_type in varchar2,
221 document in out NOCOPY CLOB, -- <BUG 7006113>
222 document_type in out NOCOPY varchar2) IS
223
224 l_item_type wf_items.item_type%TYPE;
225 l_item_key wf_items.item_key%TYPE;
226
227 l_document_id po_lines.po_header_id%TYPE;
228 l_org_id po_lines.org_id%TYPE;
229 l_document_type VARCHAR2(25);
230
231 l_document VARCHAR2(32000) := '';
232
233 l_currency_code fnd_currencies.currency_code%TYPE;
234
235 -- Bug 3668188: added new local var. note: the length of this
236 -- varchar was determined based on the length in POXWPA1B.pls,
237 -- which is the other place 'OPEN_FORM_COMMAND' attribute is used
238
239 l_open_form_command VARCHAR2(200);
240 l_view_po_url varchar2(1000); -- HTML Orders R12
241 l_edit_po_url varchar2(1000); -- HTML Orders R12
242
243 NL VARCHAR2(1) := fnd_global.newline;
244
245 i NUMBER := 0;
246 max_lines_dsp NUMBER ; -- <BUG 7006113>
247 l_line_count NUMBER := 0; -- <BUG 3616816> # lines/shipments on document
248 line_mesg fnd_new_messages.message_text%TYPE; --Bug 4695601
249 l_num_records_to_display NUMBER; -- <BUG 3616816> actual # of records to be displayed in table
250
251 -- <BUG 7006113 START>
252 -- curr_len NUMBER := 0;
253 -- prior_len NUMBER := 0;
254 -- <BUG 7006113 END>
255
256 -- po lines cursor
257
258 -- <BUG 3616816 START> Declare TABLEs for each column that is selected
259 -- from po_line_csr and po_line_loc_csr.
260 --
261 TYPE line_num_tbl_type IS TABLE OF PO_LINES.line_num%TYPE;
262 TYPE shipment_num_tbl_type IS TABLE OF PO_LINE_LOCATIONS.shipment_num%TYPE;
263 TYPE item_num_tbl_type IS TABLE OF MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
264 TYPE item_revision_tbl_type IS TABLE OF PO_LINES.item_revision%TYPE;
265 TYPE item_desc_tbl_type IS TABLE OF PO_LINES.item_description%TYPE;
266 TYPE uom_tbl_type IS TABLE OF MTL_UNITS_OF_MEASURE.unit_of_measure_tl%TYPE;
267 TYPE quantity_tbl_type IS TABLE OF PO_LINES.quantity%TYPE;
268 TYPE unit_price_tbl_type IS TABLE OF PO_LINES.unit_price%TYPE;
269 TYPE amount_tbl_type IS TABLE OF PO_LINES.amount%TYPE;
270 TYPE location_tbl_type IS TABLE OF HR_LOCATIONS.location_code%TYPE;
271 TYPE organization_name_tbl_type IS TABLE OF ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE;
272 TYPE need_by_date_tbl_type IS TABLE OF PO_LINE_LOCATIONS.need_by_date%TYPE;
273 TYPE promised_date_tbl_type IS TABLE OF PO_LINE_LOCATIONS.promised_date%TYPE;
274 TYPE shipment_type_tbl_type IS TABLE OF PO_LINE_LOCATIONS.shipment_type%TYPE;
275
276 l_line_num_tbl line_num_tbl_type;
277 l_shipment_num_tbl shipment_num_tbl_type;
278 l_item_num_tbl item_num_tbl_type;
279 l_item_revision_tbl item_revision_tbl_type;
280 l_item_desc_tbl item_desc_tbl_type;
281 l_uom_tbl uom_tbl_type;
282 l_quantity_tbl quantity_tbl_type;
283 l_unit_price_tbl unit_price_tbl_type;
284 l_amount_tbl amount_tbl_type;
285 l_location_tbl location_tbl_type;
286 l_org_name_tbl organization_name_tbl_type;
287 l_need_by_date_tbl need_by_date_tbl_type;
288 l_promised_date_tbl promised_date_tbl_type;
289 l_shipment_type_tbl shipment_type_tbl_type;
290 --
291 -- <BUG 3616816 END>
292
293 /* Bug# 1419139: kagarwal
294 ** Desc: The where clause pol.org_id = msi.organization_id(+) in the
295 ** PO lines cursor, po_line_csr, is not correct as the pol.org_id
296 ** is the operating unit which is not the same as the inventory
297 ** organization_id.
298 **
299 ** We need to use the financials_system_parameter table for the
300 ** inventory organization_id.
301 **
302 ** Also did the similar changes for the Release cursor,po_line_loc_csr.
303 */
304
305 /* Bug 2401933: sktiwari
306 Modifying cursor po_line_csr to return the translated UOM value
307 instead of unit_meas_lookup_code.
308 */
309
310 CURSOR po_line_csr(v_document_id NUMBER) IS
311 SELECT pol.line_num,
312 msi.concatenated_segments,
313 pol.item_revision,
314 pol.item_description,
315 -- pol.unit_meas_lookup_code, -- bug 2401933.remove
316 nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code), -- bug 2401933.add
317 pol.quantity,
318 pol.unit_price,
319 nvl(pol.amount, pol.quantity * pol.unit_price)
320 FROM po_lines pol,
321 mtl_system_items_kfv msi,
322 mtl_units_of_measure muom, -- bug 2401933.add
323 financials_system_parameters fsp
324 WHERE pol.po_header_id = v_document_id
325 AND pol.item_id = msi.inventory_item_id(+)
326 AND NVL(msi.organization_id, fsp.inventory_organization_id) =
327 fsp.inventory_organization_id
328 /* Bug 2299484 fixed. prevented the canceled lines to be displayed
329 in notifications.
330 */
331 AND NVL(pol.cancel_flag,'N') = 'N'
332 AND muom.unit_of_measure (+) = pol.unit_meas_lookup_code -- bug 2401933.add
333 ORDER BY pol.line_num;
334
335 -- release shipments cursor
336
337 /* Bug# 1530303: kagarwal
338 ** Desc: We need to change the where clause as the item
339 ** may not be an inventory item. For this case we should
340 ** have an outer join with the mtl_system_items_kfv.
341 **
342 ** Changed the condition:
343 ** pol.item_id = msi.inventory_item_id
344 ** to pol.item_id = msi.inventory_item_id(+)
345 **
346 */
347
348 /* Bug# 1718725: kagarwal
349 ** Desc: The unit of measure may be null at the shipment level
350 ** hence in this case we need to get the uom from line level.
351 **
352 ** Changed nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
353 */
354 /* Bug# 1770951: kagarwal
355 ** Desc: For Releases we should consider the price_override on the shipments
356 ** and not the price on the Blanket PO line as the shipment price could be
357 ** different if the price override is enabled on the Blanket.
358 */
359
360 /* Bug 2401933: sktiwari
361 Modifying cursor po_line_loc_csr to return the translated UOM value
362 instead of unit_meas_lookup_code.
363 */
364
365 CURSOR po_line_loc_csr(v_document_id NUMBER) IS
366 SELECT pll.shipment_num,
367 msi.concatenated_segments,
368 pol.item_revision,
369 pol.item_description,
370 -- Bug 2401933.start
371 -- nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
372 -- unit_meas_lookup_code,
373 nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code),
374 -- Bug 2401933.end
375 pll.quantity,
376 nvl(pll.price_override, pol.unit_price) unit_price,
377 hrl.location_code,
378 ood.organization_name,
379 pll.need_by_date,
380 pll.promised_date,
381 pll.shipment_type,
382 --Bug 4950850 Added pll.amount
383 --Bug 5563024 AMOUNT NOT SHOWN FOR A RELEASE SHIPMENT IN APPROVAL NOTIFICATION.
384 nvl(pll.amount, nvl(pll.price_override, pol.unit_price) * pll.quantity)
385 FROM po_lines pol,
386 po_line_locations pll,
387 mtl_system_items_kfv msi,
388 hr_locations_all hrl,
389 hz_locations hz,
390 org_organization_definitions ood,
391 mtl_units_of_measure muom, -- Bug 2401933.add
392 financials_system_parameters fsp
393 where PLL.PO_RELEASE_ID = v_document_id
394 and PLL.po_line_id = POL.po_line_id
395 and PLL.ship_to_location_id = HRL.location_id (+)
396 and PLL.ship_to_location_id = HZ.location_id (+)
397 and PLL.ship_to_organization_id = OOD.organization_id
398 and pol.item_id = msi.inventory_item_id(+)
399 and NVL(msi.organization_id, fsp.inventory_organization_id) =
400 fsp.inventory_organization_id
401 /* Bug 2299484 fixed. prevented the canceled shipments to be displayed
402 in notifications.
403 */
404 AND NVL(PLL.cancel_flag,'N') = 'N'
405 AND muom.unit_of_measure (+) = pol.unit_meas_lookup_code -- Bug 2401933.add
406 order by Shipment_num asc;
407
408 BEGIN
409
410 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
411 l_item_key := substr(document_id, instr(document_id, ':') + 1,
412 length(document_id) - 2);
413
414 /* Bug# 2353153
415 ** Setting application context
416 */
417
418 PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(l_item_type, l_item_key);
419
420 l_document_id := wf_engine.GetItemAttrNumber (itemtype => l_item_type,
421 itemkey => l_item_key,
422 aname => 'DOCUMENT_ID');
423
424 l_org_id := wf_engine.GetItemAttrNumber (itemtype => l_item_type,
425 itemkey => l_item_key,
426 aname => 'ORG_ID');
427
428 l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
429 itemkey => l_item_key,
430 aname => 'DOCUMENT_TYPE');
431
432 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
433
434
435 /* Bug# 1686066: kagarwal
436 ** Desc: Use the functional currency of the PO for the precision of
437 ** line amounts.
438 */
439
440 l_currency_code := wf_engine.GetItemAttrText
441 (itemtype => l_item_type,
442 itemkey => l_item_key,
443 aname => 'FUNCTIONAL_CURRENCY');
444
445
446 -- Bug 3668188
447 l_open_form_command := PO_WF_UTIL_PKG.GetItemAttrText
448 (itemtype => l_item_type,
449 itemkey => l_item_key,
450 aname => 'OPEN_FORM_COMMAND');
451
452 -- HTML Orders R12
453 -- Get the PO HTML Page URL's
454 l_view_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
455 itemtype => l_item_type,
456 itemkey => l_item_key,
457 aname => 'VIEW_DOC_URL');
458
459 l_edit_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
460 itemtype => l_item_type,
461 itemkey => l_item_key,
462 aname => 'EDIT_DOC_URL');
463
464
465 /* Bug# 2668222: kagarwal
466 ** Desc: Using profile PO_NOTIF_LINES_LIMIT to get the maximum
467 ** number of PO lines to be displayed in Approval notification.
468 ** The same profile is also used for Requisitions.
469 */
470 -- <BUG 7006113 START Moved this code to the later section of the procedure >
471 -- max_lines_dsp:= to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
472
473 -- if max_lines_dsp is NULL then
474 -- max_lines_dsp := 20;
475 -- end if;
476 -- <BUG 7006113 END>
477
478 -- <BUG 3616816 START> Fetch Release Shipments/PO Lines data into Tables.
479 --
480 IF ( l_document_type = 'RELEASE' ) THEN
481
482 OPEN po_line_loc_csr(l_document_id);
483
484 FETCH po_line_loc_csr BULK COLLECT INTO l_shipment_num_tbl
485 , l_item_num_tbl
486 , l_item_revision_tbl
487 , l_item_desc_tbl
488 , l_uom_tbl
489 , l_quantity_tbl
490 , l_unit_price_tbl
491 , l_location_tbl
492 , l_org_name_tbl
493 , l_need_by_date_tbl
494 , l_promised_date_tbl
495 , l_shipment_type_tbl
496 , l_amount_tbl; --bug 4950850
497
498 l_line_count := po_line_loc_csr%ROWCOUNT; -- Get # of records fetched.
499
500 CLOSE po_line_loc_csr;
501
502 ELSE
503
504 OPEN po_line_csr(l_document_id);
505
506 FETCH po_line_csr BULK COLLECT INTO l_line_num_tbl
507 , l_item_num_tbl
508 , l_item_revision_tbl
509 , l_item_desc_tbl
510 , l_uom_tbl
511 , l_quantity_tbl
512 , l_unit_price_tbl
513 , l_amount_tbl;
514
515 l_line_count := po_line_csr%ROWCOUNT; -- Get # of records fetched.
516
517 CLOSE po_line_csr;
518
519 END IF;
520 --
521 -- <BUG 3616816 END>
522
523 max_lines_dsp:= to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
524
525 IF max_lines_dsp IS NULL THEN
526
527 max_lines_dsp := l_line_count;
528
529 END IF;
530
531 -- <BUG 3616816 START> Determine the actual number of records to display
532 -- in the table.
533 --
534 IF ( l_line_count >= max_lines_dsp )
535 THEN
536 l_num_records_to_display := max_lines_dsp;
537 ELSE
538 l_num_records_to_display := l_line_count;
539 END IF;
540 --
541 -- <BUG 3616816 END>
542
543 if (display_type = 'text/html') then
544
545 if (nvl(l_document_type, 'PO') <> 'RELEASE') then
546
547 l_document := NL || NL || '<!-- PO_LINE_DETAILS -->'|| NL || NL || '<P><B>';
548 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_LINE_DETAILS');
549 l_document := l_document || '</B>' || NL || '<P>'; -- <BUG 3616816>
550
551 -- <BUG 3616816 START> Only display message if # of actual lines is
552 -- greater than maximum limit.
553 --
554 IF ( l_line_count > max_lines_dsp ) THEN
555
556 -- Bug 3668188: changed the code check (originally created
557 -- in bug 3607009) that determines which message to show
558 -- based on whether Open Document icon is shown in the notif.
559 -- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
560 -- previous node, using the get_po_user_msg_attribute procedure.
561 --
562 -- HTML Orders R12
563 -- Check for the URL parameters as well
564 IF (l_open_form_command IS NULL ) AND
565 (l_view_po_url IS NULL ) AND
566 (l_edit_po_url IS NULL )
567 THEN
568 -- "The first [COUNT] Purchase Order lines are summarized below."
569 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG_TRUNC');
570 ELSE
571 -- "The first [COUNT] Purchase Order lines are summarized
572 -- below. For information on additional lines, please click
573 -- the Open Document icon."
574 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG');
575 END IF;
576
577 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
578 line_mesg := FND_MESSAGE.get;
579 l_document := l_document || line_mesg || '<P>';
580
581
582 END IF;
583 --
584 -- <BUG 3616816 END>
585
586 l_document := l_document || NL || '<TABLE border=1 cellpadding=2 cellspacing=1 summary="' || fnd_message.get_string('ICX','ICX_POR_TBL_PO_TO_APPROVE_SUM') || '"> '|| NL;
587
588 l_document := l_document || '<TR>' || NL;
589
590 l_document := l_document || '<TH id="lineNum_1">' ||
591 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
592
593 l_document := l_document || '<TH id="itemNum_1">' ||
594 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || '</TH>' || NL;
595
596 l_document := l_document || '<TH id="itemRev_1">' ||
597 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || '</TH>' || NL;
598
599 l_document := l_document || '<TH id="itemDesc_1">' ||
600 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TH>' || NL;
601
602 l_document := l_document || '<TH id="uom_1">' ||
603 fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TH>' || NL;
604
605 l_document := l_document || '<TH id="quant_1">' ||
606 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
607
608 l_document := l_document || '<TH id="unitPrice_1">' ||
609 fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TH>' || NL;
610
611 l_document := l_document || '<TH id="lineAmt_1">' ||
612 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || '</TH>' || NL;
613
614 l_document := l_document || '</TR>' || NL;
615
616 -- curr_len := lengthb(l_document);
617 -- prior_len := curr_len;
618
619 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
620
621 /* Exit the cursor if the current document length and 2 times the
622 ** length added in prior line exceeds 32000 char */
623 -- < BUG 7006113 START Commented the loop to avoid the check so that maximum
624 -- lines can be displayed >
625 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
626 -- exit;
627 -- end if;
628 -- prior_len := curr_len;
629 -- < BUG 7006113 END >
630
631 l_document := l_document || '<TR>' || NL;
632
633 l_document := l_document || '<TD nowrap align=center headers="lineNum_1">'
634 || nvl(to_char(l_line_num_tbl(i)), ' ') || '</TD>' || NL;
635 l_document := l_document || '<TD nowrap headers="itemNum_1">'
636 || nvl(l_item_num_tbl(i), ' ') || '</TD>' || NL;
637 l_document := l_document || '<TD nowrap headers="itemRev_1">'
638 || nvl(l_item_revision_tbl(i), ' ') || '</TD>' || NL;
639 l_document := l_document || '<TD nowrap headers="itemDesc_1">'
640 || nvl(l_item_desc_tbl(i), ' ') || '</TD>' || NL;
641 l_document := l_document || '<TD nowrap headers="uom_1">'
642 || nvl(l_uom_tbl(i), ' ') || '</TD>' || NL;
643 l_document := l_document || '<TD nowrap align=right headers="quant_1">'
644 || nvl(to_char(l_quantity_tbl(i)), ' ') || '</TD>' || NL;
645
646 /* Bug 2868931: kagarwal
647 ** We will not format the unit price on the lines in notifications
648 */
649 -- Bug 3547777. Added the nvl clauses to unit_price and line_
650 -- amount so that box is still displayed even if value is null.
651 l_document := l_document || '<TD nowrap align=right headers="unitPrice_1">' ||
652 nvl(PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_currency_code,l_unit_price_tbl(i)),' ') || '</TD>' || NL; -- <BUG 7006113>
653
654 l_document := l_document || '<TD nowrap align=right headers="lineAmt_1">' ||
655 nvl(TO_CHAR(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(
656 l_currency_code, 30)),' ') || '</TD>' || NL;
657
658 l_document := l_document || '</TR>' || NL;
659
660 -- <BUG 7006113 START>
661 --curr_len := lengthb(l_document);
662
663 wf_notification.writetoclob(document, l_document);
664
665 l_document := NULL;
666
667 EXIT WHEN i = l_num_records_to_display;
668 -- <BUG 7006113 END>
669 end loop;
670
671 else -- release
672
673 l_document := NL || NL || '<!-- RELEASE_SHIPMENT_DETAILS -->'|| NL || NL || '<P><B>';
674 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_DETAILS');
675 l_document := l_document || '</B>' || NL || '<P>';
676
677 -- <BUG 3616816 START> Only display message if # of actual lines is
678 -- greater than maximum limit.
679 --
680 IF ( l_line_count > max_lines_dsp )
681 THEN
682 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_REL_SHIP_MESG');
683 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
684 line_mesg := FND_MESSAGE.get;
685 l_document := l_document || line_mesg || '<P>';
686 END IF;
687 --
688 -- <BUG 3616816 END>
689
690 l_document := l_document || '<TABLE border=1 cellpadding=2 cellspacing=1 summary="' || fnd_message.get_string('ICX','ICX_POR_TBL_BL_TO_APPROVE_SUM') || '"> '|| NL;
691
692 l_document := l_document || '<TR>' || NL;
693
694 l_document := l_document || '<TH id="shipNum_2">' ||
695 fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_NUMBER') || '</TH>' || NL;
696
697 l_document := l_document || '<TH id="itemNum_2">' ||
698 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || '</TH>' || NL;
699
700 l_document := l_document || '<TH id="itemRev_2">' ||
701 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || '</TH>' || NL;
702
703 l_document := l_document || '<TH id="itemDesc_2">' ||
704 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TH>' || NL;
705
706 l_document := l_document || '<TH id="uom_2">' ||
707 fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TH>' || NL;
708
709 l_document := l_document || '<TH id="quant_2">' ||
710 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
711
712 l_document := l_document || '<TH id="unitPrice_2">' ||
713 fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TH>' || NL;
714
715 l_document := l_document || '<TH id="location_2">' ||
716 fnd_message.get_string('PO', 'PO_WF_NOTIF_LOCATION') || '</TH>' || NL;
717
718 l_document := l_document || '<TH id="shipToOrg_2">' ||
719 fnd_message.get_string('PO', 'POA_SHIP_TO_ORG') || '</TH>' || NL;
720
721 l_document := l_document || '<TH id="needByDate_2">' ||
722 fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY_DATE') || '</TH>' || NL;
723 /* bug 4950850 */
724 l_document := l_document || '<TH id="lineAmt_2">' ||
725 fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') || '</TH>' || NL;
726
727 l_document := l_document || '</TR>' || NL;
728
729 -- curr_len := lengthb(l_document);
730 -- prior_len := curr_len;
731
732 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
733
734 /* Exit the cursor if the current document length and 2 times the
735 ** length added in prior line exceeds 32000 char */
736 -- < BUG 7006113 START Commented the loop to avoid the check so that
737 -- maximum lines can be displayed >
738 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
739 -- exit;
740 -- end if;
741 -- prior_len := curr_len;
742 -- < BUG 7006113 END >
743
744 l_document := l_document || '<TR>' || NL;
745
746 l_document := l_document || '<TD nowrap align=center headers="shipNum_2">'
747 || nvl(to_char(l_shipment_num_tbl(i)), ' ') || '</TD>' || NL;
748 l_document := l_document || '<TD nowrap headers="itemNum_2">'
749 || nvl(l_item_num_tbl(i), ' ') || '</TD>' || NL;
750 l_document := l_document || '<TD nowrap headers="itemRev_2">'
751 || nvl(l_item_revision_tbl(i), ' ') || '</TD>' || NL;
752 l_document := l_document || '<TD nowrap headers="itemDesc_2">'
753 || nvl(l_item_desc_tbl(i), ' ') || '</TD>' || NL;
754 l_document := l_document || '<TD nowrap headers="uom_2">'
755 || nvl(l_uom_tbl(i), ' ') || '</TD>' || NL;
756 l_document := l_document || '<TD nowrap align=right headers="quant_2">'
757 || nvl(to_char(l_quantity_tbl(i)), ' ') || '</TD>' || NL;
758
759 /* Bug 2868931: kagarwal
760 ** We will not format the unit price on the lines in notifications
761 */
762
763 l_document := l_document || '<TD nowrap align=right headers="unitPrice_2">' ||
764 nvl(PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(
765 l_currency_code,l_unit_price_tbl(i)),' ') || '</TD>' || NL; -- <BUG 7006113>
766
767 l_document := l_document || '<TD nowrap headers="location_2">'
768 || nvl(l_location_tbl(i), ' ') || '</TD>' || NL;
769 l_document := l_document || '<TD nowrap headers="shipToOrg_2">'
770 || nvl(l_org_name_tbl(i), ' ') || '</TD>' || NL;
771 l_document := l_document || '<TD nowrap headers="needByDate_2">'
772 || to_char(l_need_by_date_tbl(i)) || '</TD>' || NL;
773 /* bug 4950850 */
774 l_document := l_document || '<TD nowrap align=right headers="lineAmt_2">' ||
775 nvl(TO_CHAR(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(
776 l_currency_code, 30)),' ') || '</TD>' || NL;
777 l_document := l_document || '</TR>' || NL;
778
779 -- <BUG 7006113 START>
780 -- curr_len := lengthb(l_document);
781
782 wf_notification.writetoclob(document, l_document);
783
784 l_document := NULL;
785
786 EXIT WHEN i = l_num_records_to_display;
787 -- <BUG 7006113 END>
788
789 end loop;
790
791 end if;
792 l_document := l_document || '</TABLE></P>' || NL;
793
794 wf_notification.writetoclob(document, l_document); -- <BUG 7006113>
795
796 -- document := l_document; -- <BUG 7006113>
797
798 elsif (display_type = 'text/plain') then
799
800 if (nvl(l_document_type, 'PO') <> 'RELEASE') then
801
802 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_LINE_DETAILS') || NL || NL;
803
804 -- <BUG 3616816 START> Only display message if # of actual lines is
805 -- greater than maximum limit.
806 --
807 IF ( l_line_count > max_lines_dsp ) THEN
808
809 -- Bug 3668188: changed the code check (originally created
810 -- in bug 3607009) that determines which message to show
811 -- based on whether Open Document icon is shown in then notif.
812 -- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
813 -- previous node, using the get_po_user_msg_attribute procedure.
814 -- HTML Orders R12
815 -- Check for the URL parameters as well
816 IF (l_open_form_command IS NULL) AND
817 (l_view_po_url IS NULL) AND
818 (l_edit_po_url IS NULL)
819 THEN
820 -- "The first [COUNT] Purchase Order lines are summarized below."
821 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG_TRUNC');
822 ELSE
823 -- "The first [COUNT] Purchase Order lines are summarized
824 -- below. For information on additional lines, please click
825 -- the Open Document icon."
826 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG');
827 END IF;
828
829 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
830 line_mesg := FND_MESSAGE.get;
831 l_document := l_document || line_mesg || NL || NL;
832
833 END IF;
834 --
835 -- <BUG 3616816 END>
836
837 -- curr_len := lengthb(l_document);
838 -- prior_len := curr_len;
839
840 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
841
842 /* Exit the cursor if the current document length and 2 times the
843 ** length added in prior line exceeds 32000 char */
844 -- < BUG 7006113 START Commented the loop to avoid the check so
845 -- that maximum lines can be displayed >
846 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
847 -- exit;
848 -- end if;
849 -- prior_len := curr_len;
850 -- < BUG 7006113 END >
851
852 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ':' || to_char(l_line_num_tbl(i)) || NL;
853 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || ': ' || l_item_num_tbl(i) || NL;
854 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || ': ' || l_item_revision_tbl(i) || NL;
855 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || ': ' || l_item_desc_tbl(i) || NL;
856 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' || l_uom_tbl(i) || NL;
857 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' || to_char(l_quantity_tbl(i)) || NL;
858
859 /* Bug 2868931: kagarwal
860 ** We will not format the unit price on the lines in notifications
861 */
862
863 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
864 || PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_currency_code,l_unit_price_tbl(i)) || NL;
865 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || ': '
866 || to_char(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL || NL;
867
868 -- < BUG 7006113 START >
869 -- curr_len := lengthb(l_document);
870
871 wf_notification.writetoclob(document, l_document);
872
873 l_document := NULL;
874
875 EXIT WHEN i = l_num_records_to_display;
876 -- < BUG 7006113 END >
877
878 end loop;
879
880 else -- release
881
882 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_DETAILS') || NL || NL || NL;
883
884 -- <BUG 3616816 START> Only display message if # of actual lines is
885 -- greater than maximum limit.
886 --
887 IF ( l_line_count > max_lines_dsp )
888 THEN
889 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_REL_SHIP_MESG');
890 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
891 line_mesg := FND_MESSAGE.get;
892 l_document := l_document || line_mesg || NL || NL;
893 END IF;
894 --
895 -- <BUG 3616816 END>
896
897 -- curr_len := lengthb(l_document);
898 -- prior_len := curr_len;
899
900 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
901
902 /* Exit the cursor if the current document length and 2 times the
903 ** length added in prior line exceeds 32000 char */
904 -- <BUG 7006113 START Commented the loop to avoid the check so that
905 -- maximum lines can be displayed
906 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
907 -- exit;
908 -- end if;
909 -- prior_len := curr_len;
910 -- <BUG 7006113 END>
911
912 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_NUMBER') || ': ' || to_char(l_shipment_num_tbl(i)) || NL;
913 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || ': ' || l_item_num_tbl(i) || NL;
914 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || ': ' || l_item_revision_tbl(i) || NL;
915 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || ': ' || l_item_desc_tbl(i) || NL;
916 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' || l_uom_tbl(i) || NL;
917 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' || to_char(l_quantity_tbl(i)) || NL;
918
919 /* Bug 2868931: kagarwal
920 ** We will not format the unit price on the lines in notifications
921 */
922
923 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
924 || PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_currency_code,l_unit_price_tbl(i)) || NL;
925 -- bug 4950850
926 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') || ': '
927 || to_char(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL;
928 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LOCATION') || ': ' || l_location_tbl(i) || NL;
929 l_document := l_document || fnd_message.get_string('PO', 'POA_SHIP_TO_ORG') || ': ' || l_org_name_tbl(i) || NL;
930 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY_DATE') || ': ' || to_char(l_need_by_date_tbl(i)) || NL || NL;
931
932 -- <BUG 7006113 START>
933 -- curr_len := lengthb(l_document);
934
935
936 wf_notification.writetoclob(document, l_document);
937
938 l_document := NULL;
939
940 EXIT WHEN i = l_num_records_to_display;
941 -- <BUG 7006113 END>
942
943 end loop;
944
945 end if;
946
947 wf_notification.writetoclob(document, l_document); -- <BUG 7006113>
948 -- document := l_document; -- <Bug 7006113>
949 end if;
950
951 END get_po_lines_details;
952
953 PROCEDURE get_action_history ( document_id in varchar2,
954 display_type in varchar2,
955 document in out NOCOPY varchar2,
956 document_type in out NOCOPY varchar2) IS
957
958 l_item_type wf_items.item_type%TYPE;
959 l_item_key wf_items.item_key%TYPE;
960
961 l_document_id po_lines.po_header_id%TYPE;
962 l_org_id po_lines.org_id%TYPE;
963 l_doc_type_code VARCHAR2(80);
964
965 l_document VARCHAR2(32000) := '';
966
967 -- Bug 3668188: added new local var. note: the length of this
968 -- varchar was determined based on the length in POXWPA1B.pls,
969 -- which is the other place 'OPEN_FORM_COMMAND' attribute is used
970 l_open_form_command VARCHAR2(200);
971
972 l_view_po_url varchar2(1000); -- HTML Orders R12
973 l_edit_po_url varchar2(1000); -- HTML Orders R12
974
975 NL VARCHAR2(1) := fnd_global.newline;
976
977 -- <BUG 3616816 START> Declare TABLEs for each column that is selected
978 -- from history_csr cursor.
979 --
980 TYPE sequence_num_tbl_type IS TABLE OF PO_ACTION_HISTORY.sequence_num%TYPE;
981 TYPE full_name_tbl_type IS TABLE OF PER_ALL_PEOPLE_F.full_name%TYPE;
982 TYPE displayed_field_tbl_type IS TABLE OF PO_LOOKUP_CODES.displayed_field%TYPE;
983 TYPE action_date_tbl_type IS TABLE OF PO_ACTION_HISTORY.action_date%TYPE;
984 TYPE note_tbl_type IS TABLE OF PO_ACTION_HISTORY.note%TYPE;
985 TYPE object_revision_num_tbl_type IS TABLE OF PO_ACTION_HISTORY.object_revision_num%TYPE;
986 TYPE employee_id_tbl_type IS TABLE OF PO_ACTION_HISTORY.employee_id%TYPE;
987 TYPE created_by_tbl_type IS TABLE OF PO_ACTION_HISTORY.created_by%TYPE;
988
989 l_sequence_num_tbl sequence_num_tbl_type;
990 l_employee_name_tbl full_name_tbl_type;
991 l_action_tbl displayed_field_tbl_type;
992 l_action_date_tbl action_date_tbl_type;
993 l_note_tbl note_tbl_type;
994 l_object_revision_num_tbl object_revision_num_tbl_type;
995 l_employee_id_tbl employee_id_tbl_type;
996 l_created_by_tbl created_by_tbl_type;
997 --
998 -- <BUG 3616816 END>
999
1000 --SQL What: Query action history which is updated by both buyer and vendor
1001 --SQL Why: Since vendor doesn't have employee id, added outer join;
1002 CURSOR history_csr(v_document_id NUMBER, v_doc_type_code VARCHAR2) IS
1003 SELECT poh.SEQUENCE_NUM,
1004 per.FULL_NAME,
1005 polc.DISPLAYED_FIELD,
1006 poh.ACTION_DATE,
1007 poh.NOTE,
1008 poh.OBJECT_REVISION_NUM,
1009 poh.employee_id, /* bug 2788683 */
1010 poh.created_by /* bug 2788683 */
1011 from po_action_history poh,
1012 per_all_people_f per, -- Bug 3404451
1013 po_lookup_codes polc
1014 where OBJECT_TYPE_CODE = v_doc_type_code
1015 and poh.action_code = polc.lookup_code
1016 and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
1017 and per.person_id(+) = poh.employee_id /* bug 2788683 */
1018 and trunc(sysdate) between per.effective_start_date(+)
1019 and per.effective_end_date(+)
1020 and OBJECT_ID = v_document_id
1021 UNION ALL
1022 SELECT poh.SEQUENCE_NUM,
1023 per.FULL_NAME,
1024 NULL,
1025 poh.ACTION_DATE,
1026 poh.NOTE,
1027 poh.OBJECT_REVISION_NUM,
1028 poh.employee_id, /* bug 2788683 */
1029 poh.created_by /* bug 2788683 */
1030 from po_action_history poh,
1031 per_all_people_f per -- Bug 3404451
1032 where OBJECT_TYPE_CODE = v_doc_type_code
1033 and poh.action_code is null
1034 and per.person_id(+) = poh.employee_id /* bug 2788683 */
1035 and trunc(sysdate) between per.effective_start_date(+)
1036 and per.effective_end_date(+)
1037 and OBJECT_ID = v_document_id
1038 order by 1 desc;
1039
1040 i NUMBER := 0;
1041 max_actions_dsp NUMBER := 20;
1042 l_action_count NUMBER; -- <BUG 3616816> # of action history records
1043 l_num_records_to_display NUMBER; -- <BUG 3616816> actual # of records to display in table
1044 action_mesg fnd_new_messages.message_text%TYPE; --Bug 4695601
1045 curr_len NUMBER := 0;
1046 prior_len NUMBER := 0;
1047
1048 /* Bug 2788683 start */
1049 l_user_name fnd_user.user_name%TYPE;
1050 l_vendor_name hz_parties.party_name%TYPE;
1051 l_party_name hz_parties.party_name%TYPE;
1052 /* Bug 2788683 end */
1053
1054
1055 BEGIN
1056
1057 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1058 l_item_key := substr(document_id, instr(document_id, ':') + 1,
1059 length(document_id) - 2);
1060
1061 l_document_id := wf_engine.GetItemAttrNumber
1062 (itemtype => l_item_type,
1063 itemkey => l_item_key,
1064 aname => 'DOCUMENT_ID');
1065
1066 l_org_id := wf_engine.GetItemAttrNumber
1067 (itemtype => l_item_type,
1068 itemkey => l_item_key,
1069 aname => 'ORG_ID');
1070
1071 l_doc_type_code := wf_engine.GetItemAttrText (itemtype => l_item_type,
1072 itemkey => l_item_key,
1073 aname => 'DOCUMENT_TYPE');
1074
1075 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1076
1077 -- Bug 3668188
1078 l_open_form_command := PO_WF_UTIL_PKG.GetItemAttrText
1079 (itemtype => l_item_type,
1080 itemkey => l_item_key,
1081 aname => 'OPEN_FORM_COMMAND');
1082
1083 -- HTML Orders R12
1084 -- Get the PO HTML Page URL's
1085 l_view_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
1086 itemtype => l_item_type,
1087 itemkey => l_item_key,
1088 aname => 'VIEW_DOC_URL');
1089
1090 l_edit_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
1091 itemtype => l_item_type,
1092 itemkey => l_item_key,
1093 aname => 'EDIT_DOC_URL');
1094
1095 /* Bug# 2577478: kagarwal
1096 ** Desc: Added a new attribute ACT_HST_IN_NTF in wf definition for
1097 ** users to specify the number of PO actions to be displayed in a
1098 ** notification.
1099 ** If the attribute does not exist or is null, then we would use default
1100 ** value of 20.
1101 */
1102
1103 max_actions_dsp:= PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => l_item_type,
1104 itemkey => l_item_key,
1105 aname => 'ACT_HST_IN_NTF');
1106
1107 if max_actions_dsp is NULL then
1108 max_actions_dsp := 20;
1109 end if;
1110
1111 -- <BUG 3616816 START> Fetch all Action History data into Tables.
1112 --
1113 OPEN history_csr(l_document_id,l_doc_type_code);
1114
1115 FETCH history_csr BULK COLLECT INTO l_sequence_num_tbl
1116 , l_employee_name_tbl
1117 , l_action_tbl
1118 , l_action_date_tbl
1119 , l_note_tbl
1120 , l_object_revision_num_tbl
1121 , l_employee_id_tbl
1122 , l_created_by_tbl;
1123
1124 l_action_count := history_csr%ROWCOUNT; -- Get # of records fetched.
1125
1126 CLOSE history_csr;
1127 --
1128 -- <BUG 3616816 END>
1129
1130 -- <BUG 3616816 START> Only display message if # of actual Action History
1131 -- records is greater than maximum limit.
1132 --
1133 IF ( l_action_count > max_actions_dsp ) THEN
1134
1135 l_num_records_to_display := max_actions_dsp;
1136
1137 -- Bug 3668188: changed the code check (originally created
1138 -- in bug 3607009) that determines which message to show
1139 -- based on whether Open Document icon is shown in then notif.
1140 -- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
1141 -- previous node, using the get_po_user_msg_attribute procedure.
1142 --
1143 IF (l_open_form_command IS NULL) AND
1144 (l_view_po_url IS NULL) AND
1145 (l_edit_po_url IS NULL)
1146 THEN
1147 -- "The last [COUNT] Approval History details are summarized below."
1148 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_ACT_MESG_TRUNC');
1149 ELSE
1150 -- "The last [COUNT] Approval History details are summarized below.
1151 -- For information on additional Approval History, please click the
1152 -- Open Document icon."
1153 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_ACT_MESG');
1154 END IF;
1155
1156 FND_MESSAGE.set_token('COUNT',to_char(max_actions_dsp));
1157 action_mesg := FND_MESSAGE.get;
1158
1159 ELSE
1160
1161 l_num_records_to_display := l_action_count;
1162 action_mesg := NULL;
1163
1164 END IF;
1165 --
1166 -- <BUG 3616816 END>
1167
1168 if (display_type = 'text/html') then
1169
1170 l_document := NL || NL || '<!-- ACTION_HISTORY -->'|| NL || NL || '<P><B>';
1171 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY') || NL;
1172 l_document := l_document || '</B>' || NL || NL || '<P>'; -- <BUG 3616816>
1173
1174 -- <BUG 3616816 START> Action History message may be NULL. Only append it
1175 -- and corresponding line breaks if there is a message to display.
1176 --
1177 IF ( action_mesg IS NOT NULL )
1178 THEN
1179 l_document := l_document || action_mesg || '<P>' || NL;
1180 END IF;
1181 --
1182 -- <BUG 3616816 END>
1183
1184 l_document := l_document || '<TABLE border=1 cellpadding=2 cellspacing=1 summary="' || fnd_message.get_string('ICX', 'ICX_POR_TBL_OF_APPROVERS') || '">' || NL;
1185
1186 l_document := l_document || '<TR>';
1187
1188 l_document := l_document || '<TH id="seqNum_1">' ||
1189 fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') || '</TH>' || NL;
1190
1191 l_document := l_document || '<TH id="employee_1">' ||
1192 fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || '</TH>' || NL;
1193
1194 l_document := l_document || '<TH id="action_1">' ||
1195 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || '</TH>' || NL;
1196
1197 l_document := l_document || '<TH id="date_1">' ||
1198 fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || '</TH>' || NL;
1199
1200 l_document := l_document || '<TH id="actionNote_1">' ||
1201 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || '</TH>' || NL;
1202
1203 l_document := l_document || '</TR>' || NL;
1204
1205 curr_len := lengthb(l_document);
1206 prior_len := curr_len;
1207
1208 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
1209
1210 /* Exit the cursor if the current document length and 2 times the
1211 ** length added in prior line exceeds 32000 char */
1212
1213 if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
1214 exit;
1215 end if;
1216
1217 prior_len := curr_len;
1218
1219 l_document := l_document || '<TR>' || NL;
1220
1221 l_document := l_document || '<TD nowrap align=center headers="seqNum_1">'
1222 || nvl(to_char(l_sequence_num_tbl(i)), ' ') || '</TD>' || NL;
1223
1224 /* Bug 2788683 start */
1225 /* if action history is updated by vendor
1226 * show vendor true name(vendor name)
1227 * else action history is updated by buyer
1228 * show buyer's true name
1229 */
1230 IF l_employee_id_tbl(i) IS NULL THEN
1231 SELECT fu.user_name,
1232 hp.party_name
1233 INTO l_user_name,
1234 l_party_name
1235 FROM fnd_user fu,
1236 hz_parties hp
1237 WHERE hp.party_id = fu.customer_id
1238 AND fu.user_id = l_created_by_tbl(i);
1239
1240 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
1241
1242 l_document := l_document || '<TD nowrap headers="employee_1">' || l_party_name || '(' || l_vendor_name || ')' || '</TD>' || NL;
1243 ELSE
1244 l_document := l_document || '<TD nowrap headers="employee_1">'
1245 || nvl(l_employee_name_tbl(i), ' ') || '</TD>' || NL;
1246 END IF;
1247 /* Bug 2788683 end */
1248
1249 l_document := l_document || '<TD nowrap headers="action_1">'
1250 || nvl(l_action_tbl(i), ' ') || '</TD>' || NL;
1251 l_document := l_document || '<TD nowrap headers="date_1">'
1252 || nvl(to_char(l_action_date_tbl(i)), ' ') || '</TD>' || NL;
1253 l_document := l_document || '<TD nowrap headers="actionNote_1">'
1254 || nvl(l_note_tbl(i), ' ') || '</TD>' || NL;
1255
1256 l_document := l_document || '</TR>' || NL;
1257
1258 curr_len := lengthb(l_document);
1259
1260 end loop;
1261
1262 l_document := l_document || '</TABLE></P>' || NL;
1263
1264 document := l_document;
1265
1266 elsif (display_type = 'text/plain') then
1267
1268 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY') || NL;
1269
1270 -- <BUG 3616816 START> Action History message may be NULL. Only append it
1271 -- and corresponding line breaks if there is a message to display.
1272 --
1273 IF ( action_mesg IS NOT NULL )
1274 THEN
1275 l_document := l_document || action_mesg || NL || NL;
1276 END IF;
1277 --
1278 -- <BUG 3616816 END>
1279
1280 curr_len := lengthb(l_document);
1281 prior_len := curr_len;
1282
1283 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
1284
1285 /* Exit the cursor if the current document length and 2 times the
1286 ** length added in prior line exceeds 32000 char */
1287
1288 if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
1289 exit;
1290 end if;
1291
1292 prior_len := curr_len;
1293
1294 l_document := l_document || NL;
1295
1296 /* Bug 2462005 sktiwari:
1297 ** Added a ':' between the prompt and the data. Modified the following lines.
1298 */
1299 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') || ': ' || to_char(l_sequence_num_tbl(i)) || NL;
1300
1301 /* Bug 2788683 start */
1302 /* if action history is updated by vendor
1303 * show vendor true name(vendor name)
1304 * else action history is updated by buyer
1305 * show buyer's true name
1306 */
1307 IF l_employee_id_tbl(i) IS NULL THEN
1308 SELECT fu.user_name, hp.party_name
1309 INTO l_user_name, l_party_name
1310 FROM fnd_user fu,
1311 hz_parties hp
1312 WHERE hp.party_id = fu.customer_id
1313 AND fu.user_id = l_created_by_tbl(i);
1314
1315 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
1316
1317 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || ': ' || l_party_name || '(' || l_vendor_name || ')' || NL;
1318 ELSE
1319 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || ': ' || l_employee_name_tbl(i) || NL;
1320 END IF;
1321 /* Bug 2788683 end */
1322
1323 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || ': ' || l_action_tbl(i) || NL;
1324 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || ': ' || to_char(l_action_date_tbl(i)) || NL;
1325 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || ': ' || l_note_tbl(i) || NL;
1326
1327 l_document := l_document || NL;
1328
1329 curr_len := lengthb(l_document);
1330 end loop;
1331
1332 l_document := l_document;
1333
1334 document := l_document;
1335
1336 end if;
1337
1338 END;
1339
1340 PROCEDURE post_approval_notif(itemtype in varchar2,
1341 itemkey in varchar2,
1342 actid in number,
1343 funcmode in varchar2,
1344 resultout in out NOCOPY varchar2) is
1345
1346 -- Context setting revamp <variable addition start>
1347 l_responder_id fnd_user.user_id%TYPE;
1348 l_session_user_id NUMBER;
1349 l_session_resp_id NUMBER;
1350 l_session_appl_id NUMBER;
1351 l_preparer_resp_id NUMBER;
1352 l_preparer_appl_id NUMBER;
1353 l_progress VARCHAR2(1000);
1354 l_nid NUMBER;
1355 l_preserved_ctx VARCHAR2(5);
1356 -- Context setting revamp <variable addition end>
1357
1358 begin
1359
1360 -- Context setting revamp <start>
1361 if (funcmode = 'RESPOND') then
1362 l_nid := WF_ENGINE.context_nid;
1363
1364 SELECT fu.USER_ID
1365 INTO l_responder_id
1366 FROM fnd_user fu,
1367 wf_notifications wfn
1368 WHERE wfn.notification_id = l_nid
1369 AND wfn.original_recipient = fu.user_name;
1370
1371 -- <debug start>
1372 if (wf_engine.preserved_context = TRUE) then
1373 l_preserved_ctx := 'TRUE';
1374 else
1375 l_preserved_ctx := 'FALSE';
1376 end if;
1377 l_progress := 'notif callback preserved_ctx : '||l_preserved_ctx;
1378 IF (g_po_wf_debug = 'Y') THEN
1379 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1380 END IF;
1381 -- <debug end>
1382
1383 -- <debug start>
1384 l_progress := '010 notif callback -responder id : '||l_responder_id;
1385 IF (g_po_wf_debug = 'Y') THEN
1386 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1387 END IF;
1388 --<debug end>
1389 --Bug 5389914
1390 --Fnd_Profile.Get('USER_ID',l_session_user_id);
1391 --Fnd_Profile.Get('RESP_ID',l_session_resp_id);
1392 --Fnd_Profile.Get('RESP_APPL_ID',l_session_appl_id);
1393 l_session_user_id := fnd_global.user_id;
1394 l_session_resp_id := fnd_global.resp_id;
1395 l_session_appl_id := fnd_global.resp_appl_id;
1396
1397 IF (l_session_user_id = -1) THEN
1398 l_session_user_id := NULL;
1399 END IF;
1400
1401 IF (l_session_resp_id = -1) THEN
1402 l_session_resp_id := NULL;
1403 END IF;
1404
1405 IF (l_session_appl_id = -1) THEN
1406 l_session_appl_id := NULL;
1407 END IF;
1408
1409 -- <debug start>
1410 l_progress :='020 notification callback ses_userid: '||l_session_user_id
1411 ||' sess_resp_id '||l_session_resp_id||' sess_appl_id '
1412 ||l_session_appl_id;
1413 IF (g_po_wf_debug = 'Y') THEN
1414 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1415 END IF;
1416 --<debug end>
1417
1418 -- bug 4901406 <start> : need to shift the setting of the preparer resp and appl id
1419 -- to here, it was not initialized inside the if condition if the control went to the
1420 -- else part.
1421 l_preparer_resp_id :=
1422 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
1423 itemkey => itemkey,
1424 aname => 'RESPONSIBILITY_ID');
1425 l_preparer_appl_id :=
1426 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
1427 itemkey => itemkey,
1428 aname => 'APPLICATION_ID');
1429
1430 -- <debug start>
1431 l_progress := '030 notif callback prep resp_id:'||l_preparer_resp_id
1432 ||' prep appl id '||l_preparer_appl_id;
1433 IF (g_po_wf_debug = 'Y') THEN
1434 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1435 END IF;
1436 --<debug end>
1437
1438 -- bug 4901406 <end>
1439
1440
1441 if (l_responder_id is not null) then
1442 if (l_responder_id <> l_session_user_id) then
1443 /* possible in 2 scenarios :
1444 1. when the response is made from email using guest user feature
1445 2. When the response is made from sysadmin login
1446 */
1447
1448
1449
1450 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1451 itemkey => itemkey,
1452 aname => 'RESPONDER_USER_ID',
1453 avalue => l_responder_id);
1454 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1455 itemkey => itemkey,
1456 aname => 'RESPONDER_RESP_ID',
1457 avalue => l_preparer_resp_id);
1458 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1459 itemkey => itemkey,
1460 aname => 'RESPONDER_APPL_ID',
1461 avalue => l_preparer_appl_id);
1462 else
1463 if (l_session_resp_id is null) THEN
1464 /* possible when the response is made from the default worklist
1465 without choosing a valid responsibility */
1466 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1467 itemkey => itemkey,
1468 aname => 'RESPONDER_USER_ID',
1469 avalue => l_responder_id);
1470 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1471 itemkey => itemkey,
1472 aname => 'RESPONDER_RESP_ID',
1473 avalue => l_preparer_resp_id);
1474 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1475 itemkey => itemkey,
1476 aname => 'RESPONDER_APPL_ID',
1477 avalue => l_preparer_appl_id);
1478 else
1479 /* all values available - possible when the response is made
1480 after choosing a correct responsibility */
1481
1482 /* bug 5333226 : If the values of responsibility_id and application
1483 id are available but are incorrect - i.e. not conforming to say the
1484 sls (subledger security). This may happen when a response is made
1485 through the email or the background process picks the wf up.
1486 This may happen due to the fact that the mailer / background process
1487 carries the context set by the notification/wf it processed last*/
1488
1489 if ( l_preserved_ctx = 'TRUE') then
1490 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1491 itemkey => itemkey,
1492 aname => 'RESPONDER_USER_ID',
1493 avalue => l_responder_id);
1494 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1495 itemkey => itemkey,
1496 aname => 'RESPONDER_RESP_ID',
1497 avalue => l_session_resp_id);
1498 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1499 itemkey => itemkey,
1500 aname => 'RESPONDER_APPL_ID',
1501 avalue => l_session_appl_id);
1502 else
1503 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1504 itemkey => itemkey,
1505 aname => 'RESPONDER_USER_ID',
1506 avalue => l_responder_id);
1507 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1508 itemkey => itemkey,
1509 aname => 'RESPONDER_RESP_ID',
1510 avalue => l_preparer_resp_id);
1511 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
1512 itemkey => itemkey,
1513 aname => 'RESPONDER_APPL_ID',
1514 avalue => l_preparer_appl_id);
1515 end if;
1516
1517
1518 end if;
1519 end if;
1520 end if;
1521
1522 -- Context setting revamp <end>
1523
1524
1525 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1526 l_progress := '040 returning from notif callback -respond mode';
1527 IF (g_po_wf_debug = 'Y') THEN
1528 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1529 END IF;
1530 return;
1531 end if;
1532
1533
1534 -- Don't allow transfer
1535 if (funcmode = 'TRANSFER') then
1536
1537 fnd_message.set_name('PO', 'PO_WF_NOTIF_NO_TRANSFER');
1538 app_exception.raise_exception;
1539
1540 resultout := wf_engine.eng_completed;
1541 return;
1542
1543 end if; -- end if for funcmode = 'TRANSFER'
1544
1545 end post_approval_notif;
1546
1547
1548 /* Bug# 2616433: kagarwal
1549 ** Desc: Added new procedure to set notification subject token in
1550 ** user language.
1551 */
1552
1553 procedure Get_po_user_msg_attribute(itemtype in varchar2,
1554 itemkey in varchar2,
1555 actid in number,
1556 funcmode in varchar2,
1557 resultout out NOCOPY varchar2) IS
1558
1559 l_progress VARCHAR2(100) := '000';
1560 l_doc_string varchar2(200);
1561 l_user_name varchar2(100);
1562 l_preparer_user_name varchar2(100);
1563 l_orgid number;
1564 l_notification_type varchar2(15); --bug 3668188
1565
1566 -- <Start Word Integration 11.5.10+>
1567 l_okc_doc_type varchar2(20);
1568 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
1569 l_document_id NUMBER;
1570 l_document_subtype PO_HEADERS_ALL.type_lookup_code%TYPE;
1571 -- <End Word Integration 11.5.10+>
1572
1573 BEGIN
1574
1575 -- Do nothing in cancel or timeout mode
1576 --
1577 if (funcmode <> wf_engine.eng_run) then
1578 resultout := wf_engine.eng_null;
1579 return;
1580 end if;
1581
1582 l_progress := 'Get_po_user_msg_attribute:001: actid: ' || actid;
1583 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1584
1585 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1586 itemkey => itemkey,
1587 aname => 'ORG_ID');
1588
1589 IF l_orgid is NOT NULL THEN
1590 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
1591 END IF;
1592
1593 l_progress := 'Get_po_user_msg_attribute:010: orgid: ' || l_orgid;
1594 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1595
1596 l_user_name := wf_engine.GetActivityAttrText (itemtype => itemtype,
1597 itemkey => itemkey,
1598 actid => actid,
1599 aname => 'NTF_USER_NAME',
1600 ignore_notfound => TRUE);
1601
1602 PO_WF_PO_NOTIFICATION.GetDisplayValue(itemtype, itemkey, l_user_name);
1603
1604
1605 l_progress := 'Get_po_user_msg_attribute:015: username: ' || l_user_name;
1606 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1607
1608
1609 -- Bug 3668188: added the NTF_USER_ROLE Activity Attribute, which is
1610 -- a constant with value 'SUBMITTER' or 'APPROVER'. This distinguishes
1611 -- between the 3 different notification-functions that share this procedure.
1612 l_notification_type := wf_engine.GetActivityAttrText (
1613 itemtype => itemtype,
1614 itemkey => itemkey,
1615 actid => actid,
1616 aname => 'NTF_USER_ROLE',
1617 ignore_notfound => TRUE);
1618
1619 l_progress := 'Get_po_user_msg_attribute:020: notif type: ' || l_notification_type;
1620 -- DEBUG
1621 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1622
1623
1624 -- Bug 3668188: Removed old logic from bug 3564727 and replaced it
1625 -- with a call to is_open_document_allowed, which has updated logic.
1626 IF NOT (PO_WF_PO_NOTIFICATION.is_open_document_allowed(
1627 p_itemtype => itemtype
1628 , p_itemkey => itemkey
1629 , p_notification_type => l_notification_type)
1630 ) THEN
1631 l_progress := 'Get_po_approver_msg_attribute: 040: NULL open form';
1632 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1633
1634 -- HTML Orders R12
1635 -- Set the URL and form attributes
1636 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
1637 itemkey => itemkey,
1638 aname => 'OPEN_FORM_COMMAND' ,
1639 avalue => '');
1640
1641 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
1642 itemkey => itemkey,
1643 aname => 'VIEW_DOC_URL' ,
1644 avalue => '');
1645
1646 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
1647 itemkey => itemkey,
1648 aname => 'EDIT_DOC_URL' ,
1649 avalue => '');
1650 END IF;
1651
1652
1653 -- <Start Word Integration 11.5.10+>
1654
1655 l_conterms_exist_flag := PO_WF_UTIL_PKG.GetItemAttrText(
1656 itemtype => itemtype,
1657 itemkey => itemkey,
1658 aname => 'CONTERMS_EXIST_FLAG');
1659
1660 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText(
1661 itemtype => itemtype,
1662 itemkey => itemkey,
1663 aname => 'DOCUMENT_SUBTYPE');
1664
1665 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
1666 itemtype => itemtype,
1667 itemkey => itemkey,
1668 aname => 'DOCUMENT_ID');
1669
1670
1671 /* Set or reset the okc doc attachment attribute */
1672
1673 IF (l_conterms_exist_flag = 'Y')
1674 THEN
1675 l_okc_doc_type :=
1676 PO_CONTERMS_UTL_GRP.get_po_contract_doctype(l_document_subtype);
1677
1678 IF (('STRUCTURED' <>
1679 OKC_TERMS_UTIL_GRP.get_contract_source_code(p_document_type => l_okc_doc_type
1680 , p_document_id => l_document_id))
1681 AND
1682 ('N' =
1683 OKC_TERMS_UTIL_GRP.is_primary_terms_doc_mergeable(
1684 P_document_type => l_okc_doc_type
1685 , p_document_id => l_document_id))
1686 AND
1687 (PO_COMMUNICATION_PVT.PO_COMMUNICATION_PROFILE = 'T')
1688 )
1689 THEN
1690
1691 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
1692 itemkey => itemkey,
1693 aname => 'OKC_DOC_ATTACHMENT',
1694 avalue =>
1695 'PLSQLBLOB:PO_COMMUNICATION_PVT.OKC_DOC_ATTACH/'||
1696 itemtype||':'||itemkey);
1697
1698 ELSE
1699
1700 /* Contract terms are structured, or attached document is mergeable.
1701 * All contract terms will be in pdf; no need for other okc doc attachment.
1702 */
1703
1704 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
1705 itemkey => itemkey,
1706 aname => 'OKC_DOC_ATTACHMENT',
1707 avalue => '');
1708
1709 END IF /* not structured and not mergeable */;
1710
1711 END IF; /* l_conterms_exist_flag = 'Y' */
1712
1713
1714 -- <End Word Integration 11.5.10+>
1715
1716
1717
1718 l_progress := 'Get_po_approver_msg_attribute: 999';
1719 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1720
1721 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1726 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1727 wf_core.context('PO_WF_PO_NOTIFICATION','Get_req_approval_msg_attribute',l_progress);
1728 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
1729 l_doc_string, sqlerrm, 'PO_WF_PO_NOTIFICATION.Get_po_approver_msg_attribute');
1730 raise;
1731
1732 END Get_po_user_msg_attribute;
1733
1734
1735 /* Bug# 2616433: kagarwal
1736 ** Desc: Added new procedure to set doc type display according to the
1737 ** default language of approver or preparer.
1738 */
1739
1740 procedure GetDisplayValue(itemtype in varchar2,
1741 itemkey in varchar2,
1742 username in varchar2,
1743 doctype in varchar2,
1744 docsubtype in varchar2) IS
1745
1746 l_progress VARCHAR2(400) := '000';
1747 l_doc_type varchar2(25);
1748 l_doc_subtype varchar2(25);
1749 l_doc_disp varchar2(240);
1750 l_ga_flag varchar2(1) := null;
1751
1752 l_display_name varchar2(240);
1753 l_email_address varchar2(240);
1754 l_notification_preference varchar2(240);
1755 l_language varchar2(240);
1756 l_territory varchar2(240);
1757 l_msg_text varchar2(2000) := NULL; -- Bug 3430545
1758
1759 cursor c_lookup_value_user(p_doc_type varchar2, p_doc_subtype varchar2,
1760 p_language varchar2) is
1761 select type_name
1762 from po_document_types_tl tl, FND_LANGUAGES fl
1763 where fl.nls_language = p_language
1764 and tl.LANGUAGE = fl.language_code
1765 and tl.document_type_code = p_doc_type
1766 and tl.document_subtype = p_doc_subtype;
1767
1768 cursor c_lookup_value_doc(p_doc_type varchar2, p_doc_subtype varchar2) is
1769 select type_name
1770 from po_document_types
1771 where document_type_code = p_doc_type
1772 and document_subtype = p_doc_subtype;
1773
1774 l_document_id PO_HEADERS_ALL.po_header_id%TYPE; --<R12 STYLES PHASE II>
1775
1776 BEGIN
1777 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 001, user name: '
1778 || username;
1779 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1780
1781 if ((doctype is NULL) or (docsubtype is null)) then
1782 l_doc_type := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1783 itemkey => itemkey,
1784 aname => 'DOCUMENT_TYPE');
1785
1786 l_doc_subtype := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
1787 itemkey => itemkey,
1788 aname => 'DOCUMENT_SUBTYPE');
1789 else
1790 l_doc_type := doctype;
1791 l_doc_subtype := docsubtype;
1792 end if;
1793
1794 IF l_doc_type = 'PA' AND l_doc_subtype = 'BLANKET' THEN
1795
1796 l_ga_flag := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
1797 itemkey => itemkey,
1798 aname => 'GLOBAL_AGREEMENT_FLAG');
1799 END IF;
1800
1801 /* Bug 3430545: Modified the code to get the translated values for the wf
1802 notification attribute 'REQUIRES_APPROVAL_MSG' and 'PO_GA_TYPE'.
1803 Deleted the previous code and revamped it.
1804 */
1805 --<R12 STYLES PHASE II START>
1806 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1807 (itemtype => itemtype,
1808 itemkey => itemkey,
1809 aname => 'DOCUMENT_ID');
1810
1811 IF username is NULL THEN
1812 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 050';
1813 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1814
1815 IF l_doc_type = 'PA' AND l_doc_subtype IN ('BLANKET','CONTRACT') OR
1816 l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD' then
1817
1818 l_doc_disp := PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id);
1819 ELSE
1820 OPEN c_lookup_value_doc(l_doc_type, l_doc_subtype);
1821 FETCH c_lookup_value_doc into l_doc_disp;
1822 CLOSE c_lookup_value_doc;
1823 END IF;
1824 ELSE
1825 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 060';
1826 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1827 WF_DIRECTORY.GETROLEINFO(
1828 username,
1829 l_display_name,
1830 l_email_address,
1831 l_notification_preference,
1832 l_language,
1833 l_territory);
1834 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 080, language: '
1835 || l_language;
1836 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1837
1838 IF l_doc_type = 'PA' AND l_doc_subtype IN ('BLANKET','CONTRACT') OR
1839 l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD' then
1840
1841 l_doc_disp := PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id,l_language);
1842 ELSE
1843 OPEN c_lookup_value_user(l_doc_type, l_doc_subtype, l_language);
1844 FETCH c_lookup_value_user into l_doc_disp;
1845 CLOSE c_lookup_value_user;
1846 END IF;
1847 END IF; /* if username is null */
1848 --<R12 STYLES PHASE II END>
1849 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 100, type disp: '
1850 || l_doc_disp;
1851 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1852
1853 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
1854 itemkey => itemkey,
1855 aname => 'DOCUMENT_TYPE_DISP',
1856 avalue => l_doc_disp);
1857 BEGIN
1858 select message_text
1859 into l_msg_text
1860 from fnd_new_messages fm,fnd_languages fl
1861 where fm.message_name = 'PO_WF_NOTIF_REQUIRES_APPROVAL'
1862 and fm.language_code = fl.language_code
1863 and fl.nls_language = l_language
1864 and fm.application_id = 201; --<BUG 3712124> Include application_id to better use PK index
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 l_msg_text := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
1868 itemkey => itemkey,
1869 aname => 'REQUIRES_APPROVAL_MSG');
1870 END;
1871
1872 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
1873 itemkey => itemkey,
1874 aname => 'REQUIRES_APPROVAL_MSG',
1875 avalue => l_msg_text );
1876
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: sql err: ' || sqlerrm;
1880 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1881 IF (c_lookup_value_user%ISOPEN) THEN
1882 CLOSE c_lookup_value_user;
1883 END IF;
1884 IF (c_lookup_value_doc%ISOPEN) THEN
1885 CLOSE c_lookup_value_doc;
1886 END IF;
1887
1888 END GetDisplayValue;
1889
1890 ------------------------------------------------------------------<BUG 3607009>
1891 -------------------------------------------------------------------------------
1892 --Start of Comments
1893 --Name: is_open_document_allowed
1894 --Pre-reqs:
1895 -- None.
1896 --Modifies:
1897 -- None
1898 --Locks:
1899 -- None.
1900 --Function:
1901 -- Determines if the Open Document icon should be shown in the
1902 -- PO Approval Notification. The Open Document should not be shown if...
1903 -- (1) the document is in 'Pre-Approved' state and
1904 -- (2) document signature is required.
1905 --Parameters:
1906 --IN:
1907 --p_itemtype
1908 -- Standard parameter to be used in a workflow procedure
1909 --p_itemkey
1910 -- Standard parameter to be used in a workflow procedure
1911 --p_notification_type
1912 -- Specifies whether this notification is for the Preparer/Submitter
1913 -- or an Approver/Reviewer
1914 -- The value is derived from the WF Function attribute NTF_USER_ROLE
1915 -- in the GET_<>_NOTIFICATION_ATTRIBUTE functions in POAPPRV workflow
1916 -- Added for bug 3668188
1917 --Returns:
1918 --resultout
1919 -- A BOOLEAN TRUE if the Open Document icon should be shown, FALSE otherwise.
1920 --Testing:
1921 -- N/A
1922 --End of Comments
1923 -------------------------------------------------------------------------------
1924 -------------------------------------------------------------------------------
1925 FUNCTION is_open_document_allowed
1926 (
1927 p_itemtype IN VARCHAR2
1928 , p_itemkey IN VARCHAR2
1929 , p_notification_type IN VARCHAR2 --bug 3668188
1930 )
1931 RETURN BOOLEAN
1932 IS
1933 l_api_name VARCHAR2(30) := 'is_open_document_allowed';
1934 l_log_head VARCHAR2(100) := g_pkg_name||'.'||l_api_name;
1935 l_progress VARCHAR2(3);
1936
1937 l_authorization_status PO_HEADERS_ALL.authorization_status%TYPE;
1938 l_result BOOLEAN := TRUE;
1939
1940 BEGIN
1941
1942 l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress || 'Notification Type = ' || p_notification_type);
1943
1944 -- Get the Authorization Status (e.g. 'PRE-APPROVED','APPROVED',
1945 -- 'INCOMPLETE', etc.) of the document.
1946 --
1947 l_authorization_status := wf_engine.GetItemAttrText
1948 ( itemtype => p_itemtype
1949 , itemkey => p_itemkey
1950 , aname => 'AUTHORIZATION_STATUS'
1951 );
1952
1953 l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Authorization Status = '||l_authorization_status);
1954
1955
1956 -- bug 3668188: changed the Open Doc allowed logic.
1957 -- If document is ('Pre-Approved' or 'In Process') and the
1958 -- notification is going back to the Submitter, then that
1959 -- user should not be able to open the document for edit.
1960 --
1961 IF (l_authorization_status IN ('PRE-APPROVED', 'IN PROCESS')
1962 AND nvl(p_notification_type, 'SUBMITTER') = 'SUBMITTER')
1963 THEN
1964 l_progress:='020';
1965 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||':FALSE');
1966 l_result := FALSE;
1967 END IF;
1968
1969 l_progress:='030';
1970 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||':TRUE');
1971 return (l_result);
1972
1973 EXCEPTION
1974
1975 WHEN OTHERS THEN
1976 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||SQLERRM);
1977 RAISE;
1978
1979 END is_open_document_allowed;
1980
1981
1982
1983 END PO_WF_PO_NOTIFICATION;