PO Requisition Interface Tables in Fusion

And PO Requisition Sample Payload

In this post, we will see PO Requisition Interface Tables in Fusion. These tables are involved in the import of a PO Requisition and sample rest JSON and SOAP XML payload to create purchase requisitions in oracle procurement cloud.

Important Tables involved

Interface Tables

POR_REQ_HEADERS_INTERFACE_ALL (For Requistions Headers)
select * from POR_REQ_HEADERS_INTERFACE_ALL order by creation_date desc

POR_REQ_LINES_INTERFACE_ALL ( For Requisitions Lines)
select * from por_req_lines_interface_all order by creation_date desc

POR_REQ_DISTS_INTERFACE_ALL ( For Distributions)

Errors Table

POR_REQ_IMPORT_ERRORS ( Import program errors)
select * from POR_REQ_IMPORT_ERRORS order by creation_date desc

Requisitions Base Tables

POR_REQUISITION_HEADERS_ALL
POR_REQUISITION_LINES_ALL
POR_REQ_DISTRIBUTIONS_ALL

PO Requisition Import Program ESS Job

To Import the Requisitions you need to run ESS job.

Navigation

Menu> tools > Scheduled process> Import Requisitions

After that fill parameters like business unit and source and hit submit.

Sample PO Requisition Rest Payload

Creating Purchase Requisition in rest is a 2 step process . First we need to create Requisition Header and then Add Line

Create Header

Rest URL:

https://(domain)/fscmRestApi/resources/11.13.18.05/purchaseRequisitions

Sample Payload to create requisition Header with POST operation
{
    "RequisitioningBUId": 300000046987012,
    "PreparerId": 300000106384837,
    "ExternallyManagedFlag": false,
    "Description": "Front Brakes"
    }

Add Line

https://(domain)/fscmRestApi/resources/11.13.18.05/purchaseRequisitions/(requisitionheaderid)/child/lines

Sample rest payload to add line .POST operation

 { 
   "LineNumber":1,
   "LineTypeId": 1,
   "ItemId": 300000221882966,
    "Quantity":1,
    "UOM": "Ea",
    "ItemDescription": "Front Brakes",
    "Price": 75,
    "CurrencyCode": "USD",
     "DestinationOrganizationId": 300000047274447,
     "DeliverToLocationId": 300000047013190,
    "RequestedDeliveryDate": "2021-04-22",
     "RequesterId": 300000106384837     ,
    "DestinationTypeCode": "INVENTORY",
    "DestinationType": "Inventory",
   "DestinationOrganizationId": 300000047274447,
   "DestinationOrganizationCode": "002",
   "DestinationOrganization": "Atlanta"
         
 }

PO Requisitions WSDL URL

https://(domain)//fscmService/PurchaseRequestService?WSDL

The WSDL will be of above format. you need to replace domain with your cloud domain.

PO Requisitions Sample SOAP payload

!--working payload to create purchase requisitioningBUId

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseRequestService/types/" xmlns:pur="http://xmlns.oracle.com/apps/prc/po/editDocument/purchaseRequestService/">
   <soapenv:Header/>
   <soapenv:Body>
    <typ:createRequisition>
<typ:interfaceSourceCode>DOO</typ:interfaceSourceCode>
<typ:requisitioningBUId>300000046987012</typ:requisitioningBUId>
<typ:groupBy>SUPPLIER</typ:groupBy>
<typ:initiateApprovalAfterRequisitionImport>N</typ:initiateApprovalAfterRequisitionImport>
<typ:errorProcessingLevel>ALL</typ:errorProcessingLevel>
<typ:purchaseRequestPayload>
<pur:ApproverEmail>x.y@oracle.com</pur:ApproverEmail>
<pur:ApproverId>300000047340498</pur:ApproverId>
<pur:Description>Requisition Created by IDC </pur:Description>
<pur:DocumentStatusCode>APPROVED</pur:DocumentStatusCode>
<pur:InterfaceHeaderKey>REQ_IHK_NSK_22</pur:InterfaceHeaderKey>
<pur:PreparerEmail>x.y@oracle.com</pur:PreparerEmail>
<pur:PreparerId>300000047340498</pur:PreparerId>

 <pur:ProcurementBUId>300000046987012</pur:ProcurementBUId>
           
<pur:RequisitioningBUId>300000046987012</pur:RequisitioningBUId>
<pur:ExternallyManagedFlag>False</pur:ExternallyManagedFlag>
<pur:PurchaseRequestInputReqLineInterface>
<pur:CurrencyCode>USD</pur:CurrencyCode>
<pur:CategoryId>300000047312459</pur:CategoryId>
<pur:DeliverToLocationId>300000047013200</pur:DeliverToLocationId>

<pur:DestinationSubinventory/>
<pur:DestinationTypeCode>EXPENSE</pur:DestinationTypeCode>
<pur:InterfaceLineKey>REQ_ILK_NSK_22</pur:InterfaceLineKey>
<pur:ItemDescription>TEST Item Description</pur:ItemDescription>
<pur:LineType>Goods</pur:LineType>
<pur:ProcurementBUName>US1 Business Unit</pur:ProcurementBUName>
<pur:Quantity unitCode="">10</pur:Quantity>
<pur:Price currencyCode="USD">9</pur:Price>
<pur:RequesterEmail>x.y@oracle.com</pur:RequesterEmail>
<pur:RequesterId>300000047340498</pur:RequesterId>
           
<pur:SupplierId>300000047414635</pur:SupplierId>
<pur:SupplierSiteId>300000047414655</pur:SupplierSiteId>
<pur:UnitOfMeasureCode>zzy</pur:UnitOfMeasureCode>
<pur:RequestedDeliveryDate>2019-12-30</pur:RequestedDeliveryDate>
<pur:PurchaseRequestInputReqDistInterface>
<pur:ChargeAccountId>10000</pur:ChargeAccountId>
<pur:DistributionAmount currencyCode="USD">90</pur:DistributionAmount>
<pur:DistributionQuantity unitCode="zzy">10</pur:DistributionQuantity>
<pur:Percent>100</pur:Percent>
</pur:PurchaseRequestInputReqDistInterface>
</pur:PurchaseRequestInputReqLineInterface>
</typ:purchaseRequestPayload>
</typ:createRequisition>
   </soapenv:Body>
</soapenv:Envelope>