25 years and still loving it!

It’s incredibly hard to believe but exactly 25 years ago today I started at a little known software company of 150 people called Great Plains Software.  Fargo wasn’t exactly the Silicon Valley, but I certainly was excited to start my new job in tech-support.  With a six-week training schedule in front of me, the thought of getting paid for learning, to this recent college graduate, seemed too good to be true.

On a more personal note, June 15, 1987 also serves as the day that I met my wife.  And I claim we had the first 100% Great Plains baby;  We met at Great Plains, started dating, got engaged and married and had a child all while working for a very young and vibrant company.

There’s been much learning for me personally and professionally and an incredible amount of change in the industry.  One of Great Plains Software’s claim to fame was being one of the first Hardisk Accounting Software packages in the market, as it ran on a 5 MB Bernoulli box.  The “original” Dynamics, with it’s ability to run on both Mac and Windows OS and the upcoming Web/Cloud capabilities of Microsoft Dynamics GP 2013 highlight the largest technologies changes that I’ve seen.

It’s often been said that change is the constant in our industry and I’d have to agree.  Since software comes from the minds of people, there will always be the next great technology changes.  Zach Nelson of Netsuite was quoted as saying “I think the cloud is the last great technology architecture”.  Well, I couldn’t DISAGREE with him more.  In fact, it bodes of arrogance in my view.  Today’s world of mobiletablet devices with a “there’s an app for that” thinking is directly contrary to having ALL your software in the cloud.  You see, the point is not “I sell hammers, therefore the best solution for driving in a screw is obviously a hammer”.  The value that software plus a partners’ domain expertise provides is the ability to listen to customers and deliver a compelling solution based on their needs.  That has also been a constant.

With all the change, the best thing about my time in the industry, however, has definately been the opportunity to travel and meet so many wonderful people.  It’s the reason I get up everyday and think about how we can be better at what’s important to our customers.  Convergence and our Partner Conferences continue to invigorate me as we applaud our past accomplishments and look towards the future.

Well, here’s to the future . . . perhaps not another 25 years for me personally, but the not too distant future looks equally as enjoyable as the past 25 years.

Errol

Taken from:
25 years and still loving it!

June 15, 2012 · Errol Schoenfish · No Comments
Tags: , ,  · Posted in: Blogs I Follow Total Views: 237

Understanding Support Debugging Tool Logging Modes

David Meego - Click for blog homepageThis is the first in a series of articles that are designed to help you understand how the Support Debugging Tool works and how you can leverage its different features. In the past, I have described features without explaining how these features can be used and what the differences between different modes is. With this series I hope to remedy that situation.

 

History

The Support Debugging Tool was originally created for Microsoft Business Solutions – Great Plains version 8.0 in September 2006 to solve a particular case. That case had a situation which could not be reproduced on demand, but the resulting incorrect data could be seen in the tables. The basic concept of the tool was to combine logging with triggers and scripts. Below is the high level flow:

  • Start Logging after logging in.
  • Register triggers on event, such as table being saved.
  • When trigger event occurs run a script which looks for the error.
  • If error has occurred, keep logs and notify user.
  • If error has not occurred, delete logs and start logging again.

When the tool was deployed on site, it took 4 days before the error occurred. The tool provided the support team with the logs leading up to the error occurring. This allowed the script to be identified and the cause of the error to be found and fixed.

 

Types of Logging

Most consultants know that Microsoft Dynamics GP can create a log of all communication between a Dexterity based application and SQL Server. This is the DEXSQL.LOG file which can be enabled using settings in the Dex.ini configuration file (KB 850996).

If you are a Dexterity developer, you will know that the Dexterity Script Debugger can create a log of all scripts executed with their parameters and hierarchy. Also Dexterity can create a profiler document containing all scripts executed and table access performed, with performance statistics such as number of times executed and length of time taken. These files have the default names of Script.log and Profile.txt. The Script Debugger can be enabled in runtime to make these logging features available (KB 850487).

The Support Debugging Tool takes advantage of the fact that all three of these types of logging can be enabled programmatically without changing the Dex.ini file or using the Debug menu (KB 850498). These three types of logging have been available since the Support Debugging Tool was first created.

With the release of Build 16 of the Support Debugging Tool, SQL Profile Tracing (activity at the SQL Server, including statements inside stored procedures) and Macro Recording (user activity at the Graphical User Interface) were added as new types of logging. These extra types of logging need to be enabled before they can be used. Once the initial setup is completed, they work just like the other types of logging.

