Archive for the ‘Oracle Queries’ Category

« Older Entries

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

Find the Seeded Table,Transactional Tables, Static Data

September 11th, 2022, posted in Oracle EBS Application, Oracle Queries
Share

You can tell based on the TABLESPACE the table belongs to – this is the new Oracle Applications Tablespace Model (OATM)

Run the following code to see:

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%SEED%' -- seeded data
AND table_name LIKE 'FND%'

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%TX%' -- transaction data
AND table_name LIKE 'FND%'

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%ARCHIVE%' -- static data
AND table_name LIKE 'FND%'
Share
read more no comments

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory

July 10th, 2022, posted in Oracle Queries
Share

Recently I have installed Oracle 19c Database binary to windows machine. And after that I started DBCA to create database.

In “Configuration options” steps I got memory error. i.e. [DBT-50000] Unable to check for available memory
Earlier I selected 6GB for my SGA but after this error I had selected 1GB only. But still was facing this issue.

Here is the screenshot for the same.

[DBT-50000] Unable to check for available memory,Unable to check for available memory,DBT-50000,Oracle database,dba database

 

After searching oracle docs for known issue, I found, it’s a bug. One can find details about this bug on following oracle notes.

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory in “Specify configuration option” (Doc ID 2631718.1)

Workaround for this issue is,

Run dbca with “-J-Doracle.assistants.dbca.validate.ConfigurationParams=false” like a bellow command,

$> dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false

This will solve your issue and DBCA will run smooth, no issues than !!

Share
read more no comments
« Older Entries