I just upgraded our AX 2012 R3 development environment with cumulative update 12 (CU12) and after compiling, synchronization fails on one table with this error :
Description de l'erreur SQL: [Microsoft][SQL Server Native Client 11.0][SQL Server]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LEDGERPERIODMODULEACCESSCONTROL' and the index name 'I_7375LEDGERFISCALCALENDARPERIODIDX'. The duplicate key value is (5637144576, 5637144576).
Instruction SQL: CREATE UNIQUE INDEX I_7375LEDGERFISCALCALENDARPERIODIDX ON "DBO".LEDGERPERIODMODULEACCESSCONTROL (PARTITION,LEDGERFISCALCALENDARPERIOD)
FIX
To fix this issue all you need to do is take a backup of LEDGERPERIODMODULEACCESSCONTROL table and truncate the data from SQL. Once the data is truncated run the following job to fix the data
static void LEDGERPERIODMODULEACCESSCONTROL_DATAFIXJob(Args _args)
{
LedgerPeriodModuleAccessControl newPeriodAccessControl, oldPeriodAccessControl;
LedgerFiscalCalendarPeriod ledgerPeriods;
// Build up a new version of the table that will be flattened from the source records.
// BP documented
newPeriodAccessControl.setTmp();
// Build a list of ledger fiscal periods
insert_recordset newPeriodAccessControl
(LedgerFiscalCalendarPeriod)
select RecId from ledgerPeriods;
// Update temp table with already-converted values if any exist
update_recordset newPeriodAccessControl
setting LedgerAccessLevel = oldPeriodAccessControl.LedgerAccessLevel,
LedgerUserGroupInfo = oldPeriodAccessControl.LedgerUserGroupInfo,
TaxAccessLevel = oldPeriodAccessControl.TaxAccessLevel,
TaxUserGroupInfo = oldPeriodAccessControl.TaxUserGroupInfo,
BankAccessLevel = oldPeriodAccessControl.BankAccessLevel,
BankUserGroupInfo = oldPeriodAccessControl.BankUserGroupInfo,
CustAccessLevel = oldPeriodAccessControl.CustAccessLevel,
CustUserGroupInfo = oldPeriodAccessControl.CustUserGroupInfo,
VendAccessLevel = oldPeriodAccessControl.VendAccessLevel,
VendUserGroupInfo = oldPeriodAccessControl.VendUserGroupInfo,
SalesAccessLevel = oldPeriodAccessControl.SalesAccessLevel,
SalesUserGroupInfo = oldPeriodAccessControl.SalesUserGroupInfo,
PurchAccessLevel = oldPeriodAccessControl.PurchAccessLevel,
PurchUserGroupInfo = oldPeriodAccessControl.PurchUserGroupInfo,
InventAccessLevel = oldPeriodAccessControl.InventAccessLevel,
InventUserGroupInfo = oldPeriodAccessControl.InventUserGroupInfo,
ProdAccessLevel = oldPeriodAccessControl.ProdAccessLevel,
ProdUserGroupInfo = oldPeriodAccessControl.ProdUserGroupInfo,
ProjectAccessLevel = oldPeriodAccessControl.ProjectAccessLevel,
ProjectUserGroupInfo = oldPeriodAccessControl.ProjectUserGroupInfo,
FixedAssetsAccessLevel = oldPeriodAccessControl.FixedAssetsAccessLevel,
FixedAssetsUserGroupInfo = oldPeriodAccessControl.FixedAssetsUserGroupInfo,
PayrollAccessLevel = oldPeriodAccessControl.PayrollAccessLevel,
PayrollUserGroupInfo = oldPeriodAccessControl.PayrollUserGroupInfo,
ExpenseAccessLevel = oldPeriodAccessControl.ExpenseAccessLevel,
ExpenseUserGroupInfo = oldPeriodAccessControl.ExpenseUserGroupInfo,
FixedAssets_RUAccessLevel = oldPeriodAccessControl.FixedAssets_RUAccessLevel,
FixedAssets_RUUserGroupInfo = oldPeriodAccessControl.FixedAssets_RUUserGroupInfo,
RetailAccessLevel = oldPeriodAccessControl.RetailAccessLevel,
RetailUserGroupInfo = oldPeriodAccessControl.RetailUserGroupInfo,
CostingAccessLevel = oldPeriodAccessControl.CostingAccessLevel,
CostingUserGroupInfo = oldPeriodAccessControl.CostingUserGroupInfo
join oldPeriodAccessControl
where newPeriodAccessControl.LedgerFiscalCalendarPeriod == oldPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::None; // Converted records have a module value of none - 0
// Update temp table with non-converted values if any exist
update_recordset newPeriodAccessControl
setting LedgerAccessLevel = oldPeriodAccessControl.AccessLevel,
LedgerUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Ledger;
update_recordset newPeriodAccessControl
setting TaxAccessLevel = oldPeriodAccessControl.AccessLevel,
TaxUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Tax;
update_recordset newPeriodAccessControl
setting BankAccessLevel = oldPeriodAccessControl.AccessLevel,
BankUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Bank;
update_recordset newPeriodAccessControl
setting CustAccessLevel = oldPeriodAccessControl.AccessLevel,
CustUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Cust;
update_recordset newPeriodAccessControl
setting VendAccessLevel = oldPeriodAccessControl.AccessLevel,
VendUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Vend;
update_recordset newPeriodAccessControl
setting SalesAccessLevel = oldPeriodAccessControl.AccessLevel,
SalesUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Sales;
update_recordset newPeriodAccessControl
setting PurchAccessLevel = oldPeriodAccessControl.AccessLevel,
PurchUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Purch;
update_recordset newPeriodAccessControl
setting InventAccessLevel = oldPeriodAccessControl.AccessLevel,
InventUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Invent;
update_recordset newPeriodAccessControl
setting ProdAccessLevel = oldPeriodAccessControl.AccessLevel,
ProdUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Prod;
update_recordset newPeriodAccessControl
setting ProjectAccessLevel = oldPeriodAccessControl.AccessLevel,
ProjectUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Project;
update_recordset newPeriodAccessControl
setting FixedAssetsAccessLevel = oldPeriodAccessControl.AccessLevel,
FixedAssetsUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::FixedAssets;
update_recordset newPeriodAccessControl
setting PayrollAccessLevel = oldPeriodAccessControl.AccessLevel,
PayrollUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Payroll;
update_recordset newPeriodAccessControl
setting ExpenseAccessLevel = oldPeriodAccessControl.AccessLevel,
ExpenseUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Expense;
update_recordset newPeriodAccessControl
setting FixedAssets_RUAccessLevel = oldPeriodAccessControl.AccessLevel,
FixedAssets_RUUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::FixedAssets_RU;
update_recordset newPeriodAccessControl
setting RetailAccessLevel = oldPeriodAccessControl.AccessLevel,
RetailUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Retail;
update_recordset newPeriodAccessControl
setting CostingAccessLevel = oldPeriodAccessControl.AccessLevel,
CostingUserGroupInfo = oldPeriodAccessControl.UserGroupInfo
join oldPeriodAccessControl
where oldPeriodAccessControl.LedgerFiscalCalendarPeriod == newPeriodAccessControl.LedgerFiscalCalendarPeriod
&& oldPeriodAccessControl.ModuleName == SysModule::Costing;
// Clear old table and insert the new converted values.
delete_from oldPeriodAccessControl;
insert_recordset oldPeriodAccessControl
(LedgerFiscalCalendarPeriod,
LedgerAccessLevel, LedgerUserGroupInfo,
TaxAccessLevel, TaxUserGroupInfo,
BankAccessLevel, BankUserGroupInfo,
CustAccessLevel, CustUserGroupInfo,
VendAccessLevel, VendUserGroupInfo,
SalesAccessLevel, SalesUserGroupInfo,
PurchAccessLevel, PurchUserGroupInfo,
InventAccessLevel, InventUserGroupInfo,
ProdAccessLevel, ProdUserGroupInfo,
ProjectAccessLevel, ProjectUserGroupInfo,
FixedAssetsAccessLevel, FixedAssetsUserGroupInfo,
PayrollAccessLevel, PayrollUserGroupInfo,
ExpenseAccessLevel, ExpenseUserGroupInfo,
FixedAssets_RUAccessLevel, FixedAssets_RUUserGroupInfo,
RetailAccessLevel, RetailUserGroupInfo,
CostingAccessLevel, CostingUserGroupInfo)
select LedgerFiscalCalendarPeriod,
LedgerAccessLevel, LedgerUserGroupInfo,
TaxAccessLevel, TaxUserGroupInfo,
BankAccessLevel, BankUserGroupInfo,
CustAccessLevel, CustUserGroupInfo,
VendAccessLevel, VendUserGroupInfo,
SalesAccessLevel, SalesUserGroupInfo,
PurchAccessLevel, PurchUserGroupInfo,
InventAccessLevel, InventUserGroupInfo,
ProdAccessLevel, ProdUserGroupInfo,
ProjectAccessLevel, ProjectUserGroupInfo,
FixedAssetsAccessLevel, FixedAssetsUserGroupInfo,
PayrollAccessLevel, PayrollUserGroupInfo,
ExpenseAccessLevel, ExpenseUserGroupInfo,
FixedAssets_RUAccessLevel, FixedAssets_RUUserGroupInfo,
RetailAccessLevel, RetailUserGroupInfo,
CostingAccessLevel, CostingUserGroupInfo
from newPeriodAccessControl;
info("Done");
}
No comments:
Post a Comment