Job for Free Text Invoice

static void CopyOfInvoiceText(Args _args)
{
    DirPerson DirPerson;

    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    OMOperatingUnit         OMOperatingUnit;
    int                     row=1,site;

    Name                                name,titlename,IdentificationNumber;
    FileName                            filename;

    CustInvoiceTable CustInvoiceTable,CustInvoiceTable_recId;
    CustInvoiceline CustInvoiceline,CustInvoiceline_loc;

    SourceDocumentHeader SourceDocumentHeader,SourceDocumentHeader_loc;
    SourceDocumentLine SourceDocumentLine,SourceDocumentLine_loc,SourceDocumentLine_upd;

    Hcmi9DocumentList Hcmi9DocumentList,Hcmi9DocumentList_loc;
    HcmPersonIdentificationNumber HcmPersonIdentificationNumber;
    Hcmi9Eligibility Hcmi9Eligibility;
            HcmWorkerTitle HcmWorkerTitle;
    DirPartyName  Initials,KnownAs;
    RecId   PersonalTitleRecid,PersonalSuffixRecid;
    HcmWorker HcmWorker_loc,HcmWorker;
    Hcmi9DocumentNum  DocumentNum,I9Status;
    Hcmi9Document Hcmi9Document;
    HcmEmployment HcmEmployment;
    int64 Strvalue;
    NoYes NoYes;
    str CustomerAccount,PersonalTitle,Personalsuffix;
    DirNameAffix DirNameAffix,DirNameAffix_loc;
    TransDate InvoiceDate;
    str InvoiceAccount,cur,lang;
    recid reciddesc;
    real Quantity,Amount;
    ;
    application = SysExcelApplication::construct();
    workbooks   = application.workbooks();

   filename = "C:\\Users\\UmeshP\\Desktop\\oct 14\\FreeTaxInvoiceJobTemplate.xlsx";
    //"E:\\WA-DataMigration\\WADataImports\\ResourceRequirementTemplate.xlsx";
    ttsBegin;

    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();
   // select SourceDocumentLine_loc order by SourceDocumentLine_loc.recid desc where SourceDocumentLine_loc.SourceRelationType == 1209;
    select SourceDocumentHeader_loc order by SourceDocumentHeader_loc.recid desc;
    do
    {
        row++;
          CustomerAccount= cells.item(row, 1).value().bStr();// comment this
         if(CustomerAccount == "")
            CustomerAccount   = int2str(cells.item(row, 1).value().double());

        lang =  cells.item(row, 17).value().bStr();



          InvoiceDate= cells.item(row, 3).value().date();

        name = cells.item(row, 2).value().bStr();
       // KnownAs = cells.item(row, 6).value().bStr();

        InvoiceAccount = cells.item(row, 5).value().bStr();
         if(InvoiceAccount == "")
            InvoiceAccount   = int2str(cells.item(row, 5).value().double());

        cur = cells.item(row, 7).value().bStr();
     
        Quantity = cells.item(row, 11).value().double();
     
        Amount = cells.item(row, 6).value().double();
     
        IdentificationNumber= cells.item(row, 10).value().bStr();
         if(IdentificationNumber == "")
            IdentificationNumber   = int2str(cells.item(row, 10).value().double());

    SourceDocumentLine_loc.recid =  SourceDocumentLine_loc.recid;
      //  select SourceDocumentLine_upd where SourceDocumentLine_upd.recid == SourceDocumentLine_loc.recid;

    CustInvoiceTable.OrderAccount = CustomerAccount;
            CustInvoiceTable.InvoiceDate = InvoiceDate;
        CustInvoiceTable.InvoiceAccount = InvoiceAccount;
        CustInvoiceTable.CurrencyCode = cur;
        CustInvoiceTable.LanguageId = lang;
//custInvoiceTable.modifiedField(fieldNum(CustInvoiceTable, OrderAccount));
      //  custInvoiceTable.SourceDocumentHeader = SourceDocumentHeader_loc.RecId;
        //custInvoiceTable.SourceDocumentLine = SourceDocumentLine_upd.recid;
        CustInvoiceTable.insert();
        SourceDocumentLine_loc.recid = SourceDocumentLine_loc.recid - 1;
       
        select CustInvoiceTable_recId order by CustInvoiceTable_recId.createdDateTime desc;// where CustInvoiceTable_recId.SourceDocumentHeader == custInvoiceTable.SourceDocumentHeader;
        CustInvoiceline_loc.ParentRecId = CustInvoiceTable_recId.RecId;
        CustInvoiceline_loc.Quantity = Quantity;
        CustInvoiceline_loc.AmountCur = Amount;
        CustInvoiceline_loc.insert();
     
        ////////
     
         SourceDocumentHeader.AccountingStatus = SourceDocumentAccountingStatus::InProcess;
        SourceDocumentHeader.SourceRelationType =1209;
        SourceDocumentHeader.TypeEnumName = "SourceDocument_CustomerInvoice";
        SourceDocumentHeader.TypeEnumValue = 2;
        SourceDocumentHeader.insert();

        select SourceDocumentHeader_loc order by SourceDocumentHeader_loc.RecId desc;

        SourceDocumentLine.AccountingStatus = SourceDocumentLineAccountingStatus::Draft;
        SourceDocumentLine.SourceDocumentHeader = SourceDocumentHeader_loc.RecId;
        SourceDocumentLine.SourceRelationType = 1209;
        SourceDocumentLine.TypeEnumName = "SourceDocumentLine_CustomerInvoice";
        SourceDocumentLine.TypeEnumValue =4;
        SourceDocumentLine.insert();

        select SourceDocumentLine_loc order by SourceDocumentLine_loc.recid desc;

          select forUpdate CustInvoiceTable order by CustInvoiceTable.createdDateTime desc;// where CustInvoiceTable.SourceDocumentHeader == 0;
        if(CustInvoiceTable)
    {
        CustInvoiceTable.SourceDocumentHeader = SourceDocumentHeader_loc.RecId;
        CustInvoiceTable.SourceDocumentLine = SourceDocumentLine_loc.RecId;
        CustInvoiceTable.update();

    }
     
          type = cells.item(row+1, 1).value().variantType();
        info("Record inserted");
    }
    while (type != COMVariantType::VT_EMPTY);
    application.quit();
    ttsCommit;



}

