[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_STAT_PKG
Source
1 PACKAGE BODY Igs_Pe_Stat_Pkg AS
2 /* $Header: IGSNI48B.pls 120.2 2006/02/17 06:53:22 gmaheswa ship $ */
3 ------------------------------------------------------------------
4 -- Change History
5
6 --
7 -- Bug ID : 2000408
8 -- who when what
9 --sbaliga 6-May-2002 Modified Insert_row and Update_row as part of #2338473
10 -- CDCRUZ Sep 24,2002 New Col's added for
11 -- Person DLD
12 -- Columns Added - MATR_CAL_TYPE/MATR_SEQUENCE_NUMBER/INIT_CAL_TYPE/INIT_SEQUENCE_NUMBER
13 -- RECENT_CAL_TYPE/RECENT_SEQUENCE_NUMBER/CATALOG_CAL_TYPE/CATALOG_SEQUENCE_NUMBER
14 --bayadav 31-Jan-2002 Added one DFF in IGS_PE_STAT_DETAILS and DFF columns in IGS_AD_STAT_INT table.
15 -- Due to which 1)Using the columns attribute1 to attribute20 parameters of this TBH
16 -- to transfer the data from IGS_AD_STAT_INT DFF columns to IGS_PE_STAT_DETAILS
17 -- DFF columns
18 -- 2)Changing the earlier use of these parameters attribute1 to attribute20 for HZ table to NULL .Bug number:2203778
19 --bayadav 6-Feb-2002 Removed seven columns from igs_pe_stat_details_pkg call as a aprt of bug:2203778
20 -- 1 Person_profile_id 2 acad_dismissal 3 non_acad_dismissal 4 resid_stat_id 5 criminal_convict 6 country_cd3 7 state_of_residence
21 -- and added Person_id
22 ------------------------------------------------------------------
23
24 l_rowid VARCHAR2(25);
25 old_references igs_pe_stat_v%ROWTYPE;
26 new_references igs_pe_stat_v%ROWTYPE;
27 Y_RETURN_STATUS VARCHAR2(1);
28 Z_RETURN_STATUS VARCHAR2(1);
29 Y_MSG_DATA VARCHAR2(2000);
30 Y_PROFILE_ID igs_pe_stat_v.person_profile_id%TYPE;
31 Z_PROFILE_ID igs_pe_stat_v.person_profile_id%TYPE;
32 XXX_ROWID VARCHAR2(25);
33 Y_MSG_COUNT NUMBER;
34 Z_MSG_COUNT NUMBER;
35 Z_MSG_DATA VARCHAR2(2000);
36 PROCEDURE Set_Column_Values (
37 X_action IN VARCHAR2 ,--DEFAULT NULL,
38 X_ROWID IN OUT NOCOPY VARCHAR2 ,
39 X_PERSON_ID IN NUMBER ,--DEFAULT NULL,
40 X_ETHNIC_ORIGIN_ID IN VARCHAR2,-- DEFAULT NULL,
41 X_MARITAL_STATUS IN VARCHAR2 ,--DEFAULT NULL,
42 X_MARITAL_STAT_EFFECT_DT IN DATE ,-- DEFAULT NULL,
43 X_ANN_FAMILY_INCOME IN NUMBER ,--DEFAULT NULL,
44 X_NUMBER_IN_FAMILY IN NUMBER ,--DEFAULT NULL,
45 X_CONTENT_SOURCE_TYPE IN VARCHAR2 ,--DEFAULT NULL,
46 X_INTERNAL_FLAG IN VARCHAR2 ,--DEFAULT NULL,
47 X_PERSON_NUMBER IN VARCHAR2 ,-- DEFAULT NULL,
48 X_EFFECTIVE_START_DATE IN DATE ,--DEFAULT NULL,
49 X_effective_end_date IN DATE ,--DEFAULT NULL,
50 X_ethnic_origin IN VARCHAR2,-- DEFAULT NULL,
51 X_religion IN VARCHAR2,-- DEFAULT NULL,
52 X_next_to_kin IN VARCHAR2,-- DEFAULT NULL,
53 X_next_to_kin_meaning IN VARCHAR2 ,-- DEFAULT NULL,
54 X_place_of_birth IN VARCHAR2 ,--DEFAULT NULL,
55 X_socio_eco_status IN VARCHAR2,-- DEFAULT NULL,
56 X_socio_eco_status_desc IN VARCHAR2,-- DEFAULT NULL,
57 X_further_education IN VARCHAR2,-- DEFAULT NULL,
58 X_further_education_desc IN VARCHAR2,-- DEFAULT NULL,
59 X_in_state_tuition IN VARCHAR2,-- DEFAULT NULL,
60 X_tuition_st_Date IN DATE ,--DEFAULT NULL,
61 X_tuition_end_date IN DATE ,-- DEFAULT NULL,
62 X_person_initials IN VARCHAR2 ,--DEFAULT NULL,
63 X_primary_contact_id IN NUMBER ,--DEFAULT NULL,
64 X_personal_income IN NUMBER ,--DEFAULT NULL,
65 X_head_of_household_flag IN VARCHAR2 ,--DEFAULT NULL,
66 X_content_source_number IN VARCHAR2 ,--DEFAULT NULL,
67 x_hz_parties_ovn IN NUMBER,
68 X_ATTRIBUTE_CATEGORY IN VARCHAR2,-- DEFAULT NULL,
69 X_ATTRIBUTE1 IN VARCHAR2 ,-- DEFAULT NULL,
70 X_ATTRIBUTE2 IN VARCHAR2 ,--DEFAULT NULL,
71 X_ATTRIBUTE3 IN VARCHAR2 ,--DEFAULT NULL,
72 X_ATTRIBUTE4 IN VARCHAR2 ,--DEFAULT NULL,
73 X_ATTRIBUTE5 IN VARCHAR2 ,--DEFAULT NULL,
74 X_ATTRIBUTE6 IN VARCHAR2 ,--DEFAULT NULL,
75 X_ATTRIBUTE7 IN VARCHAR2 ,--DEFAULT NULL,
76 X_ATTRIBUTE8 IN VARCHAR2 ,--DEFAULT NULL,
77 X_ATTRIBUTE9 IN VARCHAR2 ,--DEFAULT NULL,
78 X_ATTRIBUTE10 IN VARCHAR2 ,--DEFAULT NULL,
79 X_ATTRIBUTE11 IN VARCHAR2 ,--DEFAULT NULL,
80 X_ATTRIBUTE12 IN VARCHAR2 ,--DEFAULT NULL,
81 X_ATTRIBUTE13 IN VARCHAR2 ,--DEFAULT NULL,
82 X_ATTRIBUTE14 IN VARCHAR2 ,--DEFAULT NULL,
83 X_ATTRIBUTE15 IN VARCHAR2 ,--DEFAULT NULL,
84 X_ATTRIBUTE16 IN VARCHAR2 ,--DEFAULT NULL,
85 X_ATTRIBUTE17 IN VARCHAR2 ,--DEFAULT NULL,
86 X_ATTRIBUTE18 IN VARCHAR2 ,--DEFAULT NULL,
87 X_ATTRIBUTE19 IN VARCHAR2 ,--DEFAULT NULL,
88 X_ATTRIBUTE20 IN VARCHAR2 ,--DEFAULT NULL,
89 X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 ,--DEFAULT NULL,
90 X_GLOBAL_ATTRIBUTE1 IN VARCHAR2 ,--DEFAULT NULL,
91 X_GLOBAL_ATTRIBUTE2 IN VARCHAR2 ,--DEFAULT NULL,
92 X_GLOBAL_ATTRIBUTE3 IN VARCHAR2 ,--DEFAULT NULL,
93 X_GLOBAL_ATTRIBUTE4 IN VARCHAR2 ,--DEFAULT NULL,
94 X_GLOBAL_ATTRIBUTE5 IN VARCHAR2 ,--DEFAULT NULL,
95 X_GLOBAL_ATTRIBUTE6 IN VARCHAR2 ,--DEFAULT NULL,
96 X_GLOBAL_ATTRIBUTE7 IN VARCHAR2 ,--DEFAULT NULL,
97 X_GLOBAL_ATTRIBUTE8 IN VARCHAR2 ,--DEFAULT NULL,
98 X_GLOBAL_ATTRIBUTE9 IN VARCHAR2 ,--DEFAULT NULL,
99 X_GLOBAL_ATTRIBUTE10 IN VARCHAR2 ,--DEFAULT NULL,
100 X_GLOBAL_ATTRIBUTE11 IN VARCHAR2 ,--DEFAULT NULL,
101 X_GLOBAL_ATTRIBUTE12 IN VARCHAR2 ,--DEFAULT NULL,
102 X_GLOBAL_ATTRIBUTE13 IN VARCHAR2 ,--DEFAULT NULL,
103 X_GLOBAL_ATTRIBUTE14 IN VARCHAR2 ,--DEFAULT NULL,
104 X_GLOBAL_ATTRIBUTE15 IN VARCHAR2 ,--DEFAULT NULL,
105 X_GLOBAL_ATTRIBUTE16 IN VARCHAR2 ,--DEFAULT NULL,
106 X_GLOBAL_ATTRIBUTE17 IN VARCHAR2 ,--DEFAULT NULL,
107 X_GLOBAL_ATTRIBUTE18 IN VARCHAR2 ,--DEFAULT NULL,
108 X_GLOBAL_ATTRIBUTE19 IN VARCHAR2 ,--DEFAULT NULL,
109 X_GLOBAL_ATTRIBUTE20 IN VARCHAR2 ,--DEFAULT NULL,
110 X_PARTY_LAST_UPDATE_DATE IN OUT NOCOPY DATE ,
111 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER ,
112 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
113 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
114 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
115 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
116 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
117 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
118 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
119 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
120 Z_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
121 Z_MSG_COUNT OUT NOCOPY NUMBER ,
122 Z_MSG_DATA OUT NOCOPY VARCHAR2,
123 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2 --DEFAULT NULL
124 ) AS
125 /*************************************************************
126 Created By : svisweas
127 Date Created By :23-MAY-2000
128 Purpose : TO SET COLUMN VALUES
129 Know limitations, enhancements or remarks
130 Change History
131 Who When What
132 svisweas 26-may-2000 get_fk_igs_lookups_view procedure from tbh.
133 (reverse chronological order - newest change first)
134 ***************************************************************/
135 CURSOR cur_old_ref_values IS
136 SELECT *
137 FROM IGS_PE_STAT_V
138 WHERE ROW_ID = X_rowid;
139 BEGIN
140 l_rowid := X_rowid;
141 -- Code for setting the Old and New Reference Values.
142 -- Populate Old Values.
143 OPEN cur_old_ref_values;
144 FETCH cur_old_ref_values INTO old_references;
145 IF (cur_old_ref_values%NOTFOUND) AND (X_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
146 CLOSE cur_old_ref_values;
147 Fnd_Message.Set_Name('FND', 'FORM_RECORD_DELETED');
148 IGS_GE_MSG_STACK.ADD;
149 APP_Exception.Raise_Exception;
150 RETURN;
151 END IF;
152 CLOSE cur_old_ref_values;
153 -- Populate New Values.
154 new_references.PERSON_ID := X_PERSON_ID;
155 new_references.ETHNIC_ORIGIN_ID := X_ETHNIC_ORIGIN_ID;
156 new_references.MARITAL_STATUS := X_MARITAL_STATUS;
157 new_references.MARITAL_STATUS_EFFECTIVE_DATE := X_MARITAL_STAT_EFFECT_DT;
158 new_references.ANN_FAMILY_INCOME :=X_ANN_FAMILY_INCOME;
159 new_references.NUMBER_IN_FAMILY :=X_NUMBER_IN_FAMILY;
160 new_references.CONTENT_SOURCE_TYPE :=X_CONTENT_SOURCE_TYPE;
161 new_references.INTERNAL_FLAG :=X_INTERNAL_FLAG;
162 new_references.PERSON_NUMBER :=X_PERSON_NUMBER;
163 new_references.EFFECTIVE_START_DATE :=X_EFFECTIVE_START_DATE;
164 new_references.effective_end_date :=X_EFFECTIVE_END_DATE;
165 new_references.ethnic_origin :=X_ETHNIC_ORIGIN;
166 new_references.religion :=X_RELIGION;
167 new_references.next_to_kin :=X_NEXT_TO_KIN;
168 new_references.next_to_kin_meaning := X_NEXT_TO_KIN_MEANING;
169 new_references.place_of_birth :=X_PLACE_OF_BIRTH;
170 new_references.socio_eco_status :=X_SOCIO_ECO_STATUS;
171 new_references.socio_eco_status_desc :=X_SOCIO_ECO_STATUS_DESC;
172 new_references.further_education :=X_FURTHER_EDUCATION;
173 new_references.further_education_desc :=X_FURTHER_EDUCATION_DESC;
174 new_references.in_state_tuition :=X_IN_STATE_TUITION;
175 new_references.tuition_st_Date :=X_TUITION_ST_DATE;
176 new_references.tuition_end_date :=X_TUITION_END_DATE;
177 new_references.person_initials :=X_PERSON_INITIALS;
178 new_references.primary_contact_id :=X_PRIMARY_CONTACT_ID;
179 new_references.personal_income :=X_PERSONAL_INCOME;
180 new_references.head_of_household_flag :=X_HEAD_OF_HOUSEHOLD_FLAG;
181 new_references.content_source_number :=X_CONTENT_SOURCE_NUMBER;
182 new_references.object_version_number :=X_HZ_PARTIES_OVN;
183 new_references.ATTRIBUTE_CATEGORY :=X_ATTRIBUTE_CATEGORY;
184 new_references.ATTRIBUTE1 :=X_ATTRIBUTE1;
185 new_references.ATTRIBUTE2 :=X_ATTRIBUTE2;
186 new_references.ATTRIBUTE3 :=X_ATTRIBUTE3;
187 new_references.ATTRIBUTE4 :=X_ATTRIBUTE4;
188 new_references.ATTRIBUTE5 :=X_ATTRIBUTE5;
189 new_references.ATTRIBUTE6 :=X_ATTRIBUTE6;
190 new_references.ATTRIBUTE7 :=X_ATTRIBUTE7;
191 new_references.ATTRIBUTE8 :=X_ATTRIBUTE8;
192 new_references.ATTRIBUTE9 :=X_ATTRIBUTE9;
193 new_references.ATTRIBUTE10 :=X_ATTRIBUTE10;
194 new_references.ATTRIBUTE11 :=X_ATTRIBUTE11;
195 new_references.ATTRIBUTE12 :=X_ATTRIBUTE12;
196 new_references.ATTRIBUTE13 :=X_ATTRIBUTE13;
197 new_references.ATTRIBUTE14 :=X_ATTRIBUTE14;
198 new_references.ATTRIBUTE15 :=X_ATTRIBUTE15;
199 new_references.ATTRIBUTE16 :=X_ATTRIBUTE16;
200 new_references.ATTRIBUTE17 :=X_ATTRIBUTE17;
201 new_references.ATTRIBUTE18 :=X_ATTRIBUTE18;
202 new_references.ATTRIBUTE19 :=X_ATTRIBUTE19;
203 new_references.ATTRIBUTE20 :=X_ATTRIBUTE20;
204 new_references.GLOBAL_ATTRIBUTE_CATEGORY :=X_GLOBAL_ATTRIBUTE_CATEGORY;
205 new_references.GLOBAL_ATTRIBUTE1 :=X_GLOBAL_ATTRIBUTE1;
206 new_references.GLOBAL_ATTRIBUTE2 :=X_GLOBAL_ATTRIBUTE2;
207 new_references.GLOBAL_ATTRIBUTE3 :=X_GLOBAL_ATTRIBUTE3;
208 new_references.GLOBAL_ATTRIBUTE4 :=X_GLOBAL_ATTRIBUTE4;
209 new_references.GLOBAL_ATTRIBUTE5 :=X_GLOBAL_ATTRIBUTE5;
210 new_references.GLOBAL_ATTRIBUTE6 :=X_GLOBAL_ATTRIBUTE6;
211 new_references.GLOBAL_ATTRIBUTE7 :=X_GLOBAL_ATTRIBUTE7;
212 new_references.GLOBAL_ATTRIBUTE8 :=X_GLOBAL_ATTRIBUTE8;
213 new_references.GLOBAL_ATTRIBUTE9 :=X_GLOBAL_ATTRIBUTE9;
214 new_references.GLOBAL_ATTRIBUTE10 :=X_GLOBAL_ATTRIBUTE10;
215 new_references.GLOBAL_ATTRIBUTE11 :=X_GLOBAL_ATTRIBUTE11;
216 new_references.GLOBAL_ATTRIBUTE12 :=X_GLOBAL_ATTRIBUTE12;
217 new_references.GLOBAL_ATTRIBUTE13 :=X_GLOBAL_ATTRIBUTE13;
218 new_references. GLOBAL_ATTRIBUTE14 :=X_GLOBAL_ATTRIBUTE14;
219 new_references.GLOBAL_ATTRIBUTE15 :=X_GLOBAL_ATTRIBUTE15;
220 new_references.GLOBAL_ATTRIBUTE16 :=X_GLOBAL_ATTRIBUTE16;
221 new_references.GLOBAL_ATTRIBUTE17 :=X_GLOBAL_ATTRIBUTE17;
222 new_references.GLOBAL_ATTRIBUTE18 :=X_GLOBAL_ATTRIBUTE18;
223 new_references.GLOBAL_ATTRIBUTE19 :=X_GLOBAL_ATTRIBUTE19;
224 new_references.GLOBAL_ATTRIBUTE20 :=X_GLOBAL_ATTRIBUTE20;
225 new_references.matr_cal_type := X_MATR_CAL_TYPE ;
226 new_references.matr_sequence_number := X_MATR_SEQUENCE_NUMBER ;
227 new_references.init_cal_type := X_INIT_CAL_TYPE ;
228 new_references.init_sequence_number := X_INIT_SEQUENCE_NUMBER ;
229 new_references.recent_cal_type := X_RECENT_CAL_TYPE ;
230 new_references.recent_sequence_number := X_RECENT_SEQUENCE_NUMBER ;
231 new_references.catalog_cal_type := X_CATALOG_CAL_TYPE ;
232 new_references.catalog_sequence_number := X_CATALOG_SEQUENCE_NUMBER ;
233 new_references.birth_cntry_resn_code := X_BIRTH_CNTRY_RESN_CODE;
234
235 -- new_references.PARTY_LAST_UPDATE_DATE :=X_PARTY_LAST_UPDATE_DATE;
236 -- new_references.PROFILE_ID :=Z_PROFILE_ID;
237 /* new_references.RETURN_STATUS
238 new_references.MSG_COUNT
239 new_references.MSG_DATA */
240 -- END OF POPULATION
241 END Set_Column_Values;
242
243 PROCEDURE Before_DML (
244 X_action IN VARCHAR2,-- DEFAULT NULL,
245 X_ROWID IN OUT NOCOPY VARCHAR2 ,
246 X_PERSON_ID IN NUMBER ,--DEFAULT NULL,
247 X_ETHNIC_ORIGIN_ID IN VARCHAR2 ,--DEFAULT NULL,
248 X_MARITAL_STATUS IN VARCHAR2 ,--DEFAULT NULL,
249 X_MARITAL_STAT_EFFECT_DT IN DATE ,-- DEFAULT NULL,
250 X_ANN_FAMILY_INCOME IN NUMBER,-- DEFAULT NULL,
251 X_NUMBER_IN_FAMILY IN NUMBER,-- DEFAULT NULL,
252 X_CONTENT_SOURCE_TYPE IN VARCHAR2 ,--DEFAULT NULL,
253 X_INTERNAL_FLAG IN VARCHAR2,-- DEFAULT NULL,
254 X_PERSON_NUMBER IN VARCHAR2,-- DEFAULT NULL,
255 X_EFFECTIVE_START_DATE IN DATE,-- DEFAULT NULL,
256 X_effective_end_date IN DATE ,--DEFAULT NULL,
257 X_ethnic_origin IN VARCHAR2,-- DEFAULT NULL,
258 X_religion IN VARCHAR2,-- DEFAULT NULL,
259 X_next_to_kin IN VARCHAR2,-- DEFAULT NULL,
260 X_next_to_kin_meaning IN VARCHAR2,-- DEFAULT NULL,
261 X_place_of_birth IN VARCHAR2,-- DEFAULT NULL,
262 X_socio_eco_status IN VARCHAR2,-- DEFAULT NULL,
263 X_socio_eco_status_desc IN VARCHAR2,-- DEFAULT NULL,
264 X_further_education IN VARCHAR2,-- DEFAULT NULL,
265 X_further_education_desc IN VARCHAR2,-- DEFAULT NULL,
266 X_in_state_tuition IN VARCHAR2,-- DEFAULT NULL,
267 X_tuition_st_Date IN DATE,-- DEFAULT NULL,
268 X_tuition_end_date IN DATE,-- DEFAULT NULL,
269 X_person_initials IN VARCHAR2,-- DEFAULT NULL,
270 X_primary_contact_id IN NUMBER,-- DEFAULT NULL,
271 X_personal_income IN NUMBER,-- DEFAULT NULL,
272 X_head_of_household_flag IN VARCHAR2,-- DEFAULT NULL,
273 X_content_source_number IN VARCHAR2,-- DEFAULT NULL,
274 x_hz_parties_ovn IN NUMBER,
275 X_ATTRIBUTE_CATEGORY IN VARCHAR2,-- DEFAULT NULL,
276 X_ATTRIBUTE1 IN VARCHAR2,-- DEFAULT NULL,
277 X_ATTRIBUTE2 IN VARCHAR2,-- DEFAULT NULL,
278 X_ATTRIBUTE3 IN VARCHAR2,-- DEFAULT NULL,
279 X_ATTRIBUTE4 IN VARCHAR2,-- DEFAULT NULL,
280 X_ATTRIBUTE5 IN VARCHAR2,-- DEFAULT NULL,
281 X_ATTRIBUTE6 IN VARCHAR2,-- DEFAULT NULL,
282 X_ATTRIBUTE7 IN VARCHAR2,-- DEFAULT NULL,
283 X_ATTRIBUTE8 IN VARCHAR2,-- DEFAULT NULL,
284 X_ATTRIBUTE9 IN VARCHAR2,-- DEFAULT NULL,
285 X_ATTRIBUTE10 IN VARCHAR2,-- DEFAULT NULL,
286 X_ATTRIBUTE11 IN VARCHAR2,-- DEFAULT NULL,
287 X_ATTRIBUTE12 IN VARCHAR2,-- DEFAULT NULL,
288 X_ATTRIBUTE13 IN VARCHAR2,-- DEFAULT NULL,
289 X_ATTRIBUTE14 IN VARCHAR2,-- DEFAULT NULL,
290 X_ATTRIBUTE15 IN VARCHAR2,-- DEFAULT NULL,
291 X_ATTRIBUTE16 IN VARCHAR2,-- DEFAULT NULL,
292 X_ATTRIBUTE17 IN VARCHAR2,-- DEFAULT NULL,
293 X_ATTRIBUTE18 IN VARCHAR2,-- DEFAULT NULL,
294 X_ATTRIBUTE19 IN VARCHAR2,-- DEFAULT NULL,
295 X_ATTRIBUTE20 IN VARCHAR2,-- DEFAULT NULL,
296 X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2,-- DEFAULT NULL,
297 X_GLOBAL_ATTRIBUTE1 IN VARCHAR2,-- DEFAULT NULL,
298 X_GLOBAL_ATTRIBUTE2 IN VARCHAR2,-- DEFAULT NULL,
299 X_GLOBAL_ATTRIBUTE3 IN VARCHAR2,-- DEFAULT NULL,
300 X_GLOBAL_ATTRIBUTE4 IN VARCHAR2,-- DEFAULT NULL,
301 X_GLOBAL_ATTRIBUTE5 IN VARCHAR2,-- DEFAULT NULL,
302 X_GLOBAL_ATTRIBUTE6 IN VARCHAR2,-- DEFAULT NULL,
303 X_GLOBAL_ATTRIBUTE7 IN VARCHAR2,-- DEFAULT NULL,
304 X_GLOBAL_ATTRIBUTE8 IN VARCHAR2,-- DEFAULT NULL,
305 X_GLOBAL_ATTRIBUTE9 IN VARCHAR2,-- DEFAULT NULL,
306 X_GLOBAL_ATTRIBUTE10 IN VARCHAR2,-- DEFAULT NULL,
307 X_GLOBAL_ATTRIBUTE11 IN VARCHAR2,-- DEFAULT NULL,
308 X_GLOBAL_ATTRIBUTE12 IN VARCHAR2,-- DEFAULT NULL,
309 X_GLOBAL_ATTRIBUTE13 IN VARCHAR2,-- DEFAULT NULL,
310 X_GLOBAL_ATTRIBUTE14 IN VARCHAR2,-- DEFAULT NULL,
311 X_GLOBAL_ATTRIBUTE15 IN VARCHAR2,-- DEFAULT NULL,
312 X_GLOBAL_ATTRIBUTE16 IN VARCHAR2,-- DEFAULT NULL,
313 X_GLOBAL_ATTRIBUTE17 IN VARCHAR2,-- DEFAULT NULL,
314 X_GLOBAL_ATTRIBUTE18 IN VARCHAR2,-- DEFAULT NULL,
315 X_GLOBAL_ATTRIBUTE19 IN VARCHAR2,-- DEFAULT NULL,
316 X_GLOBAL_ATTRIBUTE20 IN VARCHAR2,-- DEFAULT NULL,
317 X_MATR_CAL_TYPE IN VARCHAR2,-- DEFAULT NULL ,
318 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
319 X_INIT_CAL_TYPE IN VARCHAR2,-- DEFAULT NULL ,
320 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
321 X_RECENT_CAL_TYPE IN VARCHAR2,-- DEFAULT NULL ,
322 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
323 X_CATALOG_CAL_TYPE IN VARCHAR2,-- DEFAULT NULL ,
324 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
325 X_PARTY_LAST_UPDATE_DATE IN OUT NOCOPY DATE ,
326 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER,
327 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
328
329 ) AS
330 /*************************************************************
331 Created By : svisweas
332 Date Created By :23-MAY-2000
333 Purpose : TO VALIDATE BEFORE DML
334 Know limitations, enhancements or remarks
335 Change History
336 Who When What
337 svisweas 26-may-2000 get_fk_igs_lookups_view procedure from tbh.
338 (reverse chronological order - newest change first)
339 ***************************************************************/
340 BEGIN
341 Set_Column_Values (
342 X_action,
343 X_ROWID,
344 X_PERSON_ID,
345 X_ETHNIC_ORIGIN_ID,
346 X_MARITAL_STATUS ,
347 X_MARITAL_STAT_EFFECT_DT,
348 X_ANN_FAMILY_INCOME ,
349 X_NUMBER_IN_FAMILY ,
350 X_CONTENT_SOURCE_TYPE ,
351 X_INTERNAL_FLAG ,
352 X_PERSON_NUMBER ,
353 X_EFFECTIVE_START_DATE,
354 X_effective_end_date ,
355 X_ethnic_origin ,
356 X_religion ,
357 X_next_to_kin ,
358 X_next_to_kin_meaning ,
359 X_place_of_birth ,
360 X_socio_eco_status ,
361 X_socio_eco_status_desc ,
362 X_further_education ,
363 X_further_education_desc,
364 X_in_state_tuition ,
365 X_tuition_st_Date ,
366 X_tuition_end_date ,
367 X_person_initials ,
368 X_primary_contact_id ,
369 X_personal_income ,
370 X_head_of_household_flag,
371 X_content_source_number ,
372 x_hz_parties_ovn,
373 X_ATTRIBUTE_CATEGORY ,
374 X_ATTRIBUTE1 ,
375 X_ATTRIBUTE2 ,
376 X_ATTRIBUTE3 ,
377 X_ATTRIBUTE4 ,
378 X_ATTRIBUTE5 ,
379 X_ATTRIBUTE6 ,
380 X_ATTRIBUTE7 ,
381 X_ATTRIBUTE8 ,
382 X_ATTRIBUTE9 ,
383 X_ATTRIBUTE10 ,
384 X_ATTRIBUTE11 ,
385 X_ATTRIBUTE12 ,
386 X_ATTRIBUTE13 ,
387 X_ATTRIBUTE14 ,
388 X_ATTRIBUTE15 ,
389 X_ATTRIBUTE16 ,
390 X_ATTRIBUTE17 ,
391 X_ATTRIBUTE18 ,
392 X_ATTRIBUTE19 ,
393 X_ATTRIBUTE20 ,
394 X_GLOBAL_ATTRIBUTE_CATEGORY,
395 X_GLOBAL_ATTRIBUTE1 ,
396 X_GLOBAL_ATTRIBUTE2 ,
397 X_GLOBAL_ATTRIBUTE3 ,
398 X_GLOBAL_ATTRIBUTE4 ,
399 X_GLOBAL_ATTRIBUTE5 ,
400 X_GLOBAL_ATTRIBUTE6 ,
401 X_GLOBAL_ATTRIBUTE7 ,
402 X_GLOBAL_ATTRIBUTE8 ,
403 X_GLOBAL_ATTRIBUTE9 ,
404 X_GLOBAL_ATTRIBUTE10 ,
405 X_GLOBAL_ATTRIBUTE11 ,
406 X_GLOBAL_ATTRIBUTE12 ,
407 X_GLOBAL_ATTRIBUTE13 ,
408 X_GLOBAL_ATTRIBUTE14 ,
409 X_GLOBAL_ATTRIBUTE15 ,
410 X_GLOBAL_ATTRIBUTE16 ,
411 X_GLOBAL_ATTRIBUTE17 ,
412 X_GLOBAL_ATTRIBUTE18 ,
413 X_GLOBAL_ATTRIBUTE19 ,
414 X_GLOBAL_ATTRIBUTE20 ,
415 X_PARTY_LAST_UPDATE_DATE ,
416 X_PERSON_PROFILE_ID ,
417 X_MATR_CAL_TYPE,
418 X_MATR_SEQUENCE_NUMBER,
419 X_INIT_CAL_TYPE,
420 X_INIT_SEQUENCE_NUMBER,
421 X_RECENT_CAL_TYPE,
422 X_RECENT_SEQUENCE_NUMBER,
423 X_CATALOG_CAL_TYPE,
424 X_CATALOG_SEQUENCE_NUMBER,
425 Z_RETURN_STATUS ,
426 Z_MSG_COUNT ,
427 Z_MSG_DATA ,
428 X_BIRTH_CNTRY_RESN_CODE
429 );
430 IF (X_action = 'INSERT') THEN
431 -- Call all the procedures related to Before Insert.
432 NULL;
433
434 ELSIF (X_action = 'UPDATE') THEN
435 NULL;
436 ELSIF (X_action = 'VALIDATE_INSERT') THEN
437 -- Call all the procedures related to Before Insert.
438 NULL;
439 ELSIF (X_action = 'VALIDATE_UPDATE') THEN
440 NULL;
441 END IF;
442 END Before_DML;
443 PROCEDURE INSERT_ROW (
444 X_action IN VARCHAR2 ,
445 X_ROWID IN OUT NOCOPY VARCHAR2,
446 X_PERSON_ID IN NUMBER ,
447 X_ETHNIC_ORIGIN_ID IN VARCHAR2 ,
448 X_MARITAL_STATUS IN VARCHAR2 ,
449 X_MARITAL_STAT_EFFECT_DT IN DATE ,
450 X_ANN_FAMILY_INCOME IN NUMBER ,
451 X_NUMBER_IN_FAMILY IN NUMBER ,
452 X_CONTENT_SOURCE_TYPE IN VARCHAR2 ,
453 X_INTERNAL_FLAG IN VARCHAR2 ,
454 X_PERSON_NUMBER IN VARCHAR2 ,
455 X_EFFECTIVE_START_DATE IN DATE ,
456 X_effective_end_date IN DATE ,
457 X_ethnic_origin IN VARCHAR2 ,
458 X_religion IN VARCHAR2 ,
459 X_next_to_kin IN VARCHAR2 ,
460 X_next_to_kin_meaning IN VARCHAR2 ,
461 X_place_of_birth IN VARCHAR2 ,
462 X_socio_eco_status IN VARCHAR2 ,
463 X_socio_eco_status_desc IN VARCHAR2 ,
464 X_further_education IN VARCHAR2 ,
465 X_further_education_desc IN VARCHAR2 ,
466 X_in_state_tuition IN VARCHAR2 ,
467 X_tuition_st_Date IN DATE ,
468 X_tuition_end_date IN DATE ,
469 X_person_initials IN VARCHAR2,
470 X_primary_contact_id IN NUMBER ,
471 X_personal_income IN NUMBER ,
472 X_head_of_household_flag IN VARCHAR2 ,
473 X_content_source_number IN VARCHAR2 ,
474 x_hz_parties_ovn IN OUT NOCOPY NUMBER,
475 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
476 X_ATTRIBUTE1 IN VARCHAR2 ,
477 X_ATTRIBUTE2 IN VARCHAR2 ,
478 X_ATTRIBUTE3 IN VARCHAR2 ,
479 X_ATTRIBUTE4 IN VARCHAR2 ,
480 X_ATTRIBUTE5 IN VARCHAR2 ,
481 X_ATTRIBUTE6 IN VARCHAR2 ,
482 X_ATTRIBUTE7 IN VARCHAR2 ,
483 X_ATTRIBUTE8 IN VARCHAR2 ,
484 X_ATTRIBUTE9 IN VARCHAR2 ,
485 X_ATTRIBUTE10 IN VARCHAR2 ,
486 X_ATTRIBUTE11 IN VARCHAR2 ,
487 X_ATTRIBUTE12 IN VARCHAR2 ,
488 X_ATTRIBUTE13 IN VARCHAR2 ,
489 X_ATTRIBUTE14 IN VARCHAR2 ,
490 X_ATTRIBUTE15 IN VARCHAR2 ,
491 X_ATTRIBUTE16 IN VARCHAR2 ,
492 X_ATTRIBUTE17 IN VARCHAR2 ,
493 X_ATTRIBUTE18 IN VARCHAR2 ,
494 X_ATTRIBUTE19 IN VARCHAR2 ,
495 X_ATTRIBUTE20 IN VARCHAR2 ,
496 X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
497 X_GLOBAL_ATTRIBUTE1 IN VARCHAR2 ,
498 X_GLOBAL_ATTRIBUTE2 IN VARCHAR2 ,
499 X_GLOBAL_ATTRIBUTE3 IN VARCHAR2 ,
500 X_GLOBAL_ATTRIBUTE4 IN VARCHAR2 ,
501 X_GLOBAL_ATTRIBUTE5 IN VARCHAR2 ,
502 X_GLOBAL_ATTRIBUTE6 IN VARCHAR2 ,
503 X_GLOBAL_ATTRIBUTE7 IN VARCHAR2 ,
504 X_GLOBAL_ATTRIBUTE8 IN VARCHAR2 ,
505 X_GLOBAL_ATTRIBUTE9 IN VARCHAR2 ,
506 X_GLOBAL_ATTRIBUTE10 IN VARCHAR2 ,
507 X_GLOBAL_ATTRIBUTE11 IN VARCHAR2 ,
508 X_GLOBAL_ATTRIBUTE12 IN VARCHAR2 ,
509 X_GLOBAL_ATTRIBUTE13 IN VARCHAR2 ,
510 X_GLOBAL_ATTRIBUTE14 IN VARCHAR2 ,
511 X_GLOBAL_ATTRIBUTE15 IN VARCHAR2 ,
512 X_GLOBAL_ATTRIBUTE16 IN VARCHAR2 ,
513 X_GLOBAL_ATTRIBUTE17 IN VARCHAR2 ,
514 X_GLOBAL_ATTRIBUTE18 IN VARCHAR2 ,
515 X_GLOBAL_ATTRIBUTE19 IN VARCHAR2 ,
516 X_GLOBAL_ATTRIBUTE20 IN VARCHAR2 ,
517 X_PARTY_LAST_UPDATE_DATE IN OUT NOCOPY DATE ,
518 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER ,
519 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
520 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
521 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
522 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
523 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
524 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
525 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
526 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
527 Z_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
528 Z_MSG_COUNT OUT NOCOPY NUMBER ,
529 Z_MSG_DATA OUT NOCOPY VARCHAR2 ,
530 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
531 ) AS
532 /*************************************************************
533 Created By : svisweas
534 Date Created By :23-MAY-2000
535 Purpose : TO VALIDATE INSERT ROW
536 Know limitations, enhancements or remarks
537 Change History
538 Who When What
539 --sbaliga 7-May-2002 Modified code as part of#2338473
540 --bayadav 31-Jan-2002 Added one DFF in IGS_PE_STAT_DETAILS and DFF columns in IGS_AD_STAT_INT table.
541 -- Due to which 1)Using the columns attribute1 to attribute20 parameters of this TBH
542 -- to transfer the data from IGS_AD_STAT_INT DFF columns to IGS_PE_STAT_DETAILS DFF columns
543 -- 2)Changing the earlier use of these parameters attribute1 to attribute20 for HZ table to NULL.Bug number:2203778
544 --pkpatel 27-JUN-2003 Bug 3019813(Made the Hz_Party_v2Pub.update_person call only while HZ related attrubures are modified)
545 svisweas 26-may-2000 get_fk_igs_lookups_view procedure from tbh.
546 (reverse chronological order - newest change first)
547 ***************************************************************/
548 LV_INIT_MSG_LIST VARCHAR2(1) ;
549 LV_COMMIT VARCHAR2(1) ;
550 LV_LAST_UPDATE_DATE DATE ;
551 LV_PARTY_LAST_UPDATE_DATE DATE;
552 LV_PROFILE_ID NUMBER;
553 LV_PERSON_REC_TYPE Hz_Party_V2Pub.PERSON_REC_TYPE;
554 LV_PARTY_REC_TYPE Hz_Party_V2Pub.PARTY_REC_TYPE;
555 l_tmp_var1 VARCHAR2(2000);
556 l_tmp_var VARCHAR2(2000);
557 l_default_date DATE := igs_ge_date.igsdate('9999/01/01');
558 l_default_value VARCHAR2(1) := 'X';
559
560 lvRowID VARCHAR2(25);
561
562 BEGIN
563 Before_DML(
564 X_action=>'INSERT',
565 X_rowid => XXX_ROWID,
566 X_PERSON_ID => X_PERSON_ID,
567 X_ETHNIC_ORIGIN_ID => X_ETHNIC_ORIGIN_ID,
568 X_MARITAL_STATUS => X_MARITAL_STATUS,
569 X_MARITAL_STAT_EFFECT_DT => X_MARITAL_STAT_EFFECT_DT,
570 X_ANN_FAMILY_INCOME =>X_ANN_FAMILY_INCOME,
571 X_NUMBER_IN_FAMILY =>X_NUMBER_IN_FAMILY,
572 X_CONTENT_SOURCE_TYPE =>X_CONTENT_SOURCE_TYPE,
573 X_INTERNAL_FLAG =>X_INTERNAL_FLAG,
574 X_PERSON_NUMBER =>X_PERSON_NUMBER,
575 X_EFFECTIVE_START_DATE =>X_EFFECTIVE_START_DATE,
576 X_effective_end_date =>X_EFFECTIVE_END_DATE,
577 X_ethnic_origin =>X_ETHNIC_ORIGIN,
578 X_religion =>X_RELIGION,
579 X_next_to_kin =>X_NEXT_TO_KIN,
580 X_next_to_kin_meaning => X_NEXT_TO_KIN_MEANING,
581 X_place_of_birth =>X_PLACE_OF_BIRTH,
582 X_socio_eco_status =>X_SOCIO_ECO_STATUS,
583 X_socio_eco_status_desc =>X_SOCIO_ECO_STATUS_DESC,
584 X_further_education =>X_FURTHER_EDUCATION,
585 X_further_education_desc =>X_FURTHER_EDUCATION_DESC,
586 X_in_state_tuition =>X_IN_STATE_TUITION,
587 X_tuition_st_Date =>X_TUITION_ST_DATE,
588 X_tuition_end_date =>X_TUITION_END_DATE,
589 X_person_initials =>X_PERSON_INITIALS,
590 X_primary_contact_id =>X_PRIMARY_CONTACT_ID,
591 X_personal_income =>X_PERSONAL_INCOME,
592 X_head_of_household_flag =>X_HEAD_OF_HOUSEHOLD_FLAG,
593 X_content_source_number =>X_CONTENT_SOURCE_NUMBER,
594 x_hz_parties_ovn =>X_HZ_PARTIES_OVN,
595 X_ATTRIBUTE_CATEGORY =>X_ATTRIBUTE_CATEGORY,
596 X_ATTRIBUTE1 =>X_ATTRIBUTE1,
597 X_ATTRIBUTE2 =>X_ATTRIBUTE2,
598 X_ATTRIBUTE3 =>X_ATTRIBUTE3,
599 X_ATTRIBUTE4 =>X_ATTRIBUTE4,
600 X_ATTRIBUTE5 =>X_ATTRIBUTE5,
601 X_ATTRIBUTE6 =>X_ATTRIBUTE6,
602 X_ATTRIBUTE7 =>X_ATTRIBUTE7,
603 X_ATTRIBUTE8 =>X_ATTRIBUTE8,
604 X_ATTRIBUTE9 =>X_ATTRIBUTE9,
605 X_ATTRIBUTE10 =>X_ATTRIBUTE10,
606 X_ATTRIBUTE11 =>X_ATTRIBUTE11,
607 X_ATTRIBUTE12 =>X_ATTRIBUTE12,
608 X_ATTRIBUTE13 =>X_ATTRIBUTE13,
609 X_ATTRIBUTE14 =>X_ATTRIBUTE14,
610 X_ATTRIBUTE15 =>X_ATTRIBUTE15,
611 X_ATTRIBUTE16 =>X_ATTRIBUTE16,
612 X_ATTRIBUTE17 =>X_ATTRIBUTE17,
613 X_ATTRIBUTE18 =>X_ATTRIBUTE18,
614 X_ATTRIBUTE19 =>X_ATTRIBUTE19,
615 X_ATTRIBUTE20 =>X_ATTRIBUTE20,
616 X_GLOBAL_ATTRIBUTE_CATEGORY =>X_GLOBAL_ATTRIBUTE_CATEGORY,
617 X_GLOBAL_ATTRIBUTE1 =>X_GLOBAL_ATTRIBUTE1,
618 X_GLOBAL_ATTRIBUTE2 =>X_GLOBAL_ATTRIBUTE2,
619 X_GLOBAL_ATTRIBUTE3 =>X_GLOBAL_ATTRIBUTE3,
620 X_GLOBAL_ATTRIBUTE4 =>X_GLOBAL_ATTRIBUTE4,
621 X_GLOBAL_ATTRIBUTE5 =>X_GLOBAL_ATTRIBUTE5,
622 X_GLOBAL_ATTRIBUTE6 =>X_GLOBAL_ATTRIBUTE6,
623 X_GLOBAL_ATTRIBUTE7 =>X_GLOBAL_ATTRIBUTE7,
624 X_GLOBAL_ATTRIBUTE8 =>X_GLOBAL_ATTRIBUTE8,
625 X_GLOBAL_ATTRIBUTE9 =>X_GLOBAL_ATTRIBUTE9,
626 X_GLOBAL_ATTRIBUTE10 =>X_GLOBAL_ATTRIBUTE10,
627 X_GLOBAL_ATTRIBUTE11 =>X_GLOBAL_ATTRIBUTE11,
628 X_GLOBAL_ATTRIBUTE12 =>X_GLOBAL_ATTRIBUTE12,
629 X_GLOBAL_ATTRIBUTE13 =>X_GLOBAL_ATTRIBUTE13,
630 X_GLOBAL_ATTRIBUTE14 =>X_GLOBAL_ATTRIBUTE14,
631 X_GLOBAL_ATTRIBUTE15 =>X_GLOBAL_ATTRIBUTE15,
632 X_GLOBAL_ATTRIBUTE16 =>X_GLOBAL_ATTRIBUTE16,
633 X_GLOBAL_ATTRIBUTE17 =>X_GLOBAL_ATTRIBUTE17,
634 X_GLOBAL_ATTRIBUTE18 =>X_GLOBAL_ATTRIBUTE18,
635 X_GLOBAL_ATTRIBUTE19 =>X_GLOBAL_ATTRIBUTE19,
636 X_GLOBAL_ATTRIBUTE20 =>X_GLOBAL_ATTRIBUTE20,
637 X_MATR_CAL_TYPE => X_MATR_CAL_TYPE,
638 X_MATR_SEQUENCE_NUMBER => X_MATR_SEQUENCE_NUMBER,
639 X_INIT_CAL_TYPE => X_INIT_CAL_TYPE,
640 X_INIT_SEQUENCE_NUMBER => X_INIT_SEQUENCE_NUMBER,
641 X_RECENT_CAL_TYPE => X_RECENT_CAL_TYPE,
642 X_RECENT_SEQUENCE_NUMBER => X_RECENT_SEQUENCE_NUMBER,
643 X_CATALOG_CAL_TYPE => X_CATALOG_CAL_TYPE,
644 X_CATALOG_SEQUENCE_NUMBER => X_CATALOG_SEQUENCE_NUMBER,
645 X_PARTY_LAST_UPDATE_DATE =>X_PARTY_LAST_UPDATE_DATE,
646 X_PERSON_PROFILE_ID =>X_PERSON_PROFILE_ID,
647 X_BIRTH_CNTRY_RESN_CODE =>X_BIRTH_CNTRY_RESN_CODE);
648
649
650 -- initialize the party record
651
652 LV_PERSON_REC_TYPE.PARTY_REC.PARTY_ID := X_PERSON_ID;
653 LV_PERSON_REC_TYPE.DECLARED_ETHNICITY := NVL(X_ETHNIC_ORIGIN_ID,FND_API.G_MISS_CHAR);
654 LV_PERSON_REC_TYPE.MARITAL_STATUS := NVL(X_MARITAL_STATUS , FND_API.G_MISS_CHAR);
655 LV_PERSON_REC_TYPE.MARITAL_STATUS_EFFECTIVE_DATE := NVL(X_MARITAL_STAT_EFFECT_DT ,FND_API.G_MISS_DATE);
656 LV_PERSON_REC_TYPE.HOUSEHOLD_INCOME := NVL(X_ANN_FAMILY_INCOME,FND_API.G_MISS_NUM);
657 LV_PERSON_REC_TYPE.HOUSEHOLD_SIZE := NVL(X_NUMBER_IN_FAMILY,FND_API.G_MISS_NUM);
658 LV_PERSON_REC_TYPE.PLACE_OF_BIRTH := NVL(X_PLACE_OF_BIRTH,FND_API.G_MISS_CHAR);
659 LV_PERSON_REC_TYPE.CONTENT_SOURCE_TYPE := HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE;
660 LV_PERSON_REC_TYPE.INTERNAL_FLAG := NVL(X_INTERNAL_FLAG , FND_API.G_MISS_CHAR);
661
662 -- do not initialise the attribute 1.20 and do not pass global attributes 1.20
663
664 IF X_ACTION ='INSERT' THEN
665
666
667 -- ssawhney : v2api uptake.
668 IF ( NVL(old_references.ethnic_origin_id,l_default_value) <> NVL(new_references.ethnic_origin_id,l_default_value) OR
669 NVL(old_references.marital_status,l_default_value) <> NVL(new_references.marital_status,l_default_value) OR
670 NVL(old_references.marital_status_effective_date,l_default_date) <> NVL(new_references.marital_status_effective_date,l_default_date) OR
671 NVL(old_references.place_of_birth,l_default_value) <> NVL(new_references.place_of_birth,l_default_value)) THEN
672
673 Hz_Party_v2Pub.update_person(
674 p_party_object_version_number => x_hz_parties_ovn,
675 P_PERSON_REC => LV_PERSON_REC_TYPE,
676 X_PROFILE_ID => Z_PROFILE_ID,
677 X_RETURN_STATUS => Z_RETURN_STATUS,
678 X_MSG_COUNT =>Z_MSG_COUNT,
679 X_MSG_DATA => Z_MSG_DATA
680 );
681 ELSE
682 Z_RETURN_STATUS := 'S';
683 END IF;
684 END IF;
685
686 IF Z_RETURN_STATUS IN ('E' , 'U') THEN
687 fnd_message.set_name ('AR',Y_MSG_DATA);
688 --Code added by sbaliga as part of #2338473
689 IF z_msg_count > 1 THEN
690 FOR i IN 1..z_msg_count LOOP
691 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
692 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
693 END LOOP;
694 z_msg_data := l_tmp_var1;
695 END IF;
696
697 RETURN;
698 ELSIF Z_RETURN_STATUS = 'S' OR Z_RETURN_STATUS IS NULL THEN
699
700 --ssawhney :
701 -- removed this check as its always going to pass, so no use keeping it as
702 -- z_prof will never be same as x_per prof and x_per prof will always be null
703 -- IF Z_PROFILE_ID <> X_PERSON_PROFILE_ID OR X_PERSON_PROFILE_ID IS NULL THEN
704
705 Igs_Pe_Stat_Details_Pkg.insert_row (
706 x_rowid => lvRowID,
707 x_person_id => X_PERSON_ID,
708 x_effective_start_date => x_effective_start_date,
709 x_effective_end_date => x_effective_end_date,
710 x_religion_cd => x_religion,
711 x_socio_eco_cd => X_socio_eco_status,
712 x_next_to_kin => x_next_to_kin,
713 x_in_state_tuition => x_in_state_tuition,
714 x_tuition_st_date => x_tuition_st_date,
715 x_tuition_end_date => x_tuition_end_date,
716 x_further_education_cd => x_further_education,
717 X_MATR_CAL_TYPE => X_MATR_CAL_TYPE,
718 X_MATR_SEQUENCE_NUMBER => X_MATR_SEQUENCE_NUMBER,
719 X_INIT_CAL_TYPE => X_INIT_CAL_TYPE,
720 X_INIT_SEQUENCE_NUMBER => X_INIT_SEQUENCE_NUMBER,
721 X_RECENT_CAL_TYPE => X_RECENT_CAL_TYPE,
722 X_RECENT_SEQUENCE_NUMBER => X_RECENT_SEQUENCE_NUMBER,
723 X_CATALOG_CAL_TYPE => X_CATALOG_CAL_TYPE,
724 X_CATALOG_SEQUENCE_NUMBER => X_CATALOG_SEQUENCE_NUMBER,
725 X_MODE => 'R' ,
726 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
727 X_ATTRIBUTE1 => X_ATTRIBUTE1,
728 X_ATTRIBUTE2 => X_ATTRIBUTE2,
729 X_ATTRIBUTE3 => X_ATTRIBUTE3,
730 X_ATTRIBUTE4 => X_ATTRIBUTE4,
731 X_ATTRIBUTE5 => X_ATTRIBUTE5,
732 X_ATTRIBUTE6 => X_ATTRIBUTE6,
733 X_ATTRIBUTE7 => X_ATTRIBUTE7,
734 X_ATTRIBUTE8 => X_ATTRIBUTE8,
735 X_ATTRIBUTE9 => X_ATTRIBUTE9,
736 X_ATTRIBUTE10 => X_ATTRIBUTE10,
737 X_ATTRIBUTE11 => X_ATTRIBUTE11,
738 X_ATTRIBUTE12 => X_ATTRIBUTE12,
739 X_ATTRIBUTE13 => X_ATTRIBUTE13,
740 X_ATTRIBUTE14 => X_ATTRIBUTE14,
741 X_ATTRIBUTE15 => X_ATTRIBUTE15,
742 X_ATTRIBUTE16 => X_ATTRIBUTE16,
743 X_ATTRIBUTE17 => X_ATTRIBUTE17,
744 X_ATTRIBUTE18 => X_ATTRIBUTE18,
745 X_ATTRIBUTE19 => X_ATTRIBUTE19,
746 X_ATTRIBUTE20 => X_ATTRIBUTE20,
747 X_BIRTH_CNTRY_RESN_CODE => X_BIRTH_CNTRY_RESN_CODE);
748
749
750 -- END IF;
751 END IF;
752 -- ssawhney : dontknow why these are commented.
753 -- X_PERSON_PROFILE_ID := Z_PROFILE_ID;
754 -- X_ROWID := XXX_ROWID;
755 END Insert_Row;
756 ----------here i am calling the api and my procedure
757 PROCEDURE UPDATE_ROW (
758 X_action IN VARCHAR2 ,
759 X_ROWID IN VARCHAR2 ,
760 X_PERSON_ID IN NUMBER ,
761 X_ETHNIC_ORIGIN_ID IN VARCHAR2 ,
762 X_MARITAL_STATUS IN VARCHAR2 ,
763 X_MARITAL_STAT_EFFECT_DT IN DATE ,
764 X_ANN_FAMILY_INCOME IN NUMBER ,
765 X_NUMBER_IN_FAMILY IN NUMBER ,
766 X_CONTENT_SOURCE_TYPE IN VARCHAR2 ,
767 X_INTERNAL_FLAG IN VARCHAR2 ,
768 X_PERSON_NUMBER IN VARCHAR2 ,
769 X_EFFECTIVE_START_DATE IN DATE ,
770 X_effective_end_date IN DATE ,
771 X_ethnic_origin IN VARCHAR2 ,
772 X_religion IN VARCHAR2 ,
773 X_next_to_kin IN VARCHAR2 ,
774 X_next_to_kin_meaning IN VARCHAR2 ,
775 X_place_of_birth IN VARCHAR2 ,
776 X_socio_eco_status IN VARCHAR2 ,
777 X_socio_eco_status_desc IN VARCHAR2 ,
778 X_further_education IN VARCHAR2 ,
779 X_further_education_desc IN VARCHAR2 ,
780 X_in_state_tuition IN VARCHAR2 ,
781 X_tuition_st_Date IN DATE ,
782 X_tuition_end_date IN DATE ,
783 X_person_initials IN VARCHAR2,
784 X_primary_contact_id IN NUMBER ,
785 X_personal_income IN NUMBER ,
786 X_head_of_household_flag IN VARCHAR2 ,
787 X_content_source_number IN VARCHAR2 ,
788 x_hz_parties_ovn IN OUT NOCOPY NUMBER,
789 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
790 X_ATTRIBUTE1 IN VARCHAR2 ,
791 X_ATTRIBUTE2 IN VARCHAR2 ,
792 X_ATTRIBUTE3 IN VARCHAR2 ,
793 X_ATTRIBUTE4 IN VARCHAR2 ,
794 X_ATTRIBUTE5 IN VARCHAR2 ,
795 X_ATTRIBUTE6 IN VARCHAR2 ,
796 X_ATTRIBUTE7 IN VARCHAR2 ,
797 X_ATTRIBUTE8 IN VARCHAR2 ,
798 X_ATTRIBUTE9 IN VARCHAR2 ,
799 X_ATTRIBUTE10 IN VARCHAR2 ,
800 X_ATTRIBUTE11 IN VARCHAR2 ,
801 X_ATTRIBUTE12 IN VARCHAR2 ,
802 X_ATTRIBUTE13 IN VARCHAR2 ,
803 X_ATTRIBUTE14 IN VARCHAR2 ,
804 X_ATTRIBUTE15 IN VARCHAR2 ,
805 X_ATTRIBUTE16 IN VARCHAR2 ,
806 X_ATTRIBUTE17 IN VARCHAR2 ,
807 X_ATTRIBUTE18 IN VARCHAR2 ,
808 X_ATTRIBUTE19 IN VARCHAR2 ,
809 X_ATTRIBUTE20 IN VARCHAR2 ,
810 X_GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
811 X_GLOBAL_ATTRIBUTE1 IN VARCHAR2 ,
812 X_GLOBAL_ATTRIBUTE2 IN VARCHAR2 ,
813 X_GLOBAL_ATTRIBUTE3 IN VARCHAR2 ,
814 X_GLOBAL_ATTRIBUTE4 IN VARCHAR2 ,
815 X_GLOBAL_ATTRIBUTE5 IN VARCHAR2 ,
816 X_GLOBAL_ATTRIBUTE6 IN VARCHAR2 ,
817 X_GLOBAL_ATTRIBUTE7 IN VARCHAR2 ,
818 X_GLOBAL_ATTRIBUTE8 IN VARCHAR2 ,
819 X_GLOBAL_ATTRIBUTE9 IN VARCHAR2 ,
820 X_GLOBAL_ATTRIBUTE10 IN VARCHAR2 ,
821 X_GLOBAL_ATTRIBUTE11 IN VARCHAR2 ,
822 X_GLOBAL_ATTRIBUTE12 IN VARCHAR2 ,
823 X_GLOBAL_ATTRIBUTE13 IN VARCHAR2 ,
824 X_GLOBAL_ATTRIBUTE14 IN VARCHAR2 ,
825 X_GLOBAL_ATTRIBUTE15 IN VARCHAR2 ,
826 X_GLOBAL_ATTRIBUTE16 IN VARCHAR2 ,
827 X_GLOBAL_ATTRIBUTE17 IN VARCHAR2 ,
828 X_GLOBAL_ATTRIBUTE18 IN VARCHAR2 ,
829 X_GLOBAL_ATTRIBUTE19 IN VARCHAR2 ,
830 X_GLOBAL_ATTRIBUTE20 IN VARCHAR2 ,
831 X_PARTY_LAST_UPDATE_DATE IN OUT NOCOPY DATE ,
832 X_PERSON_PROFILE_ID IN OUT NOCOPY NUMBER ,
833 X_MATR_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
834 X_MATR_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
835 X_INIT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
836 X_INIT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
837 X_RECENT_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
838 X_RECENT_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
839 X_CATALOG_CAL_TYPE IN VARCHAR2 ,-- DEFAULT NULL ,
840 X_CATALOG_SEQUENCE_NUMBER IN NUMBER ,-- DEFAULT NULL,
841 Z_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
842 Z_MSG_COUNT OUT NOCOPY NUMBER ,
843 Z_MSG_DATA OUT NOCOPY VARCHAR2,
844 X_BIRTH_CNTRY_RESN_CODE IN VARCHAR2
845 ) AS
846 /*************************************************************
847 Created By : svisweas
848 Date Created By :23-MAY-2000
849 Purpose : TO VALIDATE UPDATE ROW
850 Know limitations, enhancements or remarks
851 Change History
852 Who When What
853 --sbaliga 7-May-2002 Added code as part of #2338473
854 --bayadav 31-Jan-2002 Added one DFF in IGS_PE_STAT_DETAILS and DFF columns in IGS_AD_STAT_INT table.
855 -- Due to which 1)Using the columns attribute1 to attribute20 parameters of this TBH
856 -- to transfer the data from IGS_AD_STAT_INT DFF columns to IGS_PE_STAT_DETAILS DFF columns
857 -- 2)Changing the earlier use of these parameters attribute1 to attribute20 for HZ table to NULL.Bug number:2203778
858 svisweas 26-may-2000 get_fk_igs_lookups_view procedure from tbh.
859 --pkpatel 27-JUN-2003 Bug 3019813(Made the Hz_Party_v2Pub.update_person call only while HZ related attrubures are modified)
860 (reverse chronological order - newest change first)
861 ***************************************************************/
862 LV_INIT_MSG_LIST VARCHAR2(1);
863 LV_COMMIT VARCHAR2(1);
864 LV_LAST_UPDATE_DATE DATE ;
865 LV_PARTY_LAST_UPDATE_DATE DATE;
866 LV_PROFILE_ID NUMBER;
867 lvRowId VARCHAR2(25);
868 l_tmp_var1 VARCHAR2(2000);
869 l_tmp_var VARCHAR2(2000);
870
871 l_default_date DATE := igs_ge_date.igsdate('9999/01/01');
872 l_default_value VARCHAR2(1) := 'X';
873
874 LV_PERSON_REC_TYPE Hz_Party_V2Pub.PERSON_REC_TYPE;
875 LV_PARTY_REC_TYPE Hz_Party_V2Pub.PARTY_REC_TYPE;
876 CURSOR party_cur IS
877 SELECT hp.last_update_date , xx.effective_start_date
878 FROM hz_person_profiles xx ,
879 hz_parties hp
880 WHERE xx.party_id = X_person_id AND
881 hp.party_id = xx.party_id AND
882 TRUNC(SYSDATE) BETWEEN TRUNC(xx.effective_start_date) AND
883 NVL(xx.effective_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'));
884
885
886 CURSOR c_profile IS
887 SELECT rowid
888 FROM IGS_PE_STAT_DETAILS
889 WHERE person_id = X_PERSON_ID;
890
891 profile_rec c_profile%ROWTYPE;
892 l_effective_Start_date DATE;
893
894 BEGIN
895 XXX_ROWID := X_ROWID;
896 Before_DML(
897 X_action =>'UPDATE',
898 X_rowid =>XXX_ROWID,
899 X_PERSON_ID => X_PERSON_ID,
900 X_ETHNIC_ORIGIN_ID => X_ETHNIC_ORIGIN_ID,
901 X_MARITAL_STATUS => X_MARITAL_STATUS,
902 X_MARITAL_STAT_EFFECT_DT =>X_MARITAL_STAT_EFFECT_DT,
903 X_ANN_FAMILY_INCOME =>X_ANN_FAMILY_INCOME,
904 X_NUMBER_IN_FAMILY =>X_NUMBER_IN_FAMILY,
905 X_CONTENT_SOURCE_TYPE =>X_CONTENT_SOURCE_TYPE,
906 X_INTERNAL_FLAG =>X_INTERNAL_FLAG,
907 X_PERSON_NUMBER =>X_PERSON_NUMBER,
908 X_EFFECTIVE_START_DATE =>X_EFFECTIVE_START_DATE,
909 X_effective_end_date =>X_EFFECTIVE_END_DATE,
910 X_ethnic_origin =>X_ETHNIC_ORIGIN,
911 X_religion =>X_RELIGION,
912 X_next_to_kin =>X_NEXT_TO_KIN,
913 X_next_to_kin_meaning => X_NEXT_TO_KIN_MEANING,
914 X_place_of_birth =>X_PLACE_OF_BIRTH,
915 X_socio_eco_status =>X_SOCIO_ECO_STATUS,
916 X_socio_eco_status_desc =>X_SOCIO_ECO_STATUS_DESC,
917 X_further_education =>X_FURTHER_EDUCATION,
918 X_further_education_desc =>X_FURTHER_EDUCATION_DESC,
919 X_in_state_tuition =>X_IN_STATE_TUITION,
920 X_tuition_st_Date =>X_TUITION_ST_DATE,
921 X_tuition_end_date =>X_TUITION_END_DATE,
922 X_person_initials =>X_PERSON_INITIALS,
923 X_primary_contact_id =>X_PRIMARY_CONTACT_ID,
924 X_personal_income =>X_PERSONAL_INCOME,
925 X_head_of_household_flag =>X_HEAD_OF_HOUSEHOLD_FLAG,
926 X_content_source_number =>X_CONTENT_SOURCE_NUMBER,
927 x_hz_parties_ovn => X_HZ_PARTIES_OVN,
928 X_ATTRIBUTE_CATEGORY =>X_ATTRIBUTE_CATEGORY,
929 X_ATTRIBUTE1 =>X_ATTRIBUTE1,
930 X_ATTRIBUTE2 =>X_ATTRIBUTE2,
931 X_ATTRIBUTE3 =>X_ATTRIBUTE3,
932 X_ATTRIBUTE4 =>X_ATTRIBUTE4,
933 X_ATTRIBUTE5 =>X_ATTRIBUTE5,
934 X_ATTRIBUTE6 =>X_ATTRIBUTE6,
935 X_ATTRIBUTE7 =>X_ATTRIBUTE7,
936 X_ATTRIBUTE8 =>X_ATTRIBUTE8,
937 X_ATTRIBUTE9 =>X_ATTRIBUTE9,
938 X_ATTRIBUTE10 =>X_ATTRIBUTE10,
939 X_ATTRIBUTE11 =>X_ATTRIBUTE11,
940 X_ATTRIBUTE12 =>X_ATTRIBUTE12,
941 X_ATTRIBUTE13 =>X_ATTRIBUTE13,
942 X_ATTRIBUTE14 =>X_ATTRIBUTE14,
943 X_ATTRIBUTE15 =>X_ATTRIBUTE15,
944 X_ATTRIBUTE16 =>X_ATTRIBUTE16,
945 X_ATTRIBUTE17 =>X_ATTRIBUTE17,
946 X_ATTRIBUTE18 =>X_ATTRIBUTE18,
947 X_ATTRIBUTE19 =>X_ATTRIBUTE19,
948 X_ATTRIBUTE20 =>X_ATTRIBUTE20,
949 X_GLOBAL_ATTRIBUTE_CATEGORY =>X_GLOBAL_ATTRIBUTE_CATEGORY,
950 X_GLOBAL_ATTRIBUTE1 =>X_GLOBAL_ATTRIBUTE1,
951 X_GLOBAL_ATTRIBUTE2 =>X_GLOBAL_ATTRIBUTE2,
952 X_GLOBAL_ATTRIBUTE3 =>X_GLOBAL_ATTRIBUTE3,
953 X_GLOBAL_ATTRIBUTE4 =>X_GLOBAL_ATTRIBUTE4,
954 X_GLOBAL_ATTRIBUTE5 =>X_GLOBAL_ATTRIBUTE5,
955 X_GLOBAL_ATTRIBUTE6 =>X_GLOBAL_ATTRIBUTE6,
956 X_GLOBAL_ATTRIBUTE7 =>X_GLOBAL_ATTRIBUTE7,
957 X_GLOBAL_ATTRIBUTE8 =>X_GLOBAL_ATTRIBUTE8,
958 X_GLOBAL_ATTRIBUTE9 =>X_GLOBAL_ATTRIBUTE9,
959 X_GLOBAL_ATTRIBUTE10 =>X_GLOBAL_ATTRIBUTE10,
960 X_GLOBAL_ATTRIBUTE11 =>X_GLOBAL_ATTRIBUTE11,
961 X_GLOBAL_ATTRIBUTE12 =>X_GLOBAL_ATTRIBUTE12,
962 X_GLOBAL_ATTRIBUTE13 =>X_GLOBAL_ATTRIBUTE13,
963 X_GLOBAL_ATTRIBUTE14 =>X_GLOBAL_ATTRIBUTE14,
964 X_GLOBAL_ATTRIBUTE15 =>X_GLOBAL_ATTRIBUTE15,
965 X_GLOBAL_ATTRIBUTE16 =>X_GLOBAL_ATTRIBUTE16,
966 X_GLOBAL_ATTRIBUTE17 =>X_GLOBAL_ATTRIBUTE17,
967 X_GLOBAL_ATTRIBUTE18 =>X_GLOBAL_ATTRIBUTE18,
968 X_GLOBAL_ATTRIBUTE19 =>X_GLOBAL_ATTRIBUTE19,
969 X_GLOBAL_ATTRIBUTE20 =>X_GLOBAL_ATTRIBUTE20,
970 X_MATR_CAL_TYPE => X_MATR_CAL_TYPE,
971 X_MATR_SEQUENCE_NUMBER => X_MATR_SEQUENCE_NUMBER,
972 X_INIT_CAL_TYPE => X_INIT_CAL_TYPE,
973 X_INIT_SEQUENCE_NUMBER => X_INIT_SEQUENCE_NUMBER,
974 X_RECENT_CAL_TYPE => X_RECENT_CAL_TYPE,
975 X_RECENT_SEQUENCE_NUMBER => X_RECENT_SEQUENCE_NUMBER,
976 X_CATALOG_CAL_TYPE => X_CATALOG_CAL_TYPE,
977 X_CATALOG_SEQUENCE_NUMBER => X_CATALOG_SEQUENCE_NUMBER,
978 X_PARTY_LAST_UPDATE_DATE =>X_PARTY_LAST_UPDATE_DATE,
979 X_PERSON_PROFILE_ID =>X_PERSON_PROFILE_ID,
980 X_BIRTH_CNTRY_RESN_CODE => X_BIRTH_CNTRY_RESN_CODE
981 );
982 OPEN Party_cur;
983 FETCH party_cur INTO LV_PARTY_LAST_UPDATE_DATE, l_effective_start_date;
984 CLOSE PARTY_CUR;
985
986
987 LV_PERSON_REC_TYPE.PARTY_REC.PARTY_ID := X_PERSON_ID;
988 LV_PERSON_REC_TYPE.DECLARED_ETHNICITY := NVL(X_ETHNIC_ORIGIN_ID,FND_API.G_MISS_CHAR);
989 LV_PERSON_REC_TYPE.MARITAL_STATUS := NVL(X_MARITAL_STATUS , FND_API.G_MISS_CHAR);
990 LV_PERSON_REC_TYPE.MARITAL_STATUS_EFFECTIVE_DATE := NVL(X_MARITAL_STAT_EFFECT_DT ,FND_API.G_MISS_DATE);
991 LV_PERSON_REC_TYPE.HOUSEHOLD_INCOME := NVL(X_ANN_FAMILY_INCOME,FND_API.G_MISS_NUM);
992 LV_PERSON_REC_TYPE.HOUSEHOLD_SIZE := NVL(X_NUMBER_IN_FAMILY,FND_API.G_MISS_NUM);
993 LV_PERSON_REC_TYPE.PLACE_OF_BIRTH := NVL(X_PLACE_OF_BIRTH,FND_API.G_MISS_CHAR);
994 LV_PERSON_REC_TYPE.CONTENT_SOURCE_TYPE := HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE;
995 LV_PERSON_REC_TYPE.INTERNAL_FLAG := NVL(X_INTERNAL_FLAG , FND_API.G_MISS_CHAR);
996
997
998 IF X_ACTION ='UPDATE' THEN
999
1000 IF ( NVL(old_references.ethnic_origin_id,l_default_value) <> NVL(new_references.ethnic_origin_id,l_default_value) OR
1001 NVL(old_references.marital_status,l_default_value) <> NVL(new_references.marital_status,l_default_value) OR
1002 NVL(old_references.marital_status_effective_date,l_default_date) <> NVL(new_references.marital_status_effective_date,l_default_date) OR
1003 NVL(old_references.place_of_birth,l_default_value) <> NVL(new_references.place_of_birth,l_default_value)) THEN
1004
1005 Hz_Party_V2Pub.update_person(
1006 p_party_object_version_number => x_hz_parties_ovn ,
1007 P_PERSON_REC => LV_PERSON_REC_TYPE,
1008 X_PROFILE_ID => Z_PROFILE_ID,
1009 X_RETURN_STATUS => Z_RETURN_STATUS,
1010 X_MSG_COUNT =>Z_MSG_COUNT,
1011 X_MSG_DATA => Z_MSG_DATA
1012 );
1013 ELSE
1014 Z_RETURN_STATUS := 'S';
1015 END IF;
1016
1017 END IF;
1018
1019 IF Z_RETURN_STATUS IN ('E' , 'U') THEN
1020 -- fnd_message.set_name ('AR', Y_MSG_DATA);
1021 --Code added by sbaliga as part of #2338473
1022 IF z_msg_count > 1 THEN
1023 FOR i IN 1..z_msg_count LOOP
1024 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1025 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
1026 END LOOP;
1027 z_msg_data := l_tmp_var1;
1028 END IF;
1029
1030 RETURN;
1031 ELSIF Z_RETURN_STATUS = 'S' OR Z_RETURN_STATUS IS NULL THEN
1032
1033 --ssawhney :
1034 -- removed this check as its always going to pass, so no use keeping it as
1035 -- PK IS NOW CHANGED
1036 -- IF Z_PROFILE_ID = X_PERSON_PROFILE_ID OR Z_PROFILE_ID IS NULL THEN remove this check as PK now changed
1037 -- fetching the details in the cursor
1038 -- even the rowid of the update is changed.
1039
1040 OPEN c_profile;
1041 FETCH c_profile INTO profile_rec;
1042 IF c_profile%FOUND THEN
1043
1044 Igs_Pe_Stat_Details_Pkg.update_row (
1045 x_rowid => profile_rec.rowid, -- xxx_rowid,
1046 x_person_id => X_PERSON_ID,
1047 x_effective_start_date => NVL(x_effective_start_date, l_effective_start_date),
1048 x_effective_end_date => x_effective_end_date,
1049 x_religion_cd => x_religion,
1050 x_socio_eco_cd => x_socio_eco_status,
1051 x_next_to_kin => x_next_to_kin,
1052 x_in_state_tuition => x_in_state_tuition,
1053 x_tuition_st_date => x_tuition_st_date,
1054 x_tuition_end_date => x_tuition_end_date,
1055 x_further_education_cd => x_further_education,
1056 X_MATR_CAL_TYPE => X_MATR_CAL_TYPE,
1057 X_MATR_SEQUENCE_NUMBER => X_MATR_SEQUENCE_NUMBER,
1058 X_INIT_CAL_TYPE => X_INIT_CAL_TYPE,
1059 X_INIT_SEQUENCE_NUMBER => X_INIT_SEQUENCE_NUMBER,
1060 X_RECENT_CAL_TYPE => X_RECENT_CAL_TYPE,
1061 X_RECENT_SEQUENCE_NUMBER => X_RECENT_SEQUENCE_NUMBER,
1062 X_CATALOG_CAL_TYPE => X_CATALOG_CAL_TYPE,
1063 X_CATALOG_SEQUENCE_NUMBER => X_CATALOG_SEQUENCE_NUMBER,
1064 X_MODE => 'R' ,
1065 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
1066 X_ATTRIBUTE1 => X_ATTRIBUTE1,
1067 X_ATTRIBUTE2 => X_ATTRIBUTE2,
1068 X_ATTRIBUTE3 => X_ATTRIBUTE3,
1069 X_ATTRIBUTE4 => X_ATTRIBUTE4,
1070 X_ATTRIBUTE5 => X_ATTRIBUTE5,
1071 X_ATTRIBUTE6 => X_ATTRIBUTE6,
1072 X_ATTRIBUTE7 => X_ATTRIBUTE7,
1073 X_ATTRIBUTE8 => X_ATTRIBUTE8,
1074 X_ATTRIBUTE9 => X_ATTRIBUTE9,
1075 X_ATTRIBUTE10 => X_ATTRIBUTE10,
1076 X_ATTRIBUTE11 => X_ATTRIBUTE11,
1077 X_ATTRIBUTE12 => X_ATTRIBUTE12,
1078 X_ATTRIBUTE13 => X_ATTRIBUTE13,
1079 X_ATTRIBUTE14 => X_ATTRIBUTE14,
1080 X_ATTRIBUTE15 => X_ATTRIBUTE15,
1081 X_ATTRIBUTE16 => X_ATTRIBUTE16,
1082 X_ATTRIBUTE17 => X_ATTRIBUTE17,
1083 X_ATTRIBUTE18 => X_ATTRIBUTE18,
1084 X_ATTRIBUTE19 => X_ATTRIBUTE19,
1085 X_ATTRIBUTE20 => X_ATTRIBUTE20,
1086 X_BIRTH_CNTRY_RESN_CODE => X_BIRTH_CNTRY_RESN_CODE);
1087
1088
1089
1090 ELSE -- if profile not found then
1091
1092
1093 XXX_rowid := NULL;
1094
1095 Igs_Pe_Stat_Details_Pkg.insert_row (
1096 x_rowid => lvRowID,
1097 x_person_id => X_PERSON_ID,
1098 x_effective_start_date => NVL(x_effective_start_date, l_effective_start_date),
1099 x_effective_end_date => x_effective_end_date,
1100 x_religion_cd => x_religion,
1101 x_socio_eco_cd => x_socio_eco_status,
1102 x_next_to_kin => x_next_to_kin,
1103 x_in_state_tuition => x_in_state_tuition,
1104 x_tuition_st_date => x_tuition_st_date,
1105 x_tuition_end_date => x_tuition_end_date,
1106 x_further_education_cd => x_further_education,
1107 X_MATR_CAL_TYPE => X_MATR_CAL_TYPE,
1108 X_MATR_SEQUENCE_NUMBER => X_MATR_SEQUENCE_NUMBER,
1109 X_INIT_CAL_TYPE => X_INIT_CAL_TYPE,
1110 X_INIT_SEQUENCE_NUMBER => X_INIT_SEQUENCE_NUMBER,
1111 X_RECENT_CAL_TYPE => X_RECENT_CAL_TYPE,
1112 X_RECENT_SEQUENCE_NUMBER => X_RECENT_SEQUENCE_NUMBER,
1113 X_CATALOG_CAL_TYPE => X_CATALOG_CAL_TYPE,
1114 X_CATALOG_SEQUENCE_NUMBER => X_CATALOG_SEQUENCE_NUMBER,
1115 X_MODE => 'R' ,
1116 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
1117 X_ATTRIBUTE1 => X_ATTRIBUTE1,
1118 X_ATTRIBUTE2 => X_ATTRIBUTE2,
1119 X_ATTRIBUTE3 => X_ATTRIBUTE3,
1120 X_ATTRIBUTE4 => X_ATTRIBUTE4,
1121 X_ATTRIBUTE5 => X_ATTRIBUTE5,
1122 X_ATTRIBUTE6 => X_ATTRIBUTE6,
1123 X_ATTRIBUTE7 => X_ATTRIBUTE7,
1124 X_ATTRIBUTE8 => X_ATTRIBUTE8,
1125 X_ATTRIBUTE9 => X_ATTRIBUTE9,
1126 X_ATTRIBUTE10 => X_ATTRIBUTE10,
1127 X_ATTRIBUTE11 => X_ATTRIBUTE11,
1128 X_ATTRIBUTE12 => X_ATTRIBUTE12,
1129 X_ATTRIBUTE13 => X_ATTRIBUTE13,
1130 X_ATTRIBUTE14 => X_ATTRIBUTE14,
1131 X_ATTRIBUTE15 => X_ATTRIBUTE15,
1132 X_ATTRIBUTE16 => X_ATTRIBUTE16,
1133 X_ATTRIBUTE17 => X_ATTRIBUTE17,
1134 X_ATTRIBUTE18 => X_ATTRIBUTE18,
1135 X_ATTRIBUTE19 => X_ATTRIBUTE19,
1136 X_ATTRIBUTE20 => X_ATTRIBUTE20,
1137 X_BIRTH_CNTRY_RESN_CODE => X_BIRTH_CNTRY_RESN_CODE);
1138 END IF;
1139
1140 IF c_profile%ISOPEN THEN
1141 close c_profile;
1142 END IF;
1143 --ELSE -- if x_person_profile_id and z_person_profile_id dont match then also insert
1144 --END IF;
1145 END IF; -- return status
1146 END UPDATE_ROW;
1147 END Igs_Pe_Stat_Pkg;