Oracle ERP 統計要用料號的 Onhand Quantity 寫入佔存檔中

統計要用料號的 Onhand Quantity 寫入佔存檔中

master_org_id=101
lv_sql_get_job=

INSERT INTO xx_wip_tbl_get_onhand  VALUE

SELECT NVL(SUM(moq.primary_transaction_quantity),0) quantity,
moq.inventory_item_id, moq.organization_id, moq.subinventory_code, hub.Priority

FROM MTL_ONHAND_QUANTITIES_DETAIL moq

INNER JOIN org_organization_definitions ood 

ON  moq.organization_id  = ood.organization_id
AND (ood.disable_date IS NULL OR ood.disable_date >= trunc(SYSDATE))

INNER JOIN xx_wip_ems_hub_data hub 

ON  hub.organization_id  = moq.organization_id
AND hub.hub_subinventory = moq.subinventory_code

INNER JOIN xx_wip_tbl_get_job job 

ON  moq.inventory_item_id  = job.inventory_item_id

AND moq.organization_id    = job.organization_id
AND hub.ems_subinventory   = job.EMS_SUBINVENTORY

WHERE 1=1
AND hub.HUB_SUBINVENTORY  IN ('A4VMI0A250','A4VMI0A25D','A4VMI0A25G','A4VMI0A25K','A4VMI0A25F','A4VMI0A25S','A40000A250','A40000A25D','A40000A25G','A40000A25K','A40000A25F','A40000A25S','A4PS00A250') 

GROUP BY 

moq.inventory_item_id, moq.organization_id, moq.subinventory_code, hub.Priority