Friday, October 21, 2011

Derive Fixed Assets(FA) Depreciation programmatically

Deriving Year-to-Date/Accumulated Depreciation, Ytd bonus, Accumulated bonus in Fixed Assets using a query will not always give an accurate depreciation amount because of various factors like transactions made against an asset, reinstatement, retirement, adjustment etc. 
DEPRECIATION - BEHIND THE SCENES
When you run depreciation, Oracle Assets processes each asset according to the transactions that you have performed on the asset since the last depreciation.


FA_DEPRN_DETAIL
For each depreciable asset, Oracle Assets inserts one row per distribution line that was active at any time during the current period.
FA_DEPRN_SUMMARY
Oracle Assets inserts one row per depreciable asset. 


Use the following API to always get accurate results:

CREATE OR REPLACE FUNCTION xxfa_accdeprn_get_fnc (
   p_asset_id   NUMBER,
   p_btc        VARCHAR2
)
   RETURN NUMBER
IS
/*********************************************************************************
 * FUNCTION *  XXFA_ACCDEPRN_GET_FNC
 * DESCRIPTION *  The following function is required to derive the Consolidated and Monthly
 *  Unplanned depreciation details for Fixed Assets.
 * PARAMETERS * ==========
 * NAME                TYPE     DESCRIPTION
 * -----------------      ---------- ---------------------------------------------
 * p_asset_id            IN      Asset ID
 * p_btc                 IN        Book Type Code
 *********************************************************************************/
   l_deprn_reserve            NUMBER;
   l_ytd_deprn                 NUMBER; -- Year-to-Date Depreciation
   l_bonus_deprn_reserve  NUMBER;
   l_bonus_ytd_deprn       NUMBER;
   l_reval_reserve            NUMBER;
   dummy_num                NUMBER;
   dummy_char                VARCHAR2 (100);
   dummy_bool                BOOLEAN;
   l_book_type_code        VARCHAR2 (20)  := p_btc;
   l_run_mode                 VARCHAR2 (20)  := 'STANDARD';
   l_asset_id                   NUMBER         := p_asset_id;
BEGIN
--Standard API to derive the current Accumulated Depreciation for the Asset
   apps.fa_query_balances_pkg.query_balances
                                 (x_asset_id                  => l_asset_id,
                                  x_book                       => l_book_type_code,
                                  x_period_ctr                => 0,
                                  x_dist_id                    => 0,
                                  x_run_mode                => l_run_mode,
                                  x_cost                       => dummy_num,
                                  x_deprn_rsv                => l_deprn_reserve,
                                  x_reval_rsv                 => l_reval_reserve,
                                  x_ytd_deprn                => l_ytd_deprn,
                                  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_reserve,
                                  x_bonus_ytd_deprn         => l_bonus_ytd_deprn,
                                  x_bonus_deprn_amount    => dummy_num
                                 );
   RETURN l_deprn_reserve; -- Accumulated Depreciation, Use
l_ytd_deprn as Return variable to calculate YTD Depreciation
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END xxfa_accdeprn_get_fnc;

Note: We can always create a procedure or function with the return type as a record type and derive ytd, accumulated depreciations or ytd, accumulated bonus reserved based on your requirement

Friday, October 7, 2011

Update FND User Password

Following is a script to update FND User password programatically from the database in Oracle:

DECLARE
   l_updpwd   BOOLEAN;
BEGIN
   l_updpwd := fnd_user_pkg.changepassword ('SVISHNUB', 'ebspro');

   IF l_updpwd
   THEN

      COMMIT;
      DBMS_OUTPUT.put_line ('success');
   ELSE
      DBMS_OUTPUT.put_line ('failed');
   END IF;
END;



Thursday, October 6, 2011

Script to assign a specific Responsibility to a User

SET SERVEROUTPUT ON;
DECLARE
   l_resp_name    VARCHAR2 (100) := '&enter_resp_name';
   l_appl_id      NUMBER;
   l_resp_id      NUMBER;
   l_user_name    VARCHAR2 (100) := '&enter_user_name';
   l_user_id      NUMBER;
   l_sec_grp_id   NUMBER;
   l_start_date   VARCHAR2 (11);
   l_desc         VARCHAR2 (255);
   l_count        NUMBER DEFAULT 0;
BEGIN
   BEGIN
      SELECT application_id, responsibility_id
        INTO l_appl_id, l_resp_id
        FROM fnd_responsibility_vl
       WHERE responsibility_name = l_resp_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
   DBMS_OUTPUT.put_line ('l_appl_id= ' || l_appl_id);
   DBMS_OUTPUT.put_line ('l_resp_id= ' || l_resp_id);
   BEGIN
      SELECT user_id
        INTO l_user_id
        FROM fnd_user
       WHERE user_name = l_user_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
   DBMS_OUTPUT.put_line ('l_user_id= ' || l_user_id);
   BEGIN
      SELECT COUNT (security_group_id)
        INTO l_count
        FROM fnd_user_resp_groups
       WHERE user_id = l_user_id AND responsibility_id = l_resp_id;
      DBMS_OUTPUT.put_line ('l_count= ' || l_count);
      IF l_count = 0
      THEN
         SELECT security_group_id, start_date, end_date
           INTO l_sec_grp_id, l_start_date, l_desc
           FROM fnd_user_resp_groups
          WHERE     user_id IS NOT NULL
                AND responsibility_id = l_resp_id
                AND ROWNUM = 1;
      ELSE
         SELECT security_group_id, start_date, end_date
           INTO l_sec_grp_id, l_start_date, l_desc
           FROM fnd_user_resp_groups
          WHERE user_id = l_user_id AND responsibility_id = l_resp_id;
      END IF;
      DBMS_OUTPUT.put_line ('l_sec_grp_id= ' || l_sec_grp_id);
      DBMS_OUTPUT.put_line ('l_start_date= ' || l_start_date);
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
   fnd_user_resp_groups_api.upload_assignment (
      user_id                         => l_user_id,               --in number,
      responsibility_id               => l_resp_id,               --in number,
      responsibility_application_id   => l_appl_id,               --in number,
      security_group_id               => l_sec_grp_id, --in number default null,
      start_date                      => TO_DATE (l_start_date, 'DD-MON-RRRR'), -- in date,
      end_date                        => NULL,                      --in date,
      description                     => NVL (l_desc, l_resp_name), --in varchar2,
      update_who_columns              => 'N'        --in varchar2 default null
                                            /* 'n' = leave old who vals.  'y' (default) = update who to current*/
      );
   DBMS_OUTPUT.put_line ('Successfully updated ');
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception ' || SUBSTR (SQLERRM, 1, 300));
END;