/**
*    
*    This procedure is called to generate the xml segment for user defined field
*    information for the given order.
*    
*    Possible Error Codes:
*    @ERROR_CODE_INVALID_ORDER
*    
*    @param @inOrderID The primary key of a MoversSuite Orders record.
*    @param @inXmlInterfaceID The primary key of a XmlInterface record.
*    @param @outStatusCode Returns whether or not an error occurred while executing this stored procedure.
*    @param @outErrorCode Returns any errors encountered while executing this stored procedure.
*/
CREATE procedure [dbo].[XmlExportUserDefinedFields]
    @inOrderID int,
    @inXmlInterfaceID int,
    @outStatusCode int output,
    @outErrorCode int output
as
set nocount on;

-- Initialize the output parameters.
set @outStatusCode = 0;
set @outErrorCode = 0;

-- Define the error codes.
declare @ERROR_CODE_INVALID_ORDER int;
set @ERROR_CODE_INVALID_ORDER = 2048;

-- Define the status codes.
declare @STATUS_CODE_PROCESSED int;
set @STATUS_CODE_PROCESSED = 1;
declare @STATUS_CODE_ERROR int;
set @STATUS_CODE_ERROR = 0;

-- Check the order.
declare @theValidOrderFlag bit;
exec @theValidOrderFlag = dbo.ValidateOrderID @inOrderID;

-- If the order is not valid, return error code @ERROR_CODE_INVALID_ORDER.
if( 0 = @theValidOrderFlag )
begin
    set @outErrorCode = @ERROR_CODE_INVALID_ORDER;
    set @outStatusCode = @STATUS_CODE_ERROR;
