Simple script for getting JDE DD descriptions for column names in oracle

select column_name, frdscr, ltrim(rtrim(substr(column_name, 3, length(column_name)-2))), ltrim(rtrim(frdtai))
from all_tab_columns, dd900.f9202
where FRSYR = '  '
and ltrim(rtrim(substr(column_name, 3, length(column_name)-2))) = ltrim(rtrim(frdtai))
and table_name = 'F4201' and owner = 'TESTDTA'

Load testing RTE–generating the load

I don’t know if I found a bug, but the SQLServer trigger for F90710 does not fire for bulk inserts.

so if you do “insert into select * from”, not all of the rows get a new unique ID and a timestamp.

So, you can use this little gem to get some records into the F90710 for load testing:

Firstly wait till there is a little bit of data in the F90710 and then execute this:

select * into testdta.F90710SRM from TESTDTA.F90710 where etevntname = 'RTSOOUT' ;

You’ll now have a copy of the F90710 without triggers.

Simply choose a record that you want to replicate in the SRM table and get it’s ETEVNTID “'E90LOG8_2181109774_6015_4284_083020111637491'”  Plug that into the yellow bit below

-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1

WHILE (@I <= 500)
BEGIN

    insert into TESTDTA.F90710
    (ETEVNTID,ETEVNTSEQ,ETEVNTTIME,ETEVNTNAME,ETEVNTTYPE,ETEVNTST,ETENV,ETEVNTUSER,ETUGRP,ETOBNM,ETVER,
    ETEVNTSNS,ETEVNTSCOPE,ETEVNTHOST,ETEVNTSRT,ETEVNTBSFN,ETFCTNM,ETEVNTPRID,ETEDATA,ETPID,ETUSER,
    ETMKEY,ETUPMJ,ETUPMT)
    select
    rtrim(ETEVNTID) + convert(varchar(4),@I),ETEVNTSEQ,ETEVNTTIME,ETEVNTNAME,ETEVNTTYPE,ETEVNTST,ETENV,ETEVNTUSER,
    ETUGRP,ETOBNM,ETVER,ETEVNTSNS,ETEVNTSCOPE,ETEVNTHOST,ETEVNTSRT,ETEVNTBSFN,ETFCTNM,ETEVNTPRID,
    ETEDATA,ETPID,ETUSER,ETMKEY,ETUPMJ,ETUPMT
    from TESTDTA.F90710srm where rtrim(ETEVNTID) = 'E90LOG8_2181109774_6015_4284_083020111637491';
       
        PRINT 'Row No = ' + CONVERT(VARCHAR(4), @I)
        SET @I = @I  + 1
END

The above will place 500 rows into the table and will fire the triggers to get a proper sequence etc.

I want my RTE events, but only from certain programs

Is this something that you want from JDE.  Sure you love RTE events, you love their speed and reliability (um, do you detect a certain amount of sarcasm)? No RTE is pretty good for getting some real time messages out of the JDE.

So clients want their RTE from P4210, but do not want them when R42950 runs.  They could modify the SOE master business function and add some exceptions there, but that is fraught with danger.

So, I’ve devised a simple and cunning plan to only process the RTE records that you want.

Firstly, create a UDC 55|RT with a code length of 10.

Add the programs that you want RTE’s to fire from in this list.  A simple way to get a list is to turn of deletion of EVENTS and look at the data in the F90710.  Group by event type and OBNM, and you’ll soon see the ones that you want and the ones that you don’t.

Now, I know you could filter these out further down the track (like in SOA), but you might find (like me) that the performance of the RTE is less than satisfactory.

So, I’m going to cull the records in the F90710, so I get as many decent transactions into the JSM queues as I can.  Behold, a modification to the trigger that JDE installs on the F90710.

Any the beauty of it is, simplicity! 

It just checks the OBNM of the incoming F90710 record, if it’s in the list of acceptable programs – we let it through.  If not, it stays at 2 (just in case we need to process it later).  No need to change the trigger for more programs, just add to the UDC.

Note that this trigger is designed for F90710 that is in TESTDTA.  This is also SQLServer syntax (I did not have to tell you that if you are still reading this!).  If you are going to put this into prod, you’ll need to prefix the owner of the UDC table with database name.  Also, there are NO guarantees or Warranties with this, you must do lots of testing before putting this into PROD!

 

USE [JDE_DEVELOPMENT]
GO
/****** Object:  Trigger [TESTDTA].[F90710_EVT_RIB]    Script Date: 08/31/2011 12:55:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [TESTDTA].[F90710_EVT_RIB] on [TESTDTA].[F90710] for insert as
begin
  set nocount on

  declare @nRowsAffected int, @seqnum decimal(38), @eventid char(255), @OBNM nchar(10), @ValidProgram int

  select @nRowsAffected = count(*) from inserted
  select @OBNM = ETOBNM from inserted
  select @validProgram = count(*) from testctl.f0005 WHERE DRSY = '55'    AND DRRT = 'RT' and DRKY = @OBNM

  if (@nRowsAffected > 0 and @validProgram > 0)
  begin
    declare inserted_cursor cursor local for select ETEVNTID from inserted
    open inserted_cursor
    fetch next from inserted_cursor into @eventid   
   
    if (@@fetch_status = 0)
    begin
      close inserted_cursor
      deallocate inserted_cursor

      declare seqnum_cursor cursor local dynamic scroll_locks for select USUKID from TESTDTA.F90730 where USOBNM = 'EVENT_SEQ' for update of USUKID
      open seqnum_cursor
      fetch next from seqnum_cursor into @seqnum
      if (@@fetch_status = 0)
      begin
        update TESTDTA.F90730 set USUKID = @seqnum + 1 where current of seqnum_cursor
        update TESTDTA.F90710 set ETEVNTTIME = getdate(), ETEVNTST = 3, ETEVNTSEQ = @seqnum where ETEVNTID = @eventid
      end
      close seqnum_cursor
      deallocate seqnum_cursor
    end
  end
end