[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_CUST_SPACE_ASSIGN_ID IN OUT NOCOPY NUMBER,
X_LOCATION_ID IN NUMBER,
X_CUST_ACCOUNT_ID IN NUMBER,
X_SITE_USE_ID IN NUMBER,
X_EXPENSE_ACCOUNT_ID IN NUMBER,
X_PROJECT_ID IN NUMBER,
X_TASK_ID IN NUMBER,
X_CUST_ASSIGN_START_DATE IN DATE,
X_CUST_ASSIGN_END_DATE IN DATE,
X_ALLOCATED_AREA_PCT IN NUMBER,
X_ALLOCATED_AREA IN NUMBER,
X_UTILIZED_AREA IN NUMBER,
X_CUST_SPACE_COMMENTS IN VARCHAR2,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_ORG_ID IN NUMBER,
X_LEASE_ID IN NUMBER,
X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
X_RECOVERY_TYPE_CODE IN VARCHAR2,
X_FIN_OBLIG_END_DATE IN DATE,
X_TENANCY_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2
)
IS
CURSOR c IS
SELECT ROWID
FROM pn_space_assign_cust_all
WHERE cust_space_assign_id = x_cust_space_assign_id;
SELECT org_id
FROM pn_locations_all
WHERE location_id = x_location_id;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.INSERT_ROW (+) SpcAsgnId: '
||x_cust_space_assign_id||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
||', CustId: '||x_cust_account_id);
SELECT pn_space_assign_cust_s.NEXTVAL
INTO x_cust_space_assign_id
FROM DUAL;
INSERT INTO pn_space_assign_cust_all
( CUST_SPACE_ASSIGN_ID,
LOCATION_ID,
CUST_ACCOUNT_ID,
SITE_USE_ID,
EXPENSE_ACCOUNT_ID,
PROJECT_ID,
TASK_ID,
CUST_ASSIGN_START_DATE,
CUST_ASSIGN_END_DATE,
ALLOCATED_AREA_PCT,
ALLOCATED_AREA,
UTILIZED_AREA,
CUST_SPACE_COMMENTS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LEASE_ID,
RECOVERY_SPACE_STD_CODE,
RECOVERY_TYPE_CODE,
FIN_OBLIG_END_DATE,
TENANCY_ID,
ORG_ID
)
VALUES
(
X_CUST_SPACE_ASSIGN_ID,
X_LOCATION_ID,
X_CUST_ACCOUNT_ID,
X_SITE_USE_ID,
X_EXPENSE_ACCOUNT_ID,
X_PROJECT_ID,
X_TASK_ID,
X_CUST_ASSIGN_START_DATE,
X_CUST_ASSIGN_END_DATE,
X_ALLOCATED_AREA_PCT,
X_ALLOCATED_AREA,
X_UTILIZED_AREA,
X_CUST_SPACE_COMMENTS,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_LEASE_ID,
X_RECOVERY_SPACE_STD_CODE,
X_RECOVERY_TYPE_CODE,
X_FIN_OBLIG_END_DATE,
X_TENANCY_ID,
l_org_id
);
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.INSERT_ROW (-) SpcAsgnId: '
||x_cust_space_assign_id||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
||', CustId: '||x_cust_account_id);
END Insert_Row;
SELECT *
FROM pn_space_assign_cust_all
WHERE cust_space_assign_id = x_cust_space_assign_id
FOR UPDATE OF cust_space_assign_id NOWAIT;
PROCEDURE Update_Row (
X_CUST_SPACE_ASSIGN_ID IN NUMBER,
X_LOCATION_ID IN NUMBER,
X_CUST_ACCOUNT_ID IN NUMBER,
X_SITE_USE_ID IN NUMBER,
X_EXPENSE_ACCOUNT_ID IN NUMBER,
X_PROJECT_ID IN NUMBER,
X_TASK_ID IN NUMBER,
X_CUST_ASSIGN_START_DATE IN DATE,
X_CUST_ASSIGN_END_DATE IN DATE,
X_ALLOCATED_AREA_PCT IN NUMBER,
X_ALLOCATED_AREA IN NUMBER,
X_UTILIZED_AREA IN NUMBER,
X_CUST_SPACE_COMMENTS IN VARCHAR2,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_UPDATE_CORRECT_OPTION IN VARCHAR2,
X_CHANGED_START_DATE OUT NOCOPY DATE,
X_LEASE_ID IN NUMBER,
X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
X_RECOVERY_TYPE_CODE IN VARCHAR2,
X_FIN_OBLIG_END_DATE IN DATE,
X_TENANCY_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2
)
IS
l_cust_space_assign_id NUMBER;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW (+) SpcAsgnId: '
||x_cust_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
||', CustId: '||x_cust_account_id);
IF X_UPDATE_CORRECT_OPTION = 'UPDATE' THEN
SELECT pn_space_assign_cust_s.NEXTVAL
INTO l_cust_space_assign_id
FROM DUAL;
INSERT INTO pn_space_assign_cust_all
(CUST_SPACE_ASSIGN_ID,
LOCATION_ID,
CUST_ACCOUNT_ID,
SITE_USE_ID,
EXPENSE_ACCOUNT_ID,
PROJECT_ID,
TASK_ID,
CUST_ASSIGN_START_DATE,
CUST_ASSIGN_END_DATE,
ALLOCATED_AREA_PCT,
ALLOCATED_AREA,
UTILIZED_AREA,
CUST_SPACE_COMMENTS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LEASE_ID,
RECOVERY_SPACE_STD_CODE,
RECOVERY_TYPE_CODE,
FIN_OBLIG_END_DATE,
TENANCY_ID,
ORG_ID)
VALUES
(l_cust_space_assign_id,
tlcustinfo.LOCATION_ID,
tlcustinfo.CUST_ACCOUNT_ID,
tlcustinfo.SITE_USE_ID,
tlcustinfo.EXPENSE_ACCOUNT_ID,
tlcustinfo.PROJECT_ID,
tlcustinfo.TASK_ID,
tlcustinfo.CUST_ASSIGN_START_DATE,
X_CUST_ASSIGN_START_DATE - 1,
tlcustinfo.ALLOCATED_AREA_PCT,
tlcustinfo.ALLOCATED_AREA,
tlcustinfo.UTILIZED_AREA,
tlcustinfo.CUST_SPACE_COMMENTS,
tlcustinfo.LAST_UPDATE_DATE,
tlcustinfo.LAST_UPDATED_BY,
tlcustinfo.CREATION_DATE,
tlcustinfo.CREATED_BY,
tlcustinfo.LAST_UPDATE_LOGIN,
tlcustinfo.ATTRIBUTE_CATEGORY,
tlcustinfo.ATTRIBUTE1,
tlcustinfo.ATTRIBUTE2,
tlcustinfo.ATTRIBUTE3,
tlcustinfo.ATTRIBUTE4,
tlcustinfo.ATTRIBUTE5,
tlcustinfo.ATTRIBUTE6,
tlcustinfo.ATTRIBUTE7,
tlcustinfo.ATTRIBUTE8,
tlcustinfo.ATTRIBUTE9,
tlcustinfo.ATTRIBUTE10,
tlcustinfo.ATTRIBUTE11,
tlcustinfo.ATTRIBUTE12,
tlcustinfo.ATTRIBUTE13,
tlcustinfo.ATTRIBUTE14,
tlcustinfo.ATTRIBUTE15,
tlcustinfo.LEASE_ID,
tlcustinfo.RECOVERY_SPACE_STD_CODE,
tlcustinfo.RECOVERY_TYPE_CODE,
l_fin_oblig_end_date,
tlcustinfo.TENANCY_ID,
tlcustinfo.org_id
);
UPDATE PN_SPACE_ASSIGN_CUST_ALL SET
LOCATION_ID = X_LOCATION_ID,
CUST_ACCOUNT_ID = X_CUST_ACCOUNT_ID,
SITE_USE_ID = X_SITE_USE_ID,
EXPENSE_ACCOUNT_ID = X_EXPENSE_ACCOUNT_ID,
PROJECT_ID = X_PROJECT_ID,
TASK_ID = X_TASK_ID,
CUST_ASSIGN_START_DATE = X_CUST_ASSIGN_START_DATE,
CUST_ASSIGN_END_DATE = X_CUST_ASSIGN_END_DATE,
ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT,
ALLOCATED_AREA = X_ALLOCATED_AREA,
UTILIZED_AREA = X_UTILIZED_AREA,
CUST_SPACE_COMMENTS = X_CUST_SPACE_COMMENTS,
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
ATTRIBUTE14 = X_ATTRIBUTE14,
ATTRIBUTE15 = X_ATTRIBUTE15,
CUST_SPACE_ASSIGN_ID = X_CUST_SPACE_ASSIGN_ID,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
LEASE_ID = X_LEASE_ID,
RECOVERY_SPACE_STD_CODE = X_RECOVERY_SPACE_STD_CODE,
RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE,
FIN_OBLIG_END_DATE = X_FIN_OBLIG_END_DATE,
TENANCY_ID = X_TENANCY_ID
WHERE CUST_SPACE_ASSIGN_ID = X_CUST_SPACE_ASSIGN_ID;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW (-) SpcAsgnId: '
||x_cust_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_cust_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_cust_assign_end_date, 'MM/DD/YYYY')
||', CustId: '||x_cust_account_id);
END Update_Row;
PROCEDURE Delete_Row (
X_CUST_SPACE_ASSIGN_ID IN NUMBER
)
IS
BEGIN
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.DELETE_ROW (+) SpcAsgnId: '
||x_cust_space_assign_id);
DELETE FROM pn_space_assign_cust_all
WHERE cust_space_assign_id = x_cust_space_assign_id;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.DELETE_ROW (-) SpcAsgnId: '
||x_cust_space_assign_id);
END Delete_Row;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_space_assign_cust_all
WHERE cust_account_id = p_cust_acnt_id
AND location_id = p_loc_id
AND cust_assign_start_date <= p_assgn_end_dt
AND NVL(cust_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
>= p_assgn_str_dt);
SELECT cust_space_assign_id, cust_account_id
FROM pn_space_assign_cust_all
WHERE location_id = p_loc_id
AND cust_assign_start_date <= p_assgn_end_dt
AND NVL(cust_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
>= p_assgn_str_dt;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_rec_arcl_dtlln_all
WHERE cust_space_assign_id = p_cust_space_assign_id);
SELECT cust_assign_start_date start_date
,NVL(cust_assign_end_date , to_date('12/31/4712','MM/DD/YYYY')) end_date
,allocated_area
,location_id
,'CUST: '||cust_space_assign_id assign_type_id
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND cust_assign_start_date <= p_end_date
AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= p_start_date
UNION ALL
SELECT cust_assign_start_date start_date
,NVL(cust_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
,allocated_area
,location_id
,'CUST: '||cust_space_assign_id assign_type_id
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND cust_assign_start_date > p_end_date
UNION ALL
SELECT emp_assign_start_date start_date
,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
,allocated_area
,location_id
,'EMP: '||emp_space_assign_id assign_type_id
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND emp_assign_start_date <= p_end_date
AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= p_start_date
UNION ALL
SELECT emp_assign_start_date start_date
,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) end_date
,allocated_area
,location_id
,'EMP: '||emp_space_assign_id assign_type_id
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND emp_assign_start_date > p_end_date
UNION ALL
SELECT min(active_start_date) start_date
,max(NVL(active_end_date, to_date('12/31/4712','MM/DD/YYYY'))) end_date
,assignable_area allocated_area
,location_id
,'LOCN.' assign_type_id
FROM pn_locations_all
WHERE location_id = p_location_id
AND active_start_date <= p_end_date
AND active_end_date >= p_start_date
GROUP BY assignable_area,location_id
ORDER BY start_date;
SELECT cust_assign_start_date
,NVL(cust_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) cust_assign_end_date
,allocated_area
,allocated_area_pct
,location_id
,ROWID
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND cust_assign_start_date <= (p_end_date + 1)
AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= (p_start_date - 1)
;
SELECT emp_assign_start_date
,NVL(emp_assign_end_date, to_date('12/31/4712','MM/DD/YYYY')) emp_assign_end_date
,allocated_area
,allocated_area_pct
,location_id
,ROWID
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND emp_assign_start_date <= (p_end_date + 1)
AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= (p_start_date - 1);
SELECT *
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND cust_assign_start_date < p_as_of_date
AND NVL(cust_assign_end_date,to_date('12/31/4712','MM/DD/YYYY')) >= p_as_of_date
ORDER BY cust_assign_start_date,cust_assign_end_date;
SELECT *
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND emp_assign_start_date < p_as_of_date
AND NVL(emp_assign_end_date,to_date('12/31/4712','MM/DD/YYYY')) >= p_as_of_date
ORDER BY emp_assign_start_date, emp_assign_end_date;
SELECT assignable_area
FROM pn_locations_all
WHERE location_id = p_location_id
AND p_start_date between active_start_date
AND NVL(active_end_date,to_date('12/31/4712','MM/DD/YYYY'))
;
SELECT org_id
FROM pn_locations_all
WHERE location_id = p_location_id;
SELECT 1 FROM DUAL
WHERE EXISTS
(SELECT '1'
FROM pn_locations_all loc
WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
AND loc.location_id = b_location_id
);
pnp_debug_pkg.debug('Update Cust Row Assign_Id: '|| cust_split_rec.cust_space_assign_id
||', Loc_Id: '|| cust_split_rec.location_id
||', Cust_Id: '|| cust_split_rec.cust_account_id);
PN_SPACE_ASSIGN_CUST_PKG.UPDATE_ROW(
X_CUST_SPACE_ASSIGN_ID => cust_split_rec.CUST_SPACE_ASSIGN_ID
,X_LOCATION_ID => cust_split_rec.LOCATION_ID
,X_CUST_ACCOUNT_ID => cust_split_rec.CUST_ACCOUNT_ID
,X_SITE_USE_ID => NULL
,X_EXPENSE_ACCOUNT_ID => cust_split_rec.EXPENSE_ACCOUNT_ID
,X_PROJECT_ID => cust_split_rec.PROJECT_ID
,X_TASK_ID => cust_split_rec.TASK_ID
,X_CUST_ASSIGN_START_DATE => l_date_table(i)
,X_CUST_ASSIGN_END_DATE => cust_split_rec.CUST_ASSIGN_END_DATE
,X_ALLOCATED_AREA_PCT => cust_split_rec.ALLOCATED_AREA_PCT
,X_ALLOCATED_AREA => cust_split_rec.ALLOCATED_AREA
,X_UTILIZED_AREA => cust_split_rec.UTILIZED_AREA
,X_CUST_SPACE_COMMENTS => cust_split_rec.CUST_SPACE_COMMENTS
,X_ATTRIBUTE_CATEGORY => cust_split_rec.ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1 => cust_split_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => cust_split_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => cust_split_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => cust_split_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => cust_split_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => cust_split_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => cust_split_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => cust_split_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => cust_split_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => cust_split_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => cust_split_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => cust_split_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => cust_split_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => cust_split_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => cust_split_rec.ATTRIBUTE15
,X_LEASE_ID => cust_split_rec.LEASE_ID
,X_TENANCY_ID => cust_split_rec.TENANCY_ID
,X_RECOVERY_SPACE_STD_CODE => cust_split_rec.RECOVERY_SPACE_STD_CODE
,X_RECOVERY_TYPE_CODE => cust_split_rec.RECOVERY_TYPE_CODE
,X_FIN_OBLIG_END_DATE => cust_split_rec.FIN_OBLIG_END_DATE
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => 1
,X_LAST_UPDATE_LOGIN => 1
,X_UPDATE_CORRECT_OPTION => 'UPDATE'
,X_CHANGED_START_DATE => l_date
,X_RETURN_STATUS => l_return_status
);
pnp_debug_pkg.debug('Update Emp Row Assign_Id: '|| emp_split_rec.emp_space_assign_id
||', Loc_Id: '|| emp_split_rec.location_id
||', Emp_Id: '|| emp_split_rec.person_id);
PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW(
X_EMP_SPACE_ASSIGN_ID => emp_split_rec.EMP_SPACE_ASSIGN_ID
,X_ATTRIBUTE1 => emp_split_rec.ATTRIBUTE1
,X_ATTRIBUTE2 => emp_split_rec.ATTRIBUTE2
,X_ATTRIBUTE3 => emp_split_rec.ATTRIBUTE3
,X_ATTRIBUTE4 => emp_split_rec.ATTRIBUTE4
,X_ATTRIBUTE5 => emp_split_rec.ATTRIBUTE5
,X_ATTRIBUTE6 => emp_split_rec.ATTRIBUTE6
,X_ATTRIBUTE7 => emp_split_rec.ATTRIBUTE7
,X_ATTRIBUTE8 => emp_split_rec.ATTRIBUTE8
,X_ATTRIBUTE9 => emp_split_rec.ATTRIBUTE9
,X_ATTRIBUTE10 => emp_split_rec.ATTRIBUTE10
,X_ATTRIBUTE11 => emp_split_rec.ATTRIBUTE11
,X_ATTRIBUTE12 => emp_split_rec.ATTRIBUTE12
,X_ATTRIBUTE13 => emp_split_rec.ATTRIBUTE13
,X_ATTRIBUTE14 => emp_split_rec.ATTRIBUTE14
,X_ATTRIBUTE15 => emp_split_rec.ATTRIBUTE15
,X_LOCATION_ID => emp_split_rec.LOCATION_ID
,X_PERSON_ID => emp_split_rec.PERSON_ID
,X_PROJECT_ID => emp_split_rec.PROJECT_ID
,X_TASK_ID => emp_split_rec.TASK_ID
,X_EMP_ASSIGN_START_DATE => l_date_table(i)
,X_EMP_ASSIGN_END_DATE => emp_split_rec.EMP_ASSIGN_END_DATE
,X_COST_CENTER_CODE => emp_split_rec.COST_CENTER_CODE
,X_ALLOCATED_AREA_PCT => emp_split_rec.ALLOCATED_AREA_PCT
,X_ALLOCATED_AREA => emp_split_rec.ALLOCATED_AREA
,X_UTILIZED_AREA => emp_split_rec.UTILIZED_AREA
,X_EMP_SPACE_COMMENTS => emp_split_rec.EMP_SPACE_COMMENTS
,X_ATTRIBUTE_CATEGORY => emp_split_rec.ATTRIBUTE_CATEGORY
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => 1
,X_LAST_UPDATE_LOGIN => 1
,X_UPDATE_CORRECT_OPTION => 'UPDATE'
,X_CHANGED_START_DATE => l_date
);
UPDATE pn_space_assign_cust_all
SET allocated_area = l_allocated_area
WHERE ROWID = cust_rec.ROWID;
UPDATE pn_space_assign_emp_all
SET allocated_area = l_allocated_area
WHERE ROWID = emp_rec.ROWID;
l_emp_updated NUMBER := 0;
l_cust_updated NUMBER := 0;
UPDATE pn_space_assign_emp_all emp
SET emp.allocated_area_pct = l_alloc_area_pct,
emp.allocated_area = l_alloc_area
WHERE emp.location_id = x_location_id
AND (emp.emp_assign_start_date <= x_end_date AND
NVL(emp.emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
AND EXISTS (SELECT '1'
FROM pn_locations_all loc
WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
AND loc.location_id = emp.location_id);
l_emp_updated := SQL%ROWCOUNT;
UPDATE pn_space_assign_cust_all cust
SET cust.allocated_area_pct = l_alloc_area_pct,
cust.allocated_area = l_alloc_area
WHERE cust.location_id = x_location_id
AND (cust.cust_assign_start_date <= x_end_date AND
NVL(cust.cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
AND EXISTS (SELECT '1'
FROM pn_locations_all loc
WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
AND loc.location_id = cust.location_id);
l_cust_updated := SQL%ROWCOUNT;
IF NVL(l_emp_updated, 0) > 0 THEN
UPDATE pn_space_assign_emp_all emp
SET emp.allocated_area_pct = (emp.allocated_area_pct + l_diff_pct),
emp.allocated_area = (emp.allocated_area + l_diff_area)
WHERE emp.location_id = x_location_id
AND (emp.emp_assign_start_date <= x_end_date AND
NVL(emp.emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
AND EXISTS (SELECT '1'
FROM pn_locations_all loc
WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
AND loc.location_id = emp.location_id)
AND ROWNUM < 2;
ELSIF NVL(l_cust_updated, 0) > 0 THEN
UPDATE pn_space_assign_cust_all cust
SET cust.allocated_area_pct = (cust.allocated_area_pct + l_diff_pct),
cust.allocated_area = (cust.allocated_area + l_diff_area)
WHERE cust.location_id = x_location_id
AND (cust.cust_assign_start_date <= x_end_date AND
NVL(cust.cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
AND EXISTS (SELECT '1'
FROM pn_locations_all loc
WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
AND loc.location_id = cust.location_id)
AND ROWNUM < 2;
SELECT COUNT(*)
INTO l_utilized_emp
FROM pn_space_assign_emp_all emp
WHERE emp.location_id = x_location_id
AND (emp.emp_assign_start_date <= NVL(x_end_date,to_date('12/31/4712','mm/dd/yyyy')) AND
NVL(emp.emp_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
AND EXISTS (SELECT '1'
FROM pn_locations_all loc
WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
AND loc.location_id = emp.location_id);
SELECT COUNT(*)
INTO l_utilized_cust
FROM pn_space_assign_cust_all cust
WHERE cust.location_id = x_location_id
AND (cust.cust_assign_start_date <= NVL(x_end_date,to_date('12/31/4712','mm/dd/yyyy')) AND
NVL(cust.cust_assign_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
AND EXISTS (SELECT '1'
FROM pn_locations_all loc
WHERE loc.location_type_lookup_code in ('OFFICE','SECTION')
AND loc.location_id = cust.location_id);
SELECT COUNT(*)
INTO l_location_count
FROM pn_locations_all
WHERE location_id = x_location_id
AND (active_start_date <= NVL(x_end_date,to_date('12/31/4712','mm/dd/yyyy')) AND
NVL(active_end_date,to_date('12/31/4712','mm/dd/yyyy')) >= x_start_date)
;
SELECT CUST_SPACE_ASSIGN_ID
,LOCATION_ID
,CUST_ACCOUNT_ID
,SITE_USE_ID
,EXPENSE_ACCOUNT_ID
,PROJECT_ID
,TASK_ID
,CUST_ASSIGN_START_DATE
,CUST_ASSIGN_END_DATE
,ALLOCATED_AREA_PCT
,ALLOCATED_AREA
,UTILIZED_AREA
,CUST_SPACE_COMMENTS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORG_ID
,LEASE_ID
,RECOVERY_SPACE_STD_CODE
,RECOVERY_TYPE_CODE
,FIN_OBLIG_END_DATE
,TENANCY_ID
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
ORDER BY cust_account_id,tenancy_id,lease_id,cust_assign_start_date,cust_assign_end_date
;
SELECT EMP_SPACE_ASSIGN_ID
,LOCATION_ID
,PERSON_ID
,PROJECT_ID
,TASK_ID
,EMP_ASSIGN_START_DATE
,EMP_ASSIGN_END_DATE
,COST_CENTER_CODE
,ALLOCATED_AREA_PCT
,ALLOCATED_AREA
,UTILIZED_AREA
,EMP_SPACE_COMMENTS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORG_ID
,SOURCE
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
ORDER BY person_id,emp_assign_start_date,emp_assign_end_date
;
,LAST_UPDATE_DATE pn_space_assign_cust_all.LAST_UPDATE_DATE%TYPE
,LAST_UPDATED_BY pn_space_assign_cust_all.LAST_UPDATED_BY%TYPE
,CREATION_DATE pn_space_assign_cust_all.CREATION_DATE%TYPE
,CREATED_BY pn_space_assign_cust_all.CREATED_BY%TYPE
,LAST_UPDATE_LOGIN pn_space_assign_cust_all.LAST_UPDATE_LOGIN%TYPE
,ATTRIBUTE_CATEGORY pn_space_assign_cust_all.ATTRIBUTE_CATEGORY%TYPE
,ATTRIBUTE1 pn_space_assign_cust_all.ATTRIBUTE1%TYPE
,ATTRIBUTE2 pn_space_assign_cust_all.ATTRIBUTE2%TYPE
,ATTRIBUTE3 pn_space_assign_cust_all.ATTRIBUTE3%TYPE
,ATTRIBUTE4 pn_space_assign_cust_all.ATTRIBUTE4%TYPE
,ATTRIBUTE5 pn_space_assign_cust_all.ATTRIBUTE5%TYPE
,ATTRIBUTE6 pn_space_assign_cust_all.ATTRIBUTE6%TYPE
,ATTRIBUTE7 pn_space_assign_cust_all.ATTRIBUTE7%TYPE
,ATTRIBUTE8 pn_space_assign_cust_all.ATTRIBUTE8%TYPE
,ATTRIBUTE9 pn_space_assign_cust_all.ATTRIBUTE9%TYPE
,ATTRIBUTE10 pn_space_assign_cust_all.ATTRIBUTE10%TYPE
,ATTRIBUTE11 pn_space_assign_cust_all.ATTRIBUTE11%TYPE
,ATTRIBUTE12 pn_space_assign_cust_all.ATTRIBUTE12%TYPE
,ATTRIBUTE13 pn_space_assign_cust_all.ATTRIBUTE13%TYPE
,ATTRIBUTE14 pn_space_assign_cust_all.ATTRIBUTE14%TYPE
,ATTRIBUTE15 pn_space_assign_cust_all.ATTRIBUTE15%TYPE
,ORG_ID pn_space_assign_cust_all.ORG_ID%TYPE
,LEASE_ID pn_space_assign_cust_all.LEASE_ID%TYPE
,RECOVERY_SPACE_STD_CODE pn_space_assign_cust_all.RECOVERY_SPACE_STD_CODE%TYPE
,RECOVERY_TYPE_CODE pn_space_assign_cust_all.RECOVERY_TYPE_CODE%TYPE
,FIN_OBLIG_END_DATE pn_space_assign_cust_all.FIN_OBLIG_END_DATE%TYPE
,TENANCY_ID pn_space_assign_cust_all.TENANCY_ID%TYPE
);
,LAST_UPDATE_DATE pn_space_assign_emp_all.LAST_UPDATE_DATE%TYPE
,LAST_UPDATED_BY pn_space_assign_emp_all.LAST_UPDATED_BY%TYPE
,CREATION_DATE pn_space_assign_emp_all.CREATION_DATE%TYPE
,CREATED_BY pn_space_assign_emp_all.CREATED_BY%TYPE
,LAST_UPDATE_LOGIN pn_space_assign_emp_all.LAST_UPDATE_LOGIN%TYPE
,ATTRIBUTE_CATEGORY pn_space_assign_emp_all.ATTRIBUTE_CATEGORY%TYPE
,ATTRIBUTE1 pn_space_assign_emp_all.ATTRIBUTE1%TYPE
,ATTRIBUTE2 pn_space_assign_emp_all.ATTRIBUTE2%TYPE
,ATTRIBUTE3 pn_space_assign_emp_all.ATTRIBUTE3%TYPE
,ATTRIBUTE4 pn_space_assign_emp_all.ATTRIBUTE4%TYPE
,ATTRIBUTE5 pn_space_assign_emp_all.ATTRIBUTE5%TYPE
,ATTRIBUTE6 pn_space_assign_emp_all.ATTRIBUTE6%TYPE
,ATTRIBUTE7 pn_space_assign_emp_all.ATTRIBUTE7%TYPE
,ATTRIBUTE8 pn_space_assign_emp_all.ATTRIBUTE8%TYPE
,ATTRIBUTE9 pn_space_assign_emp_all.ATTRIBUTE9%TYPE
,ATTRIBUTE10 pn_space_assign_emp_all.ATTRIBUTE10%TYPE
,ATTRIBUTE11 pn_space_assign_emp_all.ATTRIBUTE11%TYPE
,ATTRIBUTE12 pn_space_assign_emp_all.ATTRIBUTE12%TYPE
,ATTRIBUTE13 pn_space_assign_emp_all.ATTRIBUTE13%TYPE
,ATTRIBUTE14 pn_space_assign_emp_all.ATTRIBUTE14%TYPE
,ATTRIBUTE15 pn_space_assign_emp_all.ATTRIBUTE15%TYPE
,ORG_ID pn_space_assign_emp_all.ORG_ID%TYPE
,SOURCE pn_space_assign_emp_all.SOURCE%TYPE
);
cust_tab.delete;
UPDATE pn_space_assign_cust_all
SET cust_assign_start_date = cust_tab(i).cust_assign_start_date
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_GLOBAL.USER_ID,'-1')
,last_update_login = NVL(FND_GLOBAL.LOGIN_ID,'-1')
WHERE cust_space_assign_id = cust_tab(i+1).cust_space_assign_id;
pn_space_assign_cust_pkg.delete_row(cust_tab(i).cust_space_assign_id);
emp_tab.delete;
UPDATE pn_space_assign_emp_all
SET emp_assign_start_date = emp_tab(i).emp_assign_start_date
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_GLOBAL.USER_ID,'-1')
,last_update_login = NVL(FND_GLOBAL.LOGIN_ID,'-1')
WHERE emp_space_assign_id = emp_tab(i+1).emp_space_assign_id;
pn_space_assign_emp_pkg.delete_row(emp_tab(i).emp_space_assign_id);
PROCEDURE delete_other_assignments_emp(
x_person_id IN pn_space_assign_emp.person_id%TYPE,
x_emp_assign_start_date IN pn_space_assign_emp.emp_assign_start_date%TYPE,
x_emp_space_assign_id IN pn_space_assign_emp.emp_space_assign_id%TYPE,
x_loc_id_tbl OUT NOCOPY LOC_ID_TBL
) IS
-- one set of tables for cost center, the other for person
l_loc_tbl_past_cc loc_id_tbl;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_emp (+)');
UPDATE pn_space_assign_emp_all
SET emp_assign_end_date = (TRUNC(x_emp_assign_start_date) - 1)
WHERE person_id = x_person_id
AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_emp_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_past_ps;
UPDATE pn_space_assign_emp_all
SET emp_assign_end_date = TRUNC(x_emp_assign_start_date)
WHERE person_id = x_person_id
AND TRUNC(emp_assign_start_date) = TRUNC(x_emp_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc_ps;
UPDATE pn_space_assign_emp_all
SET emp_assign_end_date = (TRUNC(x_emp_assign_start_date) - 1)
WHERE person_id = x_person_id
AND emp_space_assign_id <> x_emp_space_assign_id
AND TRUNC(emp_assign_start_date) < TRUNC(x_emp_assign_start_date)
AND NVL(emp_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_emp_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_past_ps;
UPDATE pn_space_assign_emp_all
SET emp_assign_end_date = TRUNC(x_emp_assign_start_date)
WHERE person_id = x_person_id
AND emp_space_assign_id <> x_emp_space_assign_id
AND TRUNC(emp_assign_start_date) = TRUNC(x_emp_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc_ps;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_emp (-)');
END delete_other_assignments_emp;
PROCEDURE delete_other_assignments_cust(
x_cust_account_id IN pn_space_assign_cust.cust_account_id%TYPE,
x_cust_assign_start_date IN pn_space_assign_cust.cust_assign_start_date%TYPE,
x_cust_space_assign_id IN pn_space_assign_cust.cust_space_assign_id%TYPE,
x_loc_id_tbl OUT NOCOPY LOC_ID_TBL
) IS
l_loc_tbl_past loc_id_tbl;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_cust (+)');
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = (TRUNC(x_cust_assign_start_date) - 1)
WHERE cust_account_id = x_cust_account_id
AND cust_assign_start_date < TRUNC(x_cust_assign_start_date)
AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_cust_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_past;
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = TRUNC(x_cust_assign_start_date)
WHERE cust_account_id = x_cust_account_id
AND cust_assign_start_date = TRUNC(x_cust_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc;
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = (TRUNC(x_cust_assign_start_date) - 1)
WHERE cust_account_id = x_cust_account_id
AND cust_space_assign_id <> x_cust_space_assign_id
AND cust_assign_start_date < TRUNC(x_cust_assign_start_date)
AND NVL(cust_assign_end_date,TO_DATE('12/31/4712','MM/DD/YYYY')) >= TRUNC(x_cust_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_past;
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = TRUNC(x_cust_assign_start_date)
WHERE cust_account_id = x_cust_account_id
AND cust_space_assign_id <> x_cust_space_assign_id
AND cust_assign_start_date = TRUNC(x_cust_assign_start_date)
RETURNING location_id BULK COLLECT INTO l_loc_tbl_conc;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_CUST_PKG.delete_other_assignments_cust (+)');
END delete_other_assignments_cust;