Are you referring to the Smith_PayHist.Status? So this gets updated to "Approved" if PayPal payment succeeds and "Declined" if the payment does not succeed, right? (NOTE: I see the Smith_PayHist.Notes field says "Awaiting Payment" - does this get updated as well?)
Are there any other possible values that get updated in the cart via PayPal IPN?
I could see the Smith_StoreOrderDetails.DateUpdated and Smith_StoreOrderDetails.Returned fields get updated whenever PayPal IPN sends data for the transaction. There is a ton of info coming from PayPal IPN so I'm hoping that future releases of SC includes updated data to help me create the reports I need.
Basically, I'm trying to create the report we discussed in the recent past to obtain sales order details. Just so you have an idea, here is my query (below).
SELECT o.OrderDate, od.DateUpdated, o.Status, od.Returned, ph.PayType, sc.FirstName + ' ' + sc.LastName AS [Customer Name]
, sc.Email, CAST(od.Quantity AS INT) AS [Quantity], od.ProductSKU, od.ProductName
FROM Smith_StoreOrders o
INNER JOIN Smith_Customer AS sc ON o.CustomerID = sc.CustomerID
INNER JOIN Smith_StoreOrderDetails od ON o.OrderID = od.OrderID
INNER JOIN Smith_PayHist ph ON o.PayHistID = ph.PayHistID
WHERE o.OrderDate >= @url_FromDate
AND o.OrderDate <= @url_ToDate
ORDER BY sc.Email, o.OrderDate
Yields somthig like this:
| 8/1/2012 9:40:48 PM |
|
Open |
|
CreditCard |
Test Customer |
TEST@DOMAIN.net |
1 |
S10158 |
Test Widget 1 |
| 8/1/2012 10:40:48 PM |
|
Open |
|
PAYPALSTD |
Test Customer |
TEST@DOMAIN.net |
1 |
BR549 |
Test Widget 2 |
| 8/1/2012 11:40:48 PM |
|
Open |
|
PAYPALSTD |
Test Customer |
TEST@DOMAIN.net |
1 |
FF1234 |
Test Widget 3 |
| 8/1/2012 1240:48 PM |
|
Open |
|
CreditCard |
Test Customer |
TEST@DOMAIN.net |
1 |
ZZZ987 |
Test Widget 4 |