Is Orders.Status = "S" an order in pending state

[skinnybloke]

Hi - just doing some work on SELECT queries. I want to output a list of pending orders to a file. Having a look at the tables it looks to me that if Orders.Status = "S" then the order is in PENDING state.

Can anyone confirm this? Is there anything else I should be checking?

[Bruce]

David,

The Status of record on Orders are as follows, N = normal, F = finished (only used for Orders currently) and S = shipped, default N

I would suggest looking in the 'nPaymentStatus' field where Enumeration detailing payment status is as follows...
0 – OCC Pending
1 – Accepted/Rec’d
2 – Rejected
3 – Error
4. –Pre-Authorised
5 – Waiting for CC details
6 – Pending
8 – Test mode
9 – Partial PaymentReceived.
10 – Full Payment Received
11- Excess Paymrnt Received.
12 – Payment Sent
Default: 0

Kind regards,

[skinnybloke]

Hi Bruce - thanks for that but it does not seem to be working for me.

The items I want to print out are those I have downloaded and are sitting under the ORDERS > PENDING tab in the Actinic program

I have set my SELECT WHERE statement to be WHERE nPaymentStatus = 6; - see below:

SELECT Null AS oldaccno, Person.Name AS name, Person.[Address Line 1] AS addressline1, Person.[Address Line 2] AS addressline2, Person.[Address Line 3] AS town, Person.[Address Line 4] AS county, Person.[Address Country] AS country, Person.[Postal Code] AS postcode, Person.[Email Address] AS email, Person.[Phone Number] AS telno
FROM Person INNER JOIN [Order] ON Person.ContactID = Order.InvoiceContactID
WHERE Order.nPaymentStatus = 6;

The above statement picks up 2 records of which one is in my actinic pending folding and one is in my actinic completed folder yet ignores a second record in my pending folder.


Record 1 is picked up correctly - this has Status = S, npaymentstatus = 6

Record 2 is picked up incorrectly - this has Status = F, npaymentstatus = 6

Record 3 is ignored - this has Status = S, npaymentstatus = 10

[SureshB]

Hi David,

I have forwarded this issue to the development team to investigate further. I reckon that we would need to make few changes to the Query in order to export the Pending orders alone.

I'll keep you posted.

[SureshB]

Hi David,

Just got back from the Development team.

Try this:

SELECT Null AS oldaccno, Person.Name AS name, Person.[Address Line 1] AS addressline1, Person.[Address Line 2] AS addressline2, Person.[Address Line 3] AS town, Person.[Address Line 4] AS county, Person.[Address Country] AS country, Person.[Postal Code] AS postcode, Person.[Email Address] AS email, Person.[Phone Number] AS telno
FROM Person INNER JOIN [Order] ON Person.ContactID = Order.InvoiceContactID
WHERE Order.nPaymentStatus = 6 AND Order.[Status] <> "F";

[T2EKKIE]

Hi Bruce,

Just got question around a similar area to this post.

We have started using protx as a payment method and have been following the information in the database regarding npaymentstatus etc, however, the status for denied/abandoned orders always comes through as 0 - OCC pending.

does actinic contain any information that you know about which will enable us to distinguish between protx payments which have not been authorized and payments which have been abandoned?


Thanks

[Bruce]

PROTX now uses 3D secure, if you sign up for it, you will always get a transaction report for not authorised payments. The value comes down as '0' for both types because it only checks to see if there has been a payment made.

Kind regards.