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;

Label Printing from PL/SQL

There are cases where we need to print Labels from PL/SQL Package. It can be achieved through Oracle API. (INV_PRINT_REQUEST.SEND_XML_TCPIP)
API Description
/*
* Method for sending a string to a TCP-IP address. Used to send the
* label-XML file to a print-server. The x_return_msg returns any
* message send back from this TCP-IP address. This function returns
* 0 if the file was send successfully. It returns -1 if there was
* any error.
*/

Usage Example

v_num_return :=
INV_PRINT_REQUEST.SEND_XML_TCPIP (v_chr_ip_address,
TO_CHAR (v_num_port_no),
v_chr_xml_content,
v_chr_ret_msg,
v_chr_print_status);

Limitations :- XML Content cannot exceed 4000 Chars.

Reset Password from Backend in Oracle Apps

DECLARE
v_chr_user_name fnd_user.user_name%TYPE := UPPER (‘&Enter_User_Name’);
v_chr_new_password VARCHAR2 (30) := ‘&Enter_New_Password’;
v_boo_status BOOLEAN;
BEGIN
v_boo_status :=
fnd_user_pkg.ChangePassword (username => v_chr_user_name,
newpassword => v_chr_new_password);

IF v_boo_status = TRUE
THEN
DBMS_OUTPUT.put_line (
‘Password Reset Successful for ‘ || v_chr_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.put_line (
‘Failed to Reset Password’ || SQLCODE || ‘ ‘ || SQLERRM);
END IF;
END;

How to Add a Responsibility from Backend

PL/SQL Script to be executed from APPS

BEGIN
fnd_user_resp_groups_api.load_row
(x_user_name           => <UserName>,
x_resp_key            => <Resp Key of Responsibility>,
x_app_short_name      => ‘SYSADMIN’,
x_security_group      => ‘STANDARD’,
x_owner               => ‘APPS’,
x_start_date          => SYSDATE,
x_end_date            => NULL,
x_description         => ‘Description’
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (   ‘Resp. not assigned to : ‘
|| ‘USER_NAME’
|| ‘ Error : ‘
|| SQLERRM
);
END;