PAY ROE Work Sheet

➜ Back to Library
Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: ROE Work Sheet (XML)
Short Name: PYCAROEP_XML
DB package: PAY_PYCAROEP_XMLP_PKG 
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) + 
			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)) + 
			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)) + 
			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)) + 
			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)) + 
			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)) + 
			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)) + 
			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)) + 
			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)) + 
			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)) + 
			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
AND     ((:P_ROE_DATA = '01' AND pcrv.report_type = 'ROE')
         OR (:P_ROE_DATA = '03' AND ((pcrv.report_type ='ROE' AND (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) is null)
                                    OR (pcrv.report_type ='AROE' AND (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) is null)))
         OR (:P_ROE_DATA = '02' AND (pcrv.report_type  ='AROE' OR (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) is not null)))             
&LP_DATE_OR_PERSON
ORDER BY ROE_PER_LAST_NAME,ROE_PER_FIRST_NAME,EFFECTIVE_DATE desc,REPORT_TYPE,ASSIGNMENT_ACTION_ID DESC
Parameter Name SQL text Validation
PER_CHAR 
 
PER_CHAR 
 
Business Group Id 
 
 Number
Assignment Set 
 
 LOV Oracle
Employee Name 
 
 LOV Oracle
Selection Type 
 
 LOV Oracle
ROE Data 
 
 LOV Oracle
End Date 
 
 Date
Start Date 
 
 Date
Download
Related Blitz Reports
PAY IE Cheque Listing
PAY IE P30
PAY Void Payments
PAY NACHA
PAY Tax Payments Listing
PAY Business Payrolls Survey
PSP Organization Default Usage Encumbrance
PAY GB BIK Audit
PSP Encumbrance Suspense Charge
Blitz Report In Action