Tracing order payment stuck in a state other than PAID in Sterling Order Management

Get the order header key value:

1. Run the following SQL query to get the order header key value if you know the order number:

select ORDER_HEADER_KEY from YFS_ORDER_HEADER where ORDER_NO='WC_999999'

Enable tracing on requestCollection and executeCollection APIs:

1. Open the Application console (https:///smcfs/console/start.jsp)
2. Go to System -> Launch System Management
3. In the System Management Console, go to Tools -> Trace components
4. Click the Add button
5. Choose API for Component Type
6. Choose requestCollection for Component Name
7. Choose VERBOSE for Trace Level
8. Click the Apply button
9. Click the Add button
10. Choose API for Component Type
11. Choose executeCollection for Conponent Name
12. Choose VERBOSE for Trace Level
13. Click on the Apply button

Try to process the order:

1. Open the API tester (https:///smcfs/yfshttpapi/yantrahttpapitester.jsp)
2. In the API Name field, choose requestCollection
3. In the UserId field, enter the same user name that you used to log into the Application or OM console
4. In the Password field, enter the corresponding password
5. In the Message box, enter the following where the value of OrderHeaderKey is the value of ORDER_HEADER_KEY found from the SQL query at the top of this post:



6. Click on Test API Now! button
7. If you get an exception or error in the resulting screen, copy this down
8. If you get no error repeat the above steps except choose executeCollection as the API Name
9. If you still don't get an error or exception after running executeCollection, re-run the above steps for requestCollection again

Turn off the trace by removing the trace entries for requestCollection and executeCollection.
The verbose traces are located in the OM logs directory with a file name of sci.log.Dyyyymmdd.Tnnnnnn where yyyymmdd is the date and nnnnnn is a number that usually starts with 000000 and increments up for each successive log on the same day

Useful DB2 queries agaiinst the Sterling Order Management database

Just some useful DB2 SQL queries done directly against the Sterling Order Management database:

List of orders in a particular payment state (AUTHORIZED, INVOICED, PAID):

select ORDER_NO,ORDER_DATE from YFS_ORDER_HEADER where PAYMENT_STATUS='INVOICED'

List of orders and their payment status made after a certain date:

select T1.ORDER_NO,T2.PAYMENT_TYPE,T1.ORDER_DATE,T1.PAYMENT_STATUS from YFS_ORDER_HEADER T1,YFS_PAYMENT T2 where T1.ORDER_DATE > '2015-05-28' and T1.ORDER_HEADER_KEY=T2.ORDER_HEADER_KEY order by T1.ORDER_DATE

List of payment transactions for an order if you know the order number:

select * from YFS_CHARGE_TRANSACTION where ORDER_HEADER_KEY in (select ORDER_HEADER_KEY from YFS_ORDER_HEADER where ORDER_NO='WC_999999') order by EXECUTION_DATE