[Home] [Help]
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;
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;
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
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'
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')
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)