[Home] [Help]
190: WHERE UNIT_OF_MEASURE = p_Uom_Desc;
191: -- Cursor for Wip job id.
192: CURSOR WIP_JOB_ID_CUR(p_wo_id NUMBER) IS
193: SELECT A.WIP_ENTITY_ID, C.ORGANIZATION_ID
194: FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C
195: WHERE A.WORKORDER_ID = p_wo_id
196: AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
197: AND C.VISIT_ID = B.VISIT_ID;
198: -- Cursor for Work order operation id.
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;
790:
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
801: AND intf.organization_id = kfv.organization_id
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
816: AND intf.inventory_item_id = kfv.inventory_item_id
860: * Bug #4918991
861: */
862: /*
863: SELECT 'x'
864: FROM AHL_PRD_DISPOSITIONS_B DISP, AHL_WORKORDERS WO, CSI_ITEM_INSTANCES CSI
865: WHERE
866: DISP.INSTANCE_ID = CSI.INSTANCE_ID AND
867: DISP.WORKORDER_ID = WO.WORKORDER_ID AND
868: WO.WIP_ENTITY_ID = CSI.WIP_JOB_ID AND
888: WO.WORKORDER_NAME,
889: WO.WIP_ENTITY_ID,
890: WDJ.REBUILD_ITEM_ID,
891: wdj.MAINTENANCE_OBJECT_ID
892: FROM AHL_WORKORDERS WO,
893: WIP_DISCRETE_JOBS WDJ,
894: AHL_VISIT_TASKS_B VTS,
895: AHL_WORKORDERS WO1
896: WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
891: wdj.MAINTENANCE_OBJECT_ID
892: FROM AHL_WORKORDERS WO,
893: WIP_DISCRETE_JOBS WDJ,
894: AHL_VISIT_TASKS_B VTS,
895: AHL_WORKORDERS WO1
896: WHERE WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
897: AND WO.STATUS_CODE NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
898: AND WO.VISIT_ID = WO1.VISIT_ID
899: AND wo1.workorder_id = C_WORKORDER_ID
2018: -- Added project and locator for ER# 5854712.
2019: CURSOR AHL_WIPJOB_CUR (p_org_id NUMBER, p_wo_id NUMBER, p_wipjob IN NUMBER) IS
2020: SELECT A.STATUS_CODE, C.Visit_id, C.Inv_Locator_Id, C.project_id, B.project_task_id,
2021: LOC.subinventory_code
2022: FROM AHL_WORKORDERS A, AHL_VISIT_TASKS_B B, AHL_VISITS_B C, MTL_ITEM_LOCATIONS LOC
2023: WHERE A.WIP_ENTITY_ID = p_wipjob
2024: AND A.WORKORDER_ID = p_wo_id
2025: AND B.VISIT_TASK_ID = A.VISIT_TASK_ID
2026: AND C.VISIT_ID = B.VISIT_ID
3872:
3873: -- QWuey to select the work order dependent data to be passed to Schedule materials API.
3874: CURSOR Workop_Det_Cur(p_wo_id NUMBER) IS
3875: SELECT B.VISIT_ID,C.VISIT_TASK_ID,B.PROJECT_ID,C.PROJECT_TASK_ID
3876: FROM AHL_WORKORDERS A, AHL_VISITS_B B, AHL_VISIT_TASKS_B C
3877: WHERE A.WORKORDER_ID = p_wo_id
3878: AND A.VISIT_TASK_ID = C.VISIT_TASK_ID
3879: AND C.VISIT_ID = B.VISIT_ID;
3880: -- Bug # 9373684 (FP for Bug # 9248655) -- start
4949: */
4950: SELECT SUM(NVL(a.net_quantity,0)) INTO l_disp_qty
4951: FROM
4952: AHL_MTL_RET_DISPOSITIONS_V a,
4953: AHL_WORKORDERS b
4954: WHERE
4955: a.workorder_id=b.workorder_id
4956: AND b.master_workorder_flag = 'N'
4957: --AND b.status_code NOT IN ('17' , '22')
5027: W.wip_entity_id,
5028: (select inv_locator_id from ahl_visits_b where visit_id = w.visit_id) inv_locator_id
5029: FROM
5030: AHL_MTL_RET_DISPOSITIONS_V D,
5031: AHL_WORKORDERS W,
5032: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
5033: AHL_VISITS_B V,
5034: AHL_VISIT_TASKS_B VT,
5035: CS_INCIDENTS_ALL_B C,
5124: l_mtl_txn_dtls_where VARCHAR2(10000);
5125:
5126: TYPE l_disp_rec_type IS RECORD (
5127: workorder_id AHL_PRD_DISPOSITIONS_B.WORKORDER_ID%TYPE,
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,
5151: concatenated_segments MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE,
5152: quantity AHL_PRD_DISPOSITIONS_B.QUANTITY%TYPE,
5153: net_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE,
5154: Wo_Net_Total_Qty NUMBER,
5155: wip_entity_id AHL_WORKORDERS.WIP_ENTITY_ID%TYPE,
5156: inv_locator_id AHL_VISITS_B.INV_LOCATOR_ID%TYPE,
5157: --JKJain, Bug 9268076
5158: path_position_id AHL_PRD_DISPOSITIONS_B.PATH_POSITION_ID%TYPE
5159: );
5277: MTL_UNITS_OF_MEASURE_VL UOM,
5278: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = ''AHL_JOB_STATUS'' AND LANGUAGE= USERENV(''LANG'')) WO_STS,
5279: WIP_PARAMETERS WP,
5280: MTL_ITEM_LOCATIONS_KFV L,
5281: AHL_WORKORDERS W ';
5282:
5283:
5284: l_mtl_txn_dtls_where := '
5285: WHERE D.WORKORDER_ID = W.WORKORDER_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,
5923: WIP_DISCRETE_JOBS WDJ,
6024: (select inv_locator_id from ahl_visits_b where visit_id = vst.visit_id) inv_locator_id,
6025: (Select csi.instance_number from csi_item_instances csi where csi.instance_id = TXNS.INSTANCE_ID) instance_number
6026: FROM
6027: AHL_WORKORDER_MTL_TXNS TXNS,
6028: AHL_WORKORDERS WO,
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,
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,
6117: AHL_WORKORDERS E,
6118: (SELECT LOOKUP_CODE, MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'AHL_JOB_STATUS' AND LANGUAGE= USERENV('LANG')) WO_STS,
6119: AHL_VISITS_B V,
6120: AHL_VISIT_TASKS_B VT,
6121: AHL_WORKORDER_OPERATIONS F,
6179: l_mtl_txn_dtls VARCHAR2(10000);
6180: l_mtl_txn_dtls_where VARCHAR2(10000);
6181:
6182: TYPE l_mtlTxn_rec_type IS RECORD (
6183: workorder_id AHL_WORKORDERS.WORKORDER_ID%TYPE,
6184: organization_id AHL_WORKORDER_MTL_TXNS.ORGANIZATION_ID%TYPE,
6185: inventory_item_id AHL_WORKORDER_MTL_TXNS.INVENTORY_ITEM_ID%TYPE,
6186: serial_number AHL_WORKORDER_MTL_TXNS.SERIAL_NUMBER%TYPE,
6187: lot_number AHL_WORKORDER_MTL_TXNS.LOT_NUMBER%TYPE,
6186: serial_number AHL_WORKORDER_MTL_TXNS.SERIAL_NUMBER%TYPE,
6187: lot_number AHL_WORKORDER_MTL_TXNS.LOT_NUMBER%TYPE,
6188: revision AHL_WORKORDER_MTL_TXNS.REVISION%TYPE,
6189: instance_id AHL_WORKORDER_MTL_TXNS.INSTANCE_ID%TYPE,
6190: wip_entity_id AHL_WORKORDERS.WIP_ENTITY_ID%TYPE,
6191: inv_locator_id AHL_VISITS_B.INV_LOCATOR_ID%TYPE
6192: );
6193: l_mtlTxn_rec l_mtlTxn_rec_type;
6194:
6215: W.WIP_ENTITY_ID,
6216: V.INV_LOCATOR_ID
6217: FROM
6218: AHL_WORKORDER_MTL_TXNS T,
6219: AHL_WORKORDERS W,
6220: AHL_WORKORDER_OPERATIONS O,
6221: AHL_VISITS_B V ' ;
6222:
6223: l_mtl_txn_dtls_where := '
6684: l_mtl_txn_dtls VARCHAR2(10000);
6685: l_mtl_txn_dtls_where VARCHAR2(10000);
6686:
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,
6685: l_mtl_txn_dtls_where VARCHAR2(10000);
6686:
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,
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,
6699: instance_id CSI_ITEM_INSTANCES.INSTANCE_ID%TYPE,
6700: wip_entity_id AHL_WORKORDERS.WIP_ENTITY_ID%TYPE,
6701: quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE,
6702: uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE,
6703: unit_of_measure MTL_UNITS_OF_MEASURE_VL.UNIT_OF_MEASURE%TYPE,
6704: default_pull_supply_subinv WIP_PARAMETERS.DEFAULT_PULL_SUPPLY_SUBINV%TYPE,
6748: || DECODE(D.SEGMENT20, NULL,NULL,INV_PROJECT.GET_TASK_NUMBER(D.SEGMENT20)) LOCATOR,
6749:
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,
7668: -- Cursor to check if there exists RTS workorder in the same visit as of the given workorder and for the given instance.
7669: CURSOR Chk_Rts_Workorder(C_WORKORDER_ID NUMBER, c_ins_id NUMBER)
7670: IS
7671: SELECT 'x'
7672: FROM AHL_WORKORDERS WO,
7673: AHL_VISIT_TASKS_B VTS,
7674: AHL_WORKORDERS WO1
7675: WHERE WO.STATUS_CODE NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
7676: AND WO.VISIT_ID = WO1.VISIT_ID
7670: IS
7671: SELECT 'x'
7672: FROM AHL_WORKORDERS WO,
7673: AHL_VISIT_TASKS_B VTS,
7674: AHL_WORKORDERS WO1
7675: WHERE WO.STATUS_CODE NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
7676: AND WO.VISIT_ID = WO1.VISIT_ID
7677: AND wo1.workorder_id = C_WORKORDER_ID
7678: AND VTS.VISIT_TASK_ID = WO.VISIT_TASK_ID