[Home] [Help]
PACKAGE BODY: APPS.CN_QUOTA_PAY_ELEMENTS_PVT
Source
1 PACKAGE BODY CN_QUOTA_PAY_ELEMENTS_PVT AS
2 /* $Header: cnvqpeb.pls 115.16.115100.2 2004/05/13 00:52:57 jjhuang ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_QUOTA_PAY_ELEMENT_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvqpeb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := FND_GLOBAL.USER_ID;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := FND_GLOBAL.USER_ID;
10 G_LAST_UPDATE_LOGIN NUMBER := FND_GLOBAL.LOGIN_ID;
11
12 G_PROGRAM_TYPE VARCHAR2(30);
13
14 --+==========================================================================
15 --| Function : get_element_type
16 --| Desc :
17 --+==========================================================================
18 FUNCTION get_element_type(p_element_type_id NUMBER) RETURN VARCHAR2 IS
19
20 CURSOR get_element IS
21 select f.element_name element_name
22 from pay_element_types_f f
23 ,gl_sets_of_books glsob
24 ,cn_repositories cnr
25 where f.element_type_id = p_element_type_id
26 AND cnr.set_of_books_id = glsob.set_of_books_id
27 AND f.input_currency_code = glsob.currency_code;
28
29 l_name pay_element_types_f.element_name%TYPE;
30
31 BEGIN
32 open get_element;
33 fetch get_element into l_name;
34 close get_element;
35
36 return l_name;
37 END ;
38 --+==========================================================================
39 --| Function : check_input_exists
40 --| Desc :
41 --+==========================================================================
42 FUNCTION check_input_exists( p_quota_pay_element_id NUMBER )
43 RETURN NUMBER IS
44 l_found NUMBER := 0;
45 BEGIN
46 begin
47 select 1 into l_found FROM dual
48 where not exists
49 ( select 1
50 from cn_pay_element_inputs
51 where quota_pay_element_id = p_quota_pay_element_id);
52 return 0;
53 exception
54 when no_data_found THEN
55 l_found := 1;
56 return l_found;
57 end;
58 END;
59 --+==========================================================================
60 --| Function : check_delete_update_Allowed
61 --| Desc :
62 --| Modified by Julia Huang for Bug 2877207.
63 --+==========================================================================
64 FUNCTION check_delete_update_allowed( p_quota_pay_element_id NUMBER,
65 p_start_date DATE := NULL ,
66 p_end_date DATE := NULL,
67 p_quota_id NUMBER := NULL,
68 p_pay_element_type_id IN NUMBER := NULL )
69 RETURN NUMBER IS
70
71 l_found NUMBER := 0;
72
73 BEGIN
74 IF ( p_start_date IS NOT NULL
75 AND p_end_date IS NOT NULL
76 AND p_quota_id IS NOT NULL
77 AND p_pay_element_type_id IS NOT NULL)
78 THEN
79 BEGIN
80 SELECT 0 INTO l_found
81 FROM dual
82 WHERE NOT EXISTS
83 (
84 SELECT 1 --if query returns row, then we can't allow update/delete.
85 --if query doesnot return anything, we can do update/delete.
86 FROM
87 (--Quota_id is -1000 as carry over and regular quota_id as in cn_quotas
88 SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
89 MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
90 FROM cn_period_statuses ps,
91 cn_payment_transactions pt,
92 cn_quota_pay_elements qpe,
93 cn_salesreps cs,
94 cn_quotas cq
95 WHERE pt.credited_salesrep_id = cs.salesrep_id
96 AND pt.pay_period_id = ps.period_id
97 AND pt.pay_element_type_id = qpe.pay_element_type_id
98 AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
99 AND pt.quota_id = qpe.quota_id
100 AND nvl(cs.status, 'A') = qpe.status
101 AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
102 AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
103 AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
104 AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
105 AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
106 AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
107 GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
108 UNION ALL
109 --Quota_id is -1001 as 'PMTPLN_REC' type.
110 SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
111 MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
112 FROM cn_period_statuses ps,
113 cn_payment_transactions pt,
114 cn_quota_pay_elements qpe,
115 cn_salesreps cs,
116 cn_quotas cq
117 WHERE pt.credited_salesrep_id = cs.salesrep_id
118 AND pt.pay_period_id = ps.period_id
119 AND pt.pay_element_type_id = qpe.pay_element_type_id
120 AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
121 AND pt.quota_id = DECODE(qpe.quota_id,-1001, pt.quota_id,qpe.quota_id)
122 AND qpe.quota_id = -1001
123 AND pt.incentive_type_code = 'PMTPLN_REC'
124 AND nvl(cs.status, 'A') = qpe.status
125 AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
126 AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
127 AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
128 AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
129 AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
130 AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
131 GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
132 ) v
133 WHERE v.quota_pay_element_id = p_quota_pay_element_id
134 AND ( (p_end_date < v.end_date OR p_start_date > v.start_date)
135 OR v.quota_id <> p_quota_id
136 OR v.pay_element_type_id <> p_pay_element_type_id
137 )
138 );
139
140 EXCEPTION
141 WHEN NO_DATA_FOUND
142 THEN
143 l_found := 1;
144 END;
145
146 ELSE
147 BEGIN
148 SELECT 0 INTO l_found
149 FROM dual
150 WHERE NOT EXISTS
151 (
152 SELECT 1 --if query returns row, then we can't allow update/delete.
153 --if query doesnot return anything, we can do update/delete.
154 FROM
155 (--Quota_id is -1000 as carry over and regular quota_id as in cn_quotas
156 SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
157 MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
158 FROM cn_period_statuses ps,
159 cn_payment_transactions pt,
160 cn_quota_pay_elements qpe,
161 cn_salesreps cs,
162 cn_quotas cq
163 WHERE pt.credited_salesrep_id = cs.salesrep_id
164 AND pt.pay_period_id = ps.period_id
165 AND pt.pay_element_type_id = qpe.pay_element_type_id
166 AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
167 AND pt.quota_id = qpe.quota_id
168 AND nvl(cs.status, 'A') = qpe.status
169 AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
170 AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
171 AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
172 AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
173 AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
174 AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
175 GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
176 UNION ALL
177 --Quota_id is -1001 as 'PMTPLN_REC' type.
178 SELECT qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status,
179 MIN(ps.start_date) start_date, MAX(ps.end_date) end_date
180 FROM cn_period_statuses ps,
181 cn_payment_transactions pt,
182 cn_quota_pay_elements qpe,
183 cn_salesreps cs,
184 cn_quotas cq
185 WHERE pt.credited_salesrep_id = cs.salesrep_id
186 AND pt.pay_period_id = ps.period_id
187 AND pt.pay_element_type_id = qpe.pay_element_type_id
188 AND cq.quota_id = DECODE(pt.quota_id,-1000, cq.quota_id, pt.quota_id)
189 AND pt.quota_id = DECODE(qpe.quota_id,-1001, pt.quota_id,qpe.quota_id)
190 AND qpe.quota_id = -1001
191 AND pt.incentive_type_code = 'PMTPLN_REC'
192 AND nvl(cs.status, 'A') = qpe.status
193 AND cq.start_date <= NVL(cs.end_date_active, cq.start_date)
194 AND cs.start_date_active <= NVL( cq.end_date, cs.start_date_active)
195 AND qpe.start_date <= NVL( cq.end_date, qpe.start_date)
196 AND cq.start_date <= NVL( qpe.end_date, cq.start_date)
197 AND qpe.start_date <= NVL( cs.end_date_active, qpe.start_date)
198 AND cs.start_date_active <= NVL( qpe.end_date, cs.start_date_active)
199 GROUP BY qpe.quota_pay_element_id,qpe.quota_id,qpe.pay_element_type_id,qpe.status
200 ) v
201 WHERE v.quota_pay_element_id = p_quota_pay_element_id
202 AND (p_end_date < v.end_date OR p_start_date > v.start_date)
203 );
204
205 EXCEPTION
206 WHEN NO_DATA_FOUND
207 THEN
208 l_found := 1;
209 END;
210
211 END IF;
212
213 RETURN l_found;
214 END ;
215 --+==========================================================================
216 --| Function : get quota ID
217 --| Desc :
218 --| Modified by Julia Huang on 5/12/04 for bug 3626385. Use cn_quotas and
219 --| cn_lookups instead of cn_quota_lookups_v.
220 --+==========================================================================
221 FUNCTION get_Quota_id( p_quota_name VARCHAR2)
222 RETURN cn_quotas.quota_id%TYPE IS
223
224 l_quota_id cn_quotas.quota_id%TYPE;
225
226 --Bug 3626385
227 l_cnt NUMBER;
228
229 CURSOR get_qid_quotas IS
230 SELECT quota_id
231 FROM cn_quotas
232 WHERE name = p_quota_name;
233
234 CURSOR get_qid_lkup IS
235 SELECT v.quota_id
236 FROM
237 (SELECT TO_NUMBER(lookup_code) quota_id, meaning name
238 FROM cn_lookups
239 WHERE lookup_type = 'ELEMENT_TYPE'
240 ) v
241 WHERE v.name = p_quota_name;
242
243 BEGIN
244 /* commented out by Julia Huang for bug 3626385.
245 SELECT quota_id INTO l_quota_id
246 FROM cn_quota_lookups_v
247 WHERE name = p_quota_name;
248 */
249
250 l_cnt := 0;
251 FOR i IN get_qid_quotas
252 LOOP
253 l_quota_id := i.quota_id;
254 l_cnt := l_cnt + 1;
255 END LOOP;
256
257 IF l_cnt = 0
258 THEN
259 FOR i IN get_qid_lkup
260 LOOP
261 l_quota_id := i.quota_id;
262 l_cnt := l_cnt + 1;
263 END LOOP;
264 END IF;
265
266 IF l_cnt = 0
267 THEN
268 l_quota_id := NULL;
269 END IF;
270
271 RETURN l_quota_id;
272
273 /* commented out by Julia Huang for bug 3626385.
274 EXCEPTION
275 WHEN no_data_found THEN
276 RETURN NULL;
277 */
278 END get_quota_id;
279 --+==========================================================================
280 --| Proceudre : get Pay Element type ID
281 --| Desc :
282 --+==========================================================================
283 PROCEDURE get_pay_element_id( p_pay_element_name VARCHAR2,
284 p_start_date DATE,
285 p_end_Date DATE,
286 x_element_type_id OUT NOCOPY NUMBER ) IS
287
288 CURSOR get_functional_currency IS
289 SELECT currency_code
290 FROM gl_sets_of_books glsob,
291 cn_repositories cnr
292 WHERE cnr.set_of_books_id = glsob.set_of_books_id;
293
294 l_functional_currency gl_sets_of_books.currency_code%TYPE;
295
296 BEGIN
297
298 OPEN get_functional_currency;
299 FETCH get_functional_currency INTO l_functional_currency;
300 CLOSE get_functional_currency;
301
302 SELECT element_type_id
303 INTO x_element_type_id
304 FROM pay_element_types_f
305 WHERE element_name = p_pay_element_name
306 AND input_currency_code = l_functional_currency
307 AND p_start_date between effective_start_date and effective_end_date
308 AND effective_end_date >= nvl(p_end_date, effective_end_date);
309
310 EXCEPTION
311 WHEN no_data_found THEN
312 NULL;
313
314 WHEN too_many_rows THEN
315 NULL;
316 END get_pay_element_id;
317
318 --+==========================================================================
319 --| Procedure : valid_qpe_mapping
320 --| Desc : Procedure to validate quota pay element mapping
321 --+==========================================================================
322 PROCEDURE valid_qpe_mapping
323 (
324 x_return_status OUT NOCOPY VARCHAR2 ,
325 x_msg_count OUT NOCOPY NUMBER ,
326 x_msg_data OUT NOCOPY VARCHAR2 ,
327 p_quota_pay_element_rec IN quota_pay_element_rec_type
328 := G_MISS_QUOTA_PAY_ELEMENT_REC,
329 p_action IN VARCHAR2,
330 p_loading_status IN VARCHAR2,
331 x_loading_status OUT NOCOPY VARCHAR2
332 )
333 IS
334 l_api_name CONSTANT VARCHAR2(30) := 'valid_qpe_mapping';
335 l_null_date CONSTANT DATE := to_date('31-12-4000','DD-MM-YYYY');
336
337 l_dummy NUMBER;
338
339 l_quota_id NUMBER;
340
341 cursor get_old( p_quota_pay_element_id NUMBER) is
342 select *
343 from cn_quota_pay_elements
344 where quota_pay_element_id = p_quota_pay_element_id;
345
346 l_old_rec get_old%ROWTYPE;
347
348 BEGIN
349 -- Initialize API return status to success
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351 x_loading_status := p_loading_status;
352 -- API body
353
354 IF p_quota_pay_element_rec.quota_name IS NULL THEN
355 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
356 FND_MESSAGE.Set_Name('CN', 'CN_QUOTA_NAME_NOT_NULL');
357 FND_MSG_PUB.Add;
358 END IF;
359 x_loading_status := 'CN_QUOTA_NAME_NOT_NULL';
360 RAISE FND_API.G_EXC_ERROR ;
361 END IF;
362
363
364 IF p_quota_pay_element_rec.pay_element_name IS NULL THEN
365 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
366 FND_MESSAGE.Set_Name('CN', 'CN_PAY_ELEMENT_NAME_NOT_NULL');
367 FND_MSG_PUB.Add;
368 END IF;
369 x_loading_status := 'CN_PAY_ELEMENT_NAME_NOT_NULL';
370 RAISE FND_API.G_EXC_ERROR ;
371 END IF;
372
373
374 IF p_quota_pay_element_rec.quota_name IS NOT NULL and
375 p_quota_pay_element_rec.quota_id IS NULL THEN
376 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
377 FND_MESSAGE.Set_Name('CN', 'CN_QUOTA_NOT_EXISTS');
378 FND_MSG_PUB.Add;
379 END IF;
380 x_loading_status := 'CN_QUOTA_NOT_EXISTS';
381 RAISE FND_API.G_EXC_ERROR ;
382 END IF;
383
384
385 IF p_quota_pay_element_rec.start_date IS NULL THEN
386 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
387 FND_MESSAGE.Set_Name('CN', 'CN_PAY_ELEMENT_SD_NOT_NULL');
388 FND_MSG_PUB.Add;
389 END IF;
390 x_loading_status := 'CN_PAY_ELEMENT_SD_NOT_NULL';
391 RAISE FND_API.G_EXC_ERROR ;
392 END IF;
393
394 IF p_quota_pay_element_rec.end_date IS NULL THEN
395 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
396 FND_MESSAGE.Set_Name('CN', 'CN_PAY_ELEMENT_ED_NOT_NULL');
397 FND_MSG_PUB.Add;
398 END IF;
399 x_loading_status := 'CN_PAY_ELEMENT_ED_NOT_NULL';
400 RAISE FND_API.G_EXC_ERROR ;
401 END IF;
402
403 -- Invalid Date Range
404 IF p_quota_pay_element_rec.pay_start_date IS NOT NULL AND
405 p_quota_pay_element_rec.pay_end_date IS NOT NULL THEN
406
407 IF NOT ( p_quota_pay_element_rec.start_date
408 between p_quota_pay_element_rec.pay_start_date
409 and p_quota_pay_element_rec.pay_end_date) or
410 p_quota_pay_element_rec.pay_end_date <
411 p_quota_pay_element_rec.end_date THEN
412
413 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
414 FND_MESSAGE.Set_Name('CN', 'CN_PE_DATE_NOT_WITHIN_PAY_PE');
415 FND_MSG_PUB.Add;
416 END IF;
417 x_loading_status := 'CN_PE_DATE_NOT_WITHIN_PAY_PE';
418 RAISE FND_API.G_EXC_ERROR ;
419 END IF;
420
421 END IF;
422
423
424 IF p_quota_pay_element_rec.pay_element_name IS NOT NULL and
425 p_quota_pay_element_rec.pay_element_type_id IS NULL THEN
426 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
427 FND_MESSAGE.Set_Name('CN', 'CN_PAY_ELEMENT_NOT_EXISTS');
428 FND_MSG_PUB.Add;
429 END IF;
430 x_loading_status := 'CN_PAY_ELEMENT_NOT_EXISTS';
431 RAISE FND_API.G_EXC_ERROR ;
432 END IF;
433
434
435
436 -- Check for duplicate
437 BEGIN
438 SELECT 1 INTO l_dummy FROM dual
439 WHERE NOT EXISTS
440 ( SELECT 1
441 FROM cn_quota_pay_elements
442 WHERE quota_id = p_quota_pay_element_rec.quota_id
443 AND pay_element_type_id = p_quota_pay_element_rec.pay_element_type_id
444 AND nvl(status,'A') = nvl(p_quota_pay_element_rec.status,'A')
445 AND start_date = p_quota_pay_element_rec.start_date
446 AND ( (end_date = p_quota_pay_element_rec.end_date) OR
447 (end_date IS NULL AND p_quota_pay_element_rec.end_date IS NULL) )
448 AND ((p_quota_pay_element_rec.quota_pay_element_id IS NOT NULL AND
449 quota_pay_element_id <> p_quota_pay_element_rec.quota_pay_element_id)
450 OR
451 (p_quota_pay_element_rec.quota_pay_element_id IS NULL))
452 );
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
456 FND_MESSAGE.Set_Name('CN', 'CN_QUOTA_PAY_ELEMENT_EXISTS');
457 FND_MSG_PUB.Add;
458 END IF;
459 x_loading_status := 'CN_QUOTA_PAY_ELEMENT_EXISTS';
460 RAISE FND_API.G_EXC_ERROR ;
461 END;
462
463 -- Check if date range invalid
464 -- will check : if start_date is null
465 -- if start_date/end_date is missing
466 -- if start_date > end_date
467 IF ( (cn_api.invalid_date_range
468 (p_start_date => p_quota_pay_element_rec.start_date,
469 p_end_date => p_quota_pay_element_rec.end_date,
470 p_end_date_nullable => FND_API.G_TRUE,
471 p_loading_status => x_loading_status,
472 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
473 RAISE FND_API.G_EXC_ERROR ;
474 END IF;
475
476 -- Check for Overlap
477 BEGIN
478 SELECT 1 INTO l_dummy FROM dual
479 WHERE NOT EXISTS
480 ( SELECT 1
481 FROM cn_quota_pay_elements
482 WHERE (((end_date IS NULL)
483 AND (p_quota_pay_element_rec.end_date IS NULL))
484 OR
485 ((end_date IS NULL) AND
486 (p_quota_pay_element_rec.end_date IS NOT NULL) AND
487 ((p_quota_pay_element_rec.start_date >= start_date) OR
488 (start_date BETWEEN p_quota_pay_element_rec.start_date
489 AND p_quota_pay_element_rec.end_date))
490 )
491 OR
492 ((end_date IS NOT NULL) AND
493 (p_quota_pay_element_rec.end_date IS NULL) AND
494 ((p_quota_pay_element_rec.start_date <= start_date) OR
495 (p_quota_pay_element_rec.start_date BETWEEN start_date
496 AND end_date))
497 )
498 OR
499 ((end_date IS NOT NULL) AND
500 (p_quota_pay_element_rec.end_date IS NOT NULL) AND
501 ((start_date BETWEEN p_quota_pay_element_rec.start_date
502 AND p_quota_pay_element_rec.end_date) OR
503 (end_date BETWEEN p_quota_pay_element_rec.start_date
504 AND p_quota_pay_element_rec.end_date) OR
505 (p_quota_pay_element_rec.start_date BETWEEN start_date
506 AND end_date))
507 )
508 )
509 AND ((p_quota_pay_element_rec.quota_pay_element_id IS NOT NULL AND
510 quota_pay_element_id <> p_quota_pay_element_rec.quota_pay_element_id)
511 OR
512 (p_quota_pay_element_rec.quota_pay_element_id IS NULL))
513 AND quota_id = p_quota_pay_element_rec.quota_id
514 --AND pay_element_type_id = p_quota_pay_element_rec.pay_element_type_id
515 AND nvl(status,'A') = nvl(p_quota_pay_element_rec.status,'A')
516 );
517 EXCEPTION
518 WHEN NO_DATA_FOUND THEN
519 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
520 FND_MESSAGE.SET_NAME ('CN' , 'CN_QUOTA_PAY_ELEMENT_OVERLAPS');
521 FND_MSG_PUB.Add;
522 END IF;
523 x_loading_status := 'CN_QUOTA_PAY_ELEMENT_OVERLAPS';
524 RAISE FND_API.G_EXC_ERROR ;
525 END;
526
527 IF p_quota_pay_element_rec.quota_pay_element_id IS NOT NULL
528 AND p_quota_pay_element_rec.quota_pay_element_id <> 0 THEN
529
530 open get_old(p_quota_pay_element_rec.quota_pay_element_id);
531 fetch get_old into l_old_rec;
532 close get_old;
533 if trunc(p_quota_pay_element_rec.start_date) > trunc(l_old_rec.start_date) or
534 trunc(p_quota_pay_element_rec.end_date) < trunc(l_old_rec.end_date) or
535 p_quota_pay_element_rec.pay_element_type_id <> l_old_rec.pay_element_type_id or
536 p_quota_pay_element_rec.quota_id <> l_old_rec.quota_id or
537 p_quota_pay_element_rec.status <> l_old_rec.status
538 then
539 if check_delete_update_allowed(p_quota_pay_element_rec.quota_pay_element_id,
540 p_quota_pay_element_rec.start_date,
541 p_quota_pay_element_rec.end_date,
542 p_quota_pay_element_rec.quota_id,
543 p_quota_pay_element_rec.pay_element_type_id) > 0 THEN
544 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
545 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAY_ELEMENT_ALREADY_USED');
546 FND_MSG_PUB.Add;
547 END IF;
548 x_loading_status := 'CN_PAY_ELEMENT_ALREADY_USED';
549 RAISE FND_API.G_EXC_ERROR ;
550 end if;
551
552 if p_quota_pay_element_rec.pay_element_type_id <> l_old_rec.pay_element_type_id THEN
553 if check_input_exists(p_quota_pay_element_rec.quota_pay_element_id) > 0 THEN
554 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
555 FND_MESSAGE.SET_NAME ('CN' , 'CN_ELEMENT_INPUT_EXISTS');
556 FND_MSG_PUB.Add;
557 END IF;
558 x_loading_status := 'CN_ELEMENT_INPUT_EXISTS';
559 RAISE FND_API.G_EXC_ERROR ;
560 end if;
561
562 end if;
563 end if;
564
565 END IF;
566
567
568 -- End of API body.
569 << end_valid_pp_assign >>
570 NULL ;
571 EXCEPTION
572 WHEN FND_API.G_EXC_ERROR THEN
573 x_return_status := FND_API.G_RET_STS_ERROR ;
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
576 x_loading_status := 'UNEXPECTED_ERR';
577 WHEN OTHERS THEN
578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
579 x_loading_status := 'UNEXPECTED_ERR';
580 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
581 THEN
582 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
583 END IF;
584
585 END valid_qpe_mapping;
586 --+==========================================================================
587 --| Procedure : Create_quota_pay_element
588 --| Desc : Procedure to create a new payment plan assignment to salesrep
589 --+==========================================================================
590
591 PROCEDURE Create_quota_pay_element
592 (
593 p_api_version IN NUMBER,
594 p_init_msg_list IN VARCHAR2,
595 p_commit IN VARCHAR2,
596 p_validation_level IN NUMBER,
597 x_return_status OUT NOCOPY VARCHAR2,
598 x_msg_count OUT NOCOPY NUMBER,
599 x_msg_data OUT NOCOPY VARCHAR2,
600 p_quota_pay_element_rec IN quota_pay_element_rec_type
601 := G_MISS_QUOTA_PAY_ELEMENT_REC,
602 x_quota_pay_element_id OUT NOCOPY NUMBER,
603 x_loading_status OUT NOCOPY VARCHAR2
604 ) IS
605
606 l_api_name CONSTANT VARCHAR2(30) := 'Create_quota_pay_element';
607 l_api_version CONSTANT NUMBER := 1.0;
608
609 l_quota_pay_element_rec quota_pay_element_rec_type := G_MISS_QUOTA_PAY_ELEMENT_REC;
610 l_qpe_row cn_quota_pay_elements%ROWTYPE ;
611
612 l_salesrep_id cn_srp_pmt_plans.salesrep_id%TYPE;
613 l_role_id cn_srp_pmt_plans.role_id%TYPE;
614 l_start_date cn_srp_pmt_plans.start_date%TYPE;
615 l_end_date cn_srp_pmt_plans.end_date%TYPE;
616 l_action VARCHAR2(30) := 'CREATE';
617
618 BEGIN
619 -- Standard Start of API savepoint
620 SAVEPOINT Create_quota_pay_element;
621 -- Standard call to check for call compatibility.
622 IF NOT FND_API.compatible_api_call
623 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
624 THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627 -- Initialize message list if p_init_msg_list is set to TRUE.
628 IF FND_API.to_Boolean( p_init_msg_list ) THEN
629 FND_MSG_PUB.initialize;
630 END IF;
631 -- Initialize API return status to success
632 x_return_status := FND_API.G_RET_STS_SUCCESS;
633 x_loading_status := 'CN_INSERTED';
634 -- Assign the parameter to a local variable
635
636 l_quota_pay_element_rec := p_quota_pay_element_rec;
637
638 -- Trim spaces before/after user input string, get Value-Id para assigned
639 SELECT
640 Decode(p_quota_pay_element_rec.quota_pay_element_id,
641 FND_API.G_MISS_NUM, NULL ,
642 p_quota_pay_element_rec.quota_pay_element_id),
643 Decode(p_quota_pay_element_rec.quota_id,
644 FND_API.G_MISS_NUM, NULL ,
645 p_quota_pay_element_rec.quota_id ),
646 Decode(p_quota_pay_element_rec.pay_element_type_id,
647 FND_API.G_MISS_NUM, NULL ,
648 p_quota_pay_element_rec.pay_element_type_id),
649 Decode(p_quota_pay_element_rec.status,
650 FND_API.G_MISS_CHAR, NULL ,
651 p_quota_pay_element_rec.status),
652 Decode(p_quota_pay_element_rec.start_date,
653 FND_API.G_MISS_DATE,To_date(NULL) ,
654 trunc(p_quota_pay_element_rec.start_date)),
655 Decode(p_quota_pay_element_rec.end_date,
656 FND_API.G_MISS_DATE,To_date(NULL) ,
657 trunc(p_quota_pay_element_rec.end_date)),
658 Decode(p_quota_pay_element_rec.quota_name,
659 FND_API.G_MISS_CHAR, NULL ,
660 p_quota_pay_element_rec.quota_name),
661 Decode(p_quota_pay_element_rec.pay_element_name,
662 FND_API.G_MISS_CHAR, NULL ,
663 p_quota_pay_element_rec.pay_element_name),
664 Decode(p_quota_pay_element_rec.pay_start_date,
665 FND_API.G_MISS_DATE, NULL ,
666 p_quota_pay_element_rec.pay_start_date),
667 Decode(p_quota_pay_element_rec.pay_end_date,
668 FND_API.G_MISS_DATE, NULL ,
669 p_quota_pay_element_rec.pay_end_date)
670 INTO
671 l_quota_pay_element_rec.quota_pay_element_id,
672 l_quota_pay_element_rec.quota_id,
673 l_quota_pay_element_rec.pay_element_type_id,
674 l_quota_pay_element_rec.status,
675 l_quota_pay_element_rec.start_date,
676 l_quota_pay_element_rec.end_date,
677 l_quota_pay_element_rec.quota_name,
678 l_quota_pay_element_rec.pay_element_name,
679 l_quota_pay_element_rec.pay_start_date,
680 l_quota_pay_element_rec.pay_end_date
681 FROM dual;
682
683 -- get the quota id
684
685 l_quota_pay_element_rec.quota_id :=
686 get_quota_id(l_quota_pay_element_rec.quota_name);
687
688 -- get pay element name
689 get_pay_element_id
690 (p_pay_element_name => l_quota_pay_element_rec.pay_element_name,
691 p_start_date => l_quota_pay_element_rec.start_date,
692 p_end_Date => l_quota_pay_element_rec.end_date,
693 x_element_type_id => l_quota_pay_element_rec.pay_element_type_id);
694
695 --
696 -- Valid payment plan assignment
697 --
698 valid_qpe_mapping
699 ( x_return_status => x_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_quota_pay_element_rec => l_quota_pay_element_rec,
703 p_action => l_action,
704 p_loading_status => x_loading_status,
705 x_loading_status => x_loading_status
706 );
707 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
708 RAISE FND_API.G_EXC_ERROR ;
709 ELSE
710 -- Create cn_quota_pay_elements
711 cn_quota_pay_elements_pkg.insert_row
712 (x_quota_pay_element_id => l_quota_pay_element_rec.quota_pay_element_id
713 ,p_quota_id => l_quota_pay_element_rec.quota_id
714 ,p_pay_element_type_id => l_quota_pay_element_rec.pay_element_type_id
715 ,p_status => l_quota_pay_element_rec.status
716 ,p_start_date => l_quota_pay_element_rec.start_date
717 ,p_end_date => l_quota_pay_element_rec.end_date
718 ,p_last_update_date => G_LAST_UPDATE_DATE
719 ,p_last_updated_by => G_LAST_UPDATED_BY
720 ,p_creation_date => G_CREATION_DATE
721 ,p_created_by => G_CREATED_BY
722 ,p_last_update_login => G_LAST_UPDATE_LOGIN
723 );
724 END IF;
725 --
726 -- End of API body.
727 --
728
729 -- Standard check of p_commit.
730 IF FND_API.To_Boolean( p_commit ) THEN
731 COMMIT WORK;
732 END IF;
733 -- Standard call to get message count and if count is 1, get message info.
734 FND_MSG_PUB.Count_And_Get
735 (
736 p_count => x_msg_count ,
737 p_data => x_msg_data ,
738 p_encoded => FND_API.G_FALSE
739 );
740
741 EXCEPTION
742 WHEN FND_API.G_EXC_ERROR THEN
743 ROLLBACK TO Create_quota_pay_element;
744 x_return_status := FND_API.G_RET_STS_ERROR ;
745 FND_MSG_PUB.Count_And_Get
746 (
747 p_count => x_msg_count ,
748 p_data => x_msg_data ,
749 p_encoded => FND_API.G_FALSE
750 );
751
752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
753 ROLLBACK TO Create_quota_pay_element;
754 x_loading_status := 'UNEXPECTED_ERR';
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
756 FND_MSG_PUB.Count_And_Get
757 (
758 p_count => x_msg_count ,
759 p_data => x_msg_data ,
760 p_encoded => FND_API.G_FALSE
761 );
762 WHEN OTHERS THEN
763 ROLLBACK TO Create_quota_pay_element;
764 x_loading_status := 'UNEXPECTED_ERR';
765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
766 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
767 THEN
768 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
769 END IF;
770 FND_MSG_PUB.Count_And_Get
771 (
772 p_count => x_msg_count ,
773 p_data => x_msg_data ,
774 p_encoded => FND_API.G_FALSE
775 );
776
777 END create_quota_pay_element;
778
779 --+==========================================================================
780 --| Procedure : Update_quota_pay_element
781 --| Desc :
782 --+==========================================================================
783
784 PROCEDURE Update_quota_pay_element
785 (
786 p_api_version IN NUMBER,
787 p_init_msg_list IN VARCHAR2,
788 p_commit IN VARCHAR2,
789 p_validation_level IN NUMBER,
790 x_return_status OUT NOCOPY VARCHAR2,
791 x_msg_count OUT NOCOPY NUMBER,
792 x_msg_data OUT NOCOPY VARCHAR2,
793 po_quota_pay_element_rec IN quota_pay_element_rec_type
794 := G_MISS_quota_pay_element_rec,
795 p_quota_pay_element_rec IN quota_pay_element_rec_type:=G_MISS_QUOTA_PAY_ELEMENT_REC,
796 x_loading_status OUT NOCOPY VARCHAR2
797 ) IS
798
799 l_api_name CONSTANT VARCHAR2(30) := 'Uupdate_Quota_Pay_Element';
800 l_api_version CONSTANT NUMBER := 1.0;
801
802 l_quota_pay_element_rec quota_pay_element_rec_type := G_MISS_QUOTA_PAY_ELEMENT_REC ;
803 l_action VARCHAR2(30) := 'UPDATE';
804
805 BEGIN
806
807 -- Standard Start of API savepoint
808 SAVEPOINT Update_quota_pay_element;
809 -- Standard call to check for call compatibility.
810 IF NOT FND_API.compatible_api_call
811 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
812 THEN
813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
814 END IF;
815 -- Initialize message list if p_init_msg_list is set to TRUE.
816 IF FND_API.to_Boolean( p_init_msg_list ) THEN
817 FND_MSG_PUB.initialize;
818 END IF;
819 -- Initialize API return status to success
820 x_return_status := FND_API.G_RET_STS_SUCCESS;
821 x_loading_status := 'CN_UPDATED';
822
823 -- Assign the parameter to a local variable
824 l_quota_pay_element_rec := p_quota_pay_element_rec;
825
826 -- Trim spaces before/after user input string (New record) if missing,
827 -- assign the old value into it
828
829 SELECT
830 Decode(p_quota_pay_element_rec.quota_pay_element_id,
831 FND_API.G_MISS_NUM, null,
832 p_quota_pay_element_rec.quota_pay_element_id),
833 Decode(p_quota_pay_element_rec.quota_id,
834 FND_API.G_MISS_NUM, null,
835 p_quota_pay_element_rec.quota_id),
836 Decode(p_quota_pay_element_rec.pay_element_type_id,
837 FND_API.G_MISS_NUM, null,
838 p_quota_pay_element_rec.pay_element_type_id),
839 Decode(p_quota_pay_element_rec.status,
840 FND_API.G_MISS_CHAR, p_quota_pay_element_rec.status,
841 Ltrim(Rtrim(p_quota_pay_element_rec.status))),
842 Decode(p_quota_pay_element_rec.start_date,
843 FND_API.G_MISS_DATE, p_quota_pay_element_rec.start_date,
844 trunc(p_quota_pay_element_rec.start_date)),
845 Decode(p_quota_pay_element_rec.end_date,
846 FND_API.G_MISS_DATE, p_quota_pay_element_rec.end_date,
847 trunc(p_quota_pay_element_rec.end_date)),
848 Decode(p_quota_pay_element_rec.quota_name,
849 FND_API.G_MISS_CHAR, NULL ,
850 p_quota_pay_element_rec.quota_name),
851 Decode(p_quota_pay_element_rec.pay_element_name,
852 FND_API.G_MISS_CHAR, NULL ,
853 p_quota_pay_element_rec.pay_element_name)
854 INTO
855 l_quota_pay_element_rec.quota_pay_element_id,
856 l_quota_pay_element_rec.quota_id,
857 l_quota_pay_element_rec.pay_element_type_id,
858 l_quota_pay_element_rec.status,
859 l_quota_pay_element_rec.start_date,
860 l_quota_pay_element_rec.end_date,
861 l_quota_pay_element_rec.quota_name,
862 l_quota_pay_element_rec.pay_element_name
863 FROM dual;
864
865 l_quota_pay_element_rec.quota_id :=
866 get_quota_id(l_quota_pay_element_rec.quota_name);
867
868
869 -- get pay element name
870 get_pay_element_id
871 (p_pay_element_name => l_quota_pay_element_rec.pay_element_name,
872 p_start_date => l_quota_pay_element_rec.start_date,
873 p_end_Date => l_quota_pay_element_rec.end_date,
874 x_element_type_id => l_quota_pay_element_rec.pay_element_type_id);
875
876
877 -- Valid payment plan assignment
878 valid_qpe_mapping
879 ( x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 p_quota_pay_element_rec => l_quota_pay_element_rec,
883 p_action => l_action,
884 p_loading_status => x_loading_status,
885 x_loading_status => x_loading_status
886 );
887
888 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
889 RAISE FND_API.G_EXC_ERROR ;
890 ELSE
891 -- Update
892 cn_quota_pay_elements_pkg.update_row
893 (p_quota_pay_element_id => l_quota_pay_element_rec.quota_pay_element_id
894 ,p_quota_id => l_quota_pay_element_rec.quota_id
895 ,p_pay_element_type_id => l_quota_pay_element_rec.pay_element_type_id
896 ,p_status => l_quota_pay_element_rec.status
897 ,p_start_date => l_quota_pay_element_rec.start_date
898 ,p_end_date => l_quota_pay_element_rec.end_date
899 ,p_last_update_date => G_LAST_UPDATE_DATE
900 ,p_last_updated_by => G_LAST_UPDATED_BY
901 ,p_last_update_login => G_LAST_UPDATE_LOGIN
902 );
903 END IF;
904 -- Standard check of p_commit.
905 IF FND_API.To_Boolean( p_commit ) THEN
906 COMMIT WORK;
907 END IF;
908 -- Standard call to get message count and if count is 1, get message info.
909 FND_MSG_PUB.Count_And_Get
910 (
911 p_count => x_msg_count ,
912 p_data => x_msg_data ,
913 p_encoded => FND_API.G_FALSE
914 );
915
916 EXCEPTION
917 WHEN FND_API.G_EXC_ERROR THEN
918 ROLLBACK TO Update_quota_pay_element;
919 x_return_status := FND_API.G_RET_STS_ERROR ;
920 FND_MSG_PUB.Count_And_Get
921 (
922 p_count => x_msg_count ,
923 p_data => x_msg_data ,
924 p_encoded => FND_API.G_FALSE
925 );
926 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
927 ROLLBACK TO Update_quota_pay_element;
928 x_loading_status := 'UNEXPECTED_ERR';
929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
930 FND_MSG_PUB.Count_And_Get
931 (
932 p_count => x_msg_count ,
933 p_data => x_msg_data ,
934 p_encoded => FND_API.G_FALSE
935 );
936 WHEN OTHERS THEN
937 ROLLBACK TO Update_quota_pay_element;
938 x_loading_status := 'UNEXPECTED_ERR';
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
940 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
941 THEN
942 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
943 END IF;
944 FND_MSG_PUB.Count_And_Get
945 (
946 p_count => x_msg_count ,
947 p_data => x_msg_data ,
948 p_encoded => FND_API.G_FALSE
949 );
950 END Update_quota_pay_element;
951 --============================================================================
952 --| Procedure : Delete_quota_pay_element
953 --|
954 --============================================================================
955 PROCEDURE Delete_quota_pay_element
956 (
957 p_api_version IN NUMBER,
958 p_init_msg_list IN VARCHAR2 := CN_API.G_FALSE,
959 p_commit IN VARCHAR2 := CN_API.G_FALSE,
960 p_validation_level IN NUMBER := CN_API.G_VALID_LEVEL_FULL,
961 x_return_status OUT NOCOPY VARCHAR2,
962 x_msg_count OUT NOCOPY NUMBER,
963 x_msg_data OUT NOCOPY VARCHAR2,
964 p_quota_pay_element_id IN NUMBER,
965 x_loading_status OUT NOCOPY VARCHAR2
966 ) IS
967
968 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Quota_Pay_Element';
969 l_api_version CONSTANT NUMBER := 1.0;
970
971 BEGIN
972
973 -- Standard Start of API savepoint
974 SAVEPOINT Delete_Quota_Pay_element;
975
976 -- Standard call to check for call compatibility.
977 IF NOT FND_API.compatible_api_call
978 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
979 THEN
980 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
981 END IF;
982 -- Initialize message list if p_init_msg_list is set to TRUE.
983 IF FND_API.to_Boolean( p_init_msg_list ) THEN
984 FND_MSG_PUB.initialize;
985 END IF;
986 -- Initialize API return status to success
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988 x_loading_status := 'CN_DELETED';
989
990 IF p_quota_pay_element_id IS NOT NULL THEN
991
992 if check_delete_update_allowed(p_quota_pay_element_id) > 0 THEN
993 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
994 FND_MESSAGE.SET_NAME ('CN' , 'CN_PAY_ELEMENT_ALREADY_USED');
995 FND_MSG_PUB.Add;
996 END IF;
997 x_loading_status := 'CN_PAY_ELEMENT_ALREADY_USED';
998 RAISE FND_API.G_EXC_ERROR ;
999 end if;
1000
1001 if check_input_exists(p_quota_pay_element_id) > 0 THEN
1002 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1003 FND_MESSAGE.SET_NAME ('CN' , 'CN_ELEMENT_INPUT_EXISTS');
1004 FND_MSG_PUB.Add;
1005 END IF;
1006 x_loading_status := 'CN_ELEMENT_INPUT_EXISTS';
1007 RAISE FND_API.G_EXC_ERROR ;
1008 end if;
1009
1010
1011 END IF;
1012
1013 -- Delete record
1014 cn_quota_pay_elements_pkg.delete_row
1015 (p_quota_pay_element_id =>p_quota_pay_element_id);
1016 --
1017 -- End of API body.
1018 --
1019 -- Standard check of p_commit.
1020 IF FND_API.To_Boolean( p_commit ) THEN
1021 COMMIT WORK;
1022 END IF;
1023 -- Standard call to get message count and if count is 1, get message info.
1024 FND_MSG_PUB.Count_And_Get
1025 (
1026 p_count => x_msg_count ,
1027 p_data => x_msg_data ,
1028 p_encoded => FND_API.G_FALSE
1029 );
1030
1031 EXCEPTION
1032 WHEN FND_API.G_EXC_ERROR THEN
1033 ROLLBACK TO Delete_Quota_Pay_element;
1034 x_return_status := FND_API.G_RET_STS_ERROR ;
1035 FND_MSG_PUB.Count_And_Get
1036 (
1037 p_count => x_msg_count ,
1038 p_data => x_msg_data ,
1039 p_encoded => FND_API.G_FALSE
1040 );
1041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1042 ROLLBACK TO Delete_Quota_Pay_Element;
1043 x_loading_status := 'UNEXPECTED_ERR';
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1045 FND_MSG_PUB.Count_And_Get
1046 (
1047 p_count => x_msg_count ,
1048 p_data => x_msg_data ,
1049 p_encoded => FND_API.G_FALSE
1050 );
1051 WHEN OTHERS THEN
1052 ROLLBACK TO Delete_Quota_Pay_element;
1053 x_loading_status := 'UNEXPECTED_ERR';
1054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1055 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1056 THEN
1057 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1058 END IF;
1059 FND_MSG_PUB.Count_And_Get
1060 (
1061 p_count => x_msg_count ,
1062 p_data => x_msg_data ,
1063 p_encoded => FND_API.G_FALSE
1064 );
1065
1066 END Delete_Quota_Pay_Element;
1067 --============================================================================
1068 --| Procedure : Get_quota_pay_Element
1069 --|
1070 --============================================================================
1071 PROCEDURE Get_quota_pay_element
1072 ( p_api_version IN NUMBER,
1073 p_init_msg_list IN VARCHAR2,
1074 p_commit IN VARCHAR2,
1075 p_validation_level IN NUMBER,
1076 x_return_status OUT NOCOPY VARCHAR2,
1077 x_msg_count OUT NOCOPY NUMBER,
1078 x_msg_data OUT NOCOPY VARCHAR2,
1079 p_quota_name IN cn_quotas.name%TYPE,
1080 p_pay_element_name IN pay_element_types.element_name%TYPE,
1081 p_start_record IN NUMBER,
1082 p_increment_count IN NUMBER,
1083 p_order_by IN VARCHAR2,
1084 x_quota_pay_element_tbl OUT NOCOPY quota_pay_element_out_tbl_type,
1085 x_total_records OUT NOCOPY NUMBER,
1086 x_status OUT NOCOPY VARCHAR2,
1087 x_loading_status OUT NOCOPY VARCHAR2
1088 ) IS
1089
1090 TYPE quotacurtype IS ref CURSOR;
1091
1092 quota_cur quotacurtype;
1093
1094
1095 l_api_name CONSTANT VARCHAR2(30) := 'Get_quota_pay_element';
1096 l_api_version CONSTANT NUMBER := 1.0;
1097
1098 l_counter NUMBER;
1099
1100 l_quota_pay_element_id NUMBER;
1101 l_quota_id NUMBER;
1102 l_pay_element_type_id NUMBER;
1103 l_status cn_quota_pay_elements.status%TYPE;
1104 l_start_date DATE;
1105 l_end_date DATE;
1106
1107 l_quota_name cn_quotas.name%type;
1108 l_element_name pay_element_types.element_name%TYPE;
1109 l_e_start_date DATE;
1110 l_e_end_date DATE;
1111
1112 l_select varchar2(4000) := 'SELECT cqpe.quota_pay_element_id,
1113 cqpe.quota_id,
1114 cqpe.pay_element_type_id,
1115 cqpe.status,
1116 cqpe.start_date,
1117 cqpe.end_date,
1118 cq.name,
1119 cpet.element_name,
1120 cpet.effective_start_date,
1121 cpet.effective_end_Date
1122 FROM cn_quota_pay_elements cqpe,
1123 pay_element_types_f cpet,
1124 cn_quota_lookups_v cq,
1125 gl_sets_of_books glsob,
1126 cn_repositories cnr
1127 where
1128 cnr.set_of_books_id = glsob.set_of_books_id
1129 AND cpet.input_currency_code = glsob.currency_code
1130 AND cqpe.quota_id = cq.quota_id
1131 AND cqpe.pay_element_type_id = cpet.element_type_id
1132 And cqpe.start_date >= cpet.effective_start_date
1133 AND cqpe.end_date <= cpet.effective_end_Date
1134 AND upper(cq.name) like upper(:B1)
1135 AND upper(cpet.element_name) like upper(:B2) ';
1136
1137 l_select1 varchar2(4000) := 'SELECT cqpe.quota_pay_element_id,
1138 cqpe.quota_id,
1139 cqpe.pay_element_type_id,
1140 cqpe.status,
1141 cqpe.start_date,
1142 cqpe.end_date,
1143 cq.name,
1144 cpet.element_name,
1145 cpet.effective_start_date,
1146 cpet.effective_end_Date
1147 FROM cn_quota_pay_elements cqpe,
1148 pay_element_types_f cpet,
1149 cn_quota_lookups_v cq,
1150 gl_sets_of_books glsob,
1151 cn_repositories cnr
1152 where
1153 cnr.set_of_books_id = glsob.set_of_books_id
1154 AND cpet.input_currency_code = glsob.currency_code
1155 AND cqpe.quota_id = cq.quota_id
1156 AND cqpe.pay_element_type_id = cpet.element_type_id
1157 And cqpe.start_date >= cpet.effective_start_date
1158 AND cqpe.end_date <= cpet.effective_end_Date ';
1159
1160 BEGIN
1161
1162 --
1163 -- Standard Start of API savepoint
1164 --
1165 SAVEPOINT Get_quota_pay_element;
1166 --
1167 -- Standard call to check for call compatibility.
1168 --
1169 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1170 p_api_version ,
1171 l_api_name ,
1172 G_PKG_NAME )
1173 THEN
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 END IF;
1176 --
1177 -- Initialize message list if p_init_msg_list is set to TRUE.
1178 --
1179 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1180 FND_MSG_PUB.initialize;
1181 END IF;
1182 --
1183 -- Initialize API return status to success
1184 --
1185 x_return_status := FND_API.G_RET_STS_SUCCESS;
1186 x_loading_status := 'SELECTED';
1187 --
1188 -- API body
1189 --
1190 l_counter := 0;
1191
1192 x_total_records := 0;
1193
1194 if (( p_quota_name is null or p_quota_name = '%') and
1195 (p_pay_element_name is null or p_pay_element_name = '%')) THEN
1196
1197 l_select := l_select1;
1198
1199 end if;
1200
1201 if (( p_quota_name is null or p_quota_name = '%') and
1202 (p_pay_element_name is null or p_pay_element_name = '%')) THEN
1203
1204 OPEN quota_cur FOR l_select;
1205
1206 else
1207 OPEN quota_cur FOR l_select using p_quota_name, p_pay_element_name ;
1208 end if;
1209
1210 LOOP
1211
1212 FETCH quota_cur INTO
1213 l_quota_pay_element_id
1214 ,l_quota_id
1215 ,l_pay_element_type_id
1216 ,l_status
1217 ,l_start_date
1218 ,l_end_date
1219 ,l_quota_name
1220 ,l_element_name
1221 ,l_e_start_date
1222 ,l_e_end_date;
1223
1224 EXIT WHEN quota_cur%notfound;
1225 x_total_records := x_total_records + 1;
1226
1227 IF (l_counter + 1 BETWEEN p_start_record
1228 AND (p_start_record + p_increment_count - 1))
1229 THEN
1230 x_quota_pay_element_tbl(l_counter).quota_pay_element_id
1231 := l_quota_pay_element_id;
1232
1233 x_quota_pay_element_tbl(l_counter).quota_id
1234 := l_quota_id;
1235
1236 x_quota_pay_element_tbl(l_counter).pay_element_type_id
1237 := l_pay_element_type_id;
1238
1239 x_quota_pay_element_tbl(l_counter).status
1240 := l_status;
1241
1242 x_quota_pay_element_tbl(l_counter).start_date
1243 := l_start_date;
1244
1245 x_quota_pay_element_tbl(l_counter).end_date
1246 := l_end_date;
1247
1248 x_quota_pay_element_tbl(l_counter).quota_name
1249 := l_quota_name;
1250
1251 x_quota_pay_element_tbl(l_counter).pay_element_name
1252 := l_element_name;
1253
1254 x_quota_pay_element_tbl(l_counter).pay_start_date
1255 := l_e_start_date;
1256
1257 x_quota_pay_element_tbl(l_counter).pay_end_date
1258 := l_e_end_Date;
1259
1260 END IF;
1261
1262 l_counter := l_counter + 1;
1263
1264 END LOOP;
1265 CLOSE quota_cur;
1266
1267 x_loading_status := 'SELECTED';
1268
1269 -- End of API body.
1270
1271 -- Standard check of p_commit.
1272 IF FND_API.To_Boolean( p_commit ) THEN
1273 COMMIT WORK;
1274 END IF;
1275 FND_MSG_PUB.Count_And_Get
1276 (
1277 p_count => x_msg_count ,
1278 p_data => x_msg_data ,
1279 p_encoded => FND_API.G_FALSE
1280 );
1281
1282 EXCEPTION
1283 WHEN FND_API.G_EXC_ERROR THEN
1284 ROLLBACK TO Get_quota_pay_element;
1285 x_return_status := FND_API.G_RET_STS_ERROR ;
1286 FND_MSG_PUB.Count_And_Get
1287 (
1288 p_count => x_msg_count ,
1289 p_data => x_msg_data ,
1290 p_encoded => FND_API.G_FALSE
1291 );
1292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1293 ROLLBACK TO Get_quota_pay_element;
1294 x_loading_status := 'UNEXPECTED_ERR';
1295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1296 FND_MSG_PUB.Count_And_Get
1297 (
1298 p_count => x_msg_count ,
1299 p_data => x_msg_data ,
1300 p_encoded => FND_API.G_FALSE
1301 );
1302 WHEN OTHERS THEN
1303 ROLLBACK TO Get_quota_pay_element;
1304 x_loading_status := 'UNEXPECTED_ERR';
1305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1306 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1307 THEN
1308 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1309 END IF;
1310 FND_MSG_PUB.Count_And_Get (
1311 p_count => x_msg_count ,
1312 p_data => x_msg_data ,
1313 p_encoded => FND_API.G_FALSE
1314 );
1315 END Get_quota_pay_element;
1316
1317 END CN_QUOTA_PAY_ELEMENTS_PVT ;