Remove Old Install of Project Accounting

(0) Share

Report

Posted on by Jim Lines

Greetings,
I’m attempting to install Project Accounting in my Dev environment but am receiving an upgrade error message when launching Dynamics Utilities. My situation is that I’ve got a test (TST) database and I have restored with production data (this is not a new process for us). I’m getting the message below when I run utilities and cannot go further.

My thought is that PA was installed a long, long time ago and never used. Since then we’ve done a few upgrades and are now on GP 2013 SP2 I’m thinking there is some residual of PA that is preventing me from installing.

I queried DU000020 for PRODID = 0 (GP) and see my all of my company databases with the correct versions. If I query that table for PRODID = 258 (Project Accounting) I see existing database but they are version 10. Also, a few of our newly created companies are not listed in that table. So, I have a pretty good idea of what is wrong, but need help figuring out how to fix it.

Any advice is appreciated! Thanks.

Jim

*This post is locked for comments

I have the same question (0)

All responses (20)

Answers (0)

Sort by

Nathan Hayden 55 on at

Like
a
(0)

Report
Copy link

Link copied!

If you're okay with just initializing PA again, start by updating the db_verOldMajor, db_verOldMinor, and db_verOldBuild values in DB_Upgrade to 0 for PRODID 258. You'll also want to delete rows in DU000020 with a PRODID of 258. This will tell GP that PA is not installed.

I suspect that there's not a record for PRODID 258 for your TST company, and if that's the case, the next step you'll need to perform is to delete the PA10702 table in your TST database.

Was this reply helpful? Yes No
Jim Lines 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks Nathan. I updated the db_upgrade and set the versions to 0, but that did not seem to work. What did was completely removing the prod 258 records from db_update.

------

I was finally able to get rid of any PA traces and get it re-installed. The key seemed to be removing produce 258 from dynamics..DB_Upgrade. I did several other things like remove from DU000010, DU000020, & DU000030. There were some other scripts that I ran to remove stored procedures, views and tables. Send me a message if you need those. Thanks.

Was this reply helpful? Yes No
Derek Albaugh Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Jim,

Apologies for being late on this response.

If you were looking to reinstall/re-initialize Project Accounting for Dynamics GP, along with removing PRODID 258 in the DB_Upgrade, DU000020 and DU000030 tables, you also need to remove these sub-feature tables:

>>From the DYNAMICS/system database: PDK_Version and PA40401

>>Against each and every company database mentioned in the SY01500 table: PA10702

Why we need to do this is because when Utilities doesn't see, in this example, PRODID 258 in the above system tables, but it is installed, it then looks for these specific sub-feature tables in the DYNAMICS and/or company databases. If it finds any of these, it thinks that Project Accounting is installed and will want to upgrade it.

Once we remove these sub-feature tables, Utilities will allow you to re-install Project Accounting anew at the version that you're currently or upgrading to for Dynamics GP.

***NOTE: When we re-initialize a feature such as this, we will lose any data in the tables for this feature, so be aware of that.

Thank you,

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

Derek,

I am doing the same thing as Jim, and everything works fine until the Utilities try to update Stored Procedures. At that point I get an error Invalid Column Name 'Project Number For Drillback' in 2 of the stored procedures. The utilities crash out and, of course, I can't log into GP because the upgrade failed. I'm a Dex developer so I looked in the GP2013 PA258 extract dictionary and there is not a field by that name anywhere in the dictionary. I know these stored procedures are coming from the upgrade and their names are:

seePATopProjectManagerProfitability_Create

seePATopZeroOrNegativeProfitProjects_Create

Your help is sorely needed.

Thanks, Jeff

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

Dave Musgrave said you are the man for this. :-)

Was this reply helpful? Yes No
Derek Albaugh Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Jeff,

Tables in Dynamics GP will never have a column name with spaces in them such as 'Project Number For Drillback', but SQL Views will.

I believe the object you're looking for, that has this column in it for Project Accounting, is the PAProjects view, that is in the company database(s).

If you run this script against the company database failing on this error, you should see this column first to the last, next to a 'Salesperson ID for Drillback' column.

Drop and re-create this view to have this column, and then the upgrade should go through successfully.

Thanks

