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 ...Access to iVend cloud HO Database
Environment: iVend 6.6 cloud with SAPB1 9.2 Problem statement: Partner/Customer wants access to iVend HO/Enterprise database hosted on cloud for their internal purposes. Symptoms: NA Resolution/Work Around: With iVend Cloud Service,Partner/customer ...