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)

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;

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;

Fusion: SCM: SQL to get the RMA receipts and order number details

 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.primary_quantity
       , imt.transaction_date
       , imt.transaction_source_id
       , imt.transaction_source_name
       , imt.rcv_transaction_id
       , imt.distribution_account_id
       , imt.trx_source_line_id
       , imt.trx_source_delivery_id
       , imt.rma_line_id
       , imt.transfer_transaction_id
       , imt.transaction_set_id
       , imt.source_code
       , imt.source_line_id
       , imt.transfer_organization_id
       , imt.transfer_subinventory
       , imt.shipment_number
       , iut.serial_number
       , rsh.receipt_num rma_receipt_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
       , 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_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.header_id = dha.header_id
   and dha.submitted_flag = 'Y'
   AND dha.order_number = :p_order_number
 ORDER BY imt.creation_date DESC

Oracle Fusion SQL: SQL to Extract the Sales order Orchestraction Steps with each Step Status details

    SELECT  h.source_order_number
     , h.header_id
     , f.fulfill_line_id
     , f.source_line_number
     , f.source_line_id
     , pd.process_name
     , psvl.step_id
     , psvl.step_number
     , psvl.parent_step_number
     , psvlt.step_name
     , psi.step_instance_id
     , psi.group_id
     , psvl.step_type
     , psi.step_active
     , psi.step_status
     , psi.task_instance_id
     , nvl(to_char(psi.actual_start_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "actual_start_Date"
     , nvl(to_char(psi.actual_completion_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "actual_completion_Date"
     , nvl(to_char(psi.planned_start_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "planned_start_Date"
     , nvl(to_char(psi.planned_completion_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "planned_completion_Date"
     , nvl(to_char(psi.required_start_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "required_start_Date"
     , nvl(to_char(psi.required_completion_date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "required_completion_Date"
     , psi.last_updated_by
  FROM fusion.doo_headers_all h
     , fusion.doo_fulfill_lines_all f
     , fusion.doo_orchestration_groups g
     , fusion.doo_process_steps_b psvl
     , fusion.doo_process_definitions_b pd
     , fusion.doo_process_steps_tl psvlt
     , fusion.doo_process_step_instances psi
 WHERE 1 = 1  
   AND h.submitted_flag = 'Y'
   AND h.header_id = f.header_id
   AND f.header_id = g.header_id
   AND g.status = 'ACTIVE'
   AND f.fulfill_line_id = g.fulfillment_line_id
   AND g.doo_process_instance_id = psi.doo_process_instance_id
   AND psi.step_id = psvl.step_id
   AND psvl.step_id = psvlt.step_id
   AND psvl.doo_process_id = pd.doo_process_id
   AND psvlt.language = userenv('LANG')
   AND H.ORDER_NUMBER = :p_order_number
 ORDER BY h.source_order_number
        , LPAD(f.source_line_number, 2)
        , f.fulfill_line_id
        , psvl.doo_process_id
        , psvl.step_number;

Oracle Fusion SQL : SQL to Extract Transfer Order details

SELECT d.header_number
       , e.line_number
       , so.organization_code source_inv_org
       , do.organization_code dest_inv_org
       , sku.item_number
       , e.requested_qty
       , e.shipped_qty
       , e.received_qty
       , e.delivered_qty
       , e.source_subinventory_code source_subinventory
       , e.destination_subinventory_code dest_subinventory
       , d.fulfill_orchestration_required
       , d.source_type_lookup  hdr_source_type_lookup
       , e.source_organization_id
       , e.destination_organization_id
       , e.line_id
       , e.interface_status_lookup
       , e.source_type_lookup
       , e.destination_type_lookup
       , e.destination_location_id
       , e.status_lookup
       , e.need_by_date
       , e.scheduled_ship_date
       , e.qty_uom_code
       , e.unit_price
       , e.req_bu_id
       , e.supply_order_reference_number
       , e.supply_order_ref_line_number
       , d.header_id
       , sku.inventory_item_id
       , d.creation_date
  FROM fusion.inv_transfer_order_headers d
       , fusion.inv_transfer_order_lines e
       , fusion.inv_org_parameters so
       , fusion.inv_org_parameters do
       , fusion.egp_system_items_b sku
 WHERE 1 = 1
   AND sku.inventory_item_id = e.inventory_item_id
   AND sku.organization_id = e.source_organization_id
   AND do.organization_id = e.destination_organization_id
   AND so.organization_id = e.source_organization_id
   AND d.header_id = e.header_id
   AND so.organization_code = :p_from_organization
   AND do.organization_code = :p_to_organization
   AND sku.item_number = NVL(:p_part_number, sku.item_number)
   AND e.supply_order_reference_number = Nvl(:p_supply_order, e.supply_order_reference_number)
 ORDER BY d.creation_date DESC
          , 1
          , Lpad(e.line_number, 2);

Oracle Fusion: SQL to extract Inventory Organization Parameters Details

 

SELECT a.organization_id,
       a.organization_code,
       g.name organization_name,
       a.master_organization_id,
       b.organization_code master_organization_code,
       a.business_unit_id,
       a.legal_entity_id,
       a.negative_inv_receipt_code,
       e.meaning neg_meaning,
       a.stock_locator_control_code,
       c.meaning stock_locator_meaning,
       a.serial_number_type,
       d.meaning serial_number_type_meaning,
       a.lot_number_uniqueness,
       f.meaning lot_uniq_meaning
FROM inv_org_parameters a,
     inv_org_parameters b,
     fnd_lookups c,
     fnd_lookups d,
     fnd_lookups e,
     fnd_lookups f,
     hr_organization_units_f_tl g
WHERE a.master_organization_id = b.organization_id
  AND c.lookup_type = 'INV_LOCATION_CONTROL'
  AND a.stock_locator_control_code = c.lookup_code (+)
  AND d.lookup_type = 'INV_SERIAL_NUMBER'
  AND a.serial_number_type = d.lookup_code (+)
  AND e.lookup_type = 'INV_YES_NO_NUMERIC'
  AND a.negative_inv_receipt_code = e.lookup_code (+)
  AND f.lookup_type = 'INV_LOT_UNIQUENESS'
  AND a.lot_number_uniqueness = f.lookup_code (+)
  AND g.organization_id = a.organization_id
  AND g.language = 'US'
ORDER BY a.organization_id

Oralce Fusion: SQL to Get Concatenated Address example

Inventory Organization Location

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

SELECT z.organization_code,
       z.organization_name,
       b.location_id,
       b.active_status,
       b.effective_start_date,
       b.effective_end_date,
       b.internal_location_code,
       b.location_use,
       b.location_code,
       b.location_name,
       b.description,
       b.style,
       Concat(Concat(Concat(Concat(Concat(Concat(
                     Concat(Concat(b.address_line_1 || ', ',
                     Nvl2(b.address_line_2, b.address_line_2 || ', ', b.address_line_2)),
                     Nvl2(b.address_line_3, b.address_line_3 || ', ', b.address_line_3)),
                     Nvl2(b.address_line_4, b.address_line_4 || ', ', b.address_line_4)),
                     Nvl2(b.town_or_city, b.town_or_city || ', ', b.town_or_city)),
                     Nvl2(b.region_1, b.region_1 || ', ', b.region_1)),
                     Nvl2(b.region_2, b.region_2 || ', ', b.region_2)),
                     Nvl2(b.country, country || ', ', b.country)), b.postal_code) concatenated_address
FROM   inv_organization_definitions_v z,
       hr_locations b
WHERE  z.location_id = b.location_id;

 

Party/Customer/HZ Location

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

SELECT  concat(concat(concat(concat(concat(concat(concat(concat(address1||', ',NVL2(address2,address2||', ',address2)),NVL2(address3,address3||', ',address3)),NVL2(address4,address4||', ',address4)),NVL2(city,city||', ',city)),NVL2(county,county||', ',county)),NVL2(state,state||', ',state)),NVL2(province, province||', ',province)),postal_code) concatenated_address
  FROM hz_locations
 WHERE country ='US';  

SELECT hzp.party_name
       || ' '
       || hzp.party_number,
       hzp.party_id,
       hzps.party_site_number "ShipToPartySiteNumber",
       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,
     concat(concat(concat(concat(concat(concat(concat(concat(hzl.address1||', ', NVL2(hzl.address2, hzl.address2||', ', hzl.address2)), NVL2(hzl.address3, hzl.address3||', ', hzl.address3)), NVL2(hzl.address4, hzl.address4||', ', hzl.address4)), NVL2(hzl.city, hzl.city||', ', hzl.city)), NVL2(hzl.county, hzl.county||', ', hzl.county)), NVL2(hzl.state, hzl.state||', ', hzl.state)), NVL2(hzl.province, hzl.province||', ', hzl.province)), hzl.postal_code) concatenated_address,
       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 hzp.party_number = :p_party_number
       AND hzcsua.primary_flag = 'Y'
ORDER  BY hzp.party_number,
          hza.account_number,
          hzl.location_id

Oracle Fusion:ESS: SQL to Search Privileges required to run a ESS job

 Try using below SQL to search required Priviliges to run a specific ESS job in Oracle Fusion when user find below error message while submitting the ESS job.

ESS-02002: User 'XYZ' doesnot have sufficient priviliges to perform operation submitrequest 'ess job name'

SELECT p.code privilege
     , t.name
     , perm.code code
     , perm.resource_type_name
     , perm.action
  FROM fusion.ase_privilege_b p
     , fusion.ase_permission_b perm
     , fusion.ase_privilege_tl t
 WHERE p.privilege_id = perm.privilege_id
   AND p.privilege_id = t.privilege_id (+)
   AND t.language = 'US'
   AND sysdate BETWEEN p.effective_start_date AND nvl(p.effective_end_date, sysdate)
   AND sysdate BETWEEN perm.effective_start_date AND nvl(perm.effective_end_date, sysdate)
   AND perm.code LIKE '%'|| :p_ess_job_name|| '%'

FA:OM:SQL To List EFF Segments and attribute Columns mapping for Specific Context

 SELECT DISTINCT fdcb.context_code
              , fdcb.context_identifier
              , fdcb.enabled_flag
              , fdsb.segment_code
              , fdsb.segment_identifier
              , fdsb.column_name
  FROM fusion.fnd_df_contexts_tl fdct
     , fusion.fnd_df_contexts_b fdcb
     , fusion.fnd_df_segments_tl fdst
     , fusion.fnd_df_segments_b fdsb
 WHERE fdct.context_code = fdcb.context_code
   AND fdcb.context_code = fdsb.context_code
   AND fdst.context_code = fdsb.context_code
   AND fdst.segment_code = fdsb.segment_code
   AND fdct.application_id = fdcb.application_id
   AND fdct.language = userenv('Lang')
   AND fdst.language = userenv('Lang')
   AND fdct.descriptive_flexfield_code = 'DOO_FULFILL_LINES_ADD_INFO'
   AND fdcb.context_code = '<Context_Code>'
 ORDER BY fdcb.context_identifier
        , fdsb.segment_identifier

FA:Query - List of Assigned Roles For a Given User

SELECT ase_user_vl.user_id user_id
     , ase_user_vl.user_login user_login
     , ase_user_vl.user_display_name user_display_name
     , ase_role_vl.code code
     , ase_role_vl.role_name role_name
     , ase_role_vl.description description
     , ase_user_role_mbr.role_id role_id
  FROM fusion.ase_user_vl
     , fusion.ase_role_vl
     , fusion.ase_user_role_mbr
 WHERE ase_user_vl.user_id = ase_user_role_mbr.user_id
   AND ase_user_role_mbr.role_id = ase_role_vl.role_id
   AND ase_user_vl.effective_end_date IS NULL
   AND ase_role_vl.effective_end_date IS NULL
   AND ase_user_role_mbr.effective_end_date IS NULL
   AND upper(user_login) = upper('&user_name')
 ORDER BY role_name

FA:SQL:Manage Data Access for Users

 SELECT username
     , role
     , security_context
     , security_context_value
  FROM ( SELECT pu.username
              , prd.role_name role
              , 'Data Access Set' security_context
              , gl.name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.gl_access_sets gl
            , fusion.per_users pu
        WHERE gl.access_set_id = role.access_set_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Business Unit' security_context
            , bu.bu_name security_context_value
         FROM fusion.fun_all_business_units_v bu
            , fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.per_users pu
        WHERE role.org_id = bu.bu_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Ledgers' security_context
            , led.name security_context_value
         FROM fusion.gl_ledgers led
            , fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.per_users pu
        WHERE role.ledger_id = led.ledger_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Asset Book' security_context
            , book.book_type_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fa_book_controls book
            , fusion.per_users pu
        WHERE book.book_control_id = role.book_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Intercompany Organization' security_context
            , interco.interco_org_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fun_interco_organizations interco
            , fusion.per_users pu
        WHERE interco.interco_org_id = role.interco_org_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Cost Organization' sercurity_context
            , cost.cost_org_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.cst_cost_orgs_v cost
            , fusion.per_users pu
        WHERE cost.cost_org_id = role.cst_organization_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Manufacturing Plant' security_context
            , iop.organization_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.rcs_mfg_parameters mfg
            , inv_org_parameters iop
            , fusion.per_users pu
        WHERE mfg.organization_id = role.mfg_organization_id
          AND mfg.organization_id = iop.organization_id        
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Control Budget' security_context
            , budget.name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.xcc_control_budgets budget
            , fusion.per_users pu
        WHERE budget.control_budget_id = role.control_budget_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Reference data Set' security_context
            , st.set_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fnd_setid_sets_vl st
            , fusion.per_users pu
        WHERE st.set_id = role.set_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Inventory Organization' security_context
            , inv.organization_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.inv_org_parameters inv
            , fusion.per_users pu
        WHERE inv.organization_id = role.inv_organization_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Project Organization Classification' security_context
            , hr.classification_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.hr_org_unit_classifications_f hr
            , fusion.per_users pu
        WHERE hr.org_unit_classification_id = role.org_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid)
 WHERE username = :p_user_name
 ORDER BY 3
        --, 4

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;

FA:MFG: SQL's to extract Details on Manage Work Area/Resources/Work Centers/Standard Operations

   --Manage work Area
   SELECT iop.organization_code
        , wwav.work_area_name
        , wwav.work_area_code
        , wwav.work_area_description
     FROM fusion.wis_work_areas_vl wwav
        , fusion.inv_org_parameters iop
    WHERE wwav.organization_id = iop.organization_id
      AND iop.organization_code = '<inv_org_code>';

   -- Manage Resources
   SELECT resource_name
        , resource_code
        , resource_description
        , resource_type
        , resource_class_code
        , uom_code
        , costed_flag
     FROM fusion.wis_resources_vl wrv
        , fusion.inv_org_parameters iop
    WHERE wrv.organization_id = iop.organization_id
      AND iop.organization_code = '<inv_org_code>'
    ORDER BY 1;

   --Manage Work Centers
   SELECT wwc.work_center_name
        , wwc.work_center_code
        , wwc.work_center_description
        , wwav.work_area_name
        , resource_name
        , wwr.resource_quantity
        , wwr.available_24_hours_flag
        , wwr.check_ctp_flag
        , wwr.utilization_percentage
        , wwr.efficiency_percentage
     FROM fusion.wis_work_centers_vl wwc
        , fusion.wis_work_areas_vl wwav
        , fusion.wis_wc_resources wwr
        , fusion.wis_resources_vl wrv
        , fusion.inv_org_parameters iop
    WHERE wwc.work_area_id = wwav.work_area_id
      AND wwc.work_center_id = wwr.work_center_id
      AND wwr.resource_id = wrv.resource_id
      AND wwc.organization_id = iop.organization_id
      AND iop.organization_code = '<inv_org_code>'
    ORDER BY 1;

-- Manage Standard Operations 
SELECT iop.organization_id
     , iop.organization_code
     , flv.meaning operation_type
     , wwo.standard_operation_name
     , wwo.standard_operation_code
     , wwo.standard_operation_description
     , wwc.work_center_name
     , wwc.work_center_code
     , wwc.work_center_description
     , wwo.count_point_flag
     , wwo.auto_transact_flag
     , wwo.inactive_date
     , wwo.used_in_auto_wd_flag
     , flvai.meaning addl_mtl_at_manual_issue
     , flvoi.meaning  op_compl_with_under_issue
     , flvoe.meaning  op_compl_with_open_exceptions
     , wsor.resource_seq_number
     , wwr.resource_name
     , wsor.assigned_units
     , flvb.meaning basis
     , wsor.usage_rate
     , wsor.inverse_usage_rate
     , wsor.uom_code
     , flvs.meaning schedule_type
     , wsor.principal_flag
     , flvc.meaning charge_type
  FROM fusion.wis_standard_operations_vl wwo
     , fusion.wis_std_operation_resources wsor
     , fusion.wis_resources_vl wwr
     , fusion.fnd_lookup_values_vl flvai     
     , fusion.fnd_lookup_values_vl flvoi     
     , fusion.fnd_lookup_values_vl flvoe
     , fusion.fnd_lookup_values_vl flvc     
     , fusion.fnd_lookup_values_vl flvs     
     , fusion.fnd_lookup_values_vl flvb
     , fusion.wis_work_centers_vl wwc
     , fusion.fnd_lookup_values_vl flv
     , fusion.inv_org_parameters iop
 WHERE wwo.standard_operation_id = wsor.standard_operation_id
   AND wsor.resource_id = wwr.resource_id
   AND flvc.enabled_flag = 'Y'
   AND flvc.lookup_code = wsor.charge_type
   AND flvc.lookup_type = 'ORA_WIS_CHARGE_TYPE'    
   AND flvs.enabled_flag = 'Y'
   AND flvs.lookup_code = wsor.schedule_type
   AND flvs.lookup_type = 'ORA_WIS_RESOURCE_SCHEDULE'   
   AND flvb.enabled_flag = 'Y'
   AND flvb.lookup_code = wsor.basis_type
   AND flvb.lookup_type = 'EGP_BOM_BASIS_TYPE'
   AND wwc.work_center_id = wwo.work_center_id
   AND flvai.enabled_flag = 'Y'
   AND flvai.lookup_code = wwo.addl_mtl_at_manual_issue
   AND flvai.lookup_type = 'ORA_WIS_ADDL_MANUAL_ISSUE_OPTS'
   AND flvoi.enabled_flag = 'Y'
   AND flvoi.lookup_code = wwo.op_compl_with_under_issue
   AND flvoi.lookup_type = 'ORA_WIS_OP_COMPLETION_VAL_OPTS'
   AND flvoe.enabled_flag = 'Y'
   AND flvoe.lookup_code = wwo.op_compl_with_open_exceptions
   AND flvoe.lookup_type = 'ORA_WIS_OP_COMPLETION_VAL_OPTS'   
   AND flv.enabled_flag = 'Y'
   AND flv.lookup_code = wwo.operation_type
   AND flv.lookup_type = 'ORA_WIS_OPERATION_TYPE'
   AND wwo.organization_id = iop.organization_id
   AND iop.organization_code = '<inv_org_code>';

FA:SQL: Oralce Fusion Manufacturing Manage Scheduels/Schedule Exceptions/ Work Order Definition

 REM Manage Schedules
SELECT DISTINCT cal.schedule_name
              , zssp.schedule_id calendar_code
              , zssv.shift_id shift_num
              , zssv.shift_name
              , zssv.shift_desc description
              , zssv.start_time_ms_num from_time
              , zssv.end_time_ms_num to_time
              , zssv.shift_type_code
  FROM fusion.zmm_sr_shifts_vl zssv
     , fusion.zmm_sr_pattern_dtls zspd
     , fusion.zmm_sr_schedule_patterns zssp
     , fusion.zmm_sr_schedules_vl cal
 WHERE zspd.pattern_id (+) = zssp.pattern_id
   AND zspd.child_shift_id = zssv.shift_id
   AND zssp.schedule_id = cal.schedule_id;
   
 REM Manage Schedule exceptions
SELECT cal.schedule_name
     , zssae.schedule_id calendar_code
     , zssae.avl_exception_id
     , zsaev.avl_excp_name
     , zsaev.avl_excp_desc
     , zsaev.start_date_time
     , zsaev.end_date_time
     , zsaev.short_txt
     , zsaev.category_code
     , zsaev.whole_day_flag
     , zsaev.availability_code
  FROM fusion.zmm_sr_sched_avl_excps zssae
     , fusion.zmm_sr_avl_exceptions_vl zsaev
     , fusion.zmm_sr_schedules_vl cal
 WHERE zsaev.avl_exception_id (+) = zssae.avl_exception_id
   AND zssae.schedule_id = cal.schedule_id
   AND cal.schedule_name = 'US Planning Schedules';

REM Work Order Definitions
SELECT a.organization_id
     , iop.organization_code
     , b.inventory_item_id
     , b.item_number
     , f.alternate_bom_designator
     , d.work_definition_name_id
     , d.work_def_name
     , d.work_def_name_description
     , d.work_definition_code
     , d.work_definition_type
     , e.meaning
     , a.object_version_number
     , a.production_priority
     , a.costing_priority
     , a.work_definition_id
     , a.status_code
     , a.work_method_id
     , c.work_method_name
     , c.work_method_code
  FROM fusion.wis_work_definitions a
     , fusion.egp_system_items_b b
     , fusion.wis_work_methods_vl c
     , fusion.wis_work_definition_names_vl d
     , fusion.FND_LOOKUP_VALUES_VL e
     , fusion.egp_structures_b f
     , fusion.inv_org_parameters iop
 WHERE a.organization_id = b.organization_id
   AND a.inventory_item_id = b.inventory_item_id
   AND a.work_method_id = c.work_method_id
   AND a.organization_id = iop.organization_id
   AND iop.organization_code = 'FHK'
   and a.work_definition_name_id = d.work_definition_name_id
   and e.lookup_type = 'ORA_WIS_WORK_DEFINITION_TYPE'
   and e.lookup_code = d.work_definition_type
   and e.enabled_flag = 'Y'
   and a.bill_sequence_id = f.bill_sequence_id;