In summary: The Support Debugging Tool supports the following types of logging:

  1. DEXSQL.LOG – Communication between the application and SQL Server
  2. Script.log – Dexterity scripts executed with parameters
  3. Profile.txt – Dexterity script and table performance data
  4. Trace.trc – Activity at the SQL Server
  5. Macro.mac – Activity at the User Interface

Now that we have all the background information covered we can discuss the main topic of this article.

 

Logging Modes 

The Support Debugging Tool supports three logging modes:

  1. Individual Logging Control
  2. Manual Logging Mode
  3. Automatic Debugger Mode 

The following sections will explain the differences between the logging modes and when and how they should be used:

  

Individual Logging Control

Individual Logging Control provides the ability to turn on and off the individual logging types separately and to specify the paths for the logs. It was created as part of the development process towards the other logging modes and is really only still available as it is useful when developing and testing the Support Debugging Tool. With build 16 of the Support Debugging Tool, the controls for Individual Logging Control were moved into a secondary window (click Logging Options from the main Support Debugging Tool window) as this is not the recommended method of using logging within the tool.

Note: Individual Logging Control is only provided for development and testing purposes, and is not recommended for normal usage. This functionality might be hidden entirely in the future.

 

Manual Logging Mode

Manual Logging Mode expands on Individual Logging Control, by allowing the logging to be turned on and off with a single mouse click, menu selection or key press. The logs are automatically User, Company, date and time stamped and are stored in a central location. Manual Logging Mode can be turned on and off from the main Support Debugging Tool window (available from the application level or window level Tools menus, or by pressing Ctrl-D).

Manual Logging Mode can also be turned on and off from the application level or window level Tools menus or using Ctrl-Shift-F9 and Ctrl-Shift-F10.

Manual Logging Mode should be used when you wish to capture the logs for a specific event or process. Simply get to the point just before the process or event and start the logging, then turn off the logging as soon as possible after the process or event.

This mode of logging is great for capturing the events that cause an error message, performance problem or data error. It also has the additional benefits of not needing to exit the application to turn on logging, not needing any changes made to the Dex.ini file (so it works well on Terminal Server/Citrix environments) and only capturing the minimum amount of data to be analysed.

Note: Selection of which logging types are used with Manual Logging Mode is made from the General Tab of the Administrator Settings window. Advanced Mode Features must be enabled from the Dex.ini Settings window to access the Administrator Settings window.

 

Automatic Debugger Mode

So what happens when you don’t know what process or event is causing your issue? How do you know when to start and stop the logging? 

This is where Automatic Debugger Mode comes into the picture. Remember the basic concept from the start of this article? That concept is implemented in the Support Debugging Tool as Automatic Debugger Mode.

With Automatic Debugger Mode you create Triggers. These triggers can be based against almost any event in the application: Table events, Function, Procedure and Script events and more… 

The trigger has a Conditional Script (Dexterity sanScript) which is executed when the event occurs and the trigger fires. The script can perform whatever commands are needed to confirm if the error condition we are looking for has occurred.

If the error condition has occurred, the logs are stored and various actions can be selected.

If the error condition has not occurred, the logs are erased and the trigger waits for the next occurrence of the event. 

Automatic Debugger Mode is designed to help find those non-reproducible issues where you can see the result of something happening but are not sure of the steps to make it happen.

 

Summary 

  • Use Manual Logging Mode when you want logging for a known event.
  • Use Automatic Debugger Mode when you want logging leading up to an unknown event.
  • Don’t use Individual Logging Control.

  

In the next article we will discuss the different ways to turn Manual Logging Mode and Automatic Debugger Mode on and off. 

David

More:
Understanding Support Debugging Tool Logging Modes

June 14, 2012 · David Musgrave · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 325

Microsoft Dynamics GP2013 Partner Training Rundown

We have three classroom training opportunities for partners.  Microsoft Dynamics GP 2013 will be a game changer in the market.  Get ready and be part of making the future. 

Microsoft Dynamics GP 2013 Technical Airlift

http://gptechnicalairlift.com/

This training is targeted toward Consultants and Developers.  Registration numbers are growing and we are limited to 400 attendees, so register soon.  Also there is another event happening in Fargo, so book your hotel ASAP.

 

