LCM 工單物料耗用表

 
  
 --******** LCM 工單物料耗用表 ************
 SELECT WE.WIP_ENTITY_NAME
       ,WDJ.DATE_RELEASED
       ,LU2.MEANING WIP_STATUS
       ,MSI.SEGMENT1  ITEM_NUM
       ,MSI.DESCRIPTION ITEM_TYPE
       ,MSI2.SEGMENT1 ITEM_NO -- 發料 編號
       ,MSI2.DESCRIPTION ITEM_DESC -- 發料名稱
       ,MSI2.PRIMARY_UOM_CODE UOM  --單位
       ,WRO.QUANTITY_PER_ASSEMBLY --單元用量
       ,WDJ.START_QUANTITY  -- 開始數量
       ,WDJ.QUANTITY_COMPLETED  --完工 數量
       ,WRO.OPERATION_SEQ_NUM  OPERATION_CODE  --站別
       ,LU3.MEANING SUPPLY_TYPE_ITEM
       ,WRO.REQUIRED_QUANTITY  --需求數量
       ,WRO.QUANTITY_ISSUED  -- 發料數量
      
       ,(WRO.REQUIRED_QUANTITY- WRO.QUANTITY_ISSUED ) AS DIFF_QUANTITY -- 差異數量
      
       , CASE WHEN (WRO.REQUIRED_QUANTITY -WRO.QUANTITY_ISSUED  ) !=0 THEN 
            DECODE(NVL(WRO.REQUIRED_QUANTITY,0) ,0,-100,
                  ROUND( (WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED  )
                             / WRO.REQUIRED_QUANTITY ,4)*100 )
            ELSE 0
        END DIFF_RATE -- 差異率%
        
 FROM WIP.WIP_ENTITIES WE
     ,WIP.WIP_DISCRETE_JOBS WDJ
     ,APPS.MTL_SYSTEM_ITEMS_B MSI
     ,APPS.MTL_SYSTEM_ITEMS_B MSI2
     ,APPS.MFG_LOOKUPS  LU1
     ,APPS.MFG_LOOKUPS  LU2
     ,APPS.MFG_LOOKUPS  LU3
     ,WIP.WIP_REQUIREMENT_OPERATIONS WRO
     ,APPS.BOM_DEPARTMENTS BD  -- 生產部門
  /*   ,(  SELECT MMT.INVENTORY_ITEM_ID
              ,MMT.ORGANIZATION_ID
              ,MMT.TRANSACTION_SOURCE_ID
              ,sum(MMT.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY
              ,MMT.TRANSACTION_UOM
             -- ,MMT.PRIMARY_QUANTITY
              ,MMT.OPERATION_SEQ_NUM
              ,MMT.DEPARTMENT_ID
              ,MMT.TRANSACTION_TYPE_ID
         FROM INV.MTL_TRANSACTION_TYPES MTT
          ,INV.MTL_MATERIAL_TRANSACTIONS  MMT
          ,WIP.WIP_ENTITIES WE
         WHERE -- MTT.TRANSACTION_TYPE_NAME='WIP Component Return'
          -- AND
           MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
           AND MMT.ORGANIZATION_ID IN (426,427)  
          -- AND MMT.TRANSACTION_SOURCE_ID=581674
           AND MMT.TRANSACTION_SOURCE_ID=WE.WIP_ENTITY_ID
          -- AND WE.WIP_ENTITY_NAME='D092DE021'
         GROUP BY  MMT.INVENTORY_ITEM_ID
              ,MMT.ORGANIZATION_ID
              ,MMT.TRANSACTION_SOURCE_ID
              ,MMT.TRANSACTION_UOM
             -- ,MMT.PRIMARY_QUANTITY
              ,MMT.OPERATION_SEQ_NUM
              ,MMT.DEPARTMENT_ID 
              ,MMT.TRANSACTION_TYPE_ID
            ) TRA  --退料事務
       */    
 WHERE WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
   AND WE.ORGANIZATION_ID IN (426,427)
   AND WDJ.PRIMARY_ITEM_ID=MSI.INVENTORY_ITEM_ID
   AND WDJ.ORGANIZATION_ID=MSI.ORGANIZATION_ID
   AND WDJ.WIP_SUPPLY_TYPE=LU1.LOOKUP_CODE
   AND LU1.LOOKUP_TYPE = 'WIP_SUPPLY'
   AND LU2.LOOKUP_TYPE='WIP_JOB_STATUS'
   AND WDJ.STATUS_TYPE=LU2.LOOKUP_CODE
   AND WRO.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
   AND LU3.LOOKUP_TYPE = 'WIP_SUPPLY'
   AND WRO.WIP_SUPPLY_TYPE=LU3.LOOKUP_CODE
   AND MSI2.INVENTORY_ITEM_ID=WRO.INVENTORY_ITEM_ID
   AND MSI2.ORGANIZATION_ID=WRO.ORGANIZATION_ID
   AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
   AND BD.DEPARTMENT_CODE='LCM'
 /* 
   AND WRO.WIP_ENTITY_ID=TRA.TRANSACTION_SOURCE_ID(+)
   AND WRO.OPERATION_SEQ_NUM=TRA.OPERATION_SEQ_NUM(+)
   AND WRO.INVENTORY_ITEM_ID=TRA.INVENTORY_ITEM_ID(+)
 */ 
   AND WE.WIP_ENTITY_NAME lIKE  'M0821E156'      
   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/729024/viewspace-675612/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/729024/viewspace-675612/