Extracting CSV output and placing it on network path specified in processing option

MayuS24

Member
Hello Listers,

We have a requirement from client.
The UBE should give CSV output and the output file should be placed on the path specified in processing option.
Also the file should be renamed in a particular format as specified by client.
Can someone help me with the code to achieve this?
Thanks in advance. Really need help as this is quite urgent.

Regards,
Mayus24
 

alfredorz

Well Known Member
Hi MayuS24,

A solution that comes to mind might be: you can find the csv output in printqueue (by command or by job number, or both) and you could copy the file with command line (windows or linux, depends of enterprise operative system).
 

johndanter

Legendary Poster
Look at B34A1010
Open file (with csv)
Write to file (with commas for the files columns)
Close the file

What platform are you running E1 on?
I have written something to attach the output (PDF/CSV) of completed jobs to emails, if this helps you?

I also know how to get the output file and copy it to another destination. So it depends what exactly you want to do and why...?
 

Lucky.2811

Active Member
Easy solution would be creating a wrapper UBE and calling the main report from the wrapper. Just three lines of code is sufficient to copy the PDF

1. Call required UBE using Launch UBE BSFN and fetch Job Number
2. Do a fetch on Job Status file to fetch the exact name of the CSV file
3. Call BSFN DeleteOrCopyFlatFile to copy it to the required location



LaunchBatchApplication(B91300C.LaunchBatchApplication)

Report Name -> szReport [OBNM]
Version -> szVersionJDE [JDEVERS]
"1" -> cSynch [EV01]
"0" -> cPrintPreview [EV01]
<Blank> -> szDataSourceOverride [DATP]
Server Job Number <- mnServerJobNumber [JOBNBR]
Return <- cReturn [EV01]
"0" -> cJDELogging [JDELOG]
"0" -> cTracing [TRACING]
"1" -> cUBELoggingLevel [UBELOGLVL]
<Blank> -> szJobQueue [JOBQUE]

If Return is equal to "1"

F986110.FetchSingle [Index 1: Job Control Master Primary]

Server Job Number = TK Server Job Number [JOBNBR]
Job File Name <- TK Foundation - Future Use 2 [FNDFUF2]


Job File Name = concat(ltrim(rtrim(Job File Name),' '),' '),".csv")
From File Name = concat(JDE Print Queue Server Path, Job File Name)
To File Name = concat(JDE Print Queue Server Path, Job File Name)

DeleteOrCopyFlatFile(B4700230.DeleteOrCopyFlatFile)
From File Name -> szOriginalFlatFile [TXTDIR]
To File Name -> szNewFlatFile [NFLF]
"2" -> cActionOnFlatFiles [EV01]

End If
 

jolly

Reputable Poster
Just create an eText BI Publisher template and RD, and setup BI Publisher archiving.

No development required.

You probably need tools release 9.1.4.5 for it to work.
 

babs

Active Member
We use QNTC to place E1 files generated via Job Scheduler on network drives. Path is specified in PO's and it works well for us.
 

MayuS24

Member
Hi John,

Thanks for the reply. :)
I would like to know how to get the output file and copy it to another destination.
I am a newbie with not much knowledge on these business functions.
Basically my requirement is to rename & place the csv output on a specified path so that it will be picked up by the middleware.
So it would be great if I can get detailed info on this, will be a learning experience.
Appreciate your help.
 

MayuS24

Member
Thanks Lakshmi.
Is it possible to get the server job number without using a wrapper UBE?
I am planning to use the 'End Report (future)' event to rename & copy the file using similar code.
If I get the server job number using some bsfn then I can use your code from starting from F986110.FetchSingle.
 

harveyk

Active Member
QNTC is only relevant if you are running on an iSeries.

From a UBE you can use Database Output to output a csv to a particular location and then use the BSFN to move/copy this to the location/name specified in your Processing Option.
 

jolly

Reputable Poster
Hi John,

Thanks for the reply. :)
I would like to know how to get the output file and copy it to another destination.
I am a newbie with not much knowledge on these business functions.
Basically my requirement is to rename & place the csv output on a specified path so that it will be picked up by the middleware.
So it would be great if I can get detailed info on this, will be a learning experience.
Appreciate your help.
Sorry I don't have time to write a detailed decription... you will need to lookup the BI Publisher Archiving documentation - the output destination is configurable P95641/P95642 similar to OCM. It will allow you to output to any path on the server (including remote mounted folders). If you need it somewhere else you'll need to write a BSFN that is platform dependant.
 

johndanter

Legendary Poster
There are so many ways you can achieve what you want. As you can see. So it's up to you really.

How complex is the CSV?

If it's just simple columns and headers/footers I would go with my option of using OpenFlatFile and making the csv text yourself.
Just use A,B,C etc as you go along.
So create the header columns, then next time just start adding your variables into the csv line text

I agree it's a bit tricky but I feel the simplest to get your head around in terms of what's going on.


You'd need to make sure the Ent Server is mapped to the network location you wish to save the files onto first mind. Or whatever option you chose, this will never work anyway :)

FYI all completed jobs file live as described below (a snippet of my code)

Create file address (printqueue/OBJ_VERS_JOB.csv/pdf)
00134 | // =-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
00135 | // Create file address (printqueue/OBJ_VERS_JOB.csv/pdf)
00136 | // =-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
00137 | // Jobs in WSJ are labelled OBJ_VERS_JOBNBR_PDF but on the Print Queue they
00138 | // will be saved as follows
00139 | //
00140 | // CSV
00141 | // /App/jedwards/e900/PrintQueue/R550002_GTS0001_1935821_PDF.csv
00142 | //
00143 | // PDF
00144 | // /App/jdedwards/e900/PrintQueue/R550002_GTS0001_1935821_PDF


Download object browser and start messing about with the BSFNs you find.
Look in table F9862 for a list of all the BSFNs in E1 :)

You could use B0500725 to check the file exists (1 if it finds the file)
 
Last edited:

baha

Active Member
Hi Lakhmi,

Thanks for providing this solution, I just have a quick requestion in regards to the below line of code used in this , JDE Print Queue Server Path is this a system variable?

From File Name = concat(JDE Print Queue Server Path, Job File Name)
To File Name = concat(JDE Print Queue Server Path, Job File Name)

Thanks,
 

Lucky.2811

Active Member
Yes. We can get server job number. Job Number is part of pUBEVar data strcutre. Build a custom BSFN using below code. Once the BSFN is built it can be used repeatedly wherever job number is needed

/************************************************************************
* Variable declarations
************************************************************************/

JDEDB_RESULT eJDEDBResult = JDEDB_PASSED;
LPReportSection pReportSection;
HUSER hUser;

pReportSection = lpBhvrCom->lpReportSection;


jdeStrncpy ((JCHAR *)lpDS->szProgramId, (const JCHAR *)pReportSection->pUBEVar->szReport, (DIM(lpDS->szProgramId)-1));
jdeStrncpy ((JCHAR *)lpDS->szVersionId, (const JCHAR *)pReportSection->pUBEVar->szVersion, (DIM(lpDS->szVersionId)-1));
jdeStrncpy ((JCHAR *)lpDS->szPOTemplateId, (const JCHAR *)pReportSection->pUBEVar->szPOTemplateName, (DIM(lpDS->szPOTemplateId)-1));

MathCopy (&lpDS->mnServerJobNumber, &pReportSection->pUBEVar->mnJobNum);
 
Top