procedure ReadDataFrom_Data_Menaitech_PAYROLL(Serial_No: Integer)
var
SqlConnection: DotNet SqlConnection;
SqlCommand: DotNet SqlCommand;
SqlDataReader: DotNet SqlDataReader;
ConnectionString: Text;
GenJournalLine: Record "Gen. Journal Line";
NoSeriesMgt: Codeunit "NoSeriesManagement";
NextDocNo: Code[20];
GenJournalBatch: Record "Gen. Journal Batch";
GenJnlPostBatch: Codeunit "Gen. Jnl.-Post Batch";
Counter_Line: Integer;
ValueNumber: Integer;
SQLMonth: Text;
SQLYear: Text;
DimValue: Record "Dimension Value";
DimCode: Code[20];
DimValueCode: Code[40];
begin
Counter_Line := 1;
ValueNumber := 0;
ConnectionString := 'Server=DYNAMIC;Database=M_W_Menaitech;User Id=sa;Password=sapass@123;';
SqlConnection := SqlConnection.SqlConnection(ConnectionString.Trim());
SqlConnection.Open();
SqlCommand := SqlCommand.SqlCommand('SELECT * FROM Data_From_Menaitech WHERE Post_On_Business_Central = 0 AND Serial_No = ' + Format(Serial_No) + ' ORDER BY Serial_No ASC', SqlConnection);
SqlDataReader := SqlCommand.ExecuteReader();
if GenJournalBatch.Get('GENERAL', 'PAYROLL' + Format(Serial_No)) then begin
while SqlDataReader.Read() do begin
if (ValueNumber <> SqlDataReader.GetInt32(15)) then begin
ValueNumber := SqlDataReader.GetInt32(15);
NextDocNo := NoSeriesMgt.GetNextNo(GenJournalBatch."No. Series", Today(), true);
end;
SQLMonth := SqlDataReader.GetString(0).Trim();
SQLYear := SqlDataReader.GetString(1).Trim();
GenJournalLine.Init();
GenJournalLine."Line No." := Counter_Line;
GenJournalLine."Journal Template Name" := 'GENERAL';
GenJournalLine."Journal Batch Name" := 'PAYROLL' + Format(Serial_No);
GenJournalLine."Posting Date" := Today();
GenJournalLine."Document Type" := GenJournalLine."Document Type"::Payment;
GenJournalLine."Document No." := NextDocNo;
GenJournalLine."Account Type" := GenJournalLine."Account Type"::"Bank Account";
GenJournalLine."Account No." := SqlDataReader.GetString(6).Trim();
GenJournalLine.Amount := SqlDataReader.GetDecimal(8);
if SqlDataReader.GetDecimal(8) > 0 then begin
GenJournalLine."Credit Amount" := SqlDataReader.GetDecimal(8);
end else begin
GenJournalLine."Debit Amount" := SqlDataReader.GetDecimal(8);
end;
GenJournalLine.Description := SqlDataReader.GetString(10).Trim();
GenJournalLine."Currency Code" := SqlDataReader.GetString(13).Trim();
// insert Dimension Value ******************************************************************************************************
DimValueCode := '104';
DimCode := 'EMPLOYEE';
if DimValue.Get(DimCode, DimValueCode) then begin
Message('Dimension Value %1 for Dimension %2 is valid', DimValueCode, DimCode);
Message('Validating shortcut dimension code: %1', DimValueCode);
GenJournalLine.ValidateShortcutDimCode(1, DimValueCode);
Message('Shortcut dimension code validated successfully.');
end else begin
Error('Dimension Value %1 for Dimension %2 is not valid', DimValueCode, DimCode);
end;
//*****************************************************************************************************************************
GenJournalLine.Insert();
Counter_Line += 1;
end;
SqlDataReader.Close();
Save_Month_AND_Year_ON_Payroll_Integration_Table(SQLMonth.Trim(), SQLYear.Trim(), Serial_No, Serial_No);
end else begin
Error('General Journal Batch (PAYROLL' + Format(Serial_No) + ') Not Found.');
end;
SqlDataReader.Close();
SqlConnection.Close();
end;
