Using rollback depreciation api before override depreciation

The Rollback Depreciation API FA_DEPRN_ROLLBACK_PUB.DO_ROLLBACK was introduced with Release 12.

In prior releases, FADRB - Rollback Depreciation program was available in order to rollback the depreciation calculation after it was run for a book without closing the period.
In Release 12, this program is no longer available because a new feature was introduced - automatic depreciation rollback.

With this new feature, after running depreciation for a book without closing the period, if additional transactions are needed on particular assets (adjustment, retirement etc.), the user can update those assets with whatever transaction is needed. For those particular assets, the depreciation data is automatically rolled back and the transactions can be performed on them. After this, the user can run depreciation again to depreciate these particular assets.

*Note that the rollback is also completed for asset description changes per Note 1463816.1.

Instead of entering additional transactions, in case it is not needed, you can manually rollback depreciation for an asset by running the Rollback Depreciation API which restores the asset to its state prior to running depreciation. This may be useful for the purpose of testing some setup behavior.

You can find details about this API in Oracle Assets Users' Guide Release 12.

A sample script is provided below.

The ASSET_HDR_REC_TYPE asset structure contains unique identification information for a given asset, such as the asset ID and book type code. Both variables are required when running the API.

Steps

1) Run depreciation without closing the period

2) Run the Rollback Depreciation API procedure FA_DEPRN_ROLLBACK_PUB.DO_ROLLBACK for an asset/book

Code (SQL):

SET serveroutput ON
    DECLARE
    l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
    l_return_status VARCHAR2(1);
    l_mesg_count NUMBER;
    l_mesg varchar2(4000);
    BEGIN
    dbms_output.enable(1000000);
    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';
    --call the api
    FA_DEPRN_ROLLBACK_PUB.do_rollback
    (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,
    px_asset_hdr_rec => l_asset_hdr_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');
    END IF;
    END;
    /

3) If the script returns SUCCESS, issue a commit.

Code (SQL):

COMMIT;

API to Load Arabic Values in HR Lookups

DECLARE

   CURSOR get_lookup_details

   IS

      SELECT ltype.application_id,

             ltype.customization_level,

             ltype.creation_date,

             ltype.created_by,

             ltype.last_update_date,

             ltype.last_updated_by,

             ltype.last_update_login,

             tl.lookup_type,

             tl.security_group_id,

             tl.view_application_id,

             tl.description,

             tl.meaning

        FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype

       WHERE     ltype.lookup_type = 'XX POSITIONS'

             AND ltype.lookup_type = tl.lookup_type

             AND language = 'US';

   CURSOR get_value

   IS

      SELECT DISTINCT

             hl.lookup_code, pos.position_name_arabic, position_name_english

        FROM xxhr_position_t pos, hr_lookups hl

       WHERE     hl.meaning = pos.position_name_english

             AND hl.lookup_type = 'XX POSITIONS'

--             AND lookup_code= '321'

             AND lkp_process_flag_ar = 'N';

   l_err_msg   VARCHAR2 (1000) := NULL;

   l_db_nls_language       VARCHAR2 (500) := NULL;

BEGIN

   FOR i IN get_lookup_details

   LOOP

      FOR j IN get_value

      LOOP

         -- Set NLS_LANG to Arabic to insert  Arabic Values

         fnd_global.set_nls (

            p_nls_language                => 'ARABIC',

            p_nls_date_format             => NULL,

            p_nls_date_language           => NULL,

            p_nls_numeric_characters      => NULL,

            p_nls_sort                    => NULL,

            p_nls_territory               => NULL,

            p_db_nls_language             => l_db_nls_language,

            p_db_nls_date_format          => l_db_nls_language,

            p_db_nls_date_language        => l_db_nls_language,

            p_db_nls_numeric_characters   => l_db_nls_language,

            p_db_nls_sort                 => l_db_nls_language,

            p_db_nls_territory            => l_db_nls_language,

            p_db_nls_charset              => l_db_nls_language);

         l_err_msg := NULL;

        

        dbms_output.put_line('l_db_nls_language: ' || l_db_nls_language);

         BEGIN

            fnd_lookup_values_pkg.translate_row (

               x_lookup_type           => i.lookup_type,

               x_security_group_id     => i.security_group_id,

               x_view_application_id   => i.view_application_id,

               x_owner                 => 49431, --> User ID

               x_meaning               => j.position_name_arabic,

               x_description           => j.position_name_arabic,

               x_lookup_code           => j.lookup_code);

            UPDATE xxhr_position_t --> Stagging Table to Log Status

               SET lkp_process_flag_ar = 'Y', lkp_err_msg = NULL

             WHERE UPPER (position_name_english) =

                      UPPER (j.position_name_english);

            COMMIT;

         EXCEPTION

            WHEN OTHERS

            THEN

               l_err_msg := SQLERRM;

               UPDATE xxhr_position_t

                  SET lkp_process_flag_ar = 'N', lkp_err_msg = l_err_msg

                WHERE UPPER (position_name_english) =

                         UPPER (j.position_name_english);

               COMMIT;

         END;

      END LOOP;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;

https://josephbijoy.blogspot.com/2016/02/api-to-load-arabic-values-in-hr-lookups.html

Oracle Workflow Tables with links