end
-- Otherwise return the xml segment and set the
-- status code to @STATUS_CODE_PROCESSED.
else
begin

    ;with src as (
        select
            -- Get the user defined field values and their corresponding labels, which can be
            -- customized by the MoveType.
            case
                when isnull( SysFile.UserDefinedActive1, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel1, SysFile.UserDefinedLabel1 )
                else null
            end as SysLabel01,
            case
                when isnull( SysFile.UserDefinedActive1, 0 ) = 1 then Orders.SSUserDefined1
                else null
            end as SysField01,

            case
                when isnull( SysFile.UserDefinedActive2, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel2, SysFile.UserDefinedLabel2 )
                else null
            end as SysLabel02,
            case
                when isnull( SysFile.UserDefinedActive2, 0 ) = 1 then Orders.SSUserDefined2
                else null
            end as SysField02,

            case
                when isnull( SysFile.UserDefinedActive3, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel3, SysFile.UserDefinedLabel3 )
                else null
            end as SysLabel03,
            case
                when isnull( SysFile.UserDefinedActive3, 0 ) = 1 then Orders.SSUserDefined3
                else null
            end as SysField03,

            case
                when isnull( SysFile.UserDefinedActive4, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel4, SysFile.UserDefinedLabel4 )
                else null
            end as SysLabel04,
            case
                when isnull( SysFile.UserDefinedActive4, 0 ) = 1 then Orders.SSUserDefined4
                else null
            end as SysField04,

            case
                when isnull( SysFile.UserDefinedActive5, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel5, SysFile.UserDefinedLabel5 )
                else null
            end as SysLabel05,
            case
                when isnull( SysFile.UserDefinedActive5, 0 ) = 1 then Orders.SSUserDefined5
                else null
            end as SysField05,

            case
                when isnull( SysFile.UserDefinedActive6, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel6, SysFile.UserDefinedLabel6 )
                else null
            end as SysLabel06,
            case
                when isnull( SysFile.UserDefinedActive6, 0 ) = 1 then Orders.SSUserDefined6
                else null
            end as SysField06,

            case
                when isnull( SysFile.UserDefinedActive7, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel7, SysFile.UserDefinedLabel7 )
                else null
            end as SysLabel07,
            case
                when isnull( SysFile.UserDefinedActive7, 0 ) = 1 then Orders.SSUserDefined7
                else null
            end as SysField07,

            case
                when isnull( SysFile.UserDefinedActive8, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel8, SysFile.UserDefinedLabel8 )
                else null
            end as SysLabel08,
            case
                when isnull( SysFile.UserDefinedActive8, 0 ) = 1 then Orders.SSUserDefined8
                else null
            end as SysField08,

            -- Get the National Account user defined field values and their corresponding labels.
            case
                when isnull( Accounts.UserDefinedActive1, 0 ) = 1 then Accounts.UserDefinedLabel1
                else null
            end as NatLabel01,
            case
                when isnull( Accounts.UserDefinedActive1, 0 ) = 1 then Orders.CSUserDefined1
                else null
            end as NatField01,

            case
                when isnull( Accounts.UserDefinedActive2, 0 ) = 1 then Accounts.UserDefinedLabel2
                else null
            end as NatLabel02,
            case
                when isnull( Accounts.UserDefinedActive2, 0 ) = 1 then Orders.CSUserDefined2
                else null
            end as NatField02,

            case
                when isnull( Accounts.UserDefinedActive3, 0 ) = 1 then Accounts.UserDefinedLabel3
                else null
            end as NatLabel03,
            case
                when isnull( Accounts.UserDefinedActive3, 0 ) = 1 then Orders.CSUserDefined3
                else null
            end as NatField03,

            case
                when isnull( Accounts.UserDefinedActive4, 0 ) = 1 then Accounts.UserDefinedLabel4
                else null
            end as NatLabel04,
            case
                when isnull( Accounts.UserDefinedActive4, 0 ) = 1 then Orders.CSUserDefined4
                else null
            end as NatField04,

            case
                when isnull( Accounts.UserDefinedActive5, 0 ) = 1 then Accounts.UserDefinedLabel5
                else null
            end as NatLabel05,
            case
                when isnull( Accounts.UserDefinedActive5, 0 ) = 1 then Orders.CSUserDefined5
                else null
            end as NatField05,

            case
                when isnull( Accounts.UserDefinedActive6, 0 ) = 1 then Accounts.UserDefinedLabel6
                else null
            end as NatLabel06,
            case
                when isnull( Accounts.UserDefinedActive6, 0 ) = 1 then Orders.CSUserDefined6
                else null
            end as NatField06,

            case
                when isnull( Accounts.UserDefinedActive7, 0 ) = 1 then Accounts.UserDefinedLabel7
                else null
            end as NatLabel07,
            case
                when isnull( Accounts.UserDefinedActive7, 0 ) = 1 then Orders.CSUserDefined7
                else null
            end as NatField07,

            case
                when isnull( Accounts.UserDefinedActive8, 0 ) = 1 then Accounts.UserDefinedLabel8
                else null
            end as NatLabel08,
            case
                when isnull( Accounts.UserDefinedActive8, 0 ) = 1 then Orders.CSUserDefined8
                else null
            end as NatField08
        from Orders
        cross join SysFile
        left outer join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = Orders.MoveType
        left outer join Accounts on Accounts.AccountPriKey = Orders.AcctPriKey
        where Orders.PriKey = @inOrderId
    )
    -- EXPLICIT universal table
    select
        1 as Tag,        -- root element
        null as Parent,
        null as [UserDefinedFields!1],  -- container tag
        null as [SystemSpecificFields!2!Label01!element],
        null as [SystemSpecificFields!2!Field01!element],
        null as [SystemSpecificFields!2!Label02!element],
        null as [SystemSpecificFields!2!Field02!element],
        null as [SystemSpecificFields!2!Label03!element],
        null as [SystemSpecificFields!2!Field03!element],
        null as [SystemSpecificFields!2!Label04!element],
        null as [SystemSpecificFields!2!Field04!element],
        null as [SystemSpecificFields!2!Label05!element],
        null as [SystemSpecificFields!2!Field05!element],
        null as [SystemSpecificFields!2!Label06!element],
        null as [SystemSpecificFields!2!Field06!element],
        null as [SystemSpecificFields!2!Label07!element],
        null as [SystemSpecificFields!2!Field07!element],
        null as [SystemSpecificFields!2!Label08!element],
        null as [SystemSpecificFields!2!Field08!element],
        null as [NationalAccountSpecificFields!3!Label01!element],
        null as [NationalAccountSpecificFields!3!Field01!element],
        null as [NationalAccountSpecificFields!3!Label02!element],
        null as [NationalAccountSpecificFields!3!Field02!element],
        null as [NationalAccountSpecificFields!3!Label03!element],
        null as [NationalAccountSpecificFields!3!Field03!element],
        null as [NationalAccountSpecificFields!3!Label04!element],
        null as [NationalAccountSpecificFields!3!Field04!element],
        null as [NationalAccountSpecificFields!3!Label05!element],
        null as [NationalAccountSpecificFields!3!Field05!element],
        null as [NationalAccountSpecificFields!3!Label06!element],
        null as [NationalAccountSpecificFields!3!Field06!element],
        null as [NationalAccountSpecificFields!3!Label07!element],
        null as [NationalAccountSpecificFields!3!Field07!element],
        null as [NationalAccountSpecificFields!3!Label08!element],
        null as [NationalAccountSpecificFields!3!Field08!element]

    union all
    select
        2 as Tag,
        1 as Parent,
        null,
        src.SysLabel01,
        src.SysField01,
        src.SysLabel02,
        src.SysField02,
        src.SysLabel03,
        src.SysField03,
        src.SysLabel04,
        src.SysField04,
        src.SysLabel05,
        src.SysField05,
        src.SysLabel06,
        src.SysField06,
        src.SysLabel07,
        src.SysField07,
        src.SysLabel08,
        src.SysField08,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null
    from src

    union all
    select
        3 as Tag,
        1 as Parent,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        src.NatLabel01,
        src.NatField01,
        src.NatLabel02,
        src.NatField02,
        src.NatLabel03,
        src.NatField03,
        src.NatLabel04,
        src.NatField04,
        src.NatLabel05,
        src.NatField05,
        src.NatLabel06,
        src.NatField06,
        src.NatLabel07,
        src.NatField07,
        src.NatLabel08,
        src.NatField08
    from src
    order by Tag
    for xml explicit;

    set @outStatusCode = @STATUS_CODE_PROCESSED;
end
GO
GRANT EXECUTE ON  [dbo].[XmlExportUserDefinedFields] TO [MssExec]
GO