JumpStart Training for Microsoft Dynamics GP 2013

This training is targeted toward Consultants.  With the new web client, consultants need to understand implementing in a web environment.  This one day training will get you up to speed on topics like IIS, web services, firewalls and many other topics.

Microsoft Dynamics GP 2013 Presales Readiness

Times have changed and the way we sell has to change as well.  This one day training will get you ready to tell the Microsoft Dynamics GP story and show the product off.  After attending this training you will be able to close more deals and will have guidence of changes you can make in your organization to grow.

On September 11th we have this free day class in Fargo and October 16th in Seattle.  I would recommend going to one of these if possible.  We are working on 4 other cities with the possibility of more.  Stay tuned for more locations.

All events are listed here.  http://gptechnicalairlift.com/

Originally posted here:
Microsoft Dynamics GP2013 Partner Training Rundown

June 12, 2012 · Pam Misialek · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 254

Importing MDA with SmartConnect/eConnect

I have been using SmartConnect a lot recently, and its an incredibly powerful tool – there is nothing you can’t do with it. So gear up for many posts about it.

Recently we had the requirement to setup automated import of GL Transactions with MDA into GP, where the customer was using Wennsoft Equipment Management Series. As eConnect does not support addition of MDA lines with GL transactions, neither does SmartConnect which is built on top of eConnect. Additionally, Wennsoft has its own MDA tables which were required to be populated, and linked to the GL transactions.

Also, as our main aim was setting up automated integrations, and Integration Manager isn’t really built for automating imports (although possible using Macros – see How to schedule Dynamics GP to automatically log in and run an Integration Manager integration and here Weekly Dynamic: Automating Integrations) – we decided to go for SmartConnect.  

SmartConnect allows execution of SQL commands on both document and map inserts, which solved the problem. We wrote SQL code to insert the MDA lines with each GL transaction, and also make the associated imports into the relevant Wennsoft tables. The resulting solution is so simple – that you’d be surprised.

Below are the steps we followed, and the associated SQL scripts (built with the help of Rubal). If you are using eConnect, you would want to refer to the stored procedure below, and use in the GL Insert Post processing routine.

First created a GL Import Integration in Smart Connect, and mapped the required fields.

 

image

Add the MDA Code in the Description field with a distribution line

image

Create a new task in Smart Connect that runs if the document insert succeeds. From here call the Stored procedure that would do the inserts related to MDA. The two stored procedures used are listed below.

 

image

Finally with a couple of clicks setup the schedule for the import!

image

Please use these stored procedures only for your reference, and test them in your test environment. 
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--sp_Insert_Equipment_MDA_for JE 3447, 'EQUIP'

CREATE PROC dbo.sp_Insert_Equipment_MDA_for_JE
@jrnentry INT ,
@mdaGroup NVARCHAR(15)
AS
DECLARE @actindex INT
DECLARE @DTA_Index NVARCHAR(25)
DECLARE @amount NUMERIC(19, 5)
DECLARE @date DATETIME
DECLARE @mdaCode NVARCHAR(15)


DECLARE @CurrentSeqNum AS INT ,
@NextSeqNumber AS INT ,
@fake AS BIT


SET @CurrentSeqNum = 0
SET @fake = 1
SET @CurrentSeqNum = 0
SELECT TOP 1
@NextSeqNumber = ( SQNCLINE )
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE > @CurrentSeqNum
ORDER BY SQNCLINE ASC
SET @CurrentSeqNum = @NextSeqNumber

--select @NextSeqNumber, '1'
IF ( @NextSeqNumber IS NOT NULL )
BEGIN
WHILE ( @fake = 1 )
BEGIN
-- Check if mda codes exist and account has mda group assigned
SELECT @actindex = actindx ,
@mdaCode = RTRIM(DSCRIPTN)
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE = @CurrentSeqNum
--select @actindex as acc, @mdaCode as code
IF EXISTS ( SELECT GROUPID
FROM DTA00300
WHERE ACTINDX = @actindex
AND GROUPID = @mdaGroup )
AND EXISTS ( SELECT *
FROM DTA00200
WHERE CODEID = @mdaCode )
BEGIN
--If yes,get values and insert MDA
SELECT @DTA_Index = REPLACE(DTA_Index, '.', '')
+ 'N' ,
@date = TRXDATE
FROM GL10000
WHERE JRNENTRY = @jrnentry
SELECT @amount = ( DEBITAMT - CRDTAMNT )
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE = @CurrentSeqNum
--select @DTA_Index as dt, @amount am

