XML Gateway : Populate the ECX_INBOUND queue from backend

Please refer to http://oracleappstoday.blogspot.in/2014/06/create-custom-queue-in-oracle-and-use.html  to learn to use JMS queue as a XML payload holder before it is enqueued to ECX_INBOUND queue.

In order to populate the ECX_INBOUND queue from backend you need to do the following
Create a ECX_MSG
Enqueue the ECX_MSG to the standard ECX_INBOUND queue

The code below achieves both the above points
Please feel free to reach out to me


 set serveroutput on;
DECLARE
v_destination_queue    VARCHAR2 (100) := 'APPLSYS.ECX_INBOUND';
v_enqueue_options      SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
v_enqueue_properties   SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
        v_enqueue_payload      SYSTEM.ecxmsg;
x_enqueue_msgid        RAW (16);
        v_protocol_add         VARCHAR2(100) :=      'https://XXAB.dev1.XXAB.com:4443/oa_servlets/oracle.apps.ecx.oxta.TransportAgentServer';

         v_xml_message CLOB     := '<HEADER><NAME>Hello World</NAME></HEADER>';

BEGIN

v_enqueue_payload      :=
         SYSTEM.ecxmsg (
               message_type          => 'XML',
               message_standard      => 'OAG',
               transaction_type      => 'XX_TRX_TYPE_EBS',  -- Transaction Type
               transaction_subtype   => 'XX_SUB_TRX_TYPE_EBS', -- Transaction Sub Type
               document_number       => '110',  -- Doucument Number is a Unique Number
               partyid               => NULL,
               party_site_id         => 'XXRHESB',
               party_type            => NULL,
               protocol_type         => NULL,
               protocol_address      => v_protocol_add,
               username              => 'apps',
               password              => 'apps',
               payload               => v_xml_message
            );
         
        sys.DBMS_AQ.enqueue (
            queue_name           => v_destination_queue,
            enqueue_options      => v_enqueue_options,
            message_properties   => v_enqueue_properties,
            payload              => v_enqueue_payload,
            msgid                => x_enqueue_msgid
         );      
   
COMMIT;
     
       dbms_output.put_line(x_enqueue_msgid);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Read More

Oracle XML Gateway Tables : The Backend View

--Setup Transaction Type - Back-end Tables
select * from ecx_transactions;
select * from ecx_transactions_vl;
select * from ecx_transactions_b;

--Setup Trading Partner - Back-end Tables
select * from ecx_tp_headers;
select * from ecx_tp_details;

--Get all the details in one go here
select * from ecx_tp_details_v;


--XML Gateway map tables
select * from ecx_dtds;
select * from ecx_mappings;

--Mapping Tables
select * from ecx_proc_mappings; --Procedure Mappings
select * from ecx_attribute_mappings;
select * from ecx_level_mappings ;


--Run Time Tables
select * from ecx_doclogs;
select * from ecx_error_msgs;
select * from ecx_inbound_logs;
select * from ecx_msg_logs;

Read More

Create Custom Queue in Oracle and use as pre-queue to other queue

In our scenario we setup a JMS queue which will be used a pre-queue to ECX_INBOUND queue.
Below are the steps to achieve the same.

Queue Creation


1) Create a queue table
BEGIN
dbms_aqadm.create_queue_table
     ( queue_table        => 'XX_INQUEUE_TBL'
     , queue_payload_type => 'sys.aq$_jms_text_message'
     );
END;

2) Create a Queue based on queue table
    BEGIN
dbms_aqadm.create_queue
    ( queue_name  => 'XX_INQUEUE'
    , queue_table => 'XX_INQUEUE_TBL'
    );
END;

3) Start Queue. If you do no start the queue you will get error message
   as "ORA-25207: enqueue failed, queue APPS.XX_INQUEUE is disabled from enqueueing"

BEGIN
dbms_aqadm.start_queue
( queue_name => 'XX_INQUEUE'
);
END;

4) Query confirm queue and queue table. 

SELECT *
 FROM XX_INQUEUE_TBL
WHERE q_name = 'XX_INQUEUE';

SELECT *
 FROM DBA_QUEUES
WHERE name = 'XX_INQUEUE';


 Populating queue with XML message

 1) Run the block below to populate the queue
set serveroutput on;
DECLARE
l_destination_queue    VARCHAR2 (100) := 'XX_INQUEUE';
l_enqueue_options      SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
l_enqueue_properties   SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
l_enqueue_payload      SYS.aq$_jms_text_message;
x_enqueue_msgid        RAW (16);
l_xml_message CLOB := '<HEADER><NAME>Hello World</NAME></HEADER>';

BEGIN
 l_enqueue_payload := SYS.AQ$_JMS_TEXT_MESSAGE.construct();
 l_enqueue_payload.set_text (l_xml_message);
 sys.DBMS_AQ.enqueue (
queue_name           => l_destination_queue,
enqueue_options      => l_enqueue_options,
message_properties   => l_enqueue_properties,
payload              => l_enqueue_payload,
msgid                => x_enqueue_msgid
);
 dbms_output.put_line(x_enqueue_msgid);  
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

 2) Query the queue table to verify data is populated the queue
SELECT *
  FROM XX_INQUEUE_TBL
 WHERE q_name = 'XX_INQUEUE';

