[Home] [Help]
1: PACKAGE BODY pnp_util_func AS
2: -- $Header: PNPFUNCB.pls 120.31.12020000.3 2013/03/03 07:14:02 smahapat 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
443: l_assignable_area NUMBER;
444: l_allocated_area_emp NUMBER;
445: l_allocated_area_cust NUMBER;
446: l_date DATE := TO_DATE('31/12/4712', 'DD/MM/YYYY');
447: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
448:
449:
450: INVALID_LOCATION_TYPE EXCEPTION;
451:
557: );
558:
559: BEGIN
560:
561: l_location_type := pnp_util_func.get_location_type_lookup_code(
562: p_location_id => p_location_id,
563: p_as_of_date => l_as_of_date);
564:
565:
724: |
725: | MODIFICATION HISTORY
726: |
727: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
728: | pnp_util_func.Get_Vacant_Area
729: | - Bug Fix for #1379527
730: | 05-FEB-2001 Lakshmikanth Replaced get_building_rentable_area by
731: | get_building_assignable_area in the
732: | get_vacant_area_percent.
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));
745: INVALID_LOCATION_TYPE EXCEPTION;
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));
745: INVALID_LOCATION_TYPE EXCEPTION;
746: a NUMBER := 0;
747:
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));
745: INVALID_LOCATION_TYPE EXCEPTION;
746: a NUMBER := 0;
747:
748: BEGIN
746: a NUMBER := 0;
747:
748: BEGIN
749:
750: l_location_type := pnp_util_func.get_location_type_lookup_code(
751: p_location_id => p_location_id,
752: p_as_of_date => l_as_of_date);
753:
754: IF l_location_type IS NULL THEN
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,
822: pnp_util_func.get_as_of_date(p_as_of_date));
823: l_return_value NUMBER;
824: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
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,
822: pnp_util_func.get_as_of_date(p_as_of_date));
823: l_return_value NUMBER;
824: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
825: INVALID_LOCATION_TYPE EXCEPTION;
826: a NUMBER:= 0;
820: pnp_util_func.get_as_of_date(p_as_of_date));
821: l_Usable_Area NUMBER:= get_building_usable_area ( p_location_id,
822: pnp_util_func.get_as_of_date(p_as_of_date));
823: l_return_value NUMBER;
824: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
825: INVALID_LOCATION_TYPE EXCEPTION;
826: a NUMBER:= 0;
827:
828: BEGIN
827:
828: BEGIN
829:
830:
831: l_location_type := pnp_util_func.get_location_type_lookup_code (
832: p_location_id => p_location_id,
833: p_as_of_date => l_as_of_date);
834:
835: IF l_location_type IS NULL THEN
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:
912: CURSOR floor_count_C( p_Location_Id NUMBER
913: ,p_as_of_date DATE
924:
925: BEGIN
926:
927:
928: l_location_type := pnp_util_func.get_location_type_lookup_code (
929: p_location_id => p_location_id,
930: p_as_of_date => l_as_of_date);
931:
932: IF l_location_type IS NULL THEN
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:
1015: BEGIN
1016:
1014:
1015: BEGIN
1016:
1017:
1018: l_location_type := pnp_util_func.get_location_type_lookup_code (
1019: p_location_id => p_location_id,
1020: p_as_of_date => l_as_of_date);
1021:
1022: IF l_location_type IS NULL THEN
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);
1121:
1122: /* Commented and modified for bug 6470318
1123: CURSOR Utilised_Capacity_Emp_C( p_location_id IN NUMBER,
1124: p_As_Of_Date IN DATE) IS
1189: NVL(cust_assign_end_date, g_end_of_time)
1190: );
1191: BEGIN
1192:
1193: l_locationtype := pnp_util_func.get_location_type_lookup_code (
1194: p_location_id => p_location_id,
1195: p_as_of_date => l_as_of_date);
1196: IF l_LocationType IS NULL THEN
1197: RETURN 0;
1320: |
1321: | MODIFICATION HISTORY
1322: |
1323: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1324: | pnp_util_func.get_utilized_capacity
1325: | - Bug Fix for #1379527
1326: |
1327: | 31-OCT-2001 graghuna o added p_as_of_date for Location
1328: | Date-Effectivity.
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;
1342:
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;
1342:
1343: BEGIN
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;
1342:
1343: BEGIN
1344:
1341: INVALID_LOCATION_TYPE EXCEPTION;
1342:
1343: BEGIN
1344:
1345: l_location_type := pnp_util_func.get_location_type_lookup_code (
1346: p_location_id => p_location_id,
1347: p_as_of_date => l_as_of_date);
1348: IF l_location_type IS NULL THEN
1349: raise NO_DATA_FOUND;
1392: |
1393: | MODIFICATION HISTORY
1394: |
1395: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1396: | pnp_util_func.get_utilized_capacity
1397: | - Bug Fix for #1379527
1398: |
1399: | 31-OCT-2001 graghuna o added p_as_of_date for Location Date-Effectivity.
1400: | 05-MAY-2004 ftanudja o handle if location type is null.
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;
1411: a NUMBER:= 0;
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;
1411: a NUMBER:= 0;
1412:
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;
1411: a NUMBER:= 0;
1412:
1413: BEGIN
1413: BEGIN
1414:
1415:
1416:
1417: l_location_type := pnp_util_func.get_location_type_lookup_code (
1418: p_location_id => p_location_id,
1419: p_as_of_date => l_as_of_date);
1420:
1421: IF l_location_type IS NULL THEN
1468: |
1469: | MODIFICATION HISTORY
1470: |
1471: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1472: | pnp_util_func.get_utilized_capacity
1473: | AND pnp_util_func.Get_Vacant_Area
1474: | - Bug Fix for #1379527
1475: | 31-OCT-2001 graghuna o added p_as_of_date for Location Date-Effectivity.
1476: | 05-MAY-2004 ftanudja o handle if location type is null.
1469: | MODIFICATION HISTORY
1470: |
1471: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
1472: | pnp_util_func.get_utilized_capacity
1473: | AND pnp_util_func.Get_Vacant_Area
1474: | - Bug Fix for #1379527
1475: | 31-OCT-2001 graghuna o added p_as_of_date for Location Date-Effectivity.
1476: | 05-MAY-2004 ftanudja o handle if location type is null.
1477: +===========================================================================*/
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));
1488: INVALID_LOCATION_TYPE EXCEPTION;
1489: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
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));
1488: INVALID_LOCATION_TYPE EXCEPTION;
1489: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1490: a NUMBER := 0;
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));
1488: INVALID_LOCATION_TYPE EXCEPTION;
1489: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1490: a NUMBER := 0;
1491:
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));
1488: INVALID_LOCATION_TYPE EXCEPTION;
1489: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1490: a NUMBER := 0;
1491:
1492: BEGIN
1493:
1490: a NUMBER := 0;
1491:
1492: BEGIN
1493:
1494: l_location_type := pnp_util_func.get_location_type_lookup_code (
1495: p_location_id => p_location_id,
1496: p_as_of_date => l_as_of_date);
1497:
1498: IF l_location_type IS NULL THEN
1567: p_as_of_date IN DATE ) RETURN NUMBER
1568: IS
1569:
1570: l_totalArea NUMBER := 0;
1571: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1572:
1573:
1574: CURSOR Total_Area_C(
1575: p_leaseId IN NUMBER,
1682: l_occupancyCount NUMBER := 0;
1683: l_occupancyCountEmp NUMBER := 0;
1684: l_occupancyCountCust NUMBER := 0;
1685: l_date DATE := TO_DATE('31/12/2199' , 'DD/MM/YYYY');
1686: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
1687:
1688: BEGIN
1689:
1690: SELECT NVL(count (*), 0)
2166: RETURN BOOLEAN Is
2167:
2168: l_Dummy VARCHAR2(1);
2169:
2170: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
2171:
2172: Begin
2173:
2174: SELECT 'X'
2412: l_retnum NUMBER := 0;
2413: l_retnum_emp NUMBER := 0;
2414: l_retnum_cust NUMBER := 0;
2415: l_date DATE := TO_DATE('31/12/2199' , 'DD/MM/YYYY');
2416: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date); --ASHISH
2417:
2418: BEGIN
2419:
2420: SELECT 1
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
2470: FROM pn_locations_all
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
2514: FROM pn_locations_all
2621: ,p_end_date DATE
2622: ,p_sch_day NUMBER
2623: ,p_term_freq VARCHAR2
2624: ,p_limit_date DATE)
2625: RETURN PNP_UTIL_FUNC.virtual_sched_tbl_type IS
2626:
2627:
2628: CURSOR get_freq_num(p_freq_char IN VARCHAR2) IS
2629: SELECT TO_NUMBER(DECODE( p_freq_char
2634: , 'YR', 12
2635: , -1)) AS freq_num
2636: FROM DUAL;
2637:
2638: l_sched_tbl PNP_UTIL_FUNC.virtual_sched_tbl_type;
2639:
2640: l_frequency NUMBER;
2641:
2642: l_current_st_dt DATE;
2893: FROM pn_payment_terms_all
2894: WHERE payment_term_ID = p_term_ID;
2895:
2896: l_frequency NUMBER;
2897: l_sched_tbl PNP_UTIL_FUNC.virtual_sched_tbl_type;
2898:
2899: /* user defined exceptions */
2900: INVALID_PARAM EXCEPTION;
2901: INVALID_TERMINATION_DATE EXCEPTION;
2954: FOR term_rec IN terms_for_max_appr_sched( p_lease_ID
2955: ,sd_other.schedule_date)
2956: LOOP
2957:
2958: l_sched_tbl := PNP_UTIL_FUNC.create_virtual_schedules
2959: ( p_start_date => term_rec.start_date
2960: ,p_end_date => term_rec.end_date
2961: ,p_sch_day => term_rec.schedule_day
2962: ,p_term_freq => term_rec.frequency_code
3013: END IF;
3014:
3015: FOR term_rec IN term_details(p_term_ID) LOOP
3016:
3017: l_sched_tbl := PNP_UTIL_FUNC.create_virtual_schedules
3018: ( p_start_date => term_rec.start_date
3019: ,p_end_date => term_rec.end_date
3020: ,p_sch_day => term_rec.schedule_day
3021: ,p_term_freq => term_rec.frequency_code
3154: FUNCTION MIN_LEASE_TERMINATE_DATE (p_lease_id IN NUMBER) RETURN DATE IS
3155:
3156: l_min_lease_terminate_date DATE;
3157: l_norm_trm_exsts BOOLEAN := FALSE;
3158: l_item_end_dt_tbl pnp_util_func.item_end_dt_tbl_type;
3159: i NUMBER;
3160: maxDt DATE := TO_DATE('01/01/0001', 'MM/DD/YYYY');
3161:
3162: CURSOR csr_lst_appr_schd(p_lease_ID IN NUMBER) IS
3313: -- date in case there is no approved schedule
3314: -- 24-MAR-10 acprakas Bug#9323699. Reverted back the change of 8806693.
3315: +=============================================================================*/
3316: FUNCTION fetch_item_end_dates( p_lease_id NUMBER)
3317: RETURN pnp_util_func.item_end_dt_tbl_type IS
3318:
3319: CURSOR csr_term_info(p_lease_ID IN NUMBER) IS
3320: SELECT frequency_code,
3321: payment_term_id,
3323: FROM pn_payment_terms_all
3324: WHERE lease_id = p_lease_id
3325: AND NVL(status,'APPROVED') = 'APPROVED';
3326:
3327: l_item_end_dt_tbl pnp_util_func.item_end_dt_tbl_type;
3328: i NUMBER := 0;
3329:
3330: BEGIN
3331:
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:
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:
3685: BEGIN
3686:
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:
3764: BEGIN
3765:
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:
3841: BEGIN
3842:
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:
3919: BEGIN
3920:
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:
3998: BEGIN
3999:
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:
4078: BEGIN
4079:
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:
4156: BEGIN
4157:
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:
4235: BEGIN
4236:
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:
4313: BEGIN
4314:
4363: |
4364: | MODIFICATION HISTORY
4365: |
4366: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
4367: | pnp_util_func.get_utilized_capacity
4368: | - Bug Fix for #1379527
4369: |
4370: | 18-FEB-2004 abanerje o Handled NO_DATA_FOUND to return 0.
4371: | All the select statements have been
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);
4385: INVALID_LOCATION_TYPE EXCEPTION;
4386:
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);
4385: INVALID_LOCATION_TYPE EXCEPTION;
4386:
4387:
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);
4385: INVALID_LOCATION_TYPE EXCEPTION;
4386:
4387:
4388:
4387:
4388:
4389: BEGIN
4390:
4391: l_location_type := pnp_util_func.get_location_type_lookup_code (
4392: p_location_id => p_location_id,
4393: p_as_of_date => l_as_of_date);
4394: IF l_Location_Type IS NULL THEN
4395: RETURN 0;
4439: |
4440: | MODIFICATION HISTORY
4441: |
4442: | 17-AUG-2000 Daniel Thota Added default SYSDATE to call to
4443: | pnp_util_func.get_utilized_capacity
4444: | - Bug Fix for #1379527
4445: | 30-OCT-2002 Satish Tripathi o Access _all table for performance issues.
4446: | 31-OCT-2001 graghuna o added p_as_of_date for Location
4447: | Date-Effectivity
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);
4464:
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);
4464:
4465:
4466: CURSOR Office_Vacancy_C( p_Location_Id IN NUMBER
4467: ,p_as_of_date IN DATE
4474: );
4475:
4476: BEGIN
4477:
4478: l_location_type := pnp_util_func.get_location_type_lookup_code (
4479: p_location_id => p_location_id,
4480: p_as_of_date => l_as_of_date);
4481:
4482: IF l_Location_Type IS NULL THEN
4546: p_as_of_date IN DATE )
4547: RETURN BOOLEAN
4548: IS
4549: l_exists VARCHAR2(1) := 'N';
4550: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4551:
4552: CURSOR emp_assign_future (l_date DATE) IS
4553: SELECT 'Y'
4554: FROM pn_space_assign_emp_all
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;
4708:
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;
4708:
4709:
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;
4708:
4709:
4710:
4709:
4710:
4711: BEGIN
4712:
4713: l_location_type := pnp_util_func.get_location_type_lookup_code (
4714: p_location_id => p_location_id,
4715: p_as_of_date => l_as_of_date); --ASHISH
4716:
4717: IF l_location_type IS NULL THEN
4777: l_usable_area NUMBER;
4778: l_common_area NUMBER;
4779: l_secondary_area NUMBER;
4780: INVALID_LOCATION_TYPE EXCEPTION;
4781: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4782:
4783: CURSOR Area_C( p_Location_Id IN NUMBER
4784: ,p_as_of_date IN DATE
4785: ,p_location_type IN VARCHAR2) IS
4792: AND Location_Id = p_Location_Id
4793: );
4794: BEGIN
4795:
4796: l_location_type := pnp_util_func.get_location_type_lookup_code (
4797: p_location_id => p_location_id,
4798: p_as_of_date => l_as_of_date);
4799:
4800: IF l_Location_Type IS NULL THEN
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);
4880:
4881: BEGIN
4882:
4883: l_location_type := pnp_util_func.get_location_type_lookup_code (
4879: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4880:
4881: BEGIN
4882:
4883: l_location_type := pnp_util_func.get_location_type_lookup_code (
4884: p_location_id => p_location_id,
4885: p_as_of_date => l_as_of_date);
4886:
4887: IF l_location_type IS NULL THEN
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);
4964:
4965: BEGIN
4966:
4967: l_location_type := pnp_util_func.get_location_type_lookup_code (
4963: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
4964:
4965: BEGIN
4966:
4967: l_location_type := pnp_util_func.get_location_type_lookup_code (
4968: p_location_id => p_location_id,
4969: p_as_of_date => l_as_of_date);
4970:
4971: IF l_location_type IS NULL THEN
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
5208:
5209: SELECT location_type_lookup_code,parent_location_id
5228: WHERE a.location_id = p_Location_Id
5229: AND l_as_of_date BETWEEN a.active_start_date AND a.active_end_date
5230: AND b.location_id = l_parent_location_id
5231: AND l_as_of_date BETWEEN b.active_start_date AND b.active_end_date
5232: AND c.location_id = pnp_util_func.GET_PARENT_LOCATION_ID(l_parent_location_id)
5233: AND l_as_of_date BETWEEN c.active_start_date AND c.active_end_date
5234: AND prop.property_id(+) = c.property_id
5235: AND d.location_park_id(+) = prop.location_park_id
5236: AND d.location_park_type(+) = 'OFFPRK'
5391: ) RETURN NUMBER
5392: IS
5393:
5394: l_rentable_area NUMBER;
5395: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
5396:
5397: BEGIN
5398:
5399: IF p_loc_type_lookup_code IS NULL AND p_location_id IS NULL THEN
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
5588: FROM pn_locations_all
5594: begin
5595:
5596: IF p_loc_type is NULL THEN
5597:
5598: l_loc_type := pnp_util_func.get_location_type_lookup_code (
5599: p_location_id => p_location_id,
5600: p_as_of_date => l_as_of_date);
5601:
5602: IF l_loc_type IS NULL THEN
6163:
6164: BEGIN
6165: /* Commented and moved to validate_vacant_area Bug 5854636
6166: IF p_called_frm_mode IS NULL THEN
6167: l_fut_str_dt := pnp_util_func.get_min_futr_str_dt(p_loc_id,p_str_dt);
6168: ELSIF p_called_frm_mode = 'PNTSPACE_UPDATE' THEN
6169: l_fut_str_dt := g_end_of_time;
6170: END IF;
6171:
6228: | 30-OCT-02 Satish o Access _all table for performance issues.
6229: | 31-DEC-02 Mrinal o Added NO_DATA_FOUND exception.
6230: | 20-OCT-03 ftanudja o removed nvl from locn tbl filter. 3197410.
6231: | 07-JAN-04 Daniel o Added new OUT parameter l_total_allocated_area_pct in
6232: | call to pnp_util_func.get_allocated_area. bug # 3354278
6233: | 26-MAY-04 abanerje o Added NVL to the select statement so that the
6234: | p_assignable_area is set to -99 when the area is common
6235: | Using this method we are able to distinguish the
6236: | condition when
6271: l_total_allocated_area NUMBER;
6272: l_total_allocated_area_pct NUMBER;
6273: l_future VARCHAR2(1) := NULL;
6274: l_end_dt DATE := nvl(p_end_dt, g_end_of_time);
6275: l_area_rec pnp_util_func.pn_location_area_rec;
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
6284:
6285: BEGIN
6286: PNP_DEBUG_PKG.debug ('validate_vacant_area(+)');
6287:
6288: pnp_util_func.fetch_loctn_area(
6289: p_type => pnp_util_func.get_location_type_lookup_code
6290: (
6291: p_location_id => p_location_id,
6292: p_as_of_date => p_st_date
6285: BEGIN
6286: PNP_DEBUG_PKG.debug ('validate_vacant_area(+)');
6287:
6288: pnp_util_func.fetch_loctn_area(
6289: p_type => pnp_util_func.get_location_type_lookup_code
6290: (
6291: p_location_id => p_location_id,
6292: p_as_of_date => p_st_date
6293: ),
6315: ELSE
6316: l_new_allocated_area_pct := NVL( p_new_allocated_area_pct, (p_new_allocated_area/p_assignable_area)*100 );
6317: l_old_allocated_area_pct := NVL( p_old_allocated_area_pct, (p_old_allocated_area/p_assignable_area)*100 );
6318: END IF;
6319: pnp_util_func.get_allocated_area (p_loc_id => p_location_id,
6320: p_str_dt => p_st_date,
6321: p_new_end_dt => p_end_dt,
6322: p_allocated_area => l_total_allocated_area,
6323: p_allocated_area_pct => l_total_allocated_area_pct,
6330: (NVL(l_new_allocated_area_pct,0) - NVL(l_old_allocated_area_pct,0)
6331: > 100 - NVL(l_total_allocated_area_pct,0))
6332: THEN
6333: IF p_called_frm_mode IS NULL THEN
6334: l_fut_str_dt := pnp_util_func.get_min_futr_str_dt(p_location_id,p_st_date);
6335: ELSIF p_called_frm_mode = 'PNTSPACE_UPDATE' THEN
6336: l_fut_str_dt := g_end_of_time;
6337: END IF;
6338:
6339: IF NVL( p_end_dt, g_end_of_time )
6340: > NVL( l_fut_str_dt, g_end_of_time )
6341: THEN
6342: p_end_dt := l_fut_str_dt - 1;
6343: pnp_util_func.get_allocated_area (p_loc_id => p_location_id,
6344: p_str_dt => p_st_date,
6345: p_new_end_dt => p_end_dt,
6346: p_allocated_area => l_total_allocated_area,
6347: p_allocated_area_pct => l_total_allocated_area_pct,
6729: l_vacant_area_percent NUMBER := 0;
6730: l_vacancy NUMBER := 0;
6731: l_area_utilized NUMBER := 0;
6732: l_date DATE := TO_DATE('31/12/4712' , 'DD/MM/YYYY');
6733: l_as_of_date DATE := pnp_util_func.get_as_of_date(p_as_of_date);
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)
6867: p_Location_Type IN VARCHAR2,
6868: p_Assignable_Area IN NUMBER )
6869: RETURN BOOLEAN IS
6870:
6871: v_location_rec pnp_util_func.PN_LOCATION_AREA_REC;
6872: v_space_allocation_rec pnp_util_func.PN_SPACE_AREA_REC;
6873: INVALID_ASSIGNABLE_AREA EXCEPTION;
6874:
6875: CURSOR start_date_cur IS
6868: p_Assignable_Area IN NUMBER )
6869: RETURN BOOLEAN IS
6870:
6871: v_location_rec pnp_util_func.PN_LOCATION_AREA_REC;
6872: v_space_allocation_rec pnp_util_func.PN_SPACE_AREA_REC;
6873: INVALID_ASSIGNABLE_AREA EXCEPTION;
6874:
6875: CURSOR start_date_cur IS
6876: SELECT emp_assign_start_date
7122:
7123: l_index INTEGER := 0;
7124: l_space_assign_cust_tbl SPACE_ASSIGNMENT_TBL;
7125: l_space_assign_emp_tbl SPACE_ASSIGNMENT_TBL;
7126: l_api_name VARCHAR2(50) := 'pnp_util_func.get_space_assignments';
7127:
7128: BEGIN
7129:
7130: PNP_DEBUG_PKG.DEBUG('----------------------------------');
7127:
7128: BEGIN
7129:
7130: PNP_DEBUG_PKG.DEBUG('----------------------------------');
7131: PNP_DEBUG_PKG.DEBUG('pnp_util_func.get_space_assignments (+)');
7132: PNP_DEBUG_PKG.DEBUG('Get space assignments:INPUT');
7133: PNP_DEBUG_PKG.DEBUG('----------------------------------');
7134: PNP_DEBUG_PKG.DEBUG('Location id : ' || p_location_id);
7135: PNP_DEBUG_PKG.DEBUG('Start Date : ' || p_start_date);
7150: space_assign_cust_rec.cust_space_assign_id;
7151: l_space_assign_cust_tbl(l_index).assign_start_date :=
7152: space_assign_cust_rec.cust_assign_start_date;
7153: l_space_assign_cust_tbl(l_index).assign_end_date :=
7154: NVL(space_assign_cust_rec.cust_assign_end_date,pnp_util_func.g_end_of_time);
7155: l_space_assign_cust_tbl(l_index).allocated_area :=
7156: space_assign_cust_rec.allocated_area;
7157: l_space_assign_cust_tbl(l_index).allocated_area_pct :=
7158: space_assign_cust_rec.allocated_area_pct;
7184: space_assign_emp_rec.emp_space_assign_id;
7185: l_space_assign_emp_tbl(l_index).assign_start_date :=
7186: space_assign_emp_rec.emp_assign_start_date;
7187: l_space_assign_emp_tbl(l_index).assign_end_date :=
7188: NVL(space_assign_emp_rec.emp_assign_end_date,pnp_util_func.g_end_of_time);
7189: l_space_assign_emp_tbl(l_index).allocated_area :=
7190: space_assign_emp_rec.allocated_area;
7191: l_space_assign_emp_tbl(l_index).allocated_area_pct :=
7192: space_assign_emp_rec.allocated_area_pct;
7202: END LOOP;
7203: x_space_assign_emp_tbl := l_space_assign_emp_tbl;
7204:
7205: x_return_status := FND_API.G_RET_STS_SUCCESS;
7206: PNP_DEBUG_PKG.DEBUG('pnp_util_func.get_space_assignments (-)');
7207:
7208: EXCEPTION
7209: WHEN OTHERS THEN
7210: fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
7257: )
7258: IS
7259: l_space_assign_cust_tbl SPACE_ASSIGNMENT_TBL;
7260: l_space_assign_emp_tbl SPACE_ASSIGNMENT_TBL;
7261: l_api_name VARCHAR2(50) := 'pnp_util_func.Validate_assignemnt_for_date ';
7262: USER_DEF_ERROR Exception;
7263:
7264: CURSOR validate_start_date_cursor IS
7265: SELECT 'x'
7314: BEGIN
7315: x_return_status := FND_API.G_RET_STS_SUCCESS;
7316:
7317: PNP_DEBUG_PKG.log('----------------------------------');
7318: PNP_DEBUG_PKG.log('pnp_util_func.validate_assignment_for_date (+)');
7319: PNP_DEBUG_PKG.log('Validate Assignment for date:INPUT');
7320: PNP_DEBUG_PKG.log('----------------------------------');
7321: PNP_DEBUG_PKG.log('Location id : ' || p_location_id);
7322: PNP_DEBUG_PKG.log('Start Date : ' || p_start_date);
7342: END LOOP;
7343:
7344: END IF;
7345:
7346: PNP_DEBUG_PKG.DEBUG('pnp_util_func.validate_assignment_for_date (-)');
7347:
7348: EXCEPTION
7349:
7350: WHEN USER_DEF_ERROR THEN
7398: l_space_assign_emp_tbl SPACE_ASSIGNMENT_TBL;
7399: l_space_assign_tbl SPACE_ASSIGNMENT_TBL;
7400: l_total_Area NUMBER := 0;
7401: l_index NUMBER := 0;
7402: l_api_name VARCHAR2(50):= 'pnp_util_func.Validate_assignable_area';
7403:
7404: UNEXPECTED_ERROR EXCEPTION;
7405: BEGIN
7406:
7404: UNEXPECTED_ERROR EXCEPTION;
7405: BEGIN
7406:
7407: PNP_DEBUG_PKG.log('----------------------------------');
7408: PNP_DEBUG_PKG.log('pnp_util_func.validate_assignable_area (+)');
7409: PNP_DEBUG_PKG.log('Validate Assignable area: INPUT');
7410: PNP_DEBUG_PKG.log('----------------------------------');
7411: PNP_DEBUG_PKG.log('Location id : ' || p_location_id);
7412: PNP_DEBUG_PKG.log('Start Date : ' || p_start_date);
7508: END LOOP;
7509:
7510: END IF;
7511:
7512: PNP_DEBUG_PKG.log('pnp_util_func.validate_assignable_area (-)');
7513: EXCEPTION
7514:
7515:
7516: WHEN UNEXPECTED_ERROR THEN
7575: IS
7576:
7577:
7578: l_space_assign_tbl SPACE_ASSIGNMENT_TBL;
7579: l_api_name VARCHAR2(50) := 'pnp_util_func.validate_date_assignable_area';
7580: l_filename VARCHAR2(40) := 'Date_EFF'||to_char(SYSDATE,'DDMMYYHHMMSS');
7581:
7582: UNEXPECTED_ERROR Exception;
7583: l_str_date_old DATE;
7593: -- Remove after debug
7594: x_return_status := FND_API.G_RET_STS_SUCCESS;
7595:
7596: PNP_DEBUG_PKG.log('---------------------------------------');
7597: PNP_DEBUG_PKG.log('pnp_util_func.validate_date_assignable_area (+)');
7598: PNP_DEBUG_PKG.log('Validate date assignable_area : INPUT PARAMS');
7599: PNP_DEBUG_PKG.log('---------------------------------------');
7600: PNP_DEBUG_PKG.log('Location id : ' || p_location_id);
7601: PNP_DEBUG_PKG.log('Location Type : ' || p_location_type);
7647:
7648: END LOOP;
7649:
7650: PNP_DEBUG_PKG.log('-----------------------------');
7651: PNP_DEBUG_PKG.log('pnp_util_func.validate_date_assignable_area (-)');
7652: PNP_DEBUG_PKG.log('-----------------------------');
7653:
7654: EXCEPTION
7655:
7666: END validate_date_assignable_area;
7667:
7668: ----------------------------------------------------
7669: -- 31-OCT-2002 graghuna o Created due for forms 6i issue.
7670: -- WHEN pnp_util_func are called from forms
7671: -- and the p_As_of_date is not passed, the
7672: -- form generates error even though
7673: -- p_as_of_date is declared as NULL in the
7674: -- package.
8169: WHERE tnc.lease_id = p_lease_id
8170: AND tnc.location_id = p_location_id
8171: AND p_as_of_date BETWEEN nvl(tnc.occupancy_date, tnc.estimated_occupancy_date) AND tnc.expiration_date;
8172:
8173: l_area_rec pnp_util_func.pn_location_area_rec;
8174: l_area NUMBER := null;
8175: l_desc VARCHAR2(100);
8176:
8177: BEGIN
8175: l_desc VARCHAR2(100);
8176:
8177: BEGIN
8178:
8179: l_desc := 'pnp_util_func.fetch_tenancy_area';
8180:
8181: pnp_debug_pkg.log(l_desc ||' (+)');
8182:
8183: FOR area_cur IN get_tenancy_info LOOP
8183: FOR area_cur IN get_tenancy_info LOOP
8184:
8185: IF p_area_type_code IN ('LOCTN_RENTABLE','LOCTN_USABLE','LOCTN_ASSIGNABLE') THEN
8186:
8187: pnp_util_func.fetch_loctn_area(
8188: p_type => pnp_util_func.get_location_type_lookup_code(
8189: p_location_id, p_as_of_date),
8190: p_location_id => p_location_id,
8191: p_as_of_date => p_as_of_date,
8184:
8185: IF p_area_type_code IN ('LOCTN_RENTABLE','LOCTN_USABLE','LOCTN_ASSIGNABLE') THEN
8186:
8187: pnp_util_func.fetch_loctn_area(
8188: p_type => pnp_util_func.get_location_type_lookup_code(
8189: p_location_id, p_as_of_date),
8190: p_location_id => p_location_id,
8191: p_as_of_date => p_as_of_date,
8192: x_area => l_area_rec);
8912:
8913: BEGIN
8914:
8915: FOR rec IN get_loc_cur(p_lease_id) LOOP
8916: l_location_name_rec := pnp_util_func.get_location_name(rec.location_id,p_as_of_date);
8917: END LOOP;
8918:
8919: RETURN NVL(l_location_name_rec.office,
8920: NVL(l_location_name_rec.floor,l_location_name_rec.building)
8956:
8957: BEGIN
8958:
8959: FOR rec IN get_loc_cur(p_lease_id) LOOP
8960: l_location_name_rec := pnp_util_func.get_location_name(rec.location_id,p_as_of_date);
8961: END LOOP;
8962:
8963: RETURN NVL(l_location_name_rec.office_location_code,
8964: NVL(l_location_name_rec.floor_location_code,l_location_name_rec.building_location_code)
9000:
9001: BEGIN
9002:
9003: FOR rec IN get_loc_cur(p_lease_id) LOOP
9004: l_location_name_rec := pnp_util_func.get_location_name(rec.location_id,p_as_of_date);
9005: END LOOP;
9006:
9007: RETURN l_location_name_rec.property_name;
9008:
9016:
9017: -------------------------------------
9018: -- End of Package
9019: --------------------------------------
9020: END pnp_util_func;