[Home] [Help]
PACKAGE BODY: APPS.BIS_RESPONSIBILITY_PUB
Source
1 PACKAGE BODY BIS_RESPONSIBILITY_PUB AS
2 /* $Header: BISPRSPB.pls 120.0 2005/05/31 18:09:32 appldev noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISPRSPB.pls |
10 REM | PACKAGE |
11 REM | BIS_RESPONSIBILITY_PUB |
12 REM | DESCRIPTION |
13 REM | Module: Private package that calls the FND packages to |
14 REM | insert records in the FND Responsibility table |
15 REM | |
16 REM | NOTES |
17 REM | 07-MAR-2005 KRISHNA Created. |
18 REM +=======================================================================+
19 */
20
21 PROCEDURE UPDATE_ROW(
22 p_application_id IN NUMBER
23 , p_responsibility_id IN NUMBER
24 , p_menu_id IN NUMBER
25 , x_return_status OUT NOCOPY VARCHAR2
26 , x_msg_count OUT NOCOPY NUMBER
27 , x_msg_data OUT NOCOPY VARCHAR2
28 ) IS
29
30 l_responsibility_rec Responsibility_Rec_Type;
31
32 CURSOR cResponsibility IS
33 SELECT web_host_name,
34 web_agent_name,
35 data_group_application_id,
36 data_group_id,
37 start_date,
38 end_date,
39 group_application_id,
40 request_group_id,
41 version ,
42 responsibility_key ,
43 responsibility_name,
44 description
45 FROM fnd_responsibility_vl
46 WHERE application_id = p_application_id
47 AND responsibility_id = p_responsibility_id;
48
49 BEGIN
50
51 IF cResponsibility%ISOPEN THEN
52 CLOSE cResponsibility;
53 END IF;
54
55 OPEN cResponsibility;
56 FETCH cResponsibility INTO
57 l_responsibility_rec.web_host_name,
58 l_responsibility_rec.web_agent_name,
59 l_responsibility_rec.data_group_application_id,
60 l_responsibility_rec.data_group_id,
61 l_responsibility_rec.start_date,
62 l_responsibility_rec.end_date,
63 l_responsibility_rec.group_application_id,
64 l_responsibility_rec.request_group_id,
65 l_responsibility_rec.version,
66 l_responsibility_rec.responsibility_key,
67 l_responsibility_rec.responsibility_name,
68 l_responsibility_rec.description;
69 CLOSE cResponsibility;
70 --dbms_output.put_line( 'just before calling update row is');
71 FND_RESPONSIBILITY_PKG.UPDATE_ROW(
72 X_RESPONSIBILITY_ID => p_responsibility_id
73 , X_APPLICATION_ID => p_application_id
74 , X_WEB_HOST_NAME => l_responsibility_rec.web_host_name
75 , X_WEB_AGENT_NAME => l_responsibility_rec.web_agent_name
76 , X_DATA_GROUP_APPLICATION_ID => l_responsibility_rec.data_group_application_id
77 , X_DATA_GROUP_ID => l_responsibility_rec.data_group_id
78 , X_MENU_ID => p_menu_id
79 , X_START_DATE => l_responsibility_rec.start_date
80 , X_END_DATE => l_responsibility_rec.end_date
81 , X_GROUP_APPLICATION_ID => l_responsibility_rec.group_application_id
82 , X_REQUEST_GROUP_ID => l_responsibility_rec.request_group_id
83 , X_VERSION => l_responsibility_rec.version
84 , X_RESPONSIBILITY_KEY => l_responsibility_rec.responsibility_key
85 , X_RESPONSIBILITY_NAME => l_responsibility_rec.responsibility_name
86 , X_DESCRIPTION => l_responsibility_rec.description
87 , X_LAST_UPDATE_DATE => sysdate
88 , X_LAST_UPDATED_BY => fnd_global.user_id
89 , X_LAST_UPDATE_LOGIN => fnd_global.user_id
90 );
91
92 EXCEPTION
93 WHEN FND_API.G_EXC_ERROR THEN
94 IF(cResponsibility%ISOPEN) THEN
95 CLOSE cResponsibility;
96 END IF;
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
99 ,p_count => x_msg_count
100 ,p_data => x_msg_data);
101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
102 IF(cResponsibility%ISOPEN) THEN
103 CLOSE cResponsibility;
104 END IF;
105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
106 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
107 ,p_count => x_msg_count
108 ,p_data => x_msg_data);
109 WHEN NO_DATA_FOUND THEN
110 IF(cResponsibility%ISOPEN) THEN
111 CLOSE cResponsibility;
112 END IF;
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
115 ,p_count => x_msg_count
116 ,p_data => x_msg_data);
117 WHEN OTHERS THEN
118 IF(cResponsibility%ISOPEN) THEN
119 CLOSE cResponsibility;
120 END IF;
121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122
123 FND_MSG_PUB.Count_And_Get( p_encoded => 'F'
124 ,p_count => x_msg_count
125 ,p_data => x_msg_data);
126 IF (x_msg_data IS NULL) THEN
127 x_msg_data := SQLERRM;
128 END IF;
129
130 END UPDATE_ROW;
131
132
133 PROCEDURE LOCK_ROW
134 ( p_application_id IN NUMBER
135 , p_responsibility_id IN NUMBER
136 , p_last_update_date IN DATE
137 ) IS
138
139 l_last_update_date DATE;
140
141 CURSOR cResponsibility IS
142 SELECT last_update_date
143 FROM fnd_responsibility
144 WHERE responsibility_id = p_responsibility_id
145 AND application_id = p_application_id
146 FOR UPDATE OF menu_id NOWAIT;
147
148 BEGIN
149
150 fnd_msg_pub.initialize;
151
152 SAVEPOINT SP_LOCK_ROW;
153
154 IF cResponsibility%ISOPEN THEN
155 CLOSE cResponsibility;
156 END IF;
157 OPEN cResponsibility;
158 FETCH cResponsibility INTO l_last_update_date;
159
160 IF (cResponsibility%NOTFOUND) THEN
161 FND_MESSAGE.SET_NAME('BIS','BIS_RESP_DELETED_ERROR');
162 FND_MSG_PUB.ADD;
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 IF p_last_update_date IS NOT NULL THEN
167 IF p_last_update_date <> l_last_update_date THEN
168 FND_MESSAGE.SET_NAME('BIS','BIS_RESP_CHANGED_ERROR');
169 FND_MSG_PUB.ADD;
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172 END IF;
173
174 ROLLBACK TO SP_LOCK_ROW;
175 CLOSE cResponsibility;
176
177 EXCEPTION
178 WHEN FND_API.G_EXC_ERROR
179 THEN NULL;
180 WHEN OTHERS THEN
181 IF(cResponsibility%ISOPEN) THEN
182 CLOSE cResponsibility;
183 END IF;
184 ROLLBACK TO SP_LOCK_ROW;
185 FND_MESSAGE.SET_NAME('BIS','BIS_RESP_LOCKED_ERROR');
186 FND_MSG_PUB.ADD;
187 RAISE FND_API.G_EXC_ERROR;
188 END LOCK_ROW;
189
190 END BIS_RESPONSIBILITY_PUB;