Error A reservation hierarchy must be set up for the item. You can create transactions for the item after a reservation hierarchy has been set up

Error:- If you are getting error "A reservation hierarchy must be set up for the item. You can create transactions for the item after a reservation hierarchy has been set up" at the time of Calculate/Posting Statement

Solution:- Run below procedure into HQ Database

USE [ProdDynamicsAX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[ReservationHierarchyNotFound] (@frodate as datetime,@storeid as int) as
select itemid from  RETAILTRANSACTIONSALESTRANS where transdate=@frodate and store=@storeid and itemid in (select itemid from InventTable where itemid not in (select itemid from WHSRESERVATIONHIERARCHYITEM) )

Exec ReservationHierarchyNotFound  '2018-09-26','011'

How to Change Retail Store Name HQ and Store

Scenario :-  If you want to change your Retail Channel Store Number which is already running with different  Number.

Steps:-  Go to the Retail - Common -Retail Channels--Retail Stores Right Click on Store Number Go to the Record Info Click on Rename , this process will take time to rename in all the transaction but in only HQ (AX).

Change Name in Store Database run below query into the SQL

AX-- In case sync data from store to HQ after Rename

delete from  RETAILTRANSACTIONTAXTRANSGTE where storeid='AFCOURT' and transactionid in

(select transactionid from RETAILTRANSACTIONSALESTRANS where store='AFCOURT' and transdate='2018/08/24')

delete from RETAILTRANSACTIONINFOCODETRANS where store='AFCOURT' and transdate='2018/08/24'

delete from RETAILTRANSACTIONPAYMENTTRANS where store='AFCOURT'  and transdate='2018/08/24'

delete from  RETAILTRANSACTIONSALESTRANS  where store ='AFCOURT' and transdate='2018/08/24'

delete from RETAILTRANSACTIONTABLE where store='AFCOURT' and transdate='2018/08/24'

POS 

update RETAILTRANSACTIONSALESTRANS set store='011' where store='AFCOURT' and transdate='2018/08/24'

update RETAILTRANSACTIONTABLE set store='011' where store='AFCOURT' and transdate='2018/08/24'

update RETAILTRANSACTIONINFOCODETRANS set store='011' where store='AFCOURT' and transdate='2018/08/24'

update RETAILTRANSACTIONTAXTRANSGTE set storeid='011' where transactionid in (

select transactionid from  RETAILTRANSACTIONTAXTRANSGTE   where  transactionid

in (select transactionid from RETAILTRANSACTIONSALESTRANS where store='011' and transdate='2018/08/24'))

or Create Procedure

USE [ProdDynamicsAX]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[RCT_RetailStoreNameChange] (@transdate as datetime) as

update [192.168.2.55].PRODFCT011.ax.RETAILTRANSACTIONSALESTRANS set store='011' where store='AFCOURT' and transdate= @transdate

update [192.168.2.55].PRODFCT011.ax.RETAILTRANSACTIONTABLE set store='011' where store='AFCOURT' and transdate= @transdate

update [192.168.2.55].PRODFCT011.ax.RETAILTRANSACTIONINFOCODETRANS set store='011' where store='AFCOURT' and transdate= @transdate

update [192.168.2.55].PRODFCT011.ax.RETAILTRANSACTIONTAXTRANSGTE set storeid='011' where transactionid in (

select transactionid from  [192.168.2.55].PRODFCT011.ax.RETAILTRANSACTIONTAXTRANSGTE   where  transactionid 

in (select transactionid from [192.168.2.55].PRODFCT011.ax.RETAILTRANSACTIONSALESTRANS where store='011' and transdate=@transdate))

delete from  RETAILTRANSACTIONTAXTRANSGTE where storeid='AFCOURT' and transactionid in 

 (select transactionid from RETAILTRANSACTIONSALESTRANS where store='AFCOURT' and transdate=@transdate)

delete from RETAILTRANSACTIONINFOCODETRANS where store='AFCOURT' and transdate=@transdate

delete from RETAILTRANSACTIONPAYMENTTRANS where store='AFCOURT'  and transdate=@transdate

delete from  RETAILTRANSACTIONSALESTRANS  where store ='AFCOURT' and transdate=@transdate

delete from RETAILTRANSACTIONTABLE where store='AFCOURT' and transdate= @transdate

Retail Statement Calculating/Posting Error " Unknown Or Incorrectly selected return lot."

Problem :- Retail Statement Calculating/Posting Error " Unknown or incorrectly Selected return lot "

Cause :- This problem comes due to partial statement posting or got disconnect while calculating statement.

Solution:- Remove ReturnTransactionId from RetailTransactionSalesTrans, After Calculating and Posting ,again you have to update that ReturnTransactionId into RetailTransactionSalesTrans with same record.

SQL Query :- Below query you have to run on your HQ database

1.  Select Returntransactionid,* From Retailtransactionsalestrans Where Itemid='1242A9991000018' And Dataareaid='1006' and STATEMENTID ='002-000376'

2. Update Retailtransactionsalestrans Set Returntransactionid='' Where Itemid='1242A9991000018' And Dataareaid='1006' And Receiptid='00910420000017' and STATEMENTID ='002-000376'

3. Update Retailtransactionsalestrans Set Returntransactionid='009-T-087-11926' Where Itemid='1242A9991000018' And Dataareaid='1006' And Receiptid='00910420000017' and STATEMENTID ='002-000376'

The Quantity Being Returned is greater than what can be covered by the return lot

Problem :- Facing Error at the time of calculating Retail Statement "The Quantity Being Returned is greater than what can be covered by the return lot."

Solution :- Comment Image code then calculate the statement. 

How To Delete and Re Post Retail Statement

Problem :- Getting retail statement calculation error "Insufficient Inventory Transactions With Status On Order,Reserved Ordered Reserved Physical And Picked."

Cause :- this error comes due to partial calculation or partial posted statement.

Solution:- Run below procedure into the HQ database, then clear the statement and again calculate statement.

USE [ProdDynamicsAX]
GO
/****** Object:  StoredProcedure [dbo].[RCT_ReverseStatement]    Script Date: 09-10-2018 07:37:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[ReverseStatement] (@dataareaid  nvarchar(50),@stmtid nvarchar(50), @storeid nvarchar(50) )as

--declare @dataareaid  nvarchar(50)
--set @dataareaid='1001'
--declare @stmtid   nvarchar(50)
--set @stmtid='AFCOURT-000051'
--declare  @storeid   nvarchar(50) 
--set @storeid = '011'


update retailtransactiontable set SALESORDERID = '' where statementid = @stmtid and type = 2 and store = @storeid and recid in 
(select recid from retailtransactiontable where statementid = @stmtid and type = 2 and store = @storeid  )

update RETAILSTATEMENTTABLE set POSTINGDATE = '' where STATEMENTID = @stmtid and recid in 
(select recid from RETAILSTATEMENTTABLE  where STATEMENTID = @stmtid )

UPDATE RETAILTRANSACTIONTABLE SET STATEMENTID ='', ENTRYSTATUS = 0,SALESORDERID = '',INVOICEID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND ENTRYSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONTABLE where  DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND ENTRYSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONTABLE SET STATEMENTID ='', SALESORDERID = '',INVOICEID = '' 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND ENTRYSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONTABLE where  DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND ENTRYSTATUS in (1,3) )


UPDATE RETAILTRANSACTIONSALESTRANS SET STATEMENTID ='', INVENTTRANSID = '', TRANSACTIONSTATUS = 0, INVENTSTATUSSALES = 0,COSTAMOUNT = 0, INVENTDIMID = '' 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONSALESTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONSALESTRANS SET STATEMENTID ='', INVENTTRANSID = '', INVENTSTATUSSALES = 0, COSTAMOUNT = 0, INVENTDIMID = '' 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONSALESTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))


UPDATE RETAILTRANSACTIONPAYMENTTRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0,STATEMENTCODE  = ''
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONPAYMENTTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3))


UPDATE RETAILTRANSACTIONPAYMENTTRANS SET STATEMENTID = '', STATEMENTCODE  = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONPAYMENTTRANS where DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))


UPDATE RETAILTRANSACTIONBANKEDTENDERTRANS SET STATEMENTID = '',TRANSACTIONSTATUS = 0 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONBANKEDTENDERTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
 AND TRANSACTIONSTATUS not in (1,3))


UPDATE RETAILTRANSACTIONBANKEDTENDERTRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONBANKEDTENDERTRANS where  DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))


UPDATE RETAILTRANSACTIONSAFETENDERTRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONSAFETENDERTRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
 AND TRANSACTIONSTATUS not in (1,3))

UPDATE RETAILTRANSACTIONSAFETENDERTRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) 
AND TRANSACTIONSTATUS in (1,3)
and recid in (select recid from  RETAILTRANSACTIONSAFETENDERTRANS where  DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
AND TRANSACTIONSTATUS in (1,3))

UPDATE RETAILTRANSACTIONINCOMEEXPENSETRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 
WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINCOMEEXPENSETRANS where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) 
AND TRANSACTIONSTATUS not in (1,3) )


UPDATE RETAILTRANSACTIONINCOMEEXPENSETRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINCOMEEXPENSETRANS WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) )

UPDATE RETAILTRANSACTIONORDERINVOICETRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONORDERINVOICETRANS where  DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONORDERINVOICETRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONORDERINVOICETRANS WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) )

UPDATE RETAILTRANSACTIONINFOCODETRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0 WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINFOCODETRANS where DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) )

UPDATE RETAILTRANSACTIONINFOCODETRANS SET STATEMENTID = '' WHERE DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
(select recid from RETAILTRANSACTIONINFOCODETRANS where DATAAREAID = @dataareaid 
AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) )

UPDATE RETAILTRANSACTIONTENDERDECLARATIONTRANS SET STATEMENTID = '', TRANSACTIONSTATUS = 0, 
StatementCode = '' WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS not in (1,3) and recid in
(select recid from RETAILTRANSACTIONTENDERDECLARATIONTRANS where  DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid)
 AND TRANSACTIONSTATUS not in (1,3))

UPDATE RETAILTRANSACTIONTENDERDECLARATIONTRANS SET STATEMENTID = '', StatementCode = '' WHERE
 DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3) and recid in 
 (select recid from RETAILTRANSACTIONTENDERDECLARATIONTRANS where  DATAAREAID = @dataareaid AND 
 STATEMENTID in (@stmtid) AND TRANSACTIONSTATUS in (1,3))

DELETE RETAILSTATEMENTJOUR WHERE DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) and recid in 
(select recid from RETAILSTATEMENTJOUR where DATAAREAID = @dataareaid AND STATEMENTID in (@stmtid) )

DELETE RETAILSTATEMENTTRANS WHERE DATAAREAID = @dataareaid AND  STATEMENTID in (@stmtid) and recid in
(select recid from RETAILSTATEMENTTRANS where  DATAAREAID = @dataareaid AND  STATEMENTID in (@stmtid))

Delete RETAILSTATEMENTVOUCHER where STATEMENTID in (@stmtid) and recid in 
(select recid from RETAILSTATEMENTVOUCHER  where STATEMENTID in (@stmtid))

Exec Reversestatement '1001','afcourt-000037','011'