Oracle apps FA asset addtion API Script

/* Formatted on 2013/06/21 18:48 (Formatter Plus v4.8.8) */
DECLARE
   l_trans_rec             fa_api_types.trans_rec_type;
   l_dist_trans_rec        fa_api_types.trans_rec_type;
   l_asset_hdr_rec         fa_api_types.asset_hdr_rec_type;
   l_asset_desc_rec        fa_api_types.asset_desc_rec_type;
   l_asset_cat_rec         fa_api_types.asset_cat_rec_type;
   l_asset_type_rec        fa_api_types.asset_type_rec_type;
   l_asset_hierarchy_rec   fa_api_types.asset_hierarchy_rec_type;
   l_asset_fin_rec         fa_api_types.asset_fin_rec_type;
   l_asset_deprn_rec       fa_api_types.asset_deprn_rec_type;
   l_asset_dist_rec        fa_api_types.asset_dist_rec_type;
   l_asset_dist_tbl        fa_api_types.asset_dist_tbl_type;
   l_inv_tbl               fa_api_types.inv_tbl_type;
   l_inv_rate_tbl          fa_api_types.inv_rate_tbl_type;
   l_return_status         VARCHAR2 (1);
   l_mesg_count            NUMBER;
   l_mesg                  VARCHAR2 (4000);
   l_expense_ccid          NUMBER;
   l_location_ccid         NUMBER;
   l_category_id           NUMBER;
   l_asset_key_ccid        NUMBER;

   CURSOR cur_fa_addition
   IS
      SELECT   *
          FROM oomco_fa_upload
         WHERE TO_CHAR (dateplacedinservice, 'DD-MON-YYYY') = '30-APR-2005'
           AND asset_type IN ('CAPITALIZED')
           AND assetnumber NOT IN (SELECT asset_number
                                     FROM fa_additions_b)
      ORDER BY dateplacedinservice DESC;
BEGIN
   FOR c1 IN cur_fa_addition
   LOOP
      BEGIN
         SELECT code_combination_id
           INTO l_expense_ccid
           FROM gl_code_combinations
          WHERE enabled_flag = 'Y'
            AND    segment1
                || '.'
                || segment2
                || '.'
                || segment3
                || '.'
                || segment4
                || '.'
                || segment5
                || '.'
                || segment6
                || '.'
                || segment7
                || '.'
                || segment8 = c1.expense_account;
      EXCEPTION
         WHEN OTHERS
         THEN
            --l_expense_ccid := NULL;
            DBMS_OUTPUT.put_line (   ' Expense account does not exist'
                                  || c1.expense_account
                                 );
      END;

      BEGIN
         SELECT location_id
           INTO l_location_ccid
           FROM fa_locations
          WHERE    segment1
                || '-'
                || segment2
                || '-'
                || segment3
                || '-'
                || segment4
                || '-'
                || segment5
                || '-'
                || segment6 =
                      c1.country
                   || '-'
                   || c1.region
                   || '-'
                   || c1.wilatay
                   || '-'
                   || c1.sbu
                   || '-'
                   || c1.cost_centre
                   || '-'
                   || c1.asset_location;
      EXCEPTION
         WHEN OTHERS
         THEN
            --l_location_ccid := NULL;
            DBMS_OUTPUT.put_line (   'Location does not exist'
                                  || c1.country
                                  || '-'
                                  || c1.region
                                  || '-'
                                  || c1.wilatay
                                  || '-'
                                  || c1.sbu
                                  || '-'
                                  || c1.cost_centre
                                  || '-'
                                  || c1.asset_location
                                 );
      END;

      BEGIN
         SELECT category_id
           INTO l_category_id
           FROM fa_categories
          WHERE segment1 || '-' || segment2 || '-' || segment3 =
                      c1.majorcategory
                   || '-'
                   || c1.minor_category
                   || '-'
                   || c1.subminor;
      EXCEPTION
         WHEN OTHERS
         THEN
            --l_category_id := NULL;
            DBMS_OUTPUT.put_line (   ' Category does not exist'
                                  || c1.majorcategory
                                  || '-'
                                  || c1.minor_category
                                  || '-'
                                  || c1.subminor
                                 );
      END;

      BEGIN
         SELECT code_combination_id
           INTO l_asset_key_ccid
           FROM fa_asset_keywords
          WHERE segment1 || '.' || segment2 = c1.asset_key;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (' Asset Key not exist' || c1.asset_key);
      END;

      DBMS_OUTPUT.ENABLE (10000000);
      fa_srvr_msg.init_server_message;
