[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 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
FROM ahl_mr_instances_temp;
SELECT mr_effectivity_id, item_instance_id, serial_number, item_number,
inventory_item_id, location, status, owner, condition
FROM ahl_mr_instances_temp;
SELECT DISTINCT item_instance_id, serial_number, item_number,
inventory_item_id, location, status, owner, condition
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';
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);
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,
NULL,
NULL
);
x_mr_item_inst_tbl.DELETE(j);
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 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,
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
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 = NVL(c_mr_header_id, A.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 = 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(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
)
)
-- ORDER BY ii.instance_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 --,
--(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
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 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(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
)
)
--ORDER BY ii.instance_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 --,
--(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 '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(MR.effective_from)
AND trunc(nvl(MR.effective_to,SYSDATE+1))
AND mr_status_code='COMPLETE'
)
)
--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,
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
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(MR.effective_from)
AND trunc(nvl(MR.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,
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(MR.effective_from)
AND trunc(nvl(MR.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,
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(MR.effective_from)
AND trunc(nvl(MR.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
where mre.relationship_id is not null
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(MR.effective_from)
AND trunc(nvl(MR.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
where vis.mr_visit_type_code = p_visit_type_code
and mre.mr_header_id = vis.mr_header_id
and mre.relationship_id is not null
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(MR.effective_from)
AND trunc(nvl(MR.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 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;
DELETE FROM ahl_applicable_instances;
l_subj_id_tbl.DELETE;
l_pc_node_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;
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 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 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;
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);