AX Integration, Get data from SQL db to AX

AX Integration, Get data from SQL db to AX

//This class created by Ki  11-June-19 for PSA-AX integration
class PSA_ProjectMasterInboundProcess extends RunBaseBatch
{
    System.Data.OleDb.OleDbConnection objConn;
    System.Data.OleDb.OleDbCommand cmdSelect,cmdUpdate;
    System.Data.OleDb.OleDbDataReader reader,readerUpdate;
    container resultCon;
    str connectStr;
    ProjParameters projParameters;
    Map dimAttributeMap;
    ProjId  projID;
    Name    projName;

}

.............

public boolean canGoBatch()
{
    return true;

}
..........>

protected boolean canGoBatchJournal()
{
    return true;

}

........................>

public void createProject(container _c)
{
    boolean ret = true;
    ProjTable           newProjTable;
    ProjId              projIdNumSeq,projIdLoc;
    Name                projNameLoc;
    str                 projGroupLoc,projContractLoc,projTypeLoc,projStatusLoc,buLoc,deptLoc,custIdLoc,ccLoc,purposeLoc;
    int                 projTypeValue,projStatusValue;
    ProjTable           projTableLoc;
    Description         guidLoc;

    container           ledgerdimensionheader;
    DimensionDefault    dimensiondefaultheader;
    STG_ProjMaster  projMasterLoc;
    Struct structHdr = new Struct();

    NumberSeq projNumberSeq;
    CompanyInfo companyInfo = companyInfo::find();

    ;
    projNameLoc     = conpeek(_c, 1);
    projGroupLoc    = conpeek(_c, 2);
    projContractLoc = conpeek(_c, 3);
    projTypeLoc     = conpeek(_c, 4);
    projStatusLoc   = conpeek(_c, 5);
    buLoc           = conpeek(_c, 6);
    deptLoc         = conpeek(_c, 7);
    custIdLoc       = conpeek(_c, 8);
    ccLoc           = conpeek(_c, 9);
    purposeLoc      = conpeek(_c, 10);
    guidLoc         = conpeek(_c, 11);
    projIdLoc      = conpeek(_c, 12);


    projTableLoc = ProjTable::find(projIdLoc);
    if(!projTableLoc.ProjId)
    {
        projTypeValue = str2int(projTypeLoc);
        projStatusValue = str2int(projStatusLoc);

        projNumberSeq = NumberSeq::newGetNum(ProjParameters::numRefProjId(), true);

        if (projNumberSeq)
        {
            projIdNumSeq    = projNumberSeq.num();
        }
        projID                  = projIdNumSeq;
        newProjTable.ProjId     = projIdNumSeq;
        newProjTable.Name       = projNameLoc;
        newProjTable.ProjGroupId = projGroupLoc;
        newProjTable.ProjInvoiceProjId = projContractLoc;
        newProjTable.Type       = projTypeValue;
        newProjTable.Status     = projStatusValue;
        newProjTable.CustAccount = custIdLoc;

        if (buLoc && deptLoc && custIdLoc && ccLoc && purposeLoc)
        {
            structHdr.add('BusinessUnit', buLoc);
            structHdr.add('Department', deptLoc);
            structHdr.add('Customer', custIdLoc);
            structHdr.add('CostCenter', ccLoc);
            structHdr.add('Purpose', purposeLoc);
            structHdr.add('Project', projID);


            ledgerDimensionHeader += structHdr.fields();
            ledgerDimensionHeader += structHdr.fieldName(1);
            ledgerDimensionHeader += structHdr.valueIndex(1);
            ledgerDimensionHeader += structHdr.fieldName(2);
            ledgerDimensionHeader += structHdr.valueIndex(2);
            ledgerDimensionHeader += structHdr.fieldName(3);
            ledgerDimensionHeader += structHdr.valueIndex(3);
            ledgerDimensionHeader += structHdr.fieldName(4);
            ledgerDimensionHeader += structHdr.valueIndex(4);
            ledgerDimensionHeader += structHdr.fieldName(5);
            ledgerDimensionHeader += structHdr.valueIndex(5);
            ledgerDimensionHeader += structHdr.fieldName(6);
            ledgerDimensionHeader += structHdr.valueIndex(6);

            dimensionDefaultHeader = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimensionHeader);
        }

        newProjTable.DefaultDimension = dimensionDefaultHeader;
        newProjTable.insert();

        select forUpdate projMasterLoc where projMasterLoc.GUID == guidLoc;
        projMasterLoc.ProjId = newProjTable.ProjId;//projMasterLoc.ProjId = projID;
        projMasterLoc.update();
    }
    else
    {
        ttsBegin;
        select forUpdate newProjTable where newProjTable.ProjId ==   projTableLoc.ProjId;
        projTypeValue = str2int(projTypeLoc);
        projStatusValue = str2int(projStatusLoc);

        newProjTable.ProjGroupId = projGroupLoc;
        newProjTable.ProjInvoiceProjId = projContractLoc;
        newProjTable.Type       = projTypeValue;
        newProjTable.Status     = projStatusValue;
        newProjTable.CustAccount = custIdLoc;
        newProjTable.DefaultDimension = dimensionDefaultHeader;
        newProjTable.update();
        ttsCommit;

    }

}

...............................>

