[Home] [Help]
PACKAGE BODY: APPS.PA_STATUS_LISTS_PVT
Source
1 PACKAGE BODY pa_status_lists_pvt AS
2 /* $Header: PACISLVB.pls 120.1 2005/08/19 16:18:39 mwasowic noship $ */
3
4
5 -- --------------------------------------------------------------------------
6 -- FUNCTION
7 -- check_status_list_inuse
8 -- PURPOSE
9 -- This function checks whether the given status List is in use or not.
10 -- If in use it returns 'Y' otherwise it returns 'N'.
11 --
12 -- HISTORY
13 -- 16-JAN-04 rasinha Created
14 --
15 FUNCTION check_status_list_inuse( P_STATUS_LIST_ID NUMBER)
16 RETURN VARCHAR2
17 IS
18 CURSOR ci_val_cur
19 IS
20 SELECT 1
21 FROM pa_obj_status_lists
22 WHERE object_type= 'PA_CI_TYPES'
23 AND STATUS_TYPE = 'CONTROL_ITEM'
24 AND STATUS_LIST_ID =P_STATUS_LIST_ID;
25
26 l_number NUMBER :=0;
27 l_return VARCHAR2(10) := NULL;
28 BEGIN
29 OPEN ci_val_cur;
30 FETCH ci_val_cur INTO l_number;
31 IF ci_val_cur%NOTFOUND THEN
32 l_return := 'N';
33 ELSE
34 l_return := 'Y';
35 END IF;
36 CLOSE ci_val_cur; --Added for bug# 3867679
37 RETURN l_return;
38 END check_status_list_inuse;
39
40
41 -- --------------------------------------------------------------------------
42 -- PROCEDURE
43 -- CreateStatusList
44 -- PURPOSE
45 -- This procedure inserts a row into the pa_status_lists table.
46 --
47 -- HISTORY
48 -- 16-JAN-04 rasinha Created
49 --
50
51 PROCEDURE CreateStatusList (
52 P_RECORD_VERSION_NUMBER IN NUMBER,
53 P_STATUS_LIST_ID IN NUMBER,
54 P_STATUS_TYPE IN VARCHAR2,
55 P_NAME IN VARCHAR2,
56 P_START_DATE_ACTIVE IN DATE,
57 P_END_DATE_ACTIVE IN DATE,
58 P_DESCRIPTION IN VARCHAR2,
59 P_LAST_UPDATE_DATE IN DATE ,
60 P_LAST_UPDATED_BY IN NUMBER ,
61 P_CREATION_DATE IN DATE ,
62 P_CREATED_BY IN NUMBER ,
63 P_LAST_UPDATE_LOGIN IN NUMBER,
64 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
65 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
66 X_MSG_DATA OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
67 ) IS
68
69 BEGIN
70
71 -- Initialize the Error Stack
72 PA_DEBUG.init_err_stack('PA_STATUS_LISTS_PVT.Insert_Row');
73 x_msg_count := 0;
74 x_msg_data := NULL;
75
76 -- Initialize the return status to success
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 PA_STATUS_LISTS_PKG.INSERT_ROW
80 (
81 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
82 X_STATUS_LIST_ID => P_STATUS_LIST_ID,
83 X_STATUS_TYPE => P_STATUS_TYPE,
84 X_NAME => P_NAME,
85 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
86 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
87 X_DESCRIPTION => P_DESCRIPTION,
88 X_CREATION_DATE => P_CREATION_DATE,
89 X_CREATED_BY => P_CREATED_BY,
90 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
92 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
93 );
94 -- Reset the Error Stack
95 PA_DEBUG.reset_err_stack;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 x_msg_count := 1;
100 x_msg_data := substr(SQLERRM,1,240);
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
102 FND_MSG_PUB.add_exc_msg
103 (p_pkg_name => 'PA_STTUS_LISTS_PVT'
104 , p_procedure_name => PA_DEBUG.G_Err_Stack
105 , p_error_text => substr(SQLERRM,1,240));
106 RETURN;
107
108 END CreateStatusList;
109
110
111
112 -- -------------------------------------------------------------------------
113 -- PROCEDURE
114 -- UpdateStatusList
115 -- PURPOSE
116 -- This procedure updates a row in the pa_status_lists table.
117 --
118 -- HISTORY
119 -- 16-JAN-04 rasinha Created
120
121 PROCEDURE UpdateStatusList (
122 P_RECORD_VERSION_NUMBER IN NUMBER,
123 P_STATUS_LIST_ID IN NUMBER,
124 P_STATUS_TYPE IN VARCHAR2,
125 P_NAME IN VARCHAR2,
126 P_START_DATE_ACTIVE IN DATE,
127 P_END_DATE_ACTIVE IN DATE,
128 P_DESCRIPTION IN VARCHAR2,
129 P_LAST_UPDATE_DATE IN DATE ,
130 P_LAST_UPDATED_BY IN NUMBER ,
131 P_LAST_UPDATE_LOGIN IN NUMBER ,
132 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
133 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
134 X_MSG_DATA OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
135 ) IS
136 Invalid_Ret_Status EXCEPTION;
137 l_msg_count NUMBER;
138 l_msg_data VARCHAR2(2000);
139 l_data VARCHAR2(2000);
140 l_msg_index_out NUMBER;
141 l_debug_mode VARCHAR2(1);
142 g_module_name VARCHAR2(100) := 'pa.plsql.PA_STATUS_LISTS';
143 l_debug_level2 CONSTANT NUMBER := 2;
144 l_debug_level3 CONSTANT NUMBER := 3;
145 l_debug_level4 CONSTANT NUMBER := 4;
146 l_debug_level5 CONSTANT NUMBER := 5;
147 BEGIN
148 savepoint UpdateSL;
149 -- Initialize the Error Stack
150 PA_DEBUG.init_err_stack('PA_STATUS_LISTS_PVT.Update_Row');
151 x_msg_count := 0;
152 x_msg_data := NULL;
153
154 -- Initialize the return status to success
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
157 IF l_debug_mode = 'Y' THEN
158 pa_debug.g_err_stage:= 'Validating input parameters';
159 pa_debug.write(g_module_name,pa_debug.g_err_stage,
160 l_debug_level3);
161 END IF;
162
163 IF l_debug_mode = 'Y' THEN
164 pa_debug.g_err_stage:= 'P_STATUS_LIST_ID = '|| P_STATUS_LIST_ID;
165 pa_debug.write(g_module_name,pa_debug.g_err_stage,
166 l_debug_level5);
167 pa_debug.g_err_stage:= 'P_STATUS_TYPE = '|| P_STATUS_TYPE;
168 pa_debug.write(g_module_name,pa_debug.g_err_stage,
169 l_debug_level5);
170
171 END IF;
172 IF l_debug_mode = 'Y' THEN
173 pa_debug.g_err_stage:= 'about to call lock row method';
174 pa_debug.write(g_module_name,pa_debug.g_err_stage,
175 l_debug_level3);
176 END IF;
177 PA_STATUS_LISTS_PKG.LOCK_ROW
178 (
179 X_STATUS_LIST_ID => P_STATUS_LIST_ID,
180 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER
181 );
182 IF l_debug_mode = 'Y' THEN
183 pa_debug.g_err_stage:= 'lock row method called';
184 pa_debug.write(g_module_name,pa_debug.g_err_stage,
185 l_debug_level3);
186 END IF;
187 l_msg_count := FND_MSG_PUB.count_msg;
188 if(l_msg_count<>0) then
189 Raise Invalid_Ret_Status;
190 end if;
191 IF l_debug_mode = 'Y' THEN
192 pa_debug.g_err_stage:= 'about to call PA_STATUS_LISTS_PKG.UPDATE_ROW';
193 pa_debug.write(g_module_name,pa_debug.g_err_stage,
194 l_debug_level3);
195 END IF;
196 PA_STATUS_LISTS_PKG.UPDATE_ROW
197 (X_STATUS_LIST_ID => P_STATUS_LIST_ID,
198 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
199 X_STATUS_TYPE => P_STATUS_TYPE,
200 X_NAME => P_NAME,
201 X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
202 X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
203 X_DESCRIPTION => P_DESCRIPTION,
204 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
205 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
206 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN
207 );
208
209 -- Reset the Error Stack
210 PA_DEBUG.reset_err_stack;
211
212 EXCEPTION
213 WHEN Invalid_Ret_Status THEN
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 l_msg_count := FND_MSG_PUB.count_msg;
216
217 IF l_msg_count = 1 and x_msg_data IS NULL THEN
218 PA_INTERFACE_UTILS_PUB.get_messages
219 (p_encoded => FND_API.G_TRUE
220 ,p_msg_index => 1
221 ,p_msg_count => l_msg_count
222 ,p_msg_data => l_msg_data
223 ,p_data => l_data
224 ,p_msg_index_out => l_msg_index_out);
225 x_msg_data := l_data;
226 x_msg_count := l_msg_count;
227 ELSE
228 x_msg_count := l_msg_count;
229 END IF;
230 rollback to UpdateSL;
231 RETURN;
232 WHEN OTHERS THEN
233 x_msg_count := 1;
234 x_msg_data := substr(SQLERRM,1,240);
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 FND_MSG_PUB.add_exc_msg
237 (p_pkg_name => 'PA_STATUS_LISTS_PVT'
238 , p_procedure_name => PA_DEBUG.G_Err_Stack
239 , p_error_text => substr(SQLERRM,1,240));
240 rollback to UpdateSL;
241 RAISE;
242
243 END UpdateStatusList;
244
245
246 -- ---------------------------------------------------------------------
247 -- PROCEDURE
248 -- DeleteStatusList
249 -- PURPOSE
250 -- This procedure deletes a row in the pa_status_lists table.
251 --
252 -- If a row is deleted, this API returns (S)uccess for the
253 -- x_return_status.
254 --
255 -- HISTORY
256 -- 16-JAN-04 rasinha Created
257 --
258
259
260 PROCEDURE DeleteStatusList (
261 P_STATUS_LIST_ID IN NUMBER,
262 P_RECORD_VERSION_NUMBER IN NUMBER,
263 X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
264 X_MSG_COUNT OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
265 X_MSG_DATA OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
266 )
267 IS
268 Invalid_Ret_Status EXCEPTION;
269 l_msg_count NUMBER;
270 l_msg_data VARCHAR2(2000);
271 l_data VARCHAR2(2000);
272 l_msg_index_out NUMBER;
273 l_debug_mode VARCHAR2(1);
274
275 BEGIN
276 savepoint DeleteSL;
277
278
279 -- Initialize the Error Stack
280 PA_DEBUG.init_err_stack('PA_STATUS_LISTS_PVT.Delete_Row');
281 x_msg_count := 0;
282 x_msg_data := NULL;
283
284 -- Initialize the return status to success
285 x_return_status := FND_API.G_RET_STS_SUCCESS;
286
287 PA_STATUS_LISTS_PKG.LOCK_ROW
288 (
289 X_STATUS_LIST_ID => P_STATUS_LIST_ID,
290 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER
291 );
292
293 l_msg_count := FND_MSG_PUB.count_msg;
294 if(l_msg_count<>0) then
295 Raise Invalid_Ret_Status;
296 end if;
297
298 -- Delete Role
299 PA_STATUS_LISTS_PKG.DELETE_ROW
300 ( X_STATUS_LIST_ID => P_STATUS_LIST_ID,
301 X_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER
302 );
303
304 -- Reset the Error Stack
305 PA_DEBUG.reset_err_stack;
306
307 EXCEPTION
308 WHEN Invalid_Ret_Status THEN
309 x_return_status := FND_API.G_RET_STS_ERROR;
310 l_msg_count := FND_MSG_PUB.count_msg;
311
312 IF l_msg_count = 1 and x_msg_data IS NULL THEN
313 PA_INTERFACE_UTILS_PUB.get_messages
314 (p_encoded => FND_API.G_TRUE
315 ,p_msg_index => 1
316 ,p_msg_count => l_msg_count
317 ,p_msg_data => l_msg_data
318 ,p_data => l_data
319 ,p_msg_index_out => l_msg_index_out);
320 x_msg_data := l_data;
321 x_msg_count := l_msg_count;
322 ELSE
323 x_msg_count := l_msg_count;
324 END IF;
325 rollback to DeleteSL;
326 RETURN;
327 WHEN OTHERS THEN
328 x_msg_count := 1;
329 x_msg_data := substr(SQLERRM,1,240);
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331 FND_MSG_PUB.add_exc_msg
332 (p_pkg_name => 'PA_STATUS_LISTS_PVT'
333 , p_procedure_name => PA_DEBUG.G_Err_Stack
334 , p_error_text => substr(SQLERRM,1,240));
335 rollback to DeleteSL;
336 RAISE;
337
338 END DeleteStatusList;
339
340
341 END pa_status_lists_pvt;