Re: CEA/General Ledger

["Bunch, Wendy" <wbunch@xxxxxxxxxxx>]

It depends on if you are only looking for certain types of entries, only certain periods or dates, or for the whole year like so many year end auditors are looking for.

We use Showcase Query - but as other have said could be any Query tool...  below is my version of the select statement for use with 8.3.4.   

Excel has row limits and you can only email something so large.

If you have to download entire year or large period of time - will need IT to download it in some other format than excel and to a portable storage device and send it to them.



SELECT 
	LHLDGR,
	LHBOOK,
	LHYEAR,
	LHPERD,
	LHJNEN,
	HHTRNO,
	LHJNLN,
	HHJDAT,
	CRSG01,
	CRSG02,
	CRSG03,
	CRSG04,
	CRSG05,
	LHDRAM,
	LHCRAM,
	LHDRAT,
	LHCRAT,
	HHEVNT,
	LHSTAT,
	LHLDES,
	LHUSER,
	LHDATE,
	LHTIME,
	HHLDES,
	HHRJEN,
	HHRDAT,
	HHCURR,
	HHREAS,
	LHJRF1,
	LHJRF2,
	LHDREF,
	LHDRAM - LHCRAM AS NET,
	LAANB1,
	LAANB2,
	LAANB3,
	LAANB4,
	LAANB5,
	LAANB6 
FROM 
	S06B831P.P830PFLE.GLH GLH
	LEFT OUTER JOIN (insert mainframe).(insert production environment).GCR GCR
	ON 
		GLH.LHLDGR = GCR.CRLDGR AND
		GLH.LHIAN = GCR.CRIAN
	LEFT OUTER JOIN (insert mainframe).(insert production environment).GLA GLA
	ON 
		GLH.LHLDGR = GLA.LALDGR AND
		GLH.LHBOOK = GLA.LABOOK AND
		GLH.LHYEAR = GLA.LAYEAR AND
		GLH.LHPERD = GLA.LAPERD AND
		GLH.LHJNEN = GLA.LAJNEN AND
		GLH.LHJNLN = GLA.LAJNLN
	LEFT OUTER JOIN insert mainframe).(insert production environment).GHH GHH
	ON 
		GLH.LHLDGR = GHH.HHLDGR AND
		GLH.LHBOOK = GHH.HHBOOK AND
		GLH.LHYEAR = GHH.HHYEAR AND
		GLH.LHPERD = GHH.HHPERD AND
		GLH.LHJNEN = GHH.HHJNEN
	 
WHERE 
	( LHLDGR = &LHLDGR
	AND LHBOOK = 'ACT'
	AND LHYEAR = &LHYEAR
	AND LHPERD BETWEEN &LHPERD AND &LHPERD
	AND HHTRNO BETWEEN &HHTRNO AND &HHTRNO
	AND LHJNEN BETWEEN &LHJNEN AND &LHJNEN)
	AND LHJRF1 = &LHJRF1
	AND LHUSER = &LHUSER
	AND CRSG04 BETWEEN &CRSG04 AND &CRSG04
	AND CRSG01 = &CRSG012
	AND LHDATE BETWEEN &LHDATE AND &LHDATE 
ORDER BY 
	5, 
	7


Wendy Bunch 
Cost Accountant 
Sensata Technologies
(Formerly Wabash Technologies)
1375 Swan Street 
PO Box 829 
Huntington, IN  46750-0829 
Direct (260) 355-4204 
Main  (260) 355-4100 
Fax    (260) 355-4265 
wbunch@xxxxxxxxxxx

Important Notice: Please note that my email address has changed, kindly send all future emails to wbunch@xxxxxxxxxxx






-----Original Message-----
From: BPCS-L [mailto:bpcs-l-bounces@xxxxxxxxxxxx] On Behalf Of Molyneux, Tom
Sent: Wednesday, December 17, 2014 8:52 AM
To: BPCS ERP System
Subject: Re: [BPCS-L] CEA/General Ledger

Steve,

We are on 8.3.2, so I hope the file names are still the same.

Use iSeries Data Transfer to create an Excel file from the GLH file.

If necessary, use AS400 Query over GLH file to extract/sort/cut down the data you're interested in and create a work file, then drop this into excel via Data transfer.

We also use a piece of software called CoolSpools to create Excel files from iSeries data files, which is really easy to use and produces really good excel files. I have no affiliation to the company, just think it's a really good bit of software which we use extensively.

Cheers

Tom Molyneux

National Oilwell Varco
Completion & Production Solutions
Greengate Way, Greengate, Chadderton
M24 1SA
Direct: +44 161 214 2142
Tom.Molyneux@xxxxxxx | www.nov.com


-----Original Message-----
From: BPCS-L [mailto:bpcs-l-bounces@xxxxxxxxxxxx] On Behalf Of Steve Keller
Sent: 17 December 2014 13:32
To: 'bpcs-l@xxxxxxxxxxxx'
Subject: [BPCS-L] CEA/General Ledger

We are being audited by IRS and I need to get general ledger data at the detail level into excel or csv file.

Does anyone know the best approach in getting this data?

We are on LX version 8.3.4

Thanks,

Steve Keller


CONFIDENTIALITY NOTICE: The information contained in this e-mail communication and any attached documentation may be privileged, confidential or otherwise protected from disclosure and is intended only for the use of the designated recipient(s). It is not intended for transmission to, or receipt by, any unauthorized person. The use, distribution, transmittal or re-transmittal by an unintended recipient of this communication is strictly prohibited without our express approval in writing or by e-mail. If you are not the intended recipient of this e-mail, please delete it from your system without copying it and notify the above sender.
--
This is the BPCS ERP System (BPCS-L) mailing list To post a message email: BPCS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/bpcs-l.


________________________________

Mono Pumps Limited
Registered in England No. 300721.
Registered Office : Stonedale Road, Oldends Lane Industrial Estate, Stonehouse, Gloucestershire, GL10 3RQ
--
This is the BPCS ERP System (BPCS-L) mailing list To post a message email: BPCS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/bpcs-l.