/*
*    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.
*    @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, then 'None' is assumed.
*    @inIsStorageProcessing: Used by spRecordBatOrStorageError to log errors via spBATRecordError (0) or spStorageRecordError (1).
*    
*    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) = null,
    @inIsStorageProcessing bit,
    @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 @theBatErrorCode int
declare @theSourceTable varchar(30) = 'Order'
declare @theObjectTable varchar(20) = 'Order'

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 @theAuthorityTypeName varchar(30)
declare @theMoveTypeGroupName varchar(20)
declare @theCommodityTypeName varchar(26)

-- 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 = Orders.AuthPriKey,
    @theMoveTypeGroupId = MoveTypeGroups.MTGroupPriKey,
    @theCommodityId = Orders.Commodity,
    @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( @theAuthorityId is null )
begin
    exec @theBatErrorCode = spRecordBatOrStorageError
        @inIsStorageProcessing = @inIsStorageProcessing,
        @inErrorCodeID = @ORDER_IS_MISSING_VALID_AUTHORITY_TYPE,
        @inOrderID = @inOrderID,
        @inSessionID = @inSessionID,
        @inSourceTable = @theSourceTable,
        @inSourceID = @inOrderID,
        @inObjectTable = @theObjectTable,
        @inObjectID = @inOrderID

    set @outHadError = 1
end

if( @theMoveTypeGroupId is null )
begin
    exec @theBatErrorCode = spRecordBatOrStorageError
        @inIsStorageProcessing = @inIsStorageProcessing,
        @inErrorCodeID = @ORDER_IS_MISSING_VALID_MOVE_TYPE_GROUP,
        @inOrderID = @inOrderID,
        @inSessionID = @inSessionID,
        @inSourceTable = @theSourceTable,
        @inSourceID = @inOrderID,
        @inObjectTable = @theObjectTable,
        @inObjectID = @inOrderID

    set @outHadError = 1
end

if( @theCommodityId is null )
begin
    exec @theBatErrorCode = spRecordBatOrStorageError
        @inIsStorageProcessing = @inIsStorageProcessing,
        @inErrorCodeID = @ORDER_IS_MISSING_VALID_COMMODITY_TYPE,
        @inOrderID = @inOrderID,
        @inSessionID = @inSessionID,
        @inSourceTable = @theSourceTable,
        @inSourceID = @inOrderID,
        @inObjectTable = @theObjectTable,
        @inObjectID = @inOrderID

    set @outHadError = 1
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 XLedgerAuthorityTypeObjectValue on XLedgerAuthorityTypeObjectValue.XLedgerAuthorityTypeObjectValueId = Xgl.XLedgerAuthorityTypeObjectValueFid
    inner join XLedgerMoveTypeObjectValue on XLedgerMoveTypeObjectValue.XLedgerMoveTypeObjectValueId = Xgl.XLedgerMoveTypeObjectValueFid
    inner join XLedgerCommodityTypeObjectValue on XLedgerCommodityTypeObjectValue.XLedgerCommodityTypeObjectValueId = Xgl.XLedgerCommodityTypeObjectValueFid
    inner join XLedgerExpenseType on XLedgerExpenseType.XLedgerExpenseTypeId = Xgl.XLedgerExpenseTypeFid
    where XLedgerAuthorityTypeObjectValue.AuthorityTypeFid = @theAuthorityId and
        XLedgerMoveTypeObjectValue.MoveTypeGroupFID = @theMoveTypeGroupId and
        XLedgerCommodityTypeObjectValue.CommTypeFid = @theCommodityId and
        XLedgerExpenseType.TypeName = @inXLedgerExpenseType

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

        set @outHadError = 1
    end

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

        set @outHadError = 1
    end

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

        set @outHadError = 1
    end

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

        set @outHadError = 1
    end

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

        set @outHadError = 1
    end

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

        set @outHadError = 1
    end

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

        set @outHadError = 1
    end

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

        set @outHadError = 1
    end

    if( @theXLedgerCommodityTypeObjectValueHidden = 1 )
    begin
        exec @theBatErrorCode = spRecordBatOrStorageError
            @inIsStorageProcessing = @inIsStorageProcessing,
            @inErrorCodeID = @COMMODITY_TYPE_MARKED_AS_HIDDEN,
            @inOrderID = @inOrderID,
            @inSessionID = @inSessionID,
            @inSourceTable = @theSourceTable,
            @inSourceID = @inOrderID,
            @inObjectTable = @theObjectTable,
            @inObjectID = @inOrderID,
            @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