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