/**
*    
*    This procedure gets a general ledger number and an Accounting Account primary key
* for the Expense account that is mapped to a particular item code.
*    
*    Input Parameters:
*    @param @inSessionID ID to uniquely identify current BAT Session.
*    @param @inOrderID Primary key of the Order record.
*    @param @inItemCodeID Primary key of the ItemCode record.
*    @param @inLaborTypeID Primary key of the LaborType record.
*    @param @inBranchID The primary key for the branch.
*    @param @inDivisionID The primary key for the division.
*    @param @inSource The source used to log errors.
*    @param @inSourceID The primary key for the source.
*    @param @inObject The object(more generic than source i.e. Orders) used to log errors.
*    @param @inObjectID The primary key for the object.
*    @param @outExpenseGLNumber The Expense general ledger number.
* @param @outAccountingAccountId Primary key of an AccountingAccount record for the Expense account.
*    @param @outErrorCode Error code indicating what happened during the call.
*/

create procedure [dbo].[BATBuildExpenseGLNumber_XLedger]
    @inSessionID varchar(50),
    @inOrderID int,
    @inItemCodeID int,
    @inLaborTypeID int,
    @inBranchID int,
    @inDivisionID int,
    @inSource varchar(50),
    @inSourceID int,
    @inObject varchar(15),
    @inObjectID int,
    @outExpenseGLNumber varchar(66) output,
    @outAccountingAccountId int output,
    @outErrorCode     int output
as
set nocount on

declare @ERROR_MISSING_ACCOUNT int
set @ERROR_MISSING_ACCOUNT = 758

-- Initialize output variables
set @outExpenseGLNumber = ''
set @outErrorCode = 0
set @outAccountingAccountId = null

-- Get the Expense account's ID and GL number.
select
    @outAccountingAccountId = XLedgerItemCodeMap.AccountingAccountFid,
    @outExpenseGLNumber = isnull( AccountingAccount.Code, '' )
from XLedgerItemCodeMap
inner join XLedgerItemCodeMapAccountType on
    XLedgerItemCodeMapAccountType.XLedgerItemCodeMapAccountTypeId = XLedgerItemCodeMap.XLedgerItemCodeMapAccountTypeFid
inner join AccountingAccount on AccountingAccount.AccountingAccountId = XLedgerItemCodeMap.AccountingAccountFid
where
    XLedgerItemCodeMapAccountType.MappingType = 'Expense' and
    XLedgerItemCodeMap.ItemCodeFid = @inItemCodeID

-- If the account could not be found or its GL number is empty, log an error.
if( @outAccountingAccountId is null or @outExpenseGLNumber = '' )
begin
    set @outErrorCode = 1

    -- Get the item code's description to add to the error message.
    declare @theItemCode int
    select @theItemCode = isnull( ItemCode, -1 )
    from ItemCode
    where ICPriKey = @inItemCodeID

    -- Log an error for the missing account.
    exec spBATRecordError
        @inSessionID,
        @inSource,
        @inSourceID,
        @ERROR_MISSING_ACCOUNT,
        @inObject,
        @inObjectID,
        @outExpenseGLNumber,
        @theItemCode,
        null
end
GO
GRANT EXECUTE ON  [dbo].[BATBuildExpenseGLNumber_XLedger] TO [MssExec]
GO