PAY IR8S Ad Hoc Printed Report New

select distinct pac.assignment_action_id
       ,pac.assignment_id
       ,ppa.payroll_action_id
       ,pay_sg_iras_archive.get_archive_value('X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME', pac.assignment_action_id) X_LEGAL_ENTITY_NAME
	   ,pay_sg_iras_archive.get_archive_value('X_SG_LEGAL_ENTITY_SG_ER_INCOME_TAX_NUMBER',pac.assignment_action_id) X_ER_TAX_NUMBER
       ,pay_sg_iras_archive.get_archive_value('X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME', pac.assignment_action_id) X_EE_LEGAL_NAME
       ,pay_sg_iras_archive.get_archive_value('X_PER_NATIONAL_IDENTIFIER', pac.assignment_action_id) X_EE_NRIC
	   ,pay_sg_iras_archive.get_archive_value('X_PEOPLE_FLEXFIELD_SG_SG_INCOME_TAX_NUMBER', pac.assignment_action_id) X_EE_TAX_NUMBER
       ,to_number(pay_sg_iras_archive.get_archive_value('X_IR8S_MOA_408_PER_LE_YTD', pac.assignment_action_id))  X_IR8S_MOA_408_PER_LE_YTD
       ,to_number(pay_sg_iras_archive.get_archive_value('X_IR8S_MOA_409_PER_LE_YTD', pac.assignment_action_id))  X_IR8S_MOA_409_PER_LE_YTD
	   ,decode(pay_sg_iras_archive.get_archive_value('X_HR_IR8S_INDICATORS_SG_ASG_CPF_OVERSEAS_POST_OBLIGATORY', pac.assignment_action_id),'Y','X',Null) X_IR8S_CPF_OBLI_YES
	   ,decode(pay_sg_iras_archive.get_archive_value('X_HR_IR8S_INDICATORS_SG_ASG_CPF_OVERSEAS_POST_OBLIGATORY', pac.assignment_action_id),'N','X',' ','X',Null) X_IR8S_CPF_OBLI_NO
       ,to_char(fnd_date.canonical_to_date(pay_sg_iras_archive.get_archive_value('X_ASG_OVERSEAS_DATE_FROM', pac.assignment_action_id)),'DD-MON-YYYY') X_ASG_OVS_DATE_FROM
	   ,to_char(fnd_date.canonical_to_date(pay_sg_iras_archive.get_archive_value('X_ASG_OVERSEAS_DATE_TO', pac.assignment_action_id)),'DD-MON-YYYY') X_ASG_OVS_DATE_TO
 from   pay_assignment_actions pac,
        pay_payroll_actions ppa,
        per_all_assignments_f paa
where   ppa.payroll_action_id = pac.payroll_action_id
  and   pac.assignment_id = paa.assignment_id
  and   ppa.report_type ='SG_IR8S_ADHOC_REPORT'
  and   ppa.action_type = 'X'
  and   ppa.action_status ='C'
  and   ppa.payroll_action_id = :P_PAYROLL_ACTION_ID
  /*order by
  decode(pay_sg_iras_archive.get_archive_value('X_HR_IR8S_INDICATORS_SG_ASG_CPF_OVERSEAS_POST_OBLIGATORY', pac.assignment_action_id),'Y','X',Null),
  decode(pay_sg_iras_archive.get_archive_value('X_HR_IR8S_INDICATORS_SG_ASG_CPF_OVERSEAS_POST_OBLIGATORY', pac.assignment_action_id),'N','X',' ','X',Null),
  to_char(fnd_date.canonical_to_date(pay_sg_iras_archive.get_archive_value('X_ASG_OVERSEAS_DATE_FROM', pac.assignment_action_id)),'DD-MON-YYYY'),
  to_char(fnd_date.canonical_to_date(pay_sg_iras_archive.get_archive_value('X_ASG_OVERSEAS_DATE_TO', pac.assignment_action_id)),'DD-MON-YYYY'),
pac.assignment_id,
ppa.payroll_action_id,
pay_sg_iras_archive.get_archive_value('X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME', pac.assignment_action_id),
pay_sg_iras_archive.get_archive_value('X_SG_LEGAL_ENTITY_SG_ER_INCOME_TAX_NUMBER',pac.assignment_action_id),
pay_sg_iras_archive.get_archive_value('X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME',pac.assignment_action_id),
pay_sg_iras_archive.get_archive_value('X_PER_NATIONAL_IDENTIFIER', pac.assignment_action_id),
pay_sg_iras_archive.get_archive_value('X_PEOPLE_FLEXFIELD_SG_SG_INCOME_TAX_NUMBER', pac.assignment_action_id),
to_number(pay_sg_iras_archive.get_archive_value('X_IR8S_MOA_408_PER_LE_YTD', pac.assignment_action_id))*/