---------Get Process details------------------

SELECT distinct wa_process.NAME, wat_process.display_name, wa_process.item_type,wa_process.TYPE,wa_process.FUNCTION
,trunc(wa_process.BEGIN_DATE),trunc(wa_process.END_DATE),wa_process.VERSION
  FROM wf_activities wa_process, wf_activities_tl wat_process
WHERE wa_process.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_process.TYPE = 'PROCESS'
--   AND wa_process.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type =:A AND TYPE = 'PROCESS')
   AND wat_process.item_type = wa_process.item_type
   AND wat_process.VERSION = wa_process.VERSION
   AND wat_process.LANGUAGE = 'US'
   AND wat_process.NAME = wa_process.NAME
   And to_char(trunc(wa_process.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_process.END_DATE) is null or trunc(wa_process.END_DATE) > sysdate)
UNION
---------Get Event details------------------
SELECT distinct wa_event.NAME, wat_event.display_name, wa_event.item_type,wa_event.TYPE,wa_event.FUNCTION
,trunc(wa_event.BEGIN_DATE),trunc(wa_event.END_DATE),wa_event.VERSION
  FROM wf_activities wa_event, wf_activities_tl wat_event
WHERE wa_event.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_event.TYPE = 'EVENT'
--   AND wa_event.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'EVENT')
   AND wat_event.item_type = wa_event.item_type
   AND wat_event.VERSION = wa_event.VERSION
   AND wat_event.LANGUAGE = 'US'
   AND wat_event.NAME = wa_event.NAME
   And to_char(trunc(wa_event.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_event.END_DATE) is null or trunc(wa_event.END_DATE) > sysdate)
UNION
---------Get Function details------------------
SELECT distinct wa_func.NAME, wat_func.display_name, wa_func.item_type,wa_func.TYPE,wa_func.FUNCTION
,trunc(wa_func.BEGIN_DATE),trunc(wa_func.END_DATE),wa_func.VERSION
  FROM wf_activities wa_func, wf_activities_tl wat_func
WHERE wa_func.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_func.TYPE = 'FUNCTION'
--   AND wa_func.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'FUNCTION')
   AND wat_func.item_type = wa_func.item_type
   AND wat_func.VERSION = wa_func.VERSION
   AND wat_func.LANGUAGE = 'US'
   AND wat_func.NAME = wa_func.NAME
   And to_char(trunc(wa_func.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_func.END_DATE) is null or trunc(wa_func.END_DATE) > sysdate)
UNION  
---------Get Notification details------------------
SELECT distinct wa_not.NAME, wat_not.display_name, wa_not.item_type,wa_not.TYPE,wa_not.FUNCTION
,trunc(wa_not.BEGIN_DATE),trunc(wa_not.END_DATE),wa_not.VERSION
  FROM wf_activities wa_not, wf_activities_tl wat_not
WHERE wa_not.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
   AND wa_not.TYPE = 'NOTICE'
--   AND wa_not.VERSION = (SELECT MAX (VERSION)
--                       FROM wf_activities
--                      WHERE item_type = :A AND TYPE = 'NOTICE')
   AND wat_not.item_type = wa_not.item_type
   AND wat_not.VERSION = wa_not.VERSION
   AND wat_not.LANGUAGE = 'US'
   AND wat_not.NAME = wa_not.NAME
   And to_char(trunc(wa_not.BEGIN_DATE),'DD_MON_YYYY') > '01-JAN-1947'
   And (trunc(wa_not.END_DATE) is null or trunc(wa_not.END_DATE) > sysdate)
UNION
---------Get Message  details------------------
SELECT ws.name,wst.display_name,ws.TYPE,'Message',null,null,null,null FROM wf_messages ws,WF_messages_TL wst
Where ws.TYPE IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.type=wst.TYPE
And ws.name=wst.name
And wst.language='US'
UNION
---------Get Message Attribute details------------------
Select ws.NAME,wst.DISPLAY_NAME,ws.message_type,'Message Attribute',null,null,null,null
from  WF_MESSAGE_ATTRIBUTES ws,WF_MESSAGE_ATTRIBUTES_TL wst
Where ws.message_type  IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And ws.NAME=wst.NAME
And ws.message_TYPE=wst.MESSAGE_TYPE
And wst.language='US'
Union
---------Get Lookup  details------------------
Select  wl.LOOKUP_TYPE NAME,wlt.DISPLAY_NAME,wl.item_type,'Lookup Type',null,null,null,null
from wf_lookup_types wl,
      wf_lookup_types_TL wlt
  Where wl.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
And wl.LOOKUP_TYPE=wlt.LOOKUP_TYPE
And wl.ITEM_TYPE=wlt.ITEM_TYPE
And wlt.language='US'
UNION
---------Get Item Attribute details------------------
Select WIA.NAME,wit.DISPLAY_NAME,WIA.item_type,'Item Attribute',null,null,null,null
FROM
WF_ITEM_ATTRIBUTES WIA,
WF_ITEM_ATTRIBUTES_TL WIT
Where WIA.item_type =WIT.item_type
And WIA.name=WIT.name
And language='US'
And WIA.item_type IN (select distinct item_type from wf_activities where item_type in
('XXMOM_QA'))
order by 3,4;