Code to Import the Journals from excel file in D365F&O X++

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

internal final class Daximportjournal

{

   public static void main(Args _args)

   {

       System.IO.Stream         stream;

       FileUploadBuild          fileUpload;

       DialogGroup              dlgUploadGroup;

       FileUploadBuild          fileUploadBuild;

       FormBuildControl         formBuildControl;

       str                      journalnum,businessunit,costcenter,department,itemgroup,projectid,Journalname;

       LedgerJournalTable       ledgerJournalTable,ledgerJournalTableloc,ledgerJournalTableloc1;

       LedgerJournalTrans       ledgerJournalTrans;

       ledgerJournalName        ledgerJournalName;

       Dialog                   dialog = new Dialog("Import of Journals");

       LedgerJournalTable       legderjournalTable;

       DimensionDefault         result;

       DimensionAttribute       dimensionAttribute;

       NumberSeq                numberseq;

       DimensionAttributeValue  dimensionAttributeValue;

 

       DimensionAttributeValueSetStorage    valueSetStorage = new DimensionAttributeValueSetStorage();

       container                           conAttr = ["BusinessUnit", "CostCenter", "Department","ItemGroup","Project"];

 

       dlgUploadGroup = dialog.addGroup("Select excel file");

       formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());

       fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');

       fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

       fileUploadBuild.fileTypesAccepted('.xlsx');

 

       if (dialog.run() && dialog.closedOk())

       {

           FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));

           FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

 

           if (fileUploadResult != null && fileUploadResult.getUploadStatus())

           {

               stream = fileUploadResult.openResult();

               using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(stream))

               {

                   int rowCount, i,y;

                   Package.Load(stream);

                   OfficeOpenXml.ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);

                   OfficeOpenXml.ExcelRange range = worksheet.Cells;

                   rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;

                   try

                   {

                       for (i = 2; i<= rowCount; i++)

                       {

                           journalnum      = range.get_Item(i, 1).value;

                           businessunit    = range.get_Item(i, 2).value;

                           costcenter      = range.get_Item(i, 3).value;

                           department      = range.get_Item(i, 4).value;

                           itemgroup       = range.get_Item(i, 5).value;

                           projectid       = range.get_Item(i, 6).value;

                           Journalname     = range.get_Item(i, 7).value;

                           container               conValue = [businessunit, costcenter, department,itemgroup,projectid];

                           str                     dimValue;

                           for (y = 1; y <= conLen(conAttr); y++)

                           {

                               dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,y));

                               if (dimensionAttribute.RecId == 0)

                               {

                                   continue;

                               }

                               dimValue = conPeek(conValue,y);

                               if (dimValue != "")

                               {

 

                                   dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,true);

                                   valueSetStorage.addItem(dimensionAttributeValue);

                               }

                           }

                           conValue = conNull();

                           result = valueSetStorage.save();

                           ledgerJournalTableloc = LedgerJournalTable::find(journalnum,false);

                           if(ledgerJournalTableloc)

                           {

                               select forupdate ledgerJournalTable

                               where ledgerJournalTable.JournalNum == journalnum;

                       

                               ttsbegin;

                               ledgerJournalTable.DefaultDimension = result;

                               ledgerJournalTable.update();

                               ttscommit;

                           }

                           else

                           {

                               ttsbegin;

                               ledgerJournalTableloc.clear();

                               ledgerJournalTableloc.initValue();

                               ledgerJournalTableloc.JournalNum = journalnum;

                               ledgerJournalTableloc.JournalName = Journalname;

                               ledgerJournalTableloc.JournalType = ledgerjournaltype::Daily;

                               ledgerJournalTableloc.DefaultDimension = result;

                               ledgerJournalTableloc.insert();

                               ttscommit;

                               select ledgerJournalTableloc1

                                   where ledgerJournalTableloc1.JournalName == Journalname;

 

                               numberSeq = NumberSeq::newGetVoucherFromId(ledgerJournalTableloc1.NumberSequenceTable,false);

 

                               ledgerJournalTrans.clear();

                               ledgerJournalTrans.initValue();

                               ledgerJournalTrans.defaultRow();

                               ledgerJournalTrans.JournalNum = ledgerJournalTableloc.JournalNum;

                               ledgerJournalTrans.Voucher = numberseq.voucher();

                               ledgerJournalTrans.insert();

                           }

                       }

                   }

                   catch (Exception::Error)

                   {

                       ttsabort;

                       throw error("error here");

                   }

                   

               }

           }

       }

   }

}

............................................

Output :

Code to create the Invoice approval in D365 f&o

 My requirement is to create invoice approvals for the posted invoice register lines which were available in find voucher of invoice approval.

AP>Invoice approval>Create journal>Find voucher.

I need to create two journals with the details present in image.

class VendInvoiceApprovalAutomationService extends SysOperationServiceBase

{

   public void processRecords()

   {

       QueryRun                queryRun = this.query();

       LedgerJournalTrans      ledgerJournalTrans;

       LedgerJournalTable      ledgerJournalTable;

 

       while(queryRun.next())

       {

           ledgerJournalTrans = queryRun.get(tableNum(LedgerJournalTrans));

 

           try

           {

               ttsbegin;

               this.createHeader(ledgerJournalTable);

               if (ledgerJournalTable.RecId && ledgerJournalTrans.RecId)

               {

                   this.createApprovalLines(ledgerJournalTrans,ledgerJournalTable);

                   Info(strFmt("Vendor approval journal created -%1",ledgerJournalTable.JournalNum));

               }

               else

               {

                   throw error("Record not found.");

               }

               ttscommit;

           }

           catch

           {

               continue;

           }

       }

   }

 

