[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_VISAPASS_PUB
Source
1 PACKAGE BODY IGS_PE_VISAPASS_PUB AS
2 /* $Header: IGSPE16B.pls 120.1 2005/10/17 02:22:50 appldev noship $ */
3
4 /******************************************************************************
5 || Created By : ssaleem
6 || Created On : 01-Sep-2004
7 || Purpose : This public API is used to update and insert records to
8 || Visa, Passport and Visit Histry tables in IGS
9 ||
10 || Known limitations, enhancements or remarks :
11 || Change History :
12 || Who When What
13 || vredkar 14-Oct-2005 Bug#4654248,replaced generic duplicate/overlap
14 || exists messages with component specific messages
15 ******************************************************************************/
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):='IGS_PE_VISAPASS_PUB';
18
19 -- Start of comments
20 -- API name : Create_Visa
21 -- Type : Public
22 -- Function :
23 -- Pre-reqs : None.
24 -- Parameters :
25 -- IN : p_api_version IN NUMBER Required
26 -- p_init_msg_list IN VARCHAR2 Optional
27 -- Default = FND_API.G_FALSE
28 -- p_commit IN VARCHAR2 Optional
29 -- Default = FND_API.G_FALSE
30 -- Default = FND_API.G_VALID_LEVEL_FULL
31 -- p_visa_rec IN visa_rec_type
32 --
33 --
34 -- OUT : x_return_status OUT VARCHAR2(1)
35 -- x_msg_count OUT NUMBER
36 -- x_msg_data OUT VARCHAR2(2000)
37 -- x_visa_id OUT NUMBER
38 --
39 --
40 --
41 -- Version : Current version x.x
42 -- Changed....
43 -- previous version y.y
44 -- Changed....
45 -- .
46 -- .
47 -- Initial version 1.0
48 --
49 -- Notes :
50 --
51 -- End of comments
52
53 PROCEDURE Create_Visa
54 ( p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
56 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2,
60 p_visa_rec IN visa_rec_type,
61 x_visa_id OUT NOCOPY NUMBER
62 )
63 IS
64
65
66 l_api_name CONSTANT VARCHAR2(30) := 'Create_Visa';
67 l_api_version CONSTANT NUMBER := 1.0;
68
69 l_error_code igs_pe_visa_int.error_code%TYPE;
70 l_message_name VARCHAR2(30);
71 l_app VARCHAR2(50);
72 l_rowid ROWID := NULL;
73
74 l_visa_rec visa_rec_type;
75 BEGIN
76 SAVEPOINT Create_Visa_PUB;
77 IF NOT FND_API.Compatible_API_Call ( l_api_version,
78 p_api_version,
79 l_api_name,
80 G_PKG_NAME )
81 THEN
82 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
83 END IF;
84
85 IF FND_API.to_Boolean( p_init_msg_list ) THEN
86 FND_MSG_PUB.initialize;
87 END IF;
88
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90 -- Start here
91
92 l_visa_rec := p_visa_rec;
93 x_msg_count := 0;
94
95 l_visa_rec.visa_issue_date := TRUNC(l_visa_rec.visa_issue_date);
96 l_visa_rec.visa_expiry_date := TRUNC(l_visa_rec.visa_expiry_date);
97
98
99 IF l_visa_rec.person_id IS NULL OR l_visa_rec.person_id = FND_API.G_MISS_NUM THEN
100 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
101 fnd_message.set_token('PARAM','PERSON_ID');
102 igs_ge_msg_stack.add;
103 app_exception.raise_exception;
104 END IF;
105
106 IF l_visa_rec.visa_type IS NULL OR l_visa_rec.visa_type = FND_API.G_MISS_CHAR THEN
107 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
108 fnd_message.set_token('PARAM','VISA_TYPE');
109 igs_ge_msg_stack.add;
110 app_exception.raise_exception;
111 END IF;
112
113 IF l_visa_rec.visa_number IS NULL OR l_visa_rec.visa_number = FND_API.G_MISS_CHAR THEN
114 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
115 fnd_message.set_token('PARAM','VISA_NUMBER');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END IF;
119
120 IF l_visa_rec.visa_issue_date IS NULL OR l_visa_rec.visa_issue_date = FND_API.G_MISS_DATE THEN
121 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
122 fnd_message.set_token('PARAM','VISA_ISSUE_DATE');
123 igs_ge_msg_stack.add;
124 app_exception.raise_exception;
125 END IF;
126
127 IF l_visa_rec.visa_expiry_date IS NULL OR l_visa_rec.visa_expiry_date = FND_API.G_MISS_DATE THEN
128 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
129 fnd_message.set_token('PARAM','visa_expiry_date');
130 igs_ge_msg_stack.add;
131 app_exception.raise_exception;
132 END IF;
133
134 IF l_visa_rec.visa_issuing_post = FND_API.G_MISS_CHAR THEN
135 l_visa_rec.visa_issuing_post := NULL;
136 END IF;
137
138 IF l_visa_rec.passport_id = FND_API.G_MISS_NUM THEN
139 l_visa_rec.passport_id := NULL;
140 END IF;
141
142 IF l_visa_rec.agent_org_unit_cd = FND_API.G_MISS_CHAR THEN
143 l_visa_rec.agent_org_unit_cd := NULL;
144 END IF;
145
146 IF l_visa_rec.agent_person_id = FND_API.G_MISS_NUM THEN
147 l_visa_rec.agent_person_id := NULL;
148 END IF;
149
150 IF l_visa_rec.visa_number = FND_API.G_MISS_CHAR THEN
151 l_visa_rec.visa_number := NULL;
152 END IF;
153
154 IF l_visa_rec.agent_contact_name = FND_API.G_MISS_CHAR THEN
155 l_visa_rec.agent_contact_name := NULL;
156 END IF;
157
158 IF l_visa_rec.attribute_category = FND_API.G_MISS_CHAR THEN
159 l_visa_rec.attribute_category := NULL;
160 END IF;
161
162 IF l_visa_rec.attribute1 = FND_API.G_MISS_CHAR THEN
163 l_visa_rec.attribute1 := NULL;
164 END IF;
165
166 IF l_visa_rec.attribute2 = FND_API.G_MISS_CHAR THEN
167 l_visa_rec.attribute2 := NULL;
168 END IF;
169
170 IF l_visa_rec.attribute4 = FND_API.G_MISS_CHAR THEN
171 l_visa_rec.attribute4 := NULL;
172 END IF;
173
174 IF l_visa_rec.attribute5 = FND_API.G_MISS_CHAR THEN
175 l_visa_rec.attribute5 := NULL;
176 END IF;
177
178 IF l_visa_rec.attribute6 = FND_API.G_MISS_CHAR THEN
179 l_visa_rec.attribute6 := NULL;
180 END IF;
181
182 IF l_visa_rec.attribute7 = FND_API.G_MISS_CHAR THEN
183 l_visa_rec.attribute7 := NULL;
184 END IF;
185
186 IF l_visa_rec.attribute8 = FND_API.G_MISS_CHAR THEN
187 l_visa_rec.attribute8 := NULL;
188 END IF;
189
190 IF l_visa_rec.attribute9 = FND_API.G_MISS_CHAR THEN
191 l_visa_rec.attribute9 := NULL;
192 END IF;
193
194 IF l_visa_rec.attribute10 = FND_API.G_MISS_CHAR THEN
195 l_visa_rec.attribute10 := NULL;
196 END IF;
197
198 IF l_visa_rec.attribute11 = FND_API.G_MISS_CHAR THEN
199 l_visa_rec.attribute11 := NULL;
200 END IF;
201
202 IF l_visa_rec.attribute12 = FND_API.G_MISS_CHAR THEN
203 l_visa_rec.attribute12 := NULL;
204 END IF;
205
206 IF l_visa_rec.attribute13 = FND_API.G_MISS_CHAR THEN
207 l_visa_rec.attribute13 := NULL;
208 END IF;
209
210 IF l_visa_rec.attribute14 = FND_API.G_MISS_CHAR THEN
211 l_visa_rec.attribute14 := NULL;
212 END IF;
213
214 IF l_visa_rec.attribute15 = FND_API.G_MISS_CHAR THEN
215 l_visa_rec.attribute15 := NULL;
216 END IF;
217
218 IF l_visa_rec.attribute16 = FND_API.G_MISS_CHAR THEN
219 l_visa_rec.attribute16 := NULL;
220 END IF;
221
222 IF l_visa_rec.attribute17 = FND_API.G_MISS_CHAR THEN
223 l_visa_rec.attribute17 := NULL;
224 END IF;
225
226 IF l_visa_rec.attribute18 = FND_API.G_MISS_CHAR THEN
227 l_visa_rec.attribute18 := NULL;
228 END IF;
229
230 IF l_visa_rec.attribute19 = FND_API.G_MISS_CHAR THEN
231 l_visa_rec.attribute19 := NULL;
232 END IF;
233
234 IF l_visa_rec.attribute20 = FND_API.G_MISS_CHAR THEN
235 l_visa_rec.attribute20 := NULL;
236 END IF;
237
238 IF l_visa_rec.visa_issuing_country = FND_API.G_MISS_CHAR THEN
239 l_visa_rec.visa_issuing_country := NULL;
240 END IF;
241
242 IF igs_ad_imp_026.validate_visa_pub(api_visa_rec => l_visa_rec, p_err_code => l_error_code) THEN
243
244 IGS_PE_VISA_PKG.INSERT_ROW(
245 X_ROWID => l_rowid,
246 X_VISA_ID => x_visa_id,
247 X_PERSON_ID => l_visa_rec.person_id,
248 X_VISA_TYPE => l_visa_rec.VISA_TYPE ,
249 X_VISA_NUMBER => l_visa_rec.VISA_NUMBER,
250 X_VISA_ISSUE_DATE => l_visa_rec.VISA_ISSUE_DATE ,
251 X_VISA_EXPIRY_DATE => l_visa_rec.VISA_EXPIRY_DATE,
252 X_VISA_CATEGORY => NULL ,
253 X_VISA_ISSUING_POST => l_visa_rec.VISA_ISSUING_POST,
254 X_PASSPORT_ID => l_visa_rec.PASSPORT_ID,
255 X_AGENT_ORG_UNIT_CD => l_visa_rec.AGENT_ORG_UNIT_CD ,
256 X_AGENT_PERSON_ID => l_visa_rec.AGENT_PERSON_ID ,
257 X_AGENT_CONTACT_NAME => l_visa_rec.AGENT_CONTACT_NAME ,
258 X_ATTRIBUTE_CATEGORY => l_visa_rec.ATTRIBUTE_CATEGORY ,
259 X_ATTRIBUTE1 => l_visa_rec.ATTRIBUTE1 ,
260 X_ATTRIBUTE2 => l_visa_rec.ATTRIBUTE2 ,
261 X_ATTRIBUTE3 => l_visa_rec.ATTRIBUTE3 ,
262 X_ATTRIBUTE4 => l_visa_rec.ATTRIBUTE4 ,
263 X_ATTRIBUTE5 => l_visa_rec.ATTRIBUTE5 ,
264 X_ATTRIBUTE6 => l_visa_rec.ATTRIBUTE6 ,
265 X_ATTRIBUTE7 => l_visa_rec.ATTRIBUTE7 ,
266 X_ATTRIBUTE8 => l_visa_rec.ATTRIBUTE8 ,
267 X_ATTRIBUTE9 => l_visa_rec.ATTRIBUTE9 ,
268 X_ATTRIBUTE10 => l_visa_rec.ATTRIBUTE10 ,
269 X_ATTRIBUTE11 => l_visa_rec.ATTRIBUTE11 ,
270 X_ATTRIBUTE12 => l_visa_rec.ATTRIBUTE12 ,
271 X_ATTRIBUTE13 => l_visa_rec.ATTRIBUTE13 ,
272 X_ATTRIBUTE14 => l_visa_rec.ATTRIBUTE14 ,
273 X_ATTRIBUTE15 => l_visa_rec.ATTRIBUTE15 ,
274 X_ATTRIBUTE16 => l_visa_rec.ATTRIBUTE16 ,
275 X_ATTRIBUTE17 => l_visa_rec.ATTRIBUTE17 ,
276 X_ATTRIBUTE18 => l_visa_rec.ATTRIBUTE18 ,
277 X_ATTRIBUTE19 => l_visa_rec.ATTRIBUTE19 ,
278 X_ATTRIBUTE20 => l_visa_rec.ATTRIBUTE20 ,
279 x_visa_issuing_country => l_visa_rec.visa_issuing_country);
280 ELSE
281 RAISE FND_API.G_EXC_ERROR;
282 END IF;
283
284 -- End Here
285
286 IF l_error_code = 'E555' THEN
287 x_msg_count := 1;
288 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
289 END IF;
290
291
292 IF FND_API.To_Boolean( p_commit ) THEN
293 COMMIT WORK;
294 END IF;
295
296 EXCEPTION
297 WHEN FND_API.G_EXC_ERROR THEN
298 ROLLBACK TO Create_Visa_PUB;
299 x_return_status := FND_API.G_RET_STS_ERROR ;
300 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
301 x_msg_count := 1;
302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
303 ROLLBACK TO Create_Visa_PUB;
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
305 FND_MSG_PUB.Count_And_Get
306 ( p_count => x_msg_count ,
307 p_data => x_msg_data
308 );
309 WHEN OTHERS THEN
310 ROLLBACK TO Create_Visa_PUB;
311
312 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
313 x_msg_count := 1;
314
315 IF l_message_name IN('IGS_PE_VIS_ASOC_PASS_EXP','IGS_FOREIGN_KEY_REFERENCE','IGS_PE_VISA_DATE_OVERLAP','IGS_EN_INV','IGS_PS_LGCY_MANDATORY', 'IGS_PE_VIPS_UPD_ERR') THEN
316 x_return_status := FND_API.G_RET_STS_ERROR ;
317 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
318 ELSIF l_message_name = 'FORM_RECORD_DELETED' THEN
319 x_return_status := FND_API.G_RET_STS_ERROR ;
320 fnd_message.set_name ('IGS', 'IGS_EN_INV');
321 fnd_message.set_token('PARAM','PERSON_ID');
322 igs_ge_msg_stack.add;
323 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F');
324 ELSIF l_message_name = 'IGS_PE_VISA_DUP_EXISTS' THEN
325 x_return_status := FND_API.G_RET_STS_ERROR ;
326 fnd_message.set_name ('IGS', 'IGS_PE_UNIQUE_FAILED');
327 fnd_message.set_token('COLUMN','PERSON_ID,VISA_TYPE,VISA_ISSUE_DATE');
328 igs_ge_msg_stack.add;
329 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F');
330 ELSE
331 x_msg_data := SQLERRM;
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
333 END IF;
334
335 END Create_Visa;
336
337
338
339
340 -- Start of comments
341 -- API name : Update_Visa
342 -- Type : Public
343 -- Function :
344 -- Pre-reqs : None.
345 -- Parameters :
346 -- IN : p_api_version IN NUMBER Required
347 -- p_init_msg_list IN VARCHAR2 Optional
348 -- Default = FND_API.G_FALSE
349 -- p_commit IN VARCHAR2 Optional
350 -- Default = FND_API.G_FALSE
351 -- Default = FND_API.G_VALID_LEVEL_FULL
352 -- p_visa_rec IN visa_rec_type
353 --
354 --
355 -- OUT : x_return_status OUT VARCHAR2(1)
356 -- x_msg_count OUT NUMBER
357 -- x_msg_data OUT VARCHAR2(2000)
358 --
359 --
360 --
361 -- Version : Current version x.x
362 -- Changed....
363 -- previous version y.y
364 -- Changed....
365 -- .
366 -- .
367 -- Initial version 1.0
368 --
369 -- Notes :
370 --
371 -- End of comments
372
373 PROCEDURE Update_Visa
374 ( p_api_version IN NUMBER,
375 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
376 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_msg_count OUT NOCOPY NUMBER,
379 x_msg_data OUT NOCOPY VARCHAR2,
380 p_visa_rec IN visa_rec_type
381 )
382 IS
383
384 CURSOR null_handlng_cur(cp_visa_rec IN visa_rec_type) IS
385 SELECT rowid,
386 visa_id,person_id,visa_type, visa_number, visa_issue_date, visa_expiry_date,visa_category,
387 visa_issuing_post,passport_id, agent_org_unit_cd, agent_person_id, agent_contact_name,
388 attribute_category,attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
389 attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13,
390 attribute14, attribute15, attribute16, attribute17, attribute18, attribute19, attribute20,
391 visa_issuing_country
392 FROM IGS_PE_VISA
393 WHERE VISA_ID = cp_visa_rec.visa_id FOR UPDATE NOWAIT;
394
395 dup_visa_rec null_handlng_cur%ROWTYPE;
396 l_error_code igs_pe_visa_int.error_code%TYPE;
397
398 l_message_name VARCHAR2(30);
399 l_app VARCHAR2(50);
400
401 l_api_name CONSTANT VARCHAR2(30) := 'Update_Visa';
402 l_api_version CONSTANT NUMBER := 1.0;
403
404 l_visa_rec visa_rec_type;
405
406 E_RESOURCE_BUSY EXCEPTION;
407 PRAGMA EXCEPTION_INIT(E_RESOURCE_BUSY, -54);
408
409 BEGIN
410 SAVEPOINT Update_Visa_PUB;
411 IF NOT FND_API.Compatible_API_Call ( l_api_version,
412 p_api_version,
413 l_api_name,
414 G_PKG_NAME )
415 THEN
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417 END IF;
418
419 IF FND_API.to_Boolean( p_init_msg_list ) THEN
420 FND_MSG_PUB.initialize;
421 END IF;
422
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 -- Start here
426
427 l_visa_rec := p_visa_rec;
428
429 l_visa_rec.visa_issue_date := TRUNC(l_visa_rec.visa_issue_date);
430 l_visa_rec.visa_expiry_date := TRUNC(l_visa_rec.visa_expiry_date);
431
432 IF l_visa_rec.visa_id IS NULL OR l_visa_rec.visa_id = FND_API.G_MISS_NUM THEN
433 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
434 fnd_message.set_token('PARAM','VISA_ID');
435 igs_ge_msg_stack.add;
436 app_exception.raise_exception;
437 END IF;
438
439 OPEN null_handlng_cur(p_visa_rec);
440 FETCH null_handlng_cur INTO dup_visa_rec;
441 IF null_handlng_cur%NOTFOUND THEN
442 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_UPD_ERR');
443 fnd_message.set_token('VALUES','VISA_ID=' || p_visa_rec.visa_id);
444 igs_ge_msg_stack.add;
445 app_exception.raise_exception;
446 END IF;
447 CLOSE null_handlng_cur;
448
449 IF l_visa_rec.person_id = FND_API.G_MISS_NUM THEN
450 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
451 fnd_message.set_token('PARAM','PERSON_ID');
452 igs_ge_msg_stack.add;
453 app_exception.raise_exception;
454 ELSIF l_visa_rec.person_id IS NULL THEN
455 l_visa_rec.person_id := dup_visa_rec.person_id;
456 ELSIF l_visa_rec.person_id <> dup_visa_rec.person_id THEN
457 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_COL_NONUPD');
458 fnd_message.set_token('COLUMN','PERSON_ID');
459 igs_ge_msg_stack.add;
460 app_exception.raise_exception;
461 END IF;
462
463 IF l_visa_rec.visa_type = FND_API.G_MISS_CHAR THEN
464 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
465 fnd_message.set_token('PARAM','VISA_TYPE');
466 igs_ge_msg_stack.add;
467 app_exception.raise_exception;
468 ELSIF l_visa_rec.visa_type IS NULL THEN
469 l_visa_rec.visa_type := dup_visa_rec.visa_type;
470 ELSIF l_visa_rec.visa_type <> dup_visa_rec.visa_type THEN
471 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_COL_NONUPD');
472 fnd_message.set_token('COLUMN','VISA_TYPE');
473 igs_ge_msg_stack.add;
474 app_exception.raise_exception;
475 END IF;
476
477 IF l_visa_rec.visa_number = FND_API.G_MISS_CHAR THEN
478 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
479 fnd_message.set_token('PARAM','VISA_NUMBER');
480 igs_ge_msg_stack.add;
481 app_exception.raise_exception;
482 ELSIF l_visa_rec.visa_number IS NULL THEN
483 l_visa_rec.visa_number := dup_visa_rec.visa_number;
484 END IF;
485
486 IF l_visa_rec.visa_issue_date = FND_API.G_MISS_DATE THEN
487 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
488 fnd_message.set_token('PARAM','VISA_ISSUE_DATE');
489 igs_ge_msg_stack.add;
490 app_exception.raise_exception;
491 ELSIF l_visa_rec.visa_issue_date IS NULL THEN
492 l_visa_rec.visa_issue_date := dup_visa_rec.visa_issue_date;
493 ELSIF l_visa_rec.visa_issue_date <> dup_visa_rec.visa_issue_date THEN
494 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_COL_NONUPD');
495 fnd_message.set_token('COLUMN','VISA_ISSUE_DATE');
496 igs_ge_msg_stack.add;
497 app_exception.raise_exception;
498 END IF;
499
500 IF l_visa_rec.visa_expiry_date = FND_API.G_MISS_DATE THEN
501 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
502 fnd_message.set_token('PARAM','VISA_EXPIRY_DATE');
503 igs_ge_msg_stack.add;
504 app_exception.raise_exception;
505 ELSIF l_visa_rec.visa_expiry_date IS NULL THEN
506 l_visa_rec.visa_expiry_date := dup_visa_rec.visa_expiry_date;
507 END IF;
508
509 IF l_visa_rec.visa_issuing_post = FND_API.G_MISS_CHAR THEN
510 l_visa_rec.visa_issuing_post := NULL;
511 ELSIF l_visa_rec.visa_issuing_post IS NULL THEN
512 l_visa_rec.visa_issuing_post := dup_visa_rec.visa_issuing_post;
513 END IF;
514
515 IF l_visa_rec.passport_id = FND_API.G_MISS_NUM THEN
516 l_visa_rec.passport_id := NULL;
517 ELSIF l_visa_rec.passport_id IS NULL THEN
518 l_visa_rec.passport_id := dup_visa_rec.passport_id;
519 END IF;
520
521 IF l_visa_rec.agent_org_unit_cd = FND_API.G_MISS_CHAR THEN
522 l_visa_rec.agent_org_unit_cd := NULL;
523 ELSIF l_visa_rec.agent_org_unit_cd IS NULL THEN
524 l_visa_rec.agent_org_unit_cd := dup_visa_rec.agent_org_unit_cd ;
525 END IF;
526
527 IF l_visa_rec.agent_person_id = FND_API.G_MISS_NUM THEN
528 l_visa_rec.agent_person_id := NULL;
529 ELSIF l_visa_rec.agent_person_id IS NULL THEN
530 l_visa_rec.agent_person_id := dup_visa_rec.agent_person_id;
531 END IF;
532
533 IF l_visa_rec.agent_contact_name = FND_API.G_MISS_CHAR THEN
534 l_visa_rec.agent_contact_name := NULL;
535 ELSIF l_visa_rec.agent_contact_name IS NULL THEN
536 l_visa_rec.agent_contact_name := dup_visa_rec.agent_contact_name;
537 END IF;
538
539 IF l_visa_rec.attribute_category = FND_API.G_MISS_CHAR THEN
540 l_visa_rec.attribute_category := NULL;
541 ELSIF l_visa_rec.attribute_category IS NULL THEN
542 l_visa_rec.attribute_category := dup_visa_rec.attribute_category;
543 END IF;
544
545 IF l_visa_rec.attribute1 = FND_API.G_MISS_CHAR THEN
546 l_visa_rec.attribute1 := NULL;
547 ELSIF l_visa_rec.attribute1 IS NULL THEN
548 l_visa_rec.attribute1 := dup_visa_rec.attribute1;
549 END IF;
550
551 IF l_visa_rec.attribute2 = FND_API.G_MISS_CHAR THEN
552 l_visa_rec.attribute2 := NULL;
553 ELSIF l_visa_rec.attribute2 IS NULL THEN
554 l_visa_rec.attribute2 := dup_visa_rec.attribute2;
555 END IF;
556
557 IF l_visa_rec.attribute3 = FND_API.G_MISS_CHAR THEN
558 l_visa_rec.attribute3 := NULL;
559 ELSIF l_visa_rec.attribute3 IS NULL THEN
560 l_visa_rec.attribute3 := dup_visa_rec.attribute3;
561 END IF;
562
563 IF l_visa_rec.attribute4 = FND_API.G_MISS_CHAR THEN
564 l_visa_rec.attribute4 := NULL;
565 ELSIF l_visa_rec.attribute4 IS NULL THEN
566 l_visa_rec.attribute4 := dup_visa_rec.attribute4;
567 END IF;
568
569 IF l_visa_rec.attribute5 = FND_API.G_MISS_CHAR THEN
570 l_visa_rec.attribute5 := NULL;
571 ELSIF l_visa_rec.attribute5 IS NULL THEN
572 l_visa_rec.attribute5 := dup_visa_rec.attribute5;
573 END IF;
574
575 IF l_visa_rec.attribute6 = FND_API.G_MISS_CHAR THEN
576 l_visa_rec.attribute6 := NULL;
577 ELSIF l_visa_rec.attribute6 IS NULL THEN
578 l_visa_rec.attribute6 := dup_visa_rec.attribute6;
579 END IF;
580
581 IF l_visa_rec.attribute7 = FND_API.G_MISS_CHAR THEN
582 l_visa_rec.attribute7 := NULL;
583 ELSIF l_visa_rec.attribute7 IS NULL THEN
584 l_visa_rec.attribute7 := dup_visa_rec.attribute7;
585 END IF;
586
587 IF l_visa_rec.attribute8 = FND_API.G_MISS_CHAR THEN
588 l_visa_rec.attribute8 := NULL;
589 ELSIF l_visa_rec.attribute8 IS NULL THEN
590 l_visa_rec.attribute8 := dup_visa_rec.attribute8;
591 END IF;
592
593 IF l_visa_rec.attribute9 = FND_API.G_MISS_CHAR THEN
594 l_visa_rec.attribute9 := NULL;
595 ELSIF l_visa_rec.attribute9 IS NULL THEN
596 l_visa_rec.attribute9 := dup_visa_rec.attribute9;
597 END IF;
598
599 IF l_visa_rec.attribute10 = FND_API.G_MISS_CHAR THEN
600 l_visa_rec.attribute10 := NULL;
601 ELSIF l_visa_rec.attribute10 IS NULL THEN
602 l_visa_rec.attribute10 := dup_visa_rec.attribute10;
603 END IF;
604
605 IF l_visa_rec.attribute11 = FND_API.G_MISS_CHAR THEN
606 l_visa_rec.attribute11 := NULL;
607 ELSIF l_visa_rec.attribute11 IS NULL THEN
608 l_visa_rec.attribute11 := dup_visa_rec.attribute11;
609 END IF;
610
611 IF l_visa_rec.attribute12 = FND_API.G_MISS_CHAR THEN
612 l_visa_rec.attribute12 := NULL;
613 ELSIF l_visa_rec.attribute12 IS NULL THEN
614 l_visa_rec.attribute12 := dup_visa_rec.attribute12;
615 END IF;
616
617 IF l_visa_rec.attribute13 = FND_API.G_MISS_CHAR THEN
618 l_visa_rec.attribute13 := NULL;
619 ELSIF l_visa_rec.attribute13 IS NULL THEN
620 l_visa_rec.attribute13 := dup_visa_rec.attribute13;
621 END IF;
622
623 IF l_visa_rec.attribute14 = FND_API.G_MISS_CHAR THEN
624 l_visa_rec.attribute14 := NULL;
625 ELSIF l_visa_rec.attribute14 IS NULL THEN
626 l_visa_rec.attribute14 := dup_visa_rec.attribute14;
627 END IF;
628
629 IF l_visa_rec.attribute15 = FND_API.G_MISS_CHAR THEN
630 l_visa_rec.attribute15 := NULL;
631 ELSIF l_visa_rec.attribute15 IS NULL THEN
632 l_visa_rec.attribute15 := dup_visa_rec.attribute15;
633 END IF;
634
635 IF l_visa_rec.attribute16 = FND_API.G_MISS_CHAR THEN
636 l_visa_rec.attribute16 := NULL;
637 ELSIF l_visa_rec.attribute16 IS NULL THEN
638 l_visa_rec.attribute16 := dup_visa_rec.attribute16;
639 END IF;
640
641 IF l_visa_rec.attribute17 = FND_API.G_MISS_CHAR THEN
642 l_visa_rec.attribute17 := NULL;
643 ELSIF l_visa_rec.attribute17 IS NULL THEN
644 l_visa_rec.attribute17 := dup_visa_rec.attribute17;
645 END IF;
646
647 IF l_visa_rec.attribute18 = FND_API.G_MISS_CHAR THEN
648 l_visa_rec.attribute18 := NULL;
649 ELSIF l_visa_rec.attribute18 IS NULL THEN
650 l_visa_rec.attribute18 := dup_visa_rec.attribute18;
651 END IF;
652
653 IF l_visa_rec.attribute19 = FND_API.G_MISS_CHAR THEN
654 l_visa_rec.attribute19 := NULL;
655 ELSIF l_visa_rec.attribute19 IS NULL THEN
656 l_visa_rec.attribute19 := dup_visa_rec.attribute19;
657 END IF;
658
659 IF l_visa_rec.attribute20 = FND_API.G_MISS_CHAR THEN
660 l_visa_rec.attribute20 := NULL;
661 ELSIF l_visa_rec.attribute20 IS NULL THEN
662 l_visa_rec.attribute20 := dup_visa_rec.attribute20;
663 END IF;
664
665 IF l_visa_rec.visa_issuing_country = FND_API.G_MISS_CHAR THEN
666 l_visa_rec.visa_issuing_country := NULL;
667 ELSIF l_visa_rec.visa_issuing_country IS NULL THEN
668 l_visa_rec.visa_issuing_country := dup_visa_rec.visa_issuing_country;
669 END IF;
670
671 x_msg_count := 0;
672
673 IF igs_ad_imp_026.validate_visa_pub(api_visa_rec => l_visa_rec, p_err_code => l_error_code) THEN
674
675 IGS_PE_VISA_PKG.UPDATE_ROW (
676 X_ROWID => dup_visa_rec.rowid,
677 X_VISA_ID => l_visa_rec.visa_id,
678 X_PERSON_ID => l_visa_rec.person_id,
679 X_VISA_TYPE => l_visa_rec.visa_type,
680 X_VISA_NUMBER => l_visa_rec.visa_number,
681 X_VISA_ISSUE_DATE => l_visa_rec.visa_issue_date,
682 X_VISA_EXPIRY_DATE => l_visa_rec.visa_expiry_date,
683 X_VISA_CATEGORY => NULL,
684 X_VISA_ISSUING_POST => l_visa_rec.visa_issuing_post,
685 X_PASSPORT_ID => l_visa_rec.passport_id,
686 X_AGENT_ORG_UNIT_CD => l_visa_rec.agent_org_unit_cd,
687 X_AGENT_PERSON_ID => l_visa_rec.agent_person_id,
688 X_AGENT_CONTACT_NAME => l_visa_rec.agent_contact_name,
689 X_ATTRIBUTE_CATEGORY => l_visa_rec.attribute_category,
690 X_ATTRIBUTE1 => l_visa_rec.attribute1,
691 X_ATTRIBUTE2 => l_visa_rec.attribute2,
692 X_ATTRIBUTE3 => l_visa_rec.attribute3,
693 X_ATTRIBUTE4 => l_visa_rec.attribute4,
694 X_ATTRIBUTE5 => l_visa_rec.attribute5,
695 X_ATTRIBUTE6 => l_visa_rec.attribute6,
696 X_ATTRIBUTE7 => l_visa_rec.attribute7,
697 X_ATTRIBUTE8 => l_visa_rec.attribute8,
698 X_ATTRIBUTE9 => l_visa_rec.attribute9,
699 X_ATTRIBUTE10 => l_visa_rec.attribute10,
700 X_ATTRIBUTE11 => l_visa_rec.attribute11,
701 X_ATTRIBUTE12 => l_visa_rec.attribute12,
702 X_ATTRIBUTE13 => l_visa_rec.attribute13,
703 X_ATTRIBUTE14 => l_visa_rec.attribute14,
704 X_ATTRIBUTE15 => l_visa_rec.attribute15,
705 X_ATTRIBUTE16 => l_visa_rec.attribute16,
706 X_ATTRIBUTE17 => l_visa_rec.attribute17,
707 X_ATTRIBUTE18 => l_visa_rec.attribute18,
708 X_ATTRIBUTE19 => l_visa_rec.attribute19,
709 X_ATTRIBUTE20 => l_visa_rec.attribute20,
710 X_visa_issuing_country => l_visa_rec.visa_issuing_country);
711 ELSE
712 RAISE FND_API.G_EXC_ERROR;
713 END IF;
714
715 IF l_error_code = 'E555' THEN
716 x_msg_count := 1;
717 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
718 END IF;
719
720 -- End Here
721
722 IF FND_API.To_Boolean( p_commit ) THEN
723 COMMIT WORK;
724 END IF;
725
726 EXCEPTION
727 WHEN E_RESOURCE_BUSY THEN
728 ROLLBACK TO Update_Visa_PUB;
729 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_LOCKED');
730 igs_ge_msg_stack.add;
731 x_return_status := FND_API.G_RET_STS_ERROR ;
732 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
733 x_msg_count := 1;
734 WHEN FND_API.G_EXC_ERROR THEN
735 ROLLBACK TO Update_Visa_PUB;
736 x_return_status := FND_API.G_RET_STS_ERROR ;
737 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
738 x_msg_count := 1;
739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
740 ROLLBACK TO Update_Visa_PUB;
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
742 FND_MSG_PUB.Count_And_Get
743 ( p_count => x_msg_count ,
744 p_data => x_msg_data
745 );
746 WHEN OTHERS THEN
747 ROLLBACK TO Update_Visa_PUB;
748 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
749
750 x_msg_count := 1;
751 IF l_message_name IN('IGS_PE_VIS_ASOC_PASS_EXP','IGS_PE_VISA_DUP_EXISTS','IGS_PE_VISA_DATE_OVERLAP','IGS_EN_INV','IGS_PS_LGCY_MANDATORY', 'IGS_PE_VIPS_UPD_ERR','IGS_PE_VIPS_COL_NONUPD','FORM_RECORD_DELETED') THEN
752 x_return_status := FND_API.G_RET_STS_ERROR ;
753 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
754 ELSE
755 x_msg_data := SQLERRM;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
757 END IF;
758
759 END Update_Visa;
760
761
762 -- Start of comments
763 -- API name : Create_VisitHistry
764 -- Type : Public
765 -- Function :
766 -- Pre-reqs : None.
767 -- Parameters :
768 -- IN : p_api_version IN NUMBER Required
769 -- p_init_msg_list IN VARCHAR2 Optional
770 -- Default = FND_API.G_FALSE
771 -- p_commit IN VARCHAR2 Optional
772 -- Default = FND_API.G_FALSE
773 -- Default = FND_API.G_VALID_LEVEL_FULL
774 -- p_visit_hstry_rec IN visit_hstry_rec_type
775 --
776 --
777 -- OUT : x_return_status OUT VARCHAR2(1)
778 -- x_msg_count OUT NUMBER
779 -- x_msg_data OUT VARCHAR2(2000)
780 --
781 --
782 --
783 --
784 -- Version : Current version x.x
785 -- Changed....
786 -- previous version y.y
787 -- Changed....
788 -- .
789 -- .
790 -- Initial version 1.0
791 --
792 -- Notes :
793 --
794 -- End of comments
795
796 PROCEDURE Create_VisitHistry
797 ( p_api_version IN NUMBER,
798 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
799 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
800 x_return_status OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2,
803 p_visit_hstry_rec IN visit_hstry_rec_type
804 )
805 IS
806 l_api_name CONSTANT VARCHAR2(30) := 'Create_VisitHistry';
807 l_api_version CONSTANT NUMBER := 1.0;
808
809 l_error_code VARCHAR2(30);
810 l_message_name VARCHAR2(30);
811 l_app VARCHAR2(50);
812 l_rowid ROWID := NULL;
813
814 l_visit_hstry_rec visit_hstry_rec_type;
815
816 BEGIN
817 l_visit_hstry_rec := p_visit_hstry_rec;
818
819 l_visit_hstry_rec.visit_start_date := TRUNC(l_visit_hstry_rec.visit_start_date);
820 l_visit_hstry_rec.visit_end_date := TRUNC(l_visit_hstry_rec.visit_end_date);
821
822 SAVEPOINT Create_VisitHistry_PUB;
823 IF NOT FND_API.Compatible_API_Call ( l_api_version,
824 p_api_version,
825 l_api_name,
826 G_PKG_NAME )
827 THEN
828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
829 END IF;
830
831 IF FND_API.to_Boolean( p_init_msg_list ) THEN
832 FND_MSG_PUB.initialize;
833 END IF;
834
835 x_return_status := FND_API.G_RET_STS_SUCCESS;
836
837 -- Start here
838 x_msg_count := 0;
839
840
841 IF l_visit_hstry_rec.port_of_entry IS NULL OR l_visit_hstry_rec.port_of_entry = FND_API.G_MISS_CHAR THEN
842 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
843 fnd_message.set_token('PARAM','PORT_OF_ENTRY');
844 igs_ge_msg_stack.add;
845 app_exception.raise_exception;
846 END IF;
847
848 IF l_visit_hstry_rec.cntry_entry_form_num IS NULL OR l_visit_hstry_rec.cntry_entry_form_num = FND_API.G_MISS_CHAR THEN
849 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
850 fnd_message.set_token('PARAM','CNTRY_ENTRY_FORM_NUM');
851 igs_ge_msg_stack.add;
852 app_exception.raise_exception;
853 END IF;
854
855 IF l_visit_hstry_rec.visa_id IS NULL OR l_visit_hstry_rec.visa_id = FND_API.G_MISS_NUM THEN
856 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
857 fnd_message.set_token('PARAM','VISA_ID');
858 igs_ge_msg_stack.add;
859 app_exception.raise_exception;
860 END IF;
861
862 IF l_visit_hstry_rec.visit_start_date IS NULL OR l_visit_hstry_rec.visit_start_date = FND_API.G_MISS_DATE THEN
863 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
864 fnd_message.set_token('PARAM','VISIT_START_DATE');
865 igs_ge_msg_stack.add;
866 app_exception.raise_exception;
867 END IF;
868
869 IF l_visit_hstry_rec.visit_end_date = FND_API.G_MISS_DATE THEN
870 l_visit_hstry_rec.visit_end_date := NULL;
871 END IF;
872
873 IF l_visit_hstry_rec.remarks = FND_API.G_MISS_CHAR THEN
874 l_visit_hstry_rec.remarks := NULL;
875 END IF;
876
877 IF igs_ad_imp_026.Validate_visit_histry_pub(api_visit_rec => l_visit_hstry_rec, p_err_code => l_error_code) THEN
878
879 igs_pe_visit_histry_pkg.insert_row(
880 X_ROWID => l_rowid,
881 X_PORT_OF_ENTRY => l_visit_hstry_rec.port_of_entry,
882 X_CNTRY_ENTRY_FORM_NUM => l_visit_hstry_rec.cntry_entry_form_num ,
883 X_VISA_ID => l_visit_hstry_rec.visa_id ,
884 X_VISIT_START_DATE => l_visit_hstry_rec.visit_start_date ,
885 X_VISIT_END_DATE => l_visit_hstry_rec.visit_end_date ,
886 X_REMARKS => l_visit_hstry_rec.remarks,
887 X_ATTRIBUTE_CATEGORY => NULL,
888 X_ATTRIBUTE1 => NULL,
889 X_ATTRIBUTE2 => NULL,
890 X_ATTRIBUTE3 => NULL,
891 X_ATTRIBUTE4 => NULL,
892 X_ATTRIBUTE5 => NULL,
893 X_ATTRIBUTE6 => NULL,
894 X_ATTRIBUTE7 => NULL,
895 X_ATTRIBUTE8 => NULL,
896 X_ATTRIBUTE9 => NULL,
897 X_ATTRIBUTE10 => NULL,
898 X_ATTRIBUTE11 => NULL,
899 X_ATTRIBUTE12 => NULL,
900 X_ATTRIBUTE13 => NULL,
901 X_ATTRIBUTE14 => NULL,
902 X_ATTRIBUTE15 => NULL,
903 X_ATTRIBUTE16 => NULL,
904 X_ATTRIBUTE17 => NULL,
905 X_ATTRIBUTE18 => NULL,
906 X_ATTRIBUTE19 => NULL,
907 X_ATTRIBUTE20 => NULL,
908 X_MODE => 'R');
909 ELSE
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912
913 IF FND_API.To_Boolean( p_commit ) THEN
914 COMMIT WORK;
915 END IF;
916
917 EXCEPTION
918 WHEN FND_API.G_EXC_ERROR THEN
919 ROLLBACK TO Create_VisitHistry_PUB;
920 x_return_status := FND_API.G_RET_STS_ERROR ;
921 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
922 x_msg_count := 1;
923 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
924 ROLLBACK TO Create_VisitHistry_PUB;
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
926 FND_MSG_PUB.Count_And_Get
927 ( p_count => x_msg_count ,
928 p_data => x_msg_data
929 );
930 WHEN OTHERS THEN
931 ROLLBACK TO Create_VisitHistry_PUB;
932
933 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
934
935 x_msg_count := 1;
936
937 IF l_message_name IN('IGS_PE_PORT_DATE_OVERLAP','IGS_EN_INV','IGS_PS_LGCY_MANDATORY', 'IGS_PE_VIPS_UPD_ERR','IGS_PE_VIPS_COL_NONUPD') THEN
938 x_return_status := FND_API.G_RET_STS_ERROR ;
939 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
940 ELSIF l_message_name = 'IGS_PE_PORT_DUP_EXISTS' THEN
941 x_return_status := FND_API.G_RET_STS_ERROR ;
942 fnd_message.set_name ('IGS', 'IGS_PE_UNIQUE_FAILED');
943 fnd_message.set_token('COLUMN','PORT_OF_ENTRY,CNTRY_ENTRY_FORM_NUM');
944 igs_ge_msg_stack.add;
945 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F');
946 ELSE
947 x_msg_data := ' ' || SQLERRM;
948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
949 END IF;
950
951 END Create_VisitHistry;
952
953
954 -- Start of comments
955 -- API name : Update_VisitHistry
956 -- Type : Public
957 -- Function :
958 -- Pre-reqs : None.
959 -- Parameters :
960 -- IN : p_api_version IN NUMBER Required
961 -- p_init_msg_list IN VARCHAR2 Optional
962 -- Default = FND_API.G_FALSE
963 -- p_commit IN VARCHAR2 Optional
964 -- Default = FND_API.G_FALSE
965 -- Default = FND_API.G_VALID_LEVEL_FULL
966 -- p_visit_hstry_rec IN visit_hstry_rec_type
967 --
968 --
969 -- OUT : x_return_status OUT VARCHAR2(1)
970 -- x_msg_count OUT NUMBER
971 -- x_msg_data OUT VARCHAR2(2000)
972 --
973 --
974 --
975 -- Version : Current version x.x
976 -- Changed....
977 -- previous version y.y
978 -- Changed....
979 -- .
980 -- .
981 -- Initial version 1.0
982 --
983 -- Notes :
984 --
985 -- End of comments
986
987 PROCEDURE Update_VisitHistry
988 ( p_api_version IN NUMBER,
989 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
990 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
991 x_return_status OUT NOCOPY VARCHAR2,
992 x_msg_count OUT NOCOPY NUMBER,
993 x_msg_data OUT NOCOPY VARCHAR2,
994 p_visit_hstry_rec IN visit_hstry_rec_type
995 )
996 IS
997
998 CURSOR null_handlng_cur(cp_visit_rec IN visit_hstry_rec_type) IS
999 SELECT rowid,
1000 port_of_entry, cntry_entry_form_num, visa_id, visit_start_date, visit_end_date, remarks
1001 FROM IGS_PE_VISIT_HISTRY
1002 WHERE port_of_entry = cp_visit_rec.port_of_entry AND
1003 cntry_entry_form_num = cp_visit_rec.cntry_entry_form_num FOR UPDATE NOWAIT;
1004
1005 dup_visit_rec null_handlng_cur%ROWTYPE;
1006 l_error_code VARCHAR2(30);
1007
1008 l_message_name VARCHAR2(30);
1009 l_app VARCHAR2(50);
1010
1011 l_api_name CONSTANT VARCHAR2(30) := 'Update_VisitHistry';
1012 l_api_version CONSTANT NUMBER := 1.0;
1013
1014 l_visit_hstry_rec visit_hstry_rec_type;
1015
1016 E_RESOURCE_BUSY EXCEPTION;
1017 PRAGMA EXCEPTION_INIT(E_RESOURCE_BUSY, -54);
1018
1019 BEGIN
1020 l_visit_hstry_rec := p_visit_hstry_rec;
1021
1022 l_visit_hstry_rec.visit_start_date := TRUNC(l_visit_hstry_rec.visit_start_date);
1023 l_visit_hstry_rec.visit_end_date := TRUNC(l_visit_hstry_rec.visit_end_date);
1024
1025 SAVEPOINT Update_VisitHistry_PUB;
1026 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1027 p_api_version,
1028 l_api_name,
1029 G_PKG_NAME )
1030 THEN
1031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1032 END IF;
1033
1034 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1035 FND_MSG_PUB.initialize;
1036 END IF;
1037
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 -- Start here
1041 x_msg_count := 0;
1042
1043 IF p_visit_hstry_rec.port_of_entry IS NULL OR p_visit_hstry_rec.port_of_entry = FND_API.G_MISS_CHAR THEN
1044 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1045 fnd_message.set_token('PARAM','PORT_OF_ENTRY');
1046 igs_ge_msg_stack.add;
1047 app_exception.raise_exception;
1048 END IF;
1049
1050 IF p_visit_hstry_rec.cntry_entry_form_num IS NULL OR p_visit_hstry_rec.cntry_entry_form_num = FND_API.G_MISS_CHAR THEN
1051 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1052 fnd_message.set_token('PARAM','CNTRY_ENTRY_FORM_NUM');
1053 igs_ge_msg_stack.add;
1054 app_exception.raise_exception;
1055 END IF;
1056
1057 OPEN null_handlng_cur(p_visit_hstry_rec);
1058 FETCH null_handlng_cur INTO dup_visit_rec;
1059 IF null_handlng_cur%NOTFOUND THEN
1060 CLOSE null_handlng_cur;
1061 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_UPD_ERR');
1062 fnd_message.set_token('VALUES','CNTRY_ENTRY_FORM_NUM=' || p_visit_hstry_rec.cntry_entry_form_num || ', PORT_OF_ENTRY=' || p_visit_hstry_rec.port_of_entry );
1063 igs_ge_msg_stack.add;
1064 app_exception.raise_exception;
1065 END IF;
1066 CLOSE null_handlng_cur;
1067
1068 IF l_visit_hstry_rec.visa_id = FND_API.G_MISS_NUM THEN
1069 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1070 fnd_message.set_token('PARAM','VISA_ID');
1071 igs_ge_msg_stack.add;
1072 app_exception.raise_exception;
1073 ELSIF l_visit_hstry_rec.visa_id IS NULL THEN
1074 l_visit_hstry_rec.visa_id := dup_visit_rec.visa_id;
1075 END IF;
1076
1077 IF l_visit_hstry_rec.visit_start_date = FND_API.G_MISS_DATE THEN
1078 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1079 fnd_message.set_token('PARAM','VISIT_START_DATE');
1080 igs_ge_msg_stack.add;
1081 app_exception.raise_exception;
1082 ELSIF l_visit_hstry_rec.visit_start_date IS NULL THEN
1083 l_visit_hstry_rec.visit_start_date := dup_visit_rec.visit_start_date;
1084 END IF;
1085
1086 IF l_visit_hstry_rec.visit_end_date = FND_API.G_MISS_DATE THEN
1087 l_visit_hstry_rec.visit_end_date := NULL;
1088 ELSIF l_visit_hstry_rec.visit_end_date IS NULL THEN
1089 l_visit_hstry_rec.visit_end_date := dup_visit_rec.visit_end_date;
1090 END IF;
1091
1092 IF l_visit_hstry_rec.remarks = FND_API.G_MISS_CHAR THEN
1093 l_visit_hstry_rec.remarks := NULL;
1094 ELSIF l_visit_hstry_rec.remarks IS NULL THEN
1095 l_visit_hstry_rec.remarks := dup_visit_rec.remarks;
1096 END IF;
1097
1098 IF igs_ad_imp_026.Validate_visit_histry_pub(api_visit_rec => l_visit_hstry_rec, p_err_code => l_error_code) THEN
1099
1100 igs_pe_visit_histry_pkg.update_row(
1101 X_ROWID => dup_visit_rec.rowid,
1102 X_PORT_OF_ENTRY => l_visit_hstry_rec.port_of_entry,
1103 X_CNTRY_ENTRY_FORM_NUM => l_visit_hstry_rec.cntry_entry_form_num,
1104 X_VISA_ID => l_visit_hstry_rec.visa_id ,
1105 X_VISIT_START_DATE => l_visit_hstry_rec.visit_start_date,
1106 X_VISIT_END_DATE => l_visit_hstry_rec.visit_end_date,
1107 X_REMARKS => l_visit_hstry_rec.remarks,
1108 X_ATTRIBUTE_CATEGORY => NULL,
1109 X_ATTRIBUTE1 => NULL,
1110 X_ATTRIBUTE2 => NULL,
1111 X_ATTRIBUTE3 => NULL,
1112 X_ATTRIBUTE4 => NULL,
1113 X_ATTRIBUTE5 => NULL,
1114 X_ATTRIBUTE6 => NULL,
1115 X_ATTRIBUTE7 => NULL,
1116 X_ATTRIBUTE8 => NULL,
1117 X_ATTRIBUTE9 => NULL,
1118 X_ATTRIBUTE10 => NULL,
1119 X_ATTRIBUTE11 => NULL,
1120 X_ATTRIBUTE12 => NULL,
1121 X_ATTRIBUTE13 => NULL,
1122 X_ATTRIBUTE14 => NULL,
1123 X_ATTRIBUTE15 => NULL,
1124 X_ATTRIBUTE16 => NULL,
1125 X_ATTRIBUTE17 => NULL,
1126 X_ATTRIBUTE18 => NULL,
1127 X_ATTRIBUTE19 => NULL,
1128 X_ATTRIBUTE20 => NULL);
1129 ELSE
1130 RAISE FND_API.G_EXC_ERROR;
1131 END IF;
1132
1133 -- End Here
1134
1135 IF FND_API.To_Boolean( p_commit ) THEN
1136 COMMIT WORK;
1137 END IF;
1138
1139 EXCEPTION
1140 WHEN E_RESOURCE_BUSY THEN
1141 ROLLBACK TO Update_VisitHistry_PUB;
1142 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_LOCKED');
1143 igs_ge_msg_stack.add;
1144 x_return_status := FND_API.G_RET_STS_ERROR ;
1145 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
1146 x_msg_count := 1;
1147 WHEN FND_API.G_EXC_ERROR THEN
1148 ROLLBACK TO Update_VisitHistry_PUB;
1149 x_return_status := FND_API.G_RET_STS_ERROR ;
1150 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
1151 x_msg_count := 1;
1152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1153 ROLLBACK TO Update_VisitHistry_PUB;
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1155 FND_MSG_PUB.Count_And_Get
1156 ( p_count => x_msg_count ,
1157 p_data => x_msg_data
1158 );
1159 WHEN OTHERS THEN
1160 ROLLBACK TO Update_VisitHistry_PUB;
1161
1162 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1163
1164 x_msg_count := 1;
1165
1166 IF l_message_name IN('IGS_PE_PORT_DUP_EXISTS','IGS_PE_PORT_DATE_OVERLAP','IGS_EN_INV','IGS_PS_LGCY_MANDATORY', 'IGS_PE_VIPS_UPD_ERR','IGS_PE_VIPS_COL_NONUPD') THEN
1167 x_return_status := FND_API.G_RET_STS_ERROR ;
1168 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
1169 ELSE
1170 x_msg_data := SQLERRM;
1171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1172 END IF;
1173
1174 END Update_VisitHistry;
1175
1176
1177 -- Start of comments
1178 -- API name : Create_Passport
1179 -- Type : Public
1180 -- Function :
1181 -- Pre-reqs : None.
1182 -- Parameters :
1183 -- IN : p_api_version IN NUMBER Required
1184 -- p_init_msg_list IN VARCHAR2 Optional
1185 -- Default = FND_API.G_FALSE
1186 -- p_commit IN VARCHAR2 Optional
1187 -- Default = FND_API.G_FALSE
1188 -- Default = FND_API.G_VALID_LEVEL_FULL
1189 -- p_passport_rec IN passport_rec_type
1190 --
1191 --
1192 -- OUT : x_return_status OUT VARCHAR2(1)
1193 -- x_msg_count OUT NUMBER
1194 -- x_msg_data OUT VARCHAR2(2000)
1195 -- x_passport_id OUT NUMBER
1196 --
1197 --
1198 --
1199 -- Version : Current version x.x
1200 -- Changed....
1201 -- previous version y.y
1202 -- Changed....
1203 -- .
1204 -- .
1205 -- Initial version 1.0
1206 --
1207 -- Notes :
1208 --
1209 -- End of comments
1210
1211 PROCEDURE Create_Passport
1212 ( p_api_version IN NUMBER,
1213 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1214 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1215 x_return_status OUT NOCOPY VARCHAR2,
1216 x_msg_count OUT NOCOPY NUMBER,
1217 x_msg_data OUT NOCOPY VARCHAR2,
1218 p_passport_rec IN passport_rec_type,
1219 x_passport_id OUT NOCOPY NUMBER
1220 )
1221 IS
1222
1223 l_api_name CONSTANT VARCHAR2(30) := 'Create_Passport';
1224 l_api_version CONSTANT NUMBER := 1.0;
1225
1226 l_error_code VARCHAR2(30);
1227 l_message_name VARCHAR2(30);
1228 l_app VARCHAR2(50);
1229 l_rowid ROWID := NULL;
1230
1231 l_passport_rec passport_rec_type;
1232
1233 BEGIN
1234 l_passport_rec := p_passport_rec;
1235 l_passport_rec.passport_expiry_date := TRUNC(l_passport_rec.passport_expiry_date);
1236 SAVEPOINT Create_Passport_PUB;
1237 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1238 p_api_version,
1239 l_api_name,
1240 G_PKG_NAME )
1241 THEN
1242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1243 END IF;
1244
1245 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1246 FND_MSG_PUB.initialize;
1247 END IF;
1248
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250 -- Start here
1251
1252 x_msg_count := 0;
1253
1254 IF l_passport_rec.person_id IS NULL OR l_passport_rec.person_id = FND_API.G_MISS_NUM THEN
1255 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1256 fnd_message.set_token('PARAM','PERSON_ID');
1257 igs_ge_msg_stack.add;
1258 app_exception.raise_exception;
1259 END IF;
1260
1261 IF l_passport_rec.passport_number IS NULL OR l_passport_rec.passport_number = FND_API.G_MISS_CHAR THEN
1262 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1263 fnd_message.set_token('PARAM','PASSPORT_NUMBER');
1264 igs_ge_msg_stack.add;
1265 app_exception.raise_exception;
1266 END IF;
1267
1268 IF l_passport_rec.passport_expiry_date IS NULL OR l_passport_rec.passport_expiry_date = FND_API.G_MISS_DATE THEN
1269 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1270 fnd_message.set_token('PARAM','PASSPORT_EXPIRY_DATE');
1271 igs_ge_msg_stack.add;
1272 app_exception.raise_exception;
1273 END IF;
1274
1275 IF l_passport_rec.passport_cntry_code IS NULL OR l_passport_rec.passport_cntry_code = FND_API.G_MISS_CHAR THEN
1276 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1277 fnd_message.set_token('PARAM','PASSPORT_CNTRY_CODE');
1278 igs_ge_msg_stack.add;
1279 app_exception.raise_exception;
1280 END IF;
1281
1282 IF igs_ad_imp_026.Validate_passport_pub(api_pass_rec => l_passport_rec, p_err_code => l_error_code) THEN
1283
1284 IGS_PE_PASSPORT_PKG.INSERT_ROW(
1285 X_ROWID => l_rowid,
1286 X_PASSPORT_ID => x_passport_id ,
1287 X_PERSON_ID => l_passport_rec.person_id,
1288 X_PASSPORT_NUMBER => l_passport_rec.passport_number,
1289 X_PASSPORT_EXPIRY_DATE => l_passport_rec.passport_expiry_date,
1290 X_PASSPORT_CNTRY_CODE => l_passport_rec.passport_cntry_code ,
1291 X_ATTRIBUTE_CATEGORY => NULL ,
1292 X_ATTRIBUTE1 => NULL,
1293 X_ATTRIBUTE2 => NULL,
1294 X_ATTRIBUTE3 => NULL,
1295 X_ATTRIBUTE4 => NULL,
1296 X_ATTRIBUTE5 => NULL,
1297 X_ATTRIBUTE6 => NULL,
1298 X_ATTRIBUTE7 => NULL,
1299 X_ATTRIBUTE8 => NULL,
1300 X_ATTRIBUTE9 => NULL,
1301 X_ATTRIBUTE10 => NULL,
1302 X_ATTRIBUTE11 => NULL,
1303 X_ATTRIBUTE12 => NULL,
1304 X_ATTRIBUTE13 => NULL,
1305 X_ATTRIBUTE14 => NULL,
1306 X_ATTRIBUTE15 => NULL,
1307 X_ATTRIBUTE16 => NULL,
1308 X_ATTRIBUTE17 => NULL,
1309 X_ATTRIBUTE18 => NULL,
1310 X_ATTRIBUTE19 => NULL,
1311 X_ATTRIBUTE20 => NULL,
1312 X_MODE => 'R'
1313 );
1314
1315
1316 ELSE
1317 RAISE FND_API.G_EXC_ERROR;
1318 END IF;
1319
1320 -- End Here
1321
1322 IF FND_API.To_Boolean( p_commit ) THEN
1323 COMMIT WORK;
1324 END IF;
1325
1326 EXCEPTION
1327 WHEN FND_API.G_EXC_ERROR THEN
1328 ROLLBACK TO Create_Passport_PUB;
1329 x_return_status := FND_API.G_RET_STS_ERROR ;
1330 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
1331 x_msg_count := 1;
1332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1333 ROLLBACK TO Create_Passport_PUB;
1334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1335 FND_MSG_PUB.Count_And_Get
1336 ( p_count => x_msg_count ,
1337 p_data => x_msg_data
1338 );
1339 WHEN OTHERS THEN
1340 ROLLBACK TO Create_Passport_PUB;
1341 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1342
1343 x_msg_count := 1;
1344
1345 IF l_message_name IN('IGS_PE_VIS_ASOC_PASS_EXP','IGS_EN_INV','IGS_PS_LGCY_MANDATORY', 'IGS_PE_VIPS_UPD_ERR','IGS_PE_VIPS_COL_NONUPD') THEN
1346 x_return_status := FND_API.G_RET_STS_ERROR ;
1347 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
1348 ELSIF l_message_name = 'FORM_RECORD_DELETED' THEN
1349 x_return_status := FND_API.G_RET_STS_ERROR ;
1350 fnd_message.set_name ('IGS', 'IGS_EN_INV');
1351 fnd_message.set_token('PARAM','PERSON_ID');
1352 igs_ge_msg_stack.add;
1353 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F');
1354 ELSIF l_message_name = 'IGS_PE_PASSPORT_DUP_EXISTS' THEN
1355 x_return_status := FND_API.G_RET_STS_ERROR ;
1356 fnd_message.set_name ('IGS', 'IGS_PE_UNIQUE_FAILED');
1357 fnd_message.set_token('COLUMN','PERSON_ID,PASSPORT_CNTRY_CODE,PASSPORT_NUMBER');
1358 igs_ge_msg_stack.add;
1359 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F');
1360 ELSE
1361 x_msg_data := ' ' || SQLERRM;
1362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1363 END IF;
1364
1365
1366 END Create_Passport;
1367
1368
1369
1370 -- Start of comments
1371 -- API name : Update_Passport
1372 -- Type : Public
1373 -- Function :
1374 -- Pre-reqs : None.
1375 -- Parameters :
1376 -- IN : p_api_version IN NUMBER Required
1377 -- p_init_msg_list IN VARCHAR2 Optional
1378 -- Default = FND_API.G_FALSE
1379 -- p_commit IN VARCHAR2 Optional
1380 -- Default = FND_API.G_FALSE
1381 -- Default = FND_API.G_VALID_LEVEL_FULL
1382 -- p_passport_rec IN passport_rec_type
1383 --
1384 --
1385 -- OUT : x_return_status OUT VARCHAR2(1)
1386 -- x_msg_count OUT NUMBER
1387 -- x_msg_data OUT VARCHAR2(2000)
1388 --
1389 --
1390 --
1391 -- Version : Current version x.x
1392 -- Changed....
1393 -- previous version y.y
1394 -- Changed....
1395 -- .
1396 -- .
1397 -- Initial version 1.0
1398 --
1399 -- Notes :
1400 --
1401 -- End of comments
1402
1403 PROCEDURE Update_Passport
1404 ( p_api_version IN NUMBER,
1405 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1406 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1407 x_return_status OUT NOCOPY VARCHAR2,
1408 x_msg_count OUT NOCOPY NUMBER,
1409 x_msg_data OUT NOCOPY VARCHAR2,
1410 p_passport_rec IN passport_rec_type
1411 )
1412 IS
1413
1414 l_api_name CONSTANT VARCHAR2(30) := 'Update_Passport';
1415 l_api_version CONSTANT NUMBER := 1.0;
1416
1417 CURSOR null_handlng_cur(cp_pass IN passport_rec_type) IS
1418 SELECT rowid,
1419 PASSPORT_ID, PERSON_ID, PASSPORT_NUMBER, PASSPORT_EXPIRY_DATE, PASSPORT_CNTRY_CODE
1420 FROM IGS_PE_PASSPORT
1421 WHERE passport_id = cp_pass.passport_id FOR UPDATE NOWAIT;
1422
1423 dup_pass_rec null_handlng_cur%ROWTYPE;
1424 l_error_code VARCHAR2(30);
1425
1426 l_message_name VARCHAR2(30);
1427 l_app VARCHAR2(50);
1428
1429 l_passport_rec passport_rec_type;
1430
1431 E_RESOURCE_BUSY EXCEPTION;
1432 PRAGMA EXCEPTION_INIT(E_RESOURCE_BUSY, -54);
1433
1434 BEGIN
1435 l_passport_rec := p_passport_rec;
1436 l_passport_rec.passport_expiry_date := TRUNC(l_passport_rec.passport_expiry_date);
1437 SAVEPOINT Update_Passport_PUB;
1438 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1439 p_api_version,
1440 l_api_name,
1441 G_PKG_NAME )
1442 THEN
1443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1444 END IF;
1445
1446 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1447 FND_MSG_PUB.initialize;
1448 END IF;
1449
1450 x_return_status := FND_API.G_RET_STS_SUCCESS;
1451
1452 -- Start here
1453
1454 x_msg_count := 0;
1455
1456 IF p_passport_rec.passport_id IS NULL OR p_passport_rec.passport_id = FND_API.G_MISS_NUM THEN
1457 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1458 fnd_message.set_token('PARAM','PASSPORT_ID');
1459 igs_ge_msg_stack.add;
1460 app_exception.raise_exception;
1461 END IF;
1462
1463 OPEN null_handlng_cur(p_passport_rec);
1464 FETCH null_handlng_cur INTO dup_pass_rec;
1465 IF null_handlng_cur%NOTFOUND THEN
1466 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_UPD_ERR');
1467 fnd_message.set_token('VALUES','PASSPORT_ID=' || p_passport_rec.passport_id );
1468 igs_ge_msg_stack.add;
1469 app_exception.raise_exception;
1470 END IF;
1471 CLOSE null_handlng_cur;
1472
1473 IF l_passport_rec.person_id = FND_API.G_MISS_NUM THEN
1474 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1475 fnd_message.set_token('PARAM','PERSON_ID');
1476 igs_ge_msg_stack.add;
1477 app_exception.raise_exception;
1478 ELSIF l_passport_rec.person_id IS NULL THEN
1479 l_passport_rec.person_id := dup_pass_rec.person_id;
1480 ELSIF l_passport_rec.person_id <> dup_pass_rec.person_id THEN
1481 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_COL_NONUPD');
1482 fnd_message.set_token('COLUMN','PERSON_ID');
1483 igs_ge_msg_stack.add;
1484 app_exception.raise_exception;
1485 END IF;
1486
1487 IF l_passport_rec.passport_number = FND_API.G_MISS_CHAR THEN
1488 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1489 fnd_message.set_token('PARAM','PASSPORT_NUMBER');
1490 igs_ge_msg_stack.add;
1491 app_exception.raise_exception;
1492 ELSIF l_passport_rec.passport_number IS NULL THEN
1493 l_passport_rec.passport_number := dup_pass_rec.passport_number;
1494 ELSIF l_passport_rec.passport_number <> dup_pass_rec.passport_number THEN
1495 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_COL_NONUPD');
1496 fnd_message.set_token('COLUMN','PASSPORT_NUMBER');
1497 igs_ge_msg_stack.add;
1498 app_exception.raise_exception;
1499 END IF;
1500
1501 IF l_passport_rec.passport_expiry_date = FND_API.G_MISS_DATE THEN
1502 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1503 fnd_message.set_token('PARAM','PASSPORT_EXPIRY_DATE');
1504 igs_ge_msg_stack.add;
1505 app_exception.raise_exception;
1506 ELSIF l_passport_rec.passport_expiry_date IS NULL THEN
1507 l_passport_rec.passport_expiry_date := dup_pass_rec.passport_expiry_date;
1508 END IF;
1509
1510 IF l_passport_rec.passport_cntry_code = FND_API.G_MISS_CHAR THEN
1511 fnd_message.set_name ('IGS', 'IGS_PS_LGCY_MANDATORY');
1512 fnd_message.set_token('PARAM','PASSPORT_CNTRY_CODE');
1513 igs_ge_msg_stack.add;
1514 app_exception.raise_exception;
1515 ELSIF l_passport_rec.passport_cntry_code IS NULL THEN
1516 l_passport_rec.passport_cntry_code := dup_pass_rec.passport_cntry_code;
1517 ELSIF l_passport_rec.passport_cntry_code <> dup_pass_rec.passport_cntry_code THEN
1518 fnd_message.set_name ('IGS', 'IGS_PE_VIPS_COL_NONUPD');
1519 fnd_message.set_token('COLUMN','PASSPORT_CNTRY_CODE');
1520 igs_ge_msg_stack.add;
1521 app_exception.raise_exception;
1522 END IF;
1523
1524 IF igs_ad_imp_026.Validate_passport_pub(api_pass_rec => l_passport_rec, p_err_code => l_error_code) THEN
1525
1526 igs_pe_passport_pkg.update_row(
1527 X_ROWID => dup_pass_rec.rowid,
1528 X_PASSPORT_ID => dup_pass_rec.passport_id,
1529 X_PERSON_ID => l_passport_rec.person_id,
1530 X_PASSPORT_NUMBER => l_passport_rec.passport_number,
1531 X_PASSPORT_EXPIRY_DATE => l_passport_rec.passport_expiry_date,
1532 X_PASSPORT_CNTRY_CODE => l_passport_rec.passport_cntry_code,
1533 X_ATTRIBUTE_CATEGORY => NULL,
1534 X_ATTRIBUTE1 => NULL,
1535 X_ATTRIBUTE2 => NULL,
1536 X_ATTRIBUTE3 => NULL,
1537 X_ATTRIBUTE4 => NULL,
1538 X_ATTRIBUTE5 => NULL,
1539 X_ATTRIBUTE6 => NULL,
1540 X_ATTRIBUTE7 => NULL,
1541 X_ATTRIBUTE8 => NULL,
1542 X_ATTRIBUTE9 => NULL,
1543 X_ATTRIBUTE10 => NULL,
1544 X_ATTRIBUTE11 => NULL,
1545 X_ATTRIBUTE12 => NULL,
1546 X_ATTRIBUTE13 => NULL,
1547 X_ATTRIBUTE14 => NULL,
1548 X_ATTRIBUTE15 => NULL,
1549 X_ATTRIBUTE16 => NULL,
1550 X_ATTRIBUTE17 => NULL,
1551 X_ATTRIBUTE18 => NULL,
1552 X_ATTRIBUTE19 => NULL,
1553 X_ATTRIBUTE20 => NULL,
1554 X_MODE => 'R'
1555 );
1556 ELSE
1557 RAISE FND_API.G_EXC_ERROR;
1558 END IF;
1559
1560 -- End Here
1561
1562 IF FND_API.To_Boolean( p_commit ) THEN
1563 COMMIT WORK;
1564 END IF;
1565
1566 EXCEPTION
1567 WHEN E_RESOURCE_BUSY THEN
1568 ROLLBACK TO Update_Passport_PUB;
1569 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_LOCKED');
1570 igs_ge_msg_stack.add;
1571 x_return_status := FND_API.G_RET_STS_ERROR ;
1572 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
1573 x_msg_count := 1;
1574 WHEN FND_API.G_EXC_ERROR THEN
1575 ROLLBACK TO Update_Passport_PUB;
1576 x_return_status := FND_API.G_RET_STS_ERROR ;
1577 x_msg_data := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405);
1578 x_msg_count := 1;
1579 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1580 ROLLBACK TO Update_Passport_PUB;
1581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1582 FND_MSG_PUB.Count_And_Get
1583 ( p_count => x_msg_count ,
1584 p_data => x_msg_data
1585 );
1586 WHEN OTHERS THEN
1587 ROLLBACK TO Update_Passport_PUB;
1588
1589 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1590
1591 x_msg_count := 1;
1592 IF l_message_name IN('IGS_PE_VIS_ASOC_PASS_EXP','IGS_PE_PASSPORT_DUP_EXISTS','IGS_EN_INV','IGS_PS_LGCY_MANDATORY', 'IGS_PE_VIPS_UPD_ERR','IGS_PE_VIPS_COL_NONUPD') THEN
1593 x_return_status := FND_API.G_RET_STS_ERROR ;
1594 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_FIRST, p_encoded => 'F');
1595 ELSE
1596 x_msg_data := ' ' || SQLERRM;
1597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1598 END IF;
1599
1600 END Update_Passport;
1601
1602 END IGS_PE_VISAPASS_PUB;