[Home] [Help]
PACKAGE BODY: APPS.PA_ROLE_JOB_BG_PVT
Source
1 package body PA_ROLE_JOB_BG_PVT AS
2 /* $Header: PAXRJBVB.pls 115.2 2003/08/25 19:01:45 ramurthy ship $ */
3
4 procedure INSERT_ROW (
5 p_commit IN VARCHAR2:=FND_API.G_FALSE,
6 p_debug_mode IN VARCHAR2 DEFAULT 'N',
7 P_ROLE_JOB_BG_ID OUT NOCOPY NUMBER,
8 P_PROJECT_ROLE_ID IN NUMBER,
9 P_BUSINESS_GROUP_ID IN NUMBER,
10 P_JOB_ID IN NUMBER,
11 P_MIN_JOB_LEVEL IN NUMBER,
12 P_MAX_JOB_LEVEL IN NUMBER,
13 P_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
14 P_LAST_UPDATE_DATE IN DATE,
15 P_LAST_UPDATED_BY IN NUMBER,
16 P_CREATION_DATE IN DATE,
17 P_CREATED_BY IN NUMBER,
18 P_LAST_UPDATE_LOGIN IN NUMBER,
19 p_return_status OUT NOCOPY VARCHAR2,
20 p_msg_count OUT NOCOPY NUMBER,
21 p_msg_data OUT NOCOPY VARCHAR2
22 ) IS
23
24 l_sqlcode varchar2(30);
25 l_error_message_code varchar2(30);
26
27 BEGIN
28
29 -- hr_utility.trace_on(NULL, 'RMDUP');
30 -- hr_utility.trace('start insert row');
31
32 FND_MSG_PUB.initialize;
33
34 p_msg_count := 0;
35
36 -- Check if the role job defaults for this BG is a duplicate
37 pa_role_job_bg_utils.check_dup_job_bg_defaults(
38 p_role_job_bg_id => p_role_job_bg_id,
39 p_project_role_id => p_project_role_id,
40 p_business_group_id => p_business_group_id,
41 p_return_status => p_return_status,
42 p_error_message_code => l_error_message_code);
43
44 -- hr_utility.trace('after check_dup_job_bg_defaults');
45 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
46
47 -- Call the table handler to insert into the table
48
49 -- hr_utility.trace('before tabkle handler insert row');
50 pa_role_job_bg_pkg.insert_row(
51 P_ROLE_JOB_BG_ID => P_ROLE_JOB_BG_ID,
52 P_PROJECT_ROLE_ID => P_PROJECT_ROLE_ID,
53 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
54 P_JOB_ID => P_JOB_ID,
55 P_MIN_JOB_LEVEL => P_MIN_JOB_LEVEL,
56 P_MAX_JOB_LEVEL => P_MAX_JOB_LEVEL,
57 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
58 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
59 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
60 P_CREATION_DATE => P_CREATION_DATE,
61 P_CREATED_BY => P_CREATED_BY,
62 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
63 );
64
65 -- hr_utility.trace('after tabkle handler insert row');
66 -- hr_utility.trace('error is : ' || sqlerrm);
67
68 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
69 fnd_message.set_name('PA', l_error_message_code);
70 fnd_msg_pub.ADD;
71 p_msg_count := p_msg_count + 1;
72
73 ELSIF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
74
75 fnd_msg_pub.add_exc_msg
76 (p_pkg_name => 'pa_role_job_bg_utils',
77 p_procedure_name => 'check_dup_job_bg_defaults',
78 p_error_text => l_error_message_code);
79
80 p_msg_count := p_msg_count + 1;
81
82 END IF;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86
87 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88 l_sqlcode := SQLCODE;
89
90 fnd_msg_pub.add_exc_msg
91 (p_pkg_name => 'PA_ROLE_JOB_BG_PVT',
92 p_procedure_name => 'INSERT_ROW',
93 p_error_text => l_sqlcode);
94
95 p_msg_count := p_msg_count + 1;
96
97 END;
98
99 procedure LOCK_ROW (
100 p_commit IN VARCHAR2:=FND_API.G_FALSE,
101 p_debug_mode IN VARCHAR2 DEFAULT 'N',
102 P_ROLE_JOB_BG_ID IN NUMBER,
103 P_OBJECT_VERSION_NUMBER IN NUMBER,
104 p_return_status OUT NOCOPY VARCHAR2,
105 p_msg_count OUT NOCOPY NUMBER,
106 p_msg_data OUT NOCOPY VARCHAR2
107 ) IS
108
109 l_sqlcode varchar2(30);
110
111 BEGIN
112
113 FND_MSG_PUB.initialize;
114
115 p_msg_count := 0;
116
117 -- Call the table handler to lock the row
118
119 pa_role_job_bg_pkg.lock_row(
120 P_ROLE_JOB_BG_ID => P_ROLE_JOB_BG_ID,
121 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER
122 );
123
124 p_return_status := FND_API.G_RET_STS_SUCCESS;
125
126 EXCEPTION
127 WHEN OTHERS THEN
128
129 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130 l_sqlcode := SQLCODE;
131
132 fnd_msg_pub.add_exc_msg
133 (p_pkg_name => 'PA_ROLE_JOB_BG_PVT',
134 p_procedure_name => 'LOCK_ROW',
135 p_error_text => l_sqlcode);
136
137 p_msg_count := p_msg_count + 1;
138 END;
139
140 procedure UPDATE_ROW (
141 p_commit IN VARCHAR2:=FND_API.G_FALSE,
142 p_debug_mode IN VARCHAR2 DEFAULT 'N',
143 P_ROLE_JOB_BG_ID IN NUMBER,
144 P_PROJECT_ROLE_ID IN NUMBER,
145 P_BUSINESS_GROUP_ID IN NUMBER,
146 P_JOB_ID IN NUMBER,
147 P_MIN_JOB_LEVEL IN NUMBER,
148 P_MAX_JOB_LEVEL IN NUMBER,
149 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
150 P_LAST_UPDATE_DATE IN DATE,
151 P_LAST_UPDATED_BY IN NUMBER,
152 P_CREATION_DATE IN DATE,
153 P_CREATED_BY IN NUMBER,
154 P_LAST_UPDATE_LOGIN IN NUMBER,
155 p_return_status OUT NOCOPY VARCHAR2,
156 p_msg_count OUT NOCOPY NUMBER,
157 p_msg_data OUT NOCOPY VARCHAR2
158 ) is
159
160 l_error_message_code VARCHAR2(30);
161 l_sqlcode VARCHAR2(30);
162 l_business_group_id NUMBER(15);
163
164 BEGIN
165
166 FND_MSG_PUB.initialize;
167
168 p_msg_count := 0;
169
170 select business_group_id
171 into l_business_group_id
172 from pa_role_job_bgs
173 where role_job_bg_id = p_role_job_bg_id;
174
175 p_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 IF l_business_group_id <> p_business_group_id THEN
178
179 -- Check if the role job defaults for this BG is a duplicate
180 pa_role_job_bg_utils.check_dup_job_bg_defaults(
181 p_role_job_bg_id => p_role_job_bg_id,
182 p_project_role_id => p_project_role_id,
183 p_business_group_id => p_business_group_id,
184 p_return_status => p_return_status,
185 p_error_message_code => l_error_message_code);
186
187 END IF;
188
189 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
190
191 -- Call the table handler to update the row
192
193 pa_role_job_bg_pkg.update_row(
194 P_ROLE_JOB_BG_ID => P_ROLE_JOB_BG_ID,
195 P_PROJECT_ROLE_ID => P_PROJECT_ROLE_ID,
196 P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID,
197 P_JOB_ID => P_JOB_ID,
198 P_MIN_JOB_LEVEL => P_MIN_JOB_LEVEL,
199 P_MAX_JOB_LEVEL => P_MAX_JOB_LEVEL,
200 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER,
201 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
202 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
203 P_CREATION_DATE => P_CREATION_DATE,
204 P_CREATED_BY => P_CREATED_BY,
205 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
206 );
207
208 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
209 fnd_message.set_name('PA', l_error_message_code);
210 fnd_msg_pub.ADD;
211
212 p_msg_count := p_msg_count + 1;
213 ELSIF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
214
215 fnd_msg_pub.add_exc_msg
216 (p_pkg_name => 'PA_ROLE_JOB_BG_UTILS',
217 p_procedure_name => 'check_dup_job_bg_defaults',
218 p_error_text => l_error_message_code);
219
220 p_msg_count := p_msg_count + 1;
221 END IF;
222
223 EXCEPTION
224 WHEN OTHERS THEN
225
226 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 l_sqlcode := SQLCODE;
228
229 fnd_msg_pub.add_exc_msg
230 (p_pkg_name => 'PA_ROLE_JOB_BG_PVT',
231 p_procedure_name => 'UPDATE_ROW',
232 p_error_text => l_sqlcode);
233
234 p_msg_count := p_msg_count + 1;
235 END;
236
237
238 procedure DELETE_ROW (
239 p_commit IN VARCHAR2:=FND_API.G_FALSE,
240 p_debug_mode IN VARCHAR2 DEFAULT 'N',
241 P_ROLE_JOB_BG_ID IN NUMBER,
242 P_OBJECT_VERSION_NUMBER IN NUMBER,
243 p_return_status OUT NOCOPY VARCHAR2,
244 p_msg_count OUT NOCOPY NUMBER,
245 p_msg_data OUT NOCOPY VARCHAR2
246 ) is
247
248 l_error_message_code VARCHAR2(30);
249 l_sqlcode VARCHAR2(30);
250
251 BEGIN
252 -- hr_utility.trace_on(NULL, 'RMFORM');
253 -- hr_utility.trace('start');
254
255 FND_MSG_PUB.initialize;
256
257 p_msg_count := 0;
258
259 p_return_status := FND_API.G_RET_STS_SUCCESS;
260
261 -- hr_utility.trace('before my stuff');
262
263 -- Call the table handler to delete the job defaults for this BG.
264
265 pa_role_job_bg_pkg.delete_row(
266 P_ROLE_JOB_BG_ID => P_ROLE_JOB_BG_ID,
267 P_OBJECT_VERSION_NUMBER => P_OBJECT_VERSION_NUMBER
268 );
269
270 -- hr_utility.trace('p_return_status is ' || p_return_status);
271 -- hr_utility.trace('p_msg_data is ' || p_msg_data);
272
273 -- IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
274 -- END IF;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278
279 -- hr_utility.trace('SQLERRM is ' || SQLERRM);
280 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
281
282 l_sqlcode := SQLCODE;
283
284 fnd_msg_pub.add_exc_msg
285 (p_pkg_name => 'PA_ROLE_JOB_BG_PVT',
286 p_procedure_name => 'DELETE_ROW',
287 p_error_text => l_sqlcode);
288
289 p_msg_count := p_msg_count + 1;
290
291 END;
292 end PA_ROLE_JOB_BG_PVT;