Period Start & End Dates

[tpayne]

I need to select from F42119 in a Query for current period transactions, but the period number is not in the file.
Is there anywhere in a file I can pick up the current period start/end to automatically compare with in a query, or a program I could run that would add these to a file?

I thought to run a query that would select the current period from F0010, calculate the start/end dates from F0008 and write these to a file. Then I could link to this in my Query.

But I think it's going to be a bit hard to create period start/end dates using Query... Does anyone have a program out there? I couldn't see anything in the JDE documentation and am up against a time limit to get this done before monthend...

Thanks in advance...

 

[John_Dickey]

Tony,

What about World Writer? Configure the World Writer to prompt for
data selection values whenever it is run, and the person running it just
keys in the desired date range they want. That makes it flexible for
more than just a current period report. Even better you can output from
World Writer to a file, which you could then move into an Excel
spreadsheet via the IFS, if desired.
Guess you do not have a data warehouse, eh? We do here, and we
can set our report views to be moving dates (using phrases like CURRENT
YEAR and CURRENT MONTH, or PREVIOUS MONTH. Sure beats the heck out of
writing RPG program and doing queries and World Writers. Lets the users
do whatever they want to do. Might be something to suggest to
management.

John Dickey
Financial Systems Administrator
White-Rodgers, division of Emerson Electric
8100 West Florissant Ave.
P.O. Box 36922
St. Louis, MO 63136-9022
314-553-3067
[email protected]

 

[Eric Lehti]

I submitted a FNDSTRPDM to search all F* source members for field names for DD item PNC (period number). Didn't find a file that would help you.
(FNDSTRPDM STRING('PNC ') FILE(JDFSRC/JDESRC) MBR(F*) OPTION(*NONE) PRTMBRLIST(*YES) PRTRCDS(*ALL)

 

[tpayne]

Thanks for the help, actually I managed to find a solution which works.

Worldwriter wouldn't work in this case, we are only on A7.3cum9 so don't have the SAR's applied that allow selection when the WW is run or to allow selection on dates. I have used that before and it's a great enhancement.

We also need this to run in batch automatically - the CL creates a number of files using about 50+ Queries (ok I can see you shuddering now) - I didn't write it, but actually it works pretty well and saves writing a complicated RPG program instead, especially as the requirements keep changing.

What I did was to create a query that links F00365 with F0010. I know the company I need, so I just had to link to link to F00365 with the year and period. That returns from F00365 all the days in the period. I calculated the minimum and maximum values for the date, summarized the output, and wrote it to a file. The file now contains 1 record with the start and end date of the current period. I can link to that in my main query to select transactions for the current period where the period field is not in the file that I am extracting from.

I hope this helps anyone else with a similar need.