[Home] [Help]
208: -- inventory item id and instance id will be derived from the same task id.
209: -- So adding the inventory item Id derivation here based on instance id.
210: CURSOR get_inv_item_id (p_instance_id IN NUMBER) IS
211: SELECT inventory_item_id, serial_number
212: FROM csi_item_instances csi
213: WHERE csi.instance_id = p_instance_id;
214:
215: BEGIN
216:
749:
750: CURSOR get_quantity(c_instance_id NUMBER)
751: IS
752: SELECT quantity
753: FROM CSI_ITEM_INSTANCES
754: WHERE instance_id=c_instance_id
755: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ACTIVE_START_DATE,SYSDATE))
756: AND TRUNC(NVL(ACTIVE_END_DATE,SYSDATE));
757:
1097:
1098: CURSOR validate_item_instance(c_inv_item_id NUMBER,c_inst_id NUMBER)
1099: IS
1100: SELECT a.instance_id
1101: FROM CSI_ITEM_INSTANCES a
1102: WHERE a.inventory_item_id=c_inv_item_id
1103: AND a.instance_id=c_inst_id;
1104:
1105: l_instance_rec validate_item_instance%ROWTYPE;
1610: FND_MSG_PUB.ADD;
1611: ELSE
1612: SELECT COUNT(a.instance_id)
1613: INTO l_dummy_ctr
1614: FROM CSI_ITEM_INSTANCES a,
1615: MTL_SYSTEM_ITEMS b
1616: WHERE a.instance_id=p_prd_workorder_rec.item_instance_id
1617: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.active_start_date,SYSDATE))
1618: AND TRUNC(NVL(a.active_end_date,SYSDATE+1))
3773: IS
3774: SELECT
3775: csi.quantity
3776: FROM
3777: csi_item_instances csi,
3778: ahl_visit_tasks_b vtsk
3779: WHERE
3780: vtsk.instance_id = csi.instance_id AND
3781: vtsk.status_code <> 'DELETED' AND
4485:
4486: -- Fix for bug# 5347560.
4487: CURSOR chk_inst_in_job (p_workorder_id IN NUMBER) IS
4488: SELECT 'x'
4489: FROM CSI_ITEM_INSTANCES CII, AHL_WORKORDERS AWO
4490: WHERE CII.WIP_JOB_ID = AWO.WIP_ENTITY_ID
4491: AND AWO.workorder_id = p_workorder_id
4492: AND ACTIVE_START_DATE <= SYSDATE
4493: AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE))
4619: IS
4620: SELECT
4621: csi.quantity
4622: FROM
4623: csi_item_instances csi,
4624: ahl_visit_tasks_b vtsk
4625: WHERE
4626: vtsk.instance_id = csi.instance_id AND
4627: vtsk.status_code <> 'DELETED' AND
10810: AHL_WORKORDERS WO,
10811: AHL_VISITS_B VST,
10812: AHL_VISIT_TASKS_B VTS,
10813: WIP_DISCRETE_JOBS WDJ,
10814: CSI_ITEM_INSTANCES CSI
10815: WHERE
10816: WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
10817: AND WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
10818: AND VST.VISIT_ID = WO.VISIT_ID
11141: CURSOR tracked_material_csr( c_visit_id NUMBER ) IS
11142: select 'x'
11143: from dual
11144: where exists (select 'x'
11145: from ahl_workorders awo, csi_item_instances cii
11146: where awo.wip_entity_id = cii.wip_job_id
11147: and cii.location_type_code = 'WIP'
11148: and cii.ACTIVE_START_DATE <= SYSDATE
11149: and ((cii.ACTIVE_END_DATE IS NULL) OR (cii.ACTIVE_END_DATE >= SYSDATE))