Oracle HRMS R12 Absence Screen DFF structure based on Employee Payroll

1.

SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

AND PAYROLL_ID IN (SELECT PAYROLL_ID FROM PER_ALL_ASSIGNMENTS_F WHERE PERSON_ID = :$PROFILES$.PER_PERSON_ID AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE

)

 Testing:

/*update the dff exist data to our new dff structure*/

DECLARE

V_COUNT NUMBER := 0;

CURSOR C1 IS

SELECT PERSON_ID,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10

,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18

--SELECT COUNT(*)

FROM PER_ABSENCE_ATTENDANCES PAA

WHERE

PERSON_ID IS NOT NULL

AND (

ATTRIBUTE1  IS NOT NULL OR ATTRIBUTE2  IS NOT NULL

OR ATTRIBUTE3  IS NOT NULL OR ATTRIBUTE4  IS NOT NULL

OR ATTRIBUTE5  IS NOT NULL OR ATTRIBUTE9  IS NOT NULL

OR ATTRIBUTE11  IS NOT NULL OR ATTRIBUTE12  IS NOT NULL

OR ATTRIBUTE13  IS NOT NULL OR ATTRIBUTE14  IS NOT NULL

OR ATTRIBUTE15  IS NOT NULL OR ATTRIBUTE16  IS NOT NULL

OR ATTRIBUTE17  IS NOT NULL OR ATTRIBUTE18  IS NOT NULL

OR ATTRIBUTE19  IS NOT NULL OR ATTRIBUTE20  IS NOT NULL

)

--AND PAA.PERSON_ID = 26876

--AND PAA.ABSENCE_ATTENDANCE_ID=3946276

;

BEGIN

FOR I IN C1 LOOP

UPDATE PER_ABSENCE_ATTENDANCES PAA

SET PAA.ATTRIBUTE_CATEGORY = XXPY_CONSALIDATIONS.GET_PAYROLL_ID_PERSON(81,I.PERSON_ID)

WHERE I.PERSON_ID = PAA.PERSON_ID

;

COMMIT;

V_COUNT := V_COUNT + 1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(V_COUNT);

END;