"某料號" 在 "某帳本期間" 的 "在製成本, 工單成本更新"

在 Oracle ERP 中, 可利用 WIP_TRANSACTIONS 與 WIP_TRANSACTION_ACCOUNTS, 來取得 "某料號" 在 "某帳本期間" 的 "在製成本, 工單成本更新",

語法, 如下 :

 語法

SELECT WDJ.ORGANIZATION_ID
     , WDJ.WIP_ENTITY_ID
     , WE.WIP_ENTITY_NAME
     , WDJ.PRIMARY_ITEM_ID ASSEMBLY_ITEM_ID
     , MSI.SEGMENT1        ASSEMBLY_ITEM_CODE
     , WT.TRANSACTION_TYPE
     , SUM(DECODE( WT.TRANSACTION_TYPE, 5, WTA.BASE_TRANSACTION_VALUE
                                      , 6, WTA.BASE_TRANSACTION_VALUE
                                      )) WIP_VAR          -- 在本期關工單的差異金額
     , SUM(DECODE( WT.TRANSACTION_TYPE, 4, WTA.BASE_TRANSACTION_VALUE
                                      )) WIP_COST_UPDATE  -- 工單成本更新的金額
     , SUM(DECODE( WT.TRANSACTION_TYPE, 1, WTA.BASE_TRANSACTION_VALUE
                                      , 2, WTA.BASE_TRANSACTION_VALUE
                                      , 3, WTA.BASE_TRANSACTION_VALUE
                                      )) PERIOD_WIP_AMT   -- 本期工單製程投入的金額匯總
  FROM WIP_TRANSACTION_ACCOUNTS WTA
     , WIP_TRANSACTIONS         WT
     , WIP_DISCRETE_JOBS        WDJ
     , WIP_ENTITIES             WE
     , MTL_SYSTEM_ITEMS_B       MSI
 WHERE WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
   AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
   AND WDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND WDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID
   AND WDJ.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
   AND WT.TRANSACTION_ID = WTA.TRANSACTION_ID
   AND WTA.ACCOUNTING_LINE_TYPE = 7
   AND WT.ORGANIZATION_ID = &ORGANIZATION_ID
   AND WE.WIP_ENTITY_NAME = '&JOB_NAME'
   AND WT.ACCT_PERIOD_ID IN (SELECT ACCT_PERIOD_ID FROM ORG_ACCT_PERIODS WHERE PERIOD_NAME = '&PERIOD_NAME' AND ORGANIZATION_ID = &ORGANIZTION_ID) -- 以 PERIOD 做條件
   --AND WTA.TRANSACTION_DATE BETWEEN TO_DATE('&P_START_DATE', 'YYYYMMDD') AND TO_DATE('&END_DATE 23:59:59','YYYYMMDD HH24:MI:SS')                 -- 日期區間做條件
 GROUP BY WDJ.ORGANIZATION_ID
        , WDJ.WIP_ENTITY_ID
        , WE.WIP_ENTITY_NAME
        , WDJ.PRIMARY_ITEM_ID
        , MSI.SEGMENT1
        , WT.TRANSACTION_TYPE;