Sunday, October 2, 2016

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect