USP_DATAFORMTEMPLATE_VIEW_UNMAPPEDTRANSACTIONMESSAGE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_UNMAPPEDTRANSACTIONMESSAGE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ERRORCODE tinyint = null output,
    @ERRORMESSAGE nvarchar(max) = null output,
    @OFFICEID integer = null output,
    @REVENUETYPEID uniqueidentifier = null output,
    @APPLICATIONTYPEID uniqueidentifier = null output,
    @PAYMENTMETHODID uniqueidentifier = null output,
    @MISSINGSEGMENTMAPPING nvarchar(72) = null output,
    @ISCOMPOSITE bit = null output,
    @COMPOSITESEGMENT1ID uniqueidentifier = null output,
    @COMPOSITESEGMENT2ID uniqueidentifier = null output,
    @COMPOSITESEGMENT3ID uniqueidentifier = null output,
    @COMPOSITESEGMENT4ID uniqueidentifier = null output,
    @MISSINGACCOUNT nvarchar(max) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    declare @MAPPEDVALUES xml;
    declare @PDACCOUNTSYSTEMID uniqueidentifier;
    select @DATALOADED = 1, @MAPPEDVALUES = E.MAPPEDVALUES from dbo.GLACCOUNTMAPPINGERROR E where E.ID = @ID;
    select @ERRORCODE = M.ERRORCODE
        ,@ERRORMESSAGE = M.ERRORMESSAGE
        ,@OFFICEID = O.OFFICEID
        ,@REVENUETYPEID = M.REVENUETYPEID
        ,@APPLICATIONTYPEID = M.APPLICATIONTYPEID
        ,@PAYMENTMETHODID = M.PAYMENTMETHODID
        ,@PDACCOUNTSYSTEMID = M.PDACCOUNTSYSTEMID
    from dbo.UFN_GLACCOUNTMAPPINGERROR_MAPPEDVALUES_FROMITEMLISTXML(@MAPPEDVALUES) M
    inner join dbo.PDACCOUNTCODEMAPOFFICE O on M.PDACCOUNTCODEMAPOFFICEID = O.ID

    --Figure out what segment has the error (if it has not already been fixed)

    if @ERRORCODE = 2
    begin
        declare @SEGMENTVALUES table (TABLEID uniqueidentifier, VALUEID uniqueidentifier);
        insert into @SEGMENTVALUES (TABLEID, VALUEID)
        select distinct TABLEID, VALUEID
        from 
            (select SEGMENT02TABLEID, SEGMENT03TABLEID, SEGMENT04TABLEID, SEGMENT05TABLEID, SEGMENT06TABLEID, SEGMENT07TABLEID
                , SEGMENT08TABLEID, SEGMENT09TABLEID, SEGMENT10TABLEID, SEGMENT11TABLEID, SEGMENT12TABLEID, SEGMENT13TABLEID
                , SEGMENT14TABLEID, SEGMENT15TABLEID, SEGMENT16TABLEID, SEGMENT17TABLEID, SEGMENT18TABLEID, SEGMENT19TABLEID
                , SEGMENT20TABLEID, SEGMENT21TABLEID, SEGMENT22TABLEID, SEGMENT23TABLEID, SEGMENT24TABLEID, SEGMENT25TABLEID
                , SEGMENT26TABLEID, SEGMENT27TABLEID, SEGMENT28TABLEID, SEGMENT29TABLEID, SEGMENT30TABLEID,
                SEGMENT02ID, SEGMENT03ID, SEGMENT04ID, SEGMENT05ID, SEGMENT06ID, SEGMENT07ID
                , SEGMENT08ID, SEGMENT09ID, SEGMENT10ID, SEGMENT11ID, SEGMENT12ID, SEGMENT13ID
                , SEGMENT14ID, SEGMENT15ID, SEGMENT16ID, SEGMENT17ID, SEGMENT18ID, SEGMENT19ID
                , SEGMENT20ID, SEGMENT21ID, SEGMENT22ID, SEGMENT23ID, SEGMENT24ID, SEGMENT25ID
                , SEGMENT26ID, SEGMENT27ID, SEGMENT28ID, SEGMENT29ID, SEGMENT30ID
            from dbo.UFN_GLACCOUNTMAPPINGERROR_MAPPEDVALUES_FROMITEMLISTXML(@MAPPEDVALUES)) P
            unpivot
                (TABLEID for segment in (SEGMENT02TABLEID, SEGMENT03TABLEID, SEGMENT04TABLEID, SEGMENT05TABLEID, SEGMENT06TABLEID, SEGMENT07TABLEID
                    , SEGMENT08TABLEID, SEGMENT09TABLEID, SEGMENT10TABLEID, SEGMENT11TABLEID, SEGMENT12TABLEID, SEGMENT13TABLEID
                    , SEGMENT14TABLEID, SEGMENT15TABLEID, SEGMENT16TABLEID, SEGMENT17TABLEID, SEGMENT18TABLEID, SEGMENT19TABLEID
                    , SEGMENT20TABLEID, SEGMENT21TABLEID, SEGMENT22TABLEID, SEGMENT23TABLEID, SEGMENT24TABLEID, SEGMENT25TABLEID
                    , SEGMENT26TABLEID, SEGMENT27TABLEID, SEGMENT28TABLEID, SEGMENT29TABLEID, SEGMENT30TABLEID))  unpvt
            unpivot
                (VALUEID for value in (SEGMENT02ID, SEGMENT03ID, SEGMENT04ID, SEGMENT05ID, SEGMENT06ID, SEGMENT07ID
                    , SEGMENT08ID, SEGMENT09ID, SEGMENT10ID, SEGMENT11ID, SEGMENT12ID, SEGMENT13ID
                    , SEGMENT14ID, SEGMENT15ID, SEGMENT16ID, SEGMENT17ID, SEGMENT18ID, SEGMENT19ID
                    , SEGMENT20ID, SEGMENT21ID, SEGMENT22ID, SEGMENT23ID, SEGMENT24ID, SEGMENT25ID
                    , SEGMENT26ID, SEGMENT27ID, SEGMENT28ID, SEGMENT29ID, SEGMENT30ID)) unpvt1
            where LEFT(segment, 9) = LEFT(value, 9)

        select top 1 @MISSINGSEGMENTMAPPING = convert(nvarchar(36), S.ID) + convert(nvarchar(36),ISNULL(M.LONGDESCRIPTIONID, MAPPINGS.VALUEID))
            ,@ISCOMPOSITE = 0
        from dbo.PDACCOUNTSTRUCTURE S
        inner join PDACCOUNTTABLESAVAILABLEFORSEGMENT T on T.ID = S.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
        left join @SEGMENTVALUES MAPPINGS on T.TABLEID = MAPPINGS.TABLEID
        left join dbo.PDACCOUNTSEGMENTMAPPING M on M.PDACCOUNTSTRUCTUREID = S.ID and
            (M.LONGDESCRIPTIONID = mappings.VALUEID  or (M.ISDEFAULT = 1 and (MAPPINGS.VALUEID = '99999999-9999-9999-9999-999999999999' or MAPPINGS.VALUEID is null)))
        left join dbo.PDCOMPOSITESEGMENT C on C.ID = S.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
        where (M.ID is null or M.PDACCOUNTSEGMENTVALUEID is null) 
            and S.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID 
            and C.ID is null
            and ISNULL(M.LONGDESCRIPTIONID, MAPPINGS.VALUEID) is not null;

        if @MISSINGSEGMENTMAPPING is null
        begin
            select top 1 @MISSINGSEGMENTMAPPING = SEGMENTVALUES.ID
                ,@COMPOSITESEGMENT1ID = SEGMENTVALUES.COMPOSITESEGMENT1ID
                ,@COMPOSITESEGMENT2ID = SEGMENTVALUES.COMPOSITESEGMENT2ID
                ,@COMPOSITESEGMENT3ID = SEGMENTVALUES.COMPOSITESEGMENT3ID
                ,@COMPOSITESEGMENT4ID = SEGMENTVALUES.COMPOSITESEGMENT4ID
                ,@ISCOMPOSITE = 1
            from (
                select COMP.ID, S1.VALUEID as COMPOSITESEGMENT1ID, S2.VALUEID as COMPOSITESEGMENT2ID, S3.VALUEID as COMPOSITESEGMENT3ID, S4.VALUEID as COMPOSITESEGMENT4ID
                from (
                    select pvt.ID, pvt.[1], pvt.[2], pvt.[3], pvt.[4]
                    from (
                        select S.ID, convert(nvarchar(36), T.TABLEID) as TABLEID, L.SEQUENCE
                        from dbo.PDACCOUNTSTRUCTURE S
                        inner join dbo.PDCOMPOSITESEGMENT C on C.ID = S.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                        inner join dbo.PDCOMPOSITESEGMENTTABLELIST L on L.PDCOMPOSITESEGMENTID = C.ID
                        inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT T on T.ID = L.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
                        where S.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) as P
                    pivot
                        (MAX(TABLEID) FOR SEQUENCE IN ([1], [2], [3], [4])) pvt) COMP
                    left join @SEGMENTVALUES S1 on S1.TABLEID = COMP.[1]
                    left join @SEGMENTVALUES S2 on S2.TABLEID = COMP.[2]
                    left join @SEGMENTVALUES S3 on S3.TABLEID = COMP.[3]
                    left join @SEGMENTVALUES S4 on S4.TABLEID = COMP.[4]) as SEGMENTVALUES
                left join (
                    select pvt.ID, pvt.[1] as COMPOSITESEGMENT1ID, pvt.[2] as COMPOSITESEGMENT2ID, pvt.[3] as COMPOSITESEGMENT3ID, pvt.[4] as COMPOSITESEGMENT4ID
                    from (
                        select S.ID, convert(nvarchar(36), E.LONGDESCRIPTIONID) as LONGDESCRIPTIONID, L.SEQUENCE
                        from dbo.PDCOMPOSITESEGMENTMAPPING M
                        inner join dbo.PDACCOUNTSTRUCTURE S on M.PDACCOUNTSTRUCTUREID = S.ID
                        inner join dbo.PDCOMPOSITESEGMENTMAPPINGENTRY E on E.PDCOMPOSITESEGMENTMAPPINGID = M.ID
                        inner join dbo.PDCOMPOSITESEGMENTTABLELIST L on E.PDCOMPOSITESEGMENTTABLELISTID = L.ID
                        where S.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) as P
                    pivot
                        (MAX(LONGDESCRIPTIONID) FOR SEQUENCE IN ([1], [2], [3], [4])) pvt) MAPPEDVALUES on SEGMENTVALUES.ID = MAPPEDVALUES.ID 
                            and ISNULL(SEGMENTVALUES.COMPOSITESEGMENT1ID, @PDACCOUNTSYSTEMID) = ISNULL(MAPPEDVALUES.COMPOSITESEGMENT1ID, @PDACCOUNTSYSTEMID)
                            and ISNULL(SEGMENTVALUES.COMPOSITESEGMENT2ID, @PDACCOUNTSYSTEMID) = ISNULL(MAPPEDVALUES.COMPOSITESEGMENT2ID, @PDACCOUNTSYSTEMID)
                            and ISNULL(SEGMENTVALUES.COMPOSITESEGMENT3ID, @PDACCOUNTSYSTEMID) = ISNULL(MAPPEDVALUES.COMPOSITESEGMENT3ID, @PDACCOUNTSYSTEMID)
                            and ISNULL(SEGMENTVALUES.COMPOSITESEGMENT4ID, @PDACCOUNTSYSTEMID) = ISNULL(MAPPEDVALUES.COMPOSITESEGMENT4ID, @PDACCOUNTSYSTEMID)
                where MAPPEDVALUES.ID is null
        end
    end
    else if @ERRORCODE = 3
        select @MISSINGACCOUNT = VALUE from dbo.UFN_STRINGSPLITTER(@ERRORMESSAGE, '"') where ID = 4;

    return 0;