on 2024 Jan 04 8:22 AM
I need to find inconsistent transactions through a query. I looked into CCO Manager tables but I could not find a way to differentiate between consistent and inconsistent transactions. I can see them through Checkout Monitor but there are hundreds of inconsistent transactions and it will take a long time to do it manually. I want to take material IDs (item codes) out of these inconsistent transactions. Is there any way?
Request clarification before answering.
Hi aftab25,
have a look at this:
SELECT
SI.MaterialID,
SI.Quantity,
SI.NetAmount,
SI.GrossAmount,
SI.TaxAmount,
POSR.TotalTaxAmount,
SI.TaxCode,
SI.QuantityTypeCode,
POSR.DiscountAmount,
POSR.DiscountPercentage,
POSR.CashDeskID,
POSR.CashierID,
POSR.ExternalID,
POSR.ReleaseStatusCode AS "Status",
DATEADD(SECOND, POSR.BusinessTransactionDateTime, '1970-01-01') AS "Datum",
PI.PaymentFormCode,
EL.ErrorMessage
FROM
dbo.SalesItem SI
INNER JOIN dbo.PointOfSaleReceipt POSR ON SI.ReceiptKey = POSR.POSKey
INNER JOIN dbo.PaymentDetails PD ON POSR.POSKey = PD.ReceiptKey
INNER JOIN dbo.PaymentItem PI ON PD.PaymentDetailsKey = PI.PayDetailsKey
INNER JOIN dbo.ErrorLog EL ON EL.ReceiptKey = POSR.POSKey
WHERE
POSR.ReleaseStatusCode = 'INC'
ORDER BY
POSR.BusinessTransactionDateTime;
Adjust for your needs!
HTH
Robert
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Robert,
Thank you but I don't have ReleaseStatusCode field. I see just STATUS field in Receipt table and it has 2 for consistent transactions as well as inconsistent transactions. Similarly I dont see ErrorLog table in my CCO Manager db. We are on B1 integration and CCOM is SQL Server based.
User | Count |
---|---|
19 | |
17 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.