EBS - FA资产剩余寿命计算脚本

已于 2023-01-12 10:52:58 修改
阅读量278 收藏 1
点赞数
分类专栏： Oracle学习笔记 文章标签： 数据库 oracle plsql
于 2022-12-28 16:36:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liouzch/article/details/128470327
版权

参考自表单FAXASSET/Program Unit:INQUIRY_BOOKS.post_query

CREATE OR REPLACE PROCEDURE bhsc_aremlyf(p_book_type_code      IN VARCHAR2,
                                         p_asset_id            IN NUMBER,
                                         p_period_name         IN VARCHAR2 DEFAULT NULL,
                                         x_remaining_life      OUT NUMBER,
                                         x_remaining_life_dpis OUT NUMBER) IS
  l_period_counter              NUMBER;
  v_num_per_fiscal_year         NUMBER;
  v_num_per_fiscal_year_prorate NUMBER;
  l_prorate_date                DATE;
  l_min_cpod                    DATE;
  v_depr_when_acquired_flag     VARCHAR2(3);
  
  CURSOR cur_remlyf_1(c_book_type_code IN VARCHAR2,
                      c_asset_id       IN NUMBER,
                      c_period_counter IN NUMBER) IS
    SELECT fab.life_in_months -
           ((fafy1.fiscal_year * 12 + fcp1.period_num) -
           (fafy2.fiscal_year * 12 + fcp2.period_num)) from_prorate,
           fab.life_in_months -
           ((fafy1.fiscal_year * 12 + fcp1.period_num) -
           (fafy4.fiscal_year * 12 + fcp4.period_num)) from_dpis
      FROM fa_books            fab,
           fa_calendar_periods fcp1, -- open
           fa_calendar_periods fcp2, -- prorate
           fa_calendar_periods fcp3, -- deprn_start
           fa_calendar_periods fcp4, -- DPIS
           fa_book_controls    fabc,
           fa_deprn_periods    fdp,
           fa_fiscal_year      fafy1,
           fa_fiscal_year      fafy2,
           fa_fiscal_year      fafy3,
           fa_fiscal_year      fafy4
     WHERE fab.asset_id = c_asset_id
       AND fab.book_type_code = c_book_type_code
       AND fab.transaction_header_id_out IS NULL
       AND fabc.book_type_code = fab.book_type_code
       AND fdp.period_counter = c_period_counter
       AND fdp.book_type_code = fab.book_type_code
       AND fcp1.calendar_type =
           decode(fab.conversion_date,
                  NULL,
                  fabc.prorate_calendar,
                  fabc.deprn_calendar)
       AND fdp.calendar_period_open_date BETWEEN fcp1.start_date AND
           fcp1.end_date
       AND fcp2.calendar_type = fabc.prorate_calendar
       AND fab.prorate_date BETWEEN fcp2.start_date AND fcp2.end_date
       AND fcp3.calendar_type = fabc.deprn_calendar
       AND fab.deprn_start_date BETWEEN fcp3.start_date AND fcp3.end_date
       AND fcp4.calendar_type = fabc.deprn_calendar
       AND fab.date_placed_in_service BETWEEN fcp4.start_date AND
           fcp4.end_date
       AND fabc.fiscal_year_name = fafy1.fiscal_year_name
       AND fcp1.end_date BETWEEN fafy1.start_date AND fafy1.end_date
       AND fabc.fiscal_year_name = fafy2.fiscal_year_name
       AND fcp2.end_date BETWEEN fafy2.start_date AND fafy2.end_date
       AND fabc.fiscal_year_name = fafy3.fiscal_year_name
       AND fcp3.end_date BETWEEN fafy3.start_date AND fafy3.end_date
       AND fabc.fiscal_year_name = fafy4.fiscal_year_name
       AND fcp4.end_date BETWEEN fafy4.start_date AND fafy4.end_date;
  
  CURSOR cur_remlyf_2(c_book_type_code IN VARCHAR2,
                      c_asset_id       IN NUMBER,
                      c_period_counter IN NUMBER) IS
    SELECT fab.life_in_months -
           floor(months_between(fdp.calendar_period_close_date,
                                fab.prorate_date)) from_prorate,
           fab.life_in_months -
           floor(months_between(fdp.calendar_period_close_date,
                                fab.date_placed_in_service)) from_dpis
      FROM fa_books fab, fa_deprn_periods fdp
     WHERE fab.book_type_code = c_book_type_code
       AND fdp.book_type_code = c_book_type_code
       AND fab.asset_id = c_asset_id
       AND fab.date_ineffective IS NULL
       AND fdp.period_counter = c_period_counter;
  
  CURSOR cur_remlyf_5(c_book_type_code IN VARCHAR2,
                      c_asset_id       IN NUMBER,
                      c_period_counter IN NUMBER) IS
    SELECT ceil(months_between(fcp1.end_date, fcp2.start_date)) from_prorate,
           ceil(months_between(fcp1.end_date, fcp4.start_date)) from_dpis
      FROM fa_books            fab,
           fa_calendar_periods fcp1, -- open
           fa_calendar_periods fcp2, -- prorate
           fa_calendar_periods fcp4, -- DPIS
           fa_book_controls    fabc,
           fa_deprn_periods    fdp,
           fa_fiscal_year      fafy1,
           fa_fiscal_year      fafy2,
           fa_fiscal_year      fafy4,
           fa_calendar_types   fact
     WHERE fab.asset_id = c_asset_id
       AND fab.book_type_code = c_book_type_code
       AND fab.transaction_header_id_out IS NULL
       AND fabc.book_type_code = fab.book_type_code
       AND fact.calendar_type = fabc.deprn_calendar
       AND fdp.period_counter = c_period_counter - 1
       AND fdp.book_type_code = fab.book_type_code
       AND fcp1.calendar_type = fabc.deprn_calendar
       AND fdp.calendar_period_open_date BETWEEN fcp1.start_date AND
           fcp1.end_date
       AND fcp2.calendar_type = fabc.prorate_calendar
       AND fab.prorate_date BETWEEN fcp2.start_date AND fcp2.end_date
       AND fcp4.calendar_type = fabc.deprn_calendar
       AND fab.date_placed_in_service BETWEEN fcp4.start_date AND
           fcp4.end_date
       AND fabc.fiscal_year_name = fafy1.fiscal_year_name
       AND fcp1.end_date BETWEEN fafy1.start_date AND fafy1.end_date
       AND fabc.fiscal_year_name = fafy2.fiscal_year_name
       AND fcp2.end_date BETWEEN fafy2.start_date AND fafy2.end_date
       AND fabc.fiscal_year_name = fafy4.fiscal_year_name
       AND fcp4.end_date BETWEEN fafy4.start_date AND fafy4.end_date;
