SQL Scripts for Microsoft Dynamics GP: list of Open Payables Distributions

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of Payables distributions for transactions with a current transaction amount not equal to 0.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT PM.VCHRNMBR AS 'Voucher Number' ,PM.DSTSQNUM / 16384 AS 'Dist Seq No' ,PM.CNTRLTYP ,PM.CRDTAMNT AS 'Credit Amnt' ,PM.DEBITAMT AS 'Debit Amnt' ,GL.ACTNUMST AS 'Account Number' ,CASE WHEN PM.DISTTYPE = 1 THEN 'Cash' WHEN PM.DISTTYPE = 2 THEN 'Payable' WHEN PM.DISTTYPE = 3 THEN 'Discount Available' WHEN PM.DISTTYPE = 4 THEN 'Discount Taken' WHEN PM.DISTTYPE = 5 THEN 'Finance Charge' WHEN PM.DISTTYPE = 6 THEN 'Purchase' WHEN PM.DISTTYPE = 7 THEN 'Trade Discount' WHEN PM.DISTTYPE = 8 THEN 'Misc Charge' WHEN PM.DISTTYPE = 9 THEN 'Freight' WHEN PM.DISTTYPE = 10 THEN 'Taxes' WHEN PM.DISTTYPE = 11 THEN 'Writeoffs' WHEN PM.DISTTYPE = 12 THEN 'Other' WHEN PM.DISTTYPE = 13 THEN 'GST Disc' WHEN PM.DISTTYPE = 14 THEN 'PPS Amount' WHEN PM.DISTTYPE = 15 THEN '' WHEN PM.DISTTYPE = 16 THEN 'Round' WHEN PM.DISTTYPE = 17 THEN 'Realised Gain' WHEN PM.DISTTYPE = 18 THEN 'Realised Loss' WHEN PM.DISTTYPE = 19 THEN 'Due To' ELSE 'Due From' END AS 'Distribution Type' ,PM.CHANGED AS 'Changed' ,PM.USERID AS 'User ID' ,CASE WHEN PM.PSTGSTUS = 0 THEN 'Unposted' WHEN PM.PSTGSTUS = 1 THEN 'Posted' WHEN PM.PSTGSTUS = 2 THEN '' ELSE 'Unposted Cheque' END AS 'Posting Status' ,PM.VENDORID AS 'Vendor ID' ,PM.TRXSORCE AS 'Trx Source' ,PM.PSTGDATE AS 'Posting Date' ,PM.INTERID AS 'Intercompany ID' ,PM.CURNCYID AS 'Currency ID' ,PM.CURRNIDX AS 'Currency Index' ,PM.APTVCHNM AS 'Apply To Voucher' ,PM.DistRef AS 'Distribution Ref' FROM PM10100 PM --PM Distribution WORK OPEN (PM10100) INNER JOIN GL00105 GL --Account Index Master (GL00105) ON PM.DSTINDX = GL.ACTINDX WHERE VCHRNMBR IN ( SELECT VCHRNMBR FROM PM20000 --PM Transaction OPEN File (PM20000) WHERE CURTRXAM <> 0 )

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: List of Open Payables Transactions

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of open Payables transactions with a current transaction amount not equal to 0.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT VCHRNMBR AS 'Voucher Number' ,VENDORID AS 'Vendor ID' ,CASE WHEN DOCTYPE = 1 THEN 'Invoice' WHEN DOCTYPE = 2 THEN 'Finance Charge' WHEN DOCTYPE = 3 THEN 'Misc Charge' WHEN DOCTYPE = 4 THEN 'Return' WHEN DOCTYPE = 5 THEN 'Credit Memo' ELSE 'Payment' END AS 'Document Type' ,DOCDATE AS 'Document Date' ,DOCNUMBR AS 'Document No' ,DOCAMNT AS 'Document Amnt' ,CURTRXAM AS 'Current Trx Amnt' ,DISCAMNT AS 'Discount Amnt' ,BACHNUMB AS 'Batch Number' ,TRXSORCE AS 'Trx Source' ,BCHSOURC AS 'Batch Source' ,DISCDATE AS 'Discount Date' ,DUEDATE AS 'Due Date' ,PORDNMBR AS 'PO Number' ,WROFAMNT AS 'Write Off Amnt' ,TRXDSCRN AS 'Trx Description' ,HOLD AS 'Hold Status' ,CHEKBKID AS 'Chequebook ID' ,DINVPDOF AS 'Paid Off Date' ,POSTEDDT AS 'Posted Date' ,PTDUSRID AS 'Posted User ID' ,MODIFDT AS 'Modified Date' ,MDFUSRID AS 'Modified User ID' ,PRCHAMNT AS 'Purchases Amnt' ,TRDISAMT AS 'Trade Discount Amnt' ,MSCCHAMT AS 'Misc Charge Amnt' ,FRTAMNT AS 'Freight Amnnt' ,TAXAMNT AS 'Tax Amount' ,TTLPYMTS AS 'Total Payments' ,PYMTRMID AS 'Payment Terms ID' ,SHIPMTHD AS 'Shipping Method' ,TAXSCHID AS 'Tax Schedule ID' ,PCHSCHID AS 'Purchasing Schedule ID' ,FRTSCHID AS 'Freight Schedule ID' ,MSCSCHID AS 'Misc Schedule ID' ,PSTGDATE AS 'Posting Date' ,DISAVTKN AS 'Discount Avail Taken' ,Tax_Date AS 'Tax Date' ,PRCHDATE AS 'Purchase Date' FROM PM20000 --PM Transaction OPEN File (PM20000) WHERE CURTRXAM <> 0

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: List Open Purchase Order Lines

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of open purchase order lines from POs with a remaining sub total amount.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT PONUMBER AS 'PO Number' ,LineNumber AS 'Line Number' ,CASE WHEN POLNESTA = 1 THEN 'New' WHEN POLNESTA = 2 THEN 'Released' WHEN POLNESTA = 3 THEN 'Change Order' WHEN POLNESTA = 4 THEN 'Received' WHEN POLNESTA = 5 THEN 'Closed' ELSE 'Cancelled' END AS 'PO Line Status' ,CASE WHEN POTYPE = 1 THEN 'Standard' WHEN POTYPE = 2 THEN 'Drop Ship' WHEN POTYPE = 3 THEN 'Blanket' ELSE 'Blank Drop Ship' END AS 'PO Type' ,ITEMNMBR AS 'Item Number' ,ITEMDESC AS 'Item Description' ,VENDORID AS 'Vendor ID' ,UOFM AS 'Unit of Measure' ,QTYORDER AS 'Qty Ordered' ,UNITCOST AS 'Unit Cost' ,EXTDCOST AS 'Line Subtotal' ,TAXAMNT AS 'Tax Amount' FROM POP10110 --Purchase Order Line (POP10110) WHERE PONUMBER IN ( SELECT PONUMBER FROM POP10100 --Purchase Order Work (POP10100) WHERE REMSUBTO <> 0 ) GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: List Open Purchase Orders

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of open purchase orders.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT PONUMBER AS 'PO Number' ,CASE WHEN POSTATUS = 1 THEN 'New' WHEN POSTATUS = 2 THEN 'Released' WHEN POSTATUS = 3 THEN 'Change Order' WHEN POSTATUS = 4 THEN 'Received' WHEN POSTATUS = 5 THEN 'Closed' ELSE 'Cancelled' END AS 'PO Status' ,CASE WHEN STATGRP = 0 THEN 'Voided' WHEN STATGRP = 1 THEN 'Active' -- Includes New, Open & Modified ELSE 'Closed' -- Includes Cancelled & Closed END AS 'Status Group' ,CASE WHEN POTYPE = 1 THEN 'Standard' WHEN POTYPE = 2 THEN 'Drop Ship' WHEN POTYPE = 3 THEN 'Blanket' ELSE 'Blanket Drop Ship' END AS 'PO Type' ,DOCDATE AS 'Document Date' ,REMSUBTO AS 'Remaining Subtotal' ,SUBTOTAL AS 'Subtotal' ,TRDISAMT AS 'Trade Discount Amnt' ,FRTAMNT AS 'Freight Amnt' ,MSCCHAMT AS 'Misc Charge Smnt' ,TAXAMNT AS 'Tax Amnt' ,VENDORID AS 'Vendor ID' ,VENDNAME AS 'Vendor Name' ,PYMTRMID AS 'Payment Terms ID' ,DUEDATE AS 'Due Date' ,CREATDDT AS 'Created Date' ,MODIFDT AS 'Modified Date' ,BUYERID AS 'Buyer ID' ,CASE WHEN HOLD = 1 THEN 'On Hold' ELSE 'Open' END AS 'Hold Status' ,ONHOLDDATE AS 'Hold Date' ,Revision_Number AS 'Revision Number' ,TAXSCHID AS 'Tax Schedule ID' FROM POP10100 --Purchase Order Work (POP10100) WHERE STATGRP = 1 AND POSTATUS IN (1,2,3) AND REMSUBTO <> 0

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: Sales by Salesperson By Year

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of sales in the last five years summarised by salesperson:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT ISNULL(['RM Salesperson Master'].SLPRSNID, '* No Salesperson *') AS CustomerSalesperson ,RTRIM(LTRIM(RTRIM(ISNULL(['RM Salesperson Master'].SLPRSNFN, '')) + ' ' + ISNULL(['RM Salesperson Master'].SPRSNSLN, ''))) AS CustomerSalesPersonName ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) THEN SLSAMNT ELSE 0 END) AS SalesYear0 ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -1 THEN SLSAMNT ELSE 0 END) AS [SalesYear-1] ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -2 THEN SLSAMNT ELSE 0 END) AS [SalesYear-2] ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -3 THEN SLSAMNT ELSE 0 END) AS [SalesYear-3] ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -4 THEN SLSAMNT ELSE 0 END) AS [SalesYear-4] FROM ( SELECT CUSTNMBR ,DOCNUMBR ,RMDTYPAL ,DOCDATE ,GLPOSTDT ,CASE WHEN RMDTYPAL < 7 THEN SLSAMNT ELSE SLSAMNT * -1 END AS SLSAMNT ,SLPRSNID FROM RM20101 [sqlgreen]--[gptl=RM20101][/sqlgreen] WHERE RMDTYPAL <> 9 AND VOIDSTTS = 0 UNION ALL SELECT CUSTNMBR , DOCNUMBR , RMDTYPAL , DOCDATE , GLPOSTDT ,CASE WHEN RMDTYPAL < 7 THEN SLSAMNT ELSE SLSAMNT * -1 END AS SLSAMNT ,SLPRSNID FROM RM30101 [sqlgreen]--[gptl=RM30101][/sqlgreen] WHERE RMDTYPAL <> 9 AND VOIDSTTS = 0 ) AS ['Sales Data'] LEFT OUTER JOIN RM00301 AS ['RM Salesperson Master'] --RM Salesperson Master (RM00301) ON ['Sales Data'].SLPRSNID = ['RM Salesperson Master'].SLPRSNID GROUP BY ['RM Salesperson Master'].SLPRSNID ,['RM Salesperson Master'].SLPRSNFN ,['RM Salesperson Master'].SPRSNSLN GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: Sales by Customer By Year

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of sales in the last five years summarised by customer:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT ['RM Customer MSTR'].CUSTNMBR AS CustomerNumber ,['RM Customer MSTR'].CUSTNAME AS CustomerName ,['RM Customer MSTR'].CUSTCLAS AS CustomerClass ,['RM_Class_MSTR'].CLASDSCR AS CustomerClassDescription ,ISNULL(['RM Salesperson Master'].SLPRSNID, '* No Salesperson *') AS CustomerSalesperson ,RTRIM(LTRIM(RTRIM(ISNULL(['RM Salesperson Master'].SLPRSNFN, '')) + ' ' + ISNULL(['RM Salesperson Master'].SPRSNSLN, ''))) AS CustomerSalesPersonName ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) THEN SLSAMNT ELSE 0 END) AS SalesYear0 ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -1 THEN SLSAMNT ELSE 0 END) AS [SalesYear-1] ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -2 THEN SLSAMNT ELSE 0 END) AS [SalesYear-2] ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -3 THEN SLSAMNT ELSE 0 END) AS [SalesYear-3] ,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -4 THEN SLSAMNT ELSE 0 END) AS [SalesYear-4] FROM ( SELECT CUSTNMBR ,DOCNUMBR ,RMDTYPAL ,DOCDATE ,GLPOSTDT ,CASE WHEN RMDTYPAL < 7 THEN SLSAMNT ELSE SLSAMNT * -1 END AS SLSAMNT FROM RM20101 [sqlgreen]--[gptl=RM20101][/sqlgreen] WHERE RMDTYPAL <> 9 AND VOIDSTTS = 0 UNION ALL SELECT CUSTNMBR , DOCNUMBR , RMDTYPAL , DOCDATE , GLPOSTDT ,CASE WHEN RMDTYPAL < 7 THEN SLSAMNT ELSE SLSAMNT * -1 END AS SLSAMNT FROM RM30101 [sqlgreen]--[gptl=RM30101][/sqlgreen] WHERE RMDTYPAL <> 9 AND VOIDSTTS = 0 ) AS ['Sales Data'] INNER JOIN RM00101 ['RM Customer MSTR'] --RM Customer MSTR (RM00101) ON ['Sales Data'].CUSTNMBR = ['RM Customer MSTR'].CUSTNMBR LEFT OUTER JOIN RM00301 AS ['RM Salesperson Master'] --RM Salesperson Master (RM00301) ON ['RM Customer MSTR'].SLPRSNID = ['RM Salesperson Master'].SLPRSNID LEFT OUTER JOIN RM00201 AS ['RM_Class_MSTR'] --RM_Class_MSTR (RM00201) ON ['RM Customer MSTR'].CUSTCLAS = ['RM_Class_MSTR'].CLASSID GROUP BY ['RM Customer MSTR'].CUSTNMBR ,['RM Customer MSTR'].CUSTNAME ,['RM Customer MSTR'].CUSTCLAS ,['RM Salesperson Master'].SLPRSNID ,['RM Salesperson Master'].SLPRSNFN ,['RM Salesperson Master'].SPRSNSLN ,['RM_Class_MSTR'].CLASDSCR GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: Extract GL Period Balances

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was put together a while ago for a client who was migrating from Dynamics GP to another ERP system.