   public void createApprovalLines(LedgerJournalTrans _ledgerJournalTrans,LedgerJournalTable _ledgerJournalTable)

   {

       LedgerJournalTrans                  ledgerJournalTrans,ledgerJournalTransLoc,ledgerJournalTransNew;

       RefRecId                            ledgerJournalTransRecId = 0;

       ledgerJournalTransRecId = _ledgerJournalTrans.RecId;

 

       LedgerJournalEngine_VendApprove  ledgerJournalEngine = LedgerJournalEngine::construct(LedgerJournalType::Approval);

       LedgerJournalEngine_Server::addVoucher(_ledgerJournalTable,LedgerJournalTrans::findRecId(ledgerJournalTransRecId,false));

 

       select ledgerJournalTransLoc where ledgerJournalTransLoc.RecId == ledgerJournalTransRecId;

       select firstonly ledgerJournalTrans

           where ledgerJournalTrans.JournalNum == _ledgerJournalTable.JournalNum;

 

       ledgerJournalEngine.newJournalActive(_ledgerJournalTable);

       ledgerJournalEngine.preCreate(ledgerJournalTrans);

       ledgerJournalEngine.active(ledgerJournalTransLoc);

 

       ledgerJournalTransNew.initValue();

 

       ledgerJournalTransNew.VendTransId = ledgerJournalTransLoc.VendTransId;

       ledgerJournalTransNew.AccountType = ledgerJournalTransLoc.AccountType;

       ledgerJournalTransNew.JournalNum  = _ledgerJournalTable.JournalNum;

       ledgerJournalEngine.initValue(ledgerJournalTransNew);

       ledgerJournalTransNew.setDefaultAccount(true, ledgerJournalTransLoc.parmAccount());

       ledgerJournalTransNew.LineNum = LedgerJournalTrans::firstLineNum(_ledgerJournalTable.JournalNum) - 1;

       if (ledgerJournalTransNew.LineNum == 0)

       {

           ledgerJournalTransNew.LineNum = -1;

       }

       ledgerJournalTransNew.CurrencyCode = ledgerJournalTransLoc.CurrencyCode;

       ledgerJournalEngine.preWrite(ledgerJournalTransNew);

       ledgerJournalEngine.preWriteUpdateDefaultDimension(ledgerJournalTransNew);

       ledgerJournalTransNew.insert();

       ledgerJournalEngine.write(ledgerJournalTransNew);

       

   }

 

   public void createHeader(LedgerJournalTable      ledgerJournalTable)

   {

       LedgerJournalName  ledgerJournalName;

 

       select firstonly ledgerJournalName

           where ledgerJournalName.JournalType == LedgerJournalType::Approval;

       if (!ledgerJournalName)

       {

           throw Error ("Journal Name does not exists in the system.");

       }

       ledgerJournalTable.clear();

       ledgerJournalTable.initValue();

       ledgerJournalTable.initFromLedgerJournalName(ledgerJournalName.JournalName);

       ledgerJournalTable.JournalNum  = JournalTableData::newTable(ledgerJournalTable).nextJournalId();

       ledgerJournalTable.JournalType = LedgerJournalType::Approval;

       ledgerJournalTable.insert();

   }

 

   public QueryRun query()

   {

       QueryBuildDataSource qbds;

       QueryBuildRange qbr;

       LedgerJournalId vendInvoicePoolJournalNum = LedgerJournalTable::findVendorInvoicePool().JournalNum;

       Query query = new Query();

       qbds = query.addDataSource(tablenum(LedgerJournalTrans));

 

       qbr = qbds.addRange(fieldnum(LedgerJournalTrans, JournalNum ));

       qbr.value(queryValue(vendInvoicePoolJournalNum));

 

       qbr = qbds.addRange(fieldnum(LedgerJournalTrans, AccountType));

       qbr.value(queryValue(LedgerJournalACType::Vend));

 

       qbr = qbds.addRange(fieldnum(LedgerJournalTrans, Approved));

       qbr.value(queryValue(NoYes::No));

 

       qbds = qbds.addDataSource(tablenum(LedgerJournalTransAccountView));

       qbds.addLink(fieldnum(LedgerJournalTrans, RecId), fieldnum(LedgerJournalTransAccountView, RecId1));

       qbds.joinMode(JoinMode::ExistsJoin);

       qbds.fetchMode(QueryFetchMode::One2One);

 

       qbds = qbds.addDataSource(tablenum(VendTrans));

       qbds.joinMode(JoinMode::ExistsJoin);

       qbds.fetchMode(0);

 

       qbds.addLink(fieldnum(LedgerJournalTransAccountView, AccountNum), fieldnum(VendTrans, AccountNum));

       qbds.addLink(fieldnum(LedgerJournalTransAccountView, Voucher), fieldnum(VendTrans, Voucher));

       qbds.addLink(fieldnum(LedgerJournalTransAccountView, TransDate), fieldnum(VendTrans, TransDate));

       qbds.addLink(fieldnum(LedgerJournalTransAccountView, Invoice), fieldnum(VendTrans, Invoice));

 

       qbr = qbds.addRange(fieldnum(VendTrans, Arrival));

       qbr.value(queryValue(NoYes::Yes));

 

       qbr = qbds.addRange(fieldnum(VendTrans, JournalNum));

       qbr.value(queryValue(vendInvoicePoolJournalNum));

       

       QueryRun  qr = new QueryRun(query);

 

       return qr;

   }

}

.....................................................

Output :