USP_POSTTOGLPROCESS_FORMAT

CREATE procedure dbo.USP_POSTTOGLPROCESS_FORMAT
    (
    @ID uniqueidentifier,
    @TABLENAME nvarchar(49)
    )
as
begin

        declare @FORMAT integer            
        declare @REVENUEPOSTMETHOD tinyint    
        declare @CASHPOSTMETHOD tinyint    
        declare @RECEIVABLEPOSTMETHOD tinyint    
        declare @REVALUATIONGAINLOSSPOSTMETHOD tinyint    
        declare @SUMMARIZED bit = 0;
        declare @PDACCOUNTSYSTEMID uniqueidentifier;
        declare @PROJECTID uniqueidentifier;
        declare @DROPSQL nvarchar(5);
        declare @NOSCHEMATABLENAME nvarchar(45);
        declare @TOTALSEQUENCE int = 1;
        declare @MAXSEGMENT int = 30;
        declare @SEGMENTCOUNTER int = 0;  
        declare @NEWJOURNAL nvarchar(255);
        declare @COLUMNNAME nvarchar(50);
        declare @NORENAME bit = 0;

        create table #ALIASTABLE (ID uniqueidentifier,  ACCOUNT nvarchar(100) collate database_default, ALIAS nvarchar(100) collate database_default)

    select  
      @FORMAT = case T2.QUERYVIEWCATALOGID when 'E6FF3B8D-C9E4-4FE7-9D54-E62A1BF4C4C4' then 1 when '0C30E5DE-6B0E-43DF-B988-5A45D3B3CD30' then 2 when '2A21DDE9-F7C9-4DC9-9B8E-C449CCF33D86' then 3 when '7B7A9234-F2F4-453C-B91E-923D00FBBE6A' then 4 when 'ADD6036D-9B56-48CF-BDD2-1F48F74EDD0A' then 5 when '44853ED6-6F1B-4CAC-A38A-E00196B1D0C2' then 6 when '04858ff2-d0bb-4b1c-9dbc-ebd8b5a67b7a' then 7 else 8 end,
      @REVENUEPOSTMETHOD = T3.REVENUEPOSTMETHODCODE,
      @CASHPOSTMETHOD = T3.CASHPOSTMETHODCODE,
      @RECEIVABLEPOSTMETHOD = T3.ARPOSTMETHODCODE,
      @REVALUATIONGAINLOSSPOSTMETHOD = T3.REVALUATIONGAINLOSSPOSTMETHODCODE,
      @PDACCOUNTSYSTEMID = T1.PDACCOUNTSYSTEMID,
      @PROJECTID = isnull(T4.ID,'99999999-9999-9999-9999-999999999999')
    from     
      dbo.POSTTOGLPROCESS T1 
      join dbo.BUSINESSPROCESSVIEW T2 on T1.BUSINESSPROCESSVIEWID = T2.ID 
      join dbo.POSTTOGLPROCESSDETAIL T3 on T1.ID = T3.ID
      left join dbo.PDACCOUNTSTRUCTURE T4 on T1.PDACCOUNTSYSTEMID = T4.PDACCOUNTSYSTEMID and T4.ISPROJECTSEGMENT = 1
    where t1.ID = @ID;

    select @TOTALSEQUENCE = MAX(SEQUENCE) from PDACCOUNTSTRUCTURE where PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID =  @PDACCOUNTSYSTEMID

    select @NOSCHEMATABLENAME = OBJECT_NAME(OBJECT_ID(@TABLENAME));

    if exists(select CUSTOMJOURNAL from dbo.POSTTOGLPROCESSCUSTOMJOURNAL where LEN(replace(CUSTOMJOURNAL,' ','')) > 0)
        begin
            select @NEWJOURNAL = CUSTOMJOURNAL from dbo.POSTTOGLPROCESSCUSTOMJOURNAL
            set @NEWJOURNAL =REPLACE(@NEWJOURNAL,'''', '''''')
        end 
    else
        select @NEWJOURNAL = PRODUCT from dbo.INSTALLATIONINFO

    if @FORMAT < 8
        begin
            exec('Update ' + @TABLENAME + ' set JOURNAL = ''' + @NEWJOURNAL + '''');
            if @FORMAT < 7
                begin
                    insert into #ALIASTABLE
                        (
                        ID,
                        ACCOUNT,
                        ALIAS
                        )
                    select
                        ID,
                        ACCOUNTNUMBER,
                        ACCOUNTALIAS
                    from dbo.GLACCOUNT
                    where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
                        and  nullif(ACCOUNTALIAS,'') is not null;
                end
        end

    if @REVENUEPOSTMETHOD > 0 or @CASHPOSTMETHOD > 0 or @RECEIVABLEPOSTMETHOD > 0 or @REVALUATIONGAINLOSSPOSTMETHOD > 0
      set @SUMMARIZED = 1

    if @FORMAT < 3   
      begin
        exec('sp_rename ''' + @TABLENAME + '.[ACCOUNTNUMBER]'',''ACCOUNT NUMBER'',''COLUMN''') 
        exec('sp_rename ''' + @TABLENAME + '.[ENCUMBRANCESTATUS]'',''ENCUMBRANCE STATUS'',''COLUMN''')
        exec('sp_rename ''' + @TABLENAME + '.[POSTDATE2]'',''POST DATE'',''COLUMN''')
        exec('sp_rename ''' + @TABLENAME + '.[JOURNALREFERENCE]'',''JOURNAL REFERENCE'',''COLUMN''')        

        if @FORMAT = 2
          begin
            exec('sp_rename ''' + @TABLENAME + '.[PROJECTID]'',''TRANSACTION DISTRIBUTION PROJECT ID'',''COLUMN''')
            exec('sp_rename ''' + @TABLENAME + '.[PROJECTAMOUNT]'',''TRANSACTION DISTRIBUTION AMOUNT'',''COLUMN''')
          end
      end



    if @FORMAT = 1 
      begin
        exec( 'Update ' + @TABLENAME + ' set [POST DATE] = POSTDATE')       
   exec( 'Update ' + @TABLENAME + ' set [JOURNAL REFERENCE] = REFERENCE')   
        exec( 'Update ' + @TABLENAME + ' set [ACCOUNTSTRING] = (select ALIAS from #ALIASTABLE where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID = #ALIASTABLE.ID) where ' + @NOSCHEMATABLENAME + '.GLACCOUNTID in ( select ID from #ALIASTABLE )  ')        
        exec( 'Update ' + @TABLENAME + ' set [ACCOUNT NUMBER] = [ACCOUNTSTRING]')
        if @SUMMARIZED = 1
          begin
           -- exec( 'Update ' + @TABLENAME + ' set [ACCOUNT NUMBER] = ACCOUNTSTRING') 

            exec( 'Update ' + @TABLENAME + ' set [ENCUMBRANCE STATUS] = ''Regular''')
            exec( 'Update ' + @TABLENAME + ' set Type = ''Debit'' where DEBITCREDIT = ''D''')
            exec( 'Update ' + @TABLENAME + ' set Type = ''Credit'' where DEBITCREDIT = ''C''')          
          end
      end

    if @FORMAT = 2
      begin
        exec( 'Update ' + @TABLENAME + ' set [POST DATE] = POSTDATE')       
        exec( 'Update ' + @TABLENAME + ' set [JOURNAL REFERENCE] = REFERENCE')
        exec( 'Update ' + @TABLENAME + ' set [ACCOUNTSTRING] =  (select ALIAS from #ALIASTABLE where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID = #ALIASTABLE.ID) where ' + @NOSCHEMATABLENAME + '.GLACCOUNTID in ( select ID from #ALIASTABLE )')        

        if @SUMMARIZED = 1
          begin
            exec( 'Update ' + @TABLENAME + ' set [ACCOUNT NUMBER] = ACCOUNTSTRING') 
            exec( 'Update ' + @TABLENAME + ' set [ENCUMBRANCE STATUS] = ''Regular''')
            exec( 'Update ' + @TABLENAME + ' set Type = ''Debit'' where DEBITCREDIT = ''D''')
            exec( 'Update ' + @TABLENAME + ' set TYPE = ''Credit'' where DEBITCREDIT = ''C''')          
          end

        if @PROJECTID != '99999999-9999-9999-9999-999999999999'
          begin
            exec( 'Update ' + @TABLENAME + ' set [TRANSACTION DISTRIBUTION PROJECT ID] = (select T1.SHORTDESCRIPTION from PDACCOUNTSEGMENTVALUE T1 join PDACCOUNTSEGMENT T2 on T1.ID = T2.PDACCOUNTSEGMENTVALUEID where T1.PDACCOUNTSTRUCTUREID = ''' + @PROJECTID + '''  and T2.GLACCOUNTID = ' + @NOSCHEMATABLENAME  + '.GLACCOUNTID)')               
            exec( 'Update ' + @TABLENAME + ' set [ACCOUNT NUMBER] = (select dbo.UFN_GLACCOUNT_GETPARTIALACCOUNTSTRING( ''' + @PDACCOUNTSYSTEMID + ''',''' + @PROJECTID + ''',' + @NOSCHEMATABLENAME + '.GLACCOUNTID))')
            exec( 'Update ' + @TABLENAME + ' set [TRANSACTION DISTRIBUTION AMOUNT] = AMOUNT')  
          end
        else
            exec( 'Update ' + @TABLENAME + ' set [ACCOUNT NUMBER] = ACCOUNTSTRING')
      end


    if @FORMAT = 3
      begin
        if exists(select 1 from PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and DESCRIPTION in ('POSTDATE','TRANTYPE','DEBITCREDIT','AMOUNT','JOURNAL','REFERENCE','BATCH' )) 
            set @NORENAME = 1
        set @SEGMENTCOUNTER = @TOTALSEQUENCE 
        While @MAXSEGMENT > @SEGMENTCOUNTER
          Begin 
            set @SEGMENTCOUNTER = @SEGMENTCOUNTER + 1
            set @DROPSQL = 'SEG' + convert(nvarchar, @SEGMENTCOUNTER)
            exec('ALTER TABLE ' + @TABLENAME + ' DROP COLUMN ' + @DROPSQL )
          end

        set @SEGMENTCOUNTER = 1
        While @TOTALSEQUENCE  >= @SEGMENTCOUNTER
          Begin 
            set @DROPSQL = 'SEG' + convert(nvarchar, @SEGMENTCOUNTER) 
            exec( 'Update ' + @TABLENAME + ' set ' + @DROPSQL  + ' = (select T1.SHORTDESCRIPTION from dbo.PDACCOUNTSEGMENTVALUE T1 join dbo.PDACCOUNTSEGMENT T2 on T1.ID = T2.PDACCOUNTSEGMENTVALUEID join dbo.PDACCOUNTSTRUCTURE T3 on T3.ID = T2.PDACCOUNTSTRUCTUREID where T3.SEQUENCE = ' + @SEGMENTCOUNTER + ' and T3.PDACCOUNTSYSTEMID = ''' + @PDACCOUNTSYSTEMID + '''  and T2.GLACCOUNTID = ' + @NOSCHEMATABLENAME  + '.GLACCOUNTID)')               
            if @SEGMENTCOUNTER = 1 
                exec( 'Update ' + @TABLENAME + ' set ' + @DROPSQL  + ' = (select #ALIASTABLE.ALIAS from #ALIASTABLE where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID = #ALIASTABLE.ID) where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID in ( select #ALIASTABLE.ID from #ALIASTABLE )')
            else
                exec( 'Update ' + @TABLENAME + ' set ' + @DROPSQL  + ' = '''' where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID in ( select ID from #ALIASTABLE )')

            select @COLUMNNAME = DESCRIPTION from dbo.PDACCOUNTSTRUCTURE where sequence = @SEGMENTCOUNTER and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID

            if @NORENAME = 0
                exec('sp_rename ''' + @TABLENAME + '.' + @DROPSQL  + ''',''' + @COLUMNNAME  + ''',''COLUMN''')

            set @SEGMENTCOUNTER = @SEGMENTCOUNTER + 1            
          end 
        exec( 'Update ' + @TABLENAME + ' set [ACCOUNTSTRING] = (select ALIAS from #ALIASTABLE where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID = #ALIASTABLE.ID) where ' + @NOSCHEMATABLENAME + '.GLACCOUNTID in ( select ID from #ALIASTABLE )  ')        

      end 

    if @FORMAT = 4
        exec( 'Update ' + @TABLENAME + ' set [ACCOUNTSTRING] = (select #ALIASTABLE.ALIAS from #ALIASTABLE where #ALIASTABLE.ACCOUNT = ' + @NOSCHEMATABLENAME + '.ACCOUNTSTRING) where ' +  @NOSCHEMATABLENAME  + '.ACCOUNTSTRING in ( select #ALIASTABLE.ACCOUNT from #ALIASTABLE )')        

    if @FORMAT = 5
        begin

            exec( 'Update ' + @TABLENAME + ' set [ACCOUNTSTRING] = (select ALIAS from #ALIASTABLE where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID = #ALIASTABLE.ID) where ' + @NOSCHEMATABLENAME + '.GLACCOUNTID in ( select ID from #ALIASTABLE )  ')        
            exec( 'Update ' + @TABLENAME + ' set [ACCOUNTNUMBER] = [ACCOUNTSTRING]')
            exec( 'Update ' + @TABLENAME + ' set [POSTEDWHEN] = ''' + @NEWJOURNAL + ''' + [POSTEDWHEN]')
            --exec('sp_rename ''' + @TABLENAME + '.[ACCOUNTNUMBER]'',''GL Account'',''COLUMN''')        

            --exec('sp_rename ''' + @TABLENAME + '.[NUMBEROFRECORD]'',''No. of Distributions'',''COLUMN''')        

        end

    if @FORMAT = 6
        begin
            exec( 'Update ' + @TABLENAME + ' set [ACCOUNTSTRING] = (select ALIAS from #ALIASTABLE where ' +  @NOSCHEMATABLENAME  + '.GLACCOUNTID = #ALIASTABLE.ID) where ' + @NOSCHEMATABLENAME + '.GLACCOUNTID in ( select ID from #ALIASTABLE )  ')        
            exec( 'Update ' + @TABLENAME + ' set [ACCOUNTNUMBER] = [ACCOUNTSTRING]')        
            exec( 'Update ' + @TABLENAME + ' set [ROWTYPE] = ''SPL'' where BUSINESSPROCESSOUTPUT_PKID != 1')        
        end

    if @FORMAT = 7
        begin 
            if @PROJECTID != '99999999-9999-9999-9999-999999999999'
                begin
                    declare @SEQ int;
                    declare @LEN int;
                    declare @START int;

                    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 varchar(2048) = 'update ' + @TABLENAME + '
                                                            set ACCOUNTNUMBER = isnull(ACCOUNTALIAS, stuff(ACCOUNTSTRING,'+convert(varchar,@START)+','+convert(varchar,@LEN)+'+1,'''')),
                                                                PROJECT = isnull(DESIGNATIONALTLOOKUPID.ALTLOOKUPID,'''') 
                                                        from ' + @TABLENAME + ' 
                                                            left join dbo.V_FENXTINTEGRATION_MAPPEDDESIGNATION on substring(' + @TABLENAME + '.ACCOUNTSTRING,'+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);

                    if @SUMMARIZED = 1
                            exec('update ' + @TABLENAME + ' set [ENCUMBRANCESTATUS] = ''Regular'', [Type] = case DEBITCREDIT when ''D'' then ''Debit'' when ''C'' then ''Credit'' end');
                end
            else if @SUMMARIZED = 1
                    exec('update ' + @TABLENAME + ' set [ACCOUNTNUMBER] = [ACCOUNTSTRING], [ENCUMBRANCESTATUS] = ''Regular'', [Type] = case DEBITCREDIT when ''D'' then ''Debit'' when ''C'' then ''Credit'' end');
        end
end