[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT serial_number, item_number, inventory_item_id,
location_description, status, owner_name, condition,
mfg_date, 'm' manufacturer_id, 'c' country_code
FROM ahl_unit_installed_details_v
WHERE csi_item_instance_id = c_item_instance_id;
SELECT ii.serial_number,
(select kfv.concatenated_segments from mtl_system_items_kfv kfv
where kfv.inventory_item_id = ii.inventory_item_id
AND kfv.organization_id = ii.inv_master_organization_id) item_number,
ii.inventory_item_id,
ahl_util_uc_pkg.getcsi_locationDesc(ii.location_id, ii.location_type_code,
ii.inv_organization_id, ii.inv_subinventory_name,
ii.inv_locator_id, ii.wip_job_id) Location_description,
(select f.meaning from csi_lookups f where ii.instance_usage_code = f.lookup_code
AND f.lookup_type = 'CSI_INSTANCE_USAGE_CODE') Status,
(select p.party_name from csi_inst_party_details_v p
where p.instance_id = ii.instance_id and p.relationship_type_code = 'OWNER') owner_name,
(select mat.description from mtl_material_statuses mat where ii.INSTANCE_CONDITION_ID = mat.status_id) condition,
(select to_date(ciea.attribute_value, 'DD/MM/YYYY')
from csi_inst_extend_attrib_v ciea
where ciea.instance_id = ii.instance_id
AND ciea.attribute_code = 'AHL_MFG_DATE'
AND ciea.attribute_level = 'GLOBAL') mfg_date,
'm' manufacturer_id, 'c' country_code
FROM csi_item_instances ii
WHERE ii.instance_id = c_item_instance_id;
SELECT instance_id
FROM csi_item_instances
WHERE instance_id = c_item_instance_id
AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
SELECT inventory_item_id
FROM csi_item_instances
WHERE instance_id = c_item_instance_id
AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
SELECT mr_header_id
FROM ahl_mr_headers_app_v
WHERE mr_header_id = c_mr_header_id;
SELECT mr_header_id, mr_effectivity_id, inventory_item_id,relationship_id, pc_node_id
FROM ahl_mr_effectivities_app_v
WHERE mr_effectivity_id = NVL(c_mr_effectivity_id,mr_effectivity_id)
AND mr_header_id = c_mr_header_id;
SELECT mr_header_id, mr_effectivity_id, inventory_item_id,relationship_id, pc_node_id
FROM ahl_mr_effectivities_app_v
WHERE mr_header_id = c_mr_header_id
AND mr_effectivity_id = NVL(c_mr_effectivity_id, mr_effectivity_id);
SELECT DISTINCT item_instance_id, serial_number, item_number,
inventory_item_id, location, status, owner, condition,unit_name,uc_header_id -- JKJain, NR Analysis and Forecasting
FROM ahl_mr_instances_temp;
SELECT mr_effectivity_id, item_instance_id, serial_number, item_number,
inventory_item_id, location, status, owner, condition,unit_name,uc_header_id -- JKJain, NR Analysis and Forecasting
FROM ahl_mr_instances_temp;
SELECT DISTINCT item_instance_id, serial_number, item_number,
inventory_item_id, location, status, owner, condition,unit_name,uc_header_id -- JKJain, NR Analysis and Forecasting
FROM ahl_mr_instances_temp
ORDER BY item_number, serial_number;
SELECT unit_config_header_id, name
FROM ahl_unit_config_headers A
WHERE csi_item_instance_id = c_instance_id
OR EXISTS (SELECT 'X'
FROM csi_ii_relationships B
WHERE B.object_id = A.csi_item_instance_id
START WITH subject_id = c_instance_id
CONNECT BY subject_id = PRIOR object_id);
SELECT instance_id
FROM csi_item_instances A
WHERE inventory_item_id = c_inventory_item_id
AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND NOT EXISTS (SELECT 'X'
FROM csi_ii_relationships B
WHERE B.subject_id = A.instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(B.active_start_date,sysdate)) and trunc(NVL(b.active_end_date,sysdate+1))
);*/
SELECT instance_id
FROM csi_item_instances
WHERE inventory_item_id = c_inventory_item_id
AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(nvl(active_end_date,sysdate+1));
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers a,ahl_applicable_instances b
WHERE a.csi_item_instance_id=b.csi_item_instance_id
and a.master_config_id=b.position_id
AND b.position_id=c_relationship_id
and SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
and not exists (SELECT 'X'
FROM csi_ii_relationships
WHERE subject_id=b.csi_item_instance_id
AND relationship_type_code='COMPONENT_OF');*/
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers a,ahl_applicable_instances b
WHERE a.csi_item_instance_id=b.csi_item_instance_id
and b.position_id= c_relationship_id
AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
UNION
SELECT a.subject_id instance_id
FROM csi_ii_relationships a,ahl_applicable_instances b
WHERE a.subject_id=b.csi_item_instance_id
and b.position_id=c_relationship_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.relationship_type_code = 'COMPONENT-OF';
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
--WHERE A.master_config_id=api.position_id
--AND A.csi_item_instance_id=api.csi_item_instance_id
WHERE A.csi_item_instance_id=api.csi_item_instance_id
AND api.position_id= C_RELATIONSHIP_ID
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
AND trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = api.csi_item_instance_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND inventory_item_id = c_inventory_item_id);*/
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE A.csi_item_instance_id=api.csi_item_instance_id
AND api.position_id=c_relationship_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = api.csi_item_instance_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate))
and trunc(nvl(b.active_end_date,sysdate+1))
AND inventory_item_id = c_inventory_item_id)
UNION
SELECT subject_id instance_id
FROM csi_ii_relationships A,ahl_applicable_instances api
WHERE api.position_id=c_relationship_id
and api.csi_item_instance_id=a.subject_id
AND relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = api.csi_item_instance_id
AND sysdate between trunc(nvl(b.active_start_date,sysdate))
and trunc(nvl(b.active_end_date,sysdate+1))
AND inventory_item_id = c_inventory_item_id);
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A
WHERE EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.csi_item_instance_id
AND sysdate between trunc(nvl(b.active_start_date,sysdate))
and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id)
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = A.unit_config_header_id
AND C.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
UNION
SELECT instance_id
FROM csi_item_instances A
WHERE A.inventory_item_id = c_inventory_item_id
AND sysdate between trunc(nvl(a.active_start_date,sysdate))
and trunc(nvl(a.active_end_date,sysdate+1))
AND NOT EXISTS (SELECT 'X'
FROM csi_ii_relationships B
WHERE B.subject_id = A.instance_id
AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.relationship_type_code = 'COMPONENT-OF')
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = A.inventory_item_id
AND C.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id = PRIOR D.pc_node_id));*/
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A
WHERE EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.csi_item_instance_id
AND sysdate between trunc(nvl(b.active_start_date,sysdate))
and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id)
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = A.unit_config_header_id
AND C.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
UNION
SELECT instance_id
FROM csi_item_instances A
WHERE A.inventory_item_id = c_inventory_item_id
AND sysdate between trunc(nvl(a.active_start_date,sysdate))
and trunc(nvl(a.active_end_date,sysdate+1))
AND NOT EXISTS (SELECT 'X'
FROM csi_ii_relationships B
WHERE B.subject_id = A.instance_id
AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.relationship_type_code = 'COMPONENT-OF')
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = A.inventory_item_id
AND C.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
UNION -- aobe query added to fix bug number 5448015
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.csi_item_instance_id=api.csi_item_instance_id
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = Api.csi_item_instance_id
AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id)
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = A.unit_config_header_id
AND C.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
UNION
SELECT a.subject_id instance_id
FROM csi_ii_relationships A
WHERE relationship_type_code = 'COMPONENT-OF'
AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND A.SUBJECT_id IN (SELECT csi_item_instance_id from ahl_applicable_instances
where csi_item_instance_id=a.subject_id )
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.subject_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id)
START WITH object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers C
WHERE EXISTS (SELECT 'X'
FROM ahl_pc_associations D
WHERE D.unit_item_id = C.unit_config_header_id
AND D.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b E
WHERE E.pc_node_id = D.pc_node_id
START WITH E.pc_node_id = c_pc_node_id
CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
CONNECT BY object_id = PRIOR subject_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.relationship_type_code = 'COMPONENT-OF'
UNION
SELECT a.instance_id
FROM csi_item_instances A
WHERE A.inventory_item_id = c_inventory_item_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations B
WHERE B.unit_item_id = A.inventory_item_id
AND B.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b C
WHERE C.pc_node_id = B.pc_node_id
START WITH C.pc_node_id = c_pc_node_id
CONNECT BY C.parent_node_id= PRIOR C.pc_node_id))
UNION
SELECT A.subject_id instance_id
FROM csi_ii_relationships A
WHERE relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.subject_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id
)
START WITH object_id IN (SELECT C.instance_id
FROM csi_item_instances C
WHERE EXISTS (SELECT 'X'
FROM ahl_pc_associations D
WHERE D.unit_item_id = C.inventory_item_id
AND D.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b E
WHERE E.pc_node_id = D.pc_node_id
START WITH E.pc_node_id = c_pc_node_id
CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
CONNECT BY object_id = PRIOR subject_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.relationship_type_code = 'COMPONENT-OF';*/
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE api.position_id = c_relationship_id
and a.csi_item_instance_id=api.csi_item_instance_id
--and A.master_config_id=api.position_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations B
WHERE B.unit_item_id = A.unit_config_header_id
AND B.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b C
WHERE C.pc_node_id = B.pc_node_id
START WITH C.pc_node_id = c_pc_node_id
CONNECT BY C.parent_node_id = PRIOR C.pc_node_id))
UNION
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE api.position_id = c_relationship_id
--and A.master_config_id=api.position_id
and api.csi_item_instance_id=a.csi_item_instance_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.csi_item_instance_id
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = B.inventory_item_id
AND C.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)
)
);*/
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE api.csi_item_instance_id=a.csi_item_instance_id
and api.position_id=c_relationship_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations B
WHERE B.unit_item_id = A.unit_config_header_id
AND B.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b C
WHERE C.pc_node_id = B.pc_node_id
START WITH C.pc_node_id = c_pc_node_id
CONNECT BY C.parent_node_id= PRIOR C.pc_node_id))
UNION
SELECT a.subject_id instance_id
FROM csi_ii_relationships a
WHERE relationship_type_code = 'COMPONENT-OF'
AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
START WITH object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers Ax
WHERE ax.csi_item_instance_id=a.subject_id AND
EXISTS (SELECT 'X'
FROM ahl_pc_associations B
WHERE B.unit_item_id = Ax.unit_config_header_id
AND B.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b C
WHERE C.pc_node_id = B.pc_node_id
START WITH C.pc_node_id = c_pc_node_id
CONNECT BY C.parent_node_id= PRIOR C.pc_node_id)
)
)
CONNECT BY object_id = PRIOR subject_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.relationship_type_code = 'COMPONENT-OF'
UNION
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE api.position_id = c_relationship_id
and api.csi_item_instance_id= a.csi_item_instance_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = api.csi_item_instance_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = B.inventory_item_id
AND C.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)))
UNION
SELECT a.subject_id instance_id
FROM csi_ii_relationships a
WHERE a.relationship_type_code = 'COMPONENT-OF'
AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND subject_id in (select csi_item_instance_id from ahl_applicable_instances)
START WITH object_id IN (SELECT ax.instance_id
FROM csi_item_instances Ax
WHERE SYSDATE between trunc(nvl(ax.active_start_date,sysdate)) and trunc(nvl(ax.active_end_date,sysdate+1))
AND instance_id=a.subject_id
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations B
WHERE B.unit_item_id = Ax.inventory_item_id
AND B.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b C
WHERE C.pc_node_id = B.pc_node_id
START WITH C.pc_node_id = c_pc_node_id
CONNECT BY C.parent_node_id= PRIOR C.pc_node_id)
)
)
CONNECT BY object_id = PRIOR subject_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.relationship_type_code = 'COMPONENT-OF';*/
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE api.position_id = c_relationship_id
AND api.csi_item_instance_id=A.csi_item_instance_id
--and A.master_config_id=api.position_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = Api.csi_item_instance_id
AND B.inventory_item_id = c_inventory_item_id)
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = A.unit_config_header_id
AND C.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
UNION
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE Api.position_id = c_relationship_id
AND A.csi_item_instance_id=API.csi_item_instance_id
--and A.master_config_id=api.position_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.csi_item_instance_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = B.inventory_item_id
AND C.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)));*/
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE api.position_id = c_relationship_id
AND api.csi_item_instance_id=A.csi_item_instance_id
-- AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = api.csi_item_instance_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id)
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = A.unit_config_header_id
AND C.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id= PRIOR D.pc_node_id))
UNION
SELECT a.subject_id instance_id
FROM csi_ii_relationships A
WHERE relationship_type_code = 'COMPONENT-OF'
AND a.relationship_id in (Select position_id from AHL_APPLICABLE_INSTANCES)
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.subject_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id)
START WITH object_id IN (SELECT c.csi_item_instance_id
FROM ahl_unit_config_headers C,ahl_applicable_instances api
WHERE c.csi_item_instance_id=api.csi_item_instance_id
and api.position_id=c_relationship_id and
EXISTS (SELECT 'X'
FROM ahl_pc_associations D
WHERE D.unit_item_id = C.unit_config_header_id
AND D.association_type_flag = 'U'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b E
WHERE E.pc_node_id = D.pc_node_id
START WITH E.pc_node_id = c_pc_node_id
CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)))
CONNECT BY object_id = PRIOR subject_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.relationship_type_code = 'COMPONENT-OF'
UNION
SELECT a.csi_item_instance_id instance_id
FROM ahl_unit_config_headers A,ahl_applicable_instances api
WHERE api.position_id = c_relationship_id
AND A.csi_item_instance_id=API.csi_item_instance_id
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = api.csi_item_instance_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations C
WHERE C.unit_item_id = B.inventory_item_id
AND C.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b D
WHERE D.pc_node_id = C.pc_node_id
START WITH D.pc_node_id = c_pc_node_id
CONNECT BY D.parent_node_id= PRIOR D.pc_node_id)))
UNION
SELECT subject_id instance_id
FROM csi_ii_relationships A
WHERE relationship_type_code = 'COMPONENT-OF'
AND a.subject_id in (Select csi_item_instance_id from AHL_APPLICABLE_INSTANCES )
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM csi_item_instances B
WHERE B.instance_id = A.subject_id
AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
AND B.inventory_item_id = c_inventory_item_id)
START WITH object_id IN (SELECT instance_id
FROM csi_item_instances C
WHERE SYSDATE between trunc(nvl(c.active_start_date,sysdate)) and trunc(nvl(C.active_end_date,sysdate+1))
AND EXISTS (SELECT 'X'
FROM ahl_pc_associations D
WHERE D.unit_item_id = C.inventory_item_id
AND D.association_type_flag = 'I'
AND EXISTS (SELECT 'X'
FROM ahl_pc_nodes_b E
WHERE E.pc_node_id = D.pc_node_id
START WITH E.pc_node_id = c_pc_node_id
CONNECT BY E.parent_node_id= PRIOR E.pc_node_id)
)
)
CONNECT BY object_id = PRIOR subject_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND a.relationship_type_code = 'COMPONENT-OF';*/
SELECT A.instance_id
FROM csi_item_instances A
WHERE A.inventory_item_id = c_inventory_item_id
AND sysdate between trunc(nvl(A.active_start_date,sysdate))
and trunc(nvl(A.active_end_date,sysdate+1))
AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
SELECT A.instance_id
FROM csi_item_instances A,ahl_applicable_instances api
WHERE A.instance_id = api.csi_item_instance_id
AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND api.position_id=c_relationship_id
AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
SELECT A.instance_id
FROM csi_item_instances A,ahl_applicable_instances api
WHERE A.instance_id = api.csi_item_instance_id
AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
AND api.position_id=c_relationship_id
AND A.inventory_item_id = c_inventory_item_id
AND AHL_FMP_PVT.is_pc_assoc_valid(A.instance_id,c_pc_node_id) = FND_API.G_TRUE;
SELECT mr_header_id
FROM ahl_mr_relationships
WHERE relationship_code = 'TERMINATES'
AND related_mr_header_id = c_mr_header_id;
SELECT fleet_header_id
FROM ahl_mr_effectivities MR
WHERE MR.mr_effectivity_id = c_mr_effectivity_id ;
DELETE FROM ahl_mr_instances_temp;
DELETE FROM ahl_applicable_instances;
Select count(*) into l_counter
from ahl_applicable_instances;
x_mr_item_inst_tbl.DELETE(i);
x_mr_item_inst_tbl.DELETE(i);
x_mr_item_inst_tbl.DELETE(i);
INSERT INTO ahl_mr_instances_temp
(
MR_INSTANCE_TEMP_ID,
MR_EFFECTIVITY_ID,
ITEM_INSTANCE_ID,
SERIAL_NUMBER,
ITEM_NUMBER,
INVENTORY_ITEM_ID,
LOCATION,
STATUS,
OWNER,
CONDITION,
UNIT_NAME,
UC_HEADER_ID
)
VALUES
(
i,
x_mr_item_inst_tbl(i).mr_effectivity_id,
x_mr_item_inst_tbl(i).item_instance_id,
x_mr_item_inst_tbl(i).serial_number,
x_mr_item_inst_tbl(i).item_number,
x_mr_item_inst_tbl(i).inventory_item_id,
x_mr_item_inst_tbl(i).location,
x_mr_item_inst_tbl(i).status,
x_mr_item_inst_tbl(i).owner,
x_mr_item_inst_tbl(i).condition,
-- JKJain, NR Analysis and Forecasting
x_mr_item_inst_tbl(i).unit_name,
x_mr_item_inst_tbl(i).uc_header_id
);
x_mr_item_inst_tbl.DELETE(j);
p_insert_into_tmp_tbl IN VARCHAR2 := 'N',
x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'GET_APPLICABLE_MRS';
SELECT --NULL parent_item_instance_id,
--a.csi_item_instance_id,
--a.master_config_id,
a.unit_config_header_id
FROM ahl_unit_config_headers a
WHERE a.csi_item_instance_id = c_item_instance_id
/* Fix for bug#4052646
* AND a.parent_uc_header_id is not null -- Line commented
*/
AND a.parent_uc_header_id is null
AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(NVL(a.active_end_date,sysdate+1))
;
SELECT pc_node_id --amsriniv
FROM ahl_pc_nodes_b B
--WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select pc_node_id
from ahl_pc_associations itm, csi_item_instances csi,
(SELECT object_id
FROM csi_ii_relationships E
START WITH E.subject_id = c_instance_id
AND E.relationship_type_code = 'COMPONENT-OF'
CONNECT BY E.subject_id = PRIOR E.object_id
AND E.relationship_type_code = 'COMPONENT-OF'
union all
select c_instance_id
from dual) ii
where itm.association_type_flag = 'I'
and itm.unit_item_id = csi.inventory_item_id
and csi.instance_id = ii.object_id
UNION ALL
select pc_node_id
from ahl_pc_associations unit, ahl_unit_config_headers uc,
(SELECT object_id
FROM csi_ii_relationships E
START WITH E.subject_id = c_instance_id
AND E.relationship_type_code = 'COMPONENT-OF'
CONNECT BY E.subject_id = PRIOR E.object_id
AND E.relationship_type_code = 'COMPONENT-OF'
union
select c_instance_id
from dual) ii
where unit.association_type_flag = 'U'
and unit.unit_item_id = uc.unit_config_header_id
and uc.csi_item_instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
WITH ii AS (SELECT object_id
FROM csi_ii_relationships E
START WITH E.subject_id = c_instance_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
AND E.relationship_type_code = 'COMPONENT-OF'
CONNECT BY E.subject_id = PRIOR E.object_id
-- sunil- fix for bug7411016
AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
AND E.relationship_type_code = 'COMPONENT-OF'
UNION ALL
SELECT c_instance_id
FROM DUAL)
SELECT 'x' --pc_node_id --amsriniv
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select pc_node_id
from ahl_pc_associations itm, csi_item_instances csi,ii
where itm.association_type_flag = 'I'
and itm.unit_item_id = csi.inventory_item_id
and csi.instance_id = ii.object_id
UNION ALL
select pc_node_id
from ahl_pc_associations unit, ahl_unit_config_headers uc, ii
where unit.association_type_flag = 'U'
and unit.unit_item_id = uc.unit_config_header_id
and uc.csi_item_instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
SELECT /*+ CONNECT_BY_FILTERING */ 'x'
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
from ahl_pc_associations itm, csi_item_instances csi,
(select /*+ INDEX(E) */ object_id
FROM ahl_config_components E
START WITH E.subject_id = c_instance_id
CONNECT BY E.subject_id = PRIOR E.object_id
UNION ALL
SELECT c_instance_id
FROM DUAL) ii
where itm.association_type_flag = 'I'
and itm.unit_item_id = csi.inventory_item_id
and csi.instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
SELECT /*+ CONNECT_BY_FILTERING */ 'x'
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
from ahl_pc_associations unit, ahl_unit_config_headers uc,
(select /*+ INDEX(E) */ object_id
FROM ahl_config_components E
START WITH E.subject_id = c_instance_id
CONNECT BY E.subject_id = PRIOR E.object_id
UNION ALL
SELECT c_instance_id
FROM DUAL) ii
where unit.association_type_flag = 'U'
and unit.unit_item_id = uc.unit_config_header_id
and uc.csi_item_instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
SELECT distinct mrb.mr_header_id, mrb.repetitive_flag,
mrb.show_repetitive_code,
--mrb.preceding_mr_header_id,
mrb.copy_accomplishment_flag,
mrb.implement_status_code,
count_mr_descendents(c_mr_header_id) descendent_count,
decode (nvl(lcr.relationship_code, mrr.relationship_code ), 'INITIATES', 'INITIATED_BY', lcr.relationship_code) relationship_code,
(select MLC.mr_header_id
from ahl_mr_loop_chain_relns MLC
where MLC.mr_relationship_id = lcr.start_mr_relationship_id ) start_mr_header_id,
lcr.sequence_number
FROM ahl_mr_headers_b mrb,
ahl_mr_loop_chain_relns lcr,
ahl_mr_relationships mrr
WHERE mrb.mr_header_id = c_mr_header_id
AND lcr.mr_header_id(+) = mrb.mr_header_id
AND mrr.related_mr_header_id(+) = mrb.mr_header_id
AND mrr.relationship_code(+) = 'INITIATES';
SELECT repetitive_flag,
show_repetitive_code,
preceding_mr_header_id,
copy_accomplishment_flag,
implement_status_code,
count_mr_descendents(c_mr_header_id) descendent_count
FROM ahl_mr_headers_b --perf bug 6266738. using base tables.
WHERE mr_header_id = c_mr_header_id;
SELECT root.object_id
FROM csi_ii_relationships root
WHERE NOT EXISTS (SELECT 'x'
FROM csi_ii_relationships
WHERE subject_id = root.object_id
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
)
START WITH root.subject_id = p_instance_id
AND root.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
CONNECT BY PRIOR root.object_id = root.subject_id
AND root.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
cir.object_id, cir.subject_id, cir.position_reference
FROM ahl_mr_effectivities A,
ahl_mr_headers_app_v MR,
(select cir2.object_id,
cii2.instance_id subject_id,
nvl(uc.master_config_id, cir2.position_reference) position_reference,
0 depth
from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
where cii2.instance_id = c_instance_id
and cii2.instance_id = cir2.subject_id(+)
and cii2.instance_id = uc.csi_item_instance_id(+)
and uc.parent_uc_header_id(+) is null
and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
UNION ALL
SELECT a.object_id,
a.subject_id,
to_number(a.position_reference), level depth
FROM csi_ii_relationships a
WHERE c_components_flag = 'Y'
START WITH object_id = c_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))
CONNECT BY object_id = PRIOR subject_id
AND relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))) cir,
csi_item_instances cii
WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
AND MR.mr_header_id = A.mr_header_id
AND cir.subject_id = cii.instance_id
AND MR.mr_status_code = 'COMPLETE'
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.version_number in (SELECT max(MRM.version_number) from ahl_mr_headers_app_v MRM where SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1)) and title=mr.title and mr_status_code='COMPLETE' group by MRM.title)
AND (A.inventory_item_id = cii.inventory_item_id OR
(A.inventory_item_id IS NULL AND A.relationship_id IS NOT NULL AND
AHL_FMP_PVT.Instance_Matches_Path_Pos(cii.instance_id,A.relationship_id) = 'T'))
ORDER BY cir.depth, cir.subject_id; -- depth, subject_id
WITH cir AS (select --cir2.object_id,
cii2.instance_id subject_id,
--nvl(uc.master_config_id, cir2.position_reference) position_reference,
0 depth,
cii2.inventory_item_id
from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
where cii2.instance_id = c_instance_id
--and cii2.instance_id = cir2.subject_id(+)
--and cii2.instance_id = uc.csi_item_instance_id(+)
--and uc.parent_uc_header_id(+) is null
--and SYSDATE between trunc(nvl(uc.active_start_date,sysdate))
--and trunc(NVL(uc.active_end_date,sysdate+1))
UNION ALL
SELECT --a.object_id,
a.subject_id,
--to_number(a.position_reference),
level depth,
(select inventory_item_id
from csi_item_instances
where instance_id = a.subject_id) inventory_item_id
FROM csi_ii_relationships a
WHERE c_components_flag = 'Y'
START WITH object_id = c_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(active_start_date,sysdate))
AND trunc(NVL(active_end_date,sysdate+1))
CONNECT BY object_id = PRIOR subject_id
AND relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(active_start_date,sysdate))
AND trunc(NVL(active_end_date,sysdate+1))
)
SELECT * FROM (-- first query will match based on inventory items
SELECT A.mr_header_id, A.mr_effectivity_id,
-- A.relationship_id,
A.pc_node_id,
-- A.inventory_item_id,
-- cir.object_id,
cir.subject_id,
--cir.position_reference ,
cir.depth
FROM ahl_mr_headers_app_v MR, cir,
ahl_mr_effectivities A
WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
AND MR.mr_header_id = A.mr_header_id
AND MR.mr_status_code = 'COMPLETE'
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.version_number in (SELECT max(MRM.version_number)
from ahl_mr_headers_app_v MRM
where SYSDATE between trunc(MR.effective_from)
and trunc(nvl(MR.effective_to,SYSDATE+1))
and title=mr.title and mr_status_code='COMPLETE'
group by MRM.title)
AND A.inventory_item_id = cir.inventory_item_id
UNION ALL
-- query will match based on path position
SELECT A.mr_header_id, A.mr_effectivity_id, --A.relationship_id,
A.pc_node_id, --A.inventory_item_id,
--cir.object_id,
cir.subject_id,
--cir.position_reference,
cir.depth
FROM ahl_mr_headers_app_v MR, ahl_mc_path_positions mcp,
--ahl_mc_headers_b hdr, ahl_mc_relationships rel,
cir, ahl_mr_effectivities A
WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
AND MR.mr_header_id = A.mr_header_id
AND MR.mr_status_code = 'COMPLETE'
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND SYSDATE between trunc(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.version_number in (SELECT max(MRM.version_number)
from ahl_mr_headers_app_v MRM
where SYSDATE between trunc(MR.effective_from)
and trunc(nvl(MR.effective_to,SYSDATE+1))
and title=mr.title and mr_status_code='COMPLETE'
group by MRM.title)
AND a.relationship_id = mcp.path_position_id
AND (a.inventory_item_id IS NULL OR a.inventory_item_id = cir.inventory_item_id)
-- AND TO_NUMBER(cir.POSITION_REFERENCE) = rel.RELATIONSHIP_ID
-- AND REL.mc_header_id = HDR.mc_header_id
AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id) = 'T'
-- AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id,
-- mcp.ENCODED_PATH_POSITION, hdr.mc_id,
-- hdr.version_number, rel.position_key) = 'T'
) appl_mr
ORDER BY appl_mr.depth, appl_mr.subject_id; -- depth, subject_id
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
ii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
ii.serial_number --,
--(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
FROM ( SELECT a.subject_id
--FROM csi_ii_relationships a
FROM ahl_config_components a
START WITH object_id = c_instance_id
--AND relationship_type_code = 'COMPONENT-OF'
--AND SYSDATE between trunc(nvl(active_start_date,sysdate))
--AND trunc(NVL(active_end_date,sysdate+1))
CONNECT BY object_id = PRIOR subject_id
--AND relationship_type_code = 'COMPONENT-OF'
--AND SYSDATE between trunc(nvl(active_start_date,sysdate))
--AND trunc(NVL(active_end_date,sysdate+1))
) cir, csi_item_instances ii,
ahl_mr_effectivities A, ahl_mr_headers_b MR
WHERE --A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
ii.instance_id = cir.subject_id
AND A.inventory_item_id = ii.inventory_item_id
AND A.relationship_id is null
AND A.mr_header_id = MR.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND SYSDATE between trunc(MR.effective_from) AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.mr_status_code='COMPLETE'
AND MR.application_usg_code = c_appln_usg_code
AND MR.version_number in (SELECT /*+ push_subq no_unnest */ max(MRM.version_number)
FROM ahl_mr_headers_b MRM
WHERE mrm.title = MR.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
AND MRM.application_usg_code = c_appln_usg_code
)
-- ORDER BY ii.instance_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
ii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
ii.serial_number
FROM ( SELECT a.subject_id
FROM ahl_config_components a
START WITH object_id = c_instance_id
CONNECT BY object_id = PRIOR subject_id
) cir, csi_item_instances ii,
ahl_mr_effectivities A, ahl_mr_headers_b MR
WHERE A.mr_header_id = c_mr_header_id
AND ii.instance_id = cir.subject_id
AND A.inventory_item_id = ii.inventory_item_id
AND A.relationship_id is null
AND MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.application_usg_code = c_appln_usg_code
AND SYSDATE between trunc(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.mr_status_code='COMPLETE'
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_b MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND MRM.mr_status_code='COMPLETE'
AND MRM.application_usg_code = c_appln_usg_code
)
ORDER BY A.mr_effectivity_id, A.mr_header_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
ii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
ii.serial_number --,
--(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
FROM csi_item_instances ii,
ahl_mr_effectivities A, ahl_mr_headers_b MR
WHERE ii.instance_id = c_instance_id
--AND A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
AND A.inventory_item_id = ii.inventory_item_id
AND A.relationship_id is null
AND MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.application_usg_code = c_appln_usg_code
AND SYSDATE between trunc(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.mr_status_code='COMPLETE'
AND MR.version_number in (SELECT /*+ push_subq no_unnest */ max(MRM.version_number)
FROM ahl_mr_headers_b MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND MRM.mr_status_code='COMPLETE'
AND MRM.application_usg_code = c_appln_usg_code
)
--ORDER BY ii.instance_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
ii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
ii.serial_number --,
FROM csi_item_instances ii,
ahl_mr_effectivities A
WHERE ii.instance_id = c_instance_id
AND A.mr_header_id = c_mr_header_id
AND A.inventory_item_id = ii.inventory_item_id
AND A.relationship_id is null
AND exists (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 'x' from ahl_mr_headers_b MR
WHERE MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.application_usg_code = c_appln_usg_code
AND SYSDATE between trunc(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.mr_status_code='COMPLETE'
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_b MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND MRM.mr_status_code='COMPLETE'
AND MRM.application_usg_code = c_appln_usg_code
)
)
ORDER BY A.mr_effectivity_id, A.mr_header_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
cii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
cii.serial_number --,
--(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
FROM ahl_applicable_instances aai,csi_item_instances cii,
ahl_mr_effectivities A
WHERE
--A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
--AND
A.relationship_id IS NOT NULL
AND aai.position_id = A.relationship_id
AND aai.csi_item_instance_id = cii.instance_id
AND nvl(A.inventory_item_id,cii.inventory_item_id) = cii.inventory_item_id
AND exists (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 'x' from ahl_mr_headers_b MR
WHERE MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.application_usg_code = c_appln_usg_code
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_b MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
AND MRM.application_usg_code = c_appln_usg_code
)
)
--ORDER BY cii.instance_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
cii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
cii.serial_number --,
--(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
FROM ahl_applicable_instances aai,csi_item_instances cii,
ahl_mr_effectivities A
WHERE A.mr_header_id = c_mr_header_id
AND A.relationship_id IS NOT NULL
AND aai.position_id = A.relationship_id
AND aai.csi_item_instance_id = cii.instance_id
AND nvl(A.inventory_item_id,cii.inventory_item_id) = cii.inventory_item_id
AND exists (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 'x' from ahl_mr_headers_b MR
WHERE MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.application_usg_code = c_appln_usg_code
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_b MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
AND MRM.application_usg_code = c_appln_usg_code
)
)
--ORDER BY cii.instance_id;
SELECT A.mr_header_id, A.mr_effectivity_id, A.relationship_id, A.pc_node_id, A.inventory_item_id,
cir.object_id, cir.subject_id, cir.position_reference
FROM ahl_mr_effectivities A, ahl_mr_headers_app_v MR, ahl_mr_visit_types vis,
(select cir2.object_id,
cii2.instance_id subject_id,
nvl(uc.master_config_id, cir2.position_reference) position_reference,
0 depth
from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
where cii2.instance_id = c_instance_id
and cii2.instance_id = cir2.subject_id(+)
and cii2.instance_id = uc.csi_item_instance_id(+)
and uc.parent_uc_header_id(+) is null
and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
) cir,
csi_item_instances cii
WHERE MR.mr_header_id = A.mr_header_id
AND A.mr_header_id = vis.mr_header_id
AND vis.mr_visit_type_code = c_visit_type_code
AND cir.subject_id = cii.instance_id
AND MR.mr_status_code = 'COMPLETE'
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.version_number in (SELECT max(MRM.version_number) from ahl_mr_headers_app_v MRM where SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1)) and title=mr.title and mr_status_code='COMPLETE' group by MRM.title)
AND (A.inventory_item_id = cii.inventory_item_id OR
(A.inventory_item_id IS NULL AND A.relationship_id IS NOT NULL AND
AHL_FMP_PVT.Instance_Matches_Path_Pos(cii.instance_id,A.relationship_id) = 'T'))
ORDER BY cir.depth, cir.subject_id; -- depth, subject_id
WITH cir AS (select --cir2.object_id,
cii2.instance_id subject_id,
--nvl(uc.master_config_id, cir2.position_reference) position_reference,
0 depth,
cii2.inventory_item_id
from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
where cii2.instance_id = c_instance_id
--and cii2.instance_id = cir2.subject_id(+)
--and cii2.instance_id = uc.csi_item_instance_id(+)
--and uc.parent_uc_header_id(+) is null
--and SYSDATE between trunc(nvl(uc.active_start_date,sysdate))
--and trunc(NVL(uc.active_end_date,sysdate+1))
)
SELECT * FROM (-- first query will match based on inventory items
SELECT A.mr_header_id, A.mr_effectivity_id,
-- A.relationship_id,
A.pc_node_id,
-- A.inventory_item_id,
--cir.object_id,
cir.subject_id,
--cir.position_reference ,
cir.depth
FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, cir,
ahl_mr_effectivities A
WHERE MR.mr_header_id = A.mr_header_id
AND A.mr_header_id = vis.mr_header_id
AND vis.mr_visit_type_code = c_visit_type_code
AND MR.mr_status_code = 'COMPLETE'
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND SYSDATE between trunc(MR.effective_from) and trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.version_number in (SELECT max(MRM.version_number)
from ahl_mr_headers_app_v MRM
where SYSDATE between trunc(MR.effective_from)
and trunc(nvl(MR.effective_to,SYSDATE+1))
and title=mr.title and mr_status_code='COMPLETE'
group by MRM.title)
AND A.inventory_item_id = cir.inventory_item_id
UNION ALL
-- query will match based on path position
SELECT A.mr_header_id, A.mr_effectivity_id, --A.relationship_id,
A.pc_node_id, --A.inventory_item_id,
--cir.object_id,
cir.subject_id,
--cir.position_reference,
cir.depth
FROM ahl_mr_headers_app_v MR, ahl_mr_visit_types vis, ahl_mc_path_positions mcp,
--ahl_mc_headers_b hdr, ahl_mc_relationships rel,
cir, ahl_mr_effectivities A
WHERE MR.mr_header_id = A.mr_header_id
AND A.mr_header_id = vis.mr_header_id
AND vis.mr_visit_type_code = c_visit_type_code
AND MR.mr_status_code = 'COMPLETE'
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND SYSDATE between trunc(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND MR.version_number in (SELECT max(MRM.version_number)
from ahl_mr_headers_app_v MRM
where SYSDATE between trunc(MR.effective_from)
and trunc(nvl(MR.effective_to,SYSDATE+1))
and title=mr.title and mr_status_code='COMPLETE'
group by MRM.title)
AND a.relationship_id = mcp.path_position_id
AND (a.inventory_item_id IS NULL OR a.inventory_item_id = cir.inventory_item_id)
-- AND TO_NUMBER(cir.POSITION_REFERENCE) = rel.RELATIONSHIP_ID
-- AND REL.mc_header_id = HDR.mc_header_id
AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id) = 'T'
-- AND AHL_FMP_PVT.Instance_Matches_Path_Pos(cir.subject_id,A.relationship_id,
-- mcp.ENCODED_PATH_POSITION, hdr.mc_id,
-- hdr.version_number, rel.position_key) = 'T'
) appl_mr
ORDER BY appl_mr.depth, appl_mr.subject_id; -- depth, subject_id
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
cii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
cii.serial_number --,
--(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
FROM ( SELECT a.subject_id
--FROM csi_ii_relationships a
FROM ahl_config_components a
START WITH object_id = c_instance_id
--AND relationship_type_code = 'COMPONENT-OF'
--AND SYSDATE between trunc(nvl(active_start_date,sysdate))
--AND trunc(NVL(active_end_date,sysdate+1))
CONNECT BY object_id = PRIOR subject_id
--AND relationship_type_code = 'COMPONENT-OF'
--AND SYSDATE between trunc(nvl(active_start_date,sysdate))
--AND trunc(NVL(active_end_date,sysdate+1))
) cir, csi_item_instances cii, ahl_mr_visit_types vis,
ahl_mr_effectivities A
WHERE A.mr_header_id = vis.mr_header_id
AND vis.mr_visit_type_code = c_visit_type_code
AND cir.subject_id = cii.instance_id
AND A.inventory_item_id = cii.inventory_item_id
AND A.relationship_id is null
AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
WHERE MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_app_v MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
)
)
-- ORDER BY cii.instance_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
cii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE cii.instance_id = ciea1.instance_id(+) AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
AND ciea1.attribute_level(+) = 'GLOBAL') mfg_date,
cii.serial_number --,
--(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
FROM csi_item_instances cii,ahl_mr_visit_types vis,
ahl_mr_effectivities A
WHERE A.mr_header_id = vis.mr_header_id
AND vis.mr_visit_type_code = c_visit_type_code
AND cii.instance_id = c_instance_id
AND A.inventory_item_id = cii.inventory_item_id
AND A.relationship_id is null
AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
WHERE MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_app_v MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
)
)
--ORDER BY cii.instance_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
-- JKJain, NR Analysis and Forecasting
A.fleet_header_id,
cii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
cii.serial_number --,
--(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,
ahl_mr_effectivities A
WHERE A.mr_header_id = vis.mr_header_id
AND vis.mr_visit_type_code = c_visit_type_code
AND A.relationship_id IS NOT NULL
AND aai.position_id = A.relationship_id
AND nvl(A.inventory_item_id, cii.inventory_item_id) = cii.inventory_item_id
AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
WHERE MR.mr_header_id = A.mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND MR.version_number in (SELECT max(MRM.version_number)
FROM ahl_mr_headers_app_v MRM
WHERE mrm.title = mr.title
AND SYSDATE between trunc(MRM.effective_from)
AND trunc(nvl(MRM.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
)
)
--ORDER BY cii.instance_id;
SELECT 'x' from dual
WHERE exists (select 'x'
from ahl_mr_effectivities mre, ahl_mr_headers_b mr
where mre.relationship_id is not null
and mre.mr_header_id = mr.mr_header_id
and SYSDATE between trunc(mr.effective_from) AND trunc(nvl(mr.effective_to,SYSDATE+1))
-- commented for performance bug# 9434441 and added effective date chk directly.
--and exists (SELECT 'x' from ahl_mr_headers_app_v MR
-- WHERE MR.mr_header_id = mre.mr_header_id
-- AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
-- AND MR.version_number in (SELECT max(MRM.version_number)
-- FROM ahl_mr_headers_app_v MRM
-- WHERE mrm.title = mr.title
-- AND SYSDATE between trunc(MRM.effective_from)
-- AND trunc(nvl(MRM.effective_to,SYSDATE+1))
-- AND mr_status_code='COMPLETE'
-- )
-- )
);
SELECT 'x' from dual
WHERE exists (select 'x'
from ahl_mr_effectivities mre, ahl_mr_visit_types vis, ahl_mr_headers_b mr
where vis.mr_visit_type_code = p_visit_type_code
and mre.mr_header_id = vis.mr_header_id
and mre.mr_header_id = mr.mr_header_id
and mre.relationship_id is not null
and SYSDATE between trunc(mr.effective_from) AND trunc(nvl(mr.effective_to,SYSDATE+1))
-- commented for performance bug# 9434441 and added effective date chk directly.
--and exists (SELECT 'x' from ahl_mr_headers_app_v MR
-- WHERE MR.mr_header_id = vis.mr_header_id
-- AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
-- AND MR.version_number in (SELECT max(MRM.version_number)
-- FROM ahl_mr_headers_app_v MRM
-- WHERE mrm.title = mr.title
-- AND SYSDATE between trunc(MRM.effective_from)
-- AND trunc(nvl(MRM.effective_to,SYSDATE+1))
-- AND mr_status_code='COMPLETE'
-- )
-- )
);
SELECT 'x' from dual
WHERE exists (select 'x'
from ahl_mr_effectivities
where mr_header_id = p_mr_header_id
and relationship_id is not null);
select position_id
from ahl_applicable_instances
where csi_item_instance_id = c_instance_id;
SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
manufacture_date_from, manufacture_date_to, country_code
FROM ahl_mr_effectivity_dtls
WHERE mr_effectivity_id = c_mr_effectivity_id
ORDER BY exclude_flag ASC;
SELECT 'Y'
FROM ahl_mr_effectivity_ext_dtls a
WHERE a.mr_effectivity_id = c_mr_effectivity_id
AND rownum < 2;
SELECT csi.serial_number serial_number ,
to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
'm' manufacturer_id ,
'c' country_code
FROM csi_item_instances csi,
csi_inst_extend_attrib_v ciea1
WHERE csi.instance_id = ciea1.instance_id(+)
AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
AND ciea1.attribute_level(+) = 'GLOBAL'
AND csi.instance_id = c_item_instance_id;
SELECT 'X'
FROM ahl_mr_effectivities
WHERE mr_effectivity_id = c_mr_effectivity_id
AND inventory_item_id IS NOT NULL;
SELECT subject_id
FROM csi_ii_relationships
START WITH object_id = p_csi_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
ORDER BY level;
SELECT 'x'
FROM dual
WHERE EXISTS (select 'x' from ahl_applicable_instances);
l_bulk_insert_idx NUMBER := 0;
SELECT mr_header_id
FROM ahl_mr_relationships
WHERE related_mr_header_id = c_mr_header_id
AND relationship_code = 'TERMINATES'; -- effective date and complete validation
DELETE FROM AHL_CONFIG_COMPONENTS;
DELETE FROM ahl_applicable_instances;
l_subj_id_tbl.DELETE;
l_bulk_insert_idx := 0;
l_pc_node_id_tbl.delete;
IF (p_insert_into_tmp_tbl = 'N') OR (p_insert_into_tmp_tbl IS NULL) THEN
x_applicable_mr_tbl(l_index).mr_header_id := l_mr_header_id_tbl(i);
ELSIF (p_insert_into_tmp_tbl = 'Y') THEN
l_bulk_insert_idx := l_bulk_insert_idx + 1;
l_bulk_mr_hdr_id_tbl(l_bulk_insert_idx) := l_mr_header_id_tbl(i);
l_bulk_mr_eff_id_tbl(l_bulk_insert_idx) :=l_mr_effectivity_id_tbl(i);
l_bulk_csi_ii_id_tbl(l_bulk_insert_idx) := l_instance_id_tbl(i);
l_bulk_repetitive_flag_tbl(l_bulk_insert_idx) := l_get_mr_attri.repetitive_flag;
l_bulk_show_repetitive_tbl(l_bulk_insert_idx) := l_get_mr_attri.show_repetitive_code;
l_bulk_copy_accpsh_flag_tbl(l_bulk_insert_idx) := l_get_mr_attri.copy_accomplishment_flag;
l_bulk_implement_status_tbl(l_bulk_insert_idx) := l_get_mr_attri.implement_status_code;
l_bulk_descendent_count_tbl(l_bulk_insert_idx) := l_get_mr_attri.descendent_count;
l_bulk_reln_code_tbl(l_bulk_insert_idx) := l_get_mr_attri.relationship_code;
l_bulk_start_mr_hdr_tbl(l_bulk_insert_idx) := l_get_mr_attri.start_mr_header_id;
l_bulk_seq_num_tbl(l_bulk_insert_idx) := l_get_mr_attri.sequence_number;
IF (l_bulk_insert_idx >= 1000) THEN
-- insert into table ahl_applicable_mrs
FORALL z IN 1..l_bulk_insert_idx
INSERT INTO AHL_APPLICABLE_MRS (
MR_HEADER_ID,
MR_EFFECTIVITY_ID,
CSI_ITEM_INSTANCE_ID,
REPETITIVE_FLAG ,
SHOW_REPETITIVE_CODE,
PRECEDING_MR_HEADER_ID,
COPY_ACCOMPLISHMENT_CODE,
IMPLEMENT_STATUS_CODE,
DESCENDENT_COUNT,
ACCOMPLISH_TRIGGER_TYPE,
START_MR_HEADER_ID,
LOOP_CHAIN_SEQ_NUM,
PROCESS_STATUS_FLAG,
PROCESSING_ORDER
)
values (
l_bulk_mr_hdr_id_tbl(z),
l_bulk_mr_eff_id_tbl(z),
l_bulk_csi_ii_id_tbl(z),
l_bulk_repetitive_flag_tbl(z),
l_bulk_show_repetitive_tbl(z),
--l_bulk_preceding_mr_hdr_id_tbl(z),
null,
l_bulk_copy_accpsh_flag_tbl(z),
l_bulk_implement_status_tbl(z),
l_bulk_descendent_count_tbl(z),
l_bulk_reln_code_tbl(z),
l_bulk_start_mr_hdr_tbl(z),
l_bulk_seq_num_tbl(z),
'N', -- default
1 -- default
);
AHL_DEBUG_PUB.debug('l_bulk_insert_idx:'||l_bulk_insert_idx||': Inserted '||SQL%ROWCOUNT||' rows in AHL_APPLICABLE_MRS');
l_bulk_insert_idx := 0;
l_bulk_mr_hdr_id_tbl.delete;
l_bulk_mr_eff_id_tbl.delete;
l_bulk_csi_ii_id_tbl.delete;
l_bulk_repetitive_flag_tbl.delete;
l_bulk_show_repetitive_tbl.delete;
l_bulk_copy_accpsh_flag_tbl.delete;
l_bulk_implement_status_tbl.delete;
l_bulk_descendent_count_tbl.delete;
l_bulk_reln_code_tbl.delete;
l_bulk_start_mr_hdr_tbl.delete;
l_bulk_seq_num_tbl.delete;
END IF; -- l_bulk_insert_idx
END IF; -- p_insert_into_tmp_tbl
l_mr_header_id_tbl.delete;
l_mr_effectivity_id_tbl.delete;
l_mr_pc_node_id_tbl.delete;
l_instance_id_tbl.delete;
l_mfg_date_tbl.delete;
l_serial_num_tbl.delete;
l_fleet_id_tbl.delete;
IF (l_bulk_insert_idx > 0) THEN
-- insert into gtt ahl_applicable_mrs
FORALL z IN 1..l_bulk_insert_idx
INSERT INTO AHL_APPLICABLE_MRS (
MR_HEADER_ID,
MR_EFFECTIVITY_ID,
CSI_ITEM_INSTANCE_ID,
REPETITIVE_FLAG ,
SHOW_REPETITIVE_CODE,
PRECEDING_MR_HEADER_ID,
COPY_ACCOMPLISHMENT_CODE,
IMPLEMENT_STATUS_CODE,
DESCENDENT_COUNT,
ACCOMPLISH_TRIGGER_TYPE,
START_MR_HEADER_ID,
LOOP_CHAIN_SEQ_NUM,
PROCESS_STATUS_FLAG,
PROCESSING_ORDER
)
values (
l_bulk_mr_hdr_id_tbl(z),
l_bulk_mr_eff_id_tbl(z),
l_bulk_csi_ii_id_tbl(z),
l_bulk_repetitive_flag_tbl(z),
l_bulk_show_repetitive_tbl(z),
--l_bulk_preceding_mr_hdr_id_tbl(z),
null,
l_bulk_copy_accpsh_flag_tbl(z),
l_bulk_implement_status_tbl(z),
l_bulk_descendent_count_tbl(z),
l_bulk_reln_code_tbl(z),
l_bulk_start_mr_hdr_tbl(z),
l_bulk_seq_num_tbl(z),
'N', -- default
1 -- default
);
AHL_DEBUG_PUB.debug('Leftover:l_bulk_insert_idx:'||l_bulk_insert_idx||': Inserted '||SQL%ROWCOUNT||' rows in AHL_APPLICABLE_MRS');
END IF; -- l_bulk_insert_idx
DELETE FROM ahl_applicable_instances;
SELECT unit_config_header_id, name
FROM ahl_unit_config_headers
WHERE csi_item_instance_id IN ( SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1))
CONNECT BY subject_id = PRIOR object_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1))
)
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1));
SELECT unit_config_header_id, name
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = p_item_instance_id
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1));
SELECT A.instance_id
FROM csi_item_instances A
WHERE A.instance_id IN ( SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1))
CONNECT BY subject_id = PRIOR object_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1))
)
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1))
AND NOT EXISTS (SELECT 'X'
FROM csi_ii_relationships B
WHERE B.subject_id = A.instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND SYSDATE between trunc(nvl(B.active_start_date,sysdate)) and trunc(NVL(b.active_end_date,sysdate+1))
);
SELECT instance_id
FROM csi_item_instances
WHERE instance_id = p_item_instance_id
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1));
SELECT --count(distinct related_mr_header_id)
count(related_mr_header_id)
FROM ahl_mr_relationships
WHERE EXISTS (SELECT mr_header_id
FROM ahl_mr_headers_b M -- perf bug 6266738
WHERE mr_header_id = related_mr_header_id
AND mr_status_code = 'COMPLETE'
AND SYSDATE between trunc(effective_from) and trunc(nvl(effective_to,SYSDATE+1))
AND (version_number) in (SELECT max(M1.version_number)
from ahl_mr_headers_b M1
where M1.title = m.title -- perf bug 6266738
AND mr_status_code = 'COMPLETE'
AND SYSDATE between trunc(effective_from) and trunc(nvl(effective_to,SYSDATE+1))
)
)
START WITH mr_header_id = c_mr_header_id
AND relationship_code = 'PARENT'
CONNECT BY mr_header_id = PRIOR related_mr_header_id
AND relationship_code = 'PARENT';
SELECT count(amr.related_mr_header_id)
FROM ahl_mr_relationships amr
START WITH amr.mr_header_id = c_mr_header_id
AND amr.relationship_code = 'PARENT'
AND exists (select 'x' from ahl_mr_headers_b mr1
where mr1.mr_header_id = amr.related_mr_header_id
and mr1.version_number = (select max(mr2.version_number)
from ahl_mr_headers_b mr2
where mr2.title = mr1.title
and mr2.mr_status_code = 'COMPLETE'
and SYSDATE between trunc(mr2.effective_from)
and trunc(nvl(mr2.effective_to,SYSDATE+1))
)
)
CONNECT BY amr.mr_header_id = PRIOR amr.related_mr_header_id
AND amr.relationship_code = 'PARENT'
AND exists (select 'x' from ahl_mr_headers_b mr1
where mr1.mr_header_id = amr.related_mr_header_id
and mr1.version_number = (select max(mr2.version_number)
from ahl_mr_headers_b mr2
where mr2.title = mr1.title
and mr2.mr_status_code = 'COMPLETE'
and SYSDATE between trunc(mr2.effective_from)
and trunc(nvl(mr2.effective_to,SYSDATE+1))
)
);
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel, csi_ii_relationships csi_ii
WHERE csi_ii.subject_id = c_csi_instance_id
AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = REL.RELATIONSHIP_ID
AND REL.mc_header_id = HDR.mc_header_id;
SELECT csi.object_id
FROM csi_ii_relationships csi
WHERE csi.object_id IN
(SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
)
START WITH csi.subject_id = c_csi_instance_id
AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
CONNECT BY csi.subject_id = PRIOR csi.object_id
AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND CSI.POSITION_REFERENCE IS NOT NULL;
SELECT up.parent_mc_id, up.parent_version_number, up.parent_position_key
FROM ahl_uc_header_paths_v up
WHERE up.csi_instance_id = c_csi_instance_id;
SELECT hdr.mc_id, hdr.version_number, rel.position_key
FROM ahl_mc_headers_b hdr, ahl_mc_relationships rel, ahl_unit_config_headers uch
WHERE uch.csi_item_instance_id = c_csi_instance_id
AND TRUNC(nvl(uch.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(uch.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND hdr.mc_header_id = uch.master_config_id
AND rel.mc_header_id = hdr.mc_header_id
AND rel.parent_relationship_id IS NULL
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = uch.csi_item_instance_id
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CIR.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
AND TRUNC(nvl(CIR.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate));
SELECT ENCODED_PATH_POSITION
FROM AHL_MC_PATH_POSITIONS
WHERE PATH_POSITION_ID = c_path_position_id;
SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
manufacture_date_from, manufacture_date_to, country_code
FROM ahl_mr_effectivity_dtls -- perf bug 6266738
WHERE mr_effectivity_id = c_mr_effectivity_id
ORDER BY exclude_flag ASC;
SELECT csi.serial_number serial_number ,
to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
'm' manufacturer_id ,
'c' country_code
FROM csi_item_instances csi,
csi_inst_extend_attrib_v ciea1
WHERE csi.instance_id = ciea1.instance_id(+)
AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
AND ciea1.attribute_level(+) = 'GLOBAL'
AND csi.instance_id = c_item_instance_id;
SELECT exclude_flag, EFFECT_EXT_DTL_REC_TYPE, OWNER_ID, LOCATION_TYPE_CODE,
CSI_EXT_ATTRIBUTE_CODE, CSI_EXT_ATTRIBUTE_VALUE
FROM AHL_MR_EFFECTIVITY_EXT_DTLS
WHERE mr_effectivity_id = c_mr_effectivity_id
AND EFFECT_EXT_DTL_REC_TYPE IN ('OWNER','LOCATION','CSIEXTATTR')
ORDER BY EFFECT_EXT_DTL_REC_TYPE DESC,exclude_flag ASC; --exteremely improtant this --will break if changed
select CSI.OWNER_PARTY_ID,location_type_code from csi_item_instances CSI
where CSI.instance_id = c_item_instance_id;
SELECT ciea1.attribute_code,ciea1.attribute_value
FROM csi_item_instances csi,
csi_inst_extend_attrib_v ciea1
WHERE csi.instance_id = ciea1.instance_id(+)
AND csi.instance_id = c_item_instance_id;
SELECT mr_header_id, mr_effectivity_id
FROM ahl_mr_effectivities_app_v
WHERE inventory_item_id = c_inventory_item_id
ORDER BY mr_header_id, mr_effectivity_id;
SELECT mr_effectivity_id, repetitive_flag,
show_repetitive_code,
whichever_first_code,
implement_status_code
FROM ahl_mr_effectivities me, ahl_mr_headers_app_v mh
WHERE me.mr_header_id = mh.mr_header_id
AND mh.mr_header_id = c_mr_header_id
AND me.inventory_item_id = c_inventory_item_id;
SELECT related_mr_header_id
FROM ahl_mr_relationships_app_v
WHERE mr_header_id = c_mr_header_id
AND relationship_code = 'PARENT';
SELECT repetitive_flag,
show_repetitive_code,
whichever_first_code,
implement_status_code
FROM ahl_mr_headers_app_v
WHERE mr_header_id = c_mr_header_id;
SELECT instance_id, inventory_item_id
FROM csi_item_instances
WHERE instance_id = c_item_instance_id
AND nvl(active_start_date,sysdate) <= sysdate and
sysdate < NVL(active_end_date,sysdate+1);
DELETE FROM AHL_APPLICABLE_MRS;
INSERT INTO AHL_APPLICABLE_MRS
(
CSI_ITEM_INSTANCE_ID,
MR_HEADER_ID,
MR_EFFECTIVITY_ID,
REPETITIVE_FLAG ,
SHOW_REPETITIVE_CODE,
COPY_ACCOMPLISHMENT_CODE,
PRECEDING_MR_HEADER_ID,
IMPLEMENT_STATUS_CODE,
DESCENDENT_COUNT
)
Values
(
l_appl_mrs_tbl(i).item_instance_id,
l_appl_mrs_tbl(i).mr_header_id,
l_appl_mrs_tbl(i).mr_effectivity_id,
l_appl_mrs_tbl(i).repetitive_flag,
l_appl_mrs_tbl(i).show_repetitive_code,
l_appl_mrs_tbl(i).copy_accomplishment_flag,
l_appl_mrs_tbl(i).preceding_mr_header_id,
l_appl_mrs_tbl(i).implement_status_code,
l_appl_mrs_tbl(i).descendent_count
);
DELETE AHL_APPLICABLE_MRS A
WHERE NOT EXISTS (SELECT 'x' FROM AHL_MR_VISIT_TYPES
WHERE MR_HEADER_ID=A.MR_HEADER_ID
AND MR_VISIT_TYPE_CODE = P_VISIT_TYPE_CODE
);
DELETE AHL_APPLICABLE_MRS A
WHERE EXISTS ( select 'x' FROM
AHL_APPLICABLE_MR_RELNS REL
WHERE REL.related_mr_header_id = A.mr_header_id
AND REL.RELATED_CSI_ITEM_INSTANCE_ID = A.CSI_ITEM_INSTANCE_ID);
DELETE AHL_APPLICABLE_MR_RELNS A
WHERE NOT EXISTS (SELECT 'x' FROM AHL_APPLICABLE_MRS B
WHERE B.MR_HEADER_ID = A.ORIG_MR_HEADER_ID
AND B.CSI_ITEM_INSTANCE_ID = A.ORIG_CSI_ITEM_INSTANCE_ID);
SELECT /*+ CONNECT_BY_FILTERING */ 'x'
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
from ahl_pc_associations itm, csi_item_instances csi,
(select /*+ INDEX(E) */ object_id
FROM csi_ii_relationships E
START WITH E.subject_id = c_instance_id
AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
CONNECT BY E.subject_id = PRIOR E.object_id
AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
UNION ALL
SELECT c_instance_id
FROM DUAL) ii
where itm.association_type_flag = 'I'
and itm.unit_item_id = csi.inventory_item_id
and csi.instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
SELECT /*+ CONNECT_BY_FILTERING */ 'x'
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select /*+ UNNEST */ pc_node_id
from ahl_pc_associations unit, ahl_unit_config_headers uc,
(select /*+ INDEX(E) */ object_id
FROM csi_ii_relationships E
START WITH E.subject_id = c_instance_id
AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
CONNECT BY E.subject_id = PRIOR E.object_id
AND SYSDATE between trunc(nvl(E.active_start_date,sysdate)) and trunc(nvl(E.active_end_date,sysdate+1))
UNION ALL
SELECT c_instance_id
FROM DUAL) ii
where unit.association_type_flag = 'U'
and unit.unit_item_id = uc.unit_config_header_id
and uc.csi_item_instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
SELECT cir.subject_id, cir.object_id,
(select 'Y' from csi_ii_relationships where object_id = cir.subject_id
and relationship_type_code = 'COMPONENT-OF'
and trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
and rownum = 1) subj_child_exists
FROM csi_ii_relationships cir
START WITH cir.object_id = p_root_instance_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(cir.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(cir.active_end_date, sysdate+1))
CONNECT BY PRIOR cir.subject_id = cir.object_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(cir.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(cir.active_end_date, sysdate+1));
SELECT root.object_id
FROM csi_ii_relationships root
WHERE NOT EXISTS (SELECT 'x'
FROM csi_ii_relationships
WHERE subject_id = root.object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
)
START WITH root.subject_id = p_instance_id
AND root.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1))
CONNECT BY PRIOR root.object_id = root.subject_id
AND root.relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(root.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(root.active_end_date, sysdate+1));
INSERT INTO AHL_CONFIG_COMPONENTS (subject_id, object_id, root_object_id, subj_child_exists)
values (l_subj_id_tbl(i), l_obj_id_tbl(i), l_root_instance_id, nvl(l_child_exists_tbl(i), 'N'));
l_subj_id_tbl.delete;
l_obj_id_tbl.delete;
l_child_exists_tbl.delete;
SELECT 'x'
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select pc_node_id
from ahl_pc_associations itm, csi_item_instances csi,
(select object_id
FROM ahl_config_components E
START WITH E.subject_id = c_instance_id
CONNECT BY E.subject_id = PRIOR E.object_id
UNION ALL
SELECT c_instance_id
FROM DUAL) ii
where itm.association_type_flag = 'I'
and itm.unit_item_id = csi.inventory_item_id
and csi.instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
SELECT 'x'
FROM ahl_pc_nodes_b B
WHERE B.pc_node_id = c_pc_node_id
START WITH B.pc_node_id IN (select pc_node_id
from ahl_pc_associations unit, ahl_unit_config_headers uc,
(select object_id
FROM ahl_config_components E
START WITH E.subject_id = c_instance_id
CONNECT BY E.subject_id = PRIOR E.object_id
UNION ALL
SELECT c_instance_id
FROM DUAL) ii
where unit.association_type_flag = 'U'
and unit.unit_item_id = uc.unit_config_header_id
and uc.csi_item_instance_id = ii.object_id)
CONNECT BY B.pc_node_id = PRIOR B.parent_node_id;
SELECT repetitive_flag,
show_repetitive_code,
--preceding_mr_header_id,
copy_accomplishment_flag,
implement_status_code,
count_mr_descendents(c_mr_header_id) descendent_count
FROM ahl_mr_headers_b --perf bug 6266738. using base tables.
WHERE mr_header_id = c_mr_header_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
ii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
ii.serial_number
FROM ( SELECT a.subject_id
--FROM csi_ii_relationships a
FROM ahl_config_components a
START WITH object_id = c_instance_id
--AND relationship_type_code = 'COMPONENT-OF'
--AND SYSDATE between trunc(nvl(active_start_date,sysdate))
--AND trunc(NVL(active_end_date,sysdate+1))
CONNECT BY object_id = PRIOR subject_id
--AND relationship_type_code = 'COMPONENT-OF'
--AND SYSDATE between trunc(nvl(active_start_date,sysdate))
--AND trunc(NVL(active_end_date,sysdate+1))
) cir, csi_item_instances ii,
ahl_mr_effectivities A
WHERE A.mr_header_id = c_mr_header_id
AND ii.instance_id = cir.subject_id
AND A.inventory_item_id = ii.inventory_item_id
AND A.relationship_id is null
AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
WHERE MR.mr_header_id = c_mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND mr_status_code='COMPLETE'
)
ORDER BY A.mr_effectivity_id, A.mr_header_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
ii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
ii.serial_number
FROM csi_item_instances ii,
ahl_mr_effectivities A
WHERE ii.instance_id = c_instance_id
AND A.mr_header_id = c_mr_header_id
AND A.inventory_item_id = ii.inventory_item_id
AND A.relationship_id is null
AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
WHERE MR.mr_header_id = c_mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND mr_status_code='COMPLETE'
)
ORDER BY A.mr_effectivity_id, A.mr_header_id;
SELECT A.mr_header_id, A.mr_effectivity_id,
A.pc_node_id,
cii.instance_id subject_id,
(SELECT to_date(ciea1.attribute_value, 'DD/MM/YYYY') from csi_inst_extend_attrib_v ciea1
WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
AND ciea1.attribute_level = 'GLOBAL') mfg_date,
cii.serial_number
FROM ahl_applicable_instances aai,csi_item_instances cii,
ahl_mr_effectivities A
WHERE A.mr_header_id = c_mr_header_id
AND A.relationship_id IS NOT NULL
AND aai.position_id = A.relationship_id
AND aai.csi_item_instance_id = cii.instance_id
AND nvl(A.inventory_item_id,cii.inventory_item_id) = cii.inventory_item_id
AND exists (SELECT 'x' from ahl_mr_headers_app_v MR
WHERE MR.mr_header_id = c_mr_header_id
AND MR.program_type_code NOT IN ('MO_PROC') -- added in R12
AND mr_status_code='COMPLETE'
)
--ORDER BY cii.instance_id;
SELECT 'x' from dual
WHERE exists (select 'x'
from ahl_mr_effectivities
where mr_header_id = p_mr_header_id
and relationship_id is not null);
SELECT exclude_flag, serial_number_from, serial_number_to, manufacturer_id,
manufacture_date_from, manufacture_date_to, country_code
FROM ahl_mr_effectivity_dtls
WHERE mr_effectivity_id = c_mr_effectivity_id
ORDER BY exclude_flag ASC;
SELECT 'Y'
FROM ahl_mr_effectivity_ext_dtls a
WHERE a.mr_effectivity_id = c_mr_effectivity_id
AND rownum < 2;
SELECT subject_id
FROM csi_ii_relationships
START WITH object_id = p_csi_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
CONNECT BY PRIOR subject_id = object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
ORDER BY level;
SELECT 'x'
FROM dual
WHERE EXISTS (select 'x' from ahl_applicable_instances);
DELETE FROM AHL_CONFIG_COMPONENTS;
DELETE FROM ahl_applicable_instances;
l_subj_id_tbl.DELETE;
l_mr_header_id_tbl.delete;
l_mr_effectivity_id_tbl.delete;
l_mr_pc_node_id_tbl.delete;
l_instance_id_tbl.delete;
l_mfg_date_tbl.delete;
l_serial_num_tbl.delete;
DELETE FROM ahl_applicable_instances;