[Home] [Help]
PACKAGE BODY: APPS.PN_REC_CALC_PKG
Source
1 package body PN_REC_CALC_PKG as
2 /* $Header: PNRECALB.pls 120.9.12010000.3 2008/12/30 12:44:32 acprakas ship $ */
3
4 /*===========================================================================+
5 | PROCEDURE
6 | CALCULATE_REC_AMOUNT_BATCH
7 |
8 | DESCRIPTION
9 | Calculate recovery amount for a recovery agreement(s)
10 |
11 | SCOPE - PUBLIC
12 |
13 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
14 |
15 | ARGUMENTS : IN:
16 | p_rec_agreement_id
17 | p_lease_id
18 | p_location_id
19 | p_customer_id
20 | p_cust_site_id
21 | p_rec_agr_line_id
22 | p_rec_calc_period_id
23 | p_calc_period_start_date
24 | p_calc_period_end_date
25 | P_as_of_date
26 |
27 | OUT:
28 |
29 | RETURNS : None
30 |
31 | NOTES : Calculate recovery amount for a recovery agreement(s)
32 |
33 | MODIFICATION HISTORY
34 |
35 | 07-APR-03 Daniel Thota o Created
36 | 29-JUN-06 Hareesha o Bug #5356744 Used canonical_to_date to convert
37 | the dates,input parameters to DATE type
38 | to avoid clash with ICX:date format
39 | and other user date formats.
40 | 18-JUL-06 sdmahesh o Bug 5332426 Added handling for lazy upgrade
41 | of Term Templates for E-Tax
42 +===========================================================================*/
43
44 TYPE template_name_tbl_type IS TABLE OF pn_term_templates_all.name%TYPE INDEX BY BINARY_INTEGER;
45 TYPE template_id_tbl_type IS TABLE OF pn_term_templates_all.term_template_id%TYPE INDEX BY BINARY_INTEGER;
46 template_name_tbl template_name_tbl_type;
47 template_id_tbl template_id_tbl_type;
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
73 ) IS
74
75 l_rec_agreement_id pn_rec_agreements_all.rec_agreement_id%TYPE := NULL;
76 l_lease_id pn_rec_agreements_all.lease_id%TYPE := NULL;
77 l_location_id pn_rec_agreements_all.location_id%TYPE := NULL;
78 l_customer_id pn_rec_agreements_all.customer_id%TYPE := NULL;
79 l_cust_site_id pn_rec_agreements_all.cust_site_id%TYPE := NULL;
80
81 l_start_date pn_rec_calc_periods_all.start_date%TYPE;
82 l_end_date pn_rec_calc_periods_all.end_date%TYPE ;
83 l_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE ;
84 l_calc_period_ending pn_rec_calc_periods_all.end_date%TYPE :=
85 trunc(fnd_date.canonical_to_date(p_calc_period_ending));
86
87 l_error VARCHAR2(2000) := 'Success';
88 l_error_code NUMBER := 0;
89
90 CURSOR csr_get_agr IS
91 SELECT rec_agreement_num,
92 rec_agreement_name
93 FROM pn_rec_agreements_all
94 WHERE rec_agreement_id = p_rec_agreement_id;
95
96 CURSOR csr_get_lease IS
97 SELECT name,
98 lease_num
99 FROM pn_leases_all
100 WHERE lease_id = p_lease_id;
101
102 CURSOR csr_get_location IS
103 SELECT location_code
104 FROM pn_locations_all
105 WHERE location_id = p_location_id
106 AND NVL(l_as_of_date,sysdate) between active_start_date and
107 active_end_date;
108
109 CURSOR calc_rec_amount_wloc IS
110 SELECT pra.rec_agreement_id
111 ,pra.lease_id
112 ,pra.location_id
113 ,pra.customer_id
114 ,pra.cust_site_id
115 ,prc.start_date
116 ,prc.end_date
117 ,prc.as_of_date
118 ,prc.rec_calc_period_id
119 FROM pn_leases pl
120 ,pn_rec_agreements_all pra
121 ,pn_rec_calc_periods_all prc
122 ,pn_locations_all ploc
123 WHERE pl.lease_id = pra.lease_id
124 AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
125 AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
126 AND pra.rec_agreement_id = prc.rec_agreement_id
127 AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
128 AND ploc.location_id = pra.location_id
129 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
130 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
131 AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
132 AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
133 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
134 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
135 AND (pl.org_id = p_org_id or p_org_id is null)
136 ORDER BY pl.lease_id, pra.rec_agreement_id
137 ;
138
139 CURSOR calc_rec_amount_woloc IS
140 SELECT pra.rec_agreement_id
141 ,pra.lease_id
142 ,pra.location_id
143 ,pra.customer_id
144 ,pra.cust_site_id
145 ,prc.start_date
146 ,prc.end_date
147 ,prc.as_of_date
148 ,prc.rec_calc_period_id
149 FROM pn_leases pl
150 ,pn_rec_agreements_all pra
151 ,pn_rec_calc_periods_all prc
152 WHERE pl.lease_id = pra.lease_id
153 AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
154 AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
155 AND pra.rec_agreement_id = prc.rec_agreement_id
156 AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
157 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
158 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
159 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
160 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
161 AND (pl.org_id = p_org_id or p_org_id is null)
162 ORDER BY pl.lease_id, pra.rec_agreement_id
163 ;
164
165 CURSOR calc_rec_amount_wloc_prop IS
166 SELECT pra.rec_agreement_id
167 ,pra.lease_id
168 ,pra.location_id
169 ,pra.customer_id
170 ,pra.cust_site_id
171 ,prc.start_date
172 ,prc.end_date
173 ,prc.as_of_date
174 ,prc.rec_calc_period_id
175 FROM pn_leases pl
176 ,pn_rec_agreements_all pra
177 ,pn_rec_calc_periods_all prc
178 ,pn_locations_all ploc
179 ,pn_properties_all prop
180 WHERE pl.lease_id = pra.lease_id
181 AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
182 AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
183 AND pra.rec_agreement_id = prc.rec_agreement_id
184 AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
185 AND ploc.location_id = pra.location_id
186 AND ploc.property_id = prop.property_id
187 AND prop.property_code = p_property_name
188 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
189 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
190 AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
191 AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
192 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
193 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
194 AND (pl.org_id = p_org_id or p_org_id is null)
195 ORDER BY pl.lease_id, pra.rec_agreement_id
196 ;
197
198 CURSOR calc_rec_amount_woloc_prop IS
199 SELECT pra.rec_agreement_id
200 ,pra.lease_id
201 ,pra.location_id
202 ,pra.customer_id
203 ,pra.cust_site_id
204 ,prc.start_date
205 ,prc.end_date
206 ,prc.as_of_date
207 ,prc.rec_calc_period_id
208 FROM pn_leases pl
209 ,pn_rec_agreements_all pra
210 ,pn_rec_calc_periods_all prc
211 ,pn_locations_all ploc
212 ,pn_properties_all prop
213 WHERE pl.lease_id = pra.lease_id
214 AND pra.customer_id = nvl(p_customer_id,pra.customer_id)
215 AND pra.cust_site_id = nvl(p_cust_site_id,pra.cust_site_id)
216 AND pra.rec_agreement_id = prc.rec_agreement_id
217 AND prc.end_date <= nvl(l_calc_period_ending,prc.end_date)
218 AND ploc.location_id = pra.location_id
219 AND ploc.property_id = prop.property_id
220 AND prop.property_code = p_property_name
221 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
222 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
223 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
224 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
225 AND (pl.org_id = p_org_id or p_org_id is null)
226 ORDER BY pl.lease_id, pra.rec_agreement_id
227 ;
228
229 l_processed NUMBER := 0;
230 l_success_count NUMBER := 0;
231 l_fail_count NUMBER := 0;
232 l_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE;
233
234 BEGIN
235
236 pnp_debug_pkg.log('PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT_BATCH (+) ');
237
238 l_start_date := TRUNC(fnd_date.canonical_to_date(p_calc_period_startdate));
239 l_end_date := TRUNC(fnd_date.canonical_to_date(p_calc_period_enddate));
240 l_as_of_date := TRUNC(fnd_date.canonical_to_date(p_as_ofdate));
241
242 fnd_message.set_name ('PN','PN_RECALC_AGR_BATCH_INP_PARAM');
243 fnd_message.set_token ('AGR_ID',p_rec_agreement_id);
244 fnd_message.set_token ('LEASE_ID',l_lease_id);
245 fnd_message.set_token ('LOC_ID',p_location_id);
246 fnd_message.set_token ('LINE_ID',p_rec_agr_line_id);
247 fnd_message.set_token ('PRD_ID',p_rec_calc_period_id);
248 fnd_message.set_token ('CUST_ID',p_customer_id);
249 fnd_message.set_token ('SITE_ID',p_cust_site_id);
250 fnd_message.set_token ('ST_DATE',l_start_date);
251 fnd_message.set_token ('END_DT',l_end_date);
252 fnd_message.set_token ('AS_DATE',l_as_of_date);
253 fnd_message.set_token ('LSNO_FRM',p_lease_num_from);
254 fnd_message.set_token ('LSNO_TO',p_lease_num_to);
255 fnd_message.set_token ('LOC_FRM',p_location_code_from);
256 fnd_message.set_token ('LOC_TO',p_location_code_to);
257 fnd_message.set_token ('REC_FRM',p_rec_agr_num_from);
258 fnd_message.set_token ('REC_TO',p_rec_agr_num_to);
259 fnd_message.set_token ('PROP_NAME',p_property_name);
260 fnd_message.set_token ('CUST_NAME',p_customer_name);
261 fnd_message.set_token ('CUST_SITE',p_customer_site);
262 fnd_message.set_token ('PRD_END',l_calc_period_ending);
263 pnp_debug_pkg.put_log_msg(fnd_message.get);
264
265 /* PL/SQL Tables to cache term templates which are lazy upgraded for E-Tax*/
266 template_name_tbl.DELETE;
267 template_id_tbl.DELETE;
268
269
270 /* if p_org_ID is not null, then set.
271 else if in R12, current org is already set
272 */
273 IF p_org_id is NOT NULL THEN
274 pn_mo_cache_utils.fnd_req_set_org_id (p_org_id);
275 /* uncomment to debug
276 pnp_debug_pkg.log('Set the org id with value:' || to_char(pn_mo_cache_utils.get_current_org_id)); */
277 END IF;
278
279 IF ((p_rec_agreement_id IS NOT NULL)
280 or (p_lease_id IS NOT NULL)
281 or (p_location_id IS NOT NULL)
282 or (p_rec_agr_line_id IS NOT NULL)
283 or (p_rec_calc_period_id IS NOT NULL)
284 or (p_calc_period_startdate IS NOT NULL)
285 or (p_calc_period_enddate IS NOT NULL)
286 or (p_as_ofdate IS NOT NULL)
287 ) THEN
288
289 l_processed := l_processed + 1;
290
291 PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT(
292 p_rec_agreement_id => p_rec_agreement_id
293 ,p_lease_id => p_lease_id
294 ,p_location_id => p_location_id
295 ,p_customer_id => p_customer_id
296 ,p_cust_site_id => p_cust_site_id
297 ,p_rec_agr_line_id => p_rec_agr_line_id
298 ,p_rec_calc_period_id => p_rec_calc_period_id
299 ,p_calc_period_start_date => l_start_date
300 ,p_calc_period_end_date => l_end_date
301 ,p_as_of_date => l_as_of_date
302 ,p_error => l_error
303 ,p_error_code => l_error_code
304 );
305
306 IF nvl(l_error_code,0) <> -99 THEN
307
308 l_success_count := l_success_count + 1;
309 ELSE
310 l_fail_count := l_fail_count + 1;
311
312 END IF;
313
314 ELSE
315
316 IF p_location_code_from IS NOT NULL or p_location_code_to IS NOT NULL THEN
317 IF p_property_name IS NOT NULL THEN
318 OPEN calc_rec_amount_wloc_prop;
319 ELSE
320 OPEN calc_rec_amount_wloc;
321 END IF;
322 ELSE
323 IF p_property_name IS NOT NULL THEN
324 OPEN calc_rec_amount_woloc_prop;
325 ELSE
326 OPEN calc_rec_amount_woloc;
327 END IF;
328 END IF;
329
330 LOOP
331
332 IF calc_rec_amount_wloc_prop%ISOPEN THEN
333 FETCH calc_rec_amount_wloc_prop INTO
334 l_rec_agreement_id
335 ,l_lease_id
336 ,l_location_id
337 ,l_customer_id
338 ,l_cust_site_id
339 ,l_start_date
340 ,l_end_date
341 ,l_as_of_date
342 ,l_rec_calc_period_id;
343 EXIT WHEN calc_rec_amount_wloc_prop%NOTFOUND;
344 ELSIF calc_rec_amount_wloc%ISOPEN THEN
345 FETCH calc_rec_amount_wloc INTO
346 l_rec_agreement_id
347 ,l_lease_id
348 ,l_location_id
349 ,l_customer_id
350 ,l_cust_site_id
351 ,l_start_date
352 ,l_end_date
353 ,l_as_of_date
354 ,l_rec_calc_period_id;
355 EXIT WHEN calc_rec_amount_wloc%NOTFOUND;
356 ELSIF calc_rec_amount_woloc_prop%ISOPEN THEN
357 FETCH calc_rec_amount_woloc_prop INTO
358 l_rec_agreement_id
359 ,l_lease_id
360 ,l_location_id
361 ,l_customer_id
362 ,l_cust_site_id
363 ,l_start_date
364 ,l_end_date
365 ,l_as_of_date
366 ,l_rec_calc_period_id;
367 EXIT WHEN calc_rec_amount_woloc_prop%NOTFOUND;
368 ELSIF calc_rec_amount_woloc%ISOPEN THEN
369 FETCH calc_rec_amount_woloc INTO
370 l_rec_agreement_id
371 ,l_lease_id
372 ,l_location_id
373 ,l_customer_id
374 ,l_cust_site_id
375 ,l_start_date
376 ,l_end_date
377 ,l_as_of_date
378 ,l_rec_calc_period_id;
379 EXIT WHEN calc_rec_amount_woloc%NOTFOUND;
380 END IF;
381
382 l_error := 'Success';
383 l_error_code := null;
384 l_processed := l_processed + 1;
385
386 PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT(
387 p_rec_agreement_id => l_rec_agreement_id
388 ,p_lease_id => l_lease_id
389 ,p_location_id => l_location_id
390 ,p_customer_id => l_customer_id
391 ,p_cust_site_id => l_cust_site_id
392 ,p_rec_agr_line_id => NULL
393 ,p_rec_calc_period_id => l_rec_calc_period_id
394 ,p_calc_period_start_date => l_start_date
395 ,p_calc_period_end_date => l_end_date
396 ,p_as_of_date => l_as_of_date
397 ,p_error => l_error
398 ,p_error_code => l_error_code
399 );
400
401 IF nvl(l_error_code,0) <> -99 THEN
402
403 l_success_count := l_success_count + 1;
404 ELSE
405 l_fail_count := l_fail_count + 1;
406
407 END IF;
408
409 END LOOP;
410
411 fnd_message.set_name ('PN','PN_RECALC_AGR_PROC');
412 fnd_message.set_token ('NUM', l_processed);
413 pnp_debug_pkg.put_log_msg(fnd_message.get);
414
415 fnd_message.set_name ('PN','PN_RECALC_AGR_SUC');
416 fnd_message.set_token ('NUM', l_success_count);
417 pnp_debug_pkg.put_log_msg(fnd_message.get);
418
419 fnd_message.set_name ('PN','PN_RECALC_AGR_FAIL');
420 fnd_message.set_token ('NUM', l_fail_count);
421 pnp_debug_pkg.put_log_msg(fnd_message.get);
422
423 IF calc_rec_amount_wloc_prop%ISOPEN THEN
424 CLOSE calc_rec_amount_wloc_prop;
425 ELSIF calc_rec_amount_wloc%ISOPEN THEN
426 CLOSE calc_rec_amount_wloc;
427 ELSIF calc_rec_amount_woloc_prop%ISOPEN THEN
428 CLOSE calc_rec_amount_woloc_prop;
429 ELSIF calc_rec_amount_woloc%ISOPEN THEN
430 CLOSE calc_rec_amount_woloc;
431 END IF;
432
433
434 END IF;
435
436 /*Logging information for upgraded Term Templates*/
437 FOR i IN 1 .. template_id_tbl.COUNT LOOP
438 pnp_debug_pkg.put_log_msg('Term template '||template_name_tbl(i)||
439 ' has an existing tax code or tax group.A corresponding tax classification will replace it');
440 END LOOP;
441
442 pnp_debug_pkg.log('PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT_BATCH (-) ');
443
444 EXCEPTION
445
446 When OTHERS Then
447 pnp_debug_pkg.log('Error in PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT_BATCH :'||to_char(sqlcode)||' : '||sqlerrm);
448 Errbuf := SQLERRM;
449 Retcode := 2;
450 rollback;
451 raise;
452
453
454 END CALCULATE_REC_AMOUNT_BATCH;
455
456 /*=============================================================================+
457 | PROCEDURE
458 | CALCULATE_REC_AMOUNT_BATCH
459 |
460 | DESCRIPTION
461 | Calculate recovery amount for a recovery agreement(s)
462 |
463 | SCOPE - PUBLIC
464 |
465 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
466 |
467 | ARGUMENTS : IN:
468 | p_rec_agreement_id
469 | p_lease_id
470 | p_location_id
471 | p_customer_id
472 | p_cust_site_id
473 | p_rec_agr_line_id
474 | p_rec_calc_period_id
475 | p_calc_period_start_date
476 | p_calc_period_end_date
477 | P_as_of_date
478 |
479 | OUT:
480 |
481 | RETURNS : None
482 |
483 | NOTES : Calculate recovery amount for a recovery agreement(s)
484 |
485 | MODIFICATION HISTORY
486 |
487 | 11-SEP-03 Daniel Thota o Created an overloaded proc to fix bug 3138335
488 | 05-FEB-05 piagrawa o Modified the tokens LOC_CODE_FRM and
489 | LOC_CODE_TO in message
490 | PN_RECALC_AGR_INP_PARAM. Bug 4144583.
491 | 05-DEC-2007 acprakas o Bug#6438840. Modified procedure to accept
492 | start_date, end_date and as_of_date as parameters
493 | and to pick up the recovery agreements correclty.
494 +============================================================================*/
495 PROCEDURE CALCULATE_REC_AMOUNT_BATCH(
496 errbuf OUT NOCOPY VARCHAR2
497 ,retcode OUT NOCOPY VARCHAR2
498 ,p_calc_period_startdate IN VARCHAR2 --Bug#6438840
499 ,p_calc_period_enddate IN VARCHAR2 --Bug#6438840
500 ,p_as_ofdate IN VARCHAR2 --Bug#6438840
501 ,p_lease_num_from IN VARCHAR2
502 ,p_lease_num_to IN VARCHAR2
503 ,p_location_code_from IN VARCHAR2
504 ,p_location_code_to IN VARCHAR2
505 ,p_rec_agr_num_from IN VARCHAR2
506 ,p_rec_agr_num_to IN VARCHAR2
507 ,p_property_name IN VARCHAR2
508 ,p_customer_name IN VARCHAR2
509 ,p_customer_site IN VARCHAR2
510 ,p_calc_period_ending IN VARCHAR2
511 ,p_org_id IN NUMBER
512 ) IS
513
514 l_rec_agreement_id pn_rec_agreements_all.rec_agreement_id%TYPE := NULL;
515 l_lease_id pn_rec_agreements_all.lease_id%TYPE := NULL;
516 l_location_id pn_rec_agreements_all.location_id%TYPE := NULL;
517 l_customer_id pn_rec_agreements_all.customer_id%TYPE := NULL;
518 l_cust_site_id pn_rec_agreements_all.cust_site_id%TYPE := NULL;
519 l_start_date pn_rec_calc_periods_all.start_date%TYPE := fnd_date.canonical_to_date(p_calc_period_startdate); --Bug#6438840
520 l_end_date pn_rec_calc_periods_all.end_date%TYPE := fnd_date.canonical_to_date(p_calc_period_enddate); --Bug#6438840
521 l_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE := fnd_date.canonical_to_date(p_as_ofdate); --Bug#6438840
522 l_calc_period_ending pn_rec_calc_periods_all.end_date%TYPE :=
523 trunc(fnd_date.canonical_to_date(p_calc_period_ending));
524
525 l_error VARCHAR2(2000) := 'Success';
526 l_error_code NUMBER := 0;
527
528 CURSOR calc_rec_amount_wloc IS
529 SELECT pra.rec_agreement_id
530 ,pra.lease_id
531 ,pra.location_id
532 ,pra.customer_id
533 ,pra.cust_site_id
534 FROM pn_leases pl
535 ,pn_rec_agreements_all pra
536 ,pn_locations_all ploc
537 WHERE pl.lease_id = pra.lease_id
538 AND ploc.location_id = pra.location_id
539 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
540 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
541 AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
542 AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
543 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
544 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
545 AND (pl.org_id = p_org_id or p_org_id is null)
546 ORDER BY pl.lease_id, pra.rec_agreement_id
547 ;
548
549 CURSOR calc_rec_amount_woloc IS
550 SELECT pra.rec_agreement_id
551 ,pra.lease_id
552 ,pra.location_id
553 ,pra.customer_id
554 ,pra.cust_site_id
555 FROM pn_leases pl
556 ,pn_rec_agreements_all pra
557 WHERE pl.lease_id = pra.lease_id
558 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
559 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
560 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
561 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
562 AND (pl.org_id = p_org_id or p_org_id is null)
563 ORDER BY pl.lease_id, pra.rec_agreement_id
564 ;
565
566 CURSOR calc_rec_amount_wloc_prop IS
567 SELECT pra.rec_agreement_id
568 ,pra.lease_id
569 ,pra.location_id
570 ,pra.customer_id
571 ,pra.cust_site_id
572 FROM pn_leases pl
573 ,pn_rec_agreements_all pra
574 ,pn_locations_all ploc
575 ,pn_properties_all prop
576 WHERE pl.lease_id = pra.lease_id
577 AND ploc.location_id = pra.location_id
578 AND ploc.property_id = prop.property_id
579 AND prop.property_code = p_property_name
580 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
581 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
582 AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
583 AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
584 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
585 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
586 AND (pl.org_id = p_org_id or p_org_id is null)
587 ORDER BY pl.lease_id, pra.rec_agreement_id
588 ;
589
590 CURSOR calc_rec_amount_woloc_prop IS
591 SELECT pra.rec_agreement_id
592 ,pra.lease_id
593 ,pra.location_id
594 ,pra.customer_id
595 ,pra.cust_site_id
596 FROM pn_leases pl
597 ,pn_rec_agreements_all pra
598 ,pn_locations_all ploc
599 ,pn_properties_all prop
600 WHERE pl.lease_id = pra.lease_id
601 AND ploc.location_id = pra.location_id
602 AND ploc.property_id = prop.property_id
603 AND prop.property_code = p_property_name
604 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
605 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
606 AND pra.rec_agreement_num >= nvl(p_rec_agr_num_from,pra.rec_agreement_num)
607 AND pra.rec_agreement_num <= nvl(p_rec_agr_num_to,pra.rec_agreement_num)
608 AND (pl.org_id = p_org_id or p_org_id is null)
609 ORDER BY pl.lease_id, pra.rec_agreement_id
610 ;
611
612 l_processed NUMBER := 0;
613 l_success_count NUMBER := 0;
614 l_fail_count NUMBER := 0;
615 l_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE;
616
617 BEGIN
618
619 pnp_debug_pkg.log('PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT_BATCH (+) ');
620 fnd_message.set_name ('PN','PN_RECALC_AGR_INP_PARAM');
621 fnd_message.set_token ('LEASE_FRM',p_lease_num_from);
622 fnd_message.set_token ('LEASE_TO',p_lease_num_to);
623 fnd_message.set_token ('LOC_CODE_FRM',p_location_code_from);
624 fnd_message.set_token ('LOC_CODE_TO',p_location_code_to);
625 fnd_message.set_token ('REC_FRM',p_rec_agr_num_from);
626 fnd_message.set_token ('REC_TO',p_rec_agr_num_to);
627 fnd_message.set_token ('PROP_NAME',p_property_name);
628 fnd_message.set_token ('CUST_NAME',p_customer_name);
629 fnd_message.set_token ('CUST_SITE',p_customer_site);
630 fnd_message.set_token ('PRD_END',l_calc_period_ending);
631
632 pnp_debug_pkg.put_log_msg(fnd_message.get);
633
634 /* if p_org_ID is not null, then set.
635 else if in R12, current org is already set
636 */
637 IF p_org_id is NOT NULL THEN
638 pn_mo_cache_utils.fnd_req_set_org_id (p_org_id);
639 /* uncomment to debug
640 pnp_debug_pkg.log('Set the org id with value:' || to_char(pn_mo_cache_utils.get_current_org_id)); */
641 END IF;
642
643 IF p_location_code_from IS NOT NULL or p_location_code_to IS NOT NULL THEN
644 IF p_property_name IS NOT NULL THEN
645 OPEN calc_rec_amount_wloc_prop;
646 ELSE
647 OPEN calc_rec_amount_wloc;
648 END IF;
649 ELSE
650 IF p_property_name IS NOT NULL THEN
651 OPEN calc_rec_amount_woloc_prop;
652 ELSE
653 OPEN calc_rec_amount_woloc;
654 END IF;
655 END IF;
656
657 LOOP
658
659 IF calc_rec_amount_wloc_prop%ISOPEN THEN
660 FETCH calc_rec_amount_wloc_prop INTO
661 l_rec_agreement_id
662 ,l_lease_id
663 ,l_location_id
664 ,l_customer_id
665 ,l_cust_site_id;
666 EXIT WHEN calc_rec_amount_wloc_prop%NOTFOUND;
667 ELSIF calc_rec_amount_wloc%ISOPEN THEN
668 FETCH calc_rec_amount_wloc INTO
669 l_rec_agreement_id
670 ,l_lease_id
671 ,l_location_id
672 ,l_customer_id
673 ,l_cust_site_id;
674 EXIT WHEN calc_rec_amount_wloc%NOTFOUND;
675 ELSIF calc_rec_amount_woloc_prop%ISOPEN THEN
676 FETCH calc_rec_amount_woloc_prop INTO
677 l_rec_agreement_id
678 ,l_lease_id
679 ,l_location_id
680 ,l_customer_id
681 ,l_cust_site_id;
682 EXIT WHEN calc_rec_amount_woloc_prop%NOTFOUND;
683 ELSIF calc_rec_amount_woloc%ISOPEN THEN
684 FETCH calc_rec_amount_woloc INTO
685 l_rec_agreement_id
686 ,l_lease_id
687 ,l_location_id
688 ,l_customer_id
689 ,l_cust_site_id;
690 EXIT WHEN calc_rec_amount_woloc%NOTFOUND;
691 END IF;
692
693 l_error := 'Success';
694 l_error_code := null;
695
696 l_rec_calc_period_id := validate_create_calc_period(p_rec_agreement_id => l_rec_agreement_id
697 ,p_start_date => l_start_date
698 ,p_end_date => l_end_date
699 ,p_as_of_date => l_as_of_date);
700 IF l_rec_calc_period_id <> -1
701 THEN
702
703 l_processed := l_processed + 1;
704
705 PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT(
706 p_rec_agreement_id => l_rec_agreement_id
707 ,p_lease_id => l_lease_id
708 ,p_location_id => l_location_id
709 ,p_customer_id => l_customer_id
710 ,p_cust_site_id => l_cust_site_id
711 ,p_rec_agr_line_id => NULL
712 ,p_rec_calc_period_id => l_rec_calc_period_id
713 ,p_calc_period_start_date => l_start_date
714 ,p_calc_period_end_date => l_end_date
715 ,p_as_of_date => l_as_of_date
716 ,p_error => l_error
717 ,p_error_code => l_error_code
718 );
719
720 IF nvl(l_error_code,0) <> -99 THEN
721
722 l_success_count := l_success_count + 1;
723 ELSE
724 l_fail_count := l_fail_count + 1;
725
726 END IF;
727
728 END IF; --l_rec_calc_period_id <> -1
729
730 END LOOP;
731
732 pnp_debug_pkg.put_log_msg('
733 ===============================================================================');
734
735 fnd_message.set_name ('PN','PN_RECALC_AGR_PROC');
736 fnd_message.set_token ('NUM', l_processed);
737 pnp_debug_pkg.put_log_msg(fnd_message.get);
738
739 fnd_message.set_name ('PN','PN_RECALC_AGR_SUC');
740 fnd_message.set_token ('NUM', l_success_count);
741 pnp_debug_pkg.put_log_msg(fnd_message.get);
742
743 fnd_message.set_name ('PN','PN_RECALC_AGR_FAIL');
744 fnd_message.set_token ('NUM', l_fail_count);
745 pnp_debug_pkg.put_log_msg(fnd_message.get);
746
747 pnp_debug_pkg.put_log_msg('
748 ===============================================================================');
749
750 IF calc_rec_amount_wloc_prop%ISOPEN THEN
751 CLOSE calc_rec_amount_wloc_prop;
752 ELSIF calc_rec_amount_wloc%ISOPEN THEN
753 CLOSE calc_rec_amount_wloc;
754 ELSIF calc_rec_amount_woloc_prop%ISOPEN THEN
755 CLOSE calc_rec_amount_woloc_prop;
756 ELSIF calc_rec_amount_woloc%ISOPEN THEN
757 CLOSE calc_rec_amount_woloc;
758 END IF;
759
760 pnp_debug_pkg.log('PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT_BATCH (-) ');
761
762 EXCEPTION
763
764 When OTHERS Then
765 pnp_debug_pkg.log('Error in PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT_BATCH :'||to_char(sqlcode)||' : '||sqlerrm);
766 Errbuf := SQLERRM;
767 Retcode := 2;
768 rollback;
769 raise;
770
771 END CALCULATE_REC_AMOUNT_BATCH;
772
773 /*=============================================================================+
774 | PROCEDURE
775 | CALCULATE_REC_AMOUNT
776 |
777 | DESCRIPTION
778 | Calculate recovery amount for a recovery agreement(s)
779 |
780 | SCOPE - PUBLIC
781 |
782 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
783 |
784 | ARGUMENTS : IN:
785 | p_rec_agreement_id
786 | p_lease_id
787 | p_location_id
788 | p_customer_id
789 | p_cust_site_id
790 | p_rec_agr_line_id
791 | p_rec_calc_period_id
792 | p_calc_period_start_date
793 | p_calc_period_end_date
794 | P_as_of_date
795 |
796 | OUT:
797 |
798 | RETURNS : None
799 |
800 | NOTES : Calculate recovery amount for a recovery agreement(s)
801 |
802 | MODIFICATION HISTORY
803 |
804 | 07-APR-03 Daniel o Created
805 | 22-NOV-05 Kiran o Changed csr_check_line_status, csr_check_period_line
806 | calc_all_cons, calc_all_no_cons, calc_no_cons; replaced
807 | pn_rec_agr_lines_all with pn_rec_agr_lines
808 +============================================================================*/
809
810 PROCEDURE CALCULATE_REC_AMOUNT(
811 p_rec_agreement_id IN NUMBER
812 ,p_lease_id IN NUMBER
813 ,p_location_id IN NUMBER
814 ,p_customer_id IN NUMBER
815 ,p_cust_site_id IN NUMBER
816 ,p_rec_agr_line_id IN NUMBER DEFAULT NULL
817 ,p_rec_calc_period_id IN NUMBER DEFAULT NULL
818 ,p_calc_period_start_date IN DATE
819 ,p_calc_period_end_date IN DATE
820 ,p_as_of_date IN DATE
821 ,p_error IN OUT NOCOPY VARCHAR2
822 ,p_error_code IN OUT NOCOPY NUMBER
823 ) IS
824
825 l_line_expenses pn_rec_expcl_dtlln_all.computed_recoverable_amt%TYPE := 0;
826 l_fee_before_contr pn_rec_expcl_dtlln_all.cls_line_fee_before_contr_ovr%TYPE :=0;
827 l_fee_after_contr pn_rec_expcl_dtlln_all.cls_line_fee_after_contr_ovr%TYPE :=0;
828 l_tot_prop_area pn_rec_arcl_dtl_all.TOTAL_assignable_area%TYPE;
829 l_ten_recoverable_area_rec ten_recoverable_area_rec;
830 l_ten_recoverable_area pn_rec_arcl_dtlln_all.occupied_area%TYPE:=0;
831 l_ten_occupancy_pct pn_rec_arcl_dtlln_all.occupancy_pct%TYPE:=0;
832 l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE:=0;
833 l_line_constraints pn_rec_agr_linconst_all.value%TYPE:=0;
834 l_line_abatements pn_rec_agr_linabat_all.amount%TYPE:=0;
835 l_ten_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
836 l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE:=0;
837 l_amount_per_sft NUMBER:=0;
838 l_budget_amount_per_sft NUMBER:=0;
839 l_rate pn_rec_agr_lines_all.fixed_rate%TYPE:= 0;
840 l_constrained_actual pn_rec_period_lines_all.constrained_actual%TYPE:=0;
841 l_constrained_budget pn_rec_period_lines_all.constrained_actual%TYPE:=0;
842 l_actual_prorata_share pn_rec_period_lines_all.actual_prorata_share%TYPE:=0;
843 l_reconciled_amount pn_rec_period_lines_all.reconciled_amount%TYPE:=0;
844 l_rowId ROWID:= NULL;
845 l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE := NULL;
846 l_creation_date DATE := SYSDATE;
847 l_created_by NUMBER := NVL(fnd_profile.value('USER_ID'), 0);
848 l_BUDGET_PCT NUMBER:=0;
849 l_TENANCY_START_DATE DATE;
850 l_TENANCY_END_DATE DATE;
851 l_STATUS VARCHAR2(30):=NULL;
852 l_BUDGET_PRORATA_SHARE NUMBER:=0;
853 l_BUDGET_COST_PER_AREA NUMBER:=0;
854 l_BUDGET_RECOVERY NUMBER:=0;
855 l_BUDGET_EXPENSE PN_REC_EXPCL_DTLLN_ALL.budgeted_amt%TYPE;
856 l_count NUMBER := 0;
857 i NUMBER := 0;
858 l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE:=0;
859 l_prior_period_cap pn_rec_period_lines_all.constrained_actual%TYPE:=0;
860 l_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE:= NULL;
861 l_end_date pn_rec_period_lines_all.end_date%TYPE;
862 l_billing_type pn_rec_period_lines_all.billing_type%TYPE;
863 l_billing_purpose pn_rec_period_lines_all.billing_purpose%TYPE;
864 l_error_code NUMBER := 0;
865 l_rec_agr_name pn_rec_agreements_all.REC_AGREEMENT_NAME%TYPE;
866 l_rec_agr_num pn_rec_agreements_all.REC_AGREEMENT_NUM%TYPE;
867 --------------------------------------------------------------------------
868 -- Cursor to bring all details of a line. This cursor will be used in the
869 -- event that the user chooses to hit the 'Calculate All' button and every
870 -- line lying within the calc period needs to be picked up for recoery calc
871 --------------------------------------------------------------------------
872
873 CURSOR csr_currency_code is
874 SELECT currency_code
875 ,negative_recovery
876 ,rec_agreement_name
877 ,rec_agreement_num
878 ,org_id
879 FROM pn_rec_agreements_all
880 WHERE rec_agreement_id = p_rec_agreement_id;
881
882 CURSOR agr_lines_all IS
883 SELECT lines.rec_agr_line_id
884 ,lines.type
885 ,lines.purpose
886 ,lines.start_date
887 ,lines.end_date
888 ,lines.calc_method
889 ,lines.fixed_amount
890 ,lines.fixed_rate
891 ,lines.fixed_pct
892 ,lines.multiple_pct
893 FROM pn_rec_agr_lines_all lines
894 WHERE lines.rec_agreement_id = p_rec_agreement_id
895 AND p_as_of_date between lines.start_date AND end_date ;
896
897 --------------------------------------------------------------------------
898 -- Cursor to bring all details of a line. This cursor will be used in the
899 -- event that the user chooses to hit the 'Calculate ' button and the line
900 -- id is available.
901 --------------------------------------------------------------------------
902 CURSOR agr_lines_one IS
903 SELECT lines.rec_agr_line_id
904 ,lines.type
905 ,lines.purpose
906 ,lines.start_date
907 ,lines.end_date
908 ,lines.calc_method
909 ,lines.fixed_amount
910 ,lines.fixed_rate
911 ,lines.fixed_pct
912 ,lines.multiple_pct
913 FROM pn_rec_agr_lines_all lines
914 WHERE lines.rec_agr_line_id = p_rec_agr_line_id
915 AND p_as_of_date between lines.start_date AND end_date ;
916
917 CURSOR csr_check_line_status IS
918 SELECT 'Y'
919 FROM DUAL
920 WHERE exists (SELECT NULL
921 FROM pn_rec_period_lines_all plines
922 ,pn_rec_agr_lines_all lines
923 ,pn_rec_calc_periods_all calc_periods
924 WHERE lines.rec_agreement_id = p_rec_agreement_id
925 AND p_as_of_date between lines.start_date and lines.end_date
926 AND plines.rec_agr_line_id = lines.rec_agr_line_id
927 AND plines.start_date = p_calc_period_start_date
928 AND plines.end_date = p_calc_period_end_date
929 AND UPPER(plines.status) <> 'COMPLETE'
930 AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
931 AND calc_periods.start_date = p_calc_period_start_date
932 AND calc_periods.end_date = p_calc_period_end_date
933 AND calc_periods.as_of_date = p_as_of_date
934 );
935
936 CURSOR csr_check_period_line IS
937 SELECT 'Y'
938 FROM DUAL
939 WHERE not exists (SELECT NULL
940 FROM pn_rec_period_lines_all plines
941 ,pn_rec_agr_lines_all lines
942 ,pn_rec_calc_periods_all calc_periods
943 WHERE lines.rec_agreement_id = p_rec_agreement_id
944 AND p_as_of_date between lines.start_date and lines.end_date
945 AND plines.rec_agr_line_id = lines.rec_agr_line_id
946 AND plines.start_date = p_calc_period_start_date
947 AND plines.end_date = p_calc_period_end_date
948 AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
949 AND calc_periods.start_date = p_calc_period_start_date
950 AND calc_periods.end_date = p_calc_period_end_date
951 AND calc_periods.as_of_date = p_as_of_date
952 );
953 --------------------------------------------------------------------------------------------------
954 -- This cursor to be used in creating one term for all lines and when the user hits 'Calculate All'
955 -- and consolidate option is set to 'Y'
956 --------------------------------------------------------------------------------------------------
957
958 CURSOR calc_all_cons IS
959 SELECT NVL(SUM(NVL(plines.reconciled_amount,0)),0) RECONCILED_AMOUNT
960 FROM pn_rec_period_lines_all plines
961 ,pn_rec_calc_periods_all calc_periods
962 ,pn_rec_agr_lines_all lines
963 WHERE lines.rec_agreement_id = p_rec_agreement_id
964 AND plines.rec_agr_line_id = lines.rec_agr_line_id
965 AND plines.start_date = p_calc_period_start_date
966 AND plines.end_date = p_calc_period_end_date
967 AND UPPER(plines.status) = 'COMPLETE'
968 AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
969 AND calc_periods.start_date = p_calc_period_start_date
970 AND calc_periods.end_date = p_calc_period_end_date
971 AND calc_periods.as_of_date = p_as_of_date
972 ;
973
974 CURSOR calc_all_no_cons IS
975 SELECT plines.rec_agr_line_id
976 ,plines.end_date
977 ,NVL(plines.reconciled_amount,0) RECONCILED_AMOUNT
978 ,plines.billing_type
979 ,plines.billing_purpose
980 FROM pn_rec_period_lines_all plines
981 ,pn_rec_calc_periods_all calc_periods
982 ,pn_rec_agr_lines_all lines
983 WHERE lines.rec_agreement_id = p_rec_agreement_id
984 AND plines.rec_agr_line_id = lines.rec_agr_line_id
985 AND plines.start_date = p_calc_period_start_date
986 AND plines.end_date = p_calc_period_end_date
987 AND UPPER(plines.status) = 'COMPLETE'
988 AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
989 AND calc_periods.start_date = p_calc_period_start_date
990 AND calc_periods.end_date = p_calc_period_end_date
991 AND calc_periods.as_of_date = p_as_of_date
992 ;
993 --------------------------------------------------------------------------------------------
994 -- This cursor to be used in creating one term for a line and when the user hits 'Calculate'
995 --------------------------------------------------------------------------------------------
996
997 CURSOR calc_no_cons IS
998 SELECT plines.rec_agr_line_id
999 ,plines.end_date
1000 ,nvl(plines.reconciled_amount,0) RECONCILED_AMOUNT
1001 ,plines.billing_type
1002 ,plines.billing_purpose
1003 FROM pn_rec_period_lines_all plines
1004 ,pn_rec_calc_periods_all calc_periods
1005 WHERE plines.rec_agr_line_id = p_rec_agr_line_id
1006 AND plines.start_date = p_calc_period_start_date
1007 AND plines.end_date = p_calc_period_end_date
1008 AND upper(plines.status) = 'COMPLETE'
1009 AND calc_periods.rec_calc_period_id = plines.rec_calc_period_id
1010 AND calc_periods.start_date = p_calc_period_start_date
1011 AND calc_periods.end_date = p_calc_period_end_date
1012 AND calc_periods.as_of_date = p_as_of_date
1013 ;
1014
1015 /* PL/SQL table to store the constraints details */
1016 line_constr_tbl g_line_constr_type;
1017
1018 agr_lines_record agr_lines_all%ROWTYPE;
1019 calc_all_no_cons_rec calc_all_no_cons%ROWTYPE;
1020 calc_all_cons_rec calc_all_cons%ROWTYPE;
1021 calc_no_cons_rec calc_no_cons%ROWTYPE;
1022 l_negative_recovery pn_rec_agreements_all.negative_recovery%TYPE;
1023 l_opya_exists BOOLEAN := FALSE;
1024 l_opyc_exists BOOLEAN := FALSE;
1025 l_rate_amt_exists BOOLEAN := FALSE;
1026 l_total_lines NUMBER := 0;
1027 l_success_lines NUMBER := 0;
1028 l_error_lines NUMBER := 0;
1029 l_calculate_all BOOLEAN := FALSE;
1030 l_open_exists VARCHAR2(1) := 'N';
1031 l_no_prd_line VARCHAR2(1) := 'N';
1032
1033 l_consolidate VARCHAR2(30);
1034 l_org_id NUMBER;
1035
1036 BEGIN
1037
1038 pnp_debug_pkg.log('PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT (+) ');
1039
1040 /* Get currency code and negative recovery value for the agreement */
1041
1042 FOR rec IN csr_currency_code LOOP
1043 g_currency_code := rec.currency_code;
1044 l_negative_recovery := rec.negative_recovery;
1045 l_rec_agr_name := rec.rec_agreement_name;
1046 l_rec_agr_num := rec.rec_agreement_num;
1047 l_org_id := rec.org_id;
1048 END LOOP;
1049
1050 l_consolidate := NVL(pn_mo_cache_utils.get_profile_value('PN_REC_CONSOLIDATE_TERMS', l_org_id), 'N');
1051
1052 pnp_debug_pkg.put_log_msg(' ');
1053 fnd_message.set_name ('PN','PN_RECALC_AGR_NAME');
1054 fnd_message.set_token ('NAME', l_rec_agr_name);
1055 pnp_debug_pkg.put_log_msg(fnd_message.get);
1056
1057 fnd_message.set_name ('PN','PN_RECALC_AGR_NUMBER');
1058 fnd_message.set_token ('NUM', l_rec_agr_num);
1059 pnp_debug_pkg.put_log_msg(fnd_message.get);
1060
1061 pnp_debug_pkg.log('calculate_rec_amount - agreement id : '||p_rec_agreement_id);
1062 pnp_debug_pkg.log('calculate_rec_amount - agreement id : '||p_rec_agreement_id);
1063 pnp_debug_pkg.log('calculate_rec_amount - currency code : '||g_currency_code);
1064 pnp_debug_pkg.log('calculate_rec_amount - -ve rent rule : '||l_negative_recovery);
1065
1066 IF p_rec_agr_line_id IS NULL THEN
1067 OPEN agr_lines_all;
1068 l_calculate_all := TRUE;
1069 ELSE
1070 OPEN agr_lines_one;
1071 l_calculate_all := FALSE;
1072 END IF;
1073
1074 LOOP
1075
1076 IF agr_lines_all%ISOPEN THEN
1077 FETCH agr_lines_all INTO agr_lines_record;
1078 EXIT WHEN agr_lines_all%NOTFOUND;
1079 ELSIF agr_lines_one%ISOPEN THEN
1080 FETCH agr_lines_one INTO agr_lines_record;
1081 EXIT WHEN agr_lines_one%NOTFOUND;
1082 END IF;
1083
1084 l_total_lines := l_total_lines + 1;
1085
1086 /* Initializing all variables used for calculation */
1087
1088 p_error := 'Success';
1089 p_error_code := 0;
1090 l_line_expenses := 0;
1091 l_fee_before_contr := 0;
1092 l_fee_after_contr := 0;
1093 l_budget_expense := NULL;
1094 l_contr_actual_recovery := 0;
1095 l_ten_actual_recovery := 0;
1096 l_budget_recovery := 0;
1097 l_status := NULL;
1098 l_tot_prop_area := NULL;
1099 l_rate := 0;
1100 l_budget_cost_per_area := 0;
1101 l_ten_recoverable_area := 0;
1102 l_ten_occupancy_pct := 0;
1103 l_ten_recoverable_area_rec.occupied_area := NULL;
1104 l_ten_recoverable_area_rec.occupancy_pct := NULL;
1105 l_amount_per_sft := 0;
1106 l_budget_amount_per_sft := 0;
1107 l_billed_recovery := 0;
1108 l_prior_period_amount := 0;
1109 l_prior_period_cap := 0;
1110 l_line_abatements := 0;
1111 l_constrained_actual := 0;
1112 l_constrained_budget := 0;
1113 l_actual_prorata_share := 0;
1114 l_budget_prorata_share := 0;
1115 l_reconciled_amount := 0;
1116 l_rowId := NULL;
1117 l_rec_period_lines_id := NULL;
1118 l_BUDGET_PCT := 0;
1119 l_tenancy_start_date := NULL;
1120 l_tenancy_end_date := NULL;
1121 line_constr_tbl.delete;
1122
1123 pnp_debug_pkg.log('Processing line id : '||agr_lines_record.rec_agr_line_id);
1124
1125 /* Test for calculation Method */
1126
1127 pnp_debug_pkg.log('Calculation Method : '||agr_lines_record.calc_method);
1128
1129 IF agr_lines_record.calc_method <> 'FIXEDAMT' THEN
1130
1131 -- Only if calculation method is either Prorata Share, Fixed Pct or Fixed Rate
1132
1133 IF agr_lines_record.calc_method IN('PRORATSH','FIXEDPCT') THEN
1134
1135 pnp_debug_pkg.log('Calculation Method : Prorata Share/Fixed Pct - line id :'||agr_lines_record.rec_agr_line_id);
1136 -- If calculation method is Prorata Share get the expenses for the line
1137 -- and the total area of the property
1138
1139 pnp_debug_pkg.log('get_line_expenses....Agr line id : '||agr_lines_record.rec_agr_line_id);
1140 pnp_debug_pkg.log('get_line_expenses - Customer id : '||p_customer_id);
1141 pnp_debug_pkg.log('get_line_expenses - Lease id : '||p_lease_id);
1142 pnp_debug_pkg.log('get_line_expenses - Location id : '||p_location_id);
1143 pnp_debug_pkg.log('get_line_expenses - Start Date : '||p_calc_period_start_date);
1144 pnp_debug_pkg.log('get_line_expenses - End Date : '||p_calc_period_end_date);
1145
1146 PN_REC_CALC_PKG.get_line_expenses(
1147 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1148 ,p_customer_id => p_customer_id
1149 ,p_lease_id => p_lease_id
1150 ,p_location_id => p_location_id
1151 ,p_calc_period_start_date => p_calc_period_start_date
1152 ,p_calc_period_end_date => p_calc_period_end_date
1153 ,p_calc_period_as_of_date => p_as_of_date
1154 ,p_recoverable_amt => l_line_expenses
1155 ,p_fee_before_contr => l_fee_before_contr
1156 ,p_fee_after_contr => l_fee_after_contr
1157 ,p_error => p_error
1158 ,p_error_code => p_error_code
1159 );
1160
1161 pnp_debug_pkg.log('get_line_expenses - Recoverable_amt : '||l_line_expenses);
1162 pnp_debug_pkg.log('get_line_expenses - Fee Before Contr : '||l_fee_before_contr);
1163 pnp_debug_pkg.log('get_line_expenses - Fee after contr : '||l_fee_after_contr);
1164 pnp_debug_pkg.log('get_line_expenses - Return Status : '||p_error);
1165 pnp_debug_pkg.log('get_line_expenses - Return Code : '||p_error_code);
1166
1167 IF p_error_code <> -99 THEN
1168 l_budget_expense := PN_REC_CALC_PKG.get_budget_expenses(
1169 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1170 ,p_customer_id => p_customer_id
1171 ,p_lease_id => p_lease_id
1172 ,p_location_id => p_location_id
1173 ,p_calc_period_start_date => p_calc_period_start_date
1174 ,p_calc_period_end_date => p_calc_period_end_date
1175 ,p_calc_period_as_of_date => p_as_of_date
1176 );
1177
1178 IF l_budget_expense = -99 THEN
1179
1180 p_error_code := -99;
1181 l_budget_expense := null;
1182
1183 ELSE
1184 p_error_code := 0;
1185
1186 END IF;
1187
1188
1189 pnp_debug_pkg.log('get_budget_expenses - Return Code : '||p_error_code);
1190 pnp_debug_pkg.log('Budget Expense : '||l_budget_expense);
1191
1192 END IF;
1193
1194 pnp_debug_pkg.log('Error Code 1 : '||p_error_code);
1195
1196 IF p_error_code <> -99 THEN
1197
1198 l_contr_actual_recovery := PN_REC_CALC_PKG.get_contr_actual_recovery(
1199 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1200 ,p_customer_id => p_customer_id
1201 ,p_lease_id => p_lease_id
1202 ,p_location_id => p_location_id
1203 ,p_calc_period_start_date => p_calc_period_start_date
1204 ,p_calc_period_end_date => p_calc_period_end_date
1205 ,p_as_of_date => p_as_of_date
1206 ,p_called_from => 'CALC'
1207 );
1208
1209 IF l_contr_actual_recovery = -99 THEN
1210
1211 p_error_code := -99;
1212 l_contr_actual_recovery := null;
1213
1214 ELSE
1215 p_error_code := 0;
1216
1217 END IF;
1218
1219 pnp_debug_pkg.log('Contr Actual Recovery : '||l_contr_actual_recovery);
1220
1221 END IF;
1222
1223 pnp_debug_pkg.log('Error Code 2 : '||p_error_code);
1224
1225 /* If total expenses is greater than contributors prorata share
1226 then subtract the contributors prorata share from total expenses.
1227 Also apply the fee after contributors if the fee before
1228 contributors has not been applied */
1229
1230 IF p_error_code <> -99 AND
1231 nvl(l_line_expenses,0) >= nvl(l_contr_actual_recovery,0) THEN
1232
1233 /* Apply fee after contributor only if fee before contributor
1234 has not been applied */
1235
1236 IF l_fee_before_contr = 0 THEN
1237
1238 l_line_expenses := (nvl(l_line_expenses,0) - nvl(l_contr_actual_recovery,0))
1239 + ((l_fee_after_contr / 100) *
1240 (nvl(l_line_expenses,0) - nvl(l_contr_actual_recovery,0)));
1241
1242 pnp_debug_pkg.log('get_line_expenses - expense after contr and fee : '||l_line_expenses);
1243 ELSE
1244
1245 l_line_expenses := nvl(l_line_expenses,0) - nvl(l_contr_actual_recovery,0);
1246 pnp_debug_pkg.log('get_line_expenses - expenses after contr : '||l_line_expenses);
1247
1248 END IF;
1249
1250
1251 ELSIF p_error_code <> -99 AND
1252 nvl(l_line_expenses,0) < nvl(l_contr_actual_recovery,0) THEN
1253
1254 l_line_expenses := 0;
1255
1256 END IF;
1257
1258 pnp_debug_pkg.log('Line expenses after contributors : '||l_line_expenses);
1259
1260
1261 pnp_debug_pkg.log('Error Code 3 : '||p_error_code);
1262
1263 IF p_error_code <> -99 AND
1264 agr_lines_record.calc_method = 'FIXEDPCT' AND
1265 nvl(agr_lines_record.fixed_pct,0) <> 0 THEN
1266
1267 pnp_debug_pkg.log('Error Code 4 : '||p_error_code);
1268 l_ten_actual_recovery := l_line_expenses*agr_lines_record.fixed_pct/100;
1269
1270 l_budget_recovery := l_budget_expense*agr_lines_record.fixed_pct/100;
1271
1272 l_status := 'COMPLETE';
1273
1274 pnp_debug_pkg.log('Calculation Method : Fixed Pct - tenant actual recovery :'||
1275 l_ten_actual_recovery||l_status);
1276 p_error := 'Success';
1277 p_error_code := 0;
1278
1279 ELSIF p_error_code <> -99 AND
1280 agr_lines_record.calc_method = 'FIXEDPCT' AND
1281 nvl(agr_lines_record.fixed_pct,0) = 0 THEN
1282
1283 pnp_debug_pkg.log('Error Code 5 : '||p_error_code);
1284 l_ten_actual_recovery := 0;
1285 l_status := 'ERROR';
1286
1287 fnd_message.set_name ('PN','PN_RECALC_PCT_NOT');
1288 pnp_debug_pkg.put_log_msg(fnd_message.get);
1289
1290 p_error := 'Percentage not specified for Fixed Percentage calc. method';
1291 p_error_code := -99;
1292
1293 -- END IF;
1294
1295 ELSIF p_error_code <> -99 AND
1296 agr_lines_record.calc_method = 'PRORATSH' THEN
1297
1298 pnp_debug_pkg.log('Error Code 6 : '||p_error_code);
1299 -- Only if calculation method is either Prorata Share, Fixed Pct or Fixed Rate
1300
1301
1302 pnp_debug_pkg.log('get_tot_prop_area - Agr line id : '||agr_lines_record.rec_agr_line_id);
1303 pnp_debug_pkg.log('get_tot_prop_area - Customer id : '||p_customer_id);
1304 pnp_debug_pkg.log('get_tot_prop_area - Lease id : '||p_lease_id);
1305 pnp_debug_pkg.log('get_tot_prop_area - Location id : '||p_location_id);
1306 pnp_debug_pkg.log('get_tot_prop_area - Start Date : '||p_calc_period_start_date);
1307 pnp_debug_pkg.log('get_tot_prop_area - End Date : '||p_calc_period_end_date);
1308 pnp_debug_pkg.log('get_tot_prop_area - As of Date : '||p_as_of_date);
1309
1310 l_tot_prop_area := PN_REC_CALC_PKG.get_tot_prop_area(
1311 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1312 ,p_customer_id => p_customer_id
1313 ,p_lease_id => p_lease_id
1314 ,p_location_id => p_location_id
1315 ,p_calc_period_start_date => p_calc_period_start_date
1316 ,p_calc_period_end_date => p_calc_period_end_date
1317 ,p_as_of_date => p_as_of_date
1318 );
1319
1320 IF l_tot_prop_area = -99 THEN
1321
1322 fnd_message.set_name ('PN','PN_RECALB_TOT_AR');
1323 pnp_debug_pkg.put_log_msg(fnd_message.get);
1324
1325 p_error := 'Error while getting Total Property Area ';
1326 p_error_code := -99;
1327 l_tot_prop_area := 0;
1328
1329 ELSE
1330 p_error := 'Success';
1331 p_error_code := 0;
1332 pnp_debug_pkg.log('Total Property Area : '||l_tot_prop_area);
1333
1334 END IF;
1335
1336
1337 pnp_debug_pkg.log('Error Code 7 : '||p_error_code);
1338 IF p_error_code <> -99 AND
1339 (nvl(l_tot_prop_area,0) <> 0) AND
1340 (nvl(l_line_expenses,0) <> 0) THEN
1341
1342 pnp_debug_pkg.log('Error Code 8 : '||p_error_code);
1343 -- Compute the rate
1344
1345 l_rate := l_line_expenses/l_tot_prop_area;
1346 pnp_debug_pkg.log('Calc. Method : Prorata Share - total expense :'||l_line_expenses);
1347 pnp_debug_pkg.log('Calc. Method : Prorata Share - total area :'||l_tot_prop_area);
1348
1349 END IF;
1350
1351 pnp_debug_pkg.log('Error Code 9 : '||p_error_code);
1352 IF p_error_code <> -99 AND
1353 (nvl(l_tot_prop_area,0) <> 0) AND
1354 nvl(l_budget_expense,0) <> 0 THEN
1355
1356 pnp_debug_pkg.log('Error Code 10 : '||p_error_code);
1357 l_budget_cost_per_area := l_budget_expense/l_tot_prop_area;
1358
1359 pnp_debug_pkg.log('Calc. Method : Prorata Share - Budget total expense :'||l_budget_expense);
1360 pnp_debug_pkg.log('Calc. Method : Prorata Share - Budget total area :'||l_tot_prop_area);
1361 END IF;
1362 ------------------------------------------------------------------------
1363 -- Compute tenant's share of the area to be used in the calculation
1364 -- The procedure returns a record with occupied area and the occupancy %
1365 ------------------------------------------------------------------------
1366
1367 pnp_debug_pkg.log('Error Code 11 : '||p_error_code);
1368 IF p_error_code <> -99 THEN
1369
1370 pnp_debug_pkg.log('Error Code 12 : '||p_error_code);
1371 pnp_debug_pkg.log('ten_recoverable_areaAgr line id : '||agr_lines_record.rec_agr_line_id);
1372 pnp_debug_pkg.log('ten_recoverable_area - Customer id : '||p_customer_id);
1373 pnp_debug_pkg.log('ten_recoverable_area - Lease id : '||p_lease_id);
1374 pnp_debug_pkg.log('ten_recoverable_area - Location id : '||p_location_id);
1375 pnp_debug_pkg.log('ten_recoverable_area - Start Date : '||p_calc_period_start_date);
1376 pnp_debug_pkg.log('ten_recoverable_area - End Date : '||p_calc_period_end_date);
1377 pnp_debug_pkg.log('ten_recoverable_area - As of Date : '||p_as_of_date);
1378
1379 l_ten_recoverable_area_rec := PN_REC_CALC_PKG.ten_recoverable_area(
1380 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1381 ,p_customer_id => p_customer_id
1382 ,p_lease_id => p_lease_id
1383 ,p_location_id => p_location_id
1384 ,p_calc_period_start_date => p_calc_period_start_date
1385 ,p_calc_period_end_date => p_calc_period_end_date
1386 ,p_as_of_date => p_as_of_date
1387 );
1388
1389 IF l_ten_recoverable_area_rec.occupied_area = -99 AND
1390 l_ten_recoverable_area_rec.occupancy_pct = -99 THEN
1391
1392 l_ten_recoverable_area := 0;
1393 l_ten_occupancy_pct := 0;
1394 p_error := 'Error getting tenant recoverable area';
1395 p_error_code := -99;
1396
1397 ELSE
1398
1399 l_ten_recoverable_area := l_ten_recoverable_area_rec.occupied_area;
1400 l_ten_occupancy_pct := l_ten_recoverable_area_rec.occupancy_pct;
1401 p_error := 'Success';
1402 p_error_code := 0;
1403
1404 pnp_debug_pkg.log('Calc. Method : Prorata Share - tenant rec area :'
1405 ||l_ten_recoverable_area);
1406 pnp_debug_pkg.log('Calc. Method : Prorata Share - tenant occ% :'
1407 ||l_ten_occupancy_pct);
1408 END IF;
1409
1410 pnp_debug_pkg.log('Error Code 13 : '||p_error_code);
1411 END IF;
1412 pnp_debug_pkg.log('Error Code 14 : '||p_error_code);
1413
1414 END IF; /* end of fixed pct and prorata share */
1415
1416 pnp_debug_pkg.log('Error Code 15 : '||p_error_code);
1417 ELSIF agr_lines_record.calc_method = 'FIXEDRT' THEN
1418
1419 pnp_debug_pkg.log('Calculation Method : Fixed Rate - line id :'||agr_lines_record.rec_agr_line_id);
1420 -- For fixed rate we have the rate available and since the user puts
1421 -- in the recoverable area occupancy % is 100%
1422
1423 l_rate := agr_lines_record.fixed_rate;
1424 l_ten_recoverable_area := PN_REC_CALC_PKG.get_recoverable_area(
1425 p_rec_calc_period_id => p_rec_calc_period_id
1426 ,p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1427 );
1428 IF l_ten_recoverable_area = -99 THEN
1429
1430 l_ten_recoverable_area := 0;
1431 p_error := 'Error getting tenant rec. area for fixed rate';
1432 p_error_code := -99;
1433
1434 ELSE
1435
1436 l_ten_occupancy_pct := 100;
1437 p_error_code := 0;
1438
1439 pnp_debug_pkg.log('Calc. Method : Fixed Rate - tenant rec area :'
1440 ||l_ten_recoverable_area);
1441 pnp_debug_pkg.log('Calc. Method : Fixed Rate - tenant occ% :'||
1442 l_ten_occupancy_pct);
1443 END IF;
1444
1445 END IF; /* end of all cal. methods */
1446
1447 -- Calculate tenant's actual recovery
1448
1449 pnp_debug_pkg.log('Error Code 16 : '||p_error_code);
1450 IF p_error_code <> -99 AND
1451 agr_lines_record.calc_method in ('FIXEDRT','PRORATSH')AND
1452 (nvl(l_rate,0) <> 0) AND
1453 (nvl(l_ten_recoverable_area,0) <> 0) AND
1454 (nvl(l_ten_occupancy_pct,0) <> 0) THEN
1455
1456 l_amount_per_sft := l_rate*agr_lines_record.multiple_pct/100;
1457
1458 l_ten_actual_recovery := l_amount_per_sft*((l_ten_recoverable_area*l_ten_occupancy_pct)/100);
1459
1460 l_status := 'COMPLETE';
1461
1462 pnp_debug_pkg.log('Calculation Method : Prorata Share/Fixed Rate - amount per sq ft :'||
1463 l_amount_per_sft);
1464 pnp_debug_pkg.log('Calculation Method : Prorata Share/Fixed Rate - tenant actual recovery :'||l_ten_actual_recovery||l_status);
1465
1466 pnp_debug_pkg.log('Error Code 17 : '||p_error_code);
1467 ELSIF p_error_code <> -99 AND
1468 agr_lines_record.calc_method in ('FIXEDRT','PRORATSH') AND
1469 (nvl(l_rate,0) = 0 OR
1470 nvl(l_ten_recoverable_area,0) = 0 OR
1471 nvl(l_ten_occupancy_pct,0) = 0) THEN
1472
1473 l_ten_actual_recovery := 0;
1474 l_status := 'ERROR';
1475 p_error := 'Rate or recoverable Area or Occupancy pct is zero';
1476 p_error_code := -99;
1477 pnp_debug_pkg.log('Rate or recoverable Area or Occupancy pct is zero');
1478 pnp_debug_pkg.log('Rate is ' || to_char(nvl(l_rate,0)));
1479 pnp_debug_pkg.log('Recoverable Area is ' || to_char(nvl(l_ten_recoverable_area,0)));
1480 pnp_debug_pkg.log('Occ. Pct. is ' || to_char(nvl(l_ten_occupancy_pct,0)));
1481
1482 END IF;
1483
1484 pnp_debug_pkg.log('Error Code 18 : '||p_error_code);
1485 /* Get budget recovery for prorata share */
1486 IF p_error_code <> -99 AND
1487 agr_lines_record.calc_method = 'PRORATSH' AND
1488 nvl(l_budget_cost_per_area,0) <> 0 THEN
1489
1490 l_budget_amount_per_sft := l_budget_cost_per_area*agr_lines_record.multiple_pct/100;
1491
1492 l_budget_recovery := l_budget_amount_per_sft*((l_ten_recoverable_area*l_ten_occupancy_pct)/100);
1493
1494 END IF;
1495
1496 ELSIF p_error_code <> -99 AND agr_lines_record.calc_method = 'FIXEDAMT' THEN
1497
1498 pnp_debug_pkg.log('Error Code 19 : '||p_error_code);
1499 -- Tenant's actual recovery for fixed amount calc. method is user supplied
1500 l_status := 'COMPLETE';
1501 l_ten_actual_recovery := agr_lines_record.fixed_amount;
1502
1503 pnp_debug_pkg.log('Calculation Method : Fixed Amount - tenant actual recovery :'||l_ten_actual_recovery);
1504
1505 END IF; /* end of getting actual recovery for all calculation methods */
1506
1507 pnp_debug_pkg.log('Error Code 20 : '||p_error_code);
1508 IF p_error_code <> -99 THEN
1509
1510 -- Calculate billed recovery, constraints and abatements.
1511
1512 pnp_debug_pkg.log('get_billed_recovery - Agr line id : '||
1513 agr_lines_record.rec_agr_line_id);
1514 pnp_debug_pkg.log('get_billed_recovery - Payment Purpose: '||
1515 agr_lines_record.purpose);
1516 pnp_debug_pkg.log('get_billed_recovery - Payment Type: '||
1517 agr_lines_record.type);
1518 pnp_debug_pkg.log('get_billed_recovery - Lease id : '||p_lease_id);
1519 pnp_debug_pkg.log('get_billed_recovery - Location id : '||p_location_id); -- 110403
1520 pnp_debug_pkg.log('get_billed_recovery - Start Date : '||p_calc_period_start_date);
1521 pnp_debug_pkg.log('get_billed_recovery - End Date : '||p_calc_period_end_date);
1522 pnp_debug_pkg.log('get_billed_recovery - Calc Period Id : '||p_rec_calc_period_id);
1523
1524 l_billed_recovery := PN_REC_CALC_PKG.get_billed_recovery(
1525 p_payment_purpose => agr_lines_record.purpose
1526 ,p_payment_type => agr_lines_record.type
1527 ,p_lease_id => p_lease_id
1528 ,p_location_id => p_location_id -- 110403
1529 ,p_calc_period_start_date => p_calc_period_start_date
1530 ,p_calc_period_end_date => p_calc_period_end_date
1531 ,p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1532 ,p_rec_calc_period_id => p_rec_calc_period_id
1533 );
1534
1535 IF l_billed_recovery = -99 THEN
1536 p_error := 'error getting billed recovery';
1537 p_error_code := -99;
1538 l_billed_recovery := 0;
1539 ELSE
1540 p_error := 'Success';
1541 p_error_code := 0;
1542
1543 END IF;
1544
1545 pnp_debug_pkg.log('Billed Recovery :'||l_billed_recovery);
1546
1547 END IF;
1548
1549 pnp_debug_pkg.log('Error Code 21 : '||p_error_code);
1550 IF p_error_code <> -99 THEN
1551
1552 line_constr_tbl := PN_REC_CALC_PKG.get_line_constraints(
1553 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id ,
1554 p_as_of_date => p_as_of_date
1555 );
1556
1557 IF (line_constr_tbl.count > 0)
1558 AND (line_constr_tbl(1).constr_order = -99) THEN
1559 p_error := 'Error getting line constraints';
1560 p_error_code := -99;
1561 line_constr_tbl.delete;
1562
1563 ELSE
1564
1565 p_error := 'Success';
1566 p_error_code := 0;
1567
1568 END IF;
1569
1570 END IF;
1571
1572 pnp_debug_pkg.log('After getting constraints error code:'||p_error_code);
1573 pnp_debug_pkg.log('Error Code 22 : '||p_error_code);
1574 IF p_error_code <> -99 THEN
1575
1576 l_prior_period_amount := PN_REC_CALC_PKG.get_prior_period_actual_amount(
1577 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1578 ,p_start_date => p_calc_period_start_date
1579 ,p_as_of_date => p_as_of_date
1580 ,p_called_from => 'CALC'
1581 );
1582
1583 IF l_prior_period_amount = -99 THEN
1584
1585 p_error := 'Error getting prior period actual amount';
1586 p_error_code := -99;
1587 l_prior_period_amount := 0;
1588
1589 ELSE
1590
1591 p_error := 'Success';
1592 p_error_code := 0;
1593
1594 END IF;
1595
1596 END IF;
1597
1598 pnp_debug_pkg.log('Error Code 23 : '||p_error_code);
1599 IF p_error_code <> -99 THEN
1600
1601 l_prior_period_cap := PN_REC_CALC_PKG.get_prior_period_cap(
1602 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1603 ,p_start_date => p_calc_period_start_date
1604 ,p_end_date => p_calc_period_end_date
1605 ,p_as_of_date => p_as_of_date
1606 ,p_called_from => 'CALC'
1607 );
1608
1609 IF l_prior_period_cap = -99 THEN
1610
1611 p_error := 'Error getting prior period actual cap';
1612 p_error_code := -99;
1613 l_prior_period_cap := 0;
1614
1615 ELSE
1616
1617 p_error := 'Success';
1618 p_error_code := 0;
1619
1620 END IF;
1621
1622 END IF;
1623
1624 pnp_debug_pkg.log('Error Code 24 : '||p_error_code);
1625 IF p_error_code <> -99 THEN
1626
1627 l_line_abatements := PN_REC_CALC_PKG.get_line_abatements(
1628 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1629 ,p_as_of_date => p_as_of_date
1630 );
1631
1632 IF l_line_abatements = -99 THEN
1633
1634 p_error := 'Error getting line abatements';
1635 p_error_code := -99;
1636 l_line_abatements := 0;
1637
1638 ELSE
1639
1640 p_error := 'Success';
1641 p_error_code := 0;
1642
1643 END IF;
1644
1645 pnp_debug_pkg.log('Abatements :'||l_line_abatements);
1646
1647 END IF;
1648
1649
1650 pnp_debug_pkg.log('Error Code 25 : '||p_error_code);
1651 IF p_error_code <> -99 THEN
1652
1653 /* Now apply the constraints on the actual recovery */
1654
1655 l_constrained_actual := l_ten_actual_recovery;
1656 l_constrained_budget := l_budget_recovery;
1657
1658 /* Apply the Amount and Rate Constraints */
1659
1660 l_rate_amt_exists := FALSE;
1661
1662 FOR l_count in 1 .. line_constr_tbl.count
1663 LOOP
1664
1665 IF (line_constr_tbl(l_count).scope = 'PRSH' and
1666 line_constr_tbl(l_count).relation = 'MIN') THEN
1667
1668 l_rate_amt_exists := TRUE;
1669
1670 IF l_ten_actual_recovery < line_constr_tbl(l_count).value THEN
1671 l_constrained_actual := line_constr_tbl(l_count).value;
1672 ELSE
1673 l_constrained_actual := l_ten_actual_recovery;
1674 END IF;
1675
1676 IF l_budget_recovery < line_constr_tbl(l_count).value THEN
1677 l_constrained_budget := line_constr_tbl(l_count).value;
1678 ELSE
1679 l_constrained_budget := l_budget_recovery;
1680 END IF;
1681
1682 ELSIF (line_constr_tbl(l_count).scope = 'PRSH' and
1683 line_constr_tbl(l_count).relation = 'MAX') THEN
1684
1685 l_rate_amt_exists := TRUE;
1686
1687
1688 IF l_ten_actual_recovery > line_constr_tbl(l_count).value THEN
1689 l_constrained_actual := line_constr_tbl(l_count).value;
1690 ELSE
1691 l_constrained_actual := l_ten_actual_recovery;
1692 END IF;
1693
1694 IF l_budget_recovery > line_constr_tbl(l_count).value THEN
1695 l_constrained_budget := line_constr_tbl(l_count).value;
1696 ELSE
1697 l_constrained_budget := l_budget_recovery;
1698 END IF;
1699
1700 ELSIF (line_constr_tbl(l_count).scope = 'RATE'
1701 and line_constr_tbl(l_count).relation = 'MIN') THEN
1702
1703 l_rate_amt_exists := TRUE;
1704
1705 pnp_debug_pkg.log('Min Rate Cons - amt per sqft : '||l_amount_per_sft);
1706 pnp_debug_pkg.log('Min Rate Cons - Cons. value : '||line_constr_tbl(l_count).value);
1707 l_rate_amt_exists := TRUE;
1708
1709 IF l_amount_per_sft < line_constr_tbl(l_count).value THEN
1710 l_constrained_actual :=
1711 (line_constr_tbl(l_count).value * agr_lines_record.multiple_pct/100) *
1712 ((l_ten_recoverable_area* l_ten_occupancy_pct)/100);
1713 ELSE
1714 l_constrained_actual := l_ten_actual_recovery;
1715 END IF;
1716
1717 IF l_budget_amount_per_sft < line_constr_tbl(l_count).value THEN
1718 l_constrained_budget :=
1719 (line_constr_tbl(l_count).value * agr_lines_record.multiple_pct/100) *
1720 ((l_ten_recoverable_area* l_ten_occupancy_pct)/100);
1721 ELSE
1722 l_constrained_budget := l_budget_recovery;
1723 END IF;
1724
1725
1726 ELSIF (line_constr_tbl(l_count).scope = 'RATE'
1727 and line_constr_tbl(l_count).relation = 'MAX') THEN
1728
1729 l_rate_amt_exists := TRUE;
1730
1731 pnp_debug_pkg.log('Max Rate Cons - amt per sqft : '||l_amount_per_sft);
1732 pnp_debug_pkg.log('Max Rate Cons - Cons. value : '||line_constr_tbl(l_count).value);
1733
1734 IF l_amount_per_sft > line_constr_tbl(l_count).value THEN
1735 l_constrained_actual :=
1736 (line_constr_tbl(l_count).value * agr_lines_record.multiple_pct/100) *
1737 ((l_ten_recoverable_area*l_ten_occupancy_pct)/100);
1738 ELSE
1739 l_constrained_actual := l_ten_actual_recovery;
1740 END IF;
1741
1742 IF l_budget_amount_per_sft > line_constr_tbl(l_count).value THEN
1743 l_constrained_budget :=
1744 (line_constr_tbl(l_count).value * agr_lines_record.multiple_pct/100) *
1745 ((l_ten_recoverable_area*l_ten_occupancy_pct)/100);
1746 ELSE
1747 l_constrained_budget := l_budget_recovery;
1748 END IF;
1749
1750 END IF;
1751 END LOOP;
1752 END IF;
1753
1754 pnp_debug_pkg.log('Constrained Actual after min/max cons : '||l_constrained_actual);
1755 pnp_debug_pkg.log('Constrained Budget after min/max cons : '||l_constrained_budget);
1756
1757 /* If rate or amount type of constraints do not exists only then
1758 apply the %age over prior year amount or %age over prior year
1759 cap constraint */
1760
1761 pnp_debug_pkg.log('Error Code 26 : '||p_error_code);
1762 IF p_error_code <> -99 AND not l_rate_amt_exists THEN
1763
1764 /* verify that both %age over prior year amount or %age over prior year
1765 cap type of constraints DO NOT exist */
1766
1767 l_opya_exists := FALSE;
1768 l_opyc_exists := FALSE;
1769
1770 FOR l_count in 1 .. line_constr_tbl.count
1771 LOOP
1772
1773 IF (line_constr_tbl(l_count).scope = 'OPYA'
1774 and line_constr_tbl(l_count).relation = 'MAX') THEN
1775
1776 l_opya_exists := TRUE;
1777
1778 ELSIF (line_constr_tbl(l_count).scope = 'OPYC'
1779 and line_constr_tbl(l_count).relation = 'MAX') THEN
1780
1781 l_opyc_exists := TRUE;
1782
1783 END IF;
1784 END LOOP;
1785
1786 /* Error if both % over prior year actual and % over prior
1787 year cap is entered */
1788
1789 IF l_opya_exists and l_opyc_exists THEN
1790
1791 fnd_message.set_name ('PN','PN_RECALC_CAP_EXT');
1792 pnp_debug_pkg.put_log_msg(fnd_message.get);
1793
1794 p_error := 'Both cumulative and non-cumulative caps exists.';
1795 p_error_code := -99;
1796
1797 ELSE
1798
1799 /* Apply the % over prior year constraint */
1800
1801 FOR l_count in 1 .. line_constr_tbl.count
1802 LOOP
1803
1804 /* For the 1st calculation period there will be no
1805 prior period and hence the l_prior_period_amount
1806 is set to -1 */
1807
1808 IF (line_constr_tbl(l_count).scope = 'OPYA'
1809 and line_constr_tbl(l_count).relation = 'MAX')
1810 and l_prior_period_amount <> -1 THEN
1811
1812 pnp_debug_pkg.log('Prior Period Amount :'||l_prior_period_amount);
1813 pnp_debug_pkg.log('OPYA - Actual Recovery :'||l_ten_actual_recovery);
1814 pnp_debug_pkg.log('OPYA - Budget Recovery :'||l_budget_recovery);
1815
1816 IF l_ten_actual_recovery <
1817 ((line_constr_tbl(l_count).value*l_prior_period_amount/100)+
1818 l_prior_period_amount) THEN
1819
1820 l_constrained_actual := l_ten_actual_recovery;
1821
1822 ELSE
1823
1824 l_constrained_actual :=
1825 ((line_constr_tbl(l_count).value*l_prior_period_amount/100)+
1826 l_prior_period_amount);
1827
1828 END IF;
1829
1830 IF l_budget_recovery <
1831 ((line_constr_tbl(l_count).value*l_prior_period_amount/100)+
1832 l_prior_period_amount) THEN
1833
1834 l_constrained_budget := l_budget_recovery;
1835
1836 ELSE
1837
1838 l_constrained_budget :=
1839 ((line_constr_tbl(l_count).value*l_prior_period_amount/100) +
1840 l_prior_period_amount);
1841
1842 END IF;
1843
1844 ELSIF (line_constr_tbl(l_count).scope = 'OPYC'
1845 and line_constr_tbl(l_count).relation = 'MAX')
1846 and l_prior_period_cap <> -1 THEN
1847
1848 pnp_debug_pkg.log('Prior Period Cap :'||l_prior_period_cap);
1849 pnp_debug_pkg.log('OPYC - Actual Recovery :'||l_ten_actual_recovery);
1850 pnp_debug_pkg.log('OPYC - Budget Recovery :'||l_budget_recovery);
1851
1852 IF l_ten_actual_recovery < l_prior_period_cap THEN
1853
1854 l_constrained_actual := l_ten_actual_recovery;
1855
1856 ELSE
1857
1858 l_constrained_actual := l_prior_period_cap;
1859
1860 END IF;
1861
1862 IF l_budget_recovery < l_prior_period_cap THEN
1863
1864 l_constrained_budget := l_budget_recovery;
1865
1866 ELSE
1867
1868 l_constrained_budget := l_prior_period_cap;
1869
1870 END IF;
1871
1872 END IF;
1873
1874 END LOOP;
1875
1876 pnp_debug_pkg.log('Constrained Actual :'||l_constrained_actual);
1877 pnp_debug_pkg.log('Constrained Budget :'||l_constrained_budget);
1878
1879 END IF;
1880
1881 END IF;
1882
1883 pnp_debug_pkg.log('Error Code 27 : '||p_error_code);
1884
1885 IF p_error_code <> -99 THEN
1886
1887 /* Apply abatements to actual */
1888
1889 IF nvl(l_constrained_actual,0) > nvl(l_line_abatements,0) THEN
1890
1891 l_actual_prorata_share := nvl(l_constrained_actual,0) - nvl(l_line_abatements,0);
1892
1893 ELSE
1894
1895 l_actual_prorata_share := 0;
1896
1897 END IF;
1898
1899 pnp_debug_pkg.log('Actual Prorata Share :'||l_actual_prorata_share);
1900
1901 /* Apply abatements to Budget */
1902
1903 IF nvl(l_constrained_budget,0) > nvl(l_line_abatements,0) THEN
1904
1905 l_budget_prorata_share := nvl(l_constrained_budget,0) - nvl(l_line_abatements,0);
1906
1907 ELSE
1908
1909 l_budget_prorata_share := 0;
1910
1911 END IF;
1912
1913 pnp_debug_pkg.log('Budget Prorata Share :'||l_budget_prorata_share);
1914
1915 /* Apply Billed Recovery to actual */
1916
1917 IF nvl(l_actual_prorata_share,0) > nvl(l_billed_recovery,0) THEN
1918
1919 l_reconciled_amount := nvl(l_actual_prorata_share,0) - nvl(l_billed_recovery,0);
1920
1921 ELSE
1922
1923 /* If -ve rent, check if we need to credit. If yes then credit else set to 0 */
1924
1925 IF NVL(l_negative_recovery,'IGNORE') = 'IGNORE' THEN
1926
1927 l_reconciled_amount := 0;
1928
1929 ELSE
1930
1931 l_reconciled_amount := nvl(l_actual_prorata_share,0) - nvl(l_billed_recovery,0);
1932
1933 END IF;
1934
1935 END IF;
1936
1937 pnp_debug_pkg.log('Reconciled Amount :'||l_reconciled_amount);
1938
1939 END IF;
1940
1941 pnp_debug_pkg.log('Error Code 28 : '||p_error_code);
1942 IF p_error_code <> -99 THEN
1943
1944 /* Check if recovery amount has already been calculated for the period start and
1945 end dates and the as of date.If it has not, then insert a new record into the
1946 PN_REC_PERIOD_LINES_ALL table with the calculated values or else update the
1947 existing record with the values as a result of the re-calculation. */
1948
1949 l_rec_period_lines_id := PN_REC_CALC_PKG.find_if_period_line_exists(
1950 p_rec_agr_line_id => agr_lines_record.rec_agr_line_id
1951 ,p_rec_calc_period_id => p_rec_calc_period_id);
1952
1953 IF l_rec_period_lines_id = -99 THEN
1954
1955 p_error := 'Error checking for period line ';
1956 p_error_code := -99;
1957
1958 ELSE
1959
1960 p_error := 'Success';
1961 p_error_code := 0;
1962
1963 END IF;
1964
1965 pnp_debug_pkg.log('Recovery period line id :'||l_rec_period_lines_id);
1966
1967 END IF;
1968
1969 pnp_debug_pkg.log('Error Code 29 : '||p_error_code);
1970 IF p_error_code <> -99 and l_rec_period_lines_id IS NULL THEN
1971
1972 pnp_debug_pkg.log('Inserting into PN_REC_PERIOD_LINES_ALL ');
1973
1974 IF p_error_code = -99 THEN
1975 l_status := 'Error';
1976 ELSE
1977 l_status := 'COMPLETE';
1978 END IF;
1979
1980 PN_REC_CALC_PKG.INSERT_PERIOD_LINES_ROW(
1981 X_ROWID => l_rowId
1982 ,X_REC_PERIOD_LINES_ID => l_rec_period_lines_id
1983 ,X_BUDGET_PCT => l_BUDGET_PCT
1984 ,X_OCCUPANCY_PCT => l_ten_occupancy_pct
1985 ,X_MULTIPLE_PCT => agr_lines_record.MULTIPLE_PCT
1986 ,X_FIXED_PCT => agr_lines_record.fixed_pct
1987 ,X_TENANCY_START_DATE => l_tenancy_start_date
1988 ,X_TENANCY_END_DATE => l_tenancy_end_date
1989 ,X_STATUS => l_status
1990 ,X_BUDGET_PRORATA_SHARE => l_BUDGET_PRORATA_SHARE
1991 ,X_BUDGET_COST_PER_AREA => l_BUDGET_COST_PER_AREA
1992 ,X_TOTAL_AREA => l_tot_prop_area
1993 ,X_TOTAL_EXPENSE => l_line_expenses
1994 ,X_RECOVERABLE_AREA => l_ten_recoverable_area
1995 ,X_ACTUAL_RECOVERY => l_ten_actual_recovery
1996 ,X_CONSTRAINED_ACTUAL => l_constrained_actual
1997 ,X_ABATEMENTS => l_line_abatements
1998 ,X_ACTUAL_PRORATA_SHARE => l_actual_prorata_share
1999 ,X_BILLED_RECOVERY => l_billed_recovery
2000 ,X_RECONCILED_AMOUNT => l_reconciled_amount
2001 ,X_BUDGET_RECOVERY => l_BUDGET_RECOVERY
2002 ,X_BUDGET_EXPENSE => l_BUDGET_EXPENSE
2003 ,X_REC_CALC_PERIOD_ID => p_REC_CALC_PERIOD_ID
2004 ,X_REC_AGR_LINE_ID => agr_lines_record.REC_AGR_LINE_ID
2005 ,X_AS_OF_DATE => p_AS_OF_DATE
2006 ,X_START_DATE => p_calc_period_START_DATE
2007 ,X_END_DATE => p_calc_period_END_DATE
2008 ,X_BILLING_TYPE => agr_lines_record.type
2009 ,X_BILLING_PURPOSE => agr_lines_record.purpose
2010 ,X_CUST_ACCOUNT_ID => p_customer_id
2011 ,X_CREATION_DATE => l_creation_date
2012 ,X_CREATED_BY => l_created_by
2013 ,X_LAST_UPDATE_DATE => l_creation_date
2014 ,X_LAST_UPDATED_BY => l_created_by
2015 ,X_LAST_UPDATE_LOGIN => l_created_by
2016 ,X_ERROR_CODE => l_error_code);
2017
2018 IF l_error_code = -99 THEN
2019
2020 p_error := 'Error inserting into period lines';
2021 p_error_code := -99;
2022
2023 ELSE
2024
2025 p_error := 'Success';
2026 p_error_code := 0;
2027
2028 END IF;
2029
2030 ELSIF p_error_code <> -99 AND l_rec_period_lines_id is not null THEN
2031 pnp_debug_pkg.log('Error Code 30 : '||p_error_code);
2032
2033 pnp_debug_pkg.log('Updating PN_REC_PERIOD_LINES_ALL ');
2034
2035 IF p_error_code = -99 THEN
2036 l_status := 'Error';
2037 ELSE
2038 l_status := 'COMPLETE';
2039 END IF;
2040
2041 PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW(
2042 X_REC_PERIOD_LINES_ID => l_rec_period_lines_id
2043 ,X_BUDGET_PCT => l_BUDGET_PCT
2044 ,X_OCCUPANCY_PCT => l_ten_occupancy_pct
2045 ,X_MULTIPLE_PCT => agr_lines_record.MULTIPLE_PCT
2046 ,X_FIXED_PCT => agr_lines_record.fixed_pct
2047 ,X_TENANCY_START_DATE => l_TENANCY_START_DATE
2048 ,X_TENANCY_END_DATE => l_TENANCY_END_DATE
2049 ,X_STATUS => l_status
2050 ,X_BUDGET_PRORATA_SHARE => l_BUDGET_PRORATA_SHARE
2051 ,X_BUDGET_COST_PER_AREA => l_BUDGET_COST_PER_AREA
2052 ,X_TOTAL_AREA => l_tot_prop_area
2053 ,X_TOTAL_EXPENSE => l_line_expenses
2054 ,X_RECOVERABLE_AREA => l_ten_recoverable_area
2055 ,X_ACTUAL_RECOVERY => l_ten_actual_recovery
2056 ,X_CONSTRAINED_ACTUAL => l_constrained_actual
2057 ,X_ABATEMENTS => l_line_abatements
2058 ,X_ACTUAL_PRORATA_SHARE => l_actual_prorata_share
2059 ,X_BILLED_RECOVERY => l_billed_recovery
2060 ,X_RECONCILED_AMOUNT => l_reconciled_amount
2061 ,X_BUDGET_RECOVERY => l_BUDGET_RECOVERY
2062 ,X_BUDGET_EXPENSE => l_BUDGET_EXPENSE
2063 ,X_REC_CALC_PERIOD_ID => p_REC_CALC_PERIOD_ID
2064 ,X_REC_AGR_LINE_ID => agr_lines_record.REC_AGR_LINE_ID
2065 ,X_AS_OF_DATE => p_AS_OF_DATE
2066 ,X_START_DATE => p_calc_period_START_DATE
2067 ,X_END_DATE => p_calc_period_END_DATE
2068 ,X_BILLING_TYPE => agr_lines_record.type
2069 ,X_BILLING_PURPOSE => agr_lines_record.purpose
2070 ,X_CUST_ACCOUNT_ID => p_customer_id
2071 ,X_LAST_UPDATE_DATE => l_creation_date
2072 ,X_LAST_UPDATED_BY => l_created_by
2073 ,X_LAST_UPDATE_LOGIN => l_created_by
2074 ,X_ERROR_CODE => l_error_code);
2075
2076 IF l_error_code = -99 THEN
2077
2078 p_error := 'Error updating into period lines';
2079 p_error_code := -99;
2080
2081 ELSE
2082
2083 p_error := 'Success';
2084 p_error_code := 0;
2085
2086 END IF;
2087
2088 END IF;
2089
2090
2091 IF p_error_code = -99 THEN
2092 l_error_lines := l_error_lines + 1;
2093 ELSE
2094 l_success_lines := l_success_lines + 1;
2095 END IF;
2096
2097 END LOOP;
2098
2099 pnp_debug_pkg.put_log_msg(' ');
2100 pnp_debug_pkg.put_log_msg('===============================================================================');
2101
2102 fnd_message.set_name ('PN','PN_RECALC_LINE_PROC');
2103 fnd_message.set_token ('NUM', l_total_lines);
2104 pnp_debug_pkg.put_log_msg(fnd_message.get);
2105
2106 fnd_message.set_name ('PN','PN_RECALC_LINE_SUC');
2107 fnd_message.set_token ('NUM', l_success_lines);
2108 pnp_debug_pkg.put_log_msg(fnd_message.get);
2109
2110 fnd_message.set_name ('PN','PN_RECALC_LINE_FAIL');
2111 fnd_message.set_token ('NUM', l_error_lines);
2112 pnp_debug_pkg.put_log_msg(fnd_message.get);
2113
2114 pnp_debug_pkg.put_log_msg('===============================================================================');
2115
2116 IF agr_lines_all%ISOPEN THEN
2117 CLOSE agr_lines_all;
2118 ELSIF agr_lines_one%ISOPEN THEN
2119 CLOSE agr_lines_one;
2120 END IF;
2121
2122 /* Commit the record in pn_rec_period_lines */
2123
2124 COMMIT;
2125
2126 IF l_consolidate = 'Y' THEN
2127
2128 /* Check to see if calculation has been successfully done for all lines */
2129
2130 /* Check to see if there are period lines with error status */
2131 OPEN csr_check_line_status;
2132 FETCH csr_check_line_status into l_open_exists;
2133 IF csr_check_line_status%NOTFOUND THEN
2134 l_open_exists := 'N';
2135 END IF;
2136 CLOSE csr_check_line_status;
2137
2138 /* Check to see if calculation has not been done for a line */
2139 OPEN csr_check_period_line;
2140 FETCH csr_check_period_line into l_no_prd_line;
2141 IF csr_check_period_line%NOTFOUND THEN
2142 l_no_prd_line := 'N';
2143 END IF;
2144 CLOSE csr_check_period_line;
2145
2146
2147 END IF;
2148
2149
2150 pnp_debug_pkg.log('Consolidate Terms - Yes/No :'||l_consolidate);
2151
2152 IF l_consolidate = 'Y' and
2153 (l_open_exists = 'Y' OR l_no_prd_line = 'Y') THEN
2154
2155 fnd_message.set_name ('PN','PN_RECALC_LN_INCOM');
2156 pnp_debug_pkg.put_log_msg(fnd_message.get);
2157
2158 fnd_message.set_name ('PN','PN_RECALC_BT_NOT_CRTD');
2159 pnp_debug_pkg.put_log_msg(fnd_message.get);
2160
2161 p_error := 'Calculation not successful for all lines';
2162 p_error_code := -99;
2163
2164 ELSIF (l_consolidate = 'Y' and (l_open_exists = 'N' AND l_no_prd_line = 'N')) OR
2165 (l_consolidate = 'N') THEN
2166
2167 pnp_debug_pkg.log('Creating Term(s).....');
2168
2169 pnp_debug_pkg.log('Consolidate Terms - Yes/No :'||l_consolidate);
2170
2171 IF l_calculate_all AND l_consolidate = 'Y' THEN
2172
2173 /* get the sum of reconciled amount for all the lines for the
2174 recovery agreement */
2175
2176 pnp_debug_pkg.log('Opening cursor for Calculate All and Consolidate Terms');
2177 pnp_debug_pkg.log('Agreement Id '|| p_rec_agreement_id);
2178
2179 OPEN calc_all_cons;
2180
2181 ELSIF l_calculate_all AND l_consolidate = 'N' THEN
2182
2183 /* get the reconciled amounts for all the lines of the agreement for which
2184 calculation has been successfully */
2185
2186 pnp_debug_pkg.log('Opening cursor for Calculate All and no consolidation');
2187 pnp_debug_pkg.log('Agreement Id '|| p_rec_agreement_id);
2188
2189 OPEN calc_all_no_cons;
2190
2191 ELSIF not l_calculate_all AND l_consolidate = 'Y' THEN
2192
2193 /* get the sum of reconciled amount for all the lines for the
2194 recovery agreement of the line passed to the routine */
2195
2196 pnp_debug_pkg.log('Opening cursor for Calculate and Consolidate Terms');
2197 pnp_debug_pkg.log('Agreement Id '|| p_rec_agreement_id);
2198
2199 OPEN calc_all_cons;
2200
2201 ELSIF not l_calculate_all AND l_consolidate = 'N' THEN
2202
2203 /* get the reconciled amount for the lines which was passed to the calculate
2204 routine and for which the calculation has been successful */
2205
2206 pnp_debug_pkg.log('Opening cursor for Calculate and no consolidation');
2207 pnp_debug_pkg.log('Agreement Line Id '|| p_rec_agr_line_id);
2208 OPEN calc_no_cons;
2209
2210 END IF;
2211
2212 LOOP
2213
2214 IF calc_all_cons%ISOPEN THEN
2215
2216 FETCH calc_all_cons INTO calc_all_cons_rec;
2217 EXIT WHEN calc_all_cons%NOTFOUND;
2218
2219 ELSIF calc_all_no_cons%ISOPEN THEN
2220
2221 FETCH calc_all_no_cons INTO calc_all_no_cons_rec;
2222 EXIT WHEN calc_all_no_cons%NOTFOUND;
2223
2224 ELSIF calc_no_cons%ISOPEN THEN
2225
2226 FETCH calc_no_cons INTO calc_no_cons_rec;
2227 EXIT WHEN calc_no_cons%NOTFOUND;
2228
2229 END IF;
2230
2231 IF (l_consolidate = 'Y')THEN
2232 l_rec_agr_line_id := -1;
2233 l_end_date := p_calc_period_end_date;
2234 l_reconciled_amount := calc_all_cons_rec.reconciled_amount;
2235 l_rec_agr_line_id := NULL;
2236 ELSE
2237
2238 IF calc_all_no_cons%ISOPEN THEN
2239
2240 l_rec_agr_line_id := calc_all_no_cons_rec.rec_agr_line_id;
2241 l_end_date := calc_all_no_cons_rec.end_date;
2242 l_reconciled_amount := calc_all_no_cons_rec.reconciled_amount;
2243 l_billing_type := calc_all_no_cons_rec.billing_type;
2244 l_billing_purpose := calc_all_no_cons_rec.billing_purpose;
2245
2246 ELSIF calc_no_cons%ISOPEN THEN
2247
2248 l_rec_agr_line_id := calc_no_cons_rec.rec_agr_line_id;
2249 l_end_date := calc_no_cons_rec.end_date;
2250 l_reconciled_amount := calc_no_cons_rec.reconciled_amount;
2251 l_billing_type := calc_no_cons_rec.billing_type;
2252 l_billing_purpose := calc_no_cons_rec.billing_purpose;
2253
2254 END IF;
2255
2256 END IF;
2257
2258 pnp_debug_pkg.log('Line Id :'||l_rec_agr_line_id);
2259 pnp_debug_pkg.log('Term start Date :'||l_end_date);
2260 pnp_debug_pkg.log(' Lease Id :'||p_lease_id);
2261 pnp_debug_pkg.log(' Reconciled Amount:'||l_reconciled_amount);
2262 pnp_debug_pkg.log('Calc Period Id :'||p_rec_calc_period_id);
2263 pnp_debug_pkg.log('Calc Period End date :'||l_end_date);
2264 pnp_debug_pkg.log('Agreement Id :'||p_rec_agreement_id);
2265 pnp_debug_pkg.log('Location Id :'||p_location_id);
2266 pnp_debug_pkg.log('Billing Type :'||l_billing_type);
2267 pnp_debug_pkg.log('Billing Purpose :'||l_billing_purpose);
2268
2269 IF l_rec_agr_line_id is null THEN
2270 l_rec_agr_line_id := -1;
2271 pnp_debug_pkg.log('Set Line Id to -1');
2272 END IF;
2273
2274 PN_REC_CALC_PKG.create_payment_terms(
2275 p_lease_id => p_lease_id
2276 ,p_payment_amount => l_reconciled_amount
2277 ,p_rec_calc_period_id => p_rec_calc_period_id
2278 ,p_calc_period_end_date => l_end_date
2279 ,p_rec_agreement_id => p_rec_agreement_id
2280 ,p_rec_agr_line_id => l_rec_agr_line_id
2281 ,p_location_id => p_location_id
2282 ,p_amount_type => 'CAM'
2283 ,p_org_id => l_org_id
2284 ,p_billing_type => l_billing_type
2285 ,p_billing_purpose => l_billing_purpose
2286 ,p_customer_id => p_customer_id
2287 ,p_cust_site_id => p_cust_site_id
2288 ,p_consolidate => l_consolidate
2289 ,p_error => p_error
2290 ,p_error_code => p_error_code
2291 );
2292
2293 IF p_error_code = -99 THEN
2294 IF l_consolidate = 'Y' AND l_calculate_all THEN
2295
2296 ROLLBACK;
2297 UPDATE pn_rec_period_lines_all
2298 SET STATUS = 'Error'
2299 WHERE rec_agr_line_id in (SELECT rec_agr_line_id
2300 FROM PN_REC_AGR_LINES_ALL
2301 WHERE rec_agreement_id = p_rec_agreement_id)
2302 AND start_date = p_calc_period_start_date
2303 AND end_date = p_calc_period_end_date
2304 AND rec_calc_period_id = p_rec_calc_period_id;
2305 COMMIT;
2306
2307 ELSE
2308
2309 ROLLBACK;
2310 UPDATE pn_rec_period_lines_all
2311 SET STATUS = 'Error'
2312 WHERE rec_agr_line_id = l_rec_agr_line_id
2313 AND start_date = p_calc_period_start_date
2314 AND end_date = p_calc_period_end_date
2315 AND rec_calc_period_id = p_rec_calc_period_id;
2316 COMMIT;
2317
2318 END IF;
2319
2320 ELSE
2321
2322 COMMIT;
2323
2324 END IF;
2325
2326 END LOOP;
2327
2328 END IF;
2329
2330 pnp_debug_pkg.log('PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT (-) ');
2331
2332 EXCEPTION
2333
2334 When OTHERS Then
2335 pnp_debug_pkg.log('PN_REC_CALC_PKG.CALCULATE_REC_AMOUNT '|| to_char(sqlcode));
2336
2337 END CALCULATE_REC_AMOUNT;
2338
2339 /*===========================================================================+
2340 | FUNCTION
2341 | GET_RECOVERABLE_AREA
2342 |
2343 | DESCRIPTION
2344 | Gets recoverable area from pn_rec_period_lines_all table
2345 | for a line if the calc method is 'Fixed rate'
2346 |
2347 | SCOPE - PUBLIC
2348 |
2349 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2350 |
2351 | ARGUMENTS : IN:
2352 | p_rec_period_lines_id
2353 | p_rec_agr_line_id
2354 | p_start_date
2355 | p_end_date
2356 | p_as_of_date
2357 |
2358 | OUT:
2359 |
2360 | RETURNS : None
2361 |
2362 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2363 | for a line if the calc method is 'Fixed rate'
2364 |
2365 | MODIFICATION HISTORY
2366 |
2367 | 19-MAY-2003 Daniel Thota o Created
2368 +===========================================================================*/
2369 FUNCTION get_recoverable_area (
2370 p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
2371 ,p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
2372 )
2373 RETURN pn_rec_period_lines_all.recoverable_area%TYPE IS
2374
2375 l_recoverable_area pn_rec_period_lines_all.recoverable_area%TYPE;
2376
2377 BEGIN
2378
2379 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_recoverable_area (+) ');
2380
2381 SELECT NVL(plines.recoverable_area,0)
2382 INTO l_recoverable_area
2383 FROM pn_rec_period_lines_all plines
2384 WHERE plines.rec_agr_line_id = p_rec_agr_line_id
2385 AND plines.rec_calc_period_id = p_rec_calc_period_id
2386 ;
2387
2388 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_recoverable_area (-) ');
2389
2390 RETURN l_recoverable_area;
2391
2392
2393 EXCEPTION
2394
2395 WHEN OTHERS
2396 THEN
2397 fnd_message.set_name ('PN','PN_RECALB_TNT_AR');
2398 pnp_debug_pkg.put_log_msg(fnd_message.get||' '||to_char(sqlcode));
2399 RETURN -99;
2400
2401
2402 END get_recoverable_area;
2403
2404 /*===========================================================================+
2405 | FUNCTION
2406 | GET_TOT_PROP_AREA
2407 |
2408 | DESCRIPTION
2409 | Gets recoverable area from pn_rec_period_lines_all table
2410 | for a line if the calc method is 'Fixed rate'
2411 |
2412 | SCOPE - PUBLIC
2413 |
2414 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2415 |
2416 | ARGUMENTS : IN:
2417 | p_rec_period_lines_id
2418 | p_rec_agr_line_id
2419 | p_start_date
2420 | p_end_date
2421 | p_as_of_date
2422 |
2423 | OUT:
2424 |
2425 | RETURNS : None
2426 |
2427 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2428 | for a line if the calc method is 'Fixed rate'
2429 |
2430 | MODIFICATION HISTORY
2431 |
2432 | 19-MAY-2003 Daniel Thota o Created
2433 | 22-Aug-2003 Ashish oBug#3107849 added the code to return
2434 | the total_area based on area_type
2435 +===========================================================================*/
2436 FUNCTION get_tot_prop_area (
2437 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
2438 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
2439 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
2440 ,p_location_id pn_rec_agreements_all.location_id%TYPE
2441 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
2442 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
2443 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
2444 )
2445 RETURN pn_rec_arcl_dtl_all.TOTAL_assignable_area%TYPE IS
2446
2447 l_tot_prop_area pn_rec_arcl_dtl_all.TOTAL_assignable_area%TYPE;
2448 l_total_occp_area pn_rec_arcl_dtl_all.TOTAL_OCCUPIED_AREA%Type;
2449 l_total_wgt_avg_area pn_rec_arcl_dtl_all.TOTAL_WEIGHTED_AVG%Type;
2450 l_floor_pct pn_rec_agr_linarea_all.FLOOR_PCT%Type;
2451 l_area_type pn_rec_agr_linarea_all.area_type%Type;
2452 l_area_class_dtl_id pn_rec_arcl_dtl_all.area_class_dtl_id%Type;
2453 l_asgn_area_contr pn_rec_arcl_dtlln_all.ASSIGNABLE_AREA%Type;
2454 l_occp_area_contr pn_rec_arcl_dtlln_all.occupied_area%Type;
2455 l_wgt_avg_area_contr pn_rec_arcl_dtlln_all.WEIGHTED_AVG%Type;
2456 l_net_asgn_area pn_rec_arcl_dtlln_all.ASSIGNABLE_AREA%Type;
2457 l_net_occp_area pn_rec_arcl_dtlln_all.ASSIGNABLE_AREA%Type;
2458 l_net_wgt_avg_area pn_rec_arcl_dtlln_all.ASSIGNABLE_AREA%Type;
2459 l_floor_area pn_rec_arcl_dtl_all.TOTAL_assignable_area%TYPE;
2460 l_greater_area pn_rec_arcl_dtlln_all.ASSIGNABLE_AREA%Type;
2461 l_context VARCHAR2(2000):= null;
2462
2463 cursor c_area is
2464 SELECT area_class_dtl_hdr.TOTAL_assignable_area
2465 ,nvl(area_class_dtl_hdr.TOTAL_OCCUPIED_AREA_ovr,area_class_dtl_hdr.TOTAL_OCCUPIED_AREA)
2466 ,nvl(area_class_dtl_hdr.TOTAL_WEIGHTED_AVG_ovr, area_class_dtl_hdr.TOTAL_WEIGHTED_AVG)
2467 ,linearea.FLOOR_PCT
2468 ,linearea.area_type
2469 ,area_class_dtl_hdr.area_class_dtl_id
2470 FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
2471 ,pn_rec_arcl_dtl_all area_class_dtl_hdr
2472 ,pn_rec_agr_linarea_all linearea
2473 ,pn_rec_arcl_all aclass
2474 WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
2475 AND p_as_of_date between linearea.start_date and linearea.end_date
2476 AND linearea.area_class_id = aclass.area_class_id
2477 AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
2478 AND area_class_dtl_hdr.as_of_date = p_as_of_date
2479 AND area_class_dtl_hdr.from_date = p_calc_period_start_date
2480 AND area_class_dtl_hdr.to_date = p_calc_period_end_date
2481 AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
2482 AND area_class_dtl_lines.cust_account_id = p_customer_id
2483 AND area_class_dtl_lines.lease_id = p_lease_id
2484 AND area_class_dtl_lines.location_id = p_location_id
2485 ;
2486 BEGIN
2487
2488 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_tot_prop_area (+) ');
2489
2490 l_context := 'getting area type';
2491
2492 open c_area;
2493 fetch c_area INTO l_tot_prop_area ,
2494 l_total_occp_area,
2495 l_total_wgt_avg_area,
2496 l_floor_pct,
2497 l_area_type,
2498 l_area_class_dtl_id;
2499 if c_area%NotFound then
2500 l_tot_prop_area := -99;
2501 close c_area;
2502 fnd_message.set_name ('PN','PN_RECALB_AR_NF');
2503 pnp_debug_pkg.put_log_msg(fnd_message.get);
2504 RETURN l_tot_prop_area;
2505 end if;
2506
2507 l_context := 'getting contributors';
2508
2509 SELECT NVL(SUM(ASSIGNABLE_AREA),0),
2510 NVL(SUM(NVL(occupied_area_ovr, occupied_area)),0),
2511 NVL(SUM (NVL( WEIGHTED_AVG_OVR, WEIGHTED_AVG)),0)
2512 INTO
2513 l_asgn_area_contr,
2514 l_occp_area_contr,
2515 l_wgt_avg_area_contr
2516 FROM pn_rec_arcl_dtlln_all area_class_dtl
2517 WHERE area_class_dtl_line_id = l_area_class_dtl_id
2518 AND exclude_area_ovr_flag = 'Y'
2519 AND include_flag ='Y';
2520
2521 l_context := 'deriving applicable area ';
2522
2523 l_net_asgn_area := l_tot_prop_area - l_asgn_area_contr;
2524 l_net_occp_area := l_total_occp_area - l_occp_area_contr;
2525 l_net_wgt_avg_area := l_total_wgt_avg_area - l_wgt_avg_area_contr;
2526 l_floor_area := l_net_asgn_area*nvl(l_floor_pct,100)/100;
2527 l_greater_area := null;
2528
2529 if l_area_type = 'OCUPD' then
2530 l_greater_area := l_net_occp_area;
2531 elsif l_area_type = 'TAROC' then
2532 if l_floor_area < l_net_occp_area then
2533 l_greater_area := l_net_occp_area;
2534 else
2535 l_greater_area := l_floor_area;
2536 end if;
2537 elsif l_area_type = 'TARWA' then
2538 if l_floor_area < l_net_wgt_avg_area then
2539 l_greater_area := l_net_wgt_avg_area;
2540 else
2541 l_greater_area := l_floor_area;
2542 end if;
2543 elsif l_area_type = 'TASGN' then
2544 l_greater_area := l_net_asgn_area;
2545 elsif l_area_type = 'WTAVG' then
2546 l_greater_area := l_net_wgt_avg_area;
2547 end if;
2548 l_tot_prop_area := l_greater_area;
2549
2550 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_tot_prop_area (-) ');
2551
2552 RETURN l_tot_prop_area;
2553
2554 EXCEPTION
2555
2556 WHEN OTHERS THEN
2557 pnp_debug_pkg.log(substrb('Error in get_tot_prop_area - '|| l_context,1,244));
2558 pnp_debug_pkg.log('Error in get_tot_prop_area - '|| to_char(sqlcode));
2559 RETURN -99;
2560
2561
2562 END get_tot_prop_area;
2563
2564 /*===========================================================================+
2565 | FUNCTION
2566 | TEN_RECOVERABLE_AREA
2567 |
2568 | DESCRIPTION
2569 | Gets recoverable area from pn_rec_period_lines_all table
2570 | for a line if the calc method is 'Fixed rate'
2571 |
2572 | SCOPE - PUBLIC
2573 |
2574 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2575 |
2576 | ARGUMENTS : IN:
2577 | p_rec_period_lines_id
2578 | p_rec_agr_line_id
2579 | p_start_date
2580 | p_end_date
2581 | p_as_of_date
2582 |
2583 | OUT:
2584 |
2585 | RETURNS : None
2586 |
2587 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2588 | for a line if the calc method is 'Fixed rate'
2589 |
2590 | MODIFICATION HISTORY
2591 |
2592 | 19-MAY-2003 Daniel Thota o Created
2593 +===========================================================================*/
2594 FUNCTION ten_recoverable_area (
2595 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
2596 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
2597 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
2598 ,p_location_id pn_rec_agreements_all.location_id%TYPE
2599 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
2600 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
2601 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
2602 )
2603 RETURN ten_recoverable_area_rec IS
2604
2605 l_ten_recoverable_area_rec ten_recoverable_area_rec;
2606
2607 BEGIN
2608
2609 pnp_debug_pkg.log('PN_REC_CALC_PKG.ten_recoverable_area (+) ');
2610
2611 SELECT nvl(area_class_dtl_lines.occupied_area_ovr, area_class_dtl_lines.occupied_area)
2612 ,occupancy_pct
2613 INTO l_ten_recoverable_area_rec
2614 FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
2615 ,pn_rec_arcl_dtl_all area_class_dtl_hdr
2616 ,pn_rec_agr_linarea_all linearea
2617 ,pn_rec_arcl_all aclass
2618 WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
2619 AND p_as_of_date between linearea.start_date and linearea.end_date
2620 AND linearea.area_class_id = aclass.area_class_id
2621 AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
2622 AND area_class_dtl_hdr.as_of_date = p_as_of_date
2623 AND area_class_dtl_hdr.from_date = p_calc_period_start_date
2624 AND area_class_dtl_hdr.to_date = p_calc_period_end_date
2625 AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
2626 AND area_class_dtl_lines.cust_account_id = p_customer_id
2627 AND area_class_dtl_lines.lease_id = p_lease_id
2628 AND area_class_dtl_lines.location_id = p_location_id
2629 AND area_class_dtl_lines.include_flag = 'Y'
2630 ;
2631
2632 pnp_debug_pkg.log('PN_REC_CALC_PKG.ten_recoverable_area (-) ');
2633
2634 RETURN l_ten_recoverable_area_rec;
2635
2636 EXCEPTION
2637
2638 WHEN OTHERS
2639 THEN
2640
2641 pnp_debug_pkg.log('Error while getting tenant occupied area ' || to_char(sqlcode));
2642 l_ten_recoverable_area_rec.occupied_area := -99;
2643 l_ten_recoverable_area_rec.occupancy_pct := -99;
2644 RETURN l_ten_recoverable_area_rec;
2645
2646
2647 END ten_recoverable_area;
2648
2649 /*===========================================================================+
2650 | FUNCTION
2651 | GET_CONTR_ACTUAL_RECOVERY
2652 |
2653 | DESCRIPTION
2654 | Gets actual recovery amount of the contributor(s) to be subtracted from the expenses
2655 |
2656 | SCOPE - PUBLIC
2657 |
2658 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2659 |
2660 | ARGUMENTS : IN:
2661 | p_rec_period_lines_id
2662 | p_rec_agr_line_id
2663 | p_start_date
2664 | p_end_date
2665 | p_as_of_date
2666 |
2667 | OUT:
2668 |
2669 | RETURNS : None
2670 |
2671 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2672 | for a line if the calc method is 'Fixed rate'
2673 |
2674 | MODIFICATION HISTORY
2675 |
2676 | 19-MAY-2003 Daniel Thota o Created
2677 | 04-SEP-2003 Amita Singh o Added new cursor csr_get_line
2678 | Added parameters p_line_purpose,p_line_type
2679 | to cursor chk_contr_calculated.Changed WHERE
2680 | clause in the cursor to use them.
2681 | Fix for bug # 3123283
2682 +===========================================================================*/
2683 FUNCTION get_contr_actual_recovery (
2684 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
2685 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
2686 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
2687 ,p_location_id pn_rec_agreements_all.location_id%TYPE
2688 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
2689 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
2690 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
2691 ,p_called_from VARCHAR2
2692 )
2693 RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
2694
2695 -- Fix for bug # 3123283
2696 CURSOR csr_get_line IS
2697 SELECT purpose, type
2698 FROM pn_rec_agr_lines_all
2699 WHERE rec_agr_line_id = p_rec_agr_line_id;
2700 -- Fix for bug # 3123283
2701
2702 cursor chk_contr_calculated (p_line_purpose VARCHAR2, p_line_type VARCHAR2) is --Fix for bug # 3123283
2703 SELECT 'Y'
2704 FROM dual
2705 WHERE exists(
2706 SELECT 'Y'
2707 FROM pn_rec_period_lines_all period_lines
2708 ,pn_rec_agreements_all recagr
2709 ,pn_rec_agr_lines_all lines
2710 WHERE nvl(period_lines.actual_prorata_share,0) = 0
2711 AND period_lines.start_date = p_calc_period_start_date
2712 AND period_lines.end_date = p_calc_period_end_date
2713 AND period_lines.as_of_date = p_as_of_date
2714 and lines.purpose = p_line_purpose -- Fix for bug # 3123283
2715 and lines.type = p_line_type -- Fix for bug # 3123283
2716 and lines.rec_agr_line_id = period_lines.rec_agr_line_id
2717 and lines.rec_agreement_id = recagr.rec_agreement_id
2718 and (recagr.location_id, recagr.customer_id, recagr.lease_id) in
2719 (
2720 SELECT area_class_dtl_lines.location_id,
2721 area_class_dtl_lines.cust_account_id,
2722 area_class_dtl_lines.lease_id
2723 FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
2724 WHERE area_class_dtl_id =
2725 (SELECT area_class_dtl_hdr.area_class_dtl_id
2726 FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
2727 ,pn_rec_arcl_dtl_all area_class_dtl_hdr
2728 ,pn_rec_agr_linarea_all linearea
2729 ,pn_rec_arcl_all aclass
2730 WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
2731 AND p_as_of_date between linearea.start_date
2732 and linearea.end_date
2733 AND linearea.area_class_id = aclass.area_class_id
2734 AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
2735 AND area_class_dtl_hdr.as_of_date = p_as_of_date
2736 AND area_class_dtl_hdr.from_date = p_calc_period_start_date
2737 AND area_class_dtl_hdr.to_date = p_calc_period_end_date
2738 AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
2739 AND area_class_dtl_lines.cust_account_id = p_customer_id
2740 AND area_class_dtl_lines.lease_id = p_lease_id
2741 AND area_class_dtl_lines.location_id = p_location_id)
2742 AND area_class_dtl_lines.include_flag = 'Y'
2743 AND area_class_dtl_lines.exclude_prorata_ovr_flag = 'Y'
2744 ));
2745
2746 l_contr_actual_recovery pn_rec_period_lines_all.actual_recovery%TYPE := 0;
2747 l_exists VARCHAR2(1) := 'N';
2748
2749 -- Fix for bug # 3123283
2750 l_line_purpose pn_rec_agr_lines_all.PURPOSE%TYPE;
2751 l_line_type pn_rec_agr_lines_all.TYPE%TYPE;
2752
2753 BEGIN
2754
2755 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_contr_actual_recovery (+) ');
2756
2757 -- Fix for bug # 3123283
2758 OPEN csr_get_line;
2759 FETCH csr_get_line into l_line_purpose, l_line_type;
2760 IF csr_get_line%NOTFOUND THEN
2761
2762 CLOSE csr_get_line;
2763 IF p_called_from = 'CALCUI' THEN
2764 return 0;
2765 ELSE
2766 return -99;
2767 END IF;
2768
2769 END IF;
2770
2771 CLOSE csr_get_line;
2772 -- Fix for bug # 3123283
2773
2774 OPEN chk_contr_calculated(l_line_purpose, l_line_type); -- Fix for bug # 3123283
2775 FETCH chk_contr_calculated into l_exists;
2776 IF chk_contr_calculated%FOUND and l_exists = 'Y' THEN
2777 pnp_debug_pkg.log('Calculation has not been done for one of the contributors ');
2778 close chk_contr_calculated;
2779 IF p_called_from = 'CALCUI' THEN
2780 return 0;
2781 ELSE
2782 return -99;
2783 END IF;
2784 ELSE
2785 close chk_contr_calculated;
2786
2787 END IF;
2788
2789 SELECT NVL(SUM(NVL(period_lines.actual_prorata_share,0)),0)
2790 INTO l_contr_actual_recovery
2791 FROM pn_rec_period_lines_all period_lines
2792 ,pn_rec_agreements_all recagr
2793 ,pn_rec_agr_lines_all lines
2794 WHERE period_lines.start_date = p_calc_period_start_date
2795 AND period_lines.end_date = p_calc_period_end_date
2796 AND period_lines.as_of_date = p_as_of_date
2797 AND lines.rec_agr_line_id = period_lines.rec_agr_line_id
2798 AND lines.rec_agreement_id = recagr.rec_agreement_id
2799 AND lines.purpose = l_line_purpose
2800 AND lines.type = l_line_type
2801 AND (recagr.location_id, recagr.customer_id, recagr.lease_id) in
2802 (
2803 SELECT area_class_dtl_lines.location_id,
2804 area_class_dtl_lines.cust_account_id,
2805 area_class_dtl_lines.lease_id
2806 FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
2807 WHERE area_class_dtl_id =
2808 (SELECT area_class_dtl_hdr.area_class_dtl_id
2809 FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
2810 ,pn_rec_arcl_dtl_all area_class_dtl_hdr
2811 ,pn_rec_agr_linarea_all linearea
2812 ,pn_rec_arcl_all aclass
2813 WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
2814 AND p_as_of_date between linearea.start_date
2815 and linearea.end_date
2816 AND linearea.area_class_id = aclass.area_class_id
2817 AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
2818 AND area_class_dtl_hdr.as_of_date = p_as_of_date
2819 AND area_class_dtl_hdr.from_date = p_calc_period_start_date
2820 AND area_class_dtl_hdr.to_date = p_calc_period_end_date
2821 AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
2822 AND area_class_dtl_lines.cust_account_id = p_customer_id
2823 AND area_class_dtl_lines.lease_id = p_lease_id
2824 AND area_class_dtl_lines.location_id = p_location_id)
2825 AND area_class_dtl_lines.include_flag = 'Y'
2826 AND area_class_dtl_lines.exclude_prorata_ovr_flag = 'Y'
2827 );
2828
2829 pnp_debug_pkg.log('get_contr_actual_recovery contributor exp ' ||
2830 to_char(l_contr_actual_recovery));
2831
2832 IF p_called_from = 'CALCUI'AND l_contr_actual_recovery = 0 THEN
2833 RETURN 0;
2834 ELSE
2835 RETURN l_contr_actual_recovery;
2836 END IF;
2837
2838 EXCEPTION
2839
2840 WHEN OTHERS
2841 THEN
2842
2843 fnd_message.set_name ('PN','PN_RECALC_CAL_NOT_CONTRB');
2844 pnp_debug_pkg.put_log_msg(fnd_message.get);
2845 pnp_debug_pkg.put_log_msg(TO_CHAR(sqlcode));
2846 IF p_called_from = 'CALCUI' THEN
2847 RETURN 0;
2848 ELSE
2849 RETURN -99;
2850 END IF;
2851
2852 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_contr_actual_recovery (-) ');
2853
2854 END get_contr_actual_recovery;
2855
2856 /*===========================================================================+
2857 | FUNCTION
2858 | GET_LINE_EXPENSES
2859 |
2860 | DESCRIPTION
2861 | Gets recoverable area from pn_rec_period_lines_all table
2862 | for a line if the calc method is 'Fixed rate'
2863 |
2864 | SCOPE - PUBLIC
2865 |
2866 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2867 |
2868 | ARGUMENTS : IN:
2869 | p_rec_period_lines_id
2870 | p_rec_agr_line_id
2871 | p_start_date
2872 | p_end_date
2873 | p_as_of_date
2874 |
2875 | OUT:
2876 |
2877 | RETURNS : None
2878 |
2879 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2880 | for a line if the calc method is 'Fixed rate'
2881 |
2882 | MODIFICATION HISTORY
2883 |
2884 | 19-MAY-2003 Daniel Thota o Created
2885 +===========================================================================*/
2886 PROCEDURE get_line_expenses (
2887 p_rec_agr_line_id IN NUMBER
2888 ,p_customer_id IN NUMBER
2889 ,p_lease_id IN NUMBER
2890 ,p_location_id IN NUMBER
2891 ,p_calc_period_start_date IN DATE
2892 ,p_calc_period_end_date IN DATE
2893 ,p_calc_period_as_of_date IN DATE
2894 ,p_recoverable_amt IN OUT NOCOPY NUMBER
2895 ,p_fee_before_contr IN OUT NOCOPY NUMBER
2896 ,p_fee_after_contr IN OUT NOCOPY NUMBER
2897 ,p_error IN OUT NOCOPY VARCHAR2
2898 ,p_error_code IN OUT NOCOPY NUMBER
2899 ) IS
2900
2901 l_line_expenses pn_rec_expcl_dtlln_all.computed_recoverable_amt%TYPE;
2902 l_fee_before pn_rec_expcl_dtlln_all.cls_line_fee_before_contr_ovr%TYPE;
2903 l_fee_after pn_rec_expcl_dtlln_all.cls_line_fee_after_contr_ovr%TYPE;
2904
2905 BEGIN
2906
2907 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_line_expenses (+) ');
2908
2909 SELECT nvl(exp_detail_line.computed_recoverable_amt,0),
2910 nvl(exp_detail_line.cls_line_fee_before_contr_ovr,0),
2911 nvl(exp_detail_hdr.cls_line_fee_after_contr,0)
2912 INTO l_line_expenses, l_fee_before, l_fee_after
2913 FROM pn_rec_expcl_all rec_exp_class
2914 ,pn_rec_agr_linexp_all lineexp
2915 ,pn_rec_expcl_dtl_all exp_detail_hdr
2916 ,pn_rec_exp_line_all exp_extract_hdr
2917 ,pn_rec_expcl_dtlln_all exp_detail_line
2918 WHERE exp_detail_hdr.expense_class_dtl_id = exp_detail_line.expense_class_dtl_id
2919 AND exp_detail_line.cust_account_id = p_customer_id
2920 AND exp_detail_line.lease_id = p_lease_id
2921 AND exp_detail_line.location_id = p_location_id
2922 AND exp_extract_hdr.to_date = p_calc_period_end_date
2923 AND exp_extract_hdr.from_date = p_calc_period_start_date
2924 AND exp_extract_hdr.as_of_date = p_calc_period_as_of_date
2925 AND exp_extract_hdr.expense_line_id = exp_detail_hdr.expense_line_id
2926 AND exp_detail_hdr.expense_class_id = rec_exp_class.expense_class_id
2927 AND rec_exp_class.expense_class_id = lineexp.expense_class_id
2928 AND p_calc_period_as_of_date between lineexp.start_date and lineexp.end_date
2929 AND lineexp.rec_agr_line_id = p_rec_agr_line_id
2930 ;
2931
2932 p_recoverable_amt := l_line_expenses;
2933 p_fee_before_contr := l_fee_before;
2934 p_fee_after_contr := l_fee_after;
2935 p_error := 'Success in getting line expenses';
2936 p_error_code := 0;
2937
2938 EXCEPTION
2939
2940 WHEN OTHERS
2941 THEN
2942 p_recoverable_amt := 0;
2943 p_fee_before_contr := 0;
2944 p_fee_after_contr := 0;
2945 p_error := 'Error getting line expenses' || to_char(sqlcode);
2946 p_error_code := -99;
2947
2948 fnd_message.set_name ('PN','PN_RECALB_LNEXP_NF');
2949 pnp_debug_pkg.put_log_msg(fnd_message.get||' '|| to_char(sqlcode));
2950
2951 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_line_expenses (-) ');
2952
2953 END get_line_expenses;
2954
2955 /*===========================================================================+
2956 | FUNCTION
2957 | GET_BUDGET_EXPENSES
2958 |
2959 | DESCRIPTION
2960 | Gets recoverable area from pn_rec_period_lines_all table
2961 | for a line if the calc method is 'Fixed rate'
2962 |
2963 | SCOPE - PUBLIC
2964 |
2965 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2966 |
2967 | ARGUMENTS : IN:
2968 | p_rec_period_lines_id
2969 | p_rec_agr_line_id
2970 | p_start_date
2971 | p_end_date
2972 | p_as_of_date
2973 |
2974 | OUT:
2975 |
2976 | RETURNS : None
2977 |
2978 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
2979 | for a line if the calc method is 'Fixed rate'
2980 |
2981 | MODIFICATION HISTORY
2982 |
2983 | 19-MAY-03 dthota o Created
2984 | 27-AUG-04 abanerje o Modified the select statement to apply the share%
2985 | to the budgeted amount. Bug 3711709.
2986 +===========================================================================*/
2987 FUNCTION get_budget_expenses (
2988 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
2989 ,p_customer_id pn_rec_agreements_all.customer_id%TYPE
2990 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
2991 ,p_location_id pn_rec_agreements_all.location_id%TYPE
2992 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
2993 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
2994 ,p_calc_period_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
2995 )
2996 RETURN pn_rec_expcl_dtlln_all.budgeted_amt%TYPE IS
2997
2998 l_budget_expenses pn_rec_expcl_dtlln_all.budgeted_amt%TYPE;
2999
3000 BEGIN
3001
3002 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_budget_expenses (+) ');
3003 pnp_debug_pkg.log('Agr line ID: '||p_rec_agr_line_id);
3004 pnp_debug_pkg.log('Cust ID: '||p_customer_id);
3005 pnp_debug_pkg.log('lease ID: '||p_lease_id);
3006 pnp_debug_pkg.log('Location ID: '||p_location_id);
3007 pnp_debug_pkg.log('Start Date : '||p_calc_period_start_date);
3008 pnp_debug_pkg.log('End Date : ' ||p_calc_period_end_date);
3009 pnp_debug_pkg.log('As of Date : '||p_calc_period_as_of_date);
3010
3011
3012
3013 SELECT NVL(
3014 SUM(
3015 NVL(expcl_lndtl_alloc.BUDGETED_AMT* (
3016 (NVL
3017 (NVL
3018 (expcl_lndtl_alloc.CLS_LINE_DTL_SHARE_PCT_OVR,
3019 expcl_lndtl_alloc.CLS_LINE_DTL_SHARE_PCT)
3020 ,100)
3021 )/100)
3022 ,0)
3023 ) ,0)
3024 INTO l_budget_expenses
3025 FROM pn_rec_expcl_all rec_exp_class
3026 ,pn_rec_agr_linexp_all lineexp
3027 ,pn_rec_expcl_dtl_all exp_detail_hdr
3028 ,pn_rec_exp_line_all exp_extract_hdr
3029 ,pn_rec_expcl_dtlln_all exp_detail_line
3030 ,pn_rec_expcl_dtlacc_all expcl_lndtl_alloc
3031 WHERE exp_detail_hdr.expense_class_dtl_id = exp_detail_line.expense_class_dtl_id
3032 AND exp_detail_line.cust_account_id = p_customer_id
3033 AND exp_detail_line.lease_id = p_lease_id
3034 AND exp_detail_line.location_id = p_location_id
3035 AND exp_extract_hdr.to_date = p_calc_period_end_date
3036 AND exp_extract_hdr.from_date = p_calc_period_start_date
3037 AND exp_extract_hdr.as_of_date = p_calc_period_as_of_date
3038 AND exp_extract_hdr.expense_line_id = exp_detail_hdr.expense_line_id
3039 AND exp_detail_hdr.expense_class_id = rec_exp_class.expense_class_id
3040 AND rec_exp_class.expense_class_id = lineexp.expense_class_id
3041 AND p_calc_period_as_of_date between lineexp.start_date AND lineexp.end_date
3042 AND lineexp.rec_agr_line_id = p_rec_agr_line_id
3043 AND expcl_lndtl_alloc.expense_class_line_id = exp_detail_line.expense_class_line_id
3044 ;
3045 pnp_debug_pkg.log('Cal exp: '||l_budget_expenses);
3046 RETURN l_budget_expenses;
3047
3048 EXCEPTION
3049
3050 WHEN OTHERS
3051 THEN
3052 fnd_message.set_name ('PN','PN_RECALB_BDEXP_NF');
3053 pnp_debug_pkg.put_log_msg(fnd_message.get||' '|| to_char(sqlcode));
3054
3055 RETURN -99;
3056
3057 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_budget_expenses (-) ');
3058
3059 END get_budget_expenses;
3060
3061 /*===========================================================================+
3062 | FUNCTION
3063 | GET_BILLED_RECOVERY
3064 |
3065 | DESCRIPTION
3066 | Gets recoverable area from pn_rec_period_lines_all table
3067 | for a line if the calc method is 'Fixed rate'
3068 |
3069 | SCOPE - PUBLIC
3070 |
3071 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3072 |
3073 | ARGUMENTS : IN:
3074 | p_rec_period_lines_id
3075 | p_rec_agr_line_id
3076 | p_start_date
3077 | p_end_date
3078 | p_as_of_date
3079 |
3080 | OUT:
3081 |
3082 | RETURNS : None
3083 |
3084 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3085 | for a line if the calc method is 'Fixed rate'
3086 |
3087 | MODIFICATION HISTORY
3088 |
3089 | 19-MAY-2003 Daniel Thota o Created
3090 | 05-Aug-2003 Ashish Kumar oBug#3066286 in the function get_billed_recovery
3091 | remove the code referencing the table PN_REC_LINBILL
3092 | 18-Aug-2003 Ashish Bug #3094082 added the condition
3093 | ppt.currency_code = g_currency_code
3094 | 04-Nov-2003 Daniel Thota o Changed the where clause to account for multi-tenancy
3095 | so that billing terms of a lease are now associated with a location.
3096 | Added a new parameter p_location_id for the function
3097 +===========================================================================*/
3098 FUNCTION get_billed_recovery (
3099 p_payment_purpose pn_rec_agr_lines_all.purpose%TYPE
3100 ,p_payment_type pn_rec_agr_lines_all.type%TYPE
3101 ,p_lease_id pn_rec_agreements_all.lease_id%TYPE
3102 ,p_location_id pn_rec_agreements_all.location_id%TYPE
3103 ,p_calc_period_start_date pn_rec_calc_periods_all.start_date%TYPE
3104 ,p_calc_period_end_date pn_rec_calc_periods_all.end_date%TYPE
3105 ,p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
3106 ,p_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE
3107 )
3108 RETURN pn_rec_period_lines_all.billed_recovery%TYPE IS
3109
3110 l_billed_recovery pn_rec_period_lines_all.billed_recovery%TYPE;
3111 l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE :=
3112 PN_REC_CALC_PKG.find_if_period_line_exists(
3113 p_rec_agr_line_id
3114 ,p_rec_calc_period_id
3115 );
3116
3117 BEGIN
3118
3119 pnp_debug_pkg.log('PN_REC_CALC_PKG.billed_recovery (+) ');
3120
3121
3122 SELECT nvl(SUM(pitem.actual_amount),0)
3123 INTO l_billed_recovery
3124 FROM pn_payment_items_all pitem
3125 ,pn_payment_schedules_all psched
3126 ,pn_payment_terms_all ppt
3127 WHERE psched.payment_status_lookup_code = 'APPROVED'
3128 AND to_date(to_char(psched.schedule_date,'mm/yyyy'),'mm/yyyy') between
3129 to_date(to_char( p_calc_period_start_date,'mm/yyyy'),'mm/yyyy')
3130 and to_date(to_char(p_calc_period_end_date,'mm/yyyy'),'mm/yyyy')
3131 AND psched.lease_id = p_lease_id
3132 AND psched.payment_schedule_id = pitem.payment_schedule_id
3133 AND pitem.payment_item_type_lookup_code = 'CASH'
3134 AND pitem.payment_term_id = ppt.payment_term_id
3135 AND nvl(pitem.export_to_ar_flag,'N') = 'Y'
3136 AND ppt.payment_purpose_code = p_payment_purpose
3137 AND ppt.payment_term_type_code = p_payment_type
3138 AND ppt.start_date <= p_calc_period_end_date
3139 AND ppt.end_date >= p_calc_period_start_date
3140 AND ppt.currency_code = g_currency_code
3141 AND ppt.recoverable_flag = 'Y'
3142 AND ppt.lease_id = p_lease_id
3143 AND ppt.location_id = p_location_id
3144 ;
3145
3146 RETURN l_billed_recovery;
3147
3148 EXCEPTION
3149
3150 WHEN OTHERS
3151 THEN
3152 fnd_message.set_name ('PN','PN_RECALB_BLREC_NF');
3153 pnp_debug_pkg.put_log_msg(fnd_message.get||' '|| to_char(sqlcode));
3154 RETURN -99;
3155
3156 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_billed_recovery (-) ');
3157
3158 END get_billed_recovery;
3159
3160 /*===========================================================================+
3161 | FUNCTION
3162 | GET_LINE_CONSTRAINTS
3163 |
3164 | DESCRIPTION
3165 | Gets recoverable area from pn_rec_period_lines_all table
3166 | for a line if the calc method is 'Fixed rate'
3167 |
3168 | SCOPE - PUBLIC
3169 |
3170 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3171 |
3172 | ARGUMENTS : IN:
3173 | p_rec_period_lines_id
3174 | p_rec_agr_line_id
3175 | p_start_date
3176 | p_end_date
3177 | p_as_of_date
3178 |
3179 | OUT:
3180 |
3181 | RETURNS : None
3182 |
3183 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3184 | for a line if the calc method is 'Fixed rate'
3185 |
3186 | MODIFICATION HISTORY
3187 |
3188 | 19-MAY-2003 Daniel Thota o Created
3189 +===========================================================================*/
3190 FUNCTION get_line_constraints (
3191 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
3192 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
3193 )
3194 RETURN g_line_constr_type IS
3195
3196 CURSOR get_line_constr_csr IS
3197 SELECT CONSTR_ORDER,
3198 SCOPE,
3199 RELATION,
3200 VALUE,
3201 CPI_INDEX,
3202 BASE_YEAR
3203 FROM PN_REC_AGR_LINCONST_ALL lineconst
3204 WHERE lineconst.rec_agr_line_id = p_rec_agr_line_id
3205 AND p_as_of_date between lineconst.start_date and lineconst.end_date
3206 ;
3207
3208 /* PL/SQL table to store the constraints details */
3209 line_constr_tbl g_line_constr_type;
3210
3211 i NUMBER :=0;
3212
3213 BEGIN
3214
3215 pnp_debug_pkg.log('PN_REC_CALC_PKG.line_constraints (+) ');
3216 pnp_debug_pkg.log('PN_REC_CALC_PKG.line_constraints -line id'|| p_rec_agr_line_id);
3217 pnp_debug_pkg.log('PN_REC_CALC_PKG.line_constraints -as of date '|| to_char(p_as_of_date));
3218
3219 FOR line_constr_rec in get_line_constr_csr
3220
3221 LOOP
3222
3223 i := i + 1;
3224
3225 line_constr_tbl(i).constr_order := line_constr_rec.constr_order;
3226 line_constr_tbl(i).scope := line_constr_rec.scope;
3227 line_constr_tbl(i).relation := line_constr_rec.relation;
3228 line_constr_tbl(i).value := line_constr_rec.value;
3229 line_constr_tbl(i).cpi_index := line_constr_rec.cpi_index;
3230 line_constr_tbl(i).base_year := line_constr_rec.base_year;
3231
3232 END LOOP;
3233
3234 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_line_constraints (-) ');
3235 RETURN line_constr_tbl;
3236
3237 EXCEPTION
3238
3239 WHEN OTHERS
3240 THEN
3241 fnd_message.set_name ('PN','PN_RECALB_CONST_NF');
3242 pnp_debug_pkg.put_log_msg(fnd_message.get||' '|| to_char(sqlcode));
3243
3244 line_constr_tbl(1).constr_order := -99;
3245 line_constr_tbl(1).scope := null;
3246 line_constr_tbl(1).relation := null;
3247 line_constr_tbl(1).value := null;
3248 line_constr_tbl(1).cpi_index := null;
3249 line_constr_tbl(1).base_year := null;
3250
3251 RETURN line_constr_tbl;
3252
3253
3254 END get_line_constraints;
3255
3256 /*===========================================================================+
3257 | FUNCTION
3258 | GET_LINE_ABATEMENTS
3259 |
3260 | DESCRIPTION
3261 | Gets recoverable area from pn_rec_period_lines_all table
3262 | for a line if the calc method is 'Fixed rate'
3263 |
3264 | SCOPE - PUBLIC
3265 |
3266 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3267 |
3268 | ARGUMENTS : IN:
3269 | p_rec_period_lines_id
3270 | p_rec_agr_line_id
3271 | p_start_date
3272 | p_end_date
3273 | p_as_of_date
3274 |
3275 | OUT:
3276 |
3277 | RETURNS : None
3278 |
3279 | NOTES : Gets recoverable area from pn_rec_period_lines_all table
3280 | for a line if the calc method is 'Fixed rate'
3281 |
3282 | MODIFICATION HISTORY
3283 |
3284 | 19-MAY-2003 Daniel Thota o Created
3285 +===========================================================================*/
3286 FUNCTION get_line_abatements (
3287 p_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE
3288 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
3289 )
3290 RETURN pn_rec_agr_linabat_all.amount%TYPE IS
3291
3292
3293 CURSOR csr_get_abate IS
3294 SELECT NVL(SUM(NVL(amount,0)),0)
3295 FROM pn_rec_agr_linabat_all abate
3296 WHERE abate.rec_agr_line_id = p_rec_agr_line_id
3297 AND p_as_of_date between abate.start_date AND abate.end_date;
3298
3299 l_line_abatements pn_rec_agr_linabat_all.amount%TYPE;
3300
3301 BEGIN
3302
3303 OPEN csr_get_abate;
3304 FETCH csr_get_abate into l_line_abatements;
3305 CLOSE csr_get_abate;
3306
3307 pnp_debug_pkg.log('PN_REC_CALC_PKG.line_abatements (+) ');
3308
3309
3310 RETURN l_line_abatements;
3311
3312 EXCEPTION
3313
3314 WHEN OTHERS THEN
3315
3316 fnd_message.set_name ('PN','PN_RECALB_ABAT_NF');
3317 pnp_debug_pkg.put_log_msg(fnd_message.get||' '|| to_char(sqlcode));
3318
3319 RETURN -99;
3320
3321 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_line_abatements (-) ');
3322
3323 END get_line_abatements;
3324
3325 /*===========================================================================+
3326 | FUNCTION
3327 | FIND_IF_PERIOD_LINE_EXISTS
3328 |
3329 | DESCRIPTION
3330 | Finds if period line exists for a line
3331 |
3332 | SCOPE - PUBLIC
3333 |
3334 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3335 |
3336 | ARGUMENTS : IN:
3337 |
3338 | OUT:
3339 |
3340 | RETURNS : None
3341 |
3342 | NOTES : Finds if period line exists for a line
3343 |
3344 | MODIFICATION HISTORY
3345 |
3346 | 22-MAY-2003 Daniel Thota o Created
3347 +===========================================================================*/
3348 FUNCTION find_if_period_line_exists (
3349 p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
3350 ,p_rec_calc_period_id pn_rec_period_lines_all.rec_calc_period_id%TYPE
3351 )
3352 RETURN pn_rec_period_lines_all.rec_period_lines_id%TYPE IS
3353
3354 CURSOR csr_chck_exist IS
3355 SELECT periods.rec_period_lines_id
3356 FROM pn_rec_period_lines_all periods
3357 WHERE periods.rec_agr_line_id = p_rec_agr_line_id
3358 AND periods.rec_calc_period_id = p_rec_calc_period_id;
3359
3360 l_rec_period_lines_id pn_rec_period_lines_all.rec_period_lines_id%TYPE;
3361
3362 BEGIN
3363
3364 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.find_if_period_line_exists (+)');
3365
3366 OPEN csr_chck_exist;
3367 FETCH csr_chck_exist INTO l_rec_period_lines_id;
3368 IF csr_chck_exist%NOTFOUND THEN
3369 l_rec_period_lines_id := null;
3370 END IF;
3371 CLOSE csr_chck_exist;
3372
3373 PNP_DEBUG_PKG.debug ('PN_VAR_RENT_PKG.find_if_period_line_exists (-)');
3374
3375 RETURN l_rec_period_lines_id;
3376
3377 EXCEPTION
3378
3379 WHEN TOO_MANY_ROWS
3380 THEN
3381
3382 fnd_message.set_name ('PN','PN_RECALB_PRDLN');
3383 pnp_debug_pkg.put_log_msg(fnd_message.get||' '||to_char(sqlcode));
3384 return -99;
3385
3386 WHEN OTHERS
3387 THEN
3388 fnd_message.set_name ('PN','PN_RECALB_CHK_PRDLN');
3389 pnp_debug_pkg.put_log_msg(fnd_message.get||' '||to_char(sqlcode));
3390 RETURN -99;
3391
3392
3393 END find_if_period_line_exists;
3394
3395 /*===========================================================================+
3396 | PROCEDURE
3397 | INSERT_PERIOD_LINES_ROW
3398 |
3399 | DESCRIPTION
3400 | Create records in the PN_REC_PERIOD_LINES_ALL table
3401 |
3402 | SCOPE - PUBLIC
3403 |
3404 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3405 |
3406 | ARGUMENTS : IN:
3407 |
3408 | OUT:
3409 |
3410 | RETURNS : None
3411 |
3412 | NOTES : Create records in the PN_REC_PERIOD_LINES_ALL
3413 |
3414 | MODIFICATION HISTORY
3415 |
3416 | 21-MAY-2003 Daniel Thota o Created
3417 | 25-JUL-2003 Daniel Thota o Added X_FIXED_PCT to INSERT_PERIOD_LINES_ROW
3418 | and UPDATE_PERIOD_LINES_ROW.Fix for bug# 3067662
3419 +===========================================================================*/
3420 procedure INSERT_PERIOD_LINES_ROW (
3421 X_ROWID IN OUT NOCOPY VARCHAR2
3422 ,X_REC_PERIOD_LINES_ID IN OUT NOCOPY NUMBER
3423 ,X_BUDGET_PCT IN NUMBER
3424 ,X_OCCUPANCY_PCT IN NUMBER
3425 ,X_MULTIPLE_PCT IN NUMBER
3426 ,X_TENANCY_START_DATE IN DATE
3427 ,X_TENANCY_END_DATE IN DATE
3428 ,X_STATUS IN VARCHAR2
3429 ,X_BUDGET_PRORATA_SHARE IN NUMBER
3430 ,X_BUDGET_COST_PER_AREA IN NUMBER
3431 ,X_TOTAL_AREA IN NUMBER
3432 ,X_TOTAL_EXPENSE IN NUMBER
3433 ,X_RECOVERABLE_AREA IN NUMBER
3434 ,X_ACTUAL_RECOVERY IN NUMBER
3435 ,X_CONSTRAINED_ACTUAL IN NUMBER
3436 ,X_ABATEMENTS IN NUMBER
3437 ,X_ACTUAL_PRORATA_SHARE IN NUMBER
3438 ,X_BILLED_RECOVERY IN NUMBER
3439 ,X_RECONCILED_AMOUNT IN NUMBER
3440 ,X_BUDGET_RECOVERY IN NUMBER
3441 ,X_BUDGET_EXPENSE IN NUMBER
3442 ,X_REC_CALC_PERIOD_ID IN NUMBER
3443 ,X_REC_AGR_LINE_ID IN NUMBER
3444 ,X_AS_OF_DATE IN DATE
3445 ,X_START_DATE IN DATE
3446 ,X_END_DATE IN DATE
3447 ,X_BILLING_TYPE IN VARCHAR2
3448 ,X_BILLING_PURPOSE IN VARCHAR2
3449 ,X_CUST_ACCOUNT_ID IN NUMBER
3450 ,X_CREATION_DATE IN DATE
3451 ,X_CREATED_BY IN NUMBER
3452 ,X_LAST_UPDATE_DATE IN DATE
3453 ,X_LAST_UPDATED_BY IN NUMBER
3454 ,X_LAST_UPDATE_LOGIN IN NUMBER
3455 ,X_FIXED_PCT IN NUMBER
3456 ,X_ERROR_CODE IN OUT NOCOPY NUMBER
3457 ) is
3458
3459 CURSOR C is
3460 select ROWID
3461 from PN_REC_PERIOD_LINES
3462 where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID;
3463
3464 CURSOR org_cur IS
3465 SELECT org_id
3466 FROM pn_rec_calc_periods_all
3467 WHERE rec_calc_period_id = X_REC_CALC_PERIOD_ID;
3468
3469 l_org_ID NUMBER;
3470
3471 BEGIN
3472
3473 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_LINES_ROW (+)');
3474
3475 -------------------------------------------------------
3476 -- Select the nextval for group date id
3477 -------------------------------------------------------
3478 IF ( X_REC_PERIOD_LINES_ID IS NULL) THEN
3479 select pn_rec_period_lines_s.nextval
3480 into X_REC_PERIOD_LINES_ID
3481 from dual;
3482 END IF;
3483
3484 FOR org_rec IN org_cur LOOP
3485 l_org_ID := org_rec.org_id;
3486 END LOOP;
3487
3488 IF l_org_ID IS NULL THEN
3489 l_org_ID := pn_mo_cache_utils.get_current_org_id;
3490 END IF;
3491
3492
3493 INSERT INTO PN_REC_PERIOD_LINES_ALL (
3494 BUDGET_PCT
3495 ,OCCUPANCY_PCT
3496 ,MULTIPLE_PCT
3497 ,TENANCY_START_DATE
3498 ,TENANCY_END_DATE
3499 ,STATUS
3500 ,BUDGET_PRORATA_SHARE
3501 ,BUDGET_COST_PER_AREA
3502 ,LAST_UPDATE_DATE
3503 ,LAST_UPDATED_BY
3504 ,CREATION_DATE
3505 ,CREATED_BY
3506 ,LAST_UPDATE_LOGIN
3507 ,TOTAL_AREA
3508 ,TOTAL_EXPENSE
3509 ,RECOVERABLE_AREA
3510 ,ACTUAL_RECOVERY
3511 ,CONSTRAINED_ACTUAL
3512 ,ABATEMENTS
3513 ,ACTUAL_PRORATA_SHARE
3514 ,BILLED_RECOVERY
3515 ,RECONCILED_AMOUNT
3516 ,BUDGET_RECOVERY
3517 ,BUDGET_EXPENSE
3518 ,REC_PERIOD_LINES_ID
3519 ,REC_CALC_PERIOD_ID
3520 ,REC_AGR_LINE_ID
3521 ,AS_OF_DATE
3522 ,START_DATE
3523 ,END_DATE
3524 ,BILLING_TYPE
3525 ,BILLING_PURPOSE
3526 ,CUST_ACCOUNT_ID
3527 ,FIXED_PCT
3528 ,ORG_ID
3529 )
3530 values(
3531 X_BUDGET_PCT
3532 ,X_OCCUPANCY_PCT
3533 ,X_MULTIPLE_PCT
3534 ,X_TENANCY_START_DATE
3535 ,X_TENANCY_END_DATE
3536 ,X_STATUS
3537 ,X_BUDGET_PRORATA_SHARE
3538 ,X_BUDGET_COST_PER_AREA
3539 ,X_LAST_UPDATE_DATE
3540 ,X_LAST_UPDATED_BY
3541 ,X_CREATION_DATE
3542 ,X_CREATED_BY
3543 ,X_LAST_UPDATE_LOGIN
3544 ,X_TOTAL_AREA
3545 ,X_TOTAL_EXPENSE
3546 ,X_RECOVERABLE_AREA
3547 ,X_ACTUAL_RECOVERY
3548 ,X_CONSTRAINED_ACTUAL
3549 ,X_ABATEMENTS
3550 ,X_ACTUAL_PRORATA_SHARE
3551 ,X_BILLED_RECOVERY
3552 ,X_RECONCILED_AMOUNT
3553 ,X_BUDGET_RECOVERY
3554 ,X_BUDGET_EXPENSE
3555 ,X_REC_PERIOD_LINES_ID
3556 ,X_REC_CALC_PERIOD_ID
3557 ,X_REC_AGR_LINE_ID
3558 ,X_AS_OF_DATE
3559 ,X_START_DATE
3560 ,X_END_DATE
3561 ,X_BILLING_TYPE
3562 ,X_BILLING_PURPOSE
3563 ,X_CUST_ACCOUNT_ID
3564 ,X_FIXED_PCT
3565 ,l_org_ID
3566 );
3567
3568
3569
3570 open c;
3571 fetch c into X_ROWID;
3572 if (c%notfound) then
3573 close c;
3574 raise no_data_found;
3575 end if;
3576 close c;
3577
3578 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_LINES_ROW (-)');
3579
3580 EXCEPTION
3581 WHEN OTHERS THEN
3582
3583 X_ERROR_CODE := -99;
3584 PNP_DEBUG_PKG.log ('Error inserting into period lines'|| to_char(sqlcode));
3585
3586 end INSERT_PERIOD_LINES_ROW;
3587
3588 /*===========================================================================+
3589 | PROCEDURE
3590 | UPDATE_PERIOD_LINES_ROW
3591 |
3592 | DESCRIPTION
3593 | Update records in the PN_REC_PERIOD_LINES_ALL table
3594 |
3595 | SCOPE - PUBLIC
3596 |
3597 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3598 |
3599 | ARGUMENTS : IN:
3600 |
3601 | OUT:
3602 |
3603 | RETURNS : None
3604 |
3605 | NOTES : Update records in the PN_REC_PERIOD_LINES_ALL
3606 |
3607 | MODIFICATION HISTORY
3608 |
3609 | 21-MAY-2003 Daniel Thota o Created
3610 | 25-JUL-2003 Daniel Thota o Added X_FIXED_PCT to INSERT_PERIOD_LINES_ROW
3611 | and UPDATE_PERIOD_LINES_ROW.Fix for bug# 3067662
3612 +===========================================================================*/
3613 procedure UPDATE_PERIOD_LINES_ROW (
3614 X_REC_PERIOD_LINES_ID in NUMBER
3615 ,X_BUDGET_PCT in NUMBER
3616 ,X_OCCUPANCY_PCT in NUMBER
3617 ,X_MULTIPLE_PCT in NUMBER
3618 ,X_TENANCY_START_DATE in DATE
3619 ,X_TENANCY_END_DATE in DATE
3620 ,X_STATUS in VARCHAR2
3621 ,X_BUDGET_PRORATA_SHARE in NUMBER
3622 ,X_BUDGET_COST_PER_AREA in NUMBER
3623 ,X_TOTAL_AREA in NUMBER
3624 ,X_TOTAL_EXPENSE in NUMBER
3625 ,X_RECOVERABLE_AREA in NUMBER
3626 ,X_ACTUAL_RECOVERY in NUMBER
3627 ,X_CONSTRAINED_ACTUAL in NUMBER
3628 ,X_ABATEMENTS in NUMBER
3629 ,X_ACTUAL_PRORATA_SHARE in NUMBER
3630 ,X_BILLED_RECOVERY in NUMBER
3631 ,X_RECONCILED_AMOUNT in NUMBER
3632 ,X_BUDGET_RECOVERY in NUMBER
3633 ,X_BUDGET_EXPENSE in NUMBER
3634 ,X_REC_CALC_PERIOD_ID in NUMBER
3635 ,X_REC_AGR_LINE_ID in NUMBER
3636 ,X_AS_OF_DATE in DATE
3637 ,X_START_DATE in DATE
3638 ,X_END_DATE in DATE
3639 ,X_BILLING_TYPE in VARCHAR2
3640 ,X_BILLING_PURPOSE in VARCHAR2
3641 ,X_CUST_ACCOUNT_ID in NUMBER
3642 ,X_LAST_UPDATE_DATE in DATE
3643 ,X_LAST_UPDATED_BY in NUMBER
3644 ,X_LAST_UPDATE_LOGIN in NUMBER
3645 ,X_FIXED_PCT in NUMBER
3646 ,X_ERROR_CODE in out NOCOPY NUMBER
3647 ) is
3648
3649 BEGIN
3650
3651 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW (+)');
3652
3653 update PN_REC_PERIOD_LINES_ALL set
3654 BUDGET_PCT = X_BUDGET_PCT
3655 ,OCCUPANCY_PCT = X_OCCUPANCY_PCT
3656 ,MULTIPLE_PCT = X_MULTIPLE_PCT
3657 ,TENANCY_START_DATE = X_TENANCY_START_DATE
3658 ,TENANCY_END_DATE = X_TENANCY_END_DATE
3659 ,STATUS = X_STATUS
3660 ,BUDGET_PRORATA_SHARE = X_BUDGET_PRORATA_SHARE
3661 ,BUDGET_COST_PER_AREA = X_BUDGET_COST_PER_AREA
3662 ,TOTAL_AREA = X_TOTAL_AREA
3663 ,TOTAL_EXPENSE = X_TOTAL_EXPENSE
3664 ,RECOVERABLE_AREA = X_RECOVERABLE_AREA
3665 ,ACTUAL_RECOVERY = X_ACTUAL_RECOVERY
3666 ,CONSTRAINED_ACTUAL = X_CONSTRAINED_ACTUAL
3667 ,ABATEMENTS = X_ABATEMENTS
3668 ,ACTUAL_PRORATA_SHARE = X_ACTUAL_PRORATA_SHARE
3669 ,BILLED_RECOVERY = X_BILLED_RECOVERY
3670 ,RECONCILED_AMOUNT = X_RECONCILED_AMOUNT
3671 ,BUDGET_RECOVERY = X_BUDGET_RECOVERY
3672 ,BUDGET_EXPENSE = X_BUDGET_EXPENSE
3673 ,REC_CALC_PERIOD_ID = X_REC_CALC_PERIOD_ID
3674 ,REC_AGR_LINE_ID = X_REC_AGR_LINE_ID
3675 ,AS_OF_DATE = X_AS_OF_DATE
3676 ,START_DATE = X_START_DATE
3677 ,END_DATE = X_END_DATE
3678 ,BILLING_TYPE = X_BILLING_TYPE
3679 ,BILLING_PURPOSE = X_BILLING_PURPOSE
3680 ,CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID
3681 ,REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID
3682 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
3683 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
3684 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
3685 ,FIXED_PCT = X_FIXED_PCT
3686 where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID
3687 ;
3688
3689 if (sql%notfound) then
3690 raise no_data_found;
3691 end if;
3692
3693 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_LINES_ROW (-)');
3694
3695 EXCEPTION
3696 WHEN OTHERS THEN
3697
3698 X_ERROR_CODE := -99;
3699 PNP_DEBUG_PKG.log ('Error updating into period lines'|| to_char(sqlcode));
3700
3701 end UPDATE_PERIOD_LINES_ROW;
3702
3703 /*===========================================================================+
3704 | PROCEDURE
3705 | DELETE_PERIOD_LINES_ROW
3706 |
3707 | DESCRIPTION
3708 | Delete records in the PN_REC_PERIOD_LINES_ALL table
3709 |
3710 | SCOPE - PUBLIC
3711 |
3712 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3713 |
3714 | ARGUMENTS : IN:
3715 |
3716 | OUT:
3717 |
3718 | RETURNS : None
3719 |
3720 | NOTES : Delete records in the PN_REC_PERIOD_LINES_ALL
3721 |
3722 | MODIFICATION HISTORY
3723 |
3724 | 21-MAY-2003 Daniel Thota o Created
3725 +===========================================================================*/
3726
3727 procedure DELETE_PERIOD_LINES_ROW (
3728 X_REC_PERIOD_LINES_ID in NUMBER
3729 ) is
3730
3731 BEGIN
3732
3733 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_LINES_ROW (+)');
3734
3735 delete from PN_REC_PERIOD_LINES_ALL
3736 where REC_PERIOD_LINES_ID = X_REC_PERIOD_LINES_ID;
3737
3738 if (sql%notfound) then
3739 raise no_data_found;
3740 end if;
3741
3742 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_LINES_ROW (-)');
3743
3744 end DELETE_PERIOD_LINES_ROW;
3745
3746 /*===========================================================================+
3747 | PROCEDURE
3748 | INSERT_PERIOD_BILLREC_ROW
3749 |
3750 | DESCRIPTION
3751 | Create records in the PN_REC_PERIOD_BILL_ALL table
3752 |
3753 | SCOPE - PUBLIC
3754 |
3755 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3756 |
3757 | ARGUMENTS : IN:
3758 |
3759 | OUT:
3760 |
3761 | RETURNS : None
3762 |
3763 | NOTES : Create records in the PN_REC_PERIOD_BILL_ALL
3764 |
3765 | MODIFICATION HISTORY
3766 |
3767 | 21-MAY-2003 Daniel Thota o Created
3768 +===========================================================================*/
3769 procedure INSERT_PERIOD_BILLREC_ROW (
3770 X_ROWID IN OUT NOCOPY VARCHAR2
3771 ,X_PERIOD_BILLREC_ID IN OUT NOCOPY NUMBER
3772 ,X_REC_AGREEMENT_ID IN NUMBER
3773 ,X_REC_AGR_LINE_ID IN NUMBER
3774 ,X_REC_CALC_PERIOD_ID IN NUMBER
3775 ,X_AMOUNT IN NUMBER
3776 ,X_CREATION_DATE IN DATE
3777 ,X_CREATED_BY IN NUMBER
3778 ,X_LAST_UPDATE_DATE IN DATE
3779 ,X_LAST_UPDATED_BY IN NUMBER
3780 ,X_LAST_UPDATE_LOGIN IN NUMBER
3781 ) is
3782 CURSOR C is
3783 SELECT ROWID FROM PN_REC_PERIOD_BILL_ALL
3784 WHERE PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID;
3785
3786 CURSOR org_cur IS
3787 SELECT org_id
3788 FROM pn_rec_agreements_all
3789 WHERE rec_agreement_id = X_REC_AGREEMENT_ID;
3790
3791 l_org_ID NUMBER;
3792
3793 BEGIN
3794
3795 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_BILLREC_ROW (+)');
3796
3797 -------------------------------------------------------
3798 -- Select the nextval for PERIOD_BILLREC_ID
3799 -------------------------------------------------------
3800 IF ( X_PERIOD_BILLREC_ID IS NULL) THEN
3801 SELECT PN_REC_PERIOD_BILL_S.nextval
3802 INTO X_PERIOD_BILLREC_ID
3803 FROM dual;
3804 END IF;
3805
3806 FOR org_rec IN org_cur LOOP
3807 l_org_ID := org_rec.org_id;
3808 END LOOP;
3809
3810 IF l_org_ID IS NULL THEN
3811 l_org_ID := pn_mo_cache_utils.get_current_org_id;
3812 END IF;
3813
3814 INSERT INTO PN_REC_PERIOD_BILL_ALL (
3815 PERIOD_BILLREC_ID
3816 ,REC_AGREEMENT_ID
3817 ,REC_AGR_LINE_ID
3818 ,REC_CALC_PERIOD_ID
3819 ,LAST_UPDATE_DATE
3820 ,LAST_UPDATED_BY
3821 ,CREATION_DATE
3822 ,CREATED_BY
3823 ,LAST_UPDATE_LOGIN
3824 ,AMOUNT
3825 ,ORG_ID
3826 )
3827 VALUES(
3828 X_PERIOD_BILLREC_ID
3829 ,X_REC_AGREEMENT_ID
3830 ,X_REC_AGR_LINE_ID
3831 ,X_REC_CALC_PERIOD_ID
3832 ,X_LAST_UPDATE_DATE
3833 ,X_LAST_UPDATED_BY
3834 ,X_CREATION_DATE
3835 ,X_CREATED_BY
3836 ,X_LAST_UPDATE_LOGIN
3837 ,X_AMOUNT
3838 ,l_org_ID
3839 );
3840
3841 OPEN C;
3842 FETCH C INTO X_ROWID;
3843 IF (C%NOTFOUND) THEN
3844 CLOSE C;
3845 RAISE NO_DATA_FOUND;
3846 END IF;
3847 CLOSE C;
3848
3849 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.INSERT_PERIOD_BILLREC_ROW (-)');
3850
3851 END INSERT_PERIOD_BILLREC_ROW;
3852
3853 /*===========================================================================+
3854 | PROCEDURE
3855 | UPDATE_PERIOD_BILLREC_ROW
3856 |
3857 | DESCRIPTION
3858 | Update records in the PN_REC_PERIOD_BILL_ALL table
3859 |
3860 | SCOPE - PUBLIC
3861 |
3862 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3863 |
3864 | ARGUMENTS : IN:
3865 |
3866 | OUT:
3867 |
3868 | RETURNS : None
3869 |
3870 | NOTES : Update records in the PN_REC_PERIOD_BILL_ALL
3871 |
3872 | MODIFICATION HISTORY
3873 |
3874 | 21-MAY-2003 Daniel Thota o Created
3875 +===========================================================================*/
3876 procedure UPDATE_PERIOD_BILLREC_ROW (
3877 X_PERIOD_BILLREC_ID in NUMBER
3878 ,X_REC_AGREEMENT_ID in NUMBER
3879 ,X_REC_AGR_LINE_ID in NUMBER
3880 ,X_REC_CALC_PERIOD_ID in NUMBER
3881 ,X_AMOUNT in NUMBER
3882 ,X_LAST_UPDATE_DATE in DATE
3883 ,X_LAST_UPDATED_BY in NUMBER
3884 ,X_LAST_UPDATE_LOGIN in NUMBER
3885 ) is
3886
3887 BEGIN
3888
3889 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_BILLREC_ROW (+)');
3890
3891 update PN_REC_PERIOD_BILL_ALL set
3892 REC_AGR_LINE_ID = X_REC_AGR_LINE_ID
3893 ,REC_AGREEMENT_ID = X_REC_AGREEMENT_ID
3894 ,REC_CALC_PERIOD_ID = X_REC_CALC_PERIOD_ID
3895 ,AMOUNT = X_AMOUNT
3896 ,PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID
3897 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
3898 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
3899 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
3900 where PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID
3901 ;
3902
3903 if (sql%notfound) then
3904 raise no_data_found;
3905 end if;
3906
3907 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.UPDATE_PERIOD_BILLREC_ROW (-)');
3908
3909 end UPDATE_PERIOD_BILLREC_ROW;
3910
3911 /*===========================================================================+
3912 | PROCEDURE
3913 | DELETE_PERIOD_BILLREC_ROW
3914 |
3915 | DESCRIPTION
3916 | Delete records in the PN_REC_PERIOD_BILL_ALL table
3917 |
3918 | SCOPE - PUBLIC
3919 |
3920 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3921 |
3922 | ARGUMENTS : IN:
3923 |
3924 | OUT:
3925 |
3926 | RETURNS : None
3927 |
3928 | NOTES : Delete records in the PN_REC_PERIOD_BILL_ALL
3929 |
3930 | MODIFICATION HISTORY
3931 |
3932 | 21-MAY-2003 Daniel Thota o Created
3933 +===========================================================================*/
3934 procedure DELETE_PERIOD_BILLREC_ROW (
3935 X_PERIOD_BILLREC_ID in NUMBER
3936 ) is
3937
3938 BEGIN
3939
3940 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_BILLREC_ROW (+)');
3941
3942 delete from PN_REC_PERIOD_BILL_ALL
3943 where PERIOD_BILLREC_ID = X_PERIOD_BILLREC_ID;
3944
3945 if (sql%notfound) then
3946 raise no_data_found;
3947 end if;
3948
3949 PNP_DEBUG_PKG.log ('PN_REC_CALC_PKG.DELETE_PERIOD_BILLREC_ROW (-)');
3950
3951 end DELETE_PERIOD_BILLREC_ROW;
3952
3953 /*===========================================================================+
3954 | PROCEDURE
3955 | create_payment_terms
3956 |
3957 | DESCRIPTION
3958 | Procedure for creation of recovery payment terms.
3959 |
3960 | SCOPE - PUBLIC
3961 |
3962 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3963 |
3964 | ARGUMENTS : IN:
3965 |
3966 | OUT:
3967 |
3968 | RETURNS : None
3969 |
3970 | NOTES : Procedure for creation of recovery payment terms.
3971 |
3972 | MODIFICATION HISTORY
3973 |
3974 | 21-MAY-03 Daniel o Created
3975 | 15-Aug-03 Ashish o Bug#3099398 add the alias to the select clause
3976 | in the cursor
3977 | csr_distributions,csr_template and csr_lease_term
3978 | 04-Sep-03 Daniel o assigned l_rec_agr_line_id with p_rec_agr_line_id
3979 | Fix for bug # 3123730,3122264
3980 | 16-JUN-04 abanerje o Modified call to pnt_payment_terms_pkg.insert_row
3981 | to pass term_template_id. Bug 3657130.
3982 | 15-SEP-04 atuppad o In the call pnt_payment_terms_pkg.insert_row,
3983 | corrected the code to copy the payment DFF into
3984 | payment DFF of new IR term and not in AR Projects
3985 | DFF. Bug # 3841542
3986 | 21-APR-05 ftanudja o Added area_type_code, area defaulting. #4324777
3987 | 15-JUL-05 ftanudja o R12 change: add logic for tax_clsfctn_cd. #4495054
3988 | 28-NOV-05 pikhar o fetched org_id using cursor
3989 | 18-JUL-06 sdmahesh o Bug 5332426 Added handling for lazy upgrade
3990 | of Term Templates for E-Tax
3991 | 24-SEP-06 acprakas o Bug#6370014. Modified procedure to set schedule day,
3992 | term start and term end date to 28 if it is more than 28.
3993 +===========================================================================*/
3994
3995 PROCEDURE create_payment_terms(
3996 p_lease_id IN NUMBER
3997 ,p_payment_amount IN NUMBER
3998 ,p_calc_period_end_date IN DATE
3999 ,p_rec_agreement_id IN NUMBER
4000 ,p_rec_agr_line_id IN NUMBER
4001 ,p_rec_calc_period_id IN NUMBER
4002 ,p_location_id IN NUMBER
4003 ,p_amount_type IN VARCHAR2
4004 ,p_org_id IN NUMBER
4005 ,p_billing_type IN VARCHAR2
4006 ,p_billing_purpose IN VARCHAR2
4007 ,p_customer_id IN NUMBER
4008 ,p_cust_site_id IN NUMBER
4009 ,p_consolidate IN VARCHAR2
4010 ,p_error IN OUT NOCOPY VARCHAR2
4011 ,p_error_code IN OUT NOCOPY NUMBER
4012 ) IS
4013
4014 l_lease_class_code pn_leases.lease_class_code%TYPE;
4015 l_distribution_id pn_distributions.distribution_id%TYPE;
4016 l_payment_term_id pn_payment_terms.payment_term_id%TYPE;
4017 l_lease_change_id pn_lease_details.lease_change_id%TYPE;
4018 l_rowid ROWID;
4019 l_distribution_count NUMBER := 0;
4020 l_payment_start_date pn_payment_terms.start_date%TYPE;
4021 l_payment_end_date pn_payment_terms.end_date%TYPE;
4022 l_frequency pn_payment_terms.frequency_code%type;
4023 l_schedule_day pn_payment_terms.schedule_day%type;
4024 l_set_of_books_id gl_sets_of_books.set_of_books_id%type;
4025 l_context varchar2(2000);
4026 l_period_billrec_id PN_REC_PERIOD_BILL_all.period_billrec_id%TYPE:= NULL;
4027 l_payment_amount pn_payment_terms.actual_amount%type;
4028 l_period_bill_record period_bill_record;
4029 l_is_r12 BOOLEAN := pn_r12_util_pkg.is_r12;
4030 l_dummy VARCHAR2(30);
4031
4032 l_creation_date DATE := SYSDATE;
4033 l_created_by NUMBER := NVL(fnd_profile.value('USER_ID'), 0);
4034 l_term_date DATE; --Bug#6370014
4035
4036 CURSOR csr_temp_dist(p_term_template_id IN NUMBER)
4037 IS
4038 SELECT pd.*
4039 FROM pn_distributions_all pd
4040 WHERE pd.term_template_id = p_term_template_id;
4041
4042 CURSOR csr_term_dist(p_term_id IN NUMBER)
4043 IS
4044 SELECT pd.*
4045 FROM pn_distributions_all pd
4046 WHERE pd.payment_term_id = p_term_id;
4047
4048 CURSOR csr_template (p_rec_agreement_id IN NUMBER)
4049 IS
4050 SELECT ptt.*
4051 FROM pn_term_templates_all ptt,
4052 pn_rec_agreements_all prec
4053 WHERE ptt.term_template_id = prec.term_template_id
4054 AND prec.rec_agreement_id = p_rec_agreement_id;
4055
4056 CURSOR csr_template_upg (p_rec_agreement_id IN NUMBER)
4057 IS
4058 SELECT ptt.*
4059 FROM pn_term_templates_all ptt,
4060 pn_rec_agreements_all prec
4061 WHERE ptt.term_template_id = prec.term_template_id
4062 AND (ptt.tax_code_id IS NOT NULL OR ptt.tax_group_id IS NOT NULL)
4063 AND ptt.tax_classification_code IS NULL
4064 AND prec.rec_agreement_id = p_rec_agreement_id;
4065
4066
4067 rec_template pn_term_templates_all%ROWTYPE;
4068
4069 CURSOR csr_lease_term IS
4070 SELECT term.*
4071 FROM pn_payment_terms_all term
4072 WHERE term.lease_id = p_lease_id
4073 AND term.PAYMENT_TERM_TYPE_CODE = p_billing_type
4074 AND term.PAYMENT_PURPOSE_CODE = p_billing_purpose
4075 AND term.RECOVERABLE_FLAG = 'Y'
4076 AND rownum = 1;
4077
4078 rec_lease_term pn_payment_terms_all%ROWTYPE;
4079 l_term_details VARCHAR2(1) := 'N';
4080 rec_distributions pn_distributions%ROWTYPE;
4081 l_rec_agr_line_id number;
4082 l_area pn_payment_terms.area%TYPE;
4083 l_area_type_code pn_payment_terms.area_type_code%TYPE;
4084
4085
4086 CURSOR org_cur IS
4087 SELECT org_id
4088 FROM pn_leases_all
4089 WHERE lease_id = p_lease_id;
4090
4091 l_org_id NUMBER;
4092
4093
4094 BEGIN
4095
4096 pnp_debug_pkg.log ('PN_REC_CALC_PKG.create_payment_terms : (+)');
4097
4098 IF p_org_id IS NULL THEN
4099 FOR rec IN org_cur LOOP
4100 l_org_id := rec.org_id;
4101 END LOOP;
4102 ELSE
4103 l_org_id := p_org_id;
4104 END IF;
4105
4106 l_context := 'Getting lease details';
4107
4108 BEGIN
4109 SELECT pl.lease_class_code
4110 ,pld.lease_change_id
4111 INTO l_lease_class_code
4112 ,l_lease_change_id
4113 FROM pn_leases_all pl
4114 ,pn_lease_details_all pld
4115 WHERE pl.lease_id = pld.lease_id
4116 AND pld.lease_id = p_lease_id;
4117
4118 EXCEPTION
4119 WHEN OTHERS THEN
4120 pnp_debug_pkg.log ('Unable to get Lease Details :'||
4121 to_char(SQLCODE));
4122 p_error := 'Unable to get Lease Details';
4123 p_error_code := -99;
4124 return;
4125
4126 END;
4127
4128 IF p_error_code <> -99 THEN
4129
4130 pnp_debug_pkg.log ('create_payment_terms - Org id :'||l_org_id);
4131
4132 l_context := 'Getting SOB ';
4133
4134 l_set_of_books_id := TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID', l_org_id));
4135 pnp_debug_pkg.log ('create_payment_terms - Set of books id :'||l_set_of_books_id);
4136
4137 /*E-Tax lazy upgrade for Term Template*/
4138
4139 IF l_is_r12 THEN
4140
4141 OPEN csr_template_upg(p_rec_agreement_id);
4142 FETCH csr_template_upg INTO rec_template;
4143
4144 IF csr_template_upg%FOUND THEN
4145
4146 l_dummy := pn_r12_util_pkg.check_tax_upgrade(rec_template.term_template_id);
4147 pnp_debug_pkg.log('Term Template '||rec_template.name||' upgraded');
4148 template_name_tbl(NVL(template_name_tbl.LAST,0)+1) := rec_template.name;
4149 template_id_tbl(NVL(template_id_tbl.LAST,0)+1) := rec_template.term_template_id;
4150
4151 END IF;
4152
4153 CLOSE csr_template_upg;
4154
4155 END IF;
4156
4157
4158 l_context := 'Getting template details ';
4159
4160 OPEN csr_template(p_rec_agreement_id);
4161 FETCH csr_template INTO rec_template;
4162
4163 IF csr_template%NOTFOUND THEN
4164
4165 IF nvl(p_consolidate,'N') = 'N' THEN
4166
4167 l_context := 'Getting term details ';
4168 l_term_details := 'Y';
4169 OPEN csr_lease_term;
4170 FETCH csr_lease_term INTO rec_lease_term;
4171 CLOSE csr_lease_term;
4172
4173 ELSE
4174
4175 pnp_debug_pkg.log ('With Consolidation Option a term template is needed');
4176 p_error := 'Unable to get Lease Details';
4177 p_error_code := -99;
4178 CLOSE csr_template;
4179 return;
4180
4181 END IF;
4182
4183 END IF;
4184
4185 CLOSE csr_template;
4186
4187
4188 l_context := 'Setting term attributes ';
4189 IF l_lease_class_code = 'DIRECT' THEN
4190
4191 /* lease is of class: DIRECT */
4192
4193 rec_template.customer_id := NULL;
4194 rec_template.customer_site_use_id := NULL;
4195 rec_template.cust_ship_site_id := NULL;
4196 rec_template.cust_trx_type_id := NULL;
4197 rec_template.inv_rule_id := NULL;
4198 rec_template.account_rule_id := NULL;
4199 rec_template.salesrep_id := NULL;
4200 rec_template.cust_po_number := NULL;
4201 rec_template.receipt_method_id := NULL;
4202 ELSE
4203
4204 /* lease is 'sub-lease' or third-party */
4205
4206 rec_template.project_id := NULL;
4207 rec_template.task_id := NULL;
4208 rec_template.organization_id := NULL;
4209 rec_template.expenditure_type := NULL;
4210 rec_template.expenditure_item_date := NULL;
4211 rec_template.vendor_id := NULL;
4212 rec_template.vendor_site_id := NULL;
4213 rec_template.tax_group_id := NULL;
4214 rec_template.distribution_set_id := NULL;
4215 rec_template.po_header_id := NULL;
4216 END IF;
4217
4218 IF l_is_r12 THEN
4219 rec_template.tax_group_id := NULL;
4220 rec_template.tax_code_id := NULL;
4221 ELSE
4222 rec_template.tax_classification_code := NULL;
4223 END IF;
4224
4225 l_frequency := 'OT';
4226
4227 --Bug#6370014
4228 IF to_char(p_calc_period_end_date,'dd') > 28
4229 THEN
4230 l_schedule_day := 28;
4231 l_term_date := p_calc_period_end_date - (to_char(p_calc_period_end_date,'dd') - 28);
4232 ELSE
4233 l_schedule_day := to_char(p_calc_period_end_date,'dd');
4234 l_term_date := p_calc_period_end_date;
4235 END IF;
4236
4237
4238 l_context := 'Checking term exists ';
4239 l_period_bill_record := PN_REC_CALC_PKG.find_if_rec_payterm_exists(
4240 p_rec_agreement_id
4241 ,p_rec_agr_line_id
4242 ,p_rec_calc_period_id
4243 ,p_consolidate
4244 );
4245
4246 IF l_period_bill_record.period_billrec_id = -99 THEN
4247
4248 p_error := 'Error checking for payment terms';
4249 p_error_code := -99;
4250
4251 ELSE
4252
4253 l_payment_amount := nvl(l_period_bill_record.amount,0);
4254 l_period_billrec_id := l_period_bill_record.period_billrec_id;
4255
4256 END IF;
4257
4258 pnp_debug_pkg.log ('create_payment_terms - approved amount '|| l_payment_amount);
4259 pnp_debug_pkg.log ('create_payment_terms - period_billrec_id '|| l_period_billrec_id);
4260 IF p_error_code <> -99 and l_period_billrec_id IS NOT NULL THEN
4261
4262 l_payment_amount := p_payment_amount - l_payment_amount;
4263
4264 l_context := 'Updating period_billrec ';
4265
4266 PN_REC_CALC_PKG.update_period_billrec_row (
4267 X_PERIOD_BILLREC_ID => l_period_billrec_id
4268 ,X_REC_AGREEMENT_ID => p_rec_agreement_id
4269 ,X_REC_AGR_LINE_ID => p_rec_agr_line_id
4270 ,X_REC_CALC_PERIOD_ID => p_rec_calc_period_id
4271 ,X_AMOUNT => p_payment_amount
4272 ,X_LAST_UPDATE_DATE => l_creation_date
4273 ,X_LAST_UPDATED_BY => l_created_by
4274 ,X_LAST_UPDATE_LOGIN => l_created_by
4275 );
4276 ELSIF p_error_code <> -99 and l_period_billrec_id IS NULL THEN
4277
4278 l_payment_amount := p_payment_amount;
4279
4280 l_context := 'Inserting period_billrec ';
4281
4282 pnp_debug_pkg.log ('insert_period_billrec_row - agr id :'||p_rec_agreement_id);
4283 pnp_debug_pkg.log ('insert_period_billrec_row - p_rec_agr_line_id :'||p_rec_agr_line_id);
4284 pnp_debug_pkg.log ('insert_period_billrec_row - p_rec_calc_period_id :'||p_rec_calc_period_id);
4285 pnp_debug_pkg.log ('insert_period_billrec_row - amount :'||l_payment_amount);
4286 PN_REC_CALC_PKG.insert_period_billrec_row (
4287 X_ROWID => l_rowId
4288 ,X_PERIOD_BILLREC_ID => l_period_billrec_id
4289 ,X_REC_AGREEMENT_ID => p_rec_agreement_id
4290 ,X_REC_AGR_LINE_ID => p_rec_agr_line_id
4291 ,X_REC_CALC_PERIOD_ID => p_rec_calc_period_id
4292 ,X_AMOUNT => l_payment_amount
4293 ,X_CREATION_DATE => l_creation_date
4294 ,X_CREATED_BY => l_created_by
4295 ,X_LAST_UPDATE_DATE => l_creation_date
4296 ,X_LAST_UPDATED_BY => l_created_by
4297 ,X_LAST_UPDATE_LOGIN => l_created_by
4298 );
4299 END IF;
4300
4301 IF p_error_code <> -99 AND l_payment_amount <> 0 THEN
4302
4303 IF p_rec_agr_line_id = -1 THEN
4304
4305 l_rec_agr_line_id := null;
4306
4307 ELSE
4308 -- Fix for bug # 3123730,3122264
4309 l_rec_agr_line_id := p_rec_agr_line_id;
4310
4311 END IF;
4312
4313 pnp_debug_pkg.log ('create_payment_terms - l_payment_amount :'||l_payment_amount);
4314 pnp_debug_pkg.log ('create_payment_terms - Row Id :'||l_rowid);
4315 pnp_debug_pkg.log ('create_payment_terms - l_payment_amount :'||l_payment_amount);
4316 pnp_debug_pkg.log ('create_payment_terms - Payment Term Id :'||l_payment_term_id);
4317 pnp_debug_pkg.log ('create_payment_terms - Billing Purpose :'||p_billing_purpose);
4318 pnp_debug_pkg.log ('create_payment_terms - Billing Type :'||p_billing_Type);
4319 pnp_debug_pkg.log ('create_payment_terms - Frequency Code :'||l_frequency);
4320 pnp_debug_pkg.log ('create_payment_terms - Lease Id :'||p_lease_id);
4321 pnp_debug_pkg.log ('create_payment_terms - Lease change Id :'||l_lease_change_id);
4322 pnp_debug_pkg.log ('create_payment_terms - Start Date :'||p_calc_period_end_date);
4323 pnp_debug_pkg.log ('create_payment_terms - End Date :'||p_calc_period_end_date);
4324 pnp_debug_pkg.log ('create_payment_terms - SOB :'||rec_template.set_of_books_id);
4325 pnp_debug_pkg.log ('create_payment_terms - SOB :'||l_set_of_books_id);
4326 pnp_debug_pkg.log ('create_payment_terms - Currency Code :'||g_currency_code);
4327 pnp_debug_pkg.log ('create_payment_terms - Vendor Id :'||rec_template.vendor_id);
4328 pnp_debug_pkg.log ('create_payment_terms - Vendor Site Id :'||rec_template.vendor_site_id);
4329 pnp_debug_pkg.log ('create_payment_terms - Actual Amount :'||l_payment_amount);
4330 pnp_debug_pkg.log ('create_payment_terms - Customer Site Use :'||rec_template.customer_site_use_id);
4331 pnp_debug_pkg.log ('create_payment_terms - Location :'||p_location_id);
4332 pnp_debug_pkg.log ('create_payment_terms - Schedule Day :'||l_schedule_day);
4333 pnp_debug_pkg.log ('create_payment_terms - Customer Ship to :'||rec_template.cust_ship_site_id);
4334 pnp_debug_pkg.log ('create_payment_terms - AP Ar Temr Id :'||rec_template.ap_ar_term_id);
4335 pnp_debug_pkg.log ('create_payment_terms - Trx Id :'||rec_template.cust_trx_type_id);
4336 pnp_debug_pkg.log ('create_payment_terms - Project Id :'||rec_template.project_id);
4337 pnp_debug_pkg.log ('create_payment_terms - Task Id :'||rec_template.task_id);
4338 pnp_debug_pkg.log ('create_payment_terms - Organization Id :'||rec_template.organization_id);
4339 pnp_debug_pkg.log ('create_payment_terms - Exend Type :'||rec_template.expenditure_type);
4340 pnp_debug_pkg.log ('create_payment_terms - Exend Item Date :'||rec_template.expenditure_item_date);
4341 pnp_debug_pkg.log ('create_payment_terms - Tax Group Id :'||rec_template.tax_group_id);
4342 pnp_debug_pkg.log ('create_payment_terms - Tax Code Id :'||rec_template.tax_code_id);
4343 pnp_debug_pkg.log ('create_payment_terms - Tax Incl :'||rec_template.tax_included);
4344 pnp_debug_pkg.log ('create_payment_terms - Distr Set Id :'||rec_template.distribution_set_id);
4345 pnp_debug_pkg.log ('create_payment_terms - Inv rule Id :'||rec_template.inv_rule_id);
4346 pnp_debug_pkg.log ('create_payment_terms - Acct rule Id :'||rec_template.account_rule_id);
4347 pnp_debug_pkg.log ('create_payment_terms - Sales Rep Id :'||rec_template.salesrep_id);
4348 pnp_debug_pkg.log ('create_payment_terms - PO header Id :'||rec_template.po_header_id);
4349 pnp_debug_pkg.log ('create_payment_terms - PO # :'||rec_template.cust_po_number);
4350 pnp_debug_pkg.log ('create_payment_terms - Receipt method id :'||rec_template.receipt_method_id);
4351 pnp_debug_pkg.log ('create_payment_terms - Org id :'||p_org_id);
4352 pnp_debug_pkg.log ('create_payment_terms - Period Billrec id :'||l_period_billrec_id);
4353 pnp_debug_pkg.log ('create_payment_terms - Rec Agr Line id :'||l_rec_agr_line_id);
4354 pnp_debug_pkg.log ('create_payment_terms - Term Template ID :'||rec_template.term_template_id);
4355
4356 IF p_location_id IS NOT NULL AND
4357 p_calc_period_end_date IS NOT NULL THEN
4358
4359 l_area_type_code := 'LOCTN_RENTABLE';
4360 l_area := pnp_util_func.fetch_tenancy_area(
4361 p_lease_id => p_lease_id,
4362 p_location_id => p_location_id,
4363 p_as_of_date => p_calc_period_end_date,
4364 p_area_type_code => l_area_type_code);
4365
4366 END IF;
4367
4368 l_context := 'Creating payment term ';
4369
4370 pnt_payment_terms_pkg.insert_row (
4371 x_rowid => l_rowid
4372 ,x_payment_term_id => l_payment_term_id
4373 ,x_index_period_id => null
4374 ,x_index_term_indicator => null
4375 ,x_var_rent_inv_id => null
4376 ,x_var_rent_type => null
4377 ,x_last_update_date => SYSDATE
4378 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
4379 ,x_creation_date => SYSDATE
4380 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
4381 ,x_payment_purpose_code => nvl(p_billing_purpose,rec_template.payment_purpose_code)
4382 ,x_payment_term_type_code => nvl(p_billing_type,rec_template.payment_term_type_code)
4383 ,x_frequency_code => l_frequency
4384 ,x_lease_id => p_lease_id
4385 ,x_lease_change_id => l_lease_change_id
4386 ,x_start_date => l_term_date --Bug#6370014
4387 ,x_end_date => l_term_date --Bug#6370014
4388 ,x_set_of_books_id => NVL(rec_template.set_of_books_id,l_set_of_books_id)
4389 --,x_currency_code => NVL(rec_template.currency_code, l_currency_code)
4390 ,x_currency_code => g_currency_code
4391 ,x_rate => 1 -- not used in application
4392 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
4393 ,x_vendor_id => nvl(rec_template.vendor_id,rec_lease_term.vendor_id)
4394 ,x_vendor_site_id => nvl(rec_template.vendor_site_id,rec_lease_term.vendor_site_id)
4395 ,x_target_date => NULL
4396 ,x_actual_amount => l_payment_amount
4397 ,x_estimated_amount => NULL
4398 ,x_attribute_category => rec_template.attribute_category
4399 ,x_attribute1 => rec_template.attribute1
4400 ,x_attribute2 => rec_template.attribute2
4401 ,x_attribute3 => rec_template.attribute3
4402 ,x_attribute4 => rec_template.attribute4
4403 ,x_attribute5 => rec_template.attribute5
4404 ,x_attribute6 => rec_template.attribute6
4405 ,x_attribute7 => rec_template.attribute7
4406 ,x_attribute8 => rec_template.attribute8
4407 ,x_attribute9 => rec_template.attribute9
4408 ,x_attribute10 => rec_template.attribute10
4409 ,x_attribute11 => rec_template.attribute11
4410 ,x_attribute12 => rec_template.attribute12
4411 ,x_attribute13 => rec_template.attribute13
4412 ,x_attribute14 => rec_template.attribute14
4413 ,x_attribute15 => rec_template.attribute15
4414 ,x_project_attribute_category => rec_lease_term.project_attribute_category
4415 ,x_project_attribute1 => rec_lease_term.project_attribute1
4416 ,x_project_attribute2 => rec_lease_term.project_attribute2
4417 ,x_project_attribute3 => rec_lease_term.project_attribute3
4418 ,x_project_attribute4 => rec_lease_term.project_attribute4
4419 ,x_project_attribute5 => rec_lease_term.project_attribute5
4420 ,x_project_attribute6 => rec_lease_term.project_attribute6
4421 ,x_project_attribute7 => rec_lease_term.project_attribute7
4422 ,x_project_attribute8 => rec_lease_term.project_attribute8
4423 ,x_project_attribute9 => rec_lease_term.project_attribute9
4424 ,x_project_attribute10 => rec_lease_term.project_attribute10
4425 ,x_project_attribute11 => rec_lease_term.project_attribute11
4426 ,x_project_attribute12 => rec_lease_term.project_attribute12
4427 ,x_project_attribute13 => rec_lease_term.project_attribute13
4428 ,x_project_attribute14 => rec_lease_term.project_attribute14
4429 ,x_project_attribute15 => rec_lease_term.project_attribute15
4430 ,x_customer_id => p_customer_id
4431 ,x_customer_site_use_id => p_cust_site_id
4432 ,x_normalize => 'N'
4433 ,x_location_id => p_location_id
4434 ,x_schedule_day => l_schedule_day
4435 ,x_cust_ship_site_id => nvl(rec_template.cust_ship_site_id,rec_lease_term.cust_ship_site_id)
4436 ,x_ap_ar_term_id => nvl(rec_template.ap_ar_term_id,rec_lease_term.ap_ar_term_id)
4437 ,x_cust_trx_type_id => nvl(rec_template.cust_trx_type_id,rec_lease_term.cust_trx_type_id)
4438 ,x_project_id => nvl(rec_template.project_id,rec_lease_term.project_id)
4439 ,x_task_id => nvl(rec_template.task_id,rec_lease_term.task_id)
4440 ,x_organization_id => nvl(rec_template.organization_id,rec_lease_term.organization_id)
4441 ,x_expenditure_type => nvl(rec_template.expenditure_type,rec_lease_term.expenditure_type)
4442 ,x_expenditure_item_date => nvl(rec_template.expenditure_item_date,rec_lease_term.expenditure_item_date)
4443 ,x_tax_group_id => nvl(rec_template.tax_group_id,rec_lease_term.tax_group_id)
4444 ,x_tax_code_id => nvl(rec_template.tax_code_id,rec_lease_term.tax_code_id)
4445 ,x_tax_classification_code => nvl(rec_template.tax_classification_code,rec_lease_term.tax_classification_code)
4446
4447 ,x_tax_included => nvl(rec_template.tax_included,rec_lease_term.tax_included)
4448 ,x_distribution_set_id => nvl(rec_template.distribution_set_id,rec_lease_term.distribution_set_id)
4449 ,x_inv_rule_id => nvl(rec_template.inv_rule_id,rec_lease_term.inv_rule_id)
4450 ,x_account_rule_id => nvl(rec_template.account_rule_id,rec_lease_term.account_rule_id)
4451 ,x_salesrep_id => nvl(rec_template.salesrep_id,rec_lease_term.salesrep_id)
4452 ,x_approved_by => NULL
4453 ,x_status => 'DRAFT'
4454 ,x_po_header_id => nvl(rec_template.po_header_id,rec_lease_term.po_header_id)
4455 ,x_cust_po_number => nvl(rec_template.cust_po_number,rec_lease_term.cust_po_number)
4456 ,x_receipt_method_id => nvl(rec_template.receipt_method_id,rec_lease_term.receipt_method_id)
4457 ,x_calling_form => 'PNRECALB'
4458 ,x_org_id => l_org_id
4459 ,x_period_billrec_id => l_period_billrec_id
4460 ,x_rec_agr_line_id => l_rec_agr_line_id
4461 ,x_amount_type => 'CAM'
4462 ,x_recoverable_flag => NULL
4463 ,x_term_template_id => rec_template.term_template_id
4464 ,x_area => l_area
4465 ,x_area_type_code => l_area_type_code
4466 );
4467
4468
4469 /* Create a record in pn_distributions */
4470
4471
4472 l_context := 'Creating Account Distributions ';
4473
4474 IF l_term_details = 'Y' THEN
4475
4476 OPEN csr_term_dist(rec_lease_term.payment_term_id);
4477
4478 ELSE
4479
4480 OPEN csr_temp_dist(rec_template.term_template_id);
4481
4482 END IF;
4483
4484 l_distribution_count := 0;
4485
4486 LOOP
4487 IF csr_term_dist%ISOPEN THEN
4488
4489 FETCH csr_term_dist into rec_distributions;
4490 EXIT WHEN csr_term_dist%NOTFOUND;
4491
4492 ELSIF csr_temp_dist%ISOPEN THEN
4493
4494 FETCH csr_temp_dist into rec_distributions;
4495 EXIT WHEN csr_temp_dist%NOTFOUND;
4496
4497 END IF;
4498
4499 pnp_debug_pkg.log(' account_id '||rec_distributions.account_id);
4500 pnp_debug_pkg.log(' account_class '||rec_distributions.account_id);
4501
4502 l_context := 'Inserting Account Distributions ';
4503 pn_distributions_pkg.insert_row (
4504 x_rowid => l_rowid
4505 ,x_distribution_id => l_distribution_id
4506 ,x_account_id => rec_distributions.account_id
4507 ,x_payment_term_id => l_payment_term_id
4508 ,x_term_template_id => NULL
4509 ,x_account_class => rec_distributions.account_class
4510 ,x_percentage => rec_distributions.percentage
4511 ,x_line_number => rec_distributions.line_number
4512 ,x_last_update_date => SYSDATE
4513 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
4514 ,x_creation_date => SYSDATE
4515 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
4516 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
4517 ,x_attribute_category => rec_distributions.attribute_category
4518 ,x_attribute1 => rec_distributions.attribute1
4519 ,x_attribute2 => rec_distributions.attribute2
4520 ,x_attribute3 => rec_distributions.attribute3
4521 ,x_attribute4 => rec_distributions.attribute4
4522 ,x_attribute5 => rec_distributions.attribute5
4523 ,x_attribute6 => rec_distributions.attribute6
4524 ,x_attribute7 => rec_distributions.attribute7
4525 ,x_attribute8 => rec_distributions.attribute8
4526 ,x_attribute9 => rec_distributions.attribute9
4527 ,x_attribute10 => rec_distributions.attribute10
4528 ,x_attribute11 => rec_distributions.attribute11
4529 ,x_attribute12 => rec_distributions.attribute12
4530 ,x_attribute13 => rec_distributions.attribute13
4531 ,x_attribute14 => rec_distributions.attribute14
4532 ,x_attribute15 => rec_distributions.attribute15
4533 ,x_org_id => l_org_id
4534 );
4535
4536 l_rowid := NULL;
4537 l_distribution_id := NULL;
4538 l_distribution_count := l_distribution_count + 1;
4539
4540 END LOOP;
4541
4542 END IF;
4543
4544 END IF;
4545 pnp_debug_pkg.log('PN_REC_CALC_PKG.create_payment_terms (-) ');
4546
4547 EXCEPTION
4548 when others then
4549 pnp_debug_pkg.log('Error while' || l_context || to_char(sqlcode));
4550 p_error := 'Error creating billing term';
4551 p_error_code := -99;
4552
4553 END create_payment_terms;
4554
4555 /*===========================================================================+
4556 | FUNCTION
4557 | FIND_IF_REC_PAYTERM_EXISTS
4558 |
4559 | DESCRIPTION
4560 | Find if Recovery Payment Termfor a line/agreement is available
4561 |
4562 | SCOPE - PUBLIC
4563 |
4564 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4565 |
4566 | ARGUMENTS : IN:
4567 |
4568 | OUT:
4569 |
4570 | RETURNS : None
4571 |
4572 | NOTES : Find if Recovery Payment Termfor a line/agreement is available
4573 |
4574 | MODIFICATION HISTORY
4575 |
4576 | 19-MAY-2003 Daniel Thota o Created
4577 | 04-SEP-2003 Daniel Thota o Removed DECODE and used IF..THEN..ELSE instead
4578 | Fix for bugs 3123730,3122264
4579 | 05-SEP-2003 Daniel Thota o Added code to delete from pn_distributions_all,
4580 | before deleting from pn_payment_terms_all
4581 | 17-SEP-2003 Daniel Thota o Put in cursors get_distributions_exist_nocons
4582 | and cursors get_distributions_exist_cons
4583 | Fix for bug # 3142328
4584 +===========================================================================*/
4585 FUNCTION find_if_rec_payterm_exists(
4586 p_rec_agreement_id PN_REC_PERIOD_BILL_all.period_billrec_id%TYPE
4587 ,p_rec_agr_line_id PN_REC_PERIOD_BILL_all.rec_agr_line_id%TYPE
4588 ,p_rec_calc_period_id PN_REC_PERIOD_BILL_all.rec_calc_period_id%TYPE
4589 ,p_consolidate VARCHAR2
4590 )
4591 RETURN period_bill_record IS
4592
4593 l_period_bill_record period_bill_record;
4594 l_context VARCHAR2(2000);
4595 l_distributions_exist VARCHAR2(1):= 'N';
4596
4597 -- Fix for bug # 3142328
4598 CURSOR get_distributions_exist_nocons IS
4599 SELECT 'Y'
4600 FROM dual
4601 WHERE EXISTS (SELECT 1
4602 FROM pn_distributions_all dist
4603 ,pn_payment_terms_all term
4604 WHERE term.payment_term_id = dist.payment_term_id
4605 AND term.period_billrec_id = l_period_bill_record.period_billrec_id
4606 AND term.rec_agr_line_id = p_rec_agr_line_id
4607 AND term.status = 'DRAFT')
4608 ;
4609
4610 CURSOR get_distributions_exist_cons IS
4611 SELECT 'Y'
4612 FROM dual
4613 WHERE EXISTS (SELECT 1
4614 FROM pn_distributions_all dist
4615 ,pn_payment_terms_all term
4616 WHERE term.payment_term_id = dist.payment_term_id
4617 AND term.period_billrec_id = l_period_bill_record.period_billrec_id
4618 AND term.status = 'DRAFT')
4619 ;
4620 -- Fix for bug # 3142328
4621
4622 BEGIN
4623
4624 pnp_debug_pkg.log('PN_REC_CALC_PKG.find_if_rec_payterm_exists (+) ');
4625
4626 l_period_bill_record.amount := 0;
4627 l_period_bill_record.period_billrec_id:= 0;
4628 l_distributions_exist := 'N';
4629
4630 /* check to see if billed amount record exists for agreement or
4631 period line */
4632
4633 l_context := 'selecting billed amount record';
4634
4635 pnp_debug_pkg.log('find_if_rec_payterm_exists - getting billed_rec_id');
4636
4637 IF (p_consolidate = 'N') THEN
4638 SELECT period_billrec_id
4639 INTO l_period_bill_record.period_billrec_id
4640 FROM PN_REC_PERIOD_BILL_all
4641 WHERE rec_agreement_id = p_rec_agreement_id
4642 AND rec_agr_line_id = p_rec_agr_line_id
4643 AND rec_calc_period_id = p_rec_calc_period_id;
4644 ELSIF (p_consolidate = 'Y') THEN
4645 SELECT period_billrec_id
4646 INTO l_period_bill_record.period_billrec_id
4647 FROM PN_REC_PERIOD_BILL_all
4648 WHERE rec_agreement_id = p_rec_agreement_id
4649 AND rec_calc_period_id = p_rec_calc_period_id;
4650 END IF;
4651
4652 pnp_debug_pkg.log('find_if_rec_payterm_exists - bille_rec_id '|| l_period_bill_record.period_billrec_id);
4653
4654 /* Get the amount of approved terms for the period */
4655
4656 l_context := 'getting approved billed amount';
4657
4658 pnp_debug_pkg.log('find_if_rec_payterm_exists - getting approved amount');
4659
4660 IF (p_consolidate = 'N') THEN
4661 SELECT NVL(SUM(actual_amount),0)
4662 INTO l_period_bill_record.amount
4663 FROM pn_payment_terms_all
4664 WHERE period_billrec_id = l_period_bill_record.period_billrec_id
4665 AND rec_agr_line_id = p_rec_agr_line_id
4666 AND status = 'APPROVED';
4667 ELSIF (p_consolidate = 'Y') THEN
4668 SELECT NVL(SUM(actual_amount),0)
4669 INTO l_period_bill_record.amount
4670 FROM pn_payment_terms_all
4671 WHERE period_billrec_id = l_period_bill_record.period_billrec_id
4672 AND status = 'APPROVED';
4673 END IF;
4674
4675 pnp_debug_pkg.log('find_if_rec_payterm_exists - approved amount'|| l_period_bill_record.amount);
4676 l_context := 'deleting billing terms';
4677
4678 pnp_debug_pkg.log('find_if_rec_payterm_exists - deleting terms ');
4679
4680 IF (p_consolidate = 'N') THEN
4681
4682 -- Fix for bug # 3142328
4683 OPEN get_distributions_exist_nocons;
4684 FETCH get_distributions_exist_nocons INTO l_distributions_exist;
4685 CLOSE get_distributions_exist_nocons;
4686
4687 pnp_debug_pkg.log('now deleting terms l_distributions_exist: '||l_distributions_exist);
4688 IF l_distributions_exist = 'Y' THEN
4689
4690 DELETE pn_distributions_all
4691 WHERE payment_term_id in (SELECT payment_term_id
4692 FROM pn_payment_terms_all
4693 WHERE period_billrec_id = l_period_bill_record.period_billrec_id
4694 AND rec_agr_line_id = p_rec_agr_line_id
4695 AND status = 'DRAFT')
4696 ;
4697
4698 END IF;
4699
4700
4701 DELETE pn_payment_terms_all
4702 WHERE period_billrec_id = l_period_bill_record.period_billrec_id
4703 AND rec_agr_line_id = p_rec_agr_line_id
4704 AND status = 'DRAFT';
4705
4706 ELSIF (p_consolidate = 'Y') THEN
4707
4708 -- Fix for bug # 3142328
4709 OPEN get_distributions_exist_cons;
4710 FETCH get_distributions_exist_cons INTO l_distributions_exist;
4711 CLOSE get_distributions_exist_cons;
4712
4713 IF l_distributions_exist = 'Y' THEN
4714
4715 DELETE pn_distributions_all
4716 WHERE payment_term_id in (SELECT payment_term_id
4717 FROM pn_payment_terms_all
4718 WHERE period_billrec_id = l_period_bill_record.period_billrec_id
4719 AND status = 'DRAFT')
4720 ;
4721
4722 END IF;
4723
4724 DELETE pn_payment_terms_all
4725 WHERE period_billrec_id = l_period_bill_record.period_billrec_id
4726 AND status = 'DRAFT';
4727
4728 END IF;
4729
4730 pnp_debug_pkg.log('find_if_rec_payterm_exists - terms deleted '|| to_char(SQL%ROWCOUNT));
4731 pnp_debug_pkg.log('PN_REC_CALC_PKG.find_if_rec_payterm_exists (-) ');
4732
4733 RETURN l_period_bill_record;
4734
4735 EXCEPTION
4736
4737 WHEN NO_DATA_FOUND THEN
4738 RETURN NULL;
4739
4740 WHEN OTHERS
4741 THEN
4742 pnp_debug_pkg.log('Error while '|| l_context || to_char(sqlcode));
4743 l_period_bill_record.period_billrec_id := -99;
4744 l_period_bill_record.amount := null;
4745
4746 END find_if_rec_payterm_exists;
4747
4748 /*===========================================================================+
4749 | FUNCTION
4750 | GET_PRIOR_PERIOD_AMOUNT
4751 |
4752 | DESCRIPTION
4753 | Obtains prior period amount for a line
4754 |
4755 | SCOPE - PUBLIC
4756 |
4757 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4758 |
4759 | ARGUMENTS : IN:
4760 |
4761 | OUT:
4762 |
4763 | RETURNS : None
4764 |
4765 | NOTES : Obtains prior period amount for a line
4766 |
4767 | MODIFICATION HISTORY
4768 |
4769 | 19-MAY-2003 Daniel Thota o Created
4770 +===========================================================================*/
4771 FUNCTION get_prior_period_actual_amount(
4772 p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
4773 ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4774 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4775 ,p_called_from VARCHAR2
4776 )
4777 RETURN pn_rec_period_lines_all.constrained_actual%TYPE IS
4778
4779 l_prior_period_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4780 l_percent pn_rec_agr_linconst_all.value%TYPE;
4781
4782 CURSOR csr_get_curr_percent (p_as_of_date date) is
4783 SELECT lineconst.VALUE
4784 FROM pn_rec_agr_linconst_all lineconst
4785 WHERE lineconst.rec_agr_line_id = p_rec_agr_line_id
4786 AND p_as_of_date between lineconst.start_date and lineconst.end_date
4787 AND lineconst.RELATION = 'MAX'
4788 AND lineconst.SCOPE = 'OPYA';
4789
4790 BEGIN
4791
4792 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_prior_period_actual_amount (+) ');
4793
4794 SELECT NVL(period_lines.constrained_actual,0)
4795 INTO l_prior_period_amount
4796 FROM pn_rec_period_lines_all period_lines
4797 WHERE rec_agr_line_id = p_rec_agr_line_id
4798 AND end_date = (SELECT max(end_date)
4799 FROM pn_rec_period_lines_all
4800 WHERE start_date < p_start_date
4801 AND end_date < p_start_date
4802 AND rec_agr_line_id = p_rec_agr_line_id) ;
4803
4804
4805 IF p_called_from = 'CALCUI' THEN
4806
4807 OPEN csr_get_curr_percent(p_as_of_date);
4808 FETCH csr_get_curr_percent into l_percent;
4809 IF csr_get_curr_percent%NOTFOUND THEN
4810
4811 close csr_get_curr_percent;
4812 return null;
4813
4814 END IF;
4815
4816
4817 l_prior_period_amount := (l_percent * l_prior_period_amount/100)+ l_prior_period_amount;
4818
4819 END IF;
4820
4821 RETURN l_prior_period_amount;
4822
4823 EXCEPTION
4824
4825 WHEN TOO_MANY_ROWS
4826 THEN
4827 pnp_debug_pkg.log('get_prior_period_actual_amount - Multiple prior periods found');
4828
4829 IF p_called_from = 'CALCUI' THEN
4830 return null;
4831 ELSE
4832 return -99;
4833 END IF;
4834
4835 /* if this routine is being called for the 1st calculation period
4836 prior period actual recovery will not be found. hence set it
4837 to -1 */
4838
4839 WHEN NO_DATA_FOUND THEN
4840 IF p_called_from = 'CALCUI' THEN
4841 return null;
4842 ELSE
4843 RETURN -1;
4844 END IF;
4845
4846 WHEN OTHERS
4847 THEN
4848 pnp_debug_pkg.log('Error getting prior period actual amount '|| to_char(sqlcode));
4849 IF p_called_from = 'CALCUI' THEN
4850 return null;
4851 ELSE
4852 return -99;
4853 END IF;
4854
4855 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_prior_period_actual_amount (-) ');
4856
4857 END get_prior_period_actual_amount;
4858
4859 /*===========================================================================+
4860 | FUNCTION
4861 | get_prior_period_cap
4862 |
4863 | DESCRIPTION
4864 | Obtains prior period amount for a line
4865 |
4866 | SCOPE - PUBLIC
4867 |
4868 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
4869 |
4870 | ARGUMENTS : IN:
4871 |
4872 | OUT:
4873 |
4874 | RETURNS : None
4875 |
4876 | NOTES : Obtains prior period cap for a line
4877 |
4878 | MODIFICATION HISTORY
4879 |
4880 | 23-aug-2003 achauhan o Created
4881 +===========================================================================*/
4882 FUNCTION get_prior_period_cap(
4883 p_rec_agr_line_id pn_rec_period_lines_all.rec_agr_line_id%TYPE
4884 ,p_start_date pn_rec_calc_periods_all.start_date%TYPE
4885 ,p_end_date pn_rec_calc_periods_all.end_date%TYPE
4886 ,p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE
4887 ,p_called_from VARCHAR2
4888 )
4889 RETURN pn_rec_period_lines_all.actual_recovery%TYPE IS
4890
4891 l_percent pn_rec_agr_linconst_all.value%TYPE;
4892 l_cap_amount pn_rec_period_lines_all.actual_recovery%TYPE;
4893 l_start_date pn_rec_period_lines_all.start_date%TYPE;
4894 l_end_date pn_rec_period_lines_all.end_date%TYPE;
4895
4896 CURSOR csr_get_base_cap is
4897 SELECT NVL(period_lines.actual_recovery,0), period_lines.start_date, period_lines.end_date
4898 FROM pn_rec_period_lines_all period_lines
4899 WHERE rec_agr_line_id = p_rec_agr_line_id
4900 AND start_date = (select min(start_date)
4901 from pn_rec_period_lines_all
4902 WHERE rec_agr_line_id = p_rec_agr_line_id) ;
4903
4904 CURSOR csr_get_prior_periods (p_start_date date, p_fst_end_date date) is
4905 SELECT lineconst.VALUE
4906 FROM pn_rec_period_lines_all period_lines,
4907 pn_rec_agr_linconst_all lineconst,
4908 pn_rec_calc_periods_all recperiod
4909 WHERE period_lines.rec_agr_line_id = p_rec_agr_line_id
4910 AND recperiod.rec_calc_period_id = period_lines.rec_calc_period_id
4911 AND recperiod.start_date > p_fst_end_date
4912 AND recperiod.end_date > p_fst_end_date
4913 AND recperiod.start_date < p_start_date
4914 AND recperiod.end_date < p_start_date
4915 AND lineconst.rec_agr_line_id = period_lines.rec_agr_line_id
4916 AND recperiod.as_of_date between lineconst.start_date and lineconst.end_date
4917 AND lineconst.RELATION = 'MAX'
4918 AND lineconst.SCOPE = 'OPYC';
4919
4920 CURSOR csr_get_curr_percent (p_start_date date, p_end_date date, p_as_of_date date) is
4921 SELECT lineconst.VALUE
4922 FROM pn_rec_agr_linconst_all lineconst
4923 WHERE lineconst.rec_agr_line_id = p_rec_agr_line_id
4924 AND p_as_of_date between lineconst.start_date and lineconst.end_date
4925 AND lineconst.RELATION = 'MAX'
4926 AND lineconst.SCOPE = 'OPYC';
4927
4928 BEGIN
4929
4930 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_prior_period_cap (+) ');
4931
4932 OPEN csr_get_base_cap;
4933 FETCH csr_get_base_cap into l_cap_amount,l_start_date,l_end_date;
4934
4935 /* If it is the calculation for the 1st period will not get any row in the cursor */
4936
4937 IF csr_get_base_cap%NOTFOUND THEN
4938
4939 CLOSE csr_get_base_cap;
4940 IF p_called_from = 'CALCUI' THEN
4941 return null;
4942 ELSE
4943 RETURN -1;
4944 END IF;
4945
4946 /* If it is recalulate of the 1st period then also return back as -1 */
4947
4948 ELSIF csr_get_base_cap%FOUND AND l_start_date = p_start_date AND l_end_date = p_end_date THEN
4949
4950 CLOSE csr_get_base_cap;
4951 IF p_called_from = 'CALCUI' THEN
4952 return null;
4953 ELSE
4954 RETURN -1;
4955 END IF;
4956
4957 END IF;
4958 CLOSE csr_get_base_cap;
4959
4960 /* Derive the prior period cap */
4961
4962 OPEN csr_get_prior_periods(p_start_date, l_end_date);
4963
4964 LOOP
4965
4966 FETCH csr_get_prior_periods into l_percent;
4967 EXIT WHEN csr_get_prior_periods%NOTFOUND;
4968 l_cap_amount := l_cap_amount + (l_cap_amount * l_percent/100);
4969
4970 END LOOP;
4971
4972 CLOSE csr_get_prior_periods;
4973
4974 /* Derive the percent for the current period */
4975
4976 OPEN csr_get_curr_percent(p_start_date, p_end_date, p_as_of_date);
4977 FETCH csr_get_curr_percent into l_percent;
4978 IF csr_get_curr_percent%NOTFOUND THEN
4979
4980 close csr_get_curr_percent;
4981 IF p_called_from = 'CALCUI' THEN
4982 return null;
4983 ELSE
4984 RETURN -1;
4985 END IF;
4986
4987 ELSE
4988
4989 l_cap_amount := l_cap_amount + (l_cap_amount * l_percent/100);
4990 close csr_get_curr_percent;
4991
4992 END IF;
4993
4994 pnp_debug_pkg.log('PN_REC_CALC_PKG.get_prior_period_cap (-) ');
4995
4996 RETURN l_cap_amount;
4997
4998 EXCEPTION
4999
5000 WHEN OTHERS
5001 THEN
5002 pnp_debug_pkg.log('Error getting prior year cap '|| to_char(sqlcode));
5003 IF p_called_from = 'CALCUI' THEN
5004 return null;
5005 ELSE
5006 return -99;
5007 END IF;
5008
5009
5010 END get_prior_period_cap;
5011
5012 /*===========================================================================+
5013 | PROCEDURE
5014 | LOCK_AREA_EXP_CLASS_DTL
5015 |
5016 | DESCRIPTION
5017 | Lock the status of the area class and expense class details for the approved billing term
5018 |
5019 | SCOPE - PUBLIC
5020 |
5021 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
5022 |
5023 | ARGUMENTS : IN: p_term_id
5024 |
5025 | OUT:
5026 |
5027 | RETURNS : None
5028 |
5029 | NOTES :
5030 |
5031 | MODIFICATION HISTORY
5032 |
5033 | 26-aug-2003 Ashish o Created
5034 +===========================================================================*/
5035
5036
5037 PROCEDURE lock_area_exp_cls_dtl(p_payment_term_id in pn_payment_terms_all.payment_term_id%TYPE) is
5038
5039 cursor c_term is
5040 select term.period_billrec_id,
5041 term.rec_agr_line_id
5042 from pn_payment_terms_all term
5043 where term.payment_term_id = p_payment_term_id;
5044
5045 cursor c_lines_cons is
5046 select
5047 agr.customer_id as customer_id,
5048 agr.lease_id as lease_id,
5049 agr.location_id as location_id,
5050 period.start_date as start_date,
5051 period.end_date as end_date ,
5052 period.as_of_date as as_of_date ,
5053 agrlines.rec_agr_line_id as rec_agr_line_id
5054 from pn_payment_terms_all term
5055 ,pn_rec_period_bill_all bill
5056 ,pn_rec_calc_periods_all period
5057 ,pn_rec_agr_lines_all agrlines
5058 ,pn_rec_agreements_all agr
5059 where term.payment_term_id = p_payment_term_id
5060 and bill.period_billrec_id = term.period_billrec_id
5061 and period.rec_calc_period_id = bill.rec_calc_period_id
5062 and agrlines.rec_agreement_id = bill.rec_agreement_id
5063 and period.as_of_date between agrlines.start_date and agrlines.end_date
5064 and agr.rec_agreement_id = agrlines.rec_agreement_id
5065 ;
5066
5067 cursor c_lines is
5068 select
5069 agr.customer_id as customer_id,
5070 agr.lease_id as lease_id,
5071 agr.location_id as location_id,
5072 period.start_date as start_date,
5073 period.end_date as end_date ,
5074 period.as_of_date as as_of_date ,
5075 agrlines.rec_agr_line_id as rec_agr_line_id
5076 from pn_payment_terms_all term
5077 ,pn_rec_period_bill_all bill
5078 ,pn_rec_calc_periods_all period
5079 ,pn_rec_agr_lines_all agrlines
5080 ,pn_rec_agreements_all agr
5081 where term.payment_term_id = p_payment_term_id
5082 and bill.period_billrec_id = term.period_billrec_id
5083 and period.rec_calc_period_id = bill.rec_calc_period_id
5084 and agrlines.rec_agreement_id = bill.rec_agreement_id
5085 and agrlines.rec_agr_line_id = term.rec_agr_line_id
5086 and period.as_of_date between agrlines.start_date and agrlines.end_date
5087 and agr.rec_agreement_id = agrlines.rec_agreement_id
5088 ;
5089
5090 cursor c_expense_class_detail(p_customer_id number,
5091 p_lease_id number,
5092 p_location_id number,
5093 p_calc_period_start_date date,
5094 p_calc_period_end_date date,
5095 p_calc_period_as_of_date date,
5096 p_rec_agr_line_id number) is
5097 SELECT
5098 exp_detail_hdr.expense_class_dtl_id as expense_class_dtl_id
5099 FROM pn_rec_expcl_all rec_exp_class
5100 ,pn_rec_agr_linexp_all lineexp
5101 ,pn_rec_expcl_dtl_all exp_detail_hdr
5102 ,pn_rec_exp_line_all exp_extract_hdr
5103 ,pn_rec_expcl_dtlln_all exp_detail_line
5104 WHERE exp_detail_hdr.expense_class_dtl_id = exp_detail_line.expense_class_dtl_id
5105 AND exp_detail_line.cust_account_id = p_customer_id
5106 AND exp_detail_line.lease_id = p_lease_id
5107 AND exp_detail_line.location_id = p_location_id
5108 AND exp_extract_hdr.to_date = p_calc_period_end_date
5109 AND exp_extract_hdr.from_date = p_calc_period_start_date
5110 AND exp_extract_hdr.as_of_date = p_calc_period_as_of_date
5111 AND exp_extract_hdr.expense_line_id = exp_detail_hdr.expense_line_id
5112 AND exp_detail_hdr.expense_class_id = rec_exp_class.expense_class_id
5113 AND rec_exp_class.expense_class_id = lineexp.expense_class_id
5114 AND p_calc_period_as_of_date between lineexp.start_date and lineexp.end_date
5115 AND lineexp.rec_agr_line_id = p_rec_agr_line_id
5116 ;
5117
5118
5119 cursor c_area_class_detail( p_rec_agr_line_id number,
5120 p_as_of_date date,
5121 p_calc_period_start_date date,
5122 p_calc_period_end_date date,
5123 p_customer_id number,
5124 p_lease_id number,
5125 p_location_id number
5126 ) is
5127 SELECT
5128 area_class_dtl_hdr.area_class_dtl_id as area_class_dtl_id
5129 FROM pn_rec_arcl_dtlln_all area_class_dtl_lines
5130 ,pn_rec_arcl_dtl_all area_class_dtl_hdr
5131 ,pn_rec_agr_linarea_all linearea
5132 ,pn_rec_arcl_all aclass
5133 WHERE linearea.rec_agr_line_id = p_rec_agr_line_id
5134 AND p_as_of_date between linearea.start_date and linearea.end_date
5135 AND aclass.area_class_id = linearea.area_class_id
5136 AND area_class_dtl_hdr.area_class_id = aclass.area_class_id
5137 AND area_class_dtl_hdr.as_of_date = p_as_of_date
5138 AND area_class_dtl_hdr.from_date = p_calc_period_start_date
5139 AND area_class_dtl_hdr.to_date = p_calc_period_end_date
5140 AND area_class_dtl_lines.area_class_dtl_id = area_class_dtl_hdr.area_class_dtl_id
5141 AND area_class_dtl_lines.cust_account_id = p_customer_id
5142 AND area_class_dtl_lines.lease_id = p_lease_id
5143 AND area_class_dtl_lines.location_id = p_location_id
5144 ;
5145
5146 l_customer_id pn_rec_agreements_all.customer_id%TYPE;
5147 l_lease_id pn_rec_agreements_all.lease_id%TYPE;
5148 l_location_id pn_rec_agreements_all.location_id%TYPE;
5149 l_start_date pn_rec_calc_periods_all.start_date%TYPE;
5150 l_end_date pn_rec_calc_periods_all.end_date%TYPE;
5151 l_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE;
5152 l_rec_agr_line_id pn_rec_agr_lines_all.rec_agr_line_id%TYPE;
5153
5154 begin
5155
5156 pnp_debug_pkg.log('PN_REC_CALC_PKG.lock_area_exp_cls_dtl (+) ');
5157
5158 FOR l_term_rec in c_term
5159 LOOP
5160
5161 /* If it is a consolidated term then get all the recovery lines */
5162
5163 IF nvl(l_term_rec.rec_agr_line_id, -1) = -1 THEN
5164
5165 OPEN c_lines_cons;
5166
5167 ELSE
5168
5169 OPEN c_lines;
5170
5171 END IF;
5172
5173 LOOP
5174
5175 IF c_lines_cons%ISOPEN THEN
5176
5177 FETCH c_lines_cons INTO
5178 l_customer_id,
5179 l_lease_id,
5180 l_location_id,
5181 l_start_date,
5182 l_end_date,
5183 l_as_of_date,
5184 l_rec_agr_line_id;
5185
5186 EXIT when c_lines_cons%NOTFOUND;
5187
5188 ELSIF c_lines%ISOPEN THEN
5189
5190 FETCH c_lines INTO
5191 l_customer_id,
5192 l_lease_id,
5193 l_location_id,
5194 l_start_date,
5195 l_end_date,
5196 l_as_of_date,
5197 l_rec_agr_line_id ;
5198
5199 EXIT when c_lines%NOTFOUND;
5200
5201 END IF;
5202
5203 FOR l_exp_class_detail_rec in c_expense_class_detail(
5204 l_customer_id,
5205 l_lease_id,
5206 l_location_id,
5207 l_start_date,
5208 l_end_date,
5209 l_as_of_date,
5210 l_rec_agr_line_id )
5211 LOOP
5212 update pn_rec_expcl_dtl_all
5213 set status = 'LOCKED',
5214 last_update_date = sysdate,
5215 last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
5216 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
5217 where
5218 expense_class_dtl_id = l_exp_class_detail_rec.expense_class_dtl_id;
5219
5220 END LOOP;
5221
5222 FOR l_area_class_detail_rec in c_area_class_detail(
5223 l_rec_agr_line_id,
5224 l_as_of_date,
5225 l_start_date,
5226 l_end_date,
5227 l_customer_id,
5228 l_lease_id,
5229 l_location_id)
5230 LOOP
5231 update pn_rec_arcl_dtl_all
5232 set status = 'LOCKED' ,
5233 last_update_date = sysdate,
5234 last_updated_by = NVL(fnd_profile.value('USER_ID'),0),
5235 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0)
5236 where
5237 area_class_dtl_id = l_area_class_detail_rec.area_class_dtl_id;
5238 END LOOP;
5239
5240 END LOOP;
5241
5242 IF c_lines_cons%ISOPEN THEN
5243
5244 CLOSE c_lines_cons;
5245
5246 ELSIF c_lines%ISOPEN THEN
5247
5248 CLOSE c_lines;
5249
5250 END IF;
5251
5252 END LOOP;
5253 commit;
5254 pnp_debug_pkg.log('PN_REC_CALC_PKG.lock_area_exp_cls_dtl (-) ');
5255 Exception
5256 when others then
5257 pnp_debug_pkg.log(' error in PN_REC_CALC_PKG.lock_area_exp_cls_dtl :'||to_char(sqlcode)||' : '||sqlerrm);
5258 raise;
5259
5260 END lock_area_exp_cls_dtl ;
5261
5262 /*===========================================================================+
5263 | FUNCTION
5264 | validate_create_calc_period
5265 |
5266 | DESCRIPTION
5267 | If the period record already exists for the recovery agreement for the
5268 | calculation period specified through start_date, end_date, as_of_date
5269 | then returns -1 else, creates a period record and returns rec_calc_period_id.
5270 |
5271 | SCOPE - PUBLIC
5272 |
5273 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
5274 |
5275 | ARGUMENTS : IN: p_rec_agreement_id
5276 | p_start_date
5277 | p_end_date
5278 | p_as_of_date
5279 |
5280 | RETURNS : rec_calc_period_id
5281 |
5282 | NOTES :
5283 |
5284 | MODIFICATION HISTORY
5285 |
5286 | 05-Dec-2005 acprakas o Created
5287 | 30-Dec-2008 acprakas o Bug#7645185. Modified the insert stmt to populate org_id also.
5288 +===========================================================================*/
5289
5290 FUNCTION validate_create_calc_period(p_rec_agreement_id pn_rec_agreements_all.REC_AGREEMENT_ID%TYPE,
5291 p_start_date pn_rec_calc_periods_all.start_date%TYPE,
5292 p_end_date pn_rec_calc_periods_all.end_date%TYPE,
5293 p_as_of_date pn_rec_calc_periods_all.as_of_date%TYPE)
5294 RETURN NUMBER
5295 IS
5296 l_rec_calc_period_id pn_rec_calc_periods_all.rec_calc_period_id%TYPE;
5297 l_period_count NUMBER;
5298 l_org_id NUMBER;
5299 BEGIN
5300 pnp_debug_pkg.log('PN_REC_CALC_PKG.validate_create_calc_period (+) ');
5301
5302 SELECT count(1)
5303 INTO l_period_count
5304 FROM pn_rec_calc_periods_all
5305 WHERE rec_agreement_id = p_rec_agreement_id
5306 AND start_date = p_start_date
5307 AND end_date = p_end_date
5308 AND as_of_date = p_as_of_date;
5309
5310 l_org_id := pn_mo_cache_utils.get_current_org_id;
5311
5312 IF l_period_count = 0
5313 THEN
5314 select pn_rec_calc_periods_s.nextval
5315 into l_rec_calc_period_id
5316 from dual;
5317
5318 insert into pn_rec_calc_periods_all
5319 (rec_calc_period_id,
5320 REC_AGREEMENT_ID,
5321 last_update_date,
5322 last_updated_by,
5323 creation_date,
5324 created_by,
5325 last_update_login,
5326 start_date,
5327 end_date,
5328 as_of_date,
5329 org_id
5330 )
5331 values
5332 (l_rec_calc_period_id,
5333 p_rec_agreement_id,
5334 sysdate,
5335 TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
5336 sysdate,
5337 TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
5338 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
5339 p_start_date,
5340 p_end_date,
5341 p_as_of_date,
5342 l_org_id
5343 );
5344 return l_rec_calc_period_id;
5345 ELSE
5346 fnd_message.set_name ('PN','PN_REC_AGRMNT_PERIOD_EXIST');
5347 pnp_debug_pkg.put_log_msg(fnd_message.get);
5348 return -1;
5349 END IF;
5350 pnp_debug_pkg.log('PN_REC_CALC_PKG.validate_create_calculation_period (-) ');
5351
5352 EXCEPTION
5353 WHEN OTHERS THEN
5354 pnp_debug_pkg.log('error in PN_REC_CALC_PKG.validate_create_calc_period :'||to_char(sqlcode)||' : '||sqlerrm);
5355 raise;
5356 END validate_create_calc_period;
5357
5358 END PN_REC_CALC_PKG;