USP_FENXT_POSTEXCEPTIONS

            CREATE procedure dbo.USP_FENXT_POSTEXCEPTIONS
            (
                @POSTTOGLPROCESSID uniqueidentifier,
                @TABLENAME sysname,
                @EXCEPTIONTABLENAME sysname,
                @EXCEPTIONTABLECOLUMNLIST nvarchar(2048)
            )
            as

            declare @SEQ int;
            declare @LEN int;
            declare @START int;

            declare @PDACCOUNTSYSTEMID uniqueidentifier = (select PDACCOUNTSYSTEMID from dbo.POSTTOGLPROCESS where ID = @POSTTOGLPROCESSID);

            create table #tempCheckFENXT
                            (
                            JOURNALENTRYID uniqueidentifier,
                            PDACCOUNTSYSTEMID uniqueidentifier,
                            ACCOUNTNUMBER nvarchar(100),
                            UIPROJECTID nvarchar(20),
                            ERRORMSG nvarchar(255)
                            )

            select 
                @SEQ = SEQUENCE, 
                @LEN = LENGTH
            from dbo.PDACCOUNTSTRUCTURE
            where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID 
                and ISPROJECTSEGMENT = 1;

            select 
                @START = sum(LENGTH)+sum(case SEPARATORCODE when 6 then 0 else 1 end)
            from PDACCOUNTSTRUCTURE
            where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID 
                and SEQUENCE < @SEQ;

            declare @sqlstring nvarchar(max) = 
                'insert into #tempCheckFENXT
                    (
                    JOURNALENTRYID, 
                    PDACCOUNTSYSTEMID, 
                    ACCOUNTNUMBER, 
                    UIPROJECTID
                    )
                select 
                    JournalEntry.ID, 
                    GLACCOUNT.PDACCOUNTSYSTEMID,
                    isnull(nullif(ACCOUNTALIAS,''''), stuff(ACCOUNTNUMBER,'+convert(varchar,@START)+','+convert(varchar,@LEN)+'+1,'''')), 
                    DESIGNATIONALTLOOKUPID.ALTLOOKUPID 
                from ' + @TABLENAME + ' 
                    inner join dbo.JOURNALENTRY on '+@TABLENAME+'.GLTRANSACTIONID = JOURNALENTRY.ID 
                    inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
                    left join dbo.V_FENXTINTEGRATION_MAPPEDDESIGNATION on substring(GLACCOUNT.ACCOUNTNUMBER,'+convert(varchar,@START)+'+1,'+convert(varchar,@LEN)+') = V_FENXTINTEGRATION_MAPPEDDESIGNATION.SHORTDESCRIPTION 
                            and V_FENXTINTEGRATION_MAPPEDDESIGNATION.PDACCOUNTSYSTEMID = ''' + convert(varchar(36),@PDACCOUNTSYSTEMID) + ''' 
                    left join dbo.DESIGNATIONALTLOOKUPID on V_FENXTINTEGRATION_MAPPEDDESIGNATION.DESIGNATIONID = DESIGNATIONALTLOOKUPID.DESIGNATIONID and DESIGNATIONALTLOOKUPID.ALTLOOKUPIDTYPECODEID = ''E5837E89-B51A-4BAD-8E06-50D515DC64A2'''

            exec (@sqlString);

            update #tempCheckFENXT
            set ERRORMSG = case 
                when FENXTACCOUNT.ID is null then 'This account number doesn''t exist in Financial Edge NXT' 
                when #tempCheckFENXT.UIPROJECTID is null then 'This account requires a project'
                when FENXTACCOUNT.PREVENTPOSTINGDATE < convert(date,getdate()) then 'This account number has been closed for posting in Financial Edge NXT'
                when FENXTACCOUNT.PROJECTREQUIRED = 1 and FENXTPROJECT.ID is null then 'This account number requires a project for posting in Financial Edge NXT'
                when FENXTPROJECT.PREVENTPOSTINGAFTER = 1 and FENXTPROJECT.POSTINGDATE< convert(date,getdate()) then 'The project associated with this account number is closed for posting in Financial Edge NXT'
                end
            from #tempCheckFENXT
                left join dbo.FENXTACCOUNT on #tempCheckFENXT.ACCOUNTNUMBER = FENXTACCOUNT.ACCOUNTNUMBER and #tempCheckFENXT.PDACCOUNTSYSTEMID = FENXTACCOUNT.PDACCOUNTSYSTEMID
                left join dbo.FENXTPROJECT on #tempCheckFENXT.UIPROJECTID = FENXTPROJECT.UIPROJECTID and #tempCheckFENXT.PDACCOUNTSYSTEMID = FENXTPROJECT.PDACCOUNTSYSTEMID;

            update #tempCheckFENXT
                set ERRORMSG = 'Part of a transaction that is invalid according to Financial Edge NXT posting rules. See the ' + JOURNALENTRY.TRANSACTIONTYPE + ' side of this transaction for details.'
            from #tempCheckFENXT as temp 
                inner join dbo.JOURNALENTRY on temp.JournalEntryid = JOURNALENTRY.ID
                inner join dbo.JOURNALENTRY as JE2 on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = JE2.FINANCIALTRANSACTIONLINEITEMID and JOURNALENTRY.ID != JE2.ID
                inner join #tempCheckFENXT on JE2.ID = #tempCheckFENXT.JOURNALENTRYID
            where temp.ERRORMSG is not null 
            and #tempCheckFENXT.ERRORMSG is null;

            set @sqlstring = 
                'insert into '+@EXCEPTIONTABLENAME+' ('+@EXCEPTIONTABLECOLUMNLIST+')
                select 
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                    JOURNALENTRY.ID,
                    JOURNALENTRY.TRANSACTIONTYPE,
                    JOURNALENTRY.TRANSACTIONTYPECODE, FINANCIALTRANSACTIONLINEITEM.POSTDATE,
                    GLACCOUNT.ACCOUNTNUMBER,
                    '''',
                    JOURNALENTRY.BASEAMOUNT,
                    JOURNALENTRY.COMMENT,
                    #tempCheckFENXT.ERRORMSG,
                    CURRENCYSET.BASECURRENCYID,
                    BASECURRENCY.ISO4217,
                    isnull(BASERATE.RATE,0),
                    JOURNALENTRY.TRANSACTIONAMOUNT,
                    JOURNALENTRY.TRANSACTIONCURRENCYID,
                    TRANSACTIONCURRENCY.ISO4217,
                    JOURNALENTRY.ORGAMOUNT,
                    isnull(ORGANIZATIONRATE.RATE,0)
                from #tempCheckFENXT 
                    inner join dbo.JOURNALENTRY on #tempCheckFENXT.JOURNALENTRYID = JOURNALENTRY.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID
                    inner join dbo.PDACCOUNTSYSTEM on GLACCOUNT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                    inner join dbo.CURRENCY as BASECURRENCY on CURRENCYSET.BASECURRENCYID = BASECURRENCY.ID
                    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
                    left join dbo.CURRENCYEXCHANGERATE as BASERATE on JOURNALENTRY_EXT.PRECALCBASEEXCHANGERATEID = BASERATE.ID
                    left join dbo.CURRENCY as TRANSACTIONCURRENCY on JOURNALENTRY.TRANSACTIONCURRENCYID = TRANSACTIONCURRENCY.ID
                    left join dbo.CURRENCYEXCHANGERATE as ORGANIZATIONRATE on JOURNALENTRY_EXT.PRECALCORGANIZATIONEXCHANGERATEID = ORGANIZATIONRATE.ID
                where #tempCheckFENXT.ERRORMSG is not null;'

            exec (@sqlstring);

            drop table #tempCheckFENXT;