Shannon's JD Edwards CNC Blog: April 2015

Thursday, 30 April 2015

simple… return integer to unicode character parameter.

It occurs all of the time, you track internal business function success or failure as an integer, and then you finally want to return it to NER / ER / TER as a char, but a double byte char because it’s unicode.

This is somewhat painful, but I have the remedy. It also uses the crazy ?: operator, which is sure to confuse all of your friends.

In this case if i is non 0, it’ll return 1, else it’ll return 0 – nice.

       lpDS->cErrorCode=i?_J('1'):_J('0') ;

This could be written:

if (i != 0)

{

lpDS->cErrorCode=_J(‘1’) ;

}

else

{

lpDS->cErrorCode=_J(‘0’) ;

}

Tuesday, 28 April 2015

Different MO viewer in IE

The inline editor in JDE is nice now, but there are some dangers in activating it that you need to be aware of!

ActiveX

If you are using the traditional ActiveX controls for editing media objects, they look like this:

Note that this activeX control is showing an MO that was created in the browser based editor, see the html control characters. This is an old and clunky control too, I’d avoid it if I were you.

The contents from this look like the following in F00165:

{ \ r t f 1 \ a n s i \ a n s i c p g 1 2 5 2 \ d e f f 0 \ d e f l a n g 1 0 3 3 { \ f o n t t b l { \ f 0 \ f s w i s s \ f p r q 2 \ f c h a r s e t 0   C o u r i e r   N e w ; } } 
  
 { \ c o l o r t b l   ; \ r e d 0 \ g r e e n 0 \ b l u e 0 ; } 
  
 \ v i e w k i n d 4 \ u c 1 \ p a r d \ c f 1 \ f 0 \ f s 2 0   j h j \ p a r 
  
 \ p a r 
  
 n e w   l i n e \ p a r 
  
 \ p a r 
  
 a s d s a d   a s d s a d   n e x t \ p a r 
  
 \ p a r 
  
 I   d u n n o \ p a r 
  
 }

You can see how this is actually written as RTF to the BLOB. This is rubbish and hard to pull apart. If you get the choice, avoid it.

Inline editor

It looks WAY cooler – see below. Also it’s the same on other browsers, which is nice. I recommend making this your standard. But, read on!

When you look into the F00165 gdtxft, you see the following text. Note that SQL developer is pretty lackadaisical about how it interprets the string. You think it’s totally RAD and interpreting a unicode string, it’s not. It’s actually displaying a byte at a time the varchar2 string that is coming back from my fancy f() function to cast the RAW to varchar.

The above is really important when it comes to cowboying.

(CLOB) T h i s   i s   a   m e d i a   O b j e c t . < b r / >

T h i s   i s   a   n e w   l i n e < b r / > s o   i s   t h i s

Note also that the formatting is TOTALLY different, it’s html based formatting. The <br/> etc is part of the text, but not shown in the HTML representation of the string, as it’s HTML. Awesome.

So, what you see from above is that you really need to choose one method or the other so that the text is formatted nicely. The problem will be when you come to print this media object text on a report, it’ll have html control characters OR it’ll have all the RTF stuff.

The choice between the two settings is in SM, illustrated below:

Change you just insert Media Objects into F00165?

Yes, OLE and file based is easy, text quite a bit harder = but possible. What you are going to find is that if you finally work out the correct casting / unicode and text conversion functions in your statement, JDE STILL will not show the text up as text, it’ll all be funny characters. This is because JDE store the code as little endian / big endian. This is not really strictly exactly the right use, but let me explain. unicode is AL16UTF16 (well my unicode) represents most characters in 2 (or more) bytes. Therefore the character H is 0 & 72 (decimal values for the two bytes). The data is stored in the second byte (when dealing with conversions and unicode routines in the DB). The issue with JDE is that it uses the first byte at the significant digit.

See above, if you just do an insert into the F00165 with something like:

insert into testdta.f00165
values (
'ABGT',
'12',
4,
0,
' ',
'HBECK',
115090,
163908,
'AL16UTF16',
' ',
' ',
' ',
' ',
' ',
' ',
0,
0,
utl_i18n.string_to_raw('Hello','AL16UTF16'));

You get “chinese like” characters come up in the APP. This is because JDE thinks that the first byte is significant, but oracle wrote the second one as significant – DAMN!

If you are a bit nerdy (like me), then there is always a solution. I wrote a java program that reversed the byte order of the CLOB byte array, therefore converting the oracle based CLOB character significance into something that JDE understands.

What does this mean, well for starters the combination of the java code and the SQL can have you adding media objects that can be read and then written / modified in JDE – nice! Also, what I learned from doing this means that I’m going to finally write a program that is going to change UBE QUEUEs without checking in or out versions – hack the BLOB in F983051 directly.

Friday, 24 April 2015

JD Edwards scheduler catch up canceller

You’ve all been in this situation. The scheduler has been down for the weekend (intended or not) and now you need to switch it on… But it’s painful. ALL of the jobs are going to launch and play catch up. You really do not care for running all of the jobs… again… one after the other… duplicates… What are you going to do?

Well, you could spend the next 3 hours double clicking queues, holding jobs, deleting next jobs setting status’ – but I’m not going to do that. I’m going to use my fried SQLCowboy to get the job done – yeeeehar!

First statement holds all of the queues for the ones that are being used in your scheduler (note that I did not use active jobs and I also did not limit by machine – which you might need to).

I then enable the scheduler in JDE

Then you can delete the waiting jobs that have been launched by the SCHEDULER user

Then release the queues

update sy910.f986130 set qcqusts = '02' where QCJOBQUE in (
select distinct CASE  WHEN sjjobque IS NULL THEN N'QBATCH' WHEN sjjobque = ' ' THEN N'QBATCH' ELSE sjjobque END from sy910.f91300 
);
commit;

--ENABLE THE SCHJEDULER NOW IN JDE

--WAIT FOR IT TO CATCH UP, IT CAN TAKE A WHILE


--check for active jobs

select count(1) from svm910.f986110 where jcjobsts = 'W' and jcuser = 'SCHEDULER'  ;

--one you are happy with catchup, delete the W jobs

delete from svm910.f986110 where jcjobsts = 'W' and jcuser = 'SCHEDULER' ;

--activate your queues again
  update sy910.f986130 update sy910.f986130 
set qcqusts = '02' 
where QCJOBQUE in (
select distinct CASE  WHEN sjjobque IS NULL THEN N'QBATCH' WHEN sjjobque = ' ' THEN N'QBATCH' ELSE sjjobque END 
from sy910.f91300 
);
commit;

select count(1) from svm910.f986110 where jcjobsts = 'W' and jcuser = 'SCHEDULER'  ;

delete from svm910.f986110 where jcjobsts = 'W' and jcuser = 'SCHEDULER' ;

  update sy910.f986130 
set qcqusts = '01' 
where QCJOBQUE in (
  select distinct CASE  WHEN sjjobque IS NULL THEN N'QBATCH' WHEN sjjobque = ' ' THEN N'QBATCH' ELSE sjjobque END 
  from sy910.f91300 
);
commit;
set qcqusts = '01' where QCJOBQUE in (
select distinct CASE  WHEN sjjobque IS NULL THEN N'QBATCH' WHEN sjjobque = ' ' THEN N'QBATCH' ELSE sjjobque END from sy910.f91300 
);
commit;

Oracle application testing suite install woes–part 2

It’s the MOST frustrating thing to have a piece of software that should install in 15 minutes burn a day of time. This is a day that you’ll never get back and a day that you cannot change for, as your quote says “1 hour to install and test”. Far out!!!! It’s also potentially the most frustrating day that you’ve been on the planet.

I’ve had my fair share of installation problems with OATS, but I find that you must be a mega administrator of the machine, or nothing is ever going to work. Remember to run the installer as administrator too, just to get that out of the way. Check that regedit works and you can create keys under the software tree.

I did an install that failed on the oxe installer (this is a REAL problem area for me), and then no matter how much restarting and cleaning up, the installer would not work. It’s so painful.

C:\Users\shannon.moir>C:\software\OATS-W~2.287\\oxe\setup.exe /s /f1"C:\software
\OATS-W~2.287\\oxe\oxe_install.iss"

The command that it’s failing on is above, this has changed from the last release. I’m installing 12.5.0.1.

I tried about 8 times with FULL cleans that I’ve mentioned in a previous post. (regedit, sc delete OracleATSHelper) etc etc. Nothing worked.

I then decided to install the database manually, just by running C:\software\oats-win32-full-12.5.0.1.287\oxe\setup.exe, leaving all the defaults and remembering the password.

I then ran the OATS installer manually (not choosing the works burger radio button) and chose to use an existing oracle database. I chose all of the database defaults it gave me and entered the SYS password I chose earlier. And POW (very batman) I have a working OATS installation.

I might just use this method from now on…

introspective logging–why do I get this?

This tells you a lot about what is going on with a running UBE

Job Is Not Responding 
Job Is Not Responding 

Please look for the highlighted fields, correct the entries, and resubmit your request.

The above is annoying, but sensible. The job only is looking for these interrupts (when you say “hey enable logging”) when JDE is in ER and looping through it’s own engine. When the UBE is running a massive SQL statement, it’s not out there listening for you to tell it to enable logging.

