[Home] [Help]
1434: l_notes VARCHAR2(2000);
1435: CURSOR Get_Visit_Instance_Workorders(p_visit_id IN NUMBER, p_instance_id IN NUMBER) IS
1436: SELECT WO.WORKORDER_ID WORKORDER_ID
1437: FROM
1438: AHL_VISITS_B VST, AHL_VISIT_TASKS_B TSK, AHL_WORKORDERS WO
1439: WHERE
1440: VST.VISIT_ID = p_visit_id
1441: AND VST.VISIT_ID = TSK.VISIT_ID
1442: AND TSK.INSTANCE_ID = p_instance_id
1541: SELECT CII.instance_id, CII.location_type_code,
1542: CII.instance_usage_code, CII.wip_job_id, CII.inv_locator_id,
1543: MIL.segment19, MIL.segment20, MIL.concatenated_segments inv_locator_name,
1544: MIL.subinventory_code, WO.workorder_name
1545: FROM CSI_ITEM_INSTANCES CII , MTL_ITEM_LOCATIONS_KFV MIL, AHL_WORKORDERS WO
1546: WHERE CII.instance_id = c_instance_id
1547: AND CII.inv_locator_id = MIL.inventory_location_id (+)
1548: AND CII.wip_job_id = WO.wip_entity_id(+);*/
1549:
1551: SELECT CII.instance_id, CII.location_type_code,
1552: CII.instance_usage_code, CII.wip_job_id, CII.inv_locator_id,
1553: MIL.segment19, MIL.segment20, MIL_kfv.concatenated_segments inv_locator_name,
1554: MIL.subinventory_code, WO.workorder_name
1555: FROM CSI_ITEM_INSTANCES CII , MTL_ITEM_LOCATIONS MIL, MTL_ITEM_LOCATIONS_KFV MIL_kfv, AHL_WORKORDERS WO
1556: WHERE CII.instance_id = c_instance_id
1557: AND CII.inv_locator_id = MIL.inventory_location_id (+)
1558: AND MIL_kfv.INVENTORY_LOCATION_ID(+) = MIL.INVENTORY_LOCATION_ID
1559: AND MIL_kfv.ORGANIZATION_ID(+) = MIL.ORGANIZATION_ID
1578:
1579: --Cursor to Get Visit Number and OSP number From Workorder.
1580: Cursor Get_Visit_And_OSP_From_Wip_Job(c_wip_entity_id IN NUMBER) IS
1581: SELECT vst.visit_number,osph.osp_order_number
1582: FROM ahl_visits_b vst, ahl_visit_tasks_b vts, ahl_workorders wo,ahl_osp_orders_b osph, ahl_osp_order_lines ospl
1583: WHERE
1584: wo.wip_entity_id = c_wip_entity_id
1585: and wo.visit_task_id IS NOT NULL
1586: and wo.visit_task_id = vts.visit_task_id
1620: --l_locator_name mtl_item_locations_kfv.concatenated_segments%TYPE;
1621: l_sub_inventory mtl_item_locations.subinventory_code%TYPE;
1622: l_locator_name VARCHAR2(204);
1623: -- end fix for bug 16304850
1624: l_job_name ahl_workorders.workorder_name%TYPE;
1625: l_osp_order ahl_osp_orders_b.osp_order_number%TYPE;
1626:
1627: BEGIN
1628:
1726: FROM (
1727: /* STHILAK bug #13641891 : commented the query and framed the modified query
1728: SELECT 1 quantity
1729: FROM AHL_PRD_DISPOSITIONS_B DISP,
1730: AHL_WORKORDERS WO,
1731: CSI_ITEM_INSTANCES CSI
1732: WHERE DISP.workorder_id = WO.workorder_id
1733: AND DISP.instance_id = CSI.instance_id
1734: AND CSI.wip_job_id = WO.wip_entity_id
1741:
1742: -- For non serial controlled items
1743: SELECT DISP.quantity quantity
1744: FROM AHL_PRD_DISPOSITIONS_B DISP,
1745: AHL_WORKORDERS WO,
1746: CSI_ITEM_INSTANCES CSI
1747: WHERE DISP.workorder_id = WO.workorder_id
1748: AND DISP.inventory_item_id = CSI.inventory_item_id
1749: AND CSI.wip_job_id = WO.wip_entity_id
1761: AND CSI.instance_id = c_instance_id
1762: AND CSI.location_type_code = 'WIP'
1763: AND CSI.serial_number IS NOT NULL
1764: AND disp.workorder_id IN
1765: (SELECT workorder_id FROM AHL_WORKORDERS WHERE visit_id =c_visit_id
1766: )
1767: AND NVL(DISP.status_code,'x') NOT IN ('TERMINATED','COMPLETED')
1768:
1769: UNION ALL
1778: AND NVL(DISP.status_code,'x') NOT IN ('TERMINATED','COMPLETED')
1779: AND NVL(DISP.lot_number,'x') = NVL(CSI.lot_number,'x')
1780: AND NVL(DISP.item_revision,'x') = NVL(CSI.inventory_revision,'x')
1781: AND DISP.workorder_id IN
1782: (SELECT workorder_id FROM AHL_WORKORDERS WHERE visit_id =c_visit_id
1783: )
1784: );
1785:
1786: l_count NUMBER;