[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_CITIZENSHIPS_PKG
Source
1 PACKAGE BODY Igs_Pe_citizenships_pkg AS
2 /* $Header: IGSNI74B.pls 120.1 2005/09/21 01:07:41 appldev ship $ */
3
4 PROCEDURE validate_date(p_start_dt IN DATE
5 ,p_end_dt IN DATE
6 ,party_id IN NUMBER
7 ,citznship_id IN NUMBER
8 ,country_cd IN VARCHAR2)
9 AS
10 ------------------------------------------------------------------------------------------
11 --Created by : vredkar
12 --Date created: 20-Sep-2005
13 --
14 --Purpose:
15 --Known limitations/enhancements and/or remarks:
16 --
17 --Change History:
18 --Who When What
19 ----------------------------------------------------------------------------------------------
20
21 l_bth_dt IGS_PE_PERSON_BASE_V.birth_date%TYPE;
22 l_default_date DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
23
24 CURSOR validate_brth_dt(cp_person_id NUMBER) IS
25 SELECT birth_date
26 FROM IGS_PE_PERSON_BASE_V
27 WHERE person_id = cp_person_id ;
28
29 CURSOR validate_dt_overlap(cp_party_id NUMBER, cp_start_date DATE, cp_end_date DATE , cp_citzn_id NUMBER , cp_country_cd VARCHAR2) IS
30 SELECT 'X'
31 FROM hz_citizenship
32 WHERE party_id = cp_party_id
33 AND cp_country_cd = country_code
34 AND (cp_citzn_id <> citizenship_id OR cp_citzn_id IS NULL)
35 AND (cp_start_date between date_recognized AND NVL(end_date,l_default_date)
36 OR cp_end_date between date_recognized AND NVL(end_date,l_default_date)
37 OR (cp_start_date <= date_recognized
38 AND NVL(cp_end_date,l_default_date) >= NVL(end_date,l_default_date)) );
39
40 l_Overlap_check VARCHAR2(1);
41
42 BEGIN
43 OPEN validate_brth_dt(party_id);
44 FETCH validate_brth_dt INTO l_bth_dt;
45 CLOSE validate_brth_dt;
46
47 IF p_end_dt IS NOT NULL AND p_end_dt < p_start_dt THEN
48 FND_MESSAGE.SET_NAME('IGS','IGS_FI_ST_DT_LE_END_DT');
49 IGS_GE_MSG_STACK.ADD;
50 APP_EXCEPTION.RAISE_EXCEPTION;
51
52 ELSIF l_bth_dt IS NOT NULL AND l_bth_dt > p_start_dt THEN
53 FND_MESSAGE.SET_NAME('IGS','IGS_PE_DREC_GT_BTDT');
54 IGS_GE_MSG_STACK.ADD;
55 APP_EXCEPTION.RAISE_EXCEPTION;
56 END IF;
57
58
59 OPEN validate_dt_overlap(party_id, p_start_dt, p_end_dt ,citznship_id,country_cd);
60 FETCH validate_dt_overlap INTO l_Overlap_check;
61 IF (validate_dt_overlap%FOUND) THEN
62 CLOSE validate_dt_overlap;
63 FND_MESSAGE.SET_NAME('IGS','IGS_PE_CIT_DATE_OVER');
64 IGS_GE_MSG_STACK.ADD;
65 APP_EXCEPTION.RAISE_EXCEPTION;
66 END IF;
67 CLOSE validate_dt_overlap;
68 END validate_date;
69
70
71 PROCEDURE Citizenship(
72 p_action IN VARCHAR2 ,
73 P_birth_or_selected IN VARCHAR2,
74 P_country_code IN Varchar2,
75 p_date_disowned IN Date,
76 p_date_recognized IN DATE,
77 p_DOCUMENT_REFERENCE IN VARCHAR2,
78 p_DOCUMENT_TYPE IN VARCHAR2,
79 p_PARTY_ID IN NUMBER,
80 p_END_DATE IN DATE,
81 p_TERRITORY_SHORT_NAME IN VARCHAR2,
82 p_last_update_date IN OUT NOCOPY DATE,
83 P_citizenship_id IN OUT NOCOPY NUMBER,
84 p_return_status OUT NOCOPY VARCHAR2 ,
85 p_msg_count OUT NOCOPY VARCHAR2 ,
86 p_msg_data OUT NOCOPY VARCHAR2,
87 p_object_version_number IN OUT NOCOPY NUMBER,
88 p_Calling_From IN VARCHAR2
89 ) AS
90
91 lv_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
92 lv_commit VARCHAR2(1) := FND_API.G_FALSE ;
93 lv_citizenship_id NUMBER;
94 lv_object_version_number NUMBER;
95 lv_citizenship_rec_type HZ_PERSON_INFO_V2PUB.citizenship_rec_type;
96 lv_last_update_date DATE := p_last_update_date;
97
98 tmp_var VARCHAR2(2000);
99 tmp_var1 VARCHAR2(2000);
100
101 BEGIN
102
103
104 IF p_action='INSERT' THEN
105 lv_citizenship_rec_type.birth_or_selected := p_birth_or_selected;
106 lv_citizenship_rec_type.country_code := p_country_code;
107 lv_citizenship_rec_type.date_disowned := p_date_disowned;
108 lv_citizenship_rec_type.date_recognized := p_date_recognized;
109 lv_citizenship_rec_type.document_reference := p_document_reference;
110 lv_citizenship_rec_type.party_id := p_party_id;
111 lv_citizenship_rec_type.end_date := p_end_date;
112 lv_citizenship_rec_type.document_type := p_document_type;
113 lv_citizenship_rec_type.created_by_module := 'IGS';
114 lv_citizenship_rec_type.application_id := 8405;
115
116 IF p_Calling_From='SS' THEN
117 validate_date(p_date_recognized ,p_end_date ,p_party_id , p_citizenship_id, p_country_code );
118 END IF;
119
120 --gmaheswa: HZ_API ia changed to HZ_PERSON_INFOR_V2PUB from HZ_PER_INFO_PUB.
121 HZ_PERSON_INFO_V2PUB.create_citizenship(
122 p_init_msg_list => lv_init_msg_list,
123 p_citizenship_rec => lv_citizenship_rec_type,
124 x_return_status => p_return_status,
125 x_msg_count => p_msg_count,
126 x_msg_data => p_msg_Data,
127 x_citizenship_id => p_citizenship_id
128 );
129
130 IF p_return_status IN ('E','U') THEN
131 -- ssawhney bug 2338473
132 IF p_msg_count > 1 THEN
133 FOR i IN 1..p_msg_count LOOP
134 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
135 tmp_var1 := tmp_var1 || ' '|| tmp_var;
136 END LOOP;
137 p_msg_data := tmp_var1;
138 END IF;
139 RETURN;
140 ELSE
141 p_object_version_number := 1;
142 END IF;
143 ELSIF p_action = 'UPDATE' THEN
144
145 lv_citizenship_rec_type.birth_or_selected := NVL(p_birth_or_selected,FND_API.G_MISS_CHAR);
146 lv_citizenship_rec_type.country_code := p_country_code;
147 lv_citizenship_rec_type.date_disowned := NVL(p_date_disowned,FND_API.G_MISS_DATE);
148 lv_citizenship_rec_type.date_recognized := NVL(p_date_recognized,FND_API.G_MISS_DATE);
149 lv_citizenship_rec_type.document_reference := NVL(p_document_reference,FND_API.G_MISS_CHAR);
150 lv_citizenship_rec_type.party_id := p_party_id;
151 lv_citizenship_rec_type.end_date := NVL(p_end_date,FND_API.G_MISS_DATE);
152 lv_citizenship_rec_type.citizenship_id := p_citizenship_id;
153 lv_citizenship_rec_type.document_type := NVL(p_document_type,FND_API.G_MISS_CHAR);
154
155 IF p_Calling_From='SS' THEN
156 validate_date(p_date_recognized ,p_end_date ,p_party_id , p_citizenship_id , p_country_code );
157 END IF;
158
159 --gmaheswa: HZ_API ia changed to HZ_PERSON_INFOR_V2PUB from HZ_PER_INFO_PUB.
160 HZ_PERSON_INFO_V2PUB.update_citizenship(
161 p_init_msg_list => lv_init_msg_list,
162 p_citizenship_rec => lv_citizenship_rec_type,
163 x_return_status => p_return_status,
164 x_msg_count => p_msg_count,
165 x_msg_data => p_msg_data,
166 p_object_version_number => p_object_version_number
167 );
168
169 IF p_return_status IN ('E','U') THEN
170
171 -- ssawhney bug 2338473
172 IF p_msg_count > 1 THEN
173 FOR i IN 1..p_msg_count LOOP
174 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
175 tmp_var1 := tmp_var1 || ' '|| tmp_var;
176 END LOOP;
177 p_msg_data := tmp_var1;
178 END IF;
179 RETURN;
180 END IF;
181
182 END IF;
183 END Citizenship;
184
185
186
187 END IGS_PE_CITIZENSHIPS_PKG;