SQL Store Procedure sample

ALTER PROCEDURE Proc_Test

       -- Add the parameters for the stored procedure here
       @name varchar(50),
       @trns_dt datetime
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
    -- Insert statements for procedure here
       SELECT * from AdventureWorks2012.Sales.SalesOrderDetail D
       print @name
       --where convert(varchar(10),D.ModifiedDate,120)=convert(varchar(10),@trns_dt,120)
END
GO
--Select getdate()
--Select convert(varchar(10),GETDATE(),105)

Get Lastest Purchase price of an item ax 2012

private Price GetLatestPurchPrice(ItemId _item)

{
    InventItemPrice invItemprice;
    ;
    select firstOnly invItemprice order by RecId desc
        where invItemprice.ItemId == _item
            && invItemprice.PriceType == CostingVersionPriceType::Purch
                && invItemprice.CostingType == InventItemCostingType::Last;
   
    return  invItemprice.Price;
}

Rounding in SSRS Expression based on count of Decimal place ax 2012

=iif(Fields!RoundingDecimals.value=0,FormatNumber(Fields!Fieldname.Value,3),FormatNumber(Fields!FieldName.value,fields!RoundingDecimals.Value))

To add a new financial dimension for your custom table

1. Drag the EDT (AOT >> Data Dictionary >> Extended Data Types >> DimensionDefault) to the fields of your table

2. Create a new “Normal” relation on your table linking the newly added “DefaultDimension” field to the “RecId” field of DimensionAttributeValueSet table

3. Now create a view for your table. Take care of the following conventions:

• View should be named exactly DimAttributeYourTable
• Add your table to DimAttributeYourTable >> Metadata >> Data Sources node
• This data source should be named exactly BackingEntity
• Add the following three fields naming them exactly as mentioned
  • Key – Use surrogate key field e.g. RecId
  • Value – Use natural key field e.g TrackingNum
  • Name – Use any descriptive field e.g. DeliveryName

4. Create and run the following  job to clear the dimension cache:

static void clearCache(Args _args)
{
    DimensionCache::clearAllScopes();
    info("done");
}

5. Now open General Ledger >> Setup >> Financial dimensions >> Financial dimensions
6. Create a new financial dimension
7. Select your newly added dimension from the “Use values from” lookup:

---

Now if you want to add this financial dimension to any of the existing chart of accounts, perform the following steps:

1. Open General Ledger >> Setup >> Chart of accounts >> Configure account structures
2. Select any of the active account structures e.g. “Account Structure P&L (Active)”
3. Click Edit button
4. Click Add segment button
5. Select the newly added dimension
6. Finally activate the dimension to let the changes take effect.

Updating system fields -- Created/Modified by

               new OverwriteSystemFieldsPermission().assert();
                XXXX.overwriteSystemfields(true);
                XXXX.(fieldNum(pylLoanTable,ModifiedBy)) =  COMVariant2Str(cells.item(row,        42).value());
                XXXX.(fieldNum(pylLoanTable,CreatedBy)) =  COMVariant2Str(cells.item(row, 65).value());
                XXXX.(fieldNum(pylLoanTable,modifiedDateTime)) = DateTimeUtil::newDateTime(cells.item(row, 41).value().date(),timeNow());
                XXXX.(fieldNum(pylLoanTable,createdDateTime)) = DateTimeUtil::newDateTime(cells.item(row, 64).value().date(),timeNow());
                XXXX.insert();
                CodeAccessPermission::revertAssert();

Updating dimension group for an item in ax 2012

static void UpdatingDimensionGroup(Args _args)
{

     inventTable inventTable;
 
     ttsBegin;
    while select inventTable
    {
            InventTableInventoryDimensionGroups::updateDimensionGroupsForItem(curext(),      inventTable.itemid, EcoResStorageDimensionGroup::findByDimensionGroupName("XXX_SDG").RecId,    EcoResTrackingDimensionGroup::findByDimensionGroupName("XXX_TDG").RecId,inventtable.product);
    }
    ttsCommit;

}

