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>

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>

SOAP to Create Debit memo (Oracle Fusion Receivables Cloud Service)

 SOAP API
===========

https://servername/fscmService/DebitMemoService?WSDL

Service Name

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

createDebitMemo

Sample Payload

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

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/debitMemoService/types/" xmlns:deb="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/debitMemoService/">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:createDebitMemo>

         <typ:debitMemo>

            <deb:BatchSourceSequenceId>37711366</deb:BatchSourceSequenceId>

            <!--BATCH_SOURCE_SEQ_ID :-->

            <deb:CustomerTrxSquenceId>38859442</deb:CustomerTrxSquenceId>

            <!--CUST_TRX_TYPE_SEQ_ID put Debit memo here :-->

            <deb:InvoiceCurrencyCode>USD</deb:InvoiceCurrencyCode>

            <deb:OrgId>33059258</deb:OrgId>

            <deb:BillToCustomerId>132506363</deb:BillToCustomerId>

            <!--BILL_TO_CUSTOMER_ID:-->

            <deb:BillToAddressId>316731415</deb:BillToAddressId>

            <!--BILL_TO_ADDRESS_ID:-->

            <deb:BillToSiteUseId>316731416</deb:BillToSiteUseId>

            <!--BILL_TO_SITE_USE_ID:-->

            <deb:Comments>test debit memo service</deb:Comments>

            <deb:DefaultTaxationCountry>US</deb:DefaultTaxationCountry>

            <deb:PaymentTermsId>32914118</deb:PaymentTermsId>

            <!--TERM_ID:-->

            <deb:RemitToAddressSequenceId>32914100</deb:RemitToAddressSequenceId>

            <!--REMIT_TO_ADDRESS_SEQ_ID:-->

            <deb:ShipToAddressId>132255334</deb:ShipToAddressId>

            <!--SHIP_TO_PARTY_ADDRESS_ID:-->

            <deb:ShipToCustomerId>114525837</deb:ShipToCustomerId>

            <!--SHIP_TO_PARTY_ID:-->

            <deb:ShipToSiteUseId>132298316</deb:ShipToSiteUseId>

            <!--SHIP_TO_PARTY_SITE_USE_ID:-->

            <deb:TrxDate>2024-02-12</deb:TrxDate>

            <deb:DebitMemoLine>

               <deb:LineNumber>1</deb:LineNumber>

               <deb:Description>test1 debit memo service</deb:Description>

               <deb:InvoicedQuantity unitCode="">1</deb:InvoicedQuantity>

               <deb:UnitSellingPrice currencyCode="USD">100</deb:UnitSellingPrice>

               <deb:LineType>LINE</deb:LineType>

               <deb:ExtendedAmount currencyCode="USD">100</deb:ExtendedAmount>

               <deb:RevenueAmount currencyCode="USD">100</deb:RevenueAmount>

               <deb:OrgId>33059258</deb:OrgId>

            </deb:DebitMemoLine>

            <deb:DebitMemoLine>

               <deb:LineNumber>2</deb:LineNumber>

               <deb:Description>test2 debit memo service</deb:Description>

               <deb:InvoicedQuantity unitCode="">1</deb:InvoicedQuantity>

               <deb:UnitSellingPrice currencyCode="USD">100</deb:UnitSellingPrice>

               <deb:LineType>LINE</deb:LineType>

               <deb:ExtendedAmount currencyCode="USD">500</deb:ExtendedAmount>

               <deb:RevenueAmount currencyCode="USD">500</deb:RevenueAmount>

               <deb:OrgId>33059258</deb:OrgId>

            </deb:DebitMemoLine>

         </typ:debitMemo>

      </typ:createDebitMemo>

   </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)

FA:SQL: Query to get inventory organization/inventory organization location and business unit and Legal entity

SELECT iod.organization_code,
       iod.organization_name,
       iod.business_unit_name,
       iod.business_unit_id,
       iod.legal_entity,
       xep.NAME legal_entity_name,
       hla.location_code,
       hla.location_name,
       hla.internal_location_code ,
              Concat(Concat(Concat(Concat(Concat(Concat( Concat(Concat(hla.address_line_1
              || ', ', Nvl2(hla.address_line_2, hla.address_line_2
              || ', ', hla.address_line_2)), Nvl2(hla.address_line_3, hla.address_line_3
              || ', ', hla.address_line_3)), Nvl2(hla.address_line_4, hla.address_line_4
              || ', ', hla.address_line_4)), Nvl2(hla.town_or_city, hla.town_or_city
              || ', ', hla.town_or_city)), Nvl2(hla.region_1, hla.region_1
              || ', ', hla.region_1)), Nvl2(hla.region_2, hla.region_2
              || ', ', hla.region_2)), Nvl2(hla.country, country
              || ', ', hla.country)), hla.postal_code) concatenated_address,
       iod.inventory_enabled_flag,
       iod.inventory_flag,
       iod.distributed_organization_flag,
       iod.profit_center_bu_id,
       iod.mfg_plant_flag,
       iod.contract_mfg_flag,
       iod.eam_enabled_flag,
       iod.timezone_code
 FROM inv_organization_definitions_v iod,
      hr_locations_all hla,
      xle_entity_profiles xep
