Triggering UBE subsystem processes from outside E1

HolderAndrew

Well Known Member
Hi All,

Since the old days (B7321-->Xe) we have always had the ability to insert records directly into F986113 from outside of OneWorld in order to trigger UBE subsystem processes effectively. This was done by concatenating the required RI DSTR values into a pipe separated string and assigning it to the SRVBLOBA column along with all the other necessary columns (PID, VERS, ENHV, etc...).

In 811 this is no longer possible as the SRVBLOBA column is set to *POINTER when a record is inserted via the standard api ubeReport_AddSubsystemRecord. I have a few questions which I would be very grateful if anybody out there knows.

Q) Where is the RI DSTR information now stored?
Q) Does anybody know of a method to insert records into F986113 successfully from outside EnterpriseOne 8.11 (or maybe 8.10)?

I suspect that we need to use interoperability method (using COM wrappers) but then this takes time for the connection to take place before the BSFN api can be called successfully.
I was hoping that we could maybe have a table trigger to call the api upon an insert action on a temp file but this can only work inside of E1?

Any ideas would be appreciated.

Regards

Andrew Holder
 
Andrew,

We used the same mechanism for interfacing OneWorld Xe Asynchronusly from another system.

We are upgrading to 8.11.

I played with the F986113 content using a VB program that extracts the content of the BLOB as a byte array.

I found out that the BLOB still contains a chain of parameters in the form of a String, separated by a pipe. The only difference is that this string is UNICODE.

I used OneAssist to call [AddPrepayAdjustmenttoSubsystemQ]like this:
var field, dsD4202270A = new Object();
dsD4202270A.szVersion = ""; // [VERS]
dsD4202270A.cSuppressErrorMessage = ""; // [SUPPS]
dsD4202270A.szErrorMessageID = ""; // [DTAI]
dsD4202270A.mnDocumentOrderInvoiceE = "97"; // [DOCO]
dsD4202270A.szOrderType = "SP"; // [DCTO]
dsD4202270A.szCompanyKeyOrderNo = "00001"; // [KCOO]
dsD4202270A.szOrderSuffix = "000"; // [SFXO]
dsD4202270A.szPrepaymentOriginationFlag = "AB"; // [POF]

AddPrepayAdjustmenttoSubsystemQ( dsD4202270A )

for (field in dsD4202270A) {
writeln( field + ": " + dsD4202270A[field] );
}

You can easily read the content of each structure member for R004201 from the code above:

Report Interconnect Structure <~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[DOCO] mnDocumentOrderInvoiceE
[DCTO] szOrderType
[KCOO] szCompanyKeyOrderNo
[SFXO] szOrderSuffix
[POF] szPrepaymentOriginationFlag

The output of the resulting record in the table using my VB program is this:

===============================================
SSPID=R004201 , SSVERS=XJDE0002 , SSJOBNBR=0, SSJOBSTS=R , SSOPCR=W, SSEXEHOST=ERP-APP1 , SSORGHOST=WORKSTATION-339 , SSENHV=DV811 , SSUSER=SLECLERC , SSPSWD=****** , SSSBMDATE=105262, SSSBMTIME=5, SSJOBPTY=1, SSACTDATE=0, SSACTTIME=0, SSFUNO= , SSKOPT= , SSSRVBLOBA=[9][][7][][|][][][P][][|][][0][][0][][0][][0][][1][][|][][0][][0][][0][][|][][A][][][][], SSFNDFUF1= , SSFNDFUF2= , SSMATH01=0, SSMATH02=0, SSPROCESSID=0, SSDATE01=0, SSDATE02=0, EOR
===============================================

You can see the parameters in the binary field.

I ran the same program, but instead of converting a byte to a char, I kept the decimal representation:

[57][0][55][0][124][0][83][0][80][0][124][0][48][0][48][0][48][0][48][0][49][0][124][0][48][0][48][0][48][0][124][0][65][0][66][0][0][0]

