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