[Home] [Help]
PACKAGE BODY: APPS.HZ_PERSON_PROFILES_PKG
Source
1 PACKAGE BODY HZ_PERSON_PROFILES_PKG AS
2 /*$Header: ARHPERTB.pls 115.20 2003/09/03 20:55:33 kttang 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 PLACE_OF_BIRTH = DECODE( X_PLACE_OF_BIRTH, NULL, PLACE_OF_BIRTH, FND_API.G_MISS_CHAR, NULL, X_PLACE_OF_BIRTH ),
415 DATE_OF_DEATH = DECODE( X_DATE_OF_DEATH, NULL, DATE_OF_DEATH, FND_API.G_MISS_DATE, NULL, X_DATE_OF_DEATH ),
416 DECEASED_FLAG = DECODE( X_DECEASED_FLAG, NULL, DECEASED_FLAG, FND_API.G_MISS_CHAR, NULL, X_DECEASED_FLAG ),
417 GENDER = DECODE( X_GENDER, NULL, GENDER, FND_API.G_MISS_CHAR, NULL, X_GENDER ),
418 DECLARED_ETHNICITY = DECODE( X_DECLARED_ETHNICITY, NULL, DECLARED_ETHNICITY, FND_API.G_MISS_CHAR, NULL, X_DECLARED_ETHNICITY ),
419 MARITAL_STATUS = DECODE( X_MARITAL_STATUS, NULL, MARITAL_STATUS, FND_API.G_MISS_CHAR, NULL, X_MARITAL_STATUS ),
420 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 ),
421 PERSONAL_INCOME = DECODE( X_PERSONAL_INCOME, NULL, PERSONAL_INCOME, FND_API.G_MISS_NUM, NULL, X_PERSONAL_INCOME ),
422 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 ),
423 HOUSEHOLD_INCOME = DECODE( X_HOUSEHOLD_INCOME, NULL, HOUSEHOLD_INCOME, FND_API.G_MISS_NUM, NULL, X_HOUSEHOLD_INCOME ),
424 HOUSEHOLD_SIZE = DECODE( X_HOUSEHOLD_SIZE, NULL, HOUSEHOLD_SIZE, FND_API.G_MISS_NUM, NULL, X_HOUSEHOLD_SIZE ),
425 RENT_OWN_IND = DECODE( X_RENT_OWN_IND, NULL, RENT_OWN_IND, FND_API.G_MISS_CHAR, NULL, X_RENT_OWN_IND ),
426 LAST_KNOWN_GPS = DECODE( X_LAST_KNOWN_GPS, NULL, LAST_KNOWN_GPS, FND_API.G_MISS_CHAR, NULL, X_LAST_KNOWN_GPS ),
427 EFFECTIVE_START_DATE = DECODE( X_EFFECTIVE_START_DATE, NULL, EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_START_DATE ),
428 EFFECTIVE_END_DATE = DECODE( X_EFFECTIVE_END_DATE, NULL, EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_END_DATE ),
429 CONTENT_SOURCE_TYPE = DECODE( X_CONTENT_SOURCE_TYPE, NULL, CONTENT_SOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, X_CONTENT_SOURCE_TYPE ),
430 KNOWN_AS2 = DECODE( X_KNOWN_AS2, NULL, KNOWN_AS2, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS2 ),
431 KNOWN_AS3 = DECODE( X_KNOWN_AS3, NULL, KNOWN_AS3, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS3 ),
432 KNOWN_AS4 = DECODE( X_KNOWN_AS4, NULL, KNOWN_AS4, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS4 ),
433 KNOWN_AS5 = DECODE( X_KNOWN_AS5, NULL, KNOWN_AS5, FND_API.G_MISS_CHAR, NULL, X_KNOWN_AS5 ),
434 MIDDLE_NAME_PHONETIC = DECODE( X_MIDDLE_NAME_PHONETIC, NULL, MIDDLE_NAME_PHONETIC, FND_API.G_MISS_CHAR, NULL, X_MIDDLE_NAME_PHONETIC ),
435 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
436 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
437 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
438 ACTUAL_CONTENT_SOURCE = DECODE( X_ACTUAL_CONTENT_SOURCE, NULL, ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, NULL, X_ACTUAL_CONTENT_SOURCE ),
439 VERSION_NUMBER = DECODE( X_VERSION_NUMBER, NULL, VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_VERSION_NUMBER )
440 WHERE ROWID = X_RowId;
441
442 IF ( SQL%NOTFOUND ) THEN
443 RAISE NO_DATA_FOUND;
444 END IF;
445
446 END Update_Row;
447
448 PROCEDURE Lock_Row (
449 X_Rowid IN OUT NOCOPY VARCHAR2,
450 X_PERSON_PROFILE_ID IN NUMBER,
451 X_PARTY_ID IN NUMBER,
452 X_LAST_UPDATE_DATE IN DATE,
453 X_LAST_UPDATED_BY IN NUMBER,
454 X_CREATION_DATE IN DATE,
455 X_CREATED_BY IN NUMBER,
456 X_LAST_UPDATE_LOGIN IN NUMBER,
457 X_REQUEST_ID IN NUMBER,
458 X_PROGRAM_APPLICATION_ID IN NUMBER,
459 X_PROGRAM_ID IN NUMBER,
460 X_PROGRAM_UPDATE_DATE IN DATE,
461 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
462 X_ATTRIBUTE1 IN VARCHAR2,
463 X_ATTRIBUTE2 IN VARCHAR2,
464 X_ATTRIBUTE3 IN VARCHAR2,
465 X_ATTRIBUTE4 IN VARCHAR2,
466 X_ATTRIBUTE5 IN VARCHAR2,
467 X_ATTRIBUTE6 IN VARCHAR2,
468 X_ATTRIBUTE7 IN VARCHAR2,
469 X_ATTRIBUTE8 IN VARCHAR2,
470 X_ATTRIBUTE9 IN VARCHAR2,
471 X_ATTRIBUTE10 IN VARCHAR2,
472 X_ATTRIBUTE11 IN VARCHAR2,
473 X_ATTRIBUTE12 IN VARCHAR2,
474 X_ATTRIBUTE13 IN VARCHAR2,
475 X_ATTRIBUTE14 IN VARCHAR2,
476 X_ATTRIBUTE15 IN VARCHAR2,
477 X_ATTRIBUTE16 IN VARCHAR2,
478 X_ATTRIBUTE17 IN VARCHAR2,
479 X_ATTRIBUTE18 IN VARCHAR2,
480 X_ATTRIBUTE19 IN VARCHAR2,
481 X_ATTRIBUTE20 IN VARCHAR2,
482 X_INTERNAL_FLAG IN VARCHAR2,
483 X_PERSON_PRE_NAME_ADJUNCT IN VARCHAR2,
484 X_PERSON_FIRST_NAME IN VARCHAR2,
485 X_PERSON_MIDDLE_NAME IN VARCHAR2,
486 X_PERSON_LAST_NAME IN VARCHAR2,
487 X_PERSON_NAME_SUFFIX IN VARCHAR2,
488 X_PERSON_TITLE IN VARCHAR2,
489 X_PERSON_ACADEMIC_TITLE IN VARCHAR2,
490 X_PERSON_PREVIOUS_LAST_NAME IN VARCHAR2,
491 X_PERSON_INITIALS IN VARCHAR2,
492 X_KNOWN_AS IN VARCHAR2,
493 X_PERSON_NAME_PHONETIC IN VARCHAR2,
494 X_PERSON_FIRST_NAME_PHONETIC IN VARCHAR2,
495 X_PERSON_LAST_NAME_PHONETIC IN VARCHAR2,
496 X_TAX_REFERENCE IN VARCHAR2,
497 X_JGZZ_FISCAL_CODE IN VARCHAR2,
498 X_PERSON_IDEN_TYPE IN VARCHAR2,
499 X_PERSON_IDENTIFIER IN VARCHAR2,
500 X_DATE_OF_BIRTH IN DATE,
501 X_PLACE_OF_BIRTH IN VARCHAR2,
502 X_DATE_OF_DEATH IN DATE,
503 X_DECEASED_FLAG IN VARCHAR2,
504 X_GENDER IN VARCHAR2,
505 X_DECLARED_ETHNICITY IN VARCHAR2,
506 X_MARITAL_STATUS IN VARCHAR2,
507 X_MARITAL_STATUS_EFF_DATE IN DATE,
508 X_PERSONAL_INCOME IN NUMBER,
509 X_HEAD_OF_HOUSEHOLD_FLAG IN VARCHAR2,
510 X_HOUSEHOLD_INCOME IN NUMBER,
511 X_HOUSEHOLD_SIZE IN NUMBER,
512 X_RENT_OWN_IND IN VARCHAR2,
513 X_LAST_KNOWN_GPS IN VARCHAR2,
514 X_EFFECTIVE_START_DATE IN DATE,
515 X_EFFECTIVE_END_DATE IN DATE,
516 X_CONTENT_SOURCE_TYPE IN VARCHAR2,
517 X_KNOWN_AS2 IN VARCHAR2,
518 X_KNOWN_AS3 IN VARCHAR2,
519 X_KNOWN_AS4 IN VARCHAR2,
520 X_KNOWN_AS5 IN VARCHAR2,
521 X_MIDDLE_NAME_PHONETIC IN VARCHAR2,
522 X_OBJECT_VERSION_NUMBER IN NUMBER,
523 X_CREATED_BY_MODULE IN VARCHAR2,
524 X_APPLICATION_ID IN NUMBER,
525 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2 DEFAULT NULL
526 ) IS
527
528 CURSOR C IS
529 SELECT * FROM HZ_PERSON_PROFILES
530 WHERE ROWID = x_Rowid
531 FOR UPDATE NOWAIT;
532 Recinfo C%ROWTYPE;
533
534 BEGIN
535
536 OPEN C;
537 FETCH C INTO Recinfo;
538 IF ( C%NOTFOUND ) THEN
539 CLOSE C;
540 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
541 APP_EXCEPTION.RAISE_EXCEPTION;
542 END IF;
543 CLOSE C;
544
545 IF (
546 ( ( Recinfo.PERSON_PROFILE_ID = X_PERSON_PROFILE_ID )
547 OR ( ( Recinfo.PERSON_PROFILE_ID IS NULL )
548 AND ( X_PERSON_PROFILE_ID IS NULL ) ) )
549 AND ( ( Recinfo.PARTY_ID = X_PARTY_ID )
550 OR ( ( Recinfo.PARTY_ID IS NULL )
551 AND ( X_PARTY_ID IS NULL ) ) )
552 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
553 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
554 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
555 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
556 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
557 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
558 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
559 OR ( ( Recinfo.CREATION_DATE IS NULL )
560 AND ( X_CREATION_DATE IS NULL ) ) )
561 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
562 OR ( ( Recinfo.CREATED_BY IS NULL )
563 AND ( X_CREATED_BY IS NULL ) ) )
564 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
565 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
566 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
567 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
568 OR ( ( Recinfo.REQUEST_ID IS NULL )
569 AND ( X_REQUEST_ID IS NULL ) ) )
570 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
571 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
572 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
573 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
574 OR ( ( Recinfo.PROGRAM_ID IS NULL )
575 AND ( X_PROGRAM_ID IS NULL ) ) )
576 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
577 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
578 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
579 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
580 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
581 AND ( X_ATTRIBUTE_CATEGORY IS NULL ) ) )
582 AND ( ( Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1 )
583 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
584 AND ( X_ATTRIBUTE1 IS NULL ) ) )
585 AND ( ( Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2 )
586 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
587 AND ( X_ATTRIBUTE2 IS NULL ) ) )
588 AND ( ( Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3 )
589 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
590 AND ( X_ATTRIBUTE3 IS NULL ) ) )
591 AND ( ( Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4 )
592 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
593 AND ( X_ATTRIBUTE4 IS NULL ) ) )
594 AND ( ( Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5 )
595 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
596 AND ( X_ATTRIBUTE5 IS NULL ) ) )
597 AND ( ( Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6 )
598 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
599 AND ( X_ATTRIBUTE6 IS NULL ) ) )
600 AND ( ( Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7 )
601 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
602 AND ( X_ATTRIBUTE7 IS NULL ) ) )
603 AND ( ( Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8 )
604 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
605 AND ( X_ATTRIBUTE8 IS NULL ) ) )
606 AND ( ( Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9 )
607 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
608 AND ( X_ATTRIBUTE9 IS NULL ) ) )
609 AND ( ( Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10 )
610 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
611 AND ( X_ATTRIBUTE10 IS NULL ) ) )
612 AND ( ( Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11 )
613 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
614 AND ( X_ATTRIBUTE11 IS NULL ) ) )
615 AND ( ( Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12 )
616 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
617 AND ( X_ATTRIBUTE12 IS NULL ) ) )
618 AND ( ( Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13 )
619 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
620 AND ( X_ATTRIBUTE13 IS NULL ) ) )
621 AND ( ( Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14 )
622 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
623 AND ( X_ATTRIBUTE14 IS NULL ) ) )
624 AND ( ( Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15 )
625 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
626 AND ( X_ATTRIBUTE15 IS NULL ) ) )
627 AND ( ( Recinfo.ATTRIBUTE16 = X_ATTRIBUTE16 )
628 OR ( ( Recinfo.ATTRIBUTE16 IS NULL )
629 AND ( X_ATTRIBUTE16 IS NULL ) ) )
630 AND ( ( Recinfo.ATTRIBUTE17 = X_ATTRIBUTE17 )
631 OR ( ( Recinfo.ATTRIBUTE17 IS NULL )
632 AND ( X_ATTRIBUTE17 IS NULL ) ) )
633 AND ( ( Recinfo.ATTRIBUTE18 = X_ATTRIBUTE18 )
634 OR ( ( Recinfo.ATTRIBUTE18 IS NULL )
635 AND ( X_ATTRIBUTE18 IS NULL ) ) )
636 AND ( ( Recinfo.ATTRIBUTE19 = X_ATTRIBUTE19 )
637 OR ( ( Recinfo.ATTRIBUTE19 IS NULL )
638 AND ( X_ATTRIBUTE19 IS NULL ) ) )
639 AND ( ( Recinfo.ATTRIBUTE20 = X_ATTRIBUTE20 )
640 OR ( ( Recinfo.ATTRIBUTE20 IS NULL )
641 AND ( X_ATTRIBUTE20 IS NULL ) ) )
642 AND ( ( Recinfo.INTERNAL_FLAG = X_INTERNAL_FLAG )
643 OR ( ( Recinfo.INTERNAL_FLAG IS NULL )
644 AND ( X_INTERNAL_FLAG IS NULL ) ) )
645 AND ( ( Recinfo.PERSON_PRE_NAME_ADJUNCT = X_PERSON_PRE_NAME_ADJUNCT )
646 OR ( ( Recinfo.PERSON_PRE_NAME_ADJUNCT IS NULL )
647 AND ( X_PERSON_PRE_NAME_ADJUNCT IS NULL ) ) )
648 AND ( ( Recinfo.PERSON_FIRST_NAME = X_PERSON_FIRST_NAME )
649 OR ( ( Recinfo.PERSON_FIRST_NAME IS NULL )
650 AND ( X_PERSON_FIRST_NAME IS NULL ) ) )
651 AND ( ( Recinfo.PERSON_MIDDLE_NAME = X_PERSON_MIDDLE_NAME )
652 OR ( ( Recinfo.PERSON_MIDDLE_NAME IS NULL )
653 AND ( X_PERSON_MIDDLE_NAME IS NULL ) ) )
654 AND ( ( Recinfo.PERSON_LAST_NAME = X_PERSON_LAST_NAME )
655 OR ( ( Recinfo.PERSON_LAST_NAME IS NULL )
656 AND ( X_PERSON_LAST_NAME IS NULL ) ) )
657 AND ( ( Recinfo.PERSON_NAME_SUFFIX = X_PERSON_NAME_SUFFIX )
658 OR ( ( Recinfo.PERSON_NAME_SUFFIX IS NULL )
659 AND ( X_PERSON_NAME_SUFFIX IS NULL ) ) )
660 AND ( ( Recinfo.PERSON_TITLE = X_PERSON_TITLE )
661 OR ( ( Recinfo.PERSON_TITLE IS NULL )
662 AND ( X_PERSON_TITLE IS NULL ) ) )
663 AND ( ( Recinfo.PERSON_ACADEMIC_TITLE = X_PERSON_ACADEMIC_TITLE )
664 OR ( ( Recinfo.PERSON_ACADEMIC_TITLE IS NULL )
665 AND ( X_PERSON_ACADEMIC_TITLE IS NULL ) ) )
666 AND ( ( Recinfo.PERSON_PREVIOUS_LAST_NAME = X_PERSON_PREVIOUS_LAST_NAME )
667 OR ( ( Recinfo.PERSON_PREVIOUS_LAST_NAME IS NULL )
668 AND ( X_PERSON_PREVIOUS_LAST_NAME IS NULL ) ) )
669 AND ( ( Recinfo.PERSON_INITIALS = X_PERSON_INITIALS )
670 OR ( ( Recinfo.PERSON_INITIALS IS NULL )
671 AND ( X_PERSON_INITIALS IS NULL ) ) )
672 AND ( ( Recinfo.KNOWN_AS = X_KNOWN_AS )
673 OR ( ( Recinfo.KNOWN_AS IS NULL )
674 AND ( X_KNOWN_AS IS NULL ) ) )
675 AND ( ( Recinfo.PERSON_NAME_PHONETIC = X_PERSON_NAME_PHONETIC )
676 OR ( ( Recinfo.PERSON_NAME_PHONETIC IS NULL )
677 AND ( X_PERSON_NAME_PHONETIC IS NULL ) ) )
678 AND ( ( Recinfo.PERSON_FIRST_NAME_PHONETIC = X_PERSON_FIRST_NAME_PHONETIC )
679 OR ( ( Recinfo.PERSON_FIRST_NAME_PHONETIC IS NULL )
680 AND ( X_PERSON_FIRST_NAME_PHONETIC IS NULL ) ) )
681 AND ( ( Recinfo.PERSON_LAST_NAME_PHONETIC = X_PERSON_LAST_NAME_PHONETIC )
682 OR ( ( Recinfo.PERSON_LAST_NAME_PHONETIC IS NULL )
683 AND ( X_PERSON_LAST_NAME_PHONETIC IS NULL ) ) )
684 AND ( ( Recinfo.TAX_REFERENCE = X_TAX_REFERENCE )
685 OR ( ( Recinfo.TAX_REFERENCE IS NULL )
686 AND ( X_TAX_REFERENCE IS NULL ) ) )
687 AND ( ( Recinfo.JGZZ_FISCAL_CODE = X_JGZZ_FISCAL_CODE )
688 OR ( ( Recinfo.JGZZ_FISCAL_CODE IS NULL )
689 AND ( X_JGZZ_FISCAL_CODE IS NULL ) ) )
690 AND ( ( Recinfo.PERSON_IDEN_TYPE = X_PERSON_IDEN_TYPE )
691 OR ( ( Recinfo.PERSON_IDEN_TYPE IS NULL )
692 AND ( X_PERSON_IDEN_TYPE IS NULL ) ) )
693 AND ( ( Recinfo.PERSON_IDENTIFIER = X_PERSON_IDENTIFIER )
694 OR ( ( Recinfo.PERSON_IDENTIFIER IS NULL )
695 AND ( X_PERSON_IDENTIFIER IS NULL ) ) )
696 AND ( ( Recinfo.DATE_OF_BIRTH = X_DATE_OF_BIRTH )
697 OR ( ( Recinfo.DATE_OF_BIRTH IS NULL )
698 AND ( X_DATE_OF_BIRTH IS NULL ) ) )
699 AND ( ( Recinfo.PLACE_OF_BIRTH = X_PLACE_OF_BIRTH )
700 OR ( ( Recinfo.PLACE_OF_BIRTH IS NULL )
701 AND ( X_PLACE_OF_BIRTH IS NULL ) ) )
702 AND ( ( Recinfo.DATE_OF_DEATH = X_DATE_OF_DEATH )
703 OR ( ( Recinfo.DATE_OF_DEATH IS NULL )
704 AND ( X_DATE_OF_DEATH IS NULL ) ) )
705 AND ( ( Recinfo.DECEASED_FLAG = X_DECEASED_FLAG )
706 OR ( ( Recinfo.DECEASED_FLAG IS NULL )
707 AND ( X_DECEASED_FLAG IS NULL ) ) )
708 AND ( ( Recinfo.GENDER = X_GENDER )
709 OR ( ( Recinfo.GENDER IS NULL )
710 AND ( X_GENDER IS NULL ) ) )
711 AND ( ( Recinfo.DECLARED_ETHNICITY = X_DECLARED_ETHNICITY )
712 OR ( ( Recinfo.DECLARED_ETHNICITY IS NULL )
713 AND ( X_DECLARED_ETHNICITY IS NULL ) ) )
714 AND ( ( Recinfo.MARITAL_STATUS = X_MARITAL_STATUS )
715 OR ( ( Recinfo.MARITAL_STATUS IS NULL )
716 AND ( X_MARITAL_STATUS IS NULL ) ) )
717 AND ( ( Recinfo.MARITAL_STATUS_EFFECTIVE_DATE = X_MARITAL_STATUS_EFF_DATE )
718 OR ( ( Recinfo.MARITAL_STATUS_EFFECTIVE_DATE IS NULL )
719 AND ( X_MARITAL_STATUS_EFF_DATE IS NULL ) ) )
720 AND ( ( Recinfo.PERSONAL_INCOME = X_PERSONAL_INCOME )
721 OR ( ( Recinfo.PERSONAL_INCOME IS NULL )
722 AND ( X_PERSONAL_INCOME IS NULL ) ) )
723 AND ( ( Recinfo.HEAD_OF_HOUSEHOLD_FLAG = X_HEAD_OF_HOUSEHOLD_FLAG )
724 OR ( ( Recinfo.HEAD_OF_HOUSEHOLD_FLAG IS NULL )
725 AND ( X_HEAD_OF_HOUSEHOLD_FLAG IS NULL ) ) )
726 AND ( ( Recinfo.HOUSEHOLD_INCOME = X_HOUSEHOLD_INCOME )
727 OR ( ( Recinfo.HOUSEHOLD_INCOME IS NULL )
728 AND ( X_HOUSEHOLD_INCOME IS NULL ) ) )
729 AND ( ( Recinfo.HOUSEHOLD_SIZE = X_HOUSEHOLD_SIZE )
730 OR ( ( Recinfo.HOUSEHOLD_SIZE IS NULL )
731 AND ( X_HOUSEHOLD_SIZE IS NULL ) ) )
732 AND ( ( Recinfo.RENT_OWN_IND = X_RENT_OWN_IND )
733 OR ( ( Recinfo.RENT_OWN_IND IS NULL )
734 AND ( X_RENT_OWN_IND IS NULL ) ) )
735 AND ( ( Recinfo.LAST_KNOWN_GPS = X_LAST_KNOWN_GPS )
736 OR ( ( Recinfo.LAST_KNOWN_GPS IS NULL )
737 AND ( X_LAST_KNOWN_GPS IS NULL ) ) )
738 AND ( ( Recinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE )
739 OR ( ( Recinfo.EFFECTIVE_START_DATE IS NULL )
740 AND ( X_EFFECTIVE_START_DATE IS NULL ) ) )
741 AND ( ( Recinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE )
742 OR ( ( Recinfo.EFFECTIVE_END_DATE IS NULL )
743 AND ( X_EFFECTIVE_END_DATE IS NULL ) ) )
744 AND ( ( Recinfo.CONTENT_SOURCE_TYPE = X_CONTENT_SOURCE_TYPE )
745 OR ( ( Recinfo.CONTENT_SOURCE_TYPE IS NULL )
746 AND ( X_CONTENT_SOURCE_TYPE IS NULL ) ) )
747 AND ( ( Recinfo.KNOWN_AS2 = X_KNOWN_AS2 )
748 OR ( ( Recinfo.KNOWN_AS2 IS NULL )
749 AND ( X_KNOWN_AS2 IS NULL ) ) )
750 AND ( ( Recinfo.KNOWN_AS3 = X_KNOWN_AS3 )
751 OR ( ( Recinfo.KNOWN_AS3 IS NULL )
752 AND ( X_KNOWN_AS3 IS NULL ) ) )
753 AND ( ( Recinfo.KNOWN_AS4 = X_KNOWN_AS4 )
754 OR ( ( Recinfo.KNOWN_AS4 IS NULL )
755 AND ( X_KNOWN_AS4 IS NULL ) ) )
756 AND ( ( Recinfo.KNOWN_AS5 = X_KNOWN_AS5 )
757 OR ( ( Recinfo.KNOWN_AS5 IS NULL )
758 AND ( X_KNOWN_AS5 IS NULL ) ) )
759 AND ( ( Recinfo.MIDDLE_NAME_PHONETIC = X_MIDDLE_NAME_PHONETIC )
760 OR ( ( Recinfo.MIDDLE_NAME_PHONETIC IS NULL )
761 AND ( X_MIDDLE_NAME_PHONETIC IS NULL ) ) )
762 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
763 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
764 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
765 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
766 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
767 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
768 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
769 OR ( ( Recinfo.APPLICATION_ID IS NULL )
770 AND ( X_APPLICATION_ID IS NULL ) ) )
771 AND ( ( Recinfo.ACTUAL_CONTENT_SOURCE = X_ACTUAL_CONTENT_SOURCE )
772 OR ( ( Recinfo.ACTUAL_CONTENT_SOURCE IS NULL )
773 AND ( X_ACTUAL_CONTENT_SOURCE IS NULL ) ) )
774 ) THEN
775 RETURN;
776 ELSE
777 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
778 APP_EXCEPTION.RAISE_EXCEPTION;
779 END IF;
780
781 END Lock_Row;
782
783 PROCEDURE Select_Row (
784 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER,
785 X_PARTY_ID OUT NOCOPY NUMBER,
786 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
787 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
788 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
789 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
790 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
791 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
792 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
793 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
794 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
795 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
796 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
797 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
798 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
799 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
800 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
801 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
802 X_ATTRIBUTE16 OUT NOCOPY VARCHAR2,
803 X_ATTRIBUTE17 OUT NOCOPY VARCHAR2,
804 X_ATTRIBUTE18 OUT NOCOPY VARCHAR2,
805 X_ATTRIBUTE19 OUT NOCOPY VARCHAR2,
806 X_ATTRIBUTE20 OUT NOCOPY VARCHAR2,
807 X_INTERNAL_FLAG OUT NOCOPY VARCHAR2,
808 X_PERSON_PRE_NAME_ADJUNCT OUT NOCOPY VARCHAR2,
809 X_PERSON_FIRST_NAME OUT NOCOPY VARCHAR2,
810 X_PERSON_MIDDLE_NAME OUT NOCOPY VARCHAR2,
811 X_PERSON_LAST_NAME OUT NOCOPY VARCHAR2,
812 X_PERSON_NAME_SUFFIX OUT NOCOPY VARCHAR2,
813 X_PERSON_TITLE OUT NOCOPY VARCHAR2,
814 X_PERSON_ACADEMIC_TITLE OUT NOCOPY VARCHAR2,
815 X_PERSON_PREVIOUS_LAST_NAME OUT NOCOPY VARCHAR2,
816 X_PERSON_INITIALS OUT NOCOPY VARCHAR2,
817 X_KNOWN_AS OUT NOCOPY VARCHAR2,
818 X_PERSON_NAME_PHONETIC OUT NOCOPY VARCHAR2,
819 X_PERSON_FIRST_NAME_PHONETIC OUT NOCOPY VARCHAR2,
820 X_PERSON_LAST_NAME_PHONETIC OUT NOCOPY VARCHAR2,
821 X_TAX_REFERENCE OUT NOCOPY VARCHAR2,
822 X_JGZZ_FISCAL_CODE OUT NOCOPY VARCHAR2,
823 X_PERSON_IDEN_TYPE OUT NOCOPY VARCHAR2,
824 X_PERSON_IDENTIFIER OUT NOCOPY VARCHAR2,
825 X_DATE_OF_BIRTH OUT NOCOPY DATE,
826 X_PLACE_OF_BIRTH OUT NOCOPY VARCHAR2,
827 X_DATE_OF_DEATH OUT NOCOPY DATE,
828 X_DECEASED_FLAG OUT NOCOPY VARCHAR2,
829 X_GENDER OUT NOCOPY VARCHAR2,
830 X_DECLARED_ETHNICITY OUT NOCOPY VARCHAR2,
831 X_MARITAL_STATUS OUT NOCOPY VARCHAR2,
832 X_MARITAL_STATUS_EFF_DATE OUT NOCOPY DATE,
833 X_PERSONAL_INCOME OUT NOCOPY NUMBER,
834 X_HEAD_OF_HOUSEHOLD_FLAG OUT NOCOPY VARCHAR2,
835 X_HOUSEHOLD_INCOME OUT NOCOPY NUMBER,
836 X_HOUSEHOLD_SIZE OUT NOCOPY NUMBER,
837 X_RENT_OWN_IND OUT NOCOPY VARCHAR2,
838 X_LAST_KNOWN_GPS OUT NOCOPY VARCHAR2,
839 X_EFFECTIVE_START_DATE OUT NOCOPY DATE,
840 X_EFFECTIVE_END_DATE OUT NOCOPY DATE,
841 X_CONTENT_SOURCE_TYPE OUT NOCOPY VARCHAR2,
842 X_KNOWN_AS2 OUT NOCOPY VARCHAR2,
843 X_KNOWN_AS3 OUT NOCOPY VARCHAR2,
844 X_KNOWN_AS4 OUT NOCOPY VARCHAR2,
845 X_KNOWN_AS5 OUT NOCOPY VARCHAR2,
846 X_MIDDLE_NAME_PHONETIC OUT NOCOPY VARCHAR2,
847 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
848 X_APPLICATION_ID OUT NOCOPY NUMBER,
849 X_ACTUAL_CONTENT_SOURCE OUT NOCOPY VARCHAR2
850 ) IS
851
852 BEGIN
853
854 SELECT
855 NVL( PERSON_PROFILE_ID, FND_API.G_MISS_NUM ),
856 NVL( PARTY_ID, FND_API.G_MISS_NUM ),
857 NVL( ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR ),
858 NVL( ATTRIBUTE1, FND_API.G_MISS_CHAR ),
859 NVL( ATTRIBUTE2, FND_API.G_MISS_CHAR ),
860 NVL( ATTRIBUTE3, FND_API.G_MISS_CHAR ),
861 NVL( ATTRIBUTE4, FND_API.G_MISS_CHAR ),
862 NVL( ATTRIBUTE5, FND_API.G_MISS_CHAR ),
863 NVL( ATTRIBUTE6, FND_API.G_MISS_CHAR ),
864 NVL( ATTRIBUTE7, FND_API.G_MISS_CHAR ),
865 NVL( ATTRIBUTE8, FND_API.G_MISS_CHAR ),
866 NVL( ATTRIBUTE9, FND_API.G_MISS_CHAR ),
867 NVL( ATTRIBUTE10, FND_API.G_MISS_CHAR ),
868 NVL( ATTRIBUTE11, FND_API.G_MISS_CHAR ),
869 NVL( ATTRIBUTE12, FND_API.G_MISS_CHAR ),
870 NVL( ATTRIBUTE13, FND_API.G_MISS_CHAR ),
871 NVL( ATTRIBUTE14, FND_API.G_MISS_CHAR ),
872 NVL( ATTRIBUTE15, FND_API.G_MISS_CHAR ),
873 NVL( ATTRIBUTE16, FND_API.G_MISS_CHAR ),
874 NVL( ATTRIBUTE17, FND_API.G_MISS_CHAR ),
875 NVL( ATTRIBUTE18, FND_API.G_MISS_CHAR ),
876 NVL( ATTRIBUTE19, FND_API.G_MISS_CHAR ),
877 NVL( ATTRIBUTE20, FND_API.G_MISS_CHAR ),
878 NVL( INTERNAL_FLAG, FND_API.G_MISS_CHAR ),
879 NVL( PERSON_PRE_NAME_ADJUNCT, FND_API.G_MISS_CHAR ),
880 NVL( PERSON_FIRST_NAME, FND_API.G_MISS_CHAR ),
881 NVL( PERSON_MIDDLE_NAME, FND_API.G_MISS_CHAR ),
882 NVL( PERSON_LAST_NAME, FND_API.G_MISS_CHAR ),
883 NVL( PERSON_NAME_SUFFIX, FND_API.G_MISS_CHAR ),
884 NVL( PERSON_TITLE, FND_API.G_MISS_CHAR ),
885 NVL( PERSON_ACADEMIC_TITLE, FND_API.G_MISS_CHAR ),
886 NVL( PERSON_PREVIOUS_LAST_NAME, FND_API.G_MISS_CHAR ),
887 NVL( PERSON_INITIALS, FND_API.G_MISS_CHAR ),
888 NVL( KNOWN_AS, FND_API.G_MISS_CHAR ),
889 NVL( PERSON_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
890 NVL( PERSON_FIRST_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
891 NVL( PERSON_LAST_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
892 NVL( TAX_REFERENCE, FND_API.G_MISS_CHAR ),
893 NVL( JGZZ_FISCAL_CODE, FND_API.G_MISS_CHAR ),
894 NVL( PERSON_IDEN_TYPE, FND_API.G_MISS_CHAR ),
895 NVL( PERSON_IDENTIFIER, FND_API.G_MISS_CHAR ),
896 NVL( DATE_OF_BIRTH, FND_API.G_MISS_DATE ),
897 NVL( PLACE_OF_BIRTH, FND_API.G_MISS_CHAR ),
898 NVL( DATE_OF_DEATH, FND_API.G_MISS_DATE ),
899 NVL( DECEASED_FLAG, FND_API.G_MISS_CHAR ),
900 NVL( GENDER, FND_API.G_MISS_CHAR ),
901 NVL( DECLARED_ETHNICITY, FND_API.G_MISS_CHAR ),
902 NVL( MARITAL_STATUS, FND_API.G_MISS_CHAR ),
903 NVL( MARITAL_STATUS_EFFECTIVE_DATE, FND_API.G_MISS_DATE ),
904 NVL( PERSONAL_INCOME, FND_API.G_MISS_NUM ),
905 NVL( HEAD_OF_HOUSEHOLD_FLAG, FND_API.G_MISS_CHAR ),
906 NVL( HOUSEHOLD_INCOME, FND_API.G_MISS_NUM ),
907 NVL( HOUSEHOLD_SIZE, FND_API.G_MISS_NUM ),
908 NVL( RENT_OWN_IND, FND_API.G_MISS_CHAR ),
909 NVL( LAST_KNOWN_GPS, FND_API.G_MISS_CHAR ),
910 NVL( EFFECTIVE_START_DATE, FND_API.G_MISS_DATE ),
911 NVL( EFFECTIVE_END_DATE, FND_API.G_MISS_DATE ),
912 NVL( CONTENT_SOURCE_TYPE, FND_API.G_MISS_CHAR ),
913 NVL( KNOWN_AS2, FND_API.G_MISS_CHAR ),
914 NVL( KNOWN_AS3, FND_API.G_MISS_CHAR ),
915 NVL( KNOWN_AS4, FND_API.G_MISS_CHAR ),
916 NVL( KNOWN_AS5, FND_API.G_MISS_CHAR ),
917 NVL( MIDDLE_NAME_PHONETIC, FND_API.G_MISS_CHAR ),
918 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
919 NVL( APPLICATION_ID, FND_API.G_MISS_NUM ),
920 NVL( ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR )
921 INTO
922 X_PERSON_PROFILE_ID,
923 X_PARTY_ID,
924 X_ATTRIBUTE_CATEGORY,
925 X_ATTRIBUTE1,
926 X_ATTRIBUTE2,
927 X_ATTRIBUTE3,
928 X_ATTRIBUTE4,
929 X_ATTRIBUTE5,
930 X_ATTRIBUTE6,
931 X_ATTRIBUTE7,
932 X_ATTRIBUTE8,
933 X_ATTRIBUTE9,
934 X_ATTRIBUTE10,
935 X_ATTRIBUTE11,
936 X_ATTRIBUTE12,
937 X_ATTRIBUTE13,
938 X_ATTRIBUTE14,
939 X_ATTRIBUTE15,
940 X_ATTRIBUTE16,
941 X_ATTRIBUTE17,
942 X_ATTRIBUTE18,
943 X_ATTRIBUTE19,
944 X_ATTRIBUTE20,
945 X_INTERNAL_FLAG,
946 X_PERSON_PRE_NAME_ADJUNCT,
947 X_PERSON_FIRST_NAME,
948 X_PERSON_MIDDLE_NAME,
949 X_PERSON_LAST_NAME,
950 X_PERSON_NAME_SUFFIX,
951 X_PERSON_TITLE,
952 X_PERSON_ACADEMIC_TITLE,
953 X_PERSON_PREVIOUS_LAST_NAME,
954 X_PERSON_INITIALS,
955 X_KNOWN_AS,
956 X_PERSON_NAME_PHONETIC,
957 X_PERSON_FIRST_NAME_PHONETIC,
958 X_PERSON_LAST_NAME_PHONETIC,
959 X_TAX_REFERENCE,
960 X_JGZZ_FISCAL_CODE,
961 X_PERSON_IDEN_TYPE,
962 X_PERSON_IDENTIFIER,
963 X_DATE_OF_BIRTH,
964 X_PLACE_OF_BIRTH,
965 X_DATE_OF_DEATH,
966 X_DECEASED_FLAG,
967 X_GENDER,
968 X_DECLARED_ETHNICITY,
969 X_MARITAL_STATUS,
970 X_MARITAL_STATUS_EFF_DATE,
971 X_PERSONAL_INCOME,
972 X_HEAD_OF_HOUSEHOLD_FLAG,
973 X_HOUSEHOLD_INCOME,
974 X_HOUSEHOLD_SIZE,
975 X_RENT_OWN_IND,
976 X_LAST_KNOWN_GPS,
977 X_EFFECTIVE_START_DATE,
978 X_EFFECTIVE_END_DATE,
979 X_CONTENT_SOURCE_TYPE,
980 X_KNOWN_AS2,
981 X_KNOWN_AS3,
982 X_KNOWN_AS4,
983 X_KNOWN_AS5,
984 X_MIDDLE_NAME_PHONETIC,
985 X_CREATED_BY_MODULE,
986 X_APPLICATION_ID,
987 X_ACTUAL_CONTENT_SOURCE
988 FROM HZ_PERSON_PROFILES
989 WHERE PERSON_PROFILE_ID = X_PERSON_PROFILE_ID;
990
991 EXCEPTION
992 WHEN NO_DATA_FOUND THEN
993 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
994 FND_MESSAGE.SET_TOKEN( 'RECORD', 'person_profile_rec');
995 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_PERSON_PROFILE_ID ) );
996 FND_MSG_PUB.ADD;
997 RAISE FND_API.G_EXC_ERROR;
998
999 END Select_Row;
1000
1001 PROCEDURE Delete_Row (
1002 X_PERSON_PROFILE_ID IN NUMBER
1003 ) IS
1004
1005 BEGIN
1006
1007 DELETE FROM HZ_PERSON_PROFILES
1008 WHERE PERSON_PROFILE_ID = X_PERSON_PROFILE_ID;
1009
1010 IF ( SQL%NOTFOUND ) THEN
1011 RAISE NO_DATA_FOUND;
1012 END IF;
1013
1014 END Delete_Row;
1015
1016 END HZ_PERSON_PROFILES_PKG;