-- desc info
      l_asset_hdr_rec.asset_id := oomco_asset_id.NEXTVAL;
      l_asset_desc_rec.asset_number := c1.assetnumber;
      l_asset_desc_rec.description := c1.description;
      l_asset_desc_rec.asset_key_ccid :=l_asset_key_ccid;
-- cat info
      l_asset_cat_rec.category_id := l_category_id;
--type info
      l_asset_type_rec.asset_type := c1.asset_type;
-- fin info
      l_asset_fin_rec.COST := c1.asset_cost;
      l_asset_fin_rec.original_cost := c1.asset_cost;
      l_asset_fin_rec.date_placed_in_service := c1.dateplacedinservice;
      l_asset_fin_rec.prorate_date := c1.dateplacedinservice;
      l_asset_fin_rec.life_in_months := c1.lifeinmonths;
      l_asset_fin_rec.depreciate_flag := 'YES';
-- deprn info
      l_asset_deprn_rec.ytd_deprn := NULL;
      l_asset_deprn_rec.deprn_reserve := NULL;
      l_asset_deprn_rec.bonus_ytd_deprn := 0;
      l_asset_deprn_rec.bonus_deprn_reserve := 0;
-- book / trans info
      l_asset_hdr_rec.book_type_code := 'CORPORATE BOOK';
-- distribution info
      l_asset_dist_rec.units_assigned := c1.units;
      l_asset_dist_rec.expense_ccid := l_expense_ccid;
      l_asset_dist_rec.location_ccid := l_location_ccid;
      l_asset_dist_rec.assigned_to := NULL;
      l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
      l_asset_dist_tbl (1) := l_asset_dist_rec;
-- call the api
      fa_addition_pub.do_addition
                            (
-- std parameters
                             p_api_version               => 1.0,
                             p_init_msg_list             => fnd_api.g_false,
                             p_commit                    => fnd_api.g_false,
                             p_validation_level          => fnd_api.g_valid_level_full,
                             p_calling_fn                => NULL,
                             x_return_status             => l_return_status,
                             x_msg_count                 => l_mesg_count,
                             x_msg_data                  => l_mesg,
-- api parameters
                             px_trans_rec                => l_trans_rec,
                             px_dist_trans_rec           => l_dist_trans_rec,
                             px_asset_hdr_rec            => l_asset_hdr_rec,
                             px_asset_desc_rec           => l_asset_desc_rec,
                             px_asset_type_rec           => l_asset_type_rec,
                             px_asset_cat_rec            => l_asset_cat_rec,
                             px_asset_hierarchy_rec      => l_asset_hierarchy_rec,
                             px_asset_fin_rec            => l_asset_fin_rec,
                             px_asset_deprn_rec          => l_asset_deprn_rec,
                             px_asset_dist_tbl           => l_asset_dist_tbl,
                             px_inv_tbl                  => l_inv_tbl
                            );
      COMMIT;
