XML Gateway Transaction for PO Creation and Update using seeded map and transaction

Setup:

1. Creating Hub:

Go to ‘XML Gateway’ ResponsibilityàSelect ‘Define Hubs’

We need to Enter Protocol Address from ‘Application Diagnostics’ Responsibilityà Select Application à Select ‘Application Object Library’ à Select ‘XML Gateway Tests’ à

For HTTP Protocol Address URL Execute ‘OTA Round Trip Test’

For HTTPS Protocol Address URL Execute ‘SSL Test’

2. Trading Partner Setup:

Navigate to ‘XML Gateway’ ResponsibilityàSelect ‘Define Trading Partners’

We Created ‘Supp_Test1’ as a Trading Partner and we will use this supplier in PO.

Transaction Type PO is for Purchasing, and subtype PRO is for Process PO (i.e. a new purchasing document). For change order transmission POCO subtype is entered in the setup. The last letter of the subtype signifies the direction of messaging which is OUTBOUND.

Test

Creating a PO with the Supplier we mentioned in Trading Partner Setup Window.

Click on Approve. In Approval Window ensure that transmission method is XML.

Once PO is Approved .Go to System Administrator-àWorkFlow-àTransaction Monitor

Select ‘Outbound Messages’ and click on ‘Go’.

As Shown above For PO Number 20 the Transaction Sub Type PRO is Created Success- fully. Now we will update this PO to generate Transaction Sub Type POCO.

To View the XML Data, select Document ID and Click on Document, then click on View XML.

Following is the XML File Generated.

This example demonstrates using seeded transaction and map. I will cover custom transaction and map in next posts.

XML Gateway Engine Setup Diagnostic Test

This diagnostic test verifies the XML Gateway engine is working properly.

Go to ‘Application Diagnostics’ Responsibility à Select Application à Select ‘Application Object Library’ à Select ‘XML Gateway Tests’ à

Result : Successful

You are ready to do transactions.

Oracle XML Gateway Overview

Introduction

• Oracle XML Gateway is a set of services that allows easy integration with the Oracle E-Business Suite to support XML messaging.

• With Oracle XML Gateway services, you are assured consistent XML message    implementation when integrating with the Oracle E-Business Suite.

• The majority of messages delivered with the Oracle E-Business Suite are mapped using the Open  Application Group (OAG) standard.

XML Gateway Features

• The services supported by Oracle XML Gateway are grouped into four functional areas as follows:

         Message Designer

         XML Gateway Setup

         Execution Engine

         Transport Agent

• Message Designers:

    Use this wizard-guided tool to define message maps containing the RDBMS-to-XML or

    XML-to-XML data transformations.

•  Utilizes Servlet-Based Transport Agent:

     The servlet-based Transport Agent delivers and receives XML messages using

     SMTP, HTTP, or HTTPS protocols. Protocol types which can be used are as below:

XML Gateway Message Designer

• It is a Wizard Guided Tool used to define message maps. A message map represents the relationship between the source and target data elements.
• Each message map consists of a data source and data target representing where the data is coming from and where it is mapped to.
• There are Two Types of Data Definitions: RDBMS Data Definition & XML Data Definition
• RDBMS-based data definitions can be based on database tables, database views, Application Open Interface tables, or Application APIs.
• XML-based data definitions can be based on an XML Document Type Definition(DTD).
• In Message Designer we are doing two things:

          1. Map Source Data Structure to Target Data Structure

          2. Map Source Data Element to Target Data Element

Define Hub

• A hub is an integration point within your network (either your intranet or the internet). Hubs are typically used to route documents to and from trading partners.
• If we define the hub as the trading partner, you can identify all the buyers and sellers who are conducting business on the hub as Trading Partners to the hub.
• Before defining a hub, you must first complete these setup steps:

               Define System Profile Values

               Define XML Gateway Responsibility

               Define the utl_file_dir parameters

Define Transactions

• Use the Define Transactions form to define the transactions that will be used by the XML Gateway Execution Engine.
• You will then associate these transactions will with a trading partner in the Trading Partner Setup form.
• The Define Transactions form provides the following:

       i. A cross-reference between the external transaction identifiers and the internal Oracle transaction identifiers.

      ii. Identification of the queue from which to retrieve inbound messages

For Outbound and Inbound XML we can refer  ECX_OUTQUEUE and ECX_DOCLOGS tables.

Trading Partner Setup

• Trading Partner Setup plays a vital role in XML Gateway Messaging. 
• It enable messages for the trading partner by identifying the internal and external transaction type and transaction subtype codes, and the XML standard associated with the message.
• In the XML Gateway, the term "Trading Partner" refers to an entity such as a customer, supplier, bank branch, or internal  locations at a particular address with which you exchange messages.
• The Trading Partner Setup form requires an entry for each Transaction Type and Transaction Subtype associated with this trading partner.

XML Gateway Setups

1. Define System Profile Options – The required profile options are as below

1. ECX: Log File Path - The directory path for XML messages and log files. Log File Path where the XML messages and runtime log are stored .

2. ECX: XSLT File Path - The directory path for XSLT style sheets. 

3. ECX: System Administrator Email Address - XML Gateway System Administrator e-mail address.  

4. ECX: Server Time Zone – The time zone in which the database server is running. 

2. Assign XML Gateway Responsibility – Assign ‘XML Gateway’ Responsibility using System Administrator à Security à User à Define

3. Define UTL_FILE_DIR Parameter –

To use Oracle XML Gateway, you must first create directories where the XML message process log and XSLT style sheets will be stored. Oracle XML Gateway uses the UTL_FILE package to read and write to the server.

It is set up in the system.

4. Hub Definitions Form -

A hub is an integration point typically used to route documents to and from trading partners. The Hub Definitions form is used to define the hub and the authorized users conducting business via the hub. The hub users entered in this form will appear on the Trading Partner Setup form.

XML Gateway Responsibility à Define Hubs

Name: Required Field. Enter the Hub name.

Protocol Type: Required Field. Protocol Type is the communication protocol associated with the hub, such as SMTP or HTTP. Select a value from the seeded list of values. The description for the protocol type is displayed

Protocol Address: When protocol type is HTTP or HTTPS, protocol address is prompted.

Protocol Address is the complete URL (including service/servlet) where the Transport Agent, will attempt to post the XML Document. If the Protocol type is SMTP, the protocol address is an e-mail address.

Hub Users

Username (Required): Enter the user name of the trading partner conducting business via the hub.

Password (Required): Enter the password for this user.

Hub Entity Code (Required): Enter the hub entity code for this user. It is the code found in the XML envelope to identify the source of the message.

5. Define XML Standards Form -

XML Gateway Responsibility à Define XML Standards

This form defines standards bodies for XML messages, such as OAG (Open Applications Group), ROSETTANET etc. Most probably we will be using OAG as XML Standards so need not create any entry here.

6. Define Transactions Form - 

XML Gateway Responsibility à Define Transactions

Define Transactions form is used to define the transactions that will be used by the XML Gateway Execution Engine. We then associate these transactions with a trading partner in the Trading Partner Setup form. The Define Transactions form provides the following:

• A cross-reference between the external transaction identifiers and the internal Oracle transaction identifiers.

• Identification of the queue from which to retrieve inbound messages

Party Type (Required): Party Type defines the type of trading partner, such as Supplier, Customer, Bank, or internal locations (such as warehouses). Select a value from the list of values.

Transaction Type (Required): Transaction Type is the product short name for the base Oracle Application associated with the transaction, such as "AR" for Oracle Receivables.

Transaction Subtype (Required): Transaction Subtype is a code for a particular transaction within the application specified by the Transaction Type. The last position of the code represents the direction of the transaction: "I" for inbound, "O" for outbound.

Transaction Description: Enter a description for the transaction.

Standard Code (Required): The XML standard to be used for this transaction. The Standard Codes are set up in the Define XML Standards form. Choose the code from the list of values.

Direction (Required): Direction indicates if the message is inbound or outbound. Select "IN" for inbound messages, or "OUT" for outbound messages from the list of values.

External Transaction Type (Required): External Transaction Type is the primary external identifier for the XML message.

External Transaction Subtype (Required): External Transaction Subtype is the secondary external identifier for the XML message. The combination of the External Transaction Type and the External Transaction Subtype should cross-reference this message to the Oracle internal transaction identified by the Transaction Type and the Transaction Subtype.

Queue (Required for Inbound Messages): A queue is a table in a database where transactions are staged for processing. Default queues are defined during installation. Select a queue from the list of values. The field is disabled for outbound messages.

7. Define Lookup Values –

XML Gateway Responsibility à Define Lookup Values

Seeded lookup types are defined here which are used in XML Gateway.

8. Trading Partner Setup –

XML Gateway Responsibility à Define Trading Partners

The Trading Partner Setup form is used to:

• Set up trading partners for multiple operating units.

• Enable messages for the trading partner by identifying the internal and external transaction type and transaction subtype codes, and the XML standard associated with the message.

• Access the Trading Partner User Setup form.

• Access the Trading Partner Code Conversion form.

• Select a message map for the trading partner.

• Identify the communications protocol and address for a message. Optionally, the user can be selected from a hub.

Trading Partner Type (Required):

Trading Partner Type defines the type of trading partner, such as Supplier, Customer, Bank or internal location.

Trading Partner Name (Required):

Given the selection in the Trading Partner Type, the appropriate Trading Partner

Names are displayed in the Trading Partner Name list of values.

Trading Partner Site (Required):

Given the selection in the Trading Partner Name, the appropriate Trading Partner Sites are displayed in the list of values.

Company Admin Email (Required):

This is the e-mail address of the administration contact to receive e-mails regarding warnings and errors.

Trading Partner Details

These details are discussed earlier in the document.

9. Code Conversion

The Oracle XML Gateway code conversion function provides a method to cross-reference the codes defined in Oracle E-Business Suite to codes used by trading partners, the XML standard, or other standard codes in the transactions.

XML Gateway Responsibility à Define Code Conversion

Location where BI Publisher Report Output (PDF, EXCEL, HTML, RTF, etc.) stored in server

Many a times we come across requirement wherein we want to email/FTP report output generated from BI Publisher report. Well, you can get report output using below query :

SELECT outputfile_name
  FROM fnd_concurrent_requests
 WHERE request_id = p_request_id        -- (Request ID of your report program);

However, this query will give report output in .out format.
Suppose BI Publisher report is in RTF, EXCEL, HTML or PDF format and we want to email/FTP this report output, where can we get this from server?

This is stored in server and to get the location you can use below query :

SELECT file_type, file_name
  FROM fnd_conc_req_outputs
 WHERE concurrent_request_id = p_request_id -- (Request ID of your report program);

Hope this helps !!!