VIEW: APPS.PAY_CA_ROE_VALUES_V

Source

View Text - Preformatted

SELECT ppa.payroll_action_id, paa.assignment_action_id, ppa.effective_date, paa.assignment_id, ppa.business_group_id, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_SERIAL_NUMBER') ROE_SERIAL_NUMBER, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PREVIOUS_SERIAL_NUMBER') ROE_PREVIOUS_SERIAL_NUMBER, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_ASG_NUMBER') ROE_PAYROLL_REFERENCE_NUMBER, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_NAME') ROE_TAX_UNIT_NAME, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_ADDRESS_LINE_1') ROE_TAX_UNIT_ADDRESS_LINE_1, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_ADDRESS_LINE_2') ROE_TAX_UNIT_ADDRESS_LINE_2, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_ADDRESS_LINE_3') ROE_TAX_UNIT_ADDRESS_LINE_3, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_PROVINCE') ROE_TAX_UNIT_PROVINCE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_CITY') ROE_TAX_UNIT_CITY, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_COUNTRY') ROE_TAX_UNIT_COUNTRY, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_CANADA_EMPLOYER_IDENTIFICATION_ORG_BUSINESS_NUMBER') ROE_CA_EMPLR_IDENT_ORG_BUS_NO, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PAY_PERIOD_TYPE') ROE_PAY_PERIOD_TYPE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TAX_UNIT_POSTAL_CODE') ROE_TAX_UNIT_POSTAL_CODE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_SOCIAL_INSURANCE_NUMBER') ROE_PER_SOCIAL_INSURANCE_NUMBR, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_FIRST_NAME') ROE_PER_FIRST_NAME, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_LAST_NAME') ROE_PER_LAST_NAME, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_ADDRESS_LINE_1') ROE_PER_ADDRESS_LINE_1, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_ADDRESS_LINE_2') ROE_PER_ADDRESS_LINE_2, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_ADDRESS_LINE_3') ROE_PER_ADDRESS_LINE_3, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_PROVINCE') ROE_PER_PROVINCE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_CITY') ROE_PER_CITY, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_COUNTRY') ROE_PER_COUNTRY, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_POSTAL_CODE') ROE_PER_POSTAL_CODE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_EMP_PER_HIRE_DATE') ROE_EMP_PER_HIRE_DATE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PAY_EARNED_END_DATE') ROE_PAY_EARNED_END_DATE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_FINAL_PAY_PERIOD_ENDING_DATE') ROE_FINAL_PAY_PERIOD_END_DATE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_ASG_JOB') ROE_ASG_JOB, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_UNKNOWN_NOT_RETURNING') ROE_UNKNOWN_NOT_RETURNING, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_EXPECTED_DATE_OF_RECALL') ROE_EXPECTED_DATE_OF_RECALL, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TOTAL_INSURABLE_HOURS') ROE_TOTAL_INSURABLE_HOURS, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_TOTAL_INSURABLE_EARNINGS') ROE_TOTAL_INSURABLE_EARNINGS, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_1') ROE_INSURABLE_EARNING_1, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_2') ROE_INSURABLE_EARNING_2, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_3') ROE_INSURABLE_EARNING_3, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_4') ROE_INSURABLE_EARNING_4, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_5') ROE_INSURABLE_EARNING_5, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_6') ROE_INSURABLE_EARNING_6, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_7') ROE_INSURABLE_EARNING_7, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_8') ROE_INSURABLE_EARNING_8, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_9') ROE_INSURABLE_EARNING_9, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_10') ROE_INSURABLE_EARNING_10, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_11') ROE_INSURABLE_EARNING_11, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_12') ROE_INSURABLE_EARNING_12, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_13') ROE_INSURABLE_EARNING_13, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_14') ROE_INSURABLE_EARNING_14, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_15') ROE_INSURABLE_EARNING_15, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_16') ROE_INSURABLE_EARNING_16, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_17') ROE_INSURABLE_EARNING_17, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_18') ROE_INSURABLE_EARNING_18, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_19') ROE_INSURABLE_EARNING_19, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_20') ROE_INSURABLE_EARNING_20, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_21') ROE_INSURABLE_EARNING_21, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_22') ROE_INSURABLE_EARNING_22, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_23') ROE_INSURABLE_EARNING_23, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_24') ROE_INSURABLE_EARNING_24, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_25') ROE_INSURABLE_EARNING_25, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_26') ROE_INSURABLE_EARNING_26, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_27') ROE_INSURABLE_EARNING_27, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_28') ROE_INSURABLE_EARNING_28, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_29') ROE_INSURABLE_EARNING_29, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_30') ROE_INSURABLE_EARNING_30, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_31') ROE_INSURABLE_EARNING_31, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_32') ROE_INSURABLE_EARNING_32, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_33') ROE_INSURABLE_EARNING_33, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_34') ROE_INSURABLE_EARNING_34, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_35') ROE_INSURABLE_EARNING_35, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_36') ROE_INSURABLE_EARNING_36, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_37') ROE_INSURABLE_EARNING_37, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_38') ROE_INSURABLE_EARNING_38, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_39') ROE_INSURABLE_EARNING_39, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_40') ROE_INSURABLE_EARNING_40, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_41') ROE_INSURABLE_EARNING_41, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_42') ROE_INSURABLE_EARNING_42, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_43') ROE_INSURABLE_EARNING_43, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_44') ROE_INSURABLE_EARNING_44, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_45') ROE_INSURABLE_EARNING_45, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_46') ROE_INSURABLE_EARNING_46, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_47') ROE_INSURABLE_EARNING_47, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_48') ROE_INSURABLE_EARNING_48, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_49') ROE_INSURABLE_EARNING_49, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_50') ROE_INSURABLE_EARNING_50, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_51') ROE_INSURABLE_EARNING_51, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_52') ROE_INSURABLE_EARNING_52, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INSURABLE_EARNING_53') ROE_INSURABLE_EARNING_53, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_REASON') ROE_REASON, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_CONTACT_PERSON') ROE_CONTACT_PERSON, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_CONTACT_PHONE_NUMBER') ROE_CONTACT_PHONE_NUMBER, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17A') ROE_BOX_17A, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17B_DATE1') ROE_BOX_17B_DATE1, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17B_AMOUNT1') ROE_BOX_17B_AMOUNT1, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17B_DATE2') ROE_BOX_17B_DATE2, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17B_AMOUNT2') ROE_BOX_17B_AMOUNT2, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17B_DATE3') ROE_BOX_17B_DATE3, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17B_AMOUNT3') ROE_BOX_17B_AMOUNT3, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17C_DESC1') ROE_BOX_17C_DESC1, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17C_AMOUNT1') ROE_BOX_17C_AMOUNT1, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17C_DESC2') ROE_BOX_17C_DESC2, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17C_AMOUNT2') ROE_BOX_17C_AMOUNT2, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17C_DESC3') ROE_BOX_17C_DESC3, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_17C_AMOUNT3') ROE_BOX_17C_AMOUNT3, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_COMMENTS') ROE_COMMENTS, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_19_PAYMENT_START_DATE') ROE_BOX_19_PAYMENT_START_DATE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_19_PAYMENT_AMOUNT') ROE_BOX_19_PAYMENT_AMOUNT, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_BOX_19_DAY_WEEK') ROE_BOX_19_DAY_WEEK, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_CORRESPONDENCE_LANGUAGE') ROE_PER_CORRESPONDENCE_LANG, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_PER_TELEPHONE_NUMBER') ROE_PER_TELEPHONE_NUMBER, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_ISSUER') ROE_ISSUER, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_INCLUDE_EXCLUDE') ROE_INCLUDE_EXCLUDE, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_MANUAL') ROE_MANUAL, pay_ca_archive_utils.get_archive_value(paa.assignment_action_id, 'ROE_DATE') ROE_DATE FROM pay_assignment_actions paa, pay_payroll_actions ppa WHERE ppa.payroll_action_id=paa.payroll_action_id AND ppa.action_type='X' AND ppa.action_status='C' AND ppa.report_type='ROE'

