Supply/Demand的数据从何处计算而来

zhuguangwei

是的,但你关了查找窗口,再select mtl_supply_demand_temp里的数是不正确的.
只能通过SQL去计算

menfanfeng

ammonecho

最初由 adian 发布
[B]多谢zhuguangwei！

我发现显示在supply/demand中的数据是来自于mtl_supply_demand_temp。

[/B]

這個應該是在你按了FIND以後觸發了一些TRIGGER 然後塞進去的吧，而且每次要先在FORM裏面差了以後才會在TABLE裏面有值吧

Andyisme

最初由 zhuguangwei 发布
[B]以下几条SQL 就是计算出

SELECT  
--*************************  
-- DEMAND - SALES ORDER  
--*************************  
md.inventory_item_id  
, md.organization_id  
, ml.meaning supply_demand_type  
, sha.order_number || '' identifier  
, msi.segment1 item_no  
, md.primary_uom_quantity*-1 qty  
, md.subinventory  
, md.requirement_date  
, md.mrp_date  
--, md.*  
FROM mtl_demand md  
, mtl_system_items msi  
, so_lines_all sla  
, so_headers_all sha  
, mfg_lookups ml  
WHERE md.inventory_item_id = msi.inventory_item_id  
AND md.organization_id = msi.organization_id  
AND md.parent_demand_id IS NULL  
AND md.available_to_mrp = 1 AND available_to_atp = 1  
AND md.primary_uom_quantity > md.completed_quantity  
AND md.demand_source_line = sla.line_id  
AND sla.header_id = sha.header_id  
AND md.demand_source_type = ml.lookup_code  
AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'  
--AND md.inventory_item_id = 160325  
UNION ALL  
SELECT  
--*************************  
-- SUPPLY - PURCHASE ORDER  
--*************************  
ms.item_id  
, ms.to_organization_id  
, 'Purchase Order' demand_supply_type  
, pha.segment1 po_number  
, msi.segment1 item_no  
, ms.quantity  
, '' subinventory  
, ms.expected_delivery_date  
, ms.mrp_expected_delivery_date  
--, ms.*  
FROM mtl_supply ms  
, mtl_system_items msi  
, po_headers_all pha  
WHERE ms.item_id = msi.inventory_item_id  
AND ms.to_organization_id = msi.organization_id  
AND ms.po_header_id = pha.po_header_id  
--AND ms.item_id=144951  
UNION ALL  
SELECT  
--*************************  
-- DEMAND - WIP DISCRETE JOB  
--*************************  
wro.inventory_item_id  
, wro.organization_id  
, 'WIP Discrete Job' supply_demand_type  
, we.wip_entity_name  
, wro.segment1  
, (wro.required_quantity - wro.quantity_issued)*-1 qty_open  
, wro.supply_subinventory  
, wro.date_required  
, wro.mps_date_required  
FROM wip_discrete_jobs wdj  
, wip_entities we  
, wip_requirement_operations wro  
WHERE wdj.wip_entity_id = we.wip_entity_id  
AND we.wip_entity_id = wro.wip_entity_id  
AND wdj.status_type = 3  
AND wro.required_quantity - wro.quantity_issued > 0  
--AND wro.inventory_item_id = 144951  
UNION ALL  
SELECT  
--*************************  
-- SUPPLY - WIP DISCRETE JOB  
--*************************  
wdj.primary_item_id  
, wdj.organization_id  
, 'WIP Discrete Job' supply_demand_type  
, we.wip_entity_name  
, msi.segment1  
, wdj.start_quantity  - wdj.quantity_completed quantity_pending  
, '' subinventory  
, wdj.scheduled_completion_date  
, wdj.mps_scheduled_completion_date  
FROM wip_discrete_jobs wdj  
, wip_entities we  
, mtl_system_items msi  
WHERE wdj.wip_entity_id = we.wip_entity_id  
AND wdj.status_type = 3  
AND wdj.primary_item_id = msi.inventory_item_id  
AND wdj.organization_id = msi.organization_id  
--AND wro.required_quantity - wro.quantity_issued > 0  
--AND wdj.primary_item_id = 119094  
UNION ALL  
SELECT  
--*************************  
-- DEMAND/SUPPLY - MRP  
--*************************  
msd.inventory_item_id  
, msd.organization_id  
, 'Discrete MPS' supply_demand_type  
, msd.schedule_designator  
, msi.segment1  
, msd.schedule_quantity  
, '' subinventory  
, msd.schedule_date  
, msd.schedule_date  
FROM mrp_schedule_dates msd  
, mtl_system_items msi  
WHERE msd.inventory_item_id = msi.inventory_item_id  
AND msd.organization_id = msi.organization_id  
AND msd.supply_demand_type = 2  
-- AND msd.inventory_item_id = 160325; [/B]

