1 package PN_REC_CALC_PKG as
2 /* $Header: PNRECALS.pls 120.0.12010000.2 2008/09/04 12:26:31 mumohan ship $ */
3
4 g_rec_agr_line_id pn_rec_agr_linconst_all.rec_agr_line_id%TYPE;
5 g_calc_period_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE;
6
7 g_ext_precision number;
8 g_min_acct_unit number;
9 g_currency_code gl_sets_of_books.currency_code%type;
10 g_precision number;
11
12 TYPE ten_recoverable_area_rec IS RECORD (
13 occupied_area PN_REC_ARCL_DTLLN_ALL.occupied_area%TYPE
14 ,occupancy_pct PN_REC_ARCL_DTLLN_ALL.occupancy_pct%TYPE
15 );
16
17 TYPE period_bill_record IS RECORD (
18 period_billrec_id PN_REC_PERIOD_BILL_ALL.period_billrec_id%TYPE
19 ,amount PN_REC_PERIOD_BILL_ALL.amount%TYPE
20 );
21
22 TYPE expenses_record IS RECORD (
23 computed_recoverable_amt PN_REC_EXPCL_DTLLN_ALL.computed_recoverable_amt%TYPE
24 ,budgeted_amt PN_REC_EXPCL_DTLLN_ALL.computed_recoverable_amt%TYPE
25 );
26
27
28 CURSOR get_line_constr_csr IS
29 SELECT constr_order
30 ,const.scope
31 ,const.relation
32 ,const.value
33 ,const.cpi_index
34 ,const.base_year
35 FROM pn_rec_agr_linconst_all const
36 WHERE const.rec_agr_line_id = g_rec_agr_line_id
37 AND g_calc_period_as_of_date between const.start_date
38 AND const.end_date
39 ORDER BY const.constr_order ;
40
41 TYPE g_line_constr_type IS
42 TABLE OF get_line_constr_csr%ROWTYPE
43 INDEX BY BINARY_INTEGER;
44
45
46 g_line_success VARCHAR2(30);
47 g_all_lines_success VARCHAR2(30);
48
49 PROCEDURE CALCULATE_REC_AMOUNT_BATCH(
50 errbuf OUT NOCOPY VARCHAR2
51 ,retcode OUT NOCOPY VARCHAR2
52 ,p_rec_agreement_id IN NUMBER
53 ,p_lease_id IN NUMBER
54 ,p_location_id IN NUMBER
55 ,p_customer_id IN NUMBER
56 ,p_cust_site_id IN NUMBER
57 ,p_rec_agr_line_id IN NUMBER DEFAULT NULL
58 ,p_rec_calc_period_id IN NUMBER DEFAULT NULL
59 ,p_calc_period_startdate IN VARCHAR2
60 ,p_calc_period_enddate IN VARCHAR2
61 ,p_as_ofdate IN VARCHAR2
62 ,p_lease_num_from IN VARCHAR2
63 ,p_lease_num_to IN VARCHAR2
64 ,p_location_code_from IN VARCHAR2
65 ,p_location_code_to IN VARCHAR2
66 ,p_rec_agr_num_from IN VARCHAR2
67 ,p_rec_agr_num_to IN VARCHAR2
68 ,p_property_name IN VARCHAR2
69 ,p_customer_name IN VARCHAR2
70 ,p_customer_site IN VARCHAR2
71 ,p_calc_period_ending IN VARCHAR2
72 ,p_org_id IN NUMBER DEFAULT NULL
73 );
74
75 -- Created an overloaded proc to fix bug 3138335
76
77 PROCEDURE CALCULATE_REC_AMOUNT_BATCH(
78 errbuf OUT NOCOPY VARCHAR2
79 ,retcode OUT NOCOPY VARCHAR2
80 ,p_calc_period_startdate IN VARCHAR2 --Bug#6438840
81 ,p_calc_period_enddate IN VARCHAR2 --Bug#6438840
82 ,p_as_ofdate IN VARCHAR2 --Bug#6438840
83 ,p_lease_num_from IN VARCHAR2
84 ,p_lease_num_to IN VARCHAR2
85 ,p_location_code_from IN VARCHAR2
86 ,p_location_code_to IN VARCHAR2
87 ,p_rec_agr_num_from IN VARCHAR2
88 ,p_rec_agr_num_to IN VARCHAR2
89 ,p_property_name IN VARCHAR2
90 ,p_customer_name IN VARCHAR2
91 ,p_customer_site IN VARCHAR2
92 ,p_calc_period_ending IN VARCHAR2
93 ,p_org_id IN NUMBER DEFAULT NULL
94 );
95
96 PROCEDURE CALCULATE_REC_AMOUNT(
97 p_rec_agreement_id IN NUMBER
98 ,p_lease_id IN NUMBER
99 ,p_location_id IN NUMBER
100 ,p_customer_id IN NUMBER
101 ,p_cust_site_id IN NUMBER
102 ,p_rec_agr_line_id IN NUMBER DEFAULT NULL
103 ,p_rec_calc_period_id IN NUMBER DEFAULT NULL
104 ,p_calc_period_start_date IN DATE
105 ,p_calc_period_end_date IN DATE
106 ,p_as_of_date IN DATE
107 ,p_error IN OUT NOCOPY VARCHAR2
108 ,p_error_code IN OUT NOCOPY NUMBER
109 );
110
111 FUNCTION get_recoverable_area (
112 p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
113 ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
114 )
115 RETURN pn_rec_period_lines_all.recoverable_area%TYPE;
116
117 PROCEDURE get_line_expenses (
118 p_rec_agr_line_id IN NUMBER
119 ,p_customer_id IN NUMBER
120 ,p_lease_id IN NUMBER
121 ,p_location_id IN NUMBER
122 ,p_calc_period_start_date IN DATE
123 ,p_calc_period_end_date IN DATE
124 ,p_calc_period_as_of_date IN DATE
125 ,p_recoverable_amt IN OUT NOCOPY NUMBER
126 ,p_fee_before_contr IN OUT NOCOPY NUMBER
127 ,p_fee_after_contr IN OUT NOCOPY NUMBER
128 ,p_error IN OUT NOCOPY VARCHAR2
129 ,p_error_code IN OUT NOCOPY NUMBER
130 );
131
132 FUNCTION get_contr_actual_recovery (
133 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
134 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
135 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
136 ,p_location_id pn_rec_agreements_all.location_id%TYPE
137 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
138 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
139 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
140 ,p_called_from VARCHAR2 DEFAULT 'CALCUI'
141 )
142 RETURN pn_rec_period_lines_all.actual_recovery%TYPE;
143
144 FUNCTION get_budget_expenses (
145 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
146 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
147 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
148 ,p_location_id pn_rec_agreements_all.location_id%TYPE
149 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
150 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
151 ,p_calc_period_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
152 )
153 RETURN pn_rec_expcl_dtlln_all.budgeted_amt%TYPE;
154
155 FUNCTION get_tot_prop_area (
156 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
157 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
158 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
159 ,p_location_id pn_rec_agreements_all.location_id%TYPE
160 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
161 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
162 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
163 )
164 RETURN pn_rec_arcl_dtl_all.TOTAL_assignable_area%TYPE;
165
166 FUNCTION ten_recoverable_area (
167 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
168 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
169 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
170 ,p_location_id pn_rec_agreements_all.location_id%TYPE
171 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
172 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
173 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
174 )
175 RETURN ten_recoverable_area_rec;
176
177 -- 04-Nov-2003 Daniel Thota o Changed the where clause to account for multi-tenancy
178 -- so that billing terms of a lease are now associated with a location.
179 -- Added a new parameter p_location_id for the function
180
181 FUNCTION get_billed_recovery (
182 p_payment_purpose pn_rec_agr_lines_all.purpose%TYPE
183 ,p_payment_type pn_rec_agr_lines_all.type%TYPE
184 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
185 ,p_location_id pn_rec_agreements_all.location_id%TYPE
186 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
187 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
188 ,p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
189 ,p_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE
190 )
191 RETURN pn_rec_period_lines_all.billed_recovery%TYPE;
192
193 FUNCTION get_line_constraints (
194 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
195 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
196 )
197 RETURN g_line_constr_type;
198
199 FUNCTION get_line_abatements (
200 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
201 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
202 )
203 RETURN pn_rec_agr_linabat_all.amount%TYPE;
204
205 FUNCTION find_if_period_line_exists (
206 p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
207 ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
208 )
209 RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE;
210
211 procedure INSERT_PERIOD_LINES_ROW (
212 X_ROWID in out NOCOPY VARCHAR2
213 ,X_REC_PERIOD_LINES_ID in out NOCOPY NUMBER
214 ,X_BUDGET_PCT in NUMBER
215 ,X_OCCUPANCY_PCT in NUMBER
216 ,X_MULTIPLE_PCT in NUMBER
217 ,X_TENANCY_START_DATE in DATE
218 ,X_TENANCY_END_DATE in DATE
219 ,X_STATUS in VARCHAR2
220 ,X_BUDGET_PRORATA_SHARE in NUMBER
221 ,X_BUDGET_COST_PER_AREA in NUMBER
222 ,X_TOTAL_AREA in NUMBER
223 ,X_TOTAL_EXPENSE in NUMBER
224 ,X_RECOVERABLE_AREA in NUMBER
225 ,X_ACTUAL_RECOVERY in NUMBER
226 ,X_CONSTRAINED_ACTUAL in NUMBER
227 ,X_ABATEMENTS in NUMBER
228 ,X_ACTUAL_PRORATA_SHARE in NUMBER
229 ,X_BILLED_RECOVERY in NUMBER
230 ,X_RECONCILED_AMOUNT in NUMBER
231 ,X_BUDGET_RECOVERY in NUMBER
232 ,X_BUDGET_EXPENSE in NUMBER
233 ,X_REC_CALC_PERIOD_ID in NUMBER
234 ,X_REC_AGR_LINE_ID in NUMBER
235 ,X_AS_OF_DATE in DATE
236 ,X_START_DATE in DATE
237 ,X_END_DATE in DATE
238 ,X_BILLING_TYPE in VARCHAR2
239 ,X_BILLING_PURPOSE in VARCHAR2
240 ,X_CUST_ACCOUNT_ID in NUMBER
241 ,X_CREATION_DATE in DATE
242 ,X_CREATED_BY in NUMBER
243 ,X_LAST_UPDATE_DATE in DATE
244 ,X_LAST_UPDATED_BY in NUMBER
245 ,X_LAST_UPDATE_LOGIN in NUMBER
246 ,X_FIXED_PCT in NUMBER
247 ,X_ERROR_CODE in out NOCOPY NUMBER
248 );
249
250 procedure UPDATE_PERIOD_LINES_ROW(
251 X_REC_PERIOD_LINES_ID in NUMBER
252 ,X_BUDGET_PCT in NUMBER
253 ,X_OCCUPANCY_PCT in NUMBER
254 ,X_MULTIPLE_PCT in NUMBER
255 ,X_TENANCY_START_DATE in DATE
256 ,X_TENANCY_END_DATE in DATE
257 ,X_STATUS in VARCHAR2
258 ,X_BUDGET_PRORATA_SHARE in NUMBER
259 ,X_BUDGET_COST_PER_AREA in NUMBER
260 ,X_TOTAL_AREA in NUMBER
261 ,X_TOTAL_EXPENSE in NUMBER
262 ,X_RECOVERABLE_AREA in NUMBER
263 ,X_ACTUAL_RECOVERY in NUMBER
264 ,X_CONSTRAINED_ACTUAL in NUMBER
265 ,X_ABATEMENTS in NUMBER
266 ,X_ACTUAL_PRORATA_SHARE in NUMBER
267 ,X_BILLED_RECOVERY in NUMBER
268 ,X_RECONCILED_AMOUNT in NUMBER
269 ,X_BUDGET_RECOVERY in NUMBER
270 ,X_BUDGET_EXPENSE in NUMBER
271 ,X_REC_CALC_PERIOD_ID in NUMBER
272 ,X_REC_AGR_LINE_ID in NUMBER
273 ,X_AS_OF_DATE in DATE
274 ,X_START_DATE in DATE
275 ,X_END_DATE in DATE
276 ,X_BILLING_TYPE in VARCHAR2
277 ,X_BILLING_PURPOSE in VARCHAR2
278 ,X_CUST_ACCOUNT_ID in NUMBER
279 ,X_LAST_UPDATE_DATE in DATE
280 ,X_LAST_UPDATED_BY in NUMBER
281 ,X_LAST_UPDATE_LOGIN in NUMBER
282 ,X_FIXED_PCT in NUMBER
283 ,X_ERROR_CODE in out NOCOPY NUMBER
284 );
285
286 procedure DELETE_PERIOD_LINES_ROW (
287 X_REC_PERIOD_LINES_ID in NUMBER
288 );
289
290 procedure INSERT_PERIOD_BILLREC_ROW (
291 X_ROWID in out NOCOPY VARCHAR2
292 ,X_PERIOD_BILLREC_ID in out NOCOPY NUMBER
293 ,X_REC_AGREEMENT_ID in NUMBER
294 ,X_REC_AGR_LINE_ID in NUMBER
295 ,X_REC_CALC_PERIOD_ID in NUMBER
296 ,X_AMOUNT in NUMBER
297 ,X_CREATION_DATE in DATE
298 ,X_CREATED_BY in NUMBER
299 ,X_LAST_UPDATE_DATE in DATE
300 ,X_LAST_UPDATED_BY in NUMBER
301 ,X_LAST_UPDATE_LOGIN in NUMBER
302 );
303
304 procedure UPDATE_PERIOD_BILLREC_ROW (
305 X_PERIOD_BILLREC_ID in NUMBER
306 ,X_REC_AGREEMENT_ID in NUMBER
307 ,X_REC_AGR_LINE_ID in NUMBER
308 ,X_REC_CALC_PERIOD_ID in NUMBER
309 ,X_AMOUNT in NUMBER
310 ,X_LAST_UPDATE_DATE in DATE
311 ,X_LAST_UPDATED_BY in NUMBER
312 ,X_LAST_UPDATE_LOGIN in NUMBER
313 );
314
315 procedure DELETE_PERIOD_BILLREC_ROW (
316 X_PERIOD_BILLREC_ID in NUMBER
317 );
318
319 PROCEDURE create_payment_terms(
320 p_lease_id IN NUMBER
321 ,p_payment_amount IN NUMBER
322 ,p_calc_period_end_date IN DATE
323 ,p_rec_agreement_id IN NUMBER
324 ,p_rec_agr_line_id IN NUMBER
325 ,p_rec_calc_period_id IN NUMBER
326 ,p_location_id IN NUMBER
327 ,p_amount_type IN VARCHAR2
328 ,p_org_id IN NUMBER
329 ,p_billing_type IN VARCHAR2
330 ,p_billing_purpose IN VARCHAR2
331 ,p_customer_id IN NUMBER
332 ,p_cust_site_id IN NUMBER
333 ,p_consolidate IN VARCHAR2
334 ,p_error IN OUT NOCOPY VARCHAR2
335 ,p_error_code IN OUT NOCOPY NUMBER
336 );
337
338 FUNCTION find_if_rec_payterm_exists(
339 p_rec_agreement_id PN_REC_PERIOD_BILL_all.period_billrec_id%TYPE
340 ,p_rec_agr_line_id PN_REC_PERIOD_BILL_all.rec_agr_line_id%TYPE
341 ,p_rec_calc_period_id PN_REC_PERIOD_BILL_all.rec_calc_period_id%TYPE
342 ,p_consolidate IN VARCHAR2
343 )
344 RETURN period_bill_record;
345
346 FUNCTION get_prior_period_actual_amount(
347 p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
348 ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
349 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE DEFAULT NULL
350 ,p_called_from VARCHAR2 DEFAULT 'CALCUI'
351 )
352 RETURN pn_rec_period_lines_all.constrained_actual%TYPE;
353
354 FUNCTION get_prior_period_cap(
355 p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
356 ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
357 ,p_end_date pn_rec_calc_periods_all.end_date%TYPE
358 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
359 ,p_called_from VARCHAR2 DEFAULT 'CALCUI'
360 )
361 RETURN pn_rec_period_lines_all.actual_recovery%TYPE;
362
363 PROCEDURE lock_area_exp_cls_dtl( p_payment_term_id IN pn_payment_terms_all.payment_term_id%TYPE);
364
365 FUNCTION validate_create_calc_period(p_rec_agreement_id pn_rec_agreements_all.REC_AGREEMENT_ID%TYPE,
366 p_start_date pn_rec_calc_periods_all.start_date%TYPE,
367 p_end_date pn_rec_calc_periods_all.end_date%TYPE,
368 p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE)
369 RETURN NUMBER;
370
371 END PN_REC_CALC_PKG;
[Home] [Help]