Julian Date and its sql conversion..

Time and again we pull up UPMJs from the tables via backend and need to convert the date that gets pulled into Gregorian. Following query when appended to any date column in a jde table will give the Gregorian date for the corresponding Julian:

TO_CHAR(TO_DATE(XXXXXX+1900000,’YYYYDDD’),’MM-DD-YYYY’)

Notes:
- where XXXXXX is the column name to convert.
- this only works on 19th century onwards.


If you are more comfortable with XLS following are the two conversion formulae that will help:

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+MID(A1,2,2),1,RIGHT(A1,3)) where A1 is your date in Julian

="1"&RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000") where A1 is your date in gregorian.

Convert JULIAN to standard date using SQL….

EnterpriseOne stores dates in Julian using the following format CYYDDD (“C” is the century, “YY” is the year and “DDD” is the number of days since the start of the year).
To convert a julian date to a standard (gregorian) date format:

TO_CHAR(TO_DATE(XXXXXX+1900000,’YYYYDDD’),’MM-DD-YYYY’)

Notes:
- where XXXXXX is the column name to convert.
- this only works on 19th century onwards.

if you need help putting it in a query lemme know…

Obtaining a list of tables for a specific column in Oracle DB.

If you are a CNC you definitely know that there may be multiple ways to do the same thing because that’s imperative for success. Now if I have a column name and I need to know all the tables in the database which have the particular column, I can easily do a XREF in fastpath and search for all the tables that use a specific data item. But in case you haven’t build the cross reference and there is a need to find it out , here’s what can be done in the old school way:

DBA_TAB_COLUMNS is a view that will help us obtain a list of tables for a specific column in Oracle database
.
example:

SELECT owner, table_name, column_name
FROM DBA_TAB_COLUMNS
WHERE column_name like ‘%ADDJ’
ORDER BY owner, table_name

The ID used to login to the db must have dba privilege for the query to work….. after all  what’s life without any limitations

SQL query to find Schema Sizes and space info….

SELECT T.TABLESPACE_NAME “TABLESPACE NAME”, 

TO_CHAR(T.TOTALMB/1048576,’99,999,999.99′) “TOTALMB”, 

