Contoh manipulasi tanggal di AX 2009

static void Testing_Tgl(Args _args)
{

    str tglitem,tglstr,blnstr,thnstr,tglwalbln;
    date tgl,akhirbln;
    date d = today(),x;
    int i;

    ;
    tglitem = '2011/06/10';

    tglstr=date2str(str2date(tglitem,321), 123, 2, 0, 0, 0,0);
    blnstr=date2str(str2date(tglitem,321), 123, 0, 0, 2, 0,0);
    thnstr=date2str(str2date(tglitem,321), 123, 0, 0, 0, 0,4);


    info('Tgl :' +tglstr);
    info('Bln :' +blnstr);
    info('thn :' +thnstr);
   

    info('Waktu :'+time2str(timeNow(),TimeSeparator::Dot, TimeFormat::Hour24));

    akhirbln = EndMth(str2date(tglitem,321));

    info('Tgl Awal Bulan '+blnstr+': '+date2str(str2date(thnstr+blnstr+'01',321), 123,2,0,0,0,0));
    info('Tgl Akhir Bulan '+blnstr+': '+date2str(akhirbln, 123, 2, 1, 0, 1,0));

   // info(date2str(str2date(thnstr+blnstr+tglstr,321), 123, 2, 1, 2, 1,4));
    info(date2str(str2date('201110'+tglstr,321), 123, 2, -1, 2, -1,4));

}

Mengetahui koneksi user di SQL Server

SELECT d.name, p.login_time,p.status,p.hostname,p.loginame
from master.dbo.sysprocesses p
join master.dbo.sysdatabases d on p.dbID = d.dbID

Import General Journal dengan CSV (termasuk Fixed asset & prepayment)

