[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_NATURAL_BP_PKG
Source
1 PACKAGE BODY pn_var_natural_bp_pkg AS
2 -- $Header: PNVRNBPB.pls 120.0 2007/10/03 14:29:26 rthumma noship $
3
4 -------------------------------------------------------------------------------
5 -- NAME : BUILD_BKPT_DETAILS_MAIN
6 --
7 -- DESCRIPTION : This procedures calls build_bkpt_details which creates
8 -- Bkpt Detail records for a NATURAL Breakpoint. This is
9 -- followed by calculation of group and period volumes and
10 -- the inserting data into details defaults tables
11 --
12 -- ARGUMENTS :
13 --
14 -- IN PARAM : p_var_rent_id (mandatory)
15 --
16 -- OUT PARM : 1. Error Message
17 -- 2. Error Code
18 --
19 -- INVOKED FROM : PNXVAREN.pld (ON-COMMIT)
20 -- HISTORY :
21 --
22 -- 06-JUL-06 Pikhar o Created
23 -- 09-JAN-06 Pikhar o Bug 5702932. Added NVL condition in bkpt_rate.
24 -- 13-MAR-06 Pikhar o Bug 5918092. Calculate volumes using headers rate
25 -------------------------------------------------------------------------------
26
27 PROCEDURE build_bkpt_details_main(errbuf OUT NOCOPY VARCHAR2,
28 retcode OUT NOCOPY VARCHAR2,
29 p_var_rent_id IN NUMBER)
30 IS
31 l_bkpt_rec BKPT_REC_TYPE;
32 l_lease_id NUMBER;
33 l_var_rent_id NUMBER;
34 l_bkpt_hdr_id NUMBER;
35 l_bkpt_rate NUMBER;
36 l_err VARCHAR2(2000);
37 l_ret VARCHAR2(2000);
38 l_counter NUMBER := 0;
39 l_rowid VARCHAR2(18) := NULL;
40 l_bkpt_default_Id NUMBER := NULL;
41 l_bkpt_default_Num NUMBER := NULL;
42 l_reporting_freq VARCHAR2(30);
43 l_reporting_periods NUMBER;
44 l_annual_basis_amount NUMBER := NULL;
45 l_period_bkpt_vol NUMBER := 0;
46 l_group_bkpt_vol NUMBER := 0;
47 l_natural_break_rate NUMBER;
48 l_start_date DATE;
49 l_end_date DATE;
50 l_actual_amount NUMBER(20,4);
51 l_head_dflt_id NUMBER;
52 l_header_id NUMBER;
53 l_det_break_rate NUMBER(20,4);
54 l_final_bkpt_rate NUMBER(20,4);
55 l_dummy VARCHAR2(1);
56 l_null NUMBER :=0;
57
58
59
60
61 CURSOR process_all_defaults_cur
62 IS
63 SELECT bkhd_default_id, natural_break_rate
64 FROM pn_var_bkhd_defaults_all
65 WHERE var_rent_id = p_var_rent_id
66 AND break_type = 'NATURAL';
67
68 CURSOR detail_rate_cur (p_bkhd_def_id IN NUMBER,
69 p_bkhd_def_st_date IN DATE,
70 p_bkhd_def_end_date IN DATE,
71 p_bkhd_def_rate IN NUMBER)
72 IS
73 SELECT NVL(bkpt_rate,p_bkhd_def_rate) bkpt_rate
74 FROM pn_var_bkdt_defaults_all
75 WHERE bkhd_default_id = p_bkhd_def_id
76 AND p_bkhd_def_st_date <= bkdt_end_date
77 AND p_bkhd_def_end_date >= bkdt_start_date;
78
79
80 CURSOR line_info_cur(p_line_item_id IN NUMBER)
81 IS
82 SELECT period_id, var_rent_id
83 FROM pn_var_lines_all
84 WHERE line_item_id = p_line_item_id;
85
86
87 CURSOR line_cur(p_bkhd_default_id IN NUMBER) IS
88 select line_item_id
89 from pn_var_lines_all
90 where line_default_id IN (select line_default_id
91 from pn_var_bkhd_defaults_all
92 where bkhd_default_id = p_bkhd_default_id);
93
94
95 BEGIN
96
97 PNP_DEBUG_PKG.LOG ('pn_var_natural_bp_pkg.build_bkpt_details_main : (+)');
98
99 IF p_var_rent_id IS NULL THEN
100
101 PNP_DEBUG_PKG.LOG ('p_var_rent_id IS NULL');
102 errbuf := 'Var Rent ID is, Please enter Var Rent ID';
103 retcode := 2;
104
105 ELSE
106
107 l_var_rent_id := p_var_rent_id;
108
109 SELECT lease_id
110 INTO l_lease_id
111 FROM pn_var_rents_all
112 WHERE var_rent_id = l_var_rent_id;
113
114 SELECT reptg_freq_code
115 INTO l_reporting_freq
116 FROM pn_var_rent_dates_all
117 WHERE var_rent_id = p_var_rent_id;
118
119 l_reporting_periods := NULL;
120 l_reporting_periods := NVL(pn_var_rent_pkg.find_reporting_periods(p_freq_code=>l_reporting_freq), 1);
121
122 FOR bkhd_def_rec in process_all_defaults_cur
123 LOOP
124
125 l_head_dflt_id := bkhd_def_rec.bkhd_default_id;
126 l_natural_break_rate := bkhd_def_rec.natural_break_rate;
127
128 build_bkpt_details(errbuf => l_err,
129 retcode => l_ret,
130 p_lease_id => l_lease_id,
131 p_var_rent_id => l_var_rent_id,
132 p_head_dflt_id => l_head_dflt_id,
133 p_header_id => NULL,
134 p_bkpt_rec => l_bkpt_rec
135 );
136
137 FOR l_counter IN l_bkpt_rec.FIRST .. l_bkpt_rec.LAST
138 LOOP
139
140 l_start_date := l_bkpt_rec(l_counter).start_date;
141 l_end_date := l_bkpt_rec(l_counter).end_date;
142 l_actual_amount := l_bkpt_rec(l_counter).amount;
143 l_annual_basis_amount := l_actual_amount;
144
145 l_bkpt_rate := NULL;
146 FOR det_rec IN detail_rate_cur(p_bkhd_def_id => l_head_dflt_id,
147 p_bkhd_def_st_date => l_start_date,
148 p_bkhd_def_end_date => l_end_date,
149 p_bkhd_def_rate => l_natural_break_rate)
150 LOOP
151 l_bkpt_rate := det_rec.bkpt_rate;
152 END LOOP;
153
154 l_bkpt_rec(l_counter).bkpt_rate := l_bkpt_rate;
155
156 END LOOP;
157
158
159 DELETE FROM pn_var_bkpts_det_all
160 WHERE var_rent_id = p_var_rent_id
161 AND bkpt_header_id IN (SELECT bkpt_header_id
162 FROM pn_var_bkpts_head_all
163 WHERE bkhd_default_id = l_head_dflt_id);
164
165 DELETE FROM PN_VAR_BKDT_DEFAULTS_ALL
166 WHERE bkhd_default_id = l_head_dflt_id;
167
168 DELETE FROM pn_var_bkpts_head_all
169 WHERE bkhd_default_id = l_head_dflt_id;
170
171 FOR l_counter IN l_bkpt_rec.FIRST .. l_bkpt_rec.LAST
172 LOOP
173
174 l_start_date := l_bkpt_rec(l_counter).start_date;
175 l_end_date := l_bkpt_rec(l_counter).end_date;
176 l_actual_amount := l_bkpt_rec(l_counter).amount;
177 l_bkpt_rate := l_bkpt_rec(l_counter).bkpt_rate;
178
179 l_period_bkpt_vol := ROUND((l_actual_amount / NVL(l_natural_break_rate,l_bkpt_rate)), 2);
180 l_group_bkpt_vol := ROUND((l_period_bkpt_vol/l_reporting_periods),2);
181
182 BEGIN
183 l_rowid := NULL;
184 l_bkpt_default_Id := NULL;
185 l_bkpt_default_Num := NULL;
186
187 PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW(x_rowid => l_rowId,
188 x_bkdt_default_id => l_bkpt_default_Id,
189 x_bkdt_detail_num => l_bkpt_default_Num,
190 x_bkhd_default_id => l_head_dflt_id,
191 x_bkdt_start_date => l_start_date,
192 x_bkdt_end_date => l_end_date,
193 x_period_bkpt_vol_start => l_period_bkpt_vol,
194 x_period_bkpt_vol_end => null,
195 x_group_bkpt_vol_start => l_group_bkpt_vol,
196 x_group_bkpt_vol_end => null,
197 x_bkpt_rate => NVL(l_bkpt_rate,l_natural_break_rate),
198 x_processed_flag => null,
199 x_var_rent_id => p_var_rent_id,
200 x_creation_date => SYSDATE,
201 x_created_by => NVL (FND_PROFILE.VALUE ('USER_ID'), 0),
202 x_last_update_date => SYSDATE,
203 x_last_updated_by => NVL (FND_PROFILE.VALUE ('USER_ID'), 0),
204 x_last_update_login => NVL (FND_PROFILE.VALUE ('LOGIN_ID'), 0),
205 x_org_id => NVL (FND_PROFILE.VALUE ('ORG_ID'), 0 ),
206 x_annual_basis_amount => l_actual_amount,
207 x_attribute_category => NULL,
208 x_attribute1 => NULL,
209 x_attribute2 => NULL,
210 x_attribute3 => NULL,
211 x_attribute4 => NULL,
212 x_attribute5 => NULL,
213 x_attribute6 => NULL,
214 x_attribute7 => NULL,
215 x_attribute8 => NULL,
216 x_attribute9 => NULL,
217 x_attribute10 => NULL,
218 x_attribute11 => NULL,
219 x_attribute12 => NULL,
220 x_attribute13 => NULL,
221 x_attribute14 => NULL,
222 x_attribute15 => NULL);
223
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 /*DBMS_OUTPUT.PUT_LINE(SUBSTR('Error while PN_VAR_BKDT_DEFAULTS_PKG.INSERT_ROW - '||
228 TO_CHAR(SQLCODE)||' : '||SQLERRM, 1, 244));*/
229 errbuf := SQLERRM;
230 retcode := 2;
231 ROLLBACK;
232 END;
233
234 END LOOP;
235 END LOOP;
236 END IF;
237
238 PNP_DEBUG_PKG.LOG ('pn_var_natural_bp_pkg.build_bkpt_details_main : (-)');
239
240 END build_bkpt_details_main;
241
242 -------------------------------------------------------------------------------
243 -- NAME : BUILD_BKPT_DETAILS
244 --
245 -- DESCRIPTION : This procedure splits Bkpt Detail records for a NATURAL
246 -- lease based on Var Rent dates for re-distribution
247 -- whenever there is a change in term dates or number of terms
248 -- IF a lease has Var Rent Term T1 exists from 1/1/01 to 12/31/2002
249 -- and Var Rent Term T2 exists from 2/1/01 to 2/28/01
250 -- and Include in Var Rent has been checked then
251
252 -- T1 |--------------------| T2 |----|
253 -- 1/1/01 12/31/2002 2/1/01 2/28/01
254 --
255 -- Bkpt details will be created as follows.....
256 -- Bkpt detail BKPT1 for T1 :|---|
257 -- 1/1/01 1/31/01
258 -- Bkpt detail BKPT2 for T1+ T2 : |---|
259 -- 2/1/01 2/28/01
260 -- Bkpt detail BKPT3 for T1 : |----------|
261 -- 3/1/01 12/31/01
262 -- Bkpt detail BKPT4 for T1 : |-------------|
263 -- 1/1/02 12/31/02
264 --
265 --
266 -- ARGUMENTS:
267 --
268 -- IN PARAM 1) p_lease_id (optional)
269 -- 2) p_var_rent_id (mandatory)
270 -- 3) Header_default_id (mandatory if Header_id is null)
271 -- 4) Header_id (mandatory if Header_default_id is null)
272 -- OUT PARM 1) Error Message
273 -- 2) Error Code
274 -- 3) bkpt detail start dates table
275 -- 4) bkpt detail end dates table
276 -- 5) bkpt detail actual amounts table
277 --
278 -- INVOKED FROM : ON-INSERT trigger at TERMS block level
279 -- HISTORY:
280
281 -- 06-JUL-06 Pikhar o Created
282 -- 28-MAR-07 Pikhar o Bug 5958344. Excluded DRAFT RI terms from NBB
283 -------------------------------------------------------------------------------
284
285
286 PROCEDURE build_bkpt_details(errbuf OUT NOCOPY VARCHAR2,
287 retcode OUT NOCOPY VARCHAR2,
288 p_lease_id IN NUMBER,
289 p_var_rent_id IN NUMBER,
290 p_head_dflt_id IN NUMBER,
291 p_header_id IN NUMBER,
292 p_bkpt_rec IN OUT NOCOPY bkpt_rec_type)
293 IS
294
295 l_counter NUMBER;
296 l_lease_id NUMBER;
297 l_bkhd_st_dt DATE;
298 l_bkhd_end_dt DATE;
299 l_bkpt_rate NUMBER;
300 l_date DATE;
301 l_det_amt NUMBER;
302 l_act_amt NUMBER;
303 l_reporting_periods NUMBER;
304
305 l_bkpt_rec bkpt_rec_type;
306
307
308 /***************************************************************************
309 *Cursor for Bkpt Header defaults data
310 ***************************************************************************/
311
312 CURSOR bkhd_def_cur
313 IS
314 SELECT bkhd_start_date,
315 bkhd_end_date,
316 bkhd_default_id,
317 natural_break_rate
318 FROM pn_var_bkhd_defaults_all
319 WHERE bkhd_default_id = p_head_dflt_id
320 AND break_type = 'NATURAL'
321 ORDER BY bkhd_start_date;
322
323 /***************************************************************************
324 * Cursor to get payment term data - used to build PL/SQL record *
325 ***************************************************************************/
326
327 CURSOR term_cur (p_bkhd_start_date IN DATE,
328 p_bkhd_end_date IN DATE,
329 p_lease_id IN NUMBER,
330 p_bkhd_def_id IN NUMBER,
331 p_bkpt_rate IN NUMBER)
332 IS
333 SELECT DISTINCT start_date FROM(
334 SELECT distinct GREATEST(start_date, p_bkhd_start_date) start_date
335 FROM pn_payment_terms_all
336 WHERE lease_id = p_lease_id
337 AND include_in_var_rent IN ('BASETERM','INCLUDE_RI')
338 AND index_period_id IS NULL
339 AND p_bkhd_start_date <= end_date
340 AND p_bkhd_end_date >= start_date
341 UNION
342 SELECT distinct (LEAST(end_date, p_bkhd_end_date) + 1) start_date
343 FROM pn_payment_terms_all
344 WHERE lease_id = p_lease_id
345 AND include_in_var_rent IN ('BASETERM','INCLUDE_RI')
346 AND index_period_id IS NULL
347 AND p_bkhd_start_date <= end_date
348 AND p_bkhd_end_date >= start_date
349 AND (LEAST(end_date, p_bkhd_end_date) + 1) <= p_bkhd_end_date
350 UNION
351 SELECT distinct GREATEST(start_date, p_bkhd_start_date) start_date
352 FROM pn_payment_terms_all
353 WHERE lease_id = p_lease_id
354 AND include_in_var_rent IN ('INCLUDE_RI')
355 AND index_period_id IS NOT NULL
356 AND status = 'APPROVED'
357 AND p_bkhd_start_date <= end_date
358 AND p_bkhd_end_date >= start_date
359 UNION
360 SELECT distinct (LEAST(end_date, p_bkhd_end_date) + 1) start_date
361 FROM pn_payment_terms_all
362 WHERE lease_id = p_lease_id
363 AND include_in_var_rent IN ('INCLUDE_RI')
364 AND index_period_id IS NOT NULL
365 AND status = 'APPROVED'
366 AND p_bkhd_start_date <= end_date
367 AND p_bkhd_end_date >= start_date
368 AND (LEAST(end_date, p_bkhd_end_date) + 1) <= p_bkhd_end_date
369 UNION
370 SELECT p_bkhd_start_date start_date
371 FROM DUAL
372 UNION
373 SELECT distinct bkdt_start_date start_date
374 FROM pn_var_bkdt_defaults_all
375 WHERE bkhd_default_id = p_bkhd_def_id
376 AND bkpt_rate <> p_bkpt_rate
377 UNION
378 SELECT distinct (bkdt_end_date +1) start_date
379 FROM pn_var_bkdt_defaults_all
380 WHERE bkhd_default_id = p_bkhd_def_id
381 AND bkpt_rate <> p_bkpt_rate
382 )
383 WHERE start_date <= p_bkhd_end_date
384 ORDER BY start_date;
385
386
387
388 /***************************************************************************
389 * Cursor to get payment term data - used to calculate annual basis amount *
390 ***************************************************************************/
391
392 CURSOR paymt_terms_inner_cur(p_det_st_dt IN DATE,
393 p_det_end_date IN DATE,
394 p_lease_id IN NUMBER)
395 IS
396 SELECT DISTINCT * from (
397 SELECT start_date, end_date, frequency_code, SUM(actual_amount) actual_amount
398 FROM pn_payment_terms_all
399 WHERE lease_id = p_lease_id
400 AND include_in_var_rent in ('BASETERM','INCLUDE_RI')
401 AND index_period_id IS NULL
402 AND p_det_st_dt <= end_date
403 AND p_det_end_date >= start_date
404 GROUP by start_date, end_date, frequency_code, actual_amount
405 UNION
406 SELECT start_date, end_date, frequency_code, SUM(actual_amount) actual_amount
407 FROM pn_payment_terms_all
408 WHERE lease_id = p_lease_id
409 AND include_in_var_rent in ('INCLUDE_RI')
410 AND index_period_id IS NOT NULL
411 AND status = 'APPROVED'
412 AND p_det_st_dt <= end_date
413 AND p_det_end_date >= start_date
414 GROUP by start_date, end_date, frequency_code, actual_amount);
415
416
417 BEGIN
418
419 PNP_DEBUG_PKG.LOG ('pn_var_natural_bp_pkg.build_bkpt_details : (+)');
420
421 IF p_var_rent_id IS NULL AND p_lease_id IS NULL THEN
422 errbuf := 'Lease ID and Var Rent ID are NULL';
423 retcode := 2;
424
425 ELSE
426 IF p_lease_id IS NULL AND p_var_rent_id IS NOT NULL THEN
427
428 SELECT lease_id
429 INTO l_lease_id
430 FROM pn_var_rents_all
431 WHERE var_rent_id = p_var_rent_id;
432
433 ELSE
434 l_lease_id := p_lease_id;
435 END IF;
436 END IF;
437
438 FOR bkhd_def_rec IN bkhd_def_cur
439 LOOP
440
441 l_bkhd_st_dt := bkhd_def_rec.bkhd_start_date;
442 l_bkhd_end_dt := bkhd_def_rec.bkhd_end_date;
443 l_bkpt_rate := bkhd_def_rec.natural_break_rate;
444
445 l_bkpt_rec.DELETE;
446
447 l_counter := 0;
448 FOR term_rec IN term_cur (p_bkhd_start_date => l_bkhd_st_dt,
449 p_bkhd_end_date => l_bkhd_end_dt,
450 p_lease_id => l_lease_id,
451 p_bkhd_def_id => p_head_dflt_id,
452 p_bkpt_rate => l_bkpt_rate)
453 LOOP
454 l_bkpt_rec(l_counter).start_date := term_rec.start_date;
455 l_counter := l_counter + 1;
456
457 END LOOP;
458
459 FOR l_counter in l_bkpt_rec.FIRST.. l_bkpt_rec.LAST
460 LOOP
461
462 IF l_counter < l_bkpt_rec.LAST THEN
463 /* Not the last record */
464 l_bkpt_rec(l_counter).end_date := (l_bkpt_rec(l_counter+1).start_date -1);
465 ELSE
466 /* This is the last record. Hence end_date must be the header default end date */
467 l_bkpt_rec(l_counter).end_date := l_bkhd_end_dt;
468 END IF;
469
470 l_det_amt := 0;
471 l_act_amt := 0;
472
473 FOR inner_rec IN paymt_terms_inner_cur(l_bkpt_rec(l_counter).start_date,
474 l_bkpt_rec(l_counter).end_date,
475 l_lease_id)
476 LOOP
477
478 l_det_amt := inner_rec.actual_amount;
479 --Get Billing Term Reporting Frequency
480 IF inner_rec.frequency_code = 'OT' THEN
481 l_reporting_periods := 1;
482 ELSE
483 l_reporting_periods := NVL(pn_var_rent_pkg.find_reporting_periods(p_freq_code=>inner_rec.frequency_code), 1);
484 END IF;
485
486 l_det_amt := ROUND(l_det_amt * l_reporting_periods, 4);
487 l_act_amt := l_act_amt + l_det_amt;
488
489 END LOOP;
490
491 l_bkpt_rec(l_counter).amount := l_act_amt;
492
493 END LOOP;
494
495 END LOOP;
496
497 p_bkpt_rec := l_bkpt_rec;
498
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 PNP_DEBUG_PKG.LOG(SUBSTR('pn_var_natural_bp_pkg.build_bkpt_details - '||
503 TO_CHAR(SQLCODE)||' : '||SQLERRM, 1, 244));
504 errbuf := SQLERRM;
505 retcode := 2;
506 ROLLBACK;
507
508 PNP_DEBUG_PKG.LOG ('pn_var_natural_bp_pkg.build_bkpt_details : (-)');
509
510 END build_bkpt_details;
511
512
513 -------------------------------------------------------------------------------
514 -- NAME : PN_VAR_NAT_TO_ARTIFICIAL
515 --
516 -- DESCRIPTION : This procedure is a CP run from SRS screen. In Diagnostic
517 -- mode, it lists to the user, all breakpoint headers which
518 -- has break type as Natural and not null value of Base Rent.
519 -- In update mode, it updates for the break_type from
520 -- Natural to Artificial for the breakpoints headers
521 --
522 --
523 -- ARGUMENTS
524 -- IN PARAM : p_mode (mandatory)
525 -- p_prop_id (optional)
526 -- p_loc_id (optional)
527 -- p_lease_id (optional)
528 -- p_var_rent_id (optional)
529 --
530 -- OUT PARM : None
531 --
532 -- INVOKED FROM :
533 --
534 -- HISTORY
535 --
536 -- 07-AUG-06 Pikhar o Created
537 -- 28-MAR-07 Pikhar o Bug 5956725. Converted Natural Breakpoints to FLAT
538 -- 07-May-07 Pikhar o Bug 6033669. Converted Natural Breakpoint should not
539 -- have annualised basis amount
540 -- 07-May-07 Pikhar o Bug 6033314. Modified query for lease_loc_cur
541 -------------------------------------------------------------------------------
542
543 procedure PN_VAR_NAT_TO_ARTIFICIAL(errbuf OUT NOCOPY VARCHAR2
544 ,retcode OUT NOCOPY VARCHAR2
545 ,p_mode IN VARCHAR2
546 ,p_prop_id IN NUMBER
547 ,p_loc_id IN NUMBER
548 ,p_lease_id IN NUMBER
549 ,p_var_rent_id IN NUMBER) IS
550
551
552 /* Data Structures */
553 TYPE NUM_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
554 TYPE DATE_TBL IS TABLE OF DATE INDEX BY BINARY_INTEGER;
555
556 /* Variables */
557 l_var_rent_id NUMBER :=0;
558 l_per_print NUMBER :=0;
559 l_var_print NUMBER :=0;
560 l_lease_print NUMBER :=0;
561 l_prop_id NUMBER;
562 l_loc_id NUMBER;
563 l_lease_id NUMBER;
564 l_lease_num VARCHAR2(80);
565 l_counter NUMBER;
566 l_var_rent_num VARCHAR2(30);
567 l_sales_type VARCHAR2(30);
568 l_item_category VARCHAR2(30);
569 l_message VARCHAR2(2000);
570 l_message1 VARCHAR2(2000);
571 l_update_count NUMBER :=0;
572 l_diagnostic NUMBER :=0;
573 l_lease_tab NUM_TBL;
574 l_var_lease_tab VAR_LEASE_TYPE;
575
576
577 /* Cursors */
578
579 CURSOR lease_loc_cur (p_prop_id IN NUMBER)
580 IS
581 SELECT distinct lease_id
582 FROM
583 (
584 SELECT lease_id
585 FROM pn_leases_all
586 WHERE location_id in (SELECT location_id
587 FROM pn_locations_all loc
588 START with loc.location_id in (SELECT location_id
589 FROM pn_locations_all
590 WHERE property_id = p_prop_id)
591 CONNECT by prior loc.location_id = parent_location_id)
592 UNION
593 SELECT lease_id
594 FROM pn_tenancies_all
595 WHERE location_id in (SELECT location_id
596 FROM pn_locations_all loc
597 START with loc.location_id in (SELECT location_id
598 FROM pn_locations_all
599 WHERE property_id = p_prop_id)
600 CONNECT by prior loc.location_id = parent_location_id)
601 );
602
603 CURSOR lease_cur(p_loc_id IN NUMBER)
604 IS
605 SELECT lease_id from
606 (SELECT lease_id
607 FROM pn_tenancies_all
608 WHERE location_id = p_loc_id
609 UNION
610 SELECT lease_id
611 FROM pn_leases_all
612 WHERE location_id = p_loc_id
613 );
614
615
616 CURSOR lease_var_cur (p_var_rent_id IN NUMBER)
617 IS
618 SELECT lease_id, rent_num
619 FROM PN_VAR_RENTS_ALL
620 WHERE var_rent_id = p_var_rent_id;
621
622 CURSOR var_cur(p_lease_id IN NUMBER)
623 IS
624 SELECT var_rent_id,rent_num
625 FROM pn_var_rents_All
626 WHERE lease_id = p_lease_id
627 ORDER BY var_rent_id DESC;
628
629 CURSOR periods_cur (p_var_rent_id IN NUMBER)
630 IS
631 SELECT period_id, start_date, end_date
632 FROM pn_var_periods_all
633 WHERE var_rent_id = p_var_rent_id;
634
635
636 CURSOR lines_cur (p_period_id IN NUMBER)
637 IS
638 SELECT line_item_id, sales_type_code, item_category_code
639 FROM pn_var_lines_all
640 WHERE period_id = p_period_id;
641
642
643 CURSOR bkhd_head_cur (p_line_item_id IN NUMBER)
644 IS
645 SELECT bkpt_header_id, base_rent, natural_break_rate
646 FROM pn_var_bkpts_head_all
647 WHERE line_item_id = p_line_item_id
648 AND break_type = 'NATURAL'
649 AND base_rent IS NOT NULL;
650
651
652 CURSOR sales_type_cur(p_sales_code IN VARCHAR2)
653 IS
654 SELECT fnd1.meaning
655 FROM fnd_lookups fnd1
656 WHERE lookup_type='PN_SALES_CHANNEL'
657 AND lookup_code = p_sales_code
658 AND sysdate between
659 nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
660 AND enabled_flag='Y';
661
662
663 CURSOR item_category_cur(p_item_code IN VARCHAR2)
664 IS
665 SELECT fnd2.meaning
666 FROM fnd_lookups fnd2
667 WHERE lookup_type='PN_ITEM_CATEGORY'
668 AND lookup_code = p_item_code
669 AND sysdate between
670 nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
671 AND enabled_flag='Y';
672
673
674 /* Exceptions */
675 BAD_CALL_EXCEPTION EXCEPTION;
676
677
678
679 BEGIN
680
681 pnp_debug_pkg.log('pn_var_natural_bp_pkg.pn_var_nat_to_artificial (+) : ');
682
683 l_prop_id := p_prop_id;
684 l_loc_id := p_loc_id;
685 l_var_rent_id := p_var_rent_id;
686
687
688 l_var_lease_tab.DELETE;
689
690 IF p_var_rent_id IS NOT NULL THEN
691
692 l_var_lease_tab(1).var_rent_id := p_var_rent_id;
693 OPEN lease_var_cur(p_var_rent_id);
694 FETCH lease_var_cur INTO l_var_lease_tab(1).lease_id,l_var_lease_tab(1).var_rent_num ;
695 CLOSE lease_var_cur;
696
697 ELSE
698
699 l_lease_tab.DELETE;
700
701 IF p_lease_id IS NOT NULL THEN
702
703 l_lease_tab(1) := p_lease_id;
704
705 ELSIF p_loc_id IS NOT NULL THEN
706
707 OPEN lease_cur(p_loc_id => l_loc_id);
708 FETCH lease_cur BULK COLLECT INTO l_lease_tab;
709 CLOSE lease_cur;
710
711 ELSIF p_prop_id IS NOT NULL THEN
712
713 OPEN lease_loc_cur(p_prop_id => l_prop_id);
714 FETCH lease_loc_cur BULK COLLECT INTO l_lease_tab;
715 CLOSE lease_loc_cur;
716
717 ELSE
718 RAISE BAD_CALL_EXCEPTION;
719 END IF;
720
721 l_counter := 0; /* used to populate PL/SQL table */
722
723 IF l_lease_tab.count >0 THEN
724 FOR lease_rec IN l_lease_tab.FIRST .. l_lease_tab.LAST
725 LOOP
726
727 FOR var_rec in var_cur(l_lease_tab(lease_rec))
728 LOOP
729 l_var_lease_tab(l_counter).var_rent_id := var_rec.var_rent_id;
730 l_var_lease_tab(l_counter).lease_id := l_lease_tab(lease_rec);
731 l_var_lease_tab(l_counter).var_rent_num := var_rec.rent_num;
732 l_counter := l_counter + 1;
733 END LOOP;
734
735 END LOOP;
736 END IF;
737
738 END IF;
739
740
741 IF l_var_lease_tab.count > 0 THEN
742
743 FOR var_rec in l_var_lease_tab.FIRST .. l_var_lease_tab.LAST
744 LOOP
745
746 l_var_rent_id := l_var_lease_tab(var_rec).var_rent_id;
747 l_var_rent_num := l_var_lease_tab(var_rec).var_rent_num;
748 l_lease_id := l_var_lease_tab(var_rec).lease_id;
749 l_var_print := 0;
750
751 IF l_var_lease_tab.EXISTS(var_rec -1) THEN
752 IF l_lease_id <> l_var_lease_tab(var_rec - 1 ).lease_id THEN
753 l_lease_print := 0;
754 END IF;
755 END IF;
756
757
758 FOR per_rec in periods_cur (p_var_rent_id => l_var_rent_id)
759 LOOP
760
761 l_per_print := 0;
762 FOR lines_rec in lines_cur (p_period_id => per_rec.period_id)
763 LOOP
764
765
766 OPEN sales_type_cur(p_sales_code => lines_rec.sales_type_code);
767 FETCH sales_type_cur INTO l_sales_type;
768 CLOSE sales_type_cur;
769 IF l_sales_type IS NULL THEN
770 l_sales_type := lines_rec.sales_type_code;
771 END IF;
772
773 OPEN item_category_cur(p_item_code => lines_rec.item_category_code);
774 FETCH item_category_cur INTO l_item_category;
775 CLOSE item_category_cur;
776 IF l_item_category IS NULL THEN
777 l_item_category := lines_rec.item_category_code;
778 END IF;
779
780
781 FOR bkhd_rec in bkhd_head_cur (lines_rec.line_item_id)
782 LOOP
783
784 IF p_mode = 'D' THEN
785
786 IF l_lease_print = 0 THEN
787
788 fnd_message.set_name ('PN','PN_LEASE_NUMBER');
789 l_message := fnd_message.get;
790 Fnd_File.Put_Line ( Fnd_File.OutPut,' ');
791 Fnd_File.Put_Line ( Fnd_File.OutPut,' ');
792 Fnd_File.Put_Line ( Fnd_File.OutPut,' ');
793 Fnd_File.Put_Line ( Fnd_File.OutPut,'================================================================================');
794 Fnd_File.Put_Line ( Fnd_File.OutPut,' '||l_message ||' : ' || l_lease_id);
795 Fnd_File.Put_Line ( Fnd_File.OutPut,'================================================================================');
796 l_message := NULL;
797 l_lease_print := 1; /* This is to ensure that the lease name is printed
798 just once for all VRs belonging to that lease*/
799
800 END IF;
801
802
803 IF l_var_print = 0 THEN
804
805 fnd_message.set_name ('PN','PN_VARENT_NUM');
806 fnd_message.set_token('VAR_RENT_NUM',l_var_rent_num);
807 l_message := fnd_message.get;
808 Fnd_File.Put_Line ( Fnd_File.OutPut,' ');
809 Fnd_File.Put_Line ( Fnd_File.OutPut,' ');
810 Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
811 Fnd_File.Put_Line ( Fnd_File.OutPut,' ' || l_message);
812 Fnd_File.Put_Line ( Fnd_File.OutPut,' ---------------------------------------------------------------------------');
813 l_message := NULL;
814 l_var_print := 1; /* This is to ensure that the VR name is printed
815 just once for all periods belonging to that VR*/
816
817 END IF;
818
819
820 IF l_per_print = 0 THEN
821
822 fnd_message.set_name ('PN','PN_VAR_PER_ST_DT');
823 l_message := fnd_message.get;
824 fnd_message.set_name ('PN','PN_VAR_PER_END_DT');
825 l_message := l_message||' '||fnd_message.get;
826
827 fnd_message.set_name ('PN','PN_VAR_SALES_TYPE');
828 l_message1 := fnd_message.get;
829 fnd_message.set_name ('PN','PN_VAR_ITEM_CATEGORY');
830 l_message1 := l_message1|| ' '||fnd_message.get;
831 fnd_message.set_name ('PN','PN_VAR_NAT_BREAK_RATE');
832 l_message1 := l_message1|| ' '||fnd_message.get;
833 fnd_message.set_name ('PN','PN_VAR_BASE_RENT');
834 l_message1 := l_message1||' '||fnd_message.get;
835
836 Fnd_File.Put_Line ( Fnd_File.OutPut, ' ');
837 Fnd_File.Put_Line ( Fnd_File.OutPut, ' ---------------------------------------------------------------------');
838 Fnd_File.Put_Line ( Fnd_File.OutPut, ' '||l_message);
839 Fnd_File.Put_Line ( Fnd_File.OutPut, ' '||per_rec.start_date||' '||per_rec.end_date);
840 Fnd_File.Put_Line ( Fnd_File.OutPut, ' ---------------------------------------------------------------------');
841 Fnd_File.Put_Line ( Fnd_File.OutPut, ' '||l_message1);
842 Fnd_File.Put_Line ( Fnd_File.OutPut, ' ---------------------------------------------------------------');
843
844 l_per_print :=1; /* This is to ensure that the period Header is printed
845 just once for all lines belonging to that period */
846
847 END IF;
848
849 Fnd_File.Put_Line ( Fnd_File.OutPut,' '
850 ||RPAD(l_sales_type,13,' ')||' '
851 ||RPAD(l_item_category,17,' ')||' '
852 ||LPAD(bkhd_rec.natural_break_rate,17,' ')
853 ||LPAD(bkhd_rec.base_rent,13,' '));
854
855 l_diagnostic := l_diagnostic +1;
856
857 ELSIF p_mode = 'U' THEN
858
859 UPDATE PN_VAR_BKPTS_HEAD_ALL
860 SET break_type = 'ARTIFICIAL'
861 , natural_break_rate = NULL
862 WHERE bkpt_header_id = bkhd_rec.bkpt_header_id;
863
864 UPDATE PN_VAR_BKPTS_HEAD_ALL
865 SET breakpoint_type = 'FLAT'
866 WHERE bkpt_header_id = bkhd_rec.bkpt_header_id;
867
868 UPDATE PN_VAR_BKPTS_DET_ALL
869 SET ANNUAL_BASIS_AMOUNT = NULL
870 WHERE bkpt_header_id = bkhd_rec.bkpt_header_id;
871
872 l_update_count := l_update_count + 1;
873
874 ELSE
875 pnp_debug_pkg.log('BAD_CALL_EXCEPTION');
876 RAISE BAD_CALL_EXCEPTION;
877 END IF;
878
879 END LOOP; /* Headers */
880
881 END LOOP; /* lines*/
882
883 END LOOP; /* periods */
884
885 END LOOP; /* var rent */
886
887 END IF;
888
889 IF p_mode = 'U' and l_update_count > 0 THEN
890 pnp_debug_pkg.log(l_update_count||' records updated' );
891 COMMIT;
892 ELSIF p_mode = 'D' and l_diagnostic > 0 THEN
893 Fnd_File.Put_Line ( Fnd_File.OutPut,' ');
894 Fnd_File.Put_Line ( Fnd_File.OutPut,'================================================================================');
895 END IF;
896
897
898 pnp_debug_pkg.log('pn_var_natural_bp_pkg.pn_var_nat_to_artificial (-) : ');
899
900 EXCEPTION
901 WHEN BAD_CALL_EXCEPTION
902 THEN
903 NULL;
904
905 END PN_VAR_NAT_TO_ARTIFICIAL;
906
907
908
909 END pn_var_natural_bp_pkg;