Wednesday, February 25, 2009

Project Accounting Timesheet Costs

Recently Steve and I have been working on a customization project to address one of my ongoing "sticking points" when using project accounting timesheets for salaried employees. It is one of those issues that bugs me enough that I routinely have to turn to others...other project accounting consultants, trainers, MS support folks, etc to give myself a reality check :)

So what is the issue? Well, let's work through the following example...
  • Employee 1 is a salaried employee making $1000 per weekly pay period, making their hourly rate $25/hr based on 2080 hrs/year
  • Let's assume that the pay period is the same as the timesheet report period (weekly)
  • Employee records 45 hrs on their timesheet for the week

When the timesheet posts, it will post 45 hrs of cost to the project at $25/hr -- $1125. This $1125 also posts in the general ledger:

  • $1125 Debit to Work in Progress or Cost of Goods Sold/Expense as appropriate
  • $1125 Credit to Contra Account for Costs

The intention of this is that it records the project-related expense, and is then offset with the salary expense when it is recorded via payroll or a journal entry. Of course, do we all see the issue with the example above? $1125 of cost is recorded, although we are only going to pay the fabulous Employee A $1000/pay period. So we are effectively overstating the cost on the project and in the general ledger.

The customization we are working on resets the unit cost on the timesheet. So, in the example above it would do the following:

  • $1000 pay/45 total hours = $22.22
  • Reset unit cost per line on timesheet to $22.22
  • Reset distributions so that $1000 is debited to WIP (or COGS/Exp) and credited to Contra account

It should work, and will save clients a lot of frustration. I just laugh at myself that I didn't think to rope Steve in to it sooner. Now let's not even talk about what would happen if the pay period and timesheet reporting period were not the same!

Forecaster Sections and Accounts

In Forecaster, you can design Lines (also referred to as Line Sets) to represent the accounts that should be displayed in an input set or in on a report. These lines can then be assigned to cost centers for input sets, so when a particular department (e.g., Accounting) accesses their input set to enter or review their budget, only the accounts applicable to the selected department are displayed. This concept of assigning lines to specific cost centers is referred to as an "override" on the input set.

The applicable paths we have discussed so far in Forecaster:
  • Build>>Lines
  • Build>>Reports or Report Worksheet
  • Setup>>Budgets>>Input Set>>Overrides
Additionally, you can set up Sections (Setup>>Budgets>>Sections). Sections are just that..section headers, and can be added to lines for input sets or reports, to label a particular section and to help with the presentation of the data.

So...why am I telling you all this? Well, last week I had a client with a strangest issue. In their input set, a particular account was showing with the wrong label. We deleted the account, re-added it. Same result. Wrong label. We made sure the label was correct on the account as well as in the line set. Same result. All users and workstations had the same issue. Argh.

At a loss, I started a case. We stared at the issue some more. We decided to install the latest service pack. But before we had a chance....EUREKA! The just happened to notice that she had a Section with the same ID value as the account that was being problematic. So, the input set was confused and was pulling the label from the Segment although it was presenting data and saving like an account.

Just wanted to share this little nugget of experience, so that you can spare yourself the time we spent to find it :)

Tuesday, February 24, 2009

Windows update can cause GP VBA issues

Several years ago I developed several Modifier & VBA mods for a client that used Citrix server. The mods worked fine on the Citrix servers for Administrators and some users, but for other users, the VBA simply would not work. After weeks of research and fiddling in the registry, my recollection was that the only resolution we found was a blanket registry permission change, elevating the user's permissions in a large branch of the registry to full control. Not ideal, but after spending hours, we eventually had to just resolve the issue and move on.

Well, it appears that this type of issue may come up more, but fortunately there is a patch this time. Greg Wilson (Microsoft) has written a post on the Developing for Dynamics GP blog that explains how a Windows Update has caused registry permission issues with VBA mods, and has information about the rollup update that fixes the problem.

Friday, February 20, 2009

Project Accounting Integration Frustration - Part 2: Customer Aliases

In the first article of this topic, I explained the difficulties I ran into trying to assign customer aliases while importing Customer Project Info records into PA00501. In this article I'll present one possible solution for generating customer aliases that does not require any looping in my .NET code, automatically fills in gaps in the numbering sequence, and only requires one call to the SQL database.

