The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update Group Org
Delete Group Org
Calls to these procedures will invoke calls to table handlers which
do actual insert, update and delete into tables.
******************************************************************************************/
--Package variables.
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_GROUP_ORGS_PVT';
SELECT start_date_active,
NVL(end_date_active, TRUNC(SYSDATE)+1),
group_name
FROM jtf_rs_groups_vl
WHERE group_id = ll_group_id
;
SELECT hr.date_from,
NVL(hr.date_to, TRUNC(SYSDATE)+1),
hr.NAME
FROM hr_all_organization_units_vl hr
WHERE hr.organization_id = ll_org_id
;
SELECT 'Y'
FROM jtf_rs_group_organizations
WHERE group_id = ll_group_id
AND organization_id = ll_org_id
;
/* Insert the row into the table */
INSERT INTO jtf_rs_group_organizations (
GROUP_ID,
ORGANIZATION_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY
) VALUES (
P_GROUP_ID,
P_ORGANIZATION_ID,
1,
SYSDATE,
jtf_resource_utl.created_by,
SYSDATE,
jtf_resource_utl.updated_by,
jtf_resource_utl.login_id,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_ATTRIBUTE4,
P_ATTRIBUTE5,
P_ATTRIBUTE6,
P_ATTRIBUTE7,
P_ATTRIBUTE8,
P_ATTRIBUTE9,
P_ATTRIBUTE10,
P_ATTRIBUTE11,
P_ATTRIBUTE12,
P_ATTRIBUTE13,
P_ATTRIBUTE14,
P_ATTRIBUTE15,
P_ATTRIBUTE_CATEGORY
);
/* Procedure to update the resource group - HR Org mapping
*/
PROCEDURE update_group_org
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE,
P_ORGANIZATION_ID IN JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE,
P_ATTRIBUTE1 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUP_ORGANIZATIONS.ATTRIBUTE_CATEGORY%TYPE,
P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_GROUP_ORGANIZATIONS.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GROUP_ORG';
CURSOR c_group_org_update(ll_group_id IN NUMBER,
ll_organization_id IN NUMBER) IS
SELECT
group_id l_group_id,
organization_id l_organization_id,
object_version_number l_object_version_number,
DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
DECODE(p_attribute_category,fnd_api.g_miss_char, attribute_category, p_attribute_category) l_attribute_category
FROM jtf_rs_group_organizations
WHERE group_id = ll_group_id
AND organization_id = ll_organization_id;
group_org_rec c_group_org_update%ROWTYPE;
SAVEPOINT sp_update_group_org;
OPEN c_group_org_update(l_group_id, l_organization_id);
FETCH c_group_org_update INTO group_org_rec;
IF c_group_org_update%NOTFOUND THEN
CLOSE c_group_org_update;
CLOSE c_group_org_update;
UPDATE jtf_rs_group_organizations SET
GROUP_ID = group_org_rec.l_group_id,
ORGANIZATION_ID = group_org_rec.l_organization_id,
OBJECT_VERSION_NUMBER = l_object_version_number,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = jtf_resource_utl.updated_by,
LAST_UPDATE_LOGIN = jtf_resource_utl.login_id,
ATTRIBUTE1 = group_org_rec.l_attribute1,
ATTRIBUTE2 = group_org_rec.l_attribute2,
ATTRIBUTE3 = group_org_rec.l_attribute3,
ATTRIBUTE4 = group_org_rec.l_attribute4,
ATTRIBUTE5 = group_org_rec.l_attribute5,
ATTRIBUTE6 = group_org_rec.l_attribute6,
ATTRIBUTE7 = group_org_rec.l_attribute7,
ATTRIBUTE8 = group_org_rec.l_attribute8,
ATTRIBUTE9 = group_org_rec.l_attribute9,
ATTRIBUTE10 = group_org_rec.l_attribute10,
ATTRIBUTE11 = group_org_rec.l_attribute11,
ATTRIBUTE12 = group_org_rec.l_attribute12,
ATTRIBUTE13 = group_org_rec.l_attribute13,
ATTRIBUTE14 = group_org_rec.l_attribute14,
ATTRIBUTE15 = group_org_rec.l_attribute15,
ATTRIBUTE_CATEGORY = group_org_rec.l_attribute_category
WHERE GROUP_ID = l_group_id
AND ORGANIZATION_ID = l_organization_id;
ROLLBACK TO sp_update_group_org;
ROLLBACK TO sp_update_group_org;
ROLLBACK TO sp_update_group_org;
END update_group_org;
/* Procedure to delete resource group - HR Org mapping
*/
PROCEDURE delete_group_org
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN JTF_RS_GROUP_ORGANIZATIONS.GROUP_ID%TYPE,
P_ORGANIZATION_ID IN JTF_RS_GROUP_ORGANIZATIONS.ORGANIZATION_ID%TYPE,
P_OBJECT_VERSION_NUMBER IN JTF_RS_GROUP_ORGANIZATIONS.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP_ORG';
SELECT object_version_number
FROM jtf_rs_group_organizations
WHERE group_id = ll_group_id
AND organization_id = ll_organization_id
;
SAVEPOINT sp_delete_group_org;
/* delete table data*/
DELETE FROM jtf_rs_group_organizations
WHERE GROUP_ID = P_GROUP_ID
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
ROLLBACK TO sp_delete_group_org;
ROLLBACK TO sp_delete_group_org;
ROLLBACK TO sp_delete_group_org;
END delete_group_org;