And do Pipelined function cache results so that the results could be re-used in more than one place in a procedure..?
No, of course not. I explained a complete scenario how to implement this using pipeline to gain performance without using any temporary tables. And we need to use collections to build the array, in this case a VARRAY.
The ultimate goal is to fetch the rows before the last row is fetched skipping the intermediate stage of loading into temporary table. And when used in parallel it does it's job quite well.
There are several ways to achieve the task, however, "it depends" on the scenario.
Yes, your almost there
select count(1) into VARIABLE from TABLE(t_employee_ids);