2.1.1.UBE Output Files and Background Into Ubes at Runtime

Table of Contents

1Business Problem

2Overview

2.1.1.UBE output files and background into UBEs at runtime

2.2Method of obtaining the output files

3Functional Solution

3.1Proposed Flow

3.2Table Design requests

3.2.1.Email Configuration table F550002

3.3Application Screen Requests

3.3.1.Selection

3.3.2.Logic Required

3.3.3.Screen /Shots

3.4Report Functional Specs

3.4.1.Data Selection

3.4.2.Sequencing

3.4.3.Output layout

3.4.4.Logic required

4Technical Solution

4.1B550002 - *** WSJ PDF Email Address BSFN

4.1.1.Datastructure D5500002

4.2F550002 - *** WSJ PDF Email Config table

Back to Top Generated on Tuesday 16 September 2014 at 07:44:28 for environment E900 Development Environment pathcode DV900 (OW Info v4.2.0.7U)

4.3F550002C - *** WSJ PDF Email Count table

4.4P550002 – *** WSJ Emailing Config Application

4.4.1.Event Rules

4.5T550002 – PO DSTR for P550002

4.6R550002 – *** WSJ PDF Email UBE

4.6.1.Event Rules

4.7R550002T – *** WSJ PDF Test UBE Harness

4.7.1.Event Rules

4.7.2.PO Values

4.8T550002U – *** WSJ PDF Email UBE DSTR PO

4.9V550002A – *** WSJ PDF Email Config Table BSVW

4.10Data Dictionary Items

5Objects Affected

6Assumptions

1Business Problem

Almost ALL UBE jobs run on the E1 system will produce an output file of some sort. PDF, CSV or Text file etc. Users gain access to the completed jobs output by using the WSJ screen in E1 to see all their submitted jobs and using the row exits there in to obtain the output files.

This document deals with the issue of gaining access to these files and potentially having the E1 software configured to automatically email out completed jobs output files.PDF / CSV etc to designated users.

*** has several clients who would like the ability to be sent an E1 jobs output file or to be notified of certain processing events within the E1 system. UBE completed successfully or errors within the job etc.

These notifications will be in the form of emails that will have the completed jobs output file attached to the email.

E1 already does give you the ability to email completed jobs PDFs via the BI Publisher bolt on. This is a fairly labour intensive endeavour and is also OBNM specific. It is also more focused on and intended for media rich output. There is also another method of sending emails in E1 called OSA Output Stream Access. OSA is a very powerful feature in E1 but requires scripts to be written for a Unix platform and sadly is fairly unexplored feature in E1.Beyond the current skill set of ***.

The preferred way to send emails would be to create a solution in E1s native ER language using well tried and tested methodologies. A goal of this development is once complete, it would not involve any further development effort to give further UBE/VERS the ability to email its completed job file output.

Due to the fact the WSJ screen in E1 indicates the jobs status, it is important to note that *** can benefit from this proposed functionality by giving ***the ability monitor sensitive jobs by sweeping over the submitted jobs table looking for these jobs that may have ended in error. A designated internal ***user or team can then be notified automatically when things go wrong.

2Overview

This scope of this document is to describe at a technical, object level, the method used to get certain E1 completed job output files sent via a mail server connected to E1 automatically. The approach taken here is to try and make this functionality totally achievable via configuration alone. This is to remove the need for future development effort in order to add future UBE jobs .

UBEs and VERS wishing to be emailed can simply be added to the config table.

BI Publisher is an extension of E1 that can send completed jobs via email but this process is intended for the optimisation and graphic illustration of the completed jobs ‘data’ with the aid of a rich text template.

BI Publisher is a great tool but lends itself to marketing media more than the simpler requirement needed here.

Plus BI needs too much development work at OBNMetc UBE level in order to merge the completed jobs output onto a media rich template file.

2.1.1.UBE output files and background into UBEs at runtime

ALL UBE jobs in E1 when run on the server will write a record to the WSJ table, F986110

Most UBE jobs in E1 will produce an output file of some sort. CSV, PDF, TXT or XML etc. These can be seen and accessed via the Work with Submitted Jobs screen (WSJ).

