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