[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row
(
X_ROWID IN OUT NOCOPY VARCHAR2,
X_TENANCY_ID IN OUT NOCOPY NUMBER,
X_LOCATION_ID IN NUMBER,
X_LEASE_ID IN NUMBER,
X_LEASE_CHANGE_ID IN NUMBER,
X_TENANCY_USAGE_LOOKUP_CODE IN VARCHAR2,
X_PRIMARY_FLAG IN VARCHAR2,
X_ESTIMATED_OCCUPANCY_DATE IN DATE,
X_OCCUPANCY_DATE IN DATE,
X_EXPIRATION_DATE IN DATE,
X_ASSIGNABLE_FLAG IN VARCHAR2,
X_SUBLEASEABLE_FLAG IN VARCHAR2,
X_TENANTS_PROPORTIONATE_SHARE IN NUMBER,
X_ALLOCATED_AREA_PCT IN NUMBER,
X_ALLOCATED_AREA IN NUMBER,
X_STATUS 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_TENANCY_OVELAP_WRN OUT NOCOPY VARCHAR2,
X_RECOVERY_TYPE_CODE IN VARCHAR2,
X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
X_FIN_OBLIG_END_DATE IN DATE,
X_CUSTOMER_ID IN NUMBER,
X_CUSTOMER_SITE_USE_ID IN NUMBER,
X_LEASE_RENTABLE_AREA IN NUMBER,
X_LEASE_USABLE_AREA IN NUMBER,
X_LEASE_ASSIGNABLE_AREA IN NUMBER,
X_LEASE_LOAD_FACTOR IN NUMBER,
X_LOCATION_RENTABLE_AREA IN NUMBER,
X_LOCATION_USABLE_AREA IN NUMBER,
X_LOCATION_ASSIGNABLE_AREA IN NUMBER,
X_LOCATION_LOAD_FACTOR IN NUMBER
)
IS
CURSOR C IS
SELECT ROWID
FROM pn_tenancies_all
WHERE tenancy_id = x_tenancy_id;
SELECT org_id
FROM pn_leases_all
WHERE lease_id = x_lease_id;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_ROW (+)');
SELECT pn_tenancies_s.NEXTVAL
INTO x_tenancy_id
FROM DUAL;
INSERT INTO pn_tenancies_all
(
TENANCY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOCATION_ID,
LEASE_ID,
LEASE_CHANGE_ID,
TENANCY_USAGE_LOOKUP_CODE,
PRIMARY_FLAG,
ESTIMATED_OCCUPANCY_DATE,
OCCUPANCY_DATE,
EXPIRATION_DATE,
ASSIGNABLE_FLAG,
SUBLEASEABLE_FLAG,
TENANTS_PROPORTIONATE_SHARE,
ALLOCATED_AREA_PCT,
ALLOCATED_AREA,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID,
RECOVERY_TYPE_CODE,
RECOVERY_SPACE_STD_CODE,
FIN_OBLIG_END_DATE,
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
LEASE_RENTABLE_AREA,
LEASE_USABLE_AREA,
LEASE_ASSIGNABLE_AREA,
LEASE_LOAD_FACTOR,
LOCATION_RENTABLE_AREA,
LOCATION_USABLE_AREA,
LOCATION_ASSIGNABLE_AREA,
LOCATION_LOAD_FACTOR
)
VALUES
(
X_TENANCY_ID,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_LOCATION_ID,
X_LEASE_ID,
X_LEASE_CHANGE_ID,
X_TENANCY_USAGE_LOOKUP_CODE,
X_PRIMARY_FLAG,
X_ESTIMATED_OCCUPANCY_DATE,
X_OCCUPANCY_DATE,
X_EXPIRATION_DATE,
X_ASSIGNABLE_FLAG,
X_SUBLEASEABLE_FLAG,
X_TENANTS_PROPORTIONATE_SHARE,
X_ALLOCATED_AREA_PCT,
X_ALLOCATED_AREA,
X_STATUS,
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,
l_ORG_ID,
X_RECOVERY_TYPE_CODE,
X_RECOVERY_SPACE_STD_CODE,
X_FIN_OBLIG_END_DATE,
X_CUSTOMER_ID,
X_CUSTOMER_SITE_USE_ID,
X_LEASE_RENTABLE_AREA,
X_LEASE_USABLE_AREA,
X_LEASE_ASSIGNABLE_AREA,
X_LEASE_LOAD_FACTOR,
X_LOCATION_RENTABLE_AREA,
X_LOCATION_USABLE_AREA,
X_LOCATION_ASSIGNABLE_AREA,
X_LOCATION_LOAD_FACTOR
);
pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_ROW (-)');
END Insert_Row;
SELECT *
FROM pn_tenancies_all
WHERE tenancy_id = x_tenancy_id
FOR UPDATE OF tenancy_id NOWAIT;
PROCEDURE Update_Row
(
X_TENANCY_ID IN NUMBER,
X_LOCATION_ID IN NUMBER,
X_LEASE_ID IN NUMBER,
X_LEASE_CHANGE_ID IN NUMBER,
X_TENANCY_USAGE_LOOKUP_CODE IN VARCHAR2,
X_PRIMARY_FLAG IN VARCHAR2,
X_ESTIMATED_OCCUPANCY_DATE IN DATE,
X_OCCUPANCY_DATE IN DATE,
X_EXPIRATION_DATE IN DATE,
X_ASSIGNABLE_FLAG IN VARCHAR2,
X_SUBLEASEABLE_FLAG IN VARCHAR2,
X_TENANTS_PROPORTIONATE_SHARE IN NUMBER,
X_ALLOCATED_AREA_PCT IN NUMBER,
X_ALLOCATED_AREA IN NUMBER,
X_STATUS 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_TENANCY_OVELAP_WRN OUT NOCOPY VARCHAR2,
X_RECOVERY_TYPE_CODE IN VARCHAR2,
X_RECOVERY_SPACE_STD_CODE IN VARCHAR2,
X_FIN_OBLIG_END_DATE IN DATE,
X_CUSTOMER_ID IN NUMBER,
X_CUSTOMER_SITE_USE_ID IN NUMBER,
X_LEASE_RENTABLE_AREA IN NUMBER,
X_LEASE_USABLE_AREA IN NUMBER,
X_LEASE_ASSIGNABLE_AREA IN NUMBER,
X_LEASE_LOAD_FACTOR IN NUMBER,
X_LOCATION_RENTABLE_AREA IN NUMBER,
X_LOCATION_USABLE_AREA IN NUMBER,
X_LOCATION_ASSIGNABLE_AREA IN NUMBER,
X_LOCATION_LOAD_FACTOR IN NUMBER
)
IS
CURSOR c2 IS
SELECT *
FROM pn_tenancies_all
WHERE tenancy_id = x_tenancy_id;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_ROW (+)');
INSERT INTO PN_TENANCIES_HISTORY
(
TENANCY_HISTORY_ID,
TENANCY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOCATION_ID,
LEASE_ID,
LEASE_CHANGE_ID,
NEW_LEASE_CHANGE_ID,
TENANCY_USAGE_LOOKUP_CODE,
PRIMARY_FLAG,
ESTIMATED_OCCUPANCY_DATE,
OCCUPANCY_DATE,
EXPIRATION_DATE,
ASSIGNABLE_FLAG,
SUBLEASEABLE_FLAG,
TENANTS_PROPORTIONATE_SHARE,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID,
RECOVERY_TYPE_CODE,
RECOVERY_SPACE_STD_CODE,
FIN_OBLIG_END_DATE,
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
LEASE_RENTABLE_AREA,
LEASE_USABLE_AREA,
LEASE_ASSIGNABLE_AREA,
LEASE_LOAD_FACTOR,
LOCATION_RENTABLE_AREA,
LOCATION_USABLE_AREA,
LOCATION_ASSIGNABLE_AREA,
LOCATION_LOAD_FACTOR
)
VALUES
(
pn_tenancies_history_s.NEXTVAL,
recInfoForHist.TENANCY_ID,
recInfoForHist.LAST_UPDATE_DATE,
recInfoForHist.LAST_UPDATED_BY,
recInfoForHist.CREATION_DATE,
recInfoForHist.CREATED_BY,
recInfoForHist.LAST_UPDATE_LOGIN,
recInfoForHist.LOCATION_ID,
recInfoForHist.LEASE_ID,
recInfoForHist.LEASE_CHANGE_ID,
X_LEASE_CHANGE_ID,
recInfoForHist.TENANCY_USAGE_LOOKUP_CODE,
recInfoForHist.PRIMARY_FLAG,
recInfoForHist.ESTIMATED_OCCUPANCY_DATE,
recInfoForHist.OCCUPANCY_DATE,
recInfoForHist.EXPIRATION_DATE,
recInfoForHist.ASSIGNABLE_FLAG,
recInfoForHist.SUBLEASEABLE_FLAG,
recInfoForHist.TENANTS_PROPORTIONATE_SHARE,
recInfoForHist.STATUS,
recInfoForHist.ATTRIBUTE_CATEGORY,
recInfoForHist.ATTRIBUTE1,
recInfoForHist.ATTRIBUTE2,
recInfoForHist.ATTRIBUTE3,
recInfoForHist.ATTRIBUTE4,
recInfoForHist.ATTRIBUTE5,
recInfoForHist.ATTRIBUTE6,
recInfoForHist.ATTRIBUTE7,
recInfoForHist.ATTRIBUTE8,
recInfoForHist.ATTRIBUTE9,
recInfoForHist.ATTRIBUTE10,
recInfoForHist.ATTRIBUTE11,
recInfoForHist.ATTRIBUTE12,
recInfoForHist.ATTRIBUTE13,
recInfoForHist.ATTRIBUTE14,
recInfoForHist.ATTRIBUTE15,
recInfoForHist.ORG_ID,
recInfoForHist.RECOVERY_TYPE_CODE,
recInfoForHist.RECOVERY_SPACE_STD_CODE,
recInfoForHist.FIN_OBLIG_END_DATE,
recInfoForHist.CUSTOMER_ID,
recInfoForHist.CUSTOMER_SITE_USE_ID,
recInfoForHist.LEASE_RENTABLE_AREA,
recInfoForHist.LEASE_USABLE_AREA,
recInfoForHist.LEASE_ASSIGNABLE_AREA,
recInfoForHist.LEASE_LOAD_FACTOR,
recInfoForHist.LOCATION_RENTABLE_AREA,
recInfoForHist.LOCATION_USABLE_AREA,
recInfoForHist.LOCATION_ASSIGNABLE_AREA,
recInfoForHist.LOCATION_LOAD_FACTOR
);
UPDATE pn_tenancies_all
SET LOCATION_ID = X_LOCATION_ID,
LEASE_CHANGE_ID = X_LEASE_CHANGE_ID,
TENANCY_USAGE_LOOKUP_CODE = X_TENANCY_USAGE_LOOKUP_CODE,
PRIMARY_FLAG = X_PRIMARY_FLAG,
ESTIMATED_OCCUPANCY_DATE = X_ESTIMATED_OCCUPANCY_DATE,
OCCUPANCY_DATE = X_OCCUPANCY_DATE,
EXPIRATION_DATE = X_EXPIRATION_DATE,
ASSIGNABLE_FLAG = X_ASSIGNABLE_FLAG,
SUBLEASEABLE_FLAG = X_SUBLEASEABLE_FLAG,
TENANTS_PROPORTIONATE_SHARE = X_TENANTS_PROPORTIONATE_SHARE,
ALLOCATED_AREA_PCT = X_ALLOCATED_AREA_PCT,
ALLOCATED_AREA = X_ALLOCATED_AREA,
STATUS = X_STATUS,
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,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
RECOVERY_TYPE_CODE = X_RECOVERY_TYPE_CODE,
RECOVERY_SPACE_STD_CODE = X_RECOVERY_SPACE_STD_CODE,
FIN_OBLIG_END_DATE = X_FIN_OBLIG_END_DATE,
CUSTOMER_ID = X_CUSTOMER_ID,
CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID,
LEASE_RENTABLE_AREA = X_LEASE_RENTABLE_AREA,
LEASE_USABLE_AREA = X_LEASE_USABLE_AREA,
LEASE_ASSIGNABLE_AREA = X_LEASE_ASSIGNABLE_AREA,
LEASE_LOAD_FACTOR = X_LEASE_LOAD_FACTOR,
LOCATION_RENTABLE_AREA = X_LOCATION_RENTABLE_AREA,
LOCATION_USABLE_AREA = X_LOCATION_USABLE_AREA,
LOCATION_ASSIGNABLE_AREA = X_LOCATION_ASSIGNABLE_AREA,
LOCATION_LOAD_FACTOR = X_LOCATION_LOAD_FACTOR
WHERE TENANCY_ID = X_TENANCY_ID ;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_ROW (-)');
END Update_Row;
PROCEDURE Delete_Row
(
X_TENANCY_ID IN NUMBER
)
IS
BEGIN
pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_ROW (+)');
DELETE FROM pn_tenancies_all
WHERE tenancy_id = x_tenancy_id;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_ROW (-)');
END Delete_Row;
SELECT 1
INTO l_dummy
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM pn_tenancies_all pnt
WHERE pnt.lease_id = x_lease_id
AND pnt.status = 'A'
AND pnt.primary_flag = 'Y'
AND ((x_tenancy_id IS NULL) or (pnt.tenancy_id <> x_tenancy_id))
);
SELECT org_id
FROM pn_leases_all pnl
WHERE pnl.lease_id = x_lease_id;
SELECT location_code
INTO l_locationCode
FROM pn_locations_all
WHERE location_id = x_location_id
AND ROWNUM < 2;
SELECT pnl.parent_lease_id
INTO l_parentLeaseId
FROM pn_leases_all pnl
WHERE pnl.lease_id = x_lease_id;
SELECT pnt.lease_id
INTO l_LeaseId
FROM pn_tenancies_all pnt
WHERE pnt.status = 'A'
AND pnt.lease_id = x_lease_id
AND pnt.location_id IN
(SELECT b.parent_location_id
FROM pn_locations_all b
CONNECT BY b.location_id = PRIOR parent_location_id
START WITH b.location_id = x_location_id
)
AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
AND TRUNC (x_expiration_date)
OR TRUNC(pnt.expiration_date)
BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
AND TRUNC (x_expiration_date)
)
AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id)
)
AND ROWNUM < 2;
SELECT lease_num
INTO l_leaseNumber
FROM pn_leases_all
WHERE lease_id <> x_lease_id
AND parent_lease_id IS NULL
AND lease_id =
(SELECT lease_id
FROM pn_tenancies_all pnt
WHERE pnt.status = 'A'
AND pnt.location_id IN
(SELECT a.location_id
FROM pn_locations_all a
CONNECT BY PRIOR a.parent_location_id = a.location_id
START WITH a.location_id = x_location_id
UNION ALL
SELECT b.location_id
FROM pn_locations_all b
CONNECT BY PRIOR b.location_id = b.parent_location_id
START WITH b.location_id = x_location_id
)
AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
AND TRUNC (x_expiration_date)
OR TRUNC(pnt.expiration_date)
BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
AND TRUNC (x_expiration_date)
)
AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id))
AND ROWNUM < 2
);
SELECT lease_num
INTO l_leaseNumber
FROM pn_leases_all
WHERE parent_lease_id = x_lease_id
AND lease_id =
(SELECT lease_id
FROM pn_tenancies_all pnt
WHERE pnt.status = 'A'
AND pnt.location_id IN
(SELECT b.location_id
FROM pn_locations_all b
CONNECT BY PRIOR b.location_id = b.parent_location_id
START WITH b.location_id = x_location_id
)
AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
< TRUNC(NVL(x_occupancy_date, x_estimated_occupancy_date))
OR TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
> TRUNC (x_expiration_date)
OR TRUNC(pnt.expiration_date)
< TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
OR TRUNC(pnt.expiration_date)
> TRUNC (x_expiration_date)
)
AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id)
)
AND ROWNUM < 2
);
SELECT location_id
INTO l_leaseNumber
FROM pn_locations_all
WHERE location_id = x_location_id
AND location_id IN
(SELECT b.location_id
FROM pn_locations_all b
CONNECT BY PRIOR b.location_id = b.parent_location_id
START WITH b.location_id IN
(SELECT pnt.location_id
FROM pn_tenancies_all pnt
WHERE pnt.status = 'A'
AND pnt.lease_id = l_parentLeaseId
AND (TRUNC(NVL(x_occupancy_date, x_estimated_occupancy_date))
BETWEEN TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
AND TRUNC(pnt.expiration_date)
AND TRUNC (x_expiration_date)
BETWEEN TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
AND TRUNC(pnt.expiration_date)
)
)
)
AND ROWNUM < 2;
SELECT lease_num
INTO l_leaseNumber
FROM pn_leases_all
WHERE lease_id =
(SELECT lease_id
FROM pn_tenancies_all pnt
WHERE pnt.status = 'A'
AND pnt.lease_id <> l_parentLeaseId
AND pnt.location_id IN
(SELECT a.location_id
FROM pn_locations_all a
CONNECT BY PRIOR a.parent_location_id = a.location_id
START WITH a.location_id = x_location_id
UNION ALL
SELECT b.location_id
FROM pn_locations_all b
CONNECT BY PRIOR b.location_id = b.parent_location_id
START WITH b.location_id = x_location_id
)
AND (TRUNC(NVL(pnt.occupancy_date, pnt.estimated_occupancy_date))
BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
AND TRUNC (x_expiration_date)
OR TRUNC(pnt.expiration_date)
BETWEEN TRUNC (NVL(x_occupancy_date, x_estimated_occupancy_date))
AND TRUNC (x_expiration_date)
)
AND ((x_tenancy_id IS NULL) OR (pnt.tenancy_id <> x_tenancy_id))
AND ROWNUM < 2
);
SELECT location_code,
location_type_lookup_code
FROM pn_locations_all pnl
WHERE pnl.location_id = p_location_id
AND p_start_date BETWEEN pnl.active_start_date AND pnl.active_end_date;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_space_assign_cust_all spc
WHERE spc.tenancy_id = p_tenancy_id
AND (EXISTS (SELECT NULL
FROM pn_rec_arcl_dtl_all mst,
pn_rec_arcl_dtlln_all dtl
WHERE mst.area_class_dtl_id = dtl.area_class_dtl_id
AND dtl.cust_space_assign_id = spc.cust_space_assign_id) OR
EXISTS (SELECT NULL
FROM pn_rec_expcl_dtl_all mst,
pn_rec_expcl_dtlln_all dtl
WHERE mst.expense_class_dtl_id = dtl.expense_class_dtl_id
AND dtl.cust_space_assign_id = spc.cust_space_assign_id))
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_space_assign_cust_all spc
WHERE spc.tenancy_id = p_tenancy_id
AND (NVL(spc.cust_assign_end_date,p_cust_assign_end_dt) >= p_cust_assign_start_dt OR
spc.cust_assign_start_date <= p_cust_assign_end_dt)
AND (EXISTS (SELECT NULL
FROM pn_rec_arcl_dtl_all mst,
pn_rec_arcl_dtlln_all dtl
WHERE mst.area_class_dtl_id = dtl.area_class_dtl_id
AND mst.status = 'LOCKED'
AND dtl.cust_space_assign_id = spc.cust_space_assign_id) OR
EXISTS (SELECT NULL
FROM pn_rec_expcl_dtl_all mst,
pn_rec_expcl_dtlln_all dtl
WHERE mst.expense_class_dtl_id = dtl.expense_class_dtl_id
AND mst.status = 'LOCKED'
AND dtl.cust_space_assign_id = spc.cust_space_assign_id))
);
SELECT active_start_date,
NVL(active_end_date, p_to_date) active_end_date,
assignable_area,
location_type_lookup_code
FROM pn_locations_all
WHERE location_id = p_location_id
AND active_start_date <= p_to_date
AND NVL(active_end_date, p_to_date) >= p_from_date
ORDER BY active_start_date;
loc_info_tbl.delete;
SELECT cust_assign_start_date,
NVL(cust_assign_end_date, p_to_date) cust_assign_end_date,
allocated_area
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND cust_assign_start_date <= p_to_date
AND NVL(cust_assign_end_date, p_to_date) >= p_from_date;
SELECT emp_assign_start_date,
NVL(emp_assign_end_date, p_to_date) emp_assign_end_date,
allocated_area
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND emp_assign_start_date <= p_to_date
AND NVL(emp_assign_end_date, p_to_date) >= p_from_date;
space_assign_info_tbl.delete;
PROCEDURE insert_space_assign_row(
p_location_id IN NUMBER
,p_lease_id IN NUMBER
,p_customer_id IN NUMBER
,p_cust_site_use_id IN NUMBER
,p_recovery_space_std_code IN VARCHAR2
,p_recovery_type_code IN VARCHAR2
,p_fin_oblig_end_date IN DATE
,p_tenancy_id IN NUMBER
,p_org_id IN NUMBER
,p_space_assign_info_tbl IN space_assign_info_type
,p_return_status OUT NOCOPY VARCHAR2
)
IS
l_rowid ROWID := NULL;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_SPACE_ASSIGN_ROW (+)');
pn_space_assign_cust_pkg.insert_row(
x_rowid => l_rowId,
x_cust_space_assign_id => l_cust_space_assign_id,
x_location_id => p_location_id,
x_cust_account_id => p_customer_id,
x_site_use_id => p_cust_site_use_id,
x_expense_account_id => NULL,
x_project_id => NULL,
x_task_id => NULL,
x_cust_assign_start_date => p_space_assign_info_tbl(i).cust_assign_start_date,
x_cust_assign_end_date => p_space_assign_info_tbl(i).cust_assign_end_date,
x_allocated_area_pct => p_space_assign_info_tbl(i).allocated_area_pct,
x_allocated_area => p_space_assign_info_tbl(i).allocated_area,
x_utilized_area => 1,
x_fin_oblig_end_date => p_space_assign_info_tbl(i).fin_oblig_end_date,
x_cust_space_comments => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_creation_date => SYSDATE,
x_created_by => NVL(fnd_profile.value('USER_ID'),-1),
x_last_update_date => SYSDATE,
x_last_updated_by => NVL(fnd_profile.value('USER_ID'),-1),
x_last_update_login => NVL(fnd_profile.value('USER_ID'),-1),
x_org_id => p_org_id,
x_lease_id => p_lease_id,
x_recovery_space_std_code => p_recovery_space_std_code,
x_recovery_type_code => p_recovery_type_code,
x_tenancy_id => p_tenancy_id,
x_return_status => p_return_status);
pnp_debug_pkg.debug('PN_TENANCIES_PKG.INSERT_SPACE_ASSIGN_ROW (-)');
END insert_space_assign_row;
space_assign_info_tbl.delete;
SELECT 'Y'
FROM DUAl
WHERE EXISTS (SELECT NULL
FROM pn_leases_all pnl,
pn_tenancies_all ten
WHERE pnl.lease_id <> p_lease_id
AND pnl.lease_class_code <> 'DIRECT'
AND pnl.lease_id = ten.lease_id
AND ten.location_id = p_location_id
AND NVL(ten.estimated_occupancy_date, ten.occupancy_date)
<= p_cust_assign_end_dt
AND ten.expiration_date >= p_cust_assign_start_dt
);
SELECT 'Y'
FROM DUAl
WHERE EXISTS (SELECT NULL
from pn_locations_all pl
where pl.location_id = p_location_id
and pl.assignable_area = 0
and pl.active_start_date <= nvl(p_cust_assign_end_dt,to_date('12/31/4712','MM/DD/YYYY'))
and pl.active_end_date >= p_cust_assign_start_dt);
SELECT org_id
FROM pn_leases_all pnl
WHERE pnl.lease_id = p_lease_id;
space_assign_info_tbl.delete;
SELECT org_id
FROM pn_leases_all pnl
WHERE pnl.lease_id = p_lease_id;
insert_space_assign_row (
p_location_id => p_location_id
,p_lease_id => p_lease_id
,p_customer_id => p_customer_id
,p_cust_site_use_id => p_cust_site_use_id
,p_recovery_space_std_code => p_recovery_space_std_code
,p_recovery_type_code => p_recovery_type_code
,p_fin_oblig_end_date => p_fin_oblig_end_date
,p_tenancy_id => p_tenancy_id
,p_org_id => p_org_id
,p_space_assign_info_tbl => space_assign_info_tbl
,p_return_status => l_return_status
);
PROCEDURE delete_auto_space_assign (
p_tenancy_id IN NUMBER
,p_cust_assign_start_date IN DATE
,p_cust_assign_end_date IN DATE
,p_action OUT NOCOPY VARCHAR2
,p_location_id IN pn_locations_all.location_id%TYPE DEFAULT NULL
,p_loc_type_code IN pn_locations_all.location_type_lookup_code%TYPE DEFAULT NULL
)
IS
l_count NUMBER := 0;
SELECT org_id
FROM pn_tenancies_all
WHERE tenancy_id = p_tenancy_id;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_AUTO_SPACE_ASSIGN (+) Auto_Space_Dist: '
||l_auto_space_dist||', TenancyId: '||p_tenancy_id);
DELETE FROM pn_rec_expcl_dtlln_all
WHERE cust_space_assign_id IN (SELECT cust_space_assign_id
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id);
DELETE FROM pn_rec_arcl_dtlln_all
WHERE cust_space_assign_id IN (SELECT cust_space_assign_id
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id);
DELETE FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.DELETE_AUTO_SPACE_ASSIGN (-) Deleted '||l_del_count||
' Space Assgn Rows');
pnp_debug_pkg.log('Delete_auto_space_assign - Errmsg: ' || sqlerrm);
END delete_auto_space_assign;
PROCEDURE update_auto_space_assign(
p_location_id IN NUMBER
,p_lease_id IN NUMBER
,p_customer_id IN NUMBER
,p_cust_site_use_id IN NUMBER
,p_cust_assign_start_dt IN DATE
,p_cust_assign_end_dt IN DATE
,p_recovery_space_std_code IN VARCHAR2
,p_recovery_type_code IN VARCHAR2
,p_fin_oblig_end_date IN DATE
,p_allocated_pct IN NUMBER
,p_tenancy_id IN NUMBER
,p_org_id IN NUMBER
,p_location_id_old IN NUMBER
,p_customer_id_old IN NUMBER
,p_cust_site_use_id_old IN NUMBER
,p_cust_assign_start_dt_old IN DATE
,p_cust_assign_end_dt_old IN DATE
,p_recovery_space_std_code_old IN VARCHAR2
,p_recovery_type_code_old IN VARCHAR2
,p_fin_oblig_end_date_old IN DATE
,p_allocated_pct_old IN NUMBER
,p_action OUT NOCOPY VARCHAR2
,p_msg OUT NOCOPY VARCHAR2
)
IS
CURSOR csr_min_cust_assign IS
SELECT cust_space_assign_id,
cust_assign_start_date,
cust_assign_end_date,
allocated_area
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id
AND cust_assign_start_date = (SELECT MIN(cust_assign_start_date)
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id);
SELECT cust_space_assign_id,
cust_assign_start_date,
cust_assign_end_date,
allocated_area
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id
AND cust_assign_end_date = (SELECT MAX(cust_assign_end_date)
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id);
SELECT org_id
FROM pn_tenancies_all
WHERE tenancy_id = p_tenancy_id;
SELECT cust_assign_start_date,
cust_assign_end_date
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_AUTO_SPACE_ASSIGN (+) Auto_Space_Dist: '
||l_auto_space_dist);
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 1: Location is changed.');
delete_auto_space_assign(
p_tenancy_id => p_tenancy_id
,p_cust_assign_start_date => p_cust_assign_start_dt_old
,p_cust_assign_end_date => p_cust_assign_end_dt_old
,p_action => p_action
);
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: Tenancy start/end dates are changed.');
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 1. Tenancy Start date brought in.');
DELETE FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id
AND cust_assign_end_date < p_cust_assign_start_dt;
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 1. Rows Deleted: '||l_count);
UPDATE pn_space_assign_cust_all
SET cust_assign_start_date = p_cust_assign_start_dt
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
WHERE tenancy_id = p_tenancy_id
AND cust_assign_start_date < p_cust_assign_start_dt
AND cust_assign_end_date >= p_cust_assign_start_dt;
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 1. Rows Updated: '||l_count);
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 3. Tenancy End date brought in.');
DELETE FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id
AND cust_assign_start_date > p_cust_assign_end_dt;
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 3. Rows Deleted: '||l_count);
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = p_cust_assign_end_dt
,fin_oblig_end_date = p_fin_oblig_end_date
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
WHERE tenancy_id = p_tenancy_id
AND cust_assign_start_date <= p_cust_assign_end_dt
AND cust_assign_end_date > p_cust_assign_end_dt;
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 3. Rows Updated: '||l_count);
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 2: 2/4. Tenancy Start/End'
||' expanded out.');
space_assign_tbl.delete;
pnp_debug_pkg.debug(' Case: 2. Update Space Assgn... i: '||i
||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
||', area: '||space_assign_info_tbl(i).allocated_area
||',');
UPDATE pn_space_assign_cust_all
SET cust_assign_start_date = space_assign_info_tbl(i).cust_assign_start_date
WHERE cust_space_assign_id = l_min_cust_assign_id;
pnp_debug_pkg.debug(' Case: 4. Update Space Assgn... i: '||i
||', start_date: '||space_assign_info_tbl(i).cust_assign_start_date
||', end_date: '||space_assign_info_tbl(i).cust_assign_end_date
||', area: '||space_assign_info_tbl(i).allocated_area
||',');
UPDATE pn_space_assign_cust_all
SET cust_assign_end_date = space_assign_info_tbl(i).cust_assign_end_date
,fin_oblig_end_date = l_fin_oblig_end_date
WHERE cust_space_assign_id = l_max_cust_assign_id;
UPDATE pn_space_assign_cust_all
SET fin_oblig_end_date = cust_assign_end_date
WHERE cust_space_assign_id = l_max_cust_assign_id;
insert_space_assign_row(
p_location_id => p_location_id
,p_lease_id => p_lease_id
,p_customer_id => p_customer_id
,p_cust_site_use_id => p_cust_site_use_id
,p_recovery_space_std_code => p_recovery_space_std_code
,p_recovery_type_code => p_recovery_type_code
,p_fin_oblig_end_date => p_fin_oblig_end_date
,p_tenancy_id => p_tenancy_id
,p_org_id => p_org_id
,p_space_assign_info_tbl => space_assign_tbl
,p_return_status => l_return_status
);
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 3: fin_oblig_end_date is changed.');
UPDATE pn_space_assign_cust_all
SET fin_oblig_end_date = p_fin_oblig_end_date
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
WHERE tenancy_id = p_tenancy_id
AND fin_oblig_end_date = p_fin_oblig_end_date_old;
UPDATE pn_space_assign_cust_all
SET fin_oblig_end_date = p_fin_oblig_end_date
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
WHERE tenancy_id = p_tenancy_id
AND cust_assign_end_date = p_cust_assign_end_dt;
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 4: customer_id/cust_site_use_id/'
||'space_std/recovery_type is changed.');
UPDATE pn_space_assign_cust_all
SET cust_account_id = p_customer_id
,site_use_id = p_cust_site_use_id
,recovery_space_std_code = p_recovery_space_std_code
,recovery_type_code = p_recovery_type_code
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
WHERE tenancy_id = p_tenancy_id;
pnp_debug_pkg.debug('Update_Auto_Space_Assign : Check# 5: allocated_area_pct is changed.');
UPDATE pn_space_assign_cust_all
SET allocated_area_pct = p_allocated_pct
,allocated_area = l_allocated_area
WHERE tenancy_id = p_tenancy_id
AND cust_assign_start_date = l_cust_assign_start_date;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_AUTO_SPACE_ASSIGN (-)');
pnp_debug_pkg.log('Update_auto_space_assign - Errmsg: ' || sqlerrm);
END update_auto_space_assign;
PROCEDURE Update_Dup_Space_Assign(
p_location_id IN NUMBER
,p_customer_id IN NUMBER
,p_lease_id IN NUMBER
,p_tenancy_id IN NUMBER
,p_cust_site_use_id IN NUMBER
,p_cust_assign_start_dt IN DATE
,p_cust_assign_end_dt IN DATE
,p_recovery_space_std_code IN VARCHAR2
,p_recovery_type_code IN VARCHAR2
,p_fin_oblig_end_date IN DATE
,p_allocated_pct IN NUMBER
,p_org_id IN NUMBER
,p_action OUT NOCOPY VARCHAR2
,p_msg OUT NOCOPY VARCHAR2
)
IS
CURSOR get_cust_space_assign_id IS
SELECT cust_space_assign_id,
NVL(cust_assign_start_date, pnt_locations_pkg.g_start_of_time) cust_assign_start_date,
NVL(cust_assign_end_date, pnt_locations_pkg.g_end_of_time) cust_assign_end_date
FROM pn_space_assign_cust_all
WHERE cust_account_id = p_customer_id
AND location_id = p_location_id
AND cust_assign_start_date <= p_cust_assign_end_dt
AND NVL(cust_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
>= p_cust_assign_start_dt;
SELECT org_id
FROM pn_locations_all pnl
WHERE pnl.location_id = p_location_id
AND ROWNUM < 2;
pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_DUP_SPACE_ASSIGN (+) Auto_Space_Dist: '
||l_auto_space_dist);
UPDATE pn_space_assign_cust_all
SET lease_id = p_lease_id
,tenancy_id = p_tenancy_id
,cust_assign_start_date = NVL(cust_assign_start_date, pnt_locations_pkg.g_start_of_time)
,cust_assign_end_date = NVL(cust_assign_end_date, pnt_locations_pkg.g_end_of_time)
,site_use_id = p_cust_site_use_id
,recovery_space_std_code = p_recovery_space_std_code
,recovery_type_code = p_recovery_type_code
,fin_oblig_end_date = NVL(cust_assign_end_date, pnt_locations_pkg.g_end_of_time)
,last_update_date = SYSDATE
,last_updated_by = NVL(FND_PROFILE.VALUE('USER_ID'),-1)
WHERE cust_space_assign_id = l_cust_space_assign_id;
pn_tenancies_pkg.update_auto_space_assign
(
p_location_id => p_location_id
,p_lease_id => p_lease_id
,p_customer_id => p_customer_id
,p_cust_site_use_id => p_cust_site_use_id
,p_cust_assign_start_dt => p_cust_assign_start_dt
,p_cust_assign_end_dt => p_cust_assign_end_dt
,p_recovery_space_std_code => p_recovery_space_std_code
,p_recovery_type_code => p_recovery_type_code
,p_fin_oblig_end_date => p_fin_oblig_end_date
,p_allocated_pct => p_allocated_pct
,p_tenancy_id => p_tenancy_id
,p_org_id => p_org_id
,p_location_id_old => p_location_id
,p_customer_id_old => p_customer_id
,p_cust_site_use_id_old => p_cust_site_use_id
,p_cust_assign_start_dt_old => l_cust_assign_start_date
,p_cust_assign_end_dt_old => l_cust_assign_end_date
,p_recovery_space_std_code_old => p_recovery_space_std_code
,p_recovery_type_code_old => p_recovery_type_code
,p_fin_oblig_end_date_old => p_fin_oblig_end_date
,p_allocated_pct_old => p_allocated_pct
,p_action => p_action
,p_msg => p_msg
);
pnp_debug_pkg.debug('PN_TENANCIES_PKG.UPDATE_DUP_SPACE_ASSIGN (-)');
END Update_Dup_Space_Assign;
SELECT min(allocated_area_pct) min_area_pct
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id;
SELECT leases.lease_class_code lease_code,
allocated_area_pct
FROM pn_leases_all leases,
pn_tenancies_all tenant
WHERE leases.lease_id = tenant.lease_id
AND tenant.tenancy_id = p_tenancy_id;
SELECT min(allocated_area) min_area
FROM pn_space_assign_cust_all
WHERE tenancy_id = p_tenancy_id;
SELECT leases.lease_class_code lease_code,
allocated_area
FROM pn_leases_all leases,
pn_tenancies_all tenant
WHERE leases.lease_id = tenant.lease_id
AND tenant.tenancy_id = p_tenancy_id;
SELECT cust_assign_start_date,
NVL(cust_assign_end_date, p_to_date) cust_assign_end_date,
nvl(allocated_area,0) allocated_area
FROM pn_space_assign_cust_all
WHERE location_id = p_location_id
AND cust_assign_start_date <= p_to_date
AND NVL(cust_assign_end_date, p_to_date) >= p_from_date;
SELECT emp_assign_start_date,
NVL(emp_assign_end_date, p_to_date) emp_assign_end_date,
nvl(allocated_area,0) allocated_area
FROM pn_space_assign_emp_all
WHERE location_id = p_location_id
AND emp_assign_start_date <= p_to_date
AND NVL(emp_assign_end_date, p_to_date) >= p_from_date;