Query used to found Sales Difference between iVend & SAP in Consolidation

Query used to found Sales Difference between iVend & SAP in Consolidation

*******Internal to CXS Support***********

 Environment :

iVend 6.6  integrated With SAPB1

Problem Statement :

Sales Difference between iVend & SAP against particular date transacation.Customer is using Transaction consolidation.

Symptoms: NA

Resolution/Work Around:

Below are the query we can used to trace issue

Query to find no of transaction of particular date

Query1:-

select count(distinct TransactionKey ) from trxtransaction with (nolock) where
IsVoided='0' and IsSuspended='0' and BusinessDate>='Put Problemtical date' and BusinessDate<= 'Put Problemtical date'

Example
select count(distinct TransactionKey ) from trxtransaction with (nolock) where

IsVoided='0' and IsSuspended='0' and BusinessDate>='2022-03-28' and BusinessDate<= '2022-03-28'

Query2:

Query to find to check all transaction of particular date exists in RepIntegrationLog

select count(distinct SourceKey  )from RepIntegrationLog with (nolock) where  sourcetype='143' and sourcekey in (

select TransactionKey from trxtransaction with (nolock) where

IsVoided='0' and IsSuspended='0' and BusinessDate>= 'Put Problemtical date' and BusinessDate<= 'Put Problemtical date')

Example:
select count(distinct SourceKey  )from RepIntegrationLog with (nolock) where  sourcetype='143' and sourcekey in (

select TransactionKey from trxtransaction with (nolock) where

IsVoided='0' and IsSuspended='0' and BusinessDate>= '2022-03-28' and BusinessDate<= '2022-03-28')

 Query3:-

Query to find all transaction exists in transaction Table are part of consolidation or not

select count (distinct TransactionKey) from ConTrxTransactionMapping with (nolock)

where TransactionKey in (select TransactionKey  from trxtransaction with (nolock) where

IsVoided='0' and IsSuspended='0' and BusinessDate>= 'Put Problemtical date' and BusinessDate<= 'Put Problemtical date')

Example:-select count (distinct TransactionKey) from ConTrxTransactionMapping with (nolock)

where TransactionKey in (select TransactionKey  from trxtransaction with (nolock) where --CustomerKey='91f5e265-041b-4e20-bf7a-cd86d168816e'

IsVoided='0' and IsSuspended='0' and BusinessDate>= '2022-03-28' and BusinessDate<= '2022-03-28')

Note:-If count of query1,quey2,query3 are same its means all transaction are part of consolidation and present in RepIntegrationlog

Query4:-

Query to find no of consolidated transaction key against particular date

select distinct ConsolidatedTransactionKey from ConTrxTransactionMapping with (nolock)

where TransactionKey in (select TransactionKey  from trxtransaction with (nolock) where --CustomerKey='91f5e265-041b-4e20-bf7a-cd86d168816e'

IsVoided='0' and IsSuspended='0' and BusinessDate>= 'Put Problemtical date' and BusinessDate<= 'Put Problemtical date')

Example:
select distinct ConsolidatedTransactionKey from ConTrxTransactionMapping with (nolock)
where TransactionKey in (select TransactionKey  from trxtransaction with (nolock) where --CustomerKey='91f5e265-041b-4e20-bf7a-cd86d168816e'

IsVoided='0' and IsSuspended='0' and BusinessDate>= '2022-03-28' and BusinessDate<= '2022-03-28')

Query5:-

Query to find no of productkey  against consolidation transaction matched with trxtransaction sale item based on  ConsolidatedTransactionKey from query4

select count(distinct productkey) from ConTrxTransactionSaleItem where transactionkey='ConsolidatedTransactionKey from query4'

Example:
select count(distinct productkey) from ConTrxTransactionSaleItem where transactionkey='0159B693-68F0-42FB-A3F2-5C84C81BB63A' 

Query6:-

select count (distinct productkey)  from TrxTransactionSaleItem where transactionkey in (

select  TransactionKey from ConTrxTransactionMapping

where ConsolidatedTransactionKey='ConsolidatedTransactionKey from query4)

Example:
select count (distinct productkey)  from TrxTransactionSaleItem where transactionkey in (

select  TransactionKey from ConTrxTransactionMapping

where ConsolidatedTransactionKey='0159B693-68F0-42FB-A3F2-5C84C81BB63A')

Note:-If query 5 and query 6 count mismatch its means there are some transaction missing in ConTrxTransactionSaleItem

Query7:-

Below query to find missing product on consolidation against particular consolidation

select distinct ProductKey into #1 from TrxTransactionSaleItem with (nolock) where transactionkey in (

select distinct TransactionKey from ConTrxTransactionMapping with (nolock)

where ConsolidatedTransactionKey='ConsolidatedTransactionKey from query4')

select * from #1 where productkey not in (

select ProductKey from ConTrxTransactionSaleItem with (nolock) where transactionkey='ConsolidatedTransactionKey from query4')

Example:-
select distinct ProductKey into #1 from TrxTransactionSaleItem with (nolock) where transactionkey in (

select distinct TransactionKey from ConTrxTransactionMapping with (nolock)

where ConsolidatedTransactionKey='0159B693-68F0-42FB-A3F2-5C84C81BB63A')

select * from #1 where productkey not in (

select ProductKey from ConTrxTransactionSaleItem with (nolock) where transactionkey='0159B693-68F0-42FB-A3F2-5C84C81BB63A')

Query8:

Query used to fetch missing data against productkey found from query7 and consolidation key from query4
select * from ConTrxTransactionSaleItem with (nolock) where transactionkey='ConsolidatedTransactionKey from query4'
and ProductKey in (productkey from query7)

select * from TrxTransactionSaleItem where transactionkey in (

select distinct TransactionKey from ConTrxTransactionMapping with (nolock)

where ConsolidatedTransactionKey='0159B693-68F0-42FB-A3F2-5C84C81BB63A')

and ProductKey in (productkey from query7))

Example:-
select * from ConTrxTransactionSaleItem with (nolock) where transactionkey='0159B693-68F0-42FB-A3F2-5C84C81BB63A'

and ProductKey in ('e17b3b41-0a13-414a-8943-3f6ea87cc6be')

select * from TrxTransactionSaleItem where transactionkey in (

select distinct TransactionKey from ConTrxTransactionMapping with (nolock)

where ConsolidatedTransactionKey='0159B693-68F0-42FB-A3F2-5C84C81BB63A')

and ProductKey in ('e17b3b41-0a13-414a-8943-3f6ea87cc6be')

Provide user data from sale table according to requirement of user  to create Ar invoice manually on downtime after stop Intgeration and ignore inventory impact

Query8:-

select b.id,a.* from TrxTransactionSaleItem a, invproduct b where a.ProductKey=b.ProductKey  and transactionkey in (

select distinct TransactionKey from ConTrxTransactionMapping with (nolock)

where ConsolidatedTransactionKey='ConsolidatedTransactionKey from query4')

and a.ProductKey in (productkey from query7')

Example:-
select b.id,a.* from TrxTransactionSaleItem a, invproduct b where a.ProductKey=b.ProductKey  and transactionkey in (

select distinct TransactionKey from ConTrxTransactionMapping with (nolock)

where ConsolidatedTransactionKey='61E3CCEF-9BEE-47F7-AC8F-17B1AC714515')

and a.ProductKey in ('e17b3b41-0a13-414a-8943-3f6ea87cc6be')
 
Recommendations /Suggestion: This article is created only for Support team.