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