[Home] [Help]
PACKAGE BODY: APPS.CSP_PC_FORM_PICKHEADERS
Source
1 PACKAGE BODY CSP_PC_FORM_PICKHEADERS AS
2 /*$Header: cspgtphb.pls 120.0 2005/05/25 11:31:02 appldev noship $*/
3 -- Start of Comments
4 -- Package name : CSP_PC_FORM_PICKHEADERS
5 -- Purpose : A wrapper to prepare data to call the update, delete and insert procedures of the
6 -- CSP_picklist_header_PVT.
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- --------- ------ ------------------------------------------
10 -- klou 02/09/00 Add standrd messages.
11 -- klou 01/12/00 Replace change AS_UTILITY call with JTF_PLSQL_API.
12 -- klou 17/12/99 Create.
13 --
14 -- NOTE :
15 -- End of Comments
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PC_FORM_PICKHEADERS';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtphb.pls';
19 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
20 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
21
22 PROCEDURE Validate_And_Write (
23 P_Api_Version_Number IN NUMBER,
24 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
25 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
27 p_action_code IN NUMBER,
28 px_picklist_header_id IN OUT NOCOPY NUMBER,
29 p_CREATED_BY IN NUMBER := FND_API.G_MISS_NUM,
30 p_CREATION_DATE IN DATE := FND_API.G_MISS_DATE,
31 p_LAST_UPDATED_BY IN NUMBER := FND_API.G_MISS_NUM,
32 p_LAST_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
33 p_LAST_UPDATE_LOGIN IN NUMBER := FND_API.G_MISS_NUM,
34 p_ORGANIZATION_ID IN NUMBER := FND_API.G_MISS_NUM,
35 p_PICKLIST_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR,
36 p_PICKLIST_STATUS IN VARCHAR2 := FND_API.G_MISS_CHAR,
37 p_DATE_CREATED IN DATE := FND_API.G_MISS_DATE,
38 p_DATE_CONFIRMED IN DATE := FND_API.G_MISS_DATE,
39 p_ATTRIBUTE_CATEGORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
40 p_ATTRIBUTE1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
41 p_ATTRIBUTE2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
42 p_ATTRIBUTE3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
43 p_ATTRIBUTE4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
44 p_ATTRIBUTE5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
45 p_ATTRIBUTE6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
46 p_ATTRIBUTE7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
47 p_ATTRIBUTE8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
48 p_ATTRIBUTE9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
49 p_ATTRIBUTE10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
50 p_ATTRIBUTE11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
51 p_ATTRIBUTE12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
52 p_ATTRIBUTE13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
53 p_ATTRIBUTE14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
54 p_ATTRIBUTE15 IN VARCHAR2,
55 X_Return_Status OUT NOCOPY VARCHAR2,
56 X_Msg_Count OUT NOCOPY NUMBER,
57 X_Msg_Data OUT NOCOPY VARCHAR2
58 )
59 IS
60 l_api_version_number CONSTANT NUMBER := 1.0;
61 l_api_name CONSTANT VARCHAR2(20) := 'Validate_And_Write';
62 l_msg_data VARCHAR2(300);
63 l_check_existence NUMBER := 0;
64 l_check_var VARCHAR2(30);
65 l_return_status VARCHAR2(1);
66 l_msg_count NUMBER := 0;
67 l_picklist_header_id NUMBER;
68 l_commit VARCHAR2(1) := FND_API.G_FALSE;
69 -- for inserting data, the validation_level should be none
70 -- because we do not want to call the core apps standard validations.
71 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
72 l_creation_date DATE := p_creation_date;
73 l_last_update_date DATE := p_last_update_date;
74 l_PICK_HEADER_Rec CSP_PICKLIST_HEADER_PVT.PICK_HEADER_Rec_Type;
75 EXCP_USER_DEFINED EXCEPTION;
76 l_created_by NUMBER := p_created_by;
77 l_last_update_login NUMBER := p_last_update_login;
78 l_last_updated_by NUMBER := p_last_updated_by;
79 Cursor l_Get_Creation_Date_Csr Is
80 Select creation_date
81 From csp_picklist_headers
82 Where picklist_header_id = px_PICKLIST_HEADER_ID;
83
84 BEGIN
85 SAVEPOINT Validate_And_Write_PUB;
86 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
87 -- initialize message list
88 FND_MSG_PUB.initialize;
89 END IF;
90
91 -- Standard call to check for call compatibility.
92 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
93 p_api_version_number,
94 l_api_name,
95 G_PKG_NAME)
96 THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100
101 IF nvl(p_action_code, fnd_api.g_miss_num) NOT IN (0, 1, 2) THEN
102 fnd_message.set_name ('INV', 'INV-INVALID ACTION');
103 fnd_message.set_token ('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
104 fnd_msg_pub.add;
105 RAISE EXCP_USER_DEFINED;
106 END IF;
107
108 IF p_action_code = 0 THEN
109 IF nvl(px_picklist_header_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
110 -- check whethter the px_picklist_header_id already exists.
111 BEGIN
112 SELECT picklist_header_id INTO l_check_existence
113 FROM CSP_PICKLIST_HEADERS
114 WHERE picklist_header_id = px_picklist_header_id
115 AND organization_id = p_organization_id;
116
117 fnd_message.set_name ('CSP', 'CSP_DUPLICATE_RECORD');
118 fnd_msg_pub.add;
119 -- l_msg_data := 'Header ID '||px_picklist_header_id||' already exists. It is not allowed to create a new record again with this ID.';
120 RAISE EXCP_USER_DEFINED;
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN -- This is what we want!
123 NULL;
124 WHEN OTHERS THEN
125 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
126 fnd_message.set_token('ERR_FIELD', 'px_picklist_header_id', FALSE);
127 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
128 fnd_message.set_token('TABLE', 'CSP_PICKLIST_HEADERS', FALSE);
129 FND_MSG_PUB.ADD;
130 RAISE EXCP_USER_DEFINED;
131 END;
132 END IF;
133
134 -- Validate the status against the lookup codes.
135 IF nvl(p_PICKLIST_STATUS, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
136 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
137 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_picklist_status', FALSE);
138 FND_MSG_PUB.ADD;
139 RAISE EXCP_USER_DEFINED;
140 ELSE
141 BEGIN
142 select distinct lookup_code into l_check_var
143 from fnd_lookups
144 where lookup_type = 'CSP_PICKLIST_STATUS'
145 and lookup_code = p_PICKLIST_STATUS;
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 fnd_message.set_name('CSP', 'CSP_INVALID_PICK_STATUS');
149 fnd_message.set_token('PICKLIST_STATUS', p_PICKLIST_STATUS, FALSE);
150 fnd_msg_pub.add;
151 RAISE EXCP_USER_DEFINED;
152 WHEN OTHERS THEN
153 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
154 fnd_message.set_token('ERR_FIELD', 'px_picklist_status', FALSE);
155 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
156 fnd_message.set_token('TABLE', 'FND_LOOKUPS', FALSE);
157 FND_MSG_PUB.ADD;
158 RAISE EXCP_USER_DEFINED;
159 END;
160 END IF;
161
162 --validation of organization_id
163 IF nvl(p_organization_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
164 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
165 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', FALSE);
166 FND_MSG_PUB.ADD;
167 RAISE EXCP_USER_DEFINED;
168 ELSE
169 BEGIN
170 select organization_id into l_check_existence
171 from mtl_parameters
172 where organization_id = p_organization_id;
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
176 FND_MSG_PUB.ADD;
177 RAISE EXCP_USER_DEFINED;
178 WHEN OTHERS THEN
179 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
180 fnd_message.set_token('ERR_FIELD', 'p_organization_id', FALSE);
181 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
182 fnd_message.set_token('TABLE', 'ORG_ORGANIZATION_DEFINITIONS', FALSE);
183 FND_MSG_PUB.ADD;
184 RAISE EXCP_USER_DEFINED;
185 END;
186 END IF;
187
188 -- check creation_date and last_update_date
189 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
190 l_creation_date := sysdate;
191 END IF;
192
193 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
194 l_last_update_date := sysdate;
195 END IF;
196
197 IF nvl(l_created_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
198 l_created_by := g_user_id;
199 END IF;
200
201 IF nvl(l_last_update_login, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
202 l_last_update_login := g_login_id;
203 END IF;
204
205 IF nvl(l_last_updated_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
206 l_last_updated_by := g_user_id;
207 END IF;
208
209
210 ELSIF p_action_code = 1 THEN
211 -- Picklist header id is required for update.
212 -- We do need to take care the case which the user updates the picklist_header_id to a record
213 -- which already exists because the picklist_header_id is a primary key.
214 IF nvl(px_picklist_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
215 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
216 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_picklist_header_id', FALSE);
217 FND_MSG_PUB.ADD;
218 RAISE EXCP_USER_DEFINED;
219 ELSE
220 BEGIN
221 select picklist_header_id into l_check_existence
222 from csp_picklist_headers
223 where picklist_header_id = px_picklist_header_id;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 fnd_message.set_name('CSP', 'CSP_INVALID_PICKLIST_HEADER');
227 fnd_message.set_token ('HEADER_ID', to_char(px_picklist_header_id), FALSE);
228 FND_MSG_PUB.ADD;
229 RAISE EXCP_USER_DEFINED;
230 WHEN OTHERS THEN
231 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
232 fnd_message.set_token('ERR_FIELD', 'px_picklist_header_id', FALSE);
233 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
234 fnd_message.set_token('TABLE', 'CSP_PICKLIST_HEADERS', FALSE);
235 FND_MSG_PUB.ADD;
236 RAISE EXCP_USER_DEFINED;
237 END;
238 END IF;
239
240 -- Validate the status against the lookup codes.
241 IF nvl(p_PICKLIST_STATUS, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
242 BEGIN
243 select distinct lookup_code into l_check_var
244 from fnd_lookups
245 where lookup_type = 'CSP_PICKLIST_STATUS'
246 and lookup_code = p_PICKLIST_STATUS;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 fnd_message.set_name('CSP', 'CSP_INVALID_PICK_STATUS');
250 fnd_message.set_token('PICKLIST_STATUS', p_PICKLIST_STATUS, FALSE);
251 fnd_msg_pub.add;
252 RAISE EXCP_USER_DEFINED;
253 WHEN OTHERS THEN
254 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
255 fnd_message.set_token('ERR_FIELD', 'px_picklist_status', FALSE);
256 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
257 fnd_message.set_token('TABLE', 'FND_LOOKUPS', FALSE);
258 FND_MSG_PUB.ADD;
259 RAISE EXCP_USER_DEFINED;
260 END;
261 END IF;
262
263 --validation of organization_id
264 IF nvl(p_organization_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
265 BEGIN
266 select organization_id into l_check_existence
267 from mtl_parameters
268 where organization_id = p_organization_id;
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
272 FND_MSG_PUB.ADD;
273 RAISE EXCP_USER_DEFINED;
274 WHEN OTHERS THEN
275 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
276 fnd_message.set_token('ERR_FIELD', 'p_organization_id', FALSE);
277 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
278 fnd_message.set_token('TABLE', 'MTL_PARAMETERS', FALSE);
279 FND_MSG_PUB.ADD;
280 RAISE EXCP_USER_DEFINED;
281 END;
282 END IF;
283
284 -- validate the creation_date
285 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
286 Open l_Get_Creation_Date_Csr;
287 Fetch l_Get_Creation_Date_Csr into l_creation_date;
288 If l_Get_Creation_Date_Csr%NOTFOUND Then
289 Close l_Get_Creation_Date_Csr;
290 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
291 fnd_message.set_token('ERR_FIELD', 'p_cretaion_date', FALSE);
292 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
293 fnd_message.set_token('TABLE', 'CSP_PICKLIST_HEADERS', FALSE);
294 FND_MSG_PUB.ADD;
295 RAISE EXCP_USER_DEFINED;
296 End if;
297 Close l_Get_Creation_Date_Csr;
298 End if;
299
300 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
301 l_last_update_date := sysdate;
302 END IF;
303
304 ELSE -- p_action_code = 2
305 IF nvl(px_picklist_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
306 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
307 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_picklist_header_id', FALSE);
308 FND_MSG_PUB.ADD;
309 RAISE EXCP_USER_DEFINED;
310 ELSE
311 BEGIN
312 select picklist_header_id/0 into l_check_existence
313 from csp_picklist_headers
314 where picklist_header_id = px_picklist_header_id;
315 EXCEPTION
319 fnd_msg_pub.add;
316 WHEN NO_DATA_FOUND THEN
317 fnd_message.set_name('CSP', 'CSP_INVALID_PICKLIST_HEADER');
318 fnd_message.set_token ('HEADER_ID', to_char(px_picklist_header_id), FALSE);
320 RAISE EXCP_USER_DEFINED;
321 WHEN OTHERS THEN
322 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
323 fnd_message.set_token('ERR_FIELD', 'px_picklist_header_id', FALSE);
324 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
325 fnd_message.set_token('TABLE', 'CSP_PICKLIST_HEADERS', FALSE);
326 FND_MSG_PUB.ADD;
327 RAISE EXCP_USER_DEFINED;
328 END;
329 END IF;
330
331 END IF;
332
333 -- Construct the record to call the CSP_picklist_header_PVT insert, update and delete operation
334 l_pick_header_rec.picklist_header_id := px_picklist_header_id;
335 l_pick_header_rec.CREATED_BY := nvl(l_CREATED_BY, fnd_api.g_miss_num);
336 l_pick_header_rec.CREATION_DATE := l_CREATION_DATE;
337 l_pick_header_rec.LAST_UPDATED_BY := nvl(l_LAST_UPDATED_BY, fnd_api.g_miss_num);
338 l_pick_header_rec.LAST_UPDATE_DATE := l_LAST_UPDATE_DATE;
339 l_pick_header_rec.LAST_UPDATE_LOGIN := l_LAST_UPDATE_LOGIN;
340 l_pick_header_rec.ORGANIZATION_ID := nvl(p_ORGANIZATION_ID, fnd_api.g_miss_num);
341 l_pick_header_rec.PICKLIST_NUMBER := p_PICKLIST_NUMBER;
342 l_pick_header_rec.PICKLIST_STATUS := nvl(p_PICKLIST_STATUS, fnd_api.g_miss_char);
343 l_pick_header_rec.DATE_CREATED := p_DATE_CREATED;
344 l_pick_header_rec.DATE_CONFIRMED := p_DATE_CONFIRMED;
345 l_pick_header_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
346 l_pick_header_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
347 l_pick_header_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
348 l_pick_header_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
349 l_pick_header_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
350 l_pick_header_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
351 l_pick_header_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
352 l_pick_header_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
353 l_pick_header_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
354 l_pick_header_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
355 l_pick_header_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
356 l_pick_header_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
357 l_pick_header_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
358 l_pick_header_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
359 l_pick_header_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
360 l_pick_header_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
361
362 -- call different operations based on the p_action_code
363 IF p_action_code = 0 THEN
364 -- call create procedure
365 CSP_PICKLIST_HEADER_PVT.Create_picklist_header(
366 P_Api_Version_Number => P_Api_Version_Number,
367 P_Init_Msg_List => P_Init_Msg_List,
368 P_Commit => l_Commit,
369 p_validation_level => l_validation_level,
370 P_PICK_HEADER_Rec => l_PICK_HEADER_Rec,
371 X_picklist_header_id => l_picklist_header_id,
372 X_Return_Status => l_Return_Status,
373 X_Msg_Count => l_Msg_Count,
374 X_Msg_Data => l_Msg_Data
375 );
376
377 ELSIF p_action_code = 1 THEN
378 -- call update procedure
379 CSP_PICKLIST_HEADER_PVT.Update_picklist_header(
380 P_Api_Version_Number => P_Api_Version_Number,
381 P_Init_Msg_List => P_Init_Msg_List,
382 P_Commit => l_Commit,
383 p_validation_level => l_validation_level,
384 P_PICK_HEADER_Rec => l_PICK_HEADER_Rec,
385 X_Return_Status => l_Return_Status,
386 X_Msg_Count => l_Msg_Count,
387 X_Msg_Data => l_Msg_Data
388 );
389
390 ELSE
391 -- call delete procedure
392 CSP_PICKLIST_HEADER_PVT.Delete_picklist_header(
393 P_Api_Version_Number => P_Api_Version_Number,
394 P_Init_Msg_List => P_Init_Msg_List,
395 P_Commit => l_Commit,
396 p_validation_level => l_validation_level,
397 P_PICK_HEADER_Rec => l_PICK_HEADER_Rec,
398 X_Return_Status => l_Return_Status,
399 X_Msg_Count => l_Msg_Count,
400 X_Msg_Data => l_Msg_Data
401 );
402 END IF;
403
404 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
405 RAISE FND_API.G_EXC_ERROR;
406 END IF;
407
408 x_return_status := FND_API.G_RET_STS_SUCCESS;
409 px_picklist_header_id := l_picklist_header_id;
410 IF fnd_api.to_boolean(p_commit) THEN
411 commit work;
412 END IF;
413
414 EXCEPTION
415 WHEN EXCP_USER_DEFINED THEN
416 Rollback to Validate_And_Write_PUB;
417 x_return_status := FND_API.G_RET_STS_ERROR;
418 fnd_msg_pub.count_and_get
419 ( p_count => x_msg_count
420 , p_data => x_msg_data);
421
422 --for debugging purpose
423 --x_msg_data := l_msg_data;
424
425 WHEN FND_API.G_EXC_ERROR THEN
426 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
427 P_API_NAME => L_API_NAME
428 ,P_PKG_NAME => G_PKG_NAME
429 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
430 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
431 ,X_MSG_COUNT => X_MSG_COUNT
432 ,X_MSG_DATA => X_MSG_DATA
433 ,X_RETURN_STATUS => X_RETURN_STATUS);
434
435 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
436 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
437 P_API_NAME => L_API_NAME
438 ,P_PKG_NAME => G_PKG_NAME
439 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
440 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
441 ,X_MSG_COUNT => X_MSG_COUNT
442 ,X_MSG_DATA => X_MSG_DATA
443 ,X_RETURN_STATUS => X_RETURN_STATUS);
444 WHEN OTHERS THEN
445 Rollback to Validate_And_Write_PUB;
446 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
447 fnd_message.set_token ('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
448 fnd_message.set_token ('SQLERRM', sqlerrm, FALSE);
449 fnd_msg_pub.add;
450 fnd_msg_pub.count_and_get
451 ( p_count => x_msg_count
452 , p_data => x_msg_data);
453 x_return_status := fnd_api.g_ret_sts_error;
454
455 END Validate_And_Write;
456
457 END CSP_PC_FORM_PICKHEADERS;