SQL view for all posted Payables transactions in Dynamics GP

6 Responses to SQL view for all posted Payables transactions in Dynamics GP

1. Lisa Armstrong says:

   April 22, 2010 at 12:06 pm

   Victoria,

   This is a WONDERFUL website – thank you sooooooooooooo much. I’m bookmarking it!!!!

   Lisa

   Reply
2. stanley says:

   November 17, 2009 at 12:11 pm

   Victoria,
   In your case for the PYENTTYP you have 3 for EFT. I am working on doing a custom app for our company to write checks and process EFT. So if I just put in the PYENTTYP = 3 will the system automatically pick it up as an EFT? Or is there more I will need to do to get the system to recognize 3 as EFT?

   Reply
   • Victoria Yudin says:

     November 17, 2009 at 6:59 pm

     Stanley,

     If the payment is entered as an EFT type payment, it will have PYENTTYP = 3.

     -Victoria

     Reply
3. Laura says:

   October 28, 2009 at 7:40 pm

   Hi Victoria,
   Thanks for all the great GP reports and info. This site is amazing!
   I am new to GP, and just started working with Smartlist Builder and SQL Views a couple weeks ago. My company needs a clean downloadable AP aging and I have just about got it right, except I am finding it difficult to create a total for the aging buckets.
   I have created a view, originally because I wanted to create an aging date that would be the docdate if the open item wasn’t an invoice, and the due date if it was an invoice so that I could then use the aging date to calculate which bucket the open invoice belonged to.
   Then I created 5 buckets for the aging days and have the invoice amts totalling correctly in those buckets.

   Now I just want to create a total for all the buckets and SLB doesn’t allow you to use calculated fields inside calculated fields. And I can’t figure out an easy way to total the buckets in the view I created because it doesn’t seem to allow me to use a newly created field either.
   Here’s my view and a couple of the bucket fields. I would really appreciate your help with creating a total for all the bucket fields.

   Thanks,
   Laura

   CREATE VIEW [LAW_payables_aging_date] AS
   SELECT
   CASE DOCTYPE
   WHEN 1 THEN DUEDATE
   WHEN 2 THEN DOCDATE
   WHEN 3 THEN DOCDATE
   WHEN 4 THEN DOCDATE
   WHEN 5 THEN DOCDATE
   WHEN 6 THEN DOCDATE
   ELSE DOCDATE
   END Aging_Date,
   VCHRNMBR,DOCTYPE,DOCDATE,DOCNUMBR,DUEDATE,
   PORDNMBR,WROFAMNT,VOIDED,DOCAMNT, T1.VENDORID, VENDNAME
   from dbo.PM20000 T1
   INNER JOIN dbo.PM00200 T2
   on
   T2.[VENDORID] = T1.[VENDORID]

   2 of the BUCKETS which were created in calculations in SLB:

   CURRENT BUCKET

   CASE
   WHEN (DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date} , GETDATE() ) < '0' and
   {*LAW_payables_aging_date:Document Type} = 5) THEN ({*LAW_payables_aging_date:Document Amount} * -1)
   WHEN (DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date} , GETDATE() ) ’0′ and DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date}, GETDATE() ) ’0′ and DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date}, GETDATE() ) < '31') and
   {*LAW_payables_aging_date:Document Type} = 1 THEN
   {*LAW_payables_aging_date:Document Amount}
   ELSE NULL
   END

   I want to add the current bucket to 1 – 30 days and 31-60 days and 61-90 days, etc.

   Reply
   • Victoria Yudin says:

     October 29, 2009 at 5:09 am

     Laura,

     Thanks for the kind words!

     I am not sure I follow – are you looking to add a column with a total of all the buckets? If so, that should just be the Current Trx Amount (CURTRXAM), why add the calculations? Also, I would suggest 2 changes to your code:

     1. Use Current Trx Amount (CURTRXAM) instead of the Document Amount – anything partially paid will have the amount still due in the CURTRXAM, using the Document amount will overstate your payables.
     2. Change the sign on the ‘credit’ transactions (DOCTYPE 4, 5 and 6). GP stores everything in payables as a positive number. If you have open payments, credit memos or returns, then they will show up as positive numbers and overstate your payables.

     Hope that helps. Please write back if you need additional help.

     -Victoria

     Reply

4. Pingback: SQL View for All Posted Payables Transactions - DynamicAccounting.net