Monday, August 21, 2017

Dynamics NAV - LS Retail - Pharmacy - "The operation could not complete because a record in the Prescription Order Table was locked by another user. Please retry the activity." when scanning a prescription

Codeunits involved upon prescription scan

  • T10015350 Prescription Order
    • Flowfields:Prescription Order Lines Sum: Amount, Insurance Payment, Discount amount,Customer Payment
  • C10015331 Prescription POS Connection
    • ScanPrescriptionOrder
      • GetPrescriptionOrder
      • C10015395 Pharmacy Web client
        • GetPrescriptionOrder
          • Possibly Writing to Prescription Order, and stalling while calculating flowfields, resulting in access error as code continues to populate Prescription Order
      • GetAndReservePrescriptionOrder

Friday, August 18, 2017

Thursday, August 17, 2017

Dynamics GP - SQL View - Inventory Transaction Adjustment Line Detail with Reason codes, accounts and username

/****** Object:  View [dbo].[BI_INV_ItemAdjDtl]    Script Date: 17/08/2017 10:11:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[BI_INV_ItemAdjDtl]
AS
SELECT        dbo.IV30200.TRXSORCE, dbo.IV30200.IVDOCTYP, dbo.IV30200.DOCNUMBR, dbo.IV30200.DOCDATE, dbo.IV30200.BACHNUMB, dbo.IV30200.GLPOSTDT, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
                         dbo.IV30300.UOFM, dbo.IV30300.TRXQTY, dbo.IV30300.UNITCOST, dbo.IV30300.EXTDCOST, dbo.IV30300.TRXLOCTN, dbo.IV30300.TRNSTLOC, dbo.IV30300.QTYBSUOM, dbo.IV30300.Reason_Code,
                         dbo.IV40300.Reason_Code_Description, dbo.GL00105.ACTNUMST AS IVActNum, dbo.GL00100.ACTDESCR AS IVActDesc, GL00105_1.ACTNUMST AS OffsetActNum, GL00100_1.ACTDESCR AS OffsetActDesc,
                         GLTrxSmry.UserNm
FROM            dbo.IV30200 INNER JOIN
                         dbo.IV30300 ON dbo.IV30200.IVDOCTYP = dbo.IV30300.DOCTYPE AND dbo.IV30200.DOCNUMBR = dbo.IV30300.DOCNUMBR INNER JOIN
                         dbo.GL00100 ON dbo.IV30300.IVIVINDX = dbo.GL00100.ACTINDX INNER JOIN
                         dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX INNER JOIN
                         dbo.GL00100 AS GL00100_1 ON dbo.IV30300.IVIVOFIX = GL00100_1.ACTINDX INNER JOIN
                         dbo.GL00105 AS GL00105_1 ON GL00100_1.ACTINDX = GL00105_1.ACTINDX INNER JOIN
                         dbo.IV00101 ON dbo.IV30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                             (SELECT        ORTRXSRC AS TrxSource, DTAControlNum AS SourceDocNum, USWHPSTD AS UserNm
                               FROM            dbo.GL10000
                               UNION
                               SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                               FROM            dbo.GL20000
                               UNION
                               SELECT        ORGNTSRC, ORCTRNUM, USWHPSTD
                               FROM            dbo.GL30000) AS GLTrxSmry ON dbo.IV30300.TRXSORCE = GLTrxSmry.TrxSource AND dbo.IV30300.DOCNUMBR = GLTrxSmry.SourceDocNum LEFT OUTER JOIN
                         dbo.IV40300 ON dbo.IV30300.Reason_Code = dbo.IV40300.Reason_Code


GO


Dynamics GP - Reason Code Table - IV40300

Reason Code Table - IV40300

Monday, August 14, 2017

Eone Smartview and Smartlist builder - the multi-part identifier could not be bound

http://www.eonesolutions.com/discussion/smartlist-builder-lists-not-populating/


Reinstall the latest versions of Smartlist Builder and Smartview

Globalprotect v4 server certificate verification failed

Cause:
The proper certificates are not installed

Resolution:

  • After browsing to the portal to download the globalprotect client, if the site is showing a certificate error
    • Click on the error>View Certificates>Certification Path
    • It may have multiple certificates listed here. Install all certificates with a red x to the Trusted Certification Authority folder
    • Refresh the page to confirm that the certificates are working correctly

Saturday, August 12, 2017

Windows IRQL not less or equal BSOD


  • Download Malwarebytes and scan machine to remove any malware
  • Stop steam service
  • Stop all antivirus
  • Stop all firewall
  • Install new drivers

Thursday, August 10, 2017

SQL - Cannot edit job steps

https://davidbridge.wordpress.com/2011/05/13/cannot-edit-job-steps-in-sql-server-2008-r2-management-studio-on-64-bit-windows/

Run cmd as administrator
Run these two lines
This will register 64 bit DTS.dll
c:\windows\system32\regsvr32 "c:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll"
This will register 32 bit which is required as SSMS is a 32-bit application.
c:\windows\syswow64\regsvr32 "c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dts.dll"

SQL - getdate() without any time

DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Dynamics GP - SQL View - Aged AP Payments, GL, Invoices, POs - AP Aging by Payment Date

SELECT        P.VENDORID AS Vendor_ID, PM.VENDNAME AS Vendor_Name, CASE P.DOCTYPE WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Finance Charge' WHEN 3 THEN 'Misc Charge' END AS Document_Type,
                         P.DOCDATE AS Document_Date, P.VCHRNMBR AS Voucher_Number, P.DOCNUMBR AS Document_Number, P.DOCAMNT AS Document_Amount, P.TEN99AMNT AS [1099_Amount],
                         P.TEN99BOXNUMBER AS [1099_Box],
                         CASE P.TEN99TYPE WHEN 1 THEN 'Not a 1099 Vendor' WHEN 2 THEN 'Divident' WHEN 3 THEN 'Interest' WHEN 4 THEN 'Miscellaneous' WHEN 5 THEN 'Withholding' ELSE '' END AS [1099_Type],
                         COALESCE (PA.APPLDAMT, 0) AS Applied_Amount, COALESCE (PA.VCHRNMBR, '') AS Payment_Voucher_Number, COALESCE (P2.DOCNUMBR, '') AS Payment_Doc_Number, COALESCE (P2.DOCDATE, '1/1/1900')
                         AS Payment_Date, COALESCE (PA.DATE1, '1/1/1900') AS Apply_Date, COALESCE (PA.GLPOSTDT, '1/1/1900') AS Apply_GL_Posting_Date,
                         CASE PA.DOCTYPE WHEN 4 THEN 'Return' WHEN 5 THEN 'Credit Memo' WHEN 6 THEN 'Payment' ELSE '' END AS Payment_Type, COALESCE (PA.POSTED, 'Unpaid') AS Payment_Status, P.PORDNMBR,
                         PO.DOCDATE AS PODate, PO.CURNCYID, P.DUEDATE, PM.VNDCLSID, PO.POPCONTNUM AS ReqNo, P.CURTRXAM, CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) < 31 AND
                         P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) < 31 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [Current], CASE WHEN datediff(d, COALESCE (P2.DOCDATE,
                         getdate()), getdate()) BETWEEN 31 AND 60 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 31 AND 60 AND
                         P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [31_to_60_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 61 AND 90 AND
                         P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 61 AND 90 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [61_to_90_Days], CASE WHEN datediff(d,
                         COALESCE (P2.DOCDATE, getdate()), getdate()) BETWEEN 91 AND 120 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d, P.DOCDATE, getdate()) BETWEEN 91 AND 120 AND
                         P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [91_to_120_Days], CASE WHEN datediff(d, COALESCE (P2.DOCDATE, getdate()), getdate()) > 90 AND P.DOCTYPE < 4 THEN P.CURTRXAM WHEN datediff(d,
                          P.DOCDATE, getdate()) > 120 AND P.DOCTYPE > 3 THEN P.CURTRXAM * - 1 ELSE 0 END AS [120_and_Over], DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AS AgingDate, DATEDIFF(d, COALESCE (P2.DOCDATE,
                          GETDATE()), GETDATE()) AS DaysOld, ISNULL(PO.SUBTOTAL, 0) AS POSubTot, ISNULL(PO.TAXAMNT, 0) AS POTaxAmt, ISNULL(PO.SUBTOTAL + PO.TAXAMNT, 0) AS POTotal
FROM            (SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                          FROM            dbo.PM30200
                          UNION ALL
                          SELECT        VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE, PORDNMBR, DUEDATE, CURTRXAM
                          FROM            dbo.PM20000) AS P LEFT OUTER JOIN
                         dbo.POP10100 AS PO ON P.PORDNMBR = PO.PONUMBER LEFT OUTER JOIN
                             (SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, CASE POSTED WHEN 0 THEN 'Unposted' ELSE 'Posted' END AS POSTED
                               FROM            dbo.PM10200
                               UNION
                               SELECT        VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APPLDAMT, VCHRNMBR, DATE1, GLPOSTDT, 'Posted' AS POSTED
                               FROM            dbo.PM30300) AS PA ON P.VCHRNMBR = PA.APTVCHNM AND P.VENDORID = PA.VENDORID AND P.DOCTYPE = PA.APTODCTY LEFT OUTER JOIN
                             (SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                               FROM            dbo.PM20000 AS PM20000_1
                               UNION ALL
                               SELECT        VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
                               FROM            dbo.PM30200 AS PM30200_1) AS P2 ON P2.VCHRNMBR = PA.VCHRNMBR AND P2.DOCTYPE = PA.DOCTYPE LEFT OUTER JOIN
                         dbo.PM00200 AS PM ON P.VENDORID = PM.VENDORID
WHERE        (P.DOCTYPE IN (1, 2, 3)) AND (P.VOIDED = 0)

Dynamics GP - Can print to screen, but when printing to printer, nothing prints, with no error message

When a batch is posted, 3 or 4 reports may print to screen.
The way GP processes these tasks is as follows

  • Print Report1 to screen
  • Print Report2 to screen
  • Print Report3 to screen
  • Print Report4 to screen
  • Complete Posting
  • Submit print requests to printer
GP will not actually submit the print jobs to the printer until all of the "print to screen"s have been processed


If this is not the issue then try
Backup Reports.dic first
After ruling out printer and connectivity issues, test printing with a new dictionary.

If this works, dictionary is corrupted.
Remove old dictionary, reimport all customizations

Wednesday, August 9, 2017

Dynamics GP - Fix Note indexes on imported records

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/02/04/fixing-missing-note-index-values/

This script will update all NOTEINDX fields with a value of 0 to the correct noteindx value for the @tablename defined


/*
** AssignNoteIndx.sql
**
** This script assigns note index values to all records
** in the given table that currently do not have one.
**
** Edit the table name below and execute in
** context of appropriate company database.
*/

/* -- parameters -- */

Declare @tablename sysname

/* ############## */

set @tablename = 'RM00101' -- put the table name here

/* ############## */


/* -- working variables -- */
set nocount on

