ATP Inquiry API

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
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 ,

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


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

FOR rec_cur_items IN cur_items
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;


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

APPS.MRP_ATP_PUB.Call_ATP (l_session_id,

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');


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 :=
TO_CHAR (v_num_port_no),

Limitations :- XML Content cannot exceed 4000 Chars.

Namespace prefix ‘ref’ used but not declared in XML Publisher


Caused by: oracle.xdo.parser.v2.XPathException: Namespace prefix 'ref' used but not declared.
	at oracle.xdo.parser.v2.XSLProcessor.reportException(
	at oracle.xdo.parser.v2.XSLProcessor.newXSLStylesheet(

This Error generally due to higher BI Publisher versions.

If Word Properties of the XML Field has source something like ‘<?ref:xdo0040?>’ you will face this error


Make BI Publisher backward compatible.


Go to Options


Build -> Form field size  -> Set to Backward Compatible.




BOM Explosion Query

SELECT level ,
msi1.segment1 AS "Parent",
case when msi1.PLANNING_MAKE_BUY_CODE=1 then
when msi1.PLANNING_MAKE_BUY_CODE=2 then
LPAD (' ', 10 * (LEVEL - 1)) || msi2.segment1 AS "Component",
bic.COMPONENT_QUANTITY AS "Component Quantity",
case when msi2.PLANNING_MAKE_BUY_CODE=1 then
when msi2.PLANNING_MAKE_BUY_CODE=2 then
--LEVEL + 1,
SYS_CONNECT_BY_PATH (msi1.segment1, ' / ') || ' / ' || msi2.segment1
"Full Structure"
FROM apps.bom_inventory_components bic,
(select * from apps.bom_bill_of_materials where organization_id =&Organization_id )bom,
apps.mtl_system_items msi1,
apps.mtl_system_items msi2
WHERE bom.bill_sequence_id = bic.bill_sequence_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND msi1.organization_id = bom.organization_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = bom.organization_id
START WITH bom.assembly_item_id IN
(SELECT msib.inventory_item_id
FROM apps.mtl_system_items_b msib
WHERE msib.segment1=&Item_number
AND msib.organization_id = &Organization_id )
CONNECT BY NOCYCLE PRIOR bic.component_item_id = bom.assembly_item_id

XML Publisher Add On Not working in Office 2013 / Office 2016 / Office 365

If you are unable to add XML Publisher add-on to MS Word. Most Probably it is due to 32 bit version of Office.

Follow the below instructions.

Check your MS Office Bit version . MS Word -> File -> Account -> About Word



If it is 32 bit version install 32 bit version of XML Publisher . Once you install 32 bit version It should automatically be visible on MS Word.

OutOfMemory Error with XML Publisher Reports

We see this error (java.lang.OutOfMemoryError) generally when there is a huge data to process for OPP.

There are multiple options to resolve this error.

  1. Increased profile option value Concurrent:OPP Process Timeout to a higher value.   If increasing the profile option value doesn’t work do the following
  2.  Increase OPP memory size from default  to 2 GB

Use the below query to find out the size. if it is less than 2GB , increase the value to 2GB.

SELECT developer_parameters

  FROM fnd_cp_services s, fnd_concurrent_queues q

WHERE q.concurrent_queue_name = ‘FNDCPOPP’ and s.service_id = q.manager_type

Change the value to ‘J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m

BI Publisher Add-In does not show up in Office 2010/Windows 7

If you are installing BI Publisher for the first time in your Laptop .If you have a Windows 7 (64 bit) Laptop and if your MS office is a 32 bit version, you will face this issue.

Where can i see my office version


The XML Publisher installation will be smooth, but if you open the MS Word you will not see the Add in Option in the tool bar.


Because you are installing a software that’s compatible on a 32 bit version on a 64 bit machine, Microsoft makes this Add in as an Inactive Add in

Open MS Word.go to File–> Options – Word options box opens and in that go to Add Ins


Under the Inactive application Add on you will see “BI Publisher Template for Word” as a COM Add in. In the above screen shot it shows active because i have fixed the issue

If you go to Manage: COM Add in at the bottom and try to enable this Add in, because its considered malicious Microsoft is going to make it Inactive again.


The only way out is You need to install “Visual Studio 2010 Tools for Office Run time” from Microsoft site, its a free executable.  Visual Studio 2010 download link The moment you install this “BI Publisher Template for Word” will become an active Add on and then bing!, you will see the BI Publisher Add in show up once you restart your word.


Caution: Close all MS Office related stuff during this installation process .



List Of Responsibilities to which a concurrent program is Attached

This simple query can help you to identify the list of responsibilities to which a concurrent program is attached.

SELECT DISTINCT responsibility_name
FROM apps.fnd_responsibility_vl frv,
apps.fnd_request_group_units frg,
apps.fnd_concurrent_programs_tl fcp
WHERE frv.request_group_id = frg.request_group_id
AND frg.request_unit_id = fcp.concurrent_program_id
AND fcp.user_concurrent_program_name = ‘<Concurrent Program Name>’