[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: +===========================================================================*/
433:
434: FUNCTION get_vacant_area ( p_location_id NUMBER,
435: p_as_of_date DATE)
482: CURSOR Allocated_Area_Cust_C(p_Location_Id IN NUMBER
483: ,p_As_of_date IN DATE
484: ,p_location_type IN VARCHAR2) IS
485: (SELECT NVL(SUM(allocated_area), 0) AS Area
486: FROM pn_space_assign_cust_all
487: WHERE cust_assign_start_date <= p_As_of_date
488: AND NVL(cust_assign_end_date, l_date) >= p_As_of_date
489: AND location_Id IN (SELECT location_id
490: FROM pn_locations_all
521:
522: CURSOR Allocated_Area_Child_Cust_C(p_Location_Id IN NUMBER
523: ,p_As_of_date IN DATE) IS
524: (SELECT NVL(SUM(c.allocated_area), 0) AS Area
525: FROM pn_space_assign_cust_all c
526: WHERE c.cust_assign_start_date <= p_as_of_date
527: AND NVL(c.cust_assign_end_date, l_date) >= p_as_of_date
528: AND c.location_id = p_location_id
529: AND EXISTS (SELECT NULL
998: --
999: -- 14-MAY-98 Neeraj o Created
1000: -- 05-AUG-98 Nagabh o Modified to take only location_id arg.
1001: -- 16-JUN-00 Daniel o Included reference to PN_SPACE_ASSIGN_EMP
1002: -- AND PN_SPACE_ASSIGN_CUST for new space
1003: -- assignment architecture.
1004: -- 17-AUG-00 Daniel o Added new parameter p_as_of_date to the
1005: -- function. Changed the WHERE clause to
1006: -- include p_as_of_date - Bug Fix for #1379527
1024: -- All the select statements have been converted
1025: -- to cursors. The l_location_type is checked
1026: -- for null to return 0 Bug #3384965.
1027: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
1028: -- PN_SPACE_ASSIGN_CUST with _ALL table.
1029: +===========================================================================*/
1030:
1031: FUNCTION get_utilized_capacity ( p_location_id IN NUMBER,
1032: p_As_Of_Date IN DATE)
1056:
1057: CURSOR Utilised_Capacity_Cust_C( p_location_id IN NUMBER
1058: ,p_As_Of_Date IN DATE) IS
1059: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1060: FROM pn_space_assign_cust_all
1061: WHERE location_id IN (SELECT a.location_id
1062: FROM pn_locations_all a
1063: WHERE p_as_of_date BETWEEN active_start_date AND
1064: active_end_date
1081:
1082: CURSOR Utilised_Capacity_Child_Cust_C( p_location_id IN NUMBER,
1083: p_As_Of_Date IN DATE) IS
1084: (SELECT SUM(NVL(UTILIZED_AREA,0)) AS Area
1085: FROM pn_space_assign_cust_all
1086: WHERE location_id = p_location_id
1087: AND p_as_of_date BETWEEN cust_assign_start_date AND
1088: NVL(cust_assign_end_date, g_end_of_time)
1089: );
1516: -- 19-SEP-00 Lakshmikanth K o Replacing the TO_DATE('31-DEC-2199' , ('DD/MM/YYYY'))
1517: -- with TO_DATE('31/12/2199' , ('DD/MM/YYYY'))
1518: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity.
1519: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
1520: -- PN_SPACE_ASSIGN_CUST with _ALL table.
1521: -------------------------------------------------------------------------------
1522: FUNCTION GET_LOCATION_OCCUPANCY (
1523: p_locationId IN NUMBER,
1524: p_As_Of_Date IN DATE
1540: AND NVL(emp_assign_end_date, l_date) >= l_as_of_date;
1541:
1542: SELECT NVL(count (*), 0)
1543: INTO l_occupancyCountCust
1544: FROM pn_space_assign_cust_all
1545: WHERE location_id = p_locationId
1546: AND cust_assign_start_date <= l_as_of_date
1547: AND NVL(cust_assign_end_date, l_date) >= l_as_of_date;
1548:
2246: -- in the end date comparision with the as_of_date
2247: -- 19-SEP-00 Lakshmikanth o Replacing the TO_DATE('31-DEC-2199',('DD/MM/YYYY'))
2248: -- with TO_DATE('31/12/2199' , ('DD/MM/YYYY'))
2249: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
2250: -- PN_SPACE_ASSIGN_CUST with _ALL table.
2251: -------------------------------------------------------------------------------
2252: FUNCTION Get_Occupancy_Status(p_location_id NUMBER,
2253: p_As_Of_Date DATE)
2254: RETURN NUMBER
2271: AND rownum = 1 ;
2272:
2273: SELECT 1
2274: INTO l_retnum_cust
2275: FROM pn_space_assign_cust_all
2276: WHERE location_id = p_location_id
2277: AND cust_assign_start_date <= l_as_of_date
2278: AND NVL(cust_assign_end_date, l_date) >= l_as_of_date
2279: AND rownum = 1 ;
4377: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity
4378: -- 20-OCT-03 ftanudja o revamped code to remove 'OR', nvl,GROUP BY.
4379: -- 3197410
4380: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
4381: -- PN_SPACE_ASSIGN_CUST with _ALL table.
4382: -- 16-MAR-07 CSRIPERU o Bug#5959164. Modified cursors emp_assign_future
4383: -- and cust_assign_future to ignore allocated_area_pct
4384: -- while checking for active assignments for a location.
4385: +=============================================================================*/
4426: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4427:
4428: CURSOR cust_assign_future (l_date DATE) IS
4429: SELECT 'Y'
4430: FROM pn_space_assign_cust_all
4431: WHERE cust_assign_start_date > l_date
4432: --Bug#5959164 AND allocated_area_pct > 0
4433: AND location_id IN (SELECT location_id
4434: FROM pn_locations_all
4438: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4439:
4440: CURSOR cust_assign_current (l_date DATE) IS
4441: SELECT 'Y'
4442: FROM pn_space_assign_cust_all
4443: WHERE l_date BETWEEN cust_assign_start_date AND cust_assign_end_date
4444: AND location_id IN (SELECT location_id
4445: FROM pn_locations_all
4446: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
4449: AND l_as_of_date BETWEEN PRIOR active_start_date AND PRIOR active_end_date);
4450:
4451: CURSOR cust_assign_current_open (l_date DATE) IS
4452: SELECT 'Y'
4453: FROM pn_space_assign_cust_all
4454: WHERE l_date >= cust_assign_start_date AND cust_assign_end_date IS NULL -- for open assignments time
4455: AND location_id IN (SELECT location_id
4456: FROM pn_locations_all
4457: WHERE l_as_of_date BETWEEN active_start_date AND active_end_date
5918: -- REFERENCE :
5919: -- HISTORY :
5920: -- 01-APR-02 Mrinal Misra o Created
5921: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
5922: -- PN_SPACE_ASSIGN_CUST with _ALL table.
5923: +===========================================================================*/
5924: FUNCTION get_min_futr_str_dt(p_loc_id IN NUMBER,
5925: p_str_dt IN DATE)
5926: RETURN DATE IS
5937: AND TRUNC(emp_assign_start_date) > TRUNC(p_str_dt);
5938:
5939: SELECT MIN(cust_assign_start_date)
5940: INTO l_cust_min_str_dt
5941: FROM pn_space_assign_cust_all
5942: WHERE location_id = p_loc_id
5943: AND TRUNC(cust_assign_start_date) > TRUNC(p_str_dt);
5944:
5945: IF NVL(TRUNC(l_emp_min_str_dt),TO_DATE('12/31/4712','mm/dd/yyyy')) <
5980: -- 30-DEC-04 Kiran o Bug # 4093603 - Added new param p_called_frm_mode
5981: -- if p_called_frm_mode is PNTSPACE_UPDATE then
5982: -- do not get min future start date.
5983: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
5984: -- PN_SPACE_ASSIGN_CUST with _ALL table.
5985: -- 30-JAN-07 csriperu o Bug 5854636 - Moved the future assignment check to
5986: -- validate_vacant_area
5987: +===========================================================================*/
5988:
6028: SELECT NVL(SUM(allocated_area), 0)
6029: ,NVL(SUM(allocated_area_pct), 0)
6030: INTO l_allocated_area_cust
6031: ,l_allocated_area_pct_cust
6032: FROM pn_space_assign_cust_all
6033: WHERE location_id = p_loc_id
6034: AND cust_assign_start_date <= NVL(p_new_end_dt,TO_DATE('12/31/4712','mm/dd/yyyy'))
6035: AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','mm/dd/yyyy')) >= p_str_dt;
6036:
6307: | 14-Nov-03 Satish o Fix for BUG# 3260023 (Issue 4). Made emp_rec, cust_rec
6308: | as _ALL%ROWTYPE. Modified all 4 cursors to select from
6309: | _ALL tables.
6310: | 28-Apr-04 vmmehta o Fix for BUG# 3197182. Changed call to
6311: | pn_space_assign_cust_pkg.update_row
6312: | Added parameter x_return_status
6313: | 18-JUN-04 Mrinal o Fixed for BUG# 3297892, calculate allocated_area
6314: | based on alloc_area_pct.
6315: | 13-AUG-04 Anand o Added NVL for emp/cust_end_date attributes.
6321: p_str_dt IN DATE,
6322: p_end_dt IN DATE) IS
6323:
6324: l_new_emp_alloc_pct PN_SPACE_ASSIGN_EMP.allocated_area_pct%TYPE;
6325: l_new_cust_alloc_pct PN_SPACE_ASSIGN_CUST.allocated_area_pct%TYPE;
6326: l_new_emp_alloc_area PN_SPACE_ASSIGN_EMP.allocated_area%TYPE;
6327: l_new_cust_alloc_area PN_SPACE_ASSIGN_CUST.allocated_area%TYPE;
6328: l_assgn_str_dt DATE;
6329: l_assgn_end_dt DATE;
6323:
6324: l_new_emp_alloc_pct PN_SPACE_ASSIGN_EMP.allocated_area_pct%TYPE;
6325: l_new_cust_alloc_pct PN_SPACE_ASSIGN_CUST.allocated_area_pct%TYPE;
6326: l_new_emp_alloc_area PN_SPACE_ASSIGN_EMP.allocated_area%TYPE;
6327: l_new_cust_alloc_area PN_SPACE_ASSIGN_CUST.allocated_area%TYPE;
6328: l_assgn_str_dt DATE;
6329: l_assgn_end_dt DATE;
6330: l_date DATE;
6331: l_mode VARCHAR2(15);
6329: l_assgn_end_dt DATE;
6330: l_date DATE;
6331: l_mode VARCHAR2(15);
6332: emp_rec pn_space_assign_emp_all%ROWTYPE;
6333: cust_rec pn_space_assign_cust_all%ROWTYPE;
6334: l_return_status VARCHAR2(30);
6335:
6336: CURSOR get_emp_assgn(p_loc_id IN NUMBER,
6337: p_str_dt IN DATE,
6345: CURSOR get_cust_assgn(p_loc_id IN NUMBER,
6346: p_str_dt IN DATE,
6347: p_end_dt IN DATE) IS
6348: SELECT *
6349: FROM pn_space_assign_cust_all
6350: WHERE location_id = p_loc_id
6351: AND cust_assign_start_date <= p_end_dt
6352: AND NVL(cust_assign_end_date, g_end_of_time) >= p_str_dt;
6353:
6364: CURSOR get_cust_assgn1(p_loc_id IN NUMBER,
6365: p_str_dt IN DATE,
6366: p_end_dt IN DATE) IS
6367: SELECT *
6368: FROM pn_space_assign_cust_all
6369: WHERE location_id = p_loc_id
6370: AND NVL(cust_assign_end_date, g_end_of_time) >= p_str_dt;
6371:
6372: -- 102403 -- date track space assignment
6457: FETCH get_cust_assgn INTO cust_rec;
6458: EXIT WHEN get_cust_assgn%NOTFOUND;
6459: END IF;
6460:
6461: PN_SPACE_ASSIGN_CUST_PKG.tlcustinfo := cust_rec;
6462: l_new_cust_alloc_area := ROUND(((NVL(cust_rec.allocated_area_pct,0)*p_assgn_area)/100),2);
6463:
6464: IF cust_rec.cust_assign_start_date >= p_str_dt AND
6465: NVL(cust_rec.cust_assign_end_date,g_end_of_time) <= p_end_dt THEN
6474: l_assgn_str_dt := p_str_dt;
6475: END IF;
6476: END IF;
6477:
6478: PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW(
6479: X_CUST_SPACE_ASSIGN_ID => cust_rec.CUST_SPACE_ASSIGN_ID,
6480: X_LOCATION_ID => cust_rec.LOCATION_ID,
6481: X_CUST_ACCOUNT_ID => cust_rec.CUST_ACCOUNT_ID,
6482: X_SITE_USE_ID => cust_rec.SITE_USE_ID,
6538: -- 30-OCT-02 Satish Tripathi o Access _all table for performance issues.
6539: -- 31-OCT-01 graghuna o added p_as_of_date for Location Date-Effectivity
6540: -- 20-OCT-03 ftanudja o removed nvl from locn tbl filter. 3197410.
6541: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
6542: -- PN_SPACE_ASSIGN_CUST with _ALL table.
6543: +===========================================================================*/
6544: PROCEDURE get_area ( p_Location_Id IN NUMBER,
6545: p_location_type IN VARCHAR2,
6546: p_area_type IN VARCHAR2 ,
6600: );
6601:
6602: CURSOR c_space_cust is
6603: SELECT NVL(SUM(Allocated_Area), 0),NVL(SUM(UTILIZED_AREA),0)
6604: FROM PN_SPACE_ASSIGN_CUST_ALL
6605: WHERE cust_assign_start_date <= l_as_of_date
6606: AND NVL(cust_assign_end_date, l_date) >= l_as_of_date
6607: AND Location_Id IN (SELECT Location_Id
6608: FROM pn_locations_all
6698: -- REFERENCE :
6699: -- HISTORY :
6700: -- 29-MAY-02 Kiran Hegde o Created - Fix for Bug#2384573
6701: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
6702: -- PN_SPACE_ASSIGN_CUST with _ALL table.
6703: +===========================================================================*/
6704:
6705: FUNCTION validate_assignable_area ( p_Location_Id IN NUMBER,
6706: p_Location_Type IN VARCHAR2,
6716: FROM pn_space_assign_emp_all
6717: WHERE location_id = p_Location_Id
6718: UNION
6719: SELECT cust_assign_start_date
6720: FROM pn_space_assign_cust_all
6721: WHERE location_id = p_Location_Id
6722: ORDER BY 1;
6723:
6724: BEGIN
6917: | MODIFICATION HISTORY
6918: | 26-JUL-2002 graghuna o Created
6919: | 27-aug-2003 kkhegde o Changed the cursor queries to pick all the ovelapping
6920: | assignments for given start-end dates.
6921: | 16-MAR-2007 CSRIPERU o Bug#5959164. Modified Cursors pn_space_assign_cust_cursor
6922: | and pn_space_assign_emp_cursor to ignore allocated_area while
6923: | checking for active assignments to a location.
6924: +===========================================================================*/
6925:
6934: x_return_status OUT NOCOPY VARCHAR2,
6935: x_return_message OUT NOCOPY VARCHAR2
6936: )
6937: IS
6938: CURSOR pn_space_assign_cust_cursor IS
6939: SELECT *
6940: FROM pn_space_assign_cust_all
6941: WHERE location_id IN
6942: ( SELECT location_id
6936: )
6937: IS
6938: CURSOR pn_space_assign_cust_cursor IS
6939: SELECT *
6940: FROM pn_space_assign_cust_all
6941: WHERE location_id IN
6942: ( SELECT location_id
6943: FROM pn_locations_all
6944: START WITH location_id = p_location_id
6976: PNP_DEBUG_PKG.DEBUG('----------------------------------');
6977:
6978:
6979:
6980: FOR space_assign_cust_rec in pn_space_assign_cust_cursor
6981: LOOP
6982: l_index := l_index + 1;
6983: PNP_DEBUG_PKG.DEBUG('Assigning Cust data index : '|| l_index);
6984: l_space_assign_cust_tbl(l_index).cust_account_id :=
7078: -- 07-APR-04 abanerje o Added NVL to end dates for cursors
7079: -- validate_start_date_cursor and
7080: -- validate_end_date_cursor. Bug #3486311
7081: -- 15-JUN-05 piagrawa o Bug 4307795 - Replaced PN_SPACE_ASSIGN_EMP,
7082: -- PN_SPACE_ASSIGN_CUST with _ALL table.
7083: -- 16-MAR-07 csriperu o Bug#5959164. Modified Cursors validate_start_date_cursor
7084: -- and validate_end_date_cursor to ignore allocated_area and
7085: -- allocated_area_pct while checking for active assignments
7086: -- to a location.
7114: AND emp_assign_start_date < p_start_date
7115: AND NVL(emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= p_start_date_old
7116: UNION
7117: SELECT 'x'
7118: FROM pn_space_assign_cust_all
7119: WHERE location_id IN (SELECT location_id
7120: FROM pn_locations_all
7121: START WITH location_id = p_location_id
7122: CONNECT BY PRIOR location_id = parent_location_id )
7139: AND NVL(emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) > p_end_date
7140: AND emp_assign_start_date <= p_end_date_old
7141: UNION
7142: SELECT 'x'
7143: FROM pn_space_assign_cust_all
7144: WHERE location_id IN (SELECT location_id
7145: FROM pn_locations_all
7146: START WITH location_id = p_location_id
7147: CONNECT BY PRIOR location_id = parent_location_id )