[Home] [Help]
PACKAGE BODY: APPS.PO_WF_PO_NOTIFICATION
Source
1 PACKAGE BODY PO_WF_PO_NOTIFICATION AS
2 /* $Header: POXWPA7B.pls 120.30.12020000.6 2013/03/16 14:04:59 inagdeo 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 --Added this private procedure as part of bug 13951919 fix
8 PROCEDURE update_action_history (p_action_code IN VARCHAR2,
9 p_recipient_id IN NUMBER,
10 p_note IN VARCHAR2,
11 p_po_header_id IN NUMBER,
12 p_current_id IN NUMBER,
13 p_doc_type IN po_action_history.OBJECT_TYPE_CODE%TYPE,
14 p_doc_subtype IN po_action_history.OBJECT_SUB_TYPE_CODE%TYPE,
15 p_approval_path_id IN po_action_history.APPROVAL_PATH_ID%TYPE, --<bug 14105414>
16 p_draft_id IN NUMBER,
17 p_draft_type IN VARCHAR2); -- PAR Approval
18
19 PROCEDURE get_po_approve_msg ( document_id in varchar2,
20 display_type in varchar2,
21 document in out NOCOPY varchar2,
22 document_type in out NOCOPY varchar2) IS
23
24 l_item_type wf_items.item_type%TYPE;
25 l_item_key wf_items.item_key%TYPE;
26
27 l_document_id po_headers.po_header_id%TYPE;
28 l_org_id po_headers.org_id%TYPE;
29 l_currency_code fnd_currencies.CURRENCY_CODE%TYPE;
30 l_header_msg VARCHAR2(500);
31 l_po_amount VARCHAR2(30);
32 l_tax_amount VARCHAR2(30);
33 --bug 12396408
34 l_po_amount_numeric NUMBER;
35 l_tax_amount_numeric NUMBER;
36 l_description po_headers.comments%TYPE;
37 l_forwarded_from per_all_people_f.full_name%TYPE;
38 l_preparer per_all_people_f.full_name%TYPE;
39 --<UTF-8 FPI START>
40 -- l_note VARCHAR2(480); /* < UTF8 FPI - changed from VARCHAR2(240) > */
41 l_note po_action_history.note%TYPE;
42 --<UTF-8 FPI END>
43 l_document VARCHAR2(32000) := '';
44 l_tax_amt NUMBER;
45
46 /* Start Bug# 3972475 */
47 X_precision number;
48 X_ext_precision number;
49 X_min_acct_unit number;
50 /* End Bug# 3972475*/
51 l_supplier po_vendors.vendor_name%type; --Bug 4254468
52 l_supplier_site po_vendor_sites_all.vendor_site_code%type; --Bug 4254468
53 NL VARCHAR2(1) := fnd_global.newline;
54
55 --Added by Eric Ma for IL PO Notification on Apr-13,2009 ,Begin
56 -------------------------------------------------------------------------------------
57 lv_tax_region varchar2(30); --tax region code
58 ln_jai_excl_nr_tax NUMBER; --exclusive non-recoverable tax
59 lv_progress VARCHAR2(255); --for saving the debug information
60 lv_document_type VARCHAR2(25); --document type
61 -------------------------------------------------------------------------------------
62 --Added by Eric Ma for IL PO Notification on Apr-13,2009 ,End
63 BEGIN
64
65 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
66 l_item_key := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
67
68 l_document_id := wf_engine.GetItemAttrNumber
69 (itemtype => l_item_type,
70 itemkey => l_item_key,
71 aname => 'DOCUMENT_ID');
72
73 l_org_id := wf_engine.GetItemAttrNumber
74 (itemtype => l_item_type,
75 itemkey => l_item_key,
76 aname => 'ORG_ID');
77
78 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
79
80
81 l_currency_code := wf_engine.GetItemAttrText
82 (itemtype => l_item_type,
83 itemkey => l_item_key,
84 aname => 'FUNCTIONAL_CURRENCY');
85
86 l_po_amount := wf_engine.GetItemAttrText
87 (itemtype => l_item_type,
88 itemkey => l_item_key,
89 aname => 'PO_AMOUNT_DSP');
90
91 l_tax_amount := wf_engine.GetItemAttrText
92 (itemtype => l_item_type,
93 itemkey => l_item_key,
94 aname => 'TAX_AMOUNT_DSP');
95 -- bug 12396408
96 l_po_amount_numeric := wf_engine.GetItemAttrNumber
97 (itemtype => l_item_type,
98 itemkey => l_item_key,
99 aname => 'PO_AMOUNT_DSP_NUMERIC');
100
101 l_tax_amount_numeric := wf_engine.GetItemAttrNumber
102 (itemtype => l_item_type,
103 itemkey => l_item_key,
104 aname => 'TAX_AMOUNT_DSP_NUMERIC');
105
106 l_description := wf_engine.GetItemAttrText
107 (itemtype => l_item_type,
108 itemkey => l_item_key,
109 aname => 'PO_DESCRIPTION');
110
111 l_forwarded_from := wf_engine.GetItemAttrText
112 (itemtype => l_item_type,
113 itemkey => l_item_key,
114 aname => 'FORWARD_FROM_DISP_NAME');
115
116 l_preparer := wf_engine.GetItemAttrText
117 (itemtype => l_item_type,
118 itemkey => l_item_key,
119 aname => 'PREPARER_DISPLAY_NAME');
120
121 l_note := wf_engine.GetItemAttrText
122 (itemtype => l_item_type,
123 itemkey => l_item_key,
124 aname => 'NOTE');
125
126 --<Bug 4254468 Start> Show supplier and supplier site for
127 -- approval notifications
128 l_supplier := PO_WF_UTIL_PKG.GetItemAttrText
129 (itemtype => l_item_type,
130 itemkey => l_item_key,
131 aname => 'SUPPLIER');
132
133 l_supplier_site := PO_WF_UTIL_PKG.GetItemAttrText
134 (itemtype => l_item_type,
135 itemkey => l_item_key,
136 aname => 'SUPPLIER_SITE');
137 --<Bug 4254468 End>
138
139 /*Start Bug# 3972475 - replaced the below sql to get the tax amount
140 to account for canceled QTY. Also accounted for new order types introduced
141 in 11i10 that use amount instead of quantity (where quantity_ordered is null).
142
143 Since we are performing divide and multiply by operations we need rounding
144 logic based on the currency.
145
146 If we are using minimum accountable unit we apply:
147 rounded tax = round(tax/mau)*mau, otherwise
148 rounded tax = round(tax, precision)
149
150 Old tax select:
151 SELECT nvl(sum(nonrecoverable_tax), 0)
152 INTO l_tax_amt
153 FROM po_lines pol,
154 po_distributions pod
155 WHERE pol.po_header_id = l_document_id
156 AND pod.po_line_id = pol.po_line_id;
157 */
158
159 --Modified by Eric Ma for IL po workflow notification on Apr-13,2009 ,Begin
160 -------------------------------------------------------------------------------------
161 lv_tax_region := JAI_PO_WF_UTIL_PUB.Get_Tax_Region (pn_org_id => l_org_id);
162
163 IF (lv_tax_region ='JAI')
164 THEN
165 --Get document type
166 lv_document_type := wf_engine.GetItemAttrText
167 ( itemtype => l_item_type
168 , itemkey => l_item_key
169 , aname => 'DOCUMENT_TYPE'
170 );
171
172 --Indian localization tax calculation
173 IF lv_document_type = 'RELEASE'
174 THEN
175 JAI_PO_WF_UTIL_PUB.Get_Jai_Tax_Amount
176 ( pv_document_type => JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE
177 , pn_document_id => l_document_id
178 , xn_excl_tax_amount => l_tax_amt
179 , xn_excl_nr_tax_amount => ln_jai_excl_nr_tax
180 );
181
182 ELSE
183 JAI_PO_WF_UTIL_PUB.Get_Jai_Tax_Amount
184 ( pv_document_type => JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE
185 , pn_document_id => l_document_id
186 , xn_excl_tax_amount => l_tax_amt
187 , xn_excl_nr_tax_amount => ln_jai_excl_nr_tax
188 );
189 END IF; --(lv_document_type = 'RELEASE')
190 ELSE
191 --original tax calc code
192
193 fnd_currency.get_info( l_currency_code,
194 X_precision,
195 X_ext_precision,
196 X_min_acct_unit);
197
198
199 IF (x_min_acct_unit IS NOT NULL) AND
200 (x_min_acct_unit <> 0)
201 THEN
202 SELECT sum( round (POD.nonrecoverable_tax *
203 decode(quantity_ordered,
204 NULL,
205 --Bug16222308 Handling the quantity zero on distribution
206 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ) ,
207 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / decode ( nvl(POD.quantity_ordered, 1),0,1, nvl(POD.quantity_ordered, 1) )
208 ) / X_min_acct_unit
209 ) * X_min_acct_unit
210 )
211 INTO l_tax_amt
212 FROM po_lines pol,
213 po_distributions pod
214 WHERE pol.po_header_id = l_document_id
215 AND pod.po_line_id = pol.po_line_id;
216 ELSE
217 SELECT sum( round (POD.nonrecoverable_tax *
218 decode(quantity_ordered,
219 NULL,
220 --Bug16222308 Handling the quantity zero on distribution
221 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ) ,
222 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / decode ( nvl(POD.quantity_ordered, 1),0,1, nvl(POD.quantity_ordered, 1) )
223 ),
224 X_precision
225 )
226 )
227 INTO l_tax_amt
228 FROM po_lines pol,
229 po_distributions pod
230 WHERE pol.po_header_id = l_document_id
231 AND pod.po_line_id = pol.po_line_id;
232 END IF;
233 END IF;--(lv_tax_region ='JAI')
234 -------------------------------------------------------------------------------------
235 --Modified by Eric Ma for IL po workflow notification on Apr-13,2009 ,End
236
237 if (display_type = 'text/html') then
238
239 l_document := NL || NL || '<!-- PO_APPROVE_MSG -->'|| NL || NL || '<P>';
240
241 l_document := l_document || '</P>' || NL;
242
243 --Bug 9067919, which is the entity used to represent a non-breaking
244 --space had the semi-colon missing. Replaced all occurences with the correct
245 --syntax instead of
246
247 l_document := l_document || '<P><TABLE border=0 cellpadding=0 cellspacing=0 SUMMARY=""><TR><TD align=right >' || NL ||
248 fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_AMOUNT') ||
249 ' </TD>' || NL;
250
251 -- 12396408 Formatting the total amount
252 l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || nvl(TO_CHAR(l_po_amount_numeric, FND_CURRENCY.GET_FORMAT_MASK(
253 l_currency_code, 30)),' ') || '</TD></TR>' || NL;
254
255 if l_tax_amt > 0 then
256
257 l_document := l_document || '<TR><TD align=right>' ||
258 fnd_message.get_string('PO', 'PO_WF_NOTIF_TAX_AMOUNT') ||
259 ' </TD>' || NL;
260 --bug 12396408 formatting the tax
261 l_document := l_document || '<TD align=left>' || l_currency_code || ' ' || nvl(TO_CHAR(l_tax_amount_numeric , FND_CURRENCY.GET_FORMAT_MASK(
262 l_currency_code, 30)),' ') ||
263 '</TD></TR></TABLE></P>' || NL;
264
265 else
266
267 l_document := l_document || '</TABLE></P>' || NL || NL;
268
269 end if;
270
271 --<Bug 4254468 Start> Show supplier and supplier site for
272 -- approval notifications
273 l_document := l_document || '<P>' || NL;
274 l_document := l_document || fnd_message.get_string('PO', 'PO_FO_VENDOR') ||
275 ' '|| l_supplier || NL;
276 l_document := l_document || '<BR>' || NL;
277 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SUPPLIER_SITE') ||
278 ' '|| l_supplier_site || NL;
279 l_document := l_document || '</P>' || NL;
280 --<Bug 4254468 End>
281
282 if l_description is not null then
283 l_document := l_document || '<P>' || NL;
284 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DOC_DESCRIPTION') || NL || '<BR>';
285 l_document := l_document || l_description;
286 l_document := l_document || '<BR></P>' || NL;
287 end if;
288
289 else -- plain text notification is defined in the WF.
290
291 null;
292
293 end if;
294
295 document := l_document;
296
297 END;
298
299
300 PROCEDURE get_po_lines_details ( document_id in varchar2,
301 display_type in varchar2,
302 document in out NOCOPY CLOB, -- <BUG 7006113>
303 document_type in out NOCOPY varchar2) IS
304
305 l_item_type wf_items.item_type%TYPE;
306 l_item_key wf_items.item_key%TYPE;
307
308 l_document_id po_lines.po_header_id%TYPE;
309 l_org_id po_lines.org_id%TYPE;
310 l_document_type VARCHAR2(25);
311
312 l_document VARCHAR2(32000) := '';
313
314 l_currency_code fnd_currencies.currency_code%TYPE;
315
316 -- Bug 3668188: added new local var. note: the length of this
317 -- varchar was determined based on the length in POXWPA1B.pls,
318 -- which is the other place 'OPEN_FORM_COMMAND' attribute is used
319
320 l_open_form_command VARCHAR2(200);
321 l_view_po_url varchar2(1000); -- HTML Orders R12
322 l_edit_po_url varchar2(1000); -- HTML Orders R12
323
324 NL VARCHAR2(1) := fnd_global.newline;
325
326 i NUMBER := 0;
327 max_lines_dsp NUMBER ; -- <BUG 7006113>
328 l_line_count NUMBER := 0; -- <BUG 3616816> # lines/shipments on document
329 line_mesg fnd_new_messages.message_text%TYPE; --Bug 4695601
330 l_num_records_to_display NUMBER; -- <BUG 3616816> actual # of records to be displayed in table
331
332 -- <BUG 7006113 START>
333 curr_len NUMBER := 0; --<BUG 7614278 Reverting back the commented variable>
334 -- prior_len NUMBER := 0;
335 -- <BUG 7006113 END>
336
337 -- po lines cursor
338
339 -- <BUG 3616816 START> Declare TABLEs for each column that is selected
340 -- from po_line_csr and po_line_loc_csr.
341 --
342 TYPE line_num_tbl_type IS TABLE OF PO_LINES.line_num%TYPE;
343 TYPE shipment_num_tbl_type IS TABLE OF PO_LINE_LOCATIONS.shipment_num%TYPE;
344 TYPE item_num_tbl_type IS TABLE OF MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
345 TYPE item_revision_tbl_type IS TABLE OF PO_LINES.item_revision%TYPE;
346 TYPE item_desc_tbl_type IS TABLE OF PO_LINES.item_description%TYPE;
347 TYPE uom_tbl_type IS TABLE OF MTL_UNITS_OF_MEASURE.unit_of_measure_tl%TYPE;
348 TYPE quantity_tbl_type IS TABLE OF PO_LINES.quantity%TYPE;
349 TYPE unit_price_tbl_type IS TABLE OF PO_LINES.unit_price%TYPE;
350 TYPE amount_tbl_type IS TABLE OF PO_LINES.amount%TYPE;
351 TYPE location_tbl_type IS TABLE OF HR_LOCATIONS.location_code%TYPE;
352 TYPE organization_name_tbl_type IS TABLE OF ORG_ORGANIZATION_DEFINITIONS.organization_name%TYPE;
353 TYPE need_by_date_tbl_type IS TABLE OF PO_LINE_LOCATIONS.need_by_date%TYPE;
354 TYPE promised_date_tbl_type IS TABLE OF PO_LINE_LOCATIONS.promised_date%TYPE;
355 TYPE shipment_type_tbl_type IS TABLE OF PO_LINE_LOCATIONS.shipment_type%TYPE;
356 /* CLM Changes Start */
357 TYPE clm_option_num_tbl_type IS TABLE OF PO_LINES.CLM_OPTION_NUM%TYPE;
358 TYPE CLM_BASE_LINE_NUM_tbl_type IS TABLE OF po_lines.LINE_NUM_DISPLAY%TYPE;
359 TYPE CONTRACT_TYPE_tbl_type is table of po_lookup_codes.DESCRIPTION%TYPE;
360 TYPE LINE_NUM_DISPLAY_tbl_type is table of po_lines.line_num_display%type;
361 /* CLM changes End */
362
363 l_line_num_tbl line_num_tbl_type;
364 l_shipment_num_tbl shipment_num_tbl_type;
365 l_item_num_tbl item_num_tbl_type;
366 l_item_revision_tbl item_revision_tbl_type;
367 l_item_desc_tbl item_desc_tbl_type;
368 l_uom_tbl uom_tbl_type;
369 l_quantity_tbl quantity_tbl_type;
370 l_unit_price_tbl unit_price_tbl_type;
371 l_amount_tbl amount_tbl_type;
372 l_location_tbl location_tbl_type;
373 l_org_name_tbl organization_name_tbl_type;
374 l_need_by_date_tbl need_by_date_tbl_type;
375 l_promised_date_tbl promised_date_tbl_type;
376 l_shipment_type_tbl shipment_type_tbl_type;
377
378 --
379 /* CLM Changes Start */
380 l_clm_option_num_tbl clm_option_num_tbl_type;
381 l_CLM_BASE_LINE_NUM_tbl CLM_BASE_LINE_NUM_tbl_type;
382 l_contract_type_tbl CONTRACT_TYPE_tbl_type;
383 l_line_num_display_tbl LINE_NUM_DISPLAY_tbl_type;
384 l_clm_document VARCHAR2(1) := 'N'; -- Whether its a CLM document or Not
385 /* CLM Changes End */
386 --CLM apprvl
387 l_draft_id NUMBER;
388 -- <BUG 3616816 END>
389
390 /* Bug# 1419139: kagarwal
391 ** Desc: The where clause pol.org_id = msi.organization_id(+) in the
392 ** PO lines cursor, po_line_csr, is not correct as the pol.org_id
393 ** is the operating unit which is not the same as the inventory
394 ** organization_id.
395 **
396 ** We need to use the financials_system_parameter table for the
397 ** inventory organization_id.
398 **
399 ** Also did the similar changes for the Release cursor,po_line_loc_csr.
400 */
401
402 /* Bug 2401933: sktiwari
403 Modifying cursor po_line_csr to return the translated UOM value
404 instead of unit_meas_lookup_code.
405 */
406
407 CURSOR po_line_csr(v_document_id NUMBER) IS
408 SELECT pol.line_num,
409 msi.concatenated_segments,
410 pol.item_revision,
411 pol.item_description,
412 -- pol.unit_meas_lookup_code, -- bug 2401933.remove
413 nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code), -- bug 2401933.add
414 pol.quantity,
415 pol.unit_price,
416 nvl(pol.amount, pol.quantity * pol.unit_price),
417 pol.CLM_OPTION_NUM,
418 po2.LINE_NUM_DISPLAY --Base line num
419 -- ,pol.contract_type
420 ,lkp1.description
421 ,pol.LINE_NUM_DISPLAY
422 FROM po_lines_all pol, --CLM Apprvl
423 po_lines_all po2, -- CLM Apprvl
424 po_lookup_codes lkp1,
425 mtl_system_items_kfv msi,
426 mtl_units_of_measure muom, -- bug 2401933.add
427 financials_system_parameters fsp
428 WHERE pol.po_header_id = v_document_id
429 AND lkp1.lookup_code(+) = pol.contract_type
430 AND lkp1.lookup_type(+) = decode(pol.order_type_lookup_code,
431 'QUANTITY', 'PO_FEDERAL_CONTRACT_TYPES_QTY',
432 'PO_FEDERAL_CONTRACT_TYPES_AMT')
433 AND pol.item_id = msi.inventory_item_id(+)
434 AND NVL(msi.organization_id, fsp.inventory_organization_id) =
435 fsp.inventory_organization_id
436 /* Bug 2299484 fixed. prevented the canceled lines to be displayed
437 in notifications.
438 */
439 AND NVL(pol.cancel_flag,'N') = 'N'
440 AND muom.unit_of_measure (+) = pol.unit_meas_lookup_code -- bug 2401933.add
441 AND pol.CLM_BASE_LINE_NUM = po2.PO_LINE_ID(+)
442 ORDER BY pol.LINE_NUM_DISPLAY;
443
444 -- release shipments cursor
445
446 /* Bug# 1530303: kagarwal
447 ** Desc: We need to change the where clause as the item
448 ** may not be an inventory item. For this case we should
449 ** have an outer join with the mtl_system_items_kfv.
450 **
451 ** Changed the condition:
452 ** pol.item_id = msi.inventory_item_id
453 ** to pol.item_id = msi.inventory_item_id(+)
454 **
455 */
456
457 /* Bug# 1718725: kagarwal
458 ** Desc: The unit of measure may be null at the shipment level
459 ** hence in this case we need to get the uom from line level.
460 **
461 ** Changed nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
462 */
463 /* Bug# 1770951: kagarwal
464 ** Desc: For Releases we should consider the price_override on the shipments
465 ** and not the price on the Blanket PO line as the shipment price could be
466 ** different if the price override is enabled on the Blanket.
467 */
468
469 /* Bug 2401933: sktiwari
470 Modifying cursor po_line_loc_csr to return the translated UOM value
471 instead of unit_meas_lookup_code.
472 */
473 /* CLM apprvl start - for modification documents only the attributes that are chnaged from the original documents needs to be displayed.*/
474 CURSOR mod_line_csr(v_document_id NUMBER, v_draft_id NUMBER) IS
475 SELECT pol.line_num,
476 msi.concatenated_segments,
477 pol.item_revision,
478 pol.item_description,
479 -- pol.unit_meas_lookup_code, -- bug 2401933.remove
480 nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code), -- bug 2401933.add
481 pol.quantity,
482 pol.unit_price,
483 nvl(pol.amount, pol.quantity * pol.unit_price),
484 pol.CLM_OPTION_NUM,
485 po2.LINE_NUM_DISPLAY --Base line num
486 -- ,pol.contract_type
487 ,lkp1.description
488 ,pol.LINE_NUM_DISPLAY
489 FROM po_lines_draft_all pol, --CLM Apprvl
490 po_lines_draft_all po2, -- CLM Apprvl
491 po_lookup_codes lkp1,
492 mtl_system_items_kfv msi,
493 mtl_units_of_measure muom, -- bug 2401933.add
494 financials_system_parameters fsp
495 WHERE pol.po_header_id = v_document_id
496 AND nvl(pol.draft_id, -1) = nvl(v_draft_id, -1)
497 AND lkp1.lookup_code(+) = pol.contract_type
498 AND lkp1.lookup_type(+) = decode(pol.order_type_lookup_code,
499 'QUANTITY', 'PO_FEDERAL_CONTRACT_TYPES_QTY',
500 'PO_FEDERAL_CONTRACT_TYPES_AMT')
501 AND pol.item_id = msi.inventory_item_id(+)
502 AND NVL(msi.organization_id, fsp.inventory_organization_id) =
503 fsp.inventory_organization_id
504 /* Bug 2299484 fixed. prevented the canceled lines to be displayed
505 in notifications.
506 */
507 AND NVL(pol.cancel_flag,'N') = 'N'
508 AND muom.unit_of_measure (+) = pol.unit_meas_lookup_code -- bug 2401933.add
509 AND pol.CLM_BASE_LINE_NUM = po2.PO_LINE_ID(+)
510 ORDER BY pol.LINE_NUM_DISPLAY;
511 /*CLM Apprvl end*/
512
513 CURSOR po_line_loc_csr(v_document_id NUMBER) IS
514 SELECT pll.shipment_num,
515 msi.concatenated_segments,
516 pol.item_revision,
517 pol.item_description,
518 -- Bug 2401933.start
519 -- nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
520 -- unit_meas_lookup_code,
521 nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code),
522 -- Bug 2401933.end
523 pll.quantity,
524 nvl(pll.price_override, pol.unit_price) unit_price,
525 hrl.location_code,
526 ood.organization_name,
527 pll.need_by_date,
528 pll.promised_date,
529 pll.shipment_type,
530 --Bug 4950850 Added pll.amount
531 --Bug 5563024 AMOUNT NOT SHOWN FOR A RELEASE SHIPMENT IN APPROVAL NOTIFICATION.
532 nvl(pll.amount, nvl(pll.price_override, pol.unit_price) * pll.quantity)
533 FROM po_lines_all pol, -- CLM apprvl
534 po_line_locations_all pll, -- CLM apprvl
535 mtl_system_items_kfv msi,
536 hr_locations_all hrl,
537 hz_locations hz,
538 org_organization_definitions ood,
539 mtl_units_of_measure muom, -- Bug 2401933.add
540 financials_system_parameters fsp
541 where PLL.PO_RELEASE_ID = v_document_id
542 and PLL.po_line_id = POL.po_line_id
543 and PLL.ship_to_location_id = HRL.location_id (+)
544 and PLL.ship_to_location_id = HZ.location_id (+)
545 and PLL.ship_to_organization_id = OOD.organization_id
546 and pol.item_id = msi.inventory_item_id(+)
547 and NVL(msi.organization_id, fsp.inventory_organization_id) =
548 fsp.inventory_organization_id
549 /* Bug 2299484 fixed. prevented the canceled shipments to be displayed
550 in notifications.
551 */
552 AND NVL(PLL.cancel_flag,'N') = 'N'
553 AND muom.unit_of_measure (+) = pol.unit_meas_lookup_code -- Bug 2401933.add
554 order by Shipment_num asc;
555
556
557
558 BEGIN
559
560 if document is null then
561 document := ' ';
562 end if;
563
564 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
565 l_item_key := substr(document_id, instr(document_id, ':') + 1,
566 length(document_id) - 2);
567
568 /* Bug# 2353153
569 ** Setting application context
570 */
571
572 PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(l_item_type, l_item_key);
573
574 l_document_id := wf_engine.GetItemAttrNumber (itemtype => l_item_type,
575 itemkey => l_item_key,
576 aname => 'DOCUMENT_ID');
577
578 l_org_id := wf_engine.GetItemAttrNumber (itemtype => l_item_type,
579 itemkey => l_item_key,
580 aname => 'ORG_ID');
581
582 l_document_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
583 itemkey => l_item_key,
584 aname => 'DOCUMENT_TYPE');
585
586 -- CLM Apprvl
587 l_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => l_item_type,
588 itemkey => l_item_key,
589 aname => 'DRAFT_ID');
590
591 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
592
593
594 /* Bug# 1686066: kagarwal
595 ** Desc: Use the functional currency of the PO for the precision of
596 ** line amounts.
597 */
598
599 l_currency_code := wf_engine.GetItemAttrText
600 (itemtype => l_item_type,
601 itemkey => l_item_key,
602 aname => 'FUNCTIONAL_CURRENCY');
603
604
605 -- Bug 3668188
606 l_open_form_command := PO_WF_UTIL_PKG.GetItemAttrText
607 (itemtype => l_item_type,
608 itemkey => l_item_key,
609 aname => 'OPEN_FORM_COMMAND');
610
611 -- HTML Orders R12
612 -- Get the PO HTML Page URL's
613 l_view_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
614 itemtype => l_item_type,
615 itemkey => l_item_key,
616 aname => 'VIEW_DOC_URL');
617
618 l_edit_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
619 itemtype => l_item_type,
620 itemkey => l_item_key,
621 aname => 'EDIT_DOC_URL');
622
623 -- CLM Aprvl
624 Begin
625 SELECT 'Y' into l_clm_document
626 FROM po_headers_all h,
627 po_doc_style_headers s
628 WHERE h.po_header_id = l_document_id
629 AND h.style_id = s.style_id
630 AND Nvl(s.clm_flag,'N') = 'Y';
631 Exception
632 When No_Data_Found Then
633 l_clm_document := 'N';
634 End;
635 -- CLM Aprvl
636
637
638 /* Bug# 2668222: kagarwal
639 ** Desc: Using profile PO_NOTIF_LINES_LIMIT to get the maximum
640 ** number of PO lines to be displayed in Approval notification.
641 ** The same profile is also used for Requisitions.
642 */
643 -- <BUG 7006113 START Moved this code to the later section of the procedure >
644 -- max_lines_dsp:= to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
645
646 -- if max_lines_dsp is NULL then
647 -- max_lines_dsp := 20;
648 -- end if;
649 -- <BUG 7006113 END>
650
651 -- <BUG 3616816 START> Fetch Release Shipments/PO Lines data into Tables.
652 --
653 IF ( l_document_type = 'RELEASE' ) THEN
654
655 OPEN po_line_loc_csr(l_document_id);
656
657 FETCH po_line_loc_csr BULK COLLECT INTO l_shipment_num_tbl
658 , l_item_num_tbl
659 , l_item_revision_tbl
660 , l_item_desc_tbl
661 , l_uom_tbl
662 , l_quantity_tbl
663 , l_unit_price_tbl
664 , l_location_tbl
665 , l_org_name_tbl
666 , l_need_by_date_tbl
667 , l_promised_date_tbl
668 , l_shipment_type_tbl
669 , l_amount_tbl; --bug 4950850
670
671
672 l_line_count := po_line_loc_csr%ROWCOUNT; -- Get # of records fetched.
673
674 CLOSE po_line_loc_csr;
675
676 ELSE
677 -- CLM Apprvl. In case of modification document the mod_line_csr needs to opened
678 IF l_draft_id is not null AND l_draft_id <> -1 THEN
679 OPEN mod_line_csr(l_document_id, l_draft_id); -- CLM Apprvl
680
681 FETCH mod_line_csr BULK COLLECT INTO l_line_num_tbl
682 , l_item_num_tbl
683 , l_item_revision_tbl
684 , l_item_desc_tbl
685 , l_uom_tbl
686 , l_quantity_tbl
687 , l_unit_price_tbl
688 , l_amount_tbl
689 , l_clm_option_num_tbl
690 , l_CLM_BASE_LINE_NUM_tbl
691 , l_contract_type_tbl
692 , l_line_num_display_tbl;
693
694 l_line_count := mod_line_csr%ROWCOUNT; -- Get # of records fetched.
695 ELSE
696 OPEN po_line_csr(l_document_id);
697
698 FETCH po_line_csr BULK COLLECT INTO l_line_num_tbl
699 , l_item_num_tbl
700 , l_item_revision_tbl
701 , l_item_desc_tbl
702 , l_uom_tbl
703 , l_quantity_tbl
704 , l_unit_price_tbl
705 , l_amount_tbl
706 , l_clm_option_num_tbl
707 , l_CLM_BASE_LINE_NUM_tbl
708 , l_contract_type_tbl
709 , l_line_num_display_tbl;
710
711 l_line_count := po_line_csr%ROWCOUNT; -- Get # of records fetched.
712
713 CLOSE po_line_csr;
714 END IF;
715
716 END IF;
717 --
718 -- <BUG 3616816 END>
719
720 max_lines_dsp:= to_number(fnd_profile.value('PO_NOTIF_LINES_LIMIT'));
721
722 IF max_lines_dsp IS NULL THEN
723
724 max_lines_dsp := l_line_count;
725
726 END IF;
727
728 -- <BUG 3616816 START> Determine the actual number of records to display
729 -- in the table.
730 --
731 IF ( l_line_count >= max_lines_dsp )
732 THEN
733 l_num_records_to_display := max_lines_dsp;
734 ELSE
735 l_num_records_to_display := l_line_count;
736 END IF;
737 --
738 -- <BUG 3616816 END>
739
740 if (display_type = 'text/html') then
741
742 if (nvl(l_document_type, 'PO') <> 'RELEASE') then
743
744 l_document := NL || NL || '<!-- PO_LINE_DETAILS -->'|| NL || NL || '<P><B>';
745 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_LINE_DETAILS');
746 l_document := l_document || '</B>' || NL || '<P>'; -- <BUG 3616816>
747
748 -- <BUG 3616816 START> Only display message if # of actual lines is
749 -- greater than maximum limit.
750 --
751 IF ( l_line_count > max_lines_dsp ) THEN
752
753 -- Bug 3668188: changed the code check (originally created
754 -- in bug 3607009) that determines which message to show
755 -- based on whether Open Document icon is shown in the notif.
756 -- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
757 -- previous node, using the get_po_user_msg_attribute procedure.
758 --
759 -- HTML Orders R12
760 -- Check for the URL parameters as well
761 IF (l_open_form_command IS NULL ) AND
762 (l_view_po_url IS NULL ) AND
763 (l_edit_po_url IS NULL )
764 THEN
765 -- "The first [COUNT] Purchase Order lines are summarized below."
766 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG_TRUNC');
767 ELSE
768 -- "The first [COUNT] Purchase Order lines are summarized
769 -- below. For information on additional lines, please click
770 -- the Open Document icon."
771 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG');
772 END IF;
773
774 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
775 line_mesg := FND_MESSAGE.get;
776 l_document := l_document || line_mesg || '<P>';
777
778
779 END IF;
780 --
781 -- <BUG 3616816 END>
782
783 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;
784
785 l_document := l_document || '<TR>' || NL;
786
787 IF (l_clm_document='Y') THEN
788 l_document := l_document || '<TH id="lineNumDisp_1">' ||
789 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
790 else
791 l_document := l_document || '<TH id="lineNum_1">' ||
792 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || '</TH>' || NL;
793 end if;
794
795 IF (l_clm_document='Y') THEN
796 l_document := l_document || '<TH id="BaselineNum_1">' ||
797 fnd_message.get_string('PO', 'PO_WF_NOTIF_BASE_LINE_NUMBER') || '</TH>' || NL;
798
799 l_document := l_document || '<TH id="OptionlineNum_1">' ||
800 fnd_message.get_string('PO', 'PO_WF_NOTIF_OPTION_NUMBER') || '</TH>' || NL;
801 END IF;
802
803
804 l_document := l_document || '<TH id="itemNum_1">' ||
805 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || '</TH>' || NL;
806
807 l_document := l_document || '<TH id="itemRev_1">' ||
808 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || '</TH>' || NL;
809
810 l_document := l_document || '<TH id="itemDesc_1">' ||
811 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TH>' || NL;
812
813 IF (l_clm_document='Y') THEN
814 l_document := l_document || '<TH id="ContractType_1">' ||
815 fnd_message.get_string('PO', 'PO_WF_NOTIF_CONTRACT_TYPE') || '</TH>' || NL;
816 END IF;
817
818 l_document := l_document || '<TH id="uom_1">' ||
819 fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TH>' || NL;
820
821 l_document := l_document || '<TH id="quant_1">' ||
822 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
823
824
825 l_document := l_document || '<TH id="unitPrice_1">' ||
826 fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TH>' || NL;
827
828 l_document := l_document || '<TH id="lineAmt_1">' ||
829 fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || '</TH>' || NL;
830
831 l_document := l_document || '</TR>' || NL;
832
833 -- curr_len := lengthb(l_document);
834 -- prior_len := curr_len;
835
836 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
837
838 /* Exit the cursor if the current document length and 2 times the
839 ** length added in prior line exceeds 32000 char */
840 -- < BUG 7006113 START Commented the loop to avoid the check so that maximum
841 -- lines can be displayed >
842 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
843 -- exit;
844 -- end if;
845 -- prior_len := curr_len;
846 -- < BUG 7006113 END >
847
848 l_document := l_document || '<TR>' || NL;
849 IF (l_clm_document='Y') THEN
850 l_document := l_document || '<TD nowrap align=center headers="lineNumDisp_1">'
851 || nvl(l_line_num_display_tbl(i), ' ') || '</TD>' || NL;
852 else
853 l_document := l_document || '<TD nowrap align=center headers="lineNum_1">'
854 || nvl(to_char(l_line_num_tbl(i)), ' ') || '</TD>' || NL;
855 end if;
856
857 IF (l_clm_document='Y') THEN
858 l_document := l_document || '<TD nowrap align=center headers="BaselineNum_1">'
859 || nvl(to_char(l_CLM_BASE_LINE_NUM_tbl(i)), ' ') || '</TD>' || NL;
860
861 l_document := l_document || '<TD nowrap align=center headers="OptionlineNum_1">'
862 || nvl(to_char(l_clm_option_num_tbl(i)), ' ') || '</TD>' || NL;
863 END IF;
864
865 l_document := l_document || '<TD nowrap headers="itemNum_1">'
866 || nvl(l_item_num_tbl(i), ' ') || '</TD>' || NL;
867 l_document := l_document || '<TD nowrap headers="itemRev_1">'
868 || nvl(l_item_revision_tbl(i), ' ') || '</TD>' || NL;
869 /* Bug 11825584 removing nowrap and adding align=left*/
870 l_document := l_document || '<TD align=left headers="itemDesc_1">'
871 || nvl(l_item_desc_tbl(i), ' ') || '</TD>' || NL;
872
873 IF (l_clm_document='Y') THEN
874 l_document := l_document || '<TD nowrap headers="ContractType_1">'
875 || nvl(l_contract_type_tbl(i), ' ') || '</TD>' || NL;
876 END IF;
877
878 l_document := l_document || '<TD nowrap headers="uom_1">'
879 || nvl(l_uom_tbl(i), ' ') || '</TD>' || NL;
880 l_document := l_document || '<TD nowrap align=right headers="quant_1">'
881 || nvl(to_char(l_quantity_tbl(i)), ' ') || '</TD>' || NL;
882
883
884
885 /* Bug 2868931: kagarwal
886 ** We will not format the unit price on the lines in notifications
887 */
888 -- Bug 3547777. Added the nvl clauses to unit_price and line_
889 -- amount so that box is still displayed even if value is null.
890 l_document := l_document || '<TD nowrap align=right headers="unitPrice_1">' ||
891 nvl(PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_currency_code,l_unit_price_tbl(i)),' ') || '</TD>' || NL; -- <BUG 7006113>
892
893 l_document := l_document || '<TD nowrap align=right headers="lineAmt_1">' ||
894 nvl(TO_CHAR(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(
895 l_currency_code, 30)),' ') || '</TD>' || NL;
896
897 l_document := l_document || '</TR>' || NL;
898
899 -- <BUG 7006113 START>
900 --curr_len := lengthb(l_document);
901
902 wf_notification.writetoclob(document, l_document);
903
904 l_document := NULL;
905
906 EXIT WHEN i = l_num_records_to_display;
907 -- <BUG 7006113 END>
908 end loop;
909
910 else -- release
911
912 l_document := NL || NL || '<!-- RELEASE_SHIPMENT_DETAILS -->'|| NL || NL || '<P><B>';
913 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_DETAILS');
914 l_document := l_document || '</B>' || NL || '<P>';
915
916 -- <BUG 3616816 START> Only display message if # of actual lines is
917 -- greater than maximum limit.
918 --
919 IF ( l_line_count > max_lines_dsp )
920 THEN
921 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_REL_SHIP_MESG');
922 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
923 line_mesg := FND_MESSAGE.get;
924 l_document := l_document || line_mesg || '<P>';
925 END IF;
926 --
927 -- <BUG 3616816 END>
928
929 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;
930
931 l_document := l_document || '<TR>' || NL;
932
933 l_document := l_document || '<TH id="shipNum_2">' ||
934 fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_NUMBER') || '</TH>' || NL;
935
936 l_document := l_document || '<TH id="itemNum_2">' ||
937 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || '</TH>' || NL;
938
939 l_document := l_document || '<TH id="itemRev_2">' ||
940 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || '</TH>' || NL;
941
942 l_document := l_document || '<TH id="itemDesc_2">' ||
943 fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || '</TH>' || NL;
944
945 l_document := l_document || '<TH id="uom_2">' ||
946 fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || '</TH>' || NL;
947
948 l_document := l_document || '<TH id="quant_2">' ||
949 fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || '</TH>' || NL;
950
951 l_document := l_document || '<TH id="unitPrice_2">' ||
952 fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || '</TH>' || NL;
953
954 l_document := l_document || '<TH id="location_2">' ||
955 fnd_message.get_string('PO', 'PO_WF_NOTIF_LOCATION') || '</TH>' || NL;
956
957 l_document := l_document || '<TH id="shipToOrg_2">' ||
958 fnd_message.get_string('PO', 'POA_SHIP_TO_ORG') || '</TH>' || NL;
959
960 l_document := l_document || '<TH id="needByDate_2">' ||
961 fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY_DATE') || '</TH>' || NL;
962 /* bug 4950850 */
963 l_document := l_document || '<TH id="lineAmt_2">' ||
964 fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') || '</TH>' || NL;
965
966 l_document := l_document || '</TR>' || NL;
967
968 -- curr_len := lengthb(l_document);
969 -- prior_len := curr_len;
970
971 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
972
973 /* Exit the cursor if the current document length and 2 times the
974 ** length added in prior line exceeds 32000 char */
975 -- < BUG 7006113 START Commented the loop to avoid the check so that
976 -- maximum lines can be displayed >
977 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
978 -- exit;
979 -- end if;
980 -- prior_len := curr_len;
981 -- < BUG 7006113 END >
982
983 l_document := l_document || '<TR>' || NL;
984
985 l_document := l_document || '<TD nowrap align=center headers="shipNum_2">'
986 || nvl(to_char(l_shipment_num_tbl(i)), ' ') || '</TD>' || NL;
987 l_document := l_document || '<TD nowrap headers="itemNum_2">'
988 || nvl(l_item_num_tbl(i), ' ') || '</TD>' || NL;
989 l_document := l_document || '<TD nowrap headers="itemRev_2">'
990 || nvl(l_item_revision_tbl(i), ' ') || '</TD>' || NL;
991 /* Bug 11825584 removing nowrap and adding align=left*/
992 l_document := l_document || '<TD align=left headers="itemDesc_2">'
993 || nvl(l_item_desc_tbl(i), ' ') || '</TD>' || NL;
994 l_document := l_document || '<TD nowrap headers="uom_2">'
995 || nvl(l_uom_tbl(i), ' ') || '</TD>' || NL;
996 l_document := l_document || '<TD nowrap align=right headers="quant_2">'
997 || nvl(to_char(l_quantity_tbl(i)), ' ') || '</TD>' || NL;
998
999 /* Bug 2868931: kagarwal
1000 ** We will not format the unit price on the lines in notifications
1001 */
1002
1003 l_document := l_document || '<TD nowrap align=right headers="unitPrice_2">' ||
1004 nvl(PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(
1005 l_currency_code,l_unit_price_tbl(i)),' ') || '</TD>' || NL; -- <BUG 7006113>
1006
1007 l_document := l_document || '<TD nowrap headers="location_2">'
1008 || nvl(l_location_tbl(i), ' ') || '</TD>' || NL;
1009 l_document := l_document || '<TD nowrap headers="shipToOrg_2">'
1010 || nvl(l_org_name_tbl(i), ' ') || '</TD>' || NL;
1011 l_document := l_document || '<TD nowrap headers="needByDate_2">'
1012 || to_char(l_need_by_date_tbl(i) ,FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1013 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || '''') || '</TD>' || NL;
1014 /* bug 4950850 */
1015 l_document := l_document || '<TD nowrap align=right headers="lineAmt_2">' ||
1016 nvl(TO_CHAR(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(
1017 l_currency_code, 30)),' ') || '</TD>' || NL;
1018 l_document := l_document || '</TR>' || NL;
1019
1020 -- <BUG 7006113 START>
1021 -- curr_len := lengthb(l_document);
1022
1023 wf_notification.writetoclob(document, l_document);
1024
1025 l_document := NULL;
1026
1027 EXIT WHEN i = l_num_records_to_display;
1028 -- <BUG 7006113 END>
1029
1030 end loop;
1031
1032 end if;
1033 l_document := l_document || '</TABLE></P>' || NL;
1034
1035 --<BUG 7614278 Added condition to check whether the document has value and if
1036 --so call the function WriteToClob().
1037 curr_len := lengthb(l_document);
1038 IF (NVL(curr_len,0) > 0 ) THEN
1039 wf_notification.writetoclob(document, l_document); -- <BUG 7006113>
1040 END IF;
1041 -- document := l_document; -- <BUG 7006113>
1042
1043 elsif (display_type = 'text/plain') then
1044
1045 if (nvl(l_document_type, 'PO') <> 'RELEASE') then
1046
1047 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_PO_LINE_DETAILS') || NL || NL;
1048
1049 -- <BUG 3616816 START> Only display message if # of actual lines is
1050 -- greater than maximum limit.
1051 --
1052 IF ( l_line_count > max_lines_dsp ) THEN
1053
1054 -- Bug 3668188: changed the code check (originally created
1055 -- in bug 3607009) that determines which message to show
1056 -- based on whether Open Document icon is shown in then notif.
1057 -- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
1058 -- previous node, using the get_po_user_msg_attribute procedure.
1059 -- HTML Orders R12
1060 -- Check for the URL parameters as well
1061 IF (l_open_form_command IS NULL) AND
1062 (l_view_po_url IS NULL) AND
1063 (l_edit_po_url IS NULL)
1064 THEN
1065 -- "The first [COUNT] Purchase Order lines are summarized below."
1066 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG_TRUNC');
1067 ELSE
1068 -- "The first [COUNT] Purchase Order lines are summarized
1069 -- below. For information on additional lines, please click
1070 -- the Open Document icon."
1071 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_LINE_MESG');
1072 END IF;
1073
1074 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
1075 line_mesg := FND_MESSAGE.get;
1076 l_document := l_document || line_mesg || NL || NL;
1077
1078 END IF;
1079 --
1080 -- <BUG 3616816 END>
1081
1082 -- curr_len := lengthb(l_document);
1083 -- prior_len := curr_len;
1084
1085 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
1086
1087 /* Exit the cursor if the current document length and 2 times the
1088 ** length added in prior line exceeds 32000 char */
1089 -- < BUG 7006113 START Commented the loop to avoid the check so
1090 -- that maximum lines can be displayed >
1091 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
1092 -- exit;
1093 -- end if;
1094 -- prior_len := curr_len;
1095 -- < BUG 7006113 END >
1096 IF (not l_clm_document='Y') THEN
1097 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ':' || to_char(l_line_num_tbl(i)) || NL;
1098 else
1099 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_NUMBER') || ':' || to_char(l_line_num_display_tbl(i)) || NL;
1100 end if;
1101 IF (l_clm_document='Y') THEN
1102 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_BASE_LINE_NUMBER') || ':' || to_char(l_CLM_BASE_LINE_NUM_tbl(i)) || NL;
1103 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_OPTION_NUMBER') || ':' || to_char(l_clm_option_num_tbl(i)) || NL;
1104 end if;
1105 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || ': ' || l_item_num_tbl(i) || NL;
1106 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || ': ' || l_item_revision_tbl(i) || NL;
1107 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || ': ' || l_item_desc_tbl(i) || NL;
1108
1109 IF (l_clm_document='Y') THEN
1110 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_CONTRACT_TYPE') || ': ' || l_contract_type_tbl(i) || NL;
1111 end if;
1112
1113 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' || l_uom_tbl(i) || NL;
1114 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' || to_char(l_quantity_tbl(i)) || NL;
1115
1116 /* Bug 2868931: kagarwal
1117 ** We will not format the unit price on the lines in notifications
1118 */
1119
1120 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
1121 || PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_currency_code,l_unit_price_tbl(i)) || NL;
1122 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LINE_AMOUNT') || ': '
1123 || to_char(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL || NL;
1124
1125 -- < BUG 7006113 START >
1126 -- curr_len := lengthb(l_document);
1127
1128 wf_notification.writetoclob(document, l_document);
1129
1130 l_document := NULL;
1131
1132 EXIT WHEN i = l_num_records_to_display;
1133 -- < BUG 7006113 END >
1134
1135 end loop;
1136
1137 else -- release
1138
1139 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_DETAILS') || NL || NL || NL;
1140
1141 -- <BUG 3616816 START> Only display message if # of actual lines is
1142 -- greater than maximum limit.
1143 --
1144 IF ( l_line_count > max_lines_dsp )
1145 THEN
1146 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_REL_SHIP_MESG');
1147 FND_MESSAGE.set_token('COUNT',to_char(max_lines_dsp));
1148 line_mesg := FND_MESSAGE.get;
1149 l_document := l_document || line_mesg || NL || NL;
1150 END IF;
1151 --
1152 -- <BUG 3616816 END>
1153
1154 -- curr_len := lengthb(l_document);
1155 -- prior_len := curr_len;
1156
1157 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
1158
1159 /* Exit the cursor if the current document length and 2 times the
1160 ** length added in prior line exceeds 32000 char */
1161 -- <BUG 7006113 START Commented the loop to avoid the check so that
1162 -- maximum lines can be displayed
1163 -- if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
1164 -- exit;
1165 -- end if;
1166 -- prior_len := curr_len;
1167 -- <BUG 7006113 END>
1168
1169 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SHIP_NUMBER') || ': ' || to_char(l_shipment_num_tbl(i)) || NL;
1170 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_NUMBER') || ': ' || l_item_num_tbl(i) || NL;
1171 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_REVISION') || ': ' || l_item_revision_tbl(i) || NL;
1172 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ITEM_DESC') || ': ' || l_item_desc_tbl(i) || NL;
1173 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UOM') || ': ' || l_uom_tbl(i) || NL;
1174 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_QUANTITY') || ': ' || to_char(l_quantity_tbl(i)) || NL;
1175
1176 /* Bug 2868931: kagarwal
1177 ** We will not format the unit price on the lines in notifications
1178 */
1179
1180 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_UNIT_PRICE') || ': '
1181 || PO_WF_REQ_NOTIFICATION.FORMAT_CURRENCY_NO_PRECESION(l_currency_code,l_unit_price_tbl(i)) || NL;
1182 -- bug 4950850
1183 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_AMOUNT') || ': '
1184 || to_char(l_amount_tbl(i), FND_CURRENCY.GET_FORMAT_MASK(l_currency_code, 30)) || NL;
1185 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_LOCATION') || ': ' || l_location_tbl(i) || NL;
1186 l_document := l_document || fnd_message.get_string('PO', 'POA_SHIP_TO_ORG') || ': ' || l_org_name_tbl(i) || NL;
1187 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_NEED_BY_DATE') || ': ' || to_char(l_need_by_date_tbl(i),FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1188 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN') || '''') || NL || NL;
1189
1190 -- <BUG 7006113 START>
1191 -- curr_len := lengthb(l_document);
1192
1193
1194 wf_notification.writetoclob(document, l_document);
1195
1196 l_document := NULL;
1197
1198 EXIT WHEN i = l_num_records_to_display;
1199 -- <BUG 7006113 END>
1200
1201 end loop;
1202
1203 end if;
1204
1205 --<BUG 7614278 Added condition to check whether the document has value and if
1206 --so call the function WriteToClob().
1207 curr_len := lengthb(l_document);
1208 IF (NVL(curr_len,0) > 0 ) THEN
1209 wf_notification.writetoclob(document, l_document); -- <BUG 7006113>
1210 END IF;
1211 -- document := l_document; -- <Bug 7006113>
1212 end if;
1213
1214 END get_po_lines_details;
1215
1216 PROCEDURE get_action_history ( document_id in varchar2,
1217 display_type in varchar2,
1218 document in out NOCOPY varchar2,
1219 document_type in out NOCOPY varchar2) IS
1220
1221 l_item_type wf_items.item_type%TYPE;
1222 l_item_key wf_items.item_key%TYPE;
1223
1224 l_document_id po_lines.po_header_id%TYPE;
1225 l_org_id po_lines.org_id%TYPE;
1226 l_doc_type_code VARCHAR2(80);
1227
1228 l_document VARCHAR2(32000) := '';
1229
1230 -- Bug 3668188: added new local var. note: the length of this
1231 -- varchar was determined based on the length in POXWPA1B.pls,
1232 -- which is the other place 'OPEN_FORM_COMMAND' attribute is used
1233 l_open_form_command VARCHAR2(200);
1234
1235 l_view_po_url varchar2(1000); -- HTML Orders R12
1236 l_edit_po_url varchar2(1000); -- HTML Orders R12
1237
1238 NL VARCHAR2(1) := fnd_global.newline;
1239
1240 -- <BUG 3616816 START> Declare TABLEs for each column that is selected
1241 -- from history_csr cursor.
1242 --
1243 TYPE sequence_num_tbl_type IS TABLE OF PO_ACTION_HISTORY.sequence_num%TYPE;
1244 TYPE full_name_tbl_type IS TABLE OF PER_ALL_PEOPLE_F.full_name%TYPE;
1245 TYPE displayed_field_tbl_type IS TABLE OF PO_LOOKUP_CODES.displayed_field%TYPE;
1246 TYPE action_date_tbl_type IS TABLE OF PO_ACTION_HISTORY.action_date%TYPE;
1247 TYPE note_tbl_type IS TABLE OF PO_ACTION_HISTORY.note%TYPE;
1248 TYPE object_revision_num_tbl_type IS TABLE OF PO_ACTION_HISTORY.object_revision_num%TYPE;
1249 TYPE employee_id_tbl_type IS TABLE OF PO_ACTION_HISTORY.employee_id%TYPE;
1250 TYPE created_by_tbl_type IS TABLE OF PO_ACTION_HISTORY.created_by%TYPE;
1251
1252 l_sequence_num_tbl sequence_num_tbl_type;
1253 l_employee_name_tbl full_name_tbl_type;
1254 l_action_tbl displayed_field_tbl_type;
1255 l_action_date_tbl action_date_tbl_type;
1256 l_note_tbl note_tbl_type;
1257 l_object_revision_num_tbl object_revision_num_tbl_type;
1258 l_employee_id_tbl employee_id_tbl_type;
1259 l_created_by_tbl created_by_tbl_type;
1260 --
1261 -- <BUG 3616816 END>
1262
1263 --SQL What: Query action history which is updated by both buyer and vendor
1264 --SQL Why: Since vendor doesn't have employee id, added outer join;
1265 -- CLM Apprvl. Introduced object_sub_type_code condition to get the proper records in case of modification document.
1266 CURSOR history_csr(v_document_id NUMBER, v_doc_type_code VARCHAR2, v_doc_sub_type_code VARCHAR2) IS
1267 SELECT poh.SEQUENCE_NUM,
1268 per.FULL_NAME,
1269 polc.DISPLAYED_FIELD,
1270 poh.ACTION_DATE,
1271 poh.NOTE,
1272 poh.OBJECT_REVISION_NUM,
1273 poh.employee_id, /* bug 2788683 */
1274 poh.created_by /* bug 2788683 */
1275 from po_action_history poh,
1276 per_all_people_f per, -- Bug 3404451
1277 po_lookup_codes polc
1278 where OBJECT_TYPE_CODE = v_doc_type_code
1279 and poh.object_sub_type_code = v_doc_sub_type_code
1280 and poh.action_code = polc.lookup_code
1281 and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
1282 and per.person_id(+) = poh.employee_id /* bug 2788683 */
1283 and trunc(sysdate) between per.effective_start_date(+)
1284 and per.effective_end_date(+)
1285 and OBJECT_ID = v_document_id
1286 UNION ALL
1287 SELECT poh.SEQUENCE_NUM,
1288 per.FULL_NAME,
1289 NULL,
1290 poh.ACTION_DATE,
1291 poh.NOTE,
1292 poh.OBJECT_REVISION_NUM,
1293 poh.employee_id, /* bug 2788683 */
1294 poh.created_by /* bug 2788683 */
1295 from po_action_history poh,
1296 per_all_people_f per -- Bug 3404451
1297 where OBJECT_TYPE_CODE = v_doc_type_code
1298 and poh.object_sub_type_code = v_doc_sub_type_code
1299 and poh.action_code is null
1300 and per.person_id(+) = poh.employee_id /* bug 2788683 */
1301 and trunc(sysdate) between per.effective_start_date(+)
1302 and per.effective_end_date(+)
1303 and OBJECT_ID = v_document_id
1304 order by 1 desc;
1305
1306 i NUMBER := 0;
1307 max_actions_dsp NUMBER := 20;
1308 l_action_count NUMBER; -- <BUG 3616816> # of action history records
1309 l_num_records_to_display NUMBER; -- <BUG 3616816> actual # of records to display in table
1310 action_mesg fnd_new_messages.message_text%TYPE; --Bug 4695601
1311 curr_len NUMBER := 0;
1312 prior_len NUMBER := 0;
1313
1314 /* Bug 2788683 start */
1315 l_user_name fnd_user.user_name%TYPE;
1316 l_vendor_name hz_parties.party_name%TYPE;
1317 l_party_name hz_parties.party_name%TYPE;
1318 /* Bug 2788683 end */
1319 l_supplier po_vendors.vendor_name%TYPE; --<BUG 7475571>
1320 l_draft_id NUMBER;
1321 l_doc_sub_type_code po_action_history.object_sub_type_code%TYPE; -- CLM Apprvl
1322 l_draft_type PO_DRAFTS.DRAFT_TYPE%TYPE; -- PAR Approval
1323
1324 BEGIN
1325
1326 l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
1327 l_item_key := substr(document_id, instr(document_id, ':') + 1,
1328 length(document_id) - 2);
1329
1330 l_document_id := wf_engine.GetItemAttrNumber
1331 (itemtype => l_item_type,
1332 itemkey => l_item_key,
1333 aname => 'DOCUMENT_ID');
1334
1335 l_draft_id := po_wf_util_pkg.GetItemAttrNumber
1336 (itemtype => l_item_type,
1337 itemkey => l_item_key,
1338 aname => 'DRAFT_ID');
1339
1340 l_org_id := wf_engine.GetItemAttrNumber
1341 (itemtype => l_item_type,
1342 itemkey => l_item_key,
1343 aname => 'ORG_ID');
1344
1345 l_doc_type_code := wf_engine.GetItemAttrText (itemtype => l_item_type,
1346 itemkey => l_item_key,
1347 aname => 'DOCUMENT_TYPE');
1348
1349 -- CLM Apprvl, object sub type code for mods need to recorded as 'MODIFICATION' in po action history
1350 if l_draft_id is not null and l_draft_id <> -1 then
1351 -- pAR Approval
1352 l_draft_type := wf_engine.GetItemAttrText (itemtype => l_item_type,
1353 itemkey => l_item_key,
1354 aname => 'DRAFT_TYPE');
1355 IF l_draft_type = 'PAR' THEN
1356 l_doc_sub_type_code := 'POST_AWARD_REQUEST';
1357 ELSE
1358 l_doc_sub_type_code := 'MODIFICATION';
1359 END IF;
1360 else
1361 l_doc_sub_type_code := wf_engine.GetItemAttrText (itemtype => l_item_type,
1362 itemkey => l_item_key,
1363 aname => 'DOCUMENT_SUBTYPE');
1364 end if;
1365
1366 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1367
1368 -- Bug 3668188
1369 l_open_form_command := PO_WF_UTIL_PKG.GetItemAttrText
1370 (itemtype => l_item_type,
1371 itemkey => l_item_key,
1372 aname => 'OPEN_FORM_COMMAND');
1373
1374 -- HTML Orders R12
1375 -- Get the PO HTML Page URL's
1376 l_view_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
1377 itemtype => l_item_type,
1378 itemkey => l_item_key,
1379 aname => 'VIEW_DOC_URL');
1380
1381 l_edit_po_url := PO_WF_UTIL_PKG.GetItemAttrText (
1382 itemtype => l_item_type,
1383 itemkey => l_item_key,
1384 aname => 'EDIT_DOC_URL');
1385
1386 /* Bug# 2577478: kagarwal
1387 ** Desc: Added a new attribute ACT_HST_IN_NTF in wf definition for
1388 ** users to specify the number of PO actions to be displayed in a
1389 ** notification.
1390 ** If the attribute does not exist or is null, then we would use default
1391 ** value of 20.
1392 */
1393
1394 max_actions_dsp:= PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => l_item_type,
1395 itemkey => l_item_key,
1396 aname => 'ACT_HST_IN_NTF');
1397
1398 if max_actions_dsp is NULL then
1399 max_actions_dsp := 20;
1400 end if;
1401
1402 -- <BUG 3616816 START> Fetch all Action History data into Tables.
1403 --
1404 --CLM Apprvl. for modification documents draft_id is recorded as the object_id of po_action_history.
1405 if l_draft_id is not null and l_draft_id <> -1 then
1406 OPEN history_csr(l_draft_id,l_doc_type_code,l_doc_sub_type_code);
1407 else
1408 OPEN history_csr(l_document_id,l_doc_type_code,l_doc_sub_type_code);
1409 end if;
1410
1411 FETCH history_csr BULK COLLECT INTO l_sequence_num_tbl
1412 , l_employee_name_tbl
1413 , l_action_tbl
1414 , l_action_date_tbl
1415 , l_note_tbl
1416 , l_object_revision_num_tbl
1417 , l_employee_id_tbl
1418 , l_created_by_tbl;
1419
1420 l_action_count := history_csr%ROWCOUNT; -- Get # of records fetched.
1421
1422 CLOSE history_csr;
1423 --
1424 -- <BUG 3616816 END>
1425
1426 -- <BUG 3616816 START> Only display message if # of actual Action History
1427 -- records is greater than maximum limit.
1428 --
1429 IF ( l_action_count > max_actions_dsp ) THEN
1430
1431 l_num_records_to_display := max_actions_dsp;
1432
1433 -- Bug 3668188: changed the code check (originally created
1434 -- in bug 3607009) that determines which message to show
1435 -- based on whether Open Document icon is shown in then notif.
1436 -- The value of WF attribute 'OPEN_FORM_COMMAND' is set in a
1437 -- previous node, using the get_po_user_msg_attribute procedure.
1438 --
1439 IF (l_open_form_command IS NULL) AND
1440 (l_view_po_url IS NULL) AND
1441 (l_edit_po_url IS NULL)
1442 THEN
1443 -- "The last [COUNT] Approval History details are summarized below."
1444 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_ACT_MESG_TRUNC');
1445 ELSE
1446 -- "The last [COUNT] Approval History details are summarized below.
1447 -- For information on additional Approval History, please click the
1448 -- Open Document icon."
1449 FND_MESSAGE.set_name('PO','PO_WF_NOTIF_PO_ACT_MESG');
1450 END IF;
1451
1452 FND_MESSAGE.set_token('COUNT',to_char(max_actions_dsp));
1453 action_mesg := FND_MESSAGE.get;
1454
1455 ELSE
1456
1457 l_num_records_to_display := l_action_count;
1458 action_mesg := NULL;
1459
1460 END IF;
1461 --
1462 -- <BUG 3616816 END>
1463
1464 if (display_type = 'text/html') then
1465
1466 l_document := NL || NL || '<!-- ACTION_HISTORY -->'|| NL || NL || '<P><B>';
1467 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY') || NL;
1468 l_document := l_document || '</B>' || NL || NL || '<P>'; -- <BUG 3616816>
1469
1470 -- <BUG 3616816 START> Action History message may be NULL. Only append it
1471 -- and corresponding line breaks if there is a message to display.
1472 --
1473 IF ( action_mesg IS NOT NULL )
1474 THEN
1475 l_document := l_document || action_mesg || '<P>' || NL;
1476 END IF;
1477 --
1478 -- <BUG 3616816 END>
1479
1480 l_document := l_document || '<TABLE border=1 cellpadding=2 cellspacing=1 summary="' || fnd_message.get_string('ICX', 'ICX_POR_TBL_OF_APPROVERS') || '">' || NL;
1481
1482 l_document := l_document || '<TR>';
1483
1484 l_document := l_document || '<TH id="seqNum_1">' ||
1485 fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') || '</TH>' || NL;
1486
1487 l_document := l_document || '<TH id="employee_1">' ||
1488 fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || '</TH>' || NL;
1489
1490 l_document := l_document || '<TH id="action_1">' ||
1491 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || '</TH>' || NL;
1492
1493 l_document := l_document || '<TH id="date_1">' ||
1494 fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || '</TH>' || NL;
1495
1496 l_document := l_document || '<TH id="actionNote_1">' ||
1497 fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || '</TH>' || NL;
1498
1499 l_document := l_document || '</TR>' || NL;
1500
1501 curr_len := lengthb(l_document);
1502 prior_len := curr_len;
1503
1504 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
1505
1506 /* Exit the cursor if the current document length and 2 times the
1507 ** length added in prior line exceeds 32000 char */
1508
1509 if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
1510 exit;
1511 end if;
1512
1513 prior_len := curr_len;
1514
1515 l_document := l_document || '<TR>' || NL;
1516
1517 l_document := l_document || '<TD nowrap align=center headers="seqNum_1">'
1518 || nvl(to_char(l_sequence_num_tbl(i)), ' ') || '</TD>' || NL;
1519
1520 /* Bug 2788683 start */
1521 /* if action history is updated by vendor
1522 * show vendor true name(vendor name)
1523 * else action history is updated by buyer
1524 * show buyer's true name
1525 */
1526 IF l_employee_id_tbl(i) IS NULL THEN
1527 BEGIN --<BUG 7475571>
1528 SELECT fu.user_name,
1529 hp.party_name
1530 INTO l_user_name,
1531 l_party_name
1532 FROM fnd_user fu,
1533 hz_parties hp
1534 WHERE hp.party_id = fu.customer_id
1535 AND fu.user_id = l_created_by_tbl(i);
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 NULL;
1539 END;
1540 -- <BUG 7475571 Added the below IF condition so that if the l_user_name
1541 -- returns NULL value because of supplier user setup the PO approval
1542 -- notifcation should not error out. Instead modified the code such that
1543 -- the action history column for change request will show the corresponding
1544 -- supplier name rather than supplier user name.
1545 IF l_user_name IS NULL THEN
1546 l_user_name := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_item_type,
1547 itemkey => l_item_key,
1548 aname => 'SUPPLIER');
1549
1550 l_document := l_document || '<TD nowrap headers="employee1">' || l_user_name || '</TD>' || NL;
1551 ELSE
1552 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
1553
1554 l_document := l_document || '<TD nowrap headers="employee_1">' || l_party_name || '(' || l_vendor_name || ')' || '</TD>' || NL;
1555 END IF;
1556 ELSE
1557 l_document := l_document || '<TD nowrap headers="employee_1">'
1558 || nvl(l_employee_name_tbl(i), ' ') || '</TD>' || NL;
1559 END IF;
1560 /* Bug 2788683 end */
1561
1562 l_document := l_document || '<TD nowrap headers="action_1">'
1563 || nvl(l_action_tbl(i), ' ') || '</TD>' || NL;
1564 l_document := l_document || '<TD nowrap headers="date_1">'
1565 || nvl(to_char(l_action_date_tbl(i),FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1566 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || ''''), ' ') || '</TD>' || NL;
1567 /* Bug 11825584 removing nowrap and adding align=left*/
1568 l_document := l_document || '<TD align=left headers="actionNote_1">'
1569 || nvl(l_note_tbl(i), ' ') || '</TD>' || NL;
1570
1571 l_document := l_document || '</TR>' || NL;
1572
1573 curr_len := lengthb(l_document);
1574
1575 end loop;
1576
1577 l_document := l_document || '</TABLE></P>' || NL;
1578
1579 document := l_document;
1580
1581 elsif (display_type = 'text/plain') then
1582
1583 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_HISTORY') || NL;
1584
1585 -- <BUG 3616816 START> Action History message may be NULL. Only append it
1586 -- and corresponding line breaks if there is a message to display.
1587 --
1588 IF ( action_mesg IS NOT NULL )
1589 THEN
1590 l_document := l_document || action_mesg || NL || NL;
1591 END IF;
1592 --
1593 -- <BUG 3616816 END>
1594
1595 curr_len := lengthb(l_document);
1596 prior_len := curr_len;
1597
1598 FOR i IN 1..l_num_records_to_display LOOP -- <BUG 3616816>
1599
1600 /* Exit the cursor if the current document length and 2 times the
1601 ** length added in prior line exceeds 32000 char */
1602
1603 if (curr_len + (2 * (curr_len - prior_len))) >= 32000 then
1604 exit;
1605 end if;
1606
1607 prior_len := curr_len;
1608
1609 l_document := l_document || NL;
1610
1611 /* Bug 2462005 sktiwari:
1612 ** Added a ':' between the prompt and the data. Modified the following lines.
1613 */
1614 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_SEQ_NUM') || ': ' || to_char(l_sequence_num_tbl(i)) || NL;
1615
1616 /* Bug 2788683 start */
1617 /* if action history is updated by vendor
1618 * show vendor true name(vendor name)
1619 * else action history is updated by buyer
1620 * show buyer's true name
1621 */
1622 IF l_employee_id_tbl(i) IS NULL THEN
1623 BEGIN --<BUG 7475571>
1624 SELECT fu.user_name, hp.party_name
1625 INTO l_user_name, l_party_name
1626 FROM fnd_user fu,
1627 hz_parties hp
1628 WHERE hp.party_id = fu.customer_id
1629 AND fu.user_id = l_created_by_tbl(i);
1630 EXCEPTION
1631 WHEN OTHERS THEN
1632 NULL;
1633 END;
1634 -- <BUG 7475571 Added the below IF condition so that if the l_user_name
1635 -- returns NULL value because of supplier user setup the PO approval
1636 -- notifcation should not error out. Instead modified the code such that
1637 -- the action history column for change request will show the corresponding
1638 -- supplier name rather than supplier user name.
1639 IF l_user_name IS NULL THEN
1640 l_user_name := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => l_item_type,
1641 itemkey => l_item_key,
1642 aname => 'SUPPLIER');
1643
1644 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || l_user_name || NL;
1645 ELSE
1646 po_inq_sv.get_vendor_name(l_user_name => l_user_name, x_vendor_name => l_vendor_name);
1647
1648 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || ': ' || l_party_name || '(' || l_vendor_name || ')' || NL;
1649 END IF;
1650 ELSE
1651 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_EMPLOYEE') || ': ' || l_employee_name_tbl(i) || NL;
1652 END IF;
1653 /* Bug 2788683 end */
1654
1655 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION') || ': ' || l_action_tbl(i) || NL;
1656 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_DATE') || ': ' || to_char(l_action_date_tbl(i),FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', fnd_global.user_id),
1657 'NLS_CALENDAR = ''' || NVL( FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', fnd_global.user_id) ,'GREGORIAN' ) || '''') || NL;
1658 l_document := l_document || fnd_message.get_string('PO', 'PO_WF_NOTIF_ACTION_NOTE') || ': ' || l_note_tbl(i) || NL;
1659
1660 l_document := l_document || NL;
1661
1662 curr_len := lengthb(l_document);
1663 end loop;
1664
1665 l_document := l_document;
1666
1667 document := l_document;
1668
1669 end if;
1670
1671 END;
1672
1673 PROCEDURE post_approval_notif(itemtype in varchar2,
1674 itemkey in varchar2,
1675 actid in number,
1676 funcmode in varchar2,
1677 resultout in out NOCOPY varchar2) is
1678
1679 -- Context setting revamp <variable addition start>
1680 l_responder_id fnd_user.user_id%TYPE;
1681 l_session_user_id NUMBER;
1682 l_session_resp_id NUMBER;
1683 l_session_appl_id NUMBER;
1684 l_preparer_resp_id NUMBER;
1685 l_preparer_appl_id NUMBER;
1686 l_progress VARCHAR2(1000);
1687 l_nid NUMBER;
1688 l_preserved_ctx VARCHAR2(5);
1689 -- Context setting revamp <variable addition end>
1690
1691 --Added the below variables as part of bug 13951919 fix. Used to log the Delegate action in the PO_ACTION_HISTORY table.
1692 l_po_header_id po_headers_all.po_header_id%TYPE;
1693 l_doc_type po_action_history.OBJECT_TYPE_CODE%TYPE;
1694 l_doc_sub_type po_action_history.OBJECT_SUB_TYPE_CODE%TYPE;
1695 l_action po_action_history.action_code%TYPE;
1696 l_new_recipient_id wf_roles.orig_system_id%TYPE;
1697 l_current_recipient_id wf_roles.orig_system_id%TYPE;
1698 l_origsys wf_roles.orig_system%TYPE;
1699 l_original_recipient wf_notifications.original_recipient%TYPE;
1700 l_current_recipient_role wf_notifications.recipient_role%TYPE;
1701
1702 --Added the below variables as part of bug 14105414 fix.
1703 x_user_id number;
1704 l_forward_to_username_response varchar2(100);
1705 l_forward_to_id number;
1706 l_forward_to_username varchar2(100);
1707 l_error_msg varchar2(500);
1708 l_preparer_id number;
1709 x_CanOwnerApproveFlag varchar2(1);
1710 l_orgid number;
1711 l_note po_action_history.note%TYPE;
1712 l_respond_action_text varchar2(100);
1713 l_is_forward_valid boolean;
1714 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
1715
1716 l_draft_id number;
1717 --15859236 start
1718 l_new_recipient_name wf_users.name%type;
1719 l_new_recipient_display_name wf_users.display_name%type;
1720 --15859236 end
1721 l_draft_type PO_DRAFTS.DRAFT_TYPE%TYPE; -- PAR Approval
1722 begin
1723
1724 --Start of code changes for inserting the delegate action in Action History. Bug 13951919 fix
1725 l_progress := '001';
1726 IF (g_po_wf_debug = 'Y') THEN
1727 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1728 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'funcmode : '||funcmode);
1729 END IF;
1730
1731 if (funcmode IN ('FORWARD', 'QUESTION', 'ANSWER','TIMEOUT')) then
1732 if (funcmode = 'FORWARD') then
1733 l_action := 'DELEGATE';
1734 elsif (funcmode = 'QUESTION') then
1735 l_action := 'QUESTION';
1736 elsif (funcmode = 'ANSWER') then
1737 l_action := 'ANSWER';
1738 elsif (funcmode = 'TIMEOUT') then
1739 l_action := 'NO ACTION';
1740 end if;
1741
1742 IF (g_po_wf_debug = 'Y') THEN
1743 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_action : '||l_action);
1744 END IF;
1745
1746 l_po_header_id := PO_WF_UTIL_PKG.GetItemAttrNumber( itemtype => itemtype,
1747 itemkey => itemkey,
1748 aname => 'DOCUMENT_ID');
1749 l_doc_type := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
1750 itemkey => itemkey,
1751 aname => 'DOCUMENT_TYPE');
1752 l_doc_sub_type := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
1753 itemkey => itemkey,
1754 aname => 'DOCUMENT_SUBTYPE');
1755
1756 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber( itemtype => itemtype,
1757 itemkey => itemkey,
1758 aname => 'DRAFT_ID');
1759 -- PAR Approval
1760 l_draft_type := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
1761 itemkey => itemkey,
1762 aname => 'DRAFT_TYPE');
1763
1764 IF (g_po_wf_debug = 'Y') THEN
1765 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'Document_ID : '||l_po_header_id);
1766 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'Draft_id : '||l_draft_id);
1767 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_doc_type : '||l_doc_type);
1768 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_doc_sub_type : '||l_doc_sub_type);
1769 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_draft_type : '||l_draft_type);
1770 END IF;
1771
1772 IF (l_action <> 'NO ACTION') THEN
1773 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_NEW_ROLE, l_origsys, l_new_recipient_id);
1774
1775 IF (g_po_wf_debug = 'Y') THEN
1776 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_origsys : '||l_origsys);
1777 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_new_recipient_id : '||l_new_recipient_id);
1778 END IF;
1779
1780 ELSE
1781 BEGIN
1782 SELECT original_recipient,
1783 Decode(MORE_INFO_ROLE, NULL, RECIPIENT_ROLE,MORE_INFO_ROLE)
1784 INTO l_original_recipient, l_current_recipient_role
1785 FROM wf_notifications
1786 WHERE
1787 notification_id = WF_ENGINE.context_nid
1788 AND ( MORE_INFO_ROLE IS NOT NULL OR RECIPIENT_ROLE <> ORIGINAL_RECIPIENT );
1789
1790 IF (g_po_wf_debug = 'Y') THEN
1791 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_original_recipient : '||l_original_recipient);
1792 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_current_recipient_role : '||l_current_recipient_role);
1793 END IF;
1794
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 l_original_recipient := NULL;
1798 END;
1799
1800 IF l_original_recipient IS NOT NULL THEN
1801 Wf_Directory.GetRoleOrigSysInfo(l_original_recipient, l_origsys, l_new_recipient_id);
1802 END IF;
1803
1804 END IF;
1805
1806 /* We should not be allowing the delegation of a notication
1807 to a user who is not an employee. */
1808
1809 if((funcmode = 'FORWARD') AND (l_origsys <> 'PER')) then
1810 fnd_message.set_name('PO', 'PO_INVALID_USER_FOR_REASSIGN');
1811 app_exception.raise_exception;
1812 end if;
1813
1814 l_progress := '002';
1815
1816 IF (funcmode = 'ANSWER') THEN
1817 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_MORE_INFO_ROLE, l_origsys, l_current_recipient_id);
1818 ELSIF (funcmode = 'TIMEOUT') THEN
1819 Wf_Directory.GetRoleOrigSysInfo(l_current_recipient_role, l_origsys, l_current_recipient_id);
1820 ELSE
1821 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_RECIPIENT_ROLE, l_origsys, l_current_recipient_id);
1822 END IF;
1823
1824 l_progress := '003';
1825
1826 IF (g_po_wf_debug = 'Y') THEN
1827 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1828 END IF;
1829
1830 IF l_new_recipient_id IS NOT NULL THEN
1831 IF (g_po_wf_debug = 'Y') THEN
1832 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'Before executing update_action_history');
1833 END IF;
1834
1835 --15859236 start
1836 wf_directory.GetUserName(l_origsys, l_new_recipient_id, l_new_recipient_name, l_new_recipient_display_name);
1837
1838 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1839 itemkey => itemkey,
1840 aname => 'FORWARD_TO_ID',
1841 avalue => l_new_recipient_id);
1842
1843 wf_engine.SetItemAttrText (itemtype => itemtype,
1844 itemkey => itemkey,
1845 aname => 'FORWARD_TO_USERNAME',
1846 avalue => l_new_recipient_name);
1847
1848 wf_engine.SetItemAttrText (itemtype => itemtype,
1849 itemkey => itemkey,
1850 aname => 'FORWARD_TO_DISPLAY_NAME',
1851 avalue => l_new_recipient_display_name);
1852 --15859236 end
1853
1854 -- PAR Approval
1855 update_action_history(p_action_code => l_action,
1856 p_recipient_id => l_new_recipient_id,
1857 p_note => WF_ENGINE.CONTEXT_USER_COMMENT,
1858 p_po_header_id => l_po_header_id,
1859 p_current_id => l_current_recipient_id,
1860 p_doc_type => l_doc_type,
1861 p_doc_subtype => l_doc_sub_type,
1862 p_approval_path_id => NULL, --<bug 14105414>
1863 p_draft_id => l_draft_id,
1864 p_draft_type => l_draft_type);
1865
1866 IF (g_po_wf_debug = 'Y') THEN
1867 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'After executing update_action_history');
1868 END IF;
1869 END IF;
1870
1871 l_progress := '004';
1872
1873 IF (funcmode <> 'TIMEOUT') THEN
1874 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
1875 END IF;
1876
1877 return;
1878 end if;
1879
1880 --End of code changes for inserting the delegate action in Action History. Bug 13951919 fix
1881
1882 -- Context setting revamp <start>
1883 if (funcmode = 'RESPOND') then
1884 l_nid := WF_ENGINE.context_nid;
1885
1886 SELECT fu.USER_ID
1887 INTO l_responder_id
1888 FROM fnd_user fu,
1889 wf_notifications wfn
1890 WHERE wfn.notification_id = l_nid
1891 AND wfn.original_recipient = fu.user_name;
1892
1893 --Start of code changes for updating the action code in the action history
1894 --as per the approver action. <bug 14105414>
1895 /* Get the current approver's response/action. Here it can be either FORWARD
1896 or APPROVE or APPROVE_AND_FORWARD or REJECT. */
1897 l_respond_action_text := Wf_Notification.GetAttrText(l_nid, 'RESULT');
1898
1899 IF (g_po_wf_debug = 'Y') THEN
1900 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
1901 'l_respond_action_text: ' || l_respond_action_text);
1902 END IF;
1903
1904 /* This boolean flag will be set to FALSE if the forwarded person is invalid */
1905 l_is_forward_valid := TRUE;
1906
1907 IF (l_respond_action_text = 'FORWARD'
1908 OR l_respond_action_text = 'APPROVE_AND_FORWARD') THEN
1909
1910 /*
1911 ** Desc: When responding from the E-mail notifications, the forward
1912 ** to failed as the org context was not set.
1913 */
1914
1915 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1916 itemkey => itemkey,
1917 aname => 'ORG_ID');
1918 IF (g_po_wf_debug = 'Y') THEN
1919 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
1920 'l_orgid: ' || l_orgid);
1921 END IF;
1922
1923 IF l_orgid is NOT NULL THEN
1924
1925 po_moac_utils_pvt.set_org_context(l_orgid); --<R12 MOAC>
1926
1927 END IF;
1928
1929 /* Check that the value entered by responder as the FORWARD-TO user, is actually
1930 ** a valid employee (has an employee id).
1931 ** If valid, then set the FORWARD-FROM USERNAME and ID from the old FORWARD-TO.
1932 ** Then set the Forward-To to the one the user entered in the response.
1933 */
1934 /* NOTE: We take the value entered by the user and set it to ALL CAPITAL LETTERS!!!
1935 */
1936 l_forward_to_username_response := wf_notification.GetAttrText(l_nid, 'FORWARD_TO_USERNAME_RESPONSE');
1937
1938 IF (g_po_wf_debug = 'Y') THEN
1939 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
1940 'l_forward_to_username_response: ' || l_forward_to_username_response);
1941 END IF;
1942
1943 l_forward_to_username_response := UPPER(l_forward_to_username_response);
1944
1945 IF po_reqapproval_findapprv1.CheckForwardTo(l_forward_to_username_response,
1946 x_user_id) = 'Y'
1947 THEN
1948
1949 /* The FORWARD-FROM is now the old FORWARD-TO and the NEW FORWARD-TO is set
1950 ** to what the user entered in the response
1951 */
1952
1953 l_forward_to_username:= wf_engine.GetItemAttrText (itemtype => itemtype,
1954 itemkey => itemkey,
1955 aname => 'FORWARD_TO_USERNAME');
1956 IF (g_po_wf_debug = 'Y') THEN
1957 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
1958 'l_forward_to_username: ' || l_forward_to_username);
1959 END IF;
1960
1961 /*
1962 ** Desc: When the approver takes approve action from the Notification form after
1963 ** modifying the PO. The Approver attributes are set but forward-to attributes
1964 ** are set to Null. Now if any error is encountered, the Notification is sent to
1965 ** the approver and if after the error the approver forwards the PO, the
1966 ** is_forward_to_valid function sets the forward-from and approver attributes
1967 ** from the forward-to attributes (it has not changed as of now) and then sets
1968 ** the forward-to attributes to the the response-forward person but in this case
1969 ** the forward-to attributes had been set to null by previous approve action
1970 ** hence the approver_username was set to NULL by this function.
1971 **
1972 ** If the forward-to attributes are null when taking the forward action we
1973 ** should use the approver attributes. This will ensure that the approver
1974 ** attributes and the forward-from attributes are not set to NULL on
1975 ** forwarding the document.
1976 */
1977
1978 IF l_forward_to_username is NOT NULL THEN
1979
1980 l_forward_to_id:= wf_engine.GetItemAttrNumber (itemtype => itemtype,
1981 itemkey => itemkey,
1982 aname => 'FORWARD_TO_ID');
1983 ELSE /* get the approver name who took this action */
1984 l_forward_to_id:= wf_engine.GetItemAttrNumber (itemtype => itemtype,
1985 itemkey => itemkey,
1986 aname => 'APPROVER_EMPID');
1987 END IF;
1988
1989 l_current_recipient_id := l_forward_to_id; --for updating action history
1990
1991 /*
1992 ** Here, x_user_id is forwarded person user ID
1993 ** l_preparer_id is Document Preparer ID
1994 */
1995 l_preparer_id := wf_engine.GetItemAttrText(itemtype => itemtype,
1996 itemkey => itemkey,
1997 aname => 'PREPARER_ID');
1998 IF (g_po_wf_debug = 'Y') THEN
1999 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
2000 'l_preparer_id: ' || l_preparer_id ||
2001 ', x_user_id: ' || x_user_id);
2002 END IF;
2003
2004 IF (x_user_id = l_preparer_id) THEN
2005 /* If the forward person is Preparer. Then check whether the owner can approve or not */
2006 PO_REQAPPROVAL_FINDAPPRV1.CheckOwnerCanApprove(itemtype, itemkey,
2007 x_CanOwnerApproveFlag);
2008
2009 IF (g_po_wf_debug = 'Y') THEN
2010 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
2011 'l_preparer_id: ' || l_preparer_id ||
2012 ', x_CanOwnerApproveFlag: ' || x_CanOwnerApproveFlag);
2013 END IF;
2014
2015 IF x_CanOwnerApproveFlag = 'N' then
2016 l_is_forward_valid := FALSE;
2017 END IF;
2018 END IF;
2019 ELSE
2020 --Forward-To person is invalid, set current employee id as recipient.
2021 l_is_forward_valid := FALSE;
2022 l_current_recipient_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2023 itemkey => itemkey,
2024 aname => 'APPROVER_EMPID');
2025 END IF;
2026 ELSE
2027 l_is_forward_valid := FALSE;
2028 --To record the real approver.
2029 Wf_Directory.GetRoleOrigSysInfo(WF_ENGINE.CONTEXT_RECIPIENT_ROLE, l_origsys, l_current_recipient_id);
2030
2031 END IF;
2032
2033 l_progress := '005 PO_WF_PO_NOTIFICATION.post_approval_notif';
2034 IF (g_po_wf_debug = 'Y') THEN
2035 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2036 END IF;
2037
2038 /* If the FORWARD is valid, set all the required workflow attributes */
2039 IF (l_is_forward_valid = TRUE) THEN
2040
2041 l_progress := '005 is_forward_valid TRUE';
2042 IF (g_po_wf_debug = 'Y') THEN
2043 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2044 END IF;
2045
2046 /* Set the FORWARD_FROM */
2047 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2048 itemkey => itemkey,
2049 aname => 'FORWARD_FROM_ID',
2050 avalue => l_forward_to_id);
2051
2052 /* Set the approver to the person who took the action on the notification,
2053 ** i.e. the old forward-to person
2054 */
2055 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
2056 itemkey => itemkey,
2057 aname => 'APPROVER_EMPID',
2058 avalue => l_forward_to_id);
2059
2060 wf_engine.SetItemAttrNumber ( itemtype => itemType,
2061 itemkey => itemkey,
2062 aname => 'FORWARD_TO_ID',
2063 avalue => x_user_id);
2064
2065 /* Set the Subject of the Approval notification to "requires your approval".
2066 ** Since the user entered a valid forward-to, then set the
2067 ** "Invalid Forward-to" message to NULL.
2068 */
2069 fnd_message.set_name ('PO','PO_WF_NOTIF_REQUIRES_APPROVAL');
2070 l_error_msg := fnd_message.get;
2071
2072 wf_engine.SetItemAttrText ( itemtype => itemType,
2073 itemkey => itemkey,
2074 aname => 'REQUIRES_APPROVAL_MSG' ,
2075 avalue => l_error_msg);
2076
2077 wf_engine.SetItemAttrText ( itemtype => itemType,
2078 itemkey => itemkey,
2079 aname => 'WRONG_FORWARD_TO_MSG' ,
2080 avalue => '');
2081
2082
2083 /* Other workflow attributes are set in PO_REQAPPROVAL_FINDAPPRV1.Set_Forward_To_From_App_fwd */
2084
2085 ELSIF (l_is_forward_valid = FALSE) THEN
2086 /* If the forwarded person is invalid or forwarded person is Preparer
2087 ** and doesn't have approval authority, show error message.
2088 */
2089
2090 /* Set the error message that will be shown to the user in the ERROR MESSAGE
2091 ** Field in the Notification.
2092 */
2093 l_progress := '005 is_forward_valid FALSE';
2094 IF (g_po_wf_debug = 'Y') THEN
2095 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2096 END IF;
2097
2098 /* Set the Subject of the Approval notification to "Invalid forward-to"
2099 ** Since the user entered an invalid forward-to, then set the
2100 ** "requires your approval" message to NULL.
2101 */
2102 fnd_message.set_name ('PO','PO_WF_NOTIF_INVALID_FORWARD');
2103 l_error_msg := fnd_message.get;
2104
2105 wf_engine.SetItemAttrText ( itemtype => itemType,
2106 itemkey => itemkey,
2107 aname => 'REQUIRES_APPROVAL_MSG' ,
2108 avalue => '');
2109
2110 wf_engine.SetItemAttrText ( itemtype => itemType,
2111 itemkey => itemkey,
2112 aname => 'WRONG_FORWARD_TO_MSG' ,
2113 avalue => l_error_msg);
2114
2115 END IF;
2116
2117 /*
2118 ** Here the logic to update the action code in action history.
2119 ** The insert action is still performed in PO_DOCUMENT_ACTION_UTIL.handle_auth_action_history.
2120 */
2121 l_progress := '006 PO_WF_PO_NOTIFICATION.post_approval_notif';
2122 IF (g_po_wf_debug = 'Y') THEN
2123 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2124 END IF;
2125
2126 /*
2127 ** Update action history for APPROVE_AND_FORWARD
2128 ** even when the forward-to person is invalid.
2129 */
2130 IF (l_respond_action_text in ('APPROVE','APPROVE_AND_FORWARD'))
2131 OR (l_respond_action_text = 'FORWARD' AND l_is_forward_valid = TRUE) THEN
2132
2133 IF l_respond_action_text = 'FORWARD' THEN
2134 l_action := 'FORWARD';
2135 ELSIF l_respond_action_text = 'APPROVE' THEN
2136 l_action := 'APPROVE';
2137 ELSIF l_respond_action_text = 'APPROVE_AND_FORWARD' THEN
2138 l_action := 'APPROVE AND FORWARD';
2139 END IF;
2140
2141 /* Reset response note */
2142 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2143 itemkey => itemkey,
2144 aname => 'DOCUMENT_TYPE');
2145
2146 IF (l_doc_type='PO' OR l_doc_type='PA' OR l_doc_type='RELEASE' ) THEN
2147 l_note := wf_notification.GetAttrText(l_nid, 'NOTE_R');
2148 PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
2149 itemkey => itemkey,
2150 aname => 'NOTE',
2151 avalue => l_note);
2152 PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
2153 itemkey => itemkey,
2154 aname => 'NOTE_R',
2155 avalue => NULL);
2156 wf_notification.SetAttrText(l_nid, 'NOTE', l_note);
2157 wf_notification.SetAttrText(l_nid, 'NOTE_R', null);
2158 END IF;
2159
2160 l_po_header_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2161 itemkey => itemkey,
2162 aname => 'DOCUMENT_ID');
2163 l_doc_sub_type := wf_engine.GetItemAttrText(itemtype => itemtype,
2164 itemkey => itemkey,
2165 aname => 'DOCUMENT_SUBTYPE');
2166 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2167 itemkey => itemkey,
2168 aname => 'APPROVAL_PATH_ID');
2169
2170 l_draft_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2171 itemkey => itemkey,
2172 aname => 'DRAFT_ID');
2173 -- PAR Approval
2174 l_draft_type := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
2175 itemkey => itemkey,
2176 aname => 'DRAFT_TYPE');
2177
2178 l_progress := '007 start to update action history';
2179 IF (g_po_wf_debug = 'Y') THEN
2180 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2181 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_action : ' || l_action ||
2182 ', l_po_header_id: ' || l_po_header_id ||
2183 ', l_draft_id: ' || l_draft_id ||
2184 ', l_draft_type: ' || l_draft_type ||
2185 ', l_current_recipient_id: ' || l_current_recipient_id);
2186 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_note: ' || l_note);
2187 END IF;
2188
2189 update_action_history(p_action_code => l_action,
2190 p_recipient_id => NULL,
2191 p_note => l_note,
2192 p_po_header_id => l_po_header_id,
2193 p_current_id => l_current_recipient_id,
2194 p_doc_type=> l_doc_type,
2195 p_doc_subtype => l_doc_sub_type,
2196 p_approval_path_id => l_approval_path_id,
2197 p_draft_id => l_draft_id,
2198 p_draft_type => l_draft_type); -- PAR Approval
2199 END IF;
2200
2201 l_progress := '008 PO_WF_PO_NOTIFICATION.post_approval_notif';
2202 IF (g_po_wf_debug = 'Y') THEN
2203 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2204 END IF;
2205 --End of code changes. <bug 14105414>
2206
2207 -- <debug start>
2208 if (wf_engine.preserved_context = TRUE) then
2209 l_preserved_ctx := 'TRUE';
2210 else
2211 l_preserved_ctx := 'FALSE';
2212 end if;
2213 l_progress := 'notif callback preserved_ctx : '||l_preserved_ctx;
2214 IF (g_po_wf_debug = 'Y') THEN
2215 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2216 END IF;
2217 -- <debug end>
2218
2219 -- <debug start>
2220 l_progress := '010 notif callback -responder id : '||l_responder_id;
2221 IF (g_po_wf_debug = 'Y') THEN
2222 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2223 END IF;
2224 --<debug end>
2225 --Bug 5389914
2226 --Fnd_Profile.Get('USER_ID',l_session_user_id);
2227 --Fnd_Profile.Get('RESP_ID',l_session_resp_id);
2228 --Fnd_Profile.Get('RESP_APPL_ID',l_session_appl_id);
2229 l_session_user_id := fnd_global.user_id;
2230 l_session_resp_id := fnd_global.resp_id;
2231 l_session_appl_id := fnd_global.resp_appl_id;
2232
2233 IF (l_session_user_id = -1) THEN
2234 l_session_user_id := NULL;
2235 END IF;
2236
2237 IF (l_session_resp_id = -1) THEN
2238 l_session_resp_id := NULL;
2239 END IF;
2240
2241 IF (l_session_appl_id = -1) THEN
2242 l_session_appl_id := NULL;
2243 END IF;
2244
2245 -- <debug start>
2246 l_progress :='020 notification callback ses_userid: '||l_session_user_id
2247 ||' sess_resp_id '||l_session_resp_id||' sess_appl_id '
2248 ||l_session_appl_id;
2249 IF (g_po_wf_debug = 'Y') THEN
2250 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2251 END IF;
2252 --<debug end>
2253
2254 -- bug 4901406 <start> : need to shift the setting of the preparer resp and appl id
2255 -- to here, it was not initialized inside the if condition if the control went to the
2256 -- else part.
2257 l_preparer_resp_id :=
2258 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
2259 itemkey => itemkey,
2260 aname => 'RESPONSIBILITY_ID');
2261 l_preparer_appl_id :=
2262 PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype=>itemtype,
2263 itemkey => itemkey,
2264 aname => 'APPLICATION_ID');
2265
2266 -- <debug start>
2267 l_progress := '030 notif callback prep resp_id:'||l_preparer_resp_id
2268 ||' prep appl id '||l_preparer_appl_id;
2269 IF (g_po_wf_debug = 'Y') THEN
2270 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2271 END IF;
2272 --<debug end>
2273
2274 -- bug 4901406 <end>
2275
2276
2277 if (l_responder_id is not null) then
2278 if (l_responder_id <> l_session_user_id) then
2279 /* possible in 2 scenarios :
2280 1. when the response is made from email using guest user feature
2281 2. When the response is made from sysadmin login
2282 */
2283
2284
2285
2286 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2287 itemkey => itemkey,
2288 aname => 'RESPONDER_USER_ID',
2289 avalue => l_responder_id);
2290 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2291 itemkey => itemkey,
2292 aname => 'RESPONDER_RESP_ID',
2293 avalue => l_preparer_resp_id);
2294 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2295 itemkey => itemkey,
2296 aname => 'RESPONDER_APPL_ID',
2297 avalue => l_preparer_appl_id);
2298 else
2299 if (l_session_resp_id is null) THEN
2300 /* possible when the response is made from the default worklist
2301 without choosing a valid responsibility */
2302 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2303 itemkey => itemkey,
2304 aname => 'RESPONDER_USER_ID',
2305 avalue => l_responder_id);
2306 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2307 itemkey => itemkey,
2308 aname => 'RESPONDER_RESP_ID',
2309 avalue => l_preparer_resp_id);
2310 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2311 itemkey => itemkey,
2312 aname => 'RESPONDER_APPL_ID',
2313 avalue => l_preparer_appl_id);
2314 else
2315 /* all values available - possible when the response is made
2316 after choosing a correct responsibility */
2317
2318 /* bug 5333226 : If the values of responsibility_id and application
2319 id are available but are incorrect - i.e. not conforming to say the
2320 sls (subledger security). This may happen when a response is made
2321 through the email or the background process picks the wf up.
2322 This may happen due to the fact that the mailer / background process
2323 carries the context set by the notification/wf it processed last*/
2324
2325 if ( l_preserved_ctx = 'TRUE') then
2326 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2327 itemkey => itemkey,
2328 aname => 'RESPONDER_USER_ID',
2329 avalue => l_responder_id);
2330 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2331 itemkey => itemkey,
2332 aname => 'RESPONDER_RESP_ID',
2333 avalue => l_session_resp_id);
2334 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2335 itemkey => itemkey,
2336 aname => 'RESPONDER_APPL_ID',
2337 avalue => l_session_appl_id);
2338 else
2339 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2340 itemkey => itemkey,
2341 aname => 'RESPONDER_USER_ID',
2342 avalue => l_responder_id);
2343 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2344 itemkey => itemkey,
2345 aname => 'RESPONDER_RESP_ID',
2346 avalue => l_preparer_resp_id);
2347 PO_WF_UTIL_PKG.SetItemAttrNumber(itemtype=>itemtype,
2348 itemkey => itemkey,
2349 aname => 'RESPONDER_APPL_ID',
2350 avalue => l_preparer_appl_id);
2351 end if;
2352
2353
2354 end if;
2355 end if;
2356 end if;
2357
2358 -- Context setting revamp <end>
2359
2360
2361 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
2362 l_progress := '040 returning from notif callback -respond mode';
2363 IF (g_po_wf_debug = 'Y') THEN
2364 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2365 END IF;
2366 return;
2367 end if;
2368
2369
2370 -- Don't allow transfer
2371 if (funcmode = 'TRANSFER') then
2372
2373 fnd_message.set_name('PO', 'PO_WF_NOTIF_NO_TRANSFER');
2374 app_exception.raise_exception;
2375
2376 resultout := wf_engine.eng_completed;
2377 return;
2378
2379 end if; -- end if for funcmode = 'TRANSFER'
2380
2381 end post_approval_notif;
2382
2383
2384 /* Bug# 2616433: kagarwal
2385 ** Desc: Added new procedure to set notification subject token in
2386 ** user language.
2387 */
2388
2389 procedure Get_po_user_msg_attribute(itemtype in varchar2,
2390 itemkey in varchar2,
2391 actid in number,
2392 funcmode in varchar2,
2393 resultout out NOCOPY varchar2) IS
2394
2395 l_progress VARCHAR2(100) := '000';
2396 l_doc_string varchar2(200);
2397 l_user_name varchar2(100);
2398 l_preparer_user_name varchar2(100);
2399 l_orgid number;
2400 l_notification_type varchar2(15); --bug 3668188
2401
2402 -- <Start Word Integration 11.5.10+>
2403 l_okc_doc_type varchar2(20);
2404 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
2405 l_document_id NUMBER;
2406 l_document_subtype PO_HEADERS_ALL.type_lookup_code%TYPE;
2407 -- <End Word Integration 11.5.10+>
2408
2409 BEGIN
2410
2411 -- Do nothing in cancel or timeout mode
2412 --
2413 if (funcmode <> wf_engine.eng_run) then
2414 resultout := wf_engine.eng_null;
2415 return;
2416 end if;
2417
2418 l_progress := 'Get_po_user_msg_attribute:001: actid: ' || actid;
2419 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2420
2421 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2422 itemkey => itemkey,
2423 aname => 'ORG_ID');
2424
2425 IF l_orgid is NOT NULL THEN
2426 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
2427 END IF;
2428
2429 l_progress := 'Get_po_user_msg_attribute:010: orgid: ' || l_orgid;
2430 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2431
2432 l_user_name := wf_engine.GetActivityAttrText (itemtype => itemtype,
2433 itemkey => itemkey,
2434 actid => actid,
2435 aname => 'NTF_USER_NAME',
2436 ignore_notfound => TRUE);
2437
2438 PO_WF_PO_NOTIFICATION.GetDisplayValue(itemtype, itemkey, l_user_name);
2439
2440
2441 l_progress := 'Get_po_user_msg_attribute:015: username: ' || l_user_name;
2442 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2443
2444
2445 -- Bug 3668188: added the NTF_USER_ROLE Activity Attribute, which is
2446 -- a constant with value 'SUBMITTER' or 'APPROVER'. This distinguishes
2447 -- between the 3 different notification-functions that share this procedure.
2448 l_notification_type := wf_engine.GetActivityAttrText (
2449 itemtype => itemtype,
2450 itemkey => itemkey,
2451 actid => actid,
2452 aname => 'NTF_USER_ROLE',
2453 ignore_notfound => TRUE);
2454
2455 l_progress := 'Get_po_user_msg_attribute:020: notif type: ' || l_notification_type;
2456 -- DEBUG
2457 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2458
2459
2460 -- Bug 3668188: Removed old logic from bug 3564727 and replaced it
2461 -- with a call to is_open_document_allowed, which has updated logic.
2462 IF NOT (PO_WF_PO_NOTIFICATION.is_open_document_allowed(
2463 p_itemtype => itemtype
2464 , p_itemkey => itemkey
2465 , p_notification_type => l_notification_type)
2466 ) THEN
2467 l_progress := 'Get_po_approver_msg_attribute: 040: NULL open form';
2468 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2469
2470 -- HTML Orders R12
2471 -- Set the URL and form attributes
2472 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
2473 itemkey => itemkey,
2474 aname => 'OPEN_FORM_COMMAND' ,
2475 avalue => '');
2476
2477 -- PO AME Project : VIEW_DOC_URL must always be seen even though po is in PRE-APPROVED state.
2478 /*PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
2479 itemkey => itemkey,
2480 aname => 'VIEW_DOC_URL' ,
2481 avalue => '');*/
2482
2483 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemType,
2484 itemkey => itemkey,
2485 aname => 'EDIT_DOC_URL' ,
2486 avalue => '');
2487 END IF;
2488
2489
2490 -- <Start Word Integration 11.5.10+>
2491
2492 l_conterms_exist_flag := PO_WF_UTIL_PKG.GetItemAttrText(
2493 itemtype => itemtype,
2494 itemkey => itemkey,
2495 aname => 'CONTERMS_EXIST_FLAG');
2496
2497 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText(
2498 itemtype => itemtype,
2499 itemkey => itemkey,
2500 aname => 'DOCUMENT_SUBTYPE');
2501
2502 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber(
2503 itemtype => itemtype,
2504 itemkey => itemkey,
2505 aname => 'DOCUMENT_ID');
2506
2507
2508 /* Set or reset the okc doc attachment attribute */
2509
2510 IF (l_conterms_exist_flag = 'Y')
2511 THEN
2512 l_okc_doc_type :=
2513 PO_CONTERMS_UTL_GRP.get_po_contract_doctype(l_document_subtype);
2514
2515 IF (('STRUCTURED' <>
2516 OKC_TERMS_UTIL_GRP.get_contract_source_code(p_document_type => l_okc_doc_type
2517 , p_document_id => l_document_id))
2518 AND
2519 ('N' =
2520 OKC_TERMS_UTIL_GRP.is_primary_terms_doc_mergeable(
2521 P_document_type => l_okc_doc_type
2522 , p_document_id => l_document_id))
2523 AND
2524 (PO_COMMUNICATION_PVT.PO_COMMUNICATION_PROFILE = 'T')
2525 )
2526 THEN
2527
2528 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
2529 itemkey => itemkey,
2530 aname => 'OKC_DOC_ATTACHMENT',
2531 avalue =>
2532 'PLSQLBLOB:PO_COMMUNICATION_PVT.OKC_DOC_ATTACH/'||
2533 itemtype||':'||itemkey);
2534
2535 ELSE
2536
2537 /* Contract terms are structured, or attached document is mergeable.
2538 * All contract terms will be in pdf; no need for other okc doc attachment.
2539 */
2540
2541 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
2542 itemkey => itemkey,
2543 aname => 'OKC_DOC_ATTACHMENT',
2544 avalue => '');
2545
2546 END IF /* not structured and not mergeable */;
2547
2548 END IF; /* l_conterms_exist_flag = 'Y' */
2549
2550
2551 -- <End Word Integration 11.5.10+>
2552
2553
2554
2555 l_progress := 'Get_po_approver_msg_attribute: 999';
2556 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2557
2558 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
2559
2560 EXCEPTION
2561 WHEN OTHERS THEN
2562 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2563 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2564 wf_core.context('PO_WF_PO_NOTIFICATION','Get_req_approval_msg_attribute',l_progress);
2565 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
2566 l_doc_string, sqlerrm, 'PO_WF_PO_NOTIFICATION.Get_po_approver_msg_attribute');
2567 raise;
2568
2569 END Get_po_user_msg_attribute;
2570
2571
2572 /* Bug# 2616433: kagarwal
2573 ** Desc: Added new procedure to set doc type display according to the
2574 ** default language of approver or preparer.
2575 */
2576
2577 procedure GetDisplayValue(itemtype in varchar2,
2578 itemkey in varchar2,
2579 username in varchar2,
2580 doctype in varchar2,
2581 docsubtype in varchar2) IS
2582
2583 l_progress VARCHAR2(400) := '000';
2584 l_doc_type varchar2(25);
2585 l_doc_subtype varchar2(25);
2586 l_doc_disp varchar2(240);
2587 l_ga_flag varchar2(1) := null;
2588
2589 l_display_name varchar2(240);
2590 l_email_address varchar2(240);
2591 l_notification_preference varchar2(240);
2592 l_language varchar2(240);
2593 l_territory varchar2(240);
2594 l_msg_text varchar2(2000) := NULL; -- Bug 3430545
2595 l_language_code fnd_languages.language_code%TYPE;
2596
2597 cursor c_lookup_value_user(p_doc_type varchar2, p_doc_subtype varchar2,
2598 p_language varchar2) is
2599 select type_name
2600 from po_document_types_tl tl, FND_LANGUAGES fl
2601 where fl.nls_language = p_language
2602 and tl.LANGUAGE = fl.language_code
2603 and tl.document_type_code = p_doc_type
2604 and tl.document_subtype = p_doc_subtype;
2605
2606 cursor c_lookup_value_doc(p_doc_type varchar2, p_doc_subtype varchar2) is
2607 select type_name
2608 from po_document_types
2609 where document_type_code = p_doc_type
2610 and document_subtype = p_doc_subtype;
2611
2612 l_document_id PO_HEADERS_ALL.po_header_id%TYPE; --<R12 STYLES PHASE II>
2613 l_draft_id NUMBER; -- CLM Apprvl
2614 l_draft_type PO_DRAFTS.DRAFT_TYPE%TYPE; -- PAR Approval
2615
2616 BEGIN
2617 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 001, user name: '
2618 || username;
2619 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2620
2621 if ((doctype is NULL) or (docsubtype is null)) then
2622 l_doc_type := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
2623 itemkey => itemkey,
2624 aname => 'DOCUMENT_TYPE');
2625
2626 l_doc_subtype := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
2627 itemkey => itemkey,
2628 aname => 'DOCUMENT_SUBTYPE');
2629 else
2630 l_doc_type := doctype;
2631 l_doc_subtype := docsubtype;
2632 end if;
2633
2634 IF l_doc_type = 'PA' AND l_doc_subtype = 'BLANKET' THEN
2635
2636 l_ga_flag := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
2637 itemkey => itemkey,
2638 aname => 'GLOBAL_AGREEMENT_FLAG');
2639 END IF;
2640
2641 /* Bug 3430545: Modified the code to get the translated values for the wf
2642 notification attribute 'REQUIRES_APPROVAL_MSG' and 'PO_GA_TYPE'.
2643 Deleted the previous code and revamped it.
2644 */
2645 --<R12 STYLES PHASE II START>
2646 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2647 (itemtype => itemtype,
2648 itemkey => itemkey,
2649 aname => 'DOCUMENT_ID');
2650
2651 -- CLM Apprvl
2652 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2653 (itemtype => itemtype,
2654 itemkey => itemkey,
2655 aname => 'DRAFT_ID');
2656
2657 -- PAR Approval
2658 l_draft_type := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
2659 itemkey => itemkey,
2660 aname => 'DRAFT_TYPE');
2661
2662 IF username is NULL THEN
2663 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 050';
2664 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2665
2666 IF l_doc_type = 'PA' AND l_doc_subtype IN ('BLANKET','CONTRACT') OR
2667 l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD' then
2668 -- CLM Apprvl. In case of modification documents that document type display needs to be 'Modification'
2669 -- PAr Approval. For PAr, documnet type display is 'Post Award Request' and it is based on draft type.
2670 IF l_draft_id is not null AND l_draft_id <> -1 AND l_draft_type = 'MOD' then
2671 l_doc_disp:= 'Modification';
2672 -- PAR Approval
2673 ELSIF l_draft_id is not null AND l_draft_id <> -1 AND l_draft_type = 'PAR' then
2674 l_doc_disp:= 'Post Award Request';
2675 ELSE
2676 l_doc_disp:= PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id);
2677 END IF;
2678
2679 ELSE
2680 OPEN c_lookup_value_doc(l_doc_type, l_doc_subtype);
2681 FETCH c_lookup_value_doc into l_doc_disp;
2682 CLOSE c_lookup_value_doc;
2683 END IF;
2684 ELSE
2685 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 060';
2686 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2687 WF_DIRECTORY.GETROLEINFO(
2688 username,
2689 l_display_name,
2690 l_email_address,
2691 l_notification_preference,
2692 l_language,
2693 l_territory);
2694 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 080, language: '
2695 || l_language;
2696 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2697
2698 BEGIN
2699
2700 SELECT language_code
2701 INTO l_language_code
2702 FROM fnd_languages
2703 WHERE nls_language = l_language;
2704
2705 EXCEPTION
2706 WHEN OTHERS THEN
2707 l_language_code := NULL;
2708 END;
2709
2710 IF l_doc_type = 'PA' AND l_doc_subtype IN ('BLANKET','CONTRACT') OR
2711 l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD' then
2712 -- CLM Apprvl. In case of modification documents that document type display needs to be 'Modification'
2713 -- PAR Approval. For PAr, documnet type display is 'Post Award Request' and it is based on draft type.
2714 IF l_draft_id is not null AND l_draft_id <> -1 AND l_draft_type = 'MOD' then
2715 l_doc_disp:= 'Modification';
2716 -- PAR Approval
2717 ELSIF l_draft_id is not null AND l_draft_id <> -1 AND l_draft_type = 'PAR' then
2718 l_doc_disp:= 'Post Award Request';
2719 ELSE
2720 l_doc_disp:= PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_document_id);
2721 END IF;
2722
2723 ELSE
2724 OPEN c_lookup_value_user(l_doc_type, l_doc_subtype, l_language);
2725 FETCH c_lookup_value_user into l_doc_disp;
2726 CLOSE c_lookup_value_user;
2727 END IF;
2728 END IF; /* if username is null */
2729 --<R12 STYLES PHASE II END>
2730 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: 100, type disp: '
2731 || l_doc_disp;
2732 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2733
2734 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
2735 itemkey => itemkey,
2736 aname => 'DOCUMENT_TYPE_DISP',
2737 avalue => l_doc_disp);
2738 BEGIN
2739 select message_text
2740 into l_msg_text
2741 from fnd_new_messages fm,fnd_languages fl
2742 where fm.message_name = 'PO_WF_NOTIF_REQUIRES_APPROVAL'
2743 and fm.language_code = fl.language_code
2744 and fl.nls_language = l_language
2745 and fm.application_id = 201; --<BUG 3712124> Include application_id to better use PK index
2746 EXCEPTION
2747 WHEN OTHERS THEN
2748 l_msg_text := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
2749 itemkey => itemkey,
2750 aname => 'REQUIRES_APPROVAL_MSG');
2751 END;
2752
2753 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
2754 itemkey => itemkey,
2755 aname => 'REQUIRES_APPROVAL_MSG',
2756 avalue => l_msg_text );
2757
2758 /* PO AME Project start :
2759 Fetching and setting REQUIRES_REVIEW_MSG attribute */
2760 BEGIN
2761 select message_text
2762 into l_msg_text
2763 from fnd_new_messages fm,fnd_languages fl
2764 where fm.message_name = 'PO_WF_NOTIF_REQUIRES_REVIEW'
2765 and fm.language_code = fl.language_code
2766 and fl.nls_language = l_language
2767 and fm.application_id = 201;
2768 EXCEPTION
2769 WHEN OTHERS THEN
2770 l_msg_text := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
2771 itemkey => itemkey,
2772 aname => 'REQUIRES_REVIEW_MSG');
2773 END;
2774
2775 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
2776 itemkey => itemkey,
2777 aname => 'REQUIRES_REVIEW_MSG',
2778 avalue => l_msg_text );
2779
2780 BEGIN
2781 select message_text
2782 into l_msg_text
2783 from fnd_new_messages fm,fnd_languages fl
2784 where fm.message_name = 'PO_WF_NOTIF_REQUIRES_ESIGN'
2785 and fm.language_code = fl.language_code
2786 and fl.nls_language = l_language
2787 and fm.application_id = 201;
2788 EXCEPTION
2789 WHEN OTHERS THEN
2790 l_msg_text := PO_WF_UTIL_PKG.GetItemAttrText(itemtype => itemtype,
2791 itemkey => itemkey,
2792 aname => 'REQUIRES_ESIGN_MSG');
2793 END;
2794
2795 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
2796 itemkey => itemkey,
2797 aname => 'REQUIRES_ESIGN_MSG',
2798 avalue => l_msg_text );
2799
2800 /* PO AME Project end */
2801
2802 EXCEPTION
2803 WHEN OTHERS THEN
2804 l_progress := 'PO_WF_PO_NOTIFICATION.GetDisplayValue: sql err: ' || sqlerrm;
2805 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2806 IF (c_lookup_value_user%ISOPEN) THEN
2807 CLOSE c_lookup_value_user;
2808 END IF;
2809 IF (c_lookup_value_doc%ISOPEN) THEN
2810 CLOSE c_lookup_value_doc;
2811 END IF;
2812
2813 END GetDisplayValue;
2814
2815 ------------------------------------------------------------------<BUG 3607009>
2816 -------------------------------------------------------------------------------
2817 --Start of Comments
2818 --Name: is_open_document_allowed
2819 --Pre-reqs:
2820 -- None.
2821 --Modifies:
2822 -- None
2823 --Locks:
2824 -- None.
2825 --Function:
2826 -- Determines if the Open Document icon should be shown in the
2827 -- PO Approval Notification. The Open Document should not be shown if...
2828 -- (1) the document is in 'Pre-Approved' state and
2829 -- (2) document signature is required.
2830 --Parameters:
2831 --IN:
2832 --p_itemtype
2833 -- Standard parameter to be used in a workflow procedure
2834 --p_itemkey
2835 -- Standard parameter to be used in a workflow procedure
2836 --p_notification_type
2837 -- Specifies whether this notification is for the Preparer/Submitter
2838 -- or an Approver/Reviewer
2839 -- The value is derived from the WF Function attribute NTF_USER_ROLE
2840 -- in the GET_<>_NOTIFICATION_ATTRIBUTE functions in POAPPRV workflow
2841 -- Added for bug 3668188
2842 --Returns:
2843 --resultout
2844 -- A BOOLEAN TRUE if the Open Document icon should be shown, FALSE otherwise.
2845 --Testing:
2846 -- N/A
2847 --End of Comments
2848 -------------------------------------------------------------------------------
2849 -------------------------------------------------------------------------------
2850 FUNCTION is_open_document_allowed
2851 (
2852 p_itemtype IN VARCHAR2
2853 , p_itemkey IN VARCHAR2
2854 , p_notification_type IN VARCHAR2 --bug 3668188
2855 )
2856 RETURN BOOLEAN
2857 IS
2858 l_api_name VARCHAR2(30) := 'is_open_document_allowed';
2859 l_log_head VARCHAR2(100) := g_pkg_name||'.'||l_api_name;
2860 l_progress VARCHAR2(3);
2861
2862 l_authorization_status PO_HEADERS_ALL.authorization_status%TYPE;
2863 l_result BOOLEAN := TRUE;
2864
2865 BEGIN
2866
2867 l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress || 'Notification Type = ' || p_notification_type);
2868
2869 -- Get the Authorization Status (e.g. 'PRE-APPROVED','APPROVED',
2870 -- 'INCOMPLETE', etc.) of the document.
2871 --
2872 l_authorization_status := wf_engine.GetItemAttrText
2873 ( itemtype => p_itemtype
2874 , itemkey => p_itemkey
2875 , aname => 'AUTHORIZATION_STATUS'
2876 );
2877
2878 l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Authorization Status = '||l_authorization_status);
2879
2880
2881 -- bug 3668188: changed the Open Doc allowed logic.
2882 -- If document is ('Pre-Approved' or 'In Process') and the
2883 -- notification is going back to the Submitter, then that
2884 -- user should not be able to open the document for edit.
2885 --
2886 IF (l_authorization_status IN ('PRE-APPROVED', 'IN PROCESS')
2887 AND nvl(p_notification_type, 'SUBMITTER') = 'SUBMITTER')
2888 THEN
2889 l_progress:='020';
2890 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||':FALSE');
2891 l_result := FALSE;
2892 END IF;
2893
2894 l_progress:='030';
2895 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||':TRUE');
2896 return (l_result);
2897
2898 EXCEPTION
2899
2900 WHEN OTHERS THEN
2901 PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||SQLERRM);
2902 RAISE;
2903
2904 END is_open_document_allowed;
2905
2906 ------------------<BUG 13951919>-----------------------------------------------
2907 -------------------------------------------------------------------------------
2908 --Start of Comments
2909 --Name: update_action_history
2910 --Pre-reqs:
2911 -- None.
2912 --Modifies:
2913 -- None
2914 --Locks:
2915 -- None.
2916 --Function:
2917 -- This procedure updates the po_action_history table based on the approvers response.
2918 --Parameters:
2919 --IN:
2920 --p_action_code
2921 --p_recipient_id
2922 --p_note
2923 --p_po_header_id
2924 --p_current_id
2925 --p_doc_type
2926 --p_doc_subtype
2927 --p_approval_path_id --<bug 14105414>
2928 --p_draft_id
2929
2930 --OUT:
2931
2932 --End of Comments
2933 -------------------------------------------------------------------------------
2934 -------------------------------------------------------------------------------
2935
2936 PROCEDURE update_action_history (p_action_code IN VARCHAR2,
2937 p_recipient_id IN NUMBER,
2938 p_note IN VARCHAR2,
2939 p_po_header_id IN NUMBER,
2940 p_current_id IN NUMBER,
2941 p_doc_type IN po_action_history.OBJECT_TYPE_CODE%TYPE,
2942 p_doc_subtype IN po_action_history.OBJECT_SUB_TYPE_CODE%TYPE,
2943 p_approval_path_id IN PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE, --<bug 14105414>
2944 p_draft_id IN NUMBER,
2945 p_draft_type IN VARCHAR2) --PAR Approval
2946 IS
2947 pragma AUTONOMOUS_TRANSACTION;
2948
2949 l_progress VARCHAR2(100) := '000';
2950
2951 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
2952 l_object_revision_num PO_ACTION_HISTORY.OBJECT_REVISION_NUM%TYPE;
2953 l_approval_path_id PO_ACTION_HISTORY.APPROVAL_PATH_ID%TYPE;
2954 l_request_id PO_ACTION_HISTORY.REQUEST_ID%TYPE;
2955 l_program_application_id PO_ACTION_HISTORY.PROGRAM_APPLICATION_ID%TYPE;
2956 l_program_date PO_ACTION_HISTORY.PROGRAM_DATE%TYPE;
2957 l_program_id PO_ACTION_HISTORY.PROGRAM_ID%TYPE;
2958
2959 l_document_id NUMBER;
2960 l_doc_sub_type po_action_history.OBJECT_SUB_TYPE_CODE%TYPE;
2961
2962 BEGIN
2963
2964 -- Handling mod approval case.
2965 if(nvl(p_draft_id, -1) = -1) then
2966 l_document_id := p_po_header_id;
2967 l_doc_sub_type := p_doc_subtype;
2968 else
2969 l_document_id := p_draft_id;
2970 -- PAR Approval
2971 IF p_draft_type = 'MOD' THEN
2972 l_doc_sub_type := 'MODIFICATION';
2973 ELSIF p_draft_type ='PAR' THEN
2974 l_doc_sub_type := 'POST_AWARD_REQUEST';
2975 END IF;
2976 end if;
2977
2978 --Start of code changes: <bug 14105414>
2979 SELECT max(sequence_num)
2980 INTO l_sequence_num
2981 FROM PO_ACTION_HISTORY
2982 WHERE object_id = l_document_id
2983 AND object_type_code = p_doc_type
2984 AND object_sub_type_code = l_doc_sub_type;
2985
2986
2987 SELECT object_revision_num,
2988 approval_path_id,
2989 request_id,
2990 program_application_id,
2991 program_date,
2992 program_id
2993 INTO l_object_revision_num,
2994 l_approval_path_id,
2995 l_request_id,
2996 l_program_application_id,
2997 l_program_date,
2998 l_program_id
2999 FROM PO_ACTION_HISTORY
3000 WHERE object_id = l_document_id
3001 AND object_type_code = p_doc_type
3002 AND object_sub_type_code = l_doc_sub_type
3003 AND employee_id = p_current_id
3004 AND ACTION_CODE IS NULL
3005 AND rownum =1;
3006 --End of code changes. <bug 14105414>
3007
3008 l_progress := '010';
3009
3010 -- Update action history with the action details
3011 UPDATE PO_ACTION_HISTORY
3012 SET last_update_date = sysdate,
3013 last_updated_by = fnd_global.user_id,
3014 last_update_login = fnd_global.login_id ,
3015 action_date = sysdate,
3016 action_code = p_action_code,
3017 note = p_note,
3018 approval_path_id = nvl(p_approval_path_id, l_approval_path_id), --<bug 14105414>
3019 offline_code = NULL
3020 WHERE employee_id = p_current_id
3021 AND object_id = l_document_id
3022 AND object_type_code = p_doc_type
3023 AND object_sub_type_code = l_doc_sub_type
3024 AND action_code IS NULL;
3025
3026 l_progress := '020';
3027
3028 IF p_recipient_id IS NOT NULL THEN --<bug 14105414>
3029 INSERT INTO PO_ACTION_HISTORY
3030 (object_id,
3031 object_type_code,
3032 object_sub_type_code,
3033 sequence_num,
3034 last_update_date,
3035 last_updated_by,
3036 employee_id,
3037 action_code,
3038 action_date,
3039 note,
3040 object_revision_num,
3041 last_update_login,
3042 creation_date,
3043 created_by,
3044 request_id,
3045 program_application_id,
3046 program_id,
3047 program_date,
3048 approval_path_id,
3049 offline_code,
3050 program_update_date
3051 )
3052 VALUES (l_document_id,
3053 p_doc_type,
3054 l_doc_sub_type,
3055 l_sequence_num + 1,
3056 sysdate,
3057 fnd_global.user_id,
3058 p_recipient_id,
3059 NULL,
3060 NULL,
3061 NULL,
3062 l_object_revision_num,
3063 fnd_global.login_id,
3064 sysdate,
3065 fnd_global.user_id,
3066 l_request_id,
3067 l_program_application_id,
3068 l_program_id,
3069 l_program_date,
3070 l_approval_path_id,
3071 NULL,
3072 sysdate);
3073 END IF; --<bug 14105414>
3074 l_progress := '030';
3075
3076 commit;
3077 EXCEPTION
3078 WHEN OTHERS THEN
3079 wf_core.context('PO_WF_PO_NOTIFICATION','update_action_history',l_progress,sqlerrm);
3080 RAISE;
3081 END;
3082
3083 END PO_WF_PO_NOTIFICATION;