ATP Inquiry API


DECLARE
v_count NUMBER;
v_errbuf VARCHAR2 (200);
v_retcode VARCHAR2 (1);
v_sqlcode NUMBER;
v_interface_id NUMBER ;
x_return_status VARCHAR2 (2);
x_msg_data VARCHAR2 (500);
x_msg_count NUMBER;
l_session_id NUMBER;
l_error_message VARCHAR2 (250);
x_error_message VARCHAR2 (80);
v_count NUMBER;
l_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
x_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
x_atp_details MRP_ATP_PUB.ATP_Details_Typ;

CURSOR cur_items
IS
SELECT msib.inventory_item_id, segment1 item_name
FROM mtl_system_items_b msib
WHERE msib.organization_id = -- organization_id
AND msib.segment1 IN
(......);-- Give your item list in quotes seperated by ,
BEGIN

v_count := 1; -- Setting Record# for API input table type

SELECT OE_ORDER_SCH_UTIL.Get_Session_Id INTO l_session_id FROM DUAL;

DBMS_OUTPUT.put_line ('Session ID ' || l_session_id); -- Session for temp table

FOR rec_cur_items IN cur_items
LOOP
DBMS_OUTPUT.put_line ('Item: ' || rec_cur_items.item_name);

-- MSC_ATP_GLOBAL.Extend_Atp (l_atp_rec, x_return_status, 1); -- if using 11i

l_atp_rec.Inventory_Item_Id (v_count) := rec_cur_items.inventory_item_id; --** inventory_item_id. Mandatory
l_atp_rec.Inventory_Item_Name (v_count) := rec_cur_items.item_name; -- Segment1
l_atp_rec.Quantity_Ordered (v_count) := 1; -- ** Quantity. Mandatory
l_atp_rec.Quantity_UOM (v_count) := 'EA'; --** Primary UOM form Items definition. Mandatory
l_atp_rec.Requested_Ship_Date (v_count) := SYSDATE;
l_atp_rec.Ship_Set_Name (v_count) := 'Test1';-- Pass any dummy value if your line set creteria is Ship Set.
-- If your line set creteria is Arrival Set Pass requested_arrival_date & Arrival Set name (it can be any random value).
--Either requested_ship_date or requested_arrival_date should be provided. Error will be generated if none or both of these dates are provided
l_atp_rec.Action (v_count) := 100; -- 100- ATP Inquiry , 110- Scheduling , 120 Rescheduling
l_atp_rec.Instance_Id (v_count) := 81; -- can be obtained from MRP_AP_APPS_INSTANCES.instance_id
l_atp_rec.identifier (v_count) := MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL; -- sequence for identifier
l_atp_rec.sequence_number (v_count) := v_count; -- This is the sequence in which you want the items to be ordered in ATP Results
l_atp_rec.Source_Organization_Id (v_count) := ; -- Source Organization ID
l_atp_rec.OE_Flag (v_count) := 'N';
l_atp_rec.Insert_Flag (v_count) := 0; -- Hardcoded value for profile MRP:Calculate Supply Demand 0= NO
l_atp_rec.Attribute_04 (v_count) := 0; -- With this Attribute set to 1 this will enable the Period (Horizontal Plan),
l_atp_rec.Customer_Id (v_count) := NULL;
l_atp_rec.Customer_Site_Id (v_count) := NULL;
l_atp_rec.Calling_Module (v_count) := NULL; -- use 724 when calling from MSC_ATP_CALL - otherwise NULL
l_atp_rec.Row_Id (v_count) := NULL;
l_atp_rec.Source_Organization_Code (v_count) := NULL;
l_atp_rec.Organization_Id (v_count) := ; -- Organization ID
l_atp_rec.order_number (v_count) := NULL;
l_atp_rec.line_number (v_count) := NULL;
l_atp_rec.override_flag (v_count) := 'N';
l_error_message := NULL;

v_count := v_count + 1;

END LOOP;

DBMS_OUTPUT.put_line ('Calling API... ');

APPS.MRP_ATP_PUB.Call_ATP (l_session_id,
l_atp_rec,
x_atp_rec,
x_atp_supply_demand,
x_atp_period,
x_atp_details,
x_return_status,
x_msg_data,
x_msg_count);

DBMS_OUTPUT.put_line (' API Return Status: ' || x_return_status);

DBMS_OUTPUT.put_line (' Query MRP_ATP_SCHEDULE_TEMP_V with session ID '||l_session_id || 'to get ATP Results');

COMMIT;
END;

Advertisements