1. Using Receivables Credit to Cash
  2. How You Add User-Defined Attributes to OAGIS 10.1 XML Receivables Transactions

How You Add User-Defined Attributes to OAGIS 10.1 XML Receivables Transactions

Add and maintain user-defined attributes at the invoice header, invoice line, and invoice tax line level of OAGIS 10.1 XML Receivables transactions.

OAGIS 10.1 XML provides many standard attributes for including information related to a transaction, such as purchase orders, shipping information, inventory details, and accounting. You can include attributes not mapped in and with no standard tag available in OAGIS 10.1 XML, by adding the attributes to the UserArea extension of OAGIS 10.1.

Prepare User-Defined Attributes

You use public APIs to insert and process user-defined attributes in XML transactions.

The public APIs include:

  • arp_util.insert_ar_extension_attributes: Use the public API arp_util.insert_ar_extension_attributes to create and load user-defined attributes into the AR_EXTENSION_ATTRIBUTES table.

  • arp_util.update_ar_extension_attributes: Use the public API arp_util.update_ar_extension_attributes to update the ATTRIBUTE_NAME and ATTRIBUTE_VALUE of user-defined attributes already in AR_EXTENSION_ATTRIBUTES table.

  • arp_util.update_ready_for_xml_flag: Use the public API arp_util.update_ready_for_xml_flag to set RA_CUSTOMER_TRX_ALL.READY_FOR_XML_DELIVERY to Y in the Load Extension Attributes for XML Transactions BIP report. The signature of this API is available in the delivered data model for the BIP report process.

Use these APIs in the PL/SQL code you write in the anonymous code block of the delivered Load Extension Attributes for XML Transactions BIP data model. The Load Extension Attributes for XML Transactions BIP report process runs as part of the Generate and Transfer XML Transactions process.

The extension attributes for Receivables transactions are computed and loaded into the AR_EXTENSION_ATTRIBUTES table during XML creation and delivery, once you configure the Load Extension Attributes for XML Transactions BIP data model to compute and load extension attributes using the API arp_util.insert_ar_extension_attributes. The API arp_util.update_ready_for_xml_flag sets RA_CUSTOMER_TRX_ALL.READY_FOR_XML_DELIVERY to Y.

Any errors encountered during the loading of extension attributes into the AR_EXTENSION_ATTRIBUTES table appear in the report output of the Load Extension Attributes for XML Transactions BIP process.

To add user-defined attributes to OAGIS 10.1 XML transactions, complete these steps:

  1. Log in to Oracle BI Publisher Enterprise using the xml p server URL for a user with the BI Administrator Role.

  2. Navigate to the Data Models folder in the BI Publisher Enterprise Catalog.

  3. Select the Load Extension Attributes for XML Transactions data model.

  4. In the Diagram tab, click the gear icon and select Edit Data Set.

  5. In the Edit Data Set window, complete the required fields.

  6. In the SQL Query section, write your PL/SQL code to compute and load the extension attributes for your transactions.

  7. Ensure that your PL/SQL code sets RA_CUSTOMER_TRX_ALL.READY_FOR_XML_DELIVERY to Y for transactions after the extension attributes are inserted. The API to set this is arp_util.update_ready_for_xml_flag.

  8. Save your work.

When you have finished defining your extension attributes and completed creating the Receivables transactions to deliver in XML format, run the Generate and Transfer XML Transactions process to deliver these transactions.

If RA_CUSTOMER_TRX_ALL.READY_FOR_XML_DELIVERY isn't set to Y by the Load Extension Attributes for XML Transactions process, the process marks the XML Delivery Status of the transactions as Delivery Failed. Review the report output of the BIP process for errors encountered while inserting extension attributes for transactions that didn't have READY_FOR_XML_DELIVERY set to Y.

API Signatures

The signatures of the APIs are available in the delivered data model for the Load Extension Attributes for XML Transactions BIP report process.

The signature of the API arp_util.insert_ar_extension_attributes to insert user-defined attributes into the AR_EXTENSION_ATTRIBUTES table is as follows:

PROCEDURE insert_ar_extension_attributes(extension_attr_tbl IN ARP_UTIL.EXTENSION_ATTR_TBL_TYPE,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

x_return_status OUT NOCOPY VARCHAR2);

In this case EXTENSION_ATTR_TBL_TYPE is a PL/SQL table of the EXTENSION_ATTRIBUTE_RECORD record type. The definition of the EXTENSION_ATTRIBUTE_RECORD record type is as follows:

TYPE EXTENSION_ATTRIBUTE_RECORD IS RECORD(EXTN_ENTITY_ID AR_EXTENSION_ATTRIBUTES.EXTN_ENTITY_ID%type, ASSOCIATED_EXTN_ENTITY_ID1 AR_EXTENSION_ATTRIBUTES.ASSOCIATED_EXTN_ENTITY_ID1%type, ASSOCIATED_EXTN_ENTITY_ID2 AR_EXTENSION_ATTRIBUTES.ASSOCIATED_EXTN_ENTITY_ID2%type,ENTITY_TYPE_CODE AR_EXTENSION_ATTRIBUTES.ENTITY_TYPE_CODE%type,PROCESS_TYPE_CODE AR_EXTENSION_ATTRIBUTES.PROCESS_TYPE_CODE%type,ATTRIBUTE_NAME AR_EXTENSION_ATTRIBUTES.ATTRIBUTE_NAME%type,ATTRIBUTE_VALUE AR_EXTENSION_ATTRIBUTES.ATTRIBUTE_VALUE%type);

The signature of the API arp_util.update_ar_extension_attributes to update ATTRIBUTE_NAME and ATTRIBUTE_VALUE of the user-defined attributes already in the AR_EXTENSION_ATTRIBUTES table is as follows:

PROCEDURE update_ar_extension_attributes(extension_attr_tbl IN ARP_UTIL.EXT_ATTR_TBL_FR_UPDATE,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

x_return_status OUT NOCOPY VARCHAR2);

In this case EXT_ATTR_TBL_FR_UPDATE is a PL/SQL table of the EXT_ATTR_RECORD_FR_UPDATE record type. The definition of the EXT_ATTR_RECORD_FR_UPDATE record type is as follows:

TYPE EXT_ATTR_RECORD_FR_UPDATE IS RECORD(EXTN_ENTITY_ID AR_EXTENSION_ATTRIBUTES.EXTN_ENTITY_ID%type, ENTITY_TYPE_CODE AR_EXTENSION_ATTRIBUTES.ENTITY_TYPE_CODE%type, PROCESS_TYPE_CODE AR_EXTENSION_ATTRIBUTES.PROCESS_TYPE_CODE%type, ATTRIBUTE_NAME AR_EXTENSION_ATTRIBUTES.ATTRIBUTE_NAME%type, NEW_ATTRIBUTE_NAME AR_EXTENSION_ATTRIBUTES.ATTRIBUTE_NAME%type, NEW_ATTRIBUTE_VALUE AR_EXTENSION_ATTRIBUTES.ATTRIBUTE_VALUE%type);

The signature of the API arp_util.update_ready_for_xml_flag to update READY_FOR_XML_DELIVERY_FLAG in the RA_CUSTOMER_TRX_ALL table is as follows:

PROCEDURE update_ready_for_xml_flag(p_customer_trx_id IN NUMBER,

p_xml_ready_flag IN VARCHAR2,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

x_return_status OUT NOCOPY VARCHAR2);