1,357
My Finance department required the detail of WIP Cost ending balance from the Dynamics GP.
A Wip Cost ledger activity shows that when we book our cost into the system it directly goes into Wip cost (Deferred cost) ledger activity, and when milestone of revenue completes the cost charged automatically in the system with predefined milestone percentages.
Can anyone tell me which tables should be used to fetch the complete detail?
*This post is locked for comments
Hi Harry,
Many thanks for the query you provided. It is working at my end. However, I can't see the breakup through Project and expenses occurring in each Cost Category of all projects. Could any table from Financials be used to identify each transaction and the Cost Cat with it's project name?
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
----------------------------------
Hi Harry,
WIP cost are collected from Payroll, Purchasing, Inventory and Project Accounting modules.
To brief it a little:
Payroll provides cost of employees salaries
Purchasing gives direct cost of sales
Inventory gives the cost of consumption of inventory items
Project Accounting allocates when we recognize the revenue from Project module, all allocated cost will also be recognized
Hope the above information helps.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (