[Home] [Help]
939: --
940: -- IN PARAM : Location Id.
941: -- History :
942: -- 20-OCT-03 DThota o Created. Fix for bug # 3234403
943: -- 05-NOV-03 DThota o Initialized pn_space_assign_emp_pkg.tlempinfo
944: -- := emp_split_rec
945: -- 06-NOV-03 DThota o Aliased assignable_area to allocated_area
946: -- in csr_main.
947: -- 07-NOV-03 DThota o Used min on active_start_date and max on
964: -- 19-MAY-05 ftanudja o #4349490 - Changed csr_emp and csr_cust to increase
965: -- range by +/- 1 day so that split assignments are
966: -- included.
967: -- 21-JUN-05 hareesha o Bug 4284035 - Replaced pn_space_assign_cust,
968: -- pn_space_assign_emp and pn_loactions with _ALL
969: -- table.
970: -- 25-Aug-05 hareesha o Bug 4551557 - Modified csr_main cursor query to
971: -- include space assignments starting after the specified
972: -- end_date.
1013: ,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
1014: ,allocated_area
1015: ,location_id
1016: ,'EMP: '||emp_space_assign_id assign_type_id
1017: FROM pn_space_assign_emp_all
1018: WHERE location_id = p_location_id
1019: AND emp_assign_start_date <= p_end_date
1020: AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= p_start_date
1021: UNION ALL
1023: ,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
1024: ,allocated_area
1025: ,location_id
1026: ,'EMP: '||emp_space_assign_id assign_type_id
1027: FROM pn_space_assign_emp_all
1028: WHERE location_id = p_location_id
1029: AND emp_assign_start_date > p_end_date
1030: UNION ALL
1031: SELECT min(active_start_date) start_date
1067: ,allocated_area
1068: ,allocated_area_pct
1069: ,location_id
1070: ,ROWID
1071: FROM pn_space_assign_emp_all
1072: WHERE location_id = p_location_id
1073: AND emp_assign_start_date <= (p_end_date + 1)
1074: AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= (p_start_date - 1);
1075:
1089: -------------------------------------------------------------------------------------
1090: -- This cursor is used to get all employee assignment records whose start date is less than
1091: -- and end date greater than the split date returned by 'process vacancy'.
1092: -------------------------------------------------------------------------------------
1093: CURSOR csr_emp_split(p_as_of_date PN_SPACE_ASSIGN_EMP_ALL.emp_assign_start_date%TYPE) IS
1094: SELECT *
1095: FROM pn_space_assign_emp_all
1096: WHERE location_id = p_location_id
1097: AND emp_assign_start_date < p_as_of_date
1091: -- and end date greater than the split date returned by 'process vacancy'.
1092: -------------------------------------------------------------------------------------
1093: CURSOR csr_emp_split(p_as_of_date PN_SPACE_ASSIGN_EMP_ALL.emp_assign_start_date%TYPE) IS
1094: SELECT *
1095: FROM pn_space_assign_emp_all
1096: WHERE location_id = p_location_id
1097: AND emp_assign_start_date < p_as_of_date
1098: AND NVL(emp_assign_end_date,to_date('12/31/4712','MM/DD/YYYY')) >= p_as_of_date
1099: ORDER BY emp_assign_start_date, emp_assign_end_date;
1258: pnp_debug_pkg.debug('Update Emp Row Assign_Id: '|| emp_split_rec.emp_space_assign_id
1259: ||', Loc_Id: '|| emp_split_rec.location_id
1260: ||', Emp_Id: '|| emp_split_rec.person_id);
1261:
1262: pn_space_assign_emp_pkg.tlempinfo := emp_split_rec;
1263: ---------------------------------------------------------------------------------------
1264: -- Splits the existing assignment with the (date-1) passed from the date table returned
1265: -- by process vacancy and creates a new assignment with the split date
1266: ---------------------------------------------------------------------------------------
1263: ---------------------------------------------------------------------------------------
1264: -- Splits the existing assignment with the (date-1) passed from the date table returned
1265: -- by process vacancy and creates a new assignment with the split date
1266: ---------------------------------------------------------------------------------------
1267: PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW(
1268: X_EMP_SPACE_ASSIGN_ID => emp_split_rec.EMP_SPACE_ASSIGN_ID
1269: ,X_ATTRIBUTE1 => emp_split_rec.ATTRIBUTE1
1270: ,X_ATTRIBUTE2 => emp_split_rec.ATTRIBUTE2
1271: ,X_ATTRIBUTE3 => emp_split_rec.ATTRIBUTE3
1364:
1365: ELSE
1366: l_allocated_area := TRUNC((l_assignable_area * emp_rec.allocated_area_pct)/100, 2); /*4533091*/
1367: IF l_allocated_area <> emp_rec.allocated_area THEN
1368: UPDATE pn_space_assign_emp_all
1369: SET allocated_area = l_allocated_area
1370: WHERE ROWID = emp_rec.ROWID;
1371: END IF;
1372: END IF;
1393: -- add/subtract the fractional remainder after space
1394: -- redistribution to one record.
1395: -- 20-OCT-03 dthota o bug # 3234403 - Copied this procedure from PNTSPACE.pld
1396: -- 14-DEC-04 STripath o Fixed for bug# 4092157. If l_total_pct <> 100, update
1397: -- either pn_space_assign_emp or pn_space_assign_cust.
1398: -- 22-FEB-05 MMisra o Bug # 4198937. Merged IF conditions where area and
1399: -- percentage differnce was being checked.
1400: -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,pn_locations
1401: -- pn_space_assign_cust with _ALL table.
1396: -- 14-DEC-04 STripath o Fixed for bug# 4092157. If l_total_pct <> 100, update
1397: -- either pn_space_assign_emp or pn_space_assign_cust.
1398: -- 22-FEB-05 MMisra o Bug # 4198937. Merged IF conditions where area and
1399: -- percentage differnce was being checked.
1400: -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,pn_locations
1401: -- pn_space_assign_cust with _ALL table.
1402: -------------------------------------------------------------------------------
1403: PROCEDURE area_pct_and_area(x_usable_area NUMBER,
1404: x_location_id NUMBER,
1412: l_diff_pct NUMBER;
1413: l_diff_area NUMBER;
1414: l_emp_updated NUMBER := 0;
1415: l_cust_updated NUMBER := 0;
1416: l_alloc_area_pct pn_space_assign_emp_all.allocated_area_pct%TYPE;
1417: l_alloc_area pn_space_assign_emp_all.allocated_area%TYPE;
1418:
1419: BEGIN
1420:
1413: l_diff_area NUMBER;
1414: l_emp_updated NUMBER := 0;
1415: l_cust_updated NUMBER := 0;
1416: l_alloc_area_pct pn_space_assign_emp_all.allocated_area_pct%TYPE;
1417: l_alloc_area pn_space_assign_emp_all.allocated_area%TYPE;
1418:
1419: BEGIN
1420:
1421: l_utilized := PN_SPACE_ASSIGN_CUST_PKG.assignment_count(x_location_id,x_start_date,x_end_date);
1432:
1433: IF l_utilized <> 0 THEN
1434:
1435:
1436: UPDATE pn_space_assign_emp_all emp
1437: SET emp.allocated_area_pct = l_alloc_area_pct,
1438: emp.allocated_area = l_alloc_area
1439: WHERE emp.location_id = x_location_id
1440: AND (emp.emp_assign_start_date <= x_end_date AND
1469: l_diff_area := x_usable_area - l_total_area;
1470:
1471:
1472: IF NVL(l_emp_updated, 0) > 0 THEN
1473: UPDATE pn_space_assign_emp_all emp
1474: SET emp.allocated_area_pct = (emp.allocated_area_pct + l_diff_pct),
1475: emp.allocated_area = (emp.allocated_area + l_diff_area)
1476: WHERE emp.location_id = x_location_id
1477: AND (emp.emp_assign_start_date <= x_end_date AND
1520: -- HISTORY :
1521: -- 16-ARP-02 MMisra o Created
1522: -- 20-OCT-03 DThota o bug 3234403 - Copied this procedure from PNTSPACE.pld
1523: -- 07-MAR-05 ftanudja o Used subquery referencing for performance. #4199297.
1524: -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,
1525: -- pn_space_assign_cust, pn_locations with _ALL table.
1526: +============================================================================*/
1527:
1528: FUNCTION assignment_count(x_location_id IN NUMBER,
1540: l_utilized_cust := 0;
1541:
1542: SELECT COUNT(*)
1543: INTO l_utilized_emp
1544: FROM pn_space_assign_emp_all emp
1545: WHERE emp.location_id = x_location_id
1546: AND (emp.emp_assign_start_date <= NVL(x_end_date,to_date('12/31/4712','mm/dd/yyyy')) AND
1547: NVL(emp.emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
1548: AND EXISTS (SELECT '1'
1668: -- assignments, update the assignment instead of calling
1669: -- Update_Row in Correct mode, and other changes.
1670: -- 30-SEP-04 STripathi o Modified for Update and Delete for Emp_Tab.
1671: -- Update i+1 row BUT DELETE i th row.
1672: -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,
1673: -- pn_space_assign_cust with _ALL table.
1674: -------------------------------------------------------------------------------
1675:
1676: PROCEDURE Defrag_Contig_Assign (
1763: ,ATTRIBUTE14
1764: ,ATTRIBUTE15
1765: ,ORG_ID
1766: ,SOURCE
1767: FROM pn_space_assign_emp_all
1768: WHERE location_id = p_location_id
1769: ORDER BY person_id,emp_assign_start_date,emp_assign_end_date
1770: ;
1771:
1769: ORDER BY person_id,emp_assign_start_date,emp_assign_end_date
1770: ;
1771:
1772: --------------------------------------------------------------------------
1773: -- Define a record of PN_SPACE_CUST_ASSIGN_ALL and PN_SPACE_ASSIGN_EMP_ALL
1774: --------------------------------------------------------------------------
1775:
1776: TYPE cust_rec_type IS RECORD(
1777: CUST_SPACE_ASSIGN_ID pn_space_assign_cust_all.CUST_SPACE_ASSIGN_ID%TYPE
1816: ,TENANCY_ID pn_space_assign_cust_all.TENANCY_ID%TYPE
1817: );
1818:
1819: TYPE emp_rec_type IS RECORD(
1820: EMP_SPACE_ASSIGN_ID pn_space_assign_emp_all.EMP_SPACE_ASSIGN_ID%TYPE
1821: ,LOCATION_ID pn_space_assign_emp_all.LOCATION_ID%TYPE
1822: ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1817: );
1818:
1819: TYPE emp_rec_type IS RECORD(
1820: EMP_SPACE_ASSIGN_ID pn_space_assign_emp_all.EMP_SPACE_ASSIGN_ID%TYPE
1821: ,LOCATION_ID pn_space_assign_emp_all.LOCATION_ID%TYPE
1822: ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1818:
1819: TYPE emp_rec_type IS RECORD(
1820: EMP_SPACE_ASSIGN_ID pn_space_assign_emp_all.EMP_SPACE_ASSIGN_ID%TYPE
1821: ,LOCATION_ID pn_space_assign_emp_all.LOCATION_ID%TYPE
1822: ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1819: TYPE emp_rec_type IS RECORD(
1820: EMP_SPACE_ASSIGN_ID pn_space_assign_emp_all.EMP_SPACE_ASSIGN_ID%TYPE
1821: ,LOCATION_ID pn_space_assign_emp_all.LOCATION_ID%TYPE
1822: ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1820: EMP_SPACE_ASSIGN_ID pn_space_assign_emp_all.EMP_SPACE_ASSIGN_ID%TYPE
1821: ,LOCATION_ID pn_space_assign_emp_all.LOCATION_ID%TYPE
1822: ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1821: ,LOCATION_ID pn_space_assign_emp_all.LOCATION_ID%TYPE
1822: ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1822: ,PERSON_ID pn_space_assign_emp_all.PERSON_ID%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1823: ,PROJECT_ID pn_space_assign_emp_all.PROJECT_ID%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1824: ,TASK_ID pn_space_assign_emp_all.TASK_ID%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1825: ,EMP_ASSIGN_START_DATE pn_space_assign_emp_all.EMP_ASSIGN_START_DATE%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1826: ,EMP_ASSIGN_END_DATE pn_space_assign_emp_all.EMP_ASSIGN_END_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1827: ,COST_CENTER_CODE pn_space_assign_emp_all.COST_CENTER_CODE%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1828: ,ALLOCATED_AREA_PCT pn_space_assign_emp_all.ALLOCATED_AREA_PCT%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1829: ,ALLOCATED_AREA pn_space_assign_emp_all.ALLOCATED_AREA%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1830: ,UTILIZED_AREA pn_space_assign_emp_all.UTILIZED_AREA%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1831: ,EMP_SPACE_COMMENTS pn_space_assign_emp_all.EMP_SPACE_COMMENTS%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1832: ,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1833: ,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1834: ,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1835: ,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1836: ,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1837: ,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1838: ,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1839: ,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1840: ,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1841: ,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1842: ,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1843: ,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1844: ,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852: ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1845: ,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852: ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1853: ,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
1846: ,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852: ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1853: ,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
1854: ,SOURCE pn_space_assign_emp_all.SOURCE%TYPE
1847: ,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852: ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1853: ,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
1854: ,SOURCE pn_space_assign_emp_all.SOURCE%TYPE
1855: );
1848: ,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852: ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1853: ,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
1854: ,SOURCE pn_space_assign_emp_all.SOURCE%TYPE
1855: );
1856:
1849: ,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852: ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1853: ,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
1854: ,SOURCE pn_space_assign_emp_all.SOURCE%TYPE
1855: );
1856:
1857: ----------------------------------------------------------
1850: ,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
1851: ,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
1852: ,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
1853: ,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
1854: ,SOURCE pn_space_assign_emp_all.SOURCE%TYPE
1855: );
1856:
1857: ----------------------------------------------------------
1858: -- Define a PL/SQL table for employee and customer records
2032: -- If stepping thru emp PL/SQL table records finds contigous records with
2033: -- consecutive dates update the (i+1) record with the start date of the ith
2034: -- record and .......
2035: ---------------------------------------------------------------------------
2036: UPDATE pn_space_assign_emp_all
2037: SET emp_assign_start_date = emp_tab(i).emp_assign_start_date
2038: ,last_update_date = SYSDATE
2039: ,last_updated_by = NVL(FND_GLOBAL.USER_ID,'-1')
2040: ,last_update_login = NVL(FND_GLOBAL.LOGIN_ID,'-1')
2044: -- ....... update the (i+1) record with the start date of the ith record
2045: -- in the PL/SQL table as well and delete the ith record from the DB
2046: ---------------------------------------------------------------------------
2047: emp_tab(i+1).emp_assign_start_date := emp_tab(i).emp_assign_start_date;
2048: pn_space_assign_emp_pkg.delete_row(emp_tab(i).emp_space_assign_id);
2049:
2050: END IF;
2051: END IF;
2052: pnp_debug_pkg.debug('Defrag_Contig_Assign Emp_Tab... l_rec_num>1 End_LOOP i= '||i);
2102: -- ARGUMENTS : IN : x_person_id, x_cost_center_code, x_emp_assign_start_date
2103: -- x_emp_space_assign_id, x_loc_id_tbl
2104: -- REFERENCE : PN_COMMON.debug()
2105: -- HISTORY :
2106: -- 06-FEB-01 dthota o bug # 1609377 - Added code to update PN_SPACE_ASSIGN_EMP.
2107: -- emp_assign_end_date of all employee assignments whose
2108: -- start date is less than the start date of the assignmemnt
2109: -- record being entered with (emp_assign_start_date -1) and
2110: -- all employee assignments whose start date is equal to the
2112: -- (emp_assign_start_date)
2113: -- 09-MAR-04 ftanudj o added parameter x_emp_space_assign_id.
2114: -- 06-APR-05 ftanudja o Moved from PNTSPACE library. #4270051.
2115: -- o Added parameter x_loc_id_tbl, x_cost_center_code.
2116: -- 21-JUN-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp
2117: -- with _ALL table.
2118: -- 30-JUN-05 MMisra o Removed UPDATE for cost center assignments.
2119: -- o Removed input param. x_cost_center_code.
2120: -------------------------------------------------------------------------------
2118: -- 30-JUN-05 MMisra o Removed UPDATE for cost center assignments.
2119: -- o Removed input param. x_cost_center_code.
2120: -------------------------------------------------------------------------------
2121: PROCEDURE delete_other_assignments_emp(
2122: x_person_id IN pn_space_assign_emp.person_id%TYPE,
2123: x_emp_assign_start_date IN pn_space_assign_emp.emp_assign_start_date%TYPE,
2124: x_emp_space_assign_id IN pn_space_assign_emp.emp_space_assign_id%TYPE,
2125: x_loc_id_tbl OUT NOCOPY LOC_ID_TBL
2126: ) IS
2119: -- o Removed input param. x_cost_center_code.
2120: -------------------------------------------------------------------------------
2121: PROCEDURE delete_other_assignments_emp(
2122: x_person_id IN pn_space_assign_emp.person_id%TYPE,
2123: x_emp_assign_start_date IN pn_space_assign_emp.emp_assign_start_date%TYPE,
2124: x_emp_space_assign_id IN pn_space_assign_emp.emp_space_assign_id%TYPE,
2125: x_loc_id_tbl OUT NOCOPY LOC_ID_TBL
2126: ) IS
2127: -- one set of tables for cost center, the other for person
2120: -------------------------------------------------------------------------------
2121: PROCEDURE delete_other_assignments_emp(
2122: x_person_id IN pn_space_assign_emp.person_id%TYPE,
2123: x_emp_assign_start_date IN pn_space_assign_emp.emp_assign_start_date%TYPE,
2124: x_emp_space_assign_id IN pn_space_assign_emp.emp_space_assign_id%TYPE,
2125: x_loc_id_tbl OUT NOCOPY LOC_ID_TBL
2126: ) IS
2127: -- one set of tables for cost center, the other for person
2128:
2138: pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_emp (+)');
2139:
2140: IF x_emp_space_assign_id IS NULL THEN
2141:
2142: UPDATE pn_space_assign_emp_all
2143: SET emp_assign_end_date = (TRUNC(x_emp_assign_start_date) - 1)
2144: WHERE person_id = x_person_id
2145: AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
2146: AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_emp_assign_start_date)
2145: AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
2146: AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_emp_assign_start_date)
2147: RETURNING location_id BULK COLLECT INTO l_loc_tbl_past_ps;
2148:
2149: UPDATE pn_space_assign_emp_all
2150: SET emp_assign_end_date = TRUNC(x_emp_assign_start_date)
2151: WHERE person_id = x_person_id
2152: AND TRUNC(emp_assign_start_date) = TRUNC(x_emp_assign_start_date)
2153: RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc_ps;
2153: RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc_ps;
2154:
2155: ELSE
2156:
2157: UPDATE pn_space_assign_emp_all
2158: SET emp_assign_end_date = (TRUNC(x_emp_assign_start_date) - 1)
2159: WHERE person_id = x_person_id
2160: AND emp_space_assign_id <> x_emp_space_assign_id
2161: AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
2161: AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
2162: AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_emp_assign_start_date)
2163: RETURNING location_id BULK COLLECT INTO l_loc_tbl_past_ps;
2164:
2165: UPDATE pn_space_assign_emp_all
2166: SET emp_assign_end_date = TRUNC(x_emp_assign_start_date)
2167: WHERE person_id = x_person_id
2168: AND emp_space_assign_id <> x_emp_space_assign_id
2169: AND TRUNC(emp_assign_start_date) = TRUNC(x_emp_assign_start_date)