You are currently viewing SQL query to fetch Position Costing information
SQL query to fetch Position Costing information

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
       , Position_Name
       ,p.position_code Position_Code
       ,pcaa.source_sub_type costing_type
        || '-'
        || 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