I only recently discovered this approach (which appears to be a standard approach to the problem that many before me have already conquered), so I haven't yet thought of possible drawbacks or complications, but so far it seems to work in concept, and even better, it is a general solution that can be applied to many different situations where you need a robust, flexible, and fast method for getting a next number.

So I was already pretty convinced that looping through an arbitrary list of customer alias options was not practical, and that I was going to have to come up with a more comprehensive solution. What is the alternative to taking a subset of choices and checking each one of them against the list of existing customer aliases?

How about if we reverse the situation. Instead of asking "Is this single value already used?", what if we ask "What are ALL of the values that are already in use?". For that new question to make sense, we have to provide some context, so let's try "Out of all of the possible options of our entire value set, what are all of the values that are already in use?" Huh?

Let's consider that our entire set of possible customer aliases is 00000-ZZZZZ. So that's a "base 36" numbering scheme, which gives us 60,466,176 possible permutations. More than enough for any real GP customer. Hmmmmm. So instead of generating a single value, and then checking it against my alias list, what if I had a list of every single possible alias value at my disposal? Well, first of all, it means that I don't have to write a routine to generate a base 36 number, which is a pain, and I could therefore also probably eliminate any looping code. But how in the world could I possibly deal with a list of 60 million values?

If I think "procedurally", I know that I can't possibly loop through that many values. So loops and cursors are out of the question. But if I think in terms of "set based" operations, where SQL Server will magically sift through my request in a split second, the number of records effectively becomes irrelevant. (There are practical limitations that I'll consider later, but bear with me for now.) Okay...so...if I have a list of every possible customer alias, and I also have a list of every customer alias that has already been assigned in GP, what does that mean? It means that I can use a set based operation to compare the two lists, aka a SQL JOIN! And, if I'm able to compare the two lists using a JOIN, that also means that I can compare them in two different ways: An inner join, and an outer join. This was the "Eureka!" moment at which I knew there was hope.

If I can do an outer join between the two lists, that means that SQL Server will instantly tell me which values have not been used yet. I can then pick any number out of that list, and I will have a valid customer alias.

Okay, so the theory sounds promising. But how can I possibly implement this? How in the world do you generate a list of every 5-digit base 36 number? And that's where my friend's story comes in. While on a road trip several years ago, a friend told me that he worked with a SQL guru that was able to generate any series of sequential numbers with a single SQL statement. To do it, he used a less well known outcast cousin of the Join Family: the Cross Join, also known as the Cartesian Join. Under almost all conventional business circumstances, cartesian joins are considered a mistake--rather than combining matching values between two tables, you combined all possible values between the two tables--not good. But when we want to generate every possible permutation of two data sets, cross joins become your savior.

So how do we use a cross join to generate 60 million values? Well, let's start with the basis for our values: base 36. We want to work with the values from 0-9 and A-Z. So if we had two tables that contained the values 0-Z, we could do a cross join between them to get all of the possible values from 00 to ZZ. So let's create a "sequence" table that has our list of possible values:

CREATE TABLE [cstbSequence](
[Seq] [char](1) NOT NULL
) ON [PRIMARY]

Next, populate that table with 36 values, from 0 to Z. (Technically, you could avoid using a sequence table, but the code gets really silly looking, and there are some conveniences to this approach.)

Now, use a cross join to produce a list of values from 00 to ZZ:

SELECT a.Seq AS S1, b.Seq AS S2
FROM cstbSequence a
CROSS JOIN
cstbSequence b

Instantly, you have 1,296 values. No looping!

