script for Creating Tax Reserve Adjustments in FA

script for Creating Tax Reserve Adjustments in FA

CREATE OR REPLACE PROCEDURE XXX_TAX_RESERVE_ADJUST(errbuf       OUT  VARCHAR2,
                                                                                                                              retcode      OUT  VARCHAR2,
                                                                                                                              p_asset_number IN   VARCHAR2,
                                                                                                                              p_fiscal_year  IN NUMBER,
                                                                                                                              p_tax_book     IN VARCHAR2)
AS
/* This Script will create Tax Reserve Adjustments for assets. The assets and their amounts can be stored in a temporary table
    whose structure is as follows;
CREATE TABLE XXX_tax_reserve_temp
                      (asset_number        VARCHAR2(50),
                       fiscal_Year         NUMBER,
                       deprn_amount        NUMBER,
                       tax_book            VARCHAR2(50),
                       error_message       VARCHAR2(4000),
                       process_status      VARCHAR2(1));
*/

   l_return_status      varchar2(5);
   x_return_status              BOOLEAN;
   l_faxcat                     BOOLEAN;
   l_dummy                      VARCHAR2(1);
   l_msg_count      number:= 0;
   l_msg_data      varchar2(4000);
   l_trans_rec      FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec     FA_API_TYPES.asset_hdr_rec_type;
   l_asset_fin_rec_adj    FA_API_TYPES.asset_fin_rec_type;
   l_asset_fin_rec_new   FA_API_TYPES.asset_fin_rec_type;
   l_asset_fin_mrc_tbl_new  FA_API_TYPES.asset_fin_tbl_type;
   l_inv_trans_rec    FA_API_TYPES.inv_trans_rec_type;
   l_inv_tbl        FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl    FA_API_TYPES.inv_rate_tbl_type;
   l_asset_deprn_rec_adj  FA_API_TYPES.asset_deprn_rec_type;
   l_asset_deprn_rec_new  FA_API_TYPES.asset_deprn_rec_type;
   l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
   l_inv_rec        FA_API_TYPES.inv_rec_type;
   l_group_reclass_options_rec  FA_API_TYPES.group_reclass_options_rec_type;
   l_trx_header_id NUMBER;
   l_unplanned_deprn_rec   FA_API_TYPES.unplanned_deprn_rec_type;


   l_transaction_header_id                    number(15);
   l_new_cost                                 NUMBER;
   l_cost                                     NUMBER;
   l_adjusted_cost                            NUMBER;
   l_date_placed_in_service                   DATE;
   l_prorate_date                             DATE;
   l_deprn_start_date                         DATE;
   l_ceiling_name                             VARCHAR2(30);
   l_bonus_rule                               VARCHAR2(30);
   l_deprn_method_code                        VARCHAR2(12);
   l_recoverable_cost                         NUMBER;
   l_adjusted_rate                            NUMBER;
   l_life_in_months                           NUMBER(4);
   l_salvage_value                            NUMBER;
   l_depreciate_flag                          VARCHAR2(3);
   l_rate_adjustment_factor                   NUMBER;
   l_reval_amortization_basis                 NUMBER;
   l_production_capacity                      NUMBER;
   l_perd_fully_reserved                      NUMBER(38);
   l_current_units                            NUMBER;
   l_asset_category_id                        NUMBER(15);
   l_asset_type                               VARCHAR2(11);
   l_rowid                                    VARCHAR2(100);
   l_tax_book                                 VARCHAR2(50);
   l_trx_date                                 DATE;
   l_fiscal_year                              NUMBER ;
   l_old_deprn_amount                         NUMBER;
   retire_count                               number:=0;
   h_status BOOLEAN := TRUE;
   CURSOR main_csr IS
   SELECT rowid frowid,
          asset_number,
          fiscal_year,
          deprn_amount,
    tax_book
   FROM XXX_tax_reserve_temp
   WHERE process_status = 'N'
   AND   asset_number = NVL(p_asset_number, asset_number)
   AND   fiscal_year  = NVL(p_fiscal_year, fiscal_year)
   AND   tax_book     = NVL(p_tax_book,    tax_book);

   CURSOR asset_csr(v_asset_number VARCHAR2) IS
   SELECT asset_id
   FROM fa_additions_b
   WHERE asset_number = v_asset_number;
   l_asset_id        NUMBER(15);
   l_asset_number    VARCHAR2(15);
   l_header_seq_id   NUMBER;
   l_deprn_resv      NUMBER;

   l_error_tbl      okl_Accounting_util.error_message_type;
   l_error_message     VARCHAR2(2000);
   l_error_message1     VARCHAR2(2000);
   CURSOR tax_book_csr IS
   SELECT book_type_code
   FROM fa_book_controls
   WHERE set_of_books_id = okl_accounting_util.get_set_of_books_id
   AND  book_class = 'TAX';
   CURSOR curr_fiscal_csr(v_tax_book VARCHAR2) IS 
   SELECT fadp.fiscal_year
   FROM fa_deprn_periods fadp
   WHERE fadp.book_type_code = v_tax_book
   AND fadp.period_close_date is null;

   CURSOR dtl_csr(v_asset_id VARCHAR2, v_tax_book VARCHAR2) IS
 SELECT fab.cost,
            fab.adjusted_cost,
            fab.date_placed_in_service,
      fab.prorate_date,
            fab.deprn_start_date,
            fab.ceiling_name,
      fab.bonus_rule,
            fab.deprn_method_code, fab.recoverable_cost,
      fab.adjusted_rate, fab.life_in_months, fab.salvage_value,
      fab.depreciate_flag, fab.rate_adjustment_factor,
      nvl(fab.reval_amortization_basis,0),
      nvl(fab.production_capacity,0),
      nvl(fab.period_counter_fully_reserved,0),
            fa.current_units,
            fa.asset_category_id,
            fa.asset_type
        FROM  fa_books fab,
                fa_additions_b fa
 where fab.asset_id = fa.asset_id
    AND fa.asset_id = v_asset_id
 AND fab.book_type_code = v_tax_book
 AND fab.date_ineffective is null;
   CURSOR trx_date_csr(v_asset_id NUMBER, v_fiscal_year NUMBER, v_tax_book VARCHAR2) IS
     SELECT dp.calendar_period_close_date
     FROM apps.fa_deprn_periods dp
     WHERE dp.period_counter =
            (SELECT    max(dp1.period_counter)
      FROM fa_deprn_periods dp1, fa_deprn_summary ds1
      WHERE dp1.fiscal_year    = v_fiscal_year
      and dp1.period_counter = ds1.period_counter
      and dp1.book_type_code = v_tax_book
      AND ds1.asset_id = v_asset_id
      AND ds1.book_type_code = v_tax_book)
      AND dp.book_type_code =  v_tax_book;
   CURSOR check_trx(v_asset_id NUMBER, v_tax_book VARCHAR2, v_trx_date DATE) IS
   SELECT 1 FROM fa_transaction_headers
   WHERE asset_id = v_asset_id
   AND book_type_code = v_tax_book
   AND transaction_type_code = 'TAX'
   AND transaction_date_entered = v_trx_date;
   CURSOR retire_csr(v_asset_id NUMBER, v_tax_book VARCHAR2) IS
   SELECT count(1)
   FROM fa_transaction_headers
   WHERE transaction_header_id =
        (SELECT max(transaction_header_id)
        FROM fa_transaction_headers
      WHERE asset_id = v_asset_id
      AND book_type_code = l_tax_book)
   AND transaction_type_code = 'FULL RETIREMENT'
   AND retire_count < 2;
   l_retire_count NUMBER;
   l_current_fiscal_year NUMBER;
 
   CURSOR check_fiscal_csr(v_asset_id NUMBER, v_curr_fiscal_year NUMBER, v_tax_book VARCHAR2, v_given_year NUMBER) IS 
   SELECT 1 FROM DUAL
   WHERE v_given_year IN (
   SELECT DISTINCT fadp.fiscal_year
   FROM fa_deprn_periods fadp, fa_deprn_summary fads, fa_books bk
   WHERE fadp.book_type_code = v_tax_book
   AND fads.asset_id = v_asset_id
   AND fads.deprn_source_code = 'DEPRN'
   AND fadp.period_counter = fads.period_counter
   AND fadp.fiscal_year < v_curr_fiscal_year
   AND bk.book_type_code = v_tax_book
   AND bk.asset_id = v_asset_id
   AND bk.date_ineffective IS NULL
   AND bk.date_placed_in_service <= fadp.calendar_period_close_date);

