Sample Payload for Creating Customer Account Relationships in Oracle Fusion

SOAP API: crmService/CustomerAccountService

Service Name: mergeCustomerAccount

Payload: 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"

xmlns:typ="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/applicationModule/types/"

xmlns:cus="http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/"

xmlns:cus1="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountContactRole/"

xmlns:par="http://xmlns.oracle.com/apps/cdm/foundation/parties/partyService/"

xmlns:sour="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/sourceSystemRef/"

xmlns:cus2="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountContact/"

xmlns:cus3="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountRel/"

xmlns:cus4="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSiteUse/"

xmlns:cus5="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccountSite/"

xmlns:cus6="http://xmlns.oracle.com/apps/cdm/foundation/parties/flex/custAccount/">

<soapenv:Header/>

<soapenv:Body>

<typ:mergeCustomerAccount>

<typ:customerAccount>

<!--Optional:-->

<cus:CustomerAccountId>100000024833854</cus:CustomerAccountId>

<!--Optional:-->

<cus:PartyId>100000014253697</cus:PartyId>

<!--Optional:-->

<cus:CreatedByModule>ORA_HZ_DATA_IMPORT</cus:CreatedByModule>

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

<cus:CustomerAccountRelationship>

<!--Optional:-->

<cus:CustomerAccountId>100000024833854</cus:CustomerAccountId>

<!--Optional:-->

<cus:RelatedCustomerAccountId>300000036314223</cus:RelatedCustomerAccountId>

<cus:CustomerReciprocalFlag>false</cus:CustomerReciprocalFlag>

<!--Optional:-->

<cus:Status>A</cus:Status>

<!--Optional:-->

<cus:BillToFlag>false</cus:BillToFlag>

<!--Optional:-->

<cus:ShipToFlag>true</cus:ShipToFlag>

<!--Optional:-->

<cus:StartDate>2024-07-31</cus:StartDate>

<!--Optional:-->

<cus:CreatedByModule>POS_SUPPLIER_MGMT</cus:CreatedByModule>

<!--Optional:-->

<cus:SetId>300000000003311</cus:SetId>

</cus:CustomerAccountRelationship>

</typ:customerAccount>

</typ:mergeCustomerAccount>

</soapenv:Body>

</soapenv:Envelope>

Page Composer - Enabling Button and executing SOAP/REST API in Oracle fusion

Using Page Composer one can enable a custom Button and execute the SOAP/REST API dynamically by passing the parameters using the Bind parameters available in Oracle Fusion page. 

Follow the steps mentioned below.
 

Enable the Page Composer using Sandbox. (Configuration > Sandboxes > Create Sandbox)

Name : customButton

Publishable: Yes

Select Checkbox for Page Composer 

Navigate to the Page where the button needed. For the blog I'm creating the button On "Transactions" Page where user can click the button and submit an ESS job "Print Receivables Transactions" to print the invoice and attach the pdf to the invoice also send an copy of invoice to the customer. 

Navigate to Receivables > Billing > Manage Transaction

After opening the transaction window Click on Tools > Page Composer

Click On the "Structure" tab and pull the page towards Up from the bottom so that the technical components of the page can be visible.

Under the "Miscellaneous" Tab, just above the Generate Bill will be adding the Button so hover the cursor and select the main frame of the "Generate Bill" Section.

Click the Add "+" button on the Dock. Make sure the Frame element "panelFromLayout" is selected.

Click on "Open" next to "Component" in the Add Content window.

Click on "+ Add" next to the HTML Markup in the available components and click on "Close" button.

After adding the HTML Markup, you should see the "HTML Markup" is added on the page and two new elements added to "panelFormLAyout". 

Select the "<>outputTest: New HTML Markup" and click on gear icon (Show the properties of New HTML Markup).

In the "Component Properties" window click the Down arrow next to the "Value" and select the "Expression Builder".

In the "Expression Builder Copy paste the below code (Kindly modify the code as per your requirement" and test the code before deploying it in the Production or any of your environment. 

<html>

<body>

