[Home] [Help]
PACKAGE BODY: APPS.CSP_PL_SHIP_UTIL
Source
1 Package BODY CSP_PL_SHIP_UTIL AS
2 /* $Header: cspgtpsb.pls 120.0 2005/05/25 11:27:47 appldev noship $ */
3 -- Start of comments
4 --
5 -- API name : CSP_PL_SHIP_UTIL
6 -- Type : PUBLIC
7 -- Purpose : CSP Utility programs to handle confirm_ship, update packlist line status and header status.
8 --
9 -- Modification History
10 -- Userid Date Comments
11 -- --------- ------ ------------------------------------------
12 -- klou 01/12/99 replace as_utility calls with jtf_plsql_api.
13 -- klou 01/04/99 created
14 --
15 -- Note :
16 -- End of comments
17
18 -- ****/////////////////////////////////////////////////////////////////////////////**** --
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PL_SHIP_UTIL';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtpsb.pls';
22
23 PROCEDURE Confirm_Ship(
24 /* $Header: cspgtpsb.pls 120.0 2005/05/25 11:27:47 appldev noship $ */
25 -- Start of Comments
26 -- Procedure name : Confirm_Ship
27 -- Purpose : This procedure updates the packlist_line_status and the quantity_shipped in the csp_packlist_lines table.
28 -- It requires the packlist_header_id and the quantity_shipped.
29 -- History :
30 -- Userid Date Comments
31 -- --------- ------ ------------------------------------------
32 -- klou 01/26/00 Add standard exception messages.
33 -- klou 01/12/00 Replace AS_UTLIITY calls with JTF
34 -- klou 01/04/99 created.
35 --
36 -- NOTES: If validations have been done in the precedent procedure from which this one is being called,
37 -- doing a full validation in this procedure is unnecessary. To avoid repeating the same validations,
38 -- you can set the p_validation_level to fnd_api.g_valid_level_none. However, it is your responsibility
39 -- to make sure all proper validations have been done if you decided not to use the validations in this
40 -- procedure. You are recommended to let this procedure handle the validations if you are in doubt.
41 --
42 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to
43 -- FND_API.G_VALID_LEVEL_NONE. If you do not do your own validations before calling this procedure,
44 -- you should set the p_validation_level to FND_API.G_VALID_LEVEL_FULL when calling this procedure.
45 -- End of Comments
46
47 P_Api_Version_Number IN NUMBER,
48 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
49 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
50 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
51 p_packlist_header_id IN NUMBER,
52 p_organization_id IN NUMBER,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2)
56
57 IS
58
59 l_api_version_number CONSTANT NUMBER := 1.0;
60 l_api_name CONSTANT VARCHAR2(20) := 'Confirm_Ship';
61 l_msg_data VARCHAR2(300);
62 l_check_existence NUMBER := 0;
63 l_return_status VARCHAR2(1);
64 l_msg_count NUMBER := 0;
65 l_commit VARCHAR2(1) := FND_API.G_FALSE;
66 l_organization_id NUMBER;
67 l_picklist_line_id NUMBER := 0;
68 l_transaction_quantity NUMBER := 0;
69 l_transaction_temp_id NUMBER := 0;
70 l_packlist_header_id NUMBER;
71 --l_counter NUMBER := 0; -- used to test the loop. can be removed after debug.
72
73 -- for inserting data, the validation_level should be none
74 -- because we do not want to call the core apps standard validations.
75 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
76 l_autoreceipt_flag csp_moveorder_headers.autoreceipt_flag%type;
77
78 l_outcome BOOLEAN := TRUE;
79 l_error_code VARCHAR2(200);
80 l_error_explanation VARCHAR2(240);
81
82 EXCP_USER_DEFINED EXCEPTION;
83
84 -- Define excp_nosavepoint exception to trap oracle's No Savepoint exception.
85 EXCP_NOSAVEPOINT EXCEPTION;
86 PRAGMA EXCEPTION_INIT(EXCP_NOSAVEPOINT, -1086);
87
88 Cursor C_Get_packlist_lines IS
89 Select PACKLIST_LINE_ID,
90 CREATED_BY,
91 CREATION_DATE,
92 LAST_UPDATED_BY,
93 LAST_UPDATE_DATE,
94 LAST_UPDATE_LOGIN,
95 ORGANIZATION_ID,
96 PACKLIST_LINE_NUMBER,
97 PACKLIST_HEADER_ID,
98 BOX_ID,
99 PICKLIST_LINE_ID,
100 PACKLIST_LINE_STATUS,
101 INVENTORY_ITEM_ID,
102 QUANTITY_PACKED,
103 QUANTITY_SHIPPED,
104 QUANTITY_RECEIVED,
105 ATTRIBUTE_CATEGORY,
106 ATTRIBUTE1,
107 ATTRIBUTE2,
108 ATTRIBUTE3,
109 ATTRIBUTE4,
110 ATTRIBUTE5,
111 ATTRIBUTE6,
112 ATTRIBUTE7,
113 ATTRIBUTE8,
114 ATTRIBUTE9,
115 ATTRIBUTE10,
116 ATTRIBUTE11,
117 ATTRIBUTE12,
118 ATTRIBUTE13,
119 ATTRIBUTE14,
120 ATTRIBUTE15,
121 UOM_CODE,
122 LINE_ID
123 From CSP_PACKLIST_LINES
124 WHERE organization_id = p_organization_id
125 AND packlist_header_id = p_packlist_header_id;
126
127 Cursor C_Get_Packlist_Headers IS
128 SELECT
129 PACKLIST_HEADER_ID ,
130 CREATED_BY ,
131 CREATION_DATE ,
132 LAST_UPDATED_BY ,
133 LAST_UPDATE_DATE ,
134 LAST_UPDATE_LOGIN ,
135 ORGANIZATION_ID ,
136 PACKLIST_NUMBER ,
137 SUBINVENTORY_CODE ,
138 PACKLIST_STATUS ,
139 DATE_CREATED ,
140 DATE_PACKED ,
141 DATE_SHIPPED ,
142 DATE_RECEIVED ,
143 CARRIER ,
144 SHIPMENT_METHOD ,
145 WAYBILL ,
146 COMMENTS ,
147 LOCATION_ID,
148 PARTY_SITE_ID,
149 ATTRIBUTE_CATEGORY ,
150 ATTRIBUTE1 ,
151 ATTRIBUTE2 ,
152 ATTRIBUTE3 ,
153 ATTRIBUTE4 ,
154 ATTRIBUTE5 ,
155 ATTRIBUTE6 ,
156 ATTRIBUTE7 ,
157 ATTRIBUTE8 ,
158 ATTRIBUTE9 ,
159 ATTRIBUTE10 ,
160 ATTRIBUTE11 ,
161 ATTRIBUTE12 ,
162 ATTRIBUTE13 ,
163 ATTRIBUTE14 ,
164 ATTRIBUTE15
165 From CSP_PACKLIST_HEADERS
166 WHERE organization_id = p_organization_id
167 AND packlist_header_id = p_packlist_header_id;
168
169 l_packlist_headers_rec CSP_packlist_headers_PVT.PLH_Rec_Type;
170 l_packlist_line_rec CSP_packlist_lines_PVT.PLL_Rec_Type;
171 l_transaction_header_id NUMBER := null;
172 l_temp_id NUMBER;
173 l_move_order_line_id NUMBER;
174 l_header_id NUMBER;
175 l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
176
177 CURSOR C_Get_Temp_ID IS
178 SELECT transaction_temp_id
179 FROM CSP_Picklist_Lines
180 WHERE picklist_line_id = l_packlist_line_rec.picklist_line_id;
181
182 CURSOR C_Get_Move_Order_Line_ID(p_temp_id NUMBER) IS
183 SELECT move_order_line_id
184 FROM mtl_material_transactions_temp
185 WHERE transaction_temp_id = p_temp_id
186 AND organization_id = p_organization_id;
187
188
189
190 BEGIN
191 SAVEPOINT Confirm_Ship_PUB;
192 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
193 -- initialize message list
194 FND_MSG_PUB.initialize;
195 END IF;
196
197 -- Standard call to check for call compatibility.
198 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
199 p_api_version_number,
200 l_api_name,
201 G_PKG_NAME)
202 THEN
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204 END IF;
205
206
207 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
208 -- validate p_organization_id
209 IF p_organization_id IS NULL THEN
210 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
211 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
212 FND_MSG_PUB.ADD;
213 RAISE EXCP_USER_DEFINED;
214 ELSE
215 IF p_validation_level = fnd_api.g_valid_level_full THEN
216 BEGIN
217 select organization_id into l_check_existence
218 from mtl_parameters
219 where organization_id = p_organization_id;
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
223 FND_MSG_PUB.ADD;
224 RAISE EXCP_USER_DEFINED;
225 WHEN OTHERS THEN
226 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
227 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
228 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
229 fnd_message.set_token('TABLE', 'mtl_parameters', TRUE);
230 FND_MSG_PUB.ADD;
231 RAISE EXCP_USER_DEFINED;
232 END;
233 END IF;
234 NULL;
235 END IF;
236
237 -- validate packlist_header_id
238 IF p_packlist_header_id IS NULL THEN
239 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
240 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_packlist_header_id', TRUE);
241 FND_MSG_PUB.ADD;
242 RAISE EXCP_USER_DEFINED;
243 ELSE
244 IF p_validation_level = fnd_api.g_valid_level_full THEN
245 BEGIN
246 select packlist_header_id into l_check_existence
247 from csp_packlist_headers
248 where organization_id = p_organization_id
249 and packlist_header_id = p_packlist_header_id;
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_PACKLIST_HEADER');
253 FND_MESSAGE.SET_TOKEN('HEADER_ID', to_char(p_packlist_header_id), TRUE);
254 FND_MSG_PUB.ADD;
255 RAISE EXCP_USER_DEFINED;
256 WHEN OTHERS THEN
257 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
258 fnd_message.set_token('ERR_FIELD', 'p_packlist_header_id', TRUE);
259 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
260 fnd_message.set_token('TABLE', 'csp_packlist_headers', TRUE);
261 FND_MSG_PUB.ADD;
262 RAISE EXCP_USER_DEFINED;
263 END;
264 END IF;
265 NULL;
266 END IF;
267 END IF; -- end full validations
268
269 -- Update the packlist header status to shipped and date_shipped to sysdate
270 Open C_Get_Packlist_Headers;
271 Fetch C_Get_Packlist_Headers Into l_packlist_headers_rec;
272
273 IF C_Get_Packlist_Headers%NOTFOUND THEN
274 CLOSE C_Get_Packlist_Headers;
275 fnd_message.set_name ('CSP', 'CSP_INVALID_PACKLIST_HEADER');
276 fnd_message.set_token ('HEADER_ID', to_char(p_packlist_header_id), TRUE);
277 fnd_msg_pub.add;
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280 CLOSE C_Get_Packlist_Headers;
281 l_packlist_headers_rec.date_shipped := sysdate;
282 l_packlist_headers_rec.packlist_status := '2';
283 l_packlist_headers_rec.last_update_date := sysdate;
284 /*update_packlist_header_sts (
285 P_Api_Version_Number => l_api_version_number,
286 P_Init_Msg_List => FND_API.G_true,
287 P_Commit => l_commit,
288 p_validation_level => l_validation_level,
289 p_packlist_header_id => p_packlist_header_id,
290 p_organization_id => p_organization_id,
291 p_packlist_status => '2',
292 x_return_status => l_return_status,
293 x_msg_count => l_msg_count,
294 x_msg_data => l_msg_data );*/
295
296 -- call the CSP_Packlist_Headers_PVT.Update_packlist_headers to updat the packlist_status.
297 CSP_Packlist_Headers_PVT.Update_packlist_headers(
298 P_Api_Version_Number => l_api_version_number,
299 P_Init_Msg_List => p_init_msg_list,
300 P_Commit => FND_API.G_FALSE,
301 p_validation_level => l_validation_level,
302 P_Identity_Salesforce_Id => NULL,
303 P_PLH_Rec => l_packlist_headers_rec,
304 X_Return_Status => l_return_status,
305 X_Msg_Count => l_msg_count,
306 X_Msg_Data => l_msg_data
307 );
308
309 IF l_return_status <> fnd_api.g_ret_sts_success THEN
310 --dbms_output.put_line('Failed in 1 ');
311 RAISE FND_API.G_EXC_ERROR;
312 END IF;
313
314 -- First we need to determine whether the move order associated with this packlist is an autoreceipt or
315 -- a manual receipt. If it is an autoreceipt, we need to initiate the material transactions. Else,
316 -- we need to split the material transactions into two stages.
317 -- Steps
318 -- 1. get the picklist_line_id from the packlist_lines cursor.
319 -- 2. get the trasaction_temp_id from the picklist_line_id.
320 -- 3. get the move_order_line_id from the transaction_temp_id.
321 OPEN C_Get_packlist_lines;
322
323 LOOP
324 FETCH C_Get_packlist_lines INTO l_packlist_line_rec;
325 EXIT WHEN C_Get_packlist_lines%NOTFOUND;
326
327 -- Update the packlist status to 'shipped' and the quantity_shipped to p_quantity_shipped.
328 Update_Packlist_Sts_Qty (
329 P_Api_Version_Number => l_api_version_number,
330 P_Init_Msg_List => FND_API.G_true,
331 P_Commit => l_commit,
332 p_validation_level => l_validation_level,
333 p_organization_id => p_organization_id,
334 p_packlist_line_id => l_packlist_line_rec.packlist_line_id,
335 p_line_status => '2',
336 p_quantity_packed => NULL,
337 p_quantity_shipped => l_packlist_line_rec.quantity_packed,
338 p_quantity_received => NULL,
339 x_return_status => l_return_status,
340 x_msg_count => l_msg_count,
341 x_msg_data => l_msg_data
342 );
343
344 IF l_return_status <> fnd_api.g_ret_sts_success THEN
345 -- dbms_output.put_line('Failed in 2 ');
346
347 RAISE FND_API.G_EXC_ERROR;
348 END IF;
349
350 -- Update the quantity_shipped of the l_packlist_line_rec to quantity_packed.
351 l_packlist_line_rec.quantity_shipped := l_packlist_line_rec.quantity_packed;
352 BEGIN
353 OPEN C_Get_Temp_ID;
354 FETCH C_Get_Temp_ID INTO l_temp_id;
355 IF C_Get_Temp_ID%NOTFOUND THEN
356 CLOSE C_Get_Temp_ID;
357 fnd_message.set_name ('CSP', 'CSP_NO_TXN_RECORD');
358 fnd_message.set_token ('PICKLIST_ID', to_char(l_packlist_line_rec.picklist_line_id), TRUE);
359 fnd_msg_pub.add;
360 RAISE EXCP_USER_DEFINED;
361 END IF;
362
363 CLOSE C_Get_Temp_ID;
364 -- get the move_order_line based on the l_temp_id
365 BEGIN
366 OPEN C_Get_Move_Order_Line_ID(l_temp_id);
367 FETCH C_Get_Move_Order_Line_ID INTO l_move_order_line_id;
368 IF C_Get_Move_Order_Line_ID%NOTFOUND THEN
369 CLOSE C_Get_Move_Order_Line_ID;
370 fnd_message.set_name ('CSP', 'CSP_PACKLIST_MOVEORDER_ERRORS');
371 fnd_message.set_token ('HEADER_ID', to_char(p_packlist_header_id), TRUE);
372 fnd_msg_pub.add;
373 RAISE EXCP_USER_DEFINED;
374 END IF;
375
376 CLOSE C_Get_Move_Order_Line_ID;
377 select header_id into l_header_id
378 from csp_moveorder_lines
379 where line_id = l_move_order_line_id;
380
381 select autoreceipt_flag into l_autoreceipt_flag
382 from csp_moveorder_headers
383 where header_id = l_header_id;
384
385 -- Update the quantity_delivered of the move order line.
386 l_trolin_rec := INV_Trolin_util.Query_Row(l_move_order_line_id);
387 l_trolin_rec.quantity_delivered := nvl(l_trolin_rec.quantity_delivered,0) + l_packlist_line_rec.quantity_shipped;
388 l_trolin_rec.last_update_date := SYSDATE;
389 l_trolin_rec.last_updated_by := FND_GLOBAL.USER_ID;
390 l_trolin_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
391 INV_Trolin_Util.Update_Row(l_trolin_rec);
392
393 IF l_autoreceipt_flag = 'Y' THEN
394 -- call the ccsp_mo_mtltxns_util.confirm_receipt with l_validation_level = none
395 csp_mo_mtltxns_util.confirm_receipt (
396 P_Api_Version_Number => l_api_version_number,
397 P_Init_Msg_List => FND_API.G_True,
398 P_Commit => l_commit,
399 p_validation_level => l_validation_level,
400 p_packlist_line_id => l_packlist_line_rec.packlist_line_id,
401 p_organization_id => p_organization_id,
402 p_transaction_temp_id => l_temp_id,
403 p_quantity_received => l_packlist_line_rec.quantity_shipped,
404 px_transaction_header_id => l_transaction_header_id,
405 p_process_flag => FND_API.G_FALSE,
406 X_Return_Status => l_return_status,
407 X_Msg_Count => l_msg_count,
408 X_Msg_Data => l_msg_data
409 );
410
411
412 IF l_return_status <> fnd_api.g_ret_sts_success THEN
413 -- dbms_output.put_line('Failed in 3');
414
415 Rollback to Confirm_Ship_PUB;
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417 END IF;
418
419 ELSIF l_autoreceipt_flag = 'N' THEN
420
421 CSP_PC_FORM_MTLTXNS.CSP_MO_LINES_MANUAL_RECEIPT (
422 P_Api_Version_Number => l_api_version_number,
423 P_Init_Msg_List => FND_API.G_True,
424 P_Commit => l_commit,
425 p_validation_level => l_validation_level,
426 p_organization_id => p_organization_id,
427 p_transaction_temp_id => l_temp_id,
428 px_transaction_header_id => l_transaction_header_id,
429 p_process_flag => FND_API.G_FALSE,
430 X_Return_Status => l_return_status,
431 X_Msg_Count => l_msg_count,
432 X_Msg_Data => l_msg_data
433 );
434
435 IF l_return_status <> fnd_api.g_ret_sts_success THEN
436 -- dbms_output.put_line('Failed in 4 ');
437
438 Rollback to Confirm_Ship_PUB;
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 END IF;
441 ELSE
442 fnd_message.set_name ('CSP', 'CSP_INVALID_MO_RECEIPT_TYPE');
443 fnd_message.set_token ('HEADER_ID', to_char(l_header_id), TRUE);
444 fnd_msg_pub.add;
445 IF C_Get_packlist_lines%ISOPEN THEN
446 CLOSE C_Get_packlist_lines;
447 END IF;
448 RAISE EXCP_USER_DEFINED;
449
450 END IF;
451
452 EXCEPTION
453 WHEN EXCP_NOSAVEPOINT THEN
454 RAISE EXCP_NOSAVEPOINT;
455 WHEN NO_DATA_FOUND THEN
456 fnd_message.set_name ('CSP', 'CSP_INVALID_MOVEORDER');
457 fnd_message.set_token ('HEADER_ID', to_char(l_header_id), TRUE);
458 fnd_msg_pub.add;
459 RAISE EXCP_USER_DEFINED;
460 WHEN EXCP_USER_DEFINED THEN
461 RAISE EXCP_USER_DEFINED;
462 WHEN OTHERS THEN
463 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
464 fnd_message.set_token('SQLEERM', sqlerrm, TRUE);
465 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
466 fnd_msg_pub.add;
467 RAISE EXCP_USER_DEFINED;
468
469 END;
470 END;
471 --l_counter := l_counter+1;
472
473 END LOOP;
474 IF C_Get_packlist_lines%rowcount = 0 THEN
475 CLOSE C_Get_packlist_lines;
476 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_PACKLIST_HEADER');
477 FND_MESSAGE.SET_TOKEN('HEADER_ID', to_char(p_packlist_header_id), TRUE);
478 FND_MSG_PUB.ADD;
479 RAISE EXCP_USER_DEFINED;
480 END IF;
481
482 IF C_Get_packlist_lines%ISOPEN THEN
483 CLOSE C_Get_packlist_lines;
484 END IF;
485
486 IF l_transaction_header_id IS NOT NULL THEN
487 IF NOT CSP_Mo_Mtltxns_Util.Call_Online (p_transaction_header_id => l_transaction_header_id) THEN
488 l_outcome := FALSE;
489 x_return_status := FND_API.G_RET_STS_SUCCESS;
490 fnd_msg_pub.count_and_get
491 ( p_count => x_msg_count
492 , p_data => x_msg_data);
493 Return;
494 END IF;
495 END IF;
496
497 IF fnd_api.to_boolean(p_commit) THEN
498 commit work;
499 END IF;
500
501 fnd_msg_pub.count_and_get
502 ( p_count => x_msg_count
503 , p_data => x_msg_data);
504 x_return_status := fnd_api.g_ret_sts_success;
505
506 EXCEPTION
507 WHEN EXCP_NOSAVEPOINT THEN
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 fnd_msg_pub.count_and_get
510 ( p_count => x_msg_count
511 , p_data => x_msg_data);
512 WHEN EXCP_USER_DEFINED THEN
513 Rollback to Confirm_Ship_PUB;
514 x_return_status := FND_API.G_RET_STS_ERROR;
515 fnd_msg_pub.count_and_get
516 ( p_count => x_msg_count
517 , p_data => x_msg_data);
518 WHEN FND_API.G_EXC_ERROR THEN
519 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
520 P_API_NAME => L_API_NAME
521 ,P_PKG_NAME => G_PKG_NAME
522 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
523 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
524 ,X_MSG_COUNT => X_MSG_COUNT
525 ,X_MSG_DATA => X_MSG_DATA
526 ,X_RETURN_STATUS => X_RETURN_STATUS);
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
528 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
529 P_API_NAME => L_API_NAME
530 ,P_PKG_NAME => G_PKG_NAME
531 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
532 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
533 ,X_MSG_COUNT => X_MSG_COUNT
534 ,X_MSG_DATA => X_MSG_DATA
535 ,X_RETURN_STATUS => X_RETURN_STATUS);
536 WHEN OTHERS THEN
537 Rollback to Confirm_Ship_PUB;
538 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
539 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
540 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
541 fnd_msg_pub.add;
542 fnd_msg_pub.count_and_get
543 ( p_count => x_msg_count
544 , p_data => x_msg_data);
545 x_return_status := fnd_api.g_ret_sts_error;
546 END Confirm_Ship;
547
548
549 Procedure Update_Packlist_Sts_Qty(
550 P_Api_Version_Number IN NUMBER,
551 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
552 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
553 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
554 p_organization_id IN NUMBER,
555 p_packlist_line_id IN NUMBER,
556 p_line_status IN VARCHAR2,
557 p_quantity_packed IN NUMBER,
558 p_quantity_shipped IN NUMBER,
559 p_quantity_received IN NUMBER,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_count OUT NOCOPY NUMBER,
562 x_msg_data OUT NOCOPY VARCHAR2)
563 IS
564 l_packlist_line_rec CSP_packlist_lines_PVT.PLL_Rec_Type;
565
566 Cursor C_Get_packlist_lines IS
567 Select PACKLIST_LINE_ID,
568 CREATED_BY,
569 CREATION_DATE,
570 LAST_UPDATED_BY,
571 LAST_UPDATE_DATE,
572 LAST_UPDATE_LOGIN,
573 ORGANIZATION_ID,
574 PACKLIST_LINE_NUMBER,
575 PACKLIST_HEADER_ID,
576 BOX_ID,
577 PICKLIST_LINE_ID,
578 PACKLIST_LINE_STATUS,
579 INVENTORY_ITEM_ID,
580 QUANTITY_PACKED,
581 QUANTITY_SHIPPED,
582 QUANTITY_RECEIVED,
583 ATTRIBUTE_CATEGORY,
584 ATTRIBUTE1,
585 ATTRIBUTE2,
586 ATTRIBUTE3,
587 ATTRIBUTE4,
588 ATTRIBUTE5,
589 ATTRIBUTE6,
590 ATTRIBUTE7,
591 ATTRIBUTE8,
592 ATTRIBUTE9,
593 ATTRIBUTE10,
594 ATTRIBUTE11,
595 ATTRIBUTE12,
596 ATTRIBUTE13,
597 ATTRIBUTE14,
598 ATTRIBUTE15,
599 UOM_CODE,
600 LINE_ID
601 From CSP_PACKLIST_LINES
602 WHERE organization_id = p_organization_id
603 AND packlist_line_id = p_packlist_line_id;
604 -- For Update NOWAIT;
605
606 l_api_version_number CONSTANT NUMBER := 1.0;
607 l_api_name CONSTANT VARCHAR2(50) := 'Update_Packlist_Sts_Qty';
608 l_msg_count NUMBER;
609 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
610 l_msg_data VARCHAR2(300);
611 l_check_existence NUMBER;
612 l_packlist_header_id NUMBER;
613 EXCP_USER_DEFINED EXCEPTION;
614 BEGIN
615
616 SAVEPOINT Update_Packlist_Sts_Qty_PUB;
617 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
618 -- initialize message list
619 FND_MSG_PUB.initialize;
620 END IF;
621
622 -- Standard call to check for call compatibility.
623 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
624 p_api_version_number,
625 l_api_name,
626 G_PKG_NAME)
627 THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631 -- validate p_organization_id
632 IF p_organization_id IS NULL THEN
633 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
634 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
635 FND_MSG_PUB.ADD;
636 RAISE EXCP_USER_DEFINED;
637 ELSE
638 IF p_validation_level = fnd_api.g_valid_level_full THEN
639 BEGIN
640 select organization_id into l_check_existence
641 from mtl_parameters
642 where organization_id = p_organization_id;
643 EXCEPTION
644 WHEN NO_DATA_FOUND THEN
645 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
646 FND_MSG_PUB.ADD;
647 RAISE EXCP_USER_DEFINED;
648 WHEN OTHERS THEN
649 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
650 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
651 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
652 fnd_message.set_token('TABLE', 'mtl_parameters', TRUE);
653 FND_MSG_PUB.ADD;
654 RAISE EXCP_USER_DEFINED;
655 END;
656 END IF;
657 NULL;
658 END IF;
659
660 -- validate the p_packlist_line_id
661 IF p_packlist_line_id IS NULL THEN
662 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
663 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_packlist_line_id', TRUE);
664 FND_MSG_PUB.ADD;
665 RAISE EXCP_USER_DEFINED;
666 ELSE
667 IF p_validation_level = fnd_api.g_valid_level_full THEN
668 BEGIN
669 select packlist_line_id into l_check_existence
670 from csp_packlist_lines
671 where organization_id = p_organization_id
672 and packlist_line_id = p_packlist_line_id;
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN
675 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_PACKLIST_LINE');
676 FND_MSG_PUB.ADD;
677 RAISE EXCP_USER_DEFINED;
678 WHEN OTHERS THEN
679 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
680 fnd_message.set_token('ERR_FIELD', 'p_packlist_line_id', TRUE);
681 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
682 fnd_message.set_token('TABLE', 'csp_packlist_lines', TRUE);
683 fnd_msg_pub.add;
684 RAISE EXCP_USER_DEFINED;
685
686 END;
687 END IF;
688 NULL;
689 END IF;
690
691
692 -- now it's ready to perform the update
693 OPEN C_Get_packlist_lines;
694 FETCH C_Get_packlist_lines INTO l_packlist_line_rec;
695 IF C_Get_packlist_lines%NOTFOUND THEN
696 CLOSE C_Get_packlist_lines;
697 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_PACKLIST_LINE');
698 FND_MSG_PUB.ADD;
699 RAISE EXCP_USER_DEFINED;
700 ELSE
701
702 IF p_line_status IS NOT NULL THEN
703 l_packlist_line_rec.packlist_line_status := p_line_status;
704 END IF;
705 IF p_quantity_packed IS NOT NULL THEN
706 l_packlist_line_rec.quantity_packed := p_quantity_packed;
707 END IF;
708 IF p_quantity_shipped IS NOT NULL THEN
709 l_packlist_line_rec.quantity_shipped:= p_quantity_shipped;
710 END IF;
711 IF p_quantity_received IS NOT NULL THEN
712 l_packlist_line_rec.quantity_received:= p_quantity_received;
713 END IF;
714
715 l_packlist_line_rec.last_update_date := sysdate;
716 CSP_packlist_lines_PVT.Update_packlist_lines(
717 P_Api_Version_Number => l_api_version_number,
718 P_Init_Msg_List => FND_API.G_FALSE,
719 P_Commit => FND_API.G_FALSE,
720 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
721 P_Identity_Salesforce_Id => NULL,
722 P_PLL_Rec => l_packlist_line_rec,
723 X_Return_Status => l_return_status,
724 X_Msg_Count => l_msg_count,
725 X_Msg_Data => l_msg_data
726 );
727 END IF;
728 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731 x_return_status := l_return_status;
732
733 EXCEPTION
734 WHEN EXCP_USER_DEFINED THEN
735 Rollback to Update_Packlist_Sts_Qty_PUB;
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 fnd_msg_pub.count_and_get
738 ( p_count => x_msg_count
739 , p_data => x_msg_data);
740 WHEN FND_API.G_EXC_ERROR THEN
741 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
742 P_API_NAME => L_API_NAME
743 ,P_PKG_NAME => G_PKG_NAME
744 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
745 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
746 ,X_MSG_COUNT => X_MSG_COUNT
747 ,X_MSG_DATA => X_MSG_DATA
748 ,X_RETURN_STATUS => X_RETURN_STATUS);
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
751 P_API_NAME => L_API_NAME
752 ,P_PKG_NAME => G_PKG_NAME
753 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
754 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
755 ,X_MSG_COUNT => X_MSG_COUNT
756 ,X_MSG_DATA => X_MSG_DATA
757 ,X_RETURN_STATUS => X_RETURN_STATUS);
758 WHEN OTHERS THEN
759 Rollback to Update_Packlist_Sts_Qty_PUB;
760 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
761 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
762 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
763 fnd_msg_pub.add;
764 fnd_msg_pub.count_and_get
765 ( p_count => x_msg_count
766 , p_data => x_msg_data);
767 x_return_status := fnd_api.g_ret_sts_error;
768
769 END Update_Packlist_Sts_Qty;
770
771
772 FUNCTION validate_pl_line_status (
773 -- Start of Comments
774 -- Function name : validate_pl_line_status
775 -- Purpose : This function checks whether the statuses of all packlist lines of a packlist header have been closed.
776 -- It returns fnd_api.g_true if the statuses of all lines has been set to shipped.
777 -- Otherwise, it returns fnd_api.g_false.
778 -- History :
779 -- Person Date Descriptions
780 -- ------ -------- -----------------
781 -- klou 12-Apr-2000 Add p_check_receipt_short. If true, check also whether the status is '4' when it is not
782 -- the p_status_to_be_validated.
783 -- klou 06-Feb-2000 Added standard messages.
784 -- klou 04-Jan-2000 Created.
785 --
786 -- NOTES:
787 -- End of Comments
788 p_packlist_header_id IN NUMBER,
789 p_status_to_be_validated IN VARCHAR2,
790 p_check_receipt_short BOOLEAN := FALSE)
791 RETURN VARCHAR2
792 IS
793 l_line_id NUMBER;
794 l_line_status VARCHAR2(30) := '-1';
795 CURSOR C_Get_Packlist_Lines IS
796 SELECT packlist_line_id
797 FROM CSP_Packlist_LINES
798 WHERE packlist_header_id = p_packlist_header_id;
799
800 BEGIN
801 OPEN C_Get_Packlist_Lines;
802 LOOP
803 FETCH C_Get_Packlist_Lines INTO l_line_id;
804 EXIT WHEN C_Get_Packlist_Lines%NOTFOUND;
805
806 BEGIN
807 SELECT packlist_line_status INTO l_line_status
808 FROM CSP_Packlist_Lines
809 WHERE packlist_line_id = l_line_id;
810
811 IF l_line_status <> p_status_to_be_validated THEN
812 IF p_check_receipt_short THEN
813 IF l_line_status <> '4' THEN
814 CLOSE C_Get_Packlist_Lines;
815 RETURN fnd_api.g_false;
816 END IF;
817 ELSE
818 RETURN fnd_api.g_false;
819 END IF;
820 END IF;
821 EXCEPTION
822 WHEN OTHERS THEN
823 CLOSE C_Get_Packlist_Lines;
824 RETURN fnd_api.g_false;
825 END;
826
827 END LOOP;
828
829 IF C_Get_Packlist_Lines%rowcount = 0 THEN
830 IF C_Get_Packlist_Lines%ISOPEN THEN
831 CLOSE C_Get_Packlist_Lines;
832 END IF;
833 RETURN fnd_api.g_false;
834 END IF;
835
836 IF C_Get_Packlist_Lines%ISOPEN THEN
837 CLOSE C_Get_Packlist_Lines;
838 END IF;
839
840 RETURN fnd_api.g_true;
841
842 END validate_pl_line_status;
843
844
845 Procedure update_packlist_header_sts (
846 P_Api_Version_Number IN NUMBER,
847 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
848 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
849 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
850 p_packlist_header_id IN NUMBER,
851 p_organization_id IN NUMBER,
852 p_packlist_status IN VARCHAR2 := FND_API.G_MISS_CHAR,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2
856
857 )
858 IS
859 Cursor C_Get_Packlist_Headers IS
860 SELECT
861 PACKLIST_HEADER_ID ,
862 CREATED_BY ,
863 CREATION_DATE ,
864 LAST_UPDATED_BY ,
865 LAST_UPDATE_DATE ,
866 LAST_UPDATE_LOGIN ,
867 ORGANIZATION_ID ,
868 PACKLIST_NUMBER ,
869 SUBINVENTORY_CODE ,
870 PACKLIST_STATUS ,
871 DATE_CREATED ,
872 DATE_PACKED ,
873 DATE_SHIPPED ,
874 DATE_RECEIVED ,
875 CARRIER ,
876 SHIPMENT_METHOD ,
877 WAYBILL ,
878 COMMENTS ,
879 LOCATION_ID,
880 PARTY_SITE_ID,
881 ATTRIBUTE_CATEGORY ,
882 ATTRIBUTE1 ,
883 ATTRIBUTE2 ,
884 ATTRIBUTE3 ,
885 ATTRIBUTE4 ,
886 ATTRIBUTE5 ,
887 ATTRIBUTE6 ,
888 ATTRIBUTE7 ,
889 ATTRIBUTE8 ,
890 ATTRIBUTE9 ,
891 ATTRIBUTE10 ,
892 ATTRIBUTE11 ,
893 ATTRIBUTE12 ,
894 ATTRIBUTE13 ,
895 ATTRIBUTE14 ,
896 ATTRIBUTE15
897 From CSP_PACKLIST_HEADERS
898 WHERE organization_id = p_organization_id
899 AND packlist_header_id = p_packlist_header_id;
900
901 l_packlist_headers_rec CSP_packlist_headers_PVT.PLH_Rec_Type;
902 l_api_version_number CONSTANT NUMBER := 1.0;
903 l_api_name CONSTANT VARCHAR2(50) := 'Update_Packlist_Sts_Qty';
904 l_msg_count NUMBER;
905 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
906 l_msg_data VARCHAR2(300);
907 l_check_existence NUMBER;
908 l_packlist_header_id NUMBER;
909 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
910 EXCP_USER_DEFINED EXCEPTION;
911
912 BEGIN
913 SAVEPOINT Update_Packlist_Sts_Qty_PUB;
914
915 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
916 -- initialize message list
917 FND_MSG_PUB.initialize;
918 END IF;
919
920 -- Standard call to check for call compatibility.
921 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
922 p_api_version_number,
923 l_api_name,
924 G_PKG_NAME)
925 THEN
926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
927 END IF;
928
929 IF p_validation_level = fnd_api.g_valid_level_full THEN
930 -- validate the p_organization_id
931 IF p_organization_id IS NULL THEN
932 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
933 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
934 FND_MSG_PUB.ADD;
935 RAISE EXCP_USER_DEFINED;
936 ELSE
937 IF p_validation_level = fnd_api.g_valid_level_full THEN
938 BEGIN
939 select organization_id into l_check_existence
940 from mtl_parameters
941 where organization_id = p_organization_id;
942 EXCEPTION
943 WHEN NO_DATA_FOUND THEN
944 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
945 FND_MSG_PUB.ADD;
946 RAISE EXCP_USER_DEFINED;
947 WHEN OTHERS THEN
948 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
949 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
950 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
951 fnd_message.set_token('TABLE', 'mtl_parameters', TRUE);
952 FND_MSG_PUB.ADD;
953 RAISE EXCP_USER_DEFINED;
954 END;
955 END IF;
956 NULL;
957 END IF;
958
959 IF p_packlist_header_id IS NULL THEN
960 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
961 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_packlist_header_id', TRUE);
962 FND_MSG_PUB.ADD;
963 RAISE EXCP_USER_DEFINED;
964 ELSE
965 -- IF p_validation_level = fnd_api.g_valid_level_full THEN
966 BEGIN
967 select packlist_header_id into l_check_existence
968 from csp_packlist_headers
969 where organization_id = p_organization_id
970 and packlist_header_id = p_packlist_header_id;
971 EXCEPTION
972 WHEN NO_DATA_FOUND THEN
973 fnd_message.set_name ('CSP', 'CSP_INVALID_PACKLIST_HEADER');
974 fnd_message.set_token ('HEADER_ID', to_char(p_packlist_header_id), TRUE);
975 fnd_msg_pub.add;
976 RAISE EXCP_USER_DEFINED;
977 WHEN OTHERS THEN
978 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
979 fnd_message.set_token('ERR_FIELD', 'p_packlist_header_id', TRUE);
980 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
981 fnd_message.set_token('TABLE', 'csp_packlist_headers', TRUE);
982 FND_MSG_PUB.ADD;
983 RAISE EXCP_USER_DEFINED;
984 END;
985 --END IF;
986 END IF;
987 END IF;
988 -- Now it's ready to do the update
989 OPEN C_Get_Packlist_Headers;
990 FETCH C_Get_Packlist_Headers INTO l_packlist_headers_rec;
991
992 IF C_Get_Packlist_Headers%NOTFOUND THEN
993 CLOSE C_Get_Packlist_Headers;
994 fnd_message.set_name ('CSP', 'CSP_INVALID_PACKLIST_HEADER');
995 fnd_message.set_token ('HEADER_ID', to_char(p_packlist_header_id), TRUE);
996 fnd_msg_pub.add;
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 END IF;
999 CLOSE C_Get_Packlist_Headers;
1000
1001 IF nvl(p_packlist_status, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
1002 l_packlist_headers_rec.packlist_status := p_packlist_status;
1003 ELSE
1004 l_packlist_headers_rec.packlist_status := '2';
1005 END IF;
1006
1007 l_packlist_headers_rec.last_update_date := sysdate;
1008
1009 -- call the CSP_Packlist_Headers_PVT.Update_packlist_headers to updat the packlist_status.
1010 CSP_Packlist_Headers_PVT.Update_packlist_headers(
1011 P_Api_Version_Number => l_api_version_number,
1012 P_Init_Msg_List => p_init_msg_list,
1013 P_Commit => FND_API.G_FALSE,
1014 p_validation_level => l_validation_level,
1015 P_Identity_Salesforce_Id => NULL,
1016 P_PLH_Rec => l_packlist_headers_rec,
1017 X_Return_Status => l_return_status,
1018 X_Msg_Count => l_msg_count,
1019 X_Msg_Data => l_msg_data
1020 );
1021
1022 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1023 RAISE FND_API.G_EXC_ERROR;
1024 END IF;
1025
1026 IF fnd_api.to_boolean(p_commit) THEN
1027 commit WORK;
1028 END IF;
1029
1030 x_return_status := fnd_api.g_ret_sts_success;
1031
1032 EXCEPTION
1033 WHEN EXCP_USER_DEFINED THEN
1034 Rollback to Update_Packlist_Sts_Qty_PUB;
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 fnd_msg_pub.count_and_get
1037 ( p_count => x_msg_count
1038 , p_data => x_msg_data);
1039
1040 --for debugging purpose
1041 -- x_msg_data := l_msg_data;
1042
1043 WHEN FND_API.G_EXC_ERROR THEN
1044 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1045 P_API_NAME => L_API_NAME
1046 ,P_PKG_NAME => G_PKG_NAME
1047 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1048 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1049 ,X_MSG_COUNT => X_MSG_COUNT
1050 ,X_MSG_DATA => X_MSG_DATA
1051 ,X_RETURN_STATUS => X_RETURN_STATUS);
1052
1053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1054 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1055 P_API_NAME => L_API_NAME
1056 ,P_PKG_NAME => G_PKG_NAME
1057 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1058 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1059 ,X_MSG_COUNT => X_MSG_COUNT
1060 ,X_MSG_DATA => X_MSG_DATA
1061 ,X_RETURN_STATUS => X_RETURN_STATUS);
1062
1063 WHEN OTHERS THEN
1064 Rollback to Update_Packlist_Sts_Qty_PUB;
1065 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1066 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1067 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1068 fnd_msg_pub.add;
1069 fnd_msg_pub.count_and_get
1070 ( p_count => x_msg_count
1071 , p_data => x_msg_data);
1072 x_return_status := fnd_api.g_ret_sts_error;
1073
1074 END update_packlist_header_sts;
1075
1076 END CSP_PL_SHIP_UTIL;