--dump messages
      l_mesg_count := fnd_msg_pub.count_msg;

      IF l_mesg_count > 0
      THEN
         l_mesg :=
               CHR (10)
            || SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_first, fnd_api.g_false),
                       1,
                       250
                      );
         DBMS_OUTPUT.put_line (l_mesg);

         FOR i IN 1 .. (l_mesg_count - 1)
         LOOP
            l_mesg :=
               SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false),
                       1,
                       250
                      );
            DBMS_OUTPUT.put_line (l_mesg);
         END LOOP;

         fnd_msg_pub.delete_msg ();
      END IF;

      IF (l_return_status <> fnd_api.g_ret_sts_success)
      THEN
         DBMS_OUTPUT.put_line ('FAILURE');
      /*UPDATE oomco_fa_upload
         SET error_message = 'FAILURE'
       WHERE assetnumber = c1.assetnumber;

      COMMIT;*/
      ELSE
         DBMS_OUTPUT.put_line ('SUCCESS');
         DBMS_OUTPUT.put_line (   'THID'
                               || TO_CHAR (l_trans_rec.transaction_header_id)
                              );
         DBMS_OUTPUT.put_line ('ASSET_ID'
                               || TO_CHAR (l_asset_hdr_rec.asset_id)
                              );
         DBMS_OUTPUT.put_line ('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
      END IF;

      COMMIT;
   END LOOP;
END;

Fixed Asset detail query

Fixed Assets details Query
------------------------------

SELECT DISTINCT fab.description, fcb.segment1 majorcategory,
fcb.segment2 minor_category, fcb.segment3 subminor,
fab.current_units units, fbv.COST asset_cost,
fbv.original_cost, fab.asset_number,
NVL ((SELECT deprn_amount
FROM fa_deprn_detail
WHERE asset_id = fab.asset_id
AND deprn_source_code = 'D'),
0
) deprn_amount,
fbv.COST
- NVL ((SELECT deprn_amount
FROM fa_deprn_detail
WHERE asset_id = fab.asset_id
AND deprn_source_code = 'D'),
0
) nbv_value,
fl.segment1 country, fl.segment2 region, fl.segment3 wilatay,
fl.segment4 sbu, fl.segment5 cost_centre,
fl.segment6 lasset_location,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 expense_account,
NULL clearing_account, fbv.date_placed_in_service,
fbv.prorate_date, 'STL' depreciationmethod,
fbv.life_in_months lifeinmonths, fab.asset_type,
(SELECT segment1 || '.' || segment2
FROM fa_asset_keywords
WHERE code_combination_id = fab.asset_key_ccid
AND ROWNUM = 1) asset_key
FROM apps.fa_additions_v fab,
apps.fa_books_v fbv,
apps.fa_categories_b fcb,
apps.fa_deprn_periods fdp,
apps.fa_distribution_history fdh,
apps.gl_code_combinations gcc,
apps.fa_locations fl
WHERE 1 = 1
AND fab.asset_id = fbv.asset_id
AND fcb.category_id = fab.asset_category_id
AND fbv.transaction_header_id_out IS NULL
AND fdp.book_type_code = fbv.book_type_code
AND fdh.asset_id = fbv.asset_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.location_id = fl.location_id
AND fbv.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL
--and fbv.date_placed_in_service!=fbv.PRORATE_DATE
--and fbv.COST! =fbv.ORIGINAL_COST
--and fab.asset_number like '%TEST%'

Fixed Asset Location creation script

Fixed Asset Location creation script
------------------------------------------

/* Formatted on 2013/04/20 02:21 (Formatter Plus v4.8.8) */
DECLARE
l_application_short_name VARCHAR2 (50);
l_key_flex_code VARCHAR2 (4);
l_structure_number NUMBER;
l_validation_date DATE;
l_combination_id NUMBER;
l_keyval_status BOOLEAN;
l_concat_segments fnd_flex_ext.segmentarray;
CURSOR cur_syscomb
IS
SELECT DISTINCT country, region, wilatay, sbu, cost_centre,
asset_location
FROM oomco_fa_upload
WHERE country
|| '-'
|| region
|| '-'
|| wilatay
|| '-'
|| sbu
|| '-'
|| cost_centre
|| '-'
|| asset_location NOT IN (
SELECT segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
FROM fa_locations)
ORDER BY 1;
BEGIN
fnd_global.apps_initialize (1131, 20563, 140, 0, -1);
FOR i_cur_outcomb IN cur_syscomb
LOOP
l_application_short_name := 'OFA';
l_key_flex_code := 'LOC#';
l_structure_number := 101;
l_validation_date := TO_DATE ('01-JAN-2013', 'DD-MON-YYYY');
--'USA-NY-SAN FRANCISCO-OFFICE1'
l_concat_segments (1) := i_cur_outcomb.country;
l_concat_segments (2) := i_cur_outcomb.region;
l_concat_segments (3) := i_cur_outcomb.wilatay;
l_concat_segments (4) := i_cur_outcomb.sbu;
l_concat_segments (5) := i_cur_outcomb.cost_centre;
l_concat_segments (6) := i_cur_outcomb.asset_location;
l_concat_segments (7) := null;
-- l_concat_segments (8) := i_cur_outcomb.segment8;
/* l_concat_segments (1) := '11';
l_concat_segments (2) := '111';
l_concat_segments (3) := '511105';
l_concat_segments (4) := '01';
l_concat_segments (5) := '1006';
l_concat_segments (6) := '102';
l_concat_segments (7) := '00000';
l_concat_segments (8) := '0000';*/
/* l_concat_segments (1) := 'USA';
l_concat_segments (2) := 'NY';
l_concat_segments (3) := 'BALA';
l_concat_segments (4) := '1';*/
DBMS_OUTPUT.put_line ('API Started');
fnd_flex_server1.set_debugging ('6');
DBMS_OUTPUT.put_line
( 'Delimiter:'
|| fnd_flex_ext.get_delimiter
(l_application_short_name,
l_key_flex_code,
101
)
);
IF fnd_flex_ext.get_combination_id
(application_short_name => l_application_short_name,
key_flex_code => l_key_flex_code,
structure_number => 101,
validation_date => l_validation_date,
n_segments => 7,
segments => l_concat_segments,
combination_id => l_combination_id
)
THEN
DBMS_OUTPUT.put_line ('API Combination Created' || l_combination_id);
ELSE
DBMS_OUTPUT.put_line (fnd_message.get);
DBMS_OUTPUT.put_line ( 'API Not Combination Created'
|| l_combination_id
);
END IF;
END LOOP;
COMMIT;
END;

FA DELETION API SCRIPT

/* Formatted on 2013/05/14 13:47 (Formatter Plus v4.8.8) */
DECLARE
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_return_status VARCHAR2 (1);
l_mesg_count NUMBER := 0;
l_mesg_len NUMBER;
l_mesg VARCHAR2 (4000);
CURSOR cur_fa_addition
IS
SELECT asset_id
FROM apps.fa_additions ;
BEGIN
FOR c1 IN cur_fa_addition
LOOP
DBMS_OUTPUT.ENABLE (1000000);
fa_srvr_msg.init_server_message;
-- asset header info
l_asset_hdr_rec.asset_id := c1.asset_id;
l_asset_hdr_rec.book_type_code := 'CORPORATE BOOK';
fa_deletion_pub.do_delete
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
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_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => NULL,
px_asset_hdr_rec => l_asset_hdr_rec
);
l_mesg_count := fnd_msg_pub.count_msg;
IF l_mesg_count > 0
THEN
l_mesg :=
CHR (10)
|| SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_first, fnd_api.g_false),
1,
250
);
DBMS_OUTPUT.put_line (l_mesg);
FOR i IN 1 .. (l_mesg_count - 1)
LOOP
l_mesg :=
SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false),
1,
250
);
DBMS_OUTPUT.put_line (l_mesg);
END LOOP;
fnd_msg_pub.delete_msg ();
END IF;
IF (l_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('FAILURE');
ELSE
DBMS_OUTPUT.put_line ('SUCCESS');
DBMS_OUTPUT.put_line ('ASSET_ID'
|| TO_CHAR (l_asset_hdr_rec.asset_id)
);
DBMS_OUTPUT.put_line ('BOOK: ' || l_asset_hdr_rec.book_type_code);
END IF;
END LOOP;
COMMIT;
END;

FA Asset Update API SCRIPT

Asset Update API SCRIPT IN R12 Fixed assets
----------------------------------------------------------

/* Formatted on 2013/05/06 13:01 (Formatter Plus v4.8.8) */
DECLARE
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_return_status VARCHAR2 (1);
l_mesg_count NUMBER := 0;
l_mesg_len NUMBER;
l_mesg VARCHAR2 (4000);
l_asset_id NUMBER;
--l_new_life FA_API_TYPES.asset_hr_attr_rec_type;
CURSOR cur_fa_addition
IS
SELECT *
FROM oomco_fa_upload
WHERE assetnumber IN (
SELECT a.asset_number
FROM fa_additions_b a, fa_books_v b
WHERE a.asset_id = b.asset_id
AND b.COST IN (0)
AND a.asset_number IN (SELECT assetnumber
FROM oomco_fa_upload));
BEGIN
FOR c1 IN cur_fa_addition
LOOP
BEGIN
SELECT asset_id
INTO l_asset_id
FROM fa_additions_b
WHERE asset_number = c1.assetnumber;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( ' Asset number does not exit'
|| c1.assetnumber
);
END;
fnd_profile.put ('PRINT_DEBUG', 'Y');
DBMS_OUTPUT.ENABLE (1000000);
fa_srvr_msg.init_server_message;
fa_debug_pkg.initialize;
--asset header info
l_asset_hdr_rec.asset_id := l_asset_id;
l_asset_hdr_rec.book_type_code := 'CORPORATE BOOK';
-- fin info
l_asset_fin_rec_adj.COST := c1.asset_cost;
--l_new_life.life_in_months:=100;
fa_adjustment_pub.do_adjustment
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
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_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => 'ADJ_TEST_SCRIPT',
px_trans_rec => l_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
p_asset_fin_rec_adj => l_asset_fin_rec_adj,
x_asset_fin_rec_new => l_asset_fin_rec_new,
x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
px_inv_trans_rec => l_inv_trans_rec,
px_inv_tbl => l_inv_tbl,
-- px_inv_rate_tbl => l_inv_rate_tbl,
p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
x_asset_deprn_rec_new => l_asset_deprn_rec_new,
x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
p_group_reclass_options_rec => l_group_reclass_options_rec
);
DBMS_OUTPUT.put_line (l_return_status);
IF (l_return_status <> fnd_api.g_ret_sts_success)
THEN
fa_debug_pkg.dump_debug_messages (max_mesgs => 0);
l_mesg_count := fnd_msg_pub.count_msg;
IF l_mesg_count > 0
THEN
l_mesg :=
SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_first, fnd_api.g_false),
1,
512
);
DBMS_OUTPUT.put_line (SUBSTR (l_mesg, 1, 255));
FOR i IN 1 .. l_mesg_count - 1
LOOP
l_mesg :=
SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_next,
fnd_api.g_false),
1,
512
);
DBMS_OUTPUT.put_line (SUBSTR (l_mesg, 1, 255));
END LOOP;
fnd_msg_pub.delete_msg ();
END IF;
ELSE
DBMS_OUTPUT.put_line ('SUCCESS');
COMMIT;
DBMS_OUTPUT.put_line ( 'THID'
|| TO_CHAR (l_trans_rec.transaction_header_id)
);
END IF;
END LOOP;
END;

