1 PACKAGE BODY pn_index_lease_common_pkg AS
2 -- $Header: PNINCOMB.pls 120.6 2007/01/02 07:41:01 pseeram ship $
3
4 --============================================================================+
5 -- Copyright (c) 2001 Oracle Corporation
6 -- Redwood Shores, California, USA
7 -- All rights reserved.
8 -- ===========================================================================+
9 -- Name
10 -- pn_index_lease_common_pkgBKUP
11 --
12 -- Description
13 -- This package contains procedures used by some of index lease
14 -- feature of Property Manager.
15 --
16 --
17 -- History :
18 -- 09-APR-01 jreyes o Created
19 -- 10-AUG-01 psidhu o Added function GET_MAX_SCHEDULE_DATE
20 -- 14-AUG-01 psidhu o Added function GET_PROJECT_NAME
21 -- 24-AUG-01 psidhu o Added procedure GET_EXCLUDE_TERM
22 -- 12-SEP-01 psidhu o Added procedure GET_AP_ORGANIZATION_NAME
23 -- 13-DEC-01 Mrinal Misra o Added dbdrv command.
24 -- 15-JAN-02 Mrinal Misra o In dbdrv command changed phase=pls to phase=plb.
25 -- 07-MAR-02 achauhan o Changed the select statements in
26 -- chk_for_approved_index_periods
27 -- to make the queries more performant.
28 -- 11-MAR-02 Lakshmikanth o Fix for the GSCC issues.
29 -- Katputur Added the following line at the beginning
30 -- 12-AUG-02 Pooja Sidhu o Added parameter p_carry_forward_flag to
31 -- get_index_lease.
32 -- 29-Oct-02 Pooja Sidhu o Removed default null clause from parameters.
33 -- Fix for GSCC warning
34 -- "No default parameter values in package body".
35 -- 15-JUN-04 Anand o Added proc defn UPDATE_LOCATION_FOR_IR_TERMS.
36 -- 24-JUN-05 piagrawa o Overloaded get_ar_trx_type, get_ap_tax_details,
37 -- get_tax_group, get_po_number, get_distribution_set,
38 -- get_project_name for use with MOAC
39 -- 14-MAR-06 Hareesha o Bug #4756588 Removed procedure get_ap_tax_details,
40 -- get_ar_tax_code_name, get_tax_group
41 -- 24-MAR-06 Hareesha o Bug # 5116270 Added org_id parameter to
42 -- get_salesperson
43 -- 09-NOV-06 Prabhakar o Added parameter p_index_multiplier to get_index_lease
44 -- Added paramenter p_index_multiplier to get_index_period
45 -- 08-DEC-06 Prabhakar o Added parameters proration_rule,proration_period_start_date
46 -- to get_index_lease
47 --============================================================================+
48
49 -------------------------------------------------------------------------------
50 -- PROCDURE : chk_for_approved_index_periods
51 -- INVOKED FROM :
52 -- PURPOSE :
53 -- HISTORY :
54 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods,
55 -- pn_payment_terms with _ALL table.
56 -------------------------------------------------------------------------------
57 PROCEDURE chk_for_approved_index_periods
58 (
59 p_index_lease_id IN NUMBER
60 ,p_index_lease_period_id IN NUMBER
61 ,p_chk_index_ind IN VARCHAR2
62 ,p_msg IN OUT NOCOPY VARCHAR2
63 )
64 AS
65 v CHAR (1) := 0;
66 l_zero_period NUMBER := 0;
67
68 BEGIN
69 --
70 -- p_msg
71 -- - PN_APPROVED_PERIODS_NOT_FOUND - no index lease found
72 -- - PN_APPROVED_PERIODS_FOUND - no index lease period with this id was found
73 --
74
75 BEGIN
76 IF p_index_lease_period_id IS NULL
77 THEN
78 SELECT '1'
79 INTO v
80 FROM DUAL
81 WHERE EXISTS (
82 SELECT 1
83 FROM pn_index_lease_periods_all pilp
84 ,pn_payment_terms_all ppt
85 WHERE pilp.index_period_id = ppt.index_period_id
86 AND ppt.status = 'APPROVED'
87 AND pilp.index_lease_id = p_index_lease_id
88 AND ppt.index_period_id > l_zero_period
89 AND ( p_chk_index_ind = 'Y'
90 AND ppt.index_term_indicator IN
91 (
92 pn_index_amount_pkg.c_index_pay_term_type_recur
93 ,pn_index_amount_pkg.c_index_pay_term_type_backbill)
94 OR NVL (p_chk_index_ind, 'N') = 'N'));
95 ELSE
96 SELECT '1'
97 INTO v
98 FROM DUAL
99 WHERE EXISTS (
100 SELECT 1
101 FROM pn_payment_terms_all ppt
102 WHERE ppt.index_period_id = p_index_lease_period_id
103 AND ppt.status = 'APPROVED'
104 AND ( p_chk_index_ind = 'Y'
105 AND ppt.index_term_indicator IN
106 (
107 pn_index_amount_pkg.c_index_pay_term_type_recur
108 ,pn_index_amount_pkg.c_index_pay_term_type_backbill)
109 OR NVL (p_chk_index_ind, 'N') = 'N'));
110 END IF; --p_index_lease_period_id is null
111 EXCEPTION
112 WHEN NO_DATA_FOUND
113 THEN
114 v := '0';
115 p_msg := 'PN_APPROVED_PERIODS_NOT_FOUND';
116 END;
117
118 IF v = '1'
119 THEN
120 p_msg := 'PN_APPROVED_PERIODS_FOUND';
121 END IF;
122 END chk_for_approved_index_periods;
123
124 -------------------------------------------------------------------------------
125 -- PROCEDURE : chk_for_exported_items
126 -- DESCRIPTION : This procedure will check if an index rent period has
127 -- payment items that have been exported to ap or ar
128 -- PURPOSE :
129 -- HISTORY :
130 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_payment_terms, pn_payment_items
131 -- with _ALL table.
132 -------------------------------------------------------------------------------
133
134 PROCEDURE chk_for_exported_items
135 (
136 ip_index_period_id IN NUMBER
137 ,op_msg OUT NOCOPY VARCHAR2
138 )
139 AS
140 v CHAR (1) := 0;
141 BEGIN
142 --
143 -- p_msg
144 -- - PN_EXPORTED_ITEM_NOT_FOUND - no index lease found
145 -- - PN_EXPORTED_ITEM_FOUND - no index lease period with this id was found
146 --
147
148 BEGIN
149 SELECT '1'
150 INTO v
151 FROM DUAL
152 WHERE EXISTS ( SELECT 1
153 FROM pn_payment_items_all ppi, pn_payment_terms_all ppt
154 WHERE ppt.payment_term_id = ppi.payment_term_id
155 AND ppi.payment_item_type_lookup_code = 'CASH'
156 AND ( ppi.transferred_to_ap_flag = 'Y'
157 OR ppi.transferred_to_ar_flag = 'Y')
158 AND ppt.index_period_id = ip_index_period_id);
159 EXCEPTION
160 WHEN NO_DATA_FOUND
161 THEN
162 v := '0';
163 op_msg := 'PN_EXPORTED_ITEM_NOT_FOUND';
164 END;
165
166 IF v = '1'
167 THEN
168 op_msg := 'PN_EXPORTED_ITEM_FOUND';
169 END IF;
170 END chk_for_exported_items;
171
172
173
174 -------------------------------------------------------------------------------
175 -- PROCDURE : chk_for_payment_reqd_fields
176 -- INVOKED FROM :
177 -- PURPOSE :
178 -- HISTORY :
179 -- 10-Jul-01 psidhu o Bug # 1875457 - Commented check for code_combination_id
180 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_leases, pn_payment_terms with
181 -- _ALL table.
182 -------------------------------------------------------------------------------
183 PROCEDURE chk_for_payment_reqd_fields (
184 p_payment_term_id IN NUMBER
185 ,p_msg OUT NOCOPY VARCHAR2) IS
186 CURSOR curr_payment_term (
187 ip_payment_term_id IN NUMBER) IS
188 SELECT pl.lease_id
189 ,pl.lease_num
190 ,pl.lease_class_code
191 ,ppt.payment_term_id
192 ,ppt.payment_purpose_code
193 ,ppt.payment_term_type_code
194 ,ppt.frequency_code
195 ,ppt.start_date
196 ,ppt.end_date
197 ,ppt.schedule_day
198 ,ppt.vendor_id
199 ,ppt.vendor_site_id
200 ,ppt.customer_id
201 ,ppt.customer_site_use_id
202 ,ppt.code_combination_id
203 FROM pn_leases_all pl, pn_payment_terms_all ppt
204 WHERE pl.lease_id = ppt.lease_id
205 AND ppt.payment_term_id = ip_payment_term_id;
206
207 rec_payment_term curr_payment_term%ROWTYPE;
208 BEGIN
209 --
210 -- getting payment termdetails
211 --
212 OPEN curr_payment_term (p_payment_term_id);
213 FETCH curr_payment_term INTO rec_payment_term;
214
215 --
216 -- if payment term is found...
217 --
218 IF curr_payment_term%FOUND THEN
219 --
220 -- checking if any of required fields for a direct lease is missing
221 -- if any field is missing, returning message
222 --
223
224 IF (rec_payment_term.lease_class_code = pn_index_amount_pkg.c_lease_class_direct) THEN
225 IF ( rec_payment_term.payment_term_id IS NULL
226 OR rec_payment_term.payment_purpose_code IS NULL
227 OR rec_payment_term.payment_term_type_code IS NULL
228 OR rec_payment_term.frequency_code IS NULL
229 OR rec_payment_term.start_date IS NULL
230 OR rec_payment_term.end_date IS NULL
231 OR rec_payment_term.schedule_day IS NULL
232 OR rec_payment_term.vendor_id IS NULL
233 OR rec_payment_term.vendor_site_id IS NULL
234 -- OR rec_payment_term.code_combination_id IS NULL
235 )
236 THEN
237 p_msg := 'PN_REQD_FLDS_PAY_DIRECT';
238 END IF;
239 -- if any field is missing, returning message
240 --
241 -- checking if any of required fields for a 3rd class lease is missing
242 -- if any field is missing, returning message
243 --
244 ELSIF ( rec_payment_term.payment_term_id IS NULL
245 OR rec_payment_term.payment_purpose_code IS NULL
246 OR rec_payment_term.payment_term_type_code IS NULL
247 OR rec_payment_term.frequency_code IS NULL
248 OR rec_payment_term.start_date IS NULL
249 OR rec_payment_term.end_date IS NULL
250 OR rec_payment_term.schedule_day IS NULL
251 OR rec_payment_term.customer_id IS NULL
252 OR rec_payment_term.customer_site_use_id IS NULL
253 -- OR rec_payment_term.code_combination_id IS NULL
254 )
255 THEN
256 p_msg := 'PN_REQD_FLDS_PAY_3CLASS';
257 END IF;
258 END IF; --curr_payment_term%FOUND
259
260 CLOSE curr_payment_term;
261 END chk_for_payment_reqd_fields;
262
263
264
265 ------------------------------------------------------------------------
266 -- PROCEDURE : put_log
267 ------------------------------------------------------------------------
268 PROCEDURE put_log (
269 p_string VARCHAR2) IS
270 BEGIN
271 fnd_file.put_line (fnd_file.LOG, p_string);
272 END put_log;
273
274
275 ------------------------------------------------------------------------
276 -- PROCEDURE : put_OUTPUT
277 ------------------------------------------------------------------------
278 PROCEDURE put_output (
279 p_string VARCHAR2) IS
280 BEGIN
281 fnd_file.put_line (fnd_file.LOG, p_string);
282 fnd_file.put_line (fnd_file.output, p_string);
283 END put_output;
284
285
286 /*===========================================================================+
287 | PROCEDURE
288 | Find_Base_Index
289 |
290 | DESCRIPTION
291 | Finds the base Index value for an Index and Base year
292 |
293 | ARGUMENTS: index_id, base-year
294 |
295 | NOTES:
296 | Called at all Debug points spread across this file
297 |
298 | USED BY:
299 | PNTINDEX
300 |
301 | MODIFICATION HISTORY
302 |
303 +===========================================================================*/
304
305
306 FUNCTION find_base_index (
307 p_index_id IN NUMBER
308 ,p_base_year IN DATE )
309 RETURN NUMBER IS
310 v_base_index NUMBER := NULL;
311 BEGIN
312 BEGIN
313 SELECT lines.index_figure
314 INTO v_base_index
315 FROM pn_index_history_lines lines, pn_index_history_headers headers
316 WHERE lines.index_id = headers.index_id
317 AND TO_CHAR (lines.index_date, 'MON-RRRR') = TO_CHAR (p_base_year, 'MON-RRRR')
318 AND lines.index_id = p_index_id;
319 EXCEPTION
320 WHEN NO_DATA_FOUND
321 THEN
322 v_base_index := NULL;
323 END;
324
325 RETURN v_base_index;
326 END find_base_index;
327
328 -------------------------------------------------------------------------------
329 -- PROCDURE : get_index_lease
330 -- INVOKED FROM :
331 -- PURPOSE : Procedure to get Index Lease details
332 -- HISTORY :
333 -- 26-NOV-03 DThota o bug 3271061 - Added parameters p_aggregation_flag,
334 -- p_index_finder_months to get_index_lease.
335 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_leases with _ALL table.
336 -- 09-NOV-06 Prabhakar o Added p_index_multiplier parameter.
337 -- 08-DEC-06 Prabhakar o Added proration_rule,proratio_period_start_date
338 -- to get_index_lease
339 -------------------------------------------------------------------------------
340 PROCEDURE get_index_lease
341 (
342 p_index_lease_id IN NUMBER
343 ,p_commencement_date OUT NOCOPY DATE
344 ,p_termination_date OUT NOCOPY DATE
345 ,p_assessment_date OUT NOCOPY DATE
346 ,p_assessment_interval OUT NOCOPY NUMBER
347 ,p_relationship_default OUT NOCOPY VARCHAR2
348 ,p_spread_frequency OUT NOCOPY VARCHAR2
349 ,p_basis_percent_default OUT NOCOPY NUMBER
350 ,p_intial_basis OUT NOCOPY NUMBER
351 ,p_base_index OUT NOCOPY NUMBER
352 ,p_index_finder_method OUT NOCOPY VARCHAR2
353 ,p_negative_rent_type OUT NOCOPY VARCHAR2
354 ,p_increase_on OUT NOCOPY VARCHAR2
355 ,p_basis_type OUT NOCOPY VARCHAR2
356 ,p_reference_period OUT NOCOPY VARCHAR2
357 ,p_base_year OUT NOCOPY DATE
358 ,p_rounding_flag OUT NOCOPY VARCHAR2
359 ,p_gross_flag OUT NOCOPY VARCHAR2
360 ,p_carry_forward_flag OUT NOCOPY VARCHAR2
361 ,p_aggregation_flag OUT NOCOPY VARCHAR2
362 ,p_index_finder_months OUT NOCOPY NUMBER
363 ,p_index_multiplier OUT NOCOPY NUMBER
364 ,p_proration_rule OUT NOCOPY VARCHAR2
365 ,p_proration_period_start_date OUT NOCOPY DATE
366 )
367 AS
368
369 v CHAR (1) := 0;
370 CURSOR c IS
371 SELECT *
372 FROM pn_index_leases_all
373 WHERE index_lease_id = p_index_lease_id;
374 BEGIN
375 FOR c_rec IN c
376 LOOP
377 p_commencement_date := c_rec.commencement_date;
378 p_termination_date := c_rec.termination_date;
379 p_assessment_date := c_rec.assessment_date;
380 p_assessment_interval := c_rec.assessment_interval;
381 p_relationship_default := c_rec.relationship_default;
382 p_spread_frequency := c_rec.spread_frequency;
383 p_basis_percent_default := c_rec.basis_percent_default;
384 p_intial_basis := c_rec.initial_basis;
385 p_base_index := c_rec.base_index;
386 p_index_finder_method := c_rec.index_finder_method;
387 p_negative_rent_type := c_rec.negative_rent_type;
388 p_increase_on := c_rec.increase_on;
389 p_basis_type := c_rec.basis_type;
390 p_reference_period := c_rec.reference_period;
391 p_base_year := c_rec.base_year;
392 p_rounding_flag := c_rec.rounding_flag;
393 p_gross_flag := c_rec.gross_flag;
394 p_carry_forward_flag := c_rec.carry_forward_flag;
395 p_aggregation_flag := c_rec.aggregation_flag;
396 p_index_finder_months := c_rec.index_finder_months;
397 p_index_multiplier := nvl (c_rec.index_multiplier, 1);
398 p_proration_rule := nvl (c_rec.proration_rule, 'NO_PRORATION');
399 p_proration_period_start_date := c_rec.proration_period_start_date;
400 END LOOP;
401 END get_index_lease;
402
403 -------------------------------------------------------------------------------
404 -- PROCDURE : get_index_period
405 -- INVOKED FROM :
406 -- PURPOSE : Procedure to get Index Period details
407 -- HISTORY :
408 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with _ALL
409 -- table.
410 -- 09-NOV-06 Prabhakar o Added index_multiplier
411 -------------------------------------------------------------------------------
412 PROCEDURE get_index_period
413 (
414 p_index_period_id IN NUMBER
415 ,p_basis_start_date OUT NOCOPY DATE
416 ,p_basis_end_date OUT NOCOPY DATE
417 ,p_index_finder_date OUT NOCOPY DATE
418 ,p_current_basis OUT NOCOPY NUMBER
419 ,p_relationship OUT NOCOPY VARCHAR2
420 ,p_index_percent_change OUT NOCOPY NUMBER
421 ,p_basis_percent_change OUT NOCOPY NUMBER
422 ,p_index_multiplier OUT NOCOPY NUMBER
423 )
424 AS
425 v CHAR (1) := 0;
426
427 CURSOR c IS
428 SELECT *
429 FROM pn_index_lease_periods_all
430 WHERE index_period_id = p_index_period_id;
431 BEGIN
432 FOR c_rec IN c
433 LOOP
434 p_basis_start_date := c_rec.basis_start_date;
435 p_basis_end_date := c_rec.basis_end_date;
436 p_index_finder_date := c_rec.index_finder_date;
437 p_current_basis := c_rec.current_basis;
438 p_relationship := c_rec.relationship;
439 p_index_percent_change := c_rec.index_percent_change;
440 p_basis_percent_change := c_rec.basis_percent_change;
441 p_index_multiplier := nvl (c_rec.index_multiplier, 1);
442 END LOOP;
443 END get_index_period;
444
445
446
447
448 -------------------------------------------------------------------------------
449 -- PROCDURE : get_index_start_end_dates
450 -- INVOKED FROM :
451 -- PURPOSE :
452 -- HISTORY :
453 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_lease_details, pn_leases,
454 -- pn_index_lease_periods, pn_index_leases with _ALL table.
455 -------------------------------------------------------------------------------
456 PROCEDURE get_index_start_end_dates
457 (
458 p_index_period_id IN NUMBER
459 ,p_commencement_date OUT NOCOPY DATE
460 ,p_termination_date OUT NOCOPY DATE
461 )
462 AS
463 CURSOR c IS
464 SELECT index_leases.commencement_date, lease_det.lease_termination_date
465 FROM pn_index_lease_periods_all periods, pn_index_leases_all index_leases,
466 pn_leases_all leases, pn_lease_details_all lease_det
467 WHERE periods.index_period_id = p_index_period_id
468 AND index_leases.index_lease_id = periods.index_lease_id
469 AND index_leases.lease_id = leases.lease_id
470 AND leases.lease_id = lease_det.lease_id
471 AND rownum = 1;
472 BEGIN
473 FOR c_rec IN c
474 LOOP
475 p_commencement_date := c_rec.commencement_date;
476 p_termination_date := c_rec.lease_termination_date;
477 END LOOP;
478 END get_index_start_end_dates;
479
480
481
482 /*===========================================================================+
483 | PROCEDURE
484 | get_index_details
485 |
486 | DESCRIPTION
487 | Procedure to get Index Type details
488 |
489 | ARGUMENTS:
490 |
491 | NOTES:
492 |
493 | MODIFICATION HISTORY
494 |
495 |
496 +===========================================================================*/
497 PROCEDURE get_index_details (
498 p_index_line_id IN NUMBER
499 ,p_index_date OUT NOCOPY DATE
500 ,p_index_figure OUT NOCOPY NUMBER) AS
501 v CHAR (1) := 0;
502
503 CURSOR c IS
504 SELECT *
505 FROM pn_index_history_lines
506 WHERE index_line_id = p_index_line_id;
507 BEGIN
508 FOR c_rec IN c
509 LOOP
510 p_index_date := c_rec.index_date;
511 p_index_figure := c_rec.index_figure;
512 END LOOP;
513 END get_index_details;
514
515
516 ------------------------------------------------------------------------
517 -- PROCEDURE : GET_AR_PAYMENT_TERM
518 -- HISTORY:
519 -- 28-NOV-05 pikhar o Changed ra_terms_v to ra_terms
520 ------------------------------------------------------------------------
521
522 FUNCTION get_ar_payment_term (
523 p_term_id NUMBER)
524 RETURN VARCHAR2 IS
525 l_name VARCHAR2 (15);
526 BEGIN
527 SELECT name
528 INTO l_name
529 FROM ra_terms
530 WHERE term_id = p_term_id;
531 RETURN l_name;
532 EXCEPTION
533 WHEN OTHERS
534 THEN
535 RETURN NULL;
536 END get_ar_payment_term;
537
538
539 ------------------------------------------------------------------------
540 -- PROCEDURE : GET_AP_PAYMENT_TERM
541 -- HISTORY:
542 -- 28-NOV-05 pikhar o Changed ap_terms_v to ap_terms
543 ------------------------------------------------------------------------
544
545 FUNCTION get_ap_payment_term (
546 p_term_id NUMBER)
547 RETURN VARCHAR2 IS
548 l_name VARCHAR2 (50);
549 BEGIN
550 SELECT name
551 INTO l_name
552 FROM ap_terms
553 WHERE term_id = p_term_id;
554 RETURN l_name;
555 EXCEPTION
556 WHEN OTHERS
557 THEN
558 RETURN NULL;
559 END get_ap_payment_term;
560
561
562 --------------------------------------------------------------------------------
563 -- PROCEDURE : GET_AR_TRX_TYPE
564 -- HISTORY
565 -- 28-NOV-05 pikhar o Replaced ra_cust_trx_types with _ALL table.
566 -- REDUNDANT FUNCTION - DO NOT USE!!
567 --------------------------------------------------------------------------------
568
569 FUNCTION get_ar_trx_type (
570 p_cust_trx_type_id NUMBER)
571 RETURN VARCHAR2 IS
572 l_name VARCHAR2 (20);
573 BEGIN
574 SELECT name
575 INTO l_name
576 FROM ra_cust_trx_types
577 WHERE cust_trx_type_id = p_cust_trx_type_id;
578 RETURN l_name;
579 EXCEPTION
580 WHEN OTHERS
581 THEN
582 RETURN NULL;
583 END get_ar_trx_type;
584
585
586 -------------------------------------------------------------------------------
587 -- PROCDURE : get_index_period_details
588 -- INVOKED FROM :
589 -- PURPOSE :
590 -- HISTORY :
591 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with _ALL
592 -- table.
593 -------------------------------------------------------------------------------
594
595 FUNCTION get_index_period_details (
596 p_index_period_id NUMBER)
597 RETURN index_lease_periods_rec
598 IS
599 l_index_lease_periods_rec index_lease_periods_rec;
600 BEGIN
601 SELECT current_basis
602 ,constraint_rent_due
603 ,unconstraint_rent_due
604 ,index_percent_change
605 ,current_index_line_id
606 ,current_index_line_value
607 ,previous_index_line_id
608 ,previous_index_line_value
609 INTO l_index_lease_periods_rec
610 FROM pn_index_lease_periods_all
611 WHERE index_period_id = p_index_period_id;
612 RETURN l_index_lease_periods_rec;
613 EXCEPTION
614 WHEN OTHERS
615 THEN
616 RETURN NULL;
617 END get_index_period_details;
618
619
620 -------------------------------------------------------------------------------
621 -- PROCDURE : get_lease_change_id
622 -- INVOKED FROM :
623 -- PURPOSE :
624 -- HISTORY :
625 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_lease_changes with _ALL table.
626 -------------------------------------------------------------------------------
627
628 FUNCTION get_lease_change_id (
629 p_lease_id NUMBER)
630 RETURN NUMBER
631 IS
632 l_lease_change_id NUMBER;
633 BEGIN
634 SELECT lease_change_id
635 INTO l_lease_change_id
636 FROM pn_lease_changes_all
637 WHERE lease_change_number IS NULL
638 AND lease_id = p_lease_id;
639 RETURN l_lease_change_id;
640 EXCEPTION
641 WHEN OTHERS
642 THEN
643 RETURN NULL;
644 END get_lease_change_id;
645
646 -------------------------------------------------------------------------------
647 -- PROCDURE : delete_index_payment_term
648 -- PURPOSE : This procedure will delete index payment terms and its associated
649 -- records in the intersection table for an index period
650 -- ARGUMENTS : IN : index_period_id - index rent period id (MANDATORY)
651 -- payment_period_id - payment period id (OPTIONAL);
652 -- HISTORY :
653 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_payment_terms with _ALL table.
654 -------------------------------------------------------------------------------
655
656 PROCEDURE delete_index_payment_term (
657 p_index_period_id IN NUMBER
658 ,p_payment_term_id IN NUMBER
659 ,p_msg OUT NOCOPY VARCHAR2) IS
660 l_tax_data_rec tax_data_rec;
661 BEGIN
662 DELETE FROM pn_payment_terms_all
663 WHERE ( payment_term_id = p_payment_term_id
664 OR p_payment_term_id IS NULL)
665 AND index_period_id = p_index_period_id;
666
667 -- IF SQL%NOTFOUND
668 -- THEN
669 -- --DBMS_OUTPUT.put_line ('NO ROWS DELETED');
670 -- ELSE
671 -- --DBMS_OUTPUT.put_line ('ROWS DELETED FROM pn_payment_terms');
672 -- END IF;
673
674
675 -- IF SQL%NOTFOUND
676 -- THEN
677 -- --DBMS_OUTPUT.put_line ('NO ROWS DELETED');
678 -- ELSE
679 -- --DBMS_OUTPUT.put_line ('ROWS DELETED FROM pn_index_period_terms');
680 -- END IF;
681 END delete_index_payment_term;
682
683
684 -------------------------------------------------------------------------------
685 -- PROCDURE : find_if_period_exists
686 -- INVOKED FROM :
687 -- PURPOSE :
688 -- HISTORY :
689 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with _ALL
690 -- table.
691 -------------------------------------------------------------------------------
692 FUNCTION find_if_period_exists (
693 p_index_lease_id NUMBER)
694 RETURN NUMBER
695 IS
696 l_period_exists NUMBER;
697 BEGIN
698 SELECT 1
699 INTO l_period_exists
700 FROM DUAL
701 WHERE EXISTS ( SELECT periods.index_period_id
702 FROM pn_index_lease_periods_all periods
703 WHERE periods.index_lease_id = p_index_lease_id);
704 RETURN l_period_exists;
705 EXCEPTION
706 WHEN OTHERS
707 THEN
708 RETURN NULL;
709 END find_if_period_exists;
710
711
712 -------------------------------------------------------------------------------
713 -- PROCDURE : find_if_term_exists
714 -- INVOKED FROM :
715 -- PURPOSE :
716 -- HISTORY :
717 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_payment_terms with _ALL table.
718 -------------------------------------------------------------------------------
719 FUNCTION find_if_term_exists (
720 p_index_period_id NUMBER)
721 RETURN NUMBER
722 IS
723 l_term_exists NUMBER;
724 BEGIN
725 SELECT 1
726 INTO l_term_exists
727 FROM DUAL
728 WHERE EXISTS ( SELECT ppt.payment_term_id
729 FROM pn_payment_terms_all ppt
730 WHERE ppt.index_period_id = p_index_period_id);
731 RETURN l_term_exists;
732 EXCEPTION
733 WHEN OTHERS
734 THEN
735 RETURN NULL;
736 END find_if_term_exists;
737
738
739 -------------------------------------------------------------------------------
740 -- PROCDURE : find_if_norm_term_exists
741 -- INVOKED FROM :
742 -- PURPOSE :
743 -- HISTORY :
744 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_payment_terms with _ALL table.
745 -------------------------------------------------------------------------------
746 FUNCTION find_if_norm_term_exists (
747 p_index_period_id NUMBER)
748 RETURN NUMBER
749 IS
750 l_term_exists NUMBER;
751 BEGIN
752 SELECT 1
753 INTO l_term_exists
754 FROM DUAL
755 WHERE EXISTS ( SELECT ppt.payment_term_id
756 FROM pn_payment_terms_all ppt
757 WHERE ppt.index_period_id = p_index_period_id
758 AND ppt.status = 'APPROVED');
759 RETURN l_term_exists;
760 EXCEPTION
761 WHEN OTHERS
762 THEN
763 RETURN NULL;
764 END find_if_norm_term_exists;
765
766
767 -------------------------------------------------------------------------------
768 -- PROCDURE : find_if_template_used
769 -- INVOKED FROM :
770 -- PURPOSE :
771 -- HISTORY :
772 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_leases, pn_term_templates
773 -- with _ALL table.
774 -------------------------------------------------------------------------------
775 FUNCTION find_if_template_used (
776 p_term_template_id NUMBER)
777 RETURN NUMBER
778 IS
779 l_template_used NUMBER;
780 BEGIN
781 SELECT 1
782 INTO l_template_used
783 FROM DUAL
784 WHERE EXISTS ( SELECT pil.index_lease_id
785 FROM pn_index_leases_all pil, pn_term_templates_all ptt
786 WHERE ptt.term_template_id = pil.term_template_id
787 AND ptt.term_template_id = p_term_template_id);
788 RETURN l_template_used;
789 EXCEPTION
790 WHEN OTHERS
791 THEN
792 RETURN NULL;
793 END find_if_template_used;
794
795
796 -------------------------------------------------------------------------------
797 -- PROCDURE : get_term_status
798 -- INVOKED FROM :
799 -- PURPOSE :
800 -- HISTORY :
801 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_payment_terms with _ALL table.
802 -------------------------------------------------------------------------------
803 FUNCTION get_term_status (
804 p_payment_term_id NUMBER)
805 RETURN VARCHAR2
806 IS
807 l_term_status VARCHAR2 (30);
808 BEGIN
809 SELECT status
810 INTO l_term_status
811 FROM pn_payment_terms_all
812 WHERE payment_term_id = p_payment_term_id;
813 RETURN l_term_status;
814 EXCEPTION
815 WHEN OTHERS
816 THEN
817 RETURN NULL;
818 END get_term_status;
819
820 ----------------------------------------------------------------------
821 -- PROCEDURE : GET PO NUMBER
822 -- HISTORY:
823 -- 28-NOV-05 pikhar o Replaced po_headers with _all Table
824 ------------------------------------------------------------------------
825
826 FUNCTION get_po_number (
827 p_po_header_id NUMBER)
828 RETURN VARCHAR2
829 IS
830 l_po_number VARCHAR2 (30);
831 BEGIN
832 SELECT segment1
833 INTO l_po_number
834 FROM po_headers_all
835 WHERE po_header_id = p_po_header_id;
836 RETURN l_po_number;
837 EXCEPTION
838 WHEN OTHERS
839 THEN
840 RETURN NULL;
841 END get_po_number;
842
843
844 ------------------------------------------------------------------------
845 -- PROCEDURE : GET RECEIPT METHOD
846 ------------------------------------------------------------------------
847
848 FUNCTION get_receipt_method (
849 p_receipt_method_id NUMBER)
850 RETURN VARCHAR2 IS
851 l_receipt_method VARCHAR2 (30);
852 BEGIN
853 SELECT name
854 INTO l_receipt_method
855 FROM ar_receipt_methods
856 WHERE receipt_method_id = p_receipt_method_id;
857 RETURN l_receipt_method;
858 EXCEPTION
859 WHEN OTHERS
860 THEN
861 RETURN NULL;
862 END get_receipt_method;
863
864
865 ------------------------------------------------------------------------
866 -- PROCEDURE : GET LOCATION CODE
867 -- 30-oct-2002 - date effectivity change
868 ------------------------------------------------------------------------
869
870 FUNCTION get_location (
871 p_location_id NUMBER)
872 RETURN VARCHAR2 IS
873 l_location_code VARCHAR2 (90);
874 BEGIN
875 SELECT location_code
876 INTO l_location_code
877 FROM pn_locations_all
878 WHERE location_id = p_location_id
879 AND ROWNUM < 2;
880 RETURN l_location_code ;
881 EXCEPTION
882 WHEN OTHERS
883 THEN
884 RETURN NULL;
885 END get_location;
886
887
888 ------------------------------------------------------------------------
889 -- PROCEDURE : lookup_index_history
890 -- DESCRIPTION: This procedure will derive the cpi value and index history id using
891 -- finder date provided.
892 --
893 ------------------------------------------------------------------------
894
895 PROCEDURE lookup_index_history (
896 p_index_history_id IN NUMBER
897 ,p_index_finder_date IN DATE
898 ,op_cpi_value OUT NOCOPY NUMBER
899 ,op_cpi_id OUT NOCOPY NUMBER
900 ,op_msg OUT NOCOPY VARCHAR2) IS
901 v_index_line_id pn_index_history_lines.index_line_id%TYPE;
902 v_index_figure pn_index_history_lines.index_figure%TYPE;
903 BEGIN
904 --put_log ('..In lookup_index_history');
905 --
906 -- Do a lookup on index history using finder date
907 --
908 SELECT phl.index_line_id
909 ,phl.index_figure
910 INTO v_index_line_id
911 ,v_index_figure
912 FROM pn_index_history_lines phl
913 WHERE phl.index_id = p_index_history_id
914 AND TO_CHAR (phl.index_date, 'Mm-YYYY') =
915 TO_CHAR (p_index_finder_date, 'Mm-YYYY');
916 --
917 -- Only return value and id if the index value is populated
918 --
919 op_cpi_value := v_index_figure;
920 op_cpi_id := v_index_line_id;
921 EXCEPTION
922 WHEN TOO_MANY_ROWS
923 THEN
924 put_log (' Cannot Derive Index Amount - TOO_MANY_ROWS');
925 WHEN NO_DATA_FOUND
926 THEN
927 --put_log (' Cannot Derive Index Amount - NO_DATA_FOUND');
928 put_log (
929 ' Cannot Find Index Record for '
930 || NVL (TO_CHAR (p_index_finder_date, 'mon-yy'), 'No Finder Date Provided'));
931 WHEN OTHERS
932 THEN
933 put_log ( ' Cannot Derive Index Amount - Unknow Error:'
934 || SQLERRM);
935 END lookup_index_history;
936
937
938 -------------------------------------------------------------------------------
939 -- PROCDURE : get_term_template
940 -- INVOKED FROM :
941 -- PURPOSE :
942 -- HISTORY :
943 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_term_templates with _ALL table.
944 -------------------------------------------------------------------------------
945 FUNCTION get_term_template (
946 p_term_template_id NUMBER)
947 RETURN VARCHAR2
948 IS
949 l_term_template VARCHAR2 (100);
950 BEGIN
951 SELECT name
952 INTO l_term_template
953 FROM pn_term_templates_all
954 WHERE term_template_id = p_term_template_id;
955 RETURN l_term_template;
956 EXCEPTION
957 WHEN OTHERS
958 THEN
959 RETURN NULL;
960 END get_term_template;
961
962
963 ------------------------------------------------------------------------
964 -- PROCEDURE : GET_APPROVER
965 ------------------------------------------------------------------------
966
967 FUNCTION get_approver (
968 p_approved_by NUMBER)
969 RETURN VARCHAR2 IS
970 l_approver VARCHAR2 (100);
971 BEGIN
972 SELECT user_name
973 INTO l_approver
974 FROM fnd_user
975 WHERE user_id = p_approved_by;
976 RETURN l_approver;
977 EXCEPTION
978 WHEN OTHERS
979 THEN
980 RETURN NULL;
981 END get_approver;
982
983
984 -------------------------------------------------------------------------------
985 -- PROCDURE : get_lease_class
986 -- INVOKED FROM :
987 -- PURPOSE :
988 -- HISTORY :
989 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_leases with _ALL table.
990 -------------------------------------------------------------------------------
991
992 FUNCTION get_lease_class (
993 p_lease_id NUMBER)
994 RETURN VARCHAR2
995 IS
996 l_lease_class VARCHAR2 (30);
997 BEGIN
998 SELECT lease_class_code
999 INTO l_lease_class
1000 FROM pn_leases_all
1001 WHERE lease_id = p_lease_id;
1002 RETURN l_lease_class;
1003 EXCEPTION
1004 WHEN OTHERS
1005 THEN
1006 RETURN NULL;
1007 END get_lease_class;
1008
1009
1010 ------------------------------------------------------------------------
1011 -- PROCEDURE : append_msg
1012 -- DESCRIPTION: This procedure will append the new message (p_new_messages) to
1013 -- existing messages.
1014 --
1015 ------------------------------------------------------------------------
1016
1017 PROCEDURE append_msg (
1018 p_new_msg IN VARCHAR2
1019 ,p_all_msg IN OUT NOCOPY VARCHAR2) IS
1020 v_existing_msg VARCHAR2 (1000);
1021 BEGIN
1022 v_existing_msg := p_all_msg;
1023
1024 IF p_new_msg IS NOT NULL
1025 THEN
1026 IF p_all_msg = 'PN_INDEX_SUCCESS'
1027 OR p_all_msg IS NULL
1028 THEN
1029 p_all_msg := p_new_msg;
1030 ELSE
1031 -- checking if error already has been recorded
1032 IF INSTR (v_existing_msg, p_new_msg) = 0
1033 THEN
1034 p_all_msg := v_existing_msg
1035 || ','
1036 || p_new_msg;
1037 END IF; --INSTR (v_existing_msg, p_new_msg) = 0
1038 END IF; --
1039 END IF; --p_new_msg IS NOT NULL
1040 END append_msg;
1041
1042
1043 ------------------------------------------------------------------------
1044 -- PROCEDURE : GET_SALESPERSON
1045 -- HISTORY
1046 -- 24-MAR-06 Hareesha o Bug # 5116270 Added org_id parameter to
1047 -- get_salesperson
1048 ------------------------------------------------------------------------
1049 FUNCTION get_salesperson (
1050 p_salesrep_id NUMBER,
1051 p_org_id NUMBER) RETURN VARCHAR2 IS
1052
1053 l_salesperson ra_salesreps.name%type;
1054
1055 CURSOR get_salesrep_cur IS
1056 SELECT name
1057 FROM ra_salesreps
1058 WHERE salesrep_id = p_salesrep_id
1059 AND org_id = p_org_id;
1060
1061 BEGIN
1062 FOR rec IN get_salesrep_cur LOOP
1063 l_salesperson := rec.name;
1064 END LOOP;
1065
1066 RETURN l_salesperson;
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 RAISE;
1070
1071 END get_salesperson;
1072
1073
1074 ------------------------------------------------------------------------
1075 -- PROCEDURE : GET_INVOICING_RULE
1076 ------------------------------------------------------------------------
1077
1078 FUNCTION get_invoicing_rule (
1079 p_rule_id NUMBER)
1080 RETURN VARCHAR2 IS
1081 l_invoicing_rule VARCHAR2 (100);
1082 BEGIN
1083 SELECT name
1084 INTO l_invoicing_rule
1085 FROM ra_rules
1086 WHERE rule_id = p_rule_id;
1087 RETURN l_invoicing_rule;
1088 EXCEPTION
1089 WHEN OTHERS
1090 THEN
1091 RETURN NULL;
1092 END get_invoicing_rule;
1093
1094 ------------------------------------------------------------------------
1095 -- PROCEDURE : GET_ACCOUNTING_RULE
1096 ------------------------------------------------------------------------
1097
1098 FUNCTION get_accounting_rule (
1099 p_rule_id NUMBER)
1100 RETURN VARCHAR2 IS
1101 l_accounting_rule VARCHAR2 (100);
1102 BEGIN
1103 SELECT name
1104 INTO l_accounting_rule
1105 FROM ra_rules
1106 WHERE rule_id = p_rule_id;
1107 RETURN l_accounting_rule;
1108 EXCEPTION
1109 WHEN OTHERS
1110 THEN
1111 RETURN NULL;
1112 END get_accounting_rule;
1113
1114
1115 ------------------------------------------------------------------------
1116 -- PROCEDURE : GET_DISTRIBUTION_SET
1117 -- HISTORY:
1118 -- 28-NOV-05 pikhar o Replaced ap_distribution_set with _ALL table
1119 ------------------------------------------------------------------------
1120
1121 FUNCTION get_distribution_set (
1122 p_distribution_set_id NUMBER)
1123 RETURN VARCHAR2 IS
1124 l_distribution_set VARCHAR2 (100);
1125 BEGIN
1126 SELECT distribution_set_name
1127 INTO l_distribution_set
1128 FROM ap_distribution_sets_all
1129 WHERE distribution_set_id = p_distribution_set_id;
1130 RETURN l_distribution_set;
1131 EXCEPTION
1132 WHEN OTHERS
1133 THEN
1134 RETURN NULL;
1135 END get_distribution_set;
1136
1137
1138 /*============================================================================+
1139 -- NAME : get_project_details
1140 -- DESCRIPTION : This FUNCTION RETURNs details of a project for a project id.
1141 -- SCOPE : PUBLIC
1142 -- INVOKED FROM : forms libraries
1143 -- ARGUMENTS : IN : p_project_id, p_project_name, p_organization
1144 -- RETURNS : Transaction Type
1145 -- HISTORY :
1146 -- 24-Jun-05 piagrawa o Bug 4284035 - Replaced pa_projects with _all tables.
1147 +============================================================================*/
1148 PROCEDURE get_project_details (
1149 p_project_id IN NUMBER
1150 ,p_project_name OUT NOCOPY VARCHAR2
1151 ,p_organization OUT NOCOPY VARCHAR2) AS
1152 CURSOR c IS
1153 SELECT projects.name project_name
1154 ,org.name organization
1155 FROM pa_projects_all projects, hr_organization_units org
1156 WHERE projects.project_id = p_project_id
1157 AND projects.carrying_out_organization_id = org.organization_id;
1158 BEGIN
1159 FOR c_rec IN c
1160 LOOP
1161 p_project_name := c_rec.project_name;
1162 p_organization := c_rec.ORGANIZATION;
1163 END LOOP;
1164 END get_project_details;
1165
1166 -------------------------------------------------------------------------------
1167 -- PROCDURE : find_if_hist_line_used
1168 -- INVOKED FROM :
1169 -- PURPOSE :
1170 -- HISTORY :
1171 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods,
1172 -- with _ALL table.
1173 -------------------------------------------------------------------------------
1174 FUNCTION find_if_hist_line_used (
1175 p_index_line_id NUMBER)
1176 RETURN NUMBER IS
1177 l_hist_line_used NUMBER;
1178 BEGIN
1179 BEGIN
1180 SELECT 1
1181 INTO l_hist_line_used
1182 FROM DUAL
1183 WHERE EXISTS ( SELECT periods.previous_index_line_id
1184 FROM pn_index_lease_periods_all periods
1185 ,pn_index_history_lines lines
1186 WHERE periods.previous_index_line_id = lines.index_line_id
1187 AND lines.index_line_id = p_index_line_id);
1188 RETURN l_hist_line_used;
1189 EXCEPTION
1190 WHEN OTHERS
1191 THEN
1192 l_hist_line_used := NULL;
1193 END;
1194
1195 IF l_hist_line_used IS NULL
1196 THEN
1197 BEGIN
1198 SELECT 2
1199 INTO l_hist_line_used
1200 FROM DUAL
1201 WHERE EXISTS ( SELECT periods.current_index_line_id
1202 FROM pn_index_lease_periods_all periods
1203 ,pn_index_history_lines lines
1204 WHERE periods.current_index_line_id = lines.index_line_id
1205 AND lines.index_line_id = p_index_line_id);
1206 RETURN l_hist_line_used;
1207 EXCEPTION
1208 WHEN OTHERS
1209 THEN
1210 l_hist_line_used := NULL;
1211 END;
1212 END IF;
1213
1214 RETURN l_hist_line_used;
1215 END find_if_hist_line_used;
1216
1217 -------------------------------------------------------------------------------
1218 -- PROCDURE : find_if_calc_exists
1219 -- INVOKED FROM :
1220 -- PURPOSE : Find if Calculation is Done
1221 -- HISTORY :
1222 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_payment_terms,pn_index_leases
1223 -- pn_index_lease_periods with _ALL table.
1224 -------------------------------------------------------------------------------
1225 FUNCTION find_if_calc_exists (
1226 p_index_lease_id NUMBER)
1227 RETURN NUMBER IS
1228 l_calc_exists NUMBER;
1229 BEGIN
1230 SELECT 1
1231 INTO l_calc_exists
1232 FROM DUAL
1233 WHERE EXISTS ( SELECT terms.payment_term_id
1234 FROM pn_payment_terms_all terms
1235 ,pn_index_leases_all lease
1236 ,pn_index_lease_periods_all periods
1237 WHERE terms.index_period_id = periods.index_period_id
1238 AND periods.index_lease_id = lease.index_lease_id
1239 AND lease.index_lease_id = p_index_lease_id);
1240 RETURN l_calc_exists;
1241 EXCEPTION
1242 WHEN OTHERS
1243 THEN
1244 RETURN NULL;
1245 END find_if_calc_exists;
1246
1247 -------------------------------------------------------------------------------
1248 -- PROCDURE : find_if_basis_exists
1249 -- INVOKED FROM :
1250 -- PURPOSE : Find if Current Basis Exists for any period
1251 -- HISTORY :
1252 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_lease_periods with _ALL
1253 -- table.
1254 -------------------------------------------------------------------------------
1255 FUNCTION find_if_basis_exists (
1256 p_index_lease_id NUMBER)
1257 RETURN NUMBER
1258 IS
1259 l_basis_exists NUMBER;
1260 BEGIN
1261 SELECT 1
1262 INTO l_basis_exists
1263 FROM DUAL
1264 WHERE EXISTS ( SELECT current_basis
1265 FROM pn_index_lease_periods_all
1266 WHERE index_lease_id = p_index_lease_id
1267 AND current_basis is null);
1268 RETURN l_basis_exists;
1269 EXCEPTION
1270 WHEN OTHERS
1271 THEN
1272 RETURN NULL;
1273 END find_if_basis_exists;
1274
1275 -------------------------------------------------------------------------------
1276 -- PROCDURE : GET_MAX_SCHEDULE_DATE
1277 -- INVOKED FROM :
1278 -- PURPOSE : Get the max schedule date from pn_payment_schedules
1279 -- that has exported items associated with it.
1280 -- HISTORY :
1281 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_payment_schedules with _ALL table.
1282 -------------------------------------------------------------------------------
1283 FUNCTION GET_MAX_SCHEDULE_DATE (p_index_leaseId IN NUMBER
1284 ) RETURN DATE
1285 IS
1286
1287 l_max_sch_date DATE ;
1288
1289 BEGIN
1290 BEGIN
1291 SELECT max(SCHEDULE_DATE)
1292 INTO l_max_sch_date
1293 FROM pn_index_lease_periods_all pilp,
1294 pn_payment_terms_all ppt,
1295 pn_payment_items_all ppi,
1296 pn_payment_schedules_all pps
1297 WHERE pilp.index_period_id=ppt.index_period_id
1298 AND ppt.payment_term_id=ppi.payment_term_id
1299 AND pps.payment_schedule_id=ppi.payment_schedule_id
1300 AND (ppi.export_to_ar_flag='Y' OR
1301 ppi.export_to_ap_flag='Y' )
1302 AND pilp.index_lease_id=p_index_leaseId;
1303
1304 EXCEPTION
1305 when NO_DATA_FOUND then
1306 return NULL;
1307 END;
1308 return l_max_sch_date;
1309 END GET_MAX_SCHEDULE_DATE;
1310
1311 ------------------------------------------------------------------------
1312 -- FUNCTION : GET_PROJECT_NAME
1313 -- HISTORY
1314 -- 23-APR-04 ftanudja o Changed pa_projects_expend_v to pa_projects
1315 -- for performance. #3239094.
1316 -- 28-NOV-05 pikhar o replaced pa_projects with _ALL table
1317 ------------------------------------------------------------------------
1318
1319 FUNCTION get_project_name (
1320 p_project_id NUMBER)
1321 RETURN VARCHAR2 IS
1322 l_project_name VARCHAR2 (100);
1323 BEGIN
1324 SELECT name
1325 INTO l_project_name
1326 FROM pa_projects_all
1327 WHERE project_id = p_project_id;
1328 RETURN l_project_name;
1329 EXCEPTION
1330 WHEN OTHERS
1331 THEN
1332 RETURN NULL;
1333 END get_project_name;
1334
1335 -------------------------------------------------------------------------------
1336 -- PROCDURE : chk_for_approved_index_periods
1337 -- INVOKED FROM :
1338 -- PURPOSE :
1339 -- HISTORY :
1340 -- 11-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_exclude_term with _ALL table.
1341 -------------------------------------------------------------------------------
1342 PROCEDURE get_exclude_term ( p_index_lease_id IN NUMBER,
1343 p_payment_term_id IN NUMBER,
1344 p_exclude_flag OUT NOCOPY VARCHAR2,
1345 p_index_exclude_term_id OUT NOCOPY NUMBER)
1346 IS
1347 BEGIN
1348 SELECT 'N' ,index_exclude_term_id
1349 INTO p_exclude_flag,p_index_exclude_term_id
1350 FROM pn_index_exclude_term_all
1351 WHERE index_lease_id=p_index_lease_id
1352 AND payment_term_id=p_payment_term_id;
1353
1354 EXCEPTION
1355 WHEN OTHERS
1356 THEN
1357 NULL;
1358 END get_exclude_term;
1359
1360 ------------------------------------------------------------------------
1361 -- FUNCTION : GET_AP_ORGANIZATION_NAME
1362 -- HISTORY :
1363 -- 25-MAR-2004 Mrinal Misra o Changed view name in SELECT statement.
1364 ------------------------------------------------------------------------
1365
1366 FUNCTION get_ap_organization_name (
1367 p_organization_id NUMBER )
1368 RETURN VARCHAR2 IS
1369 l_organization_name VARCHAR2 (60);
1370 BEGIN
1371 SELECT name
1372 INTO l_organization_name
1373 FROM pa_organizations_expend_v
1374 WHERE organization_id = p_organization_id;
1375 RETURN l_organization_name;
1376 EXCEPTION
1377 WHEN OTHERS
1378 THEN
1379 RETURN NULL;
1380 END get_ap_organization_name;
1381
1382 -------------------------------------------------------------------------------
1383 -- NAME : UPDATE_LOCATION_FOR_IR_TERMS()
1384 -- PURPOSE : Updates the location ID for the terms assocaited with IR
1385 -- DESCRIPTION : Updates the location ID for the terms assocaited with IR
1386 -- whenever the location associated with IR agreement is
1387 -- updated.
1388 -- SCOPE : PUBLIC
1389 -- ARGUMENTS : p_index_lease_id : index lease ID.
1390 -- p_location_id : new location ID that terms to be updated with
1391 -- p_return_status : return status of the procedure
1392 -- RETURNS : None
1393 -- HISTORY :
1394 -- 03-JUN-04 ATUPPAD o Created.
1395 -- For 'Edit location at IR Agreement' enhancement.
1396 -------------------------------------------------------------------------------
1397 PROCEDURE UPDATE_LOCATION_FOR_IR_TERMS(
1398 p_index_lease_id IN NUMBER,
1399 p_location_id IN NUMBER,
1400 p_return_status OUT NOCOPY VARCHAR2)
1401 IS
1402
1403 CURSOR C_UPD_TERMS IS
1404 SELECT ppt.payment_term_id,
1405 NVL(ppt.status, 'X')
1406 FROM PN_PAYMENT_TERMS_ALL ppt,
1407 PN_INDEX_LEASE_PERIODS_ALL pilp
1408 WHERE ppt.status = 'DRAFT'
1409 AND ppt.index_period_id = pilp.index_period_id
1410 AND pilp.index_lease_id = p_index_lease_id
1411 UNION ALL
1412 SELECT ppt.payment_term_id,
1413 NVL(ppt.status, 'X')
1414 FROM PN_PAYMENT_TERMS_ALL ppt,
1415 PN_INDEX_LEASE_PERIODS_ALL pilp,
1416 PN_LEASES_ALL pl
1417 WHERE ppt.status = 'APPROVED'
1418 AND ppt.lease_id = pl.lease_id
1419 AND ppt.index_period_id = pilp.index_period_id
1420 AND pilp.index_lease_id = p_index_lease_id
1421 AND EXISTS (SELECT NULL
1422 FROM PN_PAYMENT_ITEMS_ALL ppi
1423 WHERE DECODE(pl.lease_class_code,
1424 'DIRECT', NVL(ppi.transferred_to_ap_flag,'N'),
1425 'THIRD_PARTY', NVL(ppi.transferred_to_ar_flag,'N'),
1426 'SUB_LEASE', NVL(ppi.transferred_to_ar_flag,'N')) = 'N'
1427 AND ppi.payment_term_id = ppt.payment_term_id);
1428
1429 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1430 TYPE char_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1431 l_payment_term_id number_tbl_type;
1432 l_payment_status char_tbl_type;
1433 l_flag BOOLEAN := FALSE;
1434
1435 BEGIN
1436 PNP_DEBUG_PKG.debug ('PN_INDEX_LEASE_COMMON_PKG.UPDATE_LOCATION_FOR_IR_TERMS (+)');
1437
1438 OPEN C_UPD_TERMS;
1439 LOOP
1440 FETCH C_UPD_TERMS
1441 BULK COLLECT INTO l_payment_term_id, l_payment_status
1442 LIMIT 1000;
1443
1444 FORALL i IN 1..l_payment_term_id.COUNT
1445 UPDATE PN_PAYMENT_TERMS_ALL
1446 SET location_id = p_location_id
1447 WHERE payment_term_id = l_payment_term_id(i);
1448
1449 FOR i IN 1..l_payment_term_id.COUNT LOOP
1450 IF (l_payment_status(i) = 'APPROVED') THEN
1451 l_flag := TRUE;
1452 EXIT;
1453 END IF;
1454 END LOOP;
1455
1456 EXIT WHEN C_UPD_TERMS%NOTFOUND;
1457
1458 END LOOP;
1459
1460 -- Initialize API return status to success
1461 p_return_status := FND_API.G_RET_STS_SUCCESS;
1462
1463 IF l_flag THEN
1464 p_return_status := 'SUCCESS_FIRE_RECALC';
1465 END IF;
1466
1467 PNP_DEBUG_PKG.debug ('PN_INDEX_LEASE_COMMON_PKG.UPDATE_LOCATION_FOR_IR_TERMS (-)');
1468
1469 EXCEPTION
1470
1471 WHEN FND_API.G_EXC_ERROR THEN
1472 p_return_status := FND_API.G_RET_STS_ERROR;
1473
1474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1475 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1476
1477 WHEN OTHERS THEN
1478 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1479
1480 END UPDATE_LOCATION_FOR_IR_TERMS;
1481
1482 /* --- OVERLOADED functions and procedures for MOAC START --- */
1483 /*============================================================================+
1484 -- NAME : get_ar_trx_type
1485 -- DESCRIPTION : This FUNCTION RETURNs Transaction Type for a given Customer
1486 -- Transaction Type Id FROM Receivables.
1487 -- SCOPE : PUBLIC
1488 -- INVOKED FROM : forms libraries
1489 -- ARGUMENTS : IN : p_trx_id, p_org_id
1490 -- RETURNS : Transaction Type
1491 -- HISTORY :
1492 -- 24-Jun-05 piagrawa o Created
1493 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
1494 -- must call this.
1495 -- 28-NOV-05 pikhar o replaced ra_cust_trx_types with _ALL table.
1496 +============================================================================*/
1497
1498 FUNCTION get_ar_trx_type ( p_cust_trx_type_id NUMBER
1499 ,p_org_id IN NUMBER) RETURN VARCHAR2 IS
1500 l_name VARCHAR2 (20);
1501 BEGIN
1502 SELECT name
1503 INTO l_name
1504 FROM ra_cust_trx_types_all
1505 WHERE cust_trx_type_id = p_cust_trx_type_id
1506 AND org_id = p_org_id;
1507
1508 RETURN l_name;
1509
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 RETURN NULL;
1513 END get_ar_trx_type;
1514
1515 -----------------------------------------------------------------------
1516 -- NAME : get_po_number
1517 -- DESCRIPTION : This FUNCTION returns the po number
1518 -- SCOPE : PUBLIC
1519 -- INVOKED FROM : forms libraries
1520 -- ARGUMENTS : IN : p_po_header_id, p_org_id
1521 -- RETURNS : po number
1522 -- HISTORY :
1523 -- 24-Jun-05 piagrawa o Created
1524 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
1525 -- must call this.
1526 ------------------------------------------------------------------------
1527
1528 FUNCTION get_po_number ( p_po_header_id NUMBER
1529 ,p_org_id NUMBER) RETURN VARCHAR2
1530 IS
1531 l_po_number VARCHAR2 (30);
1532 BEGIN
1533 SELECT segment1
1534 INTO l_po_number
1535 FROM po_headers_all
1536 WHERE po_header_id = p_po_header_id
1537 AND org_id = p_org_id;
1538
1539 RETURN l_po_number;
1540
1541 EXCEPTION
1542 WHEN OTHERS THEN
1543 RETURN NULL;
1544 END get_po_number;
1545
1546
1547
1548 ------------------------------------------------------------------------
1549 -- NAME : get_distribution_set
1550 -- DESCRIPTION : This FUNCTION returns the distribution set
1551 -- SCOPE : PUBLIC
1552 -- INVOKED FROM : forms libraries
1553 -- ARGUMENTS : IN : p_distribution_set_id, p_org_id
1554 -- RETURNS : distribution set
1555 -- HISTORY :
1556 -- 24-Jun-05 piagrawa o Created
1557 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
1558 -- must call this.
1559 -- 28-Nov-05 pikhar o replaced ap_distribution_sets with _ALL
1560 ------------------------------------------------------------------------
1561
1562 FUNCTION get_distribution_set (p_distribution_set_id NUMBER
1563 , p_org_id NUMBER) RETURN VARCHAR2 IS
1564 l_distribution_set VARCHAR2 (100);
1565 BEGIN
1566 SELECT distribution_set_name
1567 INTO l_distribution_set
1568 FROM ap_distribution_sets_all
1569 WHERE distribution_set_id = p_distribution_set_id
1570 AND org_id = p_org_id;
1571
1572 RETURN l_distribution_set;
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 RETURN NULL;
1576 END get_distribution_set;
1577
1578
1579 ------------------------------------------------------------------------
1580 -- NAME : get_project_name
1581 -- DESCRIPTION : This FUNCTION returns the project name.
1582 -- SCOPE : PUBLIC
1583 -- INVOKED FROM : forms libraries
1584 -- ARGUMENTS : IN : p_project_id, p_org_id
1585 -- RETURNS : project name
1586 -- HISTORY :
1587 -- 24-Jun-05 piagrawa o Created
1588 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
1589 -- must call this.
1590 ------------------------------------------------------------------------
1591 FUNCTION get_project_name ( p_project_id NUMBER
1592 ,p_org_id NUMBER) RETURN VARCHAR2 IS
1593 l_project_name VARCHAR2 (100);
1594 BEGIN
1595 SELECT name
1596 INTO l_project_name
1597 FROM pa_projects_all
1598 WHERE project_id = p_project_id
1599 AND org_id = p_org_id;
1600
1601 RETURN l_project_name;
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 RETURN NULL;
1606 END get_project_name;
1607 /* --- OVERLOADED functions and procedures for MOAC END --- */
1608
1609 END pn_index_lease_common_pkg;
[Home] [Help]