Import and create GL transactions (Ledger and Project type) into AX 2012 using x++ code - csv file

#public class LedgerImportNew extends RunBase
        #{
        #    CommaIo                     csvFile;
        #    Filename                    filename;
        #    DialogField                 dialogFilename;
        #    container                   readCon;
        #    container                   ledgerDimensions, offsetDimensions;
        #    counter                     icount,inserted;
        #    Amount                      amount;
        #    LedgerJournalACType         ledgerJournalACType;
        #    ledgerJournalName           ledgerJournalName;
        #    LedgerJournalTable          ledgerJournalTable;
        #    LedgerJournalTrans          ledgerJournalTrans;
        #    LedgerJournalTrans_Project  ledgerProjects;
        #
        #
        #    #define.CurrentVersion(2)
        #    #localmacro.CurrentList
        #        filename,
        #        insertIncorrectRecords,
        #    #endmacro
        #
        #    #localmacro.ListVersion1
        #        filename,
        #        insertIncorrectRecords
        #    #endmacro
        #
        #    #File
        #
        #}

        #public Object dialog()
        #{
        #    DialogRunbase       dialog = super();
        #    ;
        #    dialogFilename  = dialog.addField(extendedTypeStr(FilenameOpen));
        #
        #    dialogFilename.value(filename);
        #    dialog.filenameLookupFilter(["All files", #AllFiles]);
        #
        #    return dialog;
        #}
        #

        #public boolean getFromDialog()
        #{
        #    filename                = dialogFilename.value();
        #
        #    return true;
        #}

        #public LedgerDimensionAccount getLedgerDimension(AccountNum _accountNum)
        #{
        #    MainAccount                 mainAccount;
        #
        #    RefRecId                    accountStructureId;
        #    List                        dimensionList = new List(Types::Class);
        #    DimensionDefaultingEngine   dimensionDefaultingEngine;
        #    ;
        #
        #    mainAccount = MainAccount::findByMainAccountId(_accountNum);
        #
        #    accountStructureId = DimensionHierarchy::getAccountStructure(mainAccount.RecId);
        #
        #    if(mainAccount)
        #    {
        #        dimensionDefaultingEngine = DimensionDefaultingEngine::constructForMainAccountId(mainAccount.RecId, accountStructureId);
        #        dimensionDefaultingEngine.applyDimensionSources(dimensionList);
        #
        #        return dimensionDefaultingEngine.getLedgerDimension();
        #    }
        #    else
        #        return 0;
        #}

        #void run()
        #{
        #    boolean                             first = true;
        #    MainAccount                         mainAccount;
        #    DimensionAttributeValueCombination  dimensionAttributeValueCombination;
        #    MainAccountNum                      mainAccountNum;
        #    NumberSequenceTable                 numberSequenceTable;
        #    LedgerJournalEngine_Daily           ledgerJournalEngine_Daily;
        #    Voucher                             voucher;
        #    DimensionLedgerAccountType          ledgerAccountType;
        #    str                                 accountType;
        #    Amount                              amountCurDebit,amountCurCredit;
        #    container                           conSplitValue;
        #    projTable                           projTable;
        #    DimensionAttributeValueCombination  ledgerDimension;
        #    ProjId                              projid;
        #    ;
        #
        #    csvFile = new CommaIO(filename, 'r');
        #    try
        #    {
        #        if (csvFile)
        #        {
        #            ttsbegin;
        #            if(first) //Create Journal Header
        #            {
        #                select firstOnly ledgerJournalName where ledgerJournalName.JournalType == LedgerJournalType::Daily;
        #
        #                if(!ledgerJournalName.RecId)
        #                {
        #                    throw error ("ledgerJournalName doesn't exgist");
        #                }
        #
        #                ledgerJournalTable.clear();
        #                ledgerJournalTable.initValue();
        #                ledgerJournalTable.JournalNum    = JournalTableData::newTable(ledgerJournalTable).nextJournalId();
        #                ledgerJournalTable.journalType   = LedgerJournalType::Daily;
        #                ledgerJournalTable.JournalName   = ledgerJournalName.JournalName;
        #                ledgerJournalTable.Name          = ledgerJournalName.Name;
        #                ledgerJournalTable.initFromLedgerJournalName(ledgerJournalName.JournalName);
        #                ledgerJournalTable.insert();
        #
        #                first = false;
        #            }
        #
        #            numberSequenceTable = NumberSequenceTable::find(LedgerJournalName::find(ledgerJournalTable.JournalName).NumberSequenceTable);
        #
        #            voucher =  NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence).voucher();
        #
        #            while (csvFile.status() == IO_Status::OK)
        #            {
        #                readCon = csvFile.read();
        #
        #                icount++;
        #
        #                amountCurDebit = 0;
        #                amountCurCredit = 0;
        #
        #                if (readCon && icount > 1)//dont insert first record of file : header
        #                {
        #                    amountCurDebit = conPeek(readCon,7);
        #                    amountCurCredit = conPeek(readCon,8);
        #
        #                    //split and read accountNumber and Department values
        #                    //AccountNum = conpeek(conSplitValue,1)
        #                    //Department = conpeek(conSplitValue,2)
        #                    conSplitValue = this.splitAccountNumAndDept(strLRTrim(conPeek(readCon,5)));
        #
        #                    if(amountCurDebit > 0 || amountCurCredit > 0)
        #                    {
        #                        //Create Trans ...
        #
        #                        ledgerJournalTrans.clear();
        #                        ledgerJournalTrans.initValue();
        #                        ledgerJournalTrans.JournalNum         = ledgerJournalTable.JournalNum ;
        #                        ledgerJournalTrans.Txt                = conPeek(readCon,6);
        #                        ledgerJournalTrans.transDate          = str2Date(conPeek(readCon,1),213);
        #
        #                        accountType = strLRTrim(conPeek(readCon,3));
        #
        #                        if(enum2str(LedgerJournalACType::Project) == accountType)
        #                        {
        #                            ledgerJournalTrans.AccountType   = LedgerJournalACType::Project;
        #                        }
        #                        else if(accountType == "")
        #                        {
        #                            ledgerJournalTrans.AccountType   = LedgerJournalACType::Ledger;
        #                        }
        #
        #                       //use the below code if need to read account type from account number

