[Home] [Help]
PACKAGE BODY: APPS.PN_INDEX_RENT_UTILS
Source
1 PACKAGE BODY PN_INDEX_RENT_UTILS AS
2 /* $Header: PNIRUTLB.pls 120.1 2010/12/15 10:41:47 seevivek noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'PN_INDEX_RENT_UTILS_PKG';
5
6 --Procedure to validate the Lookup code and Lookup Meaning for the input parameter Lookup Type
7
8 PROCEDURE get_index_lease_id (
9 p_parameter_name IN VARCHAR2
10 , p_operation IN VARCHAR2 DEFAULT 'UPDATE_INDEX_RENT'
11 , p_index_lease_number IN OUT NOCOPY VARCHAR2
12 , x_index_lease_id IN OUT NOCOPY NUMBER
13 , x_lease_id OUT NOCOPY NUMBER
14 , x_org_id IN OUT NOCOPY NUMBER
15 , x_index_id OUT NOCOPY NUMBER
16 , x_return_status OUT NOCOPY VARCHAR2
17 )
18 IS
19 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
20 l_api_name_full CONSTANT VARCHAR2 (61)
21 := g_pkg_name
22 || '.'
23 || l_api_name;
24
25 CURSOR c_index_lease_num
26 IS
27 SELECT index_lease_id
28 ,index_lease_number
29 ,lease_id
30 ,org_id
31 ,index_id
32 FROM pn_index_leases_all
33 WHERE index_lease_number = p_index_lease_number
34 AND org_id=x_org_id;
35
36
37 CURSOR c_index_lease_id
38 IS
39 SELECT index_lease_id
40 ,index_lease_number
41 ,lease_id
42 ,org_id
43 ,index_id
44 FROM pn_index_leases_all
45 WHERE index_lease_id = x_index_lease_id
46 AND org_id=x_org_id;
47 BEGIN
48 -- Initialize the return status.
49 x_return_status := fnd_api.g_ret_sts_success;
50
51 IF ( x_index_lease_id IS NULL
52 OR x_index_lease_id = pn_index_rent_utils.g_pn_miss_num)
53 THEN
54 OPEN c_index_lease_num;
55
56 FETCH c_index_lease_num
57 INTO x_index_lease_id
58 ,p_index_lease_number
59 ,x_lease_id
60 ,x_org_id
61 , x_index_id;
62
63 IF (c_index_lease_num%NOTFOUND)
64 THEN
65 x_return_status := fnd_api.g_ret_sts_error;
66 ELSE
67 x_return_status := fnd_api.g_ret_sts_success;
68 END IF;
69
70 IF c_index_lease_num%ISOPEN
71 THEN
72 CLOSE c_index_lease_num;
73 END IF;
74 ELSE
75 OPEN c_index_lease_id;
76
77 FETCH c_index_lease_id
78 INTO x_index_lease_id
79 ,p_index_lease_number
80 ,x_lease_id
81 ,x_org_id
82 ,x_index_id;
83
84 IF (c_index_lease_id%NOTFOUND)
85 THEN
86 x_return_status := fnd_api.g_ret_sts_error;
87 ELSE
88 x_return_status := fnd_api.g_ret_sts_success;
89 END IF;
90
91 IF c_index_lease_id%ISOPEN
92 THEN
93 CLOSE c_index_lease_id;
94 END IF;
95 END IF;
96 EXCEPTION
97 WHEN OTHERS
98 THEN
99 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
100 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
101 fnd_message.set_token ('P_TEXT', l_api_name_full
102 || '-'
103 || SQLERRM);
104 fnd_msg_pub.ADD;
105
106
107 IF c_index_lease_num%ISOPEN
108 THEN
109 CLOSE c_index_lease_num;
110 ELSIF c_index_lease_id%ISOPEN
111 THEN
112 CLOSE c_index_lease_id;
113 END IF;
114
115 x_return_status := fnd_api.g_ret_sts_unexp_error;
116
117 END get_index_lease_id;
118 PROCEDURE get_lookup_code (
119 p_parameter_name IN VARCHAR2
120 , p_operation IN VARCHAR2 DEFAULT 'CREATE_RENT_INDEX'
121 , p_lookup_meaning IN VARCHAR2
122 , p_lookup_type IN VARCHAR2
123 , x_lookup_type_code IN OUT NOCOPY VARCHAR2
124 , x_return_status OUT NOCOPY VARCHAR2
125 )
126 IS
127 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
128 l_api_name_full CONSTANT VARCHAR2 (61)
129 := g_pkg_name
130 || '.'
131 || l_api_name;
132
133 CURSOR c_lookup_type
134 IS
135 SELECT flv.lookup_code
136 FROM fnd_lookup_values_vl flv
137 WHERE flv.lookup_type = p_lookup_type
138 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (flv.start_date_active
139 , SYSDATE))
140 AND TRUNC (NVL (flv.end_date_active
141 , SYSDATE))
142 AND flv.enabled_flag = 'Y'
143 AND flv.meaning = p_lookup_meaning;
144
145 CURSOR c_lookup_type_code
146 IS
147 SELECT flv.lookup_code
148 FROM fnd_lookup_values_vl flv
149 WHERE flv.lookup_type = p_lookup_type
150 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (flv.start_date_active
151 , SYSDATE))
152 AND TRUNC (NVL (flv.end_date_active
153 , SYSDATE))
154 AND flv.enabled_flag = 'Y'
155 AND flv.lookup_code = x_lookup_type_code;
156 BEGIN
157 -- Initialize the return status.
158 x_return_status := fnd_api.g_ret_sts_success;
159
160 IF ( x_lookup_type_code IS NULL
161 OR x_lookup_type_code = pn_index_rent_utils.g_pn_miss_char
162 )
163 THEN
164 OPEN c_lookup_type;
165
166 FETCH c_lookup_type
167 INTO x_lookup_type_code;
168
169 IF (c_lookup_type%NOTFOUND)
170 THEN
171 x_return_status := fnd_api.g_ret_sts_error;
172 ELSE
173 x_return_status := fnd_api.g_ret_sts_success;
174 END IF;
175
176 IF c_lookup_type%ISOPEN
177 THEN
178 CLOSE c_lookup_type;
179 END IF;
180 ELSE
181 OPEN c_lookup_type_code;
182
183 FETCH c_lookup_type_code
184 INTO x_lookup_type_code;
185
186 IF (c_lookup_type_code%NOTFOUND)
187 THEN
188 x_return_status := fnd_api.g_ret_sts_error;
189 ELSE
190 x_return_status := fnd_api.g_ret_sts_success;
191 END IF;
192
193 IF c_lookup_type_code%ISOPEN
194 THEN
195 CLOSE c_lookup_type_code;
196 END IF;
197 END IF;
198 EXCEPTION
199 WHEN OTHERS
200 THEN
201
202 IF c_lookup_type%ISOPEN
203 THEN
204 CLOSE c_lookup_type;
205 ELSIF c_lookup_type_code%ISOPEN
206 THEN
207 CLOSE c_lookup_type_code;
208 END IF;
209
210 x_return_status := fnd_api.g_ret_sts_unexp_error;
211
212 END get_lookup_code;
213
214 PROCEDURE get_lease_id
215 ( p_parameter_name IN VARCHAR2
216 , p_operation IN VARCHAR2 DEFAULT 'CREATE_INDEX_RENT'
217 , p_lease_name IN VARCHAR2
218 , p_lease_number IN VARCHAR2
219 , p_org_id IN NUMBER
220 , x_lease_id IN OUT NOCOPY NUMBER
221 , x_lease_class_code OUT NOCOPY VARCHAR2
222 , x_lease_commencement_date OUT NOCOPY DATE
223 , x_lease_termination_date OUT NOCOPY DATE
224 , x_lease_execution_date OUT NOCOPY DATE
225 , x_lease_extension_end_date OUT NOCOPY DATE
226 , x_return_status OUT NOCOPY VARCHAR2
227 )
228
229 IS
230 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
231 l_api_name_full CONSTANT VARCHAR2 (61)
232 := g_pkg_name
233 || '.'
234 || l_api_name;
235
236 CURSOR c_lease_id
237 IS
238 SELECT PL.lease_id lease_id
239 ,PL.lease_class_code lease_class_code
240 ,PLD.lease_commencement_date commencement_date
241 ,PLD.lease_termination_date termination_date
242 ,PLD.lease_execution_date lease_execution_date
243 ,PLD.lease_extension_end_date
244 FROM pn_leases_all PL
245 ,pn_lease_details_all PLD
246 WHERE PLD.lease_id = PL.lease_id
247 AND PL.lease_id = x_lease_id
248 AND PLD.org_id = PL.org_id
249 AND PL.org_id = p_org_id;
250
251 CURSOR c_lease_num
252 IS
253 SELECT PL.lease_id lease_id
254 ,PL.lease_class_code lease_class_code
255 ,PLD.lease_commencement_date commencement_date
256 ,PLD.lease_termination_date termination_date
257 ,PLD.lease_execution_date lease_execution_date
258 ,PLD.lease_extension_end_date
259 FROM pn_leases_all PL
260 ,pn_lease_details_all PLD
261 WHERE PL.lease_num = p_lease_number
262 AND PLD.lease_id = PL.lease_id
263 -- AND PL.lease_id = x_lease_id
264 AND PLD.org_id = PL.org_id
265 AND PL.org_id = p_org_id;
266
267 CURSOR c_lease_name
268 IS
269
270 SELECT PL.lease_id lease_id
271 ,PL.lease_class_code lease_class_code
272 ,PLD.lease_commencement_date commencement_date
273 ,PLD.lease_termination_date termination_date
274 ,PLD.lease_execution_date lease_execution_date
275 ,PLD.lease_extension_end_date
276 FROM pn_leases_all PL
277 ,pn_lease_details_all PLD
278 WHERE PL.name = p_lease_name
279 AND PLD.lease_id = PL.lease_id
280 --AND PL.lease_id = x_lease_id
281 AND PLD.org_id = PL.org_id
282 AND PL.org_id = p_org_id;
283 BEGIN
284 -- Initialize the return status.
285 x_return_status := fnd_api.g_ret_sts_success;
286
287 IF ( (x_lease_id IS NULL OR x_lease_id = pn_index_rent_utils.g_pn_miss_num)
288 AND
289 (p_lease_number IS NULL OR p_lease_number = pn_index_rent_utils.g_pn_miss_char)
290 )
291 THEN
292 OPEN c_lease_name;
293
294 FETCH c_lease_name
295 INTO x_lease_id
296 ,x_lease_class_code
297 ,x_lease_commencement_date
298 ,x_lease_termination_date
299 ,x_lease_execution_date
300 ,x_lease_extension_end_date;
301
302 IF (c_lease_name%NOTFOUND)
303 THEN
304 x_return_status := fnd_api.g_ret_sts_error;
305 ELSE
306 x_return_status := fnd_api.g_ret_sts_success;
307 END IF;
308
309 IF c_lease_name%ISOPEN
310 THEN
311 CLOSE c_lease_name;
312 END IF;
313
314 ELSIF ( (x_lease_id IS NULL OR x_lease_id = pn_index_rent_utils.g_pn_miss_num)
315 AND
316 (p_lease_name IS NULL OR p_lease_name = pn_index_rent_utils.g_pn_miss_char)
317 )
318 THEN
319 OPEN c_lease_num;
320
321 FETCH c_lease_num
322 INTO x_lease_id
323 ,x_lease_class_code
324 ,x_lease_commencement_date
325 ,x_lease_termination_date
326 ,x_lease_execution_date
327 ,x_lease_extension_end_date;
328
329 IF (c_lease_num%NOTFOUND)
330 THEN
331 x_return_status := fnd_api.g_ret_sts_error;
332 ELSE
333 x_return_status := fnd_api.g_ret_sts_success;
334 END IF;
335
336 IF c_lease_num%ISOPEN
337 THEN
338 CLOSE c_lease_num;
339 END IF;
340
341 ELSE
342 OPEN c_lease_id;
343
344 FETCH c_lease_id
345 INTO x_lease_id
346 ,x_lease_class_code
347 ,x_lease_commencement_date
348 ,x_lease_termination_date
349 ,x_lease_execution_date
350 ,x_lease_extension_end_date;
351
352 IF (c_lease_id%NOTFOUND)
353 THEN
354 x_return_status := fnd_api.g_ret_sts_error;
355 ELSE
356 x_return_status := fnd_api.g_ret_sts_success;
357 END IF;
358
359 IF c_lease_id%ISOPEN
360 THEN
361 CLOSE c_lease_id;
362 END IF;
363 END IF;
364 EXCEPTION
365 WHEN OTHERS
366 THEN
367 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
368 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
369 fnd_message.set_token ('P_TEXT', l_api_name_full
370 || '-'
371 || SQLERRM);
372 fnd_msg_pub.ADD;
373
374 IF c_lease_name%ISOPEN
375 THEN
376 CLOSE c_lease_name;
377 ELSIF c_lease_num%ISOPEN
378 THEN
379 CLOSE c_lease_num;
380 ELSIF c_lease_id%ISOPEN
381 THEN
382 CLOSE c_lease_id;
383 END IF;
384
385 x_return_status := fnd_api.g_ret_sts_unexp_error;
386
387 END GET_LEASE_ID;
388
389 PROCEDURE get_location_id (
390 p_parameter_name IN VARCHAR2
391 , p_operation IN VARCHAR2 DEFAULT 'CREATE_INDEX_RENT'
392 , p_lease_id IN NUMBER
393 , p_location_code IN VARCHAR2
394 , p_org_id IN NUMBER
395 , x_location_id IN OUT NOCOPY NUMBER
396 , x_return_status OUT NOCOPY VARCHAR2
397 )
398
399 IS
400 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
401 l_api_name_full CONSTANT VARCHAR2 (61)
402 := g_pkg_name
403 || '.'
404 || l_api_name;
405
406 CURSOR c_location_code
407 IS
408 SELECT PT.location_id
409 FROM pn_tenancies_all PT
410 , pn_locations_all PL
411 , fnd_lookups LOC_LKP
412 , fnd_lookups FLG_LKP
413 WHERE PT.location_id = PL.location_id
414 AND PL.org_id = p_org_id
415 AND NVL(PT.occupancy_date, PT.estimated_occupancy_date)
416 BETWEEN PL.active_start_date
417 AND PL.active_end_date
418 AND PT.lease_id = p_lease_id
419 AND LOC_LKP.lookup_code = PL.LOCATION_TYPE_LOOKUP_CODE
420 AND LOC_LKP.lookup_type = 'PN_LOCATION_TYPE'
421 AND FLG_LKP.lookup_code = pt.primary_flag
422 AND FLG_LKP.lookup_type ='PN_YES_NO'
423 AND PL.location_code =p_location_code;
424
425
426 CURSOR c_location_id
427 IS
428 SELECT PT.location_id
429 FROM pn_tenancies_all PT
430 , pn_locations_all PL
431 , fnd_lookups LOC_LKP
432 , fnd_lookups FLG_LKP
433 WHERE PT.location_id = PL.location_id
434 AND PL.org_id = p_org_id
435 AND NVL(PT.occupancy_date, PT.estimated_occupancy_date)
436 BETWEEN PL.active_start_date
437 AND PL.active_end_date
438 AND PT.lease_id = p_lease_id
439 AND LOC_LKP.lookup_code = PL.LOCATION_TYPE_LOOKUP_CODE
440 AND LOC_LKP.lookup_type ='PN_LOCATION_TYPE'
441 AND FLG_LKP.lookup_code = pt.primary_flag
442 AND FLG_LKP.lookup_type ='PN_YES_NO'
443 AND PL.location_id = x_location_id;
444
445 BEGIN
446 -- Initialize the return status.
447 x_return_status := fnd_api.g_ret_sts_success;
448
449 IF ( x_location_id IS NULL
450 OR x_location_id = pn_index_rent_utils.g_pn_miss_num)
451 THEN
452 OPEN c_location_code;
453
454 FETCH c_location_code
455 INTO x_location_id;
456
457 IF (c_location_code%NOTFOUND)
458 THEN
459
460 x_return_status := fnd_api.g_ret_sts_error;
461 ELSE
462 x_return_status := fnd_api.g_ret_sts_success;
463 END IF;
464
465 IF c_location_code%ISOPEN
466 THEN
467 CLOSE c_location_code;
468 END IF;
469 ELSE
470 OPEN c_location_id;
471
472 FETCH c_location_id
473 INTO x_location_id;
474
475 IF (c_location_id%NOTFOUND)
476 THEN
477 x_return_status := fnd_api.g_ret_sts_error;
478 ELSE
479 x_return_status := fnd_api.g_ret_sts_success;
480 END IF;
481
482 IF c_location_id%ISOPEN
483 THEN
484 CLOSE c_location_id;
485 END IF;
486 END IF;
487 EXCEPTION
488 WHEN OTHERS
489 THEN
490 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
491 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
492 fnd_message.set_token ('P_TEXT', l_api_name_full
493 || '-'
494 || SQLERRM);
495 fnd_msg_pub.ADD;
496
497 IF c_location_code%ISOPEN
498 THEN
499 CLOSE c_location_code;
500 ELSIF c_location_id%ISOPEN
501 THEN
502 CLOSE c_location_id;
503 END IF;
504 x_return_status := fnd_api.g_ret_sts_unexp_error;
505 END get_location_id;
506
507 PROCEDURE get_index_id (
508 p_parameter_name IN VARCHAR2
509 , p_operation IN VARCHAR2 DEFAULT 'CREATE_INDEX_RENT'
510 , p_index_name IN VARCHAR2
511 , x_index_id IN OUT NOCOPY NUMBER
512 , x_return_status OUT NOCOPY VARCHAR2
513 )
514 IS
515 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
516 l_api_name_full CONSTANT VARCHAR2 (61)
517 := g_pkg_name
518 || '.'
519 || l_api_name;
520
521 CURSOR c_index
522 IS
523 SELECT index_id
524 FROM pn_index_history_headers
525 WHERE name = p_index_name;
526
527 CURSOR c_index_id
528 IS
529
530 SELECT index_id
531 FROM pn_index_history_headers
532 WHERE index_id = x_index_id;
533 BEGIN
534 -- Initialize the return status.
535 x_return_status := fnd_api.g_ret_sts_success;
536
537 IF ( x_index_id IS NULL
538 OR x_index_id = pn_index_rent_utils.g_pn_miss_num)
539 THEN
540 OPEN c_index;
541
542 FETCH c_index
543 INTO x_index_id;
544
545 IF (c_index%NOTFOUND)
546 THEN
547 x_return_status := fnd_api.g_ret_sts_error;
548 ELSE
549 x_return_status := fnd_api.g_ret_sts_success;
550 END IF;
551
552 IF c_index%ISOPEN
553 THEN
554 CLOSE c_index;
555 END IF;
556 ELSE
557 OPEN c_index_id;
558
559 FETCH c_index_id
560 INTO x_index_id;
561
562 IF (c_index_id%NOTFOUND)
563 THEN
564 x_return_status := fnd_api.g_ret_sts_error;
565 ELSE
566 x_return_status := fnd_api.g_ret_sts_success;
567 END IF;
568
569 IF c_index_id%ISOPEN
570 THEN
571 CLOSE c_index_id;
572 END IF;
573 END IF;
574 EXCEPTION
575 WHEN OTHERS
576 THEN
577 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
578 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
579 fnd_message.set_token ('P_TEXT', l_api_name_full
580 || '-'
581 || SQLERRM);
582 fnd_msg_pub.ADD;
583
584 IF c_index%ISOPEN
585 THEN
586 CLOSE c_index;
587 ELSIF c_index_id%ISOPEN
588 THEN
589 CLOSE c_index_id;
590 END IF;
591
592 x_return_status := fnd_api.g_ret_sts_unexp_error;
593
594 END get_index_id;
595
596 PROCEDURE get_user_id (
597 p_parameter_name IN VARCHAR2
598 , p_operation IN VARCHAR2 DEFAULT 'CREATE_INDEX_RENT'
599 , p_user_name IN VARCHAR2
600 , x_user_id IN OUT NOCOPY NUMBER
601 , x_return_status OUT NOCOPY VARCHAR2
602 )
603 IS
604 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
605 l_api_name_full CONSTANT VARCHAR2 (61)
606 := g_pkg_name
607 || '.'
608 || l_api_name;
609
610 CURSOR c_user
611 IS
612 SELECT user_id
613 FROM fnd_user
614 WHERE TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date, SYSDATE))
615 AND TRUNC (NVL (end_date, SYSDATE))
616 AND user_name = p_user_name;
617
618 CURSOR c_user_id
619 IS
620 SELECT user_id
621 FROM fnd_user
622 WHERE TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date, SYSDATE))
623 AND TRUNC (NVL (end_date, SYSDATE))
624 AND user_id = x_user_id;
625 BEGIN
626 -- Initialize the return status.
627 x_return_status := fnd_api.g_ret_sts_success;
628
629 IF ( x_user_id IS NULL
630 OR x_user_id = pn_index_rent_utils.g_pn_miss_num)
631 THEN
632 OPEN c_user;
633
634 FETCH c_user
635 INTO x_user_id;
636
637 IF (c_user%NOTFOUND)
638 THEN
639 x_return_status := fnd_api.g_ret_sts_error;
640 ELSE
641 x_return_status := fnd_api.g_ret_sts_success;
642 END IF;
643
644 IF c_user%ISOPEN
645 THEN
646 CLOSE c_user;
647 END IF;
648 ELSE
649 OPEN c_user_id;
650
651 FETCH c_user_id
652 INTO x_user_id;
653
654 IF (c_user_id%NOTFOUND)
655 THEN
656 x_return_status := fnd_api.g_ret_sts_error;
657 ELSE
658 x_return_status := fnd_api.g_ret_sts_success;
659 END IF;
660
661 IF c_user_id%ISOPEN
662 THEN
663 CLOSE c_user_id;
664 END IF;
665 END IF;
666 EXCEPTION
667 WHEN OTHERS
668 THEN
669 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
670 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
671 fnd_message.set_token ('P_TEXT', l_api_name_full
672 || '-'
673 || SQLERRM);
674 fnd_msg_pub.ADD;
675
676 IF c_user%ISOPEN
677 THEN
678 CLOSE c_user;
679 ELSIF c_user_id%ISOPEN
680 THEN
681 CLOSE c_user_id;
682 END IF;
683
684 x_return_status := fnd_api.g_ret_sts_unexp_error;
685 END get_user_id;
686
687 -----------------------------------------------------------------------
688 PROCEDURE get_currency_code(
689 p_parameter_name IN VARCHAR2
690 , p_operation IN VARCHAR2 DEFAULT 'CREATE_RENT_INDEX'
691 , p_functional_currency_code IN VARCHAR2
692 , x_currency_code IN OUT NOCOPY VARCHAR2
693 , x_user_conversion_type OUT NOCOPY VARCHAR2
694 , x_conversion_type OUT NOCOPY VARCHAR2
695 , x_return_status OUT NOCOPY VARCHAR2
696 )
697 IS
698 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
699 l_api_name_full CONSTANT VARCHAR2 (61)
700 := g_pkg_name
701 || '.'
702 || l_api_name;
703
704 CURSOR c_currency_code
705 IS
706 SELECT pn.currency_code
707 , gl.user_conversion_type
708 , pn.conversion_type
709 FROM pn_currencies pn, gl_daily_conversion_types gl
710 WHERE pn.conversion_type = gl.conversion_type
711 AND pn.currency_code = x_currency_code;
712 --AND pn.currency_code <> NVL(x_currency_code,p_functional_currency_code);
713
714 BEGIN
715 -- Initialize the return status.
716 x_return_status := fnd_api.g_ret_sts_success;
717
718 IF ( ( x_currency_code IS NOT NULL
719 AND x_currency_code<> pn_index_rent_utils.g_pn_miss_char
720 )
721 OR
722 ( p_functional_currency_code IS NOT NULL
723 AND p_functional_currency_code <> pn_index_rent_utils.g_pn_miss_char
724 )
725 )
726 THEN
727 OPEN c_currency_code;
728
729 FETCH c_currency_code
730 INTO x_currency_code
731 ,x_user_conversion_type
732 ,x_conversion_type;
733
734 IF (c_currency_code%NOTFOUND)
735 THEN
736 x_return_status := fnd_api.g_ret_sts_error;
737 ELSE
738 x_return_status := fnd_api.g_ret_sts_success;
739 END IF;
740
741 IF c_currency_code%ISOPEN
742 THEN
743 CLOSE c_currency_code;
744
745 END IF;
746 END IF;
747 EXCEPTION
748 WHEN OTHERS
749 THEN
750 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
751 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
752 fnd_message.set_token ('P_TEXT', l_api_name_full
753 || '-'
754 || SQLERRM);
755 fnd_msg_pub.ADD;
756
757 IF c_currency_code%ISOPEN
758 THEN
759 CLOSE c_currency_code;
760 END IF;
761
762 x_return_status := fnd_api.g_ret_sts_unexp_error;
763 END get_currency_code;
764 ---------------------------------------------------------------------------
765 PROCEDURE get_increase_on(
766 p_parameter_name IN VARCHAR2
767 , p_operation IN VARCHAR2 DEFAULT 'CREATE_RENT_INDEX'
768 , p_lookup_meaning IN VARCHAR2
769 , p_lease_id IN VARCHAR2
770 , p_currency_code IN VARCHAR2
771 , x_lookup_code IN OUT NOCOPY VARCHAR2
772 , x_return_status OUT NOCOPY VARCHAR2
773 )
774 IS
775 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
776 l_api_name_full CONSTANT VARCHAR2 (61)
777 := g_pkg_name
778 || '.'
779 || l_api_name;
780
781
782 CURSOR c_lookup
783 IS
784 SELECT DISTINCT LOOK.lookup_code lookup_code
785 FROM fnd_lookups LOOK
786 ,pn_payment_terms_all TERMS
787 WHERE LOOK.lookup_type = 'PN_PAYMENT_TERM_TYPE'
788 AND LOOK.enabled_flag = 'Y'
789 AND TERMS.lease_id = p_lease_id
790 AND DECODE(TERMS.index_period_id,NULL,'APPROVED',TERMS.status)='APPROVED'
791 AND TERMS.payment_term_type_code=LOOK.lookup_code
792 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL(start_date_active, SYSDATE)) and TRUNC (NVL(end_date_active, SYSDATE))
793 AND currency_code = p_currency_code
794 AND LOOK.meaning= p_lookup_meaning;
795
796 CURSOR c_lookup_code
797 IS
798 SELECT DISTINCT LOOK.lookup_code lookup_code
799 FROM fnd_lookups LOOK
800 ,pn_payment_terms_all TERMS
801 WHERE LOOK.lookup_type = 'PN_PAYMENT_TERM_TYPE'
802 AND LOOK.enabled_flag = 'Y'
803 AND TERMS.lease_id = p_lease_id
804 AND DECODE(TERMS.index_period_id,NULL,'APPROVED',TERMS.status)='APPROVED'
805 AND TERMS.payment_term_type_code=LOOK.lookup_code
806 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL(start_date_active, SYSDATE)) and TRUNC (NVL(end_date_active, SYSDATE))
807 AND currency_code = p_currency_code
808 AND LOOK.lookup_code= x_lookup_code;
809 BEGIN
810 -- Initialize the return status.
811 x_return_status := fnd_api.g_ret_sts_success;
812
813 IF ( x_lookup_code IS NULL
814 OR x_lookup_code = pn_index_rent_utils.g_pn_miss_char
815 )
816 THEN
817 OPEN c_lookup;
818
819 FETCH c_lookup
820 INTO x_lookup_code;
821
822 IF (c_lookup%NOTFOUND)
823 THEN
824 x_return_status := fnd_api.g_ret_sts_error;
825 ELSE
826 x_return_status := fnd_api.g_ret_sts_success;
827 END IF;
828
829 IF c_lookup%ISOPEN
830 THEN
831 CLOSE c_lookup;
832 END IF;
833 ELSE
834 OPEN c_lookup_code;
835
836 FETCH c_lookup_code
837 INTO x_lookup_code;
838
839 IF (c_lookup_code%NOTFOUND)
840 THEN
841 x_return_status := fnd_api.g_ret_sts_error;
842 ELSE
843 x_return_status := fnd_api.g_ret_sts_success;
844 END IF;
845
846 IF c_lookup_code%ISOPEN
847 THEN
848 CLOSE c_lookup_code;
849 END IF;
850 END IF;
851 EXCEPTION
852 WHEN OTHERS
853 THEN
854 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
855 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
856 fnd_message.set_token ('P_TEXT', l_api_name_full
857 || '-'
858 || SQLERRM);
859 fnd_msg_pub.ADD;
860
861 IF c_lookup%ISOPEN
862 THEN
863 CLOSE c_lookup;
864 ELSIF c_lookup_code%ISOPEN
865 THEN
866 CLOSE c_lookup_code;
867 END IF;
868
869 x_return_status := fnd_api.g_ret_sts_unexp_error;
870
871 END get_increase_on;
872
873 -------------------------------------------------------------------------------------
874 -- Validate Term Template Id return sucess/error
875 -------------------------------------------------------------------------------------
876 PROCEDURE get_term_template_id (
877 p_parameter_name IN VARCHAR2
878 , p_termtemp_name IN VARCHAR2
879 , p_operation IN VARCHAR2 DEFAULT 'CREATE_RENT_INDEX'
880 , p_termtemp_type IN VARCHAR2
881 , p_org_id IN NUMBER
882 , x_return_status OUT NOCOPY VARCHAR2
883 , x_termtemp_id IN OUT NOCOPY NUMBER
884 )
885 IS
886 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
887 l_api_name_full CONSTANT VARCHAR2 (61)
888 := g_pkg_name
889 || '.'
890 || l_api_name;
891
892 CURSOR c_termtemp
893 IS
894 SELECT term_template_id
895 FROM pn_term_templates_all
896 WHERE active = 'Y'
897 --AND set_of_books_id = 1001 -- change it
898 AND org_id = p_org_id
899 AND ( (term_template_type = p_termtemp_type)
900 OR
901 ( term_template_type ='NEUTRAL')
902 )
903 AND NAME = p_termtemp_name;
904
905 CURSOR c_termtemp_id
906 IS
907 SELECT term_template_id
908 FROM pn_term_templates_all
909 WHERE active = 'Y'
910 --AND set_of_books_id = 1001 -- change it
911 AND org_id = p_org_id
912 AND ( (term_template_type = p_termtemp_type)
913 OR
914 ( term_template_type ='NEUTRAL')
915 )
916 AND term_template_id = x_termtemp_id;
917 BEGIN
918 -- Initialize the return status.
919 x_return_status := fnd_api.g_ret_sts_success;
920
921 IF (x_termtemp_id) IS NULL
922 OR
923 (x_termtemp_id=pn_index_rent_utils.g_pn_miss_num)
924 THEN
925 OPEN c_termtemp;
926
927 FETCH c_termtemp
928 INTO x_termtemp_id;
929
930 IF (c_termtemp%NOTFOUND)
931 THEN
932 x_return_status := fnd_api.g_ret_sts_error;
933 ELSE
934 x_return_status := fnd_api.g_ret_sts_success;
935 END IF;
936
937 IF c_termtemp%ISOPEN
938 THEN
939 CLOSE c_termtemp;
940 END IF;
941 ELSE
942 OPEN c_termtemp_id;
943
944 FETCH c_termtemp_id
945 INTO x_termtemp_id;
946
947 IF (c_termtemp_id%NOTFOUND)
948 THEN
949 x_return_status := fnd_api.g_ret_sts_error;
950 ELSE
951 x_return_status := fnd_api.g_ret_sts_success;
952 END IF;
953
954 IF c_termtemp_id%ISOPEN
955 THEN
956 CLOSE c_termtemp_id;
957 END IF;
958 END IF;
959
960
961 EXCEPTION
962 WHEN OTHERS
963 THEN
964 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
965 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
966 fnd_message.set_token ('P_TEXT', l_api_name_full
967 || '-'
968 || SQLERRM);
969 fnd_msg_pub.ADD;
970
971 IF c_termtemp%ISOPEN
972 THEN
973 CLOSE c_termtemp;
974 ELSIF c_termtemp_id%ISOPEN
975 THEN
976 CLOSE c_termtemp_id;
977 END IF;
978
979 x_return_status := fnd_api.g_ret_sts_unexp_error;
980 END get_term_template_id;
981
982 END PN_INDEX_RENT_UTILS;