AppsDBA Scripts

December 2, 2022 How to Get Apps Password From Backend

APPS DBAAppsDBA ScriptsInstallations/Configurations-ApplicationsOracle APPS DBAScripts

How to Get Apps Password From Backend

STEP 1. First login to Database through Command Prompt

sqlplus / as sysdba

STEP 2 : Create Function From Below Script to decrypt the encrypted password

create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,
in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;

STEP 3 : Run the Below Query for Retrieving AppsPassword

SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;

ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
ZHA49B7AB1BB637F7A4347721229CF2F31A95DEC86B3CCFAEF9B40D4EB9AA8F0030EDDFC2324D873
AB2FFFA3B302AEE6EBD1

STEP 4: Run the Below Query to get the Password

SELECT apps.decrypt_pin_func(‘GUEST/ORACLE123′,’ZHA49B7AB1BB637F7A4347721229CF2F31A95DEC86B3CCFAEF9B40D4EB9AA8F0030EDDFC2324D873AB2FFFA3B302AEE6EBD1’) from dual;

APPS.DECRYPT_PIN_FUNC(‘GUEST/ORACLE’,’ZHA49B7AB1BB637F7A4347721229CF2F31A95DEC86B3CCFAEF9B40D4EB9AA8F0030EDDFC2324D873AB2FFFA3B302AEE6EBD1′)
——————————————————————————–
APPS

STEP 5: Now Login With the Password Retrieved.

SQL> conn apps/APPS;
Connected.

November 9, 2017 How to Find Request Group associated for a Program

AppsDBA ScriptsScripts

How to Find Request Group associated for a Program

SELECT rg.application_id
       “Request Group Application ID”,
       rg.request_group_id
       “Request Group – Group ID”,
       rg.request_group_name,
       rg.description,
       rgu.unit_application_id,
       rgu.request_group_id
       “Request Group Unit – Group ID”,
       rgu.request_unit_id,
       cp.concurrent_program_id,
       cp.concurrent_program_name,
       cpt.user_concurrent_program_name,
       Decode(rgu.request_unit_type, ‘P’, ‘Program’,
                                     ‘S’, ‘Set’,
                                     rgu.request_unit_type) “Unit Type”
FROM   fnd_request_groups rg,
       fnd_request_group_units rgu,
       fnd_concurrent_programs cp,
       fnd_concurrent_programs_tl cpt
WHERE  rg.request_group_id = rgu.request_group_id
       AND rgu.request_unit_id = cp.concurrent_program_id
       AND cp.concurrent_program_id = cpt.concurrent_program_id
       AND cpt.user_concurrent_program_name = ‘Program Name’;

November 9, 2017 How to Find Scheduled Concurrent Program

AppsDBA ScriptsScripts

How to Find Scheduled Concurrent Program

Below Query will Fetch all the details for a scheduled concurrent program.

SELECT a.requested_by,
       a.status_code,
       a.phase_code,
       a.request_id,
       b.user_concurrent_program_name,
       c.concurrent_program_name,
       a.requested_start_date,
       c.execution_method_code,
       d.execution_file_name,
       d.execution_file_path
FROM   apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_programs_tl b,
       apps.fnd_concurrent_programs c,
       apps.fnd_executables d
WHERE  a.status_code IN ( ‘Q’, ‘I’ )
       AND a.concurrent_program_id = b.concurrent_program_id
       AND b.concurrent_program_id = c.concurrent_program_id
       AND c.application_id = d.application_id
       AND c.executable_id = d.executable_id
       AND a.requested_start_date > SYSDATE
       AND a.hold_flag = ‘N’
ORDER  BY 1;

Query For Finding schedules for a particular program

October 10, 2017 Query To find the details of the Accounting Flexfield structure

AppsDBA ScriptsConfigurations-GLScripts

SELECT gls.name,
       idfs.id_flex_num chart_of_accounts_id,
       idfs.segment_num,
       idfs.flex_value_set_id,
       fvs.flex_value_set_name,
       idfs.application_id,
       idfs.id_flex_code,
       idfs.application_column_name,
       idfs.segment_name,
       fvs.security_enabled_flag,
       ( CASE
           WHEN fvs.validation_type = ‘F’ THEN ‘Table’
           WHEN fvs.validation_type = ‘I’ THEN ‘Independent’
           WHEN fvs.validation_type = ‘D’ THEN ‘Dependent’
           WHEN fvs.validation_type = ‘N’ THEN ‘None’
           WHEN fvs.validation_type = ‘P’ THEN ‘Pair’
           WHEN fvs.validation_type = ‘U’ THEN ‘Special’
           ELSE ‘Unknown Type’
         END )          validation_type,
       ( CASE
           WHEN fvs.validation_type = ‘F’ THEN fvt.application_table_name
           ELSE ‘Not Applicable’
         END )          validation_table_name
FROM   gl_ledgers gls,
       fnd_id_flex_segments idfs,
       fnd_flex_value_sets fvs,
       fnd_flex_validation_tables fvt
WHERE  gls.chart_of_accounts_id (+) = idfs.id_flex_num
       AND fvs.flex_value_set_id = idfs.flex_value_set_id
       AND gls.ledger_id = &ledgerid
       AND idfs.application_id = 101
       AND idfs.id_flex_code IN ( ‘GL#’, ‘GLLE’ )
       AND fvs.flex_value_set_id = fvt.flex_value_set_id (+)
ORDER  BY idfs.id_flex_code,
          idfs.id_flex_num,
          idfs.segment_num ASC;