Abdul Ghani blog on Dynamics AX: DMF

Showing posts with label DMF. Show all posts

Thursday, 17 November 2016

Import and create GL transactions (Ledger and Project type) into AX 2012 using x++ code - csv file

#public class LedgerImportNew extends RunBase
        #{
        #    CommaIo                     csvFile;
        #    Filename                    filename;
        #    DialogField                 dialogFilename;
        #    container                   readCon;
        #    container                   ledgerDimensions, offsetDimensions;
        #    counter                     icount,inserted;
        #    Amount                      amount;
        #    LedgerJournalACType         ledgerJournalACType;
        #    ledgerJournalName           ledgerJournalName;
        #    LedgerJournalTable          ledgerJournalTable;
        #    LedgerJournalTrans          ledgerJournalTrans;
        #    LedgerJournalTrans_Project ledgerProjects;
        #
        #
        #    #define.CurrentVersion(2)
        #    #localmacro.CurrentList
        #        filename,
        #        insertIncorrectRecords,
        #    #endmacro
        #
        #    #localmacro.ListVersion1
        #        filename,
        #        insertIncorrectRecords
        #    #endmacro
        #
        #    #File
        #
        #}

        #public Object dialog()
        #{
        #    DialogRunbase       dialog = super();
        #    ;
        #    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
        #
        #    dialogFilename.value(filename);
        #    dialog.filenameLookupFilter(["All files", #AllFiles]);
        #
        #    return dialog;
        #}
        #

        #public boolean getFromDialog()
        #{
        #    filename                = dialogFilename.value();
        #
        #    return true;
        #}

        #public LedgerDimensionAccount getLedgerDimension(AccountNum _accountNum)
        #{
        #    MainAccount                 mainAccount;
        #
        #    RefRecId                    accountStructureId;
        #    List                        dimensionList = new List(Types::Class);
        #    DimensionDefaultingEngine   dimensionDefaultingEngine;
        #    ;
        #
        #    mainAccount = MainAccount::findByMainAccountId(_accountNum);
        #
        #    accountStructureId = DimensionHierarchy::getAccountStructure(mainAccount.RecId);
        #
        #    if(mainAccount)
        #    {
        #        dimensionDefaultingEngine = DimensionDefaultingEngine::constructForMainAccountId(mainAccount.RecId, accountStructureId);
        #        dimensionDefaultingEngine.applyDimensionSources(dimensionList);
        #
        #        return dimensionDefaultingEngine.getLedgerDimension();
        #    }
        #    else
        #        return 0;
        #}

        #void run()
        #{
        #    boolean                             first = true;
        #    MainAccount                         mainAccount;
        #    DimensionAttributeValueCombination dimensionAttributeValueCombination;
        #    MainAccountNum                      mainAccountNum;
        #    NumberSequenceTable                 numberSequenceTable;
        #    LedgerJournalEngine_Daily           ledgerJournalEngine_Daily;
        #    Voucher                             voucher;
        #    DimensionLedgerAccountType          ledgerAccountType;
        #    str                                 accountType;
        #    Amount                              amountCurDebit,amountCurCredit;
        #    container                           conSplitValue;
        #    projTable                           projTable;
        #    DimensionAttributeValueCombination ledgerDimension;
        #    ProjId                              projid;
        #    ;
        #
        #    csvFile = new CommaIO(filename, 'r');
        #    try
        #    {
        #        if (csvFile)
        #        {
        #            ttsbegin;
        #            if(first) //Create Journal Header
        #            {
        #                select firstOnly ledgerJournalName where ledgerJournalName.JournalType == LedgerJournalType::Daily;
        #
        #                if(!ledgerJournalName.RecId)
        #                {
        #                    throw error ("ledgerJournalName doesn't exgist");
        #                }
        #
        #                ledgerJournalTable.clear();
        #                ledgerJournalTable.initValue();
        #                ledgerJournalTable.JournalNum    = JournalTableData::newTable(ledgerJournalTable).nextJournalId();
        #                ledgerJournalTable.journalType   = LedgerJournalType::Daily;
        #                ledgerJournalTable.JournalName   = ledgerJournalName.JournalName;
        #                ledgerJournalTable.Name          = ledgerJournalName.Name;
        #                ledgerJournalTable.initFromLedgerJournalName(ledgerJournalName.JournalName);
        #                ledgerJournalTable.insert();
        #
        #                first = false;
        #            }
        #
        #            numberSequenceTable = NumberSequenceTable::find(LedgerJournalName::find(ledgerJournalTable.JournalName).NumberSequenceTable);
        #
        #            voucher = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence).voucher();
        #
        #            while (csvFile.status() == IO_Status::OK)
        #            {
        #                readCon = csvFile.read();
        #
        #                icount++;
        #
        #                amountCurDebit = 0;
        #                amountCurCredit = 0;
        #
        #                if (readCon && icount > 1)//dont insert first record of file : header
        #                {
        #                    amountCurDebit = conPeek(readCon,7);
        #                    amountCurCredit = conPeek(readCon,8);
        #
        #                    //split and read accountNumber and Department values
        #                    //AccountNum = conpeek(conSplitValue,1)
        #                    //Department = conpeek(conSplitValue,2)
        #                    conSplitValue = this.splitAccountNumAndDept(strLRTrim(conPeek(readCon,5)));
        #
        #                    if(amountCurDebit > 0 || amountCurCredit > 0)
        #                    {
        #                        //Create Trans ...
        #
        #                        ledgerJournalTrans.clear();
        #                        ledgerJournalTrans.initValue();
        #                        ledgerJournalTrans.JournalNum         = ledgerJournalTable.JournalNum ;
        #                        ledgerJournalTrans.Txt                = conPeek(readCon,6);
        #                        ledgerJournalTrans.transDate          = str2Date(conPeek(readCon,1),213);
        #
        #                        accountType = strLRTrim(conPeek(readCon,3));
        #
        #                        if(enum2str(LedgerJournalACType::Project) == accountType)
        #                        {
        #                            ledgerJournalTrans.AccountType   = LedgerJournalACType::Project;
        #                        }
        #                        else if(accountType == "")
        #                        {
        #                            ledgerJournalTrans.AccountType   = LedgerJournalACType::Ledger;
        #                        }
        #
        #                       //use the below code if need to read account type from account number