Updating Site specific order settings of an item in ax 2012

    InventDim       inventDim,invDim;
    InventTable     inventTable;
    inventDimId inventDimId;
    InventItemPurchSetup    invPurch;
    InventItemSalesSetup    invSal;
    InventItemInventSetup   invInvent;
   
    while select inventTable
    {      
   
            invDim.clear();
            Select forUpdate invDim where invDim.InventSiteId == "AD" && invDim.InventLocationId == '' && InvDim.InventBatchId == '';
            if (!invDim)
            {
                invDim.InventBatchId     = '';
                invDim.InventLocationId  = '';
                invDim.InventSiteId      = "AD";                
                invDim = InventDim::findOrCreate(invDim);
            }
            inventDimId = invDim.InventDimId;

            // Delete existing dimensions
            delete_from invPurch where invPurch.ItemId == inventTable.ItemId &&
            invPurch.InventDimId != "AllBlank";              

            // Purchase Location Id
            invDim.clear();
            Select forUpdate invDim where invDim.InventSiteId == '' && invDim.InventLocationId == "AD-DRY" && InvDim.InventBatchId == '';
            if (!invDim)
            {
                invDim.InventBatchId     = '';
                invDim.InventLocationId  = "AD-DRY";
                invDim.InventSiteId      = '';
                invDim.initFromInventLocation(invDim.inventLocation());
                invDim = InventDim::findOrCreate(invDim);
            }
           
            // Dimension for Location
            invPurch.clear();
            invPurch.initFromDefault();
            invPurch.ItemId = inventTable.ItemId;
           
            invPurch.InventDimId = inventDimId;
            invPurch.InventDimIdDefault = invDim.InventDimId;
            invPurch.insert();
   
            delete_from invSal where invSal.ItemId == inventTable.ItemId &&
            invSal.InventDimId != "AllBlank";  
            invSal.clear();
            invSal.initFromDefault();
            invSal.ItemId = inventTable.ItemId;
           
            invSal.InventDimId = inventDimId;
            invSal.InventDimIdDefault = invDim.InventDimId;
            invSal.insert();
   
            delete_from invInvent where invInvent.ItemId == inventTable.ItemId &&
            invInvent.InventDimId != "AllBlank";
            invInvent.clear();
            invInvent.initFromDefault();
            invInvent.ItemId = inventTable.ItemId;
           
            invInvent.InventDimId = inventDimId;
            invInvent.InventDimIdDefault = invDim.InventDimId;
            invInvent.insert();
    }
   

Updating default order setting of an item in ax 2012

    InventTable inventTable;
    InventItemInventSetup inventItemInventSetup;
    InventItemPurchSetup inventItemPurchSetup;
    InventItemSalesSetup inventItemSalesSetup;
    InventDim inventDim;
    InventDimId dimId;

   ;
   ttsBegin;
    inventDim.initValue();
   inventDim.InventSiteId = "AD";
   inventDim = InventDim::findOrCreate(inventDim);
   dimId =  inventDim.inventDimId;
   while select inventTable
   {
        info(strFmt("%1",inventTable.ItemId));

    inventItemInventSetup.clear();
    inventItemPurchSetup.clear();
    inventItemSalesSetup.clear();

   select inventItemInventSetup where inventItemInventSetup.InventDimId == inventDim.inventDimId;
   if(!inventItemInventSetup)
    {
        //Site specific setup
       inventItemInventSetup.initValue();
       inventItemInventSetup.InventDimId = inventDim.inventDimId;
       inventItemInventSetup.ItemId = inventTable.ItemId;
       inventItemInventSetup.insert();
   }
   select inventItemPurchSetup where inventItemPurchSetup.InventDimId == inventDim.inventDimId;
   if(!inventItemPurchSetup)
   {
       inventItemPurchSetup.initValue();
       inventItemPurchSetup.InventDimId = inventDim.inventDimId;
       inventItemPurchSetup.ItemId = inventTable.ItemId;
       inventItemPurchSetup.insert();
   }
   select inventItemSalesSetup where inventItemSalesSetup.InventDimId == inventDim.inventDimId;
   if(!inventItemSalesSetup)
   {
       inventItemSalesSetup.initValue();
       inventItemSalesSetup.InventDimId = inventDim.inventDimId;
       inventItemSalesSetup.ItemId = inventTable.ItemId;
       inventItemSalesSetup.insert();
   }
    inventItemInventSetup.clear();
    inventItemPurchSetup.clear();
    inventItemSalesSetup.clear();
   //Default order settings
   inventItemInventSetup= inventItemInventSetup::findDefault(inventTable.itemId, true);
   inventItemInventSetup.InventDimIdDefault = inventDim.inventDimId;
   inventItemInventSetup.update();


   inventItemPurchSetup = inventItemPurchSetup::findDefault(inventTable.itemId, true);
   inventItemPurchSetup.InventDimIdDefault = inventDim.inventDimId;
   inventItemPurchSetup.update();


   inventItemSalesSetup= inventItemSalesSetup::findDefault(inventTable.itemId, true);
   inventItemSalesSetup.InventDimIdDefault = inventDim.inventDimId;
   inventItemSalesSetup.update();

    }
    ttsCommit;

