--EXEC _as_ImportARLine '2017-03-11', 1, 'BF', 66, '3ET001', 'AR', 'REFTEST', '1000>001','TESTDESC';
--EXEC _as_ImportARLine '2017-03-11', 1, 'BF', 66, '3ET001', 'AR', 'REFTEST', '1000>001','TESTDESC';
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = '_as_ImportARLine')
DROP PROCEDURE _as_ImportARLine
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Alexander Toufexis
-- Create date: 11/3/17
-- Description: Import AR Batch line
-- Notes: No GL support, home currency only.
-- =============================================
CREATE PROCEDURE _as_ImportARLine
-- Add the parameters for the stored procedure here
@Date as date,
@BatchID as int,
@TrCode as varchar(100),
@Amount as float,
@Account as varchar(100),
@Module as varchar(2), --GL/AR/AP
@Reference as varchar(50),
@GLContra as varchar(100),
@Description as varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Declares
DECLARE @CustID int
DECLARE @TrCodeID int
DECLARE @ModuleID int
DECLARE @isDebit bit
DECLARE @GLID int
DECLARE @LineIDPerm int
--GET Master data
SELECT @ModuleID = CASE WHEN @Module = 'AR' THEN 0
WHEN @Module = 'AP' THEN 1
END
IF @Module = 'AR'
BEGIN
SELECT @CustID = COALESCE((SELECT DCLink FROM Client WHERE Client.Account = @Account),0);
END
ELSE
BEGIN
SELECT @CustID = COALESCE((SELECT DCLink FROM Vendor WHERE Vendor.Account = @Account),0);
END
SELECT @TrCodeID = COALESCE((SELECT idTrCodes FROM TrCodes WHERE Code = @TrCode AND iModule = @ModuleID + 5),0);
SELECT @isDebit = COALESCE((SELECT DebitTrans FROM TrCodes WHERE Code = @TrCode AND iModule = @ModuleID + 5),0);
SELECT @GLID = COALESCE((SELECT AccountLInk FROM Accounts WHERE Accounts.Master_Sub_Account = @GLContra),0);
SELECT @LineIDPerm = 0-- COALESCE((SELECT MAX(idLinePermanent)+1 FROM _etblARAPBatchLines),(SELECT MAX(idLinePermanent)+1 FROM _etblARAPBatchHistoryLines),1)
--Validations:
DECLARE @msg as varchar(200)
--Customer validation
IF @CustID = 0
BEGIN
SELECT @msg = 'No Customer/supplier found for account ' + @Account;
RAISERROR(@msg, 15, 1);
RETURN -1
END
--@TrCodeID check
IF @TrCodeID = 0
BEGIN
SELECT @msg = 'No Transaction code found for tr code ' + @TrCode;
RAISERROR(@msg, 15, 1);
RETURN -1
END
IF @GLID = 0
BEGIN
SELECT @msg = 'No GL Account found for gl code ' + @GLContra;
RAISERROR(@msg, 15, 1);
RETURN -1
END
-- --NOW INSERT!
INSERT INTO [dbo].[_etblARAPBatchLines]
([iBatchID]
,[idLinePermanent]
,[dTxDate]
,[iAccountID]
,[iAccountCurrencyID]
,[iTrCodeID]
,[iGLContraID]
,[bPostDated]
,[cReference]
,[cDescription]
,[cOrderNumber]
,[fAmountExcl]
,[iTaxTypeID]
,[fAmountIncl]
,[fExchangeRate]
,[fAmountExclForeign]
,[fAmountInclForeign]
,[fAccountExchangeRate]
,[fAccountForeignAmountExcl]
,[fAccountForeignAmountIncl]
,[iDiscGLContraID]
,[fDiscPercent]
,[fDiscAmountExcl]
,[iDiscTaxTypeID]
,[fDiscAmountIncl]
,[fDiscAmountExclForeign]
,[fDiscAmountInclForeign]
,[fAccountForeignDiscAmountExcl]
,[fAccountForeignDiscAmountIncl]
,[iProjectID]
,[iSalesRepID]
,[iBatchSettlementTermsID]
,[iModule]
,[iTaxAccountID]
,[bIsDebit]
,[iMBPropertyID]
,[iMBPortionID]
,[iMBServiceID]
,[iMBPropertyPortionServiceID]
,[_etblARAPBatchLines_iBranchID]
--,[_etblARAPBatchLines_dCreatedDate]
--,[_etblARAPBatchLines_dModifiedDate]
--,[_etblARAPBatchLines_iCreatedBranchID]
--,[_etblARAPBatchLines_iModifiedBranchID]
--,[_etblARAPBatchLines_iCreatedAgentID]
--,[_etblARAPBatchLines_iModifiedAgentID]
--,[_etblARAPBatchLines_iChangeSetID]
--,[_etblARAPBatchLines_Checksum]
)
VALUES
(@BatchID --<iBatchID, int,>
,@LineIDPerm --<idLinePermanent, int,>
,@Date --<dTxDate, datetime,>
,@CustID --<iAccountID, int,>
,0 --<iAccountCurrencyID, int,>
,@TrCodeID --<iTrCodeID, int,>
,@GLID --<iGLContraID, int,>
,0 --<bPostDated, bit,>
,@Reference --<cReference, varchar(50),>
,@Description --<cDescription, varchar(100),>
,'' --<cOrderNumber, varchar(20),>
,@Amount --<fAmountExcl, float,>
,0 --<iTaxTypeID, int,>
,@Amount --<fAmountIncl, float,>
,0 --<fExchangeRate, float,>
,0 --<fAmountExclForeign, float,>
,0 --<fAmountInclForeign, float,>
,0 --<fAccountExchangeRate, float,>
,0 --<fAccountForeignAmountExcl, float,>
,0 --<fAccountForeignAmountIncl, float,>
,0 --<iDiscGLContraID, int,>
,0 --<fDiscPercent, float,>
,0 --<fDiscAmountExcl, float,>
,0 --<iDiscTaxTypeID, int,>
,0 --<fDiscAmountIncl, float,>
,0 --<fDiscAmountExclForeign, float,>
,0 --<fDiscAmountInclForeign, float,>
,0 --<fAccountForeignDiscAmountExcl, float,>
,0 --<fAccountForeignDiscAmountIncl, float,>
,0 --<iProjectID, int,>
,0 --<iSalesRepID, int,>
,0 --<iBatchSettlementTermsID, int,>
,@ModuleID --<iModule, int,>
,0 --<iTaxAccountID, int,>
,@isDebit --<bIsDebit, bit,>
,0 --<iMBPropertyID, int,>
,0 --<iMBPortionID, int,>
,0 --<iMBServiceID, int,>
,0 --<iMBPropertyPortionServiceID, int,>
,0 --<_etblARAPBatchLines_iBranchID, int,>
--,<_etblARAPBatchLines_dCreatedDate, datetime,>
--,<_etblARAPBatchLines_dModifiedDate, datetime,>
--,<_etblARAPBatchLines_iCreatedBranchID, int,>
--,<_etblARAPBatchLines_iModifiedBranchID, int,>
--,<_etblARAPBatchLines_iCreatedAgentID, int,>
--,<_etblARAPBatchLines_iModifiedAgentID, int,>
--,<_etblARAPBatchLines_iChangeSetID, int,>
--,<_etblARAPBatchLines_Checksum, binary(20),>
);
SELECT @msg = 'Tx Inserted with ref: ' + @Reference;
PRINT @msg;
END
GO
Overview
Content Tools