BIP – Query to find list of positions against a department

There are times when we just need a quick query to count number of positions created in HCM against a department. Below is a sample SQL which can be handy in such situations:

select * 
  from hr_all_positions_f hapf
 where hapf.organization_id IN (select distinct organization_id 
                                  from HR_ORGANIZATION_UNITS_F_TL houft
                                 where ( LIKE 'Test%'
                                     OR LIKE 'Abc%'