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;



}