Archive for the ‘Oracle Queries’ Category

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

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

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

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

ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error

June 21st, 2022, posted in Oracle Queries
Share

I was getting this TNS error while creating Listner with “netca”, here is the screenshot for the same.

Listner,TNS-04404,ServiceAliasException,Service Alias

 

 

D:\app\db193\bin>netca

Oracle Net Services Configuration:
Configuring Listener:LISTENERCBT
ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error
  caused by: oracle.net.config.ConfigException: TNS-04414: File error
  caused by: TNS-04605: Invalid syntax error: Unexpected char or LITERAL "IEPDB1" before or at  IEPDB1 = (
  Error in file D:\TNS_ADMIN\tnsnames.ora

This is my windows machine and creating new listner for my newly created CDB. DB version is 19.3

As above error shows that I have some issue with my tnsnames.ora with some of my TNS Entry with IEPDB1.

In my environment TNS_ADMIN environment variable is set and it is pointing to my D:\TNS_ADMIN\tnsnames.ora

Here I found some junk characters near IEPDB1 TNS entry, I removed it and save the file and ran netca again…
Now, all went good..
It solved my problem !!!

Share