[Home] [Help]
PACKAGE BODY: APPS.HZ_PERSON_PROFILES_PKG
Source
1 PACKAGE BODY HZ_PERSON_PROFILES_PKG AS
2 /*$Header: ARHPERTB.pls 120.7.12020000.2 2012/07/18 09:56:30 rgokavar ship $ */
3
4 g_miss_content_source_type CONSTANT VARCHAR2(30) := 'USER_ENTERED';
5 g_sst_source_type CONSTANT VARCHAR2(30) := 'SST';
6
7 PROCEDURE Insert_Row (
8 X_ROWID OUT NOCOPY ROWID,
9 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER,
10 X_PARTY_ID IN NUMBER,
11 X_PERSON_NAME IN VARCHAR2,
12 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
13 X_ATTRIBUTE1 IN VARCHAR2,
14 X_ATTRIBUTE2 IN VARCHAR2,
15 X_ATTRIBUTE3 IN VARCHAR2,
16 X_ATTRIBUTE4 IN VARCHAR2,
17 X_ATTRIBUTE5 IN VARCHAR2,
18 X_ATTRIBUTE6 IN VARCHAR2,
19 X_ATTRIBUTE7 IN VARCHAR2,
20 X_ATTRIBUTE8 IN VARCHAR2,
21 X_ATTRIBUTE9 IN VARCHAR2,
22 X_ATTRIBUTE10 IN VARCHAR2,
23 X_ATTRIBUTE11 IN VARCHAR2,
24 X_ATTRIBUTE12 IN VARCHAR2,
25 X_ATTRIBUTE13 IN VARCHAR2,
26 X_ATTRIBUTE14 IN VARCHAR2,
27 X_ATTRIBUTE15 IN VARCHAR2,
28 X_ATTRIBUTE16 IN VARCHAR2,
29 X_ATTRIBUTE17 IN VARCHAR2,
30 X_ATTRIBUTE18 IN VARCHAR2,
31 X_ATTRIBUTE19 IN VARCHAR2,
32 X_ATTRIBUTE20 IN VARCHAR2,
33 X_INTERNAL_FLAG IN VARCHAR2,
34 X_PERSON_PRE_NAME_ADJUNCT IN VARCHAR2,
35 X_PERSON_FIRST_NAME IN VARCHAR2,
36 X_PERSON_MIDDLE_NAME IN VARCHAR2,
37 X_PERSON_LAST_NAME IN VARCHAR2,
38 X_PERSON_NAME_SUFFIX IN VARCHAR2,
39 X_PERSON_TITLE IN VARCHAR2,
40 X_PERSON_ACADEMIC_TITLE IN VARCHAR2,
41 X_PERSON_PREVIOUS_LAST_NAME IN VARCHAR2,
42 X_PERSON_INITIALS IN VARCHAR2,
43 X_KNOWN_AS IN VARCHAR2,
44 X_PERSON_NAME_PHONETIC IN VARCHAR2,
45 X_PERSON_FIRST_NAME_PHONETIC IN VARCHAR2,
46 X_PERSON_LAST_NAME_PHONETIC IN VARCHAR2,
47 X_TAX_REFERENCE IN VARCHAR2,
48 X_JGZZ_FISCAL_CODE IN VARCHAR2,
49 X_PERSON_IDEN_TYPE IN VARCHAR2,
50 X_PERSON_IDENTIFIER IN VARCHAR2,
51 X_DATE_OF_BIRTH IN DATE,
52 X_PLACE_OF_BIRTH IN VARCHAR2,
53 X_DATE_OF_DEATH IN DATE,
54 X_DECEASED_FLAG IN VARCHAR2,
55 X_GENDER IN VARCHAR2,
56 X_DECLARED_ETHNICITY IN VARCHAR2,
57 X_MARITAL_STATUS IN VARCHAR2,
58 X_MARITAL_STATUS_EFF_DATE IN DATE,
59 X_PERSONAL_INCOME IN NUMBER,
60 X_HEAD_OF_HOUSEHOLD_FLAG IN VARCHAR2,
61 X_HOUSEHOLD_INCOME IN NUMBER,
62 X_HOUSEHOLD_SIZE IN NUMBER,
63 X_RENT_OWN_IND IN VARCHAR2,
64 X_LAST_KNOWN_GPS IN VARCHAR2,
65 X_EFFECTIVE_START_DATE IN DATE,
66 X_EFFECTIVE_END_DATE IN DATE,
67 X_CONTENT_SOURCE_TYPE IN VARCHAR2,
68 X_KNOWN_AS2 IN VARCHAR2,
69 X_KNOWN_AS3 IN VARCHAR2,
70 X_KNOWN_AS4 IN VARCHAR2,
71 X_KNOWN_AS5 IN VARCHAR2,
72 X_MIDDLE_NAME_PHONETIC IN VARCHAR2,
73 X_OBJECT_VERSION_NUMBER IN NUMBER,
74 X_CREATED_BY_MODULE IN VARCHAR2,
75 X_APPLICATION_ID IN NUMBER,
76 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2,
77 X_VERSION_NUMBER IN NUMBER default 1
78 ) IS
79
80 l_success VARCHAR2(1) := 'N';
81
82 BEGIN
83
84 WHILE l_success = 'N' LOOP
85 BEGIN
86 INSERT INTO HZ_PERSON_PROFILES (
87 PERSON_PROFILE_ID,
88 PARTY_ID,
89 PERSON_NAME,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 CREATION_DATE,
93 CREATED_BY,
94 LAST_UPDATE_LOGIN,
95 REQUEST_ID,
96 PROGRAM_APPLICATION_ID,
97 PROGRAM_ID,
98 PROGRAM_UPDATE_DATE,
99 ATTRIBUTE_CATEGORY,
100 ATTRIBUTE1,
101 ATTRIBUTE2,
102 ATTRIBUTE3,
103 ATTRIBUTE4,
104 ATTRIBUTE5,
105 ATTRIBUTE6,
106 ATTRIBUTE7,
107 ATTRIBUTE8,
108 ATTRIBUTE9,
109 ATTRIBUTE10,
110 ATTRIBUTE11,
111 ATTRIBUTE12,
112 ATTRIBUTE13,
113 ATTRIBUTE14,
114 ATTRIBUTE15,
115 ATTRIBUTE16,
116 ATTRIBUTE17,
117 ATTRIBUTE18,
118 ATTRIBUTE19,
119 ATTRIBUTE20,
120 INTERNAL_FLAG,
121 PERSON_PRE_NAME_ADJUNCT,
122 PERSON_FIRST_NAME,
123 PERSON_MIDDLE_NAME,
124 PERSON_LAST_NAME,
125 PERSON_NAME_SUFFIX,
126 PERSON_TITLE,
127 PERSON_ACADEMIC_TITLE,
128 PERSON_PREVIOUS_LAST_NAME,
129 PERSON_INITIALS,
130 KNOWN_AS,
131 PERSON_NAME_PHONETIC,
132 PERSON_FIRST_NAME_PHONETIC,
133 PERSON_LAST_NAME_PHONETIC,
134 TAX_REFERENCE,
135 JGZZ_FISCAL_CODE,
136 PERSON_IDEN_TYPE,
137 PERSON_IDENTIFIER,
138 DATE_OF_BIRTH,
139 PLACE_OF_BIRTH,
140 DATE_OF_DEATH,
141 DECEASED_FLAG,
142 GENDER,
143 DECLARED_ETHNICITY,
144 MARITAL_STATUS,
145 MARITAL_STATUS_EFFECTIVE_DATE,
146 PERSONAL_INCOME,
147 HEAD_OF_HOUSEHOLD_FLAG,
148 HOUSEHOLD_INCOME,
149 HOUSEHOLD_SIZE,
150 RENT_OWN_IND,
151 LAST_KNOWN_GPS,
152 EFFECTIVE_START_DATE,
153 EFFECTIVE_END_DATE,
154 CONTENT_SOURCE_TYPE,
155 KNOWN_AS2,
156 KNOWN_AS3,
157 KNOWN_AS4,
158 KNOWN_AS5,
159 MIDDLE_NAME_PHONETIC,
160 OBJECT_VERSION_NUMBER,
161 CREATED_BY_MODULE,
162 APPLICATION_ID,
163 ACTUAL_CONTENT_SOURCE,
164 VERSION_NUMBER
165 )
166 VALUES (
167 DECODE( X_PERSON_PROFILE_ID, FND_API.G_MISS_NUM, HZ_PERSON_PROFILES_S.NEXTVAL, NULL, HZ_PERSON_PROFILES_S.NEXTVAL, X_PERSON_PROFILE_ID ),
168 DECODE( X_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID ),
169 DECODE( X_PERSON_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_NAME ),
170 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
171 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
172 HZ_UTILITY_V2PUB.CREATION_DATE,
173 HZ_UTILITY_V2PUB.CREATED_BY,
174 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
175 HZ_UTILITY_V2PUB.REQUEST_ID,
176 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
177 HZ_UTILITY_V2PUB.PROGRAM_ID,
178 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
179 DECODE( X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
180 DECODE( X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
181 DECODE( X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
182 DECODE( X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
183 DECODE( X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
184 DECODE( X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
185 DECODE( X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
186 DECODE( X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
187 DECODE( X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
188 DECODE( X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
189 DECODE( X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
190 DECODE( X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
191 DECODE( X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
192 DECODE( X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
193 DECODE( X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
194 DECODE( X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
195 DECODE( X_ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE16 ),
196 DECODE( X_ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE17 ),
197 DECODE( X_ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE18 ),
198 DECODE( X_ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE19 ),
199 DECODE( X_ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE20 ),
200 DECODE( X_INTERNAL_FLAG, FND_API.G_MISS_CHAR, NULL, X_INTERNAL_FLAG ),
201 DECODE( X_PERSON_PRE_NAME_ADJUNCT, FND_API.G_MISS_CHAR, NULL, X_PERSON_PRE_NAME_ADJUNCT ),
202 DECODE( X_PERSON_FIRST_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_FIRST_NAME ),
203 DECODE( X_PERSON_MIDDLE_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_MIDDLE_NAME ),
204 DECODE( X_PERSON_LAST_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_LAST_NAME ),
205 DECODE( X_PERSON_NAME_SUFFIX, FND_API.G_MISS_CHAR, NULL, X_PERSON_NAME_SUFFIX ),
206 DECODE( X_PERSON_TITLE, FND_API.G_MISS_CHAR, NULL, X_PERSON_TITLE ),
207 DECODE( X_PERSON_ACADEMIC_TITLE, FND_API.G_MISS_CHAR, NULL, X_PERSON_ACADEMIC_TITLE ),
208 DECODE( X_PERSON_PREVIOUS_LAST_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_PREVIOUS_LAST_NAME ),
209 DECODE( X_PERSON_INITIALS, FND_API.G_MISS_CHAR, NULL, X_PERSON_INITIALS ),
210 DECODE( X_KNOWN_AS, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS ),
211 DECODE( X_PERSON_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_PERSON_NAME_PHONETIC ),
212 DECODE( X_PERSON_FIRST_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_PERSON_FIRST_NAME_PHONETIC ),
213 DECODE( X_PERSON_LAST_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_PERSON_LAST_NAME_PHONETIC ),
214 DECODE( X_TAX_REFERENCE, FND_API.G_MISS_CHAR, NULL, X_TAX_REFERENCE ),
215 DECODE( X_JGZZ_FISCAL_CODE, FND_API.G_MISS_CHAR, NULL, X_JGZZ_FISCAL_CODE ),
216 DECODE( X_PERSON_IDEN_TYPE, FND_API.G_MISS_CHAR, NULL, X_PERSON_IDEN_TYPE ),
217 DECODE( X_PERSON_IDENTIFIER, FND_API.G_MISS_CHAR, NULL, X_PERSON_IDENTIFIER ),
218 DECODE( X_DATE_OF_BIRTH, FND_API.G_MISS_DATE, TO_DATE( NULL ), X_DATE_OF_BIRTH ),
219 DECODE( X_PLACE_OF_BIRTH, FND_API.G_MISS_CHAR, NULL, X_PLACE_OF_BIRTH ),
220 DECODE( X_DATE_OF_DEATH, FND_API.G_MISS_DATE, TO_DATE( NULL ), X_DATE_OF_DEATH ),
221 DECODE( X_DECEASED_FLAG, FND_API.G_MISS_CHAR, NULL, X_DECEASED_FLAG ),
222 DECODE( X_GENDER, FND_API.G_MISS_CHAR, NULL, X_GENDER ),
223 DECODE( X_DECLARED_ETHNICITY, FND_API.G_MISS_CHAR, NULL, X_DECLARED_ETHNICITY ),
224 DECODE( X_MARITAL_STATUS, FND_API.G_MISS_CHAR, NULL, X_MARITAL_STATUS ),
225 DECODE( X_MARITAL_STATUS_EFF_DATE, FND_API.G_MISS_DATE, TO_DATE( NULL ), X_MARITAL_STATUS_EFF_DATE ),
226 DECODE( X_PERSONAL_INCOME, FND_API.G_MISS_NUM, NULL, X_PERSONAL_INCOME ),
227 DECODE( X_HEAD_OF_HOUSEHOLD_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_HEAD_OF_HOUSEHOLD_FLAG ),
228 DECODE( X_HOUSEHOLD_INCOME, FND_API.G_MISS_NUM, NULL, X_HOUSEHOLD_INCOME ),
229 DECODE( X_HOUSEHOLD_SIZE, FND_API.G_MISS_NUM, NULL, X_HOUSEHOLD_SIZE ),
230 DECODE( X_RENT_OWN_IND, FND_API.G_MISS_CHAR, NULL, X_RENT_OWN_IND ),
231 DECODE( X_LAST_KNOWN_GPS, FND_API.G_MISS_CHAR, NULL, X_LAST_KNOWN_GPS ),
232 DECODE( X_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, trunc(hz_utility_v2pub.CREATION_DATE), NULL, trunc(hz_utility_v2pub.CREATION_DATE), X_EFFECTIVE_START_DATE ),
233 DECODE( X_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE( NULL ), X_EFFECTIVE_END_DATE ),
234 DECODE( X_CONTENT_SOURCE_TYPE, FND_API.G_MISS_CHAR, G_MISS_CONTENT_SOURCE_TYPE, NULL, G_MISS_CONTENT_SOURCE_TYPE, X_CONTENT_SOURCE_TYPE ),
235 DECODE( X_KNOWN_AS2, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS2 ),
236 DECODE( X_KNOWN_AS3, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS3 ),
237 DECODE( X_KNOWN_AS4, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS4 ),
238 DECODE( X_KNOWN_AS5, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS5 ),
239 DECODE( X_MIDDLE_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_MIDDLE_NAME_PHONETIC ),
240 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
241 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
242 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
243 DECODE( X_ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, G_SST_SOURCE_TYPE, NULL, G_SST_SOURCE_TYPE, X_ACTUAL_CONTENT_SOURCE ),
244 DECODE( X_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_VERSION_NUMBER )
245
246 ) RETURNING
247 ROWID, PERSON_PROFILE_ID
248 INTO
249 X_ROWID, X_PERSON_PROFILE_ID;
250
251 l_success := 'Y';
252
253 EXCEPTION
254 WHEN DUP_VAL_ON_INDEX THEN
255 IF INSTRB( SQLERRM, 'HZ_PERSON_PROFILES_U1' ) <> 0 OR
256 INSTRB( SQLERRM, 'HZ_PERSON_PROFILES_PK' ) <> 0
257 THEN
258 DECLARE
259 l_count NUMBER;
260 l_dummy VARCHAR2(1);
261 BEGIN
262 l_count := 1;
263 WHILE l_count > 0 LOOP
264 SELECT HZ_PERSON_PROFILES_S.NEXTVAL
265 INTO X_PERSON_PROFILE_ID FROM dual;
266 BEGIN
267 SELECT 'Y' INTO l_dummy
268 FROM HZ_PERSON_PROFILES
269 WHERE PERSON_PROFILE_ID = X_PERSON_PROFILE_ID;
270 l_count := 1;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 l_count := 0;
274 END;
275 END LOOP;
276 END;
277 ELSE
278 RAISE;
279 END IF;
280
281 END;
282 END LOOP;
283
284 END Insert_Row;
285
286 PROCEDURE Update_Row (
287 X_Rowid IN OUT NOCOPY VARCHAR2,
288 X_PERSON_PROFILE_ID IN NUMBER,
289 X_PARTY_ID IN NUMBER,
290 x_PERSON_NAME IN VARCHAR2,
291 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
292 X_ATTRIBUTE1 IN VARCHAR2,
293 X_ATTRIBUTE2 IN VARCHAR2,
294 X_ATTRIBUTE3 IN VARCHAR2,
295 X_ATTRIBUTE4 IN VARCHAR2,
296 X_ATTRIBUTE5 IN VARCHAR2,
297 X_ATTRIBUTE6 IN VARCHAR2,
298 X_ATTRIBUTE7 IN VARCHAR2,
299 X_ATTRIBUTE8 IN VARCHAR2,
300 X_ATTRIBUTE9 IN VARCHAR2,
301 X_ATTRIBUTE10 IN VARCHAR2,
302 X_ATTRIBUTE11 IN VARCHAR2,
303 X_ATTRIBUTE12 IN VARCHAR2,
304 X_ATTRIBUTE13 IN VARCHAR2,
305 X_ATTRIBUTE14 IN VARCHAR2,
306 X_ATTRIBUTE15 IN VARCHAR2,
307 X_ATTRIBUTE16 IN VARCHAR2,
308 X_ATTRIBUTE17 IN VARCHAR2,
309 X_ATTRIBUTE18 IN VARCHAR2,
310 X_ATTRIBUTE19 IN VARCHAR2,
311 X_ATTRIBUTE20 IN VARCHAR2,
312 X_INTERNAL_FLAG IN VARCHAR2,
313 X_PERSON_PRE_NAME_ADJUNCT IN VARCHAR2,
314 X_PERSON_FIRST_NAME IN VARCHAR2,
315 X_PERSON_MIDDLE_NAME IN VARCHAR2,
316 X_PERSON_LAST_NAME IN VARCHAR2,
317 X_PERSON_NAME_SUFFIX IN VARCHAR2,
318 X_PERSON_TITLE IN VARCHAR2,
319 X_PERSON_ACADEMIC_TITLE IN VARCHAR2,
320 X_PERSON_PREVIOUS_LAST_NAME IN VARCHAR2,
321 X_PERSON_INITIALS IN VARCHAR2,
322 X_KNOWN_AS IN VARCHAR2,
323 X_PERSON_NAME_PHONETIC IN VARCHAR2,
324 X_PERSON_FIRST_NAME_PHONETIC IN VARCHAR2,
325 X_PERSON_LAST_NAME_PHONETIC IN VARCHAR2,
326 X_TAX_REFERENCE IN VARCHAR2,
327 X_JGZZ_FISCAL_CODE IN VARCHAR2,
328 X_PERSON_IDEN_TYPE IN VARCHAR2,
329 X_PERSON_IDENTIFIER IN VARCHAR2,
330 X_DATE_OF_BIRTH IN DATE,
331 X_PLACE_OF_BIRTH IN VARCHAR2,
332 X_DATE_OF_DEATH IN DATE,
333 X_DECEASED_FLAG IN VARCHAR2 DEFAULT NULL,
334 X_GENDER IN VARCHAR2,
335 X_DECLARED_ETHNICITY IN VARCHAR2,
336 X_MARITAL_STATUS IN VARCHAR2,
337 X_MARITAL_STATUS_EFF_DATE IN DATE,
338 X_PERSONAL_INCOME IN NUMBER,
339 X_HEAD_OF_HOUSEHOLD_FLAG IN VARCHAR2,
340 X_HOUSEHOLD_INCOME IN NUMBER,
341 X_HOUSEHOLD_SIZE IN NUMBER,
342 X_RENT_OWN_IND IN VARCHAR2,
343 X_LAST_KNOWN_GPS IN VARCHAR2,
344 X_EFFECTIVE_START_DATE IN DATE,
345 X_EFFECTIVE_END_DATE IN DATE,
346 X_CONTENT_SOURCE_TYPE IN VARCHAR2,
347 X_KNOWN_AS2 IN VARCHAR2,
348 X_KNOWN_AS3 IN VARCHAR2,
349 X_KNOWN_AS4 IN VARCHAR2,
350 X_KNOWN_AS5 IN VARCHAR2,
351 X_MIDDLE_NAME_PHONETIC IN VARCHAR2,
352 X_OBJECT_VERSION_NUMBER IN NUMBER,
353 X_CREATED_BY_MODULE IN VARCHAR2,
354 X_APPLICATION_ID IN NUMBER,
355 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2 DEFAULT NULL,
356 X_VERSION_NUMBER IN NUMBER DEFAULT NULL
357 ) IS
358
359 BEGIN
360
361 UPDATE HZ_PERSON_PROFILES SET
362 PERSON_PROFILE_ID = DECODE( X_PERSON_PROFILE_ID, NULL, PERSON_PROFILE_ID, FND_API.G_MISS_NUM, NULL, X_PERSON_PROFILE_ID ),
363 PARTY_ID = DECODE( X_PARTY_ID, NULL, PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID ),
364 PERSON_NAME = DECODE( X_PERSON_NAME, NULL, PERSON_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_NAME),
365 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
366 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
367 CREATION_DATE = CREATION_DATE,
368 CREATED_BY = CREATED_BY,
369 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
370 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
371 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
372 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
373 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
374 ATTRIBUTE_CATEGORY = DECODE( X_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
375 ATTRIBUTE1 = DECODE( X_ATTRIBUTE1, NULL, ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
376 ATTRIBUTE2 = DECODE( X_ATTRIBUTE2, NULL, ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
377 ATTRIBUTE3 = DECODE( X_ATTRIBUTE3, NULL, ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
378 ATTRIBUTE4 = DECODE( X_ATTRIBUTE4, NULL, ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
379 ATTRIBUTE5 = DECODE( X_ATTRIBUTE5, NULL, ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
380 ATTRIBUTE6 = DECODE( X_ATTRIBUTE6, NULL, ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
381 ATTRIBUTE7 = DECODE( X_ATTRIBUTE7, NULL, ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
382 ATTRIBUTE8 = DECODE( X_ATTRIBUTE8, NULL, ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
383 ATTRIBUTE9 = DECODE( X_ATTRIBUTE9, NULL, ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
384 ATTRIBUTE10 = DECODE( X_ATTRIBUTE10, NULL, ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
385 ATTRIBUTE11 = DECODE( X_ATTRIBUTE11, NULL, ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
386 ATTRIBUTE12 = DECODE( X_ATTRIBUTE12, NULL, ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
387 ATTRIBUTE13 = DECODE( X_ATTRIBUTE13, NULL, ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
388 ATTRIBUTE14 = DECODE( X_ATTRIBUTE14, NULL, ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
389 ATTRIBUTE15 = DECODE( X_ATTRIBUTE15, NULL, ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
390 ATTRIBUTE16 = DECODE( X_ATTRIBUTE16, NULL, ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE16 ),
391 ATTRIBUTE17 = DECODE( X_ATTRIBUTE17, NULL, ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE17 ),
392 ATTRIBUTE18 = DECODE( X_ATTRIBUTE18, NULL, ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE18 ),
393 ATTRIBUTE19 = DECODE( X_ATTRIBUTE19, NULL, ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE19 ),
394 ATTRIBUTE20 = DECODE( X_ATTRIBUTE20, NULL, ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE20 ),
395 INTERNAL_FLAG = DECODE( X_INTERNAL_FLAG, NULL, INTERNAL_FLAG, FND_API.G_MISS_CHAR, NULL, X_INTERNAL_FLAG ),
396 PERSON_PRE_NAME_ADJUNCT = DECODE( X_PERSON_PRE_NAME_ADJUNCT, NULL, PERSON_PRE_NAME_ADJUNCT, FND_API.G_MISS_CHAR, NULL, X_PERSON_PRE_NAME_ADJUNCT ),
397 PERSON_FIRST_NAME = DECODE( X_PERSON_FIRST_NAME, NULL, PERSON_FIRST_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_FIRST_NAME ),
398 PERSON_MIDDLE_NAME = DECODE( X_PERSON_MIDDLE_NAME, NULL, PERSON_MIDDLE_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_MIDDLE_NAME ),
399 PERSON_LAST_NAME = DECODE( X_PERSON_LAST_NAME, NULL, PERSON_LAST_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_LAST_NAME ),
400 PERSON_NAME_SUFFIX = DECODE( X_PERSON_NAME_SUFFIX, NULL, PERSON_NAME_SUFFIX, FND_API.G_MISS_CHAR, NULL, X_PERSON_NAME_SUFFIX ),
401 PERSON_TITLE = DECODE( X_PERSON_TITLE, NULL, PERSON_TITLE, FND_API.G_MISS_CHAR, NULL, X_PERSON_TITLE ),
402 PERSON_ACADEMIC_TITLE = DECODE( X_PERSON_ACADEMIC_TITLE, NULL, PERSON_ACADEMIC_TITLE, FND_API.G_MISS_CHAR, NULL, X_PERSON_ACADEMIC_TITLE ),
403 PERSON_PREVIOUS_LAST_NAME = DECODE( X_PERSON_PREVIOUS_LAST_NAME, NULL, PERSON_PREVIOUS_LAST_NAME, FND_API.G_MISS_CHAR, NULL, X_PERSON_PREVIOUS_LAST_NAME ),
404 PERSON_INITIALS = DECODE( X_PERSON_INITIALS, NULL, PERSON_INITIALS, FND_API.G_MISS_CHAR, NULL, X_PERSON_INITIALS ),
405 KNOWN_AS = DECODE( X_KNOWN_AS, NULL, KNOWN_AS, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS ),
406 PERSON_NAME_PHONETIC = DECODE( X_PERSON_NAME_PHONETIC, NULL, PERSON_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_PERSON_NAME_PHONETIC ),
407 PERSON_FIRST_NAME_PHONETIC = DECODE( X_PERSON_FIRST_NAME_PHONETIC, NULL, PERSON_FIRST_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_PERSON_FIRST_NAME_PHONETIC ),
408 PERSON_LAST_NAME_PHONETIC = DECODE( X_PERSON_LAST_NAME_PHONETIC, NULL, PERSON_LAST_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_PERSON_LAST_NAME_PHONETIC ),
409 TAX_REFERENCE = DECODE( X_TAX_REFERENCE, NULL, TAX_REFERENCE, FND_API.G_MISS_CHAR, NULL, X_TAX_REFERENCE ),
410 JGZZ_FISCAL_CODE = DECODE( X_JGZZ_FISCAL_CODE, NULL, JGZZ_FISCAL_CODE, FND_API.G_MISS_CHAR, NULL, X_JGZZ_FISCAL_CODE ),
411 PERSON_IDEN_TYPE = DECODE( X_PERSON_IDEN_TYPE, NULL, PERSON_IDEN_TYPE, FND_API.G_MISS_CHAR, NULL, X_PERSON_IDEN_TYPE ),
412 PERSON_IDENTIFIER = DECODE( X_PERSON_IDENTIFIER, NULL, PERSON_IDENTIFIER, FND_API.G_MISS_CHAR, NULL, X_PERSON_IDENTIFIER ),
413 -- DATE_OF_BIRTH = DECODE( X_DATE_OF_BIRTH, NULL, DATE_OF_BIRTH, FND_API.G_MISS_DATE, NULL, X_DATE_OF_BIRTH ),
414 DATE_OF_BIRTH = DECODE( TO_CHAR(X_DATE_OF_BIRTH,'DD/MM/YYYY'), NULL, DATE_OF_BIRTH, TO_CHAR(FND_API.G_MISS_DATE,'DD/MM/YYYY'), NULL, X_DATE_OF_BIRTH ),
415 PLACE_OF_BIRTH = DECODE( X_PLACE_OF_BIRTH, NULL, PLACE_OF_BIRTH, FND_API.G_MISS_CHAR, NULL, X_PLACE_OF_BIRTH ),
416 DATE_OF_DEATH = DECODE( X_DATE_OF_DEATH, NULL, DATE_OF_DEATH, FND_API.G_MISS_DATE, NULL, X_DATE_OF_DEATH ),
417 DECEASED_FLAG = DECODE( X_DECEASED_FLAG, NULL, DECEASED_FLAG, FND_API.G_MISS_CHAR, NULL, X_DECEASED_FLAG ),
418 GENDER = DECODE( X_GENDER, NULL, GENDER, FND_API.G_MISS_CHAR, NULL, X_GENDER ),
419 DECLARED_ETHNICITY = DECODE( X_DECLARED_ETHNICITY, NULL, DECLARED_ETHNICITY, FND_API.G_MISS_CHAR, NULL, X_DECLARED_ETHNICITY ),
420 MARITAL_STATUS = DECODE( X_MARITAL_STATUS, NULL, MARITAL_STATUS, FND_API.G_MISS_CHAR, NULL, X_MARITAL_STATUS ),
421 MARITAL_STATUS_EFFECTIVE_DATE = DECODE( X_MARITAL_STATUS_EFF_DATE, NULL, MARITAL_STATUS_EFFECTIVE_DATE, FND_API.G_MISS_DATE, NULL, X_MARITAL_STATUS_EFF_DATE ),
422 PERSONAL_INCOME = DECODE( X_PERSONAL_INCOME, NULL, PERSONAL_INCOME, FND_API.G_MISS_NUM, NULL, X_PERSONAL_INCOME ),
423 HEAD_OF_HOUSEHOLD_FLAG = DECODE( X_HEAD_OF_HOUSEHOLD_FLAG, NULL, HEAD_OF_HOUSEHOLD_FLAG, FND_API.G_MISS_CHAR, 'N', X_HEAD_OF_HOUSEHOLD_FLAG ),
424 HOUSEHOLD_INCOME = DECODE( X_HOUSEHOLD_INCOME, NULL, HOUSEHOLD_INCOME, FND_API.G_MISS_NUM, NULL, X_HOUSEHOLD_INCOME ),
425 HOUSEHOLD_SIZE = DECODE( X_HOUSEHOLD_SIZE, NULL, HOUSEHOLD_SIZE, FND_API.G_MISS_NUM, NULL, X_HOUSEHOLD_SIZE ),
426 RENT_OWN_IND = DECODE( X_RENT_OWN_IND, NULL, RENT_OWN_IND, FND_API.G_MISS_CHAR, NULL, X_RENT_OWN_IND ),
427 LAST_KNOWN_GPS = DECODE( X_LAST_KNOWN_GPS, NULL, LAST_KNOWN_GPS, FND_API.G_MISS_CHAR, NULL, X_LAST_KNOWN_GPS ),
428 EFFECTIVE_START_DATE = DECODE( X_EFFECTIVE_START_DATE, NULL, EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_START_DATE ),
429 EFFECTIVE_END_DATE = DECODE( X_EFFECTIVE_END_DATE, NULL, EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_END_DATE ),
430 CONTENT_SOURCE_TYPE = DECODE( X_CONTENT_SOURCE_TYPE, NULL, CONTENT_SOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, X_CONTENT_SOURCE_TYPE ),
431 KNOWN_AS2 = DECODE( X_KNOWN_AS2, NULL, KNOWN_AS2, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS2 ),
432 KNOWN_AS3 = DECODE( X_KNOWN_AS3, NULL, KNOWN_AS3, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS3 ),
433 KNOWN_AS4 = DECODE( X_KNOWN_AS4, NULL, KNOWN_AS4, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS4 ),
434 KNOWN_AS5 = DECODE( X_KNOWN_AS5, NULL, KNOWN_AS5, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS5 ),
435 MIDDLE_NAME_PHONETIC = DECODE( X_MIDDLE_NAME_PHONETIC, NULL, MIDDLE_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_MIDDLE_NAME_PHONETIC ),
436 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
437 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
438 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
439 ACTUAL_CONTENT_SOURCE = DECODE( X_ACTUAL_CONTENT_SOURCE, NULL, ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, NULL, X_ACTUAL_CONTENT_SOURCE ),
440 VERSION_NUMBER = DECODE( X_VERSION_NUMBER, NULL, VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_VERSION_NUMBER )
441 WHERE ROWID = X_RowId;
442
443 IF ( SQL%NOTFOUND ) THEN
444 RAISE NO_DATA_FOUND;
445 END IF;
446
447 END Update_Row;
448
449 PROCEDURE Lock_Row (
450 X_Rowid IN OUT NOCOPY VARCHAR2,
451 X_PERSON_PROFILE_ID IN NUMBER,
452 X_PARTY_ID IN NUMBER,
453 X_LAST_UPDATE_DATE IN DATE,
454 X_LAST_UPDATED_BY IN NUMBER,
455 X_CREATION_DATE IN DATE,
456 X_CREATED_BY IN NUMBER,
457 X_LAST_UPDATE_LOGIN IN NUMBER,
458 X_REQUEST_ID IN NUMBER,
459 X_PROGRAM_APPLICATION_ID IN NUMBER,
460 X_PROGRAM_ID IN NUMBER,
461 X_PROGRAM_UPDATE_DATE IN DATE,
462 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
463 X_ATTRIBUTE1 IN VARCHAR2,
464 X_ATTRIBUTE2 IN VARCHAR2,
465 X_ATTRIBUTE3 IN VARCHAR2,
466 X_ATTRIBUTE4 IN VARCHAR2,
467 X_ATTRIBUTE5 IN VARCHAR2,
468 X_ATTRIBUTE6 IN VARCHAR2,
469 X_ATTRIBUTE7 IN VARCHAR2,
470 X_ATTRIBUTE8 IN VARCHAR2,
471 X_ATTRIBUTE9 IN VARCHAR2,
472 X_ATTRIBUTE10 IN VARCHAR2,
473 X_ATTRIBUTE11 IN VARCHAR2,
474 X_ATTRIBUTE12 IN VARCHAR2,
475 X_ATTRIBUTE13 IN VARCHAR2,
476 X_ATTRIBUTE14 IN VARCHAR2,
477 X_ATTRIBUTE15 IN VARCHAR2,
478 X_ATTRIBUTE16 IN VARCHAR2,
479 X_ATTRIBUTE17 IN VARCHAR2,
480 X_ATTRIBUTE18 IN VARCHAR2,
481 X_ATTRIBUTE19 IN VARCHAR2,
482 X_ATTRIBUTE20 IN VARCHAR2,
483 X_INTERNAL_FLAG IN VARCHAR2,
484 X_PERSON_PRE_NAME_ADJUNCT IN VARCHAR2,
485 X_PERSON_FIRST_NAME IN VARCHAR2,
486 X_PERSON_MIDDLE_NAME IN VARCHAR2,
487 X_PERSON_LAST_NAME IN VARCHAR2,
488 X_PERSON_NAME_SUFFIX IN VARCHAR2,
489 X_PERSON_TITLE IN VARCHAR2,
490 X_PERSON_ACADEMIC_TITLE IN VARCHAR2,
491 X_PERSON_PREVIOUS_LAST_NAME IN VARCHAR2,
492 X_PERSON_INITIALS IN VARCHAR2,
493 X_KNOWN_AS IN VARCHAR2,
494 X_PERSON_NAME_PHONETIC IN VARCHAR2,
495 X_PERSON_FIRST_NAME_PHONETIC IN VARCHAR2,
496 X_PERSON_LAST_NAME_PHONETIC IN VARCHAR2,
497 X_TAX_REFERENCE IN VARCHAR2,
498 X_JGZZ_FISCAL_CODE IN VARCHAR2,
499 X_PERSON_IDEN_TYPE IN VARCHAR2,
500 X_PERSON_IDENTIFIER IN VARCHAR2,
501 X_DATE_OF_BIRTH IN DATE,
502 X_PLACE_OF_BIRTH IN VARCHAR2,
503 X_DATE_OF_DEATH IN DATE,
504 X_DECEASED_FLAG IN VARCHAR2,
505 X_GENDER IN VARCHAR2,
506 X_DECLARED_ETHNICITY IN VARCHAR2,
507 X_MARITAL_STATUS IN VARCHAR2,
508 X_MARITAL_STATUS_EFF_DATE IN DATE,
509 X_PERSONAL_INCOME IN NUMBER,
510 X_HEAD_OF_HOUSEHOLD_FLAG IN VARCHAR2,
511 X_HOUSEHOLD_INCOME IN NUMBER,
512 X_HOUSEHOLD_SIZE IN NUMBER,
513 X_RENT_OWN_IND IN VARCHAR2,
514 X_LAST_KNOWN_GPS IN VARCHAR2,
515 X_EFFECTIVE_START_DATE IN DATE,
516 X_EFFECTIVE_END_DATE IN DATE,
517 X_CONTENT_SOURCE_TYPE IN VARCHAR2,
518 X_KNOWN_AS2 IN VARCHAR2,
519 X_KNOWN_AS3 IN VARCHAR2,
520 X_KNOWN_AS4 IN VARCHAR2,
521 X_KNOWN_AS5 IN VARCHAR2,
522 X_MIDDLE_NAME_PHONETIC IN VARCHAR2,
523 X_OBJECT_VERSION_NUMBER IN NUMBER,
524 X_CREATED_BY_MODULE IN VARCHAR2,
525 X_APPLICATION_ID IN NUMBER,
526 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2 DEFAULT NULL
527 ) IS
528
529 CURSOR C IS
530 SELECT * FROM HZ_PERSON_PROFILES
531 WHERE ROWID = x_Rowid
532 FOR UPDATE NOWAIT;
533 Recinfo C%ROWTYPE;
534
535 BEGIN
536
537 OPEN C;
538 FETCH C INTO Recinfo;
539 IF ( C%NOTFOUND ) THEN
540 CLOSE C;
541 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
542 APP_EXCEPTION.RAISE_EXCEPTION;
543 END IF;
544 CLOSE C;
545
546 IF (
547 ( ( Recinfo.PERSON_PROFILE_ID = X_PERSON_PROFILE_ID )
548 OR ( ( Recinfo.PERSON_PROFILE_ID IS NULL )
549 AND ( X_PERSON_PROFILE_ID IS NULL ) ) )
550 AND ( ( Recinfo.PARTY_ID = X_PARTY_ID )
551 OR ( ( Recinfo.PARTY_ID IS NULL )
552 AND ( X_PARTY_ID IS NULL ) ) )
553 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
554 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
555 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
556 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
557 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
558 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
559 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
560 OR ( ( Recinfo.CREATION_DATE IS NULL )
561 AND ( X_CREATION_DATE IS NULL ) ) )
562 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
563 OR ( ( Recinfo.CREATED_BY IS NULL )
564 AND ( X_CREATED_BY IS NULL ) ) )
565 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
566 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
567 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
568 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
569 OR ( ( Recinfo.REQUEST_ID IS NULL )
570 AND ( X_REQUEST_ID IS NULL ) ) )
571 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
572 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
573 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
574 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
575 OR ( ( Recinfo.PROGRAM_ID IS NULL )
576 AND ( X_PROGRAM_ID IS NULL ) ) )
577 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
578 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
579 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
580 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
581 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
582 AND ( X_ATTRIBUTE_CATEGORY IS NULL ) ) )
583 AND ( ( Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1 )
584 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
585 AND ( X_ATTRIBUTE1 IS NULL ) ) )
586 AND ( ( Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2 )
587 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
588 AND ( X_ATTRIBUTE2 IS NULL ) ) )
589 AND ( ( Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3 )
590 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
591 AND ( X_ATTRIBUTE3 IS NULL ) ) )
592 AND ( ( Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4 )
593 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
594 AND ( X_ATTRIBUTE4 IS NULL ) ) )
595 AND ( ( Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5 )
596 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
597 AND ( X_ATTRIBUTE5 IS NULL ) ) )
598 AND ( ( Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6 )
599 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
600 AND ( X_ATTRIBUTE6 IS NULL ) ) )
601 AND ( ( Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7 )
602 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
603 AND ( X_ATTRIBUTE7 IS NULL ) ) )
604 AND ( ( Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8 )
605 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
606 AND ( X_ATTRIBUTE8 IS NULL ) ) )
607 AND ( ( Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9 )
608 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
609 AND ( X_ATTRIBUTE9 IS NULL ) ) )
610 AND ( ( Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10 )
611 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
612 AND ( X_ATTRIBUTE10 IS NULL ) ) )
613 AND ( ( Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11 )
614 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
615 AND ( X_ATTRIBUTE11 IS NULL ) ) )
616 AND ( ( Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12 )
617 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
618 AND ( X_ATTRIBUTE12 IS NULL ) ) )
619 AND ( ( Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13 )
620 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
621 AND ( X_ATTRIBUTE13 IS NULL ) ) )
622 AND ( ( Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14 )
623 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
624 AND ( X_ATTRIBUTE14 IS NULL ) ) )
625 AND ( ( Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15 )
626 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
627 AND ( X_ATTRIBUTE15 IS NULL ) ) )
628 AND ( ( Recinfo.ATTRIBUTE16 = X_ATTRIBUTE16 )
629 OR ( ( Recinfo.ATTRIBUTE16 IS NULL )
630 AND ( X_ATTRIBUTE16 IS NULL ) ) )
631 AND ( ( Recinfo.ATTRIBUTE17 = X_ATTRIBUTE17 )
632 OR ( ( Recinfo.ATTRIBUTE17 IS NULL )
633 AND ( X_ATTRIBUTE17 IS NULL ) ) )
634 AND ( ( Recinfo.ATTRIBUTE18 = X_ATTRIBUTE18 )
635 OR ( ( Recinfo.ATTRIBUTE18 IS NULL )
636 AND ( X_ATTRIBUTE18 IS NULL ) ) )
637 AND ( ( Recinfo.ATTRIBUTE19 = X_ATTRIBUTE19 )
638 OR ( ( Recinfo.ATTRIBUTE19 IS NULL )
639 AND ( X_ATTRIBUTE19 IS NULL ) ) )
640 AND ( ( Recinfo.ATTRIBUTE20 = X_ATTRIBUTE20 )
641 OR ( ( Recinfo.ATTRIBUTE20 IS NULL )
642 AND ( X_ATTRIBUTE20 IS NULL ) ) )
643 AND ( ( Recinfo.INTERNAL_FLAG = X_INTERNAL_FLAG )
644 OR ( ( Recinfo.INTERNAL_FLAG IS NULL )
645 AND ( X_INTERNAL_FLAG IS NULL ) ) )
646 AND ( ( Recinfo.PERSON_PRE_NAME_ADJUNCT = X_PERSON_PRE_NAME_ADJUNCT )
647 OR ( ( Recinfo.PERSON_PRE_NAME_ADJUNCT IS NULL )
648 AND ( X_PERSON_PRE_NAME_ADJUNCT IS NULL ) ) )
649 AND ( ( Recinfo.PERSON_FIRST_NAME = X_PERSON_FIRST_NAME )
650 OR ( ( Recinfo.PERSON_FIRST_NAME IS NULL )
651 AND ( X_PERSON_FIRST_NAME IS NULL ) ) )
652 AND ( ( Recinfo.PERSON_MIDDLE_NAME = X_PERSON_MIDDLE_NAME )
653 OR ( ( Recinfo.PERSON_MIDDLE_NAME IS NULL )
654 AND ( X_PERSON_MIDDLE_NAME IS NULL ) ) )
655 AND ( ( Recinfo.PERSON_LAST_NAME = X_PERSON_LAST_NAME )
656 OR ( ( Recinfo.PERSON_LAST_NAME IS NULL )
657 AND ( X_PERSON_LAST_NAME IS NULL ) ) )
658 AND ( ( Recinfo.PERSON_NAME_SUFFIX = X_PERSON_NAME_SUFFIX )
659 OR ( ( Recinfo.PERSON_NAME_SUFFIX IS NULL )
660 AND ( X_PERSON_NAME_SUFFIX IS NULL ) ) )
661 AND ( ( Recinfo.PERSON_TITLE = X_PERSON_TITLE )
662 OR ( ( Recinfo.PERSON_TITLE IS NULL )
663 AND ( X_PERSON_TITLE IS NULL ) ) )
664 AND ( ( Recinfo.PERSON_ACADEMIC_TITLE = X_PERSON_ACADEMIC_TITLE )
665 OR ( ( Recinfo.PERSON_ACADEMIC_TITLE IS NULL )
666 AND ( X_PERSON_ACADEMIC_TITLE IS NULL ) ) )
667 AND ( ( Recinfo.PERSON_PREVIOUS_LAST_NAME = X_PERSON_PREVIOUS_LAST_NAME )
668 OR ( ( Recinfo.PERSON_PREVIOUS_LAST_NAME IS NULL )
669 AND ( X_PERSON_PREVIOUS_LAST_NAME IS NULL ) ) )
670 AND ( ( Recinfo.PERSON_INITIALS = X_PERSON_INITIALS )
671 OR ( ( Recinfo.PERSON_INITIALS IS NULL )
672 AND ( X_PERSON_INITIALS IS NULL ) ) )
673 AND ( ( Recinfo.KNOWN_AS = X_KNOWN_AS )
674 OR ( ( Recinfo.KNOWN_AS IS NULL )
675 AND ( X_KNOWN_AS IS NULL ) ) )
676 AND ( ( Recinfo.PERSON_NAME_PHONETIC = X_PERSON_NAME_PHONETIC )
677 OR ( ( Recinfo.PERSON_NAME_PHONETIC IS NULL )
678 AND ( X_PERSON_NAME_PHONETIC IS NULL ) ) )
679 AND ( ( Recinfo.PERSON_FIRST_NAME_PHONETIC = X_PERSON_FIRST_NAME_PHONETIC )
680 OR ( ( Recinfo.PERSON_FIRST_NAME_PHONETIC IS NULL )
681 AND ( X_PERSON_FIRST_NAME_PHONETIC IS NULL ) ) )
682 AND ( ( Recinfo.PERSON_LAST_NAME_PHONETIC = X_PERSON_LAST_NAME_PHONETIC )
683 OR ( ( Recinfo.PERSON_LAST_NAME_PHONETIC IS NULL )
684 AND ( X_PERSON_LAST_NAME_PHONETIC IS NULL ) ) )
685 AND ( ( Recinfo.TAX_REFERENCE = X_TAX_REFERENCE )
686 OR ( ( Recinfo.TAX_REFERENCE IS NULL )
687 AND ( X_TAX_REFERENCE IS NULL ) ) )
688 AND ( ( Recinfo.JGZZ_FISCAL_CODE = X_JGZZ_FISCAL_CODE )
689 OR ( ( Recinfo.JGZZ_FISCAL_CODE IS NULL )
690 AND ( X_JGZZ_FISCAL_CODE IS NULL ) ) )
691 AND ( ( Recinfo.PERSON_IDEN_TYPE = X_PERSON_IDEN_TYPE )
692 OR ( ( Recinfo.PERSON_IDEN_TYPE IS NULL )
693 AND ( X_PERSON_IDEN_TYPE IS NULL ) ) )
694 AND ( ( Recinfo.PERSON_IDENTIFIER = X_PERSON_IDENTIFIER )
695 OR ( ( Recinfo.PERSON_IDENTIFIER IS NULL )
696 AND ( X_PERSON_IDENTIFIER IS NULL ) ) )
697 AND ( ( Recinfo.DATE_OF_BIRTH = X_DATE_OF_BIRTH )
698 OR ( ( Recinfo.DATE_OF_BIRTH IS NULL )
699 AND ( X_DATE_OF_BIRTH IS NULL ) ) )
700 AND ( ( Recinfo.PLACE_OF_BIRTH = X_PLACE_OF_BIRTH )
701 OR ( ( Recinfo.PLACE_OF_BIRTH IS NULL )
702 AND ( X_PLACE_OF_BIRTH IS NULL ) ) )
703 AND ( ( Recinfo.DATE_OF_DEATH = X_DATE_OF_DEATH )
704 OR ( ( Recinfo.DATE_OF_DEATH IS NULL )
705 AND ( X_DATE_OF_DEATH IS NULL ) ) )
706 AND ( ( Recinfo.DECEASED_FLAG = X_DECEASED_FLAG )
707 OR ( ( Recinfo.DECEASED_FLAG IS NULL )
708 AND ( X_DECEASED_FLAG IS NULL ) ) )
709 AND ( ( Recinfo.GENDER = X_GENDER )
710 OR ( ( Recinfo.GENDER IS NULL )
711 AND ( X_GENDER IS NULL ) ) )
712 AND ( ( Recinfo.DECLARED_ETHNICITY = X_DECLARED_ETHNICITY )
713 OR ( ( Recinfo.DECLARED_ETHNICITY IS NULL )
714 AND ( X_DECLARED_ETHNICITY IS NULL ) ) )
715 AND ( ( Recinfo.MARITAL_STATUS = X_MARITAL_STATUS )
716 OR ( ( Recinfo.MARITAL_STATUS IS NULL )
717 AND ( X_MARITAL_STATUS IS NULL ) ) )
718 AND ( ( Recinfo.MARITAL_STATUS_EFFECTIVE_DATE = X_MARITAL_STATUS_EFF_DATE )
719 OR ( ( Recinfo.MARITAL_STATUS_EFFECTIVE_DATE IS NULL )
720 AND ( X_MARITAL_STATUS_EFF_DATE IS NULL ) ) )
721 AND ( ( Recinfo.PERSONAL_INCOME = X_PERSONAL_INCOME )
722 OR ( ( Recinfo.PERSONAL_INCOME IS NULL )
723 AND ( X_PERSONAL_INCOME IS NULL ) ) )
724 AND ( ( Recinfo.HEAD_OF_HOUSEHOLD_FLAG = X_HEAD_OF_HOUSEHOLD_FLAG )
725 OR ( ( Recinfo.HEAD_OF_HOUSEHOLD_FLAG IS NULL )
726 AND ( X_HEAD_OF_HOUSEHOLD_FLAG IS NULL ) ) )
727 AND ( ( Recinfo.HOUSEHOLD_INCOME = X_HOUSEHOLD_INCOME )
728 OR ( ( Recinfo.HOUSEHOLD_INCOME IS NULL )
729 AND ( X_HOUSEHOLD_INCOME IS NULL ) ) )
730 AND ( ( Recinfo.HOUSEHOLD_SIZE = X_HOUSEHOLD_SIZE )
731 OR ( ( Recinfo.HOUSEHOLD_SIZE IS NULL )
732 AND ( X_HOUSEHOLD_SIZE IS NULL ) ) )
733 AND ( ( Recinfo.RENT_OWN_IND = X_RENT_OWN_IND )
734 OR ( ( Recinfo.RENT_OWN_IND IS NULL )
735 AND ( X_RENT_OWN_IND IS NULL ) ) )
736 AND ( ( Recinfo.LAST_KNOWN_GPS = X_LAST_KNOWN_GPS )
737 OR ( ( Recinfo.LAST_KNOWN_GPS IS NULL )
738 AND ( X_LAST_KNOWN_GPS IS NULL ) ) )
739 AND ( ( Recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE )
740 OR ( ( Recinfo.EFFECTIVE_START_DATE IS NULL )
741 AND ( X_EFFECTIVE_START_DATE IS NULL ) ) )
742 AND ( ( Recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE )
743 OR ( ( Recinfo.EFFECTIVE_END_DATE IS NULL )
744 AND ( X_EFFECTIVE_END_DATE IS NULL ) ) )
745 AND ( ( Recinfo.CONTENT_SOURCE_TYPE = X_CONTENT_SOURCE_TYPE )
746 OR ( ( Recinfo.CONTENT_SOURCE_TYPE IS NULL )
747 AND ( X_CONTENT_SOURCE_TYPE IS NULL ) ) )
748 AND ( ( Recinfo.KNOWN_AS2 = X_KNOWN_AS2 )
749 OR ( ( Recinfo.KNOWN_AS2 IS NULL )
750 AND ( X_KNOWN_AS2 IS NULL ) ) )
751 AND ( ( Recinfo.KNOWN_AS3 = X_KNOWN_AS3 )
752 OR ( ( Recinfo.KNOWN_AS3 IS NULL )
753 AND ( X_KNOWN_AS3 IS NULL ) ) )
754 AND ( ( Recinfo.KNOWN_AS4 = X_KNOWN_AS4 )
755 OR ( ( Recinfo.KNOWN_AS4 IS NULL )
756 AND ( X_KNOWN_AS4 IS NULL ) ) )
757 AND ( ( Recinfo.KNOWN_AS5 = X_KNOWN_AS5 )
758 OR ( ( Recinfo.KNOWN_AS5 IS NULL )
759 AND ( X_KNOWN_AS5 IS NULL ) ) )
760 AND ( ( Recinfo.MIDDLE_NAME_PHONETIC = X_MIDDLE_NAME_PHONETIC )
761 OR ( ( Recinfo.MIDDLE_NAME_PHONETIC IS NULL )
762 AND ( X_MIDDLE_NAME_PHONETIC IS NULL ) ) )
763 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
764 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
765 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
766 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
767 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
768 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
769 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
770 OR ( ( Recinfo.APPLICATION_ID IS NULL )
771 AND ( X_APPLICATION_ID IS NULL ) ) )
772 AND ( ( Recinfo.ACTUAL_CONTENT_SOURCE = X_ACTUAL_CONTENT_SOURCE )
773 OR ( ( Recinfo.ACTUAL_CONTENT_SOURCE IS NULL )
774 AND ( X_ACTUAL_CONTENT_SOURCE IS NULL ) ) )
775 ) THEN
776 RETURN;
777 ELSE
778 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
779 APP_EXCEPTION.RAISE_EXCEPTION;
780 END IF;
781
782 END Lock_Row;
783
784 PROCEDURE Select_Row (
785 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER,
786 X_PARTY_ID OUT NOCOPY NUMBER,
787 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
788 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
789 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
790 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
791 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
792 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
793 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
794 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
795 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
796 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
797 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
798 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
799 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
800 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
801 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
802 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
803 X_ATTRIBUTE16 OUT NOCOPY VARCHAR2,
804 X_ATTRIBUTE17 OUT NOCOPY VARCHAR2,
805 X_ATTRIBUTE18 OUT NOCOPY VARCHAR2,
806 X_ATTRIBUTE19 OUT NOCOPY VARCHAR2,
807 X_ATTRIBUTE20 OUT NOCOPY VARCHAR2,
808 X_INTERNAL_FLAG OUT NOCOPY VARCHAR2,
809 X_PERSON_PRE_NAME_ADJUNCT OUT NOCOPY VARCHAR2,
810 X_PERSON_FIRST_NAME OUT NOCOPY VARCHAR2,
811 X_PERSON_MIDDLE_NAME OUT NOCOPY VARCHAR2,
812 X_PERSON_LAST_NAME OUT NOCOPY VARCHAR2,
813 X_PERSON_NAME_SUFFIX OUT NOCOPY VARCHAR2,
814 X_PERSON_TITLE OUT NOCOPY VARCHAR2,
815 X_PERSON_ACADEMIC_TITLE OUT NOCOPY VARCHAR2,
816 X_PERSON_PREVIOUS_LAST_NAME OUT NOCOPY VARCHAR2,
817 X_PERSON_INITIALS OUT NOCOPY VARCHAR2,
818 X_KNOWN_AS OUT NOCOPY VARCHAR2,
819 X_PERSON_NAME_PHONETIC OUT NOCOPY VARCHAR2,
820 X_PERSON_FIRST_NAME_PHONETIC OUT NOCOPY VARCHAR2,
821 X_PERSON_LAST_NAME_PHONETIC OUT NOCOPY VARCHAR2,
822 X_TAX_REFERENCE OUT NOCOPY VARCHAR2,
823 X_JGZZ_FISCAL_CODE OUT NOCOPY VARCHAR2,
824 X_PERSON_IDEN_TYPE OUT NOCOPY VARCHAR2,
825 X_PERSON_IDENTIFIER OUT NOCOPY VARCHAR2,
826 X_DATE_OF_BIRTH OUT NOCOPY DATE,
827 X_PLACE_OF_BIRTH OUT NOCOPY VARCHAR2,
828 X_DATE_OF_DEATH OUT NOCOPY DATE,
829 X_DECEASED_FLAG OUT NOCOPY VARCHAR2,
830 X_GENDER OUT NOCOPY VARCHAR2,
831 X_DECLARED_ETHNICITY OUT NOCOPY VARCHAR2,
832 X_MARITAL_STATUS OUT NOCOPY VARCHAR2,
833 X_MARITAL_STATUS_EFF_DATE OUT NOCOPY DATE,
834 X_PERSONAL_INCOME OUT NOCOPY NUMBER,
835 X_HEAD_OF_HOUSEHOLD_FLAG OUT NOCOPY VARCHAR2,
836 X_HOUSEHOLD_INCOME OUT NOCOPY NUMBER,
837 X_HOUSEHOLD_SIZE OUT NOCOPY NUMBER,
838 X_RENT_OWN_IND OUT NOCOPY VARCHAR2,
839 X_LAST_KNOWN_GPS OUT NOCOPY VARCHAR2,
840 X_EFFECTIVE_START_DATE OUT NOCOPY DATE,
841 X_EFFECTIVE_END_DATE OUT NOCOPY DATE,
842 X_CONTENT_SOURCE_TYPE OUT NOCOPY VARCHAR2,
843 X_KNOWN_AS2 OUT NOCOPY VARCHAR2,
844 X_KNOWN_AS3 OUT NOCOPY VARCHAR2,
845 X_KNOWN_AS4 OUT NOCOPY VARCHAR2,
846 X_KNOWN_AS5 OUT NOCOPY VARCHAR2,
847 X_MIDDLE_NAME_PHONETIC OUT NOCOPY VARCHAR2,
848 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
849 X_APPLICATION_ID OUT NOCOPY NUMBER,
850 X_ACTUAL_CONTENT_SOURCE OUT NOCOPY VARCHAR2
851 ) IS
852
853 BEGIN
854
855 SELECT
856 NVL( PERSON_PROFILE_ID, FND_API.G_MISS_NUM ),
857 NVL( PARTY_ID, FND_API.G_MISS_NUM ),
858 NVL( ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR ),
859 NVL( ATTRIBUTE1, FND_API.G_MISS_CHAR ),
860 NVL( ATTRIBUTE2, FND_API.G_MISS_CHAR ),
861 NVL( ATTRIBUTE3, FND_API.G_MISS_CHAR ),
862 NVL( ATTRIBUTE4, FND_API.G_MISS_CHAR ),
863 NVL( ATTRIBUTE5, FND_API.G_MISS_CHAR ),
864 NVL( ATTRIBUTE6, FND_API.G_MISS_CHAR ),
865 NVL( ATTRIBUTE7, FND_API.G_MISS_CHAR ),
866 NVL( ATTRIBUTE8, FND_API.G_MISS_CHAR ),
867 NVL( ATTRIBUTE9, FND_API.G_MISS_CHAR ),
868 NVL( ATTRIBUTE10, FND_API.G_MISS_CHAR ),
869 NVL( ATTRIBUTE11, FND_API.G_MISS_CHAR ),
870 NVL( ATTRIBUTE12, FND_API.G_MISS_CHAR ),
871 NVL( ATTRIBUTE13, FND_API.G_MISS_CHAR ),
872 NVL( ATTRIBUTE14, FND_API.G_MISS_CHAR ),
873 NVL( ATTRIBUTE15, FND_API.G_MISS_CHAR ),
874 NVL( ATTRIBUTE16, FND_API.G_MISS_CHAR ),
875 NVL( ATTRIBUTE17, FND_API.G_MISS_CHAR ),
876 NVL( ATTRIBUTE18, FND_API.G_MISS_CHAR ),
877 NVL( ATTRIBUTE19, FND_API.G_MISS_CHAR ),
878 NVL( ATTRIBUTE20, FND_API.G_MISS_CHAR ),
879 NVL( INTERNAL_FLAG, FND_API.G_MISS_CHAR ),
880 NVL( PERSON_PRE_NAME_ADJUNCT, FND_API.G_MISS_CHAR ),
881 NVL( PERSON_FIRST_NAME, FND_API.G_MISS_CHAR ),
882 NVL( PERSON_MIDDLE_NAME, FND_API.G_MISS_CHAR ),
883 NVL( PERSON_LAST_NAME, FND_API.G_MISS_CHAR ),
884 NVL( PERSON_NAME_SUFFIX, FND_API.G_MISS_CHAR ),
885 NVL( PERSON_TITLE, FND_API.G_MISS_CHAR ),
886 NVL( PERSON_ACADEMIC_TITLE, FND_API.G_MISS_CHAR ),
887 NVL( PERSON_PREVIOUS_LAST_NAME, FND_API.G_MISS_CHAR ),
888 NVL( PERSON_INITIALS, FND_API.G_MISS_CHAR ),
889 NVL( KNOWN_AS, FND_API.G_MISS_CHAR ),
890 NVL( PERSON_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
891 NVL( PERSON_FIRST_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
892 NVL( PERSON_LAST_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
893 NVL( TAX_REFERENCE, FND_API.G_MISS_CHAR ),
894 NVL( JGZZ_FISCAL_CODE, FND_API.G_MISS_CHAR ),
895 NVL( PERSON_IDEN_TYPE, FND_API.G_MISS_CHAR ),
896 NVL( PERSON_IDENTIFIER, FND_API.G_MISS_CHAR ),
897 NVL( DATE_OF_BIRTH, FND_API.G_MISS_DATE ),
898 NVL( PLACE_OF_BIRTH, FND_API.G_MISS_CHAR ),
899 NVL( DATE_OF_DEATH, FND_API.G_MISS_DATE ),
900 NVL( DECEASED_FLAG, FND_API.G_MISS_CHAR ),
901 NVL( GENDER, FND_API.G_MISS_CHAR ),
902 NVL( DECLARED_ETHNICITY, FND_API.G_MISS_CHAR ),
903 NVL( MARITAL_STATUS, FND_API.G_MISS_CHAR ),
904 NVL( MARITAL_STATUS_EFFECTIVE_DATE, FND_API.G_MISS_DATE ),
905 NVL( PERSONAL_INCOME, FND_API.G_MISS_NUM ),
906 NVL( HEAD_OF_HOUSEHOLD_FLAG, FND_API.G_MISS_CHAR ),
907 NVL( HOUSEHOLD_INCOME, FND_API.G_MISS_NUM ),
908 NVL( HOUSEHOLD_SIZE, FND_API.G_MISS_NUM ),
909 NVL( RENT_OWN_IND, FND_API.G_MISS_CHAR ),
910 NVL( LAST_KNOWN_GPS, FND_API.G_MISS_CHAR ),
911 NVL( EFFECTIVE_START_DATE, FND_API.G_MISS_DATE ),
912 NVL( EFFECTIVE_END_DATE, FND_API.G_MISS_DATE ),
913 NVL( CONTENT_SOURCE_TYPE, FND_API.G_MISS_CHAR ),
914 NVL( KNOWN_AS2, FND_API.G_MISS_CHAR ),
915 NVL( KNOWN_AS3, FND_API.G_MISS_CHAR ),
916 NVL( KNOWN_AS4, FND_API.G_MISS_CHAR ),
917 NVL( KNOWN_AS5, FND_API.G_MISS_CHAR ),
918 NVL( MIDDLE_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
919 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
920 NVL( APPLICATION_ID, FND_API.G_MISS_NUM ),
921 NVL( ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR )
922 INTO
923 X_PERSON_PROFILE_ID,
924 X_PARTY_ID,
925 X_ATTRIBUTE_CATEGORY,
926 X_ATTRIBUTE1,
927 X_ATTRIBUTE2,
928 X_ATTRIBUTE3,
929 X_ATTRIBUTE4,
930 X_ATTRIBUTE5,
931 X_ATTRIBUTE6,
932 X_ATTRIBUTE7,
933 X_ATTRIBUTE8,
934 X_ATTRIBUTE9,
935 X_ATTRIBUTE10,
936 X_ATTRIBUTE11,
937 X_ATTRIBUTE12,
938 X_ATTRIBUTE13,
939 X_ATTRIBUTE14,
940 X_ATTRIBUTE15,
941 X_ATTRIBUTE16,
942 X_ATTRIBUTE17,
943 X_ATTRIBUTE18,
944 X_ATTRIBUTE19,
945 X_ATTRIBUTE20,
946 X_INTERNAL_FLAG,
947 X_PERSON_PRE_NAME_ADJUNCT,
948 X_PERSON_FIRST_NAME,
949 X_PERSON_MIDDLE_NAME,
950 X_PERSON_LAST_NAME,
951 X_PERSON_NAME_SUFFIX,
952 X_PERSON_TITLE,
953 X_PERSON_ACADEMIC_TITLE,
954 X_PERSON_PREVIOUS_LAST_NAME,
955 X_PERSON_INITIALS,
956 X_KNOWN_AS,
957 X_PERSON_NAME_PHONETIC,
958 X_PERSON_FIRST_NAME_PHONETIC,
959 X_PERSON_LAST_NAME_PHONETIC,
960 X_TAX_REFERENCE,
961 X_JGZZ_FISCAL_CODE,
962 X_PERSON_IDEN_TYPE,
963 X_PERSON_IDENTIFIER,
964 X_DATE_OF_BIRTH,
965 X_PLACE_OF_BIRTH,
966 X_DATE_OF_DEATH,
967 X_DECEASED_FLAG,
968 X_GENDER,
969 X_DECLARED_ETHNICITY,
970 X_MARITAL_STATUS,
971 X_MARITAL_STATUS_EFF_DATE,
972 X_PERSONAL_INCOME,
973 X_HEAD_OF_HOUSEHOLD_FLAG,
974 X_HOUSEHOLD_INCOME,
975 X_HOUSEHOLD_SIZE,
976 X_RENT_OWN_IND,
977 X_LAST_KNOWN_GPS,
978 X_EFFECTIVE_START_DATE,
979 X_EFFECTIVE_END_DATE,
980 X_CONTENT_SOURCE_TYPE,
981 X_KNOWN_AS2,
982 X_KNOWN_AS3,
983 X_KNOWN_AS4,
984 X_KNOWN_AS5,
985 X_MIDDLE_NAME_PHONETIC,
986 X_CREATED_BY_MODULE,
987 X_APPLICATION_ID,
988 X_ACTUAL_CONTENT_SOURCE
989 FROM HZ_PERSON_PROFILES
990 WHERE PERSON_PROFILE_ID = X_PERSON_PROFILE_ID;
991
992 EXCEPTION
993 WHEN NO_DATA_FOUND THEN
994 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
995 FND_MESSAGE.SET_TOKEN( 'RECORD', 'person_profile_rec');
996 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_PERSON_PROFILE_ID ) );
997 FND_MSG_PUB.ADD;
998 RAISE FND_API.G_EXC_ERROR;
999
1000 END Select_Row;
1001
1002 PROCEDURE Delete_Row (
1003 X_PERSON_PROFILE_ID IN NUMBER
1004 ) IS
1005
1006 BEGIN
1007
1008 DELETE FROM HZ_PERSON_PROFILES
1009 WHERE PERSON_PROFILE_ID = X_PERSON_PROFILE_ID;
1010
1011 IF ( SQL%NOTFOUND ) THEN
1012 RAISE NO_DATA_FOUND;
1013 END IF;
1014
1015 END Delete_Row;
1016
1017 END HZ_PERSON_PROFILES_PKG;