Updating Requester On Open Purchase Orders(PO) using SOAP Service

There are instances where we need to update the requester in bulk on Open PO. 

One such instance is: 

We do data conversion of Open PO using FBDI, The fbdi only supports requester name. If we have employees with same name in Oracle then there is a chance that wrong employee will get added as requester on the Purchase order in Oracle.

If there are very few such PO's then we can do manually from UI. For Bulk We can use SOAP UI groovy Automation or OIC or any other integration tool.

In any case, either we do manually or from webservice(from SOAP UI or Integration) this will create a change order.

Note: Currently Rest API does not support updating requester on PO Distribution.

Few things we need to keep in mind while updating requester on PO distribution is:

• The communication should not be sent again to supplier- As it is open PO and supplier would have been already communicated for this PO
  
• Bypassing Approval workflow- We might not want to go through the approval process again so we can bypass.
• The new/updated requester will get notified about the change. We can disable this as well fro. BPM but in many cases we need to inform the updated/corrected requester about the change so we can keep this notification enabled.

The SOAP service which we can use for this purpose is:

https://xxxx..fa.us2.oraclecloud.com/fscmService/PurchaseOrderServiceV2?WSDL

The sample payload will be:

<soapenv:Envelope
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:typ="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/"
    xmlns:pur="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/"
    xmlns:draf="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchaseOrderDistribution/"
    xmlns:pjc="http://xmlns.oracle.com/apps/prc/po/commonPo/flex/PJCPoDraftDistribution/"
    xmlns:draf1="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchasingDocumentSchedule/"
    xmlns:draf2="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchasingDocumentLine/"
    xmlns:draf3="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchasingDocumentHeader/">
    <soapenv:Header/>
    <soapenv:Body>
        <typ:changePurchaseOrder>
            <typ:changeOrderEntry>
                <pur:POHeaderId>2357</pur:POHeaderId>
                <pur:OrderNumber>PO-00041950</pur:OrderNumber>
                <pur:SupplierCommunicationMethodCode>NONE</pur:SupplierCommunicationMethodCode>
                <pur:ApprovalAction>Bypass Approval</pur:ApprovalAction>
                <pur:ChangeOrderDescription>Change Order for transferring ownership to correct requester</pur:ChangeOrderDescription>
                <pur:ChangeOrderInitiatingParty>buyer</pur:ChangeOrderInitiatingParty>
                <pur:PurchaseOrderEntryLine>
                    <pur:POLineId>3769</pur:POLineId>
                    <pur:PurchaseOrderEntrySchedule>
                        <pur:LineLocationId>4152</pur:LineLocationId>
                        <pur:PurchaseOrderEntryDistribution>
                            <pur:PODistributionId>3758</pur:PODistributionId>
                            <pur:RequesterId>100000021818804</pur:RequesterId>
                        </pur:PurchaseOrderEntryDistribution>
                    </pur:PurchaseOrderEntrySchedule>
                </pur:PurchaseOrderEntryLine>
                <pur:PurchaseOrderEntryLine>
                    <pur:POLineId>3768</pur:POLineId>
                    <pur:PurchaseOrderEntrySchedule>
                        <pur:LineLocationId>4151</pur:LineLocationId>
                        <pur:PurchaseOrderEntryDistribution>
                            <pur:PODistributionId>3757</pur:PODistributionId>
                            <pur:RequesterId>100000021818804</pur:RequesterId>
                        </pur:PurchaseOrderEntryDistribution>
                    </pur:PurchaseOrderEntrySchedule>
                </pur:PurchaseOrderEntryLine>
            </typ:changeOrderEntry>
        </typ:changePurchaseOrder>
    </soapenv:Body>
</soapenv:Envelope>

If there are many such PO's then we can create a query to generate sample payload and most of the things are static and some are dynamic:

The below query will generate payload for each line and distribution, we can tweak this to have grouped by PO and have single payload string for each PO from data model only or  Else  we can do this grouping in integration and create final payload to be sent to Oracle:

