/*
*    Description: Fetches the appropriate XglFid value to use for an order for the specified @inXLedgerExpenseType name.
*
*    Input @params:
*    ===========================================================================================================================
*    @inOrderID: The primary key for the order of interest.  If null, the none-none-none-none Xgl will be used.
*    @inSessionID: A BAT or Storage session identifier used for any error logging.
*    @inXLedgerExpenseType: One of the XLedgerExpenseType TypeNames.  Expense types cannot be hidden nor marked as cannot post.
*        If not specified or if i@nOrderID is null, then 'None' is assumed.
*    @inIsStorageProcessing: Used by spRecordBatOrStorageError to log errors via spBATRecordError (0) or spStorageRecordError (1).
*    @inSourceTable: Used as is.
*    @inSourceID: Used as is.
*    @inObjectTable: Used as is.
*    @inObjectID: Used as is.
*    
*    Returned parameters:
*    ===========================================================================================================================
*    @outXGlId: The primary key of the appropriate Xgl is returned through the parameter.
*    @outHadError: The success (0) or failure(1) of this stored proc is returned through this parameter.  If set to 1, then
*        one or more BATErrorLog rows will have been added to that table.  If this is set to 1, then @outXGlId will be null.
*/
CREATE PROCEDURE [dbo].[spBATGetSingleXLedgerXglId]
    @inOrderID int,
    @inSessionID varchar(50),
    @inXLedgerExpenseType varchar(16),
    @inIsStorageProcessing bit,
    @inSourceTable varchar(50),
    @inSourceID int,
    @inObjectTable varchar(20),
    @inObjectID int,
    @inAllowNullsAuthorityMoveTypeCommodity bit = 0,
    @outXGlId int out,
    @outHadError bit out
as

set nocount on

-- initialize output parameters
select
    @outXGlId = null,
    @outHadError = 0,
    @inXLedgerExpenseType = isnull( @inXLedgerExpenseType, 'None' )

declare @theAuthorityId int
declare @theMoveTypeGroupId int
declare @theCommodityId int

declare @ORDER_IS_MISSING_VALID_AUTHORITY_TYPE int = 1503
declare @ORDER_IS_MISSING_VALID_MOVE_TYPE_GROUP int = 1504
declare @ORDER_IS_MISSING_VALID_COMMODITY_TYPE int = 1505
declare @UNABLE_TO_LOCATE_XGL_FOR_ORDER int = 1506

declare @XGL_ITSELF_MARKED_AS_CANNOT_POST int = 1507
declare @XGL_ITSELF_MARKED_AS_HIDDEN int = 1508
declare @AUTHORITY_TYPE_MARKED_AS_CANNOT_POST int = 1509
declare @AUTHORITY_TYPE_MARKED_AS_HIDDEN int = 1510
declare @MOVE_TYPE_MARKED_AS_CANNOT_POST int = 1511
declare @MOVE_TYPE_MARKED_AS_HIDDEN int = 1512
declare @COMMODITY_TYPE_MARKED_AS_CANNOT_POST int = 1513
declare @COMMODITY_TYPE_MARKED_AS_HIDDEN int = 1514

declare @theXLedgerXglItselfCanPost bit
declare @theXLedgerXglItselfHidden bit
declare @theXLedgerAuthorityTypeObjectValueCanPost bit
declare @theXLedgerAuthorityTypeObjectValueHidden bit
declare @theXLedgerMoveTypeObjectValueCanPost bit
declare @theXLedgerMoveTypeObjectValueHidden bit
declare @theXLedgerCommodityTypeObjectValueCanPost bit
declare @theXLedgerCommodityTypeObjectValueHidden bit

declare @theNoneXLedgerAuthorityTypeObjectValueId int
declare @theNoneXLedgerMoveTypeObjectValueId int
declare @theNoneXLedgerCommodityTypeObjectValueId int

declare @theAuthorityTypeName varchar(30)
declare @theMoveTypeGroupName varchar(20)
declare @theCommodityTypeName varchar(26)

