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.
PACKAGE BODY: APPS.IGS_PS_GEN_006

Source


1 PACKAGE BODY IGS_PS_GEN_006 AS
2 /* $Header: IGSPS06B.pls 120.4 2006/02/13 01:18:50 sommukhe ship $ */
3 --  Who                  When                             What
4 --sarakshi               30-Apr-2004                      Bug#3568858, Added parameters ovrd_wkld_val_flag, workload_val_code to crsp_ins_uv_hist .
5 --sarakshi               03-Nov-2003                      Enh#3116171,Modified the procedure crsp_ins_uv_hist to include a new parameter p_billing_credit_points
6 --sarakshi               02-Sep-2003                      Enh#3052452,removed the reference of the column sup_unit_allowed_ind and sub_unit_allowed_ind
7 -- shtatiko             03-FEB-2003                      Bug# 2550411, Modified crsp_ins_ci_uop_uoo procedure and added
8 --                                                       the procedure, log_parameters.
9 -- shtatiko             25-OCT-2002                      Added auditable_ind, audit_permission_ind and max_auditors_allowed
10 --                                                       parameters to igs_ps_unit_ver_hist_pkg.insert_row call in
11 --                                                       crsp_ins_uv_hist procedure. This has been done as part of Bug# 2636716.
12 -- jbegum               11 Sep   02                      As part of bug fix of bug #2563596
13 --                                                       removed the space present at the end of message string
14 --                                                       during the assignment of message IGS_PS_SUCCESSROLL_UOO_UAI
15 --                                                       to local variable gv_message
16 --                                                       Also replaced message name OSS_CRS_PAR_ROLL_UOP_UO_UAI with
17 --                                                       IGS_PS_PAR_ROLL_UOP_UO_UAI and message name OSS_CRS_PARROLL_UOP_UO_UAI
18 --                                                       with IGS_PS_PARROLL_UOP_UO_UAI
19 -- jbegum               18 April 02                      As part of bug fix of bug #2322290 and bug#2250784
20 --                                                       Removed the following 4 columns
21 --                                                       BILLING_CREDIT_POINTS,BILLING_HRS,FIN_AID_CP,FIN_AID_HRS
22 --                                                       from crsp_ins_uv_hist procedure.
23 -- rgangara 03-May-2001 modified by adding 2 more parameters as per DLD Unit Section Enrollment Info.
24 
25 PROCEDURE log_parameters ( p_c_param_name    VARCHAR2 ,
26                            p_c_param_value   VARCHAR2
27                          ) IS
28 /***********************************************************************************************
29 
30   Created By     :  SHTATIKO
31   Date Created By:  31-JAN-2003
32 
33   Purpose        :  To log the parameters. This has been added as part of Bug Fix 2550411.
34 
35   Known limitations,enhancements,remarks:
36   Change History
37   Who     When       What
38   smvk   09-Jul-2004 Bug # 3676145. Modified the cursor c_ucl to select active (not closed) unit classes.
39 ********************************************************************************************** */
40 BEGIN
41   fnd_message.set_name('IGS','IGS_PS_DEL_PRIORITY_LOG');
42   fnd_message.set_token('PARAMETER_NAME', p_c_param_name );
43   fnd_message.set_token('PARAMETER_VAL' , p_c_param_value ) ;
44   fnd_file.put_line(fnd_file.LOG,FND_MESSAGE.GET);
45 END log_parameters  ;
46 
47 
48 FUNCTION crsp_get_ucl_mode(
49   p_unit_class IN VARCHAR2 )
50 RETURN VARCHAR2 AS
51         CURSOR c_ucl (cp_unit_class     IGS_AS_UNIT_CLASS.unit_class%TYPE) IS
52         SELECT  unit_mode
53         FROM    IGS_AS_UNIT_CLASS
54         WHERE unit_class = cp_unit_class
55 	AND   closed_ind = 'N';
56         v_unit_mode     IGS_AS_UNIT_CLASS.unit_mode%TYPE;
57 BEGIN
58         IF p_unit_class IS NULL THEN
59                 RETURN NULL;
60         ELSE
61                 OPEN c_ucl (p_unit_class);
62                 FETCH c_ucl INTO v_unit_mode;
63                 IF c_ucl%NOTFOUND THEN
64                         CLOSE c_ucl;
65                         RETURN NULL;
66                 ELSE
67                         CLOSE c_ucl;
68                         RETURN v_unit_mode;
69                 END IF;
70         END IF;
71 END crsp_get_ucl_mode;
72 
73 FUNCTION crsp_get_uoo_id(
74   p_unit_cd IN VARCHAR2 ,
75   p_version_number IN NUMBER ,
76   p_cal_type IN VARCHAR2 ,
77   p_ci_sequence_number IN NUMBER ,
78   p_location_cd IN VARCHAR2 ,
79   p_unit_class IN VARCHAR2 )
80 RETURN NUMBER AS
81 BEGIN
82 DECLARE
83         v_uoo_id                igs_ps_unit_ofr_opt.uoo_id%TYPE;
84         -- this cursor used when primary key is passed
85         CURSOR  c_uoo IS
86                 SELECT  IGS_PS_UNIT_OFR_OPT.uoo_id
87                 FROM    IGS_PS_UNIT_OFR_OPT
88                 WHERE   IGS_PS_UNIT_OFR_OPT.unit_cd = p_unit_cd AND
89                         IGS_PS_UNIT_OFR_OPT.version_number = p_version_number AND
90                         IGS_PS_UNIT_OFR_OPT.cal_type = p_cal_type AND
91                         IGS_PS_UNIT_OFR_OPT.ci_sequence_number = p_ci_sequence_number AND
92                         IGS_PS_UNIT_OFR_OPT.location_cd = p_location_cd AND
93                         IGS_PS_UNIT_OFR_OPT.unit_class = p_unit_class;
94 BEGIN
95         -- This module returns the IGS_PS_UNIT offering option ID for the specified
96         -- IGS_PS_UNIT offering option.
97         OPEN c_uoo;
98         FETCH c_uoo INTO v_uoo_id;
99         IF c_uoo%NOTFOUND THEN
100                 CLOSE c_uoo;
101                 RETURN NULL;
102         END IF;
103         CLOSE c_uoo;
104         RETURN v_uoo_id;
105 END;
106 END crsp_get_uoo_id;
107 
108 PROCEDURE crsp_get_uoo_key(
109   p_unit_cd IN OUT NOCOPY VARCHAR2 ,
110   p_version_number IN OUT NOCOPY NUMBER ,
111   p_cal_type IN OUT NOCOPY VARCHAR2 ,
112   p_ci_sequence_number IN OUT NOCOPY NUMBER ,
113   p_location_cd IN OUT NOCOPY VARCHAR2 ,
114   p_unit_class IN OUT NOCOPY VARCHAR2 ,
115   p_uoo_id IN OUT NOCOPY NUMBER )
116 AS
117 BEGIN
118 DECLARE
119         lv_param_values                 VARCHAR2(1080);
120         gv_unit_offering_option_rec     IGS_PS_UNIT_OFR_OPT%ROWTYPE;
121         -- this cursor used when primary key is passed
122         CURSOR  c_unit_offering_option_prim(
123                         cp_unit_cd IGS_PS_UNIT_OFR_OPT.unit_cd%TYPE,
124                         cp_version_number IGS_PS_UNIT_OFR_OPT.version_number%TYPE,
125                         cp_cal_type IGS_PS_UNIT_OFR_OPT.cal_type%TYPE,
126                         cp_ci_sequence_number IGS_PS_UNIT_OFR_OPT.ci_sequence_number%TYPE,
127                         cp_location_cd IGS_PS_UNIT_OFR_OPT.location_cd%TYPE,
128                         cp_unit_class IGS_PS_UNIT_OFR_OPT.unit_class%TYPE) IS
129                 SELECT  IGS_PS_UNIT_OFR_OPT.uoo_id
130                 FROM    IGS_PS_UNIT_OFR_OPT
131                 WHERE   IGS_PS_UNIT_OFR_OPT.unit_cd = cp_unit_cd AND
132                         IGS_PS_UNIT_OFR_OPT.version_number = cp_version_number AND
133                         IGS_PS_UNIT_OFR_OPT.cal_type= cp_cal_type AND
134                         IGS_PS_UNIT_OFR_OPT.ci_sequence_number = cp_ci_sequence_number AND
135                         IGS_PS_UNIT_OFR_OPT.location_cd = cp_location_cd AND
136                         IGS_PS_UNIT_OFR_OPT.unit_class = cp_unit_class;
137         -- this cursor is used when unique key is passed
138         CURSOR  c_unit_offering_option_uniq(
139                         cp_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
140                 SELECT  *
141                 FROM    IGS_PS_UNIT_OFR_OPT
142                 WHERE   IGS_PS_UNIT_OFR_OPT.uoo_id = cp_uoo_id;
143 BEGIN
144         -- This module returns IGS_PS_UNIT_OFR_OPT primary key
145         -- or unique key depending on the parameters
146         IF (p_unit_cd IS NOT NULL) THEN
147                 OPEN c_unit_offering_option_prim(p_unit_cd,
148                                                  p_version_number,
149                                                  p_cal_type,
150                                                  p_ci_sequence_number,
151                                                  p_location_cd,
152                                                  p_unit_class);
153                 FETCH c_unit_offering_option_prim INTO p_uoo_id;
154                 CLOSE c_unit_offering_option_prim;
155         ELSIF p_uoo_id IS NOT NULL THEN
156                 OPEN c_unit_offering_option_uniq(p_uoo_id);
157                 FETCH c_unit_offering_option_uniq INTO gv_unit_offering_option_rec;
158                 p_unit_cd := gv_unit_offering_option_rec.unit_cd;
159                 p_version_number := gv_unit_offering_option_rec.version_number;
160                 p_cal_type := gv_unit_offering_option_rec.cal_type;
161                 p_ci_sequence_number := gv_unit_offering_option_rec.ci_sequence_number;
162                 p_location_cd := gv_unit_offering_option_rec.location_cd;
163                 p_unit_class := gv_unit_offering_option_rec.unit_class;
164                 CLOSE c_unit_offering_option_uniq;
165         ELSE
166                 -- Do nothing
167                 NULL;
168         END IF;
169 EXCEPTION
170 
171         WHEN OTHERS THEN
172                 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
173                 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_006.crsp_get_uoo_key');
174                 IGS_GE_MSG_STACK.ADD;
175                 lv_param_values := p_unit_cd||','||to_char(p_version_number)||','||p_cal_type
176                                         ||','||to_char(p_ci_sequence_number)||','||p_location_cd
177                                         ||','||p_unit_class||','||to_char(p_uoo_id);
178                 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
179                 Fnd_Message.Set_Token('VALUE',lv_param_values);
180                 IGS_GE_MSG_STACK.ADD;
181                 App_Exception.Raise_Exception;
182 END;
183 END crsp_get_uoo_key;
184 
185 FUNCTION crsp_get_us_admin(
186   p_unit_set_cd IN VARCHAR2 ,
187   p_version_number IN NUMBER )
188 RETURN VARCHAR2 AS
189         lv_param_values                 VARCHAR2(1080);
190 BEGIN   -- crsp_get_us_admin
191         -- This module fetches the value for the administrative_ind for a
192         -- IGS_PS_UNIT set from the unit_set_table.
193 DECLARE
194 
195         v_administrative_ind            IGS_EN_UNIT_SET.administrative_ind%TYPE;
196         CURSOR c_us IS
197                 SELECT  us.administrative_ind
198                 FROM    IGS_EN_UNIT_SET us
199                 WHERE   us.unit_set_cd          = p_unit_set_cd AND
200                         us.version_number       = p_version_number;
201 BEGIN
202         -- 1. Fetch the administrative indicator
203         OPEN c_us;
204         FETCH c_us INTO v_administrative_ind;
205         IF (c_us%FOUND) THEN
206                 CLOSE c_us;
207                 RETURN v_administrative_ind;
208         END IF;
209         CLOSE c_us;
210         RETURN NULL;
211 EXCEPTION
212         WHEN OTHERS THEN
213                 IF (c_us%ISOPEN) THEN
214                         CLOSE c_us;
215                 END IF;
216                 App_Exception.Raise_Exception;
217 END;
218 EXCEPTION
219         WHEN OTHERS THEN
220                 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
221                 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_006.crsp_get_us_admin');
222                 IGS_GE_MSG_STACK.ADD;
223                 lv_param_values := p_unit_set_cd||','||to_char(p_version_number);
224                 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
225                 Fnd_Message.Set_Token('VALUE',lv_param_values);
226                 IGS_GE_MSG_STACK.ADD;
227                 App_Exception.Raise_Exception;
228 END crsp_get_us_admin;
229 
230 FUNCTION crsp_get_us_sys_sts(
231   p_unit_set_status IN VARCHAR2 )
232 RETURN VARCHAR2 AS
233 lv_param_values                 VARCHAR2(1080);
234 BEGIN
235 
236         -- crsp_get_us_sys_sts
237         -- This module fetches the value for the system IGS_PS_UNIT set status for a
238         -- IGS_PS_UNIT set from the IGS_EN_UNIT_SET_STAT table.
239 DECLARE
240 
241         v_uss_s_unit_set_status         IGS_EN_UNIT_SET.unit_set_status%TYPE;
242         CURSOR c_uss IS
243                 SELECT  uss.s_unit_set_status
244                 FROM    IGS_EN_UNIT_SET_STAT            uss
245                 WHERE   uss.unit_set_status     = p_unit_set_status;
246 BEGIN
247         -- Get the system IGS_PS_UNIT set status
248         OPEN c_uss;
249         FETCH c_uss INTO v_uss_s_unit_set_status;
250         IF (c_uss%FOUND) THEN
251                 CLOSE c_uss;
252                 RETURN v_uss_s_unit_set_status;
253         END IF;
254         CLOSE c_uss;
255         RETURN NULL;
256 EXCEPTION
257         WHEN OTHERS THEN
258                 IF (c_uss%ISOPEN) THEN
259                         CLOSE c_uss;
260                 END IF;
261                 App_Exception.Raise_Exception;
262 END;
263 EXCEPTION
264         WHEN OTHERS THEN
265                 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
266                 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_006.crsp_get_us_sys_sts');
267                 IGS_GE_MSG_STACK.ADD;
268                 lv_param_values := p_unit_set_status;
269                 Fnd_Message.Set_Token('VALUE',lv_param_values);
270                 IGS_GE_MSG_STACK.ADD;
271                 App_Exception.Raise_Exception;
272 END crsp_get_us_sys_sts;
273 
274 
275 
276 
277 PROCEDURE crsp_ins_ci_uop_uoo(
278   errbuf  out NOCOPY  varchar2,
279   retcode out NOCOPY  number,
280   p_source_cal  IN VARCHAR2 ,
281   p_dest_cal  IN VARCHAR2 ,
282   p_org_unit   IN VARCHAR2,
283   p_org_id IN NUMBER )
284 
285 AS
286 -------------------------------------------------------------------------------------------
287 --Change History:
288 --Who             When             What
289 --sarakshi        17-Oct-2005      Bug#4657596, added fnd logging and corrected  ERRBUF setting in the exception
290 --sarakshi        14-Oct-2005      BUg#4657596, modified the exception such that get_string is called appropriately.
291 --sommukhe        01-SEP-2005      Bug# 4538540 , removed  p_org_unit_cd and used Rollback in when others of the Exception Block.
292 --shtatiko        3-FEB-2003       Bug# 2550411, Added code to log information wherever needed to make
293 --                                 log file more informative.
294 -------------------------------------------------------------------------------------------
295 
296   p_source_cal_type                       igs_ca_inst.cal_type%type ;
297   p_source_sequence_number                igs_ca_inst.sequence_number%type;
298   p_dest_cal_type                         igs_ca_inst.cal_type%type;
299   p_dest_sequence_number                  igs_ca_inst.sequence_number%type;
300   gv_check                                VARCHAR2(1);
301   gv_uoo_rec                              igs_ps_unit_ofr_opt%ROWTYPE;
302   gv_cal_instance_rec                     igs_ca_inst%ROWTYPE;
303   gv_start_dt                             igs_ca_inst.start_dt%TYPE;
304   gv_end_dt                               igs_ca_inst.end_dt%TYPE;
305   gv_uv_rec                               igs_ps_unit_ver%ROWTYPE;
306   gv_message                              VARCHAR2(255);
307   gv_rec_inserted_cnt                     NUMBER(4);
308   v_uop_identifier                        VARCHAR2(255);
309   v_message                               VARCHAR2(255);
310   v_uoo_uai_error_flag                    BOOLEAN ;
311   v_none_uoo_uai_recs_inserted            BOOLEAN ;
312   v_some_uoo_uai_recs_inserted            BOOLEAN ;
313   v_all_uoo_uai_recs_inserted             BOOLEAN ;
314   v_total_none_uoo_uai_inserted           BOOLEAN ;
315   v_total_some_uoo_uai_inserted           BOOLEAN ;
316   v_total_all_uoo_uai_inserted            BOOLEAN ;
317   v_none_uop_recs_inserted                BOOLEAN ;
318   v_some_uop_recs_inserted                BOOLEAN ;
319   v_all_uop_recs_inserted                 BOOLEAN ;
320   v_uap_insert_error                      BOOLEAN ;
321   lv_out_date                             DATE;
322 
323   CURSOR gc_cal_type_exists IS
324   SELECT  'x'
325   FROM    igs_ca_type
326   WHERE   cal_type = p_source_cal_type;
327 
328   CURSOR gc_cal_instance_exists(cp_cal_type             igs_ca_inst.cal_type%TYPE,
329                                 cp_sequence_number      igs_ca_inst.sequence_number%TYPE) IS
330   SELECT  *
331   FROM    igs_ca_inst
332   WHERE   cal_type        = cp_cal_type
333   AND     sequence_number = cp_sequence_number;
334 
335   CURSOR gc_unit_offering_pattern IS
336   SELECT  uop.unit_cd,
337           uop.version_number,
338           uop.cal_type,
339           uop.ci_sequence_number,
340           uop.ci_start_dt,
341           uop.ci_end_dt,
342           uop.waitlist_allowed,
343           uop.max_students_per_waitlist,
344 	  uop.delete_flag
345   FROM    igs_ps_unit_ofr_pat     uop,
346           igs_ps_unit_ver         uv
347   WHERE   uop.cal_type            = p_source_cal_type
348   AND     uop.ci_sequence_number  = p_source_sequence_number
349   AND     uv.unit_cd              = uop.unit_cd
350   AND     uv.version_number       = uop.version_number
351   AND     uv.expiry_dt            IS NULL
352   AND     uv.owner_org_unit_cd    = NVL(p_org_unit,uv.owner_org_unit_cd)
353   AND     uop.delete_flag = 'N';
354   gv_uop_rec                              gc_unit_offering_pattern%ROWTYPE;
355 
356   CURSOR gc_check_dest_uo_exists(cp_unit_cd              igs_ps_unit_ofr.unit_cd%TYPE,
357                                  cp_version_number       igs_ps_unit_ofr.version_number%TYPE,
358                                  cp_dest_cal_type        igs_ps_unit_ofr.cal_type%TYPE) IS
359   SELECT 'x' FROM igs_ps_unit_ofr
360   WHERE   unit_cd                 = cp_unit_cd
361   AND     version_number          = cp_version_number
362   AND     cal_type                = cp_dest_cal_type;
363   gv_uo_rec                       gc_check_dest_uo_exists%ROWTYPE;
364 
365   CURSOR gc_check_uop_exists (cp_unit_cd              igs_ps_unit_ofr_pat.unit_cd%TYPE,
366                               cp_version_number       igs_ps_unit_ofr_pat.version_number%TYPE) IS
367   SELECT  'x'
368   FROM    igs_ps_unit_ofr_pat
369   WHERE   unit_cd                 = cp_unit_cd
370   AND     version_number          = cp_version_number
371   AND     cal_type                = p_dest_cal_type
372   AND     ci_sequence_number      = p_dest_sequence_number
373   AND     delete_flag = 'N';
374 
375   x_rowid         VARCHAR2(25);
376   INVALID         EXCEPTION;
377   VALID           EXCEPTION;
378 
379   l_start_dt      igs_ca_inst.start_dt%TYPE;
380   l_end_dt        igs_ca_inst.end_dt%TYPE;
381 
382 BEGIN
383 
384   igs_ge_gen_003.set_org_id(p_org_id);
385 
386   retcode:=0;
387 
388   -- Assigning initial values to local variables which were being initialised using DEFAULT
389   -- clause.Done as part of bug #2563596 to remove GSCC warning.
390 
391   gv_rec_inserted_cnt             := 0;
392   v_uoo_uai_error_flag            := FALSE;
393   v_none_uoo_uai_recs_inserted    := FALSE;
394   v_some_uoo_uai_recs_inserted    := FALSE;
395   v_all_uoo_uai_recs_inserted     := FALSE;
396   v_total_none_uoo_uai_inserted   := FALSE;
397   v_total_some_uoo_uai_inserted   := FALSE;
398   v_total_all_uoo_uai_inserted    := FALSE;
399   v_none_uop_recs_inserted        := FALSE;
400   v_some_uop_recs_inserted        := FALSE;
401   v_all_uop_recs_inserted         := FALSE;
402   v_uap_insert_error              := FALSE;
403 
404 
405   -- Extract source calendar
406   p_source_cal_type        := RTRIM(SUBSTR(p_source_cal, 102, 10));
407   p_source_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_source_cal, 113, 8)));
408 
409   -- Extract destination calendar
410   p_dest_cal_type          := RTRIM(SUBSTR(p_dest_cal, 102, 10));
411   p_dest_sequence_number   := TO_NUMBER(RTRIM(SUBSTR(p_dest_cal, 113, 8)));
412 
413 
414   -- Log all the parameters passed. This has been added as part of Bug# 2550411 by shtatiko
415   l_start_dt := TO_DATE ( RTRIM(SUBSTR(p_source_cal, 12, 10)), 'DD/MM/YYYY' ) ;
416   l_end_dt := TO_DATE ( RTRIM(SUBSTR(p_source_cal, 23, 10)), 'DD/MM/YYYY' );
417 
418   fnd_file.put_line ( fnd_file.LOG,  ' ' );
419   log_parameters ( p_c_param_name  => igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'SOURCE_CAL' ),
420                    p_c_param_value => TO_CHAR ( l_start_dt, 'DD-MON-YYYY' ) || ' - ' ||
421                                       TO_CHAR ( l_end_dt, 'DD-MON-YYYY' ) || ' - ' ||
422                                       p_source_cal_type );
423 
424   l_start_dt := TO_DATE ( RTRIM(SUBSTR(p_dest_cal, 12, 10)), 'DD/MM/YYYY' ) ;
425   l_end_dt := TO_DATE ( RTRIM(SUBSTR(p_dest_cal, 23, 10)), 'DD/MM/YYYY' );
426 
427   log_parameters ( p_c_param_name  => igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DEST_CAL' ),
428                    p_c_param_value => TO_CHAR ( l_start_dt, 'DD-MON-YYYY' ) || ' - ' ||
429                                       TO_CHAR ( l_end_dt, 'DD-MON-YYYY' ) || ' - ' ||
430                                       p_dest_cal_type );
431 
432   log_parameters ( p_c_param_name  => igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'ORG_UNIT_CD' ),
433                    p_c_param_value =>  NVL(p_org_unit,'%') );
434 
435   fnd_file.put_line ( fnd_file.LOG,  ' ' );
436   fnd_file.put_line ( fnd_file.LOG,  ' ' );
437 
438   v_message          := NULL;
439   v_uap_insert_error := FALSE;
440 
441   /* DO NOT REMOVE THIS COMMENTED CODE TO TRACK THE BUG WHICH HAS REMOVED THIS VALIDATION
442    Enhancement Bug : 1298281
443    Now, Allowing roll-over between 2 difft calender types.
444    -- Check calendar type for source and destination is the same
445    -- If not, records can't be rolled over
446    IF (p_source_cal_type <> p_dest_cal_type) THEN
447            v_message := 'IGS_PS_ONLY_ROLLOVER_UO';
448            RAISE invalid;
449    END IF;
450   */
451 
452 
453   -- validating that the calendar type is open and of type 'TEACHING'
454   -- As part of the bug# 1956374 changed to the below call from igs_ps_val_uop.crsp_val_uo_cal_type
455   IF (igs_as_val_uai.crsp_val_uo_cal_type (p_source_cal_type,v_message) = FALSE) THEN
456       RAISE invalid;
457   END IF;
458 
459   -- validating that the source calendar instance exists
460   OPEN gc_cal_instance_exists(p_source_cal_type,
461                               p_source_sequence_number);
462   FETCH gc_cal_instance_exists INTO gv_cal_instance_rec;
463   IF (gc_cal_instance_exists%NOTFOUND) THEN
464     CLOSE gc_cal_instance_exists;
465     v_message  := 'IGS_PS_SRC_CALINST_NOT_EXIST';
466     RAISE invalid;
467   END IF;
468   CLOSE gc_cal_instance_exists;
469 
470   -- validating that the destination calendar instance exists
471   OPEN gc_cal_instance_exists(p_dest_cal_type,
472                               p_dest_sequence_number);
473   FETCH gc_cal_instance_exists INTO gv_cal_instance_rec;
474   IF (gc_cal_instance_exists%NOTFOUND) THEN
475     CLOSE gc_cal_instance_exists;
476     v_message:= 'IGS_PS_DEST_CAL_INST_NOT_EXIS';
477     RAISE invalid;
478   END IF;
479   -- get start and end dates
480   gv_start_dt := gv_cal_instance_rec.start_dt;
481   gv_end_dt := gv_cal_instance_rec.end_dt;
482   CLOSE gc_cal_instance_exists;
483 
484   -- validating that the destination calendar instance is active
485   IF (igs_as_val_uai.crsp_val_crs_ci (p_dest_cal_type,
486                                       p_dest_sequence_number,
487                                       v_message) = FALSE) THEN
488     RAISE invalid;
489   END IF;
490   --End of Parameter Validation
491 
492 
493   --Enhancement bug no 1800179, pmarada. Insert a record in log entry table.
494   --This will be used in failure report IGSPS11
495 
496   igs_ge_gen_003.genp_ins_log ('USEC-ROLL' ,
497                                ' ',
498                                lv_out_date );
499 
500   -- selecting IGS_PS_UNIT_OFR_PAT records from IGS_PS_UNIT_OFR_PAT and IGS_PS_UNIT_VER
501   OPEN gc_unit_offering_pattern;
502   LOOP
503     FETCH gc_unit_offering_pattern INTO gv_uop_rec;
504     EXIT WHEN gc_unit_offering_pattern%NOTFOUND;
505 
506     -- This logging Unit information has been added as part of Bug# 2550411 by shtatiko
507     fnd_file.put_line ( fnd_file.LOG, ' ');
508     fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_CD' )
509                                       || '           : ' || gv_uop_rec.unit_cd );
510     fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'UNIT_VER_NUM' )
511                                       || ' : ' || TO_CHAR (gv_uop_rec.version_number) );
512     fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'LEGACY_TOKENS', 'CAL_TYPE' )
513                                       || '       : ' || p_dest_cal_type );
514     fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_FI_LOCKBOX', 'START_DT' )
515                                       || '          : ' || fnd_date.date_to_displaydate (gv_start_dt) );
516     fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_FI_LOCKBOX', 'END_DT' )
517                                       || '            : ' || fnd_date.date_to_displaydate (gv_end_dt) );
518     fnd_file.put_line ( fnd_file.LOG, ' ');
519 
520     -- Check that IGS_PS_UNIT version in not inactive, otherwise
521     -- it can't be updated
522     IF (igs_ps_val_unit.crsp_val_iud_uv_dtl( gv_uop_rec.unit_cd,
523                                              gv_uop_rec.version_number,
524                                              gv_message) = TRUE) THEN
525 
526       -- Check if the Destination Calender Type for this Unit_cd and Version number exists
527       -- in IGS_PS_UNIT_OFR
528       OPEN gc_check_dest_uo_exists(gv_uop_rec.unit_cd,
529                                    gv_uop_rec.version_number,
530                                    p_dest_cal_type);
531       FETCH gc_check_dest_uo_exists INTO gv_uo_rec;
532       IF (gc_check_dest_uo_exists%NOTFOUND) THEN
533         -- This message has been added as part of Bug# 2550411 by shtatiko to log more specific message in case
534         -- Destination Calendar is not defined for the current Unit.
535         fnd_message.set_name ( 'IGS', 'IGS_PS_NO_DEST_CAL_UNIT' );
536         fnd_file.put_line ( fnd_file.LOG, '   ' || fnd_message.get );
537       ELSE
538         OPEN gc_check_uop_exists (gv_uop_rec.unit_cd,
539                                   gv_uop_rec.version_number);
540         FETCH gc_check_uop_exists INTO gv_check;
541         IF (gc_check_uop_exists%NOTFOUND) THEN
542 
543           igs_ps_unit_ofr_pat_pkg.Insert_Row(
544             x_rowid                     => x_rowid,
545             x_unit_cd                   => gv_uop_rec.unit_cd,
546             x_version_number            => gv_uop_rec.version_number,
547             x_ci_sequence_number        => p_dest_sequence_number,
548             x_cal_type                  => p_dest_cal_type,
549             x_ci_start_dt               => gv_start_dt,
550             x_ci_end_dt                 => gv_end_dt,
551             x_waitlist_allowed          => gv_uop_rec.waitlist_allowed,
552             x_max_students_per_waitlist => gv_uop_rec.max_students_per_waitlist,
553             x_mode                      => 'R',
554             x_org_id                    => p_org_id,
555 	    x_delete_flag               => gv_uop_rec.delete_flag,
556 	    x_abort_flag                => 'N');
557 
558           gv_rec_inserted_cnt := gv_rec_inserted_cnt + 1;
559           -- This message has been added as part of Bug# 2550411 by shtatiko
560           fnd_message.set_name ( 'IGS', 'IGS_PS_ROLL_UOP_SUCCESS' );
561           fnd_file.put_line ( fnd_file.LOG, '   ' || fnd_message.get );
562         ELSE
563           -- This message has been added as part of Bug# 2550411 by shtatiko
564           fnd_message.set_name ( 'IGS', 'IGS_PS_ROLL_UOP_EXISTS' );
565           fnd_file.put_line ( fnd_file.LOG, '   ' || fnd_message.get );
566         END IF;
567         CLOSE gc_check_uop_exists;
568 
569         --Enhancement bug no 1800179
570         -- insert IGS_PS_UNIT_OFR_OPT and IGS_AS_UNITASS_ITEM records for
571         -- the IGS_PS_UNIT_OFR_PAT record
572         IF (igs_ps_gen_008.crsp_ins_uop_uoo( gv_uop_rec.unit_cd,
573                                              gv_uop_rec.version_number,
574                                              p_dest_cal_type,
575                                              p_source_sequence_number,
576                                              p_dest_sequence_number,
577                                              p_source_cal_type,
578                                              gv_message,
579                                              lv_out_date) = TRUE) THEN
580           -- This logging of message returned by the function has been added to log the
581           -- status of importing Unit Offering Pattern and other details. This has been done
582           -- as per Bug fix 2550411 by shtatiko
583           fnd_message.set_name ( 'IGS', gv_message );
584           fnd_file.put_line ( fnd_file.LOG, '   ' || fnd_message.get );
585 
586           IF (gv_message = 'IGS_PS_NO_UOO_AND_UAI_ROLLED'  OR
587               gv_message = 'IGS_PS_UOO_NO_UOO_TOBE_ROLLED' OR
588               gv_message = 'IGS_PS_UOO_NO_UAI_TOBE_ROLLED' OR
589               gv_message ='IGS_PS_NO_UOO_UAI_ROLLED') THEN
590             v_none_uoo_uai_recs_inserted := TRUE;
591           ELSIF (gv_message = 'IGS_PS_PARROLL_UOO_UAI' ) THEN
592             v_some_uoo_uai_recs_inserted := TRUE;
593           ELSIF (gv_message = 'IGS_PS_PARTIALROLL_UOO_USI' ) THEN
594             v_some_uoo_uai_recs_inserted := TRUE;
595             v_uap_insert_error := TRUE;
596           ELSIF (gv_message ='IGS_PS_SUCCESSROLL_UOO_UAI') THEN
597             v_all_uoo_uai_recs_inserted := TRUE;
598           ELSIF (gv_message = 'IGS_PS_SUCCESS_ROLL_UOO_UAI') THEN
599             v_all_uoo_uai_recs_inserted := TRUE;
600             v_uap_insert_error := TRUE;
601           END IF;
602         ELSE
603           -- crsp_ins_uop_uoo returns FALSE
604           -- Then insert record into run log using rjr details and
605           -- error message details
606 
607           -- This logging of message returned by the function has been added to log the
608           -- status of importing Unit Offering Pattern and other details. This has been done
609           -- as per Bug fix 2550411 by shtatiko
610           fnd_message.set_name ( 'IGS', gv_message );
611           fnd_file.put_line ( fnd_file.LOG, '   ' || fnd_message.get );
612 
613           v_uoo_uai_error_flag := TRUE;
614           IF ( gv_message='IGS_PS_INV_NO_UOO_ROLLED' OR
615                gv_message = 'IGS_PS_INV_UOO_ROLLED'  OR
616                gv_message = 'IGS_PS_INV_NO_UAI_TOBE_ROLLED' OR
617                gv_message = 'IGS_PS_INV_NO_UAI_OBS_DATA'  OR
618                gv_message ='IGS_PS_NO_UOO_UA_ROL_INVALID') THEN
619             v_none_uoo_uai_recs_inserted := TRUE;
620           ELSIF (gv_message = 'IGS_PS_PRINV_NO_UOO_ROLLED'  OR
621                  gv_message ='IGS_PS_PRINV_NO_UOO_OBS_DATA'  OR
622                  gv_message = 'IGS_PS_INV_UAI_PAR_ROLL'  OR
623                  gv_message = 'IGS_PS_PRINV_NO_UOO_INVALID' OR
624                  gv_message = 'IGS_PS_INV_ALL_UAI_ROLLED'  OR
625                  gv_message='IGS_PS_INV_PARROLL_UOO_OBSDAT' OR
626                  gv_message = 'IGS_PS_PRINV_PARROL_UOO_OBS'  OR
627                  gv_message = 'IGS_PS_PRINV_NO_UAI_ROLLED' OR
628                  gv_message = 'IGS_PS_PRINV_NO_UAI_ROL_OBS' OR
629                  gv_message ='IGS_PS_PRINV_NO_UAI_INVALID'  OR
630                  gv_message = 'IGS_PS_PRINV_PARROLL_UAI'  OR
631                  gv_message= 'IGS_PS_PRINV_UOO_UAI'  OR
632                  gv_message = 'IGS_PS_PRINV_ALL_UAI_ROLLED' OR
633                  gv_message = 'IGS_PS_INV_ALL_UOO_ROLLED' OR
634                  gv_message = 'IGS_PS_PRINV_ALL_UOO_ROLLED') THEN
635             v_some_uoo_uai_recs_inserted := TRUE;
636           ELSIF (gv_message = 'IGS_PS_PARTILROLL_USI'  OR
637                  gv_message = 'IGS_PS_PARROLL_UAI_INVLD_DATA' OR
638                  gv_message='IGS_PS_INVALID_DATA' OR
639                  gv_message = 'IGS_PS_PARROLL_USI_INVALID'  OR
640                  gv_message = 'IGS_PS_NOTROLLED_INVALID_DATA' OR
641                  gv_message = 'IGS_PS_PARTIALROLL_UAI'  OR
642                  gv_message = 'IGS_PS_PARTIALROLL_UOO_INVALI' OR
643                  gv_message = 'IGS_PS_PARROLL_UOO_AND_UAI'  OR
644                  gv_message = 'IGS_PS_PARTIALROLL_UOO_INVDAT' OR
645                  gv_message = 'IGS_PS_PARTIALROLL_UAI_UAIINV') THEN
646               v_some_uoo_uai_recs_inserted := TRUE;
647               v_uap_insert_error := TRUE;
648           END IF;
649         END IF;
650       END IF;   -- cursor gc_check_dest_uo_exists
651       CLOSE gc_check_dest_uo_exists;
652     END IF;
653   END LOOP;
654 
655   IF ((v_none_uoo_uai_recs_inserted = TRUE AND
656        v_some_uoo_uai_recs_inserted = TRUE AND
657        v_all_uoo_uai_recs_inserted = TRUE)    OR
658       (v_none_uoo_uai_recs_inserted = TRUE AND
659        v_some_uoo_uai_recs_inserted = TRUE AND
660        v_all_uoo_uai_recs_inserted = FALSE)   OR
661       (v_none_uoo_uai_recs_inserted = TRUE  AND
662        v_some_uoo_uai_recs_inserted = FALSE AND
663        v_all_uoo_uai_recs_inserted = TRUE)    OR
664       (v_none_uoo_uai_recs_inserted = FALSE AND
665        v_some_uoo_uai_recs_inserted = TRUE  AND
666        v_all_uoo_uai_recs_inserted = TRUE)    OR
667       (v_none_uoo_uai_recs_inserted = FALSE AND
668        v_some_uoo_uai_recs_inserted = TRUE  AND
669        v_all_uoo_uai_recs_inserted = FALSE))  THEN
670     v_total_some_uoo_uai_inserted := TRUE;
671   ELSIF ((v_none_uoo_uai_recs_inserted = TRUE  AND
672           v_some_uoo_uai_recs_inserted = FALSE AND
673           v_all_uoo_uai_recs_inserted = FALSE)   OR
674          (v_none_uoo_uai_recs_inserted = FALSE AND
675           v_some_uoo_uai_recs_inserted = FALSE AND
676           v_all_uoo_uai_recs_inserted = FALSE)) THEN
677     v_total_none_uoo_uai_inserted := TRUE;
678   ELSIF ( v_none_uoo_uai_recs_inserted = FALSE AND
679           v_some_uoo_uai_recs_inserted = FALSE AND
680           v_all_uoo_uai_recs_inserted = TRUE) THEN
681     v_total_all_uoo_uai_inserted := TRUE;
682   END IF;
683 
684   -- set uop indicate flag
685   -- if no IGS_PS_UNIT_OFR_PAT records were inserted
686   IF (gv_rec_inserted_cnt = 0) THEN
687     v_none_uop_recs_inserted := TRUE;
688   -- if all IGS_PS_UNIT_OFR_PAT records were inserted
689   ELSIF (gv_rec_inserted_cnt = gc_unit_offering_pattern%ROWCOUNT) THEN
690     v_all_uop_recs_inserted := TRUE;
691   -- if some IGS_PS_UNIT_OFR_PAT records were inserted
692   ELSE
693     v_some_uop_recs_inserted := TRUE;
694   END IF;
695   CLOSE gc_unit_offering_pattern;
696 
697   IF v_none_uop_recs_inserted = TRUE THEN
698     IF v_total_none_uoo_uai_inserted = TRUE THEN
699       IF v_uoo_uai_error_flag = FALSE THEN
700         v_message := 'IGS_PS_NO_UOP_UO_UAI';
701         RAISE valid;
702       ELSE
703         v_message := 'IGS_PS_NO_UOP_HAVE_BEEN_ROLL';
704         COMMIT;
705         RAISE invalid;
706       END IF;
707     ELSIF v_total_some_uoo_uai_inserted = TRUE THEN
708       IF v_uoo_uai_error_flag = FALSE THEN
709         IF v_uap_insert_error = FALSE THEN
710           v_message := 'IGS_PS_PAR_ROLL_UOP_UO_UAI';
711         ELSE
712           v_message :='IGS_PS_PARROLL_UOP_UO_UAI';
713         END IF;
714         RAISE valid;
715       ELSE
716         IF v_uap_insert_error = FALSE THEN
717           v_message :='IGS_PS_NO_UOP_HAVE_BEEN_ROLL';
718         ELSE
719           v_message:='IGS_PS_NO_UOP_HAVEBEEN_ROLLED';
720         END IF;
721         COMMIT;
722         RAISE invalid;
723       END IF;
724     ELSIF v_total_all_uoo_uai_inserted = TRUE THEN
725       IF v_uap_insert_error = FALSE THEN
726         v_message := 'IGS_PS_PAR_ROLL_UOP_UO_UAI';
727       ELSE
728         v_message := 'IGS_PS_PARROLL_UOP_UO_UAI';
729       END IF;
730       RAISE valid;
731     END IF;
732   ELSIF v_some_uop_recs_inserted = TRUE THEN
733     IF v_total_none_uoo_uai_inserted = TRUE THEN
734       IF v_uoo_uai_error_flag = FALSE THEN
735         v_message := 'IGS_PS_PAR_ROLL_UOP_UO_UAI';
736         RAISE valid;
737       ELSE
738         v_message := 'IGS_PS_PARTIAL_ROLL_UOP';
739         COMMIT;
740         RAISE invalid;
741       END IF;
742     ELSIF v_total_some_uoo_uai_inserted = TRUE THEN
743       IF v_uoo_uai_error_flag = FALSE THEN
744         IF v_uap_insert_error = FALSE THEN
745           v_message := 'IGS_PS_PAR_ROLL_UOP_UO_UAI';
746         ELSE
747           v_message :='IGS_PS_PARROLL_UOP_UO_UAI';
748         END IF;
749         RAISE valid;
750       ELSE
751         IF v_uap_insert_error = FALSE THEN
752           v_message :='IGS_PS_PAR_ROLL_UOP_UO_UAI';
753         ELSE
754           v_message := 'IGS_PS_PARROLL_UOP';
755         END IF;
756         COMMIT;
757         RAISE invalid;
758       END IF;
759     ELSIF v_total_all_uoo_uai_inserted = TRUE THEN
760       IF v_uap_insert_error = FALSE THEN
761         v_message := 'IGS_PS_PAR_ROLL_UOP_UO_UAI';
762       ELSE
763         v_message :='IGS_PS_PARROLL_UOP_UO_UAI';
764       END IF;
765       RAISE VALID;
766     END IF;
767   ELSIF v_all_uop_recs_inserted = TRUE THEN
768     IF v_total_none_uoo_uai_inserted = TRUE THEN
769       IF v_uoo_uai_error_flag = FALSE THEN
770         v_message := 'IGS_PS_SUCCESSROLL_UOP_UO_UAI';
771         RAISE valid;
772       ELSE
773         v_message := 'IGS_PS_SUCCESSFUL_ROLL_UOP';
774         COMMIT;
775         RAISE invalid;
776       END IF;
777     ELSIF v_total_some_uoo_uai_inserted = TRUE THEN
778       IF v_uoo_uai_error_flag = FALSE THEN
779         IF v_uap_insert_error = FALSE THEN
780           v_message := 'IGS_PS_SUCCESSROLL_UOP_UO_UAI';
781         ELSE
782           v_message :='IGS_PS_SUCCESS_ROLL_UOP_UO_UA';
783         END IF;
784         RAISE VALID;
785       ELSE
786         IF v_uap_insert_error = FALSE THEN
787           v_message := 'IGS_PS_SUCCESSFUL_ROLL_UOP';
788         ELSE
789           v_message :='IGS_PS_SUCCESS_ROLL_UOP';
790         END IF;
791         COMMIT;
792         RAISE invalid;
793       END IF;
794     ELSIF v_total_all_uoo_uai_inserted = TRUE THEN
795       IF v_uap_insert_error = FALSE THEN
796         v_message := 'IGS_PS_SUCCESSROLL_UOP_UO_UAI';
797       ELSE
798         v_message :='IGS_PS_SUCCESS_ROLL_UOP_UO_UA';
799       END IF;
800       RAISE VALID;
801     END IF;
802   END IF;
803 
804 EXCEPTION
805   WHEN VALID THEN
806     COMMIT;
807     RETCODE:=0;
808     -- Code has been changed to log the error message instead of assigning it to ERRBUF (Bug# 2550411)
809     fnd_file.put_line ( fnd_file.LOG, ' ');
810     fnd_file.put_line ( fnd_file.LOG, fnd_message.get_string('IGS',v_message) );
811   WHEN INVALID THEN
812     RETCODE:=2;
813     -- Code has been changed to log the error message instead of assigning it to ERRBUF (Bug# 2550411)
814     fnd_file.put_line ( fnd_file.LOG, ' ');
815     fnd_file.put_line ( fnd_file.LOG, fnd_message.get_string('IGS',v_message) );
816   WHEN OTHERS THEN
817     RETCODE:=2;
818     ROLLBACK;
819     -- SQLERRM has been added as per Bug Fux 2550411
820     ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
821     IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
822     --Fnd log implementation
823     IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
824       fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_006.crsp_ins_ci_uop_uoo.in_exception_section_OTHERS.err_msg',
825       SUBSTRB(SQLERRM,1,4000));
826     END IF;
827 
828 END crsp_ins_ci_uop_uoo;
829 
830 --sommukhe 13-Feb-2006 bug#3306014 modified cursor c_ou for performance reason
831 PROCEDURE crsp_ins_us_hist(
832   p_unit_set_cd IN VARCHAR2 ,
833   p_version_number IN NUMBER ,
834   p_new_unit_set_status IN VARCHAR2 ,
835   p_old_unit_set_status IN VARCHAR2 ,
836   p_new_unit_set_cat IN VARCHAR2 ,
837   p_old_unit_set_cat IN VARCHAR2 ,
838   p_new_start_dt IN DATE ,
839   p_old_start_dt IN DATE ,
840   p_new_review_dt IN DATE ,
841   p_old_review_dt IN DATE ,
842   p_new_expiry_dt IN DATE ,
843   p_old_expiry_dt IN DATE ,
844   p_new_end_dt IN DATE ,
845   p_old_end_dt IN DATE ,
846   p_new_title IN VARCHAR2 ,
847   p_old_title IN VARCHAR2 ,
848   p_new_short_title IN VARCHAR2 ,
849   p_old_short_title IN VARCHAR2 ,
850   p_new_abbreviation IN VARCHAR2 ,
851   p_old_abbreviation IN VARCHAR2 ,
852   p_new_responsible_org_unit_cd IN VARCHAR2 ,
853   p_old_responsible_org_unit_cd IN VARCHAR2 ,
854   p_new_responsible_ou_start_dt IN DATE ,
855   p_old_responsible_ou_start_dt IN DATE ,
856   p_new_administrative_ind IN VARCHAR2 ,
857   p_old_administrative_ind IN VARCHAR2 ,
858   p_new_authorisation_rqrd_ind IN VARCHAR2 ,
859   p_old_authorisation_rqrd_ind IN VARCHAR2 ,
860   p_new_update_who IN VARCHAR2 ,
861   p_old_update_who IN VARCHAR2 ,
862   p_new_update_on IN DATE ,
863   p_old_update_on IN DATE )
864 AS
865 lv_param_values                 VARCHAR2(1080);
866 BEGIN   -- crsp_ins_us_hist
867         -- Insert a IGS_EN_UNIT_SET_HIST record. This routine also gets descriptions from
868         -- relevant reference data records for historical purposes.
869 DECLARE
870 
871         v_ush_rec                       IGS_EN_UNIT_SET_HIST%ROWTYPE;
872         v_create_history                BOOLEAN := FALSE;
873         v_hist_start_dt                 IGS_EN_UNIT_SET.LAST_UPDATE_DATE%TYPE;
874         v_hist_end_dt                   IGS_EN_UNIT_SET.LAST_UPDATE_DATE%TYPE;
875         v_hist_who                      IGS_EN_UNIT_SET.LAST_UPDATED_BY%TYPE;
876         CURSOR  c_ou IS
877                 SELECT  ou.party_name  description
878                 FROM    IGS_OR_INST_ORG_BASE_V  ou
879                 WHERE   ou.party_number   = p_old_responsible_org_unit_cd AND
880                         ou.start_dt     = p_old_responsible_ou_start_dt;
881 
882                 x_rowid         VARCHAR2(25);
883                 l_org_id        NUMBER(15);
884 BEGIN
885         -- If any of the old IGS_EN_UNIT_SET values (p_old_<column_name>) are different from
886         -- the associated new IGS_EN_UNIT_SET values (p_new_<column_name>) (with the
887         -- exception of the LAST_UPDATED_BY and LAST_UPDATE_DATE columns) then create a
888         -- IGS_EN_UNIT_SET_HIST history record with the old IGS_EN_UNIT_SET values
889         -- (p_old_<column_name>).  Only write the changed values to the history
890         -- record.  Do not set the LAST_UPDATED_BY and LAST_UPDATE_DATE columns when creating the
891         -- history record.
892         IF p_new_unit_set_status <> p_old_unit_set_status THEN
893                 v_ush_rec.unit_set_status := p_old_unit_set_status;
894                 v_create_history := TRUE;
895         END IF;
896         IF p_new_unit_set_cat <> p_old_unit_set_cat THEN
897                 v_ush_rec.unit_set_cat := p_old_unit_set_cat;
898                 v_create_history := TRUE;
899         END IF;
900         IF p_new_start_dt <> p_old_start_dt OR
901                         (p_new_start_dt         IS NULL AND
902                         p_old_start_dt          IS NOT NULL) OR
903                         (p_new_start_dt         IS NOT NULL AND
904                         p_old_start_dt          IS NULL) THEN
905                 v_ush_rec.start_dt := p_old_start_dt;
906                 v_create_history := TRUE;
907         END IF;
908         IF (p_new_review_dt <> p_old_review_dt) OR
909                         (p_new_review_dt        IS NULL AND
910                         p_old_review_dt         IS NOT NULL) OR
911                         (p_new_review_dt        IS NOT NULL AND
912                         p_old_review_dt         IS NULL) THEN
913                 v_ush_rec.review_dt := p_old_review_dt;
914                 v_create_history := TRUE;
915         END IF;
916         IF (p_new_expiry_dt <> p_old_expiry_dt) OR
917                         (p_new_expiry_dt        IS NULL AND
918                         p_old_expiry_dt         IS NOT NULL) OR
919                         (p_new_expiry_dt        IS NOT NULL AND
920                         p_old_expiry_dt         IS NULL) THEN
921                 v_ush_rec.expiry_dt := p_old_expiry_dt;
922                 v_create_history := TRUE;
923         END IF;
924         IF (p_new_end_dt <> p_old_end_dt) OR
925                         (p_new_end_dt           IS NULL AND
926                         p_old_end_dt            IS NOT NULL) OR
927                         (p_new_end_dt           IS NOT NULL AND
928                         p_old_end_dt            IS NULL) THEN
929                 v_ush_rec.end_dt := p_old_end_dt;
930                 v_create_history := TRUE;
931         END IF;
932         IF p_new_title <> p_old_title THEN
933                 v_ush_rec.title:= p_old_title;
934                 v_create_history := TRUE;
935         END IF;
936         IF p_new_short_title <> p_old_short_title THEN
937                 v_ush_rec.short_title := p_old_short_title;
938                 v_create_history := TRUE;
939         END IF;
940         IF p_new_abbreviation <> p_old_abbreviation THEN
941                 v_ush_rec.abbreviation := p_old_abbreviation;
942                 v_create_history := TRUE;
943         END IF;
944         IF (p_new_responsible_org_unit_cd <> p_old_responsible_org_unit_cd) OR
945                         (p_new_responsible_org_unit_cd  IS NULL AND
946                         p_old_responsible_org_unit_cd   IS NOT NULL) OR
947                         (p_new_responsible_org_unit_cd  IS NOT NULL AND
948                         p_old_responsible_org_unit_cd   IS NULL) THEN
949                 v_ush_rec.responsible_org_unit_cd := p_old_responsible_org_unit_cd;
950                 v_create_history := TRUE;
951         END IF;
952         IF (p_new_responsible_ou_start_dt <> p_old_responsible_ou_start_dt) OR
953                         (p_new_responsible_ou_start_dt  IS NULL AND
954                         p_old_responsible_ou_start_dt   IS NOT NULL) OR
955                         (p_new_responsible_ou_start_dt  IS NOT NULL AND
956                         p_old_responsible_ou_start_dt   IS NULL)THEN
957                 v_ush_rec.responsible_ou_start_dt := p_old_responsible_ou_start_dt;
958                 v_create_history := TRUE;
959         END IF;
960         IF p_new_administrative_ind <> p_old_administrative_ind THEN
961                 v_ush_rec.administrative_ind := p_old_administrative_ind;
962                 v_create_history := TRUE;
963         END IF;
964         IF p_new_authorisation_rqrd_ind <> p_old_authorisation_rqrd_ind THEN
965                 v_ush_rec.authorisation_rqrd_ind := p_old_authorisation_rqrd_ind;
966                 v_create_history := TRUE;
967         END IF;
968         -- create a history record if any column has changed
969         IF v_create_history = TRUE THEN
970                 v_ush_rec.unit_set_cd           := p_unit_set_cd;
971                 v_ush_rec.version_number        := p_version_number;
972                 v_ush_rec.hist_start_dt         := p_old_update_on;
973                 v_ush_rec.hist_end_dt           := p_new_update_on;
974                 v_ush_rec.hist_who              := p_old_update_who;
975                 IF p_new_responsible_org_unit_cd <> p_old_responsible_org_unit_cd OR
976                                 (p_new_responsible_ou_start_dt <> p_old_responsible_ou_start_dt OR
977                                 (p_new_responsible_ou_start_dt  IS NULL AND
978                                 p_old_responsible_ou_start_dt   IS NOT NULL) OR
979                                 (p_new_responsible_ou_start_dt  IS NOT NULL AND
980                                 p_old_responsible_ou_start_dt   IS NULL))       THEN
981                         OPEN c_ou;
982                         FETCH c_ou INTO v_ush_rec.ou_description;
983                         CLOSE c_ou;
984                 ELSE
985                         v_ush_rec.ou_description := NULL;
986                 END IF;
987 
988                 l_org_id := igs_ge_gen_003.get_org_id;
989 
990                 IGS_EN_UNIT_SET_HIST_PKG.Insert_Row(
991                                                 X_ROWID                  =>             x_rowid,
992                                                 X_UNIT_SET_CD            =>             v_ush_rec.unit_set_cd,
993                                                 X_VERSION_NUMBER         =>             v_ush_rec.version_number,
994                                                 X_HIST_START_DT          =>             v_ush_rec.hist_start_dt,
995                                                 X_HIST_END_DT            =>             v_ush_rec.hist_end_dt,
996                                                 X_HIST_WHO               =>             v_ush_rec.hist_who,
997                                                 X_UNIT_SET_STATUS        =>             v_ush_rec.unit_set_status,
998                                                 X_UNIT_SET_CAT           =>             v_ush_rec.unit_set_cat,
999                                                 X_START_DT               =>             v_ush_rec.start_dt,
1000                                                 X_REVIEW_DT              =>             v_ush_rec.review_dt,
1001                                                 X_EXPIRY_DT              =>             v_ush_rec.expiry_dt,
1002                                                 X_END_DT                 =>             v_ush_rec.end_dt,
1003                                                 X_TITLE                  =>             v_ush_rec.title,
1004                                                 X_SHORT_TITLE            =>             v_ush_rec.short_title,
1005                                                 X_ABBREVIATION           =>             v_ush_rec.abbreviation,
1006                                                 X_RESPONSIBLE_ORG_UNIT_CD=>             v_ush_rec.responsible_org_unit_cd,
1007                                                 X_RESPONSIBLE_OU_START_DT=>             v_ush_rec.responsible_ou_start_dt,
1008                                                 X_OU_DESCRIPTION         =>             v_ush_rec.ou_description,
1009                                                 X_ADMINISTRATIVE_IND     =>             v_ush_rec.administrative_ind,
1010                                                 X_AUTHORISATION_RQRD_IND =>             v_ush_rec.authorisation_rqrd_ind,
1011                                                 X_MODE                   =>             'R',
1012                                                 X_ORG_ID                 =>             l_org_id);
1013         END IF;
1014 EXCEPTION
1015         WHEN OTHERS THEN
1016                 IF (c_ou%ISOPEN) THEN
1017                         CLOSE c_ou;
1018                 END IF;
1019                 App_Exception.Raise_Exception;
1020 END;
1021 EXCEPTION
1022 
1023         WHEN OTHERS THEN
1024                 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1025                 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_006.crsp_ins_us_hist');
1026                 IGS_GE_MSG_STACK.ADD;
1027                 lv_param_values := p_unit_set_cd||','||to_char(p_version_number)||','||p_new_unit_set_status||','
1028                 ||p_old_unit_set_status||','||p_new_unit_set_cat||','||p_old_unit_set_cat||','
1029                 ||fnd_date.date_to_displaydate(p_new_start_dt)||','
1030                 ||fnd_date.date_to_displaydate(p_old_start_dt)||','||fnd_date.date_to_displaydate(p_new_review_dt)||','
1031                 ||fnd_date.date_to_displaydate(p_old_review_dt)||','||
1032                 fnd_date.date_to_displaydate(p_new_expiry_dt)||','||fnd_date.date_to_displaydate(p_old_expiry_dt)||','
1033                 ||fnd_date.date_to_displaydate(p_new_end_dt)||','||fnd_date.date_to_displaydate(p_old_end_dt)
1034                 ||','||p_new_title||','||p_old_title||','||p_new_short_title||','||p_old_short_title||','||p_new_abbreviation
1035                 ||','||p_old_abbreviation||','||p_new_responsible_org_unit_cd||','||p_old_responsible_org_unit_cd||','||
1036                 fnd_date.date_to_displaydate(p_new_responsible_ou_start_dt)||','||
1037                 fnd_date.date_to_displaydate(p_old_responsible_ou_start_dt)||','||p_new_administrative_ind
1038                 ||','||p_old_administrative_ind||','||p_new_authorisation_rqrd_ind||','||p_old_authorisation_rqrd_ind||','||
1039                 p_new_update_who||','||p_old_update_who||','||
1040                 fnd_date.date_to_displaydate(p_new_update_on)||','||fnd_date.date_to_displaydate(p_old_update_on);
1041                 Fnd_Message.Set_Token('VALUE',lv_param_values);
1042                 IGS_GE_MSG_STACK.ADD;
1043                 App_Exception.Raise_Exception;
1044 END crsp_ins_us_hist;
1045 
1046 -- sommukhe 13-Feb-2006 Bug#3306014, modified cursors c_org_unit for performance reason.
1047 -- rgangara 03-May-2001 added 2 cols i.e. ss_enrol_ind and ivr_enrol_ind as per DLD Unit Section Enrollment DLD
1048 -- rbezawad 24-May-2001 added 47 cols as per DLD PSP001-US
1049 -- apelleti 14-JUN-2001 renamed column registration_exclusion_flag to ss_display_ind as per DLD PSP001-US
1050 
1051 PROCEDURE crsp_ins_uv_hist(
1052   p_unit_cd IN VARCHAR2 ,
1053   p_version_number IN NUMBER ,
1054   p_last_update_on IN DATE ,
1055   p_update_on IN DATE ,
1056   p_last_update_who IN VARCHAR2 ,
1057   p_start_dt IN DATE ,
1058   p_review_dt IN DATE ,
1059   p_expiry_dt IN DATE ,
1060   p_end_dt IN DATE ,
1061   p_unit_status IN VARCHAR2 ,
1062   p_title IN VARCHAR2 ,
1063   p_short_title IN VARCHAR2 ,
1064   p_title_override_ind IN VARCHAR2 ,
1065   p_abbreviation IN VARCHAR2 ,
1066   p_unit_level IN VARCHAR2 ,
1067   p_credit_point_descriptor IN VARCHAR2 ,
1068   p_achievable_credit_points IN NUMBER ,
1069   p_enrolled_credit_points IN NUMBER ,
1070   p_supp_exam_permitted_ind IN VARCHAR2 ,
1071   p_points_increment IN NUMBER ,
1072   p_points_min IN NUMBER ,
1073   p_points_max IN NUMBER ,
1074   p_points_override_ind IN VARCHAR2 ,
1075   p_coord_person_id IN NUMBER ,
1076   p_owner_org_unit_cd IN VARCHAR2 ,
1077   p_owner_ou_start_dt IN DATE ,
1078   p_award_course_only_ind IN VARCHAR2 ,
1079   p_research_unit_ind IN VARCHAR2 ,
1080   p_industrial_ind IN VARCHAR2 ,
1081   p_practical_ind IN VARCHAR2 ,
1082   p_repeatable_ind IN VARCHAR2 ,
1083   p_assessable_ind IN VARCHAR2 ,
1084   p_unit_int_course_level_cd IN VARCHAR2,
1085   p_ss_enrol_ind IN VARCHAR2 ,
1086   p_ivr_enrol_ind IN VARCHAR2 ,
1087   -- Added by rbezawad as per PSP001-US DLD on 24-May-2001
1088   p_advance_maximum IN NUMBER,
1089   p_approval_date IN DATE,
1090   p_cal_type_enrol_load_cal IN VARCHAR2,
1091   p_cal_type_offer_load_cal IN VARCHAR2,
1092   p_clock_hours IN NUMBER,
1093   p_contact_hrs_lab IN NUMBER,
1094   p_contact_hrs_lecture IN NUMBER,
1095   p_contact_hrs_other IN NUMBER,
1096   p_continuing_education_units IN NUMBER,
1097   p_curriculum_id IN VARCHAR2 ,
1098   p_enrollment_expected IN NUMBER,
1099   p_enrollment_maximum IN NUMBER,
1100   p_enrollment_minimum IN NUMBER,
1101   p_exclude_from_max_cp_limit IN VARCHAR2 ,
1102   p_federal_financial_aid IN VARCHAR2 ,
1103   p_institutional_financial_aid IN VARCHAR2 ,
1104   p_lab_credit_points IN NUMBER,
1105   p_lecture_credit_points IN NUMBER,
1106   p_max_repeat_credit_points IN NUMBER,
1107   p_max_repeats_for_credit IN NUMBER,
1108   p_max_repeats_for_funding IN NUMBER,
1109   p_non_schd_required_hrs IN NUMBER,
1110   p_other_credit_points IN NUMBER,
1111   p_override_enrollment_max IN NUMBER,
1112   p_record_exclusion_flag IN VARCHAR2 ,
1113   p_ss_display_ind IN VARCHAR2 ,
1114   p_rpt_fmly_id IN NUMBER,
1115   p_same_teach_period_repeats IN NUMBER ,
1116   p_same_teach_period_repeats_cp IN NUMBER,
1117   p_same_teaching_period IN VARCHAR2,
1118   p_sequence_num_enrol_load_cal IN NUMBER,
1119   p_sequence_num_offer_load_cal IN NUMBER,
1120   p_special_permission_ind IN VARCHAR2 ,
1121   p_state_financial_aid IN VARCHAR2 ,
1122   p_subtitle_id IN NUMBER,
1123   p_subtitle_modifiable_flag IN VARCHAR2 ,
1124   p_unit_type_id IN NUMBER,
1125   p_work_load_cp_lab IN NUMBER,
1126   p_work_load_cp_lecture IN NUMBER,
1127   p_work_load_other IN NUMBER,
1128   p_claimable_hours IN NUMBER ,
1129   p_auditable_ind IN VARCHAR2,
1130   p_audit_permission_ind IN VARCHAR2,
1131   p_max_auditors_allowed IN NUMBER,
1132   p_billing_credit_points IN NUMBER,
1133   p_ovrd_wkld_val_flag IN VARCHAR2,
1134   p_workload_val_code IN VARCHAR2,
1135   p_billing_hrs IN NUMBER )
1136 AS
1137         lv_param_values         VARCHAR2(1080);
1138         v_ul_description        IGS_PS_UNIT_LEVEL.description%TYPE;
1139         v_ou_description        IGS_OR_UNIT.description%TYPE;
1140         v_uicl_description      IGS_PS_UNIT_INT_LVL.description%TYPE;
1141         v_level_code            IGS_PS_UNIT_TYPE_LVL.LEVEL_CODE%TYPE;
1142         v_repeat_code           IGS_PS_RPT_FMLY_ALL.REPEAT_CODE%TYPE;
1143         v_subtitle              IGS_PS_UNIT_SUBTITLE.SUBTITLE%TYPE;
1144 
1145         CURSOR  c_unit_level(
1146                         cp_unit_level IGS_PS_UNIT_LEVEL.unit_level%TYPE) IS
1147                 SELECT  description
1148                 FROM    IGS_PS_UNIT_LEVEL
1149                 WHERE   unit_level = cp_unit_level;
1150         CURSOR  c_org_unit(
1151                         cp_org_unit_cd IGS_OR_UNIT.org_unit_cd%TYPE,
1152                         cp_start_dt IGS_OR_UNIT.start_dt%TYPE) IS
1153                 SELECT  party_name description
1154                 FROM    IGS_OR_INST_ORG_BASE_V
1155                 WHERE   party_number  = cp_org_unit_cd AND
1156                         start_dt = cp_start_dt;
1157         CURSOR  c_unit_int_course_level(
1158                         cp_unit_int_course_level_cd
1159                                 IGS_PS_UNIT_INT_LVL.unit_int_course_level_cd%TYPE) IS
1160                 SELECT  description
1161                 FROM    IGS_PS_UNIT_INT_LVL
1162                 WHERE   unit_int_course_level_cd = cp_unit_int_course_level_cd;
1163 
1164         CURSOR c_unit_type_level ( cp_unit_type_id IGS_PS_UNIT_TYPE_LVL.UNIT_TYPE_ID%TYPE ) IS
1165           SELECT level_code
1166           FROM   IGS_PS_UNIT_TYPE_LVL
1167           WHERE  UNIT_TYPE_ID = cp_unit_type_id;
1168 
1169         CURSOR c_repeat_family ( cp_rpt_fmly_id IGS_PS_RPT_FMLY_ALL.RPT_FMLY_ID%TYPE ) IS
1170           SELECT repeat_code
1171           FROM   IGS_PS_RPT_FMLY_ALL
1172           WHERE  RPT_FMLY_ID= cp_rpt_fmly_id;
1173 
1174         CURSOR c_unit_subtitle ( cp_subtitle_id IGS_PS_UNIT_SUBTITLE.SUBTITLE_ID%TYPE ) IS
1175           SELECT subtitle
1176           FROM   igs_ps_unit_subtitle
1177           WHERE  subtitle_id = cp_subtitle_id;
1178 
1179                 x_rowid                 VARCHAR2(25);
1180                 l_org_id                NUMBER(15);
1181 BEGIN
1182         IF(p_unit_level IS NOT NULL) THEN
1183                 OPEN c_unit_level(
1184                         p_unit_level);
1185                 FETCH c_unit_level INTO v_ul_description;
1186                 CLOSE c_unit_level;
1187         ELSE
1188                 v_ul_description := NULL;
1189         END IF;
1190         IF(p_owner_org_unit_cd IS NOT NULL) THEN
1191                 OPEN c_org_unit(
1192                         p_owner_org_unit_cd,
1193                         p_owner_ou_start_dt);
1194                 FETCH c_org_unit INTO v_ou_description;
1195                 CLOSE c_org_unit;
1196         ELSE
1197                 v_ou_description := NULL;
1198         END IF;
1199         IF(p_unit_int_course_level_cd IS NOT NULL) THEN
1200                 OPEN c_unit_int_course_level(
1201                         p_unit_int_course_level_cd);
1202                 FETCH c_unit_int_course_level INTO v_uicl_description;
1203                 CLOSE c_unit_int_course_level;
1204         ELSE
1205                 v_uicl_description := NULL;
1206         END IF;
1207         IF(p_unit_type_id IS NOT NULL) THEN
1208                 OPEN c_unit_type_level(p_unit_type_id);
1209                 FETCH c_unit_type_level INTO v_level_code;
1210                 CLOSE c_unit_type_level;
1211         ELSE
1212                 v_level_code := NULL;
1213         END IF;
1214 
1215         IF(p_rpt_fmly_id IS NOT NULL) THEN
1216                 OPEN c_repeat_family(
1217                         p_rpt_fmly_id);
1218                 FETCH c_repeat_family INTO v_repeat_code;
1219                 CLOSE c_repeat_family;
1220         ELSE
1221                 v_repeat_code := NULL;
1222         END IF;
1223 
1224         IF(p_subtitle_id IS NOT NULL) THEN
1225                 OPEN c_unit_subtitle(
1226                         p_subtitle_id);
1227                 FETCH c_unit_subtitle INTO v_subtitle;
1228                 CLOSE c_unit_subtitle;
1229         ELSE
1230                 v_subtitle := NULL;
1231         END IF;
1232 
1233 
1234         l_org_id := igs_ge_gen_003.get_org_id;
1235 
1236         IGS_PS_UNIT_VER_HIST_PKG.Insert_Row(
1237                         X_ROWID                         =>      x_rowid,
1238                         X_UNIT_CD                     =>        p_unit_cd,
1239                         X_VERSION_NUMBER              =>        p_version_number,
1240                         X_HIST_START_DT               =>        p_last_update_on,
1241                         X_HIST_END_DT                 =>        p_update_on,
1242                         X_HIST_WHO                    =>        p_last_update_who,
1243                         X_START_DT                    =>        p_start_dt,
1244                         X_REVIEW_DT                   =>        p_review_dt,
1245                         X_EXPIRY_DT                   =>        p_expiry_dt,
1246                         X_END_DT                      =>        p_end_dt,
1247                         X_UNIT_STATUS                 =>        p_unit_status,
1248                         X_TITLE                       =>        p_title,
1249                         X_SHORT_TITLE                 =>        p_short_title,
1250                         X_TITLE_OVERRIDE_IND          =>        p_title_override_ind,
1251                         X_ABBREVIATION                =>        p_abbreviation,
1252                         X_UNIT_LEVEL                  =>        p_unit_level,
1253                         X_UL_DESCRIPTION              =>        v_ul_description,
1254                         X_CREDIT_POINT_DESCRIPTOR     =>        p_credit_point_descriptor,
1255                         X_ENROLLED_CREDIT_POINTS      =>        p_enrolled_credit_points,
1256                         X_POINTS_OVERRIDE_IND         =>        p_points_override_ind,
1257                         X_SUPP_EXAM_PERMITTED_IND     =>        p_supp_exam_permitted_ind,
1258                         X_COORD_PERSON_ID             =>        p_coord_person_id,
1259                         X_OWNER_ORG_UNIT_CD           =>        p_owner_org_unit_cd,
1260                         X_OWNER_OU_START_DT           =>        p_owner_ou_start_dt,
1261                         X_OU_DESCRIPTION              =>        v_ou_description,
1262                         X_AWARD_COURSE_ONLY_IND       =>        p_award_course_only_ind,
1263                         X_RESEARCH_UNIT_IND           =>        p_research_unit_ind,
1264                         X_INDUSTRIAL_IND              =>        p_industrial_ind,
1265                         X_PRACTICAL_IND               =>        p_practical_ind,
1266                         X_REPEATABLE_IND              =>        p_repeatable_ind,
1267                         X_ASSESSABLE_IND              =>        p_assessable_ind,
1268                         X_ACHIEVABLE_CREDIT_POINTS      =>      p_achievable_credit_points,
1269                         X_POINTS_INCREMENT            =>        p_points_increment,
1270                         X_POINTS_MIN                  =>        p_points_min,
1271                         X_POINTS_MAX                  =>        p_points_max,
1272                         X_UNIT_INT_COURSE_LEVEL_CD      =>      p_unit_int_course_level_cd,
1273                         X_UICL_DESCRIPTION            =>        v_uicl_description,
1274                         X_MODE                        =>        'R',
1275                         X_ORG_ID                      =>         l_org_id,
1276                         X_SS_ENROL_IND                =>        p_ss_enrol_ind,
1277                         X_IVR_ENROL_IND               =>        p_ivr_enrol_ind,
1278                         -- Added By rbezawad as per PSP001-US DLD on 24-May-2001
1279                         X_ADVANCE_MAXIMUM                 =>     p_advance_maximum,
1280                         X_APPROVAL_DATE                   =>     p_approval_date,
1281                         X_CAL_TYPE_ENROL_LOAD_CAL         =>     p_cal_type_enrol_load_cal,
1282                         X_CAL_TYPE_OFFER_LOAD_CAL         =>     p_cal_type_offer_load_cal,
1283                         X_CLOCK_HOURS                     =>     p_clock_hours,
1284                         X_CONTACT_HRS_LAB                 =>     p_contact_hrs_lab,
1285                         X_CONTACT_HRS_LECTURE             =>     p_contact_hrs_lecture,
1286                         X_CONTACT_HRS_OTHER               =>     p_contact_hrs_other,
1287                         X_CONTINUING_EDUCATION_UNITS      =>     p_continuing_education_units,
1288                         X_CURRICULUM_ID                   =>     p_curriculum_id,
1289                         X_ENROLLMENT_EXPECTED             =>     p_enrollment_expected,
1290                         X_ENROLLMENT_MAXIMUM              =>     p_enrollment_maximum,
1291                         X_ENROLLMENT_MINIMUM              =>     p_enrollment_minimum,
1292                         X_EXCLUDE_FROM_MAX_CP_LIMIT       =>     p_exclude_from_max_cp_limit,
1293                         X_FEDERAL_FINANCIAL_AID           =>     p_federal_financial_aid,
1294                         X_INSTITUTIONAL_FINANCIAL_AID     =>     p_institutional_financial_aid,
1295                         X_LAB_CREDIT_POINTS               =>     p_lab_credit_points,
1296                         X_LECTURE_CREDIT_POINTS           =>     p_lecture_credit_points,
1297                         X_LEVEL_CODE                      =>     v_level_code,
1298                         X_MAX_REPEAT_CREDIT_POINTS        =>     p_max_repeat_credit_points,
1299                         X_MAX_REPEATS_FOR_CREDIT          =>     p_max_repeats_for_credit,
1300                         X_MAX_REPEATS_FOR_FUNDING         =>     p_max_repeats_for_funding,
1301                         X_NON_SCHD_REQUIRED_HRS           =>     p_non_schd_required_hrs,
1302                         X_OTHER_CREDIT_POINTS             =>     p_other_credit_points,
1303                         X_OVERRIDE_ENROLLMENT_MAX         =>     p_override_enrollment_max,
1304                         X_RECORD_EXCLUSION_FLAG           =>     p_record_exclusion_flag,
1305                         X_SS_DISPLAY_IND                  =>     p_ss_display_ind,
1306                         X_REPEAT_CODE                     =>     v_repeat_code,
1307                         X_RPT_FMLY_ID                     =>     p_rpt_fmly_id,
1308                         X_SAME_TEACH_PERIOD_REPEATS       =>     p_same_teach_period_repeats,
1309                         X_SAME_TEACH_PERIOD_REPEATS_CP    =>     p_same_teach_period_repeats_cp,
1310                         X_SAME_TEACHING_PERIOD            =>     p_same_teaching_period,
1311                         X_SEQUENCE_NUM_ENROL_LOAD_CAL     =>     p_sequence_num_enrol_load_cal,
1312                         X_SEQUENCE_NUM_OFFER_LOAD_CAL     =>     p_sequence_num_offer_load_cal,
1313                         X_SPECIAL_PERMISSION_IND          =>     p_special_permission_ind,
1314                         X_STATE_FINANCIAL_AID             =>     p_state_financial_aid,
1315                         X_SUBTITLE                        =>     v_subtitle,
1316                         X_SUBTITLE_ID                     =>     p_subtitle_id,
1317                         X_SUBTITLE_MODIFIABLE_FLAG        =>     p_subtitle_modifiable_flag,
1318                         X_UNIT_TYPE_ID                    =>     p_unit_type_id,
1319                         X_WORK_LOAD_CP_LAB                =>     p_work_load_cp_lab,
1320                         X_WORK_LOAD_CP_LECTURE            =>     p_work_load_cp_lecture,
1321                         X_WORK_LOAD_OTHER                 =>     p_work_load_other,
1322                         x_claimable_hours                 =>     p_claimable_hours ,
1323                         x_auditable_ind                   =>     p_auditable_ind,
1324                         x_audit_permission_ind            =>     p_audit_permission_ind,
1325                         x_max_auditors_allowed            =>     p_max_auditors_allowed,
1326 			x_billing_credit_points           =>     p_billing_credit_points,
1327 			x_ovrd_wkld_val_flag              =>     p_ovrd_wkld_val_flag,
1328 			x_workload_val_code               =>     p_workload_val_code,
1329 			x_billing_hrs                     =>     p_billing_hrs);
1330 
1331 EXCEPTION
1332         WHEN OTHERS THEN
1333 
1334                 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1335                 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_006.crsp_ins_uv_hist');
1336                 IGS_GE_MSG_STACK.ADD;
1337                 lv_param_values := p_unit_cd||','||to_char(p_version_number)||','||fnd_date.date_to_displaydate(p_last_update_on)||','||fnd_date.date_to_displaydate(p_update_on)||','
1338                                         ||p_last_update_who||','
1339                                         ||fnd_date.date_to_displaydate(p_start_dt)||','||fnd_date.date_to_displaydate(p_review_dt)||','||fnd_date.date_to_displaydate(p_expiry_dt)||','
1340                                         ||fnd_date.date_to_displaydate(p_end_dt)||','||p_unit_status||','||p_title||','||p_short_title||
1341                                         ','||p_title_override_ind||','||p_abbreviation||','||p_unit_level||','||
1342                                         p_credit_point_descriptor||','||to_char(p_achievable_credit_points)||','||
1343                                         to_char(p_enrolled_credit_points)||','||p_supp_exam_permitted_ind||','||
1344                                         to_char(p_points_increment)||','||to_char(p_points_min)||','||to_char(p_points_max)
1345                                         ||','||p_points_override_ind ||','||to_char(p_coord_person_id )||','|| p_owner_org_unit_cd
1346                                         ||','||fnd_date.date_to_displaydate(p_owner_ou_start_dt)||','||p_award_course_only_ind ||','||p_research_unit_ind
1347                                         ||','|| p_industrial_ind
1348                                         ||','||p_practical_ind||','||p_repeatable_ind||','||p_assessable_ind||','||p_unit_int_course_level_cd||','||TO_CHAR(p_billing_credit_points)||','||p_ovrd_wkld_val_flag||','||p_workload_val_code
1349 					||',' || to_char(p_billing_hrs);
1350 
1351                 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
1352                 Fnd_Message.Set_Token('VALUE',lv_param_values);
1353                 IGS_GE_MSG_STACK.ADD;
1354                 App_Exception.Raise_Exception;
1355 END crsp_ins_uv_hist;
1356 
1357 
1358 END IGS_PS_GEN_006;