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 CV WHERE fdsh.reversal_event_id = xh.event_id --注意此处用冲销/反转的的的reversal_event_id AND xh.ae_header_id = xl.ae_header_id AND fdsh.asset_id = 100023422 AND xl.code_combination_id = gcc.code_combination_id AND xh.gl_transfer_status_code = 'Y' AND GCC.SEGMENT3 = CV.flex_value AND CV.COA_CLASS = 'S3'/*AND H.BOOK_TYPE_CODE LIKE '%US%'*/ AND XH.LEDGER_ID = 2022 ORDER 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 CV WHERE h.event_id = xh.event_id AND xh.ae_header_id = xl.ae_header_id AND h.asset_id = 100023422 AND xl.code_combination_id = gcc.code_combination_id AND xh.gl_transfer_status_code = 'Y' AND GCC.SEGMENT3 = CV.flex_value AND 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 CV WHERE fds.event_id = xh.event_id AND xh.ae_header_id = xl.ae_header_id AND fds.asset_id = 100023422 AND xl.code_combination_id = gcc.code_combination_id AND xh.gl_transfer_status_code = 'Y' AND GCC.SEGMENT3 = CV.flex_value AND CV.COA_CLASS = 'S3'/*AND H.BOOK_TYPE_CODE LIKE '%US%'*/ AND XH.LEDGER_ID = 2022UNION 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 CV WHERE fdsh.reversal_event_id = xh.event_id AND xh.ae_header_id = xl.ae_header_id AND fdsh.asset_id = 100023422 AND xl.code_combination_id = gcc.code_combination_id AND xh.gl_transfer_status_code = 'Y' AND GCC.SEGMENT3 = CV.flex_value AND CV.COA_CLASS = 'S3'/*AND H.BOOK_TYPE_CODE LIKE '%US%'*/ AND XH.LEDGER_ID = 2022 ORDER BY 1;

参考资料：

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