[Home] [Help]
PACKAGE BODY: APPS.OZF_FUND_REQUEST_APR_PVT
Source
1 PACKAGE BODY ozf_fund_request_apr_pvt AS
2 /* $Header: ozfvwfrb.pls 120.1.12010000.3 2009/05/13 12:04:04 nepanda ship $ */
3
4 ----------------------------------------------------------
5 -- Start of Comments
6 --
7 -- NAME
8 -- OZF_fund_Request_Apr_PVT
9 --
10 -- PURPOSE
11 -- This package contains all transactions to be done for
12 -- Fund Request Approvals and Fund Transfer Approvals
13 -- in Oracle Marketing(Funds and Budgets)
14 --
15 -- HISTORY
16 -- 03/22/2001 MUMU PANDE CREATION
17 -- 07/09/2001 MUMU PANDE Set the subjects in set_trans_Activity_details procedure
18 -- 08/14/2001 MUMU PANDE Updation for a approval_type
19 -- 11/06/2001 MUMU PANDE Updation for updating transferd in amount for child fund
20 -- 02/26/2002 MUMU PANDE Fixed BUG#2241661
21 -- 06/18/2002 Mumu Pande Fixed Bug# 2092868
22 -- 07/01/2002 Ying Zhao Fix bug 2352621
23 -- 10/03/2002 Ying Zhao Fix bug 2577992
24 -- 01/27/2003 Ying Zhao Fix bug 2771105(same as 11.5.8 bug 2753608) APPROVAL NOTE NOT SHOWING IN APPROVAL/REJECTION EMAIL
25 -- 03/21/2003 Feliu Fix bug 2861097.
26 -- 01/22/2004 kdass Fix bug 3390310. Changed the workflow attributes back to AMS from OZF
27 -- 04/20/2004 Ribha Mehrotra Fix bug 3579649. Send the original amount as null to update_fund api.
28 -- 06/17/2004 Ribha Mehrotra Fix bug 3638512. Set the ams_amount when approver is the requestor.
29 -- 29/07/2008 kpatro Fix bug 7290977
30 -- 5/13/2009 nepanda Fix for bug 8434546
31
32 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_Fund_Request_Apr_PVT';
33 g_file_name CONSTANT VARCHAR2(15) := 'ozfvwfrb.pls';
34 g_cons_fund_mode CONSTANT VARCHAR2(30) := 'WORKFLOW';
35 -- changed by mpande 08/14/2001
36 g_transfer_approval_type CONSTANT VARCHAR2(30) := 'BUDGET';
37 --g_transfer_approval_type CONSTANT VARCHAR2(30) := 'BUDGET_REQUEST';
38 g_budget_source_status CONSTANT VARCHAR2(30) := 'OZF_BUDGETSOURCE_STATUS';
39 g_workflow_process CONSTANT VARCHAR2(30) := 'AMSGAPP';
40 g_item_type CONSTANT VARCHAR2(30) := 'AMSGAPP';
41 -- addded 08/14/2001 mpande
42 g_activity_type CONSTANT VARCHAR2(30) := 'FREQ';
43 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
44 ---------------------------------------------------------------------
45 -- PROCEDURE
46 -- Approve_Request
47 --
48 --
49 -- PURPOSE
50 -- This Procedure will Update the source and target funds and update the fund_request.
51 --
52 -- IN
53 --p_commit IN VARCHAR2 := fnd_api.g_false
54 --p_update_status IN VARCHAR2 := 'Y'
55 --p_fund_request_id IN NUMBER
56 --p_target_fund_id IN NUMBER
57 --p_source_fund_id IN NUMBER
58 --p_requester_id IN NUMBER
59 --p_approver_id IN NUMBER
60 --p_requested_amount IN NUMBER both in TRANSFER FROM fund currency
61 --
62 -- OUT
63 --
64 -- NOTES
65 -- HISTORY
66 -- 02/20/2001 MUMU PANDE CREATION
67 -- 10/22/2001 mpande Changed code different owner allocation bug
68 -- End of Comments
69 ------------------------------------------------------------------------------
70
71
72 PROCEDURE approve_request(
73 p_commit IN VARCHAR2 := fnd_api.g_false
74 ,p_update_status IN VARCHAR2 := 'Y'
75 ,p_act_budget_id IN NUMBER
76 ,p_target_fund_id IN NUMBER
77 ,p_source_fund_id IN NUMBER
78 ,p_requester_id IN NUMBER
79 ,p_approver_id IN NUMBER
80 -- ,p_requested_amount IN NUMBER -- both in TRANSFER FROM fund currency ,(SOURCE)
81 -- this is because the amount which goes to the workflow goes in TRANSFER FROM currency
82 -- and also returns in from fund currency
83 ,p_requestor_owner IN VARCHAR2 := 'N'
84 ,p_approved_amount IN NUMBER -- in TARNSFER FROM fund currency
85 ,p_child_flag IN VARCHAR2 := 'N'
86 ,p_note IN VARCHAR2
87 ,p_workflow_flag IN VARCHAr2 := 'N'-- flag to indicate that th ereor is being updated from workflow
88 ,x_return_status OUT NOCOPY VARCHAR2
89 ,x_msg_count OUT NOCOPY NUMBER
90 ,x_msg_data OUT NOCOPY VARCHAR2)
91 IS
92 -- Local variables
93 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Request';
94 l_full_name CONSTANT VARCHAR2(60)
95 := g_pkg_name || '.' || l_api_name;
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_return_status VARCHAR2(1);
98 l_msg_count NUMBER;
99 l_msg_data VARCHAR2(4000);
100 l_object_version_number NUMBER;
101 -- Record variables for creating the fund request.
102 l_source_fund_rec ozf_funds_pvt.fund_rec_type; -- source fund record
103 l_target_fund_rec ozf_funds_pvt.fund_rec_type; -- target fund record
104 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type; -- fund request record
105
106 -- Cursor to find source fund details
107 CURSOR c_fund_detail(
108 cv_fund_id IN NUMBER)
109 IS
110 SELECT original_budget source_org_budget
111 ,transfered_in_amt source_trans_in_amt
112 ,transfered_out_amt source_trans_out_amt
113 ,holdback_amt source_holdback_amt
114 ,currency_code_tc source_currency_code
115 ,object_version_number source_obj_num
116 ,committed_amt
117 FROM ozf_funds_all_vl
118 WHERE fund_id = cv_fund_id;
119
120 l_source_rec c_fund_detail%ROWTYPE;
121 l_act_budget_obj_num NUMBER;
122
123 -- Cursor records
124 CURSOR c_target_fund_detail(
125 cv_fund_id IN NUMBER)
126 IS
127 SELECT original_budget target_org_budget
128 ,transfered_in_amt target_trans_in_amt
129 ,status_code target_status_code
130 ,user_status_id target_user_status_id
131 ,currency_code_tc target_currency_code
132 ,object_version_number target_obj_num
133 FROM ozf_funds_all_vl
134 WHERE fund_id = cv_fund_id;
135
136 l_target_rec c_target_fund_detail%ROWTYPE;
137
138 -- Cursor to find fund_request details
139 CURSOR c_act_budget_detail(
140 p_act_budget_id IN NUMBER)
141 IS
142 SELECT object_version_number
143 FROM ozf_act_budgets
144 WHERE activity_budget_id = p_act_budget_id;
145
146 CURSOR c_to_fund_currency(
147 p_to_fund_id IN NUMBER)
148 IS
149 SELECT currency_code_tc
150 FROM ozf_funds_all_vl
151 WHERE fund_id = p_to_fund_id;
152
153 l_to_currency VARCHAR2(3);
154 l_rate NUMBER;
155 l_to_curr_approved_amt NUMBER := 0;
156 BEGIN
157 SAVEPOINT approve_request;
158 -- Initialize
159 IF G_DEBUG THEN
160 ozf_utility_pvt.debug_message(l_full_name || ': start');
161 END IF;
162 x_return_status := fnd_api.g_ret_sts_success;
163 OPEN c_fund_detail(p_source_fund_id);
164 FETCH c_fund_detail INTO l_source_rec.source_org_budget,
165 l_source_rec.source_trans_in_amt,
166 l_source_rec.source_trans_out_amt,
167 l_source_rec.source_holdback_amt,
168 l_source_rec.source_currency_code,
169 l_source_rec.source_obj_num,
170 l_source_rec.committed_amt;
171 IF (c_fund_detail%NOTFOUND) THEN
172 CLOSE c_fund_detail;
173
174 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
175 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
176 fnd_msg_pub.add;
177 END IF;
178
179 RAISE fnd_api.g_exc_error;
180 END IF;
181
182 CLOSE c_fund_detail;
183
184 IF (
185 NVL(
186 NVL(l_source_rec.source_org_budget, 0) -
187 NVL(l_source_rec.source_holdback_amt, 0) +
188 NVL(l_source_rec.source_trans_in_amt, 0) -
189 NVL(l_source_rec.source_trans_out_amt, 0)-
190 NVL(l_source_rec.committed_amt, 0)
191 ,0)) <
192 p_approved_amount THEN
193 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
194 fnd_message.set_name('OZF', 'OZF_AMT_UNAVAILABLE');
195 fnd_msg_pub.add;
196 END IF;
197
198 RAISE fnd_api.g_exc_error;
199 END IF;
200
201 ----- Convert the approved amount to the to_fund_currency
202 OPEN c_to_fund_currency(p_target_fund_id);
203 FETCH c_to_fund_currency INTO l_to_currency;
204
205 IF (c_to_fund_currency%NOTFOUND) THEN
206 CLOSE c_to_fund_currency;
207
208 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
209 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
210 fnd_msg_pub.add;
211 END IF;
212
213 RAISE fnd_api.g_exc_error;
214 END IF;
215
216 CLOSE c_to_fund_currency;
217
218 ---- if the two currncies are different then convert the approved amount into the Transfer to fund currency
219 IF l_to_currency <> l_source_rec.source_currency_code THEN
220 ozf_utility_pvt.convert_currency(
221 x_return_status => l_return_status
222 ,p_from_currency => l_source_rec.source_currency_code
223 ,p_to_currency => l_to_currency
224 ,p_from_amount => p_approved_amount
225 ,x_to_amount => l_to_curr_approved_amt
226 ,x_rate => l_rate);
227
228 IF l_return_status = fnd_api.g_ret_sts_error THEN
229 RAISE fnd_api.g_exc_error;
230 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
231 RAISE fnd_api.g_exc_unexpected_error;
232 END IF;
233 ELSE
234 l_to_curr_approved_amt := p_approved_amount;
235 END IF; --/// end if transfer currency
236
237 -- Initialize the fund records
238 ozf_funds_pvt.init_fund_rec(x_fund_rec => l_source_fund_rec);
239 -- Source record
240 l_source_fund_rec.fund_id := p_source_fund_id;
241 l_source_fund_rec.object_version_number := l_source_rec.source_obj_num;
242 l_source_fund_rec.transfered_out_amt :=
243 NVL(l_source_rec.source_trans_out_amt, 0) + p_approved_amount; -- TRANSFERED IN AMT
244 IF G_DEBUG THEN
245 ozf_utility_pvt.debug_message(
246 l_full_name || 'updating source_fund' || l_source_rec.source_obj_num);
247 END IF;
248 -- Update source fund
249 -- Source record
250 ozf_funds_pvt.update_fund(
251 p_api_version => l_api_version
252 ,p_init_msg_list => fnd_api.g_false
253 ,p_commit => fnd_api.g_false
254 ,x_return_status => l_return_status
255 ,x_msg_count => l_msg_count
256 ,x_msg_data => l_msg_data
257 ,p_fund_rec => l_source_fund_rec
258 ,p_mode => 'ADJUST');
259
260 IF l_return_status = fnd_api.g_ret_sts_error THEN
261 RAISE fnd_api.g_exc_error;
262 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
263 RAISE fnd_api.g_exc_unexpected_error;
264 END IF;
265
266 OPEN c_target_fund_detail(p_target_fund_id);
267 FETCH c_target_fund_detail INTO l_target_rec.target_org_budget,
268 l_target_rec.target_trans_in_amt,
269 l_target_rec.target_status_code,
270 l_target_rec.target_user_status_id,
271 l_target_rec.target_currency_code,
272 l_target_rec.target_obj_num;
273
274 IF (c_target_fund_detail%NOTFOUND) THEN
275 CLOSE c_target_fund_detail;
276
277 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
278 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
279 fnd_msg_pub.add;
280 END IF;
281
282 RAISE fnd_api.g_exc_error;
283 END IF;
284
285 CLOSE c_target_fund_detail;
286 ozf_funds_pvt.init_fund_rec(x_fund_rec => l_target_fund_rec);
287 -- Target record
288
289 l_target_fund_rec.fund_id := p_target_fund_id;
290 l_target_fund_rec.object_version_number := l_target_rec.target_obj_num;
291
292 IF p_child_flag = 'Y' THEN
293 /*
294 l_target_fund_rec.original_budget := NVL(l_to_curr_approved_amt, 0); ---changed 21st JULY to accomodate
295 l_target_fund_rec.transfered_in_amt := fnd_api.g_miss_num;
296 */
297 -- mpande 11/02/2001 changed
298 l_target_fund_rec.original_budget := 0;
299 l_target_fund_rec.transfered_in_amt := NVL(l_to_curr_approved_amt, 0);
300
301 l_target_fund_rec.status_code := 'ACTIVE';
302 l_target_fund_rec.user_status_id :=
303 ozf_utility_pvt.get_default_user_status(
304 'OZF_FUND_STATUS'
305 ,l_target_fund_rec.status_code);
306 ELSE
307 l_target_fund_rec.transfered_in_amt :=
308 NVL(l_target_rec.target_trans_in_amt, 0) + l_to_curr_approved_amt;
309 -- Transfered In AMT added in to fund currency
310 --l_target_fund_rec.original_budget := fnd_api.g_miss_num; --bug fix 3579649:rimehrot
311 END IF;
312
313 IF G_DEBUG THEN
314 ozf_utility_pvt.debug_message(
315 l_full_name ||
316 'updating target_fund' ||
317 l_target_fund_rec.status_code ||
318 l_target_fund_rec.user_status_id);
319 END IF;
320
321 IF p_child_flag = 'Y' THEN
322 IF p_requestor_owner = 'N' THEN
323 -- Update target fund to active other wise the caller API ( OZF_FUNDRULES_PVT) will update the fund to active
324 ozf_funds_pvt.update_fund(
325 p_api_version => l_api_version
326 ,p_init_msg_list => fnd_api.g_false
327 ,p_commit => fnd_api.g_false
328 ,x_return_status => l_return_status
329 ,x_msg_count => l_msg_count
330 ,x_msg_data => l_msg_data
331 ,p_fund_rec => l_target_fund_rec
332 ,p_mode => g_cons_fund_mode);
333 ELSIF p_requestor_owner = 'Y' THEN
334 -- mpande 10/19/2001 commented
335 /*
336 OPEN c_act_budget_detail(p_act_budget_id);
337 FETCH c_act_budget_detail INTO l_act_budget_obj_num;
338
339 IF (c_act_budget_detail%NOTFOUND) THEN
340 CLOSE c_act_budget_detail;
341
342 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
343 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
344 fnd_msg_pub.add;
345 END IF;
346
347 RAISE fnd_api.g_exc_error;
348 END IF;
349
350 CLOSE c_act_budget_detail;
351 --- update th erequest stauts to approved
352 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budget_rec);
353 l_act_budget_rec.activity_budget_id := p_act_budget_id;
354 l_act_budget_rec.object_version_number := l_act_budget_obj_num;
355 -- this will set the status to approved
356 l_act_budget_rec.status_code := 'APPROVED'; -- Approved amount
357 l_act_budget_rec.comment := p_note;
358 l_act_budget_rec.user_status_id :=
359 ozf_utility_pvt.get_default_user_status(
360 g_budget_source_status
361 ,l_act_budget_rec.status_code);
362 -- Fund request record
363 ozf_actbudgets_pvt.update_act_budgets(
364 p_api_version => l_api_version
365 ,p_init_msg_list => fnd_api.g_false
366 ,p_commit => fnd_api.g_false
367 ,x_return_status => l_return_status
368 ,x_msg_count => l_msg_count
369 ,x_msg_data => l_msg_data
370 ,p_act_budgets_rec => l_act_budget_rec
371 ,p_child_approval_flag => FND_API.g_false
372 ,p_requestor_owner_flag => p_requestor_owner
373 );
374
375 IF l_return_status = fnd_api.g_ret_sts_error THEN
376 RAISE fnd_api.g_exc_error;
377 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
378 RAISE fnd_api.g_exc_unexpected_error;
379 END IF;
380 */
381 NULL;
382 END IF;
383 ELSIF p_child_flag = 'N' THEN
384 -- if not a child transfer always update the target fund
385 ozf_funds_pvt.update_fund(
386 p_api_version => l_api_version
387 ,p_init_msg_list => fnd_api.g_false
388 ,p_commit => fnd_api.g_false
389 ,x_return_status => l_return_status
390 ,x_msg_count => l_msg_count
391 ,x_msg_data => l_msg_data
392 ,p_fund_rec => l_target_fund_rec
393 ,p_mode => 'ADJUST');
394 END IF;
395
396 IF l_return_status = fnd_api.g_ret_sts_error THEN
397 RAISE fnd_api.g_exc_error;
398 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
399 RAISE fnd_api.g_exc_unexpected_error;
400 END IF;
401 -- if the fund transfer is being updated from workflow the request record should be approved
402 -- other wise the caller API will make it approved
403 -- mpande 09/06/2001 IF p_child_flag = 'N' AND p_workflow_flag = 'Y' THEN
404 IF p_workflow_flag = 'Y' THEN
405 -- Fund Request Detail
406 OPEN c_act_budget_detail(p_act_budget_id);
407 FETCH c_act_budget_detail INTO l_act_budget_obj_num;
408
409 IF (c_act_budget_detail%NOTFOUND) THEN
410 CLOSE c_act_budget_detail;
411
412 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
413 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
414 fnd_msg_pub.add;
415 END IF;
416
417 RAISE fnd_api.g_exc_error;
418 END IF;
419
420 CLOSE c_act_budget_detail;
421 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budget_rec);
422 l_act_budget_rec.activity_budget_id := p_act_budget_id;
423 l_act_budget_rec.object_version_number := l_act_budget_obj_num;
424 l_act_budget_rec.approver_id := p_approver_id;
425 l_act_budget_rec.approved_in_currency := l_source_rec.source_currency_code;
426 l_act_budget_rec.approved_original_amount := p_approved_amount;
427 l_act_budget_rec.status_code := 'APPROVED'; -- Approved amount
428 l_act_budget_rec.comment := p_note;
429 l_act_budget_rec.user_status_id :=
430 ozf_utility_pvt.get_default_user_status(
431 g_budget_source_status
432 ,l_act_budget_rec.status_code);
433 -- Fund request record
434 ozf_actbudgets_pvt.update_act_budgets(
435 p_api_version => l_api_version
436 ,p_init_msg_list => fnd_api.g_false
437 ,p_commit => fnd_api.g_false
438 ,x_return_status => l_return_status
439 ,x_msg_count => l_msg_count
440 ,x_msg_data => l_msg_data
441 ,p_act_budgets_rec => l_act_budget_rec);
442
443 -- Set the return status
444 IF l_return_status = fnd_api.g_ret_sts_error THEN
445 RAISE fnd_api.g_exc_error;
446 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
447 RAISE fnd_api.g_exc_unexpected_error;
448 END IF;
449 END IF ;
450 fnd_msg_pub.count_and_get(
451 p_encoded => fnd_api.g_false
452 ,p_count => x_msg_count
453 ,p_data => x_msg_data);
454
455 -- Conditional commit;
456 IF fnd_api.to_boolean(p_commit)
457 AND x_return_status = fnd_api.g_ret_sts_success THEN
458 COMMIT WORK;
459 END IF;
460 EXCEPTION
461 WHEN fnd_api.g_exc_error THEN
462 ROLLBACK TO approve_request;
463 x_return_status := fnd_api.g_ret_sts_error;
464 fnd_msg_pub.count_and_get(
465 p_encoded => fnd_api.g_false
466 ,p_count => x_msg_count
467 ,p_data => x_msg_data);
468 WHEN fnd_api.g_exc_unexpected_error THEN
469 ROLLBACK TO approve_request;
470 x_return_status := fnd_api.g_ret_sts_unexp_error;
471 fnd_msg_pub.count_and_get(
472 p_encoded => fnd_api.g_false
473 ,p_count => x_msg_count
474 ,p_data => x_msg_data);
475 WHEN OTHERS THEN
476 ROLLBACK TO approve_request;
477 x_return_status := fnd_api.g_ret_sts_unexp_error;
478
479 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
480 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
481 END IF;
482
483 fnd_msg_pub.count_and_get(
484 p_encoded => fnd_api.g_false
485 ,p_count => x_msg_count
486 ,p_data => x_msg_data);
487 END approve_request;
488
489 ---------------------------------------------------------------------
490 -- PROCEDURE
491 -- negative_request
492 --
493 --
494 -- PURPOSE
495 -- This Procedure will Update the source and target funds and update the fund_request.
496 -- called only when the request is rejected or error occured in approval process
497 -- for successful approval, approve_request() is called.
498 --
499 -- IN
500 --p_commit IN VARCHAR2 := fnd_api.g_false
501 --p_update_status IN VARCHAR2 := 'Y'
502 --p_fund_request_id IN NUMBER
503 --p_target_fund_id IN NUMBER
504 --p_source_fund_id IN NUMBER
505 --p_requester_id IN NUMBER
506 --p_approver_id IN NUMBER
507 --p_requested_amount IN NUMBER both in TRANSFER FROM fund currency
508 --p_status_code IN VARCAHR2(30)
509 --p_user_status_id IN NUMBER
510
511 --
512 -- OUT
513 --
514 -- NOTES
515 -- HISTORY
516 -- 02/20/2001 MUMU PANDE CREATION
517 -- End of Comments
518 ------------------------------------------------------------------------------
519
520
521 PROCEDURE negative_request(
522 p_commit IN VARCHAR2 := fnd_api.g_false
523 ,p_act_budget_id IN NUMBER
524 ,p_target_fund_id IN NUMBER
525 ,p_source_fund_id IN NUMBER
526 ,p_requester_id IN NUMBER
527 ,p_approver_id IN NUMBER
528 -- ,p_requested_amount IN NUMBER -- both in TRANSFER FROM fund currency ,(SOURCE)
529 -- this is because the amount which goes to the workflow goes in TRANSFER FROM currency
530 -- and also returns in from fund currency
531 ,p_requestor_owner IN VARCHAR2 := 'N'
532 ,p_approved_amount IN NUMBER -- in TARNSFER FROM fund currency
533 ,p_child_flag IN VARCHAR2 := 'N'
534 ,p_note IN VARCHAR2
535 ,p_status_code IN VARCHAR2
536 ,p_user_status_id IN NUMBER
537 ,x_return_status OUT NOCOPY VARCHAR2
538 ,x_msg_count OUT NOCOPY NUMBER
539 ,x_msg_data OUT NOCOPY VARCHAR2
540 )
541 IS
542 -- Local variables
543 l_api_name CONSTANT VARCHAR2(30) := 'Rejected_Request';
544 l_full_name CONSTANT VARCHAR2(60)
545 := g_pkg_name || '.' || l_api_name;
546 l_api_version CONSTANT NUMBER := 1.0;
547 l_return_status VARCHAR2(1);
548 l_msg_count NUMBER;
549 l_msg_data VARCHAR2(4000);
550 l_object_version_number NUMBER;
551 -- Record variables for creating the fund request.
552 l_target_fund_rec ozf_funds_pvt.fund_rec_type; -- target fund record
553 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type; -- fund request record
554 l_act_budget_obj_num NUMBER;
555
556 -- Cursor records
557 CURSOR c_target_fund_detail(
558 cv_fund_id IN NUMBER)
559 IS
560 SELECT original_budget target_org_budget
561 ,transfered_in_amt target_trans_in_amt
562 ,status_code target_status_code
563 ,user_status_id target_user_status_id
564 ,currency_code_tc target_currency_code
565 ,object_version_number target_obj_num
566 FROM ozf_funds_all_vl
567 WHERE fund_id = cv_fund_id;
568
569 l_target_rec c_target_fund_detail%ROWTYPE;
570
571 -- Cursor to find fund_request details
572 CURSOR c_act_budget_detail(
573 p_act_budget_id IN NUMBER)
574 IS
575 SELECT object_version_number
576 FROM ozf_act_budgets
577 WHERE activity_budget_id = p_act_budget_id;
578
579 CURSOR c_to_fund_currency(
580 p_to_fund_id IN NUMBER)
581 IS
582 SELECT currency_code_tc
583 FROM ozf_funds_all_vl
584 WHERE fund_id = p_to_fund_id;
585
586 l_to_currency VARCHAR2(3);
587 l_rate NUMBER;
588 l_to_curr_approved_amt NUMBER := 0;
589 BEGIN
590 SAVEPOINT negative_request;
591 -- Initialize
592 IF G_DEBUG THEN
593 ozf_utility_pvt.debug_message(l_full_name || ': start');
594 END IF;
595 OPEN c_target_fund_detail(p_target_fund_id);
596 FETCH c_target_fund_detail INTO l_target_rec.target_org_budget,
597 l_target_rec.target_trans_in_amt,
598 l_target_rec.target_status_code,
599 l_target_rec.target_user_status_id,
600 l_target_rec.target_currency_code,
601 l_target_rec.target_obj_num;
602
603 IF (c_target_fund_detail%NOTFOUND) THEN
604 CLOSE c_target_fund_detail;
605
606 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
607 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
608 fnd_msg_pub.add;
609 END IF;
610
611 RAISE fnd_api.g_exc_error;
612 END IF;
613
614 CLOSE c_target_fund_detail;
615 ozf_funds_pvt.init_fund_rec(x_fund_rec => l_target_fund_rec);
616 -- Target record
617
618 l_target_fund_rec.fund_id := p_target_fund_id;
619 l_target_fund_rec.object_version_number := l_target_rec.target_obj_num;
620
621 /*
622 IF p_child_flag = 'Y' THEN
623 IF p_status_code = 'REJECTED' THEN
624 l_target_fund_rec.status_code := 'REJECTED';
625 ELSE
626 l_target_fund_rec.status_code := 'DRAFT';
627 END IF;
628 l_target_fund_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
629 'OZF_FUND_STATUS'
630 ,l_target_fund_rec.status_code);
631 ELSE
632 l_target_fund_rec.transfered_in_amt :=
633 NVL(l_target_rec.target_trans_in_amt, 0) + l_to_curr_approved_amt;
634 -- Transfered In AMT added in to fund currency
635 l_target_fund_rec.original_budget := fnd_api.g_miss_num;
636 END IF;
637 */
638 IF G_DEBUG THEN
639 ozf_utility_pvt.debug_message(
640 l_full_name ||
641 'updating target_fund' ||
642 l_target_fund_rec.status_code ||
643 l_target_fund_rec.user_status_id);
644 END IF;
645
646 IF p_child_flag = 'Y' THEN
647 IF p_requestor_owner = 'N' THEN
648 -- yzhao: 06/28/2002
649 IF p_status_code = 'REJECTED' THEN
650 l_target_fund_rec.status_code := 'REJECTED';
651 ELSE
652 l_target_fund_rec.status_code := 'DRAFT';
653 END IF;
654 l_target_fund_rec.user_status_id := ozf_utility_pvt.get_default_user_status(
655 'OZF_FUND_STATUS'
656 , l_target_fund_rec.status_code);
657 -- yzhao: 06/28/2002 end
658
659 -- Update target fund
660 ozf_funds_pvt.update_fund(
661 p_api_version => l_api_version
662 ,p_init_msg_list => fnd_api.g_false
663 ,p_commit => fnd_api.g_false
664 ,x_return_status => l_return_status
665 ,x_msg_count => l_msg_count
666 ,x_msg_data => l_msg_data
667 ,p_fund_rec => l_target_fund_rec
668 ,p_mode => g_cons_fund_mode);
669 /* -- should never enter in this because reject is never called
670 ELSIF p_requestor_owner = 'Y' THEN
671 OPEN c_act_budget_detail(p_act_budget_id);
672 FETCH c_act_budget_detail INTO l_act_budget_obj_num;
673
674 IF (c_act_budget_detail%NOTFOUND) THEN
675 CLOSE c_act_budget_detail;
676
677 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
678 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
679 fnd_msg_pub.add;
680 END IF;
681
682 RAISE fnd_api.g_exc_error;
683 END IF;
684
685 CLOSE c_act_budget_detail;
686 --- update th erequest stauts to pending before submitting
687 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budget_rec);
688 l_act_budget_rec.activity_budget_id := p_act_budget_id;
689 l_act_budget_rec.object_version_number := l_act_budget_obj_num;
690 -- this will set the status to pending
691 l_act_budget_rec.status_code := 'APPROVED'; -- Approved amount
692 l_act_budget_rec.user_status_id :=
693 ozf_utility_pvt.get_default_user_status(
694 g_budget_source_status
695 ,l_act_budget_rec.status_code);
696 -- Fund request record
697 ozf_actbudgets_pvt.update_act_budgets(
698 p_api_version => l_api_version
699 ,p_init_msg_list => fnd_api.g_false
700 ,p_commit => fnd_api.g_false
701 ,x_return_status => l_return_status
702 ,x_msg_count => l_msg_count
703 ,x_msg_data => l_msg_data
704 ,p_act_budgets_rec => l_act_budget_rec
705 ,p_child_approval_flag => FND_API.g_true
706 );
707
708 IF l_return_status = fnd_api.g_ret_sts_error THEN
709 RAISE fnd_api.g_exc_error;
710 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
711 RAISE fnd_api.g_exc_unexpected_error;
712 END IF;
713 END IF;
714
715 ELSIF p_child_flag = 'N' THEN
716 ozf_funds_pvt.update_fund(
717 p_api_version => l_api_version
718 ,p_init_msg_list => fnd_api.g_false
719 ,p_commit => fnd_api.g_false
720 ,x_return_status => l_return_status
721 ,x_msg_count => l_msg_count
722 ,x_msg_data => l_msg_data
723 ,p_fund_rec => l_target_fund_rec
724 ,p_mode => 'ADJUST');
725 */
726 END IF;
727 END IF;
728
729 IF l_return_status = fnd_api.g_ret_sts_error THEN
730 RAISE fnd_api.g_exc_error;
731 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
732 RAISE fnd_api.g_exc_unexpected_error;
733 END IF;
734
735 -- Fund Request Detail
736 OPEN c_act_budget_detail(p_act_budget_id);
737 FETCH c_act_budget_detail INTO l_act_budget_obj_num;
738
739 IF (c_act_budget_detail%NOTFOUND) THEN
740 CLOSE c_act_budget_detail;
741
742 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
743 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
744 fnd_msg_pub.add;
745 END IF;
746
747 RAISE fnd_api.g_exc_error;
748 END IF;
749
750 CLOSE c_act_budget_detail;
751 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budget_rec);
752 l_act_budget_rec.activity_budget_id := p_act_budget_id;
753 l_act_budget_rec.object_version_number := l_act_budget_obj_num;
754 -- l_act_budget_rec.approved_amount := p_approved_amount; -- Approved amount
755 -- l_act_budget_rec.approver_id := p_approver_id; -- Approved amount
756 -- changed on 6/24/2002 for ENH#2352621
757 l_act_budget_rec.status_code := p_status_code;
758 l_act_budget_rec.user_status_id :=
759 ozf_utility_pvt.get_default_user_status(
760 g_budget_source_status
761 ,l_act_budget_rec.status_code);
762 l_act_budget_rec.comment := p_note;
763 -- Fund request record
764 ozf_actbudgets_pvt.update_act_budgets(
765 p_api_version => l_api_version
766 ,p_init_msg_list => fnd_api.g_false
767 ,p_commit => fnd_api.g_false
768 ,x_return_status => l_return_status
769 ,x_msg_count => l_msg_count
770 ,x_msg_data => l_msg_data
771 ,p_act_budgets_rec => l_act_budget_rec);
772
773 -- Set the return status
774 IF l_return_status = fnd_api.g_ret_sts_error THEN
775 RAISE fnd_api.g_exc_error;
776 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
777 RAISE fnd_api.g_exc_unexpected_error;
778 END IF;
779
780 fnd_msg_pub.count_and_get(
781 p_encoded => fnd_api.g_false
782 ,p_count => x_msg_count
783 ,p_data => x_msg_data);
784
785 -- Conditional commit;
786 IF fnd_api.to_boolean(p_commit)
787 AND x_return_status = fnd_api.g_ret_sts_success THEN
788 COMMIT WORK;
789 END IF;
790 EXCEPTION
791 WHEN fnd_api.g_exc_error THEN
792 ROLLBACK TO negative_request;
793 x_return_status := fnd_api.g_ret_sts_error;
794 fnd_msg_pub.count_and_get(
795 p_encoded => fnd_api.g_false
796 ,p_count => x_msg_count
797 ,p_data => x_msg_data);
798 WHEN fnd_api.g_exc_unexpected_error THEN
799 ROLLBACK TO negative_request;
800 x_return_status := fnd_api.g_ret_sts_unexp_error;
801 fnd_msg_pub.count_and_get(
802 p_encoded => fnd_api.g_false
803 ,p_count => x_msg_count
804 ,p_data => x_msg_data);
805 WHEN OTHERS THEN
806 ROLLBACK TO negative_request;
807 x_return_status := fnd_api.g_ret_sts_unexp_error;
808
809 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
810 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
811 END IF;
812
813 fnd_msg_pub.count_and_get(
814 p_encoded => fnd_api.g_false
815 ,p_count => x_msg_count
816 ,p_data => x_msg_data);
817 END negative_request;
818
819 ---------------------------------------------------------------------
820 -- PROCEDURE
821 -- Create_Fund_Request
822 --
823 --
824 -- PURPOSE
825 -- This Procedure will create the fund request
826 --
827 --
828 -- IN
829 -- p_commit IN VARCHAR2 := fnd_api.g_false
830 -- p_update_status IN VARCHAR2 := 'Y'
831 -- p_approval_for IN VARCHAR2 := 'FUND'
832 -- p_approval_for_id IN NUMBER
833 -- p_requester_id IN NUMBER
834 -- p_requested_amount IN NUMBER
835 -- p_approval_fm IN VARCHAR2 := 'FUND'
836 -- p_approval_fm_id IN NUMBER DEFAULT NULL
837 -- p_transfer_type IN VARCHAR2 := 'TRANSFER' --- 'REQUEST' OR 'TRANSFER'
838 -- p_child_flag IN VARCHAR2 := 'N' -- flag to indicate wether it is a child fund creation
839 -- p_act_budget_id IN NUMBER := NULL -- request_id ( for a child fund it is null)
840 -- p_init_msg_list IN VARCHAR2 := fnd_api.g_false
841 -- p_justification IN VARCHAR2
842 --
843 -- OUT
844 -- x_return_status OUT VARCHAR2
845 -- x_msg_count OUT NUMBER
846 -- x_msg_data OUT VARCHAR2
847 -- x_request_id OUT NUMBER
848 -- x_approver_id OUT NUMBER
849 -- x_is_requester_owner OUT VARCHAR2 -- Use this variable to conditionally trigger the workflow if value ='NO'
850
851 -- Used By Activities
852 --
853 -- NOTES
854 --
855 --
856 --
857 -- HISTORY
858 -- 02/20/2001 MUMU PANDE CREATION
859 -- End of Comments
860 PROCEDURE create_fund_request(
861 p_commit IN VARCHAR2 := fnd_api.g_false
862 ,p_update_status IN VARCHAR2 := 'Y'
863 ,p_approval_for IN VARCHAR2 := 'FUND'
864 ,p_approval_for_id IN NUMBER
865 ,p_requester_id IN NUMBER
866 ,p_requested_amount IN NUMBER
867 ,p_approval_fm IN VARCHAR2 := 'FUND'
868 -- ,p_approval_fm_id IN NUMBER DEFAULT NULL yzhao: fix GSCC. default can only be defined in spec.
869 ,p_approval_fm_id IN NUMBER
870 ,p_transfer_type IN VARCHAR2 := 'REQUEST' --- 'REQUEST' OR 'TRANSFER'
871 ,p_child_flag IN VARCHAR2 := 'N' -- flag to indicate whether it is a child fund creation
872 ,p_act_budget_id IN NUMBER := NULL -- request_id ( for a child fund it is null)
873 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
874 ,p_justification IN VARCHAR2
875 ,p_allocation_flag IN VARCHAR2 := 'N' -- flag to indicate whether it is an allocation or not
876 ,x_return_status OUT NOCOPY VARCHAR2
877 ,x_msg_count OUT NOCOPY NUMBER
878 ,x_msg_data OUT NOCOPY VARCHAR2
879 ,x_request_id OUT NOCOPY NUMBER
880 ,x_approver_id OUT NOCOPY NUMBER
881 ,x_is_requester_owner OUT NOCOPY VARCHAR2 -- Use this variable to conditionally trigger the workflow if value ='NO'
882 )
883 IS
884 -- Local variables
885 l_api_version CONSTANT NUMBER := 1.0;
886 l_return_status VARCHAR2(1)
887 := fnd_api.g_ret_sts_success;
888 l_msg_count NUMBER;
889 l_msg_data VARCHAR2(4000);
890 l_api_name CONSTANT VARCHAR2(30)
891 := 'Create_Fund_Request';
892 l_full_name CONSTANT VARCHAR2(60)
893 := g_pkg_name || '.' || l_api_name;
894 -- Record variables for creating the fund request.
895 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type;
896 l_target_fund_rec ozf_funds_pvt.fund_rec_type;
897 l_object_version_number NUMBER;
898 l_to_currency VARCHAR2(30);
899 l_rate NUMBER;
900 --- local variable to hold the requested amount converted in transfer from fund currency
901 l_fm_curr_requested_amount NUMBER;
902 l_fm_currency VARCHAR2(30);
903 l_fund_objvernum NUMBER;
904 l_act_budget_id NUMBER := p_act_budget_id;
905 l_reject_status_id NUMBER;
906 l_new_status_id NUMBER;
907 x_child_approver_id NUMBER;
908
909 CURSOR c_fund_detail(
910 cv_fund_id NUMBER)
911 IS
912 SELECT object_version_number
913 FROM ozf_funds_all_b
914 WHERE fund_id = cv_fund_id;
915
916 -- Cursor to find the owner of the parent fund
917 CURSOR c_parent_fund_owner(
918 p_parent_fund_id NUMBER)
919 IS
920 SELECT owner
921 FROM ozf_funds_all_b
922 WHERE fund_id = p_parent_fund_id;
923
924 -- cursors to get the transfer from and transfer to currency
925 CURSOR c_fm_fund_currency(
926 p_fm_fund_id IN NUMBER)
927 IS
928 SELECT currency_code_tc
929 FROM ozf_funds_all_vl
930 WHERE fund_id = p_fm_fund_id;
931
932 -- cursors to get the transfer from and transfer to currency
933 CURSOR c_to_fund_currency(
934 p_to_fund_id IN NUMBER)
935 IS
936 SELECT currency_code_tc
937 FROM ozf_funds_all_vl
938 WHERE fund_id = p_to_fund_id;
939
940 -- cursor to get the act_budget_rec info ( request info)
941 CURSOR c_act_budget_rec(
942 p_act_budget_id IN NUMBER)
943 IS
944 SELECT object_version_number
945 ,request_amount
946 ,user_status_id
947 FROM ozf_act_budgets
948 WHERE activity_budget_id = p_act_budget_id;
949
950 l_act_budget_appr_rec c_act_budget_rec%ROWTYPE;
951 BEGIN
952 SAVEPOINT create_fund_request;
953 x_return_status := fnd_api.g_ret_sts_success;
954 -- Initialize
955 IF G_DEBUG THEN
956 ozf_utility_pvt.debug_message(l_full_name || ': start');
957 END IF;
958
959 IF p_child_flag = 'Y' THEN
960 -- Initialize the request variable prior to creating the request
961 l_act_budget_rec.status_code := 'NEW';
962 l_act_budget_rec.user_status_id :=
963 ozf_utility_pvt.get_default_user_status(
964 g_budget_source_status
965 ,l_act_budget_rec.status_code);
966 l_act_budget_rec.arc_act_budget_used_by := 'FUND'; -- hardcoded to fund
967 l_act_budget_rec.act_budget_used_by_id := p_approval_for_id;
968 l_act_budget_rec.requester_id := p_requester_id;
969 l_act_budget_rec.request_amount := p_requested_amount; --- in transferring to fund currency
970 l_act_budget_rec.budget_source_type := p_approval_fm;
971 l_act_budget_rec.budget_source_id := p_approval_fm_id;
972 l_act_budget_rec.justification := p_justification;
973 l_act_budget_rec.transfer_type := p_transfer_type;
974 l_act_budget_rec.transaction_type := 'CREDIT';
975 --l_act_budget_rec.date_required_by := p_needbydate;
976 -- Create_transfer record
977 ozf_actbudgets_pvt.create_act_budgets(
978 p_api_version => l_api_version
979 ,x_return_status => l_return_status
980 ,x_msg_count => l_msg_count
981 ,x_msg_data => l_msg_data
982 ,p_act_budgets_rec => l_act_budget_rec
983 ,x_act_budget_id => l_act_budget_id);
984
985 ------------if no request is created terminate the process
986 IF l_return_status = fnd_api.g_ret_sts_error THEN
987 RAISE fnd_api.g_exc_error;
988 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
989 RAISE fnd_api.g_exc_unexpected_error;
990 END IF;
991
992 IF G_DEBUG THEN
993 ozf_utility_pvt.debug_message(l_full_name || ': end creating request');
994 END IF;
995 END IF;
996
997 -- Get parent fund owner for the p_approval_fm_id
998 OPEN c_parent_fund_owner(p_approval_fm_id);
999 FETCH c_parent_fund_owner INTO x_approver_id;
1000
1001 IF (c_parent_fund_owner%NOTFOUND) THEN
1002 CLOSE c_parent_fund_owner;
1003
1004 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1005 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1006 fnd_msg_pub.add;
1007 END IF;
1008
1009 RAISE fnd_api.g_exc_error;
1010 END IF;
1011
1012 CLOSE c_parent_fund_owner;
1013
1014 -- nepanda : fix for bug # 8434546 : start
1015 -- Get current fund owner
1016 OPEN c_parent_fund_owner(p_approval_for_id);
1017 FETCH c_parent_fund_owner INTO x_child_approver_id;
1018
1019 IF (c_parent_fund_owner%NOTFOUND) THEN
1020 CLOSE c_parent_fund_owner;
1021
1022 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1023 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1024 fnd_msg_pub.add;
1025 END IF;
1026
1027 RAISE fnd_api.g_exc_error;
1028 END IF;
1029
1030 CLOSE c_parent_fund_owner;
1031 -- 10/22/2001 mpande Changed code different owner allocation bug
1032 IF p_allocation_flag = 'Y' THEN
1033 -- no approval required for allocation
1034 x_is_requester_owner := 'Y';
1035 ELSE
1036 -- Check if requester is also the owner of the parent fund OR child fund owner is same as parent fund owner
1037 IF x_approver_id = p_requester_id OR x_approver_id = x_child_approver_id THEN
1038 x_is_requester_owner := 'Y';
1039 ELSE
1040 x_is_requester_owner := 'N';
1041 END IF;
1042 END IF;
1043
1044 ----- check if the from and to currency are same
1045 OPEN c_fm_fund_currency(p_approval_fm_id);
1046 FETCH c_fm_fund_currency INTO l_fm_currency;
1047
1048 IF (c_fm_fund_currency%NOTFOUND) THEN
1049 CLOSE c_fm_fund_currency;
1050
1051 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1052 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1053 fnd_msg_pub.add;
1054 END IF;
1055
1056 RAISE fnd_api.g_exc_error;
1057 END IF;
1058
1059 CLOSE c_fm_fund_currency;
1060 OPEN c_to_fund_currency(p_approval_for_id);
1061 FETCH c_to_fund_currency INTO l_to_currency;
1062
1063 IF (c_to_fund_currency%NOTFOUND) THEN
1064 CLOSE c_to_fund_currency;
1065
1066 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1067 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1068 fnd_msg_pub.add;
1069 END IF;
1070
1071 RAISE fnd_api.g_exc_error;
1072 END IF;
1073
1074 CLOSE c_to_fund_currency;
1075
1076 ---- if the two currncies are different then convert the requested amount into the Transfer from fund currency
1077 IF l_to_currency <> l_fm_currency THEN
1078 ozf_utility_pvt.convert_currency(
1079 x_return_status => l_return_status
1080 ,p_from_currency => l_to_currency
1081 ,p_to_currency => l_fm_currency
1082 ,p_from_amount => p_requested_amount
1083 ,x_to_amount => l_fm_curr_requested_amount
1084 ,x_rate => l_rate);
1085
1086 IF l_return_status = fnd_api.g_ret_sts_error THEN
1087 RAISE fnd_api.g_exc_error;
1088 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1089 RAISE fnd_api.g_exc_unexpected_error;
1090 END IF;
1091 ELSE
1092 l_fm_curr_requested_amount := NVL(p_requested_amount, 0); -- when currencies are the same the
1093 END IF; -- end if transfer currency
1094 OPEN c_act_budget_rec(l_act_budget_id);
1095 FETCH c_act_budget_rec INTO l_act_budget_appr_rec.object_version_number,
1096 l_act_budget_appr_rec.request_amount,
1097 l_act_budget_appr_rec.user_status_id;
1098
1099 IF (c_act_budget_rec%NOTFOUND) THEN
1100 CLOSE c_act_budget_rec;
1101
1102 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1103 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
1104 fnd_msg_pub.add;
1105 END IF;
1106
1107 RAISE fnd_api.g_exc_error;
1108 END IF;
1109
1110 CLOSE c_act_budget_rec;
1111
1112 IF p_child_flag = 'Y' THEN
1113 -- change the act_budget statuscdode = 'PENDING'
1114 ---update the request stauts to pending before submitting if requestor owner is same then the
1115 -- transfer would become approved directly
1116 IF G_DEBUG THEN
1117 ozf_utility_pvt.debug_message(l_full_name || ': beforing updating child approval to pending');
1118 END IF;
1119 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budget_rec);
1120 l_act_budget_rec.activity_budget_id := l_act_budget_id;
1121 l_act_budget_rec.object_version_number := l_act_budget_appr_rec.object_version_number;
1122 l_act_budget_rec.status_code := 'APPROVED'; -- Approved amount
1123 l_act_budget_rec.user_status_id :=
1124 ozf_utility_pvt.get_default_user_status(
1125 g_budget_source_status
1126 ,l_act_budget_rec.status_code);
1127 -- Fund request record
1128 ozf_actbudgets_pvt.update_act_budgets(
1129 p_api_version => l_api_version
1130 ,p_init_msg_list => fnd_api.g_false
1131 ,p_commit => fnd_api.g_false
1132 ,x_return_status => l_return_status
1133 ,x_msg_count => l_msg_count
1134 ,x_msg_data => l_msg_data
1135 ,p_act_budgets_rec => l_act_budget_rec
1136 ,p_child_approval_flag => FND_API.g_true
1137 -- 10/22/2001 mpande Changed code different owner allocation bug
1138 ,p_requestor_owner_flag =>x_is_requester_owner );
1139
1140 IF l_return_status = fnd_api.g_ret_sts_error THEN
1141 RAISE fnd_api.g_exc_error;
1142 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1143 RAISE fnd_api.g_exc_unexpected_error;
1144 END IF;
1145 -- if child fund the object veriosn number is +1
1146 l_act_budget_appr_rec.object_version_number := l_act_budget_appr_rec.object_version_number +1 ;
1147 END IF; -- end if for child fund
1148
1149 -- If the parent fund owner and the requester are the same then call Approve_Request
1150 -- else trigger the workflow
1151 IF x_is_requester_owner = 'Y' THEN
1152 -- apporve it directly if requester and owner is the same
1153 approve_request(
1154 p_commit => fnd_api.g_false
1155 ,p_update_status => p_update_status
1156 ,p_act_budget_id => l_act_budget_id
1157 ,p_target_fund_id => p_approval_for_id
1158 ,p_source_fund_id => p_approval_fm_id
1159 ,p_requester_id => p_requester_id
1160 ,p_requestor_owner => x_is_requester_owner
1161 ,p_approver_id => x_approver_id
1162 -- ,p_requested_amount => l_fm_curr_requested_amount -- should be passed transferring fm fund_currency
1163 ,p_approved_amount => l_fm_curr_requested_amount -- in transferring fm fund_currency
1164 ,p_note => NULL
1165 ,p_child_flag => p_child_flag
1166 ,x_return_status => l_return_status
1167 ,x_msg_count => l_msg_count
1168 ,x_msg_data => l_msg_data);
1169
1170 IF l_return_status = fnd_api.g_ret_sts_error THEN
1171 RAISE fnd_api.g_exc_error;
1172 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1173 RAISE fnd_api.g_exc_unexpected_error;
1174 END IF;
1175 ELSIF x_is_requester_owner = 'N' THEN
1176
1177 l_new_status_id :=
1178 ozf_utility_pvt.get_default_user_status(g_budget_source_status, 'APPROVED');
1179 l_reject_status_id :=
1180 ozf_utility_pvt.get_default_user_status(g_budget_source_status, 'REJECTED');
1181 -- 08/14/2001 mpande changed activity type
1182 --only for child budget creation.
1183 IF p_child_flag = 'Y' THEN
1184
1185 ams_gen_approval_pvt.startprocess(
1186 p_activity_type => g_activity_type
1187 ,p_activity_id => l_act_budget_id
1188 ,p_approval_type => g_transfer_approval_type
1189 ,p_object_version_number => l_act_budget_appr_rec.object_version_number + 1
1190 ,p_orig_stat_id => l_act_budget_appr_rec.user_status_id
1191 ,p_new_stat_id => l_new_status_id
1192 ,p_reject_stat_id => l_reject_status_id
1193 ,p_requester_userid => p_requester_id
1194 ,p_notes_from_requester => p_justification
1195 ,p_workflowprocess => g_workflow_process
1196 ,p_item_type => g_item_type
1197 ,p_gen_process_flag => p_child_flag);
1198 -- update the request status to pending here.
1199 END IF; -- end of child flag.
1200
1201 END IF; -- ENDIF x_is_requester_owner
1202
1203 -- Conditional commit;
1204 IF fnd_api.to_boolean(p_commit)
1205 AND x_return_status = fnd_api.g_ret_sts_success THEN
1206 COMMIT WORK;
1207 END IF;
1208
1209 fnd_msg_pub.count_and_get(
1210 p_encoded => fnd_api.g_false
1211 ,p_count => x_msg_count
1212 ,p_data => x_msg_data);
1213 EXCEPTION
1214 WHEN fnd_api.g_exc_error THEN
1215 ROLLBACK TO create_fund_request;
1216 x_return_status := fnd_api.g_ret_sts_error;
1217 fnd_msg_pub.count_and_get(
1218 p_encoded => fnd_api.g_false
1219 ,p_count => x_msg_count
1220 ,p_data => x_msg_data);
1221 WHEN fnd_api.g_exc_unexpected_error THEN
1222 ROLLBACK TO create_fund_request;
1223 x_return_status := fnd_api.g_ret_sts_unexp_error;
1224 fnd_msg_pub.count_and_get(
1225 p_encoded => fnd_api.g_false
1226 ,p_count => x_msg_count
1227 ,p_data => x_msg_data);
1228 WHEN OTHERS THEN
1229 ROLLBACK TO create_fund_request;
1230 x_return_status := fnd_api.g_ret_sts_unexp_error;
1231
1232 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1233 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1234 END IF;
1235
1236 fnd_msg_pub.count_and_get(
1237 p_encoded => fnd_api.g_false
1238 ,p_count => x_msg_count
1239 ,p_data => x_msg_data);
1240 END create_fund_request;
1241
1242 --------------------------------------------------------------------------
1243 -- PROCEDURE
1244 -- Notify_requestor_FYI
1245 --
1246 -- PURPOSE
1247 -- Generate the FYI Document for display in messages, either
1248 -- text or html
1249 -- IN
1250 -- document_id - Item Key
1251 -- display_type - either 'text/plain' or 'text/html'
1252 -- document - document buffer
1253 -- document_type - type of document buffer created, either 'text/plain'
1254 -- or 'text/html'
1255 -- OUT
1256 -- USED BY
1257 -- - Oracle MArketing Generic Apporval
1258 -- HISTORY
1259 -- 03/15/2001 MUMU PANDE CREATION
1260
1261 PROCEDURE notify_requestor_fyi(
1262 document_id IN VARCHAR2
1263 ,display_type IN VARCHAR2
1264 ,document IN OUT NOCOPY VARCHAR2
1265 ,document_type IN OUT NOCOPY VARCHAR2)
1266 IS
1267 l_api_name VARCHAR2(61) := g_pkg_name || 'Notify_Requestor_FYI';
1268 l_hyphen_pos1 NUMBER;
1269 l_fyi_notification VARCHAR2(10000);
1270 l_activity_type VARCHAR2(30);
1271 l_item_type VARCHAR2(30);
1272 l_item_key VARCHAR2(30);
1273 l_approval_type VARCHAR2(30);
1274 l_approver VARCHAR2(200);
1275 l_note VARCHAR2(4000);
1276 l_string VARCHAR2(2500);
1277 l_string1 VARCHAR2(2500);
1278 l_requester VARCHAR2(360);
1279 l_string2 VARCHAR2(2500);
1280 l_requested_amt NUMBER;
1281 l_reason_meaning VARCHAR2(2000);
1282 l_act_budget_id NUMBER;
1283
1284 CURSOR c_act_budget_rec(
1285 p_act_budget_id IN NUMBER)
1286 IS
1287 SELECT act.request_date
1288 ,act.budget_source_id approval_from_id
1289 ,fund1.short_name from_budget_name
1290 ,fund1.owner_full_name from_budget_owner_name
1291 ,fund1.fund_number from_budget_number
1292 ,fund1.currency_code_tc from_budget_curr
1293 ,act.act_budget_used_by_id approval_for_id
1294 ,fund2.short_name to_budget_name
1295 ,fund2.owner_full_name to_budget_owner_name
1296 ,fund2.fund_number to_budget_number
1297 ,fund2.currency_code_tc to_budget_curr
1298 ,act.date_required_by
1299 ,act.reason_code
1300 FROM ozf_act_budgets act
1301 ,ozf_fund_details_v fund1
1302 ,ozf_fund_details_v fund2
1303 WHERE activity_budget_id = p_act_budget_id
1304 AND act.budget_source_id = fund1.fund_id
1305 AND act.act_budget_used_by_id = fund2.fund_id;
1306
1307 l_request_rec c_act_budget_rec%ROWTYPE;
1308 BEGIN
1309 IF G_DEBUG THEN
1310 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
1311 END IF;
1312 document_type := 'text/plain';
1313 -- parse document_id for the ':' dividing item type name from item key value
1314 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1315 -- release 2.5 version of this demo
1316 l_hyphen_pos1 := INSTR(document_id, ':');
1317 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1318 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1319 l_activity_type :=
1320 wf_engine.getitemattrtext(
1321 itemtype => l_item_type
1322 ,itemkey => l_item_key
1323 ,aname => 'AMS_ACTIVITY_TYPE');
1324 l_act_budget_id :=
1325 wf_engine.getitemattrnumber(
1326 itemtype => l_item_type
1327 ,itemkey => l_item_key
1328 ,aname => 'AMS_ACTIVITY_ID');
1329 l_requested_amt :=
1330 wf_engine.getitemattrnumber(
1331 itemtype => l_item_type
1332 ,itemkey => l_item_key
1333 ,aname => 'AMS_REQUESTED_AMOUNT');
1334 l_note := wf_engine.getitemattrtext(
1335 itemtype => l_item_type
1336 ,itemkey => l_item_key
1337 ,aname => 'AMS_NOTES_FROM_REQUESTOR');
1338 l_approver :=
1339 wf_engine.getitemattrtext(
1340 itemtype => l_item_type
1341 ,itemkey => l_item_key
1342 ,aname => 'AMS_APPROVER_DISPLAY_NAME');
1343 OPEN c_act_budget_rec(l_act_budget_id);
1344 FETCH c_act_budget_rec INTO l_request_rec;
1345 CLOSE c_act_budget_rec;
1346 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUESTER_FYI_SUB');
1347 fnd_message.set_token('BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1348 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1349 fnd_message.set_token('AMOUNT', l_requested_amt, FALSE);
1350 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1351 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1352 l_string := fnd_message.get;
1353 wf_engine.setitemattrtext(
1354 itemtype => l_item_type
1355 ,itemkey => l_item_key
1356 ,aname => 'FYI_SUBJECT'
1357 ,avalue => l_string);
1358 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUEST_INFO');
1359 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
1360 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1361 fnd_message.set_token('REQUEST_DATE', l_request_rec.request_date, FALSE);
1362 fnd_message.set_token('FROM_BUDGET_NAME', l_request_rec.from_budget_name, FALSE);
1363 fnd_message.set_token('FROM_BUDGET_NUMBER', l_request_rec.from_budget_number, FALSE);
1364 fnd_message.set_token('FROM_BUDGET_OWNER', l_request_rec.from_budget_owner_name, FALSE);
1365 fnd_message.set_token('TO_BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1366 fnd_message.set_token('To_BUDGET_NUMBER', l_request_rec.to_budget_number, FALSE);
1367 fnd_message.set_token('TO_BUDGET_OWNER', l_request_rec.to_budget_owner_name, FALSE);
1368 fnd_message.set_token('REQUIRED_BY_DATE', l_request_rec.date_required_by, FALSE);
1369 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1370 fnd_message.set_token('REQUEST_AMOUNT', l_requested_amt, FALSE);
1371 fnd_message.set_token('JUSTIFICATION', l_note, FALSE);
1372 l_reason_meaning :=
1373 ozf_utility_pvt.get_lookup_meaning('AMS_TRANSFER_REASON', l_request_rec.reason_code);
1374 fnd_message.set_token('REASON', l_reason_meaning, FALSE);
1375 l_string1 := fnd_message.get;
1376
1377 l_note := wf_engine.getitemattrtext(
1378 itemtype => l_item_type
1379 ,itemkey => l_item_key
1380 ,aname => 'APPROVAL_NOTE');
1381
1382 /*
1383 l_forwarder :=
1384 wf_engine.getitemattrtext(
1385 itemtype => l_item_type
1386 ,itemkey => l_item_key
1387 ,aname => 'AMS_FORWARD_FROM_USERNAME');
1388 */
1389 -- IF (display_type = 'text/plain') THEN
1390 l_fyi_notification :=
1391 l_string || fnd_global.local_chr(10) || l_string1 || fnd_global.local_chr(10) || l_string2;
1392 document := document || l_fyi_notification;
1393 document_type := 'text/plain';
1394 RETURN;
1395 -- END IF;
1396
1397 /* IF (display_type = 'text/html') THEN
1398 l_fyi_notification :=
1399 l_string ||
1400 FND_GLOBAL.LOCAL_CHR(10) ||
1401 l_string1 ||
1402 FND_GLOBAL.LOCAL_CHR(10) ||
1403 l_string2;
1404 document := document||l_appreq_notification;
1405 document_type := 'text/html';
1406 RETURN;
1407 END IF;
1408 */
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 wf_core.context('AMSGAPP', 'Notify_requestor_FYI', l_item_type, l_item_key);
1412 RAISE;
1413 END notify_requestor_fyi;
1414
1415 --------------------------------------------------------------------------
1416 -- PROCEDURE
1417 -- Notify_requestor_of Approval
1418 --
1419 -- PURPOSE
1420 -- Generate the Approval Document for display in messages, either
1421 -- text or html
1422 -- IN
1423 -- document_id - Item Key
1424 -- display_type - either 'text/plain' or 'text/html'
1425 -- document - document buffer
1426 -- document_type - type of document buffer created, either 'text/plain'
1427 -- or 'text/html'
1428 -- OUT
1429 -- USED BY
1430 -- - Oracle MArketing Generic Apporval
1431 -- HISTORY
1432 -- 03/15/2001 MUMU PANDE CREATION
1433 ----------------------------------------------------------------------------
1434
1435 PROCEDURE notify_requestor_of_approval(
1436 document_id IN VARCHAR2
1437 ,display_type IN VARCHAR2
1438 ,document IN OUT NOCOPY VARCHAR2
1439 ,document_type IN OUT NOCOPY VARCHAR2)
1440 IS
1441 l_api_name VARCHAR2(100)
1442 := g_pkg_name || 'Notify_Requestor_of_approval';
1443 l_hyphen_pos1 NUMBER;
1444 l_appr_notification VARCHAR2(10000);
1445 l_activity_type VARCHAR2(30);
1446 l_item_type VARCHAR2(30);
1447 l_item_key VARCHAR2(30);
1448 l_approval_type VARCHAR2(30);
1449 l_approver VARCHAR2(200);
1450 l_note VARCHAR2(4000);
1451 l_approver_note VARCHAR2(4000);
1452 l_approved_amt NUMBER;
1453 l_string VARCHAR2(2500);
1454 l_string1 VARCHAR2(2500);
1455 l_requester VARCHAR2(360);
1456 l_string2 VARCHAR2(2500);
1457 l_requested_amt NUMBER;
1458 l_reason_meaning VARCHAR2(2000);
1459 l_act_budget_id NUMBER;
1460
1461 CURSOR c_act_budget_rec(
1462 p_act_budget_id IN NUMBER)
1463 IS
1464 SELECT act.request_date
1465 ,act.budget_source_id approval_from_id
1466 ,fund1.short_name from_budget_name
1467 ,fund1.owner_full_name from_budget_owner_name
1468 ,fund1.fund_number from_budget_number
1469 ,fund1.currency_code_tc from_budget_curr
1470 ,act.act_budget_used_by_id approval_for_id
1471 ,fund2.short_name to_budget_name
1472 ,fund2.owner_full_name to_budget_owner_name
1473 ,fund2.fund_number to_budget_number
1474 ,fund2.currency_code_tc to_budget_curr
1475 ,act.date_required_by
1476 ,act.reason_code
1477 FROM ozf_act_budgets act
1478 ,ozf_fund_details_v fund1
1479 ,ozf_fund_details_v fund2
1480 WHERE activity_budget_id = p_act_budget_id
1481 AND act.budget_source_id = fund1.fund_id
1482 AND act.act_budget_used_by_id = fund2.fund_id;
1483
1484 l_request_rec c_act_budget_rec%ROWTYPE;
1485 BEGIN
1486 IF G_DEBUG THEN
1487 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
1488 END IF;
1489 document_type := 'text/plain';
1490 -- parse document_id for the ':' dividing item type name from item key value
1491 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1492 -- release 2.5 version of this demo
1493 l_hyphen_pos1 := INSTR(document_id, ':');
1494 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1495 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1496 l_activity_type :=
1497 wf_engine.getitemattrtext(
1498 itemtype => l_item_type
1499 ,itemkey => l_item_key
1500 ,aname => 'AMS_ACTIVITY_TYPE');
1501 l_act_budget_id :=
1502 wf_engine.getitemattrnumber(
1503 itemtype => l_item_type
1504 ,itemkey => l_item_key
1505 ,aname => 'AMS_ACTIVITY_ID');
1506 l_requested_amt :=
1507 wf_engine.getitemattrnumber(
1508 itemtype => l_item_type
1509 ,itemkey => l_item_key
1510 ,aname => 'AMS_REQUESTED_AMOUNT');
1511 l_approved_amt :=
1512 wf_engine.getitemattrnumber(
1513 itemtype => l_item_type
1514 ,itemkey => l_item_key
1515 ,aname => 'AMS_AMOUNT');
1516 l_note := wf_engine.getitemattrtext(
1517 itemtype => l_item_type
1518 ,itemkey => l_item_key
1519 ,aname => 'AMS_NOTES_FROM_REQUESTOR');
1520 l_approver :=
1521 wf_engine.getitemattrtext(
1522 itemtype => l_item_type
1523 ,itemkey => l_item_key
1524 ,aname => 'AMS_APPROVER');
1525
1526 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) APPROVAL NOTE NOT SHOWING IN APPROVAL EMAIL */
1527 l_approver_note :=
1528 wf_engine.getitemattrtext (
1529 itemtype=> l_item_type,
1530 itemkey=> l_item_key,
1531 aname => 'APPROVAL_NOTE'
1532 );
1533 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) ends - APPROVAL NOTE NOT SHOWING IN APPROVAL EMAIL */
1534
1535 OPEN c_act_budget_rec(l_act_budget_id);
1536 FETCH c_act_budget_rec INTO l_request_rec;
1537 CLOSE c_act_budget_rec;
1538 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUESTER_APP_SUB');
1539 fnd_message.set_token('BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1540 -- fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1541 -- fnd_message.set_token('AMOUNT', l_approved_amt, FALSE);
1542 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1543 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1544 l_string := fnd_message.get;
1545 wf_engine.setitemattrtext(
1546 itemtype => l_item_type
1547 ,itemkey => l_item_key
1548 ,aname => 'APRV_SUBJECT'
1549 ,avalue => l_string);
1550 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUEST_INFO');
1551 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
1552 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1553 fnd_message.set_token('REQUEST_DATE', l_request_rec.request_date, FALSE);
1554 fnd_message.set_token('FROM_BUDGET_NAME', l_request_rec.from_budget_name, FALSE);
1555 fnd_message.set_token('FROM_BUDGET_NUMBER', l_request_rec.from_budget_number, FALSE);
1556 fnd_message.set_token('FROM_BUDGET_OWNER', l_request_rec.from_budget_owner_name, FALSE);
1557 fnd_message.set_token('TO_BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1558 fnd_message.set_token('To_BUDGET_NUMBER', l_request_rec.to_budget_number, FALSE);
1559 fnd_message.set_token('TO_BUDGET_OWNER', l_request_rec.to_budget_owner_name, FALSE);
1560 fnd_message.set_token('REQUIRED_BY_DATE', l_request_rec.date_required_by, FALSE);
1561 -- commented on 10/22/2001 mpande
1562 -- yzhao: 01/23/2003 uncomment following 2 lines as tokens are defined in message
1563 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1564 fnd_message.set_token('REQUEST_AMOUNT', l_requested_amt, FALSE);
1565 fnd_message.set_token('JUSTIFICATION', l_note, FALSE);
1566 l_reason_meaning :=
1567 ozf_utility_pvt.get_lookup_meaning('AMS_TRANSFER_REASON', l_request_rec.reason_code);
1568 fnd_message.set_token('REASON', l_reason_meaning, FALSE);
1569 l_string1 := fnd_message.get;
1570 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUESTER_ADDENDUM');
1571 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
1572 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1573 fnd_message.set_token('AMOUNT', l_approved_amt, FALSE);
1574 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) APPROVAL NOTE NOT SHOWING IN APPROVAL EMAIL
1575 fnd_message.set_token('NOTES_FROM_APPROVER', l_note, FALSE); */
1576 fnd_message.set_token('NOTES_FROM_APPROVER', l_approver_note, FALSE);
1577 l_string2 := fnd_message.get;
1578 /*
1579 l_note := wf_engine.getitemattrtext(
1580 itemtype => l_item_type
1581 ,itemkey => l_item_key
1582 ,aname => 'NOTE');
1583
1584
1585 l_forwarder :=
1586 wf_engine.getitemattrtext(
1587 itemtype => l_item_type
1588 ,itemkey => l_item_key
1589 ,aname => 'AMS_FORWARD_FROM_USERNAME');
1590 */
1591 -- IF (display_type = 'text/plain') THEN
1592 l_appr_notification :=
1593 l_string || fnd_global.local_chr(10) || l_string1 || fnd_global.local_chr(10) || l_string2;
1594 document := document || l_appr_notification;
1595 document_type := 'text/plain';
1596 RETURN;
1597 -- END IF;
1598
1599 /* IF (display_type = 'text/html') THEN
1600 l_appreq_notification :=
1601 l_string ||
1602 FND_GLOBAL.LOCAL_CHR(10) ||
1603 l_string1 ||
1604 FND_GLOBAL.LOCAL_CHR(10) ||
1605 l_string2;
1606 document := document||l_appreq_notification;
1607 document_type := 'text/html';
1608 RETURN;
1609 END IF;
1610 */
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 wf_core.context('AMSGAPP', 'Notify_requestor_FYI', l_item_type, l_item_key);
1614 RAISE;
1615 END notify_requestor_of_approval;
1616
1617 --------------------------------------------------------------------------
1618 -- PROCEDURE
1619 -- Notify_requestor_of rejection
1620 --
1621 -- PURPOSE
1622 -- Generate the Rejection Document for display in messages, either
1623 -- text or html
1624 -- IN
1625 -- document_id - Item Key
1626 -- display_type - either 'text/plain' or 'text/html'
1627 -- document - document buffer
1628 -- document_type - type of document buffer created, either 'text/plain'
1629 -- or 'text/html'
1630 -- OUT
1631 -- USED BY
1632 -- - Oracle MArketing Generic Apporval
1633 -- HISTORY
1634 -- 03/15/2001 MUMU PANDE CREATION
1635 -------------------------------------------------------------------------------
1636
1637 PROCEDURE notify_requestor_of_rejection(
1638 document_id IN VARCHAR2
1639 ,display_type IN VARCHAR2
1640 ,document IN OUT NOCOPY VARCHAR2
1641 ,document_type IN OUT NOCOPY VARCHAR2)
1642 IS
1643 l_api_name VARCHAR2(100)
1644 := g_pkg_name || 'Notify_Requestor_of_rejection';
1645 l_act_budget_id NUMBER;
1646 l_hyphen_pos1 NUMBER;
1647 l_rej_notification VARCHAR2(10000);
1648 l_activity_type VARCHAR2(30);
1649 l_item_type VARCHAR2(30);
1650 l_item_key VARCHAR2(30);
1651 l_approval_type VARCHAR2(30);
1652 l_approver VARCHAR2(200);
1653 l_note VARCHAR2(4000);
1654 l_approved_amt NUMBER;
1655 l_string VARCHAR2(2500);
1656 l_string1 VARCHAR2(2500);
1657 l_start_date DATE;
1658 l_requester VARCHAR2(360);
1659 l_string2 VARCHAR2(2500);
1660 l_requested_amt NUMBER;
1661 l_reason_meaning VARCHAR2(2000);
1662
1663 CURSOR c_act_budget_rec(
1664 p_act_budget_id IN NUMBER)
1665 IS
1666 SELECT act.request_date
1667 ,act.budget_source_id approval_from_id
1668 ,fund1.short_name from_budget_name
1669 ,fund1.owner_full_name from_budget_owner_name
1670 ,fund1.fund_number from_budget_number
1671 ,fund1.currency_code_tc from_budget_curr
1672 ,act.act_budget_used_by_id approval_for_id
1673 ,fund2.short_name to_budget_name
1674 ,fund2.owner_full_name to_budget_owner_name
1675 ,fund2.fund_number to_budget_number
1676 ,fund2.currency_code_tc to_budget_curr
1677 ,act.date_required_by
1678 ,act.reason_code
1679 FROM ozf_act_budgets act
1680 ,ozf_fund_details_v fund1
1681 ,ozf_fund_details_v fund2
1682 WHERE activity_budget_id = p_act_budget_id
1683 AND act.budget_source_id = fund1.fund_id
1684 AND act.act_budget_used_by_id = fund2.fund_id;
1685
1686 l_request_rec c_act_budget_rec%ROWTYPE;
1687 BEGIN
1688 IF G_DEBUG THEN
1689 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
1690 END IF;
1691 document_type := 'text/plain';
1692 -- parse document_id for the ':' dividing item type name from item key value
1693 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1694 -- release 2.5 version of this demo
1695 l_hyphen_pos1 := INSTR(document_id, ':');
1696 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1697 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1698 l_activity_type :=
1699 wf_engine.getitemattrtext(
1700 itemtype => l_item_type
1701 ,itemkey => l_item_key
1702 ,aname => 'AMS_ACTIVITY_TYPE');
1703 l_act_budget_id :=
1704 wf_engine.getitemattrnumber(
1705 itemtype => l_item_type
1706 ,itemkey => l_item_key
1707 ,aname => 'AMS_ACTIVITY_ID');
1708 l_requested_amt :=
1709 wf_engine.getitemattrnumber(
1710 itemtype => l_item_type
1711 ,itemkey => l_item_key
1712 ,aname => 'AMS_REQUESTED_AMOUNT');
1713 l_note := wf_engine.getitemattrtext(
1714 itemtype => l_item_type
1715 ,itemkey => l_item_key
1716 ,aname => 'AMS_NOTES_FROM_REQUESTOR');
1717 l_approver :=
1718 wf_engine.getitemattrtext(
1719 itemtype => l_item_type
1720 ,itemkey => l_item_key
1721 ,aname => 'AMS_APPROVER');
1722 OPEN c_act_budget_rec(l_act_budget_id);
1723 FETCH c_act_budget_rec INTO l_request_rec;
1724 CLOSE c_act_budget_rec;
1725 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUESTER_REJ_SUB');
1726 fnd_message.set_token('BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1727 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1728 fnd_message.set_token('AMOUNT', l_requested_amt, FALSE);
1729 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1730 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1731 l_string := fnd_message.get;
1732 wf_engine.setitemattrtext(
1733 itemtype => l_item_type
1734 ,itemkey => l_item_key
1735 ,aname => 'REJECT_SUBJECT'
1736 ,avalue => l_string);
1737 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUEST_INFO');
1738 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
1739 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1740 fnd_message.set_token('REQUEST_DATE', l_request_rec.request_date, FALSE);
1741 fnd_message.set_token('FROM_BUDGET_NAME', l_request_rec.from_budget_name, FALSE);
1742 fnd_message.set_token('FROM_BUDGET_NUMBER', l_request_rec.from_budget_number, FALSE);
1743 fnd_message.set_token('FROM_BUDGET_OWNER', l_request_rec.from_budget_owner_name, FALSE);
1744 fnd_message.set_token('TO_BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1745 fnd_message.set_token('To_BUDGET_NUMBER', l_request_rec.to_budget_number, FALSE);
1746 fnd_message.set_token('TO_BUDGET_OWNER', l_request_rec.to_budget_owner_name, FALSE);
1747 fnd_message.set_token('REQUIRED_BY_DATE', l_request_rec.date_required_by, FALSE);
1748 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1749 fnd_message.set_token('REQUEST_AMOUNT', l_requested_amt, FALSE);
1750 fnd_message.set_token('JUSTIFICATION', l_note, FALSE);
1751 l_reason_meaning :=
1752 ozf_utility_pvt.get_lookup_meaning('AMS_TRANSFER_REASON', l_request_rec.reason_code);
1753 fnd_message.set_token('REASON', l_reason_meaning, FALSE);
1754 -- l_string1 := Substr(FND_MESSAGE.Get,1,2500);
1755 l_string1 := fnd_message.get;
1756 /*
1757 l_note := wf_engine.getitemattrtext(
1758 itemtype => l_item_type
1759 ,itemkey => l_item_key
1760 ,aname => 'NOTE');
1761
1762
1763 l_forwarder :=
1764 wf_engine.getitemattrtext(
1765 itemtype => l_item_type
1766 ,itemkey => l_item_key
1767 ,aname => 'AMS_FORWARD_FROM_USERNAME');
1768 */
1769
1770 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) APPROVer's NOTE NOT SHOWING IN rejection EMAIL */
1771 l_note :=
1772 wf_engine.getitemattrtext (
1773 itemtype=> l_item_type,
1774 itemkey=> l_item_key,
1775 aname => 'APPROVAL_NOTE'
1776 );
1777 fnd_message.set_name ('OZF', 'OZF_WF_NTF_APPROVER_NOTE');
1778 fnd_message.set_token ('NOTES_FROM_APPROVER', l_note, FALSE);
1779 l_string2 := SUBSTR(FND_MESSAGE.Get, 1, 2500);
1780 /* yzhao: 01/23/2003 fix bug 2771105(same as 11.5.8 bug 2753608) ends - APPROVer's NOTE NOT SHOWING IN rejection EMAIL */
1781
1782 -- IF (display_type = 'text/plain') THEN
1783 l_rej_notification :=
1784 l_string || fnd_global.local_chr(10) || l_string1 || fnd_global.local_chr(10) || l_string2;
1785 document := document || l_rej_notification;
1786 document_type := 'text/plain';
1787 RETURN;
1788 -- END IF;
1789
1790 /* IF (display_type = 'text/html') THEN
1791 l_appreq_notification :=
1792 l_string ||
1793 FND_GLOBAL.LOCAL_CHR(10) ||
1794 l_string1 ||
1795 FND_GLOBAL.LOCAL_CHR(10) ||
1796 l_string2;
1797 document := document||l_appreq_notification;
1798 document_type := 'text/html';
1799 RETURN;
1800 END IF;
1801 */
1802 EXCEPTION
1803 WHEN OTHERS THEN
1804 wf_core.context('AMSGAPP', 'Notify_requestor_of_rejection', l_item_type, l_item_key);
1805 RAISE;
1806 END notify_requestor_of_rejection;
1807
1808 --------------------------------------------------------------------------
1809 -- PROCEDURE
1810 -- Notify_approval_required
1811 --
1812 -- PURPOSE
1813 -- Generate the Rejection Document for display in messages, either
1814 -- text or html
1815 -- IN
1816 -- document_id - Item Key
1817 -- display_type - either 'text/plain' or 'text/html'
1818 -- document - document buffer
1819 -- document_type - type of document buffer created, either 'text/plain'
1820 -- or 'text/html'
1821 -- OUT
1822 -- USED BY
1823 -- - Oracle MArketing Generic Apporval
1824 -- HISTORY
1825 -- 03/15/2001 MUMU PANDE CREATION
1826
1827
1828 PROCEDURE notify_approval_required(
1829 document_id IN VARCHAR2
1830 ,display_type IN VARCHAR2
1831 ,document IN OUT NOCOPY VARCHAR2
1832 ,document_type IN OUT NOCOPY VARCHAR2)
1833 IS
1834 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_approval_required';
1835 l_hyphen_pos1 NUMBER;
1836 l_appreq_notification VARCHAR2(10000);
1837 l_activity_type VARCHAR2(30);
1838 l_item_type VARCHAR2(30);
1839 l_item_key VARCHAR2(30);
1840 l_approval_type VARCHAR2(30);
1841 l_forwarder VARCHAR2(360);
1842 l_note VARCHAR2(4000);
1843 l_requested_amt NUMBER;
1844 l_approved_amt NUMBER;
1845 l_string VARCHAR2(2500);
1846 l_string1 VARCHAR2(2500);
1847 l_approver VARCHAR2(200);
1848 l_requester VARCHAR2(360);
1849 l_string2 VARCHAR2(2500);
1850 l_reason_meaning VARCHAR2(2000);
1851 l_act_budget_id NUMBER;
1852
1853 CURSOR c_act_budget_rec(
1854 p_act_budget_id IN NUMBER)
1855 IS
1856 SELECT act.request_date
1857 ,act.budget_source_id approval_from_id
1858 ,fund1.short_name from_budget_name
1859 ,fund1.owner_full_name from_budget_owner_name
1860 ,fund1.fund_number from_budget_number
1861 ,fund1.currency_code_tc from_budget_curr
1862 ,act.act_budget_used_by_id approval_for_id
1863 ,fund2.short_name to_budget_name
1864 ,fund2.owner_full_name to_budget_owner_name
1865 ,fund2.fund_number to_budget_number
1866 ,fund2.currency_code_tc to_budget_curr
1867 ,act.date_required_by
1868 ,act.reason_code
1869 FROM ozf_act_budgets act
1870 ,ozf_fund_details_v fund1
1871 ,ozf_fund_details_v fund2
1872 WHERE activity_budget_id = p_act_budget_id
1873 AND act.budget_source_id = fund1.fund_id
1874 AND act.act_budget_used_by_id = fund2.fund_id;
1875
1876 l_request_rec c_act_budget_rec%ROWTYPE;
1877 BEGIN
1878 IF G_DEBUG THEN
1879 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
1880 END IF;
1881 document_type := 'text/plain';
1882 -- parse document_id for the ':' dividing item type name from item key value
1883 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1884 -- release 2.5 version of this demo
1885 l_hyphen_pos1 := INSTR(document_id, ':');
1886 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1887 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1888 l_activity_type :=
1889 wf_engine.getitemattrtext(
1890 itemtype => l_item_type
1891 ,itemkey => l_item_key
1892 ,aname => 'AMS_ACTIVITY_TYPE');
1893 l_act_budget_id :=
1894 wf_engine.getitemattrnumber(
1895 itemtype => l_item_type
1896 ,itemkey => l_item_key
1897 ,aname => 'AMS_ACTIVITY_ID');
1898 l_requested_amt :=
1899 wf_engine.getitemattrnumber(
1900 itemtype => l_item_type
1901 ,itemkey => l_item_key
1902 ,aname => 'AMS_REQUESTED_AMOUNT');
1903 l_note := wf_engine.getitemattrtext(
1904 itemtype => l_item_type
1905 ,itemkey => l_item_key
1906 ,aname => 'AMS_NOTES_FROM_REQUESTOR');
1907 l_approver :=
1908 wf_engine.getitemattrtext(
1909 itemtype => l_item_type
1910 ,itemkey => l_item_key
1911 ,aname => 'AMS_APPROVER_DISPLAY_NAME');
1912 OPEN c_act_budget_rec(l_act_budget_id);
1913 FETCH c_act_budget_rec INTO l_request_rec;
1914 CLOSE c_act_budget_rec;
1915 fnd_message.set_name('OZF', 'OZF_WF_NTF_APPROVER_OF_REQ_SUB');
1916 fnd_message.set_token('BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1917 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1918 fnd_message.set_token('AMOUNT', l_requested_amt, FALSE);
1919 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1920 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1921 l_string := fnd_message.get;
1922 wf_engine.setitemattrtext(
1923 itemtype => l_item_type
1924 ,itemkey => l_item_key
1925 ,aname => 'APP_SUBJECT'
1926 ,avalue => l_string);
1927 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUEST_INFO');
1928 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
1929 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
1930 fnd_message.set_token('REQUEST_DATE', l_request_rec.request_date, FALSE);
1931 fnd_message.set_token('FROM_BUDGET_NAME', l_request_rec.from_budget_name, FALSE);
1932 fnd_message.set_token('FROM_BUDGET_NUMBER', l_request_rec.from_budget_number, FALSE);
1933 fnd_message.set_token('FROM_BUDGET_OWNER', l_request_rec.from_budget_owner_name, FALSE);
1934 fnd_message.set_token('TO_BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
1935 fnd_message.set_token('To_BUDGET_NUMBER', l_request_rec.to_budget_number, FALSE);
1936 fnd_message.set_token('TO_BUDGET_OWNER', l_request_rec.to_budget_owner_name, FALSE);
1937 fnd_message.set_token('REQUIRED_BY_DATE', l_request_rec.date_required_by, FALSE);
1938 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
1939 fnd_message.set_token('REQUEST_AMOUNT', l_requested_amt, FALSE);
1940 fnd_message.set_token('JUSTIFICATION', l_note, FALSE);
1941 l_reason_meaning :=
1942 ozf_utility_pvt.get_lookup_meaning('AMS_TRANSFER_REASON', l_request_rec.reason_code);
1943 fnd_message.set_token('REASON', l_reason_meaning, FALSE);
1944 l_string1 := fnd_message.get;
1945 /*
1946 l_note := wf_engine.getitemattrtext(
1947 itemtype => l_item_type
1948 ,itemkey => l_item_key
1949 ,aname => 'NOTE');
1950
1951
1952 l_forwarder :=
1953 wf_engine.getitemattrtext(
1954 itemtype => l_item_type
1955 ,itemkey => l_item_key
1956 ,aname => 'AMS_FORWARD_FROM_USERNAME');
1957 */
1958 -- IF (display_type = 'text/plain') THEN
1959 l_appreq_notification :=
1960 l_string || fnd_global.local_chr(10) || l_string1 || fnd_global.local_chr(10) || l_string2;
1961 document := document || l_appreq_notification;
1962 document_type := 'text/plain';
1963 RETURN;
1964 -- END IF;
1965
1966 /* IF (display_type = 'text/html') THEN
1967 l_appreq_notification :=
1968 l_string ||
1969 FND_GLOBAL.LOCAL_CHR(10) ||
1970 l_string1 ||
1971 FND_GLOBAL.LOCAL_CHR(10) ||
1972 l_string2;
1973 document := document||l_appreq_notification;
1974 document_type := 'text/html';
1975 RETURN;
1976 END IF;
1977 */
1978
1979 EXCEPTION
1980 WHEN OTHERS THEN
1981 wf_core.context('AMSGAPP', 'Notify_requestor_FYI', l_item_type, l_item_key);
1982 RAISE;
1983 END notify_approval_required;
1984
1985 --------------------------------------------------------------------------
1986 -- PROCEDURE
1987 -- notify_appr_req_reminder
1988 --
1989 -- PURPOSE
1990 -- Generate the Rejection Document for display in messages, either
1991 -- text or html
1992 -- IN
1993 -- document_id - Item Key
1994 -- display_type - either 'text/plain' or 'text/html'
1995 -- document - document buffer
1996 -- document_type - type of document buffer created, either 'text/plain'
1997 -- or 'text/html'
1998 -- OUT
1999 -- USED BY
2000 -- - Oracle MArketing Generic Apporval
2001 -- HISTORY
2002 -- 03/15/2001 MUMU PANDE CREATION
2003
2004 PROCEDURE notify_appr_req_reminder(
2005 document_id IN VARCHAR2
2006 ,display_type IN VARCHAR2
2007 ,document IN OUT NOCOPY VARCHAR2
2008 ,document_type IN OUT NOCOPY VARCHAR2)
2009 IS
2010 l_api_name VARCHAR2(100) := g_pkg_name || 'notify_appr_req_reminder';
2011 l_hyphen_pos1 NUMBER;
2012 l_apprem_notification VARCHAR2(10000);
2013 l_activity_type VARCHAR2(30);
2014 l_item_type VARCHAR2(30);
2015 l_item_key VARCHAR2(30);
2016 l_approval_type VARCHAR2(30);
2017 l_note VARCHAR2(4000);
2018 l_approved_amt NUMBER;
2019 l_forwarder VARCHAR2(360);
2020 l_string VARCHAR2(2500);
2021 l_string1 VARCHAR2(2500);
2022 l_approver VARCHAR2(200);
2023 l_requester VARCHAR2(360);
2024 l_string2 VARCHAR2(2500);
2025 l_reason_meaning VARCHAR2(2000);
2026 l_act_budget_id NUMBER;
2027 l_requested_amt NUMBER;
2028
2029 CURSOR c_act_budget_rec(
2030 p_act_budget_id IN NUMBER)
2031 IS
2032 SELECT act.request_date
2033 ,act.budget_source_id approval_from_id
2034 ,fund1.short_name from_budget_name
2035 ,fund1.owner_full_name from_budget_owner_name
2036 ,fund1.fund_number from_budget_number
2037 ,fund1.currency_code_tc from_budget_curr
2038 ,act.act_budget_used_by_id approval_for_id
2039 ,fund2.short_name to_budget_name
2040 ,fund2.owner_full_name to_budget_owner_name
2041 ,fund2.fund_number to_budget_number
2042 ,fund2.currency_code_tc to_budget_curr
2043 ,act.date_required_by
2044 ,act.reason_code
2045 FROM ozf_act_budgets act
2046 ,ozf_fund_details_v fund1
2047 ,ozf_fund_details_v fund2
2048 WHERE activity_budget_id = p_act_budget_id
2049 AND act.budget_source_id = fund1.fund_id
2050 AND act.act_budget_used_by_id = fund2.fund_id;
2051
2052 l_request_rec c_act_budget_rec%ROWTYPE;
2053 BEGIN
2054 IF G_DEBUG THEN
2055 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
2056 END IF;
2057 document_type := 'text/plain';
2058 -- parse document_id for the ':' dividing item type name from item key value
2059 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
2060 -- release 2.5 version of this demo
2061 l_hyphen_pos1 := INSTR(document_id, ':');
2062 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
2063 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
2064 l_activity_type :=
2065 wf_engine.getitemattrtext(
2066 itemtype => l_item_type
2067 ,itemkey => l_item_key
2068 ,aname => 'AMS_ACTIVITY_TYPE');
2069 l_act_budget_id :=
2070 wf_engine.getitemattrnumber(
2071 itemtype => l_item_type
2072 ,itemkey => l_item_key
2073 ,aname => 'AMS_ACTIVITY_ID');
2074 l_requested_amt :=
2075 wf_engine.getitemattrnumber(
2076 itemtype => l_item_type
2077 ,itemkey => l_item_key
2078 ,aname => 'AMS_REQUESTED_AMOUNT');
2079 l_note := wf_engine.getitemattrtext(
2080 itemtype => l_item_type
2081 ,itemkey => l_item_key
2082 ,aname => 'AMS_NOTES_FROM_REQUESTOR');
2083 l_approver :=
2084 wf_engine.getitemattrtext(
2085 itemtype => l_item_type
2086 ,itemkey => l_item_key
2087 ,aname => 'AMS_APPROVER');
2088 OPEN c_act_budget_rec(l_act_budget_id);
2089 FETCH c_act_budget_rec INTO l_request_rec;
2090 CLOSE c_act_budget_rec;
2091 fnd_message.set_name('OZF', 'OZF_WF_NTF_APPROVER_OF_REQ_SUB');
2092 fnd_message.set_token('BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
2093 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
2094 fnd_message.set_token('AMOUNT', l_requested_amt, FALSE);
2095 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
2096 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
2097 l_string := fnd_message.get;
2098 wf_engine.setitemattrtext(
2099 itemtype => l_item_type
2100 ,itemkey => l_item_key
2101 ,aname => 'FYI_SUBJECT'
2102 ,avalue => l_string);
2103 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUEST_INFO');
2104 fnd_message.set_token('APPROVER_NAME', l_approver, FALSE);
2105 fnd_message.set_token('REQUEST_NUMBER', l_act_budget_id, FALSE);
2106 fnd_message.set_token('REQUEST_DATE', l_request_rec.request_date, FALSE);
2107 fnd_message.set_token('FROM_BUDGET_NAME', l_request_rec.from_budget_name, FALSE);
2108 fnd_message.set_token('FROM_BUDGET_NUMBER', l_request_rec.from_budget_number, FALSE);
2109 fnd_message.set_token('FROM_BUDGET_OWNER', l_request_rec.from_budget_owner_name, FALSE);
2110 fnd_message.set_token('TO_BUDGET_NAME', l_request_rec.to_budget_name, FALSE);
2111 fnd_message.set_token('To_BUDGET_NUMBER', l_request_rec.to_budget_number, FALSE);
2112 fnd_message.set_token('TO_BUDGET_OWNER', l_request_rec.to_budget_owner_name, FALSE);
2113 fnd_message.set_token('REQUIRED_BY_DATE', l_request_rec.date_required_by, FALSE);
2114 fnd_message.set_token('CURRENCY_CODE', l_request_rec.to_budget_curr, FALSE);
2115 fnd_message.set_token('REQUEST_AMOUNT', l_requested_amt, FALSE);
2116 fnd_message.set_token('JUSTIFICATION', l_note, FALSE);
2117 l_reason_meaning :=
2118 ozf_utility_pvt.get_lookup_meaning('AMS_TRANSFER_REASON', l_request_rec.reason_code);
2119 fnd_message.set_token('REASON', l_reason_meaning, FALSE);
2120 l_string1 := fnd_message.get;
2121 /*
2122 l_note := wf_engine.getitemattrtext(
2123 itemtype => l_item_type
2124 ,itemkey => l_item_key
2125 ,aname => 'NOTE');
2126
2127
2128 l_forwarder :=
2129 wf_engine.getitemattrtext(
2130 itemtype => l_item_type
2131 ,itemkey => l_item_key
2132 ,aname => 'AMS_FORWARD_FROM_USERNAME');
2133 */
2134 -- IF (display_type = 'text/plain') THEN
2135 l_apprem_notification :=
2136 l_string || fnd_global.local_chr(10) || l_string1 || fnd_global.local_chr(10) || l_string2;
2137 document := document || l_apprem_notification;
2138 document_type := 'text/plain';
2139 RETURN;
2140 -- END IF;
2141
2142 /* IF (display_type = 'text/html') THEN
2143 l_appreq_notification :=
2144 l_string ||
2145 FND_GLOBAL.LOCAL_CHR(10) ||
2146 l_string1 ||
2147 FND_GLOBAL.LOCAL_CHR(10) ||
2148 l_string2;
2149 document := document||l_appreq_notification;
2150 document_type := 'text/html';
2151 RETURN;
2152 END IF;
2153 */
2154
2155 EXCEPTION
2156 WHEN OTHERS THEN
2157 wf_core.context('AMSGAPP', 'notify_appr_req_reminder', l_item_type, l_item_key);
2158 RAISE;
2159 END notify_appr_req_reminder;
2160
2161 ---------------------------------------------------------------------
2162 -- PROCEDURE
2163 -- Set_trans_Activity_details
2164 --
2165 --
2166 -- PURPOSE
2167 -- This Procedure will set all the item attribute details
2168 --
2169 --
2170 -- IN
2171 --
2172 --
2173 -- OUT
2174 --
2175 -- Used By Activities
2176 --
2177 -- NOTES
2178 --
2179 --
2180 --
2181 -- HISTORY
2182 -- 02/20/2001 MUMU PANDE CREATION
2183 -- 07/09/2001 MUMU PANDE Modified to set all subjects
2184 -- End of Comments
2185 --------------------------------------------------------------------
2186 PROCEDURE set_trans_activity_details(
2187 itemtype IN VARCHAR2
2188 ,itemkey IN VARCHAR2
2189 ,actid IN NUMBER
2190 ,funcmode IN VARCHAR2
2191 ,resultout OUT NOCOPY VARCHAR2)
2192 IS
2193 l_activity_id NUMBER;
2194 /*
2195 l_activity_type VARCHAR2(30) := 'FUND';
2196 l_approval_type VARCHAR2(30) := 'ROOT_BUDGET';
2197 */
2198 -- mpande 08/14/2001 cahnged as per new reqmts.
2199 l_activity_type VARCHAR2(30) := 'FREQ';
2200 l_approval_type VARCHAR2(30) := 'BUDGET';
2201
2202 l_object_details ams_gen_approval_pvt.objrectyp;
2203 l_approval_detail_id NUMBER;
2204 l_approver_seq NUMBER;
2205 l_return_status VARCHAR2(1);
2206 l_msg_count NUMBER;
2207 l_msg_data VARCHAR2(4000);
2208 l_error_msg VARCHAR2(4000);
2209 l_orig_stat_id NUMBER;
2210 x_resource_id NUMBER;
2211 l_full_name VARCHAR2(240);
2212 l_fund_number VARCHAR2(30);
2213 l_requested_amt NUMBER;
2214 l_fund_id NUMBER;
2215 l_child_transfer_flag VARCHAR2(3);
2216 l_string VARCHAR2(3000);
2217 l_budget_source_id NUMBER;
2218 -- l_approver VARCHAR2(300);
2219 l_lookup_meaning VARCHAR2(240);
2220 l_justification VARCHAR2(4000);
2221
2222 CURSOR c_act_budget_rec(
2223 p_act_budget_id IN NUMBER)
2224 IS
2225 SELECT act_budget_used_by_id
2226 FROM ozf_act_budgets
2227 WHERE activity_budget_id = p_act_budget_id;
2228 /* 02/26/2002 mpande added for budget transfer not picking up correct approval rules
2229 with category */
2230 CURSOR c_budget_src_rec(
2231 p_act_budget_id IN NUMBER)
2232 IS
2233 SELECT budget_source_id
2234 FROM ozf_act_budgets
2235 WHERE activity_budget_id = p_act_budget_id;
2236
2237 CURSOR c_src_category(
2238 p_src_id IN NUMBER)
2239 IS
2240 SELECT to_char(category_id)
2241 FROM ozf_funds_all_b
2242 WHERE fund_id = p_src_id;
2243 /* End of Addition for category-approval*/
2244
2245 CURSOR c_fund_rec(
2246 p_act_id IN NUMBER)
2247 IS
2248 SELECT short_name
2249 ,custom_setup_id
2250 ,original_budget
2251 ,org_id
2252 ,to_char(category_id)
2253 ,start_date_active
2254 ,end_date_active
2255 ,owner
2256 ,currency_code_tc
2257 FROM ozf_funds_all_vl
2258 WHERE fund_id = p_act_id;
2259
2260 CURSOR c_transfer_rec(
2261 p_act_budget_id IN NUMBER)
2262 IS
2263 SELECT fund.short_name
2264 ,fund.custom_setup_id
2265 ,act1.request_amount
2266 ,fund.org_id
2267 ,'FUND'
2268 ,fund.start_date_active
2269 ,fund.end_date_active
2270 ,act1.requester_id
2271 ,act1.request_currency
2272 FROM ozf_act_budgets act1
2273 ,ozf_funds_all_vl fund
2274 WHERE activity_budget_id = p_act_budget_id
2275 AND act1.act_budget_used_by_id = fund.fund_id;
2276
2277 -- yzhao: 01/28/2003 get budget request's justification
2278 CURSOR c_get_justification(p_act_budget_id IN NUMBER) IS
2279 SELECT notes
2280 FROM jtf_notes_vl
2281 WHERE source_object_code = 'AMS_FREQ'
2282 AND note_type = 'AMS_JUSTIFICATION'
2283 AND source_object_id = p_act_budget_id;
2284
2285 BEGIN
2286 fnd_msg_pub.initialize;
2287 l_activity_id :=
2288 wf_engine.getitemattrnumber(
2289 itemtype => itemtype
2290 ,itemkey => itemkey
2291 ,aname => 'AMS_ACTIVITY_ID');
2292 l_child_transfer_flag :=
2293 wf_engine.getitemattrtext(
2294 itemtype => itemtype
2295 ,itemkey => itemkey
2296 ,aname => 'AMS_GENERIC_FLAG');
2297
2298 IF l_child_transfer_flag = 'Y' THEN
2299 OPEN c_act_budget_rec(l_activity_id);
2300 FETCH c_act_budget_rec INTO l_fund_id;
2301
2302
2303 IF (c_act_budget_rec%NOTFOUND) THEN
2304 CLOSE c_act_budget_rec;
2305
2306 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2307 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2308 fnd_msg_pub.add;
2309 END IF;
2310
2311 RAISE fnd_api.g_exc_error;
2312 END IF;
2313 CLOSE c_act_budget_rec;
2314 OPEN c_fund_rec(l_fund_id);
2315 FETCH c_fund_rec INTO l_object_details.name,
2316 l_object_details.setup_type_id,
2317 l_object_details.total_header_amount,
2318 l_object_details.org_id,
2319 l_object_details.object_type,
2320 l_object_details.start_date,
2321 l_object_details.end_date,
2322 l_object_details.owner_id,
2323 l_object_details.currency;
2324
2325 IF (c_fund_rec%NOTFOUND) THEN
2326 CLOSE c_fund_rec;
2327
2328 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2329 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2330 fnd_msg_pub.add;
2331 END IF;
2332
2333 RAISE fnd_api.g_exc_error;
2334 END IF;
2335 CLOSE c_fund_rec;
2336 ELSE
2337 OPEN c_transfer_rec(l_activity_id);
2338 FETCH c_transfer_rec INTO l_object_details.name,
2339 l_object_details.setup_type_id,
2340 l_object_details.total_header_amount,
2341 l_object_details.org_id,
2342 l_object_details.object_type,
2343 l_object_details.start_date,
2344 l_object_details.end_date,
2345 l_object_details.owner_id,
2346 l_object_details.currency;
2347 IF (c_transfer_rec%NOTFOUND) THEN
2348 CLOSE c_fund_rec;
2349
2350 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2351 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2352 fnd_msg_pub.add;
2353 END IF;
2354
2355 RAISE fnd_api.g_exc_error;
2356 END IF;
2357 CLOSE c_transfer_rec;
2358
2359 END IF;
2360 /* 02/26/2002 mpande added for budget transfer not going to the correct approver */
2361 OPEN c_budget_src_rec(l_activity_id);
2362 FETCH c_budget_src_rec INTO l_budget_source_id;
2363
2364
2365 IF (c_budget_src_rec%NOTFOUND) THEN
2366 CLOSE c_budget_src_rec;
2367
2368 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2369 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2370 fnd_msg_pub.add;
2371 END IF;
2372
2373 RAISE fnd_api.g_exc_error;
2374 END IF;
2375 CLOSE c_budget_src_rec;
2376
2377 /*Fix for Bug 7290977*/
2378
2379 OPEN c_act_budget_rec(l_activity_id);
2380 FETCH c_act_budget_rec INTO l_fund_id;
2381 CLOSE c_act_budget_rec;
2382
2383 OPEN c_src_category(l_fund_id);
2384 FETCH c_src_category INTO l_object_details.object_type;
2385 CLOSE c_src_category;
2386
2387 /*End of Addition for budget category */
2388
2389
2390 IF (funcmode = 'RUN') THEN
2391 ams_gen_approval_pvt.get_approval_details(
2392 p_activity_id => l_activity_id
2393 ,p_activity_type => g_activity_type
2394 ,p_approval_type => g_transfer_approval_type
2395 ,p_object_details => l_object_details
2396 ,x_approval_detail_id => l_approval_detail_id
2397 ,x_approver_seq => l_approver_seq
2398 ,x_return_status => l_return_status);
2399
2400 IF l_return_status = fnd_api.g_ret_sts_success THEN
2401 wf_engine.setitemattrnumber(
2402 itemtype => itemtype
2403 ,itemkey => itemkey
2404 ,aname => 'AMS_APPROVAL_DETAIL_ID'
2405 ,avalue => l_approval_detail_id);
2406 wf_engine.setitemattrnumber(
2407 itemtype => itemtype
2408 ,itemkey => itemkey
2409 ,aname => 'AMS_APPROVER_SEQ'
2410 ,avalue => l_approver_seq);
2411 wf_engine.setitemattrnumber(
2412 itemtype => itemtype
2413 ,itemkey => itemkey
2414 ,aname => 'AMS_REQUESTED_AMOUNT'
2415 ,avalue => l_object_details.total_header_amount);
2416
2417 -- yzhao: 01/28/2003 set justfication
2418 OPEN c_get_justification(l_activity_id);
2419 FETCH c_get_justification INTO l_justification;
2420 CLOSE c_get_justification;
2421 wf_engine.setitemattrtext(
2422 itemtype => itemtype
2423 ,itemkey => itemkey
2424 ,aname => 'AMS_NOTES_FROM_REQUESTOR'
2425 ,avalue => l_justification);
2426
2427 /* set the fyi subject
2428 l_approver :=
2429 wf_engine.getitemattrtext(
2430 itemtype => itemtype
2431 ,itemkey => itemkey
2432 ,aname => 'AMS_APPROVER');
2433 */
2434
2435 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUESTER_FYI_SUB');
2436 fnd_message.set_token('BUDGET_NAME', l_object_details.name, FALSE);
2437 fnd_message.set_token('CURRENCY_CODE', l_object_details.currency, FALSE);
2438 fnd_message.set_token('AMOUNT', l_object_details.total_header_amount, FALSE);
2439 fnd_message.set_token('REQUEST_NUMBER', l_activity_id, FALSE);
2440 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
2441 l_string := fnd_message.get;
2442 wf_engine.setitemattrtext(
2443 itemtype => itemtype
2444 ,itemkey => itemkey
2445 ,aname => 'FYI_SUBJECT'
2446 ,avalue => l_string);
2447
2448 -- set the approval subject
2449 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUESTER_APP_SUB');
2450 fnd_message.set_token('BUDGET_NAME', l_object_details.name, FALSE);
2451 -- fnd_message.set_token('CURRENCY_CODE', l_object_details.currency, FALSE);
2452 -- fnd_message.set_token('AMOUNT', l_object_details.total_header_amount, FALSE);
2453 fnd_message.set_token('REQUEST_NUMBER', l_activity_id, FALSE);
2454 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
2455 l_string := fnd_message.get;
2456 wf_engine.setitemattrtext(
2457 itemtype => itemtype
2458 ,itemkey => itemkey
2459 ,aname => 'APRV_SUBJECT'
2460 ,avalue => l_string);
2461 -- set the reject subject
2462 fnd_message.set_name('OZF', 'OZF_WF_NTF_REQUESTER_REJ_SUB');
2463 fnd_message.set_token('BUDGET_NAME', l_object_details.name, FALSE);
2464 fnd_message.set_token('CURRENCY_CODE', l_object_details.currency, FALSE);
2465 fnd_message.set_token('AMOUNT', l_object_details.total_header_amount, FALSE);
2466 fnd_message.set_token('REQUEST_NUMBER', l_activity_id, FALSE);
2467 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
2468 l_string := fnd_message.get;
2469 wf_engine.setitemattrtext(
2470 itemtype => itemtype
2471 ,itemkey => itemkey
2472 ,aname => 'REJECT_SUBJECT'
2473 ,avalue => l_string);
2474 -- set the approval requred subject
2475 fnd_message.set_name('OZF', 'OZF_WF_NTF_APPROVER_OF_REQ_SUB');
2476 fnd_message.set_token('BUDGET_NAME', l_object_details.name, FALSE);
2477 fnd_message.set_token('CURRENCY_CODE', l_object_details.currency, FALSE);
2478 fnd_message.set_token('AMOUNT', l_object_details.total_header_amount, FALSE);
2479 fnd_message.set_token('REQUEST_NUMBER', l_activity_id, FALSE);
2480 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
2481 l_string := fnd_message.get;
2482 wf_engine.setitemattrtext(
2483 itemtype => itemtype
2484 ,itemkey => itemkey
2485 ,aname => 'APP_SUBJECT'
2486 ,avalue => l_string);
2487 /* mpande added for implementation of BUG#2352621*/
2488 l_lookup_meaning := ozf_utility_pvt.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER','FREQ');
2489 wf_engine.setitemattrtext (
2490 itemtype=> itemtype,
2491 itemkey=> itemkey,
2492 aname => 'AMS_APPROVAL_OBJECT_MEANING',
2493 avalue=> l_lookup_meaning
2494 );
2495 wf_engine.setitemattrtext (
2496 itemtype=> itemtype,
2497 itemkey=> itemkey,
2498 aname => 'AMS_APPROVAL_OBJECT_NAME',
2499 avalue=> l_object_details.name
2500 );
2501 /* End of Addition for Bug#2352621*/
2502
2503
2504 resultout := 'COMPLETE:SUCCESS';
2505 ELSE
2506 fnd_msg_pub.count_and_get(
2507 p_encoded => fnd_api.g_false
2508 ,p_count => l_msg_count
2509 ,p_data => l_msg_data);
2510 ams_gen_approval_pvt.handle_err(
2511 p_itemtype => itemtype
2512 ,p_itemkey => itemkey
2513 ,p_msg_count => l_msg_count
2514 , -- Number of error Messages
2515 p_msg_data => l_msg_data
2516 ,p_attr_name => 'AMS_ERROR_MSG'
2517 ,x_error_msg => l_error_msg);
2518 wf_core.context(
2519 'ozf_fund_request_apr_pvt'
2520 ,'Set_trans_Activity_Details'
2521 ,itemtype
2522 ,itemkey
2523 ,actid
2524 ,l_error_msg);
2525 -- RAISE FND_API.G_EXC_ERROR;
2526 resultout := 'COMPLETE:ERROR';
2527 END IF;
2528 END IF;
2529
2530 --
2531 -- CANCEL mode
2532 --
2533 IF (funcmode = 'CANCEL') THEN
2534 resultout := 'COMPLETE:';
2535 RETURN;
2536 END IF;
2537
2538 --
2539 -- TIMEOUT mode
2540 --
2541 IF (funcmode = 'TIMEOUT') THEN
2542 resultout := 'COMPLETE:';
2543 RETURN;
2544 END IF;
2545 --
2546
2547 EXCEPTION
2548 WHEN fnd_api.g_exc_error THEN
2549 wf_core.context(
2550 'ozf_fund_request_apr_pvt'
2551 ,'Set_trans_Activity_Detail'
2552 ,itemtype
2553 ,itemkey
2554 ,actid
2555 ,funcmode
2556 ,l_error_msg);
2557 RAISE;
2558 WHEN OTHERS THEN
2559 fnd_msg_pub.count_and_get(
2560 p_encoded => fnd_api.g_false
2561 ,p_count => l_msg_count
2562 ,p_data => l_msg_data);
2563 ams_gen_approval_pvt.handle_err(
2564 p_itemtype => itemtype
2565 ,p_itemkey => itemkey
2566 ,p_msg_count => l_msg_count
2567 , -- Number of error Messages
2568 p_msg_data => l_msg_data
2569 ,p_attr_name => 'AMS_ERROR_MSG'
2570 ,x_error_msg => l_error_msg);
2571 resultout := 'COMPLETE:ERROR';
2572
2573 RAISE;
2574 END set_trans_activity_details;
2575
2576
2577 ---------------------------------------------------------------------
2578 -- PROCEDURE
2579 -- Get_Ntf_Rule_Values
2580 --
2581 -- PURPOSE
2582 -- This Procedure will check the value apporved_amount in the
2583 -- of the notification rule of the approver
2584 -- IN
2585 -- p_approver_name IN VARCHAR2,
2586 -- p_result IN VARCHAR2 --
2587 -- OUT
2588 -- x_text_value OUT VARCHAR2
2589 -- x_number_value OUT NUMBER
2590 --
2591 -- Used By Activities
2592 --
2593 -- NOTES
2594 --
2595 -- HISTORY
2596 -- 10/2/2002 MUMU PANDE CREATION
2597 -- End of Comments
2598 -------------------------------------------------------------------
2599 PROCEDURE Get_Ntf_Rule_Values
2600 (p_approver_name IN VARCHAR2,
2601 x_text_value OUT NOCOPY VARCHAR2,
2602 x_number_value OUT NOCOPY NUMBER)
2603 IS
2604 CURSOR c_get_rule IS
2605 SELECT b.text_value, b.number_value
2606 FROM wf_routing_rules a, wf_routing_rule_attributes b
2607 WHERE a.rule_id = b.rule_id
2608 AND a.role = p_approver_name
2609 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(begin_date, sysdate -1)) AND
2610 TRUNC(NVL(end_date,sysdate+1))
2611 AND a.message_name = 'AMS_APPROVAL_REQUIRED_OZF'
2612 AND b.name = 'AMS_AMOUNT';
2613
2614 BEGIN
2615 x_text_value := null;
2616 x_number_value := null;
2617 OPEN c_get_rule;
2618 FETCH c_get_rule INTO x_text_value, x_number_value;
2619 IF c_get_rule%NOTFOUND THEN
2620 x_text_value := NULL;
2621 x_number_value := 0;
2622 END IF;
2623 CLOSE c_get_rule;
2624 EXCEPTION
2625 WHEN OTHERS THEN
2626 IF G_DEBUG THEN
2627 ozf_utility_pvt.debug_message ('ozf_fund_approval_pvt.get_ntf_rule_values() exception.' || SQLERRM);
2628 END IF;
2629 END Get_Ntf_Rule_Values;
2630
2631
2632 --------------------------------------------------------------------------
2633 -- yzhao: internal procedure called by update_budgettrans_status() to fix bug 2750841(same as 2741039)
2634 --------------------------------------------------------------------------
2635 FUNCTION find_org_id (p_fund_id IN NUMBER) RETURN number IS
2636 l_org_id number := NULL;
2637
2638 CURSOR get_fund_org_csr(p_id in number) IS
2639 SELECT org_id
2640 FROM ozf_funds_all_b
2641 WHERE fund_id = p_id;
2642
2643 BEGIN
2644
2645 OPEN get_fund_org_csr(p_fund_id);
2646 FETCH get_fund_org_csr INTO l_org_id;
2647 CLOSE get_fund_org_csr;
2648
2649 RETURN l_org_id;
2650
2651 END find_org_id;
2652 --------------------------------------------------------------------------
2653 --------------------------------------------------------------------------
2654 -- yzhao: internal procedure called by update_budgettrans_status() to fix bug 2750841(same as 2741039)
2655 --------------------------------------------------------------------------
2656 PROCEDURE set_org_ctx (p_org_id IN NUMBER) IS
2657 BEGIN
2658
2659 IF p_org_id is not NULL THEN
2660 fnd_client_info.set_org_context(to_char(p_org_id));
2661 END IF;
2662
2663 END set_org_ctx;
2664 --------------------------------------------------------------------------
2665
2666 ---------------------------------------------------------------------
2667 -- PROCEDURE
2668 -- Update_Budgettrans_Statas
2669 --
2670 --
2671 -- PURPOSE
2672 -- This Procedure will update the status
2673 --
2674 --
2675 -- IN
2676 --
2677 --
2678 -- OUT
2679 --
2680 -- Used By Activities
2681 --
2682 -- NOTES
2683 --
2684 --
2685 --
2686 -- HISTORY
2687 -- 02/20/2001 MUMU PANDE CREATION
2688 -- End of Comments
2689 -------------------------------------------------------------------
2690
2691 PROCEDURE update_budgettrans_status(
2692 itemtype IN VARCHAR2
2693 ,itemkey IN VARCHAR2
2694 ,actid IN NUMBER
2695 ,funcmode IN VARCHAR2
2696 ,resultout OUT NOCOPY VARCHAR2)
2697 IS
2698 l_status_code VARCHAR2(30);
2699 l_api_version CONSTANT NUMBER := 1.0;
2700 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2701 l_msg_count NUMBER;
2702 l_msg_data VARCHAR2(4000);
2703 l_api_name CONSTANT VARCHAR2(30) := 'Update_ParBudget_Status';
2704 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2705 l_next_status_id NUMBER;
2706 l_approved_amount NUMBER;
2707 l_update_status VARCHAR2(30);
2708 l_error_msg VARCHAR2(4000);
2709 l_object_version_number NUMBER;
2710 l_act_budget_id NUMBER;
2711 l_approver_id NUMBER;
2712 l_approval_for_id NUMBER;
2713 l_approval_fm_id NUMBER;
2714 l_requester_id NUMBER;
2715 l_requested_amt NUMBER;
2716 l_approver VARCHAR2(320);
2717 l_text_value VARCHAR2(2000);
2718 l_number_value NUMBER;
2719 l_note VARCHAR2(4000);
2720 l_child_transfer_flag VARCHAR2(3);
2721 l_to_currency VARCHAR2(15);
2722 l_from_currency VARCHAR2(15);
2723 l_approved_amt_in_from_curr NUMBER;
2724 l_org_id NUMBER;
2725
2726 CURSOR c_act_budget_rec(
2727 p_act_budget_id IN NUMBER)
2728 IS
2729 SELECT budget_source_id approval_from_id
2730 ,act_budget_used_by_id approval_for_id
2731 ,request_currency
2732 ,fund.currency_code_tc
2733 FROM ozf_act_budgets act
2734 ,ozf_funds_all_b fund
2735 WHERE activity_budget_id = p_act_budget_id
2736 AND act.budget_source_id = fund.fund_id;
2737
2738 BEGIN
2739 IF funcmode = 'RUN' THEN
2740 l_update_status :=
2741 wf_engine.getitemattrtext(
2742 itemtype => itemtype
2743 ,itemkey => itemkey
2744 ,aname => 'UPDATE_GEN_STATUS');
2745 l_approved_amount :=
2746 wf_engine.getitemattrnumber(
2747 itemtype => itemtype
2748 ,itemkey => itemkey
2749 ,aname => 'AMS_AMOUNT');
2750 l_approver_id :=
2751 wf_engine.getitemattrnumber(
2752 itemtype => itemtype
2753 ,itemkey => itemkey
2754 ,aname => 'AMS_APPROVER_ID');
2755 l_requester_id :=
2756 wf_engine.getitemattrnumber(
2757 itemtype => itemtype
2758 ,itemkey => itemkey
2759 ,aname => 'AMS_REQUESTER_ID');
2760
2761 IF l_update_status = 'APPROVED' THEN
2762 l_next_status_id :=
2763 wf_engine.getitemattrnumber(
2764 itemtype => itemtype
2765 ,itemkey => itemkey
2766 ,aname => 'AMS_NEW_STAT_ID');
2767
2768 /* yzhao 10/03/2002 bug#2577992 when automatic approval notification rule is set
2769 if auto approval amount > request amount, then final approval amount := request amount
2770 else final approval amount := auto approval amount
2771 */
2772 l_approver :=
2773 wf_engine.getitemattrtext (
2774 itemtype=> itemtype,
2775 itemkey=> itemkey,
2776 aname => 'AMS_APPROVER'
2777 );
2778 l_requested_amt :=
2779 wf_engine.getitemattrnumber (
2780 itemtype=> itemtype,
2781 itemkey=> itemkey,
2782 aname => 'AMS_REQUESTED_AMOUNT'
2783 );
2784
2785 /* Approved Amount is null in the following cases
2786 when requester and approver are the same, no approval is required and AMS_AMOUNT is not set
2787 should take AMS_REQUESTED_AMOUNT. Fix for 3638512
2788 */
2789
2790 IF l_approved_amount IS NULL THEN
2791 IF l_approver_id = l_requester_id THEN
2792 l_approved_amount := l_requested_amt;
2793 END IF;
2794 END IF;
2795
2796 Get_Ntf_Rule_Values
2797 (p_approver_name => l_approver,
2798 x_text_value => l_text_value ,
2799 x_number_value => l_number_value);
2800
2801 IF NVL(l_number_value, 0) > 0 THEN
2802 IF l_number_value > l_requested_amt THEN
2803 l_approved_amount := l_requested_amt;
2804 ELSE
2805 l_approved_amount := l_number_value;
2806 END IF;
2807
2808 -- set approval amount to workflow so notificaiton gets the correct amount
2809 wf_engine.setitemattrnumber (
2810 itemtype=> itemtype,
2811 itemkey=> itemkey,
2812 aname => 'AMS_AMOUNT',
2813 avalue=> l_approved_amount
2814 );
2815 END IF;
2816 -- End of fix for bug#2577792
2817
2818 -- mpande 6/11/2002 bug#2352621
2819 ELSIF l_update_status = 'REJECTED' THEN
2820 l_next_status_id :=
2821 wf_engine.getitemattrnumber (
2822 itemtype=> itemtype,
2823 itemkey=> itemkey,
2824 aname => 'AMS_REJECT_STAT_ID'
2825 );
2826 -- yzhao 6/28/2002 bug#2352621 revert status
2827 ELSE
2828 l_next_status_id :=
2829 ozf_utility_pvt.get_default_user_status(
2830 g_budget_source_status
2831 , 'NEW');
2832 END IF;
2833
2834
2835 l_child_transfer_flag :=
2836 wf_engine.getitemattrtext(
2837 itemtype => itemtype
2838 ,itemkey => itemkey
2839 ,aname => 'AMS_GENERIC_FLAG');
2840
2841 l_note := wf_engine.getitemattrtext(
2842 itemtype => itemtype
2843 ,itemkey => itemkey
2844 ,aname => 'APPROVAL_NOTE');
2845
2846 l_object_version_number :=
2847 wf_engine.getitemattrnumber(
2848 itemtype => itemtype
2849 ,itemkey => itemkey
2850 ,aname => 'AMS_OBJECT_VERSION_NUMBER');
2851 l_act_budget_id :=
2852 wf_engine.getitemattrnumber(
2853 itemtype => itemtype
2854 ,itemkey => itemkey
2855 ,aname => 'AMS_ACTIVITY_ID');
2856 OPEN c_act_budget_rec(l_act_budget_id);
2857 FETCH c_act_budget_rec INTO l_approval_fm_id, l_approval_for_id, l_to_currency, l_from_currency;
2858 IF (c_act_budget_rec%NOTFOUND) THEN
2859 CLOSE c_act_budget_rec;
2860
2861 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2862 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2863 fnd_msg_pub.add;
2864 END IF;
2865
2866 RAISE fnd_api.g_exc_error;
2867 END IF;
2868 CLOSE c_act_budget_rec;
2869
2870 -- [BEGIN OF BUG 2750841(same as 2741039) FIXING by yzhao 01/10/2003]
2871 -- get source budget's org_id so workflow resumes requestor's responsibility
2872 l_org_id := find_org_id (l_approval_for_id);
2873 -- set org_context since workflow mailer does not set the context
2874 set_org_ctx (l_org_id);
2875 -- [END OF BUG 2750841(same as 2741039) FIXING by yzhao 01/10/2003]
2876
2877 IF l_to_currency <> l_from_currency THEN
2878 -- 08/16/2001 yzhao: convert the request amount to source fund's(approver) currency.
2879 ozf_utility_pvt.convert_currency (
2880 x_return_status=> l_return_status
2881 ,p_from_currency=> l_to_currency
2882 ,p_to_currency=> l_from_currency
2883 ,p_from_amount=> l_approved_amount
2884 ,x_to_amount=> l_approved_amt_in_from_curr
2885 );
2886
2887 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2888 RAISE fnd_api.g_exc_unexpected_error;
2889 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
2890 RAISE fnd_api.g_exc_error;
2891 END IF;
2892
2893 ELSE
2894 l_approved_amt_in_from_curr := l_approved_amount;
2895 END IF;
2896
2897 -- x_return_status := fnd_api.g_ret_sts_success;
2898 l_status_code := ozf_utility_pvt.get_system_status_code(l_next_status_id);
2899
2900 IF l_update_status = 'APPROVED' THEN
2901 approve_request(
2902 p_commit => fnd_api.g_false
2903 ,p_update_status => 'Y'
2904 ,p_act_budget_id => l_act_budget_id
2905 ,p_target_fund_id => l_approval_for_id
2906 ,p_source_fund_id => l_approval_fm_id
2907 ,p_requester_id => l_requester_id
2908 ,p_requestor_owner => 'N'
2909 ,p_approver_id => l_approver_id
2910 -- ,p_requested_amount => l_fm_curr_requested_amount -- should be passed transferring fm fund_currency
2911 ,p_approved_amount => l_approved_amt_in_from_curr -- in transferring fm fund_currency
2912 ,p_child_flag => l_child_transfer_flag
2913 ,p_workflow_flag => 'Y'
2914 ,x_return_status => l_return_status
2915 ,x_msg_count => l_msg_count
2916 ,x_msg_data => l_msg_data
2917 ,p_note => l_note);
2918 ELSE
2919 -- 6/14/2002 mpande changed for implementaion of ENH#2352621
2920 negative_request(
2921 p_commit => fnd_api.g_false
2922 ,p_act_budget_id => l_act_budget_id
2923 ,p_target_fund_id => l_approval_for_id
2924 ,p_source_fund_id => l_approval_fm_id
2925 ,p_requester_id => l_requester_id
2926 ,p_requestor_owner => 'N'
2927 ,p_approver_id => l_approver_id
2928 -- ,p_requested_amount => l_fm_curr_requested_amount -- should be passed transferring fm fund_currency
2929 ,p_approved_amount => l_approved_amt_in_from_curr -- in transferring fm fund_currency
2930 ,p_child_flag => l_child_transfer_flag
2931 ,p_note => l_note
2932 ,p_status_code => l_status_code
2933 ,p_user_status_id => l_next_status_id
2934 ,x_return_status => l_return_status
2935 ,x_msg_count => l_msg_count
2936 ,x_msg_data => l_msg_data);
2937 END IF;
2938
2939 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2940 ams_gen_approval_pvt.handle_err(
2941 p_itemtype => itemtype
2942 ,p_itemkey => itemkey
2943 ,p_msg_count => l_msg_count
2944 , -- Number of error Messages
2945 p_msg_data => l_msg_data
2946 ,p_attr_name => 'AMS_ERROR_MSG'
2947 ,x_error_msg => l_error_msg);
2948 -- mpande 6/11/2002 bug#2352621
2949 resultout := 'COMPLETE:ERROR';
2950 ELSE
2951 resultout := 'COMPLETE:SUCCESS';
2952 END IF;
2953
2954 END IF;
2955
2956 -- CANCEL mode
2957 --
2958 IF (funcmode = 'CANCEL') THEN
2959 resultout := 'COMPLETE:';
2960 RETURN;
2961 END IF;
2962
2963 --
2964 -- TIMEOUT mode
2965 --
2966 IF (funcmode = 'TIMEOUT') THEN
2967 resultout := 'COMPLETE:';
2968 RETURN;
2969 END IF;
2970
2971 fnd_msg_pub.count_and_get(
2972 p_encoded => fnd_api.g_false
2973 ,p_count => l_msg_count
2974 ,p_data => l_msg_data);
2975 IF G_DEBUG THEN
2976 ozf_utility_pvt.debug_message(l_full_name || ': l_return_status' || l_return_status);
2977 END IF;
2978 EXCEPTION
2979 WHEN OTHERS THEN
2980 -- x_return_status := fnd_api.g_ret_sts_error;
2981 fnd_msg_pub.count_and_get(
2982 p_encoded => fnd_api.g_false
2983 ,p_count => l_msg_count
2984 ,p_data => l_msg_data);
2985 ams_gen_approval_pvt.handle_err(
2986 p_itemtype => itemtype
2987 ,p_itemkey => itemkey
2988 ,p_msg_count => l_msg_count
2989 , -- Number of error Messages
2990 p_msg_data => l_msg_data
2991 ,p_attr_name => 'AMS_ERROR_MSG'
2992 ,x_error_msg => l_error_msg);
2993 resultout := 'COMPLETE:ERROR';
2994 RAISE;
2995 END update_budgettrans_status;
2996
2997 ---------------------------------------------------------------------
2998 -- PROCEDURE
2999 -- Approve_holdback
3000 --
3001 --
3002 -- PURPOSE
3003 -- This Procedure will Update the fund where reserve is done to holdback from
3004 -- available amount
3005 --
3006 -- IN
3007 -- p_commit IN VARCHAR2 -- Transaction commit identifier
3008 -- p_act_budget_id IN NUMBER -- Fund request identifier having the request details
3009 -- p_transac_fund_id IN NUMBER -- transaction fund
3010 -- p_requester_id IN NUMBER -- Person initiating the fund release --should always be the owner of the fund
3011 -- p_requested_amount IN NUMBER -- Requested amount
3012 -- p_transfer_type In VARCHAR2
3013 -- OUT
3014 -- x_return_status OUT VARCHAR2
3015 -- x_msg_count OUT NUMBER
3016 -- x_msg_data OUT VARCHAR2
3017 --
3018 -- Used By Activities
3019 --
3020 -- NOTES
3021 --
3022 --
3023 --
3024 -- HISTORY
3025 -- 06/07/2000 MPANDE CREATION
3026 --
3027 -- End of Comments
3028
3029 PROCEDURE approve_holdback(
3030 p_commit IN VARCHAR2 := fnd_api.g_false
3031 ,p_act_budget_id IN NUMBER
3032 ,p_transfer_type IN VARCHAR2
3033 ,p_transac_fund_id IN NUMBER
3034 ,p_requester_id IN NUMBER
3035 ,p_approver_id IN NUMBER
3036 ,p_requested_amount IN NUMBER
3037 ,x_return_status OUT NOCOPY VARCHAR2
3038 ,x_msg_count OUT NOCOPY NUMBER
3039 ,x_msg_data OUT NOCOPY VARCHAR2)
3040 IS
3041 -- Local variables
3042 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Holdback';
3043 l_full_name CONSTANT VARCHAR2(60)
3044 := g_pkg_name || '.' || l_api_name;
3045 l_api_version CONSTANT NUMBER := 1.0;
3046 l_msg_count NUMBER;
3047 l_msg_data VARCHAR2(4000);
3048 l_return_status VARCHAR2(1)
3049 := fnd_api.g_ret_sts_success;
3050 l_object_version_number NUMBER;
3051 -- Record variables for creating the fund request.
3052 l_fund_rec ozf_funds_pvt.fund_rec_type; -- transaction fund record
3053 l_act_budget_rec ozf_actbudgets_pvt.act_budgets_rec_type; -- fund request record
3054
3055 -- Cursor to find fund details
3056 -- sql repository 14894880
3057 CURSOR c_fund_detail(
3058 cv_fund_id NUMBER)
3059 IS
3060 SELECT fund_id
3061 ,(NVL(original_budget, 0) - NVL(holdback_amt, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0)- NVL(committed_amt,0)) available_budget
3062 ,holdback_amt
3063 ,object_version_number
3064 FROM ozf_funds_all_b
3065 WHERE fund_id = cv_fund_id;
3066
3067 l_holdback_amt NUMBER;
3068 l_available_amt NUMBER;
3069 -- cr_transac_detail c_fund_detail%ROWTYPE;
3070
3071 l_obj_number NUMBER;
3072 l_fund_id NUMBER;
3073
3074 -- Cursor to find fund_request details
3075 CURSOR c_request_detail(
3076 p_act_budget_id IN NUMBER)
3077 IS
3078 SELECT activity_budget_id
3079 ,status_code
3080 ,object_version_number
3081 FROM ozf_act_budgets
3082 WHERE activity_budget_id = p_act_budget_id;
3083
3084 -- Cursor records
3085 l_request_status VARCHAR2(10);
3086 l_request_id NUMBER;
3087 l_req_user_status_id NUMBER;
3088 l_req_object_version_number NUMBER;
3089 BEGIN
3090 x_return_status := fnd_api.g_ret_sts_success;
3091 -- Initialize
3092 IF G_DEBUG THEN
3093 ozf_utility_pvt.debug_message(l_full_name || ': start');
3094 END IF;
3095 -- Source Fund Details
3096 OPEN c_fund_detail(p_transac_fund_id);
3097 FETCH c_fund_detail INTO l_fund_id, l_available_amt, l_holdback_amt, l_obj_number;
3098
3099 IF (c_fund_detail%NOTFOUND) THEN
3100 CLOSE c_fund_detail;
3101
3102 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3103 fnd_message.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
3104 fnd_msg_pub.add;
3105 END IF;
3106
3107 RAISE fnd_api.g_exc_error;
3108 END IF;
3109
3110 CLOSE c_fund_detail;
3111 -- Check to see whether the fund has sufficient available amount to reserve/release
3112 -- Initialize the fund records
3113 ozf_funds_pvt.init_fund_rec(x_fund_rec => l_fund_rec);
3114
3115 IF p_transfer_type = 'RESERVE' THEN
3116 IF NVL(l_available_amt, 0) < NVL(p_requested_amount, 0) THEN
3117 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3118 fnd_message.set_name('OZF', 'OZF_AMT_UNAVAILABLE');
3119 fnd_msg_pub.add;
3120 END IF;
3121
3122 RAISE fnd_api.g_exc_error;
3123 END IF;
3124
3125 l_fund_rec.holdback_amt := NVL(l_holdback_amt, 0) + NVL(p_requested_amount, 0); -- HOLDBACK AMT
3126 ELSIF p_transfer_type = 'RELEASE' THEN
3127 IF NVL(l_holdback_amt, 0) < NVL(p_requested_amount, 0) THEN
3128 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3129 fnd_message.set_name('OZF', 'OZF_AMT_UNAVAILABLE');
3130 fnd_msg_pub.add;
3131 END IF;
3132
3133 RAISE fnd_api.g_exc_error;
3134 END IF;
3135
3136 l_fund_rec.holdback_amt := NVL(l_holdback_amt, 0) - NVL(p_requested_amount, 0); -- HOLDBACK AMT
3137 END IF;
3138
3139 IF G_DEBUG THEN
3140 ozf_utility_pvt.debug_message(l_full_name || l_fund_id || l_obj_number);
3141 END IF;
3142 -- Transaction Fund record
3143 l_fund_rec.fund_id := l_fund_id;
3144 l_fund_rec.object_version_number := l_obj_number;
3145 -- Update source fund
3146 ozf_funds_pvt.update_fund(
3147 p_api_version => l_api_version
3148 ,p_commit => fnd_api.g_false
3149 ,x_return_status => l_return_status
3150 ,x_msg_count => l_msg_count
3151 ,x_msg_data => l_msg_data
3152 ,p_fund_rec => l_fund_rec
3153 ,p_mode => 'ADJUST');
3154
3155 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3156 RAISE fnd_api.g_exc_unexpected_error;
3157 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3158 RAISE fnd_api.g_exc_error;
3159 END IF;
3160
3161 -- Conditional commit
3162 IF fnd_api.to_boolean(p_commit)
3163 AND l_return_status = fnd_api.g_ret_sts_success THEN
3164 COMMIT WORK;
3165 END IF;
3166
3167 fnd_msg_pub.count_and_get(
3168 p_encoded => fnd_api.g_false
3169 ,p_count => x_msg_count
3170 ,p_data => x_msg_data);
3171 EXCEPTION
3172 WHEN fnd_api.g_exc_error THEN
3173 x_return_status := fnd_api.g_ret_sts_error;
3174 fnd_msg_pub.count_and_get(
3175 p_encoded => fnd_api.g_false
3176 ,p_count => x_msg_count
3177 ,p_data => x_msg_data);
3178 WHEN fnd_api.g_exc_unexpected_error THEN
3179 x_return_status := fnd_api.g_ret_sts_unexp_error;
3180 fnd_msg_pub.count_and_get(
3181 p_encoded => fnd_api.g_false
3182 ,p_count => x_msg_count
3183 ,p_data => x_msg_data);
3184 WHEN OTHERS THEN
3185 x_return_status := fnd_api.g_ret_sts_unexp_error;
3186
3187 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3188 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3189 END IF;
3190
3191 fnd_msg_pub.count_and_get(
3192 p_encoded => fnd_api.g_false
3193 ,p_count => x_msg_count
3194 ,p_data => x_msg_data);
3195 END approve_holdback;
3196 END ozf_fund_request_apr_pvt;