SOAP Payload to get Sales Order Details Oracle cloud 20a (Oracle Fusion Sales order details)

Use SOAP UI

---------------------------------------------------------------------------------------------------

Service WSDL URL: https://XXX-XX.oraclecloud.com/fscmService/OrderInformationService?WSDL

Operation: Get_order_details

--------------------------------------------------------------------------------------------------------------------------

<soapenv:Envelope xmlns:ord="http://xmlns.oracle.com/apps/scm/doo/decomposition/orderDetailServices/orderInformationService/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/scm/doo/decomposition/orderDetailServices/orderInformationService/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-DCBB78C265A4ECA9AB15943860156682">

            <wsu:Created>2020-07-10T13:00:15.668Z</wsu:Created>

         </wsu:Timestamp>

         <wsse:UsernameToken wsu:Id="UsernameToken-DCBB78C265A4ECA9AB15943860075601">

            <wsse:Username>{fusion_username}</wsse:Username>

            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">{_fusion_password}</wsse:Password>

            <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">zv144sI+5IfEFAgr5Rg9fA==</wsse:Nonce>

            <wsu:Created>2020-07-10T13:00:07.551Z</wsu:Created>

         </wsse:UsernameToken>

      </wsse:Security>

   </soapenv:Header>

   <soapenv:Body>

      <typ:GetOrderDetails>

         <!--Zero or more repetitions:-->

         <typ:Order>

            <ord:SourceTransactionNumber>{sales_order_number_here}</ord:SourceTransactionNumber>

         </typ:Order>

      </typ:GetOrderDetails>

   </soapenv:Body>

</soapenv:Envelope>

Query to find ship_to and bill_to site Id for Sales order Import Oracle Cloud 20a (Oracle Fusion Sales order Import )

SELECT  hzp.party_name

        ||

        ' '

        ||

        hzp.party_number                                   ,

        hzp.party_id                                       ,

        HZA.account_number                                 ,

        HZA.Account_name                                   ,

        hza.CUST_ACCOUNT_ID                                ,

        HZA.status "Account Status"                        ,

        hzp.status "Party Status"                          ,

        hzps.status "Party Site Status"                    ,

        hzps.party_site_id "PARTY SITE ID - for SHIP_TO"   ,

        hzcasa.status "Account Site Status"                ,

        hzcsua.site_use_id "Account Site ID - for BILL_TO" ,

        hzcasa.start_Date                                  ,

        hzcasa.end_Date                                    ,

        hzcsua.SITE_USE_CODE                               ,

        hzcasa.BILL_TO_FLAG                                ,

        hzcasa.SHIP_TO_FLAG                                ,

        hzcsua.PRIMARY_FLAG                                ,

        hzcsua.STATUS "Account Site USE Status"            ,

        hzcsua.LOCATION                                    ,

        hzl.ADDRESS1                                       ,

        hzl.ADDRESS2                                       ,

        hzl.ADDRESS3                                       ,

        hzl.ADDRESS4                                       ,

        hzl.CITY                                           ,

        hzl.POSTAL_CODE                                    ,

        hzl.STATE                                          ,

        hzl.COUNTRY                                        ,

        hzl.LOCATION_ID

FROM    fusion.HZ_PARTIES HZP               ,

        fusion.HZ_PARTY_SITES hzps          ,

        fusion.HZ_CUST_ACCOUNTS HZA         ,

        fusion.HZ_CUST_ACCT_SITES_ALL hzcasa,

        fusion.HZ_CUST_SITE_USES_ALL hzcsua ,

        fusion.hz_locations HZL

WHERE   hzP.party_id                 = HZA.party_id (+)

        AND hza.CUST_ACCOUNT_ID      = hzcasa.CUST_ACCOUNT_ID (+)

        AND hzcasa.party_site_id     = hzps.party_site_id (+)

        AND hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id (+)

        AND hzps.location_id         = hzl.location_id (+)

        --         and       (

        --                      hzcasa.start_Date    <= sysdate

        --                        OR hzcasa.start_Date IS NULL

        --                )

        --                AND

        --                (

        --                        hzcasa.end_date    >= sysdate

        --                        OR hzcasa.end_date IS NULL

        --                )

        AND upper(hzP.party_name) LIKE upper('%&CUSTOMER_NAME%')

ORDER BY hzp.party_number ,

        hza.account_number,

        hzl.LOCATION_ID;

----------------------------------------------------------------

Refer Oracle not : 2474011.1  for more details.

Account Hierarchy query In Fusion Cloud

SELECT TREE_CODE, PARENT_TREE_NODE_ID, PK1_START_VALUE

FROM FND_TREE_NODE

WHERE TREE_STRUCTURE_CODE = 'HIERARCHY NAME HERE'

IF PARENT_TREE_NODE_ID is NULL that means it is the parent code or child code dose not belong to any parent.

Find Oracle Fusion . Cloud Sales order Import Errors - query

Below query will return Error messages for "Import Sales Order ESS" in Oracle Management cloud.


SELECT
msg.*

FROM fusion.DOO_ORDER_HEADERS_ALL_INT orderHdr,
fusion.DOO_ORDER_LINES_ALL_INT OrderLine ,
fusion.DOO_MESSAGES_VL MSG ,
fusion.DOO_MESSAGE_REQUESTS DMR
WHERE orderHdr.source_transaction_ID = dmv.msg_entity_id1
AND orderHdr.source_transaction_ID = OrderLine.source_transaction_ID
AND orderHdr.interface_status_code IS NOT NULL
AND orderHdr.load_request_id = OrderLine.load_request_id
AND DMR.ACTIVE_FLAG = 'Y'
AND DMV.MSG_REQUEST_ID = DMR.MSG_REQUEST_ID
AND orderHdr.batch_name = 'SI2140420' ORDER BY orderHdr.creation_Date DESC

select
REQ.CREATION_DATE    ,
REQ.ACTIVE_FLAG      ,
REQ.REQ_ENTITY_TYPE  ,
REQ.REQ_ENTITY_ID1   ,
REQ.REQ_ENTITY_ID2   ,
REQ.REQ_ENTITY_ID3   ,
REQ.REQ_ENTITY_ID4   ,
REQ.REQ_ENTITY_ID5   ,
MB.MESSAGE_ID   M_ID     ,
MT.MESSAGE_ID        ,
MT.MESSAGE_TEXT      ,
MB.MESSAGE_TYPE      ,
MB.MSG_REQUEST_ID    ,
MB.MESSAGE_NAME
from    DOO_MESSAGE_REQUESTS REQ,
DOO_MESSAGES_B MB       ,
DOO_MESSAGES_TL MT 
where   MB.MSG_REQUEST_ID =REQ.MSG_REQUEST_ID
and MB.MESSAGE_ID = MT.MESSAGE_ID
and MT.LANGUAGE   = 'US'
and REQ.ACTIVE_FLAG = 'Y'
-- and REQ.REQ_ENTITY_ID1  = '20200331-29718711-U1'
order by creation_date desc