[Home] [Help]
PACKAGE BODY: APPS.PO_POAPPROVAL_INIT1
Source
1 PACKAGE BODY PO_POAPPROVAL_INIT1 AS
2 /* $Header: POXWPA2B.pls 120.35.12020000.6 2013/03/22 06:47:06 kuichen ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on; --< Bug 3554754 >
7
8 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_POAPPROVAL_INIT1';
9 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
10
11 /*=======================================================================+
12 | FILENAME
13 | POXWPA2B.pls
14 |
15 | DESCRIPTION
16 | PL/SQL body for package: PO_POAPPROVAL_INIT1
17 |
18 | NOTES Ben Chihaoui Created 6/15/97
19 | MODIFIED (MM/DD/YY)
20 | Eric Ma 13/04/2009 Add code for PO notification of Indian Localization
21 *=======================================================================*/
22 --
23
24 Cursor GetPOHdr_csr(p_po_header_id NUMBER) RETURN POHdrRecord is
25 select PO_HEADER_ID,COMMENTS,AUTHORIZATION_STATUS,
26 TYPE_LOOKUP_CODE,AGENT_ID,SEGMENT1,CLOSED_CODE,CURRENCY_CODE
27 from po_headers_all
28 where PO_HEADER_ID = p_po_header_id;
29
30
31 Cursor GetDraftPOHdr_csr(p_po_header_id NUMBER, p_draft_id number) RETURN POHdrRecord is
32 select PO_HEADER_ID,COMMENTS,AUTHORIZATION_STATUS,
33 TYPE_LOOKUP_CODE,AGENT_ID,SEGMENT1,CLOSED_CODE,CURRENCY_CODE
34 from po_headers_draft_all
35 where PO_HEADER_ID = p_po_header_id
36 and draft_id = p_draft_id;
37
38 Cursor GetRelHdr_csr(p_rel_header_id NUMBER) RETURN RelHdrRecord is
39 select PORL.PO_RELEASE_ID,PORL.PO_HEADER_ID,PORL.AUTHORIZATION_STATUS,
40 PORL.RELEASE_TYPE,PORL.AGENT_ID,PORL.RELEASE_NUM,PORL.CLOSED_CODE,
41 POH.SEGMENT1, POH.CURRENCY_CODE, POH.COMMENTS
42 -- Bug 10140786 Selected comments to set PO_DESCRIPTION in release workflow.
43 from po_releases_all PORL, po_headers_all POH
44 where PORL.PO_RELEASE_ID = p_rel_header_id
45 and PORL.po_header_id = POH.po_header_id;
46
47 -- The following are local/Private procedure that support the workflow APIs:
48
49 procedure getPOAttributes(p_po_header_id in NUMBER,
50 itemtype in varchar2,
51 itemkey in varchar2);
52 --
53 procedure SetPOHdrAttributes(itemtype in varchar2, itemkey in varchar2);
54
55 --
56 procedure getRelAttributes(p_rel_header_id in NUMBER,
57 itemtype in varchar2,
58 itemkey in varchar2);
59 --
60 procedure SetRelHdrAttributes(itemtype in varchar2, itemkey in varchar2);
61
62 --
63 -- Get_PO_Attributes
64 -- Get the requisition values on the doc header and assigns then to workflow attributes
65 --
66 procedure Get_PO_Attributes( itemtype in varchar2,
67 itemkey in varchar2,
68 actid in number,
69 funcmode in varchar2,
70 resultout out NOCOPY varchar2 ) is
71 l_orgid NUMBER;
72 l_po_header_id NUMBER;
73 l_doc_type VARCHAR2(25);
74 l_authorization_status VARCHAR2(25);
75 x_progress varchar2(100);
76
77 l_doc_string varchar2(200);
78 l_preparer_user_name varchar2(100);
79
80
81 l_po_revision_num_curr NUMBER;
82 l_po_revision_num_orig NUMBER;
83
84
85 BEGIN
86
87 x_progress := 'PO_POAPPROVAL_INIT1.Get_PO_Attributes: 01';
88 IF (g_po_wf_debug = 'Y') THEN
89 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
90 END IF;
91
92
93 -- Do nothing in cancel or timeout mode
94 --
95 if (funcmode <> wf_engine.eng_run) then
96
97 resultout := wf_engine.eng_null;
98 return;
99
100 end if;
101 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
102 itemkey => itemkey,
103 aname => 'ORG_ID');
104
105 IF l_orgid is NOT NULL THEN
106
107 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
108
109 END IF;
110
111 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
112 itemkey => itemkey,
113 aname => 'DOCUMENT_ID');
114
115
116 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
117 itemkey => itemkey,
118 aname => 'DOCUMENT_TYPE');
119
120 IF l_doc_type IN ('PO','PA') THEN
121
122
123 GetPOAttributes(l_po_header_id,itemtype,itemkey);
124
125 ELSE
126
127 GetRelAttributes(l_po_header_id,itemtype,itemkey);
128
129 END IF;
130
131 -- code added for bug 8291565 FP
132 -- for blocking FYI notification to web supplier users when there is no change in the revision number of the PO
133
134 l_po_revision_num_curr := wf_engine.GetItemAttrText (itemtype => itemtype,
135 itemkey => itemkey,
136 aname => 'REVISION_NUMBER');
137
138 IF l_doc_type IN ('PO', 'PA') THEN
139
140 SELECT (Nvl (comm_rev_num, -1))
141 INTO l_po_revision_num_orig
142 FROM po_headers_all
143 WHERE po_header_id = l_po_header_id;
144
145 -- added for bug 9072034 (to check revision number for releases.)
146 ELSIF l_doc_type in ('RELEASE') THEN
147
148 SELECT (Nvl (comm_rev_num, -1))
149 INTO l_po_revision_num_orig
150 FROM po_releases_all
151 WHERE po_release_id = l_po_header_id;
152
153 END IF;
154
155 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
156 itemkey => itemkey,
157 aname => 'OLD_PO_REVISION_NUM',
158 AVALUE => l_po_revision_num_orig);
159
160 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
161 itemkey => itemkey,
162 aname => 'NEW_PO_REVISION_NUM',
163 AVALUE => l_po_revision_num_curr);
164
165 IF (l_po_revision_num_orig >= 0 ) THEN
166
167 IF l_po_revision_num_curr = l_po_revision_num_orig THEN
168
169 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
170 itemkey => itemkey,
171 aname => 'HAS_REVISION_NUM_INCREMENTED',
172 avalue => 'N');
173
174 ELSE
175
176 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
177 itemkey => itemkey,
178 aname => 'HAS_REVISION_NUM_INCREMENTED',
179 avalue => 'Y');
180
181 END IF;
182
183 ELSE
184
185 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
186 itemkey => itemkey,
187 aname => 'HAS_REVISION_NUM_INCREMENTED',
188 avalue => 'Y');
189
190 END IF;
191
192 -- end of code added for bug 8291565 FP
193
194 --
195 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
196 --
197 x_progress := 'PO_POAPPROVAL_INIT1.Get_PO_Attributes: 02';
198 IF (g_po_wf_debug = 'Y') THEN
199 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
200 END IF;
201
202
203 EXCEPTION
204
205 WHEN OTHERS THEN
206 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
207 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
208 wf_core.context('PO_POAPPROVAL_INIT1','Get_PO_Attributes',x_progress);
209 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_POAPPROVAL_INIT1.GET_PO_ATTRIBUTES');
210 raise;
211
212 END Get_PO_Attributes;
213
214
215 --
216 -- Is_this_new_doc
217 -- Is this a new document or is this a change order.
218 --
219 procedure Is_this_new_doc( itemtype in varchar2,
220 itemkey in varchar2,
221 actid in number,
222 funcmode in varchar2,
223 resultout out NOCOPY varchar2 ) is
224 l_doc_status varchar2(25);
225 l_approved_date DATE;
226 l_doc_id NUMBER;
227 l_doc_type VARCHAR2(25);
228 l_orgid NUMBER;
229 x_progress varchar2(100);
230
231 l_doc_string varchar2(200);
232 l_preparer_user_name varchar2(100);
233
234 l_is_new_document VARCHAR2(1); -- <SVC_NOTIFICATIONS FPJ>
235 l_proc_name VARCHAR2(30) := 'is_this_new_doc';
236 l_return_status VARCHAR2(1); --< Bug 3554754 >
237
238 BEGIN
239
240 x_progress := 'PO_POAPPROVAL_INIT1.Is_this_new_doc: 01';
241 IF (g_po_wf_debug = 'Y') THEN
242 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
243 END IF;
244
245
246 -- Do nothing in cancel or timeout mode
247 --
248 if (funcmode <> wf_engine.eng_run) then
249
250 resultout := wf_engine.eng_null;
251 return;
252
253 end if;
254
255 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
256 itemkey => itemkey,
257 aname => 'ORG_ID');
258
259 IF l_orgid is NOT NULL THEN
260
261 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
262
263 END IF;
264
265
266 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
267 itemkey => itemkey,
268 aname => 'DOCUMENT_ID');
269
270
271 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
272 itemkey => itemkey,
273 aname => 'DOCUMENT_TYPE');
274
275 --< Bug 3554754 Start >
276 get_approved_date( p_doc_type => l_doc_type
277 , p_doc_id => l_doc_id
278 , x_return_status => l_return_status
279 , x_approved_date => l_approved_date
280 );
281 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
282 RAISE FND_API.g_exc_unexpected_error;
283 END IF;
284 --< Bug 3554754 End >
285
286 /* If the doc does not have an approved date, then it's new */
287 IF l_approved_date IS NULL THEN
288 l_is_new_document := 'Y'; -- <SVC_NOTIFICATIONS FPJ>
289 ELSE
290 l_is_new_document := 'N'; -- <SVC_NOTIFICATIONS FPJ>
291 END IF;
292
293 -- <SVC_NOTIFICATIONS FPJ START>
294 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
295 itemkey => itemkey,
296 aname => 'IS_NEW_DOCUMENT',
297 avalue => l_is_new_document );
298
299 resultout := wf_engine.eng_completed || ':' || l_is_new_document;
300 -- <SVC_NOTIFICATIONS FPJ END>
301
302 x_progress := 'PO_POAPPROVAL_INIT1.Is_this_new_doc: 02: ' || l_is_new_document;
303 IF (g_po_wf_debug = 'Y') THEN
304 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
305 END IF;
306
307
308 EXCEPTION
309
310 WHEN OTHERS THEN
311 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
312 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
313 wf_core.context('PO_POAPPROVAL_INIT1','Is_this_new_doc',x_progress);
314 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_POAPPROVAL_INIT1.IS_THIS_NEW_DOC');
315 raise;
316
317 END Is_this_new_doc;
318
319 -- Is_Acceptance_Required
320 -- Is Acceptance required on this Document
321 --
322 -- IN
323 -- itemtype -- itemkey -- actid -- funcmode
324 -- OUT
325 -- Resultout
326 -- - Y/N
327 --
328
329 procedure Is_Acceptance_Required( itemtype in varchar2,
330 itemkey in varchar2,
331 actid in number,
332 funcmode in varchar2,
333 resultout out NOCOPY varchar2 ) is
334
335
336 l_orgid NUMBER;
337 l_po_header_id NUMBER;
338 l_doc_type VARCHAR2(25);
339 l_acceptance_required VARCHAR2(1);
340 l_acceptance_due_date DATE;
341 x_progress varchar2(100);
342
343 l_doc_string varchar2(200);
344 l_preparer_user_name varchar2(100);
345
346 BEGIN
347
348 x_progress := 'PO_POAPPROVAL_INIT1.Is_Acceptance_Required: 01';
349 IF (g_po_wf_debug = 'Y') THEN
350 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
351 END IF;
352
353
354 -- Do nothing in cancel or timeout mode
355 --
356 if (funcmode <> wf_engine.eng_run) then
357
358 resultout := wf_engine.eng_null;
359 return;
360
361 end if;
362
363 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
364 itemkey => itemkey,
365 aname => 'ORG_ID');
366
367 IF l_orgid is NOT NULL THEN
368
369 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
370
371 END IF;
372
373
374 l_po_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
375 itemkey => itemkey,
376 aname => 'DOCUMENT_ID');
377
378 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
379 itemkey => itemkey,
380 aname => 'DOCUMENT_TYPE');
381
382 IF l_doc_type IN ('PO','PA') THEN
383
384 select acceptance_required_flag,acceptance_due_date
385 into l_acceptance_required, l_acceptance_due_date
386 from po_headers
387 where po_header_id= l_po_header_id;
388
389 ELSIF l_doc_type='RELEASE' THEN
390
391 select acceptance_required_flag,acceptance_due_date
392 into l_acceptance_required, l_acceptance_due_date
393 from po_releases
394 where po_release_id= l_po_header_id;
395
396 END IF;
397
398 IF NVL(l_acceptance_required,'N') = 'Y' THEN
399
400 resultout := wf_engine.eng_completed || ':' || 'Y';
401
402 ELSE
403
404 resultout := wf_engine.eng_completed || ':' || 'N';
405
406 END IF;
407
408 x_progress := 'PO_POAPPROVAL_INIT1.Is_Acceptance_Required: 02';
409 IF (g_po_wf_debug = 'Y') THEN
410 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,x_progress);
411 END IF;
412
413 EXCEPTION
414 WHEN OTHERS THEN
415 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
416 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
417 wf_core.context('PO_POAPPROVAL_INIT1','Is_Acceptance_Required',x_progress);
418 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_POAPPROVAL_INIT1.IS_ACCEPTANCE_REQUIRED');
419 raise;
420
421 END Is_Acceptance_Required;
422
423 /****************************************************************************
424 * The Following are the supporting APIs to the workflow functions.
425 * These API's are Private (Not declared in the Package specs).
426 ****************************************************************************/
427
428 procedure GetPOAttributes(p_po_header_id in NUMBER,
429 itemtype in varchar2,
430 itemkey in varchar2) is
431
432 x_progress varchar2(100) := '000';
433 l_draft_id number; -- CLM Aprvl
434
435 counter NUMBER:=0;
436 BEGIN
437
438 x_progress := 'PO_POAPPROVAL_INIT1.GetPOAttributes: 01';
439 IF (g_po_wf_debug = 'Y') THEN
440 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
441 END IF;
442
443
444 -- CLM Aprvl
445 l_draft_id := po_wf_util_pkg. GetItemAttrNumber (itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'DRAFT_ID');
448 --Bug 13742477 :: If draft id is null, it should fetch the records from PO_HEADERS_ALL
449 If (l_draft_id = -1 OR l_draft_id is null) Then
450
451 /* Fetch the PO Header, then set the attributes. */
452 open GetPOHdr_csr(p_po_header_id);
453 FETCH GetPOHdr_csr into POHdr_rec;
454 close GetPOHdr_csr;
455
456 Else
457 -- CLM Aprvl
458 /* Fetch the PO Headers Draft, then set the attributes. */
459 open GetDraftPOHdr_csr(p_po_header_id, l_draft_id);
460 FETCH GetDraftPOHdr_csr into POHdr_rec;
461 close GetDraftPOHdr_csr;
462
463 End If;
464 -- CLM Aprvl
465
466 x_progress := 'PO_POAPPROVAL_INIT1.GetPOAttributes: 02';
467 IF (g_po_wf_debug = 'Y') THEN
468 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
469 END IF;
470
471 SetPOHdrAttributes(itemtype, itemkey);
472
473 x_progress := 'PO_POAPPROVAL_INIT1.GetPOAttributes: 03';
474 IF (g_po_wf_debug = 'Y') THEN
475 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
476 END IF;
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 wf_core.context('PO_POAPPROVAL_INIT1','GetPOAttributes',x_progress);
481 raise;
482
483 end GetPOAttributes;
484 --
485 procedure SetPOHdrAttributes(itemtype in varchar2, itemkey in varchar2) is
486
487 l_po_amount number;
488 l_po_amount_disp varchar2(30);
489 l_tax_amount number;
490 l_tax_amount_disp varchar2(30);
491 l_total_amount number;
492 l_total_amount_disp varchar2(30);
493 l_doc_id number;
494 l_draft_id number; -- CLM apprvl
495 l_ame_approval_id number; -- CLM apprvl
496 x_progress varchar2(100) := '000';
497
498 l_auth_stat varchar2(80);
499 l_closed_code varchar2(80);
500 l_doc_type varchar2(25);
501 l_doc_subtype varchar2(25);
502 l_doc_type_disp varchar2(240); /* Bug# 2616433 */
503 -- l_doc_subtype_disp varchar2(80);
504 l_ga_flag varchar2(1) := null; -- FPI GA
505
506 /* Start Bug# 3972475 */
507 X_precision number;
508 X_ext_precision number;
509 X_min_acct_unit number;
510 /* End Bug# 3972475*/
511 --CLM Apprvl
512 is_clm_enabled VARCHAR2(1);
513 l_currency_code varchar2(30);
514 l_total_funded_amount NUMBER;
515 l_total_funded_amount_disp VARCHAR2(30);
516 -- CLM apprvl
517 l_clm_document_number varchar2(50);
518 l_modification_number varchar2(100);
519
520 --Added by Eric Ma for IL PO Notification on Apr-13,2009 ,Begin
521 -------------------------------------------------------------------------------------
522 ln_jai_excl_nr_tax number; --exclusive non-recoverable tax
523 lv_tax_region varchar2(30); --tax region code
524 -------------------------------------------------------------------------------------
525 --Added by Eric Ma for IL PO Notification on Apr-13,2009 ,End
526 cursor c1(p_auth_stat varchar2) is
527 select DISPLAYED_FIELD
528 from po_lookup_codes
529 where lookup_type='AUTHORIZATION STATUS'
530 and lookup_code = p_auth_stat;
531
532 cursor c2(p_closed_code varchar2) is
533 select DISPLAYED_FIELD
534 from po_lookup_codes
535 where lookup_type='DOCUMENT STATE'
536 and lookup_code = p_closed_code;
537
538 /* Bug# 2616433: kagarwal
539 ** Desc: We will get the document type display value from
540 ** po document types.
541 */
542
543 cursor c3(p_doc_type varchar2, p_doc_subtype varchar2) is
544 select type_name
545 from po_document_types
546 where document_type_code = p_doc_type
547 and document_subtype = p_doc_subtype;
548
549
550 BEGIN
551
552 x_progress := 'PO_POAPPROVAL_INIT1.SetPOHdrAttributes: 01';
553 IF (g_po_wf_debug = 'Y') THEN
554 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
555 END IF;
556 -- CLM Apprvl. The document_number has to be set to draft_id in case of modifications
557 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
558 itemkey => itemkey,
559 aname => 'DRAFT_ID');
560
561 l_ame_approval_id := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
562 itemkey => itemkey,
563 aname => 'AME_TRANSACTION_ID');
564
565 -- CLM apprvl. In the approval notification the appropriate doc number in the correct document numbering format must be displayed.
566
567 l_clm_document_number := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
568 itemkey => itemkey,
569 aname => 'CLM_DOCUMENT_NUMBER');
570
571 l_modification_number := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => itemtype,
572 itemkey => itemkey,
573 aname => 'MODIFICATION_NUMBER');
574
575 if l_draft_id <> -1 then
576 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
577 itemkey => itemkey,
578 aname => 'DOCUMENT_NUMBER',
579 avalue => l_modification_number);
580 else
581 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
582 itemkey => itemkey,
583 aname => 'DOCUMENT_NUMBER',
584 avalue => l_clm_document_number);
585 end if;
586
587 -- CLM apprvl end
588
589 /* Bug# 2423635: kagarwal
590 ** Desc: There is no need to set the DOCUMENT_ID again as
591 ** it is set at the time of startup.
592 */
593
594 wf_engine.SetItemAttrText ( itemtype => itemtype,
595 itemkey => itemkey,
596 aname => 'AUTHORIZATION_STATUS',
597 avalue => POHdr_rec.authorization_status);
598 --
599 wf_engine.SetItemAttrText ( itemtype => itemtype,
600 itemkey => itemkey,
601 aname => 'CLOSED_CODE',
602 avalue => POHdr_rec.closed_code);
603
604 --
605 wf_engine.SetItemAttrText ( itemtype => itemtype,
606 itemkey => itemkey,
607 aname => 'FUNCTIONAL_CURRENCY',
608 avalue => POHdr_rec.currency_code);
609 --
610 wf_engine.SetItemAttrText ( itemtype => itemtype,
611 itemkey => itemkey,
612 aname => 'PO_DESCRIPTION',
613 avalue => POHdr_rec.comments);
614
615
616 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
617 itemkey => itemkey,
618 aname => 'DOCUMENT_ID');
619
620
621
622 /* Get the translated values for the DOC_TYPE, DOC_SUBTYPE, AUTH_STATUS and
623 ** CLOSED_CODE. These will be displayed in the notifications.
624 */
625 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
626 itemkey => itemkey,
627 aname => 'DOCUMENT_TYPE');
628
629 l_doc_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
630 itemkey => itemkey,
631 aname => 'DOCUMENT_SUBTYPE');
632
633 OPEN C1(POHdr_rec.authorization_status);
634 FETCH C1 into l_auth_stat;
635 CLOSE C1;
636
637 OPEN C2(POHdr_rec.closed_code);
638 FETCH C2 into l_closed_code;
639 CLOSE C2;
640
641 /* Bug# 2616433 */
642 --<R12 STYLES PHASE II START>
643 if l_doc_type = 'PA' AND l_doc_subtype IN ('BLANKET','CONTRACT') OR
644 l_doc_type = 'PO' AND l_doc_subtype = 'STANDARD' then
645 -- CLM Apprvl. In case of modification documents that document type display needs to be 'Modification'
646 IF l_draft_id <> -1 then
647 l_doc_type_disp:= 'Modification';
648 ELSE
649 l_doc_type_disp:= PO_DOC_STYLE_PVT.GET_STYLE_DISPLAY_NAME(l_doc_id);
650 END IF;
651 else
652
653 OPEN C3(l_doc_type, l_doc_subtype);
654 FETCH C3 into l_doc_type_disp;
655 CLOSE C3;
656 end if;
657 --<R12 STYLES PHASE II END>
658
659 wf_engine.SetItemAttrText ( itemtype => itemtype,
660 itemkey => itemkey,
661 aname => 'AUTHORIZATION_STATUS_DISP',
662 avalue => l_auth_stat);
663 --
664 wf_engine.SetItemAttrText ( itemtype => itemtype,
665 itemkey => itemkey,
666 aname => 'CLOSED_CODE_DISP',
667 avalue => l_closed_code);
668
669 --<R12 STYLES PHASE II>
670 -- Removed FPI GA Modifications to get PO_GA_TYPE Message for GA
671
672 wf_engine.SetItemAttrText ( itemtype => itemtype,
673 itemkey => itemkey,
674 aname => 'DOCUMENT_TYPE_DISP',
675 avalue => l_doc_type_disp);
676 --
677
678 /* Bug# 2616433: kagarwal
679 ** Desc: We will only be using one display attribute for type and
680 ** subtype - DOCUMENT_TYPE_DISP, hence commenting the code below
681 */
682
683
684 -- x_progress := 'SetPOHdrAttributes: 02. Values= ' || l_doc_type||l_doc_type_disp||'draft_id: '||l_draft_id||'l_ame_approval_id: '||l_ame_approval_id;
685 /* bug12665187 : Resetting message text so that it could stick to limit x_progress(100); */
686 x_progress := 'SetPOHdrAttributes: 02. Values';
687
688 IF (g_po_wf_debug = 'Y') THEN
689 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
690 END IF;
691
692 /* Bug 979454 */
693 -- CLM Apprvl..For modification document the changed amount only needs to be displayed
694 if l_draft_id <> -1 then
695 l_po_amount := po_ame_setup_pvt.get_modification_amount(l_ame_approval_id);
696 else
697 l_po_amount := po_notifications_sv3.get_doc_total(l_doc_subtype, l_doc_id);
698 end if;
699
700
701 --bug 12396408
702 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'PO_AMOUNT_DSP_NUMERIC',
705 avalue => l_po_amount);
706
707 l_po_amount_disp := TO_CHAR(l_po_amount,FND_CURRENCY.GET_FORMAT_MASK(
708 POHdr_rec.currency_code,30));
709
710 x_progress := 'l_po_amount: '||l_po_amount||'l_po_amount_disp: '||l_po_amount_disp;
711 IF (g_po_wf_debug = 'Y') THEN
712 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
713 END IF;
714
715 wf_engine.SetItemAttrText ( itemtype => itemtype,
716 itemkey => itemkey,
717 aname => 'PO_AMOUNT_DSP',
718 avalue => l_po_amount_disp);
719
720 /*Start Bug# 3972475 - replaced the below sql to get the tax amount
721 to account for canceled QTY. Also accounted for new order types introduced
722 in 11i10 that use amount instead of quantity (where quantity_ordered is null).
723
724 Since we are performing divide and multiply by operations we need rounding
725 logic based on the currency.
726
727 If we are using minimum accountable unit we apply:
728 rounded tax = round(tax/mau)*mau, otherwise
729 rounded tax = round(tax, precision)
730
731 Old tax select:
732 select nvl(sum(NONRECOVERABLE_TAX),0)
733 into l_tax_amount
734 from po_distributions
735 where po_header_id = POHdr_rec.po_header_id;
736 */
737
738 --Modified by Eric Ma for IL PO Notification on Apr-13,2009,Begin
739 ------------------------------------------------------------------------------------
740 lv_tax_region := JAI_PO_WF_UTIL_PUB.get_tax_region
741 ( pv_document_type => JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE
742 , pn_document_id => l_doc_id
743 );
744
745 IF(lv_tax_region ='JAI')
746 THEN
747 --Indian localization tax calc code
748 JAI_PO_WF_UTIL_PUB.Get_Jai_Tax_Amount ( pv_document_type => JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE
749 , pn_document_id => l_doc_id
750 , xn_excl_tax_amount => l_tax_amount
751 , xn_excl_nr_tax_amount => ln_jai_excl_nr_tax
752 );
753 ELSE
754 --original tax calc code
755 fnd_currency.get_info( POHdr_rec.currency_code,
756 X_precision,
757 X_ext_precision,
758 X_min_acct_unit);
759
760 BEGIN
761 IF (x_min_acct_unit IS NOT NULL) AND
762 (x_min_acct_unit <> 0)
763 THEN
764 -- Bug 14651103 : Changed sqls to calculate tax on basis of POLL.matching_basis rather than relying qunatity_ordered
765 SELECT nvl(sum( round (nvl(POD.nonrecoverable_tax,0) *
766 decode(POLL.matching_basis,
767 'AMOUNT',
768 --Bug16222308 Handling the quantity zero on distribution
769 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
770 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
771 ) / X_min_acct_unit
772 ) * X_min_acct_unit
773 ),0)
774 INTO l_tax_amount
775 FROM po_distributions_all POD,
776 po_line_locations_all POLL
777 WHERE POD.po_header_id = POHdr_rec.po_header_id
778 AND POD.line_location_id=POLL.line_location_id
779 AND POD.po_header_id=POLL.po_header_id
780 AND Nvl(POD.distribution_type,'STANDARD') NOT IN ('PREPAYMENT') --11876122
781 AND POD.line_location_id IS NOT NULL; -- 13887381
782 /* Bug 11876122: Adding condition on distribution_type as in case of
783 complex PO's PREPAYMENT type distributions should not be considered
784 for calculating the tax amount to be shown in approver's window.
785 */
786
787 ELSE
788 -- Bug 14651103 : Changed sqls to calculate tax on basis of POLL.matching_basis rather than relying qunatity_ordered
789 SELECT nvl(sum( round (nvl(POD.nonrecoverable_tax,0) *
790 decode(POLL.matching_basis,
791 'AMOUNT',
792 --Bug16222308 Handling the quantity zero on distribution
793 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
794 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
795 ),
796 X_precision
797 )
798 ),0)
799 INTO l_tax_amount
800 FROM po_distributions_all POD,
801 po_line_locations_all POLL
802 WHERE POD.po_header_id = POHdr_rec.po_header_id
803 AND POD.line_location_id=POLL.line_location_id
804 AND POD.po_header_id=POLL.po_header_id
805 AND Nvl(distribution_type,'STANDARD') NOT IN ('PREPAYMENT') -- 11876122
806 AND POD.line_location_id IS NOT NULL; -- 13887381
807 END IF;
808 EXCEPTION
809 WHEN NO_DATA_FOUND THEN
810 l_tax_amount := 0;
811 END;
812
813
814 /*End Bug# 3972475 */
815 END IF; --(lv_tax_region ='JAI')
816 ---------------------------------------------------------------------------
817 --Modified by Eric Ma for IL PO Notification on Apr-13,2009,End
818 --bug 12396408
819 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemtype,
820 itemkey => itemkey,
821 aname => 'TAX_AMOUNT_DSP_NUMERIC',
822 avalue => l_tax_amount );
823
824 l_tax_amount_disp := TO_CHAR(l_tax_amount,FND_CURRENCY.GET_FORMAT_MASK(
825 POHdr_rec.currency_code,30));
826
827 wf_engine.SetItemAttrText ( itemtype => itemtype,
828 itemkey => itemkey,
829 aname => 'TAX_AMOUNT_DSP',
830 avalue => l_tax_amount_disp );
831
832 l_total_amount := l_po_amount + l_tax_amount;
833
834 l_total_amount_disp := TO_CHAR(l_total_amount,FND_CURRENCY.GET_FORMAT_MASK(
835 POHdr_rec.currency_code,30));
836
837 wf_engine.SetItemAttrText ( itemtype => itemtype,
838 itemkey => itemkey,
839 aname => 'TOTAL_AMOUNT_DSP',
840 avalue => l_total_amount_disp);
841
842 -- CLM Apprvl. Total funded value has to be diplayed in the approval notification for the CLM documents.
843
844 is_clm_enabled := NVL(FND_PROFILE.VALUE('PO_CLM_ENABLED'),'N');
845 IF is_clm_enabled = 'Y' THEN
846
847 select sum(funded_value)
848 into l_total_funded_amount
849 from po_distributions_merge_v
850 where po_header_id = l_doc_id
851 and draft_id = l_draft_id;
852
853 -- total funded amount must be displayed in functional currency
854 l_currency_code := PO_CORE_S2.get_base_currency;
855
856 l_total_funded_amount_disp := TO_CHAR(l_total_funded_amount,FND_CURRENCY.GET_FORMAT_MASK(
857 l_currency_code,30));
858
859 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
860 itemkey => itemkey,
861 aname => 'FUNDED_AMOUNT_DISPLAY',
862 avalue => l_total_funded_amount_disp);
863
864 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
865 itemkey => itemkey,
866 aname => 'FUNDED_TITLE_DSP',
867 avalue => 'Funded Value');
868 END IF;
869
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 wf_core.context('PO_POAPPROVAL_INIT1','SetPOHdrAttributes',x_progress);
874 raise;
875
876
877 end SetPOHdrAttributes;
878
879 --
880 procedure GetRelAttributes(p_rel_header_id in NUMBER,
881 itemtype in varchar2,
882 itemkey in varchar2) is
883
884 x_progress varchar2(100) := '000';
885
886 counter NUMBER:=0;
887 BEGIN
888
889
890 x_progress := 'PO_POAPPROVAL_INIT1.GetRelAttributes: 01';
891 IF (g_po_wf_debug = 'Y') THEN
892 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
893 END IF;
894
895 /* Fetch the Release Header, then set the attributes. */
896 open GetRelHdr_csr(p_rel_header_id);
897 FETCH GetRelHdr_csr into RelHdr_rec;
898 close GetRelHdr_csr;
899
900 x_progress := 'PO_POAPPROVAL_INIT1.GetRelAttributes: 02';
901 IF (g_po_wf_debug = 'Y') THEN
902 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
903 END IF;
904
905 SetRelHdrAttributes(itemtype, itemkey);
906
907 x_progress := 'PO_POAPPROVAL_INIT1.GetReLattributes: 03';
908 IF (g_po_wf_debug = 'Y') THEN
909 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
910 END IF;
911
912 EXCEPTION
913 WHEN OTHERS THEN
914 CLOSE GetRelHdr_csr;
915
916 wf_core.context('PO_POAPPROVAL_INIT1','GetRelAttributes',x_progress);
917 raise;
918
919 end GetRelAttributes;
920
921 --
922 procedure SetRelHdrAttributes(itemtype in varchar2, itemkey in varchar2) is
923
924 l_po_amount number;
925 l_po_amount_disp varchar2(30);
926 l_tax_amount number;
927 l_tax_amount_disp varchar2(30);
928 l_total_amount number;
929 l_total_amount_disp varchar2(30);
930 l_doc_id number;
931 x_progress varchar2(100) := '000';
932
933 l_auth_stat varchar2(80);
934 l_closed_code varchar2(80);
935 l_doc_type varchar2(25);
936 l_doc_subtype varchar2(25);
937 l_doc_type_disp varchar2(240); /* Bug# 2616433 */
938 -- l_doc_subtype_disp varchar2(80);
939
940 /* Start Bug# 3972475 */
941 X_precision number;
942 X_ext_precision number;
943 X_min_acct_unit number;
944 /* End Bug# 3972475*/
945
946 --Added by Eric Ma for IL PO Notification on Apr-13,2009,Begin
947 ------------------------------------------------------------------------------------
948 ln_jai_excl_nr_tax number; --exclusive non-recoverable tax
949 lv_tax_region varchar2(30); --tax region code
950 -------------------------------------------------------------------------------------
951 --Added by Eric Ma for IL PO Notification on Apr-13,2009,End
952 cursor c1(p_auth_stat varchar2) is
953 select DISPLAYED_FIELD
954 from po_lookup_codes
955 where lookup_type='AUTHORIZATION STATUS'
956 and lookup_code = p_auth_stat;
957
958 /*
959 cursor c2(p_closed_code varchar2) is
960 select DISPLAYED_FIELD
961 from po_lookup_codes
962 where lookup_type='DOCUMENT STATE'
963 and lookup_code = p_closed_code;
964 */
965
966 /* Bug# 2616433: kagarwal
967 ** Desc: We will get the document type display value from
968 ** po document types.
969 */
970
971 cursor c3(p_doc_type varchar2, p_doc_subtype varchar2) is
972 select type_name
973 from po_document_types
974 where document_type_code = p_doc_type
975 and document_subtype = p_doc_subtype;
976
977 /*
978 cursor c3(p_doc_type varchar2) is
979 select DISPLAYED_FIELD
980 from po_lookup_codes
981 where lookup_type='DOCUMENT TYPE'
982 and lookup_code = p_doc_type;
983
984 cursor c4(p_doc_subtype varchar2) is
985 select DISPLAYED_FIELD
986 from po_lookup_codes
987 where lookup_type='DOCUMENT SUBTYPE'
988 and lookup_code = p_doc_subtype;
989 */
990
991 BEGIN
992
993 x_progress := 'PO_POAPPROVAL_INIT1.SetPOHdrAttributes : 01';
994 IF (g_po_wf_debug = 'Y') THEN
995 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
996 END IF;
997
998
999 wf_engine.SetItemAttrText ( itemtype => itemtype,
1000 itemkey => itemkey,
1001 aname => 'RELEASE_TYPE',
1002 avalue => RelHdr_rec.Release_Type);
1003 --
1004 wf_engine.SetItemAttrNumber ( itemtype => itemType,
1005 itemkey => itemkey,
1006 aname => 'RELEASE_NUM',
1007 avalue => RelHdr_rec.Release_num);
1008 --
1009
1010 wf_engine.SetItemAttrText ( itemtype => itemtype,
1011 itemkey => itemkey,
1012 aname => 'AUTHORIZATION_STATUS',
1013 avalue => RelHdr_rec.authorization_status);
1014 --
1015 wf_engine.SetItemAttrText ( itemtype => itemtype,
1016 itemkey => itemkey,
1017 aname => 'CLOSED_CODE',
1018 avalue => RelHdr_rec.closed_code);
1019 --
1020 wf_engine.SetItemAttrText ( itemtype => itemtype,
1021 itemkey => itemkey,
1022 aname => 'DOCUMENT_NUMBER',
1023 avalue => RelHdr_rec.po_number);
1024 --
1025 wf_engine.SetItemAttrText ( itemtype => itemtype,
1026 itemkey => itemkey,
1027 aname => 'FUNCTIONAL_CURRENCY',
1028 avalue => RelHdr_rec.currency_code);
1029 --
1030 wf_engine.SetItemAttrText ( itemtype => itemtype,
1031 itemkey => itemkey,
1032 aname => 'RELEASE_NUM_DASH',
1033 avalue => '-');
1034
1035 --Bug 10140786 - Setting the PO_DESCRIPTION
1036 wf_engine.SetItemAttrText ( itemtype => itemtype,
1037 itemkey => itemkey,
1038 aname => 'PO_DESCRIPTION',
1039 avalue => RelHdr_rec.comments);
1040
1041 OPEN C1(RelHdr_rec.authorization_status);
1042 FETCH C1 into l_auth_stat;
1043 CLOSE C1;
1044
1045 /*
1046 OPEN C2(RelHdr_rec.closed_code);
1047 FETCH C2 into l_closed_code;
1048 CLOSE C2;
1049 */
1050
1051 /* Bug# 2616433 */
1052
1053 OPEN C3('RELEASE', RelHdr_rec.Release_Type);
1054 FETCH C3 into l_doc_type_disp;
1055 CLOSE C3;
1056
1057 /*
1058 OPEN C4(RelHdr_rec.Release_Type);
1059 FETCH C4 into l_doc_subtype_disp;
1060 CLOSE C4;
1061 */
1062
1063 --
1064 wf_engine.SetItemAttrText ( itemtype => itemtype,
1065 itemkey => itemkey,
1066 aname => 'AUTHORIZATION_STATUS_DISP',
1067 avalue => l_auth_stat);
1068 --
1069 /* Not using this currently
1070 wf_engine.SetItemAttrText ( itemtype => itemtype,
1071 itemkey => itemkey,
1072 aname => 'CLOSED_CODE_DISP',
1073 avalue => l_closed_code);
1074 */
1075 --
1076 wf_engine.SetItemAttrText ( itemtype => itemtype,
1077 itemkey => itemkey,
1078 aname => 'DOCUMENT_TYPE_DISP',
1079 avalue => l_doc_type_disp);
1080 --
1081
1082 /* Bug# 2616433: kagarwal
1083 ** Desc: We will only be using one display attribute for type and
1084 ** subtype - DOCUMENT_TYPE_DISP, hence commenting the code below
1085 */
1086
1087 /*
1088 wf_engine.SetItemAttrText ( itemtype => itemtype,
1089 itemkey => itemkey,
1090 aname => 'DOCUMENT_SUBTYPE_DISP',
1091 avalue => l_doc_subtype_disp);
1092 */
1093
1094 l_doc_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1095 itemkey => itemkey,
1096 aname => 'DOCUMENT_ID');
1097
1098 l_po_amount := po_notifications_sv3.get_doc_total('RELEASE', l_doc_id);
1099
1100 l_po_amount_disp := TO_CHAR(l_po_amount,FND_CURRENCY.GET_FORMAT_MASK(
1101 RelHdr_rec.currency_code,30));
1102
1103 wf_engine.SetItemAttrText ( itemtype => itemtype,
1104 itemkey => itemkey,
1105 aname => 'PO_AMOUNT_DSP',
1106 avalue => l_po_amount_disp);
1107
1108 /*Start Bug# 3972475 - replaced the below sql to get the tax amount
1109 taking canceled release lines into account. Also accounted for new order
1110 types introduced in 11i10 that use amount instead of quantity
1111 (where quantity_ordered is null).
1112
1113 Since we are performing divide and multiply by operations we need rounding
1114 logic based on the currency.
1115
1116 If we are using minimum accountable unit we apply:
1117 rounded tax = round(tax/mau)*mau, otherwise
1118 rounded tax = round(tax, precision)
1119
1120 Old tax select:
1121 select nvl(sum(NONRECOVERABLE_TAX),0)
1122 into l_tax_amount
1123 from po_distributions
1124 where po_release_id = RelHdr_rec.Po_Release_id;
1125 */
1126
1127 --Modified by Eric Ma for IL PO Notification on Apr-13,2009,Begin
1128 ------------------------------------------------------------------------------------
1129 lv_tax_region := JAI_PO_WF_UTIL_PUB.get_tax_region
1130 ( pv_document_type => JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE
1131 , pn_document_id => l_doc_id
1132 );
1133 IF(lv_tax_region ='JAI')
1134 THEN
1135 --Indian localization tax calc code
1136 JAI_PO_WF_UTIL_PUB.Get_Jai_Tax_Amount( pv_document_type => JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE
1137 , pn_document_id => RelHdr_rec.PO_RELEASE_ID
1138 , xn_excl_tax_amount => l_tax_amount
1139 , xn_excl_nr_tax_amount => ln_jai_excl_nr_tax
1140 );
1141 ELSE
1142 --original tax calc code
1143 fnd_currency.get_info( RelHdr_rec.currency_code,
1144 X_precision,
1145 X_ext_precision,
1146 X_min_acct_unit);
1147
1148 IF (x_min_acct_unit IS NOT NULL) AND
1149 (x_min_acct_unit <> 0)
1150 THEN
1151 SELECT nvl(sum( round (POD.nonrecoverable_tax *
1152 decode(quantity_ordered,
1153 NULL,
1154 --Bug16222308 Handling the quantity zero on distribution
1155 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
1156 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
1157 ) / X_min_acct_unit
1158 ) * X_min_acct_unit
1159 ),0)
1160 INTO l_tax_amount
1161 FROM po_distributions_all POD
1162 WHERE po_release_id = RelHdr_rec.po_release_id;
1163 ELSE
1164 SELECT nvl(sum( round (POD.nonrecoverable_tax *
1165 decode(quantity_ordered,
1166 NULL,
1167 --Bug16222308 Handling the quantity zero on distribution
1168 (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / Decode ( nvl(POD.amount_ordered, 1),0,1,nvl(POD.amount_ordered, 1) ),
1169 (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / Decode ( nvl(POD.quantity_ordered, 1),0,1,nvl(POD.quantity_ordered, 1) )
1170 ),
1171 X_precision
1172 )
1173 ),0)
1174 INTO l_tax_amount
1175 FROM po_distributions_all POD
1176 WHERE po_release_id = RelHdr_rec.po_release_id;
1177 END IF;
1178
1179 /*End Bug# 3972475 */
1180 END IF;--(lv_tax_region ='JAI')
1181 ------------------------------------------------------------------------------------
1182 --Modified by Eric Ma for IL PO Notification on Apr-13,2009,End
1183
1184 l_tax_amount_disp := TO_CHAR(l_tax_amount,FND_CURRENCY.GET_FORMAT_MASK(
1185 RelHdr_rec.currency_code,30));
1186
1187 wf_engine.SetItemAttrText ( itemtype => itemtype,
1188 itemkey => itemkey,
1189 aname => 'TAX_AMOUNT_DSP',
1190 avalue => l_tax_amount_disp );
1191
1192 l_total_amount := l_po_amount + l_tax_amount;
1193
1194
1195 l_total_amount_disp := TO_CHAR(l_total_amount,FND_CURRENCY.GET_FORMAT_MASK(
1196 RelHdr_rec.currency_code,30));
1197
1198 wf_engine.SetItemAttrText ( itemtype => itemtype,
1199 itemkey => itemkey,
1200 aname => 'TOTAL_AMOUNT_DSP',
1201 avalue => l_total_amount_disp);
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 wf_core.context('PO_POAPPROVAL_INIT1','SetRelHdrAttributes',x_progress);
1206 raise;
1207
1208
1209 end SetRelHdrAttributes;
1210 --
1211
1212 /****************************************************************************
1213 *
1214 * Function Reserve_Unreserve_Check(...)
1215 *
1216 * Specifications:
1217 *
1218 * If action = UNRESERVE then
1219 * If doc_status in ('REJECTED', 'CANCELLED') then return FALSE.
1220 * If any distribution is UNRESERVED (encumbered_flag = 'N') then
1221 * return TRUE else return FALSE.
1222 * If action = RESERVE then
1223 * If doc_status = 'CANCELLED' then return FALSE.
1224 * If any distribution is RESERVED (encumbered_flag = 'Y') then
1225 * return TRUE else return FALSE.
1226 *
1227 ****************************************************************************/
1228 -- <ENCUMBRANCE FPJ START>
1229 -- Rewriting the following procedure to use the encumbrance APIs
1230
1231 function Reserve_Unreserve_Check (action VARCHAR2, doc_header_id NUMBER,
1232 doc_type_code VARCHAR2, doc_status VARCHAR2,
1233 doc_cancel_flag VARCHAR2)
1234 return BOOLEAN
1235 is
1236 l_progress varchar2(200);
1237 l_doc_subtype po_headers_all.type_lookup_code%TYPE;
1238 p_return_status varchar2(1);
1239 p_action_flag varchar2(1);
1240
1241 begin
1242
1243 l_progress := '000';
1244
1245 /* If the document has been cancelled, then we need to disable Reserve
1246 ** and Unreserve.
1247 */
1248 IF ( NVL(doc_cancel_flag,'N') = 'Y' ) THEN
1249
1250 return FALSE;
1251 END IF;
1252
1253 l_progress := '001';
1254
1255 -- Get the doc subtype
1256 IF (doc_type_code IN ('PO', 'PA')) THEN
1257
1258 SELECT type_lookup_code
1259 INTO l_doc_subtype
1260 FROM po_headers
1261 WHERE po_header_id = doc_header_id;
1262
1263 ELSIF (doc_type_code = 'RELEASE') THEN
1264 SELECT shipment_type
1265 INTO l_doc_subtype
1266 FROM po_line_locations
1267 WHERE po_release_id = doc_header_id
1268 AND ROWNUM = 1;
1269
1270 ELSIF (doc_type_code = 'REQUISITION') THEN
1271 SELECT type_lookup_code
1272 INTO l_doc_subtype
1273 FROM po_requisition_headers
1274 WHERE requisition_header_id = doc_header_id;
1275
1276 ELSE
1277 wf_core.context('PO_POAPPROVAL_INIT1',
1278 'Reserve_Unreserve_Check - Invalid doctype', '004');
1279 app_exception.Raise_Exception;
1280
1281 END IF;
1282
1283 l_progress := '020';
1284
1285 IF (action = 'UNRESERVE') THEN
1286
1287 PO_DOCUMENT_FUNDS_PVT.is_unreservable(
1288 x_return_status => p_return_status
1289 , p_doc_type => doc_type_code
1290 , p_doc_subtype => l_doc_subtype
1291 , p_doc_level => PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER
1292 , p_doc_level_id => doc_header_id
1293 , x_unreservable_flag => p_action_flag);
1294
1295 l_progress := '030';
1296
1297 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1299 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
1300 RAISE FND_API.G_EXC_ERROR;
1301 END IF;
1302
1303 l_progress := '040';
1304
1305 IF p_action_flag = PO_DOCUMENT_FUNDS_PVT.g_parameter_YES THEN
1306 RETURN TRUE;
1307 ELSE
1308 RETURN FALSE;
1309 END IF;
1310
1311 ELSIF (action = 'RESERVE') THEN
1312
1313 PO_DOCUMENT_FUNDS_PVT.is_reservable(
1314 x_return_status => p_return_status
1315 , p_doc_type => doc_type_code
1316 , p_doc_subtype => l_doc_subtype
1317 , p_doc_level => PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER
1318 , p_doc_level_id => doc_header_id
1319 , x_reservable_flag => p_action_flag);
1320
1321 l_progress := '050';
1322
1323 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1325 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
1326 RAISE FND_API.G_EXC_ERROR;
1327 END IF;
1328
1329 l_progress := '060';
1330
1331 IF p_action_flag = PO_DOCUMENT_FUNDS_PVT.g_parameter_YES THEN
1332 RETURN TRUE;
1333 ELSE
1334 RETURN FALSE;
1335 END IF;
1336
1337 ELSE
1338 wf_core.context('PO_POAPPROVAL_INIT1',
1339 'Reserve_Unreserve_Check - Invalid action', '004');
1340 app_exception.Raise_Exception;
1341
1342 END IF;
1343
1344 l_progress := '100';
1345
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 wf_core.context('PO_POAPPROVAL_INIT1','Reserve_Unreserve_Check',l_progress);
1349 raise;
1350
1351 end Reserve_Unreserve_Check;
1352
1353 -- <ENCUMBRANCE FPJ END>
1354
1355 -- <SVC_NOTIFICATIONS FPJ START>
1356
1357 -------------------------------------------------------------------------------
1358 --Start of Comments
1359 --Name: Get_Formatted_Address
1360 --Pre-reqs:
1361 -- None.
1362 --Modifies:
1363 -- None.
1364 --Locks:
1365 -- None.
1366 --Function:
1367 -- Given a location ID, concatenates fields from the hr_locations into an
1368 -- address using different fields and formats for different countries and
1369 -- address types.
1370 --Parameters:
1371 --IN:
1372 --in_location_id
1373 -- Location ID
1374 --RETURNS:
1375 -- Concatenated address as a VARCHAR2
1376 --End of Comments
1377 -------------------------------------------------------------------------------
1378 FUNCTION Get_Formatted_Address(p_location_id in NUMBER)
1379 RETURN VARCHAR2 IS
1380
1381 l_address varchar2(2000);
1382
1383 begin
1384 --SQL What: Concatenate fields from hr_locations
1385 --SQL Why: To return an address
1386 SELECT substrb ( part1 || decode(part2, NULL, '', part2 || ' ') || part3,
1387 1, 2000)
1388 INTO l_address
1389 FROM
1390 (SELECT hrl.address_line_1 || ', '
1391 || decode(hrl.address_line_2, null, '', hrl.address_line_2||', ')
1392 || decode(hrl.address_line_3, null, '', hrl.address_line_3||', ')
1393 || decode(hr_general.decode_lookup(hrl.country||'_PROVINCE',hrl.town_or_city ),
1394 NULL, decode(hrl.town_or_city, null, '', hrl.town_or_city ||', '),
1395 hr_general.decode_lookup(hrl.country||'_PROVINCE',hrl.town_or_city ) || ', ')
1396 AS part1,
1397 nvl(decode(hrl.region_1,
1398 null, hrl.region_2,
1399 decode(flv1.meaning,
1400 null, decode(flv2.meaning,
1401 null, flv3.meaning,
1402 flv2.lookup_code),
1403 flv1.lookup_code) ),
1404 hrl.region_2)
1405 AS part2,
1406 decode(hrl.postal_code, null, '', hrl.postal_code || ', ')
1407 || ftv.territory_short_name
1408 AS part3
1409 FROM hr_locations hrl,
1410 fnd_territories_vl ftv,
1411 fnd_lookup_values_vl flv1,
1412 fnd_lookup_values_vl flv2,
1413 fnd_lookup_values_vl flv3
1414 WHERE hrl.region_1 = flv1.lookup_code (+) and
1415 hrl.country || '_PROVINCE' = flv1.lookup_type (+) and
1416 hrl.region_2 = flv2.lookup_code (+) and
1417 hrl.country || '_STATE' = flv2.lookup_type (+) and
1418 hrl.region_1 = flv3.lookup_code (+) and
1419 hrl.country || '_COUNTY' = flv3.lookup_type (+) and
1420 hrl.country = ftv.territory_code (+) and
1421 hrl.location_id = p_location_id);
1422
1423 RETURN l_address;
1424
1425 end Get_Formatted_Address;
1426
1427 -------------------------------------------------------------------------------
1428 --Start of Comments
1429 --Name: Get_Formatted_Full_Name
1430 --Pre-reqs:
1431 -- None.
1432 --Modifies:
1433 -- None.
1434 --Locks:
1435 -- None.
1436 --Function:
1437 -- Given a first and last name, returns a concatenated full name.
1438 --Parameters:
1439 --IN:
1440 --in_first_name
1441 -- First Name
1442 --in_last_name
1443 -- Last Name
1444 --RETURNS:
1445 -- Concatenated full name as a VARCHAR2
1446 --End of Comments
1447 -------------------------------------------------------------------------------
1448 FUNCTION Get_Formatted_Full_Name(p_first_name in VARCHAR2,
1449 p_last_name in VARCHAR2)
1450 RETURN VARCHAR2 IS
1451
1452 l_optional_space VARCHAR2(1);
1453
1454 BEGIN
1455 if(p_first_name is not null and p_last_name is not null) then
1456 l_optional_space := ' ';
1457 else
1458 l_optional_space := '';
1459 end if;
1460
1461 return (p_first_name || l_optional_space || p_last_name);
1462
1463 end Get_Formatted_Full_Name;
1464
1465 -------------------------------------------------------------------------------
1466 --Start of Comments
1467 --Name: get_temp_labor_requester
1468 --Pre-reqs:
1469 -- None.
1470 --Modifies:
1471 -- None.
1472 --Locks:
1473 -- None.
1474 --Function:
1475 -- Returns the employee_id of the requester for the given Temp Labor PO line,
1476 -- based on a series of rules.
1477 --Parameters:
1478 --IN:
1479 --p_po_line_id
1480 -- Temp Labor PO line that we are retrieving the requester for.
1481 --OUT:
1482 --x_requester_id
1483 -- employee_id of the requester
1484 --End of Comments
1485 -------------------------------------------------------------------------------
1486 PROCEDURE get_temp_labor_requester (
1487 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE,
1488 x_requester_id OUT NOCOPY PO_REQUISITION_LINES.to_person_id%TYPE
1489 ) IS
1490 l_proc_name CONSTANT VARCHAR2(30) := 'get_temp_labor_requester';
1491 l_progress VARCHAR2(3) := '000';
1492
1493 CURSOR l_line_req_requester_csr (
1494 p_po_line_id PO_LINES.po_line_id%TYPE
1495 ) IS
1496 -- SQL What: Retrieves the requester from the backing requisition of the
1497 -- PO line, if available.
1498 -- SQL Why: To determine the recipient of the Temp Labor notification.
1499 --
1500 -- <Complex Work R12>:
1501 -- o Added ORDER BY clause so that, if there are multiple
1502 -- pay items on a line, we will pull the requisition from the first
1503 -- line location.
1504 -- Bug 5004284: Restored the outer join that was removed in 120.4. This
1505 -- is necessary because even if no backing requisition lines were found,
1506 -- we still want to return POH.agent_id.
1507 SELECT PRL.to_person_id, POH.agent_id
1508 FROM po_lines POL,
1509 po_line_locations PLL,
1510 -- For Shared Procurement, the destination OU may be different from
1511 -- the Purchasing OU:
1512 po_requisition_lines_all PRL,
1513 po_headers POH
1514 WHERE POL.po_line_id = p_po_line_id
1515 AND POL.po_line_id = PLL.po_line_id -- JOIN
1516 AND PLL.line_location_id = PRL.line_location_id (+) -- JOIN
1517 AND POH.po_header_id = POL.po_header_id -- JOIN
1518 ORDER BY PLL.shipment_num;
1519
1520 CURSOR l_line_dist_requesters_csr (
1521 p_po_line_id PO_LINES.po_line_id%TYPE
1522 ) IS
1523 -- SQL What: For the given PO line, retrieves the requesters from the
1524 -- distributions, starting with the first distribution that has a
1525 -- requester.
1526 -- SQL Why: To determine the recipient of the Temp Labor notification.
1527 --
1528 -- <Complex Work R12>: Added join to po_line_locations and shipment_num
1529 -- to the ORDER BY clause so we will get the first distribution on the
1530 -- first pay item that has a deliver-to-person, in the case of a complex
1531 -- work PO that has multiple pay items on a fixed-price temp labor line.
1532 -- Changed po_distributions to po_distributions_all to only have a single
1533 -- secured synonym in the query.
1534 SELECT POD.deliver_to_person_id
1535 FROM po_distributions_all POD
1536 , po_line_locations PLL -- <Complex Work R12>
1537 WHERE POD.line_location_id = PLL.line_location_id
1538 AND PLL.po_line_id = p_po_line_id
1539 AND POD.deliver_to_person_id IS NOT NULL
1540 AND PLL.shipment_type <> 'PREPAYMENT'
1541 ORDER BY PLL.shipment_num, POD.distribution_num ASC;
1542
1543 l_requester_id PO_REQUISITION_LINES.to_person_id%TYPE := null;
1544 l_agent_id PO_HEADERS.agent_id%TYPE;
1545 l_po_header_id PO_HEADERS.po_header_id%TYPE;
1546 BEGIN
1547 -- Determine the recipient of this notification using the 3 rules below:
1548
1549 -- Rule 1. If the PO line has a backing requisition, use the requester
1550 -- on the requisition.
1551 OPEN l_line_req_requester_csr (p_po_line_id);
1552 FETCH l_line_req_requester_csr INTO l_requester_id, l_agent_id;
1553 CLOSE l_line_req_requester_csr;
1554
1555 l_progress := '010';
1556 IF g_debug_stmt THEN
1557 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
1558 FND_LOG.string (
1559 log_level => FND_LOG.LEVEL_EVENT,
1560 module => g_module_prefix || l_proc_name,
1561 message => '1. Requester on backing requisition: '||l_requester_id );
1562 END IF;
1563 END IF;
1564
1565 -- Rule 2. Otherwise, use the requester on the first distribution of
1566 -- the PO, if available.
1567 -- Bug 5004284: Changed the condition from l_line_req_requester_csr%NOTFOUND
1568 -- (introduced in 120.4) back to l_requester_id IS NULL. Even if there are no
1569 -- backing reqs, the cursor should always return a row (with the agent_id).
1570 IF (l_requester_id IS NULL) THEN
1571 OPEN l_line_dist_requesters_csr (p_po_line_id);
1572 FETCH l_line_dist_requesters_csr INTO l_requester_id;
1573
1574 l_progress := '020';
1575 IF g_debug_stmt THEN
1576 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
1577 FND_LOG.string (
1578 log_level => FND_LOG.LEVEL_EVENT,
1579 module => g_module_prefix || l_proc_name,
1580 message => '2. Requester on PO distribution: '||l_requester_id );
1581 END IF;
1582 END IF;
1583
1584 -- Rule 3. Otherwise, use the buyer on the PO.
1585 IF (l_line_dist_requesters_csr%NOTFOUND) THEN
1586 l_requester_id := l_agent_id;
1587
1588 l_progress := '030';
1589 IF g_debug_stmt THEN
1590 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EVENT) THEN
1591 FND_LOG.string (
1592 log_level => FND_LOG.LEVEL_EVENT,
1593 module => g_module_prefix || l_proc_name,
1594 message => '3. Using the buyer on the PO as the requester: '||l_requester_id );
1595 END IF;
1596 END IF;
1597 END IF;
1598
1599 CLOSE l_line_dist_requesters_csr;
1600
1601 END IF; -- l_requester_id IS NULL
1602
1603 x_requester_id := l_requester_id;
1604
1605 EXCEPTION
1606 WHEN OTHERS THEN
1607 IF (l_line_req_requester_csr%ISOPEN) THEN
1608 CLOSE l_line_req_requester_csr;
1609 END IF;
1610
1611 IF (l_line_dist_requesters_csr%ISOPEN) THEN
1612 CLOSE l_line_dist_requesters_csr;
1613 END IF;
1614
1615 wf_core.context( g_pkg_name, l_proc_name, l_progress );
1616 RAISE;
1617
1618 END get_temp_labor_requester;
1619
1620 -------------------------------------------------------------------------------
1621 --Start of Comments
1622 --Name: launch_notify_tl_requesters
1623 --Pre-reqs:
1624 -- None.
1625 --Modifies:
1626 -- None.
1627 --Locks:
1628 -- None.
1629 --Function:
1630 -- For each new Temp Labor line (i.e. line that has not been approved before)
1631 -- on a standard PO, starts the Notify Temp Labor Requester process to send
1632 -- a PO Approval notification to the requester.
1633 --Parameters:
1634 --IN:
1635 --itemtype
1636 -- Workflow Item Type.
1637 --itemkey
1638 -- Workflow Item Key.
1639 --actid
1640 -- Identifies the Workflow activity that is calling this procedure.
1641 --funcmode
1642 -- Workflow mode that this procedure is being called in: Run, Cancel, etc.
1643 --OUT:
1644 --resultout
1645 -- Standard result returned to Workflow: COMPLETED, ERROR, etc.
1646 --End of Comments
1647 -------------------------------------------------------------------------------
1648 PROCEDURE launch_notify_tl_requesters (
1649 itemtype IN VARCHAR2,
1650 itemkey IN VARCHAR2,
1651 actid IN NUMBER,
1652 funcmode IN VARCHAR2,
1653 resultout OUT NOCOPY VARCHAR2
1654 ) IS
1655 l_proc_name VARCHAR2(30) := 'launch_notify_tl_requesters';
1656 l_progress VARCHAR2(3) := '000';
1657
1658 CURSOR l_temp_labor_lines_csr (
1659 p_po_header_id PO_HEADERS.po_header_id%TYPE,
1660 p_is_new_document VARCHAR2
1661 ) IS
1662 -- SQL What: Retrieves the Temp Labor lines of the given PO.
1663 -- We retrieve all lines if this is a new PO, or just the
1664 -- new lines if this is an existing PO.
1665 -- SQL Why: To send a notification to the requester of each line.
1666 SELECT POL.po_line_id,
1667 POL.contractor_first_name,
1668 POL.contractor_last_name,
1669 PJ.name job_name
1670 FROM po_headers POH,
1671 po_lines POL,
1672 per_jobs_vl PJ
1673 WHERE POH.po_header_id = p_po_header_id
1674 AND POH.po_header_id = POL.po_header_id -- JOIN
1675 AND POL.purchase_basis = 'TEMP LABOR'
1676 AND PJ.job_id = POL.job_id -- JOIN
1677 AND -- For a new document, we want all of the lines.
1678 ((p_is_new_document = 'Y') OR
1679
1680 -- For an existing document, we only want the new lines - i.e.
1681 -- the lines that do not have any older archived revisions.
1682 NOT EXISTS
1683 (SELECT 1
1684 FROM po_lines_archive PLA
1685 WHERE PLA.po_line_id = POL.po_line_id -- JOIN
1686 AND PLA.revision_num <> POH.revision_num));
1687
1688 l_tl_line_rec l_temp_labor_lines_csr%ROWTYPE;
1689
1690 l_document_id PO_HEADERS.po_header_id%TYPE;
1691 l_document_type PO_DOCUMENT_TYPES_ALL_B.document_type_code%TYPE;
1692 l_document_subtype PO_DOCUMENT_TYPES_ALL_B.document_subtype%TYPE;
1693 l_is_new_document VARCHAR2(1);
1694 l_contractor_or_job VARCHAR2(500);
1695 l_requester_id PO_REQUISITION_LINES.to_person_id%TYPE;
1696 l_approver_user_name WF_USERS.name%TYPE;
1697 l_item_key WF_ITEMS.item_key%TYPE;
1698 l_item_key_seq NUMBER;
1699 -- bug 12773562 : declaring local variable.
1700 l_clm_document_number varchar2(50);
1701
1702 BEGIN
1703 -- Do nothing if the Workflow mode is Cancel or Timeout.
1704 if (funcmode <> wf_engine.eng_run) then
1705 resultout := wf_engine.eng_null;
1706 return;
1707 end if;
1708
1709 l_document_type := po_wf_util_pkg.GetItemAttrText (
1710 itemtype => itemtype,
1711 itemkey => itemkey,
1712 aname => 'DOCUMENT_TYPE');
1713
1714 l_document_subtype := po_wf_util_pkg.GetItemAttrText (
1715 itemtype => itemtype,
1716 itemkey => itemkey,
1717 aname => 'DOCUMENT_SUBTYPE');
1718
1719 IF (g_po_wf_debug = 'Y') THEN
1720 PO_WF_DEBUG_PKG.insert_debug ( itemtype, itemkey,
1721 g_pkg_name||'.'||l_proc_name||': document type: ' || l_document_type
1722 || ', document subtype: ' || l_document_subtype );
1723 END IF;
1724
1725 -- This notification should only be sent for standard POs.
1726 IF (l_document_type = 'PO') AND (l_document_subtype = 'STANDARD') THEN
1727
1728 l_progress := '010';
1729
1730 l_document_id := po_wf_util_pkg.GetItemAttrNumber (
1731 itemtype => itemtype,
1732 itemkey => itemkey,
1733 aname => 'DOCUMENT_ID' );
1734
1735 l_is_new_document := po_wf_util_pkg.GetItemAttrText (
1736 itemtype => itemtype,
1737 itemkey => itemkey,
1738 aname => 'IS_NEW_DOCUMENT');
1739
1740 l_approver_user_name := po_wf_util_pkg.GetItemAttrText (
1741 itemtype => itemtype,
1742 itemkey => itemkey,
1743 aname => 'APPROVER_USER_NAME');
1744
1745 /* bug 12773562 In the approval notification the appropriate doc number in the correct
1746 document numbering format must be displayed.*/
1747 l_clm_document_number := po_wf_util_pkg.GetItemAttrText (
1748 itemtype => itemtype,
1749 itemkey => itemkey,
1750 aname => 'CLM_DOCUMENT_NUMBER');
1751
1752 -- Loop through the Temp Labor lines of this PO.
1753 FOR l_tl_line_rec
1754 IN l_temp_labor_lines_csr (l_document_id, l_is_new_document ) LOOP
1755
1756 l_progress := '020';
1757
1758 -- Determine the requester for this Temp Labor PO line.
1759 get_temp_labor_requester (
1760 p_po_line_id => l_tl_line_rec.po_line_id,
1761 x_requester_id => l_requester_id );
1762
1763 -- For the subject of the notification, use the contractor name,
1764 -- if available, or otherwise the job name.
1765 IF (l_tl_line_rec.contractor_first_name IS NOT NULL)
1766 OR (l_tl_line_rec.contractor_last_name IS NOT NULL) THEN
1767 l_contractor_or_job :=
1768 get_formatted_full_name( l_tl_line_rec.contractor_first_name,
1769 l_tl_line_rec.contractor_last_name );
1770 ELSE
1771 l_contractor_or_job := l_tl_line_rec.job_name;
1772 END IF;
1773
1774 -- Get a unique value from the sequence.
1775 SELECT PO_WF_ITEMKEY_S.nextval
1776 INTO l_item_key_seq
1777 FROM dual;
1778
1779 -- Generate the item key from the PO line ID and the sequence value.
1780 l_item_key := l_tl_line_rec.po_line_id || '-' || l_item_key_seq;
1781
1782 l_progress := '030';
1783
1784 -- Start a child process to send a notification for this PO line.
1785 start_po_line_wf_process (
1786 p_item_type => ItemType,
1787 p_item_key => l_item_key,
1788 p_process => 'NOTIFY_TEMP_LABOR_REQUESTER',
1789 p_parent_item_type => ItemType,
1790 p_parent_item_key => ItemKey,
1791 p_po_line_id => l_tl_line_rec.po_line_id,
1792 p_requester_id => l_requester_id,
1793 p_contractor_or_job => l_contractor_or_job,
1794 p_approver_user_name => l_approver_user_name,
1795 p_clm_document_number =>l_clm_document_number -- bug 12773562 passing parameter
1796 );
1797
1798 END LOOP; -- l_temp_labor_requesters_csr
1799
1800 END IF; -- Standard PO
1801
1802 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1803
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 wf_core.context( g_pkg_name, l_proc_name, l_progress );
1807 RAISE;
1808 END launch_notify_tl_requesters;
1809
1810 -------------------------------------------------------------------------------
1811 --Start of Comments
1812 --Name: start_po_line_wf_process
1813 --Pre-reqs:
1814 -- None.
1815 --Modifies:
1816 -- None.
1817 --Locks:
1818 -- None.
1819 --Function:
1820 -- Starts a child workflow process for the given PO line.
1821 --Parameters:
1822 --IN:
1823 --p_item_type
1824 -- Workflow Item Type of the child process.
1825 --p_item_key
1826 -- Workflow Item Key of the child process.
1827 --p_process
1828 -- Name of the child process.
1829 --p_parent_item_type
1830 -- Workflow Item Type of the parent process.
1831 --p_parent_item_key
1832 -- Workflow Item Key of the parent process.
1833 --p_po_line_id
1834 -- PO line for the child process
1835 --End of Comments
1836 -------------------------------------------------------------------------------
1837 PROCEDURE start_po_line_wf_process (
1838 p_item_type IN VARCHAR2,
1839 p_item_key IN VARCHAR2,
1840 p_process IN VARCHAR2,
1841 p_parent_item_type IN VARCHAR2,
1842 p_parent_item_key IN VARCHAR2,
1843 p_po_line_id IN NUMBER,
1844 p_requester_id IN NUMBER,
1845 p_contractor_or_job IN VARCHAR2,
1846 p_approver_user_name IN VARCHAR2,
1847 p_clm_document_number IN VARCHAR2 -- bug 12773562
1848 ) IS
1849 l_proc_name VARCHAR2(30) := 'start_po_line_wf_process';
1850 l_progress VARCHAR2(3) := '000';
1851 l_requester_user_name WF_USERS.name%TYPE;
1852 l_requester_disp_name WF_USERS.display_name%TYPE;
1853 l_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
1854 l_req_header_id PO_REQUISITION_HEADERS_ALL.requisition_header_id%TYPE;
1855 l_document_number PO_HEADERS_ALL.segment1%TYPE;
1856 l_create_cwk_url VARCHAR2(500);
1857 BEGIN
1858 IF (g_po_wf_debug = 'Y') THEN
1859 PO_WF_DEBUG_PKG.insert_debug ( p_item_type, p_item_key,
1860 substrb (
1861 g_pkg_name||'.'||l_proc_name||': p_item_type: '||p_item_type
1862 ||', p_item_key: '||p_item_key||', p_process: '||p_process
1863 ||', p_po_line_id: '||p_po_line_id,
1864 1, 1000 ) );
1865 END IF;
1866
1867 PO_REQAPPROVAL_INIT1.get_user_name (
1868 p_employee_id => p_requester_id,
1869 x_username => l_requester_user_name,
1870 x_user_display_name => l_requester_disp_name );
1871
1872 l_progress := '010';
1873
1874 -- Create the child process.
1875 wf_engine.CreateProcess(itemtype => p_item_type,
1876 itemkey => p_item_key,
1877 process => p_process );
1878
1879 l_progress := '020';
1880
1881 -- Set some workflow item attributes.
1882 po_wf_util_pkg.SetItemAttrNumber (itemtype => p_item_type,
1883 itemkey => p_item_key,
1884 aname => 'PO_LINE_ID',
1885 avalue => p_po_line_id);
1886
1887 po_wf_util_pkg.SetItemAttrNumber (itemtype => p_item_type,
1888 itemkey => p_item_key,
1889 aname => 'REQUESTER_ID',
1890 avalue => p_requester_id);
1891
1892 po_wf_util_pkg.SetItemAttrText ( ItemType => p_item_type,
1893 ItemKey => p_item_key,
1894 aname => 'REQUESTER_USER_NAME',
1895 avalue => l_requester_user_name );
1896
1897 po_wf_util_pkg.SetItemAttrText (itemtype => p_item_type,
1898 itemkey => p_item_key,
1899 aname => 'CONTRACTOR_OR_JOB',
1900 avalue => p_contractor_or_job);
1901
1902 po_wf_util_pkg.SetItemAttrText (itemtype => p_item_type,
1903 itemkey => p_item_key,
1904 aname => 'APPROVER_USER_NAME',
1905 avalue => p_approver_user_name);
1906
1907 -- bug 12773562 : Assigning CLM_DOCUMENT_NUMBER to workflow atribute
1908 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => p_item_type,
1909 itemkey => p_item_key,
1910 aname => 'CLM_DOCUMENT_NUMBER',
1911 avalue => p_clm_document_number);
1912
1913
1914 -- Bug 3441007 START
1915 -- For BLAF Compliance, we are now showing the links in the Related
1916 -- Applications section, so we need to set the URL attributes.
1917
1918 -- SQL What: Retrieve the PO_HEADER_ID and REQUISITION_HEADER_ID for the
1919 -- Temp Labor PO line.
1920 SELECT POH.po_header_id,
1921 POH.segment1,
1922 PRL.requisition_header_id
1923 INTO l_po_header_id,
1924 l_document_number,
1925 l_req_header_id
1926 FROM po_lines POL,
1927 po_headers POH,
1928 po_line_locations PLL,
1929 po_requisition_lines_all PRL
1930 WHERE POL.po_line_id = p_po_line_id
1931 AND POL.po_header_id = POH.po_header_id -- JOIN
1932 AND POL.po_line_id = PLL.po_line_id -- JOIN
1933 AND PLL.line_location_id = PRL.line_location_id (+); -- JOIN
1934
1935 po_wf_util_pkg.SetItemAttrText ( ItemType => p_item_type,
1936 ItemKey => p_item_key,
1937 aname => 'DOCUMENT_NUMBER',
1938 avalue => l_document_number );
1939
1940 -- Show the 'View Purchase Order' link.
1941 po_wf_util_pkg.SetItemAttrText ( ItemType => p_item_type,
1942 ItemKey => p_item_key,
1943 aname => 'VIEW_PO_URL',
1944 avalue =>
1945 'OA.jsp?OAFunc=POS_VIEW_ORDER&PoHeaderId='||l_po_header_id );
1946
1947 -- Show the 'View Requisition' link if there is a backing requisition.
1948 IF (l_req_header_id IS NOT NULL) THEN
1949
1950 po_wf_util_pkg.SetItemAttrText ( ItemType => p_item_type,
1951 ItemKey => p_item_key,
1952 aname => 'VIEW_REQ_URL',
1953 avalue =>
1954 'OA.jsp?OAFunc=ICX_POR_LAUNCH_IP&porMode=viewReq'
1955 ||'&porReqHeaderId='||l_req_header_id
1956 ||'&currNid=-&#NID-'); --bug 16515181
1957
1958 END IF; -- l_req_header_id
1959
1960 -- Show the 'Create Contractor Assignment' link if the required version of
1961 -- HR Self Service is installed.
1962 HR_PO_INFO.get_url_place_cwk ( p_po_line_id => p_po_line_id,
1963 p_destination => l_create_cwk_url );
1964
1965 IF (l_create_cwk_url IS NOT NULL) THEN
1966 po_wf_util_pkg.SetItemAttrText ( ItemType => p_item_type,
1967 ItemKey => p_item_key,
1968 aname => 'CREATE_CWK_ASSIGNMENT_URL',
1969 avalue => l_create_cwk_url );
1970 END IF;
1971 -- Bug 3441007 END
1972
1973 l_progress := '030';
1974
1975 -- Set the parent-child relationship between the 2 processes.
1976 wf_engine.SetItemParent (itemtype => p_item_type,
1977 itemkey => p_item_key,
1978 parent_itemtype => p_parent_item_type,
1979 parent_itemkey => p_parent_item_key,
1980 parent_context => NULL);
1981
1982 l_progress := '040';
1983
1984 -- Start the child process.
1985 wf_engine.StartProcess (itemtype => p_item_type,
1986 itemkey => p_item_key );
1987
1988 EXCEPTION
1989 WHEN OTHERS THEN
1990 wf_core.context( g_pkg_name, l_proc_name, l_progress );
1991 RAISE;
1992 END start_po_line_wf_process;
1993 -- <SVC_NOTIFICATIONS FPJ END>
1994
1995 --< Bug 3554754 Start >
1996 --------------------------------------------------------------------------------
1997 --Start of Comments
1998 --Name: get_approved_date
1999 --Pre-reqs:
2000 -- None.
2001 --Modifies:
2002 -- FND_MSG_PUB
2003 --Locks:
2004 -- None.
2005 --Function:
2006 -- Returns the approved date from the document header based upon p_doc_type and
2007 -- p_doc_id.
2008 --Parameters:
2009 --IN:
2010 --p_doc_type
2011 -- 'PO', 'PA', or 'RELEASE'.
2012 --p_doc_id
2013 --OUT:
2014 --x_return_status
2015 -- FND_API.g_ret_sts_success - on success
2016 -- FND_API.g_ret_sts_error - when p_doc_type is not 'PO', 'PA', or 'RELEASE'.
2017 -- FND_API.g_ret_sts_unexp_error - unexpected error
2018 --x_approved_date
2019 --End of Comments
2020 --------------------------------------------------------------------------------
2021 PROCEDURE get_approved_date
2022 ( p_doc_type IN VARCHAR2
2023 , p_doc_id IN VARCHAR2
2024 , x_return_status OUT NOCOPY VARCHAR2
2025 , x_approved_date OUT NOCOPY DATE
2026 )
2027 IS
2028 BEGIN
2029 x_return_status := FND_API.g_ret_sts_success;
2030
2031 -- Validate the input parameters
2032 IF (p_doc_type NOT IN ('PO','PA','RELEASE')) OR
2033 (p_doc_id IS NULL)
2034 THEN
2035 RAISE FND_API.g_exc_error;
2036 END IF;
2037
2038 IF (p_doc_type = 'RELEASE') THEN
2039 SELECT approved_date
2040 INTO x_approved_date
2041 FROM po_releases_all
2042 WHERE po_release_id = p_doc_id;
2043 ELSE
2044 SELECT approved_date
2045 INTO x_approved_date
2046 FROM po_headers_all
2047 WHERE po_header_id= p_doc_id;
2048 END IF;
2049
2050 IF g_debug_stmt THEN
2051 PO_DEBUG.debug_var(g_module_prefix||'get_approved_date','END','x_approved_date',x_approved_date);
2052 END IF;
2053
2054 EXCEPTION
2055 WHEN FND_API.g_exc_error THEN
2056 x_return_status := FND_API.g_ret_sts_error;
2057 IF g_debug_stmt THEN
2058 PO_DEBUG.debug_stmt(g_module_prefix||'get_approved_date','ERROR','Invalid input params');
2059 END IF;
2060 WHEN OTHERS THEN
2061 x_return_status := FND_API.g_ret_sts_unexp_error;
2062 PO_DEBUG.handle_unexp_error( p_pkg_name => g_pkg_name
2063 , p_proc_name => 'get_approved_date'
2064 );
2065 END get_approved_date;
2066 --< Bug 3554754 End >
2067
2068 --
2069 end PO_POAPPROVAL_INIT1;