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