SELECT LPAD(' ', 6 * (LEVEL – 1)) || menu_entry.entry_sequence sequence,
LPAD(' ', 6 * (LEVEL – 1)) || menu.user_menu_name SubMenu_Descrition,
LPAD(' ', 6 * (LEVEL – 1)) || func.user_function_name Function_Description,
menu.menu_id,
func.function_id,
menu_entry.grant_flag Grant_Flag,
DECODE(menu_entry.sub_menu_id,
null,
'FUNCTION',
DECODE(menu_entry.function_id, null, 'SUBMENU', 'BOTH')) Type
FROM fnd_menu_entries menu_entry,
fnd_menus_vl menu,
fnd_form_functions_vl func
WHERE 1 = 1
AND menu_entry.sub_menu_id = menu.menu_id(+)
AND menu_entry.function_id = func.function_id(+)
AND grant_flag = 'Y'
START WITH menu_entry.menu_id =
(SELECT menu_id
FROM fnd_menus_tl menu2
WHERE menu2.user_menu_name = 'FIN_GL_MANAGER'–根据自己实际情况替换
and menu2.language = 'ZHS')
CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
ORDER SIBLINGS BY menu_entry.entry_sequence;
分类目录归档：EBS_GL_SQL
外部审计数据提取命令
SELECT FFV1.DESCRIPTION 机构名称,
GCC.SEGMENT3 "科目代码",
FFV3.DESCRIPTION 科目名称,
FU.USER_NAME 凭证录入人,
GJS.USER_JE_SOURCE_NAME 日记账来源,
GJC.USER_JE_CATEGORY_NAME 日记账类别,
GJH.PERIOD_NAME 会计期间,
GJH.POSTED_DATE 过账日期,
GJH.EXTERNAL_REFERENCE 凭证号码,
GJL.DESCRIPTION 摘要,
GJL.ENTERED_DR 借方,
GJL.ENTERED_CR 贷方,
'' 方向,
'' 余额,
'' 外币代码,
'' 外币借方,
'' 外币贷方
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
GL_JE_CATEGORIES GJC,
GL_JE_SOURCES GJS,
FND_USER FU,
FND_FLEX_VALUE_SETS FFVS1,
FND_FLEX_VALUES_VL FFV1,
FND_FLEX_VALUE_SETS FFVS3,
FND_FLEX_VALUES_VL FFV3
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GJC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJH.CREATED_BY = FU.USER_ID(+)
AND GCC.SEGMENT1 = FFV1.FLEX_VALUE
AND FFVS1.FLEX_VALUE_SET_ID = FFV1.FLEX_VALUE_SET_ID
AND FFVS1.FLEX_VALUE_SET_NAME = 'XXXX_COMPANY' –第一段，根据自己环境确定
AND GCC.SEGMENT3 = FFV3.FLEX_VALUE
AND FFVS3.FLEX_VALUE_SET_ID = FFV3.FLEX_VALUE_SET_ID
AND FFVS3.FLEX_VALUE_SET_NAME = 'XXXX_ACCOUNT' –第三段，根据自己环境确定
AND GJH.STATUS = 'P'
AND GJH.ACTUAL_FLAG = 'A'
ORDER BY GCC.SEGMENT1, GJH.PERIOD_NAME;
获取员工主管信息
SELECT PP.LAST_NAME 员工姓名,
PP.PERSON_ID 员工ID,
PP.SEX 性别,
PP.EMAIL_ADDRESS 电子邮件,
PPF.LAST_NAME 主管姓名,
PPF.EMAIL_ADDRESS 主管EMAIL
FROM PER_PEOPLE_F PP, PER_ASSIGNMENTS_F PA, PER_PEOPLE_F PPF
WHERE PP.PERSON_ID = PA.PERSON_ID
AND SYSDATE BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND PA.SUPERVISOR_ID = PPF.PERSON_ID
ORDER BY PP.CREATION_DATE ASC;
获取会计科目段子父级关系
––-相关的参数值请根据自己的实际情况填写
SELECT DATA_SOURCE.FLEX_VALUE_SET_NAME 值集名称,
DATA_SOURCE.FLEX_VALUE_SET_ID 值集ID,
DATA_SOURCE.FLEX_VALUE 值代码,
DATA_SOURCE.DESCRIPTION 值描述,
DATA_SOURCE.SUMMARY_FLAG 是否父级,
DATA_SOURCE.HIERARCHY_NAME 组级别,
DATA_SOURCE.RANGE_FROM 范围从,
DATA_SOURCE.RANGE_TO 范围至
FROM (SELECT FFVS.FLEX_VALUE_SET_NAME FLEX_VALUE_SET_NAME,
FFVS.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
FFV.FLEX_VALUE FLEX_VALUE,
FFV.SUMMARY_FLAG SUMMARY_FLAG,
FFV.DESCRIPTION DESCRIPTION,
FFHV.HIERARCHY_NAME HIERARCHY_NAME,
FFVNH.CHILD_FLEX_VALUE_LOW RANGE_FROM,
FFVNH.CHILD_FLEX_VALUE_HIGH RANGE_TO
FROM FND_FLEX_VALUES_VL FFV,
FND_FLEX_VALUE_NORM_HIERARCHY FFVNH,
FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_HIERARCHIES_VL FFHV
WHERE FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_SET_ID = FFVNH.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE = FFVNH.PARENT_FLEX_VALUE
AND FFV.STRUCTURED_HIERARCHY_LEVEL = FFHV.HIERARCHY_ID
AND FFVS.FLEX_VALUE_SET_NAME = 'XXXX_ACCOUNT'
AND FFV.ENABLED_FLAG = 'Y'
AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
AND FFVS.FLEX_VALUE_SET_NAME IN
(SELECT B.SEGMENT_NAME
FROM APPLSYS.FND_ID_FLEX_STRUCTURES A,
APPLSYS.FND_ID_FLEX_SEGMENTS B,
APPLSYS.FND_FLEX_VALUE_SETS C,
(SELECT *
FROM APPLSYS.FND_SEGMENT_ATTRIBUTE_VALUES AB
WHERE AB.ID_FLEX_CODE = 'GL#'
AND AB.ATTRIBUTE_VALUE = 'Y'
AND AB.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL') ABB
WHERE A.ID_FLEX_CODE = B.ID_FLEX_CODE
AND A.ID_FLEX_CODE = 'GL#'
AND A.ID_FLEX_NUM = B.ID_FLEX_NUM
AND B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID
AND B.ID_FLEX_NUM = ABB.ID_FLEX_NUM(+)
AND B.APPLICATION_COLUMN_NAME =
ABB.APPLICATION_COLUMN_NAME(+)
AND A.ID_FLEX_STRUCTURE_CODE = 'XXXX_ACCOUNT_FLEXFIELD')
UNION
SELECT FFVS.FLEX_VALUE_SET_NAME FLEX_VALUE_SET_NAME,
FFVS.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
FFV.FLEX_VALUE FLEX_VALUE,
FFV.SUMMARY_FLAG SUMMARY_FLAG,
FFV.DESCRIPTION DESCRIPTION,
'明细组' HIERARCHY_NAME,
NULL RANGE_FROM,
NULL RANGE_TO
FROM FND_FLEX_VALUES_VL FFV, FND_FLEX_VALUE_SETS FFVS
WHERE FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME = 'XXXX_ACCOUNT'
AND FFV.ENABLED_FLAG = 'Y'
AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
AND FFVS.FLEX_VALUE_SET_NAME IN
(SELECT B.SEGMENT_NAME
FROM APPLSYS.FND_ID_FLEX_STRUCTURES A,
APPLSYS.FND_ID_FLEX_SEGMENTS B,
APPLSYS.FND_FLEX_VALUE_SETS C,
(SELECT *
FROM APPLSYS.FND_SEGMENT_ATTRIBUTE_VALUES AB
WHERE AB.ID_FLEX_CODE = 'GL#'
AND AB.ATTRIBUTE_VALUE = 'Y'
AND AB.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL') ABB
WHERE A.ID_FLEX_CODE = B.ID_FLEX_CODE
AND A.ID_FLEX_CODE = 'GL#'
AND A.ID_FLEX_NUM = B.ID_FLEX_NUM
AND B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID
AND B.ID_FLEX_NUM = ABB.ID_FLEX_NUM(+)
AND B.APPLICATION_COLUMN_NAME =
ABB.APPLICATION_COLUMN_NAME(+)
AND A.ID_FLEX_STRUCTURE_CODE = 'XXXX_ACCOUNT_FLEXFIELD')
AND FFV.SUMMARY_FLAG = 'N') DATA_SOURCE
ORDER BY DATA_SOURCE.FLEX_VALUE_SET_ID, DATA_SOURCE.FLEX_VALUE;
获取FSG报表明细行定义的SQL
客户要求取出某指定的FSG报表的明细行定义的数据，以下是相关的SQL命令：
SELECT *
FROM (SELECT RRA.NAME,
RRV.SEQUENCE,
RRV.DESCRIPTION,
RRV.NUMBER_CHARACTERS_INDENTED,
RRV.NUMBER_LINES_SKIPPED_BEFORE,
RRV.NUMBER_LINES_SKIPPED_AFTER,
RRV.AMOUNT_TYPE,
RRV.UNIT_OF_MEASURE_ID,
RRV.PARAMETER_NUM,
RRV.PERIOD_OFFSET,
RRV.FACTOR,
RRV.LEVEL_OF_DETAIL,
RRV.DISPLAY_FLAG,
RRV.DISPLAY_ZERO_AMOUNT_FLAG,
RRV.CHANGE_SIGN_FLAG,
RRV.CHANGE_VARIANCE_SIGN_FLAG,
RRV.CALCULATION_PRECEDENCE_FLAG,
RRC.SIGN,
RRC.LEDGER_ID,
RRC.SEGMENT1_LOW || '.' || RRC.SEGMENT2_LOW || '.' ||
RRC.SEGMENT3_LOW || '.' || RRC.SEGMENT4_LOW || '.' ||
RRC.SEGMENT5_LOW || '.' || RRC.SEGMENT6_LOW || '.' ||
RRC.SEGMENT7_LOW || '.' || RRC.SEGMENT8_LOW SEGMENT_LOW,
RRC.SEGMENT1_HIGH || '.' || RRC.SEGMENT2_HIGH || '.' ||
RRC.SEGMENT3_HIGH || '.' || RRC.SEGMENT4_HIGH || '.' ||
RRC.SEGMENT5_HIGH || '.' || RRC.SEGMENT6_HIGH || '.' ||
RRC.SEGMENT7_HIGH || '.' || RRC.SEGMENT8_HIGH SEGMENT_HIGH,
RRC.SEGMENT1_TYPE || '.' || RRC.SEGMENT2_TYPE || '.' ||
RRC.SEGMENT3_TYPE || '.' || RRC.SEGMENT4_TYPE || '.' ||
RRC.SEGMENT5_TYPE || '.' || RRC.SEGMENT6_TYPE || '.' ||
RRC.SEGMENT7_TYPE || '.' || RRC.SEGMENT8_TYPE SEGMENT_HIGH_TYPE,
RRC.RANGE_MODE,
RRC.DR_CR_NET_CODE,
NULL CALCULATION_SEQ,
NULL OPERATOR,
NULL CONSTANT,
NULL AXIS_SEQ_LOW,
NULL AXIS_SEQ_HIGH,
NULL AXIS_NAME_LOW
FROM RG_REPORT_AXIS_SETS_V RRA,
RG_REPORT_AXES_V RRV,
RG_REPORT_AXIS_CONTENTS RRC
WHERE 1 = 1
AND RRC.AXIS_SET_ID = RRA.AXIS_SET_ID
AND RRC.AXIS_SEQ = RRV.SEQUENCE
AND RRA.AXIS_SET_ID = RRV.AXIS_SET_ID
AND RRA.NAME IN ('XXXX_新准则CIRC统计指标')
UNION ALL
SELECT RRA.NAME,
RRV.SEQUENCE,
RRV.DESCRIPTION,
RRV.NUMBER_CHARACTERS_INDENTED,
RRV.NUMBER_LINES_SKIPPED_BEFORE,
RRV.NUMBER_LINES_SKIPPED_AFTER,
RRV.AMOUNT_TYPE,
RRV.UNIT_OF_MEASURE_ID,
RRV.PARAMETER_NUM,
RRV.PERIOD_OFFSET,
RRV.FACTOR,
RRV.LEVEL_OF_DETAIL,
RRV.DISPLAY_FLAG,
RRV.DISPLAY_ZERO_AMOUNT_FLAG,
RRV.CHANGE_SIGN_FLAG,
RRV.CHANGE_VARIANCE_SIGN_FLAG,
RRV.CALCULATION_PRECEDENCE_FLAG,
NULL SIGN,
NULL LEDGER_ID,
NULL SEGMENT_LOW,
NULL SEGMENT_HIGH,
NULL SEGMENT_HIGH_TYPE,
NULL RANGE_MODE,
NULL DR_CR_NET_CODE,
RRC1.CALCULATION_SEQ,
RRC1.OPERATOR,
RRC1.CONSTANT,
RRC1.AXIS_SEQ_LOW,
RRC1.AXIS_SEQ_HIGH,
RRC1.AXIS_NAME_LOW
FROM RG_REPORT_AXIS_SETS_V RRA,
RG_REPORT_AXES_V RRV,
RG_REPORT_CALCULATIONS RRC1
WHERE 1 = 1
AND RRC1.AXIS_SET_ID = RRA.AXIS_SET_ID
AND RRC1.AXIS_SEQ = RRV.SEQUENCE
AND RRA.AXIS_SET_ID = RRV.AXIS_SET_ID
AND RRA.NAME IN ('XXXX_新准则CIRC统计指标')
UNION ALL
SELECT RRA.NAME,
RRV.SEQUENCE,
RRV.DESCRIPTION,
RRV.NUMBER_CHARACTERS_INDENTED,
RRV.NUMBER_LINES_SKIPPED_BEFORE,
RRV.NUMBER_LINES_SKIPPED_AFTER,
RRV.AMOUNT_TYPE,
RRV.UNIT_OF_MEASURE_ID,
RRV.PARAMETER_NUM,
RRV.PERIOD_OFFSET,
RRV.FACTOR,
RRV.LEVEL_OF_DETAIL,
RRV.DISPLAY_FLAG,
RRV.DISPLAY_ZERO_AMOUNT_FLAG,
RRV.CHANGE_SIGN_FLAG,
RRV.CHANGE_VARIANCE_SIGN_FLAG,
RRV.CALCULATION_PRECEDENCE_FLAG,
NULL SIGN,
NULL LEDGER_ID,
NULL SEGMENT_LOW,
NULL SEGMENT_HIGH,
NULL SEGMENT_HIGH_TYPE,
NULL RANGE_MODE,
NULL DR_CR_NET_CODE,
NULL CALCULATION_SEQ,
NULL OPERATOR,
NULL CONSTANT,
NULL AXIS_SEQ_LOW,
NULL AXIS_SEQ_HIGH,
NULL AXIS_NAME_LOW
FROM RG_REPORT_AXIS_SETS_V RRA, RG_REPORT_AXES_V RRV
WHERE 1 = 1
AND RRA.AXIS_SET_ID = RRV.AXIS_SET_ID
AND RRA.NAME IN ('XXXX_新准则CIRC统计指标')
AND NOT EXISTS (SELECT 1
FROM RG_REPORT_AXIS_CONTENTS T
WHERE T.AXIS_SET_ID = RRA.AXIS_SET_ID
AND T.AXIS_SEQ = RRV.SEQUENCE)
AND NOT EXISTS (SELECT 1
FROM RG_REPORT_CALCULATIONS T2
WHERE T2.AXIS_SET_ID = RRA.AXIS_SET_ID
AND T2.AXIS_SEQ = RRV.SEQUENCE)
)
WHERE 1 = 1
ORDER BY NAME, SEQUENCE;