It sounds like you guys have a solution, but I wanted a POC for this that we could check into our library, so I came up with this…
SQL> create or replace type expr_skulist_type as object(skulist number,
2 item varchar2(25),
3 int_action_date date,
4 int_action_type varchar2(1),
5 tran_level number,
6 item_level number);
7 /
Type created.
SQL> create or replace function expr_skulists return skulists pipelined as
2 l_expr_skulist_type expr_skulist_type;
3 begin
4 for r in (select skulist,src.item,int_action_date,int_action_type, src.tran_level, src.item_level
5 from expr_integration.stg_out_skulist_detail src
6 inner join rms14.item_master on(src.item=item_master.item)
7 where item_master.status ='A'
8 --and src.int_action_date > to_timestamp('2016-11-15 14:28:57', 'YYYY-MM-DD HH24:MI:SS')
9 and rownum <= 10) loop
10 l_expr_skulist_type := expr_skulist_type(r.skulist,r.item,r.int_action_date,r.int_action_type,r.tran_level,r.item_level);
11 pipe row(l_expr_skulist_type);
12 end loop;
13 end;
14 /
Function created.
SQL> select * from table(expr_skulists());
SKULIST ITEM INT_ACTIO I TRAN_LEVEL ITEM_LEVEL
---------- ------------------------- --------- - ---------- ----------
1044 75991377 02-AUG-16 A 2 2
1044 75996974 02-AUG-16 A 2 2
1044 75996987 02-AUG-16 A 2 2
1044 75996990 02-AUG-16 A 2 2
1044 75997009 02-AUG-16 A 2 2
1044 75997025 02-AUG-16 A 2 2
1044 75997038 02-AUG-16 A 2 2
1044 75997041 02-AUG-16 A 2 2
1044 75997054 02-AUG-16 A 2 2
1044 75997070 02-AUG-16 A 2 2
10 rows selected.
SQL>
The trick is to sort the select, and as you work your way through the dataset, check if the skulist and item has changed, “reset” your logic. As you process a given skulist and item, you can calculate the logic as you process each row. For example, is the int_action_date_greater the previous date? Have you already seen tran_level = 1 and this one is 2? Based on what you find, you either call the pipe row() line, or not.
Thanks,
Steve