/**
* Description: Validates the customer number for BAT transactions.
* This stored procedure checks the customer number against the appropriate accounting system's customer table.
*
* Error codes:
* IF GREAT PLAINS IS ACTIVE or INACTIVE
* 338 if the customer number is not in the accounting system's table
* 339 if the customer is marked as inactive
* 340 if the customer is marked as on hold
*/
CREATE PROCEDURE [dbo].[BATCheckCustomerStatus]
    @inBATSessionID varchar(50),
    @inObjectTable varchar(20) = null,
    @inObjectID int = null
as

set nocount on

declare @theErrorCode int = 0
declare @theBlankSessionID varchar(50) = ''
declare @theBATProcessTqble varchar(50) = 'BATProcess'

declare @theInvalidCustomers table
(
    RecordID int identity( 1, 1 ) not null,
    CustomerNumber varchar(15) null,
    ErrorCodeID int not null,
    [Source] varchar(50),
    SourceRecord int
)

declare @theErrorCounter int
declare @theRecordID int = 1
declare @theCustomerNumber varchar(15)
declare @theErrorCodeID int
declare @theSource varchar(50)
declare @theSourceRecord int

insert into @theInvalidCustomers
(
    CustomerNumber,
    ErrorCodeID,
    [Source],
    SourceRecord
)
select
    CustomerNumber = rtrim( BATProcess.CustomerNumber ),
    ErrorCodeID = case
        when CustomerInformation.CustomerJoinField is null then 338
        when CustomerInformation.Inactive = 1 then 339
        when CustomerInformation.Hold = 1 then 340
    end,
    [Source] = BATProcess.[Source],
    SourceRecord = BATProcess.SourceRecord
from BATProcessCustomer_Synonym as BATProcess
left outer join CustomerInformation_Synonym as CustomerInformation on CustomerInformation.CustomerJoinField = BATProcess.CustomerJoinField
where BATProcess.BATSessionID = @inBATSessionID and
    BATProcess.CustomerNumber is not null and
    (
        CustomerInformation.CustomerJoinField is null or
        CustomerInformation.Inactive = 1 or
        CustomerInformation.Hold = 1
    )
set @theErrorCounter = ( select count(*) from @theInvalidCustomers )

if( @theErrorCounter > 0 )
begin
    -- Log all of the errors
    while( @theRecordID <= @theErrorCounter )
    begin
        select
            @theCustomerNumber = CustomerNumber,
            @theErrorCodeID = ErrorCodeID,
            @theSource = [Source],
            @theSourceRecord = SourceRecord
        from @theInvalidCustomers
        where RecordID = @theRecordID

        exec spBATRecordError
            @theBlankSessionID,
            @theSource,
            @theSourceRecord,
            @theErrorCodeID,
            @inObjectTable,
            @inObjectID,
            @theCustomerNumber

        set @theRecordID = @theRecordID + 1
    end
    set @theErrorCode = 1
end

return @theErrorCode
GO
GRANT EXECUTE ON  [dbo].[BATCheckCustomerStatus] TO [MssExec]
GO