eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.RRS_HIERARCHY_CRUD_PKG dependencies on RRS_SITE_GROUP_MEMBERS

Line 168: FROM RRS_SITE_GROUP_MEMBERS

164: END IF; */
165:
166: SELECT CHILD_MEMBER_ID
167: INTO v_root_id
168: FROM RRS_SITE_GROUP_MEMBERS
169: WHERE SITE_GROUP_ID = v_hier_id
170: AND PARENT_MEMBER_ID = -1;
171:
172: --dbms_output.put_line('before update node');

Line 781: FROM RRS_SITE_GROUP_MEMBERS_TEMP

777: x_msg_data OUT NOCOPY VARCHAR2
778: )IS
779: CURSOR validate_rules_cursor IS
780: SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
781: FROM RRS_SITE_GROUP_MEMBERS_TEMP
782: WHERE CHILD_ID NOT IN (
783: SELECT CHILD_ID
784: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
785: WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'

Line 784: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR

780: SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
781: FROM RRS_SITE_GROUP_MEMBERS_TEMP
782: WHERE CHILD_ID NOT IN (
783: SELECT CHILD_ID
784: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
785: WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
786: AND RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code
787: AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
788: AND RSGMT.PARENT_TYPE = RGR.OBJECT1

Line 931: FROM RRS_SITE_GROUP_MEMBERS_TEMP

927: v_c_type VARCHAR2(30);
928:
929: CURSOR new_nodes_cursor IS
930: SELECT *
931: FROM RRS_SITE_GROUP_MEMBERS_TEMP
932: WHERE CHILD_TYPE = 'NODE'
933: AND CHILD_ID IS NULL
934: AND CHILD_NUMBER IS NOT NULL
935: AND CHILD_NUMBER NOT IN (

Line 942: FROM RRS_SITE_GROUP_MEMBERS_TEMP

938: WHERE NODE_IDENTIFICATION_NUMBER IS NOT NULL);
939:
940: CURSOR update_nodes_cursor IS
941: SELECT *
942: FROM RRS_SITE_GROUP_MEMBERS_TEMP
943: WHERE CHILD_TYPE = 'NODE'
944: AND CHILD_NUMBER IS NOT NULL
945: AND (CHILD_PURPOSE_CODE IS NOT NULL
946: OR CHILD_NODE_NAME IS NOT NULL

Line 951: FROM RRS_SITE_GROUP_MEMBERS_TEMP;

947: OR CHILD_NODE_DESCRIPTION IS NOT NULL);
948:
949: CURSOR new_members_cursor IS
950: SELECT *
951: FROM RRS_SITE_GROUP_MEMBERS_TEMP;
952:
953: CURSOR validate_p_id_num_cursor IS
954: SELECT PARENT_ID, PARENT_NUMBER
955: FROM RRS_SITE_GROUP_MEMBERS_TEMP

Line 955: FROM RRS_SITE_GROUP_MEMBERS_TEMP

951: FROM RRS_SITE_GROUP_MEMBERS_TEMP;
952:
953: CURSOR validate_p_id_num_cursor IS
954: SELECT PARENT_ID, PARENT_NUMBER
955: FROM RRS_SITE_GROUP_MEMBERS_TEMP
956: WHERE (PARENT_ID IS NOT NULL AND PARENT_NUMBER IS NOT NULL)
957: AND ((PARENT_ID, PARENT_NUMBER) NOT IN (SELECT SITE_ID, SITE_IDENTIFICATION_NUMBER FROM RRS_SITES_VL)
958: AND (PARENT_ID, PARENT_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
959:

Line 962: FROM RRS_SITE_GROUP_MEMBERS_TEMP

958: AND (PARENT_ID, PARENT_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
959:
960: CURSOR validate_c_id_num_cursor IS
961: SELECT CHILD_ID, CHILD_NUMBER
962: FROM RRS_SITE_GROUP_MEMBERS_TEMP
963: WHERE (CHILD_ID IS NOT NULL AND CHILD_NUMBER IS NOT NULL)
964: AND ((CHILD_ID, CHILD_NUMBER) NOT IN (SELECT SITE_ID, SITE_IDENTIFICATION_NUMBER FROM RRS_SITES_VL)
965: AND (CHILD_ID, CHILD_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
966:

Line 969: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

965: AND (CHILD_ID, CHILD_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
966:
967: CURSOR validate_p_id_cursor IS
968: SELECT PARENT_TYPE, PARENT_ID, PARENT_NUMBER
969: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
970: WHERE PARENT_ID IS NOT NULL
971: AND ((PARENT_TYPE = 'SITE'
972: AND NOT EXISTS (
973: SELECT SITE_ID

Line 984: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

980: WHERE SITE_GROUP_NODE_ID = RSGMT.PARENT_ID)));
981:
982: CURSOR validate_c_id_cursor IS
983: SELECT CHILD_TYPE, CHILD_ID, CHILD_NUMBER
984: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
985: WHERE CHILD_ID IS NOT NULL
986: AND ((CHILD_TYPE = 'SITE'
987: AND NOT EXISTS (
988: SELECT SITE_ID

Line 999: FROM RRS_SITE_GROUP_MEMBERS_TEMP

995: WHERE SITE_GROUP_NODE_ID = RSGMT.CHILD_ID)));
996:
997: CURSOR validate_dup_number_cursor IS
998: SELECT CHILD_TYPE, CHILD_NUMBER
999: FROM RRS_SITE_GROUP_MEMBERS_TEMP
1000: GROUP BY CHILD_TYPE, CHILD_NUMBER
1001: HAVING COUNT(*) > 1;
1002:
1003: CURSOR validate_num_cursor IS

Line 1005: FROM RRS_SITE_GROUP_MEMBERS_TEMP

1001: HAVING COUNT(*) > 1;
1002:
1003: CURSOR validate_num_cursor IS
1004: SELECT DECODE(PARENT_ID, NULL, PARENT_NUMBER, CHILD_NUMBER), DECODE(PARENT_ID, NULL, PARENT_TYPE, CHILD_TYPE)
1005: FROM RRS_SITE_GROUP_MEMBERS_TEMP
1006: WHERE CHILD_ID IS NULL
1007: OR PARENT_ID IS NULL;
1008:
1009: CURSOR validate_site_template_cursor IS

Line 1011: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITES_VL RSV

1007: OR PARENT_ID IS NULL;
1008:
1009: CURSOR validate_site_template_cursor IS
1010: SELECT CHILD_NUMBER
1011: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITES_VL RSV
1012: WHERE RSGMT.CHILD_ID = RSV.SITE_ID
1013: AND RSGMT.CHILD_TYPE = 'SITE'
1014: AND IS_TEMPLATE_FLAG = 'Y';
1015:

Line 1018: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITE_GROUP_NODES_VL RSGNV

1014: AND IS_TEMPLATE_FLAG = 'Y';
1015:
1016: CURSOR validate_node_name_cursor IS
1017: SELECT RSGNV.NAME
1018: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITE_GROUP_NODES_VL RSGNV
1019: WHERE RSGMT.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID
1020: GROUP BY RSGMT.PARENT_ID, RSGNV.NAME
1021: HAVING COUNT(*) > 1;
1022:

Line 1025: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1021: HAVING COUNT(*) > 1;
1022:
1023: CURSOR validate_p_in_hier_curosr IS
1024: SELECT RSGMT.CHILD_TYPE, RSGMT.CHILD_NUMBER
1025: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1026: WHERE RSGMT.PARENT_ID <> p_root_id
1027: AND (RSGMT.PARENT_TYPE, RSGMT.PARENT_NUMBER) NOT IN
1028: (SELECT RSGMT2.CHILD_TYPE, RSGMT2.CHILD_NUMBER
1029: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2

Line 1029: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2

1025: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1026: WHERE RSGMT.PARENT_ID <> p_root_id
1027: AND (RSGMT.PARENT_TYPE, RSGMT.PARENT_NUMBER) NOT IN
1028: (SELECT RSGMT2.CHILD_TYPE, RSGMT2.CHILD_NUMBER
1029: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1030: START WITH RSGMT2.PARENT_ID = p_root_id
1031: CONNECT BY PRIOR RSGMT2.CHILD_ID = RSGMT2.PARENT_ID);
1032:
1033: BEGIN

Line 1040: --insert new row into RRS_SITE_GROUP_MEMBERS table

1036:
1037: --initialize the return status
1038: x_return_status := FND_API.G_RET_STS_SUCCESS;
1039:
1040: --insert new row into RRS_SITE_GROUP_MEMBERS table
1041: INSERT INTO RRS_SITE_GROUP_MEMBERS
1042: (
1043: SITE_GROUP_VERSION_ID,
1044: SITE_GROUP_ID,

Line 1041: INSERT INTO RRS_SITE_GROUP_MEMBERS

1037: --initialize the return status
1038: x_return_status := FND_API.G_RET_STS_SUCCESS;
1039:
1040: --insert new row into RRS_SITE_GROUP_MEMBERS table
1041: INSERT INTO RRS_SITE_GROUP_MEMBERS
1042: (
1043: SITE_GROUP_VERSION_ID,
1044: SITE_GROUP_ID,
1045: PARENT_MEMBER_ID,

Line 1075: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP

1071: RAISE e_insert_failed;
1072: END IF;*/
1073:
1074: IF p_hier_members_tab IS NOT NULL THEN --members list is specify
1075: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
1076: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
1077:
1078: FOR i in p_hier_members_tab.FIRST..p_hier_members_tab.LAST LOOP
1079:

Line 1076: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;

1072: END IF;*/
1073:
1074: IF p_hier_members_tab IS NOT NULL THEN --members list is specify
1075: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
1076: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
1077:
1078: FOR i in p_hier_members_tab.FIRST..p_hier_members_tab.LAST LOOP
1079:
1080: IF p_hier_members_tab(i).child_object_type <> 'SITE' AND

Line 1090: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP

1086: --dbms_output.put_line('invalid transaction type');
1087: RAISE FND_API.G_EXC_ERROR;
1088: END IF;
1089:
1090: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
1091: (
1092: PARENT_TYPE,
1093: PARENT_ID,
1094: PARENT_NUMBER,

Line 1126: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1122: --check the number of root and the purpose code of the root node
1123: BEGIN
1124: SELECT RSGMT.CHILD_ID, RSGMT.CHILD_NUMBER
1125: INTO v_root_id, v_root_number
1126: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1127: WHERE (RSGMT.PARENT_ID = -1 OR RSGMT.PARENT_TYPE = 'NONE')
1128: AND (RSGMT.CHILD_ID IS NOT NULL OR RSGMT.CHILD_NUMBER IS NOT NULL)
1129: AND RSGMT.CHILD_TYPE = 'NODE';
1130: EXCEPTION

Line 1186: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP

1182: WHERE SITE_GROUP_NODE_ID = p_root_id;
1183: END IF;
1184:
1185: --delete the root node record
1186: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP
1187: WHERE (PARENT_ID = -1 OR PARENT_TYPE = 'NONE')
1188: AND (CHILD_ID IS NOT NULL OR CHILD_NUMBER IS NOT NULL)
1189: AND CHILD_TYPE = 'NODE';
1190:

Line 1195: FROM RRS_SITE_GROUP_MEMBERS_TEMP

1191: --validate id/number
1192: --case1: both null
1193: SELECT COUNT(*)
1194: INTO v_count
1195: FROM RRS_SITE_GROUP_MEMBERS_TEMP
1196: WHERE (PARENT_ID IS NULL AND PARENT_NUMBER IS NULL)
1197: OR (CHILD_ID IS NULL AND CHILD_NUMBER IS NULL);
1198:
1199: IF v_count <> 0 THEN

Line 1317: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1313:
1314: --case3: either id/number is null
1315: --set the parent_id
1316: --dbms_output.put_line('before case3 update parent id/num');
1317: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1318: SET PARENT_ID = (
1319: SELECT DECODE(PARENT_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
1320: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1321: WHERE RSGMT2.PARENT_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)

Line 1320: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2

1316: --dbms_output.put_line('before case3 update parent id/num');
1317: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1318: SET PARENT_ID = (
1319: SELECT DECODE(PARENT_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
1320: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1321: WHERE RSGMT2.PARENT_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)
1322: AND RSGMT2.PARENT_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
1323: AND RSGMT.ROWID = RSGMT2.ROWID)
1324: WHERE PARENT_ID IS NULL;

Line 1326: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1322: AND RSGMT2.PARENT_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
1323: AND RSGMT.ROWID = RSGMT2.ROWID)
1324: WHERE PARENT_ID IS NULL;
1325: --set the parent_number
1326: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1327: SET PARENT_NUMBER = (
1328: SELECT DECODE(PARENT_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
1329: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1330: WHERE RSGMT2.PARENT_ID = RSV.SITE_ID(+)

Line 1329: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2

1325: --set the parent_number
1326: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1327: SET PARENT_NUMBER = (
1328: SELECT DECODE(PARENT_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
1329: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1330: WHERE RSGMT2.PARENT_ID = RSV.SITE_ID(+)
1331: AND RSGMT2.PARENT_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1332: AND RSGMT.ROWID = RSGMT2.ROWID)
1333: WHERE PARENT_NUMBER IS NULL;

Line 1336: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1332: AND RSGMT.ROWID = RSGMT2.ROWID)
1333: WHERE PARENT_NUMBER IS NULL;
1334: --set the child_id
1335: --dbms_output.put_line('before case3 update child id/num');
1336: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1337: SET CHILD_ID = (
1338: SELECT DECODE(CHILD_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
1339: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1340: WHERE RSGMT2.CHILD_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)

Line 1339: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2

1335: --dbms_output.put_line('before case3 update child id/num');
1336: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1337: SET CHILD_ID = (
1338: SELECT DECODE(CHILD_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
1339: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1340: WHERE RSGMT2.CHILD_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)
1341: AND RSGMT2.CHILD_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
1342: AND RSGMT.ROWID = RSGMT2.ROWID)
1343: WHERE CHILD_ID IS NULL;

Line 1345: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1341: AND RSGMT2.CHILD_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
1342: AND RSGMT.ROWID = RSGMT2.ROWID)
1343: WHERE CHILD_ID IS NULL;
1344: --set the child_number
1345: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1346: SET CHILD_NUMBER = (
1347: SELECT DECODE(CHILD_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
1348: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1349: WHERE RSGMT2.CHILD_ID = RSV.SITE_ID(+)

Line 1348: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2

1344: --set the child_number
1345: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1346: SET CHILD_NUMBER = (
1347: SELECT DECODE(CHILD_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
1348: FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1349: WHERE RSGMT2.CHILD_ID = RSV.SITE_ID(+)
1350: AND RSGMT2.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1351: AND RSGMT.ROWID = RSGMT2.ROWID)
1352: WHERE CHILD_NUMBER IS NULL;

Line 1479: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1475: --RulesFwk
1476: IF p_hier_purpose_code IS NOT NULL THEN
1477: --default the purpose code for parent
1478: --dbms_output.put_line('before defaulting parent purpose code');
1479: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1480: SET PARENT_PURPOSE_CODE = (
1481: SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
1482: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
1483: WHERE RSGMT2.PARENT_ID = RSU.SITE_ID(+)

Line 1482: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV

1478: --dbms_output.put_line('before defaulting parent purpose code');
1479: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1480: SET PARENT_PURPOSE_CODE = (
1481: SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
1482: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
1483: WHERE RSGMT2.PARENT_ID = RSU.SITE_ID(+)
1484: AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
1485: AND RSGMT2.PARENT_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1486: AND RSGMT.ROWID = RSGMT2.ROWID);

Line 1490: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT

1486: AND RSGMT.ROWID = RSGMT2.ROWID);
1487:
1488: --default the purpose code for child
1489: --dbms_output.put_line('before defaulting child purpose code');
1490: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1491: SET CHILD_PURPOSE_CODE = (
1492: SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
1493: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
1494: WHERE RSGMT2.CHILD_ID = RSU.SITE_ID(+)

Line 1493: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV

1489: --dbms_output.put_line('before defaulting child purpose code');
1490: UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1491: SET CHILD_PURPOSE_CODE = (
1492: SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
1493: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
1494: WHERE RSGMT2.CHILD_ID = RSU.SITE_ID(+)
1495: AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
1496: AND RSGMT2.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1497: AND RSU.IS_PRIMARY_FLAG(+) = 'Y'

Line 1518: INSERT INTO RRS_SITE_GROUP_MEMBERS

1514:
1515: --insert into members table
1516: --dbms_output.put_line('before inserting into members');
1517: FOR mem_rec IN new_members_cursor LOOP
1518: INSERT INTO RRS_SITE_GROUP_MEMBERS
1519: (
1520: SITE_GROUP_VERSION_ID,
1521: SITE_GROUP_ID,
1522: PARENT_MEMBER_ID,

Line 1616: FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV

1612:
1613: BEGIN
1614: SELECT RSGM.CHILD_MEMBER_ID, RSGNV.NODE_IDENTIFICATION_NUMBER
1615: INTO v_root_id, v_root_number
1616: FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV
1617: WHERE RSGM.SITE_GROUP_ID = v_hier_id
1618: AND RSGM.PARENT_MEMBER_ID = -1
1619: AND RSGM.CHILD_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID
1620: AND RSGNV.NODE_PURPOSE_CODE = 'ROOT';

Line 1682: DELETE FROM RRS_SITE_GROUP_MEMBERS

1678:
1679: IF p_hier_members_tab IS NOT NULL OR FND_API.To_Boolean(p_nullify_flag) THEN
1680:
1681: --dbms_output.put_line('before delete members table');
1682: DELETE FROM RRS_SITE_GROUP_MEMBERS
1683: WHERE SITE_GROUP_ID = v_hier_id;
1684:
1685: --dbms_output.put_line('before create hierarchy members');
1686: Create_Hierarchy_Members(

Line 1764: FROM RRS_SITE_GROUP_MEMBERS RSGM,

1760: RSGM.CHILD_MEMBER_ID AS C_ID,
1761: NVL(RSV2.SITE_IDENTIFICATION_NUMBER, RSGNV2.NODE_IDENTIFICATION_NUMBER) AS C_NUMBER,
1762: DECODE(RSV2.SITE_ID, NULL, RSGNV2.NODE_PURPOSE_CODE, RSU2.SITE_USE_TYPE_CODE) AS C_PURPOSE_CODE,
1763: RSGM.SEQUENCE_NUMBER AS C_SEQ_NUMBER
1764: FROM RRS_SITE_GROUP_MEMBERS RSGM,
1765: RRS_SITE_GROUP_NODES_VL RSGNV,
1766: RRS_SITES_VL RSV, RRS_SITE_USES RSU,
1767: RRS_SITE_GROUP_NODES_VL RSGNV2,
1768: RRS_SITES_VL RSV2,

Line 1785: FROM RRS_SITE_GROUP_MEMBERS_TEMP;

1781: AND SITE_GROUP_ID = v_source_hier_id;
1782:
1783: CURSOR new_members_cursor IS
1784: SELECT *
1785: FROM RRS_SITE_GROUP_MEMBERS_TEMP;
1786:
1787: CURSOR validate_rules_cursor IS
1788: SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
1789: FROM RRS_SITE_GROUP_MEMBERS_TEMP

Line 1789: FROM RRS_SITE_GROUP_MEMBERS_TEMP

1785: FROM RRS_SITE_GROUP_MEMBERS_TEMP;
1786:
1787: CURSOR validate_rules_cursor IS
1788: SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
1789: FROM RRS_SITE_GROUP_MEMBERS_TEMP
1790: WHERE CHILD_ID NOT IN (
1791: SELECT CHILD_ID
1792: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
1793: WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'

Line 1792: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR

1788: SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
1789: FROM RRS_SITE_GROUP_MEMBERS_TEMP
1790: WHERE CHILD_ID NOT IN (
1791: SELECT CHILD_ID
1792: FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
1793: WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
1794: AND RGR.GROUP_PURPOSE_CODE = v_dest_hier_purpose_code
1795: AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
1796: AND RSGMT.PARENT_TYPE = RGR.OBJECT1

Line 1930: FROM RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS RSGM

1926: IF p_hier_members_rec.dest_parent_object_type = 'NODE' THEN
1927: BEGIN
1928: SELECT SITE_GROUP_NODE_ID, NODE_PURPOSE_CODE
1929: INTO v_dest_parent_id, v_dest_parent_purpose_code
1930: FROM RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS RSGM
1931: WHERE RSGNV.NODE_IDENTIFICATION_NUMBER = p_hier_members_rec.dest_parent_id_number
1932: AND RSGNV.SITE_GROUP_NODE_ID = RSGM.CHILD_MEMBER_ID
1933: AND RSGM.SITE_GROUP_ID = v_dest_hier_id;
1934: EXCEPTION

Line 1948: FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU, RRS_SITE_GROUP_MEMBERS RSGM

1944: ELSIF p_hier_members_rec.dest_parent_object_type = 'SITE' THEN
1945: BEGIN
1946: SELECT RSV.SITE_ID, RSU.SITE_USE_TYPE_CODE
1947: INTO v_dest_parent_id, v_dest_parent_purpose_code
1948: FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU, RRS_SITE_GROUP_MEMBERS RSGM
1949: WHERE RSV.SITE_IDENTIFICATION_NUMBER = p_hier_members_rec.dest_parent_id_number
1950: AND RSV.SITE_ID = RSU.SITE_ID(+)
1951: AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
1952: AND RSV.SITE_ID = RSGM.CHILD_MEMBER_ID

Line 1981: FROM RRS_SITE_GROUP_MEMBERS

1977: --dbms_output.put_line('Transaction ADD');
1978: --child id should not appear in the dest hier
1979: SELECT COUNT(*)
1980: INTO v_count
1981: FROM RRS_SITE_GROUP_MEMBERS
1982: WHERE CHILD_MEMBER_ID = v_child_id
1983: AND SITE_GROUP_ID = v_dest_hier_id;
1984: IF v_count <> 0 THEN
1985: --RRS_HIER_CHILD_EXISTS

Line 2025: INSERT INTO RRS_SITE_GROUP_MEMBERS

2021: END IF;
2022:
2023: --add to members table
2024: --dbms_output.put_line('before add to members table');
2025: INSERT INTO RRS_SITE_GROUP_MEMBERS
2026: (
2027: SITE_GROUP_VERSION_ID,
2028: SITE_GROUP_ID,
2029: PARENT_MEMBER_ID,

Line 2059: FROM RRS_SITE_GROUP_MEMBERS

2055: --dbms_output.put_line('Transaction REMOVE');
2056: --child id should appear in the destination hierarchy
2057: SELECT COUNT(*)
2058: INTO v_count
2059: FROM RRS_SITE_GROUP_MEMBERS
2060: WHERE SITE_GROUP_ID = v_dest_hier_id
2061: AND CHILD_MEMBER_ID = v_child_id;
2062:
2063: IF v_count = 0 THEN

Line 2075: DELETE FROM RRS_SITE_GROUP_MEMBERS

2071: RAISE FND_API.G_EXC_ERROR;
2072: END IF;
2073:
2074: --remove the subtree in members table
2075: DELETE FROM RRS_SITE_GROUP_MEMBERS
2076: WHERE CHILD_MEMBER_ID IN (
2077: SELECT CHILD_MEMBER_ID
2078: FROM RRS_SITE_GROUP_MEMBERS
2079: START WITH CHILD_MEMBER_ID = v_child_id

Line 2078: FROM RRS_SITE_GROUP_MEMBERS

2074: --remove the subtree in members table
2075: DELETE FROM RRS_SITE_GROUP_MEMBERS
2076: WHERE CHILD_MEMBER_ID IN (
2077: SELECT CHILD_MEMBER_ID
2078: FROM RRS_SITE_GROUP_MEMBERS
2079: START WITH CHILD_MEMBER_ID = v_child_id
2080: AND SITE_GROUP_ID = v_dest_hier_id
2081: CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
2082: AND SITE_GROUP_ID = v_dest_hier_id)

Line 2110: FROM RRS_SITE_GROUP_MEMBERS

2106: --2. get source parent id
2107: BEGIN
2108: SELECT PARENT_MEMBER_ID
2109: INTO v_source_parent_id
2110: FROM RRS_SITE_GROUP_MEMBERS
2111: WHERE SITE_GROUP_ID = v_source_hier_id
2112: AND CHILD_MEMBER_ID = v_child_id;
2113: EXCEPTION
2114: WHEN NO_DATA_FOUND THEN

Line 2141: FROM RRS_SITE_GROUP_MEMBERS

2137: SELECT COUNT(*)
2138: INTO v_count
2139: FROM (
2140: SELECT CHILD_MEMBER_ID
2141: FROM RRS_SITE_GROUP_MEMBERS
2142: START WITH CHILD_MEMBER_ID = v_child_id
2143: AND SITE_GROUP_ID = v_source_hier_id
2144: CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
2145: AND SITE_GROUP_ID = v_source_hier_id) TMP

Line 2157: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP

2153: RAISE FND_API.G_EXC_ERROR;
2154: END IF;
2155: END IF;
2156:
2157: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
2158: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
2159:
2160: FOR rec IN parent_child_cursor LOOP
2161: --check only when source and dest hier are different

Line 2158: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;

2154: END IF;
2155: END IF;
2156:
2157: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
2158: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
2159:
2160: FOR rec IN parent_child_cursor LOOP
2161: --check only when source and dest hier are different
2162: IF v_source_hier_id <> v_dest_hier_id THEN

Line 2166: FROM RRS_SITE_GROUP_MEMBERS

2162: IF v_source_hier_id <> v_dest_hier_id THEN
2163: --validate child id. Should not appear in dest hier
2164: SELECT COUNT(*)
2165: INTO v_count
2166: FROM RRS_SITE_GROUP_MEMBERS
2167: WHERE CHILD_MEMBER_ID = rec.C_ID
2168: AND SITE_GROUP_ID = v_dest_hier_id;
2169:
2170: IF v_count <> 0 THEN

Line 2190: FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV

2186: IF p_hier_members_rec.child_object_type = 'NODE' THEN
2187: --dbms_output.put_line('before check duplicated node name');
2188: SELECT COUNT(*)
2189: INTO v_count
2190: FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV
2191: WHERE RSGM.SITE_GROUP_ID = v_dest_hier_id
2192: AND RSGM.PARENT_MEMBER_ID = v_dest_parent_id
2193: AND RSGM.CHILD_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID
2194: AND RSGNV.NAME = v_child_name;

Line 2207: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP

2203: END IF;
2204: END IF;
2205:
2206: --dbms_output.put_line('before insert into temp table 1');
2207: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
2208: (
2209: PARENT_TYPE,
2210: PARENT_ID,
2211: PARENT_NUMBER,

Line 2236: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP

2232: );
2233:
2234: ELSE --other node/site in the subtree
2235: --dbms_output.put_line('before insert into temp table 2');
2236: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
2237: (
2238: PARENT_TYPE,
2239: PARENT_ID,
2240: PARENT_NUMBER,

Line 2287: DELETE FROM RRS_SITE_GROUP_MEMBERS

2283:
2284: --remove the subtree in source hier for MOVE transaction
2285: IF p_hier_members_rec.transaction_type = 'MOVE' THEN
2286: --dbms_output.put_line('before remove the subtree in source hier');
2287: DELETE FROM RRS_SITE_GROUP_MEMBERS
2288: WHERE CHILD_MEMBER_ID IN (
2289: SELECT CHILD_MEMBER_ID
2290: FROM RRS_SITE_GROUP_MEMBERS
2291: START WITH CHILD_MEMBER_ID = v_child_id

Line 2290: FROM RRS_SITE_GROUP_MEMBERS

2286: --dbms_output.put_line('before remove the subtree in source hier');
2287: DELETE FROM RRS_SITE_GROUP_MEMBERS
2288: WHERE CHILD_MEMBER_ID IN (
2289: SELECT CHILD_MEMBER_ID
2290: FROM RRS_SITE_GROUP_MEMBERS
2291: START WITH CHILD_MEMBER_ID = v_child_id
2292: AND SITE_GROUP_ID = v_source_hier_id
2293: CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
2294: AND SITE_GROUP_ID = v_source_hier_id)

Line 2302: INSERT INTO RRS_SITE_GROUP_MEMBERS

2298: --insert into members table
2299: --dbms_output.put_line('before insert into members table');
2300: FOR rec IN new_members_cursor LOOP
2301: --dbms_output.put_line(''||v_dest_hier_id||'/'||rec.PARENT_ID||'/'||rec.CHILD_ID);
2302: INSERT INTO RRS_SITE_GROUP_MEMBERS
2303: (
2304: SITE_GROUP_VERSION_ID,
2305: SITE_GROUP_ID,
2306: PARENT_MEMBER_ID,

Line 2381: FROM RRS_SITE_GROUP_MEMBERS RSGM,

2377: RSGM.CHILD_MEMBER_ID AS C_ID,
2378: NVL(RSV2.SITE_IDENTIFICATION_NUMBER, RSGNV2.NODE_IDENTIFICATION_NUMBER) AS C_NUMBER,
2379: DECODE(RSV2.SITE_ID, NULL, RSGNV2.NODE_PURPOSE_CODE, RSU2.SITE_USE_TYPE_CODE) AS C_PURPOSE_CODE,
2380: RSGM.SEQUENCE_NUMBER AS C_SEQ_NUMBER
2381: FROM RRS_SITE_GROUP_MEMBERS RSGM,
2382: RRS_SITE_GROUP_NODES_VL RSGNV,
2383: RRS_SITES_VL RSV, RRS_SITE_USES RSU,
2384: RRS_SITE_GROUP_NODES_VL RSGNV2,
2385: RRS_SITES_VL RSV2,

Line 2422: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP

2418: --RulesFwk
2419: --dbms_output.put_line('before RulesFwk');
2420: IF v_hier_purpose_code IS NOT NULL THEN
2421:
2422: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
2423: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
2424:
2425: FOR rec IN parent_child_cursor LOOP
2426: IF rec.P_ID <> -1 THEN

Line 2423: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;

2419: --dbms_output.put_line('before RulesFwk');
2420: IF v_hier_purpose_code IS NOT NULL THEN
2421:
2422: --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
2423: DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
2424:
2425: FOR rec IN parent_child_cursor LOOP
2426: IF rec.P_ID <> -1 THEN
2427: --insert into members temp table

Line 2429: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP

2425: FOR rec IN parent_child_cursor LOOP
2426: IF rec.P_ID <> -1 THEN
2427: --insert into members temp table
2428: --dbms_output.put_line('before insert into temp table 2');
2429: INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
2430: (
2431: PARENT_TYPE,
2432: PARENT_ID,
2433: PARENT_NUMBER,

Line 2604: FROM RRS_SITE_GROUP_MEMBERS

2600:
2601: --child id should not appear in the dest hier
2602: SELECT COUNT(*)
2603: INTO v_count
2604: FROM RRS_SITE_GROUP_MEMBERS
2605: WHERE CHILD_MEMBER_ID = p_child_id
2606: AND SITE_GROUP_ID = p_hier_id;
2607: IF v_count <> 0 THEN
2608: --RRS_HIER_CHILD_EXISTS