----transaction_header_id
----book_type_code
----asset_id
----transaction_type_code = 'TAX'
----transaction_date_entered = 31-Oct-2008 for 2008
----date_effective = sysdate
----transaction_name = comments
----
PROCEDURE GET_DEPRN_SUM(p_asset_id    IN NUMBER,
                        p_fiscal_year IN NUMBER,
                        p_tax_book    IN VARCHAR2,
                        x_deprn_amount OUT NUMBER,
      x_deprn_resv   OUT NUMBER,
      x_return_status OUT VARCHAR2)
IS
   l_period_counter_adjusted   NUMBER;
   l_transaction_date_entered  DATE;
   l_book_type_code     VARCHAR2(100);
   l_fiscal_year        NUMBER;
   dummy_num            number;
   dummy_char           varchar2(10);
   dummy_bool           boolean;
   l_bonus_deprn_rsv number;
   l_bonus_ytd_deprn number;
   l_bonus_deprn_amount number;
   l_ytd_deprn2         NUMBER;
   l_ytd_deprn1         NUMBER;
   l_ytd_deprn          NUMBER;
   l_prv_period_counter NUMBER;
   period_check         NUMBER;
   l_asset_id           NUMBER; 
   l_return_status      VARCHAR2(1);
   l_fiscal_ytd_deprn   NUMBER;
   CURSOR ast_csr IS
   SELECT asset_id,
          asset_number
   FROM fa_additions_b fa
   WHERE asset_id = p_asset_id;

   CURSOR c1(v_fiscal_year NUMBER, v_book_type_code VARCHAR2, v_asset_id NUMBER) IS
   SELECT dp.period_counter,
       dp.calendar_period_close_date
   FROM fa_deprn_periods dp
   WHERE dp.period_counter = (SELECT max(dp1.period_counter)
           FROM fa_deprn_periods dp1, fa_deprn_summary ds1
        WHERE dp1.fiscal_year = v_fiscal_year
         and dp1.period_counter = ds1.period_counter
        and dp1.book_type_code = v_book_type_code
        and ds1.asset_id = v_asset_id
        and ds1.book_type_code = v_book_type_code)
           and dp.book_type_code = v_book_type_code;
