[Home] [Help]
1: PACKAGE BODY pnp_util_func AS
2: -- $Header: PNPFUNCB.pls 120.19.12010000.3 2008/11/12 06:33:02 rthumma ship $
3:
4: /*===========================================================================+
5: -- NAME : get_total_payment_item_amt
64: ELSE
65: IF (amounts_record.currency_code = p_curr_code) THEN
66: total_item_amount := total_item_amount + NVL(amounts_record.actual_amount,0);
67: ELSE
68: l_conv_type := pnp_util_func.check_conversion_type(p_curr_code,l_org_id);
69:
70: IF upper(l_conv_type) <> 'USER' THEN
71:
72: IF amounts_record.due_date >= SYSDATE THEN
75: l_conv_date := amounts_record.due_date;
76: END IF;
77:
78: total_item_amount := total_item_amount +
79: NVL(pnp_util_func.export_curr_amount(
80: currency_code => amounts_record.currency_code,
81: export_currency_code => p_curr_code,
82: export_date => l_conv_date,
83: conversion_type => l_conv_type,
293: l_Allocated_Area NUMBER;
294: l_Allocated_Area_Emp NUMBER;
295: l_Allocated_Area_Cust NUMBER;
296: l_date DATE := TO_DATE('31/12/4712' , 'DD/MM/YYYY');
297: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
298: INVALID_LOCATION_TYPE EXCEPTION;
299:
300: CURSOR Alloc_Area_Emp_C ( p_Location_Id IN NUMBER
301: ,p_Cost_Center IN NUMBER
336: );
337:
338: BEGIN
339:
340: l_location_type := pnp_util_func.get_location_type_lookup_code (
341: p_location_id => p_location_id,
342: p_as_of_date => l_as_of_date);
343:
344: IF l_Location_Type IS NULL THEN
441: l_assignable_area NUMBER;
442: l_allocated_area_emp NUMBER;
443: l_allocated_area_cust NUMBER;
444: l_date DATE := TO_DATE('31/12/4712', 'DD/MM/YYYY');
445: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
446:
447:
448: INVALID_LOCATION_TYPE EXCEPTION;
449:
533: );
534:
535: BEGIN
536:
537: l_location_type := pnp_util_func.get_location_type_lookup_code(
538: p_location_id => p_location_id,
539: p_as_of_date => l_as_of_date);
540:
541:
645: |
646: | MODIFICATION HISTORY
647: |
648: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
649: | pnp_util_func.Get_Vacant_Area
650: | - Bug Fix for #1379527
651: | 05-FEB-2001 Lakshmikanth Replaced get_building_rentable_area by
652: | get_building_assignable_area in the
653: | get_vacant_area_percent.
658: FUNCTION get_vacant_area_percent ( p_Location_Id NUMBER ,
659: p_as_of_date DATE ) RETURN NUMBER IS
660:
661: l_Location_Type pn_locations.location_type_lookup_code%type;
662: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
663: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,
664: pnp_util_func.get_as_of_date(p_as_of_date));
665: l_Assignable_Area NUMBER:= get_building_assignable_area ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
666: INVALID_LOCATION_TYPE EXCEPTION;
660:
661: l_Location_Type pn_locations.location_type_lookup_code%type;
662: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
663: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,
664: pnp_util_func.get_as_of_date(p_as_of_date));
665: l_Assignable_Area NUMBER:= get_building_assignable_area ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
666: INVALID_LOCATION_TYPE EXCEPTION;
667: a NUMBER := 0;
668:
661: l_Location_Type pn_locations.location_type_lookup_code%type;
662: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
663: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,
664: pnp_util_func.get_as_of_date(p_as_of_date));
665: l_Assignable_Area NUMBER:= get_building_assignable_area ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
666: INVALID_LOCATION_TYPE EXCEPTION;
667: a NUMBER := 0;
668:
669: BEGIN
667: a NUMBER := 0;
668:
669: BEGIN
670:
671: l_location_type := pnp_util_func.get_location_type_lookup_code(
672: p_location_id => p_location_id,
673: p_as_of_date => l_as_of_date);
674:
675: IF l_location_type IS NULL THEN
737:
738: l_Location_Type pn_locations.location_type_lookup_code%type;
739:
740: l_Rentable_Area NUMBER:= get_building_rentable_area (p_location_id,
741: pnp_util_func.get_as_of_date(p_as_of_date));
742: l_Usable_Area NUMBER:= get_building_usable_area ( p_location_id,
743: pnp_util_func.get_as_of_date(p_as_of_date));
744: l_return_value NUMBER;
745: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
739:
740: l_Rentable_Area NUMBER:= get_building_rentable_area (p_location_id,
741: pnp_util_func.get_as_of_date(p_as_of_date));
742: l_Usable_Area NUMBER:= get_building_usable_area ( p_location_id,
743: pnp_util_func.get_as_of_date(p_as_of_date));
744: l_return_value NUMBER;
745: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
746: INVALID_LOCATION_TYPE EXCEPTION;
747: a NUMBER:= 0;
741: pnp_util_func.get_as_of_date(p_as_of_date));
742: l_Usable_Area NUMBER:= get_building_usable_area ( p_location_id,
743: pnp_util_func.get_as_of_date(p_as_of_date));
744: l_return_value NUMBER;
745: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
746: INVALID_LOCATION_TYPE EXCEPTION;
747: a NUMBER:= 0;
748:
749: BEGIN
748:
749: BEGIN
750:
751:
752: l_location_type := pnp_util_func.get_location_type_lookup_code (
753: p_location_id => p_location_id,
754: p_as_of_date => l_as_of_date);
755:
756: IF l_location_type IS NULL THEN
826: RETURN NUMBER IS
827:
828: l_Location_Type pn_locations.location_type_lookup_code%type;
829: l_floors NUMBER;
830: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
831: INVALID_LOCATION_TYPE EXCEPTION;
832:
833: CURSOR floor_count_C( p_Location_Id NUMBER
834: ,p_as_of_date DATE
845:
846: BEGIN
847:
848:
849: l_location_type := pnp_util_func.get_location_type_lookup_code (
850: p_location_id => p_location_id,
851: p_as_of_date => l_as_of_date);
852:
853: IF l_location_type IS NULL THEN
929: RETURN NUMBER IS
930:
931: l_Location_Type pn_locations.location_type_lookup_code%type;
932: l_offices NUMBER;
933: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
934: INVALID_LOCATION_TYPE EXCEPTION;
935:
936: BEGIN
937:
935:
936: BEGIN
937:
938:
939: l_location_type := pnp_util_func.get_location_type_lookup_code (
940: p_location_id => p_location_id,
941: p_as_of_date => l_as_of_date);
942:
943: IF l_location_type IS NULL THEN
1035: l_LocationType pn_locations.location_type_lookup_code%TYPE;
1036: l_UtilizedCapacity NUMBER:=0;
1037: l_UtilizedCapacityEmp NUMBER:=0;
1038: l_UtilizedCapacityCust NUMBER:=0;
1039: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1040:
1041: CURSOR Utilised_Capacity_Emp_C( p_location_id IN NUMBER,
1042: p_As_Of_Date IN DATE) IS
1043: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1088: NVL(cust_assign_end_date, g_end_of_time)
1089: );
1090: BEGIN
1091:
1092: l_locationtype := pnp_util_func.get_location_type_lookup_code (
1093: p_location_id => p_location_id,
1094: p_as_of_date => l_as_of_date);
1095: IF l_LocationType IS NULL THEN
1096: RETURN 0;
1165: |
1166: | MODIFICATION HISTORY
1167: |
1168: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1169: | pnp_util_func.get_utilized_capacity
1170: | - Bug Fix for #1379527
1171: |
1172: | 31-OCT-2001 graghuna o added p_as_of_date for Location
1173: | Date-Effectivity.
1179: p_as_of_date IN DATE )
1180: RETURN NUMBER IS
1181:
1182: l_Location_Type pn_locations.location_type_lookup_code%type;
1183: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1184: l_max_capacity NUMBER:= pnp_util_func.get_building_max_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1185: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1186: INVALID_LOCATION_TYPE EXCEPTION;
1187:
1180: RETURN NUMBER IS
1181:
1182: l_Location_Type pn_locations.location_type_lookup_code%type;
1183: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1184: l_max_capacity NUMBER:= pnp_util_func.get_building_max_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1185: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1186: INVALID_LOCATION_TYPE EXCEPTION;
1187:
1188: BEGIN
1181:
1182: l_Location_Type pn_locations.location_type_lookup_code%type;
1183: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1184: l_max_capacity NUMBER:= pnp_util_func.get_building_max_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1185: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1186: INVALID_LOCATION_TYPE EXCEPTION;
1187:
1188: BEGIN
1189:
1186: INVALID_LOCATION_TYPE EXCEPTION;
1187:
1188: BEGIN
1189:
1190: l_location_type := pnp_util_func.get_location_type_lookup_code (
1191: p_location_id => p_location_id,
1192: p_as_of_date => l_as_of_date);
1193: IF l_location_type IS NULL THEN
1194: raise NO_DATA_FOUND;
1237: |
1238: | MODIFICATION HISTORY
1239: |
1240: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1241: | pnp_util_func.get_utilized_capacity
1242: | - Bug Fix for #1379527
1243: |
1244: | 31-OCT-2001 graghuna o added p_as_of_date for Location Date-Effectivity.
1245: | 05-MAY-2004 ftanudja o handle if location type is null.
1248: FUNCTION get_occupancy_percent ( p_Location_Id NUMBER ,
1249: p_as_of_date IN DATE ) RETURN NUMBER IS
1250:
1251: l_Location_Type pn_locations.location_type_lookup_code%type;
1252: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1253: l_max_capacity NUMBER:= get_building_max_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1254: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1255: INVALID_LOCATION_TYPE EXCEPTION;
1256: a NUMBER:= 0;
1249: p_as_of_date IN DATE ) RETURN NUMBER IS
1250:
1251: l_Location_Type pn_locations.location_type_lookup_code%type;
1252: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1253: l_max_capacity NUMBER:= get_building_max_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1254: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1255: INVALID_LOCATION_TYPE EXCEPTION;
1256: a NUMBER:= 0;
1257:
1250:
1251: l_Location_Type pn_locations.location_type_lookup_code%type;
1252: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1253: l_max_capacity NUMBER:= get_building_max_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1254: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1255: INVALID_LOCATION_TYPE EXCEPTION;
1256: a NUMBER:= 0;
1257:
1258: BEGIN
1258: BEGIN
1259:
1260:
1261:
1262: l_location_type := pnp_util_func.get_location_type_lookup_code (
1263: p_location_id => p_location_id,
1264: p_as_of_date => l_as_of_date);
1265:
1266: IF l_location_type IS NULL THEN
1313: |
1314: | MODIFICATION HISTORY
1315: |
1316: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1317: | pnp_util_func.get_utilized_capacity
1318: | AND pnp_util_func.Get_Vacant_Area
1319: | - Bug Fix for #1379527
1320: | 31-OCT-2001 graghuna o added p_as_of_date for Location Date-Effectivity.
1321: | 05-MAY-2004 ftanudja o handle if location type is null.
1314: | MODIFICATION HISTORY
1315: |
1316: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1317: | pnp_util_func.get_utilized_capacity
1318: | AND pnp_util_func.Get_Vacant_Area
1319: | - Bug Fix for #1379527
1320: | 31-OCT-2001 graghuna o added p_as_of_date for Location Date-Effectivity.
1321: | 05-MAY-2004 ftanudja o handle if location type is null.
1322: +===========================================================================*/
1326: ) RETURN NUMBER IS
1327:
1328: l_Location_Type pn_locations.location_type_lookup_code%type;
1329:
1330: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1331: l_rentable_area NUMBER:= get_building_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1332: l_utilized_capacity NUMBER:= get_utilized_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1333: INVALID_LOCATION_TYPE EXCEPTION;
1334: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1327:
1328: l_Location_Type pn_locations.location_type_lookup_code%type;
1329:
1330: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1331: l_rentable_area NUMBER:= get_building_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1332: l_utilized_capacity NUMBER:= get_utilized_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1333: INVALID_LOCATION_TYPE EXCEPTION;
1334: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1335: a NUMBER := 0;
1328: l_Location_Type pn_locations.location_type_lookup_code%type;
1329:
1330: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1331: l_rentable_area NUMBER:= get_building_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1332: l_utilized_capacity NUMBER:= get_utilized_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1333: INVALID_LOCATION_TYPE EXCEPTION;
1334: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1335: a NUMBER := 0;
1336:
1330: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1331: l_rentable_area NUMBER:= get_building_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1332: l_utilized_capacity NUMBER:= get_utilized_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1333: INVALID_LOCATION_TYPE EXCEPTION;
1334: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1335: a NUMBER := 0;
1336:
1337: BEGIN
1338:
1335: a NUMBER := 0;
1336:
1337: BEGIN
1338:
1339: l_location_type := pnp_util_func.get_location_type_lookup_code (
1340: p_location_id => p_location_id,
1341: p_as_of_date => l_as_of_date);
1342:
1343: IF l_location_type IS NULL THEN
1412: p_as_of_date IN DATE ) RETURN NUMBER
1413: IS
1414:
1415: l_totalArea NUMBER := 0;
1416: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1417:
1418:
1419: CURSOR Total_Area_C(
1420: p_leaseId IN NUMBER,
1527: l_occupancyCount NUMBER := 0;
1528: l_occupancyCountEmp NUMBER := 0;
1529: l_occupancyCountCust NUMBER := 0;
1530: l_date DATE := TO_DATE('31/12/2199' , 'DD/MM/YYYY');
1531: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1532:
1533: BEGIN
1534:
1535: SELECT NVL(count (*), 0)
2011: RETURN BOOLEAN Is
2012:
2013: l_Dummy VARCHAR2(1);
2014:
2015: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2016:
2017: Begin
2018:
2019: SELECT 'X'
2257: l_retnum NUMBER := 0;
2258: l_retnum_emp NUMBER := 0;
2259: l_retnum_cust NUMBER := 0;
2260: l_date DATE := TO_DATE('31/12/2199' , 'DD/MM/YYYY');
2261: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date); --ASHISH
2262:
2263: BEGIN
2264:
2265: SELECT 1
2307: p_as_of_date DATE,
2308: p_ignore_date BOOLEAN) RETURN VARCHAR2
2309: IS
2310: l_location_code pn_locations_all.location_code%TYPE;
2311: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2312:
2313: CURSOR fetch_loc_code IS
2314: SELECT location_code
2315: FROM pn_locations_all
2351: p_as_of_date DATE,
2352: p_ignore_date BOOLEAN) RETURN VARCHAR2
2353: IS
2354: l_location_type_lookup_code pn_locations_all.location_type_lookup_code%TYPE;
2355: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2356:
2357: CURSOR fetch_code IS
2358: SELECT location_type_lookup_code
2359: FROM pn_locations_all
2466: ,p_end_date DATE
2467: ,p_sch_day NUMBER
2468: ,p_term_freq VARCHAR2
2469: ,p_limit_date DATE)
2470: RETURN PNP_UTIL_FUNC.virtual_sched_tbl_type IS
2471:
2472:
2473: CURSOR get_freq_num(p_freq_char IN VARCHAR2) IS
2474: SELECT TO_NUMBER(DECODE( p_freq_char
2479: , 'YR', 12
2480: , -1)) AS freq_num
2481: FROM DUAL;
2482:
2483: l_sched_tbl PNP_UTIL_FUNC.virtual_sched_tbl_type;
2484:
2485: l_frequency NUMBER;
2486:
2487: l_current_st_dt DATE;
2738: FROM pn_payment_terms_all
2739: WHERE payment_term_ID = p_term_ID;
2740:
2741: l_frequency NUMBER;
2742: l_sched_tbl PNP_UTIL_FUNC.virtual_sched_tbl_type;
2743:
2744: /* user defined exceptions */
2745: INVALID_PARAM EXCEPTION;
2746: INVALID_TERMINATION_DATE EXCEPTION;
2799: FOR term_rec IN terms_for_max_appr_sched( p_lease_ID
2800: ,sd_other.schedule_date)
2801: LOOP
2802:
2803: l_sched_tbl := PNP_UTIL_FUNC.create_virtual_schedules
2804: ( p_start_date => term_rec.start_date
2805: ,p_end_date => term_rec.end_date
2806: ,p_sch_day => term_rec.schedule_day
2807: ,p_term_freq => term_rec.frequency_code
2858: END IF;
2859:
2860: FOR term_rec IN term_details(p_term_ID) LOOP
2861:
2862: l_sched_tbl := PNP_UTIL_FUNC.create_virtual_schedules
2863: ( p_start_date => term_rec.start_date
2864: ,p_end_date => term_rec.end_date
2865: ,p_sch_day => term_rec.schedule_day
2866: ,p_term_freq => term_rec.frequency_code
2999: FUNCTION MIN_LEASE_TERMINATE_DATE (p_lease_id IN NUMBER) RETURN DATE IS
3000:
3001: l_min_lease_terminate_date DATE;
3002: l_norm_trm_exsts BOOLEAN := FALSE;
3003: l_item_end_dt_tbl pnp_util_func.item_end_dt_tbl_type;
3004: i NUMBER;
3005: maxDt DATE := TO_DATE('01/01/0001', 'MM/DD/YYYY');
3006:
3007: CURSOR csr_lst_appr_schd(p_lease_ID IN NUMBER) IS
3155: -- 11-SEP-07 rthumma Bug # 6366630. Enhancement for new profile
3156: -- option for lease early termination
3157: +=============================================================================*/
3158: FUNCTION fetch_item_end_dates( p_lease_id NUMBER)
3159: RETURN pnp_util_func.item_end_dt_tbl_type IS
3160:
3161: CURSOR csr_term_info(p_lease_ID IN NUMBER) IS
3162: SELECT frequency_code,
3163: payment_term_id,
3165: FROM pn_payment_terms_all
3166: WHERE lease_id = p_lease_id
3167: AND NVL(status,'APPROVED') = 'APPROVED';
3168:
3169: l_item_end_dt_tbl pnp_util_func.item_end_dt_tbl_type;
3170: i NUMBER := 0;
3171:
3172: BEGIN
3173: FOR rec IN csr_term_info(p_lease_id) LOOP
3441: ) RETURN NUMBER
3442: IS
3443: l_location_type pn_locations.location_type_lookup_code%type;
3444: l_area pn_location_area_rec;
3445: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3446: invalid_location_type EXCEPTION;
3447:
3448: BEGIN
3449:
3520: ) RETURN NUMBER
3521: IS
3522: l_location_type pn_locations.location_type_lookup_code%type;
3523: l_area pn_location_area_rec;
3524: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3525: invalid_location_type EXCEPTION;
3526:
3527: BEGIN
3528:
3599: ) RETURN NUMBER
3600: IS
3601: l_location_type pn_locations.location_type_lookup_code%type;
3602: l_area pn_location_area_rec;
3603: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3604: invalid_location_type EXCEPTION;
3605:
3606: BEGIN
3607:
3676: RETURN NUMBER
3677: IS
3678: l_location_type pn_locations.location_type_lookup_code%type;
3679: l_area pn_location_area_rec;
3680: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3681: invalid_location_type EXCEPTION;
3682:
3683: BEGIN
3684:
3754: RETURN NUMBER
3755: IS
3756: l_location_type pn_locations.location_type_lookup_code%type;
3757: l_area pn_location_area_rec;
3758: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3759: invalid_location_type EXCEPTION;
3760:
3761: BEGIN
3762:
3833: RETURN NUMBER
3834: IS
3835: l_location_type pn_locations.location_type_lookup_code%type;
3836: l_area pn_location_area_rec;
3837: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3838: invalid_location_type EXCEPTION;
3839:
3840: BEGIN
3841:
3913: ) RETURN NUMBER
3914: IS
3915: l_location_type pn_locations.location_type_lookup_code%type;
3916: l_area pn_location_area_rec;
3917: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3918: invalid_location_type EXCEPTION;
3919:
3920: BEGIN
3921:
3991: p_as_of_date IN DATE ) RETURN NUMBER
3992: IS
3993: l_location_type pn_locations.location_type_lookup_code%type;
3994: l_area pn_location_area_rec;
3995: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3996: invalid_location_type EXCEPTION;
3997:
3998: BEGIN
3999:
4070: p_as_of_date IN DATE ) RETURN NUMBER
4071: IS
4072: l_location_type pn_locations.location_type_lookup_code%type;
4073: l_area pn_location_area_rec;
4074: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4075: invalid_location_type EXCEPTION;
4076:
4077: BEGIN
4078:
4148: p_as_of_date IN DATE ) RETURN NUMBER
4149: IS
4150: l_location_type pn_locations.location_type_lookup_code%type;
4151: l_area pn_location_area_rec;
4152: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4153: invalid_location_type EXCEPTION;
4154:
4155: BEGIN
4156:
4205: |
4206: | MODIFICATION HISTORY
4207: |
4208: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
4209: | pnp_util_func.get_utilized_capacity
4210: | - Bug Fix for #1379527
4211: |
4212: | 18-FEB-2004 abanerje o Handled NO_DATA_FOUND to return 0.
4213: | All the select statements have been
4220: p_as_of_date DATE) RETURN NUMBER IS
4221:
4222: l_Location_Type pn_locations.location_type_lookup_code%type;
4223:
4224: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4225: l_max_capacity NUMBER:= pnp_util_func.get_floor_max_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4226: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4227: INVALID_LOCATION_TYPE EXCEPTION;
4228:
4221:
4222: l_Location_Type pn_locations.location_type_lookup_code%type;
4223:
4224: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4225: l_max_capacity NUMBER:= pnp_util_func.get_floor_max_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4226: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4227: INVALID_LOCATION_TYPE EXCEPTION;
4228:
4229:
4222: l_Location_Type pn_locations.location_type_lookup_code%type;
4223:
4224: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4225: l_max_capacity NUMBER:= pnp_util_func.get_floor_max_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4226: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4227: INVALID_LOCATION_TYPE EXCEPTION;
4228:
4229:
4230:
4229:
4230:
4231: BEGIN
4232:
4233: l_location_type := pnp_util_func.get_location_type_lookup_code (
4234: p_location_id => p_location_id,
4235: p_as_of_date => l_as_of_date);
4236: IF l_Location_Type IS NULL THEN
4237: RETURN 0;
4281: |
4282: | MODIFICATION HISTORY
4283: |
4284: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
4285: | pnp_util_func.get_utilized_capacity
4286: | - Bug Fix for #1379527
4287: | 30-OCT-2002 Satish Tripathi o Access _all table for performance issues.
4288: | 31-OCT-2001 graghuna o added p_as_of_date for Location
4289: | Date-Effectivity
4298: FUNCTION get_office_vacancy ( p_Location_Id NUMBER ,
4299: p_as_of_date IN DATE ) RETURN NUMBER IS
4300:
4301: l_Location_Type pn_locations.location_type_lookup_code%type;
4302: l_utilized_capacity NUMBER:= get_utilized_capacity(p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4303: l_max_capacity NUMBER;
4304: INVALID_LOCATION_TYPE EXCEPTION;
4305: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4306:
4301: l_Location_Type pn_locations.location_type_lookup_code%type;
4302: l_utilized_capacity NUMBER:= get_utilized_capacity(p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4303: l_max_capacity NUMBER;
4304: INVALID_LOCATION_TYPE EXCEPTION;
4305: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4306:
4307:
4308: CURSOR Office_Vacancy_C( p_Location_Id IN NUMBER
4309: ,p_as_of_date IN DATE
4316: );
4317:
4318: BEGIN
4319:
4320: l_location_type := pnp_util_func.get_location_type_lookup_code (
4321: p_location_id => p_location_id,
4322: p_as_of_date => l_as_of_date);
4323:
4324: IF l_Location_Type IS NULL THEN
4388: p_as_of_date IN DATE )
4389: RETURN BOOLEAN
4390: IS
4391: l_exists VARCHAR2(1) := 'N';
4392: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4393:
4394: CURSOR emp_assign_future (l_date DATE) IS
4395: SELECT 'Y'
4396: FROM pn_space_assign_emp_all
4542: FUNCTION get_floor_secondary_area ( p_Location_Id NUMBER,
4543: p_as_of_date DATE ) RETURN NUMBER IS
4544:
4545: l_Location_Type pn_locations.location_type_lookup_code%type;
4546: l_rentable_area NUMBER:= get_floor_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4547: l_usable_area NUMBER:= get_floor_usable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4548: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date); --ASHISH
4549: INVALID_LOCATION_TYPE EXCEPTION;
4550:
4543: p_as_of_date DATE ) RETURN NUMBER IS
4544:
4545: l_Location_Type pn_locations.location_type_lookup_code%type;
4546: l_rentable_area NUMBER:= get_floor_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4547: l_usable_area NUMBER:= get_floor_usable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4548: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date); --ASHISH
4549: INVALID_LOCATION_TYPE EXCEPTION;
4550:
4551:
4544:
4545: l_Location_Type pn_locations.location_type_lookup_code%type;
4546: l_rentable_area NUMBER:= get_floor_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4547: l_usable_area NUMBER:= get_floor_usable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4548: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date); --ASHISH
4549: INVALID_LOCATION_TYPE EXCEPTION;
4550:
4551:
4552:
4551:
4552:
4553: BEGIN
4554:
4555: l_location_type := pnp_util_func.get_location_type_lookup_code (
4556: p_location_id => p_location_id,
4557: p_as_of_date => l_as_of_date); --ASHISH
4558:
4559: IF l_location_type IS NULL THEN
4619: l_usable_area NUMBER;
4620: l_common_area NUMBER;
4621: l_secondary_area NUMBER;
4622: INVALID_LOCATION_TYPE EXCEPTION;
4623: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4624:
4625: CURSOR Area_C( p_Location_Id IN NUMBER
4626: ,p_as_of_date IN DATE
4627: ,p_location_type IN VARCHAR2) IS
4634: AND Location_Id = p_Location_Id
4635: );
4636: BEGIN
4637:
4638: l_location_type := pnp_util_func.get_location_type_lookup_code (
4639: p_location_id => p_location_id,
4640: p_as_of_date => l_as_of_date);
4641:
4642: IF l_Location_Type IS NULL THEN
4717: l_Location_Type pn_locations.location_type_lookup_code%type;
4718: l_common_area NUMBER;
4719: INVALID_LOCATION_TYPE EXCEPTION;
4720:
4721: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4722:
4723: BEGIN
4724:
4725: l_location_type := pnp_util_func.get_location_type_lookup_code (
4721: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4722:
4723: BEGIN
4724:
4725: l_location_type := pnp_util_func.get_location_type_lookup_code (
4726: p_location_id => p_location_id,
4727: p_as_of_date => l_as_of_date);
4728:
4729: IF l_location_type IS NULL THEN
4801: l_Location_Type pn_locations.location_type_lookup_code%type;
4802: l_common_area NUMBER;
4803: INVALID_LOCATION_TYPE EXCEPTION;
4804:
4805: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4806:
4807: BEGIN
4808:
4809: l_location_type := pnp_util_func.get_location_type_lookup_code (
4805: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4806:
4807: BEGIN
4808:
4809: l_location_type := pnp_util_func.get_location_type_lookup_code (
4810: p_location_id => p_location_id,
4811: p_as_of_date => l_as_of_date);
4812:
4813: IF l_location_type IS NULL THEN
5043:
5044: l_parent_location_id NUMBER;
5045: l_location_type_lookup_code pn_locations.location_type_lookup_code%type;
5046: l_location_name_rec LOCATION_NAME_REC;
5047: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5048:
5049: BEGIN
5050:
5051: SELECT location_type_lookup_code,parent_location_id
5070: WHERE a.location_id = p_Location_Id
5071: AND l_as_of_date BETWEEN a.active_start_date AND a.active_end_date
5072: AND b.location_id = l_parent_location_id
5073: AND l_as_of_date BETWEEN b.active_start_date AND b.active_end_date
5074: AND c.location_id = pnp_util_func.GET_PARENT_LOCATION_ID(l_parent_location_id)
5075: AND l_as_of_date BETWEEN c.active_start_date AND c.active_end_date
5076: AND prop.property_id(+) = c.property_id
5077: AND d.location_park_id(+) = prop.location_park_id
5078: AND d.location_park_type(+) = 'OFFPRK'
5233: ) RETURN NUMBER
5234: IS
5235:
5236: l_rentable_area NUMBER;
5237: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5238:
5239: BEGIN
5240:
5241: IF p_loc_type_lookup_code IS NULL AND p_location_id IS NULL THEN
5422: RETURN VARCHAR2 IS
5423:
5424: l_uom PN_LOCATIONS.uom_code%type;
5425: l_loc_type PN_LOCATIONS.location_type_lookup_code%type;
5426: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5427:
5428: CURSOR get_uom_code_cur IS
5429: SELECT uom_code
5430: FROM pn_locations_all
5436: begin
5437:
5438: IF p_loc_type is NULL THEN
5439:
5440: l_loc_type := pnp_util_func.get_location_type_lookup_code (
5441: p_location_id => p_location_id,
5442: p_as_of_date => l_as_of_date);
5443:
5444: IF l_loc_type IS NULL THEN
6002:
6003: BEGIN
6004: /* Commented and moved to validate_vacant_area Bug 5854636
6005: IF p_called_frm_mode IS NULL THEN
6006: l_fut_str_dt := pnp_util_func.get_min_futr_str_dt(p_loc_id,p_str_dt);
6007: ELSIF p_called_frm_mode = 'PNTSPACE_UPDATE' THEN
6008: l_fut_str_dt := g_end_of_time;
6009: END IF;
6010:
6067: | 30-OCT-02 Satish o Access _all table for performance issues.
6068: | 31-DEC-02 Mrinal o Added NO_DATA_FOUND exception.
6069: | 20-OCT-03 ftanudja o removed nvl from locn tbl filter. 3197410.
6070: | 07-JAN-04 Daniel o Added new OUT parameter l_total_allocated_area_pct in
6071: | call to pnp_util_func.get_allocated_area. bug # 3354278
6072: | 26-MAY-04 abanerje o Added NVL to the select statement so that the
6073: | p_assignable_area is set to -99 when the area is common
6074: | Using this method we are able to distinguish the
6075: | condition when
6110: l_total_allocated_area NUMBER;
6111: l_total_allocated_area_pct NUMBER;
6112: l_future VARCHAR2(1) := NULL;
6113: l_end_dt DATE := nvl(p_end_dt, g_end_of_time);
6114: l_area_rec pnp_util_func.pn_location_area_rec;
6115: l_fut_str_dt DATE; -- Added for bug#5854636
6116: l_common_flag VARCHAR2(1);
6117: CURSOR get_common_flag IS
6118: SELECT common_area_flag
6123:
6124: BEGIN
6125: PNP_DEBUG_PKG.debug ('validate_vacant_area(+)');
6126:
6127: pnp_util_func.fetch_loctn_area(
6128: p_type => pnp_util_func.get_location_type_lookup_code
6129: (
6130: p_location_id => p_location_id,
6131: p_as_of_date => p_st_date
6124: BEGIN
6125: PNP_DEBUG_PKG.debug ('validate_vacant_area(+)');
6126:
6127: pnp_util_func.fetch_loctn_area(
6128: p_type => pnp_util_func.get_location_type_lookup_code
6129: (
6130: p_location_id => p_location_id,
6131: p_as_of_date => p_st_date
6132: ),
6154: ELSE
6155: l_new_allocated_area_pct := NVL( p_new_allocated_area_pct, (p_new_allocated_area/p_assignable_area)*100 );
6156: l_old_allocated_area_pct := NVL( p_old_allocated_area_pct, (p_old_allocated_area/p_assignable_area)*100 );
6157: END IF;
6158: pnp_util_func.get_allocated_area (p_loc_id => p_location_id,
6159: p_str_dt => p_st_date,
6160: p_new_end_dt => p_end_dt,
6161: p_allocated_area => l_total_allocated_area,
6162: p_allocated_area_pct => l_total_allocated_area_pct,
6169: (NVL(l_new_allocated_area_pct,0) - NVL(l_old_allocated_area_pct,0)
6170: > 100 - NVL(l_total_allocated_area_pct,0))
6171: THEN
6172: IF p_called_frm_mode IS NULL THEN
6173: l_fut_str_dt := pnp_util_func.get_min_futr_str_dt(p_location_id,p_st_date);
6174: ELSIF p_called_frm_mode = 'PNTSPACE_UPDATE' THEN
6175: l_fut_str_dt := g_end_of_time;
6176: END IF;
6177:
6178: IF NVL( p_end_dt, g_end_of_time )
6179: > NVL( l_fut_str_dt, g_end_of_time )
6180: THEN
6181: p_end_dt := l_fut_str_dt - 1;
6182: pnp_util_func.get_allocated_area (p_loc_id => p_location_id,
6183: p_str_dt => p_st_date,
6184: p_new_end_dt => p_end_dt,
6185: p_allocated_area => l_total_allocated_area,
6186: p_allocated_area_pct => l_total_allocated_area_pct,
6568: l_vacant_area_percent NUMBER := 0;
6569: l_vacancy NUMBER := 0;
6570: l_area_utilized NUMBER := 0;
6571: l_date DATE := TO_DATE('31/12/4712' , 'DD/MM/YYYY');
6572: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
6573:
6574: CURSOR c_loc is
6575: SELECT NVL(SUM(ASSIGNABLE_AREA),0) , NVL(SUM(RENTABLE_AREA),0) , NVL(SUM(USABLE_AREA),0)
6576: , NVL(SUM(COMMON_AREA),0),NVL(SUM(MAX_CAPACITY),0), NVL(SUM(OPTIMUM_CAPACITY),0)
6706: p_Location_Type IN VARCHAR2,
6707: p_Assignable_Area IN NUMBER )
6708: RETURN BOOLEAN IS
6709:
6710: v_location_rec pnp_util_func.PN_LOCATION_AREA_REC;
6711: v_space_allocation_rec pnp_util_func.PN_SPACE_AREA_REC;
6712: INVALID_ASSIGNABLE_AREA EXCEPTION;
6713:
6714: CURSOR start_date_cur IS
6707: p_Assignable_Area IN NUMBER )
6708: RETURN BOOLEAN IS
6709:
6710: v_location_rec pnp_util_func.PN_LOCATION_AREA_REC;
6711: v_space_allocation_rec pnp_util_func.PN_SPACE_AREA_REC;
6712: INVALID_ASSIGNABLE_AREA EXCEPTION;
6713:
6714: CURSOR start_date_cur IS
6715: SELECT emp_assign_start_date
6961:
6962: l_index INTEGER := 0;
6963: l_space_assign_cust_tbl SPACE_ASSIGNMENT_TBL;
6964: l_space_assign_emp_tbl SPACE_ASSIGNMENT_TBL;
6965: l_api_name VARCHAR2(50) := 'pnp_util_func.get_space_assignments';
6966:
6967: BEGIN
6968:
6969: PNP_DEBUG_PKG.DEBUG('----------------------------------');
6966:
6967: BEGIN
6968:
6969: PNP_DEBUG_PKG.DEBUG('----------------------------------');
6970: PNP_DEBUG_PKG.DEBUG('pnp_util_func.get_space_assignments (+)');
6971: PNP_DEBUG_PKG.DEBUG('Get space assignments:INPUT');
6972: PNP_DEBUG_PKG.DEBUG('----------------------------------');
6973: PNP_DEBUG_PKG.DEBUG('Location id : ' || p_location_id);
6974: PNP_DEBUG_PKG.DEBUG('Start Date : ' || p_start_date);
6989: space_assign_cust_rec.cust_space_assign_id;
6990: l_space_assign_cust_tbl(l_index).assign_start_date :=
6991: space_assign_cust_rec.cust_assign_start_date;
6992: l_space_assign_cust_tbl(l_index).assign_end_date :=
6993: NVL(space_assign_cust_rec.cust_assign_end_date,pnp_util_func.g_end_of_time);
6994: l_space_assign_cust_tbl(l_index).allocated_area :=
6995: space_assign_cust_rec.allocated_area;
6996: l_space_assign_cust_tbl(l_index).allocated_area_pct :=
6997: space_assign_cust_rec.allocated_area_pct;
7023: space_assign_emp_rec.emp_space_assign_id;
7024: l_space_assign_emp_tbl(l_index).assign_start_date :=
7025: space_assign_emp_rec.emp_assign_start_date;
7026: l_space_assign_emp_tbl(l_index).assign_end_date :=
7027: NVL(space_assign_emp_rec.emp_assign_end_date,pnp_util_func.g_end_of_time);
7028: l_space_assign_emp_tbl(l_index).allocated_area :=
7029: space_assign_emp_rec.allocated_area;
7030: l_space_assign_emp_tbl(l_index).allocated_area_pct :=
7031: space_assign_emp_rec.allocated_area_pct;
7041: END LOOP;
7042: x_space_assign_emp_tbl := l_space_assign_emp_tbl;
7043:
7044: x_return_status := FND_API.G_RET_STS_SUCCESS;
7045: PNP_DEBUG_PKG.DEBUG('pnp_util_func.get_space_assignments (-)');
7046:
7047: EXCEPTION
7048: WHEN OTHERS THEN
7049: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7096: )
7097: IS
7098: l_space_assign_cust_tbl SPACE_ASSIGNMENT_TBL;
7099: l_space_assign_emp_tbl SPACE_ASSIGNMENT_TBL;
7100: l_api_name VARCHAR2(50) := 'pnp_util_func.Validate_assignemnt_for_date ';
7101: USER_DEF_ERROR Exception;
7102:
7103: CURSOR validate_start_date_cursor IS
7104: SELECT 'x'
7153: BEGIN
7154: x_return_status := FND_API.G_RET_STS_SUCCESS;
7155:
7156: PNP_DEBUG_PKG.log('----------------------------------');
7157: PNP_DEBUG_PKG.log('pnp_util_func.validate_assignment_for_date (+)');
7158: PNP_DEBUG_PKG.log('Validate Assignment for date:INPUT');
7159: PNP_DEBUG_PKG.log('----------------------------------');
7160: PNP_DEBUG_PKG.log('Location id : ' || p_location_id);
7161: PNP_DEBUG_PKG.log('Start Date : ' || p_start_date);
7181: END LOOP;
7182:
7183: END IF;
7184:
7185: PNP_DEBUG_PKG.DEBUG('pnp_util_func.validate_assignment_for_date (-)');
7186:
7187: EXCEPTION
7188:
7189: WHEN USER_DEF_ERROR THEN
7237: l_space_assign_emp_tbl SPACE_ASSIGNMENT_TBL;
7238: l_space_assign_tbl SPACE_ASSIGNMENT_TBL;
7239: l_total_Area NUMBER := 0;
7240: l_index NUMBER := 0;
7241: l_api_name VARCHAR2(50):= 'pnp_util_func.Validate_assignable_area';
7242:
7243: UNEXPECTED_ERROR EXCEPTION;
7244: BEGIN
7245:
7243: UNEXPECTED_ERROR EXCEPTION;
7244: BEGIN
7245:
7246: PNP_DEBUG_PKG.log('----------------------------------');
7247: PNP_DEBUG_PKG.log('pnp_util_func.validate_assignable_area (+)');
7248: PNP_DEBUG_PKG.log('Validate Assignable area: INPUT');
7249: PNP_DEBUG_PKG.log('----------------------------------');
7250: PNP_DEBUG_PKG.log('Location id : ' || p_location_id);
7251: PNP_DEBUG_PKG.log('Start Date : ' || p_start_date);
7347: END LOOP;
7348:
7349: END IF;
7350:
7351: PNP_DEBUG_PKG.log('pnp_util_func.validate_assignable_area (-)');
7352: EXCEPTION
7353:
7354:
7355: WHEN UNEXPECTED_ERROR THEN
7414: IS
7415:
7416:
7417: l_space_assign_tbl SPACE_ASSIGNMENT_TBL;
7418: l_api_name VARCHAR2(50) := 'pnp_util_func.validate_date_assignable_area';
7419: l_filename VARCHAR2(40) := 'Date_EFF'||to_char(SYSDATE,'DDMMYYHHMMSS');
7420:
7421: UNEXPECTED_ERROR Exception;
7422: l_str_date_old DATE;
7432: -- Remove after debug
7433: x_return_status := FND_API.G_RET_STS_SUCCESS;
7434:
7435: PNP_DEBUG_PKG.log('---------------------------------------');
7436: PNP_DEBUG_PKG.log('pnp_util_func.validate_date_assignable_area (+)');
7437: PNP_DEBUG_PKG.log('Validate date assignable_area : INPUT PARAMS');
7438: PNP_DEBUG_PKG.log('---------------------------------------');
7439: PNP_DEBUG_PKG.log('Location id : ' || p_location_id);
7440: PNP_DEBUG_PKG.log('Location Type : ' || p_location_type);
7486:
7487: END LOOP;
7488:
7489: PNP_DEBUG_PKG.log('-----------------------------');
7490: PNP_DEBUG_PKG.log('pnp_util_func.validate_date_assignable_area (-)');
7491: PNP_DEBUG_PKG.log('-----------------------------');
7492:
7493: EXCEPTION
7494:
7505: END validate_date_assignable_area;
7506:
7507: ----------------------------------------------------
7508: -- 31-OCT-2002 graghuna o Created due for forms 6i issue.
7509: -- WHEN pnp_util_func are called from forms
7510: -- and the p_As_of_date is not passed, the
7511: -- form generates error even though
7512: -- p_as_of_date is declared as NULL in the
7513: -- package.
8001: WHERE tnc.lease_id = p_lease_id
8002: AND tnc.location_id = p_location_id
8003: AND p_as_of_date BETWEEN nvl(tnc.occupancy_date, tnc.estimated_occupancy_date) AND tnc.expiration_date;
8004:
8005: l_area_rec pnp_util_func.pn_location_area_rec;
8006: l_area NUMBER := null;
8007: l_desc VARCHAR2(100);
8008:
8009: BEGIN
8007: l_desc VARCHAR2(100);
8008:
8009: BEGIN
8010:
8011: l_desc := 'pnp_util_func.fetch_tenancy_area';
8012:
8013: pnp_debug_pkg.log(l_desc ||' (+)');
8014:
8015: FOR area_cur IN get_tenancy_info LOOP
8015: FOR area_cur IN get_tenancy_info LOOP
8016:
8017: IF p_area_type_code IN ('LOCTN_RENTABLE','LOCTN_USABLE','LOCTN_ASSIGNABLE') THEN
8018:
8019: pnp_util_func.fetch_loctn_area(
8020: p_type => pnp_util_func.get_location_type_lookup_code(
8021: p_location_id, p_as_of_date),
8022: p_location_id => p_location_id,
8023: p_as_of_date => p_as_of_date,
8016:
8017: IF p_area_type_code IN ('LOCTN_RENTABLE','LOCTN_USABLE','LOCTN_ASSIGNABLE') THEN
8018:
8019: pnp_util_func.fetch_loctn_area(
8020: p_type => pnp_util_func.get_location_type_lookup_code(
8021: p_location_id, p_as_of_date),
8022: p_location_id => p_location_id,
8023: p_as_of_date => p_as_of_date,
8024: x_area => l_area_rec);
8734:
8735: BEGIN
8736:
8737: FOR rec IN get_loc_cur(p_lease_id) LOOP
8738: l_location_name_rec := pnp_util_func.get_location_name(rec.location_id,p_as_of_date);
8739: END LOOP;
8740:
8741: RETURN NVL(l_location_name_rec.office,
8742: NVL(l_location_name_rec.floor,l_location_name_rec.building)
8778:
8779: BEGIN
8780:
8781: FOR rec IN get_loc_cur(p_lease_id) LOOP
8782: l_location_name_rec := pnp_util_func.get_location_name(rec.location_id,p_as_of_date);
8783: END LOOP;
8784:
8785: RETURN NVL(l_location_name_rec.office_location_code,
8786: NVL(l_location_name_rec.floor_location_code,l_location_name_rec.building_location_code)
8822:
8823: BEGIN
8824:
8825: FOR rec IN get_loc_cur(p_lease_id) LOOP
8826: l_location_name_rec := pnp_util_func.get_location_name(rec.location_id,p_as_of_date);
8827: END LOOP;
8828:
8829: RETURN l_location_name_rec.property_name;
8830:
8838:
8839: -------------------------------------
8840: -- End of Package --
8841: --------------------------------------
8842: END pnp_util_func;