Wednesday, December 23, 2015

Ms Excel macro to create txt file

Macro to create txt file (flat file). You could change separator as you need. 

Txt creator

Here is the code:

Sub CreateTXT()

    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:\AX\" & ActiveSheet.Name & ".txt"
    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 & ";"  'SEPARATOR !!!
        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







Job for Insert or Update Worker in AX 2012 R3

You should already have Payroll Position Master. And be carefull with date validation in most of HCM and Payroll tables related.

static void Import_Worker_InsertUpdate(Args _args)
{
RecId                   workerRecId,hcmEmploymentRecId;

HcmPersonnelNumberId    personnumx;
date                    datehiringx,datebirthx;
str 20                  namefirtsx,namemidx,namelastx,initialx,educationx,subeducationx;
str 20                  bankx,bankaccx,npx,levelx,updatedatax;
str 1                   genderx;
HcmPersonGender         hcmgenderEnum;
Gender                  genderEnum;

Dialog                  dialog;
DialogField             dialogField,dialogField2,dialogdate,dialogField3,dialogField4;
Filename                filename,filenamex;
str 60                  oldnumx,newnumx,cityx,namex,positionx;
str 100                 addressx;

TextIO                  io;
Container               con;
Container               filterCriteria;

COMVariant cOMVariant;

int i,j,k;
SysOperationProgress simpleProgress;
Numberseq   numberseq;
Filename    filepathz;
Filename    filenamez;
Filename    fileTypez;
str         fileNameString;
CommaTextIo         file;

boolean                                 ret = true;
CompanyInfo                             companyInfo;
HcmEmploymentRecId                      newEmploymentRecId;
ValidFromDateTime                       employmentStartDateTime;
ValidToDateTime                         employmentEndDateTime;

DirPerson                               _dirPerson;
DirPersonName                           _dirPersonName;
DirPartyTable                           _dirPartyTable;
DirPartyLocation                        _dirPartyLocation;

HcmEmploymentType                       hcmEmploymentType = HcmEmploymentType::Employee;
NumberSeq                               numberSeqPersonnelNum;
AxLogisticsPostalAddress                axLogisticsPostalAddress = new AxLogisticsPostalAddress();
AxLogisticsLocation                     axLogisticsLocation;
LogisticsPostalAddress                  logisticsPostalAddress_Default,_logisticsPostalAddress;

HcmWorker                               newHcmWorker,_hcmWorker;
HcmPersonPrivateDetails                 _hcmPersonPrivateDetails0,_hcmPersonPrivateDetails;
HcmPersonEducation                      _hcmPersonEducation;
HcmWorkerBankAccount                    _hcmWorkerBankAccount;
HcmEmploymentEmployee                   _hcmEmploymentEmployee,_hcmEmploymentEmployee0;
HcmPositionDetail                       _hcmPositionDetail;
HcmEmployment                           _hcmEmployment,_hcmEmployment2;
HcmPositionWorkerAssignment             _hcmPositionWorkerAssignment,_hcmPositionWorkerAssignment2;
HcmPersonLaborUnion                     _hcmPersonLaborUnion;
HcmPositionUnionAgreement               _hcmPositionUnionAgreement;

#avifiles
;

Dialog          = new Dialog("Import insert and update worker ("+curext()+")");

dialogField     = dialog.addField(ExtendedTypeStr("FilenameOpen"));

filterCriteria  = ['*.txt','*.txt'];// To filter only TXT files
filterCriteria  = dialog.filenameLookupFilter(filterCriteria);
dialog.run();

if(dialog.run())
fileName        = dialogField.value();

if(!fileName)
{
    Error('File is not selected. Process cancelled!');
    return;
}

filenamex = filename;

[filepathz, filenamez, fileTypez] = fileNameSplit(filenamex);
fileNameString= filenamez + fileTypez;

try //#1
{
    if(dialog.run()) //#2
    {
        file = new CommaTextIo(dialogField.value(), 'r');
        file.inFieldDelimiter(';');

            j=0;
            k=0;
            while((file.status()==IO_Status::Ok)) //#3
            {
                con     = file.read();
                //info(int2str(conlen(con)));

                if (conlen(con) == 19) //#4
                {
                    //*** GetValue
                    personnumx      = conPeek(con, 1);
                    datehiringx     = str2Date(conPeek(con, 2),123);
                    namefirtsx      = conPeek(con, 3);
                    namemidx        = conPeek(con, 4);
                    namelastx       = conPeek(con, 5);
                    datebirthx      = str2Date(conPeek(con, 6),123);
                    genderx         = conPeek(con, 7);
                        switch (genderx)
                        {
                            case 'F':
                                hcmgenderEnum   = HcmPersonGender::Female;
                                genderEnum      = Gender::Female;
                                break;

                            case 'M':
                                hcmgenderEnum = HcmPersonGender::Male;
                                genderEnum      = Gender::Male;
                                break;

                            default:
                                hcmgenderEnum = HcmPersonGender::None;
                                genderEnum      = Gender::Unknown;
                        }
                    initialx          = conPeek(con, 8);
                    positionx         = conPeek(con, 9);
                    addressx          = conPeek(con, 10);
                    cityx             = conPeek(con, 11);

                    companyInfo = CompanyInfo::findByCompany_IN(conPeek(con, 12));
                    educationx        = conPeek(con, 13);
                    subeducationx     = conPeek(con, 14);
                    bankx             = conPeek(con, 15);
                    bankaccx          = conPeek(con, 16);
                    npx               = conPeek(con, 17);
                    levelx            = conPeek(con, 18);
                    updatedatax       = conPeek(con, 19);

                    employmentStartDateTime     = DateTimeUtil::newDateTime(datehiringx,timeNow());
                    employmentEndDateTime       = DateTimeUtil::maxValue();

                    if (!HcmWorker::findByPersonnelNumber(personnumx).RecId)
                    {
                            //{warning(strFmt('Employee exists!  %1',personnumx)); }

                            /*** BEGIN INSERT DATA ***/

                            _dirPersonName.FirstName     = namefirtsx;
                            _dirPersonName.MiddleName    = namemidx;
                            _dirPersonName.LastName      = namelastx;
                            namex                        = namefirtsx+' '+namemidx+' '+namelastx;

                            _dirPartyTable = DirPartyTable::createNew(DirPartyType::Person, namex);

                            _dirPerson                   = DirPerson::find(_dirPartyTable.RecId, true);
                            _dirPerson.Gender            = genderEnum;
                            _dirPerson.Initials          = initialx;
                            _dirPerson.ProfessionalTitle = positionx;
                            _dirPerson.LanguageId        = 'en-us';
                            _dirPerson.NameSequence      = DirNameSequence::find("FirstMiddleLast").RecId;
                            _dirPerson.write();

                            //HcmWorkerTransition::newCreateHcmWorker(_dirPersonName,personnumx,companyInfo.RecId,
                            //                                        hcmEmploymentType,employmentStartDateTime,employmentEndDateTime);

                            _hcmWorker.PersonnelNumber      = personnumx;
                            _hcmWorker.Person               = _dirPerson.RecId;
                            _hcmWorker.insert();

                            newHcmWorker = HcmWorker::findByPersonnelNumber(personnumx);
                            //info(strFmt('0.personnumx %1',personnumx));

                            //info(strFmt('1.newHcmWorker.RecId %1',newHcmWorker.RecId));
                            if(newHcmWorker.RecId != 0)
                            {
                                select * from _hcmEmployment where _hcmEmployment.Worker == newHcmWorker.RecId;

                                // Replace any exisiting employment
                                //info(strFmt('2._hcmEmployment.Worker %1',_hcmEmployment.Worker));
                                if(!_hcmEmployment)
                                {
                                    newEmploymentRecId = HcmWorkerTransition::newCreateHcmEmployment(newHcmWorker.RecId, companyInfo.RecId,
                                                            hcmEmploymentType, employmentStartDateTime, employmentEndDateTime);
                                }
                                else
                                {
                                    delete_from _hcmEmployment2 where _hcmEmployment2.Worker == newHcmWorker.RecId;
                                    newEmploymentRecId = HcmWorkerTransition::newCreateHcmEmployment(newHcmWorker.RecId, companyInfo.RecId,
                                                            hcmEmploymentType, employmentStartDateTime, employmentEndDateTime);
                                }
                            }

                            _hcmPersonPrivateDetails0 = HcmPersonPrivateDetails::findByPerson(newHcmWorker.Person);
                            //info(strFmt('4._hcmPersonPrivateDetails0.RecId %1',_hcmPersonPrivateDetails0.RecId));

                            if(!_hcmPersonPrivateDetails0)
                            {
                                //info('4._hcmPersonPrivateDetails0 - no exist');
                                _hcmPersonPrivateDetails.Person      = newHcmWorker.Person;
                                _hcmPersonPrivateDetails.BirthDate   = datebirthx;
                                _hcmPersonPrivateDetails.Education   = educationx+' / '+levelx;
                                _hcmPersonPrivateDetails.Gender      = hcmgenderEnum;
                                _hcmPersonPrivateDetails.insert();

                            }
                            else
                            {
                                //info('4._hcmPersonPrivateDetails0 - exist');
                                select forupdate _hcmPersonPrivateDetails0;
                                ttsBegin;
                                _hcmPersonPrivateDetails0.BirthDate   = datebirthx;
                                _hcmPersonPrivateDetails0.Gender      = hcmgenderEnum;
                                _hcmPersonPrivateDetails0.update();
                                ttsCommit;
                            }

                            //Create address
                            axLogisticsLocation = new AxLogisticsLocation();
                            axLogisticsLocation.validateInput(true);
                            axLogisticsLocation.parmIsPostalAddress(NoYes::Yes);
                            axLogisticsLocation.parmDescription(namefirtsx
                                                        + (namemidx ? " " + namemidx : "")
                                                        + (namelastx ? " " + namelastx : ""));
                            axLogisticsLocation.save();


                            axLogisticsPostalAddress    = new AxLogisticsPostalAddress();
                            axLogisticsPostalAddress.parmLocation(axLogisticsLocation.parmRecId());
                            axLogisticsPostalAddress.validateInput(true);

                            logisticsPostalAddress_Default.initValue();

                            axLogisticsPostalAddress.parmCountryRegionId(logisticsPostalAddress_Default.CountryRegionId);
                            //axLogisticsPostalAddress.parmZipCode(conpeek(_c, #PostalCode));
                            //axLogisticsPostalAddress.parmZipCodeRecId(LogisticsAddressZipCode::find(conpeek(_c, #PostalCode)).RecId);
                            axLogisticsPostalAddress.parmStreet(addressx);
                            axLogisticsPostalAddress.parmCity(cityx);
                            axLogisticsPostalAddress.parmCityRecId(LogisticsAddresssCity::findCity(cityx,logisticsPostalAddress_Default.CountryRegionId).RecId);
                            axLogisticsPostalAddress.save();

                            DirParty::addLocation(newHcmWorker.Person, axLogisticsLocation.parmRecId(), true, true, true);

                            _hcmPersonEducation.Person = newHcmWorker.Person;
                            _hcmPersonEducation.EducationDiscipline = HcmEducationDiscipline::findByEducationDiscipline(educationx).recid;
                            _hcmPersonEducation.insert();

                            _hcmWorkerBankAccount.Worker     = newHcmWorker.RecId;
                            _hcmWorkerBankAccount.AccountId  = '001';
                            _hcmWorkerBankAccount.AccountNum = bankaccx;
                            _hcmWorkerBankAccount.Name       = namefirtsx+' '+namemidx+' '+namelastx;
                            _hcmWorkerBankAccount.insert();

                            select * from _hcmPositionDetail where _hcmPositionDetail.Description == positionx;
                            if(!_hcmPositionDetail)
                            { error(strFmt('Position name not found ! %1',positionx)); }

                            select * from _hcmPositionWorkerAssignment where _hcmPositionWorkerAssignment.Worker == newHcmWorker.RecId;
                            if(!_hcmPositionWorkerAssignment)
                            {
                                HcmPositionTransition::newCreateHcmPositionWorkerAssignment(_hcmPositionDetail.Position,newHcmWorker.RecId,
                                    employmentStartDateTime,employmentEndDateTime);
                            }
                            else
                            {
                                delete_from _hcmPositionWorkerAssignment2 where _hcmPositionWorkerAssignment2.Worker == newHcmWorker.RecId;
                                HcmPositionTransition::newCreateHcmPositionWorkerAssignment(_hcmPositionDetail.Position,newHcmWorker.RecId,
                                    employmentStartDateTime,employmentEndDateTime);
                            }

                            If(!HcmEmploymentEmployee::findByEmployment(newEmploymentRecId))
                            {
                                _hcmEmploymentEmployee.initValue();
                                _hcmEmploymentEmployee.Employment       = newEmploymentRecId;
                                _hcmEmploymentEmployee.ValidFrom        = employmentStartDateTime;
                                _hcmEmploymentEmployee.ValidTo          = employmentEndDateTime;
                                _hcmEmploymentEmployee.IncomeTaxCode    = HcmIncomeTaxCode::findByIncomeTaxCode(npx).RecId;
                                _hcmEmploymentEmployee.insert();
                            }

                            _hcmPersonLaborUnion.initValue();
                            _hcmPersonLaborUnion.Person     = newHcmWorker.Person;
                            _hcmPersonLaborUnion.LaborUnion = HcmUnions::findByUnion('DEMOCO').RecId;
                            _hcmPersonLaborUnion.StartDate = datehiringx;
                            _hcmPersonLaborUnion.insert();

                            j++;
                            /*** END ***/
                   } // If NOT EXIST
                   else
                   {
                        if(updatedatax == 'UPDATE')
                        {
                            // If EXIST

                            newHcmWorker = HcmWorker::findByPersonnelNumber(personnumx);

                            delete_from _hcmEmployment2 where _hcmEmployment2.Worker == newHcmWorker.RecId;
                            newEmploymentRecId = HcmWorkerTransition::newCreateHcmEmployment(newHcmWorker.RecId, companyInfo.RecId,
                                                    hcmEmploymentType, employmentStartDateTime, employmentEndDateTime);

                            _hcmPersonPrivateDetails0 = HcmPersonPrivateDetails::findByPerson(newHcmWorker.Person);

                            if(!_hcmPersonPrivateDetails0)
                            {
                                //info('4._hcmPersonPrivateDetails0 - no exist');
                                _hcmPersonPrivateDetails.Person      = newHcmWorker.Person;
                                _hcmPersonPrivateDetails.BirthDate   = datebirthx;
                                _hcmPersonPrivateDetails.Education   = educationx+' / '+levelx;
                                _hcmPersonPrivateDetails.Gender      = hcmgenderEnum;
                                _hcmPersonPrivateDetails.insert();
                            }
                            else
                            {
                                //info('4._hcmPersonPrivateDetails0 - exist');
                                select forupdate _hcmPersonPrivateDetails0;
                                ttsBegin;
                                _hcmPersonPrivateDetails0.BirthDate   = datebirthx;
                                _hcmPersonPrivateDetails0.Gender      = hcmgenderEnum;
                                _hcmPersonPrivateDetails0.update();
                                ttsCommit;
                            }

                            select * from _dirPartyLocation where _dirPartyLocation.Party == newHcmWorker.Person;

                            select forUpdate _logisticsPostalAddress
                                where _logisticsPostalAddress.Location == _dirPartyLocation.Location;
                                if(_logisticsPostalAddress)
                                {
                                     logisticsPostalAddress_Default.initValue();
                                     ttsBegin;
                                         _logisticsPostalAddress.Street  = addressx;
                                         _logisticsPostalAddress.City    = cityx;
                                         _logisticsPostalAddress.CityRecId = LogisticsAddresssCity::findCity(cityx,logisticsPostalAddress_Default.CountryRegionId).RecId;
                                         _logisticsPostalAddress.Address = addressx+' '+cityx;
                                         _logisticsPostalAddress.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
                                        _logisticsPostalAddress.update();
                                    ttsCommit;
                                }

                                select forUpdate _hcmPersonEducation where _hcmPersonEducation.Person == newHcmWorker.Person;
                                if(_hcmPersonEducation)
                                {
                                    ttsBegin;
                                    _hcmPersonEducation.EducationDiscipline = HcmEducationDiscipline::findByEducationDiscipline(educationx).recid;
                                    _hcmPersonEducation.update();
                                    ttsCommit;
                                }

                                select forUpdate _hcmWorkerBankAccount where _hcmWorkerBankAccount.Worker == newHcmWorker.RecId;
                                if(_hcmWorkerBankAccount)
                                {
                                    ttsBegin;
                                    _hcmWorkerBankAccount.AccountId  = '001';
                                    _hcmWorkerBankAccount.AccountNum = bankaccx;
                                    _hcmWorkerBankAccount.Name       = namefirtsx+' '+namemidx+' '+namelastx;
                                    _hcmWorkerBankAccount.update();
                                    ttsCommit;
                                }
                                select * from _hcmPositionDetail where _hcmPositionDetail.Description == positionx;

                                select * from _hcmPositionWorkerAssignment where _hcmPositionWorkerAssignment.Worker == newHcmWorker.RecId;
                                if(!_hcmPositionWorkerAssignment)
                                {
                                    HcmPositionTransition::newCreateHcmPositionWorkerAssignment(_hcmPositionDetail.Position,newHcmWorker.RecId,
                                        employmentStartDateTime,employmentEndDateTime);
                                }
                                else
                                {
                                    delete_from _hcmPositionWorkerAssignment2 where _hcmPositionWorkerAssignment2.Worker == newHcmWorker.RecId;
                                    HcmPositionTransition::newCreateHcmPositionWorkerAssignment(_hcmPositionDetail.Position,newHcmWorker.RecId,
                                        employmentStartDateTime,employmentEndDateTime);
                                }

                                If(!HcmEmploymentEmployee::findByEmployment(newEmploymentRecId))
                                {
                                    _hcmEmploymentEmployee.initValue();
                                    _hcmEmploymentEmployee.Employment       = newEmploymentRecId;
                                    _hcmEmploymentEmployee.ValidFrom        = employmentStartDateTime;
                                    _hcmEmploymentEmployee.ValidTo          = employmentEndDateTime;
                                    _hcmEmploymentEmployee.IncomeTaxCode    = HcmIncomeTaxCode::findByIncomeTaxCode(npx).RecId;
                                    _hcmEmploymentEmployee.insert();
                                }
                                else
                                {    select forUpdate _hcmEmploymentEmployee where _hcmEmploymentEmployee.Employment == newEmploymentRecId;
                                     ttsBegin;
                                    _hcmEmploymentEmployee.Employment       = newEmploymentRecId;
                                    _hcmEmploymentEmployee.ValidFrom        = employmentStartDateTime;
                                    _hcmEmploymentEmployee.ValidTo          = employmentEndDateTime;
                                    _hcmEmploymentEmployee.IncomeTaxCode    = HcmIncomeTaxCode::findByIncomeTaxCode(npx).RecId;
                                    _hcmEmploymentEmployee.update();
                                    ttsCommit;
                                }
                                k++;
                        } //if(updatedatax == 'UPDATE')
                   } // If EXIST
                } // #4 if (conlen(con)
            }// #3 while((file.status()

    }// #2 if(dialog.run())
info(strfmt('Proces insert %1, update %2',j,k));
}// #1 try
catch
{
    error (strFmt('Process Cancelled'));
}
}

