The following SQL Script will show you all Payables invoices that have been entered into your system where the price your vendor is charging does not match the price of your purchase order.
We use this, in conjunction with Task Centre, to alert us when a vendor has charged us more than what our purchase order was submitted for. Very valuable when you have a payables clerk processing 100’s of invoices a day (it is easy for them to make a mistake).
This script has options for total amount of discrepancy. This allows you to “tweak” the script to not show line items that are off a couple of pennies (or whatever tolerance you wish).
We actually use $5, so that is set as default in the script. To change, replace the value in line 2 (SET @dollar_amount=5), to whatever dollar value you want. DO NOT INCLUDE THE DECIMALS.
The script using the following views:
– Purchase Order (tspvPurchaseOrder)
**SQL Script***
DECLARE @dollar_amount INT
SET @dollar_amount = 5
select [Invoice_Date] = c.receiptdate,
[PO_Number] = a.po_number,
[POP_Type] = case when c.poptype = ‘1’ then ‘Shipment’
when c.poptype = ‘2’ then ‘ Invoice’
when c.poptype = ‘3’ then ‘Shipment/Invoice’
else ‘N/A’
end,
[PO_Date] = po_date,
[Vendor_ID] = vendor_id,
[Vendor_Name] = vendor_name,
[Item_Number] = d.itemnmbr,
[Item_Desc] = d.itemdesc,
[PO_Qty] = d.qtyorder,
[Inv Unit Cost] = b.unitcost,
[PO Unit Cost] = d.unitcost,
[Created_By] = c.user2ent
from tspvpurchaseorder a
join pop30310 b on a.po_number = b.ponumber
join pop30300 c on b.poprctnm = c.poprctnm
join pop10110 d on a.po_number = d.ponumber
AND b.itemnmbr = d.itemnmbr
AND b.itemdesc = d.itemdesc
where ( ( b.unitcost – d.unitcost ) > @dollar_amount )
order by po_number desc
The following SQL Script will show analyze the freight you charged to a customer versus the freight you were billed from you vendor provided there is a SOP to POP link between the sales order and purchase order.
This question was raised in the Dynamics GP Community by JCohen:
We are looking for a way to associate outgoing freight charges from 3rd party carriers (not invoiced to customers) with applicable sales invoices in order to better track the profitability of customers. It seems, to me, that it is the flip-side of landed costs but I have not found a way to link them. Any suggestions?
https://community.dynamics.com/product/gp/f/32/t/63015.aspx
***SQL Script***
SELECT a.Sales_Doc_Num,
c.DOCNUMBR Payables_Document,
c.PORDNMBR PO_Number,
c.POSTEDDT Posted_Date,
c.FRTAMNT Vendor_Freight_Amount,
a.Freight Sales_Freight_Charged,
[Freight_Diff]=a.Freight-c.FRTAMNT
FROM dbo.tspvSalesDocumentHistory a
JOIN dbo.tspvSopToPop b ON a.Sales_Doc_Num = b.Sales_Doc_Num
JOIN pm30200 c ON b.PO_Number = c.PORDNMBR
***One thing to note, since this script combines Sales Document Headers and Purchase Line Items (Headers vs Details) the freight analysis for each Sales Order will be repeated for the number of lines on the Purchase Order. This can easily be cleaned up in a report. Feel free to Contact Me if you need any help.***
The following SQL View shows you all invoices that are coming due next week. We use this view in conjunction with Task Centre to send out a list (fax or email) to our customers of invoices will be due next week. This has really helped our aging process and past due invoices. Our customers have expressed an appreciation for this friendly reminder.
***SQL Script***
select * from
(select custnmbr, docnumbr, duedate, cspornbr, docdate, ortrxamt, curtrxam from rm20101
where duedate >= dateadd(wk, datediff(wk, 0, getdate())+1,0)-1
and duedate < dateadd(wk, datediff(wk, 0, getdate())+2,0)-1) A
left join
(select custnmbr, custname, cntcprsn, stmtname, adrscode, address1, address2, address3, city, state, zip, phone1, phone2, phone3, fax, slprsnid, pymtrmid from rm00101) B
on a.custnmbr = b.custnmbr
left join
(select customer_num, invoiceStatementTo from spvcustomer) C
on a.custnmbr = c.customer_num
order by a.custnmbr asc, a.docnumbr asc
If you have some time, check out Dynamics GP 2010 Reporting from Packt Publishing. This book will give you some great information with regards to reporting and Dynamics GP.
Whether you are looking to utilize the built in reporting tools such as Smart Lists and Report Writer, or if you are looking to dive a little deeper into SQL, Excel, or Smart List Builder, this book is for you. This book also has a nice breakdown of how the Dynamics GP databases are structured. Between what this book teaches and Victoria Yudin’s list of GP Tables, you should have all the tools you need to get started writing your own reports.
Following up on my last script, SQL Scripts–Order Count–Previous Week By User, this script will show you the number of orders entered by user for the current year.
***Note that you will need to make sure the @doc_date is set to January 1st of the current year***
This script uses the following views:
– Historical Sales Documents (tspvSalesDocuementHistory)
DECLARE @doc_date DATETIME
–Set @doc_date to January 1st of the current year–
SET @doc_date = ‘1/1/2010’
SELECT created_by,
[Orders_Entered] = COUNT(*)
FROM dbo.tspvSalesDocumentHistory
WHERE Doc_Date >= @doc_date
AND Sales_Doc_Type = ‘order’
GROUP BY Created_By
ORDER BY orders_entered desc
Do you ever wonder what your sales order workload looks like for you customer service staff? This script will show you the number of orders entered by user for last week.
***Note that you will need to make sure the @doc_date is set to January 1st of the current year***
This script uses the following views:
– Open Sales Documents (tspvSalesDocument)
– Historical Sales Documents (tspvSalesDocuementHistory)
DECLARE @doc_date DATETIME
–Set @doc_date to January 1st of the current year–
SET @doc_date = ‘1/1/2011’
SELECT created_by,
[Week] = DATEPART(ww, doc_date),
[Orders_Entered] = COUNT(*)
FROM ( select Created_By,
Doc_Date
from tspvSalesDocumentHistory
WHERE Doc_Date >= @doc_date
AND Sales_Doc_Type = ‘order’
AND Original_Num = ”
union all
select Created_By,
Doc_Date
FROM tspvSalesDocument
WHERE Doc_Date >= @doc_date
AND Sales_Doc_Type = ‘order’
AND Original_Num = ”
) as combined
WHERE DATEPART(ww,combined.Doc_Date) = DATEPART(ww,GETDATE()-7)
GROUP BY Created_By,
DATEPART(ww, doc_date)
ORDER BY DATEPART(ww, Doc_Date),
Created_By
So…you know that you had something that you needed to order for a customer, but you cannot remember what it is to save your life? Well the following view will show you a listing of all open sales order with line items that need to be purchased. Whether the line item is back ordered, or a drop ship, this view will show you what needs to be purchased (or linked to an existing purchase order).
This script uses the following views:
Sales Document (tspvSalesDocument)
Sales Line Item (tspvSalesLineItem)
***SQL Script***
SELECT a.Sales_Doc_Num,
Doc_Date,
Sales_Batch,
Customer_Num,
Customer_Name,
Created_By,
Item_Number,
Item_Description,
Is_Dropship,
Quantity,
Notes
FROM dbo.tspvSalesDocument a
JOIN dbo.tspvSalesLineItem b ON a.Sales_Doc_Num = b.Sales_Doc_Num
WHERE b.Purchasing_Status = ‘needs purchase’
ORDER BY Sales_Batch,
Sales_Doc_ID
The following view shows all Sales Documents with active or inactive holds.
This view is also a modification of the spvSalesDocumentHolds view from Salespad (www.salespad.net).
You can name the view whatever you want by change the [tspvSalesDocumentHolds] below. Also the USE [TSP] tells the script to only execute on my database named TSP. You will need to modify the [TSP] to be whatever your database name is.
USE [TSP]
GO
/****** Object: View [dbo].[tspvSalesDocumentHold] Script Date: 04/28/2011 14:20:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[tspvSalesDocumentHold]
as
select
[Sales_Doc_Type]=CASE SOPTYPE when 1 then ‘QUOTE’ when 2 then ‘ORDER’ when 3 then ‘INVOICE’ when 4 then ‘RETURN’ when 5 then ‘BACKORDER’ end,
[Sales_Doc_Num]=SOPNUMBE,
[Hold_Code]=SOP10104.PRCHLDID,
[Is_Deleted]=cast(DELETE1 as bit),
[User_ID]=USERID,
[Last_Update_On]=cast(convert(char(11), HOLDDATE, 1) + convert(varchar, TIME1, 14) as smalldatetime),
[Cant_Transfer]=cast(XFERPHOL as bit),
[Cant_Post]=cast(POSTPHOL as bit),
[Cant_Fulfill]=cast(FUFIPHOL as bit),
[Cant_Print]=cast(PRINPHOL as bit)
from SOP10104 (nolock) join SOP00100 as h (nolock) on h.PRCHLDID = SOP10104.PRCHLDID
We recently had a vendor decrease pricing (yes…you read that right…DECREASE!!!!). While this is wonderful to see, it has cause a little bit of a problem for us. We use a product call SmartConnect to import and update items and pricing. When our vendor sent us our updated price sheet, we imported the updates en masse, which caused our Standard Cost to decrease on many of the items. The problem with this is that we had products where their FIFO (FIFO is what we use…this would apply to LIFO as well) valuation was now higher than the Standard Cost. This means we are selling our product based on a cost that is actually less than we paid for it. This means lower margins or even losing money on the sell of the product.
For example:
We purchased 5 x widgets for $100.00. We have sold 3 x widgets for $125 based on a Standard Cost of $100. That is a 25% margin we just made on each widget. Now the price decrease comes in and our new Standard Cost is $90.00. We sell the next 2 widgets, based on a standard cost of $90 and 25% margin, for $120. Now if our actual costs were $90, then that would be a 25% margin as well, but remember we still have 2 x widgets in stock that we paid $100 for. Now our margin is only 20% ($100 cost and $120 sell = 20% margin).
The solution:
I have written a script that will compare our Standard Cost to our inventory valuation table to see if we have purchased something for more than we have our Standard Cost setup.
***This script assumes you are using Standard Cost as your calculation for setting margin/markup on your item. If you are not, then let me know and I will show you how to change to Current Cost.***
This script uses the following views:
Item Master (tspvItemMaster)
Inventory Receipts (tspvInventoryReceipts)
***SQL Script***
SELECT Item_Class_Code,
a.Item_Number,
[OnHand] = Qty_Received – Qty_Sold,
b.Unit_Cost,
a.Standard_Cost,
[CostDiff] = a.Standard_Cost – b.Unit_Cost
FROM dbo.tspvItemMaster a
LEFT JOIN dbo.tspvInventoryReceipts b ON a.Item_Number = b.Item_Number
WHERE Qty_Received – Qty_Sold > 0
AND ( a.Standard_Cost – b.Unit_Cost < -0.02 )
AND Standard_Cost <> ‘0.00’
ORDER BY (a.Standard_Cost-b.Unit_Cost)
***Updated 4/22/2011***
The above script will return the true FIFO/LIFO layer. So if you have 2 x widgets at a cost of $100 and 3 x widgets at a cost of $95 then you will see 2 results. The first row showing how 2 x widgets with a unit_cost of $100 and then a second row showing 3 x widgets with a unit_cost of $95.
The script below will give you the MAX FIFO/LIFO value found and only return 1 row for each item_number. So the results would just show the widget (no quantities) with a unit_cost of $100 (because $100 is more than $95).
***SQL Script MAX***
Select Item_Class_Code,
Item_Number,
Item_Description,
Standard_Cost,
[Unit_Cost]=( Select MAX(b.Unit_Cost) AS ‘unit_cost’
from dbo.tspvInventoryReceipts b
Where b.Item_Number = a.Item_Number
AND ( Qty_Received – Qty_Sold > 0 )
group by b.Item_Number
),
[Cost_Diff]=a.Standard_Cost-( Select MAX(b.Unit_Cost) AS ‘unit_cost’
from dbo.tspvInventoryReceipts b
Where b.Item_Number = a.Item_Number
AND ( Qty_Received – Qty_Sold > 0 )
group by b.Item_Number
)
from dbo.tspvItemMaster a
WHERE a.Item_Number NOT LIKE ‘*%’
AND Item_Class_Code <> ‘used equip’
AND ( Select MAX(b.Unit_Cost) AS ‘unit_cost’
from dbo.tspvInventoryReceipts b
Where b.Item_Number = a.Item_Number
AND ( Qty_Received – Qty_Sold > 0 )
group by b.Item_Number
) IS NOT NULL
AND a.Standard_Cost – ( Select MAX(b.Unit_Cost) AS ‘unit_cost’
from dbo.tspvInventoryReceipts b
Where b.Item_Number = a.Item_Number
AND ( Qty_Received – Qty_Sold > 0 )
group by b.Item_Number
) < -0.02
ORDER BY Item_Class_Code, a.Item_Number
Folks…I just wanted to update you on the status of the RLDU blog. It has obviously been pretty stagnant over the past couple of months. I recently received a big promotion at work and I am know the “Director of Operations” (big title I know…). This has taken up a very large amount of time at work as well as at home (where I usually get to write these posts). I will continue to maintain this blog as I have time. I am still very much involved in the day to day operations of our GP environment and will continue to post *hopefully* helpful and useful information. I will never be the kind of blogger who posts for the sake of posts counts (even though I do admit I hate seeing months go by with no posts). I appreciate the understanding and continued support.
Ron
Real Life Dynamics User 