/**
*    
*    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
    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 [SystemSpecificFields/Label01],
        case
            when isnull( SysFile.UserDefinedActive1, 0 ) = 1 then Orders.SSUserDefined1
            else null
        end as [SystemSpecificFields/Field01],

        case
            when isnull( SysFile.UserDefinedActive2, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel2, SysFile.UserDefinedLabel2 )
            else null
        end as [SystemSpecificFields/Label02],
        case
            when isnull( SysFile.UserDefinedActive2, 0 ) = 1 then Orders.SSUserDefined2
            else null
        end as [SystemSpecificFields/Field02],

        case
            when isnull( SysFile.UserDefinedActive3, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel3, SysFile.UserDefinedLabel3 )
            else null
        end as [SystemSpecificFields/Label03],
        case
            when isnull( SysFile.UserDefinedActive3, 0 ) = 1 then Orders.SSUserDefined3
            else null
        end as [SystemSpecificFields/Field03],

        case
            when isnull( SysFile.UserDefinedActive4, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel4, SysFile.UserDefinedLabel4 )
            else null
        end as [SystemSpecificFields/Label04],
        case
            when isnull( SysFile.UserDefinedActive4, 0 ) = 1 then Orders.SSUserDefined4
            else null
        end as [SystemSpecificFields/Field04],

        case
            when isnull( SysFile.UserDefinedActive5, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel5, SysFile.UserDefinedLabel5 )
            else null
        end as [SystemSpecificFields/Label05],
        case
            when isnull( SysFile.UserDefinedActive5, 0 ) = 1 then Orders.SSUserDefined5
            else null
        end as [SystemSpecificFields/Field05],

        case
            when isnull( SysFile.UserDefinedActive6, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel6, SysFile.UserDefinedLabel6 )
            else null
        end as [SystemSpecificFields/Label06],
        case
            when isnull( SysFile.UserDefinedActive6, 0 ) = 1 then Orders.SSUserDefined6
            else null
        end as [SystemSpecificFields/Field06],

        case
            when isnull( SysFile.UserDefinedActive7, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel7, SysFile.UserDefinedLabel7 )
            else null
        end as [SystemSpecificFields/Label07],
        case
            when isnull( SysFile.UserDefinedActive7, 0 ) = 1 then Orders.SSUserDefined7
            else null
        end as [SystemSpecificFields/Field07],

        case
            when isnull( SysFile.UserDefinedActive8, 0 ) = 1 then isnull( SUDFMoveTypeLabels.UserDefinedLabel8, SysFile.UserDefinedLabel8 )
            else null
        end as [SystemSpecificFields/Label08],
        case
            when isnull( SysFile.UserDefinedActive8, 0 ) = 1 then Orders.SSUserDefined8
            else null
        end as [SystemSpecificFields/Field08],

        -- 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 [NationalAccountSpecificFields/Label01],
        case
            when isnull( Accounts.UserDefinedActive1, 0 ) = 1 then Orders.CSUserDefined1
            else null
        end as [NationalAccountSpecificFields/Field01],

        case
            when isnull( Accounts.UserDefinedActive2, 0 ) = 1 then Accounts.UserDefinedLabel2
            else null
        end as [NationalAccountSpecificFields/Label02],
        case
            when isnull( Accounts.UserDefinedActive2, 0 ) = 1 then Orders.CSUserDefined2
            else null
        end as [NationalAccountSpecificFields/Field02],

        case
            when isnull( Accounts.UserDefinedActive3, 0 ) = 1 then Accounts.UserDefinedLabel3
            else null
        end as [NationalAccountSpecificFields/Label03],
        case
            when isnull( Accounts.UserDefinedActive3, 0 ) = 1 then Orders.CSUserDefined3
            else null
        end as [NationalAccountSpecificFields/Field03],

        case
            when isnull( Accounts.UserDefinedActive4, 0 ) = 1 then Accounts.UserDefinedLabel4
            else null
        end as [NationalAccountSpecificFields/Label04],
        case
            when isnull( Accounts.UserDefinedActive4, 0 ) = 1 then Orders.CSUserDefined4
            else null
        end as [NationalAccountSpecificFields/Field04],

        case
            when isnull( Accounts.UserDefinedActive5, 0 ) = 1 then Accounts.UserDefinedLabel5
            else null
        end as [NationalAccountSpecificFields/Label05],
        case
            when isnull( Accounts.UserDefinedActive5, 0 ) = 1 then Orders.CSUserDefined5
            else null
        end as [NationalAccountSpecificFields/Field05],

        case
            when isnull( Accounts.UserDefinedActive6, 0 ) = 1 then Accounts.UserDefinedLabel6
            else null
        end as [NationalAccountSpecificFields/Label06],
        case
            when isnull( Accounts.UserDefinedActive6, 0 ) = 1 then Orders.CSUserDefined6
            else null
        end as [NationalAccountSpecificFields/Field06],

        case
            when isnull( Accounts.UserDefinedActive7, 0 ) = 1 then Accounts.UserDefinedLabel7
            else null
        end as [NationalAccountSpecificFields/Label07],
        case
            when isnull( Accounts.UserDefinedActive7, 0 ) = 1 then Orders.CSUserDefined7
            else null
        end as [NationalAccountSpecificFields/Field07],

        case
            when isnull( Accounts.UserDefinedActive8, 0 ) = 1 then Accounts.UserDefinedLabel8
            else null
        end as [NationalAccountSpecificFields/Label08],
        case
            when isnull( Accounts.UserDefinedActive8, 0 ) = 1 then Orders.CSUserDefined8
            else null
        end as [NationalAccountSpecificFields/Field08]

    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
    for xml path ('UserDefinedFields'), type

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