Import transactions to update VAT Prod. correctly

jackies

Hi People

We created a range of dataports to assist our users to import transactions in Navision. These consist of:
Import with net amount
Import with gross amount
Import with net amount and bal. account
Import with gross amount and bal. account

I have discovered a problem with the VAT prod. posting group that changes the value of the imported amount, especially with the net amount imports.
This is how this dataport currently works:
1. Validate account no.
2. General Journal Line is populated with the G/L account VAT Bus. and Prod. set-up
3. Amount from spreadsheet is imported in the designated field (VAT base for net import and amount for gross input)
4. VAT amount is calculated based on G/L account VAT Bus. and Prod. set-up
5. VAT Bus. and Prod. set-up is copied from the spreadsheet and adjusts VAT amount accordingly

The current procedure causes a problem when the G/L account VAT Bus. and Prod. set-up differ from what you have in the spreadsheet for import.
Example:
G/L account VAT Bus. and Prod. is Local and 0%, respectively
In the spreadsheet the VAT Bus. and Prod. is Local and 15%, respectively and the VAT Base amount (NET) is 20,000
This is what the dataport does in this case:
1. Validate account no.
2. General Journal Line is populated with the account VAT Bus. and Prod. set-up - VAT Bus. Local and Prod. 0%
3. 20,000 from spreadsheet is imported
4. Since the G/L account VAT Prod. set-up is 0%, 20,000 from VAT base amount is also copied under Amount
5. VAT Bus. Local and Prod. set-up 15% is copied from the spreadsheet and since Navision calculates the VAT amount based on Amount these are the results:
Amount = 20,000
VAT Amount = 2,608.70
VAT Base Amount = 17,391.30

If the validation process sequence was correct, Navision should have validated the figures based on the VAT Bus. and Prod. set-up from the spreadsheet. The VAT Base Amount should not change and any VAT charge should be adjusted to the Amount and VAT Amount accordingly.

I believe the code must be ammended in such a way that the dataport validates the VAT Bus. and Prod. based on what it is in the spreadsheet and not what's in the G/L account...

Dataport Import with net amount and bal. account C/AL code

Documentation()
LP 24/07/08: Import Vat Transactions
CXL 19/12/08: Added Global Dimension 1 & 2 Code

Gen. Journal Line - OnPreDataItem()
IF GenJournalLine.FIND('+') THEN vLine := GenJournalLine."Line No.";

Gen. Journal Line - OnBeforeExportRecord()

Gen. Journal Line - OnAfterExportRecord()

Gen. Journal Line - OnBeforeImportRecord()

Gen. Journal Line - OnAfterImportRecord()
vDescription := "Gen. Journal Line".Description;
vVATBusPostingGroup := "Gen. Journal Line"."VAT Bus. Posting Group";
vVATProdPostingGroup := "Gen. Journal Line"."VAT Prod. Posting Group";
vCurrency := "Gen. Journal Line"."Currency Code";
vDocNo := "Gen. Journal Line"."Document No.";

//CXL//
vdimshrtcut1 := "Gen. Journal Line"."Shortcut Dimension 1 Code";
vdimshrtcut2 := "Gen. Journal Line"."Shortcut Dimension 2 Code";
//CXL//

IF vDocNo = '' THEN BEGIN
   IF GenJournalBatch.GET('GENERAL','IMPORT') THEN BEGIN
      "No." := NoSeriesMgt.GetNextNo(GenJournalBatch."No. Series",WORKDATE,FALSE);
      vDocNo := "No.";
   END ELSE ERROR('Please select a No. Series');
END;

vBaseAmount := "Gen. Journal Line"."VAT Base Amount";
vLine := vLine + 10000;

"Gen. Journal Line"."Line No." := vLine;
"Gen. Journal Line"."Journal Template Name":='GENERAL';
"Gen. Journal Line"."Journal Batch Name":='IMPORT';

"Gen. Journal Line".VALIDATE("Account No.");

"Gen. Journal Line"."VAT Base Amount" := vBaseAmount;
"Gen. Journal Line".VALIDATE("Gen. Journal Line"."VAT Base Amount");

"Gen. Journal Line"."Document No." := vDocNo;
"Gen. Journal Line".Description := vDescription;
"Gen. Journal Line"."VAT Bus. Posting Group" := vVATBusPostingGroup;
"Gen. Journal Line"."VAT Prod. Posting Group" := vVATProdPostingGroup;
"Gen. Journal Line"."Currency Code" := vCurrency;

//CXL//
"Gen. Journal Line"."Shortcut Dimension 1 Code" := vdimshrtcut1;
"Gen. Journal Line"."Shortcut Dimension 2 Code" := vdimshrtcut2;
//CXL//

"Gen. Journal Line".VALIDATE("Gen. Journal Line"."Currency Code");
"Gen. Journal Line".VALIDATE("Gen. Journal Line"."VAT Amount (LCY)");
"Gen. Journal Line".VALIDATE("Gen. Journal Line"."VAT Bus. Posting Group");
"Gen. Journal Line".VALIDATE("Gen. Journal Line"."VAT Prod. Posting Group");

//CXL//
"Gen. Journal Line".VALIDATE("Gen. Journal Line"."Shortcut Dimension 1 Code");
"Gen. Journal Line".VALIDATE("Gen. Journal Line"."Shortcut Dimension 2 Code");
//CXL//


"Gen. Journal Line"."Bill-to/Pay-to No." := '';

IF GenJournalBatch."Posting No. Series" <> '' THEN
  "Gen. Journal Line"."Posting No. Series" := GenJournalBatch."Posting No. Series";


Gen. Journal Line - OnPostDataItem()
//NoSeriesMgt.SaveNoSeries();

Dataport fields

Enabled	SourceExpr	
Yes	"Account Type"		
Yes	"Account No."		
Yes	"External Document No."	
Yes	"Posting Date"		
Yes	"Document Type"		
Yes	"Document No."		
Yes	Description		
Yes	"VAT Bus. Posting Group"		
Yes	"VAT Prod. Posting Group"		
Yes	"Currency Code"	
Yes	"VAT Base Amount"		
Yes	"Bal. Account Type"		
Yes	"Bal. Account No."	
Yes	"Shortcut Dimension 1 Code"		
Yes	"Shortcut Dimension 2 Code"

Thanks in advance!

jackies

Anyone... :?

jackies

We have fixed this issue...

Added a second validation for VAT Base Amount after the code for the imported fields..