[Home] [Help]
PACKAGE BODY: APPS.PA_ROLE_LISTS_PVT
Source
1 package body PA_ROLE_LISTS_PVT AS
2 /* $Header: PARLTPVB.pls 120.1 2005/08/19 16:55:54 mwasowic noship $ */
3 ------------------------------------------------------------------------------
4 procedure INSERT_ROW (
5 P_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 P_ROLE_LIST_ID NUMBER,
7 P_NAME VARCHAR2,
8 P_START_DATE_ACTIVE DATE,
9 P_END_DATE_ACTIVE DATE,
10 P_DESCRIPTION VARCHAR2,
11 P_ATTRIBUTE_CATEGORY VARCHAR2,
12 P_ATTRIBUTE1 VARCHAR2,
13 P_ATTRIBUTE2 VARCHAR2,
14 P_ATTRIBUTE3 VARCHAR2,
15 P_ATTRIBUTE4 VARCHAR2,
16 P_ATTRIBUTE5 VARCHAR2,
17 P_ATTRIBUTE6 VARCHAR2,
18 P_ATTRIBUTE7 VARCHAR2,
19 P_ATTRIBUTE8 VARCHAR2,
20 P_ATTRIBUTE9 VARCHAR2,
21 P_ATTRIBUTE10 VARCHAR2,
22 P_ATTRIBUTE11 VARCHAR2,
23 P_ATTRIBUTE12 VARCHAR2,
24 P_ATTRIBUTE13 VARCHAR2,
25 P_ATTRIBUTE14 VARCHAR2,
26 P_ATTRIBUTE15 VARCHAR2,
27 P_CREATION_DATE DATE,
28 P_CREATED_BY NUMBER,
29 P_LAST_UPDATE_DATE DATE,
30 P_LAST_UPDATED_BY NUMBER,
31 P_LAST_UPDATE_LOGIN NUMBER,
32 X_RETURN_STATUS out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
33 X_MSG_COUNT out NOCOPY NUMBER, --File.Sql.39 bug 4440895
34 X_MSG_DATA out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
35 ) is
36 begin
37 PA_DEBUG.init_err_stack('PA_ROLE_LISTS_PVT.INSERT_ROW');
38
39 -- Check for duplicate row list names
40 PA_ROLE_UTILS.CHECK_DUP_ROLE_LIST_NAME(
41 p_name,
42 X_RETURN_STATUS,
43 X_MSG_DATA);
44
45 if X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS then
46 --call table handler to insert the row
47 PA_ROLE_LISTS_PKG.INSERT_ROW(
48 P_ROWID,
49 P_ROLE_LIST_ID,
50 P_NAME,
51 P_START_DATE_ACTIVE,
52 P_END_DATE_ACTIVE,
53 P_DESCRIPTION,
54 P_ATTRIBUTE_CATEGORY,
55 P_ATTRIBUTE1,
56 P_ATTRIBUTE2,
57 P_ATTRIBUTE3,
58 P_ATTRIBUTE4,
59 P_ATTRIBUTE5,
60 P_ATTRIBUTE6,
61 P_ATTRIBUTE7,
62 P_ATTRIBUTE8,
63 P_ATTRIBUTE9,
64 P_ATTRIBUTE10,
65 P_ATTRIBUTE11,
66 P_ATTRIBUTE12,
67 P_ATTRIBUTE13,
68 P_ATTRIBUTE14,
69 P_ATTRIBUTE15,
70 P_CREATION_DATE,
71 P_CREATED_BY,
72 P_LAST_UPDATE_DATE,
73 P_LAST_UPDATED_BY,
74 P_LAST_UPDATE_LOGIN);
75
76 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
77 X_MSG_COUNT := 0;
78 X_MSG_DATA := NULL;
79 PA_DEBUG.reset_err_stack;
80 else
81 X_MSG_COUNT := 1;
82 FND_MSG_PUB.add_exc_msg(
83 p_pkg_name => 'PA_ROLE_LISTS_PVT',
84 p_procedure_name => PA_DEBUG.G_err_stack,
85 p_error_text => X_MSG_DATA);
86 end if;
87
88 exception
89 when others then
90 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
91 X_MSG_COUNT := 1;
92 X_MSG_DATA := substr(SQLERRM, 1, 240);
93 FND_MSG_PUB.add_exc_msg(
94 p_pkg_name => 'PA_ROLE_LISTS_PVT',
95 p_procedure_name => PA_DEBUG.G_err_stack,
96 p_error_text => X_MSG_DATA);
97 end;
98
99 ------------------------------------------------------------------------------
100 procedure LOCK_ROW (
101 P_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
102 P_RECORD_VERSION_NUMBER NUMBER,
103 X_RETURN_STATUS out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
104 X_MSG_COUNT out NOCOPY NUMBER, --File.Sql.39 bug 4440895
105 X_MSG_DATA out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
106 ) is
107 begin
108 PA_DEBUG.init_err_stack('PA_ROLE_LISTS_PVT.LOCK_ROW');
109 --any validation to be added here ?
110 --call table handler to lock the row
111 PA_ROLE_LISTS_PKG.LOCK_ROW(
112 P_ROWID,
113 P_RECORD_VERSION_NUMBER);
114
115 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
116 X_MSG_COUNT := 0;
117 X_MSG_DATA := NULL;
118 PA_DEBUG.reset_err_stack;
119
120 exception
121 when others then
122 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
123 X_MSG_COUNT := 1;
124 X_MSG_DATA := substr(SQLERRM, 1, 240);
125 FND_MSG_PUB.add_exc_msg(
126 p_pkg_name => 'PA_ROLE_LISTS_PVT',
127 p_procedure_name => PA_DEBUG.G_err_stack,
128 p_error_text => X_MSG_DATA);
129 end;
130
131 ------------------------------------------------------------------------------
132 procedure UPDATE_ROW (
133 P_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
134 P_ROLE_LIST_ID NUMBER,
135 P_NAME VARCHAR2,
136 P_START_DATE_ACTIVE DATE,
137 P_END_DATE_ACTIVE DATE,
138 P_DESCRIPTION VARCHAR2,
139 P_ATTRIBUTE_CATEGORY VARCHAR2,
140 P_ATTRIBUTE1 VARCHAR2,
141 P_ATTRIBUTE2 VARCHAR2,
142 P_ATTRIBUTE3 VARCHAR2,
143 P_ATTRIBUTE4 VARCHAR2,
144 P_ATTRIBUTE5 VARCHAR2,
145 P_ATTRIBUTE6 VARCHAR2,
146 P_ATTRIBUTE7 VARCHAR2,
147 P_ATTRIBUTE8 VARCHAR2,
148 P_ATTRIBUTE9 VARCHAR2,
149 P_ATTRIBUTE10 VARCHAR2,
150 P_ATTRIBUTE11 VARCHAR2,
151 P_ATTRIBUTE12 VARCHAR2,
152 P_ATTRIBUTE13 VARCHAR2,
153 P_ATTRIBUTE14 VARCHAR2,
154 P_ATTRIBUTE15 VARCHAR2,
155 P_CREATION_DATE DATE,
156 P_CREATED_BY NUMBER,
157 P_LAST_UPDATE_DATE DATE,
158 P_LAST_UPDATED_BY NUMBER,
159 P_LAST_UPDATE_LOGIN NUMBER,
160 X_RETURN_STATUS out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
161 X_MSG_COUNT out NOCOPY NUMBER, --File.Sql.39 bug 4440895
162 X_MSG_DATA out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
163 ) is
164 v_name PA_ROLE_LISTS.name%TYPE;
165 begin
166 PA_DEBUG.init_err_stack('PA_ROLE_LISTS_PVT.UPDATE_ROW');
167
168 select name
169 into v_name
170 from pa_role_lists
171 where rowid = p_rowid;
172
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174
175 if v_name <> p_name then
176 -- Check for duplicate row list names
177 PA_ROLE_UTILS.CHECK_DUP_ROLE_LIST_NAME(
178 p_name,
179 X_RETURN_STATUS,
180 X_MSG_DATA);
181 end if;
182
183 if X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS then
184 --call table handler to update the row
185 PA_ROLE_LISTS_PKG.UPDATE_ROW(
186 P_ROWID,
187 P_ROLE_LIST_ID,
188 P_NAME,
189 P_START_DATE_ACTIVE,
190 P_END_DATE_ACTIVE,
191 P_DESCRIPTION,
192 P_ATTRIBUTE_CATEGORY,
193 P_ATTRIBUTE1,
194 P_ATTRIBUTE2,
195 P_ATTRIBUTE3,
196 P_ATTRIBUTE4,
197 P_ATTRIBUTE5,
198 P_ATTRIBUTE6,
199 P_ATTRIBUTE7,
200 P_ATTRIBUTE8,
201 P_ATTRIBUTE9,
202 P_ATTRIBUTE10,
203 P_ATTRIBUTE11,
204 P_ATTRIBUTE12,
205 P_ATTRIBUTE13,
206 P_ATTRIBUTE14,
207 P_ATTRIBUTE15,
208 P_CREATION_DATE,
209 P_CREATED_BY,
210 P_LAST_UPDATE_DATE,
211 P_LAST_UPDATED_BY,
212 P_LAST_UPDATE_LOGIN);
213
214 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
215 X_MSG_COUNT := 0;
216 X_MSG_DATA := NULL;
217 PA_DEBUG.reset_err_stack;
218 else
219 X_MSG_COUNT := 1;
220 FND_MSG_PUB.add_exc_msg(
221 p_pkg_name => 'PA_ROLE_LISTS_PVT',
222 p_procedure_name => PA_DEBUG.G_err_stack,
223 p_error_text => X_MSG_DATA);
224 end if;
225
226 exception
227 when others then
228 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
229 X_MSG_COUNT := 1;
230 X_MSG_DATA := substr(SQLERRM, 1, 240);
231 FND_MSG_PUB.add_exc_msg(
232 p_pkg_name => 'PA_ROLE_LISTS_PVT',
233 p_procedure_name => PA_DEBUG.G_err_stack,
234 p_error_text => X_MSG_DATA);
235 end;
236
237 ------------------------------------------------------------------------------
238 procedure DELETE_ROW (
239 P_ROWID VARCHAR2,
240 X_RETURN_STATUS out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
241 X_MSG_COUNT out NOCOPY NUMBER, --File.Sql.39 bug 4440895
242 X_MSG_DATA out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
243 ) is
244 v_role_list_id number;
245 begin
246 PA_DEBUG.init_err_stack('PA_ROLE_LISTS_PVT.DELETE_ROW');
247 --validate if the role list can be deleted or not
248 select role_list_id
249 into v_role_list_id
250 from pa_role_lists
251 where rowid = P_ROWID;
252
253 PA_ROLE_UTILS.CHECK_DELETE_ROLE_LIST_OK(
254 v_role_list_id,
255 X_RETURN_STATUS,
256 X_MSG_DATA);
257
258 if X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS then
259 ----call the table handler to delete role list
260 PA_ROLE_LISTS_PKG.DELETE_ROW(P_ROWID);
261
262 delete from PA_ROLE_LIST_MEMBERS
263 where ROLE_LIST_ID = v_role_list_id;
264
265 X_MSG_COUNT := 0;
266 X_MSG_DATA := NULL;
267 PA_DEBUG.reset_err_stack;
268
269 else
270 X_MSG_COUNT := 1;
271 FND_MSG_PUB.add_exc_msg(
272 p_pkg_name => 'PA_ROLE_LISTS_PVT',
273 p_procedure_name => PA_DEBUG.G_err_stack,
274 p_error_text => X_MSG_DATA);
275 end if;
276
277 exception
278 when others then
279 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
280 X_MSG_COUNT := 1;
281 X_MSG_DATA := substr(SQLERRM, 1, 240);
282 FND_MSG_PUB.add_exc_msg(
283 p_pkg_name => 'PA_ROLE_LISTS_PVT',
284 p_procedure_name => PA_DEBUG.G_err_stack,
285 p_error_text => X_MSG_DATA);
286 end;
287
288 end PA_ROLE_LISTS_PVT;