打开报表 所有库存值报表 相关SQL.
但是cst_inv_qty_temp CIQT, cst_inv_cost_temp CICT,两个临时表我在触发器和程序单元并未发现相关信息.
SELECT &P_ITEM_SEG ITEM_NUMBER,
&P_CAT_SEG CATEGORY,
MSI.description DESCRIPTION,
MSI.primary_uom_code UOM_CODE,
ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) STK_QTY,
0 INT_QTY,
0 RCV_QTY,
ROUND(
SUM(CIQT.rollback_qty) *
DECODE(SUB.asset_inventory,1,CICT.item_cost,0) *
:P_Exchange_Rate/
:Round_Unit
) *
:Round_Unit STK_VALUE,
0 INT_VALUE,
0 RCV_VALUE
FROM cst_inv_qty_temp CIQT,
cst_inv_cost_temp CICT,
mtl_parameters MP,
mtl_system_items_vl MSI,
mtl_categories MC,
mtl_secondary_inventories SUB
WHERECIQT.qty_source in (3,4)
ANDCICT.cost_source = 1
ANDCICT.organization_id = CIQT.organization_id
ANDCICT.inventory_item_id = CIQT.inventory_item_id
AND( MP.primary_cost_method = 1
ORCICT.cost_group_id = CIQT.cost_group_id) -- should we split?
ANDMP.organization_id = CIQT.organization_id
ANDSUB.organization_id = CIQT.organization_id
ANDSUB.secondary_inventory_name = CIQT.subinventory_code
ANDMSI.organization_id = CIQT.organization_id
ANDMSI.inventory_item_id = CIQT.inventory_item_id
ANDMC.category_id= CIQT.category_id
GROUP
BY &P_ITEM_SEG,
&P_CAT_SEG,
MSI.description,
MSI.primary_uom_code,
DECODE(SUB.asset_inventory,1,CICT.item_cost,0)
UNION ALL
SELECT &P_ITEM_SEG ITEM_NUMBER,
&P_CAT_SEG CATEGORY,
MSI.description DESCRIPTION,
MSI.primary_uom_code UOM_CODE,
0 STK_QTY,
ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) INT_QTY,
0 RCV_QTY,
0 STK_VALUE,
ROUND(
SUM(CIQT.rollback_qty) *
CICT.item_cost *
:P_Exchange_Rate/
:Round_Unit
) *
:Round_Unit INT_VALUE,
0 RCV_VALUE
FROM cst_inv_qty_temp CIQT,
cst_inv_cost_temp CICT,
mtl_parameters MP,
mtl_system_items_vl MSI,
mtl_categories MC
WHERECIQT.qty_source in (6,7)
ANDCICT.organization_id = :P_ORG_ID
ANDCICT.cost_source = 1
ANDCICT.organization_id = CIQT.organization_id
ANDCICT.inventory_item_id = CIQT.inventory_item_id
AND( MP.primary_cost_method = 1
ORCICT.cost_group_id = CIQT.cost_group_id) -- should we split?
ANDMP.organization_id = CIQT.organization_id
ANDMSI.organization_id = CIQT.organization_id
ANDMSI.inventory_item_id = CIQT.inventory_item_id
ANDMC.category_id= CIQT.category_id
GROUP
BY &P_ITEM_SEG,
&P_CAT_SEG,
MSI.description,
MSI.primary_uom_code,
CICT.item_cost
UNION ALL
SELECT &P_ITEM_SEG ITEM_NUMBER,
&P_CAT_SEG CATEGORY,
MSI.description DESCRIPTION,
MSI.primary_uom_code UOM_CODE,
0 STK_QTY,
0 INT_QTY,
ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) RCV_QTY,
0 STK_VALUE,
0 INT_VALUE,
ROUND(
SUM(CIQT.rollback_qty)*
CICT.item_cost *
:P_Exchange_Rate /
:Round_Unit
) *
:Round_Unit RCV_VALUE
FROMcst_inv_qty_temp CIQT,
cst_inv_cost_temp CICT,
mtl_system_items_vl MSI,
mtl_categories MC
WHERE CIQT.qty_source = 9
AND CICT.cost_source = 3
AND CICT.organization_id = CIQT.organization_id
AND CICT.inventory_item_id = CIQT.inventory_item_id
AND CICT.rcv_transaction_id = CIQT.rcv_transaction_id
AND MSI.organization_id = CIQT.organization_id
AND MSI.inventory_item_id = CIQT.inventory_item_id
AND MC.category_id= CIQT.category_id
GROUP
BY &P_ITEM_SEG,
&P_CAT_SEG,
MSI.description,
MSI.primary_uom_code,
CICT.item_cost
|
