alter procedure _as_FCR_RevalueBank
@nProfitAmount float
,@nLossAmount float
,@nProfitAccount int
,@nLossAccount int
,@nBankAccountLink int
,@nExchangeRateAmount float
,@nTransactionDate datetime
,@nPexLex varchar(10)
,@nReference varchar(1000)
,@nIsBranch bit
,@noldDate datetime
,@nProjectID int
AS
BEGIN
/*usage:
exec _as_FCR_RevalueBank
@nProfitAmount = 0
,@nLossAmount = 5654.74
,@nProfitAccount = 13704
,@nLossAccount = 13754
,@nBankAccountLink = 17689
,@nExchangeRateAmount = 0.041883
,@nTransactionDate = '2023-06-19'
,@nPexLex = 'PEX'
,@nReference = 'FXR0001'
,@nIsBranch = 0
,@noldDate = '2023-06-19'
,@nProjectID = 0
select top 10 a.master_sub_account,* from postgl
left join accounts a on postgl.accountlink = a.accountlink
order by postgl.autoidx desc
*/
SET NOCOUNT ON;
--DECLARE @nProfitAmount float
--DECLARE @nLossAmount float
--DECLARE @nProfitAccount int
--DECLARE @nLossAccount int
--DECLARE @nAccountLink int
--DECLARE @nExchangeRateAmount float
--DECLARE @nTransactionDate datetime
--DECLARE @nPexLex varchar(10)
--DECLARE @nReference varchar(1000)
--DECLARE @nIsBranch bit
--DEClARE @noldDate datetime
--DECLARE @nProjectID int
DECLARE @RC int
DECLARE @RC2 int
DECLARE @TxDate datetime
DECLARE @Id varchar(5)
DECLARE @ClientLink int
DECLARE @TrCodeID int
DECLARE @DebitAP float
DECLARE @CreditAP float
DECLARE @DebitGL1 float
DECLARE @CreditGL1 float
DECLARE @DebitGL2 float
DECLARE @CreditGL2 float
DECLARE @CurrencyID int
DECLARE @ExchangeRate float
DECLARE @ForeignDebit float
DECLARE @ForeignCredit float
DECLARE @Description varchar(40)
DECLARE @TaxTypeID int
DECLARE @Reference varchar(20)
DECLARE @Order_No varchar(20)
DECLARE @ExtOrderNum varchar(20)
DECLARE @Audit_No VARCHAR(20)
DECLARE @Tax_Amount float
DECLARE @ForeignTaxAmount float
DECLARE @Project int
DECLARE @Outstanding float
DECLARE @ForeignOutstanding float
DECLARE @InvNumKey int
DECLARE @CRCCheck float
DECLARE @DTStamp datetime
DECLARE @UserName varchar(20)
DECLARE @Reference2 varchar(20)
DECLARE @SettlementTermsID int
DECLARE @Profit float
DECLARE @Loss float
DECLARE @ProfitAccount int
DECLARE @LossAccount int
DECLARE @BankAccLink int
DECLARE @GLContraAccLink int
DECLARE @VenClassID int
DECLARE @DrCrAccount int
DECLARE @Reconciled varchar(2)
DECLARE @JobCodeLink int
DECLARE @MFPID int
DECLARE @cPayeeName varchar(100)
DECLARE @bPrintCheque bit
DECLARE @Period int
DECLARE @AutoIdx bigint
DECLARE @TxBranchID int
DECLARE @RepID int
DECLARE @JCRepCost float
DECLARE @IsJCDocLine bit
DECLARE @IsSTGLDocLine bit
DECLARE @iInvLineID bigint
DECLARE @AutoIdx2 bigint
DECLARE @AutoIdx3 bigint
DECLARE @AuditTemp float
DECLARE @cBankRef varchar(20)
DECLARE @bMaintainBranchID bit
declare @GLTaxAccountID int
declare @cLineUserFields Varchar(max)
declare @LinkAccCode int
declare @TillID int
declare @iMBPropertyID int
declare @iMBPortionID int
declare @iMBServiceID int
declare @iMBMeterID int
declare @iMBPropertyPortionServiceID int
-- TODO: Set parameter values here.
SELECT @Profit = @nProfitAmount
SELECT @Loss = @nLossAmount
SELECT @ProfitAccount = @nProfitAccount
SELECT @LossAccount = @nLossAccount
SELECT @ExchangeRate = @nExchangeRateAmount
SELECT @TxDate = @nTransactionDate
SELECT @TrCodeID = (SELECT idTrCodes FROM TrCodes WHERE Code = @nPexLex AND iModule = 4)--6=AP, 5=AR, 4=GL
select @AutoIdx = 0
select @TxBranchID = 0
select @RepID = 0
select @JCRepCost = 0
select @IsJCDocLine = 0
select @IsSTGLDocLine = 0
select @iInvLineID = 0
select @LinkAccCode = 0
select @TillID = 0
select @iMBPropertyID = 0
select @iMBPortionID = 0
select @iMBServiceID = 0
select @iMBMeterID = 0
select @iMBPropertyPortionServiceID = 0
SELECT @Reference = @nReference
SELECT @DrCrAccount = @nBankAccountLink
SELECT @Reconciled = ''
SELECT @JobCodeLink = 0
SELECT @MFPID = 0
SELECT @cPayeeName = ''
SELECT @bPrintCheque = 0
SELECT @cBankRef = ''
SELECT @bMaintainBranchID = 1
-- **** EARLY BREAK IF AMOUNT IS 0****
IF ((ROUND(@Profit + @Loss,2)) = 0)
begin
SELECT 0
Return
end
--NOW: WE SET THE CONTRA ACCOUNT, AND THE DEBIT/CREDIT AMOUNT
IF (ABS(@Profit) > 0)
BEGIN
--PROFIT > 0
SELECT @GLContraAccLink = @ProfitAccount
IF (@Profit > 0) --if profit, then:
BEGIN
--Profit is positive, so DEBIT AP Account (Credit GL Contra)
SELECT @DebitAP = @Profit
SELECT @CreditAP = 0
SELECT @DebitGL1 = @Profit
SELECT @CreditGL1 = 0
SELECT @DebitGL2 = 0
SELECT @CreditGL2 = @Profit
END
ELSE
BEGIN
--Profit is negative, so this is REVERSE TRANSACTION
SELECT @DebitAP = 0
SELECT @CreditAP = -@Profit
SELECT @DebitGL1 = 0
SELECT @CreditGL1 = -@Profit
SELECT @DebitGL2 = -@Profit
SELECT @CreditGL2 = 0
END
END
ELSE
BEGIN
--Loss > 0
SELECT @GLContraAccLink = @LossAccount
IF (@Loss > 0) --if loss, then:
BEGIN
--loss is positive, so CREDIT AP Account (Debit GL Contra)
SELECT @DebitAP = 0
SELECT @CreditAP = @Loss
SELECT @DebitGL1 = 0
SELECT @CreditGL1 = @Loss
SELECT @DebitGL2 = @Loss
SELECT @CreditGL2 = 0
END
ELSE
BEGIN
--Loss is negative, so this is REVERSE TRANSACTION
SELECT @DebitAP = -@Loss
SELECT @CreditAP = 0
SELECT @DebitGL1 = -@Loss
SELECT @CreditGL1 = 0
SELECT @DebitGL2 = 0
SELECT @CreditGL2 = -@Loss
END
END
SELECT @Id = 'CB'
SELECT @CurrencyID = 0
SELECT @ForeignDebit = 0
SELECT @ForeignCredit = 0
SELECT @Description = 'FX Revaluation @ ' + CAST(ROUND(@ExchangeRate,6) AS VARCHAR(10))
SELECT @TaxTypeID = 0
SELECT @Order_No = ''
SELECT @ExtOrderNum = ''
select @cLineUserFields = ''
select @GLTaxAccountID = 0
EXEC @AuditTemp = _bspNextAuditNo;
select @Audit_No = CASE
WHEN @nIsBranch = 1 THEN Cast(@TxBranchID as varchar(20)) + '.' + CAST(@AuditTemp as varchar(10)) + '.0001'
ELSE CAST(@AuditTemp as varchar(10)) + '.0001' END; --'GET NEXT AUDIT NO!!!!' --Check SP _bspNextAuditNo
SELECT @Tax_Amount = 0
SELECT @ForeignTaxAmount = 0
SELECT @Project = @nProjectID
SELECT @Outstanding = 0 --MMMM CHECK!
SELECT @ForeignOutstanding = 0
SELECT @InvNumKey = 0
SELECT @CRCCheck = 0
SELECT @DTStamp = GETDATE()
SELECT @UserName = 'Asamco' --misschien nog veranderen?
SELECT @Reference2 = 'FX Reval ' + Convert(varchar(25), @noldDate, 112) -- (YYYYMMDD)
SELECT @SettlementTermsID = 0
Select @Period = (Select Max(idPeriod) + 1 from _etblPeriod where dPeriodDate < @TxDate)
BEGIN TRY
BEGIN TRANSACTION
--NOW EXECUTE LINE 1 OF GL Tx
EXECUTE @RC = [_bspPostGLTrans]
@AutoIdx2 OUTPUT
,@TxDate
,@Id
,@nBankAccountLink
,@TrCodeID
,@DebitGL1
,@CreditGL1
,@CurrencyID
,@ExchangeRate
,@ForeignDebit
,@ForeignCredit
,@Description
,@TaxTypeID
,@Reference
,@Order_No
,@ExtOrderNum
,@Audit_No
,@Tax_Amount
,@ForeignTaxAmount
,@Project
,@Period
,@Reconciled
,@DrCrAccount
,@JobCodeLink
,@MFPID
,@UserName
,@cPayeeName
,@bPrintCheque
,@Reference2
,@TxBranchID
,@RepID
,@JCRepCost
,@IsJCDocLine
,@IsSTGLDocLine
,@iInvLineID
,@cBankRef
,@bMaintainBranchID
,@GLTaxAccountID
,@cLineUserFields
--NOW EXECUTE LINE 2 OF GL Tx
EXECUTE @RC = [_bspPostGLTrans]
@AutoIdx3 OUTPUT
,@TxDate
,@Id
,@GLContraAccLink
,@TrCodeID
,@DebitGL2
,@CreditGL2
,@CurrencyID
,@ExchangeRate
,@ForeignDebit
,@ForeignCredit
,@Description
,@TaxTypeID
,@Reference
,@Order_No
,@ExtOrderNum
,@Audit_No
,@Tax_Amount
,@ForeignTaxAmount
,@Project
,@Period
,@Reconciled
,@DrCrAccount
,@JobCodeLink
,@MFPID
,@UserName
,@cPayeeName
,@bPrintCheque
,@Reference2
,@TxBranchID
,@RepID
,@JCRepCost
,@IsJCDocLine
,@IsSTGLDocLine
,@iInvLineID
,@cBankRef
,@bMaintainBranchID
,@GLTaxAccountID
,@cLineUserFields
Select @AutoIdx;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
THROW;
END CATCH
END -- sproc
Overview
Content Tools