[Home] [Help]
288: p_Cost_Center VARCHAR2,
289: p_As_Of_Date DATE )
290: RETURN NUMBER IS
291:
292: l_Location_Type pn_locations.location_type_lookup_code%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');
307: AND emp_assign_start_date <= p_As_Of_Date
308: AND NVL(emp_assign_end_date, l_date) >= p_As_Of_Date
309: AND Location_Id IN (
310: SELECT Location_Id
311: FROM PN_LOCATIONS_ALL
312: WHERE Location_Type_Lookup_Code = 'OFFICE'
313: AND p_As_Of_Date BETWEEN active_start_date AND active_end_date
314: START WITH Location_Id = p_Location_Id
315: CONNECT BY PRIOR Location_Id = Parent_Location_Id
326: WHERE cust_assign_start_date <= p_As_Of_Date
327: AND NVL(cust_assign_end_date, l_date) >= p_As_Of_Date
328: AND Location_Id IN (
329: SELECT Location_Id
330: FROM PN_LOCATIONS_ALL
331: WHERE Location_Type_Lookup_Code = 'OFFICE'
332: AND p_As_Of_Date BETWEEN active_start_date AND active_end_date
333: START WITH Location_Id = p_Location_Id
334: CONNECT BY PRIOR Location_Id = Parent_Location_Id
383:
384: /*===========================================================================+
385: -- NAME : Get_Vacant_Area
386: -- DESCRIPTION : RETURN the Vacant Area
387: -- NOTES : Currently being used in views "PN_LOCATIONS_V",
388: -- "PN_BUILDING_V", "PN_FLOORS_V", "PN_OFFICES_V"
389: -- AND form PNTSPACE.fmb ( Space Assignments form )
390: -- ASSUMPTION : Sum of Usable Areas of Offices = Usable Area of Floor
391: -- Sum of Usable Areas of Floors = Usable Area of Building
434: FUNCTION get_vacant_area ( p_location_id NUMBER,
435: p_as_of_date DATE)
436: RETURN NUMBER IS
437:
438: l_location_type pn_locations.location_type_lookup_code%type;
439: l_usable_area NUMBER;
440: l_allocated_area NUMBER;
441: l_assignable_area NUMBER;
442: l_allocated_area_emp NUMBER;
450: CURSOR Assignable_Area_C(p_Location_Id IN NUMBER
451: ,p_As_of_date IN DATE
452: ,p_location_type IN VARCHAR2) IS
453: (SELECT NVL(SUM(assignable_area), 0) AS Area
454: FROM pn_locations_all
455: WHERE location_type_lookup_code = p_location_type
456: AND status = 'A'
457: AND p_as_of_date BETWEEN active_start_date AND active_end_date
458: START WITH location_id = p_Location_Id
467: FROM pn_space_assign_emp_all
468: WHERE emp_assign_start_date <= p_as_of_date
469: AND NVL(emp_assign_end_date, l_date) >= p_as_of_date
470: AND location_id IN (SELECT Location_Id
471: FROM pn_locations_all
472: WHERE location_type_lookup_code = p_location_type
473: AND Status = 'A'
474: AND p_as_of_date BETWEEN active_start_date AND active_end_date
475: START WITH Location_Id = p_Location_Id
486: FROM pn_space_assign_cust_all
487: WHERE cust_assign_start_date <= p_As_of_date
488: AND NVL(cust_assign_end_date, l_date) >= p_As_of_date
489: AND location_Id IN (SELECT location_id
490: FROM pn_locations_all
491: WHERE location_type_lookup_code = p_location_type
492: AND status = 'A'
493: AND p_as_of_date BETWEEN active_start_date AND active_end_date
494: START WITH location_id = p_location_id
499:
500: CURSOR Assignable_Area_Child_C(p_Location_Id IN NUMBER
501: ,p_As_of_date IN DATE) IS
502: (SELECT NVL(SUM(assignable_area), 0) AS Area
503: FROM pn_locations_all
504: WHERE location_id = p_location_id
505: AND p_as_of_date BETWEEN active_start_date AND active_end_date
506: AND status = 'A'
507: );
513: WHERE E.emp_assign_start_date <= p_as_of_date
514: AND NVL(e.emp_assign_end_date, l_date) >= p_as_of_date
515: AND e.location_id = p_location_id
516: AND EXISTS (SELECT NULL
517: FROM pn_locations_all l
518: WHERE l.status = 'A'
519: AND l.location_id = p_Location_Id)
520: );
521:
526: WHERE c.cust_assign_start_date <= p_as_of_date
527: AND NVL(c.cust_assign_end_date, l_date) >= p_as_of_date
528: AND c.location_id = p_location_id
529: AND EXISTS (SELECT NULL
530: FROM pn_locations_all l
531: WHERE l.status = 'A'
532: AND l.location_id = p_location_id)
533: );
534:
634: | RETURNS:
635: | Vacant area percentage for a location_id ( building )
636: |
637: | NOTES:
638: | Currently being used in views "PN_LOCATIONS_V"
639: | "PN_BUILDING_V"
640: | AND form PNSULOCN.fmb ( Locations form )
641: |
642: | ASSUMPTION:
657:
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));
717: | RETURNS:
718: | Load Factor for a location_id ( Building/LAND )
719: |
720: | NOTES:
721: | Currently being used in views "PN_LOCATIONS_V"
722: | "PN_BUILDING_V"
723: | AND form PNSULOCN.fmb ( Locations form )
724: |
725: | ASSUMPTION:
734: FUNCTION get_load_factor ( p_Location_Id NUMBER ,
735: p_as_of_date DATE ) RETURN NUMBER IS
736:
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,
797: | RETURNS:
798: | RETURN the NUMBER of floors associated with a ( Building/LAND )
799: |
800: | NOTES:
801: | Currently being used in views "PN_LOCATIONS_V"
802: | "PN_BUILDING_V"
803: | AND form PNSULOCN.fmb ( Locations form )
804: |
805: | ASSUMPTION:
824: FUNCTION get_floors ( p_Location_Id NUMBER ,
825: p_as_of_date DATE )
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:
832:
833: CURSOR floor_count_C( p_Location_Id NUMBER
834: ,p_as_of_date DATE
835: ,p_location_type VARCHAR2) IS
836: (SELECT COUNT(pn_locations_all.floor) AS floor_count
837: FROM pn_locations_all
838: WHERE Location_Type_Lookup_Code = p_location_type
839: AND Status = 'A'
840: AND p_as_of_date BETWEEN active_start_date AND active_end_date
833: CURSOR floor_count_C( p_Location_Id NUMBER
834: ,p_as_of_date DATE
835: ,p_location_type VARCHAR2) IS
836: (SELECT COUNT(pn_locations_all.floor) AS floor_count
837: FROM pn_locations_all
838: WHERE Location_Type_Lookup_Code = p_location_type
839: AND Status = 'A'
840: AND p_as_of_date BETWEEN active_start_date AND active_end_date
841: START WITH Location_Id = p_Location_Id
902: | RETURNS:
903: | RETURN the NUMBER of offices associated with a ( Building/LAND )
904: |
905: | NOTES:
906: | Currently being used in views "PN_LOCATIONS_V"
907: | "PN_BUILDING_V"
908: | AND form PNSULOCN.fmb ( Locations form )
909: |
910: | ASSUMPTION:
927: FUNCTION get_offices ( p_Location_Id NUMBER ,
928: p_as_of_date IN DATE )
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:
951: END IF;
952:
953: SELECT COUNT(office)
954: INTO l_offices
955: FROM pn_locations_all
956: WHERE Location_Type_Lookup_Code = l_location_type --'OFFICE'
957: AND Status = 'A' --BUG#2168485
958: AND l_as_of_date BETWEEN active_start_date AND active_end_date
959: START WITH Location_Id = p_Location_Id
983:
984: /*===========================================================================+
985: -- NAME : get_utilized_capacity
986: -- DESCRIPTION : RETURN the Utilized Capacity for a given location_id
987: -- NOTES : Currently being used in view "PN_LOCATIONS_V"
988: -- "PN_BUILDING_V", "PN_FLOORS_V", "PN_OFFICES_V"
989: -- AND Space Assignments form - "PNTSPACE.fmb"
990: -- SCOPE : PUBLIC
991: -- INVOKED FROM :
1031: FUNCTION get_utilized_capacity ( p_location_id IN NUMBER,
1032: p_As_Of_Date IN DATE)
1033: RETURN NUMBER
1034: IS
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);
1042: p_As_Of_Date IN DATE) IS
1043: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1044: FROM pn_space_assign_emp_all
1045: WHERE location_id IN (SELECT a.location_id
1046: FROM pn_locations_all a
1047: WHERE p_As_Of_Date BETWEEN active_start_date AND
1048: active_end_date
1049: START WITH a.location_id = p_location_id
1050: CONNECT BY PRIOR a.location_id = a.parent_location_id
1058: ,p_As_Of_Date IN DATE) IS
1059: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1060: FROM pn_space_assign_cust_all
1061: WHERE location_id IN (SELECT a.location_id
1062: FROM pn_locations_all a
1063: WHERE p_as_of_date BETWEEN active_start_date AND
1064: active_end_date
1065: START WITH a.location_id = p_location_id
1066: CONNECT BY PRIOR a.location_id = a.parent_location_id
1158: | OUT: none
1159: |
1160: | RETURNS : Vacant capacity for a location (building/LAND)
1161: |
1162: | NOTES : Currently being used in view "PN_LOCATIONS_V"
1163: | "PN_BUILDING_V"
1164: | AND Space Assignments form - "PNTSPACE.fmb"
1165: |
1166: | MODIFICATION HISTORY
1178: FUNCTION get_vacancy ( p_Location_Id NUMBER,
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;
1230: | OUT: none
1231: |
1232: | RETURNS : Occupancy Percentage for a location (building/LAND)
1233: |
1234: | NOTES : Currently being used in view "PN_LOCATIONS_V"
1235: | "PN_BUILDING_V"
1236: | AND Space Assignments form - "PNTSPACE.fmb"
1237: |
1238: | MODIFICATION HISTORY
1247:
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;
1306: | OUT: none
1307: |
1308: | RETURNS : Utilized Area for a location (building/LAND)
1309: |
1310: | NOTES : Currently being used in view "PN_LOCATIONS_V"
1311: | "PN_BUILDING_V"
1312: | AND Space Assignments form - "PNTSPACE.fmb"
1313: |
1314: | MODIFICATION HISTORY
1324: FUNCTION get_area_utilized ( p_Location_Id NUMBER,
1325: p_as_of_date IN DATE
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));
1420: p_leaseId IN NUMBER,
1421: p_as_of_date IN DATE ) IS
1422:
1423: (SELECT NVL(SUM(pnl.RENTABLE_AREA),0) AS Area
1424: FROM pn_locations_all pnl,
1425: pn_tenancies_all pnt
1426: WHERE pnt.lease_id = p_leaseId
1427: AND pnt.status = 'A'
1428: AND pnl.location_id = pnt.location_id
2017: Begin
2018:
2019: SELECT 'X'
2020: INTO l_Dummy
2021: FROM pn_locations_all
2022: WHERE Status = 'A'
2023: AND Location_Id = p_Location_Id
2024: AND l_as_of_date BETWEEN active_start_date AND active_end_date;
2025:
2044: l_seqnum NUMBER;
2045:
2046: begin
2047:
2048: SELECT pn_locations_s.NEXTVAL
2049: INTO l_seqnum
2050: FROM DUAL;
2051:
2052: RETURN l_seqnum;
2306: p_location_id NUMBER,
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
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
2316: WHERE location_id = p_location_id
2317: AND p_as_of_date BETWEEN active_start_date AND active_end_date;
2318:
2319: CURSOR fetch_loc_code_ignore_date IS
2317: AND p_as_of_date BETWEEN active_start_date AND active_end_date;
2318:
2319: CURSOR fetch_loc_code_ignore_date IS
2320: SELECT location_code
2321: FROM pn_locations_all
2322: WHERE location_id = p_location_id
2323: AND ROWNUM < 2;
2324:
2325: BEGIN
2350: FUNCTION Get_Location_Type_Lookup_Code (p_location_id NUMBER ,
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
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
2360: WHERE location_id = p_location_id
2361: AND l_as_of_date BETWEEN active_start_date AND active_end_date;
2362:
2363: CURSOR fetch_code_ignore_date IS
2361: AND l_as_of_date BETWEEN active_start_date AND active_end_date;
2362:
2363: CURSOR fetch_code_ignore_date IS
2364: SELECT location_type_lookup_code
2365: FROM pn_locations_all
2366: WHERE location_id = p_location_id
2367: AND ROWNUM < 2;
2368:
2369: BEGIN
3416: | RETURNS:
3417: | RETURN the sum of rentable_area of offices associated with a ( Building/LAND )
3418: |
3419: | NOTES:
3420: | Currently being used in views "PN_LOCATIONS_V"
3421: | "PN_BUILDING_V"
3422: | AND form PNSULOCN.fmb ( Locations form )
3423: |
3424: | ASSUMPTION:
3439: FUNCTION get_building_rentable_area ( p_Location_Id NUMBER ,
3440: p_as_of_date IN DATE
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:
3493: | RETURNS:
3494: | RETURN the sum of usable_area of offices associated with a ( Building/LAND )
3495: |
3496: | NOTES:
3497: | Currently being used in views "PN_LOCATIONS_V"
3498: | "PN_BUILDING_V"
3499: | AND form PNSULOCN.fmb ( Locations form )
3500: |
3501: | ASSUMPTION:
3518: FUNCTION get_building_usable_area ( p_Location_Id NUMBER ,
3519: p_as_of_date IN DATE
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:
3572: | RETURNS:
3573: | RETURN the sum of assignable_area of offices associated with a ( Building/LAND )
3574: |
3575: | NOTES:
3576: | Currently being used in views "PN_LOCATIONS_V"
3577: | "PN_BUILDING_V"
3578: | AND form PNSULOCN.fmb ( Locations form )
3579: |
3580: | ASSUMPTION:
3597: p_Location_Id NUMBER ,
3598: p_as_of_date IN DATE
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:
3651: | RETURNS:
3652: | RETURN the sum of rentable_area of offices associated with a ( Floor/Parcel )
3653: |
3654: | NOTES:
3655: | Currently being used in views "PN_LOCATIONS_V"
3656: | "PN_BUILDING_V"
3657: | AND form PNSULOCN.fmb ( Locations form )
3658: |
3659: | ASSUMPTION:
3674: p_as_of_date IN DATE
3675: )
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:
3728: | RETURNS:
3729: | RETURN the sum of usable_area of offices associated with a ( Floor/Parcel )
3730: |
3731: | NOTES:
3732: | Currently being used in views "PN_LOCATIONS_V"
3733: | "PN_BUILDING_V"
3734: | AND form PNSULOCN.fmb ( Locations form )
3735: |
3736: | ASSUMPTION:
3752: FUNCTION get_floor_usable_area ( p_Location_Id IN NUMBER ,
3753: p_as_of_date IN DATE )
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:
3806: | RETURNS:
3807: | RETURN the sum of assignable_area of offices associated with a ( Floor/Parcel )
3808: |
3809: | NOTES:
3810: | Currently being used in views "PN_LOCATIONS_V"
3811: | "PN_BUILDING_V"
3812: | AND form PNSULOCN.fmb ( Locations form )
3813: |
3814: | ASSUMPTION:
3831: FUNCTION get_floor_assignable_area ( p_Location_Id NUMBER ,
3832: p_as_of_date IN DATE )
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:
3885: | RETURNS:
3886: | RETURN the sum of max_capacity of offices associated with a ( Floor/Parcel )
3887: |
3888: | NOTES:
3889: | Currently being used in views "PN_LOCATIONS_V"
3890: | "PN_BUILDING_V"
3891: | AND form PNSULOCN.fmb ( Locations form )
3892: |
3893: | ASSUMPTION:
3911: p_Location_Id NUMBER ,
3912: p_as_of_date IN DATE
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:
3965: | RETURNS:
3966: | RETURN the sum of optimum_capacity of offices associated with a ( Floor/Parcel )
3967: |
3968: | NOTES:
3969: | Currently being used in views "PN_LOCATIONS_V"
3970: | "PN_BUILDING_V"
3971: | AND form PNSULOCN.fmb ( Locations form )
3972: |
3973: | ASSUMPTION:
3989:
3990: FUNCTION get_floor_optimum_capacity ( p_Location_Id NUMBER ,
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:
4044: | RETURNS:
4045: | RETURN the sum of max_capacity of offices associated with a ( Building/LAND )
4046: |
4047: | NOTES:
4048: | Currently being used in views "PN_LOCATIONS_V"
4049: | "PN_BUILDING_V"
4050: | AND form PNSULOCN.fmb ( Locations form )
4051: |
4052: | ASSUMPTION:
4068:
4069: FUNCTION get_building_max_capacity ( p_Location_Id NUMBER ,
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:
4122: | RETURNS:
4123: | RETURN the sum of optimum_capacity of offices associated with a ( Building/LAND )
4124: |
4125: | NOTES:
4126: | Currently being used in views "PN_LOCATIONS_V"
4127: | "PN_BUILDING_V"
4128: | AND form PNSULOCN.fmb ( Locations form )
4129: |
4130: | ASSUMPTION:
4146:
4147: FUNCTION get_building_optimum_capacity ( p_Location_Id NUMBER ,
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:
4198: | OUT: none
4199: |
4200: | RETURNS : Vacant capacity for a location (Floor/Parcel)
4201: |
4202: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4203: | "PN_BUILDING_V"
4204: | AND Space Assignments form - "PNTSPACE.fmb"
4205: |
4206: | MODIFICATION HISTORY
4218:
4219: FUNCTION get_floor_vacancy ( p_Location_Id NUMBER,
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);
4274: | OUT: none
4275: |
4276: | RETURNS : Vacant capacity for a location (Office/Section)
4277: |
4278: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4279: | "PN_BUILDING_V"
4280: | AND Space Assignments form - "PNTSPACE.fmb"
4281: |
4282: | MODIFICATION HISTORY
4297:
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);
4308: CURSOR Office_Vacancy_C( p_Location_Id IN NUMBER
4309: ,p_as_of_date IN DATE
4310: ,p_location_type IN VARCHAR2) IS
4311: (SELECT NVL((max_capacity), 0) AS vacancy
4312: FROM pn_locations_all
4313: WHERE Location_Type_Lookup_Code = p_location_type
4314: AND p_as_of_date BETWEEN active_start_date AND active_end_date
4315: AND Location_Id = p_Location_Id
4316: );
4396: FROM pn_space_assign_emp_all
4397: WHERE emp_assign_start_date > l_date
4398: --Bug#5959164 AND allocated_area_pct > 0
4399: AND location_id IN (SELECT location_id
4400: FROM pn_locations_all
4401: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4402: START WITH location_id = p_Location_Id
4403: CONNECT BY PRIOR location_id = parent_location_id
4404: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4407: SELECT 'Y'
4408: FROM pn_space_assign_emp_all
4409: WHERE l_date BETWEEN emp_assign_start_date AND emp_assign_end_date
4410: AND location_id IN (SELECT location_id
4411: FROM pn_locations_all
4412: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4413: START WITH location_id = p_Location_Id
4414: CONNECT BY PRIOR location_id = parent_location_id
4415: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4418: SELECT 'Y'
4419: FROM pn_space_assign_emp_all
4420: WHERE l_date >= emp_assign_start_date AND emp_assign_end_date IS NULL -- for open assignments time
4421: AND location_id IN (SELECT location_id
4422: FROM pn_locations_all
4423: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4424: START WITH location_id = p_Location_Id
4425: CONNECT BY PRIOR location_id = parent_location_id
4426: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4430: FROM pn_space_assign_cust_all
4431: WHERE cust_assign_start_date > l_date
4432: --Bug#5959164 AND allocated_area_pct > 0
4433: AND location_id IN (SELECT location_id
4434: FROM pn_locations_all
4435: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4436: START WITH location_id = p_Location_Id
4437: CONNECT BY PRIOR location_id = parent_location_id
4438: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4441: SELECT 'Y'
4442: FROM pn_space_assign_cust_all
4443: WHERE l_date BETWEEN cust_assign_start_date AND cust_assign_end_date
4444: AND location_id IN (SELECT location_id
4445: FROM pn_locations_all
4446: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4447: START WITH location_id = p_Location_Id
4448: CONNECT BY PRIOR location_id = parent_location_id
4449: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4452: SELECT 'Y'
4453: FROM pn_space_assign_cust_all
4454: WHERE l_date >= cust_assign_start_date AND cust_assign_end_date IS NULL -- for open assignments time
4455: AND location_id IN (SELECT location_id
4456: FROM pn_locations_all
4457: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4458: START WITH location_id = p_Location_Id
4459: CONNECT BY PRIOR location_id = parent_location_id
4460: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4529: | OUT: none
4530: |
4531: | RETURNS : secondary_circulation_area for a given location_id ( Floor/Parcel )
4532: |
4533: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4534: | "PN_BUILDING_V"
4535: | AND Space Assignments form - "PNTSPACE.fmb"
4536: |
4537: | MODIFICATION HISTORY
4541:
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;
4593: |
4594: | RETURNS : secondary_circulation_area for a given location_id
4595: | ( Office/Section )
4596: |
4597: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4598: | "PN_BUILDING_V"
4599: | AND Space Assignments form - "PNTSPACE.fmb"
4600: |
4601: | MODIFICATION HISTORY
4613:
4614: FUNCTION get_office_secondary_area ( p_Location_Id NUMBER ,
4615: p_as_of_date IN DATE ) RETURN NUMBER IS
4616:
4617: l_Location_Type pn_locations.location_type_lookup_code%type;
4618: l_assignable_area NUMBER;
4619: l_usable_area NUMBER;
4620: l_common_area NUMBER;
4621: l_secondary_area NUMBER;
4627: ,p_location_type IN VARCHAR2) IS
4628: (SELECT NVL((USABLE_AREA), 0) AS usable_area
4629: ,NVL((ASSIGNABLE_AREA), 0) AS assignable_area
4630: ,NVL((COMMON_AREA), 0) AS common_area
4631: FROM pn_locations_all
4632: WHERE Location_Type_Lookup_Code = p_location_type
4633: AND p_as_of_date BETWEEN active_start_date AND active_end_date
4634: AND Location_Id = p_Location_Id
4635: );
4690: | RETURNS:
4691: | RETURN the sum of common areas of offices associated with a ( Floor/Parcel )
4692: |
4693: | NOTES:
4694: | Currently being used in views "PN_LOCATIONS_V"
4695: | "PN_BUILDING_V"
4696: | AND form PNSULOCN.fmb ( Locations form )
4697: |
4698: | ASSUMPTION:
4713:
4714: FUNCTION get_floor_common_area ( p_Location_Id NUMBER ,
4715: p_as_of_date IN DATE ) RETURN NUMBER IS
4716:
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);
4736: end if;
4737:
4738: SELECT NVL(SUM(COMMON_AREA),0)
4739: INTO l_common_area
4740: FROM pn_locations_all
4741: WHERE Location_Type_Lookup_Code = l_location_type --'OFFICE'
4742: AND Status = 'A'
4743: AND l_as_of_date BETWEEN active_start_date AND active_end_date
4744: START WITH Location_Id = p_Location_Id
4797:
4798: FUNCTION get_building_common_area ( p_Location_Id NUMBER ,
4799: p_as_of_date IN DATE ) RETURN NUMBER IS
4800:
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);
4820: end if;
4821:
4822: SELECT NVL(SUM(COMMON_AREA),0)
4823: INTO l_common_area
4824: FROM pn_locations_all
4825: WHERE Location_Type_Lookup_Code = l_location_type
4826: AND Status = 'A'
4827: AND l_as_of_date BETWEEN active_start_date AND active_end_date
4828: START WITH Location_Id = p_Location_Id
4866: |
4867: | OUT:
4868: | none
4869: |
4870: | RETURNS : parent_location_id FROM PN_LOCATIONS
4871: |
4872: | NOTES :
4873: |
4874: | MODIFICATION HISTORY
4887: IS
4888:
4889: CURSOR get_parent_loc_id IS
4890: SELECT parent_location_id
4891: FROM pn_locations_all
4892: WHERE location_id = p_location_id;
4893:
4894: l_parent_location_id NUMBER;
4895: BEGIN
5029: -- 30-OCT-02 Satish Tripathi o Access _all table for performance issues.
5030: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity
5031: -- 20-OCT-03 ftanudja o removed nvl from locn tbl filter. 3197410.
5032: -- 02-JUL-04 Satish Tripathi o Fixed for 3740584, added l_as_of_date BETWEEN
5033: -- ... for each pn_locations_all table.
5034: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_PROPERTIES
5035: -- with _ALL table.
5036: +============================================================================*/
5037:
5041: RETURN location_name_rec
5042: IS
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
5049: BEGIN
5050:
5051: SELECT location_type_lookup_code,parent_location_id
5052: INTO l_location_type_lookup_code,l_parent_location_id
5053: FROM pn_locations_all
5054: WHERE location_id = p_Location_Id
5055: AND p_as_of_date BETWEEN active_start_date AND active_end_date;
5056:
5057: IF l_location_type_lookup_code IN ('OFFICE','SECTION') THEN
5060: b.FLOOR,c.location_code building_location_code,c.BUILDING,
5061: prop.property_code,prop.property_name,
5062: d.name office_park_name, e.name region_name
5063: INTO l_location_name_rec
5064: FROM pn_locations_all a,
5065: pn_locations_all b,
5066: pn_locations_all c,
5067: pn_location_parks d,
5068: pn_location_parks e,
5061: prop.property_code,prop.property_name,
5062: d.name office_park_name, e.name region_name
5063: INTO l_location_name_rec
5064: FROM pn_locations_all a,
5065: pn_locations_all b,
5066: pn_locations_all c,
5067: pn_location_parks d,
5068: pn_location_parks e,
5069: pn_properties_all prop
5062: d.name office_park_name, e.name region_name
5063: INTO l_location_name_rec
5064: FROM pn_locations_all a,
5065: pn_locations_all b,
5066: pn_locations_all c,
5067: pn_location_parks d,
5068: pn_location_parks e,
5069: pn_properties_all prop
5070: WHERE a.location_id = p_Location_Id
5087: b.FLOOR,c.location_code building_location_code,c.BUILDING,
5088: prop.property_code,prop.property_name,
5089: d.name office_park_name, e.name region_name
5090: INTO l_location_name_rec
5091: FROM pn_locations_all b,
5092: pn_locations_all c,
5093: pn_location_parks d,
5094: pn_location_parks e,
5095: pn_properties_all prop
5088: prop.property_code,prop.property_name,
5089: d.name office_park_name, e.name region_name
5090: INTO l_location_name_rec
5091: FROM pn_locations_all b,
5092: pn_locations_all c,
5093: pn_location_parks d,
5094: pn_location_parks e,
5095: pn_properties_all prop
5096: WHERE b.location_id = p_Location_Id
5111: '' FLOOR,c.location_code building_location_code,c.BUILDING,
5112: prop.property_code,prop.property_name,
5113: d.name office_park_name, e.name region_name
5114: INTO l_location_name_rec
5115: FROM pn_locations_all c,
5116: pn_location_parks d,
5117: pn_location_parks e,
5118: pn_properties_all prop
5119: WHERE c.location_id = p_Location_Id
5255: /* Getting rentable area for Office/Section */
5256:
5257: SELECT NVL(rentable_area,0)
5258: INTO l_rentable_area
5259: FROM pn_locations_all
5260: WHERE location_id = p_location_id
5261: AND active_start_date <= l_as_of_date
5262: AND active_end_date >= l_as_of_date;
5263:
5420: p_as_of_date IN DATE )
5421:
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
5421:
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
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
5431: WHERE location_type_lookup_code = l_loc_type
5432: AND l_as_of_date BETWEEN active_start_date AND active_end_date
5433: Start with location_id = p_location_id
5434: CONNECT BY PRIOR parent_location_id = location_id
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
6119: FROM pn_locations_all
6120: WHERE location_id = p_location_id
6121: AND active_start_date <= l_end_dt
6122: AND active_end_date >= p_st_date;
6123:
6548: p_loc_area OUT NOCOPY PN_LOCATION_AREA_REC,
6549: p_space_area OUT NOCOPY PN_SPACE_AREA_REC
6550: )
6551: IS
6552: l_location_type_lookup_code pn_locations.location_type_lookup_code%type;
6553: l_assignable_area NUMBER := 0;
6554: l_rentable_area NUMBER := 0;
6555: l_usable_area NUMBER := 0;
6556: l_common_area NUMBER := 0;
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)
6577: FROM pn_locations_all
6578: WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
6579: AND Status = 'A'
6580: AND l_as_of_date BETWEEN active_start_date AND active_end_date
6581: START WITH Location_Id = p_Location_Id
6588: FROM PN_SPACE_ASSIGN_EMP_ALL
6589: WHERE emp_assign_start_date <= l_as_of_date
6590: AND NVL(emp_assign_end_date, l_date) >= l_as_of_date
6591: AND Location_Id IN (SELECT Location_Id
6592: FROM pn_locations_all
6593: WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
6594: AND Status = 'A'
6595: AND l_as_of_date BETWEEN active_start_date AND active_end_date
6596: START WITH Location_Id = p_Location_Id
6604: FROM PN_SPACE_ASSIGN_CUST_ALL
6605: WHERE cust_assign_start_date <= l_as_of_date
6606: AND NVL(cust_assign_end_date, l_date) >= l_as_of_date
6607: AND Location_Id IN (SELECT Location_Id
6608: FROM pn_locations_all
6609: WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
6610: AND Status = 'A'
6611: AND l_as_of_date BETWEEN active_start_date AND active_end_date
6612: START WITH Location_Id = p_Location_Id
6939: SELECT *
6940: FROM pn_space_assign_cust_all
6941: WHERE location_id IN
6942: ( SELECT location_id
6943: FROM pn_locations_all
6944: START WITH location_id = p_location_id
6945: CONNECT BY PRIOR location_id = parent_location_id )
6946: AND NVL(cust_assign_end_date,g_end_of_time) >= p_start_date
6947: AND cust_assign_start_date <= NVL(p_end_date, g_end_of_time);
6951: SELECT *
6952: FROM pn_space_assign_emp_all
6953: WHERE location_id IN
6954: ( SELECT location_id
6955: FROM pn_locations_all
6956: START WITH location_id = p_location_id
6957: CONNECT BY PRIOR location_id = parent_location_id )
6958: AND NVL(emp_assign_end_date,g_end_of_time) >= p_start_date
6959: AND emp_assign_start_date <= NVL(p_end_date, g_end_of_time);
7105: FROM DUAL
7106: WHERE EXISTS (SELECT 'x'
7107: FROM pn_space_assign_emp_all
7108: WHERE location_id IN (SELECT location_id
7109: FROM pn_locations_all
7110: START WITH location_id = p_location_id
7111: CONNECT BY PRIOR location_id = parent_location_id )
7112: --Bug#5959164 AND allocated_area > 0
7113: --Bug#5959164 AND allocated_area_pct > 0
7116: UNION
7117: SELECT 'x'
7118: FROM pn_space_assign_cust_all
7119: WHERE location_id IN (SELECT location_id
7120: FROM pn_locations_all
7121: START WITH location_id = p_location_id
7122: CONNECT BY PRIOR location_id = parent_location_id )
7123: --Bug#5959164 AND allocated_area > 0
7124: --Bug#5959164 AND allocated_area_pct > 0
7130: FROM DUAL
7131: WHERE EXISTS (SELECT 'x'
7132: FROM pn_space_assign_emp_all
7133: WHERE location_id IN (SELECT location_id
7134: FROM pn_locations_all
7135: START WITH location_id = p_location_id
7136: CONNECT BY PRIOR location_id = parent_location_id )
7137: --Bug#5959164 AND allocated_area > 0
7138: --Bug#5959164 AND allocated_area_pct > 0
7141: UNION
7142: SELECT 'x'
7143: FROM pn_space_assign_cust_all
7144: WHERE location_id IN (SELECT location_id
7145: FROM pn_locations_all
7146: START WITH location_id = p_location_id
7147: CONNECT BY PRIOR location_id = parent_location_id )
7148: --Bug#5959164 AND allocated_area > 0
7149: --Bug#5959164 AND allocated_area_pct > 0
7422: l_str_date_old DATE;
7423:
7424: CURSOR pn_location_cursor IS
7425: SELECT *
7426: FROM pn_locations_all
7427: WHERE location_id = p_location_id
7428: AND active_start_date = p_Active_start_date_old
7429: AND active_end_date = p_active_end_date_old;
7430:
7549: IS
7550:
7551: CURSOR locations_cursor Is
7552: SELECT *
7553: FROM pn_locations_all
7554: WHERE location_id = p_location_id
7555: AND (active_start_date <= p_start_Date AND active_end_Date >= p_end_date);
7556:
7557: BEGIN
7579: -- REFERENCE :
7580: -- HISTORY :
7581: -- 24-jun-03 Kiran o Created. CAM impact on Locations.
7582: -- 20-OCT-03 ftanudja o removed GROUP BY expression. 3197410.
7583: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced pn_tenancies and pn_locations
7584: -- with _ALL table.
7585: -------------------------------------------------------------------------------
7586:
7587: FUNCTION Exist_Tenancy_For_End_Date
7597: (select tenancy_id
7598: from pn_tenancies_all
7599: where location_id in
7600: (select loc.location_id
7601: from pn_locations_all loc
7602: connect by prior loc.location_id = loc.parent_location_id
7603: start with loc.location_id = p_Location_Id)
7604: and primary_flag = 'Y'
7605: and EXPIRATION_DATE > p_New_End_Date
7625: -- REFERENCE :
7626: -- HISTORY :
7627: -- 24-jun-03 Kiran o Created. CAM impact on Locations.
7628: -- 20-OCT-03 ftanudja o removed GROUP BY expression. 3197410.
7629: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced pn_tenancies and pn_locations
7630: -- with _ALL table.
7631: -------------------------------------------------------------------------------
7632:
7633: FUNCTION Exist_Tenancy_For_Start_Date
7643: (select tenancy_id
7644: from pn_tenancies_all
7645: where location_id in
7646: (select loc.location_id
7647: from pn_locations_all loc
7648: connect by prior loc.location_id = loc.parent_location_id
7649: start with loc.location_id = p_Location_Id)
7650: and primary_flag = 'Y'
7651: and nvl(OCCUPANCY_DATE,ESTIMATED_OCCUPANCY_DATE) < p_New_Start_Date
7674: -- HISTORY :
7675: -- 24-jun-03 Kiran o Created. CAM impact on Locations.
7676: -- 20-OCT-03 ftanudja o removed GROUP BY expression. 3197410.
7677: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced pn_rec_arcl_dtlln and
7678: -- pn_locations with _ALL table.
7679: -------------------------------------------------------------------------------
7680: FUNCTION Exist_Area_Class_Dtls_For_Loc
7681: ( p_Location_Id IN NUMBER,
7682: p_active_start_date IN DATE default NULL,
7690: (select area_class_dtl_line_id
7691: from pn_rec_arcl_dtlln_all
7692: where location_id in
7693: (select loc.location_id
7694: from pn_locations_all loc
7695: connect by prior loc.location_id = loc.parent_location_id
7696: start with loc.location_id = p_Location_Id)
7697: );
7698:
7859: loc.rentable_area,
7860: loc.usable_area,
7861: loc.assignable_area
7862: FROM pn_payment_terms_all trm,
7863: pn_locations_all loc
7864: WHERE trm.lease_id = p_lease_id
7865: AND loc.location_id = p_loc_id
7866: AND trm.location_id = p_loc_id
7867: AND trm.area_type_code NOT IN ('OTHER')
8089: usable_area,
8090: assignable_area,
8091: active_start_date,
8092: active_end_date
8093: FROM pn_locations_all
8094: WHERE location_id = p_ofc_loc_id;
8095:
8096: CURSOR get_affected_payment_terms (p_ofc_id NUMBER, p_flr_id NUMBER, p_bld_id NUMBER,
8097: p_start_date DATE, p_end_date DATE) IS
8116: CURSOR get_flr_area (p_as_of_date DATE) IS
8117: SELECT sum(nvl(rentable_area,0)) rentable,
8118: sum(nvl(usable_area,0)) usable,
8119: sum(nvl(assignable_area,0)) assignable
8120: FROM pn_locations_all loc
8121: WHERE loc.parent_location_id = p_flr_loc_id
8122: AND p_as_of_date BETWEEN active_start_date AND active_end_date
8123: AND location_id <> p_ofc_loc_id;
8124:
8126: CURSOR get_bld_area (p_as_of_date DATE) IS
8127: SELECT sum(nvl(o.rentable_area,0)) rentable,
8128: sum(nvl(o.usable_area,0)) usable,
8129: sum(nvl(o.assignable_area,0)) assignable
8130: FROM pn_locations_all f, pn_locations_all o
8131: WHERE p_bld_loc_id = f.parent_location_id
8132: AND f.location_id = o.parent_location_id
8133: AND p_as_of_date BETWEEN f.active_start_date AND f.active_end_date
8134: AND p_as_of_date BETWEEN o.active_start_date AND o.active_end_date
8278: nvl(sum(ofc.usable_area),0) usable,
8279: nvl(sum(ofc.assignable_area),0) assignable,
8280: nvl(sum(ofc.max_capacity),0) max_capacity,
8281: nvl(sum(ofc.optimum_capacity),0) optimum_capacity
8282: FROM pn_locations_all ofc,
8283: pn_locations_all flr
8284: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8285: AND p_as_of_date BETWEEN flr.active_start_date AND flr.active_end_date
8286: AND flr.parent_location_id = p_location_id
8279: nvl(sum(ofc.assignable_area),0) assignable,
8280: nvl(sum(ofc.max_capacity),0) max_capacity,
8281: nvl(sum(ofc.optimum_capacity),0) optimum_capacity
8282: FROM pn_locations_all ofc,
8283: pn_locations_all flr
8284: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8285: AND p_as_of_date BETWEEN flr.active_start_date AND flr.active_end_date
8286: AND flr.parent_location_id = p_location_id
8287: AND ofc.parent_location_id = flr.location_id;
8291: nvl(sum(ofc.usable_area),0) usable,
8292: nvl(sum(ofc.assignable_area),0) assignable,
8293: nvl(sum(ofc.max_capacity),0) max_capacity,
8294: nvl(sum(ofc.optimum_capacity),0) optimum_capacity
8295: FROM pn_locations_all ofc
8296: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8297: AND ofc.parent_location_id = p_location_id;
8298:
8299: CURSOR office_area IS
8301: usable_area usable,
8302: assignable_area assignable,
8303: max_capacity max_capacity,
8304: optimum_capacity optimum_capacity
8305: FROM pn_locations_all ofc
8306: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8307: AND ofc.location_id = p_location_id;
8308:
8309: BEGIN