But we theoretically want 00000-ZZZZZ, so how do we do that 5 times? Well, if we actually wanted to generate that list (we don't! it would take forever!), we would do something like:

SELECT a.Seq AS S1, b.Seq AS S2, c.Seq AS S3,
d.Seq AS S4, e.Seq AS S5

FROM cstbSequence a
CROSS JOIN
cstbSequence b
CROSS JOIN
cstbSequence c
CROSS JOIN
cstbSequence d
CROSS JOIN
cstbSequence e


And, like magic, we can have every possible value from 00000-ZZZZZ. So now let's move from theory to reality, where a 60 million row table is a bit excessive, impractical, and actually unnecessary. Instead of generating all of the permutations, what if we just generated the 4 digit values, from 0000 to ZZZZ? That brings us down to a much more reasonable 1.6 million records. Let's do it!

First, let's create a table to hold our list of possible values:

CREATE TABLE [cstbAliasValues](
[AliasValue] [char](5) NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_cstbAliasValues] ON [cstbAliasValues]
([AliasValue] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]


And now let's fill up the table:

INSERT INTO cstbAliasValues
SELECT S1+S2+S3+S4 AS AliasValue FROM
(
SELECT a.Seq AS S1, b.Seq AS S2, c.Seq AS S3, d.Seq AS S4
FROM cstbSequence a
CROSS JOIN
cstbSequence b
CROSS JOIN
cstbSequence c
CROSS JOIN
cstbSequence d
) seq
ORDER BY S1, S2, S3, S4


On my modest virtual server, this operation takes about 21 seconds, but it's a one-time process.

So now we have a list of all of the 4 digit alias values, so let's compare them to our list of assigned aliases. But if we have a 4 digit list, how do we compare it to a 5 digit alias list? Well, if we go back to the requirements of this solution, I said that I wanted to be able to generate an alias that resembled my customer ID. In other words, I would like the first few characters of my alias to be the same as my customer ID. So let's stick with our example of customer ID 123456. By doing so, we are able to check our PA00501 table for aliases that, at a minimum, begin with the number 1--we really don't care about the other permutations that begin with 0 or 2-Z. One way to do that is to use a derived table, where we pretend that our alias values all begin with the number 1.

SELECT av.Alias, cpi.PAcustalias FROM
(SELECT '1'+AliasValue AS Alias FROM cstbAliasValues) av
LEFT OUTER JOIN PA00501 cpi ON av.Alias = cpi.PAcustalias

Assuming that our customer IDs range from 1-9, in this manner, we can just use a value of 1-9 to make our 1.6 million values become a 'virtual' 15 million values.

Almost there!

So now, let's add a filter to narrow down our range and just check the values that can begin with the numbers 123, and only show available aliases:

SELECT av.Alias, cpi.PAcustalias FROM
(SELECT '1'+AliasValue AS Alias FROM cstbAliasValues) av
LEFT OUTER JOIN PA00501 cpi ON cpi.PAcustalias = av.Alias
WHERE av.Alias LIKE '123%' AND cpi.PAcustalias IS NULL

And finally, since we really only need 1 available alias, we don't care about all of the available options--we just want the next available alias, so let's add a TOP clause:

SELECT TOP 1 av.Alias FROM
(SELECT '1'+AliasValue AS Alias FROM cstbAliasValues) av
LEFT OUTER JOIN PA00501 cpi ON cpi.PAcustalias = av.Alias
WHERE av.Alias LIKE '123%' AND cpi.PAcustalias IS NULL


And so there you have it. A SQL statement that will return the next available, sequential, base 36, customer alias in a single call to the database without using a single loop.

The general benefits of this solution is that it technically allows you to have multiple 'systems' issue unique numbers. In my example, GP is issuing customer alias numbers for manually entered customers, and I'm issuing customer alias values for imported customers.

Besides the obvious benefits of this approach, there are several other powerful possibilities.

For instance, let's say that you need to create a routine that will automatically generate unique serial numbers for a hundred different products in a product line. And let's say that you want to avoid using the numbers 0 and 1 and the letters O and I because the two look very similar when printed by your serial number printer. In that case, you just remove those four values from your sequence table, and when you generate your serial numbers, you are guaranteed to never have those characters. Just use the product prefix and then append the unique value to the end. No need to have convoluted logic to make sure that those 4 characters are never issued.

Or, suppose that you need to issue unique document numbers, but you never want to issue the same number twice, even if the document is physically deleted from your document table. In that case, after you issue the number, you can simply delete it from your values table.

Or maybe you needed blocks of 500 unique numbers to issue to a client application that was used by a remote sales force so that they could work offline, but you need a record of every block that was issued and to whom it was issued? Just change the TOP 1 clause to TOP 500, and add a few additional fields to your values table.

This approach does take a few more steps to deploy and implement, but if you need a long term, robust solution for unique numbering, I'm thinking it's a pretty good one.

If you can think of any drawbacks or flaws to this approach, I'm very interested in feedback. I have not yet implemented this in production, so there may be some issues that I haven't considered.

Reset GP after Integration Manager error

Integration Manager is a useful tool for certain situations, but if you've used it enough, you know that it has a few annoying quirks.

In order for IM to run integrations, Dynamics GP must be running. While the integration runs, IM hides the main GP window, and then executes its macros. When the integration is complete, it un-hides GP. This usually works fine, but there are cases where IM will have an error, causing the integration to fail or stop. Sometimes when this happens, IM will not un-hide GP. This can get pretty annoying after a few times when you are debugging an integration.

Even though you can't see GP, the Dynamics.exe process stays running, so you have to kill that process. But then your login is still sitting in the ACTIVITY table, so you have to delete that the next time you login. If this happens rarely, no big deal, but while testing an integration recently, I had some errors that caused the integration to fail, and left GP hidden every single time. After the 10th time of resetting GP, I finally decided to honor MacGyver and find a better solution.

While testing my integration, if an IM error occurred, I wanted to automatically:

1) Get rid of the orphaned Dynamics.exe process
2) Clear my orphaned GP login
3) While I'm at it, why not just log me back into GP?

