VIEW: APPS.PAY_GB_YEAR_END_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT act.assignment_action_id , act.assignment_id , act.payroll_action_id , act.action_status , act.chunk_number , pay_gb_eoy_archive.canonical_to_number(fai.VALUE) payroll_id , pay_gb_eoy_archive.get_arch_date(fai.context1,'X_EFFECTIVE_END_DATE') EFFECTIVE_END_DATE , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_ADDRESS_LINE1') ADDRESS_LINE1 , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_ADDRESS_LINE2') ADDRESS_LINE2 , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_ADDRESS_LINE3') ADDRESS_LINE3 , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_ASSIGNMENT_NUMBER') ASSIGNMENT_NUMBER , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_COUNTY') COUNTY , pay_gb_eoy_archive.get_arch_date(fai.context1,'X_DATE_OF_BIRTH') DATE_OF_BIRTH , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_DIRECTOR_INDICATOR') DIRECTOR_INDICATOR , pay_gb_eoy_archive.get_arch_date(fai.context1,'X_EFFECTIVE_START_DATE') EFFECTIVE_START_DATE , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_EOY_PRIMARY_FLAG') EOY_PRIMARY_FLAG , pay_gb_eoy_archive.get_arch_str(fai.context1, 'X_EXPENSE_CHECK_SEND_TO_ADDRESS') EXPENSE_CHECK_SEND_TO_ADDRESS , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_FIRST_NAME') FIRST_NAME , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_GROSS_PAY') GROSS_PAY , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_LAST_ASG_ACTION_ID') LAST_ASG_ACTION_ID , pay_gb_eoy_archive.get_arch_date(fai.context1,'X_LAST_EFFECTIVE_DATE') LAST_EFFECTIVE_DATE , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_LAST_NAME') LAST_NAME , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_LOCATION_ID') LOCATION_ID , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_MIDDLE_NAME') MIDDLE_NAME , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_MULTIPLE_ASG_FLAG') MULTIPLE_ASG_FLAG , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_AGGREGATED_PAYE_FLAG') AGGREGATED_PAYE_FLAG , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_NATIONAL_INSURANCE_NUMBER') NATIONAL_INSURANCE_NUMBER , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_ORGANIZATION_ID') ORGANIZATION_ID , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_PENSIONER_INDICATOR') PENSIONER_INDICATOR , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_PEOPLE_GROUP_ID') PEOPLE_GROUP_ID , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_PERSON_ID') PERSON_ID , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_POSTAL_CODE') POSTAL_CODE , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_PREVIOUS_TAXABLE_PAY') PREVIOUS_TAXABLE_PAY , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_PREVIOUS_TAX_PAID') PREVIOUS_TAX_PAID , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_PREVIOUS_YEAR_ASG_ACTION_ID') PREVIOUS_YEAR_ASG_ACTION_ID , pay_gb_eoy_archive.get_arch_date(fai.context1, 'X_PREVIOUS_YEAR_EFFECTIVE_DATE') PREVIOUS_YEAR_EFFECTIVE_DATE , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_SEX') SEX , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_SSP') SSP , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_SMP') SMP , pay_gb_eoy_archive.get_arch_date(fai.context1,'X_START_OF_EMP') START_OF_EMP , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_SUPERANNUATION_PAID') SUPERANNUATION_PAID , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_SUPERANNUATION_REFUND') SUPERANNUATION_REFUND , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_TAXABLE_PAY') TAXABLE_PAY , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_TAX_CODE') TAX_CODE , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_TAX_PAID') TAX_PAID , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_TAX_REFUND') TAX_REFUND , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_TAX_RUN_RESULT_ID') TAX_RUN_RESULT_ID , pay_gb_eoy_archive.get_arch_date(fai.context1,'X_TERMINATION_DATE') TERMINATION_DATE , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_TERMINATION_TYPE') TERMINATION_TYPE , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_TITLE') TITLE , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_TOWN_OR_CITY') TOWN_OR_CITY , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_W1_M1_INDICATOR') W1_M1_INDICATOR , pay_gb_eoy_archive.get_arch_str(fai.context1,'X_WEEK_53_INDICATOR') WEEK_53_INDICATOR , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_WIDOWS_AND_ORPHANS') WIDOWS_AND_ORPHANS , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_NOTIONAL_PAY') NOTIONAL_PAY , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_SAP') SAP , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_SPP_ADOPT') SPP_ADOPT , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_SPP_BIRTH') SPP_BIRTH , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_STUDENT_LOANS') STUDENT_LOANS , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_TAX_CREDITS') TAX_CREDITS , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_ASPP_ADOPT') ASPP_ADOPT , pay_gb_eoy_archive.get_arch_num(fai.context1,'X_ASPP_BIRTH') ASPP_BIRTH FROM pay_assignment_actions act, ff_archive_items fai WHERE act.assignment_action_id = fai.context1 AND fai.user_entity_id = (SELECT fue.user_entity_id FROM ff_user_entities fue WHERE fue.user_entity_name = 'X_PAYROLL_ID' AND fue.legislation_code = 'GB' AND fue.business_group_id IS NULL)

View Text - HTML Formatted

