Importing Multiple SalesOrders & Multiple SalesLins from Excel


static void MultiSalesOrdersLinefromExcel(Args _args)
{
    SalesTable      salesTable;
    SalesLine       salesLine;
    InventDim       inventDim;
    NumberSeq        num;
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet,worksheet1;
    SysExcelCells cells,cells1;
    COMVariantType type,type1;
    int row=1,row1=1;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    workbooks.open('C:\\Users\\shiva.garapalli\\Desktop\\SalesOrderCreate.xlsx');
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromName("Header");
    worksheet1 = worksheets.itemFromName("Lines");
    cells = worksheet.cells();
    cells1 = worksheet1.cells();
     
    ttsBegin;
    
    do
    {
        row1++;
        salesTable.clear();
         
        num = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
        salesTable.SalesId = num.num();
         
        salesTable.CustAccount = cells.item(row1,1).value().bStr();
        salesTable.initValue(SalesType::Sales);
        salesTable.initFromCustTable();
        salesTable.insert();
     
    do
    {
        row++;
        if( salesTable.CustAccount == cells1.item(row, 1).value().bStr())
        {
            inventDim.clear();
            inventDim.InventSiteId = cells1.item(row,5).value().bStr();//int2str(real2int(cells1.item(row,5).value().double()));
            inventDim.InventLocationId = cells1.item(row,6).value().bstr();//int2str(real2int(cells1.item(row,6).value().double()));
             
            salesLine.clear();
            salesLine.initValue(salesTable.SalesType);
            salesLine.initFromSalesTable(salesTable);
            salesLine.ItemId = cells1.item(row, 2).value().bStr();
            salesLine.initFromInventTable(InventTable::find(cells1.item(row, 2).value().bStr()));
            salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
            salesLine.SalesQty = any2int(cells1.item(row,3).value().toString());
            salesLine.RemainSalesPhysical = salesLine.SalesQty;
            salesLine.SalesUnit = cells1.item(row,4).value().bStr();
            salesLine.DlvMode = cells1.item(row,7).value().bStr();
            salesLine.QtyOrdered = salesLine.calcQtyOrdered();
            salesLine.RemainInventPhysical = salesLine.QtyOrdered;
            salesLine.setPriceDisc(InventDim::find(salesLine.InventDimId));
         
            if (salesLine.validateWrite())
            {
                salesLine.insert();
            }
        }
        type = cells1.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
        row = 0;
        type1 = cells.item(row1+1, 1).value().variantType();
    }
    while(type1 != COMVariantType::VT_EMPTY);
     
  
    
    workbooks.close();
    application.quit();
     
    info("Done!");
      ttsCommit;
}
Advertisements

Importing Sales Order & Sales Lines from Excel file

static void MultiSalesOrdersLineImportfromExcel(Args _args)
{
SalesTable      salesTable;
SalesLine       salesLine;
InventDim       inventDim;
NumberSeq        num;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet,worksheet1;
SysExcelCells cells,cells1;
COMVariantType type;
int row;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbooks.open('C:\\Users\\ax.user01\\Desktop\\SalesOrderCreate.xlsx');
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromName("Header");
worksheet1 = worksheets.itemFromName("Lines");
cells = worksheet.cells();
cells1 = worksheet1.cells();

num = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
salesTable.SalesId = num.num();

salesTable.CustAccount = cells.item(1,1).value().bStr();
salesTable.initValue(SalesType::Sales);
salesTable.initFromCustTable();
if(salesTable.validateWrite())
{
salesTable.insert();
}

do
{
row++;
inventDim.clear();
inventDim.InventSiteId = int2str(real2int(cells1.item(row,4).value().double()));
inventDim.InventLocationId = int2str(real2int(cells1.item(row,5).value().double()));

salesLine.clear();
salesLine.initValue(salesTable.SalesType);
salesLine.initFromSalesTable(salesTable);
salesLine.ItemId = cells1.item(row, 1).value().bStr();
salesLine.initFromInventTable(InventTable::find(cells1.item(row, 1).value().bStr()));
salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
salesLine.SalesQty = any2int(cells1.item(row,2).value().toString());
salesLine.RemainSalesPhysical = salesLine.SalesQty;
salesLine.SalesUnit = cells1.item(row,3).value().bStr();
salesLine.QtyOrdered = salesLine.calcQtyOrdered();
salesLine.RemainInventPhysical = salesLine.QtyOrdered;
salesLine.setPriceDisc(InventDim::find(salesLine.InventDimId));

type = cells1.item(row+1, 1).value().variantType();
if (salesLine.validateWrite())
{
salesLine.insert();
}
}
while (type != COMVariantType::VT_EMPTY);

workbooks.close();
application.quit();

info("Done!");
}

Importing Sales Order & Sales Lines from CSV file

In below code I have taken two CSV files one for Sales Order & another for Sales Lines:

static void MultiSalesOrdersLineImport(Args _args)
{
SalesTable      salesTable;
SalesLine       salesLine;
InventDim       inventDim;
container readcon,readcon1;
NumberSeq        num;
CommaIo commaIo,commaIo1;
commaIo = new CommaIo('C:\\Users\\ax.user01\\Desktop\\SalesOrder&Line.csv','r');
commaIo1 = new CommaIo('C:\\Users\\ax.user01\\Desktop\\SalesOrderNo.csv','r');
commaIo.infieldDelimiter(',');
try
{
ttsbegin;
// Order header
salesTable.clear();
readcon1  = commaIo1.read();
salesTable.CustAccount = conpeek(readcon1,1);
num = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
salesTable.SalesId = num.num();
salesTable.initValue(SalesType::Sales);
salesTable.initFromCustTable();

if (salesTable.validateWrite())
{
salesTable.insert();

while(commaIo.status() == IO_Status::OK)
{
//Order line
readcon = commaIo.read();
salesLine.ItemId = conpeek(readcon,1);

if(salesLine.ItemId == "0")
break;

inventDim.clear();
inventDim.InventSiteId = conpeek(readcon,4);
inventDim.InventLocationId = conpeek(readcon,5);

salesLine.clear();
salesLine.initValue(salesTable.SalesType);
salesLine.initFromSalesTable(salesTable);
salesLine.ItemId = conpeek(readcon,1);
salesLine.initFromInventTable(InventTable::find(conpeek(readcon,1)));

salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
salesLine.SalesQty = conpeek(readcon,2);
salesLine.RemainSalesPhysical = salesLine.SalesQty;
salesLine.SalesUnit = conpeek(readcon,3);
salesLine.QtyOrdered = salesLine.calcQtyOrdered();
salesLine.RemainInventPhysical = salesLine.QtyOrdered;

salesLine.setPriceDisc(InventDim::find(salesLine.InventDimId));

if (salesLine.validateWrite())
{
salesLine.insert();
}
else
throw error("Order line");
}
}
else
throw error("Order header");
ttscommit;
}
catch
{
error("Error!");
return;
}
info("Done!");
}

Evaluating expression given as a String in X++ [AX 2012]

Executes the specified X++ code that is passed as a string.

anytype runBuf(str job,[anytype param])

Below is the simple example:

void RunBufExample()
{
    str myJob = "int myfunc(int i) {return i+7;}";
    ExecutePermission perm;
    ;
 
    perm = new ExecutePermission();
    if (perm != null)
    {
        perm.assert();  // Assert the use of the dangerous API RunBuf. 
        print runBuf(myJob, 42);
        pause;
        CodeAccessPermission::revertAssert();
    }
}

Splitters in Dynamics AX 2012 / SysFormSplitter_Y

//For Reference Tutorial Form in Forms tutorial_Form_Split

//**Make Sure you set the AutoDeclaration to yes for the Groups

public class FormRun extends ObjectRun
{
SysFormSplitter_Y _formSplitter;
}

void init()
{
super();

_formSplitter = new SysFormSplitter_Y(SPLITTER NAME,TOP GROUP NAME,element);
}

______________________________________________________________________
int mouseUp(int x, int y, int button, boolean ctrl, boolean shift)
{
int ret;

ret = super(x, y, button, ctrl, shift);

Return _formSplitter.mouseUp(x, y, button, ctrl, shift);
}
_______________________________________________________________________
int mouseMove(int x, int y, int button, boolean ctrl, boolean shift)
{
int ret;

ret = super(x, y, button, ctrl, shift);

Return _formSplitter.mouseMove(x,y,button,ctrl,shift);
}
_______________________________________________________________________
int mouseDown(int x, int y, int button, boolean ctrl, boolean shift)
{
int ret;

ret = super(x, y, button, ctrl, shift);

Return _formSplitter.mouseDown(x, y, button, ctrl, shift);
}
_______________________________________________________________________

Using Resource image in display()

Using Resource images in tables:

Create a Window Control in grid with properties as:

Width:16
Height:16
Verticalspacing:0
ImageMode:Centre
DataSoure:SalesTable
DataMethod:validIcon

Just Write the below code in SalesTable:

//BP Deviation Documented
display int validIcon()
{
;
switch(this.SalesStatus)
{
case SalesStatus::Backorder:
return 10002;
case SalesStatus::Invoiced:
return 10022;
case SalesStatus::Canceled:
return 929;
case SalesStatus::Delivered:
return 1030;
}
return 0;
}

//Ref Form:Project management and accounting/Setup/Project management and accounting workflows

web

Display Method

Below is the display() to get the count of all Invoiced Sales Order’s from the SalesTable:

(Write Below Code in CustTable Methods) //For Table Level Coding


[SysClientCacheDataMethodAttribute(true)]
display Int64 InvoicedCount()
{
SalesTable salesTable;
;

SELECT COUNT(RecId) FROM salesTable group by salesTable.CustAccount
Where salesTable.SalesStatus == SalesStatus::Invoiced && salesTable.CustAccount == this.AccountNum;
Return salesTable.RecId;
}

Below is the display() to get the count of all Sales Order’s from the SalesTable:
(Write Below Code in CustTable DataSource Methods)// For Form Level Coding


[SysClientCacheDataMethodAttribute(true)]
display Int64 SalesOrderCount()
{
;
SELECT COUNT(RecId) FROM salesTable group by salesTable.CustAccount
Where salesTable.SalesStatus == SalesStatus::Invoiced
|| SalesStatus::Backorder && salesTable.CustAccount == CustTable.AccountNum;

Return salesTable.RecId;
}

OR

Passing current record buffer


[SysClientCacheDataMethodAttribute(true)]
display DimensionValue BusinessUnit(InventJournalTrans _inventJournalTrans)//Passing current record buffer
{

DimensionAttributeValueSetStorage dimStorage;
Counter i;
DimensionValue dimensionValue;
RefRecId defaultdim;

defaultdim = _inventJournalTrans.DefaultDimension; //this number is basically from field DefaultDimension
dimStorage = DimensionAttributeValueSetStorage::find(defaultdim);

for (i=1 ; i<= dimStorage.elements() ; i++)
{
if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name=='BusinessUnit') //Give the Attribute you want to get the value for.
{
dimensionValue = dimStorage.getDisplayValueByIndex(i);
break;
}
}
return dimensionValue;
}