傳統庫齡不外乎都是用 MTL_MATERIAL_TRANSACTIONS MMT回推庫齡
但此寫法時間久了會執行很慢
研究了一下 MTL_ONHAND_QUANTITIES_DETAIL MOQ也可以用來計算庫齡
缺點就是沒辦法回推某個時間點的庫齡，因為MOQ是即時的
MOQ會記錄所有入庫的記錄
MOQ.DATE_RECEIVED就是實際入庫的時間
再利用 MOQ.CREATE_TRANSACTION_ID就可以知道對應的 MMT的來源
以此可以判斷要怎麼調整日期判斷
目前公司是採先進先出
故此會先將 MOQ.DATE_RECEIVED較早的庫存先出庫
並利用 MOQ.UPDATE_TRANSACTION_ID 可以得知最後一筆扣庫存的MMT資料 (但我覺得沒什麼用，還要研究有沒有所以記錄)
另外，目前公司是要把 MMT.TRANSACTION_TYPE_ID IN (44, 18, 1350) 以外的 MMT資料當作最後一個庫齡期間
避免員工利用移倉或雜收發調整系統庫齡
SELECT REPLACE(MSI.SEGMENT1, ',', '@') AS ITEM_NUM,
MSI.ATTRIBUTE2,
REPLACE(MSI.DESCRIPTION, ',', '@') AS ITEM_DESC,
MSI.PRIMARY_UOM_CODE AS UOM,
MIC1.CATEGORY_CONCAT_SEGS AS COST_TYPE,
MIC2.CATEGORY_CONCAT_SEGS AS INV_TYPE,
FV.MEANING AS INV_TYPE_NAME,
FV.TAG AS PM,
TRUNC(MOQ.DATE_RECEIVED) AS DATE_RECEIVED,
CASE
WHEN MMT.TRANSACTION_TYPE_ID IN (44, 18, 1350) THEN
CASE
WHEN MMT.TRANSACTION_TYPE_ID = 1350 AND MMT.ATTRIBUTE10 IS NOT NULL THEN
TO_DATE(MMT.ATTRIBUTE10, 'YYYY-MM-DD')
ELSE
TRUNC(MMT.TRANSACTION_DATE)
END
ELSE
DATE '1900-01-01'
END mmt_date,
TRUNC(SYSDATE) - TRUNC(MOQ.DATE_RECEIVED) AS DAYS,
TRUNC(SYSDATE)
- CASE
WHEN MMT.TRANSACTION_TYPE_ID IN (44, 18, 1350) THEN
CASE
WHEN MMT.TRANSACTION_TYPE_ID = 1350 AND MMT.ATTRIBUTE10 IS NOT NULL THEN
TO_DATE(MMT.ATTRIBUTE10, 'YYYY-MM-DD')
ELSE
TRUNC(MMT.TRANSACTION_DATE)
END
ELSE
DATE '1900-01-01'
END DAYS_NEW, --雜收發自動歸類到最後一期
MOQ.SUBINVENTORY_CODE,
MOQ.PRIMARY_TRANSACTION_QUANTITY AS TRX_QTY,
ACE_CST_ITEM_COST.GET_ITEM_COST_V1(MOQ.ORGANIZATION_ID, MOQ.INVENTORY_ITEM_ID, TO_CHAR(SYSDATE, 'YYYY/MM/DD')) AS ITEM_COST, --取成本
MOQ.ORGANIZATION_ID,
MOQ.INVENTORY_ITEM_ID,
MIC1.CATEGORY_ID AS COST_CATEGORY_ID,
MIC2.CATEGORY_ID AS INV_CATEGORY_ID,
OOD.ORGANIZATION_NAME,
NVL((SELECT CONVERSION_RATE --轉台幣匯率
FROM GL_DAILY_RATES_V
WHERE CONVERSION_DATE = TRUNC(SYSDATE)
AND FROM_CURRENCY = GL.CURRENCY_CODE
AND TO_CURRENCY = 'TWD'
AND USER_CONVERSION_TYPE = 'Qisda'), 1) TW_CONVERSION_RATE,
AIV.VENDOR_NAME,
moq.SUBINVENTORY_CODE
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ,
MTL_SYSTEM_ITEMS MSI,
MTL_ITEM_CATEGORIES_V MIC1,
MTL_ITEM_CATEGORIES_V MIC2,
FND_LOOKUP_VALUES FV,
ORG_ORGANIZATION_DEFINITIONS OOD,
GL.GL_LEDGERS GL,
ACEINV_ITEM_EARLY_VENDOR AIV, --客製
MTL_MATERIAL_TRANSACTIONS MMT
WHERE MOQ.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MOQ.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MIC2.CATEGORY_SET_ID = 1
AND MIC2.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC2.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC1.CATEGORY_SET_ID = 1100000021
AND MIC1.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC1.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC2.CATEGORY_CONCAT_SEGS = FV.LOOKUP_CODE(+)
AND OOD.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND MSI.ORGANIZATION_ID = AIV.ORGANIZATION_ID(+)
AND MSI.INVENTORY_ITEM_ID = AIV.ITEM_ID(+)
AND MOQ.UPDATE_TRANSACTION_ID= MMT.TRANSACTION_ID
AND MOQ.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND OOD.ORGANIZATION_ID NOT IN (105, 107, 110, 178)
AND MOQ.ORGANIZATION_ID = 137 --!!
AND FV.LOOKUP_TYPE(+) = '产品处与产品对应表'
AND FV.LANGUAGE(+) = USERENV('LANG')
-- AND MIC1.CATEGORY_CONCAT_SEGS ='ACETW-C4'
-- AND msi.segment1 LIKE 'DFM2002A1-C8'
AND ( (MSI.SEGMENT1 NOT LIKE 'REPAIR%') --排除特殊料號
AND (MSI.SEGMENT1 NOT LIKE 'CHARGE%')
AND (MSI.SEGMENT1 NOT LIKE 'DEPOSIT%')
)
AND EXISTS (SELECT 1 --只計算資產倉
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = moq.organization_id
AND MSI.ASSET_INVENTORY = 1
AND MSI.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE)
留言列表
Record (2)
