[Home] [Help]
PACKAGE BODY: APPS.OZF_QUOTA_PUB
Source
1 PACKAGE BODY OZF_QUOTA_PUB AS
2 /* $Header: OZFPQUOB.pls 120.5 2006/06/01 15:23:09 mgudivak noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_QUOTA_PUB';
5 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
6
7 PROCEDURE generate_product_spread(
8 p_api_version IN NUMBER
9 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
10 ,p_commit IN VARCHAR2 := fnd_api.g_false
11 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
12 ,p_quota_id IN NUMBER
13 ,x_return_status OUT NOCOPY VARCHAR2
14 ,x_error_number OUT NOCOPY NUMBER
15 ,x_error_message OUT NOCOPY VARCHAR2) ;
16
17 PROCEDURE create_allocation(
18 p_api_version IN NUMBER
19 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
20 ,p_commit IN VARCHAR2 := fnd_api.g_false
21 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
22 ,p_alloc_rec IN alloc_rec_type
23 ,x_return_status OUT NOCOPY VARCHAR2
24 ,x_msg_count OUT NOCOPY NUMBER
25 ,x_msg_data OUT NOCOPY VARCHAR2
26 ,x_alloc_id OUT NOCOPY NUMBER) ;
27
28 PROCEDURE publish_allocation(
29 p_api_version IN NUMBER
30 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
31 ,p_commit IN VARCHAR2 := fnd_api.g_false
32 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
33 ,p_alloc_id IN NUMBER
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_data OUT NOCOPY VARCHAR2) ;
37
38
39 PROCEDURE validate_time_period (p_period_type_id IN NUMBER,
40 p_time_id IN NUMBER,
41 x_return_status OUT NOCOPY VARCHAR2)
42 IS
43
44 CURSOR c_chk_period(p_time_id IN NUMBER) IS
45 SELECT 1 FROM ozf_time_ent_period WHERE ent_period_id = p_time_id;
46
47 CURSOR c_chk_qtr(p_time_id IN NUMBER) IS
48 SELECT 1 FROM ozf_time_ent_qtr WHERE ent_qtr_id = p_time_id;
49
50 l_time_id NUMBER;
51
52 BEGIN
53
54 IF p_period_type_id IS NULL OR p_period_type_id NOT IN (32,64)
55 THEN
56 --
57 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
58 THEN
59 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_PERIOD');
60 fnd_message.set_token('VALUE', p_period_type_id);
61 fnd_msg_pub.add;
62 END IF;
63 x_return_status := fnd_api.g_ret_sts_error;
64 ELSE
65
66 IF p_period_type_id = 32
67 THEN
68 open c_chk_period(p_time_id);
69 fetch c_chk_period INTO l_time_id;
70 close c_chk_period;
71 ELSE
72 open c_chk_qtr(p_time_id);
73 fetch c_chk_qtr INTO l_time_id;
74 close c_chk_qtr;
75 END IF;
76
77 IF l_time_id IS NULL
78 THEN
79 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
80 THEN
81 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_TIMEID');
82 fnd_message.set_token('VALUE', p_time_id);
83 fnd_msg_pub.add;
84 END IF;
85 x_return_status := fnd_api.g_ret_sts_error;
86 END IF;
87
88 END IF;
89
90 END validate_time_period;
91
92 ---------------------------------------------------------------------
93 -- PROCEDURE
94 -- validate_quota_attributes
95 --
96 -- PURPOSE
97 -- Validate quota attributes.
98 --
99 -- PARAMETERS
100 -- p_quota_rec: quota record to be validated
101 -- p_mode: CREATE or UPDATE
102 -- x_return_status: return status
103 --
104 -- HISTORY
105 -- 06/29/2005 kdass Created
106
107 /*
108 Required
109 --------
110 quota_id
111 quota_number
112 short_name
113 custom_setup_id
114 user_status_id
115 start_period_name
116 end_period_name
117 quota_amount
118 owner
119 product_spread_time_id
120
121 Foreign Keys
122 ------------
123 quota_id
124 parent_quota_id
125 custom_setup_id
126 user_status_id
127 owner
128 threshold_id
129 product_spread_time_id
130
131 Columns allowed to update
132 -------------------------
133 short_name
134 description
135 quota_amount
136 owner
137 threshold_id
138
139 Derived
140 -------
141 status_code
142 start_date_active
143 end_date_active
144 currency_code_tc
145 created_from
146 */
147 ---------------------------------------------------------------------
148 PROCEDURE validate_quota_attributes (
149 p_quota_rec IN OUT NOCOPY quota_rec_type
150 ,p_mode IN VARCHAR2
151 ,p_method IN VARCHAR2 := FND_API.G_MISS_CHAR
152 ,p_fund_rec IN OUT NOCOPY OZF_Funds_Pub.fund_rec_type
153 ,x_return_status OUT NOCOPY VARCHAR2
154 )
155 IS
156 l_api_name VARCHAR(30) := 'Validate_Quota_Attributes';
157 l_quota_exists NUMBER := NULL;
158 l_period_exists NUMBER := NULL;
159 l_custom_setup_exists NUMBER := NULL;
160 l_owner_exists NUMBER := NULL;
161 l_threshold_exists NUMBER := NULL;
162 l_dummy_date DATE := NULL;
163
164 -- Columns to default
165 l_status_code VARCHAR2(30);
166 l_start_date_active DATE := NULL;
167 l_end_date_active DATE := NULL;
168 l_currency_code_tc VARCHAR2(30);
169 l_created_from VARCHAR2(30);
170
171 CURSOR c_quota_exists (p_quota_id IN NUMBER) IS
172 SELECT 1
173 FROM ozf_funds_all_b
174 WHERE fund_type = 'QUOTA'
175 AND fund_id = p_quota_id;
176
177 CURSOR c_quota_num_exists (p_quota_number IN VARCHAR2) IS
178 SELECT fund_id
179 FROM ozf_funds_all_b
180 WHERE fund_type = 'QUOTA'
181 AND fund_number = p_quota_number;
182
183 CURSOR c_custom_setup_exists (p_custom_setup_id IN NUMBER) IS
184 SELECT custom_setup_id
185 FROM ams_custom_setups_vl
186 WHERE object_type = 'FUND'
187 AND application_id = 682
188 AND activity_type_code = 'QUOTA'
189 AND custom_setup_id = p_custom_setup_id;
190
191 CURSOR c_period_exists (p_period IN VARCHAR2) IS
192 SELECT start_date, end_date FROM OZF_TIME_ENT_PERIOD
193 WHERE name = p_period
194 UNION ALL
195 SELECT start_date, end_date FROM OZF_TIME_ENT_QTR
196 WHERE name = p_period
197 UNION ALL
198 SELECT start_date, end_date FROM OZF_TIME_ENT_YEAR
199 WHERE name = p_period;
200
201 CURSOR c_user_status_id (p_user_status_id IN NUMBER) IS
202 SELECT system_status_code
203 FROM ams_user_statuses_vl
204 WHERE system_status_type = 'OZF_FUND_STATUS'
205 AND user_status_id = p_user_status_id
206 AND enabled_flag ='Y';
207
208 CURSOR c_resource_exists (p_resource_id IN NUMBER) IS
209 SELECT 1
210 FROM jtf_rs_resource_extns
211 WHERE resource_id = p_resource_id
212 AND category = 'EMPLOYEE';
213
214 CURSOR c_threshold_exists (p_threshold_id IN NUMBER) IS
215 SELECT 1
216 FROM ozf_thresholds_all_b
217 WHERE threshold_id = p_threshold_id
218 AND threshold_type = 'QUOTA';
219
220 BEGIN
221
222 IF G_DEBUG THEN
223 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name);
224 END IF;
225
226 IF p_mode = 'CREATE'
227 THEN
228 -- Reset quota id value if provided
229 p_quota_rec.quota_id := NULL;
230
231 -- Required Column Check ------------------------
232 -- Short_Name
233 IF p_quota_rec.short_name = fnd_api.g_miss_char
234 OR
235 p_quota_rec.short_name IS NULL
236 THEN
237 --
238 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
239 THEN
240 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
241 fnd_message.set_token('COL_NAME','SHORT_NAME');
242 fnd_msg_pub.add;
243 END IF;
244 x_return_status := fnd_api.g_ret_sts_error;
245 END IF;
246
247 -- Custom_Setup_Id
248 IF p_quota_rec.custom_setup_id = fnd_api.g_miss_num
249 OR
250 p_quota_rec.custom_setup_id IS NULL
251 THEN
252 --
253 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
254 THEN
255 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
256 fnd_message.set_token('COL_NAME','CUSTOM_SETUP_ID');
257 fnd_msg_pub.add;
258 END IF;
259 x_return_status := fnd_api.g_ret_sts_error;
260 END IF;
261
262 -- User_Status_Id
263 IF p_quota_rec.user_status_id = fnd_api.g_miss_num
264 OR
265 p_quota_rec.user_status_id IS NULL
266 THEN
267 --
268 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
269 THEN
270 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
271 fnd_message.set_token('COL_NAME','USER_STATUS_ID');
272 fnd_msg_pub.add;
273 END IF;
274 x_return_status := fnd_api.g_ret_sts_error;
275 END IF;
276
277 -- Start_Period_Name
278 IF p_quota_rec.start_period_name = fnd_api.g_miss_char
279 OR
280 p_quota_rec.start_period_name IS NULL
281 THEN
282 --
283 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
284 THEN
285 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
286 fnd_message.set_token('COL_NAME','START_PERIOD_NAME');
287 fnd_msg_pub.add;
288 END IF;
289 x_return_status := fnd_api.g_ret_sts_error;
290 END IF;
291
292 -- End_Period_Name
293 IF p_quota_rec.start_period_name = fnd_api.g_miss_char
294 OR
295 p_quota_rec.start_period_name IS NULL
296 THEN
297 --
298 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
299 THEN
300 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
301 fnd_message.set_token('COL_NAME','END_PERIOD_NAME');
302 fnd_msg_pub.add;
303 END IF;
304 x_return_status := fnd_api.g_ret_sts_error;
305 END IF;
306
307 -- Quota_Amount
308 IF p_quota_rec.quota_amount = fnd_api.g_miss_num
309 OR
310 p_quota_rec.quota_amount IS NULL
311 THEN
312 --
313 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
314 THEN
315 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
316 fnd_message.set_token('COL_NAME','QUOTA_AMOUNT');
317 fnd_msg_pub.add;
318 END IF;
319 x_return_status := fnd_api.g_ret_sts_error;
320 END IF;
321
322 -- Owner
323 IF p_quota_rec.owner = fnd_api.g_miss_num
324 OR
325 p_quota_rec.owner IS NULL
326 THEN
327 --
328 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
329 THEN
330 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
331 fnd_message.set_token('COL_NAME','OWNER');
332 fnd_msg_pub.add;
333 END IF;
334 x_return_status := fnd_api.g_ret_sts_error;
335 END IF;
336
337 -- Product_Spread_Time_Id
338 IF p_quota_rec.product_spread_time_id = fnd_api.g_miss_num
339 OR
340 p_quota_rec.product_spread_time_id IS NULL
341 THEN
342 --
343 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
344 THEN
345 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
346 fnd_message.set_token('COL_NAME','PRODUCT_SPREAD_TIME_ID');
347 fnd_msg_pub.add;
348 END IF;
349 x_return_status := fnd_api.g_ret_sts_error;
350 END IF;
351
352 -- Return Error when all the required columns are checked
353 IF x_return_status = fnd_api.g_ret_sts_error
354 THEN
355 RETURN;
356 END IF;
357
358 -- End Required Column Check ------------------
359
360 -- Check Foreign Key for columns that are valid for Create mode only
361
362 -- If Parent_Quota_Id is provided, then it should be valid
363 IF p_quota_rec.parent_quota_id <> fnd_api.g_miss_num
364 AND
365 p_quota_rec.parent_quota_id IS NOT NULL
366 THEN
367 --
368 -- Parent_Quota_Id should be populated only
369 -- when method is MANUAL. If method is ALLOCATION
370 -- then, the quota hierarchy is created automatically
371 --
372 IF p_method = 'ALLOCATE'
373 THEN
374 --
375 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
376 THEN
377 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
378 fnd_message.set_token('COL_NAME', 'PARENT_QUOTA_ID');
379 fnd_msg_pub.add;
380 END IF;
381 x_return_status := fnd_api.g_ret_sts_error;
382 RETURN;
383 --
384 ELSE
385 --
386 OPEN c_quota_exists (p_quota_rec.parent_quota_id);
387 FETCH c_quota_exists INTO l_quota_exists;
388 CLOSE c_quota_exists;
389
390 IF l_quota_exists IS NULL
391 THEN
392 --
393 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
394 THEN
395 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
396 fnd_message.set_token('COL_NAME', 'PARENT_QUOTA_ID');
397 fnd_msg_pub.add;
398 END IF;
399 x_return_status := fnd_api.g_ret_sts_error;
400 RETURN;
401 END IF;
402 --
403 END IF;
404 --
405 END IF; -- Check Parent Quota Id
406
407 -- Custom_Setup_Id
408 IF p_quota_rec.custom_setup_id <> fnd_api.g_miss_num
409 AND
410 p_quota_rec.custom_setup_id IS NOT NULL
411 THEN
412 OPEN c_custom_setup_exists (p_quota_rec.custom_setup_id);
413 FETCH c_custom_setup_exists INTO l_custom_setup_exists;
414 CLOSE c_custom_setup_exists;
415
416 IF l_custom_setup_exists IS NULL
417 THEN
418 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
419 THEN
420 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
421 fnd_message.set_token('COL_NAME', 'CUSTOM_SETUP_ID');
422 fnd_msg_pub.add;
423 END IF;
424 x_return_status := fnd_api.g_ret_sts_error;
425 RETURN;
426 END IF;
427 --
428 END IF;
429
430 -- User_Status_Id
431 IF p_quota_rec.user_status_id <> fnd_api.g_miss_num
432 AND
433 p_quota_rec.user_status_id IS NOT NULL
434 THEN
435 OPEN c_user_status_id (p_quota_rec.user_status_id);
436 FETCH c_user_status_id INTO l_status_code;
437 CLOSE c_user_status_id;
438
439 IF l_status_code IS NULL
440 THEN
441 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
442 THEN
443 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
444 fnd_message.set_token('COL_NAME', 'USER_STATUS_ID');
445 fnd_msg_pub.add;
446 END IF;
447 x_return_status := fnd_api.g_ret_sts_error;
448 RETURN;
449 END IF;
450 --
451 END IF;
452
453 -- Start_Period_Name
454 IF p_quota_rec.start_period_name <> fnd_api.g_miss_char
455 AND
456 p_quota_rec.start_period_name IS NOT NULL
457 THEN
458 OPEN c_period_exists (p_quota_rec.start_period_name);
459 FETCH c_period_exists INTO l_start_date_active, l_dummy_date;
460 CLOSE c_period_exists;
461
462 IF l_start_date_active IS NULL
463 THEN
464 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
465 THEN
466 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
467 fnd_message.set_token('COL_NAME', 'START_PERIOD_NAME');
468 fnd_msg_pub.add;
469 END IF;
470 x_return_status := fnd_api.g_ret_sts_error;
471 RETURN;
472 END IF;
473 --
474 END IF;
475
476 -- End_Period_Name
477 IF p_quota_rec.end_period_name <> fnd_api.g_miss_char
478 AND
479 p_quota_rec.end_period_name IS NOT NULL
480 THEN
481 OPEN c_period_exists (p_quota_rec.end_period_name);
482 FETCH c_period_exists INTO l_dummy_date, l_end_date_active;
483 CLOSE c_period_exists;
484
485 IF l_end_date_active IS NULL
486 THEN
487 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
488 THEN
489 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
490 fnd_message.set_token('COL_NAME', 'END_PERIOD_NAME');
491 fnd_msg_pub.add;
492 END IF;
493 x_return_status := fnd_api.g_ret_sts_error;
494 RETURN;
495 END IF;
496 --
497 END IF;
498
499 -- Product_Spread_Time_Id
500 IF p_quota_rec.product_spread_time_id <> fnd_api.g_miss_num
501 AND
502 p_quota_rec.product_spread_time_id IS NOT NULL
503 THEN
504
505 IF p_quota_rec.product_spread_time_id NOT IN ('32', '64')
506 THEN
507 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
508 THEN
509 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
510 fnd_message.set_token('COL_NAME', 'PRODUCT_SPREAD_TIME_ID');
511 fnd_msg_pub.add;
512 END IF;
513 x_return_status := fnd_api.g_ret_sts_error;
514 RETURN;
515 END IF;
516 --
517 END IF;
518
519 -- End checking foreign keys for columns in create mode
520
521 END IF; -- End Create Mode
522
523 -- The following columns can be updated also. So do the foreign
524 -- key check in both create and update
525 -- 1. Owner
526 -- 2. Threshold_Id
527
528 -- Owner
529 IF p_quota_rec.owner <> fnd_api.g_miss_num
530 AND
531 p_quota_rec.owner IS NOT NULL
532 THEN
533
534 OPEN c_resource_exists (p_quota_rec.owner);
535 FETCH c_resource_exists INTO l_owner_exists;
536 CLOSE c_resource_exists;
537
538 IF l_owner_exists IS NULL
539 THEN
540 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
541 THEN
542 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
543 fnd_message.set_token('COL_NAME', 'OWNER');
544 fnd_msg_pub.add;
545 END IF;
546 x_return_status := fnd_api.g_ret_sts_error;
547 RETURN;
548 END IF;
549 --
550 END IF;
551
552 -- Threshold_Id
553 IF p_quota_rec.threshold_id <> fnd_api.g_miss_num
554 AND
555 p_quota_rec.threshold_id IS NOT NULL
556 THEN
557 --
558 OPEN c_threshold_exists (p_quota_rec.threshold_id);
559 FETCH c_threshold_exists INTO l_threshold_exists;
560 CLOSE c_threshold_exists;
561
562 IF l_threshold_exists IS NULL
563 THEN
564 --
565 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
566 THEN
567 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
568 fnd_message.set_token('COL_NAME', 'THRESHOLD_ID');
569 fnd_msg_pub.add;
570 END IF;
571 x_return_status := fnd_api.g_ret_sts_error;
572 RETURN;
573 END IF;
574 --
575 END IF;
576 -- End foreign key check
577
578 IF p_mode = 'UPDATE'
579 THEN
580
581 -- UPDATE MODE
582 --if both quota id and quota number are null, then raise exception
583 IF (p_quota_rec.quota_id = fnd_api.g_miss_num OR p_quota_rec.quota_id IS NULL)
584 AND
585 (p_quota_rec.quota_number = fnd_api.g_miss_char OR p_quota_rec.quota_number IS NULL)
586 THEN
587
588 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
589 fnd_message.set_name('OZF', 'OZF_NO_QUOTA_ID_NUM');
590 fnd_msg_pub.add;
591 END IF;
592 x_return_status := fnd_api.g_ret_sts_error;
593 RETURN;
594
595 ELSE
596 --if quota id is not null it takes precedence over quota number
597 IF p_quota_rec.quota_id <> fnd_api.g_miss_num
598 AND p_quota_rec.quota_id IS NOT NULL
599 THEN
600
601 --check if the input quota_id is valid
602 OPEN c_quota_exists (p_quota_rec.quota_id);
603 FETCH c_quota_exists INTO l_quota_exists;
604 CLOSE c_quota_exists;
605
606 IF l_quota_exists IS NULL THEN
607 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
608 fnd_message.set_name('OZF', 'OZF_INVALID_QUOTA_ID');
609 fnd_msg_pub.add;
610 END IF;
611 x_return_status := fnd_api.g_ret_sts_error;
612 RETURN;
613 END IF;
614
615 --if quota number is not null
616 ELSE
617 --check if the input quota_number is valid
618 OPEN c_quota_num_exists (p_quota_rec.quota_number);
619 FETCH c_quota_num_exists INTO p_quota_rec.quota_id;
620 CLOSE c_quota_num_exists;
621
622 IF p_quota_rec.quota_id IS NULL THEN
623 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
624 fnd_message.set_name('OZF', 'OZF_INVALID_QUOTA_NUM');
625 fnd_msg_pub.add;
626 END IF;
627 x_return_status := fnd_api.g_ret_sts_error;
628 RETURN;
629 END IF;
630 --
631 END IF; -- End quota_id number check
632 --
633 END IF; -- End Quota ID and Quota Num Check
634 --
635 END IF; -- End Update Mode
636
637
638 -- Populate the Fund Record
639
640 -- Derived Columns
641 p_fund_rec.start_date_active := l_start_date_active;
642 p_fund_rec.end_date_active := l_end_date_active;
643 p_fund_rec.status_code := l_status_code;
644 p_fund_rec.currency_code_tc := fnd_profile.value ('OZF_TP_COMMON_CURRENCY');
645 --p_fund_rec.created_from := p_method; TODO: Add to Fund record
646 -- TODO: Add DFF attributes to fund record.
647
648 p_fund_rec.parent_fund_id := p_quota_rec.parent_quota_id;
649 p_fund_rec.fund_number := p_quota_rec.quota_number;
650 p_fund_rec.short_name := p_quota_rec.short_name;
651 p_fund_rec.fund_type := 'QUOTA';
652 p_fund_rec.custom_setup_id := p_quota_rec.custom_setup_id;
653 p_fund_rec.description := p_quota_rec.description;
654 p_fund_rec.category_id := '10001'; --from FundEO.getCreateAPIRec()
655 p_fund_rec.user_status_id := p_quota_rec.user_status_id;
656 p_fund_rec.start_period_name := p_quota_rec.start_period_name;
657 p_fund_rec.end_period_name := p_quota_rec.end_period_name;
658 p_fund_rec.original_budget := p_quota_rec.quota_amount;
659 p_fund_rec.owner := p_quota_rec.owner;
660 p_fund_rec.threshold_id := p_quota_rec.threshold_id;
661 p_fund_rec.product_spread_time_id := p_quota_rec.product_spread_time_id;
662
663 IF G_DEBUG THEN
664 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name);
665 END IF;
666
667 END validate_quota_attributes;
668
669
670 PROCEDURE validate_product_record(p_init_msg_list IN VARCHAR2,
671 x_Return_Status OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2,
674 p_quota_id IN NUMBER,
675 p_quota_products_rec IN quota_products_rec_type,
676 x_act_product_rec OUT NOCOPY AMS_ActProduct_PVT.act_Product_rec_type)
677 IS
678
679 l_api_name VARCHAR(30) := 'Validate_Product_Record';
680
681 l_quota_products_rec quota_products_rec_type := p_quota_products_rec;
682 l_act_product_rec AMS_ActProduct_PVT.act_Product_rec_type ;
683
684 BEGIN
685 IF G_DEBUG THEN
686 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name);
687 END IF;
688
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690
691 -- Default values
692 l_act_product_rec.ACT_PRODUCT_USED_BY_ID := p_quota_id;
693 l_act_product_rec.ARC_ACT_PRODUCT_USED_BY := 'FUND';
694 l_act_product_rec.PRIMARY_PRODUCT_FLAG := 'N';
695 l_act_product_rec.ENABLED_FLAG := 'Y';
696 l_act_product_rec.EXCLUDED_FLAG := 'N';
697
698 IF l_quota_products_rec.item_type <> fnd_api.g_miss_char
699 AND
700 l_quota_products_rec.item_type IS NOT NULL
701 THEN
702 --
703 IF l_quota_products_rec.item_type NOT IN ('PRODUCT','FAMILY')
704 THEN
705 --
706 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
707 THEN
708 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
709 fnd_message.set_token('COL_NAME', 'ITEM_TYPE');
710 fnd_msg_pub.add;
711 END IF;
712 x_return_status := fnd_api.g_ret_sts_error;
713 RETURN;
714 ELSE
715 --
716 l_act_product_rec.level_type_code := l_quota_products_rec.item_type;
717 IF l_quota_products_rec.item_type = 'PRODUCT'
718 THEN
719 l_act_product_rec.ORGANIZATION_ID := l_quota_products_rec.organization_id;
720 l_act_product_rec.INVENTORY_ITEM_ID := l_quota_products_rec.item_id;
721 ELSE
722 l_act_product_rec.CATEGORY_ID := l_quota_products_rec.item_id;
723 l_act_product_rec.CATEGORY_SET_ID := l_quota_products_rec.category_set_id;
724 END IF;
725 --
726 END IF;
727 --
728 ELSE
729 --
730 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
731 THEN
732 fnd_message.set_name('OZF', 'OZF_QUOTA_MISSING_COL_VALUE');
733 fnd_message.set_token('COL_NAME','ITEM_TYPE');
734 fnd_msg_pub.add;
735 END IF;
736 x_return_status := fnd_api.g_ret_sts_error;
737 --
738 END IF;
739
740 x_act_product_rec := l_act_product_rec;
741
742 IF G_DEBUG THEN
743 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name);
744 END IF;
745
746 EXCEPTION
747
748 WHEN FND_API.G_EXC_ERROR THEN
749 x_return_status := FND_API.G_RET_STS_ERROR ;
750 FND_MSG_PUB.Count_And_Get
751 ( P_count => x_msg_count,
752 P_data => x_msg_data
753 );
754
755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
756 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 FND_MSG_PUB.Count_And_Get
758 ( p_count => x_msg_count,
759 p_data => x_msg_data
760 );
761
762 WHEN OTHERS THEN
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
765 THEN
766 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
767 'Others exception inside Validate_Product_Record'
768 || sqlerrm);
769 END IF;
770
771 END validate_product_record;
772
773
774 PROCEDURE create_quota_header(
775 p_api_version IN NUMBER
776 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
777 ,p_commit IN VARCHAR2 := fnd_api.g_false
778 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
779 ,x_return_status OUT NOCOPY VARCHAR2
780 ,x_msg_count OUT NOCOPY NUMBER
781 ,x_msg_data OUT NOCOPY VARCHAR2
782 ,p_method IN VARCHAR2
783 ,p_mode IN VARCHAR2
784 ,p_quota_rec IN quota_rec_type
785 ,p_quota_markets_tbl IN quota_markets_tbl_type
786 ,p_quota_products_tbl IN quota_products_tbl_type
787 ,x_quota_id OUT NOCOPY NUMBER )
788 IS
789
790 l_api_name CONSTANT VARCHAR2(30) := 'Create_Quota_Header';
791 l_quota_rec quota_rec_type := p_quota_rec;
792 l_quota_markets_tbl quota_markets_tbl_type := p_quota_markets_tbl;
793 l_quota_products_tbl quota_products_tbl_type := p_quota_products_tbl;
794 l_act_product_rec AMS_ActProduct_PVT.act_Product_rec_type;
795 l_product_id NUMBER;
796 l_act_mks_id NUMBER;
797
798 -- Fund related variables
799
800 l_fund_rec OZF_Funds_Pub.fund_rec_type;
801 l_mks_rec OZF_Funds_Pub.mks_rec_type;
802 l_modifier_list_rec ozf_offer_pub.modifier_list_rec_type;
803 l_modifier_line_tbl ozf_offer_pub.modifier_line_tbl_type;
804 l_vo_pbh_tbl ozf_offer_pub.vo_disc_tbl_type;
805 l_vo_dis_tbl ozf_offer_pub.vo_disc_tbl_type;
806 l_vo_prod_tbl ozf_offer_pub.vo_prod_tbl_type;
807 l_qualifier_tbl ozf_offer_pub.qualifiers_tbl_type;
808 l_vo_mo_tbl ozf_offer_pub.vo_mo_tbl_type;
809
810 BEGIN
811
812 -- Initialize API return status to success
813 IF G_DEBUG THEN
814 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name);
815 END IF;
816
817 x_return_status := FND_API.G_RET_STS_SUCCESS;
818
819 validate_quota_attributes( p_quota_rec => l_quota_rec
820 ,p_mode => p_mode
821 ,p_method => p_method
822 ,p_fund_rec => l_fund_rec
823 ,x_return_status => x_return_status);
824
825 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
826 RAISE fnd_api.g_exc_unexpected_error;
827 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
828 RAISE fnd_api.g_exc_error;
829 END IF;
830
831 ozf_funds_pub.create_fund(p_api_version => p_api_version
832 ,p_init_msg_list => p_init_msg_list
833 ,p_commit => p_commit
834 ,p_validation_level => p_validation_level
835 ,x_return_status => x_return_status
836 ,x_msg_count => x_msg_count
837 ,x_msg_data => x_msg_data
838 ,p_fund_rec => l_fund_rec
839 ,p_modifier_list_rec => l_modifier_list_rec
840 ,p_modifier_line_tbl => l_modifier_line_tbl
841 ,p_vo_pbh_tbl => l_vo_pbh_tbl
842 ,p_vo_dis_tbl => l_vo_dis_tbl
843 ,p_vo_prod_tbl => l_vo_prod_tbl
844 ,p_qualifier_tbl => l_qualifier_tbl
845 ,p_vo_mo_tbl => l_vo_mo_tbl
846 ,x_fund_id => x_quota_id
847 );
848
849 IF G_DEBUG THEN
850 ozf_utility_pvt.debug_message('Quota ID: ' || x_quota_id);
851 ozf_utility_pvt.debug_message('Return Status: ' || x_return_status);
852 END IF;
853
854 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
855 RAISE FND_API.G_EXC_ERROR;
856 END IF;
857
858
859 IF p_quota_markets_tbl.COUNT > 0
860 THEN
861 --
862 FOR l_quota_market_counter IN 1..p_quota_markets_tbl.COUNT
863 LOOP
864 --
865 l_quota_markets_tbl(l_quota_market_counter).act_market_segment_used_by_id := x_quota_id;
866 l_quota_markets_tbl(l_quota_market_counter).arc_act_market_segment_used_by := 'FUND';
867 --
868 l_mks_rec := l_quota_markets_tbl(l_quota_market_counter);
869
870 OZF_FUNDS_PUB. create_market_segment(
871 p_api_version
872 ,p_init_msg_list
873 ,p_commit
874 ,p_validation_level
875 ,l_mks_rec
876 ,x_return_status
877 ,x_msg_count
878 ,x_msg_data
879 ,l_act_mks_id );
880
881 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884 --
885 END LOOP;
886 --
887 END IF;
888
889
890 FOR l_counter IN 1..l_quota_products_tbl.COUNT
891 LOOP
892
893 IF l_quota_products_tbl(l_counter).item_type <> 'OTHERS'
894 THEN
895 --
896 validate_product_record(p_init_msg_list => FND_API.G_FALSE,
897 x_Return_Status => x_return_status,
898 x_msg_count => x_msg_count,
899 x_msg_data => x_msg_data,
900 p_quota_id => x_quota_id,
901 p_quota_products_rec => l_quota_products_tbl(l_counter),
902 x_act_product_rec => l_act_product_rec);
903
904 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
905 RAISE FND_API.G_EXC_ERROR;
906 END IF;
907
908 ams_actproduct_pvt.create_act_product(
909 p_api_version => p_api_version
910 ,p_init_msg_list => p_init_msg_list
911 ,p_commit => p_commit
912 ,p_validation_level => p_validation_level
913 ,x_return_status => x_return_status
914 ,x_msg_count => x_msg_count
915 ,x_msg_data => x_msg_data
916 ,p_act_Product_rec => l_act_product_rec
917 ,x_act_Product_id => l_product_id );
918
919 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
920 RAISE FND_API.G_EXC_ERROR;
921 END IF;
922 END IF;
923 --
924 END LOOP;
925
926 IF G_DEBUG THEN
927 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name);
928 END IF;
929
930 EXCEPTION
931 WHEN FND_API.G_EXC_ERROR THEN
932 x_return_status := FND_API.G_RET_STS_ERROR ;
933 FND_MSG_PUB.Count_And_Get
934 ( P_count => x_msg_count,
935 P_data => x_msg_data
936 );
937
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
940 FND_MSG_PUB.Count_And_Get
941 ( p_count => x_msg_count,
942 p_data => x_msg_data
943 );
944
945 WHEN OTHERS THEN
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
947 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
948 THEN
949 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
950 'Others exception inside Create_Quota_Header'
951 || sqlerrm);
952 END IF;
953
954 END create_quota_header;
955
956 ---------------------------------------------------------------------
957 -- PROCEDURE
958 -- validate_alloc_attributes
959 --
960 -- PURPOSE
961 -- Validate allocation attributes.
962 --
963 -- PARAMETERS
964 -- p_alloc_rec: allocation record to be validated
965 -- x_return_status: return status
966 --
967 -- HISTORY
968 -- 07/04/2005 kdass Created
969 ---------------------------------------------------------------------
970 PROCEDURE validate_alloc_attributes (
971 p_alloc_rec IN OUT NOCOPY alloc_rec_type
972 ,x_return_status OUT NOCOPY VARCHAR2
973 )
974 IS
975 l_api_name VARCHAR(30) := 'validate_alloc_attributes';
976 l_quota_exists NUMBER := NULL;
977 l_period_exists NUMBER := NULL;
978
979 CURSOR c_quota_exists (p_quota_id IN NUMBER) IS
980 SELECT 1
981 FROM ozf_funds_all_b
982 WHERE fund_type = 'QUOTA'
983 AND fund_id = p_quota_id;
984
985 CURSOR c_quota_num_exists (p_quota_number IN VARCHAR2) IS
986 SELECT fund_id
987 FROM ozf_funds_all_b
988 WHERE fund_type = 'QUOTA'
989 AND fund_number = p_quota_number;
990
991 CURSOR c_period_exists (p_period IN VARCHAR2) IS
992 SELECT 1 FROM OZF_TIME_ENT_PERIOD
993 WHERE name = p_period
994 UNION
995 SELECT 1 FROM OZF_TIME_ENT_QTR
996 WHERE name = p_period
997 UNION
998 SELECT 1 FROM OZF_TIME_ENT_YEAR
999 WHERE name = p_period;
1000
1001 CURSOR c_from_date (p_period IN VARCHAR2) IS
1002 SELECT start_date
1003 FROM gl_periods_v
1004 WHERE period_set_name = fnd_profile.value ('AMS_CAMPAIGN_DEFAULT_CALENDER')
1005 AND period_name = p_period;
1006
1007 CURSOR c_to_date (p_period IN VARCHAR2) IS
1008 SELECT end_date
1009 FROM gl_periods_v
1010 WHERE period_set_name = fnd_profile.value ('AMS_CAMPAIGN_DEFAULT_CALENDER')
1011 AND period_name = p_period;
1012
1013 CURSOR c_product_spread (p_quota_id IN NUMBER) IS
1014 SELECT product_spread_time_id
1015 FROM ozf_funds_all_b
1016 WHERE fund_id = p_quota_id;
1017
1018 CURSOR c_hier_id_exists (p_hier_id IN NUMBER) IS
1019 SELECT heirarchy_id
1020 FROM ozf_terr_levels_all
1021 WHERE heirarchy_id = p_hier_id;
1022
1023 CURSOR c_from_level_exists (p_hier_id IN NUMBER, p_from_level IN NUMBER) IS
1024 SELECT level_depth
1025 FROM ozf_terr_levels_all
1026 WHERE heirarchy_id = p_hier_id
1027 AND level_depth = p_from_level;
1028
1029 CURSOR c_to_level_exists (p_hier_id IN NUMBER, p_from_level IN NUMBER, p_to_level IN NUMBER) IS
1030 SELECT level_depth
1031 FROM ozf_terr_levels_all
1032 WHERE heirarchy_id = p_hier_id
1033 AND level_depth >= p_from_level
1034 AND level_depth = p_to_level;
1035
1036 CURSOR c_start_node_exists (p_hier_id IN NUMBER, p_from_level IN NUMBER) IS
1037 SELECT node_id
1038 FROM ozf_terr_v
1039 WHERE hierarchy_id = p_hier_id
1040 AND level_depth = p_from_level;
1041
1042 CURSOR c_method_code_exists (p_method_code IN VARCHAR2) IS
1043 SELECT lookup_code
1044 FROM ozf_lookups
1045 WHERE lookup_type = 'OZF_FUND_ALLOC_METHOD'
1046 AND lookup_code = p_method_code;
1047
1048 BEGIN
1049 IF G_DEBUG THEN
1050 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name);
1051 END IF;
1052
1053 --if both quota id and quota number are null, then raise exception
1054 IF (p_alloc_rec.quota_id = fnd_api.g_miss_num OR p_alloc_rec.quota_id IS NULL) AND
1055 (p_alloc_rec.quota_number = fnd_api.g_miss_char OR p_alloc_rec.quota_number IS NULL) THEN
1056
1057 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1058 fnd_message.set_name('OZF', 'OZF_NO_QUOTA_ID_NUM');
1059 fnd_msg_pub.add;
1060 END IF;
1061 x_return_status := fnd_api.g_ret_sts_error;
1062 RETURN;
1063
1064 ELSE
1065 --if quota id is not null
1066 IF p_alloc_rec.quota_id <> fnd_api.g_miss_num AND p_alloc_rec.quota_id IS NOT NULL THEN
1067
1068 --check if the input quota_id is valid
1069 OPEN c_quota_exists (p_alloc_rec.quota_id);
1070 FETCH c_quota_exists INTO l_quota_exists;
1071 CLOSE c_quota_exists;
1072
1073 IF l_quota_exists IS NULL THEN
1074 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1075 fnd_message.set_name('OZF', 'OZF_INVALID_QUOTA_ID');
1076 fnd_msg_pub.add;
1077 END IF;
1078 x_return_status := fnd_api.g_ret_sts_error;
1079 RETURN;
1080 END IF;
1081
1082 --if quota number is not null
1083 ELSE
1084 --check if the input quota_number is valid
1085 OPEN c_quota_num_exists (p_alloc_rec.quota_number);
1086 FETCH c_quota_num_exists INTO p_alloc_rec.quota_id;
1087 CLOSE c_quota_num_exists;
1088
1089 IF p_alloc_rec.quota_id IS NULL THEN
1090 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1091 fnd_message.set_name('OZF', 'OZF_INVALID_QUOTA_NUM');
1092 fnd_msg_pub.add;
1093 END IF;
1094 x_return_status := fnd_api.g_ret_sts_error;
1095 RETURN;
1096 END IF;
1097 END IF;
1098 END IF;
1099
1100 --if hierarchy id is null, then raise exception
1101 IF p_alloc_rec.hierarchy_id = fnd_api.g_miss_num OR p_alloc_rec.hierarchy_id IS NULL THEN
1102 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1103 fnd_message.set_name('OZF', 'OZF_NO_HIER_ID');
1104 fnd_msg_pub.add;
1105 END IF;
1106 x_return_status := fnd_api.g_ret_sts_error;
1107 RETURN;
1108 ELSE
1109 --check if the input hierarchy id is valid
1110 OPEN c_hier_id_exists (p_alloc_rec.hierarchy_id);
1111 FETCH c_hier_id_exists INTO p_alloc_rec.hierarchy_id;
1112 CLOSE c_hier_id_exists;
1113
1114 IF p_alloc_rec.hierarchy_id IS NULL THEN
1115 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1116 fnd_message.set_name('OZF', 'OZF_INVALID_HIER_ID');
1117 fnd_msg_pub.add;
1118 END IF;
1119 x_return_status := fnd_api.g_ret_sts_error;
1120 RETURN;
1121 END IF;
1122 END IF;
1123
1124 --if from level is null, then raise exception
1125 IF p_alloc_rec.from_level = fnd_api.g_miss_num OR p_alloc_rec.from_level IS NULL THEN
1126 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1127 fnd_message.set_name('OZF', 'OZF_NO_FROM_LEVEL');
1128 fnd_msg_pub.add;
1129 END IF;
1130 x_return_status := fnd_api.g_ret_sts_error;
1131 RETURN;
1132 ELSE
1133 --check if the from level is valid
1134 OPEN c_from_level_exists (p_alloc_rec.hierarchy_id, p_alloc_rec.from_level);
1135 FETCH c_from_level_exists INTO p_alloc_rec.from_level;
1136 CLOSE c_from_level_exists;
1137
1138 IF p_alloc_rec.from_level IS NULL THEN
1139 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1140 fnd_message.set_name('OZF', 'OZF_INVALID_FROM_LEVEL');
1141 fnd_msg_pub.add;
1142 END IF;
1143 x_return_status := fnd_api.g_ret_sts_error;
1144 RETURN;
1145 END IF;
1146 END IF;
1147
1148 --if to level is null, then raise exception
1149 IF p_alloc_rec.to_level = fnd_api.g_miss_num OR p_alloc_rec.to_level IS NULL THEN
1150 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1151 fnd_message.set_name('OZF', 'OZF_NO_TO_LEVEL');
1152 fnd_msg_pub.add;
1153 END IF;
1154 x_return_status := fnd_api.g_ret_sts_error;
1155 RETURN;
1156 ELSE
1157 --check if the to level is valid
1158 OPEN c_to_level_exists (p_alloc_rec.hierarchy_id, p_alloc_rec.from_level, p_alloc_rec.to_level);
1159 FETCH c_to_level_exists INTO p_alloc_rec.to_level;
1160 CLOSE c_to_level_exists;
1161
1162 IF p_alloc_rec.to_level IS NULL THEN
1163 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1164 fnd_message.set_name('OZF', 'OZF_INVALID_TO_LEVEL');
1165 fnd_msg_pub.add;
1166 END IF;
1167 x_return_status := fnd_api.g_ret_sts_error;
1168 RETURN;
1169 END IF;
1170 END IF;
1171
1172 --if start node is null, then raise exception
1173 IF p_alloc_rec.start_node = fnd_api.g_miss_num OR p_alloc_rec.start_node IS NULL THEN
1174 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1175 fnd_message.set_name('OZF', 'OZF_NO_START_NODE');
1176 fnd_msg_pub.add;
1177 END IF;
1178 x_return_status := fnd_api.g_ret_sts_error;
1179 RETURN;
1180 ELSE
1181 --check if the start node is valid
1182 OPEN c_start_node_exists (p_alloc_rec.hierarchy_id, p_alloc_rec.from_level);
1183 FETCH c_start_node_exists INTO p_alloc_rec.start_node;
1184 CLOSE c_start_node_exists;
1185
1186 IF p_alloc_rec.start_node IS NULL THEN
1187 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1188 fnd_message.set_name('OZF', 'OZF_INVALID_START_NODE');
1189 fnd_msg_pub.add;
1190 END IF;
1191 x_return_status := fnd_api.g_ret_sts_error;
1192 RETURN;
1193 END IF;
1194 END IF;
1195
1196 --if start period name is null
1197 IF p_alloc_rec.start_period_name = fnd_api.g_miss_char OR p_alloc_rec.start_period_name IS NULL THEN
1198 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1199 fnd_message.set_name('OZF', 'OZF_NO_START_PERIOD');
1200 fnd_msg_pub.add;
1201 END IF;
1202 x_return_status := fnd_api.g_ret_sts_error;
1203 RETURN;
1204 ELSE
1205 --check if the input start period name is valid
1206 OPEN c_period_exists (p_alloc_rec.start_period_name);
1207 FETCH c_period_exists INTO l_period_exists;
1208 CLOSE c_period_exists;
1209
1210 IF l_period_exists IS NULL THEN
1211 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1212 fnd_message.set_name('OZF', 'OZF_INVALID_START_PERIOD');
1213 fnd_msg_pub.add;
1214 END IF;
1215 x_return_status := fnd_api.g_ret_sts_error;
1216 RETURN;
1217 END IF;
1218 l_period_exists := NULL;
1219 END IF;
1220
1221 --if end period name is null
1222 IF p_alloc_rec.end_period_name = fnd_api.g_miss_char OR p_alloc_rec.end_period_name IS NULL THEN
1223 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1224 fnd_message.set_name('OZF', 'OZF_NO_END_PERIOD');
1225 fnd_msg_pub.add;
1226 END IF;
1227 x_return_status := fnd_api.g_ret_sts_error;
1228 RETURN;
1229 ELSE
1230 --check if the input end period name is valid
1231 OPEN c_period_exists (p_alloc_rec.end_period_name);
1232 FETCH c_period_exists INTO l_period_exists;
1233 CLOSE c_period_exists;
1234
1235 IF l_period_exists IS NULL THEN
1236 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1237 fnd_message.set_name('OZF', 'OZF_INVALID_END_PERIOD');
1238 fnd_msg_pub.add;
1239 END IF;
1240 x_return_status := fnd_api.g_ret_sts_error;
1241 RETURN;
1242 END IF;
1243 END IF;
1244
1245 OPEN c_from_date (p_alloc_rec.start_period_name);
1246 FETCH c_from_date INTO p_alloc_rec.from_date;
1247 CLOSE c_from_date;
1248
1249 OPEN c_to_date (p_alloc_rec.end_period_name);
1250 FETCH c_to_date INTO p_alloc_rec.to_date;
1251 CLOSE c_to_date;
1252
1253 --if allocation amount is null, then raise exception
1254 IF p_alloc_rec.alloc_amount = fnd_api.g_miss_num OR p_alloc_rec.alloc_amount IS NULL THEN
1255 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1256 fnd_message.set_name('OZF', 'OZF_NO_ALLOC_AMT');
1257 fnd_msg_pub.add;
1258 END IF;
1259 x_return_status := fnd_api.g_ret_sts_error;
1260 RETURN;
1261 END IF;
1262
1263 --if method code is null, then raise exception
1264 IF p_alloc_rec.method_code = fnd_api.g_miss_char OR p_alloc_rec.method_code IS NULL THEN
1265 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1266 fnd_message.set_name('OZF', 'OZF_NO_METHOD_CODE');
1267 fnd_msg_pub.add;
1268 END IF;
1269 x_return_status := fnd_api.g_ret_sts_error;
1270 RETURN;
1271 ELSE
1272 --check if the method code is valid
1273 OPEN c_method_code_exists (p_alloc_rec.method_code);
1274 FETCH c_method_code_exists INTO p_alloc_rec.method_code;
1275 CLOSE c_method_code_exists;
1276
1277 IF p_alloc_rec.method_code IS NULL THEN
1278 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1279 fnd_message.set_name('OZF', 'OZF_INVALID_METHOD_CODE');
1280 fnd_msg_pub.add;
1281 END IF;
1282 x_return_status := fnd_api.g_ret_sts_error;
1283 RETURN;
1284 END IF;
1285 END IF;
1286
1287 --if basis year is null, then raise exception
1288 IF p_alloc_rec.basis_year = fnd_api.g_miss_num OR p_alloc_rec.basis_year IS NULL
1289 AND p_alloc_rec.method_code = 'PRIOR_SALES_TOTAL' THEN
1290 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1291 fnd_message.set_name('OZF', 'OZF_NO_BASIS_YEAR');
1292 fnd_msg_pub.add;
1293 END IF;
1294 x_return_status := fnd_api.g_ret_sts_error;
1295 RETURN;
1296 END IF;
1297
1298 OPEN c_product_spread (p_alloc_rec.quota_id);
1299 FETCH c_product_spread INTO p_alloc_rec.product_spread_time_id;
1300 CLOSE c_product_spread;
1301
1302 IF G_DEBUG THEN
1303 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name);
1304 END IF;
1305
1306 END validate_alloc_attributes;
1307
1308 FUNCTION get_product_allocation_id
1309 RETURN NUMBER IS
1310 l_api_version CONSTANT NUMBER := 1.0;
1311 l_api_name CONSTANT VARCHAR2(30) := 'get_product_allocation_id';
1312 l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1313
1314 CURSOR product_seq_csr IS
1315 SELECT ozf_product_allocations_s.NEXTVAL
1316 FROM DUAL;
1317
1318 CURSOR product_alloc_count_csr(p_product_alloc_id in number) IS
1319 SELECT count(p.product_allocation_id)
1320 FROM ozf_product_allocations p
1321 WHERE p.product_allocation_id = p_product_alloc_id;
1322
1323 l_count number := -1;
1324 l_product_alloc_id number := -1;
1325
1326 BEGIN
1327
1328 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
1329
1330 OPEN product_seq_csr;
1331 FETCH product_seq_csr INTO l_product_alloc_id;
1332 CLOSE product_seq_csr;
1333
1334 LOOP
1335 OPEN product_alloc_count_csr(l_product_alloc_id);
1336 FETCH product_alloc_count_csr into l_count;
1337 CLOSE product_alloc_count_csr;
1338
1339 EXIT WHEN l_count = 0;
1340
1341 OPEN product_seq_csr;
1342 FETCH product_seq_csr INTO l_product_alloc_id;
1343 CLOSE product_seq_csr;
1344
1345 END LOOP;
1346
1347 return l_product_alloc_id;
1348
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
1352 END get_product_allocation_id;
1353
1354 FUNCTION get_time_allocation_id
1355 RETURN NUMBER IS
1356 l_api_version CONSTANT NUMBER := 1.0;
1357 l_api_name CONSTANT VARCHAR2(30) := 'get_time_allocation_id';
1358 l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1359
1360 CURSOR time_seq_csr IS
1361 SELECT ozf_time_allocations_s.NEXTVAL
1362 FROM DUAL;
1363
1364 CURSOR time_alloc_count_csr(p_time_alloc_id in number) IS
1365 SELECT count(t.time_allocation_id)
1366 FROM ozf_time_allocations t
1367 WHERE t.time_allocation_id = p_time_alloc_id;
1368
1369 l_count number := -1;
1370 l_time_alloc_id number := -1;
1371
1372 BEGIN
1373
1374 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
1375
1376 OPEN time_seq_csr;
1377 FETCH time_seq_csr INTO l_time_alloc_id;
1378 CLOSE time_seq_csr;
1379
1380 LOOP
1381 OPEN time_alloc_count_csr(l_time_alloc_id);
1382 FETCH time_alloc_count_csr into l_count;
1383 CLOSE time_alloc_count_csr;
1384
1385 EXIT WHEN l_count = 0;
1386
1387 OPEN time_seq_csr;
1388 FETCH time_seq_csr INTO l_time_alloc_id;
1389 CLOSE time_seq_csr;
1390
1391 END LOOP;
1392
1393 return l_time_alloc_id;
1394
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
1398 END get_time_allocation_id;
1399
1400 FUNCTION get_account_allocation_id
1401 RETURN NUMBER IS
1402 l_api_version CONSTANT NUMBER := 1.0;
1403 l_api_name CONSTANT VARCHAR2(30) := 'get_account_allocation_id';
1404 l_full_api_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1405
1406 CURSOR account_seq_csr IS
1407 SELECT ozf_account_allocations_s.NEXTVAL
1408 FROM DUAL;
1409
1410 CURSOR account_alloc_count_csr(p_account_alloc_id in number) IS
1411 SELECT count(account_allocation_id)
1412 FROM ozf_account_allocations
1413 WHERE account_allocation_id = p_account_alloc_id;
1414
1415 l_count number := -1;
1416 l_account_alloc_id number := -1;
1417
1418 BEGIN
1419
1420 --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
1421
1422 OPEN account_seq_csr;
1423 FETCH account_seq_csr INTO l_account_alloc_id;
1424 CLOSE account_seq_csr;
1425
1426 LOOP
1427 OPEN account_alloc_count_csr(l_account_alloc_id);
1428 FETCH account_alloc_count_csr into l_count;
1429 CLOSE account_alloc_count_csr;
1430
1431 EXIT WHEN l_count = 0;
1432
1433 OPEN account_seq_csr;
1434 FETCH account_seq_csr INTO l_account_alloc_id;
1435 CLOSE account_seq_csr;
1436
1437 END LOOP;
1438
1439 return l_account_alloc_id;
1440
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
1444 END get_account_allocation_id;
1445
1446 -- This procedure can be called to create product allocations
1447 -- to a Quota or an Account
1448 PROCEDURE Create_Product_Alloc_Record(
1449 p_api_version IN NUMBER
1450 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1451 ,p_commit IN VARCHAR2 := fnd_api.g_false
1452 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1453 ,x_return_status OUT NOCOPY VARCHAR2
1454 ,x_msg_count OUT NOCOPY NUMBER
1455 ,x_msg_data OUT NOCOPY VARCHAR2
1456 ,p_product_alloc_rec IN ozf_product_allocations%ROWTYPE
1457 ,x_product_allocation_id OUT NOCOPY NUMBER )
1458 IS
1459
1460 l_api_name VARCHAR(30) := 'Create_Product_Alloc_Record';
1461
1462 l_product_allocation_id NUMBER;
1463 l_org_id NUMBER;
1464 l_object_version_number NUMBER := 1;
1465
1466 BEGIN
1467
1468 IF G_DEBUG THEN
1469 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name);
1470 END IF;
1471
1472 x_return_status := FND_API.G_RET_STS_SUCCESS;
1473
1474 l_product_allocation_id := get_product_allocation_id;
1475
1476 -- Call the Insert here
1477 Ozf_Product_Allocations_Pkg.Insert_Row(
1478 px_product_allocation_id => l_product_allocation_id,
1479 p_allocation_for => p_product_alloc_rec.allocation_for,
1480 p_allocation_for_id => p_product_alloc_rec.allocation_for_id,
1481 p_fund_id => p_product_alloc_rec.fund_id,
1482 p_item_type => p_product_alloc_rec.item_type,
1483 p_item_id => p_product_alloc_rec.item_id,
1484 p_selected_flag => p_product_alloc_rec.selected_flag,
1485 p_target => NVL(p_product_alloc_rec.target, 0),
1486 p_lysp_sales => NVL(p_product_alloc_rec.lysp_sales, 0),
1487 p_parent_product_allocation_id => p_product_alloc_rec.parent_product_allocation_id,
1488 px_object_version_number => l_object_version_number,
1489 p_creation_date => SYSDATE,
1490 p_created_by => FND_GLOBAL.USER_ID,
1491 p_last_update_date => SYSDATE,
1492 p_last_updated_by => FND_GLOBAL.USER_ID,
1493 p_last_update_login => FND_GLOBAL.conc_login_id,
1494 p_attribute_category => p_product_alloc_rec.attribute_category,
1495 p_attribute1 => p_product_alloc_rec.attribute1,
1496 p_attribute2 => p_product_alloc_rec.attribute2,
1497 p_attribute3 => p_product_alloc_rec.attribute3,
1498 p_attribute4 => p_product_alloc_rec.attribute4,
1499 p_attribute5 => p_product_alloc_rec.attribute5,
1500 p_attribute6 => p_product_alloc_rec.attribute6,
1501 p_attribute7 => p_product_alloc_rec.attribute7,
1502 p_attribute8 => p_product_alloc_rec.attribute8,
1503 p_attribute9 => p_product_alloc_rec.attribute9,
1504 p_attribute10 => p_product_alloc_rec.attribute10,
1505 p_attribute11 => p_product_alloc_rec.attribute11,
1506 p_attribute12 => p_product_alloc_rec.attribute12,
1507 p_attribute13 => p_product_alloc_rec.attribute13,
1508 p_attribute14 => p_product_alloc_rec.attribute14,
1509 p_attribute15 => p_product_alloc_rec.attribute15,
1510 px_org_id => l_org_id
1511 );
1512
1513 -- If succesfull, set the out variables
1514 -- If there is a error, calling routine will handle it
1515 x_product_allocation_Id := l_product_allocation_id;
1516 x_return_status := FND_API.G_RET_STS_SUCCESS;
1517
1518 IF G_DEBUG THEN
1519 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name);
1520 END IF;
1521
1522 EXCEPTION
1523 WHEN OTHERS THEN
1524 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1525 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1526 THEN
1527 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Product_Alloc_Record');
1528 END IF;
1529
1530 END Create_Product_Alloc_Record;
1531
1532 PROCEDURE Create_Account_Alloc_Record(
1533 p_api_version IN NUMBER
1534 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1535 ,p_commit IN VARCHAR2 := fnd_api.g_false
1536 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1537 ,x_return_status OUT NOCOPY VARCHAR2
1538 ,x_msg_count OUT NOCOPY NUMBER
1539 ,x_msg_data OUT NOCOPY VARCHAR2
1540 ,p_account_alloc_rec IN ozf_account_allocations%ROWTYPE
1541 ,x_account_allocation_id OUT NOCOPY NUMBER )
1542 IS
1543 l_api_name VARCHAR(30) := 'Create_Account_Alloc_Record';
1544
1545 l_account_allocation_id NUMBER;
1546 l_org_id NUMBER;
1547 l_object_version_number NUMBER := 1;
1548
1549 BEGIN
1550
1551 IF G_DEBUG THEN
1552 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name);
1553 END IF;
1554
1555 x_return_status := FND_API.G_RET_STS_SUCCESS;
1556
1557 -- Call the Insert here
1558 l_account_allocation_id := get_account_allocation_id;
1559
1560 Ozf_Account_Allocations_Pkg.Insert_Row(
1561 px_Account_allocation_id => l_account_allocation_id,
1562 p_allocation_for => p_account_alloc_rec.allocation_for,
1563 p_allocation_for_id => p_account_alloc_rec.allocation_for_id,
1564 p_cust_account_id => p_account_alloc_rec.cust_account_id,
1565 p_site_use_id => p_account_alloc_rec.site_use_id,
1566 p_site_use_code => 'SHIP_TO',
1567 p_location_id => p_account_alloc_rec.location_id,
1568 p_bill_to_site_use_id => p_account_alloc_rec.bill_to_site_use_id,
1569 p_bill_to_location_id => p_account_alloc_rec.bill_to_location_id,
1570 p_parent_party_id => p_account_alloc_rec.parent_party_id,
1571 p_rollup_party_id => p_account_alloc_rec.rollup_party_id,
1572 p_selected_flag => p_account_alloc_rec.selected_flag,
1573 p_target => p_account_alloc_rec.target,
1574 p_lysp_sales => p_account_alloc_rec.lysp_sales,
1575 p_parent_Account_allocation_id => p_account_alloc_rec.parent_Account_allocation_id,
1576 px_object_version_number => l_object_version_number,
1577 p_creation_date => SYSDATE,
1578 p_created_by => FND_GLOBAL.USER_ID,
1579 p_last_update_date => SYSDATE,
1580 p_last_updated_by => FND_GLOBAL.USER_ID,
1581 p_last_update_login => FND_GLOBAL.conc_login_id,
1582 p_attribute_category => p_account_alloc_rec.attribute_category,
1583 p_attribute1 => p_account_alloc_rec.attribute1,
1584 p_attribute2 => p_account_alloc_rec.attribute2,
1585 p_attribute3 => p_account_alloc_rec.attribute3,
1586 p_attribute4 => p_account_alloc_rec.attribute4,
1587 p_attribute5 => p_account_alloc_rec.attribute5,
1588 p_attribute6 => p_account_alloc_rec.attribute6,
1589 p_attribute7 => p_account_alloc_rec.attribute7,
1590 p_attribute8 => p_account_alloc_rec.attribute8,
1591 p_attribute9 => p_account_alloc_rec.attribute9,
1592 p_attribute10 => p_account_alloc_rec.attribute10,
1593 p_attribute11 => p_account_alloc_rec.attribute11,
1594 p_attribute12 => p_account_alloc_rec.attribute12,
1595 p_attribute13 => p_account_alloc_rec.attribute13,
1596 p_attribute14 => p_account_alloc_rec.attribute14,
1597 p_attribute15 => p_account_alloc_rec.attribute15,
1598 px_org_id => l_org_id
1599 );
1600
1601
1602 -- If succesfull, set the out variables
1603 -- If there is a error, calling routine will handle it
1604 x_account_allocation_Id := l_account_allocation_id;
1605 x_return_status := FND_API.G_RET_STS_SUCCESS;
1606
1607 IF G_DEBUG THEN
1608 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name);
1609 END IF;
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1614 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1615 THEN
1616 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Account_Alloc_Record');
1617 END IF;
1618
1619 END Create_Account_Alloc_Record;
1620
1621
1622 PROCEDURE Create_Time_Alloc_Record(
1623 p_api_version IN NUMBER
1624 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1625 ,p_commit IN VARCHAR2 := fnd_api.g_false
1626 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1627 ,x_return_status OUT NOCOPY VARCHAR2
1628 ,x_msg_count OUT NOCOPY NUMBER
1629 ,x_msg_data OUT NOCOPY VARCHAR2
1630 ,p_time_alloc_rec IN ozf_time_allocations%ROWTYPE
1631 ,x_time_allocation_id OUT NOCOPY NUMBER )
1632 IS
1633
1634 l_api_name VARCHAR(30) := 'Create_Time_Alloc_Record';
1635
1636 l_time_allocation_id NUMBER;
1637 l_org_id NUMBER;
1638 l_object_version_number NUMBER := 1;
1639
1640 BEGIN
1641 IF G_DEBUG THEN
1642 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name);
1643 END IF;
1644
1645 x_return_status := FND_API.G_RET_STS_SUCCESS;
1646
1647 l_time_allocation_id := get_time_allocation_id;
1648 -- Call the Insert here
1649
1650 Ozf_Time_Allocations_Pkg.Insert_Row(
1651 px_time_allocation_id => l_time_allocation_id,
1652 p_allocation_for => p_time_alloc_rec.allocation_for,
1653 p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
1654 p_time_id => p_time_alloc_rec.time_id,
1655 p_period_type_id => p_time_alloc_rec.period_type_id,
1656 p_target => NVL(p_time_alloc_rec.target, 0),
1657 p_lysp_sales => NVL(p_time_alloc_rec.lysp_sales, 0),
1658 px_object_version_number => l_object_version_number,
1659 p_creation_date => SYSDATE,
1660 p_created_by => FND_GLOBAL.USER_ID,
1661 p_last_update_date => SYSDATE,
1662 p_last_updated_by => FND_GLOBAL.USER_ID,
1663 p_last_update_login => FND_GLOBAL.conc_login_id,
1664 p_attribute_category => p_time_alloc_rec.attribute_category,
1665 p_attribute1 => p_time_alloc_rec.attribute1,
1666 p_attribute2 => p_time_alloc_rec.attribute2,
1667 p_attribute3 => p_time_alloc_rec.attribute3,
1668 p_attribute4 => p_time_alloc_rec.attribute4,
1669 p_attribute5 => p_time_alloc_rec.attribute5,
1670 p_attribute6 => p_time_alloc_rec.attribute6,
1671 p_attribute7 => p_time_alloc_rec.attribute7,
1672 p_attribute8 => p_time_alloc_rec.attribute8,
1673 p_attribute9 => p_time_alloc_rec.attribute9,
1674 p_attribute10 => p_time_alloc_rec.attribute10,
1675 p_attribute11 => p_time_alloc_rec.attribute11,
1676 p_attribute12 => p_time_alloc_rec.attribute12,
1677 p_attribute13 => p_time_alloc_rec.attribute13,
1678 p_attribute14 => p_time_alloc_rec.attribute14,
1679 p_attribute15 => p_time_alloc_rec.attribute15,
1680 px_org_id => l_org_id
1681 );
1682
1683 -- If succesfull, set the out variables
1684 -- If there is a error, calling routine will handle it
1685 x_time_allocation_Id := l_time_allocation_id;
1686 x_return_status := FND_API.G_RET_STS_SUCCESS;
1687
1688 IF G_DEBUG THEN
1689 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name);
1690 END IF;
1691
1692 EXCEPTION
1693
1694 WHEN OTHERS THEN
1695 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1696 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1697 THEN
1698 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Others exception in Create_Time_Alloc_Record');
1699 END IF;
1700
1701 END Create_Time_Alloc_Record;
1702
1703
1704
1705 -- This should be a public procedure.
1706 -- Users should be able to create add new Product Spread Records to
1707 -- Existing Quotas
1708 PROCEDURE Create_Quota_Product_Spread(
1709 p_api_version IN NUMBER
1710 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1711 ,p_commit IN VARCHAR2 := fnd_api.g_false
1712 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1713 ,x_return_status OUT NOCOPY VARCHAR2
1714 ,x_msg_count OUT NOCOPY NUMBER
1715 ,x_msg_data OUT NOCOPY VARCHAR2
1716 ,p_allocation_for IN VARCHAR2
1717 ,p_allocation_for_id IN NUMBER
1718 ,p_quota_products_tbl IN quota_products_tbl_type
1719 ,p_quota_prod_spread_tbl IN quota_prod_spread_tbl_type )
1720 IS
1721
1722 l_api_name VARCHAR(30) := 'create_quota_product_spread';
1723 l_product_alloc_rec ozf_product_allocations%ROWTYPE;
1724 l_time_alloc_rec ozf_time_allocations%ROWTYPE;
1725
1726 l_product_allocation_id NUMBER;
1727 l_time_allocation_id NUMBER;
1728 l_object_version_number NUMBER := 1;
1729
1730 BEGIN
1731 --
1732 SAVEPOINT CREATE_QUOTA_PRODUCT_SPREAD;
1733
1734 IF G_DEBUG THEN
1735 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name );
1736 END IF;
1737
1738 -- Initialize API return status to success
1739 x_return_status := FND_API.G_RET_STS_SUCCESS;
1740
1741 -- p_quota_products_tbl will already have record by now
1742
1743 IF (p_quota_prod_spread_tbl.count = 0 ) Then
1744 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1745 FND_MESSAGE.Set_Name('OZF', 'OZF_QUOTA_MISSING_PROD_SPREAD');
1746 FND_MSG_PUB.ADD;
1747 END IF;
1748 x_return_status := FND_API.G_RET_STS_ERROR;
1749 RAISE FND_API.G_EXC_ERROR;
1750 END IF;
1751
1752 FOR l_product_counter IN 1..p_quota_products_tbl.count
1753 LOOP
1754 -- Insert record into ozf_product_allocations
1755 l_product_alloc_rec := NULL;
1756
1757 IF p_allocation_for IS NOT NULL
1758 THEN
1759 l_product_alloc_rec.allocation_for := p_allocation_for;
1760 END IF;
1761
1762 IF p_allocation_for_id IS NOT NULL
1763 THEN
1764 l_product_alloc_rec.allocation_for_id := p_allocation_for_id;
1765 END IF;
1766
1767 IF p_allocation_for = 'FUND'
1768 THEN
1769 l_product_alloc_rec.fund_id := p_allocation_for_id;
1770 END IF;
1771
1772 IF ( p_quota_products_tbl(l_product_counter).item_type = 'FAMILY')
1773 THEN
1774 l_product_alloc_rec.item_type := 'PRICING_ATTRIBUTE2';
1775 ELSIF ( p_quota_products_tbl(l_product_counter).item_type = 'PRODUCT')
1776 THEN
1777 l_product_alloc_rec.item_type := 'PRICING_ATTRIBUTE1';
1778 ELSE
1779 l_product_alloc_rec.item_type := 'OTHERS';
1780 END IF;
1781
1782 l_product_alloc_rec.item_id := p_quota_products_tbl(l_product_counter).item_id;
1783 l_product_alloc_rec.selected_flag := 'N';
1784 l_product_alloc_rec.target := NVL(p_quota_products_tbl(l_product_counter).target, 0) ;
1785 l_product_alloc_rec.lysp_sales := NVL(p_quota_products_tbl(l_product_counter).lysp_sales,0);
1786
1787 Create_Product_Alloc_Record(
1788 p_api_version => p_api_version
1789 ,p_init_msg_list => p_init_msg_list
1790 ,p_commit => p_commit
1791 ,p_validation_level => p_validation_level
1792 ,x_return_status => x_return_status
1793 ,x_msg_count => x_msg_count
1794 ,x_msg_data => x_msg_data
1795 ,p_product_alloc_rec => l_product_alloc_rec
1796 ,x_product_allocation_id => l_product_allocation_id ) ;
1797
1798 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1799 RAISE FND_API.g_exc_unexpected_error;
1800 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1801 RAISE FND_API.g_exc_unexpected_error;
1802 END IF;
1803
1804 /* Target Spread Record Type
1805 time_allocation_id NUMBER
1806 allocation_for VARCHAR2(30)
1807 allocation_for_id NUMBER
1808 allocation_for_tbl_index NUMBER
1809 time_id NUMBER
1810 period_type_id NUMBER
1811 target NUMBER
1812 lysp_sales NUMBER);
1813 */
1814
1815 FOR l_TimeSpread_Counter IN p_quota_prod_spread_tbl.first..p_quota_prod_spread_tbl.last
1816 LOOP
1817 --
1818 -- Create time spread record for the corresponding product record
1819 IF p_quota_prod_spread_tbl(l_TimeSpread_Counter).allocation_for_tbl_index = l_product_counter
1820 THEN
1821
1822 l_time_alloc_rec := NULL;
1823
1824 l_time_alloc_rec.allocation_for := 'PROD';
1825 l_time_alloc_rec.allocation_for_id := l_product_allocation_id;
1826 l_time_alloc_rec.time_id := p_quota_prod_spread_tbl(l_TimeSpread_Counter).time_id;
1827 l_time_alloc_rec.period_type_id := p_quota_prod_spread_tbl(l_TimeSpread_Counter).period_type_id;
1828 l_time_alloc_rec.target := p_quota_prod_spread_tbl(l_TimeSpread_Counter).target;
1829 l_time_alloc_rec.lysp_sales := p_quota_prod_spread_tbl(l_TimeSpread_Counter).lysp_sales;
1830
1831 validate_time_period (l_time_alloc_rec.period_type_id,
1832 l_time_alloc_rec.time_id ,
1833 x_return_status);
1834
1835 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1836 RAISE FND_API.g_exc_unexpected_error;
1837 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1838 RAISE FND_API.g_exc_unexpected_error;
1839 END IF;
1840
1841 Create_Time_Alloc_Record(
1842 p_api_version => p_api_version
1843 ,p_init_msg_list => p_init_msg_list
1844 ,p_commit => p_commit
1845 ,p_validation_level => p_validation_level
1846 ,x_return_status => x_return_status
1847 ,x_msg_count => x_msg_count
1848 ,x_msg_data => x_msg_data
1849 ,p_time_alloc_rec => l_time_alloc_rec
1850 ,x_time_allocation_id => l_time_allocation_id ) ;
1851
1852 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1853 RAISE FND_API.g_exc_unexpected_error;
1854 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1855 RAISE FND_API.g_exc_unexpected_error;
1856 END IF;
1857
1858 END IF;
1859 --
1860 END LOOP; -- Time counter
1861
1862 END LOOP; -- Product counter
1863 --
1864
1865 IF G_DEBUG THEN
1866 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name );
1867 END IF;
1868
1869 FND_MSG_PUB.Count_And_Get (
1870 p_encoded => FND_API.G_FALSE,
1871 p_count => x_msg_count,
1872 p_data => x_msg_data
1873 );
1874
1875 EXCEPTION
1876 WHEN FND_API.G_EXC_ERROR THEN
1877 ROLLBACK TO create_quota_product_spread;
1878 x_return_status := FND_API.G_RET_STS_ERROR;
1879 -- Standard call to get message count and if count=1, get the message
1880 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1881 ,p_count => x_msg_count
1882 ,p_data => x_msg_data
1883 );
1884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1885 ROLLBACK TO create_quota_product_spread;
1886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1887 -- Standard call to get message count and if count=1, get the message
1888 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1889 ,p_count => x_msg_count
1890 ,p_data => x_msg_data
1891 );
1892 WHEN OTHERS THEN
1893 ROLLBACK TO create_quota_product_spread;
1894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1895 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1896 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1897 END IF;
1898 -- Standard call to get message count and if count=1, get the message
1899 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
1900 ,p_count => x_msg_count
1901 ,p_data => x_msg_data
1902 );
1903
1904 END Create_Quota_Product_Spread;
1905
1906 PROCEDURE Create_Quota_Account_Spread(
1907 p_api_version IN NUMBER
1908 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1909 ,p_commit IN VARCHAR2 := fnd_api.g_false
1910 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1911 ,x_return_status OUT NOCOPY VARCHAR2
1912 ,x_msg_count OUT NOCOPY NUMBER
1913 ,x_msg_data OUT NOCOPY VARCHAR2
1914 ,p_fund_id IN NUMBER
1915 ,p_quota_accounts_tbl IN quota_accounts_tbl_type
1916 ,p_account_spread_tbl IN account_spread_tbl_type
1917 ,p_account_products_tbl IN account_products_tbl_type
1918 ,p_acct_prod_spread_tbl IN acct_prod_spread_tbl_type )
1919 IS
1920
1921 l_quota_accounts_tbl quota_accounts_tbl_type := p_quota_accounts_tbl;
1922 l_account_spread_tbl account_spread_tbl_type := p_account_spread_tbl;
1923 l_account_products_tbl account_products_tbl_type := p_account_products_tbl;
1924 l_acct_prod_spread_tbl acct_prod_spread_tbl_type := p_acct_prod_spread_tbl;
1925
1926 l_account_allocation_id NUMBER;
1927 l_time_allocation_id NUMBER;
1928 l_account_alloc_rec ozf_account_allocations%ROWTYPE;
1929 l_time_alloc_rec ozf_time_allocations%ROWTYPE;
1930
1931 l_prod_acct_index NUMBER;
1932 l_prod_sprd_index NUMBER ;
1933 l_prod_for_this_acct_tbl quota_products_tbl_type;
1934 l_prod_sprd_for_this_acct_tbl quota_prod_spread_tbl_type;
1935
1936 l_ship_to_site_use_id NUMBER;
1937 l_cust_account_id NUMBER;
1938 l_location_id NUMBER;
1939 l_bill_to_site_use_id NUMBER;
1940 l_bill_to_location_id NUMBER;
1941 l_parent_party_id NUMBER;
1942 l_rollup_party_id NUMBER;
1943
1944 CURSOR c_site_info (p_site_use_id NUMBER,
1945 p_site_use_code VARCHAR2)
1946 IS
1947 SELECT party_site.party_id,
1948 acct_site.cust_account_id,
1949 party_site.location_id,
1950 site_use.bill_to_site_use_id
1951 FROM hz_cust_acct_sites_all acct_site,
1952 hz_party_sites party_site,
1953 hz_cust_site_uses_all site_use
1954 WHERE site_use.site_use_id = p_site_use_id
1955 AND site_use.site_use_code = p_site_use_code
1956 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
1957 AND acct_site.party_site_id = party_site.party_site_id ;
1958
1959 BEGIN
1960 --
1961 SAVEPOINT create_quota_account_spread;
1962 -- Initialize API return status to success
1963 x_return_status := FND_API.G_RET_STS_SUCCESS;
1964
1965 IF (p_quota_accounts_tbl.count = 0 ) Then
1966 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1967 FND_MESSAGE.Set_Name('OZF', 'OZF_QUOTA_MISSING_ACCOUNTS');
1968 FND_MSG_PUB.ADD;
1969 END IF;
1970 x_return_status := FND_API.G_RET_STS_ERROR;
1971 RAISE FND_API.G_EXC_ERROR;
1972 END IF;
1973
1974 IF (p_account_spread_tbl.count = 0 ) Then
1975 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1976 FND_MESSAGE.Set_Name('OZF', 'OZF_QUOTA_MISSING_ACCT_SPREAD');
1977 FND_MSG_PUB.ADD;
1978 END IF;
1979 x_return_status := FND_API.G_RET_STS_ERROR;
1980 RAISE FND_API.G_EXC_ERROR;
1981 END IF;
1982
1983 FOR l_account_counter IN 1..p_quota_accounts_tbl.count
1984 LOOP
1985 --
1986 l_account_alloc_rec := NULL;
1987
1988 l_account_alloc_rec.allocation_for := 'FUND';
1989 l_account_alloc_rec.allocation_for_id := p_fund_id;
1990 l_account_alloc_rec.selected_flag := p_quota_accounts_tbl(l_account_counter).selected_flag;
1991 l_ship_to_site_use_id := p_quota_accounts_tbl(l_account_counter).ship_to_site_use_id;
1992
1993 IF l_ship_to_site_use_id <> -9999
1994 THEN
1995 --
1996 OPEN c_site_info(l_ship_to_site_use_id,'SHIP_TO');
1997 FETCH c_site_info INTO l_rollup_party_id,l_cust_account_id,l_location_id,l_bill_to_site_use_id;
1998 CLOSE c_site_info;
1999 --
2000 ELSE
2001 -- UNALLOC Record
2002 l_cust_account_id := -9999;
2003 l_bill_to_site_use_id := -9999;
2004 l_rollup_party_id := -9999;
2005 --
2006 END IF;
2007
2008 IF (l_rollup_party_id IS NULL) Then
2009 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2010 FND_MESSAGE.Set_Name('OZF', 'OZF_QUOTA_INVALID_SHIP_TO');
2011 FND_MSG_PUB.ADD;
2012 END IF;
2013 x_return_status := FND_API.G_RET_STS_ERROR;
2014 RAISE FND_API.G_EXC_ERROR;
2015 END IF;
2016
2017 l_account_alloc_rec.cust_account_id := l_cust_account_id;
2018 l_account_alloc_rec.location_id := l_location_id;
2019 l_account_alloc_rec.bill_to_site_use_id := l_bill_to_site_use_id;
2020 l_account_alloc_rec.rollup_party_id := l_rollup_party_id;
2021
2022 IF l_bill_to_site_use_id IS NOT NULL
2023 THEN
2024
2025 IF l_bill_to_site_use_id <> -9999
2026 THEN
2027 --
2028 OPEN c_site_info(l_bill_to_site_use_id,'BILL_TO');
2029 FETCH c_site_info INTO l_parent_party_id,l_cust_account_id,l_bill_to_location_id,l_bill_to_site_use_id;
2030 CLOSE c_site_info;
2031 l_account_alloc_rec.bill_to_location_id := l_bill_to_location_id;
2032 l_account_alloc_rec.parent_party_id := l_parent_party_id;
2033 --
2034 ELSE
2035 --
2036 l_account_alloc_rec.parent_party_id := -9999;
2037 --
2038 END IF;
2039
2040 END IF;
2041
2042 l_account_alloc_rec.site_use_id := p_quota_accounts_tbl(l_account_counter).ship_to_site_use_id;
2043 l_account_alloc_rec.target := p_quota_accounts_tbl(l_account_counter).target;
2044 l_account_alloc_rec.lysp_sales := p_quota_accounts_tbl(l_account_counter).lysp_sales;
2045
2046 Create_Account_Alloc_Record(
2047 p_api_version => p_api_version
2048 ,p_init_msg_list => p_init_msg_list
2049 ,p_commit => p_commit
2050 ,p_validation_level => p_validation_level
2051 ,x_return_status => x_return_status
2052 ,x_msg_count => x_msg_count
2053 ,x_msg_data => x_msg_data
2054 ,p_account_alloc_rec => l_account_alloc_rec
2055 ,x_account_allocation_id => l_account_allocation_id ) ;
2056
2057 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2058 RAISE FND_API.g_exc_unexpected_error;
2059 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2060 RAISE FND_API.g_exc_unexpected_error;
2061 END IF;
2062
2063 IF l_account_alloc_rec.parent_party_id = -9999
2064 THEN
2065 -- This is the UNALLOC record. It will not have
2066 -- Time Spread or Product Allocation
2067 GOTO NEXT_ACCT;
2068 END IF;
2069
2070 -- Done Creating Account Allocation Record
2071 -- Start Creating Time Spread for the Account Record
2072 FOR l_TimeSpread_Counter IN p_account_spread_tbl.first..p_account_spread_tbl.last
2073 LOOP
2074 --
2075 -- Create time spread record for the corresponding product record
2076 IF p_account_spread_tbl(l_TimeSpread_Counter).allocation_for_tbl_index = l_account_counter
2077 THEN
2078
2079 l_time_alloc_rec := NULL;
2080
2081 l_time_alloc_rec.allocation_for := 'CUST';
2082 l_time_alloc_rec.allocation_for_id := l_account_allocation_id;
2083 l_time_alloc_rec.time_id := p_account_spread_tbl(l_TimeSpread_Counter).time_id;
2084 l_time_alloc_rec.period_type_id := p_account_spread_tbl(l_TimeSpread_Counter).period_type_id;
2085 l_time_alloc_rec.target := p_account_spread_tbl(l_TimeSpread_Counter).target;
2086 l_time_alloc_rec.lysp_sales := p_account_spread_tbl(l_TimeSpread_Counter).lysp_sales;
2087
2088 validate_time_period (l_time_alloc_rec.period_type_id,
2089 l_time_alloc_rec.time_id ,
2090 x_return_status);
2091
2092 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2093 RAISE FND_API.g_exc_unexpected_error;
2094 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2095 RAISE FND_API.g_exc_unexpected_error;
2096 END IF;
2097
2098 Create_Time_Alloc_Record(
2099 p_api_version => p_api_version
2100 ,p_init_msg_list => p_init_msg_list
2101 ,p_commit => p_commit
2102 ,p_validation_level => p_validation_level
2103 ,x_return_status => x_return_status
2104 ,x_msg_count => x_msg_count
2105 ,x_msg_data => x_msg_data
2106 ,p_time_alloc_rec => l_time_alloc_rec
2107 ,x_time_allocation_id => l_time_allocation_id ) ;
2108
2109 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2110 RAISE FND_API.g_exc_unexpected_error;
2111 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2112 RAISE FND_API.g_exc_unexpected_error;
2113 END IF;
2114
2115 END IF;
2116 --
2117 END LOOP; -- Time counter
2118 -- Done Creating Time Spread for the Account
2119
2120 -- Create Account Products and Product Spread if provided
2121 -- Account Counter is l_account_counter
2122 -- account_allocation_id is l_account_allocation_id
2123
2124 IF (p_account_products_tbl.COUNT = 0 OR p_acct_prod_spread_tbl.COUNT = 0 )
2125 THEN
2126 GOTO NEXT_ACCT;
2127 END IF;
2128
2129 l_prod_acct_index := 0;
2130 l_prod_sprd_index := 0;
2131 l_prod_for_this_acct_tbl.DELETE;
2132 l_prod_sprd_for_this_acct_tbl.DELETE;
2133
2134 FOR l_AccountProduct_Counter IN 1..p_account_products_tbl.COUNT
2135 LOOP
2136 --
2137 -- Populate l_prod_for_acct_tbl
2138 IF ( p_account_products_tbl(l_AccountProduct_Counter).allocation_for_tbl_index = l_account_counter )
2139 THEN
2140 --
2141 l_prod_acct_index := l_prod_acct_index + 1;
2142 l_prod_for_this_acct_tbl(l_prod_acct_index) := p_account_products_tbl(l_AccountProduct_Counter);
2143
2144 FOR l_AcctProdSprd_Counter IN 1..p_acct_prod_spread_tbl.COUNT
2145 LOOP
2146 -- Populate l_prod_spread_for_acct_tbl
2147 --
2148 IF (p_acct_prod_spread_tbl(l_AcctProdSprd_Counter).allocation_for_tbl_index = l_AccountProduct_Counter)
2149 THEN
2150 --
2151 l_prod_sprd_index := l_prod_sprd_index + 1;
2152 l_prod_sprd_for_this_acct_tbl(l_prod_sprd_index) := p_acct_prod_spread_tbl(l_AcctProdSprd_Counter);
2153 END IF;
2154 --
2155 END LOOP;
2156 --
2157 END IF; -- End account-product match
2158 --
2159 END LOOP; -- Done scanning products for the account
2160
2161 -- Create Product Spread for the account
2162 Create_Quota_Product_Spread(
2163 p_api_version => p_api_version
2164 ,p_init_msg_list => p_init_msg_list
2165 ,p_commit => p_commit
2166 ,p_validation_level => p_validation_level
2167 ,x_return_status => x_return_status
2168 ,x_msg_count => x_msg_count
2169 ,x_msg_data => x_msg_data
2170 ,p_allocation_for => 'CUST'
2171 ,p_allocation_for_id => l_account_allocation_id
2172 ,p_quota_products_tbl => l_prod_for_this_acct_tbl
2173 ,p_quota_prod_spread_tbl => l_prod_sprd_for_this_acct_tbl );
2174
2175 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2176 RAISE FND_API.g_exc_unexpected_error;
2177 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2178 RAISE FND_API.g_exc_unexpected_error;
2179 END IF;
2180
2181 <<NEXT_ACCT>>
2182 NULL;
2183 END LOOP; -- Process Next Account
2184
2185 --
2186 FND_MSG_PUB.Count_And_Get (
2187 p_encoded => FND_API.G_FALSE,
2188 p_count => x_msg_count,
2189 p_data => x_msg_data
2190 );
2191
2192 EXCEPTION
2193 WHEN FND_API.G_EXC_ERROR THEN
2194 ROLLBACK TO create_quota_account_spread;
2195 x_return_status := FND_API.G_RET_STS_ERROR;
2196 -- Standard call to get message count and if count=1, get the message
2197 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2198 ,p_count => x_msg_count
2199 ,p_data => x_msg_data
2200 );
2201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202 ROLLBACK TO create_quota_account_spread;
2203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2204 -- Standard call to get message count and if count=1, get the message
2205 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2206 ,p_count => x_msg_count
2207 ,p_data => x_msg_data
2208 );
2209 WHEN OTHERS THEN
2210 ROLLBACK TO create_quota_account_spread;
2211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2212 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2213 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,'Create_Quota_Account_Spread');
2214 END IF;
2215 -- Standard call to get message count and if count=1, get the message
2216 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2217 ,p_count => x_msg_count
2218 ,p_data => x_msg_data
2219 );
2220
2221 END Create_Quota_Account_Spread;
2222
2223 ---------------------------------------------------------------------
2224 -- PROCEDURE
2225 -- create_quota
2226 --
2227 -- PURPOSE
2228 -- Create a new quota.
2229 --
2230 -- PARAMETERS
2231 -- p_quota_rec: the new record to be inserted
2232 -- x_quota_id: return the quota_id of the new quota
2233 --
2234 -- HISTORY
2235 -- 06/29/2005 kdass Created
2236 ---------------------------------------------------------------------
2237 PROCEDURE create_quota(
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 ,x_return_status OUT NOCOPY VARCHAR2
2243 ,x_msg_count OUT NOCOPY NUMBER
2244 ,x_msg_data OUT NOCOPY VARCHAR2
2245 ,p_method IN VARCHAR2 := 'MANUAL'
2246 ,p_quota_rec IN quota_rec_type
2247 ,p_quota_markets_tbl IN quota_markets_tbl_type
2248 ,p_quota_products_tbl IN quota_products_tbl_type
2249 ,p_quota_prod_spread_tbl IN quota_prod_spread_tbl_type
2250 ,p_quota_accounts_tbl IN quota_accounts_tbl_type
2251 ,p_account_spread_tbl IN account_spread_tbl_type
2252 ,p_account_products_tbl IN account_products_tbl_type
2253 ,p_acct_prod_spread_tbl IN acct_prod_spread_tbl_type
2254 ,p_alloc_rec IN alloc_rec_type
2255 ,x_quota_id OUT NOCOPY NUMBER
2256 )
2257 IS
2258 l_api_name VARCHAR(30) := 'Create_Quota';
2259 l_mode VARCHAR2(6) := 'CREATE';
2260 l_alloc_id NUMBER;
2261
2262 l_quota_rec quota_rec_type := p_quota_rec;
2263 l_quota_markets_tbl quota_markets_tbl_type := p_quota_markets_tbl;
2264 l_quota_products_tbl quota_products_tbl_type := p_quota_products_tbl;
2265 l_quota_prod_spread_tbl quota_prod_spread_tbl_type := p_quota_prod_spread_tbl;
2266 l_quota_accounts_tbl quota_accounts_tbl_type := p_quota_accounts_tbl;
2267 l_account_spread_tbl account_spread_tbl_type := p_account_spread_tbl;
2268 l_account_products_tbl account_products_tbl_type := p_account_products_tbl;
2269 l_acct_prod_spread_tbl acct_prod_spread_tbl_type := p_acct_prod_spread_tbl;
2270 l_alloc_rec alloc_rec_type := p_alloc_rec;
2271
2272 BEGIN
2273
2274 SAVEPOINT create_quota;
2275
2276 IF G_DEBUG THEN
2277 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name );
2278 END IF;
2279
2280 -- First Validate p_method ---------------------------------------------------
2281 -- Default is 'MANUAL'
2282 IF p_method NOT IN ('MANUAL', 'ALLOCATION')
2283 THEN
2284 --
2285 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2286 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
2287 fnd_message.set_token('COL_NAME', 'P_METHOD');
2288 fnd_msg_pub.add;
2289 END IF;
2290 x_return_status := fnd_api.g_ret_sts_error;
2291 RAISE fnd_api.g_exc_error;
2292 --
2293 END IF;
2294
2295 ------------------------------------------------------------------------------
2296
2297 -- Products are always required
2298 IF (p_quota_products_tbl.COUNT = 0 )
2299 THEN
2300 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2301 FND_MESSAGE.Set_Name('OZF', 'OZF_QUOTA_MISSING_PRODUCTS');
2302 FND_MSG_PUB.ADD;
2303 END IF;
2304 x_return_status := FND_API.G_RET_STS_ERROR;
2305 raise FND_API.G_EXC_ERROR;
2306 END IF;
2307
2308 -- Create_Quota_Header will create quota and associate products
2309 -- These both are always required by any quota
2310
2311 Create_Quota_Header(
2312 p_api_version => p_api_version
2313 ,p_init_msg_list => p_init_msg_list
2314 ,p_commit => p_commit
2315 ,p_validation_level => p_validation_level
2316 ,x_return_status => x_return_status
2317 ,x_msg_count => x_msg_count
2318 ,x_msg_data => x_msg_data
2319 ,p_method => p_method
2320 ,p_mode => l_mode
2321 ,p_quota_rec => l_quota_rec
2322 ,p_quota_markets_tbl => l_quota_markets_tbl
2323 ,p_quota_products_tbl => l_quota_products_tbl
2324 ,x_quota_id => x_quota_id );
2325
2326 IF G_DEBUG THEN
2327 ozf_utility_pvt.debug_message('Quota ID: ' || x_quota_id);
2328 ozf_utility_pvt.debug_message('Return Status: ' || x_return_status);
2329 END IF;
2330
2331 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2332 RAISE FND_API.g_exc_unexpected_error;
2333 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2334 RAISE FND_API.g_exc_unexpected_error;
2335 END IF;
2336
2337 IF p_method = 'ALLOCATION'
2338 THEN
2339 --
2340 generate_product_spread(
2341 p_api_version => p_api_version
2342 ,p_init_msg_list => p_init_msg_list
2343 ,p_commit => p_commit
2344 ,p_validation_level => p_validation_level
2345 ,p_quota_id => x_quota_id
2346 ,x_return_status => x_return_status
2347 ,x_error_number => x_msg_count
2348 ,x_error_message => x_msg_data );
2349
2350 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2351 RAISE FND_API.g_exc_unexpected_error;
2352 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2353 RAISE FND_API.g_exc_unexpected_error;
2354 END IF;
2355
2356 l_alloc_rec.quota_id := x_quota_id;
2357
2358 create_allocation(
2359 p_api_version => p_api_version
2360 ,p_init_msg_list => p_init_msg_list
2361 ,p_commit => p_commit
2362 ,p_validation_level => p_validation_level
2363 ,p_alloc_rec => l_alloc_rec
2364 ,x_return_status => x_return_status
2365 ,x_msg_count => x_msg_count
2366 ,x_msg_data => x_msg_data
2367 ,x_alloc_id => l_alloc_id);
2368
2369 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2370 RAISE FND_API.g_exc_unexpected_error;
2371 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2372 RAISE FND_API.g_exc_unexpected_error;
2373 END IF;
2374
2375 publish_allocation(
2376 p_api_version => p_api_version
2377 ,p_init_msg_list => p_init_msg_list
2378 ,p_commit => p_commit
2379 ,p_validation_level => p_validation_level
2380 ,p_alloc_id => l_alloc_id
2381 ,x_return_status => x_return_status
2382 ,x_msg_count => x_msg_count
2383 ,x_msg_data => x_msg_data );
2384
2385 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2386 RAISE FND_API.g_exc_unexpected_error;
2387 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2388 RAISE FND_API.g_exc_unexpected_error;
2389 END IF;
2390
2391 END IF;
2392
2393 IF p_method = 'MANUAL'
2394 THEN
2395 --
2396 Create_Quota_Product_Spread(
2397 p_api_version => p_api_version
2398 ,p_init_msg_list => p_init_msg_list
2399 ,p_commit => p_commit
2400 ,p_validation_level => p_validation_level
2401 ,x_return_status => x_return_status
2402 ,x_msg_count => x_msg_count
2403 ,x_msg_data => x_msg_data
2404 ,p_allocation_for => 'FUND'
2405 ,p_allocation_for_id => x_quota_id
2406 ,p_quota_products_tbl => l_quota_products_tbl
2407 ,p_quota_prod_spread_tbl => l_quota_prod_spread_tbl );
2408
2409 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2410 RAISE FND_API.g_exc_unexpected_error;
2411 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2412 RAISE FND_API.g_exc_unexpected_error;
2413 END IF;
2414
2415 IF p_quota_accounts_tbl.COUNT <> 0
2416 THEN
2417 --
2418 Create_Quota_Account_Spread(
2419 p_api_version => p_api_version
2420 ,p_init_msg_list => p_init_msg_list
2421 ,p_commit => p_commit
2422 ,p_validation_level => p_validation_level
2423 ,x_return_status => x_return_status
2424 ,x_msg_count => x_msg_count
2425 ,x_msg_data => x_msg_data
2426 ,p_fund_id => x_quota_id
2427 ,p_quota_accounts_tbl => l_quota_accounts_tbl
2428 ,p_account_spread_tbl => l_account_spread_tbl
2429 ,p_account_products_tbl => l_account_products_tbl
2430 ,p_acct_prod_spread_tbl => l_acct_prod_spread_tbl);
2431 --
2432 END IF;
2433
2434 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2435 RAISE FND_API.g_exc_unexpected_error;
2436 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2437 RAISE FND_API.g_exc_unexpected_error;
2438 END IF;
2439
2440 --
2441 END IF;
2442
2443
2444 IF G_DEBUG THEN
2445 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name );
2446 END IF;
2447
2448 FND_MSG_PUB.Count_And_Get (
2449 p_encoded => FND_API.G_FALSE,
2450 p_count => x_msg_count,
2451 p_data => x_msg_data
2452 );
2453
2454 EXCEPTION
2455 WHEN FND_API.G_EXC_ERROR THEN
2456 ROLLBACK TO create_quota;
2457 x_return_status := FND_API.G_RET_STS_ERROR;
2458 -- Standard call to get message count and if count=1, get the message
2459 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2460 ,p_count => x_msg_count
2461 ,p_data => x_msg_data
2462 );
2463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2464 ROLLBACK TO create_quota;
2465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2466 -- Standard call to get message count and if count=1, get the message
2467 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2468 ,p_count => x_msg_count
2469 ,p_data => x_msg_data
2470 );
2471 WHEN OTHERS THEN
2472 ROLLBACK TO create_quota;
2473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2474 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2475 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2476 END IF;
2477 -- Standard call to get message count and if count=1, get the message
2478 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2479 ,p_count => x_msg_count
2480 ,p_data => x_msg_data
2481 );
2482 END create_quota;
2483
2484 ---------------------------------------------------------------------
2485 -- PROCEDURE
2486 -- update_quota
2487 --
2488 -- PURPOSE
2489 -- Update quota.
2490 --
2491 -- PARAMETERS
2492 -- p_quota_rec: the record with new items.
2493 --
2494 -- HISTORY
2495 -- 06/29/2005 kdass Created
2496 ---------------------------------------------------------------------
2497 PROCEDURE update_quota(
2498 p_api_version IN NUMBER
2499 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2500 ,p_commit IN VARCHAR2 := fnd_api.g_false
2501 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2502 ,x_return_status OUT NOCOPY VARCHAR2
2503 ,x_msg_count OUT NOCOPY NUMBER
2504 ,x_msg_data OUT NOCOPY VARCHAR2
2505 ,p_quota_rec IN quota_rec_type
2506 )
2507 IS
2508 l_api_name VARCHAR(30) := 'update_quota';
2509 l_mode VARCHAR2(6) := 'UPDATE';
2510 l_quota_rec quota_rec_type := p_quota_rec;
2511 l_fund_rec OZF_Funds_Pub.fund_rec_type;
2512 l_api_version NUMBER := p_api_version;
2513 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
2514 l_validation_level NUMBER := p_validation_level;
2515 l_commit VARCHAR2(1) := p_commit;
2516 l_quota_number VARCHAR2(200);
2517 l_short_name VARCHAR2(200);
2518 l_custom_setup_id NUMBER;
2519 l_description VARCHAR2(2000);
2520 l_status_code VARCHAR2(50);
2521 l_user_status_id NUMBER;
2522 l_start_period_name VARCHAR2(20);
2523 l_end_period_name VARCHAR2(20);
2524 l_quota_amount NUMBER;
2525 l_currency_code_tc VARCHAR2(10);
2526 l_owner NUMBER;
2527 l_threshold_id NUMBER;
2528 l_product_spread_time_id NUMBER;
2529 l_object_version_number NUMBER;
2530 l_quota_id NUMBER := l_quota_rec.quota_id;
2531 l_modifier_list_rec ozf_offer_pub.modifier_list_rec_type;
2532 l_modifier_line_tbl ozf_offer_pub.modifier_line_tbl_type;
2533 l_vo_pbh_tbl ozf_offer_pub.vo_disc_tbl_type;
2534 l_vo_dis_tbl ozf_offer_pub.vo_disc_tbl_type;
2535 l_vo_prod_tbl ozf_offer_pub.vo_prod_tbl_type;
2536 l_qualifier_tbl ozf_offer_pub.qualifiers_tbl_type;
2537 l_vo_mo_tbl ozf_offer_pub.vo_mo_tbl_type;
2538
2539 CURSOR c_quota_id (quota_number IN VARCHAR2) IS
2540 SELECT fund_id
2541 FROM ozf_funds_all_b
2542 WHERE fund_number = quota_number;
2543
2544 CURSOR c_quota_details (quota_id IN NUMBER) IS
2545 SELECT fund_number, short_name, custom_setup_id, description, status_code,
2546 user_status_id, start_period_name, end_period_name, original_budget,
2547 currency_code_tc, owner, threshold_id, product_spread_time_id, object_version_number
2548 FROM ozf_funds_all_vl
2549 WHERE fund_id = quota_id;
2550
2551 BEGIN
2552
2553 SAVEPOINT update_quota;
2554
2555 IF l_quota_rec.quota_id IS NULL AND l_quota_rec.quota_number IS NOT NULL THEN
2556 OPEN c_quota_id (l_quota_rec.quota_number);
2557 FETCH c_quota_id INTO l_quota_id;
2558 CLOSE c_quota_id;
2559 END IF;
2560
2561 IF G_DEBUG THEN
2562 ozf_utility_pvt.debug_message(l_api_name || ': l_quota_id :' || l_quota_id);
2563 END IF;
2564
2565 IF l_quota_id IS NOT NULL THEN
2566 OPEN c_quota_details (l_quota_id);
2567 FETCH c_quota_details INTO l_quota_number, l_short_name, l_custom_setup_id, l_description,
2568 l_status_code, l_user_status_id, l_start_period_name, l_end_period_name,
2569 l_quota_amount, l_currency_code_tc, l_owner, l_threshold_id,
2570 l_product_spread_time_id, l_object_version_number;
2571 CLOSE c_quota_details;
2572 END IF;
2573
2574 IF G_DEBUG THEN
2575 ozf_utility_pvt.debug_message(l_api_name || ': l_quota_amount :' || l_quota_amount);
2576 ozf_utility_pvt.debug_message(l_api_name || ': l_product_spread_time_id :' || l_product_spread_time_id);
2577 END IF;
2578
2579 l_quota_rec.quota_number := NVL(l_quota_rec.quota_number,l_quota_number);
2580 l_quota_rec.short_name := NVL(l_quota_rec.short_name,l_short_name);
2581 l_quota_rec.custom_setup_id := NVL(l_quota_rec.custom_setup_id,l_custom_setup_id);
2582 l_quota_rec.description := NVL(l_quota_rec.description,l_description);
2583 l_quota_rec.status_code := NVL(l_quota_rec.status_code,l_status_code);
2584 l_quota_rec.start_period_name := NVL(l_quota_rec.start_period_name,l_start_period_name);
2585 l_quota_rec.end_period_name := NVL(l_quota_rec.end_period_name,l_end_period_name);
2586 l_quota_rec.quota_amount := NVL(l_quota_rec.quota_amount,l_quota_amount);
2587 l_quota_rec.currency_code_tc := NVL(l_quota_rec.currency_code_tc,l_currency_code_tc);
2588 l_quota_rec.owner := NVL(l_quota_rec.owner,l_owner);
2589 l_quota_rec.threshold_id := NVL(l_quota_rec.threshold_id,l_threshold_id);
2590 l_quota_rec.product_spread_time_id := NVL(l_quota_rec.product_spread_time_id,l_product_spread_time_id);
2591
2592
2593 validate_quota_attributes( p_quota_rec => l_quota_rec
2594 ,p_mode => 'UPDATE'
2595 ,p_method => 'MANUAL'
2596 ,p_fund_rec => l_fund_rec
2597 ,x_return_status => x_return_status);
2598
2599 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2600 RAISE fnd_api.g_exc_unexpected_error;
2601 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2602 RAISE fnd_api.g_exc_error;
2603 END IF;
2604
2605 l_fund_rec.fund_id := l_quota_rec.quota_id;
2606 l_fund_rec.fund_number := l_quota_rec.quota_number;
2607 l_fund_rec.fund_type := 'QUOTA';
2608 l_fund_rec.short_name := l_quota_rec.short_name;
2609 l_fund_rec.custom_setup_id := l_quota_rec.custom_setup_id;
2610 l_fund_rec.description := l_quota_rec.description;
2611 l_fund_rec.category_id := '10001';
2612 l_fund_rec.status_code := l_quota_rec.status_code;
2613 l_fund_rec.user_status_id := l_quota_rec.user_status_id;
2614 l_fund_rec.start_period_name := l_quota_rec.start_period_name;
2615 l_fund_rec.end_period_name := l_quota_rec.end_period_name;
2616 l_fund_rec.start_date_active := l_quota_rec.start_date_active;
2617 l_fund_rec.end_date_active := l_quota_rec.end_date_active;
2618 l_fund_rec.original_budget := l_quota_rec.quota_amount;
2619 l_fund_rec.currency_code_tc := l_quota_rec.currency_code_tc;
2620 l_fund_rec.owner := l_quota_rec.owner;
2621 l_fund_rec.threshold_id := l_quota_rec.threshold_id;
2622 l_fund_rec.product_spread_time_id := l_quota_rec.product_spread_time_id;
2623 -- l_fund_rec.object_version_number := l_quota_rec.object_version_number;
2624
2625 -- update quota
2626 ozf_funds_pub.update_fund(p_api_version => l_api_version
2627 ,p_init_msg_list => l_init_msg_list
2628 ,p_commit => l_commit
2629 ,p_validation_level => l_validation_level
2630 ,x_return_status => x_return_status
2631 ,x_msg_count => x_msg_count
2632 ,x_msg_data => x_msg_data
2633 ,p_fund_rec => l_fund_rec
2634 ,p_modifier_list_rec => l_modifier_list_rec
2635 ,p_modifier_line_tbl => l_modifier_line_tbl
2636 ,p_vo_pbh_tbl => l_vo_pbh_tbl
2637 ,p_vo_dis_tbl => l_vo_dis_tbl
2638 ,p_vo_prod_tbl => l_vo_prod_tbl
2639 ,p_qualifier_tbl => l_qualifier_tbl
2640 ,p_vo_mo_tbl => l_vo_mo_tbl
2641 );
2642
2643 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2644 RAISE fnd_api.g_exc_unexpected_error;
2645 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2646 RAISE fnd_api.g_exc_error;
2647 END IF;
2648
2649 FND_MSG_PUB.Count_And_Get (
2650 p_encoded => FND_API.G_FALSE,
2651 p_count => x_msg_count,
2652 p_data => x_msg_data
2653 );
2654
2655 EXCEPTION
2656 WHEN FND_API.G_EXC_ERROR THEN
2657 ROLLBACK TO update_quota;
2658 x_return_status := FND_API.G_RET_STS_ERROR;
2659 -- Standard call to get message count and if count=1, get the message
2660 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2661 ,p_count => x_msg_count
2662 ,p_data => x_msg_data
2663 );
2664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2665 ROLLBACK TO update_quota;
2666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2667 -- Standard call to get message count and if count=1, get the message
2668 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2669 ,p_count => x_msg_count
2670 ,p_data => x_msg_data
2671 );
2672 WHEN OTHERS THEN
2673 ROLLBACK TO update_quota;
2674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2675 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2676 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2677 END IF;
2678 -- Standard call to get message count and if count=1, get the message
2679 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2680 ,p_count => x_msg_count
2681 ,p_data => x_msg_data
2682 );
2683 END Update_Quota;
2684
2685 PROCEDURE Update_Quota_Product_Spread(
2686 p_api_version IN NUMBER
2687 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2688 ,p_commit IN VARCHAR2 := fnd_api.g_false
2689 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2690 ,x_return_status OUT NOCOPY VARCHAR2
2691 ,x_msg_count OUT NOCOPY NUMBER
2692 ,x_msg_data OUT NOCOPY VARCHAR2
2693 ,p_quota_products_tbl IN quota_products_tbl_type
2694 ,p_quota_prod_spread_tbl IN quota_prod_spread_tbl_type )
2695 IS
2696
2697 l_api_name VARCHAR(30) := 'update_quota_product_spread';
2698 l_product_allocation_id NUMBER;
2699 l_time_allocation_id NUMBER;
2700 l_object_version_number NUMBER;
2701
2702 CURSOR c_chk_prod_alloc_id (p_product_allocation_id NUMBER) IS
2703 SELECT product_allocation_id,
2704 object_version_number
2705 FROM ozf_product_allocations
2706 WHERE product_allocation_id = p_product_allocation_id;
2707
2708 CURSOR c_chk_time_alloc_id (p_time_allocation_id NUMBER) IS
2709 SELECT time_allocation_id,
2710 object_version_number
2711 FROM ozf_time_allocations
2712 WHERE time_allocation_id = p_time_allocation_id;
2713
2714 BEGIN
2715 --
2716 SAVEPOINT UPDATE_QUOTA_PRODUCT_SPREAD;
2717
2718 IF G_DEBUG THEN
2719 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name );
2720 END IF;
2721
2722 -- Initialize API return status to success
2723 x_return_status := FND_API.G_RET_STS_SUCCESS;
2724
2725 ---------------------------------------------
2726
2727 IF p_quota_products_tbl.COUNT > 0
2728 THEN
2729 --
2730 FOR l_prod_alloc_counter IN 1..p_quota_products_tbl.COUNT
2731 LOOP
2732 --
2733 OPEN c_chk_prod_alloc_id ( p_quota_products_tbl(l_prod_alloc_counter).product_allocation_id);
2734 FETCH c_chk_prod_alloc_id INTO l_product_allocation_id ,
2735 l_object_version_number;
2736 CLOSE c_chk_prod_alloc_id;
2737
2738 IF l_product_allocation_id IS NULL
2739 THEN
2740 --
2741 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2742 THEN
2743 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
2744 fnd_message.set_token('COL_NAME', 'PRODUCT_ALLOCATION_ID');
2745 fnd_msg_pub.add;
2746 END IF;
2747 x_return_status := fnd_api.g_ret_sts_error;
2748 RETURN;
2749 --
2750 END IF;
2751
2752
2753 UPDATE ozf_product_allocations
2754 SET target = NVL(p_quota_products_tbl(l_prod_alloc_counter).target, target),
2755 lysp_sales = NVL(p_quota_products_tbl(l_prod_alloc_counter).lysp_sales, lysp_sales),
2756 object_version_number = l_object_version_number + 1 ,
2757 last_update_date = SYSDATE,
2758 last_updated_by = FND_GLOBAL.USER_ID
2759 WHERE product_allocation_id = l_product_allocation_id;
2760 --
2761 END LOOP; -- Done updating Product Allocations
2762 --
2763 END IF;
2764
2765 IF p_quota_prod_spread_tbl.COUNT > 0
2766 THEN
2767 --
2768 FOR l_prod_sprd_counter IN 1..p_quota_prod_spread_tbl.COUNT
2769 LOOP
2770 --
2771 OPEN c_chk_time_alloc_id (p_quota_prod_spread_tbl(l_prod_sprd_counter).time_allocation_id);
2772 FETCH c_chk_time_alloc_id INTO l_time_allocation_id ,
2773 l_object_version_number;
2774 CLOSE c_chk_time_alloc_id;
2775
2776 IF l_time_allocation_id IS NULL
2777 THEN
2778 --
2779 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2780 THEN
2781 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
2782 fnd_message.set_token('COL_NAME', 'TIME_ALLOCATION_ID');
2783 fnd_msg_pub.add;
2784 END IF;
2785 x_return_status := fnd_api.g_ret_sts_error;
2786 RETURN;
2787 --
2788 END IF;
2789
2790
2791 UPDATE ozf_time_allocations
2792 SET target = NVL(p_quota_prod_spread_tbl(l_prod_sprd_counter).target, target),
2793 lysp_sales = NVL(p_quota_prod_spread_tbl(l_prod_sprd_counter).lysp_sales, lysp_sales),
2794 object_version_number = l_object_version_number + 1 ,
2795 last_update_date = SYSDATE,
2796 last_updated_by = FND_GLOBAL.USER_ID
2797 WHERE time_allocation_id = l_time_allocation_id;
2798 --
2799 END LOOP;
2800 --
2801 END IF;
2802 ---------------------------------------------
2803
2804 IF G_DEBUG THEN
2805 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name );
2806 END IF;
2807
2808 FND_MSG_PUB.Count_And_Get (
2809 p_encoded => FND_API.G_FALSE,
2810 p_count => x_msg_count,
2811 p_data => x_msg_data
2812 );
2813
2814 EXCEPTION
2815 WHEN FND_API.G_EXC_ERROR THEN
2816 ROLLBACK TO update_quota_product_spread;
2817 x_return_status := FND_API.G_RET_STS_ERROR;
2818 -- Standard call to get message count and if count=1, get the message
2819 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2820 ,p_count => x_msg_count
2821 ,p_data => x_msg_data
2822 );
2823
2824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2825 ROLLBACK TO update_quota_product_spread;
2826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2827 -- Standard call to get message count and if count=1, get the message
2828 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2829 ,p_count => x_msg_count
2830 ,p_data => x_msg_data
2831 );
2832 WHEN OTHERS THEN
2833 ROLLBACK TO update_quota_product_spread;
2834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2835 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2836 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2837 END IF;
2838 -- Standard call to get message count and if count=1, get the message
2839 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2840 ,p_count => x_msg_count
2841 ,p_data => x_msg_data
2842 );
2843
2844 END Update_Quota_Product_Spread;
2845
2846
2847 PROCEDURE Update_Quota_Account_Spread(
2848 p_api_version IN NUMBER
2849 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2850 ,p_commit IN VARCHAR2 := fnd_api.g_false
2851 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2852 ,x_return_status OUT NOCOPY VARCHAR2
2853 ,x_msg_count OUT NOCOPY NUMBER
2854 ,x_msg_data OUT NOCOPY VARCHAR2
2855 ,p_quota_accounts_tbl IN quota_accounts_tbl_type
2856 ,p_account_spread_tbl IN account_spread_tbl_type )
2857 IS
2858 l_api_name VARCHAR(30) := 'update_quota_account_spread';
2859 l_account_allocation_id NUMBER;
2860 l_time_allocation_id NUMBER;
2861 l_object_version_number NUMBER;
2862
2863 CURSOR c_chk_acct_alloc_id (p_account_allocation_id NUMBER) IS
2864 SELECT account_allocation_id,
2865 object_version_number
2866 FROM ozf_account_allocations
2867 WHERE account_allocation_id = p_account_allocation_id;
2868
2869 CURSOR c_chk_time_alloc_id (p_time_allocation_id NUMBER) IS
2870 SELECT time_allocation_id,
2871 object_version_number
2872 FROM ozf_time_allocations
2873 WHERE time_allocation_id = p_time_allocation_id;
2874
2875 BEGIN
2876 --
2877 SAVEPOINT UPDATE_QUOTA_ACCOUNT_SPREAD;
2878
2879 IF G_DEBUG THEN
2880 ozf_utility_pvt.debug_message('Start Procedure: '|| l_api_name );
2881 END IF;
2882
2883 -- Initialize API return status to success
2884 x_return_status := FND_API.G_RET_STS_SUCCESS;
2885
2886 ---------------------------------------------
2887
2888 IF p_quota_accounts_tbl.COUNT > 0
2889 THEN
2890 --
2891 FOR l_acct_alloc_counter IN 1..p_quota_accounts_tbl.COUNT
2892 LOOP
2893 --
2894 OPEN c_chk_acct_alloc_id ( p_quota_accounts_tbl(l_acct_alloc_counter).account_allocation_id);
2895 FETCH c_chk_acct_alloc_id INTO l_account_allocation_id ,
2896 l_object_version_number;
2897 CLOSE c_chk_acct_alloc_id;
2898
2899 IF l_account_allocation_id IS NULL
2900 THEN
2901 --
2902 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2903 THEN
2904 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
2905 fnd_message.set_token('COL_NAME', 'ACCOUNT_ALLOCATION_ID');
2906 fnd_msg_pub.add;
2907 END IF;
2908 x_return_status := fnd_api.g_ret_sts_error;
2909 RETURN;
2910 --
2911 END IF;
2912
2913
2914 UPDATE ozf_account_allocations
2915 SET target = NVL(p_quota_accounts_tbl(l_acct_alloc_counter).target, target),
2916 lysp_sales = NVL(p_quota_accounts_tbl(l_acct_alloc_counter).lysp_sales, lysp_sales),
2917 object_version_number = l_object_version_number + 1 ,
2918 last_update_date = SYSDATE,
2919 last_updated_by = FND_GLOBAL.USER_ID
2920 WHERE account_allocation_id = l_account_allocation_id;
2921 --
2922 END LOOP; -- Done updating Account Allocations
2923 --
2924 END IF;
2925
2926 IF p_account_spread_tbl.COUNT > 0
2927 THEN
2928 --
2929 FOR l_acct_sprd_counter IN 1..p_account_spread_tbl.COUNT
2930 LOOP
2931 --
2932 OPEN c_chk_time_alloc_id (p_account_spread_tbl(l_acct_sprd_counter).time_allocation_id);
2933 FETCH c_chk_time_alloc_id INTO l_time_allocation_id ,
2934 l_object_version_number;
2935 CLOSE c_chk_time_alloc_id;
2936
2937 IF l_time_allocation_id IS NULL
2938 THEN
2939 --
2940 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2941 THEN
2942 fnd_message.set_name('OZF', 'OZF_QUOTA_INVALID_COL_VALUE');
2943 fnd_message.set_token('COL_NAME', 'TIME_ALLOCATION_ID');
2944 fnd_msg_pub.add;
2945 END IF;
2946 x_return_status := fnd_api.g_ret_sts_error;
2947 RETURN;
2948 --
2949 END IF;
2950
2951
2952 UPDATE ozf_time_allocations
2953 SET target = NVL(p_account_spread_tbl(l_acct_sprd_counter).target, target),
2954 lysp_sales = NVL(p_account_spread_tbl(l_acct_sprd_counter).lysp_sales, lysp_sales),
2955 object_version_number = l_object_version_number + 1 ,
2956 last_update_date = SYSDATE,
2957 last_updated_by = FND_GLOBAL.USER_ID
2958 WHERE time_allocation_id = l_time_allocation_id;
2959 --
2960 END LOOP;
2961 --
2962 END IF;
2963 ---------------------------------------------
2964
2965 IF G_DEBUG THEN
2966 ozf_utility_pvt.debug_message('End Procedure: '|| l_api_name );
2967 END IF;
2968
2969 FND_MSG_PUB.Count_And_Get (
2970 p_encoded => FND_API.G_FALSE,
2971 p_count => x_msg_count,
2972 p_data => x_msg_data
2973 );
2974
2975 EXCEPTION
2976 WHEN FND_API.G_EXC_ERROR THEN
2977 ROLLBACK TO update_quota_account_spread;
2978 x_return_status := FND_API.G_RET_STS_ERROR;
2979 -- Standard call to get message count and if count=1, get the message
2980 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2981 ,p_count => x_msg_count
2982 ,p_data => x_msg_data
2983 );
2984
2985 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2986 ROLLBACK TO update_quota_account_spread;
2987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2988 -- Standard call to get message count and if count=1, get the message
2989 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
2990 ,p_count => x_msg_count
2991 ,p_data => x_msg_data
2992 );
2993 WHEN OTHERS THEN
2994 ROLLBACK TO update_quota_account_spread;
2995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2996 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2997 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2998 END IF;
2999 -- Standard call to get message count and if count=1, get the message
3000 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3001 ,p_count => x_msg_count
3002 ,p_data => x_msg_data
3003 );
3004
3005
3006 END Update_Quota_Account_Spread ;
3007
3008 PROCEDURE delete_quota_allocations ( p_quota_id IN NUMBER) IS
3009
3010 CURSOR c_child_quotas (c_quota_id NUMBER) IS
3011 SELECT fund_id
3012 from ozf_funds_all_b
3013 where fund_type = 'QUOTA'
3014 and parent_fund_id = c_quota_id;
3015
3016 BEGIN
3017
3018 FOR i IN c_child_quotas(p_quota_id)
3019 LOOP
3020
3021 delete_quota_allocations(i.fund_id);
3022
3023 END LOOP;
3024
3025
3026 delete from ozf_time_allocations
3027 where allocation_for = 'PROD'
3028 and allocation_for_id in ( select product_allocation_id
3029 from ozf_product_allocations
3030 where allocation_for = 'CUST'
3031 and allocation_for_id in ( select account_allocation_id
3032 from ozf_account_allocations
3033 where allocation_for = 'FUND'
3034 and allocation_for_id = p_quota_id)
3035 );
3036
3037 delete from ozf_product_allocations
3038 where allocation_for = 'CUST'
3039 and allocation_for_id in ( select account_allocation_id
3040 from ozf_account_allocations
3041 where allocation_for = 'FUND'
3042 and allocation_for_id = p_quota_id);
3043
3044
3045 delete from ozf_time_allocations
3046 where allocation_for = 'CUST'
3047 and allocation_for_id in (select account_allocation_id
3048 from ozf_account_allocations
3049 where allocation_for = 'FUND'
3050 and allocation_for_id = p_quota_id ) ;
3051
3052 delete from ozf_account_allocations
3053 where allocation_for = 'FUND'
3054 and allocation_for_id = p_quota_id ;
3055
3056 delete from ozf_time_allocations
3057 where allocation_for = 'PROD'
3058 and allocation_for_id in (select product_allocation_id
3059 from ozf_product_allocations
3060 where allocation_for = 'FUND'
3061 and allocation_for_id = p_quota_id ) ;
3062
3063 delete from ozf_product_allocations
3064 where allocation_for = 'FUND'
3065 and allocation_for_id = p_quota_id;
3066
3067 END;
3068
3069
3070 ---------------------------------------------------------------------
3071 -- PROCEDURE
3072 -- delete_quota
3073 --
3074 -- PURPOSE
3075 -- Delete a quota.
3076 --
3077 -- PARAMETERS
3078 -- p_quota_id: the quota id
3079 --
3080 -- HISTORY
3081 -- 07/04/2005 kdass Created
3082 ---------------------------------------------------------------------
3083 PROCEDURE delete_quota(
3084 p_api_version IN NUMBER
3085 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3086 ,p_commit IN VARCHAR2 := fnd_api.g_false
3087 ,p_quota_id IN NUMBER
3088 ,x_return_status OUT NOCOPY VARCHAR2
3089 ,x_msg_count OUT NOCOPY NUMBER
3090 ,x_msg_data OUT NOCOPY VARCHAR2
3091 )
3092 IS
3093 l_api_name VARCHAR(30) := 'delete_quota';
3094 l_api_version NUMBER := p_api_version;
3095 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
3096 l_commit VARCHAR2(1) := p_commit;
3097 l_object_version NUMBER;
3098
3099 CURSOR c_valid_quota IS
3100 SELECT object_version_number
3101 FROM ozf_funds_all_b
3102 WHERE fund_type = 'QUOTA'
3103 AND fund_id = p_quota_id ;
3104
3105
3106 BEGIN
3107
3108 SAVEPOINT delete_quota;
3109
3110 --if quota id is null, then raise exception
3111 IF (p_quota_id = fnd_api.g_miss_num OR p_quota_id IS NULL) THEN
3112
3113 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3114 fnd_message.set_name('OZF', 'OZF_NO_QUOTA_ID');
3115 fnd_msg_pub.add;
3116 END IF;
3117 RAISE fnd_api.g_exc_error;
3118 END IF;
3119
3120 --check if the quota id is valid and get the object_version_number
3121 OPEN c_valid_quota;
3122 FETCH c_valid_quota INTO l_object_version;
3123 CLOSE c_valid_quota;
3124
3125 IF l_object_version IS NULL THEN
3126 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3127 fnd_message.set_name('OZF', 'OZF_INVALID_QUOTA_ID');
3128 fnd_msg_pub.add;
3129 END IF;
3130 RAISE fnd_api.g_exc_error;
3131 END IF;
3132
3133 delete_quota_allocations(p_quota_id);
3134
3135 -- delete quota
3136 OZF_FUNDS_PUB.delete_fund(p_api_version => l_api_version
3137 ,p_init_msg_list => l_init_msg_list
3138 ,p_commit => l_commit
3139 ,p_fund_id => p_quota_id
3140 ,p_object_version => l_object_version
3141 ,x_return_status => x_return_status
3142 ,x_msg_count => x_msg_count
3143 ,x_msg_data => x_msg_data
3144 );
3145
3146 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3147 RAISE fnd_api.g_exc_unexpected_error;
3148 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3149 RAISE fnd_api.g_exc_error;
3150 END IF;
3151
3152 FND_MSG_PUB.Count_And_Get (
3153 p_encoded => FND_API.G_FALSE,
3154 p_count => x_msg_count,
3155 p_data => x_msg_data
3156 );
3157
3158 EXCEPTION
3159 WHEN FND_API.G_EXC_ERROR THEN
3160 ROLLBACK TO delete_quota;
3161 x_return_status := FND_API.G_RET_STS_ERROR;
3162 -- Standard call to get message count and if count=1, get the message
3163 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3164 ,p_count => x_msg_count
3165 ,p_data => x_msg_data
3166 );
3167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3168 ROLLBACK TO delete_quota;
3169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3170 -- Standard call to get message count and if count=1, get the message
3171 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3172 ,p_count => x_msg_count
3173 ,p_data => x_msg_data
3174 );
3175 WHEN OTHERS THEN
3176 ROLLBACK TO delete_quota;
3177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3178 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3179 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3180 END IF;
3181 -- Standard call to get message count and if count=1, get the message
3182 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3183 ,p_count => x_msg_count
3184 ,p_data => x_msg_data
3185 );
3186 END delete_quota;
3187
3188 ---------------------------------------------------------------------
3189 -- PROCEDURE
3190 -- generate_product_spread
3191 --
3192 -- PURPOSE
3193 -- Create product spread for quota.
3194 --
3195 -- PARAMETERS
3196 -- p_quota_id: the quota id
3197 --
3198 -- HISTORY
3199 -- 07/04/2005 kdass Created
3200 ---------------------------------------------------------------------
3201 PROCEDURE generate_product_spread(
3202 p_api_version IN NUMBER
3203 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3204 ,p_commit IN VARCHAR2 := fnd_api.g_false
3205 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
3206 ,p_quota_id IN NUMBER
3207 ,x_return_status OUT NOCOPY VARCHAR2
3208 ,x_error_number OUT NOCOPY NUMBER
3209 ,x_error_message OUT NOCOPY VARCHAR2
3210 )
3211 IS
3212 l_api_name VARCHAR(30) := 'generate_product_spread';
3213 l_api_version NUMBER := p_api_version;
3214 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
3215 l_validation_level NUMBER := p_validation_level;
3216 l_commit VARCHAR2(1) := p_commit;
3217 l_valid_quota NUMBER := NULL;
3218 l_prod_exists NUMBER := NULL;
3219
3220 CURSOR c_valid_quota IS
3221 SELECT 1
3222 FROM ozf_funds_all_b
3223 WHERE fund_type = 'QUOTA'
3224 AND fund_id = p_quota_id;
3225
3226 CURSOR c_product_exists IS
3227 SELECT 1
3228 FROM ams_act_products
3229 WHERE act_product_used_by_id = p_quota_id
3230 and arc_act_product_used_by = 'FUND';
3231
3232 BEGIN
3233
3234 SAVEPOINT generate_product_spread;
3235
3236 --if quota id is null, then raise exception
3237 IF (p_quota_id = fnd_api.g_miss_num OR p_quota_id IS NULL) THEN
3238
3239 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3240 fnd_message.set_name('OZF', 'OZF_NO_QUOTA_ID');
3241 fnd_msg_pub.add;
3242 END IF;
3243 RAISE fnd_api.g_exc_error;
3244 END IF;
3245
3246 --check if the quota id is valid and get the object_version_number
3247 OPEN c_valid_quota;
3248 FETCH c_valid_quota INTO l_valid_quota;
3249 CLOSE c_valid_quota;
3250
3251 IF l_valid_quota IS NULL THEN
3252 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3253 fnd_message.set_name('OZF', 'OZF_INVALID_QUOTA_ID');
3254 fnd_msg_pub.add;
3255 END IF;
3256 RAISE fnd_api.g_exc_error;
3257 END IF;
3258
3259 --check if the quota has any products
3260 OPEN c_product_exists;
3261 FETCH c_product_exists INTO l_prod_exists;
3262 CLOSE c_product_exists;
3263
3264 IF l_prod_exists IS NULL THEN
3265 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3266 fnd_message.set_name('OZF', 'OZF_QUOTA_NO_PROD');
3267 fnd_msg_pub.add;
3268 END IF;
3269 RAISE fnd_api.g_exc_error;
3270 END IF;
3271
3272 -- create product spread for quota
3273 OZF_ALLOCATION_ENGINE_PVT.setup_product_spread(p_api_version => l_api_version
3274 ,p_init_msg_list => l_init_msg_list
3275 ,p_commit => l_commit
3276 ,p_validation_level => l_validation_level
3277 ,x_return_status => x_return_status
3278 ,x_error_number => x_error_number
3279 ,x_error_message => x_error_message
3280 ,p_mode => 'CREATE'
3281 ,p_obj_id => p_quota_id
3282 ,p_context => 'ROOT'
3283 );
3284
3285 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3286 RAISE fnd_api.g_exc_unexpected_error;
3287 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3288 RAISE fnd_api.g_exc_error;
3289 END IF;
3290
3291 FND_MSG_PUB.Count_And_Get(p_count => x_error_number,
3292 p_data => x_error_message);
3293
3294 EXCEPTION
3295 WHEN FND_API.G_EXC_ERROR THEN
3296 ROLLBACK TO generate_product_spread;
3297 x_return_status := FND_API.G_RET_STS_ERROR;
3298 FND_MSG_PUB.Count_And_Get(p_count => x_error_number,
3299 p_data => x_error_message);
3300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3301 ROLLBACK TO generate_product_spread;
3302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3303 FND_MSG_PUB.Count_And_Get(p_count => x_error_number,
3304 p_data => x_error_message);
3305 WHEN OTHERS THEN
3306 ROLLBACK TO generate_product_spread;
3307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3308 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3309 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3310 END IF;
3311 FND_MSG_PUB.Count_And_Get(p_count => x_error_number,
3312 p_data => x_error_message);
3313 END generate_product_spread;
3314
3315 ---------------------------------------------------------------------
3316 -- PROCEDURE
3317 -- create_allocation
3318 --
3319 -- PURPOSE
3320 -- Create quota allocation.
3321 --
3322 -- PARAMETERS
3323 -- p_alloc_rec: the new record to be inserted
3324 -- x_alloc_id: returns the allocation id of the new allocation
3325 --
3326 -- HISTORY
3327 -- 07/04/2005 kdass Created
3328 ---------------------------------------------------------------------
3329 PROCEDURE create_allocation(
3330 p_api_version IN NUMBER
3331 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3332 ,p_commit IN VARCHAR2 := fnd_api.g_false
3333 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
3334 ,p_alloc_rec IN alloc_rec_type
3335 ,x_return_status OUT NOCOPY VARCHAR2
3336 ,x_msg_count OUT NOCOPY NUMBER
3337 ,x_msg_data OUT NOCOPY VARCHAR2
3338 ,x_alloc_id OUT NOCOPY NUMBER
3339 )
3340 IS
3341 l_api_name VARCHAR(30) := 'create_allocation';
3342 l_alloc_rec alloc_rec_type := p_alloc_rec;
3343 l_act_metric_rec OZF_ACTMETRIC_PVT.act_metric_rec_type;
3344 l_api_version NUMBER := p_api_version;
3345 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
3346 l_validation_level NUMBER := p_validation_level;
3347 l_commit VARCHAR2(1) := p_commit;
3348 l_spread_exists NUMBER := NULL;
3349
3350 CURSOR c_product_spread_exists (p_quota_id IN NUMBER) IS
3351 SELECT 1
3352 FROM ozf_product_allocations
3353 WHERE fund_id = p_quota_id;
3354
3355 BEGIN
3356
3357 SAVEPOINT create_allocation;
3358
3359 validate_alloc_attributes(p_alloc_rec => l_alloc_rec
3360 ,x_return_status => x_return_status);
3361
3362 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3363 RAISE fnd_api.g_exc_unexpected_error;
3364 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3365 RAISE fnd_api.g_exc_error;
3366 END IF;
3367
3368 --check if the product spread exists
3369 OPEN c_product_spread_exists (l_alloc_rec.quota_id);
3370 FETCH c_product_spread_exists INTO l_spread_exists;
3371 CLOSE c_product_spread_exists;
3372
3373 IF l_spread_exists IS NULL THEN
3374 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3375 fnd_message.set_name('OZF', 'OZF_NO_PROD_SPREAD'); -- product spread needs to be created before creating allocation
3376 fnd_msg_pub.add;
3377 END IF;
3378 RAISE fnd_api.g_exc_error;
3379 END IF;
3380
3381 l_act_metric_rec.act_metric_used_by_id := l_alloc_rec.quota_id;
3382 l_act_metric_rec.arc_act_metric_used_by := 'FUND';
3383 l_act_metric_rec.application_id := '682';
3384 l_act_metric_rec.metric_id := '55';
3385 l_act_metric_rec.sensitive_data_flag := 'N';
3386 l_act_metric_rec.status_code := 'NEW';
3387 l_act_metric_rec.action_code := 'CREATE_NEW_BUDGET';
3388 l_act_metric_rec.hierarchy_type := 'TERRITORY';
3389 l_act_metric_rec.ex_start_node := 'N';
3390 l_act_metric_rec.hierarchy_id := l_alloc_rec.hierarchy_id;
3391 l_act_metric_rec.from_level := l_alloc_rec.from_level;
3392 l_act_metric_rec.to_level := l_alloc_rec.to_level;
3393 l_act_metric_rec.start_node := l_alloc_rec.start_node;
3394 l_act_metric_rec.start_period_name := l_alloc_rec.start_period_name;
3395 l_act_metric_rec.end_period_name := l_alloc_rec.end_period_name;
3396 l_act_metric_rec.from_date := l_alloc_rec.from_date;
3397 l_act_metric_rec.to_date := l_alloc_rec.to_date;
3398 l_act_metric_rec.func_actual_value := l_alloc_rec.alloc_amount;
3399 l_act_metric_rec.method_code := l_alloc_rec.method_code;
3400 l_act_metric_rec.basis_year := l_alloc_rec.basis_year;
3401 l_act_metric_rec.product_spread_time_id := l_alloc_rec.product_spread_time_id;
3402
3403 -- create quota allocation
3404 ozf_actmetric_pvt.create_actmetric(p_api_version => l_api_version
3405 ,p_init_msg_list => l_init_msg_list
3406 ,p_commit => l_commit
3407 ,p_validation_level => l_validation_level
3408 ,x_return_status => x_return_status
3409 ,x_msg_count => x_msg_count
3410 ,x_msg_data => x_msg_data
3411 ,p_act_metric_rec => l_act_metric_rec
3412 ,x_activity_metric_id => x_alloc_id
3413 );
3414
3415 IF x_alloc_id IS NOT NULL THEN
3416 ozf_quota_allocations_pvt.create_quota_alloc_hierarchy(p_api_version => l_api_version
3417 ,p_init_msg_list => l_init_msg_list
3418 ,p_commit => l_commit
3419 ,x_return_status => x_return_status
3420 ,x_msg_count => x_msg_count
3421 ,x_msg_data => x_msg_data
3422 ,p_alloc_id => x_alloc_id
3423 );
3424 END IF;
3425
3426 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3427 RAISE fnd_api.g_exc_unexpected_error;
3428 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3429 RAISE fnd_api.g_exc_error;
3430 END IF;
3431
3432 FND_MSG_PUB.Count_And_Get (
3433 p_encoded => FND_API.G_FALSE,
3434 p_count => x_msg_count,
3435 p_data => x_msg_data
3436 );
3437
3438 EXCEPTION
3439 WHEN FND_API.G_EXC_ERROR THEN
3440 ROLLBACK TO create_allocation;
3441 x_return_status := FND_API.G_RET_STS_ERROR;
3442 -- Standard call to get message count and if count=1, get the message
3443 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3444 ,p_count => x_msg_count
3445 ,p_data => x_msg_data
3446 );
3447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3448 ROLLBACK TO create_allocation;
3449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3450 -- Standard call to get message count and if count=1, get the message
3451 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3452 ,p_count => x_msg_count
3453 ,p_data => x_msg_data
3454 );
3455 WHEN OTHERS THEN
3456 ROLLBACK TO create_allocation;
3457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3458 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3459 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3460 END IF;
3461 -- Standard call to get message count and if count=1, get the message
3462 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3463 ,p_count => x_msg_count
3464 ,p_data => x_msg_data
3465 );
3466 END create_allocation;
3467
3468 ---------------------------------------------------------------------
3469 -- PROCEDURE
3470 -- publish_allocation
3471 --
3472 -- PURPOSE
3473 -- Publish quota allocation.
3474 --
3475 -- PARAMETERS
3476 -- p_alloc_id: allocation id
3477 --
3478 -- HISTORY
3479 -- 07/04/2005 kdass Created
3480 ---------------------------------------------------------------------
3481 PROCEDURE publish_allocation(
3482 p_api_version IN NUMBER
3483 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3484 ,p_commit IN VARCHAR2 := fnd_api.g_false
3485 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
3486 ,p_alloc_id IN NUMBER
3487 ,x_return_status OUT NOCOPY VARCHAR2
3488 ,x_msg_count OUT NOCOPY NUMBER
3489 ,x_msg_data OUT NOCOPY VARCHAR2
3490 )
3491 IS
3492 l_api_name VARCHAR(30) := 'publish_allocation';
3493 l_api_version NUMBER := p_api_version;
3494 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
3495 l_validation_level NUMBER := p_validation_level;
3496 l_commit VARCHAR2(1) := p_commit;
3497 l_valid_alloc NUMBER := NULL;
3498 l_alloc_status VARCHAR2(20);
3499 l_alloc_obj_ver NUMBER;
3500
3501 CURSOR c_valid_alloc IS
3502 SELECT 1
3503 FROM ozf_act_metrics_all
3504 WHERE activity_metric_id = p_alloc_id;
3505
3506 CURSOR c_alloc_details IS
3507 SELECT status_code, object_version_number
3508 FROM ozf_act_metrics_all
3509 WHERE activity_metric_id = p_alloc_id;
3510
3511 BEGIN
3512
3513
3514 SAVEPOINT publish_allocation;
3515
3516 --check if the allocation id is valid
3517 OPEN c_valid_alloc;
3518 FETCH c_valid_alloc INTO l_valid_alloc;
3519 CLOSE c_valid_alloc;
3520
3521 IF l_valid_alloc IS NULL THEN
3522 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3523 fnd_message.set_name('OZF', 'OZF_INVALID_ALLOC_ID');
3524 fnd_msg_pub.add;
3525 END IF;
3526 RAISE fnd_api.g_exc_error;
3527 END IF;
3528
3529 OPEN c_alloc_details;
3530 FETCH c_alloc_details INTO l_alloc_status, l_alloc_obj_ver;
3531 CLOSE c_alloc_details;
3532
3533 ozf_fund_allocations_pvt.publish_allocation(p_api_version => l_api_version
3534 ,p_init_msg_list => l_init_msg_list
3535 ,p_commit => l_commit
3536 ,p_validation_level => l_validation_level
3537 ,p_alloc_id => p_alloc_id
3538 ,p_alloc_status => l_alloc_status
3539 ,p_alloc_obj_ver => l_alloc_obj_ver
3540 ,x_return_status => x_return_status
3541 ,x_msg_count => x_msg_count
3542 ,x_msg_data => x_msg_data
3543 );
3544
3545 IF x_return_status = fnd_api.g_ret_sts_error THEN
3546 RAISE fnd_api.g_exc_error;
3547 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3548 RAISE fnd_api.g_exc_unexpected_error;
3549 END IF;
3550
3551 ozf_quota_allocations_pvt.publish_allocation(p_api_version => l_api_version
3552 ,p_init_msg_list => l_init_msg_list
3553 ,p_commit => l_commit
3554 ,p_validation_level => l_validation_level
3555 ,p_alloc_id => p_alloc_id
3556 ,x_return_status => x_return_status
3557 ,x_msg_count => x_msg_count
3558 ,x_msg_data => x_msg_data
3559 );
3560
3561 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3562 RAISE fnd_api.g_exc_unexpected_error;
3563 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3564 RAISE fnd_api.g_exc_error;
3565 END IF;
3566
3567 FND_MSG_PUB.Count_And_Get (
3568 p_encoded => FND_API.G_FALSE,
3569 p_count => x_msg_count,
3570 p_data => x_msg_data
3571 );
3572
3573 EXCEPTION
3574 WHEN FND_API.G_EXC_ERROR THEN
3575 ROLLBACK TO publish_allocation;
3576 x_return_status := FND_API.G_RET_STS_ERROR;
3577 -- Standard call to get message count and if count=1, get the message
3578 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3579 ,p_count => x_msg_count
3580 ,p_data => x_msg_data
3581 );
3582 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3583 ROLLBACK TO publish_allocation;
3584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3585 -- Standard call to get message count and if count=1, get the message
3586 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3587 ,p_count => x_msg_count
3588 ,p_data => x_msg_data
3589 );
3590 WHEN OTHERS THEN
3591 ROLLBACK TO publish_allocation;
3592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3593 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3594 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3595 END IF;
3596 -- Standard call to get message count and if count=1, get the message
3597 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3598 ,p_count => x_msg_count
3599 ,p_data => x_msg_data
3600 );
3601 END publish_allocation;
3602
3603 ---------------------------------------------------------------------
3604 -- PROCEDURE
3605 -- update_alloc_status
3606 --
3607 -- PURPOSE
3608 -- Update quota allocation status.
3609 --
3610 -- PARAMETERS
3611 -- p_alloc_id: allocation id
3612 -- p_alloc_status: allocation status
3613 --
3614 -- HISTORY
3615 -- 07/04/2005 kdass Created
3616 ---------------------------------------------------------------------
3617 PROCEDURE update_alloc_status(
3618 p_api_version IN NUMBER
3619 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3620 ,p_commit IN VARCHAR2 := fnd_api.g_false
3621 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
3622 ,p_alloc_id IN NUMBER
3623 ,p_alloc_status IN VARCHAR2
3624 ,x_return_status OUT NOCOPY VARCHAR2
3625 ,x_msg_count OUT NOCOPY NUMBER
3626 ,x_msg_data OUT NOCOPY VARCHAR2
3627 )
3628 IS
3629 l_api_name VARCHAR(30) := 'create_allocation';
3630 l_api_version NUMBER := p_api_version;
3631 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
3632 l_validation_level NUMBER := p_validation_level;
3633 l_commit VARCHAR2(1);
3634 l_alloc_obj_ver NUMBER;
3635
3636 CURSOR c_valid_alloc IS
3637 SELECT object_version_number
3638 FROM ozf_act_metrics_all
3639 WHERE activity_metric_id = p_alloc_id;
3640
3641 BEGIN
3642 null;
3643 /*
3644 SAVEPOINT update_alloc_status;
3645
3646 --check if the allocation id is valid and get the object_version_number
3647 OPEN c_valid_alloc;
3648 FETCH c_valid_alloc INTO l_alloc_obj_ver;
3649 CLOSE c_valid_alloc;
3650
3651 IF l_alloc_obj_ver IS NULL THEN
3652 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3653 fnd_message.set_name('OZF', 'OZF_INVALID_ALLOC_ID');
3654 fnd_msg_pub.add;
3655 END IF;
3656 RAISE fnd_api.g_exc_error;
3657 END IF;
3658
3659 ozf_fund_allocations_pvt.update_alloc_status(p_api_version => l_api_version
3660 ,p_init_msg_list => l_init_msg_list
3661 ,p_commit => l_commit
3662 ,p_validation_level => l_validation_level
3663 ,p_alloc_id => p_alloc_id
3664 ,p_alloc_status => p_alloc_status
3665 ,p_alloc_obj_ver => l_alloc_obj_ver
3666 ,x_return_status => x_return_status
3667 ,x_msg_count => x_msg_count
3668 ,x_msg_data => x_msg_data
3669 );
3670
3671 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3672 RAISE fnd_api.g_exc_unexpected_error;
3673 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
3674 RAISE fnd_api.g_exc_error;
3675 END IF;
3676
3677 FND_MSG_PUB.Count_And_Get (
3678 p_encoded => FND_API.G_FALSE,
3679 p_count => x_msg_count,
3680 p_data => x_msg_data
3681 );
3682 */
3683 EXCEPTION
3684 WHEN FND_API.G_EXC_ERROR THEN
3685 ROLLBACK TO update_alloc_status;
3686 x_return_status := FND_API.G_RET_STS_ERROR;
3687 -- Standard call to get message count and if count=1, get the message
3688 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3689 ,p_count => x_msg_count
3690 ,p_data => x_msg_data
3691 );
3692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3693 ROLLBACK TO update_alloc_status;
3694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695 -- Standard call to get message count and if count=1, get the message
3696 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3697 ,p_count => x_msg_count
3698 ,p_data => x_msg_data
3699 );
3700 WHEN OTHERS THEN
3701 ROLLBACK TO update_alloc_status;
3702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3703 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3704 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3705 END IF;
3706 -- Standard call to get message count and if count=1, get the message
3707 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3708 ,p_count => x_msg_count
3709 ,p_data => x_msg_data
3710 );
3711 END update_alloc_status;
3712
3713 END OZF_QUOTA_PUB;