Thursday, January 10, 2019

Upload an attachment (file,text,url) to ERP cloud transaction


Last updated: January 10, 2019

Use case: Automatically upload an attachment to ERP cloud transaction
Upload a transaction attachment (i.e. a supporting document for a Journal or AP invoice, etc..) to ERP cloud without human intervention. Client uses an external system to gather attachments and need to upload them to Oracle ERP cloud for speeding up month end processing.

Solution: 
The ERP Object Attachment Service supports the automatic upload of attachments to Oracle ERP Cloud.  An attachment can be a file, text, or Internet URL. This is a generic service used to upload attachments to several business transactions. Thus, the parameters to this service vary depending on the transaction context. 

In this document, I will walk through an example to upload an attachment to a journal entity. (note you can upload an attachment to a journal batch or journal header. The journal data entity hierarchy is Journal batch has one or more journal headers, which intern has journal lines.

ERP Object Attachment Service is available since Release 11 of cloud apps

https://[ERPCloudEndpoint]/fscmService/ErpObjectAttachmentService?WSDL

Note: I am not discussing security as part of this document. I will be using soap UI WS security with ‘UsernameToken’ to execute these requests.

Parameter
Description
Entity Name & Category Name
Entity Name of the attachment. This represents the transaction type. Ex: Journal Header, AP_INVOICES_ALL
Navigate to “Setup & Maintenance UI > Set up Tasks > Manage Attachment Entities or Manage Attachment Categories” to get valid entity names.
Allow Duplicate
Option to allow duplicate attachments
Values: Yes or No
Attachment Rows
Multiple attachments are allowed. Each attachment is a repeating entry in the XML.
User Keys: 5 user keys are allowed. A key represents a relevant lookup value to which the attachment is linked to. Ie. In journal entry case, its journal name, batch name, period, flexfield context, period set name and type
Attachment Type: URL, File,  or Text
Title: Title of the document
Content
File -  This attachment type represents base64 encoding for file content being uploaded
URL - This attachment type represents an Internet URL of your document
Text - This attachment type represents actual text such as “Invoice requires further approval”


Response Code
The response code in JSON format
If multiple attachments are uploaded, there will be a single line response with the status of all attachments
References
  • https://blogs.oracle.com/fmw/oracle-erp-cloud-object-attachment-service
  • Oracle Support (Doc ID 2369923.1) - User Keys For Oracle ERP Cloud Object Attachment Service For Entity And Category 
Prerequisites
  1. User account with appropriate permissions
    1. For this example, the user has ‘Financial Integration Specialist’ role and appropriate data access to the ledgers to which the journals are posted.
    2. Also note that I am using a standalone account on the ERP instance rather than an SSO account. That’s a topic for another post.
  2. Access to run queries in ERP cloud (typically BI administrator role)
    1. This is necessary to determine the required parameters
Example / Step-by-step instructions (Journal entry Header attachment upload)

  • Setup the SOAP UI project with the ErpObjectAttachmentService and setup the security parameters as shown in screenshot below.





  • Now let’s figure out the required parameters for uploading and linking an attachment to journal entry

    Open BIP > new data model > run the following query
     select * from FUN_ERP_ATTACHMENT_CONTEXTS where ERP_FAMILY_CODE='FIN'

          if you download and parse the xml, the output will look like this.
<G_1>
    <CREATION_DATE>2017-02-18T10:07:29.125+00:00</CREATION_DATE>
    <CREATED_BY>SEED_DATA_FROM_APPLICATION</CREATED_BY>
    <LAST_UPDATE_DATE>2017-02-18T10:07:29.128+00:00</LAST_UPDATE_DATE>
    <LAST_UPDATED_BY>SEED_DATA_FROM_APPLICATION</LAST_UPDATED_BY>
    <LAST_UPDATE_LOGIN>-1</LAST_UPDATE_LOGIN>
    <ERP_ATTACHMENT_ID>300000001082304</ERP_ATTACHMENT_ID>
    <ERP_FAMILY_CODE>FIN</ERP_FAMILY_CODE>
    <APPLICATION_ID>435</APPLICATION_ID>
    <ENTITY_CODE>GL_JE_BATCHES</ENTITY_CODE>
    <ENTITY_NAME>Journal Batch</ENTITY_NAME>
    <USER_KEY_CONTEXT1>SELECT JE_BATCH_ID FROM GL_JE_BATCHES WHERE NAME = :1 AND DEFAULT_PERIOD_NAME = :2 AND CHART_OF_ACCOUNTS_ID = (Select STRUCTURE_INSTANCE_NUMBER from FND_KF_STR_INSTANCES_VL WHERE APPLICATION_ID = 101 AND KEY_FLEXFIELD_CODE = 'GL#' AND NAME=:3) AND PERIOD_SET_NAME  = (SELECT PERIOD_SET_NAME from GL_CALENDARS where USER_PERIOD_SET_NAME = :4) AND ACCOUNTED_PERIOD_TYPE  = (SELECT PERIOD_TYPE from GL_CALENDARS where USER_PERIOD_SET_NAME = :4)</USER_KEY_CONTEXT1>
    <OBJECT_ACCESS_TASKFLOW>/WEB-INF/oracle/apps/financials/generalLedger/journals/journalEntries/ui/flow/CreateJournalBatchFlow.xml#CreateJournalBatchFlow</OBJECT_ACCESS_TASKFLOW>
    <OBJECT_VERSION_NUMBER>1</OBJECT_VERSION_NUMBER>
    <ENABLED_FLAG>Y</ENABLED_FLAG>
    <SEED_DATA_SOURCE>SDFFILE</SEED_DATA_SOURCE>
</G_1>
<G_1>
    <CREATION_DATE>2017-02-18T10:07:29.135+00:00</CREATION_DATE>
    <CREATED_BY>SEED_DATA_FROM_APPLICATION</CREATED_BY>
    <LAST_UPDATE_DATE>2017-02-18T10:07:29.137+00:00</LAST_UPDATE_DATE>
    <LAST_UPDATED_BY>SEED_DATA_FROM_APPLICATION</LAST_UPDATED_BY>
    <LAST_UPDATE_LOGIN>-1</LAST_UPDATE_LOGIN>
    <ERP_ATTACHMENT_ID>300000001082305</ERP_ATTACHMENT_ID>
    <ERP_FAMILY_CODE>FIN</ERP_FAMILY_CODE>
    <APPLICATION_ID>435</APPLICATION_ID>
    <ENTITY_CODE>GL_JE_HEADERS</ENTITY_CODE>
    <ENTITY_NAME>Journal Header</ENTITY_NAME>
    <USER_KEY_CONTEXT1>SELECT JE_HEADER_ID FROM GL_JE_HEADERS WHERE NAME = :1 AND JE_BATCH_ID = ( SELECT JE_BATCH_ID FROM GL_JE_BATCHES WHERE NAME = :2 AND DEFAULT_PERIOD_NAME = :3 AND CHART_OF_ACCOUNTS_ID = (Select STRUCTURE_INSTANCE_NUMBER from FND_KF_STR_INSTANCES_VL WHERE APPLICATION_ID = 101 AND KEY_FLEXFIELD_CODE = 'GL#' AND NAME=:4) AND PERIOD_SET_NAME  = (SELECT PERIOD_SET_NAME from GL_CALENDARS where USER_PERIOD_SET_NAME = :5) AND ACCOUNTED_PERIOD_TYPE  = (SELECT PERIOD_TYPE from GL_CALENDARS where USER_PERIOD_SET_NAME = :5))</USER_KEY_CONTEXT1>
    <OBJECT_ACCESS_TASKFLOW>/WEB-INF/oracle/apps/financials/generalLedger/journals/journalEntries/ui/flow/CreateJournalHeaderFlow.xml#CreateJournalHeaderFlow</OBJECT_ACCESS_TASKFLOW>
    <OBJECT_VERSION_NUMBER>1</OBJECT_VERSION_NUMBER>
    <ENABLED_FLAG>Y</ENABLED_FLAG>
    <SEED_DATA_SOURCE>SDFFILE</SEED_DATA_SOURCE>
</G_1

You identify the transaction entity by the ‘ENTITY_NAME’. In this case its ‘Journal Header’ (not journal batch. I am linking the document to a specific journal).
The 5 parameters required for the webservice are marked with :1, :2, :3, :4, :5


SELECT JE_HEADER_ID
    FROM GL_JE_HEADERS
    WHERE NAME = :1
    AND JE_BATCH_ID =
        (
        SELECT JE_BATCH_ID
        FROM GL_JE_BATCHES
        WHERE NAME = :2
        AND DEFAULT_PERIOD_NAME = :3
        AND CHART_OF_ACCOUNTS_ID =
            (
            Select STRUCTURE_INSTANCE_NUMBER
            from FND_KF_STR_INSTANCES_VL
            WHERE APPLICATION_ID = 101
            AND KEY_FLEXFIELD_CODE = 'GL#'
            AND NAME=:4
            )
        AND PERIOD_SET_NAME  =
            (
            SELECT PERIOD_SET_NAME
            from GL_CALENDARS
            where USER_PERIOD_SET_NAME = :5
            )
        AND ACCOUNTED_PERIOD_TYPE  =
            (
            SELECT PERIOD_TYPE
            from GL_CALENDARS
            where USER_PERIOD_SET_NAME = :5
            )
        )

value1: GL_JE_HEADERS.NAME - User entered journal header name.
value2: GL_JE_BATCHES.NAME - User entered journal batch name.
value3: GL_JE_BATCHES.DEFAULT_PERIOD_NAME - Default accounting period for journal batch.
value4: FND_KF_STR_INSTANCES_VL.NAME - flex field context)
value5: GL_CALENDARS.USER_PERIOD_SET_NAME - Period of time, extending from one date to another, defined by a party to quantify the fiscal position of the business. It can be divided into accounting periods.

  • Lets pick an existing journal batch / entry or create one. In this case I will create one for demo purposes.


