We basically have two data sources for our General Ledger Data... GLMAST and GLDETAIL
GLMAST contains one row for each GL Account Number and Year. It contains columns for Beginning Balance (begin of year) and transaction totals for each period in separate columns.
ACCT_NBR, YEAR, BEG_BAL, PRD01_AMT, PRD02_AMT.... etc.
GLDETAIL contains a row for each GL transaction. It contains Acct#, Year, Period, Transaction Date, Transaction Number, Transaction Amount, etc..
ACCT_NBR, YEAR, PERIOD, TRANS_NBR, TRANS_AMT
Our GL Report writer handles these well, BUT is older than the AS/400... So I'm trying to make this data available to a report writer (DB2 WebQuery) using SQL views and metadata.
Many GL Reports we currently have select an Account (or range) and a Year/Period (or range), then report a beginning balance, transactions and an ending balance. Some just report summary information. I was hoping to join these two files in a way that would all for efficient reporting. The purpose of the GLMAST was to avoid having to sum all of the detail up to a certain year/period for reporting.
I'm thinking that the period transaction totals in GLMAST should each be a row instead of separate columns within one row. But I'm not sure how (or if) that can be done efficiently.
Or maybe I'm approaching this completely wrong??
Anyone encounter something similar to this?
thx,
Greg
GLMAST contains one row for each GL Account Number and Year. It contains columns for Beginning Balance (begin of year) and transaction totals for each period in separate columns.
ACCT_NBR, YEAR, BEG_BAL, PRD01_AMT, PRD02_AMT.... etc.
GLDETAIL contains a row for each GL transaction. It contains Acct#, Year, Period, Transaction Date, Transaction Number, Transaction Amount, etc..
ACCT_NBR, YEAR, PERIOD, TRANS_NBR, TRANS_AMT
Our GL Report writer handles these well, BUT is older than the AS/400... So I'm trying to make this data available to a report writer (DB2 WebQuery) using SQL views and metadata.
Many GL Reports we currently have select an Account (or range) and a Year/Period (or range), then report a beginning balance, transactions and an ending balance. Some just report summary information. I was hoping to join these two files in a way that would all for efficient reporting. The purpose of the GLMAST was to avoid having to sum all of the detail up to a certain year/period for reporting.
I'm thinking that the period transaction totals in GLMAST should each be a row instead of separate columns within one row. But I'm not sure how (or if) that can be done efficiently.
Or maybe I'm approaching this completely wrong??
Anyone encounter something similar to this?
thx,
Greg
Comment