检查所有资产的剩余折旧年限

最新推荐文章于 2021-08-09 14:15:47 发布

转载最新推荐文章于 2021-08-09 14:15:47 发布 · 185 阅读

本文介绍了一个复杂的Oracle PL/SQL过程，用于计算固定资产的剩余折旧年限及月份。该过程涉及多个表的连接查询以及日期和期间计算逻辑，适用于财务管理系统中资产折旧的精确计算。

DECLARE
CURSOR c_asset IS
    SELECT fab.asset_id, fab.asset_number, fb.book_type_code
      FROM fa_additions_b fab, fa_books fb
     WHERE fab.asset_id = fb.asset_id
       AND fb.date_ineffective IS NULL
       AND fb.transaction_header_id_out IS NULL
       AND fb.period_counter_fully_retired IS NULL;

l_prorate_date DATE;
--l_book_type_code varchar2(20);
--l_asset_id number;
l_remaining_life_years NUMBER;
l_remaining_life_months NUMBER;
l_min_cpod              DATE;
l_num_per_fiscal_year   NUMBER;
dummy_num               NUMBER;
BEGIN

FOR i IN c_asset LOOP

    --get prorate date
    SELECT /*adjusted_recoverable_cost, */
     prorate_date
      INTO /*:Inquiry_books.adjusted_recoverable_cost, */ l_prorate_date
      FROM fa_books
     WHERE book_type_code = i.book_type_code
       AND asset_id = i.asset_id
       AND date_ineffective IS NULL;

    SELECT MIN(calendar_period_open_date)
      INTO l_min_cpod
      FROM fa_deprn_periods
     WHERE book_type_code = i.book_type_code;

    --get fiscal year
    SELECT number_per_fiscal_year
      INTO l_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 = i.book_type_code
               AND fab.asset_id = i.asset_id
               AND fab.book_type_code = fabc.book_type_code
               AND fab.transaction_header_id_out IS NULL);

    IF (l_num_per_fiscal_year = 12) THEN

      IF l_prorate_date < l_min_cpod THEN
        SELECT decode(fab.conversion_date,
                      NULL,
                      fab.life_in_months -
                      ((to_number(to_char(fcp1.end_date, 'YYYY')) * 12 +
                      fcp1.period_num) - (to_number(to_char(fcp2.end_date, 'YYYY')) * 12 +
                      fcp2.period_num)),
                      fab.life_in_months -
                      ((to_number(to_char(fcp1.end_date, 'YYYY')) * 12 +
                      fcp1.period_num) - (to_number(to_char(fcp3.end_date, 'YYYY')) * 12 +
                      fcp3.period_num)))
          INTO dummy_num
          FROM fa_books            fab,
               fa_calendar_periods fcp1, -- open
               fa_calendar_periods fcp2, -- prorate
               fa_calendar_periods fcp3, -- deprn_start
               fa_book_controls    fabc,
               fa_deprn_periods    fdp
         WHERE fab.asset_id = i.asset_id
           AND fab.book_type_code = i.book_type_code
           AND fab.transaction_header_id_out IS NULL
           AND fabc.book_type_code = fab.book_type_code
           AND fdp.period_counter =
               (SELECT MAX(dp.period_counter)
                  FROM fa_deprn_periods dp
                 WHERE dp.book_type_code = i.book_type_code)
           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 fcp1.start_date = fdp.calendar_period_open_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;

      ELSE

        SELECT decode(fab.conversion_date,
                      NULL,
                      fab.life_in_months -
                      (fdp1.period_counter - fdp2.period_counter),
                      fab.life_in_months -
                      (fdp1.period_counter - fdp3.period_counter))
          INTO dummy_num
          FROM fa_books         fab,
               fa_deprn_periods fdp1, -- open
               fa_deprn_periods fdp2, -- prorate
               fa_deprn_periods fdp3 -- deprn_start
         WHERE fab.asset_id = i.asset_id
           AND fab.book_type_code = i.book_type_code
           AND fab.transaction_header_id_out IS NULL
           AND fab.book_type_code = fdp1.book_type_code
           AND fdp1.period_counter =
               (SELECT MAX(dp.period_counter)
                  FROM fa_deprn_periods dp
                 WHERE dp.book_type_code = i.book_type_code)
           AND fab.book_type_code = fdp2.book_type_code
           AND (fab.prorate_date BETWEEN fdp2.calendar_period_open_date AND
               fdp2.calendar_period_close_date OR
               (fab.prorate_date > fdp2.calendar_period_close_date AND
               fdp2.period_close_date IS NULL))
           AND fab.book_type_code = fdp3.book_type_code
           AND fab.deprn_start_date BETWEEN fdp3.calendar_period_open_date AND
               fdp3.calendar_period_close_date;
      END IF;

    ELSE

      SELECT decode(fab.conversion_date,
                    NULL,
                    fab.life_in_months -
                    floor(months_between(fdp.calendar_period_close_date,
                                         fab.prorate_date)),
                    fab.life_in_months -
                    floor(months_between(fdp.calendar_period_close_date,
                                         fab.deprn_start_date)))
        INTO dummy_num
        FROM fa_books fab, fa_deprn_periods fdp
       WHERE fab.book_type_code = i.book_type_code
         AND fdp.book_type_code = i.book_type_code
         AND fab.asset_id = i.asset_id
         AND fab.date_ineffective IS NULL
         AND fdp.period_close_date IS NULL;
    END IF;

    IF (dummy_num < 1) THEN
      l_remaining_life_years := 0;
      l_remaining_life_months := 0;
      --output
      dbms_output.put_line(i.asset_number || '(' || i.book_type_code || '):' ||
                           ' Remaining years:' || l_remaining_life_years ||
                           ' Remaining months:' || l_remaining_life_months);
    ELSE
      l_remaining_life_years := floor(dummy_num / 12);
      l_remaining_life_months := MOD(dummy_num, 12);
      --output
      dbms_output.put_line(i.asset_number || '-' || i.book_type_code || ':' ||
                           ' Remaining years:' || l_remaining_life_years ||
                           ' Remaining months:' || l_remaining_life_months);
    END IF;

END LOOP;
END;