1 package PN_VAR_RENT_PKG AUTHID CURRENT_USER as
2 /* $Header: PNVRFUNS.pls 120.13 2008/04/01 10:13:05 bifernan noship $ */
3
4 status VARCHAR2(30) := 'REVERSED';
5
6 TYPE grp_date_info_rec IS RECORD (
7 grp_date_id PN_VAR_GRP_DATES.grp_date_id%TYPE,
8 grp_start_date PN_VAR_GRP_DATES.grp_start_date%TYPE,
9 grp_end_date PN_VAR_GRP_DATES.grp_end_date%TYPE,
10 group_date PN_VAR_GRP_DATES.group_date%TYPE,
11 reptg_due_date PN_VAR_GRP_DATES.reptg_due_date%TYPE,
12 inv_start_date PN_VAR_GRP_DATES.inv_start_date%TYPE,
13 inv_end_date PN_VAR_GRP_DATES.inv_end_date%TYPE,
14 invoice_date PN_VAR_GRP_DATES.invoice_date%TYPE,
15 inv_schedule_date PN_VAR_GRP_DATES.inv_schedule_date%TYPE,
16 forecasted_exp_code PN_VAR_GRP_DATES.forecasted_exp_code%TYPE
17 );
18
19 TYPE proration_factor_rec IS RECORD (
20 per_start_proration PN_VAR_PERIODS.proration_factor%TYPE,
21 first_per_gl_days PN_VAR_PERIODS.proration_factor%TYPE,
22 per_end_proration PN_VAR_PERIODS.proration_factor%TYPE,
23 last_per_gl_days PN_VAR_PERIODS.proration_factor%TYPE,
24 grp_start_proration PN_VAR_GRP_DATES.proration_factor%TYPE,
25 first_grp_gl_days PN_VAR_GRP_DATES.proration_factor%TYPE,
26 grp_end_proration PN_VAR_GRP_DATES.proration_factor%TYPE,
27 last_grp_gl_days PN_VAR_GRP_DATES.proration_factor%TYPE
28 );
29
30 PROCEDURE INSERT_PERIODS_ROW (
31 X_ROWID IN OUT NOCOPY VARCHAR2,
32 X_PERIOD_ID IN OUT NOCOPY NUMBER,
33 X_PERIOD_NUM IN OUT NOCOPY NUMBER,
34 X_VAR_RENT_ID IN NUMBER,
35 X_START_DATE IN DATE,
36 X_END_DATE IN DATE,
37 X_PRORATION_FACTOR IN NUMBER,
38 X_PARTIAL_PERIOD IN VARCHAR2,
39 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
40 X_ATTRIBUTE1 IN VARCHAR2,
41 X_ATTRIBUTE2 IN VARCHAR2,
42 X_ATTRIBUTE3 IN VARCHAR2,
43 X_ATTRIBUTE4 IN VARCHAR2,
44 X_ATTRIBUTE5 IN VARCHAR2,
45 X_ATTRIBUTE6 IN VARCHAR2,
46 X_ATTRIBUTE7 IN VARCHAR2,
47 X_ATTRIBUTE8 IN VARCHAR2,
48 X_ATTRIBUTE9 IN VARCHAR2,
49 X_ATTRIBUTE10 IN VARCHAR2,
50 X_ATTRIBUTE11 IN VARCHAR2,
51 X_ATTRIBUTE12 IN VARCHAR2,
52 X_ATTRIBUTE13 IN VARCHAR2,
53 X_ATTRIBUTE14 IN VARCHAR2,
54 X_ATTRIBUTE15 IN VARCHAR2,
55 X_CREATION_DATE IN DATE,
56 X_CREATED_BY IN NUMBER,
57 X_LAST_UPDATE_DATE IN DATE,
58 X_LAST_UPDATED_BY IN NUMBER,
59 X_LAST_UPDATE_LOGIN IN NUMBER,
60 X_ORG_ID NUMBER DEFAULT NULL
61 );
62
63 PROCEDURE DELETE_PERIODS_ROW (
64 X_VAR_RENT_ID IN NUMBER,
65 X_TERM_DATE IN DATE DEFAULT NULL
66 );
67
68 PROCEDURE CREATE_REPORT_DATES (p_var_rent_id IN NUMBER);
69
70 PROCEDURE INSERT_REPORT_DATE_ROW
71 (
72 X_ROWID IN OUT NOCOPY VARCHAR2,
73 X_REPORT_DATE_ID IN OUT NOCOPY NUMBER,
74 X_GRP_DATE_ID IN NUMBER,
75 X_VAR_RENT_ID IN NUMBER,
76 X_REPORT_START_DATE IN DATE,
77 X_REPORT_END_DATE IN DATE,
78 X_CREATION_DATE IN DATE,
79 X_CREATED_BY IN NUMBER,
80 X_LAST_UPDATE_DATE IN DATE,
81 X_LAST_UPDATED_BY IN NUMBER,
82 X_LAST_UPDATE_LOGIN IN NUMBER,
83 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
84 X_ATTRIBUTE1 IN VARCHAR2,
85 X_ATTRIBUTE2 IN VARCHAR2,
86 X_ATTRIBUTE3 IN VARCHAR2,
87 X_ATTRIBUTE4 IN VARCHAR2,
88 X_ATTRIBUTE5 IN VARCHAR2,
89 X_ATTRIBUTE6 IN VARCHAR2,
90 X_ATTRIBUTE7 IN VARCHAR2,
91 X_ATTRIBUTE8 IN VARCHAR2,
92 X_ATTRIBUTE9 IN VARCHAR2,
93 X_ATTRIBUTE10 IN VARCHAR2,
94 X_ATTRIBUTE11 IN VARCHAR2,
95 X_ATTRIBUTE12 IN VARCHAR2,
96 X_ATTRIBUTE13 IN VARCHAR2,
97 X_ATTRIBUTE14 IN VARCHAR2,
98 X_ATTRIBUTE15 IN VARCHAR2,
99 X_ORG_ID IN NUMBER
100 );
101
102 PROCEDURE DELETE_REPORT_DATE_ROW (
103 X_VAR_RENT_ID IN NUMBER,
104 X_END_DATE IN DATE
105 );
106
107
108 PROCEDURE INSERT_GRP_DATE_ROW (
109 X_ROWID IN OUT NOCOPY VARCHAR2,
110 X_GRP_DATE_ID IN OUT NOCOPY NUMBER,
111 X_VAR_RENT_ID IN NUMBER,
112 X_PERIOD_ID IN NUMBER,
113 X_GRP_START_DATE IN DATE,
114 X_GRP_END_DATE IN DATE,
115 X_GROUP_DATE IN DATE,
116 X_REPTG_DUE_DATE IN DATE,
117 X_INV_START_DATE IN DATE,
118 X_INV_END_DATE IN DATE,
119 X_INVOICE_DATE IN DATE,
120 X_INV_SCHEDULE_DATE IN DATE,
121 X_PRORATION_FACTOR IN NUMBER,
122 X_ACTUAL_EXP_CODE IN VARCHAR2,
123 X_FORECASTED_EXP_CODE IN VARCHAR2,
124 X_VARIANCE_EXP_CODE IN VARCHAR2,
125 X_CREATION_DATE IN DATE,
126 X_CREATED_BY IN NUMBER,
127 X_LAST_UPDATE_DATE IN DATE,
128 X_LAST_UPDATED_BY IN NUMBER,
129 X_LAST_UPDATE_LOGIN IN NUMBER,
130 X_ORG_ID NUMBER DEFAULT NULL
131 );
132
133 PROCEDURE DELETE_GRP_DATE_ROW(x_var_rent_id IN NUMBER,
134 x_term_date IN DATE DEFAULT NULL);
135
136 PROCEDURE CREATE_VAR_RENT_PERIODS(p_var_rent_id IN NUMBER,
137 p_cumulative_vol IN VARCHAR2 DEFAULT NULL,
138 p_comm_date IN DATE DEFAULT NULL,
139 p_term_date IN DATE DEFAULT NULL,
140 p_create_flag IN VARCHAR2 DEFAULT 'Y');
141
142 PROCEDURE CREATE_VAR_RENT_PERIODS_NOCAL(p_var_rent_id IN NUMBER,
143 p_cumulative_vol IN VARCHAR2,
144 p_yr_start_date IN DATE);
145
146 PROCEDURE DELETE_VAR_RENT_PERIODS(p_var_rent_id IN NUMBER,
147 p_term_date IN DATE DEFAULT NULL);
148
149 PROCEDURE UPDATE_VAR_RENT_PERIODS(p_var_rent_id IN NUMBER,
150 p_term_date IN DATE DEFAULT NULL);
151
152 PROCEDURE DELETE_VAR_RENT_CONSTR(p_var_rent_id IN NUMBER,
153 p_term_date IN DATE DEFAULT NULL);
154
155 PROCEDURE DELETE_VAR_RENT_LINES(p_var_rent_id IN NUMBER,
156 p_term_date IN DATE DEFAULT NULL);
157
158 PROCEDURE DELETE_VAR_VOL_HIST(p_line_item_id IN NUMBER);
159
160 PROCEDURE DELETE_VAR_RENT_DEDUCT(p_line_item_id IN NUMBER);
161
162 PROCEDURE DELETE_VAR_BKPTS_HEAD(p_line_item_id IN NUMBER);
163
164 PROCEDURE DELETE_VAR_BKPTS_DET(p_bkpt_header_id IN NUMBER);
165
166 FUNCTION find_if_period_exists (p_var_rent_id NUMBER)
167 RETURN NUMBER;
168
169 FUNCTION find_if_invoice_exists (p_var_rent_id NUMBER)
170 RETURN NUMBER;
171
172 PROCEDURE DELETE_VAR_RENT_INVOICES(p_var_rent_id IN NUMBER,
173 p_term_date IN DATE DEFAULT NULL);
174
175 FUNCTION find_if_calculation_exists (p_var_rent_id NUMBER)
176 RETURN NUMBER;
177
178 FUNCTION find_if_vrdates_exists (p_var_rent_id NUMBER)
179 RETURN NUMBER;
180
181 FUNCTION find_if_constr_exist (p_var_rent_id NUMBER,
182 p_term_date DATE DEFAULT NULL)
183 RETURN NUMBER;
184
185 FUNCTION find_if_lines_exist (p_var_rent_id NUMBER,
186 p_period_id NUMBER DEFAULT NULL,
187 p_term_date DATE DEFAULT NULL)
188 RETURN NUMBER;
189
190 FUNCTION find_if_volhist_exist ( p_line_item_id IN NUMBER )
191 RETURN NUMBER;
192
193 FUNCTION find_if_volhist_approved_exist ( p_line_item_id IN NUMBER
194 ,p_grp_date_id IN NUMBER )
195 RETURN VARCHAR2;
196
197 FUNCTION find_if_volhist_bkpts_exist ( p_id IN NUMBER,
198 p_id_type IN VARCHAR2 )
199 RETURN NUMBER;
200
201 FUNCTION find_if_deduct_exist (p_line_item_id NUMBER)
202 RETURN NUMBER;
203
204 PROCEDURE LOCK_ROW_EXCEPTION (p_column_name IN VARCHAR2,
205 p_new_value IN VARCHAR2);
206
207 FUNCTION First_Day ( p_Date Date )
208 RETURN DATE;
209
210 FUNCTION find_reporting_periods (p_period_id NUMBER)
211 RETURN NUMBER;
212
213 FUNCTION calculate_base_rent (p_var_rent_id NUMBER,
214 p_period_id NUMBER,
215 p_base_rent_type VARCHAR2)
216 RETURN NUMBER;
217
218 FUNCTION Get_Grp_date_Info ( p_var_rent_id IN NUMBER,
219 p_period_id IN NUMBER,
220 p_start_date IN DATE,
221 p_end_date IN DATE )
222 RETURN grp_date_info_rec;
223
224 FUNCTION Get_Proration_Factor ( p_var_rent_id IN NUMBER)
225 RETURN proration_factor_rec;
226
227 FUNCTION find_if_bkptshd_exist (p_line_item_id NUMBER)
228 RETURN NUMBER;
229
230 FUNCTION find_if_bkptsdet_exist (p_bkpt_header_id NUMBER)
231 RETURN NUMBER;
232
233 FUNCTION find_if_exported (p_id IN NUMBER,
234 p_block IN VARCHAR2,
235 p_start_dt IN DATE DEFAULT NULL,
236 p_end_dt IN DATE DEFAULT NULL)
237 RETURN NUMBER;
238 FUNCTION find_if_for_vol_exported (p_id IN NUMBER)
239 RETURN NUMBER;
240 FUNCTION find_status (p_period_id IN NUMBER)
241 RETURN VARCHAR2;
242
243 FUNCTION find_if_adjust_hist_exists (p_period_id IN NUMBER)
244 RETURN NUMBER;
245
246 FUNCTION approved_term_exist(p_var_rent_id NUMBER,p_period_id NUMBER DEFAULT NULL)
247 RETURN VARCHAR2 ;
248
249 PROCEDURE delete_inv_summ (p_var_rent_id IN NUMBER );
250
251 FUNCTION find_vol_ready_for_adjust(p_period_id number,p_invoice_on VARCHAR2)
252 RETURN NUMBER ;
253
254 PROCEDURE UPDATE_LOCATION_FOR_VR_TERMS(p_var_rent_id IN NUMBER,
255 p_location_id IN NUMBER,
256 p_return_status OUT NOCOPY VARCHAR2);
257
258 /*codev changes starts*/
259
260 FUNCTION dates_validation (p_var_rent_id IN NUMBER
261 ,p_period_id IN NUMBER
262 ,p_line_item_id IN NUMBER
263 ,p_check_for IN VARCHAR2
264 ,p_called_from IN VARCHAR2)
265 RETURN VARCHAR2;
266
267 FUNCTION constr_dates_validation (p_var_rent_id IN NUMBER
268 ,p_called_from IN VARCHAR2)
269 RETURN VARCHAR2;
270
271 FUNCTION find_reporting_periods (p_freq_code VARCHAR2)
272 RETURN NUMBER;
273
274 PROCEDURE extend_periods ( p_var_rent_id IN NUMBER,
275 p_extension_end_date IN DATE,
276 p_start_date IN DATE,
277 p_end_date IN DATE,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_return_message OUT NOCOPY VARCHAR2);
280
281 PROCEDURE extend_group_dates ( p_pn_var_rent_dates_rec IN PN_VAR_RENT_DATES_ALL%ROWTYPE,
282 p_period_id IN NUMBER,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_return_message OUT NOCOPY VARCHAR2);
285
286 PROCEDURE create_new_bkpts(p_var_rent_id IN NUMBER,
287 p_extension_end_date IN DATE,
288 p_old_end_date IN DATE,
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_return_message OUT NOCOPY VARCHAR2);
291
292 /*PROCEDURE process_vr_ext(p_lease_id IN NUMBER,
293 p_extension_end_date IN DATE,
294 p_old_end_date IN DATE,
295 p_var_rent_id IN NUMBER DEFAULT NULL,
296 x_return_status OUT VARCHAR2,
297 x_return_message OUT VARCHAR2);
298
299
300 PROCEDURE process_vr_early_term ( p_lease_id IN NUMBER DEFAULT NULL,
301 p_var_rent_id IN NUMBER DEFAULT NULL,
302 p_new_lease_term_date IN DATE,
303 p_old_end_date IN DATE,
304 x_return_status OUT VARCHAR2,
305 x_return_message OUT VARCHAR2);
306 */
307
308 PROCEDURE process_vr_ext (p_lease_id IN NUMBER
309 ,p_var_rent_id IN NUMBER
310 ,p_new_termn_date IN DATE
311 ,p_old_termn_date IN DATE
312 ,p_extend_setup IN VARCHAR2
313 ,x_return_status OUT NOCOPY VARCHAR2
314 ,x_return_message OUT NOCOPY VARCHAR2);
315
316
317 PROCEDURE process_vr_early_term ( p_lease_id IN NUMBER
318 ,p_var_rent_id IN NUMBER
319 ,p_new_termn_date IN DATE
320 ,p_old_termn_date IN DATE
321 ,x_return_status OUT NOCOPY VARCHAR2
322 ,x_return_message OUT NOCOPY VARCHAR2);
323
324 FUNCTION get_proration_rule( p_var_rent_id IN NUMBER DEFAULT NULL,
325 p_period_id IN NUMBER DEFAULT NULL)
326 RETURN VARCHAR2;
327
328 TYPE new_periods IS RECORD (
329 var_rent_id NUMBER,
330 period_id NUMBER,
331 start_date DATE,
332 end_date DATE,
333 flag VARCHAR2(1),
334 proration_days NUMBER
335 );
336 -- use 'N' for New period created and
337 -- 'U' for updating dates of existing period
338
339 TYPE new_periods_tbl IS TABLE OF new_periods INDEX BY BINARY_INTEGER;
340
341 TYPE cal_periods IS RECORD (
342 period_year NUMBER,
343 start_date DATE,
344 end_date DATE
345 );
346
347 TYPE cal_periods_tbl IS TABLE OF cal_periods INDEX BY BINARY_INTEGER;
348
349 TYPE group_dates_rec IS RECORD (
350 start_date DATE,
351 end_date DATE,
352 rec_found VARCHAR2(1)
356
353 );
354
355 TYPE group_dates_tbl IS TABLE OF group_dates_rec INDEX BY BINARY_INTEGER;
357 PROCEDURE generate_group_inv_tbl ( p_pn_var_rent_dates_rec IN pn_var_rent_dates_all%rowtype,
358 p_period_start_date IN DATE,
359 p_period_end_date IN DATE,
360 x_group_dates_tbl OUT NOCOPY group_dates_tbl,
361 x_inv_dates_tbl OUT NOCOPY group_dates_tbl);
362
363 PROCEDURE generate_cal_periods_tbl ( p_var_rent_dates_rec IN pn_var_rent_dates_all%rowtype,
364 p_start_date IN DATE,
365 p_end_date IN DATE,
366 p_extension_end_date IN DATE,
367 x_cal_periods_tbl OUT NOCOPY cal_periods_tbl);
368
369 /*
370 PROCEDURE remove_later_periods ( p_var_rent_id IN NUMBER,
371 p_lease_id IN NUMBER,
372 p_new_lease_term_date IN DATE,
373 p_old_end_date IN DATE,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_return_message OUT NOCOPY VARCHAR2) ;
376 */
377
378 PROCEDURE remove_later_periods ( p_var_rent_id IN NUMBER
379 , p_new_termn_date IN DATE
380 , p_old_termn_date IN DATE
381 , x_return_status OUT NOCOPY VARCHAR2
382 , x_return_message OUT NOCOPY VARCHAR2);
383
384 /*
385 PROCEDURE early_terminate_period ( p_var_rent_id IN NUMBER,
386 p_old_term_date IN DATE,
387 p_new_lease_term_date IN DATE,
388 x_period_id OUT NOCOPY NUMBER,
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_return_message OUT NOCOPY VARCHAR2);
391 */
392
393 PROCEDURE early_terminate_period ( p_var_rent_id IN NUMBER
394 ,p_period_id IN NUMBER
395 ,p_new_termn_date IN DATE
396 ,p_old_termn_date IN DATE
397 ,x_return_status OUT NOCOPY VARCHAR2
398 ,x_return_message OUT NOCOPY VARCHAR2);
399
400 FUNCTION exists_bkpt_dtldateintersect ( p_var_rent_id IN NUMBER,
401 p_line_default_id IN NUMBER,
402 p_start_date IN DATE,
403 p_end_date IN DATE)
404 RETURN BOOLEAN;
405
406 PROCEDURE check_continious_def_dates ( p_var_rent_id IN NUMBER,
407 p_line_default_id IN NUMBER,
408 p_start_date IN DATE,
409 p_end_date IN DATE,
410 x_return_status OUT NOCOPY BOOLEAN,
411 x_return_message OUT NOCOPY VARCHAR2,
412 x_date1 OUT NOCOPY DATE,
413 x_date2 OUT NOCOPY DATE);
414
415 PROCEDURE check_continious_def_dates ( p_var_rent_id IN NUMBER,
416 p_line_item_id IN NUMBER,
417 p_start_date IN DATE,
418 p_end_date IN DATE,
419 x_return_status OUT NOCOPY BOOLEAN,
420 x_return_message OUT NOCOPY VARCHAR2,
421 x_date1 OUT NOCOPY DATE,
422 x_date2 OUT NOCOPY DATE);
423
424 FUNCTION is_template_used ( p_template_id IN NUMBER)
425 RETURN BOOLEAN ;
426
427 FUNCTION FIND_IF_BKPTS_SETUP_EXISTS (p_var_rent_id NUMBER)
428 RETURN BOOLEAN;
429
430 PROCEDURE put_log(p_string VARCHAR2);
431
432 FUNCTION is_partial_period (p_period_id IN NUMBER)
433 RETURN VARCHAR2;
434
435 FUNCTION DETERMINE_FREQUENCY (
436 X_VAR_RENT_START_DATE IN PN_VAR_RENTS_ALL.COMMENCEMENT_DATE%TYPE
437 ,X_VAR_RENT_END_DATE IN PN_VAR_RENTS_ALL.TERMINATION_DATE%TYPE)
438 RETURN PN_VAR_RENT_DATES_ALL.REPTG_FREQ_CODE%TYPE;
439
440 PROCEDURE update_bkpt_details(p_var_rent_id IN NUMBER,
441 p_bkdt_dflt_id IN NUMBER,
442 p_bkpt_rate IN NUMBER);
443
444 PROCEDURE change_stratified_rows(p_bkhd_default_id IN NUMBER,
445 p_bkdt_st_date_old IN DATE,
446 p_bkdt_end_date_old IN DATE,
447 p_bkdt_default_id IN NUMBER,
448 p_bkdt_st_date IN DATE,
449 p_bkdt_end_date IN DATE);
450
451 PROCEDURE process_vr_exp_con (errbuf OUT NOCOPY VARCHAR2,
452 retcode OUT NOCOPY VARCHAR2,
453 p_lease_id NUMBER,
454 p_lease_change_id NUMBER DEFAULT NULL,
455 p_old_term_date VARCHAR2,
456 p_new_term_date VARCHAR2,
457 p_vr_context VARCHAR2,
458 p_setup_exp_context VARCHAR2 DEFAULT NULL,
462
459 p_rollover VARCHAR2 DEFAULT NULL,
460 p_request_id NUMBER DEFAULT NULL);
461
463 PROCEDURE copy_bkpt_main_to_setup (errbuf OUT NOCOPY VARCHAR2,
464 retcode OUT NOCOPY VARCHAR2,
465 p_prop_id IN NUMBER,
466 p_loc_id IN NUMBER,
467 p_lease_id IN NUMBER,
468 p_var_rent_id IN NUMBER);
469
470 FUNCTION find_if_inv_exp (p_var_rent_id NUMBER, p_invoice_date DATE) RETURN NUMBER;
471
472 FUNCTION rates_validation (p_var_rent_id IN NUMBER
473 ,p_agr_start_date IN DATE
474 ,p_agr_end_date IN DATE) RETURN VARCHAR2;
475 FUNCTION FIND_IF_ABAT_DEF_EXIST (p_var_rent_id NUMBER)
476 RETURN NUMBER ;
477 FUNCTION FIND_IF_CONSTR_DEF_EXIST (p_var_rent_id NUMBER)
478 RETURN NUMBER ;
479
480 END PN_VAR_RENT_PKG;
[Home] [Help]