<button id="custCompleteAndReview" class="button" onclick="(function(p_org_id, p_trx_number){

//console.log('Parameters received are : '+p_org_id+'-'+p_trx_number);

var xmlhttp = new XMLHttpRequest();

var finalurl = 'https://' + window.location.host + '/fscmService/ErpIntegrationService';

xmlhttp.open('POST', finalurl, true);

credentials = 'Basic ' + 'c3ZjLnBOkludGVncmF0aW9zZXJAMTIzNA==';

xmlhttp.setRequestHeader('Authorization', credentials);

var sr = '<soapenv:Envelope xmlns:soapenv=&quot;http://schemas.xmlsoap.org/soap/envelope/&quot; xmlns:typ=&quot;http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/&quot;> ' +

'<soapenv:Header/> ' +

'<soapenv:Body> ' +

'<typ:submitESSJobRequest> ' +

'<typ:jobPackageName>/oracle/apps/ess/financials/receivables/transactions/shared/</typ:jobPackageName> ' +

'<typ:jobDefinitionName>TransactionPrintProgramEss</typ:jobDefinitionName> ' +

'<typ:paramList>'+ p_org_id + '</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>ANY</typ:paramList> ' +

'<typ:paramList>TRX_NUMBER</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>'+ p_trx_number + '</typ:paramList> ' +

'<typ:paramList>'+ p_trx_number + '</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>N</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>PDF</typ:paramList> ' +

'<typ:paramList>Default Invoice Template</typ:paramList> ' +

'<typ:paramList>Default Credit Memo Template</typ:paramList> ' +

'<typ:paramList>Default Debit Memo Template</typ:paramList> ' +

'<typ:paramList>Default Chargeback Template</typ:paramList> ' +

'<typ:paramList>N</typ:paramList> ' +

'<typ:paramList>#NULL</typ:paramList> ' +

'<typ:paramList>-1</typ:paramList> ' +

'</typ:submitESSJobRequest> ' +

'</soapenv:Body> ' +

'</soapenv:Envelope> ' ;

xmlhttp.onreadystatechange = function () {

if (xmlhttp.readyState == 4) {

if (xmlhttp.status == 200) {

let result = extractResultValue(xmlhttp.responseText);

alert ('Print Receivables Transactions ESS job Submitted successfully. Process ID : ' + result );

}

}

}

xmlhttp.setRequestHeader('Content-Type', 'text/xml');

xmlhttp.send(sr);

function extractResultValue(text) {

var xmlStartIndex = text.indexOf('<env:Envelope');

var xmlEndIndex = text.lastIndexOf('</env:Envelope>') + '</env:Envelope>'.length;

var xmlContent = text.substring(xmlStartIndex, xmlEndIndex);

var parser = new DOMParser();

var xmlDoc = parser.parseFromString(xmlContent, 'text/xml');

var resultTag = xmlDoc.querySelector('result');

if (resultTag) {

return resultTag.textContent;

} else {

return null;

}

}

String.prototype.obfs = function(key, n = 126) {

if (!(typeof(key) === 'number' && key % 1 === 0)

|| !(typeof(key) === 'number' && key % 1 === 0)) {

return this.toString();

}

var chars = this.toString().split('');

for (var i = 0; i < chars.length; i++) {

var c = chars[i].charCodeAt(0);

if (c <= n) {

chars[i] = String.fromCharCode((chars[i].charCodeAt(0) + key) % n);

}

}

return chars.join('');

};

})('', '');return false" > Reprint</button>

</body>

</html> <!-- typo -->

Click on "Test" and Ok to close the Expression Builder Window. 

Click on Apply and OK button in the Component Properties window to close the Properties window. 

Test your code.

Party / Organization / Customer Merge in Oracle Fusion

Profile Options:

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

Role: Data Steward Manager

Task: Manage Administrator Profile Value

Set the below Profile options

ZCA_MERGE_REQUEST Site=Yes

ZCH_AUTO_MERGE_THRESHOLD level =0

ZCH_USER_MERGE_REQUESTS to have Allow processing without approval.

