SELECT
F.MATERIAL_CODE 产品编码,
F.MATERIAL_NAME 产品名称,
F.OM_CLASS_L3_NAME 类别,
F.ORGANIZATION_NAME 库存组织,
F.SUBINVENTORY_NAME 子库存,
SUM(F.STD_INV_AM)/10000 AS STD_INV_AM,
SUM(F.STD_INV_NM) AS STD_INV_NM,
SUM(F.CVT_INV_NM) AS CVT_INV_NM,
F.EXPIRY_AGING_DESC INV_AGE,
F.EXPIRY_AGING_NUM INV_AGE_ORD
FROM
ADS_EBS_INV_HQTY_DETAIL F
WHERE
1 = 1
AND F.MATERIAL_CLASS_L1_NAME = '${ty}'
AND ( F.OU_ID = 102 OR F.OU_ID = 127 )
AND F.YM_ID = SUBSTR( '${P_S_DATE}', 1, 7 )
AND F.INV_CLASS_01_NAME = '成品库'
AND (TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD') - NVL(F.PRODUCE_DATE, TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD'))) >='${SWITCH("true",ty=1,1200,ty=2,720,ty=3,120)}'
GROUP BY
F.MATERIAL_CODE,
F.MATERIAL_NAME,
F.OM_CLASS_L3_NAME,
F.ORGANIZATION_NAME,
F.SUBINVENTORY_NAME,
F.EXPIRY_AGING_DESC,
F.EXPIRY_AGING_NUM
ORDER BY STD_INV_NM desc
----------
或者
SELECT
F.MATERIAL_CODE 产品编码,
F.MATERIAL_NAME 产品名称,
F.OM_CLASS_L3_NAME 类别,
F.ORGANIZATION_NAME 库存组织,
F.SUBINVENTORY_NAME 子库存,
SUM(F.STD_INV_AM)/10000 AS STD_INV_AM,
SUM(F.STD_INV_NM) AS STD_INV_NM,
SUM(F.CVT_INV_NM) AS CVT_INV_NM,
F.EXPIRY_AGING_DESC INV_AGE,
F.EXPIRY_AGING_NUM INV_AGE_ORD
FROM
ADS_EBS_INV_HQTY_DETAIL F
WHERE
1 = 1
AND F.MATERIAL_CLASS_L1_NAME = '${ty}'
AND ( F.OU_ID = 102 OR F.OU_ID = 127 )
AND F.YM_ID = SUBSTR( '${P_S_DATE}', 1, 7 )
AND F.INV_CLASS_01_NAME = '成品库'
AND (TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD') - NVL(F.PRODUCE_DATE, TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD'))) >='${SWITCH(ty,1,1200,2,720,3,120)}'
GROUP BY
F.MATERIAL_CODE,
F.MATERIAL_NAME,
F.OM_CLASS_L3_NAME,
F.ORGANIZATION_NAME,
F.SUBINVENTORY_NAME,
F.EXPIRY_AGING_DESC,
F.EXPIRY_AGING_NUM
ORDER BY STD_INV_NM desc