public Str1260 formatString(Str1260 _msg)
{
    str replace(str _source, str _what, str _with)
    {
        int found = 0;
        str target = _source;
        do
        {
            found = strscan(target, _what, found, strlen(target));
            if (found != 0)
            {
                target = strdel(target, found, strlen(_what));
                target = strins(target, _with, found);
                found += strlen(_with);
            }
        } while (found != 0);
        return target;
    }
    ;
    _msg = replace(_msg, '\n', '');
    _msg = replace(_msg, '\r', '');
    _msg = replace(_msg, '\t', '');
    _msg = replace(_msg, '\'', '');
    return _msg;

}

.............................>

public container pack()
{
    return conNull();

}

....................>

public container processProjTable(System.Data.OleDb.OleDbDataReader readerProject)
{
    int startLine = infologline(), currentLine, axStatus;
    int             i;
    Name            name;
    ProjId          projIDLoc,psaProjId;
    ProjGroupId     psaProjGroupId;
    CategoryId      psaCategoryId;
    ProjTable       projTable;
    str             projStatus,projType;
    AccountNum      custId;
    Description     psaGuid;

    str             businessUnit, department,msg,purpose,costCenter;
    ProjInvoiceProjId   contractId;
    STG_ProjMaster   projMaster;


    try
    {
        if (!readerProject.IsDBNull(0))
            psaProjGroupId = readerProject.GetString(0);
        else
            psaProjGroupId = "";

        if (!readerProject.IsDBNull(1))
            psaCategoryId = readerProject.GetString(1);
        else
            psaCategoryId = "";

        if (!readerProject.IsDBNull(2))
            contractId = readerProject.GetString(2);
        else
            contractId = "";

        if (!readerProject.IsDBNull(3))
            projType = readerProject.GetString(3);
        else
            projType = "";

        if (!readerProject.IsDBNull(4))
            projStatus = readerProject.GetString(4);
        else
            projStatus = "";

        if (!readerProject.IsDBNull(5))
            businessUnit = readerProject.GetString(5);
        else
            businessUnit = "";

        if (!readerProject.IsDBNull(6))
            department = readerProject.GetString(6);
        else
            department = "";

        if (!readerProject.IsDBNull(7))
            custId = readerProject.GetString(7);
        else
            custId = "";

        if (!readerProject.IsDBNull(8))
            costCenter = readerProject.GetString(8);
        else
            costCenter = "";

        if (!readerProject.IsDBNull(9))
            purpose = readerProject.GetString(9);
        else
            purpose = "";

        if (!readerProject.IsDBNull(10))
            name = readerProject.GetString(10);
        else
            name = "";

        if (!readerProject.IsDBNull(11))
            psaProjId = readerProject.GetString(11);
        else
            psaProjId = "";
        if (!readerProject.IsDBNull(12))
            psaGuid = readerProject.GetString(12);
        else
            psaGuid = "";
        //Insert in AX staging table-start

        if(ALE_STG_ProjMaster::findByGUID(psaGuid))
        {
            ttsBegin;
            select forUpdate projMaster where projMaster.GUID == psaGuid;
            projMaster.Name         = name;
            projMaster.ProjGroupId  = psaProjGroupId;
            projMaster.ProjContractId = contractId;
            projMaster.CategoryId   = psaCategoryId;
            projMaster.ALE_PRojType = any2str(projType);
            projMaster.BusinessUnit = businessUnit;
            projMaster.CostCentre   = costCenter;
            projMaster.Department   = department;
            projMaster.ProjStage    = projStatus;
            projMaster.CustAccount  = custId;
            projMaster.Purpose      = purpose;

            projMaster.update();
            ttsCommit;
        }
        else
        {
            projMaster.clear();
            projMaster.ProjId       = "";
            projMaster.Name         = name;
            projMaster.ProjGroupId  = psaProjGroupId;
            projMaster.ProjContractId = contractId;
            projMaster.CategoryId   = psaCategoryId;
            projMaster.ALE_PRojType = any2str(projType);
            projMaster.BusinessUnit = businessUnit;
            projMaster.CostCentre   = costCenter;
            projMaster.Department   = department;
            projMaster.ProjStage    = projStatus;
            projMaster.CustAccount  = custId;
            projMaster.Purpose      = purpose;
            projMaster.GUID         = psaGuid;

            projMaster.insert();
        }

        //Insert in AX staging table-start

        ttsbegin;

        // Project name
        if (readerProject.IsDBNull(10))
        {
            msg = 'Project name cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        // Project group
        if (readerProject.IsDBNull(0))
        {
            msg = 'Project group cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        // Project category
        if (readerProject.IsDBNull(1))
        {
            msg = 'Project category cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }
        // Project Contract Id
        if (readerProject.IsDBNull(2))
        {
            msg = 'Project Contract Id cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        // Project type
        if (readerProject.IsDBNull(3))
        {
            msg = 'Project type cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        // Project Status
        if (readerProject.IsDBNull(4))
        {
            msg = 'Project Status cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }
        // businessUnit
        if (readerProject.IsDBNull(5))
        {
            msg = 'Business Unit cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }
        // Department
        if (readerProject.IsDBNull(6))
        {
            msg = 'Department cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        // Customer Id
        if (readerProject.IsDBNull(7))
        {
            msg = ' Customer Id cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        // Cost Center
        if (readerProject.IsDBNull(8))
        {
            msg = 'Cost center cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        // Purpose
        if (readerProject.IsDBNull(9))
        {
            msg = 'Purpose cannot be null';
            ttsAbort;
            axStatus = -1;
            return [axStatus, msg];
        }

        //Financial dimension
        if (!readerProject.IsDBNull(5) && !readerProject.IsDBNull(6)
            && !readerProject.IsDBNull(7) && !readerProject.IsDBNull(8) && !readerProject.IsDBNull(9))
        {
            businessUnit = readerProject.GetString(5);
            department = readerProject.GetString(6);
            custId = readerProject.GetString(7);
            costCenter = readerProject.GetString(8);
            purpose = readerProject.GetString(9);

            if (custId == '0' || businessUnit == '0' || department == '0' ||  costCenter == '0' || purpose == '0' )
            {
                msg = 'Financial dimension value 0 is not valid';
                ttsAbort;
                axStatus = -1;
                return [axStatus, msg];
            }

            /*if (!DimensionFinancialTag::findByFinancialTagCategoryAndValue(5637144576,purpose) && purpose)
            {
                msg = strFmt("Business line %1 does not exists in purpose master.",purpose);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];

            }*/

            else if (!OMOperatingUnit::findNumber(businessUnit).OMOperatingUnitType == OMOperatingUnitType::OMBusinessUnit && businessUnit)
            {
                msg = strFmt("Business unit %1 does not exists in Business unit master.",businessUnit);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];
            }
            else if (!OMOperatingUnit::findNumber(department).OMOperatingUnitType == OMOperatingUnitType::OMDepartment && department)
            {
                msg = strFmt("Department %1 does not exists in department master.",department);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];
            }
            else if (!OMOperatingUnit::findNumber(costCenter).OMOperatingUnitType == OMOperatingUnitType::OMCostCenter && costCenter)
            {
                msg = strFmt("Department %1 does not exists in costCenter master.",costCenter);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];
            }

            else if (!ProjCategory::find(psaCategoryId) && psaCategoryId)
            {
                msg = strFmt("Project category ID %1 does not exists in project Category master.",psaCategoryId);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];
            }
            else if (!ProjGroup::find(psaProjGroupId) && psaProjGroupId)
            {
                msg = strFmt("Project group ID %1 does not exists in project group master.",psaProjGroupId);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];
            }
            else if (!ProjInvoiceTable::find(contractId) && contractId)
            {
                msg = strFmt("Project contract ID %1 does not exists in project contract master.",contractId);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];
            }
            else if (!CustTable::find(custId) && custId)
            {
                msg = strFmt("Customer ID %1 does not exists in customer site master.",custId);
                axStatus = -1;
                ttsAbort;
                return [axStatus, msg];
            }
        }
        select forUpdate projTable
            where projTable.Name == name;

        if (!projMaster.ProjId)
        {
            this.createProject([name,psaProjGroupId,contractId,projType,projStatus,businessUnit,department,custId,costCenter,purpose,psaGuid]);
            axStatus = 2;
        }
        else
        {
            this.createProject([name,psaProjGroupId,contractId,projType,projStatus,businessUnit,department,custId,costCenter,purpose,psaGuid,projMaster.ProjId]);
            axStatus = 1;
        }

        msg = strFmt('Project created successfully ');
        ttscommit;

    }
    catch
    {
        ttsAbort;
        axStatus = -1;
        for (currentLine = startLine + 1; currentLine <= infologline(); currentLine++)
        {
            msg += infolog.text(currentLine);
        }
        msg = this.formatString(msg);

        infolog.clear();
    }
    return [axStatus, msg];

}

...............>

public void run()

{

    str datestr;

    //ProjMaster   projMasterUpdate;

    ProcessingStatus status;

    DataAreaId  psaCompanyId;

    string255 err;

    projParameters = ProjParameters::find();

    connectStr = 'Provider=SQLNCLI11;Integrated Security=SSPI;' +'Persist Security Info=False;Initial Catalog=' +

                    projParameters.ALE_PSA_StagingDBName + ';Data Source=' + projParameters.ALE_PSA_StagingDBServer + ';'+ 'User id=xxxxx;Password=xxxxx';

    try

    {

        objConn = new System.Data.OleDb.OleDbConnection(connectStr);

        objConn.Open();

        cmdSelect = objConn.CreateCommand();

        cmdSelect.set_CommandText('SELECT PM.ProjGroupID, PM.ProjCategoryId, PM.ProjContractID, PM.ProjType, PM.ProjStatus, PM.BUSINESSUNIT, PM.DEPARTMENT, PM.Customer, PM.CostCenter, PM.Purpose, PM.ProjName,PM.ProjId,PM.GUID,PM.CompanyId '+

                                    'FROM ProjMaster AS PM '+ 'WHERE PM.STATUS = 0');

        reader = cmdSelect.ExecuteReader();

        while (reader.Read())

        {

            psaCompanyId = reader.GetString(13);

            changeCompany(psaCompanyId)

            resultCon = this.processProjTable(reader);

            projName = reader.GetString(12);

            cmdUpdate = objConn.CreateCommand();

            cmdUpdate.set_CommandText(strFmt('UPDATE ProjMaster SET STATUS = %1,' + 'projId = ' + "'%2'" +', ERRORMESSAGE = ' + "'%3'" + 'WHERE GUID = ' + "'%4'",

                                                conPeek(resultCon, 1),projID, conPeek(resultCon, 2), projName));

            readerUpdate = cmdUpdate.ExecuteReader();

            if (conPeek(resultCon, 1) == 1)

                status = Ale_ProcessingStatus::Processed;

            else if (conPeek(resultCon, 1) == -1)

                status = Ale_ProcessingStatus::Error;

            else

                status = Ale_ProcessingStatus::Unprocessed;

            err = conPeek(resultCon, 2);

            //Update AX staging employee master-start

            //update_recordSet projMasterUpdate

                //setting processingstatus = status,ErrorMsg = err

            //where projMasterUpdate. == emplID

                //&& employeeMasterUpdate.ProcessingStatus == Ale_ProcessingStatus::Unprocessed;

            //Update AX staging employee master-end

        }

    }

    catch(Exception::Error)

    {

        error('An Exception has occurred');

    }

    if(objConn)

    {

        objConn.Close();

    }

}

.............>

public boolean runsImpersonated()

{

    return true;

}

.......................>

private void setDimensionAttribute()

{

   /* dimAttributeMap = new Map(Types::Integer, Types::Record);

    dimAttributeMap.insert(1, dimensionAttribute::findByName('BusinessLine'));

    dimAttributeMap.insert(2, dimensionAttribute::findByName('BusinessUnit'));

    dimAttributeMap.insert(3, dimensionAttribute::findByName('Department'));

    dimAttributeMap.insert(4, dimensionAttribute::findByName('Project'));

    dimAttributeMap.insert(5, dimensionAttribute::findByName('ProjectCategoryItem'));

    dimAttributeMap.insert(6, dimensionAttribute::findByName('Region'));

    dimAttributeMap.insert(7, dimensionAttribute::findByName('Site'));

    dimAttributeMap.insert(8, dimensionAttribute::findByName('Worker'));*/

}

..............>

public boolean unpack(container _packedClass)

{

    return true;

}

...............................>

static ClassDescription description()

{

    return "Project master inbound process";

}

>>>>>>>>>>>>>>>>>>

public static void main(Args args)

{

    ProjectMasterInboundProcess projectMasterInboundProcess = new  ProjectMasterInboundProcess();;

    if (projectMasterInboundProcess.prompt())

    {

        projectMasterInboundProcess.run();

    }

}

Enable button on multi select grid lines

Enable button on multi select grid lines

Open posted Ledger Journals(Expense Journal) from listpage in ax 2012

Open posted Ledger Journals(Expense Journal) from listpage in ax 2012

void clicked()
{
    #define.NewButton('NewButton')
    #define.AllOpenPostedButton('AllOpenPostedField')

    FormRun               formRun;
    FormDataSource        formDataSource;
    QueryBuildDataSource  queryBuildDataSource;
    FormControl           formControlPosted, formControlNew;
    super();

    formRun = new MenuFunction(menuitemDisplayStr(LedgerJournalTable),MenuItemType::Display).create();

    formdatasource = formrun.datasource();
    querybuilddatasource = formdatasource.query().datasourcetable(tablenum(ledgerjournaltable));
    querybuilddatasource.addrange(fieldnum(ledgerjournaltable, ale_projid)).value(projtable.projid);
    querybuilddatasource.addrange(fieldnum(ledgerjournaltable, Posted)).value(SysQuery::value(1));
    formControlPosted = formRun.design().controlName(#AllOpenPostedButton);
    formControlPosted.visible(false);

    formControlNew = formRun.design().controlName(#NewButton);
    formControlNew.visible(false);

    formRun.run();

}

create ledger Dimension for the new values(which are not available in DAVcombination table)

create ledger Dimension for the new values(which are not available in DAVcombination table)

if(ledgerDimensionvalueCombination.recid != 0)
    {
        ledgerJournalTrans.ledgerDimension  = ledgerDimensionvalueCombination.recid;
    }
    else
    {
        recId = DimensionStorage::getDynamicAccount(_projForecastUnion.ProjId, 3, 129);
        davc = DimensionAttributeValueCombination::find(recId);
        ledgerJournalTrans.DefaultDimension = davc.RecId;
    }

Ledger Offset Account Update in Expense journals.

MainAccount must.

ledgerJournalTrans.OffsetLedgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension( projPosting.LedgerDimension,ledgerJournalTrans.DefaultDimension,defaultDimensionView.DefaultDimension,defaultDimension);


////////////////////////////////////////////////////////////////////////////

public static str Tri_WorkerPersonelNo(DimensionDefault _defaultDimension)
{
    DimensionAttributeValueSet           DimensionAttributeValueSet;
    DimensionAttributeValueSetItem       DimensionAttributeValueSetItem;
    DimensionAttributeValue              DimensionAttributeValue;
    DimensionAttribute                   DimensionAttribute;
    Name                                 dimvalue,worker,workername;





     select DimensionAttributeValueSet
            where  DimensionAttributeValueSet.RecId == _defaultDimension
    join RecId, DisplayValue from DimensionAttributeValueSetItem
            where DimensionAttributeValueSetItem.DimensionAttributeValueSet == DimensionAttributeValueSet.RecId
    join RecId from DimensionAttributeValue
            where DimensionAttributeValue.RecId == DimensionAttributeValueSetItem.DimensionAttributeValue
    join RecId, Name from DimensionAttribute
            where DimensionAttribute.RecId == DimensionAttributeValue.DimensionAttribute && DimensionAttribute.Name == "Worker";

    worker = DimensionAttributeValueSetItem.DisplayValue;
    //workername = HcmWorker::findByPersonnelNumber(worker).name();


    return worker;
}
................................................................................................

Create default dimensions

Create default dimensions

private void createDefaultDimensions(ProjForecastUnion _projForecastUnion)
{
    DimensionAttributeValueSetStorage       dimStorage;
    ProjTable                               ProjTable;
    Counter                                 i;
    DimensionAttribute                      dimensionAttribute;
    DimensionAttributeValue                 dimensionAttributeValue;
    DimensionAttributeValueSetItem          dimensionAttributeValueSetItem;
    DimensionAttributeValueSetStorage       valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                        defaultDim;
    container                               conAttribute, conAttributeValue;
    DimensionValue                          BusinessLine, BusinessUnit, Department, Project, ProjectcategoryItem,
                                            Projectpurpose, Purpose, Region, Site, Vehicle, Worker;

    dimStorage = DimensionAttributeValueSetStorage::find(ProjForecastCost::find(_projForecastUnion.TransId).DefaultDimension);

    for (i=0 ; i<= dimStorage.elements() ; i++)
    {
        if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "BusinessLine")
        {
            BusinessLine = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "BusinessUnit")
        {
            BusinessUnit = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Department")
        {
            Department = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Project")
        {
            Project = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "ProjectcategoryItem")
        {
            ProjectcategoryItem = dimStorage.getDisplayValueByIndex(i);
            if(ProjectcategoryItem == '')
            {
                ProjectcategoryItem = _projForecastUnion.CategoryId;
            }
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Projectpurpose")
        {
            Projectpurpose = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Purpose")
        {
            Purpose = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Region")
        {
            Region = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Site")
        {
            Site = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Vehicle")
        {
            Vehicle = dimStorage.getDisplayValueByIndex(i);
        }
        else if (DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == "Worker")
        {
            Worker = dimStorage.getDisplayValueByIndex(i);
        }

    }


    conAttribute        = conNull();
    conAttributeValue   = conNull();

    conAttribute        = conIns(conAttribute,1,'BusinessLine');
    conAttributeValue   = conIns(conAttributeValue, 1, BusinessLine);

    conAttribute        = conIns(conAttribute,2,'BusinessUnit');
    conAttributeValue   = conIns(conAttributeValue, 2, BusinessUnit);

    conAttribute        = conIns(conAttribute,3,'Department');
    conAttributeValue   = conIns(conAttributeValue, 3, Department);

    conAttribute        = conIns(conAttribute,4,'Project');
    conAttributeValue   = conIns(conAttributeValue, 4, Project);

    conAttribute        = conIns(conAttribute,5,'ProjectcategoryItem');
    conAttributeValue   = conIns(conAttributeValue, 5, ProjectcategoryItem);

    conAttribute        = conIns(conAttribute,6,'Projectpurpose');
    conAttributeValue   = conIns(conAttributeValue, 6, Projectpurpose);

    conAttribute        = conIns(conAttribute,7,'Purpose');
    conAttributeValue   = conIns(conAttributeValue, 7, Purpose);

    conAttribute        = conIns(conAttribute,8,'Region');
    conAttributeValue   = conIns(conAttributeValue, 8, Region);

    conAttribute        = conIns(conAttribute,9,'Site');
    conAttributeValue   = conIns(conAttributeValue, 9, Site);

    conAttribute        = conIns(conAttribute,10,'Vehicle');
    conAttributeValue   = conIns(conAttributeValue, 10, Vehicle);

    conAttribute        = conIns(conAttribute,11,'Worker');
    conAttributeValue   = conIns(conAttributeValue, 11, Worker);

    for (i = 1; i <= conLen(conAttribute); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttribute,i));
        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }
        if (conPeek(conAttributeValue,i) != "")
        {
            dimensionAttributeValue =  dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,conPeek(conAttributeValue,i),false,true);
            valueSetStorage.addItem(dimensionAttributeValue);
        }

        defaultDimension = valueSetStorage.save();
    }

}

Generate expense Journal Lines in Proj Module

Generate expense Journal Lines in Proj Module

class ALE_GenerateExpenseJournalLines extends RunBase
{
    int                     recordsCount;
    TransDate               fromDate,toDate;
    ProjId                  projId;
    Name                    peroid;
    LedgerJournalId         journalId,journalIdLoc;


    DialogField dlgPeriod;
    DialogField dlgProjId;

    #define.CurrentVersion(1)
    #localmacro.CurrentList
    peroid,
    projId
    #endmacro
}


//////////////////////////////////////////////////


public static void main(Args args)
{
    JournalId           journalId;
    LedgerJournalTable  ledgerJournalTable;
    ALE_GenerateExpenseJournalLines generateExpenseJournalLines = new ALE_GenerateExpenseJournalLines();
    ledgerJournalTable = args.record();
    journalId = ledgerJournalTable.JournalNum;
    generateExpenseJournalLines.getJournalId(journalId);
    if(generateExpenseJournalLines.prompt())
    {
        generateExpenseJournalLines.run();
    }
}

.................................................................................

protected Object dialog()
{
    Dialog dialog;
    FormStringControl           controlPeriodName;

    dialog = super();
    dialog.caption('Generate Expense Journal lines');

    dlgPeriod = dialog.addFieldValue(extendedTypeStr(Name),peroid);
    dlgProjId = dialog.addFieldValue(extendedTypeStr(ProjId),projId);

    controlPeriodName = dlgPeriod.control();

    controlPeriodName.registerOverrideMethod(methodStr(FormStringControl, lookup),methodStr(ALE_GenerateJournalLines,lookupPeriodName), this);
    return dialog;
}

.................................................................................................

public boolean getFromDialog()
{
    boolean ret;

    ret = super();
    peroid = dlgPeriod.value();
    projId = dlgProjId.value();

    return ret;
}
...............................................................................................

public void getJournalId(JournalId journalIdB)
{
    journalId = journalIdB;
}

....................................................................

public void lookupPeriodName(FormStringControl _control)
{
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource;
    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tableNum(Ale_PeriodMaster),_control);

    sysTableLookup.addLookupField(fieldNum(Ale_PeriodMaster, Name));
    queryBuildDataSource = query.addDataSource(tableNum(Ale_PeriodMaster));
    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();
}

...............................................................................

public container pack()
{
    return [#CurrentVersion,#CurrentList];
}

...........................................................................

public void run()
{
    ProjForecastUnion           projForecastUnion;
    LedgerJournalTable          ledgerJournalTableLoc;
    Ale_VehicleUploader         vehicleUploader;
    Ale_PeriodMaster            periodMaster;
    TransDate                   periodFromDate,periodToDate;

    ALE_GenerateExpenseJournalLines generareJournalLines = new ALE_GenerateExpenseJournalLines();

    journalIdLoc    = journalId;
    select StartDate,Enddate from periodMaster where periodMaster.Name == peroid;
    periodFromDate  = periodMaster.StartDate;
    periodToDate    = periodMaster.Enddate;

    try
    {
        ttsBegin;
        ledgerJournalTableLoc = LedgerJournalTable::find(journalIdLoc);

        if(LedgerJournalName::find(LedgerJournalTable::find(journalIdLoc).JournalName).Ale_VehiclePrice != NoYes::Yes)
        {
            while select projForecastUnion group by ProjId,CategoryId,Ale_Period where projForecastUnion.ProjId == projId
                && projForecastUnion.Ale_Period == peroid
                && projForecastUnion.ProjTransType == ProjTransType::Cost
            {
                recordsCount++;
                generareJournalLines.createExpenseJournals(projForecastUnion,journalIdLoc);
            }
        }
     
        info(strFmt('%1 journals has been created',recordsCount));
        ttsCommit;
    }
    catch(Exception::Deadlock)
    {
        error('Caught an exception');
        retry;
    }

}


..............................................................................


public boolean unpack(container packedClass)
{
    Integer version = conPeek(packedClass,1);
    switch (version)
    {
        case #CurrentVersion :
            [version,#CurrentList] = packedClass;
        break;
        default :
        return false;
    }

    return true;
}


......................................................................./////////////////////////???????????????????

private void createExpenseJournals(ProjForecastUnion    _projForecastUnion,LedgerJournalId         _journalId)
{
    ProjPosting             projPosting;
    ProjSalesAmount         salesAmount;
    int64                   recId,ledgerDim;
    AmountMST               expenseAssesableValue;

    //ProjForecastUnion       projForecastUnionTransId;
    AxLedgerJournalTable    axLedgerJournalTable = new AxLedgerJournalTable();
    AxLedgerJournalTrans    axLedgerJournalTrans = new AxLedgerJournalTrans();
    ProjForecastUnion       projForecastUnionLoc,projForecastUnionCat;
    ProjBudgetLineDetail    projBudgetLineDetail;
    ProjForecastCost        projForecastCost;
    LedgerJournalTable      ledgerJournalTable,ledgerJournalTableLoc;
    LedgerJournalTrans      ledgerJournalTrans;
    NumberSequenceTable     numberSequenceTable;
    LedgerJournalTrans_Project  ledgerJournalTrans_Project;
    LedgerJournalTrans_ProjectTaxExtensionIN    ledgerJournalTrans_ProjectTaxExtensionIN;

    DefaultDimensionView                defaultDimensionView;
    DimensionAttributeValueCombination  ledgerDimensionvalueCombination;
    DimensionAttributeValueCombination  davc;
    ProjTable   projTable;
    projForecastUnionLoc.clear();
    select Worker,CurrencyId,Qty,LinePropertyId,TransId from projForecastUnionLoc where projForecastUnionLoc.ProjId == _projForecastUnion.ProjId
        && projForecastUnionLoc.CategoryId == _projForecastUnion.CategoryId
        && projForecastUnionLoc.Ale_PositionId == _projForecastUnion.Ale_PositionId
        && projForecastUnionLoc.Ale_Period == _projForecastUnion.Ale_Period;

    ledgerJournalTable = LedgerJournalTable::find(_journalId);
    ledgerJournalTrans.JournalNum = axLedgerJournalTable.LedgerJournalTable().JournalNum;
    //ledgerJournalTrans.DefaultDimension = DimensionDefault;
    ledgerJournalTrans.AccountType = LedgerJournalACTypE::Project;
    numberSequenceTable = NumberSequenceTable::find(LedgerJournalName::find(ledgerJournalTable.JournalName).NumberSequenceTable);
    ledgerJournalTrans.Voucher = NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence).voucher();

    select firstonly ProjId from projTable join RecId from ledgerDimensionvalueCombination where ledgerDimensionvalueCombination.DisplayValue == projTable.ProjId
        && projTable.ProjId == _projForecastUnion.ProjId;

    select LedgerDimension from projPosting where projPosting.ProjCategoryRelation == _projForecastUnion.CategoryId
        && projPosting.CategoryCode == TableGroupAll::Table;

    select DefaultDimension from defaultDimensionView where defaultDimensionView.DisplayValue == HcmWorker::find(ProjForecastCost::find(projForecastUnionLoc.TransId).Worker).PersonnelNumber;

    ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
    ledgerJournalTrans.Company = curext();
    ledgerJournalTrans.CurrencyCode = projForecastUnionLoc.CurrencyId;//ProjTable::find(_projForecastUnion.ProjId).DefaultDimension
    ledgerJournalTrans.DefaultDimension = DimensionDefaultingService::serviceMergeDefaultDimensions(ProjForecastCost::find(projForecastUnionLoc.TransId).DefaultDimension,defaultDimensionView.DefaultDimension);

    ledgerJournalTrans.Qty = projForecastUnionLoc.Qty;
    ledgerJournalTrans.TransDate = systemDateGet();
    this.createDefaultDimensions(_projForecastUnion,projForecastUnionLoc);
    if(HcmWorker::find(ProjForecastCost::find(projForecastUnionLoc.TransId).Worker).PersonnelNumber)
    {
        //ledgerDim = DimensionDefaultingService::serviceCreateLedgerDimension(projPosting.LedgerDimension);//,ledgerJournalTrans.DefaultDimension,defaultDimensionView.DefaultDimension
        //ledgerJournalTrans.OffsetLedgerDimension = DimensionDefaultingService::serviceMergeDefaultDimensions(ledgerDim,defaultDimensionView.DefaultDimension);
        ledgerJournalTrans.OffsetLedgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(projPosting.LedgerDimension,ledgerJournalTrans.DefaultDimension,defaultDimension);
    }
    else
    {
        select DefaultDimension from defaultDimensionView where defaultDimensionView.DisplayValue == 'Blank';
        ledgerJournalTrans.OffsetLedgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension( projPosting.LedgerDimension,ledgerJournalTrans.DefaultDimension,defaultDimensionView.DefaultDimension,defaultDimension);
    }

    if(ledgerDimensionvalueCombination.recid != 0)
    {
        ledgerJournalTrans.ledgerDimension  = ledgerDimensionvalueCombination.recid;
    }
    else
    {
        recId = DimensionStorage::getDynamicAccount(_projForecastUnion.ProjId, 3, 129);
        davc = DimensionAttributeValueCombination::find(recId);
        ledgerJournalTrans.DefaultDimension = davc.RecId;
    }

    ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;

    ledgerJournalTrans.insert();
    // Strat by somendra
    if(ledgerJournalTrans.recid)
    {
         select forUpdate ledgerJournalTableLoc where ledgerJournalTableLoc.JournalNum == ledgerJournalTrans.JournalNum;
         ledgerJournalTableLoc.ALE_ProjId = _projForecastUnion.ProjId;//projTable.ProjId;//projId;
         ledgerJournalTableLoc.Ale_Period = _projForecastUnion.Ale_Period;
         ledgerJournalTableLoc.update();
    }
     //End by somendra
    salesAmount = 0;
    while select TransId from projForecastUnionCat where projForecastUnionCat.ProjId == _projForecastUnion.ProjId
        && projForecastUnionCat.CategoryId == _projForecastUnion.CategoryId
        && projForecastUnionCat.Ale_Period == _projForecastUnion.Ale_Period
        && projForecastUnionCat.ProjTransType == ProjTransType::Cost
    {
        //select SalesPrice from projBudgetLineDetail where projBudgetLineDetail.Ale_TransactionID == projForecastUnionCat.TransId; //
        //salesAmount += projBudgetLineDetail.SalesPrice;
        while select SalesPrice from projForecastCost where projForecastCost.TransId == projForecastUnionCat.TransId
        {
            salesAmount += projForecastCost.SalesPrice;
        }
    }
    expenseAssesableValue = 0;
    ledgerJournalTrans_Project.CategoryId = _projForecastUnion.CategoryId;
    ledgerJournalTrans_Project.Qty = projForecastUnionLoc.Qty;
    ledgerJournalTrans_Project.ALE_PeriodName = _projForecastUnion.Ale_Period;
    ledgerJournalTrans_Project.ProjTransDate = systemDateGet();
    ledgerJournalTrans_Project.SalesPrice = salesAmount;
    ledgerJournalTrans_Project.SalesCurrencyId = projForecastUnionLoc.CurrencyId;
    ledgerJournalTrans_Project.PositionId = _projForecastUnion.Ale_PositionId;
    ledgerJournalTrans_Project.LinePropertyId = projForecastUnionLoc.LinePropertyId;
    ledgerJournalTrans_Project.ProjId = _projForecastUnion.ProjId;
    ledgerJournalTrans_Project.RefRecId = ledgerJournalTrans.RecId;
    expenseAssesableValue  = ledgerJournalTrans_Project.Qty * ledgerJournalTrans_Project.SalesPrice;
    ledgerJournalTrans_Project.Worker = ProjForecastCost::find(projForecastUnionLoc.TransId).Worker;
    ledgerJournalTrans_Project.insert();
    ledgerJournalTrans_ProjectTaxExtensionIN.LedgerJournalTrans_Project = ledgerJournalTrans_Project.RecId;
    ledgerJournalTrans_ProjectTaxExtensionIN.AssessableValue = expenseAssesableValue;
    ledgerJournalTrans_ProjectTaxExtensionIN.insert();
    ledgerJournalTrans_Project.clear();
    ledgerJournalTrans.clear();

}


........................................................................................

Number to textINR

Number to textINR

static TempStr numeralsToTxtINR(real _num)
{


int numOfPennies = (decround(frac(_num), 2) * 100) mod 100;
real test = _num - frac(_num);

int numOfTenths;
str 20 ones[19], tenths[9], hundreds, thousands, lakhs, crores;

int64 temp;
str 200 returntxt;
str 200 pennytxt;
int penny;

real modOperator(real a1, real a2)
{
int tmpi;
real tmp1, tmp2;
tmp1 = a1 / a2;
tmpi = real2int(tmp1);
tmp2 = tmpi;
return (tmp1 - tmp2)*a2;
}

real checkPower(real _test, int64 _power)
{
int64 numOfPower;

if (_test >= _power)
{
numOfPower = _test div _power;
if (numOfPower >= 100)
{
temp = numOfPower div 100;
returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;
numOfPower = numOfPower mod 100;
}
if (numOfPower >= 20)
{
temp = numOfPower div 10;
returntxt = returntxt + ' ' + tenths[temp];
numOfPower = numOfPower mod 10;
}
if (numOfPower >= 1)
{
returntxt = returntxt + ' ' + ones[numOfPower];
numOfPower = numOfPower mod 10;
}
switch(_power)
{
case 10000000 :
{
returntxt = returntxt + ' ' + Crores;
_test = modOperator(_test, 10000000);
break;
}
case 100000 :
{
returntxt = returntxt + ' ' + lakhs;
_test = modOperator(_test, 100000);
break;
}
case 1000 :
{
returntxt = returntxt + ' ' + thousands;
_test = modOperator(_test, 1000);
break;
}
case 100 :
{
returntxt = returntxt + ' ' + hundreds;
_test = modOperator(_test, 100);
break;
}
}
}
return _test;
}

#Define.text_1('One')
#Define.text_2('Two')
#Define.text_3('Three')
#Define.text_4('Four')
#Define.text_5('Five')
#Define.text_6('Six')
#Define.text_7('Seven')
#Define.text_8('Eight')
#Define.text_9('Nine')
#Define.text_10('Ten')
#Define.text_11('Eleven')
#Define.text_12('Twelve')
#Define.text_13('Thirteen')
#Define.text_14('Fourteen')
#Define.text_15('Fifteen')
#Define.text_16('Sixteen')
#Define.text_17('Seventeen')
#Define.text_18('Eighteen')
#Define.text_19('Nineteen')
#Define.text_20('Twenty')
#Define.text_30('Thirty')
#Define.text_40('Forty')
#Define.text_50('Fifty')
#Define.text_60('Sixty')
#Define.text_70('Seventy')
#Define.text_80('Eighty')
#Define.text_90('Ninety')
#Define.text_100('Hundred')
#Define.text_1000('Thousand')
#Define.text_100000('Lakh')
#Define.text_10000000('Crore')
#Define.text_and('Rupees and')
#Define.text_paise('Paise Only')
#Define.text_ruppe('Rupees Only')

ones[1] = #text_1;
ones[2] = #text_2;
ones[3] = #text_3;
ones[4] = #text_4;
ones[5] = #text_5;
ones[6] = #text_6;
ones[7] = #text_7;
ones[8] = #text_8;
ones[9] = #text_9;
ones[10] = #text_10;
ones[11] = #text_11;
ones[12] = #text_12;
ones[13] = #text_13;
ones[14] = #text_14;
ones[15] = #text_15;
ones[16] = #text_16;
ones[17] = #text_17;
ones[18] = #text_18;
ones[19] = #text_19;

tenths[1] = 'Not used';
tenths[2] = #text_20;
tenths[3] = #text_30;
tenths[4] = #text_40;
tenths[5] = #text_50;
tenths[6] = #text_60;
tenths[7] = #text_70;
tenths[8] = #text_80;
tenths[9] = #text_90;

hundreds = #text_100;
thousands = #text_1000;
lakhs = #text_100000;
crores = #text_10000000;



test = checkPower(test, 10000000);
test = checkPower(test, 100000);
test = checkPower(test, 1000);
test = checkPower(test, 100);

if (test >= 20)
{
numOfTenths = test div 10;
returntxt = returntxt + ' ' + tenths[numofTenths];
numOfTenths = numOfTenths mod 10;
test = test mod 10;
}
if (test >= 1)
{
numOfTenths = real2int(test);
returntxt = returntxt + ' ' + ones[numOfTenths];
}

if (numOfPennies)
{
if (numOfPennies >= 20)
{
penny = numOfPennies div 10;
pennytxt = tenths[penny];
numOfPennies = numOfPennies mod 10;
}
if (numOfPennies >= 1)
{
pennytxt = pennytxt + ' ' + ones[numOfPennies];
}
returntxt = returntxt + ' ' + #text_and + ' ' + pennytxt + ' ' +#text_paise;
}
else
{
returntxt = returntxt + ' ' + #text_ruppe;
}

return strFmt("%1 %2",returntxt,"******");


}