TO_CHAR(F.FREEMB/1048575,’99,999,999.99′) “FREEMB”,  TO_CHAR((T.TOTALMB-F.FREEMB)/1048576,’99,999,999.99′) “USEDMB”,  TO_CHAR((F.FREEMB/T.TOTALMB)*100,’999.9′) “PCTFREE”,  TO_CHAR(T.MAXMB/1048576,’99,999,999.99′) “MAXMB”, OBJECTS “TOTAL_OBJECTS” FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTALMB, SUM(MAXBYTES) MAXMB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T, (SELECT TABLESPACE_NAME,SUM(BYTES) FREEMB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,  (SELECT TABLESPACE_NAME,COUNT(*) OBJECTS FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) S WHERE T.TABLESPACE_NAME=F.TABLESPACE_NAME(+) AND T.TABLESPACE_NAME=S.TABLESPACE_NAME(+);

SQL to find Out all the active users in the system

select distinct SCUSER NT_ID, ABALPH, ULUGRP
  from sys7334.F0092 a, sys7334.f98owsec b, proddta.F0101 c
 where a.ULUSER = b.SCUSER
   and a.ULUSER = c.ABALKY
   and b.SCEUSER = '01'
   and c.ABAT1 = 'E'
   order by ULUGRP;

SQL to find Out all the active users in the system

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, devdta.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
(‘JDEXYZ’, ‘JDE’, ‘AFPCRPMON’);

SQL that gives all the details of all the tables…

SELECT TDOBNM, –Table Name

(Select SIMD From OBJ7334.F9860 Where SIOBNM = TDOBNM AND SIFUNO = ‘TBLE’)
Table_Description, –TableDescription
TDSQLC, –Column Name
(Select FRDSCR From DD7334.F9202 Where FRDTAI = TDOBND AND FRSYR = ‘ ‘)
Column_Description, –Column Description
(Select DRDL01 From DD7334.F9210, F0005 Where FRDTAI = TDOBND AND LTRIM(RTRIM(FROWTP)) = LTRIM(RTRIM(DRKY)) AND DRSY = ‘H98′ AND DRRT = ‘DT’)
Column_Type, — Column Type (JDE)
(Select FRDTAS From DD7334.F9210 Where FRDTAI = TDOBND)
Column_Length, — Column Length (JDE)
(Select FROWDI From DD7334.F9210 Where FRDTAI = TDOBND)
DD_Item, — Data Dictionary Item
(Select FROWER From DD7334.F9210 Where FRDTAI = TDOBND)
Edit_Rule, — Edit (Validation) Rule
(Select FROER1 From DD7334.F9210 Where FRDTAI = TDOBND)
Edit_Rule_Parm1, — Edit Rule Parameter 1
(Select FROER2 From DD7334.F9210 Where FRDTAI = TDOBND)
Edit_Rule_Parm2, — Edit Rule Parameter 2
TDPSEQ — Column Number
FROM DV7334.F98711 WHERE TDOBNM Like ‘F09%’ ORDER BY TDOBNM, TDPSEQ

Scheduler Server Jobs Status Update Issue with Oracle Database

The scheduler kernel uses the *Scheduler record in F91300 to know what machine is running the Scheduler Service. With the machine name, then finds in F98611 the Logical Datasource for that machine
and uses the value in the Database Name as the Server Map datasource for the bootstrap tables.

When the database is other than Oracle (hence ODBC), the Database Name field value coincides with the naming convention of Datasources for OneWorld/E1 (i.e ENTSERVER – B7334 Server Map).
But if the database is Oracle, the Database Name is the ORACLE DB name (i.e. OWERP8, which does not exist as a ‘Server Map’ datasource in F986110 for the Server).

Manually create a logical datasource that is a copy of the Server Map datasource by following the instructions below:

1. Sign into EnterpriseOne.

2. Run the “Database Data Sources” application (P986115).

3. Select the “ServerName – B7333 Server Map” from the “Machine Search & Select” form.

4. Make sure that the value of the “Data Source Use” search field of the “Work With Data Sources” form is set to “DB”. Then, click on the Find button.

5. Search for “ServerName – B7333 Server Map” data source in the grid. When you have found the data source in the grid, hightlight it and click on the Copy button of the “Work With Data Sources” form.

6. For the name the new database datasource, use the name of the Oracle database that appears in the JDB9900245 error message. Do not change any other fields on the “Data Source Revisions” form.

7. Click on the OK button of the “Data Source Revisions” form to save the new database datasource.

8. Exit out of OneWorld.

9. Restart the EnterpriseOne services for the change to take effect.

Corrupt Scheduler Server Record..

When JDE scheduler hangs up for certain reasons and none of the schedule jobs look like they will be executed, its very tempting to restart the scheduler server from the P91300 application. But remember in JDE there is a concept which states that just because an option is there it doesn’t necessarily mean that it will work (Product Packaging anybody!!!).
If you try to stop and start the scheduler server from the P91300 application, it corrupts the *Scheduler record in the system.f91300 table.
To start the scheduler server again stop the jde services, update the SJSCHUSER column with the security server port number for the setup and restart JDE services again.

Creating Job Queues in XE/ERP8

In case of xe/ERP8 job queues are goverened directly by the start/stop scripts rather than by entries in F986130. Create new job queues in case of unix environments the RunOneWorld and the EndOneWorld scripts need to be modified
Define a OWQUE# value at the top of the script and runqueue.sh statemet at the bottom.

OWQUE##=”QUEABCD” $SYSTEM/bin32/runque.sh $OWUSR $OWPWD $OWENV $OWQUE15 UBE 5 > $SYSTEM/bin32/ubeque2.log 2>&1 &

On UDC 98/JQ add the new Job Queue name

If you want your users to override job queues when submitting UBE's , just add the same queue info in P986130. This is important in the later releases but in XE/ERP 8 its needed only if Override Jobqueue is in use.

When a user Selects Override JobQueue, the application fetches from the table F986130 and this app writes data into the table. 

The process is much simpler in version 8.9 or higher.

With the advent of the new Queue kernel in version 8.98 the queue can be configured entirely via the P986130.

In menu GH9013 go to Work with Job queues, click on Add, fill in the details and you are good to go. All that is left to do is add go to 98|JQ UDC and add the name of your queue.