BEGIN
      l_return_status := 'S';
 
      OPEN c1(p_fiscal_year, p_tax_book, p_asset_id);
      FETCH c1 INTO l_period_counter_adjusted, l_transaction_date_entered;
   IF c1%NOTFOUND THEN
      l_return_status := 'E';
   END IF;
      CLOSE c1;
 
   FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN PROC ' || l_period_counter_adjusted || ' ' || l_transactioN_date_entered);
 
   IF (l_return_status = 'S') THEN
  
         fa_query_balances_pkg.query_balances(
                        X_asset_id    => p_asset_id,
                        X_book    => p_tax_book,
                        X_period_ctr   => l_period_counter_adjusted,
                        X_dist_id   => 0,
                        X_run_mode   => 'ADJUSTED',
                        X_cost    => dummy_num,
                        X_deprn_rsv   => l_ytd_deprn2,
                        X_reval_rsv   => dummy_num,
                        X_ytd_deprn   => dummy_num,
                        X_ytd_reval_exp  => dummy_num,
                        X_reval_deprn_exp  => dummy_num,
                        X_deprn_exp   => dummy_num,
                        X_reval_amo   => dummy_num,
                        X_prod    => dummy_num,
                        X_ytd_prod   => dummy_num,
                        X_ltd_prod   => dummy_num,
                        X_adj_cost   => dummy_num,
                        X_reval_amo_basis  => dummy_num,
                        X_bonus_rate   => dummy_num,
                        X_deprn_source_code  => dummy_char,
                        X_adjusted_flag  => dummy_bool,
                        X_transaction_header_id => -1,
                        X_bonus_deprn_rsv  => l_bonus_deprn_rsv,
                        X_bonus_ytd_deprn  => l_bonus_ytd_deprn,
                        X_bonus_deprn_amount  => l_bonus_deprn_amount);


             FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN2=>' || l_ytd_deprn2);

          SELECT min(dp.period_counter) - 1
          INTO l_prv_period_counter
          FROM fa_deprn_periods dp
          WHERE dp.fiscal_year = p_fiscal_year
          AND dp.book_type_code =p_tax_book;
          SELECT count(*) into period_check
          FROM fa_deprn_periods
          WHERE book_Type_code = p_tax_book
          AND period_counter = l_prv_period_counter;
          IF (period_check = 0) then
              l_prv_period_counter := l_prv_period_counter + 1;
          END IF;
             fa_query_balances_pkg.query_balances(
                    X_asset_id => p_asset_id,
                    X_book     => p_tax_book,
                    X_period_ctr => l_prv_period_counter,
                    X_dist_id => 0,
                    X_run_mode => 'ADJUSTED',
                    X_cost => dummy_num,
                    X_deprn_rsv => l_ytd_deprn1,
                    X_reval_rsv => dummy_num,
                    X_ytd_deprn => dummy_num,
                    X_ytd_reval_exp => dummy_num,
                    X_reval_deprn_exp => dummy_num,
                    X_deprn_exp => dummy_num,
                    X_reval_amo => dummy_num,
                    X_prod => dummy_num,
                    X_ytd_prod => dummy_num,
                    X_ltd_prod => dummy_num,
                    X_adj_cost => dummy_num,
                    X_reval_amo_basis => dummy_num,
                    X_bonus_rate => dummy_num,
                    X_deprn_source_code => dummy_char,
                    X_adjusted_flag => dummy_bool,
                    X_transaction_header_id => -1,
                    X_bonus_deprn_rsv => l_bonus_deprn_rsv,
                    X_bonus_ytd_deprn => l_bonus_ytd_deprn,
                    X_bonus_deprn_amount => l_bonus_deprn_amount);

            FND_FILE.PUT_LINE(FND_FILE.LOG,'DEPRN1=>' || l_ytd_deprn1);
          l_ytd_deprn := NVL(l_ytd_deprn2,0) - NVL(l_ytd_deprn1,0);
          x_deprn_amount := NVL(l_ytd_deprn,0);

             fa_query_balances_pkg.query_balances(X_asset_id         => p_asset_id,
                                               X_book             => p_tax_book,
                                               X_period_ctr       => 0,
                                               X_dist_id          => 0,
                                               X_run_mode         => 'ADJUSTED',
                                               X_cost             => dummy_num,
                                               X_deprn_rsv        => x_deprn_resv,
                                               X_reval_rsv        => dummy_num,
                                               X_ytd_deprn        => dummy_num,
                                               X_ytd_reval_exp    => dummy_num,
                                               X_reval_deprn_exp  => dummy_num,
                                               X_deprn_exp        => dummy_num,
                                               X_reval_amo        => dummy_num,
                                               X_prod             => dummy_num,
                                               X_ytd_prod         => dummy_num,
                                               X_ltd_prod         => dummy_num,
                                               X_adj_cost         => dummy_num,
                                               X_reval_amo_basis  => dummy_num,
                                               X_bonus_rate       => dummy_num,
                                               X_deprn_source_code => dummy_char,
                                               X_adjusted_flag    => dummy_bool,
                                               X_transaction_header_id => -1,
                                               X_bonus_deprn_rsv  => l_bonus_deprn_rsv,
                                               X_bonus_ytd_deprn  => l_bonus_ytd_deprn,
                                               X_bonus_deprn_amount => l_bonus_deprn_amount);
      END IF;            
 
   x_return_status := l_return_status;
END GET_DEPRN_SUM;

PROCEDURE update_temp_tbl(p_error_msg IN VARCHAR2,
                                                            p_return_status IN VARCHAR2,
                                                            p_rowid     IN ROWID)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

     UPDATE XXX_tax_reserve_temp
     SET process_status = l_return_status,
         error_message  = p_error_msg
     WHERE rowid = p_rowid;

     COMMIT;
END update_temp_tbl;

