1 PACKAGE BODY OZF_FUNDS_PUB AS
2 /* $Header: OZFPFUNB.pls 120.7.12010000.2 2008/08/28 07:15:47 kdass 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_fund_exists IS NULL THEN
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 ozf_funds_pvt.create_fund(p_api_version => p_api_version
905 ,p_init_msg_list => p_init_msg_list
906 ,p_commit => p_commit
907 ,p_validation_level => p_validation_level
908 ,x_return_status => x_return_status
909 ,x_msg_count => x_msg_count
910 ,x_msg_data => x_msg_data
911 ,p_fund_rec => l_pvt_fund_rec
912 ,x_fund_id => x_fund_id
913 );
914
915 IF G_DEBUG THEN
916 ozf_utility_pvt.debug_message('fund_type: ' || l_pvt_fund_rec.fund_type);
917 END IF;
918
919 IF l_pvt_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
920
921 OPEN c_list_header_id(x_fund_id);
922 FETCH c_list_header_id INTO l_qp_list_header_id;
923 CLOSE c_list_header_id;
924
925 l_modifier_list_rec.modifier_operation := 'UPDATE';
926 l_modifier_list_rec.offer_operation := 'UPDATE';
927 l_modifier_list_rec.qp_list_header_id := l_qp_list_header_id;
928
929 IF G_DEBUG THEN
930 ozf_utility_pvt.debug_message('accrual_phase: ' || l_pvt_fund_rec.accrual_phase);
931 END IF;
932
933 IF l_pvt_fund_rec.accrual_phase = 'ACCRUAL' THEN --accrual offer
934
935 ozf_offer_pub.process_modifiers(p_init_msg_list => p_init_msg_list
936 ,p_api_version => p_api_version
937 ,p_commit => p_commit
938 ,x_return_status => x_return_status
939 ,x_msg_count => x_msg_count
940 ,x_msg_data => x_msg_data
941 ,p_offer_type => 'ACCRUAL'
942 ,p_modifier_list_rec => l_modifier_list_rec --offer header details
943 ,p_modifier_line_tbl => p_modifier_line_tbl --discount rules
944 ,p_qualifier_tbl => p_qualifier_tbl --market eligibilty
945 ,p_budget_tbl => l_budget_tbl
946 ,p_act_product_tbl => l_act_product_tbl
947 ,p_discount_tbl => l_discount_tbl
948 ,p_excl_tbl => l_excl_tbl
949 ,p_offer_tier_tbl => l_offer_tier_tbl
950 ,p_prod_tbl => l_prod_tbl
951 ,p_na_qualifier_tbl => l_na_qualifier_tbl
952 ,x_qp_list_header_id => l_qp_list_header_id
953 ,x_error_location => l_error_location
954 );
955
956
957 ELSIF l_pvt_fund_rec.accrual_phase = 'VOLUME' THEN --volume offer
958
959 ozf_offer_pub.process_vo(p_init_msg_list => p_init_msg_list
960 ,p_api_version => p_api_version
961 ,p_commit => p_commit
962 ,x_return_status => x_return_status
963 ,x_msg_count => x_msg_count
964 ,x_msg_data => x_msg_data
965 ,p_modifier_list_rec => l_modifier_list_rec --offer header detail
966 ,p_vo_pbh_tbl => p_vo_pbh_tbl --discount table headers
967 ,p_vo_dis_tbl => p_vo_dis_tbl --discount table tiers
968 ,p_vo_prod_tbl => p_vo_prod_tbl --discount tabel products
969 ,p_qualifier_tbl => p_qualifier_tbl --market eligibilty
970 ,p_vo_mo_tbl => p_vo_mo_tbl --market options
971 ,p_budget_tbl => l_budget_tbl
972 ,x_qp_list_header_id => l_qp_list_header_id
973 ,x_error_location => l_error_location
974 );
975 END IF;
976
977 END IF;
978
979 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
980 RAISE fnd_api.g_exc_unexpected_error;
981 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
982 RAISE fnd_api.g_exc_error;
983 END IF;
984
985 FND_MSG_PUB.Count_And_Get (
986 p_encoded => FND_API.G_FALSE,
987 p_count => x_msg_count,
988 p_data => x_msg_data
989 );
990
991 EXCEPTION
992 WHEN FND_API.G_EXC_ERROR THEN
993 ROLLBACK TO Create_Fund_PUB;
994 x_return_status := FND_API.G_RET_STS_ERROR;
995 -- Standard call to get message count and if count=1, get the message
996 FND_MSG_PUB.Count_And_Get (
997 p_encoded => FND_API.G_FALSE,
998 p_count => x_msg_count,
999 p_data => x_msg_data
1000 );
1001 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1002 ROLLBACK TO Create_Fund_PUB;
1003 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1004 -- Standard call to get message count and if count=1, get the message
1005 FND_MSG_PUB.Count_And_Get (
1006 p_encoded => FND_API.G_FALSE,
1007 p_count => x_msg_count,
1008 p_data => x_msg_data
1009 );
1010 WHEN OTHERS THEN
1011 ROLLBACK TO Create_Fund_PUB;
1012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1013 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1014 THEN
1015 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1016 END IF;
1017 -- Standard call to get message count and if count=1, get the message
1018 FND_MSG_PUB.Count_And_Get (
1019 p_encoded => FND_API.G_FALSE,
1020 p_count => x_msg_count,
1021 p_data => x_msg_data
1022 );
1023 END Create_Fund;
1024 --------------------------------------------------------------------
1025 -- PROCEDURE
1026 -- Delete_Fund
1027 --
1028 -- PURPOSE
1029 -- Delete a fund.
1030 --
1031 -- PARAMETERS
1032 -- p_fund_id: the fund_id
1033 -- p_object_version: the object_version_number
1034 --
1035 -- NOTES
1036 -- 1. Raise exception if the object_version_number doesn't match.
1037 --------------------------------------------------------------------
1038
1039 PROCEDURE Delete_Fund(
1040 p_api_version IN NUMBER
1041 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1042 ,p_commit IN VARCHAR2 := FND_API.g_false
1043 ,x_return_status OUT NOCOPY VARCHAR2
1044 ,x_msg_count OUT NOCOPY NUMBER
1045 ,x_msg_data OUT NOCOPY VARCHAR2
1046 ,p_fund_id IN NUMBER
1047 ,p_object_version IN NUMBER
1048 )
1049
1050 IS
1051 l_dependent_object_tbl ams_utility_pvt.dependent_objects_tbl_type;
1052 l_return_status VARCHAR2(30);
1053 l_msg_count NUMBER;
1054 l_msg_data VARCHAR2(30);
1055 l_api_name VARCHAR(30) := 'Delete_Fund';
1056
1057 BEGIN
1058
1059 SAVEPOINT Delete_Fund_PUB;
1060
1061 OZF_Fund_Extension_Pvt.delete_fund(p_api_version_number => p_api_version
1062 ,p_init_msg_list => p_init_msg_list
1063 ,p_commit => p_commit
1064 ,p_object_id => p_fund_id
1065 ,p_object_version_number => p_object_version
1066 ,x_return_status => l_return_status
1067 ,x_msg_count => l_msg_count
1068 ,x_msg_data => l_msg_data
1069 );
1070
1071 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1072 RAISE fnd_api.g_exc_unexpected_error;
1073 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1074 RAISE fnd_api.g_exc_error;
1075 END IF;
1076
1077 FND_MSG_PUB.Count_And_Get(
1078 p_encoded => FND_API.G_FALSE,
1079 p_count => x_msg_count,
1080 p_data => x_msg_data
1081 );
1082
1083 EXCEPTION
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 ROLLBACK TO Delete_Fund_PUB;
1086 x_return_status := FND_API.G_RET_STS_ERROR;
1087 -- Standard call to get message count and if count=1, get the message
1088 FND_MSG_PUB.Count_And_Get (
1089 p_encoded => FND_API.G_FALSE,
1090 p_count => x_msg_count,
1091 p_data => x_msg_data
1092 );
1093 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1094 ROLLBACK TO Delete_Fund_PUB;
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1096 -- Standard call to get message count and if count=1, get the message
1097 FND_MSG_PUB.Count_And_Get (
1098 p_encoded => FND_API.G_FALSE,
1099 p_count => x_msg_count,
1100 p_data => x_msg_data
1101 );
1102 WHEN OTHERS THEN
1103 ROLLBACK TO Delete_Fund_PUB;
1104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1105 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1106 THEN
1107 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1108 END IF;
1109 -- Standard call to get message count and if count=1, get the message
1110 FND_MSG_PUB.Count_And_Get (
1111 p_encoded => FND_API.G_FALSE,
1112 p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1115
1116 END Delete_Fund;
1117
1118
1119 ---------------------------------------------------------------------
1120 -- PROCEDURE
1121 -- Update_Fund
1122 --
1123 -- PURPOSE
1124 -- Update a fund.
1125 --
1126 -- PARAMETERS
1127 -- p_fund_rec: the record with new items.
1128 -- p_mode : determines what sort of validation is to be performed during update.
1129 -- : The mode should always be 'UPDATE' except when updating the earned or committed amount
1130 --
1131 -- NOTES
1132 -- 1. Raise exception if the object_version_number doesn't match.
1133 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
1134 -- that column won't be updated.
1135 ----------------------------------------------------------------------
1136 PROCEDURE Update_fund(
1137 p_api_version IN NUMBER
1138 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1139 ,p_commit IN VARCHAR2 := fnd_api.g_false
1140 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1141 ,x_return_status OUT NOCOPY VARCHAR2
1142 ,x_msg_count OUT NOCOPY NUMBER
1143 ,x_msg_data OUT NOCOPY VARCHAR2
1144 ,p_fund_rec IN fund_rec_type
1145 ,p_modifier_list_rec IN ozf_offer_pub.modifier_list_rec_type
1146 ,p_modifier_line_tbl IN ozf_offer_pub.modifier_line_tbl_type
1147 ,p_vo_pbh_tbl IN ozf_offer_pub.vo_disc_tbl_type
1148 ,p_vo_dis_tbl IN ozf_offer_pub.vo_disc_tbl_type
1149 ,p_vo_prod_tbl IN ozf_offer_pub.vo_prod_tbl_type
1150 ,p_qualifier_tbl IN ozf_offer_pub.qualifiers_tbl_type
1151 ,p_vo_mo_tbl IN ozf_offer_pub.vo_mo_tbl_type
1152 )
1153 IS
1154 l_api_name VARCHAR(30) := 'Update_Fund';
1155 l_fund_rec fund_rec_type := p_fund_rec;
1156 l_pvt_fund_rec OZF_Funds_PVT.fund_rec_type;
1157 l_fund_id NUMBER := l_fund_rec.fund_id;
1158 l_fund_number VARCHAR2(200);
1159 l_short_name VARCHAR2(200);
1160 l_fund_type VARCHAR2(20);
1161 l_custom_setup_id NUMBER;
1162 l_description VARCHAR2(2000);
1163 l_parent_fund_id NUMBER;
1164 l_category_id NUMBER;
1165 l_business_unit_id NUMBER;
1166 l_status_code VARCHAR2(50);
1167 l_start_date_active DATE;
1168 l_end_date_active DATE;
1169 l_start_period_name VARCHAR2(20);
1170 l_end_period_name VARCHAR2(20);
1171 l_original_budget NUMBER;
1172 l_holdback_amt NUMBER;
1173 l_currency_code_tc VARCHAR2(10);
1174 l_owner NUMBER;
1175 l_accrual_basis VARCHAR2(10);
1176 l_accrual_phase VARCHAR2(10);
1177 l_accrual_discount_level VARCHAR2(10);
1178 l_threshold_id NUMBER;
1179 l_task_id NUMBER;
1180 l_liability_flag VARCHAR2(1);
1181 l_accrued_liable_account NUMBER;
1182 l_ded_adjustment_account NUMBER;
1183 l_product_spread_time_id NUMBER;
1184 l_object_version_number NUMBER;
1185 l_org_id NUMBER;
1186 l_ledger_id NUMBER;
1187 l_mode VARCHAR2(6) := 'UPDATE';
1188
1189 CURSOR c_fund_id (p_fund_number IN VARCHAR2) IS
1190 SELECT fund_id
1191 FROM ozf_funds_all_b
1192 WHERE fund_number = p_fund_number;
1193
1194 CURSOR c_fund_details (p_fund_id IN NUMBER) IS
1195 SELECT fund_number, short_name, fund_type, custom_setup_id, description, parent_fund_id, category_id,
1196 business_unit_id, status_code, start_date_active, end_date_active, start_period_name,
1197 end_period_name, original_budget, holdback_amt, currency_code_tc, owner, accrual_basis,
1198 accrual_phase, accrual_discount_level, threshold_id, task_id, liability_flag,
1199 accrued_liable_account, ded_adjustment_account, product_spread_time_id, object_version_number,
1200 org_id, ledger_id
1201 FROM ozf_funds_all_vl
1202 WHERE fund_id = p_fund_id;
1203
1204 BEGIN
1205
1206 SAVEPOINT Update_Fund_PUB;
1207
1208 IF l_fund_rec.fund_id IS NULL AND l_fund_rec.fund_number IS NOT NULL THEN
1209 OPEN c_fund_id (l_fund_rec.fund_number);
1210 FETCH c_fund_id INTO l_fund_id;
1211 CLOSE c_fund_id;
1212 END IF;
1213
1214 IF G_DEBUG THEN
1215 ozf_utility_pvt.debug_message(l_api_name || ': l_fund_id :' || l_fund_id);
1216 END IF;
1217
1218 IF l_fund_id IS NOT NULL THEN
1219 OPEN c_fund_details (l_fund_id);
1220 FETCH c_fund_details INTO l_fund_number, l_short_name, l_fund_type, l_custom_setup_id, l_description,
1221 l_parent_fund_id, l_category_id, l_business_unit_id,
1222 l_status_code, l_start_date_active, l_end_date_active,
1223 l_start_period_name, l_end_period_name, l_original_budget, l_holdback_amt,
1224 l_currency_code_tc, l_owner, l_accrual_basis, l_accrual_phase, l_accrual_discount_level,
1225 l_threshold_id, l_task_id, l_liability_flag, l_accrued_liable_account,
1226 l_ded_adjustment_account, l_product_spread_time_id, l_object_version_number,
1227 l_org_id, l_ledger_id;
1228 CLOSE c_fund_details;
1229 END IF;
1230
1231 l_fund_rec.fund_number := NVL(l_fund_rec.fund_number,l_fund_number);
1232 l_fund_rec.short_name := NVL(l_fund_rec.short_name,l_short_name);
1233 l_fund_rec.fund_type := l_fund_type;
1234 l_fund_rec.custom_setup_id := l_custom_setup_id;
1235 l_fund_rec.description := NVL(l_fund_rec.description,l_description);
1236 l_fund_rec.parent_fund_id := NVL(l_fund_rec.parent_fund_id,l_parent_fund_id);
1237 l_fund_rec.category_id := NVL(l_fund_rec.category_id,l_category_id);
1238 l_fund_rec.business_unit_id := NVL(l_fund_rec.business_unit_id,l_description);
1239 l_fund_rec.status_code := NVL(l_fund_rec.status_code,l_status_code);
1240 l_fund_rec.start_date_active := NVL(l_fund_rec.start_date_active,l_start_date_active);
1241 l_fund_rec.end_date_active := NVL(l_fund_rec.end_date_active,l_end_date_active);
1242 l_fund_rec.start_period_name := NVL(l_fund_rec.start_period_name,l_start_period_name);
1243 l_fund_rec.end_period_name := NVL(l_fund_rec.end_period_name,l_end_period_name);
1244 l_fund_rec.original_budget := NVL(l_fund_rec.original_budget,l_original_budget);
1245 l_fund_rec.holdback_amt := NVL(l_fund_rec.holdback_amt,l_holdback_amt);
1246 l_fund_rec.currency_code_tc := l_currency_code_tc;
1247 l_fund_rec.owner := NVL(l_fund_rec.owner,l_owner);
1248 l_fund_rec.accrual_basis := l_accrual_basis;
1249 l_fund_rec.accrual_phase := l_accrual_phase;
1250 l_fund_rec.accrual_discount_level := l_accrual_discount_level;
1251 l_fund_rec.threshold_id := NVL(l_fund_rec.threshold_id,l_threshold_id);
1252 l_fund_rec.task_id := NVL(l_fund_rec.task_id,l_task_id);
1253 l_fund_rec.liability_flag := NVL(l_fund_rec.liability_flag,l_liability_flag);
1254 l_fund_rec.accrued_liable_account := NVL(l_fund_rec.accrued_liable_account,l_accrued_liable_account);
1255 l_fund_rec.ded_adjustment_account := NVL(l_fund_rec.ded_adjustment_account,l_ded_adjustment_account);
1256 l_fund_rec.product_spread_time_id := NVL(l_fund_rec.product_spread_time_id,l_product_spread_time_id);
1257 l_fund_rec.object_version_number := l_object_version_number;
1258 l_fund_rec.org_id := l_org_id;
1259 l_fund_rec.ledger_id := NVL(l_fund_rec.ledger_id,l_ledger_id);
1260
1261 validate_fund_items(p_fund_rec => l_fund_rec
1262 ,p_mode => l_mode
1263 ,x_return_status => x_return_status);
1264
1265 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1266 RAISE fnd_api.g_exc_unexpected_error;
1267 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1268 RAISE fnd_api.g_exc_error;
1269 END IF;
1270
1271 l_pvt_fund_rec.fund_id := l_fund_rec.fund_id;
1272 l_pvt_fund_rec.fund_number := l_fund_rec.fund_number;
1273 l_pvt_fund_rec.short_name := l_fund_rec.short_name;
1274 l_pvt_fund_rec.fund_type := l_fund_rec.fund_type;
1275 l_pvt_fund_rec.custom_setup_id := l_fund_rec.custom_setup_id;
1276 l_pvt_fund_rec.description := l_fund_rec.description;
1277 l_pvt_fund_rec.parent_fund_id := l_fund_rec.parent_fund_id;
1278 l_pvt_fund_rec.category_id := l_fund_rec.category_id;
1279 l_pvt_fund_rec.business_unit_id := l_fund_rec.business_unit_id;
1280 l_pvt_fund_rec.status_code := l_fund_rec.status_code;
1281 l_pvt_fund_rec.user_status_id := l_fund_rec.user_status_id;
1282 l_pvt_fund_rec.start_date_active := l_fund_rec.start_date_active;
1283 l_pvt_fund_rec.end_date_active := l_fund_rec.end_date_active;
1284 l_pvt_fund_rec.start_period_name := l_fund_rec.start_period_name;
1285 l_pvt_fund_rec.end_period_name := l_fund_rec.end_period_name;
1286 l_pvt_fund_rec.original_budget := l_fund_rec.original_budget;
1287 l_pvt_fund_rec.holdback_amt := l_fund_rec.holdback_amt;
1288 l_pvt_fund_rec.currency_code_tc := l_fund_rec.currency_code_tc;
1289 l_pvt_fund_rec.owner := l_fund_rec.owner;
1290 l_pvt_fund_rec.accrual_basis := l_fund_rec.accrual_basis;
1291 l_pvt_fund_rec.accrual_phase := l_fund_rec.accrual_phase;
1292 l_pvt_fund_rec.accrual_discount_level := l_fund_rec.accrual_discount_level;
1293 l_pvt_fund_rec.threshold_id := l_fund_rec.threshold_id;
1294 l_pvt_fund_rec.task_id := l_fund_rec.task_id;
1295 l_pvt_fund_rec.liability_flag := l_fund_rec.liability_flag;
1296 l_pvt_fund_rec.accrued_liable_account := l_fund_rec.accrued_liable_account;
1297 l_pvt_fund_rec.ded_adjustment_account := l_fund_rec.ded_adjustment_account;
1298 l_pvt_fund_rec.product_spread_time_id := l_fund_rec.product_spread_time_id;
1299 l_pvt_fund_rec.object_version_number := l_fund_rec.object_version_number;
1300 l_pvt_fund_rec.org_id := l_fund_rec.org_id;
1301 l_pvt_fund_rec.ledger_id := l_fund_rec.ledger_id;
1302
1303 ozf_funds_pvt.update_fund(p_api_version => p_api_version
1304 ,p_init_msg_list => p_init_msg_list
1305 ,p_commit => p_commit
1306 ,p_validation_level => p_validation_level
1307 ,x_return_status => x_return_status
1308 ,x_msg_count => x_msg_count
1309 ,x_msg_data => x_msg_data
1310 ,p_fund_rec => l_pvt_fund_rec
1311 ,p_mode => jtf_plsql_api.g_update
1312 );
1313
1314 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1315 RAISE fnd_api.g_exc_unexpected_error;
1316 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1317 RAISE fnd_api.g_exc_error;
1318 END IF;
1319
1320 FND_MSG_PUB.Count_And_Get (
1321 p_encoded => FND_API.G_FALSE,
1322 p_count => x_msg_count,
1323 p_data => x_msg_data
1324 );
1325
1326 EXCEPTION
1327 WHEN FND_API.G_EXC_ERROR THEN
1328 ROLLBACK TO Update_Fund_PUB;
1329 x_return_status := FND_API.G_RET_STS_ERROR;
1330 -- Standard call to get message count and if count=1, get the message
1331 FND_MSG_PUB.Count_And_Get (
1332 p_encoded => FND_API.G_FALSE,
1333 p_count => x_msg_count,
1334 p_data => x_msg_data
1335 );
1336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1337 ROLLBACK TO Update_Fund_PUB;
1338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1339 -- Standard call to get message count and if count=1, get the message
1340 FND_MSG_PUB.Count_And_Get (
1341 p_encoded => FND_API.G_FALSE,
1342 p_count => x_msg_count,
1343 p_data => x_msg_data
1344 );
1345 WHEN OTHERS THEN
1346 ROLLBACK TO Update_Fund_PUB;
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1349 THEN
1350 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1351 END IF;
1352 -- Standard call to get message count and if count=1, get the message
1353 FND_MSG_PUB.Count_And_Get (
1354 p_encoded => FND_API.G_FALSE,
1355 p_count => x_msg_count,
1356 p_data => x_msg_data
1357 );
1358 END Update_Fund;
1359
1360 ---------------------------------------------------------------------
1361 -- PROCEDURE
1362 -- validate_market_segment
1363 --
1364 -- PURPOSE
1365 -- Validate market segment
1366 --
1367 -- PARAMETERS
1368 -- p_mks_rec: market segment record to be validated
1369 -- x_return_status: return status
1370 --
1371 -- HISTORY
1372 -- 06/29/2005 kdass Created
1373 ---------------------------------------------------------------------
1374 PROCEDURE validate_market_segment (
1375 p_mks_rec IN OUT NOCOPY mks_rec_type
1376 ,p_mode IN VARCHAR2
1377 ,x_return_status OUT NOCOPY VARCHAR2
1378 )
1379 IS
1380 l_api_name VARCHAR(30) := 'validate_market_segment';
1381 l_act_mkt_exists NUMBER := NULL;
1382 l_segment_used_by_exists NUMBER := NULL;
1383 l_segment_exists NUMBER := NULL;
1384 l_segment_id_exists NUMBER := NULL;
1385
1386 CURSOR c_act_mkt_exists (p_activity_market_segment_id IN NUMBER) IS
1387 SELECT 1
1388 FROM ams_act_market_segments
1389 WHERE activity_market_segment_id = p_activity_market_segment_id;
1390
1391 CURSOR c_segment_used_by_exists (p_segment_used_by_id IN NUMBER) IS
1392 SELECT 1
1393 FROM ozf_funds_all_b
1394 WHERE fund_id = p_segment_used_by_id;
1395
1396 CURSOR c_segment_exists (p_segment IN VARCHAR2) IS
1397 SELECT 1
1398 FROM ozf_lookups
1399 WHERE lookup_type = 'OZF_OFFER_DEAL_CUSTOMER_TYPES'
1400 AND enabled_flag = 'Y'
1401 AND lookup_code = p_segment;
1402
1403 CURSOR c_segment_buyer (p_segment_id IN NUMBER) IS
1404 SELECT 1
1405 FROM ams_party_market_segments ams, hz_parties hz
1406 WHERE ams.market_qualifier_type = 'BG'
1407 AND ams.market_qualifier_reference = hz.party_id
1408 AND ams.market_qualifier_reference = ams.party_id
1409 AND EXISTS
1410 ( SELECT 1
1411 FROM ams_party_market_segments
1412 WHERE market_qualifier_type = 'BG'
1413 AND market_qualifier_reference = ams.market_qualifier_reference
1414 AND market_qualifier_reference <> party_id)
1415 AND hz.party_id = p_segment_id;
1416
1417 CURSOR c_segment_cust (p_segment_id IN NUMBER) IS
1418 SELECT 1
1419 FROM qp_customers_v
1420 WHERE customer_id = p_segment_id;
1421
1422 CURSOR c_segment_billto (p_segment_id IN NUMBER) IS
1423 SELECT 1
1424 FROM oe_invoice_to_orgs_v oito,hz_cust_accounts cust_acct,hz_parties party
1425 WHERE cust_acct.party_id = party.party_id
1426 AND oito.customer_id = cust_acct.cust_account_id
1427 AND oito.organization_id = p_segment_id;
1428
1429 CURSOR c_segment_list (p_segment_id IN NUMBER) IS
1430 SELECT 1
1431 FROM ams_list_headers_all list, ams_list_headers_all_tl tl
1432 WHERE list.list_header_id = tl.list_header_id
1433 AND userenv('LANG') = language
1434 AND status_code in ( 'AVAILABLE','LOCKED','EXECUTED','EXECUTING','VALIDATED','VALIDATING')
1435 AND list.list_header_id = p_segment_id;
1436
1437 CURSOR c_segment_seg (p_segment_id IN NUMBER) IS
1438 SELECT 1
1439 FROM ams_cells_all_b cell, ams_cells_all_tl tl
1440 WHERE cell.cell_id = tl.cell_id
1441 AND userenv('LANG') = language
1442 AND cell.status_code = 'AVAILABLE'
1443 AND cell.cell_id = p_segment_id;
1444
1445 CURSOR c_segment_shipto (p_segment_id IN NUMBER) IS
1446 SELECT 1
1447 FROM qp_ship_to_orgs_v
1448 WHERE organization_id = p_segment_id;
1449
1450 CURSOR c_segment_terr (p_segment_id IN NUMBER) IS
1451 SELECT 1
1452 FROM jtf_terr_qtype_usgs jtqu, jtf_terr jt, jtf_qual_type_usgs jqtu
1453 WHERE ( TRUNC(jt.start_date_active) <= TRUNC(SYSDATE)
1454 AND ( TRUNC(jt.end_date_active) >= TRUNC(SYSDATE) OR jt.end_date_active IS NULL ))
1455 AND jt.terr_id = jtqu.terr_id
1456 AND jtqu.qual_type_usg_id = jqtu.qual_type_usg_id
1457 AND jqtu.source_id = -1003
1458 AND jqtu.qual_type_id = -1007
1459 AND jt.terr_id = p_segment_id;
1460
1461 BEGIN
1462
1463 IF p_mode = 'CREATE' THEN
1464 p_mks_rec.activity_market_segment_id := NULL;
1465 ELSE
1466 --if activity market segment id is null, then raise exception
1467 IF (p_mks_rec.activity_market_segment_id = fnd_api.g_miss_num OR p_mks_rec.activity_market_segment_id IS NULL) THEN
1468
1469 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1470 fnd_message.set_name('OZF', 'OZF_NO_ACT_SEG_ID');
1471 fnd_msg_pub.add;
1472 END IF;
1473 x_return_status := fnd_api.g_ret_sts_error;
1474 RETURN;
1475 ELSE
1476
1477 --check if the input activity_market_segment_id is valid
1478 OPEN c_act_mkt_exists (p_mks_rec.activity_market_segment_id);
1479 FETCH c_act_mkt_exists INTO l_act_mkt_exists;
1480 CLOSE c_act_mkt_exists;
1481
1482 IF l_act_mkt_exists IS NULL THEN
1483 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1484 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_SEG_ID');
1485 fnd_msg_pub.add;
1486 END IF;
1487 x_return_status := fnd_api.g_ret_sts_error;
1488 RETURN;
1489 END IF;
1490 END IF;
1491 END IF;
1492
1493 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
1494 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1495 fnd_message.set_name('OZF', 'OZF_NO_MKT_SEG_USED_BY_ID');
1496 fnd_msg_pub.add;
1497 END IF;
1498 x_return_status := fnd_api.g_ret_sts_error;
1499 RETURN;
1500 ELSE
1501
1502 OPEN c_segment_used_by_exists (p_mks_rec.act_market_segment_used_by_id);
1503 FETCH c_segment_used_by_exists INTO l_segment_used_by_exists;
1504 CLOSE c_segment_used_by_exists;
1505
1506 IF l_segment_used_by_exists IS NULL THEN
1507 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1508 fnd_message.set_name('OZF', 'OZF_INVALID_MKT_SEG_USED_BY_ID');
1509 fnd_msg_pub.add;
1510 END IF;
1511 x_return_status := fnd_api.g_ret_sts_error;
1512 RETURN;
1513 END IF;
1514 END IF;
1515
1516 p_mks_rec.arc_act_market_segment_used_by := 'FUND';
1517
1518 IF p_mks_rec.segment_type = fnd_api.g_miss_char OR p_mks_rec.segment_type IS NULL THEN
1519 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1520 fnd_message.set_name('OZF', 'OZF_NO_SEGMENT_TYPE');
1521 fnd_msg_pub.add;
1522 END IF;
1523 x_return_status := fnd_api.g_ret_sts_error;
1524 RETURN;
1525 ELSE
1526
1527 OPEN c_segment_exists (p_mks_rec.segment_type);
1528 FETCH c_segment_exists INTO l_segment_exists;
1529 CLOSE c_segment_exists;
1530
1531 IF l_segment_exists IS NULL THEN
1532 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1533 fnd_message.set_name('OZF', 'OZF_INVALID_SEGMENT_TYPE');
1534 fnd_msg_pub.add;
1535 END IF;
1536 x_return_status := fnd_api.g_ret_sts_error;
1537 RETURN;
1538 END IF;
1539 END IF;
1540
1541 IF p_mks_rec.market_segment_id = fnd_api.g_miss_num OR p_mks_rec.market_segment_id IS NULL THEN
1542 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1543 fnd_message.set_name('OZF', 'OZF_NO_MKT_SEG_ID');
1544 fnd_msg_pub.add;
1545 END IF;
1546 x_return_status := fnd_api.g_ret_sts_error;
1547 RETURN;
1548 ELSE
1549
1550 IF p_mks_rec.segment_type = 'BUYER' THEN --Buying Group
1551 OPEN c_segment_buyer (p_mks_rec.market_segment_id);
1552 FETCH c_segment_buyer INTO l_segment_id_exists;
1553 CLOSE c_segment_buyer;
1554 ELSIF p_mks_rec.segment_type = 'CUSTOMER' THEN --Customer Name
1555 OPEN c_segment_cust (p_mks_rec.market_segment_id);
1556 FETCH c_segment_cust INTO l_segment_id_exists;
1557 CLOSE c_segment_cust;
1558 ELSIF p_mks_rec.segment_type = 'CUSTOMER_BILL_TO' THEN --Customer - Bill TO
1559 OPEN c_segment_billto (p_mks_rec.market_segment_id);
1560 FETCH c_segment_billto INTO l_segment_id_exists;
1561 CLOSE c_segment_billto;
1562 ELSIF p_mks_rec.segment_type = 'LIST' THEN --List
1563 OPEN c_segment_list (p_mks_rec.market_segment_id);
1564 FETCH c_segment_list INTO l_segment_id_exists;
1565 CLOSE c_segment_list;
1566 ELSIF p_mks_rec.segment_type = 'SEGMENT' THEN --Segment
1567 OPEN c_segment_seg (p_mks_rec.market_segment_id);
1568 FETCH c_segment_seg INTO l_segment_id_exists;
1569 CLOSE c_segment_seg;
1570 ELSIF p_mks_rec.segment_type = 'SHIP_TO' THEN --Customer - Ship TO
1571 OPEN c_segment_shipto (p_mks_rec.market_segment_id);
1572 FETCH c_segment_shipto INTO l_segment_id_exists;
1573 CLOSE c_segment_shipto;
1574 ELSIF p_mks_rec.segment_type = 'TERRITORY' THEN --Territories
1575 OPEN c_segment_terr (p_mks_rec.market_segment_id);
1576 FETCH c_segment_terr INTO l_segment_id_exists;
1577 CLOSE c_segment_terr;
1578 END IF;
1579
1580 IF l_segment_id_exists IS NULL THEN
1581 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1582 fnd_message.set_name('OZF', 'OZF_INVALID_MKT_SEG_ID');
1583 fnd_msg_pub.add;
1584 END IF;
1585 x_return_status := fnd_api.g_ret_sts_error;
1586 RETURN;
1587 END IF;
1588
1589 END IF;
1590
1591 p_mks_rec.exclude_flag := NVL(p_mks_rec.exclude_flag, 'N');
1592
1593 IF p_mks_rec.exclude_flag NOT IN ('Y', 'N') THEN
1594 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1595 fnd_message.set_name('OZF', 'OZF_INVALID_EXCLUDE_FLAG');
1596 fnd_msg_pub.add;
1597 END IF;
1598 x_return_status := fnd_api.g_ret_sts_error;
1599 RETURN;
1600 END IF;
1601
1602 END validate_market_segment;
1603
1604 ---------------------------------------------------------------------
1605 -- PROCEDURE
1606 -- create_market_segment
1607 --
1608 -- PURPOSE
1609 -- Creates a market segment for fund.
1610 --
1611 -- PARAMETERS
1612 -- p_mks_rec : the record with new items.
1613 -- x_act_mks_id : return the market segment id for the fund
1614 --
1615 -- HISTORY
1616 -- 07/07/2005 kdass Created
1617 ----------------------------------------------------------------------
1618 PROCEDURE create_market_segment(
1619 p_api_version IN NUMBER
1620 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1621 ,p_commit IN VARCHAR2 := fnd_api.g_false
1622 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1623 ,p_mks_rec IN mks_rec_type
1624 ,x_return_status OUT NOCOPY VARCHAR2
1625 ,x_msg_count OUT NOCOPY NUMBER
1626 ,x_msg_data OUT NOCOPY VARCHAR2
1627 ,x_act_mks_id OUT NOCOPY NUMBER)
1628 IS
1629 l_api_name VARCHAR(30) := 'create_market_segment';
1630 l_mode VARCHAR2(6) := 'CREATE';
1631 l_mks_rec mks_rec_type := p_mks_rec;
1632 l_seg_rec ams_act_market_segments_pvt.mks_rec_type;
1633 l_api_version NUMBER := p_api_version;
1634 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
1635 l_validation_level NUMBER := p_validation_level;
1636 l_commit VARCHAR2(1) := p_commit;
1637
1638 BEGIN
1639
1640 SAVEPOINT create_market_pub;
1641
1642 validate_market_segment(p_mks_rec => l_mks_rec
1643 ,p_mode => l_mode
1644 ,x_return_status => x_return_status);
1645
1646 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1647 RAISE fnd_api.g_exc_unexpected_error;
1648 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1649 RAISE fnd_api.g_exc_error;
1650 END IF;
1651
1652 l_seg_rec.market_segment_id := l_mks_rec.market_segment_id;
1653 l_seg_rec.act_market_segment_used_by_id := l_mks_rec.act_market_segment_used_by_id;
1654 l_seg_rec.arc_act_market_segment_used_by := l_mks_rec.arc_act_market_segment_used_by;
1655 l_seg_rec.segment_type := l_mks_rec.segment_type;
1656 l_seg_rec.object_version_number := l_mks_rec.object_version_number;
1657 l_seg_rec.exclude_flag := l_mks_rec.exclude_flag;
1658
1659 ams_act_market_segments_pvt.create_market_segments(p_api_version => l_api_version
1660 ,p_init_msg_list => l_init_msg_list
1661 ,p_commit => l_commit
1662 ,p_validation_level => l_validation_level
1663 ,p_mks_rec => l_seg_rec
1664 ,x_return_status => x_return_status
1665 ,x_msg_count => x_msg_count
1666 ,x_msg_data => x_msg_data
1667 ,x_act_mks_id => x_act_mks_id
1668 );
1669
1670 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1671 RAISE fnd_api.g_exc_unexpected_error;
1672 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1673 RAISE fnd_api.g_exc_error;
1674 END IF;
1675
1676 FND_MSG_PUB.Count_And_Get (
1677 p_encoded => FND_API.G_FALSE,
1678 p_count => x_msg_count,
1679 p_data => x_msg_data
1680 );
1681
1682 EXCEPTION
1683 WHEN FND_API.G_EXC_ERROR THEN
1684 ROLLBACK TO create_market_pub;
1685 x_return_status := FND_API.G_RET_STS_ERROR;
1686 -- Standard call to get message count and if count=1, get the message
1687 FND_MSG_PUB.Count_And_Get (
1688 p_encoded => FND_API.G_FALSE,
1689 p_count => x_msg_count,
1690 p_data => x_msg_data
1691 );
1692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1693 ROLLBACK TO create_market_pub;
1694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1695 -- Standard call to get message count and if count=1, get the message
1696 FND_MSG_PUB.Count_And_Get (
1697 p_encoded => FND_API.G_FALSE,
1698 p_count => x_msg_count,
1699 p_data => x_msg_data
1700 );
1701 WHEN OTHERS THEN
1702 ROLLBACK TO create_market_pub;
1703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1704 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1705 THEN
1706 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1707 END IF;
1708 -- Standard call to get message count and if count=1, get the message
1709 FND_MSG_PUB.Count_And_Get (
1710 p_encoded => FND_API.G_FALSE,
1711 p_count => x_msg_count,
1712 p_data => x_msg_data
1713 );
1714 END create_market_segment;
1715
1716 ---------------------------------------------------------------------
1717 -- PROCEDURE
1718 -- update_market_segment
1719 --
1720 -- PURPOSE
1721 -- Updates a market segment for fund.
1722 --
1723 -- PARAMETERS
1724 -- p_mks_rec : the record with items to be updated.
1725 --
1726 -- HISTORY
1727 -- 07/07/2005 kdass Created
1728 ----------------------------------------------------------------------
1729 PROCEDURE update_market_segment(
1730 p_api_version IN NUMBER
1731 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1732 ,p_commit IN VARCHAR2 := fnd_api.g_false
1733 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1734 ,p_mks_rec IN mks_rec_type
1735 ,x_return_status OUT NOCOPY VARCHAR2
1736 ,x_msg_count OUT NOCOPY NUMBER
1737 ,x_msg_data OUT NOCOPY VARCHAR2)
1738 IS
1739 l_api_name VARCHAR(30) := 'update_market_segment';
1740 l_mode VARCHAR2(6) := 'UPDATE';
1741 l_mks_rec mks_rec_type := p_mks_rec;
1742 l_seg_rec ams_act_market_segments_pvt.mks_rec_type;
1743 l_api_version NUMBER := p_api_version;
1744 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
1745 l_validation_level NUMBER := p_validation_level;
1746 l_commit VARCHAR2(1) := p_commit;
1747 l_mkt_seg_used_by_id NUMBER;
1748 l_segment_type VARCHAR2(30);
1749 l_mkt_seg_id NUMBER;
1750 l_object_version NUMBER;
1751 l_exclude_flag VARCHAR2(1);
1752
1753 CURSOR c_mkt_seg_details (p_act_mkt_seg_id IN NUMBER) IS
1754 SELECT act_market_segment_used_by_id, segment_type, market_segment_id,
1755 object_version_number, exclude_flag
1756 FROM ams_act_market_segments
1757 WHERE activity_market_segment_id = p_act_mkt_seg_id;
1758
1759 BEGIN
1760
1761 SAVEPOINT update_market_pub;
1762
1763 IF l_mks_rec.activity_market_segment_id IS NOT NULL THEN
1764 OPEN c_mkt_seg_details (l_mks_rec.activity_market_segment_id);
1765 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;
1766 CLOSE c_mkt_seg_details;
1767 END IF;
1768
1769 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);
1770 l_mks_rec.segment_type := NVL(l_mks_rec.segment_type,l_segment_type);
1771 l_mks_rec.market_segment_id := NVL(l_mks_rec.market_segment_id, l_mkt_seg_id);
1772 l_mks_rec.object_version_number := l_object_version;
1773 l_mks_rec.exclude_flag := NVL(l_mks_rec.exclude_flag,l_exclude_flag);
1774
1775 validate_market_segment(p_mks_rec => l_mks_rec
1776 ,p_mode => l_mode
1777 ,x_return_status => x_return_status);
1778
1779 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1780 RAISE fnd_api.g_exc_unexpected_error;
1781 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1782 RAISE fnd_api.g_exc_error;
1783 END IF;
1784
1785 l_seg_rec.activity_market_segment_id := l_mks_rec.activity_market_segment_id;
1786 l_seg_rec.act_market_segment_used_by_id := l_mks_rec.act_market_segment_used_by_id;
1787 l_seg_rec.arc_act_market_segment_used_by := l_mks_rec.arc_act_market_segment_used_by;
1788 l_seg_rec.segment_type := l_mks_rec.segment_type;
1789 l_seg_rec.market_segment_id := l_mks_rec.market_segment_id;
1790 l_seg_rec.object_version_number := l_mks_rec.object_version_number;
1791 l_seg_rec.exclude_flag := l_mks_rec.exclude_flag;
1792
1793 ams_act_market_segments_pvt.update_market_segments(p_api_version => l_api_version
1794 ,p_init_msg_list => l_init_msg_list
1795 ,p_commit => l_commit
1796 ,p_validation_level => l_validation_level
1797 ,p_mks_rec => l_seg_rec
1798 ,x_return_status => x_return_status
1799 ,x_msg_count => x_msg_count
1800 ,x_msg_data => x_msg_data
1801 );
1802
1803 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1804 RAISE fnd_api.g_exc_unexpected_error;
1805 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1806 RAISE fnd_api.g_exc_error;
1807 END IF;
1808
1809 FND_MSG_PUB.Count_And_Get (
1810 p_encoded => FND_API.G_FALSE,
1811 p_count => x_msg_count,
1812 p_data => x_msg_data
1813 );
1814
1815 EXCEPTION
1816 WHEN FND_API.G_EXC_ERROR THEN
1817 ROLLBACK TO update_market_pub;
1818 x_return_status := FND_API.G_RET_STS_ERROR;
1819 -- Standard call to get message count and if count=1, get the message
1820 FND_MSG_PUB.Count_And_Get (
1821 p_encoded => FND_API.G_FALSE,
1822 p_count => x_msg_count,
1823 p_data => x_msg_data
1824 );
1825 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1826 ROLLBACK TO update_market_pub;
1827 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1828 -- Standard call to get message count and if count=1, get the message
1829 FND_MSG_PUB.Count_And_Get (
1830 p_encoded => FND_API.G_FALSE,
1831 p_count => x_msg_count,
1832 p_data => x_msg_data
1833 );
1834 WHEN OTHERS THEN
1835 ROLLBACK TO update_market_pub;
1836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1837 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1838 THEN
1839 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1840 END IF;
1841 -- Standard call to get message count and if count=1, get the message
1842 FND_MSG_PUB.Count_And_Get (
1843 p_encoded => FND_API.G_FALSE,
1844 p_count => x_msg_count,
1845 p_data => x_msg_data
1846 );
1847 END update_market_segment;
1848
1849 ---------------------------------------------------------------------
1850 -- PROCEDURE
1851 -- delete_market_segment
1852 --
1853 -- PURPOSE
1854 -- Deletes a market segment for fund.
1855 --
1856 -- PARAMETERS
1857 -- p_act_mks_id : the market segment to be deleted
1858 --
1859 -- HISTORY
1860 -- 07/07/2005 kdass Created
1861 ----------------------------------------------------------------------
1862 /**
1863 * This procedure deletes a market segment for an existing fund.
1864 * @param p_api_version Indicates the version of the API
1865 * @param p_init_msg_list Indicates whether to initialize the message stack
1866 * @param p_commit Indicates whether to commit within the program
1867 * @param p_act_mks_id Market segment identifier of the market segment to be deleted
1868 * @param x_return_status Status of the program
1869 * @param x_msg_count Number of the messages returned by the program
1870 * @param x_msg_data Return message by the program
1871 * @rep:scope public
1872 * @rep:lifecycle active
1873 * @rep:displayname Delete Market Segment
1874 * @rep:compatibility S
1875 * @rep:businessevent None
1876 */
1877 PROCEDURE delete_market_segment(
1878 p_api_version IN NUMBER
1879 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1880 ,p_commit IN VARCHAR2 := fnd_api.g_false
1881 ,p_act_mks_id IN NUMBER
1882 ,x_return_status OUT NOCOPY VARCHAR2
1883 ,x_msg_count OUT NOCOPY NUMBER
1884 ,x_msg_data OUT NOCOPY VARCHAR2)
1885 IS
1886 l_api_name VARCHAR(30) := 'delete_market_segment';
1887 l_api_version NUMBER := p_api_version;
1888 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
1889 l_commit VARCHAR2(1) := p_commit;
1890 l_object_version NUMBER := NULL;
1891
1892 CURSOR c_valid_act_mks_id IS
1893 SELECT object_version_number
1894 FROM ams_act_market_segments
1895 WHERE activity_market_segment_id = p_act_mks_id;
1896
1897 BEGIN
1898
1899 SAVEPOINT delete_market_pub;
1900
1901 --if activity market segment id is null, then raise exception
1902 IF (p_act_mks_id = fnd_api.g_miss_num OR p_act_mks_id IS NULL) THEN
1903
1904 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1905 fnd_message.set_name('OZF', 'OZF_NO_ACT_SEG_ID');
1906 fnd_msg_pub.add;
1907 END IF;
1908 RAISE fnd_api.g_exc_error;
1909 END IF;
1910
1911 --check if the activity market segment id is valid and get the object_version_number
1912 OPEN c_valid_act_mks_id;
1913 FETCH c_valid_act_mks_id INTO l_object_version;
1914 CLOSE c_valid_act_mks_id;
1915
1916 IF l_object_version IS NULL THEN
1917 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1918 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_SEG_ID');
1919 fnd_msg_pub.add;
1920 END IF;
1921 RAISE fnd_api.g_exc_error;
1922 END IF;
1923
1924 --delete market segment
1925 ams_act_market_segments_pvt.delete_market_segments(p_api_version => l_api_version
1926 ,p_init_msg_list => l_init_msg_list
1927 ,p_commit => l_commit
1928 ,p_act_mks_id => p_act_mks_id
1929 ,p_object_version => l_object_version
1930 ,x_return_status => x_return_status
1931 ,x_msg_count => x_msg_count
1932 ,x_msg_data => x_msg_data
1933 );
1934
1935 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1936 RAISE fnd_api.g_exc_unexpected_error;
1937 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1938 RAISE fnd_api.g_exc_error;
1939 END IF;
1940
1941 FND_MSG_PUB.Count_And_Get (
1942 p_encoded => FND_API.G_FALSE,
1943 p_count => x_msg_count,
1944 p_data => x_msg_data
1945 );
1946
1947 EXCEPTION
1948 WHEN FND_API.G_EXC_ERROR THEN
1949 ROLLBACK TO delete_market_pub;
1950 x_return_status := FND_API.G_RET_STS_ERROR;
1951 -- Standard call to get message count and if count=1, get the message
1952 FND_MSG_PUB.Count_And_Get (
1953 p_encoded => FND_API.G_FALSE,
1954 p_count => x_msg_count,
1955 p_data => x_msg_data
1956 );
1957 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958 ROLLBACK TO delete_market_pub;
1959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960 -- Standard call to get message count and if count=1, get the message
1961 FND_MSG_PUB.Count_And_Get (
1962 p_encoded => FND_API.G_FALSE,
1963 p_count => x_msg_count,
1964 p_data => x_msg_data
1965 );
1966 WHEN OTHERS THEN
1967 ROLLBACK TO delete_market_pub;
1968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1969 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1970 THEN
1971 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1972 END IF;
1973 -- Standard call to get message count and if count=1, get the message
1974 FND_MSG_PUB.Count_And_Get (
1975 p_encoded => FND_API.G_FALSE,
1976 p_count => x_msg_count,
1977 p_data => x_msg_data
1978 );
1979 END delete_market_segment;
1980
1981 ---------------------------------------------------------------------
1982 -- PROCEDURE
1983 -- validate_act_product
1984 --
1985 -- PURPOSE
1986 -- Validate product eligibility record
1987 --
1988 -- PARAMETERS
1989 -- p_mks_rec: product eligibility record to be validated
1990 -- x_return_status: return status
1991 --
1992 -- HISTORY
1993 -- 06/29/2005 kdass Created
1994 ---------------------------------------------------------------------
1995 PROCEDURE validate_act_product (
1996 p_act_product_rec IN OUT NOCOPY act_product_rec_type
1997 ,p_mode IN VARCHAR2
1998 ,x_return_status OUT NOCOPY VARCHAR2
1999 )
2000 IS
2001 l_api_name VARCHAR(30) := 'validate_act_product';
2002 l_act_prod_exists NUMBER := NULL;
2003 l_act_prod_used_by_exists NUMBER := NULL;
2004 l_inv_id_exists NUMBER := NULL;
2005
2006 CURSOR c_act_prod_exists (p_activity_product_id IN NUMBER) IS
2007 SELECT 1
2008 FROM ams_act_products
2009 WHERE activity_product_id = p_activity_product_id;
2010
2011 CURSOR c_act_prod_used_by_exists (p_prod_used_by IN NUMBER) IS
2012 SELECT 1
2013 FROM ozf_funds_all_b
2014 WHERE fund_id = p_prod_used_by;
2015
2016 CURSOR c_org_id (p_prod_used_by IN NUMBER) IS
2017 SELECT org_id
2018 FROM ozf_funds_all_b
2019 WHERE fund_id = p_prod_used_by;
2020
2021 CURSOR c_inv_id_exists (p_inventory_id IN NUMBER, p_org_id IN NUMBER) IS
2022 SELECT 1
2023 FROM mtl_system_items_b_kfv
2024 WHERE organization_id = p_org_id
2025 AND inventory_item_id = p_inventory_id;
2026
2027 CURSOR c_inv_name_exists (p_inventory_name IN VARCHAR2, p_org_id IN NUMBER) IS
2028 SELECT inventory_item_id
2029 FROM mtl_system_items_b_kfv
2030 WHERE organization_id = p_org_id
2031 AND concatenated_segments = p_inventory_name;
2032
2033 CURSOR c_cat_id_exists (p_category_id IN NUMBER) IS
2034 SELECT category_set_id
2035 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
2036 WHERE category_id = p_category_id;
2037
2038 --08-MAY-2006 kdass bug 5199585 SQL ID# 17778264 - added last condition so that table uses index
2039 CURSOR c_cat_name_exists (p_category_name IN VARCHAR2) IS
2040 SELECT category_id, category_set_id
2041 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
2042 WHERE category_desc = p_category_name
2043 AND NVL(category_id, 0) = category_id;
2044
2045 BEGIN
2046
2047 IF p_mode = 'CREATE' THEN
2048 p_act_product_rec.activity_product_id := NULL;
2049 ELSE
2050 --if activity product id is null, then raise exception
2051 IF (p_act_product_rec.activity_product_id = fnd_api.g_miss_num OR p_act_product_rec.activity_product_id IS NULL) THEN
2052
2053 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2054 fnd_message.set_name('OZF', 'OZF_NO_ACT_PROD_ID');
2055 fnd_msg_pub.add;
2056 END IF;
2057 x_return_status := fnd_api.g_ret_sts_error;
2058 RETURN;
2059 ELSE
2060
2061 --check if the input activity_product_id is valid
2062 OPEN c_act_prod_exists (p_act_product_rec.activity_product_id);
2063 FETCH c_act_prod_exists INTO l_act_prod_exists;
2064 CLOSE c_act_prod_exists;
2065
2066 IF l_act_prod_exists IS NULL THEN
2067 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2068 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_PROD_ID');
2069 fnd_msg_pub.add;
2070 END IF;
2071 x_return_status := fnd_api.g_ret_sts_error;
2072 RETURN;
2073 END IF;
2074 END IF;
2075 END IF;
2076
2077 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
2078 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2079 fnd_message.set_name('OZF', 'OZF_NO_PROD_USED_BY');
2080 fnd_msg_pub.add;
2081 END IF;
2082 x_return_status := fnd_api.g_ret_sts_error;
2083 RETURN;
2084 ELSE
2085
2086 OPEN c_act_prod_used_by_exists (p_act_product_rec.act_product_used_by_id);
2087 FETCH c_act_prod_used_by_exists INTO l_act_prod_used_by_exists;
2088 CLOSE c_act_prod_used_by_exists;
2089
2090 IF l_act_prod_used_by_exists IS NULL THEN
2091 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2092 fnd_message.set_name('OZF', 'OZF_INVALID_PROD_USED_BY');
2093 fnd_msg_pub.add;
2094 END IF;
2095 x_return_status := fnd_api.g_ret_sts_error;
2096 RETURN;
2097 END IF;
2098 END IF;
2099
2100 --if both inventory item id and inventory item name are null, then raise exception
2101 IF (p_act_product_rec.inventory_item_id = fnd_api.g_miss_num OR p_act_product_rec.inventory_item_id IS NULL) AND
2102 (p_act_product_rec.inventory_item_name = fnd_api.g_miss_char OR p_act_product_rec.inventory_item_name IS NULL) AND
2103 (p_act_product_rec.category_id = fnd_api.g_miss_num OR p_act_product_rec.category_id IS NULL) AND
2104 (p_act_product_rec.category_name = fnd_api.g_miss_char OR p_act_product_rec.category_name IS NULL) THEN
2105
2106 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2107 fnd_message.set_name('OZF', 'OZF_NO_INV_ITEM_CAT');
2108 fnd_msg_pub.add;
2109 END IF;
2110 x_return_status := fnd_api.g_ret_sts_error;
2111 RETURN;
2112
2113 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
2114 (p_act_product_rec.inventory_item_name <> fnd_api.g_miss_char AND p_act_product_rec.inventory_item_name IS NOT NULL) THEN
2115
2116 OPEN c_org_id (p_act_product_rec.act_product_used_by_id);
2117 FETCH c_org_id INTO p_act_product_rec.organization_id;
2118 CLOSE c_org_id;
2119
2120 --if inventory item id is not null
2121 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
2122
2123 --check if the input inventory_item_id valid
2124 OPEN c_inv_id_exists (p_act_product_rec.inventory_item_id, p_act_product_rec.organization_id);
2125 FETCH c_inv_id_exists INTO l_inv_id_exists;
2126 CLOSE c_inv_id_exists;
2127
2128 IF l_inv_id_exists IS NULL THEN
2129 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2130 fnd_message.set_name('OZF', 'OZF_INVALID_INV_ITEM_ID');
2131 fnd_msg_pub.add;
2132 END IF;
2133 x_return_status := fnd_api.g_ret_sts_error;
2134 RETURN;
2135 END IF;
2136
2137 --if inventory item name is not null
2138 ELSE
2139 --check if the input inventory item name is valid
2140 OPEN c_inv_name_exists (p_act_product_rec.inventory_item_name, p_act_product_rec.organization_id);
2141 FETCH c_inv_name_exists INTO p_act_product_rec.inventory_item_id;
2142 CLOSE c_inv_name_exists;
2143
2144 IF p_act_product_rec.inventory_item_id IS NULL THEN
2145 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2146 fnd_message.set_name('OZF', 'OZF_INVALID_INV_ITEM_NAME');
2147 fnd_msg_pub.add;
2148 END IF;
2149 x_return_status := fnd_api.g_ret_sts_error;
2150 RETURN;
2151 END IF;
2152 END IF;
2153
2154 p_act_product_rec.level_type_code := 'PRODUCT';
2155
2156 ELSIF (p_act_product_rec.category_id <> fnd_api.g_miss_num AND p_act_product_rec.category_id IS NOT NULL) OR
2157 (p_act_product_rec.category_name <> fnd_api.g_miss_char AND p_act_product_rec.category_name IS NOT NULL) THEN
2158
2159 --if category id is not null
2160 IF p_act_product_rec.category_id <> fnd_api.g_miss_num AND p_act_product_rec.category_id IS NOT NULL THEN
2161
2162 --check if the input category_id valid
2163 OPEN c_cat_id_exists (p_act_product_rec.category_id);
2164 FETCH c_cat_id_exists INTO p_act_product_rec.category_set_id;
2165 CLOSE c_cat_id_exists;
2166
2167 IF p_act_product_rec.category_set_id IS NULL THEN
2168 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2169 fnd_message.set_name('OZF', 'OZF_INVALID_CATEGORY_ID');
2170 fnd_msg_pub.add;
2171 END IF;
2172 x_return_status := fnd_api.g_ret_sts_error;
2173 RETURN;
2174 END IF;
2175
2176 --if category name is not null
2177 ELSE
2178 --check if the input category name is valid
2179 OPEN c_cat_name_exists (p_act_product_rec.category_name);
2180 FETCH c_cat_name_exists INTO p_act_product_rec.category_id, p_act_product_rec.category_set_id;
2181 CLOSE c_cat_name_exists;
2182
2183 IF p_act_product_rec.category_id IS NULL THEN
2184 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2185 fnd_message.set_name('OZF', 'OZF_INVALID_CATEGORY_NAME');
2186 fnd_msg_pub.add;
2187 END IF;
2188 x_return_status := fnd_api.g_ret_sts_error;
2189 RETURN;
2190 END IF;
2191 END IF;
2192
2193 p_act_product_rec.level_type_code := 'FAMILY';
2194
2195 END IF;
2196
2197 p_act_product_rec.arc_act_product_used_by := 'FUND';
2198
2199 p_act_product_rec.primary_product_flag := NVL(p_act_product_rec.primary_product_flag, 'N');
2200
2201 IF p_act_product_rec.primary_product_flag NOT IN ('Y', 'N') THEN
2202 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2203 fnd_message.set_name('OZF', 'OZF_INVALID_PRIMARY_FLAG');
2204 fnd_msg_pub.add;
2205 END IF;
2206 x_return_status := fnd_api.g_ret_sts_error;
2207 RETURN;
2208 END IF;
2209
2210 p_act_product_rec.excluded_flag := NVL(p_act_product_rec.excluded_flag, 'N');
2211
2212 IF p_act_product_rec.excluded_flag NOT IN ('Y', 'N') THEN
2213 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2214 fnd_message.set_name('OZF', 'OZF_INVALID_EXCLUDED_FLAG');
2215 fnd_msg_pub.add;
2216 END IF;
2217 x_return_status := fnd_api.g_ret_sts_error;
2218 RETURN;
2219 END IF;
2220
2221 END validate_act_product;
2222
2223 ----------------------------------------------------------------------
2224 -- PROCEDURE
2225 -- create_product_eligibility
2226 --
2227 -- PURPOSE
2228 -- Creates the product eligibility record for fund or quota.
2229 --
2230 -- PARAMETERS
2231 -- p_act_product_rec : the record with new items
2232 -- x_act_product_id : return the activity product id for the fund or quota
2233 --
2234 -- HISTORY
2235 -- 07/11/2005 kdass Created
2236 ----------------------------------------------------------------------
2237 PROCEDURE create_product_eligibility(
2238 p_api_version IN NUMBER
2239 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2240 ,p_commit IN VARCHAR2 := fnd_api.g_false
2241 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2242 ,p_act_product_rec IN act_product_rec_type
2243 ,x_return_status OUT NOCOPY VARCHAR2
2244 ,x_msg_count OUT NOCOPY NUMBER
2245 ,x_msg_data OUT NOCOPY VARCHAR2
2246 ,x_act_product_id OUT NOCOPY NUMBER
2247 )
2248 IS
2249 l_api_name VARCHAR(30) := 'create_product_eligibility';
2250 l_mode VARCHAR2(6) := 'CREATE';
2251 l_act_prod_rec act_product_rec_type := p_act_product_rec;
2252 l_act_product_rec ams_actproduct_pvt.act_product_rec_type;
2253 l_api_version NUMBER := p_api_version;
2254 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2255 l_validation_level NUMBER := p_validation_level;
2256 l_commit VARCHAR2(1) := p_commit;
2257
2258 BEGIN
2259
2260 SAVEPOINT create_product_pub;
2261
2262 validate_act_product(p_act_product_rec => l_act_prod_rec
2263 ,p_mode => l_mode
2264 ,x_return_status => x_return_status);
2265
2266 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2267 RAISE fnd_api.g_exc_unexpected_error;
2268 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2269 RAISE fnd_api.g_exc_error;
2270 END IF;
2271
2272 l_act_product_rec.act_product_used_by_id := l_act_prod_rec.act_product_used_by_id;
2273 l_act_product_rec.arc_act_product_used_by := l_act_prod_rec.arc_act_product_used_by;
2274 l_act_product_rec.inventory_item_id := l_act_prod_rec.inventory_item_id;
2275 l_act_product_rec.level_type_code := l_act_prod_rec.level_type_code;
2276 l_act_product_rec.category_id := l_act_prod_rec.category_id;
2277 l_act_product_rec.category_set_id := l_act_prod_rec.category_set_id;
2278 l_act_product_rec.primary_product_flag := l_act_prod_rec.primary_product_flag;
2279 l_act_product_rec.excluded_flag := l_act_prod_rec.excluded_flag;
2280 l_act_product_rec.organization_id := l_act_prod_rec.organization_id;
2281
2282 ams_actproduct_pvt.create_act_product(p_api_version => l_api_version
2283 ,p_init_msg_list => l_init_msg_list
2284 ,p_commit => l_commit
2285 ,p_validation_level => l_validation_level
2286 ,p_act_product_rec => l_act_product_rec
2287 ,x_return_status => x_return_status
2288 ,x_msg_count => x_msg_count
2289 ,x_msg_data => x_msg_data
2290 ,x_act_product_id => x_act_product_id
2291 );
2292
2293 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2294 RAISE fnd_api.g_exc_unexpected_error;
2295 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2296 RAISE fnd_api.g_exc_error;
2297 END IF;
2298
2299 FND_MSG_PUB.Count_And_Get (
2300 p_encoded => FND_API.G_FALSE,
2301 p_count => x_msg_count,
2302 p_data => x_msg_data
2303 );
2304
2305 EXCEPTION
2306 WHEN FND_API.G_EXC_ERROR THEN
2307 ROLLBACK TO create_product_pub;
2308 x_return_status := FND_API.G_RET_STS_ERROR;
2309 -- Standard call to get message count and if count=1, get the message
2310 FND_MSG_PUB.Count_And_Get (
2311 p_encoded => FND_API.G_FALSE,
2312 p_count => x_msg_count,
2313 p_data => x_msg_data
2314 );
2315 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2316 ROLLBACK TO create_product_pub;
2317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2318 -- Standard call to get message count and if count=1, get the message
2319 FND_MSG_PUB.Count_And_Get (
2320 p_encoded => FND_API.G_FALSE,
2321 p_count => x_msg_count,
2322 p_data => x_msg_data
2323 );
2324 WHEN OTHERS THEN
2325 ROLLBACK TO create_product_pub;
2326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2327 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2328 THEN
2329 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2330 END IF;
2331 -- Standard call to get message count and if count=1, get the message
2332 FND_MSG_PUB.Count_And_Get (
2333 p_encoded => FND_API.G_FALSE,
2334 p_count => x_msg_count,
2335 p_data => x_msg_data
2336 );
2337 END create_product_eligibility;
2338
2339 ---------------------------------------------------------------------
2340 -- PROCEDURE
2341 -- update_product_eligibility
2342 --
2343 -- PURPOSE
2344 -- Updates the product eligibility record for fund or quota.
2345 --
2346 -- PARAMETERS
2347 -- p_act_product_rec : the record with items to be updated
2348 --
2349 -- HISTORY
2350 -- 07/11/2005 kdass Created
2351 ----------------------------------------------------------------------
2352 PROCEDURE update_product_eligibility(
2353 p_api_version IN NUMBER
2354 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2355 ,p_commit IN VARCHAR2 := fnd_api.g_false
2356 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2357 ,p_act_product_rec IN act_product_rec_type
2358 ,x_return_status OUT NOCOPY VARCHAR2
2359 ,x_msg_count OUT NOCOPY NUMBER
2360 ,x_msg_data OUT NOCOPY VARCHAR2
2361 )
2362 IS
2363 l_api_name VARCHAR(30) := 'update_product_eligibility';
2364 l_mode VARCHAR2(6) := 'UPDATE';
2365 l_act_prod_rec act_product_rec_type := p_act_product_rec;
2366 l_act_product_rec ams_actproduct_pvt.act_product_rec_type;
2367 l_api_version NUMBER := p_api_version;
2368 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2369 l_validation_level NUMBER := p_validation_level;
2370 l_commit VARCHAR2(1) := p_commit;
2371 l_prod_used_by_id NUMBER;
2372 l_item_id NUMBER;
2373 l_cat_id NUMBER;
2374 l_cat_set_id NUMBER;
2375 l_primary_flag VARCHAR2(1);
2376 l_excluded_flag VARCHAR2(1);
2377 l_obj_ver NUMBER;
2378
2379 CURSOR c_prod_elig_details (p_act_prod_id IN NUMBER) IS
2380 SELECT act_product_used_by_id, inventory_item_id, category_id, category_set_id,
2381 primary_product_flag, excluded_flag, object_version_number
2382 FROM ams_act_products
2383 WHERE activity_product_id = p_act_prod_id;
2384
2385 BEGIN
2386
2387 SAVEPOINT update_product_pub;
2388
2389 IF l_act_prod_rec.activity_product_id IS NOT NULL THEN
2390 OPEN c_prod_elig_details (l_act_prod_rec.activity_product_id);
2391 FETCH c_prod_elig_details INTO l_prod_used_by_id, l_item_id, l_cat_id, l_cat_set_id,
2392 l_primary_flag, l_excluded_flag, l_obj_ver;
2393 CLOSE c_prod_elig_details;
2394 END IF;
2395
2396 l_act_prod_rec.act_product_used_by_id := NVL(l_act_prod_rec.act_product_used_by_id,l_prod_used_by_id);
2397 l_act_prod_rec.inventory_item_id := NVL(l_act_prod_rec.inventory_item_id,l_item_id);
2398 l_act_prod_rec.category_id := NVL(l_act_prod_rec.category_id,l_cat_id);
2399 l_act_prod_rec.category_set_id := NVL(l_act_prod_rec.category_set_id,l_cat_set_id);
2400 l_act_prod_rec.primary_product_flag := NVL(l_act_prod_rec.primary_product_flag,l_primary_flag);
2401 l_act_prod_rec.excluded_flag := NVL(l_act_prod_rec.excluded_flag,l_excluded_flag);
2402 l_act_prod_rec.object_version_number := NVL(l_act_prod_rec.object_version_number,l_obj_ver);
2403
2404 validate_act_product(p_act_product_rec => l_act_prod_rec
2405 ,p_mode => l_mode
2406 ,x_return_status => x_return_status);
2407
2408 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2409 RAISE fnd_api.g_exc_unexpected_error;
2410 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2411 RAISE fnd_api.g_exc_error;
2412 END IF;
2413
2414 l_act_product_rec.activity_product_id := l_act_prod_rec.activity_product_id;
2415 l_act_product_rec.act_product_used_by_id := l_act_prod_rec.act_product_used_by_id;
2416 l_act_product_rec.arc_act_product_used_by := l_act_prod_rec.arc_act_product_used_by;
2417 l_act_product_rec.inventory_item_id := l_act_prod_rec.inventory_item_id;
2418 l_act_product_rec.level_type_code := l_act_prod_rec.level_type_code;
2419 l_act_product_rec.category_id := l_act_prod_rec.category_id;
2420 l_act_product_rec.category_set_id := l_act_prod_rec.category_set_id;
2421 l_act_product_rec.primary_product_flag := l_act_prod_rec.primary_product_flag;
2422 l_act_product_rec.excluded_flag := l_act_prod_rec.excluded_flag;
2423 l_act_product_rec.organization_id := l_act_prod_rec.organization_id;
2424 l_act_product_rec.object_version_number := l_act_prod_rec.object_version_number;
2425 l_act_product_rec.enabled_flag := 'Y';
2426
2427 ams_actproduct_pvt.update_act_product(p_api_version => l_api_version
2428 ,p_init_msg_list => l_init_msg_list
2429 ,p_commit => l_commit
2430 ,p_validation_level => l_validation_level
2431 ,p_act_product_rec => l_act_product_rec
2432 ,x_return_status => x_return_status
2433 ,x_msg_count => x_msg_count
2434 ,x_msg_data => x_msg_data
2435 );
2436
2437 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2438 RAISE fnd_api.g_exc_unexpected_error;
2439 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2440 RAISE fnd_api.g_exc_error;
2441 END IF;
2442
2443 FND_MSG_PUB.Count_And_Get (
2444 p_encoded => FND_API.G_FALSE,
2445 p_count => x_msg_count,
2446 p_data => x_msg_data
2447 );
2448
2449 EXCEPTION
2450 WHEN FND_API.G_EXC_ERROR THEN
2451 ROLLBACK TO update_product_pub;
2452 x_return_status := FND_API.G_RET_STS_ERROR;
2453 -- Standard call to get message count and if count=1, get the message
2454 FND_MSG_PUB.Count_And_Get (
2455 p_encoded => FND_API.G_FALSE,
2456 p_count => x_msg_count,
2457 p_data => x_msg_data
2458 );
2459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2460 ROLLBACK TO update_product_pub;
2461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2462 -- Standard call to get message count and if count=1, get the message
2463 FND_MSG_PUB.Count_And_Get (
2464 p_encoded => FND_API.G_FALSE,
2465 p_count => x_msg_count,
2466 p_data => x_msg_data
2467 );
2468 WHEN OTHERS THEN
2469 ROLLBACK TO update_product_pub;
2470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2471 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2472 THEN
2473 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2474 END IF;
2475 -- Standard call to get message count and if count=1, get the message
2476 FND_MSG_PUB.Count_And_Get (
2477 p_encoded => FND_API.G_FALSE,
2478 p_count => x_msg_count,
2479 p_data => x_msg_data
2480 );
2481 END update_product_eligibility;
2482
2483 ---------------------------------------------------------------------
2484 -- PROCEDURE
2485 -- delete_product_eligibility
2486 --
2487 -- PURPOSE
2488 -- Deletes the product eligibility record for fund or quota.
2489 --
2490 -- PARAMETERS
2491 -- p_act_product_id : the product eligibility to be deleted
2492 --
2493 -- HISTORY
2494 -- 07/11/2005 kdass Created
2495 ----------------------------------------------------------------------
2496 PROCEDURE delete_product_eligibility(
2497 p_api_version IN NUMBER
2498 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2499 ,p_commit IN VARCHAR2 := fnd_api.g_false
2500 ,p_act_product_id IN NUMBER
2501 ,x_return_status OUT NOCOPY VARCHAR2
2502 ,x_msg_count OUT NOCOPY NUMBER
2503 ,x_msg_data OUT NOCOPY VARCHAR2
2504 )
2505 IS
2506 l_api_name VARCHAR(30) := 'delete_product_eligibility';
2507 l_api_version NUMBER := p_api_version;
2508 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2509 l_commit VARCHAR2(1) := p_commit;
2510 l_object_version NUMBER := NULL;
2511
2512 CURSOR c_valid_act_prod_id IS
2513 SELECT object_version_number
2514 FROM ams_act_products
2515 WHERE activity_product_id = p_act_product_id;
2516
2517 BEGIN
2518
2519 SAVEPOINT delete_product_pub;
2520
2521 --if activity market segment id is null, then raise exception
2522 IF (p_act_product_id = fnd_api.g_miss_num OR p_act_product_id IS NULL) THEN
2523
2524 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2525 fnd_message.set_name('OZF', 'OZF_NO_ACT_PROD_ID');
2526 fnd_msg_pub.add;
2527 END IF;
2528 RAISE fnd_api.g_exc_error;
2529 END IF;
2530
2531 --check if the activity product id is valid and get the object_version_number
2532 OPEN c_valid_act_prod_id;
2533 FETCH c_valid_act_prod_id INTO l_object_version;
2534 CLOSE c_valid_act_prod_id;
2535
2536 IF l_object_version IS NULL THEN
2537 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2538 fnd_message.set_name('OZF', 'OZF_INVALID_ACT_PROD_ID');
2539 fnd_msg_pub.add;
2540 END IF;
2541 RAISE fnd_api.g_exc_error;
2542 END IF;
2543
2544 --delete product eligibility record
2545 ams_actproduct_pvt.delete_act_product(p_api_version => l_api_version
2546 ,p_init_msg_list => l_init_msg_list
2547 ,p_commit => l_commit
2548 ,p_act_product_id => p_act_product_id
2549 ,p_object_version => l_object_version
2550 ,x_return_status => x_return_status
2551 ,x_msg_count => x_msg_count
2552 ,x_msg_data => x_msg_data
2553 );
2554
2555 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2556 RAISE fnd_api.g_exc_unexpected_error;
2557 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2558 RAISE fnd_api.g_exc_error;
2559 END IF;
2560
2561 FND_MSG_PUB.Count_And_Get (
2562 p_encoded => FND_API.G_FALSE,
2563 p_count => x_msg_count,
2564 p_data => x_msg_data
2565 );
2566
2567 EXCEPTION
2568 WHEN FND_API.G_EXC_ERROR THEN
2569 ROLLBACK TO delete_product_pub;
2570 x_return_status := FND_API.G_RET_STS_ERROR;
2571 -- Standard call to get message count and if count=1, get the message
2572 FND_MSG_PUB.Count_And_Get (
2573 p_encoded => FND_API.G_FALSE,
2574 p_count => x_msg_count,
2575 p_data => x_msg_data
2576 );
2577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2578 ROLLBACK TO delete_product_pub;
2579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2580 -- Standard call to get message count and if count=1, get the message
2581 FND_MSG_PUB.Count_And_Get (
2582 p_encoded => FND_API.G_FALSE,
2583 p_count => x_msg_count,
2584 p_data => x_msg_data
2585 );
2586 WHEN OTHERS THEN
2587 ROLLBACK TO delete_product_pub;
2588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2589 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2590 THEN
2591 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2592 END IF;
2593 -- Standard call to get message count and if count=1, get the message
2594 FND_MSG_PUB.Count_And_Get (
2595 p_encoded => FND_API.G_FALSE,
2596 p_count => x_msg_count,
2597 p_data => x_msg_data
2598 );
2599 END delete_product_eligibility;
2600
2601 /*kdass - funds accrual process by business event descoped due to performance issues.
2602 added back by feliu since calling API don't descope. */
2603 PROCEDURE increase_order_message_counter
2604 IS
2605 BEGIN
2606 SAVEPOINT increase_order_message_counter;
2607
2608 -- ozf_accrual_engine.increase_order_message_counter;
2609
2610 EXCEPTION
2611 WHEN OTHERS THEN
2612 ROLLBACK TO increase_order_message_counter;
2613 END increase_order_message_counter;
2614
2615
2616 END OZF_FUNDS_PUB;
[Home] [Help]