BEGIN
  FOR inquiry_books IN (SELECT fbbcv.type,
                               fbbcv.prorate_date,
                               fbbcv.sob_id,
                               fbbcv.life_in_months,
                               fbbcv.asset_id,
                               fbbcv.book_type_code,
                               fb.conversion_date
                          FROM fa_books_book_controls_v fbbcv, fa_books fb
                         WHERE fbbcv.asset_id = p_asset_id
                           AND fbbcv.book_type_code = p_book_type_code
                           AND fbbcv.transaction_header_id_out IS NULL
                           AND fb.asset_id = p_asset_id
                           AND fb.book_type_code = p_book_type_code
                           AND fb.transaction_header_id_out IS NULL) LOOP
    
    /* -- updated by yusuf 12-jan-2023
    SELECT MAX(dp.period_counter)
      INTO l_period_counter
      FROM fa_deprn_periods dp
     WHERE dp.book_type_code = inquiry_books.book_type_code
       AND dp.period_name = nvl(p_period_name, dp.period_name);*/
    SELECT MAX(fdp.period_counter)
      INTO l_period_counter
      FROM fa_calendar_periods fcp,
           fa_book_controls    fbc,
           fa_deprn_periods    fdp
     WHERE fcp.period_name = NVL(p_period_name, fcp.period_name)
       AND fcp.calendar_type =
           decode(inquiry_books.conversion_date,
                  NULL,
                  fbc.prorate_calendar,
                  fbc.deprn_calendar)
       AND fbc.book_type_code = inquiry_books.book_type_code
       AND fdp.book_type_code = inquiry_books.book_type_code
       AND fdp.calendar_period_open_date <= fcp.end_date;
    -- end update
    IF (inquiry_books.type = 'Primary') THEN
      SELECT prorate_date
        INTO l_prorate_date
        FROM fa_books
       WHERE book_type_code = inquiry_books.book_type_code
         AND asset_id = inquiry_books.asset_id
         AND date_ineffective IS NULL;
    ELSE
      SELECT prorate_date
        INTO l_prorate_date
        FROM fa_mc_books
       WHERE book_type_code = inquiry_books.book_type_code
         AND asset_id = inquiry_books.asset_id
         AND set_of_books_id = inquiry_books.sob_id
         AND date_ineffective IS NULL;
    END IF;
    
    SELECT MIN(calendar_period_open_date)
      INTO l_min_cpod
      FROM fa_deprn_periods
     WHERE book_type_code = inquiry_books.book_type_code;
    
    SELECT number_per_fiscal_year
      INTO v_num_per_fiscal_year
      FROM fa_calendar_types
     WHERE calendar_type =
           (SELECT decode(fab.conversion_date,
                          NULL,
                          fabc.deprn_calendar,
                          fabc.prorate_calendar)
              FROM fa_book_controls fabc, fa_books fab
             WHERE fabc.book_type_code = inquiry_books.book_type_code
               AND fab.asset_id = inquiry_books.asset_id
               AND fab.book_type_code = fabc.book_type_code
               AND fab.transaction_header_id_out IS NULL);
    
    SELECT number_per_fiscal_year
      INTO v_num_per_fiscal_year_prorate
      FROM fa_calendar_types
     WHERE calendar_type =
           (SELECT fabc.prorate_calendar
              FROM fa_book_controls fabc, fa_books fab
             WHERE fabc.book_type_code = inquiry_books.book_type_code
               AND fab.asset_id = inquiry_books.asset_id
               AND fab.book_type_code = fabc.book_type_code
               AND fab.transaction_header_id_out IS NULL);
    
    SELECT fct.depr_when_acquired_flag
      INTO v_depr_when_acquired_flag
      FROM fa_convention_types fct, fa_books fb
     WHERE fb.book_type_code = inquiry_books.book_type_code
       AND fb.prorate_convention_code = fct.prorate_convention_code
       AND fb.asset_id = inquiry_books.asset_id
       AND fb.transaction_header_id_out IS NULL;
    
    IF (v_num_per_fiscal_year = 12) THEN
      IF (l_prorate_date <= l_min_cpod) OR
         (v_depr_when_acquired_flag = 'YES') THEN
        IF (((v_num_per_fiscal_year_prorate = 12) AND
           (l_prorate_date < l_min_cpod)) OR
           ((v_num_per_fiscal_year_prorate = 12) AND
           (v_depr_when_acquired_flag = 'YES'))) THEN
          OPEN cur_remlyf_1(inquiry_books.book_type_code,
                            inquiry_books.asset_id,
                            l_period_counter);
          FETCH cur_remlyf_1
            INTO x_remaining_life, x_remaining_life_dpis;
          CLOSE cur_remlyf_1;
        ELSE
          OPEN cur_remlyf_2(inquiry_books.book_type_code,
                            inquiry_books.asset_id,
                            l_period_counter);
          FETCH cur_remlyf_2
            INTO x_remaining_life, x_remaining_life_dpis;
          CLOSE cur_remlyf_2;
        END IF;
      ELSIF (v_depr_when_acquired_flag = 'NO') THEN
        OPEN cur_remlyf_1(inquiry_books.book_type_code,
                          inquiry_books.asset_id,
                          l_period_counter);
        FETCH cur_remlyf_1
          INTO x_remaining_life, x_remaining_life_dpis;
        CLOSE cur_remlyf_1;
      ELSE
        OPEN cur_remlyf_2(inquiry_books.book_type_code,
                          inquiry_books.asset_id,
                          l_period_counter);
        FETCH cur_remlyf_2
          INTO x_remaining_life, x_remaining_life_dpis;
        CLOSE cur_remlyf_2;
      END IF;
    ELSE
      OPEN cur_remlyf_5(inquiry_books.book_type_code,
                        inquiry_books.asset_id,
                        l_period_counter);
      FETCH cur_remlyf_5
        INTO x_remaining_life, x_remaining_life_dpis;
      CLOSE cur_remlyf_5;
      
      IF x_remaining_life < 0 THEN
        x_remaining_life := inquiry_books.life_in_months;
      ELSE
        x_remaining_life := inquiry_books.life_in_months -
                            x_remaining_life;
      END IF;
      IF x_remaining_life_dpis < 0 THEN
        x_remaining_life_dpis := inquiry_books.life_in_months;
      ELSE
        x_remaining_life_dpis := inquiry_books.life_in_months -
                                 x_remaining_life_dpis;
      END IF;
    END IF;
    
    x_remaining_life      := greatest(x_remaining_life, 0);
    x_remaining_life_dpis := greatest(x_remaining_life_dpis, 0);
  END LOOP;
