Handy sqls for JDE CNC

-

 Find out all checked out objects using SQL
============================
select * from ol920.f9861 where sistce=3;

Access To Environments
==================
To find out what users or groups have access to a certain environment run the following SQL:
select * from sy920.f0093 where llll='<enviornment>';

Package Build Count
===============
How many packages of each type per environment did you build last year?
Here is the SQL :
select hhpkgname, hhpathcd, year(date(char(hhblddte+1900000))) as hhblddte from sy920.f96215 where hhpkgname not like 'JJ%' --remove the ESUs and hhdatp='CLIENT' --type of build and hhbldsts=50 --build completed successfully

Changing Status of Package Using SQL
=============================
Below is the SQL statement that I use to mark the package as not deployed if it shows deployed:-
update sy920.f9603 set PHISPTF=0 where phpkgname="PACKAGENAME";

?Important Tables used by Object Management Workbench (OMW) or Object Librarian (OL)
========================================================

All the tables used by Object Management Workbench are located in the SYSTEM data source (e.g.: System – 810). OMW/OL uses the following tables:

1) F98210 – Logging Header
Contains header information including the project, object type, object name, time stamps, path codes, data sources, locations and action type.

2) F98211 – Logging Detail
Contains detailed information on the errors and error codes for the projects and objects included in the F98210.

3) F98220 – Project Master
Detailed information on all newly created projects and all default projects.

4) F98221 – Project Users
Includes information on all the users in the EnterpriseOne software, the projects they are added to and the role numbers they are assigned with.

5) F98222 – Project Objects
This log details all EnterpriseOne objects that are added to Object Management Workbench projects. It includes the object name and type, the path code and project name.

6) F98223 – Allowed Actions
All the allowed actions assigned to user roles in the Object Management Configuration (OMC) are detailed in this table.

7) F98224 – Status Activity Rules
Details all project status activity rules.

8) F98225 – Transfer Activity Rules
Details all the object transfer activity rules.

9) F98230 – System Settings
Contains all OMC information other than activity rules and notification subscriptions.

10) F9829 – Notification Subscriptions

** To list ESUs

========

select distinct(SUPKGNAME), SUPATHCD from SY920.F9672 where SUPATHCD = 'PS920';



Execute the following SQL command to get all the active users with a particular role (ABCD)
===========================================================
select distinct SCUSER NT_ID, ABALPH UserName from sys7334.F0092 a, sys7334.F98OWSEC b, testdta.F0101 c where a.ULUSER = b.SCUSER and a.ULUSER = c.ABALKY and a.ULUGRP like `%ABCD%` and b.SCEUSER = `01` and c.ABAT1 = `E` and b.SCUSER not in ( `JDE` );
Check Checkin/out status of Objects

Useful Queries Check-in/out
========================
Objects Checked out -
select sipathcd as "Path Code", simkey as "Machine", SIOBNM, siuser as "User ID" from OBJ7334.F9861 where SISTCE != `1` order by siuser, siobnm;

Versions Checked out -
select VRENHV, VRUSER as "User", VRPID as "Object", VRVERS as "Version", VRMKEY as "Machine" from DV7334.F983051 where VRCHKOUTSTS=`Y` order by vruser, vrpid, vrvers;

Objects created but never Checked-in -
Select POOMWUSER, POOMWOBJID, POOMWMKLEY, POPATHCD from SYS7334.F98222 where POOMWOT !=`UBEVER` and POOMWOST=01 and POOMWCHS=`1` order by POOMWUSER, POOMWOBJID, POPATHCD;

Versions created but never Checked-in -
Select POOMWUSER, POOMWOBJID, POOMWMKEY, POPATHCD from SYS7334.F98222 where POOMWOST=01 and POOMWCHS=`1` and POOMWOT=`UBEVER` order by POOMWUSER, POOMWOBJID, POPATHCD;


Sometimes passwords are not documented as expected.  Follow these steps to decrypt 12c Weblogic encrypted passwords.

Note: These steps work on both 11g and 12c.

Step-by-step guide
Change directory to $ORACLE_HOME/common/bin (/u01/app/oracle/middleware/oracle_common/common/bin)
cd /u01/app/oracle/middleware/oracle_common/common/bin
Run wlst.sh and pass the following parameters
./wlst.sh
domain =  "/u01/data/domains/TSCOM01P_domain" – change as needed.

domain =  "/u01/data/domains/TSCOM01P_domain"

service = weblogic.security.internal.SerializedSystemIni.getEncryptionService(domain)

