[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_ROUTING_CTRLS_PKG
Source
1 PACKAGE BODY IGC_CC_ROUTING_CTRLS_PKG as
2 /* $Header: IGCCCTLB.pls 120.3.12000000.2 2007/09/26 17:21:12 smannava ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_CC_ROUTING_CTRLS_PKG';
5 G_debug_flag VARCHAR2(1) := 'N';
6
7 /* ================================================================================
8 PROCEDURE Insert_Row
9 ===============================================================================*/
10
11 PROCEDURE Insert_Row(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_Rowid IN OUT NOCOPY VARCHAR2,
20 p_ORG_ID IGC_CC_ROUTING_CTRLS_ALL.ORG_ID%TYPE,
21 p_CC_TYPE IGC_CC_ROUTING_CTRLS_ALL.CC_TYPE%TYPE,
22 p_CC_STATE IGC_CC_ROUTING_CTRLS_ALL.CC_STATE%TYPE,
23 p_CC_CAN_PRPR_APPRV_FLAG IGC_CC_ROUTING_CTRLS_ALL.CC_CAN_PRPR_APPRV_FLAG%TYPE,
24 p_CC_CAN_PRPR_ENCMBR_FLAG IGC_CC_ROUTING_CTRLS_ALL.CC_CAN_PRPR_ENCMBR_FLAG%TYPE,
25 p_wf_approval_itemtype IGC_CC_ROUTING_CTRLS_ALL.WF_APPROVAL_ITEMTYPE%TYPE,
26 p_wf_approval_process IGC_CC_ROUTING_CTRLS_ALL.WF_APPROVAL_PROCESS%TYPE,
27 p_DEFAULT_APPROVAL_PATH_ID IGC_CC_ROUTING_CTRLS_ALL.DEFAULT_APPROVAL_PATH_ID%TYPE,
28 p_LAST_UPDATE_DATE IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATE_DATE%TYPE,
29 p_LAST_UPDATE_LOGIN IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATE_LOGIN%TYPE,
30 p_CREATION_DATE IGC_CC_ROUTING_CTRLS_ALL.CREATION_DATE%TYPE,
31 p_LAST_UPDATED_BY IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATED_BY%TYPE,
32 p_CREATED_BY IGC_CC_ROUTING_CTRLS_ALL.CREATED_BY%TYPE
33 ) IS
34
35 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
36 l_api_version CONSTANT NUMBER := 1.0;
37
38 CURSOR C IS SELECT rowid FROM IGC_CC_ROUTING_CTRLS_ALL
39 WHERE org_id = p_org_id;
40
41 BEGIN
42
43 SAVEPOINT Insert_Row_Pvt ;
44
45 IF NOT FND_API.Compatible_API_Call ( l_api_version,
46 p_api_version,
47 l_api_name,
48 G_PKG_NAME
49 )
50 THEN
51 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
52 END IF;
53
54
55 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
56 FND_MSG_PUB.initialize ;
57 END IF;
58
59 x_return_status := FND_API.G_RET_STS_SUCCESS ;
60
61
62 INSERT INTO IGC_CC_ROUTING_CTRLS_ALL(
63
64 ORG_ID,
65 CC_TYPE,
66 CC_STATE,
67 CC_CAN_PRPR_APPRV_FLAG,
68 CC_CAN_PRPR_ENCMBR_FLAG,
69 WF_APPROVAL_ITEMTYPE,
70 WF_APPROVAL_PROCESS,
71 DEFAULT_APPROVAL_PATH_ID,
72 LAST_UPDATE_DATE,
73 LAST_UPDATE_LOGIN,
74 CREATION_DATE,
75 LAST_UPDATED_BY,
76 CREATED_BY )
77 VALUES (
78 p_ORG_ID,
79 p_CC_TYPE,
80 p_CC_STATE,
81 p_CC_CAN_PRPR_APPRV_FLAG,
82 p_CC_CAN_PRPR_ENCMBR_FLAG,
83 p_WF_APPROVAL_ITEMTYPE,
84 p_WF_APPROVAL_PROCESS,
85 p_DEFAULT_APPROVAL_PATH_ID,
86 p_LAST_UPDATE_DATE,
87 p_LAST_UPDATE_LOGIN,
88 p_CREATION_DATE,
89 p_LAST_UPDATED_BY,
90 p_CREATED_BY
91 );
92
93 OPEN C;
94 FETCH C INTO p_Rowid;
95 if (C%NOTFOUND) then
96 CLOSE C;
97 RAISE FND_API.G_EXC_ERROR ;
98 end if;
99 CLOSE C;
100
101 IF FND_API.To_Boolean ( p_commit ) THEN
102 COMMIT WORK;
103 END iF;
104
105 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
106 p_data => x_msg_data );
107
108 EXCEPTION
109
110 WHEN FND_API.G_EXC_ERROR THEN
111
112 ROLLBACK TO Insert_Row_Pvt ;
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
115 p_data => x_msg_data );
116
117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
118
119 ROLLBACK TO Insert_Row_Pvt ;
120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
121 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
122 p_data => x_msg_data );
123
124 WHEN OTHERS THEN
125
126 ROLLBACK TO Insert_Row_Pvt ;
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128
129 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
130 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
131 l_api_name);
132 END if;
133
134 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
135 p_data => x_msg_data );
136
137
138 END Insert_Row;
139
140
141 PROCEDURE Lock_Row(
142 p_api_version IN NUMBER,
143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
144 p_commit IN VARCHAR2 := FND_API.G_FALSE,
145 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2,
149
150 p_Rowid IN OUT NOCOPY VARCHAR2,
151 p_ORG_ID IGC_CC_ROUTING_CTRLS_ALL.ORG_ID%TYPE,
152 p_CC_TYPE IGC_CC_ROUTING_CTRLS_ALL.CC_TYPE%TYPE,
153 p_CC_STATE IGC_CC_ROUTING_CTRLS_ALL.CC_STATE%TYPE,
154 p_CC_CAN_PRPR_APPRV_FLAG IGC_CC_ROUTING_CTRLS_ALL.CC_CAN_PRPR_APPRV_FLAG%TYPE,
155 p_CC_CAN_PRPR_ENCMBR_FLAG IGC_CC_ROUTING_CTRLS_ALL.CC_CAN_PRPR_ENCMBR_FLAG%TYPE,
156 p_wf_approval_itemtype IGC_CC_ROUTING_CTRLS_ALL.WF_APPROVAL_ITEMTYPE%TYPE,
157 p_wf_approval_process IGC_CC_ROUTING_CTRLS_ALL.WF_APPROVAL_PROCESS%TYPE,
158 p_DEFAULT_APPROVAL_PATH_ID IGC_CC_ROUTING_CTRLS_ALL.DEFAULT_APPROVAL_PATH_ID%TYPE,
159 p_LAST_UPDATE_DATE IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATE_DATE%TYPE,
160 p_LAST_UPDATE_LOGIN IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATE_LOGIN%TYPE,
161 p_CREATION_DATE IGC_CC_ROUTING_CTRLS_ALL.CREATION_DATE%TYPE,
162 p_LAST_UPDATED_BY IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATED_BY%TYPE,
163 p_CREATED_BY IGC_CC_ROUTING_CTRLS_ALL.CREATED_BY%TYPE,
164 p_row_locked OUT NOCOPY VARCHAR2
165 ) IS
166
167 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
168 l_api_version CONSTANT NUMBER := 1.0;
169 Counter NUMBER;
170 CURSOR C IS
171 SELECT *
172 FROM IGC_CC_ROUTING_CTRLS_ALL
173 WHERE rowid = p_Rowid
174 FOR UPDATE of ORG_ID NOWAIT;
175 Recinfo C%ROWTYPE;
176 BEGIN
177
178 SAVEPOINT Lock_Row_Pvt ;
179
180 IF NOT FND_API.Compatible_API_Call ( l_api_version,
181 p_api_version,
182 l_api_name,
183 G_PKG_NAME )
184 THEN
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
186 END IF;
187
188
189 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
190 FND_MSG_PUB.initialize ;
191 END IF;
192
193 x_return_status := FND_API.G_RET_STS_SUCCESS ;
194 p_row_locked := FND_API.G_TRUE ;
195
196
197 OPEN C;
198
199 FETCH C INTO Recinfo;
200 if (C%NOTFOUND) then
201 CLOSE C;
202 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
203 FND_MSG_PUB.Add;
204 RAISE FND_API.G_EXC_ERROR ;
205 end if;
206 CLOSE C;
207 if (
208 (Recinfo.ORG_ID = p_ORG_ID)
209 AND ( (Recinfo.CC_TYPE = p_CC_TYPE)
210 OR ( (Recinfo.CC_TYPE IS NULL)
211 AND (p_CC_TYPE IS NULL)))
212 AND ( (Recinfo.CC_STATE = p_CC_STATE)
213 OR ( (Recinfo.CC_STATE IS NULL)
214 AND (p_CC_STATE IS NULL)))
215 AND ( (Recinfo.CC_CAN_PRPR_APPRV_FLAG = p_CC_CAN_PRPR_APPRV_FLAG)
216 OR ( (Recinfo.CC_CAN_PRPR_APPRV_FLAG IS NULL)
217 AND (p_CC_CAN_PRPR_APPRV_FLAG IS NULL)))
218 AND ( (Recinfo.CC_CAN_PRPR_ENCMBR_FLAG = p_CC_CAN_PRPR_ENCMBR_FLAG)
219 OR ( (Recinfo.CC_CAN_PRPR_ENCMBR_FLAG IS NULL)
220 AND (p_CC_CAN_PRPR_ENCMBR_FLAG IS NULL)))
221 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
222 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
223 AND (p_LAST_UPDATE_DATE IS NULL)))
224 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN )
225 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
226 AND (p_LAST_UPDATE_LOGIN IS NULL)))
227 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE )
228 OR ( (Recinfo.CREATION_DATE IS NULL)
229 AND (p_CREATION_DATE IS NULL)))
230 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY )
231 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
232 AND (p_LAST_UPDATED_BY IS NULL)))
233 AND ( (Recinfo.CREATED_BY = p_CREATED_BY )
234 OR ( (Recinfo.CREATED_BY IS NULL)
235 AND (p_CREATED_BY IS NULL)))
236
237 ) then
238 return;
239 else
240 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
241 FND_MSG_PUB.Add;
242 RAISE FND_API.G_EXC_ERROR ;
243 end if;
244
245 IF FND_API.To_Boolean ( p_commit ) THEN
246 COMMIT WORK;
247 END iF;
248
249 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
250 p_data => x_msg_data );
251
252 EXCEPTION
253
254 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
255
256 ROLLBACK TO Lock_Row_Pvt ;
257 p_row_locked := FND_API.G_FALSE;
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
260 p_data => x_msg_data );
261
262 WHEN FND_API.G_EXC_ERROR THEN
263
264 ROLLBACK TO Lock_Row_Pvt ;
265 x_return_status := FND_API.G_RET_STS_ERROR;
266 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
267 p_data => x_msg_data );
268
269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
270
274 p_data => x_msg_data );
271 ROLLBACK TO Lock_Row_Pvt ;
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
275
276 WHEN OTHERS THEN
277
278 ROLLBACK TO Lock_Row_Pvt ;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280
281 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
282 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
283 l_api_name);
284 END if;
285
286 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
287 p_data => x_msg_data );
288
289 END Lock_Row;
290
291
292 PROCEDURE Update_Row(
293
294 p_api_version IN NUMBER,
295 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
296 p_commit IN VARCHAR2 := FND_API.G_FALSE,
297 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2,
301
302 p_Rowid IN OUT NOCOPY VARCHAR2,
303 p_ORG_ID IGC_CC_ROUTING_CTRLS_ALL.ORG_ID%TYPE,
304 p_CC_TYPE IGC_CC_ROUTING_CTRLS_ALL.CC_TYPE%TYPE,
305 p_CC_STATE IGC_CC_ROUTING_CTRLS_ALL.CC_STATE%TYPE,
306 p_CC_CAN_PRPR_APPRV_FLAG IGC_CC_ROUTING_CTRLS_ALL.CC_CAN_PRPR_APPRV_FLAG%TYPE,
307 p_CC_CAN_PRPR_ENCMBR_FLAG IGC_CC_ROUTING_CTRLS_ALL.CC_CAN_PRPR_ENCMBR_FLAG%TYPE,
308 p_wf_approval_itemtype IGC_CC_ROUTING_CTRLS_ALL.WF_APPROVAL_ITEMTYPE%TYPE,
309 p_wf_approval_process IGC_CC_ROUTING_CTRLS_ALL.WF_APPROVAL_PROCESS%TYPE,
310 p_DEFAULT_APPROVAL_PATH_ID IGC_CC_ROUTING_CTRLS_ALL.DEFAULT_APPROVAL_PATH_ID%TYPE,
311 p_LAST_UPDATE_DATE IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATE_DATE%TYPE,
312 p_LAST_UPDATE_LOGIN IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATE_LOGIN%TYPE,
313 p_CREATION_DATE IGC_CC_ROUTING_CTRLS_ALL.CREATION_DATE%TYPE,
314 p_LAST_UPDATED_BY IGC_CC_ROUTING_CTRLS_ALL.LAST_UPDATED_BY%TYPE,
315 p_CREATED_BY IGC_CC_ROUTING_CTRLS_ALL.CREATED_BY%TYPE
316 ) IS
317
318 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
319 l_api_version CONSTANT NUMBER := 1.0;
320
321 BEGIN
322
323 SAVEPOINT Update_Row_Pvt ;
324
325 IF NOT FND_API.Compatible_API_Call ( l_api_version,
326 p_api_version,
327 l_api_name,
328 G_PKG_NAME )
329 THEN
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
331 END IF;
332
333
334 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
335 FND_MSG_PUB.initialize ;
336 END IF;
337
338 x_return_status := FND_API.G_RET_STS_SUCCESS ;
339
340 UPDATE IGC_CC_ROUTING_CTRLS_ALL
341 SET
342
343 ORG_ID = p_ORG_ID,
344 CC_TYPE = p_CC_TYPE,
345 CC_STATE = p_CC_STATE,
346 CC_CAN_PRPR_APPRV_FLAG = p_CC_CAN_PRPR_APPRV_FLAG,
347 CC_CAN_PRPR_ENCMBR_FLAG = p_CC_CAN_PRPR_ENCMBR_FLAG,
348 WF_APPROVAL_ITEMTYPE = p_wf_approval_itemtype,
349 WF_APPROVAL_PROCESS = p_wf_approval_process,
350 DEFAULT_APPROVAL_PATH_ID = p_DEFAULT_APPROVAL_PATH_ID,
351 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
352 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
353 CREATION_DATE = p_CREATION_DATE,
354 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
355 CREATED_BY = p_CREATED_BY
356 WHERE rowid = p_Rowid;
357 if (SQL%NOTFOUND) then
358 Raise NO_DATA_FOUND;
359 end if;
360
361 IF FND_API.To_Boolean ( p_commit ) THEN
362 COMMIT WORK;
363 END iF;
364
365 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
366 p_data => x_msg_data );
367
368 EXCEPTION
369
370 WHEN FND_API.G_EXC_ERROR THEN
371
372 ROLLBACK TO Update_Row_Pvt ;
373 x_return_status := FND_API.G_RET_STS_ERROR;
374 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
375 p_data => x_msg_data );
376
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378
379 ROLLBACK TO Update_Row_Pvt ;
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
382 p_data => x_msg_data );
383
384 WHEN OTHERS THEN
385
386 ROLLBACK TO Update_Row_Pvt ;
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388
389 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
390 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
391 l_api_name);
392 END if;
393
394 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
395 p_data => x_msg_data );
396
397
398 END Update_Row;
399
400
401 PROCEDURE Delete_Row(
402 p_api_version IN NUMBER,
403 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
404 p_commit IN VARCHAR2 := FND_API.G_FALSE,
405 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_msg_count OUT NOCOPY NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2,
409
410 p_Rowid VARCHAR2) IS
411
412 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
413 l_api_version CONSTANT NUMBER := 1.0;
414
415 BEGIN
416
417 SAVEPOINT Delete_Row_Pvt ;
418
419 IF NOT FND_API.Compatible_API_Call ( l_api_version,
420 p_api_version,
421 l_api_name,
422 G_PKG_NAME )
423 THEN
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
425 END IF;
426
427
428 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
429 FND_MSG_PUB.initialize ;
430 END IF ;
431
432 x_return_status := FND_API.G_RET_STS_SUCCESS ;
433
434
435 DELETE FROM IGC_CC_ROUTING_CTRLS_ALL
436 WHERE rowid = p_Rowid;
437 if (SQL%NOTFOUND) then
438 Raise NO_DATA_FOUND;
439 end if;
440 IF FND_API.To_Boolean ( p_commit ) THEN
441 COMMIT WORK;
442 END iF;
443
444 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
445 p_data => x_msg_data );
446
447 EXCEPTION
448
449 WHEN FND_API.G_EXC_ERROR THEN
450
451 ROLLBACK TO Delete_Row_Pvt ;
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
454 p_data => x_msg_data );
455
456 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
457
458 ROLLBACK TO Delete_Row_Pvt ;
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
460 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
461 p_data => x_msg_data );
462
463 WHEN OTHERS THEN
464
465 ROLLBACK TO Delete_Row_Pvt ;
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467
468 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
469 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
470 l_api_name);
471 END if;
472
473 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
474 p_data => x_msg_data );
475
476
477 END Delete_Row;
478
479
480 END IGC_CC_ROUTING_CTRLS_PKG;