Record Lock in SQL

//Record Lock in SQL
select cmd,* from sys.sysprocesses
where blocked > 0

exec sp_lock
exec sp_who
exec sp_who2

select
  object_name(resource_associated_entity_id) as 'TableName' ,*
from
  sys.dm_tran_locks
where resource_type = 'OBJECT'
  and resource_database_id = DB_ID()
SELECT
     blocking_session_id AS BlockingSessionID,
     session_id AS VictimSessionID,
    
     (SELECT [text] FROM sys.sysprocesses
      CROSS APPLY sys.dm_exec_sql_text([sql_handle])
      WHERE spid = blocking_session_id) AS BlockingQuery,
    
     [text] AS VictimQuery,
     wait_time/1000 AS WaitDurationSecond,
     wait_type AS WaitType,
     percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

http://shahnawazdba06.blogspot.com/2016/05/find-table-which-has-row-lock.html

New Customize Scripts Method for AX Develper in ax 2012

//New Customize Sys Develper Method in ax 2012

//Adding new method in Scripts method in ax Development window
Image :

//Class-> xppsource
 New method -> Find (Ex)

public Source findMethod(TableName _tableName)
{
    str method;
    DictTable dictTable;
    DictIndex dictIndex;
    DictField dictField;
    FieldName fieldName;
    DictType dictType;
    DictEnum dictEnum;
    int fieldCount;
    int i;
    container fields1;
    container fields2;
    container fields3;
    IdentifierName varName;
    IdentifierName varType;
    ;
    method = 'public static %1 find' + '(%2, boolean _forUpdate = false)%5' + '{%5' + ' %1 table;%5' +  '%5' + ' if (%3)%5' +
            ' {%5' +' if (_forUpdate)%5' + ' table.selectForUpdate(_forUpdate);%5' + '%5' + ' select firstOnly table%5' + ' where %4;%5' +
            ' }%5' + ' return table;%5' + '}';
    dictTable = new DictTable(tableName2id(_tableName));
    dictIndex = dictTable.indexObject(
    dictTable.replacementKey() ?
    dictTable.replacementKey() :
    dictTable.primaryIndex());
    if (dictIndex)
    {
        fieldCount = dictIndex.numberOfFields();
        for (i = 1; i <= fieldCount; i++)
        {
            dictField = new dictField(
            dictTable.id(),
            dictIndex.field(i));
            fieldName = dictField.name();
            varName = '_' + strLwr(subStr(fieldName,1,1)) +
            subStr(fieldName,2,strLen(fieldName)-1);
            if (dictField.typeId())
            {
                dictType = new DictType(dictField.typeId());
                varType = dictType.name();
            }
            else if (dictField.enumId())
            {
                dictEnum = new DictEnum(dictField.enumId());
                varType = dictEnum.name();
            }
            else
            {
                throw error(strfmt("Field '%1' type is not defined",fieldName));
            }
            fields1 += strFmt('%1 %2',varType,varName);
            fields2 += varName;
            fields3 += strFmt('table.%1 == %2',fieldName,varName);
        }
    }
    source = strFmt(method,_tableName,con2Str(fields1,', '),con2Str(fields2, ' && '),con2Str(fields3, #newLine + strRep(' ', 14) + '&& '),#newLine);
    return source;
}


// Class->EditorScrpts
public void template_method_find(Editor _editor)
{
    TreeNode objNode;
    xppSource xpp;
    Source template;
    objNode = EditorScripts::getApplObjectNode(_editor);
    if (!objNode)
    {
        return;
    }
    _editor.gotoLine(1);
    _editor.firstLine();
    while (_editor.moreLines())
    {
        _editor.deleteLines(1);
        _editor.nextLine();
    }
    xpp = new xppSource();
    template = xpp.findMethod(objNode.AOTname());
    _editor.insertLines(template);
}

//Class->EditorScripts->Method(isApplicableMethod)
case methodStr(EditorScripts, template_method_find):
            return (_aotNode && _aotNode.treeNodeType().id() == #NT_DBTABLE);


//Final Output in Ax development Workspace

Image:

Enjoy...

To add New Tool menu in ax 2012

// To add New Tool menu in ax 2012

Ax->Menu Bar-Tools (to Add new menu)

// AOT/menu/DevelomentTools/(Add new menu)

AX Retail Data Export to excel without excel addins

//AX Retail Data Export to excel without excel addins

using (SaveFileDialog sfdExport = new SaveFileDialog())
{
    sfdExport.Filter = "Excel (2003)(.xls)|*.xls";
    if (sfdExport.ShowDialog() != DialogResult.Cancel)
    {
        try
        {
            DataTable dtExport = new DataTable();
            //You can get your data and fill it here

            using (DevExpress.XtraGrid.GridControl grExport = new DevExpress.XtraGrid.GridControl())
            {
                using (DevExpress.XtraGrid.Views.Grid.GridView gvExport = new DevExpress.XtraGrid.Views.Grid.GridView())
                {
                    gvExport.OptionsBehavior.AutoPopulateColumns = true;
                    gvExport.GridControl = grExport;

                    grExport.BindingContext = new BindingContext();
                    grExport.DataSource = dtExport;
                    grExport.MainView = gvExport;
                    grExport.ForceInitialize();

                    gvExport.PopulateColumns();
                    gvExport.ExportToXls(strExportFilePath);
                }
            }
        }
        catch (Exception ex)
        {
            LSRetailPosis.ApplicationExceptionHandler.HandleException(this.ToString(), ex);
            throw;
        }
    }
}

Restricting Default cheque print

//Restricting Default cheque print

Class--> VendOutPaym->Close

//this.printDocument();

Enable remote errors in SQL(Query)

//Enable remote errors in SQL

//Report DB -

dbo.ConfigurationInfo

Deploy POS Report AX

// Deploy POS Report AX

Retail > Periodic > Data distribution > Distribution schedule.
Select job, click Run

Changing Customer Accoount Posted customer Transaction in GL ax 2012

// Changing Posted customer Transaction in GL ax 2012

Updating Posted customer transaction Payments with different customer Account .

Note:
1) Both Customers  should lie on same customer Posting profile account then only below logic will work.
2) If there is settlement update settlement Table also(CustSettlement)
3) Applicable only when customer transaction created and posted in Gl and AR Payment Journal. Not for customer invoice.

    LedgerJournalTrans          jourTrans;
    CustTrans           custTrans;
    LedgerDimensionAccount  ledgerDim;
    ;
    ledgerDim = DimensionStorage::getDynamicAccount("CustomerAccount", LedgerJournalACType::Cust);
   
    update_recordSet jourTrans setting LedgerDimension = ledgerDim
        where jourTrans.Voucher == "VoucherNum";
    update_recordSet custTrans setting AccountNum = "CustomerAccount",OrderAccount = "CustomerAccount"
        where custTrans.Voucher == "VocuherNum";

