ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4715951A9E05362FB09050512Y SELECT pcrv.ASSIGNMENT_ACTION_ID, (pay_ca_archive_utils.get_archive_value ((SELECT amend.locking_action_id FROM pay_action_interlocks amend , pay_action_interlocks org WHERE org.locked_action_id = pcrv.ASSIGNMENT_ACTION_ID AND org.locking_action_id = amend.locked_action_id), 'ROE_SERIAL_NUMBER')) ROE_SERIAL_NUMBER, decode(pcrv.report_type,'ROE',ROE_PREVIOUS_SERIAL_NUMBER,'AROE', ROE_SERIAL_NUMBER) ROE_PREVIOUS_SERIAL_NUMBER, ROE_PAYROLL_REFERENCE_NUMBER, ROE_TAX_UNIT_NAME, pcrv.ROE_TAX_UNIT_ADDRESS_LINE_1 TAX_UNIT_ADDRESS_LINE_1, pcrv.ROE_TAX_UNIT_ADDRESS_LINE_2 TAX_UNIT_ADDRESS_LINE_2, pcrv.ROE_TAX_UNIT_ADDRESS_LINE_3 TAX_UNIT_ADDRESS_LINE_3, ROE_TAX_UNIT_CITY, ROE_TAX_UNIT_PROVINCE, ROE_TAX_UNIT_COUNTRY, ROE_CA_EMPLR_IDENT_ORG_BUS_NO, ROE_PAY_PERIOD_TYPE, ROE_TAX_UNIT_POSTAL_CODE, ROE_PER_SOCIAL_INSURANCE_NUMBR, ROE_PER_FIRST_NAME, ROE_PER_LAST_NAME, ROE_PER_ADDRESS_LINE_1 EMP_ADDRESS_LINE_1, ROE_PER_ADDRESS_LINE_2 EMP_ADDRESS_LINE_2, ROE_PER_ADDRESS_LINE_3 EMP_ADDRESS_LINE_3, ROE_PER_CITY, ROE_PER_PROVINCE, ROE_PER_COUNTRY, ROE_PER_POSTAL_CODE, DECODE(ROE_EMP_PER_HIRE_DATE,null,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_EMP_PER_HIRE_DATE),'DD-MON-YYYY')) ROE_EMP_PER_HIRE_DATE, DECODE(ROE_PAY_EARNED_END_DATE,null,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_PAY_EARNED_END_DATE),'DD-MON-YYYY')) ROE_PAY_EARNED_END_DATE, DECODE(ROE_FINAL_PAY_PERIOD_END_DATE,null,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_FINAL_PAY_PERIOD_END_DATE),'DD-MON-YYYY')) ROE_FINAL_PAY_PERIOD_END_DATE, ROE_ASG_JOB, DECODE(ROE_UNKNOWN_NOT_RETURNING,'U','[X]','[ ]') ROE_UNKNOWN, DECODE(ROE_UNKNOWN_NOT_RETURNING,'N','[X]','[ ]') ROE_NOT_RETURNING, DECODE(ROE_EXPECTED_DATE_OF_RECALL,null,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_EXPECTED_DATE_OF_RECALL),'DD-MON-YYYY')) ROE_EXPECTED_DATE_OF_RECALL, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_TOTAL_INSURABLE_HOURS,0)) ROE_TOTAL_INSURABLE_HOURS, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_TOTAL_INSURABLE_EARNINGS,0)) ROE_TOTAL_INSURABLE_EARNINGS, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_1,0)) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT1,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT2,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT3,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT4,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT5,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT6,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT7,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT8,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT9,0))) + FND_NUMBER.CANONICAL_TO_NUMBER( DECODE(NVL(PAY_CA_EMP_TAX_INF.CHECK_EI_EXEMPT(pcrv.ASSIGNMENT_ID,FND_DATE.CANONICAL_TO_DATE(ROE_DATE)),'N'),'Y',0,NVL(ROE_BOX_17B_AMOUNT10,0))) ROE_INS_EARNING_1, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_2,0)) ROE_INS_EARNING_2, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_3,0)) ROE_INS_EARNING_3, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_4,0)) ROE_INS_EARNING_4, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_5,0)) ROE_INS_EARNING_5, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_6,0)) ROE_INS_EARNING_6, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_7,0)) ROE_INS_EARNING_7, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_8,0)) ROE_INS_EARNING_8, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_9,0)) ROE_INS_EARNING_9, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_10,0)) ROE_INS_EARNING_10, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_11,0)) ROE_INS_EARNING_11, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_12,0)) ROE_INS_EARNING_12, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_13,0)) ROE_INS_EARNING_13, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_14,0)) ROE_INS_EARNING_14, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_15,0)) ROE_INS_EARNING_15, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_16,0)) ROE_INS_EARNING_16, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_17,0)) ROE_INS_EARNING_17, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_18,0)) ROE_INS_EARNING_18, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_19,0)) ROE_INS_EARNING_19, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_20,0)) ROE_INS_EARNING_20, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_21,0)) ROE_INS_EARNING_21, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_22,0)) ROE_INS_EARNING_22, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_23,0)) ROE_INS_EARNING_23, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_24,0)) ROE_INS_EARNING_24, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_25,0)) ROE_INS_EARNING_25, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_26,0)) ROE_INS_EARNING_26, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_27,0)) ROE_INS_EARNING_27, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_28,0)) ROE_INS_EARNING_28, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_29,0)) ROE_INS_EARNING_29, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_30,0)) ROE_INS_EARNING_30, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_31,0)) ROE_INS_EARNING_31, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_32,0)) ROE_INS_EARNING_32, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_33,0)) ROE_INS_EARNING_33, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_34,0)) ROE_INS_EARNING_34, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_35,0)) ROE_INS_EARNING_35, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_36,0)) ROE_INS_EARNING_36, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_37,0)) ROE_INS_EARNING_37, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_38,0)) ROE_INS_EARNING_38, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_39,0)) ROE_INS_EARNING_39, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_40,0)) ROE_INS_EARNING_40, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_41,0)) ROE_INS_EARNING_41, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_42,0)) ROE_INS_EARNING_42, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_43,0)) ROE_INS_EARNING_43, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_44,0)) ROE_INS_EARNING_44, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_45,0)) ROE_INS_EARNING_45, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_46,0)) ROE_INS_EARNING_46, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_47,0)) ROE_INS_EARNING_47, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_48,0)) ROE_INS_EARNING_48, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_49,0)) ROE_INS_EARNING_49, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_50,0)) ROE_INS_EARNING_50, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_51,0)) ROE_INS_EARNING_51, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_52,0)) ROE_INS_EARNING_52, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_INSURABLE_EARNING_53,0)) ROE_INS_EARNING_53, ROE_REASON, ROE_CONTACT_PERSON, ROE_CONTACT_PHONE_NUMBER, ROE_17A_CODE, DECODE(ROE_17A_START_DATE,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_17A_START_DATE),'DD-MON-YYYY')) ROE_17A_START_DATE, DECODE(ROE_17A_END_DATE,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_17A_END_DATE),'DD-MON-YYYY')) ROE_17A_END_DATE, FND_NUMBER.CANONICAL_TO_NUMBER(nvl(ROE_BOX_17A,0)) ROE_BOX_17A, DECODE(ROE_BOX_17B_DATE1,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE1),'DD-MON-YYYY')) ROE_BOX_17B_DATE1, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT1,0)) ROE_BOX_17B_AMOUNT1, DECODE(ROE_BOX_17B_DATE2,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE2),'DD-MON-YYYY')) ROE_BOX_17B_DATE2, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT2,0)) ROE_BOX_17B_AMOUNT2, DECODE(ROE_BOX_17B_DATE3,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE3),'DD-MON-YYYY')) ROE_BOX_17B_DATE3, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT3,0)) ROE_BOX_17B_AMOUNT3, DECODE(ROE_BOX_17B_DATE4,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE4),'DD-MON-YYYY')) ROE_BOX_17B_DATE4, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT4,0)) ROE_BOX_17B_AMOUNT4, DECODE(ROE_BOX_17B_DATE5,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE5),'DD-MON-YYYY')) ROE_BOX_17B_DATE5, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT5,0)) ROE_BOX_17B_AMOUNT5, DECODE(ROE_BOX_17B_DATE6,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE6),'DD-MON-YYYY')) ROE_BOX_17B_DATE6, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT6,0)) ROE_BOX_17B_AMOUNT6, DECODE(ROE_BOX_17B_DATE7,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE7),'DD-MON-YYYY')) ROE_BOX_17B_DATE7, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT7,0)) ROE_BOX_17B_AMOUNT7, DECODE(ROE_BOX_17B_DATE8,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE8),'DD-MON-YYYY')) ROE_BOX_17B_DATE8, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT8,0)) ROE_BOX_17B_AMOUNT8, DECODE(ROE_BOX_17B_DATE9,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE9),'DD-MON-YYYY')) ROE_BOX_17B_DATE9, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT9,0)) ROE_BOX_17B_AMOUNT9, DECODE(ROE_BOX_17B_DATE10,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17B_DATE10),'DD-MON-YYYY')) ROE_BOX_17B_DATE10, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17B_AMOUNT10,0)) ROE_BOX_17B_AMOUNT10, pycadar_pkg.get_labels('PAY_CA_ROE_OTHER_MONIES',roe_box_17c_desc1) roe_box_17c_desc1, DECODE(ROE_BOX_17C_START_DATE1,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17C_START_DATE1),'DD-MON-YYYY')) ROE_BOX_17C_START_DATE1, DECODE(ROE_BOX_17C_END_DATE1,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17C_END_DATE1),'DD-MON-YYYY')) ROE_BOX_17C_END_DATE1, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17C_AMOUNT1,0)) ROE_BOX_17C_AMOUNT1, pycadar_pkg.get_labels('PAY_CA_ROE_OTHER_MONIES',roe_box_17c_desc2) roe_box_17c_desc2, DECODE(ROE_BOX_17C_START_DATE2,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17C_START_DATE2),'DD-MON-YYYY')) ROE_BOX_17C_START_DATE2, DECODE(ROE_BOX_17C_END_DATE2,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17C_END_DATE2),'DD-MON-YYYY')) ROE_BOX_17C_END_DATE2, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17C_AMOUNT2,0)) ROE_BOX_17C_AMOUNT2, pycadar_pkg.get_labels('PAY_CA_ROE_OTHER_MONIES',roe_box_17c_desc3) roe_box_17c_desc3, DECODE(ROE_BOX_17C_START_DATE3,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17C_START_DATE3),'DD-MON-YYYY')) ROE_BOX_17C_START_DATE3, DECODE(ROE_BOX_17C_END_DATE3,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_17C_END_DATE3),'DD-MON-YYYY')) ROE_BOX_17C_END_DATE3, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_17C_AMOUNT3,0)) ROE_BOX_17C_AMOUNT3, ROE_COMMENTS, DECODE(ROE_BOX_19_PAYMENT_START_DATE,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_START_DATE),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_START_DATE, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_19_PAYMENT_AMOUNT,0)) ROE_BOX_19_PAYMENT_AMOUNT, ROE_BOX_19_DAY_WEEK, ROE_BOX_19_PAYMENT_CODE1, DECODE(ROE_BOX_19_PAYMENT_START_DATE1,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_START_DATE1),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_START_DATE1, DECODE(ROE_BOX_19_PAYMENT_END_DATE1,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_END_DATE1),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_END_DATE1, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_19_PAYMENT_AMOUNT1,0)) ROE_BOX_19_PAYMENT_AMOUNT1, ROE_BOX_19_DAY_WEEK1, ROE_BOX_19_PAYMENT_CODE2, DECODE(ROE_BOX_19_PAYMENT_START_DATE2,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_START_DATE2),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_START_DATE2, DECODE(ROE_BOX_19_PAYMENT_END_DATE2,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_END_DATE2),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_END_DATE2, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_19_PAYMENT_AMOUNT2,0)) ROE_BOX_19_PAYMENT_AMOUNT2, ROE_BOX_19_DAY_WEEK2, ROE_BOX_19_PAYMENT_CODE3, DECODE(ROE_BOX_19_PAYMENT_START_DATE3,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_START_DATE3),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_START_DATE3, DECODE(ROE_BOX_19_PAYMENT_END_DATE3,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_END_DATE3),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_END_DATE3, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_19_PAYMENT_AMOUNT3,0)) ROE_BOX_19_PAYMENT_AMOUNT3, ROE_BOX_19_DAY_WEEK3, ROE_BOX_19_PAYMENT_CODE4, DECODE(ROE_BOX_19_PAYMENT_START_DATE4,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_START_DATE4),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_START_DATE4, DECODE(ROE_BOX_19_PAYMENT_END_DATE4,NULL,NULL, TO_CHAR(FND_DATE.CANONICAL_TO_DATE(ROE_BOX_19_PAYMENT_END_DATE4),'DD-MON-YYYY')) ROE_BOX_19_PAYMENT_END_DATE4, FND_NUMBER.CANONICAL_TO_NUMBER(NVL(ROE_BOX_19_PAYMENT_AMOUNT4,0)) ROE_BOX_19_PAYMENT_AMOUNT4, ROE_BOX_19_DAY_WEEK4, ROE_PER_CORRESPONDENCE_LANG, ROE_PER_TELEPHONE_NUMBER, ROE_INCLUDE_EXCLUDE, ROE_MANUAL, PAY_PYCAROEP_XMLP_PKG.cf_roe_pay_period_typeformula(ROE_PAY_PERIOD_TYPE) CF_ROE_PAY_PERIOD_TYPE, PAY_PYCAROEP_XMLP_PKG.cf_languageformula(ROE_PER_CORRESPONDENCE_LANG, DECODE ( ROE_FINAL_PAY_PERIOD_END_DATE , null , NULL , TO_CHAR ( FND_DATE.CANONICAL_TO_DATE ( ROE_FINAL_PAY_PERIOD_END_DATE ) , 'DD-MM-YYYY' ) )) CF_Language FROM pay_ca_roe_values_v pcrv, per_assignments_f paf &LP_ASSIGNMENT_SET WHERE pcrv.business_group_id=NVL(:P_BUSINESS_GROUP_ID,pcrv.business_group_id) AND pcrv.assignment_id=paf.assignment_id AND FND_DATE.CANONICAL_TO_DATE(pcrv.ROE_ASSIGNMENT_START_DATE) = paf.effective_start_date &LP_DATE_OR_PERSON ORDER BY ROE_PER_LAST_NAME,ROE_PER_FIRST_NAME,EFFECTIVE_DATE desc,REPORT_TYPE,ASSIGNMENT_ACTION_ID DESC PAY PYCAROEP_XML US PAY ROE Work Sheet Imported from BI Publisher Description: ROE Work Sheet Report Application: Payroll Source: ROE Work Sheet (XML) Short Name: PYCAROEP_XML DB package: PAY_PYCAROEP_XMLP_PKG BI Publisher &lp_assignment_set &lp_date_or_person :c_business_group_name :c_report_subtitle :cp_assignment_set_name :cp_effective_date :cp_end_date :cp_person :cp_roe_data :cp_selection_type :cp_start_date :lp_assignment_set :lp_date_or_person :p_assignment_set :p_business_group_id :p_conc_request_id :p_end_date :p_person_id :p_report_title :p_roe_data :p_selection_type :p_session_date :p_start_date 1 -40 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Id Business Group Id 2 -20 :p_ls_assignment_set Char select '1' from dual where :$flex$.selection_type:NULL = 'ASSIGNMENT_SET' US PER_CHAR 3 -30 :p_ls_person_id Char select '1' from dual where :$flex$.selection_type:NULL = 'PERSON_ID' US PER_CHAR 4 10 :p_start_date Date US Start Date Start Date 5 20 :p_end_date Date US End Date End Date 6 30 :p_roe_data LOV Oracle PAY_CA_ROE_TYPE Y select lookup_code id, meaning value, description description from hr_lookups where lookup_type='PAY_CA_ROE_TYPE' order by lookup_code SELECT MEANING FROM fnd_lookup_values WHERE lookup_type = 'PAY_CA_ROE_TYPE' AND language = userenv ('LANG') AND lookup_code = '03' Y US ROE Data ROE Data 7 40 :p_selection_type LOV Oracle ROE_CA_SELECTION_TYPE Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='PAY_CA_ROE_SELECTION_TYPE' order by value,description SELECT null from dual US Selection Type Selection Type 8 50 :p_person_id LOV Oracle ROE_CA_PERSON_ID Y Y select ppf.person_id id, ppf.full_name value, null description from per_all_people_f ppf,per_person_types ppt,per_assignments_f paf where ppf.business_group_id=fnd_global.per_business_group_id and ppt.person_type_id=ppf.person_type_id and ppt.system_person_type in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') and ppf.effective_start_date=(select max(effective_start_date) from per_people_f ppf1 where ppf1.person_id=ppf.person_id) and ppf.person_id=paf.person_id and paf.effective_start_date=(select max(effective_start_date) from per_assignments_f paf1 where paf1.assignment_id=paf.assignment_id) and paf.assignment_type='E' and paf.primary_flag='Y' and :$flex$.per_char='1' order by ppf.last_name,ppf.first_name US Employee Name Person Id 9 60 :p_assignment_set LOV Oracle ROE_CA_ASSIGNMENT_SET Y select assignment_set_id id, assignment_set_name value, null description from hr_assignment_sets where business_group_id=fnd_global.per_business_group_id and :$flex$.per_char='1' order by assignment_set_name US Assignment Set Assignment Set