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 |
