In this article we will look into how to extract the Element Eligibility and costing information.
Costing Information will be stored in pay_cost_allocations_f and pay_cost_alloc_accounts tables. Based on the source_type the costing information refers to different business objects. Like ‘EL’ for Element Links, ‘ASG’ for Assignment, ‘PAY’ for Payroll, ‘ORG’ for Organization and ‘PREL’ for Payroll Relationship.
Costing Info Query:
select
pelf.element_link_id,
decode(caa.source_sub_type,'COST','Cost Account','BAL','Offset Account','Unknown-'||caa.source_sub_type) as acct_type,
caa.segment1 as company,
caa.segment2 as department,
caa.segment3 as account,
caa.segment4 as intercompany,
caa.segment5 as future1,
caa.segment6 as future2,
caa.proportion
from
pay_cost_allocations_f ca,
pay_cost_alloc_accounts caa,
pay_element_links_f pelf
where
sysdate between CA.effective_start_date and CA.effective_end_date
and ca.source_type ='EL'
and CAA.cost_allocation_record_id = CA.cost_allocation_record_id
and pelf.element_link_id = ca.source_id
and sysdate between pelf.effective_start_date and pelf.effective_end_dateNow we have to link this costing information with the element eligibility tagged to elements.
Element Costing Query:
with cost_info as (
select
pelf.element_link_id,
decode(caa.source_sub_type,'COST','Cost Account','BAL','Offset Account','Unknown-'||caa.source_sub_type) as acct_type,
caa.segment1 as company,
caa.segment2 as department,
caa.segment3 as account,
caa.segment4 as intercompany,
caa.segment5 as future1,
caa.segment6 as future2,
caa.proportion
from
pay_cost_allocations_f ca,
pay_cost_alloc_accounts caa,
pay_element_links_f pelf
where
sysdate between CA.effective_start_date and CA.effective_end_date
and ca.source_type ='EL'
and CAA.cost_allocation_record_id = CA.cost_allocation_record_id
and pelf.element_link_id = ca.source_id
and sysdate between pelf.effective_start_date and pelf.effective_end_date
)
select
legislative_data_group,
element_name,
reporting_name,
element_effective_start_date,
element_effective_end_date,
primary_classification,
secondary_classification,
element_eligibility,
eligibility_start_date,
eligibility_end_date,
costing,
costable_type,
transfer_to_gl_flag,
Costed_Input,
COST_COMPANY,
COST_DEPARTMENT,
COST_ACCOUNT,
COST_INTERCOMPANY,
OFFSET_COMPANY,
OFFSET_DEPARTMENT,
OFFSET_ACCOUNT,
OFFSET_INTERCOMPANY
from (
select
nvl(ldg.name,'None') as legislative_data_group,
pet.element_name,
pet.reporting_name,
to_char(pet.effective_start_date,'MM/DD/YYYY') AS element_effective_start_date,
to_char(pet.effective_end_date,'MM/DD/YYYY') AS element_effective_end_date,
(select distinct class.classification_name
from pay_ele_classifications_vl class
where class.classification_id = pet.classification_id) as primary_classification,
(select distinct class.classification_name
from pay_ele_classifications_vl class
where class.classification_id = pet.secondary_classification_id) as secondary_classification,
pel.element_link_name as element_eligibility,
to_char(pel.effective_start_date,'MM/DD/YYYY') as eligibility_start_date,
to_char(pel.effective_end_date,'MM/DD/YYYY') as eligibility_end_date,
nvl((select distinct nvl2(pci.costed_flag,'Yes','No')
from pay_link_input_values_f pelv, PAY_COST_INFO_F pci
where pci.source_id = pelv.link_input_value_id
and pet.effective_start_date between pci.effective_start_date and pci.effective_end_date
and pelv.element_link_id = pel.element_link_id
and pet.effective_start_date between pelv.effective_start_date and pelv.effective_end_date
and pci.source_type = 'LIV'), 'No') as costing,
pivf.base_name Costed_Input,
hr_general.decode_lookup ('PAY_COSTABLE_TYPE', pcif.costable_type) costable_type,
pcif.transfer_to_gl_flag,
COST.company COST_COMPANY,
COST.department COST_DEPARTMENT,
COST.account COST_ACCOUNT,
COST.intercompany COST_INTERCOMPANY,
OFFSET.company OFFSET_COMPANY,
OFFSET.department OFFSET_DEPARTMENT,
OFFSET.account OFFSET_ACCOUNT,
OFFSET.intercompany OFFSET_INTERCOMPANY
from
pay_element_types_vl pet,
per_legislative_data_groups_vl ldg,
pay_element_links_f pel,
pay_cost_info_f pcif,
--pay_link_input_values_f pelv,
pay_input_values_f pivf,
(select * from cost_info where acct_type = 'Cost Account') COST,
(select * from cost_info where acct_type = 'Offset Account') OFFSET,
(select pelv.element_link_id, pelv.input_value_id
from pay_link_input_values_f pelv, PAY_COST_INFO_F pci
where pci.source_id = pelv.link_input_value_id
and sysdate between pci.effective_start_date and pci.effective_end_date
and sysdate between pelv.effective_start_date and pelv.effective_end_date
and pci.source_type = 'LIV') Costed_Inputs
where 1=1
and pet.legislative_data_group_id = ldg.legislative_data_group_id (+)
and pcif.source_id(+) = pel.element_link_id
and pcif.source_type(+) = 'EL'
and COST.element_link_id(+) = pel.element_link_id
and OFFSET.element_link_id(+) = pel.element_link_id
and Costed_Inputs.element_link_id(+) = pel.element_link_id
and pel.element_type_id (+) = pet.element_type_id
and pet.effective_start_date between pcif.effective_start_date (+) and pcif.effective_end_date (+)
and pet.effective_start_date between pel.effective_start_date (+) and pel.effective_end_date (+)
and Costed_Inputs.input_value_id = pivf.input_value_id(+)
and pet.effective_start_date between pivf.effective_start_date(+) and pivf.effective_end_date(+)
)
where 1=1
and costing = 'Yes'
and legislative_data_group = 'US Legislative Data Group'
and element_eligibility is not null
order by element_name, element_eligibilityThis query will pull only the elements which have the costing information populated at the element eligibility level.
Trending Content
