[Home] [Help]
PACKAGE BODY: APPS.PN_VAREN_UTIL
Source
1 PACKAGE BODY pn_varen_util AS
2 /* $Header: PNVARUTB.pls 120.1 2010/12/15 10:23:38 seevivek noship $ */
3
4 --------------------------------------------------------------------------------
5 --------------------------------------------------------------------------------
6 -- NAME : VALIDATE_LEASE
7 -- DESCRIPTION : Procedure to validate finalized lease and location validation
8 -- PURPOSE :
9 -- INVOKED FROM :
10 -- ARGUMENTS : p_lease_name IN PARAMETER
11 -- : p_lease_num IN PARAMETER
12 -- : x_lease_id IN OUT PARAMETER
13 -- : x_proration_days OUT PARAMETER
14 -- : x_location_id IN OUT PARAMETER
15 -- : p_location_code IN PARAMETER
16 -- : x_lease_commencement_date OUT PARAMETER
17 -- : x_lease_termination_date OUT PARAMETER
18 -- : p_org_id IN PARAMETER
19 -- : x_return_status OUT PARAMETER
20 -- REFERENCE :
21 -- HISTORY :
22 --
23 -- 20-SEP-2010 vkatta o Created
24 --------------------------------------------------------------------------------
25 PROCEDURE validate_lease (
26 p_lease_name IN VARCHAR2
27 , p_lease_num IN VARCHAR2
28 , x_lease_id IN OUT NOCOPY VARCHAR2
29 , x_proration_days OUT NOCOPY NUMBER
30 , x_location_id IN OUT NOCOPY NUMBER
31 , p_location_code IN VARCHAR2
32 , x_lease_commencement_date OUT NOCOPY DATE
33 , x_lease_termination_date OUT NOCOPY DATE
34 , p_org_id IN NUMBER
35 , x_return_status OUT NOCOPY VARCHAR2
36 )
37 AS
38 l_lease_status VARCHAR2 (1);
39 xxlease_id NUMBER;
40
41 CURSOR c_lease_id
42 IS
43 SELECT pl.lease_id
44 , pl.payment_term_proration_rule
45 , pt.location_id
46 , pld.lease_commencement_date
47 , pld.lease_termination_date
48 FROM pn_leases_all pl
49 , pn_tenancies_all pt
50 , pn_lease_details_all pld
51 , fnd_user fnr
52 , fnd_lookups lse_lkp
53 , fnd_lookups flg_lkp
54 , pn_locations_all ploc
55 , pn_properties_all prop
56 WHERE pt.lease_id(+) = pl.lease_id
57 AND ploc.location_id(+) = pt.location_id
58 AND prop.property_id(+) = ploc.property_id
59 AND NVL (pt.occupancy_date
60 , pt.estimated_occupancy_date
61 ) BETWEEN ploc.active_start_date(+) AND ploc.active_end_date(+)
62 AND pl.lease_id = pld.lease_id
63 AND fnr.user_id = pld.responsible_user
64 AND lse_lkp.lookup_code = pl.status
65 AND lse_lkp.lookup_type = 'PN_LEASE_STATUS_TYPE'
66 AND flg_lkp.lookup_code = pt.primary_flag
67 AND flg_lkp.lookup_type = 'PN_YES_NO'
68 AND pl.org_id = p_org_id
69 AND pl.lease_id = x_lease_id;
70
71 --AND pl.status = 'F';
72 CURSOR c_lease_name
73 IS
74 SELECT pl.lease_id
75 , pl.payment_term_proration_rule
76 , pt.location_id
77 , pld.lease_commencement_date
78 , pld.lease_termination_date
79 FROM pn_leases_all pl
80 , pn_tenancies_all pt
81 , pn_lease_details_all pld
82 , fnd_user fnr
83 , fnd_lookups lse_lkp
84 , fnd_lookups flg_lkp
85 , pn_locations_all ploc
86 , pn_properties_all prop
87 WHERE pt.lease_id(+) = pl.lease_id
88 AND ploc.location_id(+) = pt.location_id
89 AND prop.property_id(+) = ploc.property_id
90 AND NVL (pt.occupancy_date
91 , pt.estimated_occupancy_date
92 ) BETWEEN ploc.active_start_date(+) AND ploc.active_end_date(+)
93 AND pl.lease_id = pld.lease_id
94 AND fnr.user_id = pld.responsible_user
95 AND lse_lkp.lookup_code = pl.status
96 AND lse_lkp.lookup_type = 'PN_LEASE_STATUS_TYPE'
97 AND flg_lkp.lookup_code = pt.primary_flag
98 AND flg_lkp.lookup_type = 'PN_YES_NO'
99 AND pl.org_id = p_org_id
100 AND pl.NAME = p_lease_name;
101
102 --AND pl.status = 'F';
103 CURSOR c_lease_num
104 IS
105 SELECT pl.lease_id
106 , pl.payment_term_proration_rule
107 , pt.location_id
108 , pld.lease_commencement_date
109 , pld.lease_termination_date
110 FROM pn_leases_all pl
111 , pn_tenancies_all pt
112 , pn_lease_details_all pld
113 , fnd_user fnr
114 , fnd_lookups lse_lkp
115 , fnd_lookups flg_lkp
116 , pn_locations_all ploc
117 , pn_properties_all prop
118 WHERE pt.lease_id(+) = pl.lease_id
119 AND ploc.location_id(+) = pt.location_id
120 AND prop.property_id(+) = ploc.property_id
121 AND NVL (pt.occupancy_date
122 , pt.estimated_occupancy_date
123 ) BETWEEN ploc.active_start_date(+) AND ploc.active_end_date(+)
124 AND pl.lease_id = pld.lease_id
125 AND fnr.user_id = pld.responsible_user
126 AND lse_lkp.lookup_code = pl.status
127 AND lse_lkp.lookup_type = 'PN_LEASE_STATUS_TYPE'
128 AND flg_lkp.lookup_code = pt.primary_flag
129 AND flg_lkp.lookup_type = 'PN_YES_NO'
130 AND pl.org_id = p_org_id
131 AND pl.lease_num = p_lease_num;
132 --AND pl.status = 'F';
133 BEGIN
134 x_return_status := fnd_api.g_ret_sts_success;
135
136 IF (x_lease_id IS NOT NULL)
137 THEN
138 OPEN c_lease_id;
139
140 FETCH c_lease_id
141 INTO x_lease_id
142 , x_proration_days
143 , x_location_id
144 , x_lease_commencement_date
145 , x_lease_termination_date;
146
147 IF c_lease_id%NOTFOUND
148 THEN
149 /*fnd_message.set_name ('PN'
150 , 'PN_VAR_INV_LEASE'
151 ); -- message
152 fnd_msg_pub.ADD;*/
153 x_return_status := fnd_api.g_ret_sts_error;
154 ELSE
155 x_return_status := fnd_api.g_ret_sts_success;
156 END IF;
157
158 IF c_lease_id%ISOPEN
159 THEN
160 CLOSE c_lease_id;
161 END IF;
162 ELSIF (p_lease_name IS NOT NULL)
163 THEN
164 OPEN c_lease_name;
165
166 FETCH c_lease_name
167 INTO x_lease_id
168 , x_proration_days
169 , x_location_id
170 , x_lease_commencement_date
171 , x_lease_termination_date;
172
173 IF c_lease_name%NOTFOUND
174 THEN
175 /*fnd_message.set_name ('PN'
176 , 'PN_VAR_INV_LEASE'
177 ); -- message
178 fnd_msg_pub.ADD;*/
179 x_return_status := fnd_api.g_ret_sts_error;
180 ELSE
181 x_return_status := fnd_api.g_ret_sts_success;
182 END IF;
183
184 IF c_lease_name%ISOPEN
185 THEN
186 CLOSE c_lease_name;
187 END IF;
188 ELSIF (p_lease_num IS NOT NULL)
189 THEN
190 OPEN c_lease_num;
191
192 FETCH c_lease_num
193 INTO x_lease_id
194 , x_proration_days
195 , x_location_id
196 , x_lease_commencement_date
197 , x_lease_termination_date;
198
199 IF c_lease_num%NOTFOUND
200 THEN
201 /*fnd_message.set_name ('PN'
202 , 'PN_VAR_INV_LEASE'
203 ); -- message
204 fnd_msg_pub.ADD;*/
205 x_return_status := fnd_api.g_ret_sts_error;
206 ELSE
207 x_return_status := fnd_api.g_ret_sts_success;
208 END IF;
209
210 IF c_lease_num%ISOPEN
211 THEN
212 CLOSE c_lease_num;
213 END IF;
214 END IF;
215 EXCEPTION
216 WHEN OTHERS
217 THEN
218 x_return_status := fnd_api.g_ret_sts_error;
219 END validate_lease;
220
221 --------------------------------------------------------------------------------
222 -- NAME : VALIDATE_RENT_NUM
223 -- DESCRIPTION : Procedure to validate Variable Rent Number or Rent Id validation
224 -- PURPOSE :
225 -- INVOKED FROM :
226 -- ARGUMENTS : x_rent_num IN OUT PARAMETER
227 -- : x_var_rent_id IN OUT PARAMETER
228 -- : p_lease_id IN OUT PARAMETER
229 -- : p_org_id OUT PARAMETER
230 -- : x_return_status OUT PARAMETER
231 -- REFERENCE :
232 -- HISTORY :
233 --
234 -- 20-SEP-2010 vkatta o Created
235 --------------------------------------------------------------------------------
236 PROCEDURE validate_rent_num (
237 x_rent_num IN OUT NOCOPY VARCHAR2
238 , x_var_rent_id OUT NOCOPY VARCHAR2
239 , p_lease_id IN NUMBER
240 , p_org_id IN NUMBER
241 , x_return_status OUT NOCOPY VARCHAR2
242 )
243 AS
244 l_var_rent_id NUMBER;
245 l_org_id NUMBER;
246 l_row_id VARCHAR2 (100);
247 l_return_status VARCHAR2 (30) := NULL;
248 l_auto_var_rent_num_gen VARCHAR2 (1) DEFAULT 'N';
249
250 CURSOR c_rent_num
251 IS
252 SELECT var_rent_id
253 FROM pn_var_rents_all
254 WHERE UPPER (rent_num) = UPPER (x_rent_num)
255 AND lease_id = p_lease_id
256 AND org_id = p_org_id;
257 BEGIN
258 x_return_status := fnd_api.g_ret_sts_success;
259
260 OPEN c_rent_num;
261
262 FETCH c_rent_num
263 INTO x_var_rent_id;
264
265 IF (x_var_rent_id IS NOT NULL)
266 THEN
267 x_return_status := fnd_api.g_ret_sts_error;
268 RAISE fnd_api.g_exc_error;
269 ELSE
270 --check the system profile option
271 SELECT auto_var_rent_num_gen
272 INTO l_auto_var_rent_num_gen
273 FROM pn_system_setup_options
274 WHERE org_id = p_org_id;
275
276 --Generate sequence for rent_id
277 SELECT pn_var_rents_s.NEXTVAL
278 INTO x_var_rent_id
279 FROM DUAL;
280
281 --Auto generate rent_num is on
282 IF (l_auto_var_rent_num_gen = 'Y')
283 THEN
284 --both rent num and rent_id would be sequence value
285 x_rent_num := x_var_rent_id;
286 x_return_status := fnd_api.g_ret_sts_success;
287 ELSIF ( x_rent_num IS NULL
288 AND l_auto_var_rent_num_gen = 'N')
289 THEN
290 x_return_status := fnd_api.g_ret_sts_error;
291 ELSE
292 IF c_rent_num%NOTFOUND
293 THEN
294 --Not combination of lease, org and var rent exist
295 x_return_status :=
296 fnd_api.g_ret_sts_success;
297 ELSE
298 --combination of lease, org and var rent exist, provide new rent num
299 x_return_status := fnd_api.g_ret_sts_error;
300 END IF;
301 END IF;
302 END IF;
303 EXCEPTION
304 WHEN OTHERS
305 THEN
306 x_return_status := fnd_api.g_ret_sts_unexp_error;
307 END validate_rent_num;
308
309 --------------------------------------------------------------------------------
310 -- NAME : VALIDATE_LOCATION
311 -- DESCRIPTION : Procedure to validate Location
312 -- PURPOSE :
313 -- INVOKED FROM :
314 -- ARGUMENTS : p_lease_id IN PARAMETER
315 -- : x_location_id IN OUT PARAMETER
316 -- : x_location_code IN OUT PARAMETER
317 -- : x_return_status IN OUT PARAMETER
318 -- REFERENCE :
319 -- HISTORY :
320 --
321 -- 20-SEP-2010 vkatta o Created
322 --------------------------------------------------------------------------------
323 PROCEDURE validate_location (
324 p_lease_id IN NUMBER
325 , x_location_id IN OUT NOCOPY NUMBER
326 , x_location_code IN OUT NOCOPY VARCHAR2
327 , x_return_status OUT NOCOPY VARCHAR2
328 )
329 IS
330 CURSOR c_location_id
331 IS
332 SELECT pl.location_id
333 FROM pn_tenancies_all pt
334 , pn_locations_all pl
335 , pn_properties_all prop
336 , fnd_lookups loc_lkp
337 , fnd_lookups flg_lkp
338 WHERE pt.location_id = pl.location_id
339 AND prop.property_id(+) = pl.property_id
340 AND NVL (pt.occupancy_date
341 , pt.estimated_occupancy_date
342 ) BETWEEN pl.active_start_date AND pl.active_end_date
343 AND loc_lkp.lookup_code = pl.location_type_lookup_code
344 AND loc_lkp.lookup_type = 'PN_LOCATION_TYPE'
345 AND flg_lkp.lookup_code = pt.primary_flag
346 AND flg_lkp.lookup_type = 'PN_YES_NO'
347 AND pt.lease_id = p_lease_id
348 AND pl.location_id = x_location_id;
349
350 CURSOR c_location_code
351 IS
352 SELECT pl.location_id
353 FROM pn_tenancies_all pt
354 , pn_locations_all pl
355 , pn_properties_all prop
356 , fnd_lookups loc_lkp
357 , fnd_lookups flg_lkp
358 WHERE pt.location_id = pl.location_id
359 AND prop.property_id(+) = pl.property_id
360 AND NVL (pt.occupancy_date
361 , pt.estimated_occupancy_date
362 ) BETWEEN pl.active_start_date AND pl.active_end_date
363 AND loc_lkp.lookup_code = pl.location_type_lookup_code
364 AND loc_lkp.lookup_type = 'PN_LOCATION_TYPE'
365 AND flg_lkp.lookup_code = pt.primary_flag
366 AND flg_lkp.lookup_type = 'PN_YES_NO'
367 AND pt.lease_id = p_lease_id
368 AND pl.location_code = x_location_code;
369 BEGIN
370 x_return_status := fnd_api.g_ret_sts_success;
371
372 IF ( x_location_id IS NOT NULL
373 AND x_location_id <> g_pn_miss_num)
374 THEN
375 OPEN c_location_id;
376
377 FETCH c_location_id
378 INTO x_location_id;
379
380 IF c_location_id%NOTFOUND
381 THEN
382 x_return_status := fnd_api.g_ret_sts_error;
383 ELSE
384 x_return_status := fnd_api.g_ret_sts_success;
385 END IF;
386
387 IF c_location_id%ISOPEN
388 THEN
389 CLOSE c_location_id;
390 END IF;
391 ELSIF ( x_location_code IS NOT NULL
392 AND x_location_code <> g_pn_miss_char)
393 THEN
394 OPEN c_location_code;
395
396 FETCH c_location_code
397 INTO x_location_code;
398
399 IF c_location_code%NOTFOUND
400 THEN
401 x_return_status := fnd_api.g_ret_sts_error;
402 ELSE
403 x_return_status := fnd_api.g_ret_sts_success;
404 END IF;
405
406 IF c_location_code%ISOPEN
407 THEN
408 CLOSE c_location_code;
409 END IF;
410 END IF;
411 EXCEPTION
412 WHEN OTHERS
413 THEN
414 x_return_status := fnd_api.g_ret_sts_error;
415 END validate_location;
416
417 --------------------------------------------------------------------------------
418 -- NAME : VALIDATE_LOCATION
419 -- DESCRIPTION : Procedure to validate Lookup
420 -- PURPOSE :
421 -- INVOKED FROM :
422 -- ARGUMENTS : p_lookup_type IN PARAMETER
423 -- : x_lookup_meaning IN OUT PARAMETER
424 -- : x_lookup_code IN OUT PARAMETER
425 -- : x_return_status OUT PARAMETER
426 -- REFERENCE :
427 -- HISTORY :
428 --
429 -- 20-SEP-2010 vkatta o Created
430 --------------------------------------------------------------------------------
431 PROCEDURE validate_lookups (
432 p_lookup_type IN VARCHAR2
433 , x_lookup_meaning IN OUT NOCOPY VARCHAR2
434 , x_lookup_code IN OUT NOCOPY VARCHAR2
435 , x_return_status OUT NOCOPY VARCHAR2
436 )
437 AS
438 l_code_existing NUMBER;
439
440 CURSOR c_lookup_type
441 IS
442 SELECT meaning
443 , lookup_code
444 FROM fnd_lookups
445 WHERE lookup_type = p_lookup_type
446 AND meaning = x_lookup_meaning
447 AND enabled_flag = 'Y'
448 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
449 , SYSDATE
450 )) AND TRUNC (NVL (end_date_active
451 , SYSDATE
452 ));
453
454 CURSOR c_lookup_code
455 IS
456 SELECT meaning
457 , lookup_code
458 FROM fnd_lookups
459 WHERE lookup_type = p_lookup_type
460 AND lookup_code = x_lookup_code
461 AND enabled_flag = 'Y'
462 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
463 , SYSDATE
464 )) AND TRUNC (NVL (end_date_active
465 , SYSDATE
466 ));
467 BEGIN
468 x_return_status := fnd_api.g_ret_sts_success;
469
470 IF ( x_lookup_code IS NULL
471 OR x_lookup_code = g_pn_miss_char)
472 THEN
473 OPEN c_lookup_type;
474
475 FETCH c_lookup_type
476 INTO x_lookup_meaning
477 , x_lookup_code;
478
479 IF (c_lookup_type%NOTFOUND)
480 THEN
481 fnd_message.set_name ('PN'
482 , 'PN_VAR_INVLKP_MEANING'
483 );
484 fnd_msg_pub.ADD;
485 x_return_status := fnd_api.g_ret_sts_error;
486 ELSE
487 x_return_status := fnd_api.g_ret_sts_success;
488 END IF;
489
490 IF c_lookup_type%ISOPEN
491 THEN
492 CLOSE c_lookup_type;
493 END IF;
494 ELSE
495 OPEN c_lookup_code;
496
497 FETCH c_lookup_code
498 INTO x_lookup_meaning
499 , x_lookup_code;
500
501 IF (c_lookup_code%NOTFOUND)
502 THEN
503 fnd_message.set_name ('PN'
504 , 'PN_VAR_INVLKP_CODE'
505 );
506 fnd_msg_pub.ADD;
507 x_return_status := fnd_api.g_ret_sts_error;
508 ELSE
509 x_return_status := fnd_api.g_ret_sts_success;
510 END IF;
511
512 IF c_lookup_code%ISOPEN
513 THEN
514 CLOSE c_lookup_code;
515 END IF;
516 END IF;
517 EXCEPTION
518 WHEN OTHERS
519 THEN
520 x_return_status := fnd_api.g_ret_sts_unexp_error;
521
522 -- fnd_msg_pub.ADD;
523 IF c_lookup_type%ISOPEN
524 THEN
525 CLOSE c_lookup_type;
526 ELSIF c_lookup_code%ISOPEN
527 THEN
528 CLOSE c_lookup_code;
529 END IF;
530 END validate_lookups;
531
532 --------------------------------------------------------------------------------
533 -- NAME : VALIDATE_TERM_TEMPLATE
534 -- DESCRIPTION : Procedure for Term Template Validation
535 -- PURPOSE :
536 -- INVOKED FROM :
537 -- ARGUMENTS : p_org_id IN PARAMETER
538 -- : x_term_template_id IN OUT PARAMETER
539 -- : x_term_template_name IN OUT PARAMETER
540 -- : x_return_status OUT PARAMETER
541 -- REFERENCE :
542 -- HISTORY :
543 --
544 -- 20-SEP-2010 vkatta o Created
545 --------------------------------------------------------------------------------
546 PROCEDURE validate_term_template (
547 p_org_id IN NUMBER
548 , x_term_template_id IN OUT NOCOPY NUMBER
549 , x_term_template_name IN OUT NOCOPY VARCHAR2
550 , x_return_status OUT NOCOPY VARCHAR2
551 )
552 AS
553 CURSOR c_term_template
554 IS
555 -- The below SQL has been commented and a new SQL has been added by Vivek on 02-DEC-2010
556 /*
557 SELECT term.term_template_id
558 , term.NAME
559 FROM pn_term_templates_all term
560 WHERE active = 'Y'
561 AND ( UPPER (NAME) = UPPER (x_term_template_name)
562 OR term_template_id = x_term_template_id)
563 AND term.term_template_type IN ('NEUTRAL', 'PAYMENT')
564 AND org_id = p_org_id;
565 */
566 -- New SQL added by Vivek on 02-DEC-2010
567 SELECT term.term_template_id
568 , term.NAME
569 FROM pn_term_templates_all term
570 WHERE active = 'Y'
571 AND ( UPPER (NAME) = UPPER (NVL(x_term_template_name,NAME))
572 AND term_template_id = NVL(x_term_template_id,term_template_id))
573 AND term.term_template_type IN ('NEUTRAL', 'PAYMENT')
574 AND org_id = p_org_id;
575 BEGIN
576 x_return_status := fnd_api.g_ret_sts_success;
577
578 -- The below IF block has been commented and a new IF block has been added by Vivek on 02-DEC-2010
579 /*
580 IF ( ( x_term_template_id IS NOT NULL
581 OR x_term_template_id = g_pn_miss_num)
582 OR ( x_term_template_name IS NOT NULL
583 OR x_term_template_name = g_pn_miss_char)
584 )
585 THEN
586 */
587 -- New IF block added by Vivek on 02-DEC-2010
588
589 IF ( ( x_term_template_id IS NOT NULL
590 AND x_term_template_id <> g_pn_miss_num)
591 OR ( x_term_template_name IS NOT NULL
592 AND x_term_template_name <> g_pn_miss_char)
593 )
594 THEN
595 OPEN c_term_template;
596
597 FETCH c_term_template
598 INTO x_term_template_id
599 , x_term_template_name;
600
601 IF (c_term_template%NOTFOUND)
602 THEN
603 x_return_status := fnd_api.g_ret_sts_error;
604 ELSE
605 x_return_status := fnd_api.g_ret_sts_success;
606 END IF;
607
608 IF (c_term_template%ISOPEN)
609 THEN
610 CLOSE c_term_template;
611 END IF;
612 END IF;
613 EXCEPTION
614 WHEN OTHERS
615 THEN
616 x_return_status := fnd_api.g_ret_sts_unexp_error;
617 END validate_term_template;
618
619 --------------------------------------------------------------------------------
620 -- NAME : VALIDATE_ABST_USER
621 -- DESCRIPTION : Procedure for validating user
622 -- PURPOSE :
623 -- INVOKED FROM :
624 -- ARGUMENTS : x_user_name IN OUT PARAMETER
625 -- : x_user_id IN OUT PARAMETER
626 -- : x_return_status OUT PARAMETER
627 -- REFERENCE :
628 -- HISTORY :
629 --
630 -- 20-SEP-2010 vkatta o Created
631 --------------------------------------------------------------------------------
632 PROCEDURE validate_abst_user (
633 x_user_name IN OUT NOCOPY VARCHAR2
634 , x_user_id IN OUT NOCOPY NUMBER
635 , x_return_status OUT NOCOPY VARCHAR2
636 )
637 AS
638 CURSOR c_abstracted_user
639 IS
640 -- The below SQL has been commented and a new SQL has been added by Vivek on 02-DEC-2010
641 /*
642 SELECT user_id
643 , user_name
644 FROM fnd_user
645 WHERE ( UPPER (user_name) = UPPER (x_user_name)
646 OR user_id = x_user_id)
647 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date
648 , SYSDATE
649 )) AND TRUNC (NVL (end_date
650 , SYSDATE
651 ));
652 */
653 -- New SQL added by Vivek on 02-DEC-2010
654 SELECT user_id
655 , user_name
656 FROM fnd_user
657 WHERE ( UPPER (user_name) = UPPER (NVL(x_user_name,user_name))
658 OR user_id = NVL(x_user_id,user_id))
659 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date
660 , SYSDATE
661 )) AND TRUNC (NVL (end_date
662 , SYSDATE
663 ));
664
665 BEGIN
666 x_return_status := fnd_api.g_ret_sts_success;
667
668 -- The below IF block has been commented and a new IF block has been added by Vivek on 02-DEC-2010
669 /*
670 IF ( ( x_user_id IS NOT NULL
671 OR x_user_id = g_pn_miss_num)
672 OR ( x_user_name IS NOT NULL
673 OR x_user_name = g_pn_miss_char)
674 )
675 THEN
676 */
677 IF ( ( x_user_id IS NOT NULL
678 AND x_user_id <> g_pn_miss_num)
679 OR ( x_user_name IS NOT NULL
680 AND x_user_name <> g_pn_miss_char)
681 )
682 THEN
683 OPEN c_abstracted_user;
684
685 FETCH c_abstracted_user
686 INTO x_user_id
687 , x_user_name;
688
689 IF c_abstracted_user%NOTFOUND
690 THEN
691 x_return_status := fnd_api.g_ret_sts_error;
692 ELSE
693 x_return_status := fnd_api.g_ret_sts_success;
694 END IF;
695
696 IF c_abstracted_user%ISOPEN
697 THEN
698 CLOSE c_abstracted_user;
699 END IF;
700 END IF;
701 EXCEPTION
702 WHEN OTHERS
703 THEN
704 x_return_status := fnd_api.g_ret_sts_unexp_error;
705 END validate_abst_user;
706
707 --------------------------------------------------------------------------------
708 -- NAME : VALIDATE_CURRENCY
709 -- DESCRIPTION : Procedure for validating Currency
710 -- PURPOSE :
711 -- INVOKED FROM :
712 -- ARGUMENTS : p_currency_code IN PARAMETER
713 -- : x_return_status OUT PARAMETER
714 -- REFERENCE :
715 -- HISTORY :
716 --
717 -- 20-SEP-2010 vkatta o Created
718 --------------------------------------------------------------------------------
719 PROCEDURE validate_currency (
720 p_currency_code IN VARCHAR2
721 , x_return_status OUT NOCOPY VARCHAR2
722 )
723 AS
724 l_count NUMBER;
725 BEGIN
726 x_return_status := fnd_api.g_ret_sts_success;
727
728 SELECT COUNT (1)
729 INTO l_count
730 FROM pn_currencies pn
731 , gl_daily_conversion_types gl
732 WHERE pn.conversion_type = gl.conversion_type
733 AND currency_code = p_currency_code;
734
735 IF l_count = 1
736 THEN
737 x_return_status := fnd_api.g_ret_sts_success;
738 END IF;
739 EXCEPTION
740 WHEN NO_DATA_FOUND
741 THEN
742 x_return_status := fnd_api.g_ret_sts_error;
743 WHEN OTHERS
744 THEN
745 x_return_status := fnd_api.g_ret_sts_unexp_error;
746 END validate_currency;
747
748 --------------------------------------------------------------------------------
749 -- NAME : VALIDATE_PERIODS
750 -- DESCRIPTION : Procedure for validating Periods
751 -- PURPOSE :
752 -- INVOKED FROM :
753 -- ARGUMENTS : p_lookup_type IN PARAMETER
754 -- : x_lookup_meaning IN OUT PARAMETER
755 -- : x_lookup_code IN OUT PARAMETER
756 -- : x_return_status OUT PARAMETER
757 -- REFERENCE :
758 -- HISTORY :
759 --
760 -- 20-SEP-2010 vkatta o Created
761 --------------------------------------------------------------------------------
762 PROCEDURE validate_periods (
763 p_lookup_type IN VARCHAR2
764 , x_lookup_meaning IN OUT NOCOPY VARCHAR2
765 , x_lookup_code IN OUT NOCOPY VARCHAR2
766 , x_return_status OUT NOCOPY VARCHAR2
767 )
768 AS
769 CURSOR c_lookup
770 IS
771 -- The below SQL has been commented and a new SQL has been added by Vivek on 02-DEC-2010
772 /*
773 SELECT meaning
774 , lookup_code
775 FROM fnd_lookups
776 WHERE lookup_type = p_lookup_type --'PN_VAR_CALCULATION_METHOD'
777 AND ( UPPER (lookup_code) = UPPER (x_lookup_code)
778 OR UPPER (meaning) = UPPER (x_lookup_meaning))
779 AND enabled_flag = 'Y'
780 AND lookup_code <> 'OT'
781 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
782 , SYSDATE
783 )) AND TRUNC (NVL (end_date_active
784 , SYSDATE
785 ));
786 */
787 SELECT meaning
788 , lookup_code
789 FROM fnd_lookups
790 WHERE lookup_type = p_lookup_type --'PN_VAR_CALCULATION_METHOD'
791 AND ( UPPER (lookup_code) = UPPER (NVL(x_lookup_code,lookup_code))
792 AND UPPER (meaning) = UPPER (NVL(x_lookup_meaning,meaning)))
793 AND enabled_flag = 'Y'
794 AND lookup_code <> 'OT'
795 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active
796 , SYSDATE
797 )) AND TRUNC (NVL (end_date_active
798 , SYSDATE
799 ));
800 -- New SQL added by Vivek on 02-DEC-2010
801 BEGIN
802 x_return_status := fnd_api.g_ret_sts_success;
803
804 -- The below IF block has been commented and a new IF block has been added by Vivek on 02-DEC-2010
805 /*
806 IF ( ( x_lookup_meaning IS NOT NULL
807 OR x_lookup_meaning = g_pn_miss_char)
808 OR ( x_lookup_code IS NOT NULL
809 OR x_lookup_code = g_pn_miss_char)
810 )
811 THEN
812 */
813 IF ( ( x_lookup_meaning IS NOT NULL
814 AND x_lookup_meaning <> g_pn_miss_char)
815 OR ( x_lookup_code IS NOT NULL
816 AND x_lookup_code <> g_pn_miss_char)
817 )
818 THEN
819 -- The below IF block has been added by Vivek on 02-DEC-2010 to handle the g_pn_miss_char value of x_lookup_code
820
821 IF x_lookup_code = g_pn_miss_char
822 THEN
823 x_lookup_code:=NULL;
824 END IF;
825
826 -- The below IF block has been added by Vivek on 02-DEC-2010 to handle the g_pn_miss_char value of x_lookup_meaning
827 IF x_lookup_meaning = g_pn_miss_char
828 THEN
829 x_lookup_meaning :=NULL;
830 END IF;
831 OPEN c_lookup;
832
833 FETCH c_lookup
834 INTO x_lookup_meaning
835 , x_lookup_code;
836
837 IF (c_lookup%NOTFOUND)
838 THEN
839 x_return_status := fnd_api.g_ret_sts_error;
840 ELSE
841 x_return_status := fnd_api.g_ret_sts_success;
842 END IF;
843
844 CLOSE c_lookup;
845
846 IF c_lookup%ISOPEN
847 THEN
848 CLOSE c_lookup;
849 END IF;
850 END IF;
851 EXCEPTION
852 WHEN OTHERS
853 THEN
854 x_return_status := fnd_api.g_ret_sts_unexp_error;
855 END validate_periods;
856
857 --------------------------------------------------------------------------------
858 -- NAME : VALIDATE_CALENDAR_DATE
859 -- DESCRIPTION : Procedure for validating Calendar Date
860 -- PURPOSE :
861 -- INVOKED FROM :
862 -- ARGUMENTS : p_commencement_date IN PARAMETER
863 -- : p_use_gl_calendar IN PARAMETER
864 -- : p_year_start_date IN OUT PARAMETER
865 -- : p_gl_period_set_name IN OUT PARAMETER
866 -- : p_period_type IN OUT PARAMETER
867 -- : x_return_status OUT PARAMETER
868 -- REFERENCE :
869 -- HISTORY :
870 --
871 -- 20-SEP-2010 vkatta o Created
872 --------------------------------------------------------------------------------
873 PROCEDURE validate_calendar_date (
874 p_commencement_date IN DATE
875 , p_use_gl_calendar IN VARCHAR2
876 , p_year_start_date IN OUT NOCOPY DATE
877 , p_gl_period_set_name IN OUT NOCOPY VARCHAR2
878 , p_period_type IN OUT NOCOPY VARCHAR2
879 , x_return_status OUT NOCOPY VARCHAR2
880 )
881 IS
882 l_date DATE;
883 l_day NUMBER;
884 l_month NUMBER;
885 l_year NUMBER;
886 l_period_exists NUMBER := NULL;
887 l_count NUMBER;
888 l_comm_date DATE := TO_DATE ('01-01-2000'
889 , 'DD-MM-YYYY'
890 );
891 BEGIN
892 l_year :=
893 TO_CHAR (ADD_MONTHS (TO_DATE (p_commencement_date
894 , 'DD-MM-YYYY'
895 )
896 , -12
897 )
898 , 'YYYY'
899 );
900 l_month :=
901 TO_CHAR (TO_DATE (p_year_start_date
902 , 'DD-MM-YYYY'
903 )
904 , 'MM'
905 );
906 l_day :=
907 TO_CHAR (TO_DATE (p_year_start_date
908 , 'DD-MM-YYYY'
909 )
910 , 'DD'
911 );
912
913 IF (NVL (p_use_gl_calendar
914 , 'N'
915 ) = 'N')
916 THEN
917 x_return_status := fnd_api.g_ret_sts_success;
918
919 IF (p_year_start_date > p_commencement_date)
920 THEN
921 p_year_start_date :=
922 TO_DATE ( l_day
923 || '-'
924 || l_month
925 || '-'
926 || l_year
927 , 'DD-MM-YYYY'
928 );
929 END IF;
930 ELSIF (p_use_gl_calendar = 'Y')
931 THEN
932 x_return_status := fnd_api.g_ret_sts_success;
933
934 BEGIN
935 SELECT period_type
936 INTO p_period_type
937 FROM gl_periods
938 WHERE UPPER (period_set_name) = UPPER (p_gl_period_set_name)
939 AND UPPER (period_type) = UPPER (p_period_type) -- new condition
940 GROUP BY period_type
941 , period_set_name;
942 EXCEPTION
943 WHEN OTHERS
944 THEN
945 x_return_status := fnd_api.g_ret_sts_error;
946 END;
947 END IF;
948 EXCEPTION
949 WHEN OTHERS
950 THEN
951 x_return_status := fnd_api.g_ret_sts_error;
952 END validate_calendar_date;
953
954 --------------------------------------------------------------------------------
955 -- NAME : CHECK_PRORA_CUM_COMB
956 -- DESCRIPTION : Procedure for Proration Cumulative Combination Validation
957 -- PURPOSE :
958 -- INVOKED FROM :
959 -- ARGUMENTS : p_proration_rule_code IN PARAMETER
960 -- : p_cumulative_vol_code IN PARAMETER
961 -- : x_return_status OUT PARAMETER
962 -- REFERENCE :
963 -- HISTORY :
964 --
965 -- 20-SEP-2010 vkatta o Created
966 --------------------------------------------------------------------------------
967 PROCEDURE check_prora_cum_comb (
968 p_proration_rule_code IN VARCHAR2
969 , p_cumulative_vol_code IN VARCHAR2
970 , x_return_status OUT NOCOPY VARCHAR2
971 )
972 IS
973 BEGIN
974 IF ( p_proration_rule_code = 'LY'
975 AND p_cumulative_vol_code = 'N')
976 OR ( p_proration_rule_code = 'FY'
977 AND p_cumulative_vol_code = 'N')
978 OR ( p_proration_rule_code = 'FLY'
979 AND p_cumulative_vol_code = 'N')
980 OR ( p_proration_rule_code = 'CYNP'
981 AND p_cumulative_vol_code = 'N')
982 OR ( p_proration_rule_code = 'CYP'
983 AND p_cumulative_vol_code = 'N')
984 OR ( p_proration_rule_code = 'NP'
985 AND p_cumulative_vol_code = 'Y')
986 OR ( p_proration_rule_code = 'NP'
987 AND p_cumulative_vol_code = 'T')
988 THEN
989 fnd_message.set_name ('PN'
990 , 'PN_VAR_CALC_PRO_CODE'
991 );
992 x_return_status := fnd_api.g_ret_sts_error;
993 ELSE
994 x_return_status := fnd_api.g_ret_sts_success;
995 END IF;
996 END check_prora_cum_comb;
997
998 --------------------------------------------------------------------------------
999 -- NAME : CHECK_PRORA_INVON_COMB
1000 -- DESCRIPTION : Procedure for Invoice On Code and Proration Rule Code / forecasted Validation
1001 -- PURPOSE :
1002 -- INVOKED FROM :
1003 -- ARGUMENTS : p_proration_rule_code IN PARAMETER
1004 -- : p_invoice_on_code IN PARAMETER
1005 -- : x_return_status OUT PARAMETER
1006 -- REFERENCE :
1007 -- HISTORY :
1008 --
1009 -- 20-SEP-2010 vkatta o Created
1010 --------------------------------------------------------------------------------
1011 PROCEDURE check_prora_invon_comb (
1012 p_proration_rule_code IN VARCHAR2
1013 , p_invoice_on_code IN VARCHAR2
1014 , x_return_status OUT NOCOPY VARCHAR2
1015 )
1016 IS
1017 BEGIN
1018 IF ( p_proration_rule_code = 'LY'
1019 AND p_invoice_on_code = 'FORECASTED')
1020 OR ( p_proration_rule_code = 'FY'
1021 AND p_invoice_on_code = 'FORECASTED')
1022 OR ( p_proration_rule_code = 'FLY'
1023 AND p_invoice_on_code = 'FORECASTED')
1024 OR ( p_proration_rule_code = 'CYNP'
1025 AND p_invoice_on_code = 'FORECASTED')
1026 OR ( p_proration_rule_code = 'CYP'
1027 AND p_invoice_on_code = 'FORECASTED')
1028 THEN
1029 fnd_message.set_name ('PN'
1030 , 'PN_VAR_FOR_PRO_CODE'
1031 );
1032 x_return_status := fnd_api.g_ret_sts_error;
1033 ELSE
1034 x_return_status := fnd_api.g_ret_sts_success;
1035 END IF;
1036 END check_prora_invon_comb;
1037
1038 --------------------------------------------------------------------------------
1039 -- NAME : CHECK_PRORA_INVON_COMB
1040 -- DESCRIPTION : Procedure for Calculation Freq and Repeating Freq combination Validation
1041 -- PURPOSE :
1042 -- INVOKED FROM :
1043 -- ARGUMENTS : p_vrg_reptg_freq_code IN PARAMETER
1044 -- : p_reptg_freq_code IN PARAMETER
1045 -- : x_return_status OUT PARAMETER
1046 -- REFERENCE :
1047 -- HISTORY :
1048 --
1049 -- 20-SEP-2010 vkatta o Created
1050 --------------------------------------------------------------------------------
1051 PROCEDURE check_calc_rept_freq_comb (
1052 p_vrg_reptg_freq_code IN VARCHAR2
1053 , p_reptg_freq_code IN VARCHAR2
1054 , x_return_status OUT NOCOPY VARCHAR2
1055 )
1056 IS
1057 BEGIN
1058 IF ( p_vrg_reptg_freq_code = 'QTR'
1059 AND p_reptg_freq_code = 'MON')
1060 OR ( p_vrg_reptg_freq_code = 'SA'
1061 AND p_reptg_freq_code IN ('MON', 'QTR'))
1062 OR ( p_vrg_reptg_freq_code = 'YR'
1063 AND p_reptg_freq_code IN ('MON', 'QTR', 'SA'))
1064 THEN
1065 fnd_message.set_name ('PN'
1066 , 'PN_VAR_CALC_REPTG_FREQ'
1067 );
1068 x_return_status := fnd_api.g_ret_sts_error;
1069 ELSE
1070 x_return_status := fnd_api.g_ret_sts_success;
1071 END IF;
1072 END check_calc_rept_freq_comb;
1073
1074 --------------------------------------------------------------------------------
1075 -- NAME : CHECK_CALC_INV_FREQ_COMB
1076 -- DESCRIPTION : Procedure for Calculating Invoice frequency combination validation
1077 -- PURPOSE :
1078 -- INVOKED FROM :
1079 -- ARGUMENTS : p_vrg_reptg_freq_code IN PARAMETER
1080 -- : p_invg_freq_code IN PARAMETER
1081 -- : x_return_status OUT PARAMETER
1082 -- REFERENCE :
1083 -- HISTORY :
1084 --
1085 -- 20-SEP-2010 vkatta o Created
1086 --------------------------------------------------------------------------------
1087 PROCEDURE check_calc_inv_freq_comb (
1088 p_reptg_freq_code IN VARCHAR2
1089 , p_invg_freq_code IN VARCHAR2
1090 , x_return_status OUT NOCOPY VARCHAR2
1091 )
1092 IS
1093 BEGIN
1094 IF ( p_reptg_freq_code = 'QTR'
1095 AND p_invg_freq_code = 'MON')
1096 OR ( p_reptg_freq_code = 'SA'
1097 AND p_invg_freq_code IN ('MON', 'QTR'))
1098 OR ( p_reptg_freq_code = 'YR'
1099 AND p_invg_freq_code IN ('MON', 'QTR', 'SA'))
1100 THEN
1101 fnd_message.set_name ('PN'
1102 , 'PN_VAR_INVG_CALC_FREQ'
1103 );
1104 x_return_status := fnd_api.g_ret_sts_error;
1105 ELSE
1106 x_return_status := fnd_api.g_ret_sts_success;
1107 END IF;
1108 END check_calc_inv_freq_comb;
1109
1110 --------------------------------------------------------------------------------
1111 -- NAME : VALIDATE_AGREEMENT_TEMP
1112 -- DESCRIPTION : Procedure for Agreement Template Validation
1113 -- PURPOSE :
1114 -- INVOKED FROM :
1115 -- ARGUMENTS : x_agreement_template_id IN OUT PARAMETER
1116 -- : x_agreement_template IN OUT PARAMETER
1117 -- : x_purpose_code OUT PARAMETER
1118 -- : x_type_code OUT PARAMETER
1119 -- : x_cumulative_vol OUT PARAMETER
1120 -- : x_invoice_on OUT PARAMETER
1121 -- : x_negative_rent OUT PARAMETER
1122 -- : x_term_template_id OUT PARAMETER
1123 -- : x_abatement_amount OUT PARAMETER
1124 -- : x_proration_rule OUT PARAMETER
1125 -- : x_vrg_reptg_freq_code OUT PARAMETER
1126 -- : x_period_freq_code OUT PARAMETER
1127 -- : x_use_gl_calendar OUT PARAMETER
1128 -- : x_year_start_date OUT PARAMETER
1129 -- : x_gl_period_set_name OUT PARAMETER
1130 -- : x_period_type OUT PARAMETER
1131 -- : x_reptg_freq_code OUT PARAMETER
1132 -- : x_reptg_day_of_month OUT PARAMETER
1133 -- : x_reptg_days_after OUT PARAMETER
1134 -- : x_invg_freq_code OUT PARAMETER
1135 -- : x_invg_spread_code OUT PARAMETER
1136 -- : x_invg_day_of_month OUT PARAMETER
1137 -- : x_invg_days_after OUT PARAMETER
1138 -- : x_comments OUT PARAMETER
1139 -- : p_org_id IN PARAMETER
1140 -- : x_return_status OUT PARAMETER
1141 --
1142 -- REFERENCE :
1143 -- HISTORY :
1144 --
1145 -- 20-SEP-2010 vkatta o Created
1146 --------------------------------------------------------------------------------
1147 PROCEDURE validate_agreement_temp (
1148 x_agreement_template_id IN OUT NOCOPY NUMBER
1149 , x_agreement_template IN OUT NOCOPY VARCHAR2
1150 , x_purpose_code OUT NOCOPY VARCHAR2
1151 , x_type_code OUT NOCOPY VARCHAR2
1152 , x_cumulative_vol OUT NOCOPY VARCHAR2
1153 , x_invoice_on OUT NOCOPY VARCHAR2
1154 , x_negative_rent OUT NOCOPY VARCHAR2
1155 , x_term_template_id OUT NOCOPY NUMBER
1156 , x_abatement_amount OUT NOCOPY NUMBER
1157 , x_proration_rule OUT NOCOPY VARCHAR2
1158 , x_vrg_reptg_freq_code OUT NOCOPY VARCHAR2
1159 , x_period_freq_code OUT NOCOPY VARCHAR2
1160 , x_use_gl_calendar OUT NOCOPY VARCHAR2
1161 , x_year_start_date OUT NOCOPY DATE
1162 , x_gl_period_set_name OUT NOCOPY VARCHAR2
1163 , x_period_type OUT NOCOPY VARCHAR2
1164 , x_reptg_freq_code OUT NOCOPY VARCHAR2
1165 , x_reptg_day_of_month OUT NOCOPY NUMBER
1166 , x_reptg_days_after OUT NOCOPY NUMBER
1167 , x_invg_freq_code OUT NOCOPY VARCHAR2
1168 , x_invg_spread_code OUT NOCOPY VARCHAR2
1169 , x_invg_day_of_month OUT NOCOPY VARCHAR2
1170 , x_invg_days_after OUT NOCOPY VARCHAR2
1171 , x_comments OUT NOCOPY VARCHAR2
1172 , p_org_id IN NUMBER
1173 , x_return_status OUT NOCOPY VARCHAR2
1174 )
1175 IS
1176 CURSOR c_agreement_temp
1177 IS
1178 SELECT agreement_template_id
1179 , agreement_template
1180 , purpose_code
1181 , type_code
1182 , cumulative_vol
1183 , invoice_on
1184 , negative_rent
1185 , term_template_id
1186 , abatement_amount
1187 , proration_rule
1188 , vrg_reptg_freq_code
1189 , period_freq_code
1190 , use_gl_calendar
1191 , year_start_date
1192 , gl_period_set_name
1193 , period_type
1194 , reptg_freq_code
1195 , reptg_day_of_month
1196 , reptg_days_after
1197 , invg_freq_code
1198 , invg_spread_code
1199 , invg_day_of_month
1200 , invg_days_after
1201 , comments
1202 FROM pn_var_templates_all
1203 --WHERE agreement_template_id = x_agreement_template_id -- Commented by Vivek on 08-Dec-2010 as NULL value is not handled
1204 WHERE agreement_template_id = NVL(x_agreement_template_id,agreement_template_id) -- Added by Vivek on 08-Dec-2010 to handle NULL value
1205 --OR UPPER (agreement_template) = UPPER (x_agreement_template) -- Commented by Vivek on 08-Dec-2010 as NULL value and mutliple rows are not handled
1206 AND UPPER (agreement_template) = UPPER (NVL(x_agreement_template,agreement_template)) -- Added by Vivek on 08-Dec-2010 to handle NULL value and mutliple row cases
1207 AND org_id = p_org_id;
1208 BEGIN
1209 x_return_status := fnd_api.g_ret_sts_success;
1210
1211 IF ( ( x_agreement_template_id IS NOT NULL
1212 AND x_agreement_template_id <> g_pn_miss_num)
1213 OR ( x_agreement_template IS NOT NULL
1214 AND x_agreement_template <> g_pn_miss_char)
1215 )
1216 THEN
1217 OPEN c_agreement_temp;
1218
1219 FETCH c_agreement_temp
1220 INTO x_agreement_template_id
1221 , x_agreement_template
1222 , x_purpose_code
1223 , x_type_code
1224 , x_cumulative_vol
1225 , x_invoice_on
1226 , x_negative_rent
1227 , x_term_template_id
1228 , x_abatement_amount
1229 , x_proration_rule
1230 , x_vrg_reptg_freq_code
1231 , x_period_freq_code
1232 , x_use_gl_calendar
1233 , x_year_start_date
1234 , x_gl_period_set_name
1235 , x_period_type
1236 , x_reptg_freq_code
1237 , x_reptg_day_of_month
1238 , x_reptg_days_after
1239 , x_invg_freq_code
1240 , x_invg_spread_code
1241 , x_invg_day_of_month
1242 , x_invg_days_after
1243 , x_comments;
1244
1245 IF c_agreement_temp%NOTFOUND
1246 THEN
1247 x_return_status := fnd_api.g_ret_sts_error;
1248 ELSE
1249 x_return_status := fnd_api.g_ret_sts_success;
1250 END IF;
1251
1252 IF c_agreement_temp%ISOPEN
1253 THEN
1254 CLOSE c_agreement_temp;
1255 END IF;
1256 END IF;
1257 EXCEPTION
1258 WHEN OTHERS
1259 THEN
1260 x_return_status := fnd_api.g_ret_sts_unexp_error;
1261 NULL;
1262 END validate_agreement_temp;
1263
1264 --------------------------------------------------------------------------------
1265 -- NAME : VAR_RENT_DETAILS
1266 -- DESCRIPTION : Procedure for Variable Rent extraction validation
1267 -- PURPOSE :
1268 -- INVOKED FROM :
1269 -- ARGUMENTS : p_var_rent_id IN PARAMETER
1270 -- : p_org_id IN PARAMETER
1271 -- : x_commencement_date OUT PARAMETER
1272 -- : x_termination_date OUT PARAMETER
1273 -- : x_return_status OUT PARAMETER
1274 --
1275 -- REFERENCE :
1276 -- HISTORY :
1277 --
1278 -- 20-SEP-2010 vkatta o Created
1279 --------------------------------------------------------------------------------
1280 PROCEDURE var_rent_details (
1281 p_var_rent_id IN NUMBER
1282 , p_org_id IN NUMBER
1283 , x_commencement_date OUT NOCOPY DATE
1284 , x_termination_date OUT NOCOPY DATE
1285 , x_return_status OUT NOCOPY VARCHAR2
1286 )
1287 IS
1288 BEGIN
1289 SELECT commencement_date
1290 , termination_date
1291 INTO x_commencement_date
1292 , x_termination_date
1293 FROM pn_var_rents_all
1294 WHERE var_rent_id = p_var_rent_id
1295 AND org_id = p_org_id;
1296
1297 x_return_status := fnd_api.g_ret_sts_success;
1298 EXCEPTION
1299 WHEN OTHERS
1300 THEN
1301 x_return_status := fnd_api.g_ret_sts_error;
1302 END var_rent_details;
1303
1304 --------------------------------------------------------------------------------
1305 -- NAME : VALIDATE_FLEX_FIELDS
1306 -- DESCRIPTION : Procedure for Descriptive Flex Field Validation
1307 -- PURPOSE :
1308 -- INVOKED FROM :
1309 -- ARGUMENTS : p_desc_flex_name IN PARAMETER
1310 -- : p_attribute_category IN PARAMETER
1311 -- : p_attribute1 IN PARAMETER
1312 -- : p_attribute2 IN PARAMETER
1313 -- : p_attribute3 IN PARAMETER
1314 -- : p_attribute4 IN PARAMETER
1315 -- : p_attribute5 IN PARAMETER
1316 -- : p_attribute6 IN PARAMETER
1317 -- : p_attribute7 IN PARAMETER
1318 -- : p_attribute8 IN PARAMETER
1319 -- : p_attribute9 IN PARAMETER
1320 -- : p_attribute10 IN PARAMETER
1321 -- : p_attribute11 IN PARAMETER
1322 -- : p_attribute12 IN PARAMETER
1323 -- : p_attribute13 IN PARAMETER
1324 -- : p_attribute14 IN PARAMETER
1325 -- : p_attribute15 IN PARAMETER
1326 -- : x_return_msg OUT PARAMETER
1327 -- : x_return_status OUT PARAMETER
1328 -- REFERENCE :
1329 -- HISTORY :
1330 --
1331 -- 20-SEP-2010 vkatta o Created
1332 --------------------------------------------------------------------------------
1333 PROCEDURE validate_flex_fields (
1334 p_desc_flex_name IN VARCHAR2
1335 , p_attribute_category IN VARCHAR2 --:= NULL
1336 , p_attribute1 IN VARCHAR2 --:= NULL
1337 , p_attribute2 IN VARCHAR2 --:= NULL
1338 , p_attribute3 IN VARCHAR2 --:= NULL
1339 , p_attribute4 IN VARCHAR2 --:= NULL
1340 , p_attribute5 IN VARCHAR2 --:= NULL
1341 , p_attribute6 IN VARCHAR2 --:= NULL
1342 , p_attribute7 IN VARCHAR2 --:= NULL
1343 , p_attribute8 IN VARCHAR2 --:= NULL
1344 , p_attribute9 IN VARCHAR2 --:= NULL
1345 , p_attribute10 IN VARCHAR2 --:= NULL
1346 , p_attribute11 IN VARCHAR2 --:= NULL
1347 , p_attribute12 IN VARCHAR2 --:= NULL
1348 , p_attribute13 IN VARCHAR2 --:= NULL
1349 , p_attribute14 IN VARCHAR2 --:= NULL
1350 , p_attribute15 IN VARCHAR2 --:= NULL
1351 , x_return_msg OUT NOCOPY VARCHAR2
1352 , x_return_status OUT NOCOPY VARCHAR2
1353 )
1354 IS
1355 l_dummy VARCHAR2 (1);
1356 l_r VARCHAR2 (2000);
1357 BEGIN
1358 x_return_status := fnd_api.g_ret_sts_success;
1359 -- DEFINE ID COLUMNS
1360
1361
1362 IF ( p_attribute_category
1363 || p_attribute1
1364 || p_attribute2
1365 || p_attribute3
1366 || p_attribute4
1367 || p_attribute5
1368 || p_attribute6
1369 || p_attribute7
1370 || p_attribute8
1371 || p_attribute9
1372 || p_attribute10
1373 || p_attribute11
1374 || p_attribute12
1375 || p_attribute13
1376 || p_attribute14
1377 || p_attribute15
1378 ) IS NOT NULL
1379 THEN
1380 fnd_flex_descval.set_context_value (p_attribute_category);
1381 fnd_flex_descval.set_column_value ('ATTRIBUTE1', p_attribute1);
1382 fnd_flex_descval.set_column_value ('ATTRIBUTE2', p_attribute2);
1383 fnd_flex_descval.set_column_value ('ATTRIBUTE3', p_attribute3);
1384 fnd_flex_descval.set_column_value ('ATTRIBUTE4', p_attribute4);
1385 fnd_flex_descval.set_column_value ('ATTRIBUTE5', p_attribute5);
1386 fnd_flex_descval.set_column_value ('ATTRIBUTE6', p_attribute6);
1387 fnd_flex_descval.set_column_value ('ATTRIBUTE7', p_attribute7);
1388 fnd_flex_descval.set_column_value ('ATTRIBUTE8', p_attribute8);
1389 fnd_flex_descval.set_column_value ('ATTRIBUTE9', p_attribute9);
1390 fnd_flex_descval.set_column_value ('ATTRIBUTE10', p_attribute10);
1391 fnd_flex_descval.set_column_value ('ATTRIBUTE11', p_attribute11);
1392 fnd_flex_descval.set_column_value ('ATTRIBUTE12', p_attribute12);
1393 fnd_flex_descval.set_column_value ('ATTRIBUTE13', p_attribute13);
1394 fnd_flex_descval.set_column_value ('ATTRIBUTE14', p_attribute14);
1395 fnd_flex_descval.set_column_value ('ATTRIBUTE15', p_attribute15);
1396
1397
1398 IF NOT (fnd_flex_descval.validate_desccols ('PN',p_desc_flex_name)) THEN
1399 x_return_msg := fnd_flex_descval.error_message;
1400 x_return_status := fnd_api.g_ret_sts_error;
1401 END IF;
1402 END IF;
1403
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406 x_return_status := fnd_api.g_ret_sts_unexp_error;
1407 END validate_flex_fields;
1408
1409 --------------------------------------------------------------------------------
1410 -- NAME : VAL_RENT_DETAILS
1411 -- DESCRIPTION : Procedure for Fetching Lease id, Location Id and Variable Rent Details
1412 -- PURPOSE :
1413 -- INVOKED FROM :
1414 -- ARGUMENTS : P_RENT_NUM IN PARAMETER
1415 -- : X_VAR_RENT_ID IN PARAMETER
1416 -- : X_LEASE_ID IN PARAMETER
1417 -- : X_LOCATION_ID IN PARAMETER
1418 -- : X_RETURN_STATUS OUT PARAMETER
1419 -- REFERENCE :
1420 -- HISTORY :
1421 --
1422 -- 20-SEP-2010 vkatta o Created
1423 --------------------------------------------------------------------------------
1424 -- The below procedure code has been commented by Vivek on 08-Dec-2010
1425 /*
1426 PROCEDURE val_rent_details (
1427 p_rent_num IN VARCHAR2
1428 , x_var_rent_id OUT NOCOPY NUMBER
1429 , x_lease_id OUT NOCOPY NUMBER
1430 , x_location_id OUT NOCOPY NUMBER
1431 , x_return_status OUT NOCOPY VARCHAR2
1432 )
1433 AS
1434 l_var_rent_id NUMBER;
1435
1436 --l_return_status VARCHAR2 (30) := NULL;
1437 CURSOR c_rent_details
1438 IS
1439 SELECT var_rent_id
1440 , lease_id
1441 , location_id
1442 FROM pn_var_rents_all
1443 WHERE UPPER (rent_num) = UPPER (p_rent_num);
1444 BEGIN
1445 x_return_status := fnd_api.g_ret_sts_success;
1446
1447 OPEN c_rent_details;
1448
1449 FETCH c_rent_details
1450 INTO x_var_rent_id
1451 , x_lease_id
1452 , x_location_id;
1453
1454 IF c_rent_details%FOUND
1455 THEN
1456 --Not combination of lease, org and var rent exist
1457 x_return_status := fnd_api.g_ret_sts_success;
1458 ELSE
1459 --combination of lease, org and var rent exist, provide new rent num
1460 x_return_status := fnd_api.g_ret_sts_error;
1461 END IF;
1462
1463 IF c_rent_details%ISOPEN
1464 THEN
1465 CLOSE c_rent_details;
1466 END IF;
1467 EXCEPTION
1468 WHEN OTHERS
1469 THEN
1470 x_return_status := fnd_api.g_ret_sts_unexp_error;
1471 END val_rent_details;
1472 */
1473 -- The below procedure code has been added by Vivek on 08-Dec-2010
1474 PROCEDURE val_rent_details (
1475 p_rent_num IN OUT NOCOPY NUMBER
1476 , x_var_rent_id IN OUT NOCOPY NUMBER
1477 --, x_lease_id OUT NOCOPY NUMBER
1478 --, x_location_id OUT NOCOPY NUMBER
1479 , x_return_status OUT NOCOPY VARCHAR2
1480 )
1481 AS
1482 l_var_rent_id NUMBER;
1483
1484 --l_return_status VARCHAR2 (30) := NULL;
1485 CURSOR c_rent_details
1486 IS
1487 SELECT p_rent_num
1488 , var_rent_id
1489 -- , lease_id
1490 -- , location_id
1491 FROM pn_var_rents_all
1492 WHERE UPPER (rent_num) = UPPER (NVL(p_rent_num,rent_num))
1493 AND var_rent_id= NVL(x_var_rent_id,var_rent_id);
1494 BEGIN
1495
1496
1497 x_return_status := fnd_api.g_ret_sts_success;
1498
1499 IF (p_rent_num IS NOT NULL AND p_rent_num <> g_pn_miss_char)
1500 OR (x_var_rent_id IS NOT NULL AND x_var_rent_id<> g_pn_miss_num)
1501 THEN
1502
1503
1504 OPEN c_rent_details;
1505 -- Below Fetch has been commented by Vivek on 08-DEC-2010
1506 /*
1507 FETCH c_rent_details
1508 INTO x_var_rent_id
1509 , x_lease_id
1510 , x_location_id;
1511 */
1512 -- Below Fetch has been added by Vivek on 08-DEC-2010
1513 FETCH c_rent_details
1514 INTO p_rent_num
1515 , x_var_rent_id;
1516
1517 IF c_rent_details%FOUND
1518 THEN
1519 --Not combination of lease, org and var rent exist
1520 x_return_status := fnd_api.g_ret_sts_success;
1521 ELSE
1522 --combination of lease, org and var rent exist, provide new rent num
1523 x_return_status := fnd_api.g_ret_sts_error;
1524 END IF;
1525
1526 IF c_rent_details%ISOPEN
1527 THEN
1528 CLOSE c_rent_details;
1529 END IF;
1530 END IF;
1531 EXCEPTION
1532 WHEN OTHERS
1533 THEN
1534 x_return_status := fnd_api.g_ret_sts_unexp_error;
1535 END val_rent_details;
1536
1537 --------------------------------------------------------------------------------
1538 -- NAME : PVT_DEBUG
1539 -- DESCRIPTION : Procedure for debug
1540 -- PURPOSE :
1541 -- INVOKED FROM :
1542 -- ARGUMENTS : P_LOG_MESSAGE IN PARAMETER
1543 -- : P_LOG_LEVEL IN PARAMETER
1544 -- REFERENCE :
1545 -- HISTORY :
1546 --
1547 -- 20-SEP-2010 vkatta o Created
1548 --------------------------------------------------------------------------------
1549 PROCEDURE pvt_debug (
1550 p_log_message IN VARCHAR2
1551 , p_log_level IN NUMBER
1552 )
1553 IS
1554 BEGIN
1555 IF g_debug = 'Y'
1556 THEN
1557 pn_debug.g_err_stage := p_log_message;
1558 pn_debug.WRITE (g_module_name
1559 , pn_debug.g_err_stage
1560 , p_log_level
1561 );
1562 --ELSE
1563 -- NULL;
1564 END IF;
1565 END pvt_debug;
1566
1567 --------------------------------------------------------------------------------
1568 -- NAME : VALIDATE_PERIOD_SET_TYPE
1569 -- DESCRIPTION : Procedure for validate Period set type
1570 -- PURPOSE :
1571 -- INVOKED FROM :
1572 -- ARGUMENTS : P_GL_PERIOD_SET_NAME IN OUT PARAMETER
1573 -- : P_PERIOD_TYPE IN OUT PARAMETER
1574 -- : X_RETURN_STATUS OUT PARAMETER
1575 -- REFERENCE :
1576 -- HISTORY :
1577 --
1578 -- 11-OCT-2010 vkatta o Created
1579 --------------------------------------------------------------------------------
1580 PROCEDURE validate_period_set_type (
1581 p_gl_period_set_name IN OUT NOCOPY VARCHAR2
1582 , p_period_type IN OUT NOCOPY VARCHAR2
1583 , x_return_status OUT NOCOPY VARCHAR2
1584 )
1585 IS
1586 l_period_type pn_var_rent_dates_all.period_type%TYPE;
1587 l_gl_period_set_name pn_var_rent_dates_all.gl_period_set_name%TYPE;
1588 BEGIN
1589 x_return_status := fnd_api.g_ret_sts_success;
1590
1591 SELECT period_type
1592 , period_set_name
1593 INTO l_period_type
1594 , l_gl_period_set_name
1595 FROM gl_periods
1596 WHERE UPPER (period_set_name) = UPPER (p_gl_period_set_name)
1597 AND UPPER (period_type) = UPPER (p_period_type)
1598 GROUP BY period_type
1599 , period_set_name;
1600
1601 IF ( l_period_type IS NOT NULL
1602 AND l_gl_period_set_name IS NOT NULL)
1603 THEN
1604 p_period_type := l_period_type;
1605 p_gl_period_set_name := l_gl_period_set_name;
1606 x_return_status := fnd_api.g_ret_sts_success;
1607 ELSE
1608 x_return_status := fnd_api.g_ret_sts_error;
1609 END IF;
1610 EXCEPTION
1611 WHEN OTHERS
1612 THEN
1613 x_return_status := fnd_api.g_ret_sts_error;
1614 END validate_period_set_type;
1615
1616 --------------------------------------------------------------------------------
1617 -- NAME : BREAKPOINT_EXT
1618 -- DESCRIPTION : Procedure for verifying whether breakpoint are generated or not
1619 -- PURPOSE :
1620 -- INVOKED FROM :
1621 -- ARGUMENTS : P_VAR_RENT_ID IN PARAMETER
1622 -- : X_BKPT_EXIST OUT PARAMETER
1623 -- : X_RETURN_STATUS OUT PARAMETER
1624 -- REFERENCE :
1625 -- HISTORY :
1626 --
1627 -- 11-OCT-2010 vkatta o Created
1628 --------------------------------------------------------------------------------
1629 PROCEDURE breakpoint_ext (
1630 p_var_rent_id IN NUMBER
1631 , x_bkpt_exist OUT NOCOPY NUMBER
1632 , x_return_status OUT NOCOPY VARCHAR2
1633 )
1634 IS
1635 --l_bkpt_exist NUMBER := 0;
1636 BEGIN
1637 x_return_status := fnd_api.g_ret_sts_success;
1638
1639 SELECT COUNT (1)
1640 INTO x_bkpt_exist --l_bkpt_exist
1641 FROM pn_var_bkpts_head_all
1642 WHERE var_rent_id = p_var_rent_id;
1643 EXCEPTION
1644 WHEN OTHERS
1645 THEN
1646 x_return_status := fnd_api.g_ret_sts_error;
1647 END breakpoint_ext;
1648
1649 --------------------------------------------------------------------------------
1650 -- NAME : CONSTR_EXT
1651 -- DESCRIPTION : Procedure for verifying whether Constraints are generated or not
1652 -- PURPOSE :
1653 -- INVOKED FROM :
1654 -- ARGUMENTS : P_VAR_RENT_ID IN PARAMETER
1655 -- : X_CONSTR_COUNT OUT PARAMETER
1656 -- : X_RETURN_STATUS OUT PARAMETER
1657 -- REFERENCE :
1658 -- HISTORY :
1659 --
1660 -- 11-OCT-2010 vkatta o Created
1661 --------------------------------------------------------------------------------
1662 PROCEDURE constr_ext (
1663 p_var_rent_id IN NUMBER
1664 , x_constr_count OUT NOCOPY NUMBER
1665 , x_return_status OUT NOCOPY VARCHAR2
1666 )
1667 IS
1668 BEGIN
1669 x_return_status := fnd_api.g_ret_sts_success;
1670
1671 SELECT COUNT (1)
1672 INTO x_constr_count
1673 FROM pn_var_constr_defaults_all
1674 WHERE var_rent_id = p_var_rent_id;
1675 EXCEPTION
1676 WHEN OTHERS
1677 THEN
1678 x_return_status := fnd_api.g_ret_sts_error;
1679 END constr_ext;
1680
1681 -- Below Procedure has been added by Vivek on 08-Dec-2010 to validate lease during update flow
1682 PROCEDURE validate_lease_id (
1683 p_lease_id IN OUT NOCOPY NUMBER
1684 , x_lease_commencement_date OUT NOCOPY DATE
1685 , x_lease_termination_date OUT NOCOPY DATE
1686 , x_return_status OUT NOCOPY VARCHAR2
1687 ) IS
1688
1689 CURSOR c_lease_id
1690 IS
1691 SELECT PLA.lease_id lease_id
1692 ,PLD.lease_commencement_date lease_commencement_date
1693 ,PLD.lease_termination_date lease_termination_date
1694 FROM pn_leases_all PLA
1695 ,pn_lease_details_all PLD
1696 WHERE PLD.org_id=PLA.org_id
1697 AND PLA.org_id=FND_PROFILE.VALUE('ORG_ID')
1698 AND PLD.lease_id=PLA.lease_id
1699 AND PLA.lease_id=p_lease_id;
1700
1701 BEGIN
1702
1703 x_return_status := fnd_api.g_ret_sts_success;
1704
1705 IF (p_lease_id IS NOT NULL AND p_lease_id <> g_pn_miss_num)
1706 THEN
1707
1708
1709 OPEN c_lease_id;
1710
1711 FETCH c_lease_id
1712 INTO p_lease_id
1713 , x_lease_commencement_date
1714 , x_lease_termination_date;
1715
1716 IF c_lease_id%FOUND
1717 THEN
1718 x_return_status := fnd_api.g_ret_sts_success;
1719 ELSE
1720 x_return_status := fnd_api.g_ret_sts_error;
1721 END IF;
1722
1723 IF c_lease_id%ISOPEN
1724 THEN
1725 CLOSE c_lease_id;
1726 END IF;
1727 END IF;
1728 EXCEPTION
1729 WHEN OTHERS
1730 THEN
1731 IF c_lease_id%ISOPEN
1732 THEN
1733 CLOSE c_lease_id;
1734 END IF;
1735
1736 x_return_status := fnd_api.g_ret_sts_unexp_error;
1737
1738 END validate_lease_id;
1739 END pn_varen_util;