eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_OR_GEN_001 dependencies on IGS_OR_UNIT

Line 25: || igs_or_unit_hist_pkg.insert_row call and call to igs_pe_hz_parties_pkg.update_row

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 )

Line 51: FROM IGS_OR_UNIT_HIST

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

Line 167: p_child_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE,

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

Line 168: p_child_start_dt IN IGS_OR_UNIT.start_dt%TYPE)

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

Line 174: cp_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,

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

Line 175: cp_start_dt IGS_OR_UNIT.start_dt%TYPE) 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

Line 178: FROM IGS_OR_UNIT_REL 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

Line 234: v_name IGS_OR_UNIT_HIST.name%TYPE;

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

Line 238: -- Determine the value of the IGS_OR_UNIT_HIST.name

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

Line 247: -- Insert the IGS_OR_UNIT_HIST record.

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,

Line 248: INSERT INTO IGS_OR_UNIT_HIST

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,

Line 320: SELECT IGS_OR_UNIT.org_unit_cd,

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

Line 321: IGS_OR_UNIT.start_dt

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

Line 322: FROM IGS_OR_UNIT,

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'

Line 324: WHERE IGS_OR_UNIT.institution_cd = p_institution_cd

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:

Line 325: AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status

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 (

Line 327: ORDER BY IGS_OR_UNIT.institution_cd;

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

Line 330: cp_org_unit_cd IGS_OR_UNIT_REL.parent_org_unit_cd%TYPE,

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,

Line 331: cp_start_dt IGS_OR_UNIT_REL.parent_start_dt%TYPE) IS

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,

Line 334: FROM IGS_OR_UNIT_REL,

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

Line 335: IGS_OR_UNIT,

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

Line 342: AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status

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:

Line 349: p_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE,

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,

Line 350: p_start_dt IN IGS_OR_UNIT.start_dt%TYPE,

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)

Line 351: p_org_instn_cd IN IGS_OR_UNIT.institution_cd%TYPE,

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

Line 352: p_institution_cd IN IGS_OR_UNIT.institution_cd%TYPE,

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:

Line 363: SELECT IGS_OR_UNIT_REL.child_org_unit_cd,

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,

Line 364: IGS_OR_UNIT_REL.child_start_dt,

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

Line 365: IGS_OR_UNIT.institution_cd

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

Line 366: FROM IGS_OR_UNIT_REL,

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

Line 367: IGS_OR_UNIT,

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

Line 369: WHERE IGS_OR_UNIT_REL.parent_org_unit_cd = p_org_unit_cd

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

Line 370: AND IGS_OR_UNIT_REL.parent_start_dt = p_start_dt

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

Line 371: AND IGS_OR_UNIT_REL.logical_delete_dt IS NULL

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';

Line 372: AND IGS_OR_UNIT.org_unit_cd = IGS_OR_UNIT_REL.child_org_unit_cd

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:

Line 373: AND IGS_OR_UNIT.start_dt = IGS_OR_UNIT_REL.child_start_dt

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.

Line 374: AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status

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 (

Line 379: cp_parent_org_unit_cd IGS_OR_UNIT_REL.parent_org_unit_cd%TYPE,

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,

Line 380: cp_parent_start_dt IGS_OR_UNIT_REL.parent_start_dt%TYPE,

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

Line 381: cp_child_org_unit_cd IGS_OR_UNIT_REL.child_org_unit_cd%TYPE,

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,

Line 382: cp_child_start_dt IGS_OR_UNIT_REL.child_start_dt%TYPE) IS

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,

Line 385: FROM IGS_OR_UNIT_REL,

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

Line 386: IGS_OR_UNIT,

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 (

Line 396: AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status

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);

Line 456: -- IGS_OR_INSTITUTION codes match then update the org IGS_OR_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

Line 590: p_org_unit_cd IN IGS_OR_UNIT.org_unit_cd%TYPE,

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

Line 591: p_start_dt IN IGS_OR_UNIT.start_dt%TYPE,

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.

Line 592: p_end_dt IN IGS_OR_UNIT.end_dt%TYPE,

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

Line 599: FROM IGS_OR_UNIT_REL,

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

Line 600: IGS_OR_UNIT,

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

Line 607: AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status

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 (

Line 612: cp_parent_org_unit_cd IGS_OR_UNIT_REL.parent_org_unit_cd%TYPE,

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,

Line 613: cp_parent_start_dt IGS_OR_UNIT_REL.parent_start_dt%TYPE,

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

Line 614: cp_child_org_unit_cd IGS_OR_UNIT_REL.child_org_unit_cd%TYPE,

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,

Line 615: cp_child_start_dt IGS_OR_UNIT_REL.child_start_dt%TYPE) IS

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,

Line 618: FROM IGS_OR_UNIT_REL,

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

Line 619: IGS_OR_UNIT,

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 (

Line 629: AND IGS_OR_STATUS.org_status = IGS_OR_UNIT.org_status

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;

Line 639: -- Fetch active children for the organisational IGS_OR_UNIT.

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 (

Line 748: || mmkumar 18-Jul-2005 Party_Number impact, modified cursor hz_parties_cur, igs_or_unit_hist_pkg.insert_row call

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:

Line 889: igs_or_unit_hist_pkg.insert_row (

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,