BEGIN
   OPEN tax_book_csr;
   FETCH tax_book_csr INTO l_tax_book;
   CLOSE tax_book_csr;
 
   OPEN curr_fiscal_csr(l_tax_book);
   FETCH curr_fiscal_csr INTO l_current_fiscal_year;
   CLOSE curr_fiscal_csr;
 
   FND_FILE.PUT_LINE(FND_FILE.LOG,'Current Fiscal Year is ' || l_Current_Fiscal_year);
    
   l_error_message  := NULL;
   l_error_message1 := NULL;
 
   FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Asset Number' || CHR(9) ||
                                                                                  'Fiscal Year'  || CHR(9) ||
                                                                                 'Deprn Amount' || CHR(9) ||
                                                                                 'Tax Book'     || CHR(9) ||
                                                                                'Return Status'|| CHR(9) ||
                                                                                'Error Message' );
    
   FOR main_rec IN main_csr
   LOOP
     FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing the Asset Number=>' || main_rec.asset_number || ', ' || main_rec.fiscal_year
                           || ', ' || main_rec.tax_book);
       
    l_return_status := 'S';      

     OPEN asset_csr(main_rec.asset_number);
     FETCH asset_csr INTO l_asset_id;
     CLOSE asset_csr; 

     OPEN dtl_csr(l_asset_id, l_tax_book);
     FETCH dtl_csr INTO l_cost,
                        l_adjusted_cost,
                      l_date_placed_in_service,
                        l_prorate_date,
                     l_deprn_start_date,
                         l_ceiling_name,
                  l_bonus_rule,
                        l_deprn_method_code,
                  l_recoverable_cost,
                        l_adjusted_rate,
                  l_life_in_months,
                        l_salvage_value,
                  l_depreciate_flag,
                        l_rate_adjustment_factor,
                  l_reval_amortization_basis,
                  l_production_capacity,
                  l_perd_fully_reserved,
                        l_current_units,
                        l_asset_category_id,
                        l_asset_type;
    CLOSE dtl_csr;
 l_tax_book := main_rec.tax_book;
 l_old_deprn_amount := 0;
 
 OPEN check_fiscal_csr(l_asset_id , l_current_fiscal_year, l_tax_book, main_rec.fiscal_year);
 FETCH check_fiscal_csr INTO l_dummy;
 IF (check_fiscal_csr%NOTFOUND) THEN
        l_return_status := 'T';
     l_error_message := 'No Data Exists for the Given Financial Year';
     FND_FILE.PUT_LINE(FND_FILE.LOG,'No Data Exists for the Given Financial Year');
    END IF;
 CLOSE check_fiscal_csr;

 IF (l_return_status = 'S') THEN
     OPEN trx_date_csr(l_asset_id, main_rec.fiscal_year, l_tax_book);
        FETCH trx_date_csr INTO l_trx_date;
     CLOSE trx_Date_csr;

     FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction DAte for the Fiscal Year is ' || l_trx_date);

        l_dummy := NULL;

     OPEN check_trx(l_asset_id , l_tax_book , l_trx_date);
     FETCH check_trx INTO l_dummy;
     CLOSE check_trx;

        IF (l_dummy IS NOT NULL) THEN   
        FND_FILE.PUT_LINE(FND_FILE.LOG,'This Asset is Already processed for the given financial Year');
     l_return_status := 'D';
     l_error_message := 'Asset Already Processed Previously';
     END IF;
    END IF;
  
 IF (l_return_status = 'S') THEN

        GET_DEPRN_SUM(p_asset_id      => l_asset_id,
                      p_fiscal_year   => main_rec.fiscal_year,
                      p_tax_book      => l_tax_book,
                      x_deprn_amount  => l_old_deprn_amount,
       x_deprn_resv    => l_deprn_resv,
       x_return_status => l_return_status);
     
     IF (l_return_status <> 'S') THEN

      l_return_status := 'D';
   l_error_message := 'You did not depreciate assets during this fiscal year.  Please perform your depreciation adjustment in a later fiscal year.';
   FND_FILE.PUT_LINE(FND_FILE.LOG,'You did not depreciate assets during this fiscal year.  Please perform your depreciation adjustment in a later fiscal year.');

  ELSE
    
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Old Depreciation Amount is ' || l_old_deprn_amount);     
      FND_FILE.PUT_LINE(FND_FILE.LOG,'Deprn Reserve is  ' || l_deprn_resv);     

      IF ABS(main_rec.deprn_amount - l_old_deprn_amount + l_deprn_resv) > ABS(l_recoverable_cost) THEN
          FND_FILE.PUT_LINE(FND_FILE.LOG,'Depreciation Cannot Exceeed the Cost');
       l_Error_message := 'Depreciation Cannot Exceeed the Cost';
       l_return_status := 'C';
      END IF;

     END IF;

 END IF;
 IF (l_return_status = 'S') THEN

        IF (l_old_deprn_amount = main_rec.deprn_amount) THEN
            FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Same as the New Amount. Not Proceeding Further' );
            l_Error_message := 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Same as the New Amount. Not Proceeding Further';
            l_return_status := 'P';
       ELSE      
            FND_FILE.PUT_LINE(FND_FILE.LOG, 'The Old Depreciation Amount ' || l_old_deprn_amount || ' is Different then the New Amount. Proceeding Further');
      l_Error_message := NULL;
            l_return_status := 'S';
       END IF;
 END IF;


     IF (l_return_status = 'S') THEN
        OPEN retire_csr(l_asset_id, l_tax_book);
        FETCH retire_csr INTO l_retire_count;
        CLOSE retire_csr;

        IF (l_retire_count > 0) THEN
        l_return_status := 'R';
           FND_FILE.PUT_LINE(FND_FILE.LOG,'You cannot perform a Reserve Adjustment on a fully retired asset.');
     l_Error_message := 'You cannot perform a Reserve Adjustment on a fully retired asset.';

        ELSE

           FND_FILE.PUT_LINE(FND_FILE.LOG,'Asset is Not Retired, Proceeding Further');
     l_error_message := NULL;
       
        END IF;

     END IF;

     IF (l_return_status = 'S') THEN  
         SELECT fa_transaction_headers_s.nextval
         INTO l_transaction_header_id
         FROM dual;
         OPEN trx_date_csr(l_asset_id, main_rec.fiscal_year, l_tax_book);
         FETCH trx_date_csr INTO l_trx_date;
         CLOSE trx_date_csr;
         FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting Transaction in FA Transaction Header');

         FA_TRANSACTION_HEADERS_PKG.insert_row(X_ROWID                         => l_rowid,
                                               X_TRANSACTION_HEADER_ID         => l_transaction_header_id,
                                               X_BOOK_TYPE_CODE                => l_tax_book,
                                               X_ASSET_ID                      => l_asset_id,
                                               X_TRANSACTION_TYPE_CODE         => 'TAX',
                                               X_TRANSACTION_DATE_ENTERED      => l_trx_date,
                                               X_DATE_EFFECTIVE                => sysdate,
                                               X_LAST_UPDATE_DATE              => sysdate,
                                               X_LAST_UPDATED_BY               => fnd_global.user_id,
                                               X_RETURN_STATUS                 => x_return_status,
                                               X_CALLING_FN                    => 'CUSTOM');
         IF (x_return_status) THEN
             FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction Created Successfully');
             l_return_status := 'S';
    l_error_message := NULL;
         ELSE
             FND_FILE.PUT_LINE(FND_FILE.LOG,'Could not Create Transaction ');
    l_error_message := 'Could not Create Transaction ';
             l_return_status := 'E';
         END IF;
     
         IF (l_return_status = 'S') THEN
             l_faxcat := FA_TRX_APPROVAL_PKG.faxcat(
                                      X_book              => 'NFC TAX BOOK',
                                      X_asset_id          => l_asset_id,
                                      X_trx_type          => 'TAX',
                                      X_trx_date          => sysdate,
                                      X_init_message_flag => 'NO');
             IF (l_faxcat) THEN
                 FND_FILE.PUT_LINE(FND_FILE.LOG,'FaxCat Success');
             ELSE
                 FND_FILE.PUT_LINE(FND_FILE.LOG,'FaxCat Error');
             END IF;

             h_status := fa_txrsv_pkg.faxtxa (X_ASSET_NUMBER             => l_asset_number,
                                              X_ASSET_ID                 => l_asset_id,          
                                              X_CURRENT_UNITS            => l_current_units        ,
                                              X_ASSET_CATEGORY_ID        => l_asset_category_id      ,  
                                              X_ASSET_TYPE               => l_asset_type          ,
                                              X_NEW_COST                 => l_cost          ,
                                              X_SALVAGE_VALUE            => l_salvage_value     ,
                                              X_NEW_RECOVERABLE_COST     => l_recoverable_cost    ,
                                              X_RECOVERABLE_COST         => l_recoverable_cost     ,
                                              X_ADJUSTED_RATE            => l_adjusted_rate      ,
                                              X_CEILING_NAME             => l_ceiling_name      ,
                                              X_BONUS_RULE               => l_bonus_rule       ,
                                              X_DEPRN_METHOD_CODE        => l_deprn_method_code,   
                                              X_LIFE_IN_MONTHS           => l_life_in_months    ,
                                              X_DATE_PLACED_IN_SERVICE   => l_date_placed_in_service    ,
                                              X_PRORATE_DATE             => l_prorate_date     ,
                                              X_DEPRN_START_DATE         => l_deprn_start_date  ,  
                                              X_DEPRECIATE_FLAG          => l_depreciate_flag     ,
                                              X_PRODUCTION_CAPACITY      => l_production_capacity,  
                                              X_BOOK_TYPE_CODE           => l_tax_book,
                                              X_TRANSACTION_HEADER_ID    => l_transaction_header_id   ,
                                              X_RATE_ADJUSTMENT_FACTOR   => l_rate_adjustment_factor  ,
                                              X_REVAL_AMORTIZATION_BASIS => l_reval_amortization_basis ,
                                              X_OLD_DEPRN_RSV            => l_old_deprn_amount,
                                              X_NEW_DEPRN_RSV            => main_rec.deprn_amount,
                                              X_FISCAL_YEAR              => main_rec.fiscal_year,
                                              X_PERIOD_COUNTER_RSV       => l_perd_fully_reserved ,
                                              X_SYSDATE_VAL              => sysdate );
              IF (h_status) THEN
                  FND_FILE.PUT_LINE(FND_FILE.LOG,'faxtxa proc is Success');
                  l_return_status := 'S';
              ELSE
                  FND_FILE.PUT_LINE(FND_FILE.LOG,'faxtxa proc is Error');
      l_error_message := 'faxtxa proc is Error';
                  l_return_status := 'E';
              END IF;
       ELSE
           FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction was not successful, not proceeding further');
 
       END IF;
     END IF;

     IF (l_return_status = 'E') THEN
     l_error_tbl.DELETE;
     OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
   l_Error_message := ' ';
 
   FOR i IN 1..l_error_tbl.COUNT
   LOOP
           l_error_message := l_error_message || '|' || l_error_tbl(i);   
   END LOOP;
 
  END IF;
     update_temp_tbl(p_error_msg     => l_error_message,
                     p_return_status => l_return_status,
                     p_rowid         => main_rec.frowid);

  FND_FILE.PUT_LINE(FND_FILE.OUTPUT, main_rec.asset_number|| CHR(9) || main_rec.fiscal_year
                                                           || CHR(9) || main_rec.deprn_amount
                || CHR(9) || main_rec.tax_book
                || CHR(9) || l_return_status
                || CHR(9) || l_error_message);
   END LOOP; ---of Assets
 


