[Home] [Help]
PACKAGE BODY: APPS.PN_LEASE_UTILS
Source
1 PACKAGE BODY PN_LEASE_UTILS AS
2 /* $Header: PNLSUTLB.pls 120.1.12020000.2 2012/10/12 11:11:46 nisinha ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'PN_LEASE_UTILS';
5 g_table_name CONSTANT VARCHAR2 (40) := 'PN_LEASES_ALL';
6 g_tl_table_name CONSTANT VARCHAR2 (40) := 'NONE';
7 g_org_id CONSTANT NUMBER := fnd_profile.VALUE ('ORG_ID');--nvl(PN_MO_CACHE_UTILS.get_current_org_id,fnd_profile.VALUE ('org_id'));
8 --:= fnd_profile.VALUE ('org_id');
9 --PN_MO_CACHE_UTILS.get_current_org_id;--Messages
10
11 PROCEDURE add_null_parameter_msg (
12 p_token_apiname VARCHAR2
13 , p_token_nullparam VARCHAR2
14 )
15 IS
16 BEGIN
17 fnd_message.set_name ('PN', 'PN_API_ALL_NULL_PARAMETER');
18 fnd_message.set_token ('API_NAME', p_token_apiname);
19 fnd_message.set_token ('NULL_PARAM', p_token_nullparam);
20 fnd_msg_pub.ADD;
21
22 END add_null_parameter_msg;
23
24 --Procedure to validate the Lookup code and Lookup Meaning for the input parameter Lookup Type
25 PROCEDURE get_lookup_code (
26 p_parameter_name IN VARCHAR2
27 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
28 , p_lookup_meaning IN VARCHAR2
29 , p_lookup_type IN VARCHAR2
30 , x_lookup_type_code IN OUT NOCOPY VARCHAR2
31 , x_return_status OUT NOCOPY VARCHAR2
32 )
33 IS
34 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
35 l_api_name_full CONSTANT VARCHAR2 (61)
36 := g_pkg_name
37 || '.'
38 || l_api_name;
39
40 CURSOR c_lookup_type
41 IS
42 SELECT flv.lookup_code
43 FROM fnd_lookup_values_vl flv
44 WHERE flv.lookup_type = p_lookup_type
45 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (flv.start_date_active
46 , SYSDATE))
47 AND TRUNC (NVL (flv.end_date_active
48 , SYSDATE))
49 AND flv.enabled_flag = 'Y'
50 AND flv.meaning = p_lookup_meaning;
51
52 CURSOR c_lookup_type_code
53 IS
54 SELECT flv.lookup_code
55 FROM fnd_lookup_values_vl flv
56 WHERE flv.lookup_type = p_lookup_type
57 AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (flv.start_date_active
58 , SYSDATE))
59 AND TRUNC (NVL (flv.end_date_active
60 , SYSDATE))
61 AND flv.enabled_flag = 'Y'
62 AND flv.lookup_code = x_lookup_type_code;
63 BEGIN
64 -- Initialize the return status.
65 x_return_status := fnd_api.g_ret_sts_success;
66
67 IF ( x_lookup_type_code IS NULL
68 OR x_lookup_type_code = pn_lease_utils.g_pn_miss_char
69 )
70 THEN
71 OPEN c_lookup_type;
72
73 FETCH c_lookup_type
74 INTO x_lookup_type_code;
75
76 IF (c_lookup_type%NOTFOUND)
77 THEN
78 /* fnd_message.set_name ('PN', 'PN_INVALID_LOOKUP_VALUES');
79 fnd_message.set_token ('LOOKUP_TYPE', p_lookup_type);
80 fnd_message.set_token ('LOOKUP_MEANING', p_lookup_meaning);
81 fnd_message.set_token ('LOOKUP_CODE', x_lookup_type_code);
82 fnd_msg_pub.ADD;*/
83 x_return_status := fnd_api.g_ret_sts_error;
84 --RAISE fnd_api.g_exc_error;
85 ELSE
86 x_return_status := fnd_api.g_ret_sts_success;
87 END IF;
88
89 IF c_lookup_type%ISOPEN
90 THEN
91 CLOSE c_lookup_type;
92 END IF;
93 ELSE
94 OPEN c_lookup_type_code;
95
96 FETCH c_lookup_type_code
97 INTO x_lookup_type_code;
98
99 IF (c_lookup_type_code%NOTFOUND)
100 THEN
101 /*fnd_message.set_name ('PN', 'PN_INVALID_LOOKUP_VALUES');
102 fnd_message.set_token ('LOOKUP_TYPE', p_lookup_type);
103 fnd_message.set_token ('LOOKUP_MEANING', p_lookup_meaning);
104 fnd_message.set_token ('LOOKUP_CODE', x_lookup_type_code);
105 fnd_msg_pub.ADD;*/
106 x_return_status := fnd_api.g_ret_sts_error;
107 --RAISE fnd_api.g_exc_error;
108 ELSE
109 x_return_status := fnd_api.g_ret_sts_success;
110 END IF;
111
112 IF c_lookup_type_code%ISOPEN
113 THEN
114 CLOSE c_lookup_type_code;
115 END IF;
116 END IF;
117 EXCEPTION
118 WHEN OTHERS
119 THEN
120 /* fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
121 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
122 fnd_message.set_token ('P_TEXT', l_api_name_full
123 || '-'
124 || SQLERRM);
125 fnd_msg_pub.ADD;*/
126 --x_return_status := fnd_api.g_ret_sts_error;
127 -- RAISE fnd_api.g_exc_error;
128 IF c_lookup_type%ISOPEN
129 THEN
130 CLOSE c_lookup_type;
131 ELSIF c_lookup_type_code%ISOPEN
132 THEN
133 CLOSE c_lookup_type_code;
134 END IF;
135
136 x_return_status := fnd_api.g_ret_sts_unexp_error;
137 END get_lookup_code;
138
139 ------------------------------------------------------------------------------------
140 --Validate Lease Name to check that it is unique within ORG_ID
141 -------------------------------------------------------------------------------------
142 PROCEDURE check_lease_name (
143 p_parameter_name IN VARCHAR2
144 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
145 , x_lease_name IN OUT NOCOPY VARCHAR2
146 , x_return_status OUT NOCOPY VARCHAR2
147 )
148 IS
149 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
150 l_api_name_full CONSTANT VARCHAR2 (61)
151 := g_pkg_name
152 || '.'
153 || l_api_name;
154
155 CURSOR c_lease_name
156 IS
157 SELECT NAME
158 FROM pn_leases_all
159 WHERE NAME = x_lease_name
160 AND org_id = fnd_global.org_id;
161 --AND org_id = g_org_id;
162 BEGIN
163 -- Initialize the return status.
164 x_return_status := fnd_api.g_ret_sts_success;
165
166 OPEN c_lease_name;
167
168 FETCH c_lease_name
169 INTO x_lease_name;
170
171 IF NOT (c_lease_name%NOTFOUND)
172 THEN
173 fnd_message.set_name ('PN', 'PN_INVALID_LEASENAME');
174 fnd_message.set_token ('LEASE_NAME', x_lease_name);
175 fnd_msg_pub.ADD;
176 x_return_status := fnd_api.g_ret_sts_error;
177 ELSE
178 x_return_status := fnd_api.g_ret_sts_success;
179 END IF;
180
181 IF c_lease_name%ISOPEN
182 THEN
183 CLOSE c_lease_name;
184 END IF;
185 EXCEPTION
186 WHEN OTHERS
187 THEN
188 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
189 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
190 fnd_message.set_token ('P_TEXT', l_api_name_full
191 || '-'
192 || SQLERRM);
193 fnd_msg_pub.ADD;
194
195 IF c_lease_name%ISOPEN
196 THEN
197 CLOSE c_lease_name;
198 END IF;
199
200 x_return_status := fnd_api.g_ret_sts_unexp_error;
201 END check_lease_name;
202
203 -------------------------------------------------------------------------------------
204 --Validate the User Info
205 -------------------------------------------------------------------------------------
206 PROCEDURE get_user_id (
207 p_parameter_name IN VARCHAR2
208 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
209 , p_user_name IN VARCHAR2
210 , x_user_id IN OUT NOCOPY NUMBER
211 , x_return_status OUT NOCOPY VARCHAR2
212 )
213 IS
214 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
215 l_api_name_full CONSTANT VARCHAR2 (61)
216 := g_pkg_name
217 || '.'
218 || l_api_name;
219
220 CURSOR c_user
221 IS
222 SELECT user_id
223 FROM fnd_user
224 WHERE TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date, SYSDATE))
225 AND TRUNC (NVL (end_date, SYSDATE))
226 AND user_name = p_user_name;
227
228 CURSOR c_user_id
229 IS
230 SELECT user_id
231 FROM fnd_user
232 WHERE TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date, SYSDATE))
233 AND TRUNC (NVL (end_date, SYSDATE))
234 AND user_id = x_user_id;
235 BEGIN
236 -- Initialize the return status.
237 x_return_status := fnd_api.g_ret_sts_success;
238
239 IF ( x_user_id IS NULL
240 OR x_user_id = PN_LEASE_UTILS.G_PN_MISS_NUM)
241 THEN
242 OPEN c_user;
243
244 FETCH c_user
245 INTO x_user_id;
246
247 IF (c_user%NOTFOUND)
248 THEN
249 /*fnd_message.set_name ('PN', 'PN_INVALID_USER');
250 fnd_message.set_token ('USER_NAME', p_user_name);
251 fnd_message.set_token ('USER_ID', x_user_id);
252 fnd_msg_pub.ADD;*/
253 x_return_status := fnd_api.g_ret_sts_error;
254 ELSE
255 x_return_status := fnd_api.g_ret_sts_success;
256 END IF;
257
258 IF c_user%ISOPEN
259 THEN
260 CLOSE c_user;
261 END IF;
262 ELSE
263 OPEN c_user_id;
264
265 FETCH c_user_id
266 INTO x_user_id;
267
268 IF (c_user_id%NOTFOUND)
269 THEN
270 /*fnd_message.set_name ('PN', 'PN_INVALID_USER');
271 fnd_message.set_token ('USER_NAME', p_user_name);
272 fnd_message.set_token ('USER_ID', x_user_id);
273 fnd_msg_pub.ADD;*/
274 x_return_status := fnd_api.g_ret_sts_error;
275 ELSE
276 x_return_status := fnd_api.g_ret_sts_success;
277 END IF;
278
279 IF c_user_id%ISOPEN
280 THEN
281 CLOSE c_user_id;
282 END IF;
283 END IF;
284 EXCEPTION
285 WHEN OTHERS
286 THEN
287 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
288 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
289 fnd_message.set_token ('P_TEXT', l_api_name_full
290 || '-'
291 || SQLERRM);
292 fnd_msg_pub.ADD;*/
293
294 IF c_user%ISOPEN
295 THEN
296 CLOSE c_user;
297 ELSIF c_user_id%ISOPEN
298 THEN
299 CLOSE c_user_id;
300 END IF;
301
302 x_return_status := fnd_api.g_ret_sts_error;
303 END get_user_id;
304
305 -------------------------------------------------------------------------------------
306 --Validate the Location Code and Location id and return the location_id
307 -------------------------------------------------------------------------------------
308 PROCEDURE get_location_id (
309 p_parameter_name IN VARCHAR2
310 , p_location_code IN VARCHAR2
311 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
312 , p_org_id IN NUMBER
313 , p_lease_class_code IN VARCHAR2
314 , p_parent_lease_id IN NUMBER
315 , p_lease_comm_date IN DATE DEFAULT NULL
316 , p_lease_term_date IN DATE DEFAULT NULL
317 , x_return_status OUT NOCOPY VARCHAR2
318 , x_location_id IN OUT NOCOPY NUMBER
319 )
320 IS
321 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
322 l_api_name_full CONSTANT VARCHAR2 (61)
323 := g_pkg_name
324 || '.'
325 || l_api_name;
326 l_location_name VARCHAR2 (100);
327 l_location_code VARCHAR (100);
328 l_meaning VARCHAR2 (150);
329 l_lookup_code VARCHAR2 (30);
330
331 CURSOR c_location
332 IS
333 SELECT loc.location_code
334 , DECODE (loc.location_type_lookup_code
335 , 'BUILDING', loc.building
336 , 'LAND', loc.building
337 , 'FLOOR', loc.FLOOR
338 , 'PARCEL', loc.FLOOR
339 , 'OFFICE', loc.office
340 , 'SECTION', loc.office
341 ) location_name
342 , lkp.meaning
343 , loc.location_type_lookup_code
344 , loc.location_id
345 FROM pn_locations_all loc
346 , fnd_lookups lkp
347 WHERE loc.status = 'A'
348 --AND loc.org_id = g_org_id
349 AND loc.org_id = fnd_global.org_id
350 AND loc.location_type_lookup_code = lkp.lookup_code
351 AND loc.location_code = p_location_code
352 AND lkp.lookup_type = 'PN_LOCATION_TYPE'
353 AND ( ( p_lease_class_code <> 'DIRECT'
354 AND NVL (loc.assignable_cust, 'Y') = 'Y'
355 )
356 OR (p_lease_class_code = 'DIRECT')
357 )
358 AND loc.active_end_date >= p_lease_comm_date
359 AND loc.active_start_date <= p_lease_term_date
360 AND ( (p_lease_class_code IN ('THIRD_PARTY', 'DIRECT'))
361 OR ( p_lease_class_code = 'SUB_LEASE'
362 AND loc.location_id IN (
363 SELECT loc2.location_id
364 FROM pn_locations_all loc2
365 START WITH loc2.location_id IN (
366 SELECT location_id
367 FROM pn_tenancies_all
368 WHERE lease_id =
369 p_parent_lease_id
370 AND status = 'A')
371 CONNECT BY PRIOR loc2.location_id =
372 loc2.parent_location_id)
373 )
374 )
375 GROUP BY loc.location_code
376 , DECODE (loc.location_type_lookup_code
377 , 'BUILDING', loc.building
378 , 'LAND', loc.building
379 , 'FLOOR', loc.FLOOR
380 , 'PARCEL', loc.FLOOR
381 , 'OFFICE', loc.office
382 , 'SECTION', loc.office
383 )
384 , lkp.meaning
385 , loc.location_type_lookup_code
386 , loc.location_id;
387
388 CURSOR c_location_id
389 IS
390 SELECT loc.location_code
391 , DECODE (loc.location_type_lookup_code
392 , 'BUILDING', loc.building
393 , 'LAND', loc.building
394 , 'FLOOR', loc.FLOOR
395 , 'PARCEL', loc.FLOOR
396 , 'OFFICE', loc.office
397 , 'SECTION', loc.office
398 ) location_name
399 , lkp.meaning
400 , loc.location_type_lookup_code
401 , loc.location_id
402 FROM pn_locations_all loc
403 , fnd_lookups lkp
404 WHERE loc.status = 'A'
405 AND loc.org_id = p_org_id
406 AND loc.location_type_lookup_code = lkp.lookup_code
407 AND loc.location_id = x_location_id
408 AND lkp.lookup_type = 'PN_LOCATION_TYPE'
409 AND ( ( p_lease_class_code <> 'DIRECT'
410 AND NVL (loc.assignable_cust, 'Y') = 'Y'
411 )
412 OR (p_lease_class_code = 'DIRECT')
413 )
414 AND loc.active_end_date >= p_lease_comm_date
415 AND loc.active_start_date <= p_lease_term_date
416 AND ( (p_lease_class_code IN ('THIRD_PARTY', 'DIRECT'))
417 OR ( p_lease_class_code = 'SUB_LEASE'
418 AND loc.location_id IN (
419 SELECT loc2.location_id
420 FROM pn_locations_all loc2
421 START WITH loc2.location_id IN (
422 SELECT location_id
423 FROM pn_tenancies_all
424 WHERE lease_id =
425 p_parent_lease_id
426 AND status = 'A')
427 CONNECT BY PRIOR loc2.location_id =
428 loc2.parent_location_id)
429 )
430 )
431 GROUP BY loc.location_code
432 , DECODE (loc.location_type_lookup_code
433 , 'BUILDING', loc.building
434 , 'LAND', loc.building
435 , 'FLOOR', loc.FLOOR
436 , 'PARCEL', loc.FLOOR
437 , 'OFFICE', loc.office
438 , 'SECTION', loc.office
439 )
440 , lkp.meaning
441 , loc.location_type_lookup_code
442 , loc.location_id;
443 BEGIN
444 -- Initialize the return status.
445 x_return_status := fnd_api.g_ret_sts_success;
446
447 IF (x_location_id IS NULL or x_location_id =PN_LEASE_UTILS.G_PN_MISS_NUM)
448 THEN
449 OPEN c_location;
450
451
452
453 FETCH c_location
454 INTO l_location_code
455 , l_location_name
456 , l_meaning
457 , l_lookup_code
458 , x_location_id;
459
460 IF (c_location%NOTFOUND)
461 THEN
462 /*fnd_message.set_name ('PN', 'PN_INVALID_LOCATION');
463 fnd_message.set_token ('LOC_ID', x_location_id);
464 fnd_message.set_token ('LOC_CODE', p_location_code);
465 fnd_msg_pub.ADD;*/
466 x_return_status := fnd_api.g_ret_sts_error;
467 ELSE
468 x_return_status := fnd_api.g_ret_sts_success;
469 END IF;
470
471 IF c_location%ISOPEN
472 THEN
473 CLOSE c_location;
474 END IF;
475 ELSE
476 OPEN c_location_id;
477
478
479
480 FETCH c_location_id
481 INTO l_location_code
482 , l_location_name
483 , l_meaning
484 , l_lookup_code
485 , x_location_id;
486
487 IF (c_location_id%NOTFOUND)
488 THEN
489 /*fnd_message.set_name ('PN', 'PN_INVALID_LOCATION');
490 fnd_message.set_token ('LOC_ID', x_location_id);
491 fnd_message.set_token ('LOC_CODE', p_location_code);
492 fnd_msg_pub.ADD;*/
493 x_return_status := fnd_api.g_ret_sts_error;
494 ELSE
495 x_return_status := fnd_api.g_ret_sts_success;
496 END IF;
497
498 IF c_location_id%ISOPEN
499 THEN
500 CLOSE c_location_id;
501 END IF;
502 END IF;
503 EXCEPTION
504 WHEN OTHERS
505 THEN
506 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
507 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
508 fnd_message.set_token ('P_TEXT', l_api_name_full
509 || '-'
510 || SQLERRM);
511 fnd_msg_pub.ADD;*/
512
513 IF c_location%ISOPEN
514 THEN
515 CLOSE c_location;
516 ELSIF c_location_id%ISOPEN
517 THEN
518 CLOSE c_location_id;
519 END IF;
520
521 x_return_status := fnd_api.g_ret_sts_error;
522 END get_location_id;
523
524 -------------------------------------------------------------------------------------
525 -- VALIDATE MASTER LEASE and Return master lease Id
526 -------------------------------------------------------------------------------------
527 PROCEDURE get_masterlease_id (
528 p_parameter_name IN VARCHAR2
529 , p_master_lease IN VARCHAR2
530 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
531 , p_org_id IN NUMBER
532 , p_lease_id IN NUMBER DEFAULT NULL
533 , x_return_status OUT NOCOPY VARCHAR2
534 , x_master_lease_id IN OUT NOCOPY VARCHAR2
535 )
536 IS
537 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
538 l_api_name_full CONSTANT VARCHAR2 (61)
539 := g_pkg_name
540 || '.'
541 || l_api_name;
542
543 CURSOR c_master_lease
544 IS
545 SELECT lease_id
546 FROM pn_leases_all
547 WHERE status = 'F'
548 AND lease_class_code = 'DIRECT'
549 AND ( (p_lease_id IS NULL)
550 OR (lease_id <> p_lease_id))
551 AND org_id = p_org_id
552 AND NAME = p_master_lease;
553
554 CURSOR c_master_lease_id
555 IS
556 SELECT lease_id
557 FROM pn_leases_all
558 WHERE status = 'F'
559 AND lease_class_code = 'DIRECT'
560 AND ( (p_lease_id IS NULL)
561 OR (lease_id <> p_lease_id))
562 AND org_id = p_org_id
563 AND lease_id = x_master_lease_id;
564 BEGIN
565 -- Initialize the return status.
566 x_return_status := fnd_api.g_ret_sts_success;
567
568 IF (x_master_lease_id IS NULL
569 OR
570 x_master_lease_id=PN_LEASE_UTILS.G_PN_MISS_NUM)
571 THEN
572 OPEN c_master_lease;
573
574 FETCH c_master_lease
575 INTO x_master_lease_id;
576
577 IF (c_master_lease%NOTFOUND)
578 THEN
579 /*fnd_message.set_name ('PN', 'PN_INVALID_MASTERLEASE');
580 fnd_message.set_token ('MASTER_LEASE_ID',x_master_lease_id );
581 fnd_message.set_token ('MASTER_LEASE_NAME', p_master_lease);
582 fnd_msg_pub.ADD;*/
583 x_return_status := fnd_api.g_ret_sts_error;
584 ELSE
585 x_return_status := fnd_api.g_ret_sts_success;
586 END IF;
587
588 IF c_master_lease%ISOPEN
589 THEN
590 CLOSE c_master_lease;
591 END IF;
592 ELSE
593 OPEN c_master_lease_id;
594
595 FETCH c_master_lease_id
596 INTO x_master_lease_id;
597
598 IF (c_master_lease_id%NOTFOUND)
599 THEN
600 /*fnd_message.set_name ('PN', 'PN_INVALID_MASTERLEASE');
601 fnd_message.set_token ('MASTER_LEASE_ID',x_master_lease_id );
602 fnd_message.set_token ('MASTER_LEASE_NAME', p_master_lease);
603 fnd_msg_pub.ADD;*/
604 x_return_status := fnd_api.g_ret_sts_error;
605 ELSE
606 x_return_status := fnd_api.g_ret_sts_success;
607 END IF;
608
609 IF c_master_lease_id%ISOPEN
610 THEN
611 CLOSE c_master_lease_id;
612 END IF;
613 END IF;
614 EXCEPTION
615 WHEN OTHERS
616 THEN
617 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
618 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
619 fnd_message.set_token ('P_TEXT', l_api_name_full
620 || '-'
621 || SQLERRM);
622 fnd_msg_pub.ADD;*/
623 x_return_status := fnd_api.g_ret_sts_unexp_error;
624 IF c_master_lease%ISOPEN
625 THEN
626 CLOSE c_master_lease;
627 ELSIF c_master_lease_id%ISOPEN
628 THEN
629 CLOSE c_master_lease_id;
630 END IF;
631
632 x_return_status := fnd_api.g_ret_sts_unexp_error;
633 END get_masterlease_id;
634
635 -------------------------------------------------------------------------------------
636 -- Validate Lease Commencement and Termination Dates and return sucess/error
637 -------------------------------------------------------------------------------------
638 PROCEDURE crossval_leasedates (
639 p_parameter_name IN VARCHAR2
640 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
641 , p_lease_exec_date IN DATE
642 , p_lease_comm_date IN DATE
643 , p_lease_term_date IN DATE
644 , x_return_status OUT NOCOPY VARCHAR2
645 )
646 IS
647 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
648 l_api_name_full CONSTANT VARCHAR2 (61)
649 := g_pkg_name
650 || '.'
651 || l_api_name;
652 BEGIN
653 /*Bug#14651192 replaced the dates when null by g_pn_miss_date*/
654 IF Nvl(p_lease_term_date,pn_lease_utils.g_pn_miss_date) >= Nvl(p_lease_comm_date,pn_lease_utils.g_pn_miss_date)
655 THEN
656 x_return_status := fnd_api.g_ret_sts_success;
657 ELSE
658 /*fnd_message.set_name ('PN', 'PN_INVALID_TERMINATION_DT');
659 fnd_message.set_token ('TERMINATION_DATE', p_lease_term_date);
660 fnd_message.set_token ('COMMENCEMENT_DATE', p_lease_comm_date);
661 fnd_msg_pub.ADD;*/
662 x_return_status := fnd_api.g_ret_sts_error;
663 END IF;
664 EXCEPTION
665 WHEN OTHERS
666 THEN
667 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
668 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
669 fnd_message.set_token ('P_TEXT', l_api_name_full
670 || '-'
671 || SQLERRM);
672 fnd_msg_pub.ADD;*/
673 x_return_status := fnd_api.g_ret_sts_error;
674 END crossval_leasedates;
675
676 -------------------------------------------------------------------------------------
677 -- Validate Term Template Id return sucess/error
678 -------------------------------------------------------------------------------------
679 PROCEDURE get_term_template_id (
680 p_parameter_name IN VARCHAR2
681 , p_termtemp_name IN VARCHAR2
682 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
683 , p_termtemp_type IN VARCHAR2
684 , p_org_id IN NUMBER
685 , x_return_status OUT NOCOPY VARCHAR2
686 , x_termtemp_id IN OUT NOCOPY NUMBER
687 )
688 IS
689 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
690 l_api_name_full CONSTANT VARCHAR2 (61)
691 := g_pkg_name
692 || '.'
693 || l_api_name;
694
695 CURSOR c_termtemp
696 IS
697 SELECT term_template_id
698 FROM pn_term_templates_all
699 WHERE active = 'Y'
700 --AND set_of_books_id = 1001
701 AND org_id = p_org_id
702 AND ( (term_template_type = p_termtemp_type)
703 OR
704 ( term_template_type ='NEUTRAL')
705 )
706 AND NAME = p_termtemp_name;
707
708 CURSOR c_termtemp_id
709 IS
710 SELECT term_template_id
711 FROM pn_term_templates_all
712 WHERE active = 'Y'
713 --AND set_of_books_id = 1001
714 AND org_id = p_org_id
715 AND ( (term_template_type = p_termtemp_type)
716 OR
717 ( term_template_type ='NEUTRAL')
718 )
719 AND term_template_id = x_termtemp_id;
720 BEGIN
721 -- Initialize the return status.
722 x_return_status := fnd_api.g_ret_sts_success;
723
724 IF (x_termtemp_id) IS NULL
725 OR
726 (x_termtemp_id=PN_LEASE_UTILS.G_PN_MISS_NUM)
727 THEN
728 OPEN c_termtemp;
729
730 FETCH c_termtemp
731 INTO x_termtemp_id;
732
733 IF (c_termtemp%NOTFOUND)
734 THEN
735 /*fnd_message.set_name ('PN', 'PN_INVALID_TERM_TEMPLATE');
736 fnd_message.set_token ('TERM_TEMPLATE_ID', x_termtemp_id);
737 fnd_message.set_token ('TERM_TEMPLATE', p_termtemp_name);
738
739
740 fnd_msg_pub.ADD;*/
741 x_return_status := fnd_api.g_ret_sts_error;
742 ELSE
743 x_return_status := fnd_api.g_ret_sts_success;
744 END IF;
745
746 IF c_termtemp%ISOPEN
747 THEN
748 CLOSE c_termtemp;
749 END IF;
750 ELSE
751 OPEN c_termtemp_id;
752
753 FETCH c_termtemp_id
754 INTO x_termtemp_id;
755
756 IF (c_termtemp_id%NOTFOUND)
757 THEN
758 /*fnd_message.set_name ('PN', 'PN_INVALID_TERM_TEMPLATE');
759 fnd_message.set_token ('TERM_TEMPLATE_ID', x_termtemp_id);
760 fnd_message.set_token ('TERM_TEMPLATE', p_termtemp_name);
761
762
763 fnd_msg_pub.ADD;*/
764 x_return_status := fnd_api.g_ret_sts_error;
765 ELSE
766 x_return_status := fnd_api.g_ret_sts_success;
767 END IF;
768
769 IF c_termtemp_id%ISOPEN
770 THEN
771 CLOSE c_termtemp_id;
772 END IF;
773 END IF;
774 EXCEPTION
775 WHEN OTHERS
776 THEN
777 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
778 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
779 fnd_message.set_token ('P_TEXT', l_api_name_full
780 || '-'
781 || SQLERRM);
782 fnd_msg_pub.ADD;*/
783
784 IF c_termtemp%ISOPEN
785 THEN
786 CLOSE c_termtemp;
787 ELSIF c_termtemp_id%ISOPEN
788 THEN
789 CLOSE c_termtemp_id;
790 END IF;
791
792 x_return_status := fnd_api.g_ret_sts_error;
793 END get_term_template_id;
794
795
796 -------------------------------------------------------------------------------------
797 -- Validate GET_INVOICE_GROUPING_RULE
798 -------------------------------------------------------------------------------------
799 PROCEDURE get_invoice_grouping_rule(
800 p_parameter_name IN VARCHAR2
801 , p_grouping_rule IN VARCHAR2
802 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
803 , x_return_status OUT NOCOPY VARCHAR2
804 , x_grouping_rule_id IN OUT NOCOPY NUMBER
805 )
806 IS
807 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
808 l_api_name_full CONSTANT VARCHAR2 (61)
809 := g_pkg_name
810 || '.'
811 || l_api_name;
812
813 CURSOR c_grouping_rule
814 IS
815 SELECT grouping_rule_id
816 FROM pn_pay_group_rules
817 WHERE name=p_grouping_rule;
818
819 CURSOR c_grouping_rule_id
820 IS
821 SELECT grouping_rule_id
822 FROM pn_pay_group_rules
823 WHERE grouping_rule_id=x_grouping_rule_id;
824 BEGIN
825 -- Initialize the return status.
826 x_return_status := fnd_api.g_ret_sts_success;
827
828 IF (x_grouping_rule_id IS NULL OR x_grouping_rule_id=PN_LEASE_UTILS.G_PN_MISS_NUM)
829 THEN
830 OPEN c_grouping_rule;
831
832 FETCH c_grouping_rule
833 INTO x_grouping_rule_id;
834
835 IF (c_grouping_rule%NOTFOUND)
836 THEN
837 /*fnd_message.set_name ('PN', 'PN_INVOICE_GROUPING_RULE');
838 fnd_message.set_token ('GROUPING_ID', x_grouping_rule_id);
839 fnd_message.set_token ('GROUPING_RULE', p_grouping_rule);
840
841
842 fnd_msg_pub.ADD;*/
843 x_return_status := fnd_api.g_ret_sts_error;
844 ELSE
845 x_return_status := fnd_api.g_ret_sts_success;
846 END IF;
847
848 IF c_grouping_rule%ISOPEN
849 THEN
850 CLOSE c_grouping_rule;
851 END IF;
852 ELSE
853 OPEN c_grouping_rule_id;
854
855 FETCH c_grouping_rule_id
856 INTO x_grouping_rule_id;
857
858 IF (c_grouping_rule_id%NOTFOUND)
859 THEN
860 /*fnd_message.set_name ('PN', 'PN_INVOICE_GROUPING_RULE');
861 fnd_message.set_token ('GROUPING_ID', x_grouping_rule_id);
862 fnd_message.set_token ('GROUPING_RULE', p_grouping_rule);
863
864
865 fnd_msg_pub.ADD;*/
866 x_return_status := fnd_api.g_ret_sts_error;
867 ELSE
868 x_return_status := fnd_api.g_ret_sts_success;
869 END IF;
870
871 IF c_grouping_rule_id%ISOPEN
872 THEN
873 CLOSE c_grouping_rule_id;
874 END IF;
875 END IF;
876 EXCEPTION
877 WHEN OTHERS
878 THEN
879 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
880 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
881 fnd_message.set_token ('P_TEXT', l_api_name_full
882 || '-'
883 || SQLERRM);
884 fnd_msg_pub.ADD;*/
885
886 IF c_grouping_rule%ISOPEN
887 THEN
888 CLOSE c_grouping_rule;
889 ELSIF c_grouping_rule_id%ISOPEN
890 THEN
891 CLOSE c_grouping_rule_id;
892 END IF;
893
894 x_return_status := fnd_api.g_ret_sts_error;
895
896 END get_invoice_grouping_rule;
897 ---------------------------------Rupak BEGIN-------------------------
898
899
900 -- Validate_Desc_Flex
901 PROCEDURE validate_desc_flex (
902 p_api_name IN VARCHAR2
903 , p_application_short_name IN VARCHAR2
904 , p_desc_flex_name IN VARCHAR2
905 , p_desc_segment1 IN VARCHAR2
906 , p_desc_segment2 IN VARCHAR2
907 , p_desc_segment3 IN VARCHAR2
908 , p_desc_segment4 IN VARCHAR2
909 , p_desc_segment5 IN VARCHAR2
910 , p_desc_segment6 IN VARCHAR2
911 , p_desc_segment7 IN VARCHAR2
912 , p_desc_segment8 IN VARCHAR2
913 , p_desc_segment9 IN VARCHAR2
914 , p_desc_segment10 IN VARCHAR2
915 , p_desc_segment11 IN VARCHAR2
916 , p_desc_segment12 IN VARCHAR2
917 , p_desc_segment13 IN VARCHAR2
918 , p_desc_segment14 IN VARCHAR2
919 , p_desc_segment15 IN VARCHAR2
920 , p_desc_context IN VARCHAR2
921 , p_resp_appl_id IN NUMBER := NULL
922 , p_resp_id IN NUMBER := NULL
923 , p_return_status OUT NOCOPY VARCHAR2
924 )
925 IS
926 l_error_message VARCHAR2 (2000);
927 BEGIN
928 -- Initialize API return status to success
929 p_return_status := fnd_api.g_ret_sts_success;
930
931 IF ( p_desc_context
932 || p_desc_segment1
933 || p_desc_segment2
934 || p_desc_segment3
935 || p_desc_segment4
936 || p_desc_segment5
937 || p_desc_segment6
938 || p_desc_segment7
939 || p_desc_segment8
940 || p_desc_segment9
941 || p_desc_segment10
942 || p_desc_segment11
943 || p_desc_segment12
944 || p_desc_segment13
945 || p_desc_segment14
946 || p_desc_segment15
947 ) IS NOT NULL
948 THEN
949 fnd_flex_descval.set_context_value (p_desc_context);
950 fnd_flex_descval.set_column_value ('ATTRIBUTE1', p_desc_segment1);
951 fnd_flex_descval.set_column_value ('ATTRIBUTE2', p_desc_segment2);
952 fnd_flex_descval.set_column_value ('ATTRIBUTE3', p_desc_segment3);
953 fnd_flex_descval.set_column_value ('ATTRIBUTE4', p_desc_segment4);
954 fnd_flex_descval.set_column_value ('ATTRIBUTE5', p_desc_segment5);
955 fnd_flex_descval.set_column_value ('ATTRIBUTE6', p_desc_segment6);
956 fnd_flex_descval.set_column_value ('ATTRIBUTE7', p_desc_segment7);
957 fnd_flex_descval.set_column_value ('ATTRIBUTE8', p_desc_segment8);
958 fnd_flex_descval.set_column_value ('ATTRIBUTE9', p_desc_segment9);
959 fnd_flex_descval.set_column_value ('ATTRIBUTE10', p_desc_segment10);
960 fnd_flex_descval.set_column_value ('ATTRIBUTE11', p_desc_segment11);
961 fnd_flex_descval.set_column_value ('ATTRIBUTE12', p_desc_segment12);
962 fnd_flex_descval.set_column_value ('ATTRIBUTE13', p_desc_segment13);
963 fnd_flex_descval.set_column_value ('ATTRIBUTE14', p_desc_segment14);
964 fnd_flex_descval.set_column_value ('ATTRIBUTE15', p_desc_segment15);
965
966
967 IF NOT fnd_flex_descval.validate_desccols
968 (appl_short_name => p_application_short_name
969 , desc_flex_name => p_desc_flex_name
970 , resp_appl_id => p_resp_appl_id
971 , resp_id => p_resp_id
972 )
973 THEN
974 l_error_message := fnd_flex_descval.error_message;
975 --pn_lease_utils.add_desc_flex_msg (p_api_name, l_error_message);
976 p_return_status := fnd_api.g_ret_sts_error;
977
978 END IF;
979 END IF;
980 EXCEPTION
981 WHEN OTHERS THEN
982 p_return_status := fnd_api.g_ret_sts_unexp_error;
983 END validate_desc_flex;
984
985 PROCEDURE add_desc_flex_msg (
986 p_token_an VARCHAR2
987 , p_token_dfm VARCHAR2
988 )
989 IS
990 BEGIN
991 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
992 THEN
993 NULL;
994 /*fnd_message.set_name ('PN', 'PN_API_LEASE_DESC_FLEX_ERR');
995 fnd_message.set_token ('API_NAME', p_token_an);
996 fnd_message.set_token ('DESC_FLEX_MSG', p_token_dfm);
997 fnd_msg_pub.ADD;*/
998 END IF;
999 END add_desc_flex_msg;
1000
1001 ---------------------------------Rupak END---------------------------
1002 ---------------------------------Vivek Additions Begin ---------------------------
1003 PROCEDURE get_account_id (
1004 p_parameter_name IN VARCHAR2
1005 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
1006 , p_account_id IN OUT NOCOPY NUMBER
1007 , p_account_conc_segs IN OUT NOCOPY VARCHAR2
1008 , p_account_segment1 IN VARCHAR2
1009 , p_account_segment2 IN VARCHAR2
1010 , p_account_segment3 IN VARCHAR2
1011 , p_account_segment4 IN VARCHAR2
1012 , p_account_segment5 IN VARCHAR2
1013 , p_account_segment6 IN VARCHAR2
1014 , p_account_segment7 IN VARCHAR2
1015 , p_account_segment8 IN VARCHAR2
1016 , p_account_segment9 IN VARCHAR2
1017 , p_account_segment10 IN VARCHAR2
1018 , p_account_segment11 IN VARCHAR2
1019 , p_account_segment12 IN VARCHAR2
1020 , p_account_segment13 IN VARCHAR2
1021 , p_account_segment14 IN VARCHAR2
1022 , p_account_segment15 IN VARCHAR2
1023 , p_account_segment16 IN VARCHAR2
1024 , p_account_segment17 IN VARCHAR2
1025 , p_account_segment18 IN VARCHAR2
1026 , p_account_segment19 IN VARCHAR2
1027 , p_account_segment20 IN VARCHAR2
1028 , x_return_status OUT NOCOPY VARCHAR2
1029 )
1030 IS
1031 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
1032 l_api_name_full CONSTANT VARCHAR2 (61)
1033 := g_pkg_name
1034 || '.'
1035 || l_api_name;
1036
1037 CURSOR c_structure_num
1038 IS
1039 SELECT chart_of_accounts_id
1040 FROM gl_sets_of_books
1041 WHERE set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
1042
1043 CURSOR c_get_account (
1044 p_chart_of_acct_id IN NUMBER
1045 )
1046 IS
1047 SELECT code_combination_id
1048 FROM gl_code_combinations
1049 WHERE code_combination_id = p_account_id
1050 AND chart_of_accounts_id = p_chart_of_acct_id
1051 AND enabled_flag = 'Y';
1052
1053 l_appl_short_name CONSTANT VARCHAR2 (5) := 'SQLGL';
1054 l_key_flex_code CONSTANT VARCHAR2 (5) := 'GL#';
1055 l_structure_num NUMBER;
1056 -- := PN_LEASE_UTILS.G_PN_MISS_NUM;
1057 l_delimitor VARCHAR2 (1);
1058 -- := PN_LEASE_UTILS.G_PN_MISS_CHAR;
1059 l_account_id NUMBER;
1060 -- := PN_LEASE_UTILS.G_PN_MISS_NUM;
1061 l_concatenated_seg VARCHAR2 (800);
1062 -- := PN_LEASE_UTILS.G_PN_MISS_CHAR;
1063 BEGIN
1064 -- Initialize the return status.
1065 x_return_status := fnd_api.g_ret_sts_success;
1066 --------dbms_output.put_line ('IN Get_account_id procedure: ');
1067
1068 OPEN c_structure_num;
1069
1070 FETCH c_structure_num
1071 INTO l_structure_num;
1072
1073 IF (c_structure_num%NOTFOUND)
1074 THEN
1075 /*fnd_message.set_name ('PN', 'PN_CHART_OF_ACCOUNTS_ID');
1076 fnd_message.set_token ('STRUCTURE_NUM', l_structure_num);
1077 fnd_message.set_token ('SET_OF_BOOKS_ID', fnd_profile.VALUE ('GL_SET_OF_BKS_ID'));
1078
1079 fnd_msg_pub.ADD;*/
1080 x_return_status := fnd_api.g_ret_sts_error;
1081 ELSE
1082 x_return_status := fnd_api.g_ret_sts_success;
1083 END IF;
1084
1085 IF c_structure_num%ISOPEN
1086 THEN
1087 CLOSE c_structure_num;
1088 END IF;
1089
1090 -- Validate and Get account_id using account id
1091 IF ( (p_account_id IS NOT NULL)
1092 AND (p_account_id <> pn_lease_utils.g_pn_miss_num
1093 ) --Rupak Changed this
1094 )
1095 THEN
1096 ------dbms_output.put_line ('In UTILS:IF: account ID');
1097
1098 OPEN c_get_account (l_structure_num);
1099
1100 ------dbms_output.put_line ( 'Chart Of Accounts id: '|| l_structure_num);
1101
1102 FETCH c_get_account
1103 INTO p_account_id;
1104
1105 ------dbms_output.put_line ( 'Account id: '|| p_account_id);
1106
1107 IF (c_get_account%NOTFOUND)
1108 THEN
1109 /*fnd_message.set_name ('PN', 'PN_ACCOUNT_ID');
1110 fnd_message.set_token ('ACCOUNT_ID', p_account_id);
1111
1112 fnd_msg_pub.ADD;*/
1113 x_return_status := fnd_api.g_ret_sts_error;
1114 ELSE
1115 x_return_status := fnd_api.g_ret_sts_success;
1116 END IF;
1117
1118 IF c_get_account%ISOPEN
1119 THEN
1120 CLOSE c_get_account;
1121 END IF;
1122 ELSIF ( (p_account_conc_segs IS NOT NULL)
1123 AND (p_account_conc_segs <> pn_lease_utils.g_pn_miss_char
1124 ) --Rupak Changed this
1125 )
1126 THEN
1127 ------dbms_output.put_line('In UTILS ElseIF: Concanated Segments:p_account_segment1.Segment1');
1128 p_account_id :=
1129 fnd_flex_ext.get_ccid
1130 (application_short_name => l_appl_short_name
1131 , key_flex_code => l_key_flex_code
1132 , structure_number => l_structure_num
1133 , validation_date => TRUNC(SYSDATE)
1134 , concatenated_segments => p_account_conc_segs
1135 );
1136
1137 IF p_account_id = 0
1138 THEN
1139 /*fnd_message.set_name ('PN', 'PN_ACCOUNT_CONC_SEGS');
1140 fnd_message.set_token ('ACCOUNT_CONC_SEGS', p_account_conc_segs);
1141
1142 fnd_msg_pub.ADD;*/
1143 x_return_status := fnd_api.g_ret_sts_error;
1144 ELSE
1145 p_account_id := l_account_id;
1146 END IF;
1147 ELSE
1148 --------dbms_output.put_line--('In UTILS Else: Given Individual Segments:p_account_segment1.Segment1');
1149 l_delimitor :=
1150 fnd_flex_ext.get_delimiter
1151 (application_short_name => l_appl_short_name
1152 , key_flex_code => l_key_flex_code
1153 , structure_number => l_structure_num
1154 );
1155 l_concatenated_seg :=
1156 p_account_segment1
1157 || l_delimitor
1158 || p_account_segment2
1159 || l_delimitor
1160 || p_account_segment3
1161 || l_delimitor
1162 || p_account_segment4
1163 || l_delimitor
1164 || p_account_segment5
1165 || l_delimitor
1166 || p_account_segment6
1167 || l_delimitor
1168 || p_account_segment7
1169 || l_delimitor
1170 || p_account_segment8
1171 || l_delimitor
1172 || p_account_segment9
1173 || l_delimitor
1174 || p_account_segment10
1175 || l_delimitor
1176 || p_account_segment11
1177 || l_delimitor
1178 || p_account_segment12
1179 || l_delimitor
1180 || p_account_segment13
1181 || l_delimitor
1182 || p_account_segment14
1183 || l_delimitor
1184 || p_account_segment15
1185 || l_delimitor
1186 || p_account_segment16
1187 || l_delimitor
1188 || p_account_segment17
1189 || l_delimitor
1190 || p_account_segment18
1191 || l_delimitor
1192 || p_account_segment19
1193 || l_delimitor
1194 || p_account_segment20;
1195 p_account_id :=
1196 fnd_flex_ext.get_ccid
1197 (application_short_name => l_appl_short_name
1198 , key_flex_code => l_key_flex_code
1199 , structure_number => l_structure_num
1200 , validation_date => TRUNC(SYSDATE)
1201 , concatenated_segments => l_concatenated_seg
1202 );
1203 /*------dbms_output.put_line ( 'In UtilsELSE PART:p_account_id'
1204 || p_account_id);*/
1205
1206 IF p_account_id = 0
1207 THEN
1208 /*fnd_message.set_name ('PN', 'PN_ACCOUNT_SEGMENTS');
1209 fnd_message.set_token ('ACCOUNT_SEGMENTS', l_concatenated_seg);
1210
1211 fnd_msg_pub.ADD;*/
1212 x_return_status := fnd_api.g_ret_sts_error;
1213 --ELSE
1214 -- p_account_id:= l_account_id;
1215 END IF;
1216 END IF;
1217 EXCEPTION
1218 WHEN OTHERS
1219 THEN
1220 /* fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1221 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
1222 fnd_message.set_token ('P_TEXT', l_api_name_full
1223 || '-'
1224 || SQLERRM);
1225 fnd_msg_pub.ADD;*/
1226
1227 IF c_structure_num%ISOPEN
1228 THEN
1229 CLOSE c_structure_num;
1230 END IF;
1231
1232 IF c_get_account%ISOPEN
1233 THEN
1234 CLOSE c_get_account;
1235 END IF;
1236
1237 x_return_status := fnd_api.g_ret_sts_error;
1238 END get_account_id;
1239
1240 -- GET_COMPANY_ID
1241 PROCEDURE get_company_id (
1242 p_parameter_name IN VARCHAR2
1243 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
1244 , p_lease_role_type IN VARCHAR2
1245 , p_company_name IN VARCHAR2
1246 , x_return_status OUT NOCOPY VARCHAR2
1247 , x_company_id IN OUT NOCOPY NUMBER
1248 )
1249 IS
1250 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
1251 l_api_name_full CONSTANT VARCHAR2 (61)
1252 := g_pkg_name
1253 || '.'
1254 || l_api_name;
1255
1256 CURSOR c_company_id (
1257 p_lease_role_type VARCHAR2
1258 )
1259 IS
1260 SELECT comp.company_id company_id
1261 FROM pn_companies_all comp
1262 WHERE EXISTS (
1263 SELECT 1
1264 FROM pn_company_sites_all csite
1265 WHERE csite.lease_role_type = p_lease_role_type
1266 AND comp.company_id = csite.company_id)
1267 AND comp.company_id = x_company_id
1268 --AND comp.org_id = g_org_id;
1269 AND comp.org_id = fnd_profile.VALUE('org_id');
1270 CURSOR c_company_name (
1271 p_lease_role_type VARCHAR2
1272 )
1273 IS
1274 SELECT comp.company_id company_id
1275 FROM pn_companies_all comp
1276 WHERE EXISTS (
1277 SELECT 1
1278 FROM pn_company_sites_all csite
1279 WHERE csite.lease_role_type = p_lease_role_type
1280 AND comp.company_id = csite.company_id)
1281 AND comp.NAME = p_company_name
1282 -- AND comp.org_id = g_org_id;
1283 AND comp.org_id =fnd_profile.VALUE('org_id');
1284 BEGIN
1285 IF ( x_company_id IS NULL
1286 OR x_company_id = pn_lease_utils.g_pn_miss_num
1287 )
1288 THEN
1289 OPEN c_company_name (p_lease_role_type);
1290
1291 FETCH c_company_name
1292 INTO x_company_id;
1293 ----dbms_output.put_line('Company ID:'||x_company_id);
1294 ----dbms_output.put_line('PN_MO_CACHE_UTILS.get_current_org_id'||PN_MO_CACHE_UTILS.get_current_org_id);
1295 -------dbms_output.put_line('fnd_profile.VALUE.org_id'||fnd_profile.VALUE('org_id'));
1296 ----dbms_output.put_line('fnd_global.org_id'||fnd_global.org_id);
1297 IF (c_company_name%NOTFOUND)
1298 THEN
1299 /* fnd_message.set_name ('PN', 'PN_INVALID_COMPANY');
1300 fnd_message.set_token ('COMPANY_NAME', p_company_name);
1301 fnd_message.set_token ('COMPANY_ID', x_company_id);
1302 fnd_msg_pub.ADD;*/
1303 x_return_status := fnd_api.g_ret_sts_error;
1304 ELSE
1305 x_return_status := fnd_api.g_ret_sts_success;
1306 END IF;
1307
1308 IF c_company_name%ISOPEN
1309 THEN
1310 CLOSE c_company_name;
1311 END IF;
1312 ELSE
1313 OPEN c_company_id (p_lease_role_type);
1314
1315 FETCH c_company_id
1316 INTO x_company_id;
1317
1318
1319 IF (c_company_id%NOTFOUND)
1320 THEN
1321 /* fnd_message.set_name ('PN', 'PN_INVALID_COMPANY');
1322 fnd_message.set_token ('COMPANY_NAME', p_company_name);
1323 fnd_message.set_token ('COMPANY_ID', x_company_id);
1324 fnd_msg_pub.ADD;*/
1325
1326 x_return_status := fnd_api.g_ret_sts_error;
1327 ELSE
1328 x_return_status := fnd_api.g_ret_sts_success;
1329 END IF;
1330
1331 IF c_company_id%ISOPEN
1332 THEN
1333 CLOSE c_company_id;
1334 END IF;
1335 END IF;
1336 EXCEPTION
1337 WHEN OTHERS
1338 THEN
1339 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1340 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
1341 fnd_message.set_token ('P_TEXT', l_api_name_full
1342 || '-'
1343 || SQLERRM);
1344 fnd_msg_pub.ADD;*/
1345
1346 IF c_company_name%ISOPEN
1347 THEN
1348 CLOSE c_company_name;
1349 ELSIF c_company_id%ISOPEN
1350 THEN
1351 CLOSE c_company_id;
1352 END IF;
1353
1354 x_return_status := fnd_api.g_ret_sts_error;
1355 END get_company_id;
1356
1357 -- GET_COMPANY_SITE_ID
1358 PROCEDURE get_company_site_id (
1359 p_parameter_name IN VARCHAR2
1360 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
1361 , p_company_id IN VARCHAR2
1362 , p_company_site_name IN VARCHAR
1363 , x_return_status OUT NOCOPY VARCHAR2
1364 , x_company_site_id IN OUT NOCOPY NUMBER
1365 )
1366 IS
1367 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
1368 l_api_name_full CONSTANT VARCHAR2 (61)
1369 := g_pkg_name
1370 || '.'
1371 || l_api_name;
1372
1373 CURSOR c_company_site_id (
1374 p_company_id NUMBER
1375 , x_company_site_id NUMBER
1376 )
1377 IS
1378 SELECT company_site_id
1379 FROM pn_company_sites_all
1380 WHERE company_id = p_company_id
1381 AND company_site_id = x_company_site_id
1382 --AND org_id = g_org_id;
1383 AND org_id = fnd_profile.VALUE('ORG_ID');
1384
1385 CURSOR c_company_site_name (
1386 p_company_id NUMBER
1387 , p_company_site_name VARCHAR2
1388 )
1389 IS
1390 SELECT company_site_id
1391 FROM pn_company_sites_all
1392 WHERE company_id = p_company_id
1393 AND NAME = p_company_site_name
1394 --AND org_id = g_org_id;
1395 AND org_id = fnd_profile.VALUE('ORG_ID');
1396 BEGIN
1397 IF ( x_company_site_id IS NULL
1398 OR x_company_site_id = pn_lease_utils.g_pn_miss_num
1399 )
1400 THEN
1401 /*------dbms_output.put_line
1402 ( 'In Company Site:Hi:Company ID :p_company_site_name '
1403 || p_company_id
1404 || '-'
1405 || p_company_site_name);*/
1406
1407 OPEN c_company_site_name (p_company_id, p_company_site_name);
1408
1409 FETCH c_company_site_name
1410 INTO x_company_site_id;
1411
1412 /*------dbms_output.put_line ( 'In Company Site:company-site-id :'
1413 || x_company_site_id);*/
1414
1415 IF (c_company_site_name%NOTFOUND)
1416 THEN
1417 /*fnd_message.set_name ('PN', 'PN_INVALID_COMPANY_SITE');
1418 fnd_message.set_token ('COMPANY_SITE_NAME', p_company_site_name);
1419 fnd_message.set_token ('COMPANY_SITE_ID', x_company_site_id);
1420 fnd_msg_pub.ADD;*/
1421 x_return_status := fnd_api.g_ret_sts_error;
1422 ELSE
1423 x_return_status := fnd_api.g_ret_sts_success;
1424 END IF;
1425
1426 IF c_company_site_name%ISOPEN
1427 THEN
1428 CLOSE c_company_site_name;
1429 END IF;
1430 ELSE
1431 /*------dbms_output.put_line
1432 ( 'In Company Site:ELSE:Company ID :x_company_site_id '
1433 || p_company_id
1434 || '-'
1435 || x_company_site_id);*/
1436
1437 OPEN c_company_site_id (p_company_id, x_company_site_id);
1438
1439 FETCH c_company_site_id
1440 INTO x_company_site_id;
1441
1442 /*------dbms_output.put_line
1443 ( 'In Company Site:ELSE:AFTERcompany-site-id :'
1444 || x_company_site_id);*/
1445
1446 IF (c_company_site_id%NOTFOUND)
1447 THEN
1448 /*fnd_message.set_name ('PN', 'PN_INVALID_COMPANY_SITE');
1449 fnd_message.set_token ('COMPANY_SITE_NAME', p_company_site_name);
1450 fnd_message.set_token ('COMPANY_SITE_ID', x_company_site_id);
1451 fnd_msg_pub.ADD;*/
1452 x_return_status := fnd_api.g_ret_sts_error;
1453 ELSE
1454 x_return_status := fnd_api.g_ret_sts_success;
1455 END IF;
1456
1457 IF c_company_site_id%ISOPEN
1458 THEN
1459 CLOSE c_company_site_id;
1460 END IF;
1461 END IF;
1462 EXCEPTION
1463 WHEN OTHERS
1464 THEN
1465 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1466 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
1467 fnd_message.set_token ('P_TEXT', l_api_name_full
1468 || '-'
1469 || SQLERRM);
1470 fnd_msg_pub.ADD;*/
1471
1472 IF c_company_site_name%ISOPEN
1473 THEN
1474 CLOSE c_company_site_name;
1475 ELSIF c_company_site_id%ISOPEN
1476 THEN
1477 CLOSE c_company_site_id;
1478 END IF;
1479
1480 x_return_status := fnd_api.g_ret_sts_error;
1481 END get_company_site_id;
1482 /*
1483 PROCEDURE VALIDATE_TENANCY_DATES (
1484 p_parameter_name IN VARCHAR2
1485 , p_operation IN VARCHAR2
1486 , p_estimated_occ_date IN DATE
1487 , p_actual_occ_date IN DATE
1488 , p_tenancy_exp_date IN DATE
1489 , p_lease_comm_date IN DATE
1490 , p_lease_term_date IN DATE
1491 , x_return_status OUT NOCOPY VARCHAR2
1492 )
1493 IS
1494 l_api_name CONSTANT VARCHAR2 (30) := 'TENANCYDATES_CROSSVAL';
1495 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
1496 BEGIN
1497 --Estimated Occupancy Date
1498 IF p_estimated_occ_date >= p_lease_comm_date
1499 THEN
1500 x_return_status := fnd_api.g_ret_sts_success;
1501 ELSE
1502 fnd_message.set_name ('PN', 'PN_INV_ESTOCC_DATE');
1503 fnd_message.set_token ('ESTOCC_DATE', p_estimated_occ_date);
1504 fnd_message.set_token ('COMM_DATE', p_lease_comm_date);
1505 fnd_msg_pub.ADD;
1506 x_return_status := 'W';
1507 --RAISE fnd_api.g_exc_error;
1508 END IF;
1509
1510 --Actual Occupancy Date
1511 IF p_actual_occ_date >= p_lease_comm_date
1512 THEN
1513
1514 IF x_return_status = fnd_api.g_ret_sts_success
1515 THEN
1516 x_return_status := fnd_api.g_ret_sts_success;
1517 END IF;
1518 RAISE fnd_api.g_exc_error;
1519 ELSE
1520 fnd_message.set_name ('PN', 'PN_INV_ACTOCC_DATE');
1521 fnd_message.set_token ('ACTOCC_DATE', p_actual_occ_date);
1522 fnd_message.set_token ('COMM_DATE', p_lease_comm_date);
1523 fnd_msg_pub.ADD;
1524 x_return_status := 'W';
1525 END IF;
1526
1527 --Expiration Date
1528 IF p_tenancy_exp_date > p_lease_term_date
1529 THEN
1530 fnd_message.set_name ('PN', 'PN_INV_TENEXP_DATE');
1531 fnd_message.set_token ('TENEXP_DATE', p_tenancy_exp_date);
1532 fnd_message.set_token ('TERM_DATE', p_lease_term_date);
1533 fnd_msg_pub.ADD;
1534 x_return_status := 'W';
1535 ELSE
1536 IF x_return_status = fnd_api.g_ret_sts_success
1537 THEN
1538 x_return_status := fnd_api.g_ret_sts_success;
1539 END IF;
1540 END IF;
1541 EXCEPTION
1542 WHEN OTHERS
1543 THEN
1544 fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1545 fnd_message.set_token ('P_TEXT', l_api_name_full || '-' || SQLERRM);
1546 fnd_msg_pub.ADD;
1547 x_return_status := fnd_api.g_ret_sts_unexp_error;
1548 END VALIDATE_TENANCY_DATES;
1549 */
1550
1551 PROCEDURE VALIDATE_TENANCY_DATES (
1552 p_parameter_name IN VARCHAR2
1553 , p_operation IN VARCHAR2
1554 , p_estimated_occ_date IN DATE
1555 , p_actual_occ_date IN DATE
1556 , p_tenancy_exp_date IN DATE
1557 , p_lease_comm_date IN DATE
1558 , p_lease_term_date IN DATE
1559 , x_return_status OUT NOCOPY VARCHAR2
1560 )
1561 IS
1562 l_api_name CONSTANT VARCHAR2 (30) := 'TENANCYDATES_CROSSVAL';
1563 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
1564
1565 BEGIN
1566
1567
1568 IF ( ( p_actual_occ_date IS NOT NULL AND p_actual_occ_date <> pn_lease_utils.g_pn_miss_date)
1569 AND( p_estimated_occ_date IS NOT NULL AND p_estimated_occ_date <> pn_lease_utils.g_pn_miss_date)
1570 AND( p_tenancy_exp_date IS NOT NULL AND p_tenancy_exp_date <> pn_lease_utils.g_pn_miss_date)
1571 ) THEN
1572 IF ( ( p_estimated_occ_date < p_lease_comm_date AND p_actual_occ_date < p_lease_comm_date)
1573 OR ( p_tenancy_exp_date < p_lease_comm_date AND p_tenancy_exp_date > p_lease_term_date)
1574 OR ( p_estimated_occ_date < p_lease_comm_date)
1575 ) THEN
1576
1577
1578 /*fnd_message.set_name ('PN', 'PN_INV_ESTOCC_DATE');
1579 fnd_message.set_token ('ESTOCC_DATE', p_estimated_occ_date);
1580 fnd_message.set_token ('OCCP_DATE', p_actual_occ_date );
1581 fnd_message.set_token ('TRMN_DATE', p_tenancy_exp_date);
1582 fnd_msg_pub.ADD;*/
1583 x_return_status := fnd_api.g_ret_sts_error;
1584 END IF;
1585 ELSE
1586 /* IF ( ( p_estimated_occ_date < p_lease_comm_date)
1587 OR
1588 ( p_tenancy_exp_date < p_lease_comm_date AND p_tenancy_exp_date>p_lease_term_date)
1589 )
1590 THEN
1591
1592 /*fnd_message.set_name ('PN', 'PN_INV_ESTOCC_DATE');
1593 fnd_message.set_token ('ESTOCC_DATE', p_estimated_occ_date);
1594 fnd_message.set_token ('OCCP_DATE', p_actual_occ_date );
1595 fnd_message.set_token ('TRMN_DATE', p_tenancy_exp_date);
1596 fnd_msg_pub.ADD;
1597 END IF;*/
1598 x_return_status := fnd_api.g_ret_sts_error;
1599 END IF;
1600
1601 EXCEPTION
1602 WHEN OTHERS
1603 THEN
1604 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1605 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
1606 fnd_message.set_token ('P_TEXT', l_api_name_full
1607 || '-'
1608 || SQLERRM);
1609 fnd_msg_pub.ADD;*/
1610 x_return_status := fnd_api.g_ret_sts_error;
1611 END VALIDATE_TENANCY_DATES;
1612
1613 PROCEDURE VALIDATE_FIN_OBLIG_END_DATE (
1614 p_parameter_name IN VARCHAR2
1615 , p_operation IN VARCHAR2
1616 , p_fin_oblig_end_date IN DATE
1617 , p_tenancy_exp_date IN DATE
1618 , x_return_status OUT NOCOPY VARCHAR2
1619 )
1620 IS
1621 l_api_name CONSTANT VARCHAR2 (30) := 'TENANCYDATES_CROSSVAL';
1622 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
1623
1624 BEGIN
1625
1626
1627 IF ( p_fin_oblig_end_date IS NOT NULL
1628 AND p_fin_oblig_end_date <> pn_lease_utils.g_pn_miss_date
1629 )
1630
1631 THEN
1632 IF (p_tenancy_exp_date > p_fin_oblig_end_date) THEN
1633 /*fnd_message.set_name ('PN', 'PN_FIN_OBLIGATION_DATE');
1634 fnd_message.set_token ('FIN_OBLIGATION_DATE', p_fin_oblig_end_date);
1635 fnd_message.set_token ('EXPIRE_DATE', p_tenancy_exp_date);
1636 fnd_msg_pub.ADD;*/
1637 x_return_status := fnd_api.g_ret_sts_error;
1638 END IF;
1639 ELSE
1640 x_return_status := fnd_api.g_ret_sts_success;
1641 END IF;
1642
1643 EXCEPTION
1644 WHEN OTHERS
1645 THEN
1646 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1647 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
1648 fnd_message.set_token ('P_TEXT', l_api_name_full
1649 || '-'
1650 || SQLERRM);
1651 fnd_msg_pub.ADD;*/
1652 x_return_status := fnd_api.g_ret_sts_unexp_error;
1653 END VALIDATE_FIN_OBLIG_END_DATE;
1654
1655 -- GET TENANCY_CUSTOMER_ID
1656 PROCEDURE GET_TENANCY_CUST_ID (
1657 p_parameter_name IN VARCHAR2
1658 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
1659 , p_customer_name IN VARCHAR2
1660 , p_org_id IN NUMBER
1661 , x_customer_id IN OUT NOCOPY NUMBER
1662 , x_customer_site_use_id OUT NOCOPY NUMBER
1663 , x_return_status OUT NOCOPY VARCHAR2
1664 )
1665 IS
1666 l_api_name CONSTANT VARCHAR2 (30) := p_operation;
1667 l_api_name_full CONSTANT VARCHAR2 (61)
1668 := g_pkg_name
1669 || '.'
1670 || l_api_name;
1671
1672 CURSOR c_tenancy_customer_name
1673 IS
1674 SELECT hca.cust_account_id
1675 FROM hz_parties hp, hz_cust_accounts hca
1676 WHERE hp.party_id = hca.party_id
1677 AND hca.status = 'A'
1678 AND hp.party_name = p_customer_name;
1679
1680 CURSOR c_tenancy_customer_id
1681 IS
1682 SELECT hca.cust_account_id
1683 FROM hz_parties hp, hz_cust_accounts hca
1684 WHERE hp.party_id = hca.party_id
1685 AND hca.status = 'A'
1686 AND hca.cust_account_id = x_customer_id;
1687
1688 CURSOR c_customer_billtosite_use_id
1689 IS
1690
1691 SELECT hcsu.site_use_id customer_billtosite_use_id
1692 FROM fnd_lookups flo,
1693 hz_cust_site_uses_all hcsu,
1694 hz_cust_acct_sites_all hcas,
1695 hz_party_sites hps,
1696 hz_locations hlc,
1697 fnd_territories_tl ter
1698 WHERE hcas.cust_account_id = x_customer_id
1699 AND hcas.org_id = p_org_id
1700 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
1701 AND hcsu.org_id = hcas.org_id
1702 AND hcsu.status = 'A'
1703 AND flo.lookup_type = 'YES_NO'
1704 AND flo.lookup_code = hcsu.primary_flag
1705 AND hcsu.site_use_code = 'BILL_TO'
1706 AND hcas.party_site_id = hps.party_site_id
1707 AND hlc.location_id = hps.location_id
1708 AND hlc.country = ter.territory_code(+)
1709 AND ter.language(+) = USERENV('LANG')
1710 AND flo.meaning='Yes'
1711 ORDER BY hcsu.location;
1712 BEGIN
1713 -- Initialize the return status.
1714 x_return_status := fnd_api.g_ret_sts_success;
1715
1716 IF ( x_customer_id IS NULL
1717 OR x_customer_id = pn_lease_utils.g_pn_miss_num
1718 )
1719 THEN
1720 OPEN c_tenancy_customer_name;
1721
1722 FETCH c_tenancy_customer_name
1723 INTO x_customer_id;
1724
1725 IF (c_tenancy_customer_name%NOTFOUND)
1726 THEN
1727 /*fnd_message.set_name ('PN', 'PN_TENANCY_CUSTOMER');
1728 fnd_message.set_token ('CUSTOMER_NAME', p_customer_name);
1729 fnd_message.set_token ('CUSTOMER_ID', x_customer_id);
1730 fnd_msg_pub.ADD;*/
1731 x_return_status := fnd_api.g_ret_sts_error;
1732 ELSE
1733 x_return_status := fnd_api.g_ret_sts_success;
1734 END IF;
1735
1736 IF c_tenancy_customer_name%ISOPEN
1737 THEN
1738 CLOSE c_tenancy_customer_name;
1739 END IF;
1740 ELSE
1741 OPEN c_tenancy_customer_id;
1742
1743 FETCH c_tenancy_customer_id
1744 INTO x_customer_id;
1745
1746 IF (c_tenancy_customer_id%NOTFOUND)
1747 THEN
1748 /*fnd_message.set_name ('PN', 'PN_TENANCY_CUSTOMER');
1749 fnd_message.set_token ('CUSTOMER_NAME', p_customer_name);
1750 fnd_message.set_token ('CUSTOMER_ID', x_customer_id);
1751 fnd_msg_pub.ADD;*/
1752 x_return_status := fnd_api.g_ret_sts_error;
1753 ELSE
1754 x_return_status := fnd_api.g_ret_sts_success;
1755 END IF;
1756
1757 IF c_tenancy_customer_id%ISOPEN
1758 THEN
1759 CLOSE c_tenancy_customer_id;
1760 END IF;
1761 END IF;
1762
1763
1764 IF ( x_customer_id IS NOT NULL
1765 AND x_customer_id <> pn_lease_utils.g_pn_miss_num
1766 )
1767 THEN
1768
1769
1770 OPEN c_customer_billtosite_use_id;
1771
1772 FETCH c_customer_billtosite_use_id
1773 INTO x_customer_site_use_id;
1774
1775 IF (c_customer_billtosite_use_id%NOTFOUND)
1776 THEN
1777 /*fnd_message.set_name ('PN', 'PN_TENANCY_CUSTOMER_SITE');
1778 fnd_message.set_token ('CUSTOMER_NAME', p_customer_name);
1779 fnd_message.set_token ('CUSTOMER_ID', x_customer_id);
1780 fnd_msg_pub.ADD;*/
1781 x_return_status := fnd_api.g_ret_sts_error;
1782 ELSE
1783 x_return_status := fnd_api.g_ret_sts_success;
1784 END IF;
1785
1786 IF c_customer_billtosite_use_id%ISOPEN
1787 THEN
1788 CLOSE c_customer_billtosite_use_id;
1789 END IF;
1790
1791 END IF;
1792
1793
1794 EXCEPTION
1795 WHEN OTHERS
1796 THEN
1797 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1798 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
1799 fnd_message.set_token ('P_TEXT', l_api_name_full
1800 || '-'
1801 || SQLERRM);
1802 fnd_msg_pub.ADD;*/
1803
1804 IF c_tenancy_customer_name%ISOPEN
1805 THEN
1806 CLOSE c_tenancy_customer_name;
1807 ELSIF c_tenancy_customer_id%ISOPEN
1808 THEN
1809 CLOSE c_tenancy_customer_id;
1810 END IF;
1811
1812 IF c_customer_billtosite_use_id%ISOPEN
1813 THEN
1814 CLOSE c_customer_billtosite_use_id;
1815 END IF;
1816
1817 x_return_status := fnd_api.g_ret_sts_unexp_error;
1818
1819 END GET_TENANCY_CUST_ID;
1820
1821
1822 --Validate Customer
1823 PROCEDURE get_customer_id (
1824 p_parameter_name IN VARCHAR2
1825 , p_customer_name IN VARCHAR2
1826 , p_operation IN VARCHAR2 DEFAULT 'CREATE_LEASE'
1827 , x_return_status OUT NOCOPY VARCHAR2
1828 , x_customer_id IN OUT NOCOPY NUMBER
1829 )
1830 IS
1831 l_api_name CONSTANT VARCHAR2 (30)
1832 := 'validate_getcustomer';
1833 l_api_name_full CONSTANT VARCHAR2 (61)
1834 := g_pkg_name
1835 || '.'
1836 || l_api_name;
1837
1838 CURSOR cur_customer
1839 IS
1840 SELECT hca.cust_account_id
1841 FROM hz_parties hp
1842 , hz_cust_accounts hca
1843 WHERE hp.party_id = hca.party_id
1844 AND hca.status = 'A'
1845 AND party_name = p_customer_name;
1846
1847 CURSOR cur_customer_id
1848 IS
1849 SELECT hca.cust_account_id
1850 FROM hz_parties hp
1851 , hz_cust_accounts hca
1852 WHERE hp.party_id = hca.party_id
1853 AND hca.status = 'A'
1854 AND hca.cust_account_id = x_customer_id;
1855 BEGIN
1856 -- Initialize the return status.
1857 x_return_status := fnd_api.g_ret_sts_success;
1858 ------dbms_output.put_line ('UTILS:0');--Rupak
1859 IF (x_customer_id IS NULL or x_customer_id=PN_LEASE_UTILS.G_PN_MISS_NUM)
1860 THEN
1861 OPEN cur_customer;
1862
1863 FETCH cur_customer
1864 INTO x_customer_id;
1865 ------dbms_output.put_line ('UTILS:1');--Rupak
1866 IF (cur_customer%NOTFOUND)
1867 THEN
1868 /*fnd_message.set_name ('PN', 'PN_INVALID_CUSTOMER');
1869 fnd_message.set_token ('CUSTOMER_NAME', p_customer_name);
1870 fnd_message.set_token ('CUSTOMER_ID', x_customer_id);
1871 fnd_msg_pub.ADD;*/
1872 x_return_status := fnd_api.g_ret_sts_error;
1873 --RAISE fnd_api.g_exc_error;
1874 ELSE
1875 x_return_status := fnd_api.g_ret_sts_success;
1876 END IF;
1877
1878 IF cur_customer%ISOPEN
1879 THEN
1880 CLOSE cur_customer;
1881 END IF;
1882 ELSE
1883 OPEN cur_customer_id;
1884
1885 FETCH cur_customer_id
1886 INTO x_customer_id;
1887 ------dbms_output.put_line ('UTILS:2');--Rupak
1888 IF (cur_customer_id%NOTFOUND)
1889 THEN
1890 /*fnd_message.set_name ('PN', 'PN_INVALID_CUSTOMER');
1891 fnd_message.set_token ('CUSTOMER_NAME', p_customer_name);
1892 fnd_message.set_token ('CUSTOMER_ID', x_customer_id);
1893
1894 fnd_msg_pub.ADD;*/
1895 x_return_status := fnd_api.g_ret_sts_error;
1896 --RAISE fnd_api.g_exc_unexpected_error;
1897 ELSE
1898 x_return_status := fnd_api.g_ret_sts_success;
1899 END IF;
1900 ------dbms_output.put_line ('UTILS:3');--Rupak
1901 IF cur_customer_id%ISOPEN
1902 THEN
1903 CLOSE cur_customer_id;
1904 END IF;
1905 END IF;
1906 EXCEPTION
1907 WHEN OTHERS
1908 THEN
1909 x_return_status := fnd_api.g_ret_sts_error;
1910 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
1911 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
1912 fnd_message.set_token ('P_TEXT', l_api_name_full
1913 || '-'
1914 || SQLERRM);
1915 fnd_msg_pub.ADD;*/
1916 --RAISE fnd_api.g_exc_error;
1917 IF cur_customer%ISOPEN
1918 THEN
1919 CLOSE cur_customer;
1920 ELSIF cur_customer_id%ISOPEN
1921 THEN
1922 CLOSE cur_customer_id;
1923 END IF;
1924
1925 x_return_status := fnd_api.g_ret_sts_unexp_error;
1926 END get_customer_id;
1927
1928 PROCEDURE get_cal_start_date ( p_parameter_name IN VARCHAR2
1929 , x_cal_start IN OUT NOCOPY VARCHAR2
1930 , p_operation IN VARCHAR2
1931 , x_return_status OUT NOCOPY VARCHAR2
1932 )
1933 IS
1934
1935 l_dd_part VARCHAR2(2);
1936 l_mm_part VARCHAR2(2);
1937 l_api_name CONSTANT VARCHAR2 (30) := 'TENANCYDATES_CROSSVAL';
1938 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
1939 l_cal_start DATE := PN_LEASE_UTILS.G_PN_MISS_DATE;
1940 BEGIN
1941
1942 l_dd_part := SUBSTR(x_cal_start,1,2);
1943 l_mm_part := SUBSTR(x_cal_start,4,2);
1944
1945 IF rtrim(ltrim(translate(SUBSTR(x_cal_start,1,2),'0123456789',' '))) IS NOT NULL OR
1946 rtrim(ltrim(translate(SUBSTR(x_cal_start,4,2),'0123456789',' '))) IS NOT NULL OR
1947 SUBSTR(x_cal_start,3,1) <> '-'
1948 THEN
1949
1950 x_return_status := fnd_api.g_ret_sts_error;
1951
1952 ELSIF (to_number(l_dd_part) < 1 OR to_number(l_dd_part) > 31) OR
1953 (to_number(l_mm_part) < 1 OR to_number(l_mm_part) > 12)
1954 THEN
1955
1956 x_return_status := fnd_api.g_ret_sts_error;
1957
1958 END IF;
1959
1960 IF x_return_status <> 'S' THEN
1961 /* fnd_message.set_name ('PN', 'PN_CAL_INVALID_DATE');
1962 fnd_msg_pub.ADD;*/
1963 x_return_status := fnd_api.g_ret_sts_error;
1964
1965 END IF;
1966
1967 BEGIN
1968 SELECT to_date(l_dd_part || '-' || l_mm_part || '-' || '2007', 'DD-MM-YYYY')
1969 INTO l_cal_start
1970 FROM DUAL;
1971 x_return_status := fnd_api.g_ret_sts_success;
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 /*fnd_message.set_name ('PN', 'PN_CAL_INVALID_DATE');
1975 fnd_msg_pub.ADD;*/
1976 x_return_status := fnd_api.g_ret_sts_error;
1977
1978
1979 END;
1980 END get_cal_start_date;
1981 ---------------------------------Vivek Additions END ---------------------------
1982
1983
1984 PROCEDURE VALIDATE_COMPANY_ID (
1985 p_company_name IN VARCHAR2
1986 , x_company_id IN OUT NOCOPY NUMBER
1987 , x_return_status OUT NOCOPY VARCHAR2
1988 )
1989 IS
1990 BEGIN
1991 select company_id into x_company_id from PN_COMPANIES_ALL where upper(name) = upper(p_company_name) or company_id = x_company_id;
1992 x_return_status := fnd_api.g_ret_sts_success;
1993 EXCEPTION
1994 when NO_DATA_FOUND then
1995 x_return_status := fnd_api.g_ret_sts_error;
1996 when OTHERS then
1997 x_return_status := fnd_api.g_ret_sts_unexp_error;
1998 END ;
1999 --------------------------------Terms Begin----------------------
2000
2001 FUNCTION GET_LEASE_CONTEXT (p_lease_id IN NUMBER
2002 , p_org_id IN NUMBER
2003 )
2004 RETURN VARCHAR2
2005 IS
2006 l_lease_context VARCHAR2(20) := NULL;
2007 BEGIN
2008 SELECT change_type_lookup_code
2009 INTO l_lease_context
2010 FROM pn_lease_changes_all plc1
2011 WHERE lease_change_id = (select max(lease_change_id) from pn_lease_changes_all plc2 where lease_id = p_lease_id and org_id = p_org_id);
2012
2013 RETURN l_lease_context;
2014 EXCEPTION
2015 WHEN OTHERS THEN
2016 RETURN NULL;
2017 END GET_LEASE_CONTEXT;
2018
2019 FUNCTION GET_LEASE_CHANGE_ID (p_lease_id IN NUMBER
2020 , p_org_id IN NUMBER
2021 )
2022 RETURN NUMBER
2023 IS
2024 l_lease_change_id NUMBER;
2025 BEGIN
2026
2027 SELECT max(lease_change_id)
2028 INTO l_lease_change_id
2029 FROM pn_lease_changes_all
2030 WHERE lease_id = p_lease_id
2031 AND org_id = p_org_id;
2032
2033 RETURN l_lease_change_id;
2034 EXCEPTION
2035 WHEN OTHERS
2036 THEN
2037 RETURN NULL;
2038 END GET_LEASE_CHANGE_ID;
2039
2040 PROCEDURE VALIDATE_PAYMENT_DATES(p_parameter_name IN VARCHAR2
2041 , p_operation IN VARCHAR2
2042 , p_start_date IN DATE
2043 , p_end_date IN DATE
2044 , p_lease_comm_date IN DATE
2045 , p_lease_term_date IN DATE
2046 , x_return_status OUT NOCOPY VARCHAR2
2047 )
2048 IS
2049 l_api_name CONSTANT VARCHAR2 (30) := 'paymentdates_crossval';
2050 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
2051 BEGIN
2052 IF ( p_start_date > p_lease_comm_date
2053 OR p_start_date < p_lease_term_date)
2054 THEN
2055 x_return_status := fnd_api.g_ret_sts_success;
2056 ELSE
2057 /*fnd_message.set_name ('PN', 'PN_INVALID_PAYSTART_DATE');
2058 fnd_message.set_token ('START_DATE', p_start_date);
2059 fnd_message.set_token ('COMM_DATE', p_lease_comm_date);
2060 fnd_message.set_token ('TERM_DATE', p_lease_term_date);
2061 fnd_msg_pub.ADD;*/
2062 x_return_status := 'W';
2063 END IF;
2064
2065 --End Date
2066 IF ( p_end_date > p_lease_comm_date
2067 OR p_end_date < p_lease_term_date)
2068 THEN
2069 x_return_status := fnd_api.g_ret_sts_success;
2070 ELSE
2071 /*fnd_message.set_name ('PN', 'PN_INVALID_PAYEND_DATE');
2072 fnd_message.set_token ('END_DATE', p_end_date);
2073 fnd_message.set_token ('COMM_DATE', p_lease_comm_date);
2074 fnd_message.set_token ('TERM_DATE', p_lease_term_date);
2075 fnd_msg_pub.ADD;*/
2076 x_return_status := 'Y';
2077 END IF;
2078
2079 --Start date and end date validations
2080 IF p_end_date < p_start_date
2081 THEN
2082 /*fnd_message.set_name ('PN', 'PN_INVALID_PAYMENT_DATES');
2083 fnd_message.set_token ('START_DATE', p_start_date);
2084 fnd_message.set_token ('END_DATE', p_end_date);
2085 fnd_msg_pub.ADD;*/
2086 x_return_status := fnd_api.g_ret_sts_error;
2087 ELSE
2088 IF x_return_status = fnd_api.g_ret_sts_success
2089 THEN
2090 x_return_status := fnd_api.g_ret_sts_success;
2091 END IF;
2092 END IF;
2093 EXCEPTION
2094 WHEN OTHERS
2095 THEN
2096 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
2097 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
2098 fnd_message.set_token ('P_TEXT', l_api_name_full
2099 || '-'
2100 || SQLERRM);
2101 fnd_msg_pub.ADD;*/
2102 x_return_status := fnd_api.g_ret_sts_unexp_error;
2103
2104 END VALIDATE_PAYMENT_DATES;
2105
2106 PROCEDURE GET_VENDOR_ID (p_parameter_name IN VARCHAR2
2107 , p_vendor_name IN VARCHAR2
2108 , p_operation IN VARCHAR2 DEFAULT 'CREATE'
2109 , p_org_id IN NUMBER
2110 , x_return_status OUT NOCOPY VARCHAR2
2111 , x_vendor_id IN OUT NOCOPY NUMBER
2112 )
2113 IS
2114 l_api_name CONSTANT VARCHAR2 (30) := 'GET_VENDOR_ID';
2115 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
2116
2117 CURSOR c_vendor
2118 IS
2119 -- select * from pn_payment_terms_all where lease_id = 1057
2120 SELECT vendor_id
2121 FROM po_vendors
2122 WHERE enabled_flag = 'Y'
2123 AND TRUNC (SYSDATE) <= NVL (end_date_active, SYSDATE)
2124 AND vendor_name = p_vendor_name;
2125
2126 CURSOR c_vendor_id
2127 IS
2128 SELECT vendor_id
2129 FROM po_vendors
2130 WHERE enabled_flag = 'Y'
2131 AND TRUNC (SYSDATE) <= NVL (end_date_active, SYSDATE)
2132 AND vendor_id = x_vendor_id;
2133 BEGIN
2134 -- Initialize the return status.
2135 x_return_status := fnd_api.g_ret_sts_success;
2136
2137 IF x_vendor_id IS NULL
2138 THEN
2139 OPEN c_vendor;
2140
2141 FETCH c_vendor
2142 INTO x_vendor_id;
2143
2144 IF (c_vendor%NOTFOUND)
2145 THEN
2146 /*fnd_message.set_name ('PN', 'PN_INVALID_VENDOR');
2147 fnd_message.set_token ('VENDOR_NAME', p_vendor_name);
2148 fnd_message.set_token ('VENDOR_ID', x_vendor_id);
2149 fnd_msg_pub.ADD;*/
2150 x_return_status := fnd_api.g_ret_sts_error;
2151 ELSE
2152 x_return_status := fnd_api.g_ret_sts_success;
2153 END IF;
2154
2155 IF c_vendor%ISOPEN
2156 THEN
2157 CLOSE c_vendor;
2158 END IF;
2159 ELSE
2160 OPEN c_vendor_id;
2161
2162 FETCH c_vendor_id
2163 INTO x_vendor_id;
2164
2165 IF (c_vendor_id%NOTFOUND)
2166 THEN
2167 /*fnd_message.set_name ('PN', 'PN_INVALID_VENDOR');
2168 fnd_message.set_token ('VENDOR_NAME', p_vendor_name);
2169 fnd_message.set_token ('VENDOR_ID', x_vendor_id);
2170 fnd_msg_pub.ADD;*/
2171 x_return_status := fnd_api.g_ret_sts_error;
2172 ELSE
2173 x_return_status := fnd_api.g_ret_sts_success;
2174 END IF;
2175
2176 IF c_vendor_id%ISOPEN
2177 THEN
2178 CLOSE c_vendor_id;
2179 END IF;
2180 END IF;
2181 EXCEPTION
2182 WHEN OTHERS
2183 THEN
2184 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
2185 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
2186 fnd_message.set_token ('P_TEXT', l_api_name_full
2187 || '-'
2188 || SQLERRM);*/
2189 fnd_msg_pub.ADD;
2190 x_return_status := fnd_api.g_ret_sts_unexp_error;
2191
2192 IF c_vendor_id%ISOPEN
2193 THEN
2194 CLOSE c_vendor_id;
2195 ELSIF c_vendor%ISOPEN
2196 THEN
2197 CLOSE c_vendor;
2198 END IF;
2199 END GET_VENDOR_ID;
2200
2201 PROCEDURE GET_VENDOR_SITE_ID (
2202 p_parameter_name IN VARCHAR2
2203 , p_vendor_id IN NUMBER
2204 , p_vendor_site IN VARCHAR2
2205 , p_operation IN VARCHAR2 DEFAULT 'CREATE'
2206 , p_org_id IN NUMBER
2207 , x_return_status OUT NOCOPY VARCHAR2
2208 , x_vendor_site_id IN OUT NOCOPY NUMBER
2209 )
2210 IS
2211 l_api_name CONSTANT VARCHAR2 (30) := 'GET_VENDOR_SITE_ID';
2212 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
2213
2214 CURSOR c_vendor_site
2215 IS
2216 SELECT vendor_site_id
2217 FROM po_vendor_sites_all
2218 WHERE vendor_id = p_vendor_id
2219 AND pay_site_flag = 'Y'
2220 AND TRUNC (SYSDATE) <= NVL (inactive_date, SYSDATE)
2221 AND org_id = p_org_id
2222 AND vendor_site_code = p_vendor_site;
2223
2224 CURSOR c_vendor_site_id
2225 IS
2226 SELECT vendor_site_id
2227 FROM po_vendor_sites_all
2228 WHERE vendor_id = p_vendor_id
2229 AND pay_site_flag = 'Y'
2230 AND TRUNC (SYSDATE) <= NVL (inactive_date, SYSDATE)
2231 AND org_id = p_org_id
2232 AND vendor_site_id = x_vendor_site_id;
2233 BEGIN
2234 -- Initialize the return status.
2235 x_return_status := fnd_api.g_ret_sts_success;
2236
2237 IF x_vendor_site_id IS NULL
2238 THEN
2239 OPEN c_vendor_site;
2240
2241 FETCH c_vendor_site
2242 INTO x_vendor_site_id;
2243
2244 IF (c_vendor_site%NOTFOUND)
2245 THEN
2246 /*fnd_message.set_name ('PN', 'PN_INVALID_VENDOR_SITE');
2247 fnd_message.set_token ('VENDOR_SITE_NAME', p_vendor_site);
2248 fnd_message.set_token ('VENDOR_SITE_ID', x_vendor_site_id);
2249 fnd_msg_pub.ADD;*/
2250 x_return_status := fnd_api.g_ret_sts_error;
2251 ELSE
2252 x_return_status := fnd_api.g_ret_sts_success;
2253 END IF;
2254
2255 IF c_vendor_site%ISOPEN
2256 THEN
2257 CLOSE c_vendor_site;
2258 END IF;
2259 ELSE
2260 OPEN c_vendor_site_id;
2261
2262 FETCH c_vendor_site_id
2263 INTO x_vendor_site_id;
2264
2265 IF (c_vendor_site_id%NOTFOUND)
2266 THEN
2267 /*fnd_message.set_name ('PN', 'PN_INVALID_VENDOR_SITE');
2268 fnd_message.set_token ('VENDOR_SITE_NAME', p_vendor_site);
2269 fnd_message.set_token ('VENDOR_SITE_ID', x_vendor_site_id);
2270 fnd_msg_pub.ADD;*/
2271 x_return_status := fnd_api.g_ret_sts_error;
2272 ELSE
2273 x_return_status := fnd_api.g_ret_sts_success;
2274 END IF;
2275
2276 IF c_vendor_site_id%ISOPEN
2277 THEN
2278 CLOSE c_vendor_site_id;
2279 END IF;
2280 END IF;
2281 EXCEPTION
2282 WHEN OTHERS
2283 THEN
2284 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
2285 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
2286 fnd_message.set_token ('P_TEXT', l_api_name_full
2287 || '-'
2288 || SQLERRM);*/
2289 fnd_msg_pub.ADD;
2290 x_return_status := fnd_api.g_ret_sts_unexp_error;
2291
2292 IF c_vendor_site_id%ISOPEN
2293 THEN
2294 CLOSE c_vendor_site_id;
2295 ELSIF c_vendor_site%ISOPEN
2296 THEN
2297 CLOSE c_vendor_site;
2298 END IF;
2299 END GET_VENDOR_SITE_ID;
2300
2301 PROCEDURE GET_PAY_CUSTOMER_ID(p_parameter_name IN VARCHAR2
2302 , p_customer_name IN VARCHAR2
2303 , p_operation IN VARCHAR2 DEFAULT 'CREATE'
2304 , p_org_id IN NUMBER
2305 , x_return_status OUT NOCOPY VARCHAR2
2306 , x_customer_id IN OUT NOCOPY NUMBER
2307 )
2308 IS
2309 l_api_name CONSTANT VARCHAR2 (30) := 'GET_PAY_CUSTOMER_ID';
2310 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
2311
2312 CURSOR c_customer
2313 IS
2314 SELECT hca.cust_account_id
2315 FROM hz_parties hp, hz_cust_accounts hca
2316 WHERE hp.party_id = hca.party_id
2317 AND hca.status = 'A'
2318 AND hp.party_name = p_customer_name;
2319
2320 CURSOR c_customer_id
2321 IS
2322 SELECT hca.cust_account_id
2323 FROM hz_parties hp, hz_cust_accounts hca
2324 WHERE hp.party_id = hca.party_id
2325 AND hca.status = 'A'
2326 AND hca.cust_account_id = x_customer_id;
2327 BEGIN
2328 -- Initialize the return status.
2329 x_return_status := fnd_api.g_ret_sts_success;
2330
2331 IF x_customer_id IS NULL
2332 THEN
2333 OPEN c_customer;
2334
2335 FETCH c_customer
2336 INTO x_customer_id;
2337
2338 IF (c_customer%NOTFOUND)
2339 THEN
2340 /*fnd_message.set_name ('PN', 'PN_INVALID_CUSTOMER');
2341 fnd_message.set_token ('CUSTOMER_NAME', p_customer_name);
2342 fnd_message.set_token ('CUSTOMER_ID', x_customer_id);
2343 fnd_msg_pub.ADD;*/
2344 x_return_status := fnd_api.g_ret_sts_error;
2345 ELSE
2346 x_return_status := fnd_api.g_ret_sts_success;
2347 END IF;
2348
2349 IF c_customer%ISOPEN
2350 THEN
2351 CLOSE c_customer;
2352 END IF;
2353 ELSE
2354 OPEN c_customer_id;
2355
2356 FETCH c_customer_id
2357 INTO x_customer_id;
2358
2359 IF (c_customer_id%NOTFOUND)
2360 THEN
2361 /*fnd_message.set_name ('PN', 'PN_INVALID_CUSTOMER');
2362 fnd_message.set_token ('CUSTOMER_NAME', p_customer_name);
2363 fnd_message.set_token ('CUSTOMER_ID', x_customer_id);
2364 fnd_msg_pub.ADD;*/
2365 x_return_status := fnd_api.g_ret_sts_error;
2366 ELSE
2367 x_return_status := fnd_api.g_ret_sts_success;
2368 END IF;
2369
2370 IF c_customer_id%ISOPEN
2371 THEN
2372 CLOSE c_customer_id;
2373 END IF;
2374 END IF;
2375 EXCEPTION
2376 WHEN OTHERS
2377 THEN
2378 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
2379 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
2380 fnd_message.set_token ('P_TEXT', l_api_name_full
2381 || '-'
2382 || SQLERRM);*/
2383 fnd_msg_pub.ADD;
2384 x_return_status := fnd_api.g_ret_sts_unexp_error;
2385
2386 IF c_customer_id%ISOPEN
2387 THEN
2388 CLOSE c_customer_id;
2389 ELSIF c_customer%ISOPEN
2390 THEN
2391 CLOSE c_customer;
2392 END IF;
2393 END GET_PAY_CUSTOMER_ID;
2394
2395 PROCEDURE GET_PAY_CUSTOMER_SITE_ID (
2396 p_parameter_name IN VARCHAR2
2397 , p_customer_site IN VARCHAR2
2398 , p_customer_id IN NUMBER
2399 , p_operation IN VARCHAR2 DEFAULT 'CREATE'
2400 , p_org_id IN NUMBER
2401 , x_return_status OUT NOCOPY VARCHAR2
2402 , x_customer_site_id IN OUT NOCOPY NUMBER
2403 )
2404 IS
2405 l_api_name CONSTANT VARCHAR2 (30) := 'validate_getpaycustomersiteid';
2406 l_api_name_full CONSTANT VARCHAR2 (61) := g_pkg_name || '.' || l_api_name;
2407
2408 CURSOR c_customer_site
2409 IS
2410 SELECT hcsu.site_use_id
2411 FROM fnd_lookups flo, hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_party_sites hps, hz_locations hlc, fnd_territories_tl ter
2412 WHERE hcas.cust_account_id = p_customer_id
2413 AND hcas.org_id = p_org_id
2414 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
2415 AND hcsu.org_id = hcas.org_id
2416 AND hcsu.status = 'A'
2417 AND flo.lookup_type = 'YES_NO'
2418 AND flo.lookup_code = hcsu.primary_flag
2419 AND hcsu.site_use_code = 'BILL_TO'
2420 AND hcas.party_site_id = hps.party_site_id
2421 AND hlc.location_id = hps.location_id
2422 AND hlc.country = ter.territory_code(+)
2423 AND ter.LANGUAGE(+) = USERENV ('LANG')
2424 AND hcsu.LOCATION = p_customer_site;
2425
2426 CURSOR c_customer_site_id
2427 IS
2428 SELECT hcsu.site_use_id
2429 FROM fnd_lookups flo, hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_party_sites hps, hz_locations hlc, fnd_territories_tl ter
2430 WHERE hcas.cust_account_id = p_customer_id
2431 AND hcas.org_id = p_org_id
2432 AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
2433 AND hcsu.org_id = hcas.org_id
2434 AND hcsu.status = 'A'
2435 AND flo.lookup_type = 'YES_NO'
2436 AND flo.lookup_code = hcsu.primary_flag
2437 AND hcsu.site_use_code = 'BILL_TO'
2438 AND hcas.party_site_id = hps.party_site_id
2439 AND hlc.location_id = hps.location_id
2440 AND hlc.country = ter.territory_code(+)
2441 AND ter.LANGUAGE(+) = USERENV ('LANG')
2442 AND hcsu.site_use_id = x_customer_site_id;
2443 BEGIN
2444 -- Initialize the return status.
2445 x_return_status := fnd_api.g_ret_sts_success;
2446
2447 IF x_customer_site_id IS NULL
2448 THEN
2449 OPEN c_customer_site;
2450
2451 FETCH c_customer_site
2452 INTO x_customer_site_id;
2453
2454 IF (c_customer_site%NOTFOUND)
2455 THEN
2456 /* fnd_message.set_name ('PN', 'PN_INVALID_CUSTOMER_SITE');
2457 fnd_message.set_token ('CUSTOMER_SITE', p_customer_site);
2458 fnd_message.set_token ('CUSTOMER_SITE_ID',x_customer_site_id);
2459 fnd_msg_pub.ADD;*/
2460 x_return_status := fnd_api.g_ret_sts_error;
2461 ELSE
2462 x_return_status := fnd_api.g_ret_sts_success;
2463 END IF;
2464
2465 IF c_customer_site%ISOPEN
2466 THEN
2467 CLOSE c_customer_site;
2468 END IF;
2469 ELSE
2470 OPEN c_customer_site_id;
2471
2472 FETCH c_customer_site_id
2473 INTO x_customer_site_id;
2474
2475 IF (c_customer_site_id%NOTFOUND)
2476 THEN
2477 /*fnd_message.set_name ('PN', 'PN_INVALID_CUSTOMER_SITE');
2478 fnd_message.set_token ('CUSTOMER_SITE', p_customer_site);
2479 fnd_message.set_token ('CUSTOMER_SITE_ID',x_customer_site_id);
2480 fnd_msg_pub.ADD;*/
2481 x_return_status := fnd_api.g_ret_sts_error;
2482 ELSE
2483 x_return_status := fnd_api.g_ret_sts_success;
2484 END IF;
2485
2486 IF c_customer_site_id%ISOPEN
2487 THEN
2488 CLOSE c_customer_site_id;
2489 END IF;
2490 END IF;
2491 EXCEPTION
2492 WHEN OTHERS
2493 THEN
2494 /*fnd_message.set_name ('PN', 'PN_API_UNKNOWN_ERROR');
2495 fnd_message.set_token ('COLUMN', SUBSTR(p_parameter_name,3));
2496 fnd_message.set_token ('P_TEXT', l_api_name_full
2497 || '-'
2498 || SQLERRM);
2499 fnd_msg_pub.ADD;*/
2500 x_return_status := fnd_api.g_ret_sts_unexp_error;
2501
2502 IF c_customer_site_id%ISOPEN
2503 THEN
2504 CLOSE c_customer_site_id;
2505 ELSIF c_customer_site%ISOPEN
2506 THEN
2507 CLOSE c_customer_site;
2508 END IF;
2509 END GET_PAY_CUSTOMER_SITE_ID;
2510
2511
2512 --------------------------------Terms End-------------------------
2513 END pn_lease_utils;