Calendar

January 2012
SunMonTueWedThuFriSat
 << < > >>
1234567
891011121314
15161718192021
22232425262728
293031    

Links

Announce


Who's Online?

Member: 0
Visitor: 1

Invent Movement Journal Creation and Posting
static void MovementJournalImportExcel(Args _args)
{
     InventJournalTrans         inventJournalTrans;
     InventDim                  inventDim;
     InventJournalTable         inventJournalTable;
     InventJournalCheckPost     journalCheckPost;
     InventJournalId            journalId;
     journalTableData           journalTabledata;
     InventBatch                inventBatch;
     InventBatch                localInventBatch;
     NumberSeq                  numberSeq;
     NumberSequenceReference    numberSequenceReference;
     InventSerial               inventSerial;
     InventSerial               localinventSerial;
     int j,countno=0,i,k;
     real Scarp;
     FilenameOpen    filename;

     Sysexcelapplication excelapp=sysexcelapplication::construct();
     sysexcelworksheet excelworksheet;
     sysexcelrange excelrange;
     sysexcelcells excelcells;
    // comvariant cellvalue=new comvariant();
    ;

    // Creating Journal Header
    inventJournaltable.initValue();
    inventJournalTable.JournalNameId = 'ERecover';
    numberSeq = new NumberSeq();
    numberSequenceReference = InventParameters::numRefInventJournalId();
    numberseq = NumberSeq::newGetNum(numberSequenceReference);

    inventJournalTable.JournalId = numberseq.num();
    inventJournalTable.Description = InventJournalName::find(
inventJournalTable.JournalNameId).Description;
    inventJournalTable.insert();


    excelapp.workbooks().open('C:\\Documents and
Settings\\asrivastava\\Desktop\\ElectronicData_OnlyWstatus_10032007.xls');
    excelworksheet=excelapp.worksheets().itemFromNum(1);
    excelcells=excelworksheet.cells();

    // Creating Unit Numbers
     for(i=301;i<=5600;i++)
     {
        inventBatch.clear();
        inventBatch.initValue();
        inventBatch.itemId = excelcells.item(i,11).value().bStr();
        inventBatch.inventBatchId = excelcells.item(i,1).value().bStr();
        localinventBatch = InventBatch::find(inventBatch.inventBatchId,
inventBatch.itemId);

        if(!localinventBatch)
        {
            inventBatch.OakSerialUnit = excelcells.item(i,2).value().bStr();
            inventBatch.insert();
        }
     }

    //Creating Appartment Numbers
     for(k=1;k<=648;k++)
     {
        inventSerial.clear();
        inventSerial.initValue();
        inventSerial.InventSerialId = excelcells.item(k,8).value().bStr();
        inventSerial.ItemId = excelcells.item(k,11).value().bStr();
        localinventSerial = InventSerial::find(inventSerial.InventSerialId,
inventSerial.ItemId);

        if(!localInventSerial)
        {
            inventSerial.ProdDate = systemDateGet();
            inventSerial.insert();
        }
     }

     // Creating Journal Lines
     for(j=301;j<=5600;j++)
     {
        inventJournalTrans.clear();
        inventJournalTrans.initValue();
        inventJournalTrans.TransDate = systemDateGet();
        inventJournalTrans.LedgerAccountIdOffset = "99999";
        inventJournalTrans.JournalType = InventJournalType::Movement;
        inventJournalTrans.JournalId = inventJournalTable.JournalId;
        numberSeq = new NumberSeq();
        numberSequenceReference =
InventParameters::numRefInventJournalVoucherId();
        numberseq = NumberSeq::newGetNum(numberSequenceReference);

        inventJournalTrans.Voucher     = numberseq.num();
        inventJournalTrans.ItemId      = excelcells.item
(j,11).value().bStr();
        // defaulting branch and item name
        inventJournalTrans.CostAmount  = InventTable::find(
inventJournalTrans.ItemId).inventTableModuleInvent().Price;
        inventJournalTable             = InventJournalTable::find(
inventJournalTrans.JournalId);
        inventDim.InventLocationId     =  excelcells.item
(j,10).value().bStr();
        inventDim.inventBatchId        = excelcells.item
(j,1).value().bStr();
        inventDim.inventSerialId       = excelcells.item
(j,8).value().bStr();
        inventJournalTrans.InventDimId =
inventDim::findOrCreate(inventDim).inventDimId;
        inventJournalTrans.Qty         = 1;
        inventJournalTrans.AdjustmentNotes = "Initial Data Load";
        inventJournalTrans.LineNum = j;


        inventJournalTrans.insert();
     }


    excelapp.workbooks().item(1).saved(true);
    excelapp.workbooks().close();

    // Posting Journal
    journalTableData = JournalTableData::newTable(inventJournalTable);
    journalTableData.updateBlock
(JournalBlockLevel::InUse,JournalBlockLevel::None);
    if (!infolog.num(Exception::Error))
    {
        infolog.clear(0);
        journalCheckPost =
InventjournalCheckPost::newJournalCheckPost(JournalCheckPostType::Post,InventJournalTable);
        journalCheckPost.parmAutoBlock(true);
        journalCheckPost.run();
    }
}
16 Jan 2009
Admin · 1642 views · Leave a comment
Using ADO for interfacing AX with an external database
f we need to interface any external database with Dynamics AX, we can achieve this task by using ADO and its AX available classes:CCADOConnection, CCADORecordSet, CCADOFields, CCADOField andCCADOCommand. Here an example:

static void ADOTestJob(Args _args)
{CCADOConnection     ccConnection;
CCADOCommand        ccCommand;
CCADORecordSet      ccRecordset;
CCADOFields         ccFields;
str                 st;
str                 data1;
int                 data2;
;
ccConnection = new CCADOConnection();// Setting the connection string
ccConnection.connectionString(StrFmt('Provider=SQLOLEDB.1;Persist Security Info=False;User ID=%3;Password=%4;Initial Catalog=%2;Data Source=%1', 
'servername'  // Server's IP or name,
 'database'    // Database or catalog, 
'user'        // Username, 
'pwd'         // Password));// Open the connection
ccConnection.open();// Preparing the query
st = "SELECT * FROM mytable";// Recordset object creation
ccRecordset = new CCADORecordSet();// Executing the querycc
Recordset.open( st, ccConnection );// Reading data
while (!ccRecordset.EOF())
{
ccFields = ccRecordset.fields();// We can access fields either by name or by Index
data1 = ccFields.itemName("FIELD1").value();
data2 = ccFields.itemIdx(1).value();
info(strfmt("Data %1, %2", data1, data2));// Read next record
ccRecordset.moveNext();
}// Closing the connection
ccRecordset.close();
ccConnection.close();}


If we need to execute something (an UPDATE, DELETE, etc.), we can use the CCADOCommand:

void ExecuteSQLExt(str sql){// Creating the ADO Command objectccCommand = new CCADOCommand();// Associate it with an existing opened connectionccCommand.activeConnection(ccConnection);// Executing the commandccCommand.commandText(SQL);ccCommand.execute();}


NOTE: For working with ADO constants like cursor types (adOpenForwardOnly, adOpenKeyset, adOpenDynamic, adOpenStatic), we only need to include the macro CCADO (#CCADO) where they are defined.
03 Jan 2009 
Admin   · 260 views · 7 comments 
Sendning Report as an attachment

void SendingReportasMail(UserId userid,EmplTable empltable)

{

    #SysMailer

    SysMailer mailer;

    SysMailerAddressField tos;

    SysMailerAttachments attachments;

    InteropPermission interopPermission; 




    SysReportRun            reportRun;

    printJobSettings p1,printJobSettings;

    Args _args; 




    SysINetMail m = new SysINetMail();

    str fileName = 'C:\PurchaseOrders.pdf';

    str relayServer;

    int portNumber;

    str userName;

    str password;

    boolean NTLM;

    SysEmailParameters parameters      = SysEmailParameters::find();

    if (parameters.SMTPRelayServerName)

        relayServer =   parameters.SMTPRelayServerName;

    else

        relayServer = parameters.SMTPServerIPAddress;

    portNumber =  parameters.SMTPPortNumber;

    userName =  parameters.SMTPUserName;

    password = SysEmailParameters::password();

    NTLM = parameters.NTLM;

    CodeAccessPermission::revertAssert();

    WinAPI::deleteFile(fileName); //For Deleting the saved file

    try

    { 




        _args = new Args();

        _args.parm(userid);

        _args.name(reportstr(WI_EmailPurchDetailsNew));

        reportRun = classFactory.reportRunClass(_args);

        reportRun.query().interactive(false);

        reportRun.report().interactive(false); 




        printJobSettings =reportrun.printJobSettings();

        printJobSettings.format(PrintFormat::PDF);

        reportRun.setTarget(PrintMedium::File);

        printJobSettings.fileName(fileName);  //For Saving the file in C Drive

        reportRun.run(); 




        interopPermission = new InteropPermission(InteropKind::ComInterop);

        interopPermission.assert();

        mailer = new SysMailer();

        CodeAccessPermission::revertAssert();

        mailer.SMTPRelayServer(relayServer,portNumber,userName,password,NTLM);

        mailer.subject("Purchase Order Details");

        mailer.fromAddress("test@gamil.com","Naresh");

        tos = mailer.tos();

        attachments=mailer.attachments();

        attachments.add(fileName);      //Attaching the Saved File

        tos.appendAddress(empltable.Email);

        mailer.htmlBody("Purchase Orders Details");

        mailer.sendMail(); 




        WinAPI::deleteFile(fileName); //For Deleting the saved file

   }

   catch (Exception::Error)

   {

         infolog.clear(0);

         info("Sending Mail Failed");

   } 




} 



29 Oct 2008 
Admin   · 374 views · 1 comment 
PivotTable SetUP In Ax
1.Open Analysis server SQL Management Studio.
2.Create one Database in In Analysis Server.
3.Go to Ax Administration->SETUP->Business Analysis->OLAP->OLAP SERVESRS>Fill the ID,ServerName->Click on DataBase form->Fill the Serever Name which u created in SQL.
4.Syncronise Cubes in AX form.
5.Open Cube Definition Manager.Import The Cubes and Open Cube Instances.
6.Transfer Cube and Process.
7.Now u can see Pivot Table.


18 Oct 2008 
Admin   · 139 views · 3 comments 
Posting Ledger Vouchers
static void LedgerVoucherAPISample(Args _args)
{
LedgerVoucher _LedgerVoucher;
LedgerVoucherObject _LedgerVoucherObject;
LedgerVoucherTransObject _LedgerVoucherTransObject;
NumberSeq _NumberSeq;
Dimension _Dimension;

NumberSequenceCode _VoucherCode = 'Ledger_3'; // 'Ledger_3' is the voucher Numbersequence in the Path AX>General Ledger>Setup>Journals>Journal Names. Its the Voucher Numref for the Journal Name :)
LedgerAccount _Account = '110180';
LedgerAccount _OffsetAccount = '140270';
AmountCur _AmountCur = 12345.67;

ttsbegin;
_NumberSeq = NumberSeq::newGetVoucherFromCode(_VoucherCode);
_LedgerVoucher = LedgerVoucher::newLedgerPost(DetailSummary::Detail,
SysModule::Ledger,
_VoucherCode);
_LedgerVoucherObject = LedgerVoucherObject::newVoucher(_NumberSeq.voucher());
_LedgerVoucher.addVoucher(_LedgerVoucherObject);
_LedgerVoucherTransObject = LedgerVoucherTransObject::newCreateTrans(_LedgerVoucherObject,
LedgerPostingType::LedgerJournal,
_Account,
_Dimension,
CompanyInfo::standardCurrency(),
_AmountCur,
0,
0);
_LedgerVoucherTransObject.parmTransTxt("Arijit Basu");
_LedgerVoucher.addTrans(_LedgerVoucherTransObject);
_LedgerVoucherTransObject = LedgerVoucherTransObject::newCreateTrans(_LedgerVoucherObject,
LedgerPostingType::LedgerJournal,
_OffsetAccount,
_Dimension,
CompanyInfo::standardCurrency(),
_AmountCur*-1,
0,
0);
_LedgerVoucherTransObject.parmTransTxt("Arijit Basu");
_LedgerVoucher.addTrans(_LedgerVoucherTransObject);
_LedgerVoucher.end();

ttscommit;
}
03 Oct 2008 
Admin   · 227 views · 3 comments 
 1, 2, 3, 4, 5  Next page