END bhsc_qryaremlyf;

使用方法

1、指定截止到某个期间

2、直接查资产在当前的剩余寿命

写代码也要符合基本法
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle EBS 视频教程 FA模块
10-20
Oracle EBS 视频教程 FA模块 一共15讲
Oracle EBS R12 FA资产模块 核心标准表（Table）
qq_41206741的博客
05-26 4120
–存储资产的基础信息 SELECT * FROM fa_additions_b fab; –存储资产的事务处理历史(新增、转移、调整、报废、重建等) SELECT * FROM fa_transaction_headers fth; –存储资产成本变动信息 SELECT * FROM fa_books fb; –存储资产的分配信息(员工、费用帐户、地点) SELECT * FROM fa_distribution_history fdh; –存储资产折旧汇总信息 SELECT * FROM fa_deprn
Oracle EBS FA折旧回滚的分录追溯
贤时间
02-23 577
FA模块向子分类账和总账追溯分为两部分：事务表和折旧，但是FA相关表做其实关联计划外折旧的分录会被遗漏的原因：如果已经当月折旧，运行完成折旧后，又进行了计划外折旧，因为计划折旧时又要区分【是否进行当月摊销调整】，此时Oracle会自动把计划外折旧前的当月的折旧给回滚掉，折旧回滚程序会清理FA_DEPRN_SUMMARY和FA_DEPRN_DETAIL中的数据（清理的数据会进入历史表fa_deprn_detail_h、fa_deprn_summary_h）
linux合并fa文件_（原创）FA 期间重新打开
weixin_28778547的博客
12-28 249
FA 期间被认为是 关闭后不再能打开。虽然不知道强行打开具体会有什么问题出现， 但是还是避免不了客户误操作导致期间关闭，所以重新打开的需求将是很迫切的。我在一次事件中做过这样的操作， 测试了运行折旧， 和创建凭证， 以及核对资产的折旧金额， 均没发现问题。下面把修改过程共享下， 需要提醒的是 我在测试环境下通过， 如果要用到正式环境，请自己再仔细核对数据， 仅做学习之用。某个客户在7月1 号结6...
oracle折旧,Oracle EBS FA 获取累计折旧
weixin_35939140的博客
04-12 490
FUNCTION get_ltd_deprn(p_asset_id IN NUMBER,p_book_type_code IN VARCHAR2,p_rate_source_rule IN VARCHAR2 DEFAULT NULL,p_dist_id IN NUMBER DEFAULT 0,p_period_ctr IN NUMBER ...
oracle ebs fa_category_books,FA有用的脚本 - Oracle EBS R12 - ITPUB论坛－中国专业的IT技术社区...
weixin_34404671的博客
04-07 608
本帖最后由 daisyshen1208 于 2014-6-4 20:04 编辑*-------------------------------------------------------------------------------------------*//*新增，报废------------------------------------------------------------...
EBS-FA-固定资产-开发总结
12-26
EBS固定资产FA开发常用表结构及描述，EBS固定资产FA模块学习总结，EBS固定资产方面的查询SQL
Oracle-ERP-EBS-资产模块FA概要培训.ppt
最新发布
05-03
Oracle_ERP_EBS_资产模块FA概要培训.ppt
EBS FA 资产
12-21
oracle ebs资料，此份文档为EBS FA 资产的介绍。oracle ebs资料，此份文档为EBS FA 资产的介绍。
automatic-ebs-snapshots：用于管理自动AWS EBS快照的脚本
01-30
automatic-ebs-snapshots：用于管理自动AWS EBS快照的脚本
OracleEBS-应用开发培训.pptx
04-16
OracleEBS-应用开发培训
ORACLE_EBS_FA_常用表结构_资产库表
06-24
ORACLE_EBS_FA_常用表结构_资产库表
ORACLE EBS 基础设置要点简介（1）
2301_76957510的博客
04-01 1234
首先需要说明的是，本系列文档假定读者已经具备基本的系统相关使用知识与技能(例如，能够基本领会“ORACLE EBS系统应用基础概述”中的内容)，故所讨论的内容仅限于笔者认为从系统使用与实际业务两方面来看比较重要或者容易存疑的问题，并不能面面俱到，旨在帮助读者掌握核心、抓住要点（详尽内容必须参考ORACLE相关官方文档）。文中为讨论需要所附图文均取自ORACLE EBS 的测试环境（Vision Demo），版本以R12.1.1为主，辅之以版本R11.5.10，界面语言主要为中文（必要时辅之以英文）。两个EB
Oracle EBS R12 FA资产模块 查询资产大类和小类对应的折旧费用账户
qq_41206741的博客
07-05 744
SELECT c.segment1 资产大类 ,c.segment2 资产小类 ,gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8 || '.' || gcc.s
Oracle EBS 资产类别查询SQL
savior8718的博客
06-08 778
Oracle EBS 资产类别查询SQL
Oracle EBS 根据FA事务处理追溯SLA到GL脚本
AlexLiu_2019的博客
05-29 456
Oracle EBS 根据FA事务处理追溯SLA到GL脚本
FA模块数据流
csdn13681123263的博客
03-30 268
--资产主表： SELECT * FROM FA_ADDITIONS_V FA WHERE FA.ASSET_ID = 10014; --资产账簿 SELECT * FROM FA_BOOKS_V ...
ORACLE EBS常用表及查询语句（最终整理版） .
03-19 2803
参考一：                                                              call fnd_global.APPS_INITIALIZE(1318,50583,401) select fnd_profile.VALUE('ORG_ID') FROM DUAL select * from hr_operating
计算机 ebs-26211
11-08
计算机EBS-26211是一台拥有强大性能和高效能的计算机。它具有先进的处理器和内存配置，能够快速处理复杂的计算任务和多媒体内容。 EBS-26211采用了最新的硬件技术，包括先进的处理器和显卡。这使得该计算机能够在运行大型软件程序和处理大量数据时提供出色的性能。同时，它还拥有大容量的固态硬盘，能够存储大量文件和数据。 这台计算机还配备了多个USB端口和其他外部接口，可以方便地连接各种外部设备，如打印机、扫描仪和存储设备。此外，它还具有高速的网络连接功能，可以通过有线或无线方式进行互联网连接。 除了性能强大，EBS-26211还注重安全性和稳定性。它采用了先进的防火墙和安全软件来保护计算机免受恶意软件和网络攻击的威胁。此外，它还具有稳定的操作系统和可靠的硬件组件，确保计算机的稳定运行。 EBS-26211还具有用户友好的界面和操作系统，使得用户能够轻松地使用和管理计算机。它提供了丰富的软件工具和应用程序，可以满足不同用户的需求，如办公应用、图像处理和游戏。 总之，计算机EBS-26211是一台高性能、强大而稳定的计算机。它适用于各种计算任务，无论是在办公室还是个人使用。它的强大性能和高效能将为用户带来良好的计算体验。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值