/* type              = enum2str(MainAccount::findByMainAccountId(strLRTrim(conPeek(conSplitValue,1))).Type);
        #
        #                        if(enum2str(LedgerJournalACType::Bank) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Bank;
        #                        else if(enum2str(LedgerJournalACType::Cust) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Cust;
        #                        else if(enum2str(LedgerJournalACType::FixedAssets) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::FixedAssets;
        #                        else if(enum2str(LedgerJournalACType::Ledger) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Ledger;
        #                        else if(enum2str(LedgerJournalACType::Project) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Project;
        #                        else if(enum2str(LedgerJournalACType::Vend) == type)
        #                        ledgerJournalTrans.AccountType   = LedgerJournalACType::Vend;*/
        #
        #                        if( ledgerJournalTrans.AccountType   == LedgerJournalACType::Project)
        #                        {
        #                            projid = strLRTrim(conPeek(readCon,5));
        #
        #                            select firstonly projTable join RecId from ledgerDimension where ledgerDimension.DisplayValue == projTable.ProjId && projTable.ProjId == projid;
        #
        #                            ledgerJournalTrans.LedgerDimension = ledgerDimension.RecId;
        #                        }
        #                        else
        #                        {
        #                            ledgerJournalTrans.LedgerDimension    = this.getLedgerDimension(strLRTrim(conPeek(conSplitValue,1)));
        #                        }
        #
        #                        ledgerJournalTrans.AmountCurDebit     = amountCurDebit;
        #                        ledgerJournalTrans.AmountCurCredit    = amountCurCredit;
        #                        ledgerJournalTrans.Company            = strLRTrim(conPeek(readCon,2));
        #                        ledgerJournalTrans.CurrencyCode       = strLRTrim(conPeek(readCon,9));
        #                        ledgerJournalTrans.voucher = voucher;
        #                        ledgerJournalTrans.insert();
        #
        #                        if( ledgerJournalTrans.AccountType   == LedgerJournalACType::Project)
        #                        {
        #
        #                           if(!LedgerJournalTrans_Project::checkExist(ledgerJournalTrans.RecId))
        #                           {
        #                                ledgerProjects.clear();
        #                                ledgerProjects.initValue();
        #                                ledgerProjects.CategoryId = strLRTrim(conPeek(readCon,4));
        #                                ledgerProjects.RefRecId = ledgerJournalTrans.RecId;
        #                                ledgerProjects.ProjId = strLRTrim(conPeek(readCon,5));
        #                                ledgerProjects.insert();
        #                            }
        #                            else
        #                            {
        #                                select forUpdate ledgerProjects where ledgerProjects.RefRecId==ledgerJournalTrans.RecId;
        #
        #                                if(ledgerProjects)
        #                                {
        #                                    ledgerProjects.CategoryId = strLRTrim(conPeek(readCon,4));
        #                                    ledgerProjects.ProjId = strLRTrim(conPeek(readCon,5));
        #                                    ledgerProjects.update();
        #                                }
        #                        }
        #                     }
        #                        //Record inserted count
        #                        inserted++;
        #                    }
        #
        #                }
        #            }
        #
        #            ttsCommit;
        #        }
        #
        #        icount--;//Remove header recount from total record count
        #    }
        #    catch(Exception::Error)
        #    {
        #       info(strFmt("%1",Exception::Error));
        #    }
        #
        #    info(strfmt("%1 records inserted out of %2",inserted,icount));
        #}

        #public container splitAccountNumAndDept(str _accountNumDept, str _separator = '-')
        #{
        #
        #    int     separatorPosition;
        #    container con;
        #
        #    ;
        #
        #        separatorPosition = strfind(_accountNumDept, _separator, strlen(_accountNumDept), -strlen(_accountNumDept));
        #
        #        if(separatorPosition)
        #        {
        #            //AccountNumber
        #            con = conIns(con, 1,substr(_accountNumDept, 1, separatorPosition - 1));
        #            //Department
        #            con = conIns(con, 2,substr(_accountNumDept, separatorPosition + 1, 3));
        #
        #        }
        #        else
        #        {
        #            //AccountNumber
        #            con = conIns(con, 1,_accountNumDept);
        #
        #        }
        #
        #    return con;
        #}

        #static void main(Args args)
        #{
        #    LedgerImportNew        ledgerImportNew;
        #
        #    ;
        #
        #    ledgerImportNew = new LedgerImportNew();
        #
        #    if(ledgerImportNew.prompt())
        #    {
        #        ledgerImportNew.run();
        #    }
        #