Was this reply helpful? Yes No
Derek Albaugh Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

The script is:

Select * from PAProjects

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

Derek,

Thanks for the help, but this view must have been created programmatically, because it does not exist as a virtual table in the GP 2013 PA258 dictionary. So, can you provide me with SQL scripting that will properly create all the PA views for GP2013? Here's my situation...

We're an ISV partner. We started our dev work on GP 10.0 using TWO as our development database, and over the years have added tons of data that we can't afford to lose. However, I did not have Project Accounting in my set file. Along the way, we upgraded TWO to the various intervening versions as prescribed. Now, on GP 2013, I want to add Project Accounting to my .set file. But when I do, GP detects a version mismatch between my dictionary and the database as it pertains to PA. When I try to run Utilities to upgrade the TWO database, the duinstall.log file indicates that it cannot upgrade PA from version 10.0.

My developer colleague and friend Dave Musgrave directed me to this thread and said you could help. So, I implemented your prescribed guidance from this thread and Utilities run fine up until the stored procedures step and then it crashes with these errors. And, it may crash with other errors later in the process, I don't know. But, I need to onboard PA using our development database, so any thoughts or suggestions would be appreciated.

Thanks,

Jeff

P.S. As a last resort, I hacked the DB_Upgrade, DU000020, and DU000030 tables by setting the version to 12.0.1454 just to bypass the version check. I then used SQL maintenance in GP to drop and recreate all the PA tables at the GP2013 level. However, the views are a problem since they can't be recreated in GP.

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

I suppose I could do a clean install of GP2013 and create the TWO database, and then script out the views and then drop and create them in our dev version of TWO. But, if you have them, it would make things easier.

Was this reply helpful? Yes No
Derek Albaugh Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Jeff,

You can try this script:

