PAY P45

SELECT
  act.assignment_action_id,
  max(decode(fue.user_entity_name,'X_PAYROLL_ID',
	     fnd_number.canonical_to_number(fai.value))) payroll_id,
  max(decode(fue.user_entity_name,'X_TERMINATION_DATE',
	     fnd_date.canonical_to_date(fai.value))) termination_date,
  max(decode(fue.user_entity_name,'X_ISSUE_DATE',
	     fnd_date.canonical_to_date(fai.value))) issue_date,
  max(decode(fue.user_entity_name,'X_DECEASED_FLAG',
	     substr(fai.value,1,1))) deceased_flag,
  max(decode(fue.user_entity_name,'X_ASSIGNMENT_NUMBER',
	     substr(fai.VALUE,1,30))) assignment_number,
  max(decode(fue.user_entity_name,'X_FIRST_NAME',
	     replace(substr(UPPER(fai.value),1,20),',',' '))) first_name,
  max(decode(fue.user_entity_name,'X_LAST_NAME',
	     substr(UPPER(fai.value),1,40))) last_name,
  max(decode(fue.user_entity_name,'X_MONTH_NUMBER',
	     fnd_number.canonical_to_number(fai.value))) month_number,
  max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',
	     substr(UPPER(fai.value),1,20))) ni_number,
  max(decode(fue.user_entity_name,'X_ORGANIZATION_NAME',
	     substr(fai.value,1,60))) organization_name,
  max(decode(fue.user_entity_name,'X_TAX_PAID',
	     fnd_number.canonical_to_number(fai.value))) tax_paid,
  max(decode(fue.user_entity_name,'X_TAXABLE_PAY',
	     fnd_number.canonical_to_number(fai.value))) taxable_pay,
  max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',
	     fnd_number.canonical_to_number(fai.value))) previous_tax_paid,
  max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',
	     fnd_number.canonical_to_number(fai.value))) previous_taxable_pay,
  max(decode(fue.user_entity_name,'X_ADDRESS_LINE1',
	     substr(fai.value,1,60))) address_line1,
  max(decode(fue.user_entity_name,'X_ADDRESS_LINE2',
	     substr(fai.value,1,60))) address_line2,
  max(decode(fue.user_entity_name,'X_ADDRESS_LINE3',
	     substr(fai.value,1,60))) address_line3,
  max(decode(fue.user_entity_name,'X_TOWN_OR_CITY',
	     substr(fai.value,1,30))) town_or_city,
  max(decode(fue.user_entity_name,'X_COUNTY',
	     substr(fai.value,1,30))) county,
  max(decode(fue.user_entity_name,'X_POSTAL_CODE',
	     substr(fai.value,1,20))) post_code,
  max(decode(fue.user_entity_name,'X_STUDENT_LOAN_FLAG',
	     substr(fai.value,1,1))) student_loan_flag,
  max(decode(fue.user_entity_name,'X_TAX_CODE',
	     substr(UPPER(fai.value),1,10))) tax_code,
  max(decode(fue.user_entity_name,'X_TITLE',
	     substr(UPPER(fai.value),1,10))) title,
  max(decode(fue.user_entity_name,'X_WEEK_NUMBER',
	     fnd_number.canonical_to_number(fai.value))) week_number,
  max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',
	     substr(fai.value,1,1))) w1_m1_indicator,
  max(decode(fue.user_entity_name,'X_COUNTRY',
                     substr(fai.value,1,30))) country,
	PAY_PAYGBP45_XMLP_PKG.c_format_data_formula(max ( decode ( fue.user_entity_name , 'X_TITLE' , substr ( UPPER ( fai.value ) , 1 , 10 ) ) ), max ( decode ( fue.user_entity_name , 'X_ADDRESS_LINE1' , substr ( fai.value , 1 , 60 ) ) ), max ( decode ( fue.user_entity_name , 'X_ADDRESS_LINE2' , substr ( fai.value , 1 , 60 ) ) ), max ( decode ( fue.user_entity_name , 'X_ADDRESS_LINE3' , substr ( fai.value , 1 , 60 ) ) ), max ( decode ( fue.user_entity_name , 'X_TOWN_OR_CITY' , substr ( fai.value , 1 , 30 ) ) ), max ( decode ( fue.user_entity_name , 'X_COUNTY' , substr ( fai.value , 1 , 30 ) ) ), max ( decode ( fue.user_entity_name , 'X_COUNTRY' , substr ( fai.value , 1 , 30 ) ) ), max ( decode ( fue.user_entity_name , 'X_POSTAL_CODE' , substr ( fai.value , 1 , 20 ) ) ), max ( decode ( fue.user_entity_name , 'X_TAXABLE_PAY' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_PREVIOUS_TAXABLE_PAY' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_TAX_PAID' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_PREVIOUS_TAX_PAID' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_NATIONAL_INSURANCE_NUMBER' , substr ( UPPER ( fai.value ) , 1 , 20 ) ) ), max ( decode ( fue.user_entity_name , 'X_TERMINATION_DATE' , fnd_date.canonical_to_date ( fai.value ) ) ), :C_3_PART, max ( decode ( fue.user_entity_name , 'X_W1_M1_INDICATOR' , substr ( fai.value , 1 , 1 ) ) )) C_Format_data,
	PAY_PAYGBP45_XMLP_PKG.C_TITLE_p C_TITLE,
	PAY_PAYGBP45_XMLP_PKG.C_NI12_p C_NI12,
	PAY_PAYGBP45_XMLP_PKG.C_NI34_p C_NI34,
	PAY_PAYGBP45_XMLP_PKG.C_NI56_p C_NI56,
	PAY_PAYGBP45_XMLP_PKG.C_NI78_p C_NI78,
	PAY_PAYGBP45_XMLP_PKG.C_Ni9_p C_Ni9,
	PAY_PAYGBP45_XMLP_PKG.C_DATE_OF_LEAVING_DD_p C_DATE_OF_LEAVING_DD,
	PAY_PAYGBP45_XMLP_PKG.C_DATE_OF_LEAVING_MM_p C_DATE_OF_LEAVING_MM,
	PAY_PAYGBP45_XMLP_PKG.C_DATE_OF_LEAVING_YYYY_p C_DATE_OF_LEAVING_YYYY,
	PAY_PAYGBP45_XMLP_PKG.C_TOTAL_TAX_TD_p C_TOTAL_TAX_TD,
	PAY_PAYGBP45_XMLP_PKG.C_TOTAL_PAY_TD_p C_TOTAL_PAY_TD,
	PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE1_p C_PER_ADDRESS_LINE1,
	PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE2_p C_PER_ADDRESS_LINE2,
	PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE3_p C_PER_ADDRESS_LINE3,
	PAY_PAYGBP45_XMLP_PKG.C_PER_ADDRESS_LINE4_p C_PER_ADDRESS_LINE4,
	PAY_PAYGBP45_XMLP_PKG.C_PAY_IN_EMP_POUNDS_p C_PAY_IN_EMP_POUNDS,
	PAY_PAYGBP45_XMLP_PKG.C_PAY_IN_EMP_PENCE_p C_PAY_IN_EMP_PENCE,
	PAY_PAYGBP45_XMLP_PKG.C_TAX_IN_EMP_POUNDS_p C_TAX_IN_EMP_POUNDS,
	PAY_PAYGBP45_XMLP_PKG.C_TAX_IN_EMP_PENCE_p C_TAX_IN_EMP_PENCE,
	PAY_PAYGBP45_XMLP_PKG.C_PAY_TD_POUNDS_p C_PAY_TD_POUNDS,
	PAY_PAYGBP45_XMLP_PKG.C_PAY_TD_PENCE_p C_PAY_TD_PENCE,
	PAY_PAYGBP45_XMLP_PKG.C_TAX_TD_POUNDS_p C_TAX_TD_POUNDS,
	PAY_PAYGBP45_XMLP_PKG.C_TAX_TD_PENCE_p C_TAX_TD_PENCE
FROM
  pay_assignment_actions act,
  ff_archive_items fai,
  ff_user_entities fue
WHERE act.assignment_action_id = fai.context1
&P_ACTION_RESTRICTION
AND act.action_status = 'C'
AND fue.legislation_code = 'GB'
AND fue.business_group_id IS NULL
AND fue.user_entity_id  = fai.user_entity_id
GROUP BY act.assignment_action_id
&P_SORT_ORDER