[Home] [Help]
PACKAGE BODY: APPS.IGS_CO_GEN_001
Source
1 PACKAGE BODY IGS_CO_GEN_001 AS
2 /* $Header: IGSCO01B.pls 115.5 2002/11/28 23:03:31 nsidana ship $ */
3 FUNCTION CORP_GET_COR_CAT(
4 p_person_id IN NUMBER ,
5 p_course_cd IN VARCHAR2 DEFAULT null)
6 RETURN VARCHAR2 AS
7 BEGIN
8 DECLARE
9 CURSOR c_student_course_attempt(
10 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
11 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
12 SELECT CORRESPONDENCE_CAT
13 FROM IGS_EN_STDNT_PS_ATT
14 WHERE person_id = cp_person_id
15 AND course_cd = cp_course_cd;
16 v_correspondence_cat IGS_EN_STDNT_PS_ATT.CORRESPONDENCE_CAT%TYPE;
17 BEGIN
18 IF NVL(p_course_cd, ' ') = ' ' THEN
19 RETURN NULL;
20 END IF;
21 OPEN c_student_course_attempt(p_person_id,
22 p_course_cd);
23 LOOP
24 FETCH c_student_course_attempt INTO v_correspondence_cat;
25 IF (c_student_course_attempt%NOTFOUND) THEN
26 CLOSE c_student_course_attempt;
27 RETURN NULL;
28 END IF;
29 CLOSE c_student_course_attempt;
30 RETURN v_correspondence_cat;
31 END LOOP;
32 END;
33 END corp_get_cor_cat;
34 --
35 FUNCTION corp_get_let_title(
36 p_correspondence_type IN VARCHAR2 ,
37 p_letter_reference_number IN NUMBER )
38 RETURN VARCHAR2 AS
39 BEGIN -- corp_get_let_title
40 -- This module retrieves the letter IGS_PE_TITLE for a system letter
41 DECLARE
42 v_letter_title IGS_CO_S_LTR.letter_title%TYPE;
43 CURSOR c_sl IS
44 SELECT sl.letter_title
45 FROM IGS_CO_S_LTR sl
46 WHERE sl.CORRESPONDENCE_TYPE = p_correspondence_type
47 AND sl.letter_reference_number = p_letter_reference_number;
48 BEGIN
49 -- Set initial value
50 v_letter_title := NULL;
51 OPEN c_sl;
52 FETCH c_sl INTO v_letter_title;
53 CLOSE c_sl;
54 RETURN v_letter_title;
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF c_sl%ISOPEN THEN
58 CLOSE c_sl;
59 END IF;
60 RAISE;
61 END;
62 END corp_get_let_title;
63 --
64 FUNCTION corp_get_ocr_refnum(
65 p_s_other_reference_type IN VARCHAR2 ,
66 p_other_reference IN VARCHAR2 )
67 RETURN NUMBER AS
68 BEGIN --corp_get_ocr_refnum
69 --This module retrieves the reference number for an
70 --IGS_CO_OU_CO_REF record based on passed parameters
71 DECLARE
72 v_ocr_cnt NUMBER(1) DEFAULT 0;
73 v_reference_number IGS_CO_OU_CO_REF.reference_number%TYPE;
74 CURSOR c_ocr IS
75 SELECT ocr.reference_number
76 FROM IGS_CO_OU_CO_REF ocr
77 WHERE ocr.S_OTHER_REFERENCE_TYPE = p_s_other_reference_type
78 AND ocr.other_reference = p_other_reference;
79 BEGIN
80 --validate parameters
81 IF (p_s_other_reference_type IS NULL OR
82 p_other_reference IS NULL) THEN
83 RETURN NULL;
84 END IF;
85 FOR v_ocr_rec IN c_ocr LOOP
86 v_ocr_cnt := v_ocr_cnt + 1;
87 v_reference_number := v_ocr_rec.reference_number;
88 IF (v_ocr_cnt > 1) THEN
89 EXIT;
90 END IF;
91 END LOOP;
92 IF (v_ocr_cnt <> 1) THEN
93 RETURN NULL;
94 END IF;
95 RETURN v_reference_number;
96 EXCEPTION
97 WHEN OTHERS THEN
98 IF (c_ocr%ISOPEN) THEN
99 CLOSE c_ocr;
100 END IF;
101 RAISE;
102 END;
103 END corp_get_ocr_refnum;
104 --
105 PROCEDURE corp_get_ocv_details(
106 p_person_id IN OUT NOCOPY IGS_CO_OU_CO_V.person_id%TYPE ,
107 p_correspondence_type IN OUT NOCOPY IGS_CO_OU_CO_V.CORRESPONDENCE_TYPE%TYPE ,
108 p_cal_type IN OUT NOCOPY IGS_CO_OU_CO_V.CAL_TYPE%TYPE ,
109 p_ci_sequence_number IN OUT NOCOPY IGS_CO_OU_CO_V.ci_sequence_number%TYPE ,
110 p_course_cd IN OUT NOCOPY IGS_CO_OU_CO_V.course_cd%TYPE ,
111 p_cv_version_number IN OUT NOCOPY IGS_CO_OU_CO_V.cv_version_number%TYPE ,
112 p_unit_cd IN OUT NOCOPY IGS_CO_OU_CO_V.unit_cd%TYPE ,
113 p_uv_version_number IN OUT NOCOPY IGS_CO_OU_CO_V.uv_version_number%TYPE ,
114 p_s_other_reference_type IN OUT NOCOPY IGS_CO_OU_CO_V.S_OTHER_REFERENCE_TYPE%TYPE ,
115 p_other_reference IN OUT NOCOPY IGS_CO_OU_CO_V.other_reference%TYPE ,
116 p_addr_type IN OUT NOCOPY IGS_CO_OU_CO_V.ADDR_TYPE%TYPE ,
117 p_tracking_id IN OUT NOCOPY IGS_CO_OU_CO_V.tracking_id%TYPE ,
118 p_request_num IN OUT NOCOPY IGS_CO_OU_CO_V.request_num%TYPE ,
119 p_s_job_name IN OUT NOCOPY IGS_CO_OU_CO_V.s_job_name%TYPE ,
120 p_request_job_id IN OUT NOCOPY IGS_CO_OU_CO_V.request_job_id%TYPE ,
121 p_request_job_run_id IN OUT NOCOPY IGS_CO_OU_CO_V.request_job_run_id%TYPE ,
122 p_correspondence_cat OUT NOCOPY IGS_CO_OU_CO_V.CORRESPONDENCE_CAT%TYPE ,
123 p_reference_number OUT NOCOPY IGS_CO_OU_CO_V.reference_number%TYPE ,
124 p_issue_dt OUT NOCOPY IGS_CO_OU_CO_V.issue_dt%TYPE ,
125 p_dt_sent OUT NOCOPY IGS_CO_OU_CO_V.dt_sent%TYPE ,
126 p_unknown_return_dt OUT NOCOPY IGS_CO_OU_CO_V.unknown_return_dt%TYPE ,
127 p_adt_description OUT NOCOPY IGS_CO_OU_CO_V.adt_description%TYPE ,
128 p_create_dt OUT NOCOPY IGS_CO_OU_CO_V.create_dt%TYPE ,
129 p_originator_person_id OUT NOCOPY IGS_CO_OU_CO_V.originator_person_id%TYPE ,
130 p_output_num OUT NOCOPY IGS_CO_OU_CO_V.output_num%TYPE ,
131 p_oc_comments OUT NOCOPY IGS_CO_OU_CO_V.oc_comments%TYPE ,
132 p_cori_comments OUT NOCOPY IGS_CO_OU_CO_V.cori_comments%TYPE ,
133 p_message_name OUT NOCOPY varchar2)
134 AS
135 BEGIN -- corp_get_ocv_details
136 -- This module gets information from the latest record in the outgoing
137 -- correspondence view for a set of variable parameters.
138 DECLARE
139 CURSOR c_ocv IS
140 SELECT person_id,
141 CORRESPONDENCE_TYPE,
142 CAL_TYPE,
143 ci_sequence_number,
144 course_cd,
145 cv_version_number,
146 unit_cd,
147 uv_version_number,
148 S_OTHER_REFERENCE_TYPE,
149 other_reference,
150 ADDR_TYPE,
151 tracking_id,
152 request_num,
153 s_job_name,
154 request_job_id,
155 request_job_run_id,
156 CORRESPONDENCE_CAT,
157 reference_number,
158 issue_dt,
159 dt_sent,
160 unknown_return_dt,
161 adt_description,
162 create_dt,
163 originator_person_id,
164 output_num,
165 oc_comments,
166 cori_comments
167 FROM IGS_CO_OU_CO_V
168 WHERE (p_person_id IS NULL OR
169 person_id = p_person_id) AND
170 (p_correspondence_type IS NULL OR
171 CORRESPONDENCE_TYPE = p_correspondence_type) AND
172 (p_cal_type IS NULL OR
173 CAL_TYPE = p_cal_type) AND
174 (p_ci_sequence_number IS NULL OR
175 ci_sequence_number = p_ci_sequence_number) AND
176 (p_course_cd IS NULL OR
177 course_cd = p_course_cd) AND
178 (p_cv_version_number IS NULL OR
179 cv_version_number = p_cv_version_number) AND
180 (p_unit_cd IS NULL OR
181 unit_cd = p_unit_cd) AND
182 (p_uv_version_number IS NULL OR
183 uv_version_number = p_uv_version_number) AND
184 (p_s_other_reference_type IS NULL OR
185 S_OTHER_REFERENCE_TYPE = p_s_other_reference_type) AND
186 (p_other_reference IS NULL OR
187 other_reference = p_other_reference) AND
188 (p_addr_type IS NULL OR
189 ADDR_TYPE = p_addr_type) AND
190 (p_tracking_id IS NULL OR
191 tracking_id = p_tracking_id) AND
192 (p_request_num IS NULL OR
193 request_num = p_request_num) AND
194 (p_s_job_name IS NULL OR
195 s_job_name = p_s_job_name) AND
196 (p_request_job_id IS NULL OR
197 request_job_id = p_request_job_id) AND
198 (p_request_job_run_id IS NULL OR
199 request_job_run_id = p_request_job_run_id)
200 ORDER BY issue_dt DESC,
201 reference_number DESC;
202 v_ocv_rec c_ocv%ROWTYPE;
203 BEGIN
204 -- Set the default message number
205 p_message_name := Null;
206 -- Cursor handling
207 OPEN c_ocv;
208 FETCH c_ocv INTO v_ocv_rec;
209 IF c_ocv%NOTFOUND THEN
210 CLOSE c_ocv;
211 -- Set the out NOCOPY parameters to null
212 p_person_id := NULL;
213 p_correspondence_type := NULL;
214 p_cal_type := NULL;
215 p_ci_sequence_number := NULL;
216 p_course_cd := NULL;
217 p_cv_version_number := NULL;
218 p_unit_cd := NULL;
219 p_uv_version_number := NULL;
220 p_s_other_reference_type := NULL;
221 p_other_reference := NULL;
222 p_addr_type := NULL;
223 p_tracking_id := NULL;
224 p_request_num := NULL;
225 p_s_job_name := NULL;
226 p_request_job_id := NULL;
227 p_request_job_run_id := NULL;
228 p_correspondence_cat := NULL;
229 p_reference_number := NULL;
230 p_issue_dt := NULL;
231 p_dt_sent := NULL;
232 p_unknown_return_dt := NULL;
233 p_adt_description := NULL;
234 p_create_dt := NULL;
235 p_originator_person_id := NULL;
236 p_output_num := NULL;
237 p_oc_comments := NULL;
238 p_cori_comments := NULL;
239 p_message_name := 'IGS_AS_OUTGOING_CORREC_NOTFND';
240 RETURN;
241 END IF;
242 CLOSE c_ocv;
243 p_person_id := v_ocv_rec.person_id;
244 p_correspondence_type := v_ocv_rec.CORRESPONDENCE_TYPE;
245 p_cal_type := v_ocv_rec.CAL_TYPE;
246 p_ci_sequence_number := v_ocv_rec.ci_sequence_number;
247 p_course_cd := v_ocv_rec.course_cd;
248 p_cv_version_number := v_ocv_rec.cv_version_number;
249 p_unit_cd := v_ocv_rec.unit_cd;
250 p_uv_version_number := v_ocv_rec.uv_version_number;
251 p_s_other_reference_type := v_ocv_rec.S_OTHER_REFERENCE_TYPE;
252 p_other_reference := v_ocv_rec.other_reference;
253 p_addr_type := v_ocv_rec.ADDR_TYPE;
254 p_tracking_id := v_ocv_rec.tracking_id;
255 p_request_num := v_ocv_rec.request_num;
256 p_s_job_name := v_ocv_rec.s_job_name;
257 p_request_job_id := v_ocv_rec.request_job_id;
258 p_request_job_run_id := v_ocv_rec.request_job_run_id;
259 p_correspondence_cat := v_ocv_rec.CORRESPONDENCE_CAT;
260 p_reference_number := v_ocv_rec.reference_number;
261 p_issue_dt := v_ocv_rec.issue_dt;
262 p_dt_sent := v_ocv_rec.dt_sent;
263 p_unknown_return_dt := v_ocv_rec.unknown_return_dt;
264 p_adt_description := v_ocv_rec.adt_description;
265 p_create_dt := v_ocv_rec.create_dt;
266 p_originator_person_id := v_ocv_rec.originator_person_id;
267 p_output_num := v_ocv_rec.output_num;
268 p_oc_comments := v_ocv_rec.oc_comments;
269 p_cori_comments := v_ocv_rec.cori_comments;
270 RETURN;
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF c_ocv%ISOPEN THEN
274 CLOSE c_ocv;
275 END IF;
276 RAISE;
277 END;
278 END corp_get_ocv_details;
279 --
280 FUNCTION cors_get_ocv_issuedt(
281 p_person_id IN NUMBER ,
282 p_correspondence_type IN VARCHAR2 ,
283 p_cal_type IN VARCHAR2 ,
284 p_ci_sequence_number IN NUMBER ,
285 p_course_cd IN VARCHAR2 ,
286 p_cv_version_number IN NUMBER ,
287 p_unit_cd IN VARCHAR2 ,
288 p_uv_version_number IN NUMBER ,
289 p_s_other_reference_type IN VARCHAR2 ,
290 p_other_reference IN VARCHAR2 )
291 RETURN DATE AS
292 BEGIN -- cors_get_ocv_issuedt
293 -- This module gets the issue date from the latest record in the
294 -- outgoing correspondence view for a set of variable parameters.
295 DECLARE
296 -- Local variables to replace input parameters
297 v_person_id IGS_CO_OU_CO_V.person_id%TYPE;
298 v_correspondence_type IGS_CO_OU_CO_V.CORRESPONDENCE_TYPE%TYPE;
299 v_cal_type IGS_CO_OU_CO_V.CAL_TYPE%TYPE;
300 v_ci_sequence_number IGS_CO_OU_CO_V.ci_sequence_number%TYPE;
301 v_course_cd IGS_CO_OU_CO_V.course_cd%TYPE;
302 v_cv_version_number IGS_CO_OU_CO_V.cv_version_number%TYPE;
303 v_unit_cd IGS_CO_OU_CO_V.unit_cd%TYPE;
304 v_uv_version_number IGS_CO_OU_CO_V.uv_version_number%TYPE;
305 v_s_other_reference_type IGS_CO_OU_CO_V.S_OTHER_REFERENCE_TYPE%TYPE;
306 v_other_reference IGS_CO_OU_CO_V.other_reference%TYPE;
307 -- Local variables which are set to NULL
308 v_addr_type IGS_CO_OU_CO_V.ADDR_TYPE%TYPE DEFAULT NULL;
309 v_tracking_id IGS_CO_OU_CO_V.tracking_id%TYPE DEFAULT NULL;
310 v_request_num IGS_CO_OU_CO_V.request_num%TYPE DEFAULT NULL;
311 v_s_job_name IGS_CO_OU_CO_V.s_job_name%TYPE DEFAULT NULL;
312 v_request_job_id IGS_CO_OU_CO_V.request_job_id%TYPE DEFAULT NULL;
313 v_request_job_run_id IGS_CO_OU_CO_V.request_job_run_id%TYPE
314 DEFAULT NULL;
315 -- Local output variables
316 v_correspondence_cat IGS_CO_OU_CO_V.CORRESPONDENCE_CAT%TYPE;
317 v_reference_number IGS_CO_OU_CO_V.reference_number%TYPE;
318 v_issue_dt IGS_CO_OU_CO_V.issue_dt%TYPE;
319 v_dt_sent IGS_CO_OU_CO_V.dt_sent%TYPE;
320 v_unknown_return_dt IGS_CO_OU_CO_V.unknown_return_dt%TYPE;
321 v_adt_description IGS_CO_OU_CO_V.adt_description%TYPE;
322 v_create_dt IGS_CO_OU_CO_V.create_dt%TYPE;
323 v_originator_person_id IGS_CO_OU_CO_V.originator_person_id%TYPE;
324 v_output_num IGS_CO_OU_CO_V.output_num%TYPE;
325 v_oc_comments IGS_CO_OU_CO_V.oc_comments%TYPE;
326 v_cori_comments IGS_CO_OU_CO_V.cori_comments%TYPE;
327 v_message_name varchar2(30);
328 v_return BOOLEAN;
329 BEGIN
330 v_person_id := p_person_id;
331 v_correspondence_type := p_correspondence_type;
332 v_cal_type := p_cal_type;
333 v_ci_sequence_number := p_ci_sequence_number;
334 v_course_cd := p_course_cd;
335 v_cv_version_number := p_cv_version_number;
336 v_unit_cd := p_unit_cd;
337 v_uv_version_number := p_uv_version_number;
338 v_s_other_reference_type := p_s_other_reference_type;
339 v_other_reference := p_other_reference;
340 IGS_CO_GEN_001.corp_get_ocv_details(
341 v_person_id,
342 v_correspondence_type,
343 v_cal_type,
344 v_ci_sequence_number,
345 v_course_cd,
346 v_cv_version_number,
347 v_unit_cd,
348 v_uv_version_number,
349 v_s_other_reference_type,
350 v_other_reference,
351 v_addr_type,
352 v_tracking_id,
353 v_request_num,
354 v_s_job_name,
355 v_request_job_id,
356 v_request_job_run_id,
357 v_correspondence_cat,
358 v_reference_number,
359 v_issue_dt,
360 v_dt_sent,
361 v_unknown_return_dt,
362 v_adt_description,
363 v_create_dt,
364 v_originator_person_id,
365 v_output_num,
366 v_oc_comments,
367 v_cori_comments,
368 v_message_name);
369 IF v_message_name IS NOT NULL THEN
370 RETURN NULL;
371 END IF;
372 RETURN v_issue_dt;
373 END;
374 END cors_get_ocv_issuedt;
375 --
376 END IGS_CO_GEN_001;