UFN_REVENUE_GENERATEGLACCOUNT_PLEDGEWRITEOFF
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEINFORMATION | xml | IN | |
| @REVENUESPLITINFORMATION | xml | IN | |
| @REASONCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_REVENUE_GENERATEGLACCOUNT_PLEDGEWRITEOFF]
(
@REVENUEINFORMATION xml,
@REVENUESPLITINFORMATION xml,
@REASONCODEID uniqueidentifier
)
returns @AccountNumbers table
(
AccountString nvarchar(100),
ProjectCode varchar(255),
TransactionTypeCode tinyint,
AccountID uniqueidentifier,
ErrorMessage varchar(max),
MappedValues xml
)
as
begin
declare @REVENUETRANSACTIONTYPECODE tinyint;
declare @PAYMENTMETHODCODE tinyint;
declare @REVENUEID uniqueidentifier;
--Always use FTM tables
declare @INFORMATIONSOURCECODE tinyint = 0 -- 0 revenue tables, 1 revenue batch tables
declare @CURRENTAPPUSERID uniqueidentifier = null
select
@REVENUEID = T.c.value('(ID)[1]', 'uniqueidentifier'),
@REVENUETRANSACTIONTYPECODE = T.c.value('(TRANSACTIONTYPECODE)[1]', 'int'),
@PAYMENTMETHODCODE = T.c.value('(PAYMENTMETHODCODE)[1]', 'int')
from @REVENUEINFORMATION.nodes('/REVENUEINFORMATION/ITEM') T(c);
declare @REVENUESPLITID uniqueidentifier;
declare @REVENUESPLITTYPECODE tinyint;
declare @APPLICATIONCODE tinyint;
declare @DESIGNATIONID uniqueidentifier;
select
@REVENUESPLITID = T.c.value('(ID)[1]', 'uniqueidentifier'),
@REVENUESPLITTYPECODE = T.c.value('(TYPECODE)[1]', 'int'),
@APPLICATIONCODE = T.c.value('(APPLICATIONCODE)[1]', 'int'),
@DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier')
from @REVENUESPLITINFORMATION.nodes('/REVENUESPLITINFORMATION/ITEM') T(c)
declare @ParamTable table
(
ID uniqueidentifier,
TableID uniqueidentifier,
RowID uniqueidentifier,
ColNumber int,
FriendlyTableName nvarchar(255)
);
declare @MappedValues UDT_MAPPEDVALUESFORGETACCOUNTS_2;
declare @AccountSystemID uniqueidentifier;
declare @AcctSysName varchar(50);
declare @DefaultAccountID uniqueidentifier;
declare @DefaultAccountNumber nvarchar(100)
set @AccountSystemID = dbo.UFN_PDACCOUNT_GETACCOUNTSYSTEMID_3(@REVENUEINFORMATION);
if @AccountSystemID is null
set @AccountSystemID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';
select @AcctSysName = PDACCOUNTSYSTEM.NAME,
@DefaultAccountID = GLACCOUNT.ID,
@DefaultAccountNumber = GLACCOUNT.ACCOUNTNUMBER
from dbo.PDACCOUNTSYSTEM left join dbo.GLACCOUNT on PDACCOUNTSYSTEM.DEFAULTGLACCOUNTID = GLACCOUNT.ID
where PDACCOUNTSYSTEM.ID = @AccountSystemID;
declare @RowID uniqueidentifier = '99999999-9999-9999-9999-999999999999';
with CTE (TableID, FRIENDLYTABLENAME, SEGMENTTYPECODE)
as
(
select distinct
isnull(tables2.TABLEID, PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID) as TableID,
isnull(tables2.FRIENDLYTABLENAME, PDACCOUNTTABLESAVAILABLEFORSEGMENT.FRIENDLYTABLENAME) as FRIENDLYTABLENAME,
isnull(tableS2.SEGMENTTYPECODE,PDACCOUNTTABLESAVAILABLEFORSEGMENT.SEGMENTTYPECODE) as SEGMENTTYPECODE
from dbo.PDACCOUNTSTRUCTURE
inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT on PDACCOUNTSTRUCTURE.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID
left join dbo.PDCOMPOSITESEGMENTTABLELIST on PDACCOUNTTABLESAVAILABLEFORSEGMENT.TABLEID = PDCOMPOSITESEGMENTTABLELIST.PDCOMPOSITESEGMENTID
left join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT tables2 on PDCOMPOSITESEGMENTTABLELIST.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = tables2.ID
where PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = @AccountSystemID and (PDACCOUNTSTRUCTURE.SEGMENTTYPE = 3 or PDACCOUNTSTRUCTURE.SEGMENTTYPE = 4)
)
insert into @ParamTable (ID, TableID, RowID, ColNumber, FriendlyTableName)
select @RowID, TABLEID, V1.TablePK, row_number() over (order by FRIENDLYTABLENAME), FRIENDLYTABLENAME
from
(
select TABLEID,
case TABLEID
when 'A299FF27-A88C-4286-B59A-F818E50538DC' then dbo.UFN_PDACCOUNT_GETCONSTITUENCY_3(@REVENUEID, @INFORMATIONSOURCECODE, @REVENUEINFORMATION)
when '3EF5D061-5692-40F3-B8AD-1367A8C6FAD5' then dbo.UFN_PDACCOUNT_GETEVENTID_3(@REVENUESPLITINFORMATION)
when 'B328E14B-9269-4B6C-BF93-7B10C70F71C3' then dbo.UFN_PDACCOUNT_GETEVENTCATEGORY_3(@REVENUESPLITINFORMATION)
when '3298E2CE-EF37-4681-AC12-050979B6C5F3' then dbo.UFN_PDACCOUNT_GETAPPEALID_3(@REVENUEINFORMATION)
when '5FFF709E-142A-4997-B869-1331B1791108' then dbo.UFN_PDACCOUNT_GETMEMBERSHIPLEVEL_3(@REVENUESPLITINFORMATION)
when '130FB361-1C09-4884-830D-E6DA624635AD' then dbo.UFN_PDACCOUNT_GETCAMPAIGN_3(@REVENUESPLITINFORMATION)
when 'AEBA8FD5-42B6-4D37-93EB-8916BEC1385A' then @DESIGNATIONID
when '35ACADB7-4D01-438A-8A3B-08366EB680DE' then dbo.UFN_PDACCOUNT_GETCHANNELCODE_3(@REVENUEINFORMATION)
when '13BD89C4-47B3-48FF-9044-4F07D0D0664D' then dbo.UFN_PDACCOUNT_GETFUNDRAISINGPURPOSE_3(@REVENUESPLITINFORMATION)
when '20204242-BA0F-4788-8A88-B235946EBECF' then dbo.UFN_PDACCOUNT_GETPURPOSECATEGORY_3(@REVENUESPLITINFORMATION)
when '1278C0FF-EC92-4DF6-B51F-5E2F17C43298' then dbo.UFN_PDACCOUNT_GETPROGRAM_3(@REVENUESPLITINFORMATION)
when '5B93DEF5-AAA7-482E-B8F7-169EE0DAA53D' then dbo.UFN_PDACCOUNT_GETREVENUECATEGORY_3(@REVENUESPLITINFORMATION)
when '9917A3F1-AB96-45EB-88AD-0AECAFE2C3B1' then dbo.UFN_PDACCOUNT_GETAPPLICATIONSEGMENT_3(@REVENUESPLITINFORMATION)
when 'BDC9B454-976E-4DAF-B6CF-1416CFC3392C' then dbo.UFN_PDACCOUNT_GETSITEID_3(@REVENUESPLITID, @APPLICATIONCODE, @REVENUESPLITINFORMATION)
when '55B0FAF8-DFD3-4869-A9E7-3747A32206D1' then dbo.UFN_PDACCOUNT_GETMERCHANDISEDEPARTMENT_3(@REVENUESPLITINFORMATION)
end as TablePK, FRIENDLYTABLENAME
from CTE where SEGMENTTYPECODE = 1
union all
select
CTE.TABLEID, ROWID, tf.FRIENDLYTABLENAME
from dbo.UFN_PDACCOUNT_GETCUSTOMTABLESFORSEGMENT_2(@REVENUEID, @REVENUESPLITID, @INFORMATIONSOURCECODE, @REVENUETRANSACTIONTYPECODE, @REVENUESPLITTYPECODE,
@APPLICATIONCODE, @PAYMENTMETHODCODE, @DESIGNATIONID, @CURRENTAPPUSERID) tf
inner join CTE on tf.TABLEID = CTE.TABLEID and CTE.SEGMENTTYPECODE = 2) V1
if (select count(*) from @ParamTable) = 0
insert into @ParamTable (ID, TableID, RowID, ColNumber) values (@RowID ,null,null,1);
--Pledge defaults
declare @OFFICENUMBERID uniqueidentifier = '4C565937-8A30-42D0-A0FC-64D269DBC898' --Pledges
declare @REVENUETYPE uniqueidentifier = '3D54D5E6-D0EE-4261-9439-B4CF6C113A22' --Pledge Write-off Revenue Type
declare @APPLICATIONTYPE uniqueidentifier --Not Used
declare @PAYMENTMETHODTYPE uniqueidentifier; --Not Used
declare @SUBTYPEID uniqueidentifier = isnull(@REASONCODEID, '99999999-9999-9999-9999-999999999999')
--Membership installment plan
if @REVENUETRANSACTIONTYPECODE = 15
begin
set @OFFICENUMBERID = 'D38E0F1D-845F-42CD-837E-8030F538A32B' -- Membership installment plan
if isnull(@APPLICATIONCODE,0) = 0
set @REVENUETYPE = '1F772FDD-AB23-4798-9760-0D63C28F1705' -- OFFICEID = 19 and NAMEID = 16; -- Membership installment write-off - Contributed
else if @APPLICATIONCODE = 5
set @REVENUETYPE = '70563403-C917-4A18-A3B3-01FAF1EFA87B' -- OFFICEID = 19 and NAMEID = 4; -- Membership installment write-off - Earned
else if @APPLICATIONCODE = 18
set @REVENUETYPE = 'DA9C4042-B109-4716-BA45-6ED9834BEF88' -- where OFFICEID = 19 and NAMEID = 32; -- Membership installment write-off - Add-on (Earned)
end
insert into @MappedValues(
ID, PDAccountCodeMapOfficeID, RevenueTypeID, ApplicationTypeID, PaymentMethodID, SubTypeID, PDAccountSystemID,
Segment02TableID, Segment02ID, Segment03TableID, Segment03ID, Segment04TableID, Segment04ID, Segment05TableID, Segment05ID, Segment06TableID, Segment06ID,
Segment07TableID, Segment07ID, Segment08TableID, Segment08ID, Segment09TableID, Segment09ID, Segment10TableID, Segment10ID, Segment11TableID, Segment11ID,
Segment12TableID, Segment12ID, Segment13TableID, Segment13ID, Segment14TableID, Segment14ID, Segment15TableID, Segment15ID, Segment16TableID, Segment16ID,
Segment17TableID, Segment17ID, Segment18TableID, Segment18ID, Segment19TableID, Segment19ID, Segment20TableID, Segment20ID, Segment21TableID, Segment21ID,
Segment22TableID, Segment22ID, Segment23TableID, Segment23ID, Segment24TableID, Segment24ID, Segment25TableID, Segment25ID, Segment26TableID, Segment26ID,
Segment27TableID, Segment27ID, Segment28TableID, Segment28ID, Segment29TableID, Segment29ID, Segment30TableID, Segment30ID)
select
Pvt1.ID, @OfficeNumberId, @REVENUETYPE, @APPLICATIONTYPE, @PAYMENTMETHODTYPE, @SubTypeId, @AccountSystemID,
Pvt1.[1], Pvt2.[1], Pvt1.[2], Pvt2.[2], Pvt1.[3], Pvt2.[3], Pvt1.[4], Pvt2.[4], Pvt1.[5], Pvt2.[5], Pvt1.[6],
Pvt2.[6], Pvt1.[7], Pvt2.[7], Pvt1.[8], Pvt2.[8], Pvt1.[9], Pvt2.[9], Pvt1.[10], Pvt2.[10], Pvt1.[11], Pvt2.[11],
Pvt1.[12], Pvt2.[12], Pvt1.[13], Pvt2.[13], Pvt1.[14], Pvt2.[14], Pvt1.[15], Pvt2.[15], Pvt1.[16], Pvt2.[16],
Pvt1.[17], Pvt2.[17], Pvt1.[18], Pvt2.[18], Pvt1.[19], Pvt2.[19], Pvt1.[20], Pvt2.[20], Pvt1.[21], Pvt2.[21],
Pvt1.[22], Pvt2.[22], Pvt1.[23], Pvt2.[23], Pvt1.[24], Pvt2.[24], Pvt1.[25], Pvt2.[25], Pvt1.[26], Pvt2.[26],
Pvt1.[27], Pvt2.[27], Pvt1.[28], Pvt2.[28], Pvt1.[29], Pvt2.[29]
from
(
select ID, convert(char(36),TableID) as TableID, ColNumber
from @ParamTable) As Source1
PIVOT
(MAX(TableID) for ColNumber in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29])
) as Pvt1
inner join (
select ID, convert(char(36),RowID) as RowID, ColNumber
from @ParamTable) as Source2
PIVOT
(MAX(RowID) for Colnumber in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29])
) as Pvt2 on Pvt1.ID = Pvt2.ID --Currently this is meaningless since there's only a single row being generated at a time
declare @MAPPEDVALUESXML xml = (select tv1.* from @MappedValues tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
declare @HASHVALUE uniqueidentifier = dbo.UFN_GLACCOUNT_MD5_HASH_SEGMENTS(@MAPPEDVALUESXML)
insert into @AccountNumbers (AccountString, ProjectCode, TransactionTypeCode, AccountID, ErrorMessage, MappedValues)
select distinct GLACCOUNT.ACCOUNTNUMBER, '', TRANSACTIONTYPECODE, GLACCOUNTID, ERRORMESSAGE, convert(nvarchar(max),@MAPPEDVALUESXML)
from dbo.PDACCOUNTLOOKUPCACHE inner join dbo.GLACCOUNT on PDACCOUNTLOOKUPCACHE.GLACCOUNTID = GLACCOUNT.ID
where PDACCOUNTLOOKUPCACHE.MD5HASHCODE = @HASHVALUE and PDACCOUNTLOOKUPCACHE.MAPPEDVALUES = convert(nvarchar(max),@MAPPEDVALUESXML)
if @@rowcount != 2
begin
delete from @AccountNumbers --clear out incomplete results obtained using the hashcode
declare @SegmentValues UDT_GLACCOUNT_SEGMENTVALUES;
insert into @SegmentValues
select s.ID, s.PDAccountStructureID, s.PDAccountSegmentValueID, s.TransactionType, s.SegType
from UFN_GLACCOUNT_GETSEGMENTWORKTABLE(@mappedvalues) s
--Assume no errors
declare @AccountValues table (ID uniqueidentifier, DEBITGLACCOUNTSID uniqueidentifier, DEBITGLACCOUNTNUMBER varchar(100), CREDITGLACCOUNTSID uniqueidentifier, CREDITGLACCOUNTNUMBER varchar(100), ACCOUNTNUMBERERROR tinyint)
insert into @AccountValues (ID, DEBITGLACCOUNTSID, DEBITGLACCOUNTNUMBER, CREDITGLACCOUNTSID, CREDITGLACCOUNTNUMBER, ACCOUNTNUMBERERROR)
select ID, DEBITGLACCOUNTSID, DEBITGLACCOUNTNUMBER, CREDITGLACCOUNTSID, CREDITGLACCOUNTNUMBER, ACCOUNTNUMBERERROR
from dbo.UFN_GLACCOUNT_GETACCOUNTSFROMPARAMETERS_3(@MappedValues, @SegmentValues)
if exists (select 1 from @AccountValues where ACCOUNTNUMBERERROR != 0) --An error exists so do all the stuff to give nice error messages
begin
if exists(select 1 from dbo.PDACCOUNTSTRUCTURE where ISCONSTANTVALUE = 1 and PDACCOUNTSYSTEMID = @AccountSystemID)
insert into @ParamTable(ID, TableID, RowID, ColNumber, FriendlyTableName)
select '99999999-9999-9999-9999-999999999999', A.TABLEID, S.ID, 0, null
from dbo.PDACCOUNTSTRUCTURE S
inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT A on A.ID = S.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
where ISCONSTANTVALUE = 1 and PDACCOUNTSYSTEMID = @AccountSystemID
declare @MissingMappings table
(
ID uniqueidentifier,
SegmentName nvarchar(100),
TableID uniqueidentifier,
RowID uniqueidentifier,
FriendlyTableName nvarchar(255)
);
--Get a list of segments that are actually missing a value so we can provide the user with a better error message
insert into @MissingMappings (ID, SegmentName, TableID, RowID, FriendlyTableName)
select P.ID, S.[DESCRIPTION], P.TableID, P.RowID, P.FriendlyTableName
from dbo.PDACCOUNTSTRUCTURE S
left join (select distinct T.PDAccountStructureID, T.PDAccountSegmentValueID
from @SegmentValues T
where T.SegType != 1) SWT on SWT.PDAccountStructureID = S.ID
inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT A on A.ID = S.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
left join dbo.PDCOMPOSITESEGMENTTABLELIST L on A.TABLEID = L.PDCOMPOSITESEGMENTID
left join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT A2 on L.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = A2.ID
inner join @ParamTable P on P.TableID = COALESCE(A2.TABLEID, A.TABLEID, S.ID)
where (SWT.PDAccountStructureID is null or SWT.PDAccountSegmentValueID is null) and (S.SEGMENTTYPE = 3 or S.SEGMENTTYPE = 4) and S.PDACCOUNTSYSTEMID = @AccountSystemID
insert into @AccountNumbers (AccountString, ProjectCode, TransactionTypeCode, AccountID, ErrorMessage, MappedValues)
select
AccountString,
'',
case TranTypeCode when 'DEBITGLACCOUNTNUMBER' then 0 else 1 end,
case TranTypeCode when 'DEBITGLACCOUNTNUMBER' then DEBITGLACCOUNTSID else CREDITGLACCOUNTSID end ,
ErrorMessage,
(
select tv1.*, ErrorMessage as ERRORMESSAGE, ACCOUNTNUMBERERROR as ERRORCODE, @HashValue as MD5HASHVALUE from @MappedValues tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
from (
select ID, case when DEBITGLACCOUNTSID IS null then coalesce(@DefaultAccountNumber, '') else DEBITGLACCOUNTNUMBER end as DEBITGLACCOUNTNUMBER
, case when CREDITGLACCOUNTSID IS null then coalesce(@DefaultAccountNumber, '') else CREDITGLACCOUNTNUMBER end as CREDITGLACCOUNTNUMBER
, isnull(DEBITGLACCOUNTSID,@DefaultAccountID) as DEBITGLACCOUNTSID
, isnull(CREDITGLACCOUNTSID,@DefaultAccountID) as CREDITGLACCOUNTSID,
case ACCOUNTNUMBERERROR
when 0 then ''
when 1 then (
select
'In account system "'+ @AcctSysName + '", a mapping does not exist for Office: '+ t1.NAME +
isnull(', Revenue type: '+t2.TYPENAME,'') +
case when nullif(MV.SubTypeID,'00000000-0000-0000-0000-000000000000') is not null then
coalesce(', Reason code: '+t3.Code + ' - ' + t3.Description, ', Unknown subtype')
else
', Subtype not specified'
end
from @MappedValues MV inner join dbo.PDACCOUNTCODEMAPOFFICE t1 on MV.PDAccountCodeMapOfficeID = t1.ID
left join dbo.PDACCOUNTCODEMAPREVENUETYPE t2 on MV.RevenueTypeID = t2.ID
left join dbo.WRITEOFFREASONCODE t3 on MV.SUBTYPEID = t3.ID
where MV.ID = a.ID)
when 2 then
'In account system "'+ @AcctSysName + '", account segments have not been defined for the segment values: ' +
replace(replace(substring((
select '; Segment ''' + T.SegmentName + ''': ' +
replace(replace(substring((
select distinct ', ' + case when t10.FriendlyTableName is null then '' else t10.FriendlyTableName+ ': ' end +
coalesce(PDACCOUNTSEGMENTMAPPINGVIEW.LONGDESCRIPTION, PDCOMPOSITESEGMENTMAPPINGVIEW.LONGDESCRIPTION,
case when S.ID is not null then '<Constant value>' else null end, '<Not used>')
from @MissingMappings t10
left join dbo.PDACCOUNTSEGMENTMAPPINGVIEW on t10.RowID = PDACCOUNTSEGMENTMAPPINGVIEW.LONGDESCRIPTIONID
left join dbo.PDCOMPOSITESEGMENTMAPPINGVIEW on t10.RowID = PDCOMPOSITESEGMENTMAPPINGVIEW.LONGDESCRIPTIONID
left join dbo.PDACCOUNTSTRUCTURE S on t10.RowID = S.ID
where t10.ID = T.ID and t10.SegmentName = T.SegmentName
for xml path('')), 3, 4000),'<','<'),'>','>')
from @MissingMappings T
where T.ID = a.ID
group by T.ID, T.SegmentName
for xml path('')), 3, 4000),'<','<'),'>','>')
when 3 then
'In account system "' + @AcctSysName + '", the account "' + case when DEBITGLACCOUNTSID IS null then DEBITGLACCOUNTNUMBER else CREDITGLACCOUNTNUMBER end + '" does not exist.'
end as ErrorMessage
,ACCOUNTNUMBERERROR
from @AccountValues a) p
unpivot
(AccountString FOR TranTypeCode in (DEBITGLACCOUNTNUMBER, CREDITGLACCOUNTNUMBER) ) as unpvt
end
else --No error, so I don't need the nice error message
insert into @AccountNumbers (AccountString, ProjectCode, TransactionTypeCode, AccountID, ErrorMessage, MappedValues)
select
AccountString,
'',
case TranTypeCode when 'DEBITGLACCOUNTNUMBER' then 0 else 1 end,
case TranTypeCode when 'DEBITGLACCOUNTNUMBER' then DEBITGLACCOUNTSID else CREDITGLACCOUNTSID end ,
'' ,
(
select tv1.*, '' as ERRORMESSAGE, ACCOUNTNUMBERERROR as ERRORCODE, @HashValue as MD5HASHVALUE from @MappedValues tv1 for XML raw('ROW'), type, elements, root('MAPPEDVALUES'), BINARY BASE64)
from (
select ID, case when DEBITGLACCOUNTSID IS null then coalesce(@DefaultAccountNumber, '') else DEBITGLACCOUNTNUMBER end as DEBITGLACCOUNTNUMBER
, case when CREDITGLACCOUNTSID IS null then coalesce(@DefaultAccountNumber, '') else CREDITGLACCOUNTNUMBER end as CREDITGLACCOUNTNUMBER
, isnull(DEBITGLACCOUNTSID,@DefaultAccountID) as DEBITGLACCOUNTSID
, isnull(CREDITGLACCOUNTSID,@DefaultAccountID) as CREDITGLACCOUNTSID
,'' as ErrorMessage
,ACCOUNTNUMBERERROR
from @AccountValues a) p
unpivot
(AccountString FOR TranTypeCode in (DEBITGLACCOUNTNUMBER, CREDITGLACCOUNTNUMBER) ) as unpvt
end
return
end