[Home] [Help]
PACKAGE BODY: APPS.HZ_CITIZENSHIP_PKG
Source
1 PACKAGE BODY HZ_CITIZENSHIP_PKG as
2 /* $Header: ARHPCITB.pls 120.5 2005/10/30 04:21:28 appldev ship $ */
3
4
5
6 PROCEDURE Insert_Row(
7 x_CITIZENSHIP_ID IN OUT NOCOPY NUMBER,
8 x_BIRTH_OR_SELECTED IN VARCHAR2,
9 x_PARTY_ID IN NUMBER,
10 x_COUNTRY_CODE IN VARCHAR2,
11 x_DATE_DISOWNED IN DATE,
12 x_DATE_RECOGNIZED IN DATE,
13 x_DOCUMENT_REFERENCE IN VARCHAR2,
14 x_DOCUMENT_TYPE IN VARCHAR2,
15 x_END_DATE IN DATE,
16 x_STATUS IN VARCHAR2,
17 x_OBJECT_VERSION_NUMBER IN NUMBER,
18 x_CREATED_BY_MODULE IN VARCHAR2,
19 x_APPLICATION_ID IN NUMBER
20
21 ) IS
22
23 l_success VARCHAR2(1) := 'N';
24
25 BEGIN
26
27 WHILE l_success = 'N' LOOP
28
29 BEGIN
30
31 INSERT INTO HZ_CITIZENSHIP(
32 CITIZENSHIP_ID,
33 BIRTH_OR_SELECTED,
34 PARTY_ID,
35 COUNTRY_CODE,
36 DATE_DISOWNED,
37 DATE_RECOGNIZED,
38 DOCUMENT_REFERENCE,
39 DOCUMENT_TYPE,
40 CREATED_BY,
41 CREATION_DATE,
42 LAST_UPDATE_LOGIN,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 REQUEST_ID,
46 PROGRAM_APPLICATION_ID,
47 PROGRAM_ID,
48 PROGRAM_UPDATE_DATE,
49 END_DATE,
50 STATUS,
51 OBJECT_VERSION_NUMBER,
52 CREATED_BY_MODULE,
53 APPLICATION_ID
54 ) VALUES (
55 decode( X_CITIZENSHIP_ID, FND_API.G_MISS_NUM, HZ_CITIZENSHIP_S.NEXTVAL, NULL, HZ_CITIZENSHIP_S.NEXTVAL, X_CITIZENSHIP_ID ),
56 decode( x_BIRTH_OR_SELECTED, FND_API.G_MISS_CHAR, NULL,x_BIRTH_OR_SELECTED),
57 decode( x_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_PARTY_ID),
58 decode( x_COUNTRY_CODE, FND_API.G_MISS_CHAR, NULL,x_COUNTRY_CODE),
59 decode( x_DATE_DISOWNED, FND_API.G_MISS_DATE, TO_DATE(NULL),x_DATE_DISOWNED),
60 decode( x_DATE_RECOGNIZED, FND_API.G_MISS_DATE, TO_DATE(NULL),x_DATE_RECOGNIZED),
61 decode( x_DOCUMENT_REFERENCE, FND_API.G_MISS_CHAR, NULL,x_DOCUMENT_REFERENCE),
62 decode( x_DOCUMENT_TYPE, FND_API.G_MISS_CHAR, NULL,x_DOCUMENT_TYPE),
63 HZ_UTILITY_V2PUB.CREATED_BY,
64 HZ_UTILITY_V2PUB.CREATION_DATE,
65 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
66 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
67 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
68 HZ_UTILITY_V2PUB.REQUEST_ID,
69 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
70 HZ_UTILITY_V2PUB.PROGRAM_ID,
71 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
72 decode( x_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_END_DATE),
73 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
74 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
75 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
76 HZ_UTILITY_V2PUB.APPLICATION_ID
77 )
78 RETURNING
79 CITIZENSHIP_ID
80 INTO
81 X_CITIZENSHIP_ID;
82
83 l_success := 'Y';
84
85 EXCEPTION
86 WHEN DUP_VAL_ON_INDEX THEN
87 IF INSTRB( SQLERRM, 'HZ_CITIZENSHIP_U1' ) <> 0 OR
88 INSTRB( SQLERRM, 'HZ_CITIZENSHIP_PK' ) <> 0
89 THEN
90 DECLARE
91 l_count NUMBER;
92 l_dummy VARCHAR2(1);
93 BEGIN
94 l_count := 1;
95 WHILE l_count > 0 LOOP
96 SELECT HZ_CITIZENSHIP_S.NEXTVAL
97 INTO X_CITIZENSHIP_ID FROM dual;
98 BEGIN
99 SELECT 'Y' INTO l_dummy
100 FROM HZ_CITIZENSHIP
101 WHERE CITIZENSHIP_ID = CITIZENSHIP_ID;
102 l_count := 1;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 l_count := 0;
106 END;
107 END LOOP;
108 END;
109 ELSE
110 RAISE;
111 END IF;
112
113 END;
114 END LOOP;
115
116 END Insert_Row;
117
118
119 PROCEDURE Delete_Row( x_CITIZENSHIP_ID NUMBER
120 ) IS
121 BEGIN
122 DELETE FROM HZ_CITIZENSHIP
123 WHERE CITIZENSHIP_ID = x_CITIZENSHIP_ID;
124 If (SQL%NOTFOUND) then
125 RAISE NO_DATA_FOUND;
126 End If;
127 END Delete_Row;
128
129
130 PROCEDURE Update_Row(
131
132 x_Rowid IN OUT NOCOPY VARCHAR2,
133 x_CITIZENSHIP_ID IN OUT NOCOPY NUMBER,
134 x_BIRTH_OR_SELECTED IN VARCHAR2,
135 x_PARTY_ID IN NUMBER,
136 x_COUNTRY_CODE IN VARCHAR2,
137 x_DATE_DISOWNED IN DATE,
138 x_DATE_RECOGNIZED IN DATE,
139 x_DOCUMENT_REFERENCE IN VARCHAR2,
140 x_DOCUMENT_TYPE IN VARCHAR2,
141 x_END_DATE IN DATE,
142 x_STATUS IN VARCHAR2,
143 x_OBJECT_VERSION_NUMBER IN NUMBER,
144 x_CREATED_BY_MODULE IN VARCHAR2,
145 x_APPLICATION_ID IN NUMBER
146
147 ) IS
148 BEGIN
149 Update HZ_CITIZENSHIP
150 SET
151
152 CITIZENSHIP_ID = decode( x_CITIZENSHIP_ID, NULL, CITIZENSHIP_ID, FND_API.G_MISS_NUM, NULL, X_CITIZENSHIP_ID ),
153 BIRTH_OR_SELECTED = decode( x_BIRTH_OR_SELECTED, NULL, BIRTH_OR_SELECTED, FND_API.G_MISS_CHAR, NULL, x_BIRTH_OR_SELECTED ),
154 PARTY_ID = DECODE( X_PARTY_ID, NULL, PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID ),
155 COUNTRY_CODE = DECODE( x_COUNTRY_CODE, NULL, COUNTRY_CODE, FND_API.G_MISS_CHAR, NULL, x_COUNTRY_CODE ),
156 DATE_DISOWNED = decode( x_DATE_DISOWNED, NULL, DATE_DISOWNED, FND_API.G_MISS_DATE,NULL ,x_DATE_DISOWNED),
157 DATE_RECOGNIZED = decode( x_DATE_RECOGNIZED, NULL, DATE_RECOGNIZED, FND_API.G_MISS_DATE, NULL, x_DATE_RECOGNIZED),
158 DOCUMENT_REFERENCE = decode( x_DOCUMENT_REFERENCE, NULL, DOCUMENT_REFERENCE, FND_API.G_MISS_CHAR,NULL,x_DOCUMENT_REFERENCE),
159 DOCUMENT_TYPE = decode( x_DOCUMENT_TYPE, NULL, DOCUMENT_TYPE, FND_API.G_MISS_CHAR,NULL, x_DOCUMENT_TYPE),
160 -- Bug 3032780
161 -- CREATED_BY = HZ_UTILITY_V2PUB.CREATED_BY,
162 -- CREATION_DATE = HZ_UTILITY_V2PUB.CREATION_DATE,
163 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
164 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
165 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
166 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
167 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
168 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
169 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
170 END_DATE = decode( x_END_DATE, NULL, END_DATE, FND_API.G_MISS_DATE, NULL, x_END_DATE),
171 STATUS=decode(x_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL,x_STATUS),
172 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
173 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
174 APPLICATION_ID=HZ_UTILITY_V2PUB.APPLICATION_ID
175
176 where rowid = X_RowId;
177
178 If (SQL%NOTFOUND) then
179 RAISE NO_DATA_FOUND;
180 End If;
181 END Update_Row;
182
183
184
185 PROCEDURE Lock_Row(
186 x_Rowid IN VARCHAR2,
187 x_CITIZENSHIP_ID IN NUMBER,
188 x_BIRTH_OR_SELECTED IN VARCHAR2,
189 x_PARTY_ID IN NUMBER,
190 x_COUNTRY_CODE IN VARCHAR2,
191 x_DATE_DISOWNED IN DATE,
192 x_DATE_RECOGNIZED IN DATE,
193 x_DOCUMENT_REFERENCE IN VARCHAR2,
194 x_DOCUMENT_TYPE IN VARCHAR2,
195 x_CREATED_BY IN NUMBER,
196 x_CREATION_DATE IN DATE,
197 x_LAST_UPDATE_LOGIN IN NUMBER,
198 x_LAST_UPDATE_DATE IN DATE,
199 x_LAST_UPDATED_BY IN NUMBER,
200 x_REQUEST_ID IN NUMBER,
201 x_PROGRAM_APPLICATION_ID IN NUMBER,
202 x_PROGRAM_ID IN NUMBER,
203 x_PROGRAM_UPDATE_DATE IN DATE,
204 x_WH_UPDATE_DATE IN DATE,
205 x_END_DATE IN DATE,
206 x_STATUS IN VARCHAR2
207 ) IS
208 CURSOR C IS
209 SELECT *
210 FROM HZ_CITIZENSHIP
211 WHERE rowid = x_Rowid
212 FOR UPDATE of CITIZENSHIP_ID NOWAIT;
213 Recinfo C%ROWTYPE;
214 BEGIN
215 OPEN C;
216 FETCH C INTO Recinfo;
217 If (C%NOTFOUND) then
218 CLOSE C;
219 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
220 APP_EXCEPTION.RAISE_EXCEPTION;
221 End If;
222 CLOSE C;
223 if (
224 ( ( Recinfo.CITIZENSHIP_ID = x_CITIZENSHIP_ID)
225 OR ( ( Recinfo.CITIZENSHIP_ID = NULL )
226 AND ( x_CITIZENSHIP_ID = NULL )))
227 AND ( ( Recinfo.BIRTH_OR_SELECTED = x_BIRTH_OR_SELECTED)
228 OR ( ( Recinfo.BIRTH_OR_SELECTED = NULL )
229 AND ( x_BIRTH_OR_SELECTED = NULL )))
230 AND ( ( Recinfo.PARTY_ID = x_PARTY_ID)
231 OR ( ( Recinfo.PARTY_ID = NULL )
232 AND ( x_PARTY_ID = NULL )))
233 AND ( ( Recinfo.COUNTRY_CODE = x_COUNTRY_CODE)
234 OR ( ( Recinfo.COUNTRY_CODE = NULL )
235 AND ( x_COUNTRY_CODE = NULL )))
236 AND ( ( Recinfo.DATE_DISOWNED = x_DATE_DISOWNED)
237 OR ( ( Recinfo.DATE_DISOWNED = NULL )
238 AND ( x_DATE_DISOWNED = NULL )))
239 AND ( ( Recinfo.DATE_RECOGNIZED = x_DATE_RECOGNIZED)
240 OR ( ( Recinfo.DATE_RECOGNIZED = NULL )
241 AND ( x_DATE_RECOGNIZED = NULL )))
242 AND ( ( Recinfo.DOCUMENT_REFERENCE = x_DOCUMENT_REFERENCE)
243 OR ( ( Recinfo.DOCUMENT_REFERENCE = NULL )
244 AND ( x_DOCUMENT_REFERENCE = NULL )))
245 AND ( ( Recinfo.DOCUMENT_TYPE = x_DOCUMENT_TYPE)
246 OR ( ( Recinfo.DOCUMENT_TYPE = NULL )
247 AND ( x_DOCUMENT_TYPE = NULL )))
248 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
249 OR ( ( Recinfo.CREATED_BY = NULL )
250 AND ( x_CREATED_BY = NULL )))
251 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
252 OR ( ( Recinfo.CREATION_DATE = NULL )
253 AND ( x_CREATION_DATE = NULL )))
254 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
255 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
256 AND ( x_LAST_UPDATE_LOGIN = NULL )))
257 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
258 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
259 AND ( x_LAST_UPDATE_DATE = NULL )))
260 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
261 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
262 AND ( x_LAST_UPDATED_BY = NULL )))
263 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
267 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
264 OR ( ( Recinfo.REQUEST_ID = NULL )
265 AND ( x_REQUEST_ID = NULL )))
266 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
268 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
269 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
270 OR ( ( Recinfo.PROGRAM_ID = NULL )
271 AND ( x_PROGRAM_ID = NULL )))
272 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
273 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
274 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
275 AND ( ( Recinfo.END_DATE = x_END_DATE)
276 OR ( ( Recinfo.END_DATE = NULL )
277 AND ( x_END_DATE = NULL )))
278
279 AND ( ( Recinfo.STATUS = x_STATUS)
280 OR ( ( Recinfo.STATUS = NULL )
281 AND ( x_STATUS = NULL )))
282 ) then
283 return;
284 else
285 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
286 APP_EXCEPTION.RAISE_EXCEPTION;
287 End If;
288 END Lock_Row;
289
290
291 PROCEDURE Select_Row (
292 x_citizenship_id IN OUT NOCOPY NUMBER,
293 x_birth_or_selected OUT NOCOPY VARCHAR2,
294 x_party_id OUT NOCOPY NUMBER,
295 x_country_code OUT NOCOPY VARCHAR2,
296 x_date_disowned OUT NOCOPY DATE,
297 x_date_recognized OUT NOCOPY DATE,
298 x_document_reference OUT NOCOPY VARCHAR2,
299 x_end_date OUT NOCOPY DATE,
300 x_document_type OUT NOCOPY VARCHAR2,
301 x_status OUT NOCOPY VARCHAR2,
302 x_application_id OUT NOCOPY NUMBER,
303 x_created_by_module OUT NOCOPY VARCHAR2
304 ) IS
305 BEGIN
306
307 SELECT
308 NVL(citizenship_id, FND_API.G_MISS_NUM),
309 NVL(birth_or_selected, FND_API.G_MISS_CHAR),
310 NVL(party_id, FND_API.G_MISS_NUM),
311 NVL(country_code, FND_API.G_MISS_CHAR),
312 NVL(date_disowned, FND_API.G_MISS_DATE),
313 NVL(date_recognized, FND_API.G_MISS_DATE),
314 NVL(document_reference, FND_API.G_MISS_CHAR),
315 NVL(end_date, FND_API.G_MISS_DATE),
316 NVL(document_type, FND_API.G_MISS_CHAR),
317 NVL(status, FND_API.G_MISS_CHAR),
318 NVL(application_id, FND_API.G_MISS_NUM),
319 NVL(created_by_module, FND_API.G_MISS_CHAR)
320 INTO
321 x_citizenship_id,
322 x_birth_or_selected,
323 x_party_id,
324 x_country_code,
325 x_date_disowned,
326 x_date_recognized,
327 x_document_reference,
328 x_end_date,
329 x_document_type,
330 x_status,
331 x_application_id,
332 x_created_by_module
333 FROM HZ_CITIZENSHIP
334 WHERE citizenship_id = x_citizenship_id;
335
336 EXCEPTION
337 WHEN NO_DATA_FOUND THEN
338 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
339 FND_MESSAGE.SET_TOKEN('RECORD', 'citizenship_rec');
340 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_citizenship_id));
341 FND_MSG_PUB.ADD;
342 RAISE FND_API.G_EXC_ERROR;
343
344 END Select_Row;
345
346 END HZ_CITIZENSHIP_PKG;