[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_Row(X_Rowid VARCHAR2) is
CURSOR C_Elements is
SELECT pbe.Rowid
FROM per_budget_elements pbe
WHERE pbe.budget_version_id = (SELECT pbv.budget_version_id
FROM per_budget_versions pbv
WHERE pbv.Rowid = X_Rowid);
l_proc VARCHAR2(72) := g_package||'Delete_Row';
SELECT pbv.budget_id
INTO l_budget_id
FROM per_budget_versions pbv
WHERE pbv.Rowid = X_Rowid;
PER_BUDGET_ELEMENTS_PKG.Delete_Row(X_Rowid => l_ele_rowid);
hr_utility.set_message(800,'PER_52876_BUD_VER_DELETE_FAIL');
DELETE FROM per_budget_versions
WHERE Rowid = X_Rowid;
END Delete_Row;
SELECT null
INTO l_result
FROM per_budget_versions pbv
WHERE pbv.budget_id = X_Budget_Id
AND (pbv.rowid <> X_Rowid
OR X_Rowid is Null);
select pbv.date_to,pbv.date_from
from per_budget_versions pbv
where pbv.budget_id = X_Budget_Id
and (pbv.rowid <> X_Rowid
OR X_Rowid is null)
and pbv.date_to = (select max(pbv2.date_to)
from per_budget_versions pbv2
where pbv2.budget_id = X_Budget_Id
and (pbv2.rowid <> X_Rowid
OR X_Rowid is null)
and pbv2.date_to < X_Date_From);
select pbv.date_to,pbv.date_from
from per_budget_versions pbv
where pbv.budget_id = X_Budget_Id
and (pbv.rowid <> X_Rowid
OR X_Rowid is null)
and pbv.date_from = (select min(pbv2.date_from)
from per_budget_versions pbv2
where pbv2.budget_id = X_Budget_Id
and (pbv2.rowid <> X_Rowid
OR X_Rowid is null)
and pbv2.date_from > X_Date_To);
select pbv.date_to,pbv.date_from
from per_budget_versions pbv
where pbv.budget_id = X_Budget_Id
and (pbv.rowid <> X_Rowid
OR X_Rowid is null)
and pbv.date_to = (select max(pbv2.date_to)
from per_budget_versions pbv2
where pbv2.budget_id = X_Budget_Id
and (pbv2.rowid <> X_Rowid
OR X_Rowid is null)
and pbv2.date_from < X_Date_From);
select pbv.date_to,pbv.date_from
from per_budget_versions pbv
where pbv.budget_id = X_Budget_Id
and (pbv.rowid <> X_Rowid
OR X_Rowid is null)
and pbv.date_from = (select min(pbv2.date_from)
from per_budget_versions pbv2
where pbv2.budget_id = X_Budget_Id
and (pbv2.rowid <> X_Rowid
OR X_Rowid is null)
and pbv2.date_from > X_Date_From);
PROCEDURE Update_Versions(X_Budget_Id NUMBER
,X_Rowid VARCHAR2
,X_Date_From DATE
,X_Date_To IN OUT NOCOPY DATE) is
--
CURSOR C is select pbv1.date_from, pbv1.date_to
from per_budget_versions pbv1
where pbv1.budget_id = X_Budget_Id
and (pbv1.rowid <> X_Rowid
OR X_Rowid is NULL)
and pbv1.date_from = (select max(pbv2.date_from)
from per_budget_versions pbv2
where pbv2.date_from < X_Date_From
and pbv2.budget_id = X_Budget_Id
and (pbv2.rowid <> X_Rowid
OR X_Rowid is NULL))
FOR UPDATE;
CURSOR C2 is select pbv1.date_from, pbv1.date_to
from per_budget_versions pbv1
where pbv1.budget_id = X_Budget_Id
and (pbv1.rowid <> X_Rowid
OR X_Rowid is NULL)
and pbv1.date_from = (select min(pbv2.date_from)
from per_budget_versions pbv2
where pbv2.date_from > X_Date_From
and pbv2.budget_id = X_Budget_Id
and (pbv2.rowid <> X_Rowid
OR X_Rowid is NULL))
FOR UPDATE;
l_proc VARCHAR2(72) := g_package||'Update_Versions';
UPDATE per_budget_versions
SET date_to = X_Date_From - 1
WHERE current of C;
End Update_Versions;
Update_Versions(X_Budget_Id,X_Rowid,X_Date_From,X_Date_To);
SELECT per_budget_versions_s.nextval
INTO X_Budget_Version_Id
FROM dual;
SELECT null
FROM per_budget_versions pbv
WHERE pbv.budget_version_id = x_budget_version_id
AND (pbv.rowid <> X_ROWID or X_Rowid IS NULL);
SELECT NULL
INTO l_result
FROM per_budget_versions bver
WHERE UPPER(X_Version_Number) = UPPER(bver.Version_number)
AND X_Business_group_Id = bver.Business_Group_Id
AND X_Budget_Id = bver.Budget_Id
AND (bver.Rowid <> X_Rowid or X_Rowid is null);
/*CURSOR C IS SELECT MAX(DATE_FROM + 1)
FROM PER_BUDGET_VERSIONS
WHERE BUDGET_ID = X_Budget_Id
AND DATE_TO IS NULL;*/
CURSOR C is select pbv1.date_from,pbv1.date_to
from per_budget_versions pbv1
where pbv1.budget_id = X_Budget_Id
and pbv1.date_from = (select max(pbv2.date_from)
from per_budget_versions pbv2
where pbv2.budget_id = X_Budget_Id);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2
,X_Budget_version_id IN OUT NOCOPY NUMBER
,X_Business_group_id NUMBER
,X_Budget_id NUMBER
,X_Date_from DATE
,X_Version_number VARCHAR2
,X_Comments VARCHAR2
,X_Date_to DATE
,X_Request_id NUMBER
,X_Program_application_id NUMBER
,X_Program_id NUMBER
,X_Program_update_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
,X_Attribute16 VARCHAR2
,X_Attribute17 VARCHAR2
,X_Attribute18 VARCHAR2
,X_Attribute19 VARCHAR2
,X_Attribute20 VARCHAR2
) IS
CURSOR C1 IS SELECT rowid FROM PER_BUDGET_VERSIONS
WHERE budget_version_id = X_budget_version_id;
l_proc VARCHAR2(72) := g_package||'Insert_Row';
INSERT INTO PER_BUDGET_VERSIONS(budget_version_id
,business_group_id
,budget_id
,date_from
,version_number
,comments
,date_to
,request_id
,program_application_id
,program_id
,program_update_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
) VALUES (
X_Budget_Version_Id
,X_Business_Group_Id
,X_Budget_Id
,X_Date_from
,X_Version_number
,X_Comments
,X_Date_to
,X_Request_id
,X_Program_application_id
,X_Program_id
,X_Program_update_date
,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_Attribute16
,X_Attribute17
,X_Attribute18
,X_Attribute19
,X_Attribute20);
END Insert_Row;
,X_Program_update_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
,X_Attribute16 VARCHAR2
,X_Attribute17 VARCHAR2
,X_Attribute18 VARCHAR2
,X_Attribute19 VARCHAR2
,X_Attribute20 VARCHAR2) IS
CURSOR C IS
SELECT *
FROM PER_BUDGET_VERSIONS
WHERE rowid = X_Rowid
FOR UPDATE of budget_version_id NOWAIT;
Recinfo.Program_update_date := rtrim(Recinfo.Program_update_date);
AND ( (Recinfo.Program_update_date = X_Program_update_date)
OR ( (Recinfo.Program_update_date IS NULL)
AND (X_Program_update_date IS NULL)))
AND ( (Recinfo.attribute_category = X_Attribute_Category)
OR ( (Recinfo.attribute_category IS NULL)
AND (X_Attribute_Category IS NULL)))
AND ( (Recinfo.attribute1 = X_Attribute1)
OR ( (Recinfo.attribute1 IS NULL)
AND (X_Attribute1 IS NULL)))
AND ( (Recinfo.attribute2 = X_Attribute2)
OR ( (Recinfo.attribute2 IS NULL)
AND (X_Attribute2 IS NULL)))
AND ( (Recinfo.attribute3 = X_Attribute3)
OR ( (Recinfo.attribute3 IS NULL)
AND (X_Attribute3 IS NULL)))
AND ( (Recinfo.attribute4 = X_Attribute4)
OR ( (Recinfo.attribute4 IS NULL)
AND (X_Attribute4 IS NULL)))
AND ( (Recinfo.attribute5 = X_Attribute5)
OR ( (Recinfo.attribute5 IS NULL)
AND (X_Attribute5 IS NULL)))
AND ( (Recinfo.attribute6 = X_Attribute6)
OR ( (Recinfo.attribute6 IS NULL)
AND (X_Attribute6 IS NULL)))
AND ( (Recinfo.attribute7 = X_Attribute7)
OR ( (Recinfo.attribute7 IS NULL)
AND (X_Attribute7 IS NULL)))
AND ( (Recinfo.attribute8 = X_Attribute8)
OR ( (Recinfo.attribute8 IS NULL)
AND (X_Attribute8 IS NULL)))
AND ( (Recinfo.attribute9 = X_Attribute9)
OR ( (Recinfo.attribute9 IS NULL)
AND (X_Attribute9 IS NULL)))
AND ( (Recinfo.attribute10 = X_Attribute10)
OR ( (Recinfo.attribute10 IS NULL)
AND (X_Attribute10 IS NULL)))
AND ( (Recinfo.attribute11 = X_Attribute11)
OR ( (Recinfo.attribute11 IS NULL)
AND (X_Attribute11 IS NULL)))
AND ( (Recinfo.attribute12 = X_Attribute12)
OR ( (Recinfo.attribute12 IS NULL)
AND (X_Attribute12 IS NULL)))
AND ( (Recinfo.attribute13 = X_Attribute13)
OR ( (Recinfo.attribute13 IS NULL)
AND (X_Attribute13 IS NULL)))
AND ( (Recinfo.attribute14 = X_Attribute14)
OR ( (Recinfo.attribute14 IS NULL)
AND (X_Attribute14 IS NULL)))
AND ( (Recinfo.attribute15 = X_Attribute15)
OR ( (Recinfo.attribute15 IS NULL)
AND (X_Attribute15 IS NULL)))
AND ( (Recinfo.attribute16 = X_Attribute16)
OR ( (Recinfo.attribute16 IS NULL)
AND (X_Attribute16 IS NULL)))
AND ( (Recinfo.attribute17 = X_Attribute17)
OR ( (Recinfo.attribute17 IS NULL)
AND (X_Attribute17 IS NULL)))
AND ( (Recinfo.attribute18 = X_Attribute18)
OR ( (Recinfo.attribute18 IS NULL)
AND (X_Attribute18 IS NULL)))
AND ( (Recinfo.attribute19 = X_Attribute19)
OR ( (Recinfo.attribute19 IS NULL)
AND (X_Attribute19 IS NULL)))
AND ( (Recinfo.attribute20 = X_Attribute20)
OR ( (Recinfo.attribute20 IS NULL)
AND (X_Attribute20 IS NULL)))
) then
return;
PROCEDURE Update_Row(X_Rowid VARCHAR2
,X_Budget_Version_id NUMBER
,X_Business_Group_Id NUMBER
,X_Budget_Id NUMBER
,X_Date_from DATE
,X_Version_number VARCHAR2
,X_Comments VARCHAR2
,X_Date_to DATE
,X_Request_id NUMBER
,X_Program_application_id NUMBER
,X_Program_id NUMBER
,X_Program_update_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
,X_Attribute16 VARCHAR2
,X_Attribute17 VARCHAR2
,X_Attribute18 VARCHAR2
,X_Attribute19 VARCHAR2
,X_Attribute20 VARCHAR2) IS
l_proc VARCHAR2(72) := g_package||'Update_Row';
UPDATE PER_BUDGET_VERSIONS
SET
budget_version_id = X_Budget_Version_id
,business_group_id = X_Business_Group_Id
,budget_id = X_Budget_Id
,date_from = X_Date_from
,version_number = X_Version_number
,comments = X_Comments
,date_to = X_Date_to
,request_id = X_Request_id
,program_application_id = X_Program_application_id
,program_id = X_Program_id
,program_update_date = X_Program_update_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
,attribute16 = X_Attribute16
,attribute17 = X_Attribute17
,attribute18 = X_Attribute18
,attribute19 = X_Attribute19
,attribute20 = X_Attribute20
WHERE rowid = X_rowid;
hr_utility.set_message_token('PROCEDURE','Update_Row');
END Update_Row;