Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our DTA scripts. All of our scripts for Dynamics GP can be found here
DTA Scripts
 
No records to display.
Module List > Module COMPANY..DTA tables
DynDeveloper Table Reference is in Wiki format! Registered users have the ability to improve the table reference, add comments, and generally make the world a better place.
You are currently viewing the COMPANY database.
Change


ClearPixel
Freq
Used
 Display Name
 DTA00100Analysis Group Master
 DTA00200Analysis Codes Master
 DTA00300Account Analysis Groups
 DTA00301Account Analysis Group Codes
 DTA00700Transaction Analysis Report Options
Freq
Used
 Display Name
 DTA10100Transaction Analysis Groups
 DTA10200Transaction Analysis Codes
select * from f_Dyn_GLExtractCC() 
IF OBJECT_ID (N'dbo.f_Dyn_GLExtractCC') IS NOT NULL 
    DROP FUNCTION dbo.f_Dyn_GLExtractCC 
GO 
CREATE FUNCTION dbo.f_Dyn_GLExtractCC() 
RETURNS @gl TABLE (JRNENTRY int, SEQNUMBR int, DEX_ROW_ID int, DTARef varchar(25), 
ACTINDX int, ACCT varchar(4), codeid varchar(25), LTD_AMT numeric(19,2), PTD_AMT numeric(19,2), 
OPENYEAR int, PERIODID int, GLAMT numeric(19,5), linetype varchar(3) ) 
  
AS 
begin 
declare @dtDate datetime 
declare @periodid int 
declare @YEAR1 int 
declare @currentPeriodDT as datetime 
declare @PeriodDT as datetime 
  
--initialize 
set @dtDate = GETDATE() 
  
--get the current period 
select @currentPeriodDT = max(s.PERIODDT) 
    from SY40100 s 
        where SERIES = 2 and ODESCTN = 'General Entry' 
            and PERIODID <> 0 and PERIODDT < @dtDate 
  
--get the period before the current period 
select @PeriodDT = MAX(PeriodDT) 
    from SY40100 
    where SERIES = 2 and ODESCTN = 'General Entry' and PERIODID <> 0 and PERIODDT < @currentPeriodDT 
  
--get the data from the period before the current period 
select @periodid = Periodid, @YEAR1 = year1 
    from SY40100 where PERIODDT = @perioddt; 
  
/* GL TRX */ 
insert into @gl (JRNENTRY, SEQNUMBR ,DEX_ROW_ID , DTARef , ACTINDX, ACCT, codeid,LTD_AMT, PTD_AMT , OPENYEAR , PERIODID , GLAMT, linetype) 
    select gt.JRNENTRY, gt.SEQNUMBR, gt.dex_row_id, dtaCC.dtaref , gt.actindx, left(g.ACTNUMBR_1,4) as ACCT, 
    dtaCC.codeid , convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT, 
    convert(numeric(19,2),case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT, 
    gt.OPENYEAR , gt.PERIODID , gt.debitamt - gt.crdtamnt as GLAMT, 'GL' 
        from GL20000 gt 
            join GL00100 g on gt.ACTINDX = g.ACTINDX 
            left join DTA10200 dtaCC on dtaCC.dtaref = dbo.f_GLDTAJoin(gt.DTA_Index, gt.ORTRXSRC) 
                and dtaCC.SEQNUMBR = gt.SEQNUMBR and dtaCC.groupid = 'CC' 
        where gt.PERIODID <= @periodid 
            and LEFT (gt.ORTRXSRC,5) in ('glrev', 'gltrx',''
            and gt.OPENYEAR = @YEAR1 
union all 
/* SOP */ 
select gt.JRNENTRY, gt.SEQNUMBR, gt.dex_row_id, dtaCC.dtaref as DTARefTP, gt.actindx, left(g.ACTNUMBR_1,4) as ACCT, 
dtaCC.codeid , convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT, 
convert(numeric(19,2),case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT, 
gt.OPENYEAR , gt.PERIODID , gt.debitamt - gt.crdtamnt as GLAMT, 'SOP' 
    from GL20000 gt 
        join GL00100 g on gt.ACTINDX = g.ACTINDX 
        left join DTA10200 dtaCC on dtaCC.DOCNUMBR = gt.ORDOCNUM 
            and dtaCC.RMDTYPAL = gt.ORTRXTYP 
            and dtaCC.SEQNUMBR = gt.OrigSeqNum and dtaCC.groupid = 'CC' 
    where gt.PERIODID <= @periodid 
        and LEFT (gt.ORTRXSRC,5) in ('SLSTE'
        and gt.OPENYEAR = @YEAR1 
union all 
/* Payables */ 
select gt.JRNENTRY, gt.SEQNUMBR, gt.dex_row_id, dtaCC.dtaref as DTARefTP, gt.actindx, left(g.ACTNUMBR_1,4) as ACCT, dtaCC.codeid , 
convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT, convert(numeric(19,2),
case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT, 
gt.OPENYEAR , gt.PERIODID , gt.debitamt - gt.crdtamnt as GLAMT, 'PM' 
    from GL20000 gt 
        join GL00100 g on gt.ACTINDX = g.ACTINDX 
        left join DTA10200 dtaCC on dtaCC.DOCNUMBR = gt.ORCTRNUM 
            and dtaCC.SEQNUMBR = gt.OrigSeqNum 
            and gt.ORTRXTYP <> 6 
            and dtaCC.groupid = 'CC' 
    where gt.PERIODID <= @periodid 
        and LEFT (gt.ORTRXSRC,5) in ('PMCHK', 'PMPAY','PMTRX','PMVPY'
        and gt.OPENYEAR = @YEAR1 
union all 
/* POP */ 
select gt.JRNENTRY, gt.SEQNUMBR, gt.dex_row_id, dtaCC.dtaref as DTARefTP, gt.actindx, left(g.ACTNUMBR_1,4) as ACCT, 
dtaCC.codeid , convert(numeric(19,2),coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt)) as LTD_AMT, 
convert(numeric(19,2),case when gt.PERIODID = @periodid then coalesce(dtaCC.CODEAMT, gt.debitamt - gt.crdtamnt) else 0 end)as PTD_AMT, 
gt.OPENYEAR , gt.PERIODID , gt.debitamt - gt.crdtamnt as GLAMT, 'POP' 
    from GL20000 gt 
        join GL00100 g on gt.ACTINDX = g.ACTINDX 
        left join DTA10200 dtaCC on dtaCC.DOCNUMBR = gt.ORCTRNUM 
            and dtaCC.SEQNUMBR = gt.OrigSeqNum 
            and dtaCC.groupid = 'CC' 
    where gt.PERIODID <= @periodid 
        and LEFT (gt.ORTRXSRC,5) in ('POIVC', 'RECVG'
        and gt.OPENYEAR = @YEAR1 
RETURN 
end 
GO
 
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3