EBS FA 总帐追溯查询SQL

--1 :根据类源总帐关子模块查询

SELECT gjh.ATTRIBUTE10,

gjh.DESCRIPTION,

gjh.NAME,

GJCT.user_je_category_name,

gjl.PERIOD_NAME,

gjl.ENTERED_DR,

gjl.ENTERED_CR,

gjl.DESCRIPTION,

xte.transaction_number,

xla_oa_functions_pkg.get_ccid_description(gcc.chart_of_accounts_id,gcc.code_combination_id) concatenated_desc

FROM apps.GL_JE_LINES GJL, --日记账行

apps.GL_JE_HEADERS GJH, --日记账头

apps.GL_JE_SOURCES_TL GJST, --日记账来源

apps.GL_JE_CATEGORIES_TL GJCT, --日记账类别

apps.GL_CODE_COMBINATIONS_KFV GCC, --账户组合

apps.GL_IMPORT_REFERENCES GIR, --总账导入参考信息(GL和XLA之间的关联)

XLA.XLA_AE_LINES XAL, --子分类账行

XLA.XLA_AE_HEADERS XAH, --子分类账头

XLA.XLA_TRANSACTION_ENTITIES XTE --子分类账事务处理实体信息

WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

AND GJH.JE_SOURCE = GJST.JE_SOURCE_NAME

AND GJH.JE_CATEGORY = GJCT.JE_CATEGORY_NAME

AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE

AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID

AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAH.ENTITY_ID = XTE.ENTITY_ID

AND XAH.LEDGER_ID = XTE.LEDGER_ID

AND XAH.APPLICATION_ID = XTE.APPLICATION_ID

AND GJST.LANGUAGE = 'ZHS'

AND GJCT.LANGUAGE = 'ZHS'

AND gjl.PERIOD_NAME ='2023-01'

AND GJST.USER_JE_SOURCE_NAME='资产'

AND xte.ledger_id=2021；

--2：根据类源总帐关联FA事务查询

SELECT gjh.ATTRIBUTE10,

gjh.DESCRIPTION,

gjh.NAME,

GJCT.user_je_category_name,

gjl.PERIOD_NAME,

gjl.ENTERED_DR,

gjl.ENTERED_CR,

gjl.DESCRIPTION,

xte.transaction_number,

fa.ASSET_NUMBER,

fa.MODEL_NUMBER,

fa.MANUFACTURER_NAME,

xla_oa_functions_pkg.get_ccid_description(gcc.chart_of_accounts_id,gcc.code_combination_id) concatenated_desc,

xte.ENTITY_CODE

FROM apps.GL_JE_LINES GJL, --日记账行

apps.GL_JE_HEADERS GJH, --日记账头

apps.GL_JE_SOURCES_TL GJST, --日记账来源

apps.GL_JE_CATEGORIES_TL GJCT, --日记账类别

apps.GL_CODE_COMBINATIONS_KFV GCC, --账户组合

apps.GL_IMPORT_REFERENCES GIR, --总账导入参考信息(GL和XLA之间的关联)

XLA.XLA_AE_LINES XAL, --子分类账行

XLA.XLA_AE_HEADERS XAH, --子分类账头

XLA.XLA_TRANSACTION_ENTITIES XTE,--子分类账事务处理实体信息

FA_ADDITIONS_B fa,

FA_DEPRN_DETAIL fdd

WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

AND GJH.JE_SOURCE = GJST.JE_SOURCE_NAME

AND GJH.JE_CATEGORY = GJCT.JE_CATEGORY_NAME

AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE

AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID

AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAH.ENTITY_ID = XTE.ENTITY_ID

AND XAH.LEDGER_ID = XTE.LEDGER_ID

AND XAH.APPLICATION_ID = XTE.APPLICATION_ID

AND fa.ASSET_ID = fdd.ASSET_ID

and xte.SOURCE_ID_INT_1 = fdd.ASSET_ID

and xte.SOURCE_ID_CHAR_1 = fdd.BOOK_TYPE_CODE

and xte.SOURCE_ID_INT_2 = fdd.PERIOD_COUNTER

and xte.SOURCE_ID_INT_3 = fdd.DEPRN_RUN_ID

AND GJST.LANGUAGE = 'ZHS'

AND GJCT.LANGUAGE = 'ZHS'

AND gjl.PERIOD_NAME ='2023-01'

AND GJST.USER_JE_SOURCE_NAME='资产'

AND GJCT.user_je_category_name='折旧'

AND xte.ledger_id=2021

union all

SELECT gjh.ATTRIBUTE10,

gjh.DESCRIPTION,

gjh.NAME,

GJCT.user_je_category_name,

gjl.PERIOD_NAME,

gjl.ENTERED_DR,

gjl.ENTERED_CR,

gjl.DESCRIPTION,

xte.transaction_number,

fa.ASSET_NUMBER,

fa.MODEL_NUMBER,

fa.MANUFACTURER_NAME,

xla_oa_functions_pkg.get_ccid_description(gcc.chart_of_accounts_id,gcc.code_combination_id) concatenated_desc,

xte.ENTITY_CODE

FROM apps.GL_JE_LINES GJL, --日记账行

apps.GL_JE_HEADERS GJH, --日记账头

apps.GL_JE_SOURCES_TL GJST, --日记账来源

apps.GL_JE_CATEGORIES_TL GJCT, --日记账类别

apps.GL_CODE_COMBINATIONS_KFV GCC, --账户组合

apps.GL_IMPORT_REFERENCES GIR, --总账导入参考信息(GL和XLA之间的关联)

XLA.XLA_AE_LINES XAL, --子分类账行

XLA.XLA_AE_HEADERS XAH, --子分类账头

XLA.XLA_TRANSACTION_ENTITIES XTE,--子分类账事务处理实体信息

FA_ADDITIONS_B fa,

FA_TRANSACTION_HEADERS fth

WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

AND GJH.JE_SOURCE = GJST.JE_SOURCE_NAME

AND GJH.JE_CATEGORY = GJCT.JE_CATEGORY_NAME

AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

AND XAL.GL_SL_LINK_TABLE = GIR.GL_SL_LINK_TABLE

AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID

AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAH.ENTITY_ID = XTE.ENTITY_ID

AND XAH.LEDGER_ID = XTE.LEDGER_ID

AND XAH.APPLICATION_ID = XTE.APPLICATION_ID

AND fa.ASSET_ID = fth.ASSET_ID

and xte.SOURCE_ID_INT_1 = fth.TRANSACTION_HEADER_ID

and xte.SOURCE_ID_CHAR_1 = fth.BOOK_TYPE_CODE

AND GJST.LANGUAGE = 'ZHS'

AND GJCT.LANGUAGE = 'ZHS'

AND gjl.PERIOD_NAME ='2023-01'

AND GJST.USER_JE_SOURCE_NAME='资产'

AND GJCT.user_je_category_name<>'折旧'

AND xte.ledger_id=2021