1 PACKAGE BODY WSH_CC_REQUEST_SETUPS_PKG AS
2 /* $Header: WSHRSTHB.pls 115.4 2002/06/03 12:31:57 pkm ship $ */
3 -- Global constant holding the package name
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_CC_REQUEST_SETUPS_PKG';
5
6 /*----------------------------------------------------------*/
7 /* Insert_Row Procedure */
8 /*----------------------------------------------------------*/
9 /* --
10 -- Purpose
11 -- Insert a row into WSH_CC_REQUEST_SETUPS entity
12 --
13 -- Input Parameters
14 -- p_api_version
15 -- API version number (current version is 1.0)
16 -- p_init_msg_list (optional, default FND_API.G_FALSE)
17 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
18 -- if set to FND_API.G_TRUE
19 -- initialize error message list
20 -- if set to FND_API.G_FALSE - not initialize error
21 -- message list
22 -- p_commit (optional, default FND_API.G_FALSE)
23 -- whether or not to commit the changes to database
24 --
25 -- Input parameters for clear cross setups informations
26 --
27 -- Output Parameters
28 -- x_return_status
29 -- if the process succeeds, the value is
30 -- fnd_api.g_ret_sts_success;
31 -- if there is an expected error, the value is
32 -- fnd_api.g_ret_sts_error;
33 -- if there is an unexpected error, the value is
34 -- fnd_api.g_ret_sts_unexp_error;
35 -- x_msg_count
36 -- if there is one or more errors, the number of error messages
37 -- in the buffer
38 -- x_msg_data
39 -- if there is one and only one error, the error message
40 -- (See fnd_api package for more details about the above output parameters)
41 -- p_CC_REQUEST_SEQUENCE_ID - Clear Cross Request sequence Id ( PK)
42 --*/
43
44 PROCEDURE Insert_Row
45 (
46 p_api_version IN NUMBER ,
47 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
48 p_commit IN VARCHAR2 := fnd_api.g_false ,
49 x_return_status OUT VARCHAR2 ,
50 x_msg_count OUT NUMBER ,
51 x_msg_data OUT VARCHAR2 ,
52 p_APPLICATION_ID IN NUMBER,
53 p_MASTER_ORGANIZATION_ID IN NUMBER,
54 p_ORGANIZATION_ID IN NUMBER default null,
55 p_APPLICATION_USER_ID IN NUMBER default null,
56 p_REQUEST_TYPE_CODE IN VARCHAR2,
57 p_REQUEST_VERSION IN VARCHAR2,
58 p_REQUEST_LANGUAGE IN VARCHAR2,
59 p_REQUEST_DATE_FORMAT IN VARCHAR2,
60 p_REQUEST_DEPLOYMENT_MODE IN VARCHAR2,
61 p_REQUEST_HANDLER IN VARCHAR2,
62 p_REQUEST_OUTPUT_TYPE IN VARCHAR2,
63 p_REQUEST_INCLUDE_FLAG IN VARCHAR2,
64 p_ECCN_CATG_SET_ID IN NUMBER,
65 p_HTS_CATG_SET_ID IN NUMBER,
66 p_ADDL_CATG_SET_ID IN NUMBER,
67 p_ADDITIONAL_ECE_COUNTRY_CHECK IN VARCHAR2,
68 p_CC_REQUEST_SEQUENCE_ID OUT NUMBER
69 )
70 IS
71 l_CC_REQUEST_SEQUENCE_ID NUMBER ;
72 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
73 l_api_version number := 1.0;
74 begin
75 --dbms_output.put_line('begin api');
76 -- Standard Start of API savepoint
77 SAVEPOINT WSH_CC_REQUEST_SETUPS_PKG;
78 -- Standard call to check for call compatibility.
79 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
80 p_api_version ,
81 l_api_name ,
82 G_PKG_NAME )
83 THEN
84 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
85 FND_MSG_PUB.ADD;
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87 END IF;
88 -- Initialize message list if p_init_msg_list is set to TRUE.
89 IF FND_API.to_Boolean( p_init_msg_list ) THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92 -- Initialize API return status to success
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94 --dbms_output.put_line('begin api-2');
95
96 select wsh_cc_request_setups_s.nextval into l_CC_Request_Sequence_ID from dual;
97 /* Validate input parameters if any */
98
99 -- Insert a row into wsh_cc_users entity with all detail information
100 INSERT INTO Wsh_cc_Request_Setups
101 ( CC_REQUEST_SEQUENCE_ID
102 ,APPLICATION_ID
103 ,MASTER_ORGANIZATION_ID
104 ,ORGANIZATION_ID
105 ,APPLICATION_USER_ID
106 ,REQUEST_TYPE_CODE
107 ,REQUEST_VERSION
108 ,REQUEST_LANGUAGE
109 ,REQUEST_DATE_FORMAT
110 ,REQUEST_DEPLOYMENT_MODE
111 ,REQUEST_HANDLER
112 ,REQUEST_OUTPUT_TYPE
113 ,REQUEST_INCLUDE_FLAG
114 ,ECCN_CATG_SET_ID
115 ,HTS_CATG_SET_ID
116 ,ADDL_CATG_SET_ID
117 ,ADDITIONAL_ECE_COUNTRY_CHECK
118 ,CREATION_DATE
119 ,CREATED_BY
120 ,LAST_UPDATE_DATE
121 ,LAST_UPDATED_BY
122 ,LAST_UPDATE_LOGIN
123 )
124 values (
125 l_CC_REQUEST_SEQUENCE_ID
126 ,p_APPLICATION_ID
127 ,p_MASTER_ORGANIZATION_ID
128 ,p_ORGANIZATION_ID
129 ,p_APPLICATION_USER_ID
130 ,p_REQUEST_TYPE_CODE
131 ,p_REQUEST_VERSION
132 ,p_REQUEST_LANGUAGE
133 ,p_REQUEST_DATE_FORMAT
134 ,p_REQUEST_DEPLOYMENT_MODE
135 ,p_REQUEST_HANDLER
136 ,p_REQUEST_OUTPUT_TYPE
137 ,p_REQUEST_INCLUDE_FLAG
138 ,p_ECCN_CATG_SET_ID
139 ,p_HTS_CATG_SET_ID
140 ,p_ADDL_CATG_SET_ID
141 ,p_ADDITIONAL_ECE_COUNTRY_CHECK
142 ,sysdate
143 ,FND_GLOBAL.user_id
144 ,sysdate
145 ,FND_GLOBAL.user_id
146 ,FND_GLOBAL.login_id
147 ) ;
148 IF SQL%NOTFOUND THEN
149 FND_MESSAGE.SET_NAME('WSH', 'WSH_INSERT_FAILED');
150 FND_MSG_PUB.ADD;
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 RAISE FND_API.G_EXC_ERROR ;
153 END IF;
154 --dbms_output.put_line('Seq Id got it '||l_CC_REQUEST_SEQUENCE_ID||'success');
155 x_return_status := fnd_api.g_ret_sts_success;
156 p_CC_REQUEST_SEQUENCE_ID := l_CC_REQUEST_SEQUENCE_ID;
157 -- End of API body
158 -- Standard check of p_commit.
159 IF FND_API.To_Boolean( p_commit ) THEN
160 COMMIT WORK;
161 END IF;
162 -- Standard call to get message count and if count is 1,
163 -- get message info.
164 FND_MSG_PUB.Count_And_Get
165 ( p_count => x_msg_count,
166 p_data => x_msg_data
167 );
168
169 EXCEPTION
170 WHEN FND_API.G_EXC_ERROR THEN
171 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 FND_MSG_PUB.Count_And_Get
174 ( p_count => x_msg_count,
175 p_data => x_msg_data
176 );
177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
178 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
180 FND_MSG_PUB.Count_And_Get
181 ( p_count => x_msg_count,
182 p_data => x_msg_data
183 );
184 WHEN OTHERS THEN
185 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
187 IF FND_MSG_PUB.Check_Msg_Level
188 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
189 THEN
190 FND_MSG_PUB.Add_Exc_Msg
191 ( G_PKG_NAME ,
192 l_api_name
193 );
194 END IF;
195 FND_MSG_PUB.Count_And_Get
196 ( p_count => x_msg_count,
197 p_data => x_msg_data
198 );
199 End Insert_Row;
200 /*----------------------------------------------------------*/
201 /* Update_Row Procedure */
202 /*----------------------------------------------------------*/
203 /* --
204 -- Purpose
205 -- Update a row into WSH_CC_request_setups entity
206 -- for the given cc request seq id
207 --
208 -- Input Parameters
209 -- p_api_version
210 -- API version number (current version is 1.0)
211 -- p_init_msg_list (optional, default FND_API.G_FALSE)
212 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
213 -- if set to FND_API.G_TRUE
214 -- initialize error message list
215 -- if set to FND_API.G_FALSE - not initialize error
216 -- message list
217 -- p_commit (optional, default FND_API.G_FALSE)
218 -- whether or not to commit the changes to database
219 --
220 -- Input parameters for clear cross request setups informations
221 --
222 --
223 -- Output Parameters
224 -- x_return_status
225 -- if the process succeeds, the value is
226 -- fnd_api.g_ret_sts_success;
227 -- if there is an expected error, the value is
228 -- fnd_api.g_ret_sts_error;
229 -- if there is an unexpected error, the value is
230 -- fnd_api.g_ret_sts_unexp_error;
231 -- x_msg_count
232 -- if there is one or more errors, the number of error messages
233 -- in the buffer
234 -- x_msg_data
235 -- if there is one and only one error, the error message
236 -- (See fnd_api package for more details about the above output parameters)
237
238 --*/
239 PROCEDURE Update_Row
240 (
241 p_api_version IN NUMBER ,
242 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
243 p_commit IN VARCHAR2 := fnd_api.g_false ,
244 x_return_status OUT VARCHAR2 ,
245 x_msg_count OUT NUMBER ,
246 x_msg_data OUT VARCHAR2 ,
247 p_APPLICATION_ID IN NUMBER,
248 p_MASTER_ORGANIZATION_ID IN NUMBER,
249 p_ORGANIZATION_ID IN NUMBER default fnd_api.g_miss_num,
250 p_APPLICATION_USER_ID IN NUMBER default fnd_api.g_miss_num,
251 p_REQUEST_TYPE_CODE IN VARCHAR2,
252 p_REQUEST_VERSION IN VARCHAR2,
253 p_REQUEST_LANGUAGE IN VARCHAR2,
254 p_REQUEST_DATE_FORMAT IN VARCHAR2,
255 p_REQUEST_DEPLOYMENT_MODE IN VARCHAR2,
256 p_REQUEST_HANDLER IN VARCHAR2,
257 p_REQUEST_OUTPUT_TYPE IN VARCHAR2,
258 p_REQUEST_INCLUDE_FLAG IN VARCHAR2,
259 p_ECCN_CATG_SET_ID IN NUMBER,
260 p_HTS_CATG_SET_ID IN NUMBER,
261 p_ADDL_CATG_SET_ID IN NUMBER,
262 p_ADDITIONAL_ECE_COUNTRY_CHECK IN VARCHAR2,
263 p_CC_REQUEST_SEQUENCE_ID IN NUMBER
264 )
265 IS
266 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row' ;
267 l_api_version number := 1.0;
268 BEGIN
269 --dbms_output.put_line('begin');
270 -- Standard Start of API savepoint
271 SAVEPOINT WSH_CC_REQUEST_SETUPS_PKG;
272 -- Standard call to check for call compatibility.
273 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
274 p_api_version ,
275 l_api_name ,
276 G_PKG_NAME )
277 THEN
278 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
279 FND_MSG_PUB.ADD;
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282 -- Initialize message list if p_init_msg_list is set to TRUE.
283 IF FND_API.to_Boolean( p_init_msg_list ) THEN
284 FND_MSG_PUB.initialize;
285 END IF;
286 -- Initialize API return status to success
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288
289 -- Update a row into wsh_cc_users entity with all detail information
290 -- for the given cc seq id
291
292 update wsh_cc_request_setups
293 SET
294 MASTER_ORGANIZATION_ID = p_MASTER_ORGANIZATION_ID
295 ,APPLICATION_ID = p_APPLICATION_ID
296 ,ORGANIZATION_ID = decode(p_ORGANIZATION_ID,fnd_api.g_miss_num,
297 ORGANIZATION_ID,p_ORGANIZATION_ID)
298 ,APPLICATION_USER_ID = decode(p_APPLICATION_USER_ID,fnd_api.g_miss_num,
299 APPLICATION_USER_ID,p_APPLICATION_USER_ID)
300 ,REQUEST_TYPE_CODE = p_REQUEST_TYPE_CODE
301 ,REQUEST_VERSION = p_REQUEST_VERSION
302 ,REQUEST_LANGUAGE = p_REQUEST_LANGUAGE
303 ,REQUEST_DATE_FORMAT = p_REQUEST_DATE_FORMAT
304 ,REQUEST_DEPLOYMENT_MODE = p_REQUEST_DEPLOYMENT_MODE
305 ,REQUEST_HANDLER = p_REQUEST_HANDLER
306 ,REQUEST_OUTPUT_TYPE = p_REQUEST_OUTPUT_TYPE
307 ,REQUEST_INCLUDE_FLAG = p_REQUEST_INCLUDE_FLAG
308 ,ECCN_CATG_SET_ID = p_ECCN_CATG_SET_ID
309 ,HTS_CATG_SET_ID = p_HTS_CATG_SET_ID
310 ,ADDL_CATG_SET_ID = p_ADDL_CATG_SET_ID
311 ,ADDITIONAL_ECE_COUNTRY_CHECK = p_ADDITIONAL_ECE_COUNTRY_CHECK
312 ,LAST_UPDATE_DATE = sysdate
313 ,LAST_UPDATED_BY = FND_GLOBAL.user_id
314 ,LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
315 where cc_request_sequence_id = p_cc_request_sequence_id ;
316 IF SQL%NOTFOUND THEN
317 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_FAILED');
318 FND_MSG_PUB.ADD;
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 RAISE FND_API.G_EXC_ERROR ;
321 END IF;
322 --dbms_output.put_line('begin-5');
323 x_return_status := fnd_api.g_ret_sts_success;
324
325 -- End of API body
326 -- Standard check of p_commit.
327 IF FND_API.To_Boolean( p_commit ) THEN
328 COMMIT WORK;
329 END IF;
330 -- Standard call to get message count and if count is 1,
331 -- get message info.
332 FND_MSG_PUB.Count_And_Get
333 ( p_count => x_msg_count,
334 p_data => x_msg_data
335 );
336
337 EXCEPTION
338 WHEN FND_API.G_EXC_ERROR THEN
339 --dbms_output.put_line(sqlerrm);
340 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
341 x_return_status := FND_API.G_RET_STS_ERROR;
342 FND_MSG_PUB.Count_And_Get
343 ( p_count => x_msg_count,
344 p_data => x_msg_data
345 );
346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
350 FND_MSG_PUB.Count_And_Get
347 --dbms_output.put_line(sqlerrm);
348 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
351 ( p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354 WHEN OTHERS THEN
355 --dbms_output.put_line(sqlerrm);
356 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
357 x_return_status := 'W';
358 IF FND_MSG_PUB.Check_Msg_Level
359 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
360 THEN
361 FND_MSG_PUB.Add_Exc_Msg
362 ( G_PKG_NAME ,
363 l_api_name
364 );
365 END IF;
366 FND_MSG_PUB.Count_And_Get
367 ( p_count => x_msg_count,
368 p_data => x_msg_data
369 );
370
371 End Update_Row;
372 /*----------------------------------------------------------*/
373 /* Delete_Row Procedure */
374 /*----------------------------------------------------------*/
375 /* --
376 -- Purpose
377 -- Delete a row from WSH_CC_Request_Setups entity
378 -- for the given request seq id
379 --
380 -- Input Parameters
381 -- p_api_version
382 -- API version number (current version is 1.0)
383 -- p_init_msg_list (optional, default FND_API.G_FALSE)
384 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
385 -- if set to FND_API.G_TRUE
386 -- initialize error message list
387 -- if set to FND_API.G_FALSE - not initialize error
388 -- message list
389 -- p_commit (optional, default FND_API.G_FALSE)
390 -- whether or not to commit the changes to database
391 --
392 -- Input parameters for clear cross users informations
393 -- p_CC_REQUEST_SEQUENCE_ID -- CC Request Seq Id
394 --
395 --
396 -- Output Parameters
397 -- x_return_status
398 -- if the process succeeds, the value is
399 -- fnd_api.g_ret_sts_success;
400 -- if there is an expected error, the value is
401 -- fnd_api.g_ret_sts_error;
402 -- if there is an unexpected error, the value is
403 -- fnd_api.g_ret_sts_unexp_error;
404 -- x_msg_count
405 -- if there is one or more errors, the number of error messages
406 -- in the buffer
407 -- x_msg_data
408 -- if there is one and only one error, the error message
409 -- (See fnd_api package for more details about the above output parameters)
410
411 --*/
412 PROCEDURE Delete_Row
413 (
414 p_api_version IN NUMBER ,
415 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
416 p_commit IN VARCHAR2 := fnd_api.g_false ,
417 x_return_status OUT VARCHAR2 ,
418 x_msg_count OUT NUMBER ,
419 x_msg_data OUT VARCHAR2 ,
420 p_CC_REQUEST_SEQUENCE_ID IN NUMBER
421 )
422 IS
423 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row' ;
424 l_api_version number := 1.0;
425 BEGIN
426 --dbms_output.put_line('begin');
427 -- Standard Start of API savepoint
428 SAVEPOINT WSH_CC_REQUEST_SETUPS_PKG;
429 -- Standard call to check for call compatibility.
430 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
431 p_api_version ,
432 l_api_name ,
433 G_PKG_NAME )
434 THEN
435 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
436 FND_MSG_PUB.ADD;
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 END IF;
439 -- Initialize message list if p_init_msg_list is set to TRUE.
440 IF FND_API.to_Boolean( p_init_msg_list ) THEN
441 FND_MSG_PUB.initialize;
442 END IF;
443 -- Initialize API return status to success
444 x_return_status := FND_API.G_RET_STS_SUCCESS;
445
446 -- Delete a row from wsh_cc_request_setups entity
447 -- for the given request seq id
448
449 DELETE from wsh_cc_request_setups
450 WHERE cc_request_sequence_id = p_cc_request_sequence_id ;
451 IF SQL%NOTFOUND THEN
452 FND_MESSAGE.SET_NAME('WSH', 'WSH_DELETE_FAILED');
453 FND_MSG_PUB.ADD;
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 RAISE FND_API.G_EXC_ERROR ;
456 END IF;
457 --dbms_output.put_line('begin-5');
458 x_return_status := fnd_api.g_ret_sts_success;
459
460 -- End of API body
461 -- Standard check of p_commit.
462 IF FND_API.To_Boolean( p_commit ) THEN
463 COMMIT WORK;
464 END IF;
465 -- Standard call to get message count and if count is 1,
466 -- get message info.
467 FND_MSG_PUB.Count_And_Get
468 ( p_count => x_msg_count,
469 p_data => x_msg_data
470 );
471
472 EXCEPTION
473 WHEN FND_API.G_EXC_ERROR THEN
474 --dbms_output.put_line(sqlerrm);
475 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
476 x_return_status := FND_API.G_RET_STS_ERROR;
477 FND_MSG_PUB.Count_And_Get
478 ( p_count => x_msg_count,
482 --dbms_output.put_line(sqlerrm);
479 p_data => x_msg_data
480 );
481 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
483 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 FND_MSG_PUB.Count_And_Get
486 ( p_count => x_msg_count,
487 p_data => x_msg_data
488 );
489 WHEN OTHERS THEN
490 --dbms_output.put_line(sqlerrm);
491 ROLLBACK TO WSH_CC_REQUEST_SETUPS_PKG;
492 x_return_status := 'W';
493 IF FND_MSG_PUB.Check_Msg_Level
494 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
495 THEN
496 FND_MSG_PUB.Add_Exc_Msg
497 ( G_PKG_NAME ,
498 l_api_name
499 );
500 END IF;
501 FND_MSG_PUB.Count_And_Get
502 ( p_count => x_msg_count,
503 p_data => x_msg_data
504 );
505
506 End Delete_Row;
507 /*----------------------------------------------------------*/
508 /*----------------------------------------------------------*/
509 /* Lock_Row Procedure */
510 /*----------------------------------------------------------*/
511 /* --
512 -- Purpose
513 -- Check Lock a row of WSH_CC_REQUEST_SETUPS entity
514 -- for the given request seq id
515 --
516 -- Input Parameters
517 -- p_api_version
518 -- API version number (current version is 1.0)
519 -- p_init_msg_list (optional, default FND_API.G_FALSE)
520 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
521 -- if set to FND_API.G_TRUE
522 -- initialize error message list
523 -- if set to FND_API.G_FALSE - not initialize error
524 -- message list
525 -- p_commit (optional, default FND_API.G_FALSE)
526 -- whether or not to commit the changes to database
527 --
528 -- Input parameters for clear cross request setups informations
529 --
530 -- Output Parameters
531 -- x_return_status
532 -- if the process succeeds, the value is
533 -- fnd_api.g_ret_sts_success;
534 -- if there is an expected error, the value is
535 -- fnd_api.g_ret_sts_error;
536 -- if there is an unexpected error, the value is
537 -- fnd_api.g_ret_sts_unexp_error;
538 -- x_msg_count
539 -- if there is one or more errors, the number of error messages
540 -- in the buffer
541 -- x_msg_data
542 -- if there is one and only one error, the error message
543 -- (See fnd_api package for more details about the above output parameters)
544
545 --*/
546 PROCEDURE Lock_Row
547 (
548 p_api_version IN NUMBER ,
549 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
550 p_commit IN VARCHAR2 := fnd_api.g_false ,
551 x_return_status OUT VARCHAR2 ,
552 x_msg_count OUT NUMBER ,
553 x_msg_data OUT VARCHAR2 ,
554 p_APPLICATION_ID IN NUMBER,
555 p_MASTER_ORGANIZATION_ID IN NUMBER,
556 p_ORGANIZATION_ID IN NUMBER default fnd_api.g_miss_num,
557 p_APPLICATION_USER_ID IN NUMBER default fnd_api.g_miss_num,
558 p_REQUEST_TYPE_CODE IN VARCHAR2,
559 p_REQUEST_VERSION IN VARCHAR2,
560 p_REQUEST_LANGUAGE IN VARCHAR2,
561 p_REQUEST_DATE_FORMAT IN VARCHAR2,
562 p_REQUEST_DEPLOYMENT_MODE IN VARCHAR2,
563 p_REQUEST_HANDLER IN VARCHAR2,
564 p_REQUEST_OUTPUT_TYPE IN VARCHAR2,
565 p_REQUEST_INCLUDE_FLAG IN VARCHAR2,
566 p_ECCN_CATG_SET_ID IN NUMBER,
567 p_HTS_CATG_SET_ID IN NUMBER,
568 p_ADDL_CATG_SET_ID IN NUMBER,
569 p_ADDITIONAL_ECE_COUNTRY_CHECK IN VARCHAR2,
570 p_CC_REQUEST_SEQUENCE_ID IN NUMBER,
571 p_rowid IN VARCHAR2
572 )
573 IS
574 CURSOR lock_row IS
575 SELECT *
576 FROM wsh_cc_request_setups
577 WHERE rowid = p_rowid
578 FOR UPDATE OF CC_REQUEST_SEQUENCE_ID NOWAIT;
579
580 Recinfo lock_row%ROWTYPE;
581
582 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row' ;
583 l_api_version number := 1.0;
584 BEGIN
585 --dbms_output.put_line('begin');
586 -- Standard Start of API savepoint
587 SAVEPOINT WSH_CC_REQUEST_SETUPS_PKG;
588 -- Standard call to check for call compatibility.
589 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
590 p_api_version ,
591 l_api_name ,
592 G_PKG_NAME )
593 THEN
594 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
595 FND_MSG_PUB.ADD;
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 END IF;
598 -- Initialize message list if p_init_msg_list is set to TRUE.
599 IF FND_API.to_Boolean( p_init_msg_list ) THEN
600 FND_MSG_PUB.initialize;
601 END IF;
602 -- Initialize API return status to success
603 x_return_status := FND_API.G_RET_STS_SUCCESS;
604
605 -- Check Lock a row of wsh_cc_request_setups
609 IF (lock_row%NOTFOUND) THEN
606 OPEN lock_row;
607 FETCH lock_row into Recinfo;
608
610 CLOSE lock_row;
611 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
612 app_exception.raise_exception;
613 END IF;
614 CLOSE lock_row;
615
616 IF (
617 (Recinfo.CC_REQUEST_SEQUENCE_ID = p_CC_REQUEST_SEQUENCE_ID)
618 AND ((Recinfo.master_organization_id =p_master_organization_id) OR
619 ( (Recinfo.master_organization_id is null)
620 AND (p_master_organization_id is null )))
621 AND ((Recinfo.organization_id =p_organization_id) OR
622 ( (Recinfo.organization_id is null)
623 AND (p_organization_id is null )))
624 AND ((Recinfo.application_user_id =p_application_user_id) OR
625 ( (Recinfo.application_user_id is null)
626 AND (p_application_user_id is null )))
627 AND ((Recinfo.request_type_code =p_request_type_code) OR
628 ( (Recinfo.request_type_code is null)
629 AND (p_request_type_code is null )))
630 AND ((Recinfo.request_version =p_request_version) OR
631 ( (Recinfo.request_version is null)
632 AND (p_request_version is null )))
633 AND ((Recinfo.request_language =p_request_language) OR
634 ( (Recinfo.request_language is null)
635 AND (p_request_language is null )))
636 AND ((Recinfo.request_date_format =p_request_date_format) OR
637 ( (Recinfo.request_date_format is null)
638 AND (p_request_date_format is null )))
639 AND ((Recinfo.request_deployment_mode =p_request_deployment_mode) OR
640 ( (Recinfo.request_deployment_mode is null)
641 AND (p_request_deployment_mode is null )))
642
643 AND ((Recinfo.request_handler =p_request_handler) OR
644 ( (Recinfo.request_handler is null)
645 AND (p_request_handler is null )))
646
647 AND ((Recinfo.request_output_type =p_request_output_type) OR
648 ( (Recinfo.request_output_type is null)
649 AND (p_request_output_type is null )))
650
654
651 AND ((Recinfo.request_include_flag =p_request_include_flag) OR
652 ( (Recinfo.request_include_flag is null)
653 AND (p_request_include_flag is null )))
655 AND ((Recinfo.ECCN_CATG_SET_ID =p_ECCN_CATG_SET_ID) OR
656 ( (Recinfo.ECCN_CATG_SET_ID is null)
657 AND (p_ECCN_CATG_SET_ID is null )))
658 AND ((Recinfo.HTS_CATG_SET_ID =p_HTS_CATG_SET_ID) OR
659 ( (Recinfo.HTS_CATG_SET_ID is null)
660 AND (p_HTS_CATG_SET_ID is null )))
661 AND ((Recinfo.ADDL_CATG_SET_ID =p_ADDL_CATG_SET_ID) OR
662 ( (Recinfo.ADDL_CATG_SET_ID is null)
663 AND (p_ADDL_CATG_SET_ID is null )))
664
665 AND ((Recinfo.additional_ece_country_check =p_additional_ece_country_check) OR
666 ( (Recinfo.additional_ece_country_check is null)
667 AND (p_additional_ece_country_check is null )))
668 AND ((Recinfo.application_id=p_application_id) OR
669 ( (Recinfo.application_id is null)
670 AND (p_application_id is null )))
671
672 ) THEN
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674 ELSE
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
677 app_exception.raise_exception;
678 END IF;
679 EXCEPTION
680 WHEN others THEN
681 IF (lock_row%ISOPEN) then
682 close lock_row;
683 End if;
684 End Lock_Row;
685 END WSH_CC_REQUEST_SETUPS_PKG;
[Home] [Help]