BIP – Query to fetch assignment costing details

SELECT prgd.assignment_id
     , pprd.payroll_relationship_id
     , pprd.payroll_relationship_number
     , prt.legislation_code
     , prt.process_in_run
     , prt.base_rel_type_name
     , prtt.relationship_type_name
     , prtt.description
     , pcaf.effective_start_date
     , pcaf.effective_end_date
     , pcaa.segment1
     , pcaa.segment2
     , pcaa.segment3
     , pcaa.segment4
  FROM pay_rel_groups_dn prgd
     , pay_pay_relationships_dn pprd
     , pay_relationship_types prt
     , pay_relationship_types_tl prtt
     , pay_cost_allocations_f pcaf
     , pay_cost_alloc_accounts pcaa
 WHERE pcaf.source_type = 'ASG'
   AND pprd.payroll_relationship_id = pcaf.payroll_relationship_id
   AND pcaf.cost_allocation_record_id = pcaa.cost_allocation_record_id
   AND pprd.payroll_relationship_id = prgd.payroll_relationship_id
   AND prgd.relationship_group_id = pcaf.source_id
   AND trunc(sysdate) BETWEEN pcaf.effective_start_date AND pcaf.effective_end_date
   AND trunc(sysdate) BETWEEN pprd.start_date AND pprd.end_date
   AND pprd.relationship_type_id = prt.relationship_type_id
   AND prt.relationship_type_id = prtt.relationship_type_id
   AND prtt.language = 'US'
   AND pprd.person_id = (select distinct person_id from per_all_people_f where person_number = '212')