declare @tloop int
declare @sCompanyID integer
declare @sSessionID integer
declare @DEX_ROW_ID integer
declare @NewNoteIndex numeric(19,5)
declare @sError integer
declare @Cnt integer

set @tloop = 1
select @sCompanyID = CMPANYID
 from DYNAMICS.dbo.SY01500
 where INTERID = db_name()
set @sSessionID = @@spid
set @Cnt = 0

/* @@@ MAINLINE @@@ */

while @tloop = 1 begin
set @tloop = 0

/* -- check table exists -- */
if object_id (@tablename) is null begin
print 'table : ' + @tablename
print 'does not exist in this database : ' + db_name()
break
end
if @sCompanyID is null begin
print 'No Entry in DYNAMICS system table for this company'
break
end

print 'Assigning Note Index for table : ' + @tablename

/* -- create a proc for repeated calls -- */

exec ('create procedure #nextdex as declare @DEXROWID integer select top 1 @DEXROWID = DEX_ROW_ID from ' + @tablename + ' where NOTEINDX = 0 return isnull(@DEXROWID,0) ' )
exec ('create procedure #assignnext (@NextIndx integer, @DEXROWID integer ) as update ' + @tablename + ' set NOTEINDX = @NextIndx where DEX_ROW_ID = @DEXROWID return @@ERROR ' )


exec @DEX_ROW_ID = #nextdex

while @DEX_ROW_ID > 0 begin

begin transaction
exec DYNAMICS.dbo.smGetNextNoteIndex @sCompanyID, @sSessionID, @NewNoteIndex OUTPUT, @sError OUTPUT
exec #assignnext @NewNoteIndex, @DEX_ROW_ID
commit transaction

set @Cnt = @Cnt + 1

exec @DEX_ROW_ID = #nextdex
end

print 'Number of records changed : ' + convert(varchar(8), @Cnt)


drop procedure #nextdex
drop procedure #assignnext

end

/* ###### end of script #### */

Monday, August 7, 2017

Friday, August 4, 2017

Dynamics GP - Integration Manager 2016 - Root data not found, Could not access Microsoft.Dynamics.GP.IntegrationManager.ini file

Cause: UAC is on, and IM has been installed to one location, but then uninstalled and reinstalled to another location.

The xml files in the C:\Users\%userid%\AppData\Roaming\Microsoft Corporation\Microsoft Dynamics GP\16.0.0.0
folder cannot be overwritten, even when run as administrator causing im to constantly point to the old location


Resolution

  • Delete the following folders
    • C:\Users\%userid%\AppData\Roaming\Microsoft Corporation\Microsoft Dynamics GP\16.0.0.0 
  • Relaunch IM as administrator, and that should generate a new xml file to the new path

Thursday, August 3, 2017

Dynamics GP - Sales Comment Inquiry Zoom - Source Table and Fields



  • SY04200 - Preset comment id lines - use CMMTTEXT
  • SOP10102 - Line Comments in the blue arrow after comment id on the sop line - use CMMTTEXT
  • SOP10106 - Document Comments on the blue arrow after comment id - use CMMTTEXT

Wednesday, August 2, 2017

Dynamics GP - Manufacturing Views - MO Receipts

