[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure Update_Hierarchy_Header(
p_api_version IN NUMBER DEFAULT 1,
p_name IN VARCHAR2,
p_new_name IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_purpose_code IN VARCHAR2 DEFAULT NULL,
p_start_date IN DATE DEFAULT NULL,
p_end_date IN DATE DEFAULT NULL,
p_nullify_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
v_hier_id NUMBER;
SAVEPOINT Update_Hierarchy_Header;
SELECT SITE_GROUP_ID
INTO v_hier_id
FROM RRS_SITE_GROUPS_VL
WHERE NAME = p_name;
SELECT MEANING
INTO v_meaning
FROM RRS_LOOKUPS_V
WHERE LOOKUP_CODE = p_purpose_code
AND LOOKUP_TYPE = 'RRS_HIERARCHY_PURPOSE'
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(start_date_active, sysdate) <= sysdate
AND nvl(end_date_active, sysdate) >= sysdate;
SELECT count(*)
INTO v_count
FROM RRS_SITE_GROUPS_TL
WHERE NAME = p_new_name
AND SITE_GROUP_ID <> v_hier_id;
SELECT START_DATE
INTO v_start_date
FROM RRS_SITE_GROUPS_B
WHERE SITE_GROUP_ID = v_hier_id;
SELECT END_DATE
INTO v_end_date
FROM RRS_SITE_GROUPS_B
WHERE SITE_GROUP_ID = v_hier_id;
UPDATE RRS_SITE_GROUPS_B RSGB
SET RSGB.START_DATE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_start_date, NVL(p_start_date, RSGB.START_DATE)),
RSGB.END_DATE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_end_date, NVL(p_end_date, RSGB.END_DATE)),
RSGB.GROUP_PURPOSE_CODE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_purpose_code, NVL(p_purpose_code, RSGB.GROUP_PURPOSE_CODE))
WHERE RSGB.SITE_GROUP_ID = v_hier_id;
UPDATE RRS_SITE_GROUPS_TL RSGT
SET RSGT.DESCRIPTION = DECODE(p_nullify_flag, FND_API.G_TRUE, p_description, NVL(p_description, RSGT.DESCRIPTION)),
RSGT.NAME = NVL(p_new_name, RSGT.NAME),
RSGT.SOURCE_LANG = userenv('LANG')
WHERE RSGT.SITE_GROUP_ID = v_hier_id
AND RSGT.LANGUAGE = userenv('LANG');
RAISE e_update_failed;
SELECT CHILD_MEMBER_ID
INTO v_root_id
FROM RRS_SITE_GROUP_MEMBERS
WHERE SITE_GROUP_ID = v_hier_id
AND PARENT_MEMBER_ID = -1;
UPDATE RRS_SITE_GROUP_NODES_TL RSGNT
SET RSGNT.NAME = NVL(p_new_name, RSGNT.NAME),
RSGNT.SOURCE_LANG = userenv('LANG')
WHERE RSGNT.SITE_GROUP_NODE_ID = v_root_id
AND RSGNT.LANGUAGE = userenv('LANG');
RAISE e_update_failed;
ROLLBACK TO Update_Hierarchy_Header;
ROLLBACK TO Update_Hierarchy_Header;
x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Header:' || SQLERRM;
END Update_Hierarchy_Header;
procedure Update_Hierarchy_Node(
p_api_version IN NUMBER DEFAULT 1,
p_number IN VARCHAR2,
p_name IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_purpose_code IN VARCHAR2 DEFAULT NULL,
p_nullify_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
v_id NUMBER;
SAVEPOINT Update_Hierarchy_Node;
SELECT SITE_GROUP_NODE_ID, NODE_PURPOSE_CODE
INTO v_id, v_purpose_code
FROM RRS_SITE_GROUP_NODES_VL RSGNV
WHERE RSGNV.NODE_IDENTIFICATION_NUMBER = p_number;
SELECT MEANING
INTO v_meaning
FROM RRS_LOOKUPS_V RLV
WHERE RLV.LOOKUP_CODE = p_purpose_code
AND RLV.LOOKUP_TYPE = 'RRS_NODE_PURPOSE'
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(start_date_active, sysdate) <= sysdate
AND nvl(end_date_active, sysdate) >= sysdate;
UPDATE RRS_SITE_GROUP_NODES_TL RSGNT
SET RSGNT.DESCRIPTION = DECODE(p_nullify_flag, FND_API.G_TRUE, p_description, NVL(p_description, RSGNT.DESCRIPTION)),
RSGNT.NAME = NVL(p_name, RSGNT.NAME),
RSGNT.SOURCE_LANG = userenv('LANG')
WHERE RSGNT.SITE_GROUP_NODE_ID = v_id
AND RSGNT.LANGUAGE = userenv('LANG');
RAISE e_update_failed;
UPDATE RRS_SITE_GROUP_NODES_B RSGNB
SET RSGNB.NODE_PURPOSE_CODE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_purpose_code, NVL(p_purpose_code, RSGNB.NODE_PURPOSE_CODE))
WHERE RSGNB.SITE_GROUP_NODE_ID = v_id;
RAISE e_update_failed;
ROLLBACK TO Update_Hierarchy_Node;
ROLLBACK TO Update_Hierarchy_Node;
x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Node:' || SQLERRM;
END Update_Hierarchy_Node;
SELECT count(*)
INTO v_count
FROM RRS_SITE_GROUP_NODES_VL RSGNV
WHERE RSGNV.NODE_IDENTIFICATION_NUMBER = p_number;
SELECT MEANING
INTO v_meaning
FROM RRS_LOOKUPS_V RLV
WHERE RLV.LOOKUP_CODE = p_purpose_code
AND RLV.LOOKUP_TYPE = 'RRS_NODE_PURPOSE'
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(start_date_active, sysdate) <= sysdate
AND nvl(end_date_active, sysdate) >= sysdate;
SELECT RRS_SITES_S.NEXTVAL
INTO v_id
From dual;
INSERT INTO RRS_SITE_GROUP_NODES_TL
(
SITE_GROUP_NODE_ID,
LANGUAGE,
SOURCE_LANG,
NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT v_id,
L.LANGUAGE_CODE,
userenv('LANG'),
p_name,
p_description,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
RAISE e_insert_failed;
INSERT INTO RRS_SITE_GROUP_NODES_B
(
SITE_GROUP_NODE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
NODE_PURPOSE_CODE,
NODE_IDENTIFICATION_NUMBER
)
VALUES( v_id,
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
p_purpose_code,
p_number);
RAISE e_insert_failed;
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUPS_TL
WHERE NAME = p_hier_name;
SELECT MEANING
INTO v_meaning
FROM RRS_LOOKUPS_V RLV
WHERE RLV.LOOKUP_CODE = p_hier_purpose_code
AND RLV.LOOKUP_TYPE = 'RRS_HIERARCHY_PURPOSE'
AND nvl(enabled_flag, 'Y') = 'Y'
AND nvl(start_date_active, sysdate) <= sysdate
AND nvl(end_date_active, sysdate) >= sysdate;
SELECT RRS_SITE_GROUPS_S.NEXTVAL
INTO v_hier_id
From dual;
INSERT INTO RRS_SITE_GROUPS_TL
(
SITE_GROUP_ID,
LANGUAGE,
SOURCE_LANG,
NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT v_hier_id,
L.LANGUAGE_CODE,
userenv('LANG'),
p_hier_name,
p_hier_description,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
RAISE e_insert_failed;
INSERT INTO RRS_SITE_GROUPS_B
(
SITE_GROUP_ID,
SITE_GROUP_TYPE_CODE,
START_DATE,
END_DATE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
GROUP_PURPOSE_CODE
)
VALUES( v_hier_id,
'H',
p_hier_start_date,
p_hier_end_date,
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
p_hier_purpose_code);
RAISE e_insert_failed;
SELECT RRS_SITE_GROUP_VERSIONS_S.NEXTVAL
INTO v_hier_version_id
From dual;
INSERT INTO RRS_SITE_GROUP_VERSIONS
(
SITE_GROUP_VERSION_ID,
SITE_GROUP_ID,
VERSION_NUMBER,
SOURCE_VERSION_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES( v_hier_version_id,
v_hier_id,
1,
v_hier_version_id,
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id);
RAISE e_insert_failed;
SELECT RRS_SITES_S.NEXTVAL
INTO v_root_id
From dual;
INSERT INTO RRS_SITE_GROUP_NODES_TL
(
SITE_GROUP_NODE_ID,
LANGUAGE,
SOURCE_LANG,
NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT v_root_id,
L.LANGUAGE_CODE,
userenv('LANG'),
p_hier_name,
NULL,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
RAISE e_insert_failed;
INSERT INTO RRS_SITE_GROUP_NODES_B
(
SITE_GROUP_NODE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
NODE_PURPOSE_CODE,
NODE_IDENTIFICATION_NUMBER
)
VALUES( v_root_id,
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
'ROOT',
v_root_id);
RAISE e_insert_failed;
SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE CHILD_ID NOT IN (
SELECT CHILD_ID
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
AND RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code
AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
AND RSGMT.PARENT_TYPE = RGR.OBJECT1
AND RSGMT.PARENT_PURPOSE_CODE = RGR.CLASSIFICATION_CODE1
AND RSGMT.CHILD_TYPE = RGR.OBJECT2
AND RSGMT.CHILD_PURPOSE_CODE = RGR.CLASSIFICATION_CODE2);
SELECT COUNT(*)
INTO v_count
FROM (
SELECT *
FROM RRS_GROUP_RULES RGR
WHERE RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code) TMP;
SELECT COUNT(*)
INTO v_count
FROM (
SELECT *
FROM RRS_GROUP_RULES RGR
WHERE RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code) TMP;
SELECT COUNT(*)
INTO v_count
FROM RRS_GROUP_RULES RGR
WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
AND RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code
AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
AND RGR.OBJECT1 = p_parent_object_type
AND RGR.CLASSIFICATION_CODE1 = p_parent_purpose_code
AND RGR.OBJECT2 = p_child_object_type
AND RGR.CLASSIFICATION_CODE2 = p_child_purpose_code;
SELECT *
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE CHILD_TYPE = 'NODE'
AND CHILD_ID IS NULL
AND CHILD_NUMBER IS NOT NULL
AND CHILD_NUMBER NOT IN (
SELECT NODE_IDENTIFICATION_NUMBER
FROM RRS_SITE_GROUP_NODES_VL
WHERE NODE_IDENTIFICATION_NUMBER IS NOT NULL);
CURSOR update_nodes_cursor IS
SELECT *
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE CHILD_TYPE = 'NODE'
AND CHILD_NUMBER IS NOT NULL
AND (CHILD_PURPOSE_CODE IS NOT NULL
OR CHILD_NODE_NAME IS NOT NULL
OR CHILD_NODE_DESCRIPTION IS NOT NULL);
SELECT *
FROM RRS_SITE_GROUP_MEMBERS_TEMP;
SELECT PARENT_ID, PARENT_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE (PARENT_ID IS NOT NULL AND PARENT_NUMBER IS NOT NULL)
AND ((PARENT_ID, PARENT_NUMBER) NOT IN (SELECT SITE_ID, SITE_IDENTIFICATION_NUMBER FROM RRS_SITES_VL)
AND (PARENT_ID, PARENT_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
SELECT CHILD_ID, CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE (CHILD_ID IS NOT NULL AND CHILD_NUMBER IS NOT NULL)
AND ((CHILD_ID, CHILD_NUMBER) NOT IN (SELECT SITE_ID, SITE_IDENTIFICATION_NUMBER FROM RRS_SITES_VL)
AND (CHILD_ID, CHILD_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
SELECT PARENT_TYPE, PARENT_ID, PARENT_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
WHERE PARENT_ID IS NOT NULL
AND ((PARENT_TYPE = 'SITE'
AND NOT EXISTS (
SELECT SITE_ID
FROM RRS_SITES_B
WHERE SITE_ID = RSGMT.PARENT_ID))
OR (PARENT_TYPE = 'NODE'
AND NOT EXISTS (
SELECT SITE_GROUP_NODE_ID
FROM RRS_SITE_GROUP_NODES_B
WHERE SITE_GROUP_NODE_ID = RSGMT.PARENT_ID)));
SELECT CHILD_TYPE, CHILD_ID, CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
WHERE CHILD_ID IS NOT NULL
AND ((CHILD_TYPE = 'SITE'
AND NOT EXISTS (
SELECT SITE_ID
FROM RRS_SITES_B
WHERE SITE_ID = RSGMT.CHILD_ID))
OR (CHILD_TYPE = 'NODE'
AND NOT EXISTS (
SELECT SITE_GROUP_NODE_ID
FROM RRS_SITE_GROUP_NODES_B
WHERE SITE_GROUP_NODE_ID = RSGMT.CHILD_ID)));
SELECT CHILD_TYPE, CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP
GROUP BY CHILD_TYPE, CHILD_NUMBER
HAVING COUNT(*) > 1;
SELECT DECODE(PARENT_ID, NULL, PARENT_NUMBER, CHILD_NUMBER), DECODE(PARENT_ID, NULL, PARENT_TYPE, CHILD_TYPE)
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE CHILD_ID IS NULL
OR PARENT_ID IS NULL;
SELECT CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITES_VL RSV
WHERE RSGMT.CHILD_ID = RSV.SITE_ID
AND RSGMT.CHILD_TYPE = 'SITE'
AND IS_TEMPLATE_FLAG = 'Y';
SELECT RSGNV.NAME
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITE_GROUP_NODES_VL RSGNV
WHERE RSGMT.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID
GROUP BY RSGMT.PARENT_ID, RSGNV.NAME
HAVING COUNT(*) > 1;
SELECT RSGMT.CHILD_TYPE, RSGMT.CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
WHERE RSGMT.PARENT_ID <> p_root_id
AND (RSGMT.PARENT_TYPE, RSGMT.PARENT_NUMBER) NOT IN
(SELECT RSGMT2.CHILD_TYPE, RSGMT2.CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
START WITH RSGMT2.PARENT_ID = p_root_id
CONNECT BY PRIOR RSGMT2.CHILD_ID = RSGMT2.PARENT_ID);
INSERT INTO RRS_SITE_GROUP_MEMBERS
(
SITE_GROUP_VERSION_ID,
SITE_GROUP_ID,
PARENT_MEMBER_ID,
CHILD_MEMBER_ID,
DELETED_FLAG,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SEQUENCE_NUMBER
)
VALUES( p_hier_version_id,
p_hier_id,
-1,
p_root_id,
'N',
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
null);
RAISE e_insert_failed;
DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
(
PARENT_TYPE,
PARENT_ID,
PARENT_NUMBER,
PARENT_PURPOSE_CODE,
CHILD_TYPE,
CHILD_ID,
CHILD_NUMBER,
CHILD_PURPOSE_CODE,
SEQUENCE_NUMBER,
CHILD_NODE_NAME,
CHILD_NODE_DESCRIPTION
)
VALUES( p_hier_members_tab(i).parent_object_type,
p_hier_members_tab(i).parent_id,
p_hier_members_tab(i).parent_id_number,
NULL,
p_hier_members_tab(i).child_object_type,
p_hier_members_tab(i).child_id,
p_hier_members_tab(i).child_id_number,
p_hier_members_tab(i).child_node_purpose_code,
p_hier_members_tab(i).child_seq_number,
p_hier_members_tab(i).child_node_name,
p_hier_members_tab(i).child_node_description
);
RAISE e_insert_failed;
SELECT RSGMT.CHILD_ID, RSGMT.CHILD_NUMBER
INTO v_root_id, v_root_number
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
WHERE (RSGMT.PARENT_ID = -1 OR RSGMT.PARENT_TYPE = 'NONE')
AND (RSGMT.CHILD_ID IS NOT NULL OR RSGMT.CHILD_NUMBER IS NOT NULL)
AND RSGMT.CHILD_TYPE = 'NODE';
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUP_NODES_VL
WHERE NODE_IDENTIFICATION_NUMBER = v_root_number
AND SITE_GROUP_NODE_ID <> p_root_id;
UPDATE RRS_SITE_GROUP_NODES_B
SET NODE_IDENTIFICATION_NUMBER = v_root_number
WHERE SITE_GROUP_NODE_ID = p_root_id;
DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE (PARENT_ID = -1 OR PARENT_TYPE = 'NONE')
AND (CHILD_ID IS NOT NULL OR CHILD_NUMBER IS NOT NULL)
AND CHILD_TYPE = 'NODE';
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE (PARENT_ID IS NULL AND PARENT_NUMBER IS NULL)
OR (CHILD_ID IS NULL AND CHILD_NUMBER IS NULL);
UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
SET PARENT_ID = (
SELECT DECODE(PARENT_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
WHERE RSGMT2.PARENT_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)
AND RSGMT2.PARENT_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
AND RSGMT.ROWID = RSGMT2.ROWID)
WHERE PARENT_ID IS NULL;
UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
SET PARENT_NUMBER = (
SELECT DECODE(PARENT_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
WHERE RSGMT2.PARENT_ID = RSV.SITE_ID(+)
AND RSGMT2.PARENT_ID = RSGNV.SITE_GROUP_NODE_ID(+)
AND RSGMT.ROWID = RSGMT2.ROWID)
WHERE PARENT_NUMBER IS NULL;
UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
SET CHILD_ID = (
SELECT DECODE(CHILD_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
WHERE RSGMT2.CHILD_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)
AND RSGMT2.CHILD_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
AND RSGMT.ROWID = RSGMT2.ROWID)
WHERE CHILD_ID IS NULL;
UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
SET CHILD_NUMBER = (
SELECT DECODE(CHILD_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
WHERE RSGMT2.CHILD_ID = RSV.SITE_ID(+)
AND RSGMT2.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID(+)
AND RSGMT.ROWID = RSGMT2.ROWID)
WHERE CHILD_NUMBER IS NULL;
FOR node_rec IN update_nodes_cursor LOOP
--dbms_output.put_line('Inside update nodes');
Update_Hierarchy_Node(
p_number => node_rec.CHILD_NUMBER,
p_name => node_rec.CHILD_NODE_NAME,
p_purpose_code => node_rec.CHILD_PURPOSE_CODE,
p_description => node_rec.CHILD_NODE_DESCRIPTION,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
SET PARENT_PURPOSE_CODE = (
SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
WHERE RSGMT2.PARENT_ID = RSU.SITE_ID(+)
AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
AND RSGMT2.PARENT_ID = RSGNV.SITE_GROUP_NODE_ID(+)
AND RSGMT.ROWID = RSGMT2.ROWID);
UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
SET CHILD_PURPOSE_CODE = (
SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
WHERE RSGMT2.CHILD_ID = RSU.SITE_ID(+)
AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
AND RSGMT2.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID(+)
AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
AND RSGMT.ROWID = RSGMT2.ROWID);
INSERT INTO RRS_SITE_GROUP_MEMBERS
(
SITE_GROUP_VERSION_ID,
SITE_GROUP_ID,
PARENT_MEMBER_ID,
CHILD_MEMBER_ID,
DELETED_FLAG,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SEQUENCE_NUMBER
)
VALUES( p_hier_version_id,
p_hier_id,
mem_rec.PARENT_ID,
mem_rec.CHILD_ID,
'N',
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
mem_rec.SEQUENCE_NUMBER);
procedure Update_Hierarchy_Coarse(
p_api_version IN NUMBER DEFAULT 1,
p_hier_name IN VARCHAR2,
p_hier_new_name IN VARCHAR2 DEFAULT NULL,
p_hier_description IN VARCHAR2 DEFAULT NULL,
p_hier_purpose_code IN VARCHAR2 DEFAULT NULL,
p_hier_start_date IN DATE DEFAULT NULL,
p_hier_end_date IN DATE DEFAULT NULL,
p_hier_members_tab IN RRS_HIER_MEMBERS_COARSE_TAB DEFAULT NULL,
p_nullify_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
v_count NUMBER;
SAVEPOINT Update_Hierarchy_Coarse;
SELECT SITE_GROUP_ID
INTO v_hier_id
FROM RRS_SITE_GROUPS_VL RSGV
WHERE RSGV.NAME = p_hier_name;
SELECT RSGM.CHILD_MEMBER_ID, RSGNV.NODE_IDENTIFICATION_NUMBER
INTO v_root_id, v_root_number
FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV
WHERE RSGM.SITE_GROUP_ID = v_hier_id
AND RSGM.PARENT_MEMBER_ID = -1
AND RSGM.CHILD_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID
AND RSGNV.NODE_PURPOSE_CODE = 'ROOT';
SELECT SITE_GROUP_VERSION_ID
INTO v_hier_version_id
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = v_hier_id;
SELECT GROUP_PURPOSE_CODE
INTO v_purpose_code
FROM RRS_SITE_GROUPS_B RSGB
WHERE RSGB.SITE_GROUP_ID = v_hier_id;
Update_Hierarchy_Header(
p_name => p_hier_name,
p_new_name => p_hier_new_name,
p_description => p_hier_description,
p_purpose_code => v_purpose_code,
p_start_date => p_hier_start_date,
p_end_date => p_hier_end_date,
p_nullify_flag => p_nullify_flag,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
DELETE FROM RRS_SITE_GROUP_MEMBERS
WHERE SITE_GROUP_ID = v_hier_id;
ROLLBACK TO Update_Hierarchy_Coarse;
ROLLBACK TO Update_Hierarchy_Coarse;
x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Coarse:' || x_msg_data;
ROLLBACK TO Update_Hierarchy_Coarse;
x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Coarse:' || SQLERRM;
END Update_Hierarchy_Coarse;
procedure Update_Hierarchy_Fine(
p_api_version IN NUMBER DEFAULT 1,
p_hier_members_rec IN RRS_HIER_MEMBERS_FINE_REC,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)IS
v_source_hier_id NUMBER;
SELECT DECODE(RSV.SITE_ID, NULL, 'NODE', 'SITE') AS P_TYPE,
RSGM.PARENT_MEMBER_ID AS P_ID,
NVL(RSV.SITE_IDENTIFICATION_NUMBER, RSGNV.NODE_IDENTIFICATION_NUMBER) AS P_NUMBER,
DECODE(RSV.SITE_ID, NULL, RSGNV.NODE_PURPOSE_CODE, RSU.SITE_USE_TYPE_CODE) AS P_PURPOSE_CODE,
DECODE(RSV2.SITE_ID, NULL, 'NODE', 'SITE') AS C_TYPE,
RSGM.CHILD_MEMBER_ID AS C_ID,
NVL(RSV2.SITE_IDENTIFICATION_NUMBER, RSGNV2.NODE_IDENTIFICATION_NUMBER) AS C_NUMBER,
DECODE(RSV2.SITE_ID, NULL, RSGNV2.NODE_PURPOSE_CODE, RSU2.SITE_USE_TYPE_CODE) AS C_PURPOSE_CODE,
RSGM.SEQUENCE_NUMBER AS C_SEQ_NUMBER
FROM RRS_SITE_GROUP_MEMBERS RSGM,
RRS_SITE_GROUP_NODES_VL RSGNV,
RRS_SITES_VL RSV, RRS_SITE_USES RSU,
RRS_SITE_GROUP_NODES_VL RSGNV2,
RRS_SITES_VL RSV2,
RRS_SITE_USES RSU2
WHERE RSGM.PARENT_MEMBER_ID = RSV.SITE_ID(+)
AND RSGM.PARENT_MEMBER_ID = RSU.SITE_ID(+)
AND NVL(RSU.IS_PRIMARY_FLAG, 'Y') = 'Y'
AND RSGM.PARENT_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID(+)
AND RSGM.CHILD_MEMBER_ID = RSV2.SITE_ID(+)
AND RSGM.CHILD_MEMBER_ID = RSU2.SITE_ID(+)
AND NVL(RSU2.IS_PRIMARY_FLAG, 'Y') = 'Y'
AND RSGM.CHILD_MEMBER_ID = RSGNV2.SITE_GROUP_NODE_ID(+)
START WITH CHILD_MEMBER_ID = v_child_id
AND SITE_GROUP_ID = v_source_hier_id
CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
AND SITE_GROUP_ID = v_source_hier_id;
SELECT *
FROM RRS_SITE_GROUP_MEMBERS_TEMP;
SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
FROM RRS_SITE_GROUP_MEMBERS_TEMP
WHERE CHILD_ID NOT IN (
SELECT CHILD_ID
FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
AND RGR.GROUP_PURPOSE_CODE = v_dest_hier_purpose_code
AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
AND RSGMT.PARENT_TYPE = RGR.OBJECT1
AND RSGMT.PARENT_PURPOSE_CODE = RGR.CLASSIFICATION_CODE1
AND RSGMT.CHILD_TYPE = RGR.OBJECT2
AND RSGMT.CHILD_PURPOSE_CODE = RGR.CLASSIFICATION_CODE2);
SAVEPOINT Update_Hierarchy_Fine;
SELECT SITE_GROUP_ID, GROUP_PURPOSE_CODE
INTO v_dest_hier_id, v_dest_hier_purpose_code
FROM RRS_SITE_GROUPS_VL RSGV
WHERE RSGV.NAME = p_hier_members_rec.dest_hier_name;
SELECT SITE_GROUP_VERSION_ID
INTO v_dest_hier_version_id
FROM RRS_SITE_GROUP_VERSIONS
WHERE SITE_GROUP_ID = v_dest_hier_id;
SELECT SITE_GROUP_NODE_ID, NODE_PURPOSE_CODE, NAME
INTO v_child_id, v_child_purpose_code, v_child_name
FROM RRS_SITE_GROUP_NODES_VL
WHERE NODE_IDENTIFICATION_NUMBER = p_hier_members_rec.child_id_number;
SELECT RSV.SITE_ID, RSU.SITE_USE_TYPE_CODE, RSV.IS_TEMPLATE_FLAG
INTO v_child_id, v_child_purpose_code, v_flag
FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU
WHERE RSV.SITE_IDENTIFICATION_NUMBER = p_hier_members_rec.child_id_number
AND RSV.SITE_ID = RSU.SITE_ID(+)
AND RSU.IS_PRIMARY_FLAG(+) = 'Y';
SELECT SITE_GROUP_NODE_ID, NODE_PURPOSE_CODE
INTO v_dest_parent_id, v_dest_parent_purpose_code
FROM RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS RSGM
WHERE RSGNV.NODE_IDENTIFICATION_NUMBER = p_hier_members_rec.dest_parent_id_number
AND RSGNV.SITE_GROUP_NODE_ID = RSGM.CHILD_MEMBER_ID
AND RSGM.SITE_GROUP_ID = v_dest_hier_id;
SELECT RSV.SITE_ID, RSU.SITE_USE_TYPE_CODE
INTO v_dest_parent_id, v_dest_parent_purpose_code
FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU, RRS_SITE_GROUP_MEMBERS RSGM
WHERE RSV.SITE_IDENTIFICATION_NUMBER = p_hier_members_rec.dest_parent_id_number
AND RSV.SITE_ID = RSU.SITE_ID(+)
AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
AND RSV.SITE_ID = RSGM.CHILD_MEMBER_ID
AND RSGM.SITE_GROUP_ID = v_dest_hier_id;
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUP_MEMBERS
WHERE CHILD_MEMBER_ID = v_child_id
AND SITE_GROUP_ID = v_dest_hier_id;
INSERT INTO RRS_SITE_GROUP_MEMBERS
(
SITE_GROUP_VERSION_ID,
SITE_GROUP_ID,
PARENT_MEMBER_ID,
CHILD_MEMBER_ID,
DELETED_FLAG,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SEQUENCE_NUMBER
)
VALUES( v_dest_hier_version_id,
v_dest_hier_id,
v_dest_parent_id,
v_child_id,
'N',
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
p_hier_members_rec.child_seq_number);
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUP_MEMBERS
WHERE SITE_GROUP_ID = v_dest_hier_id
AND CHILD_MEMBER_ID = v_child_id;
DELETE FROM RRS_SITE_GROUP_MEMBERS
WHERE CHILD_MEMBER_ID IN (
SELECT CHILD_MEMBER_ID
FROM RRS_SITE_GROUP_MEMBERS
START WITH CHILD_MEMBER_ID = v_child_id
AND SITE_GROUP_ID = v_dest_hier_id
CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
AND SITE_GROUP_ID = v_dest_hier_id)
AND SITE_GROUP_ID = v_dest_hier_id;
SELECT SITE_GROUP_ID
INTO v_source_hier_id
FROM RRS_SITE_GROUPS_VL
WHERE NAME = p_hier_members_rec.source_hier_name;
SELECT PARENT_MEMBER_ID
INTO v_source_parent_id
FROM RRS_SITE_GROUP_MEMBERS
WHERE SITE_GROUP_ID = v_source_hier_id
AND CHILD_MEMBER_ID = v_child_id;
SELECT COUNT(*)
INTO v_count
FROM (
SELECT CHILD_MEMBER_ID
FROM RRS_SITE_GROUP_MEMBERS
START WITH CHILD_MEMBER_ID = v_child_id
AND SITE_GROUP_ID = v_source_hier_id
CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
AND SITE_GROUP_ID = v_source_hier_id) TMP
WHERE CHILD_MEMBER_ID = v_dest_parent_id;
DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUP_MEMBERS
WHERE CHILD_MEMBER_ID = rec.C_ID
AND SITE_GROUP_ID = v_dest_hier_id;
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV
WHERE RSGM.SITE_GROUP_ID = v_dest_hier_id
AND RSGM.PARENT_MEMBER_ID = v_dest_parent_id
AND RSGM.CHILD_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID
AND RSGNV.NAME = v_child_name;
INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
(
PARENT_TYPE,
PARENT_ID,
PARENT_NUMBER,
PARENT_PURPOSE_CODE,
CHILD_TYPE,
CHILD_ID,
CHILD_NUMBER,
CHILD_PURPOSE_CODE,
SEQUENCE_NUMBER,
CHILD_NODE_NAME,
CHILD_NODE_DESCRIPTION
)
VALUES( p_hier_members_rec.dest_parent_object_type,
v_dest_parent_id,
p_hier_members_rec.dest_parent_id_number,
v_dest_parent_purpose_code,
p_hier_members_rec.child_object_type,
v_child_id,
p_hier_members_rec.child_id_number,
v_child_purpose_code,
p_hier_members_rec.child_seq_number,
NULL,
NULL
);
INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
(
PARENT_TYPE,
PARENT_ID,
PARENT_NUMBER,
PARENT_PURPOSE_CODE,
CHILD_TYPE,
CHILD_ID,
CHILD_NUMBER,
CHILD_PURPOSE_CODE,
SEQUENCE_NUMBER,
CHILD_NODE_NAME,
CHILD_NODE_DESCRIPTION
)
VALUES( rec.P_TYPE,
rec.P_ID,
rec.P_NUMBER,
rec.P_PURPOSE_CODE,
rec.C_TYPE,
rec.C_ID,
rec.C_NUMBER,
rec.C_PURPOSE_CODE,
rec.C_SEQ_NUMBER,
NULL,
NULL
);
DELETE FROM RRS_SITE_GROUP_MEMBERS
WHERE CHILD_MEMBER_ID IN (
SELECT CHILD_MEMBER_ID
FROM RRS_SITE_GROUP_MEMBERS
START WITH CHILD_MEMBER_ID = v_child_id
AND SITE_GROUP_ID = v_source_hier_id
CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
AND SITE_GROUP_ID = v_source_hier_id)
AND SITE_GROUP_ID = v_source_hier_id;
INSERT INTO RRS_SITE_GROUP_MEMBERS
(
SITE_GROUP_VERSION_ID,
SITE_GROUP_ID,
PARENT_MEMBER_ID,
CHILD_MEMBER_ID,
DELETED_FLAG,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SEQUENCE_NUMBER
)
VALUES( v_dest_hier_version_id,
v_dest_hier_id,
rec.PARENT_ID,
rec.CHILD_ID,
'N',
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
rec.SEQUENCE_NUMBER);
ROLLBACK TO Update_Hierarchy_Fine;
ROLLBACK TO Update_Hierarchy_Fine;
x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Fine:' || x_msg_data;
ROLLBACK TO Update_Hierarchy_Fine;
x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Fine:' || SQLERRM;
END Update_Hierarchy_Fine;
SELECT DECODE(RSV.SITE_ID, NULL, 'NODE', 'SITE') AS P_TYPE,
RSGM.PARENT_MEMBER_ID AS P_ID,
NVL(RSV.SITE_IDENTIFICATION_NUMBER, RSGNV.NODE_IDENTIFICATION_NUMBER) AS P_NUMBER,
DECODE(RSV.SITE_ID, NULL, RSGNV.NODE_PURPOSE_CODE, RSU.SITE_USE_TYPE_CODE) AS P_PURPOSE_CODE,
DECODE(RSV2.SITE_ID, NULL, 'NODE', 'SITE') AS C_TYPE,
RSGM.CHILD_MEMBER_ID AS C_ID,
NVL(RSV2.SITE_IDENTIFICATION_NUMBER, RSGNV2.NODE_IDENTIFICATION_NUMBER) AS C_NUMBER,
DECODE(RSV2.SITE_ID, NULL, RSGNV2.NODE_PURPOSE_CODE, RSU2.SITE_USE_TYPE_CODE) AS C_PURPOSE_CODE,
RSGM.SEQUENCE_NUMBER AS C_SEQ_NUMBER
FROM RRS_SITE_GROUP_MEMBERS RSGM,
RRS_SITE_GROUP_NODES_VL RSGNV,
RRS_SITES_VL RSV, RRS_SITE_USES RSU,
RRS_SITE_GROUP_NODES_VL RSGNV2,
RRS_SITES_VL RSV2,
RRS_SITE_USES RSU2
WHERE RSGM.PARENT_MEMBER_ID = RSV.SITE_ID(+)
AND RSGM.PARENT_MEMBER_ID = RSU.SITE_ID(+)
AND NVL(RSU.IS_PRIMARY_FLAG, 'Y') = 'Y'
AND RSGM.PARENT_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID(+)
AND RSGM.CHILD_MEMBER_ID = RSV2.SITE_ID(+)
AND RSGM.CHILD_MEMBER_ID = RSU2.SITE_ID(+)
AND NVL(RSU2.IS_PRIMARY_FLAG, 'Y') = 'Y'
AND RSGM.CHILD_MEMBER_ID = RSGNV2.SITE_GROUP_NODE_ID(+)
START WITH PARENT_MEMBER_ID = -1
AND SITE_GROUP_ID = p_hier_id
CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
AND SITE_GROUP_ID = p_hier_id;
SELECT GROUP_PURPOSE_CODE
INTO v_hier_purpose_code
FROM RRS_SITE_GROUPS_VL RSGV
WHERE RSGV.SITE_GROUP_ID = p_hier_id;
DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
(
PARENT_TYPE,
PARENT_ID,
PARENT_NUMBER,
PARENT_PURPOSE_CODE,
CHILD_TYPE,
CHILD_ID,
CHILD_NUMBER,
CHILD_PURPOSE_CODE,
SEQUENCE_NUMBER,
CHILD_NODE_NAME,
CHILD_NODE_DESCRIPTION
)
VALUES( rec.P_TYPE,
rec.P_ID,
rec.P_NUMBER,
rec.P_PURPOSE_CODE,
rec.C_TYPE,
rec.C_ID,
rec.C_NUMBER,
rec.C_PURPOSE_CODE,
rec.C_SEQ_NUMBER,
NULL,
NULL
);
SELECT GROUP_PURPOSE_CODE, NAME
INTO v_hier_purpose_code, v_hier_name
FROM RRS_SITE_GROUPS_VL RSGV
WHERE RSGV.SITE_GROUP_ID = p_hier_id;
SELECT RSV.SITE_IDENTIFICATION_NUMBER, RSU.SITE_USE_TYPE_CODE
INTO v_parent_id_number, v_parent_purpose_code
FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU
WHERE RSV.SITE_ID = p_parent_id
AND RSV.SITE_ID = RSU.SITE_ID(+)
AND RSU.IS_PRIMARY_FLAG(+) = 'Y';
SELECT NODE_IDENTIFICATION_NUMBER, NODE_PURPOSE_CODE
INTO v_parent_id_number, v_parent_purpose_code
FROM RRS_SITE_GROUP_NODES_VL
WHERE SITE_GROUP_NODE_ID = p_parent_id;
SELECT RSV.SITE_IDENTIFICATION_NUMBER, RSU.SITE_USE_TYPE_CODE
INTO v_child_id_number, v_child_purpose_code
FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU
WHERE RSV.SITE_ID = p_child_id
AND RSV.SITE_ID = RSU.SITE_ID(+)
AND RSU.IS_PRIMARY_FLAG(+) = 'Y';
SELECT NODE_IDENTIFICATION_NUMBER, NODE_PURPOSE_CODE
INTO v_child_id_number, v_child_purpose_code
FROM RRS_SITE_GROUP_NODES_VL
WHERE SITE_GROUP_NODE_ID = p_child_id;
SELECT COUNT(*)
INTO v_count
FROM RRS_SITE_GROUP_MEMBERS
WHERE CHILD_MEMBER_ID = p_child_id
AND SITE_GROUP_ID = p_hier_id;
procedure Update_Hierarchy_Header_Test IS
v_name VARCHAR2(30) := 'R0001';
RRS_HIERARCHY_CRUD_PKG.Update_Hierarchy_Header(
p_name => v_name,
--p_new_name => v_new_name,
p_description => v_desc,
--p_purpose_code => v_purp,
p_start_date => v_sd,
--p_end_date => v_ed,
p_nullify_flag => 'T',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Update_Hierarchy_Header_Test;
procedure Update_Hierarchy_Node_Test IS
v_number VARCHAR2(30) := 'temp_11040';
RRS_HIERARCHY_CRUD_PKG.Update_Hierarchy_Node(
p_number => v_number,
--p_name => v_name,
p_description => v_desc,
--p_purpose_code => v_purp
--p_nullify_flag => 'T'
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Update_Hierarchy_Node_Test;
procedure Update_Hierarchy_Coarse_Test IS
v_name VARCHAR2(30) := 'Unit Hierarchy';
RRS_HIERARCHY_CRUD_PKG.Update_Hierarchy_Coarse(
p_hier_name => v_name,
--p_hier_new_name => v_new_name,
--p_hier_description => v_desc,
--p_hier_purpose_code => v_purp,
--p_hier_start_date => v_sd,
--p_nullify_flag => 'T',
--p_hier_end_date => v_ed,
p_hier_members_tab => v_tab,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Update_Hierarchy_Coarse_Test;
procedure Update_Hierarchy_Fine_Test IS
x_return_status VARCHAR2(30);
Update_Hierarchy_Fine(
p_hier_members_rec => v_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Update_Hierarchy_Fine_Test;