Tax information in hours journal

Tax information in hours journal

/// <summary>
/// Checks and Inserts the current record into the <c>ProjJournalTransTaxExtensionIN</c> table.
/// </summary>
public void insert(boolean  _creditnNote = false)
{
    ProjJournalTrans    projJournalTrans;
    DirPartyLocation    dirPartyLocation;
    CustTable           custTable;
    // Start added by kiran
    CompanyInfo                 companyInfo;
    Dirpartytable               dirpartytable;
    DirPartyLocation            dirPartyLocationLoc;
    LogisticsLocation           logisticsLocation;
    TaxInformation_IN           taxInformation_IN;
    // End added by kiran Tax information update

    if (this.projJournalTrans)
    {
        select firstOnly projid from projJournalTrans
            where projJournalTrans.RecId == this.ProjJournalTrans;

        custTable                   = CustTable::find(ProjTable::find(ProjJournalTrans.ProjId).CustAccount);
        dirPartyLocation            = DirPartyLocation::findPrimaryPartyLocation(custTable.Party);
        this.CustomerLocation       = LogisticsLocation::find(dirPartyLocation.Location).RecId;
        this.CustomerTaxInformation = TaxInformation_IN::findDefaultbyLocation(this.CustomerLocation).RecId;
        // Start added by kiran 28 Mar 2019 - set Assessable value
        select PartyNumber from companyInfo where companyInfo.DataArea == curext()
                join dirpartytable where dirpartytable.PartyNumber == companyInfo.PartyNumber
                    join dirPartyLocationLoc where dirPartyLocationLoc.Party == dirpartytable.RecId
                        join logisticsLocation where logisticsLocation.RecId == dirPartyLocationLoc.Location
                            join taxInformation_IN where taxInformation_IN.RegistrationLocation == logisticsLocation.RecId
                                && taxInformation_IN.IsPrimary == NoYes::Yes;
        this.TaxInformation = taxInformation_IN.RecId;// TaxRegistrationNumbers_IN::find(taxInformation_IN.GSTIN).RegistrationNumber;
        this.CompanyLocation = logisticsLocation.RecId;

        this.ServiceAccountingCodeTable = ServiceAccountingCodeTable_IN::find(ProjCategory::find(projJournalTrans.CategoryId).ServiceAccountingCodeTable_IN).RecId;
        if (_creditnNote == false)
        {
            if(ProjJournalName::find(ProjJournalTable::find(projJournalTrans.JournalId).JournalNameId).Ale_SalesPrice == NoYes::Yes || ProjJournalName::find(ProjJournalTable::find(projJournalTrans.JournalId).JournalNameId).Ale_VehiclePrice == NoYes::Yes)
            {
                this.AssessableValue        = projJournalTrans.Qty * projJournalTrans.SalesPrice;
            }
            else
            {
                this.AssessableValue        = projJournalTrans.Qty * projJournalTrans.CostPrice;
            }
        }
        // End added by kiran 28 Mar 2019

        super();

    }
}

Export voucher transactions/ split dimensions query

Query

class ALE_ExportVoucherTransToExcel extends RunBase
{
    QueryRun qr;

    DialogField dlgFromDate,dlgToDate;
    TransDate fromDate, toDate;

    #define.CurrentVersion(1)
    #localmacro.CurrentList

    #endmacro
}

......................................................................
public boolean canGoBatch()
{
    return true;
}

...........................................................
protected boolean canGoBatchJournal()
{
    return true;
}
/////////////////////////////////////////////

protected Object dialog()
{
    Dialog dialog;

    dialog = super();
    dialog.caption('Export voucher transactions to excel');

    return dialog;
}
--------------------------------------------------------------------

