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
Posts Tagged ‘Oracle DBA’
Query To Find Legal Entity Organization Company Code
February 3rd, 2020, posted in Oracle QueriesSQL for Active Banks and Branches in EBS R12
January 21st, 2020, posted in Oracle QueriesSELECT BankOrgProfile.*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 :
NID-00135: There are number active threads.
December 29th, 2019, posted in Oracle QueriesNID-00135: There are number active threads.
SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- PROD MOUNTED [oracle@oracle sujeet$ nid TARGET=SYS/sys123 DBNAME=PROD SETNAME=YES DBNEWID: Release 11.1.0.7.0 - Production on Thu Apr 26 18:04:18 2018 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to database ACSEBSP (DBID=2199446376) Connected to server version 11.1.0 NID-00135: There are 1 active threads Change of database name failed during validation - database is intact. DBNEWID - Completed with validation errors.
Solution :
Action: Ensure that all threads are closed before retrying the operation. Start and open the database to perform crash recovery, then shut down with the NORMAL or IMMEDIATE options to close it cleanly. Finally, try running the utility again.
SQL> alter database open; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. [oracle@oracle sujeet$ nid TARGET=SYS/sys123 DBNAME=PROD SETNAME=YES
ORA-28014: Cannot Drop Administrative Users
December 2nd, 2019, posted in Oracle QueriesORA-28014: cannot drop administrative users
The below error is encountered when I was trying to drop schema from 12C database :
SQL> drop user BKP CASCADE; drop user BKP CASCADE * ERROR at line 1: ORA-28014: cannot drop administrative users Solution :
SQL> alter session set "_oracle_script"=true; Session altered. SQL> drop user BKP CASCADE; User dropped.