Tuesday, July 28, 2015

Ambil Nama File dari File yang dipilih dari Dialog Box - AX 2012

static void Test_GetFileName(Args _args)
{
    container            segments, segmentReset;
    str                  annotatedFormat, format;

    CommaTextIo           file;
    container             rec,filterCriteria;
    Filename              filename,filenamex;

    int                 sep,i;
    Dialog              d;
    DialogField         df1;

    Filename filepathz;
    Filename filenamez;
    Filename fileTypez;
    str fileNameString;
    ;
    d = new Dialog("Import Item Price Discount");
    df1 = d.addField(ExtendedTypeStr("FilenameOpen"));
    filterCriteria  = ['*.txt','*.txt'];//// To filter only TXT files
    filterCriteria  = d.filenameLookupFilter(filterCriteria);
 

    d.run();

    if(d.run())
    fileName        = df1.value();

    if(!fileName)
    {
        return;
    }

    filenamex = filename;

    [filepathz, filenamez, fileTypez] = fileNameSplit(filenamex);
    fileNameString= filenamez + fileTypez;

    info(strFmt('Full path: %1',filenamex));
    info(strFmt('File name: %1',fileNameString));

}

Cek Product yang sudah di-Release di Company yang berbeda - AX 2012

static void Test_Product_Find_CrossCompany(Args _args)
{
    InventTable             _inventTable;
    ItemId                  itemidx;
    SelectableDataArea      dataAreaId1,dataAreaId2;
    Dialog                              d;
    DialogField                         df1;
    CommaTextIo                         file;
    container                           rec;

    ;

    //info('Current company '+curext());

    dataAreaId1 = curext();

    d = new Dialog(strFmt("Find Exist Product",curext()));
    df1 = d.addField(ExtendedTypeStr("ProductNumber"));

    itemidx = df1.value();

    if (d.run())
    {
        if(!df1.value())
        {return;}

            changeCompany('CMP1')
            {
                if(InventTable::exist(df1.value())==true)
                { info('Exist in: '+curext()+', ItemGroupId: '
                  +InventItemGroupItem::findByItemIdLegalEntity(df1.value(),curext()).ItemGroupId); }
                else
                { info('NOT Exist in '+curext()); }
            }

            changeCompany('CMP2')
            {
                if(InventTable::exist(df1.value())==true)
                { info('Exist in: '+curext()+', ItemGroupId: '
                  +InventItemGroupItem::findByItemIdLegalEntity(df1.value(),curext()).ItemGroupId); }
                else
                { info('NOT Exist in '+curext()); }
            }

            changeCompany('CMP3')
            {
                if(InventTable::exist(df1.value())==true)
                { info('Exist in: '+curext()+', ItemGroupId: '
                  +InventItemGroupItem::findByItemIdLegalEntity(df1.value(),curext()).ItemGroupId); }
                else
                { info('NOT Exist in '+curext()); }
            }
    }

}