Move Order Header and Line Status (1,2,3…9) in Oracle Apps

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))

Oracle Move Order Header and Line Status query output

That’s it for this article. I hope you found this helpful. Please share and subscribe.