if( @inOrderID is null )
begin
    -- Some transactions (GL,AP, PM) may not have an order so use the none-none-none-none Xgl.
    -- Note that @inXLedgerExpenseType does not apply in this case.
    select
        @theAuthorityTypeName = 'None',
        @theMoveTypeGroupName = 'None',
        @theCommodityTypeName = 'None',
        @inXLedgerExpenseType = 'None'

    select
        @outXGlId = Xgl.XglId,
        @theXLedgerXglItselfCanPost = Xgl.CanPost,
        @theXLedgerXglItselfHidden = Xgl.[Hidden],
        @theXLedgerAuthorityTypeObjectValueCanPost = XLedgerAuthorityTypeObjectValue.CanPost,
        @theXLedgerAuthorityTypeObjectValueHidden = XLedgerAuthorityTypeObjectValue.[Hidden],
        @theXLedgerMoveTypeObjectValueCanPost = XLedgerMoveTypeObjectValue.CanPost,
        @theXLedgerMoveTypeObjectValueHidden = XLedgerMoveTypeObjectValue.[Hidden],
        @theXLedgerCommodityTypeObjectValueCanPost = XLedgerCommodityTypeObjectValue.CanPost,
        @theXLedgerCommodityTypeObjectValueHidden = XLedgerCommodityTypeObjectValue.[Hidden]
    from Xgl
    inner join XLedgerAuthorityTypeObjectValue on XLedgerAuthorityTypeObjectValue.[Description] = @theAuthorityTypeName
    inner join XLedgerMoveTypeObjectValue on XLedgerMoveTypeObjectValue.[Description] = @theMoveTypeGroupName
    inner join XLedgerCommodityTypeObjectValue on XLedgerCommodityTypeObjectValue.[Description] = @theCommodityTypeName
    inner join XLedgerExpenseType on XLedgerExpenseType.TypeName = @inXLedgerExpenseType
    where Xgl.XLedgerAuthorityTypeObjectValueFid = XLedgerAuthorityTypeObjectValue.XLedgerAuthorityTypeObjectValueId and
        Xgl.XLedgerMoveTypeObjectValueFid= XLedgerMoveTypeObjectValue.XLedgerMoveTypeObjectValueId and
        Xgl.XLedgerCommodityTypeObjectValueFid = XLedgerCommodityTypeObjectValue.XLedgerCommodityTypeObjectValueId and
        Xgl.XLedgerExpenseTypeFid = XLedgerExpenseType.XLedgerExpenseTypeId
