[Home] [Help]
PACKAGE BODY: APPS.BEN_IRC_UTIL
Source
1 package body ben_irc_util as
2 /* $Header: beircutl.pkb 120.4 2008/02/18 08:06:32 rtagarra noship $ */
3 --
4 g_package varchar2(80) := 'ben_irc_util';
5
6 /**
7 pay_proposal_rec_change function compares the two structures.
8 returns true if there is any change
9 returns false, otherwise
10 **/
11 function pay_proposal_rec_change(p_pay_proposal_rec_old in per_pay_proposals%ROWTYPE,
12 p_pay_proposal_rec_new in per_pay_proposals%ROWTYPE) return boolean is
13 begin
14 -- dbms_output.put_line('Entering pay');
15
16 if
17 nvl(p_pay_proposal_rec_old.PAY_PROPOSAL_ID ,hr_api.g_number ) <> nvl(p_pay_proposal_rec_new.PAY_PROPOSAL_ID,hr_api.g_number )
18 OR nvl(p_pay_proposal_rec_old.PROPOSED_SALARY_N ,hr_api.g_number ) <> nvl(p_pay_proposal_rec_new.PROPOSED_SALARY_N ,hr_api.g_number)
19 OR nvl(p_pay_proposal_rec_old.ASSIGNMENT_ID ,hr_api.g_number ) <> nvl(p_pay_proposal_rec_new.ASSIGNMENT_ID,hr_api.g_number )
20 OR nvl(p_pay_proposal_rec_old.EVENT_ID,hr_api.g_number ) <> nvl(p_pay_proposal_rec_new.EVENT_ID ,hr_api.g_number )
21 OR nvl(p_pay_proposal_rec_old.BUSINESS_GROUP_ID ,hr_api.g_number ) <> nvl(p_pay_proposal_rec_new.BUSINESS_GROUP_ID,hr_api.g_number )
22 OR nvl(p_pay_proposal_rec_old.FORCED_RANKING,hr_api.g_number ) <> nvl(p_pay_proposal_rec_new.FORCED_RANKING,hr_api.g_number )
23 OR nvl(p_pay_proposal_rec_old.PERFORMANCE_REVIEW_ID,hr_api.g_number ) <> nvl(p_pay_proposal_rec_new.PERFORMANCE_REVIEW_ID,hr_api.g_number )
24 OR nvl(p_pay_proposal_rec_old.APPROVED,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.APPROVED,hr_api.g_varchar2 )
25 OR nvl(p_pay_proposal_rec_old.MULTIPLE_COMPONENTS,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.MULTIPLE_COMPONENTS,hr_api.g_varchar2 )
26 OR nvl(p_pay_proposal_rec_old.CHANGE_DATE,hr_api.g_date ) <> nvl(p_pay_proposal_rec_new.CHANGE_DATE ,hr_api.g_date )
27 OR nvl(p_pay_proposal_rec_old.LAST_CHANGE_DATE ,hr_api.g_date ) <> nvl(p_pay_proposal_rec_new.LAST_CHANGE_DATE ,hr_api.g_date )
28 OR nvl(p_pay_proposal_rec_old.NEXT_PERF_REVIEW_DATE ,hr_api.g_date ) <> nvl(p_pay_proposal_rec_new.NEXT_PERF_REVIEW_DATE,hr_api.g_date )
29 OR nvl(p_pay_proposal_rec_old.NEXT_SAL_REVIEW_DATE,hr_api.g_date ) <> nvl(p_pay_proposal_rec_new.NEXT_SAL_REVIEW_DATE,hr_api.g_date )
30 OR nvl(p_pay_proposal_rec_old.PERFORMANCE_RATING,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.PERFORMANCE_RATING,hr_api.g_varchar2 )
31 OR nvl(p_pay_proposal_rec_old.PROPOSAL_REASON,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.PROPOSAL_REASON,hr_api.g_varchar2 )
32 OR nvl(p_pay_proposal_rec_old.PROPOSED_SALARY ,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.PROPOSED_SALARY,hr_api.g_varchar2 )
33 OR nvl(p_pay_proposal_rec_old.REVIEW_DATE ,hr_api.g_date ) <> nvl(p_pay_proposal_rec_new.REVIEW_DATE,hr_api.g_date )
34 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE_CATEGORY,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE_CATEGORY,hr_api.g_varchar2 )
35 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE1,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE1,hr_api.g_varchar2 )
36 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE2 ,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE2,hr_api.g_varchar2 )
37 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE3 ,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE3,hr_api.g_varchar2 )
38 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE4,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE4,hr_api.g_varchar2 )
39 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE5 ,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE5,hr_api.g_varchar2 )
40 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE6,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE6 ,hr_api.g_varchar2 )
41 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE7 ,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE7,hr_api.g_varchar2 )
42 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE8 ,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE8,hr_api.g_varchar2 )
43 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE9 ,hr_api.g_varchar2 ) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE9,hr_api.g_varchar2 )
44 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE10 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE10 ,hr_api.g_varchar2)
45 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE11 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE11 ,hr_api.g_varchar2)
46 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE12 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE12 ,hr_api.g_varchar2)
47 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE13 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE13 ,hr_api.g_varchar2)
48 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE14 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE14 ,hr_api.g_varchar2)
49 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE15 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE15 ,hr_api.g_varchar2)
50 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE16 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE16 ,hr_api.g_varchar2)
51 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE17 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE17 ,hr_api.g_varchar2)
52 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE18 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE18 ,hr_api.g_varchar2)
53 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE19 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE19 ,hr_api.g_varchar2)
54 OR nvl(p_pay_proposal_rec_old.ATTRIBUTE20 ,hr_api.g_varchar2) <> nvl(p_pay_proposal_rec_new.ATTRIBUTE20 ,hr_api.g_varchar2)
55 OR nvl(p_pay_proposal_rec_old.OBJECT_VERSION_NUMBER ,hr_api.g_number) <> nvl(p_pay_proposal_rec_new.OBJECT_VERSION_NUMBER,hr_api.g_number )
56 then
57 return true;
58 else
59 return false;
60 end if;
61 end pay_proposal_rec_change;
62
63 /**
64 offer_assignment_rec_change function compares the two structures.
65 returns true if there is any change
66 returns false, otherwise
67 **/
68 function offer_assignment_rec_change(p_offer_assignment_rec_old in per_all_assignments_f%rowtype,
69 p_offer_assignment_rec_new in per_all_assignments_f%rowtype)
70 return boolean is
71
72 begin
73 --dbms_output.put_line('Entering offer');
74
75 if
76 nvl(p_offer_assignment_rec_old.RECRUITER_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.RECRUITER_ID,hr_api.g_number)
77 OR nvl(p_offer_assignment_rec_old.GRADE_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.GRADE_ID,hr_api.g_number)
78 OR nvl(p_offer_assignment_rec_old.POSITION_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.POSITION_ID,hr_api.g_number)
79 OR nvl(p_offer_assignment_rec_old.JOB_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.JOB_ID,hr_api.g_number)
80 OR nvl(p_offer_assignment_rec_old.ASSIGNMENT_STATUS_TYPE_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.ASSIGNMENT_STATUS_TYPE_ID,hr_api.g_number)
81 OR nvl(p_offer_assignment_rec_old.PAYROLL_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PAYROLL_ID,hr_api.g_number)
82 OR nvl(p_offer_assignment_rec_old.LOCATION_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.LOCATION_ID,hr_api.g_number)
83 OR nvl(p_offer_assignment_rec_old.PERSON_REFERRED_BY_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PERSON_REFERRED_BY_ID,hr_api.g_number)
84 OR nvl(p_offer_assignment_rec_old.SUPERVISOR_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SUPERVISOR_ID,hr_api.g_number)
85 OR nvl(p_offer_assignment_rec_old.SPECIAL_CEILING_STEP_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SPECIAL_CEILING_STEP_ID,hr_api.g_number)
86 OR nvl(p_offer_assignment_rec_old.PERSON_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PERSON_ID,hr_api.g_number)
87 OR nvl(p_offer_assignment_rec_old.RECRUITMENT_ACTIVITY_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.RECRUITMENT_ACTIVITY_ID,hr_api.g_number)
88 OR nvl(p_offer_assignment_rec_old.SOURCE_ORGANIZATION_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SOURCE_ORGANIZATION_ID,hr_api.g_number)
89 OR nvl(p_offer_assignment_rec_old.ORGANIZATION_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.ORGANIZATION_ID,hr_api.g_number)
90 OR nvl(p_offer_assignment_rec_old.PEOPLE_GROUP_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PEOPLE_GROUP_ID,hr_api.g_number)
91 OR nvl(p_offer_assignment_rec_old.SOFT_CODING_KEYFLEX_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SOFT_CODING_KEYFLEX_ID,hr_api.g_number)
92 OR nvl(p_offer_assignment_rec_old.VACANCY_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.VACANCY_ID,hr_api.g_number)
93 OR nvl(p_offer_assignment_rec_old.PAY_BASIS_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PAY_BASIS_ID,hr_api.g_number)
94 OR nvl(p_offer_assignment_rec_old.ASSIGNMENT_SEQUENCE,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.ASSIGNMENT_SEQUENCE,hr_api.g_number)
95 OR nvl(p_offer_assignment_rec_old.APPLICATION_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.APPLICATION_ID,hr_api.g_number)
96 OR nvl(p_offer_assignment_rec_old.ASSIGNMENT_NUMBER ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASSIGNMENT_NUMBER ,hr_api.g_varchar2)
97 OR nvl(p_offer_assignment_rec_old.CHANGE_REASON ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.CHANGE_REASON ,hr_api.g_varchar2)
98 OR nvl(p_offer_assignment_rec_old.COMMENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.COMMENT_ID,hr_api.g_number)
99 OR nvl(p_offer_assignment_rec_old.DATE_PROBATION_END,hr_api.g_date) <> nvl(p_offer_assignment_rec_new.DATE_PROBATION_END,hr_api.g_date)
100 OR nvl(p_offer_assignment_rec_old.DEFAULT_CODE_COMB_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.DEFAULT_CODE_COMB_ID,hr_api.g_number)
101 OR nvl(p_offer_assignment_rec_old.EMPLOYMENT_CATEGORY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.EMPLOYMENT_CATEGORY,hr_api.g_varchar2)
102 OR nvl(p_offer_assignment_rec_old.FREQUENCY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.FREQUENCY ,hr_api.g_varchar2)
103 OR nvl(p_offer_assignment_rec_old.INTERNAL_ADDRESS_LINE,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.INTERNAL_ADDRESS_LINE,hr_api.g_varchar2)
104 OR nvl(p_offer_assignment_rec_old.MANAGER_FLAG,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.MANAGER_FLAG,hr_api.g_varchar2)
105 OR nvl(p_offer_assignment_rec_old.NORMAL_HOURS ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.NORMAL_HOURS ,hr_api.g_number)
106 OR nvl(p_offer_assignment_rec_old.PERF_REVIEW_PERIOD ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PERF_REVIEW_PERIOD ,hr_api.g_number)
107 OR nvl(p_offer_assignment_rec_old.PERF_REVIEW_PERIOD_FREQUENCY,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.PERF_REVIEW_PERIOD_FREQUENCY ,hr_api.g_varchar2)
108 OR nvl(p_offer_assignment_rec_old.PERIOD_OF_SERVICE_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PERIOD_OF_SERVICE_ID,hr_api.g_number)
109 OR nvl(p_offer_assignment_rec_old.PROBATION_PERIOD,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PROBATION_PERIOD,hr_api.g_number)
110 OR nvl(p_offer_assignment_rec_old.PROBATION_UNIT ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.PROBATION_UNIT,hr_api.g_varchar2)
111 OR nvl(p_offer_assignment_rec_old.SAL_REVIEW_PERIOD ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SAL_REVIEW_PERIOD,hr_api.g_number)
112 OR nvl(p_offer_assignment_rec_old.SAL_REVIEW_PERIOD_FREQUENCY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.SAL_REVIEW_PERIOD_FREQUENCY ,hr_api.g_varchar2)
113 OR nvl(p_offer_assignment_rec_old.SET_OF_BOOKS_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SET_OF_BOOKS_ID,hr_api.g_number)
114 OR nvl(p_offer_assignment_rec_old.SOURCE_TYPE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.SOURCE_TYPE,hr_api.g_varchar2)
115 OR nvl(p_offer_assignment_rec_old.TIME_NORMAL_FINISH,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.TIME_NORMAL_FINISH ,hr_api.g_varchar2)
116 OR nvl(p_offer_assignment_rec_old.TIME_NORMAL_START ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.TIME_NORMAL_START,hr_api.g_varchar2)
117 OR nvl(p_offer_assignment_rec_old.REQUEST_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.REQUEST_ID,hr_api.g_number)
118 OR nvl(p_offer_assignment_rec_old.PROGRAM_APPLICATION_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PROGRAM_APPLICATION_ID,hr_api.g_number)
119 OR nvl(p_offer_assignment_rec_old.PROGRAM_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PROGRAM_ID,hr_api.g_number)
120 OR nvl(p_offer_assignment_rec_old.PROGRAM_UPDATE_DATE,hr_api.g_date) <> nvl(p_offer_assignment_rec_new.PROGRAM_UPDATE_DATE,hr_api.g_date)
121 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE_CATEGORY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE_CATEGORY ,hr_api.g_varchar2)
122 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE1 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE1,hr_api.g_varchar2)
123 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE2 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE2 ,hr_api.g_varchar2)
124 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE3 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE3 ,hr_api.g_varchar2)
125 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE4 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE4 ,hr_api.g_varchar2)
126 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE5 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE5 ,hr_api.g_varchar2)
127 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE6 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE6,hr_api.g_varchar2)
128 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE7 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE7 ,hr_api.g_varchar2)
129 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE8 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE8 ,hr_api.g_varchar2)
130 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE9 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE9 ,hr_api.g_varchar2)
131 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE10,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE10,hr_api.g_varchar2)
132 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE11 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE11,hr_api.g_varchar2)
133 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE12 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE12 ,hr_api.g_varchar2)
134 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE13 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE13 ,hr_api.g_varchar2)
135 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE14 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE14 ,hr_api.g_varchar2)
136 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE15 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE15 ,hr_api.g_varchar2)
137 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE16 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE16 ,hr_api.g_varchar2)
138 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE17 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE17 ,hr_api.g_varchar2)
139 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE18 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE18 ,hr_api.g_varchar2)
140 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE19 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE19 ,hr_api.g_varchar2)
141 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE20 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE20 ,hr_api.g_varchar2)
142 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE21 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE21 ,hr_api.g_varchar2)
143 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE22 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE22 ,hr_api.g_varchar2)
144 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE23 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE23 ,hr_api.g_varchar2)
145 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE24 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE24 ,hr_api.g_varchar2)
146 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE25 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE25 ,hr_api.g_varchar2)
147 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE26 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE26 ,hr_api.g_varchar2)
148 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE27 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE27 ,hr_api.g_varchar2)
149 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE28 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE28 ,hr_api.g_varchar2)
150 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE29 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE29 ,hr_api.g_varchar2)
151 OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE30 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE30 ,hr_api.g_varchar2)
152 OR nvl(p_offer_assignment_rec_old.TITLE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.TITLE ,hr_api.g_varchar2)
153 OR nvl(p_offer_assignment_rec_old.OBJECT_VERSION_NUMBER ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.OBJECT_VERSION_NUMBER,hr_api.g_number)
154 OR nvl(p_offer_assignment_rec_old.BARGAINING_UNIT_CODE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.BARGAINING_UNIT_CODE ,hr_api.g_varchar2)
155 OR nvl(p_offer_assignment_rec_old.LABOUR_UNION_MEMBER_FLAG,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.LABOUR_UNION_MEMBER_FLAG ,hr_api.g_varchar2)
156 OR nvl(p_offer_assignment_rec_old.HOURLY_SALARIED_CODE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.HOURLY_SALARIED_CODE ,hr_api.g_varchar2)
157 OR nvl(p_offer_assignment_rec_old.CONTRACT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.CONTRACT_ID,hr_api.g_number)
158 OR nvl(p_offer_assignment_rec_old.COLLECTIVE_AGREEMENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.COLLECTIVE_AGREEMENT_ID,hr_api.g_number)
159 OR nvl(p_offer_assignment_rec_old.CAGR_ID_FLEX_NUM ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.CAGR_ID_FLEX_NUM ,hr_api.g_number)
160 OR nvl(p_offer_assignment_rec_old.CAGR_GRADE_DEF_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.CAGR_GRADE_DEF_ID,hr_api.g_number)
161 OR nvl(p_offer_assignment_rec_old.ESTABLISHMENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.ESTABLISHMENT_ID,hr_api.g_number)
162 OR nvl(p_offer_assignment_rec_old.NOTICE_PERIOD ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.NOTICE_PERIOD ,hr_api.g_number)
163 OR nvl(p_offer_assignment_rec_old.NOTICE_PERIOD_UOM ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.NOTICE_PERIOD_UOM ,hr_api.g_varchar2)
164 OR nvl(p_offer_assignment_rec_old.EMPLOYEE_CATEGORY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.EMPLOYEE_CATEGORY ,hr_api.g_varchar2)
165 OR nvl(p_offer_assignment_rec_old.WORK_AT_HOME ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.WORK_AT_HOME ,hr_api.g_varchar2)
166 OR nvl(p_offer_assignment_rec_old.JOB_POST_SOURCE_NAME ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.JOB_POST_SOURCE_NAME ,hr_api.g_varchar2)
167 OR nvl(p_offer_assignment_rec_old.POSTING_CONTENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.POSTING_CONTENT_ID,hr_api.g_number)
168 OR nvl(p_offer_assignment_rec_old.PERIOD_OF_PLACEMENT_DATE_START,hr_api.g_date) <> nvl(p_offer_assignment_rec_new.PERIOD_OF_PLACEMENT_DATE_START,hr_api.g_date)
169 OR nvl(p_offer_assignment_rec_old.VENDOR_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.VENDOR_ID,hr_api.g_number)
170 OR nvl(p_offer_assignment_rec_old.VENDOR_EMPLOYEE_NUMBER ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.VENDOR_EMPLOYEE_NUMBER ,hr_api.g_varchar2)
171 OR nvl(p_offer_assignment_rec_old.VENDOR_ASSIGNMENT_NUMBER ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.VENDOR_ASSIGNMENT_NUMBER ,hr_api.g_varchar2)
172 OR nvl(p_offer_assignment_rec_old.ASSIGNMENT_CATEGORY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASSIGNMENT_CATEGORY ,hr_api.g_varchar2)
173 OR nvl(p_offer_assignment_rec_old.PROJECT_TITLE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.PROJECT_TITLE ,hr_api.g_varchar2)
174 OR nvl(p_offer_assignment_rec_old.APPLICANT_RANK ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.APPLICANT_RANK ,hr_api.g_number)
175 OR nvl(p_offer_assignment_rec_old.GRADE_LADDER_PGM_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.GRADE_LADDER_PGM_ID,hr_api.g_number)
176 OR nvl(p_offer_assignment_rec_old.SUPERVISOR_ASSIGNMENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SUPERVISOR_ASSIGNMENT_ID,hr_api.g_number)
177 OR nvl(p_offer_assignment_rec_old.VENDOR_SITE_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.VENDOR_SITE_ID,hr_api.g_number)
178 OR nvl(p_offer_assignment_rec_old.PO_HEADER_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PO_HEADER_ID,hr_api.g_number)
179 OR nvl(p_offer_assignment_rec_old.PO_LINE_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PO_LINE_ID,hr_api.g_number)
180 OR nvl(p_offer_assignment_rec_old.PROJECTED_ASSIGNMENT_END,hr_api.g_date) <> nvl(p_offer_assignment_rec_new.PROJECTED_ASSIGNMENT_END,hr_api.g_date)
181 OR nvl(p_offer_assignment_rec_old.BUSINESS_GROUP_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.BUSINESS_GROUP_ID,hr_api.g_number)
182
183 then
184 return true;
185 else
186 return false;
187 end if;
188 end offer_assignment_rec_change;
189
190 /**
191 is_benmngle_for_irec_reqd function chks whether benmngle should re-run for irec.
192 returns Y if re-run has to happen
193 returns N, otherwise
194 **/
195 function is_benmngle_for_irec_reqd( p_person_id in number,
196 p_assignment_id in number,
197 p_business_group_id in number,
198 p_effective_date in date,
199 p_pay_proposal_rec_old in per_pay_proposals%ROWTYPE,
200 p_pay_proposal_rec_new in per_pay_proposals%ROWTYPE,
201 p_offer_assignment_rec_old in per_all_assignments_f%rowtype,
202 p_offer_assignment_rec_new in per_all_assignments_f%rowtype
203 ) return varchar2 is
204
205 --
206 l_proc varchar2(80) := 'benutils.run_irc_benmngle_flag';
207 l_per_last_upd_date date;
208 l_pil_last_upd_date date;
209
210 l_run_benmngle varchar2(30) := 'N';
211
212 cursor c_per_last_upd_date(p_pil_last_upd_date date) is
213 select max(last_update_date)
214 from (select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
215 from per_addresses
216 where person_id = p_person_id
217 and business_group_id = p_business_group_id
218 union
219 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
220 from per_all_assignments_f
221 where person_id = p_person_id
222 and business_group_id = p_business_group_id
223 union
224 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
225 from per_all_people_f
226 where person_id = p_person_id
227 and business_group_id = p_business_group_id
228 union
229 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
230 from per_contact_relationships
231 where person_id = p_person_id
232 and business_group_id = p_business_group_id
233 union
234 select max(nvl(psl.last_update_date,p_pil_last_upd_date)) last_update_date
235 from per_pay_proposals psl, per_all_assignments_f asn
236 where psl.assignment_id = asn.assignment_id
237 and asn.person_id = p_person_id
238 and asn.business_group_id = p_business_group_id
239 union
240 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
241 from per_periods_of_service
242 where person_id = p_person_id
243 and business_group_id = p_business_group_id
244 union
245 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
246 from per_qualifications
247 where person_id = p_person_id
248 and business_group_id = p_business_group_id
249 union
250 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
251 from per_absence_attendances
252 where person_id = p_person_id
253 and business_group_id = p_business_group_id
254 union
255 select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
256 from per_person_type_usages_f
257 where person_id = p_person_id
258 );
259
260
261 cursor c_pil_last_upd_date is
262 select pil.last_update_date last_update_date
263 from ben_per_in_ler pil , ben_ler_f ler
264 where pil.person_id = p_person_id
265 and pil.business_group_id = p_business_group_id
266 and pil.assignment_id = p_assignment_id
267 and pil.per_in_ler_stat_cd = 'STRTD'
268 and ler.ler_id = pil.ler_id
269 and ler.typ_cd = 'IREC'
270 and p_effective_date between ler.effective_start_date and ler.effective_end_date;
271
272 --
273 begin
274 --
275 hr_utility.set_location('Entering:'||l_proc, 5);
276 -- dbms_output.put_line('Entering');
277 --
278 -- Get the last updated date from pil record for IREC LE run
279 --
280 open c_pil_last_upd_date;
281 fetch c_pil_last_upd_date into l_pil_last_upd_date;
282 if c_pil_last_upd_date%NOTFOUND then
283 --
284 -- If IREC life event was never run yet, we need to run it for first time
285 --
286 l_run_benmngle := 'Y' ;
287 return l_run_benmngle;
288
289 elsif l_pil_last_upd_date is not null
290 then
291 -- means irec has already been run , Then chk for following:
292 -- 1. if per_pay_proposals structure for Offer has chnged
293 -- 2. if per_all_assignment structure for Offer has chnged
294 -- 3. if any HR info abt the person has chnged.
295 --dbms_output.put_line('IREC for more than one time');
296 if pay_proposal_rec_change(p_pay_proposal_rec_old,p_pay_proposal_rec_new) then
297 l_run_benmngle := 'Y' ;
298 return l_run_benmngle;
299
300 elsif offer_assignment_rec_change(p_offer_assignment_rec_old,p_offer_assignment_rec_new) then
301 l_run_benmngle := 'Y' ;
302 return l_run_benmngle;
303 else
304 --
305 --dbms_output.put_line('No change in record');
306 open c_per_last_upd_date(l_pil_last_upd_date);
307 fetch c_per_last_upd_date into l_per_last_upd_date;
308 close c_per_last_upd_date;
309 --
310 hr_utility.set_location('l_per_last_upd_date is '||l_per_last_upd_date, 999);
311 hr_utility.set_location('l_pil_last_upd_date is '||l_pil_last_upd_date, 999);
312 --
313 if (nvl(l_per_last_upd_date,l_pil_last_upd_date) > l_pil_last_upd_date) then
314 l_run_benmngle := 'Y' ;
315 return l_run_benmngle;
316 end if;
317 end if; --pay_proposal_rec_change
318
319 end if; --c_pil_last_upd_date%NOTFOUND
320
321 return l_run_benmngle; -- returning 'N'
322
323 end is_benmngle_for_irec_reqd;
324 --
325 /*** Updating the present electable choices with approval staus code .
326 1.Once we get new set of electable choices,look back at the last electables voided, if any.
327 2.find out which electable choices were Approved.
328 3. Find out if person is still eligible for last approved electable choices.
329 1.If yes , mark the new ones as 'Approved'
330 2. Otherwise do nothing.
331 4. Find out if there are any 'enter value at enrollment' rates corresponding to Last EPE Approved .
332 1. find out if there are any 'enter value at enrollment' rates for present electable choices.
333 1. If yes, update present rate record with old rate record.
334 2. Otherwise do nothing.
335 ***/
336 PROCEDURE post_irec_process_update (
337 p_person_id IN NUMBER,
338 p_business_group_id IN NUMBER,
339 p_assignment_id IN NUMBER,
340 p_effective_date IN DATE
341 )
342 IS
343 -- get the latest pil which has been voided.
344 CURSOR c_last_pil
345 IS
346 SELECT per_in_ler_id
347 FROM ben_per_in_ler pil, ben_ler_f ler
348 WHERE pil.person_id = p_person_id
349 AND pil.business_group_id = p_business_group_id
350 AND pil.assignment_id = p_assignment_id
351 AND pil.per_in_ler_stat_cd = 'BCKDT' -- 5068367
352 AND ler.ler_id = pil.ler_id
353 AND ler.typ_cd = 'IREC'
354 AND p_effective_date BETWEEN ler.effective_start_date
355 AND ler.effective_end_date
356 ORDER BY pil.last_update_date DESC;
357
358 l_last_pil NUMBER;
359
360 -- get present pil which is in started state
361 CURSOR c_present_pil
362 IS
363 SELECT per_in_ler_id
364 FROM ben_per_in_ler pil, ben_ler_f ler
365 WHERE pil.person_id = p_person_id
366 AND pil.business_group_id = p_business_group_id
367 AND pil.assignment_id = p_assignment_id
368 AND pil.per_in_ler_stat_cd = 'STRTD'
369 AND ler.ler_id = pil.ler_id
370 AND ler.typ_cd = 'IREC'
371 AND p_effective_date BETWEEN ler.effective_start_date
372 AND ler.effective_end_date;
373
374 l_present_pil NUMBER;
375
376 -- Get the all old epe's which is approved and has common eligible comp objects
377 --- between old and new epe.
378 CURSOR c_last_epe (p_past_pil NUMBER, p_present_pil NUMBER)
379 IS
380 SELECT oipl_id, pl_id, elig_per_elctbl_chc_id,comments
381 FROM ben_elig_per_elctbl_chc past_epe
382 WHERE past_epe.per_in_ler_id = p_past_pil
383 AND past_epe.business_group_id = p_business_group_id
384 AND past_epe.elctbl_flag = 'Y'
385 AND past_epe.approval_status_cd = 'IRC_BEN_A'
386 AND EXISTS (
387 SELECT NULL
388 FROM ben_elig_per_elctbl_chc present_epe
389 WHERE present_epe.per_in_ler_id = p_present_pil
390 AND present_epe.elctbl_flag = 'Y'
391 AND present_epe.elig_flag = 'Y'
392 AND present_epe.pl_id = past_epe.pl_id
393 AND NVL (present_epe.oipl_id, present_epe.pl_id) =
394 NVL (past_epe.oipl_id, past_epe.pl_id));
395
396 -- get the new epe id for commomn comp object
397 CURSOR c_present_epe (p_present_pil NUMBER, p_pl_id NUMBER, p_oipl_id NUMBER)
398 IS
399 SELECT elig_per_elctbl_chc_id,object_version_number
400 FROM ben_elig_per_elctbl_chc present_pil
401 WHERE present_pil.per_in_ler_id = p_present_pil
402 AND present_pil.elctbl_flag = 'Y'
403 AND present_pil.elig_flag = 'Y'
404 AND present_pil.pl_id = p_pl_id
405 AND NVL (present_pil.oipl_id, present_pil.pl_id) =
406 NVL (p_oipl_id, p_pl_id)
407 AND present_pil.business_group_id = p_business_group_id;
408
409 l_present_epe_id NUMBER;
410 l_present_epe_ovn NUMBER;
411 l_comments ben_elig_per_elctbl_chc.comments%TYPE;
412
413 -- Get the ecr correspoding to past epe
414 CURSOR c_past_ecr (p_past_epe NUMBER)
415 IS
416 SELECT enrt_rt_id, val, cmcd_val, ann_val
417 FROM ben_enrt_rt
418 WHERE elig_per_elctbl_chc_id = p_past_epe
419 AND entr_val_at_enrt_flag = 'Y'
420 AND p_business_group_id = p_business_group_id;
421
422 l_past_ecr c_past_ecr%ROWTYPE;
423
424 -- Get the ecr correspoding to present epe
425 CURSOR c_present_ecr (p_present_epe NUMBER)
426 IS
427 SELECT enrt_rt_id, mx_elcn_val max_val, mn_elcn_val min_val
428 FROM ben_enrt_rt
429 WHERE elig_per_elctbl_chc_id = p_present_epe
430 AND entr_val_at_enrt_flag = 'Y'
431 AND p_business_group_id = p_business_group_id;
432
433 l_present_ecr c_present_ecr%ROWTYPE;
434 --
435 l_object_version_number NUMBER;
436 --
437 BEGIN
438 OPEN c_last_pil;
439
440 FETCH c_last_pil INTO l_last_pil;
441
442 CLOSE c_last_pil;
443 -- if there was any last run of benmngle.
444 IF l_last_pil IS NOT NULL
445 THEN
446 OPEN c_present_pil;
447
448 FETCH c_present_pil INTO l_present_pil;
449
450 CLOSE c_present_pil;
451 -- Get the present epe to be marked corresponding to past epe marked 'approved'
452 FOR l_last_epe IN c_last_epe (l_last_pil, l_present_pil)
453 LOOP
454 --
455 OPEN c_present_epe (l_present_pil,
456 l_last_epe.pl_id,
457 l_last_epe.oipl_id
458 );
459
460 FETCH c_present_epe INTO l_present_epe_id,l_present_epe_ovn;
461
462 CLOSE c_present_epe;
463 ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
464 (p_elig_per_elctbl_chc_id => l_present_epe_id,
465 p_effective_date => p_effective_date,
466 p_object_version_number => l_present_epe_ovn,
467 p_approval_status_cd => 'IRC_BEN_A',
468 p_comments => l_last_epe.comments
469 );
470 -- get the present ecr corresponding to past ecr and update the present ecr with past ecr data.
471 OPEN c_past_ecr (l_last_epe.elig_per_elctbl_chc_id);
472
473 FETCH c_past_ecr INTO l_past_ecr;
474
475 IF c_past_ecr%FOUND
476 THEN
477 OPEN c_present_ecr (l_present_epe_id);
478
479 FETCH c_present_ecr INTO l_present_ecr;
480
481 IF c_present_ecr%FOUND
482 THEN
483 IF l_past_ecr.val BETWEEN l_present_ecr.min_val
484 AND l_present_ecr.max_val
485 THEN
486 ben_enrollment_rate_api.update_enrollment_rate
487 (p_enrt_rt_id => l_present_ecr.enrt_rt_id,
488 p_val => l_past_ecr.val,
489 p_cmcd_val => l_past_ecr.cmcd_val,
490 p_ann_val => l_past_ecr.ann_val,
491 p_effective_date => p_effective_date,
492 p_object_version_number => l_object_version_number
493 );
494 END IF;
495 END IF; --c_present_ecr
496
497 CLOSE c_present_ecr;
498 END IF; --c_past_ecr
499
500 CLOSE c_past_ecr;
501 END LOOP;
502 END IF; -- last_pil not null
503 END post_irec_process_update;
504 --
505
506 /***
507 1.Firstly,this proc VOIDs ( read as DELETE) the pil,ppl,pel
508 corresponding to a Deleted Transaction of IREC
509 2.Secondly, it restores back the latest Backed out pil,pel
510 to STARTED state.
511 ***/
512
513 PROCEDURE void_or_restore_life_event (
514 p_person_id IN NUMBER,
515 p_assignment_id IN NUMBER,
516 p_offer_assignment_id IN NUMBER DEFAULT NULL,
517 p_void_per_in_ler_id IN NUMBER DEFAULT NULL,
518 p_restore_per_in_ler_id IN NUMBER DEFAULT NULL,
519 p_status_cd IN VARCHAR2 DEFAULT NULL,
520 p_effective_date IN DATE
521 )
522 IS
523 CURSOR c_ptnl (c_ptnl_ler_for_per_id IN NUMBER)
524 IS
525 SELECT ptnl.*
526 FROM ben_ptnl_ler_for_per ptnl
527 WHERE ptnl.ptnl_ler_for_per_id = c_ptnl_ler_for_per_id;
528
529 CURSOR c_pil (c_per_in_ler_id IN NUMBER)
530 IS
531 SELECT pil.*
532 FROM ben_per_in_ler pil
533 WHERE pil.per_in_ler_id = c_per_in_ler_id
534 AND pil.assignment_id = p_assignment_id
535 AND pil.person_id = p_person_id;
536
537 CURSOR c_pil_elctbl_chc_popl (p_per_in_ler_id NUMBER)
538 IS
539 SELECT pel.pil_elctbl_chc_popl_id, pel.object_version_number
540 FROM ben_pil_elctbl_chc_popl pel
541 WHERE pel.per_in_ler_id = p_per_in_ler_id;
542
543 CURSOR c_latest_ler
544 IS
545 SELECT pil.per_in_ler_id
546 FROM ben_per_in_ler pil, ben_ler_f ler
547 WHERE pil.person_id = p_person_id
548 AND pil.assignment_id = p_assignment_id
549 AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
550 AND pil.ler_id = ler.ler_id
551 AND ler.typ_cd = 'IREC'
552 AND p_effective_date BETWEEN ler.effective_start_date
553 AND ler.effective_end_date
554 ORDER BY pil.per_in_ler_id DESC;
555
556 CURSOR c_latest_bckdt_ler
557 IS
558 SELECT pil.per_in_ler_id
559 FROM ben_per_in_ler pil, ben_ler_f ler
560 WHERE pil.person_id = p_person_id
561 AND pil.assignment_id = p_assignment_id
562 AND pil.per_in_ler_stat_cd = 'BCKDT'
563 AND pil.ler_id = ler.ler_id
564 AND ler.typ_cd = 'IREC'
565 AND p_effective_date BETWEEN ler.effective_start_date
566 AND ler.effective_end_date
567 ORDER BY pil.per_in_ler_id DESC;
568
569 CURSOR c_pil_strt
570 IS
571 SELECT NULL
572 FROM ben_per_in_ler pil
573 WHERE pil.per_in_ler_id <> p_restore_per_in_ler_id
574 AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
575 AND pil.assignment_id = p_assignment_id
576 AND pil.person_id = p_person_id;
577
578 CURSOR c_get_pil
579 IS
580 SELECT *
581 FROM ben_per_in_ler
582 WHERE assignment_id = p_assignment_id
583 AND per_in_ler_stat_cd NOT IN ('VOIDD', 'PROCD')
584 AND per_in_ler_id >
585 (SELECT NVL (MAX (per_in_ler_id), -1)
586 FROM ben_pil_assignment
587 WHERE offer_assignment_id IS NOT NULL
588 AND applicant_assignment_id = p_assignment_id);
589
590 --
591
592 l_pil c_pil%ROWTYPE;
593 l_latest_ler c_latest_ler%ROWTYPE;
594 l_ptnl c_ptnl%ROWTYPE;
595 l_pil_strt c_pil_strt%ROWTYPE;
596 l_get_pil c_get_pil%ROWTYPE;
597 l_procd_dt DATE;
598 l_strtd_dt DATE;
599 l_voidd_dt DATE;
600 l_pel_object_version_number NUMBER;
601 l_pel_pk_id NUMBER;
602 --
603 BEGIN
604 /*** Voiding
605 1.Dont void if pil is in PROCESSED state ,throw error
606 2.Dont Void if it is not the latest one ,throw error
607 3.Update person_life_event (PIL)
608 4.update potential Life event (PPL)
609 5.update pil_electbl_choice_popl (PEL)
610 ***/
611 hr_utility.set_location (' Entering ben_irc_util.void_or_restore_life_event ',
612 10
613 );
614 /***
615 p_void_per_in_ler_id As null
616 ***/
617 IF p_void_per_in_ler_id IS NULL
618 THEN
619 hr_utility.set_location ('p_void_per_in_ler_id is NULL ', 888);
620 OPEN c_get_pil;
621
622 LOOP
623 FETCH c_get_pil INTO l_get_pil;
624 EXIT WHEN c_get_pil%NOTFOUND;
625 /*** Update PIl,PEL,PPL
626 ***/
627 hr_utility.set_location ('Voiding starts ', 999);
628 hr_utility.set_location ('Before update_person_life_event ', 9901);
629 ben_person_life_event_api.update_person_life_event (p_per_in_ler_id => l_get_pil.per_in_ler_id,
630 p_bckt_per_in_ler_id => NULL,
631 p_per_in_ler_stat_cd => 'VOIDD',
632 p_prvs_stat_cd => l_get_pil.per_in_ler_stat_cd,
633 p_object_version_number => l_get_pil.object_version_number,
634 p_effective_date => p_effective_date,
635 p_procd_dt => l_procd_dt,
636 p_strtd_dt => l_strtd_dt,
637 p_voidd_dt => l_voidd_dt
638 );
639 OPEN c_ptnl (l_get_pil.ptnl_ler_for_per_id);
640 FETCH c_ptnl INTO l_ptnl;
641 CLOSE c_ptnl;
642 hr_utility.set_location ('Before update_ptnl_ler_for_per_perf ',
643 9902);
644 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf (p_validate => FALSE,
645 p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id,
646 p_ptnl_ler_for_per_stat_cd => 'VOIDD',
647 p_person_id => l_ptnl.person_id,
648 p_business_group_id => l_ptnl.business_group_id,
649 p_object_version_number => l_ptnl.object_version_number,
650 p_effective_date => p_effective_date,
651 p_program_application_id => fnd_global.prog_appl_id,
652 p_program_id => fnd_global.conc_program_id,
653 p_request_id => fnd_global.conc_request_id,
654 p_program_update_date => SYSDATE,
655 p_voidd_dt => p_effective_date
656 );
657 OPEN c_pil_elctbl_chc_popl (l_get_pil.per_in_ler_id);
658 hr_utility.set_location ('Before update_pil_elctbl_chc_popl ', 9903);
659
660 LOOP
661 FETCH c_pil_elctbl_chc_popl INTO l_pel_pk_id,
662 l_pel_object_version_number;
663 EXIT WHEN c_pil_elctbl_chc_popl%NOTFOUND;
664 --
665 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl (p_validate => FALSE,
666 p_pil_elctbl_chc_popl_id => l_pel_pk_id,
667 p_pil_elctbl_popl_stat_cd => 'BCKDT',
668 p_object_version_number => l_pel_object_version_number,
669 p_effective_date => p_effective_date
670 );
671 END LOOP;
672
673 hr_utility.set_location ('After update_pil_elctbl_chc_popl ', 9904);
674 CLOSE c_pil_elctbl_chc_popl;
675 END LOOP;
676
677 CLOSE c_get_pil;
678 END IF; -- p_void_per_in_ler_id IS NULL
679
680 /***
681 p_void_per_in_ler_id not null
682 ***/
683 IF p_void_per_in_ler_id IS NOT NULL
684 THEN
685 -- Step 1
686 hr_utility.set_location ('p_void_per_in_ler_id is NOT NULL ', 888);
687 OPEN c_pil (p_void_per_in_ler_id);
688 FETCH c_pil INTO l_pil;
689 CLOSE c_pil;
690
691 IF l_pil.per_in_ler_stat_cd = 'PROCD'
692 THEN
693 fnd_message.set_name ('BEN', 'BEN_94597_IRC_OFFER_PROCESSED');
694 fnd_message.raise_error;
695 END IF;
696
697 -- Step 2
698 OPEN c_latest_ler;
699 FETCH c_latest_ler INTO l_latest_ler;
700
701 IF c_latest_ler%FOUND
702 THEN
703 IF l_latest_ler.per_in_ler_id <> p_void_per_in_ler_id
704 THEN
705 CLOSE c_latest_ler;
706 fnd_message.set_name ('BEN', 'BEN_92216_NOT_LATST_PER_IN_LER');
707 fnd_message.raise_error;
708 END IF;
709 END IF;
710
711 CLOSE c_latest_ler;
712
713 IF l_pil.per_in_ler_stat_cd <> 'VOIDD'
714 THEN
715 --step 3
716 hr_utility.set_location ('Voiding starts ', 222);
717 hr_utility.set_location ('Before update_person_life_event ', 111);
718 ben_person_life_event_api.update_person_life_event (p_per_in_ler_id => p_void_per_in_ler_id,
719 p_bckt_per_in_ler_id => NULL,
720 p_per_in_ler_stat_cd => 'VOIDD',
721 p_prvs_stat_cd => l_pil.per_in_ler_stat_cd,
722 p_object_version_number => l_pil.object_version_number,
723 p_effective_date => p_effective_date,
724 p_procd_dt => l_procd_dt,
725 p_strtd_dt => l_strtd_dt,
726 p_voidd_dt => l_voidd_dt
727 );
728 hr_utility.set_location ('After update_person_life_event ', 111);
729 -- step 4
730 OPEN c_ptnl (l_pil.ptnl_ler_for_per_id);
731 FETCH c_ptnl INTO l_ptnl;
732 CLOSE c_ptnl;
733 hr_utility.set_location ('Before update_ptnl_ler_for_per_perf ', 111);
734 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf (p_validate => FALSE,
735 p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id,
736 p_ptnl_ler_for_per_stat_cd => 'VOIDD',
737 p_person_id => l_ptnl.person_id,
738 p_business_group_id => l_ptnl.business_group_id,
739 p_object_version_number => l_ptnl.object_version_number,
740 p_effective_date => p_effective_date,
741 p_program_application_id => fnd_global.prog_appl_id,
742 p_program_id => fnd_global.conc_program_id,
743 p_request_id => fnd_global.conc_request_id,
744 p_program_update_date => SYSDATE,
745 p_voidd_dt => p_effective_date
746 );
747 hr_utility.set_location ('After update_ptnl_ler_for_per_perf ', 111);
748 -- step 5
749 OPEN c_pil_elctbl_chc_popl (p_void_per_in_ler_id);
750 hr_utility.set_location ('Before update_pil_elctbl_chc_popl ', 111);
751
752 LOOP
753 FETCH c_pil_elctbl_chc_popl INTO l_pel_pk_id,
754 l_pel_object_version_number;
755 EXIT WHEN c_pil_elctbl_chc_popl%NOTFOUND;
756 --
757 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl (p_validate => FALSE,
758 p_pil_elctbl_chc_popl_id => l_pel_pk_id,
759 p_pil_elctbl_popl_stat_cd => 'BCKDT',
760 p_object_version_number => l_pel_object_version_number,
761 p_effective_date => p_effective_date
762 );
763 END LOOP;
764
765 hr_utility.set_location ('After update_pil_elctbl_chc_popl ', 111);
766 CLOSE c_pil_elctbl_chc_popl;
767 END IF; --l_pil.per_in_ler_stat_cd <> 'VOIDD'
768 END IF; --p_void_per_in_ler_id IS NOT NULL
769
770 /*** Restoring
771 1.Dont restore (/ STRTED ) if pil is in PROCESSED state ,throw error
772 2.Dont restore ( / STRTED ) if there is already one in STARTED ,throw error
773 3.Dont restore if its not the latest backed out pil
774 3.Update person_life_event (PIL)
775 4.update potential Life event (PPL)
776 5.update pil_electbl_choice_popl (PEL)
777 ***/
778 -- Step 1
779 IF p_restore_per_in_ler_id IS NOT NULL
780 THEN
781 OPEN c_pil (p_restore_per_in_ler_id);
782 FETCH c_pil INTO l_pil;
783 CLOSE c_pil;
784
785 IF l_pil.per_in_ler_stat_cd = 'PROCD'
786 THEN
787 fnd_message.set_name ('BEN', 'BEN_94597_IRC_OFFER_PROCESSED');
788 fnd_message.raise_error;
789 END IF;
790
791 -- Step 2
792 OPEN c_latest_bckdt_ler;
793 FETCH c_latest_bckdt_ler INTO l_latest_ler;
794
795 IF c_latest_bckdt_ler%FOUND
796 THEN
797 IF l_latest_ler.per_in_ler_id <> p_restore_per_in_ler_id
798 THEN
799 CLOSE c_latest_bckdt_ler;
800 fnd_message.set_name ('BEN', 'BEN_94599_NOT_LATST_BCKDT');
801 fnd_message.raise_error;
802 END IF;
803 END IF;
804
805 CLOSE c_latest_bckdt_ler;
806 -- step 3
807 OPEN c_pil_strt;
808 FETCH c_pil_strt INTO l_pil_strt;
809
810 IF c_pil_strt%FOUND
811 THEN
812 CLOSE c_pil_strt;
813 fnd_message.set_name ('BEN', 'BEN_94598_ALREADY_ACTIVE');
814 fnd_message.raise_error;
815 END IF;
816
817 CLOSE c_pil_strt;
818
819 IF l_pil.per_in_ler_stat_cd NOT IN ('STRTD', 'VOIDD')
820 THEN
821 -- Step 4
822 hr_utility.set_location ('Restoring starts ', 222);
823 hr_utility.set_location ('Before update_person_life_event ', 222);
824 ben_person_life_event_api.update_person_life_event (p_per_in_ler_id => p_restore_per_in_ler_id,
825 p_bckt_per_in_ler_id => NULL,
826 p_per_in_ler_stat_cd => 'STRTD',
827 p_prvs_stat_cd => l_pil.per_in_ler_stat_cd,
828 p_object_version_number => l_pil.object_version_number,
829 p_effective_date => p_effective_date,
830 p_procd_dt => l_procd_dt,
831 p_strtd_dt => l_strtd_dt,
832 p_voidd_dt => l_voidd_dt
833 );
834 hr_utility.set_location ('After update_person_life_event ', 222);
835 -- step 5
836 OPEN c_ptnl (l_pil.ptnl_ler_for_per_id);
837 FETCH c_ptnl INTO l_ptnl;
838 CLOSE c_ptnl;
839 hr_utility.set_location ('Before update_ptnl_ler_for_per_perf ', 222);
840 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf (p_validate => FALSE,
841 p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id,
842 p_ptnl_ler_for_per_stat_cd => 'PROCD',
843 p_lf_evt_ocrd_dt => l_pil.lf_evt_ocrd_dt,
844 p_procd_dt => l_pil.lf_evt_ocrd_dt,
845 p_person_id => l_ptnl.person_id,
846 p_business_group_id => l_ptnl.business_group_id,
847 p_object_version_number => l_ptnl.object_version_number,
848 p_effective_date => p_effective_date,
849 p_program_application_id => fnd_global.prog_appl_id,
850 p_program_id => fnd_global.conc_program_id,
851 p_request_id => fnd_global.conc_request_id,
852 p_program_update_date => SYSDATE
853 );
854 hr_utility.set_location ('After update_ptnl_ler_for_per_perf ', 222);
855 -- step 6
856 OPEN c_pil_elctbl_chc_popl (l_pil.per_in_ler_id);
857 hr_utility.set_location ('Before update_pil_elctbl_chc_popl ', 222);
858
859 LOOP
860 FETCH c_pil_elctbl_chc_popl INTO l_pel_pk_id,
861 l_pel_object_version_number;
862 EXIT WHEN c_pil_elctbl_chc_popl%NOTFOUND;
863 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl (p_validate => FALSE,
864 p_pil_elctbl_chc_popl_id => l_pel_pk_id,
865 p_pil_elctbl_popl_stat_cd => 'STRTD',
866 p_object_version_number => l_pel_object_version_number,
867 p_effective_date => p_effective_date
868 );
869 END LOOP;
870
871 hr_utility.set_location ('After update_pil_elctbl_chc_popl ', 222);
872 CLOSE c_pil_elctbl_chc_popl;
873 END IF;
874 END IF; --IF p_restore_per_in_ler_id IS NOT NULL
875
876 hr_utility.set_location ('Leaving ben_irc_util.void_or_restore_life_event',
877 20
878 );
879 END void_or_restore_life_event;
880
881 end ben_irc_util;