[Home] [Help]
PACKAGE BODY: APPS.OZF_BUDGETAPPROVAL_PVT
Source
1 PACKAGE BODY OZF_BudgetApproval_PVT as
2 /*$Header: ozfvbdab.pls 120.9 2008/04/24 09:27:24 nirprasa ship $*/
3 -- NAME
4 -- OZF_BudgetApproval_PVT
5 --
6 -- HISTORY
7 -- 04/12/2000 sugupta CREATED
8 -- 05/17/2001 mpande UPDATED to pass user_status_id
9 -- 01/12/2001 mpande UPDATED for Note
10 -- 01/16/2002 feliu add vendor notification.
11 -- 01/30/2002 feliu fix bug 2205213.
12 -- 02/05/2002 feliu changed query for partner name.
13 -- 02/21/2002 feliu fixed bug 2231003.
14 -- 10/28/2002 feliu Change for 11.5.9
15 -- 10/28/2002 feliu added budget_request_approval for non_approval budget request.
16 -- 05/09/2003 feliu use bind variable for dynamic sql.
17 -- 12/14/2003 kdass changed table name from ams_temp_eligibility to ozf_temp_eligibility
18 -- 01/29/2004 kdass fix bug 3402233 -- removed the check for debug level for the messages.
19 -- 02/03/2004 kdass fix bug 3380548 -- added new procedure revert_approved_request
20 -- 02/12/2004 kdass fix bug 3436425 -- removed raise and exit statements from conc_validate_offer_budget
21 -- 02/23/2004 kdass fix bug 3457111 -- modified the cursor query c_check_items in check_product_market_strict
22 -- 06/08/2004 Ribha Fix Bug 3661777 -- modified the notification message OZF_OFFER_VALIDATION_MESSAGE to include budget and offer names
23 -- 12/06/2004 feliu fix bug 4032040.
24 -- 03/16/2005 feliu change size from 50 to 240 to fix issue 2 in bug 4240968
25 -- 08/31/2005 kdass fixed bug 4338544
26 -- 12/05/2005 kdass fixed bug 4662453
27 -- 12/09/2005 kdass bug 4870218 - SQL Repository fixes
28 -- 04/24/2008 nirprasa bug 6995376 - SD offer issue.
29
30 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'OZF_BudgetApproval_PVT';
31 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvbdab.pls';
32 g_cons_fund_mode CONSTANT VARCHAR2(30) := 'WORKFLOW';
33 g_status_type CONSTANT VARCHAR2(30) := 'OZF_BUDGETSOURCE_STATUS';
34 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
35
36 -------------------------------------------------------------------
37 -- NAME
38 -- Approve_ActBudget
39 -- PURPOSE
40 -- Called by the money owner to approve the
41 -- requested budget amount. The API is called
42 -- from Workflow.
43
44 PROCEDURE Approve_ActBudget (
45 p_api_version IN NUMBER,
46 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
47 p_commit IN VARCHAR2 := FND_API.G_FALSE,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2,
52
53 p_activity_budget_id IN NUMBER,
54 p_approver_id IN NUMBER,
55 p_approved_amount IN NUMBER,
56 p_approved_currency IN VARCHAR2,
57 -- 11/12/2001 mpande added the following
58 p_comment IN VARCHAR2 := NULL
59
60 );
61
62 -------------------------------------------------------------------
63 -- NAME
64 -- Reject_ActBudget
65 -- PURPOSE
66 -- Called by the money owner to reject the
67 -- requested budget amount. The API is called
68 -- from Workflow.
69 PROCEDURE Reject_ActBudget (
70 p_api_version IN NUMBER,
71 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
72 p_commit IN VARCHAR2 := FND_API.G_FALSE,
73 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2,
77
78 p_activity_budget_id IN NUMBER,
79 p_approver_id IN NUMBER,
80 -- 11/12/2001 mpande added the following
81 p_comment IN VARCHAR2 := NULL
82
83 );
84
85 --------------------------------------------------------------------------
86 -- yzhao: internal procedure called by wf_respond() to fix bug 2750841(same as 2741039)
87 --------------------------------------------------------------------------
88 FUNCTION find_org_id (p_actbudget_id IN NUMBER) RETURN number IS
89 l_org_id number := NULL;
90
91 CURSOR get_fund_org_csr(p_id in number) IS
92 SELECT org_id
93 FROM ozf_funds_all_b
94 WHERE fund_id = (SELECT budget_source_id FROM ozf_act_budgets
95 WHERE activity_budget_id = p_id);
96
97 BEGIN
98
99 OPEN get_fund_org_csr(p_actbudget_id);
100 FETCH get_fund_org_csr INTO l_org_id;
101 CLOSE get_fund_org_csr;
102
103 RETURN l_org_id;
104 END find_org_id;
105 --------------------------------------------------------------------------
106 -- yzhao: internal procedure called by wf_respond() to fix bug 2750841(same as 2741039)
107 --------------------------------------------------------------------------
108 PROCEDURE set_org_ctx (p_org_id IN NUMBER) IS
109 BEGIN
110
111 IF p_org_id is not NULL THEN
112 fnd_client_info.set_org_context(to_char(p_org_id));
113 END IF;
114
115 END set_org_ctx;
116 --------------------------------------------------------------------------
117
118
119 -------------------------------------------------------------------
120 -- NAME
121 -- WF_Respond
122 -- PURPOSE
123 -- Interface for Workflow to communicate the approver's
124 -- response to the request for money.
125 -- HISTORY
126 -- 12-Sep-2000 choang Created.
127 PROCEDURE WF_Respond (
128 p_api_version IN NUMBER,
129 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
130 p_commit IN VARCHAR2 := FND_API.G_FALSE,
131 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
132 x_return_status OUT NOCOPY VARCHAR2,
133 x_msg_count OUT NOCOPY NUMBER,
134 x_msg_data OUT NOCOPY VARCHAR2,
135
136 p_respond_status_id IN VARCHAR2,
137 p_activity_budget_id IN NUMBER,
138 p_approver_id IN NUMBER := NULL,
139 p_approved_amount IN NUMBER := NULL,
140 p_approved_currency IN VARCHAR2 := NULL,
141 -- 11/12/2001 mpande added the following
142 p_comment IN VARCHAR2 := NULL
143 )
144 IS
145 l_api_name CONSTANT VARCHAR2(30) := 'WF_Respond';
146 l_api_version CONSTANT NUMBER := 1.0;
147
148 l_status_code VARCHAR2(30);
149 l_act_budget_rec OZF_ActBudgets_PVT.Act_Budgets_Rec_Type;
150 l_org_id NUMBER;
151
152 CURSOR c_status_code (p_status_id IN NUMBER) IS
153 SELECT system_status_code
154 FROM ams_user_statuses_vl
155 WHERE user_status_id = p_status_id;
156 BEGIN
157 -- Standard call to check for call compatibility.
158 IF NOT FND_API.Compatible_API_Call ( l_api_version,
159 p_api_version,
160 l_api_name,
161 G_PACKAGE_NAME)
162 THEN
163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
164 END IF;
165 -- Initialize message list if p_init_msg_list is set to TRUE.
166 IF FND_API.to_Boolean( p_init_msg_list ) THEN
167 FND_MSG_PUB.initialize;
168 END IF;
169 -- Initialize API return status to success
170 x_return_status := FND_API.G_RET_STS_SUCCESS;
171
172 OPEN c_status_code (p_respond_status_id);
173 FETCH c_status_code INTO l_status_code;
174 CLOSE c_status_code;
175
176 -- [BEGIN OF BUG 2750841(same as 2741039) FIXING by yzhao 01/10/2003]
177 -- get budget's org_id so workflow resumes requestor's responsibility
178 l_org_id := find_org_id (p_activity_budget_id);
179 -- set org_context since workflow mailer does not set the context
180 set_org_ctx (l_org_id);
181 -- [END OF BUG 2750841(same as 2741039) FIXING by yzhao 01/10/2003]
182
183 IF l_status_code = 'APPROVED' THEN
184 Approve_ActBudget (
185 p_api_version => 1.0,
186 p_init_msg_list => FND_API.g_false,
187 p_commit => FND_API.g_false,
188 p_validation_level => p_validation_level,
189 x_return_status => x_return_status,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data,
192
193 p_activity_budget_id => p_activity_budget_id,
194 p_approver_id => p_approver_id,
195 p_approved_amount => p_approved_amount,
196 p_approved_currency => p_approved_currency,
197 -- 11/12/2001 mpande added the following
198 p_comment => p_comment
199 );
200 ELSIF l_status_code = 'REJECTED' THEN
201 Reject_ActBudget (
202 p_api_version => 1.0,
203 p_init_msg_list => FND_API.g_false,
204 p_commit => FND_API.g_false,
205 p_validation_level => p_validation_level,
206 x_return_status => x_return_status,
207 x_msg_count => x_msg_count,
208 x_msg_data => x_msg_data,
209
210 p_activity_budget_id => p_activity_budget_id,
211 p_approver_id => p_approver_id,
212 -- 11/12/2001 mpande added the following
213 p_comment => p_comment
214 );
215 ELSE
216 -- an error has occurred during the Workflow
217 -- process, so revert the status to NEW -- rely
218 -- on WF to generate a notification.
219 OZF_ActBudgets_PVT.Init_Act_Budgets_Rec (l_act_budget_rec);
220 l_act_budget_rec.activity_budget_id := p_activity_budget_id;
221 l_act_budget_rec.status_code := 'NEW';
222 l_act_budget_rec.user_status_id := ozf_utility_pvt.get_default_user_status(g_status_type, l_act_budget_rec.status_code);
223 -- 11/12/2001 mpande added the following
224 l_act_budget_rec.comment := p_comment ;
225 OZF_ActBudgets_PVT.Update_Act_Budgets (
226 p_api_version => 1.0,
227 p_init_msg_list => FND_API.g_false,
228 p_commit => FND_API.g_false,
229 p_validation_level => p_validation_level,
230 x_return_status => x_return_status,
231 x_msg_count => x_msg_count,
232 x_msg_data => x_msg_data,
233 p_act_budgets_rec => l_act_budget_rec
234 );
235 END IF;
236
237 IF (p_commit = FND_API.g_true) THEN
238 COMMIT WORK;
239 END IF;
240 END WF_Respond;
241
242
243 -------------------------------------------------------------------
244 -- NAME
245 -- Approve_ActBudget
246 -- PURPOSE
247 -- Called by the money owner to approve the
248 -- requested budget amount. The API is called
249 -- from Workflow.
250 -- HISTORY
251 -- 16-Aug-2000 choang Created.
252 PROCEDURE Approve_ActBudget (
253 p_api_version IN NUMBER,
254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
255 p_commit IN VARCHAR2 := FND_API.G_FALSE,
256 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2,
260
261 p_activity_budget_id IN NUMBER,
262 p_approver_id IN NUMBER,
263 p_approved_amount IN NUMBER,
264 p_approved_currency IN VARCHAR2,
265 -- 11/12/2001 mpande added the following
266 p_comment IN VARCHAR2 := NULL
267 )
268 IS
269 l_act_budgets_rec OZF_ActBudgets_PVT.Act_Budgets_Rec_Type;
270 BEGIN
271 OZF_ActBudgets_PVT.Init_Act_Budgets_Rec (l_act_budgets_rec);
272 l_act_budgets_rec.activity_budget_id := p_activity_budget_id;
273 l_act_budgets_rec.status_code := 'APPROVED';
274 --05/17/2001 mpande
275 l_act_budgets_rec.user_status_id := ozf_utility_pvt.get_default_user_status(g_status_type, l_act_budgets_rec.status_code);
276 l_act_budgets_rec.approver_id := p_approver_id;
277 l_act_budgets_rec.approved_in_currency := p_approved_currency;
278 l_act_budgets_rec.approved_original_amount := p_approved_amount;
279 -- 11/12/2001 mpande added the following
280 l_act_budgets_rec.comment := p_comment ;
281 OZF_ActBudgets_PVT.Update_Act_Budgets (
282 p_api_version => 1.0,
283 p_init_msg_list => p_init_msg_list,
284 p_commit => p_commit,
285 p_validation_level => p_validation_level,
286 x_return_status => x_return_status,
287 x_msg_count => x_msg_count,
288 x_msg_data => x_msg_data,
289 p_act_budgets_rec => l_act_budgets_rec
290 );
291 END Approve_ActBudget;
292
293 -------------------------------------------------------------------
294 -- NAME
295 -- Reject_ActBudget
296 -- PURPOSE
297 -- Called by the money owner to reject the
298 -- requested budget amount. The API is called
299 -- from Workflow.
300 -- HISTORY
301 -- 16-Aug-2000 choang Created.
302 PROCEDURE Reject_ActBudget (
303 p_api_version IN NUMBER,
304 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
305 p_commit IN VARCHAR2 := FND_API.G_FALSE,
306 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
307 x_return_status OUT NOCOPY VARCHAR2,
308 x_msg_count OUT NOCOPY NUMBER,
309 x_msg_data OUT NOCOPY VARCHAR2,
310
311 p_activity_budget_id IN NUMBER,
312 p_approver_id IN NUMBER,
313 -- 11/12/2001 mpande added the following
314 p_comment IN VARCHAR2 := NULL
315 )
316 IS
317 l_act_budgets_rec OZF_ActBudgets_PVT.Act_Budgets_Rec_Type;
318 -- add by feliu on 02/24/04 for soft fund. when request is rejected, the soft fund
319 -- approval should be revoke.
320 l_request_header_id NUMBER;
321
322 CURSOR c_req_rec (p_act_budget_id IN NUMBER )IS
323 SELECT req.request_header_id
324 FROM ozf_request_headers_all_b req, ozf_act_budgets act
325 WHERE req.offer_id = act.act_budget_used_by_id
326 AND act.activity_budget_id = p_act_budget_id;
327
328 BEGIN
329 OPEN c_req_rec(p_activity_budget_id);
330 FETCH c_req_rec INTO l_request_header_id;
331 CLOSE c_req_rec;
332
333 IF l_request_header_id is NOT NULL THEN
334 UPDATE ozf_approval_access
335 SET approval_access_flag = 'Y'
336 WHERE object_type ='SOFT_FUND'
337 AND object_id = l_request_header_id
338 AND approval_level = (SELECT min(approval_level) from ozf_approval_access WHERE object_type ='SOFT_FUND'
339 AND object_id = l_request_header_id );
340 END IF;
341
342 OZF_ActBudgets_PVT.Init_Act_Budgets_Rec (l_act_budgets_rec);
343 l_act_budgets_rec.activity_budget_id := p_activity_budget_id;
344 l_act_budgets_rec.approver_id := p_approver_id;
345 l_act_budgets_rec.status_code := 'REJECTED';
346 --05/17/2001 mpande
347 l_act_budgets_rec.user_status_id := ozf_utility_pvt.get_default_user_status(g_status_type, l_act_budgets_rec.status_code);
348 -- 11/12/2001 mpande added the following
349 l_act_budgets_rec.comment := p_comment ;
350 OZF_ActBudgets_PVT.Update_Act_Budgets (
351 p_api_version => 1.0,
352 p_init_msg_list => p_init_msg_list,
353 p_commit => p_commit,
354 p_validation_level => p_validation_level,
355 x_return_status => x_return_status,
356 x_msg_count => x_msg_count,
357 x_msg_data => x_msg_data,
358 p_act_budgets_rec => l_act_budgets_rec
359 );
360 END Reject_ActBudget;
361
362 -------------------------------------------------------------------
363 -- NAME
364 -- Close_ActBudget
365 -- PURPOSE
366 -- Close the books for the budget source line.
367 PROCEDURE Close_ActBudget (
368 p_api_version IN NUMBER,
369 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
370 p_commit IN VARCHAR2 := FND_API.G_FALSE,
371 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_msg_count OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2,
375
376 p_activity_budget_id IN NUMBER
377 )
378 IS
379 BEGIN
380 null;
381 END Close_ActBudget;
382
383 -------------------------------------------------------------------
384 -- NAME
385 -- Notify vendor
386 -- PURPOSE
387 -- Notify the vendor wheneever a partner creates a budget line
388 -- History
389 -- Created Mpande 01/03/2002
390 ----------------------------------------------------------------
391 PROCEDURE notify_vendor (
392 p_act_budget_rec IN OZF_ACTBUDGETS_PVT.Act_Budgets_Rec_Type,
393 x_return_status OUT NOCOPY VARCHAR2,
394 x_msg_count OUT NOCOPY NUMBER,
395 x_msg_data OUT NOCOPY VARCHAR2)
396 IS
397
398 CURSOR c_camp_owner IS
399 SELECT owner_user_id ,status_code
400 FROM ams_campaigns_all_b
401 WHERE campaign_id = p_act_budget_rec.act_budget_used_by_id;
402
403 CURSOR c_partner_name IS
404 SELECT act.request_amount,NVL(rsc.partner_party_name,'Partner')
405 , NVL(camp.description,''),
406 camp.source_code, TO_CHAR(camp.actual_exec_start_date),TO_CHAR(camp.actual_exec_end_date),camp.campaign_name
407 FROM ozf_act_budgets act, ams_campaigns_vl camp, pv_resource_info_v rsc
408 WHERE act.activity_budget_id = p_act_budget_rec.activity_budget_id
409 AND act.act_budget_used_by_id = camp.campaign_id
410 AND act.requester_Id = rsc.rsc_resource_id(+);
411
412 CURSOR c_approved_amt IS
413 SELECT SUM(approved_amount)
414 FROM ozf_act_budgets
415 WHERE act_budget_used_by_id = p_act_budget_rec.act_budget_used_by_id
416 AND arc_act_budget_used_by = 'CAMP'
417 AND budget_source_type = 'FUND'
418 AND status_code = 'APPROVED';
419
420 l_camp_owner_id NUMBER;
421 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
422 l_notification_id NUMBER;
423 l_camp_status VARCHAR2(30);
424 l_strSubject VARCHAR2(200);
425 l_partner_name VARCHAR2(30);
426 l_campaign_name VARCHAR2(30);
427 l_campaign_id NUMBER;
428 l_source_code VARCHAR2(30);
429 l_request_amt NUMBER;
430 l_approved_amt NUMBER;
431 l_start_date VARCHAR2(30);
432 l_end_date VARCHAR2(30);
433 l_camp_desc VARCHAR2(100);
434 l_strBody VARCHAR2(2000);
435
436 BEGIN
437 OPEN c_camp_owner;
438 FETCH c_camp_owner INTO l_camp_owner_id,l_camp_status;
439 CLOSE c_camp_owner;
440
441 IF l_camp_status = 'ACTIVE' THEN
442
443 OPEN c_partner_name;
444 FETCH c_partner_name INTO l_request_amt, l_partner_name
445 ,l_camp_desc,l_source_code,l_start_date,l_end_date,l_campaign_name;
446 CLOSE c_partner_name;
447
448 OPEN c_approved_amt ;
449 FETCH c_approved_amt INTO l_approved_amt;
450 CLOSE c_approved_amt;
451
452 fnd_message.set_name('OZF', 'OZF_PARTNER_SOURCING_SUBJECT');
453 fnd_message.set_token ('BUDGET_AMT', l_request_amt, FALSE);
454 fnd_message.set_token ('PARTNER_NAME', l_partner_name, FALSE);
455 l_strSubject := Substr(fnd_message.get,1,200);
456
457 fnd_message.set_name('OZF', 'OZF_NOTIFY_HEADERLINE');
458 l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10);
459 fnd_message.set_name ('OZF', 'OZF_VENDOR_MESSAGE');
460 fnd_message.set_token ('PARTNER_NAME', l_partner_name, FALSE);
461 fnd_message.set_token ('CAMP_NUMBER', l_source_code, FALSE);
462 fnd_message.set_token ('CAMP_NAME', l_campaign_name, FALSE);
463 fnd_message.set_token ('CAMP_DESC', l_camp_desc, FALSE);
464 fnd_message.set_token ('START_DATE', l_start_date, FALSE);
465 fnd_message.set_token ('END_DATE', l_end_date, FALSE);
466 fnd_message.set_token ('REQUEST_AMT', l_request_amt, FALSE);
467 fnd_message.set_token ('APPROVED_AMT', l_approved_amt, FALSE);
468 l_strBody := l_strBody || Substr(fnd_message.get,1,1000);
469
470 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
471 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
472
473 ozf_utility_pvt.send_wf_standalone_message(
474 p_subject => l_strSubject
475 ,p_body => l_strBody
476 ,p_send_to_res_id => l_camp_owner_id
477 ,x_notif_id => l_notification_id
478 ,x_return_status => l_return_status
479 );
480
481 IF l_return_status <> fnd_api.g_ret_sts_success THEN
482 RAISE fnd_api.g_exc_error;
483 END IF;
484
485 END IF;
486 EXCEPTION
487 WHEN OTHERS THEN
488 x_return_status := fnd_api.g_ret_sts_error;
489 END notify_vendor;
490
491
492 /* zy: print whole string. For debug only. Remove them when done
493 PROCEDURE zy_print( p_str VARCHAR2) IS
494 l_int NUMBER := 1;
495 l_len NUMBER;
496 BEGIN
497 l_len := length(p_str);
498 WHILE l_int <= l_len LOOP
499 dbms_output.put_line(substr(p_str, l_int, 250));
500 l_int := l_int + 250;
501 END LOOP;
502 END;
503 */
504
505
506 -------------------------------------------------------------------
507 -- NAME
508 -- revert_approved_request
509 -- PURPOSE
510 -- reverts all the approved budget lines for an offer in case the
511 -- relaxed product/customer validation fails
512 -- History
513 -- Created kdass 02-Feb-2004
514 -- changed by feliu 08/05/2005
515 ----------------------------------------------------------------
516 PROCEDURE revert_approved_request (
517 p_offer_id IN NUMBER, -- offer id
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_msg_count OUT NOCOPY NUMBER,
520 x_msg_data OUT NOCOPY VARCHAR2)
521 IS
522
523 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
524 l_fund_rec ozf_funds_pvt.fund_rec_type;
525 l_activity_budget_id NUMBER;
526 l_budget_source_id NUMBER;
527 l_committed_amt NUMBER;
528 l_return_status VARCHAR2(20);
529 l_msg_data VARCHAR2(2000) := NULL;
530 l_msg_count NUMBER;
531 l_object_version_number NUMBER;
532 -- l_util_object_version NUMBER;
533 -- l_utilization_id NUMBER;
534
535 --kdass 09-DEC-2005 bug 4870218 - SQL ID# 14892067
536 -- get all the approved budget lines for an offer
537 CURSOR c_get_actbudgets IS
538 SELECT act.activity_budget_id, act.budget_source_id,
539 (fund.committed_amt - act.request_amount) committed_amt,
540 fund.object_version_number
541 FROM ozf_act_budgets act, ozf_funds_all_b fund
542 WHERE act.arc_act_budget_used_by = 'OFFR'
543 AND act.act_budget_used_by_id = p_offer_id
544 AND act.transfer_type = 'REQUEST'
545 AND act.status_code = 'APPROVED'
546 AND act.budget_source_id = fund.fund_id;
547
548 /*
549 SELECT act.activity_budget_id, act.budget_source_id,
550 (fund.committed_amt - act.request_amount) committed_amt,
551 fund.object_version_number, util.object_version_number util_object_version,
552 util.utilization_id
553 FROM ozf_act_budgets act, ozf_fund_details_v fund, ozf_funds_utilized_all_b util
554 WHERE act.arc_act_budget_used_by = 'OFFR'
555 AND act.act_budget_used_by_id = p_offer_id
556 AND act.transfer_type = 'REQUEST'
557 AND act.status_code = 'APPROVED'
558 AND act.budget_source_id = fund.fund_id
559 AND act.activity_budget_id = util.ams_activity_budget_id;
560 */
561 BEGIN
562
563 SAVEPOINT revert_approved_request;
564
565 OPEN c_get_actbudgets;
566 LOOP
567 FETCH c_get_actbudgets INTO l_activity_budget_id, l_budget_source_id, l_committed_amt,
568 l_object_version_number;
569 EXIT WHEN c_get_actbudgets%NOTFOUND OR c_get_actbudgets%NOTFOUND is NULL;
570
571 l_fund_rec.fund_id := l_budget_source_id;
572 l_fund_rec.committed_amt := l_committed_amt;
573 l_fund_rec.object_version_number := l_object_version_number;
574
575 -- reduce the committed amount of the budget
576 ozf_funds_pvt.update_fund( p_api_version => 1.0
577 ,p_init_msg_list => fnd_api.g_false
578 ,p_commit => fnd_api.g_false
579 ,p_validation_level => fnd_api.g_valid_level_full
580 ,x_return_status => l_return_status
581 ,x_msg_count => l_msg_count
582 ,x_msg_data => l_msg_data
583 ,p_fund_rec => l_fund_rec
584 ,p_mode => jtf_plsql_api.g_update
585 );
586
587 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
588 x_return_status := l_return_status;
589 RAISE fnd_api.G_EXC_ERROR;
590 END IF;
591
592 l_act_budgets_rec.activity_budget_id := l_activity_budget_id;
593 l_act_budgets_rec.status_code := 'NEW';
594 l_act_budgets_rec.approval_date := fnd_api.g_miss_date;
595 l_act_budgets_rec.approver_id := fnd_api.g_miss_num;
596 l_act_budgets_rec.approved_amount := fnd_api.g_miss_num;
597 l_act_budgets_rec.approved_original_amount := fnd_api.g_miss_num;
598 l_act_budgets_rec.approved_in_currency := fnd_api.g_miss_char;
599
600 -- revert the APPROVED budget line to NEW
601 ozf_actbudgets_pvt.update_act_budgets ( p_api_version => 1.0
602 ,p_init_msg_list => fnd_api.g_false
603 ,p_commit => fnd_api.g_false
604 ,p_validation_level => fnd_api.g_valid_level_full
605 ,x_return_status => l_return_status
606 ,x_msg_count => l_msg_count
607 ,x_msg_data => l_msg_data
608 ,p_act_budgets_rec => l_act_budgets_rec);
609
610 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
611 x_return_status := l_return_status;
612 RAISE fnd_api.G_EXC_ERROR;
613 END IF;
614
615 /*
616 -- delete utilization record
617 ozf_fund_utilized_pvt.delete_utilization ( p_api_version => 1.0
618 ,p_init_msg_list => fnd_api.g_false
619 ,p_commit => fnd_api.g_false
620 ,x_return_status => l_return_status
621 ,x_msg_count => l_msg_count
622 ,x_msg_data => l_msg_data
623 ,p_utilization_id => l_utilization_id
624 ,p_object_version => l_util_object_version);
625
626 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
627 x_return_status := l_return_status;
628 RAISE fnd_api.G_EXC_ERROR;
629 END IF;
630 */
631 END LOOP;
632
633 CLOSE c_get_actbudgets;
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 ROLLBACK TO revert_approved_request;
638 x_return_status := fnd_api.g_ret_sts_error;
639 fnd_msg_pub.count_and_get (
640 p_count => x_msg_count
641 , p_data => x_msg_data
642 , p_encoded => fnd_api.g_false
643 );
644
645 END revert_approved_request;
646
647 -------------------------------------------------------------------
648 -- NAME
649 -- check_product_market_strict
650 -- PURPOSE
651 -- private procedure to check for
652 -- any offer's product, if it is not in budget's product list
653 -- any offer's party, if it is not in budget's party list
654 -- any budget's excluded product, if it is in offer's product list
655 -- any budget's excluded party, if it is in offer's party list
656 -- History
657 -- Created kdass 22-Sep-2003 11.5.10 Offer Budget Validation
658 ----------------------------------------------------------------
659 PROCEDURE check_product_market_strict (
660 p_exclude_only IN BOOLEAN,
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_valid_flag OUT NOCOPY VARCHAR2)
663 IS
664 CURSOR c_check_items IS
665 SELECT 1
666 FROM ozf_temp_eligibility offr
667 WHERE object_type = 'OFFR'
668 AND offr.eligibility_id > 0
669 AND (
670 (NOT EXISTS
671 (SELECT 1
672 FROM ozf_temp_eligibility fund
673 WHERE fund.object_type = 'FUND'
674 AND fund.exclude_flag = 'N'
675 AND fund.eligibility_id > 0
676 AND fund.eligibility_id = offr.eligibility_id))
677 OR
678 (EXISTS
679 (SELECT 1
680 FROM ozf_temp_eligibility fund
681 WHERE fund.object_type = 'FUND'
682 AND fund.exclude_flag = 'Y'
683 AND fund.eligibility_id > 0
684 AND fund.eligibility_id = offr.eligibility_id))
685 );
686
687 CURSOR c_check_exclude_items IS
688 SELECT 1
689 FROM ozf_temp_eligibility fund
690 WHERE fund.object_type = 'FUND'
691 AND exclude_flag = 'Y'
692 AND fund.eligibility_id > 0
693 AND EXISTS
694 (SELECT 1
695 FROM ozf_temp_eligibility offr
696 WHERE offr.object_type = 'OFFR'
697 AND offr.eligibility_id > 0
698 AND offr.eligibility_id = fund.eligibility_id);
699
700 l_exist_number NUMBER := NULL;
701
702 BEGIN
703
704 IF p_exclude_only THEN
705 OPEN c_check_exclude_items;
706 FETCH c_check_exclude_items INTO l_exist_number;
707 CLOSE c_check_exclude_items;
708 --dbms_output.put_line('l_exist_number: ' || l_exist_number);
709 IF l_exist_number = 1 THEN
710 x_return_status := fnd_api.g_ret_sts_success;
711 x_valid_flag := fnd_api.g_false;
712 RETURN;
713 END IF;
714 ELSE
715 OPEN c_check_items;
716 FETCH c_check_items INTO l_exist_number;
717 CLOSE c_check_items;
718 ----dbms_output.put_line('l_exist_number: ' || l_exist_number);
719 IF l_exist_number = 1 THEN
720 x_return_status := fnd_api.g_ret_sts_success;
721 x_valid_flag := fnd_api.g_false;
722 RETURN;
723 END IF;
724 END IF;
725 END check_product_market_strict;
726
727
728 -------------------------------------------------------------------
729 -- NAME
730 -- check_product_market_loose
731 -- PURPOSE
732 -- private procedure to check for
733 -- any offer's product, if it is not in budget's product list when relaxed offer budget validation
734 -- any offer's party, if it is not in budget's party list when relaxed offer budget validation
735 -- any budget's excluded product, if it is in offer's product list when relaxed offer budget validation
736 -- any budget's excluded party, if it is in offer's party list when relaxed offer budget validation
737 -- History
738 -- Created kdass 22-Sep-2003 11.5.10 Offer Budget Validation
739 ----------------------------------------------------------------
740 PROCEDURE check_product_market_loose (
741 p_exclude_only IN BOOLEAN,
742 x_return_status OUT NOCOPY VARCHAR2,
743 x_valid_flag OUT NOCOPY VARCHAR2)
744 IS
745 CURSOR c_check_exclude_items IS
746 SELECT 1
747 FROM ozf_temp_eligibility offr
748 WHERE object_type = 'OFFR'
749 AND offr.eligibility_id > 0
750 AND NOT EXISTS
751 (SELECT 1
752 FROM ozf_temp_eligibility fund
753 WHERE offr.eligibility_id = fund.eligibility_id
754 AND fund.object_type = 'FUND'
755 AND fund.eligibility_id > 0
756 AND fund.exclude_flag = 'Y');
757
758 CURSOR c_check_items IS
759 SELECT 1
760 FROM ozf_temp_eligibility offr, ozf_temp_eligibility fund
761 WHERE offr.object_type = 'OFFR'
762 AND fund.eligibility_id > 0
763 AND offr.eligibility_id > 0
764 AND fund.object_type = 'FUND'
765 AND fund.exclude_flag = 'N'
766 AND offr.eligibility_id = fund.eligibility_id;
767
768 l_exist_number NUMBER := NULL;
769
770 BEGIN
771
772 IF p_exclude_only THEN
773 OPEN c_check_exclude_items;
774 FETCH c_check_exclude_items INTO l_exist_number;
775 CLOSE c_check_exclude_items;
776 --dbms_output.put_line('l_exist_number: ' || l_exist_number);
777 IF NVL(l_exist_number,0) <> 1 THEN
778 x_return_status := fnd_api.g_ret_sts_success;
779 x_valid_flag := fnd_api.g_false;
780 RETURN;
781 END IF;
782 ELSE
783 OPEN c_check_items;
784 FETCH c_check_items INTO l_exist_number;
785 CLOSE c_check_items;
786 --dbms_output.put_line('l_exist_number: ' || l_exist_number);
787 IF NVL(l_exist_number,0) <> 1 THEN
788 x_return_status := fnd_api.g_ret_sts_success;
789 x_valid_flag := fnd_api.g_false;
790 RETURN;
791 END IF;
792 END IF;
793
794 END check_product_market_loose;
795
796
797 -------------------------------------------------------------------
798 -- NAME
799 -- denorm_product_for_one_budget
800 -- PURPOSE
801 -- this API will denorm budget's product eligibility to temp table
802 -- p_budget_id: fund_id
803 -- History
804 -- Created yzhao 02/03/2004
805 ----------------------------------------------------------------
806 PROCEDURE denorm_product_for_one_budget (
807 p_budget_id IN NUMBER,
808 x_budget_prod OUT NOCOPY BOOLEAN,
809 x_exclude_prod OUT NOCOPY BOOLEAN,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_msg_count OUT NOCOPY NUMBER,
812 x_msg_data OUT NOCOPY VARCHAR2)
813 IS
814 l_attribute VARCHAR2(50) := NULL;
815 l_attr_value VARCHAR2(200) := NULL;
816 l_excluded_flag VARCHAR2(1);
817 l_temp_sql VARCHAR2(2000) := NULL;
818 l_denorm_csr NUMBER;
819 l_stmt_denorm VARCHAR2(32000) := NULL;
820 l_ignore NUMBER;
821
822 -- get budget's and offer's products
823 CURSOR c_get_products(p_act_product_used_by_id IN NUMBER, p_arc_act_product_used_by IN VARCHAR2, p_excluded_flag IN VARCHAR2) IS
824 SELECT decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
825 ,excluded_flag
826 ,decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
827 FROM ams_act_products
828 WHERE act_product_used_by_id = p_act_product_used_by_id
829 AND arc_act_product_used_by = p_arc_act_product_used_by
830 AND excluded_flag = p_excluded_flag;
831
832 BEGIN
833 x_return_status := fnd_api.G_RET_STS_SUCCESS;
834 x_budget_prod := FALSE;
835 x_exclude_prod := FALSE;
836 SAVEPOINT denorm_product_for_one_budget;
837
838 FND_DSQL.init;
839 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
840 FND_DSQL.add_text('SELECT ''FUND'', ''N'', product_id FROM (');
841 -- Get all product qualifiers for 'FUND'
842 OPEN c_get_products(p_budget_id, 'FUND', 'N');
843 LOOP
844 FETCH c_get_products INTO l_attr_value,l_excluded_flag,l_attribute;
845 EXIT WHEN c_get_products%NOTFOUND OR c_get_products%NOTFOUND is NULL;
846 IF c_get_products%ROWCOUNT > 0 THEN
847 x_budget_prod := TRUE;
848 END IF;
849
850 IF c_get_products%ROWCOUNT = 1 THEN -- for first row.
851 FND_DSQL.add_text('(');
852 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
853 ( p_context => 'ITEM',
854 p_attribute => l_attribute,
855 p_attr_value_from => l_attr_value,
856 p_attr_value_to => NULL,
857 p_comparison => NULL,
858 p_type => 'PROD'
859 );
860 FND_DSQL.add_text(')');
861 ELSE
862 FND_DSQL.add_text('UNION (');
863 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
864 ( p_context => 'ITEM',
865 p_attribute => l_attribute,
866 p_attr_value_from => l_attr_value,
867 p_attr_value_to => NULL,
868 p_comparison => NULL,
869 p_type => 'PROD'
870 );
871 FND_DSQL.add_text(')');
872 END IF;
873
874 END LOOP;
875
876 CLOSE c_get_products;
877 FND_DSQL.add_text(')');
878
879 IF x_budget_prod THEN
880
881 l_denorm_csr := DBMS_SQL.open_cursor;
882 FND_DSQL.set_cursor(l_denorm_csr);
883
884 l_stmt_denorm := FND_DSQL.get_text(FALSE);
885 --dbms_output.put_line('budget query:' || l_stmt_denorm);
886 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
887 FND_DSQL.do_binds;
888 l_ignore := DBMS_SQL.execute(l_denorm_csr);
889 END IF;
890
891 FND_DSQL.init;
892 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
893 FND_DSQL.add_text('SELECT ''FUND'', ''Y'', product_id FROM (');
894 -- for exclude product of FUND.
895
896 OPEN c_get_products(p_budget_id,'FUND','Y');
897 LOOP
898 FETCH c_get_products INTO l_attr_value,l_excluded_flag,l_attribute;
899 EXIT WHEN c_get_products%NOTFOUND OR c_get_products%NOTFOUND is NULL;
900 IF c_get_products%ROWCOUNT > 0 THEN
901 x_exclude_prod := TRUE;
902 END IF;
903
904 IF c_get_products%ROWCOUNT = 1 THEN
905 -- l_exclude_sql := '(' || l_temp_sql || ')';
906 FND_DSQL.add_text('(');
907 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
908 ( p_context => 'ITEM',
909 p_attribute => l_attribute,
910 p_attr_value_from => l_attr_value,
911 p_attr_value_to => NULL,
912 p_comparison => NULL,
913 p_type => 'PROD'
914 );
915 FND_DSQL.add_text(')');
916 ELSE
917 --l_exclude_sql := l_exclude_sql || ' UNION (' || l_temp_sql || ')';
918 FND_DSQL.add_text('UNION (');
919 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
920 ( p_context => 'ITEM',
921 p_attribute => l_attribute,
922 p_attr_value_from => l_attr_value,
923 p_attr_value_to => NULL,
924 p_comparison => NULL,
925 p_type => 'PROD'
926 );
927 FND_DSQL.add_text(')');
928 END IF;
929
930 END LOOP;
931 CLOSE c_get_products;
932 FND_DSQL.add_text(')');
933
934 IF x_exclude_prod THEN
935 l_denorm_csr := DBMS_SQL.open_cursor;
936 FND_DSQL.set_cursor(l_denorm_csr);
937 l_stmt_denorm := FND_DSQL.get_text(FALSE);
938 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
939 FND_DSQL.do_binds;
940 l_ignore := DBMS_SQL.execute(l_denorm_csr);
941 END IF;
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 ROLLBACK TO denorm_product_for_one_budget;
946 x_return_status := fnd_api.g_ret_sts_error;
947 END denorm_product_for_one_budget;
948
949
950 -------------------------------------------------------------------
951 -- NAME
952 -- denorm_market_for_one_budget
953 -- PURPOSE
954 -- this API will denorm budget's market eligibility to temp table
955 -- p_budget_id: fund_id
956 -- History
957 -- Created yzhao 02/03/2004
958 ----------------------------------------------------------------
959 PROCEDURE denorm_market_for_one_budget (
960 p_budget_id IN NUMBER,
961 x_budget_mark OUT NOCOPY BOOLEAN,
962 x_exclude_mark OUT NOCOPY BOOLEAN,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_msg_count OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2)
966 IS
967 l_segment_id NUMBER;
968 l_excluded_flag VARCHAR2(1);
969 l_segment_type VARCHAR2(30);
970 l_context VARCHAR2(50) := NULL;
971 l_attribute VARCHAR2(50) := NULL;
972 l_attr_value VARCHAR2(200) := NULL;
973 l_temp_sql VARCHAR2(2000) := NULL;
974 l_denorm_csr NUMBER;
975 l_ignore NUMBER;
976 l_stmt_denorm VARCHAR2(32000) := NULL;
977
978 -- get budget's included and excluded market qualifier ids
979 CURSOR c_get_budget_market_qualifiers(p_exclude_flag IN VARCHAR2) IS
980 SELECT market_segment_id, segment_type, exclude_flag
981 FROM ams_act_market_segments
982 WHERE act_market_segment_used_by_id = p_budget_id
983 AND arc_act_market_segment_used_by = 'FUND'
984 AND exclude_flag = p_exclude_flag;
985
986 BEGIN
987 x_return_status := fnd_api.G_RET_STS_SUCCESS;
988 x_budget_mark := FALSE;
989 x_exclude_mark := FALSE;
990 SAVEPOINT denorm_market_for_one_budget;
991
992 FND_DSQL.init;
993 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
994 FND_DSQL.add_text('SELECT ''FUND'', ''N'', party_id FROM (');
995
996 OPEN c_get_budget_market_qualifiers('N');
997
998 -- Get all market qualifiers for 'FUND'
999 LOOP
1000 FETCH c_get_budget_market_qualifiers INTO l_segment_id,l_segment_type,l_excluded_flag;
1001 EXIT WHEN c_get_budget_market_qualifiers%NOTFOUND OR c_get_budget_market_qualifiers%NOTFOUND is NULL;
1002
1003 IF c_get_budget_market_qualifiers%ROWCOUNT > 0 THEN
1004 x_budget_mark := TRUE;
1005 END IF;
1006 -- should be the same as how they are created in amsvfrub.pls process_offers()
1007 IF l_segment_type = 'CUSTOMER' THEN
1008 l_context := 'CUSTOMER'; -- for customer sold to
1009 l_attribute := 'QUALIFIER_ATTRIBUTE2';
1010 /* yzhao: 02/07/2003 fix bug 2789518 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY CUSTOMER BILL TO FAILS */
1011 ELSIF l_segment_type = 'CUSTOMER_BILL_TO' THEN
1012 l_context := 'CUSTOMER'; -- for customer bill to
1013 l_attribute := 'QUALIFIER_ATTRIBUTE14';
1014 /* yzhao: 02/07/2003 fix bug 2789518 ends */
1015 ELSIF l_segment_type = 'LIST' THEN
1016 l_context := 'CUSTOMER_GROUP';
1017 l_attribute := 'QUALIFIER_ATTRIBUTE1';
1018 ELSIF l_segment_type = 'SEGMENT' THEN
1019 l_context := 'CUSTOMER_GROUP';
1020 l_attribute := 'QUALIFIER_ATTRIBUTE2';
1021 ELSIF l_segment_type = 'BUYER' THEN
1022 l_context := 'CUSTOMER_GROUP';
1023 l_attribute := 'QUALIFIER_ATTRIBUTE3';
1024 ELSIF l_segment_type = 'TERRITORY' THEN
1025 l_context := 'TERRITORY';
1026 l_attribute := 'QUALIFIER_ATTRIBUTE1';
1027 /* feliu: 04/02/2003 fix bug 2778138 */
1028 ELSIF l_segment_type = 'SHIP_TO' THEN
1029 l_context := 'CUSTOMER';
1030 l_attribute := 'QUALIFIER_ATTRIBUTE11';
1031 END IF;
1032
1033 l_attr_value := l_segment_id;
1034
1035 IF c_get_budget_market_qualifiers%ROWCOUNT = 1 THEN -- for first row.
1036 -- l_budget_product_sql := '(' || l_temp_sql || ')';
1037 FND_DSQL.add_text('(');
1038 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1039 ( p_context => l_context,
1040 p_attribute => l_attribute,
1041 p_attr_value_from => l_attr_value,
1042 p_attr_value_to => NULL,
1043 p_comparison => '=',
1044 p_type => 'ELIG'
1045 );
1046 FND_DSQL.add_text(')');
1047 ELSE
1048 --l_budget_product_sql := l_budget_product_sql || ' UNION (' || l_temp_sql || ')';
1049 FND_DSQL.add_text('UNION (');
1050 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1051 ( p_context => l_context,
1052 p_attribute => l_attribute,
1053 p_attr_value_from => l_attr_value,
1054 p_attr_value_to => NULL,
1055 p_comparison => '=',
1056 p_type => 'ELIG'
1057 );
1058 FND_DSQL.add_text(')');
1059 END IF;
1060 ----dbms_output.put_line('budget:' || l_temp_sql );
1061
1062 END LOOP;
1063 CLOSE c_get_budget_market_qualifiers;
1064 FND_DSQL.add_text(')');
1065
1066 IF x_budget_mark THEN
1067 l_denorm_csr := DBMS_SQL.open_cursor;
1068 FND_DSQL.set_cursor(l_denorm_csr);
1069 l_stmt_denorm := FND_DSQL.get_text(FALSE);
1070 -- l_budget_market_sql := l_stmt_denorm;
1071 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
1072 FND_DSQL.do_binds;
1073 l_ignore := DBMS_SQL.execute(l_denorm_csr);
1074 --dbms_output.put_line(l_ignore);
1075 END IF;
1076
1077 FND_DSQL.init;
1078 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
1079 FND_DSQL.add_text('SELECT ''FUND'', ''Y'', party_id FROM (');
1080
1081 OPEN c_get_budget_market_qualifiers('Y');
1082 -- Get all excluded market qualifiers for 'FUND'
1083 LOOP
1084 FETCH c_get_budget_market_qualifiers INTO l_segment_id,l_segment_type,l_excluded_flag;
1085 EXIT WHEN c_get_budget_market_qualifiers%NOTFOUND OR c_get_budget_market_qualifiers%NOTFOUND is NULL;
1086
1087 IF c_get_budget_market_qualifiers%ROWCOUNT > 0 THEN
1088 x_exclude_mark := TRUE;
1089 END IF;
1090 -- should be the same as how they are created in amsvfrub.pls process_offers()
1091 IF l_segment_type = 'CUSTOMER' THEN
1092 l_context := 'CUSTOMER'; -- for customer sold to
1093 l_attribute := 'QUALIFIER_ATTRIBUTE2';
1094 /* yzhao: 02/07/2003 fix bug 2789518 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY CUSTOMER BILL TO FAILS */
1095 ELSIF l_segment_type = 'CUSTOMER_BILL_TO' THEN
1096 l_context := 'CUSTOMER'; -- for customer bill to
1097 l_attribute := 'QUALIFIER_ATTRIBUTE14';
1098 /* yzhao: 02/07/2003 fix bug 2789518 ends */
1099 ELSIF l_segment_type = 'LIST' THEN
1100 l_context := 'CUSTOMER_GROUP';
1101 l_attribute := 'QUALIFIER_ATTRIBUTE1';
1102 ELSIF l_segment_type = 'SEGMENT' THEN
1103 l_context := 'CUSTOMER_GROUP';
1104 l_attribute := 'QUALIFIER_ATTRIBUTE2';
1105 ELSIF l_segment_type = 'BUYER' THEN
1106 l_context := 'CUSTOMER_GROUP';
1107 l_attribute := 'QUALIFIER_ATTRIBUTE3';
1108 ELSIF l_segment_type = 'TERRITORY' THEN
1109 l_context := 'TERRITORY';
1110 l_attribute := 'QUALIFIER_ATTRIBUTE1';
1111 /* feliu: 04/02/2003 fix bug 2778138 */
1112 ELSIF l_segment_type = 'SHIP_TO' THEN
1113 l_context := 'CUSTOMER';
1114 l_attribute := 'QUALIFIER_ATTRIBUTE11';
1115 END IF;
1116
1117 l_attr_value := l_segment_id;
1118
1119 IF c_get_budget_market_qualifiers%ROWCOUNT = 1 THEN -- for first row.
1120 -- l_budget_product_sql := '(' || l_temp_sql || ')';
1121 FND_DSQL.add_text('(');
1122 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1123 ( p_context => l_context,
1124 p_attribute => l_attribute,
1125 p_attr_value_from => l_attr_value,
1126 p_attr_value_to => NULL,
1127 p_comparison => '=',
1128 p_type => 'ELIG'
1129 );
1130 FND_DSQL.add_text(')');
1131 ELSE
1132 --l_budget_product_sql := l_budget_product_sql || ' UNION (' || l_temp_sql || ')';
1133 FND_DSQL.add_text('UNION (');
1134 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1135 ( p_context => l_context,
1136 p_attribute => l_attribute,
1137 p_attr_value_from => l_attr_value,
1138 p_attr_value_to => NULL,
1139 p_comparison => '=',
1140 p_type => 'ELIG'
1141 );
1142 FND_DSQL.add_text(')');
1143 END IF;
1144
1145 END LOOP;
1146 CLOSE c_get_budget_market_qualifiers;
1147 FND_DSQL.add_text(')');
1148 IF x_exclude_mark THEN
1149 l_denorm_csr := DBMS_SQL.open_cursor;
1150 FND_DSQL.set_cursor(l_denorm_csr);
1151 l_stmt_denorm := FND_DSQL.get_text(FALSE);
1152 --dbms_output.put_line('Budget exclude query:' || l_stmt_denorm);
1153 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
1154 FND_DSQL.do_binds;
1155 l_ignore := DBMS_SQL.execute(l_denorm_csr);
1156 --dbms_output.put_line(l_ignore);
1157 END IF;
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 ROLLBACK TO denorm_market_for_one_budget;
1162 x_return_status := fnd_api.g_ret_sts_error;
1163
1164 END denorm_market_for_one_budget;
1165
1166
1167 -------------------------------------------------------------------
1168 -- NAME
1169 -- validate_product_by_each_line
1170 -- PURPOSE
1171 -- validate product by each budget line or to check if the budget has least one of offer's product
1172 -- private procedure called by validate_object_budget
1173 -- evolved from the old API validate_product_budget
1174 -- History
1175 -- Created kdass 25-Aug-2003 11.5.10 Offer Budget Validation
1176 ----------------------------------------------------------------
1177 PROCEDURE validate_product_by_each_line (
1178 p_object_id IN NUMBER,
1179 p_object_type IN VARCHAR2,
1180 p_offer_type IN VARCHAR2,
1181 p_actbudget_id IN NUMBER,
1182 p_mode IN VARCHAR2,
1183 x_return_status OUT NOCOPY VARCHAR2,
1184 x_valid_flag OUT NOCOPY VARCHAR2)
1185 IS
1186 l_offer_product_sql VARCHAR2(32000) := NULL;
1187 l_temp_sql VARCHAR2(2000) := NULL;
1188 l_attribute VARCHAR2(50) := NULL;
1189 l_attr_value VARCHAR2(200) := NULL;
1190 l_exist_number NUMBER := NULL;
1191 l_exclude_only BOOLEAN := FALSE;
1192 l_return_status VARCHAR2(20);
1193 l_msg_count NUMBER;
1194 l_msg_data VARCHAR2(2000) := null;
1195 l_budget_prod BOOLEAN := FALSE;
1196 l_exclude_prod BOOLEAN := FALSE;
1197 l_offer_prod BOOLEAN := FALSE;
1198 l_denorm_csr NUMBER;
1199 l_ignore NUMBER;
1200 l_level_code VARCHAR2(30);
1201 l_inventory_id NUMBER;
1202 l_category_id NUMBER;
1203 l_excluded_flag VARCHAR2(1);
1204 l_stmt_denorm VARCHAR2(32000) := NULL;
1205 l_count_offer_prod NUMBER := 0;
1206
1207 CURSOR c_get_products(p_act_product_used_by_id IN NUMBER, p_arc_act_product_used_by IN VARCHAR2, p_excluded_flag IN VARCHAR2) IS
1208 SELECT decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
1209 , excluded_flag
1210 , decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
1211 FROM ams_act_products
1212 WHERE act_product_used_by_id = p_act_product_used_by_id
1213 AND arc_act_product_used_by = p_arc_act_product_used_by
1214 AND excluded_flag = p_excluded_flag;
1215
1216 CURSOR c_count_offer_prod IS
1217 SELECT count(*)
1218 FROM ozf_temp_eligibility
1219 WHERE object_type = 'OFFR';
1220
1221 BEGIN
1222
1223 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1224 x_valid_flag := fnd_api.G_TRUE;
1225
1226 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
1227
1228 denorm_product_for_one_budget (
1229 p_budget_id => p_actbudget_id,
1230 x_budget_prod => l_budget_prod,
1231 x_exclude_prod => l_exclude_prod,
1232 x_return_status => l_return_status,
1233 x_msg_count => l_msg_count,
1234 x_msg_data => l_msg_data);
1235 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1236 x_return_status := l_return_status;
1237 RAISE fnd_api.G_EXC_ERROR;
1238 END IF;
1239
1240 -- no product eligibility for budget, validation is true.
1241 IF l_budget_prod = FALSE AND l_exclude_prod = FALSE THEN
1242 x_return_status := fnd_api.g_ret_sts_success;
1243 x_valid_flag := fnd_api.g_true;
1244 RETURN;
1245 END IF;
1246 IF l_budget_prod = FALSE AND l_exclude_prod = TRUE THEN
1247 l_exclude_only := TRUE;
1248 END IF;
1249
1250
1251 FND_DSQL.init;
1252 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
1253 FND_DSQL.add_text('SELECT ''OFFR'', ''N'', product_id FROM (' );
1254 IF p_offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
1255 OPEN c_get_products(p_object_id,'OFFR','N');
1256 LOOP
1257 FETCH c_get_products INTO l_attr_value,l_excluded_flag,l_attribute;
1258 EXIT WHEN c_get_products%NOTFOUND OR c_get_products%NOTFOUND is NULL;
1259 IF c_get_products%ROWCOUNT > 0 THEN
1260 l_offer_prod := TRUE;
1261 END IF;
1262
1263 IF c_get_products%ROWCOUNT = 1 THEN
1264 -- l_offer_product_sql := '(' || l_temp_sql || ')';
1265 FND_DSQL.add_text('(');
1266 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1267 ( p_context => 'ITEM',
1268 p_attribute => l_attribute,
1269 p_attr_value_from => l_attr_value,
1270 p_attr_value_to => NULL,
1271 p_comparison => NULL,
1272 p_type => 'PROD'
1273 );
1274 FND_DSQL.add_text(')');
1275 ELSE
1276 --l_offer_product_sql := l_offer_product_sql || ' UNION (' || l_temp_sql || ')';
1277 FND_DSQL.add_text('UNION (');
1278 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1279 ( p_context => 'ITEM',
1280 p_attribute => l_attribute,
1281 p_attr_value_from => l_attr_value,
1282 p_attr_value_to => NULL,
1283 p_comparison => NULL,
1284 p_type => 'PROD'
1285 );
1286 FND_DSQL.add_text(')');
1287 END IF;
1288 END LOOP;
1289 CLOSE c_get_products;
1290
1291 ELSE -- for other offer,
1292 -- get offer's product eligibility query
1293 OZF_OFFR_ELIG_PROD_DENORM_PVT.refresh_products(
1294 p_api_version => 1.0,
1295 p_init_msg_list => fnd_api.g_false,
1296 p_commit => fnd_api.g_false,
1297 p_list_header_id => p_object_id,
1298 p_calling_from_den => 'N',
1299 x_return_status => l_return_status,
1300 x_msg_count => l_msg_count,
1301 x_msg_data => l_msg_data,
1302 x_product_stmt => l_offer_product_sql
1303 );
1304
1305 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1306 x_return_status := l_return_status;
1307 RAISE fnd_api.G_EXC_ERROR;
1308 END IF;
1309
1310 END IF;
1311 FND_DSQL.add_text(')');
1312
1313 IF l_offer_product_sql IS NULL AND l_offer_prod = FALSE THEN
1314 x_return_status := fnd_api.g_ret_sts_success;
1315 x_valid_flag := fnd_api.g_false;
1316 RETURN;
1317 END IF;
1318
1319 IF l_offer_product_sql IS NOT NULL OR l_offer_prod THEN
1320 l_denorm_csr := DBMS_SQL.open_cursor;
1321 FND_DSQL.set_cursor(l_denorm_csr);
1322 l_stmt_denorm := FND_DSQL.get_text(FALSE);
1323 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
1324 FND_DSQL.do_binds;
1325 l_ignore := DBMS_SQL.execute(l_denorm_csr);
1326 --dbms_output.put_line(l_ignore);
1327 END IF;
1328
1329 --kdass 08/31/2005 fixed bug 4338544 - if offer has no products (can happen when the
1330 --offer category has no products) then raise exception
1331 OPEN c_count_offer_prod;
1332 FETCH c_count_offer_prod INTO l_count_offer_prod;
1333 CLOSE c_count_offer_prod;
1334
1335 ozf_utility_pvt.write_conc_log('Number of products in offer: ' || l_count_offer_prod);
1336
1337 IF l_count_offer_prod = 0 THEN
1338 FND_MESSAGE.Set_Name ('OZF', 'OZF_OFFER_NO_PROD');
1339 FND_MSG_PUB.Add;
1340 RAISE fnd_api.G_EXC_ERROR;
1341 END IF;
1342
1343 IF p_mode = 'LOOSE' THEN
1344 check_product_market_loose(
1345 p_exclude_only => l_exclude_only,
1346 x_return_status => l_return_status,
1347 x_valid_flag => x_valid_flag
1348 );
1349
1350 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1351 x_return_status := l_return_status;
1352 RAISE fnd_api.G_EXC_ERROR;
1353 ELSIF x_valid_flag <> fnd_api.G_TRUE THEN
1354 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1355 FND_MESSAGE.SET_TOKEN('text', 'Product validation fails. Offer does not have a single product that matches the product of the budget');
1356 FND_MSG_PUB.Add;
1357 RAISE fnd_api.G_EXC_ERROR;
1358 END IF;
1359 ELSIF p_mode = 'STRICT' THEN
1360 check_product_market_strict(
1361 p_exclude_only => l_exclude_only,
1362 x_return_status => l_return_status,
1363 x_valid_flag => x_valid_flag
1364 );
1365
1366 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1367 x_return_status := l_return_status;
1368 RAISE fnd_api.G_EXC_ERROR;
1369 ELSIF x_valid_flag <> fnd_api.G_TRUE THEN
1370 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1371 FND_MESSAGE.SET_TOKEN('text', 'Product validation fails. Offer has product that is not in budget product list');
1372 FND_MSG_PUB.Add;
1373 RAISE fnd_api.G_EXC_ERROR;
1374 END IF;
1375 END IF;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 x_return_status := fnd_api.g_ret_sts_error;
1380 END validate_product_by_each_line;
1381
1382 -------------------------------------------------------------------
1383 -- NAME
1384 -- validate_product_by_all_lines
1385 -- PURPOSE
1386 -- validate product by all budget lines
1387 -- private procedure called by validate_object_budget_all
1388 -- History
1389 -- Created kdass 25-Aug-2003 11.5.10 Offer Budget Validation
1390 ----------------------------------------------------------------
1391 PROCEDURE validate_product_by_all_lines (
1392 p_object_id IN NUMBER,
1393 p_object_type IN VARCHAR2,
1394 p_offer_type IN VARCHAR2,
1395 x_return_status OUT NOCOPY VARCHAR2,
1396 x_valid_flag OUT NOCOPY VARCHAR2)
1397 IS
1398 l_offer_product_sql VARCHAR2(32000) := NULL;
1399 l_temp_sql VARCHAR2(2000) := NULL;
1400 l_attribute VARCHAR2(50) := NULL;
1401 l_attr_value VARCHAR2(200) := NULL;
1402 l_exist_number NUMBER := NULL;
1403 l_exclude_only BOOLEAN := FALSE;
1404 l_return_status VARCHAR2(20);
1405 l_msg_count NUMBER;
1406 l_msg_data VARCHAR2(2000) := null;
1407 l_budget_prod BOOLEAN := FALSE;
1408 l_exclude_prod BOOLEAN := FALSE;
1409 l_offer_prod BOOLEAN := FALSE;
1410 l_denorm_csr NUMBER;
1411 l_ignore NUMBER;
1412 l_level_code VARCHAR2(30);
1413 l_inventory_id NUMBER;
1414 l_category_id NUMBER;
1415 l_excluded_flag VARCHAR2(1);
1416 l_stmt_denorm VARCHAR2(32000) := NULL;
1417 -- get budget's included and excluded product id and product family id
1418 CURSOR c_get_budget_products(p_excluded_flag IN VARCHAR2) IS
1419 SELECT distinct decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
1420 ,excluded_flag
1421 ,decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
1422 FROM ams_act_products
1423 WHERE act_product_used_by_id
1424 IN
1425 (SELECT budget_source_id FROM ozf_act_budgets
1426 WHERE arc_act_budget_used_by = 'OFFR'
1427 AND act_budget_used_by_id = p_object_id
1428 AND transfer_type = 'REQUEST'
1429 AND status_code = 'APPROVED')
1430 AND arc_act_product_used_by = 'FUND'
1431 AND excluded_flag = p_excluded_flag;
1432
1433 -- get budget's product id and product family id
1434 CURSOR c_get_offer_products IS
1435 SELECT decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
1436 ,excluded_flag
1437 ,decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
1438 FROM ams_act_products
1439 WHERE act_product_used_by_id = p_object_id
1440 AND arc_act_product_used_by = 'OFFR'
1441 AND excluded_flag = 'N';
1442 BEGIN
1443
1444 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1445 x_valid_flag := fnd_api.G_TRUE;
1446
1447 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
1448
1449 FND_DSQL.init;
1450 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
1451 FND_DSQL.add_text('SELECT ''FUND'', ''N'', product_id FROM (');
1452 -- Get all product qualifiers for 'FUND'
1453 OPEN c_get_budget_products('N');
1454 LOOP
1455 FETCH c_get_budget_products INTO l_attr_value,l_excluded_flag,l_attribute;
1456 EXIT WHEN c_get_budget_products%NOTFOUND OR c_get_budget_products%NOTFOUND is NULL;
1457
1458 IF c_get_budget_products%ROWCOUNT > 0 THEN
1459 l_budget_prod := TRUE;
1460 END IF;
1461
1462 IF c_get_budget_products%ROWCOUNT = 1 THEN -- for first row.
1463 FND_DSQL.add_text('(');
1464 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1465 ( p_context => 'ITEM',
1466 p_attribute => l_attribute,
1467 p_attr_value_from => l_attr_value,
1468 p_attr_value_to => NULL,
1469 p_comparison => NULL,
1470 p_type => 'PROD'
1471 );
1472 FND_DSQL.add_text(')');
1473 ELSE
1474 FND_DSQL.add_text('UNION (');
1475 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1476 ( p_context => 'ITEM',
1477 p_attribute => l_attribute,
1478 p_attr_value_from => l_attr_value,
1479 p_attr_value_to => NULL,
1480 p_comparison => NULL,
1481 p_type => 'PROD'
1482 );
1483 FND_DSQL.add_text(')');
1484 END IF;
1485 --dbms_output.put_line('validate_product_by_all_lines: budget:');
1486 --dbms_output.put_line('validate_product_by_all_lines: budget:' || l_temp_sql);
1487
1488 END LOOP;
1489 CLOSE c_get_budget_products;
1490 FND_DSQL.add_text(')');
1491
1492 IF l_budget_prod THEN
1493 l_denorm_csr := DBMS_SQL.open_cursor;
1494 FND_DSQL.set_cursor(l_denorm_csr);
1495 l_stmt_denorm := FND_DSQL.get_text(FALSE);
1496 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
1497 FND_DSQL.do_binds;
1498 l_ignore := DBMS_SQL.execute(l_denorm_csr);
1499 --dbms_output.put_line(l_ignore);
1500 END IF;
1501
1502 FND_DSQL.init;
1503 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
1504 FND_DSQL.add_text('SELECT ''FUND'', ''Y'', product_id FROM (');
1505 -- for exclude product of FUND.
1506
1507 OPEN c_get_budget_products('Y');
1508 LOOP
1509 FETCH c_get_budget_products INTO l_attr_value,l_excluded_flag,l_attribute;
1510 EXIT WHEN c_get_budget_products%NOTFOUND OR c_get_budget_products%NOTFOUND is NULL;
1511
1512 IF c_get_budget_products%ROWCOUNT > 0 THEN
1513 l_exclude_prod := TRUE;
1514 END IF;
1515
1516 IF c_get_budget_products%ROWCOUNT = 1 THEN
1517 -- l_exclude_sql := '(' || l_temp_sql || ')';
1518 FND_DSQL.add_text('(');
1519 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1520 ( p_context => 'ITEM',
1521 p_attribute => l_attribute,
1522 p_attr_value_from => l_attr_value,
1523 p_attr_value_to => NULL,
1524 p_comparison => NULL,
1525 p_type => 'PROD'
1526 );
1527 FND_DSQL.add_text(')');
1528 ELSE
1529 --l_exclude_sql := l_exclude_sql || ' UNION (' || l_temp_sql || ')';
1530 FND_DSQL.add_text('UNION (');
1531 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1532 ( p_context => 'ITEM',
1533 p_attribute => l_attribute,
1534 p_attr_value_from => l_attr_value,
1535 p_attr_value_to => NULL,
1536 p_comparison => NULL,
1537 p_type => 'PROD'
1538 );
1539 FND_DSQL.add_text(')');
1540 END IF;
1541
1542 END LOOP;
1543 CLOSE c_get_budget_products;
1544 FND_DSQL.add_text(')');
1545
1546 IF l_exclude_prod THEN
1547 l_denorm_csr := DBMS_SQL.open_cursor;
1548 FND_DSQL.set_cursor(l_denorm_csr);
1549 l_stmt_denorm := FND_DSQL.get_text(FALSE);
1550 --dbms_output.put_line('validate_product_by_all_lines: in budget exclude');
1551 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
1552 FND_DSQL.do_binds;
1553 l_ignore := DBMS_SQL.execute(l_denorm_csr);
1554 --dbms_output.put_line(l_ignore);
1555 END IF;
1556 -- not product eligibility for budget, validation is true.
1557 IF l_budget_prod = FALSE AND l_exclude_prod = FALSE THEN
1558 x_return_status := fnd_api.g_ret_sts_success;
1559 x_valid_flag := fnd_api.g_true;
1560 RETURN;
1561 END IF;
1562
1563 IF l_budget_prod = FALSE AND l_exclude_prod = TRUE THEN
1564 l_exclude_only := TRUE;
1565 END IF;
1566
1567 FND_DSQL.init;
1568 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
1569 FND_DSQL.add_text('SELECT ''OFFR'', ''N'', product_id FROM (' );
1570 IF p_offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
1571 OPEN c_get_offer_products;
1572 LOOP
1573 FETCH c_get_offer_products INTO l_attr_value,l_excluded_flag,l_attribute;
1574 EXIT WHEN c_get_offer_products%NOTFOUND OR c_get_offer_products%NOTFOUND is NULL;
1575 IF c_get_offer_products%ROWCOUNT > 0 THEN
1576 l_offer_prod := TRUE;
1577 END IF;
1578
1579 IF c_get_offer_products%ROWCOUNT = 1 THEN
1580 -- l_offer_product_sql := '(' || l_temp_sql || ')';
1581 FND_DSQL.add_text('(');
1582 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1583 ( p_context => 'ITEM',
1584 p_attribute => l_attribute,
1585 p_attr_value_from => l_attr_value,
1586 p_attr_value_to => NULL,
1587 p_comparison => NULL,
1588 p_type => 'PROD'
1589 );
1590 FND_DSQL.add_text(')');
1591 ELSE
1592 --l_offer_product_sql := l_offer_product_sql || ' UNION (' || l_temp_sql || ')';
1593 FND_DSQL.add_text('UNION (');
1594 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1595 ( p_context => 'ITEM',
1596 p_attribute => l_attribute,
1597 p_attr_value_from => l_attr_value,
1598 p_attr_value_to => NULL,
1599 p_comparison => NULL,
1600 p_type => 'PROD'
1601 );
1602 FND_DSQL.add_text(')');
1603 END IF;
1604 END LOOP;
1605 CLOSE c_get_offer_products;
1606
1607 ELSE -- for other offer,
1608 -- get offer's product eligibility query
1609 OZF_OFFR_ELIG_PROD_DENORM_PVT.refresh_products(
1610 p_api_version => 1.0,
1611 p_init_msg_list => fnd_api.g_false,
1612 p_commit => fnd_api.g_false,
1613 p_list_header_id => p_object_id,
1614 p_calling_from_den => 'N',
1615 x_return_status => l_return_status,
1616 x_msg_count => l_msg_count,
1617 x_msg_data => l_msg_data,
1618 x_product_stmt => l_offer_product_sql
1619 );
1620 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1621 x_return_status := l_return_status;
1622 RAISE fnd_api.G_EXC_ERROR;
1623 END IF;
1624 END IF;
1625 FND_DSQL.add_text(')');
1626
1627 IF l_offer_product_sql IS NULL AND l_offer_prod = FALSE THEN
1628 x_return_status := fnd_api.g_ret_sts_success;
1629 x_valid_flag := fnd_api.g_false;
1630 RETURN;
1631 END IF;
1632
1633 IF l_offer_product_sql IS NOT NULL OR l_offer_prod THEN
1634 l_denorm_csr := DBMS_SQL.open_cursor;
1635 FND_DSQL.set_cursor(l_denorm_csr);
1636 l_stmt_denorm := FND_DSQL.get_text(FALSE);
1637 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
1638 FND_DSQL.do_binds;
1639 l_ignore := DBMS_SQL.execute(l_denorm_csr);
1640 --dbms_output.put_line(l_ignore);
1641 END IF;
1642
1643 l_exist_number := NULL;
1644
1645 check_product_market_strict(
1646 p_exclude_only => l_exclude_only,
1647 x_return_status => l_return_status,
1648 x_valid_flag => x_valid_flag
1649 );
1650
1651 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1652 x_return_status := l_return_status;
1653 RAISE fnd_api.G_EXC_ERROR;
1654 ELSIF x_valid_flag <> fnd_api.G_TRUE THEN
1655 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1656 FND_MESSAGE.SET_TOKEN('text', 'Product validation fails. Offer has product that is not in product list of all budgets');
1657 FND_MSG_PUB.Add;
1658 RAISE fnd_api.G_EXC_ERROR;
1659 END IF;
1660
1661 EXCEPTION
1662 WHEN OTHERS THEN
1663 x_return_status := fnd_api.g_ret_sts_error;
1664 END validate_product_by_all_lines;
1665
1666
1667 /* --------------------------------------------------------------------------
1668 -- yzhao: internal procedure called by validate_market_budget() to
1669 fix bug 2789492 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY BUYING GROUP DOES NOT VALI
1670 set org id since for customer bill to, denorm query on org-striped table ra_addresses party,ra_site_uses
1671 --------------------------------------------------------------------------
1672 */
1673 PROCEDURE set_budget_org (p_budget_id IN NUMBER) IS
1674
1675 l_org_id NUMBER;
1676 -- l_org_string VARCHAR2(10);
1677
1678 CURSOR get_fund_org_csr IS
1679 SELECT org_id
1680 FROM ozf_funds_all_b
1681 WHERE fund_id = p_budget_id;
1682
1683 BEGIN
1684
1685 -- l_org_string := SUBSTRB(userenv('CLIENT_INFO'),1,10);
1686 -- IF (l_org_string IS NULL) THEN
1687 OPEN get_fund_org_csr;
1688 FETCH get_fund_org_csr INTO l_org_id;
1689 CLOSE get_fund_org_csr;
1690
1691 set_org_ctx(l_org_id);
1692 -- END IF;
1693
1694 END set_budget_org;
1695
1696
1697 -------------------------------------------------------------------
1698 -- NAME
1699 -- validate_market_by_each_line
1700 -- PURPOSE
1701 -- validate customer by each budget line or to check if the budget has least one of offer's customer
1702 -- private procedure called by validate_object_budget
1703 -- evolved from the old API validate_market_budget
1704 -- History
1705 -- Created kdass 25-Aug-2003 11.5.10 Offer Budget Validation
1706 ----------------------------------------------------------------
1707 PROCEDURE validate_market_by_each_line (
1708 p_object_id IN NUMBER,
1709 p_object_type IN VARCHAR2,
1710 p_actbudget_id IN NUMBER,
1711 p_mode IN VARCHAR2,
1712 x_return_status OUT NOCOPY VARCHAR2,
1713 x_valid_flag OUT NOCOPY VARCHAR2)
1714 IS
1715 l_offer_market_sql VARCHAR2(32000) := NULL;
1716 l_budget_market_sql VARCHAR2(32000) := NULL;
1717 l_exclude_sql VARCHAR2(32000) := NULL;
1718 l_context VARCHAR2(50) := NULL;
1719 l_attribute VARCHAR2(50) := NULL;
1720 l_attr_value VARCHAR2(200) := NULL;
1721 l_exist_number NUMBER := NULL;
1722 l_exclude_only BOOLEAN := FALSE;
1723 l_return_status VARCHAR2(20);
1724 l_msg_count NUMBER;
1725 l_msg_data VARCHAR2(2000) := null;
1726 l_offer_type VARCHAR2(30);
1727 l_offer_qualifier_id NUMBER;
1728 l_denorm_csr NUMBER;
1729 l_ignore NUMBER;
1730 l_stmt_denorm VARCHAR2(32000) := NULL;
1731 l_budget_mark BOOLEAN := FALSE;
1732 l_exclude_mark BOOLEAN := FALSE;
1733 l_offer_mark BOOLEAN := FALSE;
1734
1735 -- yzhao: 02/13/2003 fix bug 2761622 AMS: VALIDATE MARKET ELIGIBILITY AND PRODUCT ELIGIBILITY BREAKS BUDGET APPROVAL
1736 -- get lumpsum or scan data offer's market qualifier
1737 CURSOR c_get_offer_customer IS
1738 SELECT offer_type, qualifier_id
1739 FROM ozf_offers
1740 WHERE qp_list_header_id = p_object_id;
1741 BEGIN
1742
1743 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1744 x_valid_flag := fnd_api.g_true;
1745
1746 /* yzhao: 02/07/2003 fix bug 2789518 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY CUSTOMER BILL TO FAILS
1747 set org id since for customer bill to, denorm query on org-striped table ra_addresses party,ra_site_uses
1748 */
1749 set_budget_org(p_budget_id => p_actbudget_id);
1750
1751 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
1752
1753 denorm_market_for_one_budget (
1754 p_budget_id => p_actbudget_id,
1755 x_budget_mark => l_budget_mark,
1756 x_exclude_mark => l_exclude_mark,
1757 x_return_status => l_return_status,
1758 x_msg_count => l_msg_count,
1759 x_msg_data => l_msg_data);
1760 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1761 x_return_status := l_return_status;
1762 RAISE fnd_api.G_EXC_ERROR;
1763 END IF;
1764
1765 -- no market eligibility for budget, validation is true.
1766 IF l_budget_mark = FALSE AND l_exclude_mark = FALSE THEN
1767 --dbms_output.put_line('budget does not have market eligiblity.');
1768 x_return_status := fnd_api.g_ret_sts_success;
1769 x_valid_flag := fnd_api.g_true;
1770 RETURN;
1771 END IF;
1772
1773 IF l_budget_mark = FALSE AND l_exclude_mark = TRUE THEN
1774 --dbms_output.put_line('budget only has exclude market eligiblity.');
1775 l_exclude_only := TRUE;
1776 END IF;
1777
1778 -- yzhao: 02/13/2003 fix bug 2761622 AMS: VALIDATE MARKET ELIGIBILITY AND PRODUCT ELIGIBILITY BREAKS BUDGET APPROVAL
1779 OPEN c_get_offer_customer;
1780 FETCH c_get_offer_customer INTO l_offer_type, l_offer_qualifier_id;
1781 CLOSE c_get_offer_customer;
1782
1783 FND_DSQL.init;
1784 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
1785 FND_DSQL.add_text('SELECT ''OFFR'', ''N'', party_id FROM (' );
1786
1787 IF l_offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
1788 -- for lumpsum and scandata, market eligibility can be only one customer
1789 IF l_offer_qualifier_id IS NULL THEN
1790 l_offer_market_sql := NULL;
1791 ELSE
1792 /* yzhao: 02/28/2003 fix bug 2828596(2761622) AMS: VALIDATE MARKET ELIGIBILITY AND PRODUCT ELIGIBILITY BREAKS BUDGET APPROVAL
1793 -- l_offer_market_sql := 'SELECT ' || l_offer_qualifier_id || ' party_id FROM DUAL';
1794 select party.party_id from hz_cust_accounts account,hz_parties party where account.party_id=party.party_id and account.cust_account_id =
1795 */
1796 l_offer_mark := TRUE;
1797 l_context := 'CUSTOMER'; -- same as customer sold to
1798 l_attribute := 'QUALIFIER_ATTRIBUTE2';
1799 l_attr_value := l_offer_qualifier_id;
1800 l_offer_market_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
1801 ( p_context => l_context,
1802 p_attribute => l_attribute,
1803 p_attr_value_from => l_attr_value,
1804 p_attr_value_to => NULL,
1805 p_comparison => '=',
1806 p_type => 'ELIG'
1807 );
1808 END IF;
1809 ELSE
1810 -- get offer's market eligibility query
1811 OZF_OFFR_ELIG_PROD_DENORM_PVT.refresh_parties(
1812 p_api_version => 1.0,
1813 p_init_msg_list => fnd_api.g_false,
1814 p_commit => fnd_api.g_false,
1815 p_list_header_id => p_object_id,
1816 p_calling_from_den => 'N',
1817 x_return_status => l_return_status,
1818 x_msg_count => l_msg_count,
1819 x_msg_data => l_msg_data,
1820 x_party_stmt => l_offer_market_sql
1821 );
1822 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1823 x_return_status := l_return_status;
1824 RAISE fnd_api.G_EXC_ERROR;
1825 END IF;
1826 END IF;
1827 FND_DSQL.add_text(')');
1828
1829 IF l_offer_market_sql IS NULL AND l_offer_mark = FALSE THEN
1830 x_return_status := fnd_api.g_ret_sts_success;
1831 x_valid_flag := fnd_api.g_false;
1832 RETURN;
1833 ELSE
1834 l_denorm_csr := DBMS_SQL.open_cursor;
1835 FND_DSQL.set_cursor(l_denorm_csr);
1836 l_stmt_denorm := FND_DSQL.get_text(FALSE);
1837 --dbms_output.put_line('offer query: '|| l_stmt_denorm);
1838 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
1839 l_offer_market_sql := l_stmt_denorm;
1840 FND_DSQL.do_binds;
1841 l_ignore := DBMS_SQL.execute(l_denorm_csr);
1842 --dbms_output.put_line(l_ignore);
1843 END IF;
1844
1845 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH) THEN
1846 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1847 FND_MESSAGE.SET_TOKEN('text', 'Offer market sql: ' || l_offer_market_sql);
1848 FND_MSG_PUB.Add;
1849 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1850 FND_MESSAGE.SET_TOKEN('text', 'Budget market sql: ' || l_budget_market_sql);
1851 FND_MSG_PUB.Add;
1852 END IF;
1853
1854 l_exist_number := NULL;
1855
1856 IF p_mode = 'LOOSE' THEN
1857 check_product_market_loose(
1858 p_exclude_only => l_exclude_only,
1859 x_return_status => l_return_status,
1860 x_valid_flag => x_valid_flag
1861 );
1862
1863 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1864 x_return_status := l_return_status;
1865 RAISE fnd_api.G_EXC_ERROR;
1866 ELSIF x_valid_flag <> fnd_api.G_TRUE THEN
1867 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1868 FND_MESSAGE.SET_TOKEN('text', 'Market validation fails. Offer does not have a single party that matches the party of the budget');
1869 FND_MSG_PUB.Add;
1870 RAISE fnd_api.G_EXC_ERROR;
1871 END IF;
1872 ELSIF p_mode = 'STRICT' THEN
1873 check_product_market_strict(
1874 p_exclude_only => l_exclude_only,
1875 x_return_status => l_return_status,
1876 x_valid_flag => x_valid_flag
1877 );
1878
1879 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1880 x_return_status := l_return_status;
1881 RAISE fnd_api.G_EXC_ERROR;
1882 ELSIF x_valid_flag <> fnd_api.G_TRUE THEN
1883 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
1884 FND_MESSAGE.SET_TOKEN('text', 'Market validation fails. Offer has party that is not in budget market list');
1885 FND_MSG_PUB.Add;
1886 RAISE fnd_api.G_EXC_ERROR;
1887 END IF;
1888 END IF;
1889
1890 EXCEPTION
1891 WHEN OTHERS THEN
1892 x_return_status := fnd_api.g_ret_sts_error;
1893
1894 END validate_market_by_each_line;
1895
1896 -------------------------------------------------------------------
1897 -- NAME
1898 -- validate_market_by_all_lines
1899 -- PURPOSE
1900 -- validate customer by all budget lines
1901 -- private procedure called by validate_object_budget_all
1902 -- History
1903 -- Created kdass 25-Aug-2003 11.5.10 Offer Budget Validation
1904 ----------------------------------------------------------------
1905 PROCEDURE validate_market_by_all_lines (
1906 p_object_id IN NUMBER,
1907 p_object_type IN VARCHAR2,
1908 x_return_status OUT NOCOPY VARCHAR2,
1909 x_valid_flag OUT NOCOPY VARCHAR2)
1910 IS
1911 l_offer_market_sql VARCHAR2(32000) := NULL;
1912 l_budget_market_sql VARCHAR2(32000) := NULL;
1913 l_exclude_sql VARCHAR2(32000) := NULL;
1914 l_temp_sql VARCHAR2(2000) := NULL;
1915 l_context VARCHAR2(50) := NULL;
1916 l_attribute VARCHAR2(50) := NULL;
1917 l_attr_value VARCHAR2(200) := NULL;
1918 l_exist_number NUMBER := NULL;
1919 l_exclude_only BOOLEAN := FALSE;
1920 l_return_status VARCHAR2(20);
1921 l_msg_count NUMBER;
1922 l_msg_data VARCHAR2(2000) := null;
1923 l_offer_type VARCHAR2(30);
1924 l_offer_qualifier_id NUMBER;
1925 l_denorm_csr NUMBER;
1926 l_ignore NUMBER;
1927 l_segment_type VARCHAR2(30);
1928 l_segment_id NUMBER;
1929 l_excluded_flag VARCHAR2(1);
1930 l_stmt_denorm VARCHAR2(32000) := NULL;
1931 l_budget_mark BOOLEAN := FALSE;
1932 l_exclude_mark BOOLEAN := FALSE;
1933 l_offer_mark BOOLEAN := FALSE;
1934
1935 -- get budget's included and excluded market qualifier ids
1936 CURSOR c_get_budget_market_qualifiers(p_exclude_flag IN VARCHAR2) IS
1937 SELECT distinct market_segment_id, segment_type, exclude_flag
1938 FROM ams_act_market_segments
1939 WHERE act_market_segment_used_by_id
1940 IN
1941 (SELECT budget_source_id FROM ozf_act_budgets
1942 WHERE arc_act_budget_used_by = 'OFFR'
1943 AND act_budget_used_by_id = p_object_id
1944 AND transfer_type = 'REQUEST'
1945 AND status_code = 'APPROVED')
1946 AND arc_act_market_segment_used_by = 'FUND'
1947 AND exclude_flag = p_exclude_flag;
1948
1949 -- yzhao: 02/13/2003 fix bug 2761622 AMS: VALIDATE MARKET ELIGIBILITY AND PRODUCT ELIGIBILITY BREAKS BUDGET APPROVAL
1950 -- get lumpsum or scan data offer's market qualifier
1951 CURSOR c_get_offer_customer IS
1952 SELECT offer_type, qualifier_id
1953 FROM ozf_offers
1954 WHERE qp_list_header_id = p_object_id;
1955
1956 BEGIN
1957
1958 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1959 x_valid_flag := fnd_api.g_true;
1960
1961 /* yzhao: 02/07/2003 fix bug 2789518 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY CUSTOMER BILL TO FAILS
1962 set org id since for customer bill to, denorm query on org-striped table ra_addresses party,ra_site_uses
1963 */
1964 --set_budget_org(p_budget_id => p_actbudget_id);
1965
1966 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
1967
1968 FND_DSQL.init;
1969 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
1970 FND_DSQL.add_text('SELECT ''FUND'', ''N'', party_id FROM (');
1971
1972 OPEN c_get_budget_market_qualifiers('N');
1973
1974 -- Get all market qualifiers for 'FUND'
1975 LOOP
1976 FETCH c_get_budget_market_qualifiers INTO l_segment_id,l_segment_type,l_excluded_flag;
1977 EXIT WHEN c_get_budget_market_qualifiers%NOTFOUND OR c_get_budget_market_qualifiers%NOTFOUND is NULL;
1978 IF c_get_budget_market_qualifiers%ROWCOUNT > 0 THEN
1979 l_budget_mark := TRUE;
1980 END IF;
1981 -- should be the same as how they are created in amsvfrub.pls process_offers()
1982 IF l_segment_type = 'CUSTOMER' THEN
1983 l_context := 'CUSTOMER'; -- for customer sold to
1984 l_attribute := 'QUALIFIER_ATTRIBUTE2';
1985 /* yzhao: 02/07/2003 fix bug 2789518 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY CUSTOMER BILL TO FAILS */
1986 ELSIF l_segment_type = 'CUSTOMER_BILL_TO' THEN
1987 l_context := 'CUSTOMER'; -- for customer bill to
1988 l_attribute := 'QUALIFIER_ATTRIBUTE14';
1989 /* yzhao: 02/07/2003 fix bug 2789518 ends */
1990 ELSIF l_segment_type = 'LIST' THEN
1991 l_context := 'CUSTOMER_GROUP';
1992 l_attribute := 'QUALIFIER_ATTRIBUTE1';
1993 ELSIF l_segment_type = 'SEGMENT' THEN
1994 l_context := 'CUSTOMER_GROUP';
1995 l_attribute := 'QUALIFIER_ATTRIBUTE2';
1996 ELSIF l_segment_type = 'BUYER' THEN
1997 l_context := 'CUSTOMER_GROUP';
1998 l_attribute := 'QUALIFIER_ATTRIBUTE3';
1999 ELSIF l_segment_type = 'TERRITORY' THEN
2000 l_context := 'TERRITORY';
2001 l_attribute := 'QUALIFIER_ATTRIBUTE1';
2002 /* feliu: 04/02/2003 fix bug 2778138 */
2003 ELSIF l_segment_type = 'SHIP_TO' THEN
2004 l_context := 'CUSTOMER';
2005 l_attribute := 'QUALIFIER_ATTRIBUTE11';
2006 END IF;
2007
2008 l_attr_value := l_segment_id;
2009
2010 IF c_get_budget_market_qualifiers%ROWCOUNT = 1 THEN -- for first row.
2011 -- l_budget_product_sql := '(' || l_temp_sql || ')';
2012 FND_DSQL.add_text('(');
2013 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
2014 ( p_context => l_context,
2015 p_attribute => l_attribute,
2016 p_attr_value_from => l_attr_value,
2017 p_attr_value_to => NULL,
2018 p_comparison => '=',
2019 p_type => 'ELIG'
2020 );
2021 FND_DSQL.add_text(')');
2022 ELSE
2023 --l_budget_product_sql := l_budget_product_sql || ' UNION (' || l_temp_sql || ')';
2024 FND_DSQL.add_text('UNION (');
2025 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
2026 ( p_context => l_context,
2027 p_attribute => l_attribute,
2028 p_attr_value_from => l_attr_value,
2029 p_attr_value_to => NULL,
2030 p_comparison => '=',
2031 p_type => 'ELIG'
2032 );
2033 FND_DSQL.add_text(')');
2034 END IF;
2035 --dbms_output.put_line('validate_market_by_all_lines: budget:' || l_temp_sql );
2036
2037 END LOOP;
2038 CLOSE c_get_budget_market_qualifiers;
2039 FND_DSQL.add_text(')');
2040
2041 IF l_budget_mark THEN
2042 l_denorm_csr := DBMS_SQL.open_cursor;
2043 FND_DSQL.set_cursor(l_denorm_csr);
2044 l_stmt_denorm := FND_DSQL.get_text(FALSE);
2045 l_budget_market_sql := l_stmt_denorm;
2046 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
2047 FND_DSQL.do_binds;
2048 l_ignore := DBMS_SQL.execute(l_denorm_csr);
2049 --dbms_output.put_line(l_ignore);
2050 END IF;
2051
2052 FND_DSQL.init;
2053 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
2054 FND_DSQL.add_text('SELECT ''FUND'', ''Y'', party_id FROM (');
2055
2056 OPEN c_get_budget_market_qualifiers('Y');
2057 -- Get all excluded market qualifiers for 'FUND'
2058 LOOP
2059 FETCH c_get_budget_market_qualifiers INTO l_segment_id,l_segment_type,l_excluded_flag;
2060 EXIT WHEN c_get_budget_market_qualifiers%NOTFOUND OR c_get_budget_market_qualifiers%NOTFOUND is NULL;
2061 IF c_get_budget_market_qualifiers%ROWCOUNT > 0 THEN
2062 l_exclude_mark := TRUE;
2063 END IF;
2064 -- should be the same as how they are created in amsvfrub.pls process_offers()
2065 IF l_segment_type = 'CUSTOMER' THEN
2066 l_context := 'CUSTOMER'; -- for customer sold to
2067 l_attribute := 'QUALIFIER_ATTRIBUTE2';
2068 /* yzhao: 02/07/2003 fix bug 2789518 - MKTF1R9:1159.0203:FUNC-BUDGET WITH MARKET ELIGIBILITY CUSTOMER BILL TO FAILS */
2069 ELSIF l_segment_type = 'CUSTOMER_BILL_TO' THEN
2070 l_context := 'CUSTOMER'; -- for customer bill to
2071 l_attribute := 'QUALIFIER_ATTRIBUTE14';
2072 /* yzhao: 02/07/2003 fix bug 2789518 ends */
2073 ELSIF l_segment_type = 'LIST' THEN
2074 l_context := 'CUSTOMER_GROUP';
2075 l_attribute := 'QUALIFIER_ATTRIBUTE1';
2076 ELSIF l_segment_type = 'SEGMENT' THEN
2077 l_context := 'CUSTOMER_GROUP';
2078 l_attribute := 'QUALIFIER_ATTRIBUTE2';
2079 ELSIF l_segment_type = 'BUYER' THEN
2080 l_context := 'CUSTOMER_GROUP';
2081 l_attribute := 'QUALIFIER_ATTRIBUTE3';
2082 ELSIF l_segment_type = 'TERRITORY' THEN
2083 l_context := 'TERRITORY';
2084 l_attribute := 'QUALIFIER_ATTRIBUTE1';
2085 /* feliu: 04/02/2003 fix bug 2778138 */
2086 ELSIF l_segment_type = 'SHIP_TO' THEN
2087 l_context := 'CUSTOMER';
2088 l_attribute := 'QUALIFIER_ATTRIBUTE11';
2089 END IF;
2090
2091 l_attr_value := l_segment_id;
2092
2093 IF c_get_budget_market_qualifiers%ROWCOUNT = 1 THEN -- for first row.
2094 -- l_budget_product_sql := '(' || l_temp_sql || ')';
2095 FND_DSQL.add_text('(');
2096 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
2097 ( p_context => l_context,
2098 p_attribute => l_attribute,
2099 p_attr_value_from => l_attr_value,
2100 p_attr_value_to => NULL,
2101 p_comparison => '=',
2102 p_type => 'ELIG'
2103 );
2104 FND_DSQL.add_text(')');
2105 ELSE
2106 --l_budget_product_sql := l_budget_product_sql || ' UNION (' || l_temp_sql || ')';
2107 FND_DSQL.add_text('UNION (');
2108 l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
2109 ( p_context => l_context,
2110 p_attribute => l_attribute,
2111 p_attr_value_from => l_attr_value,
2112 p_attr_value_to => NULL,
2113 p_comparison => '=',
2114 p_type => 'ELIG'
2115 );
2116 FND_DSQL.add_text(')');
2117 END IF;
2118
2119 END LOOP;
2120 CLOSE c_get_budget_market_qualifiers;
2121 FND_DSQL.add_text(')');
2122
2123 IF l_exclude_mark THEN
2124 l_denorm_csr := DBMS_SQL.open_cursor;
2125 FND_DSQL.set_cursor(l_denorm_csr);
2126 l_stmt_denorm := FND_DSQL.get_text(FALSE);
2127 --dbms_output.put_line('validate_market_by_all_lines: Budget exclude query:' || l_stmt_denorm);
2128 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
2129 FND_DSQL.do_binds;
2130 l_ignore := DBMS_SQL.execute(l_denorm_csr);
2131 --dbms_output.put_line(l_ignore);
2132 END IF;
2133 -- not product eligibility for budget, validation is true.
2134 IF l_budget_mark = FALSE AND l_exclude_mark = FALSE THEN
2135 --dbms_output.put_line('validate_market_by_all_lines: budget do not has market eligiblity.');
2136 x_return_status := fnd_api.g_ret_sts_success;
2137 x_valid_flag := fnd_api.g_true;
2138 RETURN;
2139 END IF;
2140
2141 IF l_budget_mark = FALSE AND l_exclude_mark = TRUE THEN
2142 --dbms_output.put_line('validate_market_by_all_lines: budget only has exclude market eligiblity.');
2143 l_exclude_only := TRUE;
2144 END IF;
2145
2146 -- yzhao: 02/13/2003 fix bug 2761622 AMS: VALIDATE MARKET ELIGIBILITY AND PRODUCT ELIGIBILITY BREAKS BUDGET APPROVAL
2147 OPEN c_get_offer_customer;
2148 FETCH c_get_offer_customer INTO l_offer_type, l_offer_qualifier_id;
2149 CLOSE c_get_offer_customer;
2150
2151 FND_DSQL.init;
2152 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
2153 FND_DSQL.add_text('SELECT ''OFFR'', ''N'', party_id FROM (' );
2154
2155 IF l_offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
2156 -- for lumpsum and scandata, market eligibility can be only one customer
2157 IF l_offer_qualifier_id IS NULL THEN
2158 l_offer_market_sql := NULL;
2159 ELSE
2160 /* yzhao: 02/28/2003 fix bug 2828596(2761622) AMS: VALIDATE MARKET ELIGIBILITY AND PRODUCT ELIGIBILITY BREAKS BUDGET APPROVAL
2161 -- l_offer_market_sql := 'SELECT ' || l_offer_qualifier_id || ' party_id FROM DUAL';
2162 select party.party_id from hz_cust_accounts account,hz_parties party where account.party_id=party.party_id and account.cust_account_id =
2163 */
2164 l_offer_mark := TRUE;
2165 l_context := 'CUSTOMER'; -- same as customer sold to
2166 l_attribute := 'QUALIFIER_ATTRIBUTE2';
2167 l_attr_value := l_offer_qualifier_id;
2168 l_offer_market_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql
2169 ( p_context => l_context,
2170 p_attribute => l_attribute,
2171 p_attr_value_from => l_attr_value,
2172 p_attr_value_to => NULL,
2173 p_comparison => '=',
2174 p_type => 'ELIG'
2175 );
2176 END IF;
2177 ELSE
2178 -- get offer's market eligibility query
2179 OZF_OFFR_ELIG_PROD_DENORM_PVT.refresh_parties(
2180 p_api_version => 1.0,
2181 p_init_msg_list => fnd_api.g_false,
2182 p_commit => fnd_api.g_false,
2183 p_list_header_id => p_object_id,
2184 p_calling_from_den => 'N',
2185 x_return_status => l_return_status,
2186 x_msg_count => l_msg_count,
2187 x_msg_data => l_msg_data,
2188 x_party_stmt => l_offer_market_sql
2189 );
2190 --dbms_output.put_line('validate_market_by_all_lines: Offer party sql returns ' || l_offer_market_sql);
2191 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2192 x_return_status := l_return_status;
2193 RAISE fnd_api.G_EXC_ERROR;
2194 END IF;
2195 END IF;
2196 FND_DSQL.add_text(')');
2197
2198 IF l_offer_market_sql IS NULL AND l_offer_mark = FALSE THEN
2199 x_return_status := fnd_api.g_ret_sts_success;
2200 x_valid_flag := fnd_api.g_false;
2201 RETURN;
2202 ELSE
2203 l_denorm_csr := DBMS_SQL.open_cursor;
2204 FND_DSQL.set_cursor(l_denorm_csr);
2205 l_stmt_denorm := FND_DSQL.get_text(FALSE);
2206 --dbms_output.put_line('validate_market_by_all_lines: offer query: '|| l_stmt_denorm);
2207 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
2208 l_offer_market_sql := l_stmt_denorm;
2209 FND_DSQL.do_binds;
2210 l_ignore := DBMS_SQL.execute(l_denorm_csr);
2211 --dbms_output.put_line(l_ignore);
2212 END IF;
2213
2214 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH) THEN
2215 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
2216 FND_MESSAGE.SET_TOKEN('text', 'Offer market sql: ' || l_offer_market_sql);
2217 FND_MSG_PUB.Add;
2218 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
2219 FND_MESSAGE.SET_TOKEN('text', 'Budget market sql: ' || l_budget_market_sql);
2220 FND_MSG_PUB.Add;
2221 END IF;
2222
2223 l_exist_number := NULL;
2224 check_product_market_strict(
2225 p_exclude_only => l_exclude_only,
2226 x_return_status => l_return_status,
2227 x_valid_flag => x_valid_flag
2228 );
2229
2230 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2231 x_return_status := l_return_status;
2232 RAISE fnd_api.G_EXC_ERROR;
2233 ELSIF x_valid_flag <> fnd_api.G_TRUE THEN
2234 FND_MESSAGE.Set_Name ('OZF', 'OZF_API_DEBUG_MESSAGE');
2235 FND_MESSAGE.SET_TOKEN('text', 'Market validation fails. Offer has party that is not in market list of all budgets');
2236 FND_MSG_PUB.Add;
2237 RAISE fnd_api.G_EXC_ERROR;
2238 END IF;
2239
2240 EXCEPTION
2241 WHEN OTHERS THEN
2242 x_return_status := fnd_api.g_ret_sts_error;
2243
2244 END validate_market_by_all_lines;
2245
2246
2247 -------------------------------------------------------------------
2248 -- NAME
2249 -- validate_object_budget
2250 -- PURPOSE
2251 -- this API will be called by the Workflow API for each budget line to
2252 -- validate whether a budget is qualified to fund an offer in terms of
2253 -- market and product eligibility
2254 -- History
2255 -- Created yzhao 01/22/2002
2256 -- CREATE GLOBAL TEMPORARY TABLE ozf_temp_eligibility(
2257 -- OBJECT_TYPE VARCHAR2(30),
2258 -- ELIGIBILITY_ID NUMBER,
2259 -- EXCLUDE_FLAG VARCHAR2(1))
2260 -- ON COMMIT DELETE ROWS;
2261 -- Modified kdass 22-Aug-2003 modified for 11.5.10 Offer Budget Validation
2262 ----------------------------------------------------------------
2263 PROCEDURE validate_object_budget (
2264 p_object_id IN NUMBER,
2265 p_object_type IN VARCHAR2,
2266 p_actbudget_id IN NUMBER,
2267 x_return_status OUT NOCOPY VARCHAR2,
2268 x_msg_count OUT NOCOPY NUMBER,
2269 x_msg_data OUT NOCOPY VARCHAR2)
2270 IS
2271 l_check_validation VARCHAR2(50);
2272 l_return_status VARCHAR2(20);
2273 l_valid_flag VARCHAR2(5);
2274 l_offer_type VARCHAR2(30);
2275 l_budget_id NUMBER;
2276 l_msg_data VARCHAR2(2000) := NULL;
2277 l_mode_product VARCHAR2(20);
2278 l_mode_market VARCHAR2(20);
2279
2280 CURSOR c_get_fund_info IS
2281 SELECT budget_source_id
2282 FROM ozf_act_budgets
2283 WHERE activity_budget_id = p_actbudget_id;
2284
2285 CURSOR c_get_offer_type IS
2286 SELECT offer_type
2287 FROM ozf_offers
2288 WHERE qp_list_header_id = p_object_id;
2289
2290 BEGIN
2291
2292 SAVEPOINT validate_object_budget;
2293
2294 l_check_validation := fnd_profile.value('OZF_CHECK_MKTG_PROD_ELIG');
2295
2296 IF G_DEBUG THEN
2297 ozf_utility_pvt.debug_message(' profile value:' || l_check_validation);
2298 END IF;
2299
2300 IF (NVL(l_check_validation, 'NO') = 'NO') THEN
2301 -- return success if profile value is NO
2302 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2303 RETURN;
2304 END IF;
2305
2306 IF p_object_type <> 'OFFR' THEN
2307 -- return success. right now we only validate offer
2308 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2309 RETURN;
2310 END IF;
2311
2312 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2313
2314 OPEN c_get_fund_info;
2315 FETCH c_get_fund_info INTO l_budget_id;
2316 CLOSE c_get_fund_info;
2317
2318 OPEN c_get_offer_type;
2319 FETCH c_get_offer_type INTO l_offer_type;
2320 CLOSE c_get_offer_type;
2321
2322 IF l_check_validation = 'PRODUCT_STRICT_CUSTOMER_STRICT' THEN
2323 l_mode_product := 'STRICT';
2324 l_mode_market := 'STRICT';
2325 ELSIF l_check_validation = 'PRODUCT_STRICT_CUSTOMER_LOOSE' THEN
2326 l_mode_product := 'STRICT';
2327 l_mode_market := 'LOOSE';
2328 ELSIF l_check_validation = 'PRODUCT_LOOSE_CUSTOMER_STRICT' THEN
2329 l_mode_product := 'LOOSE';
2330 l_mode_market := 'STRICT';
2331 END IF;
2332
2333 IF l_offer_type <> 'ORDER' THEN
2334 -- offer type 'ORDER VALUE' does not have product eligibility, so do not check
2335 validate_product_by_each_line(
2336 p_object_id => p_object_id,
2337 p_object_type => p_object_type,
2338 p_offer_type => l_offer_type,
2339 p_actbudget_id => l_budget_id,
2340 p_mode => l_mode_product,
2341 x_return_status => l_return_status,
2342 x_valid_flag => l_valid_flag
2343 );
2344 IF G_DEBUG THEN
2345 ozf_utility_pvt.debug_message(' validate_product_by_each_line returns ' || l_return_status || ' valid_flag=' || l_valid_flag);
2346 END IF;
2347 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2348 x_return_status := l_return_status;
2349 RAISE fnd_api.G_EXC_ERROR;
2350 ELSIF l_valid_flag <> fnd_api.G_TRUE THEN
2351 -- how to return back message? through fnd_message?
2352 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2353 FND_MESSAGE.Set_Name ('OZF', 'OZF_PRODUCT_ELIG_MISMATCH');
2354 FND_MSG_PUB.Add;
2355 END IF;
2356 IF G_DEBUG THEN
2357 ozf_utility_pvt.debug_message('FAILURE: budget product eligibility does not match that of offer');
2358 END IF;
2359 RAISE fnd_api.G_EXC_ERROR;
2360 END IF;
2361 END IF;
2362
2363 validate_market_by_each_line(
2364 p_object_id => p_object_id,
2365 p_object_type => p_object_type,
2366 p_actbudget_id => l_budget_id,
2367 p_mode => l_mode_market,
2368 x_return_status => l_return_status,
2369 x_valid_flag => l_valid_flag
2370 );
2371 IF G_DEBUG THEN
2372 ozf_utility_pvt.debug_message(' validate_market_by_each_line returns ' || l_return_status);
2373 END IF;
2374 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2375 x_return_status := l_return_status;
2376 RAISE fnd_api.G_EXC_ERROR;
2377 ELSIF l_valid_flag <> fnd_api.G_TRUE THEN
2378 -- how to return back message? through fnd_message?
2379 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2380 FND_MESSAGE.Set_Name ('OZF', 'OZF_MARKET_ELIG_MISMATCH');
2381 FND_MSG_PUB.Add;
2382 END IF;
2383 IF G_DEBUG THEN
2384 ozf_utility_pvt.debug_message('FAILURE: budget market eligibility does not match that of offer');
2385 END IF;
2386 RAISE fnd_api.G_EXC_ERROR;
2387 END IF;
2388 IF G_DEBUG THEN
2389 ozf_utility_pvt.debug_message(' SUCCESS ');
2390 END IF;
2391
2392 EXCEPTION
2393 WHEN fnd_api.g_exc_error THEN
2394 ROLLBACK TO validate_object_budget;
2395 x_return_status := fnd_api.g_ret_sts_error;
2396 fnd_msg_pub.count_and_get(
2397 p_encoded => fnd_api.g_false
2398 ,p_count => x_msg_count
2399 ,p_data => x_msg_data);
2400 WHEN fnd_api.g_exc_unexpected_error THEN
2401 ROLLBACK TO validate_object_budget;
2402 x_return_status := fnd_api.g_ret_sts_unexp_error;
2403 fnd_msg_pub.count_and_get(
2404 p_encoded => fnd_api.g_false
2405 ,p_count => x_msg_count
2406 ,p_data => x_msg_data);
2407
2408 WHEN OTHERS THEN
2409 ROLLBACK TO validate_object_budget;
2410 x_return_status := fnd_api.g_ret_sts_error;
2411 fnd_msg_pub.count_and_get (
2412 p_count => x_msg_count
2413 , p_data => x_msg_data
2414 , p_encoded => fnd_api.g_false
2415 );
2416
2417 END validate_object_budget;
2418
2419 -----------------------------------------------------------------
2420 -- NAME
2421 -- validate_object_budget_all
2422 -- PURPOSE
2423 -- this API will be called by the Workflow API after all the budget line
2424 -- approvals are done. it will validate the offer's market and product
2425 -- eligibility in terms of all budget lines
2426 -- History
2427 -- Created kdass 22-Aug-2003 11.5.10 Offer Budget Validation
2428 ----------------------------------------------------------------
2429 PROCEDURE validate_object_budget_all (
2430 p_object_id IN NUMBER,
2431 p_object_type IN VARCHAR2,
2432 x_return_status OUT NOCOPY VARCHAR2,
2433 x_msg_count OUT NOCOPY NUMBER,
2434 x_msg_data OUT NOCOPY VARCHAR2)
2435 IS
2436 l_check_validation VARCHAR2(50);
2437 l_return_status VARCHAR2(20);
2438 l_valid_flag VARCHAR2(5);
2439 l_offer_type VARCHAR2(30);
2440 l_msg_data VARCHAR2(2000) := NULL;
2441
2442 CURSOR c_get_offer_type IS
2443 SELECT offer_type
2444 FROM ozf_offers
2445 WHERE qp_list_header_id = p_object_id;
2446
2447 BEGIN
2448
2449 l_check_validation := fnd_profile.value('OZF_CHECK_MKTG_PROD_ELIG');
2450
2451 IF (NVL(l_check_validation, 'NO') = 'NO') OR (l_check_validation = 'PRODUCT_STRICT_CUSTOMER_STRICT') THEN
2452 -- return success if profile value is NO or PRODUCT_STRICT_CUSTOMER_STRICT
2453 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2454 RETURN;
2455 END IF;
2456
2457 IF p_object_type <> 'OFFR' THEN
2458 -- return success. right now we only validate offer
2459 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2460 RETURN;
2461 END IF;
2462
2463 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2464
2465 OPEN c_get_offer_type;
2466 FETCH c_get_offer_type INTO l_offer_type;
2467 CLOSE c_get_offer_type;
2468
2469 IF l_check_validation = 'PRODUCT_STRICT_CUSTOMER_LOOSE' THEN
2470 -- validate customer by all budget lines
2471 validate_market_by_all_lines(
2472 p_object_id => p_object_id,
2473 p_object_type => p_object_type,
2474 x_return_status => l_return_status,
2475 x_valid_flag => l_valid_flag
2476 );
2477 IF G_DEBUG THEN
2478 ozf_utility_pvt.debug_message(' validate_market_by_all_lines returns ' || l_return_status);
2479 END IF;
2480 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2481 x_return_status := l_return_status;
2482 -- revert all the approved budget lines
2483 revert_approved_request ( p_offer_id => p_object_id
2484 ,x_return_status => l_return_status
2485 ,x_msg_count => x_msg_count
2486 ,x_msg_data => l_msg_data
2487 );
2488 RAISE fnd_api.G_EXC_ERROR;
2489 ELSIF l_valid_flag <> fnd_api.G_TRUE THEN
2490 -- how to return back message? through fnd_message?
2491 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2492 FND_MESSAGE.Set_Name ('OZF', 'OZF_MARKET_ELIG_MISMATCH');
2493 FND_MSG_PUB.Add;
2494 END IF;
2495 IF G_DEBUG THEN
2496 ozf_utility_pvt.debug_message('FAILURE: all budgets market eligibility does not match that of offer');
2497 END IF;
2498 -- revert all the approved budget lines
2499 revert_approved_request ( p_offer_id => p_object_id
2500 ,x_return_status => l_return_status
2501 ,x_msg_count => x_msg_count
2502 ,x_msg_data => l_msg_data
2503 );
2504 RAISE fnd_api.G_EXC_ERROR;
2505 END IF;
2506 ELSIF l_check_validation = 'PRODUCT_LOOSE_CUSTOMER_STRICT' THEN
2507 -- validate product by all budget lines
2508 IF l_offer_type <> 'ORDER' THEN
2509 -- offer type 'ORDER VALUE' does not have product eligibility, so do not check
2510 validate_product_by_all_lines(
2511 p_object_id => p_object_id,
2512 p_object_type => p_object_type,
2513 p_offer_type => l_offer_type,
2514 x_return_status => l_return_status,
2515 x_valid_flag => l_valid_flag
2516 );
2517 IF G_DEBUG THEN
2518 ozf_utility_pvt.debug_message(' validate_product_by_all_lines returns ' || l_return_status || ' valid_flag=' || l_valid_flag);
2519 END IF;
2520 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2521 x_return_status := l_return_status;
2522 -- revert all the approved budget lines
2523 revert_approved_request ( p_offer_id => p_object_id
2524 ,x_return_status => l_return_status
2525 ,x_msg_count => x_msg_count
2526 ,x_msg_data => l_msg_data
2527 );
2528 RAISE fnd_api.G_EXC_ERROR;
2529 ELSIF l_valid_flag <> fnd_api.G_TRUE THEN
2530 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2531 FND_MESSAGE.Set_Name ('OZF', 'OZF_PRODUCT_ELIG_MISMATCH');
2532 FND_MSG_PUB.Add;
2533 END IF;
2534 IF G_DEBUG THEN
2535 ozf_utility_pvt.debug_message('FAILURE: all budgets product eligibility does not match that of offer');
2536 END IF;
2537 -- revert all the approved budget lines
2538 revert_approved_request ( p_offer_id => p_object_id
2539 ,x_return_status => l_return_status
2540 ,x_msg_count => x_msg_count
2541 ,x_msg_data => l_msg_data
2542 );
2543 RAISE fnd_api.G_EXC_ERROR;
2544 END IF;
2545 END IF;
2546 END IF;
2547
2548
2549 IF G_DEBUG THEN
2550 ozf_utility_pvt.debug_message(' SUCCESS ');
2551 END IF;
2552
2553 EXCEPTION
2554 WHEN fnd_api.g_exc_error THEN
2555 x_return_status := fnd_api.g_ret_sts_error;
2556 fnd_msg_pub.count_and_get(
2557 p_encoded => fnd_api.g_false
2558 ,p_count => x_msg_count
2559 ,p_data => x_msg_data);
2560 WHEN fnd_api.g_exc_unexpected_error THEN
2561 x_return_status := fnd_api.g_ret_sts_unexp_error;
2562 fnd_msg_pub.count_and_get(
2563 p_encoded => fnd_api.g_false
2564 ,p_count => x_msg_count
2565 ,p_data => x_msg_data);
2566
2567 WHEN OTHERS THEN
2568 x_return_status := fnd_api.g_ret_sts_error;
2569 fnd_msg_pub.count_and_get (
2570 p_count => x_msg_count
2571 , p_data => x_msg_data
2572 , p_encoded => fnd_api.g_false
2573 );
2574
2575 END validate_object_budget_all;
2576
2577
2578
2579 -------------------------------------------------------------------
2580 -- NAME
2581 -- given a customer and product, check if a budget is qualified
2582 -- PURPOSE
2583 --
2584 -- History
2585 -- Created yzhao 02/06/2004
2586 ----------------------------------------------------------------
2587 PROCEDURE check_budget_qualification(
2588 p_budget_id IN NUMBER
2589 , p_cust_account_id IN NUMBER := NULL
2590 , p_product_item_id IN NUMBER := NULL
2591 , x_qualify_flag OUT NOCOPY BOOLEAN
2592 , x_return_status OUT NOCOPY VARCHAR2
2593 , x_msg_count OUT NOCOPY NUMBER
2594 , x_msg_data OUT NOCOPY VARCHAR2)
2595 IS
2596 l_qualify_flag BOOLEAN := false;
2597 l_budget_mark BOOLEAN := FALSE;
2598 l_exclude_mark BOOLEAN := FALSE;
2599 l_temp_id NUMBER := null;
2600 l_party_id NUMBER;
2601 l_return_status VARCHAR2(30);
2602 l_msg_count NUMBER;
2603 l_msg_data VARCHAR2(2048);
2604
2605 CURSOR c_check_items(p_item_id NUMBER) IS
2606 SELECT 1
2607 FROM dual
2608 WHERE (EXISTS
2609 (SELECT 1
2610 FROM ozf_temp_eligibility
2611 WHERE object_type = 'FUND'
2612 AND exclude_flag = 'N'
2613 AND eligibility_id = p_item_id))
2614 AND (
2615 NOT EXISTS
2616 (SELECT 1
2617 FROM ozf_temp_eligibility
2618 WHERE object_type = 'FUND'
2619 AND exclude_flag = 'Y'
2620 AND eligibility_id = p_item_id));
2621
2622 CURSOR c_check_exclude_items(p_item_id NUMBER) IS
2623 SELECT 1
2624 FROM ozf_temp_eligibility
2625 WHERE object_type = 'FUND'
2626 AND exclude_flag = 'Y'
2627 AND eligibility_id = p_item_id;
2628
2629 /* currently validation use party_id, so get party_id only */
2630 CURSOR c_get_party_id IS
2631 SELECT party_id
2632 FROM hz_cust_accounts
2633 WHERE cust_account_id = p_cust_account_id;
2634
2635 BEGIN
2636 x_qualify_flag := false;
2637 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2638
2639 IF p_cust_account_id IS NULL THEN
2640 l_qualify_flag := true;
2641 ELSE
2642 OPEN c_get_party_id;
2643 FETCH c_get_party_id INTO l_party_id;
2644 CLOSE c_get_party_id;
2645
2646 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
2647
2648 denorm_market_for_one_budget (
2649 p_budget_id => p_budget_id,
2650 x_budget_mark => l_budget_mark,
2651 x_exclude_mark => l_exclude_mark,
2652 x_return_status => l_return_status,
2653 x_msg_count => l_msg_count,
2654 x_msg_data => l_msg_data);
2655 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2656 RAISE fnd_api.G_EXC_ERROR;
2657 END IF;
2658
2659 IF l_budget_mark = FALSE AND l_exclude_mark = FALSE THEN
2660 -- no market eligibility for budget, validation is true.
2661 --dbms_output.put_line('budget does not have market eligiblity.');
2662 l_qualify_flag := true;
2663 ELSIF l_budget_mark = FALSE AND l_exclude_mark = TRUE THEN
2664 -- exclude items only
2665 --dbms_output.put_line('budget only has exclude market eligiblity.');
2666 OPEN c_check_exclude_items(l_party_id);
2667 FETCH c_check_exclude_items INTO l_temp_id;
2668 CLOSE c_check_exclude_items ;
2669 IF l_temp_id IS NULL THEN
2670 l_qualify_flag := true;
2671 ELSIF l_temp_id = 1 THEN
2672 l_qualify_flag := false;
2673 END IF;
2674 ELSE
2675 -- defined include items
2676 --dbms_output.put_line('budget defines include market eligiblity.');
2677 OPEN c_check_items(l_party_id);
2678 FETCH c_check_items INTO l_temp_id;
2679 CLOSE c_check_items ;
2680 IF l_temp_id = 1 THEN
2681 l_qualify_flag := true;
2682 ELSE
2683 l_qualify_flag := false;
2684 END IF;
2685 END IF;
2686
2687 END IF;
2688
2689 IF NOT l_qualify_flag THEN
2690 x_qualify_flag := false;
2691 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2692 RETURN;
2693 END IF;
2694
2695 IF p_product_item_id IS NULL THEN
2696 x_qualify_flag := true;
2697 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2698 RETURN;
2699 END IF;
2700
2701 l_temp_id := null;
2702 l_qualify_flag := false;
2703 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
2704
2705 denorm_product_for_one_budget (
2706 p_budget_id => p_budget_id,
2707 x_budget_prod => l_budget_mark,
2708 x_exclude_prod => l_exclude_mark,
2709 x_return_status => l_return_status,
2710 x_msg_count => l_msg_count,
2711 x_msg_data => l_msg_data);
2712 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
2713 RAISE fnd_api.G_EXC_ERROR;
2714 END IF;
2715
2716 IF l_budget_mark = FALSE AND l_exclude_mark = FALSE THEN
2717 -- no product eligibility for budget, validation is true.
2718 --dbms_output.put_line('budget does not have product eligiblity.');
2719 l_qualify_flag := true;
2720 ELSIF l_budget_mark = FALSE AND l_exclude_mark = TRUE THEN
2721 -- exclude items only
2722 --dbms_output.put_line('budget only has exclude product eligiblity.');
2723 OPEN c_check_exclude_items(p_product_item_id);
2724 FETCH c_check_exclude_items INTO l_temp_id;
2725 CLOSE c_check_exclude_items ;
2726 IF l_temp_id IS NULL THEN
2727 l_qualify_flag := true;
2728 ELSIF l_temp_id = 1 THEN
2729 l_qualify_flag := false;
2730 END IF;
2731 ELSE
2732 -- defined include items
2733 --dbms_output.put_line('budget defines include market eligiblity.');
2734 OPEN c_check_items(p_product_item_id);
2735 FETCH c_check_items INTO l_temp_id;
2736 CLOSE c_check_items ;
2737 IF l_temp_id = 1 THEN
2738 l_qualify_flag := true;
2739 ELSE
2740 l_qualify_flag := false;
2741 END IF;
2742 END IF;
2743
2744 x_qualify_flag := l_qualify_flag;
2745 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2746
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 x_return_status := fnd_api.g_ret_sts_error;
2750 fnd_msg_pub.count_and_get (
2751 p_count => x_msg_count
2752 , p_data => x_msg_data
2753 , p_encoded => fnd_api.g_false
2754 );
2755
2756 END check_budget_qualification;
2757
2758
2759
2760 -------------------------------------------------------------------
2761 -- NAME
2762 -- check if offer's budget threshold is met
2763 -- if so, return offer status as 'APPROVED'
2764 -- else, send notification to offer owner, and return offer status as 'NEW'
2765 -- PURPOSE
2766 --
2767 -- History
2768 -- Created yzhao 07/11/2002
2769 ----------------------------------------------------------------
2770 /*
2771 PROCEDURE check_budget_threshold (
2772 p_object_type IN VARCHAR2,
2773 p_object_id IN NUMBER,
2774 x_new_status OUT NOCOPY VARCHAR2,
2775 x_return_status OUT NOCOPY VARCHAR2
2776 )
2777 IS
2778 l_notification_id NUMBER;
2779 l_return_status NUMBER;
2780 l_msg_count NUMBER;
2781 l_msg_data VARCHAR2(4000);
2782 l_percent NUMBER;
2783 TYPE obj_csr_type IS REF CURSOR ;
2784 l_obj_details obj_csr_type;
2785 l_budget_amount NUMBER;
2786 l_owner_id NUMBER;
2787 l_total_amt NUMBER;
2788 l_strSubject VARCHAR2(300);
2789 l_strBody VARCHAR2(2000);
2790
2791 CURSOR c_total_amount IS
2792 SELECT SUM(NVL(request_amount,0))
2793 FROM ozf_act_budgets
2794 WHERE act_budget_used_by_id= p_object_id
2795 AND arc_act_budget_used_by = p_object_type;
2796
2797 BEGIN
2798
2799 x_return_status := fnd_api.g_ret_sts_success;
2800 l_percent := NVL(Fnd_Profile.Value('AMS_APPROVAL_CUTOFF_PERCENT'),0)/100;
2801
2802 IF l_percent = 0 THEN -- if profile value is set to zero, x_new_status is 'ACTIVE'
2803 x_new_status := 'ACTIVE';
2804 ELSE
2805 IF p_object_type = 'CAMP' THEN
2806 OPEN l_obj_details FOR
2807 SELECT budget_amount_tc,owner_user_id
2808 FROM ams_campaigns_vl
2809 WHERE campaign_id = p_object_id;
2810 ELSIF p_object_type = 'CSCH' THEN
2811 OPEN l_obj_details FOR
2812 SELECT budget_amount_tc,owner_user_id
2813 FROM ams_campaign_schedules_vl
2814 WHERE schedule_id=p_object_id;
2815 ELSIF p_object_type = 'OFFR' THEN
2816 OPEN l_obj_details FOR
2817 SELECT budget_amount_tc,owner_id
2818 FROM ozf_offers
2819 WHERE qp_list_header_id=p_object_id;
2820 ELSIF p_object_type = 'EVEH' THEN
2821 OPEN l_obj_details FOR
2822 SELECT fund_amount_tc,owner_user_id
2823 FROM ams_event_headers_vl
2824 WHERE event_header_id = p_object_id;
2825 ELSIF p_object_type = 'EVEO' THEN
2826 OPEN l_obj_details FOR
2827 SELECT fund_amount_tc,owner_user_id
2828 FROM ams_event_offers_vl
2829 WHERE event_offer_id = p_object_id;
2830 ELSIF p_object_type = 'EONE' THEN
2831 OPEN l_obj_details FOR
2832 SELECT fund_amount_tc,owner_user_id
2833 FROM ams_event_offers_vl
2834 WHERE event_offer_id = p_object_id;
2835 ELSIF p_object_type = 'DELV' THEN
2836 OPEN l_obj_details FOR
2837 SELECT budget_amount_tc,owner_user_id
2838 FROM ams_deliverables_vl
2839 WHERE deliverable_id = p_object_id;
2840 ELSE
2841 Fnd_Message.Set_Name('OZF','OZF_BAD_APPROVAL_OBJECT_TYPE');
2842 Fnd_Msg_Pub.ADD;
2843 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2844 RETURN;
2845 END IF ;
2846
2847 FETCH l_obj_details INTO l_budget_amount,l_owner_id;
2848 IF l_obj_details%NOTFOUND THEN
2849 CLOSE l_obj_details;
2850 Fnd_Message.Set_Name('OZF','OZF_APPR_BAD_DETAILS');
2851 Fnd_Msg_Pub.ADD;
2852 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2853 RETURN;
2854 END IF;
2855 CLOSE l_obj_details;
2856
2857 OPEN c_total_amount;
2858 FETCH c_total_amount INTO l_total_amt;
2859 CLOSE c_total_amount;
2860 --if total request amount equal estimated amount multiple threshold,
2861 -- set x_new_status to 'ACTIVE', else set to 'DRAFT' and send notification.
2862 IF l_total_amt >= l_budget_amount * l_percent THEN
2863 x_new_status := 'ACTIVE';
2864 ELSE
2865 x_new_status := 'DRAFT';
2866
2867 fnd_message.set_name('OZF', 'OZF_PARTNER_SOURCING_SUBJECT');
2868 --fnd_message.set_token ('BUDGET_AMT', l_request_amt, FALSE);
2869 --fnd_message.set_token ('PARTNER_NAME', l_partner_name, FALSE);
2870 l_strSubject := Substr(fnd_message.get,1,200);
2871
2872 fnd_message.set_name('OZF', 'OZF_NOTIFY_HEADERLINE');
2873 --l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10);
2874 fnd_message.set_name ('OZF', 'OZF_VENDOR_MESSAGE');
2875 --fnd_message.set_token ('PARTNER_NAME', l_partner_name, FALSE);
2876 l_strBody := l_strBody || Substr(fnd_message.get,1,1000);
2877
2878 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
2879 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
2880
2881 ozf_utility_pvt.send_wf_standalone_message(
2882 p_subject => l_strSubject
2883 ,p_body => l_strBody
2884 ,p_send_to_res_id => l_owner_id
2885 ,x_notif_id => l_notification_id
2886 ,x_return_status => l_return_status
2887 );
2888 END IF; -- end of l_total_amt
2889
2890 END IF; -- end of l_percent.
2891
2892 EXCEPTION
2893 WHEN OTHERS THEN
2894 x_return_status := fnd_api.g_ret_sts_error;
2895 fnd_msg_pub.count_and_get (
2896 p_count => l_msg_count
2897 , p_data => l_msg_data
2898 , p_encoded => fnd_api.g_false
2899 );
2900 END check_budget_threshold;
2901 */
2902
2903 -------------------------------------------------------------------
2904 -- NAME
2905 -- concurrent program for budget-object eligibility validation
2906 -- PURPOSE
2907 -- Validate whether a budget is qualified to fund an object(offer only for now)
2908 -- in terms of market and product eligibility
2909 -- if validation succeeds, budget request is set to APPROVED
2910 -- otherwise, budget request is reverted to NEW.
2911 -- if it is called for offer activation, offer status is updated based on validation result
2912 -- This process is kicked off when object's budget approval is not required
2913 -- but budget-object validation is needed
2914 -- History
2915 -- Created yzhao 07/11/2002
2916 ----------------------------------------------------------------
2917 PROCEDURE conc_validate_offer_budget (
2918 x_errbuf OUT NOCOPY VARCHAR2,
2919 x_retcode OUT NOCOPY NUMBER,
2920 p_object_id IN NUMBER,
2921 p_object_type IN VARCHAR2,
2922 p_actbudget_id IN NUMBER
2923 )
2924 IS
2925 l_api_name CONSTANT VARCHAR2(30) := 'conc_validate_offer_budget';
2926 l_full_name CONSTANT VARCHAR2(60)
2927 := G_PACKAGE_NAME || '.' || l_api_name;
2928 l_new_status_id NUMBER;
2929 l_return_status VARCHAR2(30);
2930 l_msg_count NUMBER;
2931 l_msg_data VARCHAR2(2048);
2932 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type ;
2933 l_offer_status VARCHAR2(30) := 'ACTIVE';
2934 l_strSubject VARCHAR2(300);
2935 l_strBody VARCHAR2(2000);
2936 l_owner_id NUMBER;
2937 l_notification_id NUMBER;
2938 l_amount_error VARCHAR2(300);
2939 l_modifier_list_rec Ozf_Offer_Pvt.modifier_list_rec_type;
2940 l_offer_code VARCHAR2(50);
2941 l_offer_name VARCHAR2(50);
2942 l_budget_name VARCHAR2(240); -- change size from 50 to 240 to fix issue 2 in bug 4240968
2943 l_final_data VARCHAR2(2000);
2944 l_msg_index NUMBER ;
2945 l_cnt NUMBER := 0 ;
2946
2947 CURSOR c_get_requests IS
2948 SELECT activity_budget_id, act_budget_used_by_id,arc_act_budget_used_by,requester_id
2949 FROM ozf_act_budgets
2950 WHERE arc_act_budget_used_by = p_object_type
2951 AND act_budget_used_by_id = p_object_id
2952 AND transfer_type = 'REQUEST';
2953 -- AND status_code = 'PENDING'; -- should it be pending validation?
2954
2955 CURSOR c_offer_info(p_object_id IN NUMBER) IS
2956 SELECT offer_type,custom_setup_id, reusable,offer_amount,offer_code,owner_id, qph.description
2957 FROM ozf_offers , qp_list_headers qph
2958 WHERE qp_list_header_id = p_object_id
2959 and qp_list_header_id = qph.list_header_id ;
2960
2961 --kdass 09-DEC-2005 bug 4870218 - SQL ID# 14892629
2962 CURSOR c_get_budget_name(p_activity_id IN NUMBER) IS
2963 SELECT fun.short_name
2964 FROM ozf_act_budgets , ozf_funds_all_tl fun
2965 WHERE activity_budget_id = p_activity_id
2966 AND budget_source_id = fun.fund_id
2967 AND USERENV('LANG') IN (fun.language, fun.source_lang);
2968 /*
2969 CURSOR c_get_budget_name(p_activity_id IN NUMBER) IS
2970 SELECT fun.short_name
2971 FROM ozf_act_budgets , ozf_fund_details_v fun
2972 WHERE activity_budget_id = p_activity_id
2973 AND budget_source_id = fun.fund_id;
2974 */
2975
2976 BEGIN
2977 SAVEPOINT conc_validate_offer_budget;
2978
2979 x_errbuf := null;
2980 x_retcode := 0;
2981 fnd_msg_pub.initialize;
2982
2983 l_modifier_list_rec.QP_LIST_HEADER_ID := p_object_id;
2984
2985 OPEN c_offer_info(p_object_id);
2986 FETCH c_offer_info INTO l_modifier_list_rec.offer_type,l_modifier_list_rec.custom_setup_id,
2987 l_modifier_list_rec.reusable,l_modifier_list_rec.offer_amount,l_offer_code,l_owner_id, l_offer_name;
2988 CLOSE c_offer_info;
2989
2990 ozf_utility_pvt.Write_Conc_log('offer_type = ' || l_modifier_list_rec.offer_type);
2991 ozf_utility_pvt.Write_Conc_log('custom_setup_id = ' || l_modifier_list_rec.custom_setup_id);
2992 ozf_utility_pvt.Write_Conc_log('reusable = ' || l_modifier_list_rec.reusable);
2993 ozf_utility_pvt.Write_Conc_log('offer_amount = ' || l_modifier_list_rec.offer_amount);
2994
2995 IF p_actbudget_id IS NOT NULL THEN
2996
2997 ozf_utility_pvt.Write_Conc_log('p_actbudget_id IS NOT NULL');
2998
2999 -- single budget request submission. called from ozf_actbudgets_pvt.
3000 validate_object_budget ( p_object_id => p_object_id,
3001 p_object_type => p_object_type,
3002 p_actbudget_id => p_actbudget_id,
3003 x_return_status => l_return_status,
3004 x_msg_count => l_msg_count,
3005 x_msg_data => l_msg_data);
3006
3007
3008 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budgets_rec);
3009 l_act_budgets_rec.activity_budget_id := p_actbudget_id;
3010
3011 IF l_return_status = fnd_api.G_RET_STS_SUCCESS THEN
3012 ozf_utility_pvt.Write_Conc_log('validation succeeds');
3013 -- validation succeeds. Change budget request status to 'APPROVED'
3014 l_act_budgets_rec.status_code := 'APPROVED';
3015 l_act_budgets_rec.user_status_id :=
3016 ozf_utility_pvt.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
3017
3018 /*bug 4662453
3019 IF l_offer_status <> 'DRAFT' THEN
3020 l_offer_status := 'ACTIVE';
3021 ozf_utility_pvt.Write_Conc_log('l_offer_status1 : ' || l_offer_status);
3022 END IF;
3023 */
3024
3025 ELSE
3026 ozf_utility_pvt.Write_Conc_log('validation fails');
3027 -- validation fail. Change budget request status to 'NEW'
3028 l_act_budgets_rec.status_code := 'NEW';
3029 l_act_budgets_rec.user_status_id :=
3030 ozf_utility_pvt.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
3031
3032 /*bug 4662453
3033 l_offer_status := 'DRAFT';
3034 ozf_utility_pvt.Write_Conc_log('l_offer_status2 : ' || l_offer_status);
3035 */
3036
3037 OPEN c_get_budget_name(p_actbudget_id);
3038 FETCH c_get_budget_name INTO l_budget_name;
3039 CLOSE c_get_budget_name;
3040
3041 -- send notification to offer owner of budget request validation failure
3042 fnd_message.set_name('OZF', 'OZF_OFFER_VALIDATION_SUBJECT');
3043 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
3044 l_strSubject := Substr(fnd_message.get,1,200);
3045
3046 fnd_message.set_name('OZF', 'OZF_TM_NOTIFY_HEADERLINE');
3047 l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10);
3048 fnd_message.set_name ('OZF', 'OZF_OFFER_VALIDATION_MESSAGE');
3049 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
3050 fnd_message.set_token ('FUND_NAME', l_budget_name, FALSE);
3051 fnd_message.set_token ('REQUEST_ID', p_actbudget_id, FALSE);
3052 l_strBody := l_strBody || Substr(fnd_message.get,1,200);
3053
3054 WHILE l_cnt < l_msg_count
3055 LOOP
3056 Fnd_Msg_Pub.Get
3057 (p_msg_index => l_cnt + 1,
3058 p_encoded => Fnd_Api.G_FALSE,
3059 p_data => l_msg_data,
3060 p_msg_index_out => l_msg_index );
3061
3062 --kdass fix for bug 4621638
3063 l_final_data := Substr((l_final_data || l_msg_index || ': ' || l_msg_data || Fnd_Global.local_chr(10)),1,1500);
3064 /*
3065 l_final_data := l_final_data ||l_msg_index||': '
3066 ||l_msg_data||Fnd_Global.local_chr(10) ;
3067 l_final_data := Substr(l_final_data,1,1500); -- fix bug 4032040
3068 */
3069 l_cnt := l_cnt + 1 ;
3070 END LOOP ;
3071
3072 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
3073 l_strBody := l_strBody || Substr(l_final_data,1,1500) ;
3074
3075 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
3076 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
3077
3078 ozf_utility_pvt.send_wf_standalone_message( p_subject => l_strSubject
3079 ,p_body => l_strBody
3080 ,p_send_to_res_id => l_owner_id
3081 ,x_notif_id => l_notification_id
3082 ,x_return_status => l_return_status
3083 );
3084
3085 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3086 ozf_utility_pvt.Write_Conc_log('Sent notification fails.');
3087 END IF;
3088
3089 END IF; -- end of validation fail.
3090
3091 ozf_actbudgets_pvt.Update_Act_Budgets (
3092 p_api_version => 1.0,
3093 p_init_msg_list => FND_API.g_false,
3094 p_commit => FND_API.g_false,
3095 p_validation_level => fnd_api.g_valid_level_full,
3096 x_return_status => l_return_status,
3097 x_msg_count => l_msg_count,
3098 x_msg_data => l_msg_data,
3099 p_act_budgets_rec => l_act_budgets_rec
3100 );
3101
3102 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3103 ozf_utility_pvt.Write_Conc_log('Update_Act_Budgets fails.');
3104 END IF;
3105
3106 ELSE -- called from offer activation. Check all budget requests of this offer
3107 ozf_utility_pvt.Write_Conc_log('p_actbudget_id IS NULL');
3108 ozf_utility_pvt.Write_Conc_log('Validate budget requests for offer id = ' || p_object_id);
3109 FOR request_rec IN c_get_requests LOOP
3110
3111 ozf_utility_pvt.Write_Conc_log('Valid budget request id = ' || request_rec.activity_budget_id);
3112
3113 validate_object_budget( p_object_id => request_rec.act_budget_used_by_id,
3114 p_object_type => request_rec.arc_act_budget_used_by,
3115 p_actbudget_id => request_rec.activity_budget_id,
3116 x_return_status => l_return_status,
3117 x_msg_count => l_msg_count,
3118 x_msg_data => l_msg_data);
3119
3120 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budgets_rec);
3121 l_act_budgets_rec.activity_budget_id := request_rec.activity_budget_id;
3122
3123 ozf_utility_pvt.Write_Conc_log('Validation return status = ' || l_return_status);
3124
3125 IF l_return_status = fnd_api.G_RET_STS_SUCCESS THEN
3126 ozf_utility_pvt.Write_Conc_log('validation succeeds');
3127
3128 -- validation succeeds. Change budget request status to 'APPROVED'
3129 l_act_budgets_rec.status_code := 'APPROVED';
3130 l_act_budgets_rec.user_status_id :=
3131 ozf_utility_pvt.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
3132
3133 IF l_offer_status <> 'DRAFT' THEN
3134 l_offer_status := 'ACTIVE';
3135 ozf_utility_pvt.Write_Conc_log('l_offer_status3 : ' || l_offer_status);
3136 END IF;
3137
3138 ELSE
3139 ozf_utility_pvt.Write_Conc_log('validation fails');
3140
3141 l_act_budgets_rec.status_code := 'NEW';
3142 l_offer_status := 'DRAFT';
3143 ozf_utility_pvt.Write_Conc_log('l_offer_status4 : ' || l_offer_status);
3144 l_act_budgets_rec.user_status_id :=
3145 ozf_utility_pvt.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
3146
3147 OPEN c_get_budget_name(request_rec.activity_budget_id);
3148 FETCH c_get_budget_name INTO l_budget_name;
3149 CLOSE c_get_budget_name;
3150
3151 fnd_message.set_name('OZF', 'OZF_OFFER_VALIDATION_SUBJECT');
3152 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
3153 l_strSubject := Substr(fnd_message.get,1,200);
3154
3155 fnd_message.set_name('OZF', 'OZF_TM_NOTIFY_HEADERLINE');
3156 l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10);
3157 fnd_message.set_name ('OZF', 'OZF_OFFER_VALIDATION_MESSAGE');
3158 fnd_message.set_token ('OFFER_NAME', l_offer_name, FALSE);
3159 fnd_message.set_token ('FUND_NAME', l_budget_name, FALSE);
3160 fnd_message.set_token ('REQUEST_ID', request_rec.activity_budget_id, FALSE);
3161 l_strBody := l_strBody || Substr(fnd_message.get,1,200);
3162
3163 WHILE l_cnt < l_msg_count
3164 LOOP
3165 Fnd_Msg_Pub.Get
3166 (p_msg_index => l_cnt + 1,
3167 p_encoded => Fnd_Api.G_FALSE,
3168 p_data => l_msg_data,
3169 p_msg_index_out => l_msg_index );
3170
3171 --kdass fix for bug 4621638
3172 l_final_data := Substr((l_final_data || l_msg_index || ': ' || l_msg_data || Fnd_Global.local_chr(10)),1,1500);
3173 /*
3174 l_final_data := l_final_data ||l_msg_index||': '
3175 ||l_msg_data||Fnd_Global.local_chr(10) ;
3176 l_final_data := Substr(l_final_data,1,1500); -- fix bug 4032040
3177 */
3178 l_cnt := l_cnt + 1 ;
3179 END LOOP ;
3180
3181 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
3182 l_strBody := l_strBody || Substr(l_final_data,1,1500) ;
3183
3184 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
3185 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
3186
3187 ozf_utility_pvt.send_wf_standalone_message( p_subject => l_strSubject
3188 ,p_body => l_strBody
3189 ,p_send_to_res_id => request_rec.requester_id
3190 ,x_notif_id => l_notification_id
3191 ,x_return_status => l_return_status
3192 );
3193
3194 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3195 ozf_utility_pvt.Write_Conc_log('Sent notification fails.');
3196 END IF;
3197
3198
3199 ozf_utility_pvt.Write_Conc_log('l_act_budgets_rec.activity_budget_id: ' || l_act_budgets_rec.activity_budget_id);
3200
3201 /*kdass 05-DEC-2005 bug 4662453 - Update_Act_Budgets is being called twice, so removing this one
3202 ozf_actbudgets_pvt.Update_Act_Budgets ( p_api_version => 1.0,
3203 p_init_msg_list => FND_API.g_false,
3204 p_commit => FND_API.g_false,
3205 p_validation_level => fnd_api.g_valid_level_full,
3206 x_return_status => l_return_status,
3207 x_msg_count => l_msg_count,
3208 x_msg_data => l_msg_data,
3209 p_act_budgets_rec => l_act_budgets_rec
3210 );
3211
3212 ozf_utility_pvt.Write_Conc_log('return status from Update_Act_Budgets = ' || l_return_status);
3213
3214 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3215 ozf_utility_pvt.Write_Conc_log('Update_Act_Budgets fails');
3216 END IF;
3217 */
3218
3219 END IF; -- end of validation fail
3220
3221 ozf_actbudgets_pvt.Update_Act_Budgets ( p_api_version => 1.0,
3222 p_init_msg_list => FND_API.g_false,
3223 p_commit => FND_API.g_false,
3224 p_validation_level => fnd_api.g_valid_level_full,
3225 x_return_status => l_return_status,
3226 x_msg_count => l_msg_count,
3227 x_msg_data => l_msg_data,
3228 p_act_budgets_rec => l_act_budgets_rec
3229 );
3230
3231 ozf_utility_pvt.Write_Conc_log('Update_Act_Budgets returns = ' || l_return_status);
3232
3233 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3234 ozf_utility_pvt.Write_Conc_log('Valid budget request id = ' || p_actbudget_id);
3235 ELSE
3236 ozf_utility_pvt.Write_Conc_log('Invalid budget request id = ' || p_actbudget_id);
3237 END IF;
3238
3239 END LOOP;
3240
3241 END IF; -- end of p_actbudget_id.
3242
3243 ozf_utility_pvt.Write_Conc_log('validate_object_budget_all start');
3244
3245 validate_object_budget_all ( p_object_id => p_object_id,
3246 p_object_type => p_object_type,
3247 x_return_status => l_return_status,
3248 x_msg_count => l_msg_count,
3249 x_msg_data => l_msg_data
3250 );
3251
3252 ozf_utility_pvt.Write_Conc_log('return status = ' || l_return_status);
3253
3254 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
3255 l_offer_status := 'DRAFT';
3256 ozf_utility_pvt.Write_Conc_log('Relaxed validation failed');
3257 END IF;
3258
3259 --kdass 05-DEC-2005 bug 4662453 - offer activation API should only be called from offer activation.
3260 IF p_actbudget_id IS NULL THEN
3261
3262 ozf_utility_pvt.Write_Conc_log('l_offer_status : ' || l_offer_status);
3263 l_modifier_list_rec.STATUS_CODE := l_offer_status;
3264 l_modifier_list_rec.USER_STATUS_ID :=
3265 ozf_utility_pvt.get_default_user_status ('OZF_OFFER_STATUS', l_modifier_list_rec.status_code);
3266
3267 ozf_utility_pvt.Write_Conc_log('STATUS_CODE = ' || l_modifier_list_rec.STATUS_CODE);
3268 ozf_utility_pvt.Write_Conc_log('USER_STATUS_ID = ' || l_modifier_list_rec.USER_STATUS_ID);
3269
3270 -- update offer status. if validation fail.
3271 --- otherwise to 'ACTIVE'
3272 Ozf_Offer_Pvt.Activate_Offer_Over( p_api_version => 1.0
3273 ,p_init_msg_list => FND_API.g_false
3274 ,p_commit => FND_API.g_false
3275 ,x_return_status => l_return_status
3276 ,x_msg_count => l_msg_count
3277 ,x_msg_data => l_msg_data
3278 ,p_called_from => 'R'
3279 ,p_offer_rec => l_modifier_list_rec
3280 ,x_amount_error => l_amount_error
3281 );
3282
3283 ozf_utility_pvt.Write_Conc_log('Activate_Offer_Over returns = ' || l_return_status);
3284
3285 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3286 ozf_utility_pvt.Write_Conc_log('exception raised');
3287 RAISE fnd_api.g_exc_error;
3288 END IF;
3289
3290 END IF;
3291
3292 COMMIT;
3293
3294 x_retcode := 0;
3295
3296 ozf_utility_pvt.Write_Conc_log(l_msg_data);
3297
3298 EXCEPTION
3299 WHEN fnd_api.g_exc_error THEN
3300 ozf_utility_pvt.Write_Conc_log('fnd_api.g_exc_error');
3301
3302 ROLLBACK TO conc_validate_offer_budget;
3303
3304 -- revert to draft status
3305 UPDATE ozf_offers
3306 SET status_code = 'DRAFT'
3307 ,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
3308 ,status_date = SYSDATE
3309 ,object_version_number = object_version_number + 1
3310 WHERE qp_list_header_id = l_modifier_list_rec.qp_list_header_id;
3311
3312 --kdass 05-DEC-2005 bug 4662453 - reverting budget line status from pending validation to draft
3313 IF p_actbudget_id IS NOT NULL THEN
3314 UPDATE ozf_act_budgets
3315 SET status_code = 'DRAFT'
3316 ,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', 'DRAFT')
3317 ,object_version_number = object_version_number + 1
3318 WHERE activity_budget_id = p_actbudget_id
3319 AND status_code = 'PENDING';
3320 ELSE
3321 FOR request_rec IN c_get_requests
3322 LOOP
3323 UPDATE ozf_act_budgets
3324 SET status_code = 'DRAFT'
3325 ,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', 'DRAFT')
3326 ,object_version_number = object_version_number + 1
3327 WHERE activity_budget_id = request_rec.activity_budget_id
3328 AND status_code = 'PENDING';
3329 END LOOP;
3330 END IF;
3331
3332 COMMIT;
3333
3334 -- send notifiction.
3335 WHILE l_cnt < l_msg_count
3336 LOOP
3337 Fnd_Msg_Pub.Get
3338 (p_msg_index => l_cnt + 1,
3339 p_encoded => Fnd_Api.G_FALSE,
3340 p_data => l_msg_data,
3341 p_msg_index_out => l_msg_index );
3342
3343 --kdass fix for bug 4621638
3344 l_final_data := Substr((l_final_data || l_msg_index || ': ' || l_msg_data || Fnd_Global.local_chr(10)),1,1500);
3345 /*
3346 l_final_data := l_final_data ||l_msg_index||': '
3347 ||l_msg_data||Fnd_Global.local_chr(10) ;
3348 */
3349 l_cnt := l_cnt + 1 ;
3350 END LOOP ;
3351
3352 fnd_message.set_name('OZF', 'OZF_TM_CONCURR_SUBJECT');
3353 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
3354 l_strSubject := Substr(fnd_message.get,1,200);
3355
3356 fnd_message.set_name('OZF', 'OZF_TM_NOTIFY_HEADERLINE');
3357 l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10);
3358 fnd_message.set_name ('OZF', 'OZF_TM_CONCURR_MESSAGE');
3359 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
3360 l_strBody := l_strBody || Substr(fnd_message.get,1,200);
3361
3362 WHILE l_cnt < l_msg_count
3363 LOOP
3364 Fnd_Msg_Pub.Get
3365 (p_msg_index => l_cnt + 1,
3366 p_encoded => Fnd_Api.G_FALSE,
3367 p_data => l_msg_data,
3368 p_msg_index_out => l_msg_index );
3369
3370 --kdass fix for bug 4621638
3371 l_final_data := Substr((l_final_data || l_msg_index || ': ' || l_msg_data || Fnd_Global.local_chr(10)),1,1500);
3372 /*
3373 l_final_data := l_final_data ||l_msg_index||': '
3374 ||l_msg_data||Fnd_Global.local_chr(10) ;
3375 */
3376 l_cnt := l_cnt + 1 ;
3377 END LOOP ;
3378 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
3379 l_strBody := l_strBody || Substr(l_final_data,1,1500) ;
3380
3381 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
3382 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
3383
3384 ozf_utility_pvt.send_wf_standalone_message( p_subject => l_strSubject
3385 ,p_body => l_strBody
3386 ,p_send_to_res_id => l_owner_id
3387 ,x_notif_id => l_notification_id
3388 ,x_return_status => l_return_status
3389 );
3390
3391 WHEN OTHERS THEN
3392 ozf_utility_pvt.Write_Conc_log('other exception');
3393 ROLLBACK TO conc_validate_offer_budget;
3394 x_retcode := 1;
3395 x_errbuf := l_msg_data;
3396 ozf_utility_pvt.write_conc_log (x_errbuf);
3397
3398 END conc_validate_offer_budget;
3399
3400 -------------------------------------------------------------------
3401 -- NAME
3402 -- budget_request_approval
3403 -- PURPOSE
3404 -- called by each activity update api to approval budget request
3405 -- when budget request approval is not required.
3406 -- History
3407 -- Created feliu 07/11/2002
3408 ----------------------------------------------------------------
3409
3410 PROCEDURE budget_request_approval(
3411 p_init_msg_list IN VARCHAR2,
3412 p_api_version IN NUMBER,
3413 p_commit IN VARCHAR2,
3414 x_return_status OUT NOCOPY VARCHAR2,
3415 x_msg_count OUT NOCOPY NUMBER,
3416 x_msg_data OUT NOCOPY VARCHAR2,
3417 p_object_type IN VARCHAR2,
3418 p_object_id IN NUMBER,
3419 x_status_code OUT NOCOPY VARCHAR2
3420 )IS
3421 -- Local variables
3422 l_api_name CONSTANT VARCHAR2(30) := 'budget_request_approval';
3423 l_full_name CONSTANT VARCHAR2(60)
3424 := G_PACKAGE_NAME || '.' || l_api_name;
3425 l_api_version CONSTANT NUMBER := 1.0;
3426 l_msg_count NUMBER;
3427 l_msg_data VARCHAR2(4000);
3428 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3429 l_object_version_number NUMBER;
3430 l_custom_setup_id NUMBER;
3431 -- Cursor to find fund details
3432 CURSOR c_act_budgets(p_object_type VARCHAR2,p_object_id NUMBER)
3433 IS
3434 select activity_budget_id
3435 from ozf_act_budgets
3436 where act_budget_used_by_id = p_object_id
3437 and arc_act_budget_used_by = p_object_type
3438 and transfer_type = 'REQUEST'
3439 and status_code = 'NEW';
3440
3441 CURSOR c_total_budgets(p_object_type VARCHAR2,p_object_id NUMBER)
3442 IS
3443 select NVL(SUM(request_amount),0)
3444 from ozf_act_budgets
3445 where act_budget_used_by_id = p_object_id
3446 and arc_act_budget_used_by = p_object_type
3447 and transfer_type = 'REQUEST';
3448
3449 CURSOR l_budget_required (p_custom_setup_id IN NUMBER) IS
3450 SELECT NVL(attr_available_flag,'N')
3451 FROM ams_custom_setup_attr
3452 WHERE custom_setup_id = p_custom_setup_id
3453 AND object_attribute = 'BREQ';
3454
3455 CURSOR c_offer_info(p_object_id IN NUMBER) IS
3456 SELECT NVL(offer_amount,0),owner_id,custom_setup_id,offer_code
3457 FROM ozf_offers
3458 WHERE qp_list_header_id = p_object_id;
3459
3460 l_act_budget_id NUMBER;
3461 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type ;
3462 l_check_validation VARCHAR2(50) := fnd_profile.value('OZF_CHECK_MKTG_PROD_ELIG');
3463 l_request_id NUMBER;
3464 l_status_code VARCHAR2(50):= 'APPROVED';
3465 l_total_budget NUMBER;
3466 l_recal_flag VARCHAR2(1):= NVL (fnd_profile.VALUE ('AMS_BUDGET_ADJ_ALLOW_RECAL'), 'N');
3467 l_offer_amount NUMBER;
3468 l_notify_message VARCHAR2(50);
3469 l_owner_id NUMBER;
3470 l_budget_req_flag VARCHAR2(1);
3471 l_strSubject VARCHAR2(300);
3472 l_strBody VARCHAR2(1000);
3473 l_notification_id NUMBER;
3474 l_offer_code VARCHAR2(50);
3475
3476 BEGIN
3477 x_return_status := fnd_api.g_ret_sts_success;
3478 -- Initialize
3479 IF G_DEBUG THEN
3480 ozf_utility_pvt.debug_message(l_full_name || ': start');
3481 END IF;
3482
3483 IF p_object_type = 'OFFR' THEN
3484
3485 OPEN c_total_budgets(p_object_type,p_object_id);
3486 FETCH c_total_budgets INTO l_total_budget;
3487 CLOSE c_total_budgets;
3488
3489 -- get offer information.
3490 OPEN c_offer_info(p_object_id);
3491 FETCH c_offer_info INTO l_offer_amount,l_owner_id,l_custom_setup_id,l_offer_code;
3492 CLOSE c_offer_info;
3493
3494 /* -- remove by feliu on 05/23/2006 according to offer's request.
3495 -- get budget required information.
3496 OPEN l_budget_required(l_custom_setup_id);
3497 FETCH l_budget_required INTO l_budget_req_flag;
3498 CLOSE l_budget_required;
3499 */
3500 -- IF l_budget_req_flag = 'Y' THEN -- required budget
3501 --IF l_total_budget > 0 THEN
3502 IF l_recal_flag = 'N' AND l_offer_amount > 0 AND l_offer_amount > l_total_budget THEN -- if there is offer committed amount.
3503 l_status_code := 'NEW';
3504 ELSE
3505 l_status_code := 'PENDING_VALIDATION';
3506 END IF; -- offer amount
3507 --END IF; -- end of total budget.
3508 -- ELSE -- for budget not required.
3509 -- IF l_total_budget > 0 THEN -- if there is budget line
3510 -- l_status_code := 'PENDING_VALIDATION';
3511 -- ELSE -- no budget line.
3512 -- l_status_code := 'APPROVED';
3513 -- END IF;
3514 -- END IF; -- budget required.
3515 ELSE -- for other object type.
3516 l_status_code := 'APPROVED';
3517 END IF; -- end of offer type.
3518 -- added by feliu on 05/05/04 for special pricing and softfund. exclude softunf and special pricing.
3519 IF (NVL(l_check_validation, 'NO') <> 'NO' AND l_status_code = 'PENDING_VALIDATION' AND NVL(l_custom_setup_id,0) NOT IN (110,115,116,117))
3520 OR NVL(l_custom_setup_id,0) = 118
3521 THEN
3522 l_status_code := 'PENDING_VALIDATION';
3523 ELSIF l_status_code = 'NEW' THEN
3524 l_status_code := 'NEW';
3525 ELSE
3526 l_status_code := 'APPROVED';
3527 END IF;
3528
3529
3530 IF l_status_code <> 'NEW' THEN
3531
3532 FOR actbudget_rec IN c_act_budgets(p_object_type,p_object_id)
3533 LOOP
3534 ozf_actbudgets_pvt.init_act_budgets_rec(l_act_budgets_rec);
3535 l_act_budgets_rec.activity_budget_id := actbudget_rec.activity_budget_id;
3536
3537 l_act_budgets_rec.status_code := 'APPROVED'; -- will changed to "PENDING_VALIDATION" by api.
3538 l_act_budgets_rec.user_status_id :=
3539 ozf_utility_pvt.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
3540
3541 ozf_actbudgets_pvt.update_act_budgets (
3542 p_api_version=> 1.0
3543 ,p_init_msg_list=> fnd_api.g_false
3544 ,p_commit=> fnd_api.g_false
3545 ,p_validation_level=> fnd_api.g_valid_level_full
3546 ,x_return_status=> l_return_status
3547 ,x_msg_data=> x_msg_data
3548 ,x_msg_count=> x_msg_count
3549 ,p_act_budgets_rec=> l_act_budgets_rec
3550 );
3551
3552 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3553 RAISE fnd_api.g_exc_unexpected_error;
3554 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3555 RAISE fnd_api.g_exc_error;
3556 END IF;
3557
3558 END LOOP;
3559
3560 END IF;
3561
3562 IF l_status_code = 'NEW' THEN
3563
3564 fnd_message.set_name('OZF', 'OZF_OFFER_VALIDATION_SUBJECT');
3565 fnd_message.set_token ('OFFER_CODE', l_offer_code, FALSE);
3566 l_strSubject := Substr(fnd_message.get,1,200);
3567
3568 fnd_message.set_name('OZF', 'OZF_TM_NOTIFY_HEADERLINE');
3569 l_strBody := fnd_message.get ||fnd_global.local_chr(10)||fnd_global.local_chr(10);
3570 fnd_message.set_name ('OZF', 'OZF_OFF_REQ_NOT_ENOUGH');
3571 l_strBody := l_strBody || Substr(fnd_message.get,1,200);
3572
3573 fnd_message.set_name('OZF', 'OZF_NOTIFY_FOOTER');
3574 l_strBody := l_strBody || fnd_global.local_chr(10) || fnd_global.local_chr(10) ||fnd_message.get ;
3575
3576 ozf_utility_pvt.send_wf_standalone_message(
3577 p_subject => l_strSubject
3578 ,p_body => l_strBody
3579 ,p_send_to_res_id => l_owner_id
3580 ,x_notif_id => l_notification_id
3581 ,x_return_status => l_return_status
3582 );
3583
3584 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3585 RAISE fnd_api.g_exc_error;
3586 END IF;
3587
3588 END IF; -- end of status of draft.
3589
3590 IF l_status_code = 'PENDING_VALIDATION' THEN
3591 l_request_id := fnd_request.submit_request (
3592 application => 'OZF',
3593 program => 'OZFVALIELIG',
3594 start_time => sysdate,
3595 argument1 => p_object_id,
3596 argument2 => p_object_type
3597 );
3598 COMMIT;
3599 IF l_request_id <> 0 THEN
3600 x_status_code := 'PENDING_VALIDATION';
3601 ELSE
3602 RAISE fnd_api.g_exc_error;
3603 END IF;
3604 ELSIF l_status_code = 'NEW' THEN
3605 x_status_code := 'DRAFT';
3606 ELSE
3607 x_status_code := 'ACTIVE';
3608 END IF;
3609
3610 IF G_DEBUG THEN
3611 ozf_utility_pvt.debug_message(l_full_name || ': end');
3612 END IF;
3613
3614 fnd_msg_pub.count_and_get(
3615 p_encoded => fnd_api.g_false
3616 ,p_count => x_msg_count
3617 ,p_data => x_msg_data);
3618
3619 EXCEPTION
3620 WHEN fnd_api.g_exc_error THEN
3621 x_return_status := fnd_api.g_ret_sts_error;
3622 fnd_msg_pub.count_and_get(
3623 p_encoded => fnd_api.g_false
3624 ,p_count => x_msg_count
3625 ,p_data => x_msg_data);
3626 WHEN fnd_api.g_exc_unexpected_error THEN
3627 x_return_status := fnd_api.g_ret_sts_unexp_error;
3628 fnd_msg_pub.count_and_get(
3629 p_encoded => fnd_api.g_false
3630 ,p_count => x_msg_count
3631 ,p_data => x_msg_data);
3632 WHEN OTHERS THEN
3633 x_return_status := fnd_api.g_ret_sts_unexp_error;
3634
3635 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3636 fnd_msg_pub.add_exc_msg(G_PACKAGE_NAME, l_api_name);
3637 END IF;
3638
3639 fnd_msg_pub.count_and_get(
3640 p_encoded => fnd_api.g_false
3641 ,p_count => x_msg_count
3642 ,p_data => x_msg_data);
3643 END budget_request_approval;
3644
3645
3646 -------------------------------------------------------------------
3647 -- NAME
3648 -- budget_request_approval
3649 -- PURPOSE
3650 -- called by each activity update api to approval budget request
3651 -- when budget request approval is not required.
3652 -- called by objects except offer.
3653 -- History
3654 -- Created feliu 07/11/2002
3655 ----------------------------------------------------------------
3656 PROCEDURE budget_request_approval(
3657 p_init_msg_list IN VARCHAR2,
3658 p_api_version IN NUMBER,
3659 p_commit IN VARCHAR2,
3660 x_return_status OUT NOCOPY VARCHAR2,
3661 x_msg_count OUT NOCOPY NUMBER,
3662 x_msg_data OUT NOCOPY VARCHAR2,
3663 p_object_type IN VARCHAR2,
3664 p_object_id IN NUMBER
3665 )IS
3666
3667 l_budget_status VARCHAR2(30);
3668 BEGIN
3669 budget_request_approval(
3670 p_init_msg_list => p_init_msg_list,
3671 p_api_version => p_api_version,
3672 p_commit => p_commit,
3673 x_return_status => x_return_status,
3674 x_msg_count => x_msg_count,
3675 x_msg_data => x_msg_data,
3676 p_object_type => p_object_type,
3677 p_object_id => p_object_id,
3678 x_status_code => l_budget_status
3679 );
3680
3681 END budget_request_approval;
3682
3683 END OZF_BudgetApproval_PVT;