1 PACKAGE BODY IGS_OR_GEN_001 AS
2 /* $Header: IGSOR01B.pls 120.2 2005/09/27 06:55:36 appldev ship $ */
3
4 /*
5 || Created By : pkpatel
6 || Created On : 10-DEC-2001
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 || pkpatel 12-MAY-2002 Bug No: 2266315
13 || Added the Procedure update_org. Modified orgp_upd_ins_ou_sts AND
14 || orgp_upd_ou_sts to propagate the STATUS through Organizational Structure.
15 || ssawhney 11-jun-2002 BUG : 2408794
16 || ORGP_GET_WITHIN_OU, c_our cursor added check for LOGICAL_DELETE_DT IS NULL;
17 || pkpatel 25-OCT-2002 Bug No: 2613704
18 || Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
19 || pkpatel 2-DEC-2002 Bug No: 2599109
20 || Added column birth_city, birth_country in the call to TBH igs_pe_hz_parties_pkg
21 || ssawhney 30-apr-2003 V2API OVN implementation, change to call to IGS_OR_GEN_012
22 || gmaheswa 15-sep-2003 changed orgp_get_local_inst to get local active institution from the profile.
23 || Bug No: 2863933
24 || mmkumar 18-Jul-2005 Party_Number impact, inside update_org , modified cursor hz_parties_cur,
25 || igs_or_unit_hist_pkg.insert_row call and call to igs_pe_hz_parties_pkg.update_row
26 */
27
28 PROCEDURE orgp_del_instn_hist(
29 p_institution_cd IN VARCHAR2 )
30 IS
31 v_other_detail VARCHAR(255);
32 BEGIN
33 DELETE
34 FROM IGS_OR_INST_HIST
35 WHERE institution_cd = p_institution_cd;
36 EXCEPTION
37 WHEN OTHERS THEN
38 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception ;
41 END orgp_del_instn_hist;
42
43
44 PROCEDURE orgp_del_ou_hist(
45 p_org_unit_cd IN VARCHAR2 ,
46 p_start_dt IN DATE )
47 IS
48 v_other_detail VARCHAR(255);
49 BEGIN
50 DELETE
51 FROM IGS_OR_UNIT_HIST
52 WHERE org_unit_cd = p_org_unit_cd
53 AND ou_start_dt = p_start_dt;
54 EXCEPTION
55 WHEN OTHERS THEN
56 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
57 IGS_GE_MSG_STACK.ADD;
58 App_Exception.Raise_Exception ;
59 END orgp_del_ou_hist;
60
61
62 FUNCTION orgp_get_local_inst
63 RETURN VARCHAR2 IS
64 BEGIN -- orgp_get_local_inst
65 -- This module retrieves local active IGS_OR_INSTITUTION from profile value.
66 DECLARE
67
68 v_institution_cd IGS_OR_INSTITUTION.institution_cd%TYPE;
69 BEGIN
70 v_institution_cd := NULL;
71 v_institution_cd := FND_PROFILE.VALUE('IGS_OR_LOCAL_INST');
72 RETURN v_institution_cd;
73 END;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
78 IGS_GE_MSG_STACK.ADD;
79 App_Exception.Raise_Exception ;
80
81 END orgp_get_local_inst;
82
83
84 FUNCTION orgp_get_s_loc_type(
85 p_location_cd IGS_AD_LOCATION_ALL.location_cd%TYPE )
86 RETURN VARCHAR2 IS
87 gv_other_detail VARCHAR2(255);
88 BEGIN -- orgp_get_s_loc_type
89 -- This module fetches the value for s_location_type for a IGS_AD_LOCATION
90 -- from the IGS_AD_LOCATION_TYPE table.
91 DECLARE
92 CURSOR c_lot IS
93 SELECT lot.s_location_type
94 FROM IGS_AD_LOCATION_TYPE lot,
95 IGS_AD_LOCATION loc
96 WHERE lot.location_type = loc.location_type AND
97 loc.location_cd = p_location_cd;
98 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
99 BEGIN
100 -- Set the default value
101 v_s_location_type := NULL;
102 OPEN c_lot;
103 FETCH c_lot INTO v_s_location_type;
104 IF (c_lot%FOUND) THEN
105 CLOSE c_lot;
106 RETURN v_s_location_type;
107 END IF;
108 CLOSE c_lot;
109 RETURN v_s_location_type;
110 END;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception ;
117
118 END orgp_get_s_loc_type;
119
120
121 FUNCTION orgp_get_s_loc_type2(
122 p_location_type IGS_AD_LOCATION_ALL.location_type%TYPE )
123 RETURN VARCHAR2 IS
124 gv_other_detail VARCHAR2(255);
125 BEGIN -- orgp_get_s_loc_type2
126 -- This module fetches the value for s_location_type for a
127 -- IGS_AD_LOCATION?s IGS_AD_LOCATION type from the IGS_AD_LOCATION_TYPE table.
128 -- It is similar to ORGP_GET_S_LOC_TYPE except that it fetches the record
129 -- using the IGS_AD_LOCATION type instead of the IGS_AD_LOCATION code as a parameter.
130 DECLARE
131 CURSOR c_lot IS
132 SELECT s_location_type
133 FROM IGS_AD_LOCATION_TYPE
134 WHERE location_type = p_location_type;
135 v_s_location_type IGS_AD_LOCATION_TYPE.s_location_type%TYPE;
136 BEGIN
137 -- Set the default value
138 v_s_location_type := NULL;
139 OPEN c_lot;
140 FETCH c_lot INTO v_s_location_type;
141 IF (c_lot%FOUND) THEN
142 CLOSE c_lot;
143 RETURN v_s_location_type;
144 END IF;
145 CLOSE c_lot;
146 RETURN v_s_location_type;
147 END;
148 EXCEPTION
149 WHEN OTHERS THEN
150 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception ;
153 END orgp_get_s_loc_type2;
154
155
156 FUNCTION ORGP_GET_WITHIN_OU(
157 p_parent_org_unit_cd IN VARCHAR2 ,
158 p_parent_start_dt IN DATE ,
159 p_child_org_unit_cd IN VARCHAR2 ,
160 p_child_start_dt IN DATE ,
161 p_direct_match_ind IN VARCHAR2)
162 RETURN VARCHAR2 IS
163 gv_other_detail VARCHAR2(255);
164 BEGIN
165 DECLARE
166 FUNCTION orgpl_get_superiors(
167 p_child_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE,
168 p_child_start_dt IN IGS_OR_UNIT.start_dt%TYPE)
169 RETURN VARCHAR2
170 IS
171 BEGIN
172 DECLARE
173 CURSOR c_our (
174 cp_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
175 cp_start_dt IGS_OR_UNIT.start_dt%TYPE) IS
176 SELECT our.parent_org_unit_cd,
177 our.parent_start_dt
178 FROM IGS_OR_UNIT_REL our
179 WHERE our.child_org_unit_cd = cp_org_unit_cd and
180 our.child_start_dt = cp_start_dt and
181 our.logical_delete_dt IS NULL ; -- new validation as part of bug 2408794
182 BEGIN
183 -- If records found
184 FOR v_our_row IN c_our(
185 p_child_org_unit_cd,
186 p_child_start_dt) LOOP
187 IF (v_our_row.parent_org_unit_cd = p_parent_org_unit_cd) AND
188 (v_our_row.parent_start_dt = p_parent_start_dt) THEN
189 -- Direct match
190 RETURN 'Y';
191 ELSIF p_direct_match_ind = 'N' THEN
192 -- Recursive - call function again, passing the parent
193 -- as the new child.
194 IF (orgpl_get_superiors(
195 v_our_row.parent_org_unit_cd,
196 v_our_row.parent_start_dt) = 'Y') THEN
197 RETURN 'Y';
198 END IF;
199 END IF;
200 END LOOP;
201 RETURN 'N';
202 END;
203 END orgpl_get_superiors;
204 BEGIN
205 -- This module determines whether the nominated organisational IGS_PS_UNIT is within
206 -- the nominated superior OU. A recursive search is done of all superior org
207 -- IGS_PS_UNIT relationships searching for a match with the superior anywhere in the
208 -- parent tree.
209 -- If p_direct_match_ind is set the the return will only return Y if the
210 -- superior org IGS_PS_UNIT is found as an immediate superior.
211 IF (orgpl_get_superiors(
212 p_child_org_unit_cd,
213 p_child_start_dt) = 'N') THEN
214 RETURN 'N';
215 ELSE
216 RETURN 'Y';
217 END IF;
218 END;
219 END orgp_get_within_ou;
220
221 PROCEDURE orgp_ins_ou_hist(
222 p_org_unit_cd IN VARCHAR2 ,
223 p_ou_start_dt IN DATE ,
224 p_hist_start_dt IN DATE ,
225 p_hist_end_dt IN DATE ,
226 p_hist_who IN VARCHAR2 ,
227 p_ou_end_dt IN DATE ,
228 p_description IN VARCHAR2 ,
229 p_org_status IN VARCHAR2 ,
230 p_org_type IN VARCHAR2 ,
231 p_member_type IN VARCHAR2 ,
232 p_institution_cd IN VARCHAR2 )
233 IS
234 v_name IGS_OR_UNIT_HIST.name%TYPE;
235 v_other_detail VARCHAR(255);
236
237 BEGIN
238 -- Determine the value of the IGS_OR_UNIT_HIST.name
239 IF p_institution_cd IS NULL THEN
240 v_name := NULL;
241 ELSE
242 SELECT name
243 INTO v_name
244 FROM IGS_OR_INSTITUTION
245 WHERE institution_cd = p_institution_cd;
246 END IF;
247 -- Insert the IGS_OR_UNIT_HIST record.
248 INSERT INTO IGS_OR_UNIT_HIST
249 (org_unit_cd,
250 ou_start_dt,
251 hist_start_dt,
252 hist_end_dt,
253 hist_who,
254 ou_end_dt,
255 description,
256 org_status,
257 org_type,
258 member_type,
259 institution_cd,
260 name,
261 CREATED_BY,
262 CREATION_DATE,
263 LAST_UPDATED_BY,
264 LAST_UPDATE_DATE)
265 VALUES ( p_org_unit_cd,
266 p_ou_start_dt,
267 p_hist_start_dt,
268 p_hist_end_dt,
269 p_hist_who,
270 p_ou_end_dt,
271 p_description,
272 p_org_status,
273 p_org_type,
274 p_member_type,
275 p_institution_cd,
276 v_name,
277 1,
278 SYSDATE,
279 1,
280 SYSDATE);
281 EXCEPTION
282 WHEN OTHERS THEN
283 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception ;
286 END orgp_ins_ou_hist;
287
288
289 PROCEDURE orgp_upd_ins_ou_sts(
290 p_institution_cd IN VARCHAR2 ,
291 p_org_status VARCHAR2 ,
292 p_message_name OUT NOCOPY VARCHAR2 )
293 IS
294 /*
295 || Created By : pkpatel
296 || Created On : 10-DEC-2001
297 || Purpose : This procedure updates the INST record while status is made ACTIVE to INACTIVE
298 || Known limitations, enhancements or remarks :
299 || Change History :
300 || Who When What
301 || (reverse chronological order - newest change first)
302 */
303 v_complete BOOLEAN;
304 v_other_detail VARCHAR(255);
305 v_message_name VARCHAR2(30);
306 v_active_parent_exists BOOLEAN := FALSE;
307
308 -- Define a PL/SQL table to hold the Org Unit Code that are already processed
309 TYPE t_temp_table IS TABLE OF hz_parties.party_number%TYPE
310 INDEX BY BINARY_INTEGER;
311
312 temp_table t_temp_table;
313 l_index binary_integer := 0;
314 l_check NUMBER := 0;
315 l_count NUMBER := 0;
316
317 -- Cursor to find out NOCOPY the Active Organization Units attached with the Institution
318 CURSOR c_ou_instn
319 IS
320 SELECT IGS_OR_UNIT.org_unit_cd,
321 IGS_OR_UNIT.start_dt
322 FROM IGS_OR_UNIT,
323 IGS_OR_STATUS
324 WHERE IGS_OR_UNIT.institution_cd = p_institution_cd
325 AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
326 AND IGS_OR_STATUS.s_org_status = 'ACTIVE'
327 ORDER BY IGS_OR_UNIT.institution_cd;
328
329 CURSOR c_ou_parent (
330 cp_org_unit_cd IGS_OR_UNIT_REL.parent_org_unit_cd%TYPE,
331 cp_start_dt IGS_OR_UNIT_REL.parent_start_dt%TYPE) IS
332 SELECT parent_org_unit_cd,
333 parent_start_dt
334 FROM IGS_OR_UNIT_REL,
335 IGS_OR_UNIT,
336 IGS_OR_STATUS
337 WHERE child_org_unit_cd = cp_org_unit_cd
338 AND child_start_dt = cp_start_dt
339 AND logical_delete_dt IS NULL
340 AND org_unit_cd = parent_org_unit_cd
341 AND start_dt = parent_start_dt
342 AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
343 AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
344
345 ou_parent c_ou_parent%ROWTYPE;
346
347 -- Local function
348 FUNCTION orgp_upd_ins_ou_sts_loop (
349 p_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE,
350 p_start_dt IN IGS_OR_UNIT.start_dt%TYPE,
351 p_org_instn_cd IN IGS_OR_UNIT.institution_cd%TYPE,
352 p_institution_cd IN IGS_OR_UNIT.institution_cd%TYPE,
353 p_new_org_status IN IGS_OR_STATUS.s_org_status%TYPE,
354 p_complete OUT NOCOPY BOOLEAN)
355 RETURN BOOLEAN IS
356
357 v_update_ou BOOLEAN;
358 v_other_active_parent BOOLEAN;
359
360 -- Cursor to find all the Child Org Unit Code of the Org Units related to the Org Unit.
361 CURSOR c_our_child
362 IS
363 SELECT IGS_OR_UNIT_REL.child_org_unit_cd,
364 IGS_OR_UNIT_REL.child_start_dt,
365 IGS_OR_UNIT.institution_cd
366 FROM IGS_OR_UNIT_REL,
367 IGS_OR_UNIT,
368 IGS_OR_STATUS
369 WHERE IGS_OR_UNIT_REL.parent_org_unit_cd = p_org_unit_cd
370 AND IGS_OR_UNIT_REL.parent_start_dt = p_start_dt
371 AND IGS_OR_UNIT_REL.logical_delete_dt IS NULL
372 AND IGS_OR_UNIT.org_unit_cd = IGS_OR_UNIT_REL.child_org_unit_cd
373 AND IGS_OR_UNIT.start_dt = IGS_OR_UNIT_REL.child_start_dt
374 AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
375 AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
376
377 -- Fetch other active parents.
378 CURSOR c_our_ou_os_parent (
379 cp_parent_org_unit_cd IGS_OR_UNIT_REL.parent_org_unit_cd%TYPE,
380 cp_parent_start_dt IGS_OR_UNIT_REL.parent_start_dt%TYPE,
381 cp_child_org_unit_cd IGS_OR_UNIT_REL.child_org_unit_cd%TYPE,
382 cp_child_start_dt IGS_OR_UNIT_REL.child_start_dt%TYPE) IS
383 SELECT parent_org_unit_cd,
384 parent_start_dt
385 FROM IGS_OR_UNIT_REL,
386 IGS_OR_UNIT,
387 IGS_OR_STATUS
388 WHERE child_org_unit_cd = cp_child_org_unit_cd
389 AND child_start_dt = cp_child_start_dt
390 AND NOT (
391 parent_org_unit_cd = cp_parent_org_unit_cd AND
392 parent_start_dt = cp_parent_start_dt)
393 AND logical_delete_dt IS NULL
394 AND org_unit_cd = parent_org_unit_cd
395 AND start_dt = parent_start_dt
396 AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
397 AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
398
399
400 v_rowid VARCHAR2(25);
401
402 BEGIN
403 -- Default v_update_ou to true so that if no children exist
404 -- then possible to update the current OU.
405 v_update_ou := TRUE;
406 p_complete := TRUE;
407 v_other_active_parent := FALSE;
408
409 l_index := l_index + 1;
410 temp_table(l_index) := p_org_unit_cd;
411
412 -- have a cursor to select all active children(Org Unit attached with the Parent Org Unit)
413 FOR our_child IN c_our_child LOOP
414
415 -- Validate if child has other active parents.
416 FOR our_ou_os_parent IN c_our_ou_os_parent (
417 p_org_unit_cd,
418 p_start_dt,
419 our_child.child_org_unit_cd,
420 our_child.child_start_dt) LOOP
421 v_other_active_parent := TRUE;
422 EXIT;
423 END LOOP;
424
425 -- If child has other active parent then don't update.
426 IF v_other_active_parent = TRUE THEN
427 v_update_ou := FALSE;
428 EXIT;
429 END IF;
430
431 -- if any children found then
432 -- call orgp_upd_ins_ou_sts_loop to process children
433
434 IF orgp_upd_ins_ou_sts_loop(our_child.child_org_unit_cd,
435 our_child.child_start_dt,
436 our_child.institution_cd,
437 p_institution_cd,
438 p_new_org_status,
439 p_complete) = FALSE THEN
440
441 -- if orgp_upd_ins_ou_sts_loop returns false
442 -- indicates that the current OU is not to be updated as
443 -- it has active children.
444 -- set a flag to indicate this.
445 v_update_ou := FALSE;
446 EXIT;
447 END IF;
448
449 END LOOP;
450
451 -- check the results of processing as to whether to update current Org Unit
452
453 IF v_update_ou AND
454 (p_institution_cd = p_org_instn_cd) THEN
455 -- If no active children, and
456 -- IGS_OR_INSTITUTION codes match then update the org IGS_OR_UNIT
457 -- and return true to indicate to the calling parent
458 -- that there is no active child.
459
460 IF TRUNC(SYSDATE) >= p_start_dt THEN
461
462 -- Check whether the Org Unit is already processed. If it is already processed once
463 -- then do not update it again
464 FOR l_count IN 1..l_index LOOP
465 IF temp_table(l_count) = p_org_unit_cd THEN
466 l_check := l_check +1;
467 END IF;
468 END LOOP ;
469
470 IF l_check = 1 THEN
471 igs_or_gen_001.update_org(p_org_unit_cd,
472 p_new_org_status,
473 TRUNC(SYSDATE));
474
475 END IF;
476 l_check := 0;
477 RETURN TRUE;
478 ELSE
479 -- End date cannot be less than start date.
480 v_message_name := 'IGS_OR_CHECK_ST_END_DATES';
481 p_complete := FALSE;
482 RETURN FALSE;
483 END IF;
484 ELSE
485
486 -- If Children of the Current Org Unit has different ACTIVE parents.
487 IF p_institution_cd = p_org_instn_cd THEN
488
489 IF v_message_name = 'IGS_OR_INS_STAT_PROPOGATED' THEN
490 v_message_name := 'IGS_OR_CHK_ORG_STAT_PROPOGA';
491 END IF;
492
493 ELSE
494 -- If the Current Org Unit is also attached with a different Institution.
495 IF v_message_name = 'IGS_OR_INS_STAT_PROPOGATED' THEN
496 v_message_name := 'IGS_OR_CHK_INST_NOT_PROPOGATE';
497 END IF;
498
499 END IF;
500
501 -- Set the flag to indicate the propagation was not complete
502 p_complete := FALSE;
503
504 -- Return false to indicate to the calling parent that the child is still active.
505 RETURN FALSE;
506
507 END IF;
508 END orgp_upd_ins_ou_sts_loop; -- Local function ORGP_UPD_INS_OU_STS_LOOP
509
510 BEGIN -- ORGP_UPD_INS_OU_STS
511
512 -- default message to say complete propagation.
513 v_message_name := 'IGS_OR_INS_STAT_PROPOGATED';
514 v_complete := TRUE;
515
516 -- Select all active org units with the IGS_OR_INSTITUTION whose status has changed to inactive.
517 SAVEPOINT do_propagation;
518
519 FOR ou_instn IN c_ou_instn LOOP
520
521 OPEN c_ou_parent(ou_instn.org_unit_cd, ou_instn.start_dt);
522 FETCH c_ou_parent INTO ou_parent;
523 IF c_ou_parent%FOUND THEN
524 CLOSE c_ou_parent;
525 v_active_parent_exists := TRUE;
526 v_complete := FALSE;
527 EXIT;
528 END IF;
529 CLOSE c_ou_parent;
530
531 -- if any org units found then
532 -- call orgp_upd_ins_ou_sts_loop to process its children
533 IF orgp_upd_ins_ou_sts_loop(ou_instn.org_unit_cd,
534 ou_instn.start_dt,
535 p_institution_cd,
536 p_institution_cd,
537 p_org_status,
538 v_complete) = TRUE THEN
539 -- not interested in the value orgp_upd_ins_ou_sts_loop returns
540 -- as this value is used within the recursive function to indicate
541 -- if active child records exist.
542 NULL;
543
544 END IF;
545 IF v_complete = FALSE THEN
546 EXIT;
547 END IF;
548 END LOOP;
549
550 IF v_active_parent_exists THEN
551 v_message_name := 'IGS_OR_CHK_ORG_STAT_PROPOGA';
552 END IF;
553
554 IF v_complete = FALSE THEN
555 ROLLBACK TO do_propagation;
556 END IF;
557 p_message_name := v_message_name;
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 ROLLBACK TO do_propagation;
562 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
563 IGS_GE_MSG_STACK.ADD;
564 APP_EXCEPTION.RAISE_EXCEPTION;
565
566 END orgp_upd_ins_ou_sts;
567
568
569 PROCEDURE orgp_upd_ou_sts(
570 p_org_unit_cd IN VARCHAR2 ,
571 p_start_dt IN DATE ,
572 p_end_dt IN DATE ,
573 p_org_status IN VARCHAR2 ,
574 p_message_name OUT NOCOPY VARCHAR2 )
575 IS
576 /*
577 || Created By : pkpatel
578 || Created On : 10-DEC-2001
579 || Purpose : This procedure updates the ORG record while status is made ACTIVE to INACTIVE
580 || Known limitations, enhancements or remarks :
581 || Change History :
582 || Who When What
583 || (reverse chronological order - newest change first)
584 */
585 v_complete BOOLEAN;
586 v_message_name VARCHAR2(30);
587
588 -- Local function.
589 FUNCTION orgp_upd_ou_sts_loop (
590 p_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE,
591 p_start_dt IN IGS_OR_UNIT.start_dt%TYPE,
592 p_end_dt IN IGS_OR_UNIT.end_dt%TYPE,
593 p_complete OUT NOCOPY BOOLEAN)
594 RETURN BOOLEAN IS
595 -- Fetch active children.
596 CURSOR c_our_ou_os_child IS
597 SELECT child_org_unit_cd,
598 child_start_dt
599 FROM IGS_OR_UNIT_REL,
600 IGS_OR_UNIT,
601 IGS_OR_STATUS
602 WHERE parent_org_unit_cd = p_org_unit_cd
603 AND parent_start_dt = p_start_dt
604 AND logical_delete_dt IS NULL
605 AND org_unit_cd = child_org_unit_cd
606 AND start_dt = child_start_dt
607 AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
608 AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
609
610 -- Fetch other active parents.
611 CURSOR c_our_ou_os_parent (
612 cp_parent_org_unit_cd IGS_OR_UNIT_REL.parent_org_unit_cd%TYPE,
613 cp_parent_start_dt IGS_OR_UNIT_REL.parent_start_dt%TYPE,
614 cp_child_org_unit_cd IGS_OR_UNIT_REL.child_org_unit_cd%TYPE,
615 cp_child_start_dt IGS_OR_UNIT_REL.child_start_dt%TYPE) IS
616 SELECT parent_org_unit_cd,
617 parent_start_dt
618 FROM IGS_OR_UNIT_REL,
619 IGS_OR_UNIT,
620 IGS_OR_STATUS
621 WHERE child_org_unit_cd = cp_child_org_unit_cd
622 AND child_start_dt = cp_child_start_dt
623 AND NOT (
624 parent_org_unit_cd = cp_parent_org_unit_cd AND
625 parent_start_dt = cp_parent_start_dt)
626 AND logical_delete_dt IS NULL
627 AND org_unit_cd = parent_org_unit_cd
628 AND start_dt = parent_start_dt
629 AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status
630 AND IGS_OR_STATUS.s_org_status = 'ACTIVE';
631
632 v_update_ou BOOLEAN;
633 v_other_active_parent BOOLEAN;
634 v_other_detail VARCHAR2(255);
635 BEGIN
636 v_update_ou := TRUE;
637 v_other_active_parent := FALSE;
638
639 -- Fetch active children for the organisational IGS_OR_UNIT.
640 FOR our_ou_os_child IN c_our_ou_os_child LOOP
641
642 -- Validate if child has other active parents.
643 FOR our_ou_os_parent IN c_our_ou_os_parent (
644 p_org_unit_cd,
645 p_start_dt,
646 our_ou_os_child.child_org_unit_cd,
647 our_ou_os_child.child_start_dt) LOOP
648 v_other_active_parent := TRUE;
649 EXIT;
650 END LOOP;
651
652 -- If child has other active parent then don't update.
653 IF v_other_active_parent = TRUE THEN
654 v_update_ou := FALSE;
655 EXIT;
656 END IF;
657
658 -- Process active children for the child.
659 IF orgp_upd_ou_sts_loop (
660 our_ou_os_child.child_org_unit_cd,
661 our_ou_os_child.child_start_dt,
662 p_end_dt,
663 p_complete) = FALSE THEN
664 v_update_ou := FALSE;
665 EXIT;
666 END IF;
667 END LOOP;
668
669 IF v_update_ou = TRUE THEN
670 IF p_end_dt >= p_start_dt THEN
671
672 -- The Updation of the Most Parent Org Unit should be prevented. Th Updation this Org Unit should
673 -- happen in the Form.
674 IF g_org_unit_cd <> p_org_unit_cd THEN
675
676 igs_or_gen_001.update_org(p_org_unit_cd,
677 p_org_status,
678 p_end_dt);
679
680 END IF;
681 RETURN TRUE;
682 ELSE
683 p_message_name := 'IGS_OR_CHILD_ORG_UNIT_EXISTS';
684 p_complete := FALSE;
685 RETURN FALSE;
686 END IF;
687 ELSE
688 IF v_other_active_parent = TRUE THEN
689 p_message_name := 'IGS_OR_CHK_ORG_STAT_PROPOGA';
690 p_complete := FALSE;
691 END IF;
692 RETURN FALSE;
693 END IF;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 IGS_GE_MSG_STACK.ADD;
698 APP_EXCEPTION.RAISE_EXCEPTION;
699 END orgp_upd_ou_sts_loop;
700
701 BEGIN
702 p_message_name := 'IGS_OR_STAT_SUCCESS_PROPOGATE';
703 v_complete := TRUE;
704 SAVEPOINT do_propagation;
705
706 -- This global variable holds the Parent Org Unit.
707 g_org_unit_cd := p_org_unit_cd;
708
709 IF orgp_upd_ou_sts_loop (
710 p_org_unit_cd,
711 p_start_dt,
712 p_end_dt,
713 v_complete) = TRUE THEN
714
715 -- orgp_upd_ou_sts_loop is only concerned with
716 -- the return value when called by itself.
717 NULL;
718 END IF;
719
720 IF v_complete = FALSE THEN
721 ROLLBACK TO do_propagation;
722 END IF;
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 ROLLBACK TO do_propagation;
727 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
728 IGS_GE_MSG_STACK.ADD;
729 APP_EXCEPTION.RAISE_EXCEPTION;
730 END orgp_upd_ou_sts;
731
732 PROCEDURE update_org(p_org_unit_cd hz_parties.party_number%TYPE,
733 p_org_status igs_pe_hz_parties.ou_org_status%TYPE,
734 p_end_date igs_pe_hz_parties.ou_end_dt%TYPE)
735 IS
736 /*
737 || Created By : pkpatel
738 || Created On : 10-DEC-2001
739 || Purpose : This procedure updates the ORG record and creates its history.
740 || The TCA API is being called to refresh the last_update_date. So that the History would be created properly
741 || Known limitations, enhancements or remarks :
742 || Change History :
743 || Who When What
744 || skpandey 27-SEP-2005 Bug: 3663505
745 || Description: Added ATTRIBUTES 21 TO 24 to store additional information in IGS_OR_GEN_012_PKG call
746 || pkpatel 25-OCT-2002 Bug No: 2613704
747 || Modified signature of igs_pe_hz_parties_pkg to refer inst_priority_cd instead of inst_priority_code_id
748 || mmkumar 18-Jul-2005 Party_Number impact, modified cursor hz_parties_cur, igs_or_unit_hist_pkg.insert_row call
749 || and call to igs_pe_hz_parties_pkg.update_row
750 || (reverse chronological order - newest change first)
751 */
752
753 l_return_status VARCHAR2(1);
754 l_msg_data VARCHAR2(2000);
755 lv_rowid VARCHAR2(30);
756 l_org_id VARCHAR2(25);
757 l_hist_start_dt DATE;
758 l_hist_end_dt DATE;
759
760 CURSOR hz_parties_cur
761 IS
762 SELECT hp.*, ihp.oss_org_unit_cd
763 FROM hz_parties hp, igs_pe_hz_parties ihp
764 WHERE ihp.oss_org_unit_cd = p_org_unit_cd and
765 ihp.party_id = hp.party_id;
766
767 CURSOR igs_org_cur(cp_party_id igs_pe_hz_parties.party_id%TYPE)
768 IS
769 SELECT rowid, pe.*
770 FROM igs_pe_hz_parties pe
771 WHERE party_id = cp_party_id
772 FOR UPDATE OF ou_end_dt, ou_org_status NOWAIT;
773
774 hz_parties_rec hz_parties_cur%ROWTYPE;
775 igs_org_rec igs_org_cur%ROWTYPE;
776
777 BEGIN
778
779 OPEN hz_parties_cur;
780 FETCH hz_parties_cur INTO hz_parties_rec;
781 CLOSE hz_parties_cur;
782
783 -- The Record in HZ_PARTIES was still updated with STATUS 'A'(Active), since it was decided not to
784 -- touch the record in HZ_PARTIES since other products may also be using this TCA record.
785 -- This call is made so that the last update date is refreshed and the History would show the proper Start and End Date.
786
787 igs_or_gen_012_pkg.update_organization (
788 p_party_id => hz_parties_rec.party_id,
789 p_institution_cd => hz_parties_rec.party_number,
790 p_name => hz_parties_rec.party_name,
791 p_status => 'A',
792 p_last_update => hz_parties_rec.last_update_date,
793 p_attribute_category => hz_parties_rec.attribute_category,
794 p_attribute1 => hz_parties_rec.attribute1,
795 p_attribute2 => hz_parties_rec.attribute2,
796 p_attribute3 => hz_parties_rec.attribute3,
797 p_attribute4 => hz_parties_rec.attribute4,
798 p_attribute5 => hz_parties_rec.attribute5,
799 p_attribute6 => hz_parties_rec.attribute6,
800 p_attribute7 => hz_parties_rec.attribute7,
801 p_attribute8 => hz_parties_rec.attribute8,
802 p_attribute9 => hz_parties_rec.attribute9,
803 p_attribute10 => hz_parties_rec.attribute10,
804 p_attribute11 => hz_parties_rec.attribute11,
805 p_attribute12 => hz_parties_rec.attribute12,
806 p_attribute13 => hz_parties_rec.attribute13,
807 p_attribute14 => hz_parties_rec.attribute14,
808 p_attribute15 => hz_parties_rec.attribute15,
809 p_attribute16 => hz_parties_rec.attribute16,
810 p_attribute17 => hz_parties_rec.attribute17,
811 p_attribute18 => hz_parties_rec.attribute18,
812 p_attribute19 => hz_parties_rec.attribute19,
813 p_attribute20 => hz_parties_rec.attribute20,
814 p_return_status => l_return_status,
815 p_msg_data => l_msg_data,
816 p_object_version_number => hz_parties_rec.object_version_number,
817 p_attribute21 => hz_parties_rec.attribute21,
818 p_attribute22 => hz_parties_rec.attribute22,
819 p_attribute23 => hz_parties_rec.attribute23,
820 p_attribute24 => hz_parties_rec.attribute24
821 ) ;
822
823
824 IF l_return_status = 'S' THEN
825
826 -- To check whether the Record is locked.
827 OPEN igs_org_cur(hz_parties_rec.party_id);
828 FETCH igs_org_cur INTO igs_org_rec;
829 IF igs_org_cur%NOTFOUND THEN
830 CLOSE igs_org_cur;
831 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
832 IGS_GE_MSG_STACK.ADD;
833 APP_EXCEPTION.RAISE_EXCEPTION;
834 END IF;
835 CLOSE igs_org_cur;
836
837 igs_pe_hz_parties_pkg.update_row (
838 x_mode => 'R',
839 x_rowid => igs_org_rec.rowid,
840 x_party_id => igs_org_rec.party_id,
841 x_deceased_ind => igs_org_rec.deceased_ind,
842 x_archive_exclusion_ind => igs_org_rec.archive_exclusion_ind,
843 x_archive_dt => igs_org_rec.archive_dt,
844 x_purge_exclusion_ind => igs_org_rec.purge_exclusion_ind,
845 x_purge_dt => igs_org_rec.purge_dt,
846 x_oracle_username => igs_org_rec.oracle_username,
847 x_proof_of_ins => igs_org_rec.proof_of_ins,
848 x_proof_of_immu => igs_org_rec.proof_of_immu,
849 x_level_of_qual => igs_org_rec.level_of_qual,
850 x_military_service_reg => igs_org_rec.military_service_reg,
851 x_veteran => igs_org_rec.veteran,
852 x_institution_cd => igs_org_rec.institution_cd,
853 x_oi_local_institution_ind => igs_org_rec.oi_local_institution_ind,
854 x_oi_os_ind => igs_org_rec.oi_os_ind,
855 x_oi_govt_institution_cd => igs_org_rec.oi_govt_institution_cd,
856 x_oi_inst_control_type => igs_org_rec.oi_inst_control_type,
857 x_oi_institution_type => igs_org_rec.oi_institution_type,
858 x_oi_institution_status => igs_org_rec.oi_institution_status,
859 x_ou_start_dt => igs_org_rec.ou_start_dt,
860 x_ou_end_dt => p_end_date,
861 x_ou_member_type => igs_org_rec.ou_member_type,
862 x_ou_org_status => p_org_status,
863 x_ou_org_type => igs_org_rec.ou_org_type,
864 x_inst_org_ind => igs_org_rec.inst_org_ind,
865 x_inst_priority_cd => igs_org_rec.inst_priority_cd,
866 x_inst_eps_code => igs_org_rec.inst_eps_code,
867 x_inst_phone_country_code => igs_org_rec.inst_phone_country_code,
868 x_inst_phone_area_code => igs_org_rec.inst_phone_area_code,
869 x_inst_phone_number => igs_org_rec.inst_phone_number,
870 x_adv_studies_classes => igs_org_rec.adv_studies_classes,
871 x_honors_classes => igs_org_rec.honors_classes,
872 x_class_size => igs_org_rec.class_size,
873 x_sec_school_location_id => igs_org_rec.sec_school_location_id,
874 x_percent_plan_higher_edu => igs_org_rec.percent_plan_higher_edu,
875 x_fund_authorization => igs_org_rec.fund_authorization,
876 x_pe_info_verify_time => igs_org_rec.pe_info_verify_time,
877 x_birth_city => igs_org_rec.birth_city,
878 x_birth_country => igs_org_rec.birth_country,
879 x_oss_org_unit_cd => hz_parties_rec.oss_org_unit_cd --mmkumar , party_number impact
880
881 );
882
883 l_org_id := igs_ge_gen_003.get_org_id ;
884
885 -- 1 Second is deducted, to prevent the PK validation. The Hisory Start Date is part of PK.
886 l_hist_start_dt := hz_parties_rec.last_update_date - 1/(60*24*60);
887 l_hist_end_dt := SYSDATE;
888
889 igs_or_unit_hist_pkg.insert_row (
890 X_ROWID => lv_rowid,
891 X_ORG_UNIT_CD => hz_parties_rec.oss_org_unit_cd,
892 X_OU_START_DT => igs_org_rec.ou_start_dt,
893 X_HIST_START_DT => l_hist_start_dt,
894 X_HIST_END_DT => l_hist_end_dt,
895 X_HIST_WHO => hz_parties_rec.last_updated_by,
896 X_OU_END_DT => igs_org_rec.ou_end_dt,
897 X_DESCRIPTION => hz_parties_rec.party_name,
898 X_ORG_STATUS => igs_org_rec.ou_org_status,
899 X_ORG_TYPE => igs_org_rec.ou_org_type,
900 X_MEMBER_TYPE => igs_org_rec.ou_member_type,
901 X_INSTITUTION_CD => igs_org_rec.institution_cd,
902 X_NAME => NULL,
903 X_MODE => 'R' ,
904 X_ORG_ID => l_org_id
905 );
906 ELSE
907
908 IGS_GE_MSG_STACK.ADD;
909 APP_EXCEPTION.RAISE_EXCEPTION;
910
911 END IF;
912
913 -- Exception Section was not kept. All the Exceptions will be handled in the calling procedures and further in the FORM
914 END update_org;
915
916 END IGS_OR_GEN_001 ;
[Home] [Help]