/**
*
*
* Description: Validates the customer number for BAT transactions. The BATProcess table's CustomerNumber, CustomerName and CustomerNumber2 are updated.
* This stored procedure check the customer number in Great Plains if Great Plains API is active or in Movers Suite.
*
* Error codes:
* 722 if @BranchPriKey is not valid
* 725 if @AgentPriKey is not valid
* 747 if there is no customer number for @OrdPriKey and Authority for @OrdPriKey is "Own Authority" and the item's ABTPriKey is null
*
* IF GREAT PLAINS IS ACTIVE
* 723 if the name of the Great Plains database is invalid
* 724 if the alternate bill to customer is not in Great Plains
* 727 if the order's customer number is not in Great Plains
* 728 if the order's customer is marked as inactive
* 740 if the order's customer is marked as on hold
*
* IF GREAT PLAINS IS NOT ACTIVE
* 729 if the alternate bill to customer is not in the database
* 730 if the customer number for the agent's vanline is not in the database
* 731 if the customer number for the order is not in the database
* 728 if the order's customer is marked as inactive
* 740 if the order's customer is marked as on hold
*
* --------------------------------------------------------------------------------------------------------------------------------------------------------------
* NOTE: @OrdPriKey MUST BE VALIDATED PRIOR TO CALLING THIS STORED PROCEDURE !!!!                                                                 
* --------------------------------------------------------------------------------------------------------------------------------------------------------------
*/

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 RM00101.CUSTNMBR is null then 338
        when RM00101.INACTIVE = 1 then 339
        when RM00101.HOLD = 1 then 340
    end,
    [Source] = BATProcess.[Source],
    SourceRecord = BATProcess.SourceRecord
from BATProcess
left outer join RM00101_Synonym as RM00101 on RM00101.CustNmbr = BATProcess.CustomerNumber
where BATProcess.BATSessionID = @inBATSessionID and
    BATProcess.CustomerNumber is not null and
    (
        RM00101.CUSTNMBR is null or
        RM00101.INACTIVE = 1 or
        RM00101.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