This clearly shows a UNICODE String in there.

The next step will be to determine the proper SQL statement to insert UNICODE STRING instead of a STRING into the database.

Let us know of your progress!
 
Hi Sebastien,

many thanks for the reply. It is nice to know that the BLOB area might be manageable! I will let you know if I have success in inserting UNICODE DSTR that triggers subsystem UBE's.

Regards

Andrew
 
I haven't it tried yet on our custom subsystems, since they were not tested yet in 8.11, but here is an SQL statement that will create a binary string with the proper UNICODE format.


The resulting string in the binary field was:
SSSRVBLOBA=[1][][2][][3][][4][][5][][|][][][P][][|][][0][][0][][0][][0][][1][][|][]

Using <font color="red"> N'ABC' </font> casts a string to UNICODE.
Using <font color="red"> convert(nchar, 12345 ) </font> casts a number to UNICODE

The final conversion to binary is done through a last call to convert() like this:

<font color="red"> convert(binary </font> ,rtrim( convert(nchar,12345 ))+N'|'+N'SP|00001|' <font color="red"> ) </font>

Let us know if this works for you!
 
That's the trouble with going under the radar - when they change the system things tend to stop working.

I think the interoperability approach is the correct one, but instead of using COM you might consider XML instead. I think it is a lot simpler. I couldn't comment on the performance, but I've never found it to be an issue.
 
Has somebody got an example of doing this using XML interoperability?
I'm trying to figure it out and I could really use an example.

Thanks!
 
Andrew,
For years I was looking for a soln to trigger subsystem UBE by inserting record in F986113. Looks like you have already done it.
Can you pl. let me know what all fields you have to concat with pipe separation to be assigned to SRVBLOBA column

Pl. let us know
Thanks
jdeman
OW, XE, SP-21, AS400, Update7
 
After a further 10 years (how time flies!) i have finally done a little test to prove how this can be done. The sql below (on iseries) runs a subsystem UBE process based on RI DSTR comprising order number DOCO (in this example 5000720) and MODE ('M')

INSERT INTO SVM900/F986113 VALUES('R595070A', 'LT8002S', 0, 'R',
'W', 'FITAMBET', 'FITAMBET', 'JPYLIT900', 'HOLDEANI', '******',
115200, 216, 1, 0, 0, ' ', ' ', BLOB(N'|5000720|M|'), NULL,
NULL, NULL, NULL, NULL, NULL, NULL)
1 rows inserted in F986113 in SVM900.

I am not suggesting that this is necessarily the best solution! But I think it is good to know and can be useful if webservices or other methods are not available.

BRgds
 
Many thanks for this.

We have many here and not all our platforms and tech can use the BSSVs, so I'll suggest this :)

Are you able to break down and elaborate on all you're inserting please?
Especially here: BLOB(N'|5000720|M|') What does the N do?

Thanks
 
Hi List,

we might try and use this method to trigger subsystems from some interfaces (albeit temporarily!) but can anybody give example of how the insert to F986113 could work with a variable string? For example BLOB(N'123') will insert '123' into the blob column SSSRVBLOBA but how to do this with a variable, I have tried various things like BLOB(N:$VAR1) etc... I was hoping for a simple syntax for the embedded sql within the SQLRPGILE source code.

brgds
 
Hi all,

quick note to inform you that we now have 2 techniques for doing this. 1) to use SQLRPGILE member with embedded Sql to insert into F986113 (using the blob manipulation described above and 2) using RPGLE program to call BSSV (this is described in a separte thread if you search for it). Thanks for your input and good luck if youc hoose to do either of these.

Brgds
Andrew
 
Hi Andrew,

That's great! Would you have a spec or something you could share please?

As in, this is my UBE, this is it's VERS and this is the DSTR and this is what I actually inserted.
As there are a few fields in F986113 and I'd like to try this myself and make sure I get all the fields right.
 
Hi John,