I created this solution for use on my development servers--where both SQL Server and GP are installed locally, and I'm working directly on the server logged in as a Windows Administrator. I'm also just using the TWO / Fabrikam company. With a little additional effort, you could make this work on a standard GP workstation as well.

Here are the steps to "Reset GP":

Modify your Windows PATH environment variable to include the Dynamics GP application directory (i.e. add C:\Program Files\Microsoft Dynamics\GP\ to the end)



Reboot the machine so that the new PATH is loaded (you can test this by clicking Start-->Run-->Type Dynamics.exe and click OK)
Download the PS Kill utility from the MS Sysinternals site
Extract the pskill.exe file to C:\, or some other directory that you know is in the PATH (i.e. C:\Windows)

In Notepad, click on File and then New

Copy and paste the following text:

pskill dynamics.exe
sqlcmd -S sqlserver\GP10 -E -Q "DELETE FROM DYNAMICS.dbo.ACTIVITY WHERE USERID = 'sa'"
start Dynamics.exe Dynamics.set login.mac

Make sure to change the name of the SQL server and instance to match your installation. Also, this script uses Windows Authentication for SQL Server, assuming you are running as an Administrator. You can add the -U and -P parameters if you wish to specify a SQL username and password.

Save the file as C:\ResetGP.bat

In Notepad, click on File and New

Copy and paste the following text into the new Notepad file:

Logging file 'none.txt'
CheckActiveWin dictionary 'default' form Login window Login
TypeTo field 'User ID' , 'sa'
MoveTo field Password
TypeTo field Password , 'password'
CheckActiveWin dictionary 'default' form Login window Login
MoveTo field 'OK Button'
ClickHit field 'OK Button'
NewActiveWin dictionary 'default' form 'Switch Company' window 'Switch Company'
ClickHit field '(L) Company Names' item 1 # 'The World Online, Inc.'
MoveTo field 'OK Button'
ClickHit field 'OK Button'


Save the file as "login.mac" in your Dynamics GP application directory.

Now, create a shortcut on your desktop that points to the C:\ResetGP.bat file.

Viola! You can now reset GP by double clicking on an icon. You can even put the icon in your Quick Launch toolbar and make it a single click.

Thursday, February 19, 2009

Project Accounting Integration Frustration: Customer Project Info

(This is a two-part post. In this first article, I'll describe the issue I ran into, and in the second article, I'll propose a more complete solution for dealing with the issue that can be applied to other situations where unique document/transaction/record numbering is required.)


I recently developed a project accounting integration using eConnect. The integration reads 4 columns from an Excel file, and then creates all of the records in GP to fully setup the project, from the customer, to the contract, to the PA accounts, all the way through to the project budget and status flags. There were a few interesting learning experiences along the way, but the most challenging was one that I least expected: the PA Customer Options window. When you create a new customer and have PA installed, there is a Project button in the lower right corner of the customer window. This record is normally setup automatically when a customer is created in GP when PA is installed. But it is not setup automatically by eConnect.

I know, you're thinking "How hard could that be? There's only ONE required field!". That's exactly what I thought too!

The first bump occurred when I found that eConnect does not have a transaction to create the PA Customer Options record. Okay, not a big deal, I just traced the data back to the PA00501 table. It's a simple table, and I was able to just use the zDP_PA00501SI stored procedure to insert my record. I only had to pass in two pieces of data: customer number and customer alias. Simple!

