SOAP API to Create Receipt Reversal Transaction (Oracle Fusion Receivables Cloud)

 
SOAP API

===========

https://servername/fscmService/StandardReceiptService?WSDL

Service Name

=============

createReverseReceipt

Sample Payload

===============

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/receipts/shared/standardReceiptService/commonService/types/" xmlns:com="http://xmlns.oracle.com/apps/financials/receivables/receipts/shared/standardReceiptService/commonService/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:createReverseReceipt>

         <typ:reverseReceipt>

            <!--Optional:-->

            <!--<com:ReceiptNumber>1111115575</com:ReceiptNumber>-->

            <!--Optional:-->

            <com:ReversalCategory>CC_CHARGEBACK_REV</com:ReversalCategory>

            <!--Optional:-->

            <com:ReversalDate>2024-03-14</com:ReversalDate>

            <!--Optional:-->

            <com:ReversalReasonCode>CC_CHARGEBACK_CHANGE</com:ReversalReasonCode>

            <!--Optional:-->

            <com:ReversalComments>Testing</com:ReversalComments>

            <!--Optional:-->

            <com:BusinessUnit>Business Unit</com:BusinessUnit>

            <!--Optional:-->

            <!--<com:ReversalCategoryName>?</com:ReversalCategoryName>-->

            <!--Optional:-->

            <com:ReversalGlDate>2024-03-14</com:ReversalGlDate>

            <!--Optional:-->

            <!--<com:ReversalReasonName>?</com:ReversalReasonName>-->

            <!--Optional:-->

            <com:ReceiptId>3801247</com:ReceiptId>

            <!--Optional:-->

            <!--<com:InterfaceToBudgeting>?</com:InterfaceToBudgeting>-->

            <!--Optional:-->

            <!--<com:InterfaceFailureReason>?</com:InterfaceFailureReason>-->

         </typ:reverseReceipt>

      </typ:createReverseReceipt>

   </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/receivables/receipts/shared/standardReceiptService/commonService/StandardReceiptService/createReverseReceiptResponse</wsa:Action>

      <wsa:MessageID>urn:uuid:fa70c096-d413-4153-9858-3226af253140</wsa:MessageID>

   </env:Header>

   <env:Body>

      <ns0:createReverseReceiptResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/receivables/receipts/shared/standardReceiptService/commonService/types/">

         <ns1:result xsi:type="ns3:ReverseReceiptResult" xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns3="http://xmlns.oracle.com/apps/financials/receivables/receipts/shared/standardReceiptService/commonService/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns1="http://xmlns.oracle.com/apps/financials/receivables/receipts/shared/standardReceiptService/commonService/types/">

            <ns3:Value>

               <ns3:ReceiptNumber xsi:nil="true"/>

               <ns3:ReversalCategory>CC_CHARGEBACK_REV</ns3:ReversalCategory>

               <ns3:ReversalDate>2024-03-14</ns3:ReversalDate>

               <ns3:ReversalReasonCode>CC_CHARGEBACK_CHANGE</ns3:ReversalReasonCode>

               <ns3:ReversalComments>Testing</ns3:ReversalComments>

               <ns3:BusinessUnit>Business Unit</ns3:BusinessUnit>

               <ns3:ReversalCategoryName xsi:nil="true"/>

               <ns3:ReversalGlDate>2024-03-14</ns3:ReversalGlDate>

               <ns3:ReversalReasonName xsi:nil="true"/>

               <ns3:ReceiptId>3801247</ns3:ReceiptId>

               <ns3:InterfaceToBudgeting>Not Enabled</ns3:InterfaceToBudgeting>

               <ns3:InterfaceFailureReason xsi:nil="true"/>

            </ns3:Value>

         </ns1:result>

      </ns0:createReverseReceiptResponse>

   </env:Body>

</env:Envelope>

