Search This Blog

Wednesday, December 28, 2011

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

18 comments:

  1. Excellent!!! great procedure...thanks for sharing

    ReplyDelete
  2. Thank you very much :)

    ReplyDelete
  3. Thankks a lot for sharing.

    ReplyDelete
  4. Very helpful query. Thanks
    Can you also help to create a query to calculate Item MRP Planning Gross Requirement.

    ReplyDelete
  5. Amazing Job.
    Bless You.
    Thank you so much

    ReplyDelete
  6. Thanks a lot, it's a great procedure.

    ReplyDelete
  7. Thanks a lot for the sharing ! Very useful !

    ReplyDelete
  8. Very useful Procedure!! Much Appreciated!!

    ReplyDelete
  9. Thanks a lot very very helpful

    ReplyDelete
  10. Thanks a lot.. Great effort in developing the complex procedure and sharing to all.. Very helpful

    ReplyDelete
  11. How to get the Available Quantity from this procedure? the way it shows in the form

    ReplyDelete
    Replies
    1. You need to add 'Quantity' to On-Hand.
      If you want 'Available Quantity' for a date, you can restrict data by date, use below Condition(similar) in all SQLs.

      AND TRUNC (d.requirement_date) <= TRUNC(SYSDATE)

      Delete
  12. All i can Say it , Thanks Thanks Thanks.

    ReplyDelete
  13. How to get the value of "Current On-hand" as shown on the form and the inventory value to this excellent procedure.

    thanks

    ReplyDelete
  14. I can see ohq. what about the Available Qty which is ohq-reservations.

    ReplyDelete