END XXX_TAX_RESERVE_ADJUST;

Script for Creating Unplanned Depreciation (FA)

Script for Creating Unplanned Depreciation (FA)

CREATE OR REPLACE PROCEDURE XXX_UPDATE_UNPLANNED(errbuf         OUT  VARCHAR2,
                                                                                                                           retcode        OUT  VARCHAR2,
                                                                                                                           p_asset_number IN   VARCHAR2,
                                                                                                                          p_tax_book     IN   VARCHAR2)
AS           
-----This Procedure will do the following
                  ------Uncheck the depreciation flag
                  ------Chenage the Depr Method to STL
                  ------Update the unplanned amount
                  ------Change the Depr method back
---You can remove the functionality which you do not need.
---Assuming that data to be processed exists in a temporary table whose structure is as follows:
/*
CREATE TABLE XXX_unplan_depr_temp
                      (asset_number        VARCHAR2(50),
                       account             VARCHAR2(100),
                       deprn_amount        NUMBER,
                       tax_book            VARCHAR2(50),
                       error_message       VARCHAR2(4000),
                       process_status      VARCHAR2(1));
*/
   l_return_status      VARCHAR2(1);
   l_msg_count      NUMBER := 0;
   l_msg_data      VARCHAR2(4000);
   l_trans_rec      FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec     FA_API_TYPES.asset_hdr_rec_type;
   l_asset_hdr_rec1     FA_API_TYPES.asset_hdr_rec_type;
   l_asset_fin_rec_adj    FA_API_TYPES.asset_fin_rec_type;
   l_asset_fin_rec_adj1    FA_API_TYPES.asset_fin_rec_type;
   l_asset_fin_rec_new   FA_API_TYPES.asset_fin_rec_type;
   l_asset_fin_mrc_tbl_new  FA_API_TYPES.asset_fin_tbl_type;
   l_inv_trans_rec    FA_API_TYPES.inv_trans_rec_type;
   l_inv_tbl        FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl    FA_API_TYPES.inv_rate_tbl_type;
   l_asset_deprn_rec_adj  FA_API_TYPES.asset_deprn_rec_type;
   l_asset_deprn_rec_new  FA_API_TYPES.asset_deprn_rec_type;
   l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
   l_inv_rec        FA_API_TYPES.inv_rec_type;
   l_group_reclass_options_rec  FA_API_TYPES.group_reclass_options_rec_type;
   l_trx_header_id              NUMBER;
   l_unplanned_deprn_rec   FA_API_TYPES.unplanned_deprn_rec_type;

   l_deprn_method_code          VARCHAR2(50);
   l_life_in_months             NUMBER;
   l_ccid                       NUMBER;
  
   CURSOR main_csr IS
   SELECT asset_number,
          deprn_amount,
    account,
    tax_book,
    rowid frowid
   FROM XXX_unplan_depr_temp
   WHERE asset_number = NVL(p_asset_number, asset_number)
   AND   tax_book     = NVL(p_tax_book,    tax_book)
   AND   process_status = 'N';

   CURSOR asset_csr(v_asset_number VARCHAR2, v_tax_book VARCHAR2) IS
   SELECT fa.asset_id, deprn_method_code , life_in_months , depreciate_flag
   FROM apps.fa_books book, fa_additions_b fa
   WHERE book.asset_id =fa.asset_id
   AND  book.book_type_code = v_tax_book
   AND  date_ineffective IS NULL
   AND fa.asset_number = v_asset_number;
  
   CURSOR ccid_csr(v_account VARCHAR2) IS
   SELECT code_Combination_id
   FROM gl_code_combinations_kfv
   WHERE concatenated_segments = v_account;

   l_asset_id      NUMBER;
   l_header_seq_id NUMBER;
   l_depreciate_flag VARCHAR2(10);

   l_error_tbl      okl_Accounting_util.error_message_type;
   l_error_message     VARCHAR2(2000);
  
  
   PROCEDURE update_temp_tbl(p_error_msg     IN VARCHAR2,
                             p_return_status IN VARCHAR2,
                             p_rowid         IN ROWID)
   IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN

     UPDATE XXX_unplan_depr_temp
     SET  process_status = l_return_status,
          error_message  = p_error_msg
     WHERE rowid = p_rowid;
 
     COMMIT;
   END update_temp_tbl;

