EBS 常用SQL总结

--用SQL查询各Profile的设置情况
SELECT pro.profile_option_name,
       pro.user_profile_option_name,
       lev.level_type TYPE,
       --lev.level_code,
       lev.level_name,
       prv.profile_option_value
FROM apps.fnd_profile_options_vl pro,
     applsys.fnd_profile_option_values prv,
     (SELECT 10001 level_id,
             'Site' level_type,
             0 level_value,
             'Site' level_code,
             'Site' level_name
      FROM dual
      UNION ALL
      SELECT 10002 level_id,
             'App' level_type,
             app.application_id level_value,
             app.application_short_name level_code,
             app.application_name level_name
      FROM apps.fnd_application_vl app
      UNION ALL
      SELECT 10003 level_id,
             'Resp' level_type,
             resp.responsi bility_id level_value,
             resp.responsibility_key level_code,
             resp.responsibility_name level_name
      FROM apps.fnd_responsibility_vl resp
      UNION ALL
      SELECT 10004 level_id,
             'User' level_type,
             usr.user_id level_value,
             usr.user_name level_code,
             usr.user_name level_name
      FROM applsys.fnd_user usr) lev
WHERE pro.profile_option_id = prv.profile_option_id(+)
AND prv.level_id = lev.level_id(+)
AND prv.level_value = lev.level_value(+)
     --参数Profile，下面连个条件用一个即可
AND pro.user_profile_option_name LIKE 'CUX%' --Profile名称
ORDER BY pro.profile_option_name, lev.level_type, lev.level_name;