很有用的Script...謝謝..

521102yz

我們這也經常出錯

box

最初由 zhuguangwei 发布
[B]以下几条SQL 就是计算出

SELECT  
--*************************  
-- DEMAND - SALES ORDER  
--*************************  
md.inventory_item_id  
, md.organization_id  
, ml.meaning supply_demand_type  
, sha.order_number || '' identifier  
, msi.segment1 item_no  
, md.primary_uom_quantity*-1 qty  
, md.subinventory  
, md.requirement_date  
, md.mrp_date  
--, md.*  
FROM mtl_demand md  
, mtl_system_items msi  
, so_lines_all sla  
, so_headers_all sha  
, mfg_lookups ml  
WHERE md.inventory_item_id = msi.inventory_item_id  
AND md.organization_id = msi.organization_id  
AND md.parent_demand_id IS NULL  
AND md.available_to_mrp = 1 AND available_to_atp = 1  
AND md.primary_uom_quantity > md.completed_quantity  
AND md.demand_source_line = sla.line_id  
AND sla.header_id = sha.header_id  
AND md.demand_source_type = ml.lookup_code  
AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'  
--AND md.inventory_item_id = 160325  
UNION ALL  
SELECT  
--*************************  
-- SUPPLY - PURCHASE ORDER  
--*************************  
ms.item_id  
, ms.to_organization_id  
, 'Purchase Order' demand_supply_type  
, pha.segment1 po_number  
, msi.segment1 item_no  
, ms.quantity  
, '' subinventory  
, ms.expected_delivery_date  
, ms.mrp_expected_delivery_date  
--, ms.*  
FROM mtl_supply ms  
, mtl_system_items msi  
, po_headers_all pha  
WHERE ms.item_id = msi.inventory_item_id  
AND ms.to_organization_id = msi.organization_id  
AND ms.po_header_id = pha.po_header_id  
--AND ms.item_id=144951  
UNION ALL  
SELECT  
--*************************  
-- DEMAND - WIP DISCRETE JOB  
--*************************  
wro.inventory_item_id  
, wro.organization_id  
, 'WIP Discrete Job' supply_demand_type  
, we.wip_entity_name  
, wro.segment1  
, (wro.required_quantity - wro.quantity_issued)*-1 qty_open  
, wro.supply_subinventory  
, wro.date_required  
, wro.mps_date_required  
FROM wip_discrete_jobs wdj  
, wip_entities we  
, wip_requirement_operations wro  
WHERE wdj.wip_entity_id = we.wip_entity_id  
AND we.wip_entity_id = wro.wip_entity_id  
AND wdj.status_type = 3  
AND wro.required_quantity - wro.quantity_issued > 0  
--AND wro.inventory_item_id = 144951  
UNION ALL  
SELECT  
--*************************  
-- SUPPLY - WIP DISCRETE JOB  
--*************************  
wdj.primary_item_id  
, wdj.organization_id  
, 'WIP Discrete Job' supply_demand_type  
, we.wip_entity_name  
, msi.segment1  
, wdj.start_quantity  - wdj.quantity_completed quantity_pending  
, '' subinventory  
, wdj.scheduled_completion_date  
, wdj.mps_scheduled_completion_date  
FROM wip_discrete_jobs wdj  
, wip_entities we  
, mtl_system_items msi  
WHERE wdj.wip_entity_id = we.wip_entity_id  
AND wdj.status_type = 3  
AND wdj.primary_item_id = msi.inventory_item_id  
AND wdj.organization_id = msi.organization_id  
--AND wro.required_quantity - wro.quantity_issued > 0  
--AND wdj.primary_item_id = 119094  
UNION ALL  
SELECT  
--*************************  
-- DEMAND/SUPPLY - MRP  
--*************************  
msd.inventory_item_id  
, msd.organization_id  
, 'Discrete MPS' supply_demand_type  
, msd.schedule_designator  
, msi.segment1  
, msd.schedule_quantity  
, '' subinventory  
, msd.schedule_date  
, msd.schedule_date  
FROM mrp_schedule_dates msd  
, mtl_system_items msi  
WHERE msd.inventory_item_id = msi.inventory_item_id  
AND msd.organization_id = msi.organization_id  
AND msd.supply_demand_type = 2  
-- AND msd.inventory_item_id = 160325; [/B]

楼主的SQL是ORACLE10.7的吧! 有没有11i 的?

lanniechi

是不是還缺PR與MDS的部份, 且這樣撈會不會遺漏到沒考慮的部份, 是不是有其它程式碼可以一次撈出全部呢?

lightingxjh

应该还有最多的限制条件。哪位大神可以再补充些吗，比如PO的数据还要加什么条件吗