View Text - HTML Formatted

SELECT PPA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PPA.EFFECTIVE_DATE
, PAA.ASSIGNMENT_ID
, PPA.BUSINESS_GROUP_ID
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_SERIAL_NUMBER') ROE_SERIAL_NUMBER
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PREVIOUS_SERIAL_NUMBER') ROE_PREVIOUS_SERIAL_NUMBER
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_ASG_NUMBER') ROE_PAYROLL_REFERENCE_NUMBER
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_NAME') ROE_TAX_UNIT_NAME
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_ADDRESS_LINE_1') ROE_TAX_UNIT_ADDRESS_LINE_1
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_ADDRESS_LINE_2') ROE_TAX_UNIT_ADDRESS_LINE_2
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_ADDRESS_LINE_3') ROE_TAX_UNIT_ADDRESS_LINE_3
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_PROVINCE') ROE_TAX_UNIT_PROVINCE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_CITY') ROE_TAX_UNIT_CITY
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_COUNTRY') ROE_TAX_UNIT_COUNTRY
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_CANADA_EMPLOYER_IDENTIFICATION_ORG_BUSINESS_NUMBER') ROE_CA_EMPLR_IDENT_ORG_BUS_NO
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PAY_PERIOD_TYPE') ROE_PAY_PERIOD_TYPE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TAX_UNIT_POSTAL_CODE') ROE_TAX_UNIT_POSTAL_CODE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_SOCIAL_INSURANCE_NUMBER') ROE_PER_SOCIAL_INSURANCE_NUMBR
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_FIRST_NAME') ROE_PER_FIRST_NAME
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_LAST_NAME') ROE_PER_LAST_NAME
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_ADDRESS_LINE_1') ROE_PER_ADDRESS_LINE_1
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_ADDRESS_LINE_2') ROE_PER_ADDRESS_LINE_2
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_ADDRESS_LINE_3') ROE_PER_ADDRESS_LINE_3
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_PROVINCE') ROE_PER_PROVINCE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_CITY') ROE_PER_CITY
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_COUNTRY') ROE_PER_COUNTRY
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_POSTAL_CODE') ROE_PER_POSTAL_CODE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_EMP_PER_HIRE_DATE') ROE_EMP_PER_HIRE_DATE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PAY_EARNED_END_DATE') ROE_PAY_EARNED_END_DATE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_FINAL_PAY_PERIOD_ENDING_DATE') ROE_FINAL_PAY_PERIOD_END_DATE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_ASG_JOB') ROE_ASG_JOB
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_UNKNOWN_NOT_RETURNING') ROE_UNKNOWN_NOT_RETURNING
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_EXPECTED_DATE_OF_RECALL') ROE_EXPECTED_DATE_OF_RECALL
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TOTAL_INSURABLE_HOURS') ROE_TOTAL_INSURABLE_HOURS
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_TOTAL_INSURABLE_EARNINGS') ROE_TOTAL_INSURABLE_EARNINGS
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_1') ROE_INSURABLE_EARNING_1
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_2') ROE_INSURABLE_EARNING_2
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_3') ROE_INSURABLE_EARNING_3
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_4') ROE_INSURABLE_EARNING_4
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_5') ROE_INSURABLE_EARNING_5
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_6') ROE_INSURABLE_EARNING_6
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_7') ROE_INSURABLE_EARNING_7
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_8') ROE_INSURABLE_EARNING_8
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_9') ROE_INSURABLE_EARNING_9
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_10') ROE_INSURABLE_EARNING_10
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_11') ROE_INSURABLE_EARNING_11
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_12') ROE_INSURABLE_EARNING_12
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_13') ROE_INSURABLE_EARNING_13
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_14') ROE_INSURABLE_EARNING_14
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_15') ROE_INSURABLE_EARNING_15
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_16') ROE_INSURABLE_EARNING_16
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_17') ROE_INSURABLE_EARNING_17
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_18') ROE_INSURABLE_EARNING_18
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_19') ROE_INSURABLE_EARNING_19
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_20') ROE_INSURABLE_EARNING_20
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_21') ROE_INSURABLE_EARNING_21
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_22') ROE_INSURABLE_EARNING_22
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_23') ROE_INSURABLE_EARNING_23
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_24') ROE_INSURABLE_EARNING_24
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_25') ROE_INSURABLE_EARNING_25
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_26') ROE_INSURABLE_EARNING_26
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_27') ROE_INSURABLE_EARNING_27
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_28') ROE_INSURABLE_EARNING_28
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_29') ROE_INSURABLE_EARNING_29
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_30') ROE_INSURABLE_EARNING_30
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_31') ROE_INSURABLE_EARNING_31
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_32') ROE_INSURABLE_EARNING_32
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_33') ROE_INSURABLE_EARNING_33
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_34') ROE_INSURABLE_EARNING_34
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_35') ROE_INSURABLE_EARNING_35
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_36') ROE_INSURABLE_EARNING_36
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_37') ROE_INSURABLE_EARNING_37
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_38') ROE_INSURABLE_EARNING_38
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_39') ROE_INSURABLE_EARNING_39
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_40') ROE_INSURABLE_EARNING_40
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_41') ROE_INSURABLE_EARNING_41
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_42') ROE_INSURABLE_EARNING_42
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_43') ROE_INSURABLE_EARNING_43
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_44') ROE_INSURABLE_EARNING_44
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_45') ROE_INSURABLE_EARNING_45
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_46') ROE_INSURABLE_EARNING_46
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_47') ROE_INSURABLE_EARNING_47
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_48') ROE_INSURABLE_EARNING_48
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_49') ROE_INSURABLE_EARNING_49
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_50') ROE_INSURABLE_EARNING_50
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_51') ROE_INSURABLE_EARNING_51
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_52') ROE_INSURABLE_EARNING_52
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INSURABLE_EARNING_53') ROE_INSURABLE_EARNING_53
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_REASON') ROE_REASON
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_CONTACT_PERSON') ROE_CONTACT_PERSON
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_CONTACT_PHONE_NUMBER') ROE_CONTACT_PHONE_NUMBER
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17A') ROE_BOX_17A
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17B_DATE1') ROE_BOX_17B_DATE1
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17B_AMOUNT1') ROE_BOX_17B_AMOUNT1
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17B_DATE2') ROE_BOX_17B_DATE2
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17B_AMOUNT2') ROE_BOX_17B_AMOUNT2
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17B_DATE3') ROE_BOX_17B_DATE3
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17B_AMOUNT3') ROE_BOX_17B_AMOUNT3
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17C_DESC1') ROE_BOX_17C_DESC1
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17C_AMOUNT1') ROE_BOX_17C_AMOUNT1
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17C_DESC2') ROE_BOX_17C_DESC2
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17C_AMOUNT2') ROE_BOX_17C_AMOUNT2
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17C_DESC3') ROE_BOX_17C_DESC3
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_17C_AMOUNT3') ROE_BOX_17C_AMOUNT3
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_COMMENTS') ROE_COMMENTS
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_19_PAYMENT_START_DATE') ROE_BOX_19_PAYMENT_START_DATE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_19_PAYMENT_AMOUNT') ROE_BOX_19_PAYMENT_AMOUNT
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_BOX_19_DAY_WEEK') ROE_BOX_19_DAY_WEEK
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_CORRESPONDENCE_LANGUAGE') ROE_PER_CORRESPONDENCE_LANG
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_PER_TELEPHONE_NUMBER') ROE_PER_TELEPHONE_NUMBER
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_ISSUER') ROE_ISSUER
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_INCLUDE_EXCLUDE') ROE_INCLUDE_EXCLUDE
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_MANUAL') ROE_MANUAL
, PAY_CA_ARCHIVE_UTILS.GET_ARCHIVE_VALUE(PAA.ASSIGNMENT_ACTION_ID
, 'ROE_DATE') ROE_DATE
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.PAYROLL_ACTION_ID=PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE='X'
AND PPA.ACTION_STATUS='C'
AND PPA.REPORT_TYPE='ROE'