Emailing completed UBE jobs CSV/PDF

[johndanter]

Hi List,

OK, what I would like to do is automatically send UBE output of a completed job to people via email but not by creating a BI version of each UBE.
I want this to be more generic and configurable

I have and idea of making a UBE that can read over F986110 looking for completed jobs (within a time frame)

I would then like this UBE to cross check a new config table for certain OBJ/VERS to see if they are setup for external emailing....

If so, boomb, out goes the email to the user defined on the config table.

I've actually done something like this before but it was a manual step in an APPL. I got the UBEs output by basically replicated the WSJ view PDF row exit.... but it was so long ago I can't fully remember what BSFN I then used to do the emailing of the attachment and I've lost the spec

So the steps would be:
Look for completed jobs on F986110
Check if they match the config table (or the other way around maybe??)
Get the completed jobs CSV/PDF
Attach this to an email
Send email out


Has anyone done anything like this and what BSFN did you use to send the attachment email?

Thanks

John

 

[craig_welton]

John,

We have done this at several places using a trigger on F986110 and custom tables defining the UBE/Version and email distribution list.

When the job status is updated to D (or inserted with D), check the table for a record and if it exists, call the BSFN to attach and send the email. We actually wrote the emailing BSFN ourselves, but I think there is an existing one that will work. (sorry not a dev workstation right now).

Craig

 

[johndanter]

Hi Craig, thanks for this

Yeah I don't think a trigger on the F98 would go down too well here, hence my work table and sweeping UBE approach.

When you are at a station, please lmk what you find.

The TBLE Trigger ER and the BSFN would be great thanks

Ta

John

 

[craig_welton]

B0500725.EmailMergeLetter seems to do the trick.

I'm curious as to why the trigger wouldn't be considered. We have been running it for ages with no impact on performance etc.

 

[neil_shepherd]

Hi John,

We had some very specific requirements around report distribution where we'd need to e-mail and/or save to specific directories based on a bunch of criteria and used a very similar method to the sweep up you describe. UBE running over the F986110 checking records within a specific timeframe, referencing a config table and distributing as necessary. For the e-mailing we use B0500725.

Regards

Neil.

 

[johndanter]

There is a zero base object modification policy here. So I know it'll be rejected
I wanted to put a trigger on on the F986110 before to launch the UBE with logging on (if configured)
No joy

Thanks for the BSFN

 

[peterbruce]

John,

We used to have a trigger on the F986110. But a response from Oracle Customer Support (actually Oracle Development) said basically that system tables (which includes F986110) should not be modified, which includes adding JDE triggers. (see new site thread 31224 and post 170218; old site post 183049).

I have another way that may do what you need (it may not too). It involves setting up print immediate in the enterprise server jde.ini and setting up default printers. Define the printer to run a script on the server which receives the PDF (which has the UBE, version and job number in the name) to be printed as a parameter. This script can use the information in the name lookup (maybe more information from the F986110 such as user id) an email address from a lookup table and email the PDF.

If you are interested I can provide more (including technical) details. This may not be appropriate for you as it involves print immediate (without actually printing anything if needed) and it would depend how far the "zero base object modification policy" policy extends.

 

[johndanter]

Many thanks for all your replies guys, very impressive.

Did you also replicate the code in WSJ to go off and get the actual file, or something else?

Lots for me to think about here, so thanks again guys. but do keep them coming

PS: D Day today. 70 yrs on.
Repesct and hats off to all the surviving vets and those brave men gone before them

 

[Deepesh]

Hi everyone,

Just to add on to the question, do we have any BSFN or modification with which we can send multiple attachments/PDF's in one single email?

 

[johndanter]

Hi Peter,

I have sent you an email sir.
Your inbox is full apparently

Ta

John

 

[johndanter]

Has anyone here tinkered with OSAs in JDE yet?

They look like they can do the job of firing off commands/objects at certain levels. I am hoping to be able to plug in an emailing command when the UBe is complete

Tables F986168/69 seem to be able to be defined at a OBNM VERS level

 

[jdelisths]

Here is an out-of-box product that does this. Here is a link to a demo video: https://www.youtube.com/watch?v=G2eRqLicbvM

 

[johndanter]

Hi guys

Quick question on F986110 and using it in a join in BSVWs

Am i right in thinking I can't do this unless the other joined table lives in the same datasource?

In my site F9861110 lives in the GTSAX-JDED1 datasource, whilst my config table will live in DEVDTA etc

So that's my BSVW idea blown right?

