Monday, March 16, 2015

Insert PO with multiple lines from excel

Just like PR if you want to create PO with multiple lines use this code snippet..

create an excel with 

Column 1 -> vend account 
Column 2 -> ItemID
Column 3 -> Quantity
Column 4 -> Price 
Column 5 -> Purchid

If you need multiple lines , create multiple excel rows with same PurchID


static void POEXCEL(Args _args)
{
    Dialog                  dialog;
    DialogField             dialogField;
    FilenameOpen            filename, Filename2;

    PO_temp                 potemp,potempline;
    PurchTable              purchtable;
    PurchLine               purchline;

    PurchFormLetter purchFormLetter;
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    FileIOPermission        permission;
    int row = 0;
    str itemid,name,purchid;
    real price,qty;
     #File

     str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
    {
    switch (_cv.variantType())
    {
    case (COMVariantType::VT_BSTR):
    return _cv.bStr();
    case (COMVariantType::VT_R4):
    return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
    case (COMVariantType::VT_R8):
    return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
    case (COMVariantType::VT_DECIMAL):
    return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
    case (COMVariantType::VT_DATE):
    return date2str(_cv.date(),123,2,1,2,1,4);
    case (COMVariantType::VT_EMPTY):
    return "";
    default:
    throw error(strfmt("@SYS26908", _cv.variantType()));
    }
    return "";
    }


     //importing from excel
    dialog = new Dialog("Posting Payment Journal");
    dialogField = dialog.addField(ExtendedTypeStr("FilenameOpen"),"Source file");



    if (dialog.run())
   {
     filename = dialogField.value();
     permission = new fileIOpermission(filename,"RW");
     permission.assert();
     application = SysExcelApplication::construct();
     workbooks = application.workbooks();
     try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

       delete_from potemp;
         do
    {
    //Incrementing the row line to next Row
    row++;

    //row++;
    name        =  COMVariant2Str(cells.item(row,1).value());
    itemid      =  COMVariant2Str(cells.item(row,2).value());
    qty         =  cells.item(row,3).value().double();
    price       =  cells.item(row,4).value().double();
    purchid  =   COMVariant2Str(cells.item(row,5).value());

        potemp.Name       = name;
        potemp.ItemId     = itemid;
        potemp.PurchQty   = qty;
        potemp.PurchPrice = price;
        potemp.PurchId = purchid;
        potemp.insert();
                //row++;
            //name1 = COMVariant2Str(cells.item(j+1,1).value());

          type = cells.item(row+1, 1).value().variantType();

     }

       //info(strFmt("%1,%2",empId,salary));
      while (type != COMVariantType::VT_EMPTY);
      application.quit();
    }
    while select potemp group by potemp.PurchId,potemp.Name
    {
purchtable.initValue();
purchtable.PurchId = potemp.PurchId;
purchtable.OrderAccount = potemp.name;
purchtable.initFromVendTable();
//if (!purchtable.validateWrite())
//{
//throw Exception::Error;
//}
purchtable.insert();

    while select potempline where potempline.PurchId == potemp.PurchId
    {
        ttsBegin;
    purchLine.initFromPurchTable(purchTable);
    purchLine.ItemId = potempline.ItemId;
    purchLine.PurchQty = potempline.PurchQty;
    purchLine.PurchPrice = potempline.PurchPrice;
    purchLine.createLine(true, true, true, true, true, true);
    ttsCommit;
    purchLine.clear();
    }
    purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);
    purchFormLetter.update(purchTable, strFmt("Inv_%1", purchTable.PurchId));

    //Posting PO Invoice
    purchFormLetter = PurchFormLetter::construct(DocumentStatus::Invoice);
    purchFormLetter.update(purchTable, strFmt("Inv_%1", purchTable.PurchId));

    purchtable.clear();
    }
}

No comments:

Post a Comment

Convert Call stack to readable format in D365FO X++

//Input --container _xppCallStack = xSession::xppCallStack();  Public static str POL_formatXppCallStack(container _xppCallStack, int _skipFr...