When the job is complete all the output files and logs reside in a network folderon the Unix enterprise servers print queue folder.

/App/jdedwards/e900/PrintQueue/

These files can be accessed and attached to an email via the WSJ screen if downloaded and saved but this is a manual and time consuming process.

E1 does not have the DIRECT ability to email out completed jobs file however E1 does have the ability to hook into an email server and use it’s services to send emails plus seen as E1 is platform independent we can also query operating systems and manipulate network files. Combining the 2 we can get the attachment and send this via email.

2.2Method of obtaining the output files

ALL completed UBE jobs in E1 reside on table F986110. So we will use this table to get the completed jobs PDF/CSV file off the server and attach it to an email.Field F986110.JCFNDFUF2 contains key fields OBNM_VERS_JOBNBR_PDF

• Create a custom table to configure the UBE/VERS and distribution list
• Create a sweeper UBE to look for configured completed jobs and gather PDFs/CSVs
• Create a BSFN to email out of *** and add the completed jobs PDF/CSV

3Functional Solution

3.1Proposed Flow

The diagram below illustrates the F986110 table and the subsequent PDF/CSV output files. Each record will have a unique JOBNBR.

New scheduled UBE R550002 will read over the F986110 and store its last read JOBNBR. This will be used to process newly submitted jobs since the last run.

For each F986110 record FNDFUF2 will be broken down from OBNM_VERS_JOBNBR_PDF and then checked against F550002 for a match on OBNM VERS USER and active Y/N = Y

If a match is found we get the PDF/CSV, attach to an email and use the configured F550002 record to obtain destination email address and subject text etc

The work described here will be split into 2 main parts:

1. Create an application to allow completed jobs PDF/CSV to be emailed via configuration of a new custom table.

Allow email address to added and also the email subject and body text. Email limits per run and allow the attachment file to be renamed.

1. Create a new UBE to read over the WSJ F986110 job table and email out the completed jobs PDF/CSV

This UBE will incrementally sweep over the F986110 table looking for jobs that are completed or in error. For every job found, it will thenreference the worktable above and see if emailing is required.

3.2Table Design requests

Create a table to store email related values

3.2.1.Email Configuration table F550002

Create a new table in the E1 schema to store values relating to email address, email subject, email body text and email limit.

This table is to allow differentiation by OBNM, VERS and USER and job status (E/D).

The table should also have an active flag so we can disable/enable the emailing of certain jobs.

The table may also benefit from the inclusion of MCU if site separation of shared versions is required. MCU row security can then also be added.

3.3Application Screen Requests

Create an E1 application screen to allow the maintenance and creation of WSJ email related data.

3.3.1.Selection

Create a BSVW to read over table F550002. Show ALL fields. Allow MCU row security to prevent cross site data.

Allow the Find Browse form to filter on UBE VERS USER ID, Active Y/N and MCU.

3.3.2.Logic Required

Allow the users branch to default into the application screen. This can be used for row security.

Job status cannot be blank as dictated by the linked UDC.

USER ID cannot be blank. Send To/From emails cannot be blank. Subject cannot be blank.

Allow the user to select what output file they want. CSV/PDF.

Allow the user to set a limit if desired on the volume of emails a job can create per 30 min run.

3.3.3.Screen /Shots

3.4Report Functional Specs

Design a report to read over the WSJ Bootstrap F9861110 table in 30 minute intervals.

It should select and read F986110 records from the last run. Therefore it will need to keep a track of the LAST read job number so it knows where to start from next run.

It should select records with a job status equal to Done or Error D/E.

To prevent spamming an inbox it should keep a count of the number of emails sent per unique job.

The UBE should get the completed jobs file and add it as an attachment to the outbound email.

3.4.1.Data Selection

JOBSTS = D/E and UKID greater than LAST read JOBNBR

3.4.2.Sequencing

<If any data sequencing is required list it here>

3.4.3.Output layout

CSV output listing records selected for emailing. List email address etc.

3.4.4.Logic required

It is important to note the different file address of PDF and CSV files.

CSV = /App/jdedwards/e900/PrintQueue/R550002_***0001_1935821_PDF.csv

