[Home] [Help]
PACKAGE BODY: APPS.OZF_FUNDS_PUB
Source
1 PACKAGE BODY OZF_FUNDS_PUB AS
2 /* $Header: OZFPFUNB.pls 120.14.12020000.3 2013/03/10 03:22:44 bkunjan ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_FUNDS_PUB';
5 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- validate_fund_items
10 --
11 -- PURPOSE
12 -- Validate fund items.
13 --
14 -- PARAMETERS
15 -- p_fund_rec: fund record to be validated
16 -- p_mode: CREATE or UPDATE
17 -- x_return_status: return status
18 --
19 -- HISTORY
20 -- 06/29/2005 kdass Created
21 ---------------------------------------------------------------------
22 PROCEDURE validate_fund_items (
23 p_fund_rec IN OUT NOCOPY fund_rec_type
24 ,p_mode IN VARCHAR2
25 ,x_return_status OUT NOCOPY VARCHAR2
26 )
27 IS
28 l_api_name VARCHAR(30) := 'validate_fund_items';
29 l_fund_exists NUMBER := NULL;
30 l_cust_setup_exists NUMBER := NULL;
31 l_par_fund_exists NUMBER := NULL;
32 l_cat_exists NUMBER := NULL;
33 l_bus_unit_exists NUMBER := NULL;
34 l_thrh_exists NUMBER := NULL;
35 l_task_exists NUMBER := NULL;
36 l_org_exists NUMBER := NULL;
37 l_ledger_exists NUMBER := NULL;
38
39 CURSOR c_fund_exists (p_fund_id IN NUMBER) IS
40 SELECT 1
41 FROM ozf_funds_all_b
42 WHERE fund_type <> 'QUOTA'
43 AND fund_id = p_fund_id;
44
45 CURSOR c_fund_num_exists (p_fund_number IN VARCHAR2) IS
46 SELECT fund_id
47 FROM ozf_funds_all_b
48 WHERE fund_type <> 'QUOTA'
49 AND fund_number = p_fund_number;
50
51 CURSOR c_cust_setup (p_fund_type IN VARCHAR2) IS
52 SELECT min(custom_setup_id)
53 FROM ams_custom_setups_vl
54 WHERE object_type = 'FUND'
55 AND application_id = 682
56 AND activity_type_code = p_fund_type;
57
58 CURSOR c_cust_setup_exists (p_fund_type IN VARCHAR2, p_cust_setup_id IN NUMBER) IS
59 SELECT 1
60 FROM ams_custom_setups_vl
61 WHERE object_type = 'FUND'
62 AND application_id = 682
63 AND activity_type_code = p_fund_type
64 AND custom_setup_id = p_cust_setup_id;
65
66 CURSOR c_par_fund_id_exists (p_par_fund_id IN NUMBER, p_par_fund_name IN VARCHAR) IS
67 SELECT 1
68 FROM ozf_funds_all_vl
69 WHERE fund_type <> 'QUOTA'
70 AND fund_id = p_par_fund_id
71 AND short_name = p_par_fund_name;
72
73 CURSOR c_par_fund_exists (p_par_fund_id IN NUMBER) IS
74 SELECT 1
75 FROM ozf_funds_all_b
76 WHERE fund_type <> 'QUOTA'
77 AND fund_id = p_par_fund_id;
78
79 CURSOR c_par_fund_name_exists (p_par_fund_name IN VARCHAR2) IS
80 SELECT fund_id
81 FROM ozf_funds_all_vl
82 WHERE fund_type <> 'QUOTA'
83 AND short_name = p_par_fund_name;
84
85 CURSOR c_cat_exists (p_category_id IN NUMBER, p_category_name IN VARCHAR2) IS
86 SELECT 1
87 FROM ams_categories_vl
88 WHERE arc_category_created_for = 'FUND'
89 AND enabled_flag = 'Y'
90 AND category_name = p_category_name
91 AND category_id = p_category_id;
92
93 CURSOR c_cat_id_exists (p_category_id IN NUMBER) IS
94 SELECT 1
95 FROM ams_categories_vl
96 WHERE arc_category_created_for = 'FUND'
97 AND enabled_flag = 'Y'
98 AND category_id = p_category_id;
99
100 CURSOR c_cat_name_exists (p_category_name IN VARCHAR2) IS
101 SELECT category_id
102 FROM ams_categories_vl
103 WHERE arc_category_created_for = 'FUND'
104 AND enabled_flag = 'Y'
105 AND category_name = p_category_name;
106
107 CURSOR c_thrh_exists (p_threshold_id IN NUMBER, p_threshold_name IN VARCHAR2) IS
108 SELECT 1
109 FROM ozf_thresholds_vl
110 WHERE threshold_type = 'BUDGET'
111 AND end_date_active > sysdate
112 AND name = p_threshold_name
113 AND threshold_id = p_threshold_id;
114
115 CURSOR c_thrh_id_exists (p_threshold_id IN NUMBER) IS
116 SELECT 1
117 FROM ozf_thresholds_vl
118 WHERE threshold_type = 'BUDGET'
119 AND end_date_active > sysdate
120 AND threshold_id = p_threshold_id;
121
122 CURSOR c_thrh_name_exists (p_threshold_name IN VARCHAR2) IS
123 SELECT threshold_id
124 FROM ozf_thresholds_vl
125 WHERE threshold_type = 'BUDGET'
126 AND end_date_active > sysdate
127 AND name = p_threshold_name;
128
129 CURSOR c_task_exists (p_task_id IN NUMBER, p_task_name IN VARCHAR2) IS
130 SELECT 1
131 FROM ams_media_vl
132 WHERE media_type_code = 'DEAL'
133 AND media_name = p_task_name
134 AND media_id = p_task_id;
135
136 CURSOR c_task_id_exists (p_task_id IN NUMBER) IS
137 SELECT 1
138 FROM ams_media_vl
139 WHERE media_type_code = 'DEAL'
140 AND media_id = p_task_id;
141
142 CURSOR c_task_name_exists (p_task_name IN VARCHAR2) IS
143 SELECT media_id
144 FROM ams_media_vl
145 WHERE media_type_code = 'DEAL'
146 AND media_name = p_task_name;
147
148 CURSOR c_bus_unit_exists (p_bus_id IN NUMBER, p_bus_name IN VARCHAR, p_org_id IN NUMBER) IS
149 SELECT 1
150 FROM hr_all_organization_units
151 WHERE business_group_id
152 IN (SELECT business_group_id
153 FROM hr_all_organization_units
154 WHERE organization_id = p_org_id
155 AND NVL(date_from, SYSDATE) <= SYSDATE
156 AND NVL(date_to, SYSDATE) >= SYSDATE)
157 AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
158 AND NVL(date_to, SYSDATE) >= SYSDATE
159 AND name = p_bus_name
160 AND organization_id = p_bus_id;
161
162 CURSOR c_bus_id_exists (p_bus_id IN NUMBER, p_org_id IN NUMBER) IS
163 SELECT 1
164 FROM hr_all_organization_units
165 WHERE business_group_id
166 IN (SELECT business_group_id
167 FROM hr_all_organization_units
168 WHERE organization_id = p_org_id
169 AND NVL(date_from, SYSDATE) <= SYSDATE
170 AND NVL(date_to, SYSDATE) >= SYSDATE)
171 AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
172 AND NVL(date_to, SYSDATE) >= SYSDATE
173 AND organization_id = p_bus_id;
174
175 CURSOR c_bus_name_exists (p_bus_name IN VARCHAR2, p_org_id IN NUMBER) IS
176 SELECT organization_id
177 FROM hr_all_organization_units
178 WHERE business_group_id
179 IN (SELECT business_group_id
180 FROM hr_all_organization_units
181 WHERE organization_id = p_org_id
182 AND NVL(date_from, SYSDATE) <= SYSDATE
183 AND NVL(date_to, SYSDATE) >= SYSDATE)
184 AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
185 AND NVL(date_to, SYSDATE) >= SYSDATE
186 AND name = p_bus_name;
187
188 CURSOR c_user_status_id (p_status_code IN VARCHAR2) IS
189 SELECT user_status_id
190 FROM ams_user_statuses_vl
191 WHERE system_status_type = 'OZF_FUND_STATUS'
192 AND system_status_code = p_status_code
193 AND enabled_flag ='Y';
194
195 CURSOR c_ledger_exists (p_ledger_id IN NUMBER, p_ledger_name IN VARCHAR2) IS
196 SELECT 1
197 FROM gl_ledgers_public_v
198 WHERE ledger_id = p_ledger_id
199 AND name = p_ledger_name;
200
201 CURSOR c_ledger_id_exists (p_ledger_id IN NUMBER) IS
202 SELECT 1
203 FROM gl_ledgers_public_v
204 WHERE ledger_id = p_ledger_id;
205
206 CURSOR c_ledger_name_exists (p_ledger_name IN VARCHAR2) IS
207 SELECT ledger_id
208 FROM gl_ledgers_public_v
209 WHERE name = p_ledger_name;
210
211 BEGIN
212
213 IF p_fund_rec.fund_type <> fnd_api.g_miss_char AND p_fund_rec.fund_type IS NOT NULL
214 AND p_fund_rec.fund_type = 'QUOTA' THEN
215 RETURN;
216 END IF;
217
218 IF p_mode = 'CREATE' THEN
219 p_fund_rec.fund_id := NULL;
220 ELSE
221 --if both fund id and fund number are null, then raise exception
222 IF (p_fund_rec.fund_id = fnd_api.g_miss_num OR p_fund_rec.fund_id IS NULL) AND
223 (p_fund_rec.fund_number = fnd_api.g_miss_char OR p_fund_rec.fund_number IS NULL) THEN
224
225 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
226 fnd_message.set_name('OZF', 'OZF_NO_FUND_ID_NUM');
227 fnd_msg_pub.add;
228 END IF;
229 x_return_status := fnd_api.g_ret_sts_error;
230 RETURN;
231
232 ELSE
233 --if fund id is not null
234 IF p_fund_rec.fund_id <> fnd_api.g_miss_num AND p_fund_rec.fund_id IS NOT NULL THEN
235
236 --check if the input fund_id is valid
237 OPEN c_fund_exists (p_fund_rec.fund_id);
238 FETCH c_fund_exists INTO l_fund_exists;
239 CLOSE c_fund_exists;
240
241 IF l_fund_exists IS NULL THEN
242 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
243 fnd_message.set_name('OZF', 'OZF_INVALID_FUND_ID');
244 fnd_msg_pub.add;
245 END IF;
246 x_return_status := fnd_api.g_ret_sts_error;
247 RETURN;
248 END IF;
249
250 --if fund number is not null
251 ELSE
252 --check if the input fund_number is valid
253 OPEN c_fund_num_exists (p_fund_rec.fund_number);
254 FETCH c_fund_num_exists INTO p_fund_rec.fund_id;
255 CLOSE c_fund_num_exists;
256
257 IF p_fund_rec.fund_id IS NULL THEN
258 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
259 fnd_message.set_name('OZF', 'OZF_INVALID_FUND_NUM');
260 fnd_msg_pub.add;
261 END IF;
262 x_return_status := fnd_api.g_ret_sts_error;
263 RETURN;
264 END IF;
265 END IF;
266 END IF;
267 END IF;
268
269 --if fund name is null, then raise exception
270 IF p_fund_rec.short_name = fnd_api.g_miss_char OR p_fund_rec.short_name IS NULL THEN
271 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
272 fnd_message.set_name('OZF', 'OZF_NO_FUND_NAME');
273 fnd_msg_pub.add;
274 END IF;
275 x_return_status := fnd_api.g_ret_sts_error;
276 RETURN;
277 END IF;
278
279 --if fund type is null, then raise exception
280 IF p_fund_rec.fund_type = fnd_api.g_miss_char OR p_fund_rec.fund_type IS NULL THEN
281 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
282 fnd_message.set_name('OZF', 'OZF_NO_FUND_TYPE');
283 fnd_msg_pub.add;
284 END IF;
285 x_return_status := fnd_api.g_ret_sts_error;
286 RETURN;
287 ELSE
288 IF p_fund_rec.fund_type NOT IN ('FIXED', 'FULLY_ACCRUED') THEN
289 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
290 fnd_message.set_name('OZF', 'OZF_INVALID_FUND_TYPE');
291 fnd_msg_pub.add;
292 END IF;
293 x_return_status := fnd_api.g_ret_sts_error;
294 RETURN;
295 END IF;
296 END IF;
297
298 --28-AUG-08 kdass - bug 7343771: accept custom setup passed to this API
299 IF p_mode = 'CREATE' THEN
300 IF (p_fund_rec.custom_setup_id = fnd_api.g_miss_num OR p_fund_rec.custom_setup_id IS NULL) THEN
301
302 OPEN c_cust_setup (p_fund_rec.fund_type);
303 FETCH c_cust_setup INTO p_fund_rec.custom_setup_id;
304 CLOSE c_cust_setup;
305
306 ELSE
307
308 OPEN c_cust_setup_exists (p_fund_rec.fund_type, p_fund_rec.custom_setup_id);
309 FETCH c_cust_setup_exists INTO l_cust_setup_exists;
310 CLOSE c_cust_setup_exists;
311
312 IF l_cust_setup_exists IS NULL THEN
313 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
314 fnd_message.set_name('OZF', 'OZF_INVALID_SETUP_ID');
315 fnd_msg_pub.add;
316 END IF;
317 x_return_status := fnd_api.g_ret_sts_error;
318 RETURN;
319 END IF;
320 END IF;
321 END IF;
322
323 IF p_fund_rec.parent_fund_id <> fnd_api.g_miss_num AND p_fund_rec.parent_fund_id IS NOT NULL THEN
324 --check if the input parent fund id is valid
325 OPEN c_par_fund_exists (p_fund_rec.parent_fund_id);
326 FETCH c_par_fund_exists INTO l_par_fund_exists;
327 CLOSE c_par_fund_exists;
328
329 IF l_par_fund_exists IS NULL THEN -- HBANDI(21-JUNE-1010) Changed the condition l_fund_exists to l_par_fund_exists for resolving the issue #9578854.
330 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
331 fnd_message.set_name('OZF', 'OZF_INVALID_PAR_FUND_ID');
332 fnd_msg_pub.add;
333 END IF;
334 x_return_status := fnd_api.g_ret_sts_error;
335 RETURN;
336 END IF;
337 END IF;
338
339 IF (p_fund_rec.parent_fund_name <> fnd_api.g_miss_char AND p_fund_rec.parent_fund_name IS NOT NULL) AND
340 (p_fund_rec.parent_fund_id <> fnd_api.g_miss_num AND p_fund_rec.parent_fund_id IS NOT NULL) THEN
341
342 --check if the input parent fund id is valid
343 OPEN c_par_fund_id_exists (p_fund_rec.parent_fund_id, p_fund_rec.parent_fund_name);
344 FETCH c_par_fund_id_exists INTO l_par_fund_exists;
345 CLOSE c_par_fund_id_exists;
346
347 IF l_par_fund_exists IS NULL THEN
348 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
349 fnd_message.set_name('OZF', 'OZF_INVALID_PAR_FUND_ID_NAME');
350 fnd_msg_pub.add;
351 END IF;
352 x_return_status := fnd_api.g_ret_sts_error;
353 RETURN;
354 END IF;
355
356 ELSIF p_fund_rec.parent_fund_id <> fnd_api.g_miss_num AND p_fund_rec.parent_fund_id IS NOT NULL THEN
357
358 --check if the input parent fund id is valid
359 OPEN c_par_fund_exists (p_fund_rec.parent_fund_id);
360 FETCH c_par_fund_exists INTO l_par_fund_exists;
361 CLOSE c_par_fund_exists;
362
363 IF l_par_fund_exists IS NULL THEN
364 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
365 fnd_message.set_name('OZF', 'OZF_INVALID_PAR_FUND_ID');
366 fnd_msg_pub.add;
367 END IF;
368 x_return_status := fnd_api.g_ret_sts_error;
369 RETURN;
370 END IF;
371
372 ELSIF p_fund_rec.parent_fund_name <> fnd_api.g_miss_char AND p_fund_rec.parent_fund_name IS NOT NULL THEN
373
374 --check if the input parent fund name is valid
375 OPEN c_par_fund_name_exists (p_fund_rec.parent_fund_name);
376 FETCH c_par_fund_name_exists INTO p_fund_rec.parent_fund_id;
377 CLOSE c_par_fund_name_exists;
378
379 IF p_fund_rec.parent_fund_id IS NULL THEN
380 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
381 fnd_message.set_name('OZF', 'OZF_INVALID_PAR_FUND_NAME');
382 fnd_msg_pub.add;
383 END IF;
384 x_return_status := fnd_api.g_ret_sts_error;
385 RETURN;
386 END IF;
387 END IF;
388
389 IF (p_fund_rec.category_name = fnd_api.g_miss_char OR p_fund_rec.category_name IS NULL) AND
390 (p_fund_rec.category_id = fnd_api.g_miss_num OR p_fund_rec.category_id IS NULL) THEN
391
392 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
393 fnd_message.set_name('OZF', 'OZF_NO_CAT_ID_NAME');
394 fnd_msg_pub.add;
395 END IF;
396 x_return_status := fnd_api.g_ret_sts_error;
397 RETURN;
398
399 ELSIF (p_fund_rec.category_name <> fnd_api.g_miss_char AND p_fund_rec.category_name IS NOT NULL) AND
400 (p_fund_rec.category_id <> fnd_api.g_miss_num AND p_fund_rec.category_id IS NOT NULL) THEN
401
402 --check if the input category id and name are valid
403 OPEN c_cat_exists (p_fund_rec.category_id, p_fund_rec.category_name);
404 FETCH c_cat_exists INTO l_cat_exists;
405 CLOSE c_cat_exists;
406
407 IF l_cat_exists IS NULL THEN
408 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
409 fnd_message.set_name('OZF', 'OZF_INVALID_CAT_ID_NAME');
410 fnd_msg_pub.add;
411 END IF;
412 x_return_status := fnd_api.g_ret_sts_error;
413 RETURN;
414 END IF;
415
416 ELSIF p_fund_rec.category_id <> fnd_api.g_miss_num AND p_fund_rec.category_id IS NOT NULL THEN
417
418 --check if the input category id is valid
419 OPEN c_cat_id_exists (p_fund_rec.category_id);
420 FETCH c_cat_id_exists INTO l_cat_exists;
421 CLOSE c_cat_id_exists;
422
423 IF l_cat_exists IS NULL THEN
424 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
425 fnd_message.set_name('OZF', 'OZF_INVALID_CAT_ID');
426 fnd_msg_pub.add;
427 END IF;
428 x_return_status := fnd_api.g_ret_sts_error;
429 RETURN;
430 END IF;
431
432 ELSIF p_fund_rec.category_name <> fnd_api.g_miss_char AND p_fund_rec.category_name IS NOT NULL THEN
433
434 --check if the input parent category name is valid
435 OPEN c_cat_name_exists (p_fund_rec.category_name);
436 FETCH c_cat_name_exists INTO p_fund_rec.category_id;
437 CLOSE c_cat_name_exists;
438
439 IF p_fund_rec.category_id IS NULL THEN
440 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
441 fnd_message.set_name('OZF', 'OZF_INVALID_CAT_NAME');
442 fnd_msg_pub.add;
443 END IF;
444 x_return_status := fnd_api.g_ret_sts_error;
445 RETURN;
446 END IF;
447 END IF;
448
449 IF p_mode = 'CREATE' THEN
450 p_fund_rec.org_id := MO_UTILS.get_default_org_id;
451
452 IF p_fund_rec.org_id = fnd_api.g_miss_num OR p_fund_rec.org_id IS NULL THEN
453 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
454 fnd_message.set_name('OZF', 'OZF_NO_DEFAULT_ORG_ID');
455 fnd_msg_pub.add;
456 END IF;
457 END IF;
458 END IF;
459
460 IF (p_fund_rec.business_unit <> fnd_api.g_miss_char AND p_fund_rec.business_unit IS NOT NULL) AND
461 (p_fund_rec.business_unit_id <> fnd_api.g_miss_num AND p_fund_rec.business_unit_id IS NOT NULL) THEN
462
463 --check if the input business unit id and name are valid
464 OPEN c_bus_unit_exists (p_fund_rec.business_unit_id, p_fund_rec.business_unit, p_fund_rec.org_id);
465 FETCH c_bus_unit_exists INTO l_bus_unit_exists;
466 CLOSE c_bus_unit_exists;
467
468 IF l_bus_unit_exists IS NULL THEN
469 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
470 fnd_message.set_name('OZF', 'OZF_INVALID_BUS_UNIT_ID_NAME');
471 fnd_msg_pub.add;
472 END IF;
473 x_return_status := fnd_api.g_ret_sts_error;
474 RETURN;
475 END IF;
476
477 ELSIF p_fund_rec.business_unit_id <> fnd_api.g_miss_num AND p_fund_rec.business_unit_id IS NOT NULL THEN
478
479 --check if the input business_unit_id is valid
480 OPEN c_bus_id_exists (p_fund_rec.business_unit_id, p_fund_rec.org_id);
481 FETCH c_bus_id_exists INTO l_bus_unit_exists;
482 CLOSE c_bus_id_exists;
483
484 IF l_bus_unit_exists IS NULL THEN
485 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
486 fnd_message.set_name('OZF', 'OZF_INVALID_BUS_UNIT_ID');
487 fnd_msg_pub.add;
488 END IF;
489 x_return_status := fnd_api.g_ret_sts_error;
490 RETURN;
491 END IF;
492
493 ELSIF p_fund_rec.business_unit <> fnd_api.g_miss_char AND p_fund_rec.business_unit IS NOT NULL THEN
494
495 --check if the input business unit name is valid
496 OPEN c_bus_name_exists (p_fund_rec.business_unit, p_fund_rec.org_id);
497 FETCH c_bus_name_exists INTO p_fund_rec.business_unit_id;
498 CLOSE c_bus_name_exists;
499
500 IF p_fund_rec.business_unit_id IS NULL THEN
501 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
502 fnd_message.set_name('OZF', 'OZF_INVALID_BUS_UNIT');
503 fnd_msg_pub.add;
504 END IF;
505 x_return_status := fnd_api.g_ret_sts_error;
506 RETURN;
507 END IF;
508 END IF;
509
510 IF p_mode = 'CREATE' THEN
511 p_fund_rec.status_code := 'DRAFT';
512 END IF;
513
514 --if status code is not null, then get the user status id
515 IF p_fund_rec.status_code <> fnd_api.g_miss_char AND p_fund_rec.status_code IS NOT NULL THEN
516 OPEN c_user_status_id (p_fund_rec.status_code);
517 FETCH c_user_status_id INTO p_fund_rec.user_status_id;
518 CLOSE c_user_status_id;
519 END IF;
520
521 --if currency code is null, then raise exception
522 IF p_fund_rec.currency_code_tc = fnd_api.g_miss_char OR p_fund_rec.currency_code_tc IS NULL THEN
523 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
524 fnd_message.set_name('OZF', 'OZF_NO_CURR_CODE');
525 fnd_msg_pub.add;
526 END IF;
527 x_return_status := fnd_api.g_ret_sts_error;
528 RETURN;
529 END IF;
530
531 IF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
532
533 IF p_fund_rec.accrual_basis = fnd_api.g_miss_char OR p_fund_rec.accrual_basis IS NULL THEN
534 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
535 fnd_message.set_name('OZF', 'OZF_NO_ACCR_BASIS');
536 fnd_msg_pub.add;
537 END IF;
538 x_return_status := fnd_api.g_ret_sts_error;
539 RETURN;
540 ELSE
541 IF p_fund_rec.accrual_basis NOT IN ('CUSTOMER', 'SALES') THEN
542 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
543 fnd_message.set_name('OZF', 'OZF_INVALID_ACCR_BASIS');
544 fnd_msg_pub.add;
545 END IF;
546 x_return_status := fnd_api.g_ret_sts_error;
547 RETURN;
548 END IF;
549 END IF;
550
551 IF p_fund_rec.accrual_phase = fnd_api.g_miss_char OR p_fund_rec.accrual_phase IS NULL THEN
552 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
553 fnd_message.set_name('OZF', 'OZF_NO_ACCR_PHASE');
554 fnd_msg_pub.add;
555 END IF;
556 x_return_status := fnd_api.g_ret_sts_error;
557 RETURN;
558 ELSE
559 IF p_fund_rec.accrual_phase NOT IN ('ACCRUAL', 'VOLUME') THEN
560 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
561 fnd_message.set_name('OZF', 'OZF_INVALID_ACCR_PHASE');
562 fnd_msg_pub.add;
563 END IF;
564 x_return_status := fnd_api.g_ret_sts_error;
565 RETURN;
566 END IF;
567 END IF;
568
569 IF p_fund_rec.accrual_discount_level = fnd_api.g_miss_char OR p_fund_rec.accrual_discount_level IS NULL THEN
570 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
571 fnd_message.set_name('OZF', 'OZF_NO_DISC_LEVEL');
572 fnd_msg_pub.add;
573 END IF;
574 x_return_status := fnd_api.g_ret_sts_error;
575 RETURN;
576 ELSE
577 IF p_fund_rec.accrual_discount_level NOT IN ('LINE', 'LINEGROUP') THEN
578 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
579 fnd_message.set_name('OZF', 'OZF_INVALID_DISC_LEVEL');
580 fnd_msg_pub.add;
581 END IF;
582 x_return_status := fnd_api.g_ret_sts_error;
583 RETURN;
584 END IF;
585 END IF;
586
587 IF p_fund_rec.accrual_basis = 'CUSTOMER' THEN
588 p_fund_rec.liability_flag := NVL(p_fund_rec.liability_flag, 'Y');
589 ELSE
590 p_fund_rec.liability_flag := NVL(p_fund_rec.liability_flag, 'N');
591 END IF;
592
593 IF p_fund_rec.liability_flag NOT IN ('Y', 'N') THEN
594 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
595 fnd_message.set_name('OZF', 'OZF_INVALID_LIAB_FLAG');
596 fnd_msg_pub.add;
597 END IF;
598 x_return_status := fnd_api.g_ret_sts_error;
599 RETURN;
600 END IF;
601
602 END IF;
603
604 IF (p_fund_rec.threshold_id <> fnd_api.g_miss_num AND p_fund_rec.threshold_id IS NOT NULL) AND
605 (p_fund_rec.threshold_name <> fnd_api.g_miss_char AND p_fund_rec.threshold_name IS NOT NULL) THEN
606
607 --check if the input threshold id and name are valid
608 OPEN c_thrh_exists (p_fund_rec.threshold_id, p_fund_rec.threshold_name);
609 FETCH c_thrh_exists INTO l_thrh_exists;
610 CLOSE c_thrh_exists;
611
612 IF l_thrh_exists IS NULL THEN
613 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
614 fnd_message.set_name('OZF', 'OZF_INVALID_BUS_UNIT_ID_NAME');
615 fnd_msg_pub.add;
616 END IF;
617 x_return_status := fnd_api.g_ret_sts_error;
618 RETURN;
619 END IF;
620
621 ELSIF p_fund_rec.threshold_id <> fnd_api.g_miss_num AND p_fund_rec.threshold_id IS NOT NULL THEN
622
623 --check if the input threshold id is valid
624 OPEN c_thrh_id_exists (p_fund_rec.threshold_id);
625 FETCH c_thrh_id_exists INTO l_thrh_exists;
626 CLOSE c_thrh_id_exists;
627
628 IF l_thrh_exists IS NULL THEN
629 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
630 fnd_message.set_name('OZF', 'OZF_INVALID_THRH_ID');
631 fnd_msg_pub.add;
632 END IF;
633 x_return_status := fnd_api.g_ret_sts_error;
634 RETURN;
635 END IF;
636
637 ELSIF p_fund_rec.threshold_name <> fnd_api.g_miss_char AND p_fund_rec.threshold_name IS NOT NULL THEN
638
639 --check if the input threshold name is valid
640 OPEN c_thrh_name_exists (p_fund_rec.threshold_name);
641 FETCH c_thrh_name_exists INTO p_fund_rec.threshold_id;
642 CLOSE c_thrh_name_exists;
643
644 IF p_fund_rec.threshold_id IS NULL THEN
645 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
646 fnd_message.set_name('OZF', 'OZF_INVALID_THRH_NAME');
647 fnd_msg_pub.add;
648 END IF;
649 x_return_status := fnd_api.g_ret_sts_error;
650 RETURN;
651 END IF;
652 END IF;
653
654 IF (p_fund_rec.task_id <> fnd_api.g_miss_num AND p_fund_rec.task_id IS NOT NULL) AND
655 (p_fund_rec.task_name <> fnd_api.g_miss_char AND p_fund_rec.task_name IS NOT NULL) THEN
656
657 --check if the input task id and name are valid
658 OPEN c_task_exists (p_fund_rec.task_id, p_fund_rec.task_name);
659 FETCH c_task_exists INTO l_task_exists;
660 CLOSE c_task_exists;
661
662 IF l_task_exists IS NULL THEN
663 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
664 fnd_message.set_name('OZF', 'OZF_INVALID_TASK_ID_NAME');
665 fnd_msg_pub.add;
666 END IF;
667 x_return_status := fnd_api.g_ret_sts_error;
668 RETURN;
669 END IF;
670
671 ELSIF p_fund_rec.task_id <> fnd_api.g_miss_num AND p_fund_rec.task_id IS NOT NULL THEN
672
673 --check if the input task id is valid
674 OPEN c_task_id_exists (p_fund_rec.task_id);
675 FETCH c_task_id_exists INTO l_task_exists;
676 CLOSE c_task_id_exists;
677
678 IF l_task_exists IS NULL THEN
679 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
680 fnd_message.set_name('OZF', 'OZF_INVALID_TASK_ID');
681 fnd_msg_pub.add;
682 END IF;
683 x_return_status := fnd_api.g_ret_sts_error;
684 RETURN;
685 END IF;
686
687 ELSIF p_fund_rec.task_name <> fnd_api.g_miss_char AND p_fund_rec.task_name IS NOT NULL THEN
688
689 --check if the input task name is valid
690 OPEN c_task_name_exists (p_fund_rec.task_name);
691 FETCH c_task_name_exists INTO p_fund_rec.task_id;
692 CLOSE c_task_name_exists;
693
694 IF p_fund_rec.task_id IS NULL THEN
695 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
696 fnd_message.set_name('OZF', 'OZF_INVALID_TASK_NAME');
697 fnd_msg_pub.add;
698 END IF;
699 x_return_status := fnd_api.g_ret_sts_error;
700 RETURN;
701 END IF;
702 END IF;
703
704 IF (p_fund_rec.ledger_id <> fnd_api.g_miss_num AND p_fund_rec.ledger_id IS NOT NULL) AND
705 (p_fund_rec.ledger_name <> fnd_api.g_miss_char AND p_fund_rec.ledger_name IS NOT NULL) THEN
706
707 --check if the input ledger id and name are valid
708 OPEN c_ledger_exists (p_fund_rec.ledger_id, p_fund_rec.ledger_name);
709 FETCH c_ledger_exists INTO l_ledger_exists;
710 CLOSE c_ledger_exists;
711
712 IF l_ledger_exists IS NULL THEN
713 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
714 fnd_message.set_name('OZF', 'OZF_INVALID_LEDGER_ID_NAME');
715 fnd_msg_pub.add;
716 END IF;
717 x_return_status := fnd_api.g_ret_sts_error;
718 RETURN;
719 END IF;
720
721 ELSIF p_fund_rec.ledger_id <> fnd_api.g_miss_num AND p_fund_rec.ledger_id IS NOT NULL THEN
722
723 --check if the input ledger id is valid
724 OPEN c_ledger_id_exists (p_fund_rec.ledger_id);
725 FETCH c_ledger_id_exists INTO l_ledger_exists;
726 CLOSE c_ledger_id_exists;
727
728 IF l_ledger_exists IS NULL THEN
729 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
730 fnd_message.set_name('OZF', 'OZF_INVALID_LEDGER_ID');
731 fnd_msg_pub.add;
732 END IF;
733 x_return_status := fnd_api.g_ret_sts_error;
734 RETURN;
735 END IF;
736
737 ELSIF p_fund_rec.ledger_name <> fnd_api.g_miss_char AND p_fund_rec.ledger_name IS NOT NULL THEN
738
739 --check if the input ledger name is valid
740 OPEN c_ledger_name_exists (p_fund_rec.ledger_name);
741 FETCH c_ledger_name_exists INTO p_fund_rec.ledger_id;
742 CLOSE c_ledger_name_exists;
743
744 IF p_fund_rec.ledger_id IS NULL THEN
745 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
746 fnd_message.set_name('OZF', 'OZF_INVALID_LEDGER_NAME');
747 fnd_msg_pub.add;
748 END IF;
749 x_return_status := fnd_api.g_ret_sts_error;
750 RETURN;
751 END IF;
752 END IF;
753
754 END validate_fund_items;
755
756 ---------------------------------------------------------------------
757 -- PROCEDURE
758 -- Create_Fund
759 --
760 -- PURPOSE
761 -- Create a new fund (fixed budget).
762 --
763 -- PARAMETERS
764 -- p_fund_rec: the new record to be inserted
765 -- x_fund_id: return the fund_id of the new fund
766 ---------------------------------------------------------------------
767 PROCEDURE Create_fund(
768 p_api_version IN NUMBER
769 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
770 ,p_commit IN VARCHAR2 := fnd_api.g_false
771 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
772 ,x_return_status OUT NOCOPY VARCHAR2
773 ,x_msg_count OUT NOCOPY NUMBER
774 ,x_msg_data OUT NOCOPY VARCHAR2
775 ,p_fund_rec IN fund_rec_type
776 ,x_fund_id OUT NOCOPY NUMBER
777 )
778 IS
779 l_modifier_list_rec ozf_offer_pub.modifier_list_rec_type;
780 l_modifier_line_tbl ozf_offer_pub.modifier_line_tbl_type;
781 l_vo_pbh_tbl ozf_offer_pub.vo_disc_tbl_type;
782 l_vo_dis_tbl ozf_offer_pub.vo_disc_tbl_type;
783 l_vo_prod_tbl ozf_offer_pub.vo_prod_tbl_type;
784 l_qualifier_tbl ozf_offer_pub.qualifiers_tbl_type;
785 l_vo_mo_tbl ozf_offer_pub.vo_mo_tbl_type;
786
787 BEGIN
788
789 create_fund(p_api_version => p_api_version
790 ,p_init_msg_list => p_init_msg_list
791 ,p_commit => p_commit
792 ,p_validation_level => p_validation_level
793 ,x_return_status => x_return_status
794 ,x_msg_count => x_msg_count
795 ,x_msg_data => x_msg_data
796 ,p_fund_rec => p_fund_rec
797 ,p_modifier_list_rec => l_modifier_list_rec
798 ,p_modifier_line_tbl => l_modifier_line_tbl
799 ,p_vo_pbh_tbl => l_vo_pbh_tbl
800 ,p_vo_dis_tbl => l_vo_dis_tbl
801 ,p_vo_prod_tbl => l_vo_prod_tbl
802 ,p_qualifier_tbl => l_qualifier_tbl
803 ,p_vo_mo_tbl => l_vo_mo_tbl
804 ,x_fund_id => x_fund_id
805 );
806
807 END Create_fund;
808
809 ---------------------------------------------------------------------
810 -- PROCEDURE
811 -- Create_Fund
812 --
813 -- PURPOSE
814 -- Create a new fund (fully accrued budget).
815 --
816 -- PARAMETERS
817 -- p_fund_rec: the new record to be inserted
818 -- x_fund_id: return the fund_id of the new fund
819 ---------------------------------------------------------------------
820 PROCEDURE Create_fund(
821 p_api_version IN NUMBER
822 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
823 ,p_commit IN VARCHAR2 := fnd_api.g_false
824 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
825 ,x_return_status OUT NOCOPY VARCHAR2
826 ,x_msg_count OUT NOCOPY NUMBER
827 ,x_msg_data OUT NOCOPY VARCHAR2
828 ,p_fund_rec IN fund_rec_type
829 ,p_modifier_list_rec IN ozf_offer_pub.modifier_list_rec_type
830 ,p_modifier_line_tbl IN ozf_offer_pub.modifier_line_tbl_type
831 ,p_vo_pbh_tbl IN ozf_offer_pub.vo_disc_tbl_type
832 ,p_vo_dis_tbl IN ozf_offer_pub.vo_disc_tbl_type
833 ,p_vo_prod_tbl IN ozf_offer_pub.vo_prod_tbl_type
834 ,p_qualifier_tbl IN ozf_offer_pub.qualifiers_tbl_type
835 ,p_vo_mo_tbl IN ozf_offer_pub.vo_mo_tbl_type
836 ,x_fund_id OUT NOCOPY NUMBER
837 )
838 IS
839 l_api_name VARCHAR(30) := 'Create_Fund';
840 l_fund_rec OZF_FUNDS_PUB.fund_rec_type := p_fund_rec;
841 l_pvt_fund_rec OZF_Funds_PVT.fund_rec_type;
842 l_qp_list_header_id NUMBER;
843 l_error_location NUMBER;
844 l_mode VARCHAR2(6) := 'CREATE';
845 l_budget_tbl ozf_offer_pub.budget_tbl_type;
846 l_act_product_tbl ozf_offer_pub.act_product_tbl_type;
847 l_discount_tbl ozf_offer_pub.discount_line_tbl_type;
848 l_excl_tbl ozf_offer_pub.excl_rec_tbl_type;
849 l_offer_tier_tbl ozf_offer_pub.offer_tier_tbl_type;
850 l_prod_tbl ozf_offer_pub.prod_rec_tbl_type;
851 l_na_qualifier_tbl ozf_offer_pub.na_qualifier_tbl_type;
852 l_modifier_list_rec ozf_offer_pub.modifier_list_rec_type := p_modifier_list_rec;
853
854 CURSOR c_list_header_id (p_fund_id IN NUMBER) IS
855 SELECT plan_id
856 FROM ozf_funds_all_b
857 WHERE fund_id = p_fund_id;
858
859 BEGIN
860
861 SAVEPOINT Create_Fund_PUB;
862
863 validate_fund_items(p_fund_rec => l_fund_rec
864 ,p_mode => l_mode
865 ,x_return_status => x_return_status);
866
867 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
868 RAISE fnd_api.g_exc_unexpected_error;
869 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
870 RAISE fnd_api.g_exc_error;
871 END IF;
872
873 l_pvt_fund_rec.fund_id := l_fund_rec.fund_id;
874 l_pvt_fund_rec.fund_number := l_fund_rec.fund_number;
875 l_pvt_fund_rec.short_name := l_fund_rec.short_name;
876 l_pvt_fund_rec.fund_type := l_fund_rec.fund_type;
877 l_pvt_fund_rec.custom_setup_id := l_fund_rec.custom_setup_id;
878 l_pvt_fund_rec.description := l_fund_rec.description;
879 l_pvt_fund_rec.parent_fund_id := l_fund_rec.parent_fund_id;
880 l_pvt_fund_rec.category_id := l_fund_rec.category_id;
881 l_pvt_fund_rec.business_unit_id := l_fund_rec.business_unit_id;
882 l_pvt_fund_rec.status_code := l_fund_rec.status_code;
883 l_pvt_fund_rec.user_status_id := l_fund_rec.user_status_id;
884 l_pvt_fund_rec.start_date_active := l_fund_rec.start_date_active;
885 l_pvt_fund_rec.end_date_active := l_fund_rec.end_date_active;
886 l_pvt_fund_rec.start_period_name := l_fund_rec.start_period_name;
887 l_pvt_fund_rec.end_period_name := l_fund_rec.end_period_name;
888 l_pvt_fund_rec.original_budget := l_fund_rec.original_budget;
889 l_pvt_fund_rec.holdback_amt := l_fund_rec.holdback_amt;
890 l_pvt_fund_rec.currency_code_tc := l_fund_rec.currency_code_tc;
891 l_pvt_fund_rec.owner := l_fund_rec.owner;
892 l_pvt_fund_rec.accrual_basis := l_fund_rec.accrual_basis;
893 l_pvt_fund_rec.accrual_phase := l_fund_rec.accrual_phase;
894 l_pvt_fund_rec.accrual_discount_level := l_fund_rec.accrual_discount_level;
895 l_pvt_fund_rec.threshold_id := l_fund_rec.threshold_id;
896 l_pvt_fund_rec.task_id := l_fund_rec.task_id;
897 l_pvt_fund_rec.liability_flag := l_fund_rec.liability_flag;
898 l_pvt_fund_rec.accrued_liable_account := l_fund_rec.accrued_liable_account;
899 l_pvt_fund_rec.ded_adjustment_account := l_fund_rec.ded_adjustment_account;
900 l_pvt_fund_rec.product_spread_time_id := l_fund_rec.product_spread_time_id;
901 l_pvt_fund_rec.org_id := l_fund_rec.org_id;
902 l_pvt_fund_rec.ledger_id := l_fund_rec.ledger_id;
903
904
905 --Hbandi added below colums for the bug #10017563 (+)
906 l_pvt_fund_rec.apply_accrual_on := l_fund_rec.apply_accrual_on;
907 l_pvt_fund_rec.retroactive_flag := l_fund_rec.retroactive_flag;
908 l_pvt_fund_rec.qualifier_id :=l_fund_rec.qualifier_id;
909 l_pvt_fund_rec.accrual_quantity :=l_fund_rec.accrual_quantity;
910 l_pvt_fund_rec.accrue_to_level_id := l_fund_rec.accrue_to_level_id;
911 --End for the bug #10017563 (-).
912
913 --Addtional attributes added by hbandi while fixing the bug ##10017563
914 l_pvt_fund_rec.attribute_category := l_fund_rec.attribute_category;
915 l_pvt_fund_rec.attribute1 := l_fund_rec.attribute1;
916 l_pvt_fund_rec.attribute2 := l_fund_rec.attribute2;
917 l_pvt_fund_rec.attribute3 := l_fund_rec.attribute3;
918 l_pvt_fund_rec.attribute4 := l_fund_rec.attribute4;
919 l_pvt_fund_rec.attribute5 := l_fund_rec.attribute5;
920 l_pvt_fund_rec.attribute6 := l_fund_rec.attribute6;
921 l_pvt_fund_rec.attribute7 := l_fund_rec.attribute7;
922 l_pvt_fund_rec.attribute8 := l_fund_rec.attribute8;
923 l_pvt_fund_rec.attribute9 := l_fund_rec.attribute9;
924 l_pvt_fund_rec.attribute10 := l_fund_rec.attribute10;
925 l_pvt_fund_rec.attribute11 := l_fund_rec.attribute11;
926 l_pvt_fund_rec.attribute12 := l_fund_rec.attribute12;
927 l_pvt_fund_rec.attribute13 := l_fund_rec.attribute13;
928 l_pvt_fund_rec.attribute14 := l_fund_rec.attribute14;
929 l_pvt_fund_rec.attribute15 := l_fund_rec.attribute15;
930 --End of the additional attributes.
931
932 ozf_funds_pvt.create_fund(p_api_version => p_api_version
933 ,p_init_msg_list => p_init_msg_list
934 ,p_commit => p_commit
935 ,p_validation_level => p_validation_level
936 ,x_return_status => x_return_status
937 ,x_msg_count => x_msg_count
938 ,x_msg_data => x_msg_data
939 ,p_fund_rec => l_pvt_fund_rec
940 ,x_fund_id => x_fund_id
941 );
942
943 IF G_DEBUG THEN
944 ozf_utility_pvt.debug_message('fund_type: ' || l_pvt_fund_rec.fund_type);
945 END IF;
946
947 IF l_pvt_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
948
949 OPEN c_list_header_id(x_fund_id);
950 FETCH c_list_header_id INTO l_qp_list_header_id;
951 CLOSE c_list_header_id;
952
953 l_modifier_list_rec.modifier_operation := 'UPDATE';
954 l_modifier_list_rec.offer_operation := 'UPDATE';
955 l_modifier_list_rec.qp_list_header_id := l_qp_list_header_id;
956
957 IF G_DEBUG THEN
958 ozf_utility_pvt.debug_message('accrual_phase: ' || l_pvt_fund_rec.accrual_phase);
959 END IF;
960
961 IF l_pvt_fund_rec.accrual_phase = 'ACCRUAL' THEN --accrual offer
962
963 ozf_offer_pub.process_modifiers(p_init_msg_list => p_init_msg_list
964 ,p_api_version => p_api_version
965 ,p_commit => p_commit
966 ,x_return_status => x_return_status
967 ,x_msg_count => x_msg_count
968 ,x_msg_data => x_msg_data
969 ,p_offer_type => 'ACCRUAL'
970 ,p_modifier_list_rec => l_modifier_list_rec --offer header details
971 ,p_modifier_line_tbl => p_modifier_line_tbl --discount rules
972 ,p_qualifier_tbl => p_qualifier_tbl --market eligibilty
973 ,p_budget_tbl => l_budget_tbl
974 ,p_act_product_tbl => l_act_product_tbl
975 ,p_discount_tbl => l_discount_tbl
976 ,p_excl_tbl => l_excl_tbl
977 ,p_offer_tier_tbl => l_offer_tier_tbl
978 ,p_prod_tbl => l_prod_tbl
979 ,p_na_qualifier_tbl => l_na_qualifier_tbl
980 ,x_qp_list_header_id => l_qp_list_header_id
981 ,x_error_location => l_error_location
982 );
983
984
985 ELSIF l_pvt_fund_rec.accrual_phase = 'VOLUME' THEN --volume offer
986
987 ozf_offer_pub.process_vo(p_init_msg_list => p_init_msg_list
988 ,p_api_version => p_api_version
989 ,p_commit => p_commit
990 ,x_return_status => x_return_status
991 ,x_msg_count => x_msg_count
992 ,x_msg_data => x_msg_data
993 ,p_modifier_list_rec => l_modifier_list_rec --offer header detail
994 ,p_vo_pbh_tbl => p_vo_pbh_tbl --discount table headers
995 ,p_vo_dis_tbl => p_vo_dis_tbl --discount table tiers
996 ,p_vo_prod_tbl => p_vo_prod_tbl --discount tabel products
997 ,p_qualifier_tbl => p_qualifier_tbl --market eligibilty
998 ,p_vo_mo_tbl => p_vo_mo_tbl --market options
999 ,p_budget_tbl => l_budget_tbl
1000 ,x_qp_list_header_id => l_qp_list_header_id
1001 ,x_error_location => l_error_location
1002 );
1003 END IF;
1004
1005 END IF;
1006
1007 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1008 RAISE fnd_api.g_exc_unexpected_error;
1009 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1010 RAISE fnd_api.g_exc_error;
1011 END IF;
1012
1013 FND_MSG_PUB.Count_And_Get (
1014 p_encoded => FND_API.G_FALSE,
1015 p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018
1019 EXCEPTION
1020 WHEN FND_API.G_EXC_ERROR THEN
1021 ROLLBACK TO Create_Fund_PUB;
1022 x_return_status := FND_API.G_RET_STS_ERROR;
1023 -- Standard call to get message count and if count=1, get the message
1024 FND_MSG_PUB.Count_And_Get (
1025 p_encoded => FND_API.G_FALSE,
1026 p_count => x_msg_count,
1027 p_data => x_msg_data
1028 );
1029 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1030 ROLLBACK TO Create_Fund_PUB;
1031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1032 -- Standard call to get message count and if count=1, get the message
1033 FND_MSG_PUB.Count_And_Get (
1034 p_encoded => FND_API.G_FALSE,
1035 p_count => x_msg_count,
1036 p_data => x_msg_data
1037 );
1038 WHEN OTHERS THEN
1039 ROLLBACK TO Create_Fund_PUB;
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1042 THEN
1043 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1044 END IF;
1045 -- Standard call to get message count and if count=1, get the message
1046 FND_MSG_PUB.Count_And_Get (
1047 p_encoded => FND_API.G_FALSE,
1048 p_count => x_msg_count,
1049 p_data => x_msg_data
1050 );
1051 END Create_Fund;
1052 --------------------------------------------------------------------
1053 -- PROCEDURE
1054 -- Delete_Fund
1055 --
1056 -- PURPOSE
1057 -- Delete a fund.
1058 --
1059 -- PARAMETERS
1060 -- p_fund_id: the fund_id
1061 -- p_object_version: the object_version_number
1062 --
1063 -- NOTES
1064 -- 1. Raise exception if the object_version_number doesn't match.
1065 --------------------------------------------------------------------
1066
1067 PROCEDURE Delete_Fund(
1068 p_api_version IN NUMBER
1069 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1070 ,p_commit IN VARCHAR2 := FND_API.g_false
1071 ,x_return_status OUT NOCOPY VARCHAR2
1072 ,x_msg_count OUT NOCOPY NUMBER
1073 ,x_msg_data OUT NOCOPY VARCHAR2
1074 ,p_fund_id IN NUMBER
1075 ,p_object_version IN NUMBER
1076 )
1077
1078 IS
1079 l_dependent_object_tbl ams_utility_pvt.dependent_objects_tbl_type;
1080 l_return_status VARCHAR2(30);
1081 l_msg_count NUMBER;
1082 l_msg_data VARCHAR2(30);
1083 l_api_name VARCHAR(30) := 'Delete_Fund';
1084
1085 BEGIN
1086
1087 SAVEPOINT Delete_Fund_PUB;
1088
1089 OZF_Fund_Extension_Pvt.delete_fund(p_api_version_number => p_api_version
1090 ,p_init_msg_list => p_init_msg_list
1091 ,p_commit => p_commit
1092 ,p_object_id => p_fund_id
1093 ,p_object_version_number => p_object_version
1094 ,x_return_status => l_return_status
1095 ,x_msg_count => l_msg_count
1096 ,x_msg_data => l_msg_data
1097 );
1098
1099 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1100 RAISE fnd_api.g_exc_unexpected_error;
1101 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1102 RAISE fnd_api.g_exc_error;
1103 END IF;
1104
1105 FND_MSG_PUB.Count_And_Get(
1106 p_encoded => FND_API.G_FALSE,
1107 p_count => x_msg_count,
1108 p_data => x_msg_data
1109 );
1110
1111 EXCEPTION
1112 WHEN FND_API.G_EXC_ERROR THEN
1113 ROLLBACK TO Delete_Fund_PUB;
1114 x_return_status := FND_API.G_RET_STS_ERROR;
1115 -- Standard call to get message count and if count=1, get the message
1116 FND_MSG_PUB.Count_And_Get (
1117 p_encoded => FND_API.G_FALSE,
1118 p_count => x_msg_count,
1119 p_data => x_msg_data
1120 );
1121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1122 ROLLBACK TO Delete_Fund_PUB;
1123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1124 -- Standard call to get message count and if count=1, get the message
1125 FND_MSG_PUB.Count_And_Get (
1126 p_encoded => FND_API.G_FALSE,
1127 p_count => x_msg_count,
1128 p_data => x_msg_data
1129 );
1130 WHEN OTHERS THEN
1131 ROLLBACK TO Delete_Fund_PUB;
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1133 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1134 THEN
1135 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1136 END IF;
1137 -- Standard call to get message count and if count=1, get the message
1138 FND_MSG_PUB.Count_And_Get (
1139 p_encoded => FND_API.G_FALSE,
1140 p_count => x_msg_count,
1141 p_data => x_msg_data
1142 );
1143
1144 END Delete_Fund;
1145
1146
1147 ---------------------------------------------------------------------
1148 -- PROCEDURE
1149 -- Update_Fund
1150 --
1151 -- PURPOSE
1152 -- Update a fund.
1153 --
1154 -- PARAMETERS
1155 -- p_fund_rec: the record with new items.
1156 -- p_mode : determines what sort of validation is to be performed during update.
1157 -- : The mode should always be 'UPDATE' except when updating the earned or committed amount
1158 --
1159 -- NOTES
1160 -- 1. Raise exception if the object_version_number doesn't match.
1161 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
1162 -- that column won't be updated.
1163 ----------------------------------------------------------------------
1164 PROCEDURE Update_fund(
1165 p_api_version IN NUMBER
1166 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1167 ,p_commit IN VARCHAR2 := fnd_api.g_false
1168 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1169 ,x_return_status OUT NOCOPY VARCHAR2
1170 ,x_msg_count OUT NOCOPY NUMBER
1171 ,x_msg_data OUT NOCOPY VARCHAR2
1172 ,p_fund_rec IN fund_rec_type
1173 ,p_modifier_list_rec IN ozf_offer_pub.modifier_list_rec_type
1174 ,p_modifier_line_tbl IN ozf_offer_pub.modifier_line_tbl_type
1175 ,p_vo_pbh_tbl IN ozf_offer_pub.vo_disc_tbl_type
1176 ,p_vo_dis_tbl IN ozf_offer_pub.vo_disc_tbl_type
1177 ,p_vo_prod_tbl IN ozf_offer_pub.vo_prod_tbl_type
1178 ,p_qualifier_tbl IN ozf_offer_pub.qualifiers_tbl_type
1179 ,p_vo_mo_tbl IN ozf_offer_pub.vo_mo_tbl_type
1180 )
1181 IS
1182 l_api_name VARCHAR(30) := 'Update_Fund';
1183 l_fund_rec fund_rec_type := p_fund_rec;
1184 l_pvt_fund_rec OZF_Funds_PVT.fund_rec_type;
1185 l_fund_id NUMBER := l_fund_rec.fund_id;
1186 l_fund_number VARCHAR2(200);
1187 l_short_name VARCHAR2(200);
1188 l_fund_type VARCHAR2(20);
1189 l_custom_setup_id NUMBER;
1190 l_description VARCHAR2(2000);
1191 l_parent_fund_id NUMBER;
1192 l_category_id NUMBER;
1193 l_business_unit_id NUMBER;
1194 l_status_code VARCHAR2(50);
1195 l_start_date_active DATE;
1196 l_end_date_active DATE;
1197 l_start_period_name VARCHAR2(20);
1198 l_end_period_name VARCHAR2(20);
1199 l_original_budget NUMBER;
1200 l_holdback_amt NUMBER;
1201 l_currency_code_tc VARCHAR2(10);
1202 l_owner NUMBER;
1203 l_accrual_basis VARCHAR2(10);
1204 l_accrual_phase VARCHAR2(10);
1205 l_accrual_discount_level VARCHAR2(10);
1206 l_threshold_id NUMBER;
1207 l_task_id NUMBER;
1208 l_liability_flag VARCHAR2(1);
1209 l_accrued_liable_account NUMBER;
1210 l_ded_adjustment_account NUMBER;
1211 l_product_spread_time_id NUMBER;
1212 l_object_version_number NUMBER;
1213 l_org_id NUMBER;
1214 l_ledger_id NUMBER;
1215 l_mode VARCHAR2(6) := 'UPDATE';
1216
1217 CURSOR c_fund_id (p_fund_number IN VARCHAR2) IS
1218 SELECT fund_id
1219 FROM ozf_funds_all_b
1220 WHERE fund_number = p_fund_number;
1221
1222 CURSOR c_fund_details (p_fund_id IN NUMBER) IS
1223 SELECT fund_number, short_name, fund_type, custom_setup_id, description, parent_fund_id, category_id,
1224 business_unit_id, status_code, start_date_active, end_date_active, start_period_name,
1225 end_period_name, original_budget, holdback_amt, currency_code_tc, owner, accrual_basis,
1226 accrual_phase, accrual_discount_level, threshold_id, task_id, liability_flag,
1227 accrued_liable_account, ded_adjustment_account, product_spread_time_id, object_version_number,
1228 org_id, ledger_id
1229 FROM ozf_funds_all_vl
1230 WHERE fund_id = p_fund_id;
1231
1232 BEGIN
1233
1234 SAVEPOINT Update_Fund_PUB;
1235
1236 IF l_fund_rec.fund_id IS NULL AND l_fund_rec.fund_number IS NOT NULL THEN
1237 OPEN c_fund_id (l_fund_rec.fund_number);
1238 FETCH c_fund_id INTO l_fund_id;
1239 CLOSE c_fund_id;
1240 END IF;
1241
1242 IF G_DEBUG THEN
1243 ozf_utility_pvt.debug_message(l_api_name || ': l_fund_id :' || l_fund_id);
1244 END IF;
1245
1246 IF l_fund_id IS NOT NULL THEN
1247 OPEN c_fund_details (l_fund_id);
1248 FETCH c_fund_details INTO l_fund_number, l_short_name, l_fund_type, l_custom_setup_id, l_description,
1249 l_parent_fund_id, l_category_id, l_business_unit_id,
1250 l_status_code, l_start_date_active, l_end_date_active,
1251 l_start_period_name, l_end_period_name, l_original_budget, l_holdback_amt,
1252 l_currency_code_tc, l_owner, l_accrual_basis, l_accrual_phase, l_accrual_discount_level,
1253 l_threshold_id, l_task_id, l_liability_flag, l_accrued_liable_account,
1254 l_ded_adjustment_account, l_product_spread_time_id, l_object_version_number,
1255 l_org_id, l_ledger_id;
1256 CLOSE c_fund_details;
1257 END IF;
1258
1259 l_fund_rec.fund_number := NVL(l_fund_rec.fund_number,l_fund_number);
1260 l_fund_rec.short_name := NVL(l_fund_rec.short_name,l_short_name);
1261 l_fund_rec.fund_type := l_fund_type;
1262 l_fund_rec.custom_setup_id := l_custom_setup_id;
1263 l_fund_rec.description := NVL(l_fund_rec.description,l_description);
1264 l_fund_rec.parent_fund_id := NVL(l_fund_rec.parent_fund_id,l_parent_fund_id);
1265 l_fund_rec.category_id := NVL(l_fund_rec.category_id,l_category_id);
1266 --kdass - fixed bug 9432802
1267 l_fund_rec.business_unit_id := NVL(l_fund_rec.business_unit_id,l_business_unit_id);
1268 --l_fund_rec.business_unit_id := NVL(l_fund_rec.business_unit_id,l_description);
1269 l_fund_rec.status_code := NVL(l_fund_rec.status_code,l_status_code);
1270 l_fund_rec.start_date_active := NVL(l_fund_rec.start_date_active,l_start_date_active);
1271 l_fund_rec.end_date_active := NVL(l_fund_rec.end_date_active,l_end_date_active);
1272 l_fund_rec.start_period_name := NVL(l_fund_rec.start_period_name,l_start_period_name);
1273 l_fund_rec.end_period_name := NVL(l_fund_rec.end_period_name,l_end_period_name);
1274 l_fund_rec.original_budget := NVL(l_fund_rec.original_budget,l_original_budget);
1275 l_fund_rec.holdback_amt := NVL(l_fund_rec.holdback_amt,l_holdback_amt);
1276 l_fund_rec.currency_code_tc := l_currency_code_tc;
1277 l_fund_rec.owner := NVL(l_fund_rec.owner,l_owner);
1278 l_fund_rec.accrual_basis := l_accrual_basis;
1279 l_fund_rec.accrual_phase := l_accrual_phase;
1280 l_fund_rec.accrual_discount_level := l_accrual_discount_level;
1281 l_fund_rec.threshold_id := NVL(l_fund_rec.threshold_id,l_threshold_id);
1282 l_fund_rec.task_id := NVL(l_fund_rec.task_id,l_task_id);
1283 l_fund_rec.liability_flag := NVL(l_fund_rec.liability_flag,l_liability_flag);
1284 l_fund_rec.accrued_liable_account := NVL(l_fund_rec.accrued_liable_account,l_accrued_liable_account);
1285 l_fund_rec.ded_adjustment_account := NVL(l_fund_rec.ded_adjustment_account,l_ded_adjustment_account);
1286 l_fund_rec.product_spread_time_id := NVL(l_fund_rec.product_spread_time_id,l_product_spread_time_id);
1287 l_fund_rec.object_version_number := l_object_version_number;
1288 l_fund_rec.org_id := l_org_id;
1289 l_fund_rec.ledger_id := NVL(l_fund_rec.ledger_id,l_ledger_id);
1290
1291 validate_fund_items(p_fund_rec => l_fund_rec
1292 ,p_mode => l_mode
1293 ,x_return_status => x_return_status);
1294
1295 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1296 RAISE fnd_api.g_exc_unexpected_error;
1297 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1298 RAISE fnd_api.g_exc_error;
1299 END IF;
1300
1301 l_pvt_fund_rec.fund_id := l_fund_rec.fund_id;
1302 l_pvt_fund_rec.fund_number := l_fund_rec.fund_number;
1303 l_pvt_fund_rec.short_name := l_fund_rec.short_name;
1304 l_pvt_fund_rec.fund_type := l_fund_rec.fund_type;
1305 l_pvt_fund_rec.custom_setup_id := l_fund_rec.custom_setup_id;
1306 l_pvt_fund_rec.description := l_fund_rec.description;
1307 l_pvt_fund_rec.parent_fund_id := l_fund_rec.parent_fund_id;
1308 l_pvt_fund_rec.category_id := l_fund_rec.category_id;
1309 l_pvt_fund_rec.business_unit_id := l_fund_rec.business_unit_id;
1310 l_pvt_fund_rec.status_code := l_fund_rec.status_code;
1311 l_pvt_fund_rec.user_status_id := l_fund_rec.user_status_id;
1312 l_pvt_fund_rec.start_date_active := l_fund_rec.start_date_active;
1313 l_pvt_fund_rec.end_date_active := l_fund_rec.end_date_active;
1314 l_pvt_fund_rec.start_period_name := l_fund_rec.start_period_name;
1315 l_pvt_fund_rec.end_period_name := l_fund_rec.end_period_name;
1316 l_pvt_fund_rec.original_budget := l_fund_rec.original_budget;
1317 l_pvt_fund_rec.holdback_amt := l_fund_rec.holdback_amt;
1318 l_pvt_fund_rec.currency_code_tc := l_fund_rec.currency_code_tc;
1319 l_pvt_fund_rec.owner := l_fund_rec.owner;
1320 l_pvt_fund_rec.accrual_basis := l_fund_rec.accrual_basis;
1321 l_pvt_fund_rec.accrual_phase := l_fund_rec.accrual_phase;
1322 l_pvt_fund_rec.accrual_discount_level := l_fund_rec.accrual_discount_level;
1323 l_pvt_fund_rec.threshold_id := l_fund_rec.threshold_id;
1324 l_pvt_fund_rec.task_id := l_fund_rec.task_id;
1325 l_pvt_fund_rec.liability_flag := l_fund_rec.liability_flag;
1326 l_pvt_fund_rec.accrued_liable_account := l_fund_rec.accrued_liable_account;
1327 l_pvt_fund_rec.ded_adjustment_account := l_fund_rec.ded_adjustment_account;
1328 l_pvt_fund_rec.product_spread_time_id := l_fund_rec.product_spread_time_id;
1329 l_pvt_fund_rec.object_version_number := l_fund_rec.object_version_number;
1330 l_pvt_fund_rec.org_id := l_fund_rec.org_id;
1331 l_pvt_fund_rec.ledger_id := l_fund_rec.ledger_id;
1332
1333 ozf_funds_pvt.update_fund(p_api_version => p_api_version
1334 ,p_init_msg_list => p_init_msg_list
1335 ,p_commit => p_commit
1336 ,p_validation_level => p_validation_level
1337 ,x_return_status => x_return_status
1338 ,x_msg_count => x_msg_count
1339 ,x_msg_data => x_msg_data
1340 ,p_fund_rec => l_pvt_fund_rec
1341 ,p_mode => jtf_plsql_api.g_update
1342 );
1343
1344 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1345 RAISE fnd_api.g_exc_unexpected_error;
1346 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1347 RAISE fnd_api.g_exc_error;
1348 END IF;
1349
1350 FND_MSG_PUB.Count_And_Get (
1351 p_encoded => FND_API.G_FALSE,
1352 p_count => x_msg_count,
1353 p_data => x_msg_data
1354 );
1355
1356 EXCEPTION
1357 WHEN FND_API.G_EXC_ERROR THEN
1358 ROLLBACK TO Update_Fund_PUB;
1359 x_return_status := FND_API.G_RET_STS_ERROR;
1360 -- Standard call to get message count and if count=1, get the message
1361 FND_MSG_PUB.Count_And_Get (
1362 p_encoded => FND_API.G_FALSE,
1363 p_count => x_msg_count,
1364 p_data => x_msg_data
1365 );
1366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1367 ROLLBACK TO Update_Fund_PUB;
1368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1369 -- Standard call to get message count and if count=1, get the message
1370 FND_MSG_PUB.Count_And_Get (
1371 p_encoded => FND_API.G_FALSE,
1372 p_count => x_msg_count,
1373 p_data => x_msg_data
1374 );
1375 WHEN OTHERS THEN
1376 ROLLBACK TO Update_Fund_PUB;
1377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1378 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1379 THEN
1380 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1381 END IF;
1382 -- Standard call to get message count and if count=1, get the message
1383 FND_MSG_PUB.Count_And_Get (
1384 p_encoded => FND_API.G_FALSE,
1385 p_count => x_msg_count,
1386 p_data => x_msg_data
1387 );
1388 END Update_Fund;
1389
1390 ---------------------------------------------------------------------
1391 -- PROCEDURE
1392 -- validate_market_segment
1393 --
1394 -- PURPOSE
1395 -- Validate market segment
1396 --
1397 -- PARAMETERS
1398 -- p_mks_rec: market segment record to be validated
1399 -- x_return_status: return status
1400 --
1401 -- HISTORY
1402 -- 06/29/2005 kdass Created
1403 ---------------------------------------------------------------------
1404 PROCEDURE validate_market_segment (
1405 p_mks_rec IN OUT NOCOPY mks_rec_type
1406 ,p_mode IN VARCHAR2
1407 ,x_return_status OUT NOCOPY VARCHAR2
1408 )
1409 IS
1410 l_api_name VARCHAR(30) := 'validate_market_segment';
1411 l_act_mkt_exists NUMBER := NULL;
1412 l_segment_used_by_exists NUMBER := NULL;
1413 l_segment_exists NUMBER := NULL;
1414 l_segment_id_exists NUMBER := NULL;
1415
1416 CURSOR c_act_mkt_exists (p_activity_market_segment_id IN NUMBER) IS
1417 SELECT 1
1418 FROM ams_act_market_segments
1419 WHERE activity_market_segment_id = p_activity_market_segment_id;
1420
1421 CURSOR c_segment_used_by_exists (p_segment_used_by_id IN NUMBER) IS
1422 SELECT 1
1423 FROM ozf_funds_all_b
1424 WHERE fund_id = p_segment_used_by_id;
1425
1426 CURSOR c_segment_exists (p_segment IN VARCHAR2) IS
1427 SELECT 1
1428 FROM ozf_lookups
1429 WHERE lookup_type = 'OZF_OFFER_DEAL_CUSTOMER_TYPES'
1430 AND enabled_flag = 'Y'
1431 AND lookup_code = p_segment;
1432
1433 CURSOR c_segment_buyer (p_segment_id IN NUMBER) IS
1434 SELECT 1
1435 FROM ams_party_market_segments ams, hz_parties hz
1436 WHERE ams.market_qualifier_type = 'BG'
1437 AND ams.market_qualifier_reference = hz.party_id
1438 AND ams.market_qualifier_reference = ams.party_id
1439 AND EXISTS
1440 ( SELECT 1
1441 FROM ams_party_market_segments
1442 WHERE market_qualifier_type = 'BG'
1443 AND market_qualifier_reference = ams.market_qualifier_reference
1444 AND market_qualifier_reference <> party_id)
1445 AND hz.party_id = p_segment_id;
1446
1447 CURSOR c_segment_cust (p_segment_id IN NUMBER) IS
1448 SELECT 1
1449 FROM qp_customers_v
1450 WHERE customer_id = p_segment_id;
1451
1452 CURSOR c_segment_billto (p_segment_id IN NUMBER) IS
1453 SELECT 1
1454 FROM oe_invoice_to_orgs_v oito,hz_cust_accounts cust_acct,hz_parties party
1455 WHERE cust_acct.party_id = party.party_id
1456 AND oito.customer_id = cust_acct.cust_account_id
1457 AND oito.organization_id = p_segment_id;
1458
1459 CURSOR c_segment_list (p_segment_id IN NUMBER) IS
1460 SELECT 1
1461 FROM ams_list_headers_all list, ams_list_headers_all_tl tl
1462 WHERE list.list_header_id = tl.list_header_id
1463 AND userenv('LANG') = language
1464 AND status_code in ( 'AVAILABLE','LOCKED','EXECUTED','EXECUTING','VALIDATED','VALIDATING')
1465 AND list.list_header_id = p_segment_id;
1466
1467 CURSOR c_segment_seg (p_segment_id IN NUMBER) IS
1468 SELECT 1
1469 FROM ams_cells_all_b cell, ams_cells_all_tl tl
1470 WHERE cell.cell_id = tl.cell_id
1471 AND userenv('LANG') = language
1472 AND cell.status_code = 'AVAILABLE'
1473 AND cell.cell_id = p_segment_id;
1474
1475 CURSOR c_segment_shipto (p_segment_id IN NUMBER) IS
1476 SELECT 1
1477 FROM qp_ship_to_orgs_v
1478 WHERE organization_id = p_segment_id;
1479
1480 CURSOR c_segment_terr (p_segment_id IN NUMBER) IS
1481 SELECT 1
1482 FROM jtf_terr_qtype_usgs jtqu, jtf_terr jt, jtf_qual_type_usgs jqtu
1483 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE)
1484 AND ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR jt.end_date_active IS NULL ))
1485 AND jt.terr_id = jtqu.terr_id
1486 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
1487 AND jqtu.source_id = -1003
1488 AND jqtu.qual_type_id = -1007
1489 AND jt.terr_id = p_segment_id;
1490
1491 BEGIN
1492
1493 IF p_mode = 'CREATE' THEN
1494 p_mks_rec.activity_market_segment_id := NULL;
1495 ELSE
1496 --if activity market segment id is null, then raise exception
1497 IF (p_mks_rec.activity_market_segment_id = fnd_api.g_miss_num OR p_mks_rec.activity_market_segment_id IS NULL) THEN
1498
1499 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1500 fnd_message.set_name('OZF', 'OZF_NO_ACT_SEG_ID');
1501 fnd_msg_pub.add;
1502 END IF;
1503 x_return_status := fnd_api.g_ret_sts_error;
1504 RETURN;
1505 ELSE
1506
1507 --check if the input activity_market_segment_id is valid
1508 OPEN c_act_mkt_exists (p_mks_rec.activity_market_segment_id);
1509 FETCH c_act_mkt_exists INTO l_act_mkt_exists;
1510 CLOSE c_act_mkt_exists;
1511
1512 IF l_act_mkt_exists IS NULL THEN
1513 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1514 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_SEG_ID');
1515 fnd_msg_pub.add;
1516 END IF;
1517 x_return_status := fnd_api.g_ret_sts_error;
1518 RETURN;
1519 END IF;
1520 END IF;
1521 END IF;
1522
1523 IF p_mks_rec.act_market_segment_used_by_id = fnd_api.g_miss_num OR p_mks_rec.act_market_segment_used_by_id IS NULL THEN
1524 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1525 fnd_message.set_name('OZF', 'OZF_NO_MKT_SEG_USED_BY_ID');
1526 fnd_msg_pub.add;
1527 END IF;
1528 x_return_status := fnd_api.g_ret_sts_error;
1529 RETURN;
1530 ELSE
1531
1532 OPEN c_segment_used_by_exists (p_mks_rec.act_market_segment_used_by_id);
1533 FETCH c_segment_used_by_exists INTO l_segment_used_by_exists;
1534 CLOSE c_segment_used_by_exists;
1535
1536 IF l_segment_used_by_exists IS NULL THEN
1537 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1538 fnd_message.set_name('OZF', 'OZF_INVALID_MKT_SEG_USED_BY_ID');
1539 fnd_msg_pub.add;
1540 END IF;
1541 x_return_status := fnd_api.g_ret_sts_error;
1542 RETURN;
1543 END IF;
1544 END IF;
1545
1546 p_mks_rec.arc_act_market_segment_used_by := 'FUND';
1547
1548 IF p_mks_rec.segment_type = fnd_api.g_miss_char OR p_mks_rec.segment_type IS NULL THEN
1549 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1550 fnd_message.set_name('OZF', 'OZF_NO_SEGMENT_TYPE');
1551 fnd_msg_pub.add;
1552 END IF;
1553 x_return_status := fnd_api.g_ret_sts_error;
1554 RETURN;
1555 ELSE
1556
1557 OPEN c_segment_exists (p_mks_rec.segment_type);
1558 FETCH c_segment_exists INTO l_segment_exists;
1559 CLOSE c_segment_exists;
1560
1561 IF l_segment_exists IS NULL THEN
1562 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1563 fnd_message.set_name('OZF', 'OZF_INVALID_SEGMENT_TYPE');
1564 fnd_msg_pub.add;
1565 END IF;
1566 x_return_status := fnd_api.g_ret_sts_error;
1567 RETURN;
1568 END IF;
1569 END IF;
1570
1571 IF p_mks_rec.market_segment_id = fnd_api.g_miss_num OR p_mks_rec.market_segment_id IS NULL THEN
1572 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1573 fnd_message.set_name('OZF', 'OZF_NO_MKT_SEG_ID');
1574 fnd_msg_pub.add;
1575 END IF;
1576 x_return_status := fnd_api.g_ret_sts_error;
1577 RETURN;
1578 ELSE
1579
1580 IF p_mks_rec.segment_type = 'BUYER' THEN --Buying Group
1581 OPEN c_segment_buyer (p_mks_rec.market_segment_id);
1582 FETCH c_segment_buyer INTO l_segment_id_exists;
1583 CLOSE c_segment_buyer;
1584 ELSIF p_mks_rec.segment_type = 'CUSTOMER' THEN --Customer Name
1585 OPEN c_segment_cust (p_mks_rec.market_segment_id);
1586 FETCH c_segment_cust INTO l_segment_id_exists;
1587 CLOSE c_segment_cust;
1588 ELSIF p_mks_rec.segment_type = 'CUSTOMER_BILL_TO' THEN --Customer - Bill TO
1589 OPEN c_segment_billto (p_mks_rec.market_segment_id);
1590 FETCH c_segment_billto INTO l_segment_id_exists;
1591 CLOSE c_segment_billto;
1592 ELSIF p_mks_rec.segment_type = 'LIST' THEN --List
1593 OPEN c_segment_list (p_mks_rec.market_segment_id);
1594 FETCH c_segment_list INTO l_segment_id_exists;
1595 CLOSE c_segment_list;
1596 ELSIF p_mks_rec.segment_type = 'SEGMENT' THEN --Segment
1597 OPEN c_segment_seg (p_mks_rec.market_segment_id);
1598 FETCH c_segment_seg INTO l_segment_id_exists;
1599 CLOSE c_segment_seg;
1600 ELSIF p_mks_rec.segment_type = 'SHIP_TO' THEN --Customer - Ship TO
1601 OPEN c_segment_shipto (p_mks_rec.market_segment_id);
1602 FETCH c_segment_shipto INTO l_segment_id_exists;
1603 CLOSE c_segment_shipto;
1604 ELSIF p_mks_rec.segment_type = 'TERRITORY' THEN --Territories
1605 OPEN c_segment_terr (p_mks_rec.market_segment_id);
1606 FETCH c_segment_terr INTO l_segment_id_exists;
1607 CLOSE c_segment_terr;
1608 END IF;
1609
1610 IF l_segment_id_exists IS NULL THEN
1611 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1612 fnd_message.set_name('OZF', 'OZF_INVALID_MKT_SEG_ID');
1613 fnd_msg_pub.add;
1614 END IF;
1615 x_return_status := fnd_api.g_ret_sts_error;
1616 RETURN;
1617 END IF;
1618
1619 END IF;
1620
1621 p_mks_rec.exclude_flag := NVL(p_mks_rec.exclude_flag, 'N');
1622
1623 IF p_mks_rec.exclude_flag NOT IN ('Y', 'N') THEN
1624 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1625 fnd_message.set_name('OZF', 'OZF_INVALID_EXCLUDE_FLAG');
1626 fnd_msg_pub.add;
1627 END IF;
1628 x_return_status := fnd_api.g_ret_sts_error;
1629 RETURN;
1630 END IF;
1631
1632 END validate_market_segment;
1633
1634 ---------------------------------------------------------------------
1635 -- PROCEDURE
1636 -- create_market_segment
1637 --
1638 -- PURPOSE
1639 -- Creates a market segment for fund.
1640 --
1641 -- PARAMETERS
1642 -- p_mks_rec : the record with new items.
1643 -- x_act_mks_id : return the market segment id for the fund
1644 --
1645 -- HISTORY
1646 -- 07/07/2005 kdass Created
1647 ----------------------------------------------------------------------
1648 PROCEDURE create_market_segment(
1649 p_api_version IN NUMBER
1650 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1651 ,p_commit IN VARCHAR2 := fnd_api.g_false
1652 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1653 ,p_mks_rec IN mks_rec_type
1654 ,x_return_status OUT NOCOPY VARCHAR2
1655 ,x_msg_count OUT NOCOPY NUMBER
1656 ,x_msg_data OUT NOCOPY VARCHAR2
1657 ,x_act_mks_id OUT NOCOPY NUMBER)
1658 IS
1659 l_api_name VARCHAR(30) := 'create_market_segment';
1660 l_mode VARCHAR2(6) := 'CREATE';
1661 l_mks_rec mks_rec_type := p_mks_rec;
1662 l_seg_rec ams_act_market_segments_pvt.mks_rec_type;
1663 l_api_version NUMBER := p_api_version;
1664 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
1665 l_validation_level NUMBER := p_validation_level;
1666 l_commit VARCHAR2(1) := p_commit;
1667
1668 BEGIN
1669
1670 SAVEPOINT create_market_pub;
1671
1672 validate_market_segment(p_mks_rec => l_mks_rec
1673 ,p_mode => l_mode
1674 ,x_return_status => x_return_status);
1675
1676 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1677 RAISE fnd_api.g_exc_unexpected_error;
1678 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1679 RAISE fnd_api.g_exc_error;
1680 END IF;
1681
1682 l_seg_rec.market_segment_id := l_mks_rec.market_segment_id;
1683 l_seg_rec.act_market_segment_used_by_id := l_mks_rec.act_market_segment_used_by_id;
1684 l_seg_rec.arc_act_market_segment_used_by := l_mks_rec.arc_act_market_segment_used_by;
1685 l_seg_rec.segment_type := l_mks_rec.segment_type;
1686 l_seg_rec.object_version_number := l_mks_rec.object_version_number;
1687 l_seg_rec.exclude_flag := l_mks_rec.exclude_flag;
1688
1689 ams_act_market_segments_pvt.create_market_segments(p_api_version => l_api_version
1690 ,p_init_msg_list => l_init_msg_list
1691 ,p_commit => l_commit
1692 ,p_validation_level => l_validation_level
1693 ,p_mks_rec => l_seg_rec
1694 ,x_return_status => x_return_status
1695 ,x_msg_count => x_msg_count
1696 ,x_msg_data => x_msg_data
1697 ,x_act_mks_id => x_act_mks_id
1698 );
1699
1700 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1701 RAISE fnd_api.g_exc_unexpected_error;
1702 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1703 RAISE fnd_api.g_exc_error;
1704 END IF;
1705
1706 FND_MSG_PUB.Count_And_Get (
1707 p_encoded => FND_API.G_FALSE,
1708 p_count => x_msg_count,
1709 p_data => x_msg_data
1710 );
1711
1712 EXCEPTION
1713 WHEN FND_API.G_EXC_ERROR THEN
1714 ROLLBACK TO create_market_pub;
1715 x_return_status := FND_API.G_RET_STS_ERROR;
1716 -- Standard call to get message count and if count=1, get the message
1717 FND_MSG_PUB.Count_And_Get (
1718 p_encoded => FND_API.G_FALSE,
1719 p_count => x_msg_count,
1720 p_data => x_msg_data
1721 );
1722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1723 ROLLBACK TO create_market_pub;
1724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725 -- Standard call to get message count and if count=1, get the message
1726 FND_MSG_PUB.Count_And_Get (
1727 p_encoded => FND_API.G_FALSE,
1728 p_count => x_msg_count,
1729 p_data => x_msg_data
1730 );
1731 WHEN OTHERS THEN
1732 ROLLBACK TO create_market_pub;
1733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1734 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1735 THEN
1736 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1737 END IF;
1738 -- Standard call to get message count and if count=1, get the message
1739 FND_MSG_PUB.Count_And_Get (
1740 p_encoded => FND_API.G_FALSE,
1741 p_count => x_msg_count,
1742 p_data => x_msg_data
1743 );
1744 END create_market_segment;
1745
1746 ---------------------------------------------------------------------
1747 -- PROCEDURE
1748 -- update_market_segment
1749 --
1750 -- PURPOSE
1751 -- Updates a market segment for fund.
1752 --
1753 -- PARAMETERS
1754 -- p_mks_rec : the record with items to be updated.
1755 --
1756 -- HISTORY
1757 -- 07/07/2005 kdass Created
1758 ----------------------------------------------------------------------
1759 PROCEDURE update_market_segment(
1760 p_api_version IN NUMBER
1761 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1762 ,p_commit IN VARCHAR2 := fnd_api.g_false
1763 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1764 ,p_mks_rec IN mks_rec_type
1765 ,x_return_status OUT NOCOPY VARCHAR2
1766 ,x_msg_count OUT NOCOPY NUMBER
1767 ,x_msg_data OUT NOCOPY VARCHAR2)
1768 IS
1769 l_api_name VARCHAR(30) := 'update_market_segment';
1770 l_mode VARCHAR2(6) := 'UPDATE';
1771 l_mks_rec mks_rec_type := p_mks_rec;
1772 l_seg_rec ams_act_market_segments_pvt.mks_rec_type;
1773 l_api_version NUMBER := p_api_version;
1774 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
1775 l_validation_level NUMBER := p_validation_level;
1776 l_commit VARCHAR2(1) := p_commit;
1777 l_mkt_seg_used_by_id NUMBER;
1778 l_segment_type VARCHAR2(30);
1779 l_mkt_seg_id NUMBER;
1780 l_object_version NUMBER;
1781 l_exclude_flag VARCHAR2(1);
1782
1783 CURSOR c_mkt_seg_details (p_act_mkt_seg_id IN NUMBER) IS
1784 SELECT act_market_segment_used_by_id, segment_type, market_segment_id,
1785 object_version_number, exclude_flag
1786 FROM ams_act_market_segments
1787 WHERE activity_market_segment_id = p_act_mkt_seg_id;
1788
1789 BEGIN
1790
1791 SAVEPOINT update_market_pub;
1792
1793 IF l_mks_rec.activity_market_segment_id IS NOT NULL THEN
1794 OPEN c_mkt_seg_details (l_mks_rec.activity_market_segment_id);
1795 FETCH c_mkt_seg_details INTO l_mkt_seg_used_by_id, l_segment_type, l_mkt_seg_id, l_object_version, l_exclude_flag;
1796 CLOSE c_mkt_seg_details;
1797 END IF;
1798
1799 l_mks_rec.act_market_segment_used_by_id := NVL(l_mks_rec.act_market_segment_used_by_id,l_mkt_seg_used_by_id);
1800 l_mks_rec.segment_type := NVL(l_mks_rec.segment_type,l_segment_type);
1801 l_mks_rec.market_segment_id := NVL(l_mks_rec.market_segment_id, l_mkt_seg_id);
1802 l_mks_rec.object_version_number := l_object_version;
1803 l_mks_rec.exclude_flag := NVL(l_mks_rec.exclude_flag,l_exclude_flag);
1804
1805 validate_market_segment(p_mks_rec => l_mks_rec
1806 ,p_mode => l_mode
1807 ,x_return_status => x_return_status);
1808
1809 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1810 RAISE fnd_api.g_exc_unexpected_error;
1811 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1812 RAISE fnd_api.g_exc_error;
1813 END IF;
1814
1815 l_seg_rec.activity_market_segment_id := l_mks_rec.activity_market_segment_id;
1816 l_seg_rec.act_market_segment_used_by_id := l_mks_rec.act_market_segment_used_by_id;
1817 l_seg_rec.arc_act_market_segment_used_by := l_mks_rec.arc_act_market_segment_used_by;
1818 l_seg_rec.segment_type := l_mks_rec.segment_type;
1819 l_seg_rec.market_segment_id := l_mks_rec.market_segment_id;
1820 l_seg_rec.object_version_number := l_mks_rec.object_version_number;
1821 l_seg_rec.exclude_flag := l_mks_rec.exclude_flag;
1822
1823 ams_act_market_segments_pvt.update_market_segments(p_api_version => l_api_version
1824 ,p_init_msg_list => l_init_msg_list
1825 ,p_commit => l_commit
1826 ,p_validation_level => l_validation_level
1827 ,p_mks_rec => l_seg_rec
1828 ,x_return_status => x_return_status
1829 ,x_msg_count => x_msg_count
1830 ,x_msg_data => x_msg_data
1831 );
1832
1833 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1834 RAISE fnd_api.g_exc_unexpected_error;
1835 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1836 RAISE fnd_api.g_exc_error;
1837 END IF;
1838
1839 FND_MSG_PUB.Count_And_Get (
1840 p_encoded => FND_API.G_FALSE,
1841 p_count => x_msg_count,
1842 p_data => x_msg_data
1843 );
1844
1845 EXCEPTION
1846 WHEN FND_API.G_EXC_ERROR THEN
1847 ROLLBACK TO update_market_pub;
1848 x_return_status := FND_API.G_RET_STS_ERROR;
1849 -- Standard call to get message count and if count=1, get the message
1850 FND_MSG_PUB.Count_And_Get (
1851 p_encoded => FND_API.G_FALSE,
1852 p_count => x_msg_count,
1853 p_data => x_msg_data
1854 );
1855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1856 ROLLBACK TO update_market_pub;
1857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1858 -- Standard call to get message count and if count=1, get the message
1859 FND_MSG_PUB.Count_And_Get (
1860 p_encoded => FND_API.G_FALSE,
1861 p_count => x_msg_count,
1862 p_data => x_msg_data
1863 );
1864 WHEN OTHERS THEN
1865 ROLLBACK TO update_market_pub;
1866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1867 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1868 THEN
1869 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1870 END IF;
1871 -- Standard call to get message count and if count=1, get the message
1872 FND_MSG_PUB.Count_And_Get (
1873 p_encoded => FND_API.G_FALSE,
1874 p_count => x_msg_count,
1875 p_data => x_msg_data
1876 );
1877 END update_market_segment;
1878
1879 ---------------------------------------------------------------------
1880 -- PROCEDURE
1881 -- delete_market_segment
1882 --
1883 -- PURPOSE
1884 -- Deletes a market segment for fund.
1885 --
1886 -- PARAMETERS
1887 -- p_act_mks_id : the market segment to be deleted
1888 --
1889 -- HISTORY
1890 -- 07/07/2005 kdass Created
1891 ----------------------------------------------------------------------
1892 /**
1893 * This procedure deletes a market segment for an existing fund.
1894 * @param p_api_version Indicates the version of the API
1895 * @param p_init_msg_list Indicates whether to initialize the message stack
1896 * @param p_commit Indicates whether to commit within the program
1897 * @param p_act_mks_id Market segment identifier of the market segment to be deleted
1898 * @param x_return_status Status of the program
1899 * @param x_msg_count Number of the messages returned by the program
1900 * @param x_msg_data Return message by the program
1901 * @rep:scope public
1902 * @rep:lifecycle active
1903 * @rep:displayname Delete Market Segment
1904 * @rep:compatibility S
1905 * @rep:businessevent None
1906 */
1907 PROCEDURE delete_market_segment(
1908 p_api_version IN NUMBER
1909 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1910 ,p_commit IN VARCHAR2 := fnd_api.g_false
1911 ,p_act_mks_id IN NUMBER
1912 ,x_return_status OUT NOCOPY VARCHAR2
1913 ,x_msg_count OUT NOCOPY NUMBER
1914 ,x_msg_data OUT NOCOPY VARCHAR2)
1915 IS
1916 l_api_name VARCHAR(30) := 'delete_market_segment';
1917 l_api_version NUMBER := p_api_version;
1918 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
1919 l_commit VARCHAR2(1) := p_commit;
1920 l_object_version NUMBER := NULL;
1921
1922 CURSOR c_valid_act_mks_id IS
1923 SELECT object_version_number
1924 FROM ams_act_market_segments
1925 WHERE activity_market_segment_id = p_act_mks_id;
1926
1927 BEGIN
1928
1929 SAVEPOINT delete_market_pub;
1930
1931 --if activity market segment id is null, then raise exception
1932 IF (p_act_mks_id = fnd_api.g_miss_num OR p_act_mks_id IS NULL) THEN
1933
1934 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1935 fnd_message.set_name('OZF', 'OZF_NO_ACT_SEG_ID');
1936 fnd_msg_pub.add;
1937 END IF;
1938 RAISE fnd_api.g_exc_error;
1939 END IF;
1940
1941 --check if the activity market segment id is valid and get the object_version_number
1942 OPEN c_valid_act_mks_id;
1943 FETCH c_valid_act_mks_id INTO l_object_version;
1944 CLOSE c_valid_act_mks_id;
1945
1946 IF l_object_version IS NULL THEN
1947 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1948 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_SEG_ID');
1949 fnd_msg_pub.add;
1950 END IF;
1951 RAISE fnd_api.g_exc_error;
1952 END IF;
1953
1954 --delete market segment
1955 ams_act_market_segments_pvt.delete_market_segments(p_api_version => l_api_version
1956 ,p_init_msg_list => l_init_msg_list
1957 ,p_commit => l_commit
1958 ,p_act_mks_id => p_act_mks_id
1959 ,p_object_version => l_object_version
1960 ,x_return_status => x_return_status
1961 ,x_msg_count => x_msg_count
1962 ,x_msg_data => x_msg_data
1963 );
1964
1965 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1966 RAISE fnd_api.g_exc_unexpected_error;
1967 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1968 RAISE fnd_api.g_exc_error;
1969 END IF;
1970
1971 FND_MSG_PUB.Count_And_Get (
1972 p_encoded => FND_API.G_FALSE,
1973 p_count => x_msg_count,
1974 p_data => x_msg_data
1975 );
1976
1977 EXCEPTION
1978 WHEN FND_API.G_EXC_ERROR THEN
1979 ROLLBACK TO delete_market_pub;
1980 x_return_status := FND_API.G_RET_STS_ERROR;
1981 -- Standard call to get message count and if count=1, get the message
1982 FND_MSG_PUB.Count_And_Get (
1983 p_encoded => FND_API.G_FALSE,
1984 p_count => x_msg_count,
1985 p_data => x_msg_data
1986 );
1987 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1988 ROLLBACK TO delete_market_pub;
1989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1990 -- Standard call to get message count and if count=1, get the message
1991 FND_MSG_PUB.Count_And_Get (
1992 p_encoded => FND_API.G_FALSE,
1993 p_count => x_msg_count,
1994 p_data => x_msg_data
1995 );
1996 WHEN OTHERS THEN
1997 ROLLBACK TO delete_market_pub;
1998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1999 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2000 THEN
2001 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2002 END IF;
2003 -- Standard call to get message count and if count=1, get the message
2004 FND_MSG_PUB.Count_And_Get (
2005 p_encoded => FND_API.G_FALSE,
2006 p_count => x_msg_count,
2007 p_data => x_msg_data
2008 );
2009 END delete_market_segment;
2010
2011 ---------------------------------------------------------------------
2012 -- PROCEDURE
2013 -- validate_act_product
2014 --
2015 -- PURPOSE
2016 -- Validate product eligibility record
2017 --
2018 -- PARAMETERS
2019 -- p_mks_rec: product eligibility record to be validated
2020 -- x_return_status: return status
2021 --
2022 -- HISTORY
2023 -- 06/29/2005 kdass Created
2024 ---------------------------------------------------------------------
2025 PROCEDURE validate_act_product (
2026 p_act_product_rec IN OUT NOCOPY act_product_rec_type
2027 ,p_mode IN VARCHAR2
2028 ,x_return_status OUT NOCOPY VARCHAR2
2029 )
2030 IS
2031 l_api_name VARCHAR(30) := 'validate_act_product';
2032 l_act_prod_exists NUMBER := NULL;
2033 l_act_prod_used_by_exists NUMBER := NULL;
2034 l_inv_id_exists NUMBER := NULL;
2035
2036 CURSOR c_act_prod_exists (p_activity_product_id IN NUMBER) IS
2037 SELECT 1
2038 FROM ams_act_products
2039 WHERE activity_product_id = p_activity_product_id;
2040
2041 CURSOR c_act_prod_used_by_exists (p_prod_used_by IN NUMBER) IS
2042 SELECT 1
2043 FROM ozf_funds_all_b
2044 WHERE fund_id = p_prod_used_by;
2045
2046 CURSOR c_org_id (p_prod_used_by IN NUMBER) IS
2047 SELECT org_id
2048 FROM ozf_funds_all_b
2049 WHERE fund_id = p_prod_used_by;
2050
2051 CURSOR c_inv_id_exists (p_inventory_id IN NUMBER, p_org_id IN NUMBER) IS
2052 SELECT 1
2053 FROM mtl_system_items_b_kfv
2054 WHERE organization_id = p_org_id
2055 AND inventory_item_id = p_inventory_id;
2056
2057 CURSOR c_inv_name_exists (p_inventory_name IN VARCHAR2, p_org_id IN NUMBER) IS
2058 SELECT inventory_item_id
2059 FROM mtl_system_items_b_kfv
2060 WHERE organization_id = p_org_id
2061 AND concatenated_segments = p_inventory_name;
2062
2063 CURSOR c_cat_id_exists (p_category_id IN NUMBER) IS
2064 SELECT category_set_id
2065 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
2066 WHERE category_id = p_category_id;
2067
2068 --08-MAY-2006 kdass bug 5199585 SQL ID# 17778264 - added last condition so that table uses index
2069 CURSOR c_cat_name_exists (p_category_name IN VARCHAR2) IS
2070 SELECT category_id, category_set_id
2071 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
2072 WHERE category_desc = p_category_name
2073 AND NVL(category_id, 0) = category_id;
2074
2075 BEGIN
2076
2077 IF p_mode = 'CREATE' THEN
2078 p_act_product_rec.activity_product_id := NULL;
2079 ELSE
2080 --if activity product id is null, then raise exception
2081 IF (p_act_product_rec.activity_product_id = fnd_api.g_miss_num OR p_act_product_rec.activity_product_id IS NULL) THEN
2082
2083 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2084 fnd_message.set_name('OZF', 'OZF_NO_ACT_PROD_ID');
2085 fnd_msg_pub.add;
2086 END IF;
2087 x_return_status := fnd_api.g_ret_sts_error;
2088 RETURN;
2089 ELSE
2090
2091 --check if the input activity_product_id is valid
2092 OPEN c_act_prod_exists (p_act_product_rec.activity_product_id);
2093 FETCH c_act_prod_exists INTO l_act_prod_exists;
2094 CLOSE c_act_prod_exists;
2095
2096 IF l_act_prod_exists IS NULL THEN
2097 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2098 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_PROD_ID');
2099 fnd_msg_pub.add;
2100 END IF;
2101 x_return_status := fnd_api.g_ret_sts_error;
2102 RETURN;
2103 END IF;
2104 END IF;
2105 END IF;
2106
2107 IF p_act_product_rec.act_product_used_by_id = fnd_api.g_miss_num OR p_act_product_rec.act_product_used_by_id IS NULL THEN
2108 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2109 fnd_message.set_name('OZF', 'OZF_NO_PROD_USED_BY');
2110 fnd_msg_pub.add;
2111 END IF;
2112 x_return_status := fnd_api.g_ret_sts_error;
2113 RETURN;
2114 ELSE
2115
2116 OPEN c_act_prod_used_by_exists (p_act_product_rec.act_product_used_by_id);
2117 FETCH c_act_prod_used_by_exists INTO l_act_prod_used_by_exists;
2118 CLOSE c_act_prod_used_by_exists;
2119
2120 IF l_act_prod_used_by_exists IS NULL THEN
2121 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2122 fnd_message.set_name('OZF', 'OZF_INVALID_PROD_USED_BY');
2123 fnd_msg_pub.add;
2124 END IF;
2125 x_return_status := fnd_api.g_ret_sts_error;
2126 RETURN;
2127 END IF;
2128 END IF;
2129
2130 --if both inventory item id and inventory item name are null, then raise exception
2131 IF (p_act_product_rec.inventory_item_id = fnd_api.g_miss_num OR p_act_product_rec.inventory_item_id IS NULL) AND
2132 (p_act_product_rec.inventory_item_name = fnd_api.g_miss_char OR p_act_product_rec.inventory_item_name IS NULL) AND
2133 (p_act_product_rec.category_id = fnd_api.g_miss_num OR p_act_product_rec.category_id IS NULL) AND
2134 (p_act_product_rec.category_name = fnd_api.g_miss_char OR p_act_product_rec.category_name IS NULL) THEN
2135
2136 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2137 fnd_message.set_name('OZF', 'OZF_NO_INV_ITEM_CAT');
2138 fnd_msg_pub.add;
2139 END IF;
2140 x_return_status := fnd_api.g_ret_sts_error;
2141 RETURN;
2142
2143 ELSIF (p_act_product_rec.inventory_item_id <> fnd_api.g_miss_num AND p_act_product_rec.inventory_item_id IS NOT NULL) OR
2144 (p_act_product_rec.inventory_item_name <> fnd_api.g_miss_char AND p_act_product_rec.inventory_item_name IS NOT NULL) THEN
2145
2146 OPEN c_org_id (p_act_product_rec.act_product_used_by_id);
2147 FETCH c_org_id INTO p_act_product_rec.organization_id;
2148 CLOSE c_org_id;
2149
2150 --if inventory item id is not null
2151 IF p_act_product_rec.inventory_item_id <> fnd_api.g_miss_num AND p_act_product_rec.inventory_item_id IS NOT NULL THEN
2152
2153 --check if the input inventory_item_id valid
2154 OPEN c_inv_id_exists (p_act_product_rec.inventory_item_id, p_act_product_rec.organization_id);
2155 FETCH c_inv_id_exists INTO l_inv_id_exists;
2156 CLOSE c_inv_id_exists;
2157
2158 IF l_inv_id_exists IS NULL THEN
2159 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2160 fnd_message.set_name('OZF', 'OZF_INVALID_INV_ITEM_ID');
2161 fnd_msg_pub.add;
2162 END IF;
2163 x_return_status := fnd_api.g_ret_sts_error;
2164 RETURN;
2165 END IF;
2166
2167 --if inventory item name is not null
2168 ELSE
2169 --check if the input inventory item name is valid
2170 OPEN c_inv_name_exists (p_act_product_rec.inventory_item_name, p_act_product_rec.organization_id);
2171 FETCH c_inv_name_exists INTO p_act_product_rec.inventory_item_id;
2172 CLOSE c_inv_name_exists;
2173
2174 IF p_act_product_rec.inventory_item_id IS NULL THEN
2175 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2176 fnd_message.set_name('OZF', 'OZF_INVALID_INV_ITEM_NAME');
2177 fnd_msg_pub.add;
2178 END IF;
2179 x_return_status := fnd_api.g_ret_sts_error;
2180 RETURN;
2181 END IF;
2182 END IF;
2183
2184 p_act_product_rec.level_type_code := 'PRODUCT';
2185
2186 ELSIF (p_act_product_rec.category_id <> fnd_api.g_miss_num AND p_act_product_rec.category_id IS NOT NULL) OR
2187 (p_act_product_rec.category_name <> fnd_api.g_miss_char AND p_act_product_rec.category_name IS NOT NULL) THEN
2188
2189 --if category id is not null
2190 IF p_act_product_rec.category_id <> fnd_api.g_miss_num AND p_act_product_rec.category_id IS NOT NULL THEN
2191
2192 --check if the input category_id valid
2193 OPEN c_cat_id_exists (p_act_product_rec.category_id);
2194 FETCH c_cat_id_exists INTO p_act_product_rec.category_set_id;
2195 CLOSE c_cat_id_exists;
2196
2197 IF p_act_product_rec.category_set_id IS NULL THEN
2198 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2199 fnd_message.set_name('OZF', 'OZF_INVALID_CATEGORY_ID');
2200 fnd_msg_pub.add;
2201 END IF;
2202 x_return_status := fnd_api.g_ret_sts_error;
2203 RETURN;
2204 END IF;
2205
2206 --if category name is not null
2207 ELSE
2208 --check if the input category name is valid
2209 OPEN c_cat_name_exists (p_act_product_rec.category_name);
2210 FETCH c_cat_name_exists INTO p_act_product_rec.category_id, p_act_product_rec.category_set_id;
2211 CLOSE c_cat_name_exists;
2212
2213 IF p_act_product_rec.category_id IS NULL THEN
2214 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2215 fnd_message.set_name('OZF', 'OZF_INVALID_CATEGORY_NAME');
2216 fnd_msg_pub.add;
2217 END IF;
2218 x_return_status := fnd_api.g_ret_sts_error;
2219 RETURN;
2220 END IF;
2221 END IF;
2222
2223 p_act_product_rec.level_type_code := 'FAMILY';
2224
2225 END IF;
2226
2227 p_act_product_rec.arc_act_product_used_by := 'FUND';
2228
2229 p_act_product_rec.primary_product_flag := NVL(p_act_product_rec.primary_product_flag, 'N');
2230
2231 IF p_act_product_rec.primary_product_flag NOT IN ('Y', 'N') THEN
2232 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2233 fnd_message.set_name('OZF', 'OZF_INVALID_PRIMARY_FLAG');
2234 fnd_msg_pub.add;
2235 END IF;
2236 x_return_status := fnd_api.g_ret_sts_error;
2237 RETURN;
2238 END IF;
2239
2240 p_act_product_rec.excluded_flag := NVL(p_act_product_rec.excluded_flag, 'N');
2241
2242 IF p_act_product_rec.excluded_flag NOT IN ('Y', 'N') THEN
2243 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2244 fnd_message.set_name('OZF', 'OZF_INVALID_EXCLUDED_FLAG');
2245 fnd_msg_pub.add;
2246 END IF;
2247 x_return_status := fnd_api.g_ret_sts_error;
2248 RETURN;
2249 END IF;
2250
2251 END validate_act_product;
2252
2253 ----------------------------------------------------------------------
2254 -- PROCEDURE
2255 -- create_product_eligibility
2256 --
2257 -- PURPOSE
2258 -- Creates the product eligibility record for fund or quota.
2259 --
2260 -- PARAMETERS
2261 -- p_act_product_rec : the record with new items
2262 -- x_act_product_id : return the activity product id for the fund or quota
2263 --
2264 -- HISTORY
2265 -- 07/11/2005 kdass Created
2266 ----------------------------------------------------------------------
2267 PROCEDURE create_product_eligibility(
2268 p_api_version IN NUMBER
2269 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2270 ,p_commit IN VARCHAR2 := fnd_api.g_false
2271 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2272 ,p_act_product_rec IN act_product_rec_type
2273 ,x_return_status OUT NOCOPY VARCHAR2
2274 ,x_msg_count OUT NOCOPY NUMBER
2275 ,x_msg_data OUT NOCOPY VARCHAR2
2276 ,x_act_product_id OUT NOCOPY NUMBER
2277 )
2278 IS
2279 l_api_name VARCHAR(30) := 'create_product_eligibility';
2280 l_mode VARCHAR2(6) := 'CREATE';
2281 l_act_prod_rec act_product_rec_type := p_act_product_rec;
2282 l_act_product_rec ams_actproduct_pvt.act_product_rec_type;
2283 l_api_version NUMBER := p_api_version;
2284 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2285 l_validation_level NUMBER := p_validation_level;
2286 l_commit VARCHAR2(1) := p_commit;
2287
2288 BEGIN
2289
2290 SAVEPOINT create_product_pub;
2291
2292 validate_act_product(p_act_product_rec => l_act_prod_rec
2293 ,p_mode => l_mode
2294 ,x_return_status => x_return_status);
2295
2296 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2297 RAISE fnd_api.g_exc_unexpected_error;
2298 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2299 RAISE fnd_api.g_exc_error;
2300 END IF;
2301
2302 l_act_product_rec.act_product_used_by_id := l_act_prod_rec.act_product_used_by_id;
2303 l_act_product_rec.arc_act_product_used_by := l_act_prod_rec.arc_act_product_used_by;
2304 l_act_product_rec.inventory_item_id := l_act_prod_rec.inventory_item_id;
2305 l_act_product_rec.level_type_code := l_act_prod_rec.level_type_code;
2306 l_act_product_rec.category_id := l_act_prod_rec.category_id;
2307 l_act_product_rec.category_set_id := l_act_prod_rec.category_set_id;
2308 l_act_product_rec.primary_product_flag := l_act_prod_rec.primary_product_flag;
2309 l_act_product_rec.excluded_flag := l_act_prod_rec.excluded_flag;
2310 l_act_product_rec.organization_id := l_act_prod_rec.organization_id;
2311
2312 ams_actproduct_pvt.create_act_product(p_api_version => l_api_version
2313 ,p_init_msg_list => l_init_msg_list
2314 ,p_commit => l_commit
2315 ,p_validation_level => l_validation_level
2316 ,p_act_product_rec => l_act_product_rec
2317 ,x_return_status => x_return_status
2318 ,x_msg_count => x_msg_count
2319 ,x_msg_data => x_msg_data
2320 ,x_act_product_id => x_act_product_id
2321 );
2322
2323 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2324 RAISE fnd_api.g_exc_unexpected_error;
2325 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2326 RAISE fnd_api.g_exc_error;
2327 END IF;
2328
2329 FND_MSG_PUB.Count_And_Get (
2330 p_encoded => FND_API.G_FALSE,
2331 p_count => x_msg_count,
2332 p_data => x_msg_data
2333 );
2334
2335 EXCEPTION
2336 WHEN FND_API.G_EXC_ERROR THEN
2337 ROLLBACK TO create_product_pub;
2338 x_return_status := FND_API.G_RET_STS_ERROR;
2339 -- Standard call to get message count and if count=1, get the message
2340 FND_MSG_PUB.Count_And_Get (
2341 p_encoded => FND_API.G_FALSE,
2342 p_count => x_msg_count,
2343 p_data => x_msg_data
2344 );
2345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2346 ROLLBACK TO create_product_pub;
2347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2348 -- Standard call to get message count and if count=1, get the message
2349 FND_MSG_PUB.Count_And_Get (
2350 p_encoded => FND_API.G_FALSE,
2351 p_count => x_msg_count,
2352 p_data => x_msg_data
2353 );
2354 WHEN OTHERS THEN
2355 ROLLBACK TO create_product_pub;
2356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2357 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2358 THEN
2359 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2360 END IF;
2361 -- Standard call to get message count and if count=1, get the message
2362 FND_MSG_PUB.Count_And_Get (
2363 p_encoded => FND_API.G_FALSE,
2364 p_count => x_msg_count,
2365 p_data => x_msg_data
2366 );
2367 END create_product_eligibility;
2368
2369 ---------------------------------------------------------------------
2370 -- PROCEDURE
2371 -- update_product_eligibility
2372 --
2373 -- PURPOSE
2374 -- Updates the product eligibility record for fund or quota.
2375 --
2376 -- PARAMETERS
2377 -- p_act_product_rec : the record with items to be updated
2378 --
2379 -- HISTORY
2380 -- 07/11/2005 kdass Created
2381 ----------------------------------------------------------------------
2382 PROCEDURE update_product_eligibility(
2383 p_api_version IN NUMBER
2384 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2385 ,p_commit IN VARCHAR2 := fnd_api.g_false
2386 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2387 ,p_act_product_rec IN act_product_rec_type
2388 ,x_return_status OUT NOCOPY VARCHAR2
2389 ,x_msg_count OUT NOCOPY NUMBER
2390 ,x_msg_data OUT NOCOPY VARCHAR2
2391 )
2392 IS
2393 l_api_name VARCHAR(30) := 'update_product_eligibility';
2394 l_mode VARCHAR2(6) := 'UPDATE';
2395 l_act_prod_rec act_product_rec_type := p_act_product_rec;
2396 l_act_product_rec ams_actproduct_pvt.act_product_rec_type;
2397 l_api_version NUMBER := p_api_version;
2398 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2399 l_validation_level NUMBER := p_validation_level;
2400 l_commit VARCHAR2(1) := p_commit;
2401 l_prod_used_by_id NUMBER;
2402 l_item_id NUMBER;
2403 l_cat_id NUMBER;
2404 l_cat_set_id NUMBER;
2405 l_primary_flag VARCHAR2(1);
2406 l_excluded_flag VARCHAR2(1);
2407 l_obj_ver NUMBER;
2408
2409 CURSOR c_prod_elig_details (p_act_prod_id IN NUMBER) IS
2410 SELECT act_product_used_by_id, inventory_item_id, category_id, category_set_id,
2411 primary_product_flag, excluded_flag, object_version_number
2412 FROM ams_act_products
2413 WHERE activity_product_id = p_act_prod_id;
2414
2415 BEGIN
2416
2417 SAVEPOINT update_product_pub;
2418
2419 IF l_act_prod_rec.activity_product_id IS NOT NULL THEN
2420 OPEN c_prod_elig_details (l_act_prod_rec.activity_product_id);
2421 FETCH c_prod_elig_details INTO l_prod_used_by_id, l_item_id, l_cat_id, l_cat_set_id,
2422 l_primary_flag, l_excluded_flag, l_obj_ver;
2423 CLOSE c_prod_elig_details;
2424 END IF;
2425
2426 l_act_prod_rec.act_product_used_by_id := NVL(l_act_prod_rec.act_product_used_by_id,l_prod_used_by_id);
2427 l_act_prod_rec.inventory_item_id := NVL(l_act_prod_rec.inventory_item_id,l_item_id);
2428 l_act_prod_rec.category_id := NVL(l_act_prod_rec.category_id,l_cat_id);
2429 l_act_prod_rec.category_set_id := NVL(l_act_prod_rec.category_set_id,l_cat_set_id);
2430 l_act_prod_rec.primary_product_flag := NVL(l_act_prod_rec.primary_product_flag,l_primary_flag);
2431 l_act_prod_rec.excluded_flag := NVL(l_act_prod_rec.excluded_flag,l_excluded_flag);
2432 l_act_prod_rec.object_version_number := NVL(l_act_prod_rec.object_version_number,l_obj_ver);
2433
2434 validate_act_product(p_act_product_rec => l_act_prod_rec
2435 ,p_mode => l_mode
2436 ,x_return_status => x_return_status);
2437
2438 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2439 RAISE fnd_api.g_exc_unexpected_error;
2440 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2441 RAISE fnd_api.g_exc_error;
2442 END IF;
2443
2444 l_act_product_rec.activity_product_id := l_act_prod_rec.activity_product_id;
2445 l_act_product_rec.act_product_used_by_id := l_act_prod_rec.act_product_used_by_id;
2446 l_act_product_rec.arc_act_product_used_by := l_act_prod_rec.arc_act_product_used_by;
2447 l_act_product_rec.inventory_item_id := l_act_prod_rec.inventory_item_id;
2448 l_act_product_rec.level_type_code := l_act_prod_rec.level_type_code;
2449 l_act_product_rec.category_id := l_act_prod_rec.category_id;
2450 l_act_product_rec.category_set_id := l_act_prod_rec.category_set_id;
2451 l_act_product_rec.primary_product_flag := l_act_prod_rec.primary_product_flag;
2452 l_act_product_rec.excluded_flag := l_act_prod_rec.excluded_flag;
2453 l_act_product_rec.organization_id := l_act_prod_rec.organization_id;
2454 l_act_product_rec.object_version_number := l_act_prod_rec.object_version_number;
2455 l_act_product_rec.enabled_flag := 'Y';
2456
2457 ams_actproduct_pvt.update_act_product(p_api_version => l_api_version
2458 ,p_init_msg_list => l_init_msg_list
2459 ,p_commit => l_commit
2460 ,p_validation_level => l_validation_level
2461 ,p_act_product_rec => l_act_product_rec
2462 ,x_return_status => x_return_status
2463 ,x_msg_count => x_msg_count
2464 ,x_msg_data => x_msg_data
2465 );
2466
2467 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2468 RAISE fnd_api.g_exc_unexpected_error;
2469 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2470 RAISE fnd_api.g_exc_error;
2471 END IF;
2472
2473 FND_MSG_PUB.Count_And_Get (
2474 p_encoded => FND_API.G_FALSE,
2475 p_count => x_msg_count,
2476 p_data => x_msg_data
2477 );
2478
2479 EXCEPTION
2480 WHEN FND_API.G_EXC_ERROR THEN
2481 ROLLBACK TO update_product_pub;
2482 x_return_status := FND_API.G_RET_STS_ERROR;
2483 -- Standard call to get message count and if count=1, get the message
2484 FND_MSG_PUB.Count_And_Get (
2485 p_encoded => FND_API.G_FALSE,
2486 p_count => x_msg_count,
2487 p_data => x_msg_data
2488 );
2489 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2490 ROLLBACK TO update_product_pub;
2491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2492 -- Standard call to get message count and if count=1, get the message
2493 FND_MSG_PUB.Count_And_Get (
2494 p_encoded => FND_API.G_FALSE,
2495 p_count => x_msg_count,
2496 p_data => x_msg_data
2497 );
2498 WHEN OTHERS THEN
2499 ROLLBACK TO update_product_pub;
2500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2501 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2502 THEN
2503 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2504 END IF;
2505 -- Standard call to get message count and if count=1, get the message
2506 FND_MSG_PUB.Count_And_Get (
2507 p_encoded => FND_API.G_FALSE,
2508 p_count => x_msg_count,
2509 p_data => x_msg_data
2510 );
2511 END update_product_eligibility;
2512
2513 ---------------------------------------------------------------------
2514 -- PROCEDURE
2515 -- delete_product_eligibility
2516 --
2517 -- PURPOSE
2518 -- Deletes the product eligibility record for fund or quota.
2519 --
2520 -- PARAMETERS
2521 -- p_act_product_id : the product eligibility to be deleted
2522 --
2523 -- HISTORY
2524 -- 07/11/2005 kdass Created
2525 ----------------------------------------------------------------------
2526 PROCEDURE delete_product_eligibility(
2527 p_api_version IN NUMBER
2528 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2529 ,p_commit IN VARCHAR2 := fnd_api.g_false
2530 ,p_act_product_id IN NUMBER
2531 ,x_return_status OUT NOCOPY VARCHAR2
2532 ,x_msg_count OUT NOCOPY NUMBER
2533 ,x_msg_data OUT NOCOPY VARCHAR2
2534 )
2535 IS
2536 l_api_name VARCHAR(30) := 'delete_product_eligibility';
2537 l_api_version NUMBER := p_api_version;
2538 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2539 l_commit VARCHAR2(1) := p_commit;
2540 l_object_version NUMBER := NULL;
2541
2542 CURSOR c_valid_act_prod_id IS
2543 SELECT object_version_number
2544 FROM ams_act_products
2545 WHERE activity_product_id = p_act_product_id;
2546
2547 BEGIN
2548
2549 SAVEPOINT delete_product_pub;
2550
2551 --if activity market segment id is null, then raise exception
2552 IF (p_act_product_id = fnd_api.g_miss_num OR p_act_product_id IS NULL) THEN
2553
2554 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2555 fnd_message.set_name('OZF', 'OZF_NO_ACT_PROD_ID');
2556 fnd_msg_pub.add;
2557 END IF;
2558 RAISE fnd_api.g_exc_error;
2559 END IF;
2560
2561 --check if the activity product id is valid and get the object_version_number
2562 OPEN c_valid_act_prod_id;
2563 FETCH c_valid_act_prod_id INTO l_object_version;
2564 CLOSE c_valid_act_prod_id;
2565
2566 IF l_object_version IS NULL THEN
2567 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2568 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_PROD_ID');
2569 fnd_msg_pub.add;
2570 END IF;
2571 RAISE fnd_api.g_exc_error;
2572 END IF;
2573
2574 --delete product eligibility record
2575 ams_actproduct_pvt.delete_act_product(p_api_version => l_api_version
2576 ,p_init_msg_list => l_init_msg_list
2577 ,p_commit => l_commit
2578 ,p_act_product_id => p_act_product_id
2579 ,p_object_version => l_object_version
2580 ,x_return_status => x_return_status
2581 ,x_msg_count => x_msg_count
2582 ,x_msg_data => x_msg_data
2583 );
2584
2585 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2586 RAISE fnd_api.g_exc_unexpected_error;
2587 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2588 RAISE fnd_api.g_exc_error;
2589 END IF;
2590
2591 FND_MSG_PUB.Count_And_Get (
2592 p_encoded => FND_API.G_FALSE,
2593 p_count => x_msg_count,
2594 p_data => x_msg_data
2595 );
2596
2597 EXCEPTION
2598 WHEN FND_API.G_EXC_ERROR THEN
2599 ROLLBACK TO delete_product_pub;
2600 x_return_status := FND_API.G_RET_STS_ERROR;
2601 -- Standard call to get message count and if count=1, get the message
2602 FND_MSG_PUB.Count_And_Get (
2603 p_encoded => FND_API.G_FALSE,
2604 p_count => x_msg_count,
2605 p_data => x_msg_data
2606 );
2607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2608 ROLLBACK TO delete_product_pub;
2609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2610 -- Standard call to get message count and if count=1, get the message
2611 FND_MSG_PUB.Count_And_Get (
2612 p_encoded => FND_API.G_FALSE,
2613 p_count => x_msg_count,
2614 p_data => x_msg_data
2615 );
2616 WHEN OTHERS THEN
2617 ROLLBACK TO delete_product_pub;
2618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2619 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2620 THEN
2621 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2622 END IF;
2623 -- Standard call to get message count and if count=1, get the message
2624 FND_MSG_PUB.Count_And_Get (
2625 p_encoded => FND_API.G_FALSE,
2626 p_count => x_msg_count,
2627 p_data => x_msg_data
2628 );
2629 END delete_product_eligibility;
2630
2631 /*kdass - funds accrual process by business event descoped due to performance issues.
2632 added back by feliu since calling API don't descope. */
2633 PROCEDURE increase_order_message_counter
2634 IS
2635 BEGIN
2636 SAVEPOINT increase_order_message_counter;
2637
2638 -- ozf_accrual_engine.increase_order_message_counter;
2639
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 ROLLBACK TO increase_order_message_counter;
2643 END increase_order_message_counter;
2644
2645 --//ER - 12764004
2646 ---------------------------------------------------------------------
2647 -- PROCEDURE
2648 -- process_fund_from_adapter
2649 --
2650 -- PURPOSE
2651 -- Process a fund , called from adapter.
2652 --
2653 -- PARAMETERS
2654 -- p_source: the adapter.
2655 -- p_fund_rec: the record to be inserted or updated.
2656 -- p_modifier_line_tbl: Offer list line record.
2657 -- p_qualifier_tbl: market eligibility record.
2658 -- x_fund_id: return the fund_id of the fund
2659 -- x_line_tbl: return the source products of the fund
2660 --
2661 -- HISTORY
2662 --
2663 -- 10-jul-2011 BKUNJAN Created
2664 ---------------------------------------------------------------------
2665 PROCEDURE process_fund_from_adapter(
2666 p_api_version IN NUMBER
2667 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2668 ,p_commit IN VARCHAR2 := fnd_api.g_false
2669 ,p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL
2670 ,x_return_status OUT NOCOPY VARCHAR2
2671 ,x_msg_count OUT NOCOPY NUMBER
2672 ,x_msg_data OUT NOCOPY VARCHAR2
2673 ,p_source IN VARCHAR2 := 'SIEBEL'
2674 ,p_fund_rec IN fund_rec_type
2675 ,p_modifier_line_tbl IN ozf_offer_pub.modifier_line_tbl_type
2676 ,p_qualifier_tbl IN ozf_offer_pub.qualifiers_tbl_type
2677 ,x_fund_id OUT NOCOPY NUMBER
2678 ,x_msg_tbl OUT NOCOPY OZF_Utility_PVT.ozf_msg_tbl_type
2679 ,x_line_tbl OUT NOCOPY ozf_offer_pub.list_line_tbl_type
2680 ) IS
2681
2682 l_api_version CONSTANT NUMBER := 1.0;
2683 l_api_name VARCHAR(30) := 'process_fund_from_adapter';
2684 l_validation_level NUMBER := fnd_api.G_VALID_LEVEL_NONE;
2685 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2686 l_commit VARCHAR2(1) := p_commit;
2687 l_return_status VARCHAR2(1);
2688 l_msg_count NUMBER;
2689 l_msg_data VARCHAR2(2000);
2690
2691 l_fund_rec fund_rec_type := p_fund_rec;
2692 l_pvt_fund_rec OZF_FUNDS_PVT.fund_rec_type;
2693 l_pvt_fund_rec2 OZF_FUNDS_PVT.fund_rec_type;
2694 l_pvt_fund_rec3 OZF_FUNDS_PVT.fund_rec_type;
2695
2696 l_modifier_list_rec OZF_OFFER_PUB.modifier_list_rec_type;
2697 l_offer_hdr_rec OZF_OFFER_PVT.modifier_list_rec_type;
2698 l_budget_tbl OZF_OFFER_PUB.budget_tbl_type;
2699 l_act_product_tbl OZF_OFFER_PUB.act_product_tbl_type;
2700 l_discount_tbl OZF_OFFER_PUB.discount_line_tbl_type;
2701 l_excl_tbl OZF_OFFER_PUB.excl_rec_tbl_type;
2702 l_offer_tier_tbl OZF_OFFER_PUB.offer_tier_tbl_type;
2703 l_prod_tbl OZF_OFFER_PUB.prod_rec_tbl_type;
2704 l_na_qualifier_tbl OZF_OFFER_PUB.na_qualifier_tbl_type;
2705 l_modifier_line_tbl OZF_OFFER_PUB.modifier_line_tbl_type ;
2706 l_qualifier_tbl OZF_OFFER_PUB.qualifiers_tbl_type ;
2707
2708 l_qp_list_header_id NUMBER;
2709 l_mode VARCHAR2(6);
2710
2711 l_fund_id NUMBER := l_fund_rec.fund_id;
2712 l_error_location NUMBER;
2713
2714 x_act_mks_id NUMBER;
2715 l_seg_rec AMS_ACT_MARKET_SEGMENTS_PVT.mks_rec_type;
2716 k NUMBER :=1;
2717
2718 l_qualifier_id number;
2719 l_uom VARCHAR2(3);
2720 l_xref_line_id NUMBER;
2721
2722 l_insert_xref VARCHAR2(1);
2723 l_counter NUMBER := 1;
2724 l_lookup_check VARCHAR2(1); --To validate from lookups
2725
2726 l_ledger_id NUMBER;
2727 l_accr_liab_acct NUMBER;
2728 l_ded_adj_acct NUMBER;
2729 l_ledger_count NUMBER;
2730 l_ccid_count NUMBER;
2731 l_fund_number VARCHAR2(30);
2732 l_threshold_id NUMBER;
2733 l_task_id NUMBER;
2734 l_bus_unit_exists NUMBER := NULL;
2735 l_currency_code VARCHAR2(30);
2736
2737 TYPE List_Line_Rec_Type IS RECORD
2738 (
2739 LIST_LINE_NO VARCHAR2(30) := Fnd_Api.g_miss_char
2740 );
2741 TYPE List_line_tbl_type IS TABLE OF List_Line_Rec_Type INDEX BY BINARY_INTEGER;
2742 l_list_line_tbl_type List_line_tbl_type;
2743
2744 CURSOR c_list_header_id (p_fund_id IN NUMBER) IS
2745 SELECT plan_id
2746 FROM ozf_funds_all_b
2747 WHERE fund_id = p_fund_id;
2748
2749 CURSOR c_fund_details (p_fund_id IN NUMBER) IS
2750 SELECT fund_number,
2751 short_name,
2752 fund_type,
2753 description,
2754 parent_fund_id,
2755 business_unit_id,
2756 status_code,
2757 start_date_active,
2758 end_date_active,
2759 start_period_name,
2760 end_period_name,
2761 original_budget,
2762 holdback_amt,
2763 currency_code_tc,
2764 owner,
2765 accrual_basis,
2766 accrual_phase,
2767 accrual_discount_level,
2768 threshold_id,
2769 task_id,
2770 accrued_liable_account,
2771 ded_adjustment_account,
2772 product_spread_time_id,
2773 object_version_number,
2774 org_id,
2775 liability_flag
2776 FROM ozf_funds_all_vl
2777 WHERE fund_id = p_fund_id;
2778
2779 CURSOR c_market_segment (p_fund_id IN NUMBER) IS
2780 SELECT activity_market_segment_id,
2781 object_version_number
2782 FROM ams_act_market_segments
2783 WHERE act_market_segment_used_by_id = p_fund_id
2784 AND arc_act_market_segment_used_by = 'FUND';
2785
2786 CURSOR c_product_object_version (p_inventory_item_id IN NUMBER,
2787 p_category_id IN NUMBER,
2788 p_fund_id IN NUMBER,
2789 p_act_product_used_by IN VARCHAR2,
2790 p_level_type_code IN VARCHAR2) IS
2791 SELECT object_version_number
2792 FROM AMS_ACT_PRODUCTS
2793 WHERE inventory_item_id = p_inventory_item_id
2794 AND category_id = p_category_id
2795 AND act_product_used_by_id = p_fund_id
2796 AND arc_act_product_used_by = p_act_product_used_by
2797 AND level_type_code = p_level_type_code;
2798
2799 --//Fix for Bug 13654821
2800 CURSOR c_list_line(p_fund_id IN NUMBER) IS
2801 SELECT NVL(map.xref_line_id_value,line.list_line_id) list_line_id ,
2802 list_line_no
2803 FROM qp_list_lines line,
2804 ozf_funds_all_b,
2805 ozf_xref_map map
2806 WHERE fund_id = p_fund_id
2807 AND list_header_id = plan_id
2808 AND line.list_line_id = map.list_line_id(+);
2809
2810 CURSOR c_old_list_lines (p_list_header_id IN NUMBER) IS
2811 SELECT list_line_no
2812 FROM qp_list_lines
2813 WHERE list_header_id = p_list_header_id;
2814
2815 CURSOR c_resource_id(cv_source IN VARCHAR2) IS
2816 SELECT jtf.resource_id
2817 FROM JTF_RS_RESOURCE_EXTNS jtf,fnd_user fnd
2818 WHERE fnd.user_name = cv_source
2819 AND fnd.user_id = jtf.user_id;
2820
2821
2822 CURSOR c_uom ( p_product_id NUMBER
2823 , p_org_id NUMBER) IS
2824 SELECT primary_uom_code
2825 FROM mtl_system_items
2826 WHERE inventory_item_id = p_product_id
2827 AND organization_id = p_org_id;
2828
2829
2830 --//Fix for Bug 13654821
2831 CURSOR c_line_id_chk(p_list_line_id NUMBER)
2832 IS
2833 SELECT list_line_id
2834 FROM ozf_xref_map
2835 WHERE xref_line_id_value = p_list_line_id;
2836
2837 CURSOR c_ledger_from_cat IS
2838 SELECT ledger_id
2839 ,accrued_liability_account
2840 ,ded_adjustment_account
2841 FROM AMS_CATEGORIES_B
2842 WHERE category_id = 851;
2843
2844 CURSOR c_ledger_from_sysParam IS
2845 SELECT set_of_books_id
2846 FROM ozf_sys_parameters;
2847
2848 CURSOR c_ledger_from_HR IS
2849 SELECT set_of_books_id
2850 FROM hr_operating_units
2851 WHERE organization_id = fnd_profile.value('DEFAULT_ORG_ID');
2852
2853 CURSOR c_ledger_from_gl(cv_ledger_id IN NUMBER) IS
2854 SELECT COUNT(1)
2855 FROM gl_ledgers_public_v
2856 WHERE ledger_id = cv_ledger_id;
2857
2858 CURSOR c_gl_ccid(cv_code_combi_id IN NUMBER)IS
2859 SELECT COUNT(1)
2860 FROM gl_code_combinations
2861 WHERE code_combination_id = cv_code_combi_id
2862 AND enabled_flag ='Y';
2863
2864 CURSOR c_get_fund_no(cv_fund_number IN VARCHAR2) IS
2865 SELECT fund_number
2866 FROM ozf_funds_All_b
2867 WHERE fund_number = cv_fund_number;
2868
2869 CURSOR c_threshold_id (cv_threshold_id IN NUMBER) IS
2870 SELECT threshold_id
2871 FROM ozf_thresholds_vl
2872 WHERE threshold_type = 'BUDGET'
2873 AND end_date_active > SYSDATE
2874 AND threshold_id = cv_threshold_id;
2875
2876 CURSOR c_bus_id_exists (p_bus_id IN NUMBER, p_org_id IN NUMBER) IS
2877 SELECT 1
2878 FROM hr_all_organization_units
2879 WHERE business_group_id
2880 IN (SELECT business_group_id
2881 FROM hr_all_organization_units
2882 WHERE organization_id = p_org_id
2883 AND NVL(date_from, SYSDATE) <= SYSDATE
2884 AND NVL(date_to, SYSDATE) >= SYSDATE)
2885 AND type = 'BU' AND NVL(date_from, SYSDATE) <= SYSDATE
2886 AND NVL(date_to, SYSDATE) >= SYSDATE
2887 AND organization_id = p_bus_id;
2888
2889 CURSOR c_currency(p_currency_code IN VARCHAR2) IS
2890 SELECT currency_code
2891 FROM fnd_currencies
2892 WHERE currency_code = p_currency_code
2893 AND enabled_flag='Y';
2894
2895 BEGIN
2896
2897 SAVEPOINT process_fund_from_adapter;
2898
2899 IF FND_API.to_boolean(p_init_msg_list) THEN
2900 FND_MSG_PUB.initialize;
2901 END IF;
2902
2903 IF NOT FND_API.compatible_api_call
2904 (l_api_version,
2905 p_api_version,
2906 l_api_name,
2907 g_pkg_name) THEN
2908 RAISE FND_API.g_exc_unexpected_error;
2909 END IF;
2910 x_return_status := FND_API.g_ret_sts_success;
2911
2912 IF G_DEBUG THEN
2913 OZF_UTILITY_PVT.debug_message( g_pkg_name || ' : ' || l_api_name);
2914 END IF;
2915
2916 --BPEL need default, otherwise throw exception.
2917 x_line_tbl(1).list_line_id := -1;
2918 x_line_tbl(1).list_line_number := '-1';
2919
2920
2921
2922
2923 IF (l_fund_id IS NULL) THEN
2924 l_mode := 'CREATE';
2925
2926 --//Valiidation
2927 IF (l_fund_rec.fund_type = FND_API.g_miss_char OR l_fund_rec.fund_type IS NULL) THEN
2928 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2929 FND_MESSAGE.set_name('OZF', 'OZF_FUND_NO_FUND_TYPE');
2930 --//The mandatory Fund Type is missing. Please enter a valid Fund Type.
2931 FND_MSG_PUB.add;
2932 END IF;
2933 RAISE FND_API.G_EXC_ERROR;
2934 ELSE
2935 l_lookup_check :=OZF_UTILITY_PVT.check_lookup_exists(
2936 p_lookup_table_name =>'OZF_LOOKUPS'
2937 ,p_lookup_type =>'OZF_FUND_TYPE'
2938 ,p_lookup_code => l_fund_rec.fund_type);
2939
2940 IF l_lookup_check = FND_API.g_false THEN
2941 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2942 FND_MESSAGE.set_name('OZF', 'OZF_FUND_BAD_FUND_TYPE');
2943 --//Program Error: The budget type is not valid.
2944 FND_MSG_PUB.add;
2945 END IF;
2946 RAISE FND_API.G_EXC_ERROR;
2947 ELSE
2948 IF l_fund_rec.fund_type = 'QUOTA' THEN
2949 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2950 FND_MESSAGE.set_name('OZF', 'OZF_FUND_BAD_FUND_TYPE');
2951 FND_MSG_PUB.add;
2952 END IF;
2953 RAISE FND_API.G_EXC_ERROR;
2954 END IF;
2955 END IF;
2956 END IF;
2957
2958
2959 OPEN c_resource_id(p_source);
2960 FETCH c_resource_id INTO l_fund_rec.owner;
2961 CLOSE c_resource_id;
2962
2963 l_pvt_fund_rec.fund_id := l_fund_rec.fund_id;
2964
2965 IF l_fund_rec.fund_number IS NULL OR l_fund_rec.fund_number = FND_API.g_miss_char THEN
2966 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2967 FND_MESSAGE.set_name('OZF', 'OZF_NO_FUND_ID_NUM');
2968 --//Please enter a valid value for required fields Fund ID/ Fund Number
2969 FND_MSG_PUB.add;
2970 END IF;
2971 RAISE FND_API.G_EXC_ERROR;
2972 ELSE
2973 OPEN c_get_fund_no(l_fund_rec.fund_number);
2974 FETCH c_get_fund_no INTO l_fund_number;
2975 CLOSE c_get_fund_no;
2976
2977 IF l_fund_number IS NOT NULL THEN
2978 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2979 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_FUND_NUM');
2980 --//The Fund Number provided is invalid. Please provide a valid Fund Number.
2981 FND_MSG_PUB.add;
2982 END IF;
2983 RAISE FND_API.G_EXC_ERROR;
2984 END IF;
2985 END IF;
2986
2987 l_pvt_fund_rec.fund_number := l_fund_rec.fund_number;
2988
2989 IF l_fund_rec.short_name IS NULL OR l_fund_rec.short_name = FND_API.g_miss_char THEN
2990 l_pvt_fund_rec.short_name := l_fund_rec.fund_number;
2991 ELSE
2992 l_pvt_fund_rec.short_name := l_fund_rec.short_name;
2993 END IF;
2994
2995 l_pvt_fund_rec.fund_type := l_fund_rec.fund_type;
2996 l_pvt_fund_rec.custom_setup_id := l_fund_rec.custom_setup_id;
2997 l_pvt_fund_rec.object_version_number := l_fund_rec.object_version_number;
2998 l_pvt_fund_rec.description := l_fund_rec.description;
2999 l_pvt_fund_rec.parent_fund_id := l_fund_rec.parent_fund_id;
3000 l_pvt_fund_rec.category_id := NVL(l_fund_rec.category_id,851);
3001
3002 IF l_fund_rec.business_unit_id <> fnd_api.g_miss_num AND l_fund_rec.business_unit_id IS NOT NULL THEN
3003 --check if the input business_unit_id is valid
3004 OPEN c_bus_id_exists (l_fund_rec.business_unit_id, l_fund_rec.org_id);
3005 FETCH c_bus_id_exists INTO l_bus_unit_exists;
3006 CLOSE c_bus_id_exists;
3007
3008 IF l_bus_unit_exists IS NULL THEN
3009 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3010 fnd_message.set_name('OZF', 'OZF_INVALID_BUS_UNIT_ID');
3011 fnd_msg_pub.add;
3012 END IF;
3013 RAISE FND_API.G_EXC_ERROR;
3014 END IF;
3015 END IF;
3016
3017 l_pvt_fund_rec.business_unit_id := l_fund_rec.business_unit_id;
3018 l_pvt_fund_rec.status_code := 'ACTIVE';
3019 l_pvt_fund_rec.user_status_id := OZF_UTILITY_PVT.get_default_user_status('OZF_FUND_STATUS','ACTIVE');
3020
3021 IF l_fund_rec.start_date_active IS NULL OR l_fund_rec.start_date_active = FND_API.g_miss_date THEN
3022 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3023 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_START_DATE_NULL');
3024 --//Please Enter a Start Date.
3025 FND_MSG_PUB.add;
3026 END IF;
3027 RAISE FND_API.G_EXC_ERROR;
3028 END IF;
3029
3030 IF ((l_fund_rec.start_date_active <> FND_API.g_miss_date AND l_fund_rec.start_date_active IS NOT NULL)
3031 AND (l_fund_rec.end_date_active <> FND_API.g_miss_date AND l_fund_rec.end_date_active IS NOT NULL)
3032 AND (l_fund_rec.end_date_active < l_fund_rec.start_date_active)) THEN
3033
3034 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3035 FND_MESSAGE.set_name('OZF', 'OZF_FUND_DATE_MISMATCH');
3036 --//Start date cannot be later than the end date.
3037 FND_MSG_PUB.add;
3038 END IF;
3039 RAISE FND_API.G_EXC_ERROR;
3040 END IF;
3041
3042 --//Fix for Bug 14158423
3043 /*
3044 IF l_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
3045 IF l_fund_rec.start_date_active < TRUNC(SYSDATE) THEN
3046 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3047 FND_MESSAGE.set_name('OZF', 'OZF_FUND_STARTDATE_MISMATCH');
3048 --//Fully Accrued Budget 'Start Date' cannot be before today's Date. Please enter a valid 'Start Date'.
3049 FND_MSG_PUB.add;
3050 END IF;
3051 RAISE FND_API.G_EXC_ERROR;
3052 END IF;
3053 END IF;
3054 */
3055 l_pvt_fund_rec.start_date_active := l_fund_rec.start_date_active;
3056 l_pvt_fund_rec.end_date_active := l_fund_rec.end_date_active;
3057
3058 l_pvt_fund_rec.start_period_name := l_fund_rec.start_period_name;
3059 l_pvt_fund_rec.end_period_name := l_fund_rec.end_period_name;
3060
3061 --if currency code is null, then raise exception
3062 IF l_fund_rec.currency_code_tc = fnd_api.g_miss_char OR l_fund_rec.currency_code_tc IS NULL THEN
3063 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3064 fnd_message.set_name('OZF', 'OZF_NO_CURR_CODE');
3065 fnd_msg_pub.add;
3066 END IF;
3067 RAISE FND_API.G_EXC_ERROR;
3068 ELSE
3069 OPEN c_currency(l_fund_rec.currency_code_tc);
3070 FETCH c_currency INTO l_currency_code;
3071 CLOSE c_currency;
3072 IF l_currency_code IS NULL THEN
3073 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3074 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_CURRENCY_CODE');
3075 --//Mandatory field Currency Code is missing. Please enter a valid Currency Code
3076 FND_MSG_PUB.add;
3077 END IF;
3078 RAISE FND_API.G_EXC_ERROR;
3079 END IF;
3080 END IF;
3081
3082 l_pvt_fund_rec.currency_code_tc := l_fund_rec.currency_code_tc;
3083 l_pvt_fund_rec.owner := l_fund_rec.owner;
3084 l_pvt_fund_rec.accrual_basis := l_fund_rec.accrual_basis;
3085 l_pvt_fund_rec.accrual_phase := l_fund_rec.accrual_phase;
3086 l_pvt_fund_rec.accrual_discount_level := l_fund_rec.accrual_discount_level;
3087
3088 IF l_fund_rec.threshold_id IS NOT NULL AND l_fund_rec.threshold_id <> FND_API.g_miss_num THEN
3089 OPEN c_threshold_id (l_fund_rec.threshold_id);
3090 FETCH c_threshold_id INTO l_threshold_id;
3091 CLOSE c_threshold_id;
3092
3093 IF l_threshold_id IS NULL THEN
3094 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3095 FND_MESSAGE.set_name('OZF', 'OZF_THRESHOLD_NO_THRESHOLD_ID');
3096 --//Please enter a valid Id for Threshold.
3097 FND_MSG_PUB.add;
3098 END IF;
3099 RAISE FND_API.G_EXC_ERROR;
3100 END IF;
3101 END IF;
3102 l_pvt_fund_rec.threshold_id := l_fund_rec.threshold_id;
3103
3104 l_pvt_fund_rec.task_id := l_fund_rec.task_id;
3105 l_pvt_fund_rec.org_id := l_fund_rec.org_id;
3106 l_pvt_fund_rec.liability_flag := NVL(l_fund_rec.liability_flag,'Y'); --//Fix for Bug 16275083
3107 l_pvt_fund_rec.ledger_id := l_fund_rec.ledger_id;
3108 l_pvt_fund_rec.accrued_liable_account := l_fund_rec.accrued_liable_account;
3109 l_pvt_fund_rec.ded_adjustment_account := l_fund_rec.ded_adjustment_account;
3110 l_pvt_fund_rec.accrual_quantity := l_fund_rec.accrual_quantity;
3111 l_pvt_fund_rec.apply_accrual_on := l_fund_rec.apply_accrual_on;
3112 l_pvt_fund_rec.retroactive_flag := l_fund_rec.retroactive_flag;
3113 l_pvt_fund_rec.qualifier_id := l_fund_rec.qualifier_id;
3114 l_pvt_fund_rec.accrue_to_level_id := l_fund_rec.accrue_to_level_id;
3115 l_pvt_fund_rec.attribute_category := l_fund_rec.attribute_category;
3116 l_pvt_fund_rec.attribute1 := l_fund_rec.attribute1;
3117 l_pvt_fund_rec.attribute2 := l_fund_rec.attribute2;
3118 l_pvt_fund_rec.attribute3 := l_fund_rec.attribute3;
3119 l_pvt_fund_rec.attribute4 := l_fund_rec.attribute4;
3120 l_pvt_fund_rec.attribute5 := l_fund_rec.attribute5;
3121 l_pvt_fund_rec.attribute6 := l_fund_rec.attribute6;
3122 l_pvt_fund_rec.attribute7 := l_fund_rec.attribute7;
3123 l_pvt_fund_rec.attribute8 := l_fund_rec.attribute8;
3124 l_pvt_fund_rec.attribute9 := l_fund_rec.attribute9;
3125 l_pvt_fund_rec.attribute10 := l_fund_rec.attribute10;
3126 l_pvt_fund_rec.attribute11 := l_fund_rec.attribute11;
3127 l_pvt_fund_rec.attribute12 := l_fund_rec.attribute12;
3128 l_pvt_fund_rec.attribute13 := l_fund_rec.attribute13;
3129 l_pvt_fund_rec.attribute14 := l_fund_rec.attribute14;
3130 l_pvt_fund_rec.attribute15 := l_fund_rec.attribute15;
3131
3132
3133 --//Ledger Validations
3134 --//Get the Ledger details from Budget Category
3135 OPEN c_ledger_from_cat;
3136 FETCH c_ledger_from_cat INTO l_ledger_id,l_accr_liab_acct,l_ded_adj_acct;
3137 CLOSE c_ledger_from_cat;
3138
3139 IF (l_pvt_fund_rec.ledger_id = FND_API.g_miss_num OR l_pvt_fund_rec.ledger_id IS NULL) THEN
3140
3141 IF l_ledger_id IS NULL THEN
3142 --//Fetch from System Parameters
3143 OPEN c_ledger_from_sysParam;
3144 FETCH c_ledger_from_sysParam INTO l_ledger_id;
3145 CLOSE c_ledger_from_sysParam;
3146
3147 --//If Ledger ID is null again, derive it from the value of MO: Default Operating Unit.
3148 IF l_ledger_id IS NULL THEN
3149 OPEN c_ledger_from_HR;
3150 FETCH c_ledger_from_HR INTO l_ledger_id;
3151 CLOSE c_ledger_from_HR;
3152 END IF;
3153 END IF;
3154
3155 IF l_ledger_id IS NOT NULL THEN
3156 l_pvt_fund_rec.ledger_id := l_ledger_id;
3157 ELSE
3158 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3159 FND_MESSAGE.set_name('OZF', 'OZF_LEDGER_DERIVE_FAILURE');
3160 --//Not able to derive ledger identifier. Please either pass a valid ledger Identifier or set the Ledger identifier in Buudget category/System parameters/Operating Unit level.
3161 FND_MSG_PUB.add;
3162 END IF;
3163 RAISE FND_API.G_EXC_ERROR;
3164 END IF;
3165 ELSE
3166 OPEN c_ledger_from_gl(l_pvt_fund_rec.ledger_id);
3167 FETCH c_ledger_from_gl INTO l_ledger_count;
3168 CLOSE c_ledger_from_gl;
3169
3170 IF l_ledger_count = 0 THEN
3171 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3172 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_LEDGER_ID');
3173 --//The Ledger ID provided is invalid. Please provide a valid Ledger ID
3174 FND_MSG_PUB.add;
3175 END IF;
3176 RAISE FND_API.G_EXC_ERROR;
3177 END IF;
3178 END IF;
3179
3180 --// Sales/Expense/Charge Account and Accrual Liability Account Validations
3181 l_ccid_count := 0;
3182 IF (l_pvt_fund_rec.accrued_liable_account = FND_API.g_miss_num OR l_pvt_fund_rec.accrued_liable_account IS NULL) THEN
3183 l_pvt_fund_rec.accrued_liable_account := l_accr_liab_acct;
3184 ELSE
3185 OPEN c_gl_ccid(l_pvt_fund_rec.accrued_liable_account);
3186 FETCH c_gl_ccid INTO l_ccid_count;
3187 CLOSE c_gl_ccid;
3188
3189 IF l_ccid_count = 0 THEN
3190 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3191 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_ACC_LIAB_CCID');
3192 --//Accrual Liability Account provided is invalid. Please provide a valid Accrual Liability Account.
3193 FND_MSG_PUB.add;
3194 END IF;
3195 RAISE FND_API.G_EXC_ERROR;
3196 END IF;
3197 END IF;
3198
3199 l_ccid_count := 0;
3200 IF (l_pvt_fund_rec.ded_adjustment_account = FND_API.g_miss_num OR l_pvt_fund_rec.ded_adjustment_account IS NULL) THEN
3201 l_pvt_fund_rec.ded_adjustment_account := l_ded_adj_acct;
3202 ELSE
3203 OPEN c_gl_ccid(l_pvt_fund_rec.ded_adjustment_account);
3204 FETCH c_gl_ccid INTO l_ccid_count;
3205 CLOSE c_gl_ccid;
3206
3207 IF l_ccid_count = 0 THEN
3208 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3209 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_DED_ADJ_CCID');
3210 --//Sales/Expense/Charge Account provided is invalid. Please provide a valid Sales/Expense/Charge Account.
3211 FND_MSG_PUB.add;
3212 END IF;
3213 RAISE FND_API.G_EXC_ERROR;
3214 END IF;
3215 END IF;
3216
3217
3218 -- set the smuckers seeded custom setup id based on the fund type
3219 IF (l_pvt_fund_rec.fund_type = 'FIXED') THEN
3220 l_pvt_fund_rec.custom_setup_id := 301; -- seeded custom setup for Smuckers Fixed fund
3221 l_pvt_fund_rec.original_budget := l_fund_rec.original_budget;
3222 l_pvt_fund_rec.liability_flag := 'N';
3223
3224 ELSIF (l_pvt_fund_rec.fund_type = 'FULLY_ACCRUED') THEN
3225 l_pvt_fund_rec.custom_setup_id := 302; -- seeded custom setup for Smuckers Live fund
3226 --l_pvt_fund_rec.liability_flag := 'N'; -- always set to 'N' for Smuckers -- //Commented for bug 16275083
3227 l_pvt_fund_rec.accrual_basis := 'CUSTOMER';
3228 l_pvt_fund_rec.accrual_phase :='ACCRUAL';
3229 l_pvt_fund_rec.accrual_discount_level := 'LINE';
3230 l_pvt_fund_rec.original_budget := 0;
3231 l_pvt_fund_rec.holdback_amt := 0;
3232 END IF;
3233
3234 ELSE
3235 l_mode := 'UPDATE';
3236
3237 --Bugfix:6350294
3238 l_pvt_fund_rec.attribute_category := l_fund_rec.attribute_category;
3239 l_pvt_fund_rec.attribute1 := l_fund_rec.attribute1;
3240 l_pvt_fund_rec.attribute2 := l_fund_rec.attribute2;
3241 l_pvt_fund_rec.attribute3 := l_fund_rec.attribute3;
3242 l_pvt_fund_rec.attribute4 := l_fund_rec.attribute4;
3243 l_pvt_fund_rec.attribute5 := l_fund_rec.attribute5;
3244 l_pvt_fund_rec.attribute6 := l_fund_rec.attribute6;
3245 l_pvt_fund_rec.attribute7 := l_fund_rec.attribute7;
3246 l_pvt_fund_rec.attribute8 := l_fund_rec.attribute8;
3247 l_pvt_fund_rec.attribute9 := l_fund_rec.attribute9;
3248 l_pvt_fund_rec.attribute10 := l_fund_rec.attribute10;
3249 l_pvt_fund_rec.attribute11 := l_fund_rec.attribute11;
3250 l_pvt_fund_rec.attribute12 := l_fund_rec.attribute12;
3251 l_pvt_fund_rec.attribute13 := l_fund_rec.attribute13;
3252 l_pvt_fund_rec.attribute14 := l_fund_rec.attribute14;
3253 l_pvt_fund_rec.attribute15 := l_fund_rec.attribute15;
3254
3255 OPEN c_fund_details (l_fund_id);
3256 FETCH c_fund_details INTO l_pvt_fund_rec.fund_number,
3257 l_pvt_fund_rec.short_name,
3258 l_pvt_fund_rec.fund_type,
3259 l_pvt_fund_rec.description,
3260 l_pvt_fund_rec.parent_fund_id,
3261 l_pvt_fund_rec.business_unit_id,
3262 l_pvt_fund_rec.status_code,
3263 l_pvt_fund_rec.start_date_active,
3264 l_pvt_fund_rec.end_date_active,
3265 l_pvt_fund_rec.start_period_name,
3266 l_pvt_fund_rec.end_period_name,
3267 l_pvt_fund_rec.original_budget,
3268 l_pvt_fund_rec.holdback_amt,
3269 l_pvt_fund_rec.currency_code_tc,
3270 l_pvt_fund_rec.owner,
3271 l_pvt_fund_rec.accrual_basis,
3272 l_pvt_fund_rec.accrual_phase,
3273 l_pvt_fund_rec.accrual_discount_level,
3274 l_pvt_fund_rec.threshold_id,
3275 l_pvt_fund_rec.task_id,
3276 l_pvt_fund_rec.accrued_liable_account,
3277 l_pvt_fund_rec.ded_adjustment_account,
3278 l_pvt_fund_rec.product_spread_time_id,
3279 l_pvt_fund_rec.object_version_number,
3280 l_pvt_fund_rec.org_id,
3281 l_pvt_fund_rec.liability_flag;
3282
3283 IF c_fund_details%NOTFOUND THEN
3284 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3285 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_FUND_ID');
3286 --//The Fund ID provided is invalid. Please provide a valid Fund ID.
3287 FND_MSG_PUB.add;
3288 END IF;
3289 RAISE FND_API.G_EXC_ERROR;
3290 END IF;
3291 CLOSE c_fund_details;
3292
3293 l_pvt_fund_rec.fund_id :=l_fund_id;
3294
3295 l_pvt_fund_rec.short_name := l_fund_rec.short_name;
3296 IF (l_pvt_fund_rec.fund_type = 'FIXED') THEN
3297 l_pvt_fund_rec.original_budget := l_fund_rec.original_budget;
3298 ELSE
3299 l_pvt_fund_rec.liability_flag := NVL(l_fund_rec.liability_flag,l_pvt_fund_rec.liability_flag);
3300 l_pvt_fund_rec.end_date_active := l_fund_rec.end_date_active;
3301 END IF;
3302
3303 END IF;
3304
3305 IF (l_mode = 'CREATE') THEN
3306 -- Create mode
3307 OZF_FUNDS_PVT.create_fund(p_api_version => l_api_version
3308 ,p_init_msg_list => p_init_msg_list
3309 ,p_commit => p_commit
3310 ,p_validation_level => l_validation_level
3311 ,x_return_status => x_return_status
3312 ,x_msg_count => x_msg_count
3313 ,x_msg_data => x_msg_data
3314 ,p_fund_rec => l_pvt_fund_rec
3315 ,x_fund_id => x_fund_id
3316 );
3317
3318
3319 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3320 RAISE fnd_api.g_exc_unexpected_error;
3321 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3322 RAISE fnd_api.g_exc_error;
3323 END IF;
3324
3325 IF l_pvt_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
3326 OPEN c_list_header_id(x_fund_id);
3327 FETCH c_list_header_id INTO l_qp_list_header_id;
3328 CLOSE c_list_header_id;
3329
3330 l_modifier_list_rec.modifier_operation := 'UPDATE';
3331 l_modifier_list_rec.offer_operation := 'UPDATE';
3332 l_modifier_list_rec.qp_list_header_id := l_qp_list_header_id;
3333
3334 l_offer_hdr_rec.qp_list_header_id := l_qp_list_header_id;
3335 l_offer_hdr_rec.status_code := 'ACTIVE';
3336 l_offer_hdr_rec.offer_type := 'ACCRUAL';
3337 l_offer_hdr_rec.user_status_id :=1604;
3338
3339 OZF_OFFER_PVT.update_offer_status
3340 (
3341 p_commit => fnd_api.g_false,
3342 x_return_status => l_return_status,
3343 x_msg_count => x_msg_count,
3344 x_msg_data => x_msg_data,
3345 p_modifier_list_rec => l_offer_hdr_rec
3346 );
3347
3348
3349 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3350 RAISE fnd_api.g_exc_unexpected_error;
3351 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
3352 RAISE fnd_api.g_exc_error;
3353 END IF;
3354
3355 --//Bugfix : 13935880
3356 --//OZF_ACT_BUDGETS table needs to be populated when the Accrual fund created in ACTIVE status. This code flow is same as Budget Approval flow in Update_fund procedure
3357 l_pvt_fund_rec2.fund_id := x_fund_id;
3358 OZF_FUNDS_PVT.Complete_Fund_Rec(l_pvt_fund_rec2,l_pvt_fund_rec3);
3359
3360 OZF_FUNDRULES_PVT.process_accrual(
3361 p_fund_rec => l_pvt_fund_rec3
3362 ,p_api_version => l_api_version
3363 ,p_mode => 'ACTIVE'
3364 ,x_return_status => l_return_status
3365 ,x_msg_count => x_msg_count
3366 ,x_msg_data => x_msg_data );
3367
3368 IF l_return_status = fnd_api.g_ret_sts_error THEN
3369 RAISE fnd_api.g_exc_error;
3370 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3371 RAISE fnd_api.g_exc_unexpected_error;
3372 END IF;
3373
3374 IF p_modifier_line_tbl.COUNT > 0 THEN
3375 FOR i IN p_modifier_line_tbl.FIRST..p_modifier_line_tbl.LAST LOOP
3376 l_modifier_line_tbl(i).offer_line_type := NVL(p_modifier_line_tbl(i).offer_line_type,Fnd_Api.g_miss_char);
3377 l_modifier_line_tbl(i).operation := NVL(p_modifier_line_tbl(i).operation,'CREATE');
3378 l_modifier_line_tbl(i).list_line_id := NVL(p_modifier_line_tbl(i).list_line_id,Fnd_Api.g_miss_num);
3379 l_modifier_line_tbl(i).list_header_id := NVL(p_modifier_line_tbl(i).list_header_id,fnd_api.g_miss_num);
3380 l_modifier_line_tbl(i).list_line_type_code := NVL(p_modifier_line_tbl(i).list_line_type_code,'DIS');
3381 l_modifier_line_tbl(i).operand := NVL(p_modifier_line_tbl(i).operand,Fnd_Api.g_miss_num);
3382 l_modifier_line_tbl(i).start_date_active := NVL(p_modifier_line_tbl(i).start_date_active,Fnd_Api.g_miss_date) ;
3383 l_modifier_line_tbl(i).end_date_active := NVL(p_modifier_line_tbl(i).end_date_active,Fnd_Api.g_miss_date) ;
3384 l_modifier_line_tbl(i).arithmetic_operator := NVL(p_modifier_line_tbl(i).arithmetic_operator,Fnd_Api.g_miss_char);
3385 l_modifier_line_tbl(i).active_flag := NVL(p_modifier_line_tbl(i).active_flag,Fnd_Api.g_miss_char);
3386 l_modifier_line_tbl(i).qd_operand := NVL(p_modifier_line_tbl(i).qd_operand,Fnd_Api.g_miss_num);
3387 l_modifier_line_tbl(i).qd_arithmetic_operator := NVL(p_modifier_line_tbl(i).qd_arithmetic_operator,Fnd_Api.g_miss_char);
3388 l_modifier_line_tbl(i).qd_related_deal_lines_id := NVL(p_modifier_line_tbl(i).qd_related_deal_lines_id,Fnd_Api.g_miss_num);
3389 l_modifier_line_tbl(i).qd_object_version_number := NVL(p_modifier_line_tbl(i).qd_object_version_number,Fnd_Api.g_miss_num);
3390 l_modifier_line_tbl(i).qd_estimated_qty_is_max := NVL(p_modifier_line_tbl(i).qd_estimated_qty_is_max,Fnd_Api.g_miss_char);
3391 l_modifier_line_tbl(i).qd_list_line_id := NVL(p_modifier_line_tbl(i).qd_list_line_id,Fnd_Api.g_miss_num);
3392 l_modifier_line_tbl(i).qd_estimated_amount_is_max := NVL(p_modifier_line_tbl(i).qd_estimated_amount_is_max,Fnd_Api.g_miss_char);
3393 l_modifier_line_tbl(i).estim_gl_value := NVL(p_modifier_line_tbl(i).estim_gl_value,Fnd_Api.g_miss_num);
3394 l_modifier_line_tbl(i).benefit_price_list_line_id := NVL(p_modifier_line_tbl(i).benefit_price_list_line_id,Fnd_Api.g_miss_num);
3395 l_modifier_line_tbl(i).benefit_limit := NVL(p_modifier_line_tbl(i).benefit_limit,Fnd_Api.g_miss_num) ;
3396 l_modifier_line_tbl(i).benefit_qty := NVL(p_modifier_line_tbl(i).benefit_qty,Fnd_Api.g_miss_num) ;
3397 l_modifier_line_tbl(i).benefit_uom_code := NVL(p_modifier_line_tbl(i).benefit_uom_code,Fnd_Api.g_miss_char);
3398 l_modifier_line_tbl(i).substitution_context := NVL(p_modifier_line_tbl(i).substitution_context,Fnd_Api.g_miss_char);
3399 l_modifier_line_tbl(i).substitution_attr := NVL(p_modifier_line_tbl(i).substitution_attr,Fnd_Api.g_miss_char) ;
3400 l_modifier_line_tbl(i).substitution_val := NVL(p_modifier_line_tbl(i).substitution_val,Fnd_Api.g_miss_char);
3401 l_modifier_line_tbl(i).price_break_type_code := NVL(p_modifier_line_tbl(i).price_break_type_code,fnd_api.g_miss_char);
3402 l_modifier_line_tbl(i).pricing_attribute_id := NVL(p_modifier_line_tbl(i).pricing_attribute_id,Fnd_Api.g_miss_num);
3403 l_modifier_line_tbl(i).product_attribute_context := NVL(p_modifier_line_tbl(i).product_attribute_context,fnd_api.g_miss_char);
3404 l_modifier_line_tbl(i).product_attr := NVL(p_modifier_line_tbl(i).product_attr,'PRICING_ATTRIBUTE1');
3405 l_modifier_line_tbl(i).product_attr_val := NVL(p_modifier_line_tbl(i).product_attr_val,Fnd_Api.g_miss_char);
3406 l_modifier_line_tbl(i).product_uom_code := NVL(p_modifier_line_tbl(i).product_uom_code,Fnd_Api.g_miss_char);
3407 l_modifier_line_tbl(i).pricing_attribute_context := NVL(p_modifier_line_tbl(i).pricing_attribute_context,fnd_api.g_miss_char);
3408 l_modifier_line_tbl(i).pricing_attr := NVL(p_modifier_line_tbl(i).pricing_attr,'PRICING_ATTRIBUTE10');
3409 l_modifier_line_tbl(i).pricing_attr_value_from := NVL(p_modifier_line_tbl(i).pricing_attr_value_from,'1');
3410 l_modifier_line_tbl(i).pricing_attr_value_to := NVL(p_modifier_line_tbl(i).pricing_attr_value_to,Fnd_Api.g_miss_char) ;
3411 l_modifier_line_tbl(i).excluder_flag := NVL(p_modifier_line_tbl(i).excluder_flag,fnd_api.g_miss_char);
3412 l_modifier_line_tbl(i).order_value_from := NVL(p_modifier_line_tbl(i).order_value_from,Fnd_Api.g_miss_char);
3413 l_modifier_line_tbl(i).order_value_to := NVL(p_modifier_line_tbl(i).order_value_to,Fnd_Api.g_miss_char);
3414 l_modifier_line_tbl(i).qualifier_id := NVL(p_modifier_line_tbl(i).qualifier_id,Fnd_Api.g_miss_num);
3415 l_modifier_line_tbl(i).comments := NVL(p_modifier_line_tbl(i).comments,Fnd_Api.g_miss_char);
3416 l_modifier_line_tbl(i).context := NVL(p_modifier_line_tbl(i).context,Fnd_Api.g_miss_char) ;
3417 l_modifier_line_tbl(i).attribute1 := NVL(p_modifier_line_tbl(i).attribute1,Fnd_Api.g_miss_char);
3418 l_modifier_line_tbl(i).attribute2 := NVL(p_modifier_line_tbl(i).attribute2,Fnd_Api.g_miss_char);
3419 l_modifier_line_tbl(i).attribute3 := NVL(p_modifier_line_tbl(i).attribute3,Fnd_Api.g_miss_char);
3420 l_modifier_line_tbl(i).attribute4 := NVL(p_modifier_line_tbl(i).attribute4,Fnd_Api.g_miss_char);
3421 l_modifier_line_tbl(i).attribute5 := NVL(p_modifier_line_tbl(i).attribute5,Fnd_Api.g_miss_char);
3422 l_modifier_line_tbl(i).attribute6 := NVL(p_modifier_line_tbl(i).attribute6,Fnd_Api.g_miss_char);
3423 l_modifier_line_tbl(i).attribute7 := NVL(p_modifier_line_tbl(i).attribute7,Fnd_Api.g_miss_char);
3424 l_modifier_line_tbl(i).attribute8 := NVL(p_modifier_line_tbl(i).attribute8,Fnd_Api.g_miss_char);
3425 l_modifier_line_tbl(i).attribute9 := NVL(p_modifier_line_tbl(i).attribute9,Fnd_Api.g_miss_char);
3426 l_modifier_line_tbl(i).attribute10 := NVL(p_modifier_line_tbl(i).attribute10,Fnd_Api.g_miss_char);
3427 l_modifier_line_tbl(i).attribute11 := NVL(p_modifier_line_tbl(i).attribute11,Fnd_Api.g_miss_char);
3428 l_modifier_line_tbl(i).attribute12 := NVL(p_modifier_line_tbl(i).attribute12,Fnd_Api.g_miss_char);
3429 l_modifier_line_tbl(i).attribute13 := NVL(p_modifier_line_tbl(i).attribute13,Fnd_Api.g_miss_char);
3430 l_modifier_line_tbl(i).attribute14 := NVL(p_modifier_line_tbl(i).attribute14,Fnd_Api.g_miss_char);
3431 l_modifier_line_tbl(i).attribute15 := NVL(p_modifier_line_tbl(i).attribute15,Fnd_Api.g_miss_char);
3432 l_modifier_line_tbl(i).max_qty_per_order := NVL(p_modifier_line_tbl(i).max_qty_per_order,Fnd_Api.g_miss_num);
3433 l_modifier_line_tbl(i).max_qty_per_order_id := NVL(p_modifier_line_tbl(i).max_qty_per_order_id,Fnd_Api.g_miss_num);
3434 l_modifier_line_tbl(i).max_qty_per_customer := NVL(p_modifier_line_tbl(i).max_qty_per_customer,Fnd_Api.g_miss_num);
3435 l_modifier_line_tbl(i).max_qty_per_customer_id := NVL(p_modifier_line_tbl(i).max_qty_per_customer_id,Fnd_Api.g_miss_num);
3436 l_modifier_line_tbl(i).max_qty_per_rule := NVL(p_modifier_line_tbl(i).max_qty_per_rule,Fnd_Api.g_miss_num);
3437 l_modifier_line_tbl(i).max_qty_per_rule_id := NVL(p_modifier_line_tbl(i).max_qty_per_rule_id,Fnd_Api.g_miss_num);
3438 l_modifier_line_tbl(i).max_orders_per_customer := NVL(p_modifier_line_tbl(i).max_orders_per_customer,Fnd_Api.g_miss_num);
3439 l_modifier_line_tbl(i).max_orders_per_customer_id := NVL(p_modifier_line_tbl(i).max_orders_per_customer_id,Fnd_Api.g_miss_num);
3440 l_modifier_line_tbl(i).max_amount_per_rule := NVL(p_modifier_line_tbl(i).max_amount_per_rule,Fnd_Api.g_miss_num);
3441 l_modifier_line_tbl(i).max_amount_per_rule_id := NVL(p_modifier_line_tbl(i).max_amount_per_rule_id,Fnd_Api.g_miss_num);
3442 l_modifier_line_tbl(i).estimate_qty_uom := NVL(p_modifier_line_tbl(i).estimate_qty_uom,Fnd_Api.g_miss_char);
3443 l_modifier_line_tbl(i).generate_using_formula_id := NVL(p_modifier_line_tbl(i).generate_using_formula_id,Fnd_Api.g_miss_num);
3444 l_modifier_line_tbl(i).price_by_formula_id := NVL(p_modifier_line_tbl(i).price_by_formula_id,Fnd_Api.g_miss_num);
3445 l_modifier_line_tbl(i).generate_using_formula := NVL(p_modifier_line_tbl(i).generate_using_formula,Fnd_Api.g_miss_char);
3446 l_modifier_line_tbl(i).price_by_formula := NVL(p_modifier_line_tbl(i).price_by_formula,Fnd_Api.g_miss_char);
3447 l_modifier_line_tbl(i).list_line_no := NVL(p_modifier_line_tbl(i).list_line_no,Fnd_Api.g_miss_char);
3448
3449
3450 IF l_modifier_line_tbl(i).PRODUCT_ATTR_VAL = fnd_api.g_miss_num OR l_modifier_line_tbl(i).PRODUCT_ATTR_VAL is NULL THEN
3451 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3452 fnd_message.set_name('OZF', 'OZF_ACCRUAL_NO_PROD');
3453 --//An accrual type budget must have a product eligibility defined to go 'Active'. Please define product eligibility for the budget.
3454 fnd_msg_pub.add;
3455 END IF;
3456 RAISE fnd_api.g_exc_error;
3457 END IF;
3458
3459 --//BK -XREF Implementation
3460 IF l_modifier_line_tbl(i).list_line_id IS NOT NULL THEN
3461 OPEN c_line_id_chk(l_modifier_line_tbl(i).list_line_id);
3462 FETCH c_line_id_chk INTO l_xref_line_id;
3463 IF l_xref_line_id IS NOT NULL THEN
3464 l_modifier_line_tbl(i).list_line_id := l_xref_line_id;
3465 END IF;
3466 CLOSE c_line_id_chk;
3467 END IF;
3468
3469 OPEN c_uom ( l_modifier_line_tbl(i).product_attr_val
3470 , fnd_profile.value('QP_ORGANIZATION_ID')
3471 );
3472 FETCH c_uom INTO l_uom;
3473 IF ( c_uom%NOTFOUND) THEN
3474 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3475 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
3476 FND_MSG_PUB.add;
3477 END IF;
3478 RAISE Fnd_Api.g_exc_error;
3479 END IF;
3480 CLOSE c_uom;
3481 l_modifier_line_tbl(i).PRODUCT_UOM_CODE := l_uom;
3482
3483 END LOOP; -- FOR i IN p_modifier_line_tbl.FIRST..p_modifier_line_tbl.LAST LOOP
3484 END IF;
3485
3486 IF p_qualifier_tbl.COUNT > 0 THEN
3487 FOR j IN p_qualifier_tbl.FIRST..p_qualifier_tbl.LAST LOOP
3488 l_qualifier_tbl(j).qualifier_context := NVL(p_qualifier_tbl(j).qualifier_context,'CUSTOMER');
3489 l_qualifier_tbl(j).qualifier_attribute := NVL(p_qualifier_tbl(j).qualifier_attribute,'QUALIFIER_ATTRIBUTE2');
3490 l_qualifier_tbl(j).qualifier_attr_value := NVL(p_qualifier_tbl(j).qualifier_attr_value,Fnd_Api.g_miss_char);
3491 l_qualifier_tbl(j).qualifier_attr_value_to := NVL(p_qualifier_tbl(j).qualifier_attr_value_to,Fnd_Api.g_miss_char);
3492 l_qualifier_tbl(j).comparison_operator_code := NVL(p_qualifier_tbl(j).comparison_operator_code,'=');
3493 l_qualifier_tbl(j).qualifier_grouping_no := NVL(p_qualifier_tbl(j).qualifier_grouping_no,-1);
3494 l_qualifier_tbl(j).list_line_id := NVL(p_qualifier_tbl(j).list_line_id,-1);
3495 l_qualifier_tbl(j).list_header_id := NULL;
3496 l_qualifier_tbl(j).qualifier_id := NULL;
3497 l_qualifier_tbl(j).start_date_active := NVL(p_qualifier_tbl(j).start_date_active,Fnd_Api.g_miss_date);
3498 l_qualifier_tbl(j).end_date_active := NVL(p_qualifier_tbl(j).end_date_active,Fnd_Api.g_miss_date);
3499 l_qualifier_tbl(j).activity_market_segment_id := NVL(p_qualifier_tbl(j).activity_market_segment_id,Fnd_Api.g_miss_num);
3500 l_qualifier_tbl(j).operation := NVL(p_qualifier_tbl(j).operation,'CREATE');
3501 l_qualifier_tbl(j).context := NVL(p_qualifier_tbl(j).context,Fnd_Api.g_miss_char);
3502 l_qualifier_tbl(j).attribute1 := NVL(p_qualifier_tbl(j).attribute1,Fnd_Api.g_miss_char);
3503 l_qualifier_tbl(j).attribute2 := NVL(p_qualifier_tbl(j).attribute2,Fnd_Api.g_miss_char);
3504 l_qualifier_tbl(j).attribute3 := NVL(p_qualifier_tbl(j).attribute3,Fnd_Api.g_miss_char);
3505 l_qualifier_tbl(j).attribute4 := NVL(p_qualifier_tbl(j).attribute4,Fnd_Api.g_miss_char);
3506 l_qualifier_tbl(j).attribute5 := NVL(p_qualifier_tbl(j).attribute5,Fnd_Api.g_miss_char);
3507 l_qualifier_tbl(j).attribute6 := NVL(p_qualifier_tbl(j).attribute6,Fnd_Api.g_miss_char);
3508 l_qualifier_tbl(j).attribute7 := NVL(p_qualifier_tbl(j).attribute7,Fnd_Api.g_miss_char);
3509 l_qualifier_tbl(j).attribute8 := NVL(p_qualifier_tbl(j).attribute8,Fnd_Api.g_miss_char);
3510 l_qualifier_tbl(j).attribute9 := NVL(p_qualifier_tbl(j).attribute9,Fnd_Api.g_miss_char);
3511 l_qualifier_tbl(j).attribute10 := NVL(p_qualifier_tbl(j).attribute10,Fnd_Api.g_miss_char);
3512 l_qualifier_tbl(j).attribute11 := NVL(p_qualifier_tbl(j).attribute11,Fnd_Api.g_miss_char);
3513 l_qualifier_tbl(j).attribute12 := NVL(p_qualifier_tbl(j).attribute12,Fnd_Api.g_miss_char);
3514 l_qualifier_tbl(j).attribute13 := NVL(p_qualifier_tbl(j).attribute13,Fnd_Api.g_miss_char);
3515 l_qualifier_tbl(j).attribute14 := NVL(p_qualifier_tbl(j).attribute14,Fnd_Api.g_miss_char);
3516 l_qualifier_tbl(j).attribute15 := NVL(p_qualifier_tbl(j).attribute15,Fnd_Api.g_miss_char);
3517
3518 IF l_qualifier_tbl(j).qualifier_attr_value = fnd_api.g_miss_num OR l_qualifier_tbl(j).qualifier_attr_value is NULL THEN
3519 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3520 fnd_message.set_name('OZF', 'OZF_FUND_NO_CUSTOMER');
3521 fnd_msg_pub.add;
3522 END IF;
3523 RAISE fnd_api.g_exc_error;
3524 END IF;
3525
3526 END LOOP;
3527 ELSE
3528 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3529 fnd_message.set_name('OZF', 'OZF_FUND_NO_CUSTOMER');
3530 fnd_msg_pub.add;
3531 END IF;
3532 RAISE fnd_api.g_exc_error;
3533 END IF;
3534
3535 OZF_OFFER_PUB.process_modifiers(p_init_msg_list => p_init_msg_list
3536 ,p_api_version => l_api_version
3537 ,p_commit => p_commit
3538 ,x_return_status => l_return_status
3539 ,x_msg_count => x_msg_count
3540 ,x_msg_data => x_msg_data
3541 ,p_offer_type => 'ACCRUAL'
3542 ,p_modifier_list_rec => l_modifier_list_rec --offer header details
3543 ,p_modifier_line_tbl => l_modifier_line_tbl --discount rules
3544 ,p_qualifier_tbl => l_qualifier_tbl --market eligibilty
3545 ,p_budget_tbl => l_budget_tbl
3546 ,p_act_product_tbl => l_act_product_tbl
3547 ,p_discount_tbl => l_discount_tbl
3548 ,p_excl_tbl => l_excl_tbl
3549 ,p_offer_tier_tbl => l_offer_tier_tbl
3550 ,p_prod_tbl => l_prod_tbl
3551 ,p_na_qualifier_tbl => l_na_qualifier_tbl
3552 ,x_qp_list_header_id => l_qp_list_header_id
3553 ,x_error_location => l_error_location
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 --//Fix for Bug 13654821
3563 FOR list_line_rec IN c_list_line(x_fund_id) LOOP
3564 x_line_tbl(k).list_line_number := list_line_rec.list_line_no;
3565 -- x_line_tbl(k).list_line_id := list_line_rec.list_line_id;
3566 x_line_tbl(k).list_line_id := TO_NUMBER(list_line_rec.list_line_id||'.'||'3');
3567
3568 INSERT INTO ozf_xref_map ( map_attr_id
3569 , list_line_id
3570 , list_line_number
3571 , xref_line_id_value
3572 , xref_line_number_value
3573 )
3574 VALUES ( x_fund_id
3575 , list_line_rec.list_line_id
3576 , list_line_rec.list_line_no
3577 , x_line_tbl(k).list_line_id
3578 , x_line_tbl(k).list_line_number
3579 );
3580 k := k+1;
3581 END LOOP;
3582
3583 END IF;
3584
3585 -- market eligibilities
3586 IF l_pvt_fund_rec.fund_type = 'FIXED' THEN
3587 IF p_qualifier_tbl.COUNT > 0 THEN
3588 FOR i IN p_qualifier_tbl.FIRST..p_qualifier_tbl.LAST LOOP
3589 l_seg_rec.market_segment_id := p_qualifier_tbl(i).qualifier_attr_value;
3590 IF l_seg_rec.market_segment_id = fnd_api.g_miss_num OR l_seg_rec.market_segment_id is NULL THEN
3591 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3592 fnd_message.set_name('OZF', 'OZF_FUND_NO_CUSTOMER');
3593 fnd_msg_pub.add;
3594 END IF;
3595 RAISE fnd_api.g_exc_error;
3596 END IF;
3597 l_seg_rec.act_market_segment_used_by_id := x_fund_id;
3598 l_seg_rec.arc_act_market_segment_used_by := 'FUND';
3599 l_seg_rec.segment_type := 'CUSTOMER';
3600 l_seg_rec.object_version_number := 1.0;
3601
3602 AMS_ACT_MARKET_SEGMENTS_PVT.create_market_segments(
3603 p_api_version => l_api_version
3604 ,p_init_msg_list => l_init_msg_list
3605 ,p_commit => l_commit
3606 ,p_validation_level => l_validation_level
3607 ,p_mks_rec => l_seg_rec
3608 ,x_return_status => x_return_status
3609 ,x_msg_count => x_msg_count
3610 ,x_msg_data => x_msg_data
3611 ,x_act_mks_id => x_act_mks_id
3612 );
3613
3614 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3615 RAISE fnd_api.g_exc_unexpected_error;
3616 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3617 RAISE fnd_api.g_exc_error;
3618 END IF;
3619 END LOOP;
3620 ELSE
3621 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3622 fnd_message.set_name('OZF', 'OZF_FUND_NO_CUSTOMER');
3623 fnd_msg_pub.add;
3624 END IF;
3625 RAISE fnd_api.g_exc_error;
3626 END IF;
3627 END IF;
3628
3629
3630 --//Update Mode Starts
3631 --//***************************************************************************************
3632 ELSE -- Update mode
3633 OZF_FUNDS_PVT.update_fund(p_api_version => p_api_version
3634 ,p_init_msg_list => p_init_msg_list
3635 ,p_commit => p_commit
3636 ,p_validation_level => p_validation_level
3637 ,x_return_status => x_return_status
3638 ,x_msg_count => x_msg_count
3639 ,x_msg_data => x_msg_data
3640 ,p_fund_rec => l_pvt_fund_rec
3641 ,p_mode => jtf_plsql_api.g_update
3642 );
3643
3644 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3645 RAISE fnd_api.g_exc_unexpected_error;
3646 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3647 RAISE fnd_api.g_exc_error;
3648 END IF;
3649
3650
3651
3652 IF l_fund_rec.fund_type = 'FIXED' THEN
3653 IF p_qualifier_tbl.COUNT > 0 THEN
3654 FOR i IN p_qualifier_tbl.FIRST..p_qualifier_tbl.LAST LOOP
3655
3656 ams_act_market_segments_pvt.Init_Mks_Rec(l_seg_rec);
3657
3658 l_seg_rec.market_segment_id := p_qualifier_tbl(i).qualifier_attr_value;
3659 l_seg_rec.act_market_segment_used_by_id := l_fund_rec.fund_id;
3660 l_seg_rec.arc_act_market_segment_used_by := 'FUND';
3661 l_seg_rec.segment_type := p_qualifier_tbl(i).qualifier_context;
3662
3663 --suppose only one market segment for this fund.
3664
3665 OPEN c_market_segment(l_seg_rec.act_market_segment_used_by_id);
3666 FETCH c_market_segment INTO l_seg_rec.activity_market_segment_id,l_seg_rec.object_version_number;
3667 CLOSE c_market_segment;
3668
3669 AMS_ACT_MARKET_SEGMENTS_PVT.update_market_segments(
3670 p_api_version => l_api_version
3671 ,p_init_msg_list => l_init_msg_list
3672 ,p_commit => l_commit
3673 ,p_validation_level => l_validation_level
3674 ,p_mks_rec => l_seg_rec
3675 ,x_return_status => x_return_status
3676 ,x_msg_count => x_msg_count
3677 ,x_msg_data => x_msg_data
3678 );
3679
3680 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3681 RAISE fnd_api.g_exc_unexpected_error;
3682 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3683 RAISE fnd_api.g_exc_error;
3684 END IF;
3685
3686 END LOOP;
3687 END IF;
3688
3689 ELSIF l_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
3690 OPEN c_list_header_id(l_fund_rec.fund_id);
3691 FETCH c_list_header_id INTO l_qp_list_header_id;
3692 CLOSE c_list_header_id;
3693
3694 l_modifier_list_rec.modifier_operation := 'UPDATE';
3695 l_modifier_list_rec.offer_operation := 'UPDATE';
3696 l_modifier_list_rec.qp_list_header_id := l_qp_list_header_id;
3697 l_modifier_list_rec.object_version_number := fnd_api.g_miss_num;
3698
3699
3700 IF p_modifier_line_tbl.COUNT > 0 THEN
3701 FOR i IN p_modifier_line_tbl.FIRST..p_modifier_line_tbl.LAST LOOP
3702
3703 l_modifier_line_tbl(i).offer_line_type := NVL(p_modifier_line_tbl(i).offer_line_type,Fnd_Api.g_miss_char);
3704 l_modifier_line_tbl(i).operation := NVL(p_modifier_line_tbl(i).operation,fnd_api.g_miss_char);
3705 l_modifier_line_tbl(i).list_line_id := NVL(p_modifier_line_tbl(i).list_line_id,Fnd_Api.g_miss_num);
3706 l_modifier_line_tbl(i).list_header_id := l_qp_list_header_id;
3707 l_modifier_line_tbl(i).list_line_type_code := NVL(p_modifier_line_tbl(i).list_line_type_code,'DIS');
3708 l_modifier_line_tbl(i).operand := NVL(p_modifier_line_tbl(i).operand,Fnd_Api.g_miss_num);
3709 l_modifier_line_tbl(i).start_date_active := NVL(p_modifier_line_tbl(i).start_date_active,Fnd_Api.g_miss_date) ;
3710 l_modifier_line_tbl(i).end_date_active := NVL(p_modifier_line_tbl(i).end_date_active,Fnd_Api.g_miss_date) ;
3711 l_modifier_line_tbl(i).arithmetic_operator := NVL(p_modifier_line_tbl(i).arithmetic_operator,Fnd_Api.g_miss_char);
3712 l_modifier_line_tbl(i).active_flag := NVL(p_modifier_line_tbl(i).active_flag,Fnd_Api.g_miss_char);
3713 l_modifier_line_tbl(i).qd_operand := NVL(p_modifier_line_tbl(i).qd_operand,Fnd_Api.g_miss_num);
3714 l_modifier_line_tbl(i).qd_arithmetic_operator := NVL(p_modifier_line_tbl(i).qd_arithmetic_operator,Fnd_Api.g_miss_char);
3715 l_modifier_line_tbl(i).qd_related_deal_lines_id := NVL(p_modifier_line_tbl(i).qd_related_deal_lines_id,Fnd_Api.g_miss_num);
3716 l_modifier_line_tbl(i).qd_object_version_number := NVL(p_modifier_line_tbl(i).qd_object_version_number,Fnd_Api.g_miss_num);
3717 l_modifier_line_tbl(i).qd_estimated_qty_is_max := NVL(p_modifier_line_tbl(i).qd_estimated_qty_is_max,Fnd_Api.g_miss_char);
3718 l_modifier_line_tbl(i).qd_list_line_id := NVL(p_modifier_line_tbl(i).qd_list_line_id,Fnd_Api.g_miss_num);
3719 l_modifier_line_tbl(i).qd_estimated_amount_is_max := NVL(p_modifier_line_tbl(i).qd_estimated_amount_is_max,Fnd_Api.g_miss_char);
3720 l_modifier_line_tbl(i).estim_gl_value := NVL(p_modifier_line_tbl(i).estim_gl_value,Fnd_Api.g_miss_num);
3721 l_modifier_line_tbl(i).benefit_price_list_line_id := NVL(p_modifier_line_tbl(i).benefit_price_list_line_id,Fnd_Api.g_miss_num);
3722 l_modifier_line_tbl(i).benefit_limit := NVL(p_modifier_line_tbl(i).benefit_limit,Fnd_Api.g_miss_num) ;
3723 l_modifier_line_tbl(i).benefit_qty := NVL(p_modifier_line_tbl(i).benefit_qty,Fnd_Api.g_miss_num) ;
3724 l_modifier_line_tbl(i).benefit_uom_code := NVL(p_modifier_line_tbl(i).benefit_uom_code,Fnd_Api.g_miss_char);
3725 l_modifier_line_tbl(i).substitution_context := NVL(p_modifier_line_tbl(i).substitution_context,Fnd_Api.g_miss_char);
3726 l_modifier_line_tbl(i).substitution_attr := NVL(p_modifier_line_tbl(i).substitution_attr,Fnd_Api.g_miss_char) ;
3727 l_modifier_line_tbl(i).substitution_val := NVL(p_modifier_line_tbl(i).substitution_val,Fnd_Api.g_miss_char);
3728 l_modifier_line_tbl(i).price_break_type_code := NVL(p_modifier_line_tbl(i).price_break_type_code,fnd_api.g_miss_char);
3729 l_modifier_line_tbl(i).pricing_attribute_id := NVL(p_modifier_line_tbl(i).pricing_attribute_id,Fnd_Api.g_miss_num);
3730 l_modifier_line_tbl(i).product_attribute_context := NVL(p_modifier_line_tbl(i).product_attribute_context,fnd_api.g_miss_char);
3731 l_modifier_line_tbl(i).product_attr := NVL(p_modifier_line_tbl(i).product_attr,'PRICING_ATTRIBUTE1');
3732 l_modifier_line_tbl(i).product_attr_val := NVL(p_modifier_line_tbl(i).product_attr_val,Fnd_Api.g_miss_char);
3733 l_modifier_line_tbl(i).product_uom_code := NVL(p_modifier_line_tbl(i).product_uom_code,Fnd_Api.g_miss_char);
3734 l_modifier_line_tbl(i).pricing_attribute_context := NVL(p_modifier_line_tbl(i).pricing_attribute_context,fnd_api.g_miss_char);
3735 l_modifier_line_tbl(i).pricing_attr := NVL(p_modifier_line_tbl(i).pricing_attr,'PRICING_ATTRIBUTE10');
3736 l_modifier_line_tbl(i).pricing_attr_value_from := NVL(p_modifier_line_tbl(i).pricing_attr_value_from,'1');
3737 l_modifier_line_tbl(i).pricing_attr_value_to := NVL(p_modifier_line_tbl(i).pricing_attr_value_to,Fnd_Api.g_miss_char) ;
3738 l_modifier_line_tbl(i).excluder_flag := NVL(p_modifier_line_tbl(i).excluder_flag,fnd_api.g_miss_char);
3739 l_modifier_line_tbl(i).order_value_from := NVL(p_modifier_line_tbl(i).order_value_from,Fnd_Api.g_miss_char);
3740 l_modifier_line_tbl(i).order_value_to := NVL(p_modifier_line_tbl(i).order_value_to,Fnd_Api.g_miss_char);
3741 l_modifier_line_tbl(i).qualifier_id := NVL(p_modifier_line_tbl(i).qualifier_id,Fnd_Api.g_miss_num);
3742 l_modifier_line_tbl(i).comments := NVL(p_modifier_line_tbl(i).comments,Fnd_Api.g_miss_char);
3743 l_modifier_line_tbl(i).context := NVL(p_modifier_line_tbl(i).context,Fnd_Api.g_miss_char) ;
3744 l_modifier_line_tbl(i).attribute1 := NVL(p_modifier_line_tbl(i).attribute1,Fnd_Api.g_miss_char);
3745 l_modifier_line_tbl(i).attribute2 := NVL(p_modifier_line_tbl(i).attribute2,Fnd_Api.g_miss_char);
3746 l_modifier_line_tbl(i).attribute3 := NVL(p_modifier_line_tbl(i).attribute3,Fnd_Api.g_miss_char);
3747 l_modifier_line_tbl(i).attribute4 := NVL(p_modifier_line_tbl(i).attribute4,Fnd_Api.g_miss_char);
3748 l_modifier_line_tbl(i).attribute5 := NVL(p_modifier_line_tbl(i).attribute5,Fnd_Api.g_miss_char);
3749 l_modifier_line_tbl(i).attribute6 := NVL(p_modifier_line_tbl(i).attribute6,Fnd_Api.g_miss_char);
3750 l_modifier_line_tbl(i).attribute7 := NVL(p_modifier_line_tbl(i).attribute7,Fnd_Api.g_miss_char);
3751 l_modifier_line_tbl(i).attribute8 := NVL(p_modifier_line_tbl(i).attribute8,Fnd_Api.g_miss_char);
3752 l_modifier_line_tbl(i).attribute9 := NVL(p_modifier_line_tbl(i).attribute9,Fnd_Api.g_miss_char);
3753 l_modifier_line_tbl(i).attribute10 := NVL(p_modifier_line_tbl(i).attribute10,Fnd_Api.g_miss_char);
3754 l_modifier_line_tbl(i).attribute11 := NVL(p_modifier_line_tbl(i).attribute11,Fnd_Api.g_miss_char);
3755 l_modifier_line_tbl(i).attribute12 := NVL(p_modifier_line_tbl(i).attribute12,Fnd_Api.g_miss_char);
3756 l_modifier_line_tbl(i).attribute13 := NVL(p_modifier_line_tbl(i).attribute13,Fnd_Api.g_miss_char);
3757 l_modifier_line_tbl(i).attribute14 := NVL(p_modifier_line_tbl(i).attribute14,Fnd_Api.g_miss_char);
3758 l_modifier_line_tbl(i).attribute15 := NVL(p_modifier_line_tbl(i).attribute15,Fnd_Api.g_miss_char);
3759 l_modifier_line_tbl(i).max_qty_per_order := NVL(p_modifier_line_tbl(i).max_qty_per_order,Fnd_Api.g_miss_num);
3760 l_modifier_line_tbl(i).max_qty_per_order_id := NVL(p_modifier_line_tbl(i).max_qty_per_order_id,Fnd_Api.g_miss_num);
3761 l_modifier_line_tbl(i).max_qty_per_customer := NVL(p_modifier_line_tbl(i).max_qty_per_customer,Fnd_Api.g_miss_num);
3762 l_modifier_line_tbl(i).max_qty_per_customer_id := NVL(p_modifier_line_tbl(i).max_qty_per_customer_id,Fnd_Api.g_miss_num);
3763 l_modifier_line_tbl(i).max_qty_per_rule := NVL(p_modifier_line_tbl(i).max_qty_per_rule,Fnd_Api.g_miss_num);
3764 l_modifier_line_tbl(i).max_qty_per_rule_id := NVL(p_modifier_line_tbl(i).max_qty_per_rule_id,Fnd_Api.g_miss_num);
3765 l_modifier_line_tbl(i).max_orders_per_customer := NVL(p_modifier_line_tbl(i).max_orders_per_customer,Fnd_Api.g_miss_num);
3766 l_modifier_line_tbl(i).max_orders_per_customer_id := NVL(p_modifier_line_tbl(i).max_orders_per_customer_id,Fnd_Api.g_miss_num);
3767 l_modifier_line_tbl(i).max_amount_per_rule := NVL(p_modifier_line_tbl(i).max_amount_per_rule,Fnd_Api.g_miss_num);
3768 l_modifier_line_tbl(i).max_amount_per_rule_id := NVL(p_modifier_line_tbl(i).max_amount_per_rule_id,Fnd_Api.g_miss_num);
3769 l_modifier_line_tbl(i).estimate_qty_uom := NVL(p_modifier_line_tbl(i).estimate_qty_uom,Fnd_Api.g_miss_char);
3770 l_modifier_line_tbl(i).generate_using_formula_id := NVL(p_modifier_line_tbl(i).generate_using_formula_id,Fnd_Api.g_miss_num);
3771 l_modifier_line_tbl(i).price_by_formula_id := NVL(p_modifier_line_tbl(i).price_by_formula_id,Fnd_Api.g_miss_num);
3772 l_modifier_line_tbl(i).generate_using_formula := NVL(p_modifier_line_tbl(i).generate_using_formula,Fnd_Api.g_miss_char);
3773 l_modifier_line_tbl(i).price_by_formula := NVL(p_modifier_line_tbl(i).price_by_formula,Fnd_Api.g_miss_char);
3774 l_modifier_line_tbl(i).list_line_no := NVL(p_modifier_line_tbl(i).list_line_no,Fnd_Api.g_miss_char);
3775
3776 IF G_DEBUG THEN
3777 ozf_utility_pvt.debug_message('p_modifier_line_tbl(i).LIST_LINE_ID11:' || p_modifier_line_tbl(i).LIST_LINE_ID);
3778 END IF;
3779 IF p_modifier_line_tbl(i).LIST_LINE_ID <> fnd_api.g_miss_num THEN
3780 l_modifier_line_tbl(i).OPERATION := 'UPDATE';
3781 l_modifier_line_tbl(i).LIST_LINE_ID := p_modifier_line_tbl(i).LIST_LINE_ID;
3782
3783 ELSE
3784 l_modifier_line_tbl(i).OPERATION := 'CREATE';
3785 l_modifier_line_tbl(i).LIST_LINE_ID := fnd_api.g_miss_num;
3786 --Fix for bug 13876928
3787 --l_modifier_line_tbl(i).list_line_no := p_modifier_line_tbl(i).list_line_no;
3788 l_modifier_line_tbl(i).list_line_no := NVL(p_modifier_line_tbl(i).list_line_no,Fnd_Api.g_miss_char);
3789
3790 END IF;
3791
3792 IF G_DEBUG THEN
3793 ozf_utility_pvt.debug_message('l_modifier_line_tbl(i).OPERATION:' || l_modifier_line_tbl(i).OPERATION);
3794 END IF;
3795
3796 --//XREF Implemantation
3797 IF l_modifier_line_tbl(i).OPERATION = 'UPDATE' THEN
3798 l_xref_line_id := NULL;
3799 OPEN c_line_id_chk(l_modifier_line_tbl(i).list_line_id);
3800 FETCH c_line_id_chk INTO l_xref_line_id;
3801 IF G_DEBUG THEN
3802 ozf_utility_pvt.debug_message('l_xref_line_id11:' || l_xref_line_id);
3803 END IF;
3804 IF l_xref_line_id IS NOT NULL THEN
3805 l_modifier_line_tbl(i).list_line_id := l_xref_line_id;
3806 END IF;
3807 CLOSE c_line_id_chk;
3808 END IF;
3809
3810 OPEN c_uom ( l_modifier_line_tbl(i).product_attr_val
3811 , fnd_profile.value('QP_ORGANIZATION_ID')
3812 );
3813 FETCH c_uom INTO l_uom;
3814 IF ( c_uom%NOTFOUND) THEN
3815 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3816 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
3817 FND_MSG_PUB.add;
3818 END IF;
3819 RAISE Fnd_Api.g_exc_error;
3820 END IF;
3821 CLOSE c_uom;
3822 l_modifier_line_tbl(i).PRODUCT_UOM_CODE := l_uom;
3823 END LOOP;
3824 END IF;
3825
3826 --//do not pass qualifiers in accrual budget update flow
3827 l_qualifier_tbl.DELETE;
3828
3829 --//populate l_list_line_tbl_type with existing product line records
3830 OPEN c_old_list_lines(l_qp_list_header_id);
3831 FETCH c_old_list_lines BULK COLLECT INTO l_list_line_tbl_type;
3832 CLOSE c_old_list_lines;
3833
3834 OZF_OFFER_PUB.process_modifiers(p_init_msg_list => p_init_msg_list
3835 ,p_api_version => p_api_version
3836 ,p_commit => p_commit
3837 ,x_return_status => x_return_status
3838 ,x_msg_count => x_msg_count
3839 ,x_msg_data => x_msg_data
3840 ,p_offer_type => 'ACCRUAL'
3841 ,p_modifier_list_rec => l_modifier_list_rec --offer header details
3842 ,p_modifier_line_tbl => l_modifier_line_tbl --discount rules
3843 ,p_qualifier_tbl => l_qualifier_tbl --market eligibilty
3844 ,p_budget_tbl => l_budget_tbl
3845 ,p_act_product_tbl => l_act_product_tbl
3846 ,p_discount_tbl => l_discount_tbl
3847 ,p_excl_tbl => l_excl_tbl
3848 ,p_offer_tier_tbl => l_offer_tier_tbl
3849 ,p_prod_tbl => l_prod_tbl
3850 ,p_na_qualifier_tbl => l_na_qualifier_tbl
3851 ,x_qp_list_header_id => l_qp_list_header_id
3852 ,x_error_location => l_error_location
3853 );
3854
3855 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3856 RAISE fnd_api.g_exc_unexpected_error;
3857 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3858 RAISE fnd_api.g_exc_error;
3859 END IF;
3860
3861 --//Fix for Bug 13654821
3862 FOR list_line_rec IN c_list_line(l_fund_rec.fund_id) LOOP
3863 IF G_DEBUG THEN
3864 ozf_utility_pvt.debug_message('l_list_line_tbl_type.COUNT:'|| l_list_line_tbl_type.COUNT);
3865 END IF;
3866
3867 l_insert_xref := 'Y';
3868
3869 IF (l_list_line_tbl_type.COUNT >0) THEN
3870 FOR j IN l_list_line_tbl_type.FIRST..l_list_line_tbl_type.LAST LOOP
3871
3872 IF (l_list_line_tbl_type(j).list_line_no = list_line_rec.list_line_no) THEN
3873 l_insert_xref := 'N';
3874 EXIT;
3875 END IF;
3876 END LOOP;
3877 END IF; --(l_list_line_tbl_type.COUNT >0)
3878
3879 IF l_insert_xref = 'Y' THEN
3880
3881 x_line_tbl(k).list_line_number := list_line_rec.list_line_no;
3882 x_line_tbl(k).list_line_id := TO_NUMBER(list_line_rec.list_line_id||'.'||'3');
3883
3884 INSERT INTO ozf_xref_map ( map_attr_id
3885 , list_line_id
3886 , list_line_number
3887 , xref_line_id_value
3888 , xref_line_number_value
3889 )
3890 VALUES ( l_fund_rec.fund_id
3891 , list_line_rec.list_line_id
3892 , list_line_rec.list_line_no
3893 , x_line_tbl(k).list_line_id
3894 , x_line_tbl(k).list_line_number
3895 );
3896
3897 ELSE
3898
3899 x_line_tbl(k).list_line_number := list_line_rec.list_line_no;
3900 x_line_tbl(k).list_line_id := list_line_rec.list_line_id;
3901
3902 END IF; --IF l_insert_xref = 'Y' THEN
3903
3904 k := k+1;
3905
3906 END LOOP; --FOR list_line_rec IN c_list_line(l_fund_rec.fund_id)
3907
3908 END IF;
3909 END IF;
3910
3911 FND_MSG_PUB.Count_And_Get (
3912 p_encoded => FND_API.G_FALSE,
3913 p_count => x_msg_count,
3914 p_data => x_msg_data
3915 );
3916
3917 x_msg_tbl(1).msg_text :='successful';
3918
3919 FOR i IN 1..x_msg_count LOOP
3920 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
3921 END LOOP;
3922
3923 EXCEPTION
3924 WHEN FND_API.G_EXC_ERROR THEN
3925 ROLLBACK TO process_fund_from_adapter;
3926 x_return_status := FND_API.G_RET_STS_ERROR;
3927 -- Standard call to get message count and if count=1, get the message
3928 FND_MSG_PUB.Count_And_Get (
3929 p_encoded => FND_API.G_FALSE,
3930 p_count => x_msg_count,
3931 p_data => x_msg_data
3932 );
3933
3934 for i in 1..x_msg_count loop
3935 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
3936 end loop;
3937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3938 ROLLBACK TO process_fund_from_adapter;
3939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3940 -- Standard call to get message count and if count=1, get the message
3941 FND_MSG_PUB.Count_And_Get (
3942 p_encoded => FND_API.G_FALSE,
3943 p_count => x_msg_count,
3944 p_data => x_msg_data
3945 );
3946 for i in 1..x_msg_count loop
3947 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
3948 end loop;
3949 WHEN OTHERS THEN
3950 ROLLBACK TO process_fund_from_adapter;
3951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3952 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3953 THEN
3954 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3955 END IF;
3956 -- Standard call to get message count and if count=1, get the message
3957 FND_MSG_PUB.Count_And_Get (
3958 p_encoded => FND_API.G_FALSE,
3959 p_count => x_msg_count,
3960 p_data => x_msg_data
3961 );
3962 for i in 1..x_msg_count loop
3963 x_msg_tbl(i).msg_text := substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'), 1, 254);
3964 end loop;
3965
3966 END process_fund_from_adapter;
3967
3968 END OZF_FUNDS_PUB;