Stored Procedures [dbo].[GetPlasticAuthorizationsByOrderID]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Parameters
NameData TypeMax Length (Bytes)
@inOrderIDint4
@inSysUserIdint4
Permissions
TypeActionOwning Principal
GrantExecuteMssExec
SQL Script
/*
* Description: Gets a list of all pending plastic payment authorizations
* that have not been finalized yet.  If @inOrderID is specified then only
* pending plastic payment authorizations for that order are returned.  The
* user's full access to the CashReceipts module's SecurityProfile for the
* PlasticDeal's branch is required for any records that are returned.
*/

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
Uses