10 Replies Latest reply on Jul 9, 2013 4:24 PM by Eduardo Schurtz

    FA tables R11 x SLA tables R12

    Eduardo Schurtz

      Hi all...

       

      I'm migrating a FA report from R11 to R12. According to this doc http://assetsig.oaug.org/file/R12OOW081238767518.pdf the changes are:

       

      Accounting lines are now stored in the SLA Tables:

        xla_events

        xla_ae_headers

        xla_ae_lines

        xla_distribution_links

      by linking to the event id in the FA_Transaction_Headers and FA_Deprn_Summary tables. CCID no longer stored in the FA_Adjustments table.

       

      But I need more details about these changes. Which FA tables do I need to replace? How do I replace them?

       

      Tks

        • 1. Re: FA tables R11 x SLA tables R12
          Ivruksha

          Hi,

           

          please provide us with the list of fields in 11i i.e. (table name and column name) for which you are looking for replacements in Release 12 ...I would try to address that ...

           

          Regards,

          Ivruksha

          • 2. Re: FA tables R11 x SLA tables R12
            Eduardo Schurtz

            Hi Ivruksha.

             

            Let's go!

             

            Tables and columns:

             

            FA_BOOK_CONTROLS

            SET_OF_BOOKS_ID

             

            FA_DISTRIBUTION_HISTORY

            UNITS_ASSIGNED

            BOOK_TYPE_CODE

            ASSIGNED_TO

            DATE_EFFECTIVE

            ASSET_ID

            LOCATION_ID

            CODE_COMBINATION_ID

            DATE_INEFFECTIVE

            DISTRIBUTION_ID

             

            FA_BOOKS

            DATE_PLACED_IN_SERVICE

            BOOK_TYPE_CODE

            ASSET_ID

            BOOK_TYPE_CODE

            PERIOD_COUNTER_FULLY_RETIRED

            DATE_INEFFECTIVE

             

            FA_LOCATIONS

            LOCATION_ID

             

            FA_ADDITIONS

            ASSET_NUMBER

            DESCRIPTION

            ASSET_ID

            DESCRIPTION

            SERIAL_NUMBER

            TAG_NUMBER

            ASSET_TYPE

             

            FA_DEPRN_DETAIL

            DEPRN_SOURCE_CODE

            ADDITION_COST_TO_CLEAR

            COST

            DEPRN_RESERVE

            ASSET_ID

            BOOK_TYPE_CODE

            DISTRIBUTION_ID

            PERIOD_COUNTER

             

            FA_SYSTEM_CONTROLS

            COMPANY_NAME

            CATEGORY_FLEX_STRUCTURE

            LOCATION_FLEX_STRUCTURE

            ASSET_KEY_FLEX_STRUCTURE

             

            FA_DEPRN_PERIODS

            PERIOD_NAME

            PERIOD_COUNTER

            BOOK_TYPE_CODE

            PERIOD_CLOSE_DATE

             

            And do you know about the following views?

             

            FA_BOOK_CONTROLS_MRC_V

            FA_MC_DEPRN_DETAIL

            FA_MC_ADJUSTMENTS

             

            The report checks the column MRC_SOB_TYPE_CODE from GL_SETS_OF_BOOKS and... :

             

            IF 'R':

            Report uses FA_BOOK_CONTROLS_MRC_V instead of FA_BOOK_CONTROLS

                              FA_MC_DEPRN_DETAIL instead of FA_DEPRN_DETAIL

                              FA_MC_ADJUSTMENTS instead of FA_ADJUSTMENTS

             

            Are these "MRC/MC" views used in R12?

             

            Tks a lot.

            Eduardo

            • 3. Re: FA tables R11 x SLA tables R12
              Ivruksha

              Hi Eduardo,

               

              below are the changes in R12 for your requirement in 11i,

               

              Tables and columns:
              11i Table Name - FA_BOOK_CONTROLS Column Name - SET_OF_BOOKS_ID
              R12 - It is the same ...No Change

              11i - Table Name - FA_DISTRIBUTION_HISTORY
              Column Names
              UNITS_ASSIGNED = Available in R12 No change
              BOOK_TYPE_CODE = Not available in R12, however you can use TRANSACTIONHEADER_ID_IN column value from this table and compare with FA_TRANSACTION_HEADERS and

              derive this value ..
              ASSIGNED_TO = Available in R12 No change
              DATE_EFFECTIVE = Available in R12 No change
              ASSET_ID = Not available in R12
              LOCATION_ID  = Available in R12 No change
              CODE_COMBINATION_ID = Available in R12 No change
              DATE_INEFFECTIVE = Available in R12 No change
              DISTRIBUTION_ID = Available in R12 No change


              11i - Table Name - FA_BOOKS
              Column Names
              DATE_PLACED_IN_SERVICE = Available in R12 No change
              BOOK_TYPE_CODE = Not available in R12, however you can use TRANSACTIONHEADER_ID_IN column value from this table and compare with FA_TRANSACTION_HEADERS and

              derive this value ..
              ASSET_ID = Not available in R12
              PERIOD_COUNTER_FULLY_RETIRED = Available in R12 No change
              DATE_INEFFECTIVE = Available in R12 No change

               

              11i - Table Name - FA_LOCATIONS
              Column Names
              LOCATION_ID - Available in FA_DISTRIBUTION_HISTORY table ..

               

              11i - Table Name - FA_ADDITIONS
              Column Names
              ASSET_NUMBER = Available in R12 No change
              DESCRIPTION = Available in R12 No change (Check FA_ADDITIONS_TL or FA_ADDITIONS_B)
              ASSET_ID = Available in R12 No change
              SERIAL_NUMBER = Available in R12 No change
              TAG_NUMBER = Available in R12 No change
              ASSET_TYPE = Available in R12 No change


              11i - Table Name - FA_DEPRN_DETAIL
              Column Names
              DEPRN_SOURCE_CODE = Available in R12 No change
              ADDITION_COST_TO_CLEAR = Available in R12 No change
              COST = Available in R12 No change
              DEPRN_RESERVE = Available in R12 No change
              ASSET_ID = Not available in R12
              BOOK_TYPE_CODE = Not available in R12
              DISTRIBUTION_ID = Available in R12 No change
              PERIOD_COUNTER = Available in R12 No change

              11i - Table Name - FA_SYSTEM_CONTROLS
              Column Names
              COMPANY_NAME = Available in R12 No change
              CATEGORY_FLEX_STRUCTURE = Available in R12 No change
              LOCATION_FLEX_STRUCTURE = Available in R12 No change
              ASSET_KEY_FLEX_STRUCTURE = Available in R12 No change

              11i - Table Name - FA_DEPRN_PERIODS

              PERIOD_NAME = Available in R12 No change
              PERIOD_COUNTER = Available in R12 No change
              BOOK_TYPE_CODE = Not available in R12
              PERIOD_CLOSE_DATE = Available in R12 No change

              Below views are available in R12 as well..
              1) FA_BOOK_CONTROLS_MRC_V
              2) FA_MC_DEPRN_DETAIL
              3) FA_MC_ADJUSTMENTS

               

              Regards,
              Ivruksha

              1 person found this helpful
              • 4. Re: FA tables R11 x SLA tables R12
                Eduardo Schurtz

                Hi Ivruksha...

                 

                First of all, tks a lot!

                 

                Yes, the most of these objects there are in R12 but my concern is about their use. Some objects are obsolete in R12 and I don't know if I should use them or not.

                 

                For example, these views. The MRC concept in R12 is obsolete. Now we should use reporting currencies. But I'm not sure if even obsolete I can use them.

                 

                Regards,

                • 5. Re: FA tables R11 x SLA tables R12
                  Ivruksha

                  Hi Eduardo,

                   

                  your focus is on the MRC details ....the concept of MRC is still available in R12, but it underwent a change from backend perspective.

                  Instead of MRC tables, you would now look XLA tables for finding the accounting entry for functional ledger and reporting ledger  ...
                  I would tell you the way in which you can find the entries of your MRC / Reporting Ledger...

                  In Asset module from FA_TRANSACTION_HEADERS table, you would find the event_id, which is nothing but the accounting event id which is the link between asset transaction and accounting entries created by SLA..... Now for your normal ledger in functional currency there should be one event id, and for your reporting ledger there should be another one ...... you are to pick the other event ID ....
                  You can compare the event id with XLA_AE_HEADERS to find the ledger ID ..... you are to select AE_HEADER_ID for your reporting ledger ID and not the primary ledger ID ...
                  Once the AE_HEADER_ID value is derived, check the journal lines created for that header from XLA_AE_LINES, which is nothing but the actual accounting entry one sees in the front end ...
                  Before hand check GL_LEDGERS table and make note of the ledger ids for your primary ledger and your reporting ledger ....

                  I understand the difficulty in understanding this architectural change and apprehension on making the code change ....  but it would always be a continuing exercise when it comes to technology ...

                   

                  Regards,
                  Ivruksha

                  1 person found this helpful
                  • 6. Re: FA tables R11 x SLA tables R12
                    Eduardo Schurtz

                    Hi Ivruksha.

                     

                    I found out yesterday this report I'm migrating is a clone from a core report (FAS410.rdf).

                     

                    It was cloned and changed because the customer wanted to see the amount in other currencies. A parameter "Currency" was added. If the currency is different from primary currency, the report uses the _MC_ tables.

                     

                    The MRC concept was replaced to Reporting Currencies functionality in R12. So now I need to change the code to show the other currencies by using the Reporting Currencies functionality instead of MRC tables, right?

                     

                    The way for that is using SLA according to your previous answer?

                     

                    Tks a lot.

                    • 7. Re: FA tables R11 x SLA tables R12
                      Eduardo Schurtz

                      I'm checking another report core from R12 (where it uses multiple currencies) and I saw it does the same thing (it still uses MRC tables):

                       

                      IF :p_ca_set_of_books_id <> -1999

                      THEN

                       

                        BEGIN

                         select mrc_sob_type_code, currency_code

                         into :p_mrcsobtype, :lp_currency_code

                         from gl_sets_of_books

                         where set_of_books_id = :p_ca_set_of_books_id;

                        EXCEPTION

                          WHEN OTHERS THEN

                           :p_mrcsobtype := 'P';

                        END;

                      ELSE

                         :p_mrcsobtype := 'P';

                      END IF;

                       

                      IF upper(:p_mrcsobtype) = 'R'

                      THEN

                       

                           :L_FA_DEPRN_PERIODS := 'FA_MC_DEPRN_PERIODS';

                           :L_FA_ADJUSTMENTS := 'FA_MC_ADJUSTMENTS';

                           :L_FA_DEPRN_DETAIL := 'FA_MC_DEPRN_DETAIL';

                           :L_INVOICE := 'FA_MC_ASSET_INVOICES';

                       

                           :L_ADJ_SOB := 'ADJ.SET_OF_BOOKS_ID = :P_CA_SET_OF_BOOKS_ID AND ';

                           :L_DETAIL_SOB := 'DD.SET_OF_BOOKS_ID = :P_CA_SET_OF_BOOKS_ID AND ';

                           :L_PERIODS_SOB := 'DP.SET_OF_BOOKS_ID = :P_CA_SET_OF_BOOKS_ID AND ';

                           :L_INVOICE_SOB := 'AI_IN.SET_OF_BOOKS_ID = :P_CA_SET_OF_BOOKS_ID AND ';

                       

                      ELSE

                       

                           :L_FA_DEPRN_PERIODS := 'FA_DEPRN_PERIODS';

                           :L_FA_ADJUSTMENTS := 'FA_ADJUSTMENTS';

                           :L_FA_DEPRN_DETAIL := 'FA_DEPRN_DETAIL';

                           :L_INVOICE := 'FA_ASSET_INVOICES';

                       

                           :L_ADJ_SOB := '1=1 AND ';

                           :L_DETAIL_SOB := '1=1 AND ';

                           :L_PERIODS_SOB := '1=1 AND ';

                           :L_INVOICE_SOB := '1=1 AND ';

                       

                      END IF;

                       

                      The report is "Additions by Source Report" (FASASSBS).

                       

                      So don't I need to change anything?

                      • 8. Re: FA tables R11 x SLA tables R12
                        Ivruksha

                        Hi Eduardo,

                         

                        MRC tables still exists in R12 ... i have checked the Technical Reference Manual as well... (etrm.oracle.com), they are not obsolete ... but their usage has undergone a change ...
                        Better approach would be to use the report you have on hand from R12 and execute the same, and review the output ..... if it is found satisfactory, stick on to that ...
                        If there is a difference in the output, we might get in to further details on the discrepancies and work on the same ....

                        (Probably you could explore and clarify all of us, which oracle missed out to document ...!)

                         

                        Regards,
                        Ivruksha

                        • 9. Re: FA tables R11 x SLA tables R12
                          Eduardo Schurtz

                          Hi Ivruksha.

                           

                          Yes, documentation is missing...

                           

                          Note 793785.1 talks about profile MRC: Reporting Currency. And it's said this profile is obsolete in R12.


                          _MC_ tables were populated by a trigger in 11i but I think these triggers are dropped in R12.


                          It's complicated. We have no sure about what we need to change.


                          Thank you.

                          • 10. Re: FA tables R11 x SLA tables R12
                            Eduardo Schurtz

                            Hi Ivruksha.

                             

                            Just to tell you... I didn't change anything. Several documents talk about "MRC is obsolete"... "MRC does not exist in R12"... etc.. etc... But on the other hand several documents talk about that we can still use MRC in R12...

                             

                             

                            So I didn't change anything.

                             

                             

                            Tks a lot for your help.

                             

                            Eduardo