[Home] [Help]
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
266: -- include p_as_of_date - Bug Fix for #1379527
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 ,
288: p_Cost_Center VARCHAR2,
321: ,p_Cost_Center IN NUMBER
322: ,p_As_Of_Date IN DATE)
323: IS
324: (SELECT NVL(SUM(Allocated_Area), 0) AS area
325: FROM PN_SPACE_ASSIGN_CUST_ALL
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
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
410: -- 08-SEP-00 Daniel o Bug #1379527 Re-introduced comparison of p_as_of_date
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: +===========================================================================*/
435:
506: CURSOR Allocated_Area_Cust_C(p_Location_Id IN NUMBER
507: ,p_As_of_date IN DATE
508: ,p_location_type IN VARCHAR2) IS
509: (SELECT NVL(SUM(allocated_area), 0) AS Area
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
545:
546: CURSOR Allocated_Area_Child_Cust_C(p_Location_Id IN NUMBER
547: ,p_As_of_date IN DATE) IS
548: (SELECT NVL(SUM(c.allocated_area), 0) AS Area
549: FROM pn_space_assign_cust_all c
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
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
1085: -- include p_as_of_date - Bug Fix for #1379527
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: +===========================================================================*/
1111:
1157:
1158: CURSOR Utilised_Capacity_Cust_C( p_location_id IN NUMBER
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
1182:
1183: CURSOR Utilised_Capacity_Child_Cust_C( p_location_id IN NUMBER,
1184: p_As_Of_Date IN DATE) IS
1185: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1186: FROM pn_space_assign_cust_all
1187: WHERE location_id = p_location_id
1188: AND p_as_of_date BETWEEN cust_assign_start_date AND
1189: NVL(cust_assign_end_date, g_end_of_time)
1190: );
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,
1679: p_As_Of_Date IN DATE
1695: AND NVL(emp_assign_end_date, l_date) >= l_as_of_date;
1696:
1697: SELECT NVL(count (*), 0)
1698: INTO l_occupancyCountCust
1699: FROM pn_space_assign_cust_all
1700: WHERE location_id = p_locationId
1701: AND cust_assign_start_date <= l_as_of_date
1702: AND NVL(cust_assign_end_date, l_date) >= l_as_of_date;
1703:
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)
2409: RETURN NUMBER
2426: AND rownum = 1 ;
2427:
2428: SELECT 1
2429: INTO l_retnum_cust
2430: FROM pn_space_assign_cust_all
2431: WHERE location_id = p_location_id
2432: AND cust_assign_start_date <= l_as_of_date
2433: AND NVL(cust_assign_end_date, l_date) >= l_as_of_date
2434: AND rownum = 1 ;
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.
4543: +=============================================================================*/
4584: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4585:
4586: CURSOR cust_assign_future (l_date DATE) IS
4587: SELECT 'Y'
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
4596: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4597:
4598: CURSOR cust_assign_current (l_date DATE) IS
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
4607: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4608:
4609: CURSOR cust_assign_current_open (l_date DATE) IS
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
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)
6087: RETURN DATE IS
6098: AND TRUNC(emp_assign_start_date) > TRUNC(p_str_dt);
6099:
6100: SELECT MIN(cust_assign_start_date)
6101: INTO l_cust_min_str_dt
6102: FROM pn_space_assign_cust_all
6103: WHERE location_id = p_loc_id
6104: AND TRUNC(cust_assign_start_date) > TRUNC(p_str_dt);
6105:
6106: IF NVL(TRUNC(l_emp_min_str_dt),TO_DATE('12/31/4712','mm/dd/yyyy')) <
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: +===========================================================================*/
6149:
6189: SELECT NVL(SUM(allocated_area), 0)
6190: ,NVL(SUM(allocated_area_pct), 0)
6191: INTO l_allocated_area_cust
6192: ,l_allocated_area_pct_cust
6193: FROM pn_space_assign_cust_all
6194: WHERE location_id = p_loc_id
6195: AND cust_assign_start_date <= NVL(p_new_end_dt,TO_DATE('12/31/4712','mm/dd/yyyy'))
6196: AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','mm/dd/yyyy')) >= p_str_dt;
6197:
6468: | 14-Nov-03 Satish o Fix for BUG# 3260023 (Issue 4). Made emp_rec, cust_rec
6469: | as _ALL%ROWTYPE. Modified all 4 cursors to select from
6470: | _ALL tables.
6471: | 28-Apr-04 vmmehta o Fix for BUG# 3197182. Changed call to
6472: | pn_space_assign_cust_pkg.update_row
6473: | Added parameter x_return_status
6474: | 18-JUN-04 Mrinal o Fixed for BUG# 3297892, calculate allocated_area
6475: | based on alloc_area_pct.
6476: | 13-AUG-04 Anand o Added NVL for emp/cust_end_date attributes.
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;
6490: l_assgn_end_dt DATE;
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;
6492: l_mode VARCHAR2(15);
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,
6498: p_str_dt IN DATE,
6506: CURSOR get_cust_assgn(p_loc_id IN NUMBER,
6507: p_str_dt IN DATE,
6508: p_end_dt IN DATE) IS
6509: SELECT *
6510: FROM pn_space_assign_cust_all
6511: WHERE location_id = p_loc_id
6512: AND cust_assign_start_date <= p_end_dt
6513: AND NVL(cust_assign_end_date, g_end_of_time) >= p_str_dt;
6514:
6525: CURSOR get_cust_assgn1(p_loc_id IN NUMBER,
6526: p_str_dt IN DATE,
6527: p_end_dt IN DATE) IS
6528: SELECT *
6529: FROM pn_space_assign_cust_all
6530: WHERE location_id = p_loc_id
6531: AND NVL(cust_assign_end_date, g_end_of_time) >= p_str_dt;
6532:
6533: -- 102403 -- date track space assignment
6618: FETCH get_cust_assgn INTO cust_rec;
6619: EXIT WHEN get_cust_assgn%NOTFOUND;
6620: END IF;
6621:
6622: PN_SPACE_ASSIGN_CUST_PKG.tlcustinfo := cust_rec;
6623: l_new_cust_alloc_area := ROUND(((NVL(cust_rec.allocated_area_pct,0)*p_assgn_area)/100),4);
6624:
6625: IF cust_rec.cust_assign_start_date >= p_str_dt AND
6626: NVL(cust_rec.cust_assign_end_date,g_end_of_time) <= p_end_dt THEN
6635: l_assgn_str_dt := p_str_dt;
6636: END IF;
6637: END IF;
6638:
6639: PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW(
6640: X_CUST_SPACE_ASSIGN_ID => cust_rec.CUST_SPACE_ASSIGN_ID,
6641: X_LOCATION_ID => cust_rec.LOCATION_ID,
6642: X_CUST_ACCOUNT_ID => cust_rec.CUST_ACCOUNT_ID,
6643: X_SITE_USE_ID => cust_rec.SITE_USE_ID,
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,
6707: p_area_type IN VARCHAR2 ,
6761: );
6762:
6763: CURSOR c_space_cust is
6764: SELECT NVL(SUM(Allocated_Area), 0),NVL(SUM(UTILIZED_AREA),0)
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
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,
6867: p_Location_Type IN VARCHAR2,
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
6882: WHERE location_id = p_Location_Id
6883: ORDER BY 1;
6884:
6885: BEGIN
7078: | MODIFICATION HISTORY
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:
7095: x_return_status OUT NOCOPY VARCHAR2,
7096: x_return_message OUT NOCOPY VARCHAR2
7097: )
7098: IS
7099: CURSOR pn_space_assign_cust_cursor IS
7100: SELECT *
7101: FROM pn_space_assign_cust_all
7102: WHERE location_id IN
7103: ( SELECT location_id
7097: )
7098: IS
7099: CURSOR pn_space_assign_cust_cursor IS
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
7137: PNP_DEBUG_PKG.DEBUG('----------------------------------');
7138:
7139:
7140:
7141: FOR space_assign_cust_rec in pn_space_assign_cust_cursor
7142: LOOP
7143: l_index := l_index + 1;
7144: PNP_DEBUG_PKG.DEBUG('Assigning Cust data index : '|| l_index);
7145: l_space_assign_cust_tbl(l_index).cust_account_id :=
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
7247: -- to a location.
7275: AND emp_assign_start_date < p_start_date
7276: AND NVL(emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= p_start_date_old
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 )
7300: AND NVL(emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) > p_end_date
7301: AND emp_assign_start_date <= p_end_date_old
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 )