static void Import_GL_LedgerJournal_CSV(Args _args)///and also it will import the data from the Excel
{

LedgerJournalName       ledgerJournalName;

LedgerJournalTable      ledgerjournalTable;
Axledgerjournaltrans    axledgerjournaltrans;

LedgerJournalTrans        ledgerJournalTrans;
LedgerJournalTrans_Asset  ledgerJournalTrans_asset;
LedgerJournalACType       ledgerJournalACType;

Numberseq               _NumberSeq;
Voucher                 numbervoucher;

Dialog                  dialog;
DialogField             dialogField,dialogField2,dialogdate;
Filename                filename;

CommaIO                 io;
Container               con;
Container               filterCriteria;
str                     val, qtyStr, priceStr, tgl, tgl_due;
str                     acctype,offacctype,_bookId,prepay;

COMVariant cOMVariant;

int i,j,k;
SysOperationProgress simpleProgress;

#avifiles
;

    Dialog          = new dialog();

    dialogField     = dialog.addField(typeId(FileNameOpen),'File Name');
    filterCriteria  = ['*.csv','*.csv'];//// To filter only CSV files
    filterCriteria  = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();
    if(dialog.run())
    fileName        = dialogField.value();
    info(filename);
    io              = new CommaIO(fileName, "r");
    io.read();
    io.inFieldDelimiter(";");



if(fileName)
{
    ttsBegin;
    ledgerJournalTable.JournalName = "GL-BEGIN";

    ledgerJournalTable.Name    = " Saldo Awal ";
    ledgerJournalTable.initFromLedgerJournalName();
    ledgerJournalTable.insert();

    ttscommit;

    _NumberSeq = NumberSeq::newGetVoucherFromCode(ledgerJournalName::find(ledgerJournalTable.JournalName).VoucherSeries);
    numbervoucher = _NumberSeq.voucher();

    simpleProgress = SysOperationProgress::newGeneral(#aviUpdate,'Importing Transactions',100);///SysProgressOperation

   while((io.status()==IO_Status::Ok))
   {
    con     = io.read();

    if (conlen(con) == 26)
    {
        j++;
        simpleProgress.incCount();
        simpleprogress.setText(strfmt("Transaction Imported: %1",i));
        sleep(10);

        tgl = conPeek(con, 22);
        tgl_due = conPeek(con, 22);
        _BookId = conPeek(con, 19);

        acctype = conPeek(con, 3);
        offacctype = conPeek(con, 2);
        prepay = conPeek(con, 26);

        axledgerjournaltrans = new Axledgerjournaltrans();
        axledgerjournaltrans.parmJournalNum(ledgerJournalTable.JournalNum);
        // mm-dd-yy --> 213, dd-mm-yy --> 123
        axledgerjournaltrans.parmTransDate(str2date(tgl,213));
        //info(tgl + ' - '  + date2str(str2date(tgl,213),123,2,2,2,2,4));

        axledgerjournaltrans.parmVoucher(conPeek(con, 17));

        switch(acctype) //accounttype
        {
            case "Ledger":
                axledgerjournaltrans.parmAccountType(ledgerJournalACType::Ledger);
                break;

            case "Customer":
                axledgerjournaltrans.parmAccountType(ledgerJournalACType::Cust);
                break;

            case "Vendor":
                axledgerjournaltrans.parmAccountType(ledgerJournalACType::Vend);
                break;

            case "Fixed assets":
                axledgerjournaltrans.parmAccountType(ledgerJournalACType::FixedAssets);
                break;

            case "Bank":
                axledgerjournaltrans.parmAccountType(ledgerJournalACType::Bank);
                break;
        }

        axledgerjournaltrans.parmAccountNum(conPeek(con, 4));
        axledgerjournaltrans.parmTxt(conPeek(con, 6));
        axledgerjournaltrans.parmPostingProfile(conPeek(con, 8));

        if (prepay == "Y" )
        {
            axledgerjournaltrans.parmPrepayment(Noyes::Yes);
        }
        else
        {
            axledgerjournaltrans.parmPrepayment(Noyes::No);
        }

        axledgerjournaltrans.parmAmountCurDebit(conPeek(con, 11));
        axledgerjournaltrans.parmAmountCurCredit(conPeek(con, 10));

        switch(offacctype) //offset accounttype
        {
            case "Ledger":
                axledgerjournaltrans.parmOffsetAccountType(ledgerJournalACType::Ledger);
                break;

            case "Customer":
                axledgerjournaltrans.parmOffsetAccountType(ledgerJournalACType::Cust);
                break;

            case "Vendor":
                axledgerjournaltrans.parmOffsetAccountType(ledgerJournalACType::Vend);
                break;

            case "Fixed assets":
                axledgerjournaltrans.parmOffsetAccountType(ledgerJournalACType::FixedAssets);
                break;

            case "Bank":
                axledgerjournaltrans.parmOffsetAccountType(ledgerJournalACType::Bank);
                break;
        }

        axledgerjournaltrans.parmOffsetAccount(conPeek(con, 5));
        axledgerjournaltrans.parmDue(str2date(tgl_due,123));
        axledgerjournaltrans.parmDocumentDate(str2date(tgl,123));

        axledgerjournaltrans.save();
        /*
        if(acctype == "Fixed assets")
        {
        if(axledgerjournaltrans.ledgerJournalTrans().RecId)
                {
                   ttsbegin;
                   ledgerJournalTrans_asset.RefRecId = axledgerjournaltrans.ledgerJournalTrans().RecId;
                   ledgerJournalTrans_asset.AssetId  = axledgerjournaltrans.ledgerJournalTrans().getAssetId();
                   ledgerJournalTrans_Asset.Company  = axledgerjournaltrans.ledgerJournalTrans().getAssetCompany();
                   ledgerJournalTrans_asset.BookId   = _BookId;
                   ledgerJournalTrans_asset.TransType = AssetTransTypeJournal::Acquisition;
                   ledgerJournalTrans_asset.TransType = AssetTransTypeJournal::Depreciation;

                   if(assetype == "A")
                       ledgerJournalTrans_asset.TransType = AssetTransTypeJournal::Acquisition;
                   else
                       ledgerJournalTrans_asset.TransType = AssetTransTypeJournal::Depreciation;

                   ledgerJournalTrans_asset.insert();
                   ttscommit;
                }
         }
        */
        i++;
    } //if

   }//while
}//if filename
}

Buat Master Aset

static void GL_ImporttAsset(Args _args)
{
    axAssetTable    _axAssetTable;
    assetBook       _assetBook;
    AssetId         _AsseiId;
    ;

       if (Box::yesNo("Create New Asset ?",DialogButton::No))
    {

        _axAssetTable =  new axAssetTable();

        _axAssetTable.parmAssetGroup("INV.00");
        _axAssetTable.parmAssetId("008001987001");
        _axAssetTable.parmName("NEW ASSET");

        _axAssetTable.save();

        ttsBegin;
            select forUpdate _assetBook
            where _assetBook.AssetId == "008001987001" && _assetBook.BookId == "Y02";

            _assetBook.Depreciation = NoYes::Yes;

            _assetBook.update();

       ttsCommit;
    }
}

Import Item Master dengan CSV

static void ItemId_Import(Args _args)

{
    axInventTable axInventTable;
    inventTable inventTable;
    itemID itemID;
    textBuffer tb = new textBuffer();
    int cnt;
    int numLines;
    int c;
    container inLine;
    Dimension finDim;
    itemType ItemType;

    Dialog                  dialog;
    DialogField             dialogField,dialogdate;
    Filename                filename;
    CommaIO                 io;
    Container               con;
    Container               filterCriteria;

    COMVariant cOMVariant;
    int i,j,k;
    SysOperationProgress simpleProgress;

    #avifiles
    ;

    Dialog          = new dialog();

    dialogField     = dialog.addField(typeId(FileNameOpen),'File Name');
    filterCriteria  = ['*.csv','*.csv'];//// To filter only CSV files
    filterCriteria  = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();
    if(dialog.run())
    fileName        = dialogField.value();
    info(filename);
    io              = new CommaIO(fileName, "r");
    io.read();
    io.inFieldDelimiter(";");

    if(fileName)

    {

        simpleProgress = SysOperationProgress::newGeneral(#aviUpdate,'Importing Transactions',100);///SysProgressOperation

        while((io.status()==IO_Status::Ok))
        {
            con     = io.read();

            if (conlen(con) == 6)
            {
                if (inventtable::exist(conPeek(con, 1)))
                {
                }
                else
                {
                j++;
                simpleProgress.incCount();
                simpleprogress.setText(strfmt("Transaction Imported: %1",i));
                sleep(10);

                axInventTable =  new axInventTable();
                axInventTable.parmItemId(conPeek(con, 1));
                axInventTable.parmItemName(conPeek(con, 2));
                axInventTable.parmNameAlias(conPeek(con, 2));
                axInventTable.parmItemGroupId(conPeek(con, 4));
                axInventtable.parmItemType(Itemtype::Item);
                axInventTable.parmModelGroupId(conPeek(con, 6));
                axInventTable.parmDimGroupId(conPeek(con, 5));
                //axInventTable.axInventTableModule_Sales().parmTaxItemGroupId(conPeek(inLine, 13));
                //axInventTable.parmPrimaryVendorId(conPeek(inLine, 14));
                //axInventTable.axInventTableModule_Purch().parmPrice(ConPeek(inLine, 15));

                //Set financial dimension

                //finDim[2] = conPeek(inLine, 18);

                //axInventTable.parmDimension(finDim);
                axInventTable.axInventTableModule_Purch().parmUnitId(conPeek(con, 3));
                axInventTable.axInventTableModule_Sales().parmUnitId(conPeek(con, 3));
                axInventTable.axInventTableModule_Invent().parmUnitId(conPeek(con, 3));

                axInventTable.axInventItemInventSetup().axInventDim().parmInventSiteId("Utama");
                axInventTable.axInventItemInventSetup().axInventDim().parmInventLocationId("GI");
               
                axInventTable.axInventItemPurchSetup().axInventDim().parmInventSiteId("Utama");
                axInventTable.axInventItemPurchSetup().axInventDim().parmInventLocationId("GI");
               
                axInventTable.axInventItemSalesSetup().axInventDim().parmInventSiteId("Utama");
                axInventTable.axInventItemSalesSetup().axInventDim().parmInventLocationId("GI");
               
                axInventTable.axInventItemLocation().axInventDim().parmInventSiteId("Utama");

                axInventTable.save();

                ttsbegin;

                ttscommit;
                ++i;
                }
            }

        }

        info(strFmt('Imported %1 Items', i));

    }

}

//-Job End <--

Import saldo awal pada AX dengan CSV

Format impor file CSV harus menggunakan header. Adapun contoh CSV adalah sbb:

NO;TGL;ITEM ;QTY;PRICE;SITE;WH;ACCT
MEI2011;4/30/2011;320000600;100;436.5;Utama;GI;999.999
MEI2011;4/30/2011;320000611;100;519.39;Utama;GI;999.999 

Catatan: Lebih baik yang diinput adalah amount (qty*price), jangan unit price-nya. Jadi parmCostPrice nya bisa diganti dengan parmCostAmount.

==========

static void UploadInventBeginBalance_CSV(Args _args)///and also it will import the data from the Excel
{
InventJournalNameId     inventJournalNameId = "MV-00";///Assign the journal Name
AxInventJournalTrans    axInventJournalTrans;
InventJournalTable      inventJournalTable;
Dialog                  dialog;
DialogField             dialogField,dialogdate;
Filename                filename;

CommaIO                 io;
Container               con;
Container               filterCriteria;
str                     val, qtyStr, priceStr;

COMVariant cOMVariant;

int i,j,k;
SysOperationProgress simpleProgress;

#avifiles
;

    Dialog          = new dialog();
    //dialogField     = dialog
    dialogField     = dialog.addField(typeId(FileNameOpen),'File Name');
    filterCriteria  = ['*.csv','*.csv'];//// To filter only CSV files
    filterCriteria  = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();
    if(dialog.run())
    fileName        = dialogField.value();
    info(filename);
    io              = new CommaIO(fileName, "r");
    io.read();
    io.inFieldDelimiter(";");



if(fileName)
{
ttsBegin;
inventJournalTable.JournalNameId = inventJournalNameId;
inventJournalTable.initFromInventJournalName(InventJournalName::find(inventJournalNameId));
inventJournalTable.insert();
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate,'Importing Transactions',100);///SysProgressOperation


while((io.status()==IO_Status::Ok))
{
con     = io.read();

if (conlen(con) == 8)
{
j++;
simpleProgress.incCount();
simpleprogress.setText(strfmt("Transaction Imported: %1",i));
sleep(10);

axInventJournalTrans = new AxInventJournalTrans();
axInventJournalTrans.parmJournalId(inventJournalTable.JournalId);
axInventJournalTrans.parmTransDate( mkDate(30, 04, 2011));
axInventJournalTrans.parmLineNum(j);

axInventJournalTrans.parmItemId(conPeek(con, 3));

axInventJournalTrans.axInventDim().parmInventSiteId(conPeek(con, 6));

axInventJournalTrans.axInventDim().parmInventLocationId(conPeek(con, 7));

axInventJournalTrans.parmQty(conPeek(con, 4));

axInventJournalTrans.parmCostPrice(conPeek(con, 5));

axInventJournalTrans.parmLedgerAccountIdOffset(conPeek(con, 8));

axInventJournalTrans.save();
i++;
} //if

}
ttsCommit;
}
}

Buat CSV dari makro Excel

Buat CSV dari Makro EXCEL, dengan menggunakan delimiter semicolon (titik-koma).

Sebernarnya bisa saja pake save-as nya EXCEL, tapi harus ubah regional setting windowsnya.

Sub CreateCSV()

    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long
       
    'Open a text file to write
    sFname = "C:\MyCsv.csv"
    lFnum = FreeFile
   
    Open sFname For Output As lFnum
    'Loop through the rows'
        For Each rRow In ActiveSheet.UsedRange.Rows
        'Loop through the cells in the rows'
        For Each rCell In rRow.Cells
            sOutput = sOutput & rCell.Value & ";"
        Next rCell
         'remove the last comma'
        sOutput = Left(sOutput, Len(sOutput) - 1)
       
        'write to the file and reinitialize the variables'
        Print #lFnum, sOutput
        sOutput = ""
     Next rRow
   
    'Close the file'
    Close lFnum
   
End Sub

Referensi : http://www.mrexcel.com/forum/showthread.php?t=134185

Buat Trigger di SQL Server

Misalnya di table Customer dibuat trigger, supaya otomatis nambah record ke table TableCust. Nah, trigger ini dipasang di table Customer, yg punya field CustCode dan Custname. Di sini ada proses pengecekan, apakah di table tujuan, sudah ada kode & nama yang sama.

ALTER Trigger [dbo].[UpdateTableCust]
    On [dbo].[Customer]
    For Insert,Update
As
Declare @Code As Varchar(50);
Declare @Name As Varchar(50);

Select @Code = i.CustCode From inserted i;
Select @Name = i.CustName From inserted i;

If @Code is not Null
Begin
    If Not Exists(Select CustCode From TableCust Where CustCode = @Code and CustName =@Name)
        Begin
            Insert Into TableCust(CustCode,CustName) Values (@Code,@Name)

End

Buat Item Master di AX 2009 menggunakan Class AIF

Berikut ini code untuk buat item master, tapi belom ketemu untuk buat default Site & location nya....ada yang bisa bantu ?

static void Services_InventTable_Create(Args _args)
{
    str     inventdim;
    str     inventdimS;
    str     inventdimW;
    InventDim _inventdim;

    // Inventory Service class
    InventItemService     InventService;
    InventItem            Inventory;

    // Data object of Service class
    InventItem_InventTable                      InventTable;
    InventItem_DefaultInventDimInventSetup      InventDefault;
    InventItem_DefaultInventDimPurchSetup       PurchDefault;
    InventItem_DefaultInventDimSalesSetup       SalesDefault;
    InventItem_InventItemInventSetup            InventItemSetup;
    AifEntityKeyList                            entityKeyList;
    ItemId                                      ItemId;
    ;

    //Service instance
    InventService =  InventItemService::construct();
    Inventory = new InventItem();

    Inventory.createInventTable();
    InventTable = Inventory.parmInventTable().addNew();

    InventTable.parmItemId("1114");
    InventTable.parmItemName("AUTO");
    InventTable.parmItemGroupId("RM");
    InventTable.parmDimGroupId("DIM02");
    InventTable.parmModelGroupId("MED01");

         // Create Inventory
    entityKeyList = InventService.create(Inventory);

    if(entityKeyList)
        ItemId = entityKeyList.getEntityKey(1).parmKeyDataMap().lookup(fieldnum(InventTable, ItemId));
        infolog.messageWin().addLine(ItemId);

}

Hapus semua transaksi AX

Hati-hati menggunakan script ini, semua transaksi akan terhapus (hapus per Company).

Lamanya proses tergantung jumlah data. 

static void Delete_All_Trans(Args _args)
{

    SysDatabaseTransDelete databaseTransDelete = SysDatabaseTransDelete::construct();
    ;

    if (Box::yesNo(strfmt("@SYS82778", curext()),DialogButton::No))
    {
        databaseTransDelete.run();
        info("@SYS9265");
    }


}

Hapus Customer di AX 2009 menggunakan Class AIF

static void Services_CustTable_Delete(Args _args)

{
AifEntityKeyList entityKeyList;
AifEntityKey aifEntityKey;


// Data object of Service class
CustCustomerService custService;
CustTable custTable;
;

aifEntityKey = new AifEntityKey();
entityKeyList = new AifEntityKeyList();
custService = CustCustomerService::construct();

select firstonly custTable
where custTable.Name == "Cust_Service";

aifEntityKey.parmKeyDataMap(SysDictTable::getKeyData(custTable));
entityKeyList.addEntityKey(aifEntityKey);

try
{
  // Delete Customer
  custService.delete(entityKeyList);
  info ("CustTable record was successfully deleted.");
}

catch(Exception::Error)
{
   exceptionTextFallThrough();
}


}

Buat Vendor di AX 2009 menggunakan Class AIF

Berikut Script AX 2009 untuk buat vendor baru. Penamaannya agak beda dengan customer ternyata....

static void Services_VendTable_Create(Args _args)
{
    // vendor Service class
    VendVendTableService  vendService;
    VendVendTable         vendor;

    // Data object of Service class
    VendVendTable_VendTable vendTable;
    AifEntityKeyList        entityKeyList;
    AccountNum              accountNum;
    ;

    //Service instance
    vendService =  VendVendTableService::construct();
    vendor = new VendVendTable();
    vendor.createVendTable();

    vendTable = vendor.parmVendTable().addNew();


    vendTable.parmAccountNum("001");
    vendTable.parmName("Vend_Service");
    vendTable.parmVendGroup("LAIN-LAIN");
    vendTable.parmCurrency("IDR");

    vendTable.parmPartyType(DirPartyType::Organization);

    // Create vendor
    entityKeyList = vendService.create(vendor);

    if(entityKeyList)
        accountNum = entityKeyList.getEntityKey(1).parmKeyDataMap().lookup(fieldnum(VendTable, AccountNum));
        infolog.messageWin().addLine(accountNum);

}

Buat Customer di AX 2009 menggunakan Class AIF

Berikut script untuk membuat Customer di AX 2009, dengan memanfaatkan Class AIF.

AIF nya tidak perlu di setting apapun.

static void Services_CustTable_Create(Args _args)
{
    // Customer Service class
    CustCustomerService     custService;
    CustCustomer            customer;

    // Data object of Service class
    CustCustomer_CustTable  custTable;
    AifEntityKeyList        entityKeyList;
    AccountNum              accountNum;
    ;

    //Service instance
    custService =  CustCustomerService::construct();
    customer = new CustCustomer();
    customer.createCustTable();
    custTable = customer.parmCustTable().addNew();

    custTable.parmAccountNum("001");
    custTable.parmName("Cust_Service");
    custTable.parmCustGroup("KARY STAFF");
    custTable.parmCurrency("IDR");
    //custTable.parmPartyType(DirPartyType::Organization);
    custTable.parmPartyType(DirPartyType::Person);

    // Create Customer
    entityKeyList = custService.create(customer);

    if(entityKeyList)
        accountNum = entityKeyList.getEntityKey(1).parmKeyDataMap().lookup(fieldnum(CustTable, AccountNum));
        infolog.messageWin().addLine(accountNum);

}

Tambah Send To pada Windows 2008

 C:\Users\[UserName]\AppData\Roaming\Microsoft\Windows

Catatan : AppData folder is a hidden folder by default

Expression tanggal terakhir tiap bulan pada SQL Server Reporting Services

Berikut ini Expression untuk mencari tanggal terakhir tiap bulan:

=dateadd("d",-1,datevalue(year(today())&"-"&month(today())+1))

Prinsip kerja:

1. Cari tahun dan bulan saat ini, tanggal tidak perlu dicari, karena secara otomatis tanggal nya selalu 1.

    datevalue(year(today())&"-"&month(today()))

2. Tambahkan 1 pada bulan tersebut, jadi bulan+1

    datevalue(year(today())&"-"&month(today())+1)

3. Kurangi satu hari, gunakan function DateAdd.

    =dateadd("d",-1,datevalue(year(today())&"-"&month(today())+1))

    "d" : day, initial jumlah hari yang ditambahkan, m, initial jumlah bulan, dst.

     -1: nilai hari yang ditambahkan. bisa digunakan minus juga

Semoga Bermanfaat