UFN_REVENUE_GENERATEGLACCOUNT_STANDARD_5
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEINFORMATION | xml | IN | |
| @REVENUESPLITINFORMATION | xml | IN | |
| @INFORMATIONSOURCECODE | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUE_GENERATEGLACCOUNT_STANDARD_5
(
@REVENUEINFORMATION xml,
@REVENUESPLITINFORMATION xml,
@INFORMATIONSOURCECODE tinyint, -- 0 revenue tables, 1 revenue batch tables
@CURRENTAPPUSERID uniqueidentifier = null
)
returns @AccountNumbers table
(
AccountString nvarchar(100),
ProjectCode varchar(255),
TransactionTypeCode tinyint,
AccountID uniqueidentifier,
ErrorMessage varchar(max),
MappedValues xml
)
as
begin
declare @REVENUEID uniqueidentifier;
declare @REVENUETRANSACTIONTYPECODE tinyint;
declare @PAYMENTMETHODCODE tinyint;
select
top 1 @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)
-- Because of custom tables and GL mapping validation for batch we need to check if the given information source is supported before continuing.
if (@INFORMATIONSOURCECODE != 0) -- Revenue tables are always supported so we can skip checking...
begin
if (dbo.UFN_REVENUE_GENERATEGLACCOUNT_SUPPORTSINFORMATIONSOURCE(@INFORMATIONSOURCECODE) = 0) -- If the information source is not supported we need to stop.
begin
insert into @AccountNumbers (AccountString, ProjectCode, TransactionTypeCode, AccountID ,ErrorMessage)
values
('', '', null, null, 'Information source is not supported by GL mapping.');
return;
end
end
declare @ParamTable table
(
ID uniqueidentifier,
TableID uniqueidentifier,
RowID uniqueidentifier,
ColNumber int,
FriendlyTableName nvarchar(255)
);
declare @MappedValues UDT_MAPPEDVALUESFORGETACCOUNTS_2;
declare @OfficeNumber integer;
set @OfficeNumber = dbo.UFN_PDACCOUNTCODEMAPPING_GETOFFICENUMBER_2(@REVENUEID, @REVENUESPLITID, @INFORMATIONSOURCECODE, @REVENUETRANSACTIONTYPECODE, @REVENUESPLITTYPECODE, @APPLICATIONCODE, @PAYMENTMETHODCODE, @DESIGNATIONID, @CURRENTAPPUSERID);
if @OfficeNumber != 3
set @APPLICATIONCODE = nullif(@APPLICATIONCODE,0);
declare @AccountSystemID uniqueidentifier;
declare @AcctSysName varchar(50);
declare @DefaultAccountID uniqueidentifier;
declare @DefaultAccountNumber nvarchar(100)
if @INFORMATIONSOURCECODE = 0
select @AccountSystemID = PDACCOUNTSYSTEMID from dbo.FINANCIALTRANSACTION where ID = @REVENUEID
else
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;
-- When performing batch validation, if the suspense (default) accounts are turned on
-- then skip validation, as no errors will ever be presented to the user. In the future,
-- batch could show a warning indicator when a transaction is not mapped and will fall
-- into the suspense account for future reconciliation
if @INFORMATIONSOURCECODE = 1 and @DefaultAccountID is not null and @DefaultAccountNumber is not null
return;
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, case when @OfficeNumber = 5 or @APPLICATIONCODE = 87 then @RowID else V1.TablePK end, 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 or SEGMENTTYPECODE = 2
union
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)
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,
DBO.UFN_PDACCOUNTCODEMAPPING_GETOFFICE_2(@REVENUEID, @REVENUESPLITID, @INFORMATIONSOURCECODE, @REVENUETRANSACTIONTYPECODE, @REVENUESPLITTYPECODE, @APPLICATIONCODE, @PAYMENTMETHODCODE, @DESIGNATIONID, @OFFICENUMBER, @CURRENTAPPUSERID),
DBO.UFN_PDACCOUNTCODEMAPPING_GETREVENUETYPE_2(@REVENUEID, @REVENUESPLITID, @INFORMATIONSOURCECODE, @REVENUETRANSACTIONTYPECODE, @REVENUESPLITTYPECODE, @APPLICATIONCODE, @PAYMENTMETHODCODE, @DESIGNATIONID, @OFFICENUMBER, @CURRENTAPPUSERID),
DBO.UFN_PDACCOUNTCODEMAPPING_GETAPPLICATIONTYPE_2(@REVENUEID, @REVENUESPLITID, @INFORMATIONSOURCECODE, @REVENUETRANSACTIONTYPECODE, @REVENUESPLITTYPECODE, @APPLICATIONCODE, @PAYMENTMETHODCODE, @DESIGNATIONID, @OFFICENUMBER, @CURRENTAPPUSERID),
DBO.UFN_PDACCOUNTCODEMAPPING_GETPAYMENTMETHODTYPE_2(@REVENUEID, @REVENUESPLITID, @INFORMATIONSOURCECODE, @REVENUETRANSACTIONTYPECODE, @REVENUESPLITTYPECODE, @APPLICATIONCODE, @PAYMENTMETHODCODE, @DESIGNATIONID, @OFFICENUMBER, @CURRENTAPPUSERID),
DBO.UFN_PDACCOUNTCODEMAPPING_GETSUBTYPE_2(@REVENUEID, @REVENUESPLITID, @INFORMATIONSOURCECODE, @REVENUETRANSACTIONTYPECODE, @REVENUESPLITTYPECODE, @APPLICATIONCODE, @PAYMENTMETHODCODE, @DESIGNATIONID, @OFFICENUMBER, @CURRENTAPPUSERID),
@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 @x 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(@x)
insert into @AccountNumbers (AccountString, ProjectCode, TransactionTypeCode, AccountID, ErrorMessage, MappedValues)
select distinct GLACCOUNT.ACCOUNTNUMBER, '', TRANSACTIONTYPECODE, GLACCOUNTID, ERRORMESSAGE, convert(nvarchar(max),@x)
from dbo.PDACCOUNTLOOKUPCACHE inner join dbo.GLACCOUNT on PDACCOUNTLOOKUPCACHE.GLACCOUNTID = GLACCOUNT.ID
where PDACCOUNTLOOKUPCACHE.MD5HASHCODE = @HashValue and PDACCOUNTLOOKUPCACHE.MAPPEDVALUES = convert(nvarchar(max),@x)
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, 0 as HASHVALUE, @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,'')
+isnull(', Application type: '+t3.TYPENAME,'')+isnull(', Payment method type: '+t4.TYPENAME,'')
+case when nullif(MV.SubTypeID,'00000000-0000-0000-0000-000000000000') is not null then
coalesce(', Gift-in-kind subtype: '+t6.DESCRIPTION,
', Credit card subtype: '+t5.DESCRIPTION,
', Property subtype: '+t7.DESCRIPTION,
', Other subtype: '+t8.DESCRIPTION,
', Pledge subtype: '+t9.NAME,
', Benefit subtype: '+t10.NAME,
', Resource category subtype: '+t11.DESCRIPTION,
', Currency subtype: '+t12.NAME,
', Program: ' + t13.NAME,
', Fee: ' + t14.NAME,
', Tax: ' + t15.NAME,
', Facility: ' + t16.NAME,
', Merchandise department: ' + t17.NAME,
', 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.PDACCOUNTCODEMAPAPPLICATIONTYPE t3 on MV.ApplicationTypeID = t3.ID
left join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE t4 on MV.PaymentMethodID = t4.ID
left join dbo.CREDITTYPECODE t5 on MV.SubTypeID = t5.ID
left join dbo.GIFTINKINDSUBTYPECODE t6 on MV.SubTypeID = t6.ID
left join dbo.PROPERTYSUBTYPECODE t7 on MV.SubTypeID = t7.ID
left join dbo.OTHERPAYMENTMETHODCODE t8 on MV.SubTypeID = t8.ID
left join dbo.PLEDGESUBTYPE t9 on MV.SubTypeID = t9.ID
left join dbo.BENEFIT t10 on MV.SubTypeID = t10.ID
left join dbo.RESOURCECATEGORYCODE t11 on MV.SubTypeID = t11.ID
left join dbo.CURRENCY t12 on MV.SubTypeID = t12.ID
left join dbo.PROGRAM t13 on MV.SubTypeID = t13.ID
left join dbo.FEE t14 on MV.SubTypeID = t14.ID
left join dbo.TAX t15 on MV.SubTypeID = t15.ID
left join dbo.EVENTLOCATION t16 on MV.SubTypeID = t16.ID
left join dbo.MERCHANDISEDEPARTMENT t17 on MV.SubTypeID = t17.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, 0 as HASHVALUE, @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