EXECUTE sp_InsertEquipmentMDALines @jrnentry,
@actindex, @DTA_Index, @CurrentSeqNum,
@mdaGroup, @amount, @date, @mdaCode

END

--Get next line
SET @NextSeqNumber = NULL
SELECT TOP 1
@NextSeqNumber = ( SQNCLINE )
FROM GL10001
WHERE JRNENTRY = @jrnentry
AND SQNCLINE > @CurrentSeqNum
GROUP BY SQNCLINE
ORDER BY SQNCLINE ASC
--select @NextSeqNumber, 'next'
--if end of transaction, Stop processing
IF @NextSeqNumber IS NULL
BEGIN
--select 'break'
BREAK
END
ELSE
BEGIN
SET @CurrentSeqNum = @NextSeqNumber
SET @actindex = 0
SET @DTA_Index = ''

SET @amount = 0
SET @mdaCode = ''
END
END
END
 
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC dbo.sp_InsertEquipmentMDALines
@jrnentry INT ,
@actindex INT ,
@DTA_Index NVARCHAR(25) ,
@SEQNUMBR INT ,
@mdaGroup NVARCHAR(15) ,
@amount NUMERIC(19, 5) ,
@date DATETIME ,
@mdaCode NVARCHAR(15)

AS
INSERT INTO dbo.DTA10100
( DTASERIES ,
DTAREF ,
ACTINDX ,
SEQNUMBR ,
GROUPID ,
DTA_GL_Reference ,
DOCNUMBR ,
RMDTYPAL ,
GROUPAMT ,
JRNENTRY ,
TRXDATE ,
PSTGSTUS
)
VALUES ( 2 ,
@DTA_Index ,
@actindex ,
@SEQNUMBR ,
@mdaGroup ,
'' ,
'' ,
0 ,
@amount ,
@jrnentry ,
@date ,
1
)



INSERT INTO dbo.DTA10200
( DTASERIES ,
DTAREF ,
ACTINDX ,
SEQNUMBR ,
GROUPID ,
CODEID ,
DOCNUMBR ,
RMDTYPAL ,
POSTDESC ,
DTAQNTY ,
CODEAMT ,
TRXDATE
)
VALUES ( 2 ,
@DTA_Index ,
@actindex ,
@SEQNUMBR ,
@mdaGroup ,
@mdaCode ,
'' ,
0 ,
'' ,
0 ,
@amount ,
@date
)

-- This part is only required if you are using Wennsoft
INSERT INTO dbo.EQSB1010
( DTASERIES ,
DTAREF ,
ACTINDX ,
SEQNUMBR ,
SEQUENCE1 ,
Equipment_MDA_Code ,
Secondary_MDA_Group ,
Secondary_MDA_Code ,
EQS_Cost_Code ,
POSTDESC ,
DTA_GL_Reference ,
CODEAMT ,
DTAQNTY ,
Specification_Index

)
VALUES ( 2 , -- DTASERIES - smallint
@DTA_Index , -- DTAREF - char(25)
@actindex , -- ACTINDX - int
@SEQNUMBR , -- SEQNUMBR - int
1.00000 , -- SEQUENCE1 - numeric
@mdaCode , -- Equipment_MDA_Code - char(15)
'' , -- Secondary_MDA_Group - char(15)
'' , -- Secondary_MDA_Code - char(15)
'' , -- EQS_Cost_Code - char(15)
'' , -- POSTDESC - char(51)
'' , -- DTA_GL_Reference - char(25)
@amount , -- CODEAMT - numeric
0 , -- DTAQNTY - numeric
0 -- Specification_Index - int

)
 

Visit site:
Importing MDA with SmartConnect/eConnect

June 11, 2012 · Jivtesh Singh · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 638

Steps to disable third-party products or temporarily disable additional products in Dynamics GP

In the last couple of weeks I have seen a couple of GP consultants / advanced users try and disable products the long way, manually editing the dynamics.set file – Steps to disable third-party products or temporarily disable additional products in the Dynamics.set file in Microsoft Dynamics GP

Although this works – there is a much easy way to do this! Use the Dictionary control window in Support debugging tool. I personally think, it takes letter time to install Support debugging tool, then to do this manually – plus you don’t have to worry about making mistakes. SDT also takes a backup of the Dynamics.set file automatically. 

