Re: Account Inquiry, Period Detail, Query -- BPCS-L

Jesus,

For your account balances, you will have to create a join between GSB 
(Balance File) and GCR (Segment Cross Reference File).  I like to create a 
view called GSBJ01 on the 400 using SQL as follows:

CREATE VIEW BPCSF/GSBJ01
AS SELECT GSB.SBLDGR, GSB.SBBOOK, GSB.SBYEAR, GSB.SBPERD,
GSB.SBIAN, GSB.SBDRAM, GSB.SBCRAM,
GCR.CRIAN, GCR.CRSG01, GCR.CRSG02, GCR.CRSG03, GCR.CRSG04,
GCR.CRSG05, GCR.CRSG06, GCR.CRSG07, GCR.CRDESC FROM BPCSF/GSB
JOIN BPCSF/GCR
ON SBIAN=CRIAN

As Far as the detail is concerned, you will need to join GHH, GLH, and GCR.  
Optionally, you can also join GLA.  Again I like to use a view called GLHJ05 
as follows:

CREATE VIEW BPCSF/GLHJ05
AS SELECT GLH.LHLDGR, GLH.LHBOOK, GLH.LHYEAR, GLH.LHPERD,
GLH.LHJNEN, GLH.LHJNLN, GLH.LHIAN, GLH.LHDRAM, GLH.LHCRAM,
GLH.LHCRAT, GLH.LHDRAT, GLH.LHLSTS, GLH.LHSTAT, GLH.LHLDES,
GLH.LHJRF1, GLH.LHJRF2, GLH.LHDREF, GLH.LHDDAT, GLH.LHREAS,
GLA.LALDGR, GLA.LABOOK, GLA.LAYEAR, GLA.LAPERD, GLA.LAJNEN,
GLA.LAJNLN, GLA.LAANB1, GLA.LAANB2, GLA.LAANB3, GLA.LAANB4,
GLA.LAANB5, GLA.LAANB6, GCR.CRIAN, GCR.CRSG01, GCR.CRSG02,
GCR.CRSG03, GCR.CRSG04, GCR.CRSG05, GCR.CRSG06, GCR.CRSG07,
GCR.CRDESC,
GHH.HHLDGR, GHH.HHBOOK, GHH.HHYEAR, GHH.HHPERD, GHH.HHJNEN,
GHH.HHDATE, GHH.HHJDAT, GHH.HHTRNO, GHH.HHEVNT, GHH.HHUSER
FROM BPCSF/GHH JOIN BPCSF/GLH
ON HHLDGR=LHLDGR AND HHBOOK=LHBOOK AND HHYEAR=LHYEAR AND
HHPERD=LHPERD AND HHJNEN=LHJNEN
LEFT OUTER JOIN BPCSF/GLA
ON LHLDGR = LALDGR AND LHBOOK = LABOOK AND LHYEAR = LAYEAR AND
LHPERD = LAPERD AND LHJNEN = LAJNEN AND LHJNLN = LAJNLN
JOIN BPCSF/GCR ON LHIAN=CRIAN

You may need to adjust the above statments if you are using more or less 
than 7 segments or if your library is not BPCSF.  After you've done this, it 
is a simple matter of querying on GSBJ01 and GLHJ05 and you have everything 
you need in one file.

If you need any help with this, please let me know.

___ //// _________________________________________________
   |--00               Paul Holstein
   C   ^               E-Mail: pholstein@iworksoftware.com
___ \ ~/ _________________________________________________


----Original Message Follows----
From: Jesus Maynez <bpcsmaynez@yahoo.com>
Reply-To: BPCS-L@midrange.com
To: BPCS-L@midrange.com
Subject: Account Inquiry, Period Detail, Query


Hello Everybody

I'm struggling pretty bad trying to make a query to
get the same information that appears in the Period
Detail Windows from the Account Inquiry.
By mistake, we did not summarized the Journal Entries
for this year, then we have thousands of lines for the
first two weeks of January. When we try to get this
information through the Account Inquiry is taking
forever. Then, I tough on create a query and get the
information directly from the files.
I found out, Journal Entries are in the GLH file,
however I do not how to make reference to the Event
Number and Account Number. This files has a IAN
Internal Account Number, I'm pretty sure this field
can be used to link the Event Number and Account
Number, but I dont know in what other files I have to
look up.

I hope you can help me out.

Thanks
Jesus Maynez
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com
+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---