[Home] [Help]
257: -- REFERENCE :
258: -- HISTORY :
259:
260: -- 04-SEP-99 Naga Vijayapuram o Created
261: -- 16-JUN-00 Daniel Thota o Included reference to PN_SPACE_ASSIGN_EMP
262: -- AND PN_SPACE_ASSIGN_CUST for new space
263: -- assignment architecture.
264: -- 17-AUG-00 Daniel Thota o Added new parameter p_as_of_date to the
265: -- function. Changed the WHERE clause to
279: -- All the select statements have been
280: -- converted to cursors. The l_location_type
281: -- is checked for null to return 0
282: -- Bug #3384965
283: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP
284: -- and PN_SPACE_ASSIGN_CUST with _ALL table.
285: +============================================================================*/
286:
287: FUNCTION Get_Allocated_Area_By_CC ( p_Location_Id NUMBER ,
301: ,p_Cost_Center IN NUMBER
302: ,p_As_Of_Date IN DATE)
303: IS
304: (SELECT NVL(SUM(Allocated_Area), 0) AS area
305: FROM PN_SPACE_ASSIGN_EMP_ALL
306: WHERE Cost_Center_Code = p_Cost_Center
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 (
401: -- HISTORY :
402: -- 14-MAY-98 Neeraj o Created get_used_area
403: -- 05-AUG-98 Nagabh o Modified get_used_area to get_vacant_area
404: -- 04-SEP-99 Nagabh o Refined - Note ASSUMPTION/ALGORITHM above
405: -- 16-JUN-00 Daniel o Included reference to PN_SPACE_ASSIGN_EMP and
406: -- PN_SPACE_ASSIGN_CUST for new spc asgn architecture
407: -- 17-AUG-00 Daniel o Bug #1379527
408: -- 29-AUG-00 Daniel o calculation of vacant area to be dependent on assignabl
409: -- area instead of usable area - Bug #1386613
426: -- 20-OCT-03 ftanudja o removed nvl's from locn query. 3197410.
427: -- 18-FEB-04 abanerje o Handled NO_DATA_FOUND to return 0. Select statements
428: -- have been converted to cursors. The l_location_type
429: -- is checked for null to return 0. Bug #3384965.
430: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced pn_space_assign_emp
431: -- and pn_space_assign_cust with _ALL table.
432: -- 11-JUN-09 sugupta o Bug 6470318 - Replaced the cursor Utilised_Capacity_Emp_C with
433: -- dynamic query to improve the performance.
434: +===========================================================================*/
465: CURSOR Allocated_Area_Emp_C(p_Location_Id IN NUMBER
466: ,p_As_of_date IN DATE
467: ,p_location_type IN VARCHAR2) IS
468: (SELECT NVL(SUM(allocated_area), 0) AS Area
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
532:
533: CURSOR Allocated_Area_Child_Emp_C(p_Location_Id IN NUMBER
534: ,p_As_of_date IN DATE) IS
535: (SELECT NVL(SUM(e.allocated_area), 0) AS Area
536: FROM pn_space_assign_emp_all e
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
580: l_Allocated_Area_Emp := emp_area.area;
581: END LOOP;
582: */
583: l_query := 'SELECT NVL(SUM(allocated_area), 0) AS Area ';
584: l_query := l_query || ' FROM pn_space_assign_emp_all ';
585: l_query := l_query || ' WHERE emp_assign_start_date <= :p_as_of_date ';
586: l_query := l_query || ' AND NVL(emp_assign_end_date, :l_date) >= :p_as_of_date AND location_id IN ';
587: for loc in c_location_id( p_Location_Id
588: ,l_As_Of_Date
1076: -- HISTORY :
1077: --
1078: -- 14-MAY-98 Neeraj o Created
1079: -- 05-AUG-98 Nagabh o Modified to take only location_id arg.
1080: -- 16-JUN-00 Daniel o Included reference to PN_SPACE_ASSIGN_EMP
1081: -- AND PN_SPACE_ASSIGN_CUST for new space
1082: -- assignment architecture.
1083: -- 17-AUG-00 Daniel o Added new parameter p_as_of_date to the
1084: -- function. Changed the WHERE clause to
1102: -- 18-FEB-04 abanerje o Handled NO_DATA_FOUND to return 0.
1103: -- All the select statements have been converted
1104: -- to cursors. The l_location_type is checked
1105: -- for null to return 0 Bug #3384965.
1106: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
1107: -- PN_SPACE_ASSIGN_CUST with _ALL table.
1108: -- 11-June-09 sugupta o Bug 6470318 - Replaced the cursor Utilised_Capacity_Emp_C with
1109: -- dynamic query to improve the performance.
1110: +===========================================================================*/
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
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
1173:
1174: CURSOR Utilised_Capacity_Child_Emp_C( p_location_id IN NUMBER,
1175: p_As_Of_Date IN DATE) IS
1176: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1177: FROM pn_space_assign_emp_all
1178: WHERE location_id = p_location_id
1179: AND p_as_of_date BETWEEN emp_assign_start_date AND
1180: NVL(emp_assign_end_date, g_end_of_time)
1181: );
1202: LOOP
1203: l_UtilizedCapacityEmp := emp_area.area;
1204: END LOOP;*/
1205: l_query := 'SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area ';
1206: l_query := l_query || ' FROM pn_space_assign_emp_all ';
1207: l_query := l_query || ' WHERE :p_as_of_date BETWEEN emp_assign_start_date AND NVL(emp_assign_end_date, :g_end_of_time) ';
1208: l_query := l_query || ' AND location_id IN ';
1209: for loc in c_location_id( p_Location_Id
1210: ,l_As_Of_Date)
1670: -- in the end date comparision with the as_of_date
1671: -- 19-SEP-00 Lakshmikanth K o Replacing the TO_DATE('31-DEC-2199' , ('DD/MM/YYYY'))
1672: -- with TO_DATE('31/12/2199' , ('DD/MM/YYYY'))
1673: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity.
1674: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
1675: -- PN_SPACE_ASSIGN_CUST with _ALL table.
1676: -------------------------------------------------------------------------------
1677: FUNCTION GET_LOCATION_OCCUPANCY (
1678: p_locationId IN NUMBER,
1688: BEGIN
1689:
1690: SELECT NVL(count (*), 0)
1691: INTO l_occupancyCountEmp
1692: FROM pn_space_assign_emp_all
1693: WHERE location_id = p_locationId
1694: AND emp_assign_start_date <= l_as_of_date
1695: AND NVL(emp_assign_end_date, l_date) >= l_as_of_date;
1696:
2221: l_seqnum NUMBER;
2222:
2223: begin
2224:
2225: SELECT PN_SPACE_ASSIGN_EMP_S.NEXTVAL
2226: INTO l_seqnum
2227: FROM dual;
2228:
2229: RETURN l_seqnum;
2400: -- Date:= TO_DATE('31-DEC-2199' , ('DD/MM/YYYY'))
2401: -- in the end date comparision with the as_of_date
2402: -- 19-SEP-00 Lakshmikanth o Replacing the TO_DATE('31-DEC-2199',('DD/MM/YYYY'))
2403: -- with TO_DATE('31/12/2199' , ('DD/MM/YYYY'))
2404: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
2405: -- PN_SPACE_ASSIGN_CUST with _ALL table.
2406: -------------------------------------------------------------------------------
2407: FUNCTION Get_Occupancy_Status(p_location_id NUMBER,
2408: p_As_Of_Date DATE)
2418: BEGIN
2419:
2420: SELECT 1
2421: INTO l_retnum_emp
2422: FROM pn_space_assign_emp_all
2423: WHERE location_id = p_location_id
2424: AND emp_assign_start_date <= l_as_of_date
2425: AND NVL(emp_assign_end_date, l_date) >= l_as_of_date
2426: AND rownum = 1 ;
4534: -- DISTINCT for performance issues.
4535: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity
4536: -- 20-OCT-03 ftanudja o revamped code to remove 'OR', nvl,GROUP BY.
4537: -- 3197410
4538: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
4539: -- PN_SPACE_ASSIGN_CUST with _ALL table.
4540: -- 16-MAR-07 CSRIPERU o Bug#5959164. Modified cursors emp_assign_future
4541: -- and cust_assign_future to ignore allocated_area_pct
4542: -- while checking for active assignments for a location.
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
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
4562: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4563:
4564: CURSOR emp_assign_current (l_date DATE) IS
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
4573: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4574:
4575: CURSOR emp_assign_current_open (l_date DATE) IS
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
6078: -- RETURNS : Allocated Area
6079: -- REFERENCE :
6080: -- HISTORY :
6081: -- 01-APR-02 Mrinal Misra o Created
6082: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
6083: -- PN_SPACE_ASSIGN_CUST with _ALL table.
6084: +===========================================================================*/
6085: FUNCTION get_min_futr_str_dt(p_loc_id IN NUMBER,
6086: p_str_dt IN DATE)
6092: BEGIN
6093:
6094: SELECT MIN(emp_assign_start_date)
6095: INTO l_emp_min_str_dt
6096: FROM pn_space_assign_emp_all
6097: WHERE location_id = p_loc_id
6098: AND TRUNC(emp_assign_start_date) > TRUNC(p_str_dt);
6099:
6100: SELECT MIN(cust_assign_start_date)
6140: -- Fix for bug # 3354278
6141: -- 30-DEC-04 Kiran o Bug # 4093603 - Added new param p_called_frm_mode
6142: -- if p_called_frm_mode is PNTSPACE_UPDATE then
6143: -- do not get min future start date.
6144: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
6145: -- PN_SPACE_ASSIGN_CUST with _ALL table.
6146: -- 30-JAN-07 csriperu o Bug 5854636 - Moved the future assignment check to
6147: -- validate_vacant_area
6148: +===========================================================================*/
6180: SELECT NVL(SUM(allocated_area), 0)
6181: ,NVL(SUM(allocated_area_pct), 0)
6182: INTO l_allocated_area_emp
6183: ,l_allocated_area_pct_emp
6184: FROM pn_space_assign_emp_all
6185: WHERE location_id = p_loc_id
6186: AND emp_assign_start_date <= NVL(p_new_end_dt,TO_DATE('12/31/4712','mm/dd/yyyy'))
6187: AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','mm/dd/yyyy')) >= p_str_dt;
6188:
6481: p_assgn_area IN NUMBER,
6482: p_str_dt IN DATE,
6483: p_end_dt IN DATE) IS
6484:
6485: l_new_emp_alloc_pct PN_SPACE_ASSIGN_EMP.allocated_area_pct%TYPE;
6486: l_new_cust_alloc_pct PN_SPACE_ASSIGN_CUST.allocated_area_pct%TYPE;
6487: l_new_emp_alloc_area PN_SPACE_ASSIGN_EMP.allocated_area%TYPE;
6488: l_new_cust_alloc_area PN_SPACE_ASSIGN_CUST.allocated_area%TYPE;
6489: l_assgn_str_dt DATE;
6483: p_end_dt IN DATE) IS
6484:
6485: l_new_emp_alloc_pct PN_SPACE_ASSIGN_EMP.allocated_area_pct%TYPE;
6486: l_new_cust_alloc_pct PN_SPACE_ASSIGN_CUST.allocated_area_pct%TYPE;
6487: l_new_emp_alloc_area PN_SPACE_ASSIGN_EMP.allocated_area%TYPE;
6488: l_new_cust_alloc_area PN_SPACE_ASSIGN_CUST.allocated_area%TYPE;
6489: l_assgn_str_dt DATE;
6490: l_assgn_end_dt DATE;
6491: l_date DATE;
6489: l_assgn_str_dt DATE;
6490: l_assgn_end_dt DATE;
6491: l_date DATE;
6492: l_mode VARCHAR2(15);
6493: emp_rec pn_space_assign_emp_all%ROWTYPE;
6494: cust_rec pn_space_assign_cust_all%ROWTYPE;
6495: l_return_status VARCHAR2(30);
6496:
6497: CURSOR get_emp_assgn(p_loc_id IN NUMBER,
6497: CURSOR get_emp_assgn(p_loc_id IN NUMBER,
6498: p_str_dt IN DATE,
6499: p_end_dt IN DATE) IS
6500: SELECT *
6501: FROM pn_space_assign_emp_all
6502: WHERE location_id = p_loc_id
6503: AND emp_assign_start_date <= p_end_dt
6504: AND NVL(emp_assign_end_date, g_end_of_time) >= p_str_dt;
6505:
6517: CURSOR get_emp_assgn1(p_loc_id IN NUMBER,
6518: p_str_dt IN DATE,
6519: p_end_dt IN DATE) IS
6520: SELECT *
6521: FROM pn_space_assign_emp_all
6522: WHERE location_id = p_loc_id
6523: AND NVL(emp_assign_end_date, g_end_of_time) >= p_str_dt;
6524:
6525: CURSOR get_cust_assgn1(p_loc_id IN NUMBER,
6553: EXIT WHEN get_emp_assgn%NOTFOUND;
6554: END IF;
6555: -- 102403 -- date track space assignment
6556:
6557: PN_SPACE_ASSIGN_EMP_PKG.tlempinfo := emp_rec;
6558: l_new_emp_alloc_area := ROUND(((NVL(emp_rec.allocated_area_pct,0)*p_assgn_area)/100),4);
6559:
6560: IF emp_rec.emp_assign_start_date >= p_str_dt AND
6561: NVL(emp_rec.emp_assign_end_date,g_end_of_time) <= p_end_dt THEN
6571: l_assgn_str_dt := p_str_dt;
6572: END IF;
6573: END IF;
6574:
6575: PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW(
6576: X_EMP_SPACE_ASSIGN_ID => emp_rec.emp_space_assign_id,
6577: X_ATTRIBUTE1 => emp_rec.attribute1,
6578: X_ATTRIBUTE2 => emp_rec.attribute2,
6579: X_ATTRIBUTE3 => emp_rec.attribute3,
6698: -- 14-MAY-02 Ashish Kumar o Created
6699: -- 30-OCT-02 Satish Tripathi o Access _all table for performance issues.
6700: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity
6701: -- 20-OCT-03 ftanudja o removed nvl from locn tbl filter. 3197410.
6702: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
6703: -- PN_SPACE_ASSIGN_CUST with _ALL table.
6704: +===========================================================================*/
6705: PROCEDURE get_area ( p_Location_Id IN NUMBER,
6706: p_location_type IN VARCHAR2,
6745: PRIOR active_end_date;
6746:
6747: CURSOR c_space_emp is
6748: SELECT NVL(SUM(Allocated_Area), 0),NVL(SUM(UTILIZED_AREA),0)
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
6858: -- RETURNS : BOOLEAN
6859: -- REFERENCE :
6860: -- HISTORY :
6861: -- 29-MAY-02 Kiran Hegde o Created - Fix for Bug#2384573
6862: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
6863: -- PN_SPACE_ASSIGN_CUST with _ALL table.
6864: +===========================================================================*/
6865:
6866: FUNCTION validate_assignable_area ( p_Location_Id IN NUMBER,
6873: INVALID_ASSIGNABLE_AREA EXCEPTION;
6874:
6875: CURSOR start_date_cur IS
6876: SELECT emp_assign_start_date
6877: FROM pn_space_assign_emp_all
6878: WHERE location_id = p_Location_Id
6879: UNION
6880: SELECT cust_assign_start_date
6881: FROM pn_space_assign_cust_all
7079: | 26-JUL-2002 graghuna o Created
7080: | 27-aug-2003 kkhegde o Changed the cursor queries to pick all the ovelapping
7081: | assignments for given start-end dates.
7082: | 16-MAR-2007 CSRIPERU o Bug#5959164. Modified Cursors pn_space_assign_cust_cursor
7083: | and pn_space_assign_emp_cursor to ignore allocated_area while
7084: | checking for active assignments to a location.
7085: +===========================================================================*/
7086:
7087:
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);
7109: --Bug#5959164 AND NVL(allocated_area,0) > 0;
7110:
7111: CURSOR pn_space_assign_emp_cursor IS
7112: SELECT *
7113: FROM pn_space_assign_emp_all
7114: WHERE location_id IN
7115: ( SELECT location_id
7109: --Bug#5959164 AND NVL(allocated_area,0) > 0;
7110:
7111: CURSOR pn_space_assign_emp_cursor IS
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
7171:
7172: x_space_assign_cust_tbl := l_space_assign_cust_tbl;
7173:
7174:
7175: FOR space_assign_emp_rec in pn_space_assign_emp_cursor
7176: LOOP
7177: l_index := l_index + 1;
7178: PNP_DEBUG_PKG.DEBUG('Assigning emp data index : '|| l_index);
7179: l_space_assign_emp_tbl(l_index).person_id :=
7238: -- end date of location.
7239: -- 07-APR-04 abanerje o Added NVL to end dates for cursors
7240: -- validate_start_date_cursor and
7241: -- validate_end_date_cursor. Bug #3486311
7242: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
7243: -- PN_SPACE_ASSIGN_CUST with _ALL table.
7244: -- 16-MAR-07 csriperu o Bug#5959164. Modified Cursors validate_start_date_cursor
7245: -- and validate_end_date_cursor to ignore allocated_area and
7246: -- allocated_area_pct while checking for active assignments
7264: CURSOR validate_start_date_cursor IS
7265: SELECT 'x'
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 )
7289: CURSOR validate_end_date_cursor IS
7290: SELECT 'x'
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 )