Item Supply Demand

Item Supply Demand Form: This is very popular form to supply/demand information in the near future. It shows the current on-hand quantity for any item queried and then displays the supply demand information sorted by supply/demand date along with projected on-hand information.

Basically It considers below supply/demand sources:

1. Sales Orders & Internal Sales Orders

2. Reserved Sales Orders

3. WIP Demand(Work Orders)

4. WIP Supply(Work Orders)

5. Purchase Orders

Navigation: Login to Oracle applications -> Inventory(resp) -> Onhand, Availability -> Item Supply/Demand

Below procedure calculates and displays all the above mentioned supply/demand sources for the given item and organization combination.

Create below procedure in your instance and execute as shown below:

set serverout on

begin

lss_isd_proc(<Item Name>,<Organzation_code>);

end;

/

Procedure:

*****************************************************************

*****************************************************************

create or replace procedure LSS_ISD_PROC(p_item IN VARCHAR2, p_org IN VARCHAR2)
as

cursor lss_isd_cur(l_item_id NUMBER, l_org_id NUMBER)
is
--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
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
WHERE
  1                        =1
AND d.demand_source_line   = oola.line_id
AND ooha.header_id         = oola.header_id
AND d.organization_id      = l_org_id
AND d.demand_id            = dx.demand_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   = l_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            
  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,
       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 d.reservation_type != 2
   AND d.organization_id = l_org_id
   AND d.primary_uom_quantity >
                        (d.total_reservation_quantity + d.completed_quantity
                        )
   AND d.inventory_item_id = l_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
  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 = l_org_id
   AND o.inventory_item_id = l_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
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 = l_org_id
AND d.primary_item_id = l_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
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         = l_item_id
AND o.wip_entity_id             = d.wip_entity_id
AND o.organization_id           = l_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
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    = l_org_id
AND s.item_id               = l_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);

l_ohq NUMBER := 0;
org_id NUMBER;
item_id NUMBER;

begin


BEGIN
  SELECT organization_id
  INTO org_id
  FROM mtl_parameters
  WHERE 1=1
  AND organization_code = p_org;
  SELECT inventory_item_id
  INTO item_id
  FROM mtl_system_items_b msib
  WHERE 1=1
  AND segment1 like p_item
  AND organization_id = org_id;
  EXCEPTION WHEN NO_DATA_FOUND
  THEN
   DBMS_OUTPUT.PUT_LINE('Invalid Item or Organization');
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Unknown Exception while retrieving Item Information');
END;

BEGIN 
        SELECT NVL (SUM (a.primary_transaction_quantity), 0) s
          INTO l_ohq
          FROM mtl_onhand_quantities_detail a
              , mtl_secondary_inventories b
         WHERE a.organization_id = org_id
           AND a.inventory_item_id = item_id
           AND a.subinventory_code = b.secondary_inventory_name
           AND a.organization_id = b.organization_id;
       EXCEPTION
           WHEN OTHERS THEN
              l_ohq :=0;
END;


DBMS_OUTPUT.PUT_LINE('********** Availability Information for ' || p_item ||' from the ORG: ' || p_org ||' *************');
DBMS_OUTPUT.PUT_LINE(CHR(13));

DBMS_OUTPUT.PUT_LINE(RPAD('Request Date',20,' ') ||  RPAD('|Supply/Demand Type',25,' ') || RPAD('|Identifier',20,' ') || RPAD('|Quantity',20,' '));

DBMS_OUTPUT.PUT_LINE(RPAD('_',75,'_'));

FOR lss_isd_rec in lss_isd_cur(item_id,org_id)
LOOP
  BEGIN
    DBMS_OUTPUT.PUT_LINE(                      RPAD(lss_isd_rec.required_date,20,' ')||
              RPAD(lss_isd_rec.supply_demand_type,25,' ')||
              RPAD(lss_isd_rec.identifier,20,' ')||
              RPAD(lss_isd_rec.quantity,20,' '));
    
 Exception when others then

 DBMS_OUTPUT.PUT_LINE('Exception while retrieving Item Supply Demand Info');

 END;
 END LOOP;

 EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unknown Exception in the Procedure lss_isd_proc');

END lss_isd_proc;
/