/****** Object:  View [dbo].[BI_Mfg_MORcts]    Script Date: 8/2/2017 1:12:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_Mfg_MORcts]
AS
SELECT        dbo.MOP1100.MANUFACTUREORDER_I AS MONum, dbo.WO010032.DSCRIPTN, dbo.MOP1100.MOPRCTNM, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.MOP1100.DATERECD,
                         dbo.MOP1100.QTYRECVD, dbo.MOP1100.TO_SITE_I, dbo.MOP1100.USERID, dbo.MOP1100.RECEIPTCOSTSARRAY_10 AS BackflushCost, dbo.MOP1100.POSTED
FROM            dbo.IV00101 RIGHT OUTER JOIN
                         dbo.WO010032 ON dbo.IV00101.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
                         dbo.MOP1100 ON dbo.WO010032.MANUFACTUREORDER_I = dbo.MOP1100.MANUFACTUREORDER_I
GO


Monday, July 31, 2017

Dynamics GP - Manufacturing Views - BOM and Exploded BOM

/****** Object:  View [dbo].[BI_Mfg_BOM]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM]
AS
SELECT        TOP (100) PERCENT dbo.IV00101.ITEMNMBR AS BomNumber, dbo.IV00101.ITEMDESC AS BOMName, IV00101_1.ITEMNMBR AS CmpNumber, IV00101_1.ITEMDESC AS CmpName,
                         dbo.BM010115.QUANTITY_I AS CmpQty, dbo.BM010115.UOFM AS CmpUofM, dbo.BM010415.LOCNCODE, RTRIM(dbo.IV00101.ITEMNMBR) + ' | ' + dbo.IV00101.ITEMDESC AS BomLookup,
                         dbo.IV00101.ITMCLSCD, dbo.IV40400.ITMCLSDC, dbo.IVR10015.ITEMSTATUS_I, dbo.IVR10015.MAKEBUYCODE_I, dbo.IVR10015.ITEMFULFILLMETHOD,
                         CASE makebuycode_i WHEN 1 THEN 'Make' WHEN 2 THEN 'Buy' WHEN 3 THEN 'Make/Buy' END AS MakeBuy,
                         CASE itemfulfillmethod WHEN 1 THEN 'Make to Stock' WHEN 2 THEN 'Make to Order-Manual' WHEN 3 THEN 'Make to Order-Silent' END AS FulfillMethod, dbo.MN010000.NOTETEXT_I AS BomNote,
                         IV00101_1.CURRCOST AS CmpCurrCost, dbo.BM010415.BOMTYPE_I, CASE itemstatus_i WHEN 1 THEN 'Active' ELSE 'Inactive' END AS ItemEngStatus,
                         CASE WHEN iv00101.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMStatus, dbo.BM010415.EFFECTIVEDATE_I AS BOMInDate, CASE WHEN BM010415.EFFECTIVEDATE_I < getdate()
                         THEN 'Active' WHEN BM010415.EFFECTIVEDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS BOMDateStatus, CASE WHEN iv00101_1.ITEMTYPE = 2 THEN 'Discontinued' ELSE 'Active' END AS BOMCmpStatus,
                         dbo.BM010115.EFFECTIVEINDATE_I AS CmpInDate, dbo.BM010115.EFFECTIVEOUTDATE_I AS CmpOutDate, CASE WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I >= getdate()
                         THEN 'Active' WHEN EFFECTIVEINDATE_I <= getdate() AND EFFECTIVEOUTDATE_I = 0 THEN 'Active' ELSE 'Inactive' END AS CmpStatus, dbo.BM010115.ALTERNATE_I AS IsAlternate,
                         dbo.BM010115.ALTERNATEPARTFOR_I AS AlternateFor
FROM            dbo.MN010000 RIGHT OUTER JOIN
                         dbo.IVR10015 ON dbo.MN010000.NOTEINDX = dbo.IVR10015.MFGNOTEINDEX_I RIGHT OUTER JOIN
                         dbo.IV00101 LEFT OUTER JOIN
                         dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD RIGHT OUTER JOIN
                         dbo.BM010415 ON dbo.IV00101.ITEMNMBR = dbo.BM010415.ITEMNMBR LEFT OUTER JOIN
                         dbo.IV00101 AS IV00101_1 RIGHT OUTER JOIN
                         dbo.BM010115 ON IV00101_1.ITEMNMBR = dbo.BM010115.CPN_I ON dbo.BM010415.BOMCAT_I = dbo.BM010115.BOMCAT_I AND dbo.BM010415.ITEMNMBR = dbo.BM010115.PPN_I ON
                         dbo.IVR10015.ITEMNMBR = dbo.BM010415.ITEMNMBR
GO
/****** Object:  View [dbo].[BI_Mfg_ActiveBomCmps]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_ActiveBomCmps]
AS
SELECT        BomNumber AS BN, CmpNumber AS CN, ItemEngStatus, BOMStatus, BOMDateStatus, BOMCmpStatus, CmpStatus
FROM            dbo.BI_Mfg_BOM
WHERE        (ItemEngStatus = 'active') AND (BOMStatus = 'active') AND (BOMDateStatus = 'active') AND (BOMCmpStatus = 'active') AND (CmpStatus = 'active')
GO
/****** Object:  View [dbo].[BI_Mfg_BOM2]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM2]
AS
SELECT        dbo.BI_Mfg_BOM.BomNumber, dbo.BI_Mfg_BOM.BOMName, dbo.BI_Mfg_BOM.CmpNumber, dbo.BI_Mfg_BOM.CmpName, dbo.BI_Mfg_BOM.CmpQty, dbo.BI_Mfg_BOM.CmpUofM, dbo.BI_Mfg_BOM.LOCNCODE,
                         dbo.BI_Mfg_BOM.BomLookup, dbo.BI_Mfg_BOM.ITMCLSCD, dbo.BI_Mfg_BOM.ITMCLSDC, dbo.BI_Mfg_BOM.ITEMSTATUS_I, dbo.BI_Mfg_BOM.MAKEBUYCODE_I, dbo.BI_Mfg_BOM.ITEMFULFILLMETHOD,
                         dbo.BI_Mfg_BOM.MakeBuy, dbo.BI_Mfg_BOM.FulfillMethod, dbo.BI_Mfg_BOM.BomNote, dbo.BI_Mfg_BOM.CmpCurrCost, dbo.BI_Mfg_BOM.BOMTYPE_I, dbo.BI_Mfg_BOM.ItemEngStatus,
                         dbo.BI_Mfg_BOM.BOMStatus, dbo.BI_Mfg_BOM.BOMInDate, dbo.BI_Mfg_BOM.BOMDateStatus, dbo.BI_Mfg_BOM.BOMCmpStatus, dbo.BI_Mfg_BOM.CmpInDate, dbo.BI_Mfg_BOM.CmpOutDate,
                         dbo.BI_Mfg_BOM.CmpStatus, dbo.BI_Mfg_BOM.IsAlternate, dbo.BI_Mfg_BOM.AlternateFor, CASE WHEN CN IS NULL THEN 'Inactive' ELSE 'Active' END AS IsActive
FROM            dbo.BI_Mfg_BOM LEFT OUTER JOIN
                         dbo.BI_Mfg_ActiveBomCmps ON dbo.BI_Mfg_BOM.BomNumber = dbo.BI_Mfg_ActiveBomCmps.BN AND dbo.BI_Mfg_BOM.CmpNumber = dbo.BI_Mfg_ActiveBomCmps.CN
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_L1]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_L1]
AS
SELECT        L0.BomNumber AS ParentBomNo, L1.BomNumber AS CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.cmpnumber ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                         THEN l0.cmpqty ELSE L1.CmpQty * L0.CmpQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                         THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
FROM            dbo.BI_Mfg_BOM2 AS L0 LEFT OUTER JOIN
                         dbo.BI_Mfg_BOM2 AS L1 ON L0.CmpNumber = L1.BomNumber
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_L2]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_L2]
AS
SELECT        L0.ParentBomNo, L0.CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.fnum ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                         THEN l0.fqty ELSE L1.CmpQty * L0.fQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                         THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
FROM            dbo.BI_Mfg_BOM_L1 AS L0 LEFT OUTER JOIN
                         dbo.BI_Mfg_BOM2 AS L1 ON L0.FNum = L1.BomNumber
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_L3]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_L3]
AS
SELECT        L0.ParentBomNo, L0.CmpNumber, L0.CmpQty, CASE WHEN L1.BomNumber IS NULL THEN l0.fnum ELSE l1.cmpnumber END AS FNum, CASE WHEN L1.CmpQty IS NULL
                         THEN l0.fqty ELSE L1.CmpQty * L0.fQty END AS FQty, CASE WHEN l1.IsActive IS NULL THEN l0.isactive ELSE l1.isactive END AS IsActive, CASE WHEN l1.IsAlternate IS NULL
                         THEN l0.isalternate ELSE l1.isalternate END AS IsAlternate, CASE WHEN l1.AlternateFor IS NULL THEN l0.Alternatefor ELSE l1.Alternatefor END AS AlternateFor
FROM            dbo.BI_Mfg_BOM_L2 AS L0 LEFT OUTER JOIN
                         dbo.BI_Mfg_BOM2 AS L1 ON L0.FNum = L1.BomNumber
GO
/****** Object:  View [dbo].[BI_Mfg_BOM_Exploded]    Script Date: 8/2/2017 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Mfg_BOM_Exploded]
AS
SELECT        dbo.BI_Mfg_BOM_L3.ParentBomNo, dbo.IV00101.ITEMDESC AS ParentBOMDesc, dbo.BI_Mfg_BOM_L3.FNum AS CmpNum, IV00101_1.ITEMDESC AS CmpDesc, dbo.BI_Mfg_BOM_L3.FQty,
                         dbo.BI_Mfg_BOM_L3.IsActive, dbo.BI_Mfg_BOM_L3.IsAlternate, dbo.BI_Mfg_BOM_L3.AlternateFor
FROM            dbo.BI_Mfg_BOM_L3 LEFT OUTER JOIN
                         dbo.IV00101 AS IV00101_1 ON dbo.BI_Mfg_BOM_L3.FNum = IV00101_1.ITEMNMBR LEFT OUTER JOIN
                         dbo.IV00101 ON dbo.BI_Mfg_BOM_L3.ParentBomNo = dbo.IV00101.ITEMNMBR
GO

Friday, July 28, 2017

Dynamics GP - SOP Open and Hist Sales View 2017

/****** Object:  View [dbo].[BI_SOP_Hist]    Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Hist]
AS
SELECT        TOP (100) PERCENT dbo.SOP30200.DOCID, dbo.SOP30200.ORIGNUMB, dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
                          CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT) * - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
                         CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs, CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty,
                          dbo.SOP30200.CUSTNMBR AS [Customer Number],
                         CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype =
                          5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
                         dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
                         (CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
                         ISNULL(dbo.SOP30200.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
                         dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP30300.ITEMNMBR, 2)
                         AS AB, dbo.SOP30300.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
                         YEAR(dbo.SOP30200.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP30200.DOCDATE) AS VARCHAR)
                         + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP30200.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP30200.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP30200.CUSTNAME) AS CusIdName,
                         RTRIM(dbo.SOP30300.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP30300.ITEMDESC) AS ItemIdName, dbo.SOP30300.UOFM, CASE LEFT(dbo.SOP30200.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                         WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP30200.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
                         '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
                         DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                          CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
                         dbo.SOP30200.SOPTYPE, dbo.SOP30300.QTYTBAOR AS QtytoBackOrder, dbo.SOP30300.LNITMSEQ, dbo.SOP30300.ReqShipDate, dbo.SOP30300.QTYTOINV AS QtyToInvoice,
                         CASE WHEN sop30200.soptype = 4 THEN sop30300.MRKDNAMT * - 1 ELSE sop30300.MRKDNAMT END AS MarkdownAmt,
                         CASE WHEN sop30200.soptype = 4 THEN sop30300.TRDISAMT * - 1 ELSE sop30300.TRDISAMT END AS DiscountAmt, CASE WHEN sop30200.soptype = 4 THEN (sop30300.UNITPRCE * SOP30300.qtytoinv)
                         * - 1 ELSE (sop30300.UNITPRCE * SOP30300.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop30200.soptype = 4 THEN sop30300.taxamnt * - 1 ELSE sop30300.taxamnt END AS TaxAmt
FROM            dbo.RM00101 INNER JOIN
                         dbo.SOP30200 INNER JOIN
                         dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
                         dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR LEFT OUTER JOIN
                         dbo.IV40400 INNER JOIN
                         dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                         dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP30300.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                         dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                         dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE        (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC

GO
/****** Object:  View [dbo].[BI_SOP_Open]    Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_Open]
AS
SELECT        TOP (100) PERCENT dbo.SOP10100.DOCID, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
                          CASE WHEN SOP10100.soptype = 4 THEN (SOP10200.XTNDPRCE - SOP10200.TRDISAMT) * - 1 ELSE (SOP10200.xtndprce - SOP10200.TRDISAMT) END AS Sales,
                         CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
                         CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
                         CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype
                          = 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code],
                         dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure],
                         (CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
                         ISNULL(dbo.SOP10100.SLPRSNID, '(none)') AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2, dbo.IV00101.USCATVLS_3, dbo.IV00101.ITEMNMBR AS [Item Number],
                         dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.IV00101.USCATVLS_2 AS Category, dbo.SOP10200.UNITCOST, dbo.RM00101.CREATDDT, LEFT(dbo.SOP10200.ITEMNMBR, 2)
                         AS AB, dbo.SOP10200.UNITPRCE, dbo.RM00301.ZIP AS [Sales Person Department], dbo.RM00301.SLPRSNFN AS [Sales Person First Name], dbo.RM00301.SPRSNSLN AS [Sales Person Last Name],
                         YEAR(dbo.SOP10100.DOCDATE) AS DocYear, RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS DocMonth, CAST(YEAR(dbo.SOP10100.DOCDATE) AS VARCHAR)
                         + '-' + CAST(RIGHT('00' + CAST(MONTH(dbo.SOP10100.DOCDATE) AS VARCHAR), 2) AS VARCHAR) AS DocYrMonth, RTRIM(dbo.SOP10100.CUSTNMBR) + ' | ' + RTRIM(dbo.SOP10100.CUSTNAME) AS CusIdName,
                         RTRIM(dbo.SOP10200.ITEMNMBR) + ' | ' + RTRIM(dbo.SOP10200.ITEMDESC) AS ItemIdName, dbo.SOP10200.UOFM, CASE LEFT(dbo.SOP10100.CUSTNMBR, 1) WHEN '2' THEN LEFT(rm00101.Custclas, 4)
                         WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN dbo.RM00201.CLASDSCR ELSE LEFT(dbo.SOP10100.CUSTNMBR, 1) END AS Div, ISNULL(dbo.RM00301.SALSTERR,
                         '(none)') AS Salsterr, RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SRNm, LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClassDiv, dbo.RM00201.CLASDSCR, dbo.IV00101.MODIFDT,
                         DATEDIFF(d, dbo.IV00101.CREATDDT, GETDATE()) AS ItemAge, dbo.RM00101.COUNTRY, 0 AS BQty, CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custname ELSE rm00101_1.custname END AS NatCusNm,
                          CASE WHEN rm00101.CPRCSTNM = '' THEN rm00101.custnmbr ELSE rm00101.cprcstnm END AS NatCus, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCus,
                         dbo.SOP10100.SOPTYPE, dbo.SOP10200.QTYTBAOR AS QtytoBackOrder, dbo.SOP10200.LNITMSEQ, dbo.SOP10200.ReqShipDate, dbo.SOP10200.QTYTOINV AS QtyToInvoice,
                         CASE WHEN sop10100.soptype = 4 THEN sop10200.MRKDNAMT * - 1 ELSE sop10200.MRKDNAMT END AS MarkdownAmt,
                         CASE WHEN sop10100.soptype = 4 THEN sop10200.TRDISAMT * - 1 ELSE sop10200.TRDISAMT END AS DiscountAmt, CASE WHEN sop10100.soptype = 4 THEN (sop10200.UNITPRCE * SOP10200.qtytoinv)
                         * - 1 ELSE (sop10200.UNITPRCE * SOP10200.qtytoinv) END AS SalesNoMarkdown, CASE WHEN sop10100.soptype = 4 THEN sop10200.taxamnt * - 1 ELSE sop10200.taxamnt END AS TaxAmt
FROM            dbo.RM00101 INNER JOIN
                         dbo.SOP10100 INNER JOIN
                         dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE ON
                         dbo.RM00101.CUSTNMBR = dbo.SOP10100.CUSTNMBR LEFT OUTER JOIN
                         dbo.IV40400 INNER JOIN
                         dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
                         dbo.RM00301 ON dbo.SOP10100.SLPRSNID = dbo.RM00301.SLPRSNID AND dbo.SOP10200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
                         dbo.RM00101 AS RM00101_1 ON dbo.RM00101.CPRCSTNM = RM00101_1.CUSTNMBR LEFT OUTER JOIN
                         dbo.RM00201 ON dbo.RM00101.CUSTCLAS = dbo.RM00201.CLASSID
WHERE        (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0)
ORDER BY [GL Post Date] DESC

GO
/****** Object:  View [dbo].[BI_SOP_ALLSOP]    Script Date: 8/16/2017 1:32:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_ALLSOP]
AS
SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code], [Customer Name], [Customer Class],
                         [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, [Item Number], [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB,
                         UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth, DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR,
                         MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, QtytoBackOrder, LNITMSEQ, ReqShipDate, QtyToInvoice, MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
FROM            (SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
                                                    [Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
                                                    [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
                                                    DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
                                                    MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
                          FROM            dbo.BI_SOP_Open
                          UNION
                          SELECT        DOCID, ORIGNUMB, [SOP Number], [Document Date], [GL Post Date], Sales, Costs, Qty, QtytoBackOrder, QtyToInvoice, [Customer Number], [SOP Type], [Item Class Description], [Item Class Code],
                                                   [Customer Name], [Customer Class], [Base Unit Of Measure], [Units Sold], STATE, PRSTADCD, [Salesperson ID], USCATVLS_1, USCATVLS_2, USCATVLS_3, LNITMSEQ, [Item Number],
                                                   [Item Description], ShipToName, Category, UNITCOST, CREATDDT, AB, UNITPRCE, [Sales Person Department], [Sales Person First Name], [Sales Person Last Name], DocYear, DocMonth,
                                                   DocYrMonth, CusIdName, ItemIdName, UOFM, Div, Salsterr, SRNm, CusClassDiv, CLASDSCR, MODIFDT, ItemAge, COUNTRY, BQty, NatCusNm, NatCus, LocExpCus, SOPTYPE, ReqShipDate,
                                                   MarkdownAmt, DiscountAmt, SalesNoMarkdown, TaxAmt
                          FROM            dbo.BI_SOP_Hist) AS ALLSOP

GO

Dynamics GP - Manufacturing - Order-MOPSOP Link-MO-Received-Invoice View

/****** Object:  View [dbo].[BI_Mfg_MOSOLink]    Script Date: 7/30/2017 12:25:44 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_Mfg_MOSOLink]
AS
SELECT        SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QtyProcess AS MOQty, QtyMatch AS QtyAllocatedToSO
FROM            (SELECT        SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, 0 AS QtyProcess, 0 AS QtyMatch
                          FROM            dbo.IS030001
                          UNION
                          SELECT        SOPNUMBE, SOPTYPE, LNITMSEQ, CMPNTSEQ, MANUFACTUREORDER_I, ITEMNMBR, USERID, QTYProcess, QTYMATCH
                          FROM            dbo.IS010001) AS MOSO
WHERE        (MANUFACTUREORDER_I > '')
GO


/****** Object:  View [dbo].[BI_Mfg_SI]    Script Date: 7/30/2017 12:25:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[BI_Mfg_SI]
AS
SELECT        DOCID AS SIDocID, ORIGNUMB AS SIOrigNum, [SOP Number] AS SIDocNum, [Document Date] AS SIDocDate, Qty AS SIQty, [Item Number], [Item Description], SOPTYPE AS SIDocType, Sales AS SISls,
                         Costs AS SICost, LNITMSEQ
FROM            dbo.BI_SOP_ALLSOP
WHERE        (SOPTYPE = 3)
GO

/****** Object:  View [dbo].[BI_Mfg_SO]    Script Date: 7/30/2017 12:25:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_Mfg_SO]
AS
SELECT        dbo.BI_SOP_ALLSOP.SRNm, dbo.BI_SOP_ALLSOP.DOCID AS SODocID, dbo.BI_SOP_ALLSOP.ORIGNUMB AS SOOrigNum, dbo.BI_SOP_ALLSOP.[SOP Number] AS SODocNum,
                         dbo.BI_SOP_ALLSOP.[Document Date] AS SODate, dbo.BI_SOP_ALLSOP.Qty AS SOQty, dbo.BI_SOP_ALLSOP.[Item Number], dbo.BI_SOP_ALLSOP.[Item Description],
                         dbo.BI_SOP_ALLSOP.SOPTYPE AS SODocType, dbo.BI_SOP_ALLSOP.Sales AS SOSls, dbo.BI_SOP_ALLSOP.Costs AS SOCost, dbo.BI_SOP_ALLSOP.QtytoBackOrder, dbo.BI_SOP_ALLSOP.LNITMSEQ,
                         dbo.BI_SOP_ALLSOP.ReqShipDate AS SOReqShipDate, dbo.BI_SOP_ALLSOP.[Customer Number] AS SOCusNum, dbo.BI_SOP_ALLSOP.[Customer Name] AS SOCusName,
                         dbo.BI_SOP_ALLSOP.QtyToInvoice
FROM            dbo.BI_SOP_ALLSOP INNER JOIN
                         dbo.BI_Mfg_MOSOLink ON dbo.BI_SOP_ALLSOP.[SOP Number] = dbo.BI_Mfg_MOSOLink.SOPNUMBE
WHERE        (dbo.BI_SOP_ALLSOP.SOPTYPE = 2)
GO

/****** Object:  View [dbo].[BI_Mfg_AllDocs]    Script Date: 7/30/2017 12:26:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** Object:  View [dbo].[BI_Mfg_MO]    Script Date: 7/31/2017 1:45:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/****** Object:  View [dbo].[BI_Mfg_MO]    Script Date: 8/2/2017 9:52:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_Mfg_MO]
AS
SELECT        dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.DSCRIPTN, dbo.WO010032.MANUFACTUREORDERST_I AS MOStatus, dbo.WO010032.ITEMNMBR, dbo.WO010032.ARCHIVED_MO_I,
                         dbo.WO010032.BOMCAT_I, dbo.WO010032.BOMNAME_I, dbo.WO010032.ROUTINGNAME_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.STARTQTY_I, dbo.WO010032.STRTDATE, dbo.WO010032.STARTTIME_I,
                         dbo.WO010032.ENDDATE, dbo.WO010032.DRAWFROMSITE_I, dbo.WO010032.CHANGEDATE_I, dbo.WO010032.USERID, dbo.WO010032.SCHEDULEMETHOD_I, dbo.WO010032.PROJEMPLOYEEHRSSUM_I,
                         dbo.WO010032.PROJMACHINEHRSSUM_I, dbo.WO010032.MATPROJCOSTI_1, dbo.WO010032.MATPROJCOSTI_2, dbo.WO010032.MATFIXOHDPROJCOSTI_1, dbo.WO010032.MATFIXOHDPROJCOSTI_2,
                         dbo.WO010032.MATVAROHDPROJCOST_1, dbo.WO010032.MATVAROHDPROJCOST_2, dbo.WO010032.LABPROJCOSTI_1, dbo.WO010032.LABPROJCOSTI_2, dbo.WO010032.LABFIXOHDPROJCOSTI_1,
                         dbo.WO010032.LABFIXOHDPROJCOSTI_2, dbo.WO010032.LABVAROHDPROJCOSTI_1, dbo.WO010032.LABVAROHDPROJCOSTI_2, dbo.WO010032.MACHPROJCOSTI_1, dbo.WO010032.MACHPROJCOSTI_2,
                         dbo.WO010032.MACHFIXOHDPROJCOSTI_1, dbo.WO010032.MACHFIXOHDPROJCOSTI_2, dbo.WO010032.MACHVAROHDPROJCOSTI_1, dbo.WO010032.MACHVAROHDPROJCOSTI_2,
                         dbo.WO010032.POSTTOSITE_I, dbo.WO010032.LOTNUMBR, dbo.WO010032.SCHEDULINGPREFEREN_I, dbo.WO010032.PLANNAME_I, dbo.WO010032.ACTUALDEMAND_I,
                         dbo.WO010032.MANUFACTUREORDPRI_I, dbo.WO010032.Partial_Purge_Date, dbo.WO010032.NOTEINDX, dbo.WO010032.OUTSOURCED_I, dbo.WO010032.COMPCALCOPTION,
                         dbo.WO010032.COMPLETECLOSEDATE, dbo.WO010032.PSTGDATE, dbo.WO010032.PLNNDSPPLID, dbo.WO010032.PICKNUMBER, dbo.WO010032.QUICK_MO_I, dbo.WO010032.ROUTING_REVISION_LEVEL,
                         dbo.WO010032.BOM_REVISION_LEVEL, dbo.WO010032.DEX_ROW_ID, ISNULL(MORctQty.MOQtyRecvd, 0) AS MOQtyRecvd,
                         CASE WHEN MANUFACTUREORDERST_I = 1 THEN 'Quote,' WHEN MANUFACTUREORDERST_I = 2 THEN 'Open' WHEN MANUFACTUREORDERST_I = 3 THEN 'Released' WHEN MANUFACTUREORDERST_I = 4 THEN
                          'Hold' WHEN MANUFACTUREORDERST_I = 5 THEN 'Cancelled' WHEN MANUFACTUREORDERST_I = 6 THEN 'Complete' WHEN MANUFACTUREORDERST_I = 7 THEN 'Partial Receipt' WHEN MANUFACTUREORDERST_I
                          = 8 THEN 'Closed' ELSE 'Now Being Entered by ' + wo010032.userid END AS MOStatusDesc, dbo.MN010000.NOTETEXT_I AS MONote
FROM            dbo.WO010032 LEFT OUTER JOIN
                         dbo.MN010000 ON dbo.WO010032.NOTEINDX = dbo.MN010000.NOTEINDX LEFT OUTER JOIN
                             (SELECT        MANUFACTUREORDER_I, SUM(QTYRECVD) AS MOQtyRecvd
                               FROM            dbo.MOP1100
                               GROUP BY MANUFACTUREORDER_I
                               HAVING         (MANUFACTUREORDER_I > '')) AS MORctQty ON dbo.WO010032.MANUFACTUREORDER_I = MORctQty.MANUFACTUREORDER_I
WHERE        (dbo.WO010032.MANUFACTUREORDER_I > '')
GO




/****** Object:  View [dbo].[BI_Mfg_AllDocs]    Script Date: 8/2/2017 9:54:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_Mfg_AllDocs]
AS
SELECT        TOP (100) PERCENT dbo.BI_Mfg_SO.SODocID, dbo.BI_Mfg_SO.SODocType, dbo.BI_Mfg_SI.SIDocID, dbo.BI_Mfg_SI.SIDocType, dbo.BI_Mfg_SO.SRNm, dbo.BI_Mfg_SO.SODocNum,
                         dbo.BI_Mfg_SO.SOReqShipDate, dbo.BI_Mfg_SO.SOCusNum, dbo.BI_Mfg_SO.SOCusName, dbo.BI_Mfg_SO.SOQty, dbo.BI_Mfg_SO.QtytoBackOrder, dbo.BI_Mfg_SO.QtyToInvoice,
                         ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') AS MODocNo, dbo.BI_Mfg_SI.SIDocNum, dbo.BI_Mfg_SI.SIQty, dbo.BI_Mfg_SO.LNITMSEQ AS SOLine, dbo.BI_Mfg_MOSOLink.QtyAllocatedToSO,
                         dbo.BI_Mfg_MOSOLink.MOQty, dbo.BI_Mfg_MO.MOStatus, dbo.BI_Mfg_MO.MOQtyRecvd, dbo.BI_Mfg_MO.MOStatusDesc, dbo.BI_Mfg_MO.MONote
FROM            dbo.BI_Mfg_MOSOLink LEFT OUTER JOIN
                         dbo.BI_Mfg_MO ON dbo.BI_Mfg_MOSOLink.ITEMNMBR = dbo.BI_Mfg_MO.ITEMNMBR AND dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I = dbo.BI_Mfg_MO.MANUFACTUREORDER_I RIGHT OUTER JOIN
                         dbo.BI_Mfg_SO LEFT OUTER JOIN
                         dbo.BI_Mfg_SI ON dbo.BI_Mfg_SO.SODocNum = dbo.BI_Mfg_SI.SIOrigNum AND dbo.BI_Mfg_SO.[Item Number] = dbo.BI_Mfg_SI.[Item Number] AND dbo.BI_Mfg_SO.LNITMSEQ = dbo.BI_Mfg_SI.LNITMSEQ ON
                         dbo.BI_Mfg_MOSOLink.SOPNUMBE = dbo.BI_Mfg_SO.SODocNum AND dbo.BI_Mfg_MOSOLink.LNITMSEQ = dbo.BI_Mfg_SO.LNITMSEQ AND
                         dbo.BI_Mfg_MOSOLink.ITEMNMBR = dbo.BI_Mfg_SO.[Item Number]
WHERE        (ISNULL(dbo.BI_Mfg_MOSOLink.MANUFACTUREORDER_I, '') > '')
GO


Dynamics GP - Print PO Word Template directly to PDF and save with dynamic filename including PO Number, Vendor Name


  • On each user's machine, install a pdf printer
  • Set pdf printer as default printer
  • Set the GP>user preferences to print to printer
  • (alternatively, you can setup named printers, and force the PO to the pdf printer)
  • Modify the "Purchase Order Print Options" screen to add a field that populates with the required path and filename using vba
  • User will select the filename and copy
    • You could also have vba copy the path and filename to the clipboard automatically
  • User prints to pdf
  • When the pdf printer prompts for the filename and location, user will paste filepath and name and save pdf

Thursday, July 27, 2017

SQL 2016 - SSRS - Email subscriptions are not enabled

Run the SSRS Configuration Manager
Set options under Email settings and subscription settings

Wednesday, July 26, 2017

Dynamics GP - PO Receipt Posting hangs, freezes at 100%

Issue:
We were experiencing this problem with a specific number of items. Whenever a purchase receipt was posted for any one of these specific items, the posting would hang at 100%. Upon investigation, it looked as though all posting actually completed in the tables, but the original work document was failing to delete from the work tables.

No error message was being returned to screen

We enabled the DEXSQL Log, and saw this error coming up
[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'MOP1041'.*/

