Query to find out missing Transaction in SAPB1 from iVend HO

FOR INTERNAL PURPOSE

Support Level :

SQL query to find missing Transaction in SAPB1 from iVend HO :

Sales

SELECT * FROM RepIntegrationLog WHERE ApplicationKey = '1' AND SourceType = 143 AND Flag = 1 AND SourceKey IN (

Select TransactionKey As SourceKey from TrxTransaction where IsSuspended=0 and HasSales=1 and TransactionKey not in

( Select U_CXS_TRKY from [HANA32]..[HQJ_PLUMBING_SUPPLIES]."OINV" WHERE ISNULL(U_CXS_TRKY,'') <> ''))

Refunds

SELECT * FROM RepIntegrationLog WHERE ApplicationKey = '1' AND SourceType = 143 AND Flag = 1 AND SourceKey IN (

Select TransactionKey As SourceKey from TrxTransaction where IsSuspended=0 and HasRefunds=1

and TransactionKey not in ( Select U_CXS_TRKY from [HANA32]..[HQJ_PLUMBING_SUPPLIES]."ORIN" WHERE ISNULL(U_CXS_TRKY,'') <> ''))

Special Order

SELECT (SELECT TransactionId From TrxTransaction (nolock) where TransactionKey = x.SourceKey) as TransactionId,

* FROM RepIntegrationLog (nolock)x WHERE ApplicationKey = '1' AND SourceType = 143 AND Flag = 1 AND SourceKey IN (

Select TransactionId As SourceKey from TrxTransaction (nolock)where IsSuspended=0 and HasOrders=1 and TransactionKey not in

( Select U_CXS_TRID from [HANA32]..[HQJ_PLUMBING_SUPPLIES]."ORDR" WHERE ISNULL(U_CXS_TRKY,'') <> ''))

SALE with FullFillment

SELECT * FROM RepIntegrationLog WHERE ApplicationKey = '1' AND SourceType = 143 AND Flag = 1 AND SourceKey IN (

Select TransactionKey As SourceKey from TrxTransaction where IsSuspended=0 and HasSales = 1 and HasFulFillments=1 and TransactionKey not in

(Select U_CXS_TRKY from [HANA32]..[HQJ_PLUMBING_SUPPLIES]."OINV" WHERE ISNULL(U_CXS_TRKY,'') <> ''))

Delivery Of Special Order

SELECT * FROM RepIntegrationLog WHERE ApplicationKey = '1' AND SourceType = 143 AND Flag = 1 AND SourceKey IN (

Select distinct a.TransactionKey As SourceKey

from TrxTransaction (nolock) a inner join TrxTransactionSaleItem (nolock) b on a.TransactionKey = b.TransactionKey and b.Type = 3

where a.IsSuspended=0 and a.HasDeliveries=1 and a.TransactionKey not in

(Select U_CXS_TRKY from [HANA32]..[HQJ_PLUMBING_SUPPLIES]."OINV" WHERE ISNULL(U_CXS_TRKY,'') <> ''))

Delivery Of Sale With Fullfillment

SELECT * FROM RepIntegrationLog WHERE ApplicationKey = '1' AND SourceType = 143 AND Flag = 1 AND SourceKey IN (

Select distinct a.TransactionKey As SourceKey

from TrxTransaction (nolock) a inner join TrxTransactionSaleItem (nolock) b on a.TransactionKey = b.TransactionKey and b.Type = 4

where a.IsSuspended=0 and a.HasDeliveries=1 and a.TransactionKey not in

(Select U_CXS_TRKY from [HANA32]..[HQJ_PLUMBING_SUPPLIES]."ODLN" WHERE ISNULL(U_CXS_TRKY,'') <> ''))

 Sales Transaction - Invoice

SELECT sale.TransactionKey, prod.Id as ItemCode, Ware.Id as WhsCode,

saleitem.Quantity, tax.Id, sale.Tax, sale.TotalTaxableAmount as price

FROM TrxTransactionSaleItem (nolock) saleitem

inner join TrxTransaction (nolock) sale on saleitem.TransactionKey = sale.TransactionKey

