miércoles, 16 de diciembre de 2015

Posting Packing Slip Examples

I have been investigating the use of the chooseLinesQuery parameter in order to create a packing slip from multiple sales order lines.  Note the requirement to call chooseLines after having associated the query, which incidentally is called 'SalesUpdate'.  There are other parameters in the SalesFormLetter that can be used to avoid doing the heavy work of generating a query, such as relying on an array of Shipment IDs.

Example 1, from Experts-Exchange.  Multiple sales orders in one document.
  SalesFormLetter salesFormLetter;
  QueryRun queryRun;
  Query query;
  str strSalesTable = "V683904, V683905, V683906";
  ;
    
  salesFormLetter = SalesFormLetter::construct(DocumentStatus::PackingSlip);

  query = new Query(QueryStr(SalesUpdate));
  query.dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable, SalesId)).value(strSalesTable);
  queryRun = new QueryRun(query);

  salesFormLetter.chooseLinesQuery(queryRun);
  salesFormLetter.transDate(systemdateget());
  salesFormLetter.specQty(SalesUpdate::All);
  salesFormLetter.printFormLetter(false);
  salesFormLetter.createParmUpdate();
  salesFormLetter.chooseLines(null,true);
  salesFormLetter.reArrangeNow(true);
  salesFormLetter.run();

Example 2, using the WMSShipmentId (Shipment ID in the warehouse system) as the base for our packing slip.  Source here, and note the non-recommended way of instantiating the SalesFormLetter class.
  SalesFormLetter salesFormLetter;
  WMSShipment shipment;
  WMSShipmentId shipmentId = '008431_113';
  Set wmsShipmentSet = new Set(Types::String);
  ;
  wmsShipmentSet.add(shipmentId);

  salesFormLetter = SalesFormLetter_PackingSlip::newPackingSlip();
  salesFormLetter.allowEmptyTable(salesFormLetter.initAllowEmptyTable(true));
  salesFormLetter.multiForm(true);
  salesFormLetter.getLast();
  salesFormLetter.parmLineIdSet(wmsShipmentSet.pack());
  shipment = WMSShipment::find(shipmentId);
  salesFormLetter.update(shipment, systemDateGet(), SalesUpdate::PickingList, AccountOrder::None, false, true);

Example 3, the most versatile of all, is to create a query filtering on some RecIds via multiple OR values.
    SalesFormLetter         salesFormLetter;
    salesParmUpdate         salesParmUpdate;

    SysQueryRun calcQuery()
    {
        RefRecId                salesLineRecId;
        Query                   qry = new Query(QueryStr(SalesUpdatePackingSlip));
        QueryBuildDataSource    queryBuildDataSource = qry.dataSourceTable(tableNum(SalesLine));
        QueryBuildRange         queryBuildRange;
        SalesLine               salesLine;

        // Dummy Query to generate a series of SalesLine.RecId
        while select RecId from salesLine
            where salesLine.CustomerRef == 'XXX'
        {
            salesLineRecId = salesLine.RecId;
            queryBuildRange = queryBuildDataSource.addRange(fieldnum(SalesLine, RecId));
            queryBuildRange.value(SysQuery::value(salesLineRecId));
        }
        return new QueryRun(qry);
    }
    ;

    salesFormLetter = SalesFormLetter::construct(DocumentStatus::PackingSlip);

    salesFormLetter.getLast();
    salesFormLetter.resetParmListCommonCS();
    salesFormLetter.transDate(systemdateget());
    salesFormLetter.proforma(false);

    // Let's create that ParmId
    salesFormLetter.createParmUpdateFromParmUpdateRecord(
        SalesFormletterParmData::initSalesParmUpdateFormletter(
            DocumentStatus::PackingSlip, salesFormLetter.pack()));
    salesParmUpdate = salesFormLetter.salesParmUpdate();
    salesParmUpdate.SpecQty         = SalesUpdate::DeliverNow;
    salesParmUpdate.Proforma        = NoYes::No;
    salesParmUpdate.update();
    salesFormLetter.salesParmUpdate(salesParmUpdate);

    salesFormLetter.chooseLinesQuery(calcQuery());
    salesFormLetter.reArrangeNow(true);
    salesFormLetter.validateAll(false);
    salesFormLetter.printout(Printout::After);
    salesFormLetter.printFormLetter(NoYes::Yes);
    salesFormLetter.chooseLines();
    salesFormLetter.run();

