• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:July 21, 2023
  • Reading time:1 mins read
You are currently viewing How to fetch Positions without Costing Information?

In this article, let’s try to fetch the positions which doesn’t have the costing information setup.

SQL Query to fetch Positions without Costing:

SELECT hapf.position_code, hapft.name
  FROM HR_ALL_POSITIONS_F hapf
	  ,HR_ALL_POSITIONS_F_TL hapft
 WHERE 1=1
   AND hapft.position_id = hapf.position_id
   and not exists (select 'x' from PAY_COST_ALLOCATIONS_F pacf
                                  ,PAY_COST_ALLOC_ACCOUNTS pcaa
				    where  pacf.cost_allocation_record_id = pcaa.cost_allocation_record_id 
                      AND pacf.source_type = 'POS' 
                      AND pacf.source_id = hapf.position_id
					  and TRUNC(SYSDATE) BETWEEN pacf.effective_start_date AND pacf.effective_end_date
                      AND hapft.language = 'US')
   AND TRUNC(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date