Generally if you get the above warning you need to go into EM (if you are oracle or guess if you are other platforms [just kidding there are some other good monitoring tools out there'] and see the statement that is running. What you need to do is request logging / introspection when the UBE is likely to run some ER… / runtime engine code.

If the SQL statement takes 20 mins to run, you are not going to get introspective logging for ages…

Wednesday, 22 April 2015

Another quick lesson in media objects

Here is a nice example of a media object that does not work the first time…

Normal JDE screen with normal attachments.

The fact is we’ve upgraded and these attachments have not been moved to the new location. Therefore they are still pointing to the old location (funny that). I’m going somewhere, please stay with me. The OLD location let’s say \\oldservername\htmlupload is NOT available from the 910 web servers. So, when we try and look at the attachment, we get the following:

This is the web server telling us that it cannot get to the attachment. Media Object Fetch Image Failed! Noooo.

So the JAS instance cannot get to the location that is saved in the F00165 for this record. Check the MO record out with a query like the below (it might take a while).

select * from proddta.F00165 where gdgtfilenm like '%FILE-10-68-191-84-5219925678619586-1427694405303.xlsx';

We also get a handy message in server manager:

22 Apr 2015 15:57:59,184[SEVERE][JAS]Error: MediaObject file not found or accessable: \\nsgshsjdncls01\AR\E812\MediaObjects\HTMLUpload\FILE-10-68-191-84-5219925678619586-1427694405303.xlsx Corrective Action :Verify the Application Server service is running under a valid domain user ID.

But, JDE is not giving up – and neither should you!

The circled bit is the link that we are dealing with below

<a type="application/octet-stream" href="/jde/moqueue/nsgshsjdncls01/AR/E812/MediaObjects/HTMLUpload/FILE-10-68-191-84-5219925678619586-1427694405303.xlsx?stackId=1&amp;File=%2Fnsgshsjdncls01%2FAR%2FE812%2FMediaObjects%2FHTMLUpload%2FFILE-10-68-191-84-5219925678619586-1427694405303.xlsx">Hold down the Control key, and click here to save this media object</a>

You can see that the file link above (File=%2Fnsgshsjdncls01%2FAR%2FE812%2FMediaObjects%2FHTMLUpload%2FFILE-10-68-191-84-5219925678619586-1427694405303.xlsx) is actually a link to the old location. So JDE get’s angry and lets you use your browser credentials to try and grab the file. Honestly this is a better way of doing it – want to know why? You don’t have 100000 MO’s sitting in the “C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\servers\JDEPD_8080\stage\JDEPD_8080\app\webclient.war\moqueue” directory of your JAS servers – that NEVER get cleaned up.

The wrap is my PC can see the old MO location and therefore drag the attachment back to my browser. This fools everyone to thinking that their MO’s are okay – until you decommission your old deployment server and everything goes POP!

Also, the JVM does not take the hit on loading the content into memory, it’s just your browser – this is win / win to me.

Thursday, 16 April 2015

Getting bind variables from running SQL in em12c and find the UBE running it

Getting SQL bind variables can be tough.

Here is how I do it in EM12C.

goto performance –> SQL Monitoring to get to the page below

This is all of the tough SQL running at the moment.

Find the statement that you want and click the id (3rd column), you’ll get this screen

Now, click the circled button above and you’ll get this screen:

Choose show SQL Binds

Hit the save button and things get even better. em12c writes you a script with all of the variables defined, you can then run it in SQLPlus if you need to. Wow, that is awesome.

-- ============================================================================================== --
-- This file is generated by EM Database Express.			                          --
-- The SQL bind variables, if any, will be declared below.                                        --
-- ============================================================================================== --

declare
  KEY1    NCHAR(32)      := 'IMP';
  KEY2    NCHAR(32)      := 'ZZZ';
  KEY3    NCHAR(32)      := 'N';
  KEY4    NCHAR(32)      := '00133';
  KEY5    NCHAR(32)      := '00141';
  KEY6    NCHAR(32)      := '00146';
  KEY7    NCHAR(32)      := '00147';
  KEY8    NCHAR(32)      := '00178';
  KEY9    NCHAR(32)      := '00222';
  KEY10   NCHAR(32)      := '00244';
  KEY11   NCHAR(32)      := '00246';
  KEY12   NCHAR(32)      := '00250';
  KEY13   NCHAR(32)      := '00252';
  KEY14   NCHAR(32)      := '00253';
  KEY15   NCHAR(32)      := '00254';
  KEY16   NCHAR(32)      := '00255';
  KEY17   NCHAR(32)      := '00256';
  KEY18   NCHAR(32)      := '00257';
  KEY19   NCHAR(32)      := '00268';
  KEY20   NCHAR(32)      := '00275';
  KEY21   NCHAR(32)      := '00298';
  KEY22   NCHAR(32)      := '00314';
  KEY23   NCHAR(32)      := '00318';
  KEY24   NCHAR(32)      := '00352';
  KEY25   NCHAR(32)      := '00364';
  KEY26   NCHAR(32)      := '00601';
  KEY27   NCHAR(32)      := '00710';
  KEY28   NCHAR(32)      := '00756';
  KEY29   NCHAR(32)      := '00758';
  KEY30   NCHAR(32)      := '00840';
  KEY31   NCHAR(32)      := '00950';
  KEY32   NCHAR(32)      := '00963';
  KEY33   NCHAR(32)      := '00987';
  KEY34   NCHAR(32)      := '00988';
  KEY35   NCHAR(32)      := '00995';
  KEY36   NVARCHAR2(32)  := '115106';
  KEY37   NVARCHAR2(32)  := '115104';
  KEY38   NCHAR(32)      := NULL;
  KEY39   NCHAR(32)      := 'ZZZZZZZZZZZZ';
  KEY40   NCHAR(32)      := NULL;
  KEY41   NCHAR(32)      := 'ZZZZZZZZZZZZ';
begin
--  SELECT T0.GMCO, T0.GMAID, T0.GMMCU, T0.GMOBJ, T0.GMSUB, T0.GMANS, T0.GMDL01, T0.GMLDA, T0.GMBPC, T0.GMPEC, T0.GMBILL, T0.GMCRCD, T0.GMUM, T0.GMR001, T0.GMR002, T0.GMR003, T0.GMR004, T0.GMR005, T0.GMR006, T0.GMR007, T0.GMR008, T0.GMR009, T0.GMR010, T0.GMR011, T0.GMR012, T0.GMR013, T0.GMR014, T0.GMR015, T0.GMR016, T0.GMR017, T0.GMR018, T0.GMR019, T0.GMR020, T0.GMR021, T0.GMR022, T0.GMR023, T0.GMOBJA, T0.GMSUBA, T0.GMWCMP, T0.GMCCT, T0.GMERC, T0.GMHTC, T0.GMQLDA, T0.GMCCC, T0.GMFMOD, T0.GMUSER, T0.GMPID, T0.GMJOBN, T0.GMUPMJ, T0.GMUPMT, T0.GMCEC1, T0.GMCEC2, T0.GMCEC3, T0.GMCEC4, T0.GMIEC, T0.GMFPEC, T0.GMSTPC, T0.GMTXGL, T0.GMTOBJ, T0.GMTSUB, T0.GMPRGF, T0.GMTXA1, T0.GMR024, T0.GMR025, T0.GMR026, T0.GMR027, T0.GMR028, T0.GMR029, T0.GMR030, T0.GMR031, T0.GMR032, T0.GMR033, T0.GMR034, T0.GMR035, T0.GMR036, T0.GMR037, T0.GMR038, T0.GMR039, T0.GMR040, T0.GMR041, T0.GMR042, T0.GMR043, T1.MCMCU, T1.MCSTYL, T1.MCDC, T1.MCLDM, T1.MCCO, T1.MCAN8, T1.MCAN8O, T1.MCCNTY, T1.MCADDS, T1.MCFMOD, T1.MCDL01, T1.MCDL02, T1.MCDL03, T1.MCDL04, T1.MCRP01, T1.MCRP02, T1.MCRP03, T1.MCRP04, T1.MCRP05, T1.MCRP06, T1.MCRP07, T1.MCRP08, T1.MCRP09, T1.MCRP10, T1.MCRP11, T1.MCRP12, T1.MCRP13, T1.MCRP14, T1.MCRP15, T1.MCRP16, T1.MCRP17, T1.MCRP18, T1.MCRP19, T1.MCRP20, T1.MCRP21, T1.MCRP22, T1.MCRP23, T1.MCRP24, T1.MCRP25, T1.MCRP26, T1.MCRP27, T1.MCRP28, T1.MCRP29, T1.MCRP30, T1.MCTA, T1.MCTXJS, T1.MCTXA1, T1.MCEXR1, T1.MCTC01, T1.MCTC02, T1.MCTC03, T1.MCTC04, T1.MCTC05, T1.MCTC06, T1.MCTC07, T1.MCTC08, T1.MCTC09, T1.MCTC10, T1.MCND01, T1.MCND02, T1.MCND03, T1.MCND04, T1.MCND05, T1.MCND06, T1.MCND07, T1.MCND08, T1.MCND09, T1.MCND10, T1.MCCC01, T1.MCCC02, T1.MCCC03, T1.MCCC04, T1.MCCC05, T1.MCCC06, T1.MCCC07, T1.MCCC08, T1.MCCC09, T1.MCCC10, T1.MCPECC, T1.MCALS, T1.MCISS, T1.MCGLBA, T1.MCALCL, T1.MCLMTH, T1.MCLF, T1.MCOBJ1, T1.MCOBJ2, T1.MCOBJ3, T1.MCSUB1, T1.MCTOU, T1.MCSBLI, T1.MCANPA, T1.MCCT, T1.MCCERT, T1.MCMCUS, T1.MCBTYP, T1.MCPC, T1.MCPCA, T1.MCPCC, T1.MCINTA, T1.MCINTL, T1.MCD1J, T1.MCD2J, T1.MCD3J, T1.MCD4J, T1.MCD5J, T1.MCD6J, T1.MCFPDJ, T1.MCCAC, T1.MCPAC, T1.MCEEO, T1.MCERC, T1.MCUSER, T1.MCPID, T1.MCUPMJ, T1.MCJOBN, T1.MCUPMT, T1.MCBPTP, T1.MCAPSB, T1.MCTSBU FROM PRODDTA.F0901 T0,PRODDTA.F0006 T1 WHERE  (  ( T0.GMR002 = :KEY1 AND T1.MCRP20 <> :KEY2 AND T0.GMPEC <> :KEY3 AND T0.GMCO IN  ( :KEY4,:KEY5,:KEY6,:KEY7,:KEY8,:KEY9,:KEY10,:KEY11,:KEY12,:KEY13,:KEY14,:KEY15,:KEY16,:KEY17,:KEY18,:KEY19,:KEY20,:KEY21,:KEY22,:KEY23,:KEY24,:KEY25,:KEY26,:KEY27,:KEY28,:KEY29,:KEY30,:KEY31,:KEY32,:KEY33,:KEY34,:KEY35 )  )  AND  ( T0.GMUPMJ <= :KEY36 AND T0.GMUPMJ >= :KEY37 )  )  AND  ( T0.GMMCU BETWEEN :KEY38 AND :KEY39 )  AND  ( T1.MCMCU BETWEEN :KEY40 AND :KEY41 )  AND  ( T0.GMMCU=T1.MCMCU )  ORDER BY T0.GMAID ASC
end;

To find the culprit, you need to go back to this screen

Click the username field circled above

So with the above we can tell that it’s a batch job and that it’s PID is 1837 and it’s a runbatch process

So we can see in E1 that this is R55BLAHBLAH, nice one. We know the report that is running the crazy statement.

oracle in-memory and JD Edwards

I’ve been lucky enough to be exposed (said the vicar to the nun) to some oracle 12c in memory and do performance testing against this at one of my clients. This has been a very interesting exercise, of course I can only comment from basic DBA skills that I have, well – let’s be honest – basic everything skills.

I’ve been executing performance testing against the inmemory oracle 12c configuration that has been implemented in AWS. To be honest (sorry AWS) we are doing everything to try and get some performance back to the ERP, inmemory is one way of mitigating the issues that we are seeing with poor disk performance in the cloud. You can google in memory, but it’s a new 12C feature (you need to pay for it too) available in EE that allows you to pin tables in memory (wow). Not only this there is a heap of jiggery pokery that uses columnar compression and all sorts of things that allows you to somhow squeeze about 180GB of data into 21GB of inmemory allocation. TRUE!!!!

What tables do you put in memory?

Anything big really. Anything that is read a lot! There are probably a lot of other rules, but I’m dealing with an ERP OLTP environment, so that is all I really care about.

Some SQL you can use to see what is in memory. Note that big tables take a long time to put into memory, so when you start the database, you need to allow a couple of hours to put the data into memory. the database is available, but not fast until it’s all been loaded.

      select v.owner
   ,      v.segment_name name
    ,      v.populate_status status
    from   v$im_segments v;

PRODDTA    F03012    COMPLETED
PRODDTA    F55B30    STARTED
PRODDTA    F0111    COMPLETED
PRODDTA    F0006    COMPLETED
PRODDTA    F0901    COMPLETED
PRODDTA    F03B21    COMPLETED
PRODDTA    F03B11    COMPLETED
PRODDTA    F0115    COMPLETED
PRODDTA    F0150    COMPLETED
PRODDTA    F0116    COMPLETED
PRODDTA    F0101    COMPLETED
PRODDTA    F03B13    COMPLETED

  show parameter INMEMORY ;

                                           TYPE        VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
inmemory_clause_default                            string
inmemory_force                                     string      DEFAULT
inmemory_max_populate_servers                      integer     2
inmemory_query                                     string      ENABLE
inmemory_size                                      big integer 32G
inmemory_trickle_repopulate_servers_percent        integer     1
optimizer_inmemory_aware                           boolean     TRUE

Also what tables are configured:

select table_name
    ,    inmemory
    ,    inmemory_priority
    from   all_tables where owner = 'PRODDTA'
    order by inmemory desc;

What are the results?

We’ve been trying a bunch of permutations and combinations of inmemory to get the best results for this site and their usage and data. I believe that there is not going to be a single answer for all clients, but initially the results for inmemory are very promising. We were able to kick off all the scheduled jobs (a mixed load of 46 UBEs) and judge the performance of these against known benchmarks (existing on prem 812) etc. We’re also closely monitoring the DB performance metrics too, to make sure that we are not cooking it.

Initially inmemory causes much higher CPU, especially when higher rates of compression are applied to the tables. Despite the CPU being used a lot more, the overall results are better for us. Over 80% of the jobs run faster (some by MANY factors). There are a couple of exceptions, but this is where the optimizer is getting confused about in memory and whether it should be doing a table scan inmemory or an indexed lookup. Although this increase in CPU requirements is going to potentially make you need more EE licences and therefore more inmemory options, but hey – that’s the price of performance.

I was able to quantify the savings that inmemory provided with a figure in minutes (which was really nice and very real for the client). I calculated the number of seconds the complete schedule took in all DB configurations and then got a aggregated saving / cost in minutes for the entire run. It was a nice way of appreciating the difference that the tuning made.

Tuesday, 14 April 2015

using MERGE statement and getting ORA-30926: unable to get a stable set of rows in the source tables

This was a pain, but at the end of the day quite simple to solve. I had a large MERGE statement (I’d moved media objects to separate folders based upon change date) and needed to update these in bulk in the F00165 (2 million + attachments!)

My merge statement kept failing with the error above and I was scratching my head about what I’d done wrong. I used a script to populate F00165SRM (as seen in previous post) and then I used this as my master list to update the F00165 from. It was nice and simple. What I did not realise is that in pursuit of performance (damn me), I multithreaded the cr@p out of the script that populated F00165SRM, I launched a heap of them to try and catch up (under the pump again), I’d inadvertently launched a script twice, so I had a shed load of dupes in F00165SRM. this is an “essentially insert only” table, so I did not want to slow things down with uniqueness based PKs! So the reason I was getting ORA-30926 was because I had heaps of duplicates in the F00165SRM!

A quick script to deduplicate the table: (based upon)

 select count(1), mofilename
  from proddta.f00165srm
  group by mofilename
  having count(1) > 1 ;
  
  delete from proddta.f00165srm
where (rowid, mofilename) in (
select max(rowid), mofilename
from proddta.f00165srm
group by mofilename
having count(1) > 1);
commit;

First one to identify and second to delete (500000 rows – whoops). I was then able to run the merge successfully:

MERGE
  INTO    proddta.f00165 t3
  USING   (
          SELECT  t1.rowid AS rid, replace(t2.mofilename, 'Z:\','\\jdedatastore\JDEMediaObjects\MediaObjectsData\') as newMOLocation
          FROM    proddta.f00165 t1
          JOIN    proddta.f00165srm t2
          ON      substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1))) = substr(gdgtfilenm,instr(gdgtfilenm,'\',-1,1)+1, length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)))
          WHERE   instr(mofilename,'\',-1,1) > 1
            and instr(gdgtfilenm,'\',-1,1) > 1
            and  length(trim(mofilename))-(instr(mofilename,'\',-1,1)) > 5
            and length(trim(gdgtfilenm))-(instr(gdgtfilenm,'\',-1,1)) > 5       
            and  gdgtmotype = '5')      
  ON      (t3.rowid = rid)
  WHEN MATCHED THEN
  UPDATE
  SET     t3.gdgtfilenm = newMOLocation;
  commit;

That is very nice.

My statement is called an UPSERT – how cool is that!

Closing the loop on this:

Once again I had the ORA-30926 after cleaning up what I thought were all the duplicates, but there were more.

What I found was that you need to use the “JOIN” clause for the source table, this needs to be unique. Therefore the real test of duplicates was

  select count(1), substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
  from proddta.f00165srm
  group by substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
  having count(1) > 1 ;

Notice how I’m not checking the entire field, just the bit that my JOIN was joining on. I was then able to identify all of the offending records and delete them from the F00165SRM. Which this is once again a slightly more complicated version of the above example of de-duplication.

DELETE from proddta.F00165SRM 
where (rowid, substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))) in (
select min(rowid), substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
from proddta.f00165srm
group by substr(mofilename,instr(mofilename,'\',-1,1)+1, length(trim(mofilename))-(instr(mofilename,'\',-1,1)))
having count(1) > 1);

Quickly launch all scheduled jobs

Okay, I’m doing load testing and want to create some batch load, but don’t really know how. So the simplest thing that I can think of is run all of the scheduled jobs. Surely that is easy, there must be a button somewhere? NO!

Perhaps I can change the date and time on the server to fool it and get all of the jobs to run – jeepers might be a bit risky.

Ok, I’m going to go rogue on this problem:

I’m going to use runube at the command line and launch them all – easy!

select 'runube JDE PASSWORD PD910 *ALL ' || SJSCHRPTNM || ' ' || sjschver || NVL(sjjobque,'QBATCH') || ' Batch Hold Save' 
from sy910.f91300 where sjschjbstat = '01'
order by sjschsttime asc;

runube JDE PASSWORD PD910 *ALL R5503005 AALL001S             Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503006 AALL001S QCAR1      Batch Hold Save
runube JDE PASSWORD PD910 *ALL R04601    AALL00S              Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B13S AALL001S             Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503DTI5 AALL001S             Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503DTI4 AALL001S             Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B500XAALL005   QSTAT1     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413INNSW001S QREPORT    Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413ISMNG001S QBATCH     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413IVLDR001S QBATCH     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413IVGAD001S QBATCH     Batch Hold Save

Wait, there are blanks and NULLs for the QUEUE, this is annoying! Break out the advanced case statement for just this situation:

    select 'runube JDE PASSWORD PD910 *ALL ' || SJSCHRPTNM || ' ' || sjschver || CASE  WHEN sjjobque IS NULL THEN N'QBATCH' WHEN sjjobque = ' ' THEN N'QBATCH' ELSE sjjobque END  || ' Batch Hold Save' 
  from sy910.f91300 where sjschjbstat = '01'
  order by sjschsttime asc;

How cool is that case statement, so I’m saying…

when the queue is NULL, make it QBATCH, when it’s ‘ ‘ (a space) make it QBATCH, elsewise make it the queue name.

Note that I also had some problems with CASE and character set mismatch

ORA-12704: character set mismatch
12704. 00000 -  "character set mismatch"
*Cause:    One of the following
           - The string operands(other than an nlsparams argument) to an
           operator or built-in function do not have the same character
           set.
           - An nlsparams operand is not in the database character set.
           - String data with character set other than the database character
           set is passed to a built-in function not expecting it.
           - The second argument to CHR() or CSCONVERT() is not CHAR_CS or
           NCHAR_CS.
           - A string expression in the VALUES clause of an INSERT statement,
           or the SET clause of an UPDATE statement, does not have the
           same character set as the column into which the value would
           be inserted.
           - A value provided in a DEFAULT clause when creating a table does
           not have the same character set as declared for the column.
           - An argument to a PL/SQL function does not conform to the
           character set requirements of the corresponding parameter.
*Action:
Error at Line: 45 Column: 152

So I had to add the “CAST” or N’STRING’ or _J’STRING’ (that is a JDE C BSFN joke – get it?) ROTFL – NOT!

FROM:

    select 'runube JDE PASSWORD PD910 *ALL ' || SJSCHRPTNM || ' ' || sjschver || CASE  WHEN sjjobque IS NULL THEN 'QBATCH' WHEN sjjobque = ' ' THEN 'QBATCH' ELSE sjjobque END  || ' Batch Hold Save' 
  from sy910.f91300 where sjschjbstat = '01'
  order by sjschsttime asc;

   select 'runube JDE PASSWORD PD910 *ALL ' || SJSCHRPTNM || ' ' || sjschver || CASE  WHEN sjjobque IS NULL THEN N'QBATCH' WHEN sjjobque = ' ' THEN N'QBATCH' ELSE sjjobque END  || ' Batch Hold Save' 
  from sy910.f91300 where sjschjbstat = '01'
  order by sjschsttime asc;

runube JDE PASSWORD PD910 *ALL R5503005   AALL001S QBATCH Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503006   AALL001S QCAR1      Batch Hold Save
runube JDE PASSWORD PD910 *ALL R04601     AALL00S   QBATCH Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B13S AALL001S QBATCH Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503DTI5 AALL001S QBATCH Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503DTI4 AALL001S QBATCH Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B500X AALL005   QSTAT1     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413I NNSW001S QREPORT    Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413I SMNG001S QBATCH     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413I VLDR001S QBATCH     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413I VGAD001S QBATCH     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413I TDBL001S QBATCH     Batch Hold Save
runube JDE PASSWORD PD910 *ALL R5503B413I QQLD001S QBATCH     Batch Hold Save

Paste this into a windows command line, and you are away!

Monday, 13 April 2015

RTE in JDE and activating multiple subscribers

I have a situation where I’ve created my own RTE definition in JDE and I’m sending messages out of JDE when a certain event occurs. I had to make a custom RTE, so much of my code is bespoke (and the trigger points). Anyway, it’s all working like a treat.

I’m using JMSQUEUE as my transport type and putting in my RTE server as the recipient for all of the messages.

As you can see from the above, all pretty simple.

jms/com/peoplesoft/pt/e1/server/enterprise/events/ESBQueueConnectionFactory

in case you don’t want to type the above again.

This matches what you see in hermes JMS

Note that this subscriber can have multiple event subscriptions. This is made up of a group of environments and also subscribed events. Therefore you could active certain events in certain environments for the single subscriber. Nice

In my case (above) I have two event subscribers, both almost the same except for the queue that the events are going into. One is for the integration guys to grab the messages and process them. The other one is like a “tee” (you’ll know this if you are a unix nerd), which allows you to tee or split the output into another queue. this other queue does not have external subscribers popping the messages off. Therefore I get to look at the messages and work on them. I decided to put them into SubQueue00.

When you are finished debugging, just deactivate the subscriber, and you are away!

jms/com/peoplesoft/pt/e1/server/enterprise/events/SubQueue00

Ever wondered what the contents of the message is going to look like? Well here you go. this is a composite custom event.

<?xml version="1.0" encoding="UTF-8"?>
<jdeResponse xmlns="http://www.schemas.e1.oracle.com" category="RTE" environment="DV900" pwd="" responseCreator="XAPI" role="*ALL" session="3735290" token="" type="realTimeEvent" user="JDE">
   <event>
      <header>
         <eventVersion>1.0</eventVersion>
         <type>TNXSESOUT</type>
         <user>JDE</user>
         <role>*ALL</role>
         <application>P55PUTRT</application>
         <version>ZJDE0001</version>
         <sessionID>3735290</sessionID>
         <environment>DV900</environment>
         <host>TNXV-420EFB6B</host>
         <sequenceID>120</sequenceID>
         <date>04132015</date>
         <time>220151</time>
         <scope />
         <codepage>1252</codepage>
         <instanceInfo>
            <host>TNXV-420EFB6B</host>
            <port>6015</port>
            <type>JDENET</type>
         </instanceInfo>
      </header>
      <body elementCount="2">
         <detail_D553103A DSTMPL="D553103A" date="04132015" executionOrder="0" name="tenix_sendRTED553103A" parameterCount="23" time="22:01:51" type="TNXSESHDR">
            <szProgramId>R553104</szProgramId>
            <szStatusCodeNextRelease />
            <szDateAndTimeStamp />
            <szStatusofTransaction>01</szStatusofTransaction>
            <szStatusCodeLastRelease />
            <szKeyID>2015-04-09T15:42:00+10:00.AN0000000000028</szKeyID>
            <szWorkStationId>SVCNPKJDB0</szWorkStationId>
            <mnXPITransactionID>7000000018</mnXPITransactionID>
            <szInterchangeSenderID>AN01017179323-T</szInterchangeSenderID>
            <szInterchangeReceiverID>AN01014368918-T</szInterchangeReceiverID>
            <mnTimeLastUpdated>173911</mnTimeLastUpdated>
            <szDateCreated>2015-04-09T15:42:00+10:00</szDateCreated>
            <szEdiBatchNumber>AN0000000000028</szEdiBatchNumber>
            <szMessageString1 />
            <mnAmountTaxable>354.07</mnAmountTaxable>
            <szCommunicationType>false</szCommunicationType>
            <szUserId>VOTERSOJ</szUserId>
            <jdDateUpdated>2015/04/07</jdDateUpdated>
            <szExpirationDate />
            <szCurrencyCodeFrom>AUD</szCurrencyCodeFrom>
            <szKeyInfomation>005056AE1D891EE4B4A78A6A7E890185</szKeyInfomation>
            <szDateLotReceipt />
            <mnLINE_NUMBER_PARENT>10</mnLINE_NUMBER_PARENT>
         </detail_D553103A>
         <detail_D553104B DSTMPL="D553104B" date="04132015" executionOrder="1" name="tenix_sendRTED553103A" parameterCount="22" time="22:01:51" type="TNXSESDTL">
            <szProgramId>R553104</szProgramId>
            <mnUnitsTransactionQty>1.000</mnUnitsTransactionQty>
            <szStatusofTransaction>01</szStatusofTransaction>
            <szUnitOfMeasureAsInput>EA</szUnitOfMeasureAsInput>
            <szWorkStationId>SVCNPKJDB0</szWorkStationId>
            <mnAmtPricePerUnit2>354.0700</mnAmtPricePerUnit2>
            <mnTimeLastUpdated>173911</mnTimeLastUpdated>
            <mnDocumentOrderInvoiceE>951771</mnDocumentOrderInvoiceE>
            <szORIGINAL_UNIT_OF_MEASURE>C62</szORIGINAL_UNIT_OF_MEASURE>
            <szEdiBatchNumber>AN0000000000028</szEdiBatchNumber>
            <mnAmountTaxable>354.07</mnAmountTaxable>
            <szTaxExplanationCode1>V</szTaxExplanationCode1>
            <szTaxArea1>GS</szTaxArea1>
            <mnCriticalRatioPriority1>20.00</mnCriticalRatioPriority1>
            <szUserId>VOTERSOJ</szUserId>
            <szDescription>Distribution Line Fault</szDescription>
            <jdDateUpdated>2015/04/07</jdDateUpdated>
            <szIdentifier2ndItem>314-2</szIdentifier2ndItem>
            <szCurrencyCodeFrom>AUD</szCurrencyCodeFrom>
            <cJobStatus />
            <szReference1>000110003797</szReference1>
            <mnLine>1000100010</mnLine>
         </detail_D553104B>
      </body>
   </event>
</jdeResponse>

skinning e1 in 9.1.5.2

Quick changes are needed.
I wanted to change the logo at the top left and also the greeting. Both of these were very easy:
I employed a couple of resources to complete these:

Google chrome development tools
sftp and editing some core files (css and

/u01/app/oracle/Oracle/Middleware/user_projects/domains/mydomain/servers/JDE91DV/stage/JDE91DV/app/webclient.war/share/css/webclient.css
For the new icon, I just changed the one that oracle shipped, called oracle_logo_sm.jpg in the share/images/alta dir)

img#oracleImage, img#loginBrandingLogo {
    margin-top:13px; /* container is 42 px tall, object is 30px tall, (42 - 16) / 2 equals 13 */
    height:40px;
    width:120px;
    margin-left:10px;
    padding-left:4px;
    padding-right:4px;
}

For the words edit share/loginDecoration.jspf

        }
        sb.append(" <span id=\"appName\" class=\"appname\">DEMO JD Edwards provided by Myriad IT Services</span>");
        sb.append("</td>");
        sb.append("</tr></table>");
        //sb.append("</div>\n");
        sb.append("</td>\n");

}

If you’ve made the changes right, they’ll appear immediately

Now the top bar, different colour for different environment

.AFBrandingBar {
  background-color: #202020;
  color: #f2f2f2;

div#menuAndFastPathContainer {
  height: 40px;
  background-color: #202020;
}

See how the bar across the top is nice and dark now!

Then finally, our logo was a bit squished, so increase the dead space height, from 43 to 53

div#topnav, .topnav
{
    position:relative;
    height:53px;
    border-bottom: solid 1px #333333;
    box-shadow: rgba(0,0,0,0.4) 0px 5px 5px;
}

Thursday, 9 April 2015

BEA-000449 idle socket timeout

If you want to change this, there is a lot of BS on the internet, something like – change the startup parameters and add this…

XX:MaxPermSize=256m -Dweblogic.wsee.wstx.wsat.deployed=false -Dweblogic.client.socket.ConnectTimeout=30000 -Xms1536m -Xmx1536m

It does not work,

You need to go to your server, tuning and set the Login Timeout setting. Defalt is 5000 (ms), which is 5 seconds. This is 12.1.2.0. Make it bigger, like mine is 50 seconds now. I’m doing this as I’m having lots of issues with a AWS ELB.

####<Apr 9, 2015 10:57:48 AM EST> <Warning> <Socket> <jdewebprod03> <JDEPD3_8080> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1428541068826> <BEA-000449> <Closing the socket, as no data read from it on 172.31.169.170:45,489 during the configured idle timeout of 50 seconds.>
####<Apr 9, 2015 10:57:48 AM EST> <Warning> <Socket> <jdewebprod03> <JDEPD3_8080> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1428541068827> <BEA-000449> <Closing the socket, as no data read from it on 172.31.169.170:45,492 during the configured idle timeout of 50 seconds.>
####<Apr 9, 2015 10:57:48 AM EST> <Warning> <Socket> <jdewebprod03> <JDEPD3_8080> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1428541068828> <BEA-000449> <Closing the socket, as no data read from it on 172.31.169.170:45,488 during the configured idle timeout of 50 seconds.>
####<Apr 9, 2015 10:57:48 AM EST> <Warning> <Socket> <jdewebprod03> <JDEPD3_8080> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1428541068829> <BEA-000449> <Closing the socket, as no data read from it on 172.31.169.170:45,490 during the configured idle timeout of 50 seconds.>
####<Apr 9, 2015 10:58:08 AM EST> <Warning> <Socket> <jdewebprod03> <JDEPD3_8080> <[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1428541088821> <BEA-000449> <Closing the socket, as no data read from it on 172.31.137.115:24,853 during the configured idle timeout of 50 seconds.>

Tuesday, 7 April 2015

disable auto suggest, it’s killing my database

I go into the An8 field and start typing an AN8 and my application starts to query ALKY field? over F0116 and F0101

What is going on there, this is totally NOT what I want and causes the database to do some crazy query.

The screen above shows the view and the data item that is being used.

So, goto E1 menus, E1 life cycle tools –> System Admin Tools –> runtime feature admin (P958973)

Then disable it for the environment that you are dealing with.

Bad security design slowing you down

Here is an awesome example of some really poor security design and how this is translated at the database. This is the extract of the public and user records from the security workbench application.

User / Role    Object Name    Object Description    From Value    Thru Value    Security Type    Description    Data Item    Alias    View    Add    Change    Delete    Product Code    BU Security
*PUBLIC    *ALL                        1    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           133999999       133999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           141999999       141999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           146999999       146999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           147999999       147999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           168999999       168999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           173999999       173999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           178999999       178999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           222999999       222999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           227999999       227999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           244999999       244999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           246999999       246999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           252999999       252999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           253999999       253999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           254999999       254999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           255999999       255999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           257999999       257999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           268999999       268999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           275999999       275999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           291999999       291999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           298999999       298999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           314999999       314999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           318999999       318999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           327999999       327999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           352999999       352999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           710999999       710999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           756999999       756999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           758999999       758999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           800999999       800999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
*PUBLIC    *ALL           NEWSMODEL       NEWSMODEL    4    Row Security    CostCenter    MCU    Y    N    N    N

And

Individual – holy moly!!!

User / Role    Object Name    Object Description    From Value    Thru Value    Security Type    Description    Data Item    Alias    View    Add    Change    Delete    Product Code    BU Security
NSGTRAIN1    *ALL                        5    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                   9               9    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 108             124    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 132             155    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 161             165    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 167             171    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 173             178    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 202             202    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 205             205    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 215             215    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 218             218    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 222             222    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 226             228    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 234             234    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 240             240    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 243             247    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 249             249    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 250             257    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 261             266    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 268             269    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 271             271    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 273             291    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 294             294    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 298             314    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 317             319    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 327             328    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 336             336    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 345             345    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 348             349    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 352             352    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 354             357    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 364             364    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 373             373    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 393             393    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 395             395    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 397             398    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 600             629    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 710             758    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 800             800    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 840             840    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 900             900    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 902             902    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 908             908    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 910             911    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 950             950    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 960             963    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 966             969    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 972             975    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 983             983    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 987             988    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 990             990    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL                 995             995    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL             8000000         8009999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            ABM00000        ABMZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            ANL00000        ANLZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            CCN00000        CCNZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            CNS00000        CNSZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            COR00000        CORZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            DBL00000        DBLZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            GAA00000        GZZZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            HWT00000        HWTZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            LDR00000        LDRZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            MNG00000        MNGZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            MRC00000        MRCZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NDM00000        NDMZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NHO00000        NHOZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NLM00000        NLMZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NQN00000        NQNZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NSA00000        NSAZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NSL00000        NSLZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NSS00000        NSSZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NSW00000        NSWZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NTN00000        NTNZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            NWN00000        NWNZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            PPT00000        PPTZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            PST00000        PSTZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            QLD00000        QLDZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            QNP00000        QNPZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            QST00000        QSTZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            VIC00000        VICZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL            WES00000        WESZZZZZ    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           108000000       124999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           133000000       155999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           168000000       168999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           173000000       178999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           222000000       222999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           227000000       227999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           243000000       244999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           246000000       246999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           250000000       253999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           254000000       254999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           255000000       257999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           268000000       268999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           273000000       273999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           275000000       291999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           298000000       314999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           318999999       318999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           327000000       327999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           352000000       352999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           364000000       364999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           710000000       758999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           800999999       800999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           950000000       950999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           955999999       955999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           963999999       963999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           983000000       983999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           987000000       987999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           988999999       988999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           990000000       990999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           995000000       995999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL           NEWSMODEL       NEWSMODEL    4    Row Security    CostCenter    MCU    Y    N    N    N
NSGTRAIN1    *ALL          1320000000      1329999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          1510000000      1519999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          1610000000      1659999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          1670000000      1679999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          1690000000      1719999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2020000000      2029999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2050000000      2059999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2150000000      2159999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2180000000      2189999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2220000000      2229999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2260000000      2269999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2280000000      2289999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2340000000      2349999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2400000000      2409999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2450000000      2459999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2470000000      2479999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2490000000      2499999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2540000000      2549999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2610000000      2669999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2690000000      2699999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2710000000      2719999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2740000000      2749999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          2940000000      2949999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3000000000      3019999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3170000000      3199999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3280000000      3289999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3360000000      3369999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3450000000      3459999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3480000000      3499999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3540000000      3579999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3730000000      3739999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3930000000      3939999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3950000000      3959999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          3970000000      3989999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          6000000000      6299999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          8400000000      8409999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9020000000      9029999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9080000000      9089999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9100000000      9119999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9500000000      9509999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9600000000      9629999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9630000000      9639999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9660000000      9699999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9720000000      9759999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9830000000      9839999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y
NSGTRAIN1    *ALL          9870000000      9889999999    4    Row Security    CostCenter    MCU    Y    Y    Y    Y

Printing out the SQL details in EM12C shows:

-- ============================================================================================== --
-- This file is generated by EM Database Express.			                          --
-- The SQL bind variables, if any, will be declared below.                                        --
-- ============================================================================================== --


-- Note: If bind names were invalid SQL identifiers, then the bind variables may have been renamed below. --


declare
  430c87hnzs3xp_bind1     NVARCHAR2(32)  := '1234%';
  430c87hnzs3xp_bind2     NVARCHAR2(32)  := '1234%';
  430c87hnzs3xp_bind3     NVARCHAR2(32)  := 'NWN00000';
  430c87hnzs3xp_bind4     NVARCHAR2(32)  := 'NWNZZZZZ';
  430c87hnzs3xp_bind5     NVARCHAR2(32)  := 'PPT00000';
  430c87hnzs3xp_bind6     NVARCHAR2(32)  := 'PPTZZZZZ';
  430c87hnzs3xp_bind7     NVARCHAR2(32)  := 'CCN00000';
  430c87hnzs3xp_bind8     NVARCHAR2(32)  := 'CCNZZZZZ';
  430c87hnzs3xp_bind9     NVARCHAR2(32)  := 'CNS00000';
  430c87hnzs3xp_bind10    NVARCHAR2(32)  := 'CNSZZZZZ';
  430c87hnzs3xp_bind11    NVARCHAR2(32)  := 'COR00000';
  430c87hnzs3xp_bind12    NVARCHAR2(32)  := 'CORZZZZZ';
  430c87hnzs3xp_bind13    NVARCHAR2(32)  := 'DBL00000';
  430c87hnzs3xp_bind14    NVARCHAR2(32)  := 'DBLZZZZZ';
  430c87hnzs3xp_bind15    NVARCHAR2(32)  := 'GAA00000';
  430c87hnzs3xp_bind16    NVARCHAR2(32)  := 'GZZZZZZZ';
  430c87hnzs3xp_bind17    NVARCHAR2(32)  := 'HWT00000';
  430c87hnzs3xp_bind18    NVARCHAR2(32)  := 'HWTZZZZZ';
  430c87hnzs3xp_bind19    NVARCHAR2(32)  := 'LDR00000';
  430c87hnzs3xp_bind20    NVARCHAR2(32)  := 'LDRZZZZZ';
  430c87hnzs3xp_bind21    NVARCHAR2(32)  := 'MNG00000';
  430c87hnzs3xp_bind22    NVARCHAR2(32)  := 'MNGZZZZZ';
  430c87hnzs3xp_bind23    NVARCHAR2(32)  := '249';
  430c87hnzs3xp_bind24    NVARCHAR2(32)  := '250';
  430c87hnzs3xp_bind25    NVARCHAR2(32)  := '257';
  430c87hnzs3xp_bind26    NVARCHAR2(32)  := '261';
  430c87hnzs3xp_bind27    NVARCHAR2(32)  := '266';
  430c87hnzs3xp_bind28    NVARCHAR2(32)  := '268';
  430c87hnzs3xp_bind29    NVARCHAR2(32)  := '269';
  430c87hnzs3xp_bind30    NVARCHAR2(32)  := '271';
  430c87hnzs3xp_bind31    NVARCHAR2(32)  := '273';
  430c87hnzs3xp_bind32    NVARCHAR2(32)  := '291';
  430c87hnzs3xp_bind33    NVARCHAR2(32)  := '294';
  430c87hnzs3xp_bind34    NVARCHAR2(32)  := NULL;
  430c87hnzs3xp_bind35    NVARCHAR2(32)  := '5';
  430c87hnzs3xp_bind36    NVARCHAR2(32)  := '9';
  430c87hnzs3xp_bind37    NVARCHAR2(32)  := '108';
  430c87hnzs3xp_bind38    NVARCHAR2(32)  := '124';
  430c87hnzs3xp_bind39    NVARCHAR2(32)  := '168000000';
  430c87hnzs3xp_bind40    NVARCHAR2(32)  := '168999999';
  430c87hnzs3xp_bind41    NVARCHAR2(32)  := '173000000';
  430c87hnzs3xp_bind42    NVARCHAR2(32)  := '178999999';
  430c87hnzs3xp_bind43    NVARCHAR2(32)  := '298';
  430c87hnzs3xp_bind44    NVARCHAR2(32)  := '314';
  430c87hnzs3xp_bind45    NVARCHAR2(32)  := '317';
  430c87hnzs3xp_bind46    NVARCHAR2(32)  := '319';
  430c87hnzs3xp_bind47    NVARCHAR2(32)  := '327';
  430c87hnzs3xp_bind48    NVARCHAR2(32)  := '328';
  430c87hnzs3xp_bind49    NVARCHAR2(32)  := '132';
  430c87hnzs3xp_bind50    NVARCHAR2(32)  := '155';
  430c87hnzs3xp_bind51    NVARCHAR2(32)  := '161';
  430c87hnzs3xp_bind52    NVARCHAR2(32)  := '165';
  430c87hnzs3xp_bind53    NVARCHAR2(32)  := '167';
  430c87hnzs3xp_bind54    NVARCHAR2(32)  := '171';
  430c87hnzs3xp_bind55    NVARCHAR2(32)  := '222000000';
  430c87hnzs3xp_bind56    NVARCHAR2(32)  := '222999999';
  430c87hnzs3xp_bind57    NVARCHAR2(32)  := '227000000';
  430c87hnzs3xp_bind58    NVARCHAR2(32)  := '227999999';
  430c87hnzs3xp_bind59    NVARCHAR2(32)  := '243000000';
  430c87hnzs3xp_bind60    NVARCHAR2(32)  := '244999999';
  430c87hnzs3xp_bind61    NVARCHAR2(32)  := '246000000';
  430c87hnzs3xp_bind62    NVARCHAR2(32)  := '246999999';
  430c87hnzs3xp_bind63    NVARCHAR2(32)  := '250000000';
  430c87hnzs3xp_bind64    NVARCHAR2(32)  := '253999999';
  430c87hnzs3xp_bind65    NVARCHAR2(32)  := '254000000';
  430c87hnzs3xp_bind66    NVARCHAR2(32)  := '254999999';
  430c87hnzs3xp_bind67    NVARCHAR2(32)  := '255000000';
  430c87hnzs3xp_bind68    NVARCHAR2(32)  := '257999999';
  430c87hnzs3xp_bind69    NVARCHAR2(32)  := '268000000';
  430c87hnzs3xp_bind70    NVARCHAR2(32)  := '268999999';
  430c87hnzs3xp_bind71    NVARCHAR2(32)  := '336';
  430c87hnzs3xp_bind72    NVARCHAR2(32)  := '345';
  430c87hnzs3xp_bind73    NVARCHAR2(32)  := '348';
  430c87hnzs3xp_bind74    NVARCHAR2(32)  := '349';
  430c87hnzs3xp_bind75    NVARCHAR2(32)  := '352';
  430c87hnzs3xp_bind76    NVARCHAR2(32)  := '354';
  430c87hnzs3xp_bind77    NVARCHAR2(32)  := '357';
  430c87hnzs3xp_bind78    NVARCHAR2(32)  := '364';
  430c87hnzs3xp_bind79    NVARCHAR2(32)  := '373';
  430c87hnzs3xp_bind80    NVARCHAR2(32)  := '173';
  430c87hnzs3xp_bind81    NVARCHAR2(32)  := '178';
  430c87hnzs3xp_bind82    NVARCHAR2(32)  := '202';
  430c87hnzs3xp_bind83    NVARCHAR2(32)  := '205';
  430c87hnzs3xp_bind84    NVARCHAR2(32)  := '215';
  430c87hnzs3xp_bind85    NVARCHAR2(32)  := '218';
  430c87hnzs3xp_bind86    NVARCHAR2(32)  := '222';
  430c87hnzs3xp_bind87    NVARCHAR2(32)  := '226';
  430c87hnzs3xp_bind88    NVARCHAR2(32)  := '228';
  430c87hnzs3xp_bind89    NVARCHAR2(32)  := '273000000';
  430c87hnzs3xp_bind90    NVARCHAR2(32)  := '273999999';
  430c87hnzs3xp_bind91    NVARCHAR2(32)  := '275000000';
  430c87hnzs3xp_bind92    NVARCHAR2(32)  := '291999999';
  430c87hnzs3xp_bind93    NVARCHAR2(32)  := '393';
  430c87hnzs3xp_bind94    NVARCHAR2(32)  := '395';
  430c87hnzs3xp_bind95    NVARCHAR2(32)  := '397';
  430c87hnzs3xp_bind96    NVARCHAR2(32)  := '398';
  430c87hnzs3xp_bind97    NVARCHAR2(32)  := '234';
  430c87hnzs3xp_bind98    NVARCHAR2(32)  := '240';
  430c87hnzs3xp_bind99    NVARCHAR2(32)  := '243';
  430c87hnzs3xp_bind100   NVARCHAR2(32)  := '247';
  430c87hnzs3xp_bind101   NVARCHAR2(32)  := '298000000';
  430c87hnzs3xp_bind102   NVARCHAR2(32)  := '314999999';
  430c87hnzs3xp_bind103   NVARCHAR2(32)  := '318999999';
  430c87hnzs3xp_bind104   NVARCHAR2(32)  := '327000000';
  430c87hnzs3xp_bind105   NVARCHAR2(32)  := '327999999';
  430c87hnzs3xp_bind106   NVARCHAR2(32)  := '352000000';
  430c87hnzs3xp_bind107   NVARCHAR2(32)  := '352999999';
  430c87hnzs3xp_bind108   NVARCHAR2(32)  := '364000000';
  430c87hnzs3xp_bind109   NVARCHAR2(32)  := '364999999';
  430c87hnzs3xp_bind110   NVARCHAR2(32)  := '710000000';
  430c87hnzs3xp_bind111   NVARCHAR2(32)  := '758999999';
  430c87hnzs3xp_bind112   NVARCHAR2(32)  := '800999999';
  430c87hnzs3xp_bind113   NVARCHAR2(32)  := '950000000';
  430c87hnzs3xp_bind114   NVARCHAR2(32)  := '950999999';
  430c87hnzs3xp_bind115   NVARCHAR2(32)  := '600';
  430c87hnzs3xp_bind116   NVARCHAR2(32)  := '629';
  430c87hnzs3xp_bind117   NVARCHAR2(32)  := '710';
  430c87hnzs3xp_bind118   NVARCHAR2(32)  := '758';
  430c87hnzs3xp_bind119   NVARCHAR2(32)  := '800';
  430c87hnzs3xp_bind120   NVARCHAR2(32)  := '840';
  430c87hnzs3xp_bind121   NVARCHAR2(32)  := '900';
  430c87hnzs3xp_bind122   NVARCHAR2(32)  := '902';
  430c87hnzs3xp_bind123   NVARCHAR2(32)  := '908';
  430c87hnzs3xp_bind124   NVARCHAR2(32)  := 'PST00000';
  430c87hnzs3xp_bind125   NVARCHAR2(32)  := 'PSTZZZZZ';
  430c87hnzs3xp_bind126   NVARCHAR2(32)  := 'QLD00000';
  430c87hnzs3xp_bind127   NVARCHAR2(32)  := 'QLDZZZZZ';
  430c87hnzs3xp_bind128   NVARCHAR2(32)  := 'QNP00000';
  430c87hnzs3xp_bind129   NVARCHAR2(32)  := 'QNPZZZZZ';
  430c87hnzs3xp_bind130   NVARCHAR2(32)  := 'QST00000';
  430c87hnzs3xp_bind131   NVARCHAR2(32)  := 'QSTZZZZZ';
  430c87hnzs3xp_bind132   NVARCHAR2(32)  := 'VIC00000';
  430c87hnzs3xp_bind133   NVARCHAR2(32)  := 'VICZZZZZ';
  430c87hnzs3xp_bind134   NVARCHAR2(32)  := 'WES00000';
  430c87hnzs3xp_bind135   NVARCHAR2(32)  := 'WESZZZZZ';
  430c87hnzs3xp_bind136   NVARCHAR2(32)  := '108000000';
  430c87hnzs3xp_bind137   NVARCHAR2(32)  := '124999999';
  430c87hnzs3xp_bind138   NVARCHAR2(32)  := '133000000';
  430c87hnzs3xp_bind139   NVARCHAR2(32)  := '155999999';
  430c87hnzs3xp_bind140   NVARCHAR2(32)  := '910';
  430c87hnzs3xp_bind141   NVARCHAR2(32)  := '911';
  430c87hnzs3xp_bind142   NVARCHAR2(32)  := '950';
  430c87hnzs3xp_bind143   NVARCHAR2(32)  := '960';
  430c87hnzs3xp_bind144   NVARCHAR2(32)  := '963';
  430c87hnzs3xp_bind145   NVARCHAR2(32)  := '966';
  430c87hnzs3xp_bind146   NVARCHAR2(32)  := '969';
  430c87hnzs3xp_bind147   NVARCHAR2(32)  := '972';
  430c87hnzs3xp_bind148   NVARCHAR2(32)  := '975';
  430c87hnzs3xp_bind149   NVARCHAR2(32)  := '983';
  430c87hnzs3xp_bind150   NVARCHAR2(32)  := '987';
  430c87hnzs3xp_bind151   NVARCHAR2(32)  := '988';
  430c87hnzs3xp_bind152   NVARCHAR2(32)  := '990';
  430c87hnzs3xp_bind153   NVARCHAR2(32)  := '995';
  430c87hnzs3xp_bind154   NVARCHAR2(32)  := '8000000';
  430c87hnzs3xp_bind155   NVARCHAR2(32)  := '8009999';
  430c87hnzs3xp_bind156   NVARCHAR2(32)  := 'MRC00000';
  430c87hnzs3xp_bind157   NVARCHAR2(32)  := 'MRCZZZZZ';
  430c87hnzs3xp_bind158   NVARCHAR2(32)  := 'NDM00000';
  430c87hnzs3xp_bind159   NVARCHAR2(32)  := 'NDMZZZZZ';
  430c87hnzs3xp_bind160   NVARCHAR2(32)  := 'NHO00000';
  430c87hnzs3xp_bind161   NVARCHAR2(32)  := 'NHOZZZZZ';
  430c87hnzs3xp_bind162   NVARCHAR2(32)  := 'NLM00000';
  430c87hnzs3xp_bind163   NVARCHAR2(32)  := 'NLMZZZZZ';
  430c87hnzs3xp_bind164   NVARCHAR2(32)  := 'NQN00000';
  430c87hnzs3xp_bind165   NVARCHAR2(32)  := 'NQNZZZZZ';
  430c87hnzs3xp_bind166   NVARCHAR2(32)  := 'NSA00000';
  430c87hnzs3xp_bind167   NVARCHAR2(32)  := 'NSAZZZZZ';
  430c87hnzs3xp_bind168   NVARCHAR2(32)  := 'NSL00000';
  430c87hnzs3xp_bind169   NVARCHAR2(32)  := 'NSLZZZZZ';
  430c87hnzs3xp_bind170   NVARCHAR2(32)  := 'NSS00000';
  430c87hnzs3xp_bind171   NVARCHAR2(32)  := 'NSSZZZZZ';
  430c87hnzs3xp_bind172   NVARCHAR2(32)  := 'NSW00000';
  430c87hnzs3xp_bind173   NVARCHAR2(32)  := 'NSWZZZZZ';
  430c87hnzs3xp_bind174   NVARCHAR2(32)  := 'NTN00000';
  430c87hnzs3xp_bind175   NVARCHAR2(32)  := 'NTNZZZZZ';
  430c87hnzs3xp_bind176   NVARCHAR2(32)  := 'ABM00000';
  430c87hnzs3xp_bind177   NVARCHAR2(32)  := 'ABMZZZZZ';
  430c87hnzs3xp_bind178   NVARCHAR2(32)  := 'ANL00000';
  430c87hnzs3xp_bind179   NVARCHAR2(32)  := 'ANLZZZZZ';
  430c87hnzs3xp_bind180   NVARCHAR2(32)  := '955999999';
  430c87hnzs3xp_bind181   NVARCHAR2(32)  := '963999999';
  430c87hnzs3xp_bind182   NVARCHAR2(32)  := '983000000';
  430c87hnzs3xp_bind183   NVARCHAR2(32)  := '983999999';
  430c87hnzs3xp_bind184   NVARCHAR2(32)  := '987000000';
  430c87hnzs3xp_bind185   NVARCHAR2(32)  := '987999999';
  430c87hnzs3xp_bind186   NVARCHAR2(32)  := '988999999';
  430c87hnzs3xp_bind187   NVARCHAR2(32)  := '990000000';
  430c87hnzs3xp_bind188   NVARCHAR2(32)  := '990999999';
  430c87hnzs3xp_bind189   NVARCHAR2(32)  := '995000000';
  430c87hnzs3xp_bind190   NVARCHAR2(32)  := '995999999';
  430c87hnzs3xp_bind191   NVARCHAR2(32)  := 'NEWSMODEL';
  430c87hnzs3xp_bind192   NVARCHAR2(32)  := '1320000000';
  430c87hnzs3xp_bind193   NVARCHAR2(32)  := '1329999999';
  430c87hnzs3xp_bind194   NVARCHAR2(32)  := '1510000000';
  430c87hnzs3xp_bind195   NVARCHAR2(32)  := '1519999999';
  430c87hnzs3xp_bind196   NVARCHAR2(32)  := '9660000000';
  430c87hnzs3xp_bind197   NVARCHAR2(32)  := '9699999999';
  430c87hnzs3xp_bind198   NVARCHAR2(32)  := '9720000000';
  430c87hnzs3xp_bind199   NVARCHAR2(32)  := '9759999999';
  430c87hnzs3xp_bind200   NVARCHAR2(32)  := '9630000000';
  430c87hnzs3xp_bind201   NVARCHAR2(32)  := '9639999999';
  430c87hnzs3xp_bind202   NVARCHAR2(32)  := '9830000000';
  430c87hnzs3xp_bind203   NVARCHAR2(32)  := '9839999999';
  430c87hnzs3xp_bind204   NVARCHAR2(32)  := '2940000000';
  430c87hnzs3xp_bind205   NVARCHAR2(32)  := '2949999999';
  430c87hnzs3xp_bind206   NVARCHAR2(32)  := '3000000000';
  430c87hnzs3xp_bind207   NVARCHAR2(32)  := '3019999999';
  430c87hnzs3xp_bind208   NVARCHAR2(32)  := '3170000000';
  430c87hnzs3xp_bind209   NVARCHAR2(32)  := '3199999999';
  430c87hnzs3xp_bind210   NVARCHAR2(32)  := '3280000000';
  430c87hnzs3xp_bind211   NVARCHAR2(32)  := '3289999999';
  430c87hnzs3xp_bind212   NVARCHAR2(32)  := '3360000000';
  430c87hnzs3xp_bind213   NVARCHAR2(32)  := '3369999999';
  430c87hnzs3xp_bind214   NVARCHAR2(32)  := '3450000000';
  430c87hnzs3xp_bind215   NVARCHAR2(32)  := '3459999999';
  430c87hnzs3xp_bind216   NVARCHAR2(32)  := '3480000000';
  430c87hnzs3xp_bind217   NVARCHAR2(32)  := '3499999999';
  430c87hnzs3xp_bind218   NVARCHAR2(32)  := '3540000000';
  430c87hnzs3xp_bind219   NVARCHAR2(32)  := '3579999999';
  430c87hnzs3xp_bind220   NVARCHAR2(32)  := '3730000000';
  430c87hnzs3xp_bind221   NVARCHAR2(32)  := '3739999999';
  430c87hnzs3xp_bind222   NVARCHAR2(32)  := '3930000000';
  430c87hnzs3xp_bind223   NVARCHAR2(32)  := '3939999999';
  430c87hnzs3xp_bind224   NVARCHAR2(32)  := '1610000000';
  430c87hnzs3xp_bind225   NVARCHAR2(32)  := '1659999999';
  430c87hnzs3xp_bind226   NVARCHAR2(32)  := '1670000000';
  430c87hnzs3xp_bind227   NVARCHAR2(32)  := '1679999999';
  430c87hnzs3xp_bind228   NVARCHAR2(32)  := '1690000000';
  430c87hnzs3xp_bind229   NVARCHAR2(32)  := '1719999999';
  430c87hnzs3xp_bind230   NVARCHAR2(32)  := '2020000000';
  430c87hnzs3xp_bind231   NVARCHAR2(32)  := '2029999999';
  430c87hnzs3xp_bind232   NVARCHAR2(32)  := '2050000000';
  430c87hnzs3xp_bind233   NVARCHAR2(32)  := '2059999999';
  430c87hnzs3xp_bind234   NVARCHAR2(32)  := '2150000000';
  430c87hnzs3xp_bind235   NVARCHAR2(32)  := '2159999999';
  430c87hnzs3xp_bind236   NVARCHAR2(32)  := '2180000000';
  430c87hnzs3xp_bind237   NVARCHAR2(32)  := '2189999999';
  430c87hnzs3xp_bind238   NVARCHAR2(32)  := '2220000000';
  430c87hnzs3xp_bind239   NVARCHAR2(32)  := '2229999999';
  430c87hnzs3xp_bind240   NVARCHAR2(32)  := '2260000000';
  430c87hnzs3xp_bind241   NVARCHAR2(32)  := '2269999999';
  430c87hnzs3xp_bind242   NVARCHAR2(32)  := '2280000000';
  430c87hnzs3xp_bind243   NVARCHAR2(32)  := '2289999999';
  430c87hnzs3xp_bind244   NVARCHAR2(32)  := '3950000000';
  430c87hnzs3xp_bind245   NVARCHAR2(32)  := '3959999999';
  430c87hnzs3xp_bind246   NVARCHAR2(32)  := '3970000000';
  430c87hnzs3xp_bind247   NVARCHAR2(32)  := '3989999999';
  430c87hnzs3xp_bind248   NVARCHAR2(32)  := '6000000000';
  430c87hnzs3xp_bind249   NVARCHAR2(32)  := '6299999999';
  430c87hnzs3xp_bind250   NVARCHAR2(32)  := '8400000000';
  430c87hnzs3xp_bind251   NVARCHAR2(32)  := '8409999999';
  430c87hnzs3xp_bind252   NVARCHAR2(32)  := '9020000000';
  430c87hnzs3xp_bind253   NVARCHAR2(32)  := '9029999999';
  430c87hnzs3xp_bind254   NVARCHAR2(32)  := '9080000000';
  430c87hnzs3xp_bind255   NVARCHAR2(32)  := '9089999999';
  430c87hnzs3xp_bind256   NVARCHAR2(32)  := '9100000000';
  430c87hnzs3xp_bind257   NVARCHAR2(32)  := '9119999999';
  430c87hnzs3xp_bind258   NVARCHAR2(32)  := '9500000000';
  430c87hnzs3xp_bind259   NVARCHAR2(32)  := '9509999999';
  430c87hnzs3xp_bind260   NVARCHAR2(32)  := '9600000000';
  430c87hnzs3xp_bind261   NVARCHAR2(32)  := '9629999999';
  430c87hnzs3xp_bind262   NVARCHAR2(32)  := '2340000000';
  430c87hnzs3xp_bind263   NVARCHAR2(32)  := '2349999999';
  430c87hnzs3xp_bind264   NVARCHAR2(32)  := '2400000000';
  430c87hnzs3xp_bind265   NVARCHAR2(32)  := '2409999999';
  430c87hnzs3xp_bind266   NVARCHAR2(32)  := '2450000000';
  430c87hnzs3xp_bind267   NVARCHAR2(32)  := '2459999999';
  430c87hnzs3xp_bind268   NVARCHAR2(32)  := '2470000000';
  430c87hnzs3xp_bind269   NVARCHAR2(32)  := '2479999999';
  430c87hnzs3xp_bind270   NVARCHAR2(32)  := '2490000000';
  430c87hnzs3xp_bind271   NVARCHAR2(32)  := '2499999999';
  430c87hnzs3xp_bind272   NVARCHAR2(32)  := '2540000000';
  430c87hnzs3xp_bind273   NVARCHAR2(32)  := '2549999999';
  430c87hnzs3xp_bind274   NVARCHAR2(32)  := '2610000000';
  430c87hnzs3xp_bind275   NVARCHAR2(32)  := '2669999999';
  430c87hnzs3xp_bind276   NVARCHAR2(32)  := '2690000000';
  430c87hnzs3xp_bind277   NVARCHAR2(32)  := '2699999999';
  430c87hnzs3xp_bind278   NVARCHAR2(32)  := '2710000000';
  430c87hnzs3xp_bind279   NVARCHAR2(32)  := '2719999999';
  430c87hnzs3xp_bind280   NVARCHAR2(32)  := '2740000000';
  430c87hnzs3xp_bind281   NVARCHAR2(32)  := '2749999999';
  430c87hnzs3xp_bind282   NVARCHAR2(32)  := '9870000000';
  430c87hnzs3xp_bind283   NVARCHAR2(32)  := '9889999999';
begin
--  SELECT T0.ABAN8,T0.ABALPH,T0.ABALKY,T0.ABAT1,T1.ALCTY1 FROM PRODDTA.F0101 T0, PRODDTA.F0116 T1  WHERE ((((T0.ABALPH LIKE :1   OR T0.ABALKY LIKE :2  )) AND (T0.ABMCU BETWEEN :3  AND  :4   OR T0.ABMCU BETWEEN :5  AND  :6   OR T0.ABMCU BETWEEN :7  AND  :8   OR T0.ABMCU BETWEEN :9  AND  :10   OR T0.ABMCU BETWEEN :11  AND  :12   OR T0.ABMCU BETWEEN :13  AND  :14   OR T0.ABMCU BETWEEN :15  AND  :16   OR T0.ABMCU BETWEEN :17  AND  :18   OR T0.ABMCU BETWEEN :19  AND  :20   OR T0.ABMCU BETWEEN :21  AND  :22   OR T0.ABMCU = :23   OR T0.ABMCU BETWEEN :24  AND  :25   OR T0.ABMCU BETWEEN :26  AND  :27   OR T0.ABMCU BETWEEN :28  AND  :29   OR T0.ABMCU = :30   OR T0.ABMCU BETWEEN :31  AND  :32   OR T0.ABMCU = :33   OR T0.ABMCU BETWEEN :34  AND  :35   OR T0.ABMCU = :36   OR T0.ABMCU BETWEEN :37  AND  :38   OR T0.ABMCU BETWEEN :39  AND  :40   OR T0.ABMCU BETWEEN :41  AND  :42   OR T0.ABMCU BETWEEN :43  AND  :44   OR T0.ABMCU BETWEEN :45  AND  :46   OR T0.ABMCU BETWEEN :47  AND  :48   OR T0.ABMCU BETWEEN :49  AND  :50   OR T0.ABMCU BETWEEN :51  AND  :52   OR T0.ABMCU BETWEEN :53  AND  :54   OR T0.ABMCU BETWEEN :55  AND  :56   OR T0.ABMCU BETWEEN :57  AND  :58   OR T0.ABMCU BETWEEN :59  AND  :60   OR T0.ABMCU BETWEEN :61  AND  :62   OR T0.ABMCU BETWEEN :63  AND  :64   OR T0.ABMCU BETWEEN :65  AND  :66   OR T0.ABMCU BETWEEN :67  AND  :68   OR T0.ABMCU BETWEEN :69  AND  :70   OR T0.ABMCU = :71   OR T0.ABMCU = :72   OR T0.ABMCU BETWEEN :73  AND  :74   OR T0.ABMCU = :75   OR T0.ABMCU BETWEEN :76  AND  :77   OR T0.ABMCU = :78   OR T0.ABMCU = :79   OR T0.ABMCU BETWEEN :80  AND  :81   OR T0.ABMCU = :82   OR T0.ABMCU = :83   OR T0.ABMCU = :84   OR T0.ABMCU = :85   OR T0.ABMCU = :86   OR T0.ABMCU BETWEEN :87  AND  :88   OR T0.ABMCU BETWEEN :89  AND  :90   OR T0.ABMCU BETWEEN :91  AND  :92   OR T0.ABMCU = :93   OR T0.ABMCU = :94   OR T0.ABMCU BETWEEN :95  AND  :96   OR T0.ABMCU = :97   OR T0.ABMCU = :98   OR T0.ABMCU BETWEEN :99  AND  :100   OR T0.ABMCU BETWEEN :101  AND  :102   OR T0.ABMCU = :103   OR T0.ABMCU BETWEEN :104  AND  :105   OR T0.ABMCU BETWEEN :106  AND  :107   OR T0.ABMCU BETWEEN :108  AND  :109   OR T0.ABMCU BETWEEN :110  AND  :111   OR T0.ABMCU = :112   OR T0.ABMCU BETWEEN :113  AND  :114   OR T0.ABMCU BETWEEN :115  AND  :116   OR T0.ABMCU BETWEEN :117  AND  :118   OR T0.ABMCU = :119   OR T0.ABMCU = :120   OR T0.ABMCU = :121   OR T0.ABMCU = :122   OR T0.ABMCU = :123   OR T0.ABMCU BETWEEN :124  AND  :125   OR T0.ABMCU BETWEEN :126  AND  :127   OR T0.ABMCU BETWEEN :128  AND  :129   OR T0.ABMCU BETWEEN :130  AND  :131   OR T0.ABMCU BETWEEN :132  AND  :133   OR T0.ABMCU BETWEEN :134  AND  :135   OR T0.ABMCU BETWEEN :136  AND  :137   OR T0.ABMCU BETWEEN :138  AND  :139   OR T0.ABMCU BETWEEN :140  AND  :141   OR T0.ABMCU = :142   OR T0.ABMCU BETWEEN :143  AND  :144   OR T0.ABMCU BETWEEN :145  AND  :146   OR T0.ABMCU BETWEEN :147  AND  :148   OR T0.ABMCU = :149   OR T0.ABMCU BETWEEN :150  AND  :151   OR T0.ABMCU = :152   OR T0.ABMCU = :153   OR T0.ABMCU BETWEEN :154  AND  :155   OR T0.ABMCU BETWEEN :156  AND  :157   OR T0.ABMCU BETWEEN :158  AND  :159   OR T0.ABMCU BETWEEN :160  AND  :161   OR T0.ABMCU BETWEEN :162  AND  :163   OR T0.ABMCU BETWEEN :164  AND  :165   OR T0.ABMCU BETWEEN :166  AND  :167   OR T0.ABMCU BETWEEN :168  AND  :169   OR T0.ABMCU BETWEEN :170  AND  :171   OR T0.ABMCU BETWEEN :172  AND  :173   OR T0.ABMCU BETWEEN :174  AND  :175   OR T0.ABMCU BETWEEN :176  AND  :177   OR T0.ABMCU BETWEEN :178  AND  :179   OR T0.ABMCU = :180   OR T0.ABMCU = :181   OR T0.ABMCU BETWEEN :182  AND  :183   OR T0.ABMCU BETWEEN :184  AND  :185   OR T0.ABMCU = :186   OR T0.ABMCU BETWEEN :187  AND  :188   OR T0.ABMCU BETWEEN :189  AND  :190   OR T0.ABMCU = :191   OR T0.ABMCU BETWEEN :192  AND  :193   OR T0.ABMCU BETWEEN :194  AND  :195   OR T0.ABMCU BETWEEN :196  AND  :197   OR T0.ABMCU BETWEEN :198  AND  :199   OR T0.ABMCU BETWEEN :200  AND  :201   OR T0.ABMCU BETWEEN :202  AND  :203   OR T0.ABMCU BETWEEN :204  AND  :205   OR T0.ABMCU BETWEEN :206  AND  :207   OR T0.ABMCU BETWEEN :208  AND  :209   OR T0.ABMCU BETWEEN :210  AND  :211   OR T0.ABMCU BETWEEN :212  AND  :213   OR T0.ABMCU BETWEEN :214  AND  :215   OR T0.ABMCU BETWEEN :216  AND  :217   OR T0.ABMCU BETWEEN :218  AND  :219   OR T0.ABMCU BETWEEN :220  AND  :221   OR T0.ABMCU BETWEEN :222  AND  :223   OR T0.ABMCU BETWEEN :224  AND  :225   OR T0.ABMCU BETWEEN :226  AND  :227   OR T0.ABMCU BETWEEN :228  AND  :229   OR T0.ABMCU BETWEEN :230  AND  :231   OR T0.ABMCU BETWEEN :232  AND  :233   OR T0.ABMCU BETWEEN :234  AND  :235   OR T0.ABMCU BETWEEN :236  AND  :237   OR T0.ABMCU BETWEEN :238  AND  :239   OR T0.ABMCU BETWEEN :240  AND  :241   OR T0.ABMCU BETWEEN :242  AND  :243   OR T0.ABMCU BETWEEN :244  AND  :245   OR T0.ABMCU BETWEEN :246  AND  :247   OR T0.ABMCU BETWEEN :248  AND  :249   OR T0.ABMCU BETWEEN :250  AND  :251   OR T0.ABMCU BETWEEN :252  AND  :253   OR T0.ABMCU BETWEEN :254  AND  :255   OR T0.ABMCU BETWEEN :256  AND  :257   OR T0.ABMCU BETWEEN :258  AND  :259   OR T0.ABMCU BETWEEN :260  AND  :261   OR T0.ABMCU BETWEEN :262  AND  :263   OR T0.ABMCU BETWEEN :264  AND  :265   OR T0.ABMCU BETWEEN :266  AND  :267   OR T0.ABMCU BETWEEN :268  AND  :269   OR T0.ABMCU BETWEEN :270  AND  :271   OR T0.ABMCU BETWEEN :272  AND  :273   OR T0.ABMCU BETWEEN :274  AND  :275   OR T0.ABMCU BETWEEN :276  AND  :277   OR T0.ABMCU BETWEEN :278  AND  :279   OR T0.ABMCU BETWEEN :280  AND  :281   OR T0.ABMCU BETWEEN :282  AND  :283  ))) AND (T0.ABAN8 = T1.ALAN8) ORDER BY T0.ABALPH ASC  , T0.ABALKY ASC
end;
/

SELECT  T0.ABAN8 ,
        T0.ABALPH,
        T0.ABALKY,
        T0.ABAT1 ,
        T1.ALCTY1
FROM    PRODDTA.F0101 T0,
        PRODDTA.F0116 T1
WHERE
        (
                (
                        (
                                (
                                        T0.ABALPH LIKE :1
                                        OR T0.ABALKY LIKE :2
                                )
                        )
                        AND
                        (
                                T0.ABMCU BETWEEN :3 AND :4
                                OR T0.ABMCU BETWEEN :5 AND :6
                                OR T0.ABMCU BETWEEN :7 AND :8
                                OR T0.ABMCU BETWEEN :9 AND :10
                                OR T0.ABMCU BETWEEN :11 AND :12
                                OR T0.ABMCU BETWEEN :13 AND :14
                                OR T0.ABMCU BETWEEN :15 AND :16
                                OR T0.ABMCU BETWEEN :17 AND :18
                                OR T0.ABMCU BETWEEN :19 AND :20
                                OR T0.ABMCU BETWEEN :21 AND :22
                                OR T0.ABMCU = :23
                                OR T0.ABMCU BETWEEN :24 AND :25
                                OR T0.ABMCU BETWEEN :26 AND :27
                                OR T0.ABMCU BETWEEN :28 AND :29
                                OR T0.ABMCU = :30
                                OR T0.ABMCU BETWEEN :31 AND :32
                                OR T0.ABMCU = :33
                                OR T0.ABMCU BETWEEN :34 AND :35
                                OR T0.ABMCU = :36
                                OR T0.ABMCU BETWEEN :37 AND :38
                                OR T0.ABMCU BETWEEN :39 AND :40
                                OR T0.ABMCU BETWEEN :41 AND :42
                                OR T0.ABMCU BETWEEN :43 AND :44
                                OR T0.ABMCU BETWEEN :45 AND :46
                                OR T0.ABMCU BETWEEN :47 AND :48
                                OR T0.ABMCU BETWEEN :49 AND :50
                                OR T0.ABMCU BETWEEN :51 AND :52
                                OR T0.ABMCU BETWEEN :53 AND :54
                                OR T0.ABMCU BETWEEN :55 AND :56
                                OR T0.ABMCU BETWEEN :57 AND :58
                                OR T0.ABMCU BETWEEN :59 AND :60
                                OR T0.ABMCU BETWEEN :61 AND :62
                                OR T0.ABMCU BETWEEN :63 AND :64
                                OR T0.ABMCU BETWEEN :65 AND :66
                                OR T0.ABMCU BETWEEN :67 AND :68
                                OR T0.ABMCU BETWEEN :69 AND :70
                                OR T0.ABMCU = :71
                                OR T0.ABMCU = :72
                                OR T0.ABMCU BETWEEN :73 AND :74
                                OR T0.ABMCU = :75
                                OR T0.ABMCU BETWEEN :76 AND :77
                                OR T0.ABMCU = :78
                                OR T0.ABMCU = :79
                                OR T0.ABMCU BETWEEN :80 AND :81
                                OR T0.ABMCU = :82
                                OR T0.ABMCU = :83
                                OR T0.ABMCU = :84
                                OR T0.ABMCU = :85
                                OR T0.ABMCU = :86
                                OR T0.ABMCU BETWEEN :87 AND :88
                                OR T0.ABMCU BETWEEN :89 AND :90
                                OR T0.ABMCU BETWEEN :91 AND :92
                                OR T0.ABMCU = :93
                                OR T0.ABMCU = :94
                                OR T0.ABMCU BETWEEN :95 AND :96
                                OR T0.ABMCU = :97
                                OR T0.ABMCU = :98
                                OR T0.ABMCU BETWEEN :99 AND :100
                                OR T0.ABMCU BETWEEN :101 AND :102
                                OR T0.ABMCU = :103
                                OR T0.ABMCU BETWEEN :104 AND :105
                                OR T0.ABMCU BETWEEN :106 AND :107
                                OR T0.ABMCU BETWEEN :108 AND :109
                                OR T0.ABMCU BETWEEN :110 AND :111
                                OR T0.ABMCU = :112
                                OR T0.ABMCU BETWEEN :113 AND :114
                                OR T0.ABMCU BETWEEN :115 AND :116
                                OR T0.ABMCU BETWEEN :117 AND :118
                                OR T0.ABMCU = :119
                                OR T0.ABMCU = :120
                                OR T0.ABMCU = :121
                                OR T0.ABMCU = :122
                                OR T0.ABMCU = :123
                                OR T0.ABMCU BETWEEN :124 AND :125
                                OR T0.ABMCU BETWEEN :126 AND :127
                                OR T0.ABMCU BETWEEN :128 AND :129
                                OR T0.ABMCU BETWEEN :130 AND :131
                                OR T0.ABMCU BETWEEN :132 AND :133
                                OR T0.ABMCU BETWEEN :134 AND :135
                                OR T0.ABMCU BETWEEN :136 AND :137
                                OR T0.ABMCU BETWEEN :138 AND :139
                                OR T0.ABMCU BETWEEN :140 AND :141
                                OR T0.ABMCU = :142
                                OR T0.ABMCU BETWEEN :143 AND :144
                                OR T0.ABMCU BETWEEN :145 AND :146
                                OR T0.ABMCU BETWEEN :147 AND :148
                                OR T0.ABMCU = :149
                                OR T0.ABMCU BETWEEN :150 AND :151
                                OR T0.ABMCU = :152
                                OR T0.ABMCU = :153
                                OR T0.ABMCU BETWEEN :154 AND :155
                                OR T0.ABMCU BETWEEN :156 AND :157
                                OR T0.ABMCU BETWEEN :158 AND :159
                                OR T0.ABMCU BETWEEN :160 AND :161
                                OR T0.ABMCU BETWEEN :162 AND :163
                                OR T0.ABMCU BETWEEN :164 AND :165
                                OR T0.ABMCU BETWEEN :166 AND :167
                                OR T0.ABMCU BETWEEN :168 AND :169
                                OR T0.ABMCU BETWEEN :170 AND :171
                                OR T0.ABMCU BETWEEN :172 AND :173
                                OR T0.ABMCU BETWEEN :174 AND :175
                                OR T0.ABMCU BETWEEN :176 AND :177
                                OR T0.ABMCU BETWEEN :178 AND :179
                                OR T0.ABMCU = :180
                                OR T0.ABMCU = :181
                                OR T0.ABMCU BETWEEN :182 AND :183
                                OR T0.ABMCU BETWEEN :184 AND :185
                                OR T0.ABMCU = :186
                                OR T0.ABMCU BETWEEN :187 AND :188
                                OR T0.ABMCU BETWEEN :189 AND :190
                                OR T0.ABMCU = :191
                                OR T0.ABMCU BETWEEN :192 AND :193
                                OR T0.ABMCU BETWEEN :194 AND :195
                                OR T0.ABMCU BETWEEN :196 AND :197
                                OR T0.ABMCU BETWEEN :198 AND :199
                                OR T0.ABMCU BETWEEN :200 AND :201
                                OR T0.ABMCU BETWEEN :202 AND :203
                                OR T0.ABMCU BETWEEN :204 AND :205
                                OR T0.ABMCU BETWEEN :206 AND :207
                                OR T0.ABMCU BETWEEN :208 AND :209
                                OR T0.ABMCU BETWEEN :210 AND :211
                                OR T0.ABMCU BETWEEN :212 AND :213
                                OR T0.ABMCU BETWEEN :214 AND :215
                                OR T0.ABMCU BETWEEN :216 AND :217
                                OR T0.ABMCU BETWEEN :218 AND :219
                                OR T0.ABMCU BETWEEN :220 AND :221
                                OR T0.ABMCU BETWEEN :222 AND :223
                                OR T0.ABMCU BETWEEN :224 AND :225
                                OR T0.ABMCU BETWEEN :226 AND :227
                                OR T0.ABMCU BETWEEN :228 AND :229
                                OR T0.ABMCU BETWEEN :230 AND :231
                                OR T0.ABMCU BETWEEN :232 AND :233
                                OR T0.ABMCU BETWEEN :234 AND :235
                                OR T0.ABMCU BETWEEN :236 AND :237
                                OR T0.ABMCU BETWEEN :238 AND :239
                                OR T0.ABMCU BETWEEN :240 AND :241
                                OR T0.ABMCU BETWEEN :242 AND :243
                                OR T0.ABMCU BETWEEN :244 AND :245
                                OR T0.ABMCU BETWEEN :246 AND :247
                                OR T0.ABMCU BETWEEN :248 AND :249
                                OR T0.ABMCU BETWEEN :250 AND :251
                                OR T0.ABMCU BETWEEN :252 AND :253
                                OR T0.ABMCU BETWEEN :254 AND :255
                                OR T0.ABMCU BETWEEN :256 AND :257
                                OR T0.ABMCU BETWEEN :258 AND :259
                                OR T0.ABMCU BETWEEN :260 AND :261
                                OR T0.ABMCU BETWEEN :262 AND :263
                                OR T0.ABMCU BETWEEN :264 AND :265
                                OR T0.ABMCU BETWEEN :266 AND :267
                                OR T0.ABMCU BETWEEN :268 AND :269
                                OR T0.ABMCU BETWEEN :270 AND :271
                                OR T0.ABMCU BETWEEN :272 AND :273
                                OR T0.ABMCU BETWEEN :274 AND :275
                                OR T0.ABMCU BETWEEN :276 AND :277
                                OR T0.ABMCU BETWEEN :278 AND :279
                                OR T0.ABMCU BETWEEN :280 AND :281
                                OR T0.ABMCU BETWEEN :282 AND :283
                        )
                )
        )
        AND
        (
                T0.ABAN8 = T1.ALAN8
        )
ORDER BY T0.ABALPH ASC ,
        T0.ABALKY ASC

This is a Huge where clause which is hurting the parser.

Shannon's JD Edwards CNC Blog