web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Creating manual invoic...
Microsoft Dynamics GP (Archived)

Creating manual invoices from sql

(0) ShareShare
ReportReport
Posted on by Joanne Millman 445

Hello,

 It is possible that I may have to manually add some invoices to GP in order to get around a tax change issue that we are faced with and not sure how to handle in GP.

 What I may have to do is using Contract Billing, create the invoices normally.  I will then have to go through and adjust the invoices that have been run as quarterly, semi-annually and anually so that the amount that reflects Mays portion of the invoice is taken off the invoice amount, adjust the taxes accordingly and then create a new invoice for the amount that represents May's amount and calculate the taxes based on this tax change. 

 I think I have managed to determine the tables that are affected when invoices are created but what I am not sure of is how to determine what invoice number to use and where that information is kept.

 If anyone can point me in the right direction it would be greatly appreciated!

Thanks in advance!

*This post is locked for comments

I have the same question (0)
  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

    Joanne,

    I would definitely NOT suggest doing this. Even if you know all of the tables that are hit during an invoice creation, there are quite a bit of background processes that fire when creating thatinvoice (the procedure for get the next invoice number is one).  I just think I would stay away from doing that personally.  One thing we do to fix tax issues is return the invoice and reinvoice with the correct tax schedule.  If you are talking a large number of invoices, I would suggest opening a support case with Microsoft and have them help you resolve the issue.

    Ron

  • Joanne Millman Profile Picture
    Joanne Millman 445 on at

    Thanks Ron, I was afraid there would be a number of processes running behind the scenes.  I'll give you an example of our issue we are trying to figure out with taxes

     We have to implement a tax change from having 2 taxes to 1 tax.  This takes affect July 1.  Our quarterly invoices when generated in May for services from Jun 1 to Aug 31 will have to reflect that the amount for servcies in Jun will be the old 2 taxes but the amounts for Jul & Aug will be the new 1 tax.  I'm not sure how you would set that up to do that.

     Joanne

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

    This might be a little over my head but I will give you something to think about.

    Let's say you are going to invoice for your service ABC.  Since you are invoicing for 3 months, you will typically enter ABC for a quantity of 3?  This one line item with a quantity of 3 ABC's will be taxed using the 2 tax Tax Schedule ID.  With your new scenario, are you going to create new tax schedule ids or modify the existing one?

    If you are creating new tax schedule id, then you could invoice 2 line items:

      1. ABC x 1

      2. ABC x 2

    Giving the first line item the 2 tax and the second line item the 1 tax, tax schedule ids.

    Does that sound even close to being possible?

  • Joanne Millman Profile Picture
    Joanne Millman 445 on at

    That sounds like exactly what we want to do the only problem I'm having is the invoices are created using contract billing so they are created automatically.  The invoice amount is a total of the 3 months, it doesn't actually show as 3 different line items.

    We will be making a new taxcode for this new tax but we do not know how to tell the system when an invoice is created for multiple months that span over the changeover date for the new tax, take the amount before the change over date and use the old tax and the amounts after the change over date use the new tax. 

    Because GP is calculating this in the background and creating the invoices I'm stumped at what to do.

    The only thing I could really think of was, take the invoice that GP created, modify the amount to reflect the total for 2 of the months and recalculate the taxes using the new tax.  Create a new invoice with the amount for the 1 month reduced from the other invoice and calculate the tax using the old taxcode.  This of course would all be done in an sql script.  I would have to the invoice numbers from the setup file which indicates for Sales Order Processing what the next invoice number would be and increment this number each time.  I'm sure there are processes I do not see though that would need to be done.

     

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

     I am sorry but this is now beyond my knowledge of GP.  Maybe someone else could help.  I really don't think that SQL is where you want to do this.  The numbers in the Sales Order Processing setup are actually updated as well when and invoice is created (would you have caught that with your SQL query?).  Do you have a service contract with Microsoft?  I think that is where I would go next.

    Sorry I couldn't be any more help.

    Ron

  • Joanne Millman Profile Picture
    Joanne Millman 445 on at

    Thanks Ron, this beyond my knowledge of GP as well.  I have one more option to try for help and I think you are right.... Microsoft is the next step after that!

    Thanks for trying, have a great weekend!

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

     Joanne,

    I am just curious why Microsoft would be your last stop?  In my experience, they solve most of our issues the quickest.  Just curious and good luck.

    Ron

  • Joanne Millman Profile Picture
    Joanne Millman 445 on at

    I actually already contacted Microsoft and they told me we needed to go to our Microsoft Partner.  I'm waiting to discuss with them and if they can't help me then I will be going to Microsoft.

    Joanne

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

    Ah...that is interesting.  Do you have support cases left with Microsoft?  If you do, I would make sure you use one of those before paying a partner (...sorry to all the partners out there).  Best of luck.

    Ron

  • Joanne Millman Profile Picture
    Joanne Millman 445 on at

    We do not have a support case left but my thoughts were the same as yours.....

    Joanne

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans