[Home] [Help]
127:
128: -- Query for validating item segments and selecting item id
129: CURSOR Item_Cur(p_org_id number, p_item_name varchar2) IS
130: SELECT Inventory_Item_Id
131: FROM MTL_SYSTEM_ITEMS_KFV
132: WHERE Concatenated_Segments = p_item_name
133: AND Organization_Id = p_org_id
134: AND ENABLED_FLAG = 'Y'
135: AND ((START_DATE_ACTIVE IS NULL) OR (START_DATE_ACTIVE <= SYSDATE))
781: l_object_version_number NUMBER;
782: l_uom_code AHL_SCHEDULE_MATERIALS.UOM%TYPE;
783: l_quantity NUMBER;
784:
785: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
786: l_workorder_name ahl_workorders.workorder_name%TYPE;
787:
788: l_reservation_flag VARCHAR2(1);
789: l_sr_mtl_id_map_tbl SR_MTL_ID_MAP_TBL;
792: CURSOR Txn_Error_Cur(p_txn_Id NUMBER) IS
793: SELECT intf.ERROR_EXPLANATION ,intf.ERROR_CODE, kfv.concatenated_segments,
794: WO.workorder_name
795: --FROM MTL_MATERIAL_TRANSACTIONS_TEMP
796: FROM MTL_TRANSACTIONS_INTERFACE INTF, mtl_system_items_kfv kfv,
797: ahl_workorders WO
798: --WHERE TRANSACTION_TEMP_ID = p_txn_Id;
799: WHERE TRANSACTION_INTERFACE_ID = p_txn_Id
800: AND intf.inventory_item_id = kfv.inventory_item_id
807: --fix for bug# 13689288
808: CURSOR m_Txn_Error_Cur(p_inventory_item_id IN NUMBER, p_workorder_id IN NUMBER, p_hdr_txn_Id IN NUMBER) IS
809: SELECT intf.ERROR_EXPLANATION, intf.ERROR_CODE, kfv.concatenated_segments,
810: WO.workorder_name
811: FROM MTL_TRANSACTIONS_INTERFACE INTF, mtl_system_items_kfv kfv,
812: ahl_workorders WO
813: WHERE TRANSACTION_HEADER_ID = p_hdr_txn_Id
814: AND intf.inventory_item_id = p_inventory_item_id
815: AND wo.workorder_id = p_workorder_id
2031: -- Item id validation and selecting serial control code,lot control code values
2032: CURSOR AHL_ITEM_ID_CUR (p_org_id NUMBER, p_item NUMBER) IS
2033: SELECT SERIAL_NUMBER_CONTROL_CODE, LOT_CONTROL_CODE, REVISION_QTY_CONTROL_CODE,
2034: LOCATION_CONTROL_CODE,EAM_ITEM_TYPE, primary_uom_code, concatenated_segments
2035: FROM MTL_SYSTEM_ITEMS_kfv
2036: WHERE ORGANIZATION_ID = p_org_id
2037: AND INVENTORY_ITEM_ID = p_item
2038: AND ENABLED_FLAG = 'Y'
2039: AND ((START_DATE_ACTIVE IS NULL) OR (START_DATE_ACTIVE <= SYSDATE))
2204: l_curr_subinventory_code mtl_serial_numbers.current_subinventory_code%TYPE;
2205: l_curr_locator_id NUMBER;
2206: l_primary_uom_code mtl_system_items.primary_uom_code%TYPE;
2207: l_quantity NUMBER;
2208: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
2209: l_visit_id NUMBER;
2210: l_def_supply_subinv wip_parameters.default_pull_supply_subinv%TYPE;
2211: l_def_supply_locator_id NUMBER;
2212:
3882: /*
3883: --Query to select the item desccription
3884: CURSOR Item_Desc_Cur(p_org_id NUMBER, p_item_id NUMBER) IS
3885: SELECT DESCRIPTION
3886: FROM MTL_SYSTEM_ITEMS_KFV
3887: WHERE ORGANIZATION_ID = p_org_id
3888: AND INVENTORY_ITEM_ID = p_item_id;
3889: */
3890: -- Bug # 9373684 (FP for Bug # 9248655) -- end
5128: workorder_name AHL_WORKORDERS.WORKORDER_NAME%TYPE,
5129: organization_id AHL_VISITS_B.ORGANIZATION_ID%TYPE,
5130: workorder_operation_id AHL_PRD_DISPOSITIONS_B.WO_OPERATION_ID%TYPE,
5131: operation_sequence_num AHL_WORKORDER_OPERATIONS.OPERATION_SEQUENCE_NUM%TYPE,
5132: item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE,
5133: inventory_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE,
5134: item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE,
5135: immediate_disposition_code AHL_PRD_DISPOSITIONS_B.IMMEDIATE_DISPOSITION_CODE%TYPE,
5136: disposition_id AHL_PRD_DISPOSITIONS_B.DISPOSITION_ID%TYPE,
5130: workorder_operation_id AHL_PRD_DISPOSITIONS_B.WO_OPERATION_ID%TYPE,
5131: operation_sequence_num AHL_WORKORDER_OPERATIONS.OPERATION_SEQUENCE_NUM%TYPE,
5132: item_number MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE,
5133: inventory_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE,
5134: item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE,
5135: immediate_disposition_code AHL_PRD_DISPOSITIONS_B.IMMEDIATE_DISPOSITION_CODE%TYPE,
5136: disposition_id AHL_PRD_DISPOSITIONS_B.DISPOSITION_ID%TYPE,
5137: immediate_type FND_LOOKUP_VALUES.MEANING%TYPE,
5138: condition_code MTL_MATERIAL_STATUSES_TL.STATUS_CODE%TYPE,
5914: W.WIP_ENTITY_ID, -- added to filter chk_inst_relationship_csr for wip_job_id.
5915: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
5916: FROM
5917: AHL_WORKORDER_MTL_TXNS T,
5918: MTL_SYSTEM_ITEMS_KFV I,
5919: AHL_WORKORDERS W,
5920: AHL_VISITS_B V,
5921: AHL_VISIT_TASKS_B VT,
5922: CS_INCIDENTS_ALL_B C,
6029: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
6030: AHL_VISIT_TASKS_B VST_TASK,
6031: AHL_VISITS_B VST,
6032: AHL_WORKORDER_OPERATIONS WO_OP,
6033: MTL_SYSTEM_ITEMS_KFV MTL,
6034: MTL_UNITS_OF_MEASURE_VL UOM,
6035: -- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
6036: --MTL_ITEM_LOCATIONS_KFV MTL_LOC,
6037: MTL_ITEM_LOCATIONS MTL_LOC,
6108:
6109:
6110: FROM
6111: AHL_WORKORDER_MTL_TXNS A,
6112: MTL_SYSTEM_ITEMS_KFV B,
6113: MTL_UNITS_OF_MEASURE_VL UOM,
6114: -- modified to retrieve segment19 and 20 from base table to fix bug# 6611033.
6115: --MTL_ITEM_LOCATIONS_KFV D,
6116: MTL_ITEM_LOCATIONS D,
6243: l_bind_index := l_bind_index + 1;
6244: END IF;
6245:
6246: IF (P_prd_Mtltxn_criteria_rec.CONCATENATED_SEGMENTS IS NOT NULL) THEN
6247: l_mtl_txn_dtls := l_mtl_txn_dtls || ', MTL_SYSTEM_ITEMS_KFV I';
6248: l_mtl_txn_dtls_where := l_mtl_txn_dtls_where || ' AND T.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
6249: AND T.ORGANIZATION_ID = I.ORGANIZATION_ID AND I.ENABLED_FLAG = ''Y''
6250: AND ((I.START_DATE_ACTIVE IS NULL) OR (I.START_DATE_ACTIVE <= SYSDATE))
6251: AND ((I.END_DATE_ACTIVE IS NULL) OR (I.END_DATE_ACTIVE >= SYSDATE))
6589: DECODE(D.segment20,NULL,NULL,inv_project.GET_TASK_NUMBER(D.segment20)) Locator,
6590: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
6591: FROM
6592: CSI_ITEM_INSTANCES CSI,
6593: MTL_SYSTEM_ITEMS_KFV I,
6594: AHL_SEARCH_WORKORDERS_v W,
6595: WIP_PARAMETERS P,
6596: MTL_UNITS_OF_MEASURE_VL UOM,
6597: MTL_ITEM_LOCATIONS D,
6687: TYPE l_mtl_txn_rec_type IS RECORD (
6688: workorder_id AHL_WORKORDERS.WORKORDER_ID%TYPE,
6689: job_number AHL_WORKORDERS.WORKORDER_NAME%TYPE,
6690: job_status_meaning FND_LOOKUP_VALUES_VL.MEANING%TYPE,
6691: description MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE,
6692: organization_id AHL_VISITS_B.ORGANIZATION_ID%TYPE,
6693: inventory_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE,
6694: concatenated_segments MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE,
6695: serial_number CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE,
6690: job_status_meaning FND_LOOKUP_VALUES_VL.MEANING%TYPE,
6691: description MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE,
6692: organization_id AHL_VISITS_B.ORGANIZATION_ID%TYPE,
6693: inventory_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE,
6694: concatenated_segments MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE,
6695: serial_number CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE,
6696: instance_number CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE,
6697: lot_number CSI_ITEM_INSTANCES.LOT_NUMBER%TYPE,
6698: revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE,
6750: V.INV_LOCATOR_ID,
6751: (select msn.current_status from mtl_serial_numbers msn where msn.inventory_item_id = C.INVENTORY_ITEM_ID and msn.serial_number = C.serial_number) serial_num_status
6752: FROM AHL_WORKORDERS W,
6753: FND_LOOKUP_VALUES_VL MLU,
6754: MTL_SYSTEM_ITEMS_KFV I,
6755: AHL_VISITS_B V,
6756: CSI_ITEM_INSTANCES C,
6757: MTL_UNITS_OF_MEASURE_VL UOM,
6758: WIP_PARAMETERS P,