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