Showing transition stages of a request from audit table
Hi friends,
I have a table "matrl_reqst" and i have the reqst_id, qty, date_created, and status, along with other columns.
I have oracle CDC audit enabled on this table and each transition state of "status" is captured in.
Some stages are repetitive, like, once the department raises request and allocated to a buyer, the buyer returns the request for more information to the department.
Again, the department submits and the buyer go ahead with purchase, if all are clear.
Now, i want to show the data & visualization of each request, with all stages in a single line and i too need to capture the time interval between each stage.
I have planned to do below:
1. Load the first table "matrl_reqst"
2. Load audit table with "Noconcatenate". Because, i dont want my main table to end up with so many transition records
3. Construct final dataset, using MIN() function.
But with the above method, i have to do lot of hard-coding of stages as columns and write lot of code for each stage and have to compromise on "returning" to the first time return since the number of returns are not predictable.
Appreciate your help to achieve this in an easier way.
