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

在 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;
天聖經:神的心不僅在神的聖言當中,也在神所創造的萬物之中,天涯海角每一處都有神的心
Related Posts Plugin for WordPress, Blogger...