[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_APPROVAL_PVT
Source
1 PACKAGE BODY OZF_FUND_APPROVAL_PVT AS
2 /* $Header: ozfvfapb.pls 120.3.12020000.3 2012/10/22 08:55:04 nirprasa ship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 -- OZF_Fund_Approval_PVT
7 --
8 -- PURPOSE
9 -- This package contains all transactions to be done for
10 -- Fund Request Approvals and Fund Transfer Approvals
11 -- in Oracle Marketing(Funds and Budgets)
12 --
13 -- HISTORY
14 -- 03/15/2001 MUMU PANDE CREATION
15 -- 11/06/2001 Mumu Pande Updation for substring add to all message strings
16 -- 6/13/2002 Mumu Pande FYI Messages were giving Numeric or Value Error / Changed the size of local varibles
17 -- 6/11/2002 Mumu Pande Added Code For Enhancement/Bug#2352621 -- Revert Status Functionality
18 -- 08/13/2002 Ying Zhao fix bug 2508539
19 -- 10/03/2002 Ying Zhao fix bug#2577992
20 -- 10/01/2003 Venkat Modur Fix for Previous Approvers Comments 2535600
21 -- 01/27/2003 Ying Zhao Fix bug 2771105(same as 11.5.8 bug 2753608) APPROVAL NOTE NOT SHOWING IN APPROVAL/REJECTION EMAIL
22 -- 04/23/2003 Ying Zhao Fix bug 2916480 - MKTCTR9 1159 CERT:AMS-TM:FUNDS ACCRUAL PROGRAM DOES NOT UPDATE BUDGET UTILIZATIO
23 -- 03-FEB-2004 julou Bug 3389553 - added token FUND_TYPE to notification messages.
24 -- 12/09/2005 kdass Bug 4870218 - SQL Repository fixes
25 -- 11-May-2006 asylvia Bug 5199719 - SQL Repository fixes
26 -- 22-Oct-2012 nirprasa Bug 14725235 - WHEN SETTING UP VOLUME REBATES BUDGETS, GETTING AN ERROR DURING THE APPROVAL
27 g_pkg_name CONSTANT VARCHAR2 (30) := 'OZF_Fund_Approval_PVT';
28 g_file_name CONSTANT VARCHAR2 (15) := 'ozfvfapb.pls';
29 g_fund_mode CONSTANT VARCHAR2 (15) := 'WORKFLOW';
30 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
31 --------------------------------------------------------------------------
32 procedure set_org_ctx (p_org_id IN NUMBER) is
33
34 begin
35
36 if p_org_id is not NULL then
37 MO_GLOBAL.init('OZF');
38 MO_GLOBAL.set_policy_context('S', p_org_id); -- R12 Enhancements
39 end if;
40
41 end set_org_ctx;
42
43 --------------------------------------------------------------------------
44
45 function find_org_id (p_fund_id IN NUMBER)
46 return number is
47
48 l_org_id number;
49
50 cursor get_fund_org_csr(p_id in number) is
51 select org_id
52 from ozf_funds_all_b
53 where fund_id = p_id;
54
55 begin
56
57 open get_fund_org_csr(p_fund_id);
58 fetch get_fund_org_csr into l_org_id;
59 close get_fund_org_csr;
60
61 return l_org_id;
62
63 end find_org_id;
64 --------------------------------------------------------------------------
65
66
67 -- PROCEDURE
68 -- Notify_requestor_FYI
69 --
70 -- PURPOSE
71 -- Generate the FYI Document for display in messages, either
72 -- text or html
73 -- IN
74 -- document_id - Item Key
75 -- display_type - either 'text/plain' or 'text/html'
76 -- document - document buffer
77 -- document_type - type of document buffer created, either 'text/plain'
78 -- or 'text/html'
79 -- OUT
80 -- USED BY
81 -- - Oracle MArketing Generic Apporval
82 -- HISTORY
83 -- 03/15/2001 MUMU PANDE CREATION
84 PROCEDURE notify_requestor_fyi (
85 document_id IN VARCHAR2,
86 display_type IN VARCHAR2,
87 document IN OUT NOCOPY VARCHAR2,
88 document_type IN OUT NOCOPY VARCHAR2
89 ) IS
90 l_api_name VARCHAR2 (61)
91 := g_pkg_name
92 || 'Notify_Requestor_FYI';
93 l_fund_id NUMBER;
94 l_fund_number VARCHAR2 (30);
95 l_fund_name VARCHAR2 (240);
96 l_hyphen_pos1 NUMBER;
97 l_fyi_notification VARCHAR2 (10000);
98 l_activity_type VARCHAR2 (30);
99 l_item_type VARCHAR2 (100);
100 l_item_key VARCHAR2 (100);
101 l_approval_type VARCHAR2 (30);
102 l_approver VARCHAR2 (200);
103 l_note VARCHAR2 (4000);
104 l_string VARCHAR2 (1000);
105 l_string1 VARCHAR2 (2500);
106 l_curr_code VARCHAR2 (150);
107 l_start_date DATE;
108 l_requester VARCHAR2 (360);
109 l_string2 VARCHAR2 (2500);
110 l_requested_amt NUMBER;
111 l_fund_type VARCHAR2(30) := NULL;
112 l_fund_meaning VARCHAR2(240) := NULL;
113 l_return_status VARCHAR2(1);
114 --kdass 09-DEC-2005 bug 4870218 - SQL ID# 14892720
115 --asylvia 11-May-2006 bug 5199719 - SQL ID 17778754
116 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
117 select b.fund_number , t.short_name , b.currency_code_tc , b.start_date_active , res.RESOURCE_NAME ,
118 b.fund_type
119 from ozf_funds_all_b b ,
120 ozf_funds_all_tl t ,
121 jtf_rs_resource_extns_tl res
122 where b.fund_id = p_fund_id
123 and b.fund_id = t.fund_id
124 and res.resource_id = b.owner
125 and userenv ( 'LANG' ) = t.language
126 and t.language = res.language;
127 /*
128 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
129 SELECT fund_number, short_name, currency_code_tc, start_date_active,
130 owner_full_name,fund_type
131 FROM ozf_fund_details_v
132 WHERE fund_id = p_fund_id;
133 */
134 BEGIN
135 IF G_DEBUG THEN
136 ozf_utility_pvt.debug_message (
137 l_api_name
138 || 'Entering'
139 || 'document id '
140 || document_id
141 );
142 END IF;
143 document_type := 'text/plain';
144 -- parse document_id for the ':' dividing item type name from item key value
145 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
146 -- release 2.5 version of this demo
147 l_hyphen_pos1 := INSTR (document_id, ':');
148 l_item_type :=
149 SUBSTR (document_id, 1, l_hyphen_pos1
150 - 1);
151 l_item_key := SUBSTR (document_id, l_hyphen_pos1
152 + 1);
153 l_activity_type :=
154 wf_engine.getitemattrtext (
155 itemtype=> l_item_type,
156 itemkey=> l_item_key,
157 aname => 'AMS_ACTIVITY_TYPE'
158 );
159 l_fund_id :=
160 wf_engine.getitemattrtext (
161 itemtype=> l_item_type,
162 itemkey=> l_item_key,
163 aname => 'AMS_ACTIVITY_ID'
164 );
165 l_requested_amt :=
166 wf_engine.getitemattrtext (
167 itemtype=> l_item_type,
168 itemkey=> l_item_key,
169 aname => 'AMS_REQUESTED_AMOUNT'
170 );
171 l_note :=
172 wf_engine.getitemattrtext (
173 itemtype=> l_item_type,
174 itemkey=> l_item_key,
175 aname => 'AMS_NOTES_FROM_REQUESTOR'
176 );
177 l_approver :=
178 wf_engine.getitemattrtext (
179 itemtype=> l_item_type,
180 itemkey=> l_item_key,
181 aname => 'AMS_APPROVER_DISPLAY_NAME'
182 );
183 OPEN c_fund_rec (l_fund_id);
184 FETCH c_fund_rec INTO l_fund_number,
185 l_fund_name,
186 l_curr_code,
187 l_start_date,
188 l_requester,
189 l_fund_type;
190 CLOSE c_fund_rec;
191 IF l_fund_type = 'QUOTA' THEN
192 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
193 p_lookup_code => 'QUOTA',
194 x_return_status => l_return_status,
195 x_meaning => l_fund_meaning);
196 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
197 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
198 p_lookup_code => 'BUDGET',
199 x_return_status => l_return_status,
200 x_meaning => l_fund_meaning);
201 END IF;
202 fnd_message.set_name ('AMS', 'AMS_WF_NTF_REQUESTER_FYI_SUB');
203 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
204 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
205 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
206 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
207 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
208 l_string := SUBSTR(fnd_message.get,1,1000);
209 /*
210 wf_engine.setitemattrtext (
211 itemtype=> l_item_type,
212 itemkey=> l_item_key,
213 aname => 'FYI_SUBJECT',
214 avalue=> l_string
215 );
216 */
217 fnd_message.set_name ('AMS', 'AMS_WF_NTF_ROOTBUDGET_REQ_INFO');
218 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
219 fnd_message.set_token ('BUDGET_NUMBER', l_fund_number, FALSE);
220 fnd_message.set_token ('OWNER', l_requester, FALSE);
221 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
222 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
223 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
224 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
225 fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
226 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
227 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
228 --l_string1 := fnd_message.get;
229 /*
230 l_note := wf_engine.getitemattrtext(
231 itemtype => l_item_type
232 ,itemkey => l_item_key
233 ,aname => 'NOTE');
234 l_forwarder :=
235 wf_engine.getitemattrtext(
236 itemtype => l_item_type
237 ,itemkey => l_item_key
238 ,aname => 'AMS_FORWARD_FROM_USERNAME');
239 */
240 -- IF (display_type = 'text/plain') THEN
241 l_fyi_notification := SUBSTR(l_string
242 || fnd_global.local_chr (10)
243 || l_string1
244 || fnd_global.local_chr (10)
245 || l_string2,1,10000);
246 document := document
247 || l_fyi_notification;
248 document_type := 'text/plain';
249 RETURN;
250 -- END IF;
251 /* IF (display_type = 'text/html') THEN
252 l_fyi_notification :=
253 l_string ||
254 FND_GLOBAL.LOCAL_CHR(10) ||
255 l_string1 ||
256 FND_GLOBAL.LOCAL_CHR(10) ||
257 l_string2;
258 document := document||l_appreq_notification;
259 document_type := 'text/html';
260 RETURN;
261 END IF;
262 */
263 EXCEPTION
264 WHEN OTHERS THEN
265 wf_core.context (
266 'AMSGAPP',
267 'Notify_requestor_FYI',
268 l_item_type,
269 l_item_key
270 );
271 RAISE;
272 END notify_requestor_fyi;
273 --------------------------------------------------------------------------
274 -- PROCEDURE
275 -- Notify_requestor_of Approval
276 --
277 -- PURPOSE
278 -- Generate the Approval Document for display in messages, either
279 -- text or html
280 -- IN
281 -- document_id - Item Key
282 -- display_type - either 'text/plain' or 'text/html'
283 -- document - document buffer
284 -- document_type - type of document buffer created, either 'text/plain'
285 -- or 'text/html'
286 -- OUT
287 -- USED BY
288 -- - Oracle MArketing Generic Apporval
289 -- HISTORY
290 -- 03/15/2001 MUMU PANDE CREATION
291 ----------------------------------------------------------------------------
292 PROCEDURE notify_requestor_of_approval (
293 document_id IN VARCHAR2,
294 display_type IN VARCHAR2,
295 document IN OUT NOCOPY VARCHAR2,
296 document_type IN OUT NOCOPY VARCHAR2
297 ) IS
298 l_api_name VARCHAR2 (100)
299 := g_pkg_name
300 || 'Notify_Requestor_of_approval';
301 l_fund_id NUMBER;
302 l_fund_number VARCHAR2 (30);
303 l_fund_name VARCHAR2 (240);
304 l_hyphen_pos1 NUMBER;
305 l_appr_notification VARCHAR2 (10000);
306 l_activity_type VARCHAR2 (30);
307 l_item_type VARCHAR2 (80);
308 l_item_key VARCHAR2 (80);
309 l_approval_type VARCHAR2 (30);
310 l_approver VARCHAR2 (200);
311 l_note VARCHAR2 (4000);
312 l_approver_note VARCHAR2 (4000);
313 l_approved_amt NUMBER;
314 l_string VARCHAR2 (1000);
315 l_string1 VARCHAR2 (2500);
316 l_curr_code VARCHAR2 (150);
317 l_start_date DATE;
318 l_requester VARCHAR2 (360);
319 l_string2 VARCHAR2 (2500);
320 l_requested_amt NUMBER;
321 l_fund_type VARCHAR2(30);
322 l_fund_meaning VARCHAR2(240);
323 l_return_status VARCHAR2(1);
324
325 --kdass 09-DEC-2005 bug 4870218 - SQL ID# 14892679
326 --asylvia 11-May-2006 bug 5199719 - SQL ID 17778783
327 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
328 select b.fund_number , t.short_name , b.currency_code_tc , b.start_date_active , res.RESOURCE_NAME ,
329 b.fund_type
330 from ozf_funds_all_b b ,
331 ozf_funds_all_tl t ,
332 jtf_rs_resource_extns_tl res
333 where b.fund_id = p_fund_id
334 and b.fund_id = t.fund_id
335 and res.resource_id = b.owner
336 and userenv ( 'LANG' ) = t.language
337 and t.language = res.language;
338 /*
339 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
340 SELECT fund_number, short_name, currency_code_tc, start_date_active,
341 owner_full_name, fund_type -- ,original_budget 01/28/2003 yzhao: requested amount is not original_budget
342 FROM ozf_fund_details_v
343 WHERE fund_id = p_fund_id;
344 */
345 BEGIN
346 IF G_DEBUG THEN
347 ozf_utility_pvt.debug_message (
348 l_api_name
349 || 'Entering'
350 || 'document id '
351 || document_id
352 );
353 END IF;
354 document_type := 'text/plain';
355 -- parse document_id for the ':' dividing item type name from item key value
356 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
357 -- release 2.5 version of this demo
358 l_hyphen_pos1 := INSTR (document_id, ':');
359 l_item_type :=
360 SUBSTR (document_id, 1, l_hyphen_pos1
361 - 1);
362 l_item_key := SUBSTR (document_id, l_hyphen_pos1
363 + 1);
364 l_activity_type :=
365 wf_engine.getitemattrtext (
366 itemtype=> l_item_type,
367 itemkey=> l_item_key,
368 aname => 'AMS_ACTIVITY_TYPE'
369 );
370 l_fund_id :=
371 wf_engine.getitemattrtext (
372 itemtype=> l_item_type,
373 itemkey=> l_item_key,
374 aname => 'AMS_ACTIVITY_ID'
375 );
376 l_approved_amt :=
377 wf_engine.getitemattrtext (
378 itemtype=> l_item_type,
379 itemkey=> l_item_key,
380 aname => 'AMS_AMOUNT'
381 );
382 l_note :=
383 wf_engine.getitemattrtext (
384 itemtype=> l_item_type,
385 itemkey=> l_item_key,
386 aname => 'AMS_NOTES_FROM_REQUESTOR'
387 );
388 l_approver :=
389 wf_engine.getitemattrtext (
390 itemtype=> l_item_type,
391 itemkey=> l_item_key,
392 aname => 'AMS_APPROVER'
393 );
394 -- yzhao: 01/28/2003 get requested amount from workflow. After approval, requested_amount may not equal orignal_budget
395 l_requested_amt :=
396 wf_engine.getitemattrtext (
397 itemtype=> l_item_type,
398 itemkey=> l_item_key,
399 aname => 'AMS_REQUESTED_AMOUNT'
400 );
401 OPEN c_fund_rec (l_fund_id);
402 FETCH c_fund_rec INTO l_fund_number,
403 l_fund_name,
404 l_curr_code,
405 l_start_date,
406 l_requester,
407 l_fund_type;
408 -- l_requested_amt; 01/28/2003 yzhao: requested amount is not original_budget
409 CLOSE c_fund_rec;
410 IF l_fund_type = 'QUOTA' THEN
411 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
412 p_lookup_code => 'QUOTA',
413 x_return_status => l_return_status,
414 x_meaning => l_fund_meaning);
415 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
416 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
417 p_lookup_code => 'BUDGET',
418 x_return_status => l_return_status,
419 x_meaning => l_fund_meaning);
420 END IF;
421
422 fnd_message.set_name ('AMS', 'AMS_WF_NTF_REQUESTER_APP_SUB');
423 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
424 --fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
425 --fnd_message.set_token ('AMOUNT', l_approved_amt, FALSE);
426 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
427 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
428 l_string := Substr(FND_MESSAGE.Get,1,1000);
429 fnd_message.set_name ('AMS', 'AMS_WF_NTF_ROOTBUDGET_REQ_INFO');
430 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
431 fnd_message.set_token ('BUDGET_NUMBER', l_fund_number, FALSE);
432 fnd_message.set_token ('OWNER', l_requester, FALSE);
433 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
434 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
435 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
436 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
437 fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
438 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
439 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
440 --l_string1 := fnd_message.get;
441 /*
442 l_note := wf_engine.getitemattrtext(
443 itemtype => l_item_type
444 ,itemkey => l_item_key
445 ,aname => 'NOTE');
446 l_forwarder :=
447 wf_engine.getitemattrtext(
448 itemtype => l_item_type
449 ,itemkey => l_item_key
450 ,aname => 'AMS_FORWARD_FROM_USERNAME');
451 */
452 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) APPROVAL NOTE NOT SHOWING IN APPROVAL EMAIL */
453 l_approver_note :=
454 wf_engine.getitemattrtext (
455 itemtype=> l_item_type,
456 itemkey=> l_item_key,
457 aname => 'APPROVAL_NOTE'
458 );
459 fnd_message.set_name('AMS', 'AMS_WF_NTF_REQUESTER_ADDENDUM');
460 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
461 fnd_message.set_token('CURRENCY_CODE', l_curr_code, FALSE);
462 fnd_message.set_token('AMOUNT', l_approved_amt, FALSE);
463 fnd_message.set_token('NOTES_FROM_APPROVER', l_approver_note, FALSE);
464 l_string2 := fnd_message.get;
465 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) ends - APPROVAL NOTE NOT SHOWING IN APPROVAL EMAIL */
466 -- IF (display_type = 'text/plain') THEN
467 l_appr_notification := SUBSTR(l_string
468 || fnd_global.local_chr (10)
469 || l_string1
470 || fnd_global.local_chr (10)
471 || l_string2,1,10000);
472 document := document
473 || l_appr_notification;
474 document_type := 'text/plain';
475 RETURN;
476 -- END IF;
477 /* IF (display_type = 'text/html') THEN
478 l_appreq_notification :=
479 l_string ||
480 FND_GLOBAL.LOCAL_CHR(10) ||
481 l_string1 ||
482 FND_GLOBAL.LOCAL_CHR(10) ||
483 l_string2;
484 document := document||l_appreq_notification;
485 document_type := 'text/html';
486 RETURN;
487 END IF;
488 */
489 EXCEPTION
490 WHEN OTHERS THEN
491 wf_core.context (
492 'AMSGAPP',
493 'Notify_Requestor_of_approval',
494 l_item_type,
495 l_item_key
496 );
497 RAISE;
498 END notify_requestor_of_approval;
499 --------------------------------------------------------------------------
500 -- PROCEDURE
501 -- Notify_requestor_of rejection
502 --
503 -- PURPOSE
504 -- Generate the Rejection Document for display in messages, either
505 -- text or html
506 -- IN
507 -- document_id - Item Key
508 -- display_type - either 'text/plain' or 'text/html'
509 -- document - document buffer
510 -- document_type - type of document buffer created, either 'text/plain'
511 -- or 'text/html'
512 -- OUT
513 -- USED BY
514 -- - Oracle MArketing Generic Apporval
515 -- HISTORY
516 -- 03/15/2001 MUMU PANDE CREATION
517 -------------------------------------------------------------------------------
518 PROCEDURE notify_requestor_of_rejection (
519 document_id IN VARCHAR2,
520 display_type IN VARCHAR2,
521 document IN OUT NOCOPY VARCHAR2,
522 document_type IN OUT NOCOPY VARCHAR2
523 ) IS
524 l_api_name VARCHAR2 (100)
525 := g_pkg_name
526 || 'Notify_Requestor_of_rejection';
527 l_fund_id NUMBER;
528 l_fund_number VARCHAR2 (30);
529 l_fund_name VARCHAR2 (240);
530 l_hyphen_pos1 NUMBER;
531 l_rej_notification VARCHAR2 (10000);
532 l_activity_type VARCHAR2 (30);
533 l_item_type VARCHAR2 (80);
534 l_item_key VARCHAR2 (80);
535 l_approval_type VARCHAR2 (30);
536 l_approver VARCHAR2 (200);
537 l_note VARCHAR2 (4000);
538 l_string VARCHAR2 (1000);
539 l_string1 VARCHAR2 (2500);
540 l_curr_code VARCHAR2 (150);
541 l_start_date DATE;
542 l_requester VARCHAR2 (360);
543 l_string2 VARCHAR2 (2500);
544 l_requested_amt NUMBER;
545 l_fund_type VARCHAR2(30);
546 l_fund_meaning VARCHAR2(240);
547 l_return_status VARCHAR2(1);
548 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
549 SELECT fund_number, short_name, currency_code_tc, start_date_active, fund_type
550 FROM ozf_funds_all_vl
551 WHERE fund_id = p_fund_id;
552 BEGIN
553 IF G_DEBUG THEN
554 ozf_utility_pvt.debug_message (
555 l_api_name
556 || 'Entering'
557 || 'document id '
558 || document_id
559 );
560 END IF;
561 document_type := 'text/plain';
562 -- parse document_id for the ':' dividing item type name from item key value
563 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
564 -- release 2.5 version of this demo
565 l_hyphen_pos1 := INSTR (document_id, ':');
566 l_item_type :=
567 SUBSTR (document_id, 1, l_hyphen_pos1
568 - 1);
569 l_item_key := SUBSTR (document_id, l_hyphen_pos1
570 + 1);
571 l_activity_type :=
572 wf_engine.getitemattrtext (
573 itemtype=> l_item_type,
574 itemkey=> l_item_key,
575 aname => 'AMS_ACTIVITY_TYPE'
576 );
577 l_fund_id :=
578 wf_engine.getitemattrtext (
579 itemtype=> l_item_type,
580 itemkey=> l_item_key,
581 aname => 'AMS_ACTIVITY_ID'
582 );
583 l_note :=
584 wf_engine.getitemattrtext (
585 itemtype=> l_item_type,
586 itemkey=> l_item_key,
587 aname => 'AMS_NOTES_FROM_REQUESTOR'
588 );
589 l_approver :=
590 wf_engine.getitemattrtext (
591 itemtype=> l_item_type,
592 itemkey=> l_item_key,
593 aname => 'AMS_APPROVER'
594 );
595 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608): request_amount, owner not shown in reject notification */
596 l_requested_amt :=
597 wf_engine.getitemattrtext (
598 itemtype=> l_item_type,
599 itemkey=> l_item_key,
600 aname => 'AMS_REQUESTED_AMOUNT'
601 );
602 l_requester :=
603 wf_engine.getitemattrtext (
604 itemtype=> l_item_type,
605 itemkey=> l_item_key,
606 aname => 'AMS_REQUESTER'
607 );
608 OPEN c_fund_rec (l_fund_id);
609 FETCH c_fund_rec INTO l_fund_number,
610 l_fund_name,
611 l_curr_code,
612 l_start_date,
613 l_fund_type;
614 IF l_fund_type = 'QUOTA' THEN
615 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
616 p_lookup_code => 'QUOTA',
617 x_return_status => l_return_status,
618 x_meaning => l_fund_meaning);
619 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
620 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
621 p_lookup_code => 'BUDGET',
622 x_return_status => l_return_status,
623 x_meaning => l_fund_meaning);
624 END IF;
625 CLOSE c_fund_rec;
626 fnd_message.set_name ('AMS', 'AMS_WF_NTF_REQUESTER_REJ_SUB');
627 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
628 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
629 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
630 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
631 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
632 -- 01/12/2001 mpande
633 --fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
634 l_string := Substr(FND_MESSAGE.Get,1,1000);
635 --l_string := fnd_message.get;
636 /*
637 wf_engine.setitemattrtext (
638 itemtype=> l_item_type,
639 itemkey=> l_item_key,
640 aname => 'REJECT_SUBJECT',
641 avalue=> l_string
642 );
643 */
644 fnd_message.set_name ('AMS', 'AMS_WF_NTF_ROOTBUDGET_REQ_INFO');
645 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
646 fnd_message.set_token ('BUDGET_NUMBER', l_fund_number, FALSE);
647 fnd_message.set_token ('OWNER', l_requester, FALSE);
648 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
649 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
650 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
651 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
652 fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
653 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
654 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
655 /*
656 l_note := wf_engine.getitemattrtext(
657 itemtype => l_item_type
658 ,itemkey => l_item_key
659 ,aname => 'NOTE');
660 l_forwarder :=
661 wf_engine.getitemattrtext(
662 itemtype => l_item_type
663 ,itemkey => l_item_key
664 ,aname => 'AMS_FORWARD_FROM_USERNAME');
665 */
666 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) APPROVer's NOTE NOT SHOWING IN rejection EMAIL */
667 l_note :=
668 wf_engine.getitemattrtext (
669 itemtype=> l_item_type,
670 itemkey=> l_item_key,
671 aname => 'APPROVAL_NOTE'
672 );
673 fnd_message.set_name ('AMS', 'AMS_WF_NTF_APPROVER_NOTE');
674 fnd_message.set_token ('NOTES_FROM_APPROVER', l_note, FALSE);
675 l_string2 := SUBSTR(FND_MESSAGE.Get, 1, 2500);
676 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) ends - APPROVer's NOTE NOT SHOWING IN rejection EMAIL */
677 -- IF (display_type = 'text/plain') THEN
678 l_rej_notification := SUBSTR(l_string
679 || fnd_global.local_chr (10)
680 || l_string1
681 || fnd_global.local_chr (10)
682 || l_string2,1,10000);
683 document := document
684 || l_rej_notification;
685 document_type := 'text/plain';
686 RETURN;
687 EXCEPTION
688 WHEN OTHERS THEN
689 wf_core.context (
690 'AMSGAPP',
691 'Notify_requestor_of_rejection',
692 l_item_type,
693 l_item_key
694 );
695 RAISE;
696 END notify_requestor_of_rejection;
697 --------------------------------------------------------------------------
698 -- PROCEDURE
699 -- notify_approval_required
700 --
701 -- PURPOSE
702 -- Generate the Rejection Document for display in messages, either
703 -- text or html
704 -- IN
705 -- document_id - Item Key
706 -- display_type - either 'text/plain' or 'text/html'
707 -- document - document buffer
708 -- document_type - type of document buffer created, either 'text/plain'
709 -- or 'text/html'
710 -- OUT
711 -- USED BY
712 -- - Oracle MArketing Generic Apporval
713 -- HISTORY
714 -- 03/15/2001 MUMU PANDE CREATION
715 PROCEDURE notify_approval_required (
716 document_id IN VARCHAR2,
717 display_type IN VARCHAR2,
718 document IN OUT NOCOPY VARCHAR2,
719 document_type IN OUT NOCOPY VARCHAR2
720 ) IS
721 l_api_name VARCHAR2 (100)
722 := g_pkg_name
723 || 'Notify_approval_required';
724 l_fund_id NUMBER;
725 l_fund_number VARCHAR2 (30);
726 l_fund_name VARCHAR2 (240);
727 l_hyphen_pos1 NUMBER;
728 l_appreq_notification VARCHAR2 (10000);
729 l_activity_type VARCHAR2 (30);
730 l_item_type VARCHAR2 (30);
731 l_item_key VARCHAR2 (30);
732 l_approval_type VARCHAR2 (30);
733 l_forwarder VARCHAR2 (150);
734 l_note VARCHAR2 (4000);
735 l_requested_amt NUMBER;
736 l_string VARCHAR2 (1000);
737 l_string1 VARCHAR2 (2500);
738 l_approver VARCHAR2 (200);
739 l_curr_code VARCHAR2 (30);
740 l_start_date DATE;
741 l_requester VARCHAR2 (360);
742 l_string2 VARCHAR2 (2500);
743 l_approval_date VARCHAR2(30);
744 l_fund_type VARCHAR2(30);
745 l_fund_meaning VARCHAR2(240);
746 l_return_status VARCHAR2(1);
747
748 --kdass 09-DEC-2005 bug 4870218 - SQL ID# 14892648
749 --asylvia 11-May-2006 bug 5199719 - SQL ID 17778839
750 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
751 select b.fund_number , t.short_name , b.currency_code_tc , b.start_date_active , res.RESOURCE_NAME ,
752 b.fund_type
753 from ozf_funds_all_b b ,
754 ozf_funds_all_tl t ,
755 jtf_rs_resource_extns_tl res
756 where b.fund_id = p_fund_id
757 and b.fund_id = t.fund_id
758 and res.resource_id = b.owner
759 and userenv ( 'LANG' ) = t.language
760 and t.language = res.language;
761 /*
762 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
763 SELECT fund_number, short_name, currency_code_tc, start_date_active,
764 owner_full_name, fund_type
765 FROM ozf_fund_details_v
766 WHERE fund_id = p_fund_id;
767 */
768 BEGIN
769 IF G_DEBUG THEN
770 ozf_utility_pvt.debug_message (
771 l_api_name
772 || 'Entering'
773 || 'document id '
774 || document_id
775 );
776 END IF;
777 document_type := 'text/plain';
778 -- parse document_id for the ':' dividing item type name from item key value
779 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
780 -- release 2.5 version of this demo
781 l_hyphen_pos1 := INSTR (document_id, ':');
782 l_item_type :=
783 SUBSTR (document_id, 1, l_hyphen_pos1
784 - 1);
785 l_item_key := SUBSTR (document_id, l_hyphen_pos1
786 + 1);
787 l_activity_type :=
788 wf_engine.getitemattrtext (
789 itemtype=> l_item_type,
790 itemkey=> l_item_key,
791 aname => 'AMS_ACTIVITY_TYPE'
792 );
793 l_fund_id :=
794 wf_engine.getitemattrtext (
795 itemtype=> l_item_type,
796 itemkey=> l_item_key,
797 aname => 'AMS_ACTIVITY_ID'
798 );
799 l_note :=
800 wf_engine.getitemattrtext (
801 itemtype=> l_item_type,
802 itemkey=> l_item_key,
803 aname => 'AMS_NOTES_FROM_REQUESTOR'
804 );
805 l_approver :=
806 wf_engine.getitemattrtext (
807 itemtype=> l_item_type,
808 itemkey=> l_item_key,
809 aname => 'AMS_APPROVER_DISPLAY_NAME'
810 );
811 l_requested_amt :=
812 wf_engine.getitemattrtext (
813 itemtype=> l_item_type,
814 itemkey=> l_item_key,
815 aname => 'AMS_REQUESTED_AMOUNT'
816 );
817 l_requester :=
818 wf_engine.getitemattrtext (
819 itemtype=> l_item_type,
820 itemkey=> l_item_key,
821 aname => 'AMS_REQUESTER'
822 );
823 OPEN c_fund_rec (l_fund_id);
824 FETCH c_fund_rec INTO l_fund_number,
825 l_fund_name,
826 l_curr_code,
827 l_start_date,
828 l_requester,
829 l_fund_type;
830 CLOSE c_fund_rec;
831 IF l_fund_type = 'QUOTA' THEN
832 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
833 p_lookup_code => 'QUOTA',
834 x_return_status => l_return_status,
835 x_meaning => l_fund_meaning);
836 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
837 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
838 p_lookup_code => 'BUDGET',
839 x_return_status => l_return_status,
840 x_meaning => l_fund_meaning);
841 END IF;
842 fnd_message.set_name ('AMS', 'AMS_WF_NTF_APPROVER_OF_REQ_SUB');
843 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
844 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
845 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
846 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
847 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
848 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
849 l_string := SUBSTR(fnd_message.get,1,1000);
850 /*
851 wf_engine.setitemattrtext (
852 itemtype=> l_item_type,
853 itemkey=> l_item_key,
854 aname => 'APP_SUBJECT',
855 avalue=> l_string
856 );
857 */
858 fnd_message.set_name ('AMS', 'AMS_WF_NTF_ROOTBUDGET_REQ_INFO');
859 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
860 fnd_message.set_token ('BUDGET_NUMBER', l_fund_number, FALSE);
861 fnd_message.set_token ('OWNER', l_requester, FALSE);
862 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
863 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
864 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
865 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
866 fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
867 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
868 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
869 l_note :=
870 NVL(wf_engine.getitemattrtext (
871 itemtype=> l_item_type,
872 itemkey=> l_item_key,
873 aname => 'AMS_PREV_APPROVER_NOTE'
874 ),'-');
875 l_forwarder :=
876 NVL(wf_engine.getitemattrtext (
877 itemtype=> l_item_type,
878 itemkey=> l_item_key,
879 aname => 'AMS_PREV_APPROVER_DISP_NAME'
880 ),'-');
881 l_approval_date := NVL(to_char(wf_engine.getitemattrdate (
882 itemtype => l_item_type,
883 itemkey => l_item_key,
884 aname => 'AMS_APPROVAL_DATE')),'-');
885 fnd_message.set_name ('AMS', 'AMS_WF_NTF_APPROVER_ADDENDUM');
886 fnd_message.set_token ('PREV_APPROVER_NAME', l_forwarder, FALSE);
887 fnd_message.set_token ('APPROVAL_DATE', l_approval_date, FALSE);
888 fnd_message.set_token ('COMMENTS', l_note, FALSE);
889 /* will set the tokens later
890 fnd_message.set_token('BUDGET_NAME',l_fund_name,false);
891 fnd_message.set_token('BUDGET_NUMBER',l_fund_number,false);
892 */
893 -- l_string2 := Substr(FND_MESSAGE.Get,1,2500);
894 l_string2 := SUBSTR(fnd_message.get,1,2500);
895 -- IF (display_type = 'text/plain') THEN
896 l_appreq_notification := l_string
897 || fnd_global.local_chr (10)
898 || l_string1
899 || fnd_global.local_chr (10)
900 || l_string2;
901 document := document
902 || l_appreq_notification;
903 document_type := 'text/plain';
904 RETURN;
905 -- END IF;
906 /* IF (display_type = 'text/html') THEN
907 l_appreq_notification :=
908 l_string ||
909 FND_GLOBAL.LOCAL_CHR(10) ||
910 l_string1 ||
911 FND_GLOBAL.LOCAL_CHR(10) ||
912 l_string2;
913 document := document||l_appreq_notification;
914 document_type := 'text/html';
915 RETURN;
916 END IF;
917 */
918 EXCEPTION
919 WHEN OTHERS THEN
920 wf_core.context (
921 'AMSGAPP',
922 'notify_approval_required',
923 l_item_type,
924 l_item_key
925 );
926 RAISE;
927 END notify_approval_required;
928 --------------------------------------------------------------------------
929 -- PROCEDURE
930 -- notify_appr_req_reminder
931 --
932 -- PURPOSE
933 -- Generate the Rejection Document for display in messages, either
934 -- text or html
935 -- IN
936 -- document_id - Item Key
937 -- display_type - either 'text/plain' or 'text/html'
938 -- document - document buffer
939 -- document_type - type of document buffer created, either 'text/plain'
940 -- or 'text/html'
941 -- OUT
942 -- USED BY
943 -- - Oracle MArketing Generic Apporval
944 -- HISTORY
945 -- 03/15/2001 MUMU PANDE CREATION
946 PROCEDURE notify_appr_req_reminder (
947 document_id IN VARCHAR2,
948 display_type IN VARCHAR2,
949 document IN OUT NOCOPY VARCHAR2,
950 document_type IN OUT NOCOPY VARCHAR2
951 ) IS
952 l_api_name VARCHAR2 (100)
953 := g_pkg_name
954 || 'notify_appr_req_reminder';
955 l_fund_id NUMBER;
956 l_fund_number VARCHAR2 (30);
957 l_fund_name VARCHAR2 (240);
958 l_hyphen_pos1 NUMBER;
959 l_apprem_notification VARCHAR2 (10000);
960 l_activity_type VARCHAR2 (30);
961 l_item_type VARCHAR2 (80);
962 l_item_key VARCHAR2 (80);
963 l_approval_type VARCHAR2 (30);
964 l_approver VARCHAR2 (200);
965 l_note VARCHAR2 (4000);
966 l_forwarder VARCHAR2 (150);
967 l_string VARCHAR2 (1000);
968 l_string1 VARCHAR2 (2500);
969 l_curr_code VARCHAR2 (30);
970 l_start_date DATE;
971 l_requester VARCHAR2 (360);
972 l_string2 VARCHAR2 (2500);
973 l_requested_amt NUMBER;
974 l_approved_amt NUMBER;
975 l_approval_date VARCHAR2(30);
976 l_fund_type VARCHAR2(30);
977 l_fund_meaning VARCHAR2(240);
978 l_return_status VARCHAR2(1);
979 CURSOR c_fund_rec (p_fund_id IN NUMBER) IS
980 SELECT fund_number, short_name, currency_code_tc, start_date_active, fund_type
981 FROM ozf_funds_all_vl
982 WHERE fund_id = p_fund_id;
983 BEGIN
984 IF G_DEBUG THEN
985 ozf_utility_pvt.debug_message (
986 l_api_name
987 || 'Entering'
988 || 'document id '
989 || document_id
990 );
991 END IF;
992 document_type := 'text/plain';
993 -- parse document_id for the ':' dividing item type name from item key value
994 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
995 -- release 2.5 version of this demo
996 l_hyphen_pos1 := INSTR (document_id, ':');
997 l_item_type :=
998 SUBSTR (document_id, 1, l_hyphen_pos1
999 - 1);
1000 l_item_key := SUBSTR (document_id, l_hyphen_pos1
1001 + 1);
1002 l_activity_type :=
1003 wf_engine.getitemattrtext (
1004 itemtype=> l_item_type,
1005 itemkey=> l_item_key,
1006 aname => 'AMS_ACTIVITY_TYPE'
1007 );
1008 l_fund_id :=
1009 wf_engine.getitemattrtext (
1010 itemtype=> l_item_type,
1011 itemkey=> l_item_key,
1012 aname => 'AMS_ACTIVITY_ID'
1013 );
1014 l_note :=
1015 wf_engine.getitemattrtext (
1016 itemtype=> l_item_type,
1017 itemkey=> l_item_key,
1018 aname => 'AMS_NOTES_FROM_REQUESTOR'
1019 );
1020 l_approver :=
1021 wf_engine.getitemattrtext (
1022 itemtype=> l_item_type,
1023 itemkey=> l_item_key,
1024 aname => 'AMS_APPROVER'
1025 );
1026 l_requested_amt :=
1027 wf_engine.getitemattrtext (
1028 itemtype=> l_item_type,
1029 itemkey=> l_item_key,
1030 aname => 'AMS_REQUESTED_AMOUNT'
1031 );
1032 l_requester :=
1033 wf_engine.getitemattrtext (
1034 itemtype=> l_item_type,
1035 itemkey=> l_item_key,
1036 aname => 'AMS_REQUESTER'
1037 );
1038 OPEN c_fund_rec (l_fund_id);
1039 FETCH c_fund_rec INTO l_fund_number,
1040 l_fund_name,
1041 l_curr_code,
1042 l_start_date,
1043 l_fund_type;
1044 CLOSE c_fund_rec;
1045 IF l_fund_type = 'QUOTA' THEN
1046 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
1047 p_lookup_code => 'QUOTA',
1048 x_return_status => l_return_status,
1049 x_meaning => l_fund_meaning);
1050 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
1051 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
1052 p_lookup_code => 'BUDGET',
1053 x_return_status => l_return_status,
1054 x_meaning => l_fund_meaning);
1055 END IF;
1056 fnd_message.set_name ('AMS', 'AMS_WF_NTF_APPROVER_OF_REQ_SUB');
1057 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
1058 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
1059 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
1060 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
1061 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
1062 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1063 l_string := SUBSTR(fnd_message.get,1,1000);
1064 /*
1065 wf_engine.setitemattrtext (
1066 itemtype=> l_item_type,
1067 itemkey=> l_item_key,
1068 aname => 'APP_SUBJECT',
1069 avalue=> l_string
1070 );
1071 */
1072 fnd_message.set_name ('AMS', 'AMS_WF_NTF_ROOTBUDGET_REQ_INFO');
1073 fnd_message.set_token ('BUDGET_NAME', l_fund_name, FALSE);
1074 fnd_message.set_token ('BUDGET_NUMBER', l_fund_number, FALSE);
1075 fnd_message.set_token ('OWNER', l_requester, FALSE);
1076 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
1077 fnd_message.set_token ('CURRENCY_CODE', l_curr_code, FALSE);
1078 fnd_message.set_token ('AMOUNT', l_requested_amt, FALSE);
1079 fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE);
1080 fnd_message.set_token ('DESCRIPTION', l_note, FALSE);
1081 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
1082 -- l_string1 := Substr(FND_MESSAGE.Get,1,2500);
1083 l_note :=
1084 NVL(wf_engine.getitemattrtext (
1085 itemtype=> l_item_type,
1086 itemkey=> l_item_key,
1087 aname => 'AMS_PREV_APPROVER_NOTE'
1088 ),'-');
1089 l_forwarder :=
1090 NVL(wf_engine.getitemattrtext (
1091 itemtype=> l_item_type,
1092 itemkey=> l_item_key,
1093 aname => 'AMS_PREV_APPROVER_DISP_NAME'
1094 ),'-');
1095 l_approval_date := NVL(to_char(wf_engine.getitemattrdate (
1096 itemtype => l_item_type,
1097 itemkey => l_item_key,
1098 aname => 'AMS_APPROVAL_DATE')),'-');
1099 l_string1 := SUBSTR(FND_MESSAGE.Get,1,2500);
1100 fnd_message.set_name ('AMS', 'AMS_WF_NTF_APPROVER_ADDENDUM');
1101 fnd_message.set_token ('PREV_APPROVER_NAME', l_forwarder, FALSE);
1102 fnd_message.set_token ('APPROVAL_DATE', l_approval_date, FALSE);
1103 fnd_message.set_token ('COMMENTS', l_note, FALSE);
1104 /* will set the tokens later
1105 fnd_message.set_token('BUDGET_NAME',l_fund_name,false);
1106 fnd_message.set_token('BUDGET_NUMBER',l_fund_number,false);
1107 */
1108 -- l_string2 := Substr(FND_MESSAGE.Get,1,2500);
1109 l_string2 := SUBSTR(fnd_message.get,1,2500);
1110 /*
1111 l_note := wf_engine.getitemattrtext(
1112 itemtype => l_item_type
1113 ,itemkey => l_item_key
1114 ,aname => 'NOTE');
1115 l_forwarder :=
1116 wf_engine.getitemattrtext(
1117 itemtype => l_item_type
1118 ,itemkey => l_item_key
1119 ,aname => 'AMS_FORWARD_FROM_USERNAME');
1120 */
1121 -- IF (display_type = 'text/plain') THEN
1122 l_apprem_notification := l_string
1123 || fnd_global.local_chr (10)
1124 || l_string1
1125 || fnd_global.local_chr (10)
1126 || l_string2;
1127 document := document
1128 || l_apprem_notification;
1129 document_type := 'text/plain';
1130 RETURN;
1131 -- END IF;
1132 /* IF (display_type = 'text/html') THEN
1133 l_appreq_notification :=
1134 l_string ||
1135 FND_GLOBAL.LOCAL_CHR(10) ||
1136 l_string1 ||
1137 FND_GLOBAL.LOCAL_CHR(10) ||
1138 l_string2;
1139 document := document||l_appreq_notification;
1140 document_type := 'text/html';
1141 RETURN;
1142 END IF;
1143 */
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 wf_core.context (
1147 'AMSGAPP',
1148 'notify_appr_req_reminder',
1149 l_item_type,
1150 l_item_key
1151 );
1152 RAISE;
1153 END notify_appr_req_reminder;
1154 ---------------------------------------------------------------------
1155 -- PROCEDURE
1156 -- Set_ParBudget_Activity_details
1157 --
1158 --
1159 -- PURPOSE
1160 -- This Procedure will set all the item attribute details
1161 --
1162 --
1163 -- IN
1164 --
1165 --
1166 -- OUT
1167 --
1168 -- Used By Activities
1169 --
1170 -- NOTES
1171 --
1172 --
1173 --
1174 -- HISTORY
1175 -- 02/20/2001 MUMU PANDE CREATION
1176 -- End of Comments
1177 --------------------------------------------------------------------
1178 PROCEDURE set_parbudget_activity_details (
1179 itemtype IN VARCHAR2,
1180 itemkey IN VARCHAR2,
1181 actid IN NUMBER,
1182 funcmode IN VARCHAR2,
1183 resultout OUT NOCOPY VARCHAR2
1184 ) IS
1185 l_activity_id NUMBER;
1186 -- mpande changed for activity type 8/14/2001
1187 l_activity_type VARCHAR2 (30) := 'RFRQ';
1188 l_approval_type VARCHAR2 (30) := 'BUDGET';
1189 /*
1190 l_activity_type VARCHAR2 (30) := 'FUND';
1191 l_approval_type VARCHAR2 (30) := 'ROOT_BUDGET';
1192 */
1193 l_object_details ams_gen_approval_pvt.objrectyp;
1194 l_approval_detail_id NUMBER;
1195 l_approver_seq NUMBER;
1196 l_return_status VARCHAR2 (1);
1197 l_msg_count NUMBER;
1198 l_msg_data VARCHAR2 (4000);
1199 l_error_msg VARCHAR2 (4000);
1200 l_orig_stat_id NUMBER;
1201 l_full_name VARCHAR2 (60);
1202 l_fund_number VARCHAR2 (30);
1203 l_requested_amt NUMBER;
1204 l_approver VARCHAR2 (200);
1205 l_string VARCHAR2 (3000);
1206 l_lookup_meaning VARCHAR2(240);
1207 l_fund_type VARCHAR2(30);
1208 l_fund_meaning VARCHAR2(240);
1209 -- mpande 08/14/2001 changed for category id
1210 CURSOR c_fund_rec (p_act_id IN NUMBER) IS
1211 SELECT short_name, custom_setup_id, original_budget, org_id, to_char(category_id),
1212 start_date_active, end_date_active, owner, currency_code_tc, business_unit_id,
1213 fund_number, fund_type
1214 FROM ozf_funds_all_vl
1215 WHERE fund_id = p_act_id;
1216 BEGIN
1217 fnd_msg_pub.initialize;
1218 l_activity_id :=
1219 wf_engine.getitemattrnumber (
1220 itemtype=> itemtype,
1221 itemkey=> itemkey,
1222 aname => 'AMS_ACTIVITY_ID'
1223 );
1224 OPEN c_fund_rec (l_activity_id);
1225 FETCH c_fund_rec INTO l_object_details.name,
1226 l_object_details.setup_type_id,
1227 l_object_details.total_header_amount,
1228 l_object_details.org_id,
1229 l_object_details.object_type,
1230 l_object_details.start_date,
1231 l_object_details.end_date,
1232 l_object_details.owner_id,
1233 l_object_details.currency,
1234 l_object_details.business_unit_id,
1235 l_fund_number,
1236 l_fund_type;
1237 CLOSE c_fund_rec;
1238
1239 IF l_fund_type = 'QUOTA' THEN
1240 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
1241 p_lookup_code => 'QUOTA',
1242 x_return_status => l_return_status,
1243 x_meaning => l_fund_meaning);
1244 ELSIF l_fund_type IN ('FIXED', 'FULLY_ACCRUED') THEN
1245 ozf_utility_pvt.get_lookup_meaning (p_lookup_type => 'OZF_FUND_NTF_TYPE',
1246 p_lookup_code => 'BUDGET',
1247 x_return_status => l_return_status,
1248 x_meaning => l_fund_meaning);
1249 END IF;
1250
1251 IF (funcmode = 'RUN') THEN
1252 ams_gen_approval_pvt.get_approval_details (
1253 p_activity_id=> l_activity_id,
1254 p_activity_type=> l_activity_type,
1255 p_approval_type=> l_approval_type,
1256 p_object_details=> l_object_details,
1257 x_approval_detail_id=> l_approval_detail_id,
1258 x_approver_seq=> l_approver_seq,
1259 x_return_status=> l_return_status
1260 );
1261 IF l_return_status = fnd_api.g_ret_sts_success THEN
1262 /* AMS_GEN_APPROVAL_PVT.Get_User_Name
1263 ( p_user_id => l_object_details.owner_id,
1264 x_full_name => l_full_name,
1265 x_return_status => l_return_status );
1266 */
1267 wf_engine.setitemattrnumber (
1268 itemtype=> itemtype,
1269 itemkey=> itemkey,
1270 aname => 'AMS_APPROVAL_DETAIL_ID',
1271 avalue=> l_approval_detail_id
1272 );
1273 wf_engine.setitemattrnumber (
1274 itemtype=> itemtype,
1275 itemkey=> itemkey,
1276 aname => 'AMS_APPROVER_SEQ',
1277 avalue=> l_approver_seq
1278 );
1279 wf_engine.setitemattrnumber (
1280 itemtype=> itemtype,
1281 itemkey=> itemkey,
1282 aname => 'AMS_REQUESTED_AMOUNT',
1283 avalue=> l_object_details.total_header_amount
1284 );
1285 --- set all the subjects here
1286 fnd_message.set_name ('AMS', 'AMS_WF_NTF_REQUESTER_FYI_SUB');
1287 fnd_message.set_token (
1288 'BUDGET_NAME',
1289 l_object_details.name,
1290 FALSE
1291 );
1292 fnd_message.set_token (
1293 'CURRENCY_CODE',
1294 l_object_details.currency,
1295 FALSE
1296 );
1297 fnd_message.set_token (
1298 'AMOUNT',
1299 l_object_details.total_header_amount,
1300 FALSE
1301 );
1302 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
1303 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE); -- ##
1304 l_string := fnd_message.get;
1305 wf_engine.setitemattrtext (
1306 itemtype=> itemtype,
1307 itemkey=> itemkey,
1308 aname => 'FYI_SUBJECT',
1309 avalue=> l_string
1310 );
1311 fnd_message.set_name ('AMS', 'AMS_WF_NTF_REQUESTER_APP_SUB');
1312 fnd_message.set_token ('BUDGET_NAME', l_object_details.name, FALSE );
1313 -- 11/06/2001 mpande commented
1314 --fnd_message.set_token ('CURRENCY_CODE', l_object_details.currency, FALSE );
1315 --fnd_message.set_token ('AMOUNT',l_object_details.total_header_amount, FALSE);
1316 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
1317 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
1318 l_string := fnd_message.get;
1319 wf_engine.setitemattrtext (
1320 itemtype=> itemtype,
1321 itemkey=> itemkey,
1322 aname => 'APRV_SUBJECT',
1323 avalue=> l_string
1324 );
1325 fnd_message.set_name ('AMS', 'AMS_WF_NTF_REQUESTER_REJ_SUB');
1326 fnd_message.set_token (
1327 'BUDGET_NAME',
1328 l_object_details.name,
1329 FALSE
1330 );
1331 fnd_message.set_token (
1332 'CURRENCY_CODE',
1333 l_object_details.currency,
1334 FALSE
1335 );
1336 fnd_message.set_token (
1337 'AMOUNT',
1338 l_object_details.total_header_amount,
1339 FALSE
1340 );
1341 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
1342 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
1343 -- yzhao: not a token in message fnd_message.set_token ('APPROVER_NAME', l_approver, FALSE); -- ##
1344 l_string := fnd_message.get;
1345 wf_engine.setitemattrtext (
1346 itemtype=> itemtype,
1347 itemkey=> itemkey,
1348 aname => 'REJECT_SUBJECT',
1349 avalue=> l_string
1350 );
1351 fnd_message.set_name ('AMS', 'AMS_WF_NTF_APPROVER_OF_REQ_SUB');
1352 fnd_message.set_token (
1353 'BUDGET_NAME',
1354 l_object_details.name,
1355 FALSE
1356 );
1357 fnd_message.set_token (
1358 'CURRENCY_CODE',
1359 l_object_details.currency,
1360 FALSE
1361 );
1362 fnd_message.set_token (
1363 'AMOUNT',
1364 l_object_details.total_header_amount,
1365 FALSE
1366 );
1367 fnd_message.set_token ('REQUEST_NUMBER', '-', FALSE);
1368 fnd_message.set_token ('FUND_TYPE', l_fund_meaning, FALSE);
1369 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1370 l_string := fnd_message.get;
1371 wf_engine.setitemattrtext (
1372 itemtype=> itemtype,
1373 itemkey=> itemkey,
1374 aname => 'APP_SUBJECT',
1375 avalue=> l_string
1376 );
1377 /* mpande added for implementation of BUG#2352621*/
1378 l_lookup_meaning := ozf_utility_pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER','RFRQ');
1379 wf_engine.setitemattrtext (
1380 itemtype=> itemtype,
1381 itemkey=> itemkey,
1382 aname => 'AMS_APPROVAL_OBJECT_MEANING',
1383 avalue=> l_lookup_meaning
1384 );
1385 wf_engine.setitemattrtext (
1386 itemtype=> itemtype,
1387 itemkey=> itemkey,
1388 aname => 'AMS_APPROVAL_OBJECT_NAME',
1389 avalue=> l_object_details.name
1390 );
1391 /* End of Addition for Bug#2352621*/
1392 resultout := 'COMPLETE:SUCCESS';
1393 ELSE
1394 fnd_msg_pub.count_and_get (
1395 p_encoded=> fnd_api.g_false,
1396 p_count=> l_msg_count,
1397 p_data=> l_msg_data
1398 );
1399 ams_gen_approval_pvt.handle_err (
1400 p_itemtype=> itemtype,
1401 p_itemkey=> itemkey,
1402 p_msg_count=> l_msg_count, -- Number of error Messages
1403 p_msg_data=> l_msg_data,
1404 p_attr_name=> 'AMS_ERROR_MSG',
1405 x_error_msg=> l_error_msg
1406 );
1407 wf_core.context (
1408 'ams_gen_approval_pvt',
1409 'Set_Activity_Details',
1410 itemtype,
1411 itemkey,
1412 actid,
1413 l_error_msg
1414 );
1415 -- RAISE FND_API.G_EXC_ERROR;
1416 resultout := 'COMPLETE:ERROR';
1417 END IF;
1418 END IF;
1419 --
1420 -- CANCEL mode
1421 --
1422 IF (funcmode = 'CANCEL') THEN
1423 resultout := 'COMPLETE:';
1424 RETURN;
1425 END IF;
1426 --
1427 -- TIMEOUT mode
1428 --
1429 IF (funcmode = 'TIMEOUT') THEN
1430 resultout := 'COMPLETE:';
1431 RETURN;
1432 END IF;
1433 --
1434 EXCEPTION
1435 WHEN fnd_api.g_exc_error THEN
1436 wf_core.context (
1437 'AMS_FundApproval_pvt',
1438 'Set_ParBudget_Activity_Details',
1439 itemtype,
1440 itemkey,
1441 actid,
1442 funcmode,
1443 l_error_msg
1444 );
1445 RAISE;
1446 WHEN OTHERS THEN
1447 fnd_msg_pub.count_and_get (
1448 p_encoded=> fnd_api.g_false,
1449 p_count=> l_msg_count,
1450 p_data=> l_msg_data
1451 );
1452 RAISE;
1453 END set_parbudget_activity_details;
1454 ---------------------------------------------------------------------
1455 -- PROCEDURE
1456 -- Get_Ntf_Rule_Values
1457 --
1458 -- PURPOSE
1459 -- This Procedure will check the value apporved_amount in the
1460 -- of the notification rule of the approver
1461 -- IN
1462 -- p_approver_name IN VARCHAR2,
1463 -- p_result IN VARCHAR2 --
1464 -- OUT
1465 -- x_text_value OUT VARCHAR2
1466 -- x_number_value OUT NUMBER
1467 --
1468 -- Used By Activities
1469 --
1470 -- NOTES
1471 --
1472 -- HISTORY
1473 -- 10/2/2002 MUMU PANDE CREATION
1474 -- End of Comments
1475 -------------------------------------------------------------------
1476 PROCEDURE Get_Ntf_Rule_Values
1477 (p_approver_name IN VARCHAR2,
1478 x_text_value OUT NOCOPY VARCHAR2,
1479 x_number_value OUT NOCOPY NUMBER)
1480 IS
1481 CURSOR c_get_rule IS
1482 SELECT b.text_value, b.number_value
1483 FROM wf_routing_rules a, wf_routing_rule_attributes b
1484 WHERE a.rule_id = b.rule_id
1485 AND a.role = p_approver_name
1486 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(begin_date, sysdate -1)) AND
1487 TRUNC(NVL(end_date,sysdate+1))
1488 AND a.message_name = 'AMS_APPROVAL_REQUIRED_OZF'
1489 AND b.name = 'AMS_AMOUNT';
1490 BEGIN
1491 x_text_value := null;
1492 x_number_value := null;
1493 OPEN c_get_rule;
1494 FETCH c_get_rule INTO x_text_value, x_number_value;
1495 IF c_get_rule%NOTFOUND THEN
1496 x_text_value := NULL;
1497 x_number_value := 0;
1498 END IF;
1499 CLOSE c_get_rule;
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 IF G_DEBUG THEN
1503 ozf_utility_pvt.debug_message ('ozf_fund_approval_pvt.get_ntf_rule_values() exception.' || SQLERRM);
1504 END IF;
1505 RAISE;
1506 END Get_Ntf_Rule_Values;
1507 ---------------------------------------------------------------------
1508 -- PROCEDURE
1509 -- Update_ParBudget_Statas
1510 --
1511 --
1512 -- PURPOSE
1513 -- This Procedure will update the status
1514 --
1515 --
1516 -- IN
1517 --
1518 --
1519 -- OUT
1520 --
1521 -- Used By Activities
1522 --
1523 -- NOTES
1524 --
1525 --
1526 --
1527 -- HISTORY
1528 -- 02/20/2001 MUMU PANDE CREATION
1529 -- 05/26/2003 niprakas fixed the bug#2950338
1530 -- End of Comments
1531 -------------------------------------------------------------------
1532 PROCEDURE update_parbudget_status (
1533 itemtype IN VARCHAR2,
1534 itemkey IN VARCHAR2,
1535 actid IN NUMBER,
1536 funcmode IN VARCHAR2,
1537 resultout OUT NOCOPY VARCHAR2
1538 ) IS
1539 l_status_code VARCHAR2 (30);
1540 l_api_version CONSTANT NUMBER := 1.0;
1541 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1542 l_msg_count NUMBER;
1543 l_msg_data VARCHAR2 (4000);
1544 l_api_name CONSTANT VARCHAR2 (30) := 'Update_ParBudget_Status';
1545 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
1546 || '.'
1547 || l_api_name;
1548 l_fund_rec ozf_funds_pvt.fund_rec_type;
1549 l_next_status_id NUMBER;
1550 l_approved_amount NUMBER;
1551 l_update_status VARCHAR2 (12);
1552 l_error_msg VARCHAR2 (4000);
1553 l_object_version_number NUMBER;
1554 l_fund_id NUMBER;
1555 l_validation_level NUMBER := fnd_api.g_valid_level_full;
1556 l_approver VARCHAR2(320);
1557 l_text_value VARCHAR2(2000);
1558 l_number_value NUMBER;
1559 l_requested_amt NUMBER;
1560 l_requester_id NUMBER;
1561 l_approver_id NUMBER;
1562 l_user_id NUMBER;
1563 l_resp_id NUMBER;
1564 l_appl_id NUMBER;
1565 l_security_group_id NUMBER;
1566 l_fund_type VARCHAR2(30);
1567 l_org_id NUMBER;
1568 CURSOR c_get_fund_type IS
1569 SELECT fund_type from ozf_funds_all_b where fund_id = l_fund_id;
1570 BEGIN
1571 SAVEPOINT update_parbudget_status ;
1572 IF funcmode = 'RUN' THEN
1573 l_update_status :=
1574 wf_engine.getitemattrtext (
1575 itemtype=> itemtype,
1576 itemkey=> itemkey,
1577 aname => 'UPDATE_GEN_STATUS'
1578 );
1579 l_approved_amount :=
1580 wf_engine.getitemattrnumber (
1581 itemtype=> itemtype,
1582 itemkey=> itemkey,
1583 aname => 'AMS_AMOUNT'
1584 );
1585 l_approver :=
1586 wf_engine.getitemattrtext (
1587 itemtype=> itemtype,
1588 itemkey=> itemkey,
1589 aname => 'AMS_APPROVER'
1590 );
1591 l_approver_id :=
1592 wf_engine.getitemattrtext (
1593 itemtype=> itemtype,
1594 itemkey=> itemkey,
1595 aname => 'AMS_APPROVER_ID'
1596 );
1597 l_requester_id :=
1598 wf_engine.getitemattrtext (
1599 itemtype=> itemtype,
1600 itemkey=> itemkey,
1601 aname => 'AMS_REQUESTER_ID'
1602 );
1603 l_requested_amt :=
1604 wf_engine.getitemattrnumber (
1605 itemtype=> itemtype,
1606 itemkey=> itemkey,
1607 aname => 'AMS_REQUESTED_AMOUNT'
1608 );
1609 l_object_version_number :=
1610 wf_engine.getitemattrnumber (
1611 itemtype=> itemtype,
1612 itemkey=> itemkey,
1613 aname => 'AMS_OBJECT_VERSION_NUMBER'
1614 );
1615 l_fund_id :=
1616 wf_engine.getitemattrnumber (
1617 itemtype=> itemtype,
1618 itemkey=> itemkey,
1619 aname => 'AMS_ACTIVITY_ID'
1620 );
1621 -- [BEGIN OF BUG 2916480 FIXING by yzhao 23-APR-2003]
1622 -- yzhao: initialized PL/SQL security context(especially application_id)
1623 -- since self service workflow didn't set application_id when approver approves.
1624
1625 --Bug 14725235 , comment out the FND_GLOBAL... code
1626 -- get details from the wf item attributes,
1627 -- These attributes were set by ams_gen_approval_pvt.StartProcess
1628
1629 /*l_user_id := FND_GLOBAL.user_id;
1630 l_resp_id := FND_GLOBAL.resp_id;
1631 l_appl_id := FND_GLOBAL.resp_appl_id;
1632 l_security_group_id := FND_GLOBAL.security_group_id;*/
1633
1634 l_user_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1635 , itemkey => itemkey
1636 , aname => 'USER_ID'
1637 );
1638
1639 l_resp_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1640 , itemkey => itemkey
1641 , aname => 'RESPONSIBILITY_ID'
1642 );
1643
1644 l_appl_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1645 , itemkey => itemkey
1646 , aname => 'APPLICATION_ID'
1647 );
1648
1649 l_security_group_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
1650 , itemkey => itemkey
1651 , aname => 'SECURITY_GROUP_ID'
1652 );
1653
1654 OZF_DEBUG_PVT.DEBUG_MO('OZF_Fund_Approval_PVT update_parbudget_status before setting context : fund_id '||l_fund_id);
1655
1656 IF l_update_status IN ('APPROVED', 'REJECTED') AND
1657 (l_user_id IS NULL OR l_resp_id IS NULL OR l_appl_id IS NULL) THEN
1658 l_update_status := NULL;
1659 WF_ENGINE.setitemattrnumber(
1660 itemtype => itemtype
1661 ,itemkey => itemkey
1662 ,aname => 'UPDATE_GEN_STATUS'
1663 ,avalue => l_update_status
1664 );
1665 FND_MSG_PUB.count_and_get (
1666 p_encoded => fnd_api.g_false
1667 ,p_count => l_msg_count
1668 ,p_data => l_msg_data
1669 );
1670 AMS_GEN_APPROVAL_PVT.handle_err(
1671 p_itemtype => itemtype
1672 ,p_itemkey => itemkey
1673 ,p_msg_count => l_msg_count
1674 ,p_msg_data => l_msg_data
1675 ,p_attr_name => 'AMS_ERROR_MSG'
1676 ,x_error_msg => l_error_msg
1677 );
1678 WF_CORE.context(
1679 'OZF_FUND_APPROVAL_PVT'
1680 ,'update_parbudget_status'
1681 ,itemtype
1682 ,itemkey
1683 ,actid
1684 ,l_error_msg
1685 );
1686 resultout := 'COMPLETE:ERROR';
1687 RETURN;
1688 ELSIF l_update_status IN ('APPROVED', 'REJECTED') THEN
1689 --Bug 14725235
1690 FND_GLOBAL.apps_initialize( user_id => l_user_id
1691 , resp_id => l_resp_id
1692 , resp_appl_id => l_appl_id
1693 --, security_group_id => l_security_group_id
1694 );
1695 END IF;
1696
1697
1698
1699 ozf_funds_pvt.init_fund_rec (x_fund_rec => l_fund_rec);
1700
1701 --Bug 14725235
1702 OZF_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT,'OZF','OZF_Fund_Approval_PVT update_parbudget_status l_fund_id : '||l_fund_id);
1703 l_org_id := find_org_id (l_fund_id);
1704 OZF_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT,'OZF','OZF_Fund_Approval_PVT update_parbudget_status l_org_id : '||l_org_id);
1705 IF l_org_id IS NOT NULL THEN
1706 set_org_ctx (l_org_id);
1707 END IF;
1708 OZF_DEBUG_PVT.DEBUG_MO('OZF_Fund_Approval_PVT update_parbudget_status after setting context : fund_id '||l_fund_id);
1709
1710 IF l_update_status = 'APPROVED' THEN
1711 l_next_status_id :=
1712 wf_engine.getitemattrnumber (
1713 itemtype=> itemtype,
1714 itemkey=> itemkey,
1715 aname => 'AMS_NEW_STAT_ID'
1716 );
1717 /* yzhao 10/03/2002 bug#2577992 when automatic approval notification rule is set
1718 if auto approval amount > request amount, then final approval amount := request amount
1719 else final approval amount := auto approval amount
1720 */
1721 Get_Ntf_Rule_Values
1722 (p_approver_name => l_approver,
1723 x_text_value => l_text_value ,
1724 x_number_value => l_number_value);
1725 OPEN c_get_fund_type;
1726 FETCH c_get_fund_type into l_fund_type;
1727 CLOSE c_get_fund_type;
1728 IF NVL(l_number_value,0) > 0 THEN
1729 IF l_number_value > l_requested_amt THEN
1730 l_fund_rec.original_budget := l_requested_amt;
1731 ELSE
1732 l_fund_rec.original_budget := l_number_value;
1733 END IF;
1734 -- End of addition for bug#2577792
1735 -- niprakas: fix for bug#2950338 starts
1736 ELSIF l_fund_type = 'FIXED' THEN
1737 l_fund_rec.original_budget := l_approved_amount;
1738
1739 --kvattiku Fix for bug 3584105
1740 ELSIF l_fund_type = 'QUOTA' THEN
1741 l_fund_rec.original_budget := l_approved_amount;
1742
1743 END IF;
1744 -- niprakas: fix for bug#2950338 ends
1745 /* Approved Amount is null in the following cases.
1746 a) yzhao 08/13/2002 fix bug 2508539
1747 when requester and approver are the same, no approval is required and AMS_AMOUNT is not set
1748 should take AMS_REQUESTED_AMOUNT
1749 */
1750 IF l_approved_amount IS NULL THEN
1751 IF l_approver_id = l_requester_id THEN
1752 l_fund_rec.original_budget := l_requested_amt;
1753 END IF;
1754 END IF;
1755 -- niprakas: fix for bug#2950338 starts
1756 IF l_fund_type='FULLY_ACCRUED' THEN
1757 l_fund_rec.original_budget := 0;
1758 END IF;
1759 -- niprakas: fix for bug#2950338 ends
1760 -- set approval amount to workflow so notificaiton gets the correct amount
1761 wf_engine.setitemattrnumber (
1762 itemtype=> itemtype,
1763 itemkey=> itemkey,
1764 aname => 'AMS_AMOUNT',
1765 avalue=> l_fund_rec.original_budget
1766 );
1767 IF G_DEBUG THEN
1768 ozf_utility_pvt.debug_message ( l_full_name || l_update_status);
1769 END IF;
1770 -- mpande 6/11/2002 bug#2352621
1771 ELSIF l_update_status = 'REJECTED' THEN
1772 l_next_status_id :=
1773 wf_engine.getitemattrnumber (
1774 itemtype=> itemtype,
1775 itemkey=> itemkey,
1776 aname => 'AMS_REJECT_STAT_ID'
1777 );
1778 -- mpande 6/11/2002 bug#2352621
1779 -- Status is 'ERROR' during error in wf so the status of the fund should change back to 'DRAFT'
1780 ELSE
1781 --l_next_status_id :=
1782 -- ozf_utility_pvt.get_default_user_status ( 'OZF_FUND_STATUS' ,'DRAFT');
1783 -- 06/28/2002 yzhao: no valiation for update so status always revert to 'DRAFT' when error occurs
1784 l_next_status_id := wf_engine.GetItemAttrNumber(
1785 itemtype => itemtype,
1786 itemkey => itemkey,
1787 aname => 'AMS_ORIG_STAT_ID' );
1788 l_validation_level := fnd_api.g_valid_level_none;
1789 END IF;
1790 -- x_return_status := fnd_api.g_ret_sts_success;
1791 l_status_code :=
1792 ozf_utility_pvt.get_system_status_code (l_next_status_id);
1793 l_fund_rec.fund_id := l_fund_id;
1794 l_fund_rec.user_status_id := l_next_status_id;
1795 l_fund_rec.status_code := l_status_code;
1796 l_fund_rec.object_version_number := l_object_version_number
1797 + 1;
1798 IF G_DEBUG THEN
1799 ozf_utility_pvt.debug_message (
1800 l_full_name
1801 || l_status_code || ' '
1802 || l_approved_amount || ' '
1803 || l_update_status
1804 );
1805 END IF;
1806 ozf_funds_pvt.update_fund (
1807 p_api_version=> l_api_version,
1808 p_init_msg_list=> fnd_api.g_false,
1809 p_validation_level => l_validation_level,
1810 x_return_status=> l_return_status,
1811 x_msg_count=> l_msg_count,
1812 x_msg_data=> l_msg_data,
1813 p_fund_rec=> l_fund_rec,
1814 p_mode=> g_fund_mode
1815 );
1816 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1817 IF G_DEBUG THEN
1818 ozf_utility_pvt.debug_message(l_full_name || ' failed to update fund to status ' || l_status_code);
1819 END IF;
1820 ams_gen_approval_pvt.handle_err (
1821 p_itemtype=> itemtype,
1822 p_itemkey=> itemkey,
1823 p_msg_count=> l_msg_count, -- Number of error Messages
1824 p_msg_data=> l_msg_data,
1825 p_attr_name=> 'AMS_ERROR_MSG',
1826 x_error_msg=> l_error_msg
1827 );
1828 -- mpande 6/11/2002 bug#2352621
1829 resultout := 'COMPLETE:ERROR';
1830 ELSE
1831 resultout := 'COMPLETE:SUCCESS';
1832 END IF;
1833 END IF;
1834 -- CANCEL mode
1835 --
1836 IF (funcmode = 'CANCEL') THEN
1837 resultout := 'COMPLETE:';
1838 RETURN;
1839 END IF;
1840 --
1841 -- TIMEOUT mode
1842 --
1843 IF (funcmode = 'TIMEOUT') THEN
1844 resultout := 'COMPLETE:';
1845 RETURN;
1846 END IF;
1847 fnd_msg_pub.count_and_get (
1848 p_encoded=> fnd_api.g_false,
1849 p_count=> l_msg_count,
1850 p_data=> l_msg_data
1851 );
1852 IF G_DEBUG THEN
1853 ozf_utility_pvt.debug_message (
1854 l_full_name
1855 || ': l_return_status'
1856 || l_return_status
1857 );
1858 END IF;
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 -- x_return_status := fnd_api.g_ret_sts_error;
1862 ROLLBACK TO update_parbudget_status ;
1863 resultout := 'COMPLETE:ERROR';
1864 fnd_msg_pub.count_and_get (
1865 p_encoded=> fnd_api.g_false,
1866 p_count=> l_msg_count,
1867 p_data=> l_msg_data
1868 );
1869 ams_gen_approval_pvt.handle_err (
1870 p_itemtype=> itemtype,
1871 p_itemkey=> itemkey,
1872 p_msg_count=> l_msg_count, -- Number of error Messages
1873 p_msg_data=> l_msg_data,
1874 p_attr_name=> 'AMS_ERROR_MSG',
1875 x_error_msg=> l_error_msg
1876 );
1877 --RAISE;
1878 END update_parbudget_status;
1879 /* --
1880 6/11/2002 MPande COmmented after bug#2352621 was implemented
1881 PROCEDURE revert_parbudget_status (
1882 itemtype IN VARCHAR2,
1883 itemkey IN VARCHAR2,
1884 actid IN NUMBER,
1885 funcmode IN VARCHAR2,
1886 resultout OUT NOCOPY VARCHAR2
1887 ) IS
1888 l_status_code VARCHAR2 (30);
1889 l_api_version CONSTANT NUMBER := 1.0;
1890 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1891 l_msg_count NUMBER;
1892 l_msg_data VARCHAR2 (4000);
1893 l_api_name CONSTANT VARCHAR2 (30) := 'Update_ParBudget_Status';
1894 l_full_name CONSTANT VARCHAR2 (60) := g_pkg_name
1895 || '.'
1896 || l_api_name;
1897 l_fund_rec ozf_funds_pvt.fund_rec_type;
1898 l_next_status_id NUMBER;
1899 l_approved_amount NUMBER;
1900 l_update_status VARCHAR2 (12);
1901 l_error_msg VARCHAR2 (4000);
1902 l_object_version_number NUMBER;
1903 l_fund_id NUMBER;
1904 BEGIN
1905 IF funcmode = 'RUN' THEN
1906 l_object_version_number :=
1907 wf_engine.getitemattrnumber (
1908 itemtype=> itemtype,
1909 itemkey=> itemkey,
1910 aname => 'AMS_OBJECT_VERSION_NUMBER'
1911 );
1912 l_fund_id :=
1913 wf_engine.getitemattrnumber (
1914 itemtype=> itemtype,
1915 itemkey=> itemkey,
1916 aname => 'AMS_ACTIVITY_ID'
1917 );
1918 -- x_return_status := fnd_api.g_ret_sts_success;
1919 l_status_code :=
1920 ozf_utility_pvt.get_system_status_code (2100);
1921 ozf_funds_pvt.init_fund_rec (x_fund_rec => l_fund_rec);
1922 l_fund_rec.fund_id := l_fund_id;
1923 l_fund_rec.user_status_id := 2100;
1924 l_fund_rec.status_code := l_status_code;
1925 l_fund_rec.object_version_number := l_object_version_number
1926 + 1;
1927 IF G_DEBUG THEN
1928 ozf_utility_pvt.debug_message (
1929 l_full_name
1930 || l_status_code
1931 || l_approved_amount
1932 || l_update_status
1933 );
1934 END IF;
1935 ozf_funds_pvt.update_fund (
1936 p_api_version=> l_api_version,
1937 p_init_msg_list=> fnd_api.g_false,
1938 x_return_status=> l_return_status,
1939 x_msg_count=> l_msg_count,
1940 x_msg_data=> l_msg_data,
1941 p_fund_rec=> l_fund_rec,
1942 p_mode=> g_fund_mode
1943 );
1944 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1945 ams_gen_approval_pvt.handle_err (
1946 p_itemtype=> itemtype,
1947 p_itemkey=> itemkey,
1948 p_msg_count=> l_msg_count, -- Number of error Messages
1949 p_msg_data=> l_msg_data,
1950 p_attr_name=> 'AMS_ERROR_MSG',
1951 x_error_msg=> l_error_msg
1952 );
1953 END IF;
1954 END IF;
1955 -- CANCEL mode
1956 --
1957 IF (funcmode = 'CANCEL') THEN
1958 resultout := 'COMPLETE:';
1959 RETURN;
1960 END IF;
1961 --
1962 -- TIMEOUT mode
1963 --
1964 IF (funcmode = 'TIMEOUT') THEN
1965 resultout := 'COMPLETE:';
1966 RETURN;
1967 END IF;
1968 fnd_msg_pub.count_and_get (
1969 p_encoded=> fnd_api.g_false,
1970 p_count=> l_msg_count,
1971 p_data=> l_msg_data
1972 );
1973 IF G_DEBUG THEN
1974 ozf_utility_pvt.debug_message (
1975 l_full_name
1976 || ': l_return_status'
1977 || l_return_status
1978 );
1979 END IF;
1980 EXCEPTION
1981 WHEN OTHERS THEN
1982 -- x_return_status := fnd_api.g_ret_sts_error;
1983 fnd_msg_pub.count_and_get (
1984 p_encoded=> fnd_api.g_false,
1985 p_count=> l_msg_count,
1986 p_data=> l_msg_data
1987 );
1988 RAISE;
1989 END revert_parbudget_status;
1990 */
1991 ---------------------------------------------------------------------
1992 -- PROCEDURE
1993 -- get_fund_parent_owner
1994 --
1995 --
1996 -- PURPOSE
1997 -- This Procedure is a seeded function .
1998 --
1999 --
2000 -- IN
2001 --
2002 --
2003 -- OUT
2004 --
2005 -- Used By Activities
2006 --
2007 -- NOTES
2008 -- if there is notparen tfund it will default to the fund owner
2009 --
2010 --
2011 -- HISTORY
2012 -- 05/29/2001 MUMU PANDE CREATION
2013 -- End of Comments
2014 -------------------------------------------------------------------
2015 PROCEDURE get_fund_parent_owner (
2016 itemtype IN VARCHAR2,
2017 itemkey IN VARCHAR2,
2018 x_approver_id OUT NOCOPY NUMBER,
2019 x_return_status OUT NOCOPY VARCHAR2
2020 ) IS
2021 CURSOR c_parent_fund_owner (p_fund_id IN NUMBER) IS
2022 SELECT ozf2.owner, ozf1.owner
2023 FROM ozf_funds_all_b ozf2, ozf_funds_all_b ozf1
2024 WHERE ozf2.fund_id = ozf1.parent_fund_id
2025 AND ozf1.fund_id = p_fund_id;
2026 l_fund_id NUMBER;
2027 l_fund_owner_id NUMBER;
2028 BEGIN
2029 x_return_status := fnd_api.g_ret_sts_success;
2030 l_fund_id :=
2031 wf_engine.getitemattrnumber (
2032 itemtype=> itemtype,
2033 itemkey=> itemkey,
2034 aname => 'AMS_ACTIVITY_ID'
2035 );
2036 OPEN c_parent_fund_owner (l_fund_id);
2037 FETCH c_parent_fund_owner INTO x_approver_id, l_fund_owner_id;
2038 CLOSE c_parent_fund_owner;
2039 IF x_approver_id IS NULL THEN
2040 x_approver_id := l_fund_owner_id;
2041 END IF;
2042 EXCEPTION
2043 WHEN OTHERS THEN
2044 x_return_status := fnd_api.g_ret_sts_error;
2045 RAISE;
2046 END get_fund_parent_owner;
2047 END ozf_fund_approval_pvt;