Fixed Assets few useful Queries in Oracle APPS

SELECT *

   FROM fa_additions_b

  WHERE asset_number = p_asset_number;

SELECT *

   FROM fa_additions_tl

  WHERE asset_id = p_asset_id

    AND LANGUAGE = USERENV('LANG');

  SELECT *

    FROM fa_transaction_headers

   WHERE asset_id = p_asset_id

     AND book_type_code = p_book_type_code

ORDER BY book_type_code, date_effective;

SELECT *

  FROM fa_asset_history

 WHERE asset_id = p_asset_id;

 SELECT *

   FROM fa_adjustments

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_books

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_deprn_summary

   WHERE asset_id = p_asset_id

     AND book_type_code = p_book_type_code

  ORDER BY deprn_run_date;

SELECT *

    FROM fa_deprn_summary_h

   WHERE asset_id = p_asset_id;

SELECT *

    FROM fa_deprn_detail

   WHERE asset_id = p_asset_id

  ORDER BY book_type_code, deprn_run_date;

SELECT *

    FROM fa_deprn_detail_h

  WHERE asset_id = p_asset_id;

  SELECT *

    FROM fa_deprn_events

   WHERE asset_id = p_asset_id

ORDER BY book_type_code, deprn_run_date;

SELECT *

  FROM fa_asset_invoices

 WHERE asset_id = p_asset_id;

SELECT *

  FROM fa_invoice_transactions

 WHERE invoice_transaction_id IN (SELECT DISTINCT invoice_transaction_id_in

                                    FROM fa_asset_invoices

                                   WHERE asset_id = p_asset_id);

  SELECT *

    FROM fa_books_summary

   WHERE asset_id = p_asset_id

ORDER BY book_type_code, period_counter;

  SELECT *

    FROM fa_deprn_periods

   WHERE book_type_code = (SELECT DISTINCT book_type_code

                             FROM fa_deprn_detail_h

                            WHERE asset_id = p_asset_id)

ORDER BY period_counter;

SELECT *

  FROM fa_mass_additions

 WHERE asset_number = p_asset_number;

SELECT *

  FROM fa_massadd_distributions

 WHERE mass_addition_id IN (SELECT DISTINCT mass_addition_id

                              FROM FA_MASS_ADDITIONS

                             WHERE asset_number = p_asset_number);

SELECT *

  FROM fa_book_controls

 WHERE book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id);

SELECT *

  FROM fa_book_controls_history

 WHERE book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                          );

SELECT *

  FROM fa_categories_b

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number);

SELECT *

  FROM fa_categories_tl

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                        );

SELECT *

  FROM fa_category_books

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                            )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                          );

SELECT *

  FROM fa_category_book_defaults

 WHERE category_id IN (SELECT DISTINCT asset_category_id

                         FROM fa_additions_b

                        WHERE asset_number = p_asset_number

                      )

   AND book_type_code = (SELECT DISTINCT book_type_code

                           FROM fa_deprn_detail_h

                          WHERE asset_id = p_asset_id

                        );

SELECT *

  FROM fa_calendar_periods

 WHERE calendar_type =

          (SELECT deprn_calendar

             FROM fa_book_controls

            WHERE book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                     )

          );

SELECT *

  FROM fa_conventions

 WHERE prorate_convention_code =

          (SELECT DISTINCT prorate_convention_code

             FROM fa_category_books

            WHERE category_id IN (SELECT DISTINCT asset_category_id

                                    FROM fa_additions_b

                                   WHERE asset_number = p_asset_number

                                     )

              AND book_type_code = (SELECT DISTINCT book_type_code

                                      FROM fa_deprn_detail_h

                                     WHERE asset_id = p_asset_id

                                    )

          );

SELECT *

  FROM fa_methods

 WHERE     method_code =

              (SELECT DISTINCT deprn_method

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =p_asset_number

                                         )

                  AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

              )

       AND life_in_months =

              (SELECT DISTINCT life_in_months

                 FROM fa_category_book_defaults

                WHERE     category_id IN (SELECT DISTINCT asset_category_id

                                            FROM fa_additions_b

                                           WHERE asset_number =

                                                    p_asset_number)

                      AND book_type_code = (SELECT DISTINCT book_type_code

                                              FROM fa_deprn_detail_h

                                             WHERE asset_id = p_asset_id

                                             )

            );

