Archive for the ‘Oracle Queries’ Category

« Older Entries
Newer Entries »

Oracle APPS User Password From Backend

November 19th, 2023, posted in Oracle Queries
Share

Below steps to get the forgotten apps user password in oracle apps R12.

Step 1:

Connect to sys user

SQL> show user USER is “SYS”

Step 2:

Create function to know the encrypted password

SQL>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’;
/ Function created.

Step 3:

Query for password
SQL> set linesize 200 long 300
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’; ENCRYPTED_FOUNDATION_PASSWORD
—————————————————————————————————- ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391

Step 4:

Apps password using encrypted guest password

SQL> SELECT
apps.decrypt_pin_func
(‘GUEST/ORACLE’,
‘ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391’)
from dual;
APPS.DECRYPT_PIN_FUNC
(‘GUEST/ORACLE’,
‘ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391’)
———————————————————————————————————————————————
APPS

Step 5:

Test the password is working fine or not

SQL> conn apps/APPS123;
Connected.
Share
read more no comments

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
read more no comments

Retrieve Forgotten Apps Password in Oracle EBS R12

February 20th, 2023, posted in Oracle Queries
Share
I am going to share steps to retrieve the password in Oracle Apps.
But no guarantee that it will work on all EBS version. It was tested on R12.2.3.
 

Steps:

 
1: log in to the database server with sys user
 sqlplus / as sysdba
 
2: Create Function to decrypt the encrypted password
SQL> 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’;  /
 
3 : Query for Encrypted password
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;

Output

 ENCRYPTED_FOUNDATION_PASSWORD
  ——————————————————————————–  ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A
 
4: Past the Encrypted password from the above query output into the below query and execute
SELECT apps.decrypt_pin_func
(‘GUEST/ORACLE’,’ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A’)
from dual;

Output

 APPS.DECRYPT_PIN_FUNC
(‘GUEST/ORACLE’,’ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A’)
  ——————————————————————————–
  oracle123
 
5: Test apps password is working or not
 SQL> conn apps/oralce123;
Connected.
Share
read more no comments

How To Get Last Record From The Table In Oracle Sql

October 30th, 2022, posted in Oracle Queries
Share

select * from (
  select * from oe_order_headers_all
   order by creation_date desc
  )
 where rownum = 1;

Share
read more no comments

AP_SUPPLIERS , AR_CUSTOMERS DETAILS QUERY IN ORACLE APPS R12

October 16th, 2022, posted in Oracle Queries
Share
AP_SUPPLIERS MASTER DETAILS QUERY:-
*****************************************

select  a.SEGMENT1 vendor_code,    
       b.VENDOR_ID,
       a.VENDOR_NAME,
       b.VENDOR_SITE_ID,
       (select organization_id
         from hr_operating_units
        where organization_id=b.ORG_ID
       )ou_id,
        (select name
         from hr_operating_units
        where organization_id=b.ORG_ID
       )operating_name,
       b.VENDOR_SITE_CODE,
       (b.ADDRESS_LINE1||b.ADDRESS_LINE2||b.ADDRESS_LINE3||b.CITY||b.STATE||b.ZIP) address,
       null GST_REGISTRATION_NO,    
       c.PAN_NO,
       b.state
  from ap_suppliers a,
     ap_supplier_sites_All b,
     JAI_AP_TDS_VENDOR_HDRS c,
     JAI_CMN_VENDOR_SITES d
where a.vendor_id=b.vendor_id
  and b.VENDOR_ID=c.vendor_id(+)
  and b.VENDOR_SITE_ID=c.VENDOR_SITE_ID(+)
  and b.VENDOR_ID=d.vendor_id(+)
  and b.VENDOR_SITE_ID=d.VENDOR_SITE_ID(+)
   AND (NVL(A.ENABLED_FLAG,'N')='Y' OR NVL(D.INACTIVE_FLAG,'N') <> 'N')
  and b.INACTIVE_DATE is  null
  and a.end_date_active is null
  AND A.SEGMENT1 = 'PRMAAA1'
 order by a.VENDOR_NAME,b.VENDOR_SITE_CODE,b.ORG_ID

AR_CUSTOMERS MASTER DETAILS QUERY:-
**********************************************

select hp.party_number customer_number,
       hca.cust_account_id customer_id,
       hp.party_name customer_name,
       hca.account_number customer_account_number,
       hcas.org_id ou_id,
       (select name from hr_operating_units where organization_id = hcas.org_id) operating_unit_name,
       HCAS.PARTY_SITE_ID CUSTOMER_SITE_ID,
        HPS.PARTY_SITE_NUMBER CUSTOMER_SITE_NUMBER,
       (   HL.ADDRESS1
        || HL.ADDRESS2
        || HL.ADDRESS3
        || HL.ADDRESS4
        || HL.CITY
        || HL.POSTAL_CODE)
          ADDRESS,
       NULL GST_REGISTRATION_NO,
       jac.PAN_NO PAN_NO,
       hl.state,
       HP.PARTY_TYPE CUSTOMER_TYPE
from hz_cust_acct_sites_all hcas,
              hz_cust_accounts_all hca,
              hz_parties hp,
              hz_party_sites hps,
              hz_locations hl,
              JAI_CMN_CUS_ADDRESSES jac
where 1=1
--and hca.account_number = 'RDAAAG2'
and hca.cust_account_id = hcas.cust_account_id
and hca.party_id = hp.party_id
and hcas.party_site_id = hps.party_site_id
and hca.party_id = hps.party_id
and hps.location_id = hl.location_id
and jac.address_id(+) = hcas.CUST_ACCT_SITE_ID
order by customer_name
Share
read more no comments
« Older Entries
Newer Entries »