The Dictionary Control window, allows dictionaries to have their triggers and alternate or modified windows to be disabled temporarily or until re-enabled.

 

image

image

This can be used for troubleshooting issues without having to manually edit the Dynamics.set launch file and restart the application. Here is an example from David Musgrave’s blog.

Check out a lot more posts about the support debugging tool here – GPWindow.com – Dynamics GP- TOOLS FOR GP > SUPPORT DEBUGGING TOOL

More:
Steps to disable third-party products or temporarily disable additional products in Dynamics GP

June 10, 2012 · Jivtesh Singh · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 260

Canadian Payroll Midyear Tax Update has released.

The Canadian Payroll 2012 Midyear tax update has released for Microsoft Dynamics GP 10 and 2010.

The changes in this update include Ontario and Nova Scotia.

Once the update is installed the Last Tax Update field in the Payroll Control Setup – Canada window (Microsoft Dynamics GP menu >> Tools >> Setup >> Payroll – Canada >> Control) should be 06/15/2012.

Links to site:

GP 10
https://mbs.microsoft.com/customersource/downloads/taxupdates/cagpye10.htm?printpage=false

GP 2010
https://mbs.microsoft.com/customersource/downloads/taxupdates/cagptuye2010.htm?printpage=false

Thanks
Terry Heley

View original post here:
Canadian Payroll Midyear Tax Update has released.

June 5, 2012 · Terry Heley MSFT · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 219

Dynamics GP SOP line items with serial numbers and comments

Based on some reader requests, this new view is a combination of  my All SOP Line Items view and the recently posted script showing how to get a concatenated list of serial numbers in SQL. This new view will return all posted (history) or unposted (open) Sales Order Processing line items with the associated serial numbers (or lot numbers). The serial/lot numbers for each line will be concatenated into one field. The results will also include the header and line item comments.

For more Dynamics GP SOP scripts please visit my SOP SQL Views page. Or take a look at my GP Reports page for additional reporting links. SOP table information can be found on this page.

CREATE VIEW view_SOP_Line_Items_Serial
AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_SOP_Line_Items_Serial
-- Created on June 4, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates please see http://victoriayudin.com/gp-reports/
-- All line items for posted and unposted SOP transactions with serial and/or lot numbers
-- Returns Functional amounts only
-- Component Items are not taken into consideration
-- Fields that can have different values on the document header and
-- line item are both returned prefaced by 'Header' or 'Line'
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT S.*,
coalesce(C.CMMTTEXT,'') Line_Item_Comment,
coalesce(SN.Serial_Lot_Numbers,'') Serial_Lot_Numbers,
coalesce(HC.CMMTTEXT,'') Header_Comment