Also, when we switched the posting to transaction level posting, and attempted to post, we got the error message
A Get Change operation failed on POP Line Hist

Solution:
Create the MOP1041 object

/****** Object:  Table [dbo].[MOP1041]    Script Date: 7/26/2017 2:25:58 PM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[MOP1041](
[MANUFACTUREORDER_I] [char](31) NOT NULL,
[MOPDOCNUM] [char](31) NOT NULL,
[CALLEDBY] [smallint] NOT NULL,
[TO_SITE_I] [char](11) NOT NULL,
[BIN] [char](15) NOT NULL,
[SEQNUMBR] [int] NOT NULL,
[ITEMNMBR] [char](31) NOT NULL,
[IVDOCNBR] [char](17) NOT NULL,
[QUANTITY] [numeric](19, 5) NOT NULL,
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKMOP1041] PRIMARY KEY CLUSTERED
(
[MANUFACTUREORDER_I] ASC,
[MOPDOCNUM] ASC,
[CALLEDBY] ASC,
[SEQNUMBR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Thursday, July 20, 2017

Dynamics NAV - ALL GL Transactions for a period

SELECT     [CRONUS$G_L Entry].[Entry No_], [CRONUS$G_L Entry].[G_L Account No_], [CRONUS$G_L Account].Name,
                      [CRONUS$G_L Entry].[Posting Date], [CRONUS$G_L Entry].[Document Type], [CRONUS$G_L Entry].[Document No_],
                      [CRONUS$G_L Entry].Description, [CRONUS$G_L Entry].[Bal_ Account No_], [CRONUS$G_L Entry].Amount,
                      [CRONUS$G_L Entry].[Global Dimension 1 Code], [CRONUS$G_L Entry].[Global Dimension 2 Code], [CRONUS$G_L Entry].[User ID],
                      [CRONUS$G_L Entry].[Transaction No_], [CRONUS$G_L Entry].[Debit Amount], [CRONUS$G_L Entry].[Credit Amount],
                      [CRONUS$G_L Entry].[Document Date], YEAR([CRONUS$G_L Entry].[Posting Date]) AS PostYr, MONTH([CRONUS$G_L Entry].[Posting Date])
                      AS PostMth, [CRONUS$G_L Entry].[External Document No_], [CRONUS$G_L Entry].[Source Code]
FROM         [CRONUS$G_L Entry] LEFT OUTER JOIN
                      [CRONUS$G_L Account] ON [CRONUS$G_L Entry].[G_L Account No_] = [CRONUS$G_L Account].No_
WHERE     ([CRONUS$G_L Entry].[Posting Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102) AND
                      [CRONUS$G_L Entry].[Posting Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))

Dynamics NAV - Last Purchase Order from Purchase Order Archive View - Summary

SELECT     LastPO.LastPONo AS PONo, LastPO.LastVersion, dbo.[CRONUS$Purchase Header Archive].[Currency Code],
                      dbo.[CRONUS$Purchase Header Archive].[Buy-from Vendor No_], dbo.[CRONUS$Purchase Header Archive].[Pay-to Vendor No_],
                      dbo.[CRONUS$Purchase Header Archive].[Pay-to Name], dbo.[CRONUS$Purchase Header Archive].[Order Date],
                      dbo.[CRONUS$Purchase Header Archive].[Document Date], dbo.[CRONUS$Purchase Header Archive].[Archived By],
                      dbo.[CRONUS$Purchase Header Archive].[No_ Printed], dbo.[CRONUS$Purchase Header Archive].[Receiving No_],
                      SUM(dbo.[CRONUS$Purchase Line Archive].Quantity) AS Qty, SUM(dbo.[CRONUS$Purchase Line Archive].Amount) AS Amt,
                      SUM(dbo.[CRONUS$Purchase Line Archive].[Amount Including VAT]) AS AmtIncVat
FROM         dbo.[CRONUS$Purchase Header Archive] INNER JOIN
                          (SELECT     [Document Type], No_ AS LastPONo, MAX([Version No_]) AS LastVersion
                            FROM          dbo.[CRONUS$Purchase Header Archive] AS [CRONUS$Purchase Header Archive_1]
                            GROUP BY [Document Type], No_
                            HAVING      ([Document Type] = 1)) AS LastPO ON dbo.[CRONUS$Purchase Header Archive].No_ = LastPO.LastPONo AND
                      dbo.[CRONUS$Purchase Header Archive].[Version No_] = LastPO.LastVersion INNER JOIN
                      dbo.[CRONUS$Purchase Line Archive] ON
                      dbo.[CRONUS$Purchase Header Archive].[Document Type] = dbo.[CRONUS$Purchase Line Archive].[Document Type] AND
                      dbo.[CRONUS$Purchase Header Archive].No_ = dbo.[CRONUS$Purchase Line Archive].[Document No_] AND
                      dbo.[CRONUS$Purchase Header Archive].[Version No_] = dbo.[CRONUS$Purchase Line Archive].[Version No_]
GROUP BY dbo.[CRONUS$Purchase Header Archive].[Currency Code], dbo.[CRONUS$Purchase Header Archive].[Buy-from Vendor No_],
                      dbo.[CRONUS$Purchase Header Archive].[Pay-to Vendor No_], dbo.[CRONUS$Purchase Header Archive].[Pay-to Name],
                      dbo.[CRONUS$Purchase Header Archive].[Order Date], dbo.[CRONUS$Purchase Header Archive].[Document Date],
                      dbo.[CRONUS$Purchase Header Archive].[Archived By], dbo.[CRONUS$Purchase Header Archive].[No_ Printed],
                      dbo.[CRONUS$Purchase Header Archive].[Receiving No_], LastPO.LastPONo, LastPO.LastVersion
HAVING      (dbo.[CRONUS$Purchase Header Archive].[Order Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102)) AND
                      (dbo.[CRONUS$Purchase Header Archive].[Order Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))

Dynamics NAV - Last Purchase Order from Purchase Order Archive View - Detail

SELECT     LastPO.LastPONo AS PONo, LastPO.LastVersion, dbo.[CRONUS$Purchase Header Archive].[Currency Code],
                      dbo.[CRONUS$Purchase Header Archive].[Buy-from Vendor No_], dbo.[CRONUS$Purchase Header Archive].[Pay-to Vendor No_],
                      dbo.[CRONUS$Purchase Header Archive].[Pay-to Name], dbo.[CRONUS$Purchase Header Archive].[Order Date],
                      dbo.[CRONUS$Purchase Header Archive].[Document Date], dbo.[CRONUS$Purchase Header Archive].[Archived By],
                      dbo.[CRONUS$Purchase Header Archive].[No_ Printed], dbo.[CRONUS$Purchase Line Archive].No_,
                      dbo.[CRONUS$Purchase Line Archive].Description, dbo.[CRONUS$Purchase Line Archive].[Unit of Measure],
                      dbo.[CRONUS$Purchase Header Archive].[Receiving No_], dbo.[CRONUS$Purchase Line Archive].Quantity,
                      dbo.[CRONUS$Purchase Line Archive].[Direct Unit Cost], dbo.[CRONUS$Purchase Line Archive].[VAT _],
                      dbo.[CRONUS$Purchase Line Archive].Amount, dbo.[CRONUS$Purchase Line Archive].[Amount Including VAT]
FROM         dbo.[CRONUS$Purchase Header Archive] INNER JOIN
                          (SELECT     [Document Type], No_ AS LastPONo, MAX([Version No_]) AS LastVersion
                            FROM          dbo.[CRONUS$Purchase Header Archive] AS [CRONUS$Purchase Header Archive_1]
                            GROUP BY [Document Type], No_
                            HAVING      ([Document Type] = 1)) AS LastPO ON dbo.[CRONUS$Purchase Header Archive].No_ = LastPO.LastPONo AND
                      dbo.[CRONUS$Purchase Header Archive].[Version No_] = LastPO.LastVersion INNER JOIN
                      dbo.[CRONUS$Purchase Line Archive] ON
                      dbo.[CRONUS$Purchase Header Archive].[Document Type] = dbo.[CRONUS$Purchase Line Archive].[Document Type] AND
                      dbo.[CRONUS$Purchase Header Archive].No_ = dbo.[CRONUS$Purchase Line Archive].[Document No_] AND
                      dbo.[CRONUS$Purchase Header Archive].[Version No_] = dbo.[CRONUS$Purchase Line Archive].[Version No_]
WHERE     (dbo.[CRONUS$Purchase Header Archive].[Order Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102)) AND
                      (dbo.[CRONUS$Purchase Header Archive].[Order Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))

Dynamics NAV-LS Retail - Retail Sales - POS Manual Discounts View

SELECT     dbo.[CRONUS$Trans_ Sales Entry].[Store No_], dbo.[CRONUS$Trans_ Sales Entry].[POS Terminal No_],
                      dbo.[CRONUS$Trans_ Sales Entry].[Transaction No_], dbo.[CRONUS$Trans_ Sales Entry].[Receipt No_],
                      dbo.[CRONUS$Trans_ Sales Entry].[Item No_], dbo.CRONUS$Item.Description, dbo.[CRONUS$Trans_ Sales Entry].Price,
                      dbo.[CRONUS$Trans_ Sales Entry].Quantity * - 1 AS Quantity, dbo.[CRONUS$Trans_ Sales Entry].[Periodic Discount],
                      dbo.[CRONUS$Trans_ Sales Entry].[Total Discount], dbo.[CRONUS$Trans_ Sales Entry].[Line Discount],
                      dbo.[CRONUS$Trans_ Infocode Entry].Infocode, dbo.[CRONUS$Trans_ Infocode Entry].Information,
                      dbo.[CRONUS$Trans_ Infocode Entry].Amount, dbo.[CRONUS$Trans_ Sales Entry].[Net Amount] * - 1 AS [Net Amount],
                      dbo.[CRONUS$Trans_ Sales Entry].[VAT Amount], dbo.[CRONUS$Trans_ Sales Entry].[Discount Amount],
                      dbo.[CRONUS$Trans_ Sales Entry].Date, dbo.[CRONUS$Trans_ Sales Entry].[Staff ID],
                      dbo.CRONUS$Staff.[First Name] + N' ' + dbo.CRONUS$Staff.[Last Name] AS StaffName, dbo.[CRONUS$Trans_ Sales Entry].[Deal Line],
                      dbo.[CRONUS$Trans_ Sales Entry].Price * dbo.[CRONUS$Trans_ Sales Entry].Quantity * - 1 AS OrigNetAmt,
                      dbo.[CRONUS$Member Contact].Name,
                      dbo.[CRONUS$Trans_ Sales Entry].[Total Discount] + dbo.[CRONUS$Trans_ Sales Entry].[Line Discount] AS ManualDiscounts
FROM         dbo.[CRONUS$Member Contact] INNER JOIN
                      dbo.[CRONUS$Member Sales Entry] ON
                      dbo.[CRONUS$Member Contact].[Account No_] = dbo.[CRONUS$Member Sales Entry].[Member Account No_] RIGHT OUTER JOIN
                      dbo.[CRONUS$Trans_ Sales Entry] INNER JOIN
                      dbo.CRONUS$Item ON dbo.[CRONUS$Trans_ Sales Entry].[Item No_] = dbo.CRONUS$Item.No_ INNER JOIN
                      dbo.CRONUS$Staff ON dbo.[CRONUS$Trans_ Sales Entry].[Staff ID] = dbo.CRONUS$Staff.ID ON
                      dbo.[CRONUS$Member Sales Entry].[Item No_] = dbo.[CRONUS$Trans_ Sales Entry].[Item No_] AND
                      dbo.[CRONUS$Member Sales Entry].[Line No_] = dbo.[CRONUS$Trans_ Sales Entry].[Line No_] AND
                      dbo.[CRONUS$Member Sales Entry].[Document No_] = dbo.[CRONUS$Trans_ Sales Entry].[Receipt No_] LEFT OUTER JOIN
                      dbo.[CRONUS$Trans_ Infocode Entry] ON
                      dbo.[CRONUS$Trans_ Sales Entry].[Store No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Store No_] AND
                      dbo.[CRONUS$Trans_ Sales Entry].[POS Terminal No_] = dbo.[CRONUS$Trans_ Infocode Entry].[POS Terminal No_] AND
                      dbo.[CRONUS$Trans_ Sales Entry].[Transaction No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Transaction No_] AND
                      dbo.[CRONUS$Trans_ Sales Entry].[Line No_] = dbo.[CRONUS$Trans_ Infocode Entry].[Line No_]
WHERE     (dbo.[CRONUS$Trans_ Sales Entry].[Discount Amount] <> 0) AND (dbo.[CRONUS$Trans_ Sales Entry].Date >= CONVERT(DATETIME,
                      '2016-10-01 00:00:00', 102)) AND (dbo.[CRONUS$Trans_ Sales Entry].Date <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102)) AND
                      (dbo.[CRONUS$Trans_ Sales Entry].[Total Discount] + dbo.[CRONUS$Trans_ Sales Entry].[Line Discount] <> 0)

Dynamics NAV-LS Retail - Retail Sales - Voids View

SELECT     dbo.[CRONUS$POS Voided Transaction].[Receipt No_], dbo.[CRONUS$POS Voided Transaction].[Transaction Type],
                      CASE WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 0 THEN 'Logoff' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] =
                       1 THEN 'Logon' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 2 THEN 'Sales' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
                       = 3 THEN 'Payment' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 4 THEN 'Remove Tender' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
                       = 5 THEN 'Float Entry' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 6 THEN 'Change Tender' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
                       = 7 THEN 'Tender Declaration' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 8 THEN 'Voided' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
                       = 9 THEN 'Open Drawer' WHEN [CRONUS$POS Voided Transaction].[Transaction Type] = 10 THEN 'Neg Adj' WHEN [CRONUS$POS Voided Transaction].[Transaction Type]
                       = 11 THEN 'Phys Inv' END AS TrxDesc, dbo.[CRONUS$POS Voided Transaction].[Sales Staff], dbo.[CRONUS$POS Voided Transaction].[Store No_],
                      dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_], dbo.[CRONUS$POS Voided Transaction].[Staff ID],
                      dbo.[CRONUS$POS Voided Transaction].[Trans_ Date], dbo.[CRONUS$POS Voided Transaction].[Trans_ Time],
                      dbo.[CRONUS$POS Voided Transaction].[Customer No_], dbo.[CRONUS$POS Voided Trans_ Line].[Line No_],
                      dbo.[CRONUS$POS Voided Trans_ Line].Number, dbo.[CRONUS$POS Voided Trans_ Line].[Currency Code],
                      dbo.[CRONUS$POS Voided Trans_ Line].Description, dbo.[CRONUS$POS Voided Trans_ Line].Price,
                      dbo.[CRONUS$POS Voided Trans_ Line].Quantity, dbo.[CRONUS$POS Voided Trans_ Line].[Net Price],
                      dbo.[CRONUS$POS Voided Trans_ Line].[VAT _], dbo.[CRONUS$POS Voided Trans_ Line].[Discount Amount],
                      dbo.[CRONUS$POS Voided Trans_ Line].[Net Amount], dbo.[CRONUS$POS Voided Trans_ Line].[VAT Amount],
                      dbo.[CRONUS$POS Voided Trans_ Line].Amount, dbo.[CRONUS$POS Voided Infocode Entry].Infocode,
                      dbo.[CRONUS$POS Voided Infocode Entry].Information, dbo.[CRONUS$POS Voided Infocode Entry].[Info_ Amt_]
FROM         dbo.[CRONUS$POS Voided Transaction] LEFT OUTER JOIN
                      dbo.[CRONUS$POS Voided Infocode Entry] ON
                      dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_] = dbo.[CRONUS$POS Voided Infocode Entry].[POS Terminal No_] AND
                      dbo.[CRONUS$POS Voided Transaction].[Store No_] = dbo.[CRONUS$POS Voided Infocode Entry].[Store No_] AND
                      dbo.[CRONUS$POS Voided Transaction].[Receipt No_] = dbo.[CRONUS$POS Voided Infocode Entry].[Receipt No_] LEFT OUTER JOIN
                      dbo.[CRONUS$POS Voided Trans_ Line] ON
                      dbo.[CRONUS$POS Voided Transaction].[POS Terminal No_] = dbo.[CRONUS$POS Voided Trans_ Line].[POS Terminal No_] AND
                      dbo.[CRONUS$POS Voided Transaction].[Store No_] = dbo.[CRONUS$POS Voided Trans_ Line].[Store No_] AND
                      dbo.[CRONUS$POS Voided Transaction].[Receipt No_] = dbo.[CRONUS$POS Voided Trans_ Line].[Receipt No_]
WHERE     (dbo.[CRONUS$POS Voided Transaction].[Trans_ Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102)) AND
                      (dbo.[CRONUS$POS Voided Transaction].[Trans_ Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))

Dynamics NAV-LS Retail - Price Change History View

----------------------------------------------------------------------------------------------------------------
--Shows all sales price records
--Change the Date Filters to the period you want
----------------------------------------------------------------------------------------------------------------
SELECT     [CRONUS$Sales Price].[Item No_], CRONUS$Item.Description, [CRONUS$Sales Price].[Starting Date],
                      [CRONUS$Sales Price].[Ending Date], [CRONUS$Sales Price].[Unit Price Including VAT], [CRONUS$Sales Price].[Sales Type],
                      [CRONUS$Sales Price].[Sales Code], [CRONUS$Sales Price].[Currency Code], [CRONUS$Sales Price].[Variant Code],
                      [CRONUS$Sales Price].[Unit of Measure Code], [CRONUS$Sales Price].[Minimum Quantity], [CRONUS$Sales Price].[Unit Price],
                      [CRONUS$Sales Price].[Price Includes VAT], [CRONUS$Sales Price].[Allow Invoice Disc_],
                      [CRONUS$Sales Price].[VAT Bus_ Posting Gr_ (Price)], [CRONUS$Sales Price].[Allow Line Disc_], [CRONUS$Sales Price].[Markup _],
                      [CRONUS$Sales Price].[Profit _], [CRONUS$Sales Price].[Profit (LCY)]
FROM         [CRONUS$Sales Price] INNER JOIN
                      CRONUS$Item ON [CRONUS$Sales Price].[Item No_] = CRONUS$Item.No_
WHERE     ([CRONUS$Sales Price].[Starting Date] >= CONVERT(DATETIME, '2016-10-01 00:00:00', 102) AND
                      [CRONUS$Sales Price].[Starting Date] <= CONVERT(DATETIME, '2017-06-30 00:00:00', 102))
ORDER BY [CRONUS$Sales Price].[Item No_], [CRONUS$Sales Price].[Starting Date], [CRONUS$Sales Price].[Ending Date]

Tuesday, July 18, 2017

SSRS - Add default dates to date parameters


  • Current Month
    • From: 
      • =DateAdd("d",(Day(Today())*-1)+1,Today())
    • To:
      • =dateadd("d",-1,dateadd("m",1,DateAdd("d",(Day(Today())*-1)+1,Today())))

Dynamics GP - Word Forms - How to add terms and conditions to last page

http://dynamicsgpblogster.blogspot.com/2010/09/how-to-add-terms-and-conditions-page-to.html


  • Select line outside of table after total (not in page footer)
  • Insert new 1x1 table
  • Paste terms and conditions into here
  • Select Table
    • Go to Paragraph>Line and Page Breaks
    • Tick Keep Lines Together
    • Tick Page Breaks Before

Dynamics GP - seeRMHATBSRSWrapper - Output SSRS Historical AR TB to Table - including Analytical Accounting

/****** Object:  View [dbo].[BI_SOP_AA_Dtl]    Script Date: 1/26/2017 1:03:28 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_SOP_AA_Dtl]
AS
SELECT DISTINCT
                      RTRIM(CASE WHEN dbo.AAG20000.DOCNUMBR IS NULL THEN sop30300.sopnumbe ELSE AAG20000.DOCNUMBR END) AS SOPNUMBE, dbo.SOP30300.ITEMNMBR,
                      dbo.SOP30300.ITEMDESC, dbo.AAG00401.aaTrxDimCodeDescr, dbo.AAG00401.aaTrxDimCode, dbo.AAG00401.aaTrxDimCodeDescr2,
                      dbo.AAG20000.DOCNUMBR
FROM         dbo.AAG20003 INNER JOIN
                      dbo.AAG20001 ON dbo.AAG20003.aaSubLedgerDistID = dbo.AAG20001.aaSubLedgerDistID AND
                      dbo.AAG20003.aaSubLedgerHdrID = dbo.AAG20001.aaSubLedgerHdrID INNER JOIN
                      dbo.AAG00401 ON dbo.AAG20003.aaTrxDimID = dbo.AAG00401.aaTrxDimID AND dbo.AAG20003.aaTrxCodeID = dbo.AAG00401.aaTrxDimCodeID INNER JOIN
                      dbo.AAG20000 ON dbo.AAG20001.aaSubLedgerHdrID = dbo.AAG20000.aaSubLedgerHdrID FULL OUTER JOIN
                      dbo.SOP30300 ON dbo.AAG20000.DOCNUMBR = dbo.SOP30300.SOPNUMBE

GO




/****** Object:  Table [dbo].[BIT_RMHATB]    Script Date: 1/26/2017 1:01:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BIT_RMHATB](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BIT_RMHATB] ADD [AMCTRXSTT] [smallint] NOT NULL

GO

SET ANSI_PADDING OFF
GO



/****** Object:  View [dbo].[BI_RMHATB2]    Script Date: 1/26/2017 1:03:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_RMHATB2]
AS
SELECT     dbo.BIT_RMHATB.*, dbo.BI_SOP_AA_Dtl.ITEMNMBR, dbo.BI_SOP_AA_Dtl.ITEMDESC, dbo.BI_SOP_AA_Dtl.aaTrxDimCodeDescr,
                      dbo.BI_SOP_AA_Dtl.aaTrxDimCode
FROM         dbo.BIT_RMHATB LEFT OUTER JOIN
                      dbo.BI_SOP_AA_Dtl ON dbo.BIT_RMHATB.DOCNUMBR = dbo.BI_SOP_AA_Dtl.SOPNUMBE

GO
/****** Object:  View [dbo].[BI_RMHATB3]    Script Date: 7/18/2017 7:41:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_RMHATB3]
AS
 select * from(
SELECT *,
         ROW_NUMBER() OVER (PARTITION BY docnumbr ORDER BY docnumbr DESC) AS rn
   FROM BI_RMHATB2) as A

GO
-----------------------------------------------------------------------
CREATE VIEW [dbo].[BI_RMHATB4]
AS
SELECT     APPLY_AMOUNT AS OrigAppAmt, AGING_AMOUNT AS OrigAgingAmt, CURTRXAM AS OrigCurTrxamt, APPLY_AMOUNT, CASE WHEN rn = 1 AND
                      rmdtypal = 1 THEN AGING_AMOUNT ELSE (CASE WHEN rmdtypal > 1 THEN AGING_AMOUNT ELSE 0 END) END AS AGING_AMOUNT, CUSTNMBR, CUSTNAME,
                      BALNCTYP, USERDEF1, CNTCPRSN, PHONE1, SLPRSNID, SALSTERR, PYMTRMID, CRLMTAMT, CRLMTPER, CRLMTPAM, CRLMTTYP, CUSTCLAS, SHRTNAME, ZIP,
                      STATE, CUDSCRIPTN, AGNGDATE, CHCUMNUM, DOCNUMBR, RMDTYPAL, DSCRIPTN, DCURNCYID, ORTRXAMT, CASE WHEN rn = 1 AND
                      rmdtypal = 1 THEN CURTRXAM ELSE (CASE WHEN rmdtypal > 1 THEN CURTRXAM ELSE 0 END) END AS CURTRXAM, AGNGBUKT, CASHAMNT, COMDLRAM,
                      SLSAMNT, COSTAMNT, FRTAMNT, MISCAMNT, TAXAMNT, DISAVAMT, DDISTKNAM, DWROFAMNT, TRXDSCRN, DOCABREV, CHEKNMBR, DOCDATE, DUEDATE,
                      GLPOSTDT, DISCDATE, POSTDATE, DINVPDOF, DCURRNIDX, DXCHGRATE, ORCASAMT, ORSLSAMT, ORCSTAMT, ORDAVAMT, ORFRTAMT, ORMISCAMT, ORTAXAMT,
                      ORCTRXAM, ORORGTRX, DORDISTKN, DORWROFAM, DDENXRATE, DMCTRXSTT, Aging_Period_Amount, APFRDCNM, APFRDCTY, FROMCURR, APTODCNM,
                      APTODCTY, APPTOAMT, ACURNCYID, DATE1, POSTED, ADISTKNAM, AWROFAMNT, PPSAMDED, GSTDSAMT, ACURRNIDX, AXCHGRATE, RLGANLOS, ORAPTOAM,
                      AORDISTKN, AORWROFAM, ADENXRATE, AMCTRXSTT, ITEMNMBR, ITEMDESC, aaTrxDimCodeDescr, aaTrxDimCode, rn
FROM         dbo.BI_RMHATB3

GO


/****** Object:  StoredProcedure [dbo].[BI_seermHATBSRSWrapper]    Script Date: 1/26/2017 1:01:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


 CREATE procedure [dbo].[BI_seermHATBSRSWrapper]   @I_dAgingDate datetime     = NULL,   @I_cStartCustomerNumber char(15)  = NULL,   @I_cEndCustomerNumber char(15)   = NULL,   @I_cStartCustomerName  char(65)  = NULL,   @I_cEndCustomerName  char(65)   = NULL,   @I_cStartClassID  char(15)    = NULL,   @I_cEndClassID char(15)    = NULL,   @I_cStartSalesPersonID char(15)  = NULL,   @I_cEndSalesPersonID char(15)   = NULL,   @I_cStartSalesTerritory char(15)  = NULL,   @I_cEndSalesTerritory char(15)   = NULL,   @I_cStartShortName char(15)   = NULL,   @I_cEndShortName char(15)    = NULL,   @I_cStartState char(5)     = NULL,   @I_cEndState char(5)     = NULL,   @I_cStartZipCode char(11)    = NULL,   @I_cEndZipCode char(11)    = NULL,   @I_cStartPhoneNumber char(21)   = NULL,   @I_cEndPhoneNumber char(21)   = NULL,   @I_cStartUserDefined char(15)   = NULL,   @I_cEndUserDefined char(15)   = NULL,   @I_tUsingDocumentDate tinyint   = NULL,   @I_dStartDate datetime     = NULL,   @I_dEndDate datetime     = NULL,   @I_sIncludeBalanceTypes smallint  = NULL,   @I_tExcludeNoActivity tinyint   = NULL,   @I_tExcludeMultiCurrency tinyint  = NULL,   @I_tExcludeZeroBalanceCustomer tinyint = NULL,   @I_tExcludeFullyPaidTrxs tinyint  = NULL,   @I_tExcludeCreditBalance tinyint  = NULL,   @I_tExcludeUnpostedAppldCrDocs tinyint = NULL,   @I_tConsolidateNAActivity tinyint  = NULL  as   declare @FUNLCURR char(12)  CREATE TABLE #RMHATBAP(  [APFRDCNM] [char](21) NOT NULL,  [APFRDCTY] [smallint] NOT NULL,  [FROMCURR] [char](15) NOT NULL,  [APTODCNM] [char](21) NOT NULL,  [APTODCTY] [smallint] NOT NULL,  [APPTOAMT] [numeric](19, 5) NOT NULL,  [CURNCYID] [char](15) NOT NULL,  [DATE1] [datetime] NOT NULL,  [POSTED] [tinyint] NOT NULL,  [DISTKNAM] [numeric](19, 5) NOT NULL,  [WROFAMNT] [numeric](19, 5) NOT NULL,  [PPSAMDED] [numeric](19, 5) NOT NULL,  [GSTDSAMT] [numeric](19, 5) NOT NULL,  [CURRNIDX] [smallint] NOT NULL,  [XCHGRATE] [numeric](19, 7) NOT NULL,  [RLGANLOS] [numeric](19, 5) NOT NULL,  [ORAPTOAM] [numeric](19, 5) NOT NULL,  [ORDISTKN] [numeric](19, 5) NOT NULL,  [ORWROFAM] [numeric](19, 5) NOT NULL,  [DENXRATE] [numeric](19, 7) NOT NULL,  [MCTRXSTT] [smallint] NOT NULL,  [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL )  CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBAP] ON #RMHATBAP (  [APFRDCNM] ASC,  [APFRDCTY] ASC,  [APTODCNM] ASC,  [APTODCTY] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBAP] ON #RMHATBAP (  [APTODCTY] ASC,  [APTODCNM] ASC,  [APFRDCTY] ASC,  [APFRDCNM] ASC,  [DEX_ROW_ID] ASC )  CREATE TABLE #RMHATBDO(  [CUSTNMBR] [char](15) NOT NULL,  [CHCUMNUM] [char](15) NOT NULL,  [DOCNUMBR] [char](21) NOT NULL,  [RMDTYPAL] [smallint] NOT NULL,  [DSCRIPTN] [char](31) NOT NULL,  [CURNCYID] [char](15) NOT NULL,  [ORTRXAMT] [numeric](19, 5) NOT NULL,  [CURTRXAM] [numeric](19, 5) NOT NULL,  [AGNGBUKT] [smallint] NOT NULL,  [CASHAMNT] [numeric](19, 5) NOT NULL,  [COMDLRAM] [numeric](19, 5) NOT NULL,  [SLSAMNT] [numeric](19, 5) NOT NULL,  [COSTAMNT] [numeric](19, 5) NOT NULL,  [FRTAMNT] [numeric](19, 5) NOT NULL,  [MISCAMNT] [numeric](19, 5) NOT NULL,  [TAXAMNT] [numeric](19, 5) NOT NULL,  [DISAVAMT] [numeric](19, 5) NOT NULL,  [DISTKNAM] [numeric](19, 5) NOT NULL,  [WROFAMNT] [numeric](19, 5) NOT NULL,  [TRXDSCRN] [char](31) NOT NULL,  [DOCABREV] [char](3) NOT NULL,  [CHEKNMBR] [char](21) NOT NULL,  [DOCDATE] [datetime] NOT NULL,  [DUEDATE] [datetime] NOT NULL,  [GLPOSTDT] [datetime] NOT NULL,  [DISCDATE] [datetime] NOT NULL,  [POSTDATE] [datetime] NOT NULL,  [DINVPDOF] [datetime] NOT NULL,  [CURRNIDX] [smallint] NOT NULL,  [XCHGRATE] [numeric](19, 7) NOT NULL,  [ORCASAMT] [numeric](19, 5) NOT NULL,  [ORSLSAMT] [numeric](19, 5) NOT NULL,  [ORCSTAMT] [numeric](19, 5) NOT NULL,  [ORDAVAMT] [numeric](19, 5) NOT NULL,  [ORFRTAMT] [numeric](19, 5) NOT NULL,  [ORMISCAMT] [numeric](19, 5) NOT NULL,  [ORTAXAMT] [numeric](19, 5) NOT NULL,  [ORCTRXAM] [numeric](19, 5) NOT NULL,  [ORORGTRX] [numeric](19, 5) NOT NULL,  [ORDISTKN] [numeric](19, 5) NOT NULL,  [ORWROFAM] [numeric](19, 5) NOT NULL,  [DENXRATE] [numeric](19, 7) NOT NULL,  [MCTRXSTT] [smallint] NOT NULL,  [Aging_Period_Amount] [numeric](19, 5) NOT NULL,  [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL )  CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBDO] ON #RMHATBDO (  [RMDTYPAL] ASC,  [DOCNUMBR] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK1#RMHATBDO] ON #RMHATBDO (  [CUSTNMBR] ASC,  [CURNCYID] ASC,  [DSCRIPTN] ASC,  [DEX_ROW_ID] ASC )  CREATE UNIQUE NONCLUSTERED INDEX [AK2#RMHATBDO] ON #RMHATBDO (  [CURNCYID] ASC,  [DSCRIPTN] ASC,  [DEX_ROW_ID] ASC )  CREATE TABLE #RMHATBCU(  [CUSTNMBR] [char](15) NOT NULL,  [DSCRIPTN] [char](31) NOT NULL,  [AGNGDATE] [datetime] NOT NULL,  [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL )  CREATE UNIQUE NONCLUSTERED INDEX [PK#RMHATBCU] ON #RMHATBCU (  [DSCRIPTN] ASC,  [CUSTNMBR] ASC )  select @FUNLCURR = rtrim(FUNLCURR) from MC40000  If @I_cEndCustomerNumber = ''  begin  set @I_cEndCustomerNumber = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndCustomerName = ''  begin  set @I_cEndCustomerName = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndClassID = ''  begin  set @I_cEndClassID = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndSalesPersonID = ''  begin  set @I_cEndSalesPersonID = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndSalesTerritory = ''  begin  set @I_cEndSalesTerritory = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndShortName = ''  begin  set @I_cEndShortName = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndState = ''  begin  set @I_cEndState = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndZipCode = ''  begin  set @I_cEndZipCode = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndPhoneNumber = ''  begin  set @I_cEndPhoneNumber = 'þþþþþþþþþþþþþþþ'  End  If @I_cEndUserDefined = ''  begin  set @I_cEndUserDefined = 'þþþþþþþþþþþþþþþ'  End  exec [rmHistoricalAgedTrialBalance]   '#RMHATBCU',  '#RMHATBDO',   '#RMHATBAP',   '',    @I_dAgingDate,   @I_cStartCustomerNumber,   @I_cEndCustomerNumber,   @I_cStartCustomerName,   @I_cEndCustomerName,   @I_cStartClassID,   @I_cEndClassID,   @I_cStartSalesPersonID,   @I_cEndSalesPersonID,   @I_cStartSalesTerritory,   @I_cEndSalesTerritory,   @I_cStartShortName,   @I_cEndShortName,  @I_cStartState,   @I_cEndState,   @I_cStartZipCode,   @I_cEndZipCode,   @I_cStartPhoneNumber,   @I_cEndPhoneNumber,   @I_cStartUserDefined,   @I_cEndUserDefined,   1,   0,   @I_tUsingDocumentDate,   @I_dStartDate,   @I_dEndDate,   @I_sIncludeBalanceTypes,   @I_tExcludeNoActivity,   @I_tExcludeMultiCurrency,   @I_tExcludeZeroBalanceCustomer,   @I_tExcludeFullyPaidTrxs,   @I_tExcludeCreditBalance,   @I_tExcludeUnpostedAppldCrDocs,   @I_tConsolidateNAActivity,   @FUNLCURR,   0,   0,   1.0000,   0,   2,   0  
 Drop table BIT_RMHATB
 Select RMHATB.* Into BIT_RMHATB FROM ( select  case when D.RMDTYPAL <> 2 then isnull(A.APPTOAMT,0) else 0 end as APPLY_AMOUNT,  case when D.RMDTYPAL <> 2 then isnull(D.Aging_Period_Amount,0) else 0 end as AGING_AMOUNT,  isnull([CU].[CUSTNMBR], '') as [CUSTNMBR],  isnull([C].[CUSTNAME], '') as [CUSTNAME],  isnull([C].[BALNCTYP],0) as [BALNCTYP],   isnull([C].[USERDEF1], '') as [USERDEF1],  isnull([C].[CNTCPRSN],'') as [CNTCPRSN],  isnull([C].[PHONE1], '') as [PHONE1],  isnull([C].[SLPRSNID], '') as [SLPRSNID],  isnull([C].[SALSTERR], '') as [SALSTERR],   isnull([C].[PYMTRMID], '') as [PYMTRMID],   isnull([C].[CRLMTAMT], 0) as [CRLMTAMT],   isnull([C].[CRLMTPER], 0) as [CRLMTPER],   isnull([C].[CRLMTPAM], 0) as [CRLMTPAM],  isnull([C].[CRLMTTYP], 0) as [CRLMTTYP],  isnull([C].[CUSTCLAS], '') as [CUSTCLAS],  isnull([C].[SHRTNAME], '') as [SHRTNAME],  isnull([C].[ZIP], '') as [ZIP],  isnull([C].[STATE], '') as [STATE],  isnull([CU].[DSCRIPTN], '') as [CUDSCRIPTN],  isnull([CU].[AGNGDATE], '1900-01-01') as [AGNGDATE],  isnull([D].[CHCUMNUM], '') as [CHCUMNUM],  isnull([D].[DOCNUMBR], '') as [DOCNUMBR],  isnull([D].[RMDTYPAL], 0) as [RMDTYPAL],  isnull([D].[DSCRIPTN], '') as [DSCRIPTN],  isnull([D].[CURNCYID], '') as [DCURNCYID],  isnull([D].[ORTRXAMT], 0) as [ORTRXAMT],  isnull([D].[CURTRXAM], 0) as [CURTRXAM],  isnull([D].[AGNGBUKT], 0) as [AGNGBUKT],  isnull([D].[CASHAMNT], 0) as [CASHAMNT],  isnull([D].[COMDLRAM], 0) as [COMDLRAM],  isnull([D].[SLSAMNT], 0) as [SLSAMNT],  isnull([D].[COSTAMNT], 0) as [COSTAMNT],  isnull([D].[FRTAMNT], 0) as [FRTAMNT],  isnull([D].[MISCAMNT], 0) as [MISCAMNT],  isnull([D].[TAXAMNT], 0) as [TAXAMNT],  isnull([D].[DISAVAMT], 0) as [DISAVAMT],  isnull([D].[DISTKNAM], 0) as [DDISTKNAM],  isnull([D].[WROFAMNT], 0) as [DWROFAMNT],  isnull([D].[TRXDSCRN], '') as [TRXDSCRN],  isnull([D].[DOCABREV], '') as [DOCABREV],  isnull([D].[CHEKNMBR], '') as [CHEKNMBR],  isnull([D].[DOCDATE], '1900-01-01') as [DOCDATE],  isnull([D].[DUEDATE], '1900-01-01') as [DUEDATE],  isnull([D].[GLPOSTDT], '1900-01-01') as [GLPOSTDT],  isnull([D].[DISCDATE], '1900-01-01') as [DISCDATE],  isnull([D].[POSTDATE], '1900-01-01') as [POSTDATE],  isnull([D].[DINVPDOF], '1900-01-01') as [DINVPDOF],  isnull([D].[CURRNIDX], 0) as [DCURRNIDX],  isnull([D].[XCHGRATE], 0) as [DXCHGRATE],  isnull([D].[ORCASAMT], 0) as [ORCASAMT],  isnull([D].[ORSLSAMT], 0) as [ORSLSAMT],  isnull([D].[ORCSTAMT], 0) as [ORCSTAMT],  isnull([D].[ORDAVAMT], 0) as [ORDAVAMT],  isnull([D].[ORFRTAMT], 0) as [ORFRTAMT],  isnull([D].[ORMISCAMT], 0) as [ORMISCAMT],  isnull([D].[ORTAXAMT], 0) as [ORTAXAMT],  isnull([D].[ORCTRXAM], 0) as [ORCTRXAM],  isnull([D].[ORORGTRX], 0) as [ORORGTRX],  isnull([D].[ORDISTKN], 0) as [DORDISTKN],  isnull([D].[ORWROFAM], 0) as [DORWROFAM],  isnull([D].[DENXRATE], 0) as [DDENXRATE],  isnull([D].[MCTRXSTT], 0) as [DMCTRXSTT],  isnull([D].[Aging_Period_Amount], 0) as [Aging_Period_Amount],   isnull([A].[APFRDCNM],'') as [APFRDCNM],  isnull([A].[APFRDCTY], 0) as [APFRDCTY],  isnull([A].[FROMCURR], '') as [FROMCURR],  isnull([A].[APTODCNM], '') as [APTODCNM],  isnull([A].[APTODCTY], 0) as [APTODCTY],  isnull([A].[APPTOAMT], 0) as [APPTOAMT],  isnull([A].[CURNCYID], '') as [ACURNCYID],  isnull([A].[DATE1], '1900-01-01') as [DATE1],  isnull([A].[POSTED], 0) as [POSTED],  isnull([A].[DISTKNAM], 0) as [ADISTKNAM],  isnull([A].[WROFAMNT], 0) as [AWROFAMNT],  isnull([A].[PPSAMDED], 0) as [PPSAMDED],  isnull([A].[GSTDSAMT], 0) as [GSTDSAMT],  isnull([A].[CURRNIDX], 0) as [ACURRNIDX],  isnull([A].[XCHGRATE], 0) as [AXCHGRATE],  isnull([A].[RLGANLOS], 0) as [RLGANLOS],  isnull([A].[ORAPTOAM], 0) as [ORAPTOAM],  isnull([A].[ORDISTKN], 0) as [AORDISTKN],  isnull([A].[ORWROFAM], 0) as [AORWROFAM],  isnull([A].[DENXRATE], 0) as [ADENXRATE],  isnull([A].[MCTRXSTT], 0) as [AMCTRXSTT]  FROM #RMHATBCU CU left join #RMHATBDO D on CU.CUSTNMBR = D.CUSTNMBR left join #RMHATBAP A on D.RMDTYPAL = A.APTODCTY and D.DOCNUMBR = A.APTODCNM left join RM00101 C on CU.CUSTNMBR = C.CUSTNMBR ) RMHATB  WHILE  (SELECT COUNT(*) FROM   (SELECT  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT FROM BIT_RMHATB  WHERE ORTRXAMT<>0 GROUP BY  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT HAVING COUNT(*)>1 ) A)<>0  BEGIN UPDATE BIT_RMHATB SET  AGING_AMOUNT=0, ORTRXAMT=0, CURTRXAM=0, Aging_Period_Amount=0  FROM BIT_RMHATB  JOIN  ( SELECT  CUSTNMBR, DOCNUMBR, RMDTYPAL, MAX(APFRDCNM) AS APFRDCNM FROM BIT_RMHATB  GROUP BY  CUSTNMBR, DOCNUMBR, RMDTYPAL, ORTRXAMT  HAVING COUNT(*)>1 ) PARTIALLY_APPLIED  ON  PARTIALLY_APPLIED.CUSTNMBR = BIT_RMHATB.CUSTNMBR AND PARTIALLY_APPLIED.DOCNUMBR = BIT_RMHATB.DOCNUMBR AND PARTIALLY_APPLIED.RMDTYPAL = BIT_RMHATB.RMDTYPAL AND PARTIALLY_APPLIED.APFRDCNM = BIT_RMHATB.APFRDCNM  END
SELECT * FROM BI_RMHATB4

GO