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>';