Tuesday, 4 August 2015

Job for Free Text Invoice

static void CopyOfInvoiceText(Args _args)
{
DirPerson DirPerson;

SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
OMOperatingUnit OMOperatingUnit;
int row=1,site;

Name name,titlename,IdentificationNumber;
FileName filename;

CustInvoiceTable CustInvoiceTable,CustInvoiceTable_recId;
CustInvoiceline CustInvoiceline,CustInvoiceline_loc;

SourceDocumentHeader SourceDocumentHeader,SourceDocumentHeader_loc;
SourceDocumentLine SourceDocumentLine,SourceDocumentLine_loc,SourceDocumentLine_upd;

Hcmi9DocumentList Hcmi9DocumentList,Hcmi9DocumentList_loc;
HcmPersonIdentificationNumber HcmPersonIdentificationNumber;
Hcmi9Eligibility Hcmi9Eligibility;
HcmWorkerTitle HcmWorkerTitle;
DirPartyName Initials,KnownAs;
RecId PersonalTitleRecid,PersonalSuffixRecid;
HcmWorker HcmWorker_loc,HcmWorker;
Hcmi9DocumentNum DocumentNum,I9Status;
Hcmi9Document Hcmi9Document;
HcmEmployment HcmEmployment;
int64 Strvalue;
NoYes NoYes;
str CustomerAccount,PersonalTitle,Personalsuffix;
DirNameAffix DirNameAffix,DirNameAffix_loc;
TransDate InvoiceDate;
str InvoiceAccount,cur,lang;
recid reciddesc;
real Quantity,Amount;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();

filename = "C:\\Users\\UmeshP\\Desktop\\oct 14\\FreeTaxInvoiceJobTemplate.xlsx";
//"E:\\WA-DataMigration\\WADataImports\\ResourceRequirementTemplate.xlsx";
ttsBegin;

try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
// select SourceDocumentLine_loc order by SourceDocumentLine_loc.recid desc where SourceDocumentLine_loc.SourceRelationType == 1209;
select SourceDocumentHeader_loc order by SourceDocumentHeader_loc.recid desc;
do
{
row++;
CustomerAccount= cells.item(row, 1).value().bStr();// comment this
if(CustomerAccount == "")
CustomerAccount = int2str(cells.item(row, 1).value().double());

lang = cells.item(row, 17).value().bStr();

InvoiceDate= cells.item(row, 3).value().date();

name = cells.item(row, 2).value().bStr();
// KnownAs = cells.item(row, 6).value().bStr();

InvoiceAccount = cells.item(row, 5).value().bStr();
if(InvoiceAccount == "")
InvoiceAccount = int2str(cells.item(row, 5).value().double());

cur = cells.item(row, 7).value().bStr();

Quantity = cells.item(row, 11).value().double();

Amount = cells.item(row, 6).value().double();

IdentificationNumber= cells.item(row, 10).value().bStr();
if(IdentificationNumber == "")
IdentificationNumber = int2str(cells.item(row, 10).value().double());

SourceDocumentLine_loc.recid = SourceDocumentLine_loc.recid;
// select SourceDocumentLine_upd where SourceDocumentLine_upd.recid == SourceDocumentLine_loc.recid;

CustInvoiceTable.OrderAccount = CustomerAccount;
CustInvoiceTable.InvoiceDate = InvoiceDate;
CustInvoiceTable.InvoiceAccount = InvoiceAccount;
CustInvoiceTable.CurrencyCode = cur;
CustInvoiceTable.LanguageId = lang;
//custInvoiceTable.modifiedField(fieldNum(CustInvoiceTable, OrderAccount));
// custInvoiceTable.SourceDocumentHeader = SourceDocumentHeader_loc.RecId;
//custInvoiceTable.SourceDocumentLine = SourceDocumentLine_upd.recid;
CustInvoiceTable.insert();
SourceDocumentLine_loc.recid = SourceDocumentLine_loc.recid - 1;

select CustInvoiceTable_recId order by CustInvoiceTable_recId.createdDateTime desc;// where CustInvoiceTable_recId.SourceDocumentHeader == custInvoiceTable.SourceDocumentHeader;
CustInvoiceline_loc.ParentRecId = CustInvoiceTable_recId.RecId;
CustInvoiceline_loc.Quantity = Quantity;
CustInvoiceline_loc.AmountCur = Amount;
CustInvoiceline_loc.insert();

////////

SourceDocumentHeader.AccountingStatus = SourceDocumentAccountingStatus::InProcess;
SourceDocumentHeader.SourceRelationType =1209;
SourceDocumentHeader.TypeEnumName = "SourceDocument_CustomerInvoice";
SourceDocumentHeader.TypeEnumValue = 2;
SourceDocumentHeader.insert();

select SourceDocumentHeader_loc order by SourceDocumentHeader_loc.RecId desc;

SourceDocumentLine.AccountingStatus = SourceDocumentLineAccountingStatus::Draft;
SourceDocumentLine.SourceDocumentHeader = SourceDocumentHeader_loc.RecId;
SourceDocumentLine.SourceRelationType = 1209;
SourceDocumentLine.TypeEnumName = "SourceDocumentLine_CustomerInvoice";
SourceDocumentLine.TypeEnumValue =4;
SourceDocumentLine.insert();

select SourceDocumentLine_loc order by SourceDocumentLine_loc.recid desc;

select forUpdate CustInvoiceTable order by CustInvoiceTable.createdDateTime desc;// where CustInvoiceTable.SourceDocumentHeader == 0;
if(CustInvoiceTable)
{
CustInvoiceTable.SourceDocumentHeader = SourceDocumentHeader_loc.RecId;
CustInvoiceTable.SourceDocumentLine = SourceDocumentLine_loc.RecId;
CustInvoiceTable.update();

}

type = cells.item(row+1, 1).value().variantType();
info("Record inserted");
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
ttsCommit;

}

Import and create GL transactions (Ledger and Project type) into AX 2012 using x++ code - csv file

        #public boolean getFromDialog()
        #{
        #    filename                = dialogFilename.value();
        #
        #    return true;
        #}