[Home] [Help]
824: * Bug #4918991
825: */
826: /*
827: SELECT 'x'
828: FROM AHL_PRD_DISPOSITIONS_B DISP, AHL_WORKORDERS WO, CSI_ITEM_INSTANCES CSI
829: WHERE
830: DISP.INSTANCE_ID = CSI.INSTANCE_ID AND
831: DISP.WORKORDER_ID = WO.WORKORDER_ID AND
832: WO.WIP_ENTITY_ID = CSI.WIP_JOB_ID AND
1686: p_job_id IN NUMBER,
1687: p_serial_num IN VARCHAR2) IS
1688: --p_wip_location NUMBER) IS (fix for bug# 5221513).
1689: SELECT INSTANCE_ID
1690: FROM CSI_ITEM_INSTANCES CII
1691: WHERE INVENTORY_ITEM_ID = p_item_id
1692: AND WIP_JOB_ID = p_job_id
1693: AND SERIAL_NUMBER = p_serial_num
1694: AND ACTIVE_START_DATE <= SYSDATE
1706: --Query to validate the instance for the job during issue if there is a serial number avaialble
1707: CURSOR CSI_ISSUE_SER_ITEM_CUR(p_item_id IN NUMBER,
1708: p_serial_num IN VARCHAR2)IS
1709: SELECT INSTANCE_ID
1710: FROM CSI_ITEM_INSTANCES CII
1711: WHERE INVENTORY_ITEM_ID = p_item_id
1712: AND SERIAL_NUMBER = p_serial_num
1713: AND ACTIVE_START_DATE <= SYSDATE
1714: AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
2445: OR p_x_ahl_mtltxn_rec.Serial_Number = FND_API.G_MISS_CHAR) THEN
2446:
2447: -- Non serialized item:
2448:
2449: l_sql := 'SELECT INSTANCE_ID FROM CSI_ITEM_INSTANCES CII ';
2450: l_sql := l_sql || ' WHERE 1=1';
2451: --l_sql := l_Sql || ' AND INV_MASTER_ORGANIZATION_ID=:b1';
2452: l_sql := l_sql || ' AND INVENTORY_ITEM_ID =:b2' ;
2453: l_sql := l_sql || ' AND WIP_JOB_ID=:b3';
4353: p_ITEM_Revision IN VARCHAR2,
4354: p_lot_number IN VARCHAR2,
4355: p_Serial_Number IN VARCHAR2) IS
4356: SELECT 'x'
4357: FROM CSI_ITEM_INSTANCES CII
4358: WHERE CII.inventory_item_id = p_INVENTORY_ITEM_ID
4359: AND nvl(cii.inventory_revision,'1') = nvl(p_ITEM_Revision, '1')
4360: AND nvl(cii.lot_number, '1') = nvl(p_lot_number, '1')
4361: AND nvl(cii.serial_number,'1') = nvl(p_serial_number, '1')
4791: -- and is located in the wip job.
4792: CURSOR chk_inst_relationship_csr (p_instance_id IN NUMBER,
4793: p_wip_entity_id IN NUMBER) IS
4794: SELECT 'x'
4795: FROM CSI_ITEM_INSTANCES CII
4796: WHERE INSTANCE_ID = p_instance_id
4797: AND ACTIVE_START_DATE <= SYSDATE
4798: AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
4799: AND QUANTITY > 0
5077: ||
5078: DECODE(D.segment20,NULL,NULL,inv_project.GET_TASK_NUMBER(D.segment20)) Locator,
5079: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
5080: FROM
5081: CSI_ITEM_INSTANCES CSI,
5082: MTL_SYSTEM_ITEMS_KFV I,
5083: AHL_SEARCH_WORKORDERS_v W,
5084: WIP_PARAMETERS P,
5085: MTL_UNITS_OF_MEASURE_VL UOM,