Creating a new Fixed Asset through API Script

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_status                VARCHAR2 (10) := 'True';

   l_errmsg                VARCHAR2 (1000);

   l_total_records         NUMBER := 0;

   l_success_records       NUMBER := 0;

   l_failure_records       NUMBER := 0;

BEGIN

   FOR asset_data IN (SELECT *

                        FROM patient.XXAA_add_new_asset ana

                       WHERE asset_id is  null)

   LOOP

      BEGIN

         l_errmsg := ' ';

         l_status := 'True';

         l_total_records := l_total_records + 1;

         DBMS_OUTPUT.enable (10000000);

         FA_SRVR_MSG.Init_Server_Message;

         l_asset_desc_rec.tag_number := NULL;

         l_asset_desc_rec.serial_number := NULL;

         l_asset_desc_rec.model_number := NULL;

         l_asset_desc_rec.manufacturer_name := NULL;

         l_asset_cat_rec.category_id := NULL;

         l_asset_desc_rec.description := NULL;

         l_asset_dist_rec.expense_ccid := NULL;

         l_asset_fin_rec.date_placed_in_service := NULL;

         l_asset_fin_rec.cost := NULL;

         l_asset_dist_rec.location_ccid := NULL;

         l_asset_dist_rec.assigned_to := NULL;

         l_asset_hdr_rec.asset_id := NULL;

         l_asset_desc_rec.asset_number := NULL;

         ---*******************************************************************---

         l_asset_desc_rec.asset_number                 := '1234567';

         -------------- from file ----------------

         l_asset_desc_rec.tag_number := asset_data.TAG_ID;

         l_asset_desc_rec.serial_number := asset_data.serial;

         l_asset_desc_rec.model_number := asset_data.MODEL;

         l_asset_desc_rec.manufacturer_name := asset_data.BRAND;

         l_asset_cat_rec.category_id := asset_data.CATEGORY_CCID; --FA_CATEGORIES

         l_asset_desc_rec.description := asset_data.DESCRIPTION;

         l_asset_dist_rec.expense_ccid := asset_data.EX_ACCOUNT_CCID; ---gl_code_combinations_kfv

         l_asset_fin_rec.date_placed_in_service := TO_DATE (asset_data.DATE_PLACED_IN_SERVICE, 'DD-MON-RRRR');

         l_asset_fin_rec.cost := asset_data.cost;

         l_asset_deprn_rec.ytd_deprn := asset_data.ytd;

         l_asset_dist_rec.location_ccid := asset_data.LOCATION_CCID;

          l_asset_deprn_rec.deprn_reserve := asset_data.ACC_DEP;

          --l_asset_deprn_rec.bonus_ytd_deprn := 0;

          --l_asset_deprn_rec.bonus_deprn_reserve := 0;

         -------------------------------from email -------------------

         l_asset_type_rec.asset_type := 'CAPITALIZED';

         l_asset_hdr_rec.book_type_code := 'DSFH JED BOOK';

         l_asset_dist_rec.units_assigned := 1;

         l_asset_desc_rec.asset_key_ccid := 2001;         ---FA_ASSET_KEYWORDS

         -----------------------------------others--------------------------

         l_asset_fin_rec.set_of_books_id := 2021;      ------ GL_SETS_OF_BOOKS

         l_asset_desc_rec.in_use_flag := 'YES';

         l_asset_desc_rec.new_used := 'NEW';

         l_asset_desc_rec.owned_leased := 'OWNED';

         l_asset_desc_rec.current_units := 1;

         l_asset_fin_rec.deprn_method_code := 'STL';

         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;

         l_asset_fin_rec.life_in_months := 84;

         l_asset_fin_rec.depreciate_flag := 'YES';

         l_asset_fin_rec.deprn_start_date              := TO_DATE('01-JUN-2014','DD-MON-RRRR');

         l_asset_fin_rec.original_cost                 := 50000;

         l_asset_fin_rec.prorate_convention_code       := 'SAME MONTH';

         l_asset_fin_rec.salvage_type                  := 'AMT'; -- PCT - for Percentage

         l_asset_fin_rec.salvage_value                 := 1000;

         l_asset_fin_rec.percent_salvage_value         := NULL;

         l_asset_fin_rec.depreciate_flag               := 'YES';

         l_asset_fin_rec.orig_deprn_start_date         := TO_DATE('01-AUG-2010','DD-MON-RRRR');

         -- deprn info

         l_asset_deprn_rec.set_of_books_id             := 2021;

         l_asset_deprn_rec.ytd_deprn                   := 20000;

         l_asset_deprn_rec.deprn_reserve               := 20000;

         l_asset_deprn_rec.bonus_ytd_deprn             := 0;

         l_asset_deprn_rec.bonus_deprn_reserve         := 0;

        -- book / trans info

        -- Valid value in FA_BOOK_CONTROLS

        -- distribution info

        -- Valid Record from GL Code cominations with record type = 'E' (Expense)

        -- Valid Value in FA Locations

        -- 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);

         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);

               COMMIT;

            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');

            l_failure_records := l_failure_records + 1;

            l_errmsg := SUBSTR (SQLERRM, 1, 400);

            UPDATE patient.XXAA_add_new_asset

               SET status = 'FAILURE',

                   error_msg = l_errmsg || '***' || l_mesg

             WHERE 1 = 1

                  AND NVL (OLD_TAG, '-') = NVL (asset_data.OLD_TAG, '-')

                  AND NVL (TAG_ID, '-') = NVL (asset_data.TAG_ID, '-')

                  AND NVL (description, '-') = NVL (asset_data.description, '-');

                  AND NVL (serial, '-') = NVL (asset_data.serial, '-')

                  AND NVL (MODEL, '-') = NVL (asset_data.MODEL, '-')

                  AND NVL (BRAND, '-') = NVL (asset_data.BRAND, '-')

                  AND NVL (EX_ACCOUNT_CCID, '-') = NVL (asset_data.EX_ACCOUNT_CCID, '-')

                  AND NVL (cost, '-') = NVL (asset_data.cost, '-')

                  AND NVL (description, '-') = NVL (asset_data.description, '-')

                  AND NVL (LOCATION_CCID, '-') = NVL (asset_data.LOCATION_CCID, '-');

            COMMIT;

         ELSE

            DBMS_OUTPUT.put_line ('SUCCESS');

            UPDATE patient.XXAA_add_new_asset

               SET status = 'SUCCESS',

                   error_msg = ' ',

                   asset_id = l_asset_desc_rec.asset_number

             WHERE     1 = 1

                   AND NVL (OLD_TAG, '-') = NVL (asset_data.OLD_TAG, '-')

                   AND NVL (TAG_ID, '-') = NVL (asset_data.TAG_ID, '-')

                   AND NVL (description, '-') = NVL (asset_data.description, '-');

                   AND NVL (serial, '-') = NVL (asset_data.serial, '-')

                   AND NVL (MODEL, '-') = NVL (asset_data.MODEL, '-')

                   AND NVL (BRAND, '-') = NVL (asset_data.BRAND, '-')

                   AND NVL (EX_ACCOUNT_CCID, '-') = NVL (asset_data.EX_ACCOUNT_CCID, '-')

                   AND NVL (cost, '-') = NVL (asset_data.cost, '-')

                   AND NVL (LOCATION_CCID, '-') = NVL (asset_data.LOCATION_CCID, '-');

            COMMIT;

            l_success_records := l_success_records + 1;

         

            DBMS_OUTPUT.put_line ('ASSET_ID :' || TO_CHAR (l_asset_hdr_rec.asset_id) || '    ' || 'ASSET_NUMBER :' || l_asset_desc_rec.asset_number || '    ' || 'Asset Desc :' || l_asset_desc_rec.description);

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line ('========== Exception =============');

      END;

   END LOOP;

   DBMS_OUTPUT.put_line ('==============Last API Result=============');

   DBMS_OUTPUT.put_line ('Total Records To Be Loaded : ' || l_total_records);

   DBMS_OUTPUT.put_line ('Total Success Records : ' || l_success_records);

   DBMS_OUTPUT.put_line ('Total Failure Records : ' || l_failure_records);

END;