/****** Object: View [dbo].[BI_AP_Apply] Script Date: 13/09/2017 03:49:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AP_Apply]
AS
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 AS VendorClass, 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)
GO
Thursday, August 10, 2017
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
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
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 #### */
This script will update all NOTEINDX fields with a value of 0 to the correct noteindx value for the
/*
** 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
Windows - Faster Navigation - Direct Folders, Autohotkey
Direct folders - Middle click or double click folder navigation
http://www.codesector.com/directfolders
Autohotkey - customizable keystrokes to launch programs
https://autohotkey.com/
http://www.codesector.com/directfolders
Autohotkey - customizable keystrokes to launch programs
https://autohotkey.com/
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
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
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
Subscribe to:
Posts (Atom)