FROM  --sop lines
(SELECT Doc_Status = 'Open',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.STATE Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.STATE Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP10100 H
INNER JOIN SOP10200 L
	ON H.SOPTYPE = L.SOPTYPE AND H.SOPNUMBE = L.SOPNUMBE

UNION

SELECT Doc_Status = 'History',
H.SOPNUMBE SOP_Number, H.SOPTYPE SOP_Type,
H.DOCDATE Document_Date, H.GLPOSTDT GL_Posting_Date,
H.ORDRDATE Order_Date, H.DUEDATE Due_Date,
H.MSTRNUMB Master_Number,
CASE H.PSTGSTUS
  WHEN 0 THEN 'Unposted'
  WHEN 2 THEN 'Posted'
  ELSE 'Error'
  END Posting_Status,
H.CUSTNMBR Customer_ID, H.CUSTNAME Customer_Name,
H.CSTPONBR Customer_PO, H.BACHNUMB Batch_Number,
H.LOCNCODE Header_Site_ID, L.LOCNCODE Line_Site_ID,
CASE H.VOIDSTTS
  WHEN 0 THEN 'Not Voided'
  WHEN 1 THEN 'Voided'
  ELSE ''
  END Void_Status,
H.SLPRSNID Header_Salesperson, L.SLPRSNID Line_Salesperson,
H.SALSTERR Header_Territory, L.SALSTERR Line_Territory,
H.PYMTRMID Payment_Terms_ID,
H.SHIPMTHD Header_Shipping_Method, L.SHIPMTHD Line_Shipping_Method,
H.PRBTADCD Bill_To_Address_ID,
H.PRSTADCD Header_Ship_To_Address_ID, H.ShipToName Header_Ship_To_Name,
H.ADDRESS1 Header_Address_1, H.ADDRESS2 Header_Address_2,
H.ADDRESS3 Header_Address_3, H.CITY Header_City,
H.STATE Header_State, H.ZIPCODE Header_Zip_Code,
H.COUNTRY Header_Country,
L.PRSTADCD Line_Ship_To_Address_ID, L.ShipToName Line_Ship_To_Name,
L.ADDRESS1 Line_Address_1, L.ADDRESS2 Line_Address_2,
L.ADDRESS3 Line_Address_3, L.CITY Line_City,
L.STATE Line_State, L.ZIPCODE Line_Zip_Code,
L.COUNTRY Line_Country,
H.DOCAMNT Total_Document_Amount, H.MRKDNAMT Total_Markdown_Amount,
H.SUBTOTAL Document_Subtotal,
H.FRTAMNT Freight_Amount, H.MISCAMNT Misc_Amount,
H.TAXAMNT Tax_Amount, H.CURNCYID Currency_ID,
H.ReqShipDate Header_ReqShipDate, L.ReqShipDate Line_ReqShipDate,
H.USER2ENT User_to_Enter,
H.COMMNTID Header_Comment_ID, L.COMMNTID Line_Comment_ID,
L.LNITMSEQ Line_Item_Sequence, L.CMPNTSEQ Component_Sequence,
CASE L.NONINVEN
  WHEN 0 THEN 'Inventory'
  WHEN 1 THEN 'Non-Inventory'
  ELSE ''
  END Item_Type,
L.ITEMNMBR Item_Number, L.ITEMDESC Item_Description,
L.QUANTITY Quantity, L.UOFM U_of_M, L.QTYBSUOM Qty_in_Base_U_of_M,
L.QTYREMAI Qty_Remaining, L.UNITPRCE Unit_Price,
L.XTNDPRCE Extended_Price,
CASE L.MRKDNTYP
  WHEN 0 THEN 'Percentage'
  WHEN 1 THEN 'Amount'
  ELSE ''
  END Markdown_Type,
L.MRKDNAMT Markdown_Amount, L.MRKDNPCT/100 Markdown_Percentage,
L.TRDISAMT Trade_Discount_Amount,
L.UNITCOST Unit_Cost, L.EXTDCOST Extended_Cost

FROM SOP30200 H
INNER JOIN SOP30300 L
	ON H.SOPTYPE = L.SOPTYPE AND H.SOPNUMBE = L.SOPNUMBE) S --sop lines

LEFT OUTER JOIN
(SELECT p.SOPNUMBE, p.SOPTYPE, p.LNITMSEQ,
LEFT(p.serial_numbers, len(p.serial_numbers)-1) Serial_Lot_Numbers
FROM
 (SELECT p1.SOPNUMBE, p1.SOPTYPE, p1.ITEMNMBR, p1.LNITMSEQ,
  (SELECT coalesce(rtrim(SERLTNUM) + ', ','')
   FROM SOP10201 p2
   WHERE p1.SOPNUMBE = p2.SOPNUMBE and p1.SOPTYPE = p2.SOPTYPE
      and p1.ITEMNMBR = p2.ITEMNMBR and p1.LNITMSEQ = p2.LNITMSEQ
   ORDER BY SOPNUMBE
   FOR XML PATH('')) serial_numbers
  FROM SOP10201 p1
 GROUP BY SOPNUMBE, SOPTYPE, ITEMNMBR, LNITMSEQ) p) SN --serial/lot numbers
ON SN.SOPNUMBE = S.SOP_Number AND SN.SOPTYPE = S.SOP_Type AND SN.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10202 C --line item comments
ON C.SOPNUMBE = S.SOP_Number AND C.SOPTYPE = S.SOP_Type AND C.LNITMSEQ = S.Line_Item_Sequence

LEFT OUTER JOIN SOP10106 HC --header comments
ON HC.SOPNUMBE = S.SOP_Number AND HC.SOPTYPE = S.SOP_Type

--add permissions
GO
GRANT SELECT ON view_SOP_Line_Items_Serial TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

Filed under: Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code Tagged: featured, GP Reports code, GP SQL view, Sales Order Processing, SQL code

See the original post:
Dynamics GP SOP line items with serial numbers and comments

June 4, 2012 · Victoria Yudin · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 638