Posts Tagged ‘Oracle Query’

Change Values Of Decrypt Password by SQL

April 23rd, 2023, posted in Oracle Queries
Share
SQL> create user test identified by test;
User created.

SQL> grant connect,resource to test;
Grant succeeded.

SQL> select username,password from dba_users where username='TEST';
USERNAME                       PASSWORD
------------------------------ ------------------------------
TEST                           7A0F2B316C212D67

SQL> conn test/test;
Connected.

SQL> conn sys/oracle as sysdba;
Connected.

SQL> alter user test identified by newpwd;
User altered.

SQL> conn test/test;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn test/newpwd;
Connected.

SQL> show user
USER is "TEST"

SQL> conn sys/oracle as sysdba;
Connected.

SQL> alter user test identified by values  '7A0F2B316C212D67';
User altered.

SQL> conn test/newpwd;
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn test/test;
Connected.

SQL> show user;
USER is "TEST"

SQL>
Share

Query To Get The Profile Option Configurations

February 20th, 2022, posted in Oracle EBS Application, Oracle Queries
Share

SELECT ot.user_profile_option_name,
TO_CHAR (v.level_id) level_id,
DECODE (v.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User ID’,
v.level_id)
level_meaning,
DECODE (v.level_id,
10001, ‘Site’,
10002, apl.application_name,
10003, frt.responsibility_name,
10004, u.user_name,
v.level_id)
level_name,
v.profile_option_value,
o.profile_option_name,
v.creation_date value_creation_date,
v.created_by value_created_by,
v.last_update_date value_last_updated_date,
v.last_updated_by value_last_updated_by
FROM applsys.fnd_profile_options_tl ot,
applsys.fnd_profile_options o,
applsys.fnd_profile_option_values v,
applsys.fnd_responsibility_tl frt,
apps.fnd_application_vl apl,
fnd_user u
WHERE v.level_value = frt.responsibility_id(+)
AND v.profile_option_id = o.profile_option_id
AND o.profile_option_name = ot.profile_option_name
AND ot.language = ‘US’
AND NVL (frt.language, ‘US’) = ‘US’
AND v.level_value = apl.application_id(+)
AND u.user_id(+) = v.level_value
ORDER BY ot.user_profile_option_name,
v.level_id,
DECODE (v.level_id,
10001, ‘Site’,
10002, ‘Application’,
10003, frt.responsibility_name,
10004, u.user_name,
v.level_id);

Share

Query To Find Legal Entity Organization Company Code

February 3rd, 2020, posted in Oracle Queries
Share
SELECT
xep.legal_entity_id "Legal Entity ID",
xep.name "Legal Entity",
hr_outl.name "Organization Name",
hr_outl.organization_id "Organization ID",
hr_loc.location_id "Location ID",
hr_loc.country "Country Code",
hr_loc.location_code "Location Code",
glev.flex_segment_value "Company Code"
FROM
xle_entity_profiles xep,
xle_registrations reg,
--
hr_operating_units hou,
-- hr_all_organization_units hr_ou,
hr_all_organization_units_tl hr_outl,
hr_locations_all hr_loc,
--
gl_legal_entities_bsvs glev
WHERE
1=1
AND xep.transacting_entity_flag = 'Y'
AND xep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.identifying_flag = 'Y'
AND xep.legal_entity_id = hou.default_legal_context_id
AND reg.location_id = hr_loc.location_id
AND xep.legal_entity_id = glev.legal_entity_id
--
-- AND hr_ou.organization_id = hou.business_group_id
AND hr_outl.organization_id = hou.organization_id
ORDER BY hr_outl.name


Share

SQL for Active Banks and Branches in EBS R12

January 21st, 2020, posted in Oracle Queries
Share
SELECT BankOrgProfile.*SQL for Active Banks and Branches in EBS R12,SQL for Active Banks and Branches in Oracle EBS R12,SQL for Active Banks and Branches in Oracle,Oracle Query,Oracle DBA,DBA Query,SQL for Active Bank and Branche in Oracle,

FROM HZ_ORGANIZATION_PROFILES BankOrgProfile, HZ_CODE_ASSIGNMENTS BankCA

WHERE 1 = 1

AND SYSDATE BETWEEN TRUNC (BankOrgProfile.effective_start_date)

AND NVL (TRUNC (BankOrgProfile.effective_end_date),

SYSDATE + 1)

AND BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'

AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')

AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'

AND (BankCA.STATUS = 'A' OR BankCA.STATUS IS NULL)

AND BankCA.OWNER_TABLE_ID = BankOrgProfile.PARTY_ID :
Share

Table That Stores Cached Queries

November 14th, 2018, posted in Oracle Queries
Share

This error is caused by corruptions that have crept into a table that stores cached queries. The only thing to do in this situation is to delete that table and clear cache by bouncing the Apache server.

There is patch available that can reduce the chance of these corruptions from happening in the future.

Please do the following

CREATE TABLE fnd_lov_choice_values_bak AS
SELECT * FROM fnd_lov_choice_values
;

DELETE fnd_lov_choice_values;

COMMIT;

Clear the cache

Then bounce the Apache server.

Then please download and apply patch Patch:9527712:R12.FWK.B

Afterwords, please try you test again.

Share