[Home] [Help]
PACKAGE BODY: APPS.PA_ASGN_CONFLICT_HIST_PKG
Source
1 PACKAGE BODY PA_ASGN_CONFLICT_HIST_PKG as
2 /* $Header: PARGASNB.pls 120.1.12010000.4 2010/03/23 10:24:34 amehrotr ship $ */
3
4 --
5 -- Procedure : Insert_rows
6 -- Purpose : Create Rows in PA_ASSIGNMENT_CONFLICT_HIST.
7 --
8 PROCEDURE insert_rows
9 ( p_conflict_group_id IN Number := NULL ,
10 p_assignment_id IN Number ,
11 p_conflict_assignment_id IN Number ,
12 p_resolve_conflict_action_code IN VARCHAR2 ,
13 p_intra_txn_conflict_flag IN VARCHAR2 ,
14 p_processed_flag IN VARCHAR2 := 'N' ,
15 p_self_conflict_flag IN VARCHAR2 ,
16 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
17 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
18 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
19 IS
20
21 BEGIN
22 INSERT INTO pa_assignment_conflict_hist
23 ( conflict_group_id ,
24 assignment_id ,
25 conflict_assignment_id ,
26 resolve_conflicts_action_code ,
27 intra_txn_conflict_flag ,
28 processed_flag ,
29 self_conflict_flag ,
30 creation_date ,
31 created_by ,
32 last_update_date ,
33 last_updated_by ,
34 last_update_login )
35 VALUES
36 (nvl(p_conflict_group_id, pa_assignment_conflict_hist_s.nextval),
37 p_assignment_id ,
38 p_conflict_assignment_id ,
39 p_resolve_conflict_action_code ,
40 p_intra_txn_conflict_flag ,
41 p_processed_flag ,
42 p_self_conflict_flag ,
43 sysdate ,
44 fnd_global.user_id ,
45 sysdate ,
46 fnd_global.user_id ,
47 fnd_global.login_id );
48
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50 EXCEPTION
51 WHEN OTHERS THEN
52 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
53 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLICT_HIST_PKG',
54 p_procedure_name => 'insert_rows');
55 raise;
56
57 END insert_rows;
58
59
60 --
61 -- Procedure : Insert_rows (Overloaded)
62 -- Purpose : Create Rows in PA_ASSIGNMENT_CONFLICT_HIST for a single
63 -- assigment with a table of conflicting assignments.
64 --
65 PROCEDURE insert_rows
66 ( p_conflict_group_id IN NUMBER := NULL ,
67 p_assignment_id IN NUMBER ,
68 p_conflict_assignment_id_tbl IN PA_PLSQL_DATATYPES.NumTabTyp ,
69 p_resolve_conflict_action_code IN VARCHAR2 ,
70 p_intra_txn_conflict_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE:=NULL,
71 p_processed_flag IN VARCHAR2 := 'N' ,
72 x_conflict_group_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
73 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
74 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
75 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
76 IS
77 l_conflict_group_id NUMBER := p_conflict_group_id;
78 BEGIN
79
80 IF p_conflict_group_id IS NULL THEN
81 SELECT pa_assignment_conflict_hist_s.nextval
82 INTO l_conflict_group_id
83 FROM dual;
84 END IF;
85 x_conflict_group_id := l_conflict_group_id;
86
87 IF p_conflict_assignment_id_tbl.COUNT > 0 THEN
88 IF p_intra_txn_conflict_flag_tbl IS NULL THEN
89 FORALL j IN p_conflict_assignment_id_tbl.FIRST .. p_conflict_assignment_id_tbl.LAST
90 INSERT INTO pa_assignment_conflict_hist
91 ( conflict_group_id ,
92 assignment_id ,
93 conflict_assignment_id ,
94 resolve_conflicts_action_code ,
95 intra_txn_conflict_flag ,
96 processed_flag ,
97 self_conflict_flag ,
98 creation_date ,
99 created_by ,
100 last_update_date ,
101 last_updated_by ,
102 last_update_login )
103 VALUES
104 ( l_conflict_group_id ,
105 p_assignment_id ,
106 p_conflict_assignment_id_tbl(j) ,
107 p_resolve_conflict_action_code ,
108 'N' ,
109 p_processed_flag ,
110 decode((p_assignment_id - p_conflict_assignment_id_tbl(j)), 0, 'Y', 'N'),
111 sysdate ,
112 fnd_global.user_id ,
113 sysdate ,
114 fnd_global.user_id ,
115 fnd_global.login_id );
116
117 ELSE
118 FORALL j IN p_conflict_assignment_id_tbl.FIRST .. p_conflict_assignment_id_tbl.LAST
119 INSERT INTO pa_assignment_conflict_hist
120 ( conflict_group_id ,
121 assignment_id ,
122 conflict_assignment_id ,
123 resolve_conflicts_action_code ,
124 intra_txn_conflict_flag ,
125 processed_flag ,
126 self_conflict_flag ,
127 creation_date ,
128 created_by ,
129 last_update_date ,
130 last_updated_by ,
131 last_update_login )
132 VALUES
133 ( l_conflict_group_id ,
134 p_assignment_id ,
135 p_conflict_assignment_id_tbl(j) ,
136 p_resolve_conflict_action_code ,
137 p_intra_txn_conflict_flag_tbl(j) ,
138 p_processed_flag ,
139 decode((p_assignment_id - p_conflict_assignment_id_tbl(j)), 0, 'Y', 'N'),
140 sysdate ,
141 fnd_global.user_id ,
142 sysdate ,
143 fnd_global.user_id ,
144 fnd_global.login_id );
145
146 END IF;
147 END IF;
148
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150 EXCEPTION
151 WHEN OTHERS THEN
152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLICT_HIST_PKG',
154 p_procedure_name => 'insert_rows');
155 raise;
156
157 END insert_rows;
158
159
160 --
161 -- Procedure : update_rows (overloaded)
162 -- Purpose : Update rows in pa_assignment_conflict_hist with
163 -- p_resolve_conflict_action_code only. This is
164 -- overloaded procedure.
165 --
166 PROCEDURE update_rows
167 ( p_conflict_group_id IN Number ,
168 p_assignment_id IN Number ,
169 p_conflict_assignment_id IN Number ,
170 p_resolve_conflict_action_code IN VARCHAR2 ,
171 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
172 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
173 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
174 IS
175
176 BEGIN
177
178 UPDATE PA_ASSIGNMENT_CONFLICT_HIST
179 SET
180 resolve_conflicts_action_code = p_resolve_conflict_action_code,
181 last_update_date = sysdate,
182 last_updated_by = fnd_global.user_id,
183 last_update_login = fnd_global.login_id
184 WHERE conflict_group_id = p_conflict_group_id
185 AND assignment_id = p_assignment_id
186 AND conflict_assignment_id = p_conflict_assignment_id;
187
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLICT_HIST_PKG',
194 p_procedure_name => 'update_rows');
195 raise;
196
197 END update_rows;
198
199
200 --
201 -- Procedure : update_rows (overloaded)
202 -- Purpose : Update rows in pa_assignment_conflict_hist with
203 -- p_resolve_conflict_action_code only for the whole
204 -- conflict group. This is overloaded procedure. This
205 -- is called from the Resource Overcommitment page.
206 PROCEDURE update_rows
207 ( p_conflict_group_id IN Number ,
208 p_resolve_conflict_action_code IN VARCHAR2 ,
209 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
210 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
211 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
212 IS
213
214 BEGIN
215
216 UPDATE PA_ASSIGNMENT_CONFLICT_HIST
217 SET
218 resolve_conflicts_action_code = p_resolve_conflict_action_code,
219 last_update_date = sysdate,
220 last_updated_by = fnd_global.user_id,
221 last_update_login = fnd_global.login_id
222 WHERE conflict_group_id = p_conflict_group_id;
223
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLICT_HIST_PKG',
230 p_procedure_name => 'update_rows');
231 raise;
232
233 END update_rows;
234
235
236 --
237 -- Procedure : update_rows (overloaded)
238 -- Purpose : Update rows in pa_assignment_conflict_hist with
239 -- p_resolve_conflict_action_code only for the whole
240 -- conflict group. This is overloaded procedure. This
241 -- is called from the View Conflicts page.
242 PROCEDURE update_rows
243 ( p_conflict_group_id IN Number ,
244 p_assignment_id_arr IN SYSTEM.PA_NUM_TBL_TYPE ,
245 p_action_code_arr IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE ,
246 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
247 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
248 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
249 IS
250
251 BEGIN
252
253 IF p_assignment_id_arr.COUNT > 0 THEN
254 FORALL j IN p_assignment_id_arr.FIRST .. p_assignment_id_arr.LAST
255 UPDATE pa_assignment_conflict_hist
256 SET
257 resolve_conflicts_action_code = p_action_code_arr(j),
258 last_update_date = sysdate,
259 last_updated_by = fnd_global.user_id,
260 last_update_login = fnd_global.login_id
261 WHERE conflict_group_id = p_conflict_group_id
262 AND assignment_id = p_assignment_id_arr(j);
263
264 END IF;
265
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLICT_HIST_PKG',
272 p_procedure_name => 'update_rows');
273 raise;
274
275 END update_rows;
276
277
278 --
279 -- Procedure : update_rows (overloaded)
280 -- Purpose : Update rows in pa_assignment_conflict_hist with
281 -- p_processed_flag only. This is an overloaded
282 -- procedure.
283 --
284 PROCEDURE update_rows
285 ( p_conflict_group_id IN Number ,
286 p_assignment_id IN Number ,
287 p_processed_flag IN VARCHAR2 ,
288 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
289 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
290 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
291 IS
292
293 BEGIN
294
295 UPDATE PA_ASSIGNMENT_CONFLICT_HIST
296 SET
297 processed_flag = p_processed_flag,
298 last_update_date = sysdate,
299 last_updated_by = fnd_global.user_id,
300 last_update_login = fnd_global.login_id
301 WHERE conflict_group_id = p_conflict_group_id
302 AND assignment_id = p_assignment_id;
303
304 x_return_status := FND_API.G_RET_STS_SUCCESS;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLICT_HIST_PKG',
310 p_procedure_name => 'update_rows');
311 raise;
312
313 END update_rows;
314
315
316 --
317 -- Procedure : delete_rows
318 -- Purpose : Deletes rows in PA_ASSIGNMENT_CONFLICT_HIST.
319 --
320 -- Parameters :
321 --
322 PROCEDURE delete_rows
323 ( p_conflict_group_id IN NUMBER,
324 p_assignment_id IN NUMBER,
325 p_conflict_assignment_id IN NUMBER,
326 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
327 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
328 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
329 IS
330
331 BEGIN
332
333 DELETE FROM PA_ASSIGNMENT_CONFLICT_HIST
334 WHERE conflict_group_id = p_conflict_group_id
335 AND assignment_id = p_assignment_id
336 AND conflict_assignment_id = p_conflict_assignment_id;
337
338 x_return_status := FND_API.G_RET_STS_SUCCESS;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
343 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLCIT_HIST_PKG',
344 p_procedure_name => 'delete_rows');
345 raise;
346
347 END delete_rows;
348
349
350 --
351 -- Procedure : delete_conflict_rows -- bug 7118933
352 -- Purpose : Deletes rows in PA_ASSIGNMENT_CONFLICT_HIST.
353 --
354 -- Parameters :
355 --
356 PROCEDURE delete_rows
357 ( p_assignment_id IN NUMBER,
358 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
359 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
360 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
361 IS
362
363 BEGIN
364
365 DELETE FROM PA_ASSIGNMENT_CONFLICT_HIST -- Bug 9356152
366 WHERE assignment_id = p_assignment_id
367 OR CONFLICT_ASSIGNMENT_ID = p_assignment_id;
368
369 x_return_status := FND_API.G_RET_STS_SUCCESS;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASGN_CONFLCIT_HIST_PKG',
375 p_procedure_name => 'delete_conflict_rows');
376 raise;
377
378 END delete_rows;
379
380
381 END PA_ASGN_CONFLICT_HIST_PKG;