Fahad,
That's a tall order. The data for each of these transactions is maintained in tables related to their own module. My first instinct is to ask about your chart of accounts design to determine if you can use GL reporting to capture WIP data. It is possible to pull data from the GL20000 and GL30000 tables with a Union Statement and restrict it to just the accounts you desire using ACTINDX values of the various WIP accounts in order to gather detailed GL data on WIP transactions. The query would look something like this:
--------------------------
USE COMPANYDB
Declare @ReportStartDate Char(10)
Declare @ReportEndDate Char(10)
Set @ReportStartDate = '2012-01-01' --Enter Report Start Date
Set @ReportEndDate = '2012-12-31' --Enter Report End Date
Select
'OPEN' 'STATUS'
rtrim(GLHIST.JRNENTRY) JE_Number,
rtrim(GLHIST.REFRENCE) JE_Description,
rtrim(convert(Char(10),GLHIST.TRXDATE,101)) Post_Date,
rtrim(convert(Char(10),GLHIST.ORPSTDDT,101)) Entry_Date,
rtrim(GLHIST.SOURCDOC) GL_Source_Code,
rtrim(GLHIST.LASTUSER) 'User',
rtrim(GLHIST.USWHPSTD) PostingUser,
rtrim(GLHIST.TRXSORCE) Subledger_Source_Code,
rtrim(GLHIST.ORMSTRNM) Master_Name,
rtrim(GLHIST.ORMSTRID) Master_Doc_Num,
GLHIST.CRDTAMNT Credit_Amt,
GLHIST.DEBITAMT Debit_Amt,
rtrim(COA.ACTNUMST) Acct_Number
from GL20000 GLHIST (nolock)
inner join GL00105 COA (nolock)
on COA.ACTINDX = GLHIST.ACTINDX
Where GLHIST.TRXDATE Between @ReportStartDate and @ReportEndDate
and ACTINDX IN (111,115,117) --Enter Account Indexes for WIP Accounts Here
Union All
Select
'HISTORY' 'Status'
rtrim(GLHIST.JRNENTRY) JE_Number,
rtrim(GLHIST.REFRENCE) JE_Description,
rtrim(convert(Char(10),GLHIST.TRXDATE,101)) Post_Date,
rtrim(convert(Char(10),GLHIST.ORPSTDDT,101)) Entry_Date,
rtrim(GLHIST.SOURCDOC) GL_Source_Code,
rtrim(GLHIST.LASTUSER) 'User',
rtrim(GLHIST.USWHPSTD) PostingUser,
rtrim(GLHIST.TRXSORCE) Subledger_Source_Code,
rtrim(GLHIST.ORMSTRNM) Master_Name,
rtrim(GLHIST.ORMSTRID) Master_Doc_Num,
GLHIST.CRDTAMNT Credit_Amt,
GLHIST.DEBITAMT Debit_Amt,
rtrim(COA.ACTNUMST) Acct_Number
from GL30000 GLHIST (nolock)
inner join GL00105 COA (nolock)
on COA.ACTINDX = GLHIST.ACTINDX
Where GLHIST.TRXDATE Between @ReportStartDate and @ReportEndDate
and ACTINDX IN (111,115,117) --Enter Account Indexes for WIP Accounts Here
----------------------------------
