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);

}