public boolean getFromDialog()
{
    boolean ret;

    ret = super();

    return ret;
}
--------------------------------------------------------------------
public void initParmDefault()
{
    Query query = new Query();
    QueryBuildRange queryBuildRange;
    QueryBuildDataSource queryBuildDataSource;
    ;
    queryBuildDataSource = query.addDataSource(tableNum(GeneralJournalEntry));
    queryBuildRange = queryBuildDataSource.addRange(FieldNum(GeneralJournalEntry,AccountingDate));

    qr = new SysQueryRun(query);

    super();
}
----------------------------------------------------------------------
public container pack()
{
    container pack = conNull();

    if (qr)
    {
        pack = qr.pack();
    }
    return [#CurrentVersion] + [pack];
}
-----------------------------------------------------------
public QueryRun queryRun()
{
    return qr;
}
--------------------------------------------------------
public void run()
{
    #AviFiles
    Args                                args;
    FormRun                             formRun;
    DimensionAttributeValueSetStorage    dimStorage;
    Counter                              i;
    DimensionAttributeLevelValueAllView dimAttrView; //View that will display all values for ledger dimensions
    DimensionAttribute                  dimAttr;
    GeneralJournalEntry                 generalJournalEntry;
    GeneralJournalEntry                 generalJournalEntryExcel;
    GeneralJournalAccountEntry          generalJournalAccountEntry;
    ALE_GeneralJournalAccountEntryExcel generalJournalAccountEntryExcel;
    DimensionValue                      bl,dept,project,worker,vehicle;


    SysOperationProgress    progressBar = new SysOperationProgress();
    args = new Args();

    delete_from generalJournalAccountEntryExcel;
    startLengthyOperation();
    while (qr.next())
    {
        generalJournalEntry = qr.get(tableNum(GeneralJournalEntry));
        try
        {
            while select generalJournalAccountEntry
                where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
            {
                bl = '';
                dept = '';
                project = '';
                vehicle ='';
                worker = '';

                while select DisplayValue from dimAttrView
                    where dimAttrView.ValueCombinationRecId == generalJournalAccountEntry.LedgerDimension
                    join BackingEntityType,Name from dimAttr
                        where dimAttr.RecId == dimAttrView.DimensionAttribute
                {
                    switch (dimAttr.Name)
                    {
                        case 'BusinessLine':
                            bl = dimAttrView.DisplayValue;
                            break;

                        case 'Department':
                            dept =  dimAttrView.DisplayValue;
                            break;

                        case 'Project':
                            project = dimAttrView.DisplayValue;
                            break;

                        case 'Vehicle':
                            vehicle = dimAttrView.DisplayValue;
                            break;

                        case 'Worker':
                            worker =  dimAttrView.DisplayValue;
                            break;
                    }
                }

                generalJournalAccountEntryExcel.JournalNumber       = generalJournalEntry.JournalNumber;
                generalJournalAccountEntryExcel.AccountingDate      = generalJournalEntry.AccountingDate;
                generalJournalAccountEntryExcel.SubledgerVoucher    = generalJournalEntry.SubledgerVoucher;
                generalJournalAccountEntryExcel.LedgerAccount       = generalJournalAccountEntry.LedgerAccount;

                generalJournalAccountEntryExcel.TransactionCurrencyCode     = generalJournalAccountEntry.TransactionCurrencyCode;
                generalJournalAccountEntryExcel.TransactionCurrencyAmount   = generalJournalAccountEntry.TransactionCurrencyAmount;
                generalJournalAccountEntryExcel.ReportingCurrencyAmount     = generalJournalAccountEntry.ReportingCurrencyAmount;
                generalJournalAccountEntryExcel.AccountingCurrencyAmount    = generalJournalAccountEntry.AccountingCurrencyAmount;
                generalJournalAccountEntryExcel.PostingType                 = generalJournalAccountEntry.PostingType;

                generalJournalAccountEntryExcel.BusinessLine    = bl;
                generalJournalAccountEntryExcel.Department      = dept;
                generalJournalAccountEntryExcel.Project         = project;
                generalJournalAccountEntryExcel.Vehicle         = vehicle;
                generalJournalAccountEntryExcel.Worker          = worker;
                generalJournalAccountEntryExcel.insert();

                progressBar.setCaption(strfmt("Exporting the voucher transactions"));
                progressBar.setAnimation(#AviUpdate);
                progressBar.setText(strfmt("Processing"));
            }

        }
        catch(Exception::Deadlock)
        {
            error('Caught an exception');
            retry;
        }
    }

    endLengthyOperation();
    args.name(formstr(ALE_VoucherTransExportToExcel));
    formRun = new FormRun(args);
    formRun.run();
    formRun.wait();
}
-------------------------------------------------------------------
public boolean runsImpersonated()
{
    return true;
}
------------------------------------------------------
public boolean showQueryValues()
{
    return true;
}
-----------------------------------
public boolean unpack(container _packedClass)
{
    boolean     ret         = false;
    int         version     = RunBase::getVersion(_packedClass);
    container   packedQuery = conNull();

    switch (version)
    {
        case #CurrentVersion:
            [version, packedQuery] = _packedClass;

            if (SysQuery::isPackedOk(packedQuery))
            {
                qr   = new QueryRun(packedQuery);
                ret         = true;
            }
            break;

        default:
            ret = false;
    }
    return ret;
}
---------------------------------------------------
static ClassDescription description()
{
    return "Export to excel";
}
-----------------------------------------------------------
public static void main(Args args)
{
    ALE_ExportVoucherTransToExcel exportVoucherTransToExcel = new ALE_ExportVoucherTransToExcel();

    if (exportVoucherTransToExcel.prompt())
    {
        exportVoucherTransToExcel.run();
    }
}

Export to excel in ax 2012 x++

Export to excel in ax 2012 x++

void clicked()
{
   /* #AviFiles
    DimensionAttributeValueSetStorage    dimStorage;
    Counter                              i;
    DimensionAttributeLevelValueAllView dimAttrView; //View that will display all values for ledger dimensions
    DimensionAttribute                  dimAttr;

    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            font;
    int                     row,sleepCount = 1000;
    DimensionValue          bl,dept,project,worker,vehicle;
    GeneralJournalEntry     generalJournalEntryExcel;
    GeneralJournalAccountEntry generalJournalAccountEntryExcel;
    GeneralJournalAccountEntry generalJournalAccountEntryLoc;
    SysOperationProgress    progressBar = new SysOperationProgress();

    // intializing classes to export excel
    application = SysExcelApplication::construct();
    application.displayAlerts(false);
    workbooks = application.workbooks();
    workbook = workbooks.add();
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');

    // Setting Header values
    cell = cells.item(1, 1);
    cell.value("Journal Number");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 2);
    cell.value("Date");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 3);
    cell.value("Voucher");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 4);
    cell.value("Ledger account");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 5);
    cell.value("Year Closed");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 6);
    cell.value("Currency code");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 7);
    cell.value("Transaction currency amount");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1, 8);
    cell.value("Reporting Currency amount");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,9);
    cell.value("Amount");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,10);
    cell.value("Posting layer");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,11);
    cell.value("Acknowledge");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,12);
    cell.value("Project");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,13);
    cell.value("worker");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,14);
    cell.value("vehicle");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,15);
    cell.value("Department");
    font = cell.font();
    font.bold(true);

    cell = cells.item(1,16);
    cell.value("Business Line");
    font = cell.font();
    font.bold(true);

    try
    {
        row = 1;
        startLengthyOperation();
        while(queryRun.next())
        {
            generalJournalAccountEntryLoc = queryRun.get(tablenum(GeneralJournalAccountEntry));

            select generalJournalAccountEntryExcel
            join generalJournalEntryExcel where generalJournalAccountEntryExcel.GeneralJournalEntry == generalJournalEntryExcel.RecId
                && generalJournalAccountEntryExcel.RecId == generalJournalAccountEntryLoc.RecId;

                bl = '';
                dept = '';
                project = '';
                vehicle ='';
                worker = '';

                while select DisplayValue from dimAttrView
                    where dimAttrView.ValueCombinationRecId == generalJournalAccountEntryExcel.LedgerDimension
                    join BackingEntityType,Name from dimAttr
                        where dimAttr.RecId == dimAttrView.DimensionAttribute
                {
                    switch (dimAttr.Name)
                    {
                        case 'BusinessLine':
                            bl = dimAttrView.DisplayValue;
                            break;

                        case 'Department':
                            dept = "'" + dimAttrView.DisplayValue;
                            break;

                        case 'Project':
                            project = "'" + dimAttrView.DisplayValue;
                            break;

                        case 'Vehicle':
                            vehicle = dimAttrView.DisplayValue;
                            break;

                        case 'Worker':
                            worker = "'" + dimAttrView.DisplayValue;
                            break;
                    }
                }

                row++;
                cell = cells.item(row, 1);
                cell.value(generalJournalEntryExcel.JournalNumber);
                cell = cells.item(row, 2);
                cell.value(generalJournalEntryExcel.AccountingDate);
                cell = cells.item(row, 3);
                cell.value(generalJournalEntryExcel.SubledgerVoucher);
                cell = cells.item(row, 4);
                cell.value(generalJournalAccountEntryExcel.LedgerAccount);
                cell = cells.item(row, 5);
                cell.value(generalJournalEntryExcel.displayIsFiscalYearClosed());
                cell = cells.item(row, 6);
                cell.value(generalJournalAccountEntryExcel.TransactionCurrencyCode);
                cell = cells.item(row, 7);
                cell.value(generalJournalAccountEntryExcel.TransactionCurrencyAmount);
                cell = cells.item(row,8);
                cell.value(generalJournalAccountEntryExcel.ReportingCurrencyAmount);
                cell = cells.item(row, 9);
                cell.value(generalJournalAccountEntryExcel.AccountingCurrencyAmount);
                cell = cells.item(row, 10);
                cell.value(generalJournalEntryExcel.PostingLayer);
                cell = cells.item(row, 11);
                cell.value(generalJournalEntryExcel.ALE_AcknowledgeNumber);
                cell = cells.item(row, 12);
                cell.value(project);
                cell = cells.item(row, 13);
                cell.value(worker);
                cell = cells.item(row, 14);
                cell.value(vehicle);
                cell = cells.item(row, 15);
                cell.value(dept);
                cell = cells.item(row, 16);
                cell.value(bl);
            if(sleepCount == row)
            {
                sleepCount = sleepCount + 1000;
                sleep(10000);
            }
             infolog.yield();
            progressBar.setCaption(strfmt("Exporting the voucher transactions to excel",row));
            progressBar.setAnimation(#AviUpdate);
            progressBar.setText(strfmt("Processing %1 out of %2",row));
        }
        endLengthyOperation();
    }
    catch(Exception::Error)
    {
        throw(Exception::Error);
    }

    GeneralJournalAccountEntry_ds.reread();
    GeneralJournalAccountEntry_ds.research();

    application.visible(true);

    application.finalize();
    application = null;
   */
}