Would a subsection join work?

 

[David Leon]

buen dia

tienes que realizar una modificación a la funcion de negocio para que te acepte multiple anexo (B0500725.EmailMergeLetter) . Yo lo hice y me funciona muy bien.
No te recomiendo buscar en la tabla F986110 el numero de trabajo del UBE por ser muy sencible el proceso.

Utiliza este codigo si tu reporte no es transaccional.

VA evt_szJobstatusOW_JOBSTS = "P"
//
Get Local Computer ID
VA evt_szMachineKey_MKEY <- BF szMachineKey
Get Current Environment
VA evt_szEnvironmentName_ENHV <- BF szEnvironmentname
F986114.Open
F986114.Select
VA evt_szMachineKey_MKEY = TK Execution Host Name
SL ReportName = TK Program ID
SL VersionName = TK Version History
VA evt_szJobstatusOW_JOBSTS = TK Job Status
VA evt_szEnvironmentName_ENHV = TK Environment Name
F986114.Fetch Next
VA evt_szServerJobNumber_JOBNBR <- TK Server Job Number
F986114.Close
//
F98611.Fetch Single
VA evt_szDatabasePath_DATP <- TK Data Source
VA evt_szMachineKey_MKEY = TK Server Name
F986110 DS OVERRIDE
VA evt_szDatabasePath_DATP -> BF szDatabasepath
F986110.Fetch Single
VA evt_szServerJobNumber_JOBNBR = TK Server Job Number
VA evt_szMachineKey_MKEY = TK Execution Host Name
VA rpt_szDirecArchivoAdjunto_HLNK <- TK Foundation - Future Use 2
VA rpt_szDirecArchivoAdjunto_HLNK = ltrim(rtrim([VA rpt_szDirecArchivoAdjunto_HLNK]," ")," ")


Si es un reporte transaccional, debes pensar en extraer de manera mas optima el JOBNBR (Numero de Trabajo). Si existe forma. hay un proyecto que se publico en este foro y es muy bueno (PRJ_PJ0001_60_99).

Saludos,
David León
Developer

 

[David Robertson]

You can create the BSVW fine, but only 2 tables max, otherwise when you try to use it:
JDB3800025 - Total number of tables (2) allowed in cross data source join exceeded.
JDB3100056 - Failed to create cross data source request

It may also be somewhat dependant on the DB you are using, and whether they are actually on the same DB but different schemas/owners, or different databases.

Edit: You may have troubles with F986110 though, as you normally need to override the data source from SY910 to SVM910.
I would be tempted to create a dummy E1 table of the structure you need, and manually do a CREATE OR REPLACE VIEW xxxxDTA.F55JDFUN AS (SELECT Stuff FROM SVM910.F986110 JOIN xxxxDTA ON etc=etc)
eg:
This works fine on our AS400, as they are all on the same box (PRODJDE),
CREATE OR REPLACE VIEW PRODJDE.ROBERD.F55TEST AS
(SELECT * FROM PRODJDE.SVM910.F986110
JOIN PRODJDE.SY910.F0092 ON ULUSER=JCUSER
JOIN PRODJDE.PRODDTA.F0101 ON ABAN8=ULAN8);

but this fails with "Error: [SQL0512] Statement references objects in multiple databases."
CREATE OR REPLACE VIEW PRODJDE.ROBERD.F55TEST AS
(SELECT * FROM PRODJDE.SVM910.F986110
JOIN PRODJDE.SY910.F0092 ON ULUSER=JCUSER
JOIN TESTJDE.TESTDTA.F0101 ON ABAN8=ULAN8);

 

[johndanter]

Ta David (are you the David Robertson who went to see Roger Waters with together? )

Another question....does anyone know a quick way of obtaining the PDF/CSV attachment network address?

 

[David Robertson]

Aye, 'twas I!
I still have the T-shirt to prove it

 

[johndanter]

Awesome, hello!!

All I need now is a way of getting the actual PDF network address so I can pass this in as the attachment address and I'm done

 

[craig_welton]

John, are you using OSA? The complete filename should be in the datastructure.

If you're in a JDE function/UBE, the API GetPrintQueueDirectoryPathName should get you the path to the prinqueue directory. The F986110 should have the filename in FNDFUF2 (minus the extension I believe). So combining the 2 will get you the location on the enterprise server.

Craig

 

[johndanter]

Hi Craig,

Not sure about OSA (but we do have BI Pub active here)

I'll have a look to see if there is a BSFN that uses that API

Thanks