The user is displayed as [SYS.AQ$_JMS_TEXT_MESSAGE]
If you want to view the payload use the query below
SELECT A.*,A.user_data.text_vc
  FROM XX_INQUEUE_TBL A
 WHERE q_name = 'XX_INQUEUE';


Once we have the data in XX_INQUEUE. We will now dequeue the data
and enqueue it into the ECX_INBOUND queue for further processing
Below a are the api's you need to enqueue the ECX_INBOUND queue.
      SYSTEM.ecxmsg     : This is required to create a payload
sys.DBMS_AQ.enqueue : This is required to enqueue the message
  to standard APPLSYS.ECX_INBOUND queue.

I will cover the above two api's in my next post.

Read More

Common Mistakes : Oracle XML Gateway Map Development

TroubleShooting is a very tiresome task in XML Gateway.
You can reduce much of the effort by being careful about the below most common mistakes in XML Gateway

1) For Inbound Transaction Make sure the Source Definition conforms to the DTD Structure
For example , DTD expects the elements as NAME* AGE* and during the XML Gateway
 source definition we have them as <AGE><NAME>.
 This wont be a reflected as an error but the database fields mapped to them will
 have no values after the XML message is processed

2) ORA-06502 Ecx_actions.bind_proc_variable
This error most commonly occurs when there is a mismatch between database procedure parameters           and Execute Procedure Action in XML Gateway Map. Verify if there is no mismatch.
No null are being passed to not null parameters.

If you have changed the procedure parameters recently. Make sure you redefine EXECUTE                        PROCEDURE action again in the Maps

3) ORA-06502: PL/SQL: numeric or value error: NULL index table key value – ECX_ACTIONS.GET_VAR_ATTR
This happens when a action does not have a variable or value attached to it.
Most common mistake. You attached a variable to action. You later realise you do not need a variable         and delete it. The result of which is you have an action but no variable in it. You will thereby receive the         above error.

4) Changes not reflecting ?? No matter what you do error is not resolved and you are pretty sure you did it       right

Always, BIG Always, Always delete your MAP before you Load modified Map. I have been stuck for        days just because I did not delete the map and load the map again.

Also make sure you delete a map with a map name as parameter. You load a map with a file name as parameter.
Also  make sure while loading the map you are in the directory which contains the .xgm map file.

Recheck the commands below

Delete Map
java oracle.apps.ecx.loader.DeleteMap apps apps_pass ebsdev.XXXX.com:80010:EBSDEV XX_PAYLOAD_MAP

Load Map
java oracle.apps.ecx.loader.LoadMap apps apps_pass ebsdev.XXXX.com:80010:EBSDEV XX_PAYLOAD_MAP.xgm

5) You can use the below query for error messges

SELECT il.error_id,
  er.message,
  dl.payload
 FROM ECX_INBOUND_LOGS il,
  ECX_ERROR_MSGS er,
  ECX_DOCLOGS dl
WHERE il.error_id         = er.error_id(+)
       AND il.MSGID              = dl.msgid(+)
  AND dl.transaction_type   = &p_transaction_type
  AND dl.transaction_subtype= &p_tran_subtype
  AND il.status            IN (1,2)
  AND dl.time_stamp         > to_date('17-JUN-14','DD-MON_YY')
  ORDER BY il.time_stamp ASC;

6) If non of the above helps you can refer to the docs below

783908.1 : XML Gateway Debugging in R12
742588.1 : XML Gateway Data Collection Script for Inbound ECX Transactions.

Read More

XML Gateway ECX Transaction Logs

Pre 11.5.10: the logs are directed to a directory present in UTL_FILE_DIRECTORY
Post 11.5.10: the logs are maintained using FND_LOGS

How to enable Logs
Enable the Profile below

FND: Debug Log Module = ecx%
FND: Debug Log Enabled = Yes
FND: Debug Log Level = Statement

Login and navigate to Oracle Application Manager > Monitoring > Logs and query for module: ecx%.

The logs can also be retrieved by the below steps:

Execute $ECX_TOP/patch/115/sql/ECXLGINI.sql.
Review the Log messages in OAM UI. Login and navigate to Oracle Application Manager > Monitoring > Logs and query for module: ecx%.



Physical ecx log file can be fetched as below:

·         Run the below query:

 select module from apps.fnd_log_messages where module like ‘ecx%’ order by     log_sequence;

·         execute $ECX_TOP/patch/115/sql/ECXLGUTL.sql .

This script will prompt for a module name.

Read More

XML Gateway Inbound and Outbound Process

The XML Gateway can be used for Inbound and Outbound Interfacing

Inbound flow
1) OTA sends the message to ECX_INBOUND queue. ECX_INBOUND queue comprises of queue table ECX_INQUEUE.
2) ECX Inbound agent listener moves it from ECX_INQUEUE to ECX_TRANSACTION queue.
3) The ECX transaction agent listener triggers the subscription which pushes the data into the tables as per the XML Gateway Map.


Outbound flow
1) ECX message is generated.
2) Execution engine pushes it into ECX_OUTBOUND queue which comprises of queue table ECX_OUTQUEUE.
3) Message is de-queued by OTA and is send to the trading partner

Read More