1 PACKAGE BODY igs_pe_relationships_pkg AS
2 /* $Header: IGSNI41B.pls 120.6 2006/05/30 07:02:26 prbhardw ship $ */
3
4 PROCEDURE copy_address_and_usage(p_subject_id IN NUMBER,
5 p_object_id IN NUMBER,
6 p_validate OUT NOCOPY BOOLEAN) AS
7 ------------------------------------------------------------------
8 --Updated by : smanglm, Oracle India
9 --Date created: 27-MAY-2001
10 --
11 --Purpose:
12 --
13 --
14 --Known limitations/enhancements and/or remarks:
15 --
16 --Change History:
17 --Who When What
18 --gmaheswa 18-Nov-2003 Modified c_student_addr cursor to select start_dt,end_dt from
19 -- OSS extension tables,as part of address related changes .
20 -------------------------------------------------------------------
21
22 -- store the student age restriction
23 l_student_age_bar NUMBER := TO_NUMBER(NVL(FND_PROFILE.VALUE('IGS_AD_AUTONUM'),0)) ;
24
25 --
26 -- cursor to fetch the address of the student
27 --
28 CURSOR c_student_addr_prim (cp_person_id igs_pe_addr_v.person_id%TYPE,
29 cp_site_use_type hz_party_site_uses.site_use_type%TYPE) IS
30 SELECT
31 ihps.START_DATE START_DT, --hps.START_DATE_ACTIVE START_DT,
32 hps.party_site_id,
33 ihps.END_DATE END_DT, -- hps.END_DATE_ACTIVE END_DT,
34 hl.COUNTRY COUNTRY_CD,
35 hl.ADDRESS_STYLE,
36 hl.ADDRESS1 ADDR_LINE_1,
37 hl.ADDRESS2 ADDR_LINE_2,
38 hl.ADDRESS3 ADDR_LINE_3,
39 hl.ADDRESS4 ADDR_LINE_4,
40 hps.identifying_address_flag CORRESPONDENCE,
41 hl.CITY,
42 hl.STATE,
43 hl.PROVINCE,
44 hl.COUNTY,
45 hl.POSTAL_CODE,
46 hl.address_lines_phonetic,
47 hl.delivery_point_code,
48 hps.status
49 FROM
50 HZ_LOCATIONS hl,
51 hz_party_sites hps,
52 hz_party_site_uses hpsu,
53 igs_pe_hz_pty_sites ihps
54 WHERE
55 hl.location_id = hps.location_id AND
56 hps.party_id = cp_person_id AND
57 hps.party_site_id = hpsu.party_site_id AND
58 hpsu.site_use_type = cp_site_use_type AND
59 hps.party_site_id = ihps.party_site_id(+) AND
60 hps.status= 'A' AND
61 hpsu.status= 'A' AND
62 hps.identifying_address_flag = 'Y';
63
64 CURSOR c_student_addr (cp_person_id igs_pe_addr_v.person_id%TYPE,
65 cp_site_use_type hz_party_site_uses.site_use_type%TYPE) IS
66 SELECT
67 ihps.START_DATE START_DT, --hps.START_DATE_ACTIVE START_DT,
68 hps.party_site_id,
69 ihps.END_DATE END_DT, -- hps.END_DATE_ACTIVE END_DT,
70 hl.COUNTRY COUNTRY_CD,
71 hl.ADDRESS_STYLE,
72 hl.ADDRESS1 ADDR_LINE_1,
73 hl.ADDRESS2 ADDR_LINE_2,
74 hl.ADDRESS3 ADDR_LINE_3,
75 hl.ADDRESS4 ADDR_LINE_4,
76 hps.identifying_address_flag CORRESPONDENCE,
77 hl.CITY,
78 hl.STATE,
79 hl.PROVINCE,
80 hl.COUNTY,
81 hl.POSTAL_CODE,
82 hl.address_lines_phonetic,
83 hl.delivery_point_code,
84 hps.status
85 FROM
86 HZ_LOCATIONS hl,
87 hz_party_sites hps,
88 hz_party_site_uses hpsu,
89 igs_pe_hz_pty_sites ihps
90 WHERE
91 hl.location_id = hps.location_id AND
92 hps.party_id = cp_person_id AND
93 hps.party_site_id = hpsu.party_site_id AND
94 hpsu.site_use_type = cp_site_use_type AND
95 hps.party_site_id = ihps.party_site_id(+) AND
96 hps.status= 'A' AND
97 hpsu.status= 'A' AND
98 SYSDATE BETWEEN ihps.START_DATE AND NVL(ihps.END_DATE, SYSDATE)
99 ORDER BY ihps.START_DATE DESC;
100
101 rec_student_addr c_student_addr%ROWTYPE;
102
103 --
104 -- cursor to get the age of student
105 --
106 CURSOR c_student_age (cp_person_id igs_pe_person.person_id%TYPE) IS
107 SELECT TO_NUMBER(nvl((SYSDATE-birth_date)/365,0))
108 FROM igs_pe_person_base_v
109 WHERE person_id = cp_person_id;
110 l_student_age NUMBER;
111
112 l_return_status VARCHAR2(1) := NULL;
113 l_msg_count NUMBER;
114 l_msg_data VARCHAR2(2000) := NULL;
115 l_contact_person VARCHAR2(40) := NULL;
116 l_loc_id NUMBER;
117 l_rowid VARCHAR2(25);
118 l_location_id NUMBER(15);
119 l_party_site_id NUMBER;
120 l_last_update_date DATE;
121
122 l_site_last_update_date DATE;
123 l_profile_last_update_date DATE;
124 l_party_site_use_id NUMBER;
125 l_site_use_id NUMBER;
126 l_object_version_number NUMBER;
127 tmp_var VARCHAR2(2000);
128 tmp_var1 VARCHAR2(2000);
129 l_party_site_ovn hz_party_sites.object_version_number%TYPE;
130 l_location_ovn hz_locations.object_version_number%TYPE;
131 l_profile_addr_usage VARCHAR2(30);
132
133 BEGIN
134 p_validate := TRUE;
135
136 --
137 -- check whether the value exist for IGS_AD_ADR_USG or not. If no, give warning
138 --
139 l_profile_addr_usage := FND_PROFILE.VALUE('IGS_AD_ADR_USG');
140
141 IF l_profile_addr_usage IS NULL THEN
142 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_ADR_USG');
143 igs_ge_msg_stack.add;
144 app_exception.raise_exception;
145 END IF;
146
147 --
148 -- copy the address of the person to the member only if the student's age is
149 -- less than the value of profile IGS_AD_AUTONUM
150 --
151 -- get the student age
152 --
153 OPEN c_student_age (p_subject_id);
154 FETCH c_student_age INTO l_student_age;
155 CLOSE c_student_age;
156
157 IF l_student_age < l_student_age_bar THEN
158
159 --
160 -- check whether the address and usage exist for the person or not
161 -- First check whether any primary address exists associated with l_profile_addr_usage
162 -- if not found check whether any active address (Status = A and Sysdate BETWEEN start_date and NVL(end_date,Sysdate))
163 -- associated with l_profile_addr_usage is present.
164 --
165 OPEN c_student_addr_prim (p_subject_id, l_profile_addr_usage);
166 FETCH c_student_addr_prim INTO rec_student_addr;
167 IF c_student_addr_prim%NOTFOUND THEN
168 CLOSE c_student_addr_prim;
169
170 OPEN c_student_addr (p_subject_id, l_profile_addr_usage);
171 FETCH c_student_addr INTO rec_student_addr;
172 IF c_student_addr%NOTFOUND THEN
173 CLOSE c_student_addr;
174 p_validate := FALSE;
175 ELSE
176 CLOSE c_student_addr;
177 END IF;
178 ELSE
179 CLOSE c_student_addr_prim;
180 END IF;
181 --
182 -- copy the address
183 --
184
185 IF (p_validate) THEN
186 l_party_site_id := rec_student_addr.party_site_id;
187
188 IGS_PE_PERSON_ADDR_PKG.INSERT_ROW(
189 p_action =>'INSERT',
190 p_rowid => l_rowid,
191 p_location_id => l_location_id,
192 p_start_dt => rec_student_addr.START_DT,
193 p_end_dt => rec_student_addr.END_DT,
194 p_country => rec_student_addr.COUNTRY_CD,
195 p_address_style => rec_student_addr.ADDRESS_STYLE,
196 p_addr_line_1 => rec_student_addr.ADDR_LINE_1,
197 p_addr_line_2 => rec_student_addr.ADDR_LINE_2,
198 p_addr_line_3 => rec_student_addr.ADDR_LINE_3,
199 p_addr_line_4 => rec_student_addr.ADDR_LINE_4,
200 p_date_last_verified => NULL,
201 p_correspondence => rec_student_addr.CORRESPONDENCE,
202 p_city => rec_student_addr.CITY,
203 p_state => rec_student_addr.STATE,
204 p_province => rec_student_addr.PROVINCE,
205 p_county => rec_student_addr.COUNTY,
206 p_postal_code => rec_student_addr.POSTAL_CODE,
207 p_address_lines_phonetic => rec_student_addr.address_lines_phonetic,
208 p_delivery_point_code => rec_student_addr.delivery_point_code,
209 p_other_details_1 => NULL,
210 p_other_details_2 => NULL,
211 p_other_details_3 => NULL,
212 l_return_status => l_return_status ,
213 l_msg_data => l_msg_data,
214 p_party_id => p_object_id,
215 p_party_site_id => l_party_site_id,
216 p_party_type => 'PERSON',
217 p_last_update_date => l_last_update_date ,
218 p_party_site_ovn => l_party_site_ovn,
219 p_location_ovn => l_location_ovn,
220 p_status => rec_student_addr.status
221 );
222
223 IF l_return_status IN ('E','U') THEN
224 -- ssawhney bug 2338473
225 IF l_msg_count > 1 THEN
226 FOR i IN 1..l_msg_count LOOP
227 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
228 tmp_var1 := tmp_var1 || ' '|| tmp_var;
229 END LOOP;
230 l_msg_data := tmp_var1;
231 END IF;
232 RETURN;
233 END IF;
234 -- create the usage but before that initialise the variables
235 --
236 l_return_status := NULL;
237 l_msg_count := 0;
238 l_msg_data := NULL;
239 l_last_update_date := NULL;
240 l_rowid := NULL;
241 l_object_version_number := NULL;
242
243 IGS_PE_PARTY_SITE_USE_PKG.HZ_PARTY_SITE_USES_AK(
244 p_action => 'INSERT',
245 p_rowid => l_rowid,
246 p_party_site_use_id => l_party_site_use_id,
247 p_party_site_id => l_party_site_id,
248 p_site_use_type => l_profile_addr_usage,
249 p_return_status => l_return_status,
250 p_msg_data => l_msg_data,
251 p_last_update_date => l_last_update_date,
252 p_site_use_last_update_date => l_site_last_update_date,
253 p_profile_last_update_date => l_profile_last_update_date,
254 p_status => 'A',
255 P_HZ_PARTY_SITE_USE_OVN => l_object_version_number
256 );
257
258 IF l_return_status IN ('E','U') THEN
259 -- ssawhney bug 2338473
260 IF l_msg_count > 1 THEN
261 FOR i IN 1..l_msg_count LOOP
262 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
263 tmp_var1 := tmp_var1 || ' '|| tmp_var;
264 END LOOP;
265 l_msg_data := tmp_var1;
266 END IF;
267 RETURN;
268 END IF;
269
270 END IF; -- Address Exists p_validate = TRUE
271
272 END IF; -- end of l_student_age < l_student_age_bar
273
274 END copy_address_and_usage;
275
276 /*
277 WHO WHEN WHAT
278 skpandey 31-AUG-2005 Bug: 4582514
279 Description: : Call api (INSERT_ROW/ADD_ROW) only when p_caller is not 'ORGANIZATION'; and pass 'F' for x_directional_flag parameter in ADD_ROW
280 */
281
282
283 PROCEDURE CREATUPDATE_PARTY_RELATIONSHIP(
284 p_action IN VARCHAR2 ,
285 p_subject_id IN NUMBER ,
286 p_object_id IN NUMBER ,
287 p_party_relationship_type IN VARCHAR2 ,
288 p_relationship_code IN VARCHAR2,
289 p_comments IN VARCHAR2 ,
290 p_start_date IN DATE,
291 p_end_date IN DATE,
292 p_last_update_date IN OUT NOCOPY DATE ,
293 p_return_status OUT NOCOPY VARCHAR2 ,
294 p_msg_count OUT NOCOPY NUMBER ,
295 p_msg_data OUT NOCOPY VARCHAR2 ,
296 p_party_relationship_id IN OUT NOCOPY VARCHAR2 ,
297 p_party_id OUT NOCOPY NUMBER ,
298 p_party_number OUT NOCOPY VARCHAR2,
299 p_caller IN VARCHAR2 ,
300 P_Object_Version_Number IN OUT NOCOPY NUMBER,
301 P_Primary IN VARCHAR2 ,
302 P_Secondary IN VARCHAR2,
303 P_Joint_Salutation IN VARCHAR2,
304 P_Next_To_Kin IN VARCHAR2 ,
305 P_Rep_Faculty IN VARCHAR2,
306 P_Rep_Staff IN VARCHAR2,
307 P_Rep_Student IN VARCHAR2,
308 P_Rep_Alumni IN VARCHAR2,
309 p_directional_flag IN VARCHAR2,
310 p_emergency_contact_flag IN VARCHAR2
311 ) AS
312
313 CURSOR get_party_rel_type_cur(cp_relationship_type hz_relationship_types.relationship_type%type) IS
314 SELECT 'X'
315 FROM hz_relationship_types
316 WHERE RELATIONSHIP_TYPE = cp_relationship_type AND
317 SUBJECT_TYPE= 'PERSON' AND
318 OBJECT_TYPE='PERSON';
319
320 l_party_rel_type VARCHAR2(1);
321 -- lv_party_relationship_id NUMBER ;
322 prel_rec hz_relationship_v2pub.relationship_rec_type;
323
324 -- we dont want the party of the relation to be updated.
325
326 p_party_rec hz_party_v2pub.party_rec_type;
327 tmp_var VARCHAR2(2000);
328 tmp_var1 VARCHAR2(2000);
329
330 l_rowid VARCHAR2(25);
331
332 x_party_object_version_number NUMBER := NULL;
333 l_result BOOLEAN := TRUE;
334
335 PROCEDURE Validate_Dml(p_start_date IN DATE,p_end_date IN DATE , p_subject_id IN NUMBER,p_Dml VARCHAR2)
336 AS
337 /*
338 WHO WHEN WHAT
339 pkpatel 11-JUN-2003 Bug 3002425
340 Modified the cursor for Date overlap. Passed the p_subject_id and removed the join with directional flag
341 asbala 25-JUN-2003 Bug 3021520
342 Removed p_Dml = 'AFTER' condition from Validate_Dml
343 */
344 BEGIN
345 IF p_DML = 'BEFORE' THEN
346 DECLARE
347 CURSOR per_birth_dt IS
348 SELECT birth_date
349 FROM IGS_PE_PERSON_BASE_V
350 WHERE person_id = p_subject_id;
351
352 l_birth_date IGS_PE_PERSON_BASE_V.BIRTH_DATE%TYPE;
353 BEGIN
354 IF p_start_date > sysdate THEN
355 fnd_message.set_name ('IGS', 'IGS_AD_ST_DT_LT_SYS_DT');
356 igs_ge_msg_stack.add;
357 app_exception.raise_exception;
358 END IF;
359 IF p_end_date IS NOT NULL THEN
360 IF p_start_date > p_end_date THEN
361 fnd_message.set_name ('IGS', 'IGS_FI_ST_DT_LE_END_DT');
362 igs_ge_msg_stack.add;
363 app_exception.raise_exception;
364 END IF ;
365 END IF;
366 OPEN per_birth_dt;
367 FETCH per_birth_dt INTO l_birth_date;
368 CLOSE per_birth_dt;
369 IF l_birth_date IS NOT NULL AND p_start_date < l_birth_date THEN
370 fnd_message.set_name ('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374 END;
375 END IF;
376
377 END Validate_Dml;
378
379 BEGIN
380 IF p_start_date IS NOT NULL THEN
381 prel_rec.start_date := p_start_date ;
382 ELSE
383 prel_rec.start_date := sysdate;
384 END IF;
385
386 prel_rec.comments := p_comments ;
387 prel_rec.end_date := p_end_date ;
388
389 Validate_Dml(
390 p_start_date => prel_rec.start_Date,
391 p_end_date => prel_rec.end_date,
392 p_subject_id => p_subject_id,
393 p_Dml => 'BEFORE' );
394
395 IF p_action = 'INSERT' THEN
396 prel_rec.subject_id := p_subject_id;
397 prel_rec.object_id := p_object_id ;
398 prel_rec.relationship_type := p_party_relationship_type ;
399 prel_rec.relationship_code := p_relationship_code;
400
401 -- Relationships can be created between PERSON and PERSON as well as
402 -- PERSON and ORGANIZATION. SWS105 Person Rel Enhancement Bug 2613718
403
404 prel_rec.subject_type := 'PERSON';
405
406 select party_type into prel_rec.object_type from hz_parties where party_id = p_object_id;
407
408 prel_rec.subject_table_name := 'HZ_PARTIES';
409 prel_rec.object_table_name := 'HZ_PARTIES';
410 prel_rec.content_source_type := 'USER_ENTERED';
411 prel_rec.created_by_module := 'IGS';
412
413 -- Generating And Passing the party number if the profile value is set to 'No' for generating the party number
414 IF FND_PROFILE.VALUE('HZ_GENERATE_PARTY_NUMBER') = 'N' THEN
415 SELECT hz_party_number_s.nextval INTO p_party_rec.party_number FROM dual;
416 prel_rec.party_rec := p_party_rec;
417 END IF;
418
419
420
421 HZ_RELATIONSHIP_V2PUB.create_relationship (
422 p_relationship_rec => prel_rec,
423 x_relationship_id => p_party_relationship_id,
424 x_party_id => p_party_id,
425 x_party_number => p_party_number,
426 x_return_status => p_return_status,
427 x_msg_count => p_msg_count,
428 x_msg_data => p_msg_data
429 );
430
431 IF p_return_status IN ('E','U') THEN
432 -- ssawhney bug 2338473
433 IF p_msg_count > 1 THEN
434 FOR i IN 1..p_msg_count LOOP
435 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
436 tmp_var1 := tmp_var1 || ' '|| tmp_var;
437 END LOOP;
438 p_msg_data := tmp_var1;
439 END IF;
440 RETURN;
441 END IF;
442
443 -- After successful creation of hz relationship record putting 1 in object version number
444 p_object_version_number := 1;
445 OPEN get_party_rel_type_cur(p_party_relationship_type);
446 FETCH get_party_rel_type_cur INTO l_party_rel_type;
447 CLOSE get_party_rel_type_cur;
448 IF l_party_rel_type = 'X' THEN
449 -- fix for bug 5254202
450 l_result := Igs_Pe_Person_PKG.Get_PK_For_Validation(p_object_id);
451
452 IGS_PE_HZ_REL_PKG.INSERT_ROW(
453 x_rowid =>l_rowid ,
454 x_relationship_id =>p_party_relationship_id,
455 x_directional_flag =>'F',
456 x_primary =>p_primary,
457 x_secondary =>p_secondary,
458 x_joint_salutation =>p_joint_salutation,
459 x_next_to_kin =>p_next_to_kin,
460 x_rep_faculty =>p_rep_faculty,
461 x_rep_staff =>p_rep_staff,
462 x_rep_student =>p_rep_student,
463 x_rep_alumni =>p_rep_alumni,
464 x_emergency_contact_flag =>p_emergency_contact_flag);
465 END IF;
466
467 -- get the out NOCOPY parameter for the last updated date.
468 -- this cursor may return 2 records with the V2 APIs structure and HZpatchset C
469 -- hence fetch only one record, both the records will have the same last update date and
470 -- object version number
471
472
473 ELSIF p_action = 'UPDATE' THEN
474 -- get the object_version_number , new methodology for locking in V2 apis
475 -- this cursor may return 2 records with the V2 APIs structure and HZpatchset C
476 -- hence fetch only one record, both the records will have the same last update date and
477 -- object version number
478
479 -- kumma. V2 API logic has been modified, and if a field which had data is made NULL
480 -- then we have to explicitly pass it as G_MISS_CHAR.
481 -- cross checked with API coding standards also. bug number 2314209
482
483 IF p_comments IS NULL THEN
484 prel_rec.comments := FND_API.G_MISS_CHAR;
485 END IF;
486 -- skpandey. If end date field which had data is made NULL
487 -- then we have to explicitly pass it as G_MISS_DATE.
488 IF p_end_date IS NULL THEN
489 prel_rec.end_date := FND_API.G_MISS_DATE;
490 END IF;
491
492 prel_rec.relationship_id := p_party_relationship_id ;
493
494 HZ_RELATIONSHIP_V2PUB.update_relationship (
495 p_init_msg_list => null,
496 p_relationship_rec => prel_rec,
497 p_object_version_number => p_object_version_number,
498 p_party_object_version_number => x_party_object_version_number ,
499 x_return_status => p_return_status,
500 x_msg_count => p_msg_count,
501 x_msg_data => p_msg_data );
502
503 IF p_return_status IN ('E','U') THEN
504
505 -- ssawhney bug 2338473
506 IF p_msg_count > 1 THEN
507 FOR i IN 1..p_msg_count LOOP
508 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
509 tmp_var1 := tmp_var1 || ' '|| tmp_var;
510 END LOOP;
511 p_msg_data := tmp_var1;
512 END IF;
513 RETURN;
514 END IF;
515 -- Added a condition that if p_caller is not ORGANIZATION. Bug#4582514
516 OPEN get_party_rel_type_cur(p_party_relationship_type);
517 FETCH get_party_rel_type_cur INTO l_party_rel_type;
518 CLOSE get_party_rel_type_cur;
519 IF l_party_rel_type = 'X' THEN
520 IGS_PE_HZ_REL_PKG.ADD_ROW(
521 x_rowid =>l_rowid ,
522 x_relationship_id =>p_party_relationship_id,
523 x_directional_flag =>'F',
524 x_primary =>p_primary,
525 x_secondary =>p_secondary,
526 x_joint_salutation =>p_joint_salutation,
527 x_next_to_kin =>p_next_to_kin,
528 x_rep_faculty =>p_rep_faculty,
529 x_rep_staff =>p_rep_staff,
530 x_rep_student =>p_rep_student,
531 x_rep_alumni =>p_rep_alumni,
532 x_emergency_contact_flag =>p_emergency_contact_flag);
533 END IF;
534 END IF;
535
536
537 END creatupdate_party_relationship ;
538
539 END igs_pe_relationships_pkg;
[Home] [Help]