CREATE OR REPLACE TRIGGER APPS.INS_INTO_HXT_ADD_ASSIGN_INFO
BEFORE INSERT OR UPDATE
ON HR.PER_PERSON_ANALYSES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
L_ID NUMBER;
L_ASSIGNMENT_ID NUMBER;
L_ROTATION_PLAN_ID NUMBER;
L_EARNING_POLICY_ID NUMBER;
L_SHIFT_DIFFERENTIAL_POLICY_ID NUMBER;
L_HOUR_DEDUCTION_POLICY_ID NUMBER;
L_ATTRIBUTE1 VARCHAR2(150);
L_ATTRIBUTE2 VARCHAR2(150);
L_ATTRIBUTE3 VARCHAR2(150);
BEGIN
IF :NEW.ID_FLEX_NUM = 50551
THEN
SELECT AAI.ID, AAI.ASSIGNMENT_ID, AAI.ROTATION_PLAN,
AAI.EARNING_POLICY, AAI.SHIFT_DIFFERENTIAL_POLICY,
AAI.HOUR_DEDUCTION_POLICY,
AAI.ATTRIBUTE1, AAI.ATTRIBUTE2, AAI.ATTRIBUTE3
INTO L_ID, L_ASSIGNMENT_ID, L_ROTATION_PLAN_ID,
L_EARNING_POLICY_ID, L_SHIFT_DIFFERENTIAL_POLICY_ID,
L_HOUR_DEDUCTION_POLICY_ID, L_ATTRIBUTE1, L_ATTRIBUTE2, L_ATTRIBUTE3
FROM HXT_ADD_ASSIGN_INFO_F AAI
WHERE 1 = 1
AND :NEW.DATE_FROM BETWEEN AAI.EFFECTIVE_START_DATE
AND AAI.EFFECTIVE_END_DATE
AND AAI.ASSIGNMENT_ID =
(SELECT PAAF.ASSIGNMENT_ID
FROM PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.PERSON_ID = :NEW.PERSON_ID
AND :NEW.DATE_FROM BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAAF.PRIMARY_FLAG = 'Y');
BEGIN
IFL_UPD_HXT_ADD_ASSIGN_INFO_F(P_ID => L_ID,
P_DATETRACK_MODE => 'UPDATE_OVERRIDE',
P_EFFECTIVE_START_DATE => :NEW.DATE_FROM,
P_EFFECTIVE_END_DATE => :NEW.DATE_TO,
P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_AUTOGEN_HOURS_YN => 'Y',
P_ROTATION_PLAN => L_ROTATION_PLAN_ID,
P_EARNING_POLICY => L_EARNING_POLICY_ID,
P_SHIFT_DIFFERENTIAL_POLICY => L_SHIFT_DIFFERENTIAL_POLICY_ID,
P_HOUR_DEDUCTION_POLICY => L_HOUR_DEDUCTION_POLICY_ID,
P_ATTRIBUTE1 => L_ATTRIBUTE1,
P_ATTRIBUTE2 => L_ATTRIBUTE2,
P_ATTRIBUTE3 => L_ATTRIBUTE3,
P_CREATED_BY => FND_PROFILE.VALUE('USER_ID'),
P_CREATION_DATE => SYSDATE,
P_LAST_UPDATED_BY => FND_PROFILE.VALUE('USER_ID'),
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATE_LOGIN => FND_PROFILE.VALUE('LOGIN_ID')
);
END;
END IF;
END;
/
-------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE APPS.IFL_UPD_HXT_ADD_ASSIGN_INFO_F (
P_ID NUMBER,
P_DATETRACK_MODE VARCHAR2,
P_EFFECTIVE_START_DATE DATE,
P_EFFECTIVE_END_DATE DATE,
P_ASSIGNMENT_ID NUMBER,
P_AUTOGEN_HOURS_YN VARCHAR2,
P_ROTATION_PLAN NUMBER,
P_EARNING_POLICY NUMBER,
P_SHIFT_DIFFERENTIAL_POLICY NUMBER,
P_HOUR_DEDUCTION_POLICY NUMBER,
P_ATTRIBUTE1 VARCHAR2,
P_ATTRIBUTE2 VARCHAR2,
P_ATTRIBUTE3 VARCHAR2,
P_CREATED_BY NUMBER,
P_CREATION_DATE DATE,
P_LAST_UPDATED_BY NUMBER,
P_LAST_UPDATE_DATE DATE,
P_LAST_UPDATE_LOGIN NUMBER
)
IS
-- ARGUMENTS
-- P_ID -ID OF THE RECORD.
-- P_DATETRACK_MODE -THE MODE IN WHICH THE API HAS TO BE RUN.
-- -IT CAN BE RUN IN 'CORRECTION' OR
-- -'UPDATE_OVERRIDE' MODE.
-- P_EFFECTIVE_START_DATE -THE RECORD WILL BE UPDATE DAS OF THIS DATE.
-- P_EFFECTIVE_START_DATE -EFFECTIVE_START_DATE OF THE RECORD FOR THIS
-- -ASSIGNMENT THAT HAS EFFECTIVE_END_DATE AS
-- -END_OF_TIME.
-- P_ASSIGNMENT_ID -ASSIGNMENT_ID FOR WHICH THE UPDATE HAS TO BE
-- -DONE.
-- P_AUTOGEN_HOURS_YN -THE VALUE FOR THIS ARGUMENT CAN BE EITHER
-- -'Y' OR 'N',TO SPECIFY WHETHER TO AUTOGEN
-- -THE HOURS FOR THIS ASSIGNMENT.
--P_ROTATION_PLAN -ROTATION_PLAN_ID FOR THIS ASSIGNMENT.
--P_EARNING_POLICY -EARNING_POLICY_ID FOR THIS ASSIGNMENT.
--P_SHIFT_DIFFERENTIAL_POLICY -SHIFT_DIFFERENTIAL_POLICY_ID FOR THIS
-- -ASSIGNMENT.
--P_HOUR_DEDUCTION_POLICY -HOUR_DEDUCTION_POLICY_ID FOR THIS ASSIGNMENT.
CURSOR C1
IS
SELECT ROWID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
FROM HXT_ADD_ASSIGN_INFO_F
WHERE ID = P_ID
AND EFFECTIVE_START_DATE = (SELECT MAX (EFFECTIVE_START_DATE)
FROM HXT_ADD_ASSIGN_INFO_F
WHERE ID = P_ID);
CURSOR GET_FUTURE_RECORDS
IS
SELECT ROWIDTOCHAR (ROWID), ID
FROM HXT_ADD_ASSIGN_INFO_F
WHERE ID = P_ID
AND EFFECTIVE_START_DATE > P_EFFECTIVE_START_DATE;
L_EFFECTIVE_START_DATE DATE;
L_EFFECTIVE_END_DATE DATE;
C1_ROWID VARCHAR2 (30);
L_NEW_EFF_ST_DATE DATE;
L_NEW_ROWID VARCHAR2 (30);
TYPE VARCHARTABLE IS TABLE OF VARCHAR2 (30);
TYPE NUMTABLE IS TABLE OF NUMBER;
IDTAB NUMTABLE;
ROWIDTAB VARCHARTABLE;
BEGIN
OPEN C1;
FETCH C1 INTO C1_ROWID, L_EFFECTIVE_START_DATE, L_EFFECTIVE_END_DATE;
CLOSE C1;
IF C1_ROWID IS NOT NULL
THEN
/* END DATE THE EXISTING RECORD AND THEN CREATE A NEW RECORD IF AN
EXISTING RECORD FOUND */
OPEN GET_FUTURE_RECORDS;
FETCH GET_FUTURE_RECORDS BULK COLLECT INTO ROWIDTAB, IDTAB;
CLOSE GET_FUTURE_RECORDS;
IF (IDTAB.COUNT > 0)
THEN
FORALL I IN ROWIDTAB.FIRST .. ROWIDTAB.LAST
DELETE FROM HXT_ADD_ASSIGN_INFO_F
WHERE ROWID = CHARTOROWID (ROWIDTAB (I));
UPDATE HXT_ADD_ASSIGN_INFO_F AAI
SET AAI.EFFECTIVE_END_DATE = P_EFFECTIVE_START_DATE - 1
WHERE AAI.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND P_EFFECTIVE_START_DATE BETWEEN AAI.EFFECTIVE_START_DATE
AND AAI.EFFECTIVE_END_DATE;
ELSE
UPDATE HXT_ADD_ASSIGN_INFO_F
SET EFFECTIVE_END_DATE = P_EFFECTIVE_START_DATE - 1
WHERE ROWID = C1_ROWID;
END IF;
INSERT INTO HXT_ADD_ASSIGN_INFO_F
(ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ASSIGNMENT_ID,
AUTOGEN_HOURS_YN,
ROTATION_PLAN,
EARNING_POLICY,
SHIFT_DIFFERENTIAL_POLICY,
HOUR_DEDUCTION_POLICY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (P_ID,
P_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE,--HR_GENERAL.END_OF_TIME,
P_ASSIGNMENT_ID,
P_AUTOGEN_HOURS_YN,
P_ROTATION_PLAN,
P_EARNING_POLICY,
P_SHIFT_DIFFERENTIAL_POLICY,
P_HOUR_DEDUCTION_POLICY,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_CREATED_BY,
P_CREATION_DATE,
P_LAST_UPDATED_BY,
P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN
);
INSERT INTO HXT_ADD_ASSIGN_INFO_F
(ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ASSIGNMENT_ID,
AUTOGEN_HOURS_YN,
ROTATION_PLAN,
EARNING_POLICY,
SHIFT_DIFFERENTIAL_POLICY,
HOUR_DEDUCTION_POLICY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (P_ID,
P_EFFECTIVE_END_DATE + 1,
HR_GENERAL.END_OF_TIME,
P_ASSIGNMENT_ID,
'N',
P_ROTATION_PLAN,
P_EARNING_POLICY,
P_SHIFT_DIFFERENTIAL_POLICY,
P_HOUR_DEDUCTION_POLICY,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_CREATED_BY,
P_CREATION_DATE,
P_LAST_UPDATED_BY,
P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN
);
END IF;
END;
/
BEFORE INSERT OR UPDATE
ON HR.PER_PERSON_ANALYSES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
L_ID NUMBER;
L_ASSIGNMENT_ID NUMBER;
L_ROTATION_PLAN_ID NUMBER;
L_EARNING_POLICY_ID NUMBER;
L_SHIFT_DIFFERENTIAL_POLICY_ID NUMBER;
L_HOUR_DEDUCTION_POLICY_ID NUMBER;
L_ATTRIBUTE1 VARCHAR2(150);
L_ATTRIBUTE2 VARCHAR2(150);
L_ATTRIBUTE3 VARCHAR2(150);
BEGIN
IF :NEW.ID_FLEX_NUM = 50551
THEN
SELECT AAI.ID, AAI.ASSIGNMENT_ID, AAI.ROTATION_PLAN,
AAI.EARNING_POLICY, AAI.SHIFT_DIFFERENTIAL_POLICY,
AAI.HOUR_DEDUCTION_POLICY,
AAI.ATTRIBUTE1, AAI.ATTRIBUTE2, AAI.ATTRIBUTE3
INTO L_ID, L_ASSIGNMENT_ID, L_ROTATION_PLAN_ID,
L_EARNING_POLICY_ID, L_SHIFT_DIFFERENTIAL_POLICY_ID,
L_HOUR_DEDUCTION_POLICY_ID, L_ATTRIBUTE1, L_ATTRIBUTE2, L_ATTRIBUTE3
FROM HXT_ADD_ASSIGN_INFO_F AAI
WHERE 1 = 1
AND :NEW.DATE_FROM BETWEEN AAI.EFFECTIVE_START_DATE
AND AAI.EFFECTIVE_END_DATE
AND AAI.ASSIGNMENT_ID =
(SELECT PAAF.ASSIGNMENT_ID
FROM PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAAF.PERSON_ID = :NEW.PERSON_ID
AND :NEW.DATE_FROM BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAAF.PRIMARY_FLAG = 'Y');
BEGIN
IFL_UPD_HXT_ADD_ASSIGN_INFO_F(P_ID => L_ID,
P_DATETRACK_MODE => 'UPDATE_OVERRIDE',
P_EFFECTIVE_START_DATE => :NEW.DATE_FROM,
P_EFFECTIVE_END_DATE => :NEW.DATE_TO,
P_ASSIGNMENT_ID => L_ASSIGNMENT_ID,
P_AUTOGEN_HOURS_YN => 'Y',
P_ROTATION_PLAN => L_ROTATION_PLAN_ID,
P_EARNING_POLICY => L_EARNING_POLICY_ID,
P_SHIFT_DIFFERENTIAL_POLICY => L_SHIFT_DIFFERENTIAL_POLICY_ID,
P_HOUR_DEDUCTION_POLICY => L_HOUR_DEDUCTION_POLICY_ID,
P_ATTRIBUTE1 => L_ATTRIBUTE1,
P_ATTRIBUTE2 => L_ATTRIBUTE2,
P_ATTRIBUTE3 => L_ATTRIBUTE3,
P_CREATED_BY => FND_PROFILE.VALUE('USER_ID'),
P_CREATION_DATE => SYSDATE,
P_LAST_UPDATED_BY => FND_PROFILE.VALUE('USER_ID'),
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATE_LOGIN => FND_PROFILE.VALUE('LOGIN_ID')
);
END;
END IF;
END;
/
-------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE APPS.IFL_UPD_HXT_ADD_ASSIGN_INFO_F (
P_ID NUMBER,
P_DATETRACK_MODE VARCHAR2,
P_EFFECTIVE_START_DATE DATE,
P_EFFECTIVE_END_DATE DATE,
P_ASSIGNMENT_ID NUMBER,
P_AUTOGEN_HOURS_YN VARCHAR2,
P_ROTATION_PLAN NUMBER,
P_EARNING_POLICY NUMBER,
P_SHIFT_DIFFERENTIAL_POLICY NUMBER,
P_HOUR_DEDUCTION_POLICY NUMBER,
P_ATTRIBUTE1 VARCHAR2,
P_ATTRIBUTE2 VARCHAR2,
P_ATTRIBUTE3 VARCHAR2,
P_CREATED_BY NUMBER,
P_CREATION_DATE DATE,
P_LAST_UPDATED_BY NUMBER,
P_LAST_UPDATE_DATE DATE,
P_LAST_UPDATE_LOGIN NUMBER
)
IS
-- ARGUMENTS
-- P_ID -ID OF THE RECORD.
-- P_DATETRACK_MODE -THE MODE IN WHICH THE API HAS TO BE RUN.
-- -IT CAN BE RUN IN 'CORRECTION' OR
-- -'UPDATE_OVERRIDE' MODE.
-- P_EFFECTIVE_START_DATE -THE RECORD WILL BE UPDATE DAS OF THIS DATE.
-- P_EFFECTIVE_START_DATE -EFFECTIVE_START_DATE OF THE RECORD FOR THIS
-- -ASSIGNMENT THAT HAS EFFECTIVE_END_DATE AS
-- -END_OF_TIME.
-- P_ASSIGNMENT_ID -ASSIGNMENT_ID FOR WHICH THE UPDATE HAS TO BE
-- -DONE.
-- P_AUTOGEN_HOURS_YN -THE VALUE FOR THIS ARGUMENT CAN BE EITHER
-- -'Y' OR 'N',TO SPECIFY WHETHER TO AUTOGEN
-- -THE HOURS FOR THIS ASSIGNMENT.
--P_ROTATION_PLAN -ROTATION_PLAN_ID FOR THIS ASSIGNMENT.
--P_EARNING_POLICY -EARNING_POLICY_ID FOR THIS ASSIGNMENT.
--P_SHIFT_DIFFERENTIAL_POLICY -SHIFT_DIFFERENTIAL_POLICY_ID FOR THIS
-- -ASSIGNMENT.
--P_HOUR_DEDUCTION_POLICY -HOUR_DEDUCTION_POLICY_ID FOR THIS ASSIGNMENT.
CURSOR C1
IS
SELECT ROWID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
FROM HXT_ADD_ASSIGN_INFO_F
WHERE ID = P_ID
AND EFFECTIVE_START_DATE = (SELECT MAX (EFFECTIVE_START_DATE)
FROM HXT_ADD_ASSIGN_INFO_F
WHERE ID = P_ID);
CURSOR GET_FUTURE_RECORDS
IS
SELECT ROWIDTOCHAR (ROWID), ID
FROM HXT_ADD_ASSIGN_INFO_F
WHERE ID = P_ID
AND EFFECTIVE_START_DATE > P_EFFECTIVE_START_DATE;
L_EFFECTIVE_START_DATE DATE;
L_EFFECTIVE_END_DATE DATE;
C1_ROWID VARCHAR2 (30);
L_NEW_EFF_ST_DATE DATE;
L_NEW_ROWID VARCHAR2 (30);
TYPE VARCHARTABLE IS TABLE OF VARCHAR2 (30);
TYPE NUMTABLE IS TABLE OF NUMBER;
IDTAB NUMTABLE;
ROWIDTAB VARCHARTABLE;
BEGIN
OPEN C1;
FETCH C1 INTO C1_ROWID, L_EFFECTIVE_START_DATE, L_EFFECTIVE_END_DATE;
CLOSE C1;
IF C1_ROWID IS NOT NULL
THEN
/* END DATE THE EXISTING RECORD AND THEN CREATE A NEW RECORD IF AN
EXISTING RECORD FOUND */
OPEN GET_FUTURE_RECORDS;
FETCH GET_FUTURE_RECORDS BULK COLLECT INTO ROWIDTAB, IDTAB;
CLOSE GET_FUTURE_RECORDS;
IF (IDTAB.COUNT > 0)
THEN
FORALL I IN ROWIDTAB.FIRST .. ROWIDTAB.LAST
DELETE FROM HXT_ADD_ASSIGN_INFO_F
WHERE ROWID = CHARTOROWID (ROWIDTAB (I));
UPDATE HXT_ADD_ASSIGN_INFO_F AAI
SET AAI.EFFECTIVE_END_DATE = P_EFFECTIVE_START_DATE - 1
WHERE AAI.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND P_EFFECTIVE_START_DATE BETWEEN AAI.EFFECTIVE_START_DATE
AND AAI.EFFECTIVE_END_DATE;
ELSE
UPDATE HXT_ADD_ASSIGN_INFO_F
SET EFFECTIVE_END_DATE = P_EFFECTIVE_START_DATE - 1
WHERE ROWID = C1_ROWID;
END IF;
INSERT INTO HXT_ADD_ASSIGN_INFO_F
(ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ASSIGNMENT_ID,
AUTOGEN_HOURS_YN,
ROTATION_PLAN,
EARNING_POLICY,
SHIFT_DIFFERENTIAL_POLICY,
HOUR_DEDUCTION_POLICY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (P_ID,
P_EFFECTIVE_START_DATE,
P_EFFECTIVE_END_DATE,--HR_GENERAL.END_OF_TIME,
P_ASSIGNMENT_ID,
P_AUTOGEN_HOURS_YN,
P_ROTATION_PLAN,
P_EARNING_POLICY,
P_SHIFT_DIFFERENTIAL_POLICY,
P_HOUR_DEDUCTION_POLICY,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_CREATED_BY,
P_CREATION_DATE,
P_LAST_UPDATED_BY,
P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN
);
INSERT INTO HXT_ADD_ASSIGN_INFO_F
(ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ASSIGNMENT_ID,
AUTOGEN_HOURS_YN,
ROTATION_PLAN,
EARNING_POLICY,
SHIFT_DIFFERENTIAL_POLICY,
HOUR_DEDUCTION_POLICY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (P_ID,
P_EFFECTIVE_END_DATE + 1,
HR_GENERAL.END_OF_TIME,
P_ASSIGNMENT_ID,
'N',
P_ROTATION_PLAN,
P_EARNING_POLICY,
P_SHIFT_DIFFERENTIAL_POLICY,
P_HOUR_DEDUCTION_POLICY,
P_ATTRIBUTE1,
P_ATTRIBUTE2,
P_ATTRIBUTE3,
P_CREATED_BY,
P_CREATION_DATE,
P_LAST_UPDATED_BY,
P_LAST_UPDATE_DATE,
P_LAST_UPDATE_LOGIN
);
END IF;
END;
/
No comments:
Post a Comment