PAY P45 A4

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: P45 A4 Report
Short Name: PYGBA4P45
DB package: PAY_PAYGBP45_A4_PKG
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,28))) assignment_number,
  max(decode(fue.user_entity_name,'X_FIRST_NAME',
	     replace(substr(UPPER(fai.value),1,34),',',' '))) first_name,
  max(decode(fue.user_entity_name,'X_LAST_NAME',
	     substr(UPPER(fai.value),1,34))) last_name,
  decode(max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',substr(fai.value,1,1))), NULL,  	
	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,28))) 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,34))) address_line1,
  max(decode(fue.user_entity_name,'X_ADDRESS_LINE2',
	     substr(fai.value,1,34))) address_line2,
  max(decode(fue.user_entity_name,'X_ADDRESS_LINE3',
	     substr(fai.value,1,34))) 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,19))) 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,
decode ( max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',substr(fai.value,1,1))) ,NULL,  
  	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,
  max (decode(fue.user_entity_name,'X_SEX',
                     substr(fai.value,1,1))) sex,
  max (decode(fue.user_entity_name,'X_DATE_OF_BIRTH',
                     fnd_date.canonical_to_date(fai.value))) dob,
	PAY_PAYGBP45_A4_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 ) ) ),
    max ( decode ( fue.user_entity_name,  'X_SEX',substr(fai.value,1,1))),
    max ( decode ( fue.user_entity_name,  'X_DATE_OF_BIRTH',fnd_date.canonical_to_date(fai.value)))) C_Format_data,
	PAY_PAYGBP45_A4_PKG.C_TITLE_p C_TITLE,
	PAY_PAYGBP45_A4_PKG.C_NI12_p C_NI12,
	PAY_PAYGBP45_A4_PKG.C_NI34_p C_NI34,
	PAY_PAYGBP45_A4_PKG.C_NI56_p C_NI56,
	PAY_PAYGBP45_A4_PKG.C_NI78_p C_NI78,
	PAY_PAYGBP45_A4_PKG.C_Ni9_p C_Ni9,
	PAY_PAYGBP45_A4_PKG.C_DATE_OF_LEAVING_DD_p C_DATE_OF_LEAVING_DD,
	PAY_PAYGBP45_A4_PKG.C_DATE_OF_LEAVING_MM_p C_DATE_OF_LEAVING_MM,
	PAY_PAYGBP45_A4_PKG.C_DATE_OF_LEAVING_YYYY_p C_DATE_OF_LEAVING_YYYY,
	PAY_PAYGBP45_A4_PKG.C_TOTAL_TAX_TD_p C_TOTAL_TAX_TD,
	PAY_PAYGBP45_A4_PKG.C_TOTAL_PAY_TD_p C_TOTAL_PAY_TD,
	PAY_PAYGBP45_A4_PKG.C_PER_ADDRESS_LINE1_p C_PER_ADDRESS_LINE1,
	PAY_PAYGBP45_A4_PKG.C_PER_ADDRESS_LINE2_p C_PER_ADDRESS_LINE2,
	PAY_PAYGBP45_A4_PKG.C_PER_ADDRESS_LINE3_p C_PER_ADDRESS_LINE3,
	PAY_PAYGBP45_A4_PKG.C_PER_ADDRESS_LINE4_p C_PER_ADDRESS_LINE4,
	PAY_PAYGBP45_A4_PKG.C_PAY_IN_EMP_POUNDS_p C_PAY_IN_EMP_POUNDS,
	PAY_PAYGBP45_A4_PKG.C_PAY_IN_EMP_PENCE_p C_PAY_IN_EMP_PENCE,
	PAY_PAYGBP45_A4_PKG.C_TAX_IN_EMP_POUNDS_p C_TAX_IN_EMP_POUNDS,
	PAY_PAYGBP45_A4_PKG.C_TAX_IN_EMP_PENCE_p C_TAX_IN_EMP_PENCE,
	PAY_PAYGBP45_A4_PKG.C_PAY_TD_POUNDS_p C_PAY_TD_POUNDS,
	PAY_PAYGBP45_A4_PKG.C_PAY_TD_PENCE_p C_PAY_TD_PENCE,
	PAY_PAYGBP45_A4_PKG.C_TAX_TD_POUNDS_p C_TAX_TD_POUNDS,
	PAY_PAYGBP45_A4_PKG.C_TAX_TD_PENCE_p C_TAX_TD_PENCE,
	PAY_PAYGBP45_A4_PKG.C_DATE_OF_BIRTH_DD_p C_DATE_OF_BIRTH_DD,
	PAY_PAYGBP45_A4_PKG.C_DATE_OF_BIRTH_MM_p C_DATE_OF_BIRTH_MM,
	PAY_PAYGBP45_A4_PKG.C_DATE_OF_BIRTH_YYYY_p C_DATE_OF_BIRTH_YYYY,
    PAY_PAYGBP45_A4_PKG.C_SEX_M_p C_SEX_M,
    PAY_PAYGBP45_A4_PKG.C_SEX_F_p C_SEX_F,
	TO_CHAR(sysdate,'DD') C_TO_DATE_DD,
	TO_CHAR(sysdate,'MM') C_TO_DATE_MM,
	TO_CHAR(sysdate,'YYYY') C_TO_DATE_YYYY
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
Parameter Name SQL text Validation
DebugFlag
 
Assignment Number
 
LOV Oracle
Archive Request Id
 
LOV Oracle