DBA Data[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