[Home] [Help]
PACKAGE: APPS.PN_INDEX_LEASE_COMMON_PKG
Source
1 PACKAGE pn_index_lease_common_pkg AUTHID CURRENT_USER AS
2 -- $Header: PNINCOMS.pls 120.4 2007/01/02 07:40:30 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 function GET_AP_ORGANIZATION_NAME
23 -- | 13-DEC-01 Mrinal o Added dbdrv command.
24 -- | 12-AUG-02 Pooja o Added param p_carry_forward_flag to get_index_lease.
25 -- | 26-NOV-03 Daniel o Added parameters p_aggregation_flag,
26 -- | p_index_finder_months to get_index_lease.
27 -- | Fix for bug 3271061
28 -- | 15-JUN-04 Anand o Added proc spec UPDATE_LOCATION_FOR_IR_TERMS
29 -- | 24-JUN-05 piagrawa o Overloaded get_ar_trx_type, get_ap_tax_details,
30 -- | get_tax_group, get_po_number, get_distribution_set,
31 -- | get_project_name for use with MOAC
32 -- | 14-MAR-06 Hareesha o Bug #4756588 Removed procedure get_ap_tax_details,
33 -- | get_ar_tax_code,get_tax_group
34 -- | 24-MAR-06 Hareesha o Bug # 5116270 Added org_id parameter to
35 -- | get_salesperson
36 -- | 09-NOV-06 Prabhakar o Added parameter p_index_multiplier to get_index_lease
37 -- | Added p_index_multiplier to get_index_period.
38 -- | 08-DEC-06 Prabhakar o Added parameters proration_rule,proration_period_start_date
39 -- | to get_index_lease
40 -- +==========================================================================+
41
42
43 TYPE tax_data_rec IS RECORD (
44 tax_code ap_tax_codes.name%TYPE
45 ,group_code ap_awt_groups.name%TYPE);
46
47
48 ------------------------------------------------------------------------
49 -- PROCEDURE : chk_for_approved_index_periods
50 ------------------------------------------------------------------------
51 PROCEDURE chk_for_approved_index_periods (
52 p_index_lease_id IN NUMBER
53 ,p_index_lease_period_id IN NUMBER DEFAULT NULL
54 ,p_chk_index_ind IN VARCHAR2 DEFAULT NULL
55 ,p_msg IN OUT NOCOPY VARCHAR2);
56
57
58 ------------------------------------------------------------------------
59 -- PROCEDURE : chk_for_payment_required_fields
60 ------------------------------------------------------------------------
61 PROCEDURE chk_for_payment_reqd_fields (
62 p_payment_term_id IN NUMBER
63 ,p_msg OUT NOCOPY VARCHAR2);
64
65 ------------------------------------------------------------------------
66 -- PROCEDURE : chk_for_exported_items
67 -- DESCRIPTION: This procedure will check if an index rent period has
68 -- payment items that have been exported to ap or ar
69 ------------------------------------------------------------------------
70
71 PROCEDURE chk_for_exported_items (
72 ip_index_period_id IN NUMBER
73 ,op_msg OUT NOCOPY VARCHAR2);
74
75 ------------------------------------------------------------------------
76 -- PROCEDURE : put_log
77 ------------------------------------------------------------------------
78 PROCEDURE put_log (
79 p_string VARCHAR2);
80
81
82 ------------------------------------------------------------------------
83 -- PROCEDURE : put_output
84 ------------------------------------------------------------------------
85 PROCEDURE put_output (
86 p_string VARCHAR2);
87
88
89 ------------------------------------------------------------------------
90 -- PROCEDURE : GET_INDEX_LEASE
91 ------------------------------------------------------------------------
92 PROCEDURE get_index_lease (
93 p_index_lease_id IN NUMBER DEFAULT NULL
94 ,p_commencement_date OUT NOCOPY DATE
95 ,p_termination_date OUT NOCOPY DATE
96 ,p_assessment_date OUT NOCOPY DATE
97 ,p_assessment_interval OUT NOCOPY NUMBER
98 ,p_relationship_default OUT NOCOPY VARCHAR2
99 ,p_spread_frequency OUT NOCOPY VARCHAR2
100 ,p_basis_percent_default OUT NOCOPY NUMBER
101 ,p_intial_basis OUT NOCOPY NUMBER
102 ,p_base_index OUT NOCOPY NUMBER
103 ,p_index_finder_method OUT NOCOPY VARCHAR2
104 ,p_negative_rent_type OUT NOCOPY VARCHAR2
105 ,p_increase_on OUT NOCOPY VARCHAR2
106 ,p_basis_type OUT NOCOPY VARCHAR2
107 ,p_reference_period OUT NOCOPY VARCHAR2
108 ,p_base_year OUT NOCOPY DATE
109 ,p_rounding_flag OUT NOCOPY VARCHAR2
110 ,p_gross_flag OUT NOCOPY VARCHAR2
111 ,p_carry_forward_flag OUT NOCOPY VARCHAR2
112 ,p_aggregation_flag OUT NOCOPY VARCHAR2
113 ,p_index_finder_months OUT NOCOPY NUMBER
114 ,p_index_multiplier OUT NOCOPY NUMBER
115 ,p_proration_rule OUT NOCOPY VARCHAR2
116 ,p_proration_period_start_date OUT NOCOPY DATE);
117
118
119 ------------------------------------------------------------------------
120 -- PROCEDURE : GET_INDEX_PERIOD
121 ------------------------------------------------------------------------
122 PROCEDURE get_index_period (
123 p_index_period_id IN NUMBER DEFAULT NULL
124 ,p_basis_start_date OUT NOCOPY DATE
125 ,p_basis_end_date OUT NOCOPY DATE
126 ,p_index_finder_date OUT NOCOPY DATE
127 ,p_current_basis OUT NOCOPY NUMBER
128 ,p_relationship OUT NOCOPY VARCHAR2
129 ,p_index_percent_change OUT NOCOPY NUMBER
130 ,p_basis_percent_change OUT NOCOPY NUMBER
131 ,p_index_multiplier OUT NOCOPY NUMBER);
132
133
134
135 ------------------------------------------------------------------------
136 -- PROCEDURE : GET_INDEX_START_END_DATES
137 ------------------------------------------------------------------------
138 PROCEDURE get_index_start_end_dates (
139 p_index_period_id IN NUMBER DEFAULT NULL
140 ,p_commencement_date OUT NOCOPY DATE
141 ,p_termination_date OUT NOCOPY DATE );
142
143
144 ------------------------------------------------------------------------
145 -- PROCEDURE : GET_INDEX_DETAILS
146 ------------------------------------------------------------------------
147 PROCEDURE get_index_details (
148 p_index_line_id IN NUMBER DEFAULT NULL
149 ,p_index_date OUT NOCOPY DATE
150 ,p_index_figure OUT NOCOPY NUMBER);
151
152
153 ------------------------------------------------------------------------
154 -- PROCEDURE : GET_AR_PAYMENT_TERM
155 ------------------------------------------------------------------------
156
157 FUNCTION get_ar_payment_term (
158 p_term_id NUMBER)
159 RETURN VARCHAR2;
160
161
162 ------------------------------------------------------------------------
163 -- PROCEDURE : GET_AP_PAYMENT_TERM
164 ------------------------------------------------------------------------
165
166 FUNCTION get_ap_payment_term (
167 p_term_id NUMBER)
168 RETURN VARCHAR2;
169
170
171 ------------------------------------------------------------------------
172 -- PROCEDURE : GET_AR_TRX_TYPE
173 ------------------------------------------------------------------------
174
175 FUNCTION get_ar_trx_type (
176 p_cust_trx_type_id NUMBER)
177 RETURN VARCHAR2;
178
179
180 TYPE index_lease_periods_rec IS RECORD (
181 current_basis pn_index_lease_periods.current_basis%TYPE
182 ,constraint_rent_due pn_index_lease_periods.constraint_rent_due%TYPE
183 ,unconstraint_rent_due pn_index_lease_periods.unconstraint_rent_due%TYPE
184 ,index_percent_change pn_index_lease_periods.index_percent_change%TYPE
185 ,current_index_line_id pn_index_lease_periods.current_index_line_id%TYPE
186 ,current_index_line_value pn_index_lease_periods.current_index_line_value%TYPE
187 ,previous_index_line_id pn_index_lease_periods.previous_index_line_id%TYPE
188 ,previous_index_line_value pn_index_lease_periods.previous_index_line_value%TYPE);
189
190
191 ------------------------------------------------------------------------
192 -- PROCEDURE : GET_INDEX_PERIOD_DETAILS
193 ------------------------------------------------------------------------
194
195 FUNCTION get_index_period_details (
196 p_index_period_id NUMBER)
197 RETURN index_lease_periods_rec;
198
199
200 ------------------------------------------------------------------------
201 -- PROCEDURE : GET_LEASE_CHANGE_ID
202 ------------------------------------------------------------------------
203
204 FUNCTION get_lease_change_id (
205 p_lease_id NUMBER)
206 RETURN NUMBER;
207
208 PROCEDURE delete_index_payment_term (
209 p_index_period_id IN NUMBER
210 ,p_payment_term_id IN NUMBER DEFAULT NULL
211 ,p_msg OUT NOCOPY VARCHAR2);
212
213
214 ------------------------------------------------------------------------
215 -- PROCEDURE : Find if period Exists
216 ------------------------------------------------------------------------
217
218 FUNCTION find_if_period_exists (
219 p_index_lease_id NUMBER)
220 RETURN NUMBER;
221
222
223 ------------------------------------------------------------------------
224 -- PROCEDURE : Find if Term Exists
225 ------------------------------------------------------------------------
226
227 FUNCTION find_if_term_exists (
228 p_index_period_id NUMBER)
229 RETURN NUMBER;
230
231
232 ------------------------------------------------------------------------
233 -- PROCEDURE : Find if Any Normalized Payment Term Exists
234 ------------------------------------------------------------------------
235
236 FUNCTION find_if_norm_term_exists (
237 p_index_period_id NUMBER)
238 RETURN NUMBER;
239
240
241 ------------------------------------------------------------------------
242 -- PROCEDURE : Find if Template is used by any Index Lease
243 ------------------------------------------------------------------------
244
245 FUNCTION find_if_template_used (
246 p_term_template_id NUMBER)
247 RETURN NUMBER;
248
249
250 ------------------------------------------------------------------------
251 -- PROCEDURE : GET TERM STATUS
252 ------------------------------------------------------------------------
253
254 FUNCTION get_term_status (
255 p_payment_term_id NUMBER)
256 RETURN VARCHAR2;
257
258
259 ------------------------------------------------------------------------
260 -- PROCEDURE : GET PO NUMBER
261 ------------------------------------------------------------------------
262
263 FUNCTION get_po_number (
264 p_po_header_id NUMBER)
265 RETURN VARCHAR2;
266
267
268 ------------------------------------------------------------------------
269 -- PROCEDURE : GET RECEIPT METHOD
270 ------------------------------------------------------------------------
271
272 FUNCTION get_receipt_method (
273 p_receipt_method_id NUMBER)
274 RETURN VARCHAR2;
275
276
277 ------------------------------------------------------------------------
278 -- PROCEDURE : GET LOCATION CODE
279 ------------------------------------------------------------------------
280
281 FUNCTION get_location (
282 p_location_id NUMBER)
283 RETURN VARCHAR2;
284
288 -- PROCEDURE : lookup_index_history
285
286 ----------
287 --------------------------------------------------------------
289 -- DESCRIPTION: This procedure will derive the cpi value and index history id using
290 -- finder date provided.
291 --
292 ------------------------------------------------------------------------
293
294 PROCEDURE lookup_index_history (
295 p_index_history_id IN NUMBER
296 ,p_index_finder_date IN DATE
297 ,op_cpi_value OUT NOCOPY NUMBER
298 ,op_cpi_id OUT NOCOPY NUMBER
299 ,op_msg OUT NOCOPY VARCHAR2);
300
301
302 ------------------------------------------------------------------------
303 -- PROCEDURE : GET_TERM_TEMPLATE
304 ------------------------------------------------------------------------
305
306 FUNCTION get_term_template (
307 p_term_template_id NUMBER)
308 RETURN VARCHAR2;
309
310
311 ------------------------------------------------------------------------
312 -- PROCEDURE : GET_APPROVER
313 ------------------------------------------------------------------------
314
315 FUNCTION get_approver (
316 p_approved_by NUMBER)
317 RETURN VARCHAR2;
318
319
320 ------------------------------------------------------------------------
321 -- PROCEDURE : GET_LEASE_CLASS
322 ------------------------------------------------------------------------
323
324 FUNCTION get_lease_class (
325 p_lease_id NUMBER)
326 RETURN VARCHAR2;
327
328 PROCEDURE append_msg (
329 p_new_msg IN VARCHAR2
330 ,p_all_msg IN OUT NOCOPY VARCHAR2);
331
332
333 ------------------------------------------------------------------------
334 -- PROCEDURE : GET_SALESPERSON
335 ------------------------------------------------------------------------
336
337 FUNCTION get_salesperson (
338 p_salesrep_id NUMBER,
339 p_org_id NUMBER)
340 RETURN VARCHAR2;
341
342
343 ------------------------------------------------------------------------
344 -- PROCEDURE : GET_INVOICING_RULE
345 ------------------------------------------------------------------------
346
347 FUNCTION get_invoicing_rule (
348 p_rule_id NUMBER)
349 RETURN VARCHAR2;
350
351
352 ------------------------------------------------------------------------
353 -- PROCEDURE : GET_ACCOUNTING_RULE
354 ------------------------------------------------------------------------
355
356 FUNCTION get_accounting_rule (
357 p_rule_id NUMBER)
358 RETURN VARCHAR2;
359
360
361 ------------------------------------------------------------------------
362 -- PROCEDURE : GET_DISTRIBUTION_SET
363 ------------------------------------------------------------------------
364
365 FUNCTION get_distribution_set (
366 p_distribution_set_id NUMBER)
367 RETURN VARCHAR2;
368
369
370 ------------------------------------------------------------------------
371 -- PROCEDURE : GET_PROJECT_DETAILS
372 ------------------------------------------------------------------------
373
374
375 PROCEDURE get_project_details (
376 p_project_id IN NUMBER DEFAULT NULL
377 ,p_project_name OUT NOCOPY VARCHAR2
378 ,p_organization OUT NOCOPY VARCHAR2);
379
380
381 ------------------------------------------------------------------------
382 -- PROCEDURE : Find if Index History Line is Used by an Index Lease
383 ------------------------------------------------------------------------
384
385 FUNCTION find_if_hist_line_used (
386 p_index_line_id NUMBER)
387 RETURN NUMBER;
388
389
390 ------------------------------------------------------------------------
391 -- PROCEDURE : Find if Calculation is Done
392 ------------------------------------------------------------------------
393
394 FUNCTION find_if_calc_exists (
395 p_index_lease_id NUMBER)
396 RETURN NUMBER;
397
398 ------------------------------------------------------------------------
399 -- PROCEDURE : Find if Current Basis Exists for any period
400 ------------------------------------------------------------------------
401
402 FUNCTION find_if_basis_exists (
403 p_index_lease_id NUMBER)
404 RETURN NUMBER;
405
406 ------------------------------------------------------------------------
407 -- FUNCTION : GET_MAX_SCHEDULE_DATE
408 --
409 -- Get the max schedule date from pn_payment_schedules
410 -- that has exported items associated with it.
411 ------------------------------------------------------------------------
412
413 FUNCTION GET_MAX_SCHEDULE_DATE (
414 p_index_leaseId IN NUMBER)
415 RETURN DATE;
416
417 ------------------------------------------------------------------------
418 -- FUNCTION : GET_PROJECT_NAME
419 ------------------------------------------------------------------------
420
421 FUNCTION get_project_name (
422 p_project_id NUMBER)
423 RETURN VARCHAR2;
424
425 ------------------------------------------------------------------------
426 -- PROCEDURE : GET_EXCLUDE_FLAG
427 ------------------------------------------------------------------------
428
429 PROCEDURE get_exclude_term ( p_index_lease_id IN NUMBER,
430 p_payment_term_id IN NUMBER,
431 p_exclude_flag OUT NOCOPY VARCHAR2,
432 p_index_exclude_term_id OUT NOCOPY NUMBER);
433
434 ------------------------------------------------------------------------
438 FUNCTION get_ap_organization_name (
435 -- FUNCTION : GET_AP_ORGANIZATION_NAME
436 ------------------------------------------------------------------------
437
439 p_organization_id NUMBER )
440 RETURN VARCHAR2;
441
442 -------------------------------------------------------------------------------
443 -- PROCEDURE to update the index rent terms with the changed location
444 -------------------------------------------------------------------------------
445 PROCEDURE UPDATE_LOCATION_FOR_IR_TERMS(
446 p_index_lease_id IN NUMBER,
447 p_location_id IN NUMBER,
448 p_return_status OUT NOCOPY VARCHAR2);
449
450 /* overloaded functions and procedures for MOAC */
451 -------------------------------------------------------------------------------
452 -- To return Transaction Type for a given Customer Transaction Type
453 -- Id from Receivables.
454 -- USE THIS IN R12
455 -------------------------------------------------------------------------------
456 FUNCTION get_ar_trx_type (p_cust_trx_type_id NUMBER, p_org_id NUMBER)
457 RETURN VARCHAR2;
458
459 -------------------------------------------------------------------------------
460 -- FUNCTION to return po number
461 -------------------------------------------------------------------------------
462 FUNCTION get_po_number ( p_po_header_id NUMBER,p_org_id NUMBER)
463 RETURN VARCHAR2;
464
465 -------------------------------------------------------------------------------
466 -- FUNCTION to return distribution set
467 -------------------------------------------------------------------------------
468 FUNCTION get_distribution_set (p_distribution_set_id NUMBER, p_org_id NUMBER)
469 RETURN VARCHAR2;
470
471 -------------------------------------------------------------------------------
472 -- FUNCTION to return project name
473 -------------------------------------------------------------------------------
474 FUNCTION get_project_name ( p_project_id NUMBER, p_org_id NUMBER)
475 RETURN VARCHAR2;
476
477 END pn_index_lease_common_pkg;
478
479
480
481