[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_OUS_GRP
Source
1 PACKAGE BODY csi_t_txn_ous_grp as
2 /*$Header: csigtoub.pls 115.12 2004/01/08 23:54:07 rmamidip ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_ous_grp';
5 g_file_name CONSTANT VARCHAR2(12) := 'csigtoub.pls';
6
7 g_user_id NUMBER := FND_GLOBAL.User_Id;
8 g_login_id NUMBER := FND_GLOBAL.Login_Id;
9
10
11 PROCEDURE create_txn_org_assgn_dtls(
12 p_api_version IN NUMBER,
13 p_commit IN VARCHAR2 := fnd_api.g_false,
14 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
15 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
16 px_txn_org_assgn_tbl IN OUT NOCOPY csi_t_datastructures_grp.txn_org_assgn_tbl,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2)
20 IS
21
22 l_api_name CONSTANT VARCHAR2(30) := 'create_txn_org_assgn_dtls';
23 l_api_version CONSTANT NUMBER := 1.0;
24 l_debug_level NUMBER;
25
26 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(512);
29
30 l_count NUMBER;
31 l_txn_oa_rec csi_t_datastructures_grp.txn_org_assgn_rec;
32
33 BEGIN
34
35 -- Standard Start of API savepoint
36 SAVEPOINT create_txn_org_assgn_dtls;
37
38 csi_t_gen_utility_pvt.add('API Being Executed : Create Txn Org Assignments');
39 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
40
41
42 -- Initialize message list if p_init_msg_list is set to TRUE.
43 IF FND_API.To_Boolean( p_init_msg_list ) THEN
44 FND_MSG_PUB.Initialize;
45 END IF;
46
47 -- Initialize API return status to success
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49
50 -- Standard call to check for call compatibility.
51 IF NOT
52
53 FND_API.Compatible_API_Call (
54 p_current_version_number => l_api_version,
55 p_caller_version_number => p_api_version,
56 p_api_name => l_api_name,
57 p_pkg_name => g_pkg_name) THEN
58
59 RAISE FND_API.G_Exc_Unexpected_Error;
60
61 END IF;
62
63 /**** Commented for Bug 3304439
64 -- Check for the profile option and enable trace
65 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
66 dbms_session.set_sql_trace(TRUE);
67 END IF;
68 ****/
69
70 -- main code starts here
71 --
72 -- This procedure check if the installed base is active, If not active
73 -- populates the error message in the message queue and raises the
74 -- fnd_api.g_exc_error exception
75 --
76
77 csi_utility_grp.check_ib_active;
78
79 IF px_txn_org_assgn_tbl.COUNT > 0 THEN
80
81 -- loop thru org assignments table
82 FOR l_index IN px_txn_org_assgn_tbl.FIRST..px_txn_org_assgn_tbl.LAST
83 LOOP
84
85 l_txn_oa_rec := px_txn_org_assgn_tbl(l_index);
86
87 -- call api to create organization assignment records
88 csi_t_txn_ous_pvt.create_txn_org_assgn_dtls(
89 p_api_version => p_api_version,
90 p_commit => p_commit,
91 p_init_msg_list => p_init_msg_list,
92 p_validation_level => p_validation_level,
93 p_txn_org_assgn_rec => l_txn_oa_rec,
94 x_return_status => l_return_status,
95 x_msg_count => l_msg_count,
96 x_msg_data => l_msg_data);
97
98 IF l_return_status <> fnd_api.g_ret_sts_success THEN
99 raise fnd_api.g_exc_error;
100 END IF;
101
102 END LOOP;
103
104 END IF;
105 -- Standard check of p_commit.
106 IF FND_API.To_Boolean( p_commit ) THEN
107 COMMIT WORK;
108 END IF;
109
110 /**** Commented for Bug 3304439
111 -- Check for the profile option and disable the trace
112 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
113 dbms_session.set_sql_trace(false);
114 END IF;
115 ****/
116
117 -- Standard call to get message count and if count is get message info.
118 FND_MSG_PUB.Count_And_Get(
119 p_count => x_msg_count,
120 p_data => x_msg_data);
121
122 csi_t_gen_utility_pvt.add('API Successfully Executed : Create Txn Org Assignments');
123 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
124
125 csi_t_gen_utility_pvt.set_debug_off;
126
127 EXCEPTION
128 WHEN FND_API.G_EXC_ERROR THEN
129
130 ROLLBACK TO create_txn_org_assgn_dtls;
131 x_return_status := FND_API.G_RET_STS_ERROR ;
132 FND_MSG_PUB.Count_And_Get (
133 p_count => x_msg_count,
134 p_data => x_msg_data);
135
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137
138 ROLLBACK TO create_txn_org_assgn_dtls;
139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
140
141 FND_MSG_PUB.Count_And_Get(
142 p_count => x_msg_count,
143 p_data => x_msg_data);
144
145 WHEN OTHERS THEN
146
147 ROLLBACK TO create_txn_org_assgn_dtls;
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
149
150 IF FND_MSG_PUB.Check_Msg_Level(
151 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
152
153 FND_MSG_PUB.Add_Exc_Msg(
154 p_pkg_name => G_PKG_NAME,
155 p_procedure_name => l_api_name);
156
157 END IF;
158
159 FND_MSG_PUB.Count_And_Get(
160 p_count => x_msg_count,
161 p_data => x_msg_data);
162
163 END create_txn_org_assgn_dtls;
164
165 /*
166 */
167 PROCEDURE update_txn_org_assgn_dtls
168 (
169 p_api_version IN NUMBER
170 ,p_commit IN VARCHAR2 := fnd_api.g_false
171 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
172 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
173 ,p_txn_org_assgn_tbl IN csi_t_datastructures_grp.txn_org_assgn_tbl
174 ,x_return_status OUT NOCOPY VARCHAR2
175 ,x_msg_count OUT NOCOPY NUMBER
176 ,x_msg_data OUT NOCOPY VARCHAR2
177 )
178 IS
179
180 l_api_name CONSTANT VARCHAR2(30) := 'Update_Txn_Org_Assgn_Dtls';
181 l_api_version CONSTANT NUMBER := 1.0;
182 l_debug_level NUMBER;
183
184 l_return_status VARCHAR2(1) := FND_API.G_ret_sts_success;
185 l_msg_count NUMBER;
186 l_msg_data VARCHAR2(512);
187
188 BEGIN
189
190 -- Standard Start of API savepoint
191 SAVEPOINT Update_Txn_Org_Assgn_Dtls;
192
193 csi_t_gen_utility_pvt.add('API Being Executed : Update Txn Org Assignments');
194 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
195
196
197 -- Initialize message list if p_init_msg_list is set to TRUE.
198 IF FND_API.to_Boolean( p_init_msg_list ) THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201
202 -- Initialize API return status to success
203 x_return_status := FND_API.G_RET_STS_SUCCESS;
204
205 -- Standard call to check for call compatibility.
206 IF NOT
207
208 FND_API.Compatible_API_Call (
209 p_current_version_number => l_api_version,
210 p_caller_version_number => p_api_version,
211 p_api_name => l_api_name,
212 p_pkg_name => G_PKG_NAME) THEN
213
214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
215
216 END IF;
217
218 /**** Commented for Bug 3304439
219 -- Check for the profile option and enable trace
220 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
221 dbms_session.set_sql_trace(TRUE);
222 END IF;
223 ****/
224
225 -- Main API code
226 --
227 -- This procedure check if the installed base is active, If not active
228 -- populates the error message in the message queue and raises the
229 -- fnd_api.g_exc_error exception
230 --
231
232 csi_utility_grp.check_ib_active;
233
234 csi_t_txn_ous_pvt.update_txn_org_assgn_dtls(
235 p_api_version => p_api_version,
236 p_commit => p_commit,
237 p_init_msg_list => p_init_msg_list,
238 p_validation_level => p_validation_level,
239 p_txn_org_assgn_tbl => p_txn_org_assgn_tbl,
240 x_return_status => l_return_status,
241 x_msg_count => l_msg_count,
242 x_msg_data => l_msg_data);
243
244 IF l_return_status <> fnd_api.g_ret_sts_success THEN
245 RAISE fnd_api.g_exc_error;
246 END IF;
247
248 -- Standard check of p_commit.
249 IF FND_API.To_Boolean( p_commit ) THEN
250 COMMIT WORK;
251 END IF;
252
253 /**** Commented for Bug 3304439
254 -- Check for the profile option and disable the trace
255 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
256 dbms_session.set_sql_trace(false);
257 END IF;
258 ****/
259
260 -- Standard call to get message count and if count is get message info.
261 FND_MSG_PUB.Count_And_Get(
262 p_count => x_msg_count,
263 p_data => x_msg_data);
264
265 csi_t_gen_utility_pvt.add('API Successfully Executed : Update Txn Org Assignments');
266 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
267
268 csi_t_gen_utility_pvt.set_debug_off;
269
270 EXCEPTION
271 WHEN FND_API.G_EXC_ERROR THEN
272
273 ROLLBACK TO Update_Txn_Org_Assgn_Dtls;
274 x_return_status := FND_API.G_RET_STS_ERROR ;
275 FND_MSG_PUB.Count_And_Get (
276 p_count => x_msg_count,
277 p_data => x_msg_data);
278
279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
280
281 ROLLBACK TO Update_Txn_Org_Assgn_Dtls;
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
283
284 FND_MSG_PUB.Count_And_Get(
285 p_count => x_msg_count,
286 p_data => x_msg_data);
287
288 WHEN OTHERS THEN
289
290 ROLLBACK TO Update_Txn_Org_Assgn_Dtls;
291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
292
293 IF FND_MSG_PUB.Check_Msg_Level(
294 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
295
296 FND_MSG_PUB.Add_Exc_Msg(
297 p_pkg_name => G_PKG_NAME,
298 p_procedure_name => l_api_name);
299
300 END IF;
301
302 FND_MSG_PUB.Count_And_Get(
303 p_count => x_msg_count,
304 p_data => x_msg_data);
305
306 END update_txn_org_assgn_dtls;
307
308
309 PROCEDURE delete_txn_org_assgn_dtls
310 (
311 p_api_version IN NUMBER
312 ,p_commit IN VARCHAR2 := fnd_api.g_false
313 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
314 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
315 ,p_txn_org_assgn_ids_tbl IN csi_t_datastructures_grp.txn_org_assgn_ids_tbl
316 ,x_return_status OUT NOCOPY VARCHAR2
317 ,x_msg_count OUT NOCOPY NUMBER
318 ,x_msg_data OUT NOCOPY VARCHAR2
319 )
320 IS
321
322 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_org_assgn_dtls';
323 l_api_version CONSTANT NUMBER := 1.0;
324 l_debug_level NUMBER;
325
326 l_return_status VARCHAR2(1);
327 l_msg_count NUMBER;
328 l_msg_data VARCHAR2(2000);
329
330 BEGIN
331
332 -- Standard Start of API savepoint
333 SAVEPOINT delete_txn_org_assgn_dtls;
334
335 csi_t_gen_utility_pvt.add('API Being Executed : Delete Txn Org Assignments');
336 csi_t_gen_utility_pvt.add('Transaction Start Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
337
338
339 -- Initialize message list if p_init_msg_list is set to TRUE.
340 IF FND_API.to_Boolean( p_init_msg_list ) THEN
341 FND_MSG_PUB.initialize;
342 END IF;
343
344 -- Initialize API return status to success
345 x_return_status := FND_API.G_RET_STS_SUCCESS;
346
347 -- Standard call to check for call compatibility.
348 IF NOT
349
350 FND_API.Compatible_API_Call (
351 p_current_version_number => l_api_version,
352 p_caller_version_number => p_api_version,
353 p_api_name => l_api_name,
354 p_pkg_name => G_PKG_NAME) THEN
355
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357
358 END IF;
359
360 /**** Commented for Bug 3304439
361 -- Check for the profile option and enable trace
362 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
363 dbms_session.set_sql_trace(TRUE);
364 END IF;
365 ****/
366
367 -- Main API code
368 --
369 -- This procedure check if the installed base is active, If not active
370 -- populates the error message in the message queue and raises the
371 -- fnd_api.g_exc_error exception
372 --
373
374 csi_utility_grp.check_ib_active;
375
376 csi_t_txn_ous_pvt.delete_txn_org_assgn_dtls(
377 p_api_version => p_api_version,
378 p_commit => p_commit,
379 p_init_msg_list => p_init_msg_list,
380 p_validation_level => p_validation_level,
381 p_txn_org_assgn_ids_tbl => p_txn_org_assgn_ids_tbl,
382 x_return_status => l_return_status,
383 x_msg_count => l_msg_count,
384 x_msg_data => l_msg_data);
385
386 IF l_return_status <> fnd_api.g_ret_sts_success THEN
387 RAISE fnd_api.g_exc_error;
388 END IF;
389
390 -- Standard check of p_commit.
391 IF FND_API.To_Boolean( p_commit ) THEN
392 COMMIT WORK;
393 END IF;
394
395 /**** Commented for Bug 3304439
396 -- Check for the profile option and disable the trace
397 IF (fnd_profile.value('CSI_ENABLE_SQL_TRACE') = 'Y') THEN
398 dbms_session.set_sql_trace(false);
399 END IF;
400 ****/
401
402 -- Standard call to get message count and if count is get message info.
403 FND_MSG_PUB.Count_And_Get(
404 p_count => x_msg_count,
405 p_data => x_msg_data);
406
407 csi_t_gen_utility_pvt.add('API Successfully Executed : Delete Txn Org Assignments');
408 csi_t_gen_utility_pvt.add('Transaction End Time :'||to_char(sysdate, 'MM/DD/YY HH24:MI:SS'));
409
410 csi_t_gen_utility_pvt.set_debug_off;
411
412 EXCEPTION
413 WHEN FND_API.G_EXC_ERROR THEN
414
415 ROLLBACK TO delete_txn_org_assgn_dtls;
416 x_return_status := FND_API.G_RET_STS_ERROR ;
417 FND_MSG_PUB.Count_And_Get (
418 p_count => x_msg_count,
419 p_data => x_msg_data);
420
421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
422
423 ROLLBACK TO delete_txn_org_assgn_dtls;
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
425
426 FND_MSG_PUB.Count_And_Get(
427 p_count => x_msg_count,
428 p_data => x_msg_data);
429
430 WHEN OTHERS THEN
431
432 ROLLBACK TO delete_txn_org_assgn_dtls;
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
434
435 IF FND_MSG_PUB.Check_Msg_Level(
436 p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
437
438 FND_MSG_PUB.Add_Exc_Msg(
439 p_pkg_name => G_PKG_NAME,
440 p_procedure_name => l_api_name);
441
442 END IF;
443
444 FND_MSG_PUB.Count_And_Get(
445 p_count => x_msg_count,
446 p_data => x_msg_data);
447
448 END delete_txn_org_assgn_dtls;
449
450 END csi_t_txn_ous_grp;