[Home] [Help]
160: WO.visit_id = p_visit_id
161: AND WO.visit_id = VTS.visit_id
162: AND WO.visit_task_id = VTS.visit_task_id
163: AND VTS.instance_id IN(
164: SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
165: UNION
166: SELECT
167: SUBJECT_ID INSTANCE_ID
168: FROM CSI_II_RELATIONSHIPS
589: WO.visit_id = p_visit_id
590: AND WO.visit_id = VTS.visit_id
591: AND WO.visit_task_id = VTS.visit_task_id
592: AND VTS.instance_id IN(
593: SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
594: UNION
595: SELECT
596: SUBJECT_ID INSTANCE_ID
597: FROM CSI_II_RELATIONSHIPS
922: AND WO.STATUS_CODE IN('4','5','7','12')
923: AND WO.visit_id = VTS.visit_id
924: AND WO.visit_task_id = VTS.visit_task_id
925: AND VTS.instance_id IN(
926: SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
927: UNION
928: SELECT
929: SUBJECT_ID INSTANCE_ID
930: FROM CSI_II_RELATIONSHIPS
962: AND WO.visit_id = VTS.visit_id
963: AND WO.visit_task_id = VTS.visit_task_id
964: AND VTS.instance_id IN
965: (SELECT instance_id
966: FROM csi_item_instances
967: WHERE INSTANCE_ID = p_item_instance_id
968: UNION
969: SELECT SUBJECT_ID INSTANCE_ID
970: FROM CSI_II_RELATIONSHIPS
1079: WO.visit_id = p_visit_id
1080: AND WO.visit_id = VTS.visit_id
1081: AND WO.visit_task_id = VTS.visit_task_id
1082: AND VTS.instance_id IN(
1083: SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
1084: UNION
1085: SELECT
1086: SUBJECT_ID INSTANCE_ID
1087: FROM CSI_II_RELATIONSHIPS
1142: WO.visit_id = p_visit_id
1143: AND WO.visit_id = VTS.visit_id
1144: AND WO.visit_task_id = VTS.visit_task_id
1145: AND VTS.instance_id IN(
1146: SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
1147: UNION
1148: SELECT
1149: SUBJECT_ID INSTANCE_ID
1150: FROM CSI_II_RELATIONSHIPS
1222: WO.visit_id = p_visit_id
1223: AND WO.visit_id = VTS.visit_id
1224: AND WO.visit_task_id = VTS.visit_task_id
1225: AND VTS.instance_id IN(
1226: SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
1227: UNION
1228: SELECT
1229: SUBJECT_ID INSTANCE_ID
1230: FROM CSI_II_RELATIONSHIPS
1661:
1662: CURSOR get_part_info(c_instance_id NUMBER) IS
1663: SELECT M.inventory_item_id,M.concatenated_segments,C.serial_number,
1664: C.QUANTITY
1665: FROM mtl_system_items_kfv M, csi_item_instances C
1666: WHERE C.instance_id = c_instance_id
1667: AND C.inventory_item_id = M.inventory_item_id
1668: AND C.inv_master_organization_id = M.organization_id;
1669:
1683: reln.position_ref_code,
1684: csi.INVENTORY_ITEM_ID,
1685: csi.QUANTITY Inst_qty,
1686: csi.UNIT_OF_MEASURE Inst_uom_code
1687: FROM ahl_mc_relationships reln, ahl_item_associations_b iasso, csi_item_instances csi
1688: WHERE csi.INSTANCE_ID = c_instance_id
1689: AND reln.relationship_id = c_mc_relationship_id
1690: AND iasso.item_group_id = reln.item_group_id
1691: AND iasso.inventory_item_id = CSI.INVENTORY_ITEM_ID
1953:
1954: CURSOR get_part_info(c_instance_id NUMBER) IS
1955: SELECT M.inventory_item_id,M.concatenated_segments,C.serial_number,
1956: DECODE(M.serial_number_control_code,'1','N','Y') is_serial_cntld,C.quantity
1957: FROM mtl_system_items_kfv M, csi_item_instances C
1958: WHERE C.instance_id = c_instance_id
1959: AND C.inventory_item_id = M.inventory_item_id
1960: AND C.inv_master_organization_id = M.organization_id;
1961:
1959: AND C.inventory_item_id = M.inventory_item_id
1960: AND C.inv_master_organization_id = M.organization_id;
1961:
1962: CURSOR instance_tree_csr(p_root_instance_id NUMBER) IS
1963: SELECT instance_id,to_number(NULL) parent_instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
1964: UNION
1965: SELECT
1966: SUBJECT_ID INSTANCE_ID,
1967: OBJECT_ID PARENT_INSTANCE_ID