select
  '<soapenv:Envelope
  xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:typ="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/types/"
  xmlns:pur="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseOrderServiceV2/"
  xmlns:draf="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchaseOrderDistribution/"
  xmlns:pjc="http://xmlns.oracle.com/apps/prc/po/commonPo/flex/PJCPoDraftDistribution/"
  xmlns:draf1="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchasingDocumentSchedule/"
  xmlns:draf2="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchasingDocumentLine/"
  xmlns:draf3="http://xmlns.oracle.com/apps/prc/po/editDocument/flex/draftPurchasingDocumentHeader/">
  <soapenv:Header/>
  <soapenv:Body>
    <typ:changePurchaseOrder>
      <typ:changeOrderEntry>
        <pur:POHeaderId>' || h.po_header_id || '</pur:POHeaderId>
 <pur:OrderNumber>' || h.segment1 || '</pur:OrderNumber>
        <pur:SupplierCommunicationMethodCode>NONE</pur:SupplierCommunicationMethodCode>
        <pur:ApprovalAction>Bypass Approval</pur:ApprovalAction>
        <pur:ChangeOrderDescription>Change Order for transferring ownership to correct requester</pur:ChangeOrderDescription>
            <pur:ChangeOrderInitiatingParty>buyer</pur:ChangeOrderInitiatingParty>
        <pur:PurchaseOrderEntryLine>
          <pur:POLineId>' || l.po_line_id || '</pur:POLineId>
          <pur:PurchaseOrderEntrySchedule>
            <pur:LineLocationId>' || pll.LINE_LOCATION_ID || '</pur:LineLocationId>
            <pur:PurchaseOrderEntryDistribution>
              <pur:PODistributionId>' || pda.PO_DISTRIBUTION_ID || '</pur:PODistributionId>
              <pur:RequesterId>' ||(
    CASE WHEN segment1 in ('PO-00027816', 'PO-00035672') THEN (
      select
        PERSON_ID
      from
        per_all_people_f
      where
        PERSON_NUMBER = '107583'
    ) WHEN segment1 in (
      'PO-00035048', 'PO-00040032', 'PO-00041263'
    ) THEN (
      select
        PERSON_ID
      from
        per_all_people_f
      where
        PERSON_NUMBER = '113314'
    ) WHEN segment1 = 'PO-00037736' THEN (
      select
        PERSON_ID
      from
        per_all_people_f
      where
        PERSON_NUMBER = '109001'
    ) WHEN segment1 in ('PO-00037976', 'PO-00041950') THEN (
      select
        PERSON_ID
      from
        per_all_people_f
      where
        PERSON_NUMBER = '105786'
    ) WHEN segment1 in (
      'PO-00032959', 'PO-00038320', 'PO-00038428'
    ) THEN (
      select
        PERSON_ID
      from
        per_all_people_f
      where
        PERSON_NUMBER = '106572'
    ) WHEN segment1 in (
      'PO-00038998', 'PO-00040339', 'PO-00040557'
    ) THEN (
      select
        PERSON_ID
      from
        per_all_people_f
      where
        PERSON_NUMBER = '104073'
    ) WHEN segment1 in (
      'PO-00037632', 'PO-00038764', 'PO-00040111'
    ) THEN (
      select
        PERSON_ID
      from
        per_all_people_f
      where
        PERSON_NUMBER = '101689'
    ) END
  ) || '</pur:RequesterId>
            </pur:PurchaseOrderEntryDistribution>
          </pur:PurchaseOrderEntrySchedule>
        </pur:PurchaseOrderEntryLine>
      </typ:changeOrderEntry>
    </typ:changePurchaseOrder>
  </soapenv:Body>
</soapenv:Envelope>' as payload
from
  po_headers_all h,
  po_lines_all l,
  po_line_locations_all pll,
  po_distributions_all pda
where
  segment1 in (
    'PO-00027816', 'PO-00035048', 'PO-00035293',
    'PO-00035672', 'PO-00037632', 'PO-00037736',
    'PO-00037956', 'PO-00037976', 'PO-00038248',
    'PO-00038320', 'PO-00038428', 'PO-00038715',
  )
  and h.po_header_id = l.po_header_id
  and l.po_line_id = pll.po_line_id
  and pll.LINE_LOCATION_ID = pda.LINE_LOCATION_ID
order by
  segment1

 

Once this is done, we get the payload required to be sent to Oracle.

Below is a sample screenshot of request and response from SOAP UI:

 

NOTE : If we export above query output in CSV then we may get "" string so we need to remove one double quote. Otherwise the payload wont work.