end
else --if( @inOrderID is not null )
begin
    -- Get the raw Xgl data from the order.  All order revenue transactions use the authority,
    -- move type group and commodity type from the order.  Expense types vary according to the
    -- transaction type.
    select
        @theAuthorityId = isnull( Orders.AuthPriKey, -1 ),
        @theMoveTypeGroupId = isnull( MoveTypeGroups.MTGroupPriKey, -1 ),
        @theCommodityId = isnull( Orders.Commodity, -1 ),
        @theAuthorityTypeName = ltrim( rtrim( AuthorityTypes.[Description] ) ),
        @theMoveTypeGroupName = ltrim( rtrim( MoveTypeGroups.[Description] ) ),
        @theCommodityTypeName = ltrim( rtrim( CommType.Commodity ) )
    from Orders
    left outer join MoveType on MoveType.PriKey = Orders.MoveType
    left outer join MoveTypeGroups on MoveTypeGroups.MTGroupPriKey = MoveType.MTGroupPriKey
    left outer join CommType on CommType.PriKey = Orders.Commodity
    left outer join AuthorityTypes on AuthorityTypes.AuthPriKey = Orders.AuthPriKey
    where Orders.PriKey = @inOrderID

    if( @inAllowNullsAuthorityMoveTypeCommodity = 0 )
    begin
        if( @theAuthorityId = -1 )
        begin
            exec spRecordBatOrStorageError
                @inIsStorageProcessing = @inIsStorageProcessing,
                @inErrorCodeID = @ORDER_IS_MISSING_VALID_AUTHORITY_TYPE,
                @inOrderID = @inOrderID,
                @inSessionID = @inSessionID,
                @inSourceTable = @inSourceTable,
                @inSourceID = @inSourceID,
                @inObjectTable = @inObjectTable,
                @inObjectID = @inObjectID

            set @outHadError = 1
        end

        if( @theMoveTypeGroupId = -1 )
        begin
            exec spRecordBatOrStorageError
                @inIsStorageProcessing = @inIsStorageProcessing,
                @inErrorCodeID = @ORDER_IS_MISSING_VALID_MOVE_TYPE_GROUP,
                @inOrderID = @inOrderID,
                @inSessionID = @inSessionID,
                @inSourceTable = @inSourceTable,
                @inSourceID = @inSourceID,
                @inObjectTable = @inObjectTable,
                @inObjectID = @inObjectID

            set @outHadError = 1
        end

        if( @theCommodityId = -1 )
        begin
            exec spRecordBatOrStorageError
                @inIsStorageProcessing = @inIsStorageProcessing,
                @inErrorCodeID = @ORDER_IS_MISSING_VALID_COMMODITY_TYPE,
                @inOrderID = @inOrderID,
                @inSessionID = @inSessionID,
                @inSourceTable = @inSourceTable,
                @inSourceID = @inSourceID,
                @inObjectTable = @inObjectTable,
                @inObjectID = @inObjectID

            set @outHadError = 1
        end
    end
    else -- If we get here, @inAllowNullsAuthorityMoveTypeCommodity = 1.
    begin
        -- Get the IDs of the 'None' records from the XLedger Authority, MoveType and Commodity tables.
        select @theNoneXLedgerAuthorityTypeObjectValueId = XLedgerAuthorityTypeObjectValueId
        from XLedgerAuthorityTypeObjectValue
        where [Description] = 'None'
        select @theNoneXLedgerMoveTypeObjectValueId = XLedgerMoveTypeObjectValueId
        from XLedgerMoveTypeObjectValue
        where [Description] = 'None'
        select @theNoneXLedgerCommodityTypeObjectValueId = XLedgerCommodityTypeObjectValueId
        from XLedgerCommodityTypeObjectValue
        where [Description] = 'None'
    end

    if( @outHadError = 0 )
    begin
        select
            @outXGlId = Xgl.XglId,
            @theXLedgerXglItselfCanPost = Xgl.CanPost,
            @theXLedgerXglItselfHidden = Xgl.[Hidden],
            @theXLedgerAuthorityTypeObjectValueCanPost = XLedgerAuthorityTypeObjectValue.CanPost,
            @theXLedgerAuthorityTypeObjectValueHidden = XLedgerAuthorityTypeObjectValue.[Hidden],
            @theXLedgerMoveTypeObjectValueCanPost = XLedgerMoveTypeObjectValue.CanPost,
            @theXLedgerMoveTypeObjectValueHidden = XLedgerMoveTypeObjectValue.[Hidden],
            @theXLedgerCommodityTypeObjectValueCanPost = XLedgerCommodityTypeObjectValue.CanPost,
            @theXLedgerCommodityTypeObjectValueHidden = XLedgerCommodityTypeObjectValue.[Hidden]
        from Xgl
        inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeId = Xgl.XLedgerExpenseTypeFid
        left outer join XLedgerAuthorityTypeObjectValue on XLedgerAuthorityTypeObjectValue.XLedgerAuthorityTypeObjectValueId = Xgl.XLedgerAuthorityTypeObjectValueFid
        left outer join XLedgerMoveTypeObjectValue on XLedgerMoveTypeObjectValue.XLedgerMoveTypeObjectValueId = Xgl.XLedgerMoveTypeObjectValueFid
        left outer join XLedgerCommodityTypeObjectValue on XLedgerCommodityTypeObjectValue.XLedgerCommodityTypeObjectValueId = Xgl.XLedgerCommodityTypeObjectValueFid
        where
            XLedgerExpenseType.TypeName = @inXLedgerExpenseType and
            (
                XLedgerAuthorityTypeObjectValue.AuthorityTypeFid = @theAuthorityId or
                (
                    @inAllowNullsAuthorityMoveTypeCommodity = 1 and
                    @theAuthorityId = -1 and
                    XLedgerAuthorityTypeObjectValue.XLedgerAuthorityTypeObjectValueId = @theNoneXLedgerAuthorityTypeObjectValueId
                )
            ) and
            (
                XLedgerMoveTypeObjectValue.MoveTypeGroupFID = @theMoveTypeGroupId or
                (
                    @inAllowNullsAuthorityMoveTypeCommodity = 1 and
                    @theMoveTypeGroupId = -1 and
                    XLedgerMoveTypeObjectValue.XLedgerMoveTypeObjectValueId = @theNoneXLedgerMoveTypeObjectValueId
                )
            ) and
            (
                XLedgerCommodityTypeObjectValue.CommTypeFid = @theCommodityId or
                (
                    @inAllowNullsAuthorityMoveTypeCommodity = 1 and
                    @theCommodityId = -1 and
                    XLedgerCommodityTypeObjectValue.XLedgerCommodityTypeObjectValueId = @theNoneXLedgerCommodityTypeObjectValueId
                )
            )
    end
