In this article, we will try to fetch the Position Costing information using SQL Query.
If you want to know how to load Position Costing, please refer to this article.
After loading the data, if you want to reconcile or extract the data, you can use the below query:
SELECT to_char (pca.effective_start_date
,'RRRR-MM-DD') effective_start_date
,to_char (pca.effective_end_date
,'RRRR-MM-DD') effective_end_date
,pca.source_type costting_source
,p.name Position_Name
,p.position_code Position_Code
,pcaa.source_sub_type costing_type
,pcaa.proportion
,pcaa.segment1
,pcaa.segment2
,pcaa.segment3
,pcaa.segment4
,pcaa.segment5
,pcaa.segment6
,pcaa.segment7
,(pcaa.segment1
|| '-'
|| pcaa.segment2
|| '-'
|| pcaa.segment3
|| '-'
|| pcaa.segment4
|| '-'
|| pcaa.segment5
|| '-'
|| pcaa.segment6
|| '-'
|| pcaa.segment7) combined_value
FROM pay_cost_allocations_f pca
,pay_cost_alloc_accounts pcaa
,hr_all_positions_f_vl p
WHERE pca.source_type = 'POS'
AND pca.cost_allocation_record_id = pcaa.cost_allocation_record_id
AND pca.source_id = p.position_id
AND pca.effective_start_date BETWEEN p.effective_start_date AND p.effective_end_date
