Tuesday, 10 November 2015
Post to GL via Report
//AFK
BudgetName := "G/L Budget Entry".GETFILTER("G/L Budget Entry"."Budget Name");
PostingDate := "G/L Budget Entry".GETRANGEMIN("G/L Budget Entry".Date);
//Message('%1',PostingDate);
IF DATE2DMY(PostingDate,2) > 6 THEN BEGIN
StartDate := DMY2DATE(1,7,DATE2DMY(PostingDate,3));
EndDate := DMY2DATE(30,6,DATE2DMY(PostingDate,3)+1);
END ELSE BEGIN
StartDate := DMY2DATE(1,7,DATE2DMY(PostingDate,3)-1);
EndDate := DMY2DATE(30,6,DATE2DMY(PostingDate,3));
END;
BudgetAmount:= 0;
ActualAmount:= 0;
GLBudgetName.RESET;
GLBudgetName.SETRANGE(GLBudgetName.Name,BudgetName);
GLBudgetName.SETRANGE(GLBudgetName."Budget Type",GLBudgetName."Budget Type"::Ceiling);
IF GLBudgetName.FINDFIRST THEN BEGIN
GLBudgetEntry.RESET;
GLBudgetEntry.SETCURRENTKEY("Budget Name",Date,"Posting Status");
GLBudgetEntry.SETRANGE("Budget Name",GLBudgetName.Name);
GLBudgetEntry.SETRANGE(Date,PostingDate);
GLBudgetEntry.SETRANGE(GLBudgetEntry."Posting Status",GLBudgetEntry."Posting Status"::Unposted);
GLBudgetEntry.SETFILTER(GLBudgetEntry."G/L Account No.",'%1..%2','1001','5899');
IF GLBudgetEntry.FINDFIRST THEN BEGIN
GLBudgetEntry.CALCSUMS(GLBudgetEntry.Amount);
BudgetAmount := GLBudgetEntry.Amount;
IF (GLBudgetEntry."Fund No." = '01A') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
GLAcc.RESET;
GLAcc.SETRANGE(GLAcc."No.",'6001');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
END;
END ELSE
IF (GLBudgetEntry."Fund No." = '01B') THEN BEGIN
GLBudgetEntry2.RESET;
GLBudgetEntry2.SETRANGE("Budget Name",GLBudgetName.Name);
GLBudgetEntry2.SETRANGE(Date,PostingDate);
GLBudgetEntry2.SETRANGE(GLBudgetEntry2."Posting Status",GLBudgetEntry2."Posting Status"::Unposted);
GLBudgetEntry2.SETRANGE(GLBudgetEntry2."Fund No.",'01B');
GLBudgetEntry2.SETFILTER(GLBudgetEntry2."Global Dimension 2 Code",'%1..%2','002','025');
IF GLBudgetEntry2.FINDFIRST THEN BEGIN
GLAcc.RESET;
GLAcc.SETRANGE(GLAcc."No.",'6150');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry2."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry2."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
// MESSAGE(FORMAT(ActualAmount));
END;
END;
END ELSE
IF (GLBudgetEntry."Fund No." = '01D') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
GLAcc.RESET;
GLAcc.SETRANGE(GLAcc."No.",'6401');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
END;
END ELSE
IF (GLBudgetEntry."Fund No." = '01C') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
GLAcc.RESET;
GLAcc.SETRANGE(GLAcc."No.",'6201');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
END;
END ELSE
IF (GLBudgetEntry."Fund No." = '01E') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
GLAcc.RESET;
GLAcc.SETRANGE(GLAcc."No.",'6501');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
END;
END ELSE
IF (GLBudgetEntry."Fund No." = '01F') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
GLAcc.RESET;
GLAcc.SETRANGE(GLAcc."No.",'6601');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
END;
END ELSE
IF (GLBudgetEntry."Fund No." = '80') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
GLAcc.RESET;
GLAcc.SETFILTER(GLAcc."No.",'%1..%2','6800','7299');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
END;
END ELSE BEGIN
GLAcc.RESET;
GLAcc.SETRANGE(GLAcc."No.",'7301');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Actual);
ActualAmount := GLAcc.Actual;
END;
END;
IF ABS(BudgetAmount) > ABS(ActualAmount) THEN
ERROR(BTText0002,ABS(ABS(ActualAmount)-ABS(BudgetAmount)))
// MESSAGE(BTText0002,ABS(ABS(ActualAmount)-ABS(BudgetAmount)))
ELSE
//added code chacha- compares the total amount row in the imported excel
GLBudgetName.SETRANGE(GLBudgetName."Budget Type",GLBudgetName."Budget Type"::Ceiling);
IF GLBudgetName.FINDFIRST THEN BEGIN
CLEAR(GLBudgetEntry);
//GLBudgetEntry.RESET;
GLBudgetEntry.SETCURRENTKEY("Budget Name",Date,"Posting Status");
GLBudgetEntry.SETRANGE("Budget Name",GLBudgetName.Name);
GLBudgetEntry.SETRANGE(Date,PostingDate);
GLBudgetEntry.SETRANGE(GLBudgetEntry."Posting Status",GLBudgetEntry."Posting Status"::Unposted);
GLBudgetEntry.SETFILTER(GLBudgetEntry."G/L Account No.",'%1','6150');
IF GLBudgetEntry.FINDFIRST THEN BEGIN
//calcsums chacha
GLBudgetEntry.CALCSUMS(GLBudgetEntry.Amount);
BudgetTotal:= GLBudgetEntry.Amount;
IF ABS(BudgetTotal) <> ABS(ActualAmount) THEN
ERROR('The Excel total is wrong by %1 ',ABS(ABS(ActualAmount)-ABS(BudgetTotal)));
END;
END;
//chacha , check current actual amount vs allocation
IF GLAcc.FINDFIRST THEN BEGIN
CLEAR(GLAcc);
GLAcc.SETCURRENTKEY(GLAcc."No.");
GLAcc.SETFILTER(GLAcc."No.",'%1..%2','1001','5899');
GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
//GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
IF GLAcc.FINDFIRST THEN BEGIN
GLAcc.CALCFIELDS(GLAcc.Allocation);
GLAcc.CALCFIELDS(GLAcc.Actual);
// GLAcc.CALCSUMS(GLAcc.Actual);
// GLAcc.CALCSUMS(GLAcc.Allocation);
CurrentActualTotal+= GLAcc.Actual;
CurrentAllocationTotal+= GLAcc.Allocation;
RemainingAmt:=ABS(GLAcc.Actual)-ABS( CurrentAllocationTotal);
IF RemainingAmt < ABS(ActualAmount) THEN
ERROR('You dont have enough funds');
END;
END;
END;
END;
SETRANGE("Posting Status","Posting Status"::Unposted);
Window.OPEN(
SNText007 +
SNText008);
Window.UPDATE(1,SNText005);
G/L Budget Entry - OnAfterGetRecord()
IF Amount = 0 THEN
CurrReport.SKIP;
GLAcc.GET("G/L Account No.");
IF GLAcc."Account Type" = GLAcc."Account Type"::Posting THEN BEGIN
GenJnlLine.INIT;
GenJnlLine."Source Code" := GenJnlTemplate."Source Code";
GenJnlLine."System-Created Entry" := TRUE;
GenJnlLine."Journal Template Name" := LedgerBudget."Journal Template Name";
GenJnlLine."Journal Batch Name" := LedgerBudget."Journal Batch Name";
GenJnlLine."Line No." := "Entry No.";
GenJnlLine."Budget Name Code" := LedgerBudgetCode;
GenJnlLine.VALIDATE("Account Type",GenJnlLine."Account Type"::"G/L Account");
GenJnlLine.VALIDATE("Account No.","G/L Account No.");
GenJnlLine.VALIDATE("Posting Date",Date);
GenJnlLine."Document No." := DocNo;
IF Description = '' THEN
GenJnlLine.Description := COPYSTR(LedgerBudget.Name,1,MAXSTRLEN(GenJnlLine.Description))
ELSE
GenJnlLine.Description := COPYSTR(Description,1,MAXSTRLEN(GenJnlLine.Description));
GenJnlLine.VALIDATE(GenJnlLine."Bal. Account No.",GenJnlBatch."Bal. Account No.");
GenJnlLine."Gen. Posting Type" := GenJnlLine."Gen. Posting Type"::" ";
GenJnlLine."Gen. Bus. Posting Group" := '';
GenJnlLine."Gen. Prod. Posting Group" := '';
GenJnlLine."VAT Posting" := GenJnlLine."VAT Posting"::"Manual VAT Entry";
GenJnlLine."VAT %" := 0;
GenJnlLine."VAT Amount" := 0;
GenJnlLine."VAT Base Amount" := GenJnlLine.Amount;
GenJnlLine."VAT Bus. Posting Group" := '';
GenJnlLine."VAT Prod. Posting Group" := '';
GenJnlLine.VALIDATE(Amount,Amount);
GenJnlLine."Ctl. Fund No." := "Fund No.";
GenJnlLine.VALIDATE("Fund No.","Fund No.");
DimensionManagement.DIMGLBudgetTOGenJnlLine("G/L Budget Entry",GenJnlLine);
GenJnlLine."Transaction Type" := GenJnlLine."Transaction Type"::Budget;
GenJnlLine.Status := GenJnlLine.Status::Approved;
GenJnlLine."Internal Control No." := "Internal Control No.";
GenJnlLine."Document Line No." := "Entry No.";
GenJnlLine."Budget Type" :=LedgerBudget."Budget Type"; // TBL Added
GenJnlLine.Quantity := Quantity;
GenJnlLine."Unit of Measure Code" := "Unit of Measure Code";
Window.UPDATE(2,GenJnlLine."Account No.");
// Check if a Normal budget needs to be reversed and create reverse line then allow for posting to proceed. == TBL LGM
IF LedgerBudget."Budget Type" =LedgerBudget."Budget Type"::Virement THEN
TestBudgetReverse("G/L Account No.","G/L Budget Entry".Date);
// GenJnlLine.INSERT;
GenJnlPostLine.RUN(GenJnlLine);
"Posting Status" := "Posting Status"::Posted;
MODIFY;
TempGLBudEntry.TRANSFERFIELDS("G/L Budget Entry");
TempGLBudEntry.INSERT;
LinesPosted += 1;
END;
G/L Budget Entry - OnPostDataItem()
IF TempGLBudEntry.FINDFIRST THEN
REPEAT
GLTranManagement.DeleteLineDistributionBuffer(TempGLBudEntry."Internal Control No.");
UNTIL TempGLBudEntry.NEXT = 0;
Window.CLOSE;
MESSAGE(SNText006,LinesPosted);
TestBudgetReverse(BudgetAc : Code[20];PostDate : Date)
GLEntry.SETCURRENTKEY("Transaction Type","G/L Account No.","Fund No.","Global Dimension 1 Code",
"Global Dimension 2 Code","Global Dimension 3 Code","Global Dimension 4 Code",
"Global Dimension 5 Code","Global Dimension 6 Code","Global Dimension 7 Code","Global Dimension 8 Code",
"Posting Date","Budget Name Code","Budget Type");
GLEntry.SETRANGE("Transaction Type",GLEntry."Transaction Type"::Budget);
GLEntry.SETRANGE(GLEntry."G/L Account No.",BudgetAc);
GLEntry.SETRANGE("Fund No.","G/L Budget Entry"."Fund No.");
GLEntry.SETRANGE("Global Dimension 1 Code","G/L Budget Entry"."Global Dimension 1 Code");
GLEntry.SETRANGE("Global Dimension 2 Code","G/L Budget Entry"."Global Dimension 2 Code");
GLEntry.SETRANGE("Global Dimension 3 Code","G/L Budget Entry"."Global Dimension 3 Code");
GLEntry.SETRANGE("Global Dimension 4 Code","G/L Budget Entry"."Global Dimension 4 Code");
GLEntry.SETRANGE("Global Dimension 5 Code","G/L Budget Entry"."Global Dimension 5 Code");
GLEntry.SETRANGE("Global Dimension 6 Code","G/L Budget Entry"."Global Dimension 6 Code");
GLEntry.SETRANGE("Global Dimension 7 Code","G/L Budget Entry"."Global Dimension 7 Code");
GLEntry.SETRANGE("Global Dimension 8 Code","G/L Budget Entry"."Global Dimension 8 Code");
GLEntry.SETRANGE(GLEntry."Posting Date",PostDate);
GLEntry.SETRANGE("Budget Type",GLEntry."Budget Type"::Normal);
GLEntry.CALCSUMS(Amount);
AmtToReverse := GLEntry.Amount;
IF AmtToReverse > 0 THEN
GLLine.INIT;
GLLine."Source Code" := GenJnlTemplate."Source Code";
GLLine."System-Created Entry" := TRUE;
GLLine."Journal Template Name" := 'BUDGET';
GLLine."Journal Batch Name" := 'REVERSE';
GLLine."Line No." := 10000; //"G/L Budget Entry"."Entry No.";
GLLine."Budget Name Code" := LedgerBudgetCode;
GLLine.VALIDATE("Account Type",GLLine."Account Type"::"G/L Account");
GLLine.VALIDATE("Account No.",BudgetAc);
GLLine.VALIDATE("Posting Date",PostDate);
GLLine."Document No." := DocNo;
GLLine.Description := 'Reversal for normal budget line';
GLLine.VALIDATE(GLLine."Bal. Account No.",GenJnlBatch."Bal. Account No.");
GLLine."Gen. Posting Type" := GLLine."Gen. Posting Type"::" ";
GLLine."Gen. Bus. Posting Group" := '';
GLLine."Gen. Prod. Posting Group" := '';
GLLine."VAT Posting" := GLLine."VAT Posting"::"Manual VAT Entry";
GLLine."VAT %" := 0;
GLLine."VAT Amount" := 0;
GLLine."VAT Base Amount" := GLLine.Amount;
GLLine."VAT Bus. Posting Group" := '';
GLLine."VAT Prod. Posting Group" := '';
GLLine.VALIDATE(Amount,-AmtToReverse);
GLLine."Ctl. Fund No." := "G/L Budget Entry"."Fund No.";
GLLine.VALIDATE("Fund No.","G/L Budget Entry"."Fund No.");
//DimensionManagement.DIMGLBudgetTOGenJnlLine("G/L Budget Entry",GLLIne);
GLLine."Transaction Type" := GLLine."Transaction Type"::Budget;
GLLine.Status := GLLine.Status::Approved;
GLLine."Internal Control No." := "G/L Budget Entry"."Internal Control No.";
GLLine."Document Line No." := "G/L Budget Entry"."Entry No.";
GLPost.RUN(GLLine);
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment