Oracle EBS FA折旧回滚的分录追溯

FA模块向子分类账和总账追溯分为两部分：事务表和折旧，但是FA相关表做其实关联计划外折旧的分录会被遗漏的原因：
如果已经当月折旧，运行完成折旧后，又进行了计划外折旧，因为计划折旧时又要区分【是否进行当月摊销调整】，此时Oracle会自动把计划外折旧前的当月的折旧给回滚掉，折旧回滚程序会清理FA_DEPRN_SUMMARY和FA_DEPRN_DETAIL中的数据（清理的数据会进入历史表fa_deprn_detail_h、fa_deprn_summary_h）

FA折旧回滚的分录追溯

FA折旧回滚向子分类账追溯的sql如下：

--折旧回滚
SELECT XH.AE_HEADER_ID,XH.PERIOD_NAME,XH.EVENT_TYPE_CODE,GCC.SEGMENT3,CV.description,XL.ACCOUNTED_DR,XL.ACCOUNTED_CR,xl.gl_sl_link_id,xl.gl_sl_link_tableFROM fa_deprn_summary_h       fdsh,xla.xla_ae_headers       xh,xla.xla_ae_lines         xl,gl_code_combinations     gcc,CUX_COA_ALL_VALUE_DESC_V CVWHERE fdsh.reversal_event_id = xh.event_id --注意此处用冲销/反转的的的reversal_event_idAND xh.ae_header_id = xl.ae_header_idAND fdsh.asset_id = 100023422AND xl.code_combination_id = gcc.code_combination_idAND xh.gl_transfer_status_code = 'Y'AND GCC.SEGMENT3 = CV.flex_valueAND CV.COA_CLASS = 'S3'/*AND H.BOOK_TYPE_CODE LIKE '%US%'*/AND XH.LEDGER_ID = 2022ORDER BY 1;

所以完整的资产模块向子分类账追溯的sql如下：

SELECT XH.AE_HEADER_ID,XH.PERIOD_NAME,/* H.TRANSACTION_TYPE_CODE,H.TRANSACTION_SUBTYPE,*/XH.EVENT_TYPE_CODE,GCC.SEGMENT3,CV.description,XL.ACCOUNTED_DR,XL.ACCOUNTED_CR,xl.gl_sl_link_id,xl.gl_sl_link_tableFROM fa_transaction_headers   h,xla.xla_ae_headers       xh,xla.xla_ae_lines         xl,gl_code_combinations     gcc,CUX_COA_ALL_VALUE_DESC_V CVWHERE h.event_id = xh.event_idAND xh.ae_header_id = xl.ae_header_idAND h.asset_id = 100023422AND xl.code_combination_id = gcc.code_combination_idAND xh.gl_transfer_status_code = 'Y'AND GCC.SEGMENT3 = CV.flex_valueAND CV.COA_CLASS = 'S3'AND XH.LEDGER_ID = 2022
/* ORDER BY H.TRANSACTION_HEADER_ID*/
UNION ALLSELECT XH.AE_HEADER_ID,XH.PERIOD_NAME,XH.EVENT_TYPE_CODE,GCC.SEGMENT3,CV.description,XL.ACCOUNTED_DR,XL.ACCOUNTED_CR,xl.gl_sl_link_id,xl.gl_sl_link_tableFROM fa_deprn_summary         fds,xla.xla_ae_headers       xh,xla.xla_ae_lines         xl,gl_code_combinations     gcc,CUX_COA_ALL_VALUE_DESC_V CVWHERE fds.event_id = xh.event_idAND xh.ae_header_id = xl.ae_header_idAND fds.asset_id = 100023422AND xl.code_combination_id = gcc.code_combination_idAND xh.gl_transfer_status_code = 'Y'AND GCC.SEGMENT3 = CV.flex_valueAND CV.COA_CLASS = 'S3'/*AND H.BOOK_TYPE_CODE LIKE '%US%'*/AND XH.LEDGER_ID = 2022
UNION ALL
--折旧回滚
SELECT XH.AE_HEADER_ID,XH.PERIOD_NAME,XH.EVENT_TYPE_CODE,GCC.SEGMENT3,CV.description,XL.ACCOUNTED_DR,XL.ACCOUNTED_CR,xl.gl_sl_link_id,xl.gl_sl_link_tableFROM fa_deprn_summary_h       fdsh,xla.xla_ae_headers       xh,xla.xla_ae_lines         xl,gl_code_combinations     gcc,CUX_COA_ALL_VALUE_DESC_V CVWHERE fdsh.reversal_event_id = xh.event_idAND xh.ae_header_id = xl.ae_header_idAND fdsh.asset_id = 100023422AND xl.code_combination_id = gcc.code_combination_idAND xh.gl_transfer_status_code = 'Y'AND GCC.SEGMENT3 = CV.flex_valueAND CV.COA_CLASS = 'S3'/*AND H.BOOK_TYPE_CODE LIKE '%US%'*/AND XH.LEDGER_ID = 2022ORDER BY 1;

参考资料：

https://blog.csdn.net/x6_9x/article/details/136249463
https://blog.csdn.net/weixin_43283158/article/details/129672614