PAY ROE Work Sheet

➜ Back to Library
Description
Categories: BI Publisher
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
Run PAY ROE Work Sheet and other Oracle EBS reports with Blitz Report™ on our demo environment
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