Error when Deploying SSRS from VS 2010- Solved - Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

Error 1 The "DeployToReportsServerTask" task failed unexpectedly.
System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at System.Activator.CreateInstance(String assemblyString, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, Evidence securityInfo, StackCrawlMark& stackMark)
   at System.Activator.CreateInstance(String assemblyName, String typeName)
   at System.AppDomain.CreateInstance(String assemblyName, String typeName)
   at System.AppDomain.CreateInstanceAndUnwrap(String assemblyName, String typeName)
   at System.AppDomain.CreateInstanceAndUnwrap(String assemblyName, String typeName)
   at Microsoft.Dynamics.Framework.Deployment.Reports.DomainBound.PrepareTask(AppDomain tempAppDomain)
   at Microsoft.Dynamics.Framework.Deployment.Reports.DeployToReportsServerTask.Execute()
   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   at Microsoft.Build.BackEnd.TaskBuilder.<ExecuteInstantiatedTask>d__20.MoveNext() C:\Program Files (x86)\MSBuild\Microsoft\DynamicsTools\Microsoft.Dynamics.Framework.Design.Reporting.Modeling.targets 466 6 AccountantShortTermSolvency

Solution:

Please GOTO Computer properties and follow the below steps

AX 2012: Merge ledger and financial dimensions using X++ code

AX 2012: Merge ledger and financial dimensions using X++ code

Hi Friends,
In this post let us see how to use out of the box available X++ API which can be used to merge ledger and financial dimensions.  The below code works well with AX2012/ AX2012 R2/ AX2012 R3 versions of Microsoft Dynamics AX. This is useful in scenarios when we need to create journal from code and need to merge the dimensions in order to get the correct offset account values.

So to keep it simple, let consider the below scenario:
Customer "2014" in the system is having the below financial dimensions defined on it:

Now let's create a standard AX general journal and on it's journal line, let's select account type as customer, account as customer account "2014" , offset account as Ledger  and then from lookup select ledger account "110101", as shown below:

As soon as you select the ledger account, you will notice that system merges the ledger dimension and the financial dimension and create the complete offset account dimension value with the display value as shown below:

To do this from code, use API serviceCreateLedgerDimension() available DimensionDefaultingService class, it is advisable to merge the dimensions whenever you create journal from X++ code, to make sure the resulting voucher entries are against correct and complete accounts.

static void Job1(Args _args)

{

    RecId   customerDefaultDimension = CustTable::find("2014").DefaultDimension;

    RecId   ledgerDimension = AxdDimensionUtil::getMultiTypeAccountId(enumNum(LedgerJournalACType),LedgerJournalACType::Ledger,[110101,110101]);

    info(DimensionAttributeValueCombination::getDisplayValue(DimensionDefaultingService::serviceCreateLedgerDimension(ledgerDimension,customerDefaultDimension)));

}

Line 1 --> gets the RecID which relates to the financial dimension defined on the customer.

Line 2 --> Get the RecID which related to the ledger account 110101.

Line 3 --> Is actually combining the logic to convert the recID into the display value and also to merge the dimensions.

DimensionDefaultingService::serviceCreateLedgerDimension() --> is the function which merges the dimensions. It takes the ledger dimensions and then the financial dimension which needs to be merged and returns the recID of the merged dimension.

DimensionAttributeValueCombination::getDisplayValue() this function returns the display value of the recID which is passed to it. I have used it here to demonstration purpose only. 

There are many other useful API's existing in this class which can be very helpful in developing customizations which require to use financial and ledger dimensions.

SSRS: Error: The user or group name ‘CustomerDomain\SomeUser’ is not recognized

Solution:

In SQL Server Management Studio

--> Remove User(Error: user name) record  in the SysServerSessions table.

--> And  In SysServerSessionstable change the Id value to 1 for the new user;

Error While Deploying Dynamics AX 2012 R2 Report --> "The User or Group 'Contoso\Administrator' is not recognized"

// Error When Deploying Dynamics AX 2012 R2 Report:
   "The User or Group 'Contoso\Administrator' is not recognized"

http://www.qumio.com/Blog/Lists/Posts/Post.aspx?ID=10

Report Manager URL configuration Error

Error :

"Make sure that SQL Server Reporting Services is configured correctly. Verify the Web Service URL and Report Manager URL configuration in the SQL Reporting Services Configuration Manager."

Resolution :

Start -> Regedit ->
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system"

Change Data Value : 1 to 0