There are three sections to the script.

Firstly, a table is created in the DYNAMICS database into which data from all company databases will be populated:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE TABLE GLPeriodBalances ( INTERID VARCHAR(100), ACTINDX VARCHAR(100), ACTNUMST VARCHAR(100), YEAR1 VARCHAR(100), PERIODID VARCHAR(100), LEDGERID VARCHAR(100), PERDBLNC VARCHAR(100), ACTNUMBR_1 VARCHAR(100), ACTNUMBR_2 VARCHAR(100), ACTNUMBR_3 VARCHAR(100), ACCATNUM VARCHAR(100), DEBITAMT VARCHAR(100), CRDTAMNT VARCHAR(100) ) GO

Secondly, a script which can be run in each company database to populate the extract table in the DYNAMICS database:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
INSERT INTO DYNAMICS..GLPeriodBalances ( INTERID ,ACTINDX ,ACTNUMST ,YEAR1 ,PERIODID ,LEDGERID ,PERDBLNC ,ACTNUMBR_1 ,ACTNUMBR_2 ,ACTNUMBR_3 ,ACCATNUM ,DEBITAMT ,CRDTAMNT ) SELECT DB_NAME(), ['GL Period Balance'].ACTINDX ,['Account Index Master'].ACTNUMST ,['GL Period Balance'].YEAR1 ,['GL Period Balance'].PERIODID ,['GL Period Balance'].LEDGER_ID ,['GL Period Balance'].PERDBLNC ,['Account Index Master'].ACTNUMBR_1 ,['Account Index Master'].ACTNUMBR_2 ,['Account Index Master'].ACTNUMBR_3 ,['GL Period Balance'].ACCATNUM ,['GL Period Balance'].DEBITAMT ,['GL Period Balance'].CRDTAMNT FROM GL00105 AS ['Account Index Master'] --Account Index Master (GL00105) INNER JOIN GL10110 AS ['GL Period Balance'] --Account Current Summary Master (GL10110) ON ['Account Index Master'].ACTINDX = ['GL Period Balance'].ACTINDX GO