BEGIN

   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Asset Number'    || CHR(9) ||
                                     'Account'      || CHR(9) ||
                                     'Deprn Amount' || CHR(9) ||
                                     'Tax Book'     || CHR(9) ||
                                     'Process Status ' || CHR(9) ||
                                     'Error Message');
   FOR main_rec IN main_csr
   LOOP
           l_return_status := 'S';
           l_error_message := '';
  
          FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing Asset Number ' || main_rec.asset_number);
  
      OPEN asset_csr(main_rec.asset_number, main_rec.tax_book);
      FETCH asset_csr INTO l_asset_id,
                        l_deprn_method_code,
         l_life_in_months,
         l_depreciate_flag;
      CLOSE asset_csr;  

-----Update the asset depreciation Flag to NO

   IF (l_depreciate_flag <> 'NO') THEN
 ----Remove the Depreciation Flag 
         fa_trans_api_pub.DO_ADJUSTMENT(P_API_VERSION           => 1.0,
                                                                                 p_init_msg_list         => FND_API.G_TRUE,
                                                                                 p_commit                => FND_API.G_FALSE,
                                                                                 p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
                                                                                 X_RETURN_STATUS         => l_return_status,
                                                                                X_MSG_COUNT             => l_msg_count,
                                                                                X_MSG_DATA              => l_msg_data,
                                                                               P_CALLING_FN            => 'XXX',
                                                                               X_TRANSACTION_HEADER_ID => l_trx_header_id,
                                                                               P_AMORTIZATION_START_DATE => sysdate,
                                                                               P_LAST_UPDATE_DATE        => SYSDATE,
                                                                               P_LAST_UPDATED_BY         => fnd_global.user_id,
                                                                               P_CREATED_BY              => fnd_global.user_id,
                                                                               P_CREATION_DATE           => sysdate,
                                                                               P_LAST_UPDATE_LOGIN       => 1,
                                                                              P_ASSET_ID                => l_asset_id,
                                                                              P_BOOK_TYPE_CODE          => main_rec.tax_book,
                                                                              P_DEPRECIATE_FLAG         => 'NO');
                                       
          FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status After Depreciation Flag Change is   ' || l_return_status);
  
         IF (l_return_status <> 'S') THEN
         l_error_tbl.DELETE;
         OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
         l_error_message := NULL;
         FOR i IN 1..l_error_tbl.COUNT
         LOOP
              FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_tbl(i));
        l_error_message := l_error_message || '|' || l_error_tbl(i);
         END LOOP;
            ROLLBACK;
          ELSE
            COMMIT WORK;
          END IF;
   
   END IF;