Please follow the next steps:

Step1: Navigate to Customer Data Management > Duplicate Resolution 

     > Task List > Create Resolution Request > Search for Customer 

     > select it > Create Request

Step2: Duplicate Resolution (CDM) > Request is in status Pending 

> select the row > Actions > Submit

Step3: Go to Setup and Maintenance > search for Run Request Dispatch Job 

> do not modify anything, just Submit

Step4: After Request ID run successfully > Return to Duplicate Resolution 

> Status is now New

Step5: Enter on Request ID > Override

Step6: Next > Select Accounts to be merged > Merge 

> You can choose the Master Account > Next > Next > Submit

Step7: Duplicate Resolution (CDM) > Request is in status Submitted

Step8: Go to Setup and Maintenance > search for Run Request Dispatch Job 

> do not modify anything, just Submit

Step9: Duplicate Resolution (CDM) > Request is in status Completed

Step10: Check Customer in Receivables > Billing > Manage Customers

The accounts are now merged

SOAP API to FindPerson (Contact Details) using FirstName and LastName

 SOAP API

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

 https://servername/crmService/FoundationPartiesPersonService

Service Name

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

findPerson

Sample Payload 

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

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/cdm/foundation/parties/personService/applicationModule/types/" xmlns:ns43="http://xmlns.oracle.com/adf/svc/types/">

   <soapenv:Header/>

   <soapenv:Body>

<typ:findPerson>

<typ:findCriteria>

<ns43:fetchStart>0</ns43:fetchStart>

<ns43:fetchSize>1</ns43:fetchSize>

<ns43:filter>

<ns43:group>

<ns43:item>

<ns43:attribute>PersonFirstName</ns43:attribute>

<ns43:operator>=</ns43:operator>

<ns43:value>Seth</ns43:value>

</ns43:item>

<ns43:item>

<ns43:attribute>PersonLastName</ns43:attribute>

<ns43:operator>=</ns43:operator>

<ns43:value>Laskarzewski</ns43:value>

</ns43:item>

</ns43:group>

</ns43:filter>

<ns43:sortOrder>

<ns43:sortAttribute>

<ns43:name>CreationDate</ns43:name>

<ns43:descending>true</ns43:descending>

</ns43:sortAttribute>

</ns43:sortOrder>

<ns43:childFindCriteria>

<ns43:fetchStart>0</ns43:fetchStart>

<ns43:fetchSize>1</ns43:fetchSize>

<ns43:filter>

<ns43:group>

<ns43:item>

<ns43:attribute>RoleType</ns43:attribute>

<ns43:operator>=</ns43:operator>

<ns43:value>CONTACT</ns43:value>

</ns43:item>

</ns43:group>

</ns43:filter>

</ns43:childFindCriteria>

</typ:findCriteria>

</typ:findPerson>

   </soapenv:Body>

</soapenv:Envelope>

Update Credit Card "Assignment Inactive On" for a Customer in Oracle Fusion (Using SOAP/REST API)

 Use the below WSDL to get the Instrument assignment id for all available Credit Cards on a Customer

WSDL: https://serverName/fscmService/PayerDetailServiceV2?WSDL
Service Name: findAssignedInstrumentsByPayer

PartyID : PARTY_ID of HZ_PARTIES of a Customer 

CustomerAccountId : CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS 

