item supply/demand - basics on calculation

http://forums.oracle.com/forums/thread.jspa?threadID=665964


Depends a lot on how your instance is configured (mfg is there or not, external ATP sources are used or consigned inventorry exists etc)

For simple site in a distribution company looking back into my notes, here are the details:

this form is based on a table mtl_supply_demand_temp. This table is usually empty, but you can find rows here if you query the table when you are in the form after you query the form (don't close the details block, it will delete the records from this table).

One ATP rule will be automatically created in mtl_atp_rules table for every session and is used to identify the supply and demand sources. If you look at the ATP rule screen you will get an idea for the demand and supply for the quantity.

As far as the calculation goes, depending on the sources for demand and supply used in the ATP rule created, it will look at the following tables:

Supply:
mtl_supply (PO and req)
mtl_onhand_quantities_detail (onhand)
wip_discrete_jobs (wip jobs)
mrp_org_quantity_v and mrp_plans (production schedules) to name a few for supplies

Demand:
mtl_reservations
mtl_material_transactions_temp
again mrp plans may also be used if there is a component demand.



traditional form launches the native C function "INXDSD" from the INVDVDSD procedure



I tried using the same code used by the form in SQL. It worked. The only issue is that the ATP group Id specific to this request is not populating appropritely. It is always populating as 0. If I register as concurrent program probably it will populate correctly. This is required in order to delete the rows correctly belonging to that session from the mtl_supply_demand_temp at the end of the session.

You can try that if you are Ok with that approach. This way you do not have worry about finding out the tables to query. Here is that I did.

DECLARE
l_seq_num NUMBER;
rtvl NUMBER;
args1 VARCHAR (240);
args2 VARCHAR (240);
args3 VARCHAR (240);
args4 VARCHAR (240);
args5 VARCHAR (240);
args6 VARCHAR (240);
args7 VARCHAR (240);
args8 VARCHAR (240);
args9 VARCHAR (240);
args10 VARCHAR (240);
args11 VARCHAR (240);
args12 VARCHAR (240);
args13 VARCHAR (240);
args14 VARCHAR (240);
args15 VARCHAR (240);
args16 VARCHAR (240);
args17 VARCHAR (240);
args18 VARCHAR (240);
args19 VARCHAR (240);
args20 VARCHAR (240);
l_session_id NUMBER;
l_count NUMBER;
cutoff_date_j NUMBER;
l_total_temp VARCHAR2 (30);
l_avail_qty_temp VARCHAR2 (30);

CURSOR c1
IS
SELECT inventory_item_id
FROM mtl_system_items_b
WHERE organization_id = 204 AND segment1 IN ('AS18947', 'AS81414');
BEGIN
fnd_global.apps_initialize (1072, 50346, 401);
--fnd_profile.put ('CONC_DEBUG', 'TC');
/* fnd_global.initialize (session_id => l_session_id
, user_id => 1072
, resp_id => 50346
, resp_appl_id => 401
, security_group_id => 0
, site_id => 0
, login_id => 1833382
, conc_login_id => -1
, prog_appl_id => 401
, conc_program_id => 33808
, conc_request_id => fnd_global.conc_request_id
, conc_priority_request => -1
, form_id => 52654
, form_appl_id => 401
, conc_process_id => NULL
, conc_queue_id => NULL
, queue_appl_id => NULL
, server_id => 147
);*/
inv_globals.set_org_id (204);

SELECT mtl_demand_interface_s.NEXTVAL
INTO l_seq_num
FROM DUAL;

SELECT TO_CHAR (SYSDATE, 'j')
INTO cutoff_date_j
FROM DUAL;

FOR i IN c1
LOOP
IF (inv_tm.launch ('INXDSD'
, 'GROUP_ID='
|| TO_CHAR (l_seq_num)
|| ' '
|| 'ORGANIZATION_ID='
|| TO_CHAR (204)
|| ' '
|| 'INVENTORY_ITEM_ID='
|| TO_CHAR (i.inventory_item_id)
|| ' '
|| 'ONHAND_SOURCE='
|| TO_CHAR (1) --1 ATP Only, 2 For Net Subinventories, 3 all subinvs
|| ' '
|| 'CUTOFF_DATE="'
|| TO_CHAR (cutoff_date_j)
|| '" '
|| 'MRP_STATUS='
|| TO_CHAR (1)
|| ' '
|| 'ONHAND_FIELD=l_total_temp'
|| 'AVAIL_FIELD=l_avail_qty_temp'
, l_total_temp
, l_avail_qty_temp
) = FALSE
)
THEN
DBMS_OUTPUT.put_line ('FAILED');
ELSE
DBMS_OUTPUT.put_line ('SUCCESS');
DBMS_OUTPUT.put_line (l_total_temp);
DBMS_OUTPUT.put_line (l_avail_qty_temp);

INSERT INTO xx_mtl_supply_demand_temp
SELECT *
FROM mtl_supply_demand_temp
WHERE seq_num = 0;
END IF;
END LOOP;

-- fnd_profile.put ('CONC_DEBUG', NULL);
BEGIN
DELETE FROM mtl_atp_rules
WHERE rule_id IN (SELECT atp_rule_id
FROM mtl_group_atps_view
WHERE atp_group_id = 0);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
DELETE mtl_group_atps_view
WHERE atp_group_id = 0;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

BEGIN
DELETE mtl_supply_demand_temp
WHERE seq_num = 0;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

inv_utilities.do_sql ('commit');END;

As you can see I have to use seq_num as zero it was always populating as zero in this table where as from form it populates correctly.

Since this is a function, probably you can use this in discoverer queuries (I am not sure). Then query this custom table (mirror of mtl_supply_demand_temp) to show the report.