[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_emp_space_assign_id IN OUT NOCOPY NUMBER,
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_location_id IN NUMBER,
x_person_id IN NUMBER,
x_project_id IN NUMBER,
x_task_id IN NUMBER,
x_emp_assign_start_date IN DATE,
x_emp_assign_end_date IN DATE,
x_cost_center_code IN VARCHAR2,
x_allocated_area_pct IN NUMBER,
x_allocated_area IN NUMBER,
x_utilized_area IN NUMBER,
x_emp_space_comments IN VARCHAR2,
x_attribute_category 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_source IN VARCHAR2
)
IS
CURSOR c IS
SELECT ROWID
FROM pn_space_assign_emp_all
WHERE emp_space_assign_id = x_emp_space_assign_id;
SELECT org_id
FROM pn_locations_all
WHERE location_id = x_location_id;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.INSERT_ROW (+) SpcAsgnId: '
||x_emp_space_assign_id||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
SELECT pn_space_assign_emp_s.NEXTVAL
INTO x_emp_space_assign_id
FROM DUAL;
INSERT INTO pn_space_assign_emp_all
(
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
org_id,
source
)
VALUES
(
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_emp_space_assign_id,
x_location_id,
x_person_id,
x_project_id,
x_task_id,
x_emp_assign_start_date,
x_emp_assign_end_date,
x_cost_center_code,
x_allocated_area_pct,
x_allocated_area,
x_utilized_area,
x_emp_space_comments,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_attribute_category,
l_org_id,
x_source
);
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.INSERT_ROW (-) SpcAsgnId: '
||x_emp_space_assign_id||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
END Insert_Row;
SELECT *
FROM pn_space_assign_emp_all
WHERE emp_space_assign_id = x_emp_space_assign_id
FOR UPDATE OF emp_space_assign_id NOWAIT;
PROCEDURE Update_Row (
x_emp_space_assign_id IN NUMBER,
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_location_id IN NUMBER,
x_person_id IN NUMBER,
x_project_id IN NUMBER,
x_task_id IN NUMBER,
x_emp_assign_start_date IN DATE,
x_emp_assign_end_date IN DATE,
x_cost_center_code IN VARCHAR2,
x_allocated_area_pct IN NUMBER,
x_allocated_area IN NUMBER,
x_utilized_area IN NUMBER,
x_emp_space_comments IN VARCHAR2,
x_attribute_category 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_source IN VARCHAR2
)
IS
l_emp_space_assign_id NUMBER;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW (+) SpcAsgnId: '
||x_emp_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
IF x_update_correct_option = 'UPDATE' THEN
SELECT pn_space_assign_emp_s.NEXTVAL
INTO l_emp_space_assign_id
FROM DUAL;
INSERT INTO pn_space_assign_emp_all
(
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,
source,
org_id
)
VALUES
(
l_emp_space_assign_id,
tlempinfo.location_id,
tlempinfo.person_id,
tlempinfo.project_id,
tlempinfo.task_id,
tlempinfo.emp_assign_start_date,
(x_emp_assign_start_date - 1),
tlempinfo.cost_center_code,
tlempinfo.allocated_area_pct,
tlempinfo.allocated_area,
tlempinfo.utilized_area,
tlempinfo.emp_space_comments,
tlempinfo.last_update_date,
tlempinfo.last_updated_by,
tlempinfo.creation_date,
tlempinfo.created_by,
tlempinfo.last_update_login,
tlempinfo.attribute_category,
tlempinfo.attribute1,
tlempinfo.attribute2,
tlempinfo.attribute3,
tlempinfo.attribute4,
tlempinfo.attribute5,
tlempinfo.attribute6,
tlempinfo.attribute7,
tlempinfo.attribute8,
tlempinfo.attribute9,
tlempinfo.attribute10,
tlempinfo.attribute11,
tlempinfo.attribute12,
tlempinfo.attribute13,
tlempinfo.attribute14,
tlempinfo.attribute15,
tlempinfo.source,
tlempinfo.org_id
);
UPDATE pn_space_assign_emp_all
SET 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,
location_id = x_location_id,
person_id = x_person_id,
project_id = x_project_id,
task_id = x_task_id,
emp_assign_start_date = x_emp_assign_start_date,
emp_assign_end_date = x_emp_assign_end_date,
cost_center_code = x_cost_center_code,
allocated_area_pct = x_allocated_area_pct,
allocated_area = x_allocated_area,
utilized_area = x_utilized_area,
emp_space_comments = x_emp_space_comments,
attribute_category = x_attribute_category,
emp_space_assign_id = x_emp_space_assign_id,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
source = x_source
WHERE emp_space_assign_id = x_emp_space_assign_id;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.UPDATE_ROW (-) SpcAsgnId: '
||x_emp_space_assign_id||', Mode: '||x_update_correct_option||', LocId: '||x_location_id
||', StrDt: '||TO_CHAR(x_emp_assign_start_date,'MM/DD/YYYY')
||', EndDt: '||TO_CHAR(x_emp_assign_end_date, 'MM/DD/YYYY')
||', CC: '||x_cost_center_code||', PerId: '||x_person_id);
END Update_Row;
PROCEDURE delete_row(x_emp_space_assign_id IN NUMBER) IS
BEGIN
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.DELETE_ROW (+) SpcAsgnId: '
||x_emp_space_assign_id);
DELETE FROM pn_space_assign_emp_all
WHERE emp_space_assign_id = x_emp_space_assign_id;
pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.DELETE_ROW (-) SpcAsgnId: '
||x_emp_space_assign_id);
END delete_row;
SELECT location_id,
active_start_date,
active_end_date
FROM pn_locations_all
WHERE active_end_date >= p_str_dt
AND active_start_date <= p_end_dt
AND location_id = p_loc_id
ORDER BY active_start_date;
SELECT 'Y'
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM pn_locations_all
WHERE l_date BETWEEN active_start_date AND active_end_date
AND location_id = p_loc_id);
loctn_tab.delete;
SELECT loctn_tab(i+1).active_start_date -
loctn_tab(i).active_end_date
INTO l_diff
FROM DUAL;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_space_assign_emp_all
WHERE person_id = p_person_id
AND location_id = p_loc_id
AND cost_center_code = p_cost_cntr_code
AND emp_assign_start_date <= p_assgn_str_dt
AND NVL(emp_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_space_assign_emp_all
WHERE cost_center_code = p_cost_cntr_code
AND location_id = p_loc_id
AND emp_assign_start_date <= p_assgn_str_dt
AND person_id is null
AND NVL(emp_assign_end_date, TO_DATE('12/31/4712', 'MM/DD/YYYY'))
>= p_assgn_str_dt);
SELECT emp_space_assign_id
FROM pn_space_assign_emp_all
WHERE location_id = p_loc
AND person_id = p_person
AND emp_assign_start_date =
(SELECT MIN(emp_assign_start_date)
FROM pn_space_assign_emp_all
WHERE location_id = p_loc
AND person_id = p_person);
SELECT emp_space_assign_id
FROM pn_space_assign_emp_all
WHERE location_id = p_loc
AND cost_center_code = p_cc
AND emp_assign_start_date =
(SELECT MIN(emp_assign_start_date)
FROM pn_space_assign_emp_all
WHERE location_id = p_loc
AND cost_center_code = p_cc);