Hi,
We’re trying to install AX 2009 - from start to finish - and that means going through the process of populating the cubes for the Role Centres.
There are documents which describe what you need to do when things are missing. Going through that - and numerous ‘tips’ from around the web, we’re now completely stuck with getting query CUSTTABLE to work. It has been modified from that which was supplied - just to get it to ‘lose’ about 20 errors.
Here is how the query looks at present.
SELECT CUSTTABLE.ACCOUNTNUM, CUSTTABLE.CASHDISC, CUSTTABLE.SHIPCARRIERBLINDSHIPMENT, CUSTTABLE.PARTYTYPE, CUSTTABLE.PARTYID,
CUSTTABLE.SHIPCARRIERACCOUNTCODE, CUSTTABLE.PROJPRICEGROUP, CUSTTABLE.GIROTYPEFREETEXTINVOICE, CUSTTABLE.SYNCENTITYID,
CUSTTABLE.SYNCVERSION, CUSTTABLE.MEMO, CUSTTABLE.SALESDISTRICTID, CUSTTABLE.CURRENCY, CUSTTABLE.SEGMENTID, CUSTTABLE.SUBSEGMENTID,
CUSTTABLE.RFIDITEMTAGGING, CUSTTABLE.RFIDCASETAGGING, CUSTTABLE.RFIDPALLETTAGGING, CUSTTABLE.COMPANYCHAINID, CUSTTABLE.MAINCONTACTID,
CUSTTABLE.INTERCOMPANYAUTOCREATEORDERS, CUSTTABLE.IDENTIFICATIONNUMBER, CUSTTABLE.PARTYCOUNTRY, CUSTTABLE.PARTYSTATE,
CUSTTABLE.SALESGROUP, CUSTTABLE.BLOCKED, CUSTTABLE.ONETIMECUSTOMER, CUSTTABLE.ACCOUNTSTATEMENT, CUSTTABLE.CREDITMAX,
CUSTTABLE.MANDATORYCREDITLIMIT, CUSTTABLE.DIMENSION, CUSTTABLE.DIMENSION2_, CUSTTABLE.DIMENSION3_, CUSTTABLE.NAME,
CUSTTABLE.VENDACCOUNT, CUSTTABLE.TELEX, CUSTTABLE.PRICEGROUP, CUSTTABLE.MULTILINEDISC, CUSTTABLE.ENDDISC, CUSTTABLE.VATNUM,
CUSTTABLE.COUNTRYREGIONID, CUSTTABLE.INVENTLOCATION, CUSTTABLE.DLVTERM, CUSTTABLE.DLVMODE, CUSTTABLE.ADDRESS,
CUSTTABLE.MARKUPGROUP, CUSTTABLE.CLEARINGPERIOD, CUSTTABLE.ZIPCODE, CUSTTABLE.STATE, CUSTTABLE.COUNTY, CUSTTABLE.URL,
CUSTTABLE.EMAIL, CUSTTABLE.CELLULARPHONE, CUSTTABLE.PHONELOCAL, CUSTTABLE.FREIGHTZONE, CUSTTABLE.PHONE, CUSTTABLE.CREDITRATING,
CUSTTABLE.TAXGROUP, CUSTTABLE.STATISTICSGROUP, CUSTTABLE.PAYMMODE, CUSTTABLE.COMMISSIONGROUP, CUSTTABLE.BANKACCOUNT,
CUSTTABLE.PAYMSCHED, CUSTTABLE.NAMEALIAS, CUSTTABLE.CONTACTPERSONID, CUSTTABLE.INVOICEADDRESS, CUSTTABLE.TELEFAX,
CUSTTABLE.OURACCOUNTNUM, CUSTTABLE.SALESPOOLID, CUSTTABLE.INVOICEACCOUNT, CUSTTABLE.INCLTAX, CUSTTABLE.CUSTITEMGROUPID,
CAST(FLOOR(CAST(custtable.modifieddatetime AS float)) AS datetime) AS modifieddatetime, CAST(FLOOR(CAST(custtable.createddatetime AS float)) AS datetime)
AS createddatetime, CUSTTABLE.DATAAREAID, CUSTTABLE.NUMBERSEQUENCEGROUP, CUSTTABLE.LANGUAGEID, CUSTTABLE.PAYMDAYID,
CUSTTABLE.LINEOFBUSINESSID, CUSTTABLE.RECID, CUSTTABLE.DESTINATIONCODEID, CUSTTABLE.GIROTYPE, CUSTTABLE.SUPPITEMGROUPID,
CUSTTABLE.GIROTYPEINTERESTNOTE, CUSTTABLE.CUSTGROUP, CUSTTABLE.TAXLICENSENUM, CUSTTABLE.WEBSALESORDERDISPLAY,
CUSTTABLE.PAYMSPEC, CUSTTABLE.BANKCENTRALBANKPURPOSETEXT, CUSTTABLE.BANKCENTRALBANKPURPOSECODE, CUSTTABLE.CITY,
CUSTTABLE.STREET, CUSTTABLE.PAGER, CUSTTABLE.SMS, CUSTTABLE.LINEDISC, CUSTTABLE.INTERCOMPANYALLOWINDIRECTCRE80,
CUSTTABLE.PBACUSTGROUPID, CUSTTABLE.PACKMATERIALFEELICENSENUM, CUSTTABLE.DLVREASON, CUSTTABLE.FORECASTDMPINCLUDE,
CUSTTABLE.GIROTYPECOLLECTIONLETTER, CUSTTABLE.PAYMTERMID, CUSTTABLE.SALESCALENDARID, CUSTTABLE.CUSTCLASSIFICATIONID,
CUSTTABLE.INTERCOMPANYDIRECTDELIVERY, CUSTTABLE.SHIPCARRIERACCOUNT, CUSTTABLE.GIROTYPEPROJINVOICE, CUSTTABLE.INVENTSITEID,
CUSTTABLE.ORDERENTRYDEADLINEGROUPID, CUSTTABLE.SHIPCARRIERID, CUSTTABLE.SHIPCARRIERFUELSURCHARGE,
DIRPARTYTABLE.PREFIX AS dirpartytable_prefix, DIRPARTYTABLE.PROFESSIONALSUFFIX AS dirpartytable_professionalsuffix,
DIRPARTYTABLE.FIRSTNAME AS dirpartytable_firstname, DIRPARTYTABLE.MIDDLENAME AS dirpartytable_middlename,
DIRPARTYTABLE.LASTNAME AS dirpartytable_lastname, DIRPARTYTABLE.PARTYID AS dirpartytable_partyid,
DIRPARTYTABLE.NAMEALIAS AS dirpartytable_namealias, DIRPARTYTABLE.MEMO AS dirpartytable_memo,
DIRPARTYTABLE.GENERATIONALSUFFIX AS dirpartytable_generationalsuffix, DIRPARTYTABLE.NAME AS dirpartytable_name,
DIRPARTYTABLE.SALUTATION AS dirpartytable_salutation, CAST(FLOOR(CAST(dirpartytable.modifieddatetime AS float)) AS datetime)
AS dirpartytable_modifieddatetime, CAST(FLOOR(CAST(dirpartytable.createddatetime AS float)) AS datetime) AS dirpartytable_createddatetime,
DIRPARTYTABLE.DATAAREAID AS dirpartytable_dataareaid, DIRPARTYTABLE.RECID AS dirpartytable_recid,
DIRPARTYTABLE.PERSONNAMEORDER AS dirpartytable_personnameorder, DIRPARTYTABLE.LANGUAGEID AS dirpartytable_languageid,
DIRPARTYTABLE.TYPE AS dirpartytable_type, COMMISSIONSALESGROUP.NAME AS commissionsalesgroup_name,
COMMISSIONSALESGROUP.GROUPID AS commissionsalesgroup_groupid, COMMISSIONSALESGROUP.DATAAREAID AS commissionsalesgroup_dataareaid,
COMMISSIONSALESGROUP.RECID AS commissionsalesgroup_recid, ADDRESSCOUNTRYREGION.COUNTRYREGIONID AS addresscountryregion_countryregionid,
ADDRESSCOUNTRYREGION.BANKACCOUNTNUMVALIDATION AS addresscountryregion_bankaccountnumvalidation,
ADDRESSCOUNTRYREGION.CURRENCYCODE AS addresscountryregion_currencycode,
ADDRESSCOUNTRYREGION.TAXVATNUMFORMATCHECK AS addresscountryregion_taxvatnumformatcheck,
ADDRESSCOUNTRYREGION.TIMEZONE AS addresscountryregion_timezone,
ADDRESSCOUNTRYREGION.ADDRESSUSEZIPPLUS4 AS addresscountryregion_addressusezipplus4,
ADDRESSCOUNTRYREGION.TYPE AS addresscountryregion_type, ADDRESSCOUNTRYREGION.TAXCODE AS addresscountryregion_taxcode,
ADDRESSCOUNTRYREGION.ISOCODE AS addresscountryregion_isocode, ADDRESSCOUNTRYREGION.ADDRFORMAT AS addresscountryregion_addrformat,
ADDRESSCOUNTRYREGION.NAME AS addresscountryregion_name, ADDRESSCOUNTRYREGION.DATAAREAID AS addresscountryregion_dataareaid,
ADDRESSCOUNTRYREGION.RECID AS addresscountryregion_recid, ADDRESSCOUNTRYREGION.INTRASTATCODE AS addresscountryregion_intrastatcode,
ADDRESSCOUNTRYREGION.GIROACCOUNTVALIDATIONMETHOD AS addresscountryregion_giroaccountvalidationmethod,
CUSTTABLE.CUSTGROUP.custgroup AS custgroup_custgroup, CUSTTABLE.CUSTGROUP.name AS custgroup_name,
CUSTTABLE.CUSTGROUP.clearingperiod AS custgroup_clearingperiod, CUSTTABLE.CUSTGROUP.paymtermid AS custgroup_paymtermid,
CUSTTABLE.CUSTGROUP.taxgroupid AS custgroup_taxgroupid, CUSTTABLE.CUSTGROUP.dataareaid AS custgroup_dataareaid,
CUSTTABLE.CUSTGROUP.recid AS custgroup_recid, custtable.custgroup.paymidtype AS custgroup_paymidtype, INVENTSITE.SITEID AS inventsite_siteid,
INVENTSITE.NAME AS inventsite_name, INVENTSITE.DIMENSION AS inventsite_dimension, INVENTSITE.DIMENSION2_ AS inventsite_dimension2_,
INVENTSITE.DIMENSION3_ AS inventsite_dimension3_, INVENTSITE.DATAAREAID AS inventsite_dataareaid, INVENTSITE.RECID AS inventsite_recid,
INVENTSITE.TIMEZONE AS inventsite_timezone, INVENTSITE.ORDERENTRYDEADLINEGROUPID AS inventsite_orderentrydeadlinegroupid,
SMMBUSRELCHAINGROUP.DATAAREAID AS smmbusrelchaingroup_dataareaid, SMMBUSRELCHAINGROUP.RECID AS smmbusrelchaingroup_recid,
SMMBUSRELCHAINGROUP.CHAINID AS smmbusrelchaingroup_chainid, SMMBUSRELCHAINGROUP.DESCRIPTION AS smmbusrelchaingroup_description,
SMMBUSRELSEGMENTGROUP.DATAAREAID AS smmbusrelsegmentgroup_dataareaid, SMMBUSRELSEGMENTGROUP.RECID AS smmbusrelsegmentgroup_recid,
SMMBUSRELSEGMENTGROUP.SEGMENTID AS smmbusrelsegmentgroup_segmentid,
SMMBUSRELSEGMENTGROUP.DESCRIPTION AS smmbusrelsegmentgroup_description, LINEOFBUSINESS.LINEOFBUSINESSID AS lineofbusiness_lineofbusinessid,
LINEOFBUSINESS.DESCRIPTION AS lineofbusiness_description, LINEOFBUSINESS.DATAAREAID AS lineofbusiness_dataareaid,
LINEOFBUSINESS.RECID AS lineofbusiness_recid, DESTINATIONCODE.DESTINATIONCODEID AS destinationcode_destinationcodeid,
DESTINATIONCODE.DESCRIPTION AS destinationcode_description, DESTINATIONCODE.DATAAREAID AS destinationcode_dataareaid,
DESTINATIONCODE.RECID AS destinationcode_recid
FROM dbo.CUSTTABLE LEFT OUTER JOIN
dbo.DIRPARTYTABLE ON DIRPARTYTABLE.DATAAREAID = CUSTTABLE.DATAAREAID AND DIRPARTYTABLE.PARTYID = CUSTTABLE.PARTYID LEFT OUTER JOIN
dbo.COMMISSIONSALESGROUP ON COMMISSIONSALESGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND
COMMISSIONSALESGROUP.GROUPID = CUSTTABLE.SALESGROUP LEFT OUTER JOIN
dbo.ADDRESSCOUNTRYREGION ON ADDRESSCOUNTRYREGION.DATAAREAID = CUSTTABLE.DATAAREAID AND
ADDRESSCOUNTRYREGION.COUNTRYREGIONID = CUSTTABLE.COUNTRYREGIONID LEFT OUTER JOIN
dbo.CUSTGROUP ON CUSTTABLE.CUSTGROUP.dataareaid = CUSTTABLE.DATAAREAID AND
CUSTTABLE.CUSTGROUP.custgroup = CUSTTABLE.CUSTGROUP LEFT OUTER JOIN
dbo.INVENTSITE ON INVENTSITE.DATAAREAID = CUSTTABLE.DATAAREAID AND INVENTSITE.SITEID = CUSTTABLE.INVENTSITEID LEFT OUTER JOIN
dbo.SMMBUSRELCHAINGROUP ON SMMBUSRELCHAINGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND
SMMBUSRELCHAINGROUP.CHAINID = CUSTTABLE.COMPANYCHAINID LEFT OUTER JOIN
dbo.SMMBUSRELSEGMENTGROUP ON SMMBUSRELSEGMENTGROUP.DATAAREAID = CUSTTABLE.DATAAREAID AND
SMMBUSRELSEGMENTGROUP.SEGMENTID = CUSTTABLE.SEGMENTID LEFT OUTER JOIN
dbo.LINEOFBUSINESS ON LINEOFBUSINESS.DATAAREAID = CUSTTABLE.DATAAREAID AND
LINEOFBUSINESS.LINEOFBUSINESSID = CUSTTABLE.LINEOFBUSINESSID LEFT OUTER JOIN
dbo.DESTINATIONCODE ON DESTINATIONCODE.DATAAREAID = CUSTTABLE.DATAAREAID AND
DESTINATIONCODE.DESTINATIONCODEID = CUSTTABLE.DESTINATIONCODEID
Should copy and paste ok…).
The query passes syntax check.
When it runs - it fails with :-
Cannot call methods on nvarchar.
Our environment:-
Windows Server 2008
SQL Server 2008
Dynamics AX 2009 SP1
Cient works ok
Role Centres - a few KPIs work - but Customers and Vendors not working yet - and they are pretty core.
Any ideas please?
Thanks,
P