APPS.AHL_UMP_UTIL_PKG dependencies on AHL_CONFIG_COMPONENTS

Line 820: (select subj_child_exists from ahl_config_components

816: -- added query for subj_child_exists to fix perf bug# 13629335
817: CURSOR ahl_appl_parent_mr_csr(p_mr_id IN NUMBER) IS
818:
819: SELECT csi_item_instance_id,
820: (select subj_child_exists from ahl_config_components
821: where subject_id = csi_item_instance_id) child_exists
822: FROM (SELECT distinct csi_item_instance_id
823: FROM ahl_applicable_mrs
824: WHERE mr_header_id = p_mr_id);

Line 916: FROM ahl_config_components

912:
913: -- added hint as per Apps Perf team feedback to fix perf bug# 13629335
914: -- this SQL will be executed only if subj_child_exists = Y
915: SELECT /*+ push_subq */ subject_id csi_item_instance_id
916: FROM ahl_config_components
917: WHERE EXISTS (SELECT /*+ push_subq */ 'x'
918: FROM ahl_applicable_mrs AMR
919: WHERE amr.mr_header_id = p_mr_id
920: AND amr.csi_item_instance_id = subject_id)

Line 945: -- ahl_config_components contains all components for root object id only.

941: WHERE amr.mr_header_id = p_mr_id
942: AND amr.csi_item_instance_id = p_item_instance_id);
943: --
944: -- added to fix perf bug# 13629335
945: -- ahl_config_components contains all components for root object id only.
946: -- For root object Id case, get all child instances with applicability for a given mr_id
947: CURSOR ahl_appl_all_csr(p_mr_id IN NUMBER,
948: p_item_instance_id IN NUMBER) IS
949: SELECT /*+ push_subq */ cmp.subject_id csi_item_instance_id

Line 950: FROM ahl_config_components cmp

946: -- For root object Id case, get all child instances with applicability for a given mr_id
947: CURSOR ahl_appl_all_csr(p_mr_id IN NUMBER,
948: p_item_instance_id IN NUMBER) IS
949: SELECT /*+ push_subq */ cmp.subject_id csi_item_instance_id
950: FROM ahl_config_components cmp
951: WHERE EXISTS (SELECT /*+ push_subq */ 'x'
952: FROM ahl_applicable_mrs AMR
953: WHERE amr.mr_header_id = p_mr_id
954: AND amr.csi_item_instance_id = cmp.subject_id)

Line 1428: DELETE FROM AHL_CONFIG_COMPONENTS;

1424: -- added for perf fix bug# 13629335
1425: l_child_exists_tbl vchar_tbl_type;
1426:
1427: BEGIN
1428: DELETE FROM AHL_CONFIG_COMPONENTS;
1429:
1430: OPEN get_config_components(p_root_instance_id);
1431: LOOP
1432: FETCH get_config_components BULK COLLECT INTO l_subj_id_tbl, l_obj_id_tbl, l_child_exists_tbl LIMIT l_buffer_limit;

Line 1437: INSERT INTO AHL_CONFIG_COMPONENTS (subject_id, object_id, root_object_id, subj_child_exists)

1433: EXIT WHEN (l_subj_id_tbl.count = 0);
1434:
1435: -- insert into temp table.
1436: FORALL i IN 1..l_obj_id_tbl.count
1437: INSERT INTO AHL_CONFIG_COMPONENTS (subject_id, object_id, root_object_id, subj_child_exists)
1438: values (l_subj_id_tbl(i), l_obj_id_tbl(i), p_root_instance_id, nvl(l_child_exists_tbl(i), 'N'));
1439:
1440: l_subj_id_tbl.delete;
1441: l_obj_id_tbl.delete;