/*
* Don't use this stored proc directly but instead use spRevenueGetItems_Synonym.
* spRevenueGetItems_Synonym will either point to this stored proc or to
* spRevenueGetItems_Legacy if that is active.
*
*
*    Description: ItemCodes that are associated to @RevGroupPriKey.
*
*    @params:
*    @RevGroupPriKey: The revenue entry primary key used to limit which item codes are returned.
*    @inOrderId: The primary key for the current order.  Used to limit the item codes by the Xgl
*    associated to this order.
*
*/

CREATE PROCEDURE [dbo].[spRevenueGetItems_XLedger]
    @RevGroupPriKey int,
    @inOrderId int
as
set nocount on

declare @theXglId int = (
    select
        Xgl.XglId
    from Orders
    inner join XLedgerAuthorityTypeObjectValue on XLedgerAuthorityTypeObjectValue.AuthorityTypeFid = Orders.AuthPriKey
    inner join MoveType on MoveType.PriKey = Orders.MoveType
    inner join XLedgerMoveTypeObjectValue on XLedgerMoveTypeObjectValue.MoveTypeGroupFID = MoveType.MTGroupPriKey
    inner join XLedgerCommodityTypeObjectValue on XLedgerCommodityTypeObjectValue.CommTypeFid = Orders.Commodity
    inner join XLedgerExpenseType on XLedgerExpenseType.TypeName = 'None'
    inner join Xgl on Xgl.XLedgerAuthorityTypeObjectValueFid = XLedgerMoveTypeObjectValue.XLedgerMoveTypeObjectValueId and
        Xgl.XLedgerMoveTypeObjectValueFid = XLedgerMoveTypeObjectValue.XLedgerMoveTypeObjectValueId and
        Xgl.XLedgerCommodityTypeObjectValueFid = XLedgerCommodityTypeObjectValue.XLedgerCommodityTypeObjectValueId and
        Xgl.XLedgerExpenseTypeFid = XLedgerExpenseType.XLedgerExpenseTypeId
    where Orders.PriKey = @inOrderId
)

if( @theXglId is not null and exists (
    select top 1 1
    from XLedgerXglToItemCodeMapping
    inner join ItemCode on ItemCode.ICPriKey = XLedgerXglToItemCodeMapping.ItemCodeFid
    where XLedgerXglToItemCodeMapping.XglFid = @theXglId and
        ItemCode.RGPriKey = @RevGroupPriKey
) )
begin
    select
        ItemCode.ICPriKey,
        ItemCode.ItemCode,
        ItemCode.[Description],
        Service = str( ItemCode.ItemCode ) + ' - ' + ItemCode.[Description],
        SplitGroup = 0
    from XLedgerXglToItemCodeMapping
    inner join ItemCode on ItemCode.ICPriKey = XLedgerXglToItemCodeMapping.ItemCodeFid
    where XLedgerXglToItemCodeMapping.XglFid = @theXglId and
        ItemCode.RGPriKey = @RevGroupPriKey
    union all
    select
        ItemCode.ICPriKey,
        ItemCode.ItemCode,
        ItemCode.[Description],
        Service = str( ItemCode.ItemCode ) + ' - ' + ItemCode.[Description],
        SplitGroup = 1
    from RevGroups
    inner join ItemCode on RevGroups.ICPriKey = ItemCode.ICPriKey
    where RevGroups.RGPriKey = @RevGroupPriKey
end
else
begin
    exec spRevenueGetItems_Legacy
        @RevGroupPriKey = @RevGroupPriKey,
        @inOrderId = @inOrderId
end
GO
GRANT EXECUTE ON  [dbo].[spRevenueGetItems_XLedger] TO [MssExec]
GO