Company Address IDs - Import

Did you know that within Purchase Order Processing that the list of available ship to addresses pulled from the Company Address ID setup within GP?  During a POP/Inventory/SOP implementation, it's no uncommon for companies to have 100s of potential ship to addresses for purchases.  Below we've covered our favorite way to get this list of address IDs into the system using .csv/.txt files and SQL.

Step 1.)  Create a spreadsheet that list all address lines.  Our example contains the following columns.

Company ID, Address ID, Name, Contact, Address 1, City, State, Zip Code

Step 2.) Save spreadsheet to a tab-delimited text file

Step 3.) Open the text file & replace any " characters w/ {blanks}.  " characters will appear when a field contains a comma.

Step 4.) Import into SQL using SQL Server Management Studio

>>Right-Click Database

    >>Select Tasks

        >>Select Import Data

           >>Select Flat File as source

              >>Enter Staging Table Name in SQL Destination (Use name of CoAddressID_Template in our example below)

Step 5.) Create backup of table that we're about to insert into (select * into sy00600_backup{date} from sy00600.  This step is important in case we need to roll-back our insert and don't want to have to refresh the entire database.

Step 6.) Ensure that no address IDs in your staging table already exist within GP.

select * from sy00600 where locatnid in (select [Address ID] from coAddressid_template)

Step 7.) Run SQL Insert statement to move data from staging table (CoAddressID_template) to SY00600 table.

declare @cmpanyid numeric(2,0);

set @cmpanyid = (select cmpanyid from dynamics..sy01500 where cmpnynam = '{ENTER YOUR CO NAME}');

insert into gpent..sy00600 (CMPANYID,LOCATNID,LOCATNNM,ADRSCODE,ADRCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,COUNTY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAXNUMBR,CHANGEBY_I,CHANGEDATE_I,CCode)

select @cmpanyid,left([Address ID],15),left([Name],30),'',left([Contact],60),left([Address 1],60),'','',left([City],25),

'',left([State],30),left([ZIP Code],10),'','','','','','','1900-01-01 00:00:00.000','' from coAddressid_template

That should do it.  You can add additional columns to your sourcefile if you please.  Just be sure to update your insert script to account for the new columns & remember character limitations! left(Address1,60)

Our first preference is to always use the Microsoft eConnect procedures or integration tools like Integration Manager when available.  However, when all else fails, a carefully executed SQL script can achieve the same outcome.  At the end of the day, it's all data in a database.

Let us know if this works for you or if you'd like to see similar processes using SQL!

Handling Unapplied Open Balance on Purchase Order Prepayment

The ability to create purchase order prepayments was introduced w/ GP 2013R2.  With any new feature, there is almost always rooms for improvement and POP prepayments is no exception.  One of the areas of improvement is such a cluster that we felt a blog describing the issue and the necessary workaround was warranted.

Issue:

When a PO prepayment has a remaining balance and the corresponding Purchase Order is moved to history, it becomes nearly impossible to apply the prepayment to any future vouchers.

When closing a Purchase Order under Purchasing>>Transactions>>Edit Purchase Orders, the following screen appears which prompts you that there is an unapplied balance on a prepayment.

At this time, we are still able to apply the prepayment balance to any offsetting invoice or debit memo without issue.

However, once the Remove Completed Purchase Orders routine is run (Purchasing>>Routines>>Removed Competed POs), the PO is moved to history and the prepayment is NO LONGER available to select and apply to existing vouchers.  It cannot be transferred to regular payables, there cannot be any transactions entered against the PO since it is now historical, it is not available under Apply Payables Documents, & cannot be applied to future check runs.

Quite the conundrum...

Workaround:

Although the balance cannot be transferred to regular payables, we do still have the ability to transfer the balance to A/R using customer/vendor relationships.  After transferring to A/R, we can turn around and transfer it back to A/P where it will now become a regular PM voucher (Credit Memo) that can be applied to existing invoices or debit memos for the vendor.  We covered the process to transfer balances between customers and vendors in an earlier video here.

Perfect World:

All is well up until the point that the Remove Completed Purchase Orders routine is run.  During that process, there are no warnings that you'll no longer be able to apply invoices & nothing that stops you from proceeding to create the scenario that we've highlighted above.  Here's to hoping that the developers at Microsoft prevents Purchase Orders from moving to history if there is an outstanding balance on a prepayment OR they provide a more direct route to move a POP prepayment to regular payables.

Until then, this will assist you in clearing up those A/P trial balances and remaining documents come month/year-end!