After a few records imported, I had fleeting touch of self-pride, until I got this error:

Violation of PRIMARY KEY constraint 'PKPA00501'. Cannot insert duplicate key in object 'dbo.PA00501'.

After checking the PKPA00501 index, I saw that it was complaining that I had a duplicate customer alias. And that's where the arcane fun starts.

The PA Customer Alias field is a very annoying field that is limited to 5 characters. Yup, just 5. Normally, when you open the PA Customer Options window, the customer alias defaults for you, so you typically don't notice it, don't pay any attention to the default value, and care how it is generated. If your customer ID is ACME001, your alias will default to ACME0. If your customer ID is 123456, the default alias is 12345. Simple, right? Not so fast, grasshopper!

As I'm importing 50,000 customers with blocks of sequential, 6 digit customer numbers, guess what. I have customer ID 123456, and 123457, and 123458. So...clearly I can't just use the first 5 characters of the customer ID, as all would have an alias of 12345.

So I did some tests in GP to see how it generates the alias. I found that if alias 12345 is taken, it will use 12341. If that is taken, it just increments the last digit, so 12342, 12343, etc. This is fine and dandy if you have customer IDs that are fairly distinct and well distributed, like ACMEROCKETS, or ABCMETALS. But if you have sequential, numeric customer numbers that are 6 digits or longer, you start to have some challenges.

Here's an example of customer IDs, and the default alias generated by GP. Think of this as a big train wreck occurring in very slow motion:

123450 = 12345
123451 = 12341
123452 = 12342
123453 = 12343
123454 = 12344
123455 = 12346 (12345 is already used)
123456 = 12347
123457 = 12348
123458 = 12349
123459 = 12340 (GP doesn't actually use zero, but for arguments sake, I included it)

Looks fine, right? 10 customers, 10 aliases. Simple and easy, right? Well, no, the train is definitely wrecking, it's just taking its time.

What happens for customers 123401 - 123410? In that case, the default numbering scheme then goes from using the first 4 characters of the customer ID, to the first 3. So customer 123401 will get an alias of 12310. But then what will customer 123101 use? See the problem?

This all leads to a preposterous situation where a customer 123700 might receive a default alias of 11000. It's just stealing numbers from another series, attempting to have the alias resemble the customer ID, and hoping they won't all need to be used.

So at first, before I realized how many customers I was dealing with, I thought I would just write a routine that would loop through alias numbers to find an available value--I basically mimicked the GP default alias generator. I used the first 4 characters of the customer ID, and if those 10 aliases weren't available, I moved on to the first 3 characters of the customer ID.

But then that resulted in duplicate aliases, as all of those 100 alias values were taken. So then I realized that I would then have to look through the aliases starting with the first 2 characters of the customer ID. That's 1,000 values. And even then I ran into situations where that wasn't enough.

The next step would be to use the customer's first 2 digits, and check 10,000 possible alias values. The train is definitely off the tracks at this point.

It became clear that there HAD to be a better way.

The quick and dirty approach first came to mind is to throw a letter into the mix. If my options included 12340 to 12349 and also 1234A to 1234Z, that gives me 26 more options--basically a "base 36" numbering scheme. Naturally that would work, right? Maybe as a temporary solution, but as thousands of more customers were created, I could still run into an issue. So I could do something like 1234AA, where the last two characters could be alphanumeric. But if you try and write such a routine, it looks like a looping circus.

And there is another issue. This alias generation routine was in my .NET app, and in order to validate the alias, I have to make a call to SQL Server to check if the alias is in use already. So with every number I try, it's a query against SQL. Just plain bad design. If I were checking just 10 values, I'd let it slide, but thousands of values is out of the question.

So what's a better solution? I want to:

1) Generate a 5 character alias that "resembles" my customer ID
2) Make sure the alias does not already exist in PA00501
3) Generate the available alias values sequentially so that I don't have unecessary gaps
4) Eliminate looping in my code
5) Make one query against the database

This is actually a fairly common issue with business apps and databases, but there are many different nuances and business requirements around numbering, so there isn't necessarily "a solution" for all situations.

After thinking about the issue for a few minutes, I eventually remembered a story that a friend told me about a SQL Server guru that could magically generate a range of sequential numbers with a single SQL statement. That story led me to my solution, which I'll share in part two.