the sql is as below within the free format section of SQLRPGLE source - all of the columns can be defined as you like (eg: the UBE name is: EVAL $SSPID = 'R55XXXXX';)

there is one gotcha worth noting, the SBMTIME value should be a unique running number (to force uniqueness in F986113)

the blob value should be initialsd to required parm string and then converted to unicode

in our volume tests in 9.1 this is working extremely fast.

Please NOTE that this is not a supported Oracle solution - for that we are using BSSV RPGLE call to generic BSFN to insert trigger records.

Anyway, here is "the code" maybe somebody out there might find it useful. I am passing some parms through for version, env and parameter string.

/free

// Set up susbsystem column constants //

EVAL $SSPID = 'R5xxxxx';
EVAL $SSVERS = %trim(PVersion);
EVAL $SSJOBNBR = 0;
EVAL $SSJOBSTS = 'R';
EVAL $SSOPCR = 'W';
EVAL $SSJOBPTY = '1';
EVAL $SSEXEHOST= 'xxxxxxx';
EVAL $SSORGHOST= 'yyyyyyyy';
EVAL $SSENHV = %trim(PEnhv);
EVAL $SSUSER = 'USER';
EVAL $SSPSWD = '******';
EVAL $SSACTDATE = 0;
EVAL $SSACTTIME = 0;
EVAL $SSSRVBLOB = %trim(Parmstring) + $HEX;
$SSSRVBLOBC = %UCS2($SSSRVBLOB); }

EXEC SQL

INSERT INTO SVM910/F986113 VALUES(
:$SSPID,:$SSVERS,:$SSJOBNBR,:$SSJOBSTS,:$SSOPCR,:$SSEXEHOST,:$SSORGHOST,
:$SSENHV,:$SSUSER,:$SSPSWD,:$SSSBMDATE,:$SSSBMTIME,:$SSJOBPTY,
:$SSACTDATE,:$SSACTTIME,:$SSFUNO,:$SSKOPT,BLOB:)$SSSRVBLOBC),
:$SSFNDFUF1,
:$SSFNDFUF2,:$SSMATH01,:$SSMATH02,:$SSPRO00001,
:$SSDATE01,:$SSDATE02);

*INLR = *ON;
/end-free


Brgds
Andrew
 
IMO, you guys are really working against the grain when it comes to features that are available in OMW. There is a proper way of doing this within OMW that doesn't rely on SQL hacks and should not break in future releases of JDE.

If you need to launch a UBE report, the simple and obvious way is to use a Report Interconnect.
If you want to trigger the Report Interconnect in an automated fashion, then you should create an NER BSFN that has parameters for each of the Report Interconnect variables you need to pass along. **Make sure you use the Asynchronous option in the RI!**
If you need to trigger that BSFN from outside of JDE, then you should look into the JDE XML Interop API - which is an extremely simple way of publishing JDE methods to the outside world.

If you have Java developers on staff, then it should be really easy for them to build a wrapper for com.jdedwards.system.xml.XMLRequest and its "execute" method for trigger JDE BSFNs from other Java apps.

Alternatively, you can use a pre-made adapter which exposes JDE BSFNs to external development platforms.
There's a commercial option: http://www.everestsoftint.com/products_everest2jde.php
And a free / open source wrapper as well: https://github.com/brandonkirsch/JDEdwards-XMLRequest-Servlet (**Disclaimer, I wrote it. It is production-ready for non-Unicode systems, but has some fixes that need to be applied in order to send Unicode data)

Both of those options should allow you to quickly script proper execution of JDE Business Functions, without having to hack at any unofficial or unsupported methods to launch a UBE.
 
Brandon,

if you read my mails you would see that I have already said this is not our preferred method and that we have BSSV method working as well. The hack, as you call it, has been used in our integration processes for 15 years with no issues, with over 500,000 triggers per day. Sometimes you do what works, the choice is yours.

Brgds
Andrew
 
Back
Top