Sample Payload
==================

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/types/">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:findAssignedInstrumentsByPayer>
         <typ:partyId>100000014531116</typ:partyId>
         <typ:customerAccountId>100000032507573</typ:customerAccountId>
         <typ:paymentFunction>CUSTOMER_PAYMENT</typ:paymentFunction>
         <typ:instrumentType>CREDITCARD</typ:instrumentType>
      </typ:findAssignedInstrumentsByPayer>
   </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/payments/shared/payeePayerInstruments/externalPayerServiceV2/PayerDetailService/findAssignedInstrumentsByPayerResponse</wsa:Action>
      <wsa:MessageID>urn:uuid:d1447dc5-20e8-4f7e-9fa3-f90ee4f2e9df</wsa:MessageID>
   </env:Header>
   <env:Body>
      <ns0:findAssignedInstrumentsByPayerResponse xmlns:ns0="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/types/">
         <ns2:result xsi:type="ns1:AssignedPmtInstrument" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns1="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns2="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/types/">
            <ns1:InstrAssignmentId>300000055014</ns1:InstrAssignmentId>
            <ns1:ExternalPayerId>300000055013</ns1:ExternalPayerId>
            <ns1:PaymentFunction>CUSTOMER_PAYMENT</ns1:PaymentFunction>
            <ns1:PartyId>100000011116</ns1:PartyId>
            <ns1:CustomerAccountId>100000007573</ns1:CustomerAccountId>
            <ns1:AccountSiteUseId xsi:nil="true"/>
            <ns1:InstrumentType>CREDITCARD</ns1:InstrumentType>
            <ns1:InstrumentId>31300900</ns1:InstrumentId>
            <ns1:CurrencyCode xsi:nil="true"/>
            <ns1:CardNumber>7809</ns1:CardNumber>
            <ns1:CardExpirydate>2025-08-31</ns1:CardExpirydate>
            <ns1:CardIssuerCode>MASTERCARD</ns1:CardIssuerCode>
            <ns1:CardIssuerName>MasterCard</ns1:CardIssuerName>
            <ns1:PurchasecardSubtype xsi:nil="true"/>
            <ns1:CardHolderName>ABC Corp</ns1:CardHolderName>
            <ns1:CardBillingAddressId>300000019901</ns1:CardBillingAddressId>
            <ns1:CardFiName>300000008684</ns1:CardFiName>
            <ns1:CardSingleUseFlag>false</ns1:CardSingleUseFlag>
            <ns1:CardInformationOnlyFlag>false</ns1:CardInformationOnlyFlag>
            <ns1:AccountNumber xsi:nil="true"/>
            <ns1:BankName xsi:nil="true"/>
            <ns1:BankNumber xsi:nil="true"/>
            <ns1:BranchName xsi:nil="true"/>
            <ns1:BranchNumber xsi:nil="true"/>
            <ns1:BICNumber xsi:nil="true"/>
            <ns1:Description xsi:nil="true"/>
            <ns1:AssignmentLastUpdate>2022-12-16T11:49:26Z</ns1:AssignmentLastUpdate>
            <ns1:AssignmentStartDate>2022-09-30</ns1:AssignmentStartDate>
            <ns1:AssignmentEndDate>4712-12-31</ns1:AssignmentEndDate>
            <ns1:CreatedBy>xyz</ns1:CreatedBy>
            <ns1:CreationDate>2022-12-16T11:49:25.047Z</ns1:CreationDate>
            <ns1:LastUpdatedBy>xyv</ns1:LastUpdatedBy>
            <ns1:LastUpdateDate>2022-12-16T11:49:26Z</ns1:LastUpdateDate>
            <ns1:LastUpdateLogin>EFF17E6849E8E0539560310A7831</ns1:LastUpdateLogin>
            <ns1:ObjectVersionNumber>2</ns1:ObjectVersionNumber>
            <ns1:PrimaryFlag>true</ns1:PrimaryFlag>
         </ns2:result>
         <ns2:result xsi:type="ns1:AssignedPmtInstrument" xmlns:ns0="http://xmlns.oracle.com/adf/svc/types/" xmlns:ns1="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns2="http://xmlns.oracle.com/apps/financials/payments/shared/payeePayerInstruments/externalPayerServiceV2/types/">
            <ns1:InstrAssignmentId>300000063931</ns1:InstrAssignmentId>
            <ns1:ExternalPayerId>300000055013</ns1:ExternalPayerId>
            <ns1:PaymentFunction>CUSTOMER_PAYMENT</ns1:PaymentFunction>
            <ns1:PartyId>100000011116</ns1:PartyId>
            <ns1:CustomerAccountId>100000007573</ns1:CustomerAccountId>
            <ns1:AccountSiteUseId xsi:nil="true"/>
            <ns1:InstrumentType>CREDITCARD</ns1:InstrumentType>
            <ns1:InstrumentId>2479025</ns1:InstrumentId>
            <ns1:CurrencyCode xsi:nil="true"/>
            <ns1:CardNumber>XXXXXXXXXXXX5100</ns1:CardNumber>
            <ns1:CardExpirydate>2030-03-31</ns1:CardExpirydate>
            <ns1:CardIssuerCode>MASTERCARD</ns1:CardIssuerCode>
            <ns1:CardIssuerName>MasterCard</ns1:CardIssuerName>
            <ns1:PurchasecardSubtype xsi:nil="true"/>
            <ns1:CardHolderName>ABC Corp</ns1:CardHolderName>
            <ns1:CardBillingAddressId>300000013928</ns1:CardBillingAddressId>
            <ns1:CardFiName>300000008683</ns1:CardFiName>
            <ns1:CardSingleUseFlag>false</ns1:CardSingleUseFlag>
            <ns1:CardInformationOnlyFlag>false</ns1:CardInformationOnlyFlag>
            <ns1:AccountNumber xsi:nil="true"/>
            <ns1:BankName xsi:nil="true"/>
            <ns1:BankNumber xsi:nil="true"/>
            <ns1:BranchName xsi:nil="true"/>
            <ns1:BranchNumber xsi:nil="true"/>
            <ns1:BICNumber xsi:nil="true"/>
            <ns1:Description xsi:nil="true"/>
            <ns1:AssignmentLastUpdate>2023-08-28T14:52:29.542Z</ns1:AssignmentLastUpdate>
            <ns1:AssignmentStartDate>2023-08-28</ns1:AssignmentStartDate>
            <ns1:AssignmentEndDate>4712-12-31</ns1:AssignmentEndDate>
            <ns1:CreatedBy>xyz</ns1:CreatedBy>
            <ns1:CreationDate>2023-08-28T14:52:12.09Z</ns1:CreationDate>
            <ns1:LastUpdatedBy>xyz</ns1:LastUpdatedBy>
            <ns1:LastUpdateDate>2023-08-28T14:52:29.542Z</ns1:LastUpdateDate>
            <ns1:LastUpdateLogin>013AE5810B5E063E262310ABDD1</ns1:LastUpdateLogin>
            <ns1:ObjectVersionNumber>1</ns1:ObjectVersionNumber>
            <ns1:PrimaryFlag>false</ns1:PrimaryFlag>
         </ns2:result>
      </ns0:findAssignedInstrumentsByPayerResponse>
   </env:Body>
