Query - Get Supply-Demand for an Item for an org (As in Oracle Supply Demand Form)

--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
;

Posted in: Oracle Notebook,Technical