[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_GEN_007
Source
1 PACKAGE BODY IGS_PS_GEN_007 AS
2 /* $Header: IGSPS07B.pls 120.1 2006/04/17 05:59:37 sarakshi noship $ */
3
4 FUNCTION crsp_get_rct_srct(
5 p_reference_cd_type IN IGS_GE_REF_CD_TYPE_ALL.reference_cd_type%TYPE ,
6 p_message_name OUT NOCOPY VARCHAR2)
7 RETURN VARCHAR2 AS
8 BEGIN
9 -- crsp_get_rct_srct
10 -- This module returns the system reference code type
11 DECLARE
12 v_s_reference_cd_type IGS_LOOKUPS_VIEW.lookup_code%TYPE;
13 CURSOR c_rct_srct IS
14 SELECT srct.lookup_code
15 FROM IGS_GE_REF_CD_TYPE rct,
16 IGS_LOOKUPS_VIEW srct
17 WHERE rct.reference_cd_type = p_reference_cd_type AND
18 rct.closed_ind = 'N' AND
19 srct.lookup_code = rct.s_reference_cd_type AND
20 srct.lookup_type = 'REFERENCE_CD_TYPE' AND
21 srct.closed_ind = 'N';
22 BEGIN
23 OPEN c_rct_srct;
24 FETCH c_rct_srct INTO v_s_reference_cd_type;
25 IF (c_rct_srct%NOTFOUND) THEN
26 CLOSE c_rct_srct;
27 p_message_name := 'IGS_PS_NO_OPEN_REFCDTYPE_EXIS';
28 RETURN NULL;
29 END IF;
30 CLOSE c_rct_srct;
31 p_message_name := NULL;
32 RETURN v_s_reference_cd_type;
33 EXCEPTION
34 WHEN OTHERS THEN
35 IF (c_rct_srct%ISOPEN) THEN
36 CLOSE c_rct_srct;
37 END IF;
38 App_Exception.Raise_Exception;
39 END;
40 EXCEPTION
41 WHEN OTHERS THEN
42 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
43 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_007.crsp_get_rct_srct');
44 IGS_GE_MSG_STACK.ADD;
45 App_Exception.Raise_Exception;
46 END crsp_get_rct_srct;
47
48 PROCEDURE crsp_ins_cow_hist(
49 p_course_cd IN VARCHAR2 ,
50 p_version_number IN NUMBER ,
51 p_org_unit_cd IN VARCHAR2 ,
52 p_ou_start_dt IN DATE ,
53 p_last_update_on IN DATE ,
54 p_update_on IN DATE ,
55 p_last_update_who IN VARCHAR2 ,
56 p_percentage IN NUMBER )
57 --who when what
58 --sarakshi 12-Apr-2006 Bug#3655441, modified procedure crsp_ins_cow_hist to include PLANNED status also
59 AS
60 v_s_course_status IGS_PS_STAT.s_course_status%TYPE;
61 CURSOR c_get_course_status IS
62 SELECT s_course_status
63 FROM IGS_PS_STAT, IGS_PS_VER
64 WHERE IGS_PS_VER.course_cd = p_course_cd AND
65 IGS_PS_VER.version_number = p_version_number AND
66 IGS_PS_STAT.course_status = IGS_PS_VER.course_status;
67
68 x_rowid Varchar2(25);
69 l_org_id NUMBER(15);
70 BEGIN
71 OPEN c_get_course_status;
72 FETCH c_get_course_status INTO v_s_course_status;
73 CLOSE c_get_course_status;
74 IF v_s_course_status IN ('ACTIVE','PLANNED') THEN
75
76 l_org_id := igs_ge_gen_003.get_org_id;
77
78 IGS_PS_OWN_HIST_PKG.Insert_Row(
79 X_ROWID => x_rowid,
80 X_COURSE_CD =>p_course_cd,
81 X_HIST_START_DT =>p_last_update_on,
82 X_OU_START_DT =>p_ou_start_dt,
83 X_VERSION_NUMBER =>p_version_number,
84 X_ORG_UNIT_CD =>p_org_unit_cd,
85 X_HIST_END_DT =>p_update_on,
86 X_HIST_WHO =>p_last_update_who,
87 X_PERCENTAGE =>p_percentage,
88 X_MODE =>'R',
89 X_ORG_ID =>l_org_id);
90 END IF;
91 EXCEPTION
92 WHEN OTHERS THEN
93 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
94 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_007.crsp_ins_cow_hist');
95 IGS_GE_MSG_STACK.ADD;
96 App_Exception.Raise_Exception;
97 END crsp_ins_cow_hist;
98
99 PROCEDURE crsp_ins_crc_hist(
100 p_course_cd IN VARCHAR2 ,
101 p_version_number IN NUMBER ,
102 p_reference_cd_type IN VARCHAR2 ,
103 p_reference_cd IN VARCHAR2 ,
104 p_last_update_on IN DATE ,
105 p_update_on IN DATE ,
106 p_last_update_who IN VARCHAR2 ,
107 p_description IN VARCHAR2 )
108 --who when what
109 --sarakshi 12-Apr-2006 Bug#3655441, modified procedure crsp_ins_cow_hist to include PLANNED status also
110 AS
111 CURSOR c_course_status(
112 cp_course_cd IGS_PS_VER.course_cd%TYPE,
113 cp_version_number IGS_PS_VER.version_number%TYPE) IS
114 SELECT IGS_PS_STAT.s_course_status
115 FROM IGS_PS_STAT,IGS_PS_VER
116 WHERE IGS_PS_VER.course_cd = cp_course_cd AND
117 IGS_PS_VER.version_number = cp_version_number AND
118 IGS_PS_STAT.course_status = IGS_PS_VER.course_status;
119 v_course_status IGS_PS_STAT.s_course_status%TYPE;
120 x_rowid VARCHAR2(25);
121 l_org_id NUMBER(15);
122 BEGIN
123 OPEN c_course_status(
124 p_course_cd,
125 p_version_number);
126 FETCH c_course_status INTO v_course_status;
127 CLOSE c_course_status;
128 IF(v_course_status IN ('ACTIVE','PLANNED')) THEN
129
130 l_org_id := igs_ge_gen_003.get_org_id;
131
132 IGS_PS_REF_CD_HIST_PKG.Insert_Row(
133 X_ROWID =>x_rowid,
134 X_COURSE_CD =>p_course_cd,
135 X_REFERENCE_CD_TYPE =>p_reference_cd_type,
136 X_REFERENCE_CD =>p_reference_cd,
137 X_HIST_START_DT =>p_last_update_on,
138 X_VERSION_NUMBER =>p_version_number,
139 X_HIST_END_DT =>p_update_on,
140 X_HIST_WHO =>p_last_update_who,
141 X_DESCRIPTION =>p_description,
142 X_MODE =>'R',
143 X_ORG_ID =>l_org_id);
144
145 END IF;
146 EXCEPTION
147 WHEN OTHERS THEN
148 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
149 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_007.crsp_ins_crc_hist');
150 IGS_GE_MSG_STACK.ADD;
151 App_Exception.Raise_Exception;
152 END crsp_ins_crc_hist;
153
154 PROCEDURE crsp_ins_cul_hist(
155 p_unit_cd IN VARCHAR2 ,
156 p_version_number IN NUMBER ,
157 p_course_type VARCHAR2 DEFAULT NULL,
158 p_last_update_on IN DATE ,
159 p_update_on IN DATE ,
160 p_last_update_who IN VARCHAR2 ,
161 p_unit_level IN VARCHAR2 ,
162 p_wam_weighting IN NUMBER,
163 p_course_cd VARCHAR2,
164 p_course_version_number NUMBER)
165 --who when what
166 --sarakshi 12-Apr-2006 Bug#3655441, modified procedure crsp_ins_cow_hist to include PLANNED status also
167
168 AS
169 v_s_unit_status IGS_PS_UNIT_STAT.s_unit_status%TYPE;
170 CURSOR c_get_unit_status IS
171 SELECT s_unit_status
172 FROM IGS_PS_UNIT_STAT, IGS_PS_UNIT_VER
173 WHERE IGS_PS_UNIT_VER.unit_cd = p_unit_cd AND
174 IGS_PS_UNIT_VER.version_number = p_version_number AND
175 IGS_PS_UNIT_STAT.unit_status = IGS_PS_UNIT_VER.unit_status;
176
177 x_rowid Varchar2(25);
178 l_org_id NUMBER(15);
179 BEGIN
180 OPEN c_get_unit_status;
181 FETCH c_get_unit_status INTO v_s_unit_status;
182 CLOSE c_get_unit_status;
183 IF v_s_unit_status IN ('ACTIVE','PLANNED') THEN
184
185 l_org_id := igs_ge_gen_003.get_org_id;
186
187 -- ijeddy 03-nov-2003 Bug# 3181938; Modified this object as per Summary Measurement Of Attainment TD.
188
189 IGS_PS_UNIT_LVL_HIST_PKG.Insert_Row(
190 X_ROWID => x_rowid,
191 X_UNIT_CD => p_unit_cd,
192 X_VERSION_NUMBER =>p_version_number,
193 X_HIST_START_DT =>p_last_update_on,
194 X_HIST_END_DT =>p_update_on,
195 X_HIST_WHO =>p_last_update_who,
196 X_UNIT_LEVEL =>p_unit_level,
197 X_WAM_WEIGHTING =>p_wam_weighting,
198 X_MODE =>'R',
199 X_ORG_ID =>l_org_id,
200 X_COURSE_CD =>p_course_cd,
201 X_COURSE_VERSION_NUMBER =>p_course_version_number
202 );
203
204 END IF;
205 EXCEPTION
206 WHEN OTHERS THEN
207 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
208 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_007.crsp_ins_cul_hist');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END crsp_ins_cul_hist;
212
213 END IGS_PS_GEN_007;