inner join InvProduct (nolock) prod on saleitem.ProductKey = prod.ProductKey

inner join InvWarehouse (nolock) Ware on saleitem.WarehouseKey = ware.WarehouseKey

inner join TaxTaxCode (nolock) tax on saleitem.TaxCodeKey = tax.TaxCodeKey

inner join RepIntegrationLog (nolock) RIL on sale.TransactionKey = RIL.SourceKey

WHERE saleitem.Type = 0 AND sale.IsVoided = 0 AND sale.IsSuspended = 0 AND RIL.Flag =1 AND

saleitem.TransactionItemKey NOT IN (SELECT U_CXS_TDKY FROM [HANA32]..[HQJ_PLUMBING_SUPPLIES]."INV1"

where ISNULL(U_CXS_TDKY,'') <> '')

order by saleitem.TransactionKey

Refund Transaction

SELECT sale.TransactionKey, prod.Id as ItemCode, Ware.Id as WhsCode,

saleitem.Quantity, tax.Id, sale.Tax, sale.TotalTaxableAmount as price

FROM TrxTransactionSaleItem (nolock) saleitem

inner join TrxTransaction (nolock) sale on saleitem.TransactionKey = sale.TransactionKey

inner join InvProduct (nolock) prod on saleitem.ProductKey = prod.ProductKey

inner join InvWarehouse (nolock) Ware on saleitem.WarehouseKey = ware.WarehouseKey

inner join TaxTaxCode (nolock) tax on saleitem.TaxCodeKey = tax.TaxCodeKey

inner join RepIntegrationLog (nolock) RIL on sale.TransactionKey = RIL.SourceKey

WHERE saleitem.Type = 1 AND sale.IsVoided = 0 AND sale.IsSuspended = 0 AND RIL.Flag =1 AND

saleitem.TransactionItemKey NOT IN (SELECT U_CXS_TDKY FROM [HANA32]..[HQJ_PLUMBING_SUPPLIES]."RIN1"

where ISNULL(U_CXS_TDKY,'') <> '')

order by saleitem.TransactionKey

Special Order

SELECT sale.TransactionKey, prod.Id as ItemCode, Ware.Id as WhsCode,

saleitem.Quantity, tax.Id, sale.Tax, sale.TotalTaxableAmount as price

FROM TrxTransactionSaleItem (nolock) saleitem

inner join TrxTransaction (nolock) sale on saleitem.TransactionKey = sale.TransactionKey

inner join InvProduct (nolock) prod on saleitem.ProductKey = prod.ProductKey

inner join InvWarehouse (nolock) Ware on saleitem.WarehouseKey = ware.WarehouseKey

inner join TaxTaxCode (nolock) tax on saleitem.TaxCodeKey = tax.TaxCodeKey

inner join RepIntegrationLog (nolock) RIL on sale.TransactionKey = RIL.SourceKey

WHERE saleitem.Type = 1 AND sale.IsVoided = 0 AND sale.IsSuspended = 0 AND sale.HasOrders =1 AND RIL.Flag =1 AND

saleitem.TransactionItemKey NOT IN (SELECT U_CXS_TDKY FROM [HANA32]..[HQJ_PLUMBING_SUPPLIES]."RDR1"

where ISNULL(U_CXS_TDKY,'') <> '')

order by saleitem.TransactionKey

SALE with FullFillment

SELECT sale.TransactionKey, prod.Id as ItemCode, Ware.Id as WhsCode,

saleitem.Quantity, tax.Id, sale.Tax, sale.TotalTaxableAmount as price

FROM TrxTransactionSaleItem (nolock) saleitem

inner join TrxTransaction (nolock) sale on saleitem.TransactionKey = sale.TransactionKey

inner join InvProduct (nolock) prod on saleitem.ProductKey = prod.ProductKey

inner join InvWarehouse (nolock) Ware on saleitem.WarehouseKey = ware.WarehouseKey

inner join TaxTaxCode (nolock) tax on saleitem.TaxCodeKey = tax.TaxCodeKey

inner join RepIntegrationLog (nolock) RIL on sale.TransactionKey = RIL.SourceKey