WHERE iod.location_id = hla.location_id
  AND iod.legal_entity = xep.legal_entity_id
  AND NVL(iod.disable_date, sysdate+1) > sysdate;

Fusion: SQL: Query to Extract the Business Units and its related Inventory Organizations, Business Units and Costing Organizations

 

 

SELECT hou.organization_id
       , hou.name organization_name
       , iop.organization_code organization_code
       , lgr.ledger_id set_of_book_id
       , lgr.chart_of_accounts_id chart_of_account_id
       , lgr.currency_code
       , lgr.period_set_name
       , Decode(hoi.status, 'A', 'Y', 'N') inv_enabled_flag
       , bu.bu_name business_unit_name
       , iop.business_unit_id business_unit_id
       , iop.legal_entity_id legal_entity
       , hou.type organization_type
  FROM hr_all_organization_units_x hou
       , hr_org_unit_classifications_x hoi
       , inv_org_parameters iop
       , gl_ledgers lgr
       , fun_all_business_units_v bu
 WHERE hou.organization_id = hoi.organization_id
   AND hou.organization_id = iop.organization_id
   AND hoi.classification_code = 'INV'
   AND bu.primary_ledger_id = lgr.ledger_id(+)
   AND lgr.object_type_code(+) = 'L'
   AND NVL(lgr.complete_flag, 'Y') = 'Y'
   AND bu.bu_id(+) = iop.business_unit_id;  

SQL : Get Business Unit Details

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

 SELECT hauft.NAME BusinessUnit, hauft.organization_id
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date    AND houcf.effective_end_date
   AND hauft.language = 'US'
   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 sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first ; 

SQL : Get Costing Organization Details

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

 SELECT hauft.NAME BusinessUnit
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   AND hauft.language = 'US'
   AND hauft.effective_start_date = haouf.effective_start_date
   AND hauft.effective_end_date = haouf.effective_end_date
   AND houcf.classification_code = 'CST'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first; 

FA:SCM:CST: SQL to Get Item Standard Cost

 SELECT houft.name cost_organization_name
       , esi.item_number
       , csc.total_cost
       , uom.uom_code
       , cvu.val_unit_code
       , cvs.val_structure_code
       , cvs.val_structure_type_code
       , ccb.cost_book_code
  FROM cst_std_costs csc
       , cst_val_units_b cvu
       , cst_val_structures_b cvs
       , egp_system_items_vl esi
       , cst_cost_org_parameters ccop
       , hr_organization_units_f_tl houft
       , cst_cost_books_b ccb
       , inv_units_of_measure_vl uom
 WHERE csc.cost_book_id = ccb.cost_book_id
   AND csc.cost_org_id = houft.organization_id
   AND ccop.cost_org_id = houft.organization_id
   AND cvu.val_structure_id = cvs.val_structure_id
   AND csc.val_unit_id = cvu.val_unit_id
   AND esi.inventory_item_id = csc.inventory_item_id
   AND esi.organization_id = ccop.master_organization_id
   AND houft.LANGUAGE = Userenv('LANG')
   AND csc.uom_code = uom.uom_code
   AND csc.status_code = 'PUBLISHED'
   AND esi.item_number = :p_item_number
   AND houft.name = :p_cost_org_name
   AND trunc(nvl(csc.effective_end_date,sysdate+1)) > trunc(sysdate);

Fusion:SCM:MFG: SQL to Validat the Componenet Issue and WO Completion transactions against Work Order

SELECT imt.transaction_id,iop.organization_code warehouse_code
     , TO_CHAR(imt.creation_date,'DD-MON-YYYY') txn_creation_date
     , ittv.transaction_type_name
     , flva.meaning transaction_action
     , itst.transaction_source_type_name
     , esi.item_number
     , imt.subinventory_code
     , imt.transaction_quantity
     , imt.transaction_uom
     , imt.transaction_date
     , imt.transaction_source_name     
     , iut.serial_number   
  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
 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 UPPER(ittv.transaction_type_name) IN ('WORK IN PROCESS MATERIAL ISSUE', 'WORK IN PROCESS PRODUCT COMPLETION')
   AND UPPER(itst.transaction_source_type_name) = 'WORK ORDER'
   AND esi.item_number = NVL(:p_item_number,esi.item_number)
   AND iop.organization_code = NVL(:p_org_code,iop.organization_code)
   AND imt.transaction_source_name = NVL(:p_wo_number, imt.transaction_source_name)
   AND esi.item_number = NVL(:p_item_number, esi.item_number)
   ORDER BY imt.creation_date desc;