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