Integrationpublic

Integration

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Mark Chappell
    Custom SQL queries against Cloud ERP via OAICS
    Topic posted July 18, 2018 by Mark ChappellBlue Ribbon: 750+ Points, last edited July 18, 2018, tagged Adapters, Database, ERP Cloud, Integration 
    260 Views, 2 Comments
    Title:
    Custom SQL queries against Cloud ERP via OAICS
    Summary:
    Custom SQL queries against Cloud ERP via OAICS
    Content:

    Hi All,

    I am building an integration in OAICS, based on (I expect a common scenario) where we are creating Requisitions in Cloud ERP, from a flat file delivered to an FTP Server. I'm using the bulk import process, FBDI. 

    I need to enrich the Requisition data I receive, before creating the requisition. Specifically, we are given a Blanket Purchase Order number, and I need to obtain from this certain supplier details. I have a SQL query as follows;

    SELECT POH.SEGMENT1
    ,      POS.VENDOR_NAME
    ,      POSS.ADDRESS_LINE1 
    FROM   PO_HEADERS_ALL POH
    ,      POZ_SUPPLIERS_V POS
    ,      POZ_SUPPLIER_SITES_V POSS
    WHERE  POH.VENDOR_ID = POS.VENDOR_ID
    AND    POS.VENDOR_ID = POSS.VENDOR_ID
    AND    POH.SEGMENT1 = 'PA3004'

    I am hoping there is a way, we can register in our Integration and then execute custom SQL against the ERP Cloud database and pass in the value for POH.SEGMENT1.

    I have seen that the Oracle RightNow Adapter provides for the ability to use ROQL to run custom queries, but this is not available in the Oracle ERP Adapter.

    Any help / advice is greatly appreciated.

    Thank You

    Comment

     

    • Saravanan Thirunavukkarasu

      Hi Mark,

      1. Create BI DataModel in ERP Cloud with the SQL 

      2. Call BI SOAP webservice from ICS using SOAP adapter

    • Mark Chappell

      Hi Saravanan, 

      thank you for your reply. I have managed to get this working, and have a simple BI Report created in Cloud ERP, which takes a parameter and returns a single value. SQL is;

      SELECT :value FROM DUAL

      the report output is CSV. I've declared a parameter called " value ".

      immediately after I call the report in the integration (via the SOAP adapter) I use the Assign Action, to assign the report output to a variable. I use " decodeBase64( reportBytes) " to do this.

      I can successfully execute my integration (prior to calling the SOAP adapater to run the report, I have a map action and set the parameter value, to "MARK2" ) and can view the payload of the assign action, and see the returned report out put;

      77u/VkFMVUUsX1ZBTFVFCk1BUksyLE1BUksyCg==

      which when decoded gives;

      VALUE,_VALUE
      MARK2,MARK2

      My issue now is how to extract from the report output the actual returned value, in this case "MARK2", so i can use as I need in the integration. 

      Is my BI Pub Report defined incorrectly, and so returning column headers? Or do I need to decode with something other than decodeBase64( reportBytes) ? Or do we need to add in additional substring functions when using the decode function, in the assigning report output to variables? 

      any help greatley appreciated/

      thanks 

      Mark

     

    community forum