wip

最新推荐文章于 2024-02-01 16:42:07 发布

转载最新推荐文章于 2024-02-01 16:42:07 发布 · 266 阅读

本文提供了一系列针对WIP模块的SQL查询语句，包括工单基本信息查询、制程信息查询、物料领退记录查询及工单状态检查函数等。通过这些查询，可以有效地跟踪并管理工单的状态及进度。

部署运行你感兴趣的模型镜像

通過mrp發放不成功的工單可以一下sql查詢到

SELECT B.*,A.*
FROM WIP_JOB_SCHEDULE_INTERFACE A,
WIP_INTERFACE_ERRORS B
WHERE 1=1
AND A.INTERFACE_ID=B.INTERFACE_ID
and a.LAST_UPDATED_BY=9050
-------------------
工单单头主要信息之sql
SELECT WE.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
WE.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
MSI.SEGMENT1 ASSEMBLY,
DECODE(WDJ.JOB_TYPE,1,'Standard',3,'Non-standard') JOB_TYPE,
ML.MEANING STATUS_TYPE,
WDJ.START_QUANTITY,
WDJ.QUANTITY_COMPLETED,
WDJ.QUANTITY_SCRAPPED,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS WDJ,
MFG_LOOKUPS ML,
MTL_SYSTEM_ITEMS_B MSI,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND WE.PRIMARY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND ML.LOOKUP_TYPE='WIP_JOB_STATUS'
AND WDJ.STATUS_TYPE=ML.LOOKUP_CODE
AND OOD.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND TO_CHAR(WDJ.SCHEDULED_START_DATE,'YYYYMM')='200407'
AND WE.ORGANIZATION_ID=9
AND WE.WIP_ENTITY_NAME='WFY1-353045'
ORDER BY OOD.ORGANIZATION_CODE

-----------------------
显示工单制程信息中主要栏位
SELECT WE.ORGANIZATION_ID,
WE.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
WO.OPERATION_SEQ_NUM,
WO.QUANTITY_IN_QUEUE,
WO.QUANTITY_RUNNING,
WO.QUANTITY_SCRAPPED,
WO.QUANTITY_REJECTED,
WO.QUANTITY_COMPLETED
FROM WIP_OPERATIONS WO,
WIP_ENTITIES WE
WHERE WO.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WO.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND WE.ORGANIZATION_ID=9
AND WE.WIP_ENTITY_NAME='WFY1-353045'

----------------
工单领料,退料,入库,退库信息
SELECT WE.WIP_ENTITY_NAME,
ITEMA.SEGMENT1 AS "Assembly",
ITEMB.SEGMENT1 AS "item",
MTLTYPE.TRANSACTION_TYPE_NAME,
TRANMTL.TRANSACTION_QUANTITY,
TO_CHAR(TRANMTL.TRANSACTION_DATE,'yyyy/mm/dd') TRANSACTION_DATE,
TRANMTL.TRANSACTION_UOM,
TRANMTL.SUBINVENTORY_CODE
FROM MTL_MATERIAL_TRANSACTIONS TRANMTL,
WIP_ENTITIES WE,
WIP_DISCRETE_JOBS WDJ,
MTL_SYSTEM_ITEMS ITEMA,
MTL_SYSTEM_ITEMS ITEMB,
MTL_TRANSACTION_TYPES MTLTYPE
WHERE TRANMTL.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND TRANMTL.TRANSACTION_SOURCE_ID=WE.WIP_ENTITY_ID
AND TRANMTL.ORGANIZATION_ID=ITEMB.ORGANIZATION_ID
AND WE.ORGANIZATION_ID=ITEMA.ORGANIZATION_ID
AND WDJ.PRIMARY_ITEM_ID=ITEMA.INVENTORY_ITEM_ID
AND ITEMB.INVENTORY_ITEM_ID=TRANMTL.INVENTORY_ITEM_ID
AND TRANMTL.TRANSACTION_TYPE_ID=MTLTYPE.TRANSACTION_TYPE_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND TRANMTL.TRANSACTION_SOURCE_TYPE_ID IN (4,5)
AND TRANMTL.ORGANIZATION_ID=255
AND WE.WIP_ENTITY_NAME='60593'
---------------------
判断工单是否完工的函数
CREATE OR REPLACE FUNCTION CUX_WIP_QTY_OK
(ENTITY_ID NUMBER,
WIP_DATE DATE
) RETURN NUMBER IS
L_START_QTY NUMBER;
COM_QTY1 NUMBER;
COM_QTY2 NUMBER;
L_CURRENT_DATE DATE;
L_WIP_DATE DATE;
BEGIN
--GET JOB START QTY
SELECT WDJ.START_QUANTITY
INTO L_START_QTY
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = ENTITY_ID;

