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