The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2, -- 4537865
X_Batch_ID IN OUT NOCOPY NUMBER, -- 4537865
X_Org_Id IN NUMBER DEFAULT NULL, --R12 MOAC Changes: Bug 4363093
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Updated_By NUMBER,
X_Last_Update_Date DATE,
X_Last_Update_Login NUMBER,
X_Batch_Name VARCHAR2,
X_Batch_status_Code VARCHAR2,
X_Description VARCHAR2,
X_Project_Attribute VARCHAR2,
X_Effective_Date DATE,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2 )
IS
l_batch_id NUMBER;
SELECT PA_MASS_UPDATE_BATCHES_S.NextVal
INTO l_batch_id
FROM dual;
INSERT INTO PA_MASS_UPDATE_BATCHES
( Batch_ID,
Batch_Name,
Description,
Batch_Status_Code,
Project_Attribute,
Effective_Date,
org_id, --R12 MOAC Changes: Bug 4363093
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login )
VALUES
( l_batch_id,
X_Batch_Name,
X_Description,
X_Batch_Status_Code,
X_Project_Attribute,
trunc(X_Effective_Date),
l_org_Id, --R12 MOAC Changes: Bug 4363093
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_Creation_Date,
X_Created_By,
X_Last_Update_Date,
X_Last_Updated_By,
X_Last_Update_Login
);
SELECT rowid INTO X_Rowid
FROM PA_MASS_UPDATE_BATCHES
WHERE batch_id = l_batch_id;
,p_procedure_name => 'Insert_Row'
,p_error_text => SUBSTRB(SQLERRM,1,240));
END Insert_Row;
PROCEDURE Update_Row( X_Rowid VARCHAR2,
X_Last_Updated_By NUMBER,
X_Last_Update_Date DATE,
X_Last_Update_Login NUMBER,
X_Batch_Name VARCHAR2,
X_Batch_status_Code VARCHAR2,
X_Rejection_Code VARCHAR2,
X_Description VARCHAR2,
X_Project_Attribute VARCHAR2,
X_Effective_Date DATE,
X_Process_Run_By NUMBER,
X_Process_Run_Date DATE,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2 )
IS
BEGIN
UPDATE pa_mass_update_batches
SET
Batch_Name = X_Batch_Name,
Description = X_Description,
Batch_Status_Code = X_Batch_Status_Code,
Rejection_Code = X_Rejection_Code,
Project_Attribute = X_Project_Attribute,
Process_Run_Date = X_Process_Run_Date,
Process_Run_By = X_Process_Run_By,
Effective_Date = trunc(X_Effective_Date),
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
WHERE
rowid = X_Rowid;
END Update_Row;
SELECT Batch_Name,
Description,
Batch_Status_Code,
Process_Run_Date,
Process_Run_By,
Project_Attribute,
Effective_Date,
Rejection_Code,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15
FROM pa_mass_update_batches
WHERE rowid = X_rowid
FOR UPDATE NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Delete_Row( X_Rowid VARCHAR2 )
IS
l_batch_id NUMBER;
SELECT batch_id INTO l_batch_id
FROM pa_mass_update_batches
WHERE rowid = X_Rowid;
DELETE FROM pa_mass_update_details
WHERE batch_id = l_batch_id;
DELETE FROM pa_mass_update_batches
WHERE rowid = X_Rowid;
END Delete_Row;
SELECT batch_id
FROM pa_mass_update_batches
WHERE batch_status_code = 'S'
AND trunc(sysdate) >= trunc(nvl(effective_date, sysdate));
l_UPDATE_NOT_ALLOWED EXCEPTION;
SELECT batch_status_code, project_attribute, effective_date
FROM pa_mass_update_batches b
WHERE b.batch_id = X_Batch_ID
FOR UPDATE;
SELECT line_id,
project_id,
task_id,
old_attribute_value,
new_attribute_value,
update_flag,
recalculate_flag,
pa_security.allow_update(project_id) allow_update
FROM pa_mass_update_details
WHERE batch_id = X_Batch_ID
FOR UPDATE;
SELECT x_project_id PROJECT_ID,
x_task_id TASK_ID,
decode(x_task_id,
NULL, p.carrying_out_organization_id,
t.carrying_out_organization_id) OLD_VALUE,
p.project_type PROJECT_TYPE,
p.start_date PROJECT_START_DATE,
p.completion_date PROJECT_END_DATE,
pa_project_stus_utils.is_project_status_closed(
p.project_status_code) PROJECT_CLOSED,
p.public_sector_flag,
decode(x_task_id,
NULL, NULL,
t.task_manager_person_id) TASK_MANAGER_PERSON_ID,
decode(x_task_id,
NULL, NULL,
t.service_type_code) SERVICE_TYPE_CODE,
decode(x_task_id,
NULL, NULL,
t.start_date) TASK_START_DATE,
decode(x_task_id,
NULL, NULL,
t.completion_date) TASK_END_DATE,
decode(x_task_id,
NULL, p.attribute_category,
t.attribute_category) ATTRIBUTE_CATEGORY,
decode(x_task_id,
NULL, p.attribute1,
t.attribute1) ATTRIBUTE1,
decode(x_task_id,
NULL, p.attribute2,
t.attribute2) ATTRIBUTE2,
decode(x_task_id,
NULL, p.attribute3,
t.attribute3) ATTRIBUTE3,
decode(x_task_id,
NULL, p.attribute4,
t.attribute4) ATTRIBUTE4,
decode(x_task_id,
NULL, p.attribute5,
t.attribute5) ATTRIBUTE5,
decode(x_task_id,
NULL, p.attribute6,
t.attribute6) ATTRIBUTE6,
decode(x_task_id,
NULL, p.attribute7,
t.attribute7) ATTRIBUTE7,
decode(x_task_id,
NULL, p.attribute8,
t.attribute8) ATTRIBUTE8,
decode(x_task_id,
NULL, p.attribute9,
t.attribute9) ATTRIBUTE9,
decode(x_task_id,
NULL, p.attribute10,
t.attribute10) ATTRIBUTE10,
decode(x_task_id,
NULL, p.pm_product_code,
t.pm_product_code) PM_PRODUCT_CODE,
p.pm_project_reference,
decode(x_task_id,
NULL, NULL,
t.pm_task_reference) PM_TASK_REFERENCE
FROM pa_projects_all p,
pa_tasks t
WHERE p.project_id = x_project_id
AND t.project_id = p.project_id
AND ( x_task_id IS NULL
OR t.task_id = x_task_id );
UPDATE pa_mass_update_batches
SET batch_status_code = 'P',
rejection_code = NULL,
process_run_date = sysdate,
process_run_by = FND_GLOBAL.user_id,
request_id = FND_GLOBAL.Conc_Request_ID,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE CURRENT OF l_batch_csr;
UPDATE pa_mass_update_details
SET rejection_reason = NULL,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = X_Batch_ID
AND rejection_reason IS NOT NULL;
IF (fnd_function.test('PA_PAXPREPR_UPDATE_ORG') = TRUE) THEN
l_org_func_security := 'Y';
IF (l_BatchLine_rec.update_flag = 'Y') THEN
--
-- Make sure user has proper security to update the project
--
IF (l_BatchLine_rec.allow_update = 'Y') THEN
null;
raise l_UPDATE_NOT_ALLOWED;
X_insert_update_mode => 'UPDATE',
X_calling_module => 'PAXBAUPD',
X_project_id => l_ProjTask_rec.project_id,
X_task_id => l_ProjTask_rec.task_id,
X_old_value => l_ProjTask_rec.old_value,
X_new_value => l_BatchLine_rec.new_attribute_value,
X_project_type => l_ProjTask_rec.project_type,
X_project_start_date => l_ProjTask_rec.project_start_date,
X_project_end_date => l_ProjTask_rec.project_end_date,
X_public_sector_flag => l_ProjTask_rec.public_sector_flag,
X_task_manager_person_id => l_ProjTask_rec.task_manager_person_id,
X_Service_type => l_ProjTask_rec.service_type_code,
X_task_start_date => l_ProjTask_rec.task_start_date,
X_task_end_date => l_ProjTask_rec.task_end_date,
X_entered_by_user_id => FND_GLOBAL.user_id,
X_attribute_category => l_ProjTask_rec.attribute_category,
X_attribute1 => l_ProjTask_rec.attribute1,
X_attribute2 => l_ProjTask_rec.attribute2,
X_attribute3 => l_ProjTask_rec.attribute3,
X_attribute4 => l_ProjTask_rec.attribute4,
X_attribute5 => l_ProjTask_rec.attribute5,
X_attribute6 => l_ProjTask_rec.attribute6,
X_attribute7 => l_ProjTask_rec.attribute7,
X_attribute8 => l_ProjTask_rec.attribute8,
X_attribute9 => l_ProjTask_rec.attribute9,
X_attribute10 => l_ProjTask_rec.attribute10,
X_pm_product_code => l_ProjTask_rec.pm_product_code,
X_pm_project_reference => l_ProjTask_rec.pm_project_reference,
X_pm_task_reference => l_ProjTask_rec.pm_task_reference,
X_functional_security_flag => l_org_func_security,
x_warnings_only_flag => l_warnings_only_flag, --bug3134205
X_err_code => l_err_code,
X_err_stage => l_err_stage,
X_err_stack => l_err_stack );
UPDATE pa_projects_all
SET carrying_out_organization_id =
to_number(l_BatchLine_rec.new_attribute_value),
request_id = FND_GLOBAL.Conc_Request_ID,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE project_id = l_BatchLine_rec.project_id;
UPDATE pa_projects_all
SET carrying_out_organization_id =
to_number(l_BatchLine_rec.new_attribute_value),
last_updated_by = FND_GLOBAL.User_ID,
last_update_login = FND_GLOBAL.Login_ID,
last_update_date = sysdate
WHERE project_id = l_BatchLine_rec.project_id;
UPDATE pa_tasks
SET carrying_out_organization_id =
to_number(l_BatchLine_rec.new_attribute_value),
request_id = FND_GLOBAL.Conc_Request_ID,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE task_id = l_BatchLine_rec.task_id;
UPDATE pa_proj_elements
SET carrying_out_organization_id =
to_number(l_BatchLine_rec.new_attribute_value),
request_id = FND_GLOBAL.Conc_Request_ID,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE proj_element_id = l_BatchLine_rec.task_id;
UPDATE pa_tasks
SET carrying_out_organization_id =
to_number(l_BatchLine_rec.new_attribute_value),
last_updated_by = FND_GLOBAL.User_ID,
last_update_login = FND_GLOBAL.Login_ID,
last_update_date = sysdate
WHERE task_id = l_BatchLine_rec.task_id;
UPDATE pa_proj_elements
SET carrying_out_organization_id =
to_number(l_BatchLine_rec.new_attribute_value),
last_updated_by = FND_GLOBAL.User_ID,
last_update_login = FND_GLOBAL.Login_ID,
last_update_date = sysdate
WHERE proj_element_id = l_BatchLine_rec.task_id;
END IF; -- (l_BatchLine_rec.update_flag = 'Y')
WHEN l_UPDATE_NOT_ALLOWED THEN
l_err_stage := 'PA_PR_UPDATE_NOT_ALLOWED';
UPDATE pa_mass_update_details
SET rejection_reason = l_Error_Tab(i).rejection_reason,
last_updated_by = FND_GLOBAL.user_id,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.login_id
WHERE batch_id = X_Batch_ID
AND line_id = l_Error_Tab(i).line_id;
UPDATE pa_mass_update_batches
SET batch_status_code = l_proc_status,
rejection_code = ERRBUF,
process_run_by = FND_GLOBAL.user_id,
process_run_date = sysdate,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE batch_id = X_Batch_ID;
UPDATE pa_mass_update_batches
SET batch_status_code = 'R',
rejection_code = 'INVALID_STATUS',
process_run_by = FND_GLOBAL.user_id,
process_run_date = sysdate,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE batch_id = X_Batch_ID;
UPDATE pa_mass_update_batches
SET batch_status_code = 'R',
rejection_code = 'EFFECTIVE_DATE',
process_run_by = FND_GLOBAL.user_id,
process_run_date = sysdate,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE batch_id = X_Batch_ID;
UPDATE pa_mass_update_batches
SET batch_status_code = 'R',
rejection_code = 'SQL_ERROR',
process_run_by = FND_GLOBAL.user_id,
process_run_date = sysdate,
program_application_id = FND_GLOBAL.Prog_Appl_ID,
program_id = FND_GLOBAL.Conc_Program_ID,
last_update_login = FND_GLOBAL.Conc_Login_ID,
program_update_date = sysdate
WHERE batch_id = X_Batch_ID;