/*
* Don't use this stored proc directly but instead use spRevenueGetItemsForRatePlan_Synonym.
* spRevenueGetItemsForRatePlan_Synonym will either point to this stored proc or to
* spRevenueGetItemsForRatePlan_XLedger if that is active.
*
*
*    Description: ItemCodes that are associated to a specific order's rate plan..
*
*    @params:
*    @inOrderId: The primary key for the current order.
*
*/

CREATE PROCEDURE [dbo].[spRevenueGetItemsForRatePlan_XLedger]
    @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
)

declare @theRatePlanId int = (
    select
        Orders.RPPriKey
    from Orders
    where Orders.PriKey = @inOrderId
)

if( @theXglId is not null and @theRatePlanId is not null and exists (
    select top 1 1
    from Rates
    inner join  XLedgerXglToItemCodeMapping on XLedgerXglToItemCodeMapping.XglFid = @theXglId and
        XLedgerXglToItemCodeMapping.ItemCodeFid = Rates.ICPriKey
    inner join ItemCode on ItemCode.ICPriKey = XLedgerXglToItemCodeMapping.ItemCodeFid
    where Rates.RPPriKey = @theRatePlanId
) )
begin
    select distinct
        ItemCode.ICPriKey,
        ItemCode.ItemCode,
        ItemCode.[Description]
    from Rates
    inner join  XLedgerXglToItemCodeMapping on XLedgerXglToItemCodeMapping.XglFid = @theXglId and
        XLedgerXglToItemCodeMapping.ItemCodeFid = Rates.ICPriKey
    inner join ItemCode on ItemCode.ICPriKey = XLedgerXglToItemCodeMapping.ItemCodeFid
    where Rates.RPPriKey = @theRatePlanId
end
else
begin
    exec spRevenueGetItemsForRatePlan_Legacy
        @inOrderId = @inOrderId
end
GO
GRANT EXECUTE ON  [dbo].[spRevenueGetItemsForRatePlan_XLedger] TO [MssExec]
GO