Meta tags:
description=Real Life Experiences With Microsoft Dynamics GP / CRM;

Headings (most frequently used words):

sql, scripts, user, to, dynamics, week, order, count, by, script, cost, real, life, vendor, invoice, pricing, discrepancy, sop, pop, freight, analysis, invoices, coming, due, next, packt, publishing, gp, 2010, reporting, ytd, previous, orders, needing, purchase, view, sales, document, holds, standard, less, than, valuation, what, have, been, up, lately, search, subscribe, updates, tags, most, popular, latest, comments, rss, feeds, total, views, since, november, 2009, archives,

Text of the page (most frequently used words):
the (72), you (40), and (39), sql (36), this (33), order (29), #script (29), from (25), for (24), item_number (21), doc_date (21), will (20), select (19), cost (17), that (17), where (16), unit_cost (16), have (15), 2011 (15), our (15), with (14), dynamics (14), sales (14), dbo (14), comments (13), view (13), views (13), scripts (13), 2010 (12), then (12), join (11), your (11), following (11), when (11), set (11), purchase (10), user (9), show (9), 100 (9), are (9), standard (9), created_by (9), invoices (8), standard_cost (8), not (8), widgets (8), life (7), september (7), inventory (7), vendor (7), has (7), what (7), use (7), sales_doc_num (7), week (7), freight (7), po_number (7), real (6), posts (6), count (6), max (6), tspvinventoryreceipts (6), qty_received (6), qty_sold (6), group (6), than (6), margin (6), current (6), bit (6), cast (6), need (6), all (6), line (6), custnmbr (6), com (5), email (5), now (5), january (5), community (5), item (5), items (5), datepart (5), year (5), blog (4), april (4), rss (4), know (4), well (4), item_class_code (4), fifo (4), uses (4), purchased (4), price (4), decrease (4), next (4), product (4), sales_doc_type (4), invoice (4), tsp (4), whatever (4), documents (4), tspvsalesdocument (4), 1st (4), reporting (4), book (4), due (4), customers (4), unitcost (4), dollar_amount (4), get (3), like (3), wordpress (3), name (3), report (3), sign (3), subscribe (3), 2009 (3), december (3), sop (3), pop (3), previous (3), processing (3), payables (3), just (3), been (3), very (3), time (3), day (3), tspvitemmaster (3), lifo (3), value (3), only (3), return (3), row (3), each (3), would (3), more (3), see (3), how (3), left (3), change (3), valuation (3), based (3), sell (3), but (3), had (3), pricing (3), was (3), sop10104 (3), prchldid (3), also (3), document (3), customer_num (3), customer (3), orders (3), orders_entered (3), tspvsalesdocumenthistory (3), getdate (3), declare (3), note (3), make (3), number (3), entered (3), looking (3), list (3), docnumbr (3), duedate (3), poptype (3), itemnmbr (3), itemdesc (3), started (2), site (2), required (2), write (2), content (2), log (2), subscribed (2), november (2), august (2), archives (2), total (2), since (2), dynamicaccounting (2), about (2), address (2), follow (2), new (2), updates (2), update (2), past (2), couple (2), months (2), recently (2), big (2), work (2), operations (2), amount (2), home (2), continue (2), still (2), information (2), even (2), item_description (2), below (2), give (2), found (2), results (2), widget (2), showing (2), updated (2), using (2), something (2), 120 (2), costs (2), were (2), remember (2), paid (2), little (2), problem (2), means (2), actually (2), less (2), case (2), end (2), convert (2), nolock (2), tspvsalesdocumenthold (2), can (2), want (2), database (2), salespad (2), shows (2), holds (2), sales_batch (2), tspvsaleslineitem (2), needs (2), open (2), whether (2), original_num (2), combined (2), datetime (2), tspvsalesdocuementhistory (2), historical (2), sure (2), last (2), desc (2), tools (2), smart (2), into (2), between (2), some (2), out (2), packt (2), publishing (2), dateadd (2), datediff (2), fax (2), asc (2), coming (2), conjunction (2), task (2), centre (2), headers (2), analysis (2), any (2), contact (2), pordnmbr (2), frtamnt (2), way (2), link (2), them (2), charged (2), shipment (2), po_date (2), vendor_id (2), vendor_name (2), unit (2), tspvpurchaseorder (2), ponumber (2), poprctnm (2), discrepancy (2), experiences (2), microsoft (2), crm (2), design, website, comment, loading, collapse, bar, manage, subscriptions, reader, privacy, already, account, other, subscribers, february, march, may, june, october, month, 105, 499, feeds, site2, managing, outdated, aligning, websit, vpn, chromebook, robertamexy, mas, standar, latest, clear, users, activity, locks, transaction, level, posting, error, most, popular, workflow, tips, tricks, taskcentre, stored, procedures, alerts, receivables, valuations, financial, announcements, tags, enter, receive, notifications, search, older, entries, ron, folks, wanted, status, rldu, obviously, pretty, stagnant, over, received, promotion, director, title, taken, large, usually, these, maintain, much, involved, environment, post, hopefully, helpful, useful, never, kind, blogger, who, sake, counts, though, admit, hate, seeing, appreciate, understanding, continued, support, lately, cost_diff, used, equip, null, quantities, because, above, true, layer, first, second, onhand, costdiff, receipts, master, assumes, calculation, setting, markup, let, solution, written, compare, table, setup, sold, 125, made, comes, actual, stock, example, yes, read, right, while, wonderful, cause, call, import, sent, sheet, imported, masse, which, caused, many, products, their, apply, higher, selling, lower, margins, losing, money, smartconnect, soptype, quote, backorder, sopnumbe, hold_code, is_deleted, delete1, user_id, userid, last_update_on, char, holddate, varchar, time1, smalldatetime, cant_transfer, xferphol, cant_post, postphol, cant_fulfill, fufiphol, cant_print, prinphol, sop00100, object, date, ansi_nulls, quoted_identifier, create, tspvsalesdocumentholds, tells, execute, named, modify, modification, spvsalesdocumentholds, www, net, active, inactive, customer_name, is_dropship, quantity, notes, purchasing_status, sales_doc_id, needed, cannot, save, listing, back, ordered, drop, ship, linked, existing, needing, union, ever, wonder, workload, looks, service, staff, ytd, utilize, built, such, lists, writer, dive, deeper, excel, builder, nice, breakdown, databases, structured, teaches, victoria, yudin, should, writing, own, reports, tables, check, great, regards, cspornbr, docdate, ortrxamt, curtrxam, rm20101, custname, cntcprsn, stmtname, adrscode, address1, address2, address3, city, state, zip, phone1, phone2, phone3, slprsnid, pymtrmid, rm00101, invoicestatementto, spvcustomer, send, really, helped, aging, process, expressed, appreciation, friendly, reminder, one, thing, combines, details, repeated, lines, easily, cleaned, feel, free, help, payables_document, posteddt, posted_date, vendor_freight_amount, sales_freight_charged, freight_diff, tspvsoptopop, pm30200, https, 63015, aspx, associate, outgoing, charges, 3rd, party, carriers, invoiced, applicable, better, track, profitability, seems, flip, side, landed, suggestions, question, raised, jcohen, analyze, versus, billed, provided, there, invoice_date, receiptdate, pop_type, else, item_desc, po_qty, qtyorder, inv, user2ent, pop30310, pop30300, pop10110, int, replace, dollar, include, decimals, default, options, allows, tweak, off, pennies, tolerance, wish, alert, submitted, valuable, clerk, easy, mistake, system, charging, does, match, resources, skip,

