/**
*
* Gets a list of all storage items the are currently in an open billing state that use our plastic
* payment system for a specific electronic merchant provider type.  Used to convert the payment
* tokens from one provider to another. @@inOldProviderTypeId tells us the provider type that we
* are to export (we export all providers of that type).  @inNewProviderTypeCode tells us the new
* type of provider we are looking to convert these payment method tokens to.
*
*/
create procedure [dbo].[GetOpenStorageUsingPlasticPayments]
    @inOldProviderTypeId int,
    @inNewProviderTypeCode varchar(4)
as
set nocount on;

declare @theNewProviderTypeId int = ( select ProviderTypeID from PlasticProviderType where TypeCode = @inNewProviderTypeCode )

select
    PlasticInstrument.ProviderFID,
    StorageID = Storage.StoragePriKey,
    PlasticInstrument.InstrumentID,
    PlasticInstrument.Token,
    PlasticInstrument.HolderName,
    PlasticInstrument.HolderLastName,
    PlasticProvider.AccountCode,
    Branch.BranchID,
    NewProviderFID = PlasticProviderBranchMapping.ProviderFID,
    --NewAccountCode = NewPlasticProvider.AccountCode,
    NewUserName = NewPlasticProvider.UserName
from Storage
inner join PlasticInstrument on PlasticInstrument.InstrumentID = Storage.InstrumentFID
inner join PlasticProvider on PlasticProvider.ProviderID = PlasticInstrument.ProviderFID
inner join Orders on Orders.PriKey = Storage.OrdPriKey
inner join Branch on Branch.BranchPriKey = Orders.BranchPriKey
-- It's possible that this mapping is missing or not completed as yet and/or for all branches.
left outer join PlasticProviderBranchMapping on PlasticProviderBranchMapping.ProviderTypeFID = @theNewProviderTypeId and
    PlasticProviderBranchMapping.BranchFID = Orders.BranchPriKey
left outer join PlasticProvider as NewPlasticProvider on NewPlasticProvider.ProviderID =PlasticProviderBranchMapping.ProviderFID
outer apply (
    select top 1
        IsFinishedBilling = StorageItems.IsFinishedBilling
    from StorageItems
    where StorageItems.StoragePriKey = Storage.StoragePriKey and
        StorageItems.IsFinishedBilling = 0
) as SomeStorageItem
where Storage.DateStorageIn is not null and
    Storage.NextBillDate is not null and
    SomeStorageItem.IsFinishedBilling = 0 and
    PlasticProvider.ProviderTypeFID = @inOldProviderTypeId
order by PlasticProvider.AccountCode, PlasticInstrument.InstrumentID, Storage.StoragePriKey
GO
GRANT EXECUTE ON  [dbo].[GetOpenStorageUsingPlasticPayments] TO [MssExec]
GO