end

if( @outHadError = 0 )
begin
    if( @outXGlId is null )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @UNABLE_TO_LOCATE_XGL_FOR_ORDER,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theAuthorityTypeName,
            @inParam2 = @theMoveTypeGroupName,
            @inParam3 = @theCommodityTypeName,
            @inParam4 = @inXLedgerExpenseType

        set @outHadError = 1
    end

    if( @theXLedgerXglItselfCanPost = 0 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @XGL_ITSELF_MARKED_AS_CANNOT_POST,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theAuthorityTypeName,
            @inParam2 = @theMoveTypeGroupName,
            @inParam3 = @theCommodityTypeName,
            @inParam4 = @inXLedgerExpenseType

        set @outHadError = 1
    end

    if( @theXLedgerXglItselfHidden = 1 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @XGL_ITSELF_MARKED_AS_HIDDEN,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theAuthorityTypeName,
            @inParam2 = @theMoveTypeGroupName,
            @inParam3 = @theCommodityTypeName,
            @inParam4 = @inXLedgerExpenseType

        set @outHadError = 1
    end

    if( @theXLedgerAuthorityTypeObjectValueCanPost = 0 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @AUTHORITY_TYPE_MARKED_AS_CANNOT_POST,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theAuthorityTypeName

        set @outHadError = 1
    end

    if( @theXLedgerAuthorityTypeObjectValueHidden = 1 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @AUTHORITY_TYPE_MARKED_AS_HIDDEN,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theAuthorityTypeName

        set @outHadError = 1
    end

    if( @theXLedgerMoveTypeObjectValueCanPost = 0 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @MOVE_TYPE_MARKED_AS_CANNOT_POST,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theMoveTypeGroupName

        set @outHadError = 1
    end

    if( @theXLedgerMoveTypeObjectValueHidden = 1 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @MOVE_TYPE_MARKED_AS_HIDDEN,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theMoveTypeGroupName

        set @outHadError = 1
    end

    if( @theXLedgerCommodityTypeObjectValueCanPost = 0 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @COMMODITY_TYPE_MARKED_AS_CANNOT_POST,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theCommodityTypeName

        set @outHadError = 1
    end

    if( @theXLedgerCommodityTypeObjectValueHidden = 1 )
    begin
        exec spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @COMMODITY_TYPE_MARKED_AS_HIDDEN,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @inSourceTable,
            @inSourceID = @inSourceID,
            @inObjectTable = @inObjectTable,
            @inObjectID = @inObjectID,
            @inParam1 = @theCommodityTypeName

        set @outHadError = 1
    end

end

-- Report results.  @outHadError should never be null.
set @outHadError = isnull( @outHadError, 1 )
if( @outHadError = 1 )
begin
    -- If we had an error then we cannot return an Xgl.
    set @outXGlId = null
end
GO
GRANT EXECUTE ON  [dbo].[spBATGetSingleXLedgerXglId] TO [MssExec]
GO