[Home] [Help]
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:
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
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:
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:
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
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
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;
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);
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: ------------------------------------------------------------------------------+
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;
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:
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);
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:
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:
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;
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
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';
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);
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: ------------------------------------------------------------------------------+
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);
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;
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);
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: ------------------------------------------------------------------------------+
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);
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
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;
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;
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);
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: ------------------------------------------------------------------------------+
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:
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,
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,
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);
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: ------------------------------------------------------------------------------+
1089:
1090:
1091: BEGIN
1092:
1093: pnp_debug_pkg.log(l_desc ||' (+)');
1094:
1095: fnd_message.set_name('PN','PN_REC_ARCL_DTL_CP_INFO');
1096: fnd_message.set_token('ARCL' , to_char(p_area_class_id));
1097: fnd_message.set_token('STR' , p_from_date);
1097: fnd_message.set_token('STR' , p_from_date);
1098: fnd_message.set_token('END' , p_to_date);
1099: fnd_message.set_token('AOD' , p_as_of_date);
1100: fnd_message.set_token('OVR' , p_keep_override);
1101: pnp_debug_pkg.put_log_msg('');
1102: pnp_debug_pkg.put_log_msg(fnd_message.get);
1103: pnp_debug_pkg.put_log_msg('');
1104:
1105: l_vacancy_date_table.delete;
1098: fnd_message.set_token('END' , p_to_date);
1099: fnd_message.set_token('AOD' , p_as_of_date);
1100: fnd_message.set_token('OVR' , p_keep_override);
1101: pnp_debug_pkg.put_log_msg('');
1102: pnp_debug_pkg.put_log_msg(fnd_message.get);
1103: pnp_debug_pkg.put_log_msg('');
1104:
1105: l_vacancy_date_table.delete;
1106: l_vacancy_num_table.delete;
1099: fnd_message.set_token('AOD' , p_as_of_date);
1100: fnd_message.set_token('OVR' , p_keep_override);
1101: pnp_debug_pkg.put_log_msg('');
1102: pnp_debug_pkg.put_log_msg(fnd_message.get);
1103: pnp_debug_pkg.put_log_msg('');
1104:
1105: l_vacancy_date_table.delete;
1106: l_vacancy_num_table.delete;
1107: l_ref_vacancy_date_table.delete;
1124: l_area_total_tbl(0).total_weighted_avg_ovr := 0;
1125: l_area_total_tbl(0).total_weighted_avg_exc := 0;
1126:
1127: l_info:= ' fetching area class information';
1128: pnp_debug_pkg.log(l_info);
1129:
1130: FOR area_class_rec IN get_area_class_info LOOP
1131: IF l_arcl_locid IS NULL THEN l_arcl_locid := area_class_rec.location_id; END IF;
1132: IF l_arcl_propid IS NULL THEN l_arcl_propid := area_class_rec.property_id; END IF;
1141:
1142: END LOOP;
1143:
1144: l_info := ' validating UOM is unique for location and property';
1145: pnp_debug_pkg.log(l_info);
1146:
1147: IF l_arcl_propid IS NOT NULL AND l_arcl_locid IS NULL THEN
1148: FOR validate_rec IN get_uom_info_from_prop(l_arcl_propid) LOOP
1149: IF l_uom_code IS NULL THEN l_uom_code := validate_rec.uom_code;
1162: END LOOP;
1163: END IF;
1164:
1165: l_info := ' finding overrides and processing header information ';
1166: pnp_debug_pkg.log(l_info);
1167:
1168: l_regenerate := 'Y';
1169: l_count := -1;
1170:
1178: fnd_message.set_token('FDATE', p_from_date);
1179: fnd_message.set_token('TDATE', p_to_date);
1180: fnd_message.set_token('AODATE', p_as_of_date);
1181: fnd_message.set_token('NAME', get_ovr_rec.area_class_name);
1182: pnp_debug_pkg.log(fnd_message.get);
1183: RETURN;
1184: END IF;
1185:
1186: l_count := l_area_cls_ln_curnt_ovr.COUNT;
1209:
1210: END IF;
1211:
1212: l_info := ' getting prior cls dtl id for overrides';
1213: pnp_debug_pkg.log(l_info);
1214:
1215: FOR get_first_id IN get_prior_cls_dtl_id LOOP
1216: l_dummy_id := get_first_id.area_class_dtl_id;
1217: exit;
1239:
1240: FOR location_rec IN get_location_info(l_arcl_locid, l_arcl_propid) LOOP
1241:
1242: l_info := ' processing location id: '|| location_rec.location_id ||' ';
1243: pnp_debug_pkg.log(l_info);
1244:
1245: l_is_assigned := FALSE;
1246:
1247: IF l_temp_loc_id IS NULL OR
1251:
1252: -- generate vacancy data for that location id
1253:
1254: l_info := ' inserting vacancy data into details table for location: '||l_temp_loc_id||' ';
1255: pnp_debug_pkg.log(l_info);
1256:
1257: insert_vacancy_data(p_location_id => l_temp_loc_id,
1258: p_property_id => l_temp_prop_id,
1259: p_date_table => l_vacancy_date_table,
1291: l_is_assigned := TRUE;
1292:
1293: l_info := ' checking whether space assignment: '|| space_assign_rec.cust_space_assign_id ||
1294: ' meets exclusion criteria ';
1295: pnp_debug_pkg.log(l_info);
1296:
1297: l_meets_criteria := FALSE;
1298:
1299: FOR i IN 0 .. l_arcl_exc_table.COUNT - 1 LOOP
1324:
1325: l_from_date := space_assign_rec.cust_assign_start_date;
1326: l_to_date := space_assign_rec.fin_oblig_end_date;
1327: l_info := ' getting details for cust assignment :'||space_assign_rec.cust_space_assign_id||' ';
1328: pnp_debug_pkg.log(l_info);
1329:
1330: IF NOT l_meets_criteria THEN l_excl_type := null; END IF;
1331:
1332: get_area_cls_dtl_calc(
1345: p_include_flag => l_include_flag);
1346:
1347: l_info := ' finding overrides and processing into pl/sql table the details of '||
1348: 'cust assignment:'||space_assign_rec.cust_space_assign_id||' ';
1349: pnp_debug_pkg.log(l_info);
1350:
1351: l_found := FALSE;
1352:
1353: IF l_regenerate = 'Y' THEN
1401:
1402: IF l_include_flag = 'Y' AND nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'N' THEN
1403:
1404: l_info := ' figuring totals ';
1405: pnp_debug_pkg.log(l_info);
1406:
1407: l_area_total_tbl(0).total_assignable_area := l_area_total_tbl(0).total_assignable_area +
1408: nvl(location_rec.assignable_area,0);
1409: l_area_total_tbl(0).total_occupied_area := l_area_total_tbl(0).total_occupied_area + nvl(l_occup_area,0);
1420:
1421: ELSIF l_include_flag = 'Y' AND nvl(l_excl_area_ovr_flag, l_excl_area_flag) = 'Y' THEN
1422:
1423: l_info := ' figuring excluded totals ';
1424: pnp_debug_pkg.log(l_info);
1425:
1426: l_area_total_tbl(0).total_occupied_area_exc := l_area_total_tbl(0).total_occupied_area_exc +
1427: nvl(nvl(l_occup_area_ovr, l_occup_area),0);
1428: l_area_total_tbl(0).total_vacant_area_exc := l_area_total_tbl(0).total_vacant_area_exc +
1433:
1434: END IF;
1435:
1436: l_info := ' processing area cls detail information into pl/sql table ';
1437: pnp_debug_pkg.log(l_info);
1438:
1439: l_area_cls_ln_data_tbl(l_data_tbl_counter).area_class_dtl_line_id := l_area_class_dtl_line_id;
1440: l_area_cls_ln_data_tbl(l_data_tbl_counter).from_date := l_from_date;
1441: l_area_cls_ln_data_tbl(l_data_tbl_counter).to_date := l_to_date;
1464:
1465: -- collect data
1466:
1467: l_info := ' processing vacancy for cust assignment:'||space_assign_rec.cust_space_assign_id||' ';
1468: pnp_debug_pkg.log(l_info);
1469:
1470: process_vacancy(
1471: p_start_date => l_from_date,
1472: p_end_date => l_to_date,
1477:
1478: END LOOP;
1479:
1480: l_info := ' processing vacancy for location for data table';
1481: pnp_debug_pkg.log(l_info);
1482:
1483: process_vacancy(
1484: p_start_date => location_rec.active_start_date,
1485: p_end_date => location_rec.active_end_date,
1488: p_number_table => l_vacancy_num_table,
1489: p_add => TRUE);
1490:
1491: l_info := ' processing vacancy for location for reference table';
1492: pnp_debug_pkg.log(l_info);
1493:
1494: process_vacancy(
1495: p_start_date => location_rec.active_start_date,
1496: p_end_date => location_rec.active_end_date,
1503:
1504: END LOOP;
1505:
1506: l_info := ' inserting vacancy data for last location id';
1507: pnp_debug_pkg.log(l_info);
1508:
1509: insert_vacancy_data(
1510: p_location_id => l_temp_loc_id,
1511: p_property_id => l_temp_prop_id,
1532: l_ref_vacancy_date_table.delete;
1533: l_ref_vacancy_num_table.delete;
1534:
1535: l_info := ' processing header data ';
1536: pnp_debug_pkg.log(l_info);
1537:
1538: IF l_area_class_dtl_id IS NOT NULL THEN
1539:
1540: pn_rec_arcl_dtl_pkg.update_row(
1587: );
1588: END IF;
1589:
1590: l_info := ' dumping data from pl/sql table ';
1591: pnp_debug_pkg.log(l_info);
1592:
1593: process_area_class_line_data(
1594: p_old_data => l_area_cls_ln_curnt_ovr,
1595: p_new_data => l_area_cls_ln_data_tbl,
1595: p_new_data => l_area_cls_ln_data_tbl,
1596: p_hdr_id => l_area_class_dtl_id
1597: );
1598:
1599: pnp_debug_pkg.log(l_desc ||' (-)');
1600:
1601: EXCEPTION
1602: WHEN uom_exception THEN
1603: pnp_debug_pkg.log(fnd_message.get);
1599: pnp_debug_pkg.log(l_desc ||' (-)');
1600:
1601: EXCEPTION
1602: WHEN uom_exception THEN
1603: pnp_debug_pkg.log(fnd_message.get);
1604: raise;
1605: WHEN OTHERS THEN
1606: fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
1607: pnp_debug_pkg.put_log_msg(fnd_message.get);
1603: pnp_debug_pkg.log(fnd_message.get);
1604: raise;
1605: WHEN OTHERS THEN
1606: fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
1607: pnp_debug_pkg.put_log_msg(fnd_message.get);
1608: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1609: raise;
1610: END extract_area;
1611:
1604: raise;
1605: WHEN OTHERS THEN
1606: fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
1607: pnp_debug_pkg.put_log_msg(fnd_message.get);
1608: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1609: raise;
1610: END extract_area;
1611:
1612: ---------------------------- EXPENSE VALIDATIONS -----------------------------+
1895: l_is_valid BOOLEAN;
1896:
1897: BEGIN
1898:
1899: pnp_debug_pkg.log(l_desc ||' (+)');
1900:
1901: l_is_valid := check_loc_n_prop_id (p_location_id, p_property_id) AND
1902: check_expense_type (p_expense_type_code) AND
1903: check_account_id (p_expense_account_id);
1912:
1913: p_is_header_set := TRUE;
1914:
1915: l_info:= ' inserting expense line header';
1916: pnp_debug_pkg.log(l_info);
1917:
1918: pn_rec_exp_line_pkg.insert_row(
1919: x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
1920: x_expense_line_id => p_expense_line_id,
1934:
1935: END IF;
1936:
1937: l_info:= ' inserting expense line detail for header id: '||p_expense_line_id;
1938: pnp_debug_pkg.log(l_info);
1939:
1940: pn_rec_exp_line_dtl_pkg.insert_row(
1941: x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
1942: x_expense_line_id => p_expense_line_id,
1986: p_transfer_flag := 'E';
1987:
1988: END IF;
1989:
1990: pnp_debug_pkg.log(l_desc ||' (-)');
1991:
1992: EXCEPTION
1993: WHEN OTHERS THEN
1994: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1990: pnp_debug_pkg.log(l_desc ||' (-)');
1991:
1992: EXCEPTION
1993: WHEN OTHERS THEN
1994: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1995: raise;
1996: END;
1997:
1998: ------------------------------------------------------------------------------+
2086: l_failed NUMBER := 0;
2087:
2088: BEGIN
2089:
2090: pnp_debug_pkg.log(l_desc ||' (+)');
2091:
2092: fnd_message.set_name('PN','PN_REC_EXP_LN_CP_INFO');
2093: fnd_message.set_token('LOC' , to_char(p_location_id));
2094: fnd_message.set_token('PROP' , to_char(p_property_id));
2097: fnd_message.set_token('AOD' , to_char(p_as_of_date));
2098: fnd_message.set_token('CUR' , p_currency_code);
2099: fnd_message.set_token('EXPNUM', p_extract_code);
2100: fnd_message.set_token('OVR' , p_keep_override);
2101: pnp_debug_pkg.put_log_msg('');
2102: pnp_debug_pkg.put_log_msg(fnd_message.get);
2103: pnp_debug_pkg.put_log_msg('');
2104:
2105: l_info := ' initializing values';
2098: fnd_message.set_token('CUR' , p_currency_code);
2099: fnd_message.set_token('EXPNUM', p_extract_code);
2100: fnd_message.set_token('OVR' , p_keep_override);
2101: pnp_debug_pkg.put_log_msg('');
2102: pnp_debug_pkg.put_log_msg(fnd_message.get);
2103: pnp_debug_pkg.put_log_msg('');
2104:
2105: l_info := ' initializing values';
2106: pnp_debug_pkg.log(l_info);
2099: fnd_message.set_token('EXPNUM', p_extract_code);
2100: fnd_message.set_token('OVR' , p_keep_override);
2101: pnp_debug_pkg.put_log_msg('');
2102: pnp_debug_pkg.put_log_msg(fnd_message.get);
2103: pnp_debug_pkg.put_log_msg('');
2104:
2105: l_info := ' initializing values';
2106: pnp_debug_pkg.log(l_info);
2107:
2102: pnp_debug_pkg.put_log_msg(fnd_message.get);
2103: pnp_debug_pkg.put_log_msg('');
2104:
2105: l_info := ' initializing values';
2106: pnp_debug_pkg.log(l_info);
2107:
2108: l_expense_line_id := null;
2109: l_is_header_set := FALSE;
2110: l_reextract := FALSE;
2166: commit;
2167: END IF;
2168:
2169: l_info := ' updating interface table ';
2170: pnp_debug_pkg.log(l_info);
2171:
2172: UPDATE pn_rec_exp_itf
2173: SET transfer_flag = l_transfer_flag,
2174: expense_line_dtl_id = l_expense_line_dtl_id
2176:
2177: END LOOP;
2178:
2179: fnd_message.set_name('PN','PN_REC_EXP_LN');
2180: pnp_debug_pkg.put_log_msg('');
2181: pnp_debug_pkg.put_log_msg(fnd_message.get);
2182:
2183: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2184: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2177: END LOOP;
2178:
2179: fnd_message.set_name('PN','PN_REC_EXP_LN');
2180: pnp_debug_pkg.put_log_msg('');
2181: pnp_debug_pkg.put_log_msg(fnd_message.get);
2182:
2183: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2184: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2185: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2183: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2184: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2185: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2186: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2187: pnp_debug_pkg.put_log_msg(fnd_message.get);
2188: pnp_debug_pkg.put_log_msg('');
2189:
2190: pnp_debug_pkg.log(l_desc ||' (-)');
2191:
2184: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2185: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2186: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2187: pnp_debug_pkg.put_log_msg(fnd_message.get);
2188: pnp_debug_pkg.put_log_msg('');
2189:
2190: pnp_debug_pkg.log(l_desc ||' (-)');
2191:
2192: EXCEPTION
2186: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2187: pnp_debug_pkg.put_log_msg(fnd_message.get);
2188: pnp_debug_pkg.put_log_msg('');
2189:
2190: pnp_debug_pkg.log(l_desc ||' (-)');
2191:
2192: EXCEPTION
2193: WHEN OTHERS THEN
2194: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2190: pnp_debug_pkg.log(l_desc ||' (-)');
2191:
2192: EXCEPTION
2193: WHEN OTHERS THEN
2194: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2195: raise;
2196:
2197: END extract_expense_lines;
2198:
2257: temp_rec pn_rec_expcl_dtlln%ROWTYPE;
2258:
2259: BEGIN
2260:
2261: pnp_debug_pkg.log(l_desc ||' (+)');
2262:
2263: p_master_data_id := -1;
2264:
2265: l_info:= ' searching through master table';
2262:
2263: p_master_data_id := -1;
2264:
2265: l_info:= ' searching through master table';
2266: pnp_debug_pkg.log(l_info);
2267:
2268: FOR i IN 0 .. p_master_data.COUNT - 1 LOOP
2269:
2270: IF (p_master_data(i).location_id = p_location_id) AND
2270: IF (p_master_data(i).location_id = p_location_id) AND
2271: (p_master_data(i).cust_account_id = p_cust_account_id) THEN
2272:
2273: l_info:= ' updating values in master data object for location id '||p_location_id||' and customer acct id'|| p_cust_account_id;
2274: pnp_debug_pkg.log(l_info);
2275:
2276: p_master_data(i).budgeted_amt := nvl(p_master_data(i).budgeted_amt, 0) + nvl(p_budget_amount,0);
2277: p_master_data(i).expense_amt := nvl(p_master_data(i).expense_amt, 0) + nvl(p_expense_amount,0);
2278: p_master_data(i).recoverable_amt := nvl(p_master_data(i).recoverable_amt, 0) + nvl(p_recoverable_amount,0);
2299:
2300: p_master_data_id := p_master_data.COUNT;
2301:
2302: l_info:= ' creating new entry in master data object';
2303: pnp_debug_pkg.log(l_info);
2304:
2305: temp_rec.expense_class_dtl_id := p_expense_class_dtl_id;
2306: temp_rec.expense_class_line_id := p_expense_class_line_id;
2307: temp_rec.location_id := p_location_id;
2323: p_ovr_use_data(p_master_data_id) := p_use_prior_ovr;
2324:
2325: END IF;
2326:
2327: pnp_debug_pkg.log(l_desc ||' (-)');
2328:
2329: EXCEPTION
2330: WHEN OTHERS THEN
2331: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2327: pnp_debug_pkg.log(l_desc ||' (-)');
2328:
2329: EXCEPTION
2330: WHEN OTHERS THEN
2331: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2332: raise;
2333: END;
2334:
2335: ------------------------------------------------------------------------------+
2362: l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_cls_dtl_dtl_data' ;
2363:
2364: BEGIN
2365:
2366: pnp_debug_pkg.log(l_desc ||' (+)');
2367:
2368: l_info:= ' inserting into details pl/sql table ';
2369: pnp_debug_pkg.log(l_info);
2370:
2365:
2366: pnp_debug_pkg.log(l_desc ||' (+)');
2367:
2368: l_info:= ' inserting into details pl/sql table ';
2369: pnp_debug_pkg.log(l_info);
2370:
2371: l_id := p_detail_data.COUNT;
2372:
2373: p_detail_data(l_id).expense_class_line_dtl_id := p_expense_class_line_dtl_id;
2383: p_detail_data(l_id).cls_line_dtl_fee_bf_contr := p_cls_line_fee_bf_contr;
2384: p_detail_data(l_id).cls_line_dtl_share_pct_ovr := p_cls_line_shr_pct_ovr;
2385: p_detail_data(l_id).cls_line_dtl_fee_bf_contr_ovr := p_cls_line_fee_bf_contr_ovr;
2386:
2387: pnp_debug_pkg.log(l_desc ||' (-)');
2388:
2389: EXCEPTION
2390: WHEN OTHERS THEN
2391: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2387: pnp_debug_pkg.log(l_desc ||' (-)');
2388:
2389: EXCEPTION
2390: WHEN OTHERS THEN
2391: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2392: raise;
2393: END;
2394:
2395: ------------------------------------------------------------------------------+
2427: l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.process_exp_class_line_data';
2428:
2429: BEGIN
2430:
2431: pnp_debug_pkg.log(l_desc ||' (+)');
2432:
2433: FOR i IN 0 .. p_master_data.COUNT - 1 LOOP
2434:
2435: l_info:= ' figuring out whether values should be used ';
2432:
2433: FOR i IN 0 .. p_master_data.COUNT - 1 LOOP
2434:
2435: l_info:= ' figuring out whether values should be used ';
2436: pnp_debug_pkg.log(l_info);
2437:
2438: l_use_share_pct_flag := 'N';
2439: l_use_fee_pct_flag := 'N';
2440:
2454:
2455: IF l_expense_class_line_id IS NULL THEN
2456:
2457: l_info:= ' inserting data into class lines master table';
2458: pnp_debug_pkg.log(l_info);
2459:
2460: pn_rec_expcl_dtlln_pkg.insert_row(
2461: x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
2462: x_expense_class_dtl_id => p_master_data(i).expense_class_dtl_id,
2487:
2488: ELSE
2489:
2490: l_info:= ' updating data into class lines table, id: ' || l_expense_class_line_id;
2491: pnp_debug_pkg.log(l_info);
2492:
2493: pn_rec_expcl_dtlln_pkg.update_row(
2494: x_expense_class_line_id => l_expense_class_line_id,
2495: x_location_id => p_master_data(i).location_id,
2525:
2526: END LOOP;
2527:
2528: l_info:= ' processing data for class line details table';
2529: pnp_debug_pkg.log(l_info);
2530:
2531: FOR i IN 0 .. p_detail_data.COUNT - 1 LOOP
2532:
2533: l_expense_class_line_dtl_id := p_detail_data(i).expense_class_line_dtl_id;
2535: IF l_expense_class_line_dtl_id IS NULL THEN
2536:
2537: l_info := ' inserting detail data for class line header: '||
2538: p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id;
2539: pnp_debug_pkg.log(l_info);
2540:
2541: pn_rec_expcl_dtlacc_pkg.insert_row(
2542: x_org_id => to_number(pn_mo_cache_utils.get_current_org_id),
2543: x_expense_class_line_id => p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id,
2561: );
2562: ELSE
2563: l_info := ' updating detail data for class line header: '||
2564: p_master_data(p_detail_data(i).expense_class_line_id).expense_class_line_id;
2565: pnp_debug_pkg.log(l_info);
2566:
2567: pn_rec_expcl_dtlacc_pkg.update_row(
2568: x_expense_class_line_dtl_id => l_expense_class_line_dtl_id,
2569: x_expense_line_dtl_id => p_detail_data(i).expense_line_dtl_id,
2590:
2591: END LOOP;
2592:
2593: l_info := ' figuring out which data is unused';
2594: pnp_debug_pkg.log(l_info);
2595:
2596: FOR i IN 0 .. p_old_detail_data.COUNT - 1 LOOP
2597: l_is_in := FALSE;
2598: FOR j IN 0 .. l_detail_keep_table.COUNT - 1 LOOP
2613: END IF;
2614: END LOOP;
2615:
2616: l_info := ' deleting unused data';
2617: pnp_debug_pkg.log(l_info);
2618:
2619: FORALL i IN 0 .. l_detail_delete_table.COUNT - 1
2620: DELETE FROM pn_rec_expcl_dtlacc_all
2621: WHERE expense_class_line_dtl_id = l_detail_delete_table(i);
2624: DELETE FROM pn_rec_expcl_dtlln_all
2625: WHERE expense_class_line_id = l_master_delete_table(i);
2626:
2627:
2628: pnp_debug_pkg.log(l_desc ||' (-)');
2629:
2630: EXCEPTION
2631: WHEN OTHERS THEN
2632: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2628: pnp_debug_pkg.log(l_desc ||' (-)');
2629:
2630: EXCEPTION
2631: WHEN OTHERS THEN
2632: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2633: raise;
2634: END;
2635:
2636: ------------------------------------------------------------------------------+
2688: l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.dismantle_exp_line_from_dtl' ;
2689:
2690: BEGIN
2691:
2692: pnp_debug_pkg.log(l_desc ||' (+)');
2693:
2694: l_info := ' processing affected lines ';
2695: pnp_debug_pkg.log(l_info);
2696:
2691:
2692: pnp_debug_pkg.log(l_desc ||' (+)');
2693:
2694: l_info := ' processing affected lines ';
2695: pnp_debug_pkg.log(l_info);
2696:
2697: FOR class_line_dtl_rec IN get_affected_lines LOOP
2698: DELETE pn_rec_expcl_dtlacc_all
2699: WHERE expense_class_line_dtl_id = class_line_dtl_rec.expense_class_line_dtl_id;
2700: hdr_id_list(hdr_id_list.COUNT) := class_line_dtl_rec.expense_class_line_id;
2701: END LOOP;
2702:
2703: l_info := ' storing amount information ';
2704: pnp_debug_pkg.log(l_info);
2705:
2706: FOR i IN 0 .. hdr_id_list.COUNT - 1 LOOP
2707:
2708: l_update := FALSE;
2708: l_update := FALSE;
2709:
2710: FOR hdr_rec IN get_header_info(hdr_id_list(i)) LOOP
2711: l_info := ' getting update information for expclln id: '||hdr_id_list(i);
2712: pnp_debug_pkg.log(l_info);
2713:
2714: l_update := TRUE;
2715: l_count := update_list.COUNT;
2716: update_list(l_count) := hdr_id_list(i);
2727:
2728: END LOOP;
2729:
2730: l_info := ' updating header information ';
2731: pnp_debug_pkg.log(l_info);
2732:
2733: FORALL i IN 0 .. update_list.COUNT - 1
2734: UPDATE pn_rec_expcl_dtlln_all
2735: SET budgeted_amt = budgeted_list(i),
2743: last_update_login = nvl(fnd_profile.value('USER_ID'), -1)
2744: WHERE expense_class_line_id = update_list(i);
2745:
2746: l_info := ' deleting unused header lines';
2747: pnp_debug_pkg.log(l_info);
2748:
2749: FORALL i IN 0 .. delete_list.COUNT - 1
2750: DELETE pn_rec_expcl_dtlln_all
2751: WHERE expense_class_line_id = delete_list(i);
2749: FORALL i IN 0 .. delete_list.COUNT - 1
2750: DELETE pn_rec_expcl_dtlln_all
2751: WHERE expense_class_line_id = delete_list(i);
2752:
2753: pnp_debug_pkg.log(l_desc ||' (-)');
2754:
2755: EXCEPTION
2756: WHEN OTHERS THEN
2757: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2753: pnp_debug_pkg.log(l_desc ||' (-)');
2754:
2755: EXCEPTION
2756: WHEN OTHERS THEN
2757: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2758: raise;
2759: END;
2760:
2761: ------------------------------------------------------------------------------+
2829: l_total NUMBER := 0;
2830: l_failed NUMBER := 0;
2831:
2832: BEGIN
2833: pnp_debug_pkg.log(l_desc ||' (+)');
2834:
2835: FOR id IN get_exp_line_id LOOP
2836:
2837: IF p_location_id IS NOT NULL THEN
2842: IF l_propid IS NULL THEN l_propid := locn_prop_rec.property_id; END IF;
2843:
2844: FOR expcl_rec IN get_expcl_by_locn(locn_prop_rec.location_id, id.currency_code) LOOP
2845: l_info := ' extracting for expense class id : '|| expcl_rec.expense_class_id;
2846: pnp_debug_pkg.log(l_info);
2847:
2848: l_total := l_total + 1;
2849:
2850: BEGIN
2873: THEN
2874:
2875: FOR expcl_rec IN get_expcl_by_prop(nvl(l_propid, p_property_id), id.currency_code) LOOP
2876: l_info := ' extracting for expense class id : '|| expcl_rec.expense_class_id;
2877: pnp_debug_pkg.log(l_info);
2878:
2879: l_total := l_total + 1;
2880:
2881: BEGIN
2899: END IF;
2900: END LOOP;
2901:
2902: fnd_message.set_name('PN','PN_REC_EXPCL_DTL');
2903: pnp_debug_pkg.put_log_msg('');
2904: pnp_debug_pkg.put_log_msg(fnd_message.get);
2905:
2906: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2907: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2900: END LOOP;
2901:
2902: fnd_message.set_name('PN','PN_REC_EXPCL_DTL');
2903: pnp_debug_pkg.put_log_msg('');
2904: pnp_debug_pkg.put_log_msg(fnd_message.get);
2905:
2906: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2907: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2908: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2906: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
2907: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2908: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2909: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2910: pnp_debug_pkg.put_log_msg(fnd_message.get);
2911: pnp_debug_pkg.put_log_msg('');
2912:
2913: pnp_debug_pkg.log(l_desc ||' (-)');
2914:
2907: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
2908: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
2909: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2910: pnp_debug_pkg.put_log_msg(fnd_message.get);
2911: pnp_debug_pkg.put_log_msg('');
2912:
2913: pnp_debug_pkg.log(l_desc ||' (-)');
2914:
2915: EXCEPTION
2909: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
2910: pnp_debug_pkg.put_log_msg(fnd_message.get);
2911: pnp_debug_pkg.put_log_msg('');
2912:
2913: pnp_debug_pkg.log(l_desc ||' (-)');
2914:
2915: EXCEPTION
2916: WHEN OTHERS THEN
2917: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2913: pnp_debug_pkg.log(l_desc ||' (-)');
2914:
2915: EXCEPTION
2916: WHEN OTHERS THEN
2917: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2918: raise;
2919: END populate_expense_class_details;
2920:
2921: ------------------------------------------------------------------------------+
2976: l_failed NUMBER := 0;
2977:
2978: BEGIN
2979:
2980: pnp_debug_pkg.log(l_desc ||' (+)');
2981:
2982: IF p_location_id IS NOT NULL THEN
2983:
2984: FOR locn_prop_rec IN get_locn_prop_id LOOP
2987: IF l_propid IS NULL THEN l_propid := locn_prop_rec.property_id; END IF;
2988:
2989: FOR arcl_rec IN get_arcl_by_locn(locn_prop_rec.location_id) LOOP
2990: l_info := ' extracting for area class id : '|| arcl_rec.area_class_id;
2991: pnp_debug_pkg.log(l_info);
2992:
2993: l_total := l_total + 1;
2994:
2995: BEGIN
3016: THEN
3017:
3018: FOR arcl_rec IN get_arcl_by_prop(nvl(l_propid, p_property_id)) LOOP
3019: l_info := ' extracting for area class id : '|| arcl_rec.area_class_id;
3020: pnp_debug_pkg.log(l_info);
3021:
3022: l_total := l_total + 1;
3023:
3024: BEGIN
3040:
3041: END IF;
3042:
3043: fnd_message.set_name('PN','PN_REC_ARCL_DTL');
3044: pnp_debug_pkg.put_log_msg('');
3045: pnp_debug_pkg.put_log_msg(fnd_message.get);
3046:
3047: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
3048: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
3041: END IF;
3042:
3043: fnd_message.set_name('PN','PN_REC_ARCL_DTL');
3044: pnp_debug_pkg.put_log_msg('');
3045: pnp_debug_pkg.put_log_msg(fnd_message.get);
3046:
3047: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
3048: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
3049: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
3047: fnd_message.set_name('PN','PN_CP_RESULT_SUMMARY');
3048: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
3049: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
3050: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
3051: pnp_debug_pkg.put_log_msg(fnd_message.get);
3052: pnp_debug_pkg.put_log_msg('');
3053:
3054: pnp_debug_pkg.log(l_desc ||' (-)');
3055:
3048: fnd_message.set_token('TOTAL', TO_CHAR(l_total));
3049: fnd_message.set_token('PASS', TO_CHAR(l_total - l_failed));
3050: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
3051: pnp_debug_pkg.put_log_msg(fnd_message.get);
3052: pnp_debug_pkg.put_log_msg('');
3053:
3054: pnp_debug_pkg.log(l_desc ||' (-)');
3055:
3056: EXCEPTION
3050: fnd_message.set_token('FAIL', TO_CHAR(l_failed));
3051: pnp_debug_pkg.put_log_msg(fnd_message.get);
3052: pnp_debug_pkg.put_log_msg('');
3053:
3054: pnp_debug_pkg.log(l_desc ||' (-)');
3055:
3056: EXCEPTION
3057: WHEN OTHERS THEN
3058: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3054: pnp_debug_pkg.log(l_desc ||' (-)');
3055:
3056: EXCEPTION
3057: WHEN OTHERS THEN
3058: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3059: raise;
3060: END populate_area_class_details;
3061:
3062: ------------------------------------------------------------------------------+
3096: l_desc VARCHAR2(100) := 'pn_recovery_extract_pkg.find_expense_ovr_values' ;
3097: l_info VARCHAR2(300);
3098: BEGIN
3099:
3100: pnp_debug_pkg.log(l_desc ||' (+)');
3101:
3102: l_info := ' resetting ovr values variables ';
3103: pnp_debug_pkg.log(l_info);
3104:
3099:
3100: pnp_debug_pkg.log(l_desc ||' (+)');
3101:
3102: l_info := ' resetting ovr values variables ';
3103: pnp_debug_pkg.log(l_info);
3104:
3105: p_exp_cls_line_dtl_id := null;
3106: p_exp_cls_line_id := null;
3107: p_fee_af_contr_ovr := null;
3118: p_master_ovr(i).location_id = p_loc_id AND
3119: p_master_ovr(i).cust_account_id = p_cust_id THEN
3120:
3121: l_info := ' found matching data and determining which values to return ';
3122: pnp_debug_pkg.log(l_info);
3123:
3124: IF p_keep_override = 'Y' THEN
3125: p_fee_af_contr_ovr := p_master_ovr(i).cls_line_fee_after_contr_ovr;
3126: p_mst_share_pct_ovr := p_master_ovr(i).cls_line_share_pct;
3137:
3138: END IF;
3139: END LOOP;
3140:
3141: pnp_debug_pkg.log(l_desc ||' (-)');
3142:
3143: EXCEPTION
3144: WHEN OTHERS THEN
3145: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3141: pnp_debug_pkg.log(l_desc ||' (-)');
3142:
3143: EXCEPTION
3144: WHEN OTHERS THEN
3145: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3146: raise;
3147: END find_expense_ovr_values;
3148:
3149: ------------------------------------------------------------------------------+
3377: exp_cls_prior_master_ovr exp_cls_line_mst_tbl;
3378: exp_cls_prior_detail_ovr exp_cls_line_dtl_tbl;
3379:
3380: BEGIN
3381: pnp_debug_pkg.log(l_desc ||' (+)');
3382:
3383: fnd_message.set_name('PN','PN_REC_EXPCL_DTL_CP_INFO');
3384: fnd_message.set_token('EXPCL', to_char(p_expense_class_id));
3385: fnd_message.set_token('EXPLN', to_char(p_expense_line_id));
3386: fnd_message.set_token('STR' , p_from_date);
3387: fnd_message.set_token('END' , p_to_date);
3388: fnd_message.set_token('AOD' , p_as_of_date);
3389: fnd_message.set_token('OVR' , p_keep_override);
3390: pnp_debug_pkg.put_log_msg('');
3391: pnp_debug_pkg.put_log_msg(fnd_message.get);
3392: pnp_debug_pkg.put_log_msg('');
3393:
3394: l_info := ' validating currency';
3387: fnd_message.set_token('END' , p_to_date);
3388: fnd_message.set_token('AOD' , p_as_of_date);
3389: fnd_message.set_token('OVR' , p_keep_override);
3390: pnp_debug_pkg.put_log_msg('');
3391: pnp_debug_pkg.put_log_msg(fnd_message.get);
3392: pnp_debug_pkg.put_log_msg('');
3393:
3394: l_info := ' validating currency';
3395: pnp_debug_pkg.log(l_info);
3388: fnd_message.set_token('AOD' , p_as_of_date);
3389: fnd_message.set_token('OVR' , p_keep_override);
3390: pnp_debug_pkg.put_log_msg('');
3391: pnp_debug_pkg.put_log_msg(fnd_message.get);
3392: pnp_debug_pkg.put_log_msg('');
3393:
3394: l_info := ' validating currency';
3395: pnp_debug_pkg.log(l_info);
3396:
3391: pnp_debug_pkg.put_log_msg(fnd_message.get);
3392: pnp_debug_pkg.put_log_msg('');
3393:
3394: l_info := ' validating currency';
3395: pnp_debug_pkg.log(l_info);
3396:
3397: FOR check_cur IN check_currency LOOP
3398: fnd_message.set_name('PN', 'PN_REC_EXP_CUR_MISMATCH');
3399: RAISE currency_exception;
3399: RAISE currency_exception;
3400: END LOOP;
3401:
3402: l_info := ' initializing values ';
3403: pnp_debug_pkg.log(l_info);
3404:
3405: l_fee_use_table.delete;
3406: l_share_use_table.delete;
3407: l_ovr_use_table.delete;
3413: exp_cls_prior_master_ovr.delete;
3414: exp_cls_prior_detail_ovr.delete;
3415:
3416: l_info := ' caching default and override values';
3417: pnp_debug_pkg.log(l_info);
3418:
3419: l_regenerate := 'N';
3420:
3421: FOR check_exists IN is_reextract LOOP
3428: fnd_message.set_token('FDATE', p_from_date);
3429: fnd_message.set_token('TDATE', p_to_date);
3430: fnd_message.set_token('AODATE', p_as_of_date);
3431: fnd_message.set_token('NAME', check_exists.expense_class_name);
3432: pnp_debug_pkg.log(fnd_message.get);
3433: RETURN;
3434: END IF;
3435:
3436: l_regenerate := 'Y';
3465: END IF;
3466:
3467: l_dummy_id := null;
3468: l_info := ' getting prior cls dtl id for overrides ';
3469: pnp_debug_pkg.log(l_info);
3470:
3471: FOR get_first_id IN get_prior_cls_dtl_id LOOP
3472: l_dummy_id := get_first_id.expense_class_dtl_id;
3473: exit;
3492:
3493: END LOOP;
3494:
3495: l_info := ' fetching information to prepare data processing ';
3496: pnp_debug_pkg.log(l_info);
3497:
3498: FOR expense_class_rec IN get_exp_class_info LOOP
3499:
3500: IF l_expense_class_dtl_id IS NOT NULL THEN
3568:
3569: IF expense_line_rec.expense_line_indicator <> 'PARENT' THEN
3570:
3571: l_info := ' trying to find override values for a given line ';
3572: pnp_debug_pkg.log(l_info);
3573:
3574: l_found := FALSE;
3575:
3576: IF l_regenerate = 'Y' THEN
3575:
3576: IF l_regenerate = 'Y' THEN
3577:
3578: l_info := ' trying to find override values from regenerated extract';
3579: pnp_debug_pkg.log(l_info);
3580:
3581: find_expense_ovr_values(
3582: p_master_ovr => exp_cls_curnt_master_ovr,
3583: p_detail_ovr => exp_cls_curnt_detail_ovr,
3602:
3603: IF NOT l_found THEN
3604:
3605: l_info := ' trying to find override values from prior extract';
3606: pnp_debug_pkg.log(l_info);
3607:
3608: IF l_regenerate = 'N' THEN l_found := null; END IF;
3609:
3610: find_expense_ovr_values(
3629:
3630: END IF;
3631:
3632: l_info := ' calculating recovery amount and computed recovery amount ';
3633: pnp_debug_pkg.log(l_info);
3634:
3635: l_recov_amount := expense_line_rec.actual_amount * nvl(expense_class_rec.portion_pct,100) / 100;
3636: l_cpt_recov_amount := l_recov_amount * nvl(nvl(l_dtl_share_pct_ovr, expense_class_rec.cls_incl_share_pct),100) / 100 * (1 + nvl(nvl(l_dtl_fee_bf_contr_ovr, expense_class_rec.cls_incl_fee_before_contr), 0) / 100);
3637:
3635: l_recov_amount := expense_line_rec.actual_amount * nvl(expense_class_rec.portion_pct,100) / 100;
3636: l_cpt_recov_amount := l_recov_amount * nvl(nvl(l_dtl_share_pct_ovr, expense_class_rec.cls_incl_share_pct),100) / 100 * (1 + nvl(nvl(l_dtl_fee_bf_contr_ovr, expense_class_rec.cls_incl_fee_before_contr), 0) / 100);
3637:
3638: l_info:= ' collecting master class-line data for space assignment '||space_assign_rec.cust_space_assign_id||' ';
3639: pnp_debug_pkg.log(l_info);
3640:
3641: process_exp_cls_dtl_mst_data(
3642: p_master_data => exp_cls_line_master_data,
3643: p_ovr_use_data => l_ovr_use_table,
3664: p_use_prior_ovr => (NOT l_found AND l_regenerate = 'Y')
3665: );
3666:
3667: l_info:= ' collecting detail class-line data for space assignment '||space_assign_rec.cust_space_assign_id;
3668: pnp_debug_pkg.log(l_info);
3669:
3670: process_exp_cls_dtl_dtl_data(
3671: p_detail_data => exp_cls_line_detail_data,
3672: p_master_data_id => l_master_data_id,
3690: END LOOP;
3691: END LOOP;
3692:
3693: l_info := ' dumping data into table ';
3694: pnp_debug_pkg.log(l_info);
3695:
3696: process_exp_class_line_data(
3697: p_old_detail_data => exp_cls_curnt_detail_ovr,
3698: p_old_master_data => exp_cls_curnt_master_ovr,
3704: );
3705:
3706: IF l_area_class_id IS NOT NULL THEN
3707: l_info := ' generating area class detail associated with the expense class ';
3708: pnp_debug_pkg.log(l_info);
3709:
3710: extract_area(
3711: errbuf => l_dummy,
3712: retcode => l_dummy,
3715: p_from_date => p_from_date,
3716: p_to_date => p_to_date,
3717: p_keep_override => p_keep_override);
3718: END IF;
3719: pnp_debug_pkg.log(l_desc ||' (-)');
3720:
3721: EXCEPTION
3722: WHEN currency_exception THEN
3723: pnp_debug_pkg.put_log_msg(fnd_message.get);
3719: pnp_debug_pkg.log(l_desc ||' (-)');
3720:
3721: EXCEPTION
3722: WHEN currency_exception THEN
3723: pnp_debug_pkg.put_log_msg(fnd_message.get);
3724: raise;
3725: WHEN OTHERS THEN
3726: fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
3727: pnp_debug_pkg.put_log_msg(fnd_message.get);
3723: pnp_debug_pkg.put_log_msg(fnd_message.get);
3724: raise;
3725: WHEN OTHERS THEN
3726: fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
3727: pnp_debug_pkg.put_log_msg(fnd_message.get);
3728: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3729: raise;
3730: END extract_expense;
3731:
3724: raise;
3725: WHEN OTHERS THEN
3726: fnd_message.set_name('PN','PN_REC_CP_INCOMPLETE');
3727: pnp_debug_pkg.put_log_msg(fnd_message.get);
3728: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3729: raise;
3730: END extract_expense;
3731:
3732:
3800: pn_mo_cache_utils.get_current_org_id));
3801:
3802: BEGIN
3803:
3804: pnp_debug_pkg.log(l_desc ||' (+)');
3805:
3806: l_info := ' validating inputs ';
3807: pnp_debug_pkg.log(l_info);
3808:
3803:
3804: pnp_debug_pkg.log(l_desc ||' (+)');
3805:
3806: l_info := ' validating inputs ';
3807: pnp_debug_pkg.log(l_info);
3808:
3809: l_from_date := fnd_date.canonical_to_date(p_from_date);
3810: l_to_date := fnd_date.canonical_to_date(p_to_date);
3811: l_as_of_date:= fnd_date.canonical_to_date(p_as_of_date);
3827: l_currency:= p_currency_code;
3828: END IF;
3829:
3830: l_info := ' performing input validation ';
3831: pnp_debug_pkg.log(l_info);
3832:
3833: IF NOT (l_location_id IS NOT NULL OR l_property_id IS NOT NULL) THEN
3834: fnd_message.set_name('PN','PN_LOC_PROP_REQ');
3835: raise bad_input_exception;
3864: END IF;
3865:
3866: IF p_called_from = 'SRS' THEN
3867: l_info:= ' performing extraction';
3868: pnp_debug_pkg.log(l_info);
3869:
3870: extract_expense_lines(
3871: p_location_id => l_location_id,
3872: p_property_id => l_property_id,
3901: p_keep_override => p_keep_override
3902: );
3903: END IF;
3904:
3905: pnp_debug_pkg.log(l_desc ||' (-)');
3906:
3907: EXCEPTION
3908: WHEN bad_input_exception THEN
3909: pnp_debug_pkg.log(fnd_message.get);
3905: pnp_debug_pkg.log(l_desc ||' (-)');
3906:
3907: EXCEPTION
3908: WHEN bad_input_exception THEN
3909: pnp_debug_pkg.log(fnd_message.get);
3910: raise;
3911: WHEN OTHERS THEN
3912: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3913: raise;
3908: WHEN bad_input_exception THEN
3909: pnp_debug_pkg.log(fnd_message.get);
3910: raise;
3911: WHEN OTHERS THEN
3912: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
3913: raise;
3914: END extract_line_expense_area;
3915:
3916: ------------------------------------------------------------------------------+
3966: l_count INTEGER;
3967:
3968: BEGIN
3969:
3970: pnp_debug_pkg.log(l_desc ||' (+)');
3971:
3972: IF p_delete_all_flag = 'Y' THEN
3973: l_info := ' purging everything ';
3974: pnp_debug_pkg.log(l_info);
3970: pnp_debug_pkg.log(l_desc ||' (+)');
3971:
3972: IF p_delete_all_flag = 'Y' THEN
3973: l_info := ' purging everything ';
3974: pnp_debug_pkg.log(l_info);
3975: DELETE pn_rec_exp_itf;
3976: return;
3977: END IF;
3978: pnp_debug_pkg.log('p_extract_code='||p_extract_code);
3974: pnp_debug_pkg.log(l_info);
3975: DELETE pn_rec_exp_itf;
3976: return;
3977: END IF;
3978: pnp_debug_pkg.log('p_extract_code='||p_extract_code);
3979: l_cursor := dbms_sql.open_cursor;
3980: l_sqlhead := 'DELETE pn_rec_exp_itf WHERE ';
3981:
3982: l_info := ' figuring expense extract code';
3979: l_cursor := dbms_sql.open_cursor;
3980: l_sqlhead := 'DELETE pn_rec_exp_itf WHERE ';
3981:
3982: l_info := ' figuring expense extract code';
3983: pnp_debug_pkg.log(l_info);
3984: l_extract_code := p_extract_code;
3985: l_transfer_flag := p_transfer_flag;
3986: l_from_date := fnd_date.canonical_to_date(p_from_date);
3987: l_to_date := fnd_date.canonical_to_date(p_to_date);
3997:
3998: END IF;
3999:
4000: l_info := ' figuring transfer flag';
4001: pnp_debug_pkg.log(l_info);
4002:
4003: IF p_transfer_flag IS NOT NULL THEN
4004: IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4005: l_sqltail := l_sqltail || ' transfer_flag = :l_transfer_flag';
4005: l_sqltail := l_sqltail || ' transfer_flag = :l_transfer_flag';
4006: END IF;
4007:
4008: l_info := ' figuring from date';
4009: pnp_debug_pkg.log(l_info);
4010:
4011: IF p_from_date IS NOT NULL THEN
4012: IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4013: l_sqltail := l_sqltail ||' from_date >= :l_from_date)';
4014:
4015: END IF;
4016:
4017: l_info := ' figuring to date';
4018: pnp_debug_pkg.log(l_info);
4019:
4020: IF p_to_date IS NOT NULL THEN
4021: IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4022: l_sqltail := l_sqltail ||' to_date <= :l_to_date)';
4023:
4024: END IF;
4025:
4026: l_info := ' figuring location code';
4027: pnp_debug_pkg.log(l_info);
4028:
4029: IF p_location_code IS NOT NULL THEN
4030: IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4031: FOR loc_rec IN derive_loc_id_from_loc_code LOOP l_loc_id := loc_rec.location_id; END LOOP;
4033:
4034: END IF;
4035:
4036: l_info := ' figuring property code';
4037: pnp_debug_pkg.log(l_info);
4038:
4039: IF p_property_code IS NOT NULL THEN
4040: IF l_sqltail IS NOT NULL THEN l_sqltail := l_sqltail ||' AND '; END IF;
4041: FOR prop_rec IN derive_prop_id_from_prop_code LOOP l_prop_id := prop_rec.property_id; END LOOP;
4047:
4048: IF l_sqltail IS NOT NULL THEN
4049:
4050: l_info := ' deleting using dynamic SQL';
4051: pnp_debug_pkg.log(l_info);
4052: pnp_debug_pkg.log('');
4053: pnp_debug_pkg.log(l_sqlhead);
4054: pnp_debug_pkg.log(l_sqltail);
4055: pnp_debug_pkg.log('');
4048: IF l_sqltail IS NOT NULL THEN
4049:
4050: l_info := ' deleting using dynamic SQL';
4051: pnp_debug_pkg.log(l_info);
4052: pnp_debug_pkg.log('');
4053: pnp_debug_pkg.log(l_sqlhead);
4054: pnp_debug_pkg.log(l_sqltail);
4055: pnp_debug_pkg.log('');
4056:
4049:
4050: l_info := ' deleting using dynamic SQL';
4051: pnp_debug_pkg.log(l_info);
4052: pnp_debug_pkg.log('');
4053: pnp_debug_pkg.log(l_sqlhead);
4054: pnp_debug_pkg.log(l_sqltail);
4055: pnp_debug_pkg.log('');
4056:
4057: l_statement := l_sqlhead || l_sqltail;
4050: l_info := ' deleting using dynamic SQL';
4051: pnp_debug_pkg.log(l_info);
4052: pnp_debug_pkg.log('');
4053: pnp_debug_pkg.log(l_sqlhead);
4054: pnp_debug_pkg.log(l_sqltail);
4055: pnp_debug_pkg.log('');
4056:
4057: l_statement := l_sqlhead || l_sqltail;
4058: dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
4051: pnp_debug_pkg.log(l_info);
4052: pnp_debug_pkg.log('');
4053: pnp_debug_pkg.log(l_sqlhead);
4054: pnp_debug_pkg.log(l_sqltail);
4055: pnp_debug_pkg.log('');
4056:
4057: l_statement := l_sqlhead || l_sqltail;
4058: dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
4059: IF p_extract_code IS NOT NULL THEN
4086:
4087: IF dbms_sql.is_open (l_cursor) THEN
4088: dbms_sql.close_cursor (l_cursor);
4089: END IF;
4090: pnp_debug_pkg.log(l_desc ||' (-)');
4091:
4092: EXCEPTION
4093: WHEN OTHERS THEN
4094: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
4090: pnp_debug_pkg.log(l_desc ||' (-)');
4091:
4092: EXCEPTION
4093: WHEN OTHERS THEN
4094: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
4095: raise;
4096: END;
4097:
4098: END pn_recovery_extract_pkg;