/* type              = enum2str(MainAccount::findByMainAccountId(strLRTrim(conPeek(conSplitValue,1))).Type);
        #
        #                        if(enum2str(LedgerJournalACType::Bank) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Bank;
        #                        else if(enum2str(LedgerJournalACType::Cust) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Cust;
        #                        else if(enum2str(LedgerJournalACType::FixedAssets) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::FixedAssets;
        #                        else if(enum2str(LedgerJournalACType::Ledger) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Ledger;
        #                        else if(enum2str(LedgerJournalACType::Project) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Project;
        #                        else if(enum2str(LedgerJournalACType::Vend) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Vend;*/
        #
        #                        if( ledgerJournalTrans.AccountType   == LedgerJournalACType::Project)
        #                        {
        #                            projid = strLRTrim(conPeek(readCon,5));
        #
        #                            select firstonly projTable join RecId from ledgerDimension where ledgerDimension.DisplayValue == projTable.ProjId && projTable.ProjId == projid;
        #
        #                            ledgerJournalTrans.LedgerDimension = ledgerDimension.RecId;
        #                        }
        #                        else
        #                        {
        #                            ledgerJournalTrans.LedgerDimension    = this.getLedgerDimension(strLRTrim(conPeek(conSplitValue,1)));
        #                        }
        #
        #                        ledgerJournalTrans.AmountCurDebit     = amountCurDebit;
        #                        ledgerJournalTrans.AmountCurCredit    = amountCurCredit;
        #                        ledgerJournalTrans.Company            = strLRTrim(conPeek(readCon,2));
        #                        ledgerJournalTrans.CurrencyCode       = strLRTrim(conPeek(readCon,9));
        #                        ledgerJournalTrans.voucher = voucher;
        #                        ledgerJournalTrans.insert();
        #
        #                        if( ledgerJournalTrans.AccountType   == LedgerJournalACType::Project)
        #                        {
        #
        #                           if(!LedgerJournalTrans_Project::checkExist(ledgerJournalTrans.RecId))
        #                           {
        #                                ledgerProjects.clear();
        #                                ledgerProjects.initValue();
        #                                ledgerProjects.CategoryId = strLRTrim(conPeek(readCon,4));
        #                                ledgerProjects.RefRecId = ledgerJournalTrans.RecId;
        #                                ledgerProjects.ProjId = strLRTrim(conPeek(readCon,5));
        #                                ledgerProjects.insert();
        #                            }
        #                            else
        #                            {
        #                                select forUpdate ledgerProjects where ledgerProjects.RefRecId==ledgerJournalTrans.RecId;
        #
        #                                if(ledgerProjects)
        #                                {
        #                                    ledgerProjects.CategoryId = strLRTrim(conPeek(readCon,4));
        #                                    ledgerProjects.ProjId = strLRTrim(conPeek(readCon,5));
        #                                    ledgerProjects.update();
        #                                }
        #                        }
        #                     }
        #                        //Record inserted count
        #                        inserted++;
        #                    }
        #
        #                }
        #            }
        #
        #            ttsCommit;
        #        }
        #
        #        icount--;//Remove header recount from total record count
        #    }
        #    catch(Exception::Error)
        #    {
        #       info(strFmt("%1",Exception::Error));
        #    }
        #
        #    info(strfmt("%1 records inserted out of %2",inserted,icount));
        #}

        #public container splitAccountNumAndDept(str _accountNumDept, str _separator = '-')
        #{
        #
        #    int     separatorPosition;
        #    container con;
        #
        #    ;
        #
        #        separatorPosition = strfind(_accountNumDept, _separator, strlen(_accountNumDept), -strlen(_accountNumDept));
        #
        #        if(separatorPosition)
        #        {
        #            //AccountNumber
        #            con = conIns(con, 1,substr(_accountNumDept, 1, separatorPosition - 1));
        #            //Department
        #            con = conIns(con, 2,substr(_accountNumDept, separatorPosition + 1, 3));
        #
        #        }
        #        else
        #        {
        #            //AccountNumber
        #            con = conIns(con, 1,_accountNumDept);
        #
        #        }
        #
        #    return con;
        #}

        #static void main(Args  args)
        #{
        #    LedgerImportNew        ledgerImportNew;
        #
        #    ;
        #
        #    ledgerImportNew =  new LedgerImportNew();
        #
        #    if(ledgerImportNew.prompt())
        #    {
        #        ledgerImportNew.run();
        #    }
        #