[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
436: FUNCTION get_vacant_area ( p_location_id NUMBER,
437: p_as_of_date DATE)
438: RETURN NUMBER IS
439:
440: l_location_type pn_locations.location_type_lookup_code%type;
441: l_usable_area NUMBER;
442: l_allocated_area NUMBER;
443: l_assignable_area NUMBER;
444: l_allocated_area_emp NUMBER;
452: CURSOR Assignable_Area_C(p_Location_Id IN NUMBER
453: ,p_As_of_date IN DATE
454: ,p_location_type IN VARCHAR2) IS
455: (SELECT NVL(SUM(assignable_area), 0) AS Area
456: FROM pn_locations_all
457: WHERE location_type_lookup_code = p_location_type
458: AND status = 'A'
459: AND p_as_of_date BETWEEN active_start_date AND active_end_date
460: START WITH location_id = p_Location_Id
469: FROM pn_space_assign_emp_all
470: WHERE emp_assign_start_date <= p_as_of_date
471: AND NVL(emp_assign_end_date, l_date) >= p_as_of_date
472: AND location_id IN (SELECT Location_Id
473: FROM pn_locations_all
474: WHERE location_type_lookup_code = p_location_type
475: AND Status = 'A'
476: AND p_as_of_date BETWEEN active_start_date AND active_end_date
477: START WITH Location_Id = p_Location_Id
484: CURSOR c_location_id (p_Location_Id IN NUMBER
485: ,p_As_of_date IN DATE
486: ,p_location_type IN VARCHAR2) IS
487: SELECT Location_Id
488: FROM pn_locations_all
489: WHERE location_type_lookup_code = p_location_type
490: AND Status = 'A'
491: AND p_as_of_date BETWEEN active_start_date AND active_end_date
492: START WITH Location_Id = p_Location_Id
510: FROM pn_space_assign_cust_all
511: WHERE cust_assign_start_date <= p_As_of_date
512: AND NVL(cust_assign_end_date, l_date) >= p_As_of_date
513: AND location_Id IN (SELECT location_id
514: FROM pn_locations_all
515: WHERE location_type_lookup_code = p_location_type
516: AND status = 'A'
517: AND p_as_of_date BETWEEN active_start_date AND active_end_date
518: START WITH location_id = p_location_id
523:
524: CURSOR Assignable_Area_Child_C(p_Location_Id IN NUMBER
525: ,p_As_of_date IN DATE) IS
526: (SELECT NVL(SUM(assignable_area), 0) AS Area
527: FROM pn_locations_all
528: WHERE location_id = p_location_id
529: AND p_as_of_date BETWEEN active_start_date AND active_end_date
530: AND status = 'A'
531: );
537: WHERE E.emp_assign_start_date >= p_as_of_date
538: AND NVL(e.emp_assign_end_date, l_date) >= p_as_of_date
539: AND e.location_id = p_location_id
540: AND EXISTS (SELECT NULL
541: FROM pn_locations_all l
542: WHERE l.status = 'A'
543: AND l.location_id = p_Location_Id)
544: );
545:
550: WHERE c.cust_assign_start_date <= p_as_of_date
551: AND NVL(c.cust_assign_end_date, l_date) >= p_as_of_date
552: AND c.location_id = p_location_id
553: AND EXISTS (SELECT NULL
554: FROM pn_locations_all l
555: WHERE l.status = 'A'
556: AND l.location_id = p_location_id)
557: );
558:
713: | RETURNS:
714: | Vacant area percentage for a location_id ( building )
715: |
716: | NOTES:
717: | Currently being used in views "PN_LOCATIONS_V"
718: | "PN_BUILDING_V"
719: | AND form PNSULOCN.fmb ( Locations form )
720: |
721: | ASSUMPTION:
736:
737: FUNCTION get_vacant_area_percent ( p_Location_Id NUMBER ,
738: p_as_of_date DATE ) RETURN NUMBER IS
739:
740: l_Location_Type pn_locations.location_type_lookup_code%type;
741: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
742: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,
743: pnp_util_func.get_as_of_date(p_as_of_date));
744: l_Assignable_Area NUMBER:= get_building_assignable_area ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
796: | RETURNS:
797: | Load Factor for a location_id ( Building/LAND )
798: |
799: | NOTES:
800: | Currently being used in views "PN_LOCATIONS_V"
801: | "PN_BUILDING_V"
802: | AND form PNSULOCN.fmb ( Locations form )
803: |
804: | ASSUMPTION:
813: FUNCTION get_load_factor ( p_Location_Id NUMBER ,
814: p_as_of_date DATE ) RETURN NUMBER IS
815:
816:
817: l_Location_Type pn_locations.location_type_lookup_code%type;
818:
819: l_Rentable_Area NUMBER:= get_building_rentable_area (p_location_id,
820: pnp_util_func.get_as_of_date(p_as_of_date));
821: l_Usable_Area NUMBER:= get_building_usable_area ( p_location_id,
876: | RETURNS:
877: | RETURN the NUMBER of floors associated with a ( Building/LAND )
878: |
879: | NOTES:
880: | Currently being used in views "PN_LOCATIONS_V"
881: | "PN_BUILDING_V"
882: | AND form PNSULOCN.fmb ( Locations form )
883: |
884: | ASSUMPTION:
903: FUNCTION get_floors ( p_Location_Id NUMBER ,
904: p_as_of_date DATE )
905: RETURN NUMBER IS
906:
907: l_Location_Type pn_locations.location_type_lookup_code%type;
908: l_floors NUMBER;
909: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
910: INVALID_LOCATION_TYPE EXCEPTION;
911:
911:
912: CURSOR floor_count_C( p_Location_Id NUMBER
913: ,p_as_of_date DATE
914: ,p_location_type VARCHAR2) IS
915: (SELECT COUNT(pn_locations_all.floor) AS floor_count
916: FROM pn_locations_all
917: WHERE Location_Type_Lookup_Code = p_location_type
918: AND Status = 'A'
919: AND p_as_of_date BETWEEN active_start_date AND active_end_date
912: CURSOR floor_count_C( p_Location_Id NUMBER
913: ,p_as_of_date DATE
914: ,p_location_type VARCHAR2) IS
915: (SELECT COUNT(pn_locations_all.floor) AS floor_count
916: FROM pn_locations_all
917: WHERE Location_Type_Lookup_Code = p_location_type
918: AND Status = 'A'
919: AND p_as_of_date BETWEEN active_start_date AND active_end_date
920: START WITH Location_Id = p_Location_Id
981: | RETURNS:
982: | RETURN the NUMBER of offices associated with a ( Building/LAND )
983: |
984: | NOTES:
985: | Currently being used in views "PN_LOCATIONS_V"
986: | "PN_BUILDING_V"
987: | AND form PNSULOCN.fmb ( Locations form )
988: |
989: | ASSUMPTION:
1006: FUNCTION get_offices ( p_Location_Id NUMBER ,
1007: p_as_of_date IN DATE )
1008: RETURN NUMBER IS
1009:
1010: l_Location_Type pn_locations.location_type_lookup_code%type;
1011: l_offices NUMBER;
1012: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1013: INVALID_LOCATION_TYPE EXCEPTION;
1014:
1030: END IF;
1031:
1032: SELECT COUNT(office)
1033: INTO l_offices
1034: FROM pn_locations_all
1035: WHERE Location_Type_Lookup_Code = l_location_type --'OFFICE'
1036: AND Status = 'A' --BUG#2168485
1037: AND l_as_of_date BETWEEN active_start_date AND active_end_date
1038: START WITH Location_Id = p_Location_Id
1062:
1063: /*===========================================================================+
1064: -- NAME : get_utilized_capacity
1065: -- DESCRIPTION : RETURN the Utilized Capacity for a given location_id
1066: -- NOTES : Currently being used in view "PN_LOCATIONS_V"
1067: -- "PN_BUILDING_V", "PN_FLOORS_V", "PN_OFFICES_V"
1068: -- AND Space Assignments form - "PNTSPACE.fmb"
1069: -- SCOPE : PUBLIC
1070: -- INVOKED FROM :
1112: FUNCTION get_utilized_capacity ( p_location_id IN NUMBER,
1113: p_As_Of_Date IN DATE)
1114: RETURN NUMBER
1115: IS
1116: l_LocationType pn_locations.location_type_lookup_code%TYPE;
1117: l_UtilizedCapacity NUMBER:=0;
1118: l_UtilizedCapacityEmp NUMBER:=0;
1119: l_UtilizedCapacityCust NUMBER:=0;
1120: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1124: p_As_Of_Date IN DATE) IS
1125: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1126: FROM pn_space_assign_emp_all
1127: WHERE location_id IN (SELECT a.location_id
1128: FROM pn_locations_all a
1129: WHERE p_As_Of_Date BETWEEN active_start_date AND
1130: active_end_date
1131: START WITH a.location_id = p_location_id
1132: CONNECT BY PRIOR a.location_id = a.parent_location_id
1137: );*/
1138: CURSOR c_location_id (p_Location_Id IN NUMBER
1139: ,p_As_of_date IN DATE) IS
1140: SELECT Location_Id
1141: FROM pn_locations_all
1142: WHERE p_as_of_date BETWEEN active_start_date AND
1143: active_end_date
1144: START WITH Location_Id = p_Location_Id
1145: CONNECT BY PRIOR Location_Id = parent_location_id
1159: ,p_As_Of_Date IN DATE) IS
1160: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1161: FROM pn_space_assign_cust_all
1162: WHERE location_id IN (SELECT a.location_id
1163: FROM pn_locations_all a
1164: WHERE p_as_of_date BETWEEN active_start_date AND
1165: active_end_date
1166: START WITH a.location_id = p_location_id
1167: CONNECT BY PRIOR a.location_id = a.parent_location_id
1313: | OUT: none
1314: |
1315: | RETURNS : Vacant capacity for a location (building/LAND)
1316: |
1317: | NOTES : Currently being used in view "PN_LOCATIONS_V"
1318: | "PN_BUILDING_V"
1319: | AND Space Assignments form - "PNTSPACE.fmb"
1320: |
1321: | MODIFICATION HISTORY
1333: FUNCTION get_vacancy ( p_Location_Id NUMBER,
1334: p_as_of_date IN DATE )
1335: RETURN NUMBER IS
1336:
1337: l_Location_Type pn_locations.location_type_lookup_code%type;
1338: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1339: 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));
1340: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1341: INVALID_LOCATION_TYPE EXCEPTION;
1385: | OUT: none
1386: |
1387: | RETURNS : Occupancy Percentage for a location (building/LAND)
1388: |
1389: | NOTES : Currently being used in view "PN_LOCATIONS_V"
1390: | "PN_BUILDING_V"
1391: | AND Space Assignments form - "PNTSPACE.fmb"
1392: |
1393: | MODIFICATION HISTORY
1402:
1403: FUNCTION get_occupancy_percent ( p_Location_Id NUMBER ,
1404: p_as_of_date IN DATE ) RETURN NUMBER IS
1405:
1406: l_Location_Type pn_locations.location_type_lookup_code%type;
1407: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity (p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
1408: l_max_capacity NUMBER:= get_building_max_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1409: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1410: INVALID_LOCATION_TYPE EXCEPTION;
1461: | OUT: none
1462: |
1463: | RETURNS : Utilized Area for a location (building/LAND)
1464: |
1465: | NOTES : Currently being used in view "PN_LOCATIONS_V"
1466: | "PN_BUILDING_V"
1467: | AND Space Assignments form - "PNTSPACE.fmb"
1468: |
1469: | MODIFICATION HISTORY
1479: FUNCTION get_area_utilized ( p_Location_Id NUMBER,
1480: p_as_of_date IN DATE
1481: ) RETURN NUMBER IS
1482:
1483: l_Location_Type pn_locations.location_type_lookup_code%type;
1484:
1485: l_Vacant_Area NUMBER:= Get_Vacant_Area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1486: l_rentable_area NUMBER:= get_building_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1487: l_utilized_capacity NUMBER:= get_utilized_capacity ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
1575: p_leaseId IN NUMBER,
1576: p_as_of_date IN DATE ) IS
1577:
1578: (SELECT NVL(SUM(pnl.RENTABLE_AREA),0) AS Area
1579: FROM pn_locations_all pnl,
1580: pn_tenancies_all pnt
1581: WHERE pnt.lease_id = p_leaseId
1582: AND pnt.status = 'A'
1583: AND pnl.location_id = pnt.location_id
2172: Begin
2173:
2174: SELECT 'X'
2175: INTO l_Dummy
2176: FROM pn_locations_all
2177: WHERE Status = 'A'
2178: AND Location_Id = p_Location_Id
2179: AND l_as_of_date BETWEEN active_start_date AND active_end_date;
2180:
2199: l_seqnum NUMBER;
2200:
2201: begin
2202:
2203: SELECT pn_locations_s.NEXTVAL
2204: INTO l_seqnum
2205: FROM DUAL;
2206:
2207: RETURN l_seqnum;
2461: p_location_id NUMBER,
2462: p_as_of_date DATE,
2463: p_ignore_date BOOLEAN) RETURN VARCHAR2
2464: IS
2465: l_location_code pn_locations_all.location_code%TYPE;
2466: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2467:
2468: CURSOR fetch_loc_code IS
2469: SELECT location_code
2466: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2467:
2468: CURSOR fetch_loc_code IS
2469: SELECT location_code
2470: FROM pn_locations_all
2471: WHERE location_id = p_location_id
2472: AND p_as_of_date BETWEEN active_start_date AND active_end_date;
2473:
2474: CURSOR fetch_loc_code_ignore_date IS
2472: AND p_as_of_date BETWEEN active_start_date AND active_end_date;
2473:
2474: CURSOR fetch_loc_code_ignore_date IS
2475: SELECT location_code
2476: FROM pn_locations_all
2477: WHERE location_id = p_location_id
2478: AND ROWNUM < 2;
2479:
2480: BEGIN
2505: FUNCTION Get_Location_Type_Lookup_Code (p_location_id NUMBER ,
2506: p_as_of_date DATE,
2507: p_ignore_date BOOLEAN) RETURN VARCHAR2
2508: IS
2509: l_location_type_lookup_code pn_locations_all.location_type_lookup_code%TYPE;
2510: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2511:
2512: CURSOR fetch_code IS
2513: SELECT location_type_lookup_code
2510: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2511:
2512: CURSOR fetch_code IS
2513: SELECT location_type_lookup_code
2514: FROM pn_locations_all
2515: WHERE location_id = p_location_id
2516: AND l_as_of_date BETWEEN active_start_date AND active_end_date;
2517:
2518: CURSOR fetch_code_ignore_date IS
2516: AND l_as_of_date BETWEEN active_start_date AND active_end_date;
2517:
2518: CURSOR fetch_code_ignore_date IS
2519: SELECT location_type_lookup_code
2520: FROM pn_locations_all
2521: WHERE location_id = p_location_id
2522: AND ROWNUM < 2;
2523:
2524: BEGIN
3574: | RETURNS:
3575: | RETURN the sum of rentable_area of offices associated with a ( Building/LAND )
3576: |
3577: | NOTES:
3578: | Currently being used in views "PN_LOCATIONS_V"
3579: | "PN_BUILDING_V"
3580: | AND form PNSULOCN.fmb ( Locations form )
3581: |
3582: | ASSUMPTION:
3597: FUNCTION get_building_rentable_area ( 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 usable_area of offices associated with a ( Building/LAND )
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:
3676: FUNCTION get_building_usable_area ( p_Location_Id NUMBER ,
3677: p_as_of_date IN DATE
3678: ) RETURN NUMBER
3679: IS
3680: l_location_type pn_locations.location_type_lookup_code%type;
3681: l_area pn_location_area_rec;
3682: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3683: invalid_location_type EXCEPTION;
3684:
3730: | RETURNS:
3731: | RETURN the sum of assignable_area of offices associated with a ( Building/LAND )
3732: |
3733: | NOTES:
3734: | Currently being used in views "PN_LOCATIONS_V"
3735: | "PN_BUILDING_V"
3736: | AND form PNSULOCN.fmb ( Locations form )
3737: |
3738: | ASSUMPTION:
3755: p_Location_Id NUMBER ,
3756: p_as_of_date IN DATE
3757: ) RETURN NUMBER
3758: IS
3759: l_location_type pn_locations.location_type_lookup_code%type;
3760: l_area pn_location_area_rec;
3761: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3762: invalid_location_type EXCEPTION;
3763:
3809: | RETURNS:
3810: | RETURN the sum of rentable_area of offices associated with a ( Floor/Parcel )
3811: |
3812: | NOTES:
3813: | Currently being used in views "PN_LOCATIONS_V"
3814: | "PN_BUILDING_V"
3815: | AND form PNSULOCN.fmb ( Locations form )
3816: |
3817: | ASSUMPTION:
3832: p_as_of_date IN DATE
3833: )
3834: RETURN NUMBER
3835: IS
3836: l_location_type pn_locations.location_type_lookup_code%type;
3837: l_area pn_location_area_rec;
3838: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3839: invalid_location_type EXCEPTION;
3840:
3886: | RETURNS:
3887: | RETURN the sum of usable_area of offices associated with a ( Floor/Parcel )
3888: |
3889: | NOTES:
3890: | Currently being used in views "PN_LOCATIONS_V"
3891: | "PN_BUILDING_V"
3892: | AND form PNSULOCN.fmb ( Locations form )
3893: |
3894: | ASSUMPTION:
3910: FUNCTION get_floor_usable_area ( p_Location_Id IN NUMBER ,
3911: p_as_of_date IN DATE )
3912: RETURN NUMBER
3913: IS
3914: l_location_type pn_locations.location_type_lookup_code%type;
3915: l_area pn_location_area_rec;
3916: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
3917: invalid_location_type EXCEPTION;
3918:
3964: | RETURNS:
3965: | RETURN the sum of assignable_area of offices associated with a ( Floor/Parcel )
3966: |
3967: | NOTES:
3968: | Currently being used in views "PN_LOCATIONS_V"
3969: | "PN_BUILDING_V"
3970: | AND form PNSULOCN.fmb ( Locations form )
3971: |
3972: | ASSUMPTION:
3989: FUNCTION get_floor_assignable_area ( p_Location_Id NUMBER ,
3990: p_as_of_date IN DATE )
3991: 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:
4043: | RETURNS:
4044: | RETURN the sum of max_capacity of offices associated with a ( Floor/Parcel )
4045: |
4046: | NOTES:
4047: | Currently being used in views "PN_LOCATIONS_V"
4048: | "PN_BUILDING_V"
4049: | AND form PNSULOCN.fmb ( Locations form )
4050: |
4051: | ASSUMPTION:
4069: p_Location_Id NUMBER ,
4070: p_as_of_date IN DATE
4071: ) RETURN NUMBER
4072: IS
4073: l_location_type pn_locations.location_type_lookup_code%type;
4074: l_area pn_location_area_rec;
4075: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4076: invalid_location_type EXCEPTION;
4077:
4123: | RETURNS:
4124: | RETURN the sum of optimum_capacity of offices associated with a ( Floor/Parcel )
4125: |
4126: | NOTES:
4127: | Currently being used in views "PN_LOCATIONS_V"
4128: | "PN_BUILDING_V"
4129: | AND form PNSULOCN.fmb ( Locations form )
4130: |
4131: | ASSUMPTION:
4147:
4148: FUNCTION get_floor_optimum_capacity ( p_Location_Id NUMBER ,
4149: p_as_of_date IN DATE ) RETURN NUMBER
4150: IS
4151: l_location_type pn_locations.location_type_lookup_code%type;
4152: l_area pn_location_area_rec;
4153: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4154: invalid_location_type EXCEPTION;
4155:
4202: | RETURNS:
4203: | RETURN the sum of max_capacity of offices associated with a ( Building/LAND )
4204: |
4205: | NOTES:
4206: | Currently being used in views "PN_LOCATIONS_V"
4207: | "PN_BUILDING_V"
4208: | AND form PNSULOCN.fmb ( Locations form )
4209: |
4210: | ASSUMPTION:
4226:
4227: FUNCTION get_building_max_capacity ( p_Location_Id NUMBER ,
4228: p_as_of_date IN DATE ) RETURN NUMBER
4229: IS
4230: l_location_type pn_locations.location_type_lookup_code%type;
4231: l_area pn_location_area_rec;
4232: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4233: invalid_location_type EXCEPTION;
4234:
4280: | RETURNS:
4281: | RETURN the sum of optimum_capacity of offices associated with a ( Building/LAND )
4282: |
4283: | NOTES:
4284: | Currently being used in views "PN_LOCATIONS_V"
4285: | "PN_BUILDING_V"
4286: | AND form PNSULOCN.fmb ( Locations form )
4287: |
4288: | ASSUMPTION:
4304:
4305: FUNCTION get_building_optimum_capacity ( p_Location_Id NUMBER ,
4306: p_as_of_date IN DATE ) RETURN NUMBER
4307: IS
4308: l_location_type pn_locations.location_type_lookup_code%type;
4309: l_area pn_location_area_rec;
4310: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4311: invalid_location_type EXCEPTION;
4312:
4356: | OUT: none
4357: |
4358: | RETURNS : Vacant capacity for a location (Floor/Parcel)
4359: |
4360: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4361: | "PN_BUILDING_V"
4362: | AND Space Assignments form - "PNTSPACE.fmb"
4363: |
4364: | MODIFICATION HISTORY
4376:
4377: FUNCTION get_floor_vacancy ( p_Location_Id NUMBER,
4378: p_as_of_date DATE) RETURN NUMBER IS
4379:
4380: l_Location_Type pn_locations.location_type_lookup_code%type;
4381:
4382: l_utilized_capacity NUMBER:= pnp_util_func.get_utilized_capacity ( p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4383: 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));
4384: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4432: | OUT: none
4433: |
4434: | RETURNS : Vacant capacity for a location (Office/Section)
4435: |
4436: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4437: | "PN_BUILDING_V"
4438: | AND Space Assignments form - "PNTSPACE.fmb"
4439: |
4440: | MODIFICATION HISTORY
4455:
4456: FUNCTION get_office_vacancy ( p_Location_Id NUMBER ,
4457: p_as_of_date IN DATE ) RETURN NUMBER IS
4458:
4459: l_Location_Type pn_locations.location_type_lookup_code%type;
4460: l_utilized_capacity NUMBER:= get_utilized_capacity(p_location_id,pnp_util_func.get_as_of_date(p_as_of_date));
4461: l_max_capacity NUMBER;
4462: INVALID_LOCATION_TYPE EXCEPTION;
4463: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4466: CURSOR Office_Vacancy_C( p_Location_Id IN NUMBER
4467: ,p_as_of_date IN DATE
4468: ,p_location_type IN VARCHAR2) IS
4469: (SELECT NVL((max_capacity), 0) AS vacancy
4470: FROM pn_locations_all
4471: WHERE Location_Type_Lookup_Code = p_location_type
4472: AND p_as_of_date BETWEEN active_start_date AND active_end_date
4473: AND Location_Id = p_Location_Id
4474: );
4554: FROM pn_space_assign_emp_all
4555: WHERE emp_assign_start_date > l_date
4556: --Bug#5959164 AND allocated_area_pct > 0
4557: AND location_id IN (SELECT location_id
4558: FROM pn_locations_all
4559: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4560: START WITH location_id = p_Location_Id
4561: CONNECT BY PRIOR location_id = parent_location_id
4562: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4565: SELECT 'Y'
4566: FROM pn_space_assign_emp_all
4567: WHERE l_date BETWEEN emp_assign_start_date AND emp_assign_end_date
4568: AND location_id IN (SELECT location_id
4569: FROM pn_locations_all
4570: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4571: START WITH location_id = p_Location_Id
4572: CONNECT BY PRIOR location_id = parent_location_id
4573: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4576: SELECT 'Y'
4577: FROM pn_space_assign_emp_all
4578: WHERE l_date >= emp_assign_start_date AND emp_assign_end_date IS NULL -- for open assignments time
4579: AND location_id IN (SELECT location_id
4580: FROM pn_locations_all
4581: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4582: START WITH location_id = p_Location_Id
4583: CONNECT BY PRIOR location_id = parent_location_id
4584: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4588: FROM pn_space_assign_cust_all
4589: WHERE cust_assign_start_date > l_date
4590: --Bug#5959164 AND allocated_area_pct > 0
4591: AND location_id IN (SELECT location_id
4592: FROM pn_locations_all
4593: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4594: START WITH location_id = p_Location_Id
4595: CONNECT BY PRIOR location_id = parent_location_id
4596: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4599: SELECT 'Y'
4600: FROM pn_space_assign_cust_all
4601: WHERE l_date BETWEEN cust_assign_start_date AND cust_assign_end_date
4602: AND location_id IN (SELECT location_id
4603: FROM pn_locations_all
4604: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4605: START WITH location_id = p_Location_Id
4606: CONNECT BY PRIOR location_id = parent_location_id
4607: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4610: SELECT 'Y'
4611: FROM pn_space_assign_cust_all
4612: WHERE l_date >= cust_assign_start_date AND cust_assign_end_date IS NULL -- for open assignments time
4613: AND location_id IN (SELECT location_id
4614: FROM pn_locations_all
4615: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4616: START WITH location_id = p_Location_Id
4617: CONNECT BY PRIOR location_id = parent_location_id
4618: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4687: | OUT: none
4688: |
4689: | RETURNS : secondary_circulation_area for a given location_id ( Floor/Parcel )
4690: |
4691: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4692: | "PN_BUILDING_V"
4693: | AND Space Assignments form - "PNTSPACE.fmb"
4694: |
4695: | MODIFICATION HISTORY
4699:
4700: FUNCTION get_floor_secondary_area ( p_Location_Id NUMBER,
4701: p_as_of_date DATE ) RETURN NUMBER IS
4702:
4703: l_Location_Type pn_locations.location_type_lookup_code%type;
4704: l_rentable_area NUMBER:= get_floor_rentable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4705: l_usable_area NUMBER:= get_floor_usable_area ( p_Location_Id,pnp_util_func.get_as_of_date(p_as_of_date));
4706: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date); --ASHISH
4707: INVALID_LOCATION_TYPE EXCEPTION;
4751: |
4752: | RETURNS : secondary_circulation_area for a given location_id
4753: | ( Office/Section )
4754: |
4755: | NOTES : Currently being used in view "PN_LOCATIONS_V"
4756: | "PN_BUILDING_V"
4757: | AND Space Assignments form - "PNTSPACE.fmb"
4758: |
4759: | MODIFICATION HISTORY
4771:
4772: FUNCTION get_office_secondary_area ( p_Location_Id NUMBER ,
4773: p_as_of_date IN DATE ) RETURN NUMBER IS
4774:
4775: l_Location_Type pn_locations.location_type_lookup_code%type;
4776: l_assignable_area NUMBER;
4777: l_usable_area NUMBER;
4778: l_common_area NUMBER;
4779: l_secondary_area NUMBER;
4785: ,p_location_type IN VARCHAR2) IS
4786: (SELECT NVL((USABLE_AREA), 0) AS usable_area
4787: ,NVL((ASSIGNABLE_AREA), 0) AS assignable_area
4788: ,NVL((COMMON_AREA), 0) AS common_area
4789: FROM pn_locations_all
4790: WHERE Location_Type_Lookup_Code = p_location_type
4791: AND p_as_of_date BETWEEN active_start_date AND active_end_date
4792: AND Location_Id = p_Location_Id
4793: );
4848: | RETURNS:
4849: | RETURN the sum of common areas of offices associated with a ( Floor/Parcel )
4850: |
4851: | NOTES:
4852: | Currently being used in views "PN_LOCATIONS_V"
4853: | "PN_BUILDING_V"
4854: | AND form PNSULOCN.fmb ( Locations form )
4855: |
4856: | ASSUMPTION:
4871:
4872: FUNCTION get_floor_common_area ( p_Location_Id NUMBER ,
4873: p_as_of_date IN DATE ) RETURN NUMBER IS
4874:
4875: l_Location_Type pn_locations.location_type_lookup_code%type;
4876: l_common_area NUMBER;
4877: INVALID_LOCATION_TYPE EXCEPTION;
4878:
4879: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4894: end if;
4895:
4896: SELECT NVL(SUM(COMMON_AREA),0)
4897: INTO l_common_area
4898: FROM pn_locations_all
4899: WHERE Location_Type_Lookup_Code = l_location_type --'OFFICE'
4900: AND Status = 'A'
4901: AND l_as_of_date BETWEEN active_start_date AND active_end_date
4902: START WITH Location_Id = p_Location_Id
4955:
4956: FUNCTION get_building_common_area ( p_Location_Id NUMBER ,
4957: p_as_of_date IN DATE ) RETURN NUMBER IS
4958:
4959: l_Location_Type pn_locations.location_type_lookup_code%type;
4960: l_common_area NUMBER;
4961: INVALID_LOCATION_TYPE EXCEPTION;
4962:
4963: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4978: end if;
4979:
4980: SELECT NVL(SUM(COMMON_AREA),0)
4981: INTO l_common_area
4982: FROM pn_locations_all
4983: WHERE Location_Type_Lookup_Code = l_location_type
4984: AND Status = 'A'
4985: AND l_as_of_date BETWEEN active_start_date AND active_end_date
4986: START WITH Location_Id = p_Location_Id
5024: |
5025: | OUT:
5026: | none
5027: |
5028: | RETURNS : parent_location_id FROM PN_LOCATIONS
5029: |
5030: | NOTES :
5031: |
5032: | MODIFICATION HISTORY
5045: IS
5046:
5047: CURSOR get_parent_loc_id IS
5048: SELECT parent_location_id
5049: FROM pn_locations_all
5050: WHERE location_id = p_location_id;
5051:
5052: l_parent_location_id NUMBER;
5053: BEGIN
5187: -- 30-OCT-02 Satish Tripathi o Access _all table for performance issues.
5188: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity
5189: -- 20-OCT-03 ftanudja o removed nvl from locn tbl filter. 3197410.
5190: -- 02-JUL-04 Satish Tripathi o Fixed for 3740584, added l_as_of_date BETWEEN
5191: -- ... for each pn_locations_all table.
5192: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_PROPERTIES
5193: -- with _ALL table.
5194: +============================================================================*/
5195:
5199: RETURN location_name_rec
5200: IS
5201:
5202: l_parent_location_id NUMBER;
5203: l_location_type_lookup_code pn_locations.location_type_lookup_code%type;
5204: l_location_name_rec LOCATION_NAME_REC;
5205: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5206:
5207: BEGIN
5207: BEGIN
5208:
5209: SELECT location_type_lookup_code,parent_location_id
5210: INTO l_location_type_lookup_code,l_parent_location_id
5211: FROM pn_locations_all
5212: WHERE location_id = p_Location_Id
5213: AND p_as_of_date BETWEEN active_start_date AND active_end_date;
5214:
5215: IF l_location_type_lookup_code IN ('OFFICE','SECTION') THEN
5218: b.FLOOR,c.location_code building_location_code,c.BUILDING,
5219: prop.property_code,prop.property_name,
5220: d.name office_park_name, e.name region_name
5221: INTO l_location_name_rec
5222: FROM pn_locations_all a,
5223: pn_locations_all b,
5224: pn_locations_all c,
5225: pn_location_parks d,
5226: pn_location_parks e,
5219: prop.property_code,prop.property_name,
5220: d.name office_park_name, e.name region_name
5221: INTO l_location_name_rec
5222: FROM pn_locations_all a,
5223: pn_locations_all b,
5224: pn_locations_all c,
5225: pn_location_parks d,
5226: pn_location_parks e,
5227: pn_properties_all prop
5220: d.name office_park_name, e.name region_name
5221: INTO l_location_name_rec
5222: FROM pn_locations_all a,
5223: pn_locations_all b,
5224: pn_locations_all c,
5225: pn_location_parks d,
5226: pn_location_parks e,
5227: pn_properties_all prop
5228: WHERE a.location_id = p_Location_Id
5245: b.FLOOR,c.location_code building_location_code,c.BUILDING,
5246: prop.property_code,prop.property_name,
5247: d.name office_park_name, e.name region_name
5248: INTO l_location_name_rec
5249: FROM pn_locations_all b,
5250: pn_locations_all c,
5251: pn_location_parks d,
5252: pn_location_parks e,
5253: pn_properties_all prop
5246: prop.property_code,prop.property_name,
5247: d.name office_park_name, e.name region_name
5248: INTO l_location_name_rec
5249: FROM pn_locations_all b,
5250: pn_locations_all c,
5251: pn_location_parks d,
5252: pn_location_parks e,
5253: pn_properties_all prop
5254: WHERE b.location_id = p_Location_Id
5269: '' FLOOR,c.location_code building_location_code,c.BUILDING,
5270: prop.property_code,prop.property_name,
5271: d.name office_park_name, e.name region_name
5272: INTO l_location_name_rec
5273: FROM pn_locations_all c,
5274: pn_location_parks d,
5275: pn_location_parks e,
5276: pn_properties_all prop
5277: WHERE c.location_id = p_Location_Id
5413: /* Getting rentable area for Office/Section */
5414:
5415: SELECT NVL(rentable_area,0)
5416: INTO l_rentable_area
5417: FROM pn_locations_all
5418: WHERE location_id = p_location_id
5419: AND active_start_date <= l_as_of_date
5420: AND active_end_date >= l_as_of_date;
5421:
5578: p_as_of_date IN DATE )
5579:
5580: RETURN VARCHAR2 IS
5581:
5582: l_uom PN_LOCATIONS.uom_code%type;
5583: l_loc_type PN_LOCATIONS.location_type_lookup_code%type;
5584: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5585:
5586: CURSOR get_uom_code_cur IS
5579:
5580: RETURN VARCHAR2 IS
5581:
5582: l_uom PN_LOCATIONS.uom_code%type;
5583: l_loc_type PN_LOCATIONS.location_type_lookup_code%type;
5584: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5585:
5586: CURSOR get_uom_code_cur IS
5587: SELECT uom_code
5584: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5585:
5586: CURSOR get_uom_code_cur IS
5587: SELECT uom_code
5588: FROM pn_locations_all
5589: WHERE location_type_lookup_code = l_loc_type
5590: AND l_as_of_date BETWEEN active_start_date AND active_end_date
5591: Start with location_id = p_location_id
5592: CONNECT BY PRIOR parent_location_id = location_id
6276: l_fut_str_dt DATE; -- Added for bug#5854636
6277: l_common_flag VARCHAR2(1);
6278: CURSOR get_common_flag IS
6279: SELECT common_area_flag
6280: FROM pn_locations_all
6281: WHERE location_id = p_location_id
6282: AND active_start_date <= l_end_dt
6283: AND active_end_date >= p_st_date;
6284:
6709: p_loc_area OUT NOCOPY PN_LOCATION_AREA_REC,
6710: p_space_area OUT NOCOPY PN_SPACE_AREA_REC
6711: )
6712: IS
6713: l_location_type_lookup_code pn_locations.location_type_lookup_code%type;
6714: l_assignable_area NUMBER := 0;
6715: l_rentable_area NUMBER := 0;
6716: l_usable_area NUMBER := 0;
6717: l_common_area NUMBER := 0;
6734:
6735: CURSOR c_loc is
6736: SELECT NVL(SUM(ASSIGNABLE_AREA),0) , NVL(SUM(RENTABLE_AREA),0) , NVL(SUM(USABLE_AREA),0)
6737: , NVL(SUM(COMMON_AREA),0),NVL(SUM(MAX_CAPACITY),0), NVL(SUM(OPTIMUM_CAPACITY),0)
6738: FROM pn_locations_all
6739: WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
6740: AND Status = 'A'
6741: AND l_as_of_date BETWEEN active_start_date AND active_end_date
6742: START WITH Location_Id = p_Location_Id
6749: FROM PN_SPACE_ASSIGN_EMP_ALL
6750: WHERE emp_assign_start_date <= l_as_of_date
6751: AND NVL(emp_assign_end_date, l_date) >= l_as_of_date
6752: AND Location_Id IN (SELECT Location_Id
6753: FROM pn_locations_all
6754: WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
6755: AND Status = 'A'
6756: AND l_as_of_date BETWEEN active_start_date AND active_end_date
6757: START WITH Location_Id = p_Location_Id
6765: FROM PN_SPACE_ASSIGN_CUST_ALL
6766: WHERE cust_assign_start_date <= l_as_of_date
6767: AND NVL(cust_assign_end_date, l_date) >= l_as_of_date
6768: AND Location_Id IN (SELECT Location_Id
6769: FROM pn_locations_all
6770: WHERE Location_Type_Lookup_Code = l_location_type_lookup_code
6771: AND Status = 'A'
6772: AND l_as_of_date BETWEEN active_start_date AND active_end_date
6773: START WITH Location_Id = p_Location_Id
7100: SELECT *
7101: FROM pn_space_assign_cust_all
7102: WHERE location_id IN
7103: ( SELECT location_id
7104: FROM pn_locations_all
7105: START WITH location_id = p_location_id
7106: CONNECT BY PRIOR location_id = parent_location_id )
7107: AND NVL(cust_assign_end_date,g_end_of_time) >= p_start_date
7108: AND cust_assign_start_date <= NVL(p_end_date, g_end_of_time);
7112: SELECT *
7113: FROM pn_space_assign_emp_all
7114: WHERE location_id IN
7115: ( SELECT location_id
7116: FROM pn_locations_all
7117: START WITH location_id = p_location_id
7118: CONNECT BY PRIOR location_id = parent_location_id )
7119: AND NVL(emp_assign_end_date,g_end_of_time) >= p_start_date
7120: AND emp_assign_start_date <= NVL(p_end_date, g_end_of_time);
7266: FROM DUAL
7267: WHERE EXISTS (SELECT 'x'
7268: FROM pn_space_assign_emp_all
7269: WHERE location_id IN (SELECT location_id
7270: FROM pn_locations_all
7271: START WITH location_id = p_location_id
7272: CONNECT BY PRIOR location_id = parent_location_id )
7273: --Bug#5959164 AND allocated_area > 0
7274: --Bug#5959164 AND allocated_area_pct > 0
7277: UNION
7278: SELECT 'x'
7279: FROM pn_space_assign_cust_all
7280: WHERE location_id IN (SELECT location_id
7281: FROM pn_locations_all
7282: START WITH location_id = p_location_id
7283: CONNECT BY PRIOR location_id = parent_location_id )
7284: --Bug#5959164 AND allocated_area > 0
7285: --Bug#5959164 AND allocated_area_pct > 0
7291: FROM DUAL
7292: WHERE EXISTS (SELECT 'x'
7293: FROM pn_space_assign_emp_all
7294: WHERE location_id IN (SELECT location_id
7295: FROM pn_locations_all
7296: START WITH location_id = p_location_id
7297: CONNECT BY PRIOR location_id = parent_location_id )
7298: --Bug#5959164 AND allocated_area > 0
7299: --Bug#5959164 AND allocated_area_pct > 0
7302: UNION
7303: SELECT 'x'
7304: FROM pn_space_assign_cust_all
7305: WHERE location_id IN (SELECT location_id
7306: FROM pn_locations_all
7307: START WITH location_id = p_location_id
7308: CONNECT BY PRIOR location_id = parent_location_id )
7309: --Bug#5959164 AND allocated_area > 0
7310: --Bug#5959164 AND allocated_area_pct > 0
7583: l_str_date_old DATE;
7584:
7585: CURSOR pn_location_cursor IS
7586: SELECT *
7587: FROM pn_locations_all
7588: WHERE location_id = p_location_id
7589: AND active_start_date = p_Active_start_date_old
7590: AND active_end_date = p_active_end_date_old;
7591:
7714: /* Bug#7666462 : Modified the Cursor SELECT query to handle NULL condition for end date */
7715:
7716: CURSOR locations_cursor Is
7717: SELECT *
7718: FROM pn_locations_all
7719: WHERE location_id = p_location_id
7720: AND (active_start_date <= NVL(p_start_Date, active_start_date)
7721: AND nvl(active_end_Date, TO_DATE('12/31/4712','MM/DD/YYYY')) >=
7722: nvl( p_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')));
7746: -- REFERENCE :
7747: -- HISTORY :
7748: -- 24-jun-03 Kiran o Created. CAM impact on Locations.
7749: -- 20-OCT-03 ftanudja o removed GROUP BY expression. 3197410.
7750: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced pn_tenancies and pn_locations
7751: -- with _ALL table.
7752: -------------------------------------------------------------------------------
7753:
7754: FUNCTION Exist_Tenancy_For_End_Date
7764: (select tenancy_id
7765: from pn_tenancies_all
7766: where location_id in
7767: (select loc.location_id
7768: from pn_locations_all loc
7769: connect by prior loc.location_id = loc.parent_location_id
7770: start with loc.location_id = p_Location_Id)
7771: and primary_flag = 'Y'
7772: and EXPIRATION_DATE > p_New_End_Date
7792: -- REFERENCE :
7793: -- HISTORY :
7794: -- 24-jun-03 Kiran o Created. CAM impact on Locations.
7795: -- 20-OCT-03 ftanudja o removed GROUP BY expression. 3197410.
7796: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced pn_tenancies and pn_locations
7797: -- with _ALL table.
7798: -------------------------------------------------------------------------------
7799:
7800: FUNCTION Exist_Tenancy_For_Start_Date
7810: (select tenancy_id
7811: from pn_tenancies_all
7812: where location_id in
7813: (select loc.location_id
7814: from pn_locations_all loc
7815: connect by prior loc.location_id = loc.parent_location_id
7816: start with loc.location_id = p_Location_Id)
7817: and primary_flag = 'Y'
7818: and nvl(OCCUPANCY_DATE,ESTIMATED_OCCUPANCY_DATE) < p_New_Start_Date
7841: -- HISTORY :
7842: -- 24-jun-03 Kiran o Created. CAM impact on Locations.
7843: -- 20-OCT-03 ftanudja o removed GROUP BY expression. 3197410.
7844: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced pn_rec_arcl_dtlln and
7845: -- pn_locations with _ALL table.
7846: -------------------------------------------------------------------------------
7847: FUNCTION Exist_Area_Class_Dtls_For_Loc
7848: ( p_Location_Id IN NUMBER,
7849: p_active_start_date IN DATE default NULL,
7857: (select area_class_dtl_line_id
7858: from pn_rec_arcl_dtlln_all
7859: where location_id in
7860: (select loc.location_id
7861: from pn_locations_all loc
7862: connect by prior loc.location_id = loc.parent_location_id
7863: start with loc.location_id = p_Location_Id)
7864: );
7865:
8026: loc.rentable_area,
8027: loc.usable_area,
8028: loc.assignable_area
8029: FROM pn_payment_terms_all trm,
8030: pn_locations_all loc
8031: WHERE trm.lease_id = p_lease_id
8032: AND loc.location_id = p_loc_id
8033: AND trm.location_id = p_loc_id
8034: AND trm.area_type_code NOT IN ('OTHER')
8257: usable_area,
8258: assignable_area,
8259: active_start_date,
8260: active_end_date
8261: FROM pn_locations_all
8262: WHERE location_id = p_ofc_loc_id;
8263:
8264: CURSOR get_affected_payment_terms (p_ofc_id NUMBER, p_flr_id NUMBER, p_bld_id NUMBER,
8265: p_start_date DATE, p_end_date DATE) IS
8284: CURSOR get_flr_area (p_as_of_date DATE) IS
8285: SELECT sum(nvl(rentable_area,0)) rentable,
8286: sum(nvl(usable_area,0)) usable,
8287: sum(nvl(assignable_area,0)) assignable
8288: FROM pn_locations_all loc
8289: WHERE loc.parent_location_id = p_flr_loc_id
8290: AND p_as_of_date BETWEEN active_start_date AND active_end_date
8291: AND location_id <> p_ofc_loc_id;
8292:
8294: CURSOR get_bld_area (p_as_of_date DATE) IS
8295: SELECT sum(nvl(o.rentable_area,0)) rentable,
8296: sum(nvl(o.usable_area,0)) usable,
8297: sum(nvl(o.assignable_area,0)) assignable
8298: FROM pn_locations_all f, pn_locations_all o
8299: WHERE p_bld_loc_id = f.parent_location_id
8300: AND f.location_id = o.parent_location_id
8301: AND p_as_of_date BETWEEN f.active_start_date AND f.active_end_date
8302: AND p_as_of_date BETWEEN o.active_start_date AND o.active_end_date
8446: nvl(sum(ofc.usable_area),0) usable,
8447: nvl(sum(ofc.assignable_area),0) assignable,
8448: nvl(sum(ofc.max_capacity),0) max_capacity,
8449: nvl(sum(ofc.optimum_capacity),0) optimum_capacity
8450: FROM pn_locations_all ofc,
8451: pn_locations_all flr
8452: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8453: AND p_as_of_date BETWEEN flr.active_start_date AND flr.active_end_date
8454: AND flr.parent_location_id = p_location_id
8447: nvl(sum(ofc.assignable_area),0) assignable,
8448: nvl(sum(ofc.max_capacity),0) max_capacity,
8449: nvl(sum(ofc.optimum_capacity),0) optimum_capacity
8450: FROM pn_locations_all ofc,
8451: pn_locations_all flr
8452: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8453: AND p_as_of_date BETWEEN flr.active_start_date AND flr.active_end_date
8454: AND flr.parent_location_id = p_location_id
8455: AND ofc.parent_location_id = flr.location_id;
8459: nvl(sum(ofc.usable_area),0) usable,
8460: nvl(sum(ofc.assignable_area),0) assignable,
8461: nvl(sum(ofc.max_capacity),0) max_capacity,
8462: nvl(sum(ofc.optimum_capacity),0) optimum_capacity
8463: FROM pn_locations_all ofc
8464: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8465: AND ofc.parent_location_id = p_location_id;
8466:
8467: CURSOR office_area IS
8469: usable_area usable,
8470: assignable_area assignable,
8471: max_capacity max_capacity,
8472: optimum_capacity optimum_capacity
8473: FROM pn_locations_all ofc
8474: WHERE p_as_of_date BETWEEN ofc.active_start_date AND ofc.active_end_date
8475: AND ofc.location_id = p_location_id;
8476:
8477: BEGIN