[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_DEFAULTS_PKG
Source
1 package body PN_VAR_DEFAULTS_PKG as
2 /* $Header: PNVRDFTB.pls 120.0 2007/10/03 14:28:49 rthumma noship $ */
3
4 /*********** Comment out code duplicate also found in PNCHCALS.pls
5
6 ===========================================================================+
7 | PROCEDURE COPY_LINE_BKDT_DEFAULTS
8 |
9 |
10 | DESCRIPTION
11 | Create records in the PN_VAR_LINE_DEFAULTS and PN_VAR_BKDT_DEFAULTS tables
12 | when change calendar function executed.
13 |
14 | SCOPE - PUBLIC
15 |
16 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
17 |
18 | ARGUMENTS : IN:
19 | X_VAR_RENT_ID
20 | X_CHG_CAL_VAR_RENT_ID
21 |
22 | OUT:
23 |
24 | RETURNS : None
25 |
26 |
27 | MODIFICATION HISTORY
28 |
29 | 13-FEB-2003 Gary Olson o Created
30 +===========================================================================
31
32 procedure copy_line_bkdt_defaults (
33 X_VAR_RENT_ID in NUMBER,
34 X_CHG_CAL_VAR_RENT_ID in NUMBER
35 ) IS
36
37 l_linerowid VARCHAR2(18) := NULL;
38 l_lineDefId NUMBER := NULL;
39 l_lineNum NUMBER := 0;
40 l_new_rentid NUMBER := NULL;
41 l_old_rentid NUMBER := NULL;
42 l_bkhdDefId NUMBER := 0;
43 l_bkhdrowid VARCHAR2(18) := NULL;
44 l_bkhdNum NUMBER := 0;
45 l_bkdtrowid VARCHAR2(18) := NULL;
46 l_bkdtDefId NUMBER := NULL;
47 l_bkdtNum NUMBER := 0;
48
49 cursor c_get_linedef is
50 select * from pn_var_line_defaults_all
51 where var_rent_id = l_old_rentid;
52
53 cursor c_get_bkhddef (p_line_def_id NUMBER) is
54 select * from pn_var_bkhd_defaults_all
55 where line_default_id = p_line_def_id;
56
57 cursor c_get_bkdtdef (p_bkhd_def_id NUMBER) is
58 select * from pn_var_bkdt_defaults_all
59 where bkhd_default_id = p_bkhd_def_id;
60
61 begin
62
63 l_old_rentid := X_VAR_RENT_ID;
64 l_new_rentid := X_CHG_CAL_VAR_RENT_ID;
65
66 FOR c_lrec IN c_get_linedef LOOP
67
68 SELECT pn_var_line_defaults_s.nextval
69 INTO l_lineDefid
70 FROM DUAL;
71
72 l_bkhdNum := 0;
73 l_lineNum := l_lineNum + 1;
74
75 insert into pn_var_line_defaults_all (
76 line_default_id,
77 line_num,
78 var_rent_id,
79 sales_type_code,
80 item_category_code,
81 line_template_id,
82 agreement_template_id,
83 line_start_date,
84 line_end_date,
85 last_update_date,
86 last_updated_by,
87 creation_date,
88 created_by,
89 last_update_login,
90 org_id,
91 processed_flag
92 ) values (
93 l_lineDefid,
94 l_lineNum,
95 l_new_rentid,
96 c_lrec.SALES_TYPE_CODE,
97 c_lrec.ITEM_CATEGORY_CODE,
98 c_lrec.line_template_id,
99 c_lrec.AGREEMENT_TEMPLATE_ID,
100 c_lrec.LINE_START_DATE,
101 c_lrec.LINE_END_DATE,
102 sysdate,
103 NVL(fnd_profile.value('USER_ID'),0),
104 sysdate,
105 NVL(fnd_profile.value('USER_ID'),0),
106 NVL(fnd_profile.value('USER_ID'),0),
107 c_lrec.ORG_ID,
108 c_lrec.processed_flag
109 );
110
111
112 FOR c_hdrec IN c_get_bkhddef (c_lrec.line_default_id) LOOP
113
114 SELECT pn_var_bkhd_defaults_s.nextval
115 INTO l_bkhdDefId
116 FROM DUAL;
117
118 l_bkdtNum := 0;
119 l_bkhdNum := l_bkhdNum + 1;
120
121 insert into pn_var_bkhd_defaults_all (
122 bkhd_default_id,
123 bkhd_detail_num,
124 line_default_id,
125 bkhd_start_date,
126 bkhd_end_date,
127 break_type,
128 base_rent_type,
129 natural_break_rate,
130 base_rent,
131 breakpoint_type,
132 breakpoint_level,
133 bkpt_head_template_id,
134 agreement_template_id,
135 last_update_date,
136 last_updated_by,
137 creation_date,
138 created_by,
139 last_update_login,
140 org_id,
141 var_rent_id,
142 processed_flag
143 ) values (
144 l_bkhdDefId,
145 l_bkhdNum,
146 l_lineDefId,
147 c_hdrec.bkhd_start_date,
148 c_hdrec.bkhd_end_date,
149 c_hdrec.break_type,
150 c_hdrec.base_rent_type,
151 c_hdrec.natural_break_rate,
152 c_hdrec.base_rent,
153 c_hdrec.breakpoint_type,
154 c_hdrec.breakpoint_level,
155 c_hdrec.bkpt_head_template_id,
156 c_hdrec.agreement_template_id,
157 sysdate,
158 NVL(fnd_profile.value('USER_ID'),0),
159 sysdate,
160 NVL(fnd_profile.value('USER_ID'),0),
161 NVL(fnd_profile.value('USER_ID'),0),
162 c_hdrec.ORG_ID,
163 c_hdrec.var_rent_id,
164 c_hdrec.processed_flag
165 );
166
167 FOR c_dtrec IN c_get_bkdtdef (c_hdrec.bkhd_default_id) LOOP
168
169 SELECT pn_var_bkdt_defaults_s.nextval
170 INTO l_bkdtDefId
171 FROM DUAL;
172
173 l_bkdtNum := l_bkdtNum+1;
174
175
176 INSERT into pn_var_bkdt_defaults_all (
177 bkdt_default_id,
178 bkdt_detail_num,
179 bkhd_default_id,
180 bkdt_start_date,
181 bkdt_end_date,
182 period_bkpt_vol_start,
183 period_bkpt_vol_end,
184 group_bkpt_vol_start,
185 group_bkpt_vol_end,
186 bkpt_rate,
187 last_update_date,
188 last_updated_by,
189 creation_date,
190 created_by,
191 last_update_login,
192 org_id,
193 var_rent_id,
194 processed_flag
195 ) values (
196 l_bkdtDefid,
197 l_bkdtNum,
198 l_bkhdDefid,
199 c_dtrec.BKDT_START_DATE,
200 c_dtrec.BKDT_END_DATE,
201 c_dtrec.PERIOD_BKPT_VOL_START,
202 c_dtrec.PERIOD_BKPT_VOL_END,
203 c_dtrec.GROUP_BKPT_VOL_START,
204 c_dtrec.GROUP_BKPT_VOL_END,
205 c_dtrec.BKPT_RATE,
206 sysdate,
207 NVL(fnd_profile.value('USER_ID'),0),
208 sysdate,
209 NVL(fnd_profile.value('USER_ID'),0),
210 NVL(fnd_profile.value('USER_ID'),0),
211 C_dtrec.ORG_ID,
212 C_dtrec.var_rent_id,
213 C_dtrec.processed_flag
214 );
215 END LOOP;
216
217 END LOOP;
218
219 END LOOP;
220
221 commit;
222
223 end copy_line_bkdt_defaults;
224
225 ===========================================================================+
226 | PROCEDURE COPY_CONSTR_DEFAULTS
227 |
228 |
229 | DESCRIPTION
230 | Create records in the PN_VAR_CONSTR_DEFAULTS table when change calendar
231 function executed.
232 |
233 | SCOPE - PUBLIC
234 |
235 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
236 |
237 | ARGUMENTS : IN:
238 | X_VAR_RENT_ID
239 | X_CHG_CAL_VAR_RENT_ID
240 |
241 | OUT:
242 |
243 | RETURNS : None
244 |
245 |
246 | MODIFICATION HISTORY
247 |
248 | 13-FEB-2003 Gary Olson o Created
249 +===========================================================================
250
251 procedure copy_constr_defaults (
252 X_VAR_RENT_ID in NUMBER,
253 X_CHG_CAL_VAR_RENT_ID in NUMBER
254 ) IS
255
256 l_consrowid VARCHAR2(18) := NULL;
257 l_consDefId NUMBER := NULL;
258 l_consNum NUMBER := 0;
259 l_rowid VARCHAR2(32767);
260
261 l_new_rentid NUMBER := NULL;
262 l_old_rentid NUMBER := NULL;
263
264 cursor c_get_consdef is
265 select * from pn_var_constr_defaults_all
266 where var_rent_id = l_old_rentid;
267
268 BEGIN
269
270
271 l_old_rentid := X_VAR_RENT_ID;
272 l_new_rentid := X_CHG_CAL_VAR_RENT_ID;
273
274 FOR c_crec IN c_get_consdef LOOP
275
276 SELECT pn_var_constr_defaults_s.nextval
277 INTO l_consDefId
278 FROM DUAL;
279
280 l_consNum := l_consNum+1;
281
282
283 pn_var_constr_defaults_pkg.insert_row (
284 X_ROWID => l_rowid,
285 X_CONSTR_DEFAULT_ID => l_consDefid,
286 X_CONSTR_DEFAULT_NUM => l_consNum,
287 X_VAR_RENT_ID => l_new_rentid,
288 X_AGREEMENT_TEMPLATE_ID => c_crec.agreement_template_id,
289 X_CONSTR_TEMPLATE_ID => c_crec.constr_template_id ,
290 X_CONSTR_START_DATE => c_crec.constr_start_date,
291 X_CONSTR_END_DATE => c_crec.CONSTR_END_DATE,
292 X_CONSTR_CAT_CODE => c_crec.CONSTR_CAT_CODE,
293 X_TYPE_CODE => c_crec.type_code,
294 X_AMOUNT => c_crec.amount,
295 X_CREATION_DATE => sysdate,
296 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
297 X_LAST_UPDATE_DATE => sysdate,
298 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
299 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
300 X_ORG_ID => c_crec.ORG_ID
301 );
302
303 END LOOP;
304
305 commit;
306
307 END copy_constr_defaults;
308 ************* END DUPLICATE COMMENT ****************/
309
310 /*===========================================================================+
311 | PROCEDURE CREATE_DEFAULT_CONSTRAINTS
312 |
313 |
314 | DESCRIPTION
315 | Create records in the PN_VAR_CONSTRAINTS_ALL table from date range constraints
316 |
317 | SCOPE - PUBLIC
318 |
319 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
320 |
321 | ARGUMENTS : IN:
322 | X_VAR_RENT_ID
323 |
324 | OUT:
325 |
326 | RETURNS : None
327 |
328 |
329 | MODIFICATION HISTORY
330 |
331 | 13-FEB-03 GOlson o Created
332 | 28-JUN-06 Pikhar o Added desc flex and modified entering of start
333 | and end date for constraints
334 | 31-AUG-06 Pikhar o Modified cursor c1 so as to take the intersection
335 | of dates
336 +===========================================================================*/
337
338 procedure create_default_constraints (
339 X_VAR_RENT_ID in NUMBER
340 ) IS
341
342 l_var_rent_id NUMBER := NULL;
343 l_rowId VARCHAR2(18) := NULL;
344 l_constrId NUMBER := NULL;
345 l_constrNum NUMBER := 0;
346 l_null VARCHAR2(150) := NULL;
347 L_filename VARCHAR2(50) := 'Create_def_cons'||to_char(sysdate,'MMDDYYHHMMSS');
348 l_pathname VARCHAR2(20) := '/usr/tmp';
349 l_start_date DATE := NULL;
350 l_end_date DATE := NULL;
351
352 cursor c1 (p_start DATE, p_end DATE, p_defid NUMBER) is
353 select distinct period_id, start_date, end_date
354 from pn_var_periods_all
355 where var_rent_id = l_var_rent_id
356 and start_date <= p_end
357 and end_date >= p_start
358 and period_id not in (select period_id
359 from pn_var_constraints_all
360 where constr_default_id = p_defid);
361
362 cursor c2 is
363 select *
364 from pn_var_constr_defaults_all
365 where var_rent_id = l_var_rent_id;
366
367 cursor c_num (p_periodId NUMBER) is
368 select NVL(max(CONSTRAINT_NUM),0)
369 from pn_var_constraints_all
370 where period_id = p_periodId;
371
372 BEGIN
373
374 pnp_debug_pkg.debug(' create_default_constraints +');
375 l_var_rent_id := X_VAR_RENT_ID;
376
377 FOR c2_rec IN c2 LOOP
378 FOR c1_rec IN c1 (c2_rec.constr_start_date,
379 c2_rec.constr_end_date,
380 c2_rec.constr_default_id) LOOP
381
382 open c_num (c1_rec.period_id);
383 fetch c_num into l_constrNum;
384 close c_num;
385 l_constrNum := l_constrNum + 1;
386
387 IF c2_rec.constr_start_date < c1_rec.start_date THEN
388 l_start_date := c1_rec.start_date;
389 ELSE
390 l_start_date := c2_rec.constr_start_date;
391 END IF;
392
393 IF c2_rec.constr_end_date > c1_rec.end_date THEN
394 l_end_date := c1_rec.end_date;
395 ELSE
396 l_end_date := c2_rec.constr_end_date;
397 END IF;
398
399
400 PN_VAR_CONSTRAINTS_PKG.INSERT_ROW(
401 X_ROWID => l_rowid,
402 X_CONSTRAINT_ID => l_constrId,
403 x_CONSTRAINT_NUM => l_constrNum,
404 X_PERIOD_ID => c1_rec.period_id,
405 X_CONSTR_CAT_CODE => c2_rec.constr_cat_code,
406 X_TYPE_CODE => c2_rec.type_code,
407 X_AMOUNT => c2_rec.amount,
408 X_AGREEMENT_TEMPLATE_ID => c2_rec.agreement_template_id,
409 X_CONSTR_TEMPLATE_ID => c2_rec.constr_template_id,
410 X_CONSTR_DEFAULT_ID => c2_rec.constr_default_id,
411 X_COMMENTS => NULL,
412 X_ATTRIBUTE_CATEGORY => c2_rec.ATTRIBUTE_CATEGORY,
413 X_ATTRIBUTE1 => c2_rec.ATTRIBUTE1,
414 X_ATTRIBUTE2 => c2_rec.ATTRIBUTE2,
415 X_ATTRIBUTE3 => c2_rec.ATTRIBUTE3,
416 X_ATTRIBUTE4 => c2_rec.ATTRIBUTE4,
417 X_ATTRIBUTE5 => c2_rec.ATTRIBUTE5,
418 X_ATTRIBUTE6 => c2_rec.ATTRIBUTE6,
419 X_ATTRIBUTE7 => c2_rec.ATTRIBUTE7,
420 X_ATTRIBUTE8 => c2_rec.ATTRIBUTE8,
421 X_ATTRIBUTE9 => c2_rec.ATTRIBUTE9,
422 X_ATTRIBUTE10 => c2_rec.ATTRIBUTE10,
423 X_ATTRIBUTE11 => c2_rec.ATTRIBUTE11,
424 X_ATTRIBUTE12 => c2_rec.ATTRIBUTE12,
425 X_ATTRIBUTE13 => c2_rec.ATTRIBUTE13,
426 X_ATTRIBUTE14 => c2_rec.ATTRIBUTE14,
427 X_ATTRIBUTE15 => c2_rec.ATTRIBUTE15,
428 X_ORG_ID => c2_rec.org_id,
429 X_CREATION_DATE => sysdate,
430 X_CREATED_BY => FND_GLOBAL.USER_ID,
431 X_LAST_UPDATE_DATE => sysdate,
432 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
433 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
434 X_CONSTR_START_DATE => l_start_date,
435 X_CONSTR_END_DATE => l_end_date);
436
437 l_constrID := NULL;
438 l_rowid := NULL;
439
440 END LOOP;
441
442 END LOOP;
443
444 PNp_debug_pkg.debug(' create_default_constraints +');
445
446 END create_default_constraints;
447
448 /*===========================================================================+
449 | PROCEDURE CREATE_DEFAULT_LINES
450 |
451 |
452 | DESCRIPTION
453 | Create records in the PN_VAR_LINES_ALL table from date range lines.
454 |
455 | SCOPE - PUBLIC
456 |
457 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
458 |
459 | ARGUMENTS : IN:
460 | X_VAR_RENT_ID
461 |
462 | OUT:
463 |
464 | RETURNS : None
465 |
466 |
467 | MODIFICATION HISTORY
468 |
469 | 13-FEB-2003 Gary Olson o Created
470 | 04-jun-03 graghuna o Removed min/max amount and reverted back to
471 | type code , amount and used table handlers.
472 | 03-NOV-2003 cthangai o CODEV: NBP -
473 | 12-JAN-2004 Srini o Support for not creating lines again for Natural Bkpt
474 | 09-FEB-2004 Srini o Support for 1 to Many for Line Defaults to Header Defaults
475 +===========================================================================*/
476
477 procedure create_default_lines (X_VAR_RENT_ID IN NUMBER,
478 X_CREATE_FLAG IN VARCHAR2 DEFAULT 'N') IS
479 l_var_rent_id NUMBER := NULL;
480 l_rowId VARCHAR2(18) := NULL;
481 l_line_item_id NUMBER := NULL;
482 l_line_item_num NUMBER := NULL;
483 l_bkpt_header_id NUMBER := NULL;
484 l_period_start DATE := NULL;
485 l_period_end DATE := NULL;
486 l_bkhd_start_date DATE := NULL;
487 l_bkhd_end_date DATE := NULL;
488 l_bkptsNum NUMBER := NULL;
489 l_bkpt_detail_id NUMBER := 0;
490 l_bkpt_detail_num NUMBER := 0;
491 l_null VARCHAR2(150) := NULL;
492 l_reporting_periods NUMBER := 0;
493 l_period_bkpt_vol_start NUMBER := 0;
494 l_period_bkpt_vol_end NUMBER := 0;
495 l_group_bkpt_vol_start NUMBER := 0;
496 l_group_bkpt_vol_end NUMBER := 0;
497 l_filename VARCHAR2(50) := 'CREATE_DEFAULT_LINES-'||x_var_rent_id || '-'||to_char(sysdate,'MMDDYYHHMMSS');
498 l_pathname VARCHAR2(20) := '/usr/tmp';
499 l_dummy VARCHAR2(2) := NULL;
500 l_cnt NUMBER;
501 l_sales_type_code VARCHAR2(30);
502 l_item_category_code VARCHAR2(30);
503
504 CURSOR c_periods
505 IS
506 select a.period_id,
507 a.start_date,
508 a.end_date,
509 b.reptg_freq_code
510 from pn_var_periods_all a,
511 pn_var_rent_dates_all b
512 where a.var_rent_id = l_var_rent_id
513 and a.var_rent_id = b.var_rent_id;
514
515 CURSOR c_lines (p_start_date DATE, p_end_date DATE)
516 IS
517 SELECT *
518 /*sales_type_code,
519 item_category_code,
520 line_template_id,
521 agreement_template_id,
522 line_start_date,
523 line_end_date,
524 line_default_id,
525 created_by,
526 org_id*/
527 FROM pn_var_line_defaults_all
528 WHERE var_rent_id = l_var_rent_id
529 --AND NVL(processed_flag,0) <> 1
530 AND (p_start_date BETWEEN line_start_date AND line_end_date
531 OR p_end_date BETWEEN line_start_date AND line_end_date
532 OR line_start_date BETWEEN p_start_date AND p_end_date
533 OR line_end_date BETWEEN p_start_date AND p_end_date);
534
535 CURSOR c_bkheads (p_line_default_id NUMBER)
536 IS
537 SELECT *
538 /*bkhd_default_id,
539 line_default_id,
540 bkhd_start_date,
541 bkhd_end_date,
542 break_type,
543 base_rent_type,
544 natural_break_rate,
545 base_rent,
546 breakpoint_type,
547 breakpoint_level,
548 bkpt_head_template_id,
549 agreement_template_id,
550 last_update_login,
551 org_id*/
552 from pn_var_bkhd_defaults_all
553 where line_default_id = p_line_default_id
554 and NVL(processed_flag,0) <> 1;
555
556 CURSOR c_bkdets (p_head_default_id NUMBER)
557 IS
558 SELECT *
559 /*bkdt_default_id,
560 bkdt_start_date,
561 bkdt_end_date,
562 period_bkpt_vol_start,
563 period_bkpt_vol_end,
564 group_bkpt_vol_start,
565 group_bkpt_vol_end,
566 bkpt_rate,
567 org_id,
568 annual_basis_amount --03-NOV-2003 */
569 from pn_var_bkdt_defaults_all
570 where bkhd_default_id = p_head_default_id
571 and NVL(processed_flag,0) <> 1;
572
573 CURSOR c_rep_periods(p_period_id NUMBER)
574 IS
575 select count(grp_start_date)
576 from pn_var_grp_dates_all
577 where period_id = p_period_id;
578
579 CURSOR line_exists_cur ( ip_period_id NUMBER,ip_item_category_code VARCHAR2,ip_sales_type_code VARCHAR2)
580 IS
581 SELECT 'x' line_exists
582 FROM dual
583 WHERE exists ( select line_item_id
584 from pn_var_lines_all
585 where period_id = ip_period_id
586 and item_category_code = ip_item_category_code
587 and sales_type_code = ip_sales_type_code);
588
589 /* codev changes - line_exists_cur fails in its purpose if either item_category_code or sales_type_code is null
590 Hence we require two more cursors which fetch the lines incase any one of them is null*/
591 CURSOR line_exists_sales_cur ( ip_period_id NUMBER,ip_sales_type_code VARCHAR2)
592 IS
593 SELECT 'x' line_exists
594 FROM dual
595 WHERE exists ( select line_item_id
596 from pn_var_lines_all
597 where period_id = ip_period_id
598 and sales_type_code = ip_sales_type_code
599 and rownum <2);
600
601 CURSOR line_exists_item_cur ( ip_period_id NUMBER,ip_item_category_code VARCHAR2)
602 IS
603 SELECT 'x' line_exists
604 FROM dual
605 WHERE exists ( select line_item_id
606 from pn_var_lines_all
607 where period_id = ip_period_id
608 and item_category_code = ip_item_category_code
609 and rownum <2);
610 /* codev changes ends */
611
612 CURSOR c_bkheads_natural(p_line_default_id NUMBER)
613 IS
614 SELECT *
615 /*bkhd_default_id,
616 line_default_id,
617 bkhd_start_date,
618 bkhd_end_date,
619 break_type,
620 base_rent_type,
621 natural_break_rate,
622 base_rent,
623 breakpoint_type,
624 breakpoint_level,
625 bkpt_head_template_id,
626 agreement_template_id,
627 last_update_login,
628 org_id */
629 FROM pn_var_bkhd_defaults_all
630 WHERE line_default_id = p_line_default_id
631 AND break_type = 'NATURAL';
632
633 CURSOR c_bkdets_natural (p_head_default_id NUMBER)
634 IS
635 SELECT *
636 /*bkdt_default_id,
637 bkdt_start_date,
638 bkdt_end_date,
639 period_bkpt_vol_start,
640 period_bkpt_vol_end,
641 group_bkpt_vol_start,
642 group_bkpt_vol_end,
643 bkpt_rate,
644 org_id,
645 annual_basis_amount --03-NOV-2003 */
646 FROM pn_var_bkdt_defaults_all
647 WHERE bkhd_default_id = p_head_default_id;
648
649 CURSOR header_defaults_cur(p_var_rent_id NUMBER)
650 IS
651 SELECT bkhd_default_id
652 FROM pn_var_bkhd_defaults_all
653 WHERE var_rent_id = p_var_rent_id;
654 --AND break_type = 'NATURAL';
655
656 BEGIN
657 --pnp_debug_pkg.enable_file_debug(l_pathname,l_filename);
658 PNP_DEBUG_PKG.log('PN_VAR_DEFAULTS_PKG.CREATE_DEFAULT_LINES (+)');
659 l_var_rent_id := x_var_rent_id;
660 PNP_DEBUG_PKG.log('Parameter : x_var_rent_id = '|| l_var_rent_id);
661 FOR c_period_rec IN c_periods
662 LOOP
663 PNP_DEBUG_PKG.log('Processing Period => '|| c_period_rec.period_id || ' ' ||
664 c_period_rec.start_date || ' '||
665 c_period_rec.end_date || ' ' ||
666 c_period_rec.reptg_freq_code);
667 l_reporting_periods := NVL(pn_var_rent_pkg.find_reporting_periods(
668 p_freq_code => c_period_rec.reptg_freq_code),1);
669
670 PNP_DEBUG_PKG.log( 'l_reporting_periods = '|| l_reporting_periods);
671 FOR c_line_rec IN c_lines (c_period_rec.start_date, c_period_rec.end_date)
672 LOOP
673 PNP_DEBUG_PKG.log('Processing line Default ID: ' || c_line_rec.line_default_id);
674 l_rowid := NULL;
675 l_line_item_id := NULL;
676 l_line_item_num := NULL;
677 l_dummy := NULL;
678 l_sales_type_code := c_line_rec.sales_type_code;
679 l_item_category_code := c_line_rec.item_category_code;
680
681 IF l_item_category_code IS NOT NULL AND l_sales_type_code IS NOT NULL THEN
682 FOR Line_exists_rec in line_exists_cur (c_period_rec.period_id,
683 c_line_rec.item_category_code,
684 c_line_rec.sales_type_code)
685 LOOP
686 l_dummy := line_exists_rec.line_exists;
687 END LOOP;
688 ELSIF c_line_rec.item_category_code IS NULL AND c_line_rec.sales_type_code IS NOT NULL THEN
689 FOR Line_exists_rec in line_exists_sales_cur (c_period_rec.period_id,
690 c_line_rec.sales_type_code)
691 LOOP
692 l_dummy := line_exists_rec.line_exists;
693 END LOOP;
694 ELSIF c_line_rec.sales_type_code IS NULL AND c_line_rec.item_category_code IS NOT NULL THEN
695 FOR Line_exists_rec in line_exists_item_cur (c_period_rec.period_id,
696 c_line_rec.item_category_code)
697 LOOP
698 l_dummy := line_exists_rec.line_exists;
699 END LOOP;
700 END IF;
701
702
703 IF l_dummy IS NULL THEN
704 PN_VAR_LINES_PKG.INSERT_ROW(l_rowid,
705 l_line_item_id,
706 l_line_item_num,
707 c_period_rec.period_id,
708 c_line_rec.sales_type_code,
709 c_line_rec.item_category_code,
710 l_null,
711 c_line_rec.ATTRIBUTE_CATEGORY,
712 c_line_rec.ATTRIBUTE1,
713 c_line_rec.ATTRIBUTE2,
714 c_line_rec.ATTRIBUTE3,
715 c_line_rec.ATTRIBUTE4,
716 c_line_rec.ATTRIBUTE5,
717 c_line_rec.ATTRIBUTE6,
718 c_line_rec.ATTRIBUTE7,
719 c_line_rec.ATTRIBUTE8,
720 c_line_rec.ATTRIBUTE9,
721 c_line_rec.ATTRIBUTE10,
722 c_line_rec.ATTRIBUTE11,
723 c_line_rec.ATTRIBUTE12,
724 c_line_rec.ATTRIBUTE13,
725 c_line_rec.ATTRIBUTE14,
726 c_line_rec.ATTRIBUTE15,
727 c_line_rec.org_id,
728 sysdate,
729 NVL(fnd_profile.value('USER_ID'),0),
730 sysdate,
731 NVL(fnd_profile.value('USER_ID'),0),
732 NVL(fnd_profile.value('USER_ID'),0),
733 c_line_rec.line_template_id,
734 c_line_rec.agreement_template_id,
735 c_line_rec.line_default_id,
736 l_var_rent_id);
737
738 /*
739 UPDATE pn_var_line_defaults_all
740 SET processed_flag = 1
741 WHERE var_rent_id = l_var_rent_id
742 AND line_default_id = c_line_rec.line_default_id
743 AND line_start_date >= c_period_rec.start_date
744 AND line_end_date <= c_period_rec.end_date;
745 */
746 ELSE
747 SELECT line_item_id
748 INTO l_line_item_id
749 FROM pn_var_lines_all
750 WHERE line_default_id = c_line_rec.line_default_id
751 AND period_id = c_period_rec.period_id
752 AND var_rent_id = l_var_rent_id
753 AND ROWNUM = 1;
754 END IF;
755
756 --PNP_DEBUG_PKG.log('X_CREATE_FLAG:'|| X_CREATE_FLAG);
757 --PNP_DEBUG_PKG.log('Line Item ID:'|| l_line_item_id);
758 IF X_CREATE_FLAG = 'N' THEN
759 FOR c_head_rec IN c_bkheads (c_line_rec.line_default_id)
760 LOOP
761 PNP_DEBUG_PKG.log('Processing header for Line Default =' || c_line_rec.line_default_id);
762 l_rowid := NULL;
763 l_bkpt_header_id := NULL;
764 l_bkhd_start_date := NULL;
765 l_bkhd_end_Date := NULL;
766
767 PN_VAR_BKPTS_HEAD_PKG.INSERT_ROW(x_rowid => l_rowid,
768 x_bkpt_header_id => l_bkpt_header_id,
769 x_line_item_id => l_line_item_id,
770 x_period_id => c_period_rec.period_id,
771 x_break_type => c_head_rec.break_type,
772 x_base_rent_type => c_head_rec.base_rent_type,
773 x_natural_break_rate => c_head_rec.natural_break_rate,
774 x_base_rent => c_head_rec.base_rent,
775 x_breakpoint_type => c_head_rec.breakpoint_type,
776 x_bkhd_default_id => c_head_rec.bkhd_default_id,
777 x_bkhd_start_date => null,
778 x_bkhd_end_date => null,
779 x_var_rent_id => l_var_rent_id,
780 x_attribute_category => c_head_rec.ATTRIBUTE_CATEGORY,
781 x_attribute1 => c_head_rec.ATTRIBUTE1,
782 x_attribute2 => c_head_rec.ATTRIBUTE2,
783 x_attribute3 => c_head_rec.ATTRIBUTE3,
784 x_attribute4 => c_head_rec.ATTRIBUTE4,
785 x_attribute5 => c_head_rec.ATTRIBUTE5,
786 x_attribute6 => c_head_rec.ATTRIBUTE6,
787 x_attribute7 => c_head_rec.ATTRIBUTE7,
788 x_attribute8 => c_head_rec.ATTRIBUTE8,
789 x_attribute9 => c_head_rec.ATTRIBUTE9,
790 x_attribute10 => c_head_rec.ATTRIBUTE10,
791 x_attribute11 => c_head_rec.ATTRIBUTE11,
792 x_attribute12 => c_head_rec.ATTRIBUTE12,
793 x_attribute13 => c_head_rec.ATTRIBUTE13,
794 x_attribute14 => c_head_rec.ATTRIBUTE14,
795 x_attribute15 => c_head_rec.ATTRIBUTE15,
796 x_org_id => c_head_rec.org_id,
797 x_creation_date => sysdate,
798 x_created_by => NVL(fnd_profile.value('USER_ID'),0),
799 x_last_update_date => sysdate,
800 x_last_updated_by => NVL(fnd_profile.value('USER_ID'),0),
801 x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0),
802 x_bkpt_update_flag => c_head_rec.bkpt_update_flag);
803
804 FOR c_det_rec IN c_bkdets (c_head_rec.bkhd_default_id)
805 LOOP
806 PNP_DEBUG_PKG.log('Processing detail for Header Default = '|| c_head_rec.bkhd_default_id);
807
808 IF c_det_rec.bkdt_start_date > c_period_rec.start_date THEN
809 l_period_start := c_det_rec.bkdt_start_date;
810 ELSE
811 l_period_start := c_period_rec.start_date;
812 END IF;
813
814 IF c_det_rec.bkdt_end_date < c_period_rec.end_date THEN
815 l_period_end := c_det_rec.bkdt_end_date;
816 ELSE
817 l_period_end := c_period_rec.end_date;
818 END IF;
819
820 IF nvl(c_head_rec.breakpoint_level,'PERIOD') = 'PERIOD' THEN
821 l_period_bkpt_vol_start := NVL(c_det_rec.period_bkpt_vol_start,0);
822 l_period_bkpt_vol_end := NVL(c_det_rec.period_bkpt_vol_end,0);
823 IF l_period_bkpt_vol_start <> 0 THEN
824 l_group_bkpt_vol_start := round((l_period_bkpt_vol_start/l_reporting_periods),2);
825 ELSE
826 l_group_bkpt_vol_start := 0;
827 END IF;
828 IF l_period_bkpt_vol_end <> 0 THEN
829 l_group_bkpt_vol_end := round((l_period_bkpt_vol_end/l_reporting_periods),2);
830 ELSE
831 l_group_bkpt_vol_end := 0;
832 END IF;
833 ELSE
834 l_group_bkpt_vol_start := NVL(c_det_rec.group_bkpt_vol_start,0);
835 l_group_bkpt_vol_end := NVL(c_det_rec.group_bkpt_vol_end,0);
836 IF l_group_bkpt_vol_start <> 0 THEN
837 l_period_bkpt_vol_start := round((l_group_bkpt_vol_start*l_reporting_periods),2);
838 ELSE
839 l_period_bkpt_vol_start := 0;
840 END IF;
841 IF l_group_bkpt_vol_end <> 0 THEN
842 l_period_bkpt_vol_end := round((l_group_bkpt_vol_end*l_reporting_periods),2);
843 ELSE
844 l_period_bkpt_vol_end := 0;
845 END IF;
846
847 END IF;
848 IF l_period_end >= l_period_start THEN
849 l_rowid := NULL;
850 l_bkpt_detail_id := NULL;
851 l_bkpt_detail_num := NULL;
852
853 PN_VAR_BKPTS_DET_PKG.INSERT_ROW(
854 l_rowid,
855 l_bkpt_detail_id,
856 l_bkpt_detail_num,
857 l_bkpt_header_id,
858 l_period_start,
859 l_period_end,
860 l_period_bkpt_vol_start,
861 l_period_bkpt_vol_end,
862 l_group_bkpt_vol_start,
863 l_group_bkpt_vol_end,
864 c_det_rec.bkpt_rate,
865 c_det_rec.bkdt_default_id,
866 l_var_rent_id,
867 l_null,
868 c_det_rec.ATTRIBUTE_CATEGORY,
869 c_det_rec.ATTRIBUTE1,
870 c_det_rec.ATTRIBUTE2,
871 c_det_rec.ATTRIBUTE3,
872 c_det_rec.ATTRIBUTE4,
873 c_det_rec.ATTRIBUTE5,
874 c_det_rec.ATTRIBUTE6,
875 c_det_rec.ATTRIBUTE7,
876 c_det_rec.ATTRIBUTE8,
877 c_det_rec.ATTRIBUTE9,
878 c_det_rec.ATTRIBUTE10,
879 c_det_rec.ATTRIBUTE11,
880 c_det_rec.ATTRIBUTE12,
881 c_det_rec.ATTRIBUTE13,
882 c_det_rec.ATTRIBUTE14,
883 c_det_rec.ATTRIBUTE15,
884 c_det_rec.org_id,
885 sysdate,
886 NVL(fnd_profile.value('USER_ID'),0),
887 sysdate,
888 NVL(fnd_profile.value('USER_ID'),0),
889 NVL(fnd_profile.value('USER_ID'),0),
890 c_det_rec.annual_basis_amount --03-NOV-2003
891 );
892
893 IF l_bkhd_start_date is NULL OR
894 l_period_start < l_bkhd_start_date THEN
895 l_bkhd_start_date := l_period_start;
896 END IF;
897
898 IF l_bkhd_end_date is NULL OR
899 l_period_end > l_bkhd_end_date THEN
900 l_bkhd_end_date := l_period_end;
901 END IF;
902
903 IF c_head_rec.break_type = 'NATURAL' THEN
904 UPDATE pn_var_bkdt_defaults_all
905 SET processed_flag = 1
906 WHERE var_rent_id = l_var_rent_id
907 AND bkdt_default_id = c_det_rec.bkdt_default_id;
908 END IF;
909 END IF;
910
911 END LOOP;
912
913 UPDATE pn_var_bkpts_head_all
914 SET bkhd_start_date = l_bkhd_start_date,
915 bkhd_end_date = l_bkhd_end_date
916 WHERE bkpt_header_id = l_bkpt_header_id;
917
918 END LOOP;
919
920 ELSE
921 FOR c_head_rec IN c_bkheads_natural (c_line_rec.line_default_id)
922 LOOP
923 PNP_DEBUG_PKG.log('Processing Natural header for Line Default =' || c_line_rec.line_default_id);
924 l_rowid := NULL;
925 l_bkpt_header_id := NULL;
926 l_bkhd_start_date := NULL;
927 l_bkhd_end_Date := NULL;
928
929 BEGIN
930 PN_VAR_BKPTS_HEAD_PKG.INSERT_ROW(x_rowid => l_rowid,
931 x_bkpt_header_id => l_bkpt_header_id,
932 x_line_item_id => l_line_item_id,
933 x_period_id => c_period_rec.period_id,
934 x_break_type => c_head_rec.break_type,
935 x_base_rent_type => c_head_rec.base_rent_type,
936 x_natural_break_rate => c_head_rec.natural_break_rate,
937 x_base_rent => c_head_rec.base_rent,
938 x_breakpoint_type => c_head_rec.breakpoint_type,
939 x_bkhd_default_id => c_head_rec.bkhd_default_id,
940 x_bkhd_start_date => null,
941 x_bkhd_end_date => null,
942 x_var_rent_id => l_var_rent_id,
943 x_attribute_category => c_head_rec.ATTRIBUTE_CATEGORY,
944 x_attribute1 => c_head_rec.ATTRIBUTE1,
945 x_attribute2 => c_head_rec.ATTRIBUTE2,
946 x_attribute3 => c_head_rec.ATTRIBUTE3,
947 x_attribute4 => c_head_rec.ATTRIBUTE4,
948 x_attribute5 => c_head_rec.ATTRIBUTE5,
949 x_attribute6 => c_head_rec.ATTRIBUTE6,
950 x_attribute7 => c_head_rec.ATTRIBUTE7,
951 x_attribute8 => c_head_rec.ATTRIBUTE8,
952 x_attribute9 => c_head_rec.ATTRIBUTE9,
953 x_attribute10 => c_head_rec.ATTRIBUTE10,
954 x_attribute11 => c_head_rec.ATTRIBUTE11,
955 x_attribute12 => c_head_rec.ATTRIBUTE12,
956 x_attribute13 => c_head_rec.ATTRIBUTE13,
957 x_attribute14 => c_head_rec.ATTRIBUTE14,
958 x_attribute15 => c_head_rec.ATTRIBUTE15,
959 x_org_id => c_head_rec.org_id,
960 x_creation_date => sysdate,
961 x_created_by => NVL(fnd_profile.value('USER_ID'),0),
962 x_last_update_date => sysdate,
963 x_last_updated_by => NVL(fnd_profile.value('USER_ID'),0),
964 x_last_update_login => NVL(fnd_profile.value('LOGIN_ID'),0),
965 x_bkpt_update_flag => c_head_rec.bkpt_update_flag);
966 EXCEPTION
967 WHEN OTHERS THEN
968 NULL;
969 END;
970
971 FOR c_det_rec IN c_bkdets_natural (c_head_rec.bkhd_default_id)
972 LOOP
973 PNP_DEBUG_PKG.log('Processing Natural detail for Header Default = '|| c_head_rec.bkhd_default_id);
974 IF c_det_rec.bkdt_start_date > c_period_rec.start_date THEN
975 l_period_start := c_det_rec.bkdt_start_date;
976 ELSE
977 l_period_start := c_period_rec.start_date;
978 END IF;
979
980 IF c_det_rec.bkdt_end_date < c_period_rec.end_date THEN
981 l_period_end := c_det_rec.bkdt_end_date;
982 ELSE
983 l_period_end := c_period_rec.end_date;
984 END IF;
985
986 IF nvl(c_head_rec.breakpoint_level,'PERIOD') = 'PERIOD' THEN
987 l_period_bkpt_vol_start := NVL(c_det_rec.period_bkpt_vol_start,0);
988 l_period_bkpt_vol_end := NVL(c_det_rec.period_bkpt_vol_end,0);
989 IF l_period_bkpt_vol_start <> 0 THEN
990 l_group_bkpt_vol_start := round((l_period_bkpt_vol_start/l_reporting_periods),2);
991 ELSE
992 l_group_bkpt_vol_start := 0;
993 END IF;
994 IF l_period_bkpt_vol_end <> 0 THEN
995 l_group_bkpt_vol_end := round((l_period_bkpt_vol_end/l_reporting_periods),2);
996 ELSE
997 l_group_bkpt_vol_end := 0;
998 END IF;
999 ELSE
1000 l_group_bkpt_vol_start := NVL(c_det_rec.group_bkpt_vol_start,0);
1001 l_group_bkpt_vol_end := NVL(c_det_rec.group_bkpt_vol_end,0);
1002 IF l_group_bkpt_vol_start <> 0 THEN
1003 l_period_bkpt_vol_start := round((l_group_bkpt_vol_start*l_reporting_periods),2);
1004 ELSE
1005 l_period_bkpt_vol_start := 0;
1006 END IF;
1007 IF l_group_bkpt_vol_end <> 0 THEN
1008 l_period_bkpt_vol_end := round((l_group_bkpt_vol_end*l_reporting_periods),2);
1009 ELSE
1010 l_period_bkpt_vol_end := 0;
1011 END IF;
1012 END IF;
1013
1014 IF l_period_end >= l_period_start THEN
1015 l_rowid := NULL;
1016 l_bkpt_detail_id := NULL;
1017 l_bkpt_detail_num := NULL;
1018
1019 BEGIN
1020 PN_VAR_BKPTS_DET_PKG.INSERT_ROW(X_ROWID => l_rowid,
1021 X_BKPT_DETAIL_ID => l_bkpt_detail_id,
1022 X_BKPT_DETAIL_NUM => l_bkpt_detail_num,
1023 X_BKPT_HEADER_ID => l_bkpt_header_id,
1024 X_BKPT_START_DATE => l_period_start,
1025 X_BKPT_END_DATE => l_period_end,
1026 X_PERIOD_BKPT_VOL_START => l_period_bkpt_vol_start,
1027 X_PERIOD_BKPT_VOL_END => l_period_bkpt_vol_end,
1028 X_GROUP_BKPT_VOL_START => l_group_bkpt_vol_start,
1029 X_GROUP_BKPT_VOL_END => l_group_bkpt_vol_end,
1030 X_BKPT_RATE => c_det_rec.bkpt_rate,
1031 X_BKDT_DEFAULT_ID => c_det_rec.bkdt_default_id,
1032 X_VAR_RENT_ID => l_var_rent_id,
1033 X_COMMENTS => l_null,
1034 X_ATTRIBUTE_CATEGORY => c_det_rec.ATTRIBUTE_CATEGORY,
1035 X_ATTRIBUTE1 => c_det_rec.ATTRIBUTE1,
1036 X_ATTRIBUTE2 => c_det_rec.ATTRIBUTE2,
1037 X_ATTRIBUTE3 => c_det_rec.ATTRIBUTE3,
1038 X_ATTRIBUTE4 => c_det_rec.ATTRIBUTE4,
1039 X_ATTRIBUTE5 => c_det_rec.ATTRIBUTE5,
1040 X_ATTRIBUTE6 => c_det_rec.ATTRIBUTE6,
1041 X_ATTRIBUTE7 => c_det_rec.ATTRIBUTE7,
1042 X_ATTRIBUTE8 => c_det_rec.ATTRIBUTE8,
1043 X_ATTRIBUTE9 => c_det_rec.ATTRIBUTE9,
1044 X_ATTRIBUTE10 => c_det_rec.ATTRIBUTE10,
1045 X_ATTRIBUTE11 => c_det_rec.ATTRIBUTE11,
1046 X_ATTRIBUTE12 => c_det_rec.ATTRIBUTE12,
1047 X_ATTRIBUTE13 => c_det_rec.ATTRIBUTE13,
1048 X_ATTRIBUTE14 => c_det_rec.ATTRIBUTE14,
1049 X_ATTRIBUTE15 => c_det_rec.ATTRIBUTE15,
1050 X_ORG_ID => c_det_rec.org_id,
1051 X_CREATION_DATE => sysdate,
1052 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
1053 X_LAST_UPDATE_DATE => sysdate,
1054 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
1055 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),0),
1056 X_ANNUAL_BASIS_AMOUNT => c_det_rec.annual_basis_amount
1057 );
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 NULL;
1061 END;
1062
1063 IF l_bkhd_start_date is NULL OR l_period_start < l_bkhd_start_date THEN
1064 l_bkhd_start_date := l_period_start;
1065 END IF;
1066
1067 IF l_bkhd_end_date is NULL OR l_period_end > l_bkhd_end_date THEN
1068 l_bkhd_end_date := l_period_end;
1069 END IF;
1070
1071 UPDATE pn_var_bkdt_defaults_all
1072 SET processed_flag = 1
1073 WHERE var_rent_id = l_var_rent_id
1074 AND bkdt_default_id = c_det_rec.bkdt_default_id;
1075 END IF;
1076
1077 END LOOP;
1078
1079 UPDATE pn_var_bkpts_head_all
1080 SET bkhd_start_date = l_bkhd_start_date,
1081 bkhd_end_date = l_bkhd_end_date
1082 WHERE bkpt_header_id = l_bkpt_header_id;
1083 END LOOP;
1084 END IF; --X_CREATE_FLAG
1085
1086 END LOOP;
1087 END LOOP;
1088
1089 UPDATE pn_var_bkhd_defaults_all
1090 SET bkpt_update_flag = 'N'
1091 WHERE var_rent_id = x_var_rent_id;
1092
1093 --For Artificial, Update only the ones which made it to pn_var_bkpts_head_all
1094 /* DBMS_OUTPUT.PUT_LINE(' Step1'); */
1095 UPDATE pn_var_bkdt_defaults_all
1096 SET processed_flag = 1
1097 WHERE var_rent_id = l_var_rent_id
1098 AND bkhd_default_id IN (SELECT a.bkhd_default_id
1099 FROM pn_var_bkhd_defaults_all a,
1100 pn_var_bkpts_head_all b
1101 WHERE a.var_rent_id = b.var_rent_id
1102 AND a.var_rent_id = l_var_rent_id
1103 AND a.bkhd_default_id = b.bkhd_default_id
1104 AND a.break_type = b.break_type
1105 AND a.break_type <> 'NATURAL');
1106
1107 /* DBMS_OUTPUT.PUT_LINE(' Step2');*/
1108 FOR i IN header_defaults_cur(l_var_rent_id)
1109 LOOP
1110 --Set the header flag to 1 if all detail records are processed
1111 l_cnt := 0;
1112 SELECT COUNT(*)
1113 INTO l_cnt
1114 FROM pn_var_bkdt_defaults_all
1115 WHERE bkhd_default_id = i.bkhd_default_id
1116 AND NVL(processed_flag,0) <> 1;
1117 IF l_cnt = 0 THEN
1118 UPDATE pn_var_bkhd_defaults_all
1119 SET processed_flag = 1
1120 WHERE var_rent_id = l_var_rent_id
1121 AND bkhd_default_id = i.bkhd_default_id;
1122 /* DBMS_OUTPUT.PUT_LINE(' Step3'); */
1123 ELSE
1124 UPDATE pn_var_bkhd_defaults_all
1125 SET processed_flag = 0
1126 WHERE var_rent_id = l_var_rent_id
1127 AND bkhd_default_id = i.bkhd_default_id;
1128 /* DBMS_OUTPUT.PUT_LINE(' Step4'); */
1129 END IF;
1130 END LOOP;
1131
1132 /* Need to be revisited */
1133 DELETE FROM pn_var_bkpts_head_all
1134 WHERE var_rent_id = l_var_rent_id
1135 AND bkhd_start_date IS NULL
1136 AND bkhd_end_date IS NULL;
1137 --PNP_DEBUG_PKG.disable_file_debug;
1138 PNP_DEBUG_PKG.log('PN_VAR_DEFAULTS_PKG.CREATE_DEFAULT_LINES (-)');
1139
1140 end create_default_lines;
1141
1142 /*===========================================================================+
1143 | PROCEDURE DELETE_DEFAULT_LINES
1144 |
1145 |
1146 | DESCRIPTION
1147 | Delete records from the PN_VAR_LINE_DEFAULTS_ALL tables.
1148 |
1149 | SCOPE - PUBLIC
1150 |
1151 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1152 |
1153 | ARGUMENTS : IN:
1154 | X_VAR_RENT_ID
1155 |
1156 | OUT:
1157 |
1158 | RETURNS : None
1159 |
1160 |
1161 | MODIFICATION HISTORY
1162 |
1163 | 13-MAR-2003 Gary Olson o Created
1164 | 16-FEB-2004 Srini Vijayareddy o Support for multiple default header
1165 |
1166 +===========================================================================*/
1167
1168 procedure delete_default_lines (X_VAR_RENT_ID IN NUMBER,
1169 x_bkhd_default_id IN NUMBER DEFAULT NULL,
1170 x_bkpt_header_id IN NUMBER DEFAULT NULL) IS
1171 begin
1172
1173 IF x_bkhd_default_id IS NULL AND x_bkpt_header_id IS NULL THEN
1174 DELETE FROM pn_var_bkpts_det_all
1175 WHERE var_rent_id = X_VAR_RENT_ID;
1176
1177 DELETE FROM pn_var_bkpts_head_all
1178 WHERE var_rent_id = X_VAR_RENT_ID;
1179
1180 /*DELETE FROM pn_var_vol_hist_all
1181 WHERE line_item_id IN (SELECT line_item_id
1182 FROM pn_var_lines_all
1183 WHERE var_rent_id = X_VAR_RENT_ID);
1184
1185 DELETE FROM pn_var_lines_all
1186 WHERE var_rent_id = X_VAR_RENT_ID;
1187
1188 DELETE FROM pn_var_transactions_all
1189 WHERE var_rent_id = X_VAR_RENT_ID;*/
1190
1191 --COMMIT;
1192
1193 UPDATE pn_var_line_defaults_all
1194 SET processed_flag = 0
1195 WHERE var_rent_id = X_VAR_RENT_ID;
1196
1197 UPDATE pn_var_bkhd_defaults_all
1198 SET processed_flag = 0
1199 WHERE var_rent_id = X_VAR_RENT_ID;
1200
1201 UPDATE pn_var_bkdt_defaults_all
1202 SET processed_flag = 0
1203 WHERE var_rent_id = X_VAR_RENT_ID;
1204
1205 --COMMIT;
1206 ELSE
1207 IF x_bkhd_default_id IS NOT NULL THEN
1208 /*DELETE FROM pn_var_transactions_all
1209 WHERE var_rent_id = X_VAR_RENT_ID
1210 AND bkpt_detail_id IN (SELECT det.bkpt_detail_id
1211 FROM pn_var_bkpts_det_all det,
1212 pn_var_bkpts_head_all head
1213 WHERE det.var_rent_id = X_VAR_RENT_ID
1214 AND det.var_rent_id = head.var_rent_id
1215 AND det.bkpt_header_id = head.bkpt_header_id
1216 AND head.bkhd_default_id = x_bkhd_default_id);*/
1217
1218 /* commented by parag
1219 DELETE FROM pn_var_vol_hist_all
1220 --WHERE vol_hist_status_code <> 'APPROVED'
1221 WHERE actual_exp_code = 'N'
1222 AND line_item_id IN (SELECT line_item_id
1223 FROM pn_var_bkpts_head_all
1224 WHERE var_rent_id = X_VAR_RENT_ID
1225 AND bkhd_default_id = x_bkhd_default_id)
1226 AND line_item_id NOT IN (SELECT line_item_id
1227 FROM pn_var_bkpts_head_all
1228 WHERE var_rent_id = X_VAR_RENT_ID
1229 AND bkhd_default_id <> x_bkhd_default_id); */
1230
1231 /* commented by parag
1232 DELETE FROM pn_var_lines_all
1233 WHERE var_rent_id = X_VAR_RENT_ID
1234 AND line_item_id IN (SELECT line_item_id
1235 FROM pn_var_bkpts_head_all
1236 WHERE bkhd_default_id = x_bkhd_default_id)
1237 AND line_item_id NOT IN (SELECT line_item_id
1238 FROM pn_var_bkpts_head_all
1239 WHERE var_rent_id = X_VAR_RENT_ID
1240 AND bkhd_default_id <> x_bkhd_default_id); */
1241
1242 DELETE FROM pn_var_bkpts_det_all
1243 WHERE var_rent_id = X_VAR_RENT_ID
1244 AND bkpt_header_id IN (SELECT bkpt_header_id
1245 FROM pn_var_bkpts_head_all
1246 WHERE bkhd_default_id = x_bkhd_default_id);
1247
1248 DELETE FROM pn_var_bkpts_head_all
1249 WHERE var_rent_id = X_VAR_RENT_ID
1250 AND bkhd_default_id = x_bkhd_default_id;
1251 -- COMMIT;
1252
1253 UPDATE pn_var_line_defaults_all
1254 SET processed_flag = 0
1255 WHERE var_rent_id = X_VAR_RENT_ID;
1256
1257 UPDATE pn_var_bkhd_defaults_all
1258 SET processed_flag = 0
1259 WHERE var_rent_id = X_VAR_RENT_ID
1260 AND bkhd_default_id = x_bkhd_default_id;
1261
1262 UPDATE pn_var_bkdt_defaults_all
1263 SET processed_flag = 0
1264 WHERE var_rent_id = X_VAR_RENT_ID
1265 AND bkhd_default_id = x_bkhd_default_id;
1266 --COMMIT;
1267 ELSE
1268 /*DELETE FROM pn_var_transactions_all
1269 WHERE var_rent_id = X_VAR_RENT_ID
1270 AND bkpt_detail_id IN (SELECT bkpt_detail_id
1271 FROM pn_var_bkpts_det_all
1272 WHERE var_rent_id = X_VAR_RENT_ID
1273 AND bkpt_header_id = x_bkpt_header_id);*/
1274
1275 /* DELETE FROM pn_var_vol_hist_all
1276 --WHERE vol_hist_status_code <> 'APPROVED'
1277 WHERE actual_exp_code = 'N'
1278 AND line_item_id IN (SELECT line_item_id
1279 FROM pn_var_bkpts_head_all
1280 WHERE var_rent_id = X_VAR_RENT_ID
1281 AND bkpt_header_id = x_bkpt_header_id)
1282 AND line_item_id NOT IN (SELECT line_item_id
1283 FROM pn_var_bkpts_head_all
1284 WHERE var_rent_id = X_VAR_RENT_ID
1285 AND bkpt_header_id <> x_bkpt_header_id);
1286
1287 DELETE FROM pn_var_lines_all
1288 WHERE var_rent_id = X_VAR_RENT_ID
1289 AND line_item_id IN (SELECT line_item_id
1290 FROM pn_var_bkpts_head_all
1291 WHERE bkpt_header_id = x_bkpt_header_id)
1292 AND line_item_id NOT IN (SELECT line_item_id
1293 FROM pn_var_bkpts_head_all
1294 WHERE var_rent_id = X_VAR_RENT_ID
1295 AND bkpt_header_id <> x_bkpt_header_id);*/
1296
1297 DELETE FROM pn_var_bkpts_det_all
1298 WHERE var_rent_id = X_VAR_RENT_ID
1299 AND bkpt_header_id = x_bkpt_header_id;
1300
1301
1302 DELETE FROM pn_var_bkpts_head_all
1303 WHERE var_rent_id = X_VAR_RENT_ID
1304 AND bkpt_header_id = x_bkpt_header_id;
1305
1306 --COMMIT;
1307 END IF;
1308 END IF;
1309
1310 end delete_default_lines;
1311
1312 /*===========================================================================+
1313 | PROCEDURE RESET_DEFAULT_LINES
1314 |
1315 |
1316 | DESCRIPTION
1317 | Delete records from the PN_VAR_LINE_DEFAULTS_ALL tables.
1318 |
1319 | SCOPE - PUBLIC
1320 |
1321 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1322 |
1323 | ARGUMENTS : IN:
1324 | X_LINE_DEFAULT_ID
1325 |
1326 | OUT:
1327 |
1328 | RETURNS : None
1329 |
1330 |
1331 | MODIFICATION HISTORY
1332 |
1333 | 13-MAR-2003 Gary Olson o Created
1334 |
1335 +===========================================================================*/
1336
1337 procedure reset_default_lines (
1338 X_BKHD_DEFAULT_ID in NUMBER
1339 ) IS
1340
1341 begin
1342
1343 delete from pn_var_bkdt_defaults_all
1344 where bkhd_default_id = X_BKHD_DEFAULT_ID;
1345
1346 --commit;
1347
1348 end reset_default_lines;
1349
1350 /*===========================================================================+
1351 | PROCEDURE DELETE_TRANSACTIONS
1352 |
1353 |
1354 | DESCRIPTION
1355 | Delete records from the PN_VAR_TRANSACTIONS_ALL tables.
1356 |
1357 | SCOPE - PUBLIC
1358 |
1359 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1360 |
1361 | ARGUMENTS : IN:
1362 | X_VAR_RENT_ID
1363 |
1364 | OUT:
1365 |
1366 | RETURNS : None
1367 |
1368 |
1369 | MODIFICATION HISTORY
1370 |
1371 | 13-MAR-2003 Gary Olson o Created
1372 | 16-FEB-2004 Srini Vijayareddy o Support for multiple default header
1373 |
1374 +===========================================================================*/
1375
1376 procedure delete_transactions ( X_VAR_RENT_ID in NUMBER,
1377 x_bkhd_default_id IN NUMBER DEFAULT NULL,
1378 x_bkpt_header_id IN NUMBER DEFAULT NULL) IS
1379 begin
1380
1381 /*IF x_bkhd_default_id IS NULL AND x_bkpt_header_id IS NULL THEN
1382 delete from pn_var_transactions_all
1383 where var_rent_id = X_VAR_RENT_ID;
1384 ELSE
1385 IF x_bkhd_default_id IS NOT NULL THEN
1386 DELETE FROM pn_var_transactions_all
1387 WHERE var_rent_id = X_VAR_RENT_ID
1388 AND bkpt_detail_id IN (SELECT det.bkpt_detail_id
1389 FROM pn_var_bkpts_det_all det,
1390 pn_var_bkpts_head_all head
1391 WHERE det.var_rent_id = X_VAR_RENT_ID
1392 AND det.var_rent_id = head.var_rent_id
1393 AND det.bkpt_header_id = head.bkpt_header_id
1394 AND head.bkhd_default_id = x_bkhd_default_id);
1395 ELSE
1396 DELETE FROM pn_var_transactions_all
1397 WHERE var_rent_id = X_VAR_RENT_ID
1398 AND bkpt_detail_id IN (SELECT bkpt_detail_id
1399 FROM pn_var_bkpts_det_all
1400 WHERE var_rent_id = X_VAR_RENT_ID
1401 AND bkpt_header_id = x_bkpt_header_id);
1402 END IF;
1403 END IF;*/
1404 --COMMIT;
1405 NULL;
1406
1407 end delete_transactions;
1408
1409 /*===========================================================================+
1410 | FUNCTION
1411 | CALCULATE_PARTIAL_FIRST_YEAR
1412 |
1413 | DESCRIPTION
1414 | Calculates the partial first year rent due
1415 |
1416 | SCOPE - PUBLIC
1417 |
1418 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1419 |
1420 | ARGUMENTS : IN:
1421 | p_var_rent_id
1422 |
1423 | OUT:
1424 |
1425 | RETURNS : None
1426 |
1427 |
1428 | MODIFICATION HISTORY
1429 |
1430 | 13-MAR-2003 Gary Olson o Created
1431 |
1432 +===========================================================================*/
1433
1434 FUNCTION calculate_partial_first_year (X_VAR_RENT_ID IN NUMBER)
1435
1436 RETURN NUMBER IS
1437
1438 cursor csr_check_no_days (p_var_rent_id NUMBER) IS
1439 select commencement_date,
1440 TO_DATE('31-12-'||TO_CHAR(commencement_date,'YYYY'),'DD-MM-YYYY') year,
1441 TO_DATE(TO_CHAR(commencement_date,'DD-MM-')||
1442 TO_CHAR(TO_NUMBER(TO_CHAR(commencement_date,'YYYY'))+1), 'DD-MM-YYYY')-1 end_date
1443 from pn_var_rents_all
1444 where var_rent_id = p_var_rent_id;
1445
1446 /*cursor csr_group_sales (p_var_rent_id NUMBER, p_date DATE) IS
1447 select grp_date_id,bkpt_start_date, bkpt_end_date,
1448 no_of_group_days, prorated_group_sales
1449 from pn_var_transactions_all
1450 where var_rent_id = p_var_rent_id
1451 and prorated_group_sales is not null
1452 and grp_date_id in (select grp_date_id
1453 from pn_var_grp_dates_all
1454 where grp_start_date <= p_date );*/
1455
1456 cursor csr_get_gd (p_grp_date_id NUMBER) IS
1457 select grp_start_date, grp_end_date
1458 from pn_var_grp_dates_all
1459 where grp_date_id = p_grp_date_id;
1460
1461 l_start_date DATE;
1462 l_end_date DATE;
1463 l_grp_start_date DATE;
1464 l_grp_end_date DATE;
1465 l_reporting_date DATE;
1466 l_proration_factor NUMBER;
1467 l_sum_sales NUMBER := 0;
1468 l_check_days NUMBER := 0;
1469 l_proration_days NUMBER := 0;
1470
1471 BEGIN
1472
1473 PNP_DEBUG_PKG.debug ('PN_VAR_DEFAULTS_PKG.calculate_partial_first_year (+)');
1474
1475 /*open csr_check_no_days (X_VAR_RENT_ID);
1476 fetch csr_check_no_days into l_start_date, l_end_date, l_reporting_date;
1477 close csr_check_no_days;
1478
1479 l_proration_factor := ((l_end_date - l_start_date) + 1)/365;
1480 l_sum_sales := 0;
1481
1482 FOR c_amt IN csr_group_sales(X_VAR_RENT_ID, l_reporting_date) LOOP
1483 IF l_reporting_date >= c_amt.bkpt_end_date THEN
1484 l_sum_sales := l_sum_sales + c_amt.prorated_group_sales;
1485 ELSE
1486 open csr_get_gd(c_amt.grp_date_id);
1487 fetch csr_get_gd into l_grp_start_date, l_grp_end_date;
1488 close csr_get_gd;
1489 IF c_amt.bkpt_start_date > l_grp_start_date THEN
1490 l_start_date := c_amt.bkpt_start_date;
1491 ELSE
1492 l_start_date := l_grp_start_date;
1493 END IF;
1494 l_check_days := (c_amt.bkpt_end_date - l_start_date)+1;
1495 l_proration_days := l_check_days/c_amt.no_of_group_days;
1496 l_sum_sales := l_sum_sales + c_amt.prorated_group_sales*l_proration_days;
1497 END IF;
1498 END LOOP;
1499
1500 l_sum_sales := l_sum_sales/l_proration_factor;*/
1501 NULL;
1502
1503 RETURN l_sum_sales;
1504
1505 PNP_DEBUG_PKG.debug ('PN_VAR_DEFAULTS_PKG.calculate_partial_first_year (-)');
1506
1507 END calculate_partial_first_year;
1508
1509 /*===========================================================================+
1510 | FUNCTION
1511 | CALCULATE_DEFAULT_BASE_RENT
1512 |
1513 | DESCRIPTION
1514 | Calculates the base rent for a var_rent_id
1515 |
1516 | SCOPE - PUBLIC
1517 |
1518 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1519 |
1520 | ARGUMENTS : IN:
1521 | p_var_rent_id
1522 | p_base_rent_type
1523 |
1524 | OUT:
1525 |
1526 | RETURNS : None
1527 |
1528 | NOTES : Calculates the base rent for a given var_rent_id and base_rent_type
1529 |
1530 | MODIFICATION HISTORY
1531 |
1532 | 26-FEB-2003 Gary Olson o Created
1533 |
1534 +===========================================================================*/
1535
1536 FUNCTION CALCULATE_DEFAULT_BASE_RENT (p_var_rent_id NUMBER,
1537 p_base_rent_type VARCHAR2)
1538 RETURN NUMBER IS
1539
1540 l_base_rent NUMBER;
1541
1542 BEGIN
1543
1544 PNP_DEBUG_PKG.debug ('PN_VAR_DEFAULTS_PKG.CALCULATE_DEFAULT_BASE_RENT (+)');
1545
1546 IF (p_base_rent_type = 'ROLLING') THEN
1547
1548 SELECT sum(item.ACTUAL_AMOUNT)
1549 INTO l_base_rent
1550 FROM pn_payment_items_ALL item,
1551 pn_payment_terms_ALL term,
1552 pn_var_rents_ALL var,
1553 pn_payment_schedules sched
1554 WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
1555 AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID
1556 AND term.lease_id = var.lease_id
1557 AND var.var_rent_id = p_var_rent_id
1558 AND sched.SCHEDULE_DATE between term.start_date and term.end_date
1559 AND term.PAYMENT_PURPOSE_CODE = 'RENT'
1560 AND term.PAYMENT_TERM_TYPE_CODE = 'BASER'
1561 AND term.start_date >= var.commencement_date
1562 AND term.end_date <= var.termination_date
1563 AND item.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
1564 AND term.currency_code = var.currency_code --BUG#2452909
1565 ;
1566 ELSIF (p_base_rent_type = 'FIXED') THEN
1567
1568 SELECT sum(item.ACTUAL_AMOUNT)
1569 INTO l_base_rent
1570 FROM pn_payment_items_ALL item,
1571 pn_payment_terms_ALL term,
1572 pn_var_rents_ALL var,
1573 pn_payment_schedules sched
1574 WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
1575 AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID
1576 AND term.lease_id = var.lease_id
1577 AND var.var_rent_id = p_var_rent_id
1578 AND sched.SCHEDULE_DATE between term.start_date and term.end_date
1579 AND term.PAYMENT_PURPOSE_CODE = 'RENT'
1580 AND term.PAYMENT_TERM_TYPE_CODE = 'BASER'
1581 AND term.start_date >= var.commencement_date
1582 AND term.end_date <= var.termination_date
1583 AND item.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
1584 AND term.currency_code = var.currency_code
1585 ;
1586 END IF;
1587 RETURN l_base_rent;
1588
1589 EXCEPTION
1590
1591 WHEN OTHERS
1592 THEN
1593 RETURN NULL;
1594
1595 PNP_DEBUG_PKG.debug ('PN_VAR_DEFAULTS_PKG.CALCULATE_DEFAULT_BASE_RENT (-)');
1596
1597 END CALCULATE_DEFAULT_BASE_RENT;
1598
1599 /*===========================================================================+
1600 | FUNCTION
1601 | CALCULATE_PARTIAL_LAST_YEAR
1602 |
1603 | DESCRIPTION
1604 | Calculates the partial last year rent due
1605 |
1606 | SCOPE - PUBLIC
1607 |
1608 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1609 |
1610 | ARGUMENTS : IN:
1611 | p_var_rent_id
1612 |
1613 | OUT:
1614 |
1615 | RETURNS : None
1616 |
1617 |
1618 | MODIFICATION HISTORY
1619 |
1620 | 13-MAR-2003 Gary Olson o Created
1621 |
1622 +===========================================================================*/
1623
1624 FUNCTION calculate_partial_last_year (X_VAR_RENT_ID IN NUMBER)
1625
1626 RETURN NUMBER IS
1627
1628 cursor csr_check_no_days (p_var_rent_id NUMBER) IS
1629 select TO_DATE(TO_CHAR(termination_date,'DD-MM-')||
1630 TO_CHAR(TO_NUMBER(TO_CHAR(termination_date,'YYYY'))-1), 'DD-MM-YYYY')+1 start_date,
1631 TO_DATE('1-1-'||TO_CHAR(termination_date,'YYYY'),'DD-MM-YYYY') year,
1632 termination_date
1633 from pn_var_rents_all
1634 where var_rent_id = p_var_rent_id;
1635
1636 /*cursor csr_group_sales (p_var_rent_id NUMBER, p_date DATE) IS
1637 select grp_date_id,bkpt_start_date, bkpt_end_date,
1638 no_of_group_days, prorated_group_sales
1639 from pn_var_transactions_all
1640 where var_rent_id = p_var_rent_id
1641 and prorated_group_sales is not null
1642 and grp_date_id in (select grp_date_id
1643 from pn_var_grp_dates_all
1644 where grp_end_date >= p_date)
1645 order by bkpt_start_date desc;*/
1646
1647 cursor csr_get_gd (p_grp_date_id NUMBER) IS
1648 select grp_start_date, grp_end_date
1649 from pn_var_grp_dates_all
1650 where grp_date_id = p_grp_date_id;
1651
1652 l_start_date DATE;
1653 l_end_date DATE;
1654 l_grp_start_date DATE;
1655 l_grp_end_date DATE;
1656 l_reporting_date DATE;
1657 l_proration_factor NUMBER;
1658 l_sum_sales NUMBER := 0;
1659 l_check_days NUMBER := 0;
1660 l_proration_days NUMBER := 0;
1661
1662 BEGIN
1663
1664 PNP_DEBUG_PKG.debug ('PN_VAR_DEFAULTS_PKG.calculate_partial_last_year (+)');
1665
1666 /*open csr_check_no_days (X_VAR_RENT_ID);
1667 fetch csr_check_no_days into l_reporting_date, l_start_date, l_end_date;
1668 close csr_check_no_days;
1669
1670 l_proration_factor := ((l_end_date - l_start_date) + 1)/365;
1671 l_sum_sales := 0;
1672
1673 FOR c_amt IN csr_group_sales(X_VAR_RENT_ID, l_reporting_date) LOOP
1674 IF l_reporting_date <= c_amt.bkpt_start_date THEN
1675 l_sum_sales := l_sum_sales + c_amt.prorated_group_sales;
1676 ELSE
1677 open csr_get_gd(c_amt.grp_date_id);
1678 fetch csr_get_gd into l_grp_start_date, l_grp_end_date;
1679 close csr_get_gd;
1680 IF c_amt.bkpt_start_date > l_grp_start_date THEN
1681 l_start_date := c_amt.bkpt_start_date;
1682 ELSE
1683 l_start_date := l_grp_start_date;
1684 END IF;
1685 l_check_days := (c_amt.bkpt_end_date - l_start_date)+1;
1686 l_proration_days := l_check_days/c_amt.no_of_group_days;
1687 l_sum_sales := l_sum_sales + c_amt.prorated_group_sales*l_proration_days;
1688 END IF;
1689 END LOOP;
1690
1691 l_sum_sales := l_sum_sales/l_proration_factor;*/
1692 NULL;
1693
1694 RETURN l_sum_sales;
1695
1696 PNP_DEBUG_PKG.debug ('PN_VAR_DEFAULTS_PKG.calcuLate_partial_last_year (-)');
1697
1698 END calculate_partial_last_year;
1699
1700 FUNCTION find_if_line_defaults_exist (p_var_rent_id NUMBER)
1701 RETURN NUMBER IS
1702
1703 l_line_found NUMBER := 0;
1704
1705 BEGIN
1706
1707 SELECT 1
1708 INTO l_line_found
1709 FROM pn_var_line_defaults_all
1710 WHERE var_rent_id = p_var_rent_id
1711 AND rownum < 2;
1712
1713 RETURN l_line_found;
1714
1715 EXCEPTION
1716
1717 WHEN OTHERS THEN
1718 RETURN 0;
1719
1720 END find_if_line_defaults_exist;
1721
1722 FUNCTION find_if_constr_defaults_exist (p_var_rent_id NUMBER)
1723 RETURN NUMBER IS
1724
1725 l_constr_found NUMBER := 0;
1726
1727 BEGIN
1728
1729 SELECT 1
1730 INTO l_constr_found
1731 FROM pn_var_constr_defaults_all
1732 WHERE var_rent_id = p_var_rent_id;
1733
1734 RETURN l_constr_found;
1735
1736 EXCEPTION
1737
1738 WHEN OTHERS THEN
1739 RETURN 0;
1740
1741 END find_if_constr_defaults_exist;
1742
1743 PROCEDURE populate_agreement (
1744 X_VAR_RENT_ID in NUMBER,
1745 X_LINE_ID in NUMBER,
1746 X_PERIOD_ID in NUMBER,
1747 X_AGREEMENT_TEMPLATE_ID in NUMBER,
1748 X_LINE_TEMPLATE_ID in NUMBER,
1749 X_CURRENT_BLOCK in VARCHAR2
1750 ) IS
1751
1752 /*cursor c_bkhds IS
1753 select *
1754 from pn_var_bkpts_head_template_all
1755 where agreement_template_id = X_AGREEMENT_TEMPLATE_ID
1756 and line_template_id = X_LINE_TEMPLATE_ID;
1757
1758 cursor c_bkdts (p_bkpt_head_template_id NUMBER) IS
1759 select *
1760 from pn_var_bkpts_det_template_all
1761 where bkpt_head_template_id = p_bkpt_head_template_id;*/
1762
1763 l_rowid VARCHAR2(18) := NULL;
1764 l_itemId NUMBER := NULL;
1765 l_itemNum NUMBER := NULL;
1766 l_null VARCHAR2(4000) := NULL;
1767 l_nullid NUMBER := NULL;
1768 l_nulldate DATE := NULL;
1769 l_bkpt_header_id NUMBER := NULL;
1770
1771 BEGIN
1772
1773 /*IF X_CURRENT_BLOCK = ('LINE_ITEMS_BLK') THEN
1774
1775 FOR c_hd IN c_bkhds LOOP
1776 PN_VAR_BKPTS_HEAD_PKG.INSERT_ROW (
1777 X_ROWID => l_rowid,
1778 X_BKPT_HEADER_ID => l_bkpt_header_id,
1779 X_LINE_ITEM_ID => X_LINE_ID,
1780 X_PERIOD_ID => X_PERIOD_ID,
1781 X_BREAK_TYPE => c_hd.break_type,
1782 X_BASE_RENT_TYPE => c_hd.base_rent_type,
1783 X_NATURAL_BREAK_RATE => c_hd.natural_break_rate,
1784 X_BASE_RENT => c_hd.base_rent,
1785 X_BREAKPOINT_TYPE => c_hd.breakpoint_type,
1786 X_BKHD_DEFAULT_ID => l_nullid,
1787 X_BKHD_START_DATE => NULL,
1788 X_BKHD_END_DATE => NULL,
1789 X_VAR_RENT_ID => X_VAR_RENT_ID,
1790 X_ATTRIBUTE_CATEGORY => l_null,
1791 X_ATTRIBUTE1 => l_null,
1792 X_ATTRIBUTE2 => l_null,
1793 X_ATTRIBUTE3 => l_null,
1794 X_ATTRIBUTE4 => l_null,
1795 X_ATTRIBUTE5 => l_null,
1796 X_ATTRIBUTE6 => l_null,
1797 X_ATTRIBUTE7 => l_null,
1798 X_ATTRIBUTE8 => l_null,
1799 X_ATTRIBUTE9 => l_null,
1800 X_ATTRIBUTE10 => l_null,
1801 X_ATTRIBUTE11 => l_null,
1802 X_ATTRIBUTE12 => l_null,
1803 X_ATTRIBUTE13 => l_null,
1804 X_ATTRIBUTE14 => l_null,
1805 X_ATTRIBUTE15 => l_null,
1806 X_ORG_ID => c_hd.org_id,
1807 X_CREATION_DATE => sysdate,
1808 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
1809 X_LAST_UPDATE_DATE => sysdate,
1810 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
1811 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),0)
1812 );
1813
1814 COMMIT;
1815
1816 FOR c_det IN c_bkdts (c_hd.bkpt_head_template_id) LOOP
1817
1818 l_rowid := NULL;
1819 l_itemId := NULL;
1820 l_itemNum := NULL;
1821
1822 PN_VAR_BKPTS_DET_PKG.INSERT_ROW(
1823 l_rowid,
1824 l_nullid,
1825 l_itemNum,
1826 l_bkpt_header_id,
1827 l_nulldate,
1828 l_nulldate,
1829 c_det.period_bkpt_vol_start,
1830 c_det.period_bkpt_vol_end,
1831 c_det.group_bkpt_vol_start,
1832 c_det.group_bkpt_vol_end,
1833 c_det.bkpt_rate,
1834 l_nullid,
1835 X_VAR_RENT_ID,
1836 l_null,
1837 l_null,
1838 l_null,
1839 l_null,
1840 l_null,
1841 l_null,
1842 l_null,
1843 l_null,
1844 l_null,
1845 l_null,
1846 l_null,
1847 l_null,
1848 l_null,
1849 l_null,
1850 l_null,
1851 l_null,
1852 l_null,
1853 c_hd.org_id,
1854 sysdate,
1855 NVL(fnd_profile.value('USER_ID'),0),
1856 sysdate,
1857 NVL(fnd_profile.value('USER_ID'),0),
1858 NVL(fnd_profile.value('USER_ID'),0),
1859 NULL --03-NOV-2003
1860 );
1861 COMMIT;
1862
1863 END LOOP;
1864
1865 END LOOP;
1866
1867 ELSIF X_CURRENT_BLOCK = ('LINE_DEFAULTS_BLK') THEN
1868 FOR c_hd IN c_bkhds LOOP
1869 PN_VAR_BKHD_DEFAULTS_PKG.INSERT_ROW (
1870 l_rowid,
1871 l_bkpt_header_id,
1872 l_itemNum,
1873 X_LINE_ID,
1874 l_nullid,
1875 X_AGREEMENT_TEMPLATE_ID,
1876 l_nulldate,
1877 l_nulldate,
1878 c_hd.break_type,
1879 c_hd.base_rent_type,
1880 c_hd.natural_break_rate,
1881 c_hd.base_rent,
1882 c_hd.breakpoint_type,
1883 l_null,
1884 l_null,
1885 X_VAR_RENT_ID,
1886 sysdate,
1887 NVL(fnd_profile.value('USER_ID'),0),
1888 sysdate,
1889 NVL(fnd_profile.value('USER_ID'),0),
1890 NVL(fnd_profile.value('USER_ID'),0),
1891 c_hd.org_id,
1892 l_null,
1893 l_null,
1894 l_null,
1895 l_null,
1896 l_null,
1897 l_null,
1898 l_null,
1899 l_null,
1900 l_null,
1901 l_null,
1902 l_null,
1903 l_null,
1904 l_null,
1905 l_null,
1906 l_null,
1907 l_null
1908 );
1909 COMMIT;
1910
1911 FOR c_det IN c_bkdts(c_hd.bkpt_head_template_id) LOOP
1912
1913 l_rowid := NULL;
1914 l_itemId := NULL;
1915 l_itemNum := NULL;
1916
1917 PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW(
1918 l_rowid,
1919 l_itemId,
1920 l_itemNum,
1921 l_bkpt_header_id,
1922 l_nulldate,
1923 l_nulldate,
1924 c_det.period_bkpt_vol_start,
1925 c_det.period_bkpt_vol_end,
1926 c_det.group_bkpt_vol_start,
1927 c_det.group_bkpt_vol_end,
1928 c_det.bkpt_rate,
1929 l_null,
1930 X_VAR_RENT_ID,
1931 sysdate,
1932 NVL(fnd_profile.value('USER_ID'),0),
1933 sysdate,
1934 NVL(fnd_profile.value('USER_ID'),0),
1935 NVL(fnd_profile.value('USER_ID'),0),
1936 c_hd.org_id,
1937 NULL, --03-NOV-2003
1938 l_null,
1939 l_null,
1940 l_null,
1941 l_null,
1942 l_null,
1943 l_null,
1944 l_null,
1945 l_null,
1946 l_null,
1947 l_null,
1948 l_null,
1949 l_null,
1950 l_null,
1951 l_null,
1952 l_null,
1953 l_null);
1954 COMMIT;
1955
1956 END LOOP;
1957
1958 END LOOP;
1959
1960 END IF;
1961
1962 commit;*/
1963 NULL;
1964
1965 END populate_agreement;
1966
1967 PROCEDURE populate_default_dates (
1968 X_VAR_RENT_ID in NUMBER,
1969 X_BKHD_DEFAULT_ID in NUMBER,
1970 X_LINE_DEFAULT_ID in NUMBER
1971 )
1972 IS
1973
1974 begin
1975
1976 UPDATE pn_var_bkhd_defaults_all
1977 SET bkhd_start_date = (select min(bkdt_start_date)
1978 from pn_var_bkdt_defaults_all
1979 where var_rent_id = X_VAR_RENT_ID
1980 and bkhd_default_id = X_BKHD_DEFAULT_ID),
1981 bkhd_end_date = (select max(bkdt_end_date)
1982 from pn_var_bkdt_defaults_all
1983 where var_rent_id = X_VAR_RENT_ID
1984 and bkhd_default_id = X_BKHD_DEFAULT_ID)
1985 WHERE var_rent_id = X_VAR_RENT_ID
1986 AND bkhd_default_id = X_BKHD_DEFAULT_ID;
1987
1988 UPDATE pn_var_line_defaults_all
1989 SET line_start_date = (select min(bkhd_start_date)
1990 from pn_var_bkhd_defaults_all
1991 where var_rent_id = X_VAR_RENT_ID
1992 and line_default_id = X_LINE_DEFAULT_ID),
1993 line_end_date = (select max(bkhd_end_date)
1994 from pn_var_bkhd_defaults_all
1995 where var_rent_id = X_VAR_RENT_ID
1996 and line_default_id = X_LINE_DEFAULT_ID)
1997 WHERE var_rent_id = X_VAR_RENT_ID
1998 AND line_default_id = X_LINE_DEFAULT_ID;
1999
2000 commit;
2001
2002 end populate_default_dates;
2003
2004 /*=============================================================================+
2005 | PROCEDURE CREATE_SETUP_DATA
2006 |
2007 |
2008 | DESCRIPTION
2009 | Creates records in the PN_VAR_LINES_ALL, PN_VAR_BPKT_HEAD_ALL and
2010 | PN_VAR_BKPT_DET_ALL tables from DEFAULTS tables
2011 |
2012 | SCOPE - PUBLIC
2013 |
2014 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2015 |
2016 | ARGUMENTS : IN: X_VAR_RENT_ID
2017 |
2018 | OUT:
2019 |
2020 | RETURNS : None
2021 |
2022 |
2023 | MODIFICATION HISTORY
2024 |
2025 | 07-JUL-06 PIkhar o Created
2026 | 28-FEB-07 PIkhar o Bug 5904246. Added
2027 | pn_var_defaults_pkg.delete_default_lines
2028 |
2029 +=============================================================================*/
2030 PROCEDURE CREATE_SETUP_DATA (X_VAR_RENT_ID IN NUMBER) IS
2031
2032
2033 l_var_rent_id NUMBER := NULL;
2034 l_rowId VARCHAR2(18) := NULL;
2035 l_line_item_id NUMBER := NULL;
2036 l_line_item_num NUMBER := NULL;
2037 l_dummy VARCHAR2(2) := NULL;
2038 l_bkpt_header_id NUMBER := NULL;
2039 l_bkhd_start_date DATE := NULL;
2040 l_bkhd_end_Date DATE := NULL;
2041 l_bkpt_detail_id NUMBER := NULL;
2042 l_bkpt_detail_num NUMBER := NULL;
2043 l_bkdt_start_date DATE := NULL;
2044 l_bkdt_end_date DATE := NULL;
2045 l_flag VARCHAR2(1) := 'N';
2046
2047 /* Cursor to fetch all the periods */
2048
2049 CURSOR cur_periods
2050 IS
2051 SELECT a.period_id,
2052 a.start_date,
2053 a.end_date
2054 FROM pn_var_periods_all a
2055 WHERE a.var_rent_id = l_var_rent_id
2056 AND a.status IS NULL;
2057
2058
2059 /* Cursor to fetch all line defaults */
2060
2061 CURSOR cur_line_def (p_start_date DATE
2062 ,p_end_date DATE)
2063 IS
2064 SELECT *
2065 FROM pn_var_line_defaults_all
2066 WHERE var_rent_id = l_var_rent_id;
2067
2068
2069 /* Cursor to fetch all breakpoint header defaults
2070 overlaping with period */
2071
2072 CURSOR cur_bkhd_def (p_line_default_id NUMBER
2073 ,p_start_date DATE
2074 ,p_end_date DATE)
2075 IS
2076 SELECT *
2077 FROM pn_var_bkhd_defaults_all
2078 WHERE line_default_id = p_line_default_id
2079 AND bkhd_start_date <= p_end_date
2080 AND bkhd_end_date >= p_start_date;
2081
2082
2083 /* Cursor to fetch all breakpoint detail defaults
2084 overlaping with period */
2085
2086 CURSOR cur_bkdt_def (p_head_default_id NUMBER
2087 ,p_start_date DATE
2088 ,p_end_date DATE)
2089 IS
2090 SELECT *
2091 FROM pn_var_bkdt_defaults_all
2092 WHERE bkhd_default_id = p_head_default_id
2093 AND bkdt_start_date <= p_end_date
2094 AND bkdt_end_date >= p_start_date;
2095
2096
2097 /* Cursor to check if a line exist for a given line default */
2098
2099 CURSOR line_exists_cur (p_line_def_id NUMBER
2100 ,p_period_id NUMBER)
2101 IS
2102 SELECT line_item_id
2103 FROM pn_var_lines_all
2104 WHERE line_default_id = p_line_def_id
2105 AND period_id = p_period_id;
2106
2107
2108 /* Cursor to check if a breakpoint header default exists for
2109 a line default */
2110
2111 CURSOR find_if_bkhd_exists_cur (p_line_def_id NUMBER)
2112 IS
2113 SELECT bkhd_default_id
2114 FROM pn_var_bkhd_defaults_all
2115 WHERE line_default_id = p_line_def_id;
2116
2117 /* Cursor to check if a breakpoint detail default exists for
2118 a breakpoint Header default */
2119
2120 CURSOR find_if_bkdt_exists_cur (p_bkhd_def_id NUMBER)
2121 IS
2122 SELECT 'x' bkdt_exists
2123 FROM DUAL
2124 WHERE EXISTS (SELECT null
2125 from pn_var_bkdt_defaults_all
2126 where bkhd_default_id = p_bkhd_def_id);
2127
2128 BEGIN
2129
2130 PNP_DEBUG_PKG.log('PN_VAR_DEFAULTS_PKG.CREATE_SETUP_DATA (+)');
2131 /* dbms_output.put_line('PN_VAR_DEFAULTS_PKG.CREATE_SETUP_DATA (+)'); */
2132
2133 l_var_rent_id := x_var_rent_id;
2134
2135 pn_var_defaults_pkg.delete_default_lines (l_var_rent_id);
2136
2137 FOR per_rec IN cur_periods
2138 LOOP
2139
2140 FOR line_def_rec IN cur_line_def (p_start_date => per_rec.start_date
2141 ,p_end_date => per_rec.start_date)
2142 LOOP
2143
2144 l_flag := 'N';
2145
2146 FOR bkhd_exists_rec IN find_if_bkhd_exists_cur (p_line_def_id => line_def_rec.line_default_id)
2147 LOOP
2148 FOR bkdt_exists_rec IN find_if_bkdt_exists_cur(p_bkhd_def_id => bkhd_exists_rec.bkhd_default_id)
2149 LOOP
2150 l_flag := 'Y';
2151 END LOOP;
2152 END LOOP;
2153
2154 IF l_flag = 'Y' THEN
2155 l_line_item_id := NULL;
2156 OPEN line_exists_cur (p_line_def_id => line_def_rec.line_default_id
2157 ,p_period_id => per_rec.period_id);
2158 FETCH line_exists_cur INTO l_line_item_id;
2159 CLOSE line_exists_cur;
2160
2161 /*dbms_output.put_line('p_start_date '|| per_rec.start_date);
2162 dbms_output.put_line('l_line_item_id '||l_line_item_id);*/
2163
2164 IF l_line_item_id IS NULL THEN
2165
2166 /* Inserting line defaults data into PN_VAR_LINES_ALL */
2167
2168 l_rowid := NULL;
2169 l_line_item_num := NULL;
2170
2171 PN_VAR_LINES_PKG.INSERT_ROW(
2172 X_ROWID => l_rowid,
2173 X_LINE_ITEM_ID => l_line_item_id,
2174 X_LINE_ITEM_NUM => l_line_item_num,
2175 X_PERIOD_ID => per_rec.period_id,
2176 X_SALES_TYPE_CODE => line_def_rec.sales_type_code,
2177 X_ITEM_CATEGORY_CODE => line_def_rec.item_category_code,
2178 X_COMMENTS => null,
2179 X_ATTRIBUTE_CATEGORY => line_def_rec.attribute_category,
2180 X_ATTRIBUTE1 => line_def_rec.attribute1,
2181 X_ATTRIBUTE2 => line_def_rec.attribute2,
2182 X_ATTRIBUTE3 => line_def_rec.attribute3,
2183 X_ATTRIBUTE4 => line_def_rec.attribute4,
2184 X_ATTRIBUTE5 => line_def_rec.attribute5,
2185 X_ATTRIBUTE6 => line_def_rec.attribute6,
2186 X_ATTRIBUTE7 => line_def_rec.attribute7,
2187 X_ATTRIBUTE8 => line_def_rec.attribute8,
2188 X_ATTRIBUTE9 => line_def_rec.attribute9,
2189 X_ATTRIBUTE10 => line_def_rec.attribute10,
2190 X_ATTRIBUTE11 => line_def_rec.attribute11,
2191 X_ATTRIBUTE12 => line_def_rec.attribute12,
2192 X_ATTRIBUTE13 => line_def_rec.attribute13,
2193 X_ATTRIBUTE14 => line_def_rec.attribute14,
2194 X_ATTRIBUTE15 => line_def_rec.attribute15,
2195 X_ORG_ID => line_def_rec.org_id,
2196 X_CREATION_DATE => sysdate,
2197 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
2198 X_LAST_UPDATE_DATE => sysdate,
2199 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
2200 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),0),
2201 X_LINE_TEMPLATE_ID => line_def_rec.line_template_id,
2202 X_AGREEMENT_TEMPLATE_ID => line_def_rec.agreement_template_id,
2203 X_LINE_DEFAULT_ID => line_def_rec.line_default_id,
2204 X_VAR_RENT_ID => l_var_rent_id);
2205
2206
2207 ELSE
2208
2209 /* UPDATE PN_VAR_LINES_ALL, using data from PN_VAR_LINE_DEFAULTS_ALL */
2210
2211 /*dbms_output.put_line('at update');
2212 dbms_output.put_line('l_line_item_id '||l_line_item_id);*/
2213
2214 UPDATE PN_VAR_LINES_ALL SET
2215 PERIOD_ID = per_rec.period_id,
2216 SALES_TYPE_CODE = line_def_rec.sales_type_code,
2217 ITEM_CATEGORY_CODE = line_def_rec.item_category_code,
2218 ATTRIBUTE_CATEGORY = line_def_rec.attribute_category,
2219 ATTRIBUTE1 = line_def_rec.attribute1,
2220 ATTRIBUTE2 = line_def_rec.attribute2,
2221 ATTRIBUTE3 = line_def_rec.attribute3,
2222 ATTRIBUTE4 = line_def_rec.attribute4,
2223 ATTRIBUTE5 = line_def_rec.attribute5,
2224 ATTRIBUTE6 = line_def_rec.attribute6,
2225 ATTRIBUTE7 = line_def_rec.attribute7,
2226 ATTRIBUTE8 = line_def_rec.attribute8,
2227 ATTRIBUTE9 = line_def_rec.attribute9,
2228 ATTRIBUTE10 = line_def_rec.attribute10,
2229 ATTRIBUTE11 = line_def_rec.attribute11,
2230 ATTRIBUTE12 = line_def_rec.attribute12,
2231 ATTRIBUTE13 = line_def_rec.attribute13,
2232 ATTRIBUTE14 = line_def_rec.attribute14,
2233 ATTRIBUTE15 = line_def_rec.attribute15,
2234 LAST_UPDATE_DATE = sysdate,
2235 LAST_UPDATED_BY = NVL(fnd_profile.value('USER_ID'),0),
2236 LAST_UPDATE_LOGIN = NVL(fnd_profile.value('USER_ID'),0),
2237 LINE_TEMPLATE_ID = line_def_rec.line_template_id ,
2238 AGREEMENT_TEMPLATE_ID = line_def_rec.agreement_template_id,
2239 LINE_DEFAULT_ID = line_def_rec.line_default_id,
2240 VAR_RENT_ID = l_var_rent_id
2241 WHERE LINE_ITEM_ID = l_line_item_id;
2242
2243 END IF;
2244
2245 FOR bkhd_def_rec IN cur_bkhd_def (p_line_default_id => line_def_rec.line_default_id
2246 ,p_start_date => per_rec.start_date
2247 ,p_end_date => per_rec.end_date)
2248 LOOP
2249
2250 l_rowid := NULL;
2251 l_bkpt_header_id := NULL;
2252 l_bkhd_start_date := GREATEST(bkhd_def_rec.bkhd_start_date,per_rec.start_date);
2253 l_bkhd_end_Date := LEAST(bkhd_def_rec.bkhd_end_date,per_rec.end_date);
2254
2255 PN_VAR_BKPTS_HEAD_PKG.INSERT_ROW(
2256 X_ROWID => l_rowid,
2257 X_BKPT_HEADER_ID => l_bkpt_header_id,
2258 X_LINE_ITEM_ID => l_line_item_id,
2259 X_PERIOD_ID => per_rec.period_id,
2260 X_BREAK_TYPE => bkhd_def_rec.break_type,
2261 X_BASE_RENT_TYPE => bkhd_def_rec.base_rent_type,
2262 X_NATURAL_BREAK_RATE => bkhd_def_rec.natural_break_rate,
2263 X_BASE_RENT => bkhd_def_rec.base_rent,
2264 X_BREAKPOINT_TYPE => bkhd_def_rec.breakpoint_type,
2265 X_BKHD_DEFAULT_ID => bkhd_def_rec.bkhd_default_id,
2266 X_BKHD_START_DATE => l_bkhd_start_date,
2267 X_BKHD_END_DATE => l_bkhd_end_Date,
2268 X_VAR_RENT_ID => l_var_rent_id,
2269 X_ATTRIBUTE_CATEGORY => bkhd_def_rec.attribute_category,
2270 X_ATTRIBUTE1 => bkhd_def_rec.attribute1,
2271 X_ATTRIBUTE2 => bkhd_def_rec.attribute2,
2272 X_ATTRIBUTE3 => bkhd_def_rec.attribute3,
2273 X_ATTRIBUTE4 => bkhd_def_rec.attribute4,
2274 X_ATTRIBUTE5 => bkhd_def_rec.attribute5,
2275 X_ATTRIBUTE6 => bkhd_def_rec.attribute6,
2276 X_ATTRIBUTE7 => bkhd_def_rec.attribute7,
2277 X_ATTRIBUTE8 => bkhd_def_rec.attribute8,
2278 X_ATTRIBUTE9 => bkhd_def_rec.attribute9,
2279 X_ATTRIBUTE10 => bkhd_def_rec.attribute10,
2280 X_ATTRIBUTE11 => bkhd_def_rec.attribute11,
2281 X_ATTRIBUTE12 => bkhd_def_rec.attribute12,
2282 X_ATTRIBUTE13 => bkhd_def_rec.attribute13,
2283 X_ATTRIBUTE14 => bkhd_def_rec.attribute14,
2284 X_ATTRIBUTE15 => bkhd_def_rec.attribute15,
2285 X_ORG_ID => bkhd_def_rec.org_id,
2286 X_CREATION_DATE => sysdate,
2287 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
2288 X_LAST_UPDATE_DATE => sysdate,
2289 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
2290 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('LOGIN_ID'),0),
2291 X_BKPT_UPDATE_FLAG => bkhd_def_rec.bkpt_update_flag);
2292
2293
2294 /*dbms_output.put_line('l_bkpt_header_id ' || l_bkpt_header_id);
2295 dbms_output.put_line('l_line_item_id '||l_line_item_id); */
2296
2297 FOR bkdt_def_rec IN cur_bkdt_def (p_head_default_id => bkhd_def_rec.bkhd_default_id
2298 ,p_start_date => per_rec.start_date
2299 ,p_end_date => per_rec.end_date)
2300 LOOP
2301
2302 l_rowid := NULL;
2303 l_bkpt_detail_id := NULL;
2304 l_bkpt_detail_num := NULL;
2305 l_bkdt_start_date := GREATEST(bkdt_def_rec.bkdt_start_date,per_rec.start_date);
2306 l_bkdt_end_date := LEAST(bkdt_def_rec.bkdt_end_date,per_rec.end_date);
2307
2308 PN_VAR_BKPTS_DET_PKG.INSERT_ROW (
2309 X_ROWID => l_rowid,
2310 X_BKPT_DETAIL_ID => l_bkpt_detail_id,
2311 X_BKPT_DETAIL_NUM => l_bkpt_detail_num,
2312 X_BKPT_HEADER_ID => l_bkpt_header_id,
2313 X_BKPT_START_DATE => l_bkdt_start_date,
2314 X_BKPT_END_DATE => l_bkdt_end_date,
2315 X_PERIOD_BKPT_VOL_START => bkdt_def_rec.period_bkpt_vol_start,
2316 X_PERIOD_BKPT_VOL_END => bkdt_def_rec.period_bkpt_vol_end,
2317 X_GROUP_BKPT_VOL_START => bkdt_def_rec.group_bkpt_vol_start,
2318 X_GROUP_BKPT_VOL_END => bkdt_def_rec.group_bkpt_vol_end,
2319 X_BKPT_RATE => bkdt_def_rec.bkpt_rate,
2320 X_BKDT_DEFAULT_ID => bkdt_def_rec.bkdt_default_id,
2321 X_VAR_RENT_ID => l_var_rent_id,
2322 X_COMMENTS => NULL,
2323 X_ATTRIBUTE_CATEGORY => bkdt_def_rec.attribute_category,
2324 X_ATTRIBUTE1 => bkdt_def_rec.attribute1,
2325 X_ATTRIBUTE2 => bkdt_def_rec.attribute2,
2326 X_ATTRIBUTE3 => bkdt_def_rec.attribute3,
2327 X_ATTRIBUTE4 => bkdt_def_rec.attribute4,
2328 X_ATTRIBUTE5 => bkdt_def_rec.attribute5,
2329 X_ATTRIBUTE6 => bkdt_def_rec.attribute6,
2330 X_ATTRIBUTE7 => bkdt_def_rec.attribute7,
2331 X_ATTRIBUTE8 => bkdt_def_rec.attribute8,
2332 X_ATTRIBUTE9 => bkdt_def_rec.attribute9,
2333 X_ATTRIBUTE10 => bkdt_def_rec.attribute10,
2334 X_ATTRIBUTE11 => bkdt_def_rec.attribute11,
2335 X_ATTRIBUTE12 => bkdt_def_rec.attribute12,
2336 X_ATTRIBUTE13 => bkdt_def_rec.attribute13,
2337 X_ATTRIBUTE14 => bkdt_def_rec.attribute14,
2338 X_ATTRIBUTE15 => bkdt_def_rec.attribute15,
2339 X_ORG_ID => bkdt_def_rec.org_id,
2340 X_CREATION_DATE => sysdate,
2341 X_CREATED_BY => NVL(fnd_profile.value('USER_ID'),0),
2342 X_LAST_UPDATE_DATE => sysdate,
2343 X_LAST_UPDATED_BY => NVL(fnd_profile.value('USER_ID'),0),
2344 X_LAST_UPDATE_LOGIN => NVL(fnd_profile.value('USER_ID'),0),
2345 X_ANNUAL_BASIS_AMOUNT => bkdt_def_rec.annual_basis_amount);
2346
2347 /* dbms_output.put_line('l_bkpt_detail_id ' || l_bkpt_detail_id);
2348 dbms_output.put_line('l_line_item_id '||l_line_item_id); */
2349
2350 END LOOP; /* bkdt_def_rec */
2351
2352 END LOOP; /* bkhd_def_rec */
2353 END IF;
2354
2355 END LOOP; /* line_def_rec */
2356
2357 END LOOP; /* per_rec */
2358
2359 UPDATE pn_var_bkhd_defaults_all
2360 SET bkpt_update_flag = 'N'
2361 WHERE var_rent_id = l_var_rent_id;
2362
2363 UPDATE pn_var_lines_all
2364 SET bkpt_update_flag = 'Y'
2365 WHERE var_rent_id = l_var_rent_id;
2366
2367 PNP_DEBUG_PKG.log('PN_VAR_DEFAULTS_PKG.CREATE_SETUP_DATA (-)');
2368
2369 END CREATE_SETUP_DATA;
2370
2371
2372 PROCEDURE put_log(p_str VARCHAR2) IS
2373
2374 BEGIN
2375 pnp_debug_pkg.debug(p_str);
2376 END put_log;
2377
2378 END PN_VAR_DEFAULTS_PKG;
2379