SQL scripts for Project Accounting: Microsoft Great Plains series ? overview for developer

Microsoft Business Solutions Great Plains has Project Accounting module where you can budget the project, assign it to customer contract and then log expenses, timesheets, inventory spending, and finally bill the customer against the project or contract. Microsoft Great Plains Project Accounting does excellent job, but there are cases when you need developer or MS SQL DB Admin touches.

? The first script transfers salesperson and sales territory from Customer master file to Project master table

update PA01101 set SLPRSNID=b.SLPRSNID, SALSTERR=b.SALSTERR from PA01101 a join RM00101 b on a.CUSTNMBR=b.CUSTNMBR where a.SLPRSNIDb.SLPRSNID or a.SALSTERRb.SALSTERR

? The second scripts unlocks Project Accounting user, this rare and funny one ? really hard to get it from Microsoft Business Solutions Tech Support site:

delete PA000001 where userid='put user id here'

? The third script is really advanced one ? it is for users trying to place billing address with contact info on the PA invoice form at the bottom section ? address comes from Contract ? not from the project

/*
Updates Primary Bill To Address Code for Billing invoice to be the one from Contract Master table, instead of Primary Billing Address Code from Customer
*/

update PA13100 set PRBTADCD=c.PRBTADCD from PA13100 a join PA13200 b on a.PADocnumber20=b.PADocnumber20 join PA01201 c on b.PAPROJNUMBER=c.PAPROJNUMBER

/* update PA13100 set PRBTADCD=c.PRBTADCD from PA13100 a join PA13200 b on a.PADocnumber20=b.PADocnumber20 join PA01101 c on b.PACONTNUMBER=c.PACONTNUMBER */ /* Fill user defined 1 on the Billing HDR to contact person from the appropriate customer master address rec. */ update PA13100 set PAUD1 = left(b.CNTCPRSN,20) from PA13100 a join RM00102 b on a.CUSTNMBR = b.CUSTNMBR and a.PRBTADCD = b.ADRSCODE

Good luck with customization and support, if you have issues or concerns ? we are here to help! If you want us to do the job - give us a call 1-630-961-5918 or 1-866-528-0577! help@albaspectrum.com

Andrew is a Great Plains specialist in Alba Spectrum Technologies ( http://www.albaspectrum.com ) ? USA nationwide Great Plains, Microsoft CRM customization company, serving clients in Mexico-city, Chicago, Houston, Atlanta, Phoenix, New York, Los Angeles, San Francisco, San Diego, Miami, New Orleans, Toronto, Montreal and having locations in multiple states and internationally

In The News:

pen paper and inkwell


cat break through


Microsoft Great Plains Jewelry ? Implementation & Customization Overview

Each Industry and market niche has business specific and unique... Read More

Crystal Reports - Geterogeneus databases: SQL Server, Oracle, Unidata, Btrieve ? Tips For Developer

Crystal Reports is the most flexible tool on the market... Read More

Internet Faxing Service Review

The Internet is reshaping every form of communications medium, and... Read More

Do You Know These Facts About Spyware ?

Imagine something that follows you home and sets itself up... Read More

What You Should Know About Installing Screensavers

Do you remember that frustrating feeling when you find an... Read More

ERP Software Financing: the Future? ? Overview for Company Owner

In our case ? we serve Microsoft Business Solutions ERP... Read More

Corporate ERP Selection: Microsoft Great Plains

In our opinion, traditional approach when you select ERP/MRP system... Read More

IT Department Skills to Support Microsoft Great Plains and Microsoft CRM

Microsoft Great Plains as ERP and Microsoft CRM as... Read More

Fundraising Software ? How Can That Help Me?

Fundraising software lets you connect with donors in a way... Read More

SQL: Querying Microsoft Great Plains ? Overview for Database Administrator/Developer

Looks like Microsoft Great Plains becomes more... Read More

Microsoft Great Plains Inventory Control ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization

Microsoft CRM is winning market share step-by-step from such the... Read More

Crystal Reports For Microsoft Navision - Overview For Programmer/IT Specialist

Let us give you - developer some hints in the... Read More

HSphere Control Panel Tips and Tricks - Power At Your Fingertips: Part 1

The first topic we are going to discuss... Read More

Know Linux

Linux essentials:It's free for download but you have to pay... Read More

Easy Guide to RAID Recovery

What is RAID RECOVERY?RAID stands for Redundant Array of Inexpensive... Read More

Microsoft CRM Custom Design & Development: SDK, C#, SQL, Exchange, Integration, Crystal Reports

Microsoft CRM is new player on the CRM software... Read More

A Simple Computer Software Definition

What is Software?Software is a set of instruction written to... Read More

Microsoft Navision Customization Upgrade ? Tips For Programmer/IT Specialist

Currently Microsoft Business Solutions is on the way of creating... Read More

The Software 2005 Conference - A Review

The Software 2005 conference is now a wrap. This conference,... Read More

Spyware Statistics -- Whats New in May 2005?

Although statistics often is blamed for various deadly sins --... Read More

Information Products: A Business Owners Best Friend

We live in a post-industrial age where information is the... Read More

Microsoft Great Plains: If You are Orphan Client ? What to Do and FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software eEnterprise,... Read More

Begun, The Browser Wars Have

As Mozilla Firefox nears 10% market share, with well over... Read More

Imagine if Everyone Working In Your Office Was In Synch?

Microsoft-Outlook is a pretty amazing program. So much more than... Read More

Builders Beware

Which Type of Shop Can Rely On A Home Built... Read More

Great Plains Dexterity Development: FAQ

Microsoft Business Solutions Great Plains is Dexterity-written application and currently... Read More

Basic Tips and tricks for Windows XP

Running Applications in Compatibility Mode With Windows XP, you can... Read More

Increase Office Efficiency With One Simple Tool

When you need a phone number, you do a quick... Read More

Industry Experts Answer the One Million-dollar Question - Why Genie Backup Manager?

Many reasons made GBM a unanimous choice for experts, one... Read More

Performance Tuning of a Daffodil DB / One$DB -JDBC Application

This article illustrates the best practices to improve the performance... Read More

How To Avoid Getting Hooked By Pfishing

"Pfishing", sometimes spelled "Phishing", is a word that's used to... Read More

Microsoft Great Plains Project Accounting ? Overview For IT Director/Controller

Microsoft Business Solutions is now in process of creating so... Read More