WHERE saleitem.TYPE=1 AND sale.IsVoided = 0 AND sale.IsSuspended = 0 AND sale.HasSales =1 AND SALE.HasFulFillments=1 AND RIL.Flag =1 AND

saleitem.TransactionItemKey NOT IN (SELECT U_CXS_TDKY FROM [SAP-iVend-DemoStack].dbo."RDR1"

where ISNULL(U_CXS_TDKY,'') <> '')

order by saleitem.TransactionKey

Delivery Of Special Order

SELECT sale.TransactionKey, prod.Id as ItemCode, Ware.Id as WhsCode,

saleitem.Quantity, tax.Id, sale.Tax, sale.TotalTaxableAmount as price

FROM TrxTransactionSaleItem (nolock) saleitem

inner join TrxTransaction (nolock) sale on saleitem.TransactionKey = sale.TransactionKey

inner join InvProduct (nolock) prod on saleitem.ProductKey = prod.ProductKey

inner join InvWarehouse (nolock) Ware on saleitem.WarehouseKey = ware.WarehouseKey

inner join TaxTaxCode (nolock) tax on saleitem.TaxCodeKey = tax.TaxCodeKey

inner join RepIntegrationLog (nolock) RIL on sale.TransactionKey = RIL.SourceKey

WHERE saleitem.Type = 3 AND sale.IsVoided = 0 AND sale.IsSuspended = 0 AND RIL.Flag =1 AND

saleitem.TransactionItemKey NOT IN (SELECT U_CXS_TRKY FROM [HANA32]..[HQJ_PLUMBING_SUPPLIES]."INV1"

where ISNULL(U_CXS_TRKY,'') <> '')

order by saleitem.TransactionKey

Delivery Of Sale With Fullfillment

SELECT sale.TransactionKey, prod.Id as ItemCode, Ware.Id as WhsCode,

saleitem.Quantity, tax.Id, sale.Tax, sale.TotalTaxableAmount as price

FROM TrxTransactionSaleItem (nolock) saleitem

inner join TrxTransaction (nolock) sale on saleitem.TransactionKey = sale.TransactionKey

inner join InvProduct (nolock) prod on saleitem.ProductKey = prod.ProductKey

inner join InvWarehouse (nolock) Ware on saleitem.WarehouseKey = ware.WarehouseKey

inner join TaxTaxCode (nolock) tax on saleitem.TaxCodeKey = tax.TaxCodeKey

inner join RepIntegrationLog (nolock) RIL on sale.TransactionKey = RIL.SourceKey

WHERE saleitem.Type = 4 AND sale.IsVoided = 0 AND sale.IsSuspended = 0 AND RIL.Flag =1 AND

saleitem.TransactionItemKey NOT IN (SELECT U_CXS_TRKY FROM [HANA32]..[HQJ_PLUMBING_SUPPLIES]."DLN1"

where ISNULL(U_CXS_TRKY,'') <> '')

order by saleitem.TransactionKey

• Related Articles

• Query about Sale Delivery Transaction

  Environment: iVend 6.6 Product Query: There is a sale that is generated at store 2 and then a fulfillment plan has been added at Store 1. After that sale transaction delivery is fulfilled at Store 1, And finally sale return transaction has been made ...

• Company Logo Missing from Email Transaction Receipt

  Environment: iVend 6.6 7915 & SAP Business One 9.3 PL 15 Problem Statement: Configured the Email Receipt in Retail Profile, however after performing the transaction on POS Company Logo is printing from the printer, but Logo is missing from Email ...

• Find reason Code captured for line discount in table " TrxTransactionAudit "

  Environment : iVend Version 6.6 with SAPB1 10 Query: How to find reason Code captured for line discount in table " TrxTransactionAudit " Symptoms : How to find reason Code captured for line discount in table " TrxTransactionAudit " Resolution/Work ...

• Alternate way to find out transaction id in iVend from the data in the Integration Failure Log

  Product version: iVend 6.6 with SAPB1 9.2 Problem statement: Alternate way to find out transaction id in iVend from the data in the Integration Failure Log Symptoms: Alternate way to find out transaction id in iVend from the data in the Integration ...

• 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 ...