A Move Order in Oracle Apps has statuses like Incomplete, Approved, or Pre Approved, etc. Oracle maintains information about move order in MTL_TXN_REQUEST_HEADERS and MTL_TXN_REQUEST_LINES tables. The first is the header table and the second is the lines table.
HEADER_STATUS is the column in MTL_TXN_REQUEST_HEADERS and LINE_STATUS in the MTL_TXN_REQUEST_LINES table which shows the header and line status. When you query these tables and see values, you find values like 1,2…7, 8, 9, etc.
How do we know the move order header and line status from these tables? What does header status 1 mean? Does it mean Complete, Cancelled?
Let’s find it out.
Oracle apps maintain a lookup with the name MTL_TXN_REQUEST_STATUS which stores values and meanings of the move order status.
You can use the below Query to show all the move order status codes and meanings.
SELECT lookup_code , meaning FROM fnd_lookup_values_vl WHERE lookup_type = 'MTL_TXN_REQUEST_STATUS' ORDER BY lookup_code
Output
| Lookup Code | Meaning |
| 1 | Incomplete |
| 2 | Pending Approval |
| 3 | Approved |
| 4 | Not Approved |
| 5 | Closed |
| 6 | Canceled |
| 7 | Pre Approved |
| 8 | Partially Approved |
| 9 | Canceled by Source |
The same lookup is used to maintain mapping for Header and Line status.
SELECT mtrh.request_number
, mtrh.header_status
, flvh.meaning header_status_meaning
, mtrl.line_status
, flvl.meaning line_status_meaning
FROM mtl_txn_request_headers mtrh
, mtl_txn_request_lines mtrl
, fnd_lookup_values_vl flvh
, fnd_lookup_values_vl flvl
WHERE mtrh.header_id = mtrl.header_id
AND flvh.lookup_type = 'MTL_TXN_REQUEST_STATUS'
AND flvh.lookup_code = mtrh.header_status
AND flvl.lookup_type = 'MTL_TXN_REQUEST_STATUS'
AND flvl.lookup_code = mtrl.line_status
AND flvh.enabled_flag = 'Y'
AND TRUNC( SYSDATE) BETWEEN NVL(flvh.start_date_active, TRUNC( SYSDATE)) AND NVL(flvh.end_date_active, TRUNC( SYSDATE))
AND flvl.enabled_flag = 'Y'
AND TRUNC( SYSDATE) BETWEEN NVL(flvl.start_date_active, TRUNC( SYSDATE)) AND NVL(flvl.end_date_active, TRUNC( SYSDATE))
That’s it for this article. I hope you found this helpful. Please share and subscribe.