PDF = /App/jdedwards/e900/PrintQueue/R550002_***0001_1935821_PDF

4Technical Solution

A new application P550002 will be used to create records on new table F550002.

F550002 will be able to store fields based on email subject and destination addresses. F550002 will be unique on OBNM VERS USER and JOBSTS

New UBE R550002 will read over F986110 based on JOBNBR greater than the last read job number (or first starting point) saved in next number table F00022 under TBLE = ‘F550002’

New UBE R550002 - *** WSJ PDF Email UBE will be scheduled to run every 30 minutes.

The UBE will incrementally sweep over the WSJ table looking for jobs run since the previous run of R550002.

The WSJ table also holds the name of each report UBE and Version VERS that ran in that 30 minute window. It also holds the USER ID of the user who ran the job.

We can utilize these values to look for and target specific users or specific UBE, VERS and USER combinations for designated emailing out of the E1 system.

4.1B550002 -*** WSJ PDF Email Address BSFN

A simple C++ /BSFN to get the print queue address off the server

Function B550002 - *** WSJ PDF Email Address BSFN
Source Language C - C
Location 2 - Both Client & Server Function
Parent DLL C57***
System Codes 55/55

Back to Top
Generated on Tuesday 16 September 2014 at 07:39:52 for environment E900 Development Environment pathcode DV900 (OW Info v4.2.0.7U)

4.1.1.Datastructure D5500002

Datastructure D5500002 - *** WSJ PDF Email Address DSTR
System Codes 55/55

Back to Top
Generated on Tuesday 16 September 2014 at 07:39:52 for environment E900 Development Environment pathcode DV900 (OW Info v4.2.0.7U)

4.2F550002 - *** WSJ PDF Email Config table

Table to store all the relevant emailing details for each OBNM VERS USER combination

Column Prefix RE
System Codes 55/55
Go to Indexes

IndexesGo to Columns

Back to TopGenerated on Tuesday 16 September 2014 at 07:44:28 for environment E900 Development Environment pathcode DV900 (OW Info v4.2.0.7U)

4.3F550002C - *** WSJ PDF Email Counttable

Table to store a count per 30 minutes for each time an email is sent out for a given OBNM VERS USER combination. This is cleared down each run

Table F550002C - *** WSJ PDF Email Count table
Column Prefix RE
System Codes 55/55
Go to Indexes

IndexesGo to Columns

Back to Top
Generated on Tuesday 16 September 2014 at 07:50:21 for environment E900 Development Environment pathcode DV900 (OW Info v4.2.0.7U)

4.4P550002 – ***WSJ Emailing Config Application

Application to maintain the F550002 email config records and job number starting point

Application P550002 - *** WSJ PDF Email Config Application
Platform Client or Web
PO Template T550002
System Codes 55/55

4.4.1.Event Rules

4.5T550002 – PO DSTR for P550002

PO Template T550002 - *** WSJ PDF Email Config Application DSTR
System Codes 55/55

4.6R550002 – *** WSJ PDF Email UBE

This is the UBE that will that will run every 30 minutes and reference the last run job number on F00022 for ‘F550002’

It reads over F986100 using BSVW V986110A selecting on JOBNBR > saved JOBNBR on F00022 and where JOBSTS = D/E

4.6.1.Event Rules

4.7R550002T – *** WSJ PDF Test UBE Harness

All this UBE does is override the F9861110 datasource and call R550002 to allow this to run locally in DV PY and UT.

Pass in the name of the datasource DATP

4.7.1.Event Rules

00001SetF986110DS(B9861101.SetF986110DS)
"***AX-JDED1-900ServerMap"->szDatabasepath[DATP]
00002//
00003CallUBE:R550002Version:***0001
00004//
00005SetF986110DS(B9861101.SetF986110DS)
<Blank>->szDatabasepath[DATP]

4.7.2.PO Values

4.8T550002U – *** WSJ PDF Email UBE DSTR PO

PO template for R550002

PO Template T550002U - *** WSJ PDF Email UBE DSTR
System Codes 55/55

4.9V550002A – *** WSJ PDF Email Config Table BSVW