PAY Costing Detail

pcd.payroll_action_id = to_number(substr(:p_costing_name,instr(:p_costing_name,':',-1,1)+1,length(:p_costing_name)))

exists
(
select
pes.element_set_id
from
pay_element_sets pes,
pay_element_sets_tl pestl
where 
(pes.business_group_id=fnd_global.per_business_group_id or 
 (pes.business_group_id is null and 
  pes.legislation_code=
  (select legislation_code
   from per_business_groups
   where business_group_id= fnd_global.per_business_group_id
  )
 )
) and 
pes.element_set_type='C' and 
pestl.element_set_id=pes.element_set_id and 
pestl.language=userenv('LANG') and
pestl.element_set_name = :p_element_set_name and
exists
 (select 'x' 
  from   pay_element_type_rules petr
  where  petr.element_set_id = pes.element_set_id
  and    petr.element_type_id = pcd.element_type_id
  and    petr.include_or_exclude = 'I'
  union all
  select 'x' 
  from   pay_element_types_f pet1
  where  pet1.classification_id in
   (select classification_id
    from   pay_ele_classification_rules
    where  element_set_id = pes.element_set_id
   )
  and pet1.element_type_id = pcd.element_type_id
  minus
  select 'x' 
  from   pay_element_type_rules petr
  where  petr.element_set_id = pes.element_set_id
  and    petr.element_type_id = pcd.element_type_id
  and    petr.include_or_exclude = 'E'
 )
)

pcd.classification_id in
(
select
pec.classification_id 
from
pay_element_classifications pec
where 
nvl(pec.business_group_id,:p_business_group_id) = :p_business_group_id and 
(   pec.legislation_code is null or 
    pec.legislation_code in 
         (select hoi.org_information9
           from  hr_organization_information hoi
           where hoi.organization_id = :p_business_group_id
           and   hoi.org_information_context='Business Group Information'
         )
) and 
pec.classification_name = :p_classification_name
)

pcd.element_type_id in
(
select
el.element_type_id 
from
fnd_sessions fd,
pay_element_types_f_tl tl,
pay_element_types_f el
where 
nvl(el.business_group_id,fnd_global.per_business_group_id) =fnd_global.per_business_group_id and 
(el.legislation_code is null or 
 el.legislation_code=
  (select legislation_code 
   from   per_business_groups b
   where b.business_group_id=fnd_global.per_business_group_id
  )
)
and tl.language=userenv('LANG')
and el.element_type_id=tl.element_type_id
and fd.session_id=userenv('sessionid')
and fd.effective_date between el.effective_start_date and el.effective_end_date
and tl.element_name = :p_element_name
)

pcd.payroll_id in
(
select
ppf.payroll_id
from
pay_payrolls_f ppf,
fnd_sessions fnd
where 
ppf.business_group_id=:p_business_group_id and 
fnd.session_id=userenv('sessionid') and 
fnd.effective_date between ppf.effective_start_date and ppf.effective_end_date and
ppf.payroll_name = :p_payroll_name
)

pcd.consolidation_set_name = :p_consolidation_set_name

pcd.tax_unit_id =:p_tax_unit_id

pcd.organization_id = :p_organization_id

pcd.location_id = :p_location_id

pcd.person_id = :p_person_id

exists 
  (select 
    1
   from 
    hr_assignment_sets aset
   where
    aset.assignment_set_id = :p_assignment_set_id and
    nvl(aset.payroll_id,pcd.payroll_id) = pcd.payroll_id and
    (not exists 
     (select 
       1
      from 
       hr_assignment_set_amendments hasa
      where
       hasa.assignment_set_id = aset.assignment_set_id and
       hasa.include_or_exclude = 'I') or exists 
     (select 
       1
      from 
       hr_assignment_set_amendments hasa
      where
       hasa.assignment_set_id = aset.assignment_set_id and
       hasa.assignment_id = pcd.assignment_id and
       hasa.include_or_exclude = 'I')) and
    not exists 
     (select 
       1
      from 
       hr_assignment_set_amendments hasa
      where
       hasa.assignment_set_id = aset.assignment_set_id and
       hasa.assignment_id = pcd.assignment_id and
       hasa.include_or_exclude = 'E'
     )
  )

(select 
   hrl.meaning
  from 
   hr_lookups hrl,
   per_all_assignments_f paf
  where
   hrl.lookup_type = 'EMP_CAT' and
   hrl.lookup_code = paf.employment_category and
   paf.assignment_id = pcd.assignment_id and
   pcd.effective_date between paf.effective_start_date and paf.effective_end_date
) = :p_emp_cat