Oracle Fusion: Distributed Order Orchestration (Sales Order) Lines Query

 Below is the query to get Distributed Order Orchestration (Sales Order) line informations along with its EFF:

SELECT dla.header_id
     , dla.line_id
     , dla.display_line_number
     , dla.source_line_id
     , dla.source_line_number
     , dla.source_order_number
     , dla.source_order_system
     , dla.ordered_qty
     , dla.canceled_qty
     , dla.ordered_uom
     , dla.source_org_id
     , dla.org_id
     , dla.extended_amount
     , dla.line_type_code
     , dla.open_flag
     , dla.canceled_flag
     , dla.shipped_qty
     , dla.inventory_item_id
     , dla.inventory_organization_id
     , dla.unit_list_price
     , dla.unit_selling_price
     , dfla.fulfill_line_id
     , dfla.status_code
     , dfla.priced_on
     , dfla.request_ship_date
     , dfla.schedule_ship_date
     , dfla.packing_instructions
     , dfla.earliest_acceptable_ship_date
     , dfla.bill_to_customer_id
     , dfla.bill_to_site_use_id
     , dfleb.eff_line_id
     , dfleb.context_code
     , dfleb.attribute_char1
     , dfleb.attribute_char2
     , dfleb.attribute_char3
     , dfleb.attribute_char4
     , dfleb.attribute_char5
     , dfleb.attribute_char6
     , dfleb.attribute_char7
     , dfleb.attribute_char8
     , dfleb.attribute_char9
     , dfleb.attribute_char10
     , dfleb.attribute_char11
     , dfleb.attribute_char12
     , dfleb.attribute_char13
     , dfleb.attribute_char14
     , dfleb.attribute_char15
     , dfleb.attribute_char16
     , dfleb.attribute_char17
     , dfleb.attribute_char18
     , dfleb.attribute_char19
     , dfleb.attribute_char20
     , dla.created_by
     , TO_CHAR (FROM_TZ (dla.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , dla.last_updated_by
     , TO_CHAR (FROM_TZ (dla.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM doo_lines_all dla, doo_fulfill_lines_all dfla, doo_fulfill_lines_eff_b dfleb
 WHERE dla.line_id = dfla.line_id
   AND dfla.fulfill_line_id = dfleb.fulfill_line_id(+)

Oracle Fusion: Distributed Order Orchestration (Sales Order) Headers Query

 Below is the query to get Distributed Order Orchestration (Sales Order) header informations along with its EFF:

SELECT dha.header_id
     , dha.order_number
     , dha.customer_po_number
     , dha.source_order_system
     , dha.source_order_number
     , dha.source_order_id
     , dha.sold_to_party_id
     , dha.transactional_currency_code
     , TO_CHAR (dha.ordered_date, 'RRRR-MM-DD HH24:MI:SS') ordered_date
     , dha.org_id bu_id
     , dha.order_type_code
     , dha.legal_entity_id
     , dha.freeze_tax_flag
     , dha.freeze_price_flag
     , dha.freeze_shipping_charge_flag
     , TO_CHAR (dha.priced_on, 'RRRR-MM-DD HH24:MI:SS') priced_on
     , dha.submitted_flag
     , dha.status_code
     , dha.change_version_number
     , dheb.context_code
     , dheb.attribute_char1
     , dheb.attribute_char2
     , dheb.attribute_char3
     , dheb.attribute_char4
     , dheb.attribute_char5
     , dheb.attribute_char6
     , dheb.attribute_char7
     , dheb.attribute_char8
     , dheb.attribute_char9
     , dheb.attribute_char10
     , dheb.attribute_char11
     , dheb.attribute_char12
     , dheb.attribute_char13
     , dheb.attribute_char14
     , dheb.attribute_char15
     , dha.created_by
     , TO_CHAR (FROM_TZ (dha.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , dha.last_updated_by
     , TO_CHAR (FROM_TZ (dha.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM doo_headers_all dha, doo_headers_eff_b dheb
 WHERE dha.header_id = dheb.header_id(+)

Oracle Fusion: Query to Get Item Cost per Transactions (Layer Cost)

Below is the query to get item transaction cost in oracle fusion which can be seen in Review Cost Accounting Distributions form.

SELECT imt.transaction_id
     , imt.transaction_interface_id
     , imt.source_code
     , imt.source_line_id
     , cit.external_system_reference
     , cit.subinventory_code
     , cit.locator_id
     , TO_CHAR (FROM_TZ (ct.transaction_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') transaction_date
     , ct.cost_transaction_type
     , TO_CHAR (FROM_TZ (ct.cost_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') cost_date
     , ct.use_item_cost_flag
     , TO_CHAR (FROM_TZ (clc.eff_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') eff_date
     , clc.layer_cost_id
     , clc.cost_source
     , clc.cost_reference
     , clc.quantity
     , clc.uom_code
     , clc.unit_cost
     , clc.currency_code
     , clc.posted_flag
     , clc.created_by
     , TO_CHAR (FROM_TZ (clc.creation_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') creation_date
     , clc.last_updated_by
     , TO_CHAR (FROM_TZ (clc.last_update_date, DBTIMEZONE) AT TIME ZONE 'Asia/Jakarta'
              , 'RRRR-MM-DD HH24:MI:SS') last_update_date
  FROM cst_layer_costs clc
     , cst_transactions ct
     , cst_inv_transactions cit
     , inv_material_txns imt
 WHERE cit.external_system_ref_id = imt.transaction_id
   AND cit.EXTERNAL_SYSTEM_REFERENCE = 'FUSION'
   AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id
   AND clc.transaction_id = ct.transaction_id
--   AND imt.transaction_id = 1257380

As for average cost, you can query it from CST_PERPAVG_COST table.

Oracle Fusion Cloud: Query to Get Assigned Roles of a User

Below is query to get roles assigned to user(s). You can define username by using p_user_name parameter while running the query, or leave it empty to get all user's roles.

  SELECT pu.username
       , NAME.full_name
       , prdv.role_id
       , prdv.role_name
       , TO_CHAR (TRUNC (pur.start_date), 'MM/DD/YYYY') role_start_date
       , ppnf.effective_end_date
    FROM per_user_roles pur
       , per_users pu
       , per_roles_dn_vl prdv
       , per_person_names_f ppnf
   WHERE pur.user_id = pu.user_id
     AND pu.person_id = NAME.person_id(+)
     AND pur.role_id = prdv.role_id
     AND NVL (pu.suspended, 'N') = 'N'
     AND NAME.name_type = 'GLOBAL'
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppnf.effective_start_date) AND TRUNC (ppnf.effective_end_date)
     AND pu.username = nvl(:p_user_name, pu.username)
ORDER BY pu.username
       , prdv.role_name