Note there are some functional configurations that impact the parameter values. I ran the sub queries to determine the exact values of the parameters via trial and error (or ask functional team 😊). If the following query successfully returns the journal header, the attachment upload and linkage will be successful.

SELECT JE_HEADER_ID
FROM GL_JE_HEADERS
WHERE NAME = 'TestJournalHeader'
AND JE_BATCH_ID =
    (
    SELECT JE_BATCH_ID
    FROM GL_JE_BATCHES
    WHERE NAME = 'TestJournalBatch’
    AND DEFAULT_PERIOD_NAME = '13_Dec-18'
    AND CHART_OF_ACCOUNTS_ID =
        (
        Select STRUCTURE_INSTANCE_NUMBER
        from FND_KF_STR_INSTANCES_VL
        WHERE APPLICATION_ID = 101
        AND KEY_FLEXFIELD_CODE = 'GL#'
        AND NAME='******' ç config-based value
        )
    AND PERIOD_SET_NAME  =
        (
        SELECT PERIOD_SET_NAME
        from GL_CALENDARS
        where USER_PERIOD_SET_NAME = '******' ç config-based value
        )
    AND ACCOUNTED_PERIOD_TYPE  =
        (
        SELECT PERIOD_TYPE
        from GL_CALENDARS
        where USER_PERIOD_SET_NAME = '******' ç config-based value
        )
    )

  • Generate the soap request and run.