</env:Envelope>

Use the Instrument assignment ID from above Payload in below Mentioned REST API

REST API 

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

https://servername/fscmRestApi/resources/11.13.18.05/instrumentAssignments/{instrumentassignmentid}

Operation: PATCH

Sample Payload

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

{ 

      "EndDate": "2024-03-12", 

      "Intent": null 

} 

User below SQL to get the Instrument assignment id for a Customer

SELECT p.party_name

       , ca.cust_account_id

       , ca.account_number

       , iepa.ext_payer_id

       , ipiu.instrument_payment_use_id instr_assignment_id

       , ifte.*

  FROM iby_creditcard icc

       , iby_external_payers_all iepa

       , iby_pmt_instr_uses_all ipiu

       , iby_fndcpt_tx_extensions ifte

       , hz_parties p

       , hz_cust_accounts ca

 WHERE p.party_id = icc.card_owner_id

   AND p.party_id = ca.party_id

   AND icc.card_owner_id = iepa.party_id

   AND ca.cust_account_id = iepa.cust_account_id

   AND iepa.acct_site_use_id IS NULL

   AND icc.instrument_type = ipiu.instrument_type

   AND icc.instrid = ipiu.instrument_id

   AND iepa.ext_payer_id = ipiu.ext_pmt_party_id

   AND ipiu.instrument_payment_use_id = ifte.instr_assignment_id

   AND iepa.ext_payer_id = ifte.ext_payer_id

   AND ca.account_number = :p_account_number; 

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)