Oracle Cloud ERP – ErpObjectAttachmentService

ErpObjectAttachmentService is the generic attachment service for Purchas Orders, AP Invoices – etc . Few of the modules has their own attachment services, but few of them like PO, AP Inv don’t have their attachment services. In those cases – ErpObjectAttachmentService is the available service to be used.

--Sample request payload
<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>
      <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">
         <wsu:Timestamp wsu:Id="TS-25A23F3340EB6EF050160232006218824">
            <wsu:Created>2020-10-10T08:54:22.188Z</wsu:Created>
            <wsu:Expires>2020-10-11T18:34:33.188Z</wsu:Expires>
         </wsu:Timestamp>
         <wsse:UsernameToken wsu:Id="UsernameToken-25A23F3340EB6EF050160232005881123">
            <wsse:Username>casey.brown</wsse:Username>
            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">ymh75375</wsse:Password>
            <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">PaS6mzGfsoqtTU3bl0Bp4A==</wsse:Nonce>
            <wsu:Created>2020-10-10T08:54:18.811Z</wsu:Created>
         </wsse:UsernameToken>
      </wsse:Security>
   </soapenv:Header>
   <soapenv:Body>
      <typ:uploadAttachment>
         <typ:entityName>PO_HEADERS</typ:entityName>
         <typ:categoryName>MISC</typ:categoryName>
         <typ:allowDuplicate>yes</typ:allowDuplicate>
         <!--Zero or more repetitions:-->
         <typ:attachmentRows>
            <!--Optional:-->
            <erp:UserKeyA>164018</erp:UserKeyA>
            <!--Optional:-->
            <erp:UserKeyB>US1 Business Unit</erp:UserKeyB>
            <!--Optional:-->
            <erp:UserKeyC>#NULL</erp:UserKeyC>
            <!--Optional:-->
            <erp:UserKeyD>#NULL</erp:UserKeyD>
            <!--Optional:-->
            <erp:UserKeyE>#NULL</erp:UserKeyE>
            <!--Optional:-->
            <erp:AttachmentType>URL</erp:AttachmentType>
            <!--Optional:-->
            <erp:Title>Sample URL</erp:Title>
            <!--Optional:-->
            <erp:Content>http://www.google.com</erp:Content>
            <erp:Description>URL Link</erp:Description>
         </typ:attachmentRows>
      </typ:uploadAttachment>
   </soapenv:Body>
</soapenv:Envelope>

--Sample Response
<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:256c3094-f16b-4f56-9d0f-2ca02ebb7f9f</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-1h01WIKCd8FsTNEkKgEFYA22" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
            <wsu:Created>2020-10-10T08:54:23Z</wsu:Created>
            <wsu:Expires>2020-10-13T20:14:23Z</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":"SUCCESS"</result>
      </ns0:uploadAttachmentResponse>
   </env:Body>
</env:Envelope>

---Important Queries
--Query to check the user context keys to be provided in the ERPObjectAttachment Payload
select * from FUN_ERP_ATTACHMENT_CONTEXTS 
where ENTITY_CODE like 'PO%' ;

--Sample PO Header Query for flag values
select hou.name, po_header_id, pha.creation_date, pha.segment1, pha.DOCUMENT_CREATION_METHOD , DOCUMENT_STATUS ,
pha.ACCEPTANCE_REQUIRED_FLAG ,pha.SIGNATURE_REQUIRED_FLAG,
 prc_bu_id  from po_headers_all  pha , HR_ORGANIZATION_UNITS_F_TL hou
where 1=1
and hou.organization_id = pha.prc_bu_id
and hou.language='US'
 order by pha.creation_date desc  

--Sample PO Attachment Details query for header level

SELECT DISTINCT
    attch.pk1_value,
    attch.pk2_value,
    attch.pk3_value,
    attch.pk4_value,
    attch.attached_document_id,
    attch.entity_name,
    attch.creation_date,
    tl.dm_document_id,
    tl.file_name,
    tl.title,
    attch.category_name
FROM
    fnd_attached_documents  attch,
    fnd_documents_tl        tl ,
    po_headers_all ph
    WHERE attch.pk1_value =ph.po_header_id
     AND attch.document_id = tl.document_id
     AND attch.entity_name  = 'PO_HEADERS'