Aging bucket through ssrs ax 2012

//Aging bucket through ssrs ax 2012

1-30 days

=Sum(iif((Fields!TransDate.Value <= Parameters!ToDate.Value)
and (Fields!TransDate.Value >= DateAdd("d",-30,Parameters!ToDate.Value))
,Fields!Debit.Value,0))
+
Sum(iif((Fields!TransDate.Value <= Parameters!ToDate.Value)
and (Fields!TransDate.Value >= DateAdd("d",-30,Parameters!ToDate.Value))
,Fields!Credit.Value,0))

30-60 days

=Sum(iif((Fields!TransDate.Value <= DateAdd("d",-31,Parameters!ToDate.Value))
and (Fields!TransDate.Value >= DateAdd("d",-30,DateAdd("d",-30,Parameters!ToDate.Value)))
and instr(Fields!Txt.Value, "Sales"),Fields!Debit.Value,0))
+
Sum(iif((Fields!TransDate.Value <= DateAdd("d",-31,Parameters!ToDate.Value))
and (Fields!TransDate.Value >= DateAdd("d",-30,DateAdd("d",-30,Parameters!ToDate.Value))
and instr(Fields!Txt.Value, "Sales")),Fields!Credit.Value,0))


so on

Sales Order Total Discount ax 2012