-----Update the Depreciation Method to STL
      IF (l_return_status = 'S') AND (l_deprn_method_code <> 'STL') THEN
    
           fa_trans_api_pub.DO_ADJUSTMENT(P_API_VERSION           => 1.0,
                                                  p_init_msg_list         => FND_API.G_TRUE,
                                                  p_commit                => FND_API.G_FALSE,
                                                  p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
                                          X_RETURN_STATUS         => l_return_status,
                                          X_MSG_COUNT             => l_msg_count,
                                          X_MSG_DATA              => l_msg_data,
                                          P_CALLING_FN            => 'XXX',
                                          X_TRANSACTION_HEADER_ID => l_trx_header_id,
                                          P_AMORTIZATION_START_DATE => sysdate,
                                          P_LAST_UPDATE_DATE        =>     SYSDATE,
                                          P_LAST_UPDATED_BY         => fnd_global.user_id,
                                          P_CREATED_BY              => fnd_global.user_id,
                                          P_CREATION_DATE           => sysdate,
                                          P_LAST_UPDATE_LOGIN       => 1,
                                          P_ASSET_ID                => l_asset_id,
                                          P_BOOK_TYPE_CODE          => main_rec.tax_book,
            p_deprn_method_code       => 'STL',
            p_life_in_months          => l_life_in_months);
           
        FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status After Method Change is  ' || l_return_status);

        IF (l_return_status <> 'S') THEN
        l_error_tbl.DELETE;
        OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
        l_error_message := '';
        FOR i IN 1..l_error_tbl.COUNT
        LOOP
             FND_FILE.PUT_LINE(FND_FILE.LOG,l_error_tbl(i));
          l_error_message := l_error_message || '|' || l_error_tbl(i);
        END LOOP;
           ROLLBACK;       
         ELSE
           COMMIT WORK;
         END IF;
     END IF;
     OPEN ccid_csr(main_rec.account);
  FETCH ccid_csr INTO l_ccid;
  IF (ccid_csr%NOTFOUND) THEN
       FND_FILE.PUT_LINE(FND_FILE.LOG,'Account is Not correct, not updating the unplanned Depreciation');
    l_return_status := 'A';
   END IF;
   CLOSE ccid_csr;

----Create the Unplanned Depreciation

   IF (l_return_status = 'S') THEN
         FA_TRANS_API_PUB.DO_UNPLANNED(P_API_VERSION                  => 1.0,
                                         p_init_msg_list                => 'T',
                                        p_commit                       => FND_API.G_FALSE,
                                       p_validation_level             => FND_API.G_VALID_LEVEL_FULL,
                                       X_RETURN_STATUS                => l_return_status,
                                       X_MSG_COUNT                    => l_msg_count,
                                       X_MSG_DATA                     => l_msg_data,
                                       P_CALLING_FN                   => 'XXX',
                                       X_TRANSACTION_HEADER_ID        => l_trx_header_id,
                                       p_transaction_date_entered     => sysdate,
                                       p_amortization_start_date      => sysdate,
                                       P_ASSET_ID                     => l_asset_id,
                                       P_BOOK_TYPE_CODE               => main_rec.tax_book,
                                       P_CODE_COMBINATION_ID          => l_ccid,
                                       P_UNPLANNED_AMOUNT             => main_rec.deprn_amount,
                                       P_UNPLANNED_TYPE               => 'SWIFT',
                                       P_LAST_UPDATE_DATE             => sysdate,
                                       P_LAST_UPDATED_BY              => 1,
                                       P_CREATED_BY                   => 1,
                                       P_CREATION_DATE                => sysdate,
                                       P_LAST_UPDATE_LOGIN            => 1);
         FND_FILE.PUT_LINE(FND_FILE.LOG, 'Status after call of Unplanned is ' || l_return_status);
         IF (l_return_status <> 'S') THEN
         l_error_tbl.DELETE;
         OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
         l_error_message := NULL;
         FOR i IN 1..l_error_tbl.COUNT
         LOOP
               FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_tbl(i));
         l_error_message := l_error_message || '|' || l_error_tbl(i);
         END LOOP;
            ROLLBACK;
         ELSE
            COMMIT WORK;
         END IF;
  
   END IF;

----Reset the Depreciation method to the old Value

   IF (l_return_status = 'S') THEN       
           fa_trans_api_pub.DO_ADJUSTMENT(P_API_VERSION           => 1.0,
                                                     p_init_msg_list         => FND_API.G_TRUE,
                                                    p_commit                => FND_API.G_FALSE,
                                                     p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
                                          X_RETURN_STATUS         => l_return_status,
                                          X_MSG_COUNT             => l_msg_count,
                                          X_MSG_DATA              => l_msg_data,
                                          P_CALLING_FN            => 'XXX',
                                          X_TRANSACTION_HEADER_ID => l_trx_header_id,
                                          P_AMORTIZATION_START_DATE => sysdate,
                                          P_LAST_UPDATE_DATE        =>     SYSDATE,
                                          P_LAST_UPDATED_BY              => fnd_global.user_id,
                                          P_CREATED_BY                   => fnd_global.user_id,
                                          P_CREATION_DATE                => sysdate,
                                          P_LAST_UPDATE_LOGIN            => 1,
                                          P_ASSET_ID                     => l_asset_id,
                                          P_BOOK_TYPE_CODE               => main_rec.tax_book,
                                          p_deprn_method_code            => l_deprn_method_code,
                                          p_life_in_months               => l_life_in_months);
                                       
         FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status After Method Change Back is  ' || l_return_status);
  
        IF (l_return_status <> 'S') THEN
         l_error_tbl.DELETE;
         OKL_ACCOUNTING_UTIL.GET_ERROR_msg(l_error_tbl);
         l_error_message := NULL;
         FOR i IN 1..l_error_tbl.COUNT
         LOOP
              FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_tbl(i));
        l_error_message := l_error_message || '|' || l_error_tbl(i);
         END LOOP;
            ROLLBACK;
         ELSE
            COMMIT WORK;
         END IF;
  
   END IF;
  
  
  
   update_temp_tbl(p_error_msg     => l_error_message,
                      p_return_status => l_return_status,
                      p_rowid         => main_rec.frowid);
      
   FND_FILE.PUT_LINE(FND_FILE.OUTPUT,main_rec.asset_number || CHR(9) ||
                                     main_rec.account      || CHR(9) ||
                                    main_rec.deprn_amount || CHR(9) ||
                                   main_rec.tax_book     || CHR(9) ||
                                   l_return_status || CHR(9) ||
                                  l_error_message);
        
   END LOOP;      

