PAY P45 Report (laser)

Description

Categories: BI Publisher
Application: Payroll
Source: P45 Report (laser) (XML)
Short Name: PAYGB45L_XML
DB package: PAY_PAYGB45L_XMLP_PKG

SQL 116 lines

select a.*,
	PAY_PAYGB45L_XMLP_PKG.C_NI12_p C_NI12,
	PAY_PAYGB45L_XMLP_PKG.C_NI34_p C_NI34,
	PAY_PAYGB45L_XMLP_PKG.C_NI56_p C_NI56,
	PAY_PAYGB45L_XMLP_PKG.C_NI78_p C_NI78,
	PAY_PAYGB45L_XMLP_PKG.C_Ni9_p C_Ni9,
	PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_DD_p C_DATE_OF_LEAVING_DD,
	PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_MM_p C_DATE_OF_LEAVING_MM,
	PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_YYYY_p C_DATE_OF_LEAVING_YYYY,
	PAY_PAYGB45L_XMLP_PKG.C_WEEK_NO_p C_WEEK_NO,
	PAY_PAYGB45L_XMLP_PKG.C_MONTH_NO_p C_MONTH_NO,
	PAY_PAYGB45L_XMLP_PKG.C_TOTAL_TAX_TD_p C_TOTAL_TAX_TD,
	PAY_PAYGB45L_XMLP_PKG.C_TOTAL_PAY_TD_p C_TOTAL_PAY_TD,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE1_p C_PER_ADDRESS_LINE1,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE2_p C_PER_ADDRESS_LINE2,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE3_p C_PER_ADDRESS_LINE3,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE4_p C_PER_ADDRESS_LINE4,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_POUNDS_p C_PAY_IN_EMP_POUNDS,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_PENCE_p C_PAY_IN_EMP_PENCE,
	PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_POUNDS_p C_TAX_IN_EMP_POUNDS,
	PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_PENCE_p C_TAX_IN_EMP_PENCE,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_POUNDS_p C_PAY_TD_POUNDS,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_PENCE_p C_PAY_TD_PENCE,
	PAY_PAYGB45L_XMLP_PKG.C_TAX_TD_POUNDS_p C_TAX_TD_POUNDS,
	PAY_PAYGB45L_XMLP_PKG.C_TAX_TD_PENCE_p C_TAX_TD_PENCE