/****** Object: View [dbo].[PAProjects] Script Date: 2016-09-09 11:40:37 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE VIEW [dbo].[PAProjects] AS select rtrim(['PA Project Master File'].[CUSTNMBR]) as 'Customer Number', rtrim(['PA Project Master File'].[PACONTNUMBER]) as 'Contract Number', rtrim(['PA Project Master File'].[PAcontid]) as 'Contract ID', rtrim(['PA Project Master File'].[PAprojid]) as 'Project ID', rtrim(['PA Project Master File'].[PAprojname]) as 'Project Name', rtrim(['PA Project Master File'].[PAPROJNUMBER]) as 'Project Number', rtrim(['PA Project Master File'].[PAprjclsid]) as 'Project Class ID', 'Project Type' = dbo.PA_FUNC_Project_Type(['PA Project Master File'].[PAProjectType]), 'Accounting Method' = dbo.PA_FUNC_Accounting_Method(['PA Project Master File'].[PAAcctgMethod]), 'Status' = dbo.PA_FUNC_Status(['PA Project Master File'].[PASTAT]), rtrim(['PA Project Master File'].[PApurordnum]) as 'Purchase Order No.', ['PA Project Master File'].[PABBeginDate] as 'Baseline Begin Date', ['PA Project Master File'].[PABEndDate] as 'Baseline End Date', ['PA Project Master File'].[PABQuantity] as 'Baseline Quantity', ['PA Project Master File'].[PABTotalCost] as 'Baseline Total Cost', ['PA Project Master File'].[PABProfit] as 'Baseline Profit', ['PA Project Master File'].[PABBillings] as 'Baseline Billings', ['PA Project Master File'].[PABTaxPaidAmt] as 'Baseline Tax Paid Amount', ['PA Project Master File'].[PABTaxChargedAmt] as 'Baseline Tax Charged Amount', ['PA Project Master File'].[PABaselineOvhdCost] as 'Baseline Overhead Cost', ['PA Project Master File'].[PAACTUALBEGDATE] as 'Actual Begin Date', ['PA Project Master File'].[PA_Actual_End_Date] as 'Actual End Date', ['PA Project Master File'].[PAFBeginDate] as 'Forecast Begin Date', ['PA Project Master File'].[PAFEndDate] as 'Forecast End Date', ['PA Project Master File'].[PAFQuantity] as 'Forecast Quantity', ['PA Project Master File'].[PAFTotalCost] as 'Forecast Total Cost', ['PA Project Master File'].[PAFProfit] as 'Forecast Profit', ['PA Project Master File'].[PAFBillings] as 'Forecast Billings', ['PA Project Master File'].[PAFTaxPaidAmt] as 'Forecast Tax Paid Amount', ['PA Project Master File'].[PAFTaxChargedAmt] as 'Forecast Tax Charged Amount', ['PA Project Master File'].[PAForecastOvhdCost] as 'Forecast Overhead Cost', 'Close to Project Costs' = dbo.DYN_FUNC_Boolean_All(['PA Project Master File'].[PAcloseProjcosts]), 'Close to Billings' = dbo.DYN_FUNC_Boolean_All(['PA Project Master File'].[PAclosetobillings]), rtrim(['PA Project Master File'].[PADepartment]) as 'Department', rtrim(['PA Project Master File'].[PAEstimatorID]) as 'Estimator ID', rtrim(['PA Project Master File'].[PAprojmngrid]) as 'Project Manager ID', rtrim(['PA Project Master File'].[PABusMgrID]) as 'Business Manager ID', rtrim(['PA Project Master File'].[LOCATNID]) as 'Location ID', rtrim(['PA Project Master File'].[SLPRSNID]) as 'Salesperson ID', rtrim(['PA Project Master File'].[SALSTERR]) as 'Sales Territory', ['PA Project Master File'].[COMPRCNT]/100.00 as 'Commission Percent', rtrim(['PA Project Master File'].[CNTCPRSN]) as 'Contact Person', rtrim(['PA Project Master File'].[PRBTADCD]) as 'Primary Billto Address Code', rtrim(['PA Project Master File'].[PALabor_Rate_Table_ID]) as 'Labor Rate Table ID', 'Labor Rate Table Type' = dbo.PA_FUNC_Labor_Rate_Table_Type(['PA Project Master File'].[PALabor_RateTable_Type]), 'Labor Rate Table Accept' = dbo.DYN_FUNC_Boolean_All(['PA Project Master File'].[PALabor_Rate_Table_Acc]), rtrim(['PA Project Master File'].[PAEquip_Rate_Table_ID]) as 'Equip Rate Table ID', 'Equip Rate Table Accept' = dbo.DYN_FUNC_Boolean_All(['PA Project Master File'].[PAEquip_Rate_Table_Acc]), 'Billing Type' = dbo.PA_FUNC_Billing_Type(['PA Project Master File'].[PAbllngtype]), ['PA Project Master File'].[PAService_Fee_Amount] as 'Service Fee Amount', ['PA Project Master File'].[PAProject_Fee_Amount] as 'Project Fee Amount', ['PA Project Master File'].[PARetainer_Fee_Amount] as 'Retainer Fee Amount', ['PA Project Master File'].[PARetentionFeeAmount] as 'Retention Fee Amount', ['PA Project Master File'].[PAProject_Amount] as 'Project Amount', ['PA Project Master File'].[ACCTAMNT] as 'Account Amount', rtrim(['PA Project Master File'].[PABILLFORMAT]) as 'Bill Format', ['PA Project Master File'].[DSCPCTAM]/100.00 as 'Discount Percent', ['PA Project Master File'].[PA_Retention_Percent] as 'Retention Percent', rtrim(['PA Project Master File'].[PAUD1Proj]) as 'User Defined 1', rtrim(['PA Project Master File'].[PAUD2_Proj]) as 'User Defined 2', ['PA Project Master File'].[PAUnpostedQty] as 'Unposted Quantity', ['PA Project Master File'].[PAUnpostedTotalCostN] as 'Unposted Total Cost', ['PA Project Master File'].[PAUnposted_Overhead] as 'Unposted Overhead', ['PA Project Master File'].[PAUnpostedProfitN] as 'Unposted Profit', ['PA Project Master File'].[PAUnposted_Tax_Amount] as 'Unposted Tax Amount', ['PA Project Master File'].[PAUnpostAccrRevN] as 'Unposted Accrued Revenues', ['PA Project Master File'].[PAUnpostedRecogRevN] as 'Unposted Recognized Revenue', ['PA Project Master File'].[PAUnpostedCommitedQty] as 'Unposted Committed Qty', ['PA Project Master File'].[PAUnpostedCommitedCost] as 'Unposted Committed Cost', ['PA Project Master File'].[PAUnpostedCommitedTaxAmt] as 'Unposted Committed Tax Amount', ['PA Project Master File'].[PAUnposted_Project_Fee] as 'Unposted Project Fee Amount', ['PA Project Master File'].[PAUnposted_Retainer_Fee] as 'Unposted Retainer Fee Amount', ['PA Project Master File'].[PAUnposted_Service_Fee] as 'Unposted Service Fee Amount', ['PA Project Master File'].[PAUNPOSTRETAMT] as 'Unposted Retention Amount', ['PA Project Master File'].[PAUNPOSTBIEEAMOUNT] as 'Unposted BIEE Amount', ['PA Project Master File'].[PAUNPEIEBAMOUNT] as 'Unposted EIEB Amount', ['PA Project Master File'].[PA_Unposted_Billed_Reten] as 'Unposted Billed Retention', ['PA Project Master File'].[PAPostedQty] as 'Actual Qty', ['PA Project Master File'].[PAPostedTotalCostN] as 'Actual Total Cost', ['PA Project Master File'].[PAPostedProfitN] as 'Actual Profit', ['PA Project Master File'].[PAPosted_Tax_Amount] as 'Actual Tax Amount', ['PA Project Master File'].[PAPosted_Accr_RevN] as 'Actual Accrued Revenues', ['PA Project Master File'].[PAPostRecogRevN] as 'Actual Recognized Revenue', ['PA Project Master File'].[PAPostedCommitedQty] as 'Actual Committed Qty', ['PA Project Master File'].[PAPostedCommitedCost] as 'Actual Committed Cost', ['PA Project Master File'].[PAPostedCommitedTaxAmt] as 'Actual Committed Tax Amount', ['PA Project Master File'].[PAPosted_Project_Fee] as 'Actual Project Fee Amount', ['PA Project Master File'].[PAPosted_Retainer_Fee] as 'Actual Retainer Fee Amount', ['PA Project Master File'].[PAPosted_Service_Fee] as 'Actual Service Fee Amount', ['PA Project Master File'].[PAPOSTRETAMT] as 'Actual Retention Amount', ['PA Project Master File'].[PAPOSBIEEAMOUNT] as 'Actual BIEE Amount', ['PA Project Master File'].[PAPOSEIEBAMOUNT] as 'Actual EIEB Amount', ['PA Project Master File'].[PA_Actual_Billed_Retenti] as 'Actual Billed Retention', ['PA Project Master File'].[PAWrite_UpDown_Amount] as 'Write Up/Down Amount', ['PA Project Master File'].[PABilled_QtyN] as 'Billed Qty', ['PA Project Master File'].[PABilled_Cost] as 'Billed Cost', ['PA Project Master File'].[PABilled_Accrued_Revenu] as 'Billed Accrued Revenues', ['PA Project Master File'].[PACostPcntCompleted] as 'Cost Percent Completed', ['PA Project Master File'].[PAQuantityPcntCompleted] as 'Quantity Percent Completed', ['PA Project Master File'].[PA_Receipts_Amount] as 'Receipts Amount', ['PA Project Master File'].[PA_Actual_Receipts_Amoun] as 'Actual Receipts Amount', ['PA Project Master File'].[PA_Earnings] as 'Earnings', ['PA Project Master File'].[PA_Cost_of_Earnings] as 'Cost of Earnings', ['PA Project Master File'].[PAUnpostBillN] as 'Unposted Billings', ['PA Project Master File'].[PAUnpostDiscDolAmtN] as 'Unposted Discount Amount', ['PA Project Master File'].[PAUnposted_Sales_Tax_Am] as 'Unposted Sales Tax Amount', ['PA Project Master File'].[PAPostedBillingsN] as 'Actual Billings', ['PA Project Master File'].[PAPostedDiscDolAmtN] as 'Actual Discount Amount', ['PA Project Master File'].[PAPosted_Sales_Tax_Amou] as 'Actual Sales Tax Amount', ['PA Project Master File'].[PABCWPAMT] as 'BCWP Amount', ['PA Project Master File'].[PABCWSAMT] as 'BCWS Amount', ['PA Project Master File'].[PAACWPAMT] as 'ACWP Amount', ['PA Project Master File'].[NOTEINDX] as 'Note Index', ['PA Project Master File'].[PAPosted_Overhead] as 'Actual Overhead', ['PA Project Master File'].[PAApproved_Accrued_Reve] as 'Approved Accrued revenues', ['PA Project Master File'].[PAApproved_Cost] as 'Approved Cost', ['PA Project Master File'].[PAApproved_Quantity] as 'Approved Quantity', ['PA Project Master File'].[WROFAMNT] as 'Write Off Amount', ['PA Project Master File'].[ActualWriteOffAmount] as 'Actual Write Off Amount', ['PA Project Master File'].[DISTKNAM] as 'Discount Taken Amount', ['PA Project Master File'].[ActualDiscTakenAmount] as 'Actual Discount Taken Amount', ['PA Project Master File'].[PACommitted_Costs] as 'Committed Costs', ['PA Project Master File'].[PACommitted_Qty] as 'Committed Quantity', ['PA Project Master File'].[PAPOCost] as 'PO Cost', ['PA Project Master File'].[PAPOQty] as 'PO Quantity', ['PA Project Master File'].[PAPOPostedCost] as 'PO Actual Cost', ['PA Project Master File'].[PAPOPostedQty] as 'PO Actual Qty', ['PA Project Master File'].[PAtaxpaidamt] as 'Tax Paid Amount', ['PA Project Master File'].[PAPostedTaxPaidN] as 'Actual Tax Paid', ['PA Project Master File'].[PApretainage] as 'Actual Retainage', ['PA Project Master File'].[PAunpretainage] as 'Unposted Retainage', ['PA Project Master File'].[PA_Write_Off_Tax_Amount] as 'Write Off Tax Amount', ['PA Project Master File'].[PAActualWOTaxAmt] as 'Actual Write Off Tax Amount', ['PA Project Master File'].[PA_Terms_Taken_Tax_Amt] as 'Terms Taken Tax Amount', ['PA Project Master File'].[PAActualTermsTakenTax] as 'Actual Terms Taken Tax Amount', rtrim(['PA Project Master File'].[WRKRCOMP]) as 'Workers Comp', ['PA Project Master File'].[PAUnpostedLossAmount] as 'Unposted Loss Amount', ['PA Project Master File'].[PAActualLossAmount] as 'Actual Loss Amount', ['PA Project Master File'].[PAPosted_Earnings] as 'Actual Earnings', ['PA Project Master File'].[PAActualCostofEarnings] as 'Actual Cost of Earnings', 'Customer Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=THELEY102&Cmp=TWO&Prod=0' +dbo.dgppCustomerID(1,['PA Project Master File'].[CUSTNMBR] ), 'Contract Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=THELEY102&Cmp=TWO&Prod=258' +dbo.dgppContractNumber(1,['PA Project Master File'].[CUSTNMBR],['PA Project Master File'].[PACONTNUMBER] ), 'Project Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=THELEY102&Cmp=TWO&Prod=258' +dbo.dgppProjectNumber(1,['PA Project Master File'].[CUSTNMBR],['PA Project Master File'].[PACONTNUMBER],['PA Project Master File'].[PAPROJNUMBER],['PA Project Master File'].[PAprojid] ), 'Salesperson ID For Drillback' = 'dgpp://DGPB/?Db=&Srv=THELEY102&Cmp=TWO&Prod=0' +dbo.dgppSalespersonID(1,['PA Project Master File'].[SLPRSNID],2 ) from [PA01201] as ['PA Project Master File'] with (NOLOCK)

GO

As for the version mismatch for Project Accounting between GP 10.0 and GP 2013, usually when we see that, we have two options, upgrade normally meaning you may have to restore databases back to a prior version to upgrade, in this case, Project Accounting, to the middle versions up to Dynamics GP 2013.

The other option is to re-initialize Project Accounting like it's a new feature, but any PA data would be lost.

Those would be the only two options we would support. I'm curious as to how we're getting PA up to the GP 2013 version.......

Thanks

Was this reply helpful? Yes No