FA:OM:SQL: To get the RMA Order receipt details along with the Serial numbers

 WITH sn_tbl as (  SELECT dha.header_id
                            , dla.line_id
                            , dfla.fulfill_line_id
                            , dla.display_line_number | | '.' | | dfla.fulfill_line_number line_num
                            , LISTAGG(iut.serial_number, ',' on overflow truncate) WITHIN GROUP (
                                         ORDER BY iut.serial_number
                                        ) Serial_numbers
                         FROM fusion.inv_material_txns imt
                            , fusion.inv_org_parameters iop
                            , fusion.egp_system_items_b esi
                            , fusion.inv_transaction_types_vl ittv
                            , fusion.fnd_lookup_values_vl flva
                            , fusion.inv_txn_source_types_vl itst
                            , fusion.inv_unit_transactions iut
                            , fusion.rcv_shipment_headers rsh
                            , fusion.rcv_shipment_lines rsl
                            , fusion.rcv_transactions rt
                            , fusion.doo_fulfill_line_details dfld
                            , fusion.doo_fulfill_lines_all dfla
                            , fusion.doo_lines_all dla
                            , fusion.doo_headers_all dha
                        WHERE imt.organization_id = iop.organization_id
                          AND imt.organization_id = esi.organization_id
                          AND imt.inventory_item_id = esi.inventory_item_id
                          AND imt.transaction_type_id = ittv.transaction_type_id
                          AND ittv.transaction_action_id = flva.lookup_code
                          AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
                          AND flva.enabled_flag = 'Y'
                          AND ittv.transaction_source_type_id = itst.transaction_source_type_id
                          AND imt.transaction_id = iut.transaction_id(+)
                          AND ittv.transaction_type_name = 'RMA Receipt'
                          AND itst.transaction_source_type_name = 'RMA'
                          AND rsh.shipment_header_id = rsl.shipment_header_id
                          AND rsh.shipment_header_id = rt.shipment_header_id
                          AND rsl.shipment_header_id = rt.shipment_header_id
                          AND rsl.shipment_line_id = rt.shipment_line_id
                          AND rt.transaction_id = imt.rcv_transaction_id
                          AND imt.rcv_transaction_id = dfld.rma_receipt_transaction_id
                          AND dfld.fulfill_line_id = dfla.fulfill_line_id
                          AND dfla.line_id = dla.line_id
                          AND dla.header_id = dha.header_id
                          AND dfla.header_id = dha.header_id
                          AND dha.submitted_flag = 'Y'
                     GROUP BY dla.display_line_number | | '.' | | dfla.fulfill_line_number
                            , dha.header_id
                            , dla.line_id
                            , dfla.fulfill_line_id)
   SELECT hauft.NAME BusinessUnit
        , a.order_type_code
        , o.organization_code | | '  ' | | flv.meaning warehouse
        , a.order_number
        , f.source_line_number
        , i.item_number
        , b.ordered_qty l_qty
        , f.rma_delivered_qty f_del_qty
        , f.shipped_qty s_qty
        , f.CANCELED_QTY
        , initcap(f.status_code) f_status_code
        , decode(f.canceled_flag, 'Y', 'Line Canceled', 'Not Canceled') Line_cancel_status
        , a.creation_date
        , i.description
        , A.SOURCE_ORDER_SYSTEM | | ':' | | A.SOURCE_ORDER_ID ORDER_KEY
        , a.ordered_date
        , DECODE(i.serial_number_control_code, 1, 'No', 'Yes') serial_number_control_code
        , sn_tbl.Serial_numbers
     FROM fusion.doo_headers_all a
        , fusion.doo_lines_All b
        , fusion.doo_fulfill_lines_all f
        , fusion.egp_system_items_v i
        , fusion.inv_org_parameters o
        , fusion.fnd_lookup_values_vl flv
        , fusion.hr_org_unit_classifications_f houcf
        , fusion.hr_all_organization_units_f haouf
        , fusion.hr_organization_units_f_tl hauft
        , sn_tbl
    WHERE 1 = 1
      AND a.submitted_flag = 'Y'
      AND a.header_id = b.header_id
      AND b.header_id = f.header_id
      AND b.line_id = f.line_id
      AND a.order_type_code = flv.lookup_code
      AND flv.lookup_type = 'ORA_DOO_ORDER_TYPES'
      AND flv.enabled_flag = 'Y'
      AND f.fulfill_org_id = i.organization_id
      AND f.fulfill_org_id = o.organization_id
      AND f.inventory_item_id = i.inventory_item_id
      AND haouf.organization_id = houcf.organization_id
      AND haouf.organization_id = hauft.organization_id
      AND hauft.organization_id = a.org_id
      AND hauft.language = 'US'
      AND sysdate BETWEEN houcf.effective_start_date AND NVL(houcf.effective_end_date, SYSDATE + 1)
      AND sysdate BETWEEN haouf.effective_start_date AND NVL(haouf.effective_end_date, SYSDATE + 1)
      AND sysdate BETWEEN hauft.effective_start_date AND NVL(hauft.effective_end_date, SYSDATE + 1)  
      AND hauft.effective_start_date = haouf.effective_start_date
      AND hauft.effective_end_date = haouf.effective_end_date
      AND houcf.classification_code = 'FUN_BUSINESS_UNIT'
      AND f.header_id = sn_tbl.header_id
      AND f.line_id = sn_tbl.line_id
      and f.fulfill_line_id = sn_tbl.fulfill_line_id
      AND a.order_number = :p_order_number
 ORDER BY a.creation_date
        , LPAD(f.source_line_number, 2)

