--Reserved Sales Orders
SELECT
d.requirement_date Required_date ,
ml.meaning Supply_Demand_Type,
to_char(ooha.order_number) Identifier,
-1 * ( d.primary_uom_quantity - GREATEST (NVL (d.reservation_quantity, 0), d.completed_quantity) ) quantity,
oola.line_id,
oola.request_date,
wda.delivery_id
FROM
mtl_parameters p,
mtl_system_items i,
bom_calendar_dates c,
mtl_demand d,
mfg_lookups ml,
(
SELECT
DECODE (demand_source_type, 2, DECODE (reservation_type, 1, 2, 3, DECODE
(supply_source_type, 5, 23, 31), 9 ), 8, DECODE (reservation_type, 1, 21,
22), demand_source_type) supply_demand_source_type,
demand_id
FROM
mtl_demand
)
dx,
oe_order_headers_all ooha,
oe_order_lines_all oola,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE
1 =1
AND d.demand_source_line = oola.line_id
AND ooha.header_id = oola.header_id
AND d.organization_id = p_org_id
AND d.demand_id = dx.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 ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code = dx.supply_demand_source_type
AND d.primary_uom_quantity > GREATEST (NVL (d.reservation_quantity, 0),
d.completed_quantity)
AND d.inventory_item_id = p_inventory_item_id
AND d.available_to_atp = 1
AND d.reservation_type != -1
AND d.demand_source_type != 13
AND d.demand_source_type != -1
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 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 )
UNION
-- Sales Orders and Internal Sales Orders
SELECT d.requirement_date required_date, ml.meaning Supply_demand_Type,
to_char(ooha.order_number) Identifier,
NVL( -1
* ( d.primary_uom_quantity
- d.total_reservation_quantity
- d.completed_quantity
), 0) Quantity ,
oola.line_id,
oola.request_date ,
wda.delivery_id
FROM mtl_parameters p,
mtl_system_items 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 NULL
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 (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
)
UNION
--WIP DEMAND
SELECT o.date_required required_date, ml.meaning Supply_Demand_Type, we.wip_entity_name Identifier,
LEAST (-1 * (o.required_quantity - o.quantity_issued), 0) quantity, NULL, NULL,NULL
FROM
mtl_parameters p,
mfg_lookups ml,
-- mtl_atp_rules r,
mtl_system_items i,
bom_calendar_dates c,
wip_requirement_operations o,
wip_discrete_jobs d,
wip_entities we,
(select DECODE (job_type, 1, 5, 7) supply_demand_source_type, wip_entity_id from wip_discrete_jobs) dx
WHERE 1 = 1
and we.wip_entity_id = d.wip_entity_id
AND ml.lookup_type = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code = dx.supply_demand_source_type
and d.wip_entity_id = dx.wip_entity_id
AND o.organization_id = d.organization_id
AND o.organization_id = p_org_id
AND o.inventory_item_id = p_inventory_item_id
AND o.wip_entity_id = d.wip_entity_id
AND o.wip_supply_type NOT IN (5, 6)
AND o.required_quantity > 0
AND o.required_quantity <> (o.quantity_issued)
AND o.operation_seq_num > 0
AND o.date_required IS NOT NULL
AND ( o.supply_subinventory IS NULL
OR EXISTS (
SELECT 'X'
FROM mtl_secondary_inventories s
WHERE s.organization_id = o.organization_id
AND o.supply_subinventory = s.secondary_inventory_name
AND s.inventory_atp_code = 1)
)
AND d.status_type IN (1, 3, 4, 6)
AND p.organization_id = o.organization_id
AND i.organization_id = o.organization_id
AND i.inventory_item_id = o.inventory_item_id
AND p.calendar_code = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date = TRUNC (o.date_required)
UNION
--WIP Supply
SELECT
d.scheduled_completion_date required_date,
ml.meaning Supply_Demand_Type,
we.wip_entity_name Identifier,
(d.start_quantity - d.quantity_completed - d.quantity_scrapped )Quantity, NULL,NULL,NULL
FROM
wip_discrete_jobs d,
bom_calendar_dates c,
mtl_parameters p,
mtl_system_items i,
wip_entities we,
(
SELECT
DECODE (job_type, 1, 5, 7) supply_demand_source_type,
wip_entity_id
FROM
wip_discrete_jobs
)
dx,
mfg_lookups ml
WHERE
1 =1
AND d.wip_entity_id = dx.wip_entity_id
AND dx.supply_demand_source_type = ml.lookup_code
AND ml.lookup_type = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND d.wip_entity_id = we.wip_entity_id
AND d.status_type IN (1, 3, 4, 6)
AND
(
d.start_quantity - d.quantity_completed
)
> 0
AND d.organization_id = p_org_id
AND d.primary_item_id = p_inventory_item_id
AND
(
d.completion_subinventory IS NULL
OR EXISTS
(
SELECT
'X'
FROM
mtl_secondary_inventories s
WHERE
s.organization_id = d.organization_id
AND d.completion_subinventory = s.secondary_inventory_name
AND s.inventory_atp_code = 1
)
)
AND p.organization_id = d.organization_id
AND i.organization_id = d.organization_id
AND i.inventory_item_id = d.primary_item_id
AND p.calendar_code = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date = TRUNC (d.scheduled_completion_date)
UNION ALL
SELECT
d.scheduled_completion_date required_date,
ml.meaning Supply_Demand_Type,
we.wip_entity_name Identifier,
(d.start_quantity - d.quantity_completed - d.quantity_scrapped ) Quantity,NULL,NULL,NULL
FROM
mtl_parameters p,
mtl_system_items i,
bom_calendar_dates c,
wip_requirement_operations o,
wip_discrete_jobs d,
wip_entities we,
(
SELECT
DECODE (job_type, 1, 5, 7) supply_demand_source_type,
wip_entity_id
FROM
wip_discrete_jobs
)
dx,
mfg_lookups ml
WHERE
1 =1
AND d.wip_entity_id = dx.wip_entity_id
AND dx.supply_demand_source_type= ml.lookup_code
AND ml.lookup_type = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND we.wip_entity_id = d.wip_entity_id
AND o.organization_id = d.organization_id
AND o.inventory_item_id = p_inventory_item_id
AND o.wip_entity_id = d.wip_entity_id
AND o.organization_id = p_org_id
AND o.wip_supply_type NOT IN (5, 6)
AND o.required_quantity < 0
AND
(
o.required_quantity - o.quantity_issued
)
< 0
AND o.operation_seq_num > 0
AND
(
d.completion_subinventory IS NULL
OR EXISTS
(
SELECT
'X'
FROM
mtl_secondary_inventories s
WHERE
s.organization_id = d.organization_id
AND d.completion_subinventory = s.secondary_inventory_name
AND s.inventory_atp_code = 1
)
)
AND
(
d.job_type = 1
OR d.job_type = 3
)
AND d.status_type IN (1, 3, 4, 6)
AND d.organization_id = o.organization_id
AND p.organization_id = o.organization_id
AND i.organization_id = o.organization_id
AND i.inventory_item_id = o.inventory_item_id
AND p.calendar_code = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date = TRUNC (o.date_required)
UNION
--Purchase Orders:
SELECT c.next_date Required_Date,
ml.meaning Supply_demand_Type,
sx.identifier,
DECODE (s.supply_type_code, 'SHIPMENT', s.to_org_primary_quantity,
s.to_org_primary_quantity ) Quantity,NULL,NULL,NULL
FROM
mtl_system_items i,
mtl_parameters p,
bom_calendar_dates c,
mtl_supply s,
mfg_lookups ml,
( SELECT
DECODE (ms.po_header_id, NULL, DECODE (ms.supply_type_code, 'REQ', DECODE (
ms.from_organization_id, NULL, 18, 20), 12 ), DECODE (ms.supply_type_code,
'SHIPMENT', 35, 'RECEIVING', 36, 1) ) supply_demand_source_type,
poh.segment1 Identifier,
supply_source_id
FROM
mtl_supply ms,
po_headers_all poh
WHERE
1=1
AND poh.po_header_id = ms.po_header_id
) sx
WHERE
1 = 1
AND s.supply_source_id = sx.supply_source_id
AND ml.lookup_type = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code = sx.supply_demand_source_type
AND
(
(
s.req_header_id IS NULL
AND s.po_header_id IS NULL
)
OR
(
s.req_header_id = s.req_header_id
AND s.from_organization_id IS NOT NULL
)
OR
(
s.supply_type_code = 'REQ'
AND s.from_organization_id IS NULL
)
OR s.po_header_id = s.po_header_id
)
AND s.to_organization_id = p_org_id
AND s.item_id = p_inventory_item_id --v.inventory_item_id
AND s.destination_type_code = 'INVENTORY'
AND
(
s.to_subinventory IS NULL
OR EXISTS
(
SELECT
'X'
FROM
mtl_secondary_inventories s2
WHERE
s2.organization_id = s.to_organization_id
AND s.to_subinventory = s2.secondary_inventory_name
AND s2.inventory_atp_code = 1
AND s2.availability_type = s2.availability_type
)
)
AND i.organization_id = s.to_organization_id
AND i.inventory_item_id = s.item_id
AND p.organization_id = s.to_organization_id
AND p.calendar_code = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND NOT EXISTS
(
SELECT
'X'
FROM
oe_drop_ship_sources odss
WHERE
DECODE (s.po_header_id, NULL, s.req_line_id, s.po_line_location_id ) =
DECODE (s.po_header_id, NULL, odss.requisition_line_id,
odss.line_location_id )
)
AND c.calendar_date = TRUNC (s.expected_delivery_date)
/*
UNION
-- User Supply
SELECT expected_delivery_date Required_date,
'User Supply' Supply_Demand_Type,
source_name Identifier,
SUM(primary_uom_quantity) quantity,
NULL,
NULL,NULL
FROM mtl_user_supply
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_org_id
AND primary_uom_quantity <> '0'
GROUP BY expected_delivery_date,
creation_date,
primary_uom_quantity,
source_name
--
UNION
--Booked Sales Orders with NULL Scheduled Date
SELECT
oola.schedule_ship_date Required_date,
'Sales Order' Supply_Demand_Type,
to_char(ooha.order_number) Identifier,
-1 * (oola.ordered_quantity ) quantity,
oola.line_id,
oola.request_date,
wda.delivery_id
FROM
oe_order_headers_all ooha,
oe_order_lines_all oola,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE ooha.header_id = oola.header_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 oola.inventory_item_id = p_inventory_item_id
AND oola.ship_from_org_id = p_org_id
AND oola.schedule_ship_date IS NULL
AND UPPER(ooha.flow_status_code) = 'BOOKED'
*/
ORDER BY Required_date,
Supply_Demand_Type DESC,
quantity
;
Posts