encryption = weblogic.security.internal.encryption.ClearOrEncryptedService(service)

print encryption.decrypt("{AES}........TSBl5RfvPRKhI=")

Note :  "{AES}........TSBl5RfvPRKhI=" - this Encypted password, you will get it from boot.properties in $DOMAIN_HOME/server/AdminServer/boot.properties.   

Note: The processes do not have to be running.



Example:

-bash-4.1$ cd $ORACLE_HOME/oracle_common/common/bin
-bash-4.1$ ./wlst.sh

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> domain =  "/u01/data/domains/TSCOM01P_domain"
wls:/offline> service = weblogic.security.internal.SerializedSystemIni.getEncryptionService(domain)
wls:/offline> encryption = weblogic.security.internal.encryption.ClearOrEncryptedService(service)
wls:/offline> print encryption.decrypt("{AES}F6oK5iZ........SBl5RfvPRKhI=")
nnnnnnnnn
wls:/offline>
Where 'nnnnnnnnn' above will be the decrypted password.  The above works for jdbc schema passwords as well.

Issue Faced
------------------------
There is possibility that you may get the below error if the encrypted password has forward slash(\) in the end(Faced this issue in PTHY5O)
print encryption.decrypt("{AES}lPfRTSJnlMy+KYG0lXF2H9LaB2eMGRn6S3ML7Huil/E\=")

Traceback (innermost last):

File "<console>", line 1, in ?

at weblogic.security.internal.encryption.JSafeEncryptionServiceImpl.decryptBytes(JSafeEncryptionServiceImpl.java:139)

at weblogic.security.internal.encryption.JSafeEncryptionServiceImpl.decryptString(JSafeEncryptionServiceImpl.java:187)

at weblogic.security.internal.encryption.ClearOrEncryptedService.decrypt(ClearOrEncryptedService.java:96)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

weblogic.security.internal.encryption.EncryptionServiceException: weblogic.security.internal.encryption.EncryptionServiceException

In that case please remove the forward slash(\) in the end

eg:

print encryption.decrypt("{AES}lPfRTSJnlMy+KYG0lXF2H9LaB2eMGRn6S3ML7Huil/E=")

Comments

Post a Comment

Popular posts from this blog

Oracle Cloud Infrastructure(OCI) - Part2

-
Image
In my earlier post (OCI -Part1 ), we have gone through OCI basic details, let's dive deep more into it. Before that, we need to u nderstand the following concepts and terminology to help you get started with Oracle Cloud Infrastructure.  Note: OCI is re-branding of Bare Metal Cloud Service (BMCS) BARE METAL HOST Oracle Cloud Infrastructure provides you control of the physical host (“bare metal”) machine. Bare metal compute instances run directly on bare metal servers without a hypervisor. When you provision a bare metal compute instance, you maintain sole control of the physical CPU, memory, and network interface card (NIC). You can configure and utilize the full capabilities of each physical machine as if it were hardware running in your own data center. You do not share the physical machine with any other tenants. REGIONS AND AVAILABILITY DOMAINS  Oracle Cloud Infrastructure is physically hosted in regions and availability domains. A region is a localized geographic area, an...
Read more

Script to display the SQL text for a specific SQL_ID

-
--> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Parameters: sql_id --> To Run:     @sqltext.sql sql_id --> Example:    @sqltext.sql 27b3qfm5x89xn --> --> Copyright 2020@The Ultimate SQL Tuning Formula set echo off set define '&' set verify off define _sql_id=&1 set verify off set feedback off set linesize 200 set heading on set termout on col sqltext format a100 word_wrapped select  sql_text as sqltext from gv$sqltext where sql_id = '&_sql_id' order by piece; **************************************************************************************** --> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Caution:  ...
Read more

How to Change Your EnterpriseOne Environment Color

-
Image
Sometimes users start working and then forget which EnterpriseOne environment they are logged into. Can you tell which environment you are logged into? Luckily, there is a new UDC that allows you to change your EnterpriseOne environment color. You can now set up environments to be color-coded for when you need a clear visual distinction as to which environment you are logged into. This enables you to quickly identify the non-production environments by color. The new UDC is 98/CO and is available in EnterpriseOne Tools Release 9.2.4 with Applications Release 9.1 and 9.2. The image below shows the default EnterpriseOne environment color. However, you can easily change the color to reflect different environments. For example, DV could be red, PY could be green, and PD could be yellow, as shown below. To do so, just add a new row in the User Defined Codes section, enter the EnterpriseOne environment name and the HEX value of the color you want the environment to be, and ...
Read more