Thirdly, the script which is used to select data from the extract table in the DYNAMICS database:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT * FROM DYNAMICS..GLPeriodBalances GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: View to Return List of Payments and Linked Invoices

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script creates a SQL view which returns a list of payments and the invoices linked to them.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE VIEW uv_AZRCRV_InvoicesLinkedToPayments AS SELECT ['Payment Batch'].BACHNUMB AS 'Batch ID' ,['Payment Batch'].DOCDATE AS 'Payment Date' ,['Payment Batch'].DOCNUMBR As 'Cheque Number' ,['PM Transactions'].VCHRNMBR ,['PM Transactions'].VENDORID ,['PM Creditor Master'].VENDNAME ,CASE WHEN ['PM Transactions'].DOCTYPE = 1 THEN 'Invoice' WHEN ['PM Transactions'].DOCTYPE = 5 THEN 'Credit Note' WHEN ['PM Transactions'].DOCTYPE = 6 THEN 'Payment' END AS DOCTYPE ,['PM Transactions'].DOCDATE ,['PM Transactions'].VALUE ,['PM Transactions'].MODIFDT ,['PM Transactions'].PSTGDATE ,['PM Transactions'].POSTEDDT ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR FROM PM30200 AS ['Payment Batch'] WITH (NOLOCK) --PM Paid Transaction History File (PM30200) INNER JOIN PM10201 AS ['Remittance'] WITH (NOLOCK) --PM Payment Apply To Work File (PM10201) ON ['Payment Batch'].VCHRNMBR = ['Remittance'].PMNTNMBR INNER JOIN (SELECT ['PM Transaction Open'].VCHRNMBR ,['PM Transaction Open'].VENDORID ,['PM Distribution Work Open'].DSTINDX ,['PM Transaction Open'].DOCTYPE ,['PM Transaction Open'].DOCDATE ,CASE WHEN ['PM Distribution Work Open'].CRDTAMNT > 0 THEN ['PM Distribution Work Open'].CRDTAMNT * -1 ELSE ['PM Distribution Work Open'].DEBITAMT END AS 'Value' ,['PM Transaction Open'].MODIFDT ,['PM Transaction Open'].PSTGDATE ,['PM Transaction Open'].POSTEDDT FROM PM20000 AS ['PM Transaction Open'] WITH (NOLOCK) --PM Transaction OPEN File (PM20000) INNER JOIN PM10100 AS ['PM Distribution Work Open'] WITH (NOLOCK) --PM Distribution WORK OPEN (PM10100) ON ['PM Distribution Work Open'].VCHRNMBR = ['PM Transaction Open'].VCHRNMBR AND ['PM Distribution Work Open'].CNTRLTYP = ['PM Transaction Open'].CNTRLTYP UNION ALL SELECT ['PM Transaction History'].VCHRNMBR ,['PM Transaction History'].VENDORID ,['PM Distribution Hist'].DSTINDX ,['PM Transaction History'].DOCTYPE ,['PM Transaction History'].DOCDATE ,CASE WHEN ['PM Distribution Hist'].CRDTAMNT > 0 THEN ['PM Distribution Hist'].CRDTAMNT * -1 ELSE ['PM Distribution Hist'].DEBITAMT END AS 'Value' ,['PM Transaction History'].MODIFDT ,['PM Transaction History'].PSTGDATE ,['PM Transaction History'].POSTEDDT FROM PM30200 AS ['PM Transaction History'] WITH (NOLOCK) INNER JOIN PM30600 AS ['PM Distribution Hist'] WITH (NOLOCK) --PM Distribution History File (PM30600) ON ['PM Distribution Hist'].VCHRNMBR = ['PM Transaction History'].VCHRNMBR AND ['PM Distribution Hist'].DOCTYPE = ['PM Transaction History'].DOCTYPE ) AS ['PM Transactions'] ON ['PM Transactions'].VCHRNMBR = ['Remittance'].VCHRNMBR AND ['PM Transactions'].DOCTYPE = ['Remittance'].DOCTYPE INNER JOIN GL00105 AS ['Account Index Master'] WITH (NOLOCK) --Account Index Master (GL00105) ON ['Account Index Master'].ACTINDX = ['PM Transactions'].DSTINDX INNER JOIN GL00100 AS ['Account Master'] WITH (NOLOCK) --Breakdown Account Master (GL00100) ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX INNER JOIN PM00200 AS ['PM Creditor Master'] WITH (NOLOCK) --PM Vendor Master File (PM00200) ON ['PM Creditor Master'].VENDORID = ['PM Transactions'].VENDORID GO GRANT SELECT ON uv_AZRCRV_InvoicesLinkedToPayments TO DYNGRP GO

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: Delete Orphaned Vendor EFT Details

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will delete the orphaned vendor EFT details left over after a vendor record has been deleted, which was due to a but in Dynamics GP (which may be fixed by now).