L_CURRENT_DATE:=SYSDATE;
L_WIP_DATE:=WIP_DATE;

IF L_WIP_DATE-L_CURRENT_DATE<=0 THEN

---GET LAST SEQ TO MOVE QUANTITY
SELECT SUM(NVL(WMT.TRANSACTION_QUANTITY,0))
INTO COM_QTY1
FROM WIP_MOVE_TRANSACTIONS WMT,
WIP_OPERATIONS WO
WHERE WMT.WIP_ENTITY_ID=ENTITY_ID
AND WMT.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WMT.TO_OPERATION_SEQ_NUM=WO.OPERATION_SEQ_NUM
AND WO.NEXT_OPERATION_SEQ_NUM IS NULL
AND WMT.TO_INTRAOPERATION_STEP_TYPE IN (3,4,5) --1 QUEUE,2,RUNNING,3,TO MOVE,4,REJECT,5,SCRAP
AND WMT.FM_INTRAOPERATION_STEP_TYPE IN (1,2)
AND TRUNC(WMT.TRANSACTION_DATE)<=TRUNC(L_WIP_DATE);

---GET LAST SEQ POSITION QUANTITY
SELECT SUM(NVL(WMT.TRANSACTION_QUANTITY,0))
INTO COM_QTY2
FROM WIP_MOVE_TRANSACTIONS WMT,
WIP_OPERATIONS WO
WHERE WMT.WIP_ENTITY_ID=ENTITY_ID
AND WMT.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WMT.FM_OPERATION_SEQ_NUM=WO.OPERATION_SEQ_NUM
AND WO.NEXT_OPERATION_SEQ_NUM IS NULL
AND WMT.TO_INTRAOPERATION_STEP_TYPE IN (1,2) --1 QUEUE,2,RUNNING,3,TO MOVE,4,REJECT,5,SCRAP
AND WMT.FM_INTRAOPERATION_STEP_TYPE IN (3,4,5)
AND TRUNC(WMT.TRANSACTION_DATE)<=TRUNC(L_WIP_DATE);

ELSE

SELECT SUM(NVL(WMT.TRANSACTION_QUANTITY,0))
INTO COM_QTY1
FROM WIP_MOVE_TRANSACTIONS WMT,
WIP_OPERATIONS WO
WHERE WMT.WIP_ENTITY_ID=ENTITY_ID
AND WMT.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WMT.TO_OPERATION_SEQ_NUM=WO.OPERATION_SEQ_NUM
AND WO.NEXT_OPERATION_SEQ_NUM IS NULL
AND WMT.TO_INTRAOPERATION_STEP_TYPE IN (3,4,5) --1 QUEUE,2,RUNNING,3,TO MOVE,4,REJECT,5,SCRAP
AND WMT.FM_INTRAOPERATION_STEP_TYPE IN (1,2)
AND TRUNC(WMT.TRANSACTION_DATE)<=TRUNC(L_CURRENT_DATE);

---GET LAST SEQ POSITION QUANTITY
SELECT SUM(NVL(WMT.TRANSACTION_QUANTITY,0))
INTO COM_QTY2
FROM WIP_MOVE_TRANSACTIONS WMT,
WIP_OPERATIONS WO
WHERE WMT.WIP_ENTITY_ID=ENTITY_ID
AND WMT.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WMT.FM_OPERATION_SEQ_NUM=WO.OPERATION_SEQ_NUM
AND WO.NEXT_OPERATION_SEQ_NUM IS NULL
AND WMT.FM_INTRAOPERATION_STEP_TYPE IN (3,4,5) --1 QUEUE,2,RUNNING,3,TO MOVE,4,REJECT,5,SCRAP
AND WMT.TO_INTRAOPERATION_STEP_TYPE IN (1,2)
AND TRUNC(WMT.TRANSACTION_DATE)<=TRUNC(L_CURRENT_DATE);
end if ;

IF L_START_QTY>(NVL(COM_QTY1,0)-NVL(COM_QTY2,0)) THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
END CUX_WIP_QTY_OK;.

------------------------------
在工單制令closed出現錯誤時，導致工單處于status_type=15 時，確認下列操作即可

1.工單搬站talbe

SELECT WIP_ENTITY_ID
FROM WIP_MOVE_TXN_INTERFACE
Where wip_entity_id=881707

使用下列語句Update

