統計要用料號的 Onhand Quantity 寫入佔存檔中
master_org_id=101
lv_sql_get_job=
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
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))
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
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
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