USP_GLPOSTPROCESS_INSERTRECORDS

CREATE proc [dbo].[USP_GLPOSTPROCESS_INSERTRECORDS] @ParameterSetID uniqueidentifier, @IncludeByPostDate bit, @PostThroughDateCode int
as

declare @PostThroughDate date
declare @SQL varchar(max)
declare @SQLINSERT varchar(max)

if @IncludeByPostDate = 1
    begin
    if @PostThroughDateCode = 0
        select @PostThroughDate = dateadd(d,1,convert(date,ENDDATE)) from dbo.GLFISCALPERIOD where convert(date,getdate()) between STARTDATE and ENDDATE
    if @PostThroughDateCode = 1
        select @PostThroughDate = dateadd(d,1,convert(date,max(ENDDATE))) from dbo.GLFISCALPERIOD where convert(date,getdate()) > ENDDATE
    if @PostThroughDateCode = 2
        select @PostThroughDate = dateadd(d,1,convert(date,getdate()))
    end

select @SQL = replace((select 'select JOURNALENTRY.ID, JOURNALENTRY.GLACCOUNTID, GLACCOUNT.ACCOUNTSTRING, JOURNALENTRY.TRANSACTIONTYPECODE, JOURNALENTRY.TRANSACTIONAMOUNT as AMOUNT, FINANCIALTRANSACTIONLINEITEM.POSTDATE from dbo.FINANCIALTRANSACTION inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID '
    +' inner join dbo.JOURNALENTRY on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID '
    + case POSTINGOPTIONCODE when 1 then 'inner join '+IDSETREGISTER.DBOBJECTNAME+case IDSETREGISTER.OBJECTTYPE when 1 then '()' else '' end+' as selection on FINANCIALTRANSACTION.ID = selection.ID ' else '' end
    +' inner join dbo.FINANCIALSYSTEMTRANSACTIONTYPE on FINANCIALTRANSACTION.TYPECODE = FINANCIALSYSTEMTRANSACTIONTYPE.TYPECODE '
    +' inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID '
    +' where FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1 '
    +' and FINANCIALTRANSACTION.DELETEDON is null '
    + case POSTINGOPTIONCODE when 0 then 'and FINANCIALSYSTEMTRANSACTIONTYPE.ID = '''+convert(varchar(36),FINANCIALSYSTEMTRANSACTIONTYPEID)+''' ' else '' end
    +'and JOURNALENTRY.GLACCOUNTID is not null '
    + case @IncludeByPostDate when 1 then 'and FINANCIALTRANSACTIONLINEITEM.POSTDATE <= '''+convert(varchar,@PostThroughDate)+''' union ' else ' union ' end
from dbo.GLPOSTPROCESSTRANSACTIONCRITERIA left join dbo.IDSETREGISTER on GLPOSTPROCESSTRANSACTIONCRITERIA.IDSETREGISTERID = IDSETREGISTER.ID
where GLPOSTPROCESSTRANSACTIONCRITERIA.GLPOSTPROCESSID = @ParameterSetID
for xml path('')),'&lt;','<')

set @SQLINSERT = 'insert into #JOURNALENTRIESTOPOST (ID, GLACCOUNTID, ACCOUNTSTRING, TRANSACTIONTYPE, AMOUNT, POSTDATE) '+substring(@SQL,1,len(@SQL)-5)

exec (@SQLINSERT)