[Home] [Help]
1: PACKAGE BODY IGS_FI_GET_SUAEH AS
2: /* $Header: IGSFI07B.pls 115.9 2003/05/23 06:53:57 knaraset ship $ */
3: -- Routine to save SUA effective history data in a PL/SQL TABLE.
4: -- Who When What
5: -- knaraset 29-Apr-03 Modified calls to IGS_AU_GEN_003.AUDP_GET_SUAH_COL to add uoo_id, as part of MUS build bug 2829262
10: p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
11: p_unit_cd IN IGS_PS_UNIT.unit_cd%TYPE ,
12: p_effective_dt IN DATE ,
13: p_table_index IN OUT NOCOPY BINARY_INTEGER ,
14: p_suaeh_table IN OUT NOCOPY IGS_FI_GET_SUAEH.t_suaeh_dtl)
15: AS
16: gv_other_detail VARCHAR2(255);
17: lv_param_values VARCHAR2(1080);
18: BEGIN
84: BEGIN
85: DECLARE
86: v_suah_rec r_t_suaeh_dtl;
87: BEGIN
88: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_ins_sua_rec');
89:
90: v_suah_rec.person_id := p_sua_rec.person_id;
91: v_suah_rec.course_cd := p_sua_rec.course_cd;
92: v_suah_rec.unit_cd := p_sua_rec.unit_cd;
123: p_suaeh_table(p_table_index) := v_suah_rec;
124: v_suah_rec.uoo_id := p_sua_rec.uoo_id;
125: END;
126:
127: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_ins_sua_rec');
128: EXCEPTION
129: WHEN OTHERS THEN
130: if SQLCODE <> -20001 then
131: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
128: EXCEPTION
129: WHEN OTHERS THEN
130: if SQLCODE <> -20001 then
131: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
132: Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINPL_INS_SUA_REC');
133: IGS_GE_MSG_STACK.ADD;
134: App_Exception.Raise_Exception(Null, Null, fnd_message.get);
135: else
136: RAISE;
145: DECLARE
146: v_suah_rec r_t_suaeh_dtl;
147: BEGIN
148:
149: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_ins_suah_rec');
150: v_suah_rec.person_id := p_suah_rec.person_id;
151: v_suah_rec.course_cd := p_suah_rec.course_cd;
152: v_suah_rec.unit_cd := p_suah_rec.unit_cd;
153: v_suah_rec.version_number := p_suah_rec.version_number;
308: p_table_index := gv_table_index;
309: p_suaeh_table(p_table_index) := v_suah_rec;
310: END;
311:
312: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_ins_suah_rec');
313: EXCEPTION
314: WHEN OTHERS THEN
315: if SQLCODE <> -20001 then
316: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
313: EXCEPTION
314: WHEN OTHERS THEN
315: if SQLCODE <> -20001 then
316: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
317: Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINP_INS_SUAH_REC');
318: IGS_GE_MSG_STACK.ADD;
319: App_Exception.Raise_Exception(Null, Null, fnd_message.get);
320: else
321: RAISE;
330: DECLARE
331: v_suah_rec r_t_suaeh_dtl;
332: BEGIN
333:
334: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_ins_suahv_rec');
335:
336: v_suah_rec.person_id := p_suahv_rec.person_id;
337: v_suah_rec.course_cd := p_suahv_rec.course_cd;
338: v_suah_rec.unit_cd := p_suahv_rec.unit_cd;
371: p_table_index := gv_table_index;
372: p_suaeh_table(p_table_index) := v_suah_rec;
373: END;
374:
375: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_ins_suahv_rec');
376: EXCEPTION
377: WHEN OTHERS THEN
378: if SQLCODE <> -20001 then
379: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
376: EXCEPTION
377: WHEN OTHERS THEN
378: if SQLCODE <> -20001 then
379: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
380: Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINPL_INS_SUAHV_REC');
381: IGS_GE_MSG_STACK.ADD;
382: App_Exception.Raise_Exception(Null, Null, fnd_message.get);
383: else
384: RAISE;
404: ORDER BY suahv.hist_start_dt desc;
405: r_suahv IGS_AS_SUA_H_V%ROWTYPE;
406: BEGIN
407: -- check the last enrolled history for a match
408: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_prc_sua_enrhist');
409:
410: OPEN c_suahv_last_enr(
411: p_sua_rec.course_cd,
412: p_sua_rec.unit_cd,
446: END IF;
447: END IF;
448: END;
449:
450: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_prc_sua_enrhist');
451: EXCEPTION
452: WHEN OTHERS THEN
453: if SQLCODE <> -20001 then
454: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
451: EXCEPTION
452: WHEN OTHERS THEN
453: if SQLCODE <> -20001 then
454: Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
455: Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINPL_PRC_SUA_ENRHIST');
456: IGS_GE_MSG_STACK.ADD;
457: App_Exception.Raise_Exception(Null, Null, fnd_message.get);
458: else
459: RAISE;
485: -- UNCONFIRM -> ENROLLED -> COMPLETED
486: -- UNCONFIRM -> ENROLLED -> DISCONTIN
487: -- check parameters
488:
489: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE START : IGS_FI_GET_SUAEH.finp_get_suaeh');
490:
491: IF p_person_id IS NULL OR
492: p_effective_dt IS NULL THEN
493: Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
810: END IF;
811: END LOOP;
812: END;
813:
814: --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE END : IGS_FI_GET_SUAEH.finp_get_suaeh');
815: EXCEPTION
816: WHEN OTHERS THEN
817: if SQLCODE <> -20001 then
818: FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
815: EXCEPTION
816: WHEN OTHERS THEN
817: if SQLCODE <> -20001 then
818: FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
819: FND_MESSAGE.SET_TOKEN('NAME','IGS_FI_GET_SUAEH.FINP_GET_SUAEH');
820: IGS_GE_MSG_STACK.ADD;
821: lv_param_values := to_char(p_person_id)||','||
822: p_course_cd||','||p_unit_cd||','||
823: igs_ge_date.igschardt(p_effective_dt);
828: else
829: RAISE;
830: end if;
831: END finp_get_suaeh;
832: END IGS_FI_GET_SUAEH;