EXCEPTION
   WHEN OTHERS THEN
       FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
END XXX_UPDATE_UNPLANNED;

Users

Users

Any person who needs to access the Oracle Applications should have a user id. A user is registered by System Administrator (or anybody having System Administrator responsibility). Often, first the person is registered in HR module as an employee or contractor and then associated with the user. The following screen shows the user definition screen.

A user is assigned a user name and password. The password needs to be reset on the first login of the user.

The person field is a list of value coming from HR. If the person is already registered in HR, his name is selected in the Person field.

User can be assigned the access for a limited period by giving Effective Dates.

User can be forced to change password periodically by giving password expiration date values.

User is also assigned the responsibilities. The responsibilities can be assigned for a particular period by giving effective dates.

Oracle Applications comes with default some default users like SYSADMIN, APPSMGR, and AUTOINSTALL. These users are used for carrying out administrative tasks in Oracle Applications.

Technical Details on User Definition

User definition is stored in FND_USER table. This table is owned by FND module. The main fields of this table are as follows:

User_ID Automatically generated unique number

User_Name Name as entered on the user definition screen

Employee_ID Employee ID from HR module. Populated if the Employee name is associated with the user.

Party_ID Party_ID from HZ_PARTIES table. Each user is also created in HZ_PARTIES table as a party.

  

Whenever user creates or updates a transaction in any module, the transaction record is stamped with the user id. The user id is usually stored as CREATED_BY or LAST_UPDATED_BY field in the respective transaction table.

User ID of the current user can be accessed using a package FND_GLOBAL. Suppose, we need to get the current user id in a custom program. We can get the user id using the following code:

l_user_id := FND_GLOBAL.USER_ID;

When a user record is created, it is also automatically inserted in the workflow role table (WF_ROLES). This table is used for sending workflow notification to the user whenever a workflow requires such notification to be sent.

Menus

Menus

When different functions are organized in a hierarchical fashion, it is called a menu. Menu is a component of oracle applications which is used for navigation purpose. A menu can be made of different functions or it can have sub-menus under it. A menu is a tree kind of structure, the lowest node being a function. Top menu is usually assigned to a responsibility. The following figure shows menu structure for GL Budget Super Menu:

A custom menu can also be created and assigned to a custom responsibility. When creating a new custom menu, one should start at the bottom, assigning functions to sub-menus and then finally assigning sub-menus and functions to a top menu.

The following screen is used for defining new menu:

The important fields in this screen are described below:

Menu:  Short Name of the Menu

User Menu Name: User defined name of the Menu

Menu Type:  Choose ‘Standard’

Description:  Description of the Menu.

The menu items appear in the sequence given. The sequence can be 1,2,3..or 10,20,30…etc. For each menu item, a prompt should be given if we want that menu item prompt to appear. Then we can either specify a Submenu or a Function. The sub-menu should have been already defined as a menu before using in the menu.

By Clicking ‘View Tree’ button, it is possible to see the menu in graphical format.

Technical Details of the Menu

A menu detail is stored in the following tables:

FND_MENUS: This table contains Menu Header information.

FND_MENU_ENTRIES: This table contains detail of all the entries in the menu. It contains columns Entry Sequence, Sub-Menu ID and Function ID.

Some importent API's regarding Concurrent Program

Some importent API's regarding Concurrent Program

-- Name
  --   FND_CONCURRENT.Cancel_Request.
  -- Purpose
  --    It Cancels given Concurrent Request.
  --
  -- Arguments (input)
  --    request_id - request id of the request you want to cancel.
  --
  --   (out args)
  --    message    - API will fill the message with any errors while canceling
  --                 request.
  --
  -- Returns:
  --    Returns TRUE if success or FALSE on failure.
  Function Cancel_Request( Request_Id   in NUMBER,
                           Message      out NOCOPY VARCHAR2) return boolean is
    ret_val          number;
    submitter        number;
    request_missing  exception;
    no_privilege     exception;

======================================================================================
-- Procedure
--   enable_program
--
-- Purpose
--   enable or disable the concurrent program.
--
-- Arguments
--   program_short_name  - Short name of the program.
--   program_application - Application of the program.
--   enabled       - 'Y' or 'N' values.
--
PROCEDURE enable_program(short_name        IN VARCHAR2,
                         application       IN VARCHAR2,
                         enabled           IN VARCHAR2);
      
=======================================================================================
  --
  -- Name
  --   set_repeat_options
  -- Purpose
  --   Called before submitting request if the request to be submitted
  --   is a repeating request.
  --
  -- Arguments
  --   repeat_time - Time of day at which it has to be repeated
  --   repeat_interval  - Frequency at which it has to be repeated
  --   - This will be used/applied only when repeat_time
  --   - is NULL ( non null repeat_interval overrides )
  --   repeat_unit - Unit for repeat interval. Default is DAYS.
  --   - MONTHS/DAYS/HOURS/MINUTES
  --   repeat_type - Apply repeat interval from START or END of request
  --   - default is START. START/END
  --   repeat_end_time  - Time at which the repetition should be stopped
  --   incrment_dates   - 'Y' if dates should be incremented each run,
  --                      otherwise 'N'
  --
  function set_repeat_options (repeat_time IN varchar2 default NULL,
                      repeat_interval  IN number   default NULL,
                      repeat_unit      IN varchar2 default 'DAYS',
                      repeat_type      IN varchar2 default 'START',
                      repeat_end_time  IN varchar2 default NULL,
                      increment_dates  IN varchar2 default NULL) return boolean;