SELECT *

    FROM xla_events

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code)

ORDER BY event_date;

SELECT *

    FROM ( (SELECT *

              FROM xla_transaction_entities

             WHERE source_id_int_1 IN (SELECT DISTINCT

                                                  transaction_header_id

                                         FROM fa_transaction_headers

                                        WHERE asset_id = p_asset_id

                                          AND book_type_code = p_book_type_code

                                          AND event_id IS NOT NULL

                                       )

               AND source_id_char_1 = p_book_type_code

             )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE     source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             AND event_id IS NOT NULL

                                             )

                  AND source_id_char_1 = p_book_type_code

                  AND source_id_int_1 = p_asset_id

           )

          UNION ALL

          (SELECT *

             FROM xla_transaction_entities

            WHERE source_id_int_3 IN (SELECT DISTINCT deprn_run_id

                                            FROM fa_deprn_summary_h

                                           WHERE asset_id = p_asset_id

                                             AND book_type_code = p_book_type_code

                                             )

             AND source_id_char_1 = p_book_type_code

             AND source_id_int_1 = p_asset_id

                  )

       )

ORDER BY creation_date;

SELECT *

    FROM xla_ae_headers

   WHERE event_id IN (SELECT DISTINCT event_id

                        FROM fa_transaction_headers

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                      UNION ALL

                      SELECT DISTINCT event_id

                        FROM fa_deprn_summary_h

                       WHERE asset_id = p_asset_id

                         AND book_type_code = p_book_type_code

                       )

ORDER BY accounting_date;

How to find password of a User in Oracle Apps R12

--Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/

--Package Body

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;

/

Sample Script to Copy Responsibilities of one user account to another user account using API

fnd_user_pkg.addresp is an Oracle standard API to add responsibilities to a user account.

Below is the script to copy responsibilities of user “INTG_USER” to “IMPL_USER” 

DECLARE

  --
  resp_count NUMBER := 0;
  --
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'INTG_USER'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
      -- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
    AND furga.end_date IS NULL;
  --
  --
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => 'IMPL_USER',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
      --
      resp_count := resp_count + 1;
      --
    EXCEPTION
    WHEN OTHERS THEN
      --
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
      --
    END;
  END LOOP;
  --
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
  --
  COMMIT;
END;

Thanks,

Amar

How to enable the “Operating Unit” Parameter for a Concurrent Program in Oracle Apps R12

We notice that the new field as 'Operating Unit' added in R12 and this is by default is in disabled mode:

An applications responsibility can access multiple OUs. Some concurrent programs have been enhanced to process multiple operating units simultaneously, while for other requests the operating unit must be specified when you run the program.

To support this, concurrent programs are defined with an operating unit mode of

'S' for single operating unit and 'M'(concurrent program will be in this mode by default) for multiple operating units.

If the 'Operating Unit Mode' is not set for the concurrent program it will fail.

The ‘Operating Unit’ field/parameter is known as ‘Reporting Context’ in MOAC(Multi Org Access Control) terminology.

How we can achieve from the Oracle Applications Front-End.

1. Login into application with System Administration responsibility (NOT System
     Administrator)
2. Navigate: Concurrent è Programs

3. Query for Short Name or Program Name of the concurrent program like as below.

4. Click on Update icon of your program.

5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit Mode' field.
7. Save changes by clicking on 'Apply' button.

8. Change responsibility where the ‘Concurrent Program’ can be run, in this example ‘TESTOU’ is assigned to ‘AR Super User’ responsibility                              

9. Select the ‘Concurrent Program’ from the ‘SRS Window’, now you can see that ‘Operating 

    Unit’ field is enabled and you can see the Operating Units in the LOV.

 

10. This particular selected Operating Unit value can be accessed using the standard MOAC API:MO_GLOBAL.GET_CURRENT_ORG_ID

Thanks

Amar Alam