update WIP_MOVE_TXN_INTERFACE
       set group_id = NULL,
           transaction_id = NULL,
           process_status = 1,
           OVERCOMPLETION_TRANSACTION_QTY= NULL,
           OVERCOMPLETION_PRIMARY_QTY= NULL

Where and TRANSACTION_ID=4255;
2.工單計算公費table
              SELECT WIP_ENTITY_ID
                         FROM WIP_COST_TXN_INTERFACE
                Where wip_entity_id=881707

開工單工費的數據,需要check cost manager是否開啟,

使用下列語句Update

update WIP_COST_TXN_INTERFACE
       set group_id = NULL,
           transaction_id = NULL,
           PROCESS_STATUS = 1

Where and TRANSACTION_ID=4255;

3.領退料入庫等交易table

              SELECT TRANSACTION_SOURCE_ID
                         FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
                Where mmtt.transaction_source_id=881707
                     AND TRANSACTION_SOURCE_TYPE_ID = 5

運用下列語句Update

   update MTL_MATERIAL_TRANSACTIONS_TEMP
       set process_flag = 'Y',
           lock_flag = 'N',
           transaction_mode = 3,
           error_code = NULL,
           error_explanation = NULL
where PROCESS_FLAG in ('Y','E')
4.領退料入庫等交易切分錄table
                  SELECT TRANSACTION_SOURCE_ID
                         FROM MTL_MATERIAL_TRANSACTIONS
                         WHERE COSTED_FLAG IN ('N','E')

                  AND   TRANSACTION_SOURCE_TYPE_ID = 5
                And transaction_source_id=881707
           'N'為暫時還未切分錄,需要check cost manager是否開啟,開啟完畢

'E'為切錯誤,用下列語句Update后等待cost manager處理

   update MTL_MATERIAL_TRANSACTIONS mmt
        set mmt.COSTED_FLAG = 'N',
           mmt.TRANSACTION_GROUP_ID = NULL,
           mmt.PRIOR_COST = null,
           mmt.NEW_COST = null,
           mmt.ACTUAL_COST = null
where mmt.COSTED_FLAG ='E'
5.工單關閉需用用到的temp table
         Select * From   WIP_DJ_CLOSE_TEMP

有數據可直接delete

--------------------
WIP模块：外协工单组件领退料状况 04月29日(星期二)
并且与采购申请单进行关联
SELECT WE.WIP_ENTITY_NAME,
A.SEGMENT1 ASSEMBLY,
PRH.SEGMENT1 REQUISITION_NUM,
LU1.MEANING STATUS_TYPE,
WDJ.CLASS_CODE,
FU.USER_NAME,
F.LAST_NAME CREATE_NAME,
WDJ.SCHEDULED_START_DATE,
WDJ.SCHEDULED_COMPLETION_DATE,
WDJ.START_QUANTITY,
WDJ.QUANTITY_COMPLETED,
B.SEGMENT1 COMPONENT,
WRO.QUANTITY_PER_ASSEMBLY,
WRO.REQUIRED_QUANTITY ,
(WRO.QUANTITY_PER_ASSEMBLY*WDJ.QUANTITY_COMPLETED) FACT_QTY,
WRO.QUANTITY_ISSUED
FROM WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE,
PO_REQUISITION_HEADERS_ALL PRH,
PO_REQUISITION_LINES_ALL PRL,
WIP_REQUIREMENT_OPERATIONS WRO,
MTL_SYSTEM_ITEMS_B A ,
MTL_SYSTEM_ITEMS_B B,
MFG_LOOKUPS LU1,
FND_USER FU,
PER_PEOPLE_F F
WHERE WDJ.STATUS_TYPE IN (1,3,4,6)
AND WDJ.ORGANIZATION_ID=104
AND WDJ.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRH.INTERFACE_SOURCE_CODE='WIP'
AND PRL.DESTINATION_ORGANIZATION_ID=WE.ORGANIZATION_ID
AND PRL.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WE.CREATED_BY=FU.USER_ID
AND WDJ.CLASS_CODE='外协加工'
AND FU.EMPLOYEE_ID=F.PERSON_ID
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND WRO.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND A.ORGANIZATION_ID=WE.ORGANIZATION_ID
AND A.INVENTORY_ITEM_ID=WE.PRIMARY_ITEM_ID
AND B.ORGANIZATION_ID=WRO.ORGANIZATION_ID
AND B.INVENTORY_ITEM_ID=WRO.INVENTORY_ITEM_ID
ORDER BY F.LAST_NAME,WDJ.SCHEDULED_START_DATE