EBS 常用 SQL

evenno

--用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.responsibility_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 "SUBINVENTORY"
,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;