[Home] [Help]
PACKAGE BODY: APPS.PN_VARIABLE_TERM_PKG
Source
1 PACKAGE BODY pn_variable_term_pkg AS
2 -- $Header: PNVTERMB.pls 120.17.12010000.3 2009/12/24 07:09:51 jsundara ship $
3
4 -------------------------------------------------------------------------------
5 -- PROCEDURE : create_payment_term_batch
6 -- DESCRIPTION: This procedure is called by the payment term
7 -- creation concurrent program.
8 --
9 -- 15-AUG-02 dthota o Changes for Mass Calculate Variable Rent.
10 -- Added p_period_date parameter to
11 -- create_payment_term_batch, CURSOR csr_for_inv,
12 -- CURSOR csr_act_inv,CURSOR csr_var_inv.
13 -- 09-Jan-03 dthota o Changed p_period_date to VARCHAR2 from DATE in
14 -- create_payment_term_batch,CURSOR csr_for_inv,
15 -- CURSOR csr_act_inv,CURSOR csr_var_inv and added
16 -- fnd_date.canonical_to_date before p_period_date
17 -- in the WHERE clauses of the cursors.
18 -- Fix for bug # 2733870
19 -- 21-Oct-04 vmmehta o Bug# 3942264. Added code to reset term_status if term
20 -- creation fails for actual/forecasted/variance terms.
21 -- 26-Oct-06 Shabda o Changed cursor csr_var_inv to accomodate true_ups
22 -- 12-DEC-07 acprakas o Bug#6490896. Modified to create reversal terms for
23 -- invoices created for reversal.
24 -- 24-DEC-09 jsundara o BUg#9094493. If the profile option PN_VAR_VOL_INV_PRD is set,
25 -- make a call to find_volume_continuous only if VR termination date is > invoice date
26
27 -------------------------------------------------------------------------------
28
29 PROCEDURE create_payment_term_batch(
30 errbuf OUT NOCOPY VARCHAR2,
31 retcode OUT NOCOPY VARCHAR2,
32 p_lease_num_from IN VARCHAR2,
33 p_lease_num_to IN VARCHAR2,
34 p_location_code_from IN VARCHAR2,
35 p_location_code_to IN VARCHAR2,
36 p_vrent_num_from IN VARCHAR2,
37 p_vrent_num_to IN VARCHAR2,
38 p_period_num_from IN NUMBER,
39 p_period_num_to IN NUMBER,
40 p_responsible_user IN NUMBER,
41 p_period_id IN NUMBER,
42 p_org_id IN NUMBER,
43 p_period_date IN VARCHAR2
44 ) IS
45 CURSOR csr_get_vrent_wloc IS
46 SELECT pvr.lease_id,
47 pvr.var_rent_id,
48 pvr.rent_num,
49 pvr.invoice_on,
50 pvr.location_id,
51 pvr.currency_code,
52 pvr.term_template_id,
53 per.period_id,
54 per.period_num,
55 pl.lease_class_code,
56 pl.org_id
57 FROM pn_leases pl,
58 pn_lease_details_all pld,
59 pn_var_rents_all pvr,
60 pn_locations_all ploc,
61 pn_var_periods_all per
62 WHERE pl.lease_id = pvr.lease_id
63 AND pld.lease_id = pvr.lease_id
64 AND ploc.location_id = pvr.location_id
65 AND pvr.var_rent_id = per.var_rent_id
66 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
67 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
68 AND ploc.location_code >= nvl(p_location_code_from, ploc.location_code)
69 AND ploc.location_code <= nvl(p_location_code_to, ploc.location_code)
70 AND pvr.rent_num >= nvl(p_vrent_num_from,pvr.rent_num)
71 AND pvr.rent_num <= nvl(p_vrent_num_to,pvr.rent_num)
72 AND per.period_num >= nvl(p_period_num_from,per.period_num)
73 AND per.period_num <= nvl(p_period_num_to,period_num)
74 AND pld.responsible_user = nvl(p_responsible_user, pld.responsible_user)
75 AND (pl.org_id = p_org_id or p_org_id is null)
76 ORDER BY pl.lease_id, pvr.var_rent_id,per.period_num;
77
78 CURSOR csr_get_vrent_woloc IS
79 SELECT pvr.lease_id,
80 pvr.var_rent_id,
81 pvr.rent_num,
82 pvr.invoice_on,
83 pvr.location_id,
84 pvr.currency_code,
85 pvr.term_template_id,
86 per.period_id,
87 per.period_num,
88 pl.lease_class_code,
89 pl.org_id
90 FROM pn_var_rents_all pvr,
91 pn_leases pl,
92 pn_lease_details_all pld,
93 pn_var_periods_all per
94 WHERE pl.lease_id = pvr.lease_id
95 AND pld.lease_id = pvr.lease_id
96 AND pvr.var_rent_id = per.var_rent_id
97 AND pl.lease_num >= nvl(p_lease_num_from, pl.lease_num)
98 AND pl.lease_num <= nvl(p_lease_num_to, pl.lease_num)
99 AND pvr.rent_num >= nvl(p_vrent_num_from,pvr.rent_num)
100 AND pvr.rent_num <= nvl(p_vrent_num_to,pvr.rent_num)
101 AND per.period_num >= nvl(p_period_num_from,per.period_num)
102 AND per.period_num <= nvl(p_period_num_to,period_num)
103 AND pld.responsible_user = nvl(p_responsible_user, pld.responsible_user)
107
104 AND per.period_id = nvl(p_period_id,per.period_id)
105 AND (pl.org_id = p_org_id or p_org_id is null)
106 ORDER BY pl.lease_id,pvr.var_rent_id,per.period_num;
108 /* Get the forecasted amounts */
109
110 CURSOR csr_for_inv(ip_period_id NUMBER)
111 IS
112 SELECT var_rent_id,
113 var_rent_inv_id,
114 invoice_date,
115 for_per_rent,
116 period_id
117 FROM pn_var_rent_inv_all
118 WHERE period_id = ip_period_id
119 AND adjust_num = 0
120 AND nvl(for_per_rent,0) <> 0
121 AND forecasted_exp_code = 'N'
122 AND forecasted_term_status = decode(p_period_id,null,'N','Y')
123 AND pn_variable_amount_pkg.find_if_term_exists(var_rent_inv_id,'FORECASTED') ='N'
124 AND invoice_date <= nvl(fnd_date.canonical_to_date(p_period_date),to_date('12/31/4712','mm/dd/yyyy'))
125 ORDER BY invoice_date;
126
127 /* Get the actual rent amounts */
128
129 CURSOR csr_act_inv(ip_period_id NUMBER)
130 IS
131 SELECT var_rent_id,
132 var_rent_inv_id,
133 invoice_date,
134 adjust_num,
135 actual_invoiced_amount,
136 period_id,
137 credit_flag
138 FROM pn_var_rent_inv_all
139 WHERE period_id = ip_period_id
140 AND actual_exp_code = 'N'
141 AND nvl(actual_invoiced_amount,0) <> 0
142 AND actual_term_status = decode(p_period_id,null,'N','Y')
143 AND pn_variable_amount_pkg.find_if_term_exists(var_rent_inv_id,'ACTUAL') ='N'
144 AND invoice_date <= nvl(fnd_date.canonical_to_date(p_period_date),to_date('12/31/4712','mm/dd/yyyy'))
145 ORDER BY invoice_date;
146
147 /* get the actual-forecasted rent amounts */
148
149 CURSOR csr_var_inv(ip_period_id NUMBER)
150 IS
151 SELECT inv.var_rent_id,
152 inv.var_rent_inv_id,
153 inv.adjust_num,
154 inv.invoice_date,
155 inv.period_id,
156 decode(inv.adjust_num,0,(inv.actual_invoiced_amount-NVL(inv.for_per_rent,0)),
157 inv.actual_invoiced_amount) act_for_amt
158 FROM pn_var_rent_inv_all inv
159 WHERE inv.period_id = ip_period_id
160 AND inv.variance_exp_code = 'N'
161 AND nvl(decode(inv.adjust_num,0,(inv.actual_invoiced_amount-NVL(inv.for_per_rent,0)),
162 inv.actual_invoiced_amount),0) <> 0
163 AND inv.variance_term_status = decode(p_period_id,null,'N','Y')
164 AND not exists (SELECT null
165 FROM pn_var_grp_dates_all gd
166 WHERE gd.invoice_date = inv.invoice_date
167 AND gd.period_id = inv.period_id
168 AND gd.var_rent_id = inv.var_rent_id
169 AND nvl(gd.forecasted_exp_code,'N') = 'N')
170 AND pn_variable_amount_pkg.find_if_term_exists(inv.var_rent_inv_id,'VARIANCE') = 'N'
171 AND invoice_date <= nvl(fnd_date.canonical_to_date(p_period_date),to_date('12/31/4712','mm/dd/yyyy'))
172 ORDER BY inv.invoice_date;
173
174 CURSOR payment_cur(p_invoice_date DATE,p_var_rent_id NUMBER) IS
175 SELECT payment_term_id
176 FROM pn_payment_terms_all
177 WHERE var_rent_inv_id IN (SELECT var_rent_inv_id
178 FROM pn_var_rent_inv_all
179 WHERE invoice_date = p_invoice_date
180 AND var_rent_id = p_var_rent_id);
181
182
183 l_rent_num pn_var_rents.rent_num%type;
184 l_invoice_on pn_var_rents.invoice_on%type;
185 l_period_id pn_var_periods.period_id%type;
186 l_period_num pn_var_periods.period_num%type;
187 l_lease_id pn_var_rents.lease_id%type;
188 l_location_id pn_var_rents.location_id%type;
189 l_var_rent_id pn_var_rents.var_rent_id%type;
190 l_pre_var_rent_id pn_var_rents.var_rent_id%type;
191 l_context VARCHAR2(2000);
192 l_errmsg VARCHAR2(2000);
193 l_org_id pn_leases.org_id%type;
194 l_term_temp_id pn_payment_terms.term_template_id%TYPE;
195 l_lease_cls_code pn_leases.lease_class_code%TYPE;
196 l_err_flag VARCHAR2(1);
197 l_inv_sch_date DATE;
198 l_inv_start_date DATE;
199 term_count NUMBER;
200 err_flag BOOLEAN := FALSE;
201 l_trmn_dt DATE; /* 9094493 */
202
203 BEGIN
204 pn_variable_amount_pkg.put_log('pn_variable_term_pkg.create_payment_term_batch (+)' );
205
206 fnd_message.set_name ('PN','PN_VTERM_INP');
207 fnd_message.set_token ('TO_NUM',p_lease_num_to);
208 fnd_message.set_token ('FROM_NUM',p_lease_num_from);
209 fnd_message.set_token ('FROM_CODE',p_location_code_from);
210 fnd_message.set_token ('TO_CODE',p_location_code_to);
211 fnd_message.set_token ('VRN_FROM',p_vrent_num_from);
212 fnd_message.set_token ('VRN_TO',p_vrent_num_to);
213 fnd_message.set_token ('PRD_FROM',p_period_num_from);
214 fnd_message.set_token ('PRD_TO',p_period_num_to);
215 fnd_message.set_token ('USR',p_responsible_user);
216 pnp_debug_pkg.put_log_msg(fnd_message.get);
217
218
219 /* Retrieve operating unit attributes and stores them in the cache */
220 l_context := 'Retreiving operating unit attributes';
221
222 --pn_mo_global_cache.populate;
223
224
225 /* Checking Location Code From, Location Code To to open appropriate cursor */
226
227 IF p_location_code_from IS NOT NULL or p_location_code_to IS NOT NULL THEN
228 OPEN csr_get_vrent_wloc;
229 ELSE
230 OPEN csr_get_vrent_woloc;
231 END IF;
232
233 l_pre_var_rent_id := NULL;
234 LOOP
235
236 IF csr_get_vrent_wloc%ISOPEN THEN
237 FETCH csr_get_vrent_wloc INTO l_lease_id, l_var_rent_id, l_rent_num,
241 l_lease_cls_code, l_org_id;
238 l_invoice_on, l_location_id,
239 g_currency_code, l_term_temp_id,
240 l_period_id, l_period_num,
242 EXIT WHEN csr_get_vrent_wloc%NOTFOUND;
243 ELSIF csr_get_vrent_woloc%ISOPEN THEN
244 FETCH csr_get_vrent_woloc INTO l_lease_id, l_var_rent_id, l_rent_num,
245 l_invoice_on, l_location_id,
246 g_currency_code, l_term_temp_id,
247 l_period_id, l_period_num,
248 l_lease_cls_code, l_org_id;
249 EXIT WHEN csr_get_vrent_woloc%NOTFOUND;
250 END IF;
251
252
253 IF l_var_rent_id <> NVL(l_pre_var_rent_id,-9999) THEN
254 l_err_flag := 'N';
255 l_pre_var_rent_id := l_var_rent_id;
256
257 IF NOT pnp_util_func.validate_term_template(p_term_temp_id => l_term_temp_id,
258 p_lease_cls_code => l_lease_cls_code) THEN
259
260 l_err_flag := 'Y';
261 fnd_message.set_name ('PN', 'PN_MISS_TERM_TEMP_DATA');
262 l_errmsg := fnd_message.get;
263 pn_variable_amount_pkg.put_output(l_errmsg);
264
265 fnd_message.set_name ('PN','PN_SOI_VRN');
266 fnd_message.set_token ('NUM',l_rent_num);
267 pnp_debug_pkg.put_log_msg(fnd_message.get);
268
269 END IF;
270 END IF;
271
272 IF l_err_flag = 'N' THEN
273
274 pn_variable_amount_pkg.put_output ('+---------------------------------------------------------------+');
275 fnd_message.set_name ('PN','PN_RICAL_PROC');
276 pnp_debug_pkg.put_log_msg(fnd_message.get||' ...');
277
278 fnd_message.set_name ('PN','PN_SOI_VRN');
279 fnd_message.set_token ('NUM',l_rent_num);
280 pnp_debug_pkg.put_log_msg(fnd_message.get);
281
282 fnd_message.set_name ('PN','PN_VTERM_PRD_NUM');
283 fnd_message.set_token ('NUM',l_period_num);
284 pnp_debug_pkg.put_log_msg(fnd_message.get);
285
286 IF l_invoice_on = 'FORECASTED' THEN
287
288 l_context := 'opening csr_for_inv';
289
290 FOR rec_for_inv in csr_for_inv(l_period_id) LOOP
291
292 fnd_message.set_name ('PN','PN_VTERM_FORC_TRM');
293 pnp_debug_pkg.put_log_msg(fnd_message.get||' ...');
294
295 err_flag := FALSE;
296
297 /*l_inv_start_date := pn_var_rent_calc_pkg.inv_start_date
298 (inv_start_date => rec_for_inv.invoice_date
299 ,vr_id => l_var_rent_id
300 ,approved_status => 'N'); */
301
302 l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date(rec_for_inv.invoice_date,l_var_rent_id,l_period_id);
303 fnd_message.set_name ('PN','PN_SOI_INV_DT');
304 fnd_message.set_token ('DATE',l_inv_sch_date);
305 pnp_debug_pkg.put_log_msg(fnd_message.get);
306
307 fnd_message.set_name ('PN','PN_VTERM_FORC_RENT');
308 fnd_message.set_token ('RENT',round(rec_for_inv.for_per_rent,2));
309 pnp_debug_pkg.put_log_msg(fnd_message.get);
310
311
312 l_context := 'Checking if volume exists for all group dates and line items';
313
314 IF pn_variable_amount_pkg.find_volume_exists(rec_for_inv.period_id,
315 rec_for_inv.invoice_date,
316 'FORECASTED')='N' THEN
317
318 fnd_message.set_name('PN','PN_VAR_VOL_HIST');
319 l_errmsg := fnd_message.get;
320 pn_variable_amount_pkg.put_output('+-----------------------------------------------------------+');
321 pn_variable_amount_pkg.put_output(l_errmsg);
322 pn_variable_amount_pkg.put_output('+------------------------------------------------------------+');
323 errbuf := l_errmsg;
324
325
326 ELSE
327
328 IF NVL(fnd_profile.value('PN_VAR_VOL_INV_PRD'),'N')='Y' THEN
329 IF pn_variable_term_pkg.find_volume_continuous_for(rec_for_inv.var_rent_id,
330 rec_for_inv.period_id,
331 rec_for_inv.invoice_date,
332 'FORECASTED'
333 ) = 'N' THEN
334 fnd_message.set_name('PN','PN_VOL_INV_PRD');
335 l_errmsg := fnd_message.get;
336 pn_variable_amount_pkg.put_output('+-----------------------------------------------------------+');
337 pn_variable_amount_pkg.put_output(l_errmsg);
338 pn_variable_amount_pkg.put_output('+------------------------------------------------------------+');
339 errbuf := l_errmsg;
340
341 err_flag := TRUE;
342
343 END IF;
344 END IF;
345
346 IF (NOT err_flag) THEN
347 l_context:='Creating Forecasted Payment term';
348
352 ,p_payment_amount => rec_for_inv.for_per_rent
349 savepoint create_terms;
350 create_payment_terms(p_lease_id => l_lease_id
351 ,p_period_id => rec_for_inv.period_id
353 ,p_invoice_date => rec_for_inv.invoice_date
354 ,p_var_rent_id => rec_for_inv.var_rent_id
355 ,p_var_rent_inv_id => rec_for_inv.var_rent_inv_id
356 ,p_location_id => l_location_id
357 ,p_var_rent_type => 'FORECASTED'
358 ,p_org_id => l_org_id );
359
360 -- Check if term exists and set forecasted_term_status accordingly.
361
362 term_count := 0;
363
364 SELECT count(*) INTO term_count
365 FROM pn_payment_terms_all
366 WHERE var_rent_inv_id = rec_for_inv.var_rent_inv_id
367 AND var_rent_type = 'FORECASTED';
368
369 IF term_count > 0 THEN
370 UPDATE pn_var_rent_inv_all
371 SET forecasted_term_status='Y',
372 last_update_date = SYSDATE,
373 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
374 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
375 WHERE var_rent_inv_id = rec_for_inv.var_rent_inv_id;
376 ELSE
377 pn_variable_amount_pkg.put_log('term not found ...');
378 UPDATE pn_var_rent_inv_all
379 SET forecasted_term_status='N',
380 last_update_date = SYSDATE,
381 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
382 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
383 WHERE var_rent_inv_id = rec_for_inv.var_rent_inv_id;
384 END IF;
385
386 END IF;
387
388 END IF;
389 END LOOP;
390
391 l_context :='opening csr_var_inv';
392
393 FOR rec_var_inv in csr_var_inv(l_period_id) LOOP
394 fnd_message.set_name ('PN','PN_VTERM_FORC_TRM');
395 pnp_debug_pkg.put_log_msg(fnd_message.get||' ...');
396
397 err_flag := FALSE;
398 /*l_inv_start_date := pn_var_rent_calc_pkg.inv_start_date
399 (inv_start_date => rec_var_inv.invoice_date
400 ,vr_id => l_var_rent_id
401 ,approved_status => 'N'); */
402
403 l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date(rec_var_inv.invoice_date,l_var_rent_id,l_period_id);
404 fnd_message.set_name ('PN','PN_SOI_INV_DT');
405 fnd_message.set_token ('DATE',l_inv_sch_date);
406 pnp_debug_pkg.put_log_msg(fnd_message.get);
407
408 l_context := 'Checking if volume exists for all group dates and line items';
409
410 IF pn_variable_amount_pkg.find_volume_exists(rec_var_inv.period_id,
411 rec_var_inv.invoice_date,
412 'ACTUAL')='N' THEN
413
414 fnd_message.set_name('PN','PN_VAR_VOL_HIST');
415 l_errmsg := fnd_message.get;
416 pn_variable_amount_pkg.put_output('+-----------------------------------------------------------+');
417 pn_variable_amount_pkg.put_output(l_errmsg);
418 pn_variable_amount_pkg.put_output('+------------------------------------------------------------+');
419 errbuf := l_errmsg;
420
421 ELSE
422
423 IF NVL(fnd_profile.value('PN_VAR_VOL_INV_PRD'),'N')='Y' THEN
424 IF pn_variable_term_pkg.find_volume_continuous_for(rec_var_inv.var_rent_id,
425 rec_var_inv.period_id,
426 rec_var_inv.invoice_date,
427 'ACTUAL'
428 ) = 'N' THEN
429 fnd_message.set_name('PN','PN_VOL_INV_PRD');
430 l_errmsg := fnd_message.get;
431 pn_variable_amount_pkg.put_output('+-----------------------------------------------------------+');
432 pn_variable_amount_pkg.put_output(l_errmsg);
433 pn_variable_amount_pkg.put_output('+------------------------------------------------------------+');
434 errbuf := l_errmsg;
435
436 err_flag := TRUE;
437
438 END IF;
439 END IF;
440
441 IF (NOT err_flag) THEN
442
443 pn_variable_amount_pkg.put_output('Actual-Forecasted Amount :'||round(rec_var_inv.act_for_amt,2));
444 l_context :='Creating Variance Payment term';
445
446 savepoint create_terms;
447
448 create_payment_terms(p_lease_id => l_lease_id
449 ,p_period_id => rec_var_inv.period_id
453 ,p_var_rent_inv_id => rec_var_inv.var_rent_inv_id
450 ,p_payment_amount => rec_var_inv.act_for_amt
451 ,p_invoice_date => rec_var_inv.invoice_date
452 ,p_var_rent_id => rec_var_inv.var_rent_id
454 ,p_location_id => l_location_id
455 ,p_var_rent_type => 'VARIANCE'
456 ,p_org_id => l_org_id );
457
458 -- Check if term exists and set variance_term_status accordingly.
459
460 term_count := 0;
461
462 SELECT count(*) INTO term_count
463 FROM pn_payment_terms_all
464 WHERE var_rent_inv_id = rec_var_inv.var_rent_inv_id
465 AND var_rent_type = 'VARIANCE';
466
467 IF term_count > 0 THEN
468 UPDATE pn_var_rent_inv_all
469 SET variance_term_status='Y',
470 last_update_date = SYSDATE,
471 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
472 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
473 WHERE var_rent_inv_id = rec_var_inv.var_rent_inv_id;
474
475 UPDATE pn_var_rent_inv_all
476 SET true_up_status = 'Y'
477 WHERE var_rent_inv_id = rec_var_inv.var_rent_inv_id
478 AND true_up_status IS NOT NULL;
479
480 ELSE
481 pn_variable_amount_pkg.put_log('term not found ...');
482 UPDATE pn_var_rent_inv_all
483 SET variance_term_status='N',
484 last_update_date = SYSDATE,
485 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
486 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
487 WHERE var_rent_inv_id = rec_var_inv.var_rent_inv_id;
488
489 UPDATE pn_var_rent_inv_all
490 SET true_up_status = 'N'
491 WHERE var_rent_inv_id = rec_var_inv.var_rent_inv_id
492 AND true_up_status IS NOT NULL;
493
494 END IF;
495
496 END IF;
497
498 END IF;
499 END LOOP;
500 END IF; -- IF l_invoice_on = 'FORECASTED'
501
502 IF l_invoice_on = 'ACTUAL' THEN
503
504 l_context :='opening csr_act_inv';
505 FOR rec_act_inv in csr_act_inv(l_period_id) LOOP
506
507 fnd_message.set_name ('PN','PN_VTERM_AFORC_TRM');
508 pnp_debug_pkg.put_log_msg(fnd_message.get);
509
510 err_flag := FALSE;
511
512 l_inv_sch_date := pn_var_rent_calc_pkg.inv_sch_date(rec_act_inv.invoice_date,l_var_rent_id,l_period_id);
513 fnd_message.set_name ('PN','PN_SOI_INV_DT');
514 fnd_message.set_token ('DATE',l_inv_sch_date);
515 pnp_debug_pkg.put_log_msg(fnd_message.get);
516
517 l_context := 'Checking if volume exists for all group dates and line items';
518
519 IF pn_variable_amount_pkg.find_volume_exists(rec_act_inv.period_id,
520 rec_act_inv.invoice_date,
521 'ACTUAL')='N' THEN
522 fnd_message.set_name('PN','PN_VAR_VOL_HIST');
523 l_errmsg := fnd_message.get;
524 pn_variable_amount_pkg.put_output('+-----------------------------------------------------------+');
525 pn_variable_amount_pkg.put_output(l_errmsg);
526 pn_variable_amount_pkg.put_output('+------------------------------------------------------------+');
527
528
529 ELSE
530
531 IF NVL(fnd_profile.value('PN_VAR_VOL_INV_PRD'),'N')='Y' and (l_trmn_dt >= rec_act_inv.invoice_date) THEN /* 9094493 */
532 IF pn_variable_term_pkg.find_volume_continuous(rec_act_inv.var_rent_id,
533 rec_act_inv.period_id,
534 rec_act_inv.invoice_date
535 ) = 'N' THEN
536 fnd_message.set_name('PN','PN_VOL_INV_PRD');
537 l_errmsg := fnd_message.get;
538 pn_variable_amount_pkg.put_output('+-----------------------------------------------------------+');
539 pn_variable_amount_pkg.put_output(l_errmsg);
540 pn_variable_amount_pkg.put_output('+------------------------------------------------------------+');
541 errbuf := l_errmsg;
542
543 err_flag := TRUE;
544
545 END IF;
546 END IF;
547
548 IF (NOT err_flag) THEN
549
550 pn_variable_amount_pkg.put_output('Actual Amount :'|| round(rec_act_inv.actual_invoiced_amount,2));
551 l_context :='Creating Actual Payment term';
552
553 savepoint create_terms;
554
555 IF NVL(rec_act_inv.credit_flag,'N') = 'N' THEN
556
560 ,p_invoice_date => rec_act_inv.invoice_date
557 create_payment_terms(p_lease_id => l_lease_id
558 ,p_period_id => rec_act_inv.period_id
559 ,p_payment_amount => rec_act_inv.actual_invoiced_amount
561 ,p_var_rent_id => rec_act_inv.var_rent_id
562 ,p_var_rent_inv_id => rec_act_inv.var_rent_inv_id
563 ,p_location_id => l_location_id
564 ,p_var_rent_type => 'ACTUAL'
565 ,p_org_id => l_org_id );
566
567 -- Check if term exists and set actual_term_status accordingly.
568
569 term_count := 0;
570
571 SELECT count(*) INTO term_count
572 FROM pn_payment_terms_all
573 WHERE var_rent_inv_id = rec_act_inv.var_rent_inv_id
574 AND var_rent_type = 'ACTUAL';
575
576 IF term_count > 0 THEN
577 UPDATE pn_var_rent_inv_all
578 SET actual_term_status='Y',
579 last_update_date = SYSDATE,
580 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
581 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
582 WHERE var_rent_inv_id = rec_act_inv.var_rent_inv_id;
583
584 UPDATE pn_var_rent_inv_all
585 SET true_up_status = 'Y'
586 WHERE var_rent_inv_id = rec_act_inv.var_rent_inv_id
587 AND true_up_status IS NOT NULL;
588
589 ELSE
590 pn_variable_amount_pkg.put_log('term not found ...');
591 UPDATE pn_var_rent_inv_all
592 SET actual_term_status='N',
593 last_update_date = SYSDATE,
594 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
595 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
596 WHERE var_rent_inv_id = rec_act_inv.var_rent_inv_id;
597
598 UPDATE pn_var_rent_inv_all
599 SET true_up_status = 'N'
600 WHERE var_rent_inv_id = rec_act_inv.var_rent_inv_id
601 AND true_up_status IS NOT NULL;
602 END IF;
603 ELSE
604
605 FOR payment_rec IN payment_cur(rec_act_inv.invoice_date,rec_act_inv.var_rent_id) LOOP
606 pn_variable_term_pkg.create_reversal_terms(p_payment_term_id => payment_rec.payment_term_id
607 ,p_var_rent_inv_id => rec_act_inv.var_rent_inv_id
608 ,p_var_rent_type => 'ADJUSTMENT');
609 UPDATE pn_var_rent_inv_all
610 SET actual_term_status='Y',
611 last_update_date = SYSDATE,
612 last_update_login = NVL(fnd_profile.value('LOGIN_ID'),0),
613 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
614 WHERE var_rent_inv_id = rec_act_inv.var_rent_inv_id
615 AND actual_term_status='N' ;
616
617 END LOOP;
618
619 END IF; --NVL(rec_act_inv.credit_flag,'N') = 'N'
620 END IF;
621 END IF;
622 END LOOP;
623 END IF; -- If l_invoice_on is ACTUAL
624 END IF; -- If l_err_flag = 'N'
625 END LOOP;
626
627 pn_variable_amount_pkg.put_log('pn_variable_term_pkg.create_payment_term_batch (-) : ');
628
629 EXCEPTION
630
631 When OTHERS Then
632 pn_variable_amount_pkg.put_log(substrb('Error in create_payment_term_batch - ' || to_char(sqlcode)
633 ||' : ' ||sqlerrm|| ' - '|| l_context,1,244));
634 Errbuf := SQLERRM;
635 Retcode := 2;
636 rollback;
637 raise;
638
639 END create_payment_term_batch;
640
641
642 -------------------------------------------------------------------------------
643 -- PROCEDURE : create_payment_terms
644 -- Procedure for creation of variable rent payment terms.
645 --
646 -- 31-Jan-02 o Fix for bug# 2208196. Pass value for normalized flag
647 -- as 'Y'to procedure pnt_payment_terms_pkg.insert_row.
648 -- 22-Feb-02 o Added parameter x_calling_form in the call to
649 -- pnt_payment_terms_cpg.
650 -- 28-Jun-02 o Added parameter p_org_id for shared serv.
651 -- Enhancement.
652 -- 18-SEP-02 ftanudja o changed call from fnd_profile.value('PN_SET_OF..')
653 -- to pn_mo_cache_utils.get_profile_value('PN_SET_OF..')
654 -- 14-JUN-04 abanerje o Modified call to pnt_payment_terms_pkg.insert_row
655 -- to populate the term_template_id also. Bug#3657130.
656 -- 15-SEP-04 atuppad o In the call pnt_payment_terms_pkg.insert_row,
657 -- corrected the code to copy the payment DFF into
658 -- payment DFF of new VR term and not in AR Projects DFF
659 -- Bug # 3841542
660 -- 21-APR-05 ftanudja o Added area_type_code, area defaulting. #4324777
661 -- 15-JUL-05 ftanudja o R12 changes: add logic for tax_clsfctn_code. #4495054
662 -- 21-JUN-05 hareesha o Bug 4284035 - Replaced pn_var_rents, pn_distributions,
666 -- 15-MAR-07 pikhar o Bug 5930387. Added include_in_var_rent
663 -- pn_term_templates, pn_leases with _ALL table.
664 -- 23-NOV-05 pikhar o Passed org_id in pn_mo_cache_utils.get_profile_value
665 -- 13-DEC-05 rdonthul o Changed the l_payment_term_date for bug 5700403
667 -------------------------------------------------------------------------------
668
669 PROCEDURE create_payment_terms(
670 p_lease_id IN NUMBER
671 ,p_period_id IN NUMBER
672 ,p_payment_amount IN NUMBER
673 ,p_invoice_date IN DATE
674 ,p_var_rent_id IN NUMBER
675 ,p_var_rent_inv_id IN NUMBER
676 ,p_location_id IN NUMBER
677 ,p_var_rent_type IN VARCHAR2
678 ,p_org_id IN NUMBER
679 ) IS
680
681 l_lease_class_code pn_leases.lease_class_code%TYPE;
682 l_distribution_id pn_distributions.distribution_id%TYPE;
683 l_payment_term_id pn_payment_terms.payment_term_id%TYPE;
684 l_lease_change_id pn_lease_details.lease_change_id%TYPE;
685 l_rowid ROWID;
686 l_distribution_count NUMBER := 0;
687 l_inv_start_date DATE;
688 l_payment_start_date DATE;
689 l_payment_end_date DATE;
690 l_frequency pn_payment_terms.frequency_code%type;
691 l_schedule_day pn_payment_terms.schedule_day%type;
692 l_set_of_books_id gl_sets_of_books.set_of_books_id%type;
693 l_context varchar2(2000);
694 l_area pn_payment_terms.area%TYPE;
695 l_area_type_code pn_payment_terms.area_type_code%TYPE;
696 l_org_id NUMBER;
697 l_schedule_day_char VARCHAR2(8);
698 l_payment_status_lookup_code pn_payment_schedules_all.payment_status_lookup_code%type;
699 i_cnt number;
700
701 CURSOR csr_distributions (p_var_rent_id IN NUMBER)
702 IS
703 SELECT *
704 FROM pn_distributions_all
705 WHERE term_template_id = (SELECT term_template_id
706 FROM pn_var_rents_all
707 WHERE var_rent_id = p_var_rent_id);
708
709 CURSOR csr_template (p_var_rent_id IN NUMBER)
710 IS
711 SELECT *
712 FROM pn_term_templates_all
713 WHERE term_template_id = (SELECT term_template_id
714 FROM pn_var_rents_all
715 WHERE var_rent_id = p_var_rent_id);
716
717 CURSOR currency_code_cur IS
718 SELECT currency_code
719 FROM pn_var_rents_all
720 WHERE var_rent_id = p_var_rent_id;
721
722 rec_template pn_term_templates_all%ROWTYPE;
723 term_count NUMBER := 0;
724 l_currency_code pn_var_rents_all.currency_code%TYPE;
725 --l_global_rec pn_mo_cache_utils.GlobalsRecord;
726
727 -- Get the details of
728 /*CURSOR invoice_date_c IS
729 SELECT DISTINCT inv_start_date
730 FROM pn_var_grp_dates_all
731 WHERE var_rent_id = p_var_rent_id
732 AND invoice_date = p_invoice_date;*/
733
734
735 BEGIN
736
737 pn_variable_amount_pkg.put_log ('pn_variable_term_pkg.create_payment_terms : (+)');
738
739 l_context := 'Getting lease class code and lease change id';
740
741 BEGIN
742 SELECT pl.lease_class_code,
743 pld.lease_change_id,
744 pl.org_id
745 INTO l_lease_class_code,
746 l_lease_change_id,
747 l_org_id
748 FROM pn_leases_all pl,
749 pn_lease_details_all pld
750 WHERE pl.lease_id = pld.lease_id
751 AND pld.lease_id = p_lease_id;
752
753 EXCEPTION
754 WHEN TOO_MANY_ROWS THEN
755 pn_variable_amount_pkg.put_log ('Cannot Get Main Lease Details - TOO_MANY_ROWS');
756 WHEN NO_DATA_FOUND THEN
757 pn_variable_amount_pkg.put_log ('Cannot Get Main Lease Details - NO_DATA_FOUND');
758 WHEN OTHERS THEN
759 pn_variable_amount_pkg.put_log ('Cannot Get Main Lease Details - Unknown Error:'|| SQLERRM);
760 END;
761
762
763 --pn_variable_amount_pkg.put_log ('create_payment_terms - multi_org_flag :'||
764 --mo_utils.get_multi_org_flag);
765 pn_variable_amount_pkg.put_log ('create_payment_terms - Org id :'||p_org_id);
766
767
768 l_context := 'Getting set of books id';
769
770 --IF mo_utils.get_multi_org_flag = 'Y' THEN
771 --l_global_rec := pn_mo_global_cache.get_org_attributes(p_org_id);
772 --ELSE
773 --l_global_rec := pn_mo_global_cache.get_org_attributes(-3115);
774 --END IF;
775
776
777 --l_set_of_books_id := l_global_rec.set_of_books_id;
778 l_set_of_books_id := to_number(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID'
779 ,l_org_id));
780
781
782 pn_variable_amount_pkg.put_log ('create_payment_terms - Currency Code :'||g_currency_code);
783 pn_variable_amount_pkg.put_log ('create_payment_terms - Set of books id :'||l_set_of_books_id);
784
785
786 l_context := 'opening cursor csr_template';
787
788 OPEN csr_template(p_var_rent_id);
789 FETCH csr_template INTO rec_template;
790 CLOSE csr_template;
791
792
793 IF l_lease_class_code = 'DIRECT' THEN
794
795 /* lease is of class: DIRECT */
796
797 rec_template.customer_id := NULL;
798 rec_template.customer_site_use_id := NULL;
802 rec_template.account_rule_id := NULL;
799 rec_template.cust_ship_site_id := NULL;
800 rec_template.cust_trx_type_id := NULL;
801 rec_template.inv_rule_id := NULL;
803 rec_template.salesrep_id := NULL;
804 rec_template.cust_po_number := NULL;
805 rec_template.receipt_method_id := NULL;
806 ELSE
807
808 /* lease is 'sub-lease' or third-party */
809
810 rec_template.project_id := NULL;
811 rec_template.task_id := NULL;
812 rec_template.organization_id := NULL;
813 rec_template.expenditure_type := NULL;
814 rec_template.expenditure_item_date := NULL;
815 rec_template.vendor_id := NULL;
816 rec_template.vendor_site_id := NULL;
817 rec_template.tax_group_id := NULL;
818 rec_template.distribution_set_id := NULL;
819 rec_template.po_header_id := NULL;
820 END IF;
821
822 IF pn_r12_util_pkg.is_r12 THEN
823 rec_template.tax_group_id := null;
824 rec_template.tax_code_id := null;
825 ELSE
826 rec_template.tax_classification_code := null;
827 END IF;
828
829 /* Derive the payment start date */
830
831 l_context := 'Getting payment term start date';
832
833 BEGIN
834
835 /*SELECT distinct inv_schedule_date
836 INTO l_payment_start_date
837 FROM pn_var_grp_dates_all
838 WHERE period_id = p_period_id
839 AND invoice_date = p_invoice_date;*/
840 --
841 -- FOR rec IN invoice_date_c LOOP
842 /*l_inv_start_date := pn_var_rent_calc_pkg.inv_start_date(inv_start_date => p_invoice_date
843 ,vr_id => p_var_rent_id
844 ,approved_status => 'N'); */
845
846 l_payment_start_date := pn_var_rent_calc_pkg.inv_sch_date(inv_start_date => p_invoice_date
847 ,vr_id => p_var_rent_id
848 ,p_period_id => p_period_id);
849 -- END LOOP;
850
851 EXCEPTION
852 WHEN TOO_MANY_ROWS THEN
853 pn_variable_amount_pkg.put_log('Cannot Get Payment term start date- TOO_MANY_ROWS');
854 WHEN NO_DATA_FOUND THEN
855 pn_variable_amount_pkg.put_log('Cannot Get Payment term start date- NO_DATA_FOUND');
856 WHEN OTHERS THEN
857 pn_variable_amount_pkg.put_log('Cannot Get Payment term start date- Unknown Error:'|| SQLERRM);
858 END;
859
860
861
862 /* Derive the payment end date,schedule_day and frequency*/
863
864 l_context := 'Setting payment end date,frequency and schedule day';
865
866 l_payment_end_date := l_payment_start_date;
867 l_frequency := 'OT';
868 l_schedule_day := to_char(l_payment_start_date,'dd');
869
870 IF p_location_id IS NOT NULL AND
871 l_payment_start_date IS NOT NULL THEN
872
873 l_area_type_code := pn_mo_cache_utils.get_profile_value('PN_AREA_TYPE',l_org_id);
874 l_area := pnp_util_func.fetch_tenancy_area(
875 p_lease_id => p_lease_id,
876 p_location_id => p_location_id,
877 p_as_of_date => l_payment_start_date,
878 p_area_type_code => l_area_type_code);
879
880 END IF;
881
882 l_context := 'Inserting into pn_payment_terms';
883
884 FOR rec IN currency_code_cur LOOP
885 l_currency_code := rec.currency_code;
886 END LOOP;
887
888 pnt_payment_terms_pkg.insert_row (
889 x_rowid => l_rowid
890 ,x_payment_term_id => l_payment_term_id
891 ,x_index_period_id => null
892 ,x_index_term_indicator => null
893 ,x_var_rent_inv_id => p_var_rent_inv_id
894 ,x_var_rent_type => p_var_rent_type
895 ,x_last_update_date => SYSDATE
896 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
897 ,x_creation_date => SYSDATE
898 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
899 ,x_payment_purpose_code => rec_template.payment_purpose_code
900 ,x_payment_term_type_code => rec_template.payment_term_type_code
901 ,x_frequency_code => l_frequency
902 ,x_lease_id => p_lease_id
903 ,x_lease_change_id => l_lease_change_id
904 ,x_start_date => l_payment_start_date
905 ,x_end_date => l_payment_end_date
906 ,x_set_of_books_id => NVL(rec_template.set_of_books_id,l_set_of_books_id)
907 --,x_currency_code => NVL(rec_template.currency_code, l_currency_code)
908 ,x_currency_code => NVl(g_currency_code, l_currency_code)
909 ,x_rate => 1 -- not used in application
910 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
911 ,x_vendor_id => rec_template.vendor_id
912 ,x_vendor_site_id => rec_template.vendor_site_id
913 ,x_target_date => NULL
914 ,x_actual_amount => p_payment_amount
915 ,x_estimated_amount => NULL
916 ,x_attribute_category => rec_template.attribute_category
920 ,x_attribute4 => rec_template.attribute4
917 ,x_attribute1 => rec_template.attribute1
918 ,x_attribute2 => rec_template.attribute2
919 ,x_attribute3 => rec_template.attribute3
921 ,x_attribute5 => rec_template.attribute5
922 ,x_attribute6 => rec_template.attribute6
923 ,x_attribute7 => rec_template.attribute7
924 ,x_attribute8 => rec_template.attribute8
925 ,x_attribute9 => rec_template.attribute9
926 ,x_attribute10 => rec_template.attribute10
927 ,x_attribute11 => rec_template.attribute11
928 ,x_attribute12 => rec_template.attribute12
929 ,x_attribute13 => rec_template.attribute13
930 ,x_attribute14 => rec_template.attribute14
931 ,x_attribute15 => rec_template.attribute15
932 ,x_project_attribute_category => NULL
933 ,x_project_attribute1 => NULL
934 ,x_project_attribute2 => NULL
935 ,x_project_attribute3 => NULL
936 ,x_project_attribute4 => NULL
937 ,x_project_attribute5 => NULL
938 ,x_project_attribute6 => NULL
939 ,x_project_attribute7 => NULL
940 ,x_project_attribute8 => NULL
941 ,x_project_attribute9 => NULL
942 ,x_project_attribute10 => NULL
943 ,x_project_attribute11 => NULL
944 ,x_project_attribute12 => NULL
945 ,x_project_attribute13 => NULL
946 ,x_project_attribute14 => NULL
947 ,x_project_attribute15 => NULL
948 ,x_customer_id => rec_template.customer_id
949 ,x_customer_site_use_id => rec_template.customer_site_use_id
950 ,x_normalize => 'N'
951 ,x_location_id => p_location_id
952 ,x_schedule_day => l_schedule_day
953 ,x_cust_ship_site_id => rec_template.cust_ship_site_id
954 ,x_ap_ar_term_id => rec_template.ap_ar_term_id
955 ,x_cust_trx_type_id => rec_template.cust_trx_type_id
956 ,x_project_id => rec_template.project_id
957 ,x_task_id => rec_template.task_id
958 ,x_organization_id => rec_template.organization_id
959 ,x_expenditure_type => rec_template.expenditure_type
960 ,x_expenditure_item_date => rec_template.expenditure_item_date
961 ,x_tax_group_id => rec_template.tax_group_id
962 ,x_tax_code_id => rec_template.tax_code_id
963 ,x_tax_classification_code => rec_template.tax_classification_code
964 ,x_tax_included => rec_template.tax_included
965 ,x_distribution_set_id => rec_template.distribution_set_id
966 ,x_inv_rule_id => rec_template.inv_rule_id
967 ,x_account_rule_id => rec_template.account_rule_id
968 ,x_salesrep_id => rec_template.salesrep_id
969 ,x_approved_by => NULL
970 ,x_status => 'DRAFT'
971 ,x_po_header_id => rec_template.po_header_id
972 ,x_cust_po_number => rec_template.cust_po_number
973 ,x_receipt_method_id => rec_template.receipt_method_id
974 ,x_calling_form => 'PNXVAREN'
975 ,x_org_id => l_org_id
976 ,x_term_template_id => rec_template.term_template_id
977 ,x_area => l_area
978 ,x_area_type_code => l_area_type_code
979 ,x_include_in_var_rent => NULL
980 );
981
982
983
984 /* Create a record in pn_distributions */
985
986 l_distribution_count := 0;
987
988 l_context :='opening cursor csr_distributions';
989
990 FOR rec_distributions in csr_distributions(p_var_rent_id)
991
992 LOOP
993 pn_variable_amount_pkg.put_log(' account_id '||rec_distributions.account_id);
994 pn_variable_amount_pkg.put_log(' account_class '||rec_distributions.account_id);
995
996
997 l_context := 'Inserting into pn_distributions';
998 pn_distributions_pkg.insert_row (
999 x_rowid => l_rowid
1000 ,x_distribution_id => l_distribution_id
1001 ,x_account_id => rec_distributions.account_id
1002 ,x_payment_term_id => l_payment_term_id
1003 ,x_term_template_id => NULL
1004 ,x_account_class => rec_distributions.account_class
1005 ,x_percentage => rec_distributions.percentage
1006 ,x_line_number => rec_distributions.line_number
1007 ,x_last_update_date => SYSDATE
1008 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
1009 ,x_creation_date => SYSDATE
1010 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
1011 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
1015 ,x_attribute3 => rec_distributions.attribute3
1012 ,x_attribute_category => rec_distributions.attribute_category
1013 ,x_attribute1 => rec_distributions.attribute1
1014 ,x_attribute2 => rec_distributions.attribute2
1016 ,x_attribute4 => rec_distributions.attribute4
1017 ,x_attribute5 => rec_distributions.attribute5
1018 ,x_attribute6 => rec_distributions.attribute6
1019 ,x_attribute7 => rec_distributions.attribute7
1020 ,x_attribute8 => rec_distributions.attribute8
1021 ,x_attribute9 => rec_distributions.attribute9
1022 ,x_attribute10 => rec_distributions.attribute10
1023 ,x_attribute11 => rec_distributions.attribute11
1024 ,x_attribute12 => rec_distributions.attribute12
1025 ,x_attribute13 => rec_distributions.attribute13
1026 ,x_attribute14 => rec_distributions.attribute14
1027 ,x_attribute15 => rec_distributions.attribute15
1028 ,x_org_id => l_org_id
1029 );
1030
1031 l_rowid := NULL;
1032 l_distribution_id := NULL;
1033 l_distribution_count := l_distribution_count + 1;
1034
1035 END LOOP;
1036
1037 l_context := 'exiting from loop';
1038
1039 -- Check if term exists and set actual_term_status accordingly.
1040
1041 IF p_var_rent_type <> 'ADJUSTMENT' THEN
1042 SELECT count(*)
1043 INTO term_count
1044 FROM pn_payment_terms_all
1045 WHERE var_rent_inv_id = p_var_rent_inv_id
1046 AND var_rent_type = 'ACTUAL';
1047
1048 IF term_count > 0 THEN
1049 pnp_debug_pkg.debug('setting actual term status ...');
1050 UPDATE pn_var_rent_inv_all
1051 SET actual_term_status='Y'
1052 WHERE var_rent_inv_id = p_var_rent_inv_id;
1053
1054 UPDATE pn_var_rent_inv_all
1055 SET true_up_status = 'Y'
1056 WHERE var_rent_inv_id = p_var_rent_inv_id
1057 AND true_up_status IS NOT NULL;
1058
1059 ELSE
1060 UPDATE pn_var_rent_inv_all
1061 SET actual_term_status='N'
1062 WHERE var_rent_inv_id = p_var_rent_inv_id;
1063
1064 UPDATE pn_var_rent_inv_all
1065 SET true_up_status = 'N'
1066 WHERE var_rent_inv_id = p_var_rent_inv_id
1067 AND true_up_status IS NOT NULL;
1068
1069 END IF;
1070 END IF;
1071
1072 pn_variable_amount_pkg.put_log('pn_variable_term_pkg.create_payment_terms (-) ');
1073
1074 EXCEPTION
1075
1076 when others then
1077 pn_variable_amount_pkg.put_log(substrb('pn_variable_term_pkg.Error in create_payment_terms - ' ||
1078 to_char(sqlcode)||' : '||sqlerrm || ' - '|| l_context,1,244));
1079 rollback to create_terms;
1080
1081 -- Check if term exists and set actual_term_status accordingly.
1082
1083 IF p_var_rent_type <> 'ADJUSTMENT' THEN
1084 SELECT count(*)
1085 INTO term_count
1086 FROM pn_payment_terms_all
1087 WHERE var_rent_inv_id = p_var_rent_inv_id
1088 AND var_rent_type = 'ACTUAL';
1089
1090 IF term_count > 0 THEN
1091 pnp_debug_pkg.debug('setting actual term status ...');
1092 UPDATE pn_var_rent_inv_all
1093 SET actual_term_status='Y'
1094 WHERE var_rent_inv_id = p_var_rent_inv_id;
1095 ELSE
1096 UPDATE pn_var_rent_inv_all
1097 SET actual_term_status='N'
1098 WHERE var_rent_inv_id = p_var_rent_inv_id;
1099 END IF;
1100 END IF;
1101
1102 END create_payment_terms;
1103
1104
1105 PROCEDURE get_schedule_status ( p_lease_id IN NUMBER,
1106 p_schedule_date IN DATE,
1107 x_payment_status_lookup_code OUT NOCOPY VARCHAR2) IS
1108
1109 CURSOR get_schedule_cur IS
1110 SELECT payment_status_lookup_code
1111 FROM pn_payment_schedules_all
1112 WHERE lease_id = p_lease_id
1113 AND schedule_date = p_schedule_date;
1114
1115 BEGIN
1116
1117
1118 FOR get_schedule_rec in get_schedule_cur LOOP
1119 x_payment_status_lookup_code := get_schedule_rec.payment_status_lookup_code;
1120 END LOOP;
1121
1122 IF x_payment_status_lookup_code is NULL THEN
1123 x_payment_status_lookup_code := 'DRAFT';
1124 END IF;
1125
1126 END;
1127 -------------------------------------------------------------------------------
1128 -- NAME : FIND_VOLUME_CONTINUOUS()
1129 -- PURPOSE : Checks that no gaps exist in volumes for a invoice period
1130 -- DESCRIPTION : Checks taht volumes exist for each and every day of the
1131 -- invoice period
1132 -- SCOPE : PUBLIC
1133 --
1134 -- ARGUMENTS : p_var_rent_id : variable rent ID (mandatory)
1135 -- p_period_id : Id of a particular period (optional)
1139 -- HISTORY :
1136 -- p_invoice_date: Invoice date
1137 --
1138 -- RETURNS :
1140 --
1141 -- 03-APR-07 Lbala o Created.
1142 --
1143 -------------------------------------------------------------------------------
1144 FUNCTION find_volume_continuous (p_var_rent_id IN NUMBER,
1145 p_period_id IN NUMBER,
1146 p_invoice_date IN DATE
1147 ) RETURN VARCHAR2
1148 IS
1149 TYPE vol_hist_rec IS RECORD(
1150 start_date pn_var_vol_hist_all.start_date%TYPE,
1151 end_date pn_var_vol_hist_all.end_date%TYPE,
1152 line_item_id pn_var_lines_all.line_item_id%TYPE
1153 );
1154
1155 TYPE vol_hist_type IS
1156 TABLE OF vol_hist_rec
1157 INDEX BY BINARY_INTEGER;
1158
1159 vol_hist_tab vol_hist_type;
1160
1161 --Get all line items for a period
1162 CURSOR line_items_c(p_prd_id IN NUMBER) IS
1163 SELECT line_item_id
1164 FROM pn_var_lines_all
1165 WHERE period_id = p_prd_id
1166 ORDER BY line_item_id;
1167
1168 --Get all volume history records for a inv_dt,period_id,line_item_id combination
1169 CURSOR vol_hist_dates(p_prd_id IN NUMBER, p_inv_dt IN DATE, p_line_id IN NUMBER)
1170 IS
1171 SELECT start_date, end_date, line_item_id
1172 FROM pn_var_vol_hist_all vol,
1173 (SELECT gd.period_id,
1174 gd.grp_date_id
1175 FROM pn_var_grp_dates_all gd
1176 WHERE gd.period_id= p_prd_id
1177 AND gd.invoice_date = p_inv_dt OR p_inv_dt IS NULL
1178 )itemp
1179 WHERE vol.grp_date_id = itemp.grp_date_id
1180 AND vol.line_item_id = p_line_id
1181 AND vol.period_id = itemp.period_id
1182 AND vol_hist_status_code = 'APPROVED'
1183 AND actual_amount IS NOT NULL
1184 ORDER BY start_date,end_date;
1185
1186 --Get all volume history records for a inv_dt,period_id,line_item_id combination
1187 --for firstyr
1188 CURSOR vol_hist_dates_fy(p_prd_id IN NUMBER, p_inv_dt IN DATE, p_line_id IN NUMBER,p_end_dt IN DATE)
1189 IS
1190 SELECT start_date, end_date, line_item_id
1191 FROM pn_var_vol_hist_all vol,
1192 (SELECT gd.period_id,
1193 gd.grp_date_id
1194 FROM pn_var_grp_dates_all gd
1195 WHERE gd.period_id= p_prd_id
1196 AND gd.invoice_date <= p_inv_dt
1197 )itemp
1198 WHERE vol.grp_date_id = itemp.grp_date_id
1199 AND vol.line_item_id = p_line_id
1200 AND vol.period_id = itemp.period_id
1201 AND vol_hist_status_code = 'APPROVED'
1202 AND actual_amount IS NOT NULL
1203 AND start_date <= p_end_dt
1204 ORDER BY start_date,end_date;
1205
1206 --Get all volume history records for a inv_dt,period_id,line_item_id combination
1207 --for lastyr
1208 CURSOR vol_hist_dates_ly(p_prd_id IN NUMBER, p_inv_dt IN DATE, p_line_id IN NUMBER,p_st_dt IN DATE)
1209 IS
1210 SELECT start_date, end_date, line_item_id
1211 FROM pn_var_vol_hist_all vol,
1212 (SELECT gd.period_id,
1213 gd.grp_date_id
1214 FROM pn_var_grp_dates_all gd
1215 WHERE gd.period_id= p_prd_id
1216 AND gd.invoice_date >= p_inv_dt
1217 )itemp
1218 WHERE vol.grp_date_id = itemp.grp_date_id
1219 AND vol.line_item_id = p_line_id
1220 AND vol.period_id = itemp.period_id
1221 AND vol_hist_status_code = 'APPROVED'
1222 AND actual_amount IS NOT NULL
1223 AND end_date >= p_st_dt
1224 ORDER BY start_date,end_date;
1225
1226
1227 -- Get the VR details
1228 CURSOR vrent_cur(p_vr_id IN NUMBER) IS
1229 SELECT proration_rule, commencement_date, termination_date
1230 FROM pn_var_rents_all
1231 WHERE var_rent_id = p_vr_id;
1232
1233 -- Get partial period information
1234 CURSOR partial_prd(p_vr_id IN NUMBER) IS
1235 SELECT period_id, period_num, start_date, end_date
1236 FROM pn_var_periods_all
1237 WHERE partial_period='Y'
1238 AND var_rent_id = p_vr_id;
1239
1240 -- Get invoice period dates
1241 CURSOR inv_prd_cur(p_prd_id IN NUMBER,p_inv_dt IN DATE) IS
1242 SELECT inv_start_date ,inv_end_date
1243 FROM pn_var_grp_dates_all
1244 WHERE period_id = p_prd_id
1245 AND invoice_date = p_inv_dt;
1246
1247 l_prorul VARCHAR2(5):=NULL;
1248 l_line_id NUMBER:= NULL;
1249 l_invoice_date DATE := NULL;
1250 l_prev_end_dt DATE ;
1251 l_comm_dt DATE := NULL;
1252 l_term_dt DATE := NULL;
1253 l_st_dt DATE := NULL;
1254 l_end_dt DATE := NULL;
1255 inv_st_dt DATE := NULL;
1256 inv_end_dt DATE := NULL;
1257 min_st_dt DATE := to_date('01/01/2247','dd/mm/rrrr');
1258 max_end_dt DATE := NULL;
1259 l_fy_flag NUMBER:=0;
1260 l_ly_flag NUMBER:=0;
1261 l_next_prd_id NUMBER:= NULL;
1262 l_prev_prd_id NUMBER:= NULL;
1263 l_prev_line NUMBER:= NULL;
1264 l_next_line NUMBER:= NULL;
1265 l_prev_inv_dt DATE;
1266 k NUMBER:= NULL;
1267
1268 BEGIN
1269 pnp_debug_pkg.log('pn_variable_term_pkg.find_volume_continuous (+) : ');
1270
1271 l_invoice_date := p_invoice_date;
1272
1273 FOR inv_prd_rec IN inv_prd_cur(p_period_id, p_invoice_date) LOOP
1274 inv_st_dt := inv_prd_rec.inv_start_date;
1275 inv_end_dt := inv_prd_rec.inv_end_date;
1276 END LOOP;
1277
1278 l_st_dt := pn_var_rent_calc_pkg.inv_start_date(inv_start_date => l_invoice_date
1279 ,vr_id => p_var_rent_id
1280 ,p_period_id => p_period_id
1281 );
1285 ,p_period_id => p_period_id
1282
1283 l_end_dt := pn_var_rent_calc_pkg.inv_end_date(inv_start_date => l_invoice_date
1284 ,vr_id => p_var_rent_id
1286 );
1287
1288 FOR var_rent_rec IN vrent_cur(p_var_rent_id) LOOP
1289 l_prorul := var_rent_rec.proration_rule;
1290 l_comm_dt := var_rent_rec.commencement_date;
1291 l_term_dt := var_rent_rec.termination_date;
1292 END LOOP;
1293
1294 IF l_prorul IN ('FY','FLY') THEN
1295
1296 FOR prd_rec IN partial_prd(p_var_rent_id) LOOP
1297
1298 IF prd_rec.period_id = p_period_id AND prd_rec.period_num=1
1299 THEN l_st_dt := prd_rec.start_date;
1300 l_end_dt := ADD_MONTHS(prd_rec.start_date,12)-1;
1301
1302 IF (l_end_dt > l_term_dt AND l_st_dt <= l_term_dt ) THEN
1303 l_end_dt := l_term_dt;
1304 END IF;
1305
1306 l_next_prd_id := get_period(p_var_rent_id, l_end_dt);
1307 l_fy_flag:=1;
1308
1309 END IF;
1310
1311 END LOOP;
1312 END IF;
1313
1314 IF l_prorul IN ('LY','FLY') THEN
1315
1316 FOR prd_rec IN partial_prd(p_var_rent_id) LOOP
1317
1318 IF prd_rec.period_id = p_period_id AND prd_rec.end_date=l_term_dt
1319 THEN l_st_dt := ADD_MONTHS(prd_rec.end_date,-12)+1;
1320 l_end_dt := prd_rec.end_date;
1321
1322 IF (l_comm_dt > l_st_dt) THEN
1323 l_st_dt := l_comm_dt;
1324 END IF;
1325
1326 l_prev_prd_id := get_period(p_var_rent_id, l_st_dt);
1327 l_ly_flag:=1;
1328
1329 END IF;
1330 END LOOP;
1331
1332 END IF;
1333
1334 IF (l_fy_flag = 0 AND l_ly_flag = 0) THEN /* Normal invoice */
1335
1336 FOR line_rec IN line_items_c(p_period_id) LOOP
1337
1338 l_line_id := line_rec.line_item_id;
1339 vol_hist_tab.DELETE;
1340 l_prev_end_dt := NULL;
1341 min_st_dt := to_date('01/01/2247','dd/mm/rrrr');
1342 max_end_dt := to_date('01/01/1976','dd/mm/rrrr');
1343
1344
1345 OPEN vol_hist_dates(p_period_id,l_invoice_date,l_line_id);
1346 FETCH vol_hist_dates BULK COLLECT INTO vol_hist_tab;
1347 CLOSE vol_hist_dates;
1348
1349 IF(vol_hist_tab.COUNT > 0) THEN
1350 min_st_dt := vol_hist_tab(1).start_date;
1351 max_end_dt := vol_hist_tab(1).end_date;
1352 END IF;
1353
1354 FOR i IN 2..vol_hist_tab.COUNT LOOP
1355
1356 IF vol_hist_tab(i).start_date BETWEEN min_st_dt AND max_end_dt + 1 THEN
1357
1358 IF vol_hist_tab(i).end_date > max_end_dt THEN
1359 max_end_dt := vol_hist_tab(i).end_date;
1360 END IF;
1361
1362 ELSE
1363 RETURN 'N';
1364 END IF;
1365
1366 END LOOP;
1367
1368 IF ( min_st_dt > l_st_dt OR
1369 max_end_dt < l_end_dt ) THEN
1370
1371 RETURN 'N';
1372 END IF;
1373
1374
1375 END LOOP;
1376 RETURN 'Y';
1377
1378 -- For first partial period invoice
1379 -- We need to get all volume records for the 365/366 day period say 1-JUL-05 to 30-JUN-06
1380 -- So we break it up into 2 parts -- 1st volumes for partial period i.e 1-JUL-05 to 31-DEC-05
1381 -- and 2nd volumes for 1-JAN-06 to 30-JUN-06
1382 -- We then check that volumes exist for this entire period
1383
1384 ELSIF l_fy_flag =1 THEN /*FY invoice */
1385
1386 FOR line_rec IN line_items_c(p_period_id) LOOP
1387
1388 l_line_id := line_rec.line_item_id;
1389 l_next_line := get_line(l_next_prd_id,l_line_id);
1390 l_prev_end_dt := NULL;
1391 min_st_dt := to_date('01/01/2247','dd/mm/rrrr');
1392 max_end_dt := to_date('01/01/1976','dd/mm/rrrr');
1393 vol_hist_tab.DELETE;
1394
1395 OPEN vol_hist_dates(p_period_id,NULL,l_line_id);
1396 FETCH vol_hist_dates BULK COLLECT INTO vol_hist_tab;
1397 CLOSE vol_hist_dates;
1398
1399 k := vol_hist_tab.COUNT + 1;
1400
1401 FOR rec IN vol_hist_dates_fy(l_next_prd_id ,l_invoice_date ,l_next_line,l_end_dt ) LOOP
1402 vol_hist_tab(k).start_date := rec.start_date;
1403 vol_hist_tab(k).end_date := rec.end_date;
1404 vol_hist_tab(k).line_item_id := rec.line_item_id;
1405 k := k+1;
1406 END LOOP;
1407
1408 IF(vol_hist_tab.COUNT > 0) THEN
1409 min_st_dt := vol_hist_tab(1).start_date;
1410 max_end_dt := vol_hist_tab(1).end_date;
1411 END IF;
1412
1413 FOR i IN 2..vol_hist_tab.COUNT LOOP
1414
1415 IF vol_hist_tab(i).start_date BETWEEN min_st_dt AND max_end_dt + 1 THEN
1416
1417 IF vol_hist_tab(i).end_date > max_end_dt THEN
1418 max_end_dt := vol_hist_tab(i).end_date;
1419 END IF;
1420
1421 ELSE
1422 RETURN 'N';
1423 END IF;
1424
1425 END LOOP;
1426
1427 IF ( min_st_dt > l_st_dt OR
1428 max_end_dt < l_end_dt ) THEN
1429
1430 RETURN 'N';
1431 END IF;
1432
1433 END LOOP;
1437 ELSIF l_ly_flag = 1 THEN /* LY invoice */
1434 RETURN 'Y';
1435
1436 -- For last partial period invoice
1438
1439 FOR line_rec IN line_items_c(p_period_id) LOOP
1440
1441 l_line_id := line_rec.line_item_id;
1442 l_prev_line := get_line(l_prev_prd_id,l_line_id);
1443 l_prev_inv_dt := get_inv_date(l_prev_prd_id, l_st_dt);
1444 min_st_dt := to_date('01/01/2247','dd/mm/rrrr');
1445 max_end_dt := to_date('01/01/1976','dd/mm/rrrr');
1446 l_prev_end_dt := NULL;
1447 vol_hist_tab.DELETE;
1448
1449
1450 OPEN vol_hist_dates_ly(l_prev_prd_id,l_prev_inv_dt,l_prev_line,l_st_dt);
1451 FETCH vol_hist_dates_ly BULK COLLECT INTO vol_hist_tab;
1452 CLOSE vol_hist_dates_ly;
1453
1454 k := vol_hist_tab.COUNT + 1;
1455
1456 FOR rec IN vol_hist_dates(p_period_id,NULL,l_line_id) LOOP
1457 vol_hist_tab(k).start_date := rec.start_date;
1458 vol_hist_tab(k).end_date := rec.end_date;
1459 vol_hist_tab(k).line_item_id := rec.line_item_id;
1460 k := k+1;
1461 END LOOP;
1462
1463 IF(vol_hist_tab.COUNT > 0) THEN
1464 min_st_dt := vol_hist_tab(1).start_date;
1465 max_end_dt := vol_hist_tab(1).end_date;
1466 END IF;
1467
1468 FOR i IN 2..vol_hist_tab.COUNT LOOP
1469
1470 IF vol_hist_tab(i).start_date BETWEEN min_st_dt AND max_end_dt + 1 THEN
1471
1472 IF vol_hist_tab(i).end_date > max_end_dt THEN
1473 max_end_dt := vol_hist_tab(i).end_date;
1474 END IF;
1475
1476 ELSE
1477 RETURN 'N';
1478 END IF;
1479
1480 END LOOP;
1481
1482 IF ( min_st_dt > l_st_dt OR
1483 max_end_dt < l_end_dt ) THEN
1484
1485 RETURN 'N';
1486 END IF;
1487
1488 END LOOP;
1489 RETURN 'Y';
1490
1491 END IF;
1492
1493 EXCEPTION
1494 WHEN OTHERS THEN
1495 pnp_debug_pkg.log('pn_variable_term_pkg.find_volume_continuous (-) : ');
1496 RAISE;
1497
1498 END find_volume_continuous;
1499
1500 --------------------------------------------------------------------------------
1501 -- NAME : get_period
1502 -- DESCRIPTION : Gets the period id for a var_rent_id and date combination
1503 -- PURPOSE :
1504 -- INVOKED FROM :
1505 -- ARGUMENTS :
1506 -- REFERENCE :
1507 -- HISTORY :
1508 --
1509 -- 8.Mar.07 lbala o Created
1510 --------------------------------------------------------------------------------
1511 FUNCTION get_period(p_vr_id IN NUMBER,
1512 p_date IN DATE
1513 )
1514 RETURN NUMBER IS
1515
1516 -- Get the period_id
1517 CURSOR period_cur IS
1518 SELECT period_id
1519 FROM pn_var_periods_all
1520 WHERE var_rent_id = p_vr_id
1521 AND p_date BETWEEN start_date AND end_date;
1522
1523 l_prd_id NUMBER:=NULL;
1524
1525 BEGIN
1526
1527 FOR rec IN period_cur LOOP
1528 l_prd_id := rec.period_id;
1529 END LOOP;
1530
1531 RETURN l_prd_id;
1532
1533 EXCEPTION
1534 WHEN others THEN
1535 RAISE;
1536 END get_period;
1537 --------------------------------------------------------------------------------
1538 -- NAME : get_line
1539 -- DESCRIPTION : Gets the line item for a particular corresponding to a line
1540 -- item id passed
1541 -- PURPOSE :
1542 -- INVOKED FROM :
1543 -- ARGUMENTS :
1544 -- REFERENCE :
1545 -- HISTORY :
1546 --
1547 -- 8.Mar.07 lbala o Created
1548 --------------------------------------------------------------------------------
1549 FUNCTION get_line(p_prd_id IN NUMBER,
1550 p_line_id IN NUMBER
1551 )
1552 RETURN NUMBER IS
1553
1554 -- Get the details of
1555 CURSOR get_line_item
1556 IS
1557 SELECT line_item_id
1558 FROM pn_var_lines_all
1559 WHERE line_default_id IN ( SELECT line_default_id
1560 FROM pn_var_lines_all
1561 WHERE line_item_id=p_line_id
1562 )
1563 AND period_id = p_prd_id;
1564
1565 l_line_id NUMBER := NULL;
1566
1567 BEGIN
1568
1569 FOR rec IN get_line_item LOOP
1570 l_line_id := rec.line_item_id;
1571 END LOOP;
1572
1573 RETURN l_line_id;
1574
1575 EXCEPTION
1576 WHEN others THEN
1577 RAISE;
1578 END get_line;
1579 --------------------------------------------------------------------------------
1580 -- NAME : get_inv_date
1581 -- DESCRIPTION : Gets invoice date for a particular period and date combination
1582 -- PURPOSE :
1583 -- INVOKED FROM :
1584 -- ARGUMENTS :
1585 -- REFERENCE :
1586 -- HISTORY :
1587 --
1588 -- 8.Mar.07 lbala o Created
1589 --------------------------------------------------------------------------------
1590
1591 FUNCTION get_inv_date(p_prd_id IN NUMBER,
1592 p_date IN DATE
1593 )
1594 RETURN DATE IS
1595
1596 -- Get the details of
1597 CURSOR inv_dt_cur
1598 IS
1599 SELECT invoice_date
1600 FROM pn_var_grp_dates_all
1604 l_inv_date DATE;
1601 WHERE period_id = p_prd_id
1602 AND p_date BETWEEN inv_start_date AND inv_end_date ;
1603
1605
1606 BEGIN
1607
1608 FOR rec IN inv_dt_cur LOOP
1609 l_inv_date := rec.invoice_date;
1610 END LOOP;
1611
1612 RETURN l_inv_date;
1613 EXCEPTION
1614 WHEN others THEN
1615 RAISE;
1616 END get_inv_date;
1617
1618 -------------------------------------------------------------------------------
1619 -- NAME : FIND_VOLUME_CONTINUOUS_FOR()
1620 -- PURPOSE : Checks that no gaps exist in volumes for a invoice period
1621 -- DESCRIPTION : Checks that volumes exist for each and every day of the
1622 -- invoice period ,created only for forecasted or variance terms
1623 -- SCOPE : PUBLIC
1624 --
1625 -- ARGUMENTS : p_var_rent_id : variable rent ID (mandatory)
1626 -- p_period_id : Id of a particular period (optional)
1627 -- p_invoice_date: Invoice date
1628 -- p_rent_type : Forecasted or Variance
1629 -- RETURNS :
1630 -- HISTORY :
1631 --
1632 -- 03-APR-07 Lbala o Created.
1633 --
1634 -------------------------------------------------------------------------------
1635 FUNCTION find_volume_continuous_for (p_var_rent_id IN NUMBER,
1636 p_period_id IN NUMBER,
1637 p_invoice_date IN DATE,
1638 p_rent_type IN VARCHAR2
1639 ) RETURN VARCHAR2
1640 IS
1641 TYPE vol_hist_rec IS RECORD(
1642 start_date pn_var_vol_hist_all.start_date%TYPE,
1643 end_date pn_var_vol_hist_all.end_date%TYPE,
1644 line_item_id pn_var_lines_all.line_item_id%TYPE
1645 );
1646
1647 TYPE vol_hist_type IS
1648 TABLE OF vol_hist_rec
1649 INDEX BY BINARY_INTEGER;
1650
1651 vol_hist_tab vol_hist_type;
1652
1653 --Get all line items for a period
1654 CURSOR line_items_c(p_prd_id IN NUMBER) IS
1655 SELECT line_item_id
1656 FROM pn_var_lines_all
1657 WHERE period_id = p_prd_id
1658 ORDER BY line_item_id;
1659
1660 --Get all volume history records for a inv_dt,period_id,line_item_id combination
1661 --for forecasted terms
1662 CURSOR vol_hist_dates_for(p_prd_id IN NUMBER, p_inv_dt IN DATE, p_line_id IN NUMBER)
1663 IS
1664 SELECT start_date, end_date, line_item_id
1665 FROM pn_var_vol_hist_all vol,
1666 (SELECT gd.period_id,
1667 gd.grp_date_id
1668 FROM pn_var_grp_dates_all gd
1669 WHERE gd.period_id= p_prd_id
1670 AND gd.invoice_date = p_inv_dt OR p_inv_dt IS NULL
1671 )itemp
1672 WHERE vol.grp_date_id = itemp.grp_date_id
1673 AND vol.line_item_id = p_line_id
1674 AND vol.period_id = itemp.period_id
1675 AND vol_hist_status_code = 'APPROVED'
1676 AND forecasted_amount IS NOT NULL
1677 ORDER BY start_date,end_date;
1678
1679 --Get all volume history records for a inv_dt,period_id,line_item_id combination
1680 --for variance terms
1681 CURSOR vol_hist_dates_var(p_prd_id IN NUMBER, p_inv_dt IN DATE, p_line_id IN NUMBER)
1682 IS
1683 SELECT start_date, end_date, line_item_id
1684 FROM pn_var_vol_hist_all vol,
1685 (SELECT gd.period_id,
1686 gd.grp_date_id
1687 FROM pn_var_grp_dates_all gd
1688 WHERE gd.period_id= p_prd_id
1689 AND gd.invoice_date = p_inv_dt OR p_inv_dt IS NULL
1690 )itemp
1691 WHERE vol.grp_date_id = itemp.grp_date_id
1692 AND vol.line_item_id = p_line_id
1693 AND vol.period_id = itemp.period_id
1694 AND vol_hist_status_code = 'APPROVED'
1695 AND actual_amount IS NOT NULL
1696 ORDER BY start_date,end_date;
1697
1698 -- Get partial period information
1699 CURSOR partial_prd(p_vr_id IN NUMBER) IS
1700 SELECT period_id, period_num, start_date, end_date
1701 FROM pn_var_periods_all
1702 WHERE partial_period='Y'
1703 AND var_rent_id = p_vr_id;
1704
1705 -- Get invoice period dates
1706 CURSOR inv_prd_cur(p_prd_id IN NUMBER,p_inv_dt IN DATE) IS
1707 SELECT inv_start_date ,inv_end_date
1708 FROM pn_var_grp_dates_all
1709 WHERE period_id = p_prd_id
1710 AND invoice_date = p_inv_dt;
1711
1712 l_line_id NUMBER:= NULL;
1713 l_invoice_date DATE := NULL;
1714 l_prev_end_dt DATE ;
1715 l_st_dt DATE := NULL;
1716 l_end_dt DATE := NULL;
1717 min_st_dt DATE := to_date('01/01/2247','dd/mm/rrrr');
1718 max_end_dt DATE := NULL;
1719 inv_st_dt DATE := NULL;
1720 inv_end_dt DATE := NULL;
1721
1722 BEGIN
1723 pnp_debug_pkg.log('pn_variable_term_pkg.find_volume_continuous_for (+) : ');
1724
1725 l_invoice_date := p_invoice_date;
1726
1727 FOR inv_prd_rec IN inv_prd_cur(p_period_id, p_invoice_date) LOOP
1728 inv_st_dt := inv_prd_rec.inv_start_date;
1729 inv_end_dt := inv_prd_rec.inv_end_date;
1730 END LOOP;
1731
1732 l_st_dt := pn_var_rent_calc_pkg.inv_start_date(inv_start_date => l_invoice_date
1733 ,vr_id => p_var_rent_id
1734 ,p_period_id => p_period_id
1735 );
1736
1737 l_end_dt := pn_var_rent_calc_pkg.inv_end_date(inv_start_date => l_invoice_date
1738 ,vr_id => p_var_rent_id
1739 ,p_period_id => p_period_id
1740 );
1741
1742 FOR line_rec IN line_items_c(p_period_id) LOOP
1743
1744 l_line_id := line_rec.line_item_id;
1748 max_end_dt := to_date('01/01/1976','dd/mm/rrrr');
1745 vol_hist_tab.DELETE;
1746 l_prev_end_dt := NULL;
1747 min_st_dt := to_date('01/01/2247','dd/mm/rrrr');
1749
1750 IF(p_rent_type = 'FORECASTED') THEN
1751
1752 /* For forecasted terms*/
1753 OPEN vol_hist_dates_for(p_period_id,l_invoice_date,l_line_id);
1754 FETCH vol_hist_dates_for BULK COLLECT INTO vol_hist_tab;
1755 CLOSE vol_hist_dates_for;
1756
1757 ELSE
1758
1759 /* For Variance*/
1760 OPEN vol_hist_dates_var(p_period_id,l_invoice_date,l_line_id);
1761 FETCH vol_hist_dates_var BULK COLLECT INTO vol_hist_tab;
1762 CLOSE vol_hist_dates_var;
1763
1764 END IF;
1765
1766 IF(vol_hist_tab.COUNT > 0) THEN
1767 min_st_dt := vol_hist_tab(1).start_date;
1768 max_end_dt := vol_hist_tab(1).end_date;
1769 END IF;
1770
1771 FOR i IN 2..vol_hist_tab.COUNT LOOP
1772
1773 IF vol_hist_tab(i).start_date BETWEEN min_st_dt AND max_end_dt + 1 THEN
1774
1775 IF vol_hist_tab(i).end_date > max_end_dt THEN
1776 max_end_dt := vol_hist_tab(i).end_date;
1777 END IF;
1778
1779 ELSE
1780 RETURN 'N';
1781 END IF;
1782
1783 END LOOP;
1784
1785 IF ( min_st_dt > l_st_dt OR
1786 max_end_dt < l_end_dt ) THEN
1787
1788 RETURN 'N';
1789 END IF;
1790
1791 END LOOP;
1792
1793 RETURN 'Y';
1794
1795 EXCEPTION
1796 WHEN others THEN
1797 pnp_debug_pkg.log('pn_variable_term_pkg.find_volume_continuous_for (-) : ');
1798 RAISE;
1799 END find_volume_continuous_for;
1800
1801
1802 -------------------------------------------------------------------------------
1803 -- PROCEDURE : create_reversal_terms
1804 -- Procedure for creation of reversal variable rent payment terms.
1805 --
1806 -- 17-apr-07 piagrawa o Created
1807 -------------------------------------------------------------------------------
1808
1809 PROCEDURE create_reversal_terms(
1810 p_payment_term_id IN NUMBER
1811 ,p_var_rent_inv_id IN NUMBER
1812 ,p_var_rent_type IN VARCHAR2
1813 ) IS
1814
1815
1816 l_distribution_id pn_distributions.distribution_id%TYPE;
1817 l_payment_term_id pn_payment_terms.payment_term_id%TYPE;
1818 l_rowid ROWID;
1819 l_distribution_count NUMBER := 0;
1820 l_context varchar2(2000);
1821
1822 CURSOR csr_distributions
1823 IS
1824 SELECT *
1825 FROM pn_distributions_all
1826 WHERE payment_term_id = p_payment_term_id;
1827
1828 CURSOR payment_term_cur
1829 IS
1830 SELECT *
1831 FROM pn_payment_terms_all
1832 WHERE payment_term_id = p_payment_term_id;
1833
1834 term_count NUMBER := 0;
1835
1836 BEGIN
1837
1838 pn_variable_amount_pkg.put_log ('pn_variable_term_pkg.create_reversal_terms : (+)');
1839
1840 FOR payment_term_rec IN payment_term_cur LOOP
1841
1842 pnt_payment_terms_pkg.insert_row (
1843 x_rowid => l_rowid
1844 ,x_payment_term_id => l_payment_term_id
1845 ,x_index_period_id => payment_term_rec.index_period_id
1846 ,x_index_term_indicator => payment_term_rec.index_term_indicator
1847 ,x_var_rent_inv_id => p_var_rent_inv_id
1848 ,x_var_rent_type => p_var_rent_type
1849 ,x_last_update_date => SYSDATE
1850 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
1851 ,x_creation_date => SYSDATE
1852 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
1853 ,x_payment_purpose_code => payment_term_rec.payment_purpose_code
1854 ,x_payment_term_type_code => payment_term_rec.payment_term_type_code
1855 ,x_frequency_code => payment_term_rec.frequency_code
1856 ,x_lease_id => payment_term_rec.lease_id
1857 ,x_lease_change_id => payment_term_rec.lease_change_id
1858 ,x_start_date => payment_term_rec.start_date
1859 ,x_end_date => payment_term_rec.end_date
1860 ,x_set_of_books_id => payment_term_rec.set_of_books_id
1861 ,x_currency_code => payment_term_rec.currency_code
1862 ,x_rate => payment_term_rec.rate
1863 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
1864 ,x_vendor_id => payment_term_rec.vendor_id
1865 ,x_vendor_site_id => payment_term_rec.vendor_site_id
1866 ,x_target_date => NULL
1867 ,x_actual_amount => -(payment_term_rec.actual_amount)
1868 ,x_estimated_amount => NULL
1869 ,x_attribute_category => payment_term_rec.attribute_category
1870 ,x_attribute1 => payment_term_rec.attribute1
1871 ,x_attribute2 => payment_term_rec.attribute2
1872 ,x_attribute3 => payment_term_rec.attribute3
1873 ,x_attribute4 => payment_term_rec.attribute4
1874 ,x_attribute5 => payment_term_rec.attribute5
1875 ,x_attribute6 => payment_term_rec.attribute6
1876 ,x_attribute7 => payment_term_rec.attribute7
1880 ,x_attribute11 => payment_term_rec.attribute11
1877 ,x_attribute8 => payment_term_rec.attribute8
1878 ,x_attribute9 => payment_term_rec.attribute9
1879 ,x_attribute10 => payment_term_rec.attribute10
1881 ,x_attribute12 => payment_term_rec.attribute12
1882 ,x_attribute13 => payment_term_rec.attribute13
1883 ,x_attribute14 => payment_term_rec.attribute14
1884 ,x_attribute15 => payment_term_rec.attribute15
1885 ,x_project_attribute_category => NULL
1886 ,x_project_attribute1 => NULL
1887 ,x_project_attribute2 => NULL
1888 ,x_project_attribute3 => NULL
1889 ,x_project_attribute4 => NULL
1890 ,x_project_attribute5 => NULL
1891 ,x_project_attribute6 => NULL
1892 ,x_project_attribute7 => NULL
1893 ,x_project_attribute8 => NULL
1894 ,x_project_attribute9 => NULL
1895 ,x_project_attribute10 => NULL
1896 ,x_project_attribute11 => NULL
1897 ,x_project_attribute12 => NULL
1898 ,x_project_attribute13 => NULL
1899 ,x_project_attribute14 => NULL
1900 ,x_project_attribute15 => NULL
1901 ,x_customer_id => payment_term_rec.customer_id
1902 ,x_customer_site_use_id => payment_term_rec.customer_site_use_id
1903 ,x_normalize => 'N'
1904 ,x_location_id => payment_term_rec.location_id
1905 ,x_schedule_day => payment_term_rec.schedule_day
1906 ,x_cust_ship_site_id => payment_term_rec.cust_ship_site_id
1907 ,x_ap_ar_term_id => payment_term_rec.ap_ar_term_id
1908 ,x_cust_trx_type_id => payment_term_rec.cust_trx_type_id
1909 ,x_project_id => payment_term_rec.project_id
1910 ,x_task_id => payment_term_rec.task_id
1911 ,x_organization_id => payment_term_rec.organization_id
1912 ,x_expenditure_type => payment_term_rec.expenditure_type
1913 ,x_expenditure_item_date => payment_term_rec.expenditure_item_date
1914 ,x_tax_group_id => payment_term_rec.tax_group_id
1915 ,x_tax_code_id => payment_term_rec.tax_code_id
1916 ,x_tax_classification_code => payment_term_rec.tax_classification_code
1917 ,x_tax_included => payment_term_rec.tax_included
1918 ,x_distribution_set_id => payment_term_rec.distribution_set_id
1919 ,x_inv_rule_id => payment_term_rec.inv_rule_id
1920 ,x_account_rule_id => payment_term_rec.account_rule_id
1921 ,x_salesrep_id => payment_term_rec.salesrep_id
1922 ,x_approved_by => NULL
1923 ,x_status => 'DRAFT'
1924 ,x_po_header_id => payment_term_rec.po_header_id
1925 ,x_cust_po_number => payment_term_rec.cust_po_number
1926 ,x_receipt_method_id => payment_term_rec.receipt_method_id
1927 ,x_calling_form => 'PNXVAREN'
1928 ,x_org_id => payment_term_rec.org_id
1929 ,x_term_template_id => payment_term_rec.term_template_id
1930 ,x_area => payment_term_rec.area
1931 ,x_area_type_code => payment_term_rec.area_type_code
1932 ,x_include_in_var_rent => NULL
1933 );
1934
1935 END LOOP;
1936
1937 /* Create a record in pn_distributions */
1938
1939 l_distribution_count := 0;
1940
1941 FOR rec_distributions IN csr_distributions LOOP
1942
1943 pn_variable_amount_pkg.put_log(' account_id '||rec_distributions.account_id);
1944 pn_variable_amount_pkg.put_log(' account_class '||rec_distributions.account_id);
1945
1946
1947 l_context := 'Inserting into pn_distributions';
1948 pn_distributions_pkg.insert_row (
1949 x_rowid => l_rowid
1950 ,x_distribution_id => l_distribution_id
1951 ,x_account_id => rec_distributions.account_id
1952 ,x_payment_term_id => l_payment_term_id
1953 ,x_term_template_id => NULL
1954 ,x_account_class => rec_distributions.account_class
1955 ,x_percentage => rec_distributions.percentage
1956 ,x_line_number => rec_distributions.line_number
1957 ,x_last_update_date => SYSDATE
1958 ,x_last_updated_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
1959 ,x_creation_date => SYSDATE
1960 ,x_created_by => NVL (fnd_profile.VALUE ('USER_ID'), 0)
1961 ,x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0)
1962 ,x_attribute_category => rec_distributions.attribute_category
1963 ,x_attribute1 => rec_distributions.attribute1
1964 ,x_attribute2 => rec_distributions.attribute2
1965 ,x_attribute3 => rec_distributions.attribute3
1966 ,x_attribute4 => rec_distributions.attribute4
1967 ,x_attribute5 => rec_distributions.attribute5
1968 ,x_attribute6 => rec_distributions.attribute6
1969 ,x_attribute7 => rec_distributions.attribute7
1973 ,x_attribute11 => rec_distributions.attribute11
1970 ,x_attribute8 => rec_distributions.attribute8
1971 ,x_attribute9 => rec_distributions.attribute9
1972 ,x_attribute10 => rec_distributions.attribute10
1974 ,x_attribute12 => rec_distributions.attribute12
1975 ,x_attribute13 => rec_distributions.attribute13
1976 ,x_attribute14 => rec_distributions.attribute14
1977 ,x_attribute15 => rec_distributions.attribute15
1978 ,x_org_id => rec_distributions.org_id
1979 );
1980
1981 l_rowid := NULL;
1982 l_distribution_id := NULL;
1983 l_distribution_count := l_distribution_count + 1;
1984
1985 END LOOP;
1986
1987
1988 l_context := 'exiting from loop';
1989
1990 -- Check if term exists and set actual_term_status accordingly.
1991
1992 IF p_var_rent_type <> 'ADJUSTMENT' THEN
1993 SELECT count(*)
1994 INTO term_count
1995 FROM pn_payment_terms_all
1996 WHERE var_rent_inv_id = p_var_rent_inv_id
1997 AND var_rent_type = 'ACTUAL';
1998
1999 IF term_count > 0 THEN
2000 pnp_debug_pkg.debug('setting actual term status ...');
2001 UPDATE pn_var_rent_inv_all
2002 SET actual_term_status='Y'
2003 WHERE var_rent_inv_id = p_var_rent_inv_id;
2004
2005 UPDATE pn_var_rent_inv_all
2006 SET true_up_status = 'Y'
2007 WHERE var_rent_inv_id = p_var_rent_inv_id
2008 AND true_up_status IS NOT NULL;
2009
2010 ELSE
2011 UPDATE pn_var_rent_inv_all
2012 SET actual_term_status='N'
2013 WHERE var_rent_inv_id = p_var_rent_inv_id;
2014
2015 UPDATE pn_var_rent_inv_all
2016 SET true_up_status = 'N'
2017 WHERE var_rent_inv_id = p_var_rent_inv_id
2018 AND true_up_status IS NOT NULL;
2019
2020 END IF;
2021
2022 END IF;
2023
2024 pn_variable_amount_pkg.put_log('pn_variable_term_pkg.create_reversal_terms (-) ');
2025
2026 EXCEPTION
2027
2028 when others then
2029 pn_variable_amount_pkg.put_log(substrb('pn_variable_term_pkg.Error in create_reversal_terms - ' ||
2030 to_char(sqlcode)||' : '||sqlerrm || ' - '|| l_context,1,244));
2031 rollback to create_terms;
2032
2033 -- Check if term exists and set actual_term_status accordingly.
2034
2035 IF p_var_rent_type <> 'ADJUSTMENT' THEN
2036 SELECT count(*)
2037 INTO term_count
2038 FROM pn_payment_terms_all
2039 WHERE var_rent_inv_id = p_var_rent_inv_id
2040 AND var_rent_type = 'ACTUAL';
2041
2042 IF term_count > 0 THEN
2043 pnp_debug_pkg.debug('setting actual term status ...');
2044 UPDATE pn_var_rent_inv_all
2045 SET actual_term_status='Y'
2046 WHERE var_rent_inv_id = p_var_rent_inv_id;
2047 ELSE
2048 UPDATE pn_var_rent_inv_all
2049 SET actual_term_status='N'
2050 WHERE var_rent_inv_id = p_var_rent_inv_id;
2051 END IF;
2052 END IF;
2053
2054 END create_reversal_terms;
2055
2056 END pn_variable_term_pkg;
2057