工单常用表

--离散任务视图
SELECT primary_item_id,        --物料ID
       START_QUANTITY            --工单起始数量
  FROM WIP_DISCRETE_JOBS_V       --离散任务视图
   WHERE  WIP_ENTITY_NAME='49011' ---工单号（任务名称） -----根据WIP_ENTITY_NAME查出WIP_ENTITY_ID
   
--离散任务表
SELECT START_QUANTITY 
   FROM WIP_DISCRETE_JOBS   --离散任务表
   
SELECT * FROM WIP_DISCRETE_JOBS_V WHERE  WIP_ENTITY_NAME='49011'    ---根据WIP_ENTITY_NAME查出WIP_ENTITY_ID
SELECT * FROM wip_requirement_operations WHERE WIP_ENTITY_ID='74163'---根据WIP_ENTITY_ID查出inventory_item_id

--在离散任务里由工单ID查出工单表中的工单号
SELECT we.wip_entity_name, we.wip_entity_id
  FROM wip_entities we,--工单表
       wip_discrete_jobs wdj--离散任务表
 WHERE we.organization_id = wdj.organization_id
   AND we.wip_entity_id = wdj.wip_entity_id
   AND wdj.status_type = 3
   AND wdj.organization_id = :PARAMETER.ORG_ID
   AND EXISTS
 (SELECT 1
          FROM wip_operations wo
         WHERE wo.organization_id = wdj.organization_id
           AND wo.wip_entity_id = wdj.wip_entity_id
           AND (wo.quantity_in_queue + wo.quantity_running +
               wo.quantity_waiting_to_move + wo.quantity_rejected) > 0)
 ORDER BY we.wip_entity_name

SELECT WE.WIP_ENTITY_NAME
     FROM WIP_ENTITIES WE,
          WIP_DISCRETE_JOBS WDJ
    WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
      AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID

--BOM资源表 
SELECT br.resource_id,--资源ID
       br.resource_type--资源序号
  FROM bom_resources br
 WHERE br.resource_code IN ('M-FTJ-01','P-FT-01') --根据名字查找相应的资源ID，防止ID改变时无法使用
   AND br.organization_id = 84--P_ORGANIZATION_ID
--BOM部门表     
SELECT * 
  FROM bom_departments

SELECT * 
  FROM WIP_OPERATIONS_V--工序视图 
 WHERE operation_seq_num = 1 
ORDER BY CREATION_DATE DESC

--**********工单接口表*********************  
SELECT * FROM wip_txn_interface_errors ORDER BY CREATION_DATE DESC
SELECT * FROM wip_move_txn_interface ORDER BY creation_date DESC--工序移动
 
SELECT * FROM wip_job_schedule_interface ORDER BY CREATION_DATE DESC--生成工单
SELECT * FROM wip_job_dtls_interface ORDER BY CREATION_DATE DESC--工单上母材边角料
SELECT * FROM wip_interface_errors ORDER BY CREATION_DATE DESC
--退料
SELECT * FROM MTL_TRANSACTIONS_INTERFACE A ORDER BY creation_date DESC
SELECT * FROM mtl_transaction_lots_interface ORDER BY creation_date DESC
SELECT *FROM MTL_interface_errors t ORDER BY t.creation_date DESC

DELETE FROM wip_job_schedule_interface;
DELETE FROM wip_job_dtls_interface;
DELETE FROM wip_interface_errors;
DELETE FROM MTL_TRANSACTIONS_INTERFACE;
DELETE FROM mtl_transaction_lots_interface;
DELETE FROM MTL_interface_errors;

--工单事务处理表
SELECT WTV.operation_seq_num,--工序     
       WTV.transaction_quantity,--数量
       WTV.resource_code--资源代码
  FROM WIP_TRANSACTIONS_V WTV

--工单状态表              
SELECT * 
  FROM MFG_LOOKUPS 
 WHERE LOOKUP_TYPE = 'WIP_JOB_STATUS'
   AND LOOKUP_CODE IN (1,3,4,5,6,7,12,14,15) 
ORDER BY LOOKUP_CODE

SELECT * from MTL_SYSTEM_ITEMS_FVL --取最小包装量  FIXED_LOT_MULTIPLIER_MIR
SELECT * from WIP_SCHEDULE_GROUPS --取生产部门描述

SELECT BSO.OPERATION_CODE OPER_CODE --取工序代码
  FROM BOM_STANDARD_OPERATIONS BSO
  
SELECT attribute2 --取使用机台
  FROM WIP_DISCRETE_JOBS_V

SELECT wmtv.ATTRIBUTE2 --取报废原因 
  FROM WIP_MOVE_TRANSACTIONS_V wmtv--移动事务处理（装配件）