//Sales Order Total Discount ax 2012

//Scenario -1 - spliting in line level
switch (_fieldId)
    {
        case fieldNum(SalesTable, TotalSalesDiscount):
            if(this.TotalSalesDiscount > 0)
            {
                this.write();
                sumSalesLine.clear();
                Select sum(SalesPrice),sum(SalesQty),sum(LineAmount) from sumSalesLine
                        where sumSalesLine.SalesId == this.SalesId;
                ttsBegin;
                salesline.clear();
                while select forUpdate salesline
                    where salesline.SalesId == this.SalesId
                        && salesline.SalesStatus == SalesStatus::Backorder
                {
                    salesline.FccSalesPrice = decRound((this.TotalSalesDiscount) * ((((salesline.LineAmount)/sumSalesLine.LineAmount)*100)/100),3);
                    salesline.update();
                }
                ttsCommit;
                sumSalesLine.clear();
                Select sum(SalesPrice),sum(SalesQty),sum(FccSalesPrice),sum(LineAmount) from sumSalesLine
                        where sumSalesLine.SalesId == this.SalesId;
                saleeslinerecid.clear();
                select RecId from saleeslinerecid order by RecId Desc;
                ttsBegin;
                salesline.clear();
                while select forUpdate salesline
                    where salesline.SalesId == this.SalesId
                        && salesline.SalesStatus == SalesStatus::Backorder
                {
                    if(sumSalesLine.FccSalesPrice != this.TotalSalesDiscount)
                    {
                        if(saleeslinerecid.RecId == salesline.recid)
                        {
                            salesline.FccSalesPrice = salesline.FccSalesPrice +(this.TotalSalesDiscount - sumSalesLine.FccSalesPrice);
                        }
                        else
                        {
                            salesline.FccSalesPrice = salesline.FccSalesPrice;
                        }
                    }
                    salesline.LinePercent = 0;
                    salesline.LineDisc = salesline.FccSalesPrice/salesline.SalesQty;
                    salesline.modifiedField(fieldNum(SalesLine,LineDisc));
                    salesline.update();
                }
                ttsCommit;
                this.dataSource().reread();
            }
            else
            {
                this.write();
                ttsBegin;
                while select forUpdate salesline
                    where salesline.SalesId == this.SalesId
                            && salesline.SalesStatus == SalesStatus::Backorder
                {
                    salesline.LinePercent = 0;
                    salesline.LineDisc = 0;
                    salesline.FccSalesPrice = 0;
                    salesline.modifiedField(fieldNum(SalesLine,LineDisc));
                    salesline.update();
                }
                ttsCommit;
                this.dataSource().reread();
            }
            break;
    }

//Scenario -2
Populating total discout Amount by finding discount percent - Default Total disc percent field to affect header discount
//Scenario -3
switch (_fieldId)
    {
        case fieldNum(SalesTable, TotalSalesDiscount):
                if(this.TotalSalesDiscount > 0)
                {
                    Select sum(LineAmount) from sumSalesLine
                            where sumSalesLine.SalesId == this.SalesId;
                    ttsBegin;
                    while select forUpdate salesline
                        where salesline.SalesId == this.SalesId
                    {
                        salesline.LineDisc = (this.TotalSalesDiscount) * ((((salesline.LineAmount)/sumSalesLine.LineAmount)*100)/100);
                        salesline.LineAmount = (salesline.SalesQty * salesline.SalesPrice) - salesline.LineDisc;
                        salesline.update();
                    }
                    ttsCommit;
                    this.dataSource().research(true);
                }
            break;
    }