How to get open Supply Demand Query for Internal Sales Order
/********* Internal Sales Order & Sale Order Query***/
SELECT TO_CHAR (ooha.order_number) order_num,ml.meaning supply_demand_type,d.requirement_date required_date, oola.line_id, oola.request_date, wda.delivery_id,
NVL ( -1 * ( d.primary_uom_quantity - d.total_reservation_quantity - d.completed_quantity ), 0 ) qty,
FROM mtl_parameters p,
mtl_system_items_b i,
bom_calendar_dates c,
mrp_demand_om_reservations_v d,
oe_order_headers_all ooha,
oe_order_lines_all oola,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mfg_lookups ml,
(SELECT DECODE (demand_source_type,
2, DECODE (reservation_type, 1, 2, 3, 23, 9),
8, DECODE (reservation_type, 1, 21, 22),
demand_source_type
) supply_demand_source_type,
demand_id
FROM mrp_demand_om_reservations_v) dx
WHERE d.open_flag = 'Y'
AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code = dx.supply_demand_source_type
AND d.demand_id = dx.demand_id
AND ooha.header_id = oola.header_id
AND oola.line_id = d.demand_id
AND wdd.source_line_id(+) = oola.line_id
AND wdd.source_header_id(+) = oola.header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND d.reservation_type != 2
AND d.organization_id = :p_org_id
AND d.primary_uom_quantity >
(d.total_reservation_quantity + d.completed_quantity
)
AND d.inventory_item_id = :p_inventory_item_id
AND ( d.visible_demand_flag = 'Y'
OR ( NVL (d.visible_demand_flag, 'N') = 'N'
AND d.ato_line_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM oe_order_lines_all ool, mtl_demand md
WHERE TO_CHAR (ool.line_id) = md.demand_source_line
AND ool.ato_line_id = d.ato_line_id
AND ool.item_type_code = 'CONFIG'
AND md.reservation_type IN (2, 3))
)
)
AND d.reservation_type != -1
AND d.reservation_type != -1
AND d.demand_source_type != -1
AND d.demand_source_type != -1
AND TRUNC (d.requirement_date) <= :p_to_date
AND ( d.subinventory IS NULL
OR d.subinventory IN (
SELECT s.secondary_inventory_name
FROM mtl_secondary_inventories s
WHERE s.organization_id = d.organization_id
AND s.inventory_atp_code = 1
AND s.attribute1 = 'FG')
)
AND i.organization_id = d.organization_id
AND i.inventory_item_id = d.inventory_item_id
AND p.organization_id = d.organization_id
AND p.calendar_code = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date = TRUNC (d.requirement_date)
AND d.inventory_item_id =
DECODE (d.reservation_type,
1, DECODE (d.parent_demand_id,
NULL, d.inventory_item_id,
-1
),
2, d.inventory_item_id,
3, d.inventory_item_id,
-1
)
