Posting accounts can be configured throughout several modules and setup windows in Dynamics GP. Having them all at one report provides more control over the chart of account in your company.
The idea is to gather all the posting accounts configured in different setup windows in Dynamics GP and present them in a single report as shown below;
The series included in the SQL script are mentioned below, along the originating setup window:
Posting Accounts SQL Script
Tables Included:
· PM00200 | PM Vendor Master File
· RM00101 | RM Customer Master
· IV00101 | Item Master
· CT00102 | IC Cost Item Master
· SY01100 | Posting Account Master
· GL40000 | General Ledger Setup
· FA41300 | Account Group Setup
· TX00201 | Sales/Purchase Tax Master
· IV41100 | Inventory Landed Cost
· GL00100 | Account Master
· GL00105 | Account Index Master
SELECT DISTINCT
SERIES ,
Account AS AccountIndex ,
AccountType ,
ACTNUMST AS AccountNumber ,
ACTDESCR AS AccountDescription
FROM ( SELECT DISTINCT
Series ,
Account ,
AccountType
FROM ( SELECT 'Purchasing - VendorCard' AS Series ,
PMAPINDX AS APAccount ,
PMCSHIDX AS PMCASH ,
PMDAVIDX AS DiscountAvailable ,
PMDTKIDX AS DiscountTaken ,
PMFINIDX AS FinanceCharges ,
PMMSCHIX AS MisCharges ,
PMTAXIDX AS Tax ,
PMWRTIDX AS Writoffs ,
PMPRCHIX AS Purchases ,
ACPURIDX AS Accrued ,
PURPVIDX AS PurchasePrice ,
PMFRTIDX AS Freight ,
PMTDSCIX AS TradeDiscount
FROM dbo.PM00200
) AS PM UNPIVOT ( Account FOR AccountType
IN ( APAccount,
PMCASH,
DiscountAvailable,
DiscountTaken,
FinanceCharges,
MisCharges, Tax,
Writoffs,
Purchases,
Accrued,
PurchasePrice,
Freight,
TradeDiscount ) ) unpvt
UNION ALL
SELECT DISTINCT
Series ,
Account ,
AccountType
FROM ( SELECT 'Sales - CustomerCard' AS Series ,
RMCSHACC AS RMCASH ,
RMARACC AS ARAccount ,
RMSLSACC AS SALES ,
RMIVACC AS Inventory ,
RMCOSACC AS COGS ,
RMTAKACC AS DiscountTaken ,
RMAVACC AS DiscountAvailable ,
RMFCGACC AS FinanceCharges ,
RMWRACC AS Writeoffs ,
RMSORACC AS SalesReturn ,
RMOvrpymtWrtoffAcctIdx OverPayment
FROM dbo.RM00101
) AS RM UNPIVOT ( Account FOR AccountType
IN ( RMCASH,
ARAccount, SALES,
Inventory, COGS,
DiscountTaken,
DiscountAvailable,
FinanceCharges,
Writeoffs,
SalesReturn,
OverPayment ) ) unpvt
UNION ALL
SELECT DISTINCT
Series ,
Account ,
AccountType
FROM
( SELECT 'Inventory - ItemCard' AS Series ,
IVAC.ITEMNMBR ,
IVIVINDX AS Inventory ,
IVIVOFIX AS InventoryOffset ,
IVCOGSIX AS COGS ,
IVSLSIDX AS Sales ,
IVSLDSIX AS SalesDiscount ,
IVSLRNIX AS SalesReturn ,
IVINUSIX AS InUser ,
IVINSVIX AS InService ,
IVDMGIDX AS Damaged ,
IVVARIDX AS Variance ,
DPSHPIDX AS DropShip ,
PURPVIDX AS PurchasePriceVariance ,
UPPVIDX AS UnrealizedPPvariance ,
IVRETIDX AS InventoryReturn ,
ASMVRIDX AS AssemblyVariance ,
IVSCRVIX AS SalesCostRevalue ,
ICIVMFIXI ,
ICIVMVIXI ,
ICIVLVIXI ,
ICIVLABVAROHIX_I ,
ICIVLABVAROHVARIX_I ,
ICIVMEVIXI ,
ICIVMACHEFFVAROHIX_I ,
ICIVMACHEFFVAROHVARIX_I ,
ICIVMUIXI ,
ICIVMATUSAGEVAROHIX_I ,
ICIVMATUSAGEVAROHVARIX_I ,
ICIVSCIXI ,
ICIVWMIXI ,
ICIVWMATOHIXI ,
ICIVWIPMATOHVARIX_I ,
ICIVWLIXI ,
ICIVWLOHIXI ,
ICIVWIPLABOHVARIX_I ,
ICIVWCIXI ,
ICIVWMACHOHIXI ,
ICIVWIPMACHOHVARIX_I ,
ICIVCMIXI ,
ICIVCMATOHIXI ,
ICIVCOGSMATOHVARIX_I ,
ICIVCLIXI ,
ICIVCLABOHIXI ,
ICIVCOGSLABOHVARIX_I ,
ICIVCCIXI ,
ICIVCMACHOHIXI ,
ICIVCOGSMACHOHVARIX_I ,
ICIVILIXI ,
ICIVILABOHIXI ,
ICIVINVLABOHVARIX_I ,
ICIVIMXI ,
ICIVIMACHOHIXI ,
ICIVINVMACHOHVARIX_I ,
ICIVIMATOHIXI ,
ICIVINVMATOHVARIX_I
FROM dbo.IV00101 IVAC
LEFT OUTER JOIN
( SELECT
ITEMNMBR ,
ICIVMFIXI ,
ICIVMVIXI ,
ICIVLVIXI ,
ICIVLABVAROHIX_I ,
ICIVLABVAROHVARIX_I ,
ICIVMEVIXI ,
ICIVMACHEFFVAROHIX_I ,
ICIVMACHEFFVAROHVARIX_I ,
ICIVMUIXI ,
ICIVMATUSAGEVAROHIX_I ,
ICIVMATUSAGEVAROHVARIX_I ,
ICIVSCIXI ,
ICIVWMIXI ,
ICIVWMATOHIXI ,
ICIVWIPMATOHVARIX_I ,
ICIVWLIXI ,
ICIVWLOHIXI ,
ICIVWIPLABOHVARIX_I ,
ICIVWCIXI ,
ICIVWMACHOHIXI ,
ICIVWIPMACHOHVARIX_I ,
ICIVCMIXI ,
ICIVCMATOHIXI ,
ICIVCOGSMATOHVARIX_I ,
ICIVCLIXI ,
ICIVCLABOHIXI ,
ICIVCOGSLABOHVARIX_I ,
ICIVCCIXI ,
ICIVCMACHOHIXI ,
ICIVCOGSMACHOHVARIX_I ,
ICIVILIXI ,
ICIVILABOHIXI ,
ICIVINVLABOHVARIX_I ,
ICIVIMXI ,
ICIVIMACHOHIXI ,
ICIVINVMACHOHVARIX_I ,
ICIVIMATOHIXI ,
ICIVINVMATOHVARIX_I
FROM CT00102
) AS IVCO ON
IVAC.ITEMNMBR = IVCO.ITEMNMBR ) AS IV
UNPIVOT ( Account FOR AccountType
IN
( Inventory,
InventoryOffset,
COGS, Sales,
SalesDiscount,
SalesReturn,
InUser,
InService,
Damaged,
Variance,
DropShip,
PurchasePriceVariance,
UnrealizedPPvariance,
InventoryReturn,
AssemblyVariance,
SalesCostRevalue,
ICIVMFIXI,
ICIVMVIXI,
ICIVLVIXI,
ICIVLABVAROHIX_I,
ICIVLABVAROHVARIX_I,
ICIVMEVIXI,
ICIVMACHEFFVAROHIX_I,
ICIVMACHEFFVAROHVARIX_I,
ICIVMUIXI,
ICIVMATUSAGEVAROHIX_I,
ICIVMATUSAGEVAROHVARIX_I,
ICIVSCIXI,
ICIVWMIXI,
ICIVWMATOHIXI,
ICIVWIPMATOHVARIX_I,
ICIVWLIXI,
ICIVWLOHIXI,
ICIVWIPLABOHVARIX_I,
ICIVWCIXI,
ICIVWMACHOHIXI,
ICIVWIPMACHOHVARIX_I,
ICIVCMIXI,
ICIVCMATOHIXI,
ICIVCOGSMATOHVARIX_I,
ICIVCLIXI,
ICIVCLABOHIXI,
ICIVCOGSLABOHVARIX_I,
ICIVCCIXI,
ICIVCMACHOHIXI,
ICIVCOGSMACHOHVARIX_I,
ICIVILIXI,
ICIVILABOHIXI,
ICIVINVLABOHVARIX_I,
ICIVIMXI,
ICIVIMACHOHIXI,
ICIVINVMACHOHVARIX_I,
ICIVIMATOHIXI,
ICIVINVMATOHVARIX_I ) ) unpvt
UNION ALL
SELECT CASE SERIES
WHEN 1 THEN 'ALL - PostingSetup'
WHEN 2 THEN 'Financial - PostingSetup'
WHEN 3 THEN 'Sales - PostingSetup'
WHEN 4 THEN 'Purchasing - PostingSetup'
WHEN 5 THEN 'Inventory - PostingSetup'
WHEN 7 THEN 'Project - PostingSetup'
END AS SERIES ,
ACTINDX ,
PTGACDSC AS AccountType
FROM SY01100
UNION ALL
SELECT 'Financial - CheckbookCard' AS Series ,
ACTINDX ,
'Cash Account' AS AccountType
FROM dbo.CM00100
UNION ALL
SELECT 'Financial - GLSetup' AS Series ,
RERINDX ,
'Retained Earnings'
FROM dbo.GL40000
UNION ALL
SELECT DISTINCT
Series ,
Account ,
AccountType
FROM
(
SELECT
'Fixed Assets - AccountGroup' AS Series ,
DEPREXPACCTINDX AS DepreciationExpense ,
DEPRRESVACCTINDX AS DepreciationReserve ,
PRIORYRDEPRACCTINDX AS PriorYearDepreciation ,
ASSETCOSTACCTINDX AS AssetCost ,
PROCEEDSACCTINDX AS ProceedsAccount ,
RECGAINLOSSACCTINDX AS RecognizedGainANDloss ,
NONRECGAINLOSSACCTINDX NonRecognizedGainANDloss ,
CLEARINGACCTINDX AS ClearingAccount
FROM dbo.FA41300
) AS RM UNPIVOT ( Account FOR AccountType
IN ( DepreciationExpense,
DepreciationReserve,
PriorYearDepreciation,
AssetCost,
ProceedsAccount,
RecognizedGainANDloss,
NonRecognizedGainANDloss,
ClearingAccount ) ) unpvt
UNION ALL
SELECT DISTINCT
'Financial - TaxSetup' AS Series ,
ACTINDX ,
'Sales/Purchase Tax' AS AccountType
FROM TX00201
UNION ALL
SELECT DISTINCT
Series ,
Account ,
AccountType
FROM
(
SELECT 'Inventory - LandedCostSetup' AS Series ,
ACPURIDX Accrued ,
PURPVIDX PurchasePriceVariance
FROM dbo.IV41100
) AS RM UNPIVOT ( Account FOR AccountType
IN ( Accrued,
PurchasePriceVariance ) ) unpvt
) AS ModuleAccounts
LEFT OUTER JOIN
( SELECT A.ACTINDX ,
B.ACTNUMST ,
A.ACTDESCR
FROM dbo.GL00100 AS A
LEFT OUTER JOIN dbo.GL00105 AS B
ON A.ACTINDX = B.ACTINDX
) AS Accounts
ON ModuleAccounts.ACCOUNT = Accounts.ACTINDX
WHERE ACCOUNT <> 0 AND
ACTNUMST IS NOT NULL
ORDER BY SERIES ,AccountType
Best Regards,
Mahmoud M. AlSaadi
Hi Mahmoud.
ReplyDeleteThank you for your above script.
Appreciate if you could let know if i have 3rd party module like wennsoft for job costing & time track, how can it be included in above script so that we get comprehensive posting account view
I got my already programmed and blanked ATM card to withdraw the maximum of $50,000 daily for a maximum of 20 days. I am so happy about this because i got mine last week and I have used it to get $100,000. Skylink technologies is giving out the card just to help the poor and needy though it is illegal but it is something nice and they're not like other scam pretending to have the blank ATM cards. And no one gets caught when using the card. get yours today Just send them an email On skylinktechnes@yahoo.com whatsapp: +1(213)785-1553
ReplyDelete