Example 4.  Let's apply the user's printing options previously selected from the Sales form, and copy the settings to send it via X++.  Idea copied from here.
SalesFormLetter salesFormLetter; QueryRun queryRun; Query query; str strSalesTable = "V683904, V683905, V683906"; SalesFormLetter_PackingSlip salesFormLetter_PackingSlip; Counter iCnt = 0; SRSPrintDestinationSettings printSettingsOrig, printSettingsCopy; container lastValues; ; salesFormLetter = SalesFormLetter::construct(DocumentStatus::PackingSlip); query = new Query(QueryStr(SalesUpdate)); query.dataSourceTable(tablenum(SalesTable)).addRange(fieldnum(SalesTable, SalesId)).value(strSalesTable); queryRun = new QueryRun(query); salesFormLetter.chooseLinesQuery(queryRun); salesFormLetter.transDate(systemdateget()); salesFormLetter.specQty(SalesUpdate::All); salesFormLetter.printFormLetter(true); salesFormLetter.printout(Printout::After); // User print settings from Sales order form if (iCnt == 0) { salesFormLetter_PackingSlip = SalesFormLetter::construct(DocumentStatus::PackingSlip); lastValues = xSysLastValue::getValue(curext(), curUserId(), UtilElementType::Class, classStr(SalesFormLetter_PackingSlip), formStr(SalesTable)); salesFormLetter_PackingSlip.unpack(lastValues); printSettingsOrig = new SRSPrintDestinationSettings(salesFormLetter_PackingSlip.printerSettingsFormletter(PrintSetupOriginalCopy::Original)); printSettingsCopy = new SRSPrintDestinationSettings(salesFormLetter_PackingSlip.printerSettingsFormletter(PrintSetupOriginalCopy::Copy)); } iCnt++; salesFormLetter.updatePrinterSettingsFormLetter(printSettingsOrig.pack(), PrintSetupOriginalCopy::Original); salesFormLetter.updatePrinterSettingsFormLetter(printSettingsCopy.pack(), PrintSetupOriginalCopy::Copy); salesFormLetter.usePrintManagement(true); //End salesFormLetter.createParmUpdate(); //AX2009? salesFormLetter.chooseLines(null,true); salesFormLetter.reArrangeNow(true); salesFormLetter.run();


jueves, 1 de octubre de 2015

Maximum buffer size 'problemo'


We have seen the following max buffer size message appear in the Supplier (VendTable) form, after an AX 2009 to AX 2012 R3 migration.

El tamaño interno total de los registros de la instrucción SELECT combinada es de 84418 bytes; sin embargo, Microsoft Dynamics está configurado de forma predeterminada para no sobrepasar los 49152 bytes.
O bien, tendrá que especificar un valor de 'Tamaño máximo de búfer' de 83 KB o superior en la página de la ficha 'Optimización de base de datos' en la Utilidad de configuración del servidor de Microsoft Dynamics AX. El valor predeterminado es de 24 KB.

Error: The total, internal size of the records in your joined SELECT statement is 84418 bytes, but Microsoft Dynamics is by default performance-tuned not to exceed 25600 bytes.
It is strongly recommended that you split your table(s) into smaller units.
Alternatively, you have to specify a 'Maximum buffer size' value of 83 Kbytes or higher on the 'Database Tuning' tab page in the Microsoft Dynamics AX Server Configuration Utility. The default value is 24 Kbytes.

Solution 1: Firstly for an AX 2012 R3 installation we are able to increase the AOS maximum buffer size to 48 KB ...  In fact, it is recommended!  We already possessed this value in our installation however and this recourse is nowhere near sufficient.

Solution 2: A quick compare for the aforementioned form shows us that two new table joins have been added.  One of these tables has been added as an inner join, for some reason, and the other is no longer a requirement.

The clever people in Microsoft state that this is a main source of the problem "Because large joins and wide rows may lead to performance issues, we use this limit to catch performance issues and ask customers to rethink their joins."

In this instance I'm not going to refactor the table.  For example.  If we had lots of text data added to the AX2009 CompanyInfo table I'd reconsider moving these fields to a completely new entity in AX2012.

By changing one join to 'delayed' (probably doesn't affect the buffer size) and deleting one of the tables no longer required we reduced the size to...  79KB from 83KB.  Still a long way to go!
I should also point out here that it's possible that there is some kind of caching going on and maybe I should have cleaned user cache files or from the tools menu update the various cache options available.

Solution 3: Out of sheer frustration I also deleted the whole form back to the stone age (leaving SYS/SYP layer) but the error message returned was still with a huge max buffer size requirement...  Waaaaait a minute.  Let's reread the first sentence of this post.  Remember that we are performing an upgrade of an AX 2009 environment.  This leaves migrated table entities with a LOT of fields that are no longer required in AX 2012 - all marked with DEL_...  And some other fields have been brought across in the code migration as well.
The 'DEL_' list goes on and on... This is known as a 'wide row'.
In fact these other fields look suspiciously obsolete as well.  The whole 'address' and 'party' functionality has been revamped, and so I've deleted everything in the VAR layer except for those fields specific to the client - all of those that we have diligently named with a prefix (see second image for example).   The disadvantage of removing these 'Address' or 'PartyId' fields is that we have the old converted data there if you migrated both code AND data...  But I can see no other option but to delete them and move on.

Firstly with the low risk option I shall delete (and synchronise) the large number of DEL_ fields from the table, and see if we reduce the max buffer size requirement from 79KB to...
Bye-bye suckers!
75KB.  Not a surprise really.  The DEL_ fields are all added to the buffer, but without being instantiated - From MSDN: "Due to the way the kernel handles data binding, the aggregated table column size is always the width of the entire table."  An interesting experiment.  Edit: Should restart the AOS to check if the buffer size has been changed.

Next, we need to compare AX 2009 and AX 2012 VendTable models, and make sure we leave only 'valid' fields in the VAR layer.  In fact, there are only four fields that should be present.  Customer requirements and later Microsoft parches merged into the entity.  Everything else must go!
AX 2009 SYP-VAR compare
Both UseCashDisc and CashAccountRegime_ES are now in the SYS layer in AX 2012
And now with only two new fields in the VendTable we reduced the requirement down to...  72 KB.  Uff.  No where near good enough.  Closing and reopening the AX client, a common trick, actually increased the requirement up to 75 KB.  The size probable depends on the row entity selected.  This is exasperating.

Finally on this note the other tables referenced by the form query were checked and no other additional fields were found.

Solution 4.  Field Groups, Relations and Indexes.  All must go out of sheer desperation.

Nothing.  No change whatsoever.

Solution 5. Restart AOS and try Voodoo.
Those of you who have got running the AOS on the third attempt will know that black magic or something, works.
By the time it takes to restart the AOS I managed to find and sacrifice a chicken.
Error - 75KB Again.  Not even fresh blood from virgin poultry can save us.

Solution 6: Increase the maximum buffer to 75KB in the server configuration utility, restart AOS and get back to work feeling completely useless and defeated.  At least it isn't 83KB.
The End...

Edit: It's a Sunday evening now and I'm laying awake in bed - What did I miss?  It's time to search the internet for the next two solution possibilities.

Solution 7Activate forceliterals on the Query object
This will 'force the hand' of the AOS to accept the buffer allocation requirement, and allow your advanced query to run. Therefore allowing your QueryRun operation to finish as desired.
I did NOT apply this solution due to the documentation in the MSDN article:
You are advised not to use the forceLiterals keyword in X++ selectstatements, because it could expose code to an SQL injection security threat.
Solution 8Turn off Licence - Administration - Remove deprecated objects keys? [See also...]
While turning these key off does not remove the physical data from the database the query that selects a disabled field does not now receive that field in the results set.
The clever and handsome blog writer referenced above has also noted that the DEL_ fields may cause the buffer size to increase...  This approach involves unchecking the deprecated object key in the licence configuration will physically remove those fields from the database (unlike other licence keys in AX2012) with the SysDeletedObjects* configuration key - prior testing is therefore required, especially on customisations. Also, when considering how long it will take us to perform this operation it will be a  more 'efficient' operation than having to physically delete all of those DEL_ fields in each table in the VAR/CUS/USR layers.  My tests indicated that complex entities such as customers, suppliers, sales orders required the largest yet variable buffer sizes,..  Entities that initially had a buffer size requirement of 64KB dropped down to 49KB after removing the keys.  This is another huge saving, but keep in mind that in our client's case that they had all of the Keep update objects keys selected and was upgrading from AX2009.

As indicated above there is a risk for those of you who are migrating code from AX2009.  Entities such as ledger accounts, employees or product dimensions have changed and if we have customised entities referencing fields of these old entities we may lose data in the process.  Identify the changes after deselecting the above keys and refactor.  An MSDN support blog post recommends waiting for 3 months before deselecting these keys in a production environment.

Edit 12/2016: A senior technical consultant has just informed me that deselecting these deprecated object keys has enabled a 128 KB to 48 KB reduction in their buffer size.  That's seems too high to me, but I've decided that this solution is definately the one that will give us the most benefit.

Solution 9: Deactivate all non related country functionality in the licence file
Just when I was deselecting all of the 'Keep update...' keys I noticed the following in the licence configuration...

System administration > Setup > Licensing > License configuration.... "My God, it's full of stars"
It's ALL selected.  Every module, every country functionality, every everything!  The business Partner who can configure this particular AX installation would require a brain the size of a planet.
'Marvin', an AX consultant, can configure your system as he knows about Thai taxes, where to submit your US 1099 statement, can configure the Russian address book and understands the Spanish custom of 'the invoice due date says August but we'll  pay you in September'...
However like many that work in the ERP industry he is actually a maniacally depressed robot.
While deselecting the UK Reverse Tax rules or Russian facture documentation is a good thing for those of you who don't have a registered company in these countries what I've concentrated on here is specifically removing fields from the buffer.  Deselecting the multiple countries from the licence file will have the effect of physically removing fields from the database.  Let's take a look below at the first few fields of the Customer table.
All of the fields highlighted in yellow are related to country specific functionality.  Russia, Italy, Brazil and the BankCustPaymIdTable field that has a data type that is specific to Norway.  We can also see ACTMarkPriceRev in bold which is a field that exists in the VAR layer and I'm wondering if the end client still requires it's presence in the new version of AX?

No one will give you a pay-rise for doing this shit - But it's important.
The supplier form is down to 64KB!  Deleting a table from the join, removing unnecessary fields, deactivating many countries in the licence and deselected deprecated object keys has reduced the Suppliers form requirement from originally 83KB, to 79KB, then to 75KB and finally to 64KB.

It's a Sunday night and I'm working.
And 64 is such a lovely round number.
Let's call it a day.

Oh wait a minute...  I just deactivated Spain and I LIVE there.  Maybe you should talk to your business analyst before you start deactivating licence functionality?  Oh, and a compile of the application has produced errors with references to those DEL_ fields in the VAR layer that we just physically removed from the upgraded system.  Developer beware!
It's 2 a.m. and we`re nearly there.

Edit: 2 Months later...
Solution 10Reset/Clear your user's usage data.  If you can open the same row in the same form with a different user without problems, it might just work but I suspect that the solution is only temporary.  If the problem is a code migration like this one, try running out of work hours step 1 of the following blog post which performs a system flush.

jueves, 17 de septiembre de 2015

Package execution failed please check event log in DMF service box

I've been finding it difficult to locate some of our DIXF/DMF errors.  The below error occurs when we have a T-SQL database view as our data source, and we try to preview the data by an entity in a processing group, for example.

Error ES-ES: Error al ejecutar el paquete. Consulte el registro de eventos en el cuadro del servicio DMF.
Error EN-US: Package execution failed please check event log in DMF service box

We therefore need to move over to the server that is executing the data processing service, and check the event logs, origin 'DataMigrationImportExportFramework'.

DMF Service error: <Error><ErrorCode>-1071636471</ErrorCode><SubComponent>OLE DB Destination [40]</SubComponent><Description>Código de error SSIS DTS_E_OLEDBERROR. Error de OLE DB. Código de error: 0x80004005.
Hay un registro OLE DB disponible. Origen: "Microsoft OLE DB Provider for SQL Server" Resultado: 0x80004005 Descripción: "Error no especificado".
</Description></Error><Error><ErrorCode>-1071607780</ErrorCode><SubComponent>OLE DB Destination [40]</SubComponent><Description>Error con OLE DB Destination.Entradas[Entrada de destino de OLE DB].Columnas[Copy of TXT] en OLE DB Destination.Entradas[Entrada de destino de OLE DB]. Se devolvió el estado de columna: "El valor infringió las restricciones de integridad para la columna.".
</Description></Error>

Above I have highlighted the column that is causing the error.  In this case the database view was returning the TXT column without a value in one of the rows.  The error message is 'swallowed' by having so much architecture around the solution.

A mandatory column in the DMFDlvModeEntity, so no empty values please.
(But DlvMode.Txt is NOT mandatory)




viernes, 11 de septiembre de 2015

Be careful about what you name your new DIXF/DMF entities

After having ran our wizard for creating a new entity framework to import will be necessary to add the entity to the destiny entities table, below.

Entity name: Modelo de valor/grupo de activos fijos

Note, however the backslash that we have for the entity name, where it is taken directly from the form that manages these rows we wish to import.
This all works as expected, generating no warnings.  Also, we can now add the entity to a Processing Group with no problems.  It's the moment of actually obtaining the source data that generates the error.

System.IO.DirectoryNotFoundException: No se puede encontrar una parte de la ruta de acceso '\\Desrvsql2012\dmf\{B129C632-9B7D-40DC-860E-B7A1E120F01C}Modelo de valor\grupo de activos fijos..dtsx'.
It appears to have translated our name into part of a directory structure, and replaced the back slash with a forward slash.

Conclusion: Avoid these characters that may be either denied or misinterpreted by the Importation Exportation framework.

viernes, 26 de junio de 2015

Find Menu Items that call Jobs

During an upgrade from AX 2009 SP1 to AX 2012 R3 it is reasonable to assume that the AOT\Job node can be disregarded from the upgrade process.  That is until you find various menu items that point (ObjectType property) to those Jobs that have not been converted!
Menu Items are 99% of the time for launching Forms, Classes or Reports.
The first task is to find those menu items, and then to analyse if it's necessary to convert them.  Are they still relevant?  Finally, shoot the developer that is creating all of these Jobs were they should be creating classes for a more accessible and repeatable solution.

Below is a job...  To look for a Job ObjectType in the MenuItem node of the AOT.

static void Job_MenuItem_Jobs(Args _args)
{
    // Use X++ to Loop through the AOT for Menu Items that call....  Jobs!
    // http://blogs.msdn.com/b/dsiebold/archive/2010/08/13/use-x-to-loop-through-the-aot.aspx
    // http://dynamicsuser.net/forums/p/29232/154476.aspx#154476
    MenuItem    mi;

    void searchSubNodes (TreeNode _treeNode)
    {
        if (!_treeNode)
            return;

        _treeNode = _treeNode.AOTfirstChild();
        while (_treeNode)
        {
            if (_treeNode.AOTfirstChild())
            {
                searchSubNodes(_treeNode);
            }

            if (_treeNode.applObjectType())
            {
                mi = _treeNode;
                if (mi.AOTgetProperty("ObjectType") == "Job")
                {
                    info(strfmt("%1", _treeNode.treeNodePath()));
                }
            }
            _treeNode = _treeNode.AOTnextSibling();
        }
    }
    ;

    searchSubNodes (TreeNode::findNode(@"\Menu Items"));

    info("End");
}

This migration is going to be a long one.

viernes, 5 de junio de 2015

Error in a Definition Group export: "Same version of Microsoft Office Excel must be used for export and import."

Error - "Same version of Microsoft Office Excel must be used for export and import."

Se debe utilizar la misma versión de Microsoft Office Excel para exportar e importar.
I recall receiving the file from a Client to be imported via Excel/Definition Group.  There exists a KB2830391 that fixes this one, should you wish to address the issue permanently.

To quickly and painlessly resolve this issue, however I found a comment in the AXUG forum immensely useful:
"In excel file, on #DEF sheet, please change Excel Version to Office 2000, (strange, but it helps)."

Therefore I'd recommend exporting the empty Definition group (set the Export Criteria to a value that does not exist) and open up the Excel spreadsheet '#DEF' worksheet.  Compare that to the one that you have and probably it will be 'Excel Version' - 'Office2000'.  Paste the value to your spreadsheet giving you problems, and you would probably need to Save As the file as 'Excel 97 - 2003 (*.xls)'.
'Office 2000' - The secret sauce.

EDIT: 17/06/2015 - Second error now, with possibly a similar issue with Excel Versions.  Error:
"Method 'value' in COM object of class 'Range' returned error code 0x80020005 (DISP_E_TYPEMISMATCH) which means: One or more of the arguments could not be coerced."

One time out of twenty the spreadsheet would start to import data but that's a terrible success rate that is unlikely to complete when attempting thousands of records.
If you can even get this far, fall to your knees and start praying.

Solution:   Simply creating a blank Excel spreadsheet with the 'correct' Excel version and recreating each Tab with the tab name and selecting all of the data to copy across each tab.  We successfully imported the new spreadsheet from the data in the previous Excel workbook...  Also going outside for a coffee and giving each other a big hug may have helped with this particular issue (Hint: Don't touch a thing in the desktop environment while importing).

miércoles, 22 de abril de 2015

CLIENT FOUND RESPONSE CONTENT TYPE OF ”, BUT EXPECTED ‘TEXT/XML’.

I have just seen the following error on a production server, where no SSRS reports are successfully generated:

El cliente encontró el tipo de contenido de respuesta '', pero se esperaba 'text/xml'.
Error de la solicitud con una respuesta vacía.
The attempt to connect to the report server failed.  Check your connection information and that the report server is a compatible version.

The usual 'restart SQL Reporting Services' did not resolve the issue... This time.
Changing to the application configuration, business intelligence, reporting services parameters it was noticed that neither the Report manager nor the Web service urls worked,  They were indeed returning an empty response, aka ''.  It's therefore a pure reporting service or network error, and not an AX one as the parameters had not changed.

Finally the solution was found that the hard disk was full, and that no rows could be created in the tempdb database.  I presume other faults could produce this error, such as DNS changes, etc.

jueves, 1 de enero de 2015

AXUtil 2012 - s and db parameters

There are a few examples in blogs of the config switch for the AXUtil command.  However, I've not seen many that access the model store via the server and database switch.

Firstly, open up the Microsoft Dynamics AX 2012 Server Configuration and obtain the Server name with the Database name.

Server Configuration details
Let's change to the correct directory:
>CD \Program Files\Microsoft Dynamics AX\60\ManagementUtilities

Now, an example using the s and db switches:
AxUtil export /s:eszaractsql04\eszaractsql04 /db:zzzz_ax2012_db_prod_05_2014 /model:"VAR Model" /file:c:\temp\VARModel.axmodel

I assume that you will require access to the database, which is normally via the AOS service account.

Finally, an Error (0x80070005 E_ACCESSDENIED) can occur if we forget to add the path to the output file.