FA:RCV:SQL to get RMA Receipts Details in Oracle Fusion

 REM RMA Receipts Details
SELECT DHA.SOURCE_ORDER_NUMBER
     , HDR_LKP.MEANING ORDER_TYPE
     , DFLA.SOURCE_LINE_NUMBER
     , LINE_LKP.MEANING LINE_TYPE     
     , INV.ORGANIZATION_CODE
     , DSV.DISPLAY_NAME FUL_LINE_STATUS
     , (SELECT TO_CHAR(CREATION_DATE, 'DD-MON-YYYY') FROM INV_MATERIAL_TXNS WHERE RCV_TRANSACTION_ID = RMA_RECEIPT_TRANSACTION_ID) INV_TXN_CREATION_DATE
     , ESIB.ITEM_NUMBER
     , DLA.ORDERED_QTY LINE_ORDERED_QTY
     , DFLA.ORDERED_QTY FULFILL_LINE_ORDERED_QTY
     , DFLA.RMA_DELIVERED_QTY
     , DFLD.RMA_RECEIPT_NUMBER
     , DFLD.RMA_RECEIPT_LINE_NUMBER
     , DFLD.RMA_RECEIPT_DATE
     , DLA.DISPLAY_LINE_NUMBER
         || '.'
         || DFLA.FULFILL_LINE_NUMBER LINE_NUM
  FROM FUSION.INV_ORG_PARAMETERS INV
     , FUSION.DOO_FULFILL_LINES_ALL DFLA
     , FUSION.DOO_FULFILL_LINE_DETAILS DFLD
     , FUSION.FND_LOOKUP_VALUES_VL LINE_LKP     
     , FUSION.DOO_LINES_ALL DLA
     , FUSION.FND_LOOKUP_VALUES_VL HDR_LKP
     , FUSION.DOO_HEADERS_ALL DHA
     , FUSION.EGP_SYSTEM_ITEMS_B ESIB
     , FUSION.DOO_STATUSES_VL DSV
     , FUSION.DOO_ORCHESTRATION_APPLICATIONS DOA
 WHERE DHA.HEADER_ID = DFLA.HEADER_ID
   AND DHA.HEADER_ID = DLA.HEADER_ID
   AND DFLA.LINE_ID = DLA.LINE_ID
   AND DHA.SUBMITTED_FLAG = 'Y'
   AND DHA.ORDER_TYPE_CODE = HDR_LKP.LOOKUP_CODE
   AND HDR_LKP.LOOKUP_TYPE = 'ORA_DOO_ORDER_TYPES'
   AND HDR_LKP.ENABLED_FLAG = 'Y'   
   AND DFLA.LINE_TYPE_CODE = LINE_LKP.LOOKUP_CODE
   AND LINE_LKP.LOOKUP_TYPE IN ('ORA_DOO_LINE_TYPES', 'ORA_DOO_RETURN_LINE_TYPES')
   AND LINE_LKP.ENABLED_FLAG = 'Y'   
   AND DOA.ORCHESTRATION_APPLICATION_CODE = 'DOO'
   AND DOA.ORCHESTRATION_APPLICATION_ID = DSV.ORCHESTRATION_APPLICATION_ID
   AND DFLA.STATUS_CODE = DSV.STATUS_CODE
   AND DLA.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
   AND DLA.INVENTORY_ORGANIZATION_ID = ESIB.ORGANIZATION_ID
   AND DFLA.FULFILL_ORG_ID = INV.ORGANIZATION_ID
   AND UPPER(DFLD.TASK_TYPE(+)) = 'RETURN'
   AND DFLA.FULFILL_LINE_ID = DFLD.FULFILL_LINE_ID(+)
   AND DHA.SOURCE_ORDER_NUMBER = :P_ORDER_NUMBER
   --AND ESIB.ITEM_NUMBER  = NVL(:P_ITEM_NUMBER, ESIB.ITEM_NUMBER)
  ORDER BY DHA.SOURCE_ORDER_NUMBER, LPAD(DLA.DISPLAY_LINE_NUMBER, 2) ASC;