--Purpose  To verify the ABC Assignment Groups defined.
--Description  This query will fetch and list the ABC Assignment Groups for all inventory organizations defined in the system.
SELECT haou.NAME "ORGANIZATION_NAME"
      ,mp.organization_code
      ,maag.assignment_group_name "GROUP"
      ,maag.compile_name
      ,maag.secondary_inventory "SU BINVENTORY"
      ,mac.abc_class_name "CLASS_NAME"
      ,to_char (trunc (maag.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_abc_assignment_groups_v maag
      ,mtl_abc_assgn_group_classes_v magc
      ,mtl_abc_classes_v mac
      ,mtl_parameters mp
      ,hr_all_organization_units haou
WHERE      mp.organization_id = haou.organization_id
       AND mp.organization_id = maag.organization_id
       AND mp.organization_id = magc.organization_id
       AND mp.organization_id = mac.organization_id
       AND maag.assignment_group_id = magc.assignment_group_id
       AND magc.abc_class_id = mac.abc_class_id
ORDER BY 2, 3
--Purpose  To verify the Shipping Networks defined.
--Description  This query will fetch and list the shipping networks defined in the system.
SELECT from_organization_code
      ,from_organization_name
      ,to_organization_code
      ,to_organization_name
      ,intransit_type
      ,fob_point
      ,routing_header_id
      , (SELECT    gcc.segment1
                || '-'
                || gcc.segment2
                || '-'
                || gcc.segment3
                || '-'
                || gcc.segment4
                || '-'
                || gcc.segment5
                || '-'
                || gcc.segment6
         FROM   gl_code_combinations gcc
         WHERE  gcc.code_combination_id = interorg_transfer_cr_account)
          "TRANSFER_CR_ACCOUNT"
      , (SELECT    gcc.segment1
                || '-'
                || gcc.segment2
                || '-'
                || gcc.segment3
                || '-'
                || gcc.segment4
                || '-'
                || gcc.segment5
                || '-'
                || gcc.segment6
         FROM   gl_code_combinations gcc
         WHERE  gcc.code_combination_id = interorg_price_var_account)
          "PRICE_VARIANCE_ACCOUNT"
      , (SELECT    gcc.segment1
                || '-'
                || gcc.segment2
                || '-'
                || gcc.segment3
                || '-'
                || gcc.segment4
                || '-'
                || gcc.segment5
                || '-'
                || gcc.segment6
         FROM   gl_code_combinations gcc
         WHERE  gcc.code_combination_id = interorg_receivables_account)
          "INTER_ORG_RECEIVABLES_ACCOUNT"
      , (SELECT    gcc.segment1
                || '-'
                || gcc.segment2
                || '-'
                || gcc.segment3
                || '-'
                || gcc.segment4
                || '-'
                || gcc.segment5
                || '-'
                || gcc.segment6
         FROM   gl_code_combinations gcc
         WHERE  gcc.code_combination_id = interorg_payables_account)
          "INTER_ORG_PAYABLES_ACCOUNT"
      , (SELECT    gcc.segment1
                || '-'
                || gcc.segment2
                || '-'
                || gcc.segment3
                || '-'
                || gcc.segment4
                || '-'
                || gcc.segment5
                || '-'
                || gcc.segment6
         FROM   gl_code_combinations gcc
         WHERE  gcc.code_combination_id = intransit_inv_account)
          "INTRANSIT_INV_ACCOUNT"
      ,to_char (trunc (creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_shipping_network_view
ORDER BY 1, 3
--Purpose  To verify the Organization setup defined.
--Description  This query will fetch and list all the Organization setup defined in the system.
SELECT mp.organization_code
      ,houv.NAME
      ,houv.organization_type
      ,houv.location_code "LOCATION"
      ,houv.attribute1 "STORE_PRICING_ZONE CODE"
      ,hl.meaning "ORGANIZATION_CLASSIFICATION"
      ,hoiv.org_information_context
      ,hoiv.org_information1 "PRIMARY_LEDGER"
      ,hoiv.org_information2 "LEGAL_ENTITY"
      ,hoiv.org_information3 "OPERATING_UNIT"
      ,to_char (trunc (houv.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   hr_organization_units_v houv
      ,hr_organization_information_v hoiv
      ,hr_lookups hl
      ,mtl_parameters mp
WHERE      houv.organization_id = hoiv.organization_id(+)
       AND houv.organization_id = mp.organization_id
       AND hoiv.org_information1 = hl.lookup_code(+)
       AND hl.lookup_type(+) = 'ORG_CLASS'
ORDER BY 1, 2
--Purpose  To verify the Account Aliases defined.
--Description --
--This query will fetch and list the account aliases along with their GL code combination,
--for all the inventory organizations defined in the system.
SELECT mp.organization_id
      ,mp.organization_code
      ,haoc.NAME "ORGANIZATION_NAME"
      ,haoc.TYPE "ORG_TYPE"
      ,mgd.segment1 "ACCOUNT_ALIAS"
      ,   gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
          "GL_CODE_COMBINATION"
      ,to_char (trunc (mgd.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_generic_dispositions mgd   ---Account alias definition table
      ,mtl_parameters mp
      ,hr_all_organization_units haoc
      ,gl_code_combinations gcc
WHERE      mp.organization_id = haoc.organization_id
       AND mp.organization_id = mgd.organization_id
       AND gcc.code_combination_id = mgd.distribution_account
ORDER BY 1, 5
--Purpose  To verify the Subinventories defined.
--Description  This query will fetch and list all the subinventories for all the inventory organizations defined in the system.
SELECT mp.organization_code
      ,haoc.TYPE "ORG_TYPE"
      ,haoc.NAME "ORGANIZATION_NAME"
      ,miv.secondary_inventory_name "SUBINVENTORY_NAME"
      ,miv.subinventory_type
      ,miv.description
      ,miv.status_code "STATUS"
      ,miv.locator_type "LOCATOR_CONTROL"
      ,to_char (trunc (miv.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_secondary_inventories_fk_v miv
      ,mtl_parameters mp
      ,hr_all_organization_units haoc
WHERE  miv.organization_id = mp.organization_id
       AND haoc.organization_id = mp.organization_id
ORDER BY 2, 5
--Purpose  To verify the Organization Hierarchy defined.
--Description  This query will fetch and list the Organization hierarchy defined in the system.
SELECT pos.name "ORG HIERARCHY NAME"
      ,pose.d_parent_name "PARENT ORG NAME"
      ,pos.primary_structure_flag
      ,pos.position_control_structure_flg
      ,pose.d_child_name "SUBORDINATE ORG NAME"
      ,TO_CHAR (TRUNC (pos.creation_date), 'DD-MON-YYYY') "DATE_FROM"
FROM   per_organization_structures_v pos, per_org_structure_elements_v pose
WHERE  pos.organization_structure_id = pose.org_structure_version_id
ORDER BY 1, 2, 5
--Purpose  To verify the Locators defined.
--Description  This query will fetch and list the locators defined in the system for subinventories that are locator controlled.
SELECT mp.organization_code
      ,haoc.TYPE "ORG_TYPE"
      ,haoc.NAME "ORGANIZATION_NAME"
      ,miv.secondary_inventory_name "SUBINVENTORY_NAME"
      ,mil.segment1 "LOCATOR"
      ,to_char (trunc (mil.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_secondary_inventories miv
      ,mtl_parameters mp
      ,mtl_item_locations mil
      ,hr_all_organization_units haoc
WHERE      mp.organization_id = miv.organization_id
       AND miv.secondary_inventory_name = mil.subinventory_code
       AND mp.organization_id = haoc.organization_id
       AND mp.organization_id = mil.organization_id
       AND miv.locator_type = 2
ORDER BY 1, 2, 5
--Purpose  To verify the ABC Classes defined.
--Description  This query will fetch and list the ABC Classes for all inventory organizations defined in the system.
SELECT haou.name "ORGANIZATION_NAME"
      ,mp.organization_code
      ,mac.abc_class_name "CLASS_NAME"
      ,mac.description
      ,mac.disable_date
      ,TO_CHAR (TRUNC (mac.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_abc_classes_v mac
      ,mtl_parameters mp
      ,hr_all_organization_units haou
WHERE  mp.organization_id = haou.organization_id
       AND mp.organization_id = mac.organization_id
ORDER BY 2, 3
--Purpose  To verify the ABC Compiles defined.
--Description  This query will fetch and list the ABC Compiles for all inventory organizations defined in the system.
SELECT haou.NAME "ORGANIZATION_NAME"
      ,mp.organization_code
      ,mach.compile_name
      ,mach.description
      ,mach.secondary_inventory "SUBINVENTORY"
      ,mach.compile_type_description "CRITERION"
      ,mach.cost_type_description "COST_TYPE"
      ,mach.mrp_forecast_name "FORECAST"
      ,mach.mrp_plan_name "PLAN_NAME"
      ,mach.start_date "FROM_DATE"
      ,mach.cutoff_date "TO_DATE"
      ,to_char (trunc (mach.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_abc_compile_headers_v mach
      ,mtl_parameters mp
      ,hr_all_organization_units haou
WHERE  mp.organization_id = haou.organization_id
       AND mp.organization_id = mach.organization_id
ORDER BY 2, 3
---To verify the Cycle Counts defined
---- This query will fetch and list the Cycle Counts for all inventory organizations defined in the system.
SELECT haou.NAME "ORGANIZATION_NAME"
      ,mp.organization_code
      ,mcch.cycle_count_header_name "CYCLE_COUNT_NAME"
      ,mcch.description
      ,mcch.cycle_count_calendar "CALENDAR"
      , (SELECT    gcc.segment1
                || '-'
                || gcc.segment2
                || '-'
                || gcc.segment3
                || '-'
                || gcc.segment4
                || '-'
                || gcc.segment5
                || '-'
                || gcc.segment6
         FROM   gl_code_combinations gcc
         WHERE  gcc.code_combination_id = inventory_adjustment_account)
          "ADJUSTMENT_ACCOUNT"
      ,mcch.disable_date
      ,mcch.days_until_late "LATE_COUNT_DAYS"
      ,mcch.next_user_count_sequence "STARTING_SEQUENCE"
      ,mcch.unscheduled_count_entry "UNSCHEDULED_ENTRIES"
      ,mcch.automatic_recount_flag
      ,mcch.maximum_auto_recounts
      ,mcs.subinventory
      ,mcs.disable_flag
      ,mcch.serial_count_option_meaning "SERIAL_COUNT"
      ,mcch.serial_detail_option_meaning "SERIAL_DETAIL"
      ,mcch.serial_adj_option_meaning "SERIAL_ADJUSTMENT"
      ,mcch.serial_dis_option_meaning "SERIAL_DISCREPANCY"
      ,mcch.autoschedule_enabled_flag
      ,mcch.zero_count_flag "COUNT_ZERO_QUANTITY"
      ,mcch.header_last_schedule_date "LAST_DATE"
      ,mcch.header_next_schedule_date "NEXT_DATE"
      ,mcch.approval_tolerance_positive "QUANTITY_VARIANCE (+) %"
      ,mcch.approval_tolerance_negative "QUANTITY_VARIANCE (-) %"
      ,mcch.cost_tolerance_positive "ADJUSTMNT_VALUE (+) %"
      ,mcch.cost_tolerance_negative "ADJUSTMNT_VALUE (-) %"
      ,mcch.hit_miss_tolerance_positive "HIT/MISS_ANALYSIS (+) %"
      ,mcch.hit_miss_tolerance_negative "HIT/MISS_ANALYSIS (-) %"
      ,maag.assignment_group_name "GROUP"
      ,mccv.cc_class_name "CLASSES"
      ,to_char (trunc (mcch.creation_date), 'DD-MON-YYYY') "CREATION_DATE"
FROM   mtl_cycle_count_headers_v mcch
      ,mtl_cc_subinventories mcs
      ,mtl_abc_assignment_groups_v maag
      ,mtl_cycle_count_classes_v mccv
      ,mtl_parameters mp
      ,hr_all_organization_units haou
WHERE      mp.organization_id = haou.organization_id
       AND mp.organization_id = mcch.organization_id
       AND mp.organization_id = maag.organization_id
       AND mp.organization_id = mccv.organization_id
       AND mcch.cycle_count_header_id = mccv.cycle_count_header_id
       AND mcs.cycle_count_header_id(+) = mcch.cycle_count_header_id
       AND mcs.cycle_count_header_id = mccv.cycle_count_header_id
       AND maag.assignment_group_id = mcch.abc_assignment_group_id
ORDER BY 2, 3, 13

---获取concurrent trace file的path and name
SELECT req.request_id,
       req.logfile_node_name node,
       req. oracle_process_id,
       req.enable_trace,
       dest.value || '/' || lower(dbnm.value) || '_ora_' ||
       oracle_process_id || '.trc' trace_filename,
       prog.user_concurrent_program_name,
       execname.execution_file_name,
       execname.subroutine_name,
       phase_code,
       status_code,
       ses.sid,
       ses.serial#,
       ses.module,
       ses.machine
FROM fnd_concurrent_requests    req,
     v$session                  ses,
     v$process                  proc,
     v$parameter                dest,
     v$parameter                dbnm,
     fnd_concurrent_programs_vl prog,
     fnd_executables            execname
WHERE 1 = 1
AND req.request_id = 146830457/146830520
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.name = 'user_dump_dest'
AND dbnm.name = 'db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id = execname.executable_id
---查询concurrent跑了多久
SELECT fcr.request_id request_id,
       trunc(((fcr.actual_completion_date - fcr.actual_start_date) /
             (1 / 24)) * 60) exec_time,
       fcr.actual_start_date start_date,
       fcp.concurrent_program_name conc_prog,
       fcpt.user_concurrent_program_name user_conc_prog
FROM fnd_concurrent_programs    fcp,
     fnd_concurrent_programs_tl fcpt,
     fnd_concurrent_requests    fcr
WHERE trunc(((fcr.actual_completion_date - fcr.actual_start_date) /
            (1 / 24)) * 60) > nvl('', 45)
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcpt.language = userenv('Lang')
ORDER BY trunc(((fcr.actual_completion_date - fcr.actual_start_date) /
               (1 / 24)) * 60) DESC;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-758090/，如需转载，请注明出处，否则将追究法律责任。