Text of the page (random words):
real life dynamics user real life experiences with microsoft dynamics gp crm skip to content follow rss real life dynamics user real life experiences with microsoft dynamics gp crm home sql scripts views resources about me contact me september 14 2011 sql scripts vendor invoice pricing discrepancy 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 4 comments september 13 2011 sql scripts sop to pop freight analysis 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 4 comments september 12 2011 sql scripts invoices coming due next week 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 2 comments september 8 2011 packt publishing dynamics gp 2010 reporting 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 2 comments september 6 2011 sql scripts order count ytd by user 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 4 comments september 1 2011 sql scripts order count previous week by user 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 4 comments august 20 2011 sql script orders needing purchase 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 3 comments april 28 2011 sql view sales document holds 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 2 comments april 22 2011 sql script standard cost less than valuation cost 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 2 comments december 20 2010 what i have been up to lately 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 3 comments older entries search subscribe to updates enter your email address to follow this blog and receive notifications of new posts by email email address sign me up tags about me announcements financial from the community inventory inventory valuations payables purchase order processing receivables sales order processing sql sql alerts sql scripts sql stored procedures sql views taskcentre tips and tricks workflow most popular from the community 2 previous transaction level posting error sql scripts clear users and activity locks latest comments sql script standar on sql view item mas sql scripts vendor i on sql views dynamics gp q on sql views inventory g on sql views inventory robertamexy on sql scripts sop to pop f vpn for chromebook on from the community 7 aligning your websit on managing outdated inventory site2 on sql views dynamics gp dynamicaccounting ne on sql scripts invoices com dynamicaccounting ne on sql scripts order count rss feeds rss posts rss comments total views since november 2009 105 499 views archives archives select month september 2011 6 august 2011 1 april 2011 2 december 2010 1 october 2010 1 june 2010 6 may 2010 8 april 2010 13 march 2010 14 february 2010 1 january 2010 1 december 2009 1 november 2009 1 blog at wordpress com subscribe subscribed real life dynamics user join 34 other subscribers sign me up already have a wordpress com account log in now privacy real life dynamics user subscribe subscribed sign up log in report this content view site in reader manage subscriptions collapse this bar loading comments write a comment email required name required website design a site like this with wordpress com get started