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_table
  FROM 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
   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_table
  FROM 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.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_table
  FROM 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 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_table
  FROM 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 = 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_table
  FROM 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
   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_table
  FROM 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.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
 ORDER BY 1;

参考资料：

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