<soapenv:Envelope xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
  <soapenv:Header xmlns:wsa="http://www.w3.org/2005/08/addressing">
   <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <wsse:UsernameToken wsu:Id="UsernameToken-D885DCE15E67044129154714955949826">
     <wsse:Username>*****</wsse:Username>
     <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">*****</wsse:Password>
     <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">cSesHoap6XZwdWwOPPqbaA==</wsse:Nonce>
     <wsu:Created>2019-01-10T19:45:59.498Z</wsu:Created>
    </wsse:UsernameToken>
    <wsu:Timestamp wsu:Id="TS-D885DCE15E67044129154714955949825">
     <wsu:Created>2019-01-10T19:45:59.498Z</wsu:Created>
     <wsu:Expires>2019-01-10T22:32:39.498Z</wsu:Expires>
    </wsu:Timestamp>
   </wsse:Security>
  </soapenv:Header>
  <soapenv:Body>
   <typ:uploadAttachment>
    <typ:entityName>Journal Header</typ:entityName>
    <typ:categoryName>GL_JE_HEADERS</typ:categoryName>
    <typ:allowDuplicate>yes</typ:allowDuplicate>
    <typ:attachmentRows>
     <erp:UserKeyA>TestJournalHeader</erp:UserKeyA>
     <erp:UserKeyB>TestJournalBatch</erp:UserKeyB>
     <erp:UserKeyC>13_Dec-18</erp:UserKeyC>
     <erp:UserKeyD>*****</erp:UserKeyD>
     <erp:UserKeyE>*****</erp:UserKeyE>
     <erp:AttachmentType>FILE</erp:AttachmentType>
     <erp:Title>JournalAttachment.txt</erp:Title>
<erp:Content>VGhpcyBpcyBhIHNhbXBsZSBhdHRhY2htZW50IGZpbGUhDQpJZiB5b3UgZG93bmxvYWRlZCB0aGlzIGZyb20gRVJQIGNsb3VkIGNvbnNvbGUsIHlvdSBoYXZlIHN1Y2Nlc3NmdWxseSBjb21wbGV0ZWQgdGhpcyB0dXRvcmlhbC4=</erp:Content>
    </typ:attachmentRows>
   </typ:uploadAttachment>
  </soapenv:Body>
 </soapenv:Envelope>

 <env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">
  <env:Header>
   <wsa:Action>http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService//ErpObjectAttachmentService/uploadAttachmentResponse</wsa:Action>
   <wsa:MessageID>urn:uuid:7d11d866-401d-46fd-9c43-f5c624e81be5</wsa:MessageID>
   <wsse:Security env:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
    <wsu:Timestamp wsu:Id="Timestamp-yYC96LrAqM9PWRu6ZhhvhQ22" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
     <wsu:Created>2019-01-10T19:47:26Z</wsu:Created>
     <wsu:Expires>2019-01-14T07:07:26Z</wsu:Expires>
    </wsu:Timestamp>
   </wsse:Security>
  </env:Header>
  <env:Body>
   <ns0:uploadAttachmentResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
    <result xmlns="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">"Attachment1":"SUCCEEDED"</result>
   </ns0:uploadAttachmentResponse>
  </env:Body>
 </env:Envelope>

  • Verify











Posted by at 11:23 AM 3 comments
Subscribe to: Posts (Atom)