[Home] [Help]
PACKAGE BODY: APPS.PN_RECOVERY_EXTRACT_PKG
Source
1 PACKAGE BODY pn_recovery_extract_pkg AS
2 /* $Header: PNRCEXTB.pls 120.5 2007/10/09 06:07:40 bnoorbha ship $ */
3
4
5
6 ------------------------------ DECLARATIONS ----------------------------------+
7
8 TYPE exp_cls_line_use_tbl IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
9 TYPE exp_cls_line_mst_tbl IS TABLE OF pn_rec_expcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
10 TYPE exp_cls_line_dtl_tbl IS TABLE OF pn_rec_expcl_dtlacc%ROWTYPE INDEX BY BINARY_INTEGER;
11 TYPE area_cls_line_dtl_tbl IS TABLE OF pn_rec_arcl_dtlln%ROWTYPE INDEX BY BINARY_INTEGER;
12 TYPE area_cls_line_hdr_tbl IS TABLE OF pn_rec_arcl_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
13 TYPE area_cls_exc_tbl IS TABLE OF pn_rec_arcl_exc%ROWTYPE INDEX BY BINARY_INTEGER;
14
15 bad_input_exception EXCEPTION;
16 uom_exception EXCEPTION;
17 currency_exception EXCEPTION;
18
19 g_batch_commit_size CONSTANT NUMBER := 5000;
20
21 ----------------------------- AREA CLASS LINE --------------------------------+
22
23 ------------------------------------------------------------------------------+
24 -- PROCEDURE : process_vacancy
25 -- PARAMETERS: p_start_date => start date
26 -- p_end_date => end date
27 -- p_area => area associated with the two dates
28 -- p_date_table => part of data structure
29 -- p_number_table => part of data structure
30 -- p_subtract => if TRUE, then add, if FALSE, then subtract
31 --
32 -- INPUT VALIDATION :
33 -- o area cannot be null or < 0.
34 -- o start date OR end date cannot be null, start date <= end date.
35 --
36 -- UNDERLYING DATA STRUCTURE:
37 -- o The data structure consists of a table of dates and a table of numbers.
38 -- o Combined, they store vacancy information
39 -- o Example:
40 -- o 1. Given : date table DT and a number table NT.
41 -- o 2. Given : from date D1 to D2, vacancy is V1, from D2 to D3, vacancy is V2.
42 -- o 3. Result: data structure is as follows: DT(0) = D1, DT(1) = D2,
43 -- DT(2) = D3, NT(0) = V1, NT(1) = V2.
44 --
45 -- ASSUMES
46 -- o p_date_table.count = p_number_table.count + 1 IF both are not null
47 --
48 -- DESCRIPTION :
49 -- o Given a start date, end date, and area, the procedure UPDATES a
50 -- data structure
51 -- o the procedure does the following:
52 -- oo search through the given data structure to isolate the start date and
53 -- SORTS it into place
54 -- oo mark where the start date was isolated
55 -- oo continue the search, this time to isolate for the end date and again
56 -- SORTS it into place
57 -- oo mark where the end date was isolated
58 -- oo copy the rest of the data structure, IF necessary.
59 --
60 -- o special cases to watch out for :
61 -- oo When the start date and end date are BOTH LESS THAN DT(0)
62 -- oo Or BOTH GREATER THAN DT(DT.count - 1)
63 --
64 -- HISTORY:
65 -- 22-OCT-02 ftanudja created
66 -- 24-FEB-05 ftanudja o Added 'ELSIF p_date_table(j) <> l_end_date THEN'
67 -- before adding '0' in end dt processing. #4194998
68 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
69 ------------------------------------------------------------------------------+
70
71 PROCEDURE process_vacancy(p_start_date DATE,
72 p_end_date DATE,
73 p_area NUMBER,
74 p_date_table IN OUT NOCOPY date_table_type,
75 p_number_table IN OUT NOCOPY number_table_type,
76 p_add BOOLEAN)
77 IS
78 l_date_table date_table_type;
79 l_number_table number_table_type;
80 l_start_date DATE;
81 l_end_date DATE;
82 l_area NUMBER := p_area;
83 l_index NUMBER;
84 l_flag BOOLEAN := FALSE;
85 l_info VARCHAR2(100);
86 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_vacancy';
87
88 BEGIN
89
90 pnp_debug_pkg.log(l_desc ||' (+)');
91
92 l_info := ' checking validity on inputs';
93 pnp_debug_pkg.log(l_info);
94
95 IF p_area IS NULL OR
96 p_area < 0 OR
97 p_start_date IS NULL OR
98 p_end_date IS NULL OR
99 p_start_date > p_end_date THEN
100 app_exception.raise_exception;
101 END IF;
102
103 l_info := ' adjusting input';
104 pnp_debug_pkg.log(l_info);
105
106 l_start_date := TRUNC(p_start_date);
107 l_end_date := TRUNC(p_end_date) + 1;
108
109 IF NOT p_add THEN
110 l_area := -1 * l_area;
111 END IF;
112
113 IF p_date_table.count = 0 THEN
114
115 l_info := ' initializing table';
116 pnp_debug_pkg.log(l_info);
117
118 p_date_table.delete;
119 p_number_table.delete;
120
121 p_date_table(0):= l_start_date;
122 p_date_table(1):= l_end_date;
123 p_number_table(0) := l_area;
124
125 ELSE
126
127 l_info := ' processing start date ';
128 pnp_debug_pkg.log(l_info);
129
130 FOR i IN 0 .. p_date_table.count - 1 LOOP
131
132 IF p_date_table(i) >= l_start_date THEN
133 IF (l_number_table.count = 0) THEN
134 IF p_date_table(i) <> l_start_date THEN
135 l_number_table(l_number_table.count) := l_area;
136 END IF;
137 END IF;
138 l_date_table(l_date_table.count) := l_start_date;
139 l_index := i;
140 l_flag := TRUE;
141 exit;
142 ELSE
143 l_date_table(l_date_table.count) := p_date_table(i);
144 IF p_number_table.exists(i) THEN
145 l_number_table(l_number_table.count) := p_number_table(i);
146 END IF;
147 END IF;
148
149 END LOOP;
150
151 IF NOT l_flag THEN
152 l_date_table(l_date_table.count) := l_start_date;
153 l_number_table(l_number_table.count) := 0;
154 l_index := l_number_table.count - 1;
155 ELSE
156 l_flag := FALSE;
157 END IF;
158
159 l_info := ' processing end date ';
160 pnp_debug_pkg.log(l_info);
161
162 FOR j IN l_index .. p_date_table.count - 1 LOOP
163
164 IF p_date_table(j) >= l_end_date THEN
165 l_date_table(l_date_table.count) := l_end_date;
166 IF j >= 1 THEN
167 l_number_table(l_number_table.count) := p_number_table(j-1) + l_area;
168 ELSIF p_date_table(j) <> l_end_date THEN
169 l_number_table(l_number_table.count) := 0;
170 END IF;
171
172 l_index := j;
173 exit;
174 ELSE
175 IF l_start_date > p_date_table(j) THEN
176 l_number_table(l_number_table.count) := l_area;
177 l_date_table(l_date_table.count) := l_end_date;
178 l_flag := TRUE;
179 ELSE
180 IF l_start_date <> p_date_table(j) THEN
181 l_date_table(l_date_table.count) := p_date_table(j);
182 IF j>=1 THEN
183 l_number_table(l_number_table.count) := p_number_table(j-1) + l_area;
184 END IF;
185 l_index := j;
186 END IF;
187 END IF;
188
189 END IF;
190
191 END LOOP;
192
193 IF NOT l_flag THEN
194
195 l_info := ' processing the remaining ';
196 pnp_debug_pkg.log(l_info);
197
198 IF p_date_table(l_index) < l_end_date THEN
199 l_date_table(l_date_table.count) := l_end_date;
200 l_number_table(l_number_table.count) := l_area;
201 ELSE
202
203 FOR k IN l_index .. p_date_table.count - 1 LOOP
204 IF l_end_date <> p_date_table(k) THEN
205 l_date_table(l_date_table.count) := p_date_table(k);
206 END IF;
207
208 IF k > 0 THEN
209 IF l_end_date <> p_date_table(k) THEN
210 l_number_table(l_number_table.count) := p_number_table(k - 1);
211 END IF;
212 END IF;
213 END LOOP;
214
215 END IF;
216
217 END IF;
218
219 p_date_table := l_date_table;
220 p_number_table := l_number_table;
221
222 END IF;
223
224 pnp_debug_pkg.log(l_desc ||' (-)');
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
229 raise;
230 END;
231
232 ------------------------------------------------------------------------------+
233 -- PROCEDURE : find_area_ovr_values
234 -- DESCRIPTION:
235 -- 1. Given: data table p_ovr, parameters p_from, p_to, p_loc_id, etc.
236 -- 2. Search through p_ovr using the parameter criteria.
237 -- 3. If match found, check if p_keep_override = Y.
238 -- 4. If true, return override values p_weighted_area_ovr, etc.
239 -- 5. Otherwise, just return the corresponding item id.
240 --
241 -- HISTORY:
242 -- 19-MAR-03 ftanudja o created
243 -- 15-MAY-03 ftanudja o adjusted for .._ovr_flag logic.
244 -- 05-AUG-03 ftanudja o removed from date and to date restriction from
245 -- the main if condition. 3077454.
246 -- o removed param p_vacant_area_ovr.
247 --15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
248 ------------------------------------------------------------------------------+
249
250 PROCEDURE find_area_ovr_values(
251 p_ovr area_cls_line_dtl_tbl,
252 p_loc_id pn_rec_arcl_dtlln.location_id%TYPE,
253 p_cust_id pn_rec_arcl_dtlln.cust_account_id%TYPE,
254 p_from pn_rec_arcl_dtlln.from_date%TYPE,
255 p_to pn_rec_arcl_dtlln.to_date%TYPE,
256 p_weighted_avg_ovr OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE,
257 p_occupied_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.occupied_area_ovr%TYPE,
258 p_assigned_area_ovr OUT NOCOPY pn_rec_arcl_dtlln.assigned_area_ovr%TYPE,
259 p_exc_area_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE,
260 p_exc_prorata_ovr_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE,
261 p_area_cls_dtl_line_id OUT NOCOPY pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE,
262 p_found IN OUT NOCOPY BOOLEAN,
263 p_keep_override VARCHAR2
264 )
265 IS
266 l_info VARCHAR2(300);
267 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.find_area_ovr_values' ;
268 BEGIN
269
270 pnp_debug_pkg.log(l_desc ||' (+)');
271
272 p_weighted_avg_ovr := null;
273 p_occupied_area_ovr := null;
274 p_assigned_area_ovr := null;
275 p_exc_area_ovr_flag := null;
276 p_exc_prorata_ovr_flag := null;
277 p_area_cls_dtl_line_id := null;
278
279 FOR i IN 0 .. p_ovr.COUNT - 1 LOOP
280
281 l_info := ' checking overrides for loc id: '|| p_loc_id;
282 pnp_debug_pkg.log(l_info);
283
284 IF p_ovr(i).location_id = p_loc_id AND
285 (p_ovr(i).cust_account_id = p_cust_id OR (p_ovr(i).cust_account_id IS NULL AND p_cust_id IS NULL)) THEN
286
287 IF p_keep_override = 'Y' THEN
288 p_weighted_avg_ovr := p_ovr(i).weighted_avg_ovr;
289 p_occupied_area_ovr := p_ovr(i).occupied_area_ovr;
290 p_assigned_area_ovr := p_ovr(i).assigned_area_ovr;
291
292 -- check if overriden by comparing old value to new value
293
294 IF p_ovr(i).exclude_area_flag <> p_ovr(i).exclude_area_ovr_flag THEN
295 p_exc_area_ovr_flag := p_ovr(i).exclude_area_ovr_flag;
296 END IF;
297
298 IF p_ovr(i).exclude_prorata_flag <> p_ovr(i).exclude_prorata_ovr_flag THEN
299 p_exc_prorata_ovr_flag := p_ovr(i).exclude_prorata_ovr_flag;
300 END IF;
301 END IF;
302
303 IF p_found IS NOT NULL THEN
304 p_found := TRUE;
305 p_area_cls_dtl_line_id := p_ovr(i).area_class_dtl_line_id;
306 END IF;
307
308 exit;
309
310 END IF;
311 END LOOP;
312
313 pnp_debug_pkg.log(l_desc ||' (-)');
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
318 raise;
319
320 END find_area_ovr_values;
321
322 ------------------------------------------------------------------------------+
323 -- PROCEDURE : get_asgnbl_area_calc
324 --
325 -- DESCRIPTION :
326 -- o checks and processes input parameters
327 -- o derives occupancy percentage, weighted average, include flag, and occupied area
328 --
329 -- ASSUMES:
330 -- o p_landlord_from_date <= p_landlord_to_date
331 --
332 -- INPUT VALIDATION :
333 -- o IF p_rec_start_date IS NULL THEN default to landlord from date
334 -- o IF p_rec_end_date IS NULL THEN default to landlord end date
335 -- o IF p_rec_start_date < landlord from date THEN make p_rec_start_date = landlord from date
336 -- o IF p_rec_end_date > landlord to date THEN make p_rec_end_date = landlord to date
337 -- o IF landlord start date = landlord end date THEN return 0 as occupancy %
338 -- o start date OR end date cannot be null, and the former cannot be greater than the latter
339 --
340 -- HISTORY:
341 -- 22-OCT-02 ftanudja o created
342 -- 20-AUG-03 ftanudja o changed occup_pct calc to include st dt. 3107683.
343 ------------------------------------------------------------------------------+
344
345 PROCEDURE get_area_cls_dtl_calc(
346 p_from_date DATE,
347 p_to_date DATE,
348 p_rec_from_date IN OUT NOCOPY DATE,
349 p_rec_to_date IN OUT NOCOPY DATE,
350 p_as_of_date DATE,
351 p_assigned_area NUMBER,
352 p_exc_type_code VARCHAR2,
353 p_occup_pct OUT NOCOPY pn_rec_arcl_dtlln.occupancy_pct%TYPE,
354 p_weighted_avg OUT NOCOPY pn_rec_arcl_dtlln.weighted_avg%TYPE,
355 p_occup_area OUT NOCOPY pn_rec_arcl_dtlln.occupied_area%TYPE,
356 p_exc_prorata_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE,
357 p_exc_area_flag OUT NOCOPY pn_rec_arcl_dtlln.exclude_area_flag%TYPE,
358 p_include_flag OUT NOCOPY pn_rec_arcl_dtlln.include_flag%TYPE)
359 IS
360 l_info VARCHAR2(300);
361 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.get_area_cls_dtl_calc' ;
362 BEGIN
363
364 pnp_debug_pkg.log(l_desc ||' (+)');
365
366 l_info := ' validating dates ';
367 pnp_debug_pkg.log(l_info);
368
369 IF p_rec_from_date IS NULL THEN
370 p_rec_from_date := p_from_date;
371 END IF;
372
373 IF p_rec_to_date IS NULL THEN
374 p_rec_to_date := p_to_date;
375 END IF;
376
377 IF p_rec_from_date < p_from_date THEN
378 p_rec_from_date := p_from_date;
379 END IF;
380
381 IF p_rec_to_date > p_to_date THEN
382 p_rec_to_date := p_to_date;
383 END IF;
384
385 l_info := ' calculating occupancy and weighted avg';
386 pnp_debug_pkg.log(l_info);
387
388 -- handle divide by zero case; make start date inclusive
389 p_occup_pct := TO_NUMBER(p_to_date - (p_from_date - 1));
390 IF p_occup_pct <> 0 THEN
391 p_occup_pct := TO_NUMBER((p_rec_to_date - (p_rec_from_date - 1)) / p_occup_pct) * 100;
392 p_occup_pct := ROUND(p_occup_pct, 2);
393 END IF;
394
395 IF (p_as_of_date >= p_rec_from_date) AND (p_as_of_date <= p_rec_to_date) THEN
396 p_occup_area := p_assigned_area;
397 ELSE
398 p_occup_area := 0;
399 END IF;
400
401 p_weighted_avg := ROUND(p_occup_pct / 100 * p_assigned_area, 2);
402
403 IF ((p_as_of_date >= p_rec_from_date) AND (p_as_of_date <= p_rec_to_date)) THEN
404 p_include_flag := 'Y';
405 ELSE
406 p_include_flag := 'N';
410 pnp_debug_pkg.log(l_info);
407 END IF;
408
409 l_info := ' determining flags ';
411
412 IF p_exc_type_code = 'AREA' THEN
413 p_exc_area_flag := 'Y';
414 p_exc_prorata_flag := 'N';
415 ELSIF p_exc_type_code = 'PRORATA' THEN
416 p_exc_area_flag := 'N';
417 p_exc_prorata_flag := 'Y';
418 ELSIF p_exc_type_code = 'AREAPRORATA' THEN
419 p_exc_area_flag := 'Y';
420 p_exc_prorata_flag := 'Y';
421 ELSIF p_exc_type_code IS NULL THEN
422 p_exc_area_flag := 'N';
423 p_exc_prorata_flag := 'N';
424 END IF;
425
426 pnp_debug_pkg.log(l_desc ||' (-)');
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
431 raise;
432 END;
433
434 ------------------------------------------------------------------------------+
435 -- FUNCTION : is_totally_vacant
436 -- DESCRIPTION:
437 -- 1. Given :p_from, p_to date, and area p_num.
438 -- 2. Checks in the data structure whether location is fully occupied during.
439 --
440 -- HISTORY:
441 -- 19-MAR-03 ftanudja o created
442 ------------------------------------------------------------------------------+
443 FUNCTION is_totally_vacant(
444 p_from DATE,
445 p_to DATE,
446 p_num NUMBER,
447 p_date_tbl date_table_type,
448 p_num_tbl number_table_type
449 ) RETURN BOOLEAN
450 IS
451 l_result BOOLEAN := FALSE;
452 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.is_totally_vacant' ;
453 l_info VARCHAR2(300);
454 BEGIN
455
456 pnp_debug_pkg.log(l_desc ||' (+)');
457
458 FOR i IN 0 .. p_date_tbl.count - 2 LOOP
459 l_info := ' searching through table ';
460 pnp_debug_pkg.log(l_info);
461 IF p_from >= p_date_tbl(i) AND
462 p_to <= p_date_tbl(i+1) AND
463 p_num = p_num_tbl(i) THEN
464 l_result := TRUE;
465 exit;
466 END IF;
467 END LOOP;
468
469 RETURN l_result;
470
471 pnp_debug_pkg.log(l_desc ||' (-)');
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
476 raise;
477 END;
478
479 ------------------------------------------------------------------------------+
480 -- PROCEDURE : insert_vacancy_data
481 --
482 -- ASSUMES: p_date_table.count = p_number_table.count + 1 IF both are not null
483 --
484 -- DESCRIPTION :
485 -- o given a p_date_table and p_num_table, inserts the values into the
486 -- area class line details pl/sql table.
487 -- o while doing so, derive flags and find override values / totals if applies.
488 --
489 -- VALIDATION :
490 -- o IF p_date_table(i) AND p_date_table(i+1) are BOTH OUTSIDE the scope of
491 -- landlord_from and landlord_to date, DO NOT insert
492 -- o IF from date < landlord from date THEN from date = landlord from date
493 -- o IF to date < landlord to date THEN to date = landlord to date
494 -- o Insert only data with FULL vacancies. Compare data structure with
495 -- a reference data structure to determine full vacancies.
496 --
497 -- HISTORY:
498 -- 22-OCT-02 ftanudja o created.
499 -- 06-JUN-03 ftanudja o fix area totals calculation.
500 -- 05-AUG-03 ftanudja o changed vacant area ovr calculation.
501 -- 15-AUG-03 ftanudja o changed total asgnbl area calculation. 3099669.
502 ------------------------------------------------------------------------------+
503
504 PROCEDURE insert_vacancy_data(
505 p_location_id pn_locations.location_id%TYPE,
506 p_property_id pn_locations.property_id%TYPE,
507 p_date_table date_table_type,
508 p_num_table number_table_type,
509 p_ref_date_table date_table_type,
510 p_ref_num_table number_table_type,
511 p_from_date DATE,
512 p_to_date DATE,
513 p_as_of_date DATE,
514 p_assignable_area NUMBER,
515 p_curnt_ovr area_cls_line_dtl_tbl,
516 p_prior_ovr area_cls_line_dtl_tbl,
517 p_data_tbl IN OUT NOCOPY area_cls_line_dtl_tbl,
518 p_total_tbl IN OUT NOCOPY area_cls_line_hdr_tbl,
519 p_keep_override VARCHAR2,
520 p_regenerate VARCHAR2
521 )
522 IS
523 l_counter NUMBER;
524 l_found BOOLEAN;
525 l_from DATE;
526 l_to DATE;
527 l_num NUMBER;
528 l_include_flag VARCHAR2(1);
529 l_excl_prorata_flag VARCHAR2(1);
530 l_excl_area_flag VARCHAR2(1);
531 l_area_class_dtl_line_id NUMBER;
532 l_occup_area_ovr NUMBER;
533 l_weighted_avg_ovr NUMBER;
534 l_assigned_area_ovr NUMBER;
535 l_excl_prorata_ovr_flag VARCHAR2(1);
536 l_excl_area_ovr_flag VARCHAR2(1);
537 l_dummy NUMBER;
538 l_info VARCHAR2(300);
539 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.insert_vacancy_data' ;
540
541 BEGIN
542
543 pnp_debug_pkg.log(l_desc ||' (+)');
544
548 l_to := p_date_table(i+1) - 1;
545 FOR i IN 0 .. p_date_table.count - 2 LOOP
546
547 l_from := p_date_table(i);
549 l_num := p_num_table(i);
550 l_include_flag := 'N';
551
552 -- IF l_from AND l_to are
553 -- BOTH BEFORE landlord_from_dt OR
554 -- BOTH AFTER landlord_to_dt
555 -- THEN don't insert
556
557 IF l_to > p_from_date AND
558 l_from < p_to_date AND
559 is_totally_vacant(
560 p_from => l_from,
561 p_to => l_to,
562 p_num => l_num,
563 p_date_tbl => p_ref_date_table,
564 p_num_tbl => p_ref_num_table
565 ) THEN
566
567 get_area_cls_dtl_calc(
568 p_from_date => p_from_date,
569 p_to_date => p_to_date,
570 p_rec_from_date => l_from,
571 p_rec_to_date => l_to,
572 p_as_of_date => p_as_of_date,
573 p_assigned_area => 0,
574 p_exc_type_code => null,
575 p_occup_pct => l_dummy,
576 p_weighted_avg => l_dummy,
577 p_occup_area => l_dummy,
578 p_exc_prorata_flag => l_excl_prorata_flag,
579 p_exc_area_flag => l_excl_area_flag,
580 p_include_flag => l_include_flag
581 );
582
583 l_info := ' finding override values ';
584 pnp_debug_pkg.log(l_info);
585
586 l_found := FALSE;
587
588 IF p_regenerate = 'Y' THEN
589
590 find_area_ovr_values(
591 p_ovr => p_curnt_ovr,
592 p_loc_id => p_location_id,
593 p_cust_id => null,
594 p_from => l_from,
595 p_to => l_to,
596 p_weighted_avg_ovr => l_weighted_avg_ovr,
597 p_occupied_area_ovr => l_occup_area_ovr,
598 p_assigned_area_ovr => l_assigned_area_ovr,
599 p_exc_area_ovr_flag => l_excl_area_ovr_flag,
600 p_exc_prorata_ovr_flag => l_excl_prorata_ovr_flag,
601 p_area_cls_dtl_line_id => l_area_class_dtl_line_id,
602 p_found => l_found,
603 p_keep_override => p_keep_override
604 );
605
606 END IF;
607
608 IF NOT l_found THEN
609
610 IF p_regenerate = 'N' THEN l_found := null; END IF;
611
612 find_area_ovr_values(
613 p_ovr => p_prior_ovr,
614 p_loc_id => p_location_id,
615 p_cust_id => null,
616 p_from => l_from,
617 p_to => l_to,
618 p_weighted_avg_ovr => l_weighted_avg_ovr,
619 p_occupied_area_ovr => l_occup_area_ovr,
620 p_assigned_area_ovr => l_assigned_area_ovr,
621 p_exc_area_ovr_flag => l_excl_area_ovr_flag,
622 p_exc_prorata_ovr_flag => l_excl_prorata_ovr_flag,
623 p_area_cls_dtl_line_id => l_area_class_dtl_line_id,
624 p_found => l_found,
625 p_keep_override => p_keep_override
626 );
627
628 END IF;
629
630 l_counter := p_data_tbl.COUNT;
631 l_info := ' determining totals ';
632 pnp_debug_pkg.log(l_info);
633
634 -- if there are no overrides, then the value is null, for which we default to be equal the normal
635
636 IF l_excl_prorata_ovr_flag IS NULL THEN l_excl_prorata_ovr_flag := l_excl_prorata_flag; END IF;
637 IF l_excl_area_ovr_flag IS NULL THEN l_excl_area_ovr_flag := l_excl_area_flag; END IF;
638
639 IF l_include_flag = 'Y' THEN
640
641 -- occupied area, assigned_area, weighted avg always zero here
642
643 IF nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'N' THEN
644
645 p_total_tbl(0).total_assignable_area := p_total_tbl(0).total_assignable_area + nvl(p_assignable_area, 0);
646 p_total_tbl(0).total_occupied_area_ovr := p_total_tbl(0).total_occupied_area_ovr +
647 nvl(l_occup_area_ovr,0);
648 p_total_tbl(0).total_vacant_area := p_total_tbl(0).total_vacant_area + nvl(l_num,0);
649 p_total_tbl(0).total_vacant_area_ovr := p_total_tbl(0).total_vacant_area_ovr +
650 nvl(nvl(p_assignable_area - l_occup_area_ovr, l_num),0);
651 p_total_tbl(0).total_weighted_avg_ovr := p_total_tbl(0).total_weighted_avg_ovr +
652 nvl(l_weighted_avg_ovr,0);
653
654 ELSIF nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'Y' THEN
655
656 p_total_tbl(0).total_occupied_area_exc := p_total_tbl(0).total_occupied_area_exc +
657 nvl(l_occup_area_ovr,0);
658 p_total_tbl(0).total_vacant_area_exc := p_total_tbl(0).total_vacant_area_exc +
662 END IF;
659 nvl(nvl(p_assignable_area - l_occup_area_ovr, l_num),0);
660 p_total_tbl(0).total_weighted_avg_exc := p_total_tbl(0).total_weighted_avg_exc +
661 nvl(l_weighted_avg_ovr,0);
663
664 END IF;
665
666 l_info := ' populating data into pl/sql table ';
667 pnp_debug_pkg.log(l_info);
668
669 p_data_tbl(l_counter).area_class_dtl_line_id := l_area_class_dtl_line_id;
670 p_data_tbl(l_counter).from_date := l_from;
671 p_data_tbl(l_counter).to_date := l_to;
672 p_data_tbl(l_counter).location_id := p_location_id;
673 p_data_tbl(l_counter).property_id := p_property_id;
674 p_data_tbl(l_counter).cust_space_assign_id := null;
675 p_data_tbl(l_counter).cust_account_id := null;
676 p_data_tbl(l_counter).lease_id := null;
677 p_data_tbl(l_counter).assignable_area := p_assignable_area;
678 p_data_tbl(l_counter).assigned_area := 0;
679 p_data_tbl(l_counter).assigned_area_ovr := l_assigned_area_ovr;
680 p_data_tbl(l_counter).occupancy_pct := 0;
681 p_data_tbl(l_counter).occupied_area := 0;
682 p_data_tbl(l_counter).occupied_area_ovr := l_occup_area_ovr;
683 p_data_tbl(l_counter).vacant_area := l_num;
684 p_data_tbl(l_counter).vacant_area_ovr := p_assignable_area - l_occup_area_ovr;
685 p_data_tbl(l_counter).weighted_avg := 0;
686 p_data_tbl(l_counter).weighted_avg_ovr := l_weighted_avg_ovr;
687 p_data_tbl(l_counter).exclude_area_flag := l_excl_area_flag;
688 p_data_tbl(l_counter).exclude_prorata_flag := l_excl_prorata_flag;
689 p_data_tbl(l_counter).exclude_area_ovr_flag := l_excl_area_ovr_flag;
690 p_data_tbl(l_counter).exclude_prorata_ovr_flag := l_excl_prorata_ovr_flag;
691 p_data_tbl(l_counter).include_flag := l_include_flag;
692 p_data_tbl(l_counter).recovery_space_std_code := null;
693 p_data_tbl(l_counter).recovery_type_code := null;
694
695 END IF;
696
697 END LOOP;
698
699 pnp_debug_pkg.log(l_desc ||' (-)');
700
701 EXCEPTION
702 WHEN OTHERS THEN
703 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
704 raise;
705 END;
706
707 ------------------------------------------------------------------------------+
708 -- PROCEDURE : process_tables
709 -- DESCRIPTION:
710 -- 1. Given new and old data, determine which ones to insert, update, delete.
711 -- 2. The new data table handles insert and update.
712 -- 2. Tickmark those in the old data that intersects with new data.
713 -- 3. Those not tickmarked are to be deleted.
714 -- 4. Note that deletion is only for cases of regeneration.
715 --
716 -- HISTORY:
717 -- 19-MAR-03 ftanudja o created
718 -- 21-MAY-04 ftanudja o added logic for batch commit.
719 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
720 ------------------------------------------------------------------------------+
721 PROCEDURE process_area_class_line_data(
722 p_old_data area_cls_line_dtl_tbl,
723 p_new_data area_cls_line_dtl_tbl,
724 p_hdr_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE
725 )
726 IS
727 keep_table number_table_type;
728 delete_table number_table_type;
729 l_area_cls_dtl_line_id NUMBER;
730 l_is_in BOOLEAN;
731 l_info VARCHAR2(300);
732 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_area_tables';
733
734 BEGIN
735
736 pnp_debug_pkg.log(l_desc ||' (+)');
737
738 l_info := ' looking at new and old data to determine action to be taken';
739 pnp_debug_pkg.log(l_info);
740
741 FOR i IN 0 .. p_new_data.COUNT - 1 LOOP
742
743 l_area_cls_dtl_line_id := p_new_data(i).area_class_dtl_line_id;
744
745 IF l_area_cls_dtl_line_id IS NULL THEN
746
747 l_info := ' inserting row into area cls dtl line table ';
748 pnp_debug_pkg.log(l_info);
749
750 pn_rec_arcl_dtlln_pkg.insert_row(
751 x_org_id => pn_mo_cache_utils.get_current_org_id,
752 x_area_class_dtl_id => p_hdr_id,
753 x_area_class_dtl_line_id => l_area_cls_dtl_line_id,
754 x_from_date => p_new_data(i).from_date,
755 x_to_date => p_new_data(i).to_date,
756 x_location_id => p_new_data(i).location_id,
757 x_property_id => p_new_data(i).property_id,
758 x_cust_space_assign_id => p_new_data(i).cust_space_assign_id,
759 x_cust_account_id => p_new_data(i).cust_account_id,
760 x_lease_id => p_new_data(i).lease_id,
761 x_assignable_area => p_new_data(i).assignable_area,
762 x_assigned_area => p_new_data(i).assigned_area,
763 x_assigned_area_ovr => p_new_data(i).assigned_area_ovr,
764 x_occupancy_pct => p_new_data(i).occupancy_pct,
768 x_vacant_area_ovr => p_new_data(i).vacant_area_ovr,
765 x_occupied_area => p_new_data(i).occupied_area,
766 x_occupied_area_ovr => p_new_data(i).occupied_area_ovr,
767 x_vacant_area => p_new_data(i).vacant_area,
769 x_weighted_avg => p_new_data(i).weighted_avg,
770 x_weighted_avg_ovr => p_new_data(i).weighted_avg_ovr,
771 x_exclude_area_flag => p_new_data(i).exclude_area_flag,
772 x_exclude_area_ovr_flag => p_new_data(i).exclude_area_ovr_flag,
773 x_exclude_prorata_flag => p_new_data(i).exclude_prorata_flag,
774 x_exclude_prorata_ovr_flag => p_new_data(i).exclude_prorata_ovr_flag,
775 x_include_flag => p_new_data(i).include_flag,
776 x_recovery_space_std_code => p_new_data(i).recovery_space_std_code,
777 x_recovery_type_code => p_new_data(i).recovery_type_code,
778 x_last_update_date => SYSDATE,
779 x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
780 x_creation_date => SYSDATE,
781 x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
782 x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
783 );
784
785 ELSE
786
787 l_info := ' updating row in area cls dtl line table: ' || p_new_data(i).area_class_dtl_line_id ;
788 pnp_debug_pkg.log(l_info);
789
790 pn_rec_arcl_dtlln_pkg.update_row(
791 x_area_class_dtl_line_id => l_area_cls_dtl_line_id,
792 x_from_date => p_new_data(i).from_date,
793 x_to_date => p_new_data(i).to_date,
794 x_location_id => p_new_data(i).location_id,
795 x_property_id => p_new_data(i).property_id,
796 x_cust_space_assign_id => p_new_data(i).cust_space_assign_id,
797 x_cust_account_id => p_new_data(i).cust_account_id,
798 x_lease_id => p_new_data(i).lease_id,
799 x_assignable_area => p_new_data(i).assignable_area,
800 x_assigned_area => p_new_data(i).assigned_area,
801 x_assigned_area_ovr => p_new_data(i).assigned_area_ovr,
802 x_occupancy_pct => p_new_data(i).occupancy_pct,
803 x_occupied_area => p_new_data(i).occupied_area,
804 x_occupied_area_ovr => p_new_data(i).occupied_area_ovr,
805 x_vacant_area => p_new_data(i).vacant_area,
806 x_vacant_area_ovr => p_new_data(i).vacant_area_ovr,
807 x_weighted_avg => p_new_data(i).weighted_avg,
808 x_weighted_avg_ovr => p_new_data(i).weighted_avg_ovr,
809 x_exclude_area_flag => p_new_data(i).exclude_area_flag,
810 x_exclude_area_ovr_flag => p_new_data(i).exclude_area_ovr_flag,
811 x_exclude_prorata_flag => p_new_data(i).exclude_prorata_flag,
812 x_exclude_prorata_ovr_flag => p_new_data(i).exclude_prorata_ovr_flag,
813 x_include_flag => p_new_data(i).include_flag,
814 x_recovery_space_std_code => p_new_data(i).recovery_space_std_code,
815 x_recovery_type_code => p_new_data(i).recovery_type_code,
816 x_last_update_date => SYSDATE,
817 x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
818 x_creation_date => SYSDATE,
819 x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
820 x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
821 );
822
823 END IF;
824
825 keep_table(keep_table.COUNT) := l_area_cls_dtl_line_id;
826
827 -- do a batch commit if needed
828 IF mod (i, g_batch_commit_size) = 0 THEN
829 commit;
830 END IF;
831
832 END LOOP;
833
834 FOR i IN 0 .. p_old_data.COUNT - 1 LOOP
835 l_is_in := FALSE;
836 FOR j IN 0 .. keep_table.COUNT - 1 LOOP
837 IF keep_table(j) = p_old_data(i).area_class_dtl_line_id THEN l_is_in := TRUE; exit; END IF;
838 END LOOP;
839 IF NOT l_is_in THEN delete_table(delete_table.COUNT) := p_old_data(i).area_class_dtl_line_id; END IF;
840 END LOOP;
841
842 FORALL i IN 0 .. delete_table.COUNT - 1
843 DELETE FROM pn_rec_arcl_dtlln_all
844 WHERE area_class_dtl_line_id = delete_table(i);
845
846 pnp_debug_pkg.log(l_desc ||' (-)');
847
848 EXCEPTION
849 WHEN OTHERS THEN
850 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
851 raise;
852 END;
853
854 ------------------------------------------------------------------------------+
855 -- PROCEDURE : extract_area
856 -- ASSUMPTION : input validation will be done from UI.
857 -- DESCRIPTION:
858 --
859 -- OVERVIEW:
860 -- o given an area class id and other parameters, the program should populate
861 -- the area class line header and details table.
862 --
863 -- GETTING DEFAULT:
864 -- o first look to find default values from a prior extract (if new extract)
865 -- or current extract (if extract regenerated).
866 -- o if the header table entry already exists, re-use, otherwise, create a
867 -- new entry.
868 --
869 -- FETCHING INFORMATION:
870 -- o for a given location code, find its corresponding children of type
874 -- o if the location meets the criteria specified in the area class, then
871 -- 'OFFICE' and 'SECTION', whose active start date > to date AND active end
872 -- date < from date.
873 -- o for each of these children, find the corresponding space assignments.
875 -- process that location and all associated space assignments.
876 -- o for children without space assignments, generate the corresponding
877 -- vacancy details.
878 -- o once there are no more data for that location, dump data into pl/sql table.
879 -- o after the last location, exit out of the loop and dump data for that last
880 -- location into the aforementioned pl/sql table.
881 -- o while looping through the information, calculate the total headers
882 -- accordingly.
883 -- o when taking into account the space assignment start / end date and the
884 -- location active start / end date, compare with landlord from / to date
885 -- before inserting into the details table; the lesser of the landlord
886 -- to date and the end date is to be taken into account; similarly,
887 -- the greater of the landlord from date and the start date is to be
888 -- taken into account.
889 --
890 -- DUMPING DATA:
891 -- o a few pl/sql tables are used to keep new data and old data for the
892 -- details table.
893 -- o first process the new data, doing inserts and updates as necessary.
894 -- o then find the difference between new data and old data, and delete
895 -- the ones no longer used.
896 --
897 -- HISTORY:
898 -- 22-MAR-03 ftanudja o created.
899 -- 06-JUN-03 ftanudja o initialized area class dtl total_exc columns.
900 -- o fixed area totals calculation.
901 -- 10-JUL-03 ftanudja o added outer join on get_area_class_info cursor.
902 -- o/w arcl w/o exc is not picked up. 3046070.
903 -- o made prop id and loc id mutex on get_location_info.
904 -- 05-AUG-03 ftanudja o major flow change. CURSOR get_area_class_info
905 -- should not be main iteration point.
906 -- o fixed get_prior_ovr flow. 3077454.
907 -- o replaced 'PARCEL' with 'SECTION'. 3082071.
908 -- o added order by to date and from date for ovr values.
909 -- o changed vacant_area_ovr calculation method.
910 -- o optimized get_ovr_from_prior CURSOR (break in 2).
911 -- 15-AUG-03 ftanudja o changed total asgnbl area calculation. 3099669.
912 -- 22-AUG-03 ftanudja o validate UOM uniqueness for area extr. 3081996.
913 -- 21-MAY-04 ftanudja o added log output message to show input parameters.
914 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
915 -- 27-APR-06 Hareesha o Bug# 5148839 Modified call to process_vacancy
916 -- Added NVL for assignable_area.
917 ------------------------------------------------------------------------------+
918
919 PROCEDURE extract_area(
920 errbuf OUT NOCOPY VARCHAR2,
921 retcode OUT NOCOPY VARCHAR2,
922 p_area_class_id IN pn_rec_arcl.area_class_id%TYPE,
923 p_as_of_date IN VARCHAR2,
924 p_from_date IN VARCHAR2,
925 p_to_date IN VARCHAR2,
926 p_keep_override IN VARCHAR2)
927 IS
928 CURSOR get_area_class_info IS
929 SELECT class.area_class_id,
930 class.property_id,
931 class.location_id,
932 excl_dtl.exclusion_type_code,
933 excl_dtl.relational_code,
934 excl_dtl.area,
935 excl_dtl.area_class_exclusion_id,
936 excl_dtl.recovery_space_std_code,
937 excl_dtl.recovery_type_code
938 FROM pn_rec_arcl_all class,
939 pn_rec_arcl_exc_all excl_dtl
940 WHERE class.area_class_id = excl_dtl.area_class_id (+)
941 AND class.area_class_id = p_area_class_id;
942
943 CURSOR get_location_info(
944 p_location_id pn_locations.location_id%TYPE,
945 p_property_id pn_locations.property_id%TYPE) IS
946 SELECT location_id,
947 property_id,
948 active_start_date,
949 active_end_date,
950 assignable_area
951 FROM pn_locations_all
952 WHERE location_type_lookup_code IN ('SECTION','OFFICE')
953 AND active_start_date < fnd_date.canonical_to_date(p_to_date)
954 AND active_end_date > fnd_date.canonical_to_date(p_from_date)
955 AND location_id IN
956 (SELECT location_id FROM pn_locations_all
957 START WITH (location_id = p_location_id OR
958 (property_id = p_property_id AND p_location_id IS NULL))
959 CONNECT BY PRIOR location_id = parent_location_id)
960 ORDER BY location_id;
961
962 CURSOR get_cust_assignment_info(p_location_id pn_locations.location_id%TYPE) IS
963 SELECT cust_space_assign_id,
964 cust_account_id,
965 allocated_area,
966 cust_assign_start_date,
967 fin_oblig_end_date,
968 lease_id,
969 recovery_type_code,
970 recovery_space_std_code
971 FROM pn_space_assign_cust_all cust
972 WHERE cust.location_id = p_location_id
973 AND cust.cust_assign_start_date < fnd_date.canonical_to_date(p_to_date)
974 AND cust.fin_oblig_end_date > fnd_date.canonical_to_date(p_from_date);
975
976 CURSOR get_uom_info_from_prop(p_property_id pn_locations.property_id%TYPE) IS
977 SELECT uom_code
981 AND active_end_date > fnd_date.canonical_to_date(p_from_date);
978 FROM pn_locations_all
979 WHERE property_id = p_property_id
980 AND active_start_date < fnd_date.canonical_to_date(p_to_date)
982
983 CURSOR get_uom_info_from_locn(p_location_id pn_locations.location_id%TYPE) IS
984 SELECT uom_code
985 FROM pn_locations_all
986 WHERE location_id IN
987 (SELECT location_id FROM pn_locations_all
988 START WITH location_id = p_location_id
989 CONNECT BY location_id = PRIOR parent_location_id)
990 AND parent_location_id IS NULL
991 AND active_start_date < fnd_date.canonical_to_date(p_to_date)
992 AND active_end_date > fnd_date.canonical_to_date(p_from_date);
993
994 CURSOR get_ovr_from_curnt IS
995 SELECT dtl.location_id,
996 dtl.from_date,
997 dtl.to_date,
998 dtl.cust_account_id,
999 dtl.weighted_avg_ovr,
1000 dtl.occupied_area_ovr,
1001 dtl.assigned_area_ovr,
1002 dtl.exclude_prorata_flag,
1003 dtl.exclude_prorata_ovr_flag,
1004 dtl.exclude_area_flag,
1005 dtl.exclude_area_ovr_flag,
1006 dtl.recovery_space_std_code,
1007 dtl.recovery_type_code,
1008 dtl.area_class_dtl_line_id,
1009 hdr.area_class_dtl_id,
1010 hdr.status,
1011 setup.area_class_name
1012 FROM pn_rec_arcl_dtlln_all dtl,
1013 pn_rec_arcl_dtl_all hdr,
1014 pn_rec_arcl_all setup
1015 WHERE dtl.area_class_dtl_id (+) = hdr.area_class_dtl_id
1016 AND hdr.area_class_id = p_area_class_id
1017 AND TRUNC(hdr.as_of_date) = TRUNC(fnd_date.canonical_to_date(p_as_of_date))
1018 AND TRUNC(hdr.from_date) = TRUNC(fnd_date.canonical_to_date(p_from_date))
1019 AND TRUNC(hdr.to_date) = TRUNC(fnd_date.canonical_to_date(p_to_date))
1020 AND setup.area_class_id = hdr.area_class_id;
1021
1022 CURSOR get_prior_cls_dtl_id IS
1023 SELECT area_class_dtl_id
1024 FROM pn_rec_arcl_dtl_all
1025 WHERE from_date < fnd_date.canonical_to_date(p_to_date)
1026 AND to_date <= fnd_date.canonical_to_date(p_to_date)
1027 AND as_of_date < fnd_date.canonical_to_date(p_as_of_date)
1028 AND area_class_id = p_area_class_id
1029 ORDER BY as_of_date DESC, to_date DESC , from_date DESC;
1030
1031 CURSOR get_ovr_from_prior(p_prior_cls_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE) IS
1032 SELECT location_id,
1033 from_date,
1034 to_date,
1035 cust_account_id,
1036 weighted_avg_ovr,
1037 occupied_area_ovr,
1038 assigned_area_ovr,
1039 exclude_prorata_flag,
1040 exclude_prorata_ovr_flag,
1041 exclude_area_flag,
1042 exclude_area_ovr_flag,
1043 recovery_space_std_code,
1044 recovery_type_code
1045 FROM pn_rec_arcl_dtlln_all
1046 WHERE area_class_dtl_id = p_prior_cls_dtl_id;
1047
1048 l_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1049 l_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE;
1050 l_dummy_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1051 l_count NUMBER;
1052 l_data_tbl_counter NUMBER;
1053 l_regenerate VARCHAR2(1);
1054 l_found BOOLEAN;
1055 l_token VARCHAR2(100);
1056 l_is_assigned BOOLEAN;
1057 l_meets_criteria BOOLEAN;
1058 l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;
1059 l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1060 l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1061 l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1062 l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1063 l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1064 l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1065 l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1066 l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1067 l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1068 l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1069 l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1070 l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1071 l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1072 l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1073 l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;
1074 l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;
1075 l_vacancy_num_table number_table_type;
1076 l_vacancy_date_table date_table_type;
1077 l_ref_vacancy_num_table number_table_type;
1078 l_ref_vacancy_date_table date_table_type;
1079 l_area_cls_ln_data_tbl area_cls_line_dtl_tbl;
1080 l_area_cls_ln_curnt_ovr area_cls_line_dtl_tbl;
1081 l_area_cls_ln_prior_ovr area_cls_line_dtl_tbl;
1082 l_area_total_tbl area_cls_line_hdr_tbl;
1083 l_arcl_locid pn_rec_arcl.location_id%TYPE;
1084 l_arcl_propid pn_rec_arcl.property_id%TYPE;
1085 l_arcl_exc_table area_cls_exc_tbl;
1086 l_uom_code pn_locations_all.uom_code%TYPE;
1087 l_info VARCHAR2(100);
1091 BEGIN
1088 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_area' ;
1089
1090
1092
1093 pnp_debug_pkg.log(l_desc ||' (+)');
1094
1095 fnd_message.set_name('PN','PN_REC_ARCL_DTL_CP_INFO');
1096 fnd_message.set_token('ARCL' , to_char(p_area_class_id));
1097 fnd_message.set_token('STR' , p_from_date);
1098 fnd_message.set_token('END' , p_to_date);
1099 fnd_message.set_token('AOD' , p_as_of_date);
1100 fnd_message.set_token('OVR' , p_keep_override);
1101 pnp_debug_pkg.put_log_msg('');
1102 pnp_debug_pkg.put_log_msg(fnd_message.get);
1103 pnp_debug_pkg.put_log_msg('');
1104
1105 l_vacancy_date_table.delete;
1106 l_vacancy_num_table.delete;
1107 l_ref_vacancy_date_table.delete;
1108 l_ref_vacancy_num_table.delete;
1109
1110 l_area_cls_ln_curnt_ovr.delete;
1111 l_area_cls_ln_prior_ovr.delete;
1112 l_area_cls_ln_data_tbl.delete;
1113
1114 l_arcl_exc_table.delete;
1115
1116 l_area_total_tbl(0).total_assignable_area := 0;
1117 l_area_total_tbl(0).total_occupied_area := 0;
1118 l_area_total_tbl(0).total_occupied_area_ovr := 0;
1119 l_area_total_tbl(0).total_occupied_area_exc := 0;
1120 l_area_total_tbl(0).total_vacant_area := 0;
1121 l_area_total_tbl(0).total_vacant_area_ovr := 0;
1122 l_area_total_tbl(0).total_vacant_area_exc := 0;
1123 l_area_total_tbl(0).total_weighted_avg := 0;
1124 l_area_total_tbl(0).total_weighted_avg_ovr := 0;
1125 l_area_total_tbl(0).total_weighted_avg_exc := 0;
1126
1127 l_info:= ' fetching area class information';
1128 pnp_debug_pkg.log(l_info);
1129
1130 FOR area_class_rec IN get_area_class_info LOOP
1131 IF l_arcl_locid IS NULL THEN l_arcl_locid := area_class_rec.location_id; END IF;
1132 IF l_arcl_propid IS NULL THEN l_arcl_propid := area_class_rec.property_id; END IF;
1133
1134 l_count := l_arcl_exc_table.COUNT;
1135
1136 l_arcl_exc_table(l_count).recovery_space_std_code := area_class_rec.recovery_space_std_code;
1137 l_arcl_exc_table(l_count).recovery_type_code := area_class_rec.recovery_type_code;
1138 l_arcl_exc_table(l_count).exclusion_type_code := area_class_rec.exclusion_type_code;
1139 l_arcl_exc_table(l_count).relational_code := area_class_rec.relational_code;
1140 l_arcl_exc_table(l_count).area := area_class_rec.area;
1141
1142 END LOOP;
1143
1144 l_info := ' validating UOM is unique for location and property';
1145 pnp_debug_pkg.log(l_info);
1146
1147 IF l_arcl_propid IS NOT NULL AND l_arcl_locid IS NULL THEN
1148 FOR validate_rec IN get_uom_info_from_prop(l_arcl_propid) LOOP
1149 IF l_uom_code IS NULL THEN l_uom_code := validate_rec.uom_code;
1150 ELSIF l_uom_code <> validate_rec.uom_code THEN
1151 fnd_message.set_name('PN', 'PN_REC_UOM_MULTIPLE');
1152 RAISE uom_exception;
1153 END IF;
1154 END LOOP;
1155 ELSIF l_arcl_locid IS NOT NULL THEN
1156 FOR validate_rec IN get_uom_info_from_locn(l_arcl_locid) LOOP
1157 IF l_uom_code IS NULL THEN l_uom_code := validate_rec.uom_code;
1158 ELSIF l_uom_code <> validate_rec.uom_code THEN
1159 fnd_message.set_name('PN', 'PN_REC_UOM_MULTIPLE');
1160 RAISE uom_exception;
1161 END IF;
1162 END LOOP;
1163 END IF;
1164
1165 l_info := ' finding overrides and processing header information ';
1166 pnp_debug_pkg.log(l_info);
1167
1168 l_regenerate := 'Y';
1169 l_count := -1;
1170
1171 FOR get_ovr_rec IN get_ovr_from_curnt LOOP
1172
1173 IF get_ovr_rec.status = 'LOCKED' THEN
1174 fnd_message.set_name('PN','PN_REC_ARCL_DTL');
1175 l_token := fnd_message.get;
1176 fnd_message.set_name('PN','PN_REC_NO_REGEN_LOCKED');
1177 fnd_message.set_token('MODULE',l_token);
1178 fnd_message.set_token('FDATE', p_from_date);
1179 fnd_message.set_token('TDATE', p_to_date);
1180 fnd_message.set_token('AODATE', p_as_of_date);
1181 fnd_message.set_token('NAME', get_ovr_rec.area_class_name);
1182 pnp_debug_pkg.log(fnd_message.get);
1183 RETURN;
1184 END IF;
1185
1186 l_count := l_area_cls_ln_curnt_ovr.COUNT;
1187
1188 l_area_cls_ln_curnt_ovr(l_count).location_id := get_ovr_rec.location_id;
1189 l_area_cls_ln_curnt_ovr(l_count).from_date := get_ovr_rec.from_date;
1190 l_area_cls_ln_curnt_ovr(l_count).to_date := get_ovr_rec.to_date;
1191 l_area_cls_ln_curnt_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
1192 l_area_cls_ln_curnt_ovr(l_count).weighted_avg_ovr := get_ovr_rec.weighted_avg_ovr;
1193 l_area_cls_ln_curnt_ovr(l_count).occupied_area_ovr := get_ovr_rec.occupied_area_ovr;
1194 l_area_cls_ln_curnt_ovr(l_count).assigned_area_ovr := get_ovr_rec.assigned_area_ovr;
1195 l_area_cls_ln_curnt_ovr(l_count).exclude_prorata_flag := get_ovr_rec.exclude_prorata_flag;
1196 l_area_cls_ln_curnt_ovr(l_count).exclude_prorata_ovr_flag:= get_ovr_rec.exclude_prorata_ovr_flag;
1197 l_area_cls_ln_curnt_ovr(l_count).exclude_area_flag := get_ovr_rec.exclude_area_flag;
1198 l_area_cls_ln_curnt_ovr(l_count).exclude_area_ovr_flag := get_ovr_rec.exclude_area_ovr_flag;
1199 l_area_cls_ln_curnt_ovr(l_count).recovery_space_std_code := get_ovr_rec.recovery_space_std_code;
1203 l_area_class_dtl_id := get_ovr_rec.area_class_dtl_id;
1200 l_area_cls_ln_curnt_ovr(l_count).recovery_type_code := get_ovr_rec.recovery_type_code;
1201 l_area_cls_ln_curnt_ovr(l_count).area_class_dtl_line_id := get_ovr_rec.area_class_dtl_line_id;
1202
1204
1205 END LOOP;
1206
1207 IF l_area_class_dtl_id IS NULL THEN
1208 l_regenerate := 'N';
1209
1210 END IF;
1211
1212 l_info := ' getting prior cls dtl id for overrides';
1213 pnp_debug_pkg.log(l_info);
1214
1215 FOR get_first_id IN get_prior_cls_dtl_id LOOP
1216 l_dummy_id := get_first_id.area_class_dtl_id;
1217 exit;
1218 END LOOP;
1219
1220 FOR get_ovr_rec IN get_ovr_from_prior(l_dummy_id) LOOP
1221
1222 l_count := l_area_cls_ln_prior_ovr.COUNT;
1223
1224 l_area_cls_ln_prior_ovr(l_count).location_id := get_ovr_rec.location_id;
1225 l_area_cls_ln_prior_ovr(l_count).from_date := get_ovr_rec.from_date;
1226 l_area_cls_ln_prior_ovr(l_count).to_date := get_ovr_rec.to_date;
1227 l_area_cls_ln_prior_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
1228 l_area_cls_ln_prior_ovr(l_count).weighted_avg_ovr := get_ovr_rec.weighted_avg_ovr;
1229 l_area_cls_ln_prior_ovr(l_count).occupied_area_ovr := get_ovr_rec.occupied_area_ovr;
1230 l_area_cls_ln_prior_ovr(l_count).assigned_area_ovr := get_ovr_rec.assigned_area_ovr;
1231 l_area_cls_ln_prior_ovr(l_count).exclude_prorata_flag := get_ovr_rec.exclude_prorata_flag;
1232 l_area_cls_ln_prior_ovr(l_count).exclude_prorata_ovr_flag:= get_ovr_rec.exclude_prorata_ovr_flag;
1233 l_area_cls_ln_prior_ovr(l_count).exclude_area_flag := get_ovr_rec.exclude_area_flag;
1234 l_area_cls_ln_prior_ovr(l_count).exclude_area_ovr_flag := get_ovr_rec.exclude_area_ovr_flag;
1235 l_area_cls_ln_prior_ovr(l_count).recovery_space_std_code := get_ovr_rec.recovery_space_std_code;
1236 l_area_cls_ln_prior_ovr(l_count).recovery_type_code := get_ovr_rec.recovery_type_code;
1237
1238 END LOOP;
1239
1240 FOR location_rec IN get_location_info(l_arcl_locid, l_arcl_propid) LOOP
1241
1242 l_info := ' processing location id: '|| location_rec.location_id ||' ';
1243 pnp_debug_pkg.log(l_info);
1244
1245 l_is_assigned := FALSE;
1246
1247 IF l_temp_loc_id IS NULL OR
1248 l_temp_loc_id <> location_rec.location_id THEN
1249
1250 IF l_temp_loc_id IS NOT NULL THEN
1251
1252 -- generate vacancy data for that location id
1253
1254 l_info := ' inserting vacancy data into details table for location: '||l_temp_loc_id||' ';
1255 pnp_debug_pkg.log(l_info);
1256
1257 insert_vacancy_data(p_location_id => l_temp_loc_id,
1258 p_property_id => l_temp_prop_id,
1259 p_date_table => l_vacancy_date_table,
1260 p_num_table => l_vacancy_num_table,
1261 p_ref_date_table => l_ref_vacancy_date_table,
1262 p_ref_num_table => l_ref_vacancy_num_table,
1263 p_from_date => fnd_date.canonical_to_date(p_from_date),
1264 p_to_date => fnd_date.canonical_to_date(p_to_date),
1265 p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1266 p_assignable_area => l_temp_assignable_area,
1267 p_curnt_ovr => l_area_cls_ln_curnt_ovr,
1268 p_prior_ovr => l_area_cls_ln_prior_ovr,
1269 p_data_tbl => l_area_cls_ln_data_tbl,
1270 p_total_tbl => l_area_total_tbl,
1271 p_keep_override => p_keep_override,
1272 p_regenerate => l_regenerate
1273 );
1274
1275 /* reset */
1276
1277 l_vacancy_num_table.delete;
1278 l_vacancy_date_table.delete;
1279 l_ref_vacancy_num_table.delete;
1280 l_ref_vacancy_date_table.delete;
1281
1282 END IF;
1283
1284 l_temp_loc_id := location_rec.location_id;
1285 l_temp_prop_id := location_rec.property_id;
1286 l_temp_assignable_area := location_rec.assignable_area;
1287
1288 -- process data for vacancy details purposes
1289
1290 FOR space_assign_rec IN get_cust_assignment_info(location_rec.location_id) LOOP
1291 l_is_assigned := TRUE;
1292
1293 l_info := ' checking whether space assignment: '|| space_assign_rec.cust_space_assign_id ||
1294 ' meets exclusion criteria ';
1295 pnp_debug_pkg.log(l_info);
1296
1297 l_meets_criteria := FALSE;
1298
1299 FOR i IN 0 .. l_arcl_exc_table.COUNT - 1 LOOP
1300 IF l_arcl_exc_table(i).recovery_type_code = space_assign_rec.recovery_type_code AND
1301 l_arcl_exc_table(i).recovery_space_std_code = space_assign_rec.recovery_space_std_code THEN
1302
1303 IF l_arcl_exc_table(i).relational_code = 'EQ' THEN
1304 IF location_rec.assignable_area = l_arcl_exc_table(i).area THEN l_meets_criteria := TRUE; END IF;
1305 ELSIF l_arcl_exc_table(i).relational_code = 'GT' THEN
1309 ELSIF l_arcl_exc_table(i).relational_code = 'GE' THEN
1306 IF location_rec.assignable_area > l_arcl_exc_table(i).area THEN l_meets_criteria := TRUE; END IF;
1307 ELSIF l_arcl_exc_table(i).relational_code = 'LT' THEN
1308 IF location_rec.assignable_area < l_arcl_exc_table(i).area THEN l_meets_criteria := TRUE; END IF;
1310 IF location_rec.assignable_area >= l_arcl_exc_table(i).area THEN l_meets_criteria := TRUE; END IF;
1311 ELSIF l_arcl_exc_table(i).relational_code = 'LE' THEN
1312 IF location_rec.assignable_area <= l_arcl_exc_table(i).area THEN l_meets_criteria := TRUE; END IF;
1313 ELSIF l_arcl_exc_table(i).relational_code IS NULL THEN
1314 l_meets_criteria := TRUE;
1315 END IF;
1316
1317 l_excl_type := l_arcl_exc_table(i).exclusion_type_code;
1318 exit;
1319
1320 END IF;
1321 END LOOP;
1322
1323 -- put in assignments for the occupancy details
1324
1325 l_from_date := space_assign_rec.cust_assign_start_date;
1326 l_to_date := space_assign_rec.fin_oblig_end_date;
1327 l_info := ' getting details for cust assignment :'||space_assign_rec.cust_space_assign_id||' ';
1328 pnp_debug_pkg.log(l_info);
1329
1330 IF NOT l_meets_criteria THEN l_excl_type := null; END IF;
1331
1332 get_area_cls_dtl_calc(
1333 p_from_date => fnd_date.canonical_to_date(p_from_date),
1334 p_to_date => fnd_date.canonical_to_date(p_to_date),
1335 p_rec_from_date => l_from_date,
1336 p_rec_to_date => l_to_date,
1337 p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1338 p_assigned_area => space_assign_rec.allocated_area,
1339 p_exc_type_code => l_excl_type,
1340 p_occup_pct => l_occup_pct,
1341 p_weighted_avg => l_weighted_avg,
1342 p_occup_area => l_occup_area,
1343 p_exc_prorata_flag => l_excl_prorata_flag,
1344 p_exc_area_flag => l_excl_area_flag,
1345 p_include_flag => l_include_flag);
1346
1347 l_info := ' finding overrides and processing into pl/sql table the details of '||
1348 'cust assignment:'||space_assign_rec.cust_space_assign_id||' ';
1349 pnp_debug_pkg.log(l_info);
1350
1351 l_found := FALSE;
1352
1353 IF l_regenerate = 'Y' THEN
1354
1355 find_area_ovr_values(
1356 p_ovr => l_area_cls_ln_curnt_ovr,
1357 p_loc_id => location_rec.location_id,
1358 p_cust_id => space_assign_rec.cust_account_id,
1359 p_from => l_from_date,
1360 p_to => l_to_date,
1361 p_weighted_avg_ovr => l_weighted_avg_ovr,
1362 p_occupied_area_ovr => l_occup_area_ovr,
1363 p_assigned_area_ovr => l_assigned_area_ovr,
1364 p_exc_area_ovr_flag => l_excl_area_ovr_flag,
1365 p_exc_prorata_ovr_flag => l_excl_prorata_ovr_flag,
1366 p_area_cls_dtl_line_id => l_area_class_dtl_line_id,
1367 p_found => l_found,
1368 p_keep_override => p_keep_override
1369 );
1370
1371 END IF;
1372
1373 IF NOT l_found THEN
1374
1375 IF l_regenerate = 'N' THEN l_found := null; END IF;
1376
1377 find_area_ovr_values(
1378 p_ovr => l_area_cls_ln_prior_ovr,
1379 p_loc_id => location_rec.location_id,
1380 p_cust_id => space_assign_rec.cust_account_id,
1381 p_from => l_from_date,
1382 p_to => l_to_date,
1383 p_weighted_avg_ovr => l_weighted_avg_ovr,
1384 p_occupied_area_ovr => l_occup_area_ovr,
1385 p_assigned_area_ovr => l_assigned_area_ovr,
1386 p_exc_area_ovr_flag => l_excl_area_ovr_flag,
1387 p_exc_prorata_ovr_flag => l_excl_prorata_ovr_flag,
1388 p_area_cls_dtl_line_id => l_area_class_dtl_line_id,
1389 p_found => l_found,
1390 p_keep_override => p_keep_override
1391 );
1392
1393 END IF;
1394
1395 l_data_tbl_counter := l_area_cls_ln_data_tbl.COUNT;
1396
1397 -- if there are no overrides, then the value is null, for which we default to be equal the normal
1398
1399 IF l_excl_prorata_ovr_flag IS NULL THEN l_excl_prorata_ovr_flag := l_excl_prorata_flag; END IF;
1400 IF l_excl_area_ovr_flag IS NULL THEN l_excl_area_ovr_flag := l_excl_area_flag; END IF;
1401
1402 IF l_include_flag = 'Y' AND nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'N' THEN
1403
1404 l_info := ' figuring totals ';
1405 pnp_debug_pkg.log(l_info);
1406
1407 l_area_total_tbl(0).total_assignable_area := l_area_total_tbl(0).total_assignable_area +
1408 nvl(location_rec.assignable_area,0);
1412 l_area_total_tbl(0).total_vacant_area := l_area_total_tbl(0).total_vacant_area +
1409 l_area_total_tbl(0).total_occupied_area := l_area_total_tbl(0).total_occupied_area + nvl(l_occup_area,0);
1410 l_area_total_tbl(0).total_occupied_area_ovr := l_area_total_tbl(0).total_occupied_area_ovr +
1411 nvl(nvl(l_occup_area_ovr, l_occup_area),0);
1413 nvl(location_rec.assignable_area,0) - nvl(l_occup_area,0);
1414 l_area_total_tbl(0).total_vacant_area_ovr := l_area_total_tbl(0).total_vacant_area_ovr +
1415 nvl(location_rec.assignable_area - l_occup_area_ovr,
1416 (nvl(location_rec.assignable_area,0) - nvl(l_occup_area,0)));
1417 l_area_total_tbl(0).total_weighted_avg := l_area_total_tbl(0).total_weighted_avg + nvl(l_weighted_avg, 0);
1418 l_area_total_tbl(0).total_weighted_avg_ovr := l_area_total_tbl(0).total_weighted_avg_ovr +
1419 nvl(nvl(l_weighted_avg_ovr, l_weighted_avg),0);
1420
1421 ELSIF l_include_flag = 'Y' AND nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'Y' THEN
1422
1423 l_info := ' figuring excluded totals ';
1424 pnp_debug_pkg.log(l_info);
1425
1426 l_area_total_tbl(0).total_occupied_area_exc := l_area_total_tbl(0).total_occupied_area_exc +
1427 nvl(nvl(l_occup_area_ovr, l_occup_area),0);
1428 l_area_total_tbl(0).total_vacant_area_exc := l_area_total_tbl(0).total_vacant_area_exc +
1429 nvl(location_rec.assignable_area - l_occup_area_ovr,
1430 (nvl(location_rec.assignable_area,0) - nvl(l_occup_area,0)));
1431 l_area_total_tbl(0).total_weighted_avg_exc := l_area_total_tbl(0).total_weighted_avg_exc +
1432 nvl(nvl(l_weighted_avg_ovr, l_weighted_avg),0);
1433
1434 END IF;
1435
1436 l_info := ' processing area cls detail information into pl/sql table ';
1437 pnp_debug_pkg.log(l_info);
1438
1439 l_area_cls_ln_data_tbl(l_data_tbl_counter).area_class_dtl_line_id := l_area_class_dtl_line_id;
1440 l_area_cls_ln_data_tbl(l_data_tbl_counter).from_date := l_from_date;
1441 l_area_cls_ln_data_tbl(l_data_tbl_counter).to_date := l_to_date;
1442 l_area_cls_ln_data_tbl(l_data_tbl_counter).location_id := location_rec.location_id;
1443 l_area_cls_ln_data_tbl(l_data_tbl_counter).property_id := location_rec.property_id;
1444 l_area_cls_ln_data_tbl(l_data_tbl_counter).cust_space_assign_id := space_assign_rec.cust_space_assign_id;
1445 l_area_cls_ln_data_tbl(l_data_tbl_counter).cust_account_id := space_assign_rec.cust_account_id;
1446 l_area_cls_ln_data_tbl(l_data_tbl_counter).lease_id := space_assign_rec.lease_id;
1447 l_area_cls_ln_data_tbl(l_data_tbl_counter).assignable_area := location_rec.assignable_area;
1448 l_area_cls_ln_data_tbl(l_data_tbl_counter).assigned_area := space_assign_rec.allocated_area;
1449 l_area_cls_ln_data_tbl(l_data_tbl_counter).assigned_area_ovr := l_assigned_area_ovr;
1450 l_area_cls_ln_data_tbl(l_data_tbl_counter).occupancy_pct := l_occup_pct;
1451 l_area_cls_ln_data_tbl(l_data_tbl_counter).occupied_area := l_occup_area;
1452 l_area_cls_ln_data_tbl(l_data_tbl_counter).occupied_area_ovr := l_occup_area_ovr;
1453 l_area_cls_ln_data_tbl(l_data_tbl_counter).vacant_area := location_rec.assignable_area - l_occup_area;
1454 l_area_cls_ln_data_tbl(l_data_tbl_counter).vacant_area_ovr := location_rec.assignable_area - l_occup_area_ovr;
1455 l_area_cls_ln_data_tbl(l_data_tbl_counter).weighted_avg := l_weighted_avg;
1456 l_area_cls_ln_data_tbl(l_data_tbl_counter).weighted_avg_ovr := l_weighted_avg_ovr;
1457 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_area_flag := l_excl_area_flag;
1458 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_area_ovr_flag := l_excl_area_ovr_flag;
1459 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_prorata_flag := l_excl_prorata_flag;
1460 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_prorata_ovr_flag := l_excl_prorata_ovr_flag;
1461 l_area_cls_ln_data_tbl(l_data_tbl_counter).include_flag := l_include_flag;
1462 l_area_cls_ln_data_tbl(l_data_tbl_counter).recovery_space_std_code := space_assign_rec.recovery_space_std_code;
1463 l_area_cls_ln_data_tbl(l_data_tbl_counter).recovery_type_code := space_assign_rec.recovery_type_code;
1464
1465 -- collect data
1466
1467 l_info := ' processing vacancy for cust assignment:'||space_assign_rec.cust_space_assign_id||' ';
1468 pnp_debug_pkg.log(l_info);
1469
1470 process_vacancy(
1471 p_start_date => l_from_date,
1472 p_end_date => l_to_date,
1473 p_area => space_assign_rec.allocated_area,
1474 p_date_table => l_vacancy_date_table,
1475 p_number_table => l_vacancy_num_table,
1476 p_add => FALSE);
1477
1478 END LOOP;
1479
1480 l_info := ' processing vacancy for location for data table';
1484 p_start_date => location_rec.active_start_date,
1481 pnp_debug_pkg.log(l_info);
1482
1483 process_vacancy(
1485 p_end_date => location_rec.active_end_date,
1486 p_area => NVL(location_rec.assignable_area,0),
1487 p_date_table => l_vacancy_date_table,
1488 p_number_table => l_vacancy_num_table,
1489 p_add => TRUE);
1490
1491 l_info := ' processing vacancy for location for reference table';
1492 pnp_debug_pkg.log(l_info);
1493
1494 process_vacancy(
1495 p_start_date => location_rec.active_start_date,
1496 p_end_date => location_rec.active_end_date,
1497 p_area => NVL(location_rec.assignable_area,0),
1498 p_date_table => l_ref_vacancy_date_table,
1499 p_number_table => l_ref_vacancy_num_table,
1500 p_add => TRUE);
1501
1502 END IF;
1503
1504 END LOOP;
1505
1506 l_info := ' inserting vacancy data for last location id';
1507 pnp_debug_pkg.log(l_info);
1508
1509 insert_vacancy_data(
1510 p_location_id => l_temp_loc_id,
1511 p_property_id => l_temp_prop_id,
1512 p_date_table => l_vacancy_date_table,
1513 p_num_table => l_vacancy_num_table,
1514 p_ref_date_table => l_ref_vacancy_date_table,
1515 p_ref_num_table => l_ref_vacancy_num_table,
1516 p_from_date => fnd_date.canonical_to_date(p_from_date),
1517 p_to_date => fnd_date.canonical_to_date(p_to_date),
1518 p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1519 p_assignable_area => l_temp_assignable_area,
1520 p_curnt_ovr => l_area_cls_ln_curnt_ovr,
1521 p_prior_ovr => l_area_cls_ln_prior_ovr,
1522 p_data_tbl => l_area_cls_ln_data_tbl,
1523 p_total_tbl => l_area_total_tbl,
1524 p_keep_override => p_keep_override,
1525 p_regenerate => l_regenerate
1526 );
1527
1528 l_temp_loc_id := null;
1529 l_temp_prop_id := null;
1530 l_vacancy_date_table.delete;
1531 l_vacancy_num_table.delete;
1532 l_ref_vacancy_date_table.delete;
1533 l_ref_vacancy_num_table.delete;
1534
1535 l_info := ' processing header data ';
1536 pnp_debug_pkg.log(l_info);
1537
1538 IF l_area_class_dtl_id IS NOT NULL THEN
1539
1540 pn_rec_arcl_dtl_pkg.update_row(
1541 x_area_class_id => p_area_class_id,
1542 x_area_class_dtl_id => l_area_class_dtl_id,
1543 x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1544 x_from_date => fnd_date.canonical_to_date(p_from_date),
1545 x_to_date => fnd_date.canonical_to_date(p_to_date),
1546 x_status => 'OPEN',
1547 x_ttl_assignable_area => l_area_total_tbl(0).total_assignable_area,
1548 x_ttl_occupied_area => l_area_total_tbl(0).total_occupied_area,
1549 x_ttl_occupied_area_ovr => l_area_total_tbl(0).total_occupied_area_ovr,
1550 x_ttl_occupied_area_exc => l_area_total_tbl(0).total_occupied_area_exc,
1551 x_ttl_vacant_area => l_area_total_tbl(0).total_vacant_area,
1552 x_ttl_vacant_area_ovr => l_area_total_tbl(0).total_vacant_area_ovr,
1553 x_ttl_vacant_area_exc => l_area_total_tbl(0).total_vacant_area_exc,
1554 x_ttl_weighted_avg => l_area_total_tbl(0).total_weighted_avg,
1555 x_ttl_weighted_avg_ovr => l_area_total_tbl(0).total_weighted_avg_ovr,
1556 x_ttl_weighted_avg_exc => l_area_total_tbl(0).total_weighted_avg_exc,
1557 x_last_update_date => SYSDATE,
1558 x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
1559 x_creation_date => SYSDATE,
1560 x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
1561 x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
1562 );
1563 ELSE
1564 pn_rec_arcl_dtl_pkg.insert_row(
1565 x_org_id => pn_mo_cache_utils.get_current_org_id,
1566 x_area_class_id => p_area_class_id,
1567 x_area_class_dtl_id => l_area_class_dtl_id,
1568 x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1569 x_from_date => fnd_date.canonical_to_date(p_from_date),
1570 x_to_date => fnd_date.canonical_to_date(p_to_date),
1571 x_status => 'OPEN',
1572 x_ttl_assignable_area => l_area_total_tbl(0).total_assignable_area,
1573 x_ttl_occupied_area => l_area_total_tbl(0).total_occupied_area,
1574 x_ttl_occupied_area_ovr => l_area_total_tbl(0).total_occupied_area_ovr,
1575 x_ttl_occupied_area_exc => l_area_total_tbl(0).total_occupied_area_exc,
1576 x_ttl_vacant_area => l_area_total_tbl(0).total_vacant_area,
1577 x_ttl_vacant_area_ovr => l_area_total_tbl(0).total_vacant_area_ovr,
1578 x_ttl_vacant_area_exc => l_area_total_tbl(0).total_vacant_area_exc,
1579 x_ttl_weighted_avg => l_area_total_tbl(0).total_weighted_avg,
1580 x_ttl_weighted_avg_ovr => l_area_total_tbl(0).total_weighted_avg_ovr,
1581 x_ttl_weighted_avg_exc => l_area_total_tbl(0).total_weighted_avg_exc,
1582 x_last_update_date => SYSDATE,
1583 x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
1584 x_creation_date => SYSDATE,
1588 END IF;
1585 x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
1586 x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
1587 );
1589
1590 l_info := ' dumping data from pl/sql table ';
1591 pnp_debug_pkg.log(l_info);
1592
1593 process_area_class_line_data(
1594 p_old_data => l_area_cls_ln_curnt_ovr,
1595 p_new_data => l_area_cls_ln_data_tbl,
1596 p_hdr_id => l_area_class_dtl_id
1597 );
1598
1599 pnp_debug_pkg.log(l_desc ||' (-)');
1600
1601 EXCEPTION
1602 WHEN uom_exception THEN
1603 pnp_debug_pkg.log(fnd_message.get);
1604 raise;
1605 WHEN OTHERS THEN
1606 fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
1607 pnp_debug_pkg.put_log_msg(fnd_message.get);
1608 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1609 raise;
1610 END extract_area;
1611
1612 ---------------------------- EXPENSE VALIDATIONS -----------------------------+
1613
1614 ------------------------------------------------------------------------------+
1615 -- FUNCTION : check_dates
1616 -- DESCRIPTION: checks that from date and to date does not overlap for a given
1617 -- location / prop id extraction.
1618 -- NOTE : from_date < as_of_date < to_date is NOT checked.
1619 -- HISTORY:
1620 -- 19-MAR-03 ftanudja o created
1621 -- 02-JUL-03 ftanudja o added constraint p_extr code <> null
1622 ------------------------------------------------------------------------------+
1623
1624 FUNCTION check_dates(
1625 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
1626 p_from_date pn_rec_exp_line.from_date%TYPE,
1627 p_to_date pn_rec_exp_line.to_date%TYPE,
1628 p_property_id pn_rec_exp_line.property_id%TYPE,
1629 p_location_id pn_rec_exp_line.location_id%TYPE,
1630 p_extract_code pn_rec_exp_line.expense_extract_code%TYPE
1631 ) RETURN BOOLEAN
1632 IS
1633 l_result BOOLEAN := TRUE;
1634
1635 CURSOR line_check_crossing_dates IS
1636 SELECT 'TRUE' FROM pn_rec_exp_line
1637 WHERE ((p_from_date BETWEEN from_date AND to_date) OR
1638 (p_to_date BETWEEN from_date AND to_date))
1639 AND (((location_id = p_location_id) OR (location_id IS NULL AND p_location_id IS NULL)) OR
1640 (((property_id = p_property_id) OR (property_id IS NULL AND p_property_id IS NULL))
1641 AND location_id IS NULL))
1642 AND p_extract_code IS NOT NULL
1643 AND expense_extract_code <> p_extract_code
1644 AND rownum < 2;
1645
1646 BEGIN
1647 IF p_from_date > p_to_date THEN
1648 l_result:= FALSE;
1649 END IF;
1650
1651 FOR check_valid IN line_check_crossing_dates LOOP l_result := FALSE; END LOOP;
1652
1653 RETURN l_result;
1654 END;
1655
1656 ------------------------------------------------------------------------------+
1657 -- FUNCTION : check_loc_n_prop_id
1658 -- DESCRIPTION:
1659 -- 1. checks that location id is valid.
1660 -- 2. checks that property id is valid.
1661 -- 3. checks that location id / property id combination valid
1662 -- HISTORY:
1663 -- 19-MAR-03 ftanudja o created
1664 -- 06-OCT-07 bifernan o Modified cursor prop_loc_combo_check for bug 6461211
1665 ------------------------------------------------------------------------------+
1666
1667 FUNCTION check_loc_n_prop_id(
1668 p_location_id pn_locations.location_id%TYPE,
1669 p_property_id pn_locations.property_id%TYPE
1670 ) RETURN BOOLEAN
1671 IS
1672 l_result BOOLEAN:= FALSE;
1673
1674 CURSOR loc_check IS
1675 SELECT 'TRUE' FROM pn_locations_all WHERE location_id = p_location_id;
1676
1677 CURSOR prop_check IS
1678 SELECT 'TRUE' FROM pn_properties_all WHERE property_id = p_property_id;
1679
1680 /* Commented and modified for Bug 6461211
1681 CURSOR prop_loc_combo_check IS
1682 SELECT 'TRUE' FROM pn_locations_all WHERE location_id = p_location_id AND property_id = p_property_id; */
1683
1684 CURSOR prop_loc_combo_check IS
1685 SELECT 'TRUE' FROM pn_locations_all
1686 WHERE property_id = p_property_id
1687 START WITH location_id = p_location_id
1688 CONNECT BY PRIOR parent_location_id = location_id;
1689
1690 BEGIN
1691 IF p_location_id IS NOT NULL THEN
1692
1693 FOR loc_rec IN loc_check LOOP l_result := TRUE; exit; END LOOP;
1694
1695 IF p_property_id IS NOT NULL THEN
1696
1697 l_result:= FALSE;
1698 FOR loc_rec IN prop_loc_combo_check LOOP l_result := TRUE; exit;END LOOP;
1699
1700 END IF;
1701
1702 ELSIF p_property_id IS NOT NULL THEN
1703
1704 FOR prop_rec IN prop_check LOOP l_result := TRUE; exit; END LOOP;
1705
1706 END IF;
1707
1708 return l_result;
1709 END;
1710
1711 ------------------------------------------------------------------------------+
1712 -- FUNCTION : check_expense_type
1713 -- DESCRIPTION: checks that expense_type is valid
1714 -- HISTORY:
1715 -- 19-MAR-03 ftanudja o created
1716 ------------------------------------------------------------------------------+
1717
1718 FUNCTION check_expense_type(p_exp_type_code pn_rec_exp_itf.expense_type_code%TYPE)
1719 RETURN BOOLEAN
1720 IS
1721 CURSOR type_check IS
1722 SELECT 'TRUE' FROM fnd_lookups
1723 WHERE lookup_type = 'PN_PAYMENT_PURPOSE_TYPE' and lookup_code = p_exp_type_code;
1727 FOR type_rec IN type_check LOOP l_result := TRUE; exit; END LOOP;
1724
1725 l_result BOOLEAN := FALSE;
1726 BEGIN
1728 return l_result;
1729 END;
1730
1731 ------------------------------------------------------------------------------+
1732 -- FUNCTION : check_extract_code
1733 -- DESCRIPTION: checks extract_code from user
1734 -- HISTORY:
1735 -- 19-MAR-03 ftanudja o created
1736 ------------------------------------------------------------------------------+
1737
1738 FUNCTION check_extract_code(
1739 p_extract_code pn_rec_exp_line.expense_extract_code%TYPE,
1740 p_loc_id pn_rec_exp_line.location_id%TYPE,
1741 p_prop_id pn_rec_exp_line.property_id%TYPE,
1742 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
1743 p_from_date pn_rec_exp_line.from_date%TYPE,
1744 p_to_date pn_rec_exp_line.to_date%TYPE,
1745 p_currency pn_rec_exp_line.currency_code%TYPE)
1746 RETURN BOOLEAN
1747 IS
1748 CURSOR check_exists IS
1749 SELECT location_id,
1750 property_id,
1751 from_date,
1752 to_date,
1753 as_of_date,
1754 currency_code,
1755 org_id
1756 FROM pn_rec_exp_line_all
1757 WHERE expense_extract_code = p_extract_code;
1758
1759 l_exist BOOLEAN := FALSE;
1760 l_same BOOLEAN := FALSE;
1761 BEGIN
1762
1763 IF p_extract_code IS NOT NULL THEN
1764 FOR extract_rec IN check_exists LOOP
1765 l_exist := TRUE;
1766 IF ((extract_rec.location_id = p_loc_id) OR
1767 (extract_rec.location_id IS NULL AND p_loc_id IS NULL)) AND
1768 ((extract_rec.property_id = p_prop_id) OR
1769 (extract_rec.property_id IS NULL AND p_prop_id IS NULL)) AND
1770 TRUNC(extract_rec.from_date)= TRUNC(p_from_date) AND
1771 TRUNC(extract_rec.to_date) = TRUNC(p_to_date) AND
1772 extract_rec.currency_code = p_currency AND
1773 extract_rec.org_id = pn_mo_cache_utils.get_current_org_id THEN
1774 l_same := TRUE;
1775 END IF;
1776 END LOOP;
1777
1778 IF l_exist AND NOT l_same THEN
1779 RETURN FALSE;
1780 ELSE
1781 RETURN TRUE;
1782 END IF;
1783 ELSE
1784 RETURN FALSE;
1785 END IF;
1786 END;
1787
1788 ------------------------------------------------------------------------------+
1789 -- FUNCTION : check_account_id
1790 -- DESCRIPTION: checks whetever a given cc_id is valid
1791 -- HISTORY:
1792 -- 19-MAR-03 ftanudja o created
1793 ------------------------------------------------------------------------------+
1794
1795 FUNCTION check_account_id(p_cc_id pn_rec_exp_itf.expense_account_id%TYPE)
1796 RETURN BOOLEAN
1797 IS
1798 l_result BOOLEAN := FALSE;
1799 CURSOR ccid_check IS
1800 SELECT 'TRUE' FROM gl_code_combinations where code_combination_id = p_cc_id;
1801
1802 BEGIN
1803 FOR acct_rec IN ccid_check LOOP l_result:= TRUE; exit; END LOOP;
1804 RETURN l_result;
1805 END;
1806
1807 ------------------------------------------------------------------------------+
1808 -- FUNCTION : check_ccid_n_type
1809 -- DESCRIPTION:
1810 -- 1. checks for expense type and account id combination.
1811 -- 2. a given combination must exist only once given a certain from and to date
1812 --
1813 -- HISTORY:
1814 -- 19-MAR-03 ftanudja o created
1815 ------------------------------------------------------------------------------+
1816
1817 FUNCTION check_ccid_n_type(
1818 p_exp_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE,
1819 p_cc_id pn_rec_exp_line_dtl.expense_account_id%TYPE,
1820 p_from_date pn_rec_exp_line.from_date%TYPE,
1821 p_to_date pn_rec_exp_line.to_date%TYPE
1822 ) RETURN BOOLEAN
1823 IS
1824 l_result BOOLEAN := TRUE;
1825 CURSOR check_ccid_type IS
1826 SELECT 'EXISTS'
1827 FROM pn_rec_exp_line_dtl dtl,
1828 pn_rec_exp_line hdr
1829 WHERE dtl.expense_line_id = hdr.expense_line_id
1830 AND hdr.from_date = p_from_date
1831 AND hdr.to_date = p_to_date
1832 AND dtl.expense_type_code = p_exp_type_code
1833 AND dtl.expense_account_id = p_cc_id;
1834
1835
1836 BEGIN
1837 FOR acct_type_rec IN check_ccid_type LOOP l_result:= FALSE; exit; END LOOP;
1838 RETURN l_result;
1839 END;
1840
1841 ----------------------------- EXPENSE LINE -----------------------------------+
1842
1843 ------------------------------------------------------------------------------+
1844 -- PROCEDURE : validate_and_process_lines
1845 -- DESCRIPTION:
1846 -- 1. Given : some expense line data.
1847 -- 2. Check validity of each line and put them into expense lines table.
1848 -- 3. Return status flag.
1849 --
1850 -- HISTORY:
1851 -- 19-MAR-03 ftanudja o created
1852 -- 02-JUL-03 ftanudja o made p_extract_code to IN OUT for auto num gen feat.
1853 -- 15-AUG-03 ftanudja o added flexfield attributes for expclndtl. 3099278.
1854 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
1855 ------------------------------------------------------------------------------+
1856
1857 PROCEDURE validate_and_process_lines(
1858 p_transfer_flag OUT NOCOPY pn_rec_exp_itf.transfer_flag%TYPE,
1859 p_expense_line_dtl_id OUT NOCOPY pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE,
1863 p_expense_account_id pn_rec_exp_itf.expense_account_id%TYPE,
1860 p_expense_line_id IN OUT NOCOPY pn_rec_exp_line_dtl.expense_line_id%TYPE,
1861 p_is_header_set IN OUT NOCOPY BOOLEAN,
1862 p_expense_type_code pn_rec_exp_itf.expense_type_code%TYPE,
1864 p_account_description pn_rec_exp_itf.account_description%TYPE,
1865 p_actual_amount pn_rec_exp_itf.actual_amount%TYPE,
1866 p_budgeted_amount pn_rec_exp_itf.budgeted_amount%TYPE,
1867 p_currency_code pn_rec_exp_itf.currency_code%TYPE,
1868 p_location_id pn_rec_exp_itf.location_id%TYPE,
1869 p_property_id pn_rec_exp_itf.property_id%TYPE,
1870 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
1871 p_from_date pn_rec_exp_line.from_date%TYPE,
1872 p_to_date pn_rec_exp_line.to_date%TYPE,
1873 p_extract_code IN OUT NOCOPY pn_rec_exp_line.expense_extract_code%TYPE,
1874 p_keep_override VARCHAR2,
1875 p_reextract BOOLEAN,
1876 p_attribute_category pn_rec_exp_itf.attribute_category%TYPE,
1877 p_attribute1 pn_rec_exp_itf.attribute1%TYPE,
1878 p_attribute2 pn_rec_exp_itf.attribute2%TYPE,
1879 p_attribute3 pn_rec_exp_itf.attribute3%TYPE,
1880 p_attribute4 pn_rec_exp_itf.attribute4%TYPE,
1881 p_attribute5 pn_rec_exp_itf.attribute5%TYPE,
1882 p_attribute6 pn_rec_exp_itf.attribute6%TYPE,
1883 p_attribute7 pn_rec_exp_itf.attribute7%TYPE,
1884 p_attribute8 pn_rec_exp_itf.attribute8%TYPE,
1885 p_attribute9 pn_rec_exp_itf.attribute9%TYPE,
1886 p_attribute10 pn_rec_exp_itf.attribute10%TYPE,
1887 p_attribute11 pn_rec_exp_itf.attribute11%TYPE,
1888 p_attribute12 pn_rec_exp_itf.attribute12%TYPE,
1889 p_attribute13 pn_rec_exp_itf.attribute13%TYPE,
1890 p_attribute14 pn_rec_exp_itf.attribute14%TYPE,
1891 p_attribute15 pn_rec_exp_itf.attribute15%TYPE)
1892 IS
1893 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.validate_and_process_lines' ;
1894 l_info VARCHAR2(300);
1895 l_is_valid BOOLEAN;
1896
1897 BEGIN
1898
1899 pnp_debug_pkg.log(l_desc ||' (+)');
1900
1901 l_is_valid := check_loc_n_prop_id (p_location_id, p_property_id) AND
1902 check_expense_type (p_expense_type_code) AND
1903 check_account_id (p_expense_account_id);
1904
1905 IF p_reextract THEN
1906 l_is_valid := l_is_valid AND check_ccid_n_type (p_expense_type_code, p_expense_account_id, p_from_date, p_to_date);
1907 END IF;
1908
1909 IF l_is_valid THEN
1910
1911 IF NOT p_is_header_set AND NOT p_reextract THEN
1912
1913 p_is_header_set := TRUE;
1914
1915 l_info:= ' inserting expense line header';
1916 pnp_debug_pkg.log(l_info);
1917
1918 pn_rec_exp_line_pkg.insert_row(
1919 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
1920 x_expense_line_id => p_expense_line_id,
1921 x_expense_extract_code => p_extract_code,
1922 x_currency_code => p_currency_code,
1923 x_as_of_date => p_as_of_date,
1924 x_from_date => p_from_date,
1925 x_to_date => p_to_date,
1926 x_location_id => p_location_id,
1927 x_property_id => p_property_id,
1928 x_last_update_date => SYSDATE,
1929 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
1930 x_creation_date => SYSDATE,
1931 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
1932 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
1933 );
1934
1935 END IF;
1936
1937 l_info:= ' inserting expense line detail for header id: '||p_expense_line_id;
1938 pnp_debug_pkg.log(l_info);
1939
1940 pn_rec_exp_line_dtl_pkg.insert_row(
1941 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
1942 x_expense_line_id => p_expense_line_id,
1943 x_expense_line_dtl_id => p_expense_line_dtl_id,
1944 x_parent_expense_line_id => null,
1945 x_property_id => p_property_id,
1946 x_location_id => p_location_id,
1947 x_expense_type_code => p_expense_type_code,
1948 x_expense_account_id => p_expense_account_id,
1949 x_account_description => p_account_description,
1950 x_actual_amount => p_actual_amount,
1951 x_actual_amount_ovr => null,
1952 x_budgeted_amount => p_budgeted_amount,
1953 x_budgeted_amount_ovr => null,
1954 x_budgeted_pct => null,
1955 x_actual_pct => null,
1956 x_currency_code => p_currency_code,
1957 x_recoverable_flag => 'Y',
1958 x_expense_line_indicator => 'NEUTRAL',
1959 x_last_update_date => SYSDATE,
1963 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1),
1960 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
1961 x_creation_date => SYSDATE,
1962 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
1964 x_attribute_category => p_attribute_category,
1965 x_attribute1 => p_attribute1,
1966 x_attribute2 => p_attribute2,
1967 x_attribute3 => p_attribute3,
1968 x_attribute4 => p_attribute4,
1969 x_attribute5 => p_attribute5,
1970 x_attribute6 => p_attribute6,
1971 x_attribute7 => p_attribute7,
1972 x_attribute8 => p_attribute8,
1973 x_attribute9 => p_attribute9,
1974 x_attribute10 => p_attribute10,
1975 x_attribute11 => p_attribute11,
1976 x_attribute12 => p_attribute12,
1977 x_attribute13 => p_attribute13,
1978 x_attribute14 => p_attribute14,
1979 x_attribute15 => p_attribute15
1980 );
1981
1982 p_transfer_flag := 'Y';
1983
1984 ELSE
1985
1986 p_transfer_flag := 'E';
1987
1988 END IF;
1989
1990 pnp_debug_pkg.log(l_desc ||' (-)');
1991
1992 EXCEPTION
1993 WHEN OTHERS THEN
1994 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1995 raise;
1996 END;
1997
1998 ------------------------------------------------------------------------------+
1999 -- PROCEDURE : extract_expense_lines
2000 -- DESCRIPTION:
2001 -- 1. Get all lines from interface table.
2002 -- 2. Find out whether it is a re-extract or not.
2003 -- 3. Process and validate data
2004 -- 4. Update ITF table transfer flag to 'Y' if transferred, 'E' if erroneous.
2005 -- HISTORY:
2006 -- 19-MAR-03 ftanudja o created
2007 -- 02-JUL-03 ftanudja o added constraint p_extr code <> null in is_reextract.
2008 -- o made p_extract_code to IN OUT for auto num gen feat.
2009 -- 03-JUL-03 ftanudja o fixed cursor to handle cases when only prop id given.
2010 -- 10-JUL-03 ftanudja o made prop id and loc id mutex on get_itf_lines_info.
2011 -- 15-AUG-03 ftanudja o added flexfield attributes for expclndtl. 3099278.
2012 -- 21-MAY-03 ftanudja o added counters to summarize totals. 3591556.
2013 -- o added log output message to show input parameters.
2014 -- o added logic for batch commit.
2015 -- o added org_id filter.
2016 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
2017 ------------------------------------------------------------------------------+
2018
2019 PROCEDURE extract_expense_lines(
2020 p_location_id pn_rec_exp_line.location_id%TYPE,
2021 p_property_id pn_rec_exp_line.property_id%TYPE,
2022 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
2023 p_from_date pn_rec_exp_line.from_date%TYPE,
2024 p_to_date pn_rec_exp_line.to_date%TYPE,
2025 p_currency_code pn_rec_exp_line.currency_code%TYPE,
2026 p_extract_code IN OUT NOCOPY pn_rec_exp_line.expense_extract_code%TYPE,
2027 p_keep_override VARCHAR2)
2028 IS
2029 CURSOR get_itf_lines_info IS
2030 SELECT expense_type_code,
2031 expense_account_id,
2032 account_description,
2033 actual_amount,
2034 budgeted_amount,
2035 currency_code,
2036 location_id,
2037 property_id,
2038 attribute_category,
2039 attribute1,
2040 attribute2,
2041 attribute3,
2042 attribute4,
2043 attribute5,
2044 attribute6,
2045 attribute7,
2046 attribute8,
2047 attribute9,
2048 attribute10,
2049 attribute11,
2050 attribute12,
2051 attribute13,
2052 attribute14,
2053 attribute15
2054 FROM pn_rec_exp_itf
2055 WHERE transfer_flag = 'N'
2056 AND from_date = p_from_date
2057 AND to_date = p_to_date
2058 AND currency_code = p_currency_code
2059 AND (location_id IN
2060 (SELECT location_id FROM pn_locations_all
2061 WHERE active_start_date < p_to_date
2062 AND active_end_date > p_from_date
2063 START WITH (location_id = p_location_id OR
2064 (property_id = p_property_id AND p_location_id IS NULL))
2065 CONNECT BY PRIOR location_id = parent_location_id)
2066 OR
2067 (property_id = p_property_id AND p_location_id IS NULL))
2068 AND org_id = pn_mo_cache_utils.get_current_org_id
2069 FOR UPDATE OF transfer_flag, expense_line_dtl_id NOWAIT;
2070
2071 CURSOR is_reextract IS
2072 SELECT expense_line_id
2073 FROM pn_rec_exp_line_all hdr
2074 WHERE hdr.expense_extract_code = p_extract_code
2075 AND p_extract_code IS NOT NULL
2076 AND rownum < 2;
2077
2078 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_expense_lines' ;
2079 l_info VARCHAR2(300);
2080 l_is_header_set BOOLEAN;
2081 l_reextract BOOLEAN;
2082 l_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE;
2086 l_failed NUMBER := 0;
2083 l_expense_line_id pn_rec_exp_line_dtl.expense_line_id%TYPE;
2084 l_transfer_flag pn_rec_exp_itf.transfer_flag%TYPE;
2085 l_total NUMBER := 0;
2087
2088 BEGIN
2089
2090 pnp_debug_pkg.log(l_desc ||' (+)');
2091
2092 fnd_message.set_name('PN','PN_REC_EXP_LN_CP_INFO');
2093 fnd_message.set_token('LOC' , to_char(p_location_id));
2094 fnd_message.set_token('PROP' , to_char(p_property_id));
2095 fnd_message.set_token('STR' , to_char(p_from_date));
2096 fnd_message.set_token('END' , to_char(p_to_date));
2097 fnd_message.set_token('AOD' , to_char(p_as_of_date));
2098 fnd_message.set_token('CUR' , p_currency_code);
2099 fnd_message.set_token('EXPNUM', p_extract_code);
2100 fnd_message.set_token('OVR' , p_keep_override);
2101 pnp_debug_pkg.put_log_msg('');
2102 pnp_debug_pkg.put_log_msg(fnd_message.get);
2103 pnp_debug_pkg.put_log_msg('');
2104
2105 l_info := ' initializing values';
2106 pnp_debug_pkg.log(l_info);
2107
2108 l_expense_line_id := null;
2109 l_is_header_set := FALSE;
2110 l_reextract := FALSE;
2111
2112 FOR check_reextract IN is_reextract LOOP
2113 l_reextract := TRUE;
2114 l_expense_line_id := check_reextract.expense_line_id;
2115 END LOOP;
2116
2117 FOR lines_rec IN get_itf_lines_info LOOP
2118
2119 l_expense_line_dtl_id := null;
2120
2121 validate_and_process_lines(
2122 p_transfer_flag => l_transfer_flag,
2123 p_expense_line_dtl_id => l_expense_line_dtl_id,
2124 p_expense_line_id => l_expense_line_id,
2125 p_is_header_set => l_is_header_set,
2126 p_expense_type_code => lines_rec.expense_type_code,
2127 p_expense_account_id => lines_rec.expense_account_id,
2128 p_account_description => lines_rec.account_description,
2129 p_actual_amount => lines_rec.actual_amount,
2130 p_budgeted_amount => lines_rec.budgeted_amount,
2131 p_currency_code => lines_rec.currency_code,
2132 p_location_id => lines_rec.location_id,
2133 p_property_id => lines_rec.property_id,
2134 p_as_of_date => p_as_of_date,
2135 p_from_date => p_from_date,
2136 p_to_date => p_to_date,
2137 p_extract_code => p_extract_code,
2138 p_keep_override => p_keep_override,
2139 p_reextract => l_reextract,
2140 p_attribute_category => lines_rec.attribute_category,
2141 p_attribute1 => lines_rec.attribute1,
2142 p_attribute2 => lines_rec.attribute2,
2143 p_attribute3 => lines_rec.attribute3,
2144 p_attribute4 => lines_rec.attribute4,
2145 p_attribute5 => lines_rec.attribute5,
2146 p_attribute6 => lines_rec.attribute6,
2147 p_attribute7 => lines_rec.attribute7,
2148 p_attribute8 => lines_rec.attribute8,
2149 p_attribute9 => lines_rec.attribute9,
2150 p_attribute10 => lines_rec.attribute10,
2151 p_attribute11 => lines_rec.attribute11,
2152 p_attribute12 => lines_rec.attribute12,
2153 p_attribute13 => lines_rec.attribute13,
2154 p_attribute14 => lines_rec.attribute14,
2155 p_attribute15 => lines_rec.attribute15
2156 );
2157
2158 l_total := l_total + 1;
2159
2160 IF l_transfer_flag = 'E' THEN
2161 l_failed := l_failed + 1;
2162 END IF;
2163
2164 -- do a batch commit if needed
2165 IF mod(l_total, g_batch_commit_size) = 0 THEN
2166 commit;
2167 END IF;
2168
2169 l_info := ' updating interface table ';
2170 pnp_debug_pkg.log(l_info);
2171
2172 UPDATE pn_rec_exp_itf
2173 SET transfer_flag = l_transfer_flag,
2174 expense_line_dtl_id = l_expense_line_dtl_id
2175 WHERE CURRENT OF get_itf_lines_info;
2176
2177 END LOOP;
2178
2179 fnd_message.set_name('PN','PN_REC_EXP_LN');
2180 pnp_debug_pkg.put_log_msg('');
2181 pnp_debug_pkg.put_log_msg(fnd_message.get);
2182
2183 fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2184 fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2185 fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2186 fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2187 pnp_debug_pkg.put_log_msg(fnd_message.get);
2188 pnp_debug_pkg.put_log_msg('');
2189
2190 pnp_debug_pkg.log(l_desc ||' (-)');
2191
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2195 raise;
2196
2197 END extract_expense_lines;
2198
2199 --------------------------- EXPENSE CLASS LINE -------------------------------+
2200
2201 ------------------------------------------------------------------------------+
2202 -- PROCEDURE : process_exp_cls_dtl_mst_data
2203 --
2204 -- DESCRIPTION:
2205 -- 1. Given : data table p_master_data.
2206 -- 2. Determine whether a matching row can be found.
2207 -- 3. If found, update, otherwise insert new row.
2208 --
2209 -- NOTES:
2210 -- A. Use of override tables p_fee_use_table, p_share_use_table
2214 -- if and only if there aren't any values defined at the account drilldown.
2211 -- 1. The purpose is to keep track whether share pct and fee % bf contr
2212 -- should be used at this level.
2213 -- 2. It should only be used to calculate the computed recoverable amount
2215 -- B. Use of tables p_ovr_use_data and p_use_prior_ovr
2216 -- 1. p_ovr_use_data keeps track whether the current _ovr values are from
2217 -- a regeneration or from a prior extract.
2218 -- 2. it's possible to have aregenerate in which values from a prior extract
2219 -- are defaulted... since at run time it's not known which value should
2220 -- be used, this information needs to be kept to populate the correct _ovr
2221 -- values.
2222 -- 3. p_use_prior_ovr determines whether _ovr values should be used.
2223 --
2224 -- HISTORY:
2225 -- 19-MAR-03 ftanudja o created
2226 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2227 ------------------------------------------------------------------------------+
2228
2229 PROCEDURE process_exp_cls_dtl_mst_data(
2230 p_master_data IN OUT NOCOPY exp_cls_line_mst_tbl,
2231 p_ovr_use_data IN OUT NOCOPY exp_cls_line_use_tbl,
2232 p_fee_use_table IN OUT NOCOPY exp_cls_line_use_tbl,
2233 p_share_use_table IN OUT NOCOPY exp_cls_line_use_tbl,
2234 p_master_data_id OUT NOCOPY pn_rec_expcl_dtlln.expense_class_line_id%TYPE,
2235 p_expense_class_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE,
2236 p_expense_class_dtl_id pn_rec_expcl_dtlln.expense_class_dtl_id%TYPE,
2237 p_location_id pn_space_assign_cust.location_id%TYPE,
2238 p_cust_space_assign_id pn_space_assign_cust.cust_space_assign_id%TYPE,
2239 p_cust_account_id pn_space_assign_cust.cust_account_id%TYPE,
2240 p_lease_id pn_rec_expcl_dtlln.lease_id%TYPE,
2241 p_recovery_space_std_code pn_rec_expcl_dtlln.recovery_space_std_code%TYPE,
2242 p_recovery_type_code pn_rec_expcl_dtlln.recovery_type_code%TYPE,
2243 p_budget_amount pn_rec_expcl_dtlln.budgeted_amt%TYPE,
2244 p_expense_amount pn_rec_expcl_dtlln.expense_amt%TYPE,
2245 p_recoverable_amount pn_rec_expcl_dtlln.recoverable_amt%TYPE,
2246 p_cpt_recoverable_amount pn_rec_expcl_dtlln.computed_recoverable_amt%TYPE,
2247 p_cls_line_share_pct pn_rec_expcl_dtlln.cls_line_share_pct%TYPE,
2248 p_cls_line_fee_af_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_after_contr_ovr%TYPE,
2249 p_cls_line_fee_bf_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_before_contr_ovr%TYPE,
2250 p_use_fee_bf_contr pn_rec_expcl_inc.cls_incl_fee_before_contr%TYPE,
2251 p_use_share_pct pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE,
2252 p_use_prior_ovr BOOLEAN
2253 )
2254 IS
2255 l_info VARCHAR2(300);
2256 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_cls_dtl_mst_data' ;
2257 temp_rec pn_rec_expcl_dtlln%ROWTYPE;
2258
2259 BEGIN
2260
2261 pnp_debug_pkg.log(l_desc ||' (+)');
2262
2263 p_master_data_id := -1;
2264
2265 l_info:= ' searching through master table';
2266 pnp_debug_pkg.log(l_info);
2267
2268 FOR i IN 0 .. p_master_data.COUNT - 1 LOOP
2269
2270 IF (p_master_data(i).location_id = p_location_id) AND
2271 (p_master_data(i).cust_account_id = p_cust_account_id) THEN
2272
2273 l_info:= ' updating values in master data object for location id '||p_location_id||' and customer acct id'|| p_cust_account_id;
2274 pnp_debug_pkg.log(l_info);
2275
2276 p_master_data(i).budgeted_amt := nvl(p_master_data(i).budgeted_amt, 0) + nvl(p_budget_amount,0);
2277 p_master_data(i).expense_amt := nvl(p_master_data(i).expense_amt, 0) + nvl(p_expense_amount,0);
2278 p_master_data(i).recoverable_amt := nvl(p_master_data(i).recoverable_amt, 0) + nvl(p_recoverable_amount,0);
2279 p_master_data(i).computed_recoverable_amt := nvl(p_master_data(i).computed_recoverable_amt, 0) + nvl(p_cpt_recoverable_amount,0);
2280 p_master_data_id := i;
2281
2282 p_fee_use_table(i) := p_fee_use_table(i) AND (p_use_fee_bf_contr IS NULL);
2283 p_share_use_table(i) := p_share_use_table(i) AND (p_use_share_pct IS NULL);
2284
2285 IF p_ovr_use_data(i) AND NOT p_use_prior_ovr THEN
2286
2287 p_master_data(i).cls_line_share_pct := p_cls_line_share_pct;
2288 p_master_data(i).cls_line_fee_before_contr_ovr:= p_cls_line_fee_bf_contr_ovr;
2289 p_master_data(i).cls_line_fee_after_contr_ovr := p_cls_line_fee_af_contr_ovr;
2290
2291 END IF;
2292
2293 exit;
2294
2295 END IF;
2296 END LOOP;
2297
2298 IF (p_master_data_id = -1) THEN
2299
2300 p_master_data_id := p_master_data.COUNT;
2301
2302 l_info:= ' creating new entry in master data object';
2303 pnp_debug_pkg.log(l_info);
2304
2305 temp_rec.expense_class_dtl_id := p_expense_class_dtl_id;
2306 temp_rec.expense_class_line_id := p_expense_class_line_id;
2307 temp_rec.location_id := p_location_id;
2308 temp_rec.cust_space_assign_id := p_cust_space_assign_id;
2309 temp_rec.cust_account_id := p_cust_account_id;
2310 temp_rec.lease_id := p_lease_id;
2314 temp_rec.cls_line_fee_before_contr_ovr:= p_cls_line_fee_bf_contr_ovr;
2311 temp_rec.recovery_space_std_code := p_recovery_space_std_code;
2312 temp_rec.recovery_type_code := p_recovery_type_code;
2313 temp_rec.cls_line_share_pct := p_cls_line_share_pct;
2315 temp_rec.cls_line_fee_after_contr_ovr := p_cls_line_fee_af_contr_ovr;
2316 temp_rec.expense_amt := p_expense_amount;
2317 temp_rec.budgeted_amt := p_budget_amount;
2318 temp_rec.recoverable_amt := p_recoverable_amount;
2319 temp_rec.computed_recoverable_amt := p_cpt_recoverable_amount;
2320 p_master_data(p_master_data_id) := temp_rec;
2321 p_fee_use_table(p_master_data_id) := (p_use_fee_bf_contr IS NULL);
2322 p_share_use_table(p_master_data_id) := (p_use_share_pct IS NULL);
2323 p_ovr_use_data(p_master_data_id) := p_use_prior_ovr;
2324
2325 END IF;
2326
2327 pnp_debug_pkg.log(l_desc ||' (-)');
2328
2329 EXCEPTION
2330 WHEN OTHERS THEN
2331 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2332 raise;
2333 END;
2334
2335 ------------------------------------------------------------------------------+
2336 -- PROCEDURE : process_exp_cls_dtl_dtl_data
2337 -- DESCRIPTION: dumps data in plsql table, given parameters
2338 -- 19-MAR-03 ftanudja o created
2339 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2340 ------------------------------------------------------------------------------+
2341
2342 PROCEDURE process_exp_cls_dtl_dtl_data(
2343 p_detail_data IN OUT NOCOPY exp_cls_line_dtl_tbl,
2344 p_master_data_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE,
2345 p_expense_class_line_dtl_id pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE,
2346 p_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE,
2347 p_expense_account_id pn_rec_exp_line_dtl.expense_account_id%TYPE,
2348 p_expense_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE,
2349 p_expense_amount pn_rec_expcl_dtlln.expense_amt%TYPE,
2350 p_budget_amount pn_rec_expcl_dtlln.budgeted_amt%TYPE,
2351 p_recoverable_amount pn_rec_expcl_dtlln.recoverable_amt%TYPE,
2352 p_cpt_recoverable_amount pn_rec_expcl_dtlln.computed_recoverable_amt%TYPE,
2353 p_cls_line_shr_pct pn_rec_expcl_inc.cls_incl_share_pct%TYPE,
2354 p_cls_line_fee_bf_contr pn_rec_expcl_inc.cls_incl_fee_before_contr%TYPE,
2355 p_cls_line_shr_pct_ovr pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE,
2356 p_cls_line_fee_bf_contr_ovr pn_rec_expcl_dtlacc.cls_line_dtl_fee_bf_contr%TYPE
2357 )
2358 IS
2359
2360 l_info VARCHAR2(300);
2361 l_id NUMBER;
2362 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_cls_dtl_dtl_data' ;
2363
2364 BEGIN
2365
2366 pnp_debug_pkg.log(l_desc ||' (+)');
2367
2368 l_info:= ' inserting into details pl/sql table ';
2369 pnp_debug_pkg.log(l_info);
2370
2371 l_id := p_detail_data.COUNT;
2372
2373 p_detail_data(l_id).expense_class_line_dtl_id := p_expense_class_line_dtl_id;
2374 p_detail_data(l_id).expense_class_line_id := p_master_data_id;
2375 p_detail_data(l_id).expense_line_dtl_id := p_expense_line_dtl_id;
2376 p_detail_data(l_id).expense_type_code := p_expense_type_code;
2377 p_detail_data(l_id).expense_account_id := p_expense_account_id;
2378 p_detail_data(l_id).expense_amt := p_expense_amount;
2379 p_detail_data(l_id).budgeted_amt := p_budget_amount;
2380 p_detail_data(l_id).recoverable_amt := p_recoverable_amount;
2381 p_detail_data(l_id).computed_recoverable_amt := p_cpt_recoverable_amount;
2382 p_detail_data(l_id).cls_line_dtl_share_pct := p_cls_line_shr_pct;
2383 p_detail_data(l_id).cls_line_dtl_fee_bf_contr := p_cls_line_fee_bf_contr;
2384 p_detail_data(l_id).cls_line_dtl_share_pct_ovr := p_cls_line_shr_pct_ovr;
2385 p_detail_data(l_id).cls_line_dtl_fee_bf_contr_ovr := p_cls_line_fee_bf_contr_ovr;
2386
2387 pnp_debug_pkg.log(l_desc ||' (-)');
2388
2389 EXCEPTION
2390 WHEN OTHERS THEN
2391 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2392 raise;
2393 END;
2394
2395 ------------------------------------------------------------------------------+
2396 -- PROCEDURE : process_exp_class_line_data
2397 -- DESCRIPTION:
2398 -- 1. Dumps data from pl/sql table into expense class lines table.
2399 -- 2. Determine whether fee % and share % should be used at location level
2400 -- 3. If regeneration, find out which data needs to be deleted.
2401 --
2402 -- 19-MAR-03 ftanudja o created
2403 -- 21-MAY-04 ftanudja o added logic for batch commit.
2404 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2405 ------------------------------------------------------------------------------+
2406
2407 PROCEDURE process_exp_class_line_data(
2408 p_master_data IN OUT NOCOPY exp_cls_line_mst_tbl,
2409 p_old_detail_data exp_cls_line_dtl_tbl,
2410 p_old_master_data exp_cls_line_mst_tbl,
2411 p_detail_data exp_cls_line_dtl_tbl,
2412 p_fee_use_table exp_cls_line_use_tbl,
2413 p_share_use_table exp_cls_line_use_tbl,
2414 p_default_fee_bf pn_rec_expcl_dtl.cls_line_fee_before_contr%TYPE
2418 l_expense_class_line_dtl_id pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE;
2415 )
2416 IS
2417 l_expense_class_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE;
2419 l_master_keep_table number_table_type;
2420 l_detail_keep_table number_table_type;
2421 l_master_delete_table number_table_type;
2422 l_detail_delete_table number_table_type;
2423 l_is_in BOOLEAN;
2424 l_use_share_pct_flag VARCHAR2(1);
2425 l_use_fee_pct_flag VARCHAR2(1);
2426 l_info VARCHAR2(300);
2427 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_class_line_data';
2428
2429 BEGIN
2430
2431 pnp_debug_pkg.log(l_desc ||' (+)');
2432
2433 FOR i IN 0 .. p_master_data.COUNT - 1 LOOP
2434
2435 l_info:= ' figuring out whether values should be used ';
2436 pnp_debug_pkg.log(l_info);
2437
2438 l_use_share_pct_flag := 'N';
2439 l_use_fee_pct_flag := 'N';
2440
2441 IF p_fee_use_table(i) THEN
2442 l_use_fee_pct_flag := 'Y';
2443 p_master_data(i).computed_recoverable_amt := p_master_data(i).computed_recoverable_amt *
2444 (1 + nvl(nvl(p_master_data(i).cls_line_fee_before_contr_ovr, p_default_fee_bf), 0) / 100);
2445 END IF;
2446
2447 IF p_share_use_table(i) THEN
2448 l_use_share_pct_flag := 'Y';
2449 p_master_data(i).computed_recoverable_amt := p_master_data(i).computed_recoverable_amt *
2450 nvl(p_master_data(i).cls_line_share_pct, 100) / 100;
2451 END IF;
2452
2453 l_expense_class_line_id := p_master_data(i).expense_class_line_id;
2454
2455 IF l_expense_class_line_id IS NULL THEN
2456
2457 l_info:= ' inserting data into class lines master table';
2458 pnp_debug_pkg.log(l_info);
2459
2460 pn_rec_expcl_dtlln_pkg.insert_row(
2461 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
2462 x_expense_class_dtl_id => p_master_data(i).expense_class_dtl_id,
2463 x_expense_class_line_id => l_expense_class_line_id,
2464 x_location_id => p_master_data(i).location_id,
2465 x_cust_space_assign_id => p_master_data(i).cust_space_assign_id,
2466 x_cust_account_id => p_master_data(i).cust_account_id,
2467 x_lease_id => p_master_data(i).lease_id,
2468 x_recovery_space_std_code => p_master_data(i).recovery_space_std_code,
2469 x_recovery_type_code => p_master_data(i).recovery_type_code,
2470 x_budgeted_amt => p_master_data(i).budgeted_amt,
2471 x_expense_amt => p_master_data(i).expense_amt,
2472 x_recoverable_amt => p_master_data(i).recoverable_amt,
2473 x_computed_recoverable_amt => p_master_data(i).computed_recoverable_amt,
2474 x_cls_line_share_pct => p_master_data(i).cls_line_share_pct,
2475 x_cls_line_fee_bf_ct_ovr => p_master_data(i).cls_line_fee_before_contr_ovr,
2476 x_cls_line_fee_af_ct_ovr => p_master_data(i).cls_line_fee_after_contr_ovr,
2477 x_use_share_pct_flag => l_use_share_pct_flag,
2478 x_use_fee_before_contr_flag => l_use_fee_pct_flag,
2479 x_last_update_date => SYSDATE,
2480 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2481 x_creation_date => SYSDATE,
2482 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2483 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2484 );
2485
2486 p_master_data(i).expense_class_line_id := l_expense_class_line_id;
2487
2488 ELSE
2489
2490 l_info:= ' updating data into class lines table, id: ' || l_expense_class_line_id;
2491 pnp_debug_pkg.log(l_info);
2492
2493 pn_rec_expcl_dtlln_pkg.update_row(
2494 x_expense_class_line_id => l_expense_class_line_id,
2495 x_location_id => p_master_data(i).location_id,
2496 x_cust_space_assign_id => p_master_data(i).cust_space_assign_id,
2497 x_cust_account_id => p_master_data(i).cust_account_id,
2498 x_lease_id => p_master_data(i).lease_id,
2499 x_recovery_space_std_code => p_master_data(i).recovery_space_std_code,
2500 x_recovery_type_code => p_master_data(i).recovery_type_code,
2501 x_budgeted_amt => p_master_data(i).budgeted_amt,
2502 x_expense_amt => p_master_data(i).expense_amt,
2503 x_recoverable_amt => p_master_data(i).recoverable_amt,
2504 x_computed_recoverable_amt => p_master_data(i).computed_recoverable_amt,
2505 x_cls_line_share_pct => p_master_data(i).cls_line_share_pct,
2506 x_cls_line_fee_bf_ct_ovr => p_master_data(i).cls_line_fee_before_contr_ovr,
2507 x_cls_line_fee_af_ct_ovr => p_master_data(i).cls_line_fee_after_contr_ovr,
2508 x_use_share_pct_flag => l_use_share_pct_flag,
2509 x_use_fee_before_contr_flag => l_use_fee_pct_flag,
2510 x_last_update_date => SYSDATE,
2511 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2512 x_creation_date => SYSDATE,
2513 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2517 END IF;
2514 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2515 );
2516
2518
2519 l_master_keep_table(l_master_keep_table.COUNT) := l_expense_class_line_id;
2520
2521 -- do a batch commit if needed
2522 IF mod (i, g_batch_commit_size) = 0 THEN
2523 commit;
2524 END IF;
2525
2526 END LOOP;
2527
2528 l_info:= ' processing data for class line details table';
2529 pnp_debug_pkg.log(l_info);
2530
2531 FOR i IN 0 .. p_detail_data.COUNT - 1 LOOP
2532
2533 l_expense_class_line_dtl_id := p_detail_data(i).expense_class_line_dtl_id;
2534
2535 IF l_expense_class_line_dtl_id IS NULL THEN
2536
2537 l_info := ' inserting detail data for class line header: '||
2538 p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id;
2539 pnp_debug_pkg.log(l_info);
2540
2541 pn_rec_expcl_dtlacc_pkg.insert_row(
2542 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
2543 x_expense_class_line_id => p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id,
2544 x_expense_class_line_dtl_id => l_expense_class_line_dtl_id,
2545 x_expense_line_dtl_id => p_detail_data(i).expense_line_dtl_id,
2546 x_expense_account_id => p_detail_data(i).expense_account_id,
2547 x_expense_type_code => p_detail_data(i).expense_type_code,
2548 x_cls_line_dtl_share_pct => p_detail_data(i).cls_line_dtl_share_pct,
2549 x_cls_line_dtl_share_pct_ovr => p_detail_data(i).cls_line_dtl_share_pct_ovr,
2550 x_cls_line_dtl_fee_bf_ct => p_detail_data(i).cls_line_dtl_fee_bf_contr,
2551 x_cls_line_dtl_fee_bf_ct_ovr => p_detail_data(i).cls_line_dtl_fee_bf_contr_ovr,
2552 x_expense_amt => p_detail_data(i).expense_amt,
2553 x_budgeted_amt => p_detail_data(i).budgeted_amt,
2554 x_recoverable_amt => p_detail_data(i).recoverable_amt,
2555 x_computed_recoverable_amt => p_detail_data(i).computed_recoverable_amt,
2556 x_last_update_date => SYSDATE,
2557 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2558 x_creation_date => SYSDATE,
2559 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2560 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2561 );
2562 ELSE
2563 l_info := ' updating detail data for class line header: '||
2564 p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id;
2565 pnp_debug_pkg.log(l_info);
2566
2567 pn_rec_expcl_dtlacc_pkg.update_row(
2568 x_expense_class_line_dtl_id => l_expense_class_line_dtl_id,
2569 x_expense_line_dtl_id => p_detail_data(i).expense_line_dtl_id,
2570 x_expense_account_id => p_detail_data(i).expense_account_id,
2571 x_expense_type_code => p_detail_data(i).expense_type_code,
2572 x_cls_line_dtl_share_pct => p_detail_data(i).cls_line_dtl_share_pct,
2573 x_cls_line_dtl_share_pct_ovr => p_detail_data(i).cls_line_dtl_share_pct_ovr,
2574 x_cls_line_dtl_fee_bf_ct => p_detail_data(i).cls_line_dtl_fee_bf_contr,
2575 x_cls_line_dtl_fee_bf_ct_ovr => p_detail_data(i).cls_line_dtl_fee_bf_contr_ovr,
2576 x_expense_amt => p_detail_data(i).expense_amt,
2577 x_budgeted_amt => p_detail_data(i).budgeted_amt,
2578 x_recoverable_amt => p_detail_data(i).recoverable_amt,
2579 x_computed_recoverable_amt => p_detail_data(i).computed_recoverable_amt,
2580 x_last_update_date => SYSDATE,
2581 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2582 x_creation_date => SYSDATE,
2583 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2584 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2585 );
2586
2587 END IF;
2588
2589 l_detail_keep_table(l_detail_keep_table.COUNT) := l_expense_class_line_dtl_id;
2590
2591 END LOOP;
2592
2593 l_info := ' figuring out which data is unused';
2594 pnp_debug_pkg.log(l_info);
2595
2596 FOR i IN 0 .. p_old_detail_data.COUNT - 1 LOOP
2597 l_is_in := FALSE;
2598 FOR j IN 0 .. l_detail_keep_table.COUNT - 1 LOOP
2599 IF l_detail_keep_table(j) = p_old_detail_data(i).expense_class_line_dtl_id THEN l_is_in := TRUE; exit; END IF;
2600 END LOOP;
2601 IF NOT l_is_in THEN
2602 l_detail_delete_table(l_detail_delete_table.COUNT) := p_old_detail_data(i).expense_class_line_dtl_id;
2603 END IF;
2604 END LOOP;
2605
2606 FOR i IN 0 .. p_old_master_data.COUNT - 1 LOOP
2607 l_is_in := FALSE;
2608 FOR j IN 0 .. l_master_keep_table.COUNT - 1 LOOP
2609 IF l_master_keep_table(j) = p_old_master_data(i).expense_class_line_id THEN l_is_in := TRUE; exit; END IF;
2610 END LOOP;
2611 IF NOT l_is_in THEN
2612 l_master_delete_table(l_master_delete_table.COUNT) := p_old_master_data(i).expense_class_line_id;
2613 END IF;
2614 END LOOP;
2615
2616 l_info := ' deleting unused data';
2617 pnp_debug_pkg.log(l_info);
2618
2619 FORALL i IN 0 .. l_detail_delete_table.COUNT - 1
2620 DELETE FROM pn_rec_expcl_dtlacc_all
2621 WHERE expense_class_line_dtl_id = l_detail_delete_table(i);
2622
2623 FORALL i IN 0 .. l_master_delete_table.COUNT - 1
2627
2624 DELETE FROM pn_rec_expcl_dtlln_all
2625 WHERE expense_class_line_id = l_master_delete_table(i);
2626
2628 pnp_debug_pkg.log(l_desc ||' (-)');
2629
2630 EXCEPTION
2631 WHEN OTHERS THEN
2632 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2633 raise;
2634 END;
2635
2636 ------------------------------------------------------------------------------+
2637 -- PROCEDURE : dismantle_exp_line_from_dtl
2638 -- DESCRIPTION:
2639 -- 1. When an expense is allocated, it needs to be removed from the tables.
2640 -- 2. Determine which expense class line detail and header it impacts
2641 -- 3. Remove appropriate line and recalculate the comp. recoverable amount.
2642 --
2643 -- NOTE:
2644 -- 1. The total can be derived by adding cumulatively. The problem, however,
2645 -- is to determine whether fee% and share% should be used.
2646 -- 2. To accomplish this, we need to hit the database and do a comparison.
2647 -- 3. Might as well do the summation while getting that information.
2648 --
2649 -- HISTORY:
2650 -- 19-MAR-03 ftanudja o created.
2651 -- 06-AUG-03 ftanudja o add deletion mechanism for expcl lines w/ no child.
2652 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2653 ------------------------------------------------------------------------------+
2654
2655 PROCEDURE dismantle_exp_line_from_dtl(p_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE)
2656 IS
2657 CURSOR get_affected_lines IS
2658 SELECT expense_class_line_dtl_id,
2659 expense_class_line_id
2660 FROM pn_rec_expcl_dtlacc_all
2661 WHERE expense_line_dtl_id = p_expense_line_dtl_id;
2662
2663 CURSOR get_header_info (p_exp_cls_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE) IS
2664 SELECT sum(nvl(computed_recoverable_amt, 0)) computed_recoverable_amount,
2665 sum(nvl(recoverable_amt, 0)) recoverable_amount,
2666 sum(nvl(expense_amt, 0)) expense_amount,
2667 sum(nvl(budgeted_amt, 0)) budgeted_amount,
2668 min(decode(nvl(cls_line_dtl_share_pct_ovr, cls_line_dtl_share_pct), NULL, NULL, 100)) use_cls_line_share,
2669 min(decode(nvl(cls_line_dtl_fee_bf_contr_ovr, cls_line_dtl_fee_bf_contr), NULL, NULL, 0)) use_cls_line_fee
2670 FROM pn_rec_expcl_dtlacc_all
2671 WHERE expense_class_line_id = p_exp_cls_line_id;
2672
2673 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2674 TYPE use_tbl IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
2675
2676 expense_list num_tbl;
2677 budgeted_list num_tbl;
2678 recoverable_list num_tbl;
2679 computed_recoverable_list num_tbl;
2680 hdr_id_list num_tbl;
2681 update_list num_tbl;
2682 delete_list num_tbl;
2683 fee_use_tbl num_tbl;
2684 share_use_tbl num_tbl;
2685 l_update BOOLEAN;
2686 l_count NUMBER;
2687 l_info VARCHAR2(300);
2688 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.dismantle_exp_line_from_dtl' ;
2689
2690 BEGIN
2691
2692 pnp_debug_pkg.log(l_desc ||' (+)');
2693
2694 l_info := ' processing affected lines ';
2695 pnp_debug_pkg.log(l_info);
2696
2697 FOR class_line_dtl_rec IN get_affected_lines LOOP
2698 DELETE pn_rec_expcl_dtlacc_all
2699 WHERE expense_class_line_dtl_id = class_line_dtl_rec.expense_class_line_dtl_id;
2700 hdr_id_list(hdr_id_list.COUNT) := class_line_dtl_rec.expense_class_line_id;
2701 END LOOP;
2702
2703 l_info := ' storing amount information ';
2704 pnp_debug_pkg.log(l_info);
2705
2706 FOR i IN 0 .. hdr_id_list.COUNT - 1 LOOP
2707
2708 l_update := FALSE;
2709
2710 FOR hdr_rec IN get_header_info(hdr_id_list(i)) LOOP
2711 l_info := ' getting update information for expclln id: '||hdr_id_list(i);
2712 pnp_debug_pkg.log(l_info);
2713
2714 l_update := TRUE;
2715 l_count := update_list.COUNT;
2716 update_list(l_count) := hdr_id_list(i);
2717 expense_list(l_count) := hdr_rec.expense_amount;
2718 budgeted_list(l_count) := hdr_rec.budgeted_amount;
2719 recoverable_list(l_count) := hdr_rec.recoverable_amount;
2720 computed_recoverable_list(l_count) := hdr_rec.computed_recoverable_amount;
2721 fee_use_tbl(l_count) := hdr_rec.use_cls_line_fee;
2722 share_use_tbl(l_count) := hdr_rec.use_cls_line_share;
2723
2724 END LOOP;
2725
2726 IF NOT l_update THEN delete_list(delete_list.COUNT) := hdr_id_list(i); END IF;
2727
2728 END LOOP;
2729
2730 l_info := ' updating header information ';
2731 pnp_debug_pkg.log(l_info);
2732
2733 FORALL i IN 0 .. update_list.COUNT - 1
2734 UPDATE pn_rec_expcl_dtlln_all
2735 SET budgeted_amt = budgeted_list(i),
2736 expense_amt = expense_list(i),
2737 recoverable_amt = recoverable_list(i),
2738 computed_recoverable_amt = computed_recoverable_list(i) *
2739 nvl(share_use_tbl(i), cls_line_share_pct) / 100 *
2740 (1 + nvl(fee_use_tbl(i), cls_line_fee_before_contr_ovr) / 100),
2741 last_update_date = SYSDATE,
2742 last_updated_by = nvl(fnd_profile.value('USER_ID'), -1),
2746 l_info := ' deleting unused header lines';
2743 last_update_login = nvl(fnd_profile.value('USER_ID'), -1)
2744 WHERE expense_class_line_id = update_list(i);
2745
2747 pnp_debug_pkg.log(l_info);
2748
2749 FORALL i IN 0 .. delete_list.COUNT - 1
2750 DELETE pn_rec_expcl_dtlln_all
2751 WHERE expense_class_line_id = delete_list(i);
2752
2753 pnp_debug_pkg.log(l_desc ||' (-)');
2754
2755 EXCEPTION
2756 WHEN OTHERS THEN
2757 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2758 raise;
2759 END;
2760
2761 ------------------------------------------------------------------------------+
2762 -- PROCEDURE : populate_expense_class_details
2763 -- DESCRIPTION:
2764 -- 1. Given: expense extract code
2765 -- 2. Finds corresponding expense lines header id and populates all
2766 -- expense class details pertinent to that location / property.
2767 --
2768 -- IF both location_id and property_id are provided, ignore property_id
2769 -- IF location_id given, look for its parent location id and associated
2770 -- property_id (if applicable) and get associated class details
2771 --
2772 -- HISTORY:
2773 -- 19-MAR-03 ftanudja o created
2774 -- 03-JUL-03 ftanudja o fixed cursor to handle cases when only prop id given.
2775 -- 10-JUL-03 ftanudja o made prop id and loc id mutex on get_relevant_expcl.
2776 -- o fix logic on getting prop id. 3046470.
2777 -- 08-AUG-03 ftanudja o fix get .. cursor. 3090131.
2778 -- 18-SEP-03 ftanudja o added currency code filter. 3148855.
2779 -- 21-MAY-03 ftanudja o added counters to summarize totals. 3591556.
2780 -- o restructured and fixed CURSOR logic.
2781 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2782 ------------------------------------------------------------------------------+
2783
2784 PROCEDURE populate_expense_class_details(
2785 p_location_id pn_rec_exp_line.location_id%TYPE,
2786 p_property_id pn_rec_exp_line.property_id%TYPE,
2787 p_as_of_date VARCHAR2,
2788 p_from_date VARCHAR2,
2789 p_to_date VARCHAR2,
2790 p_extract_code pn_rec_exp_line.expense_extract_code%TYPE,
2791 p_keep_override VARCHAR2)
2792 IS
2793
2794 CURSOR get_exp_line_id IS
2795 SELECT expense_line_id,
2796 currency_code
2797 FROM pn_rec_exp_line_all hdr
2798 WHERE hdr.expense_extract_code = p_extract_code
2799 AND rownum < 2;
2800
2801 CURSOR get_expcl_by_prop (p_propid pn_rec_exp_line.property_id%TYPE,
2802 p_currency_code pn_rec_exp_line.currency_code%TYPE) IS
2803 SELECT expense_class_id
2804 FROM pn_rec_expcl_all
2805 WHERE property_id = p_propid
2806 AND location_id IS NULL
2807 AND currency_code = p_currency_code;
2808
2809 CURSOR get_expcl_by_locn (p_locnid pn_rec_exp_line.location_id%TYPE,
2810 p_currency_code pn_rec_exp_line.currency_code%TYPE) IS
2811 SELECT expense_class_id
2812 FROM pn_rec_expcl_all
2813 WHERE location_id = p_locnid
2814 AND currency_code = p_currency_code;
2815
2816 CURSOR get_locn_prop_id IS
2817 SELECT property_id,
2818 location_id
2819 FROM pn_locations_all
2820 WHERE active_start_date < fnd_date.canonical_to_date(p_to_date)
2821 AND active_end_date > fnd_date.canonical_to_date(p_from_date)
2822 START WITH location_id = p_location_id
2823 CONNECT BY location_id = PRIOR parent_location_id;
2824
2825 l_propid pn_rec_exp_line.property_id%TYPE;
2826 l_dummy VARCHAR2(300);
2827 l_info VARCHAR2(100);
2828 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.populate_expense_class_details' ;
2829 l_total NUMBER := 0;
2830 l_failed NUMBER := 0;
2831
2832 BEGIN
2833 pnp_debug_pkg.log(l_desc ||' (+)');
2834
2835 FOR id IN get_exp_line_id LOOP
2836
2837 IF p_location_id IS NOT NULL THEN
2838
2839 FOR locn_prop_rec IN get_locn_prop_id LOOP
2840
2841 /* If the location belongs to a property, take note of that */
2842 IF l_propid IS NULL THEN l_propid := locn_prop_rec.property_id; END IF;
2843
2844 FOR expcl_rec IN get_expcl_by_locn(locn_prop_rec.location_id, id.currency_code) LOOP
2845 l_info := ' extracting for expense class id : '|| expcl_rec.expense_class_id;
2846 pnp_debug_pkg.log(l_info);
2847
2848 l_total := l_total + 1;
2849
2850 BEGIN
2851
2852 extract_expense(
2853 errbuf => l_dummy,
2854 retcode => l_dummy,
2855 p_expense_class_id => expcl_rec.expense_class_id,
2856 p_as_of_date => p_as_of_date,
2857 p_from_date => p_from_date,
2858 p_to_date => p_to_date,
2859 p_expense_line_id => id.expense_line_id,
2860 p_keep_override => p_keep_override);
2861
2862 EXCEPTION
2863 WHEN OTHERS THEN
2864 l_failed := l_failed + 1;
2865 END;
2866
2867 END LOOP;
2868 END LOOP;
2869 END IF;
2870
2871 IF (p_property_id IS NOT NULL AND p_location_id IS NULL) OR
2872 l_propid IS NOT NULL
2876 l_info := ' extracting for expense class id : '|| expcl_rec.expense_class_id;
2873 THEN
2874
2875 FOR expcl_rec IN get_expcl_by_prop(nvl(l_propid, p_property_id), id.currency_code) LOOP
2877 pnp_debug_pkg.log(l_info);
2878
2879 l_total := l_total + 1;
2880
2881 BEGIN
2882 extract_expense(
2883 errbuf => l_dummy,
2884 retcode => l_dummy,
2885 p_expense_class_id => expcl_rec.expense_class_id,
2886 p_as_of_date => p_as_of_date,
2887 p_from_date => p_from_date,
2888 p_to_date => p_to_date,
2889 p_expense_line_id => id.expense_line_id,
2890 p_keep_override => p_keep_override);
2891
2892 EXCEPTION
2893 WHEN OTHERS THEN
2894 l_failed := l_failed + 1;
2895 END;
2896
2897 END LOOP;
2898
2899 END IF;
2900 END LOOP;
2901
2902 fnd_message.set_name('PN','PN_REC_EXPCL_DTL');
2903 pnp_debug_pkg.put_log_msg('');
2904 pnp_debug_pkg.put_log_msg(fnd_message.get);
2905
2906 fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2907 fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2908 fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2909 fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2910 pnp_debug_pkg.put_log_msg(fnd_message.get);
2911 pnp_debug_pkg.put_log_msg('');
2912
2913 pnp_debug_pkg.log(l_desc ||' (-)');
2914
2915 EXCEPTION
2916 WHEN OTHERS THEN
2917 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2918 raise;
2919 END populate_expense_class_details;
2920
2921 ------------------------------------------------------------------------------+
2922 -- PROCEDURE : populate_area_class_details
2923 -- DESCRIPTION:
2924 -- 1. Given: location id
2925 -- 2. Finds area class details pertinent to that location / property.
2926 --
2927 -- IF both location_id and property_id are provided, ignore property_id
2928 -- IF location_id given, look for its parent location id and associated
2929 -- property_id (if applicable) and get associated class details
2930 --
2931 -- HISTORY:
2932 -- 19-MAR-03 ftanudja o created
2933 -- 03-JUL-03 ftanudja o fixed cursor to handle cases when only prop id given.
2934 -- 10-JUL-03 ftanudja o made prop id and loc id mutex on get_relevant_arcl.
2935 -- o fix logic on getting prop id. 3046470.
2936 -- 08-AUG-03 ftanudja o fix get .. cursor. 3090131.
2937 -- 21-MAY-03 ftanudja o added counters to summarize totals. 3591556.
2938 -- o restructured and fixed CURSOR logic.
2939 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2940 ------------------------------------------------------------------------------+
2941
2942 PROCEDURE populate_area_class_details(
2943 p_location_id pn_rec_exp_line.location_id%TYPE,
2944 p_property_id pn_rec_exp_line.property_id%TYPE,
2945 p_as_of_date VARCHAR2,
2946 p_from_date VARCHAR2,
2947 p_to_date VARCHAR2,
2948 p_keep_override VARCHAR2)
2949 IS
2950
2951 CURSOR get_arcl_by_prop (l_propid pn_rec_exp_line.property_id%TYPE) IS
2952 SELECT area_class_id
2953 FROM pn_rec_arcl_all
2954 WHERE property_id = l_propid
2955 AND location_id IS NULL;
2956
2957 CURSOR get_arcl_by_locn (l_locnid pn_rec_exp_line.location_id%TYPE) IS
2958 SELECT area_class_id
2959 FROM pn_rec_arcl_all
2960 WHERE location_id = l_locnid;
2961
2962 CURSOR get_locn_prop_id IS
2963 SELECT property_id,
2964 location_id
2965 FROM pn_locations_all
2966 WHERE active_start_date < fnd_date.canonical_to_date(p_to_date)
2967 AND active_end_date > fnd_date.canonical_to_date(p_from_date)
2968 START WITH location_id = p_location_id
2969 CONNECT BY location_id = PRIOR parent_location_id;
2970
2971 l_propid pn_rec_exp_line.property_id%TYPE;
2972 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.populate_area_class_details' ;
2973 l_info VARCHAR2(100);
2974 l_dummy VARCHAR2(300);
2975 l_total NUMBER := 0;
2976 l_failed NUMBER := 0;
2977
2978 BEGIN
2979
2980 pnp_debug_pkg.log(l_desc ||' (+)');
2981
2982 IF p_location_id IS NOT NULL THEN
2983
2984 FOR locn_prop_rec IN get_locn_prop_id LOOP
2985
2986 /* If the location belongs to a property, take note of that */
2987 IF l_propid IS NULL THEN l_propid := locn_prop_rec.property_id; END IF;
2988
2989 FOR arcl_rec IN get_arcl_by_locn(locn_prop_rec.location_id) LOOP
2990 l_info := ' extracting for area class id : '|| arcl_rec.area_class_id;
2991 pnp_debug_pkg.log(l_info);
2992
2993 l_total := l_total + 1;
2994
2995 BEGIN
2996 extract_area(
2997 errbuf => l_dummy,
2998 retcode => l_dummy,
2999 p_area_class_id => arcl_rec.area_class_id,
3000 p_as_of_date => p_as_of_date,
3001 p_from_date => p_from_date,
3002 p_to_date => p_to_date,
3003 p_keep_override => p_keep_override);
3004
3008 END;
3005 EXCEPTION
3006 WHEN OTHERS THEN
3007 l_failed := l_failed + 1;
3009
3010 END LOOP;
3011 END LOOP;
3012 END IF;
3013
3014 IF (p_property_id IS NOT NULL AND p_location_id IS NULL) OR
3015 l_propid IS NOT NULL
3016 THEN
3017
3018 FOR arcl_rec IN get_arcl_by_prop(nvl(l_propid, p_property_id)) LOOP
3019 l_info := ' extracting for area class id : '|| arcl_rec.area_class_id;
3020 pnp_debug_pkg.log(l_info);
3021
3022 l_total := l_total + 1;
3023
3024 BEGIN
3025 extract_area(
3026 errbuf => l_dummy,
3027 retcode => l_dummy,
3028 p_area_class_id => arcl_rec.area_class_id,
3029 p_as_of_date => p_as_of_date,
3030 p_from_date => p_from_date,
3031 p_to_date => p_to_date,
3032 p_keep_override => p_keep_override);
3033
3034 EXCEPTION
3035 WHEN OTHERS THEN
3036 l_failed := l_failed + 1;
3037 END;
3038
3039 END LOOP;
3040
3041 END IF;
3042
3043 fnd_message.set_name('PN','PN_REC_ARCL_DTL');
3044 pnp_debug_pkg.put_log_msg('');
3045 pnp_debug_pkg.put_log_msg(fnd_message.get);
3046
3047 fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
3048 fnd_message.set_token('TOTAL', TO_CHAR(l_total));
3049 fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
3050 fnd_message.set_token('FAIL', TO_CHAR(l_failed));
3051 pnp_debug_pkg.put_log_msg(fnd_message.get);
3052 pnp_debug_pkg.put_log_msg('');
3053
3054 pnp_debug_pkg.log(l_desc ||' (-)');
3055
3056 EXCEPTION
3057 WHEN OTHERS THEN
3058 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3059 raise;
3060 END populate_area_class_details;
3061
3062 ------------------------------------------------------------------------------+
3063 -- PROCEDURE : find_expense_ovr_values
3064 -- DESCRIPTION:
3065 -- 1. Given: data table p_master_ovr, p_detail_ovr, parameters p_exp_type, etc.
3066 -- 2. Search through data tables using the parameter criteria.
3067 -- 3. If match found, check if p_keep_override = Y.
3068 -- 4. If true, return override values p_fee_af_contr_ovr, etc.
3069 -- 5. Otherwise, just return the corresponding item id if needed.
3070 --
3071 -- HISTORY:
3072 -- 19-MAR-03 ftanudja o created
3073 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
3074 ------------------------------------------------------------------------------+
3075
3076 PROCEDURE find_expense_ovr_values(
3077 p_master_ovr exp_cls_line_mst_tbl,
3078 p_detail_ovr exp_cls_line_dtl_tbl,
3079 p_exp_type pn_rec_expcl_dtlacc.expense_type_code%TYPE,
3080 p_exp_acct pn_rec_expcl_dtlacc.expense_account_id%TYPE,
3081 p_loc_id pn_rec_expcl_dtlln.location_id%TYPE,
3082 p_cust_id pn_rec_expcl_dtlln.cust_account_id%TYPE,
3083 p_rec_spc_std pn_rec_expcl_dtlln.recovery_space_std_code%TYPE,
3084 p_rec_type pn_rec_expcl_dtlln.recovery_type_code%TYPE,
3085 p_exp_cls_line_dtl_id OUT NOCOPY pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE,
3086 p_exp_cls_line_id OUT NOCOPY pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE,
3087 p_fee_af_contr_ovr OUT NOCOPY pn_rec_expcl_dtlln.cls_line_fee_after_contr_ovr%TYPE,
3088 p_mst_share_pct_ovr OUT NOCOPY pn_rec_expcl_dtlln.cls_line_share_pct%TYPE,
3089 p_dtl_share_pct_ovr OUT NOCOPY pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE,
3090 p_mst_fee_bf_contr_ovr OUT NOCOPY pn_rec_expcl_dtlln.cls_line_fee_before_contr_ovr%TYPE,
3091 p_dtl_fee_bf_contr_ovr OUT NOCOPY pn_rec_expcl_dtlacc.cls_line_dtl_fee_bf_contr%TYPE,
3092 p_found IN OUT NOCOPY BOOLEAN,
3093 p_keep_override VARCHAR2
3094 )
3095 IS
3096 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.find_expense_ovr_values' ;
3097 l_info VARCHAR2(300);
3098 BEGIN
3099
3100 pnp_debug_pkg.log(l_desc ||' (+)');
3101
3102 l_info := ' resetting ovr values variables ';
3103 pnp_debug_pkg.log(l_info);
3104
3105 p_exp_cls_line_dtl_id := null;
3106 p_exp_cls_line_id := null;
3107 p_fee_af_contr_ovr := null;
3108 p_mst_share_pct_ovr := null;
3109 p_dtl_share_pct_ovr := null;
3110 p_mst_fee_bf_contr_ovr := null;
3111 p_dtl_fee_bf_contr_ovr := null;
3112
3113 FOR i IN 0 .. p_master_ovr.COUNT - 1 LOOP
3114 IF p_detail_ovr(i).expense_account_id = p_exp_acct AND
3115 p_detail_ovr(i).expense_type_code = p_exp_type AND
3116 p_master_ovr(i).recovery_space_std_code = p_rec_spc_std AND
3117 p_master_ovr(i).recovery_type_code = p_rec_type AND
3118 p_master_ovr(i).location_id = p_loc_id AND
3119 p_master_ovr(i).cust_account_id = p_cust_id THEN
3120
3121 l_info := ' found matching data and determining which values to return ';
3122 pnp_debug_pkg.log(l_info);
3123
3124 IF p_keep_override = 'Y' THEN
3125 p_fee_af_contr_ovr := p_master_ovr(i).cls_line_fee_after_contr_ovr;
3126 p_mst_share_pct_ovr := p_master_ovr(i).cls_line_share_pct;
3127 p_dtl_share_pct_ovr := p_detail_ovr(i).cls_line_dtl_share_pct_ovr;
3131
3128 p_mst_fee_bf_contr_ovr := p_master_ovr(i).cls_line_fee_before_contr_ovr;
3129 p_dtl_fee_bf_contr_ovr := p_detail_ovr(i).cls_line_dtl_fee_bf_contr_ovr;
3130 END IF;
3132 IF p_found IS NOT NULL THEN
3133 p_found := TRUE;
3134 p_exp_cls_line_dtl_id := p_detail_ovr(i).expense_class_line_dtl_id;
3135 p_exp_cls_line_id := p_master_ovr(i).expense_class_line_id;
3136 END IF;
3137
3138 END IF;
3139 END LOOP;
3140
3141 pnp_debug_pkg.log(l_desc ||' (-)');
3142
3143 EXCEPTION
3144 WHEN OTHERS THEN
3145 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3146 raise;
3147 END find_expense_ovr_values;
3148
3149 ------------------------------------------------------------------------------+
3150 -- PROCEDURE : expense_class_extract
3151 -- ASSUMES : validation done at UI level when calling generate details
3152 -- ,in particular, ensuring expense_line_id is correlated to
3153 -- expense_class_id.
3154 -- DESCRIPTION:
3155 -- o given an expense class id, get details of the expense class.
3156 -- o get all expense lines for locations pertaining to that expense class.
3157 -- o get all space assignment for locations pertaining to that expense class
3158 -- for which the start date and financial obligation date is between
3159 -- the start and end date of the expense line extract.
3160 -- o collect data, do necessary calculations and put them in a pl/sql table.
3161 -- o use pl/sql table to do the proper calculation at both the class line and
3162 -- class line detail level.
3163 -- o dump data into database table; if id already exists, update, otherwise
3164 -- insert.
3165 --
3166 -- HISTORY:
3167 -- 19-MAR-03 ftanudja o created
3168 -- 12-MAY-03 ftanudja o use location / property id from exp lines to get
3169 -- space assignments, as opposed to using the expense
3170 -- class' location / property id.
3171 -- 11-JUN-03 ftanudja o add filter recoverable_flag='Y' in get_exp_lines_info.
3172 -- 10-JUL-03 ftanudja o made loc id and prop id mutex in getting cust asgnmt.
3173 -- 11-JUL-03 ftanudja o changed query for get_exp_line_info. 3045056.
3174 -- 05-AUG-03 ftanudja o added l_updcondition to fix logic. 3075129.
3175 -- o added order by to date and from date for ovr values.
3176 -- o optimized get_ovr_from_prior CURSOR (break in 2).
3177 -- 06-AUG-03 ftanudja o change flow => if found 'PARENT' exp line, do nothing.
3178 -- 18-SEP-03 ftanudja o added currency code check. 3148855.
3179 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
3180 ------------------------------------------------------------------------------+
3181
3182 PROCEDURE extract_expense(
3183 errbuf OUT NOCOPY VARCHAR2,
3184 retcode OUT NOCOPY VARCHAR2,
3185 p_expense_class_id IN pn_rec_expcl.expense_class_id%TYPE,
3186 p_as_of_date IN VARCHAR2,
3187 p_from_date IN VARCHAR2,
3188 p_to_date IN VARCHAR2,
3189 p_expense_line_id IN pn_rec_exp_line.expense_line_id%TYPE,
3190 p_keep_override IN VARCHAR2)
3191 IS
3192
3193 CURSOR check_currency IS
3194 SELECT 1
3195 FROM pn_rec_exp_line_all
3196 WHERE expense_line_id = p_expense_line_id
3197 AND currency_code NOT IN
3198 (SELECT currency_code FROM pn_rec_expcl_all
3199 WHERE expense_class_id = p_expense_class_id);
3200
3201 CURSOR get_exp_class_info IS
3202 SELECT class.expense_class_id,
3203 class_type.expense_class_type_id,
3204 class.area_class_id,
3205 class.location_id,
3206 class.property_id,
3207 class.portion_pct,
3208 class_type.expense_type_code,
3209 class_inclusion.cls_incl_share_pct,
3210 class.class_fee_before_contr,
3211 class.class_fee_after_contr,
3212 class_inclusion.cls_incl_fee_before_contr,
3213 class_inclusion.recovery_type_code,
3214 class_inclusion.recovery_space_std_code
3215 FROM pn_rec_expcl_all class,
3216 pn_rec_expcl_type_all class_type,
3217 pn_rec_expcl_inc_all class_inclusion
3218 WHERE class.expense_class_id = class_type.expense_class_id
3219 AND class_type.expense_class_type_id = class_inclusion.expense_class_type_id
3220 AND class.expense_class_id = p_expense_class_id;
3221
3222 CURSOR get_exp_lines_info (
3223 p_expense_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE) IS
3224 SELECT nvl(lines_dtl.actual_amount_ovr, lines_dtl.actual_amount) actual_amount,
3225 nvl(lines_dtl.budgeted_amount_ovr, lines_dtl.budgeted_amount) budgeted_amount,
3226 lines_dtl.expense_type_code,
3227 lines_dtl.expense_account_id,
3228 lines_dtl.location_id,
3229 lines_dtl.property_id,
3230 lines_dtl.expense_line_dtl_id,
3231 lines_dtl.expense_line_id,
3232 lines_dtl.expense_line_indicator
3233 FROM pn_rec_exp_line_dtl_all lines_dtl
3234 WHERE (lines_dtl.expense_line_id = p_expense_line_id OR
3235 lines_dtl.parent_expense_line_id IN
3236 (SELECT expense_line_dtl_id
3237 FROM pn_rec_exp_line_dtl_all
3238 WHERE expense_line_id = p_expense_line_id))
3239 AND lines_dtl.expense_type_code = p_expense_type_code
3243 p_location_id pn_locations.location_id%TYPE,
3240 AND lines_dtl.recoverable_flag = 'Y';
3241
3242 CURSOR get_cust_assignment_info(
3244 p_property_id pn_locations.property_id%TYPE,
3245 p_rec_spc_std_code pn_space_assign_cust.recovery_space_std_code%TYPE,
3246 p_rec_type_code pn_space_assign_cust.recovery_type_code%TYPE) IS
3247 SELECT cust.cust_space_assign_id,
3248 cust.cust_account_id,
3249 cust.lease_id,
3250 cust.location_id,
3251 cust.recovery_space_std_code,
3252 cust.recovery_type_code
3253 FROM pn_space_assign_cust_all cust
3254 WHERE cust.location_id IN
3255 (SELECT location_id FROM pn_locations_all locn
3256 WHERE locn.active_start_date < fnd_date.canonical_to_date(p_to_date)
3257 AND locn.active_end_date > fnd_date.canonical_to_date(p_from_date))
3258 AND cust.cust_assign_start_date < fnd_date.canonical_to_date(p_to_date)
3259 AND cust.fin_oblig_end_date > fnd_date.canonical_to_date(p_from_date)
3260 AND cust.recovery_space_std_code = p_rec_spc_std_code
3261 AND cust.recovery_type_code = p_rec_type_code
3262 AND cust.location_id IN
3263 (SELECT location_id FROM pn_locations_all
3264 START WITH (location_id = p_location_id OR
3265 (property_id = p_property_id AND p_location_id IS NULL))
3266 CONNECT BY PRIOR location_id = parent_location_id);
3267
3268 CURSOR get_ovr_from_current IS
3269 SELECT class_line.cls_line_share_pct mst_shr_pc,
3270 class_line.cls_line_fee_after_contr_ovr mst_fee_af,
3271 class_line.cls_line_fee_before_contr_ovr mst_fee_bf,
3272 class_line.location_id location_id,
3273 class_line_dtl.cls_line_dtl_fee_bf_contr_ovr dtl_fee_bf,
3274 class_line_dtl.cls_line_dtl_share_pct_ovr dtl_shr_pc,
3275 class_line.cust_account_id cust_account_id,
3276 class_line.recovery_space_std_code rec_space_std,
3277 class_line.recovery_type_code rec_type_code,
3278 class_line_dtl.expense_type_code exp_type,
3279 class_line_dtl.expense_account_id exp_acct,
3280 class_line_dtl.expense_class_line_dtl_id dtl_id,
3281 class_line_dtl.expense_class_line_id mst_id
3282 FROM pn_rec_expcl_dtl_all summary,
3283 pn_rec_expcl_dtlln_all class_line,
3284 pn_rec_expcl_dtlacc_all class_line_dtl,
3285 pn_rec_expcl_all class,
3286 pn_rec_exp_line_all lines
3287 WHERE class_line.expense_class_line_id = class_line_dtl.expense_class_line_id
3288 AND class_line.expense_class_dtl_id = summary.expense_class_dtl_id
3289 AND summary.expense_class_id = class.expense_class_id
3290 AND class.expense_class_id = p_expense_class_id
3291 AND summary.expense_line_id = lines.expense_line_id
3292 AND lines.expense_line_id = p_expense_line_id;
3293
3294 CURSOR get_prior_cls_dtl_id IS
3295 SELECT summary.expense_class_dtl_id
3296 FROM pn_rec_expcl_dtl_all summary,
3297 pn_rec_expcl_all class,
3298 pn_rec_exp_line_all line_hdr,
3299 (SELECT to_date, as_of_date FROM pn_rec_exp_line_all
3300 WHERE expense_line_id = p_expense_line_id) ref_line_hdr
3301 WHERE summary.expense_class_id = class.expense_class_id
3302 AND summary.expense_line_id = line_hdr.expense_line_id
3303 AND class.expense_class_id = p_expense_class_id
3304 AND line_hdr.from_date < ref_line_hdr.to_date
3305 AND line_hdr.to_date <= ref_line_hdr.to_date
3306 AND line_hdr.as_of_date < ref_line_hdr.as_of_date
3307 ORDER BY line_hdr.as_of_date DESC, line_hdr.to_date DESC, line_hdr.from_date DESC;
3308
3309 CURSOR get_ovr_from_prior (p_prior_cls_dtl_id pn_rec_expcl_dtlln.expense_class_dtl_id%TYPE) IS
3310 SELECT class_line.cls_line_share_pct mst_shr_pc,
3311 class_line.cls_line_fee_after_contr_ovr mst_fee_af,
3312 class_line.cls_line_fee_before_contr_ovr mst_fee_bf,
3313 class_line.location_id location_id,
3314 class_line_dtl.cls_line_dtl_fee_bf_contr_ovr dtl_fee_bf,
3315 class_line_dtl.cls_line_dtl_share_pct_ovr dtl_shr_pc,
3316 class_line.cust_account_id cust_account_id,
3317 class_line.recovery_space_std_code rec_space_std,
3318 class_line.recovery_type_code rec_type_code,
3319 class_line_dtl.expense_type_code exp_type,
3320 class_line_dtl.expense_account_id exp_acct
3321 FROM pn_rec_expcl_dtlln_all class_line,
3322 pn_rec_expcl_dtlacc_all class_line_dtl
3323 WHERE class_line.expense_class_line_id = class_line_dtl.expense_class_line_id
3324 AND class_line.expense_class_dtl_id = p_prior_cls_dtl_id;
3325
3326 CURSOR is_reextract IS
3327 SELECT dtl.expense_class_dtl_id,
3328 setup.expense_class_name,
3329 dtl.status,
3330 dtl.default_area_class_id,
3331 dtl.cls_line_portion_pct,
3332 dtl.cls_line_fee_before_contr,
3333 dtl.cls_line_fee_after_contr
3334 FROM pn_rec_expcl_dtl_all dtl,
3335 pn_rec_expcl_all setup
3336 WHERE dtl.expense_line_id = p_expense_line_id
3337 AND setup.expense_class_id = p_expense_class_id
3338 AND setup.expense_class_id = dtl.expense_class_id;
3339
3343 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_expense' ;
3340 l_regenerate VARCHAR2(1);
3341 l_info VARCHAR2(300);
3342 l_dummy VARCHAR2(300);
3344 l_token VARCHAR2(100);
3345
3346 l_master_data_id NUMBER;
3347 l_count NUMBER;
3348 l_found BOOLEAN;
3349 l_updcondition BOOLEAN;
3350 l_dummy_id pn_rec_expcl_dtl.expense_class_dtl_id%TYPE;
3351
3352 l_recov_amount pn_rec_expcl_dtlln.recoverable_amt%TYPE;
3353 l_cpt_recov_amount pn_rec_expcl_dtlln.computed_recoverable_amt%TYPE;
3354 l_mst_share_pct_ovr pn_rec_expcl_dtlln.cls_line_share_pct%TYPE;
3355 l_dtl_share_pct_ovr pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE;
3356 l_fee_af_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_after_contr_ovr%TYPE;
3357 l_dtl_fee_bf_contr_ovr pn_rec_expcl_dtlacc.cls_line_dtl_fee_bf_contr%TYPE;
3358 l_mst_fee_bf_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_before_contr_ovr%TYPE;
3359
3360 l_mst_fee_bf_contr pn_rec_expcl_dtl.cls_line_fee_before_contr%TYPE;
3361 l_fee_af_contr pn_rec_expcl_dtl.cls_line_fee_after_contr%TYPE;
3362 l_portion_pct pn_rec_expcl_dtl.cls_line_portion_pct%TYPE;
3363
3364 l_expense_cls_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE;
3365 l_expense_cls_line_dtl_id pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE;
3366 l_area_class_id pn_rec_expcl.area_class_id%TYPE;
3367 l_expense_class_dtl_id pn_rec_expcl_dtl.expense_class_dtl_id%TYPE;
3368
3369 l_fee_use_table exp_cls_line_use_tbl;
3370 l_share_use_table exp_cls_line_use_tbl;
3371 l_ovr_use_table exp_cls_line_use_tbl;
3372
3373 exp_cls_line_master_data exp_cls_line_mst_tbl;
3374 exp_cls_line_detail_data exp_cls_line_dtl_tbl;
3375 exp_cls_curnt_master_ovr exp_cls_line_mst_tbl;
3376 exp_cls_curnt_detail_ovr exp_cls_line_dtl_tbl;
3377 exp_cls_prior_master_ovr exp_cls_line_mst_tbl;
3378 exp_cls_prior_detail_ovr exp_cls_line_dtl_tbl;
3379
3380 BEGIN
3381 pnp_debug_pkg.log(l_desc ||' (+)');
3382
3383 fnd_message.set_name('PN','PN_REC_EXPCL_DTL_CP_INFO');
3384 fnd_message.set_token('EXPCL', to_char(p_expense_class_id));
3385 fnd_message.set_token('EXPLN', to_char(p_expense_line_id));
3386 fnd_message.set_token('STR' , p_from_date);
3387 fnd_message.set_token('END' , p_to_date);
3388 fnd_message.set_token('AOD' , p_as_of_date);
3389 fnd_message.set_token('OVR' , p_keep_override);
3390 pnp_debug_pkg.put_log_msg('');
3391 pnp_debug_pkg.put_log_msg(fnd_message.get);
3392 pnp_debug_pkg.put_log_msg('');
3393
3394 l_info := ' validating currency';
3395 pnp_debug_pkg.log(l_info);
3396
3397 FOR check_cur IN check_currency LOOP
3398 fnd_message.set_name('PN', 'PN_REC_EXP_CUR_MISMATCH');
3399 RAISE currency_exception;
3400 END LOOP;
3401
3402 l_info := ' initializing values ';
3403 pnp_debug_pkg.log(l_info);
3404
3405 l_fee_use_table.delete;
3406 l_share_use_table.delete;
3407 l_ovr_use_table.delete;
3408
3409 exp_cls_line_master_data.delete;
3410 exp_cls_line_detail_data.delete;
3411 exp_cls_curnt_master_ovr.delete;
3412 exp_cls_curnt_detail_ovr.delete;
3413 exp_cls_prior_master_ovr.delete;
3414 exp_cls_prior_detail_ovr.delete;
3415
3416 l_info := ' caching default and override values';
3417 pnp_debug_pkg.log(l_info);
3418
3419 l_regenerate := 'N';
3420
3421 FOR check_exists IN is_reextract LOOP
3422
3423 IF check_exists.status = 'LOCKED' THEN
3424 fnd_message.set_name('PN','PN_REC_EXPCL_DTL');
3425 l_token := fnd_message.get;
3426 fnd_message.set_name('PN','PN_REC_NO_REGEN_LOCKED');
3427 fnd_message.set_token('MODULE',l_token);
3428 fnd_message.set_token('FDATE', p_from_date);
3429 fnd_message.set_token('TDATE', p_to_date);
3430 fnd_message.set_token('AODATE', p_as_of_date);
3431 fnd_message.set_token('NAME', check_exists.expense_class_name);
3432 pnp_debug_pkg.log(fnd_message.get);
3433 RETURN;
3434 END IF;
3435
3436 l_regenerate := 'Y';
3437 l_area_class_id := check_exists.default_area_class_id;
3438 l_mst_fee_bf_contr := check_exists.cls_line_fee_before_contr;
3439 l_fee_af_contr := check_exists.cls_line_fee_after_contr;
3440 l_portion_pct := check_exists.cls_line_portion_pct;
3441 l_expense_class_dtl_id := check_exists.expense_class_dtl_id;
3442
3443 END LOOP;
3444
3445 IF l_regenerate = 'Y' THEN
3446 FOR get_ovr_rec IN get_ovr_from_current LOOP
3447 l_count := exp_cls_curnt_master_ovr.COUNT;
3448
3449 exp_cls_curnt_master_ovr(l_count).cls_line_share_pct := get_ovr_rec.mst_shr_pc;
3450 exp_cls_curnt_master_ovr(l_count).cls_line_fee_after_contr_ovr := get_ovr_rec.mst_fee_af;
3451 exp_cls_curnt_master_ovr(l_count).cls_line_fee_before_contr_ovr := get_ovr_rec.mst_fee_bf;
3452 exp_cls_curnt_master_ovr(l_count).expense_class_line_id := get_ovr_rec.mst_id;
3453 exp_cls_curnt_master_ovr(l_count).location_id := get_ovr_rec.location_id;
3454 exp_cls_curnt_master_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
3458 exp_cls_curnt_detail_ovr(l_count).cls_line_dtl_fee_bf_contr_ovr := get_ovr_rec.dtl_fee_bf;
3455 exp_cls_curnt_master_ovr(l_count).recovery_space_std_code := get_ovr_rec.rec_space_std;
3456 exp_cls_curnt_master_ovr(l_count).recovery_type_code := get_ovr_rec.rec_type_code;
3457
3459 exp_cls_curnt_detail_ovr(l_count).cls_line_dtl_share_pct_ovr := get_ovr_rec.dtl_shr_pc;
3460 exp_cls_curnt_detail_ovr(l_count).expense_class_line_dtl_id := get_ovr_rec.dtl_id;
3461 exp_cls_curnt_detail_ovr(l_count).expense_type_code := get_ovr_rec.exp_type;
3462 exp_cls_curnt_detail_ovr(l_count).expense_account_id := get_ovr_rec.exp_acct;
3463
3464 END LOOP;
3465 END IF;
3466
3467 l_dummy_id := null;
3468 l_info := ' getting prior cls dtl id for overrides ';
3469 pnp_debug_pkg.log(l_info);
3470
3471 FOR get_first_id IN get_prior_cls_dtl_id LOOP
3472 l_dummy_id := get_first_id.expense_class_dtl_id;
3473 exit;
3474 END LOOP;
3475
3476 FOR get_ovr_rec IN get_ovr_from_prior(l_dummy_id) LOOP
3477
3478 l_count := exp_cls_prior_detail_ovr.COUNT;
3479
3480 exp_cls_prior_master_ovr(l_count).cls_line_share_pct := get_ovr_rec.mst_shr_pc;
3481 exp_cls_prior_master_ovr(l_count).cls_line_fee_after_contr_ovr := get_ovr_rec.mst_fee_af;
3482 exp_cls_prior_master_ovr(l_count).cls_line_fee_before_contr_ovr := get_ovr_rec.mst_fee_bf;
3483 exp_cls_prior_master_ovr(l_count).location_id := get_ovr_rec.location_id;
3484 exp_cls_prior_master_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
3485 exp_cls_prior_master_ovr(l_count).recovery_space_std_code := get_ovr_rec.rec_space_std;
3486 exp_cls_prior_master_ovr(l_count).recovery_type_code := get_ovr_rec.rec_type_code;
3487
3488 exp_cls_prior_detail_ovr(l_count).cls_line_dtl_fee_bf_contr_ovr := get_ovr_rec.dtl_fee_bf;
3489 exp_cls_prior_detail_ovr(l_count).cls_line_dtl_share_pct_ovr := get_ovr_rec.dtl_shr_pc;
3490 exp_cls_prior_detail_ovr(l_count).expense_type_code := get_ovr_rec.exp_type;
3491 exp_cls_prior_detail_ovr(l_count).expense_account_id := get_ovr_rec.exp_acct;
3492
3493 END LOOP;
3494
3495 l_info := ' fetching information to prepare data processing ';
3496 pnp_debug_pkg.log(l_info);
3497
3498 FOR expense_class_rec IN get_exp_class_info LOOP
3499
3500 IF l_expense_class_dtl_id IS NOT NULL THEN
3501 l_updcondition :=
3502 (l_area_class_id = expense_class_rec.area_class_id AND
3503 (l_mst_fee_bf_contr = expense_class_rec.class_fee_before_contr OR
3504 (l_mst_fee_bf_contr IS NULL AND expense_class_rec.class_fee_before_contr IS NULL)) AND
3505 (l_fee_af_contr = expense_class_rec.class_fee_after_contr OR
3506 (l_fee_af_contr IS NULL AND expense_class_rec.class_fee_after_contr IS NULL)) AND
3507 (l_portion_pct = expense_class_rec.portion_pct OR
3508 (l_portion_pct IS NULL AND expense_class_rec.portion_pct IS NULL)));
3509
3510 IF NOT l_updcondition OR l_updcondition IS NULL THEN
3511
3512 l_area_class_id := expense_class_rec.area_class_id;
3513 l_mst_fee_bf_contr := expense_class_rec.class_fee_before_contr;
3514 l_fee_af_contr := expense_class_rec.class_fee_after_contr;
3515 l_portion_pct := expense_class_rec.portion_pct;
3516
3517 pn_rec_expcl_dtl_pkg.update_row(
3518 x_expense_class_id => p_expense_class_id,
3519 x_expense_line_id => p_expense_line_id,
3520 x_expense_class_dtl_id => l_expense_class_dtl_id,
3521 x_status => 'OPEN',
3522 x_def_area_cls_id => l_area_class_id,
3523 x_cls_line_fee_bf_ct => l_mst_fee_bf_contr,
3524 x_cls_line_fee_af_ct => l_fee_af_contr,
3525 x_cls_line_portion_pct => l_portion_pct,
3526 x_last_update_date => SYSDATE,
3527 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
3528 x_creation_date => SYSDATE,
3529 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
3530 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
3531 );
3532
3533 END IF;
3534
3535 ELSIF l_expense_class_dtl_id IS NULL THEN
3536
3537 l_area_class_id := expense_class_rec.area_class_id;
3538 l_mst_fee_bf_contr := expense_class_rec.class_fee_before_contr;
3539 l_fee_af_contr := expense_class_rec.class_fee_after_contr;
3540 l_portion_pct := expense_class_rec.portion_pct;
3541
3542 pn_rec_expcl_dtl_pkg.insert_row(
3543 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
3544 x_expense_class_id => p_expense_class_id,
3545 x_expense_line_id => p_expense_line_id,
3546 x_expense_class_dtl_id => l_expense_class_dtl_id,
3547 x_status => 'OPEN',
3548 x_def_area_cls_id => l_area_class_id,
3549 x_cls_line_fee_bf_ct => l_mst_fee_bf_contr,
3550 x_cls_line_fee_af_ct => l_fee_af_contr,
3551 x_cls_line_portion_pct => l_portion_pct,
3552 x_last_update_date => SYSDATE,
3556 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
3553 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
3554 x_creation_date => SYSDATE,
3555 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
3557 );
3558
3559 END IF;
3560
3561 FOR expense_line_rec IN get_exp_lines_info(expense_class_rec.expense_type_code) LOOP
3562
3563 FOR space_assign_rec IN get_cust_assignment_info(
3564 expense_line_rec.location_id,
3565 expense_line_rec.property_id,
3566 expense_class_rec.recovery_space_std_code,
3567 expense_class_rec.recovery_type_code) LOOP
3568
3569 IF expense_line_rec.expense_line_indicator <> 'PARENT' THEN
3570
3571 l_info := ' trying to find override values for a given line ';
3572 pnp_debug_pkg.log(l_info);
3573
3574 l_found := FALSE;
3575
3576 IF l_regenerate = 'Y' THEN
3577
3578 l_info := ' trying to find override values from regenerated extract';
3579 pnp_debug_pkg.log(l_info);
3580
3581 find_expense_ovr_values(
3582 p_master_ovr => exp_cls_curnt_master_ovr,
3583 p_detail_ovr => exp_cls_curnt_detail_ovr,
3584 p_exp_cls_line_dtl_id => l_expense_cls_line_dtl_id,
3585 p_exp_cls_line_id => l_expense_cls_line_id,
3586 p_exp_type => expense_class_rec.expense_type_code,
3587 p_exp_acct => expense_line_rec.expense_account_id,
3588 p_loc_id => space_assign_rec.location_id,
3589 p_cust_id => space_assign_rec.cust_account_id,
3590 p_rec_spc_std => expense_class_rec.recovery_space_std_code,
3591 p_rec_type => expense_class_rec.recovery_type_code,
3592 p_fee_af_contr_ovr => l_fee_af_contr_ovr,
3593 p_mst_share_pct_ovr => l_mst_share_pct_ovr,
3594 p_dtl_share_pct_ovr => l_dtl_share_pct_ovr,
3595 p_mst_fee_bf_contr_ovr => l_mst_fee_bf_contr_ovr,
3596 p_dtl_fee_bf_contr_ovr => l_dtl_fee_bf_contr_ovr,
3597 p_found => l_found,
3598 p_keep_override => p_keep_override
3599 );
3600
3601 END IF;
3602
3603 IF NOT l_found THEN
3604
3605 l_info := ' trying to find override values from prior extract';
3606 pnp_debug_pkg.log(l_info);
3607
3608 IF l_regenerate = 'N' THEN l_found := null; END IF;
3609
3610 find_expense_ovr_values(
3611 p_master_ovr => exp_cls_prior_master_ovr,
3612 p_detail_ovr => exp_cls_prior_detail_ovr,
3613 p_exp_cls_line_dtl_id => l_expense_cls_line_dtl_id,
3614 p_exp_cls_line_id => l_expense_cls_line_id,
3615 p_exp_type => expense_class_rec.expense_type_code,
3616 p_exp_acct => expense_line_rec.expense_account_id,
3617 p_loc_id => space_assign_rec.location_id,
3618 p_cust_id => space_assign_rec.cust_account_id,
3619 p_rec_spc_std => expense_class_rec.recovery_space_std_code,
3620 p_rec_type => expense_class_rec.recovery_type_code,
3621 p_fee_af_contr_ovr => l_fee_af_contr_ovr,
3622 p_mst_share_pct_ovr => l_mst_share_pct_ovr,
3623 p_dtl_share_pct_ovr => l_dtl_share_pct_ovr,
3624 p_mst_fee_bf_contr_ovr => l_mst_fee_bf_contr_ovr,
3625 p_dtl_fee_bf_contr_ovr => l_dtl_fee_bf_contr_ovr,
3626 p_found => l_found,
3627 p_keep_override => p_keep_override
3628 );
3629
3630 END IF;
3631
3632 l_info := ' calculating recovery amount and computed recovery amount ';
3633 pnp_debug_pkg.log(l_info);
3634
3635 l_recov_amount := expense_line_rec.actual_amount * nvl(expense_class_rec.portion_pct,100) / 100;
3636 l_cpt_recov_amount := l_recov_amount * nvl(nvl(l_dtl_share_pct_ovr, expense_class_rec.cls_incl_share_pct),100) / 100 * (1 + nvl(nvl(l_dtl_fee_bf_contr_ovr, expense_class_rec.cls_incl_fee_before_contr), 0) / 100);
3637
3638 l_info:= ' collecting master class-line data for space assignment '||space_assign_rec.cust_space_assign_id||' ';
3639 pnp_debug_pkg.log(l_info);
3640
3641 process_exp_cls_dtl_mst_data(
3642 p_master_data => exp_cls_line_master_data,
3643 p_ovr_use_data => l_ovr_use_table,
3644 p_fee_use_table => l_fee_use_table,
3645 p_share_use_table => l_share_use_table,
3646 p_master_data_id => l_master_data_id,
3647 p_expense_class_line_id => l_expense_cls_line_id,
3648 p_expense_class_dtl_id => l_expense_class_dtl_id,
3652 p_lease_id => space_assign_rec.lease_id,
3649 p_location_id => space_assign_rec.location_id,
3650 p_cust_space_assign_id => space_assign_rec.cust_space_assign_id,
3651 p_cust_account_id => space_assign_rec.cust_account_id,
3653 p_recovery_space_std_code => expense_class_rec.recovery_space_std_code,
3654 p_recovery_type_code => expense_class_rec.recovery_type_code,
3655 p_budget_amount => expense_line_rec.budgeted_amount,
3656 p_expense_amount => expense_line_rec.actual_amount,
3657 p_recoverable_amount => l_recov_amount,
3658 p_cpt_recoverable_amount => l_cpt_recov_amount,
3659 p_cls_line_share_pct => l_mst_share_pct_ovr,
3660 p_cls_line_fee_af_contr_ovr => l_fee_af_contr_ovr,
3661 p_cls_line_fee_bf_contr_ovr => l_mst_fee_bf_contr_ovr,
3662 p_use_fee_bf_contr => expense_class_rec.cls_incl_fee_before_contr,
3663 p_use_share_pct => expense_class_rec.cls_incl_share_pct,
3664 p_use_prior_ovr => (NOT l_found AND l_regenerate = 'Y')
3665 );
3666
3667 l_info:= ' collecting detail class-line data for space assignment '||space_assign_rec.cust_space_assign_id;
3668 pnp_debug_pkg.log(l_info);
3669
3670 process_exp_cls_dtl_dtl_data(
3671 p_detail_data => exp_cls_line_detail_data,
3672 p_master_data_id => l_master_data_id,
3673 p_expense_class_line_dtl_id => l_expense_cls_line_dtl_id,
3674 p_expense_line_dtl_id => expense_line_rec.expense_line_dtl_id,
3675 p_expense_account_id => expense_line_rec.expense_account_id,
3676 p_expense_type_code => expense_line_rec.expense_type_code,
3677 p_expense_amount => expense_line_rec.actual_amount,
3678 p_budget_amount => expense_line_rec.budgeted_amount,
3679 p_recoverable_amount => l_recov_amount,
3680 p_cpt_recoverable_amount => l_cpt_recov_amount,
3681 p_cls_line_shr_pct => expense_class_rec.cls_incl_share_pct,
3682 p_cls_line_fee_bf_contr => expense_class_rec.cls_incl_fee_before_contr,
3683 p_cls_line_shr_pct_ovr => l_dtl_share_pct_ovr,
3684 p_cls_line_fee_bf_contr_ovr => l_dtl_fee_bf_contr_ovr
3685 );
3686
3687 END IF;
3688
3689 END LOOP;
3690 END LOOP;
3691 END LOOP;
3692
3693 l_info := ' dumping data into table ';
3694 pnp_debug_pkg.log(l_info);
3695
3696 process_exp_class_line_data(
3697 p_old_detail_data => exp_cls_curnt_detail_ovr,
3698 p_old_master_data => exp_cls_curnt_master_ovr,
3699 p_detail_data => exp_cls_line_detail_data,
3700 p_master_data => exp_cls_line_master_data,
3701 p_fee_use_table => l_fee_use_table,
3702 p_share_use_table => l_share_use_table,
3703 p_default_fee_bf => l_mst_fee_bf_contr
3704 );
3705
3706 IF l_area_class_id IS NOT NULL THEN
3707 l_info := ' generating area class detail associated with the expense class ';
3708 pnp_debug_pkg.log(l_info);
3709
3710 extract_area(
3711 errbuf => l_dummy,
3712 retcode => l_dummy,
3713 p_area_class_id => l_area_class_id,
3714 p_as_of_date => p_as_of_date,
3715 p_from_date => p_from_date,
3716 p_to_date => p_to_date,
3717 p_keep_override => p_keep_override);
3718 END IF;
3719 pnp_debug_pkg.log(l_desc ||' (-)');
3720
3721 EXCEPTION
3722 WHEN currency_exception THEN
3723 pnp_debug_pkg.put_log_msg(fnd_message.get);
3724 raise;
3725 WHEN OTHERS THEN
3726 fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
3727 pnp_debug_pkg.put_log_msg(fnd_message.get);
3728 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3729 raise;
3730 END extract_expense;
3731
3732
3733 -------------------------- MAIN EXTRACTION ----------------------------------+
3734
3735 ------------------------------------------------------------------------------+
3736 -- PROCEDURE : extract_line_expense_area
3737 -- DESCRIPTION: main extraction program
3738 -- o check / validation of inputs.
3739 -- o go to interface table and fetch 'new' data only.
3740 -- 'new' => unique combination of expense type and account id for one extract.
3741 -- o create exp line header if it doesn't exist, and create corresponding exp
3742 -- line detail.
3743 -- o if populate detail set to 'Y' then:
3744 -- oo find exp classes above the specified location, and call the expense
3745 -- extract procedure for each one of them.
3746 -- oo find area classes above the specified location, and call the area extract
3747 -- procedure for each one of them.
3748 --
3749 -- HISTORY:
3750 -- 19-MAR-03 ftanudja o created
3751 -- 28-APR-03 ftanudja o split up p_pop_cls_dtl param into area and exp.
3752 -- 13-JUN-03 ftanudja o incorporated messages for input validation errors.
3753 -- 15-JUL-03 ftanudja o fixed main extraction program to not throw error
3757 ------------------------------------------------------------------------------+
3754 -- when check_extr_code returns FALSE when called
3755 -- from rec exp line UI (p_called_from <> 'SRS').
3756 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
3758
3759 PROCEDURE extract_line_expense_area(
3760 errbuf OUT NOCOPY VARCHAR2,
3761 retcode OUT NOCOPY VARCHAR2,
3762 p_location_code IN pn_locations.location_code%TYPE,
3763 p_property_code IN pn_properties.property_code%TYPE,
3764 p_as_of_date IN VARCHAR2,
3765 p_from_date IN VARCHAR2,
3766 p_to_date IN VARCHAR2,
3767 p_currency_code IN pn_rec_exp_line.currency_code%TYPE,
3768 p_pop_exp_class_dtl IN VARCHAR2,
3769 p_pop_area_class_dtl IN VARCHAR2,
3770 p_keep_override IN VARCHAR2,
3771 p_extract_code IN pn_rec_exp_line.expense_extract_code%TYPE,
3772 p_called_from IN VARCHAR2)
3773 IS
3774 l_as_of_date DATE;
3775 l_from_date DATE;
3776 l_to_date DATE;
3777 l_currency pn_rec_exp_line.currency_code%TYPE;
3778 l_info VARCHAR2(300);
3779 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_line_expense_area' ;
3780 l_err VARCHAR2(100);
3781 l_location_id pn_locations.location_id%TYPE;
3782 l_property_id pn_locations.property_id%TYPE;
3783 l_extract_code pn_rec_exp_line.expense_extract_code%TYPE;
3784
3785 CURSOR derive_loc_id_from_loc_code IS
3786 SELECT location_id, property_id
3787 FROM pn_locations
3788 WHERE location_code = p_location_code
3789 AND rownum = 1;
3790
3791 CURSOR derive_prop_id_from_prop_code IS
3792 SELECT property_id
3793 FROM pn_properties
3794 WHERE property_code = p_property_code;
3795
3796 CURSOR get_functional_currency_code IS
3797 SELECT currency_code
3798 FROM gl_sets_of_books
3799 WHERE set_of_books_id = TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
3800 pn_mo_cache_utils.get_current_org_id));
3801
3802 BEGIN
3803
3804 pnp_debug_pkg.log(l_desc ||' (+)');
3805
3806 l_info := ' validating inputs ';
3807 pnp_debug_pkg.log(l_info);
3808
3809 l_from_date := fnd_date.canonical_to_date(p_from_date);
3810 l_to_date := fnd_date.canonical_to_date(p_to_date);
3811 l_as_of_date:= fnd_date.canonical_to_date(p_as_of_date);
3812 l_extract_code := p_extract_code;
3813
3814 IF p_location_code IS NOT NULL THEN
3815 FOR loc_rec IN derive_loc_id_from_loc_code LOOP
3816 l_location_id := loc_rec.location_id;
3817 l_property_id := loc_rec.property_id;
3818 END LOOP;
3819 ELSIF p_property_code IS NOT NULL THEN
3820 FOR prop_rec IN derive_prop_id_from_prop_code LOOP l_property_id := prop_rec.property_id;
3821 END LOOP;
3822 END IF;
3823
3824 IF p_currency_code IS NULL THEN
3825 FOR currency_rec IN get_functional_currency_code LOOP l_currency:= currency_rec.currency_code; END LOOP;
3826 ELSE
3827 l_currency:= p_currency_code;
3828 END IF;
3829
3830 l_info := ' performing input validation ';
3831 pnp_debug_pkg.log(l_info);
3832
3833 IF NOT (l_location_id IS NOT NULL OR l_property_id IS NOT NULL) THEN
3834 fnd_message.set_name('PN','PN_LOC_PROP_REQ');
3835 raise bad_input_exception;
3836
3837 ELSIF NOT check_extract_code(p_extract_code, l_location_id, l_property_id,
3838 l_as_of_date, l_from_date, l_to_date, l_currency) THEN
3839
3840 IF p_extract_code IS NOT NULL AND
3841 p_called_from = 'SRS' THEN
3842
3843 fnd_message.set_name('PN','PN_REC_NONUNIQUE_NUM');
3844 fnd_message.set_token('NUMBER', p_extract_code);
3845 raise bad_input_exception;
3846
3847 ELSIF p_extract_code IS NULL AND
3848 pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_REC_EXPENSE_NUM',
3849 pn_mo_cache_utils.get_current_org_id) = 'N'
3850 THEN
3851
3852 fnd_message.set_name('PN','PN_REC_EXP_NUM_REQ');
3853 raise bad_input_exception;
3854
3855 END IF;
3856
3857 ELSIF NOT check_dates(l_as_of_date, l_from_date, l_to_date, l_location_id, l_property_id, p_extract_code) THEN
3858 fnd_message.set_name('PN','PN_REC_EXT_DT_OVERLAP');
3859 raise bad_input_exception;
3860
3861 ELSIF l_currency IS NULL THEN
3862 app_exception.raise_exception;
3863
3864 END IF;
3865
3866 IF p_called_from = 'SRS' THEN
3867 l_info:= ' performing extraction';
3868 pnp_debug_pkg.log(l_info);
3869
3870 extract_expense_lines(
3871 p_location_id => l_location_id,
3872 p_property_id => l_property_id,
3873 p_as_of_date => l_as_of_date,
3874 p_from_date => l_from_date,
3875 p_to_date => l_to_date,
3876 p_currency_code => l_currency,
3877 p_extract_code => l_extract_code,
3878 p_keep_override => p_keep_override
3879 );
3880 END IF;
3881
3882 IF p_pop_exp_class_dtl = 'Y' THEN
3883 populate_expense_class_details(
3887 p_from_date => p_from_date,
3884 p_location_id => l_location_id,
3885 p_property_id => l_property_id,
3886 p_as_of_date => p_as_of_date,
3888 p_to_date => p_to_date,
3889 p_extract_code => l_extract_code,
3890 p_keep_override => p_keep_override
3891 );
3892 END IF;
3893
3894 IF p_pop_area_class_dtl = 'Y' THEN
3895 populate_area_class_details(
3896 p_location_id => l_location_id,
3897 p_property_id => l_property_id,
3898 p_as_of_date => p_as_of_date,
3899 p_from_date => p_from_date,
3900 p_to_date => p_to_date,
3901 p_keep_override => p_keep_override
3902 );
3903 END IF;
3904
3905 pnp_debug_pkg.log(l_desc ||' (-)');
3906
3907 EXCEPTION
3908 WHEN bad_input_exception THEN
3909 pnp_debug_pkg.log(fnd_message.get);
3910 raise;
3911 WHEN OTHERS THEN
3912 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3913 raise;
3914 END extract_line_expense_area;
3915
3916 ------------------------------------------------------------------------------+
3917 -- PROCEDURE : purge_expense_lines_itf_data
3918 -- ASSUMES : UI validates location code, property code and expense extr code
3919 -- DESCRIPTION:
3920 -- 1. Purges data from interface table based on the given parameters.
3921 -- 2. Used dbms_SQL to build the query.
3922 --
3923 -- HISTORY:
3924 -- 18-APR-03 ftanudja o created
3925 -- 24-MAY-04 ftanudja o fixed from date / to date logic
3926 -- o move logic for delete_all_flag = 'Y' to top.
3927 -- 15-JUL-05 sdnahesh o Replaced base views with their _ALL tables
3928 -- 27-OCT-05 sdmahesh o ATG Mandated changes for SQL literals
3929 ------------------------------------------------------------------------------+
3930
3931 PROCEDURE purge_expense_lines_itf_data(
3932 errbuf OUT NOCOPY VARCHAR2,
3933 retcode OUT NOCOPY VARCHAR2,
3934 p_extract_code IN pn_rec_exp_line.expense_extract_code%TYPE,
3935 p_location_code IN pn_locations.location_code%TYPE,
3936 p_property_code IN pn_properties.property_code%TYPE,
3937 p_from_date IN VARCHAR2,
3938 p_to_date IN VARCHAR2,
3939 p_transfer_flag IN pn_rec_exp_itf.transfer_flag%TYPE,
3940 p_delete_all_flag IN VARCHAR2)
3941 IS
3942
3943 CURSOR derive_loc_id_from_loc_code IS
3944 SELECT location_id
3945 FROM pn_locations
3946 WHERE location_code = p_location_code;
3947
3948 CURSOR derive_prop_id_from_prop_code IS
3949 SELECT property_id
3950 FROM pn_properties
3951 WHERE property_code = p_property_code;
3952
3953 l_loc_id pn_locations.location_id%TYPE;
3954 l_prop_id pn_locations.property_id%TYPE;
3955 l_sqlhead VARCHAR2(300);
3956 l_sqltail VARCHAR2(900) := null;
3957 l_info VARCHAR2(300);
3958 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.purge_expense_lines_itf_data' ;
3959 l_extract_code pn_rec_exp_line.expense_extract_code%TYPE;
3960 l_transfer_flag pn_rec_exp_itf.transfer_flag%TYPE;
3961 l_from_date DATE;
3962 l_to_date DATE;
3963 l_statement VARCHAR2(5000);
3964 l_cursor INTEGER;
3965 l_rows INTEGER;
3966 l_count INTEGER;
3967
3968 BEGIN
3969
3970 pnp_debug_pkg.log(l_desc ||' (+)');
3971
3972 IF p_delete_all_flag = 'Y' THEN
3973 l_info := ' purging everything ';
3974 pnp_debug_pkg.log(l_info);
3975 DELETE pn_rec_exp_itf;
3976 return;
3977 END IF;
3978 pnp_debug_pkg.log('p_extract_code='||p_extract_code);
3979 l_cursor := dbms_sql.open_cursor;
3980 l_sqlhead := 'DELETE pn_rec_exp_itf WHERE ';
3981
3982 l_info := ' figuring expense extract code';
3983 pnp_debug_pkg.log(l_info);
3984 l_extract_code := p_extract_code;
3985 l_transfer_flag := p_transfer_flag;
3986 l_from_date := fnd_date.canonical_to_date(p_from_date);
3987 l_to_date := fnd_date.canonical_to_date(p_to_date);
3988
3989
3990 IF p_extract_code IS NOT NULL THEN
3991
3992 l_sqltail := ' expense_line_dtl_id IN ' ||
3993 '(SELECT dtl.expense_line_dtl_id ' ||
3994 ' FROM pn_rec_exp_line_all hdr, pn_rec_exp_line_dtl_all dtl ' ||
3995 ' WHERE hdr.expense_line_id = dtl.expense_line_id ' ||
3996 ' AND hdr.expense_extract_code = :l_extract_code)';
3997
3998 END IF;
3999
4000 l_info := ' figuring transfer flag';
4001 pnp_debug_pkg.log(l_info);
4002
4003 IF p_transfer_flag IS NOT NULL THEN
4004 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4005 l_sqltail := l_sqltail || ' transfer_flag = :l_transfer_flag';
4006 END IF;
4007
4008 l_info := ' figuring from date';
4009 pnp_debug_pkg.log(l_info);
4010
4011 IF p_from_date IS NOT NULL THEN
4012 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4013 l_sqltail := l_sqltail ||' from_date >= :l_from_date)';
4014
4015 END IF;
4016
4017 l_info := ' figuring to date';
4018 pnp_debug_pkg.log(l_info);
4019
4020 IF p_to_date IS NOT NULL THEN
4021 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4022 l_sqltail := l_sqltail ||' to_date <= :l_to_date)';
4023
4024 END IF;
4025
4026 l_info := ' figuring location code';
4027 pnp_debug_pkg.log(l_info);
4028
4029 IF p_location_code IS NOT NULL THEN
4030 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4031 FOR loc_rec IN derive_loc_id_from_loc_code LOOP l_loc_id := loc_rec.location_id; END LOOP;
4032 l_sqltail := l_sqltail || ' location_id = :l_loc_id';
4033
4034 END IF;
4035
4036 l_info := ' figuring property code';
4037 pnp_debug_pkg.log(l_info);
4038
4039 IF p_property_code IS NOT NULL THEN
4040 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4044 END IF;
4041 FOR prop_rec IN derive_prop_id_from_prop_code LOOP l_prop_id := prop_rec.property_id; END LOOP;
4042 l_sqltail := l_sqltail || ' property_id = :l_prop_id';
4043
4045
4046
4047
4048 IF l_sqltail IS NOT NULL THEN
4049
4050 l_info := ' deleting using dynamic SQL';
4051 pnp_debug_pkg.log(l_info);
4052 pnp_debug_pkg.log('');
4053 pnp_debug_pkg.log(l_sqlhead);
4054 pnp_debug_pkg.log(l_sqltail);
4055 pnp_debug_pkg.log('');
4056
4057 l_statement := l_sqlhead || l_sqltail;
4058 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
4059 IF p_extract_code IS NOT NULL THEN
4060 dbms_sql.bind_variable(l_cursor,'l_extract_code',l_extract_code);
4061 END IF;
4062
4063 IF p_transfer_flag IS NOT NULL THEN
4064 dbms_sql.bind_variable(l_cursor,'l_transfer_flag',l_transfer_flag);
4065 END IF;
4066
4067 IF p_from_date IS NOT NULL THEN
4068 dbms_sql.bind_variable(l_cursor,'l_from_date',l_from_date);
4069 END IF;
4070
4071 IF p_to_date IS NOT NULL THEN
4072 dbms_sql.bind_variable(l_cursor,'l_to_date',l_to_date);
4073 END IF;
4074
4075 IF p_location_code IS NOT NULL THEN
4076 dbms_sql.bind_variable(l_cursor,'l_loc_id',l_loc_id);
4077 END IF;
4078
4079 IF p_property_code IS NOT NULL THEN
4080 dbms_sql.bind_variable(l_cursor,'l_prop_id',l_prop_id);
4081 END IF;
4082 l_rows := dbms_sql.execute(l_cursor);
4083
4084
4085 END IF;
4086
4087 IF dbms_sql.is_open (l_cursor) THEN
4088 dbms_sql.close_cursor (l_cursor);
4089 END IF;
4090 pnp_debug_pkg.log(l_desc ||' (-)');
4091
4092 EXCEPTION
4093 WHEN OTHERS THEN
4094 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
4095 raise;
4096 END;
4097
4098 END pn_recovery_extract_pkg;