FA ADDITION IMPORT API SCRIPT

FA ADDITION IMPORT API SCRIPT
------------------------------------------------

/* Formatted on 2013/04/20 03:32 (Formatter Plus v4.8.8) */
DECLARE
l_trans_rec fa_api_types.trans_rec_type;
l_dist_trans_rec fa_api_types.trans_rec_type;
l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
l_asset_desc_rec fa_api_types.asset_desc_rec_type;
l_asset_cat_rec fa_api_types.asset_cat_rec_type;
l_asset_type_rec fa_api_types.asset_type_rec_type;
l_asset_hierarchy_rec fa_api_types.asset_hierarchy_rec_type;
l_asset_fin_rec fa_api_types.asset_fin_rec_type;
l_asset_deprn_rec fa_api_types.asset_deprn_rec_type;
l_asset_dist_rec fa_api_types.asset_dist_rec_type;
l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
l_inv_tbl fa_api_types.inv_tbl_type;
l_inv_rate_tbl fa_api_types.inv_rate_tbl_type;
l_return_status VARCHAR2 (1);
l_mesg_count NUMBER;
l_mesg VARCHAR2 (4000);
l_expense_ccid NUMBER;
l_location_ccid NUMBER;
l_category_id NUMBER;
CURSOR cur_fa_addition
IS
SELECT *
FROM oomco_fa_upload
WHERE to_char(DATEPLACEDINSERVICE,'DD-MON-YYYY') ='30-APR-2005'
AND asset_type in ('CAPITALIZED')
and assetnumber NOT IN (SELECT asset_number
FROM fa_additions_b)
ORDER BY DATEPLACEDINSERVICE DESC ;
BEGIN
FOR c1 IN cur_fa_addition
LOOP
BEGIN
SELECT code_combination_id
INTO l_expense_ccid
FROM gl_code_combinations
WHERE enabled_flag = 'Y'
AND segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
|| '.'
|| segment7
|| '.'
|| segment8 = c1.expense_account;
EXCEPTION
WHEN OTHERS
THEN
--l_expense_ccid := NULL;
DBMS_OUTPUT.put_line ( ' Expense account does not exist'
|| c1.expense_account
);
END;
BEGIN
SELECT location_id
INTO l_location_ccid
FROM fa_locations
WHERE segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6 =
c1.country
|| '-'
|| c1.region
|| '-'
|| c1.wilatay
|| '-'
|| c1.sbu
|| '-'
|| c1.cost_centre
|| '-'
|| c1.asset_location;
EXCEPTION
WHEN OTHERS
THEN
--l_location_ccid := NULL;
DBMS_OUTPUT.put_line ( 'Location does not exist'
|| c1.country
|| '-'
|| c1.region
|| '-'
|| c1.wilatay
|| '-'
|| c1.sbu
|| '-'
|| c1.cost_centre
|| '-'
|| c1.asset_location
);
END;
BEGIN
SELECT category_id
INTO l_category_id
FROM fa_categories
WHERE segment1 || '-' || segment2 || '-' || segment3 =
c1.majorcategory
|| '-'
|| c1.minor_category
|| '-'
|| c1.subminor;
EXCEPTION
WHEN OTHERS
THEN
--l_category_id := NULL;
DBMS_OUTPUT.put_line ( ' Category does not exist'
|| c1.majorcategory
|| '-'
|| c1.minor_category
|| '-'
|| c1.subminor
);
END;
DBMS_OUTPUT.ENABLE (10000000);
fa_srvr_msg.init_server_message;
-- desc info
l_asset_hdr_rec.asset_id := oomco_asset_id.NEXTVAL;
l_asset_desc_rec.asset_number := c1.assetnumber;
l_asset_desc_rec.description := c1.description;
l_asset_desc_rec.asset_key_ccid := 1;
-- cat info
l_asset_cat_rec.category_id := l_category_id;
--type info
l_asset_type_rec.asset_type := c1.asset_type;
-- fin info
l_asset_fin_rec.COST := c1.asset_cost;
l_asset_fin_rec.original_cost := c1.asset_cost;
l_asset_fin_rec.date_placed_in_service := c1.dateplacedinservice;
l_asset_fin_rec.prorate_date:= c1.dateplacedinservice;
l_asset_fin_rec.life_in_months := c1.lifeinmonths;
l_asset_fin_rec.depreciate_flag := 'YES';
-- deprn info
l_asset_deprn_rec.ytd_deprn := NULL;
l_asset_deprn_rec.deprn_reserve := NULL;
l_asset_deprn_rec.bonus_ytd_deprn := 0;
l_asset_deprn_rec.bonus_deprn_reserve := 0;
-- book / trans info
l_asset_hdr_rec.book_type_code := 'CORPORATE BOOK';
-- distribution info
l_asset_dist_rec.units_assigned := c1.units;
l_asset_dist_rec.expense_ccid := l_expense_ccid;
l_asset_dist_rec.location_ccid := l_location_ccid;
l_asset_dist_rec.assigned_to := NULL;
l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
l_asset_dist_tbl (1) := l_asset_dist_rec;
-- call the api
fa_addition_pub.do_addition
(
-- std parameters
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_calling_fn => NULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
-- api parameters
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec => l_asset_desc_rec,
px_asset_type_rec => l_asset_type_rec,
px_asset_cat_rec => l_asset_cat_rec,
px_asset_hierarchy_rec => l_asset_hierarchy_rec,
px_asset_fin_rec => l_asset_fin_rec,
px_asset_deprn_rec => l_asset_deprn_rec,
px_asset_dist_tbl => l_asset_dist_tbl,
px_inv_tbl => l_inv_tbl
);
COMMIT;
--dump messages
l_mesg_count := fnd_msg_pub.count_msg;
IF l_mesg_count > 0
THEN
l_mesg :=
CHR (10)
|| SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_first, fnd_api.g_false),
1,
250
);
DBMS_OUTPUT.put_line (l_mesg);
FOR i IN 1 .. (l_mesg_count - 1)
LOOP
l_mesg :=
SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false),
1,
250
);
DBMS_OUTPUT.put_line (l_mesg);
END LOOP;
fnd_msg_pub.delete_msg ();
END IF;
IF (l_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('FAILURE');
/*UPDATE oomco_fa_upload
SET error_message = 'FAILURE'
WHERE assetnumber = c1.assetnumber;
COMMIT;*/
ELSE
DBMS_OUTPUT.put_line ('SUCCESS');
DBMS_OUTPUT.put_line ( 'THID'
|| TO_CHAR (l_trans_rec.transaction_header_id)
);
DBMS_OUTPUT.put_line ('ASSET_ID'
|| TO_CHAR (l_asset_hdr_rec.asset_id)
);
DBMS_OUTPUT.put_line ('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
END IF;
COMMIT;
END LOOP;
END;

FA COST UPDATE API

206474.1

Oracle Assets Common API Documentation Supplement [ID 206449.1]
139407

KS FA

http://docs.oracle.com/cd/E18727_01/doc.121/e13586/T293142T471917.htm#adjapwoinvscr

1.63

basic_rate
adjusted_rate
deprn_amount
----------------------------
declare
   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_return_status             VARCHAR2(1);
   l_mesg_count                number;
   l_mesg                      varchar2(512);
begin
   dbms_output.enable(10000000);
   FA_SRVR_MSG.Init_Server_Message;
   -- asset header info
   l_asset_hdr_rec.asset_id       := &asset_id;
   l_asset_hdr_rec.book_type_code := '&book';
   -- fin rec info
   l_asset_fin_rec_adj.cost       := &delta_cost;
   l_asset_fin_rec_adj.basic_rate   :=&basic;
   l_asset_fin_rec_adj.adjusted_rate:=&adje;
 
 
   FA_ADJUSTMENT_PUB.do_adjustment(
          -- std parameters
          p_api_version               => 1.0,
          p_init_msg_list             => FND_API.G_FALSE,
          p_commit                    => FND_API.G_FALSE,
          p_validation_level          => FND_API.G_VALID_LEVEL_FULL,
          p_calling_fn                => null,
          x_return_status             => l_return_status,
          x_msg_count                 => l_mesg_count,
          x_msg_data                  => l_mesg,
          -- api parameters
          px_trans_rec                => l_trans_rec,
          px_asset_hdr_rec            => l_asset_hdr_rec,
          p_asset_fin_rec_adj         => l_asset_fin_rec_adj,
          x_asset_fin_rec_new         => l_asset_fin_rec_new,
          x_asset_fin_mrc_tbl_new     => l_asset_fin_mrc_tbl_new,
          px_inv_trans_rec            => l_inv_trans_rec,
          px_inv_tbl                  => l_inv_tbl,
          p_asset_deprn_rec_adj       => l_asset_deprn_rec_adj,
          x_asset_deprn_rec_new       => l_asset_deprn_rec_new,
          x_asset_deprn_mrc_tbl_new   => l_asset_deprn_mrc_tbl_new,
          p_group_reclass_options_rec => l_group_reclass_options_rec
         );
   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;
   if l_mesg_count > 0 then
      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                    (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);
      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);
         dbms_output.put_line(l_mesg);
      end loop;
      fnd_msg_pub.delete_msg();
   end if;
   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
     dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
   end if;
end;
/
---------------------------------



declare
   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_return_status             VARCHAR2(1);
   l_mesg_count                number;
   l_mesg                      varchar2(512);
begin
   dbms_output.enable(10000000);
   FA_SRVR_MSG.Init_Server_Message;
   -- asset header info
   l_asset_hdr_rec.asset_id       := &asset_id;
   l_asset_hdr_rec.book_type_code := '&book';
   -- fin rec info
   l_asset_fin_rec_adj.cost       := &delta_cost;
   --l_asset_deprn_rec_adj.deprn_amount:=&depramt
   --l_asset_fin_rec_adj.basic_rate   :=&basic;
   --l_asset_fin_rec_adj.adjusted_rate:=&adje;
 
 
   FA_ADJUSTMENT_PUB.do_adjustment(
          -- std parameters
          p_api_version               => 1.0,
          p_init_msg_list             => FND_API.G_FALSE,
          p_commit                    => FND_API.G_FALSE,
          p_validation_level          => FND_API.G_VALID_LEVEL_FULL,
          p_calling_fn                => null,
          x_return_status             => l_return_status,
          x_msg_count                 => l_mesg_count,
          x_msg_data                  => l_mesg,
          -- api parameters
          px_trans_rec                => l_trans_rec,
          px_asset_hdr_rec            => l_asset_hdr_rec,
          p_asset_fin_rec_adj         => l_asset_fin_rec_adj,
          x_asset_fin_rec_new         => l_asset_fin_rec_new,
          x_asset_fin_mrc_tbl_new     => l_asset_fin_mrc_tbl_new,
          px_inv_trans_rec            => l_inv_trans_rec,
          px_inv_tbl                  => l_inv_tbl,
          p_asset_deprn_rec_adj       => l_asset_deprn_rec_adj,
          x_asset_deprn_rec_new       => l_asset_deprn_rec_new,
          x_asset_deprn_mrc_tbl_new   => l_asset_deprn_mrc_tbl_new,
          p_group_reclass_options_rec => l_group_reclass_options_rec
         );
   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;
   if l_mesg_count > 0 then
      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                    (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);
      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);
         dbms_output.put_line(l_mesg);
      end loop;
      fnd_msg_pub.delete_msg();
   end if;
   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
     dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
   end if;
end;