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

How to preserve Leading zeros in XML Publisher Excel Ouputs

In XML Pub Excel Reports excel removes the leading Zeros and prints the whole number. There are many instances where we need to print the zeros for eg item numbers starting with 0.

There are many ways to achieve this, one of the simple ways is to convert the number to character by adding space at the end.

concatenate two spaces after the xml tag as shown below in your template.

<?concat(<XMLTag>,’  ‘)?>

Downside of it is you will not be able to use excel features like sum , count etc on this specific column.

How to generate Session Trace from Toad or SQL Plus

alter session set max_dump_file_size = 50000000;

alter session set sql_trace = true;

Run your SQL’s
alter session set sql_trace = false;

— Run the following query to find the trc file

select vpar.value || ‘/’ || instance || ‘_ora_’ || ltrim(to_char(vp.spid,’fm99999′)) || ‘.trc’
from v$process vp, v$session vs, v$parameter vpar, v$thread vthr
where vp.addr = vs.paddr
and vs.audsid = userenv(‘sessionid’)
and = ‘user_dump_dest’
Once you have the trc file use the standard tkprof procedure to get the tkp / tkprof file