SELECT ACT.ASSIGNMENT_ACTION_ID
, ACT.ASSIGNMENT_ID
, ACT.PAYROLL_ACTION_ID
, ACT.ACTION_STATUS
, ACT.CHUNK_NUMBER
, PAY_GB_EOY_ARCHIVE.CANONICAL_TO_NUMBER(FAI.VALUE) PAYROLL_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_DATE(FAI.CONTEXT1
, 'X_EFFECTIVE_END_DATE') EFFECTIVE_END_DATE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_ADDRESS_LINE1') ADDRESS_LINE1
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_ADDRESS_LINE2') ADDRESS_LINE2
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_ADDRESS_LINE3') ADDRESS_LINE3
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_ASSIGNMENT_NUMBER') ASSIGNMENT_NUMBER
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_COUNTY') COUNTY
, PAY_GB_EOY_ARCHIVE.GET_ARCH_DATE(FAI.CONTEXT1
, 'X_DATE_OF_BIRTH') DATE_OF_BIRTH
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_DIRECTOR_INDICATOR') DIRECTOR_INDICATOR
, PAY_GB_EOY_ARCHIVE.GET_ARCH_DATE(FAI.CONTEXT1
, 'X_EFFECTIVE_START_DATE') EFFECTIVE_START_DATE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_EOY_PRIMARY_FLAG') EOY_PRIMARY_FLAG
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_EXPENSE_CHECK_SEND_TO_ADDRESS') EXPENSE_CHECK_SEND_TO_ADDRESS
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_FIRST_NAME') FIRST_NAME
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_GROSS_PAY') GROSS_PAY
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_LAST_ASG_ACTION_ID') LAST_ASG_ACTION_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_DATE(FAI.CONTEXT1
, 'X_LAST_EFFECTIVE_DATE') LAST_EFFECTIVE_DATE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_LAST_NAME') LAST_NAME
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_LOCATION_ID') LOCATION_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_MIDDLE_NAME') MIDDLE_NAME
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_MULTIPLE_ASG_FLAG') MULTIPLE_ASG_FLAG
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_AGGREGATED_PAYE_FLAG') AGGREGATED_PAYE_FLAG
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_NATIONAL_INSURANCE_NUMBER') NATIONAL_INSURANCE_NUMBER
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_ORGANIZATION_ID') ORGANIZATION_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_PENSIONER_INDICATOR') PENSIONER_INDICATOR
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_PEOPLE_GROUP_ID') PEOPLE_GROUP_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_PERSON_ID') PERSON_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_POSTAL_CODE') POSTAL_CODE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_PREVIOUS_TAXABLE_PAY') PREVIOUS_TAXABLE_PAY
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_PREVIOUS_TAX_PAID') PREVIOUS_TAX_PAID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_PREVIOUS_YEAR_ASG_ACTION_ID') PREVIOUS_YEAR_ASG_ACTION_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_DATE(FAI.CONTEXT1
, 'X_PREVIOUS_YEAR_EFFECTIVE_DATE') PREVIOUS_YEAR_EFFECTIVE_DATE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_SEX') SEX
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_SSP') SSP
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_SMP') SMP
, PAY_GB_EOY_ARCHIVE.GET_ARCH_DATE(FAI.CONTEXT1
, 'X_START_OF_EMP') START_OF_EMP
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_SUPERANNUATION_PAID') SUPERANNUATION_PAID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_SUPERANNUATION_REFUND') SUPERANNUATION_REFUND
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_TAXABLE_PAY') TAXABLE_PAY
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_TAX_CODE') TAX_CODE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_TAX_PAID') TAX_PAID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_TAX_REFUND') TAX_REFUND
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_TAX_RUN_RESULT_ID') TAX_RUN_RESULT_ID
, PAY_GB_EOY_ARCHIVE.GET_ARCH_DATE(FAI.CONTEXT1
, 'X_TERMINATION_DATE') TERMINATION_DATE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_TERMINATION_TYPE') TERMINATION_TYPE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_TITLE') TITLE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_TOWN_OR_CITY') TOWN_OR_CITY
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_W1_M1_INDICATOR') W1_M1_INDICATOR
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI.CONTEXT1
, 'X_WEEK_53_INDICATOR') WEEK_53_INDICATOR
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_WIDOWS_AND_ORPHANS') WIDOWS_AND_ORPHANS
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_NOTIONAL_PAY') NOTIONAL_PAY
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_SAP') SAP
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_SPP_ADOPT') SPP_ADOPT
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_SPP_BIRTH') SPP_BIRTH
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_STUDENT_LOANS') STUDENT_LOANS
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_TAX_CREDITS') TAX_CREDITS
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_ASPP_ADOPT') ASPP_ADOPT
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI.CONTEXT1
, 'X_ASPP_BIRTH') ASPP_BIRTH
FROM PAY_ASSIGNMENT_ACTIONS ACT
, FF_ARCHIVE_ITEMS FAI
WHERE ACT.ASSIGNMENT_ACTION_ID = FAI.CONTEXT1
AND FAI.USER_ENTITY_ID = (SELECT FUE.USER_ENTITY_ID
FROM FF_USER_ENTITIES FUE
WHERE FUE.USER_ENTITY_NAME = 'X_PAYROLL_ID'
AND FUE.LEGISLATION_CODE = 'GB'
AND FUE.BUSINESS_GROUP_ID IS NULL)