[dbo].[GetPlasticAuthorizationsByOrderID]
CREATE PROCEDURE [dbo].[GetPlasticAuthorizationsByOrderID]
@inOrderID int,
@inSysUserId int
as
set nocount on
select
PlasticDealId = PlasticDeal.DealID,
PlasticInstrumentId = PlasticDeal.InstrumentFID,
DealDescription = PlasticDeal.[Description],
AuthorizationDate = PlasticDeal.AuthorizationDate,
AuthorizationAmount = PlasticDeal.AuthorizationAmount,
AuthorizedByName = dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, SysUser.LASTNAME ),
OrderId = PlasticDeal.OrderFID,
OrderNumber = Orders.OrderNo,
CustomerNumber = PlasticDeal.CustomerNumber,
CustomerName = RM00101.CustName,
ShipperName = dbo.FormatFirstNameLastName( Orders.FirstName, Orders.LastName ),
ShipperLastName = Orders.LastName,
CardNickName = PlasticInstrument.NickName,
CardLastFour = PlasticInstrument.LastFour,
BranchPriKey = PlasticDeal.BranchFID,
BranchInfo = Branch.BranchID,
BranchName = Branch.[Name],
ProviderBrand = PlasticProviderType.Brand,
DealAuthorizationId = PlasticDealAuthorization.DealAuthorizationID
from PlasticDealAuthorization
inner join PlasticDeal on PlasticDeal.DealID = PlasticDealAuthorization.DealFID
inner join PlasticInstrument on PlasticInstrument.InstrumentID = PlasticDeal.InstrumentFID
inner join PlasticProvider on PlasticProvider.ProviderID = PlasticInstrument.ProviderFID
inner join PlasticProviderType on PlasticProviderType.ProviderTypeID = PlasticProvider.ProviderTypeFID
inner join Orders on Orders.PriKey = PlasticDeal.OrderFID
inner join Branch on Branch.BranchPriKey = PlasticDeal.BranchFID
inner join SysUser on Sysuser.SysUserID = PlasticDeal.AuthorizationByFID
inner join SecModules on SecModules.[Description] = 'Cash Receipts'
inner join SecAccessTypes on SecAccessTypes.[Description] = 'Full'
inner join UserAccess on UserAccess.SysUserID = @inSysUserId
inner join SecurityProfile on SecurityProfile.SecProfilePriKey = UserAccess.SecProfilePriKey
inner join SecProfileDetail on SecProfileDetail.SecProfilePriKey = SecurityProfile.SecProfilePriKey and
SecProfileDetail.ModulePriKey = SecModules.ModulePriKey and
SecProfileDetail.AccessPriKey = SecAccessTypes.AccessPriKey
left outer join RM00101_Synonym as RM00101 on RM00101.CustNmbr = PlasticDeal.CustomerNumber
where PlasticDeal.DealType = 'A' and
( @inOrderID is null or PlasticDeal.OrderFID = @inOrderID ) and
PlasticDeal.BranchFID = SecProfileDetail.BranchPriKey
order by PlasticDeal.AuthorizationDate
GO
GRANT EXECUTE ON [dbo].[GetPlasticAuthorizationsByOrderID] TO [MssExec]
GO