PAY ROE Work Sheet
Description
- Categories
- BI Publisher
- Application
- Payroll
- Source
- ROE Work Sheet (XML)
- Short Name
- PYCAROEP_XML
- DB package
- PAY_PYCAROEP_XMLP_PKG
Imported from BI Publisher
ROE Work Sheet Report
ROE Work Sheet Report
SQL 211 lines
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 |
Parameters 6
| Parameter | Type | List of values SQL |
|---|---|---|
| Start Date | Date | |
| End Date | Date | |
| ROE Data | LOV Oracle | |
| Selection Type | LOV Oracle | |
| Employee Name | LOV Oracle | |
| Assignment Set | LOV Oracle |