[Home] [Help]
PACKAGE BODY: APPS.PN_RECOVERY_EXTRACT_PKG
Source
1 PACKAGE BODY pn_recovery_extract_pkg AS
2 /* $Header: PNRCEXTB.pls 120.5.12010000.2 2010/04/26 18:57:26 asahoo 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';
407 END IF;
408
409 l_info := ' determining flags ';
410 pnp_debug_pkg.log(l_info);
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
545 FOR i IN 0 .. p_date_table.count - 2 LOOP
546
547 l_from := p_date_table(i);
548 l_to := p_date_table(i+1) - 1;
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 +
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);
662 END IF;
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,
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,
768 x_vacant_area_ovr => p_new_data(i).vacant_area_ovr,
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
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.
874 -- o if the location meets the criteria specified in the area class, then
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 -- 26-APR-10 asahoo o Bug#9579092 Modified the condition to calculate total_assignable_area, total_occupied_area, total_occupied_area_ovr,
918 -- total_weighted_avg and total_weighted_avg_ovr
919 ------------------------------------------------------------------------------+
920
921 PROCEDURE extract_area(
922 errbuf OUT NOCOPY VARCHAR2,
923 retcode OUT NOCOPY VARCHAR2,
924 p_area_class_id IN pn_rec_arcl.area_class_id%TYPE,
925 p_as_of_date IN VARCHAR2,
926 p_from_date IN VARCHAR2,
927 p_to_date IN VARCHAR2,
928 p_keep_override IN VARCHAR2)
929 IS
930 CURSOR get_area_class_info IS
931 SELECT class.area_class_id,
932 class.property_id,
933 class.location_id,
934 excl_dtl.exclusion_type_code,
935 excl_dtl.relational_code,
936 excl_dtl.area,
937 excl_dtl.area_class_exclusion_id,
938 excl_dtl.recovery_space_std_code,
939 excl_dtl.recovery_type_code
940 FROM pn_rec_arcl_all class,
941 pn_rec_arcl_exc_all excl_dtl
942 WHERE class.area_class_id = excl_dtl.area_class_id (+)
943 AND class.area_class_id = p_area_class_id;
944
945 CURSOR get_location_info(
946 p_location_id pn_locations.location_id%TYPE,
947 p_property_id pn_locations.property_id%TYPE) IS
948 SELECT location_id,
949 property_id,
950 active_start_date,
951 active_end_date,
952 assignable_area
953 FROM pn_locations_all
954 WHERE location_type_lookup_code IN ('SECTION','OFFICE')
955 AND active_start_date < fnd_date.canonical_to_date(p_to_date)
956 AND active_end_date > fnd_date.canonical_to_date(p_from_date)
957 AND location_id IN
958 (SELECT location_id FROM pn_locations_all
959 START WITH (location_id = p_location_id OR
960 (property_id = p_property_id AND p_location_id IS NULL))
961 CONNECT BY PRIOR location_id = parent_location_id)
962 ORDER BY location_id;
963
964 CURSOR get_cust_assignment_info(p_location_id pn_locations.location_id%TYPE) IS
965 SELECT cust_space_assign_id,
966 cust_account_id,
967 allocated_area,
968 cust_assign_start_date,
969 fin_oblig_end_date,
970 lease_id,
971 recovery_type_code,
972 recovery_space_std_code
973 FROM pn_space_assign_cust_all cust
974 WHERE cust.location_id = p_location_id
975 AND cust.cust_assign_start_date < fnd_date.canonical_to_date(p_to_date)
976 AND cust.fin_oblig_end_date > fnd_date.canonical_to_date(p_from_date);
977
978 CURSOR get_uom_info_from_prop(p_property_id pn_locations.property_id%TYPE) IS
979 SELECT uom_code
980 FROM pn_locations_all
981 WHERE property_id = p_property_id
982 AND active_start_date < fnd_date.canonical_to_date(p_to_date)
983 AND active_end_date > fnd_date.canonical_to_date(p_from_date);
984
985 CURSOR get_uom_info_from_locn(p_location_id pn_locations.location_id%TYPE) IS
986 SELECT uom_code
987 FROM pn_locations_all
988 WHERE location_id IN
989 (SELECT location_id FROM pn_locations_all
990 START WITH location_id = p_location_id
991 CONNECT BY location_id = PRIOR parent_location_id)
992 AND parent_location_id IS NULL
993 AND active_start_date < fnd_date.canonical_to_date(p_to_date)
994 AND active_end_date > fnd_date.canonical_to_date(p_from_date);
995
996 CURSOR get_ovr_from_curnt IS
997 SELECT dtl.location_id,
998 dtl.from_date,
999 dtl.to_date,
1000 dtl.cust_account_id,
1001 dtl.weighted_avg_ovr,
1002 dtl.occupied_area_ovr,
1003 dtl.assigned_area_ovr,
1004 dtl.exclude_prorata_flag,
1005 dtl.exclude_prorata_ovr_flag,
1006 dtl.exclude_area_flag,
1007 dtl.exclude_area_ovr_flag,
1008 dtl.recovery_space_std_code,
1009 dtl.recovery_type_code,
1010 dtl.area_class_dtl_line_id,
1011 hdr.area_class_dtl_id,
1012 hdr.status,
1013 setup.area_class_name
1014 FROM pn_rec_arcl_dtlln_all dtl,
1015 pn_rec_arcl_dtl_all hdr,
1016 pn_rec_arcl_all setup
1017 WHERE dtl.area_class_dtl_id (+) = hdr.area_class_dtl_id
1018 AND hdr.area_class_id = p_area_class_id
1019 AND TRUNC(hdr.as_of_date) = TRUNC(fnd_date.canonical_to_date(p_as_of_date))
1020 AND TRUNC(hdr.from_date) = TRUNC(fnd_date.canonical_to_date(p_from_date))
1021 AND TRUNC(hdr.to_date) = TRUNC(fnd_date.canonical_to_date(p_to_date))
1022 AND setup.area_class_id = hdr.area_class_id;
1023
1024 CURSOR get_prior_cls_dtl_id IS
1025 SELECT area_class_dtl_id
1026 FROM pn_rec_arcl_dtl_all
1027 WHERE from_date < fnd_date.canonical_to_date(p_to_date)
1028 AND to_date <= fnd_date.canonical_to_date(p_to_date)
1029 AND as_of_date < fnd_date.canonical_to_date(p_as_of_date)
1030 AND area_class_id = p_area_class_id
1031 ORDER BY as_of_date DESC, to_date DESC , from_date DESC;
1032
1033 CURSOR get_ovr_from_prior(p_prior_cls_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE) IS
1034 SELECT location_id,
1035 from_date,
1036 to_date,
1037 cust_account_id,
1038 weighted_avg_ovr,
1039 occupied_area_ovr,
1040 assigned_area_ovr,
1041 exclude_prorata_flag,
1042 exclude_prorata_ovr_flag,
1043 exclude_area_flag,
1044 exclude_area_ovr_flag,
1045 recovery_space_std_code,
1046 recovery_type_code
1047 FROM pn_rec_arcl_dtlln_all
1048 WHERE area_class_dtl_id = p_prior_cls_dtl_id;
1049
1050 l_area_class_dtl_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1051 l_area_class_dtl_line_id pn_rec_arcl_dtlln.area_class_dtl_line_id%TYPE;
1052 l_dummy_id pn_rec_arcl_dtl.area_class_dtl_id%TYPE;
1053 l_count NUMBER;
1054 l_data_tbl_counter NUMBER;
1055 l_regenerate VARCHAR2(1);
1056 l_found BOOLEAN;
1057 l_token VARCHAR2(100);
1058 l_is_assigned BOOLEAN;
1059 l_meets_criteria BOOLEAN;
1060 l_temp_loc_id pn_rec_arcl.location_id%TYPE := NULL;
1061 l_temp_prop_id pn_rec_arcl.property_id%TYPE := NULL;
1062 l_temp_assignable_area pn_rec_arcl_dtlln.assignable_area%TYPE := NULL;
1063 l_from_date pn_rec_arcl_dtlln.from_date%TYPE;
1064 l_to_date pn_rec_arcl_dtlln.to_date%TYPE;
1065 l_occup_pct pn_rec_arcl_dtlln.occupancy_pct%TYPE;
1066 l_weighted_avg pn_rec_arcl_dtlln.weighted_avg%TYPE;
1067 l_occup_area pn_rec_arcl_dtlln.occupied_area%TYPE;
1068 l_excl_type pn_rec_arcl_exc.exclusion_type_code%TYPE;
1069 l_excl_prorata_flag pn_rec_arcl_dtlln.exclude_prorata_flag%TYPE;
1070 l_excl_area_flag pn_rec_arcl_dtlln.exclude_area_flag%TYPE;
1071 l_include_flag pn_rec_arcl_dtlln.include_flag%TYPE;
1072 l_occup_area_ovr pn_rec_arcl_dtlln.occupied_area_ovr%TYPE;
1073 l_weighted_avg_ovr pn_rec_arcl_dtlln.weighted_avg_ovr%TYPE;
1074 l_assigned_area_ovr pn_rec_arcl_dtlln.assigned_area_ovr%TYPE;
1075 l_excl_area_ovr_flag pn_rec_arcl_dtlln.exclude_area_ovr_flag%TYPE;
1076 l_excl_prorata_ovr_flag pn_rec_arcl_dtlln.exclude_prorata_ovr_flag%TYPE;
1077 l_vacancy_num_table number_table_type;
1078 l_vacancy_date_table date_table_type;
1079 l_ref_vacancy_num_table number_table_type;
1080 l_ref_vacancy_date_table date_table_type;
1081 l_area_cls_ln_data_tbl area_cls_line_dtl_tbl;
1082 l_area_cls_ln_curnt_ovr area_cls_line_dtl_tbl;
1083 l_area_cls_ln_prior_ovr area_cls_line_dtl_tbl;
1084 l_area_total_tbl area_cls_line_hdr_tbl;
1085 l_arcl_locid pn_rec_arcl.location_id%TYPE;
1086 l_arcl_propid pn_rec_arcl.property_id%TYPE;
1087 l_arcl_exc_table area_cls_exc_tbl;
1088 l_uom_code pn_locations_all.uom_code%TYPE;
1089 l_info VARCHAR2(100);
1090 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_area' ;
1091
1092
1093 BEGIN
1094
1095 pnp_debug_pkg.log(l_desc ||' (+)');
1096
1097 fnd_message.set_name('PN','PN_REC_ARCL_DTL_CP_INFO');
1098 fnd_message.set_token('ARCL' , to_char(p_area_class_id));
1099 fnd_message.set_token('STR' , p_from_date);
1100 fnd_message.set_token('END' , p_to_date);
1101 fnd_message.set_token('AOD' , p_as_of_date);
1102 fnd_message.set_token('OVR' , p_keep_override);
1103 pnp_debug_pkg.put_log_msg('');
1104 pnp_debug_pkg.put_log_msg(fnd_message.get);
1105 pnp_debug_pkg.put_log_msg('');
1106
1107 l_vacancy_date_table.delete;
1108 l_vacancy_num_table.delete;
1109 l_ref_vacancy_date_table.delete;
1110 l_ref_vacancy_num_table.delete;
1111
1112 l_area_cls_ln_curnt_ovr.delete;
1113 l_area_cls_ln_prior_ovr.delete;
1114 l_area_cls_ln_data_tbl.delete;
1115
1116 l_arcl_exc_table.delete;
1117
1118 l_area_total_tbl(0).total_assignable_area := 0;
1119 l_area_total_tbl(0).total_occupied_area := 0;
1120 l_area_total_tbl(0).total_occupied_area_ovr := 0;
1121 l_area_total_tbl(0).total_occupied_area_exc := 0;
1122 l_area_total_tbl(0).total_vacant_area := 0;
1123 l_area_total_tbl(0).total_vacant_area_ovr := 0;
1124 l_area_total_tbl(0).total_vacant_area_exc := 0;
1125 l_area_total_tbl(0).total_weighted_avg := 0;
1126 l_area_total_tbl(0).total_weighted_avg_ovr := 0;
1127 l_area_total_tbl(0).total_weighted_avg_exc := 0;
1128
1129 l_info:= ' fetching area class information';
1130 pnp_debug_pkg.log(l_info);
1131
1132 FOR area_class_rec IN get_area_class_info LOOP
1133 IF l_arcl_locid IS NULL THEN l_arcl_locid := area_class_rec.location_id; END IF;
1134 IF l_arcl_propid IS NULL THEN l_arcl_propid := area_class_rec.property_id; END IF;
1135
1136 l_count := l_arcl_exc_table.COUNT;
1137
1138 l_arcl_exc_table(l_count).recovery_space_std_code := area_class_rec.recovery_space_std_code;
1139 l_arcl_exc_table(l_count).recovery_type_code := area_class_rec.recovery_type_code;
1140 l_arcl_exc_table(l_count).exclusion_type_code := area_class_rec.exclusion_type_code;
1141 l_arcl_exc_table(l_count).relational_code := area_class_rec.relational_code;
1142 l_arcl_exc_table(l_count).area := area_class_rec.area;
1143
1144 END LOOP;
1145
1146 l_info := ' validating UOM is unique for location and property';
1147 pnp_debug_pkg.log(l_info);
1148
1149 IF l_arcl_propid IS NOT NULL AND l_arcl_locid IS NULL THEN
1150 FOR validate_rec IN get_uom_info_from_prop(l_arcl_propid) LOOP
1151 IF l_uom_code IS NULL THEN l_uom_code := validate_rec.uom_code;
1152 ELSIF l_uom_code <> validate_rec.uom_code THEN
1153 fnd_message.set_name('PN', 'PN_REC_UOM_MULTIPLE');
1154 RAISE uom_exception;
1155 END IF;
1156 END LOOP;
1157 ELSIF l_arcl_locid IS NOT NULL THEN
1158 FOR validate_rec IN get_uom_info_from_locn(l_arcl_locid) LOOP
1159 IF l_uom_code IS NULL THEN l_uom_code := validate_rec.uom_code;
1160 ELSIF l_uom_code <> validate_rec.uom_code THEN
1161 fnd_message.set_name('PN', 'PN_REC_UOM_MULTIPLE');
1162 RAISE uom_exception;
1163 END IF;
1164 END LOOP;
1165 END IF;
1166
1167 l_info := ' finding overrides and processing header information ';
1168 pnp_debug_pkg.log(l_info);
1169
1170 l_regenerate := 'Y';
1171 l_count := -1;
1172
1173 FOR get_ovr_rec IN get_ovr_from_curnt LOOP
1174
1175 IF get_ovr_rec.status = 'LOCKED' THEN
1176 fnd_message.set_name('PN','PN_REC_ARCL_DTL');
1177 l_token := fnd_message.get;
1178 fnd_message.set_name('PN','PN_REC_NO_REGEN_LOCKED');
1179 fnd_message.set_token('MODULE',l_token);
1180 fnd_message.set_token('FDATE', p_from_date);
1181 fnd_message.set_token('TDATE', p_to_date);
1182 fnd_message.set_token('AODATE', p_as_of_date);
1183 fnd_message.set_token('NAME', get_ovr_rec.area_class_name);
1184 pnp_debug_pkg.log(fnd_message.get);
1185 RETURN;
1186 END IF;
1187
1188 l_count := l_area_cls_ln_curnt_ovr.COUNT;
1189
1190 l_area_cls_ln_curnt_ovr(l_count).location_id := get_ovr_rec.location_id;
1191 l_area_cls_ln_curnt_ovr(l_count).from_date := get_ovr_rec.from_date;
1192 l_area_cls_ln_curnt_ovr(l_count).to_date := get_ovr_rec.to_date;
1193 l_area_cls_ln_curnt_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
1194 l_area_cls_ln_curnt_ovr(l_count).weighted_avg_ovr := get_ovr_rec.weighted_avg_ovr;
1195 l_area_cls_ln_curnt_ovr(l_count).occupied_area_ovr := get_ovr_rec.occupied_area_ovr;
1196 l_area_cls_ln_curnt_ovr(l_count).assigned_area_ovr := get_ovr_rec.assigned_area_ovr;
1197 l_area_cls_ln_curnt_ovr(l_count).exclude_prorata_flag := get_ovr_rec.exclude_prorata_flag;
1198 l_area_cls_ln_curnt_ovr(l_count).exclude_prorata_ovr_flag:= get_ovr_rec.exclude_prorata_ovr_flag;
1199 l_area_cls_ln_curnt_ovr(l_count).exclude_area_flag := get_ovr_rec.exclude_area_flag;
1200 l_area_cls_ln_curnt_ovr(l_count).exclude_area_ovr_flag := get_ovr_rec.exclude_area_ovr_flag;
1201 l_area_cls_ln_curnt_ovr(l_count).recovery_space_std_code := get_ovr_rec.recovery_space_std_code;
1202 l_area_cls_ln_curnt_ovr(l_count).recovery_type_code := get_ovr_rec.recovery_type_code;
1203 l_area_cls_ln_curnt_ovr(l_count).area_class_dtl_line_id := get_ovr_rec.area_class_dtl_line_id;
1204
1205 l_area_class_dtl_id := get_ovr_rec.area_class_dtl_id;
1206
1207 END LOOP;
1208
1209 IF l_area_class_dtl_id IS NULL THEN
1210 l_regenerate := 'N';
1211
1212 END IF;
1213
1214 l_info := ' getting prior cls dtl id for overrides';
1215 pnp_debug_pkg.log(l_info);
1216
1217 FOR get_first_id IN get_prior_cls_dtl_id LOOP
1218 l_dummy_id := get_first_id.area_class_dtl_id;
1219 exit;
1220 END LOOP;
1221
1222 FOR get_ovr_rec IN get_ovr_from_prior(l_dummy_id) LOOP
1223
1224 l_count := l_area_cls_ln_prior_ovr.COUNT;
1225
1226 l_area_cls_ln_prior_ovr(l_count).location_id := get_ovr_rec.location_id;
1227 l_area_cls_ln_prior_ovr(l_count).from_date := get_ovr_rec.from_date;
1228 l_area_cls_ln_prior_ovr(l_count).to_date := get_ovr_rec.to_date;
1229 l_area_cls_ln_prior_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
1230 l_area_cls_ln_prior_ovr(l_count).weighted_avg_ovr := get_ovr_rec.weighted_avg_ovr;
1231 l_area_cls_ln_prior_ovr(l_count).occupied_area_ovr := get_ovr_rec.occupied_area_ovr;
1232 l_area_cls_ln_prior_ovr(l_count).assigned_area_ovr := get_ovr_rec.assigned_area_ovr;
1233 l_area_cls_ln_prior_ovr(l_count).exclude_prorata_flag := get_ovr_rec.exclude_prorata_flag;
1234 l_area_cls_ln_prior_ovr(l_count).exclude_prorata_ovr_flag:= get_ovr_rec.exclude_prorata_ovr_flag;
1235 l_area_cls_ln_prior_ovr(l_count).exclude_area_flag := get_ovr_rec.exclude_area_flag;
1236 l_area_cls_ln_prior_ovr(l_count).exclude_area_ovr_flag := get_ovr_rec.exclude_area_ovr_flag;
1237 l_area_cls_ln_prior_ovr(l_count).recovery_space_std_code := get_ovr_rec.recovery_space_std_code;
1238 l_area_cls_ln_prior_ovr(l_count).recovery_type_code := get_ovr_rec.recovery_type_code;
1239
1240 END LOOP;
1241
1242 FOR location_rec IN get_location_info(l_arcl_locid, l_arcl_propid) LOOP
1243
1244 l_info := ' processing location id: '|| location_rec.location_id ||' ';
1245 pnp_debug_pkg.log(l_info);
1246
1247 l_is_assigned := FALSE;
1248
1249 IF l_temp_loc_id IS NULL OR
1250 l_temp_loc_id <> location_rec.location_id THEN
1251
1252 IF l_temp_loc_id IS NOT NULL THEN
1253
1254 -- generate vacancy data for that location id
1255
1256 l_info := ' inserting vacancy data into details table for location: '||l_temp_loc_id||' ';
1257 pnp_debug_pkg.log(l_info);
1258
1259 insert_vacancy_data(p_location_id => l_temp_loc_id,
1260 p_property_id => l_temp_prop_id,
1261 p_date_table => l_vacancy_date_table,
1262 p_num_table => l_vacancy_num_table,
1263 p_ref_date_table => l_ref_vacancy_date_table,
1264 p_ref_num_table => l_ref_vacancy_num_table,
1265 p_from_date => fnd_date.canonical_to_date(p_from_date),
1266 p_to_date => fnd_date.canonical_to_date(p_to_date),
1267 p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1268 p_assignable_area => l_temp_assignable_area,
1269 p_curnt_ovr => l_area_cls_ln_curnt_ovr,
1270 p_prior_ovr => l_area_cls_ln_prior_ovr,
1271 p_data_tbl => l_area_cls_ln_data_tbl,
1272 p_total_tbl => l_area_total_tbl,
1273 p_keep_override => p_keep_override,
1274 p_regenerate => l_regenerate
1275 );
1276
1277 /* reset */
1278
1279 l_vacancy_num_table.delete;
1280 l_vacancy_date_table.delete;
1281 l_ref_vacancy_num_table.delete;
1282 l_ref_vacancy_date_table.delete;
1283
1284 END IF;
1285
1286 l_temp_loc_id := location_rec.location_id;
1287 l_temp_prop_id := location_rec.property_id;
1288 l_temp_assignable_area := location_rec.assignable_area;
1289
1290 -- process data for vacancy details purposes
1291
1292 FOR space_assign_rec IN get_cust_assignment_info(location_rec.location_id) LOOP
1293 l_is_assigned := TRUE;
1294
1295 l_info := ' checking whether space assignment: '|| space_assign_rec.cust_space_assign_id ||
1296 ' meets exclusion criteria ';
1297 pnp_debug_pkg.log(l_info);
1298
1299 l_meets_criteria := FALSE;
1300
1301 FOR i IN 0 .. l_arcl_exc_table.COUNT - 1 LOOP
1302 IF l_arcl_exc_table(i).recovery_type_code = space_assign_rec.recovery_type_code AND
1303 l_arcl_exc_table(i).recovery_space_std_code = space_assign_rec.recovery_space_std_code THEN
1304
1305 IF l_arcl_exc_table(i).relational_code = 'EQ' 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 = 'GT' THEN
1308 IF location_rec.assignable_area > l_arcl_exc_table(i).area THEN l_meets_criteria := TRUE; END IF;
1309 ELSIF l_arcl_exc_table(i).relational_code = 'LT' THEN
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 = 'GE' 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 = 'LE' THEN
1314 IF location_rec.assignable_area <= l_arcl_exc_table(i).area THEN l_meets_criteria := TRUE; END IF;
1315 ELSIF l_arcl_exc_table(i).relational_code IS NULL THEN
1316 l_meets_criteria := TRUE;
1317 END IF;
1318
1319 l_excl_type := l_arcl_exc_table(i).exclusion_type_code;
1320 exit;
1321
1322 END IF;
1323 END LOOP;
1324
1325 -- put in assignments for the occupancy details
1326
1327 l_from_date := space_assign_rec.cust_assign_start_date;
1328 l_to_date := space_assign_rec.fin_oblig_end_date;
1329 l_info := ' getting details for cust assignment :'||space_assign_rec.cust_space_assign_id||' ';
1330 pnp_debug_pkg.log(l_info);
1331
1332 IF NOT l_meets_criteria THEN l_excl_type := null; END IF;
1333
1334 get_area_cls_dtl_calc(
1335 p_from_date => fnd_date.canonical_to_date(p_from_date),
1336 p_to_date => fnd_date.canonical_to_date(p_to_date),
1337 p_rec_from_date => l_from_date,
1338 p_rec_to_date => l_to_date,
1339 p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1340 p_assigned_area => space_assign_rec.allocated_area,
1341 p_exc_type_code => l_excl_type,
1342 p_occup_pct => l_occup_pct,
1343 p_weighted_avg => l_weighted_avg,
1344 p_occup_area => l_occup_area,
1345 p_exc_prorata_flag => l_excl_prorata_flag,
1346 p_exc_area_flag => l_excl_area_flag,
1347 p_include_flag => l_include_flag);
1348
1349 l_info := ' finding overrides and processing into pl/sql table the details of '||
1350 'cust assignment:'||space_assign_rec.cust_space_assign_id||' ';
1351 pnp_debug_pkg.log(l_info);
1352
1353 l_found := FALSE;
1354
1355 IF l_regenerate = 'Y' THEN
1356
1357 find_area_ovr_values(
1358 p_ovr => l_area_cls_ln_curnt_ovr,
1359 p_loc_id => location_rec.location_id,
1360 p_cust_id => space_assign_rec.cust_account_id,
1361 p_from => l_from_date,
1362 p_to => l_to_date,
1363 p_weighted_avg_ovr => l_weighted_avg_ovr,
1364 p_occupied_area_ovr => l_occup_area_ovr,
1365 p_assigned_area_ovr => l_assigned_area_ovr,
1366 p_exc_area_ovr_flag => l_excl_area_ovr_flag,
1367 p_exc_prorata_ovr_flag => l_excl_prorata_ovr_flag,
1368 p_area_cls_dtl_line_id => l_area_class_dtl_line_id,
1369 p_found => l_found,
1370 p_keep_override => p_keep_override
1371 );
1372
1373 END IF;
1374
1375 IF NOT l_found THEN
1376
1377 IF l_regenerate = 'N' THEN l_found := null; END IF;
1378
1379 find_area_ovr_values(
1380 p_ovr => l_area_cls_ln_prior_ovr,
1381 p_loc_id => location_rec.location_id,
1382 p_cust_id => space_assign_rec.cust_account_id,
1383 p_from => l_from_date,
1384 p_to => l_to_date,
1385 p_weighted_avg_ovr => l_weighted_avg_ovr,
1386 p_occupied_area_ovr => l_occup_area_ovr,
1387 p_assigned_area_ovr => l_assigned_area_ovr,
1388 p_exc_area_ovr_flag => l_excl_area_ovr_flag,
1389 p_exc_prorata_ovr_flag => l_excl_prorata_ovr_flag,
1390 p_area_cls_dtl_line_id => l_area_class_dtl_line_id,
1391 p_found => l_found,
1392 p_keep_override => p_keep_override
1393 );
1394
1395 END IF;
1396
1397 l_data_tbl_counter := l_area_cls_ln_data_tbl.COUNT;
1398
1399 -- if there are no overrides, then the value is null, for which we default to be equal the normal
1400
1401 IF l_excl_prorata_ovr_flag IS NULL THEN l_excl_prorata_ovr_flag := l_excl_prorata_flag; END IF;
1402 IF l_excl_area_ovr_flag IS NULL THEN l_excl_area_ovr_flag := l_excl_area_flag; END IF;
1403
1404
1405 --Fix for bug#9579092
1406 IF l_include_flag = 'Y' THEN
1407
1408 l_area_total_tbl(0).total_assignable_area := l_area_total_tbl(0).total_assignable_area +
1409 nvl(location_rec.assignable_area,0);
1410 l_area_total_tbl(0).total_occupied_area := l_area_total_tbl(0).total_occupied_area + nvl(l_occup_area,0);
1411 l_area_total_tbl(0).total_occupied_area_ovr := l_area_total_tbl(0).total_occupied_area_ovr +
1412 nvl(nvl(l_occup_area_ovr, l_occup_area),0);
1413 l_area_total_tbl(0).total_weighted_avg := l_area_total_tbl(0).total_weighted_avg + nvl(l_weighted_avg, 0);
1414 l_area_total_tbl(0).total_weighted_avg_ovr := l_area_total_tbl(0).total_weighted_avg_ovr +
1415 nvl(nvl(l_weighted_avg_ovr, l_weighted_avg),0);
1416
1417
1418 IF nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'N' THEN
1419
1420 l_info := ' figuring totals ';
1421 pnp_debug_pkg.log(l_info);
1422
1423
1424 l_area_total_tbl(0).total_vacant_area := l_area_total_tbl(0).total_vacant_area +
1425 nvl(location_rec.assignable_area,0) - nvl(l_occup_area,0);
1426 l_area_total_tbl(0).total_vacant_area_ovr := l_area_total_tbl(0).total_vacant_area_ovr +
1427 nvl(location_rec.assignable_area - l_occup_area_ovr,
1428 (nvl(location_rec.assignable_area,0) - nvl(l_occup_area,0)));
1429
1430 ELSIF nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'Y' THEN
1431
1432 l_info := ' figuring excluded totals ';
1433 pnp_debug_pkg.log(l_info);
1434
1435 l_area_total_tbl(0).total_occupied_area_exc := l_area_total_tbl(0).total_occupied_area_exc +
1436 nvl(nvl(l_occup_area_ovr, l_occup_area),0);
1437 l_area_total_tbl(0).total_vacant_area_exc := l_area_total_tbl(0).total_vacant_area_exc +
1438 nvl(location_rec.assignable_area - l_occup_area_ovr,
1439 (nvl(location_rec.assignable_area,0) - nvl(l_occup_area,0)));
1440 l_area_total_tbl(0).total_weighted_avg_exc := l_area_total_tbl(0).total_weighted_avg_exc +
1441 nvl(nvl(l_weighted_avg_ovr, l_weighted_avg),0);
1442
1443 END IF;
1444
1445 END IF;
1446
1447
1448 l_info := ' processing area cls detail information into pl/sql table ';
1449 pnp_debug_pkg.log(l_info);
1450
1451 l_area_cls_ln_data_tbl(l_data_tbl_counter).area_class_dtl_line_id := l_area_class_dtl_line_id;
1452 l_area_cls_ln_data_tbl(l_data_tbl_counter).from_date := l_from_date;
1453 l_area_cls_ln_data_tbl(l_data_tbl_counter).to_date := l_to_date;
1454 l_area_cls_ln_data_tbl(l_data_tbl_counter).location_id := location_rec.location_id;
1455 l_area_cls_ln_data_tbl(l_data_tbl_counter).property_id := location_rec.property_id;
1456 l_area_cls_ln_data_tbl(l_data_tbl_counter).cust_space_assign_id := space_assign_rec.cust_space_assign_id;
1457 l_area_cls_ln_data_tbl(l_data_tbl_counter).cust_account_id := space_assign_rec.cust_account_id;
1458 l_area_cls_ln_data_tbl(l_data_tbl_counter).lease_id := space_assign_rec.lease_id;
1459 l_area_cls_ln_data_tbl(l_data_tbl_counter).assignable_area := location_rec.assignable_area;
1460 l_area_cls_ln_data_tbl(l_data_tbl_counter).assigned_area := space_assign_rec.allocated_area;
1461 l_area_cls_ln_data_tbl(l_data_tbl_counter).assigned_area_ovr := l_assigned_area_ovr;
1462 l_area_cls_ln_data_tbl(l_data_tbl_counter).occupancy_pct := l_occup_pct;
1463 l_area_cls_ln_data_tbl(l_data_tbl_counter).occupied_area := l_occup_area;
1464 l_area_cls_ln_data_tbl(l_data_tbl_counter).occupied_area_ovr := l_occup_area_ovr;
1465 l_area_cls_ln_data_tbl(l_data_tbl_counter).vacant_area := location_rec.assignable_area - l_occup_area;
1466 l_area_cls_ln_data_tbl(l_data_tbl_counter).vacant_area_ovr := location_rec.assignable_area - l_occup_area_ovr;
1467 l_area_cls_ln_data_tbl(l_data_tbl_counter).weighted_avg := l_weighted_avg;
1468 l_area_cls_ln_data_tbl(l_data_tbl_counter).weighted_avg_ovr := l_weighted_avg_ovr;
1469 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_area_flag := l_excl_area_flag;
1470 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_area_ovr_flag := l_excl_area_ovr_flag;
1471 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_prorata_flag := l_excl_prorata_flag;
1472 l_area_cls_ln_data_tbl(l_data_tbl_counter).exclude_prorata_ovr_flag := l_excl_prorata_ovr_flag;
1473 l_area_cls_ln_data_tbl(l_data_tbl_counter).include_flag := l_include_flag;
1474 l_area_cls_ln_data_tbl(l_data_tbl_counter).recovery_space_std_code := space_assign_rec.recovery_space_std_code;
1475 l_area_cls_ln_data_tbl(l_data_tbl_counter).recovery_type_code := space_assign_rec.recovery_type_code;
1476
1477 -- collect data
1478
1479 l_info := ' processing vacancy for cust assignment:'||space_assign_rec.cust_space_assign_id||' ';
1480 pnp_debug_pkg.log(l_info);
1481
1482 process_vacancy(
1483 p_start_date => l_from_date,
1484 p_end_date => l_to_date,
1485 p_area => space_assign_rec.allocated_area,
1486 p_date_table => l_vacancy_date_table,
1487 p_number_table => l_vacancy_num_table,
1488 p_add => FALSE);
1489
1490 END LOOP;
1491
1492 l_info := ' processing vacancy for location for data table';
1493 pnp_debug_pkg.log(l_info);
1494
1495 process_vacancy(
1496 p_start_date => location_rec.active_start_date,
1497 p_end_date => location_rec.active_end_date,
1498 p_area => NVL(location_rec.assignable_area,0),
1499 p_date_table => l_vacancy_date_table,
1500 p_number_table => l_vacancy_num_table,
1501 p_add => TRUE);
1502
1503 l_info := ' processing vacancy for location for reference table';
1504 pnp_debug_pkg.log(l_info);
1505
1506 process_vacancy(
1507 p_start_date => location_rec.active_start_date,
1508 p_end_date => location_rec.active_end_date,
1509 p_area => NVL(location_rec.assignable_area,0),
1510 p_date_table => l_ref_vacancy_date_table,
1511 p_number_table => l_ref_vacancy_num_table,
1512 p_add => TRUE);
1513
1514 END IF;
1515
1516 END LOOP;
1517
1518 l_info := ' inserting vacancy data for last location id';
1519 pnp_debug_pkg.log(l_info);
1520
1521 insert_vacancy_data(
1522 p_location_id => l_temp_loc_id,
1523 p_property_id => l_temp_prop_id,
1524 p_date_table => l_vacancy_date_table,
1525 p_num_table => l_vacancy_num_table,
1526 p_ref_date_table => l_ref_vacancy_date_table,
1527 p_ref_num_table => l_ref_vacancy_num_table,
1528 p_from_date => fnd_date.canonical_to_date(p_from_date),
1529 p_to_date => fnd_date.canonical_to_date(p_to_date),
1530 p_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1531 p_assignable_area => l_temp_assignable_area,
1532 p_curnt_ovr => l_area_cls_ln_curnt_ovr,
1533 p_prior_ovr => l_area_cls_ln_prior_ovr,
1534 p_data_tbl => l_area_cls_ln_data_tbl,
1535 p_total_tbl => l_area_total_tbl,
1536 p_keep_override => p_keep_override,
1537 p_regenerate => l_regenerate
1538 );
1539
1540 l_temp_loc_id := null;
1541 l_temp_prop_id := null;
1542 l_vacancy_date_table.delete;
1543 l_vacancy_num_table.delete;
1544 l_ref_vacancy_date_table.delete;
1545 l_ref_vacancy_num_table.delete;
1546
1547 l_info := ' processing header data ';
1548 pnp_debug_pkg.log(l_info);
1549
1550 IF l_area_class_dtl_id IS NOT NULL THEN
1551
1552 pn_rec_arcl_dtl_pkg.update_row(
1553 x_area_class_id => p_area_class_id,
1554 x_area_class_dtl_id => l_area_class_dtl_id,
1555 x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1556 x_from_date => fnd_date.canonical_to_date(p_from_date),
1557 x_to_date => fnd_date.canonical_to_date(p_to_date),
1558 x_status => 'OPEN',
1559 x_ttl_assignable_area => l_area_total_tbl(0).total_assignable_area,
1560 x_ttl_occupied_area => l_area_total_tbl(0).total_occupied_area,
1561 x_ttl_occupied_area_ovr => l_area_total_tbl(0).total_occupied_area_ovr,
1562 x_ttl_occupied_area_exc => l_area_total_tbl(0).total_occupied_area_exc,
1563 x_ttl_vacant_area => l_area_total_tbl(0).total_vacant_area,
1564 x_ttl_vacant_area_ovr => l_area_total_tbl(0).total_vacant_area_ovr,
1565 x_ttl_vacant_area_exc => l_area_total_tbl(0).total_vacant_area_exc,
1566 x_ttl_weighted_avg => l_area_total_tbl(0).total_weighted_avg,
1567 x_ttl_weighted_avg_ovr => l_area_total_tbl(0).total_weighted_avg_ovr,
1568 x_ttl_weighted_avg_exc => l_area_total_tbl(0).total_weighted_avg_exc,
1569 x_last_update_date => SYSDATE,
1570 x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
1571 x_creation_date => SYSDATE,
1572 x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
1573 x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
1574 );
1575 ELSE
1576 pn_rec_arcl_dtl_pkg.insert_row(
1577 x_org_id => pn_mo_cache_utils.get_current_org_id,
1578 x_area_class_id => p_area_class_id,
1579 x_area_class_dtl_id => l_area_class_dtl_id,
1580 x_as_of_date => fnd_date.canonical_to_date(p_as_of_date),
1581 x_from_date => fnd_date.canonical_to_date(p_from_date),
1582 x_to_date => fnd_date.canonical_to_date(p_to_date),
1583 x_status => 'OPEN',
1584 x_ttl_assignable_area => l_area_total_tbl(0).total_assignable_area,
1585 x_ttl_occupied_area => l_area_total_tbl(0).total_occupied_area,
1586 x_ttl_occupied_area_ovr => l_area_total_tbl(0).total_occupied_area_ovr,
1587 x_ttl_occupied_area_exc => l_area_total_tbl(0).total_occupied_area_exc,
1588 x_ttl_vacant_area => l_area_total_tbl(0).total_vacant_area,
1589 x_ttl_vacant_area_ovr => l_area_total_tbl(0).total_vacant_area_ovr,
1590 x_ttl_vacant_area_exc => l_area_total_tbl(0).total_vacant_area_exc,
1591 x_ttl_weighted_avg => l_area_total_tbl(0).total_weighted_avg,
1592 x_ttl_weighted_avg_ovr => l_area_total_tbl(0).total_weighted_avg_ovr,
1593 x_ttl_weighted_avg_exc => l_area_total_tbl(0).total_weighted_avg_exc,
1594 x_last_update_date => SYSDATE,
1595 x_last_updated_by => nvl(fnd_profile.value('USER_ID'), -1),
1596 x_creation_date => SYSDATE,
1597 x_created_by => nvl(fnd_profile.value('USER_ID'), -1),
1598 x_last_update_login => nvl(fnd_profile.value('USER_ID'), -1)
1599 );
1600 END IF;
1601
1602 l_info := ' dumping data from pl/sql table ';
1603 pnp_debug_pkg.log(l_info);
1604
1605 process_area_class_line_data(
1606 p_old_data => l_area_cls_ln_curnt_ovr,
1607 p_new_data => l_area_cls_ln_data_tbl,
1608 p_hdr_id => l_area_class_dtl_id
1609 );
1610
1611 pnp_debug_pkg.log(l_desc ||' (-)');
1612
1613 EXCEPTION
1614 WHEN uom_exception THEN
1615 pnp_debug_pkg.log(fnd_message.get);
1616 raise;
1617 WHEN OTHERS THEN
1618 fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
1619 pnp_debug_pkg.put_log_msg(fnd_message.get);
1620 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1621 raise;
1622 END extract_area;
1623
1624 ---------------------------- EXPENSE VALIDATIONS -----------------------------+
1625
1626 ------------------------------------------------------------------------------+
1627 -- FUNCTION : check_dates
1628 -- DESCRIPTION: checks that from date and to date does not overlap for a given
1629 -- location / prop id extraction.
1630 -- NOTE : from_date < as_of_date < to_date is NOT checked.
1631 -- HISTORY:
1632 -- 19-MAR-03 ftanudja o created
1633 -- 02-JUL-03 ftanudja o added constraint p_extr code <> null
1634 ------------------------------------------------------------------------------+
1635
1636 FUNCTION check_dates(
1637 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
1638 p_from_date pn_rec_exp_line.from_date%TYPE,
1639 p_to_date pn_rec_exp_line.to_date%TYPE,
1640 p_property_id pn_rec_exp_line.property_id%TYPE,
1641 p_location_id pn_rec_exp_line.location_id%TYPE,
1642 p_extract_code pn_rec_exp_line.expense_extract_code%TYPE
1643 ) RETURN BOOLEAN
1644 IS
1645 l_result BOOLEAN := TRUE;
1646
1647 CURSOR line_check_crossing_dates IS
1648 SELECT 'TRUE' FROM pn_rec_exp_line
1649 WHERE ((p_from_date BETWEEN from_date AND to_date) OR
1650 (p_to_date BETWEEN from_date AND to_date))
1651 AND (((location_id = p_location_id) OR (location_id IS NULL AND p_location_id IS NULL)) OR
1652 (((property_id = p_property_id) OR (property_id IS NULL AND p_property_id IS NULL))
1653 AND location_id IS NULL))
1654 AND p_extract_code IS NOT NULL
1655 AND expense_extract_code <> p_extract_code
1656 AND rownum < 2;
1657
1658 BEGIN
1659 IF p_from_date > p_to_date THEN
1660 l_result:= FALSE;
1661 END IF;
1662
1663 FOR check_valid IN line_check_crossing_dates LOOP l_result := FALSE; END LOOP;
1664
1665 RETURN l_result;
1666 END;
1667
1668 ------------------------------------------------------------------------------+
1669 -- FUNCTION : check_loc_n_prop_id
1670 -- DESCRIPTION:
1671 -- 1. checks that location id is valid.
1672 -- 2. checks that property id is valid.
1673 -- 3. checks that location id / property id combination valid
1674 -- HISTORY:
1675 -- 19-MAR-03 ftanudja o created
1676 -- 06-OCT-07 bifernan o Modified cursor prop_loc_combo_check for bug 6461211
1677 ------------------------------------------------------------------------------+
1678
1679 FUNCTION check_loc_n_prop_id(
1680 p_location_id pn_locations.location_id%TYPE,
1681 p_property_id pn_locations.property_id%TYPE
1682 ) RETURN BOOLEAN
1683 IS
1684 l_result BOOLEAN:= FALSE;
1685
1686 CURSOR loc_check IS
1687 SELECT 'TRUE' FROM pn_locations_all WHERE location_id = p_location_id;
1688
1689 CURSOR prop_check IS
1690 SELECT 'TRUE' FROM pn_properties_all WHERE property_id = p_property_id;
1691
1692 /* Commented and modified for Bug 6461211
1693 CURSOR prop_loc_combo_check IS
1694 SELECT 'TRUE' FROM pn_locations_all WHERE location_id = p_location_id AND property_id = p_property_id; */
1695
1696 CURSOR prop_loc_combo_check IS
1697 SELECT 'TRUE' FROM pn_locations_all
1698 WHERE property_id = p_property_id
1699 START WITH location_id = p_location_id
1700 CONNECT BY PRIOR parent_location_id = location_id;
1701
1702 BEGIN
1703 IF p_location_id IS NOT NULL THEN
1704
1705 FOR loc_rec IN loc_check LOOP l_result := TRUE; exit; END LOOP;
1706
1707 IF p_property_id IS NOT NULL THEN
1708
1709 l_result:= FALSE;
1710 FOR loc_rec IN prop_loc_combo_check LOOP l_result := TRUE; exit;END LOOP;
1711
1712 END IF;
1713
1714 ELSIF p_property_id IS NOT NULL THEN
1715
1716 FOR prop_rec IN prop_check LOOP l_result := TRUE; exit; END LOOP;
1717
1718 END IF;
1719
1720 return l_result;
1721 END;
1722
1723 ------------------------------------------------------------------------------+
1724 -- FUNCTION : check_expense_type
1725 -- DESCRIPTION: checks that expense_type is valid
1726 -- HISTORY:
1727 -- 19-MAR-03 ftanudja o created
1728 ------------------------------------------------------------------------------+
1729
1730 FUNCTION check_expense_type(p_exp_type_code pn_rec_exp_itf.expense_type_code%TYPE)
1731 RETURN BOOLEAN
1732 IS
1733 CURSOR type_check IS
1734 SELECT 'TRUE' FROM fnd_lookups
1735 WHERE lookup_type = 'PN_PAYMENT_PURPOSE_TYPE' and lookup_code = p_exp_type_code;
1736
1737 l_result BOOLEAN := FALSE;
1738 BEGIN
1739 FOR type_rec IN type_check LOOP l_result := TRUE; exit; END LOOP;
1740 return l_result;
1741 END;
1742
1743 ------------------------------------------------------------------------------+
1744 -- FUNCTION : check_extract_code
1745 -- DESCRIPTION: checks extract_code from user
1746 -- HISTORY:
1747 -- 19-MAR-03 ftanudja o created
1748 ------------------------------------------------------------------------------+
1749
1750 FUNCTION check_extract_code(
1751 p_extract_code pn_rec_exp_line.expense_extract_code%TYPE,
1752 p_loc_id pn_rec_exp_line.location_id%TYPE,
1753 p_prop_id pn_rec_exp_line.property_id%TYPE,
1754 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
1755 p_from_date pn_rec_exp_line.from_date%TYPE,
1756 p_to_date pn_rec_exp_line.to_date%TYPE,
1757 p_currency pn_rec_exp_line.currency_code%TYPE)
1758 RETURN BOOLEAN
1759 IS
1760 CURSOR check_exists IS
1761 SELECT location_id,
1762 property_id,
1763 from_date,
1764 to_date,
1765 as_of_date,
1766 currency_code,
1767 org_id
1768 FROM pn_rec_exp_line_all
1769 WHERE expense_extract_code = p_extract_code;
1770
1771 l_exist BOOLEAN := FALSE;
1772 l_same BOOLEAN := FALSE;
1773 BEGIN
1774
1775 IF p_extract_code IS NOT NULL THEN
1776 FOR extract_rec IN check_exists LOOP
1777 l_exist := TRUE;
1778 IF ((extract_rec.location_id = p_loc_id) OR
1779 (extract_rec.location_id IS NULL AND p_loc_id IS NULL)) AND
1780 ((extract_rec.property_id = p_prop_id) OR
1781 (extract_rec.property_id IS NULL AND p_prop_id IS NULL)) AND
1782 TRUNC(extract_rec.from_date)= TRUNC(p_from_date) AND
1783 TRUNC(extract_rec.to_date) = TRUNC(p_to_date) AND
1784 extract_rec.currency_code = p_currency AND
1785 extract_rec.org_id = pn_mo_cache_utils.get_current_org_id THEN
1786 l_same := TRUE;
1787 END IF;
1788 END LOOP;
1789
1790 IF l_exist AND NOT l_same THEN
1791 RETURN FALSE;
1792 ELSE
1793 RETURN TRUE;
1794 END IF;
1795 ELSE
1796 RETURN FALSE;
1797 END IF;
1798 END;
1799
1800 ------------------------------------------------------------------------------+
1801 -- FUNCTION : check_account_id
1802 -- DESCRIPTION: checks whetever a given cc_id is valid
1803 -- HISTORY:
1804 -- 19-MAR-03 ftanudja o created
1805 ------------------------------------------------------------------------------+
1806
1807 FUNCTION check_account_id(p_cc_id pn_rec_exp_itf.expense_account_id%TYPE)
1808 RETURN BOOLEAN
1809 IS
1810 l_result BOOLEAN := FALSE;
1811 CURSOR ccid_check IS
1812 SELECT 'TRUE' FROM gl_code_combinations where code_combination_id = p_cc_id;
1813
1814 BEGIN
1815 FOR acct_rec IN ccid_check LOOP l_result:= TRUE; exit; END LOOP;
1816 RETURN l_result;
1817 END;
1818
1819 ------------------------------------------------------------------------------+
1820 -- FUNCTION : check_ccid_n_type
1821 -- DESCRIPTION:
1822 -- 1. checks for expense type and account id combination.
1823 -- 2. a given combination must exist only once given a certain from and to date
1824 --
1825 -- HISTORY:
1826 -- 19-MAR-03 ftanudja o created
1827 ------------------------------------------------------------------------------+
1828
1829 FUNCTION check_ccid_n_type(
1830 p_exp_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE,
1831 p_cc_id pn_rec_exp_line_dtl.expense_account_id%TYPE,
1832 p_from_date pn_rec_exp_line.from_date%TYPE,
1833 p_to_date pn_rec_exp_line.to_date%TYPE
1834 ) RETURN BOOLEAN
1835 IS
1836 l_result BOOLEAN := TRUE;
1837 CURSOR check_ccid_type IS
1838 SELECT 'EXISTS'
1839 FROM pn_rec_exp_line_dtl dtl,
1840 pn_rec_exp_line hdr
1841 WHERE dtl.expense_line_id = hdr.expense_line_id
1842 AND hdr.from_date = p_from_date
1843 AND hdr.to_date = p_to_date
1844 AND dtl.expense_type_code = p_exp_type_code
1845 AND dtl.expense_account_id = p_cc_id;
1846
1847
1848 BEGIN
1849 FOR acct_type_rec IN check_ccid_type LOOP l_result:= FALSE; exit; END LOOP;
1850 RETURN l_result;
1851 END;
1852
1853 ----------------------------- EXPENSE LINE -----------------------------------+
1854
1855 ------------------------------------------------------------------------------+
1856 -- PROCEDURE : validate_and_process_lines
1857 -- DESCRIPTION:
1858 -- 1. Given : some expense line data.
1859 -- 2. Check validity of each line and put them into expense lines table.
1860 -- 3. Return status flag.
1861 --
1862 -- HISTORY:
1863 -- 19-MAR-03 ftanudja o created
1864 -- 02-JUL-03 ftanudja o made p_extract_code to IN OUT for auto num gen feat.
1865 -- 15-AUG-03 ftanudja o added flexfield attributes for expclndtl. 3099278.
1866 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
1867 ------------------------------------------------------------------------------+
1868
1869 PROCEDURE validate_and_process_lines(
1870 p_transfer_flag OUT NOCOPY pn_rec_exp_itf.transfer_flag%TYPE,
1871 p_expense_line_dtl_id OUT NOCOPY pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE,
1872 p_expense_line_id IN OUT NOCOPY pn_rec_exp_line_dtl.expense_line_id%TYPE,
1873 p_is_header_set IN OUT NOCOPY BOOLEAN,
1874 p_expense_type_code pn_rec_exp_itf.expense_type_code%TYPE,
1875 p_expense_account_id pn_rec_exp_itf.expense_account_id%TYPE,
1876 p_account_description pn_rec_exp_itf.account_description%TYPE,
1877 p_actual_amount pn_rec_exp_itf.actual_amount%TYPE,
1878 p_budgeted_amount pn_rec_exp_itf.budgeted_amount%TYPE,
1879 p_currency_code pn_rec_exp_itf.currency_code%TYPE,
1880 p_location_id pn_rec_exp_itf.location_id%TYPE,
1881 p_property_id pn_rec_exp_itf.property_id%TYPE,
1882 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
1883 p_from_date pn_rec_exp_line.from_date%TYPE,
1884 p_to_date pn_rec_exp_line.to_date%TYPE,
1885 p_extract_code IN OUT NOCOPY pn_rec_exp_line.expense_extract_code%TYPE,
1886 p_keep_override VARCHAR2,
1887 p_reextract BOOLEAN,
1888 p_attribute_category pn_rec_exp_itf.attribute_category%TYPE,
1889 p_attribute1 pn_rec_exp_itf.attribute1%TYPE,
1890 p_attribute2 pn_rec_exp_itf.attribute2%TYPE,
1891 p_attribute3 pn_rec_exp_itf.attribute3%TYPE,
1892 p_attribute4 pn_rec_exp_itf.attribute4%TYPE,
1893 p_attribute5 pn_rec_exp_itf.attribute5%TYPE,
1894 p_attribute6 pn_rec_exp_itf.attribute6%TYPE,
1895 p_attribute7 pn_rec_exp_itf.attribute7%TYPE,
1896 p_attribute8 pn_rec_exp_itf.attribute8%TYPE,
1897 p_attribute9 pn_rec_exp_itf.attribute9%TYPE,
1898 p_attribute10 pn_rec_exp_itf.attribute10%TYPE,
1899 p_attribute11 pn_rec_exp_itf.attribute11%TYPE,
1900 p_attribute12 pn_rec_exp_itf.attribute12%TYPE,
1901 p_attribute13 pn_rec_exp_itf.attribute13%TYPE,
1902 p_attribute14 pn_rec_exp_itf.attribute14%TYPE,
1903 p_attribute15 pn_rec_exp_itf.attribute15%TYPE)
1904 IS
1905 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.validate_and_process_lines' ;
1906 l_info VARCHAR2(300);
1907 l_is_valid BOOLEAN;
1908
1909 BEGIN
1910
1911 pnp_debug_pkg.log(l_desc ||' (+)');
1912
1913 l_is_valid := check_loc_n_prop_id (p_location_id, p_property_id) AND
1914 check_expense_type (p_expense_type_code) AND
1915 check_account_id (p_expense_account_id);
1916
1917 IF p_reextract THEN
1918 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);
1919 END IF;
1920
1921 IF l_is_valid THEN
1922
1923 IF NOT p_is_header_set AND NOT p_reextract THEN
1924
1925 p_is_header_set := TRUE;
1926
1927 l_info:= ' inserting expense line header';
1928 pnp_debug_pkg.log(l_info);
1929
1930 pn_rec_exp_line_pkg.insert_row(
1931 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
1932 x_expense_line_id => p_expense_line_id,
1933 x_expense_extract_code => p_extract_code,
1934 x_currency_code => p_currency_code,
1935 x_as_of_date => p_as_of_date,
1936 x_from_date => p_from_date,
1937 x_to_date => p_to_date,
1938 x_location_id => p_location_id,
1939 x_property_id => p_property_id,
1940 x_last_update_date => SYSDATE,
1941 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
1942 x_creation_date => SYSDATE,
1943 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
1944 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
1945 );
1946
1947 END IF;
1948
1949 l_info:= ' inserting expense line detail for header id: '||p_expense_line_id;
1950 pnp_debug_pkg.log(l_info);
1951
1952 pn_rec_exp_line_dtl_pkg.insert_row(
1953 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
1954 x_expense_line_id => p_expense_line_id,
1955 x_expense_line_dtl_id => p_expense_line_dtl_id,
1956 x_parent_expense_line_id => null,
1957 x_property_id => p_property_id,
1958 x_location_id => p_location_id,
1959 x_expense_type_code => p_expense_type_code,
1960 x_expense_account_id => p_expense_account_id,
1961 x_account_description => p_account_description,
1962 x_actual_amount => p_actual_amount,
1963 x_actual_amount_ovr => null,
1964 x_budgeted_amount => p_budgeted_amount,
1965 x_budgeted_amount_ovr => null,
1966 x_budgeted_pct => null,
1967 x_actual_pct => null,
1968 x_currency_code => p_currency_code,
1969 x_recoverable_flag => 'Y',
1970 x_expense_line_indicator => 'NEUTRAL',
1971 x_last_update_date => SYSDATE,
1972 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
1973 x_creation_date => SYSDATE,
1974 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
1975 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1),
1976 x_attribute_category => p_attribute_category,
1977 x_attribute1 => p_attribute1,
1978 x_attribute2 => p_attribute2,
1979 x_attribute3 => p_attribute3,
1980 x_attribute4 => p_attribute4,
1981 x_attribute5 => p_attribute5,
1982 x_attribute6 => p_attribute6,
1983 x_attribute7 => p_attribute7,
1984 x_attribute8 => p_attribute8,
1985 x_attribute9 => p_attribute9,
1986 x_attribute10 => p_attribute10,
1987 x_attribute11 => p_attribute11,
1988 x_attribute12 => p_attribute12,
1989 x_attribute13 => p_attribute13,
1990 x_attribute14 => p_attribute14,
1991 x_attribute15 => p_attribute15
1992 );
1993
1994 p_transfer_flag := 'Y';
1995
1996 ELSE
1997
1998 p_transfer_flag := 'E';
1999
2000 END IF;
2001
2002 pnp_debug_pkg.log(l_desc ||' (-)');
2003
2004 EXCEPTION
2005 WHEN OTHERS THEN
2006 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2007 raise;
2008 END;
2009
2010 ------------------------------------------------------------------------------+
2011 -- PROCEDURE : extract_expense_lines
2012 -- DESCRIPTION:
2013 -- 1. Get all lines from interface table.
2014 -- 2. Find out whether it is a re-extract or not.
2015 -- 3. Process and validate data
2016 -- 4. Update ITF table transfer flag to 'Y' if transferred, 'E' if erroneous.
2017 -- HISTORY:
2018 -- 19-MAR-03 ftanudja o created
2019 -- 02-JUL-03 ftanudja o added constraint p_extr code <> null in is_reextract.
2020 -- o made p_extract_code to IN OUT for auto num gen feat.
2021 -- 03-JUL-03 ftanudja o fixed cursor to handle cases when only prop id given.
2022 -- 10-JUL-03 ftanudja o made prop id and loc id mutex on get_itf_lines_info.
2023 -- 15-AUG-03 ftanudja o added flexfield attributes for expclndtl. 3099278.
2024 -- 21-MAY-03 ftanudja o added counters to summarize totals. 3591556.
2025 -- o added log output message to show input parameters.
2026 -- o added logic for batch commit.
2027 -- o added org_id filter.
2028 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
2029 ------------------------------------------------------------------------------+
2030
2031 PROCEDURE extract_expense_lines(
2032 p_location_id pn_rec_exp_line.location_id%TYPE,
2033 p_property_id pn_rec_exp_line.property_id%TYPE,
2034 p_as_of_date pn_rec_exp_line.as_of_date%TYPE,
2035 p_from_date pn_rec_exp_line.from_date%TYPE,
2036 p_to_date pn_rec_exp_line.to_date%TYPE,
2037 p_currency_code pn_rec_exp_line.currency_code%TYPE,
2038 p_extract_code IN OUT NOCOPY pn_rec_exp_line.expense_extract_code%TYPE,
2039 p_keep_override VARCHAR2)
2040 IS
2041 CURSOR get_itf_lines_info IS
2042 SELECT expense_type_code,
2043 expense_account_id,
2044 account_description,
2045 actual_amount,
2046 budgeted_amount,
2047 currency_code,
2048 location_id,
2049 property_id,
2050 attribute_category,
2051 attribute1,
2052 attribute2,
2053 attribute3,
2054 attribute4,
2055 attribute5,
2056 attribute6,
2057 attribute7,
2058 attribute8,
2059 attribute9,
2060 attribute10,
2061 attribute11,
2062 attribute12,
2063 attribute13,
2064 attribute14,
2065 attribute15
2066 FROM pn_rec_exp_itf
2067 WHERE transfer_flag = 'N'
2068 AND from_date = p_from_date
2069 AND to_date = p_to_date
2070 AND currency_code = p_currency_code
2071 AND (location_id IN
2072 (SELECT location_id FROM pn_locations_all
2073 WHERE active_start_date < p_to_date
2074 AND active_end_date > p_from_date
2075 START WITH (location_id = p_location_id OR
2076 (property_id = p_property_id AND p_location_id IS NULL))
2077 CONNECT BY PRIOR location_id = parent_location_id)
2078 OR
2079 (property_id = p_property_id AND p_location_id IS NULL))
2080 AND org_id = pn_mo_cache_utils.get_current_org_id
2081 FOR UPDATE OF transfer_flag, expense_line_dtl_id NOWAIT;
2082
2083 CURSOR is_reextract IS
2084 SELECT expense_line_id
2085 FROM pn_rec_exp_line_all hdr
2086 WHERE hdr.expense_extract_code = p_extract_code
2087 AND p_extract_code IS NOT NULL
2088 AND rownum < 2;
2089
2090 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_expense_lines' ;
2091 l_info VARCHAR2(300);
2092 l_is_header_set BOOLEAN;
2093 l_reextract BOOLEAN;
2094 l_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE;
2095 l_expense_line_id pn_rec_exp_line_dtl.expense_line_id%TYPE;
2096 l_transfer_flag pn_rec_exp_itf.transfer_flag%TYPE;
2097 l_total NUMBER := 0;
2098 l_failed NUMBER := 0;
2099
2100 BEGIN
2101
2102 pnp_debug_pkg.log(l_desc ||' (+)');
2103
2104 fnd_message.set_name('PN','PN_REC_EXP_LN_CP_INFO');
2105 fnd_message.set_token('LOC' , to_char(p_location_id));
2106 fnd_message.set_token('PROP' , to_char(p_property_id));
2107 fnd_message.set_token('STR' , to_char(p_from_date));
2108 fnd_message.set_token('END' , to_char(p_to_date));
2109 fnd_message.set_token('AOD' , to_char(p_as_of_date));
2110 fnd_message.set_token('CUR' , p_currency_code);
2111 fnd_message.set_token('EXPNUM', p_extract_code);
2112 fnd_message.set_token('OVR' , p_keep_override);
2113 pnp_debug_pkg.put_log_msg('');
2114 pnp_debug_pkg.put_log_msg(fnd_message.get);
2115 pnp_debug_pkg.put_log_msg('');
2116
2117 l_info := ' initializing values';
2118 pnp_debug_pkg.log(l_info);
2119
2120 l_expense_line_id := null;
2121 l_is_header_set := FALSE;
2122 l_reextract := FALSE;
2123
2124 FOR check_reextract IN is_reextract LOOP
2125 l_reextract := TRUE;
2126 l_expense_line_id := check_reextract.expense_line_id;
2127 END LOOP;
2128
2129 FOR lines_rec IN get_itf_lines_info LOOP
2130
2131 l_expense_line_dtl_id := null;
2132
2133 validate_and_process_lines(
2134 p_transfer_flag => l_transfer_flag,
2135 p_expense_line_dtl_id => l_expense_line_dtl_id,
2136 p_expense_line_id => l_expense_line_id,
2137 p_is_header_set => l_is_header_set,
2138 p_expense_type_code => lines_rec.expense_type_code,
2139 p_expense_account_id => lines_rec.expense_account_id,
2140 p_account_description => lines_rec.account_description,
2141 p_actual_amount => lines_rec.actual_amount,
2142 p_budgeted_amount => lines_rec.budgeted_amount,
2143 p_currency_code => lines_rec.currency_code,
2144 p_location_id => lines_rec.location_id,
2145 p_property_id => lines_rec.property_id,
2146 p_as_of_date => p_as_of_date,
2147 p_from_date => p_from_date,
2148 p_to_date => p_to_date,
2149 p_extract_code => p_extract_code,
2150 p_keep_override => p_keep_override,
2151 p_reextract => l_reextract,
2152 p_attribute_category => lines_rec.attribute_category,
2153 p_attribute1 => lines_rec.attribute1,
2154 p_attribute2 => lines_rec.attribute2,
2155 p_attribute3 => lines_rec.attribute3,
2156 p_attribute4 => lines_rec.attribute4,
2157 p_attribute5 => lines_rec.attribute5,
2158 p_attribute6 => lines_rec.attribute6,
2159 p_attribute7 => lines_rec.attribute7,
2160 p_attribute8 => lines_rec.attribute8,
2161 p_attribute9 => lines_rec.attribute9,
2162 p_attribute10 => lines_rec.attribute10,
2163 p_attribute11 => lines_rec.attribute11,
2164 p_attribute12 => lines_rec.attribute12,
2165 p_attribute13 => lines_rec.attribute13,
2166 p_attribute14 => lines_rec.attribute14,
2167 p_attribute15 => lines_rec.attribute15
2168 );
2169
2170 l_total := l_total + 1;
2171
2172 IF l_transfer_flag = 'E' THEN
2173 l_failed := l_failed + 1;
2174 END IF;
2175
2176 -- do a batch commit if needed
2177 IF mod(l_total, g_batch_commit_size) = 0 THEN
2178 commit;
2179 END IF;
2180
2181 l_info := ' updating interface table ';
2182 pnp_debug_pkg.log(l_info);
2183
2184 UPDATE pn_rec_exp_itf
2185 SET transfer_flag = l_transfer_flag,
2186 expense_line_dtl_id = l_expense_line_dtl_id
2187 WHERE CURRENT OF get_itf_lines_info;
2188
2189 END LOOP;
2190
2191 fnd_message.set_name('PN','PN_REC_EXP_LN');
2192 pnp_debug_pkg.put_log_msg('');
2193 pnp_debug_pkg.put_log_msg(fnd_message.get);
2194
2195 fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2196 fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2197 fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2198 fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2199 pnp_debug_pkg.put_log_msg(fnd_message.get);
2200 pnp_debug_pkg.put_log_msg('');
2201
2202 pnp_debug_pkg.log(l_desc ||' (-)');
2203
2204 EXCEPTION
2205 WHEN OTHERS THEN
2206 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2207 raise;
2208
2209 END extract_expense_lines;
2210
2211 --------------------------- EXPENSE CLASS LINE -------------------------------+
2212
2213 ------------------------------------------------------------------------------+
2214 -- PROCEDURE : process_exp_cls_dtl_mst_data
2215 --
2216 -- DESCRIPTION:
2217 -- 1. Given : data table p_master_data.
2218 -- 2. Determine whether a matching row can be found.
2219 -- 3. If found, update, otherwise insert new row.
2220 --
2221 -- NOTES:
2222 -- A. Use of override tables p_fee_use_table, p_share_use_table
2223 -- 1. The purpose is to keep track whether share pct and fee % bf contr
2224 -- should be used at this level.
2225 -- 2. It should only be used to calculate the computed recoverable amount
2226 -- if and only if there aren't any values defined at the account drilldown.
2227 -- B. Use of tables p_ovr_use_data and p_use_prior_ovr
2228 -- 1. p_ovr_use_data keeps track whether the current _ovr values are from
2229 -- a regeneration or from a prior extract.
2230 -- 2. it's possible to have aregenerate in which values from a prior extract
2231 -- are defaulted... since at run time it's not known which value should
2232 -- be used, this information needs to be kept to populate the correct _ovr
2233 -- values.
2234 -- 3. p_use_prior_ovr determines whether _ovr values should be used.
2235 --
2236 -- HISTORY:
2237 -- 19-MAR-03 ftanudja o created
2238 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2239 ------------------------------------------------------------------------------+
2240
2241 PROCEDURE process_exp_cls_dtl_mst_data(
2242 p_master_data IN OUT NOCOPY exp_cls_line_mst_tbl,
2243 p_ovr_use_data IN OUT NOCOPY exp_cls_line_use_tbl,
2244 p_fee_use_table IN OUT NOCOPY exp_cls_line_use_tbl,
2245 p_share_use_table IN OUT NOCOPY exp_cls_line_use_tbl,
2246 p_master_data_id OUT NOCOPY pn_rec_expcl_dtlln.expense_class_line_id%TYPE,
2247 p_expense_class_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE,
2248 p_expense_class_dtl_id pn_rec_expcl_dtlln.expense_class_dtl_id%TYPE,
2249 p_location_id pn_space_assign_cust.location_id%TYPE,
2250 p_cust_space_assign_id pn_space_assign_cust.cust_space_assign_id%TYPE,
2251 p_cust_account_id pn_space_assign_cust.cust_account_id%TYPE,
2252 p_lease_id pn_rec_expcl_dtlln.lease_id%TYPE,
2253 p_recovery_space_std_code pn_rec_expcl_dtlln.recovery_space_std_code%TYPE,
2254 p_recovery_type_code pn_rec_expcl_dtlln.recovery_type_code%TYPE,
2255 p_budget_amount pn_rec_expcl_dtlln.budgeted_amt%TYPE,
2256 p_expense_amount pn_rec_expcl_dtlln.expense_amt%TYPE,
2257 p_recoverable_amount pn_rec_expcl_dtlln.recoverable_amt%TYPE,
2258 p_cpt_recoverable_amount pn_rec_expcl_dtlln.computed_recoverable_amt%TYPE,
2259 p_cls_line_share_pct pn_rec_expcl_dtlln.cls_line_share_pct%TYPE,
2260 p_cls_line_fee_af_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_after_contr_ovr%TYPE,
2261 p_cls_line_fee_bf_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_before_contr_ovr%TYPE,
2262 p_use_fee_bf_contr pn_rec_expcl_inc.cls_incl_fee_before_contr%TYPE,
2263 p_use_share_pct pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE,
2264 p_use_prior_ovr BOOLEAN
2265 )
2266 IS
2267 l_info VARCHAR2(300);
2268 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_cls_dtl_mst_data' ;
2269 temp_rec pn_rec_expcl_dtlln%ROWTYPE;
2270
2271 BEGIN
2272
2273 pnp_debug_pkg.log(l_desc ||' (+)');
2274
2275 p_master_data_id := -1;
2276
2277 l_info:= ' searching through master table';
2278 pnp_debug_pkg.log(l_info);
2279
2280 FOR i IN 0 .. p_master_data.COUNT - 1 LOOP
2281
2282 IF (p_master_data(i).location_id = p_location_id) AND
2283 (p_master_data(i).cust_account_id = p_cust_account_id) THEN
2284
2285 l_info:= ' updating values in master data object for location id '||p_location_id||' and customer acct id'|| p_cust_account_id;
2286 pnp_debug_pkg.log(l_info);
2287
2288 p_master_data(i).budgeted_amt := nvl(p_master_data(i).budgeted_amt, 0) + nvl(p_budget_amount,0);
2289 p_master_data(i).expense_amt := nvl(p_master_data(i).expense_amt, 0) + nvl(p_expense_amount,0);
2290 p_master_data(i).recoverable_amt := nvl(p_master_data(i).recoverable_amt, 0) + nvl(p_recoverable_amount,0);
2291 p_master_data(i).computed_recoverable_amt := nvl(p_master_data(i).computed_recoverable_amt, 0) + nvl(p_cpt_recoverable_amount,0);
2292 p_master_data_id := i;
2293
2294 p_fee_use_table(i) := p_fee_use_table(i) AND (p_use_fee_bf_contr IS NULL);
2295 p_share_use_table(i) := p_share_use_table(i) AND (p_use_share_pct IS NULL);
2296
2297 IF p_ovr_use_data(i) AND NOT p_use_prior_ovr THEN
2298
2299 p_master_data(i).cls_line_share_pct := p_cls_line_share_pct;
2300 p_master_data(i).cls_line_fee_before_contr_ovr:= p_cls_line_fee_bf_contr_ovr;
2301 p_master_data(i).cls_line_fee_after_contr_ovr := p_cls_line_fee_af_contr_ovr;
2302
2303 END IF;
2304
2305 exit;
2306
2307 END IF;
2308 END LOOP;
2309
2310 IF (p_master_data_id = -1) THEN
2311
2312 p_master_data_id := p_master_data.COUNT;
2313
2314 l_info:= ' creating new entry in master data object';
2315 pnp_debug_pkg.log(l_info);
2316
2317 temp_rec.expense_class_dtl_id := p_expense_class_dtl_id;
2318 temp_rec.expense_class_line_id := p_expense_class_line_id;
2319 temp_rec.location_id := p_location_id;
2320 temp_rec.cust_space_assign_id := p_cust_space_assign_id;
2321 temp_rec.cust_account_id := p_cust_account_id;
2322 temp_rec.lease_id := p_lease_id;
2323 temp_rec.recovery_space_std_code := p_recovery_space_std_code;
2324 temp_rec.recovery_type_code := p_recovery_type_code;
2325 temp_rec.cls_line_share_pct := p_cls_line_share_pct;
2326 temp_rec.cls_line_fee_before_contr_ovr:= p_cls_line_fee_bf_contr_ovr;
2327 temp_rec.cls_line_fee_after_contr_ovr := p_cls_line_fee_af_contr_ovr;
2328 temp_rec.expense_amt := p_expense_amount;
2329 temp_rec.budgeted_amt := p_budget_amount;
2330 temp_rec.recoverable_amt := p_recoverable_amount;
2331 temp_rec.computed_recoverable_amt := p_cpt_recoverable_amount;
2332 p_master_data(p_master_data_id) := temp_rec;
2333 p_fee_use_table(p_master_data_id) := (p_use_fee_bf_contr IS NULL);
2334 p_share_use_table(p_master_data_id) := (p_use_share_pct IS NULL);
2335 p_ovr_use_data(p_master_data_id) := p_use_prior_ovr;
2336
2337 END IF;
2338
2339 pnp_debug_pkg.log(l_desc ||' (-)');
2340
2341 EXCEPTION
2342 WHEN OTHERS THEN
2343 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2344 raise;
2345 END;
2346
2347 ------------------------------------------------------------------------------+
2348 -- PROCEDURE : process_exp_cls_dtl_dtl_data
2349 -- DESCRIPTION: dumps data in plsql table, given parameters
2350 -- 19-MAR-03 ftanudja o created
2351 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2352 ------------------------------------------------------------------------------+
2353
2354 PROCEDURE process_exp_cls_dtl_dtl_data(
2355 p_detail_data IN OUT NOCOPY exp_cls_line_dtl_tbl,
2356 p_master_data_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE,
2357 p_expense_class_line_dtl_id pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE,
2358 p_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE,
2359 p_expense_account_id pn_rec_exp_line_dtl.expense_account_id%TYPE,
2360 p_expense_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE,
2361 p_expense_amount pn_rec_expcl_dtlln.expense_amt%TYPE,
2362 p_budget_amount pn_rec_expcl_dtlln.budgeted_amt%TYPE,
2363 p_recoverable_amount pn_rec_expcl_dtlln.recoverable_amt%TYPE,
2364 p_cpt_recoverable_amount pn_rec_expcl_dtlln.computed_recoverable_amt%TYPE,
2365 p_cls_line_shr_pct pn_rec_expcl_inc.cls_incl_share_pct%TYPE,
2366 p_cls_line_fee_bf_contr pn_rec_expcl_inc.cls_incl_fee_before_contr%TYPE,
2367 p_cls_line_shr_pct_ovr pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE,
2368 p_cls_line_fee_bf_contr_ovr pn_rec_expcl_dtlacc.cls_line_dtl_fee_bf_contr%TYPE
2369 )
2370 IS
2371
2372 l_info VARCHAR2(300);
2373 l_id NUMBER;
2374 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_cls_dtl_dtl_data' ;
2375
2376 BEGIN
2377
2378 pnp_debug_pkg.log(l_desc ||' (+)');
2379
2380 l_info:= ' inserting into details pl/sql table ';
2381 pnp_debug_pkg.log(l_info);
2382
2383 l_id := p_detail_data.COUNT;
2384
2385 p_detail_data(l_id).expense_class_line_dtl_id := p_expense_class_line_dtl_id;
2386 p_detail_data(l_id).expense_class_line_id := p_master_data_id;
2387 p_detail_data(l_id).expense_line_dtl_id := p_expense_line_dtl_id;
2388 p_detail_data(l_id).expense_type_code := p_expense_type_code;
2389 p_detail_data(l_id).expense_account_id := p_expense_account_id;
2390 p_detail_data(l_id).expense_amt := p_expense_amount;
2391 p_detail_data(l_id).budgeted_amt := p_budget_amount;
2392 p_detail_data(l_id).recoverable_amt := p_recoverable_amount;
2393 p_detail_data(l_id).computed_recoverable_amt := p_cpt_recoverable_amount;
2394 p_detail_data(l_id).cls_line_dtl_share_pct := p_cls_line_shr_pct;
2395 p_detail_data(l_id).cls_line_dtl_fee_bf_contr := p_cls_line_fee_bf_contr;
2396 p_detail_data(l_id).cls_line_dtl_share_pct_ovr := p_cls_line_shr_pct_ovr;
2397 p_detail_data(l_id).cls_line_dtl_fee_bf_contr_ovr := p_cls_line_fee_bf_contr_ovr;
2398
2399 pnp_debug_pkg.log(l_desc ||' (-)');
2400
2401 EXCEPTION
2402 WHEN OTHERS THEN
2403 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2404 raise;
2405 END;
2406
2407 ------------------------------------------------------------------------------+
2408 -- PROCEDURE : process_exp_class_line_data
2409 -- DESCRIPTION:
2410 -- 1. Dumps data from pl/sql table into expense class lines table.
2411 -- 2. Determine whether fee % and share % should be used at location level
2412 -- 3. If regeneration, find out which data needs to be deleted.
2413 --
2414 -- 19-MAR-03 ftanudja o created
2415 -- 21-MAY-04 ftanudja o added logic for batch commit.
2416 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2417 ------------------------------------------------------------------------------+
2418
2419 PROCEDURE process_exp_class_line_data(
2420 p_master_data IN OUT NOCOPY exp_cls_line_mst_tbl,
2421 p_old_detail_data exp_cls_line_dtl_tbl,
2422 p_old_master_data exp_cls_line_mst_tbl,
2423 p_detail_data exp_cls_line_dtl_tbl,
2424 p_fee_use_table exp_cls_line_use_tbl,
2425 p_share_use_table exp_cls_line_use_tbl,
2426 p_default_fee_bf pn_rec_expcl_dtl.cls_line_fee_before_contr%TYPE
2427 )
2428 IS
2429 l_expense_class_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE;
2430 l_expense_class_line_dtl_id pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE;
2431 l_master_keep_table number_table_type;
2432 l_detail_keep_table number_table_type;
2433 l_master_delete_table number_table_type;
2434 l_detail_delete_table number_table_type;
2435 l_is_in BOOLEAN;
2436 l_use_share_pct_flag VARCHAR2(1);
2437 l_use_fee_pct_flag VARCHAR2(1);
2438 l_info VARCHAR2(300);
2439 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_class_line_data';
2440
2441 BEGIN
2442
2443 pnp_debug_pkg.log(l_desc ||' (+)');
2444
2445 FOR i IN 0 .. p_master_data.COUNT - 1 LOOP
2446
2447 l_info:= ' figuring out whether values should be used ';
2448 pnp_debug_pkg.log(l_info);
2449
2450 l_use_share_pct_flag := 'N';
2451 l_use_fee_pct_flag := 'N';
2452
2453 IF p_fee_use_table(i) THEN
2454 l_use_fee_pct_flag := 'Y';
2455 p_master_data(i).computed_recoverable_amt := p_master_data(i).computed_recoverable_amt *
2456 (1 + nvl(nvl(p_master_data(i).cls_line_fee_before_contr_ovr, p_default_fee_bf), 0) / 100);
2457 END IF;
2458
2459 IF p_share_use_table(i) THEN
2460 l_use_share_pct_flag := 'Y';
2461 p_master_data(i).computed_recoverable_amt := p_master_data(i).computed_recoverable_amt *
2462 nvl(p_master_data(i).cls_line_share_pct, 100) / 100;
2463 END IF;
2464
2465 l_expense_class_line_id := p_master_data(i).expense_class_line_id;
2466
2467 IF l_expense_class_line_id IS NULL THEN
2468
2469 l_info:= ' inserting data into class lines master table';
2470 pnp_debug_pkg.log(l_info);
2471
2472 pn_rec_expcl_dtlln_pkg.insert_row(
2473 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
2474 x_expense_class_dtl_id => p_master_data(i).expense_class_dtl_id,
2475 x_expense_class_line_id => l_expense_class_line_id,
2476 x_location_id => p_master_data(i).location_id,
2477 x_cust_space_assign_id => p_master_data(i).cust_space_assign_id,
2478 x_cust_account_id => p_master_data(i).cust_account_id,
2479 x_lease_id => p_master_data(i).lease_id,
2480 x_recovery_space_std_code => p_master_data(i).recovery_space_std_code,
2481 x_recovery_type_code => p_master_data(i).recovery_type_code,
2482 x_budgeted_amt => p_master_data(i).budgeted_amt,
2483 x_expense_amt => p_master_data(i).expense_amt,
2484 x_recoverable_amt => p_master_data(i).recoverable_amt,
2485 x_computed_recoverable_amt => p_master_data(i).computed_recoverable_amt,
2486 x_cls_line_share_pct => p_master_data(i).cls_line_share_pct,
2487 x_cls_line_fee_bf_ct_ovr => p_master_data(i).cls_line_fee_before_contr_ovr,
2488 x_cls_line_fee_af_ct_ovr => p_master_data(i).cls_line_fee_after_contr_ovr,
2489 x_use_share_pct_flag => l_use_share_pct_flag,
2490 x_use_fee_before_contr_flag => l_use_fee_pct_flag,
2491 x_last_update_date => SYSDATE,
2492 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2493 x_creation_date => SYSDATE,
2494 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2495 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2496 );
2497
2498 p_master_data(i).expense_class_line_id := l_expense_class_line_id;
2499
2500 ELSE
2501
2502 l_info:= ' updating data into class lines table, id: ' || l_expense_class_line_id;
2503 pnp_debug_pkg.log(l_info);
2504
2505 pn_rec_expcl_dtlln_pkg.update_row(
2506 x_expense_class_line_id => l_expense_class_line_id,
2507 x_location_id => p_master_data(i).location_id,
2508 x_cust_space_assign_id => p_master_data(i).cust_space_assign_id,
2509 x_cust_account_id => p_master_data(i).cust_account_id,
2510 x_lease_id => p_master_data(i).lease_id,
2511 x_recovery_space_std_code => p_master_data(i).recovery_space_std_code,
2512 x_recovery_type_code => p_master_data(i).recovery_type_code,
2513 x_budgeted_amt => p_master_data(i).budgeted_amt,
2514 x_expense_amt => p_master_data(i).expense_amt,
2515 x_recoverable_amt => p_master_data(i).recoverable_amt,
2516 x_computed_recoverable_amt => p_master_data(i).computed_recoverable_amt,
2517 x_cls_line_share_pct => p_master_data(i).cls_line_share_pct,
2518 x_cls_line_fee_bf_ct_ovr => p_master_data(i).cls_line_fee_before_contr_ovr,
2519 x_cls_line_fee_af_ct_ovr => p_master_data(i).cls_line_fee_after_contr_ovr,
2520 x_use_share_pct_flag => l_use_share_pct_flag,
2521 x_use_fee_before_contr_flag => l_use_fee_pct_flag,
2522 x_last_update_date => SYSDATE,
2523 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2524 x_creation_date => SYSDATE,
2525 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2526 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2527 );
2528
2529 END IF;
2530
2531 l_master_keep_table(l_master_keep_table.COUNT) := l_expense_class_line_id;
2532
2533 -- do a batch commit if needed
2534 IF mod (i, g_batch_commit_size) = 0 THEN
2535 commit;
2536 END IF;
2537
2538 END LOOP;
2539
2540 l_info:= ' processing data for class line details table';
2541 pnp_debug_pkg.log(l_info);
2542
2543 FOR i IN 0 .. p_detail_data.COUNT - 1 LOOP
2544
2545 l_expense_class_line_dtl_id := p_detail_data(i).expense_class_line_dtl_id;
2546
2547 IF l_expense_class_line_dtl_id IS NULL THEN
2548
2549 l_info := ' inserting detail data for class line header: '||
2550 p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id;
2551 pnp_debug_pkg.log(l_info);
2552
2553 pn_rec_expcl_dtlacc_pkg.insert_row(
2554 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
2555 x_expense_class_line_id => p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id,
2556 x_expense_class_line_dtl_id => l_expense_class_line_dtl_id,
2557 x_expense_line_dtl_id => p_detail_data(i).expense_line_dtl_id,
2558 x_expense_account_id => p_detail_data(i).expense_account_id,
2559 x_expense_type_code => p_detail_data(i).expense_type_code,
2560 x_cls_line_dtl_share_pct => p_detail_data(i).cls_line_dtl_share_pct,
2561 x_cls_line_dtl_share_pct_ovr => p_detail_data(i).cls_line_dtl_share_pct_ovr,
2562 x_cls_line_dtl_fee_bf_ct => p_detail_data(i).cls_line_dtl_fee_bf_contr,
2563 x_cls_line_dtl_fee_bf_ct_ovr => p_detail_data(i).cls_line_dtl_fee_bf_contr_ovr,
2564 x_expense_amt => p_detail_data(i).expense_amt,
2565 x_budgeted_amt => p_detail_data(i).budgeted_amt,
2566 x_recoverable_amt => p_detail_data(i).recoverable_amt,
2567 x_computed_recoverable_amt => p_detail_data(i).computed_recoverable_amt,
2568 x_last_update_date => SYSDATE,
2569 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2570 x_creation_date => SYSDATE,
2571 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2572 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2573 );
2574 ELSE
2575 l_info := ' updating detail data for class line header: '||
2576 p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id;
2577 pnp_debug_pkg.log(l_info);
2578
2579 pn_rec_expcl_dtlacc_pkg.update_row(
2580 x_expense_class_line_dtl_id => l_expense_class_line_dtl_id,
2581 x_expense_line_dtl_id => p_detail_data(i).expense_line_dtl_id,
2582 x_expense_account_id => p_detail_data(i).expense_account_id,
2583 x_expense_type_code => p_detail_data(i).expense_type_code,
2584 x_cls_line_dtl_share_pct => p_detail_data(i).cls_line_dtl_share_pct,
2585 x_cls_line_dtl_share_pct_ovr => p_detail_data(i).cls_line_dtl_share_pct_ovr,
2586 x_cls_line_dtl_fee_bf_ct => p_detail_data(i).cls_line_dtl_fee_bf_contr,
2587 x_cls_line_dtl_fee_bf_ct_ovr => p_detail_data(i).cls_line_dtl_fee_bf_contr_ovr,
2588 x_expense_amt => p_detail_data(i).expense_amt,
2589 x_budgeted_amt => p_detail_data(i).budgeted_amt,
2590 x_recoverable_amt => p_detail_data(i).recoverable_amt,
2591 x_computed_recoverable_amt => p_detail_data(i).computed_recoverable_amt,
2592 x_last_update_date => SYSDATE,
2593 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
2594 x_creation_date => SYSDATE,
2595 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
2596 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
2597 );
2598
2599 END IF;
2600
2601 l_detail_keep_table(l_detail_keep_table.COUNT) := l_expense_class_line_dtl_id;
2602
2603 END LOOP;
2604
2605 l_info := ' figuring out which data is unused';
2606 pnp_debug_pkg.log(l_info);
2607
2608 FOR i IN 0 .. p_old_detail_data.COUNT - 1 LOOP
2609 l_is_in := FALSE;
2610 FOR j IN 0 .. l_detail_keep_table.COUNT - 1 LOOP
2611 IF l_detail_keep_table(j) = p_old_detail_data(i).expense_class_line_dtl_id THEN l_is_in := TRUE; exit; END IF;
2612 END LOOP;
2613 IF NOT l_is_in THEN
2614 l_detail_delete_table(l_detail_delete_table.COUNT) := p_old_detail_data(i).expense_class_line_dtl_id;
2615 END IF;
2616 END LOOP;
2617
2618 FOR i IN 0 .. p_old_master_data.COUNT - 1 LOOP
2619 l_is_in := FALSE;
2620 FOR j IN 0 .. l_master_keep_table.COUNT - 1 LOOP
2621 IF l_master_keep_table(j) = p_old_master_data(i).expense_class_line_id THEN l_is_in := TRUE; exit; END IF;
2622 END LOOP;
2623 IF NOT l_is_in THEN
2624 l_master_delete_table(l_master_delete_table.COUNT) := p_old_master_data(i).expense_class_line_id;
2625 END IF;
2626 END LOOP;
2627
2628 l_info := ' deleting unused data';
2629 pnp_debug_pkg.log(l_info);
2630
2631 FORALL i IN 0 .. l_detail_delete_table.COUNT - 1
2632 DELETE FROM pn_rec_expcl_dtlacc_all
2633 WHERE expense_class_line_dtl_id = l_detail_delete_table(i);
2634
2635 FORALL i IN 0 .. l_master_delete_table.COUNT - 1
2636 DELETE FROM pn_rec_expcl_dtlln_all
2637 WHERE expense_class_line_id = l_master_delete_table(i);
2638
2639
2640 pnp_debug_pkg.log(l_desc ||' (-)');
2641
2642 EXCEPTION
2643 WHEN OTHERS THEN
2644 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2645 raise;
2646 END;
2647
2648 ------------------------------------------------------------------------------+
2649 -- PROCEDURE : dismantle_exp_line_from_dtl
2650 -- DESCRIPTION:
2651 -- 1. When an expense is allocated, it needs to be removed from the tables.
2652 -- 2. Determine which expense class line detail and header it impacts
2653 -- 3. Remove appropriate line and recalculate the comp. recoverable amount.
2654 --
2655 -- NOTE:
2656 -- 1. The total can be derived by adding cumulatively. The problem, however,
2657 -- is to determine whether fee% and share% should be used.
2658 -- 2. To accomplish this, we need to hit the database and do a comparison.
2659 -- 3. Might as well do the summation while getting that information.
2660 --
2661 -- HISTORY:
2662 -- 19-MAR-03 ftanudja o created.
2663 -- 06-AUG-03 ftanudja o add deletion mechanism for expcl lines w/ no child.
2664 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2665 ------------------------------------------------------------------------------+
2666
2667 PROCEDURE dismantle_exp_line_from_dtl(p_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE)
2668 IS
2669 CURSOR get_affected_lines IS
2670 SELECT expense_class_line_dtl_id,
2671 expense_class_line_id
2672 FROM pn_rec_expcl_dtlacc_all
2673 WHERE expense_line_dtl_id = p_expense_line_dtl_id;
2674
2675 CURSOR get_header_info (p_exp_cls_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE) IS
2676 SELECT sum(nvl(computed_recoverable_amt, 0)) computed_recoverable_amount,
2677 sum(nvl(recoverable_amt, 0)) recoverable_amount,
2678 sum(nvl(expense_amt, 0)) expense_amount,
2679 sum(nvl(budgeted_amt, 0)) budgeted_amount,
2680 min(decode(nvl(cls_line_dtl_share_pct_ovr, cls_line_dtl_share_pct), NULL, NULL, 100)) use_cls_line_share,
2681 min(decode(nvl(cls_line_dtl_fee_bf_contr_ovr, cls_line_dtl_fee_bf_contr), NULL, NULL, 0)) use_cls_line_fee
2682 FROM pn_rec_expcl_dtlacc_all
2683 WHERE expense_class_line_id = p_exp_cls_line_id;
2684
2685 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2686 TYPE use_tbl IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
2687
2688 expense_list num_tbl;
2689 budgeted_list num_tbl;
2690 recoverable_list num_tbl;
2691 computed_recoverable_list num_tbl;
2692 hdr_id_list num_tbl;
2693 update_list num_tbl;
2694 delete_list num_tbl;
2695 fee_use_tbl num_tbl;
2696 share_use_tbl num_tbl;
2697 l_update BOOLEAN;
2698 l_count NUMBER;
2699 l_info VARCHAR2(300);
2700 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.dismantle_exp_line_from_dtl' ;
2701
2702 BEGIN
2703
2704 pnp_debug_pkg.log(l_desc ||' (+)');
2705
2706 l_info := ' processing affected lines ';
2707 pnp_debug_pkg.log(l_info);
2708
2709 FOR class_line_dtl_rec IN get_affected_lines LOOP
2710 DELETE pn_rec_expcl_dtlacc_all
2711 WHERE expense_class_line_dtl_id = class_line_dtl_rec.expense_class_line_dtl_id;
2712 hdr_id_list(hdr_id_list.COUNT) := class_line_dtl_rec.expense_class_line_id;
2713 END LOOP;
2714
2715 l_info := ' storing amount information ';
2716 pnp_debug_pkg.log(l_info);
2717
2718 FOR i IN 0 .. hdr_id_list.COUNT - 1 LOOP
2719
2720 l_update := FALSE;
2721
2722 FOR hdr_rec IN get_header_info(hdr_id_list(i)) LOOP
2723 l_info := ' getting update information for expclln id: '||hdr_id_list(i);
2724 pnp_debug_pkg.log(l_info);
2725
2726 l_update := TRUE;
2727 l_count := update_list.COUNT;
2728 update_list(l_count) := hdr_id_list(i);
2729 expense_list(l_count) := hdr_rec.expense_amount;
2730 budgeted_list(l_count) := hdr_rec.budgeted_amount;
2731 recoverable_list(l_count) := hdr_rec.recoverable_amount;
2732 computed_recoverable_list(l_count) := hdr_rec.computed_recoverable_amount;
2733 fee_use_tbl(l_count) := hdr_rec.use_cls_line_fee;
2734 share_use_tbl(l_count) := hdr_rec.use_cls_line_share;
2735
2736 END LOOP;
2737
2738 IF NOT l_update THEN delete_list(delete_list.COUNT) := hdr_id_list(i); END IF;
2739
2740 END LOOP;
2741
2742 l_info := ' updating header information ';
2743 pnp_debug_pkg.log(l_info);
2744
2745 FORALL i IN 0 .. update_list.COUNT - 1
2746 UPDATE pn_rec_expcl_dtlln_all
2747 SET budgeted_amt = budgeted_list(i),
2748 expense_amt = expense_list(i),
2749 recoverable_amt = recoverable_list(i),
2750 computed_recoverable_amt = computed_recoverable_list(i) *
2751 nvl(share_use_tbl(i), cls_line_share_pct) / 100 *
2752 (1 + nvl(fee_use_tbl(i), cls_line_fee_before_contr_ovr) / 100),
2753 last_update_date = SYSDATE,
2754 last_updated_by = nvl(fnd_profile.value('USER_ID'), -1),
2755 last_update_login = nvl(fnd_profile.value('USER_ID'), -1)
2756 WHERE expense_class_line_id = update_list(i);
2757
2758 l_info := ' deleting unused header lines';
2759 pnp_debug_pkg.log(l_info);
2760
2761 FORALL i IN 0 .. delete_list.COUNT - 1
2762 DELETE pn_rec_expcl_dtlln_all
2763 WHERE expense_class_line_id = delete_list(i);
2764
2765 pnp_debug_pkg.log(l_desc ||' (-)');
2766
2767 EXCEPTION
2768 WHEN OTHERS THEN
2769 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2770 raise;
2771 END;
2772
2773 ------------------------------------------------------------------------------+
2774 -- PROCEDURE : populate_expense_class_details
2775 -- DESCRIPTION:
2776 -- 1. Given: expense extract code
2777 -- 2. Finds corresponding expense lines header id and populates all
2778 -- expense class details pertinent to that location / property.
2779 --
2780 -- IF both location_id and property_id are provided, ignore property_id
2781 -- IF location_id given, look for its parent location id and associated
2782 -- property_id (if applicable) and get associated class details
2783 --
2784 -- HISTORY:
2785 -- 19-MAR-03 ftanudja o created
2786 -- 03-JUL-03 ftanudja o fixed cursor to handle cases when only prop id given.
2787 -- 10-JUL-03 ftanudja o made prop id and loc id mutex on get_relevant_expcl.
2788 -- o fix logic on getting prop id. 3046470.
2789 -- 08-AUG-03 ftanudja o fix get .. cursor. 3090131.
2790 -- 18-SEP-03 ftanudja o added currency code filter. 3148855.
2791 -- 21-MAY-03 ftanudja o added counters to summarize totals. 3591556.
2792 -- o restructured and fixed CURSOR logic.
2793 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2794 ------------------------------------------------------------------------------+
2795
2796 PROCEDURE populate_expense_class_details(
2797 p_location_id pn_rec_exp_line.location_id%TYPE,
2798 p_property_id pn_rec_exp_line.property_id%TYPE,
2799 p_as_of_date VARCHAR2,
2800 p_from_date VARCHAR2,
2801 p_to_date VARCHAR2,
2802 p_extract_code pn_rec_exp_line.expense_extract_code%TYPE,
2803 p_keep_override VARCHAR2)
2804 IS
2805
2806 CURSOR get_exp_line_id IS
2807 SELECT expense_line_id,
2808 currency_code
2809 FROM pn_rec_exp_line_all hdr
2810 WHERE hdr.expense_extract_code = p_extract_code
2811 AND rownum < 2;
2812
2813 CURSOR get_expcl_by_prop (p_propid pn_rec_exp_line.property_id%TYPE,
2814 p_currency_code pn_rec_exp_line.currency_code%TYPE) IS
2815 SELECT expense_class_id
2816 FROM pn_rec_expcl_all
2817 WHERE property_id = p_propid
2818 AND location_id IS NULL
2819 AND currency_code = p_currency_code;
2820
2821 CURSOR get_expcl_by_locn (p_locnid pn_rec_exp_line.location_id%TYPE,
2822 p_currency_code pn_rec_exp_line.currency_code%TYPE) IS
2823 SELECT expense_class_id
2824 FROM pn_rec_expcl_all
2825 WHERE location_id = p_locnid
2826 AND currency_code = p_currency_code;
2827
2828 CURSOR get_locn_prop_id IS
2829 SELECT property_id,
2830 location_id
2831 FROM pn_locations_all
2832 WHERE active_start_date < fnd_date.canonical_to_date(p_to_date)
2833 AND active_end_date > fnd_date.canonical_to_date(p_from_date)
2834 START WITH location_id = p_location_id
2835 CONNECT BY location_id = PRIOR parent_location_id;
2836
2837 l_propid pn_rec_exp_line.property_id%TYPE;
2838 l_dummy VARCHAR2(300);
2839 l_info VARCHAR2(100);
2840 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.populate_expense_class_details' ;
2841 l_total NUMBER := 0;
2842 l_failed NUMBER := 0;
2843
2844 BEGIN
2845 pnp_debug_pkg.log(l_desc ||' (+)');
2846
2847 FOR id IN get_exp_line_id LOOP
2848
2849 IF p_location_id IS NOT NULL THEN
2850
2851 FOR locn_prop_rec IN get_locn_prop_id LOOP
2852
2853 /* If the location belongs to a property, take note of that */
2854 IF l_propid IS NULL THEN l_propid := locn_prop_rec.property_id; END IF;
2855
2856 FOR expcl_rec IN get_expcl_by_locn(locn_prop_rec.location_id, id.currency_code) LOOP
2857 l_info := ' extracting for expense class id : '|| expcl_rec.expense_class_id;
2858 pnp_debug_pkg.log(l_info);
2859
2860 l_total := l_total + 1;
2861
2862 BEGIN
2863
2864 extract_expense(
2865 errbuf => l_dummy,
2866 retcode => l_dummy,
2867 p_expense_class_id => expcl_rec.expense_class_id,
2868 p_as_of_date => p_as_of_date,
2869 p_from_date => p_from_date,
2870 p_to_date => p_to_date,
2871 p_expense_line_id => id.expense_line_id,
2872 p_keep_override => p_keep_override);
2873
2874 EXCEPTION
2875 WHEN OTHERS THEN
2876 l_failed := l_failed + 1;
2877 END;
2878
2879 END LOOP;
2880 END LOOP;
2881 END IF;
2882
2883 IF (p_property_id IS NOT NULL AND p_location_id IS NULL) OR
2884 l_propid IS NOT NULL
2885 THEN
2886
2887 FOR expcl_rec IN get_expcl_by_prop(nvl(l_propid, p_property_id), id.currency_code) LOOP
2888 l_info := ' extracting for expense class id : '|| expcl_rec.expense_class_id;
2889 pnp_debug_pkg.log(l_info);
2890
2891 l_total := l_total + 1;
2892
2893 BEGIN
2894 extract_expense(
2895 errbuf => l_dummy,
2896 retcode => l_dummy,
2897 p_expense_class_id => expcl_rec.expense_class_id,
2898 p_as_of_date => p_as_of_date,
2899 p_from_date => p_from_date,
2900 p_to_date => p_to_date,
2901 p_expense_line_id => id.expense_line_id,
2902 p_keep_override => p_keep_override);
2903
2904 EXCEPTION
2905 WHEN OTHERS THEN
2906 l_failed := l_failed + 1;
2907 END;
2908
2909 END LOOP;
2910
2911 END IF;
2912 END LOOP;
2913
2914 fnd_message.set_name('PN','PN_REC_EXPCL_DTL');
2915 pnp_debug_pkg.put_log_msg('');
2916 pnp_debug_pkg.put_log_msg(fnd_message.get);
2917
2918 fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2919 fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2920 fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2921 fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2922 pnp_debug_pkg.put_log_msg(fnd_message.get);
2923 pnp_debug_pkg.put_log_msg('');
2924
2925 pnp_debug_pkg.log(l_desc ||' (-)');
2926
2927 EXCEPTION
2928 WHEN OTHERS THEN
2929 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2930 raise;
2931 END populate_expense_class_details;
2932
2933 ------------------------------------------------------------------------------+
2934 -- PROCEDURE : populate_area_class_details
2935 -- DESCRIPTION:
2936 -- 1. Given: location id
2937 -- 2. Finds area class details pertinent to that location / property.
2938 --
2939 -- IF both location_id and property_id are provided, ignore property_id
2940 -- IF location_id given, look for its parent location id and associated
2941 -- property_id (if applicable) and get associated class details
2942 --
2943 -- HISTORY:
2944 -- 19-MAR-03 ftanudja o created
2945 -- 03-JUL-03 ftanudja o fixed cursor to handle cases when only prop id given.
2946 -- 10-JUL-03 ftanudja o made prop id and loc id mutex on get_relevant_arcl.
2947 -- o fix logic on getting prop id. 3046470.
2948 -- 08-AUG-03 ftanudja o fix get .. cursor. 3090131.
2949 -- 21-MAY-03 ftanudja o added counters to summarize totals. 3591556.
2950 -- o restructured and fixed CURSOR logic.
2951 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
2952 ------------------------------------------------------------------------------+
2953
2954 PROCEDURE populate_area_class_details(
2955 p_location_id pn_rec_exp_line.location_id%TYPE,
2956 p_property_id pn_rec_exp_line.property_id%TYPE,
2957 p_as_of_date VARCHAR2,
2958 p_from_date VARCHAR2,
2959 p_to_date VARCHAR2,
2960 p_keep_override VARCHAR2)
2961 IS
2962
2963 CURSOR get_arcl_by_prop (l_propid pn_rec_exp_line.property_id%TYPE) IS
2964 SELECT area_class_id
2965 FROM pn_rec_arcl_all
2966 WHERE property_id = l_propid
2967 AND location_id IS NULL;
2968
2969 CURSOR get_arcl_by_locn (l_locnid pn_rec_exp_line.location_id%TYPE) IS
2970 SELECT area_class_id
2971 FROM pn_rec_arcl_all
2972 WHERE location_id = l_locnid;
2973
2974 CURSOR get_locn_prop_id IS
2975 SELECT property_id,
2976 location_id
2977 FROM pn_locations_all
2978 WHERE active_start_date < fnd_date.canonical_to_date(p_to_date)
2979 AND active_end_date > fnd_date.canonical_to_date(p_from_date)
2980 START WITH location_id = p_location_id
2981 CONNECT BY location_id = PRIOR parent_location_id;
2982
2983 l_propid pn_rec_exp_line.property_id%TYPE;
2984 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.populate_area_class_details' ;
2985 l_info VARCHAR2(100);
2986 l_dummy VARCHAR2(300);
2987 l_total NUMBER := 0;
2988 l_failed NUMBER := 0;
2989
2990 BEGIN
2991
2992 pnp_debug_pkg.log(l_desc ||' (+)');
2993
2994 IF p_location_id IS NOT NULL THEN
2995
2996 FOR locn_prop_rec IN get_locn_prop_id LOOP
2997
2998 /* If the location belongs to a property, take note of that */
2999 IF l_propid IS NULL THEN l_propid := locn_prop_rec.property_id; END IF;
3000
3001 FOR arcl_rec IN get_arcl_by_locn(locn_prop_rec.location_id) LOOP
3002 l_info := ' extracting for area class id : '|| arcl_rec.area_class_id;
3003 pnp_debug_pkg.log(l_info);
3004
3005 l_total := l_total + 1;
3006
3007 BEGIN
3008 extract_area(
3009 errbuf => l_dummy,
3010 retcode => l_dummy,
3011 p_area_class_id => arcl_rec.area_class_id,
3012 p_as_of_date => p_as_of_date,
3013 p_from_date => p_from_date,
3014 p_to_date => p_to_date,
3015 p_keep_override => p_keep_override);
3016
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 l_failed := l_failed + 1;
3020 END;
3021
3022 END LOOP;
3023 END LOOP;
3024 END IF;
3025
3026 IF (p_property_id IS NOT NULL AND p_location_id IS NULL) OR
3027 l_propid IS NOT NULL
3028 THEN
3029
3030 FOR arcl_rec IN get_arcl_by_prop(nvl(l_propid, p_property_id)) LOOP
3031 l_info := ' extracting for area class id : '|| arcl_rec.area_class_id;
3032 pnp_debug_pkg.log(l_info);
3033
3034 l_total := l_total + 1;
3035
3036 BEGIN
3037 extract_area(
3038 errbuf => l_dummy,
3039 retcode => l_dummy,
3040 p_area_class_id => arcl_rec.area_class_id,
3041 p_as_of_date => p_as_of_date,
3042 p_from_date => p_from_date,
3043 p_to_date => p_to_date,
3044 p_keep_override => p_keep_override);
3045
3046 EXCEPTION
3047 WHEN OTHERS THEN
3048 l_failed := l_failed + 1;
3049 END;
3050
3051 END LOOP;
3052
3053 END IF;
3054
3055 fnd_message.set_name('PN','PN_REC_ARCL_DTL');
3056 pnp_debug_pkg.put_log_msg('');
3057 pnp_debug_pkg.put_log_msg(fnd_message.get);
3058
3059 fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
3060 fnd_message.set_token('TOTAL', TO_CHAR(l_total));
3061 fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
3062 fnd_message.set_token('FAIL', TO_CHAR(l_failed));
3063 pnp_debug_pkg.put_log_msg(fnd_message.get);
3064 pnp_debug_pkg.put_log_msg('');
3065
3066 pnp_debug_pkg.log(l_desc ||' (-)');
3067
3068 EXCEPTION
3069 WHEN OTHERS THEN
3070 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3071 raise;
3072 END populate_area_class_details;
3073
3074 ------------------------------------------------------------------------------+
3075 -- PROCEDURE : find_expense_ovr_values
3076 -- DESCRIPTION:
3077 -- 1. Given: data table p_master_ovr, p_detail_ovr, parameters p_exp_type, etc.
3078 -- 2. Search through data tables using the parameter criteria.
3079 -- 3. If match found, check if p_keep_override = Y.
3080 -- 4. If true, return override values p_fee_af_contr_ovr, etc.
3081 -- 5. Otherwise, just return the corresponding item id if needed.
3082 --
3083 -- HISTORY:
3084 -- 19-MAR-03 ftanudja o created
3085 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
3086 ------------------------------------------------------------------------------+
3087
3088 PROCEDURE find_expense_ovr_values(
3089 p_master_ovr exp_cls_line_mst_tbl,
3090 p_detail_ovr exp_cls_line_dtl_tbl,
3091 p_exp_type pn_rec_expcl_dtlacc.expense_type_code%TYPE,
3092 p_exp_acct pn_rec_expcl_dtlacc.expense_account_id%TYPE,
3093 p_loc_id pn_rec_expcl_dtlln.location_id%TYPE,
3094 p_cust_id pn_rec_expcl_dtlln.cust_account_id%TYPE,
3095 p_rec_spc_std pn_rec_expcl_dtlln.recovery_space_std_code%TYPE,
3096 p_rec_type pn_rec_expcl_dtlln.recovery_type_code%TYPE,
3097 p_exp_cls_line_dtl_id OUT NOCOPY pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE,
3098 p_exp_cls_line_id OUT NOCOPY pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE,
3099 p_fee_af_contr_ovr OUT NOCOPY pn_rec_expcl_dtlln.cls_line_fee_after_contr_ovr%TYPE,
3100 p_mst_share_pct_ovr OUT NOCOPY pn_rec_expcl_dtlln.cls_line_share_pct%TYPE,
3101 p_dtl_share_pct_ovr OUT NOCOPY pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE,
3102 p_mst_fee_bf_contr_ovr OUT NOCOPY pn_rec_expcl_dtlln.cls_line_fee_before_contr_ovr%TYPE,
3103 p_dtl_fee_bf_contr_ovr OUT NOCOPY pn_rec_expcl_dtlacc.cls_line_dtl_fee_bf_contr%TYPE,
3104 p_found IN OUT NOCOPY BOOLEAN,
3105 p_keep_override VARCHAR2
3106 )
3107 IS
3108 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.find_expense_ovr_values' ;
3109 l_info VARCHAR2(300);
3110 BEGIN
3111
3112 pnp_debug_pkg.log(l_desc ||' (+)');
3113
3114 l_info := ' resetting ovr values variables ';
3115 pnp_debug_pkg.log(l_info);
3116
3117 p_exp_cls_line_dtl_id := null;
3118 p_exp_cls_line_id := null;
3119 p_fee_af_contr_ovr := null;
3120 p_mst_share_pct_ovr := null;
3121 p_dtl_share_pct_ovr := null;
3122 p_mst_fee_bf_contr_ovr := null;
3123 p_dtl_fee_bf_contr_ovr := null;
3124
3125 FOR i IN 0 .. p_master_ovr.COUNT - 1 LOOP
3126 IF p_detail_ovr(i).expense_account_id = p_exp_acct AND
3127 p_detail_ovr(i).expense_type_code = p_exp_type AND
3128 p_master_ovr(i).recovery_space_std_code = p_rec_spc_std AND
3129 p_master_ovr(i).recovery_type_code = p_rec_type AND
3130 p_master_ovr(i).location_id = p_loc_id AND
3131 p_master_ovr(i).cust_account_id = p_cust_id THEN
3132
3133 l_info := ' found matching data and determining which values to return ';
3134 pnp_debug_pkg.log(l_info);
3135
3136 IF p_keep_override = 'Y' THEN
3137 p_fee_af_contr_ovr := p_master_ovr(i).cls_line_fee_after_contr_ovr;
3138 p_mst_share_pct_ovr := p_master_ovr(i).cls_line_share_pct;
3139 p_dtl_share_pct_ovr := p_detail_ovr(i).cls_line_dtl_share_pct_ovr;
3140 p_mst_fee_bf_contr_ovr := p_master_ovr(i).cls_line_fee_before_contr_ovr;
3141 p_dtl_fee_bf_contr_ovr := p_detail_ovr(i).cls_line_dtl_fee_bf_contr_ovr;
3142 END IF;
3143
3144 IF p_found IS NOT NULL THEN
3145 p_found := TRUE;
3146 p_exp_cls_line_dtl_id := p_detail_ovr(i).expense_class_line_dtl_id;
3147 p_exp_cls_line_id := p_master_ovr(i).expense_class_line_id;
3148 END IF;
3149
3150 END IF;
3151 END LOOP;
3152
3153 pnp_debug_pkg.log(l_desc ||' (-)');
3154
3155 EXCEPTION
3156 WHEN OTHERS THEN
3157 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3158 raise;
3159 END find_expense_ovr_values;
3160
3161 ------------------------------------------------------------------------------+
3162 -- PROCEDURE : expense_class_extract
3163 -- ASSUMES : validation done at UI level when calling generate details
3164 -- ,in particular, ensuring expense_line_id is correlated to
3165 -- expense_class_id.
3166 -- DESCRIPTION:
3167 -- o given an expense class id, get details of the expense class.
3168 -- o get all expense lines for locations pertaining to that expense class.
3169 -- o get all space assignment for locations pertaining to that expense class
3170 -- for which the start date and financial obligation date is between
3171 -- the start and end date of the expense line extract.
3172 -- o collect data, do necessary calculations and put them in a pl/sql table.
3173 -- o use pl/sql table to do the proper calculation at both the class line and
3174 -- class line detail level.
3175 -- o dump data into database table; if id already exists, update, otherwise
3176 -- insert.
3177 --
3178 -- HISTORY:
3179 -- 19-MAR-03 ftanudja o created
3180 -- 12-MAY-03 ftanudja o use location / property id from exp lines to get
3181 -- space assignments, as opposed to using the expense
3182 -- class' location / property id.
3183 -- 11-JUN-03 ftanudja o add filter recoverable_flag='Y' in get_exp_lines_info.
3184 -- 10-JUL-03 ftanudja o made loc id and prop id mutex in getting cust asgnmt.
3185 -- 11-JUL-03 ftanudja o changed query for get_exp_line_info. 3045056.
3186 -- 05-AUG-03 ftanudja o added l_updcondition to fix logic. 3075129.
3187 -- o added order by to date and from date for ovr values.
3188 -- o optimized get_ovr_from_prior CURSOR (break in 2).
3189 -- 06-AUG-03 ftanudja o change flow => if found 'PARENT' exp line, do nothing.
3190 -- 18-SEP-03 ftanudja o added currency code check. 3148855.
3191 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
3192 ------------------------------------------------------------------------------+
3193
3194 PROCEDURE extract_expense(
3195 errbuf OUT NOCOPY VARCHAR2,
3196 retcode OUT NOCOPY VARCHAR2,
3197 p_expense_class_id IN pn_rec_expcl.expense_class_id%TYPE,
3198 p_as_of_date IN VARCHAR2,
3199 p_from_date IN VARCHAR2,
3200 p_to_date IN VARCHAR2,
3201 p_expense_line_id IN pn_rec_exp_line.expense_line_id%TYPE,
3202 p_keep_override IN VARCHAR2)
3203 IS
3204
3205 CURSOR check_currency IS
3206 SELECT 1
3207 FROM pn_rec_exp_line_all
3208 WHERE expense_line_id = p_expense_line_id
3209 AND currency_code NOT IN
3210 (SELECT currency_code FROM pn_rec_expcl_all
3211 WHERE expense_class_id = p_expense_class_id);
3212
3213 CURSOR get_exp_class_info IS
3214 SELECT class.expense_class_id,
3215 class_type.expense_class_type_id,
3216 class.area_class_id,
3217 class.location_id,
3218 class.property_id,
3219 class.portion_pct,
3220 class_type.expense_type_code,
3221 class_inclusion.cls_incl_share_pct,
3222 class.class_fee_before_contr,
3223 class.class_fee_after_contr,
3224 class_inclusion.cls_incl_fee_before_contr,
3225 class_inclusion.recovery_type_code,
3226 class_inclusion.recovery_space_std_code
3227 FROM pn_rec_expcl_all class,
3228 pn_rec_expcl_type_all class_type,
3229 pn_rec_expcl_inc_all class_inclusion
3230 WHERE class.expense_class_id = class_type.expense_class_id
3231 AND class_type.expense_class_type_id = class_inclusion.expense_class_type_id
3232 AND class.expense_class_id = p_expense_class_id;
3233
3234 CURSOR get_exp_lines_info (
3235 p_expense_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE) IS
3236 SELECT nvl(lines_dtl.actual_amount_ovr, lines_dtl.actual_amount) actual_amount,
3237 nvl(lines_dtl.budgeted_amount_ovr, lines_dtl.budgeted_amount) budgeted_amount,
3238 lines_dtl.expense_type_code,
3239 lines_dtl.expense_account_id,
3240 lines_dtl.location_id,
3241 lines_dtl.property_id,
3242 lines_dtl.expense_line_dtl_id,
3243 lines_dtl.expense_line_id,
3244 lines_dtl.expense_line_indicator
3245 FROM pn_rec_exp_line_dtl_all lines_dtl
3246 WHERE (lines_dtl.expense_line_id = p_expense_line_id OR
3247 lines_dtl.parent_expense_line_id IN
3248 (SELECT expense_line_dtl_id
3249 FROM pn_rec_exp_line_dtl_all
3250 WHERE expense_line_id = p_expense_line_id))
3251 AND lines_dtl.expense_type_code = p_expense_type_code
3252 AND lines_dtl.recoverable_flag = 'Y';
3253
3254 CURSOR get_cust_assignment_info(
3255 p_location_id pn_locations.location_id%TYPE,
3256 p_property_id pn_locations.property_id%TYPE,
3257 p_rec_spc_std_code pn_space_assign_cust.recovery_space_std_code%TYPE,
3258 p_rec_type_code pn_space_assign_cust.recovery_type_code%TYPE) IS
3259 SELECT cust.cust_space_assign_id,
3260 cust.cust_account_id,
3261 cust.lease_id,
3262 cust.location_id,
3263 cust.recovery_space_std_code,
3264 cust.recovery_type_code
3265 FROM pn_space_assign_cust_all cust
3266 WHERE cust.location_id IN
3267 (SELECT location_id FROM pn_locations_all locn
3268 WHERE locn.active_start_date < fnd_date.canonical_to_date(p_to_date)
3269 AND locn.active_end_date > fnd_date.canonical_to_date(p_from_date))
3270 AND cust.cust_assign_start_date < fnd_date.canonical_to_date(p_to_date)
3271 AND cust.fin_oblig_end_date > fnd_date.canonical_to_date(p_from_date)
3272 AND cust.recovery_space_std_code = p_rec_spc_std_code
3273 AND cust.recovery_type_code = p_rec_type_code
3274 AND cust.location_id IN
3275 (SELECT location_id FROM pn_locations_all
3276 START WITH (location_id = p_location_id OR
3277 (property_id = p_property_id AND p_location_id IS NULL))
3278 CONNECT BY PRIOR location_id = parent_location_id);
3279
3280 CURSOR get_ovr_from_current IS
3281 SELECT class_line.cls_line_share_pct mst_shr_pc,
3282 class_line.cls_line_fee_after_contr_ovr mst_fee_af,
3283 class_line.cls_line_fee_before_contr_ovr mst_fee_bf,
3284 class_line.location_id location_id,
3285 class_line_dtl.cls_line_dtl_fee_bf_contr_ovr dtl_fee_bf,
3286 class_line_dtl.cls_line_dtl_share_pct_ovr dtl_shr_pc,
3287 class_line.cust_account_id cust_account_id,
3288 class_line.recovery_space_std_code rec_space_std,
3289 class_line.recovery_type_code rec_type_code,
3290 class_line_dtl.expense_type_code exp_type,
3291 class_line_dtl.expense_account_id exp_acct,
3292 class_line_dtl.expense_class_line_dtl_id dtl_id,
3293 class_line_dtl.expense_class_line_id mst_id
3294 FROM pn_rec_expcl_dtl_all summary,
3295 pn_rec_expcl_dtlln_all class_line,
3296 pn_rec_expcl_dtlacc_all class_line_dtl,
3297 pn_rec_expcl_all class,
3298 pn_rec_exp_line_all lines
3299 WHERE class_line.expense_class_line_id = class_line_dtl.expense_class_line_id
3300 AND class_line.expense_class_dtl_id = summary.expense_class_dtl_id
3301 AND summary.expense_class_id = class.expense_class_id
3302 AND class.expense_class_id = p_expense_class_id
3303 AND summary.expense_line_id = lines.expense_line_id
3304 AND lines.expense_line_id = p_expense_line_id;
3305
3306 CURSOR get_prior_cls_dtl_id IS
3307 SELECT summary.expense_class_dtl_id
3308 FROM pn_rec_expcl_dtl_all summary,
3309 pn_rec_expcl_all class,
3310 pn_rec_exp_line_all line_hdr,
3311 (SELECT to_date, as_of_date FROM pn_rec_exp_line_all
3312 WHERE expense_line_id = p_expense_line_id) ref_line_hdr
3313 WHERE summary.expense_class_id = class.expense_class_id
3314 AND summary.expense_line_id = line_hdr.expense_line_id
3315 AND class.expense_class_id = p_expense_class_id
3316 AND line_hdr.from_date < ref_line_hdr.to_date
3317 AND line_hdr.to_date <= ref_line_hdr.to_date
3318 AND line_hdr.as_of_date < ref_line_hdr.as_of_date
3319 ORDER BY line_hdr.as_of_date DESC, line_hdr.to_date DESC, line_hdr.from_date DESC;
3320
3321 CURSOR get_ovr_from_prior (p_prior_cls_dtl_id pn_rec_expcl_dtlln.expense_class_dtl_id%TYPE) IS
3322 SELECT class_line.cls_line_share_pct mst_shr_pc,
3323 class_line.cls_line_fee_after_contr_ovr mst_fee_af,
3324 class_line.cls_line_fee_before_contr_ovr mst_fee_bf,
3325 class_line.location_id location_id,
3326 class_line_dtl.cls_line_dtl_fee_bf_contr_ovr dtl_fee_bf,
3327 class_line_dtl.cls_line_dtl_share_pct_ovr dtl_shr_pc,
3328 class_line.cust_account_id cust_account_id,
3329 class_line.recovery_space_std_code rec_space_std,
3330 class_line.recovery_type_code rec_type_code,
3331 class_line_dtl.expense_type_code exp_type,
3332 class_line_dtl.expense_account_id exp_acct
3333 FROM pn_rec_expcl_dtlln_all class_line,
3334 pn_rec_expcl_dtlacc_all class_line_dtl
3335 WHERE class_line.expense_class_line_id = class_line_dtl.expense_class_line_id
3336 AND class_line.expense_class_dtl_id = p_prior_cls_dtl_id;
3337
3338 CURSOR is_reextract IS
3339 SELECT dtl.expense_class_dtl_id,
3340 setup.expense_class_name,
3341 dtl.status,
3342 dtl.default_area_class_id,
3343 dtl.cls_line_portion_pct,
3344 dtl.cls_line_fee_before_contr,
3345 dtl.cls_line_fee_after_contr
3346 FROM pn_rec_expcl_dtl_all dtl,
3347 pn_rec_expcl_all setup
3348 WHERE dtl.expense_line_id = p_expense_line_id
3349 AND setup.expense_class_id = p_expense_class_id
3350 AND setup.expense_class_id = dtl.expense_class_id;
3351
3352 l_regenerate VARCHAR2(1);
3353 l_info VARCHAR2(300);
3354 l_dummy VARCHAR2(300);
3355 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_expense' ;
3356 l_token VARCHAR2(100);
3357
3358 l_master_data_id NUMBER;
3359 l_count NUMBER;
3360 l_found BOOLEAN;
3361 l_updcondition BOOLEAN;
3362 l_dummy_id pn_rec_expcl_dtl.expense_class_dtl_id%TYPE;
3363
3364 l_recov_amount pn_rec_expcl_dtlln.recoverable_amt%TYPE;
3365 l_cpt_recov_amount pn_rec_expcl_dtlln.computed_recoverable_amt%TYPE;
3366 l_mst_share_pct_ovr pn_rec_expcl_dtlln.cls_line_share_pct%TYPE;
3367 l_dtl_share_pct_ovr pn_rec_expcl_dtlacc.cls_line_dtl_share_pct%TYPE;
3368 l_fee_af_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_after_contr_ovr%TYPE;
3369 l_dtl_fee_bf_contr_ovr pn_rec_expcl_dtlacc.cls_line_dtl_fee_bf_contr%TYPE;
3370 l_mst_fee_bf_contr_ovr pn_rec_expcl_dtlln.cls_line_fee_before_contr_ovr%TYPE;
3371
3372 l_mst_fee_bf_contr pn_rec_expcl_dtl.cls_line_fee_before_contr%TYPE;
3373 l_fee_af_contr pn_rec_expcl_dtl.cls_line_fee_after_contr%TYPE;
3374 l_portion_pct pn_rec_expcl_dtl.cls_line_portion_pct%TYPE;
3375
3376 l_expense_cls_line_id pn_rec_expcl_dtlln.expense_class_line_id%TYPE;
3377 l_expense_cls_line_dtl_id pn_rec_expcl_dtlacc.expense_class_line_dtl_id%TYPE;
3378 l_area_class_id pn_rec_expcl.area_class_id%TYPE;
3379 l_expense_class_dtl_id pn_rec_expcl_dtl.expense_class_dtl_id%TYPE;
3380
3381 l_fee_use_table exp_cls_line_use_tbl;
3382 l_share_use_table exp_cls_line_use_tbl;
3383 l_ovr_use_table exp_cls_line_use_tbl;
3384
3385 exp_cls_line_master_data exp_cls_line_mst_tbl;
3386 exp_cls_line_detail_data exp_cls_line_dtl_tbl;
3387 exp_cls_curnt_master_ovr exp_cls_line_mst_tbl;
3388 exp_cls_curnt_detail_ovr exp_cls_line_dtl_tbl;
3389 exp_cls_prior_master_ovr exp_cls_line_mst_tbl;
3390 exp_cls_prior_detail_ovr exp_cls_line_dtl_tbl;
3391
3392 BEGIN
3393 pnp_debug_pkg.log(l_desc ||' (+)');
3394
3395 fnd_message.set_name('PN','PN_REC_EXPCL_DTL_CP_INFO');
3396 fnd_message.set_token('EXPCL', to_char(p_expense_class_id));
3397 fnd_message.set_token('EXPLN', to_char(p_expense_line_id));
3398 fnd_message.set_token('STR' , p_from_date);
3399 fnd_message.set_token('END' , p_to_date);
3400 fnd_message.set_token('AOD' , p_as_of_date);
3401 fnd_message.set_token('OVR' , p_keep_override);
3402 pnp_debug_pkg.put_log_msg('');
3403 pnp_debug_pkg.put_log_msg(fnd_message.get);
3404 pnp_debug_pkg.put_log_msg('');
3405
3406 l_info := ' validating currency';
3407 pnp_debug_pkg.log(l_info);
3408
3409 FOR check_cur IN check_currency LOOP
3410 fnd_message.set_name('PN', 'PN_REC_EXP_CUR_MISMATCH');
3411 RAISE currency_exception;
3412 END LOOP;
3413
3414 l_info := ' initializing values ';
3415 pnp_debug_pkg.log(l_info);
3416
3417 l_fee_use_table.delete;
3418 l_share_use_table.delete;
3419 l_ovr_use_table.delete;
3420
3421 exp_cls_line_master_data.delete;
3422 exp_cls_line_detail_data.delete;
3423 exp_cls_curnt_master_ovr.delete;
3424 exp_cls_curnt_detail_ovr.delete;
3425 exp_cls_prior_master_ovr.delete;
3426 exp_cls_prior_detail_ovr.delete;
3427
3428 l_info := ' caching default and override values';
3429 pnp_debug_pkg.log(l_info);
3430
3431 l_regenerate := 'N';
3432
3433 FOR check_exists IN is_reextract LOOP
3434
3435 IF check_exists.status = 'LOCKED' THEN
3436 fnd_message.set_name('PN','PN_REC_EXPCL_DTL');
3437 l_token := fnd_message.get;
3438 fnd_message.set_name('PN','PN_REC_NO_REGEN_LOCKED');
3439 fnd_message.set_token('MODULE',l_token);
3440 fnd_message.set_token('FDATE', p_from_date);
3441 fnd_message.set_token('TDATE', p_to_date);
3442 fnd_message.set_token('AODATE', p_as_of_date);
3443 fnd_message.set_token('NAME', check_exists.expense_class_name);
3444 pnp_debug_pkg.log(fnd_message.get);
3445 RETURN;
3446 END IF;
3447
3448 l_regenerate := 'Y';
3449 l_area_class_id := check_exists.default_area_class_id;
3450 l_mst_fee_bf_contr := check_exists.cls_line_fee_before_contr;
3451 l_fee_af_contr := check_exists.cls_line_fee_after_contr;
3452 l_portion_pct := check_exists.cls_line_portion_pct;
3453 l_expense_class_dtl_id := check_exists.expense_class_dtl_id;
3454
3455 END LOOP;
3456
3457 IF l_regenerate = 'Y' THEN
3458 FOR get_ovr_rec IN get_ovr_from_current LOOP
3459 l_count := exp_cls_curnt_master_ovr.COUNT;
3460
3461 exp_cls_curnt_master_ovr(l_count).cls_line_share_pct := get_ovr_rec.mst_shr_pc;
3462 exp_cls_curnt_master_ovr(l_count).cls_line_fee_after_contr_ovr := get_ovr_rec.mst_fee_af;
3463 exp_cls_curnt_master_ovr(l_count).cls_line_fee_before_contr_ovr := get_ovr_rec.mst_fee_bf;
3464 exp_cls_curnt_master_ovr(l_count).expense_class_line_id := get_ovr_rec.mst_id;
3465 exp_cls_curnt_master_ovr(l_count).location_id := get_ovr_rec.location_id;
3466 exp_cls_curnt_master_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
3467 exp_cls_curnt_master_ovr(l_count).recovery_space_std_code := get_ovr_rec.rec_space_std;
3468 exp_cls_curnt_master_ovr(l_count).recovery_type_code := get_ovr_rec.rec_type_code;
3469
3470 exp_cls_curnt_detail_ovr(l_count).cls_line_dtl_fee_bf_contr_ovr := get_ovr_rec.dtl_fee_bf;
3471 exp_cls_curnt_detail_ovr(l_count).cls_line_dtl_share_pct_ovr := get_ovr_rec.dtl_shr_pc;
3472 exp_cls_curnt_detail_ovr(l_count).expense_class_line_dtl_id := get_ovr_rec.dtl_id;
3473 exp_cls_curnt_detail_ovr(l_count).expense_type_code := get_ovr_rec.exp_type;
3474 exp_cls_curnt_detail_ovr(l_count).expense_account_id := get_ovr_rec.exp_acct;
3475
3476 END LOOP;
3477 END IF;
3478
3479 l_dummy_id := null;
3480 l_info := ' getting prior cls dtl id for overrides ';
3481 pnp_debug_pkg.log(l_info);
3482
3483 FOR get_first_id IN get_prior_cls_dtl_id LOOP
3484 l_dummy_id := get_first_id.expense_class_dtl_id;
3485 exit;
3486 END LOOP;
3487
3488 FOR get_ovr_rec IN get_ovr_from_prior(l_dummy_id) LOOP
3489
3490 l_count := exp_cls_prior_detail_ovr.COUNT;
3491
3492 exp_cls_prior_master_ovr(l_count).cls_line_share_pct := get_ovr_rec.mst_shr_pc;
3493 exp_cls_prior_master_ovr(l_count).cls_line_fee_after_contr_ovr := get_ovr_rec.mst_fee_af;
3494 exp_cls_prior_master_ovr(l_count).cls_line_fee_before_contr_ovr := get_ovr_rec.mst_fee_bf;
3495 exp_cls_prior_master_ovr(l_count).location_id := get_ovr_rec.location_id;
3496 exp_cls_prior_master_ovr(l_count).cust_account_id := get_ovr_rec.cust_account_id;
3497 exp_cls_prior_master_ovr(l_count).recovery_space_std_code := get_ovr_rec.rec_space_std;
3498 exp_cls_prior_master_ovr(l_count).recovery_type_code := get_ovr_rec.rec_type_code;
3499
3500 exp_cls_prior_detail_ovr(l_count).cls_line_dtl_fee_bf_contr_ovr := get_ovr_rec.dtl_fee_bf;
3501 exp_cls_prior_detail_ovr(l_count).cls_line_dtl_share_pct_ovr := get_ovr_rec.dtl_shr_pc;
3502 exp_cls_prior_detail_ovr(l_count).expense_type_code := get_ovr_rec.exp_type;
3503 exp_cls_prior_detail_ovr(l_count).expense_account_id := get_ovr_rec.exp_acct;
3504
3505 END LOOP;
3506
3507 l_info := ' fetching information to prepare data processing ';
3508 pnp_debug_pkg.log(l_info);
3509
3510 FOR expense_class_rec IN get_exp_class_info LOOP
3511
3512 IF l_expense_class_dtl_id IS NOT NULL THEN
3513 l_updcondition :=
3514 (l_area_class_id = expense_class_rec.area_class_id AND
3515 (l_mst_fee_bf_contr = expense_class_rec.class_fee_before_contr OR
3516 (l_mst_fee_bf_contr IS NULL AND expense_class_rec.class_fee_before_contr IS NULL)) AND
3517 (l_fee_af_contr = expense_class_rec.class_fee_after_contr OR
3518 (l_fee_af_contr IS NULL AND expense_class_rec.class_fee_after_contr IS NULL)) AND
3519 (l_portion_pct = expense_class_rec.portion_pct OR
3520 (l_portion_pct IS NULL AND expense_class_rec.portion_pct IS NULL)));
3521
3522 IF NOT l_updcondition OR l_updcondition IS NULL THEN
3523
3524 l_area_class_id := expense_class_rec.area_class_id;
3525 l_mst_fee_bf_contr := expense_class_rec.class_fee_before_contr;
3526 l_fee_af_contr := expense_class_rec.class_fee_after_contr;
3527 l_portion_pct := expense_class_rec.portion_pct;
3528
3529 pn_rec_expcl_dtl_pkg.update_row(
3530 x_expense_class_id => p_expense_class_id,
3531 x_expense_line_id => p_expense_line_id,
3532 x_expense_class_dtl_id => l_expense_class_dtl_id,
3533 x_status => 'OPEN',
3534 x_def_area_cls_id => l_area_class_id,
3535 x_cls_line_fee_bf_ct => l_mst_fee_bf_contr,
3536 x_cls_line_fee_af_ct => l_fee_af_contr,
3537 x_cls_line_portion_pct => l_portion_pct,
3538 x_last_update_date => SYSDATE,
3539 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
3540 x_creation_date => SYSDATE,
3541 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
3542 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
3543 );
3544
3545 END IF;
3546
3547 ELSIF l_expense_class_dtl_id IS NULL THEN
3548
3549 l_area_class_id := expense_class_rec.area_class_id;
3550 l_mst_fee_bf_contr := expense_class_rec.class_fee_before_contr;
3551 l_fee_af_contr := expense_class_rec.class_fee_after_contr;
3552 l_portion_pct := expense_class_rec.portion_pct;
3553
3554 pn_rec_expcl_dtl_pkg.insert_row(
3555 x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
3556 x_expense_class_id => p_expense_class_id,
3557 x_expense_line_id => p_expense_line_id,
3558 x_expense_class_dtl_id => l_expense_class_dtl_id,
3559 x_status => 'OPEN',
3560 x_def_area_cls_id => l_area_class_id,
3561 x_cls_line_fee_bf_ct => l_mst_fee_bf_contr,
3562 x_cls_line_fee_af_ct => l_fee_af_contr,
3563 x_cls_line_portion_pct => l_portion_pct,
3564 x_last_update_date => SYSDATE,
3565 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
3566 x_creation_date => SYSDATE,
3567 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
3568 x_last_update_login => nvl(fnd_profile.value('USER_ID'),-1)
3569 );
3570
3571 END IF;
3572
3573 FOR expense_line_rec IN get_exp_lines_info(expense_class_rec.expense_type_code) LOOP
3574
3575 FOR space_assign_rec IN get_cust_assignment_info(
3576 expense_line_rec.location_id,
3577 expense_line_rec.property_id,
3578 expense_class_rec.recovery_space_std_code,
3579 expense_class_rec.recovery_type_code) LOOP
3580
3581 IF expense_line_rec.expense_line_indicator <> 'PARENT' THEN
3582
3583 l_info := ' trying to find override values for a given line ';
3584 pnp_debug_pkg.log(l_info);
3585
3586 l_found := FALSE;
3587
3588 IF l_regenerate = 'Y' THEN
3589
3590 l_info := ' trying to find override values from regenerated extract';
3591 pnp_debug_pkg.log(l_info);
3592
3593 find_expense_ovr_values(
3594 p_master_ovr => exp_cls_curnt_master_ovr,
3595 p_detail_ovr => exp_cls_curnt_detail_ovr,
3596 p_exp_cls_line_dtl_id => l_expense_cls_line_dtl_id,
3597 p_exp_cls_line_id => l_expense_cls_line_id,
3598 p_exp_type => expense_class_rec.expense_type_code,
3599 p_exp_acct => expense_line_rec.expense_account_id,
3600 p_loc_id => space_assign_rec.location_id,
3601 p_cust_id => space_assign_rec.cust_account_id,
3602 p_rec_spc_std => expense_class_rec.recovery_space_std_code,
3603 p_rec_type => expense_class_rec.recovery_type_code,
3604 p_fee_af_contr_ovr => l_fee_af_contr_ovr,
3605 p_mst_share_pct_ovr => l_mst_share_pct_ovr,
3606 p_dtl_share_pct_ovr => l_dtl_share_pct_ovr,
3607 p_mst_fee_bf_contr_ovr => l_mst_fee_bf_contr_ovr,
3608 p_dtl_fee_bf_contr_ovr => l_dtl_fee_bf_contr_ovr,
3609 p_found => l_found,
3610 p_keep_override => p_keep_override
3611 );
3612
3613 END IF;
3614
3615 IF NOT l_found THEN
3616
3617 l_info := ' trying to find override values from prior extract';
3618 pnp_debug_pkg.log(l_info);
3619
3620 IF l_regenerate = 'N' THEN l_found := null; END IF;
3621
3622 find_expense_ovr_values(
3623 p_master_ovr => exp_cls_prior_master_ovr,
3624 p_detail_ovr => exp_cls_prior_detail_ovr,
3625 p_exp_cls_line_dtl_id => l_expense_cls_line_dtl_id,
3626 p_exp_cls_line_id => l_expense_cls_line_id,
3627 p_exp_type => expense_class_rec.expense_type_code,
3628 p_exp_acct => expense_line_rec.expense_account_id,
3629 p_loc_id => space_assign_rec.location_id,
3630 p_cust_id => space_assign_rec.cust_account_id,
3631 p_rec_spc_std => expense_class_rec.recovery_space_std_code,
3632 p_rec_type => expense_class_rec.recovery_type_code,
3633 p_fee_af_contr_ovr => l_fee_af_contr_ovr,
3634 p_mst_share_pct_ovr => l_mst_share_pct_ovr,
3635 p_dtl_share_pct_ovr => l_dtl_share_pct_ovr,
3636 p_mst_fee_bf_contr_ovr => l_mst_fee_bf_contr_ovr,
3637 p_dtl_fee_bf_contr_ovr => l_dtl_fee_bf_contr_ovr,
3638 p_found => l_found,
3639 p_keep_override => p_keep_override
3640 );
3641
3642 END IF;
3643
3644 l_info := ' calculating recovery amount and computed recovery amount ';
3645 pnp_debug_pkg.log(l_info);
3646
3647 l_recov_amount := expense_line_rec.actual_amount * nvl(expense_class_rec.portion_pct,100) / 100;
3648 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);
3649
3650 l_info:= ' collecting master class-line data for space assignment '||space_assign_rec.cust_space_assign_id||' ';
3651 pnp_debug_pkg.log(l_info);
3652
3653 process_exp_cls_dtl_mst_data(
3654 p_master_data => exp_cls_line_master_data,
3655 p_ovr_use_data => l_ovr_use_table,
3656 p_fee_use_table => l_fee_use_table,
3657 p_share_use_table => l_share_use_table,
3658 p_master_data_id => l_master_data_id,
3659 p_expense_class_line_id => l_expense_cls_line_id,
3660 p_expense_class_dtl_id => l_expense_class_dtl_id,
3661 p_location_id => space_assign_rec.location_id,
3662 p_cust_space_assign_id => space_assign_rec.cust_space_assign_id,
3663 p_cust_account_id => space_assign_rec.cust_account_id,
3664 p_lease_id => space_assign_rec.lease_id,
3665 p_recovery_space_std_code => expense_class_rec.recovery_space_std_code,
3666 p_recovery_type_code => expense_class_rec.recovery_type_code,
3667 p_budget_amount => expense_line_rec.budgeted_amount,
3668 p_expense_amount => expense_line_rec.actual_amount,
3669 p_recoverable_amount => l_recov_amount,
3670 p_cpt_recoverable_amount => l_cpt_recov_amount,
3671 p_cls_line_share_pct => l_mst_share_pct_ovr,
3672 p_cls_line_fee_af_contr_ovr => l_fee_af_contr_ovr,
3673 p_cls_line_fee_bf_contr_ovr => l_mst_fee_bf_contr_ovr,
3674 p_use_fee_bf_contr => expense_class_rec.cls_incl_fee_before_contr,
3675 p_use_share_pct => expense_class_rec.cls_incl_share_pct,
3676 p_use_prior_ovr => (NOT l_found AND l_regenerate = 'Y')
3677 );
3678
3679 l_info:= ' collecting detail class-line data for space assignment '||space_assign_rec.cust_space_assign_id;
3680 pnp_debug_pkg.log(l_info);
3681
3682 process_exp_cls_dtl_dtl_data(
3683 p_detail_data => exp_cls_line_detail_data,
3684 p_master_data_id => l_master_data_id,
3685 p_expense_class_line_dtl_id => l_expense_cls_line_dtl_id,
3686 p_expense_line_dtl_id => expense_line_rec.expense_line_dtl_id,
3687 p_expense_account_id => expense_line_rec.expense_account_id,
3688 p_expense_type_code => expense_line_rec.expense_type_code,
3689 p_expense_amount => expense_line_rec.actual_amount,
3690 p_budget_amount => expense_line_rec.budgeted_amount,
3691 p_recoverable_amount => l_recov_amount,
3692 p_cpt_recoverable_amount => l_cpt_recov_amount,
3693 p_cls_line_shr_pct => expense_class_rec.cls_incl_share_pct,
3694 p_cls_line_fee_bf_contr => expense_class_rec.cls_incl_fee_before_contr,
3695 p_cls_line_shr_pct_ovr => l_dtl_share_pct_ovr,
3696 p_cls_line_fee_bf_contr_ovr => l_dtl_fee_bf_contr_ovr
3697 );
3698
3699 END IF;
3700
3701 END LOOP;
3702 END LOOP;
3703 END LOOP;
3704
3705 l_info := ' dumping data into table ';
3706 pnp_debug_pkg.log(l_info);
3707
3708 process_exp_class_line_data(
3709 p_old_detail_data => exp_cls_curnt_detail_ovr,
3710 p_old_master_data => exp_cls_curnt_master_ovr,
3711 p_detail_data => exp_cls_line_detail_data,
3712 p_master_data => exp_cls_line_master_data,
3713 p_fee_use_table => l_fee_use_table,
3714 p_share_use_table => l_share_use_table,
3715 p_default_fee_bf => l_mst_fee_bf_contr
3716 );
3717
3718 IF l_area_class_id IS NOT NULL THEN
3719 l_info := ' generating area class detail associated with the expense class ';
3720 pnp_debug_pkg.log(l_info);
3721
3722 extract_area(
3723 errbuf => l_dummy,
3724 retcode => l_dummy,
3725 p_area_class_id => l_area_class_id,
3726 p_as_of_date => p_as_of_date,
3727 p_from_date => p_from_date,
3728 p_to_date => p_to_date,
3729 p_keep_override => p_keep_override);
3730 END IF;
3731 pnp_debug_pkg.log(l_desc ||' (-)');
3732
3733 EXCEPTION
3734 WHEN currency_exception THEN
3735 pnp_debug_pkg.put_log_msg(fnd_message.get);
3736 raise;
3737 WHEN OTHERS THEN
3738 fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
3739 pnp_debug_pkg.put_log_msg(fnd_message.get);
3740 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3741 raise;
3742 END extract_expense;
3743
3744
3745 -------------------------- MAIN EXTRACTION ----------------------------------+
3746
3747 ------------------------------------------------------------------------------+
3748 -- PROCEDURE : extract_line_expense_area
3749 -- DESCRIPTION: main extraction program
3750 -- o check / validation of inputs.
3751 -- o go to interface table and fetch 'new' data only.
3752 -- 'new' => unique combination of expense type and account id for one extract.
3753 -- o create exp line header if it doesn't exist, and create corresponding exp
3754 -- line detail.
3755 -- o if populate detail set to 'Y' then:
3756 -- oo find exp classes above the specified location, and call the expense
3757 -- extract procedure for each one of them.
3758 -- oo find area classes above the specified location, and call the area extract
3759 -- procedure for each one of them.
3760 --
3761 -- HISTORY:
3762 -- 19-MAR-03 ftanudja o created
3763 -- 28-APR-03 ftanudja o split up p_pop_cls_dtl param into area and exp.
3764 -- 13-JUN-03 ftanudja o incorporated messages for input validation errors.
3765 -- 15-JUL-03 ftanudja o fixed main extraction program to not throw error
3766 -- when check_extr_code returns FALSE when called
3767 -- from rec exp line UI (p_called_from <> 'SRS').
3768 -- 15-JUL-05 SatyaDeepo Replaced base views with their _ALL tables
3769 ------------------------------------------------------------------------------+
3770
3771 PROCEDURE extract_line_expense_area(
3772 errbuf OUT NOCOPY VARCHAR2,
3773 retcode OUT NOCOPY VARCHAR2,
3774 p_location_code IN pn_locations.location_code%TYPE,
3775 p_property_code IN pn_properties.property_code%TYPE,
3776 p_as_of_date IN VARCHAR2,
3777 p_from_date IN VARCHAR2,
3778 p_to_date IN VARCHAR2,
3779 p_currency_code IN pn_rec_exp_line.currency_code%TYPE,
3780 p_pop_exp_class_dtl IN VARCHAR2,
3781 p_pop_area_class_dtl IN VARCHAR2,
3782 p_keep_override IN VARCHAR2,
3783 p_extract_code IN pn_rec_exp_line.expense_extract_code%TYPE,
3784 p_called_from IN VARCHAR2)
3785 IS
3786 l_as_of_date DATE;
3787 l_from_date DATE;
3788 l_to_date DATE;
3789 l_currency pn_rec_exp_line.currency_code%TYPE;
3790 l_info VARCHAR2(300);
3791 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.extract_line_expense_area' ;
3792 l_err VARCHAR2(100);
3793 l_location_id pn_locations.location_id%TYPE;
3794 l_property_id pn_locations.property_id%TYPE;
3795 l_extract_code pn_rec_exp_line.expense_extract_code%TYPE;
3796
3797 CURSOR derive_loc_id_from_loc_code IS
3798 SELECT location_id, property_id
3799 FROM pn_locations
3800 WHERE location_code = p_location_code
3801 AND rownum = 1;
3802
3803 CURSOR derive_prop_id_from_prop_code IS
3804 SELECT property_id
3805 FROM pn_properties
3806 WHERE property_code = p_property_code;
3807
3808 CURSOR get_functional_currency_code IS
3809 SELECT currency_code
3810 FROM gl_sets_of_books
3811 WHERE set_of_books_id = TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
3812 pn_mo_cache_utils.get_current_org_id));
3813
3814 BEGIN
3815
3816 pnp_debug_pkg.log(l_desc ||' (+)');
3817
3818 l_info := ' validating inputs ';
3819 pnp_debug_pkg.log(l_info);
3820
3821 l_from_date := fnd_date.canonical_to_date(p_from_date);
3822 l_to_date := fnd_date.canonical_to_date(p_to_date);
3823 l_as_of_date:= fnd_date.canonical_to_date(p_as_of_date);
3824 l_extract_code := p_extract_code;
3825
3826 IF p_location_code IS NOT NULL THEN
3827 FOR loc_rec IN derive_loc_id_from_loc_code LOOP
3828 l_location_id := loc_rec.location_id;
3829 l_property_id := loc_rec.property_id;
3830 END LOOP;
3831 ELSIF p_property_code IS NOT NULL THEN
3832 FOR prop_rec IN derive_prop_id_from_prop_code LOOP l_property_id := prop_rec.property_id;
3833 END LOOP;
3834 END IF;
3835
3836 IF p_currency_code IS NULL THEN
3837 FOR currency_rec IN get_functional_currency_code LOOP l_currency:= currency_rec.currency_code; END LOOP;
3838 ELSE
3839 l_currency:= p_currency_code;
3840 END IF;
3841
3842 l_info := ' performing input validation ';
3843 pnp_debug_pkg.log(l_info);
3844
3845 IF NOT (l_location_id IS NOT NULL OR l_property_id IS NOT NULL) THEN
3846 fnd_message.set_name('PN','PN_LOC_PROP_REQ');
3847 raise bad_input_exception;
3848
3849 ELSIF NOT check_extract_code(p_extract_code, l_location_id, l_property_id,
3850 l_as_of_date, l_from_date, l_to_date, l_currency) THEN
3851
3852 IF p_extract_code IS NOT NULL AND
3853 p_called_from = 'SRS' THEN
3854
3855 fnd_message.set_name('PN','PN_REC_NONUNIQUE_NUM');
3856 fnd_message.set_token('NUMBER', p_extract_code);
3857 raise bad_input_exception;
3858
3859 ELSIF p_extract_code IS NULL AND
3860 pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_REC_EXPENSE_NUM',
3861 pn_mo_cache_utils.get_current_org_id) = 'N'
3862 THEN
3863
3864 fnd_message.set_name('PN','PN_REC_EXP_NUM_REQ');
3865 raise bad_input_exception;
3866
3867 END IF;
3868
3869 ELSIF NOT check_dates(l_as_of_date, l_from_date, l_to_date, l_location_id, l_property_id, p_extract_code) THEN
3870 fnd_message.set_name('PN','PN_REC_EXT_DT_OVERLAP');
3871 raise bad_input_exception;
3872
3873 ELSIF l_currency IS NULL THEN
3874 app_exception.raise_exception;
3875
3876 END IF;
3877
3878 IF p_called_from = 'SRS' THEN
3879 l_info:= ' performing extraction';
3880 pnp_debug_pkg.log(l_info);
3881
3882 extract_expense_lines(
3883 p_location_id => l_location_id,
3884 p_property_id => l_property_id,
3885 p_as_of_date => l_as_of_date,
3886 p_from_date => l_from_date,
3887 p_to_date => l_to_date,
3888 p_currency_code => l_currency,
3889 p_extract_code => l_extract_code,
3890 p_keep_override => p_keep_override
3891 );
3892 END IF;
3893
3894 IF p_pop_exp_class_dtl = 'Y' THEN
3895 populate_expense_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_extract_code => l_extract_code,
3902 p_keep_override => p_keep_override
3903 );
3904 END IF;
3905
3906 IF p_pop_area_class_dtl = 'Y' THEN
3907 populate_area_class_details(
3908 p_location_id => l_location_id,
3909 p_property_id => l_property_id,
3910 p_as_of_date => p_as_of_date,
3911 p_from_date => p_from_date,
3912 p_to_date => p_to_date,
3913 p_keep_override => p_keep_override
3914 );
3915 END IF;
3916
3917 pnp_debug_pkg.log(l_desc ||' (-)');
3918
3919 EXCEPTION
3920 WHEN bad_input_exception THEN
3921 pnp_debug_pkg.log(fnd_message.get);
3922 raise;
3923 WHEN OTHERS THEN
3924 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3925 raise;
3926 END extract_line_expense_area;
3927
3928 ------------------------------------------------------------------------------+
3929 -- PROCEDURE : purge_expense_lines_itf_data
3930 -- ASSUMES : UI validates location code, property code and expense extr code
3931 -- DESCRIPTION:
3932 -- 1. Purges data from interface table based on the given parameters.
3933 -- 2. Used dbms_SQL to build the query.
3934 --
3935 -- HISTORY:
3936 -- 18-APR-03 ftanudja o created
3937 -- 24-MAY-04 ftanudja o fixed from date / to date logic
3938 -- o move logic for delete_all_flag = 'Y' to top.
3939 -- 15-JUL-05 sdnahesh o Replaced base views with their _ALL tables
3940 -- 27-OCT-05 sdmahesh o ATG Mandated changes for SQL literals
3941 ------------------------------------------------------------------------------+
3942
3943 PROCEDURE purge_expense_lines_itf_data(
3944 errbuf OUT NOCOPY VARCHAR2,
3945 retcode OUT NOCOPY VARCHAR2,
3946 p_extract_code IN pn_rec_exp_line.expense_extract_code%TYPE,
3947 p_location_code IN pn_locations.location_code%TYPE,
3948 p_property_code IN pn_properties.property_code%TYPE,
3949 p_from_date IN VARCHAR2,
3950 p_to_date IN VARCHAR2,
3951 p_transfer_flag IN pn_rec_exp_itf.transfer_flag%TYPE,
3952 p_delete_all_flag IN VARCHAR2)
3953 IS
3954
3955 CURSOR derive_loc_id_from_loc_code IS
3956 SELECT location_id
3957 FROM pn_locations
3958 WHERE location_code = p_location_code;
3959
3960 CURSOR derive_prop_id_from_prop_code IS
3961 SELECT property_id
3962 FROM pn_properties
3963 WHERE property_code = p_property_code;
3964
3965 l_loc_id pn_locations.location_id%TYPE;
3966 l_prop_id pn_locations.property_id%TYPE;
3967 l_sqlhead VARCHAR2(300);
3968 l_sqltail VARCHAR2(900) := null;
3969 l_info VARCHAR2(300);
3970 l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.purge_expense_lines_itf_data' ;
3971 l_extract_code pn_rec_exp_line.expense_extract_code%TYPE;
3972 l_transfer_flag pn_rec_exp_itf.transfer_flag%TYPE;
3973 l_from_date DATE;
3974 l_to_date DATE;
3975 l_statement VARCHAR2(5000);
3976 l_cursor INTEGER;
3977 l_rows INTEGER;
3978 l_count INTEGER;
3979
3980 BEGIN
3981
3982 pnp_debug_pkg.log(l_desc ||' (+)');
3983
3984 IF p_delete_all_flag = 'Y' THEN
3985 l_info := ' purging everything ';
3986 pnp_debug_pkg.log(l_info);
3987 DELETE pn_rec_exp_itf;
3988 return;
3989 END IF;
3990 pnp_debug_pkg.log('p_extract_code='||p_extract_code);
3991 l_cursor := dbms_sql.open_cursor;
3992 l_sqlhead := 'DELETE pn_rec_exp_itf WHERE ';
3993
3994 l_info := ' figuring expense extract code';
3995 pnp_debug_pkg.log(l_info);
3996 l_extract_code := p_extract_code;
3997 l_transfer_flag := p_transfer_flag;
3998 l_from_date := fnd_date.canonical_to_date(p_from_date);
3999 l_to_date := fnd_date.canonical_to_date(p_to_date);
4000
4001
4002 IF p_extract_code IS NOT NULL THEN
4003
4004 l_sqltail := ' expense_line_dtl_id IN ' ||
4005 '(SELECT dtl.expense_line_dtl_id ' ||
4006 ' FROM pn_rec_exp_line_all hdr, pn_rec_exp_line_dtl_all dtl ' ||
4007 ' WHERE hdr.expense_line_id = dtl.expense_line_id ' ||
4008 ' AND hdr.expense_extract_code = :l_extract_code)';
4009
4010 END IF;
4011
4012 l_info := ' figuring transfer flag';
4013 pnp_debug_pkg.log(l_info);
4014
4015 IF p_transfer_flag IS NOT NULL THEN
4016 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4017 l_sqltail := l_sqltail || ' transfer_flag = :l_transfer_flag';
4018 END IF;
4019
4020 l_info := ' figuring from date';
4021 pnp_debug_pkg.log(l_info);
4022
4023 IF p_from_date IS NOT NULL THEN
4024 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4025 l_sqltail := l_sqltail ||' from_date >= :l_from_date)';
4026
4027 END IF;
4028
4029 l_info := ' figuring to date';
4030 pnp_debug_pkg.log(l_info);
4031
4032 IF p_to_date IS NOT NULL THEN
4033 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4034 l_sqltail := l_sqltail ||' to_date <= :l_to_date)';
4035
4036 END IF;
4037
4038 l_info := ' figuring location code';
4039 pnp_debug_pkg.log(l_info);
4040
4041 IF p_location_code IS NOT NULL THEN
4042 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4043 FOR loc_rec IN derive_loc_id_from_loc_code LOOP l_loc_id := loc_rec.location_id; END LOOP;
4044 l_sqltail := l_sqltail || ' location_id = :l_loc_id';
4045
4046 END IF;
4047
4048 l_info := ' figuring property code';
4049 pnp_debug_pkg.log(l_info);
4050
4051 IF p_property_code IS NOT NULL THEN
4052 IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4053 FOR prop_rec IN derive_prop_id_from_prop_code LOOP l_prop_id := prop_rec.property_id; END LOOP;
4054 l_sqltail := l_sqltail || ' property_id = :l_prop_id';
4055
4056 END IF;
4057
4058
4059
4060 IF l_sqltail IS NOT NULL THEN
4061
4062 l_info := ' deleting using dynamic SQL';
4063 pnp_debug_pkg.log(l_info);
4064 pnp_debug_pkg.log('');
4065 pnp_debug_pkg.log(l_sqlhead);
4066 pnp_debug_pkg.log(l_sqltail);
4067 pnp_debug_pkg.log('');
4068
4069 l_statement := l_sqlhead || l_sqltail;
4070 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
4071 IF p_extract_code IS NOT NULL THEN
4072 dbms_sql.bind_variable(l_cursor,'l_extract_code',l_extract_code);
4073 END IF;
4074
4075 IF p_transfer_flag IS NOT NULL THEN
4076 dbms_sql.bind_variable(l_cursor,'l_transfer_flag',l_transfer_flag);
4077 END IF;
4078
4079 IF p_from_date IS NOT NULL THEN
4080 dbms_sql.bind_variable(l_cursor,'l_from_date',l_from_date);
4081 END IF;
4082
4083 IF p_to_date IS NOT NULL THEN
4084 dbms_sql.bind_variable(l_cursor,'l_to_date',l_to_date);
4085 END IF;
4086
4087 IF p_location_code IS NOT NULL THEN
4088 dbms_sql.bind_variable(l_cursor,'l_loc_id',l_loc_id);
4089 END IF;
4090
4091 IF p_property_code IS NOT NULL THEN
4092 dbms_sql.bind_variable(l_cursor,'l_prop_id',l_prop_id);
4093 END IF;
4094 l_rows := dbms_sql.execute(l_cursor);
4095
4096
4097 END IF;
4098
4099 IF dbms_sql.is_open (l_cursor) THEN
4100 dbms_sql.close_cursor (l_cursor);
4101 END IF;
4102 pnp_debug_pkg.log(l_desc ||' (-)');
4103
4104 EXCEPTION
4105 WHEN OTHERS THEN
4106 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
4107 raise;
4108 END;
4109
4110 END pn_recovery_extract_pkg;