I’ve previously posted about a SQL trigger to delete the EFT when the vendor card is deleted, but that wouldn’t remove existing orphaned records which this script will do.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
DELETE ['Address Electronic Transfer Funds Master'] FROM SY06000 AS ['Address Electronic Transfer Funds Master'] --Address Electronic Funds Transfer Master (SY06000) LEFT JOIN PM00300 AS ['PM Creditor Address Master'] --PM Address MSTR (PM00300) ON ['PM Creditor Address Master'].VENDORID = ['Address Electronic Transfer Funds Master'].VENDORID AND ['PM Creditor Address Master'].ADRSCODE = ['Address Electronic Transfer Funds Master'].ADRSCODE WHERE ['PM Creditor Address Master'].VENDORID IS NULL

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created

SQL Scripts for Microsoft Dynamics GP: Select Chart of Accounts

Microsoft Dynamics GPThis script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created to allow a client to quickly export the entire chart of accounts.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT ['Account Index Master'].ACTNUMST AS 'Account' ,['Account Master'].ACTDESCR AS 'Account Description' ,['Account Category Master'].ACCATDSC AS 'Account Category' ,CASE ['Account Master'].PSTNGTYP WHEN 0 THEN 'Balance Sheet' ELSE 'Profit and Loss' END AS 'Posting Type' ,CASE ['Account Master'].TPCLBLNC WHEN 0 THEN 'Debit' ELSE 'Credit' END AS 'Typical Balance' ,USERDEF1 AS 'User-Defined 1' ,USERDEF2 AS 'User-Defined 1' ,USRDEFS1 AS 'User-Defined 1' ,USRDEFS2 AS 'User-Defined 4' FROM GL00100 AS ['Account Master'] --Breakdown Account Master (GL00100) INNER JOIN GL00105 AS ['Account Index Master'] --Account Index Master (GL00105) ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX INNER JOIN GL00102 AS ['Account Category Master'] --Account Category Master (GL00102) ON ['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM

Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index

SQL Scripts for Microsoft Dynamics GP
Sales Transactions (Work) Against a Specific Site
Update Site Descriptions From CSV
Select All Primary Keys and Generate ALTER Script
Export Open/History PM Transactions After a Specified Date
Migrate Vendor Emails from Active Docs to Standard Email Fields
Update Inventory Accounts from Item Class
Update Accounts Payable Distribution on Work Status PM Transactions from Posting Account Setup
Update Inventory Distribution on Work Status Purchase Orders from the Item Card
Update Accrued Purchases Distribution on History Receipts from Posting Account Setup
Insert National Accounts from CSV
Import Site Bins From CSV
Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
Assign All Items to All Site Bins
SQL Trigger on PO invoice Insert to Change GL posted Date
PO Receipt History View
Workflow Assignment Review
Sales Invoice Query
Assembly Transaction Quantities Required
Generate Standard Cost Update Macro from Text File Import
Validate and Insert/Update Vendor Emails from a Text File
Update Min Order Qty and Average Lead Time on Vendor Item From Text File
Update Mfg Cost Accounts from Mfg Item Class Setup
Create Macro to Delete Items
Update Item Resource Planning on Item Quantity Master from Text File
Update Item Engineering File from a Text File
Insert Mfg BOMs from Text File
Insert Manufacturing Routings from Text File
Return Top Level BOM for Manufacturing Orders
Simple RMA Audit
Allow Workflow Originator to be an Approver
Change Web Service URi
Prefix Companies Names with System Designator
Purchased Items With Serial Numbers and Linked Sales Transactions
Script to Set Transactions as Included on VAT Daybook Return
Get Alpha Characters from an Alphanumeric String
Get Numeric Characters from an Alphanumeric String
Select Next Temporary Creditor ID
Compare Ship To Address on Work Sales Trx Against Customer
Update Ship To Name on Work Sales Transactions to Match the Customer Name
Table Function to Split String on Delimiter
Payment Run Apply Query
Remove Multicurrency from Sales Transactions
Delete Corrupt Extended Pricing Data
Insert Extended Pricing Price Sheet Header
Insert Extended Pricing Price Sheet UofM Work
Insert Extended Pricing Price Sheet Assignments
Update Item Replenishment Method for Manufacturing
Round Extended Pricing Price Sheet Item Value
Select Duplicate Extended Pricing Price Sheet Work Records
Check for Corrupt Extended Pricing Records
Return Items with Incorrect Quantities
List of PM Invoices for Vendors with POs
PO Commitment Detail
Custom Purchase Order Email Notification to Originator on Workflow Final Approval
Change Vendor Change Approvals Joins and Fields
Add Joins and Fields to PM Document Approval Notification Emails
SQL View to Return Quantity Available
SQL View to Return Category Linked to Segment 3 in COA
SQL View to Return Purchase Orders
SQL Script to Return PO Receipts
SQL View to Create Division Tree for Management Reporter
SQL View to Create Division, including UDF 3 and 4, Tree for Management Reporter
Select a List of Vendor Addresses
SQL View to Return PO Commitment Detail
SQL View to Return List of Posted Vendor Document Numbers
Verify PM Batches Exist
SQL Function To Return Approver
Insert Creditor Item Numbers
Change Email Notification Assignment
RM Aged Debt Report
Sales Line Items
List Bank Accounts with Linked GL Accounts
Set Account Categories To User-Defined Field 2
Check Posting Type for Account (Segment 2)/Account Category Combinations
List Tax Detail Transactions
Select Tax Details and Related G/L Accounts
Update Account Description by Adding 3rd Segment Description
Update Segment Descriptions from Other Database
Return Opening Balance for Period of Supplied Date
Select Debit, Credit and Net Change for All Accounts in Date Range
Select All Pending Prepayments
Available Stock for All Items
Item Report
List of Exchange Rates
List of Active Fixed Assets
Set New Vendor On Hold if EFT Exists
Set New Vendor On Hold if EFT Exists
List Taxes Linked to GL Accounts
Upload and Verify Tax Commodity Codes
Verify Tax Detail Assigned to Vendor
Copy Workflow from Source to Destination Database
Copy Workflow Calendar from Source to Destination Database
Copy Email Messages from a Source to Destination Database
Select Chart of Accounts
Delete Orphaned Vendor EFT Details
View to Return List of Payments and Linked Invoices
Extract GL Period Balances
Sales by Customer By Year
Sales by Salesperson By Year
List Open Purchase Orders
List Open Purchase Order Lines
List of Open Payables Transactions
list of Open Payables Distributions
List GL Transactions
List GL Accounts With Notes
List General Ledger Transactions (Excluding Year End Journals)
View for Payables Transactions Extract
Extract Payables Transactions from All Companies
Activate Horizontal Scroll Bars for All Existing Users
Set Vendor On Hold If EFT Details Changed
Trigger to Activate Horizontal Scroll Bars for New Users
Set Vendor On Hold When Created