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