Monday, February 16, 2009

Project Accounting Cost Category and Fee ID issue

Well, I stumbled across a known issue last week that I thought I would share. A client called last week with a strange issue. Generally, this client solves most of their own issues, so when they call, I know it is going to be an interesting and complex issue.

So here is the scoop..in Project Accounting, actual billings equaled payments/receipts received. Great. And the detail supported this summary. However, they ran reconcile on Cash Apply (Tools>>Routines>>Project>>PA Reconcile) and the payments/receipts were recalculated to be less than the actual billings. The difference was the same as the FREIGHT fee billed on the project. This was most apparent on a simple project that had one invoice, one payment. The client had noticed that it was happening on all projects with the fee called FREIGHT.

We started poking around in the database, looking at the underlying setup of the FREIGHT fee and comparing it to other fees, trying to determine what might be different. We could not see anything different. We ran a dexsql log during the PA reconcile process, to see what stored procedures/tables were being referenced. We looked through all the tables again, still no luck. So then we started to wonder what was unique about the fee ID of FREIGHT. At that point, the client said something like "you know what, this fee is named the same as a cost category". Bingo!

So we made some quick changes in the tables (test environment, of course) so that that fee ID was FREIGHT2 instead of FREIGHT. We re-ran the PA reconcile, and the payments/receipts received were now correct! What an odd little issue.

It is actually a recently documented quality report (#49611). I thought I would share the wisdom that, for now, don't name your fees and cost categories the same. For those that already have the issue, there are some scripts available from MBS Professional Services that allow you to change Cost Category IDs (and therefore correct the problem for projects that already have activity).

Co-author credit on this one has to go to Dave (the client), for the teamwork to determine the root cause :)

Monday, February 2, 2009

Project Accounting Asset Tracking

Due to Business Ready Licensing (where clients receive a "suite" of modules when purchasing Microsoft Dynamics GP), I have found more and more clients using project accounting in non-traditional ways simply because they already own the software. One of the most common non-traditional approaches is to use the module to track capital expenditures. Project accounting works great for the capturing of the myriad of costs associated with a capital project like labor, materials, consulting, and even indirect expenses like overhead and equipment usage.

One of the questions that always comes up in discovery is how to handle the "recognition" of the asset, when a capital project has reached a specific stage of completion and the construction in progress can be capitalized. I have found that by using the WIP (Work in progress) functionality of project accounting with a time and materials project, we can easily emulate the transfer of expense from a CIP (Construction in progress) account to an asset account.

Let's walk through an example of typical WIP using a cost of $100 that is billed for $150:

$100 purchase
$100 Debit Work In Progress
$100 Credit Contra Account for Cost (Accounts Payable)

$150 billing
$100 Debit Cost of Goods Sold/Expense
$100 Credit Work in Progress
$150 Debit Accounts Receivable
$150 Credit Project Revenue

Okay, so that is all well and good, but with a capital project there would be no billing, right? Well, in our process we will do a "dummy" bill as outlined below.

$100 purchase
$100 Debit Work in Progress (Construction in Progress)
$100 Credit Contra Account for Cost (Accounts Payable)

For this to work, its important to note that the billing type is set to STD (standard) and the profit type is set to Billing Rate $0.00. These settings are very important. You cannot make the items NB (not billable) or NC (no charge), or set the profit type to NONE-- the process will not work with these settings as no WIP distribution will be generated.

Then, when we do a billing (using cycle biller or manually, choosing what needs to be recognized-- either all the costs, or just a portion), the items will show up to be billed at $0.00 resulting in a $0.00 bill and no effect on receivables management. However, the costs will be moved out of WIP in to the appropriate asset account. The billing you print serves as a record of the expenses that were capitalized.

$0.00 billing
$100 Debit Cost of Goods Sold/Expense (Asset account)
$100 Credit Work in Progress (Construction in Process account)

As an additional tip, if you are using the fixed assets module. The debit above (to Cost of Goods Sold/Expense) would actually go to the FA Clearing account, and the report from the billing would be given to the user who sets up fixed assets. They can set up the fixed asset for the total amount that was moved from CIP, the action of setting the asset up in fixed assets would then move the balance from FA clearing (credit) in to the FA cost account (debit).

If anyone has any other unique processes they have accommodated in project accounting, I would love to hear about them and will post them on this blog.