[Home] [Help]
34: -- NA SF SF NA:US-US -> US:CA-CA -> CA:SF-SF
35: -- US LA LA US:CA-CA -> CA:LA-LA
36: -- US SF SF US:CA-CA -> CA:SF-SF
37: --
38: -- FND_FLEX_VALUE_HIER_ALL
39: -- ===========================================
40: -- PARENT CHILD_LOW CHILD_HIGH RANGE LEVEL COMMENT
41: -- ------- --------- ---------- ----- ----- ----------
42: -- CA LA LA C 1 CA:LA-LA
667: p_hierarchy_level IN NUMBER)
668: IS
669: BEGIN
670: INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
671: INTO fnd_flex_value_hier_all
672: (flex_value_set_id, parent_flex_value, range_attribute,
673: child_flex_value_low, child_flex_value_high, hierarchy_level,
674: last_update_date, last_updated_by,
675: creation_date, created_by)
789:
790: -- ======================================================================
791: -- PROCEDURE : compile_value_hierarchies_all
792: -- ======================================================================
793: -- Compiles the flex value hierarchies in FND_FLEX_VALUE_HIER_ALL table.
794: --
795: PROCEDURE compile_value_hierarchies_all
796: IS
797: CURSOR main_norm_cur IS
872: -- In case of error, delete the inserted rows (kind of rollback).
873: --
874: BEGIN
875: DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
876: FROM fnd_flex_value_hier_all
877: WHERE flex_value_set_id = g_vset.flex_value_set_id;
878: COMMIT;
879: EXCEPTION
880: WHEN OTHERS THEN
916: BEGIN
917: l_row_count := 1;
918: WHILE (l_row_count > 0) LOOP
919: DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
920: FROM fnd_flex_value_hier_all
921: WHERE flex_value_set_id = g_vset.flex_value_set_id and
922: rownum < 1000;
923: l_row_count := SQL%rowcount;
924: COMMIT;
925: END LOOP;
926: EXCEPTION
927: WHEN OTHERS THEN
928: raise_exception_error(error_delete_hierarchies,
929: 'Unable to delete data in FND_FLEX_VALUE_HIER_ALL table.');
930: END delete_value_hierarchies_all;
931:
932:
933: PROCEDURE delete_sec_rule_allowed_vals
988: BEGIN
989: l_row_count := 1;
990: WHILE (l_row_count > 0) LOOP
991: UPDATE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
992: fnd_flex_value_hier_all
993: SET flex_value_set_id=g_vset.flex_value_set_id
994: WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
995: rownum < 1000;
996: l_row_count := SQL%rowcount;
1071: BETWEEN
1072: fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE))
1073: AND fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE))) AND
1074: NOT EXISTS (SELECT NULL FROM
1075: FND_FLEX_VALUE_HIER_ALL H,
1076: FND_FLEX_VALUE_RULE_LINES L,
1077: FND_FLEX_VALUE_RULES R
1078: WHERE
1079: L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1104: OR NOT EXISTS
1105: (SELECT NULL FROM FND_FLEX_VALUE_RULES R
1106: WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1107: R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1108: NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_HIER_ALL H, FND_FLEX_VALUE_RULE_LINES L
1109: WHERE H.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1110: H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
1111: L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1112: L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
1151: AND FND_FLEX_VALUES_VL.FLEX_VALUE
1152: BETWEEN NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE)
1153: AND NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE)) AND
1154: NOT EXISTS (SELECT NULL FROM
1155: FND_FLEX_VALUE_HIER_ALL H,
1156: FND_FLEX_VALUE_RULE_LINES L,
1157: FND_FLEX_VALUE_RULES R
1158: WHERE
1159: L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1180: OR NOT EXISTS
1181: (SELECT NULL FROM FND_FLEX_VALUE_RULES R
1182: WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1183: R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
1184: NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_HIER_ALL H, FND_FLEX_VALUE_RULE_LINES L
1185: WHERE H.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
1186: H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
1187: L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
1188: L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND