The Oracle XML Gateway provides the following APIs:
The following APIs are for use with the execution engine:
Package: ECX_STANDARD
Package: ECX_ERRORLOG
procedure perform_xslt_transformation
(i_xml_file in out clob, i_xslt_file_name in varchar2, i_xslt_file_ver in number, i_xslt_application_code in varchar2, i_dtd_file_name in varchar2 default null, i_dtd_root_element in varchar2 default null, i_dtd_version in varchar2 default null, i_retcode out pls_integer, i_retmsg out varchar2);
Used to apply a style sheet to an XML message and return the transformed XML messaged for further processing by the calling environment.
The DTD file name, version, and root element are required input parameters for this API, therefore the associated DTDs must be loaded into the XML Gateway repository. Refer to Loading and Deleting a DTD for details on loading a DTD.
This API is independent of the Message Designer: XSLT Transformation action. This API is not intended for use in a message map.
Note: The profile option ECX_XML_VALIDATE_FLAG must be set to"Y"for this action to be performed. For more information on this profile option, see Define System Profile Options.
| Variable | Description |
|---|---|
| i_xml_file | The XML message to be transformed. |
| i_xslt_file_name | The XSLT style sheet file to be used for the transformation. |
| i_xslt_file_ver | The version of the XSLT style sheet file. The highest version with the same file name and application code is used if no version number is provided. |
| i_xslt_application_code | The name of the subdirectory where the XSLT style sheet is source-controlled (for example: ar/xml/xslt). |
| i_dtd_file_name | The name of the DTD used in the XML message. |
| i_dtd_root_element | The root element of the DTD used in the XML message. |
| i_dtd_version | Version of the DTD used in the XML message. |
| Variable | Description |
|---|---|
| i_xml_file | The transformed XML message. |
| i_retcode | Return code for the procedure. |
| i_retmsg | Return message for the procedure. |
procedure external_system
(i_outmsgid in raw, i_status in pls_integer, i_errmsg in varchar2 default null, i_timestamp in date, i_errparams in varchar2, o_ret_code out pls_integer, o_ret_msg out varchar2);
Used by both Oracle and non-Oracle messaging systems to report delivery status. The status information is written to the XML Gateway log tables to track and report transaction delivery data.
| Variable | Description |
|---|---|
| i_outmsgid | Message ID maintained by the XML Gateway execution engine for the outbound message delivered by the messaging system. |
| i_status | Message delivery status as reported by the messaging system. |
| i_errmsg | Error messages reported by the messaging system. |
| i_timestamp | Time stamp from the messaging system indicating when it processed the outbound message created by XML Gateway. |
| i_errparams | Errors reported by messaging system in multilingual delivery. |
| Variable | Description |
|---|---|
| o_ret_code | Return code for the procedure |
| o_ret_msg | Return message for the procedure |
XML Gateway has provided special purpose procedures and functions for use in the Message Designer to perform the following tasks:
Set event details to raise a business event for inbound transactions
Get event details using the event name maintained by the XML Gateway Execution Engine
Perform string manipulations
Get Trading Partner information using the sender's or receiver's Trading Partner ID maintained by the XML Gateway Execution Engine
Get message envelope data for inbound message
Get delivery data for outbound message
Get document logging information for a business document
Set message delivery status
Get System Administrator e-mail address from ECX: System Administrator Email Address system profile
Get sender logical ID from ECX_OAG_LOGICALID system profile
Set Error Exit
Use the Message Designer, Procedure Call or Function Call actions to initiate the APIs.
Important: With the exception of ECX_ERRORLOG.external_system, the procedures and functions in this appendix are meant for use in the Message Designer only and should not be used in any other context.
Most procedures include input and output arguments. You can map a source or target variable to the input arguments. The output arguments can be used in conjunction with the Send Error Message action for warnings or the ECX_ACTIONS.set_error_exit_program API for serious errors to send a notification. The notification may be sent to the Trading Partner contact, the system administrator (identified in the ECX: System Administrator Email Address system profile), or both.
If the successful completion of an API is critical to the success of your transaction, you may wish to use the Exit Program action to terminate the transaction if the API fails to process completely.
The following APIs are described in this section:
Package: ECX_STANDARD
Package: ECX_DOCUMENT
Package: ECX_CONDITIONS
Package: ECX_TRADING_PARTNER_PVT
Package: ECX_ERRORLOG
Package: ECX_ACTIONS
Package: ECX_ATTACHMENT
Package: ECX_ENG_UTILS
procedure setEventDetails
(eventname in varchar2, eventkey in varchar2, parameter1 in varchar2, parameter2 in varchar2, parameter3 in varchar2, parameter4 in varchar2, parameter5 in varchar2, parameter6 in varchar2, parameter7 in varchar2, parameter8 in varchar2, parameter9 in varchar2, parameter10 in varchar2, retcode out pls_integer, retmsg out varchar2);
Sets event details to raise a business event for inbound transactions.
This is defined at the root level as a post-process action to indicate that an inbound message has been processed. Any event subscription defined in the Oracle E-Business Suite interested in this inbound message will proceed to consume it.
This procedure must be used for all inbound transactions. The argument values vary by transaction with the exception of the confirmation message where the argument values are specific. See How to Implement an OAG Confirmation BoD to see how the event details are defined.
| Variable | Description |
|---|---|
| eventname | Unique identifier for the business event associated with the inbound message. The event name consists of the following components: ORACLE.APPS.<COMPONENT>.<TASK>.<EVENT> where: COMPONENT is based on the internal transaction type entered using the Define Transactions window. It represents the product short code. TASK is based on the internal transaction subtype entered using the Define Transactions window. It represents a description of the object. EVENT is a literal that describes the business function of the message. Following is an example of an Event Name that identifies a confirmation event associated with an outbound purchase order ORACLE.APPS.PO.POO.CONFIRM |
| eventkey | Unique identifier for the business document from the Oracle E-Business Suite associated with the business event. |
| parameter1 through parameter10 | User-defined parameters to pass data of interest to the event subscription defined in the Oracle E-Business Suite for the inbound business document. |
| Variable | Description |
|---|---|
| retcode | Return code for the procedure. |
| retmsg | Return message for the procedure. |
procedure getEventDetails
(eventname out varchar2, eventkey out varchar2, itemtype out varchar2, itemkey out varchar2, parentitemtype out varchar2, parentitemkey out varchar2, retcode out pls_integer, retmsg out varchar2);
Gets event details using the event name maintained by the XML Gateway Execution Engine.
None.
| Variable | Description |
|---|---|
| eventname | Event name passed internally to the procedure. It is a unique identifier for the business event associated with the transaction. |
| eventkey | Event key associated with the event name passed. |
| itemtype | Unique identifier for a group of objects that share the same set of item attributes (also known as variables). Item types are created using the Workflow Builder and are used by Oracle e-Business Suite application modules to group related functions. |
| itemkey | Unique identifier for an item in an item type |
| parentitemtype | Parent item type for the item type |
| parentitemkey | Parent item key for the item key |
| retcode | Return code for the procedure |
| retmsg | Return message for the procedure |
(from_agent out varchar2, to_agent out varchar2, from_system out varchar2, to_system out varchar2, retcode out pls_integer, retmsg out varchar2);
Gets event details related to the system and agent using the event name maintained by the XML Gateway Execution Engine.
The procedure is context-sensitive, so you will receive return values relevant to the context. The "from" parameters are for inbound transactions. The "to" parameters are for outbound transactions.
None
| Variable | Description |
|---|---|
| from_agent | Workflow agent (queue) the inbound message is dequeued from. |
| to_agent | Workflow agent (queue) the outbound message is enqueued to. |
| from_system | System processing the inbound message |
| to_system | System processing the outbound message |
| retcode | Return code for the procedure |
| retmsg | Return code for the message |
function getReferenceID
return varchar2;
Returns the value associated with the REFERENCEID element of the OAG CONTROLAREA. The field contains a concatenated value consisting of system name, event name, and event key delimited by ":". This function is used for message maps in which the OAG standard is not used.
For message maps created using the OAG standard, the ECX_OAG_CONTROLAREA_TP_V view is used to retrieve the reference_id identified by the business event. This value is used to map to the OAG CNTROLAREA, REFERENCEID element.
None.
None.
procedure get_devlivery_attribs
(transaction_type in varchar2, transaction_subtype in varchar2, party_id in varchar2, party_site_id in varchar2, party_type in/out varchar2, standard_type out varchar2, standard_code out varchar2, ext_type out varchar2, ext_subtype out varchar2, source_code out varchar2, destination_code out varchar2, destination_type out varchar2, destination_address out varchar2, username out varchar2, password out varchar2, map_code out varchar2, queue_name out varchar2, tp_header_id out pls_integer, retcode out pls_integer, retmsg out vachar2);
Gets setup data using the internal transaction type, subtype, party id, party_type, and party site id. The data is required to process an outbound transaction.
| Variable | Description |
|---|---|
| transaction_type | Internal transaction type passed to the procedure. The internal transaction type is entered using the Define Transactions window. |
| transaction_subtype | Internal transaction subtype passed to the procedure. The internal transaction subtype is entered using the Define Transactions window. |
| party_id | Trading partner ID passed to the procedure. Party site ID will be used if Party ID is null. The trading partner ID is entered using the Define Trading Partners window. |
| party_site_id | Trading partner site ID passed to the procedure. The trading partner site ID is entered using the Define Trading Partners window. |
| party_type | Party type associated with the trading partner and trading partner site passed to the procedure. Party type is entered using the Define Trading Partners window. |
| Variable | Description |
|---|---|
| party_type | Party type associated with the trading partner and trading partner site passed to the procedure. Party type is entered using the Define Trading Partners window. |
| standard_type | The XML standard type associated with the transaction passed to the procedure. Standard type is entered using the Define XML Standards window. |
| standard_code | The standard code (for example, OAG) associated with the transaction passed to the procedure. Standard code is entered using the Define XML Standards window. |
| ext_type | External transaction type associated with the internal transaction type passed to the procedure. External transaction type is entered using the Define Transactions window. |
| ext_subtype | External transaction subtype associated with the internal transaction subtype passed to the procedure. External transaction subtype is entered using the Define Transactions window. |
| source_code | Source location code associated with the trading partner and trading partner site passed to the procedure. Source location code is entered using the Define Trading Partners window. |
| destination_code | Destination location code associated with the trading partner and trading partner site passed to the procedure. Destination location code is entered using the Define Trading Partners window. |
| destination_type | Destination location type associated with the trading partner and trading partner site passed to the procedure. Destination location type is entered using the Define Trading Partners window. |
| destination_address | Destination address associated with the trading partner and trading partner site passed to the procedure. Destination address is entered using the Define Trading Partners window. |
| username | Username associated with the trading partner and trading partner site passed to the procedure. Username is entered using the Define Trading Partners window. |
| password | Password associated with the username for the trading partner and trading partner site passed to the procedure. The password is returned in encrypted format. Password is entered using the Define Trading Partners window. |
| map_code | Message map associated with the trading partner and transaction passed to the procedure. The map code is entered using the Define Trading Partners window. |
| queue_name | Queue name associated with the transaction passed to the procedure. The queue name is entered using the Define Transactions window. |
| tp_header_id | System-generated identifier for the trading partner passed to the procedure, entered using the Define Trading Partners window |
| retcode | Return code for the procedure |
| retmsg | Return message for the procedure |
procedure getLengthForString
(i_string in varchar2); i_length out pls_integer);
Determines the length of the string passed to it. This procedure may be used in conjunction with the getPositionInString procedure or the Perform Substring action.
| Variable | Description |
|---|---|
| i_string | The input string. |
| Variable | Description |
|---|---|
| i_length | The length of the input string. |
procedure getPositionInString
(i_string in varchar2, i_search_string in varchar2, i_start_position in pls_integer, default null, i_occurrence in pls_integer, default null, i_position out pls_integer);
Parses a concatenated string with delimiters into its individual components. The i_search_string parameter identifies the delimiter. The i_occurrence parameter identifies which occurrence of the delimiter to check for. The return value of the procedure is the position of the first character of the portion of the string you are interested in.
This procedure can be used in conjunction with the getLengthForString procedure or the Perform Substring action.
| Variable | Description |
|---|---|
| i_string | The input string. |
| i_search_string | The delimiter used in the concatenated string. |
| i_start_position | The character position to begin parsing from. |
| i_occurrence | The occurrence of the delimiter to search for. |
| Variable | Description |
|---|---|
| i_position | The character position of the first character of the portion of the input string you are interested in. |
procedure getSubString
(i_string in varchar2, i_start_position in pls_integer, default 0, i_length in pls_integer, default 0, i_substr out varchar2);
Parses a string passed to it given the start position and the length of the substring.
This procedure is used when the length of the substring is maintained in a variable whereas the Perform Substring action is used if the length of the string is a literal value.
| Variable | Description |
|---|---|
| i_string | The input string. |
| i_start_position | The character position to begin parsing from. |
| i_length | Length from start position of input string to include in resulting substring. |
| Variable | Description |
|---|---|
| i_substr | The substring. |
procedure get_receivers_tp_info
(p_party_id out number, p_party_site_id out number, p_org_id out pls_integer, p_admin_email out varchar2, retcode out pls_integer, retmsg out varchar2);
Gets Trading Partner data using the receiver's trading partner ID maintained by the XML Gateway Execution Engine. The Trading Partner data is entered using the Define Trading Partners window.
None
| Variable | Description |
|---|---|
| p_party_id | Trading Partner ID |
| p_party_site_id | Site associated with the Trading Partner. |
| p_org_id | Organization associated with the Trading Partner site. |
| p_admin_email | E-mail address associated with the Trading Partner contact. |
| retcode | Return code for the procedure |
| retmsg | Return message for the procedure |
procedure get_senders_tp_info
(p_party_id out number, p_party_site_id out number, p_org_id out pls_integer, p_admin_email out varchar2, retcode out pls_integer, retmsg out varchar2);
Gets Trading Partner data using the sender's trading partner ID maintained by the XML Gateway Execution Engine. The Trading Partner data is entered using the Define Trading Partners window.
None
| Variable | Description |
|---|---|
| p_party_id | Trading Partner ID |
| p_party_site_id | Site associated with the Trading Partner. |
| p_org_id | Organization associated with the Trading Partner site. |
| p_admin_email | E-mail address associated with the Trading Partner contact. |
| retcode | Return code for the procedure |
| retmsg | Return message for the procedure |
procedure get_sysadmin_email
(email_address out varchar2, retcode out pls_integer, errmsg out varchar2);
Gets the system administrator e-mail address defined for the ECX: System Administrator Email Address profile option.
This procedure is not required if your are using the Send Error Message action to send notifications to the system administrator. The address for the system administrator is derived.
None
| Variable | Description |
|---|---|
| email_address | E-mail address identified in the ECX: System Administrator Email Address system profile. |
| retcode | Return code for the procedure |
| errmsg | Return message for the procedure |
procedure getEnvelopeInformation
(i_internal_control_number in pls_integer, i_message_type out varchar2, i_message_standard out varchar2, i_transaction_type out varchar2, i_transaction_subtype out varchar2, i_document_number out varchar2, i_party_id out varchar2, i_party_site_id out varchar2, i_protocol_type out varchar2, i_protocol_address out varchar2, i_username out varchar2, i_password out varchar2, i_attribute1 out varchar2, i_attribute2 out varchar2, i_attribute3 out varchar2, i_attribute4 out varchar2, i_attribute5 out varchar2, retcode out pls_integer, retmsg out varchar2);
Retrieves message envelope data using the internal control number maintained by the XML Gateway Execution Engine.
See XML Gateway Envelope for information regarding the message envelope.
| Variable | Description |
|---|---|
| i_internal_control_number | Internal control number maintained by the XML Gateway execution engine associated with the inbound message. |
| Variable | Description |
|---|---|
| i_message_type | The message type is defaulted to "XML" |
| i_message_standard | The XML standard used for the business document received from the Trading Partner. The XML standard is entered using the Define XML Standards window and used in the Define Transactions window. |
| i_transaction_type | External transaction type associated with the business document received from the Trading Partner. The external transaction type is entered using the Define Transactions window and is used in the Define Trading Partners window. |
| i_transaction_ subtype | External transaction subtype associated with the business document received from the Trading Partner. The external transaction subtype is entered using the Define Transactions window and is used in the Define Trading Partners window. |
| i_document_ number | Unique identifier for the business document received from the Trading Partner. This field is not used by XML Gateway but is available for the Oracle E-Business Suite receiving application module. |
| i_party_id | Not Used. |
| i_party_site_id | The source Trading Partner Location Code entered using the Define Trading Partners window if no data is found in the Destination Trading Partner Location Code. |
| i_protocol_type | The transmission method entered using the Define Trading Partners window. |
| i_protocol_address | The address/URL associated with the transmission method. It is entered using the Define Trading Partners window. |
| i_username | The username entered using the Define Trading Partners window. |
| i_password | The password associated with the username. Entered using the Define Trading Partners window. |
| i_attribute1 | User-defined field to pass data. |
| i_attribute2 | User-defined field to pass data. |
| i_attribute3 | Data in this field will trigger the creation of another XML message that is sent to the Trading Partner identified in the Destination Trading Partner Location Code field entered using the Define Trading Partners window. |
| i_attribute4 | User-defined field to pass data. |
| i_attribute5 | User-defined field to pass data. |
| retcode | Return code for the procedure |
| retmsg | Return message for the procedure |
procedure getOAGLOGICALID
return varchar2;
Gets the sender's logical ID defined for the ECX_OAG_LOGICALID system profile.
The value defined in the ECX_OAG_LOGICALID system profile is retrieved by the ECX_OAG_CONTROLAREA_TP_V view. The value is used to map to the OAG CNTROLAREA, LOGICALID element.
This function is not required if you are using the ECX_OAG_CONTROLAREA_TP_V view.
None
None
procedure getDoclogDetails
(i_msgid in raw, i_message_type out varchar2, i_message_standard out varchar2, i_transaction_type out varchar2, i_transaction_subtype out varchar2, i_document_number out varchar2, i_party_id out varchar2, i_party_site_id out varchar2, i_protocol_type out varchar2, i_protocol_address out varchar2, i_username out varchar2, i_password out varchar2, i_attribute1 out varchar2, i_attribute2 out varchar2, i_attribute3 out varchar2, i_attribute4 out varchar2, i_attribute5 out varchar2, i_logfile out varchar2, i_internal_control_number out number, i_status out varchar2, i_time_stamp out date, i_direction out varchar2, o_retcode out pls_integer, o_retmsg out varchar2);
Gets information about transactions processed by XML Gateway. An entry is written to the ECX_DOCLOGS table for each outbound message created by XML Gateway and each inbound message processed by XML Gateway.
Error recovery is performed using the stored copy of a message. For details regarding error recovery, see XML Gateway Error Processing Item Type.
| Variable | Description |
|---|---|
| i_msgid | Message identifier provided by the XML Gateway execution engine for each message processed. |
| Variable | Description |
|---|---|
| i_message_type | The message type is defaulted to "XML" |
| i_message_standard | The XML standard associated with the business document as entered using the Define XML Standards window and used by the Define Trading Partners window. |
| i_transaction_type | External transaction type associated with the business document as entered using the Define Transactions window and used by the Define Trading Partners window. |
| i_transaction_ subtype | External transaction subtype associated with the business document as entered using the Define Transactions window and used by the Define Trading Partners window. |
| i_document_ number | Unique identifier from the Oracle E-Business Suite for the outbound business document. Unique identifier for an inbound business document received from the Trading Partner. |
| i_party_id | Trading Partner identifier associated with the business document as entered using the Define Trading Partners window. |
| i_party_site_id | Trading Partner site identifier associated with the business document as entered using the Define Trading Partners window. |
| i_protocol_type | Communication method associated with the business document as entered using the Define Trading Partners window. |
| i_protocol_address | Address/URL associated with the communication method as entered using the Define Trading Partners window. |
| i_username | The username associated with the Trading Partner as entered using the Define Trading Partners window. |
| i_password | The password associated with the username as entered using the Define Trading Partners window. |
| i_attribute1 | User-defined field to pass data. |
| i_attribute2 | User-defined field to pass data. |
| i_attribute3 | The Destination Trading Partner Location Code as entered using the Define Trading Partners window. |
| i_attribute4 | User-defined field to pass data. |
| i_attribute5 | User-defined field to pass data. |
| i_logfile | Identifies the name of the log file created by the XML Gateway execution engine for the business document processed. The log files are written to the directory identified in the ECX: Log File Path system profile. |
| i_internal_control_number | The unique identifier as defined by the XML Gateway execution engine for each business document processed. |
| i_status | The status of the business document processed. |
| i_time_stamp | Time stamp from the XML Gateway execution engine representing when the outbound business document was created or when the inbound business document was processed. |
| i_direction | Identifies whether the business document was outbound from the Oracle E-Business Suite or inbound into the Oracle E-Business Suite. |
| o_retcode | Return code for the procedure. |
| o_retmsg | Return message for the procedure. |
procedure set_error_exit_program
(i_err_type in pls_integer, i_err_code in pls_integer, i_err_msg in varchar2);
Sets the error code and message so that a notification can be sent to either the Trading Partner contact, the System Administrator (identified in the ECX: System Administrator Email Address system profile), or both.
The Send Error Message action is used for warnings that do not require the process to be terminated. This procedure is used for serious errors where you intend to terminate the process.
For more details regarding the Send Error Message action, see Map Action Editor - Return Error Message: Send Error Message.
| Variable | Description |
|---|---|
| i_err_type | A code to identify the intended recipient of the error notification. The valid values are as follows: 10 = Reporting error, do not send notification 20 = Send notification to the Trading Partner contact containing the error code and message 25 = Send notification to both the Trading Partner and System Administrator contact containing the error code and message 30 = Send notification to the System Administrator contact containing the error code and message |
| i_err_code | Code for error detected |
| i_err_msg | Message string for errors detected. Multiple messages may be concatenated into this variable and sent in the notification. |
None.
procedure register_attachment
(i_entity_name in varchar2, i_pk1_value in varchar2, i_pk2_value in varchar2, i_pk3_value in varchar2, i_pk4_value in varchar2, i_pk5_value in varchar2, i_file_id in number, i_data_type in number, x_cid out varchar2);
Called by message maps for outbound documents to register the correlation of attachment(s) to an outbound business document.
This API assumes the attachment has been defined in the Oracle Foundation module. The required input to the API are values returned when the attachment was deposited in FND.
| Variable | Description |
|---|---|
| i_entity_name | Entity name from the FND_LOBS table used to compose the x_cid value. |
| i_pk1_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk2_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk3_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk4_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk5_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_file_id | File identifier from the FND_LOBS table used to determine the file_name value from the FND_LOBS table. The file_name is used to compose the x_cid value. |
| i_data_type | Identifies the data type of the attachment file. The valid value is BLOB. Currently, only the ecx_attachment.embedded_lob_data_type value is supported (only |
| Variable | Description |
|---|---|
| x_cid | A unique identifier for the attachment within a given outbound document provided by FND when the attachment was deposited. The value is constructed using the formulate_content_id API by concatenating i_entity_name, i_pk1_value, i_pk2_value, i_pk3_value, i_pk4_value, i_pk5_value (which are provided as input to this API), and then adding the file_name (based on the file_id) from the FND_LOBS table. |
Note: register_attachment is an overloaded API. The second signature is documented below. The difference between the two APIs is that the first uses the key values from the FND_LOBS table to uniquely identify the attachment; and the second uses a user-defined identifier.
procedure register_attachment
(i_cid in varchar2, i_file_id in number, i_data_type in number);
| Variable | Description |
|---|---|
| i_cid | A unique identifier for the attachment provided by the user when the attachment was defined in FND. |
| i_file_id | File identifier from the FND_LOBS table. |
| i_data_type | Identifies the data type of the attachment file. The valid value is BLOB. The list of valid values is maintained in ecx_attachment.embedded_lob_data_type API. |
None.
procedure retrieve_attachment
(i_msgid in raw, x_cid in varchar2, x_file_name out varchar2, x_file_content_type out varchar2, x_file_data out nocopy blob, x_ora_charset out varchar2, x_file_format out varchar2);
Called by message maps for inbound documents to retrieve an attachment deposited by Oracle Transport Agent (OTA) when the inbound document was received.
Not all attachments deposited by OTA are of interest to the receiving application. Only the attachments identified by the i_msgid and x_cid parameters are retrieved using this API.
| Variable | Description |
|---|---|
| i_msgid | The message ID associated with the attachment deposited by OTA into the FND repository. |
| x_cid | A unique identifier for the attachment provided in the inbound XML document. With OAG, this would be provided in the ATTCHREF data type, FILENAME element. The exact location of the unique identifier in the XML document will vary by standard. |
| Variable | Description |
|---|---|
| x_file_name | Name of the attachment file from the FND_LOBS table. |
| x_file_content_type | Content type specified during the attachment uploading process. Information from the FND_LOBS table. |
| x_file_data | The uploaded attachment stored as a binary LOB from the FND_LOBS table. |
| x_ora_charset | Oracle character set from the FND_LOBS table. |
| x_file_format | File format ("text" or "binary") from the FND_LOBS table. |
procedure reconfig_attachment
(i_msgid in raw, i_cid in varchar2, i_entity_name in varchar2, i_pk1_value in varchar2, i_pk2_value in varchar2, i_pk3_value in varchar2, i_pk4_value in varchar2, i_pk5_value in varchar2, i_program_app_id in number, i_program_id in number, i_request_id in number, x_document_id out number);
Called by message maps for inbound documents to reset FND attributes for a previously retrieved attachment deposited by OTA when the inbound document was received.
Not all attachments retrieved using the retrieve_attachment API must be reconfigured. Use the reconfig_attachment API only if you want to update the FND attributes. Use the standard Oracle Foundation module to create a new copy of the attachment if necessary.
| Variable | Description |
|---|---|
| i_msgid | The message ID associated with the attachment deposited by OTA into the FND repository. |
| i_cid | A unique identifier for the attachment. This is the same value provided to the retrieve_attachment API that was based on the value in the inbound XML document. |
| i_entity_name | Entity name from the FND_ATTACHED_DOCUMENTS table. |
| i_pk1_value | Key value from the FND_ATTACHED_DOCUMENTS table. |
| i_pk2_value | Key value from the FND_ATTACHED_DOCUMENTS table. |
| i_pk3_value | Key value from the FND_ATTACHED_DOCUMENTS table. |
| i_pk4_value | Key value from the FND_ATTACHED_DOCUMENTS table. |
| i_pk5_value | Key value from the FND_ATTACHED_DOCUMENTS table. |
| i_program_app_id | Standard extended who column from FND_ATTACHED_DOCUMENTS table. |
| i_program_id | Standard extended who column from FND_ATTACHED_DOCUMENTS table. |
| i_request_id | Standard extended who column from FND_ATTACHED_DOCUMENTS table. |
| Variable | Description |
|---|---|
| x_document_id | Unique identifier for the reconfigured attachment. |
procedure formulate_content_id
(i_file_id in number, i_entity_name in varchar2, i_pk1_value in varchar2, i_pk2_value in varchar2, i_pk3_value in varchar2, i_pk4_value in varchar2, i_pk5_value in varchar2, x_cid out varchar2);
Called by register_attachment API to determine the unique CID for an attachment deposited into FND. This API is exposed for use outside of the register_attachment API context.
| Variable | Description |
|---|---|
| i_file_id | File identifier from the FND_LOBS table used to determine the file_name value from the FND_LOBS table. The file_name is used to compose the x_cid value. |
| i_entity_name | Entity name from the FND_LOBS table used to compose the x_cid value. |
| i_pk1_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk2_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk3_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk4_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| i_pk5_value | Key value from the FND_LOBS table used to compose the x_cid value. |
| Variable | Description |
|---|---|
| x_cid | A unique identifier for the attachment based on the concatenation of i_entity_name, i_pk1_value, i_pk2_value, i_pk3_value, i_pk4_value, i_pk5_value (which are provided as input to this API), and then adding the file_name value (based n the file_id) from the FND_LOBS table. |
procedure convert_to_cxml_date
(p_ora_date in date, x_cxml_date out varchar2);
Converts Oracle date to cXML date in ISO 8601 format. The time element is not included.
If both date and time are required, use convert_to_cxml_datetime procedure.
| Variable | Description |
|---|---|
| p_ora_date | The Oracle date. |
| Variable | Description |
|---|---|
| x_cxml_date | The cXML date converted from the Oracle date. |
procedure convert_to_cxml_datetime
(p_ora_date in date, x_cxml_date out varchar2);
Converts Oracle date and time to cXML date and time in ISO 8601 format.
If only the date is required, use convert_to_cxml_date procedure.
Important: Be sure to set the profile option ECX: Server Time Zone. If you do not set this profile option, the time zone defaults to GMT. See Define System Profile Options.
| Variable | Description |
|---|---|
| p_ora_date | The Oracle date and time. |
| Variable | Description |
|---|---|
| x_cxml_date | The cXML date and time converted from the Oracle date and time. |
procedure convert_from_cxml_datetime
(p_cxml date in varchar2, x_ora_date out date);
Converts cXML date and time to Oracle date and time.
Important: Be sure to set the profile option ECX: Server Time Zone. If you do not set this profile option, the time zone defaults to GMT. See Define System Profile Options.
| Variable | Description |
|---|---|
| p_cxml_date | The cXML date and time on an incoming document. |
| Variable | Description |
|---|---|
| x_ora_date | The Oracle date and time converted from the cXML date and time. |
Oracle XML Gateway allows you to manage trading partner information through the use of the Trading Partner Setup API (ECX_TP_SETUP_API). This API contains the following procedures allowing you to create, update, retrieve, and delete trading partner details for one or more party sites in a party.
Note: You can also manage trading partner information through the Define Trading Partner Setup Form in Oracle XML Gateway. See: Using the Define Trading Partner Setup Form.
Additional record types included in these procedures are explained in this appendix. See: Record Types.
procedure CREATE_TRADING_PARTNER( x_return_status out number, x_msg out varchar2, x_return_status_list out t_return_status_type, p_party_type in varchar2, p_org_id in number, p_org_name in varchar2, p_party_id in number, p_party_name in varchar2, p_company_admin_email v p_all_sites in varchar2 := 'N', p_override_sites in varchar2 := 'N', p_party_site_details in t_party_site_type, p_tp_details in r_tp_details_record );
Creates trading partner setup corresponding to the party_site_id passed or for all the sites in the party depending on the parameter passed.
First all the input data will be validated. If it is not valid, a warning message will be returned and no insertion will take place until the complete data passed is valid. For the creation of trading partner setup, at least one site should exist in the party.
| Variable | Description |
|---|---|
| p_party_type | Only one party type can be passed as an input data during the trading partner creation. Party type can be:
|
| p_org_id | Organization ID |
| p_org_name | Organization name Preference will be given to p_org_id passed as an in parameter. |
| p_party_id | Party ID |
| p_party_name | Party name Preference will be given to p_party_id passed as an in parameter. |
| p_company_admin_email | Email address |
| p_all_sites | It is a flag (Y, N) and the default value is set to N.If it is Y, trading partner setup will be created for all sites. Otherwise, it will be created for the specified site(s). |
| p_override_sites | It is a flag (Y, N) and the default value is set to N.If it is Y, the existing trading partner setup will be overwritten. |
| p_party_site_details | This list contains the party site details, transaction, code conversion, and user details. The information can be passed through the p_party_site_type record type.See: p_party_site_type. |
| p_tp_detail | This list contains the transaction, code conversion, and user details. The information can be passed through the r_tp_details_record record type.See: r_tp_details_record. |
| Variable | Description |
|---|---|
| x_return_status | Return status of program execution |
| x_msg | Error message of program execution |
| x_return_status_list | List of arrays of all the Ids created for the trading partner setup. This information can be passed through the t_return_status_type record type.See: t_return_status_type. |
procedure DELETE_TRADING_PARTNER( x_return_status out pls_integer, x_msg out varchar2, x_return_status_list out t_return_status_type, p_party_type in varchar2, p_org_id in number, p_org_name in varchar2, p_party_id in number, p_party_name in varchar2, p_all_sites in varchar2 := 'N', p_party_site_details in t_party_site_type, );
Modifies the details part of the trading partner setup corresponding to the party_site_id passed or for all the sites in the party depending on the parameter passed.
First all the input data will be validated. If it is not valid, a warning message will be returned and no modification will take place until the complete data passed is valid. For the modification of trading partner setup, at least one site should exist in the party.
| Variable | Description |
|---|---|
| p_party_type | While trying to delete a trading partner, only one party type can be passed as an input data. Party type can be:
|
| p_org_id | Organization ID |
| p_org_name | Organization name Preference will be given to p_org_id passed as an in parameter. |
| p_party_id | Party ID |
| p_party_name | Party name Preference will be given to p_party_id passed as an in parameter. |
| p_all_sites | It is a flag (Y, N) and the default value is set to N.If it is Y, trading partner setup will be created for all sites. Otherwise, it will be created for the specified site(s). |
| p_party_site_details | This list contains the party site details, transaction, code conversion, and user details. The information can be passed through the p_party_site_type record type.See: p_party_site_type. |
| Variable | Description |
|---|---|
| x_return_status | Return status of program execution |
| x_msg | Error message of program execution |
| x_return_status_list | List of arrays of all the Ids created for the trading partner setup. This information can be passed through the t_return_status_type record type.See: t_return_status_type. |
procedure UPDATE_TRADING_PARTNER( x_return_status out number, x_msg out varchar2, x_return_status_list out t_return_status_type, p_party_type in varchar2, p_org_id in number, p_org_name in varchar2, p_party_id in number, p_party_name in varchar2, p_party_site_details in t_party_site_type, );
Modifies the details part of the trading partner setup corresponding to the party_site_id passed or for all the sites in the party depending on the parameter passed.
First all the input data will be validated. If it is not valid, a warning message will be returned and no modification will take place until the complete data passed is valid. For the modification of trading partner setup, at least one site should exist in the party.
| Variable | Description |
|---|---|
| p_party_type | Only one party type can be passed as an input data while updating the trading partner information. Party type can be:
|
| p_org_id | Organization ID |
| p_org_name | Organization name Preference will be given to p_org_id passed as an in parameter. |
| p_party_id | Party ID |
| p_party_name | Party name Preference will be given to p_party_id passed as an in parameter. |
| p_party_site_details | This list contains the party site details, transaction, code conversion, and user details. The information can be passed through the p_party_site_type record type.See: p_party_site_type. |
| Variable | Description |
|---|---|
| x_return_status | Return status of program execution |
| x_msg | Error message of program execution |
| x_return_status_list | List of arrays of all the Ids created for the trading partner setup. This information can be passed through the t_return_status_type record type.See: t_return_status_type. |
procedure GET_TRADING_PARTNER( x_return_status out number, x_msg out varchar2, x_return_status_list out t_return_status_type, p_party_type in varchar2, p_org_id in number, p_org_name in varchar2, p_party_id in number, p_party_name in varchar2, p_all_sites in varchar2 := 'N', p_party_site_details in t_party_site_type, );
Retrieves the trading partner setup details corresponding to the party_site_id passed or for all the sites in the party depending on the parameter passed. It returns transaction details, code conversion details, and user details corresponding to the sites provided.
First all the input data will be validated. If it is not valid, a warning message will be returned and no retrieval will take place until the complete data passed is valid. For the retrieval of trading partner setup, at least one site should have the trading partner setup information.
| Variable | Description |
|---|---|
| p_party_type | Only one party type can be returned while retrieving trading partner information. Party type can be:
|
| p_org_id | Organization ID |
| p_org_name | Organization name Preference will be given to p_org_id passed as an in parameter. |
| p_party_id | Party ID |
| p_party_name | Party name Preference will be given to P_PARTY_ID passed as an in parameter. |
| p_all_sites | It is a flag (Y, N) and the default value is set to N.If it is Y, trading partner setup will be created for all sites. Otherwise, it will be created for the specified site(s). |
| p_party_site_details | This list contains the party site details, transaction, code conversion, and user details. The information can be passed through the p_party_site_type record type.See: p_party_site_type. |
| Variable | Description |
|---|---|
| x_return_status | Return status of program execution |
| x_msg | Error message of program execution |
| x_return_status_list | List of arrays of all the Ids created for the trading partner setup. This information can be passed through the t_return_status_type record type.See: t_return_status_type. |
This Trading Partner Setup API is a complex PL/SQL API, and each procedure contained in the API may include the following record types to return or pass the status of trading partner setup, or records of transaction details, code conversion, or user details.
This p_party_site_type record type returns the party site details, transaction details, code conversion, and user details.
party_site_id number, site_address1 varchar2(100), site_address2 varchar2(100), site_address3 varchar2(100), site_address4 varchar2(100), site_city varchar2(100), site_state varchar2(100), site_postal_code varchar2(100), ece_tp_location_code varchar2(100), p_company_admin_email varchar2(30), tp_header_id number, transactions t_transaction_type, -- record type for transaction details code_conversion t_xref_type, -- record type for code conversion tp_user t_tp_user_data_type -- record type for user details
| Variable | Description |
|---|---|
| party_site_id | Party site identifier |
| site_address1 | Site address |
| site_address2 | Site address |
| site_address3 | Site address |
| site_address4 | Site address |
| site_city | City information where the site is located. |
| site_state | State information where the site is located. |
| site_postal_code | Postal code information where the site is located. |
| ece_tp_location_code | Trading partner location code |
| p_company_admin_email | The email address of the administration contact associated with the trading partner |
| tp_header_id | Trading partner header identifier if the trading partner is created successfully. |
| transactions | Transaction details can be passed through the t_transaction_type record type.See: t_transaction_type. |
| code_conversion | Code conversion can be passed through the t_xref_type record type.See: t_xref_type. |
| tp_user | Code conversion can be passed through the t_tp_user_data_type record type.See: t_tp_user_data_type. |
This r_tp_details_record record type returns the transaction and code conversion details.
transactions t_transaction_type, --record type for transaction details code_conversion t_xref_type --record type for code conversion
| Variable | Description |
|---|---|
| transactions | Transaction details can be passed through the t_transaction_type record type.See: t_transaction_type. |
| code_conversion | Code conversion can be passed through the t_xref_type record type.See: t_xref_type. |
This t_return_status_type record type returns the status of trading partner setup, Id information associated with trading partner, party, code conversion, and transaction details, as well as error messages if occur during the process.
org_id, org_name, party_type, party_id number, party_name , company_admin_email , party_site_id , site_address1 , x_return_status , -- Status code x_msg , -- error message /warning message tp_header_id , -- Header id if tp created successfully tp_detail_id , -- transaction details id tp_ref_detail_id -- code conversion details id
| Variable | Description |
|---|---|
| org_id | Organization ID |
| org_name | Organization name |
| party_type | Only one party type can be returned during the trading partner creation. Party type can be:
|
| party_id | Party ID |
| party_name | Party name Preference will be given to p_party_id passed as an in parameter. |
| company_admin_email | Email address |
| party_site_id | Party site identifier |
| site_address1 | Site address |
| x_return_status | Status code |
| x_msg | An error message or a warning message |
| tp_header_id | Trading partner header ID if the trading partner is created successfully. |
| tp_detail_id | Transaction details ID |
| tp_ref_detail_id | Code conversion details ID |
This t_transaction_type record type passes the record for trading partner transaction details.
r_transaction_record -- Transaction details record. transaction_type varchar2(100), transaction_sub_type varchar2(100), standard_code varchar2(30), standard_type varchar2(10), ext_transaction_type varchar2(100), ext_transaction_sub_type varchar2(100), direction varchar2(5), map_code varchar2(100), map_id number, connection varchar2(100), protocol_type varchar2(30), username varchar2(100), password varchar2(100), protocol_address varchar2(200), source_tp_loc_code varchar2(100), dest_tp_loc_code varchar2(100), document_confirmation NUMBER, routing_id NUMBER, update_password varchar2(1), ext_process_id number, transaction_id number, --tp_header_id number, tp_detail_id number, action_type varchar2(20), hub_user_id number, hub_id number
| Variable | Description |
|---|---|
| transaction_type | Transaction type is the standard product short code for the base Oracle E-Business Suite application. These values are defined in the Define Transactions form. |
| transaction_sub_type | Transaction subtype is a code for a particular transaction within the application specified by the Transaction Type. |
| standard_code | The standard code (for example, OAG) associated with the transaction passed to the procedure. |
| standard_type | The XML standard type associated with the transaction passed to the procedure. |
| ext_transaction_type | External transaction type associated with the internal transaction type passed to the procedure. |
| ext_transaction_sub_type | External transaction subtype associated with the internal transaction subtype passed to the procedure. |
| direction | The direction associated with the internal transaction type passed to the procedure. The value IN identifies an inbound message, and the value OUT identifies an outbound message. |
| map_code | Message map associated with the trading partner and transaction passed to the procedure. |
| map_id | Message map ID |
| connection | The connection method associated with the XML message sent to a trading partner. |
| protocol_type | The application protocol passed to the procedure to transmit the document. |
| username | Username passed to the procedure to be authenticated on the receiving server. |
| password | The password associated with the username passed for the receiving server. |
| protocol_address | The complete URL (including service/servlet) passed to the procedure to transmit the XML document. |
| source_tp_loc_code | The code passed to the XML Gateway envelope to identify the source trading partner of the message. |
| dest_tp_loc_code | The code passed to the procedure for the final recipient of the XML message. |
| document_confirmation | Document confirmation number passed to the procedure for the confirmation level if the associated trading partner would like to send or receive a confirmation. |
| routing_id | Routing ID passed to the procedure to identify another trading partner to whom messages will be routed. |
| update_password | The updated password passed to the procedure. |
| ext_process_id | External process ID passed to the procedure. |
| transaction_id | Transaction ID passed to the procedure. |
| tp_detail_id | Transaction details ID passed to the procedure. |
| action_type | The action type passed to the procedure. |
| hub_user_id | The user ID passed to the procedure for the trading partner conducting business via the hub. |
| hub_id | The hub ID passed to the procedure to route documents to and from trading partners. |
This t_xref_type record type passes the record for code conversion details.
r_xref_record -- Code conversion details. p_xref_category_code varchar2(100), p_standard varchar2(100), p_oracle_value varchar2(100), p_from_tp_value varchar2(100), p_to_tp_value varchar2(100), p_standard_type varchar2(100), p_description varchar2(100), p_standard_id number, p_xref_category_id number, p_action varchar2(20) -- INSERT/ UPDATE/ DELETE );
| Variable | Description |
|---|---|
| p_xref_category_code | The category code passed to the procedure 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. |
| p_standard | The standard code passed to associate with the message. |
| p_oracle_value | This is the code defined in Oracle E-Business Suite. |
| p_from_tp_value | This is the code in the message that represents data from the trading partner's perspective. Its value is passed in an inbound source transaction. |
| p_to_tp_value | This is the code passed for an outbound message. It represents data that the trading partner is expecting to receive. |
| p_standard_type | Standard type that is passed to this procedure. |
| p_description | Description of the XML standard that is passed to this procedure. |
| p_standard_id | Standard ID |
| p_xref_category_id | Category ID |
| p_action | INSERT, UPDATE, or DELETE action that is passed for this code conversion. |
This t_tp_user_data_type record type passes the record of user data details.
r_tp_user_data_record -- User data record user_name varchar2(100), valid_user varchar2(100), p_action varchar2(20) -- INSERT/ UPDATE/ DELETE
| Variable | Description |
|---|---|
| user_name | Oracle E-Business Suite user name to be associated with the trading partner. |
| valid_user | The valid user name associated with the trading partner. |
| p_action | INSERT, UPDATE, or DELETE action that is passed for the user data record. |