from (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',
	     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 ,
	PAY_PAYGB45L_XMLP_PKG.c_format_dataformula(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_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 ) ) ), max ( decode ( fue.user_entity_name , 'X_MONTH_NUMBER' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_WEEK_NUMBER' , fnd_number.canonical_to_number ( fai.value ) ) )) C_FORMAT_DATA
	/*,
	PAY_PAYGB45L_XMLP_PKG.C_NI12_p C_NI12,
	PAY_PAYGB45L_XMLP_PKG.C_NI34_p C_NI34,
	PAY_PAYGB45L_XMLP_PKG.C_NI56_p C_NI56,
	PAY_PAYGB45L_XMLP_PKG.C_NI78_p C_NI78,
	PAY_PAYGB45L_XMLP_PKG.C_Ni9_p C_Ni9,
	PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_DD_p C_DATE_OF_LEAVING_DD,
	PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_MM_p C_DATE_OF_LEAVING_MM,
	PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_YYYY_p C_DATE_OF_LEAVING_YYYY,
	PAY_PAYGB45L_XMLP_PKG.C_WEEK_NO_p C_WEEK_NO,
	PAY_PAYGB45L_XMLP_PKG.C_MONTH_NO_p C_MONTH_NO,
	PAY_PAYGB45L_XMLP_PKG.C_TOTAL_TAX_TD_p C_TOTAL_TAX_TD,
	PAY_PAYGB45L_XMLP_PKG.C_TOTAL_PAY_TD_p C_TOTAL_PAY_TD,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE1_p C_PER_ADDRESS_LINE1,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE2_p C_PER_ADDRESS_LINE2,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE3_p C_PER_ADDRESS_LINE3,
	PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE4_p C_PER_ADDRESS_LINE4,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_POUNDS_p C_PAY_IN_EMP_POUNDS,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_PENCE_p C_PAY_IN_EMP_PENCE,
	PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_POUNDS_p C_TAX_IN_EMP_POUNDS,
	PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_PENCE_p C_TAX_IN_EMP_PENCE,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_POUNDS_p C_PAY_TD_POUNDS,
	PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_PENCE_p C_PAY_TD_PENCE,
	PAY_PAYGB45L_XMLP_PKG.C_TAX_TD_POUNDS_p C_TAX_TD_POUNDS,
	PAY_PAYGB45L_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
ORDER BY organization_name, payroll_id, assignment_number) a

select a.*, PAY_PAYGB45L_XMLP_PKG.C_NI12_p C_NI12, PAY_PAYGB45L_XMLP_PKG.C_NI34_p C_NI34, PAY_PAYGB45L_XMLP_PKG.C_NI56_p C_NI56, PAY_PAYGB45L_XMLP_PKG.C_NI78_p C_NI78, PAY_PAYGB45L_XMLP_PKG.C_Ni9_p C_Ni9, PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_DD_p C_DATE_OF_LEAVING_DD, PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_MM_p C_DATE_OF_LEAVING_MM, PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_YYYY_p C_DATE_OF_LEAVING_YYYY, PAY_PAYGB45L_XMLP_PKG.C_WEEK_NO_p C_WEEK_NO, PAY_PAYGB45L_XMLP_PKG.C_MONTH_NO_p C_MONTH_NO, PAY_PAYGB45L_XMLP_PKG.C_TOTAL_TAX_TD_p C_TOTAL_TAX_TD, PAY_PAYGB45L_XMLP_PKG.C_TOTAL_PAY_TD_p C_TOTAL_PAY_TD, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE1_p C_PER_ADDRESS_LINE1, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE2_p C_PER_ADDRESS_LINE2, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE3_p C_PER_ADDRESS_LINE3, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE4_p C_PER_ADDRESS_LINE4, PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_POUNDS_p C_PAY_IN_EMP_POUNDS, PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_PENCE_p C_PAY_IN_EMP_PENCE, PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_POUNDS_p C_TAX_IN_EMP_POUNDS, PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_PENCE_p C_TAX_IN_EMP_PENCE, PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_POUNDS_p C_PAY_TD_POUNDS, PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_PENCE_p C_PAY_TD_PENCE, PAY_PAYGB45L_XMLP_PKG.C_TAX_TD_POUNDS_p C_TAX_TD_POUNDS, PAY_PAYGB45L_XMLP_PKG.C_TAX_TD_PENCE_p C_TAX_TD_PENCE from (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', 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 , PAY_PAYGB45L_XMLP_PKG.c_format_dataformula(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_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 ) ) ), max ( decode ( fue.user_entity_name , 'X_MONTH_NUMBER' , fnd_number.canonical_to_number ( fai.value ) ) ), max ( decode ( fue.user_entity_name , 'X_WEEK_NUMBER' , fnd_number.canonical_to_number ( fai.value ) ) )) C_FORMAT_DATA /*, PAY_PAYGB45L_XMLP_PKG.C_NI12_p C_NI12, PAY_PAYGB45L_XMLP_PKG.C_NI34_p C_NI34, PAY_PAYGB45L_XMLP_PKG.C_NI56_p C_NI56, PAY_PAYGB45L_XMLP_PKG.C_NI78_p C_NI78, PAY_PAYGB45L_XMLP_PKG.C_Ni9_p C_Ni9, PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_DD_p C_DATE_OF_LEAVING_DD, PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_MM_p C_DATE_OF_LEAVING_MM, PAY_PAYGB45L_XMLP_PKG.C_DATE_OF_LEAVING_YYYY_p C_DATE_OF_LEAVING_YYYY, PAY_PAYGB45L_XMLP_PKG.C_WEEK_NO_p C_WEEK_NO, PAY_PAYGB45L_XMLP_PKG.C_MONTH_NO_p C_MONTH_NO, PAY_PAYGB45L_XMLP_PKG.C_TOTAL_TAX_TD_p C_TOTAL_TAX_TD, PAY_PAYGB45L_XMLP_PKG.C_TOTAL_PAY_TD_p C_TOTAL_PAY_TD, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE1_p C_PER_ADDRESS_LINE1, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE2_p C_PER_ADDRESS_LINE2, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE3_p C_PER_ADDRESS_LINE3, PAY_PAYGB45L_XMLP_PKG.C_PER_ADDRESS_LINE4_p C_PER_ADDRESS_LINE4, PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_POUNDS_p C_PAY_IN_EMP_POUNDS, PAY_PAYGB45L_XMLP_PKG.C_PAY_IN_EMP_PENCE_p C_PAY_IN_EMP_PENCE, PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_POUNDS_p C_TAX_IN_EMP_POUNDS, PAY_PAYGB45L_XMLP_PKG.C_TAX_IN_EMP_PENCE_p C_TAX_IN_EMP_PENCE, PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_POUNDS_p C_PAY_TD_POUNDS, PAY_PAYGB45L_XMLP_PKG.C_PAY_TD_PENCE_p C_PAY_TD_PENCE, PAY_PAYGB45L_XMLP_PKG.C_TAX_TD_POUNDS_p C_TAX_TD_POUNDS, PAY_PAYGB45L_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 ORDER BY organization_name, payroll_id, assignment_number) a

Parameters 2

Parameter	Type	List of values SQL
Archive Request Id	LOV Oracle
Assignment Number	LOV Oracle

Description

SQL 116 lines

Parameters 2

Find out more

Features

Download

FAQ