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

 

View Code

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 ;