[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_DOC_DETAILS_PKG
Source
1 PACKAGE BODY igs_as_doc_details_pkg AS
2 /* $Header: IGSDI71B.pls 115.11 2004/01/16 08:40:31 kdande noship $ */
3 l_rowid VARCHAR2 (25);
4 old_references igs_as_doc_details%ROWTYPE;
5 new_references igs_as_doc_details%ROWTYPE;
6 --
7 --
8 --
9 PROCEDURE update_fees_of_remaining_items (
10 x_person_id IN NUMBER,
11 x_document_type IN VARCHAR2,
12 x_document_sub_type IN VARCHAR2
13 ) IS
14 --
15 -- Get the items whose document fee is to be re-calculated
16 --
17 CURSOR cur_items_to_be_processed IS
18 SELECT doc.ROWID,
19 doc.*
20 FROM igs_as_doc_details doc
21 WHERE doc.person_id = x_person_id
22 AND doc.document_type = x_document_type
23 AND doc.document_sub_type = x_document_sub_type
24 AND doc.item_status = 'INCOMPLETE'
25 ORDER BY creation_date;
26 --
27 l_return_status VARCHAR2 (10);
28 l_msg_data VARCHAR2 (2000);
29 l_msg_count NUMBER;
30 BEGIN
31 FOR rec_items_to_be_processed IN cur_items_to_be_processed LOOP
32 igs_as_doc_details_pkg.update_row (
33 x_rowid => rec_items_to_be_processed.ROWID,
34 x_order_number => rec_items_to_be_processed.order_number,
35 x_document_type => rec_items_to_be_processed.document_type,
36 x_document_sub_type => rec_items_to_be_processed.document_sub_type,
37 x_item_number => rec_items_to_be_processed.item_number,
38 x_item_status => rec_items_to_be_processed.item_status,
39 x_date_produced => rec_items_to_be_processed.date_produced,
40 x_incl_curr_course => rec_items_to_be_processed.incl_curr_course,
41 x_num_of_copies => rec_items_to_be_processed.num_of_copies,
42 x_comments => rec_items_to_be_processed.comments,
43 x_recip_pers_name => rec_items_to_be_processed.recip_pers_name,
44 x_recip_inst_name => rec_items_to_be_processed.recip_inst_name,
45 x_recip_addr_line_1 => rec_items_to_be_processed.recip_addr_line_1,
46 x_recip_addr_line_2 => rec_items_to_be_processed.recip_addr_line_2,
47 x_recip_addr_line_3 => rec_items_to_be_processed.recip_addr_line_3,
48 x_recip_addr_line_4 => rec_items_to_be_processed.recip_addr_line_4,
49 x_recip_city => rec_items_to_be_processed.recip_city,
50 x_recip_postal_code => rec_items_to_be_processed.recip_postal_code,
51 x_recip_state => rec_items_to_be_processed.recip_state,
52 x_recip_province => rec_items_to_be_processed.recip_province,
53 x_recip_county => rec_items_to_be_processed.recip_county,
54 x_recip_country => rec_items_to_be_processed.recip_country,
55 x_recip_fax_area_code => rec_items_to_be_processed.recip_fax_area_code,
56 x_recip_fax_country_code => rec_items_to_be_processed.recip_fax_country_code,
57 x_recip_fax_number => rec_items_to_be_processed.recip_fax_number,
58 x_delivery_method_type => rec_items_to_be_processed.delivery_method_type,
59 x_programs_on_file => rec_items_to_be_processed.programs_on_file,
60 x_missing_acad_record_data_ind => rec_items_to_be_processed.missing_acad_record_data_ind,
61 x_missing_academic_record_data => rec_items_to_be_processed.missing_academic_record_data,
62 x_send_transcript_immediately => rec_items_to_be_processed.send_transcript_immediately,
63 x_hold_release_of_final_grades => rec_items_to_be_processed.hold_release_of_final_grades,
64 x_fgrade_cal_type => rec_items_to_be_processed.fgrade_cal_type,
65 x_fgrade_seq_num => rec_items_to_be_processed.fgrade_seq_num,
66 x_hold_degree_expected => rec_items_to_be_processed.hold_degree_expected,
67 x_deghold_cal_type => rec_items_to_be_processed.deghold_cal_type,
68 x_deghold_seq_num => rec_items_to_be_processed.deghold_seq_num,
69 x_hold_for_grade_chg => rec_items_to_be_processed.hold_for_grade_chg,
70 x_special_instr => rec_items_to_be_processed.special_instr,
71 x_express_mail_type => rec_items_to_be_processed.express_mail_type,
72 x_express_mail_track_num => rec_items_to_be_processed.express_mail_track_num,
73 x_ge_certification => rec_items_to_be_processed.ge_certification,
74 x_external_comments => rec_items_to_be_processed.external_comments,
75 x_internal_comments => rec_items_to_be_processed.internal_comments,
76 x_dup_requested => rec_items_to_be_processed.dup_requested,
77 x_dup_req_date => rec_items_to_be_processed.dup_req_date,
78 x_dup_sent_date => rec_items_to_be_processed.dup_sent_date,
79 x_enr_term_cal_type => rec_items_to_be_processed.enr_term_cal_type,
80 x_enr_ci_sequence_number => rec_items_to_be_processed.enr_ci_sequence_number,
81 x_incl_attempted_hours => rec_items_to_be_processed.incl_attempted_hours,
82 x_incl_class_rank => rec_items_to_be_processed.incl_class_rank,
83 x_incl_progresssion_status => rec_items_to_be_processed.incl_progresssion_status,
84 x_incl_class_standing => rec_items_to_be_processed.incl_class_standing,
85 x_incl_cum_hours_earned => rec_items_to_be_processed.incl_cum_hours_earned,
86 x_incl_gpa => rec_items_to_be_processed.incl_gpa,
87 x_incl_date_of_graduation => rec_items_to_be_processed.incl_date_of_graduation,
88 x_incl_degree_dates => rec_items_to_be_processed.incl_degree_dates,
89 x_incl_degree_earned => rec_items_to_be_processed.incl_degree_earned,
90 x_incl_date_of_entry => rec_items_to_be_processed.incl_date_of_entry,
91 x_incl_drop_withdrawal_dates => rec_items_to_be_processed.incl_drop_withdrawal_dates,
92 x_incl_hrs_for_curr_term => 'Y',
93 x_incl_majors => rec_items_to_be_processed.incl_majors,
94 x_incl_last_date_of_enrollment => rec_items_to_be_processed.incl_last_date_of_enrollment,
95 x_incl_professional_licensure => rec_items_to_be_processed.incl_professional_licensure,
96 x_incl_college_affiliation => rec_items_to_be_processed.incl_college_affiliation,
97 x_incl_instruction_dates => rec_items_to_be_processed.incl_instruction_dates,
98 x_incl_usec_dates => rec_items_to_be_processed.incl_usec_dates,
99 x_incl_program_attempt => rec_items_to_be_processed.incl_program_attempt,
100 x_incl_attendence_type => rec_items_to_be_processed.incl_attendence_type,
101 x_incl_last_term_enrolled => rec_items_to_be_processed.incl_last_term_enrolled,
102 x_incl_ssn => rec_items_to_be_processed.incl_ssn,
103 x_incl_date_of_birth => rec_items_to_be_processed.incl_date_of_birth,
104 x_incl_disciplin_standing => rec_items_to_be_processed.incl_disciplin_standing,
105 x_incl_no_future_term => rec_items_to_be_processed.incl_no_future_term,
106 x_incl_acurat_till_copmp_dt => rec_items_to_be_processed.incl_acurat_till_copmp_dt,
107 x_incl_cant_rel_without_sign => rec_items_to_be_processed.incl_cant_rel_without_sign,
108 x_mode => 'C',
109 x_return_status => l_return_status,
110 x_msg_data => l_msg_data,
111 x_msg_count => l_msg_count,
112 x_doc_fee_per_copy => rec_items_to_be_processed.doc_fee_per_copy,
113 x_delivery_fee => rec_items_to_be_processed.delivery_fee,
114 x_recip_email => rec_items_to_be_processed.recip_email,
115 x_overridden_doc_delivery_fee => rec_items_to_be_processed.overridden_doc_delivery_fee,
116 x_overridden_document_fee => rec_items_to_be_processed.overridden_document_fee,
117 x_fee_overridden_by => rec_items_to_be_processed.fee_overridden_by,
118 x_fee_overridden_date => rec_items_to_be_processed.fee_overridden_date,
119 x_incl_department => rec_items_to_be_processed.incl_department,
120 x_incl_field_of_stdy => rec_items_to_be_processed.incl_field_of_stdy,
121 x_incl_attend_mode => rec_items_to_be_processed.incl_attend_mode,
122 x_incl_yop_acad_prd => rec_items_to_be_processed.incl_yop_acad_prd,
123 x_incl_intrmsn_st_end => rec_items_to_be_processed.incl_intrmsn_st_end,
124 x_incl_hnrs_lvl => rec_items_to_be_processed.incl_hnrs_lvl,
125 x_incl_awards => rec_items_to_be_processed.incl_awards,
126 x_incl_award_aim => rec_items_to_be_processed.incl_award_aim,
127 x_incl_acad_sessions => rec_items_to_be_processed.incl_acad_sessions,
128 x_incl_st_end_acad_ses => rec_items_to_be_processed.incl_st_end_acad_ses,
129 x_incl_hesa_num => rec_items_to_be_processed.incl_hesa_num,
130 x_incl_location => rec_items_to_be_processed.incl_location,
131 x_incl_program_type => rec_items_to_be_processed.incl_program_type,
132 x_incl_program_name => rec_items_to_be_processed.incl_program_name,
133 x_incl_prog_atmpt_stat => rec_items_to_be_processed.incl_prog_atmpt_stat,
134 x_incl_prog_atmpt_end => rec_items_to_be_processed.incl_prog_atmpt_end,
135 x_incl_prog_atmpt_strt => rec_items_to_be_processed.incl_prog_atmpt_strt,
136 x_incl_req_cmplete => rec_items_to_be_processed.incl_req_cmplete,
137 x_incl_expected_compl_dt => rec_items_to_be_processed.incl_expected_compl_dt,
138 x_incl_conferral_dt => rec_items_to_be_processed.incl_conferral_dt,
139 x_incl_thesis_title => rec_items_to_be_processed.incl_thesis_title,
140 x_incl_program_code => rec_items_to_be_processed.incl_program_code,
141 x_incl_program_ver => rec_items_to_be_processed.incl_program_ver,
142 x_incl_stud_no => rec_items_to_be_processed.incl_stud_no,
143 x_incl_surname => rec_items_to_be_processed.incl_surname,
144 x_incl_fore_name => rec_items_to_be_processed.incl_fore_name,
145 x_incl_prev_names => rec_items_to_be_processed.incl_prev_names,
146 x_incl_initials => rec_items_to_be_processed.incl_initials,
147 x_doc_purpose_code => rec_items_to_be_processed.doc_purpose_code,
148 x_plan_id => rec_items_to_be_processed.plan_id,
149 x_produced_by => rec_items_to_be_processed.produced_by,
150 x_person_id => rec_items_to_be_processed.person_id
151 );
152 END LOOP;
153 END update_fees_of_remaining_items;
154 --
155 --
156 --
157 PROCEDURE set_column_values (
158 p_action IN VARCHAR2,
159 x_rowid IN VARCHAR2,
160 x_order_number IN NUMBER,
161 x_document_type IN VARCHAR2,
162 x_document_sub_type IN VARCHAR2,
163 x_item_number IN NUMBER,
164 x_item_status IN VARCHAR2,
165 x_date_produced IN DATE,
166 x_incl_curr_course IN VARCHAR2,
167 x_num_of_copies IN NUMBER,
168 x_comments IN VARCHAR2,
169 x_recip_pers_name IN VARCHAR2,
170 x_recip_inst_name IN VARCHAR2,
171 x_recip_addr_line_1 IN VARCHAR2,
172 x_recip_addr_line_2 IN VARCHAR2,
173 x_recip_addr_line_3 IN VARCHAR2,
174 x_recip_addr_line_4 IN VARCHAR2,
175 x_recip_city IN VARCHAR2,
176 x_recip_postal_code IN VARCHAR2,
177 x_recip_state IN VARCHAR2,
178 x_recip_province IN VARCHAR2,
179 x_recip_county IN VARCHAR2,
180 x_recip_country IN VARCHAR2,
181 x_recip_fax_area_code IN VARCHAR2,
182 x_recip_fax_country_code IN VARCHAR2,
183 x_recip_fax_number IN VARCHAR2,
184 x_delivery_method_type IN VARCHAR2,
185 x_programs_on_file IN VARCHAR2,
186 x_missing_acad_record_data_ind IN VARCHAR2,
187 x_missing_academic_record_data IN VARCHAR2,
188 x_send_transcript_immediately IN VARCHAR2,
189 x_hold_release_of_final_grades IN VARCHAR2,
190 x_fgrade_cal_type IN VARCHAR2,
191 x_fgrade_seq_num IN NUMBER,
192 x_hold_degree_expected IN VARCHAR2,
193 x_deghold_cal_type IN VARCHAR2,
194 x_deghold_seq_num IN NUMBER,
195 x_hold_for_grade_chg IN VARCHAR2,
196 x_special_instr IN VARCHAR2,
197 x_express_mail_type IN VARCHAR2,
198 x_express_mail_track_num IN VARCHAR2,
199 x_ge_certification IN VARCHAR2,
200 x_external_comments IN VARCHAR2,
201 x_internal_comments IN VARCHAR2,
202 x_dup_requested IN VARCHAR2,
203 x_dup_req_date IN DATE,
204 x_dup_sent_date IN DATE,
205 x_enr_term_cal_type IN VARCHAR2,
206 x_enr_ci_sequence_number IN NUMBER,
207 x_incl_attempted_hours IN VARCHAR2,
208 x_incl_class_rank IN VARCHAR2,
209 x_incl_progresssion_status IN VARCHAR2,
210 x_incl_class_standing IN VARCHAR2,
211 x_incl_cum_hours_earned IN VARCHAR2,
212 x_incl_gpa IN VARCHAR2,
213 x_incl_date_of_graduation IN VARCHAR2,
214 x_incl_degree_dates IN VARCHAR2,
215 x_incl_degree_earned IN VARCHAR2,
216 x_incl_date_of_entry IN VARCHAR2,
217 x_incl_drop_withdrawal_dates IN VARCHAR2,
218 x_incl_hrs_for_curr_term IN VARCHAR2,
219 x_incl_majors IN VARCHAR2,
220 x_incl_last_date_of_enrollment IN VARCHAR2,
221 x_incl_professional_licensure IN VARCHAR2,
222 x_incl_college_affiliation IN VARCHAR2,
223 x_incl_instruction_dates IN VARCHAR2,
224 x_incl_usec_dates IN VARCHAR2,
225 x_incl_program_attempt IN VARCHAR2,
226 x_incl_attendence_type IN VARCHAR2,
227 x_incl_last_term_enrolled IN VARCHAR2,
228 x_incl_ssn IN VARCHAR2,
229 x_incl_date_of_birth IN VARCHAR2,
230 x_incl_disciplin_standing IN VARCHAR2,
231 x_incl_no_future_term IN VARCHAR2,
232 x_incl_acurat_till_copmp_dt IN VARCHAR2,
233 x_incl_cant_rel_without_sign IN VARCHAR2,
234 x_creation_date IN DATE,
235 x_created_by IN NUMBER,
236 x_last_update_date IN DATE,
237 x_last_updated_by IN NUMBER,
238 x_last_update_login IN NUMBER,
239 x_doc_fee_per_copy IN NUMBER,
240 x_delivery_fee IN NUMBER,
241 x_recip_email IN VARCHAR2,
242 x_overridden_doc_delivery_fee IN NUMBER,
243 x_overridden_document_fee IN NUMBER,
244 x_fee_overridden_by IN NUMBER,
245 x_fee_overridden_date IN DATE,
246 x_incl_department IN VARCHAR2,
247 x_incl_field_of_stdy IN VARCHAR2,
248 x_incl_attend_mode IN VARCHAR2,
249 x_incl_yop_acad_prd IN VARCHAR2,
250 x_incl_intrmsn_st_end IN VARCHAR2,
251 x_incl_hnrs_lvl IN VARCHAR2,
252 x_incl_awards IN VARCHAR2,
253 x_incl_award_aim IN VARCHAR2,
254 x_incl_acad_sessions IN VARCHAR2,
255 x_incl_st_end_acad_ses IN VARCHAR2,
256 x_incl_hesa_num IN VARCHAR2,
257 x_incl_location IN VARCHAR2,
258 x_incl_program_type IN VARCHAR2,
259 x_incl_program_name IN VARCHAR2,
260 x_incl_prog_atmpt_stat IN VARCHAR2,
261 x_incl_prog_atmpt_end IN VARCHAR2,
262 x_incl_prog_atmpt_strt IN VARCHAR2,
263 x_incl_req_cmplete IN VARCHAR2,
264 x_incl_expected_compl_dt IN VARCHAR2,
265 x_incl_conferral_dt IN VARCHAR2,
266 x_incl_thesis_title IN VARCHAR2,
267 x_incl_program_code IN VARCHAR2,
268 x_incl_program_ver IN VARCHAR2,
269 x_incl_stud_no IN VARCHAR2,
270 x_incl_surname IN VARCHAR2,
271 x_incl_fore_name IN VARCHAR2,
272 x_incl_prev_names IN VARCHAR2,
273 x_incl_initials IN VARCHAR2,
274 x_doc_purpose_code IN VARCHAR2,
275 x_plan_id IN NUMBER,
276 x_produced_by IN NUMBER,
277 x_person_id IN NUMBER
278 ) AS
279 /*
280 || Created By : manu.srinivasan
281 || Created On : 28-JAN-2002
282 || Purpose : Initialises the Old and New references for the columns of the table.
283 || Known limitations, enhancements or remarks :
284 || Change History :
285 || Who When What
286 || (reverse chronological order - newest change first)
287 */
288 CURSOR cur_old_ref_values IS
289 SELECT *
290 FROM igs_as_doc_details
291 WHERE ROWID = x_rowid;
292 BEGIN
293 l_rowid := x_rowid;
294 -- Code for setting the Old and New Reference Values.
295 -- Populate Old Values.
296 OPEN cur_old_ref_values;
297 FETCH cur_old_ref_values INTO old_references;
298 IF ((cur_old_ref_values%NOTFOUND)
299 AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))
300 ) THEN
301 CLOSE cur_old_ref_values;
302 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
303 fnd_msg_pub.ADD;
304 RAISE fnd_api.g_exc_error;
305 END IF;
306 CLOSE cur_old_ref_values;
307 -- Populate New Values.
308 new_references.order_number := x_order_number;
309 new_references.document_type := x_document_type;
310 new_references.document_sub_type := x_document_sub_type;
311 new_references.item_number := x_item_number;
312 new_references.item_status := x_item_status;
313 new_references.date_produced := x_date_produced;
314 new_references.incl_curr_course := x_incl_curr_course;
315 new_references.num_of_copies := x_num_of_copies;
316 new_references.comments := x_comments;
317 new_references.recip_pers_name := x_recip_pers_name;
318 new_references.recip_inst_name := x_recip_inst_name;
319 new_references.recip_addr_line_1 := x_recip_addr_line_1;
320 new_references.recip_addr_line_2 := x_recip_addr_line_2;
321 new_references.recip_addr_line_3 := x_recip_addr_line_3;
322 new_references.recip_addr_line_4 := x_recip_addr_line_4;
323 new_references.recip_city := x_recip_city;
324 new_references.recip_postal_code := x_recip_postal_code;
325 new_references.recip_state := x_recip_state;
326 new_references.recip_province := x_recip_province;
327 new_references.recip_county := x_recip_county;
328 new_references.recip_country := x_recip_country;
329 new_references.recip_fax_area_code := x_recip_fax_area_code;
330 new_references.recip_fax_country_code := x_recip_fax_country_code;
331 new_references.recip_fax_number := x_recip_fax_number;
332 new_references.delivery_method_type := x_delivery_method_type;
333 new_references.programs_on_file := x_programs_on_file;
334 new_references.missing_acad_record_data_ind := x_missing_acad_record_data_ind;
335 new_references.missing_academic_record_data := x_missing_academic_record_data;
336 new_references.send_transcript_immediately := x_send_transcript_immediately;
337 new_references.hold_release_of_final_grades := x_hold_release_of_final_grades;
338 new_references.fgrade_cal_type := x_fgrade_cal_type;
339 new_references.fgrade_seq_num := x_fgrade_seq_num;
340 new_references.hold_degree_expected := x_hold_degree_expected;
341 new_references.deghold_cal_type := x_deghold_cal_type;
342 new_references.deghold_seq_num := x_deghold_seq_num;
343 new_references.hold_for_grade_chg := x_hold_for_grade_chg;
344 new_references.special_instr := x_special_instr;
345 new_references.express_mail_type := x_express_mail_type;
346 new_references.express_mail_track_num := x_express_mail_track_num;
347 new_references.ge_certification := x_ge_certification;
348 new_references.external_comments := x_external_comments;
349 new_references.internal_comments := x_internal_comments;
350 new_references.dup_requested := x_dup_requested;
351 new_references.dup_req_date := x_dup_req_date;
352 new_references.dup_sent_date := x_dup_sent_date;
353 new_references.enr_term_cal_type := x_enr_term_cal_type;
354 new_references.enr_ci_sequence_number := x_enr_ci_sequence_number;
355 new_references.incl_attempted_hours := x_incl_attempted_hours;
356 new_references.incl_class_rank := x_incl_class_rank;
357 new_references.incl_progresssion_status := x_incl_progresssion_status;
358 new_references.incl_class_standing := x_incl_class_standing;
359 new_references.incl_cum_hours_earned := x_incl_cum_hours_earned;
360 new_references.incl_gpa := x_incl_gpa;
361 new_references.incl_date_of_graduation := x_incl_date_of_graduation;
362 new_references.incl_degree_dates := x_incl_degree_dates;
363 new_references.incl_degree_earned := x_incl_degree_earned;
364 new_references.incl_date_of_entry := x_incl_date_of_entry;
365 new_references.incl_drop_withdrawal_dates := x_incl_drop_withdrawal_dates;
366 new_references.incl_hrs_earned_for_curr_term := x_incl_hrs_for_curr_term;
367 new_references.incl_majors := x_incl_majors;
368 new_references.incl_last_date_of_enrollment := x_incl_last_date_of_enrollment;
369 new_references.incl_professional_licensure := x_incl_professional_licensure;
370 new_references.incl_college_affiliation := x_incl_college_affiliation;
371 new_references.incl_instruction_dates := x_incl_instruction_dates;
372 new_references.incl_usec_dates := x_incl_usec_dates;
373 new_references.incl_program_attempt := x_incl_program_attempt;
374 new_references.incl_attendence_type := x_incl_attendence_type;
375 new_references.incl_last_term_enrolled := x_incl_last_term_enrolled;
376 new_references.incl_ssn := x_incl_ssn;
377 new_references.incl_date_of_birth := x_incl_date_of_birth;
378 new_references.incl_disciplin_standing := x_incl_disciplin_standing;
379 new_references.incl_no_future_term := x_incl_no_future_term;
380 new_references.incl_acurat_till_copmp_dt := x_incl_acurat_till_copmp_dt;
381 new_references.incl_cant_rel_without_sign := x_incl_cant_rel_without_sign;
382 IF (p_action = 'UPDATE') THEN
383 new_references.creation_date := old_references.creation_date;
384 new_references.created_by := old_references.created_by;
385 ELSE
386 new_references.creation_date := x_creation_date;
387 new_references.created_by := x_created_by;
388 END IF;
389 new_references.last_update_date := x_last_update_date;
390 new_references.last_updated_by := x_last_updated_by;
391 new_references.last_update_login := x_last_update_login;
392 new_references.doc_fee_per_copy := x_doc_fee_per_copy;
393 new_references.delivery_fee := x_delivery_fee;
394 new_references.recip_email := x_recip_email;
395 new_references.overridden_doc_delivery_fee := x_overridden_doc_delivery_fee;
396 new_references.overridden_document_fee := x_overridden_document_fee;
397 new_references.fee_overridden_by := x_fee_overridden_by;
398 new_references.fee_overridden_date := x_fee_overridden_date;
399 new_references.incl_department := x_incl_department;
400 new_references.incl_field_of_stdy := x_incl_field_of_stdy;
401 new_references.incl_attend_mode := x_incl_attend_mode;
402 new_references.incl_yop_acad_prd := x_incl_yop_acad_prd;
403 new_references.incl_intrmsn_st_end := x_incl_intrmsn_st_end;
404 new_references.incl_hnrs_lvl := x_incl_hnrs_lvl;
405 new_references.incl_awards := x_incl_awards;
406 new_references.incl_award_aim := x_incl_award_aim;
407 new_references.incl_acad_sessions := x_incl_acad_sessions;
408 new_references.incl_st_end_acad_ses := x_incl_st_end_acad_ses;
409 new_references.incl_hesa_num := x_incl_hesa_num;
410 new_references.incl_location := x_incl_location;
411 new_references.incl_program_type := x_incl_program_type;
412 new_references.incl_program_name := x_incl_program_name;
413 new_references.incl_prog_atmpt_stat := x_incl_prog_atmpt_stat;
414 new_references.incl_prog_atmpt_end := x_incl_prog_atmpt_end;
415 new_references.incl_prog_atmpt_strt := x_incl_prog_atmpt_strt;
416 new_references.incl_req_cmplete := x_incl_req_cmplete;
417 new_references.incl_expected_compl_dt := x_incl_expected_compl_dt;
418 new_references.incl_conferral_dt := x_incl_conferral_dt;
419 new_references.incl_thesis_title := x_incl_thesis_title;
420 new_references.incl_program_code := x_incl_program_code;
421 new_references.incl_program_ver := x_incl_program_ver;
422 new_references.incl_stud_no := x_incl_stud_no;
423 new_references.incl_surname := x_incl_surname;
424 new_references.incl_fore_name := x_incl_fore_name;
425 new_references.incl_prev_names := x_incl_prev_names;
426 new_references.incl_initials := x_incl_initials;
427 new_references.doc_purpose_code := x_doc_purpose_code;
428 new_references.plan_id := x_plan_id;
429 new_references.produced_by := x_produced_by;
430 new_references.person_id := x_person_id;
431 END set_column_values;
432 --
433 --
434 --
435 PROCEDURE check_parent_existance AS
436 /*
437 || Created By : manu.srinivasan
438 || Created On : 28-JAN-2002
439 || Purpose : Checks for the existance of Parent records.
440 || Known limitations, enhancements or remarks :
441 || Change History :
442 || Who When What
443 || (reverse chronological order - newest change first)
444 */
445 BEGIN
446 IF (((old_references.delivery_method_type = new_references.delivery_method_type))
447 OR ((new_references.delivery_method_type IS NULL))
448 ) THEN
449 NULL;
450 /* ELSIF NOT igs_as_doc_dlvy_typ_pkg.get_pk_For_validation (
451 new_references.delivery_method_type
452 ) THEN
453 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
454 FND_MSG_PUB.ADD;
455 RAISE FND_API.G_EXC_ERROR;*/
456 END IF;
457 IF (((old_references.fgrade_cal_type = new_references.fgrade_cal_type)
458 AND (old_references.fgrade_seq_num = new_references.fgrade_seq_num)
459 )
460 OR ((new_references.fgrade_cal_type IS NULL)
461 OR (new_references.fgrade_seq_num IS NULL)
462 )
463 ) THEN
464 NULL;
465 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (new_references.fgrade_cal_type, new_references.fgrade_seq_num) THEN
466 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
467 fnd_msg_pub.ADD;
468 RAISE fnd_api.g_exc_error;
469 END IF;
470 IF (((old_references.deghold_cal_type = new_references.deghold_cal_type)
471 AND (old_references.deghold_seq_num = new_references.deghold_seq_num)
472 )
473 OR ((new_references.deghold_cal_type IS NULL)
474 OR (new_references.deghold_seq_num IS NULL)
475 )
476 ) THEN
477 NULL;
478 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (new_references.deghold_cal_type, new_references.deghold_seq_num) THEN
479 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
480 fnd_msg_pub.ADD;
481 RAISE fnd_api.g_exc_error;
482 END IF;
483 IF (((old_references.order_number = new_references.order_number))
484 OR ((new_references.order_number IS NULL))
485 ) THEN
486 NULL;
487 ELSIF NOT igs_as_order_hdr_pkg.get_pk_for_validation (new_references.order_number) THEN
488 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
489 fnd_msg_pub.ADD;
490 RAISE fnd_api.g_exc_error;
491 END IF;
492 END check_parent_existance;
493 --
494 --
495 --
496 FUNCTION get_pk_for_validation (x_item_number IN NUMBER)
497 RETURN BOOLEAN AS
498 /*
499 || Created By : manu.srinivasan
500 || Created On : 28-JAN-2002
501 || Purpose : Validates the Primary Key of the table.
502 || Known limitations, enhancements or remarks :
503 || Change History :
504 || Who When What
505 || (reverse chronological order - newest change first)
506 */
507 CURSOR cur_rowid IS
508 SELECT ROWID
509 FROM igs_as_doc_details
510 WHERE item_number = x_item_number
511 FOR UPDATE NOWAIT;
512 lv_rowid cur_rowid%ROWTYPE;
513 BEGIN
514 OPEN cur_rowid;
515 FETCH cur_rowid INTO lv_rowid;
516 IF (cur_rowid%FOUND) THEN
517 CLOSE cur_rowid;
518 RETURN (TRUE);
519 ELSE
520 CLOSE cur_rowid;
521 RETURN (FALSE);
522 END IF;
523 END get_pk_for_validation;
524
525 PROCEDURE get_fk_igs_as_doc_dlvy_typ (x_delivery_method_type IN VARCHAR2) AS
526 /*
527 || Created By : manu.srinivasan
528 || Created On : 28-JAN-2002
529 || Purpose : Validates the Foreign Keys for the table.
530 || Known limitations, enhancements or remarks :
531 || Change History :
532 || Who When What
533 || (reverse chronological order - newest change first)
534 */
535 CURSOR cur_rowid IS
536 SELECT ROWID
537 FROM igs_as_doc_details
538 WHERE ((delivery_method_type = x_delivery_method_type));
539 lv_rowid cur_rowid%ROWTYPE;
540 BEGIN
541 OPEN cur_rowid;
542 FETCH cur_rowid INTO lv_rowid;
543 IF (cur_rowid%FOUND) THEN
544 CLOSE cur_rowid;
545 fnd_message.set_name ('IGS', 'IGS_AS_TIINFO_TDELY_FK');
546 fnd_msg_pub.ADD;
547 RAISE fnd_api.g_exc_error;
548 RETURN;
549 END IF;
550 CLOSE cur_rowid;
551 END get_fk_igs_as_doc_dlvy_typ;
552 --
553 --
554 --
555 PROCEDURE get_fk_igs_ca_inst (x_cal_type IN VARCHAR2, x_sequence_number IN NUMBER) AS
556 /*
557 || Created By : manu.srinivasan
558 || Created On : 28-JAN-2002
559 || Purpose : Validates the Foreign Keys for the table.
560 || Known limitations, enhancements or remarks :
561 || Change History :
562 || Who When What
563 || (reverse chronological order - newest change first)
564 */
565 CURSOR cur_rowid IS
566 SELECT ROWID
567 FROM igs_as_doc_details
568 WHERE ((fgrade_cal_type = x_cal_type)
569 AND (fgrade_seq_num = x_sequence_number)
570 )
571 OR ((deghold_cal_type = x_cal_type)
572 AND (deghold_seq_num = x_sequence_number)
573 );
574 lv_rowid cur_rowid%ROWTYPE;
575 BEGIN
576 OPEN cur_rowid;
577 FETCH cur_rowid INTO lv_rowid;
578 IF (cur_rowid%FOUND) THEN
579 CLOSE cur_rowid;
580 fnd_message.set_name ('IGS', 'IGS_AS_TIINFO_CA_FK');
581 fnd_msg_pub.ADD;
582 RAISE fnd_api.g_exc_error;
583 RETURN;
584 END IF;
585 CLOSE cur_rowid;
586 END get_fk_igs_ca_inst;
587 --
588 --
589 --
590 PROCEDURE get_fk_igs_as_order_hdr (x_order_number IN NUMBER) AS
591 /*
592 || Created By : manu.srinivasan
593 || Created On : 28-JAN-2002
594 || Purpose : Validates the Foreign Keys for the table.
595 || Known limitations, enhancements or remarks :
596 || Change History :
597 || Who When What
598 || (reverse chronological order - newest change first)
599 */
600 CURSOR cur_rowid IS
601 SELECT ROWID
602 FROM igs_as_doc_details
603 WHERE ((order_number = x_order_number));
604 lv_rowid cur_rowid%ROWTYPE;
605 BEGIN
606 OPEN cur_rowid;
607 FETCH cur_rowid INTO lv_rowid;
608 IF (cur_rowid%FOUND) THEN
609 CLOSE cur_rowid;
610 fnd_message.set_name ('IGS', 'IGS_AS_TIINFO_ORDHDR_FK');
611 fnd_msg_pub.ADD;
612 RAISE fnd_api.g_exc_error;
613 RETURN;
614 END IF;
615 CLOSE cur_rowid;
616 END get_fk_igs_as_order_hdr;
617 --
618 --
619 --
620 PROCEDURE before_dml (
621 p_action IN VARCHAR2,
622 x_rowid IN VARCHAR2,
623 x_order_number IN NUMBER,
624 x_document_type IN VARCHAR2,
625 x_document_sub_type IN VARCHAR2,
626 x_item_number IN NUMBER,
627 x_item_status IN VARCHAR2,
628 x_date_produced IN DATE,
629 x_incl_curr_course IN VARCHAR2,
630 x_num_of_copies IN NUMBER,
631 x_comments IN VARCHAR2,
632 x_recip_pers_name IN VARCHAR2,
633 x_recip_inst_name IN VARCHAR2,
634 x_recip_addr_line_1 IN VARCHAR2,
635 x_recip_addr_line_2 IN VARCHAR2,
636 x_recip_addr_line_3 IN VARCHAR2,
637 x_recip_addr_line_4 IN VARCHAR2,
638 x_recip_city IN VARCHAR2,
639 x_recip_postal_code IN VARCHAR2,
640 x_recip_state IN VARCHAR2,
641 x_recip_province IN VARCHAR2,
642 x_recip_county IN VARCHAR2,
643 x_recip_country IN VARCHAR2,
644 x_recip_fax_area_code IN VARCHAR2,
645 x_recip_fax_country_code IN VARCHAR2,
646 x_recip_fax_number IN VARCHAR2,
647 x_delivery_method_type IN VARCHAR2,
648 x_programs_on_file IN VARCHAR2,
649 x_missing_acad_record_data_ind IN VARCHAR2,
650 x_missing_academic_record_data IN VARCHAR2,
651 x_send_transcript_immediately IN VARCHAR2,
652 x_hold_release_of_final_grades IN VARCHAR2,
653 x_fgrade_cal_type IN VARCHAR2,
654 x_fgrade_seq_num IN NUMBER,
655 x_hold_degree_expected IN VARCHAR2,
656 x_deghold_cal_type IN VARCHAR2,
657 x_deghold_seq_num IN NUMBER,
658 x_hold_for_grade_chg IN VARCHAR2,
659 x_special_instr IN VARCHAR2,
660 x_express_mail_type IN VARCHAR2,
661 x_express_mail_track_num IN VARCHAR2,
662 x_ge_certification IN VARCHAR2,
663 x_external_comments IN VARCHAR2,
664 x_internal_comments IN VARCHAR2,
665 x_dup_requested IN VARCHAR2,
666 x_dup_req_date IN DATE,
667 x_dup_sent_date IN DATE,
668 x_enr_term_cal_type IN VARCHAR2,
669 x_enr_ci_sequence_number IN NUMBER,
670 x_incl_attempted_hours IN VARCHAR2,
671 x_incl_class_rank IN VARCHAR2,
672 x_incl_progresssion_status IN VARCHAR2,
673 x_incl_class_standing IN VARCHAR2,
674 x_incl_cum_hours_earned IN VARCHAR2,
675 x_incl_gpa IN VARCHAR2,
676 x_incl_date_of_graduation IN VARCHAR2,
677 x_incl_degree_dates IN VARCHAR2,
678 x_incl_degree_earned IN VARCHAR2,
679 x_incl_date_of_entry IN VARCHAR2,
680 x_incl_drop_withdrawal_dates IN VARCHAR2,
681 x_incl_hrs_for_curr_term IN VARCHAR2,
682 x_incl_majors IN VARCHAR2,
683 x_incl_last_date_of_enrollment IN VARCHAR2,
684 x_incl_professional_licensure IN VARCHAR2,
685 x_incl_college_affiliation IN VARCHAR2,
686 x_incl_instruction_dates IN VARCHAR2,
687 x_incl_usec_dates IN VARCHAR2,
688 x_incl_program_attempt IN VARCHAR2,
689 x_incl_attendence_type IN VARCHAR2,
690 x_incl_last_term_enrolled IN VARCHAR2,
691 x_incl_ssn IN VARCHAR2,
692 x_incl_date_of_birth IN VARCHAR2,
693 x_incl_disciplin_standing IN VARCHAR2,
694 x_incl_no_future_term IN VARCHAR2,
695 x_incl_acurat_till_copmp_dt IN VARCHAR2,
696 x_incl_cant_rel_without_sign IN VARCHAR2,
697 x_creation_date IN DATE,
698 x_created_by IN NUMBER,
699 x_last_update_date IN DATE,
700 x_last_updated_by IN NUMBER,
701 x_last_update_login IN NUMBER,
702 x_doc_fee_per_copy IN NUMBER,
703 x_delivery_fee IN NUMBER,
704 x_recip_email IN VARCHAR2,
705 x_overridden_doc_delivery_fee IN NUMBER,
706 x_overridden_document_fee IN NUMBER,
707 x_fee_overridden_by IN NUMBER,
708 x_fee_overridden_date IN DATE,
709 x_incl_department IN VARCHAR2,
710 x_incl_field_of_stdy IN VARCHAR2,
711 x_incl_attend_mode IN VARCHAR2,
712 x_incl_yop_acad_prd IN VARCHAR2,
713 x_incl_intrmsn_st_end IN VARCHAR2,
714 x_incl_hnrs_lvl IN VARCHAR2,
715 x_incl_awards IN VARCHAR2,
716 x_incl_award_aim IN VARCHAR2,
717 x_incl_acad_sessions IN VARCHAR2,
718 x_incl_st_end_acad_ses IN VARCHAR2,
719 x_incl_hesa_num IN VARCHAR2,
720 x_incl_location IN VARCHAR2,
721 x_incl_program_type IN VARCHAR2,
722 x_incl_program_name IN VARCHAR2,
723 x_incl_prog_atmpt_stat IN VARCHAR2,
724 x_incl_prog_atmpt_end IN VARCHAR2,
725 x_incl_prog_atmpt_strt IN VARCHAR2,
726 x_incl_req_cmplete IN VARCHAR2,
727 x_incl_expected_compl_dt IN VARCHAR2,
728 x_incl_conferral_dt IN VARCHAR2,
729 x_incl_thesis_title IN VARCHAR2,
730 x_incl_program_code IN VARCHAR2,
731 x_incl_program_ver IN VARCHAR2,
732 x_incl_stud_no IN VARCHAR2,
733 x_incl_surname IN VARCHAR2,
734 x_incl_fore_name IN VARCHAR2,
735 x_incl_prev_names IN VARCHAR2,
736 x_incl_initials IN VARCHAR2,
737 x_doc_purpose_code IN VARCHAR2,
738 x_plan_id IN NUMBER,
739 x_produced_by IN NUMBER,
740 x_person_id IN NUMBER
741 ) AS
742 /*
743 || Created By : manu.srinivasan
744 || Created On : 28-JAN-2002
745 || Purpose : Initialises the columns, Checks Constraints, Calls the
746 || Trigger Handlers for the table, before any DML operation.
747 || Known limitations, enhancements or remarks :
748 || Change History :
749 || Who When What
750 || (reverse chronological order - newest change first)
751 */
752 CURSOR c_pers IS
753 SELECT person_id,
754 request_type
755 FROM igs_as_order_hdr
756 WHERE order_number = x_order_number;
757 CURSOR c_trans_fee IS
758 SELECT COUNT (*)
759 FROM igs_as_doc_details
760 WHERE order_number = x_order_number
761 AND document_sub_type = 'LIFE_TIME_TRANS';
762 l_person_id igs_as_order_hdr.person_id%TYPE;
763 l_doc_fee igs_as_doc_details.doc_fee_per_copy%TYPE;
764 l_delivery_fee igs_as_doc_details.delivery_fee%TYPE;
765 l_num_lft_in_order NUMBER;
766 l_plan_id igs_as_doc_details.plan_id%TYPE;
767 l_request_type igs_as_order_hdr.request_type%TYPE;
768 l_old_doc_fee igs_as_doc_details.doc_fee_per_copy%TYPE;
769 l_old_delivery_fee igs_as_doc_details.delivery_fee%TYPE;
770 l_old_num_of_copies igs_as_doc_details.num_of_copies%TYPE;
771 CURSOR cur_doc_and_deliv_fee IS
772 SELECT num_of_copies
773 FROM igs_as_doc_details
774 WHERE item_number = NVL (x_item_number, -1);
775 BEGIN
776 l_plan_id := x_plan_id;
777 OPEN c_pers;
778 FETCH c_pers INTO l_person_id,
779 l_request_type;
780 CLOSE c_pers;
781 OPEN c_trans_fee;
782 FETCH c_trans_fee INTO l_num_lft_in_order;
783 CLOSE c_trans_fee;
784 -- Get the delivery and document fee.
785 IF p_action <> 'DELETE' THEN
786 igs_as_ss_doc_request.get_doc_and_delivery_fee (
787 p_person_id => l_person_id,
788 p_document_type => x_document_type,
789 p_document_sub_type => x_document_sub_type,
790 p_number_of_copies => x_num_of_copies,
791 p_delivery_method_type => x_delivery_method_type,
792 p_document_fee => l_doc_fee,
793 p_delivery_fee => l_delivery_fee,
794 p_program_on_file => x_programs_on_file,
795 p_plan_id => l_plan_id,
796 p_item_number => x_item_number
797 );
798 END IF;
799 -- If it is a bulk order then make document fee = 0;
800 IF NVL (l_request_type, 'X') = 'B' THEN
801 l_doc_fee := 0;
802 l_plan_id := NULL;
803 END IF;
804 set_column_values (
805 p_action,
806 x_rowid,
807 x_order_number,
808 x_document_type,
809 x_document_sub_type,
810 x_item_number,
811 x_item_status,
812 x_date_produced,
813 x_incl_curr_course,
814 x_num_of_copies,
815 x_comments,
816 x_recip_pers_name,
817 x_recip_inst_name,
818 x_recip_addr_line_1,
819 x_recip_addr_line_2,
820 x_recip_addr_line_3,
821 x_recip_addr_line_4,
822 x_recip_city,
823 x_recip_postal_code,
824 x_recip_state,
825 x_recip_province,
826 x_recip_county,
827 x_recip_country,
828 x_recip_fax_area_code,
829 x_recip_fax_country_code,
830 x_recip_fax_number,
831 x_delivery_method_type,
832 x_programs_on_file,
833 x_missing_acad_record_data_ind,
834 x_missing_academic_record_data,
835 x_send_transcript_immediately,
836 x_hold_release_of_final_grades,
837 x_fgrade_cal_type,
838 x_fgrade_seq_num,
839 x_hold_degree_expected,
840 x_deghold_cal_type,
841 x_deghold_seq_num,
842 x_hold_for_grade_chg,
843 x_special_instr,
844 x_express_mail_type,
845 x_express_mail_track_num,
846 x_ge_certification,
847 x_external_comments,
848 x_internal_comments,
849 x_dup_requested,
850 x_dup_req_date,
851 x_dup_sent_date,
852 x_enr_term_cal_type,
853 x_enr_ci_sequence_number,
854 x_incl_attempted_hours,
855 x_incl_class_rank,
856 x_incl_progresssion_status,
857 x_incl_class_standing,
858 x_incl_cum_hours_earned,
859 x_incl_gpa,
860 x_incl_date_of_graduation,
861 x_incl_degree_dates,
862 x_incl_degree_earned,
863 x_incl_date_of_entry,
864 x_incl_drop_withdrawal_dates,
865 x_incl_hrs_for_curr_term,
866 x_incl_majors,
867 x_incl_last_date_of_enrollment,
868 x_incl_professional_licensure,
869 x_incl_college_affiliation,
870 x_incl_instruction_dates,
871 x_incl_usec_dates,
872 x_incl_program_attempt,
873 x_incl_attendence_type,
874 x_incl_last_term_enrolled,
875 x_incl_ssn,
876 x_incl_date_of_birth,
877 x_incl_disciplin_standing,
878 x_incl_no_future_term,
879 x_incl_acurat_till_copmp_dt,
880 x_incl_cant_rel_without_sign,
881 x_creation_date,
882 x_created_by,
883 x_last_update_date,
884 x_last_updated_by,
885 x_last_update_login,
886 l_doc_fee,
887 l_delivery_fee,
888 x_recip_email,
889 x_overridden_doc_delivery_fee,
890 x_overridden_document_fee,
891 x_fee_overridden_by,
892 x_fee_overridden_date,
893 x_incl_department,
894 x_incl_field_of_stdy,
895 x_incl_attend_mode,
896 x_incl_yop_acad_prd,
897 x_incl_intrmsn_st_end,
898 x_incl_hnrs_lvl,
899 x_incl_awards,
900 x_incl_award_aim,
901 x_incl_acad_sessions,
902 x_incl_st_end_acad_ses,
903 x_incl_hesa_num,
904 x_incl_location,
905 x_incl_program_type,
906 x_incl_program_name,
907 x_incl_prog_atmpt_stat,
908 x_incl_prog_atmpt_end,
909 x_incl_prog_atmpt_strt,
910 x_incl_req_cmplete,
911 x_incl_expected_compl_dt,
912 x_incl_conferral_dt,
913 x_incl_thesis_title,
914 x_incl_program_code,
915 x_incl_program_ver,
916 x_incl_stud_no,
917 x_incl_surname,
918 x_incl_fore_name,
919 x_incl_prev_names,
920 x_incl_initials,
921 x_doc_purpose_code,
922 l_plan_id,
923 x_produced_by,
924 x_person_id
925 );
926 IF (p_action = 'INSERT') THEN
927 -- Call all the procedures related to Before Insert.
928 IF (get_pk_for_validation (new_references.item_number)) THEN
929 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
930 fnd_msg_pub.ADD;
931 RAISE fnd_api.g_exc_error;
932 END IF;
933 check_parent_existance;
934 ELSIF (p_action = 'UPDATE') THEN
935 -- Call all the procedures related to Before Update.
936 check_parent_existance;
937 ELSIF (p_action = 'VALIDATE_INSERT') THEN
938 -- Call all the procedures related to Before Insert.
939 IF (get_pk_for_validation (new_references.item_number)) THEN
940 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
941 fnd_msg_pub.ADD;
942 RAISE fnd_api.g_exc_error;
943 END IF;
944 END IF;
945 END before_dml;
946 --
947 --
948 --
949 PROCEDURE insert_row (
950 x_rowid IN OUT NOCOPY VARCHAR2,
951 x_order_number IN NUMBER,
952 x_document_type IN VARCHAR2,
953 x_document_sub_type IN VARCHAR2,
954 x_item_number IN OUT NOCOPY NUMBER,
955 x_item_status IN VARCHAR2,
956 x_date_produced IN DATE,
957 x_incl_curr_course IN VARCHAR2,
958 x_num_of_copies IN NUMBER,
959 x_comments IN VARCHAR2,
960 x_recip_pers_name IN VARCHAR2,
961 x_recip_inst_name IN VARCHAR2,
962 x_recip_addr_line_1 IN VARCHAR2,
963 x_recip_addr_line_2 IN VARCHAR2,
964 x_recip_addr_line_3 IN VARCHAR2,
965 x_recip_addr_line_4 IN VARCHAR2,
966 x_recip_city IN VARCHAR2,
967 x_recip_postal_code IN VARCHAR2,
968 x_recip_state IN VARCHAR2,
969 x_recip_province IN VARCHAR2,
970 x_recip_county IN VARCHAR2,
971 x_recip_country IN VARCHAR2,
972 x_recip_fax_area_code IN VARCHAR2,
973 x_recip_fax_country_code IN VARCHAR2,
974 x_recip_fax_number IN VARCHAR2,
975 x_delivery_method_type IN VARCHAR2,
976 x_programs_on_file IN VARCHAR2,
977 x_missing_acad_record_data_ind IN VARCHAR2,
978 x_missing_academic_record_data IN VARCHAR2,
979 x_send_transcript_immediately IN VARCHAR2,
980 x_hold_release_of_final_grades IN VARCHAR2,
981 x_fgrade_cal_type IN VARCHAR2,
982 x_fgrade_seq_num IN NUMBER,
983 x_hold_degree_expected IN VARCHAR2,
984 x_deghold_cal_type IN VARCHAR2,
985 x_deghold_seq_num IN NUMBER,
986 x_hold_for_grade_chg IN VARCHAR2,
987 x_special_instr IN VARCHAR2,
988 x_express_mail_type IN VARCHAR2,
989 x_express_mail_track_num IN VARCHAR2,
990 x_ge_certification IN VARCHAR2,
991 x_external_comments IN VARCHAR2,
992 x_internal_comments IN VARCHAR2,
993 x_dup_requested IN VARCHAR2,
994 x_dup_req_date IN DATE,
995 x_dup_sent_date IN DATE,
996 x_enr_term_cal_type IN VARCHAR2,
997 x_enr_ci_sequence_number IN NUMBER,
998 x_incl_attempted_hours IN VARCHAR2,
999 x_incl_class_rank IN VARCHAR2,
1000 x_incl_progresssion_status IN VARCHAR2,
1001 x_incl_class_standing IN VARCHAR2,
1002 x_incl_cum_hours_earned IN VARCHAR2,
1003 x_incl_gpa IN VARCHAR2,
1004 x_incl_date_of_graduation IN VARCHAR2,
1005 x_incl_degree_dates IN VARCHAR2,
1006 x_incl_degree_earned IN VARCHAR2,
1007 x_incl_date_of_entry IN VARCHAR2,
1008 x_incl_drop_withdrawal_dates IN VARCHAR2,
1009 x_incl_hrs_for_curr_term IN VARCHAR2,
1010 x_incl_majors IN VARCHAR2,
1011 x_incl_last_date_of_enrollment IN VARCHAR2,
1012 x_incl_professional_licensure IN VARCHAR2,
1013 x_incl_college_affiliation IN VARCHAR2,
1014 x_incl_instruction_dates IN VARCHAR2,
1015 x_incl_usec_dates IN VARCHAR2,
1016 x_incl_program_attempt IN VARCHAR2,
1017 x_incl_attendence_type IN VARCHAR2,
1018 x_incl_last_term_enrolled IN VARCHAR2,
1019 x_incl_ssn IN VARCHAR2,
1020 x_incl_date_of_birth IN VARCHAR2,
1021 x_incl_disciplin_standing IN VARCHAR2,
1022 x_incl_no_future_term IN VARCHAR2,
1023 x_incl_acurat_till_copmp_dt IN VARCHAR2,
1024 x_incl_cant_rel_without_sign IN VARCHAR2,
1025 x_mode IN VARCHAR2,
1026 x_return_status OUT NOCOPY VARCHAR2,
1027 x_msg_data OUT NOCOPY VARCHAR2,
1028 x_msg_count OUT NOCOPY NUMBER,
1029 x_doc_fee_per_copy IN NUMBER,
1030 x_delivery_fee IN NUMBER,
1031 x_recip_email IN VARCHAR2,
1032 x_overridden_doc_delivery_fee IN NUMBER,
1033 x_overridden_document_fee IN NUMBER,
1034 x_fee_overridden_by IN NUMBER,
1035 x_fee_overridden_date IN DATE,
1036 x_incl_department IN VARCHAR2,
1037 x_incl_field_of_stdy IN VARCHAR2,
1038 x_incl_attend_mode IN VARCHAR2,
1039 x_incl_yop_acad_prd IN VARCHAR2,
1040 x_incl_intrmsn_st_end IN VARCHAR2,
1041 x_incl_hnrs_lvl IN VARCHAR2,
1042 x_incl_awards IN VARCHAR2,
1043 x_incl_award_aim IN VARCHAR2,
1044 x_incl_acad_sessions IN VARCHAR2,
1045 x_incl_st_end_acad_ses IN VARCHAR2,
1046 x_incl_hesa_num IN VARCHAR2,
1047 x_incl_location IN VARCHAR2,
1048 x_incl_program_type IN VARCHAR2,
1049 x_incl_program_name IN VARCHAR2,
1050 x_incl_prog_atmpt_stat IN VARCHAR2,
1051 x_incl_prog_atmpt_end IN VARCHAR2,
1052 x_incl_prog_atmpt_strt IN VARCHAR2,
1053 x_incl_req_cmplete IN VARCHAR2,
1054 x_incl_expected_compl_dt IN VARCHAR2,
1055 x_incl_conferral_dt IN VARCHAR2,
1056 x_incl_thesis_title IN VARCHAR2,
1057 x_incl_program_code IN VARCHAR2,
1058 x_incl_program_ver IN VARCHAR2,
1059 x_incl_stud_no IN VARCHAR2,
1060 x_incl_surname IN VARCHAR2,
1061 x_incl_fore_name IN VARCHAR2,
1062 x_incl_prev_names IN VARCHAR2,
1063 x_incl_initials IN VARCHAR2,
1064 x_doc_purpose_code IN VARCHAR2,
1065 x_plan_id IN NUMBER,
1066 x_produced_by IN NUMBER,
1067 x_person_id IN NUMBER
1068 ) AS
1069 /*
1070 || Created By : manu.srinivasan
1071 || Created On : 28-JAN-2002
1072 || Purpose : Handles the INSERT DML logic for the table.
1073 || Known limitations, enhancements or remarks :
1074 || Change History :
1075 || Who When What
1076 || (reverse chronological order - newest change first)
1077 */
1078 CURSOR c IS
1079 SELECT ROWID
1080 FROM igs_as_doc_details
1081 WHERE item_number = x_item_number;
1082 CURSOR c_other_items_in_order IS
1083 SELECT a.*,
1084 a.ROWID
1085 FROM igs_as_doc_details a
1086 WHERE order_number = x_order_number
1087 AND item_number <> x_item_number
1088 AND document_type = 'TRANSCRIPT';
1089 x_last_update_date DATE;
1090 x_last_updated_by NUMBER;
1091 x_last_update_login NUMBER;
1092 x_request_id NUMBER;
1093 x_program_id NUMBER;
1094 x_program_application_id NUMBER;
1095 x_program_update_date DATE;
1096 BEGIN
1097 fnd_msg_pub.initialize;
1098 x_last_update_date := SYSDATE;
1099 IF (x_mode = 'I') THEN
1100 x_last_updated_by := 1;
1101 x_last_update_login := 0;
1102 ELSIF (x_mode = 'R') THEN
1103 x_last_updated_by := fnd_global.user_id;
1104 IF (x_last_updated_by IS NULL) THEN
1105 x_last_updated_by := -1;
1106 END IF;
1107 x_last_update_login := fnd_global.login_id;
1108 IF (x_last_update_login IS NULL) THEN
1109 x_last_update_login := -1;
1110 END IF;
1111 x_request_id := fnd_global.conc_request_id;
1112 x_program_id := fnd_global.conc_program_id;
1113 x_program_application_id := fnd_global.prog_appl_id;
1114 IF (x_request_id = -1) THEN
1115 x_request_id := NULL;
1116 x_program_id := NULL;
1117 x_program_application_id := NULL;
1118 x_program_update_date := NULL;
1119 ELSE
1120 x_program_update_date := SYSDATE;
1121 END IF;
1122 ELSE
1123 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1124 fnd_msg_pub.ADD;
1125 RAISE fnd_api.g_exc_error;
1126 END IF;
1127 IF x_item_number IS NULL THEN
1128 SELECT igs_as_doc_details_s.NEXTVAL
1129 INTO x_item_number
1130 FROM DUAL;
1131 END IF;
1132 before_dml (
1133 p_action => 'INSERT',
1134 x_rowid => x_rowid,
1135 x_order_number => x_order_number,
1136 x_document_type => x_document_type,
1137 x_document_sub_type => x_document_sub_type,
1138 x_item_number => x_item_number,
1139 x_item_status => x_item_status,
1140 x_date_produced => x_date_produced,
1141 x_incl_curr_course => x_incl_curr_course,
1142 x_num_of_copies => x_num_of_copies,
1143 x_comments => x_comments,
1144 x_recip_pers_name => x_recip_pers_name,
1145 x_recip_inst_name => x_recip_inst_name,
1146 x_recip_addr_line_1 => x_recip_addr_line_1,
1147 x_recip_addr_line_2 => x_recip_addr_line_2,
1148 x_recip_addr_line_3 => x_recip_addr_line_3,
1149 x_recip_addr_line_4 => x_recip_addr_line_4,
1150 x_recip_city => x_recip_city,
1151 x_recip_postal_code => x_recip_postal_code,
1152 x_recip_state => x_recip_state,
1153 x_recip_province => x_recip_province,
1154 x_recip_county => x_recip_county,
1155 x_recip_country => x_recip_country,
1156 x_recip_fax_area_code => x_recip_fax_area_code,
1157 x_recip_fax_country_code => x_recip_fax_country_code,
1158 x_recip_fax_number => x_recip_fax_number,
1159 x_delivery_method_type => x_delivery_method_type,
1160 x_programs_on_file => x_programs_on_file,
1161 x_missing_acad_record_data_ind => x_missing_acad_record_data_ind,
1162 x_missing_academic_record_data => x_missing_academic_record_data,
1163 x_send_transcript_immediately => x_send_transcript_immediately,
1164 x_hold_release_of_final_grades => x_hold_release_of_final_grades,
1165 x_fgrade_cal_type => x_fgrade_cal_type,
1166 x_fgrade_seq_num => x_fgrade_seq_num,
1167 x_hold_degree_expected => x_hold_degree_expected,
1168 x_deghold_cal_type => x_deghold_cal_type,
1169 x_deghold_seq_num => x_deghold_seq_num,
1170 x_hold_for_grade_chg => x_hold_for_grade_chg,
1171 x_special_instr => x_special_instr,
1172 x_express_mail_type => x_express_mail_type,
1173 x_express_mail_track_num => x_express_mail_track_num,
1174 x_ge_certification => x_ge_certification,
1175 x_external_comments => x_external_comments,
1176 x_internal_comments => x_internal_comments,
1177 x_dup_requested => x_dup_requested,
1178 x_dup_req_date => x_dup_req_date,
1179 x_dup_sent_date => x_dup_sent_date,
1180 x_enr_term_cal_type => x_enr_term_cal_type,
1181 x_enr_ci_sequence_number => x_enr_ci_sequence_number,
1182 x_incl_attempted_hours => x_incl_attempted_hours,
1183 x_incl_class_rank => x_incl_class_rank,
1184 x_incl_progresssion_status => x_incl_progresssion_status,
1185 x_incl_class_standing => x_incl_class_standing,
1186 x_incl_cum_hours_earned => x_incl_cum_hours_earned,
1187 x_incl_gpa => x_incl_gpa,
1188 x_incl_date_of_graduation => x_incl_date_of_graduation,
1189 x_incl_degree_dates => x_incl_degree_dates,
1190 x_incl_degree_earned => x_incl_degree_earned,
1191 x_incl_date_of_entry => x_incl_date_of_entry,
1192 x_incl_drop_withdrawal_dates => x_incl_drop_withdrawal_dates,
1193 x_incl_hrs_for_curr_term => x_incl_hrs_for_curr_term,
1194 x_incl_majors => x_incl_majors,
1195 x_incl_last_date_of_enrollment => x_incl_last_date_of_enrollment,
1196 x_incl_professional_licensure => x_incl_professional_licensure,
1197 x_incl_college_affiliation => x_incl_college_affiliation,
1198 x_incl_instruction_dates => x_incl_instruction_dates,
1199 x_incl_usec_dates => x_incl_usec_dates,
1200 x_incl_program_attempt => x_incl_program_attempt,
1201 x_incl_attendence_type => x_incl_attendence_type,
1202 x_incl_last_term_enrolled => x_incl_last_term_enrolled,
1203 x_incl_ssn => x_incl_ssn,
1204 x_incl_date_of_birth => x_incl_date_of_birth,
1205 x_incl_disciplin_standing => x_incl_disciplin_standing,
1206 x_incl_no_future_term => x_incl_no_future_term,
1207 x_incl_acurat_till_copmp_dt => x_incl_acurat_till_copmp_dt,
1208 x_incl_cant_rel_without_sign => x_incl_cant_rel_without_sign,
1209 x_creation_date => x_last_update_date,
1210 x_created_by => x_last_updated_by,
1211 x_last_update_date => x_last_update_date,
1212 x_last_updated_by => x_last_updated_by,
1213 x_last_update_login => x_last_update_login,
1214 x_doc_fee_per_copy => x_doc_fee_per_copy,
1215 x_delivery_fee => x_delivery_fee,
1216 x_recip_email => x_recip_email,
1217 x_overridden_doc_delivery_fee => x_overridden_doc_delivery_fee,
1218 x_overridden_document_fee => x_overridden_document_fee,
1219 x_fee_overridden_by => x_fee_overridden_by,
1220 x_fee_overridden_date => x_fee_overridden_date,
1221 x_incl_department => x_incl_department,
1222 x_incl_field_of_stdy => x_incl_field_of_stdy,
1223 x_incl_attend_mode => x_incl_attend_mode,
1224 x_incl_yop_acad_prd => x_incl_yop_acad_prd,
1225 x_incl_intrmsn_st_end => x_incl_intrmsn_st_end,
1226 x_incl_hnrs_lvl => x_incl_hnrs_lvl,
1227 x_incl_awards => x_incl_awards,
1228 x_incl_award_aim => x_incl_award_aim,
1229 x_incl_acad_sessions => x_incl_acad_sessions,
1230 x_incl_st_end_acad_ses => x_incl_st_end_acad_ses,
1231 x_incl_hesa_num => x_incl_hesa_num,
1232 x_incl_location => x_incl_location,
1233 x_incl_program_type => x_incl_program_type,
1234 x_incl_program_name => x_incl_program_name,
1235 x_incl_prog_atmpt_stat => x_incl_prog_atmpt_stat,
1236 x_incl_prog_atmpt_end => x_incl_prog_atmpt_end,
1237 x_incl_prog_atmpt_strt => x_incl_prog_atmpt_strt,
1238 x_incl_req_cmplete => x_incl_req_cmplete,
1239 x_incl_expected_compl_dt => x_incl_expected_compl_dt,
1240 x_incl_conferral_dt => x_incl_conferral_dt,
1241 x_incl_thesis_title => x_incl_thesis_title,
1242 x_incl_program_code => x_incl_program_code,
1243 x_incl_program_ver => x_incl_program_ver,
1244 x_incl_stud_no => x_incl_stud_no,
1245 x_incl_surname => x_incl_surname,
1246 x_incl_fore_name => x_incl_fore_name,
1247 x_incl_prev_names => x_incl_prev_names,
1248 x_incl_initials => x_incl_initials,
1249 x_doc_purpose_code => x_doc_purpose_code,
1250 x_plan_id => x_plan_id,
1251 x_produced_by => x_produced_by,
1252 x_person_id => x_person_id
1253 );
1254 INSERT INTO igs_as_doc_details
1255 (order_number, document_type, document_sub_type,
1256 item_number, item_status, date_produced,
1257 incl_curr_course, num_of_copies, comments,
1258 recip_pers_name, recip_inst_name, recip_addr_line_1,
1259 recip_addr_line_2, recip_addr_line_3, recip_addr_line_4,
1260 recip_city, recip_postal_code, recip_state,
1261 recip_province, recip_county, recip_country,
1262 recip_fax_area_code, recip_fax_country_code,
1263 recip_fax_number, delivery_method_type, programs_on_file,
1264 missing_acad_record_data_ind, missing_academic_record_data,
1265 send_transcript_immediately, hold_release_of_final_grades,
1266 fgrade_cal_type, fgrade_seq_num, hold_degree_expected,
1267 deghold_cal_type, deghold_seq_num, hold_for_grade_chg,
1268 special_instr, express_mail_type, express_mail_track_num,
1269 ge_certification, external_comments, internal_comments,
1270 dup_requested, dup_req_date, dup_sent_date,
1271 enr_term_cal_type, enr_ci_sequence_number,
1272 incl_attempted_hours, incl_class_rank,
1273 incl_progresssion_status, incl_class_standing,
1274 incl_cum_hours_earned, incl_gpa, incl_date_of_graduation,
1275 incl_degree_dates, incl_degree_earned, incl_date_of_entry,
1276 incl_drop_withdrawal_dates, incl_hrs_earned_for_curr_term,
1277 incl_majors, incl_last_date_of_enrollment,
1278 incl_professional_licensure, incl_college_affiliation,
1279 incl_instruction_dates, incl_usec_dates,
1280 incl_program_attempt, incl_attendence_type,
1281 incl_last_term_enrolled, incl_ssn, incl_date_of_birth,
1282 incl_disciplin_standing, incl_no_future_term,
1283 incl_acurat_till_copmp_dt, incl_cant_rel_without_sign,
1284 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
1285 request_id, program_id, program_application_id, program_update_date,
1286 doc_fee_per_copy, delivery_fee, recip_email,
1287 overridden_doc_delivery_fee, overridden_document_fee,
1288 fee_overridden_by, fee_overridden_date, incl_department,
1289 incl_field_of_stdy, incl_attend_mode, incl_yop_acad_prd,
1290 incl_intrmsn_st_end, incl_hnrs_lvl, incl_awards,
1291 incl_award_aim, incl_acad_sessions, incl_st_end_acad_ses,
1292 incl_hesa_num, incl_location, incl_program_type,
1293 incl_program_name, incl_prog_atmpt_stat,
1294 incl_prog_atmpt_end, incl_prog_atmpt_strt,
1295 incl_req_cmplete, incl_expected_compl_dt,
1296 incl_conferral_dt, incl_thesis_title, incl_program_code,
1297 incl_program_ver, incl_stud_no, incl_surname,
1298 incl_fore_name, incl_prev_names, incl_initials,
1299 doc_purpose_code, plan_id, produced_by,
1300 person_id)
1301 VALUES (new_references.order_number, new_references.document_type, new_references.document_sub_type,
1302 new_references.item_number, new_references.item_status, new_references.date_produced,
1303 new_references.incl_curr_course, new_references.num_of_copies, new_references.comments,
1304 new_references.recip_pers_name, new_references.recip_inst_name, new_references.recip_addr_line_1,
1305 new_references.recip_addr_line_2, new_references.recip_addr_line_3, new_references.recip_addr_line_4,
1306 new_references.recip_city, new_references.recip_postal_code, new_references.recip_state,
1307 new_references.recip_province, new_references.recip_county, new_references.recip_country,
1308 new_references.recip_fax_area_code, new_references.recip_fax_country_code,
1309 new_references.recip_fax_number, new_references.delivery_method_type, new_references.programs_on_file,
1310 new_references.missing_acad_record_data_ind, new_references.missing_academic_record_data,
1311 new_references.send_transcript_immediately, new_references.hold_release_of_final_grades,
1312 new_references.fgrade_cal_type, new_references.fgrade_seq_num, new_references.hold_degree_expected,
1313 new_references.deghold_cal_type, new_references.deghold_seq_num, new_references.hold_for_grade_chg,
1314 new_references.special_instr, new_references.express_mail_type, new_references.express_mail_track_num,
1315 new_references.ge_certification, new_references.external_comments, new_references.internal_comments,
1316 new_references.dup_requested, new_references.dup_req_date, new_references.dup_sent_date,
1317 new_references.enr_term_cal_type, new_references.enr_ci_sequence_number,
1318 new_references.incl_attempted_hours, new_references.incl_class_rank,
1319 new_references.incl_progresssion_status, new_references.incl_class_standing,
1320 new_references.incl_cum_hours_earned, new_references.incl_gpa, new_references.incl_date_of_graduation,
1321 new_references.incl_degree_dates, new_references.incl_degree_earned, new_references.incl_date_of_entry,
1322 new_references.incl_drop_withdrawal_dates, new_references.incl_hrs_earned_for_curr_term,
1323 new_references.incl_majors, new_references.incl_last_date_of_enrollment,
1324 new_references.incl_professional_licensure, new_references.incl_college_affiliation,
1325 new_references.incl_instruction_dates, new_references.incl_usec_dates,
1326 new_references.incl_program_attempt, new_references.incl_attendence_type,
1327 new_references.incl_last_term_enrolled, new_references.incl_ssn, new_references.incl_date_of_birth,
1328 new_references.incl_disciplin_standing, new_references.incl_no_future_term,
1329 new_references.incl_acurat_till_copmp_dt, new_references.incl_cant_rel_without_sign,
1330 x_last_update_date, x_last_updated_by, x_last_update_date, x_last_updated_by, x_last_update_login,
1331 x_request_id, x_program_id, x_program_application_id, x_program_update_date,
1332 new_references.doc_fee_per_copy, new_references.delivery_fee, new_references.recip_email,
1333 new_references.overridden_doc_delivery_fee, new_references.overridden_document_fee,
1334 new_references.fee_overridden_by, new_references.fee_overridden_date, new_references.incl_department,
1335 new_references.incl_field_of_stdy, new_references.incl_attend_mode, new_references.incl_yop_acad_prd,
1336 new_references.incl_intrmsn_st_end, new_references.incl_hnrs_lvl, new_references.incl_awards,
1337 new_references.incl_award_aim, new_references.incl_acad_sessions, new_references.incl_st_end_acad_ses,
1338 new_references.incl_hesa_num, new_references.incl_location, new_references.incl_program_type,
1339 new_references.incl_program_name, new_references.incl_prog_atmpt_stat,
1340 new_references.incl_prog_atmpt_end, new_references.incl_prog_atmpt_strt,
1341 new_references.incl_req_cmplete, new_references.incl_expected_compl_dt,
1342 new_references.incl_conferral_dt, new_references.incl_thesis_title, new_references.incl_program_code,
1343 new_references.incl_program_ver, new_references.incl_stud_no, new_references.incl_surname,
1344 new_references.incl_fore_name, new_references.incl_prev_names, new_references.incl_initials,
1345 new_references.doc_purpose_code, new_references.plan_id, new_references.produced_by,
1346 new_references.person_id);
1347 OPEN c;
1348 FETCH c INTO x_rowid;
1349 IF (c%NOTFOUND) THEN
1350 CLOSE c;
1351 RAISE NO_DATA_FOUND;
1352 END IF;
1353 CLOSE c;
1354 --Call to update the amount in order header table
1355 igs_as_ss_doc_request.update_order_fee (
1356 p_order_number => x_order_number,
1357 p_item_number => x_item_number,
1358 p_old_sub_doc_type => NULL,
1359 p_old_deliv_type => NULL,
1360 p_old_num_copies => NULL,
1361 p_new_sub_doc_type => x_document_sub_type,
1362 p_new_deliv_type => x_delivery_method_type,
1363 p_new_num_copies => x_num_of_copies,
1364 p_return_status => x_return_status,
1365 p_msg_data => x_msg_data,
1366 p_msg_count => x_msg_count
1367 );
1368 IF NVL (x_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success THEN
1369 RETURN;
1370 END IF;
1371 -- Call procedure to insert/Update record in IGS_AS_DOC_FEE_PMNT table.
1372 IF new_references.plan_id IS NOT NULL THEN
1373 igs_as_documents_api.upd_doc_fee_pmnt (
1374 p_person_id => new_references.person_id,
1375 p_plan_id => new_references.plan_id,
1376 p_num_copies => new_references.num_of_copies,
1377 p_program_on_file => new_references.programs_on_file,
1378 p_operation => 'I'
1379 );
1380 END IF;
1381 -- Initialize API return status to success.
1382 x_return_status := fnd_api.g_ret_sts_success;
1383 -- Standard call to get message count and if count is 1, get message info
1384 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1385 EXCEPTION
1386 WHEN fnd_api.g_exc_error THEN
1387 x_return_status := fnd_api.g_ret_sts_error;
1388 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1389 RETURN;
1390 WHEN fnd_api.g_exc_unexpected_error THEN
1391 x_return_status := fnd_api.g_ret_sts_unexp_error;
1392 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1393 RETURN;
1394 WHEN OTHERS THEN
1395 x_return_status := fnd_api.g_ret_sts_unexp_error;
1396 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1397 fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
1398 fnd_msg_pub.ADD;
1399 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1400 RETURN;
1401 END insert_row;
1402 --
1403 --
1404 --
1405 PROCEDURE lock_row (
1406 x_rowid IN VARCHAR2,
1407 x_order_number IN NUMBER,
1408 x_document_type IN VARCHAR2,
1409 x_document_sub_type IN VARCHAR2,
1410 x_item_number IN NUMBER,
1411 x_item_status IN VARCHAR2,
1412 x_date_produced IN DATE,
1413 x_incl_curr_course IN VARCHAR2,
1414 x_num_of_copies IN NUMBER,
1415 x_comments IN VARCHAR2,
1416 x_recip_pers_name IN VARCHAR2,
1417 x_recip_inst_name IN VARCHAR2,
1418 x_recip_addr_line_1 IN VARCHAR2,
1419 x_recip_addr_line_2 IN VARCHAR2,
1420 x_recip_addr_line_3 IN VARCHAR2,
1421 x_recip_addr_line_4 IN VARCHAR2,
1422 x_recip_city IN VARCHAR2,
1423 x_recip_postal_code IN VARCHAR2,
1424 x_recip_state IN VARCHAR2,
1425 x_recip_province IN VARCHAR2,
1426 x_recip_county IN VARCHAR2,
1427 x_recip_country IN VARCHAR2,
1428 x_recip_fax_area_code IN VARCHAR2,
1429 x_recip_fax_country_code IN VARCHAR2,
1430 x_recip_fax_number IN VARCHAR2,
1431 x_delivery_method_type IN VARCHAR2,
1432 x_programs_on_file IN VARCHAR2,
1433 x_missing_acad_record_data_ind IN VARCHAR2,
1434 x_missing_academic_record_data IN VARCHAR2,
1435 x_send_transcript_immediately IN VARCHAR2,
1436 x_hold_release_of_final_grades IN VARCHAR2,
1437 x_fgrade_cal_type IN VARCHAR2,
1438 x_fgrade_seq_num IN NUMBER,
1439 x_hold_degree_expected IN VARCHAR2,
1440 x_deghold_cal_type IN VARCHAR2,
1441 x_deghold_seq_num IN NUMBER,
1442 x_hold_for_grade_chg IN VARCHAR2,
1443 x_special_instr IN VARCHAR2,
1444 x_express_mail_type IN VARCHAR2,
1445 x_express_mail_track_num IN VARCHAR2,
1446 x_ge_certification IN VARCHAR2,
1447 x_external_comments IN VARCHAR2,
1448 x_internal_comments IN VARCHAR2,
1449 x_dup_requested IN VARCHAR2,
1450 x_dup_req_date IN DATE,
1451 x_dup_sent_date IN DATE,
1452 x_enr_term_cal_type IN VARCHAR2,
1453 x_enr_ci_sequence_number IN NUMBER,
1454 x_incl_attempted_hours IN VARCHAR2,
1455 x_incl_class_rank IN VARCHAR2,
1456 x_incl_progresssion_status IN VARCHAR2,
1457 x_incl_class_standing IN VARCHAR2,
1458 x_incl_cum_hours_earned IN VARCHAR2,
1459 x_incl_gpa IN VARCHAR2,
1460 x_incl_date_of_graduation IN VARCHAR2,
1461 x_incl_degree_dates IN VARCHAR2,
1462 x_incl_degree_earned IN VARCHAR2,
1463 x_incl_date_of_entry IN VARCHAR2,
1464 x_incl_drop_withdrawal_dates IN VARCHAR2,
1465 x_incl_hrs_for_curr_term IN VARCHAR2,
1466 x_incl_majors IN VARCHAR2,
1467 x_incl_last_date_of_enrollment IN VARCHAR2,
1468 x_incl_professional_licensure IN VARCHAR2,
1469 x_incl_college_affiliation IN VARCHAR2,
1470 x_incl_instruction_dates IN VARCHAR2,
1471 x_incl_usec_dates IN VARCHAR2,
1472 x_incl_program_attempt IN VARCHAR2,
1473 x_incl_attendence_type IN VARCHAR2,
1474 x_incl_last_term_enrolled IN VARCHAR2,
1475 x_incl_ssn IN VARCHAR2,
1476 x_incl_date_of_birth IN VARCHAR2,
1477 x_incl_disciplin_standing IN VARCHAR2,
1478 x_incl_no_future_term IN VARCHAR2,
1479 x_incl_acurat_till_copmp_dt IN VARCHAR2,
1480 x_incl_cant_rel_without_sign IN VARCHAR2,
1481 x_return_status OUT NOCOPY VARCHAR2,
1482 x_msg_data OUT NOCOPY VARCHAR2,
1483 x_msg_count OUT NOCOPY NUMBER,
1484 x_doc_fee_per_copy IN NUMBER,
1485 x_delivery_fee IN NUMBER,
1486 x_recip_email IN VARCHAR2,
1487 x_overridden_doc_delivery_fee IN NUMBER,
1488 x_overridden_document_fee IN NUMBER,
1489 x_fee_overridden_by IN NUMBER,
1490 x_fee_overridden_date IN DATE,
1491 x_incl_department IN VARCHAR2,
1492 x_incl_field_of_stdy IN VARCHAR2,
1493 x_incl_attend_mode IN VARCHAR2,
1494 x_incl_yop_acad_prd IN VARCHAR2,
1495 x_incl_intrmsn_st_end IN VARCHAR2,
1496 x_incl_hnrs_lvl IN VARCHAR2,
1497 x_incl_awards IN VARCHAR2,
1498 x_incl_award_aim IN VARCHAR2,
1499 x_incl_acad_sessions IN VARCHAR2,
1500 x_incl_st_end_acad_ses IN VARCHAR2,
1501 x_incl_hesa_num IN VARCHAR2,
1502 x_incl_location IN VARCHAR2,
1503 x_incl_program_type IN VARCHAR2,
1504 x_incl_program_name IN VARCHAR2,
1505 x_incl_prog_atmpt_stat IN VARCHAR2,
1506 x_incl_prog_atmpt_end IN VARCHAR2,
1507 x_incl_prog_atmpt_strt IN VARCHAR2,
1508 x_incl_req_cmplete IN VARCHAR2,
1509 x_incl_expected_compl_dt IN VARCHAR2,
1510 x_incl_conferral_dt IN VARCHAR2,
1511 x_incl_thesis_title IN VARCHAR2,
1512 x_incl_program_code IN VARCHAR2,
1513 x_incl_program_ver IN VARCHAR2,
1514 x_incl_stud_no IN VARCHAR2,
1515 x_incl_surname IN VARCHAR2,
1516 x_incl_fore_name IN VARCHAR2,
1517 x_incl_prev_names IN VARCHAR2,
1518 x_incl_initials IN VARCHAR2,
1519 x_doc_purpose_code IN VARCHAR2,
1520 x_plan_id IN NUMBER,
1521 x_produced_by IN NUMBER,
1522 x_person_id IN NUMBER
1523 ) AS
1524 /*
1525 || Created By : manu.srinivasan
1526 || Created On : 28-JAN-2002
1527 || Purpose : Handles the LOCK mechanism for the table.
1528 || Known limitations, enhancements or remarks :
1529 || Change History :
1530 || Who When What
1531 || (reverse chronological order - newest change first)
1532 */
1533 CURSOR c1 IS
1534 SELECT order_number,
1535 document_type,
1536 document_sub_type,
1537 item_status,
1538 date_produced,
1539 incl_curr_course,
1540 num_of_copies,
1541 comments,
1542 recip_pers_name,
1543 recip_inst_name,
1544 recip_addr_line_1,
1545 recip_addr_line_2,
1546 recip_addr_line_3,
1547 recip_addr_line_4,
1548 recip_city,
1549 recip_postal_code,
1550 recip_state,
1551 recip_province,
1552 recip_county,
1553 recip_country,
1554 recip_fax_area_code,
1555 recip_fax_country_code,
1556 recip_fax_number,
1557 delivery_method_type,
1558 programs_on_file,
1559 missing_acad_record_data_ind,
1560 missing_academic_record_data,
1561 send_transcript_immediately,
1562 hold_release_of_final_grades,
1563 fgrade_cal_type,
1564 fgrade_seq_num,
1565 hold_degree_expected,
1566 deghold_cal_type,
1567 deghold_seq_num,
1568 hold_for_grade_chg,
1569 special_instr,
1570 express_mail_type,
1571 express_mail_track_num,
1572 ge_certification,
1573 external_comments,
1574 internal_comments,
1575 dup_requested,
1576 dup_req_date,
1577 dup_sent_date,
1578 enr_term_cal_type,
1579 enr_ci_sequence_number,
1580 incl_attempted_hours,
1581 incl_class_rank,
1582 incl_progresssion_status,
1583 incl_class_standing,
1584 incl_cum_hours_earned,
1585 incl_gpa,
1586 incl_date_of_graduation,
1587 incl_degree_dates,
1588 incl_degree_earned,
1589 incl_date_of_entry,
1590 incl_drop_withdrawal_dates,
1591 incl_hrs_earned_for_curr_term,
1592 incl_majors,
1593 incl_last_date_of_enrollment,
1594 incl_professional_licensure,
1595 incl_college_affiliation,
1596 incl_instruction_dates,
1597 incl_usec_dates,
1598 incl_program_attempt,
1599 incl_attendence_type,
1600 incl_last_term_enrolled,
1601 incl_ssn,
1602 incl_date_of_birth,
1603 incl_disciplin_standing,
1604 incl_no_future_term,
1605 incl_acurat_till_copmp_dt,
1606 incl_cant_rel_without_sign,
1607 doc_fee_per_copy,
1608 delivery_fee,
1609 recip_email,
1610 overridden_doc_delivery_fee,
1611 overridden_document_fee,
1612 fee_overridden_by,
1613 fee_overridden_date,
1614 incl_department,
1615 incl_field_of_stdy,
1616 incl_attend_mode,
1617 incl_yop_acad_prd,
1618 incl_intrmsn_st_end,
1619 incl_hnrs_lvl,
1620 incl_awards,
1621 incl_award_aim,
1622 incl_acad_sessions,
1623 incl_st_end_acad_ses,
1624 incl_hesa_num,
1625 incl_location,
1626 incl_program_type,
1627 incl_program_name,
1628 incl_prog_atmpt_stat,
1629 incl_prog_atmpt_end,
1630 incl_prog_atmpt_strt,
1631 incl_req_cmplete,
1632 incl_expected_compl_dt,
1633 incl_conferral_dt,
1634 incl_thesis_title,
1635 incl_program_code,
1636 incl_program_ver,
1637 incl_stud_no,
1638 incl_surname,
1639 incl_fore_name,
1640 incl_prev_names,
1641 incl_initials,
1642 doc_purpose_code,
1643 plan_id,
1644 produced_by,
1645 person_id
1646 FROM igs_as_doc_details
1647 WHERE ROWID = x_rowid
1648 FOR UPDATE NOWAIT;
1649 tlinfo c1%ROWTYPE;
1650 BEGIN
1651 fnd_msg_pub.initialize;
1652 OPEN c1;
1653 FETCH c1 INTO tlinfo;
1654 IF (c1%NOTFOUND) THEN
1655 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
1656 CLOSE c1;
1657 fnd_msg_pub.ADD;
1658 RAISE fnd_api.g_exc_error;
1659 RETURN;
1660 END IF;
1661 CLOSE c1;
1662 IF ((tlinfo.order_number = x_order_number)
1663 AND (tlinfo.document_type = x_document_type)
1664 AND (tlinfo.document_sub_type = x_document_sub_type)
1665 AND ((tlinfo.item_status = x_item_status)
1666 OR ((tlinfo.item_status IS NULL)
1667 AND (x_item_status IS NULL)
1668 )
1669 )
1670 AND ((tlinfo.date_produced = x_date_produced)
1671 OR ((tlinfo.date_produced IS NULL)
1672 AND (x_date_produced IS NULL)
1673 )
1674 )
1675 AND (tlinfo.incl_curr_course = x_incl_curr_course
1676 OR ((tlinfo.incl_curr_course IS NULL)
1677 AND (x_incl_curr_course IS NULL)
1678 )
1679 )
1680 AND (tlinfo.num_of_copies = x_num_of_copies)
1681 AND ((tlinfo.comments = x_comments)
1682 OR ((tlinfo.comments IS NULL)
1683 AND (x_comments IS NULL)
1684 )
1685 )
1686 AND ((tlinfo.recip_pers_name = x_recip_pers_name)
1687 OR ((tlinfo.recip_pers_name IS NULL)
1688 AND (x_recip_pers_name IS NULL)
1689 )
1690 )
1691 AND ((tlinfo.recip_inst_name = x_recip_inst_name)
1692 OR ((tlinfo.recip_inst_name IS NULL)
1693 AND (x_recip_inst_name IS NULL)
1694 )
1695 )
1696 AND (tlinfo.recip_addr_line_1 = x_recip_addr_line_1)
1697 AND ((tlinfo.recip_addr_line_2 = x_recip_addr_line_2)
1698 OR ((tlinfo.recip_addr_line_2 IS NULL)
1699 AND (x_recip_addr_line_2 IS NULL)
1700 )
1701 )
1702 AND ((tlinfo.recip_addr_line_3 = x_recip_addr_line_3)
1703 OR ((tlinfo.recip_addr_line_3 IS NULL)
1704 AND (x_recip_addr_line_3 IS NULL)
1705 )
1706 )
1707 AND ((tlinfo.recip_addr_line_4 = x_recip_addr_line_4)
1708 OR ((tlinfo.recip_addr_line_4 IS NULL)
1709 AND (x_recip_addr_line_4 IS NULL)
1710 )
1711 )
1712 AND ((tlinfo.recip_city = x_recip_city)
1713 OR ((tlinfo.recip_city IS NULL)
1714 AND (x_recip_city IS NULL)
1715 )
1716 )
1717 AND ((tlinfo.recip_postal_code = x_recip_postal_code)
1718 OR ((tlinfo.recip_postal_code IS NULL)
1719 AND (x_recip_postal_code IS NULL)
1720 )
1721 )
1722 AND ((tlinfo.recip_state = x_recip_state)
1723 OR ((tlinfo.recip_state IS NULL)
1724 AND (x_recip_state IS NULL)
1725 )
1726 )
1727 AND ((tlinfo.recip_province = x_recip_province)
1728 OR ((tlinfo.recip_province IS NULL)
1729 AND (x_recip_province IS NULL)
1730 )
1731 )
1732 AND ((tlinfo.recip_county = x_recip_county)
1733 OR ((tlinfo.recip_county IS NULL)
1734 AND (x_recip_county IS NULL)
1735 )
1736 )
1737 AND (tlinfo.recip_country = x_recip_country)
1738 AND ((tlinfo.recip_fax_area_code = x_recip_fax_area_code)
1739 OR ((tlinfo.recip_fax_area_code IS NULL)
1740 AND (x_recip_fax_area_code IS NULL)
1741 )
1742 )
1743 AND ((tlinfo.recip_fax_country_code = x_recip_fax_country_code)
1744 OR ((tlinfo.recip_fax_country_code IS NULL)
1745 AND (x_recip_fax_country_code IS NULL)
1746 )
1747 )
1748 AND ((tlinfo.recip_fax_number = x_recip_fax_number)
1749 OR ((tlinfo.recip_fax_number IS NULL)
1750 AND (x_recip_fax_number IS NULL)
1751 )
1752 )
1753 AND (tlinfo.delivery_method_type = x_delivery_method_type)
1754 AND ((tlinfo.programs_on_file = x_programs_on_file)
1755 OR ((tlinfo.programs_on_file IS NULL)
1756 AND (x_programs_on_file IS NULL)
1757 )
1758 )
1759 AND ((tlinfo.missing_acad_record_data_ind = x_missing_acad_record_data_ind)
1760 OR ((tlinfo.missing_acad_record_data_ind IS NULL)
1761 AND (x_missing_acad_record_data_ind IS NULL)
1762 )
1763 )
1764 AND ((tlinfo.missing_academic_record_data = x_missing_academic_record_data)
1765 OR ((tlinfo.missing_academic_record_data IS NULL)
1766 AND (x_missing_academic_record_data IS NULL)
1767 )
1768 )
1769 AND ((tlinfo.send_transcript_immediately = x_send_transcript_immediately)
1770 OR ((tlinfo.send_transcript_immediately IS NULL)
1771 AND (x_send_transcript_immediately IS NULL)
1772 )
1773 )
1774 AND ((tlinfo.hold_release_of_final_grades = x_hold_release_of_final_grades)
1775 OR ((tlinfo.hold_release_of_final_grades IS NULL)
1776 AND (x_hold_release_of_final_grades IS NULL)
1777 )
1778 )
1779 AND ((tlinfo.fgrade_cal_type = x_fgrade_cal_type)
1780 OR ((tlinfo.fgrade_cal_type IS NULL)
1781 AND (x_fgrade_cal_type IS NULL)
1782 )
1783 )
1784 AND ((tlinfo.fgrade_seq_num = x_fgrade_seq_num)
1785 OR ((tlinfo.fgrade_seq_num IS NULL)
1786 AND (x_fgrade_seq_num IS NULL)
1787 )
1788 )
1789 AND ((tlinfo.hold_degree_expected = x_hold_degree_expected)
1790 OR ((tlinfo.hold_degree_expected IS NULL)
1791 AND (x_hold_degree_expected IS NULL)
1792 )
1793 )
1794 AND ((tlinfo.deghold_cal_type = x_deghold_cal_type)
1795 OR ((tlinfo.deghold_cal_type IS NULL)
1796 AND (x_deghold_cal_type IS NULL)
1797 )
1798 )
1799 AND ((tlinfo.deghold_seq_num = x_deghold_seq_num)
1800 OR ((tlinfo.deghold_seq_num IS NULL)
1801 AND (x_deghold_seq_num IS NULL)
1802 )
1803 )
1804 AND ((tlinfo.hold_for_grade_chg = x_hold_for_grade_chg)
1805 OR ((tlinfo.hold_for_grade_chg IS NULL)
1806 AND (x_hold_for_grade_chg IS NULL)
1807 )
1808 )
1809 AND ((tlinfo.special_instr = x_special_instr)
1810 OR ((tlinfo.special_instr IS NULL)
1811 AND (x_special_instr IS NULL)
1812 )
1813 )
1814 AND ((tlinfo.express_mail_type = x_express_mail_type)
1815 OR ((tlinfo.express_mail_type IS NULL)
1816 AND (x_express_mail_type IS NULL)
1817 )
1818 )
1819 AND ((tlinfo.express_mail_track_num = x_express_mail_track_num)
1820 OR ((tlinfo.express_mail_track_num IS NULL)
1821 AND (x_express_mail_track_num IS NULL)
1822 )
1823 )
1824 AND ((tlinfo.ge_certification = x_ge_certification)
1825 OR ((tlinfo.ge_certification IS NULL)
1826 AND (x_ge_certification IS NULL)
1827 )
1828 )
1829 AND ((tlinfo.external_comments = x_external_comments)
1830 OR ((tlinfo.external_comments IS NULL)
1831 AND (x_external_comments IS NULL)
1832 )
1833 )
1834 AND ((tlinfo.internal_comments = x_internal_comments)
1835 OR ((tlinfo.internal_comments IS NULL)
1836 AND (x_internal_comments IS NULL)
1837 )
1838 )
1839 AND ((tlinfo.dup_requested = x_dup_requested)
1840 OR ((tlinfo.dup_requested IS NULL)
1841 AND (x_dup_requested IS NULL)
1842 )
1843 )
1844 AND ((tlinfo.dup_req_date = x_dup_req_date)
1845 OR ((tlinfo.dup_req_date IS NULL)
1846 AND (x_dup_req_date IS NULL)
1847 )
1848 )
1849 AND ((tlinfo.dup_sent_date = x_dup_sent_date)
1850 OR ((tlinfo.dup_sent_date IS NULL)
1851 AND (x_dup_sent_date IS NULL)
1852 )
1853 )
1854 AND ((tlinfo.enr_term_cal_type = x_enr_term_cal_type)
1855 OR ((tlinfo.enr_term_cal_type IS NULL)
1856 AND (x_enr_term_cal_type IS NULL)
1857 )
1858 )
1859 AND ((tlinfo.enr_ci_sequence_number = x_enr_ci_sequence_number)
1860 OR ((tlinfo.enr_ci_sequence_number IS NULL)
1861 AND (x_enr_ci_sequence_number IS NULL)
1862 )
1863 )
1864 AND ((tlinfo.incl_attempted_hours = x_incl_attempted_hours)
1865 OR ((tlinfo.incl_attempted_hours IS NULL)
1866 AND (x_incl_attempted_hours IS NULL)
1867 )
1868 )
1869 AND ((tlinfo.incl_class_rank = x_incl_class_rank)
1870 OR ((tlinfo.incl_class_rank IS NULL)
1871 AND (x_incl_class_rank IS NULL)
1872 )
1873 )
1874 AND ((tlinfo.incl_progresssion_status = x_incl_progresssion_status)
1875 OR ((tlinfo.incl_progresssion_status IS NULL)
1876 AND (x_incl_progresssion_status IS NULL)
1877 )
1878 )
1879 AND ((tlinfo.incl_class_standing = x_incl_class_standing)
1880 OR ((tlinfo.incl_class_standing IS NULL)
1881 AND (x_incl_class_standing IS NULL)
1882 )
1883 )
1884 AND ((tlinfo.incl_cum_hours_earned = x_incl_cum_hours_earned)
1885 OR ((tlinfo.incl_cum_hours_earned IS NULL)
1886 AND (x_incl_cum_hours_earned IS NULL)
1887 )
1888 )
1889 AND ((tlinfo.incl_gpa = x_incl_gpa)
1890 OR ((tlinfo.incl_gpa IS NULL)
1891 AND (x_incl_gpa IS NULL)
1892 )
1893 )
1894 AND ((tlinfo.incl_date_of_graduation = x_incl_date_of_graduation)
1895 OR ((tlinfo.incl_date_of_graduation IS NULL)
1896 AND (x_incl_date_of_graduation IS NULL)
1897 )
1898 )
1899 AND ((tlinfo.incl_degree_dates = x_incl_degree_dates)
1900 OR ((tlinfo.incl_degree_dates IS NULL)
1901 AND (x_incl_degree_dates IS NULL)
1902 )
1903 )
1904 AND ((tlinfo.incl_degree_earned = x_incl_degree_earned)
1905 OR ((tlinfo.incl_degree_earned IS NULL)
1906 AND (x_incl_degree_earned IS NULL)
1907 )
1908 )
1909 AND ((tlinfo.incl_date_of_entry = x_incl_date_of_entry)
1910 OR ((tlinfo.incl_date_of_entry IS NULL)
1911 AND (x_incl_date_of_entry IS NULL)
1912 )
1913 )
1914 AND ((tlinfo.incl_drop_withdrawal_dates = x_incl_drop_withdrawal_dates)
1915 OR ((tlinfo.incl_drop_withdrawal_dates IS NULL)
1916 AND (x_incl_drop_withdrawal_dates IS NULL)
1917 )
1918 )
1919 AND ((tlinfo.incl_hrs_earned_for_curr_term = x_incl_hrs_for_curr_term)
1920 OR ((tlinfo.incl_hrs_earned_for_curr_term IS NULL)
1921 AND (x_incl_hrs_for_curr_term IS NULL)
1922 )
1923 )
1924 AND ((tlinfo.incl_majors = x_incl_majors)
1925 OR ((tlinfo.incl_majors IS NULL)
1926 AND (x_incl_majors IS NULL)
1927 )
1928 )
1929 AND ((tlinfo.incl_last_date_of_enrollment = x_incl_last_date_of_enrollment)
1930 OR ((tlinfo.incl_last_date_of_enrollment IS NULL)
1931 AND (x_incl_last_date_of_enrollment IS NULL)
1932 )
1933 )
1934 AND ((tlinfo.incl_professional_licensure = x_incl_professional_licensure)
1935 OR ((tlinfo.incl_professional_licensure IS NULL)
1936 AND (x_incl_professional_licensure IS NULL)
1937 )
1938 )
1939 AND ((tlinfo.incl_college_affiliation = x_incl_college_affiliation)
1940 OR ((tlinfo.incl_college_affiliation IS NULL)
1941 AND (x_incl_college_affiliation IS NULL)
1942 )
1943 )
1944 AND ((tlinfo.incl_instruction_dates = x_incl_instruction_dates)
1945 OR ((tlinfo.incl_instruction_dates IS NULL)
1946 AND (x_incl_instruction_dates IS NULL)
1947 )
1948 )
1949 AND ((tlinfo.incl_usec_dates = x_incl_usec_dates)
1950 OR ((tlinfo.incl_usec_dates IS NULL)
1951 AND (x_incl_usec_dates IS NULL)
1952 )
1953 )
1954 AND ((tlinfo.incl_program_attempt = x_incl_program_attempt)
1955 OR ((tlinfo.incl_program_attempt IS NULL)
1956 AND (x_incl_program_attempt IS NULL)
1957 )
1958 )
1959 AND ((tlinfo.incl_attendence_type = x_incl_attendence_type)
1960 OR ((tlinfo.incl_attendence_type IS NULL)
1961 AND (x_incl_attendence_type IS NULL)
1962 )
1963 )
1964 AND ((tlinfo.incl_last_term_enrolled = x_incl_last_term_enrolled)
1965 OR ((tlinfo.incl_last_term_enrolled IS NULL)
1966 AND (x_incl_last_term_enrolled IS NULL)
1967 )
1968 )
1969 AND ((tlinfo.incl_ssn = x_incl_ssn)
1970 OR ((tlinfo.incl_ssn IS NULL)
1971 AND (x_incl_ssn IS NULL)
1972 )
1973 )
1974 AND ((tlinfo.incl_date_of_birth = x_incl_date_of_birth)
1975 OR ((tlinfo.incl_date_of_birth IS NULL)
1976 AND (x_incl_date_of_birth IS NULL)
1977 )
1978 )
1979 AND ((tlinfo.incl_disciplin_standing = x_incl_disciplin_standing)
1980 OR ((tlinfo.incl_disciplin_standing IS NULL)
1981 AND (x_incl_disciplin_standing IS NULL)
1982 )
1983 )
1984 AND ((tlinfo.incl_no_future_term = x_incl_no_future_term)
1985 OR ((tlinfo.incl_no_future_term IS NULL)
1986 AND (x_incl_no_future_term IS NULL)
1987 )
1988 )
1989 AND ((tlinfo.incl_acurat_till_copmp_dt = x_incl_acurat_till_copmp_dt)
1990 OR ((tlinfo.incl_acurat_till_copmp_dt IS NULL)
1991 AND (x_incl_acurat_till_copmp_dt IS NULL)
1992 )
1993 )
1994 AND ((tlinfo.incl_cant_rel_without_sign = x_incl_cant_rel_without_sign)
1995 OR ((tlinfo.incl_cant_rel_without_sign IS NULL)
1996 AND (x_incl_cant_rel_without_sign IS NULL)
1997 )
1998 )
1999 AND ((tlinfo.doc_fee_per_copy = x_doc_fee_per_copy)
2000 OR ((tlinfo.doc_fee_per_copy IS NULL)
2001 AND (x_doc_fee_per_copy IS NULL)
2002 )
2003 )
2004 AND ((tlinfo.delivery_fee = x_delivery_fee)
2005 OR ((tlinfo.delivery_fee IS NULL)
2006 AND (x_delivery_fee IS NULL)
2007 )
2008 )
2009 AND ((tlinfo.recip_email = x_recip_email)
2010 OR ((tlinfo.recip_email IS NULL)
2011 AND (x_recip_email IS NULL)
2012 )
2013 )
2014 AND ((tlinfo.overridden_doc_delivery_fee = x_overridden_doc_delivery_fee)
2015 OR ((tlinfo.overridden_doc_delivery_fee IS NULL)
2016 AND (x_overridden_doc_delivery_fee IS NULL)
2017 )
2018 )
2019 AND ((tlinfo.overridden_document_fee = x_overridden_document_fee)
2020 OR ((tlinfo.overridden_document_fee IS NULL)
2021 AND (x_overridden_document_fee IS NULL)
2022 )
2023 )
2024 AND ((tlinfo.fee_overridden_by = x_fee_overridden_by)
2025 OR ((tlinfo.fee_overridden_by IS NULL)
2026 AND (x_fee_overridden_by IS NULL)
2027 )
2028 )
2029 AND ((tlinfo.fee_overridden_date = x_fee_overridden_date)
2030 OR ((tlinfo.fee_overridden_date IS NULL)
2031 AND (x_fee_overridden_date IS NULL)
2032 )
2033 )
2034 AND ((tlinfo.incl_department = x_incl_department)
2035 OR ((tlinfo.incl_department IS NULL)
2036 AND (x_incl_department IS NULL)
2037 )
2038 )
2039 AND ((tlinfo.incl_field_of_stdy = x_incl_field_of_stdy)
2040 OR ((tlinfo.incl_field_of_stdy IS NULL)
2041 AND (x_incl_field_of_stdy IS NULL)
2042 )
2043 )
2044 AND ((tlinfo.incl_attend_mode = x_incl_attend_mode)
2045 OR ((tlinfo.incl_attend_mode IS NULL)
2046 AND (x_incl_attend_mode IS NULL)
2047 )
2048 )
2049 AND ((tlinfo.incl_yop_acad_prd = x_incl_yop_acad_prd)
2050 OR ((tlinfo.incl_yop_acad_prd IS NULL)
2051 AND (x_incl_yop_acad_prd IS NULL)
2052 )
2053 )
2054 AND ((tlinfo.incl_intrmsn_st_end = x_incl_intrmsn_st_end)
2055 OR ((tlinfo.incl_intrmsn_st_end IS NULL)
2056 AND (x_incl_intrmsn_st_end IS NULL)
2057 )
2058 )
2059 AND ((tlinfo.incl_hnrs_lvl = x_incl_hnrs_lvl)
2060 OR ((tlinfo.incl_hnrs_lvl IS NULL)
2061 AND (x_incl_hnrs_lvl IS NULL)
2062 )
2063 )
2064 AND ((tlinfo.incl_awards = x_incl_awards)
2065 OR ((tlinfo.incl_awards IS NULL)
2066 AND (x_incl_awards IS NULL)
2067 )
2068 )
2069 AND ((tlinfo.incl_award_aim = x_incl_award_aim)
2070 OR ((tlinfo.incl_award_aim IS NULL)
2071 AND (x_incl_award_aim IS NULL)
2072 )
2073 )
2074 AND ((tlinfo.incl_acad_sessions = x_incl_acad_sessions)
2075 OR ((tlinfo.incl_acad_sessions IS NULL)
2076 AND (x_incl_acad_sessions IS NULL)
2077 )
2078 )
2079 AND ((tlinfo.incl_st_end_acad_ses = x_incl_st_end_acad_ses)
2080 OR ((tlinfo.incl_st_end_acad_ses IS NULL)
2081 AND (x_incl_st_end_acad_ses IS NULL)
2082 )
2083 )
2084 AND ((tlinfo.incl_hesa_num = x_incl_hesa_num)
2085 OR ((tlinfo.incl_hesa_num IS NULL)
2086 AND (x_incl_hesa_num IS NULL)
2087 )
2088 )
2089 AND ((tlinfo.incl_location = x_incl_location)
2090 OR ((tlinfo.incl_location IS NULL)
2091 AND (x_incl_location IS NULL)
2092 )
2093 )
2094 AND ((tlinfo.incl_program_type = x_incl_program_type)
2095 OR ((tlinfo.incl_program_type IS NULL)
2096 AND (x_incl_program_type IS NULL)
2097 )
2098 )
2099 AND ((tlinfo.incl_program_name = x_incl_program_name)
2100 OR ((tlinfo.incl_program_name IS NULL)
2101 AND (x_incl_program_name IS NULL)
2102 )
2103 )
2104 AND ((tlinfo.incl_prog_atmpt_stat = x_incl_prog_atmpt_stat)
2105 OR ((tlinfo.incl_prog_atmpt_stat IS NULL)
2106 AND (x_incl_prog_atmpt_stat IS NULL)
2107 )
2108 )
2109 AND ((tlinfo.incl_prog_atmpt_end = x_incl_prog_atmpt_end)
2110 OR ((tlinfo.incl_prog_atmpt_end IS NULL)
2111 AND (x_incl_prog_atmpt_end IS NULL)
2112 )
2113 )
2114 AND ((tlinfo.incl_prog_atmpt_strt = x_incl_prog_atmpt_strt)
2115 OR ((tlinfo.incl_prog_atmpt_strt IS NULL)
2116 AND (x_incl_prog_atmpt_strt IS NULL)
2117 )
2118 )
2119 AND ((tlinfo.incl_req_cmplete = x_incl_req_cmplete)
2120 OR ((tlinfo.incl_req_cmplete IS NULL)
2121 AND (x_incl_req_cmplete IS NULL)
2122 )
2123 )
2124 AND ((tlinfo.incl_expected_compl_dt = x_incl_expected_compl_dt)
2125 OR ((tlinfo.incl_expected_compl_dt IS NULL)
2126 AND (x_incl_expected_compl_dt IS NULL)
2127 )
2128 )
2129 AND ((tlinfo.incl_conferral_dt = x_incl_conferral_dt)
2130 OR ((tlinfo.incl_conferral_dt IS NULL)
2131 AND (x_incl_conferral_dt IS NULL)
2132 )
2133 )
2134 AND ((tlinfo.incl_thesis_title = x_incl_thesis_title)
2135 OR ((tlinfo.incl_thesis_title IS NULL)
2136 AND (x_incl_thesis_title IS NULL)
2137 )
2138 )
2139 AND ((tlinfo.incl_program_code = x_incl_program_code)
2140 OR ((tlinfo.incl_program_code IS NULL)
2141 AND (x_incl_program_code IS NULL)
2142 )
2143 )
2144 AND ((tlinfo.incl_program_ver = x_incl_program_ver)
2145 OR ((tlinfo.incl_program_ver IS NULL)
2146 AND (x_incl_program_ver IS NULL)
2147 )
2148 )
2149 AND ((tlinfo.incl_stud_no = x_incl_stud_no)
2150 OR ((tlinfo.incl_stud_no IS NULL)
2151 AND (x_incl_stud_no IS NULL)
2152 )
2153 )
2154 AND ((tlinfo.incl_surname = x_incl_surname)
2155 OR ((tlinfo.incl_surname IS NULL)
2156 AND (x_incl_surname IS NULL)
2157 )
2158 )
2159 AND ((tlinfo.incl_fore_name = x_incl_fore_name)
2160 OR ((tlinfo.incl_fore_name IS NULL)
2161 AND (x_incl_fore_name IS NULL)
2162 )
2163 )
2164 AND ((tlinfo.incl_prev_names = x_incl_prev_names)
2165 OR ((tlinfo.incl_prev_names IS NULL)
2166 AND (x_incl_prev_names IS NULL)
2167 )
2168 )
2169 AND ((tlinfo.incl_initials = x_incl_initials)
2170 OR ((tlinfo.incl_initials IS NULL)
2171 AND (x_incl_initials IS NULL)
2172 )
2173 )
2174 AND ((tlinfo.doc_purpose_code = x_doc_purpose_code)
2175 OR ((tlinfo.doc_purpose_code IS NULL)
2176 AND (x_doc_purpose_code IS NULL)
2177 )
2178 )
2179 AND ((tlinfo.plan_id = x_plan_id)
2180 OR ((tlinfo.plan_id IS NULL)
2181 AND (x_plan_id IS NULL)
2182 )
2183 )
2184 AND ((tlinfo.produced_by = x_produced_by)
2185 OR ((tlinfo.produced_by IS NULL)
2186 AND (x_produced_by IS NULL)
2187 )
2188 )
2189 AND ((tlinfo.person_id = x_person_id)
2190 OR ((tlinfo.person_id IS NULL)
2191 AND (x_person_id IS NULL)
2192 )
2193 )
2194 ) THEN
2195 NULL;
2196 ELSE
2197 fnd_message.set_name ('FND', '*' || x_rowid || '*');
2198 fnd_msg_pub.ADD;
2199 RAISE fnd_api.g_exc_error;
2200 END IF;
2201 -- Initialize API return status to success.
2202 x_return_status := fnd_api.g_ret_sts_success;
2203 -- Standard call to get message count and if count is 1, get message info
2204 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2205 EXCEPTION
2206 WHEN fnd_api.g_exc_error THEN
2207 x_return_status := fnd_api.g_ret_sts_error;
2208 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2209 RETURN;
2210 WHEN fnd_api.g_exc_unexpected_error THEN
2211 x_return_status := fnd_api.g_ret_sts_unexp_error;
2212 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2213 RETURN;
2214 WHEN OTHERS THEN
2215 x_return_status := fnd_api.g_ret_sts_unexp_error;
2216 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2217 fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
2218 fnd_msg_pub.ADD;
2219 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2220 RETURN;
2221 END lock_row;
2222 --
2223 --
2224 --
2225 PROCEDURE update_row (
2226 x_rowid IN VARCHAR2,
2227 x_order_number IN NUMBER,
2228 x_document_type IN VARCHAR2,
2229 x_document_sub_type IN VARCHAR2,
2230 x_item_number IN NUMBER,
2231 x_item_status IN VARCHAR2,
2232 x_date_produced IN DATE,
2233 x_incl_curr_course IN VARCHAR2,
2234 x_num_of_copies IN NUMBER,
2235 x_comments IN VARCHAR2,
2236 x_recip_pers_name IN VARCHAR2,
2237 x_recip_inst_name IN VARCHAR2,
2238 x_recip_addr_line_1 IN VARCHAR2,
2239 x_recip_addr_line_2 IN VARCHAR2,
2240 x_recip_addr_line_3 IN VARCHAR2,
2241 x_recip_addr_line_4 IN VARCHAR2,
2242 x_recip_city IN VARCHAR2,
2243 x_recip_postal_code IN VARCHAR2,
2244 x_recip_state IN VARCHAR2,
2245 x_recip_province IN VARCHAR2,
2246 x_recip_county IN VARCHAR2,
2247 x_recip_country IN VARCHAR2,
2248 x_recip_fax_area_code IN VARCHAR2,
2249 x_recip_fax_country_code IN VARCHAR2,
2250 x_recip_fax_number IN VARCHAR2,
2251 x_delivery_method_type IN VARCHAR2,
2252 x_programs_on_file IN VARCHAR2,
2253 x_missing_acad_record_data_ind IN VARCHAR2,
2254 x_missing_academic_record_data IN VARCHAR2,
2255 x_send_transcript_immediately IN VARCHAR2,
2256 x_hold_release_of_final_grades IN VARCHAR2,
2257 x_fgrade_cal_type IN VARCHAR2,
2258 x_fgrade_seq_num IN NUMBER,
2259 x_hold_degree_expected IN VARCHAR2,
2260 x_deghold_cal_type IN VARCHAR2,
2261 x_deghold_seq_num IN NUMBER,
2262 x_hold_for_grade_chg IN VARCHAR2,
2263 x_special_instr IN VARCHAR2,
2264 x_express_mail_type IN VARCHAR2,
2265 x_express_mail_track_num IN VARCHAR2,
2266 x_ge_certification IN VARCHAR2,
2267 x_external_comments IN VARCHAR2,
2268 x_internal_comments IN VARCHAR2,
2269 x_dup_requested IN VARCHAR2,
2270 x_dup_req_date IN DATE,
2271 x_dup_sent_date IN DATE,
2272 x_enr_term_cal_type IN VARCHAR2,
2273 x_enr_ci_sequence_number IN NUMBER,
2274 x_incl_attempted_hours IN VARCHAR2,
2275 x_incl_class_rank IN VARCHAR2,
2276 x_incl_progresssion_status IN VARCHAR2,
2277 x_incl_class_standing IN VARCHAR2,
2278 x_incl_cum_hours_earned IN VARCHAR2,
2279 x_incl_gpa IN VARCHAR2,
2280 x_incl_date_of_graduation IN VARCHAR2,
2281 x_incl_degree_dates IN VARCHAR2,
2282 x_incl_degree_earned IN VARCHAR2,
2283 x_incl_date_of_entry IN VARCHAR2,
2284 x_incl_drop_withdrawal_dates IN VARCHAR2,
2285 x_incl_hrs_for_curr_term IN VARCHAR2,
2286 x_incl_majors IN VARCHAR2,
2287 x_incl_last_date_of_enrollment IN VARCHAR2,
2288 x_incl_professional_licensure IN VARCHAR2,
2289 x_incl_college_affiliation IN VARCHAR2,
2290 x_incl_instruction_dates IN VARCHAR2,
2291 x_incl_usec_dates IN VARCHAR2,
2292 x_incl_program_attempt IN VARCHAR2,
2293 x_incl_attendence_type IN VARCHAR2,
2294 x_incl_last_term_enrolled IN VARCHAR2,
2295 x_incl_ssn IN VARCHAR2,
2296 x_incl_date_of_birth IN VARCHAR2,
2297 x_incl_disciplin_standing IN VARCHAR2,
2298 x_incl_no_future_term IN VARCHAR2,
2299 x_incl_acurat_till_copmp_dt IN VARCHAR2,
2300 x_incl_cant_rel_without_sign IN VARCHAR2,
2301 x_mode IN VARCHAR2,
2302 x_return_status OUT NOCOPY VARCHAR2,
2303 x_msg_data OUT NOCOPY VARCHAR2,
2304 x_msg_count OUT NOCOPY NUMBER,
2305 x_doc_fee_per_copy IN NUMBER,
2306 x_delivery_fee IN NUMBER,
2307 x_recip_email IN VARCHAR2,
2308 x_overridden_doc_delivery_fee IN NUMBER,
2309 x_overridden_document_fee IN NUMBER,
2310 x_fee_overridden_by IN NUMBER,
2311 x_fee_overridden_date IN DATE,
2312 x_incl_department IN VARCHAR2,
2313 x_incl_field_of_stdy IN VARCHAR2,
2314 x_incl_attend_mode IN VARCHAR2,
2315 x_incl_yop_acad_prd IN VARCHAR2,
2316 x_incl_intrmsn_st_end IN VARCHAR2,
2317 x_incl_hnrs_lvl IN VARCHAR2,
2318 x_incl_awards IN VARCHAR2,
2319 x_incl_award_aim IN VARCHAR2,
2320 x_incl_acad_sessions IN VARCHAR2,
2321 x_incl_st_end_acad_ses IN VARCHAR2,
2322 x_incl_hesa_num IN VARCHAR2,
2323 x_incl_location IN VARCHAR2,
2324 x_incl_program_type IN VARCHAR2,
2325 x_incl_program_name IN VARCHAR2,
2326 x_incl_prog_atmpt_stat IN VARCHAR2,
2327 x_incl_prog_atmpt_end IN VARCHAR2,
2328 x_incl_prog_atmpt_strt IN VARCHAR2,
2329 x_incl_req_cmplete IN VARCHAR2,
2330 x_incl_expected_compl_dt IN VARCHAR2,
2331 x_incl_conferral_dt IN VARCHAR2,
2332 x_incl_thesis_title IN VARCHAR2,
2333 x_incl_program_code IN VARCHAR2,
2334 x_incl_program_ver IN VARCHAR2,
2335 x_incl_stud_no IN VARCHAR2,
2336 x_incl_surname IN VARCHAR2,
2337 x_incl_fore_name IN VARCHAR2,
2338 x_incl_prev_names IN VARCHAR2,
2339 x_incl_initials IN VARCHAR2,
2340 x_doc_purpose_code IN VARCHAR2,
2341 x_plan_id IN NUMBER,
2342 x_produced_by IN NUMBER,
2343 x_person_id IN NUMBER
2344 ) AS
2345 /*
2346 || Created By : manu.srinivasan
2347 || Created On : 28-JAN-2002
2348 || Purpose : Handles the UPDATE DML logic for the table.
2349 || Known limitations, enhancements or remarks :
2350 || Change History :
2351 || Who When What
2352 || (reverse chronological order - newest change first)
2353 */
2354 x_last_update_date DATE;
2355 x_last_updated_by NUMBER;
2356 x_last_update_login NUMBER;
2357 x_request_id NUMBER;
2358 x_program_id NUMBER;
2359 x_program_application_id NUMBER;
2360 x_program_update_date DATE;
2361 BEGIN
2362 fnd_msg_pub.initialize;
2363 x_last_update_date := SYSDATE;
2364 IF (x_mode = 'I') THEN
2365 x_last_updated_by := 1;
2366 x_last_update_login := 0;
2367 ELSIF (x_mode IN ('R', 'C')) THEN
2368 x_last_updated_by := fnd_global.user_id;
2369 IF x_last_updated_by IS NULL THEN
2370 x_last_updated_by := -1;
2371 END IF;
2372 x_last_update_login := fnd_global.login_id;
2373 IF (x_last_update_login IS NULL) THEN
2374 x_last_update_login := -1;
2375 END IF;
2376 ELSE
2377 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
2378 fnd_msg_pub.ADD;
2379 RAISE fnd_api.g_exc_error;
2380 END IF;
2381 before_dml (
2382 p_action => 'UPDATE',
2383 x_rowid => x_rowid,
2384 x_order_number => x_order_number,
2385 x_document_type => x_document_type,
2386 x_document_sub_type => x_document_sub_type,
2387 x_item_number => x_item_number,
2388 x_item_status => x_item_status,
2389 x_date_produced => x_date_produced,
2390 x_incl_curr_course => x_incl_curr_course,
2391 x_num_of_copies => x_num_of_copies,
2392 x_comments => x_comments,
2393 x_recip_pers_name => x_recip_pers_name,
2394 x_recip_inst_name => x_recip_inst_name,
2395 x_recip_addr_line_1 => x_recip_addr_line_1,
2396 x_recip_addr_line_2 => x_recip_addr_line_2,
2397 x_recip_addr_line_3 => x_recip_addr_line_3,
2398 x_recip_addr_line_4 => x_recip_addr_line_4,
2399 x_recip_city => x_recip_city,
2400 x_recip_postal_code => x_recip_postal_code,
2401 x_recip_state => x_recip_state,
2402 x_recip_province => x_recip_province,
2403 x_recip_county => x_recip_county,
2404 x_recip_country => x_recip_country,
2405 x_recip_fax_area_code => x_recip_fax_area_code,
2406 x_recip_fax_country_code => x_recip_fax_country_code,
2407 x_recip_fax_number => x_recip_fax_number,
2408 x_delivery_method_type => x_delivery_method_type,
2409 x_programs_on_file => x_programs_on_file,
2410 x_missing_acad_record_data_ind => x_missing_acad_record_data_ind,
2411 x_missing_academic_record_data => x_missing_academic_record_data,
2412 x_send_transcript_immediately => x_send_transcript_immediately,
2413 x_hold_release_of_final_grades => x_hold_release_of_final_grades,
2414 x_fgrade_cal_type => x_fgrade_cal_type,
2415 x_fgrade_seq_num => x_fgrade_seq_num,
2416 x_hold_degree_expected => x_hold_degree_expected,
2417 x_deghold_cal_type => x_deghold_cal_type,
2418 x_deghold_seq_num => x_deghold_seq_num,
2419 x_hold_for_grade_chg => x_hold_for_grade_chg,
2420 x_special_instr => x_special_instr,
2421 x_express_mail_type => x_express_mail_type,
2422 x_express_mail_track_num => x_express_mail_track_num,
2423 x_ge_certification => x_ge_certification,
2424 x_external_comments => x_external_comments,
2425 x_internal_comments => x_internal_comments,
2426 x_dup_requested => x_dup_requested,
2427 x_dup_req_date => x_dup_req_date,
2428 x_dup_sent_date => x_dup_sent_date,
2429 x_enr_term_cal_type => x_enr_term_cal_type,
2430 x_enr_ci_sequence_number => x_enr_ci_sequence_number,
2431 x_incl_attempted_hours => x_incl_attempted_hours,
2432 x_incl_class_rank => x_incl_class_rank,
2433 x_incl_progresssion_status => x_incl_progresssion_status,
2434 x_incl_class_standing => x_incl_class_standing,
2435 x_incl_cum_hours_earned => x_incl_cum_hours_earned,
2436 x_incl_gpa => x_incl_gpa,
2437 x_incl_date_of_graduation => x_incl_date_of_graduation,
2438 x_incl_degree_dates => x_incl_degree_dates,
2439 x_incl_degree_earned => x_incl_degree_earned,
2440 x_incl_date_of_entry => x_incl_date_of_entry,
2441 x_incl_drop_withdrawal_dates => x_incl_drop_withdrawal_dates,
2442 x_incl_hrs_for_curr_term => x_incl_hrs_for_curr_term,
2443 x_incl_majors => x_incl_majors,
2444 x_incl_last_date_of_enrollment => x_incl_last_date_of_enrollment,
2445 x_incl_professional_licensure => x_incl_professional_licensure,
2446 x_incl_college_affiliation => x_incl_college_affiliation,
2447 x_incl_instruction_dates => x_incl_instruction_dates,
2448 x_incl_usec_dates => x_incl_usec_dates,
2449 x_incl_program_attempt => x_incl_program_attempt,
2450 x_incl_attendence_type => x_incl_attendence_type,
2451 x_incl_last_term_enrolled => x_incl_last_term_enrolled,
2452 x_incl_ssn => x_incl_ssn,
2453 x_incl_date_of_birth => x_incl_date_of_birth,
2454 x_incl_disciplin_standing => x_incl_disciplin_standing,
2455 x_incl_no_future_term => x_incl_no_future_term,
2456 x_incl_acurat_till_copmp_dt => x_incl_acurat_till_copmp_dt,
2457 x_incl_cant_rel_without_sign => x_incl_cant_rel_without_sign,
2458 x_creation_date => x_last_update_date,
2459 x_created_by => x_last_updated_by,
2460 x_last_update_date => x_last_update_date,
2461 x_last_updated_by => x_last_updated_by,
2462 x_last_update_login => x_last_update_login,
2463 x_doc_fee_per_copy => x_doc_fee_per_copy,
2464 x_delivery_fee => x_delivery_fee,
2465 x_recip_email => x_recip_email,
2466 x_overridden_doc_delivery_fee => x_overridden_doc_delivery_fee,
2467 x_overridden_document_fee => x_overridden_document_fee,
2468 x_fee_overridden_by => x_fee_overridden_by,
2469 x_fee_overridden_date => x_fee_overridden_date,
2470 x_incl_department => x_incl_department,
2471 x_incl_field_of_stdy => x_incl_field_of_stdy,
2472 x_incl_attend_mode => x_incl_attend_mode,
2473 x_incl_yop_acad_prd => x_incl_yop_acad_prd,
2474 x_incl_intrmsn_st_end => x_incl_intrmsn_st_end,
2475 x_incl_hnrs_lvl => x_incl_hnrs_lvl,
2476 x_incl_awards => x_incl_awards,
2477 x_incl_award_aim => x_incl_award_aim,
2478 x_incl_acad_sessions => x_incl_acad_sessions,
2479 x_incl_st_end_acad_ses => x_incl_st_end_acad_ses,
2480 x_incl_hesa_num => x_incl_hesa_num,
2481 x_incl_location => x_incl_location,
2482 x_incl_program_type => x_incl_program_type,
2483 x_incl_program_name => x_incl_program_name,
2484 x_incl_prog_atmpt_stat => x_incl_prog_atmpt_stat,
2485 x_incl_prog_atmpt_end => x_incl_prog_atmpt_end,
2486 x_incl_prog_atmpt_strt => x_incl_prog_atmpt_strt,
2487 x_incl_req_cmplete => x_incl_req_cmplete,
2488 x_incl_expected_compl_dt => x_incl_expected_compl_dt,
2489 x_incl_conferral_dt => x_incl_conferral_dt,
2490 x_incl_thesis_title => x_incl_thesis_title,
2491 x_incl_program_code => x_incl_program_code,
2492 x_incl_program_ver => x_incl_program_ver,
2493 x_incl_stud_no => x_incl_stud_no,
2494 x_incl_surname => x_incl_surname,
2495 x_incl_fore_name => x_incl_fore_name,
2496 x_incl_prev_names => x_incl_prev_names,
2497 x_incl_initials => x_incl_initials,
2498 x_doc_purpose_code => x_doc_purpose_code,
2499 x_plan_id => x_plan_id,
2500 x_produced_by => x_produced_by,
2501 x_person_id => x_person_id
2502 );
2503 IF (x_mode = 'R') THEN
2504 x_request_id := fnd_global.conc_request_id;
2505 x_program_id := fnd_global.conc_program_id;
2506 x_program_application_id := fnd_global.prog_appl_id;
2507 IF (x_request_id = -1) THEN
2508 x_request_id := old_references.request_id;
2509 x_program_id := old_references.program_id;
2510 x_program_application_id := old_references.program_application_id;
2511 x_program_update_date := old_references.program_update_date;
2512 ELSE
2513 x_program_update_date := SYSDATE;
2514 END IF;
2515 END IF;
2516 UPDATE igs_as_doc_details
2517 SET order_number = new_references.order_number,
2518 document_type = new_references.document_type,
2519 document_sub_type = new_references.document_sub_type,
2520 item_status = new_references.item_status,
2521 date_produced = new_references.date_produced,
2522 incl_curr_course = new_references.incl_curr_course,
2523 num_of_copies = new_references.num_of_copies,
2524 comments = new_references.comments,
2525 recip_pers_name = new_references.recip_pers_name,
2526 recip_inst_name = new_references.recip_inst_name,
2527 recip_addr_line_1 = new_references.recip_addr_line_1,
2528 recip_addr_line_2 = new_references.recip_addr_line_2,
2529 recip_addr_line_3 = new_references.recip_addr_line_3,
2530 recip_addr_line_4 = new_references.recip_addr_line_4,
2531 recip_city = new_references.recip_city,
2532 recip_postal_code = new_references.recip_postal_code,
2533 recip_state = new_references.recip_state,
2534 recip_province = new_references.recip_province,
2535 recip_county = new_references.recip_county,
2536 recip_country = new_references.recip_country,
2537 recip_fax_area_code = new_references.recip_fax_area_code,
2538 recip_fax_country_code = new_references.recip_fax_country_code,
2539 recip_fax_number = new_references.recip_fax_number,
2540 delivery_method_type = new_references.delivery_method_type,
2541 programs_on_file = new_references.programs_on_file,
2542 missing_acad_record_data_ind = new_references.missing_acad_record_data_ind,
2543 missing_academic_record_data = new_references.missing_academic_record_data,
2544 send_transcript_immediately = new_references.send_transcript_immediately,
2545 hold_release_of_final_grades = new_references.hold_release_of_final_grades,
2546 fgrade_cal_type = new_references.fgrade_cal_type,
2547 fgrade_seq_num = new_references.fgrade_seq_num,
2548 hold_degree_expected = new_references.hold_degree_expected,
2549 deghold_cal_type = new_references.deghold_cal_type,
2550 deghold_seq_num = new_references.deghold_seq_num,
2551 hold_for_grade_chg = new_references.hold_for_grade_chg,
2552 special_instr = new_references.special_instr,
2553 express_mail_type = new_references.express_mail_type,
2554 express_mail_track_num = new_references.express_mail_track_num,
2555 ge_certification = new_references.ge_certification,
2556 external_comments = new_references.external_comments,
2557 internal_comments = new_references.internal_comments,
2558 dup_requested = new_references.dup_requested,
2559 dup_req_date = new_references.dup_req_date,
2560 dup_sent_date = new_references.dup_sent_date,
2561 enr_term_cal_type = new_references.enr_term_cal_type,
2562 enr_ci_sequence_number = new_references.enr_ci_sequence_number,
2563 incl_attempted_hours = new_references.incl_attempted_hours,
2564 incl_class_rank = new_references.incl_class_rank,
2565 incl_progresssion_status = new_references.incl_progresssion_status,
2566 incl_class_standing = new_references.incl_class_standing,
2567 incl_cum_hours_earned = new_references.incl_cum_hours_earned,
2568 incl_gpa = new_references.incl_gpa,
2569 incl_date_of_graduation = new_references.incl_date_of_graduation,
2570 incl_degree_dates = new_references.incl_degree_dates,
2571 incl_degree_earned = new_references.incl_degree_earned,
2572 incl_date_of_entry = new_references.incl_date_of_entry,
2573 incl_drop_withdrawal_dates = new_references.incl_drop_withdrawal_dates,
2574 incl_hrs_earned_for_curr_term = new_references.incl_hrs_earned_for_curr_term,
2575 incl_majors = new_references.incl_majors,
2576 incl_last_date_of_enrollment = new_references.incl_last_date_of_enrollment,
2577 incl_professional_licensure = new_references.incl_professional_licensure,
2578 incl_college_affiliation = new_references.incl_college_affiliation,
2579 incl_instruction_dates = new_references.incl_instruction_dates,
2580 incl_usec_dates = new_references.incl_usec_dates,
2581 incl_program_attempt = new_references.incl_program_attempt,
2582 incl_attendence_type = new_references.incl_attendence_type,
2583 incl_last_term_enrolled = new_references.incl_last_term_enrolled,
2584 incl_ssn = new_references.incl_ssn,
2585 incl_date_of_birth = new_references.incl_date_of_birth,
2586 incl_disciplin_standing = new_references.incl_disciplin_standing,
2587 incl_no_future_term = new_references.incl_no_future_term,
2588 incl_acurat_till_copmp_dt = new_references.incl_acurat_till_copmp_dt,
2589 incl_cant_rel_without_sign = new_references.incl_cant_rel_without_sign,
2590 last_update_date = x_last_update_date,
2591 last_updated_by = x_last_updated_by,
2592 last_update_login = x_last_update_login,
2593 request_id = x_request_id,
2594 program_id = x_program_id,
2595 program_application_id = x_program_application_id,
2596 program_update_date = x_program_update_date,
2597 doc_fee_per_copy = new_references.doc_fee_per_copy,
2598 delivery_fee = new_references.delivery_fee,
2599 recip_email = new_references.recip_email,
2600 overridden_doc_delivery_fee = new_references.overridden_doc_delivery_fee,
2601 overridden_document_fee = new_references.overridden_document_fee,
2602 fee_overridden_by = new_references.fee_overridden_by,
2603 fee_overridden_date = new_references.fee_overridden_date,
2604 incl_department = new_references.incl_department,
2605 incl_field_of_stdy = new_references.incl_field_of_stdy,
2606 incl_attend_mode = new_references.incl_attend_mode,
2607 incl_yop_acad_prd = new_references.incl_yop_acad_prd,
2608 incl_intrmsn_st_end = new_references.incl_intrmsn_st_end,
2609 incl_hnrs_lvl = new_references.incl_hnrs_lvl,
2610 incl_awards = new_references.incl_awards,
2611 incl_award_aim = new_references.incl_award_aim,
2612 incl_acad_sessions = new_references.incl_acad_sessions,
2613 incl_st_end_acad_ses = new_references.incl_st_end_acad_ses,
2614 incl_hesa_num = new_references.incl_hesa_num,
2615 incl_location = new_references.incl_location,
2616 incl_program_type = new_references.incl_program_type,
2617 incl_program_name = new_references.incl_program_name,
2618 incl_prog_atmpt_stat = new_references.incl_prog_atmpt_stat,
2619 incl_prog_atmpt_end = new_references.incl_prog_atmpt_end,
2620 incl_prog_atmpt_strt = new_references.incl_prog_atmpt_strt,
2621 incl_req_cmplete = new_references.incl_req_cmplete,
2622 incl_expected_compl_dt = new_references.incl_expected_compl_dt,
2623 incl_conferral_dt = new_references.incl_conferral_dt,
2624 incl_thesis_title = new_references.incl_thesis_title,
2625 incl_program_code = new_references.incl_program_code,
2626 incl_program_ver = new_references.incl_program_ver,
2627 incl_stud_no = new_references.incl_stud_no,
2628 incl_surname = new_references.incl_surname,
2629 incl_fore_name = new_references.incl_fore_name,
2630 incl_prev_names = new_references.incl_prev_names,
2631 incl_initials = new_references.incl_initials,
2632 doc_purpose_code = new_references.doc_purpose_code,
2633 plan_id = new_references.plan_id,
2634 produced_by = new_references.produced_by,
2635 person_id = new_references.person_id
2636 WHERE ROWID = x_rowid;
2637 IF (SQL%NOTFOUND) THEN
2638 RAISE NO_DATA_FOUND;
2639 END IF;
2640 IF (x_mode = 'R') THEN
2641 update_fees_of_remaining_items (
2642 x_person_id => old_references.person_id,
2643 x_document_type => old_references.document_type,
2644 x_document_sub_type => old_references.document_sub_type
2645 );
2646 END IF;
2647 --
2648 -- Send a notification to the student informing about the document production
2649 -- when the produced_by and date_produced are entered by the user.
2650 --
2651 IF ((new_references.missing_acad_record_data_ind = 'Y')
2652 AND (old_references.produced_by <> new_references.produced_by)
2653 AND (old_references.date_produced <> new_references.date_produced)
2654 ) THEN
2655 DECLARE
2656 CURSOR cur_document_type (cp_document_type IN VARCHAR2) IS
2657 SELECT lkup.meaning
2658 FROM igs_lookups_view lkup
2659 WHERE lkup.lookup_type = 'IGS_AS_DOCUMENT_TYPE'
2660 AND lkup.lookup_code = cp_document_type;
2661 CURSOR cur_document_sub_type (cp_document_sub_type IN VARCHAR2) IS
2662 SELECT lkup.meaning
2663 FROM igs_lookups_view lkup
2664 WHERE lkup.lookup_type = 'IGS_AS_DOCUMENT_SUB_TYPE'
2665 AND lkup.lookup_code = cp_document_sub_type;
2666 CURSOR cur_delivery_method (cp_delivery_method_type IN VARCHAR2) IS
2667 SELECT description
2668 FROM igs_as_doc_dlvy_typ
2669 WHERE delivery_method_type = cp_delivery_method_type;
2670 rec_document_type cur_document_type%ROWTYPE;
2671 rec_document_sub_type cur_document_sub_type%ROWTYPE;
2672 rec_delivery_method cur_delivery_method%ROWTYPE;
2673 BEGIN
2674 OPEN cur_delivery_method (new_references.delivery_method_type);
2675 FETCH cur_delivery_method INTO rec_delivery_method;
2676 CLOSE cur_delivery_method;
2677 OPEN cur_document_type (new_references.document_type);
2678 FETCH cur_document_type INTO rec_document_type;
2679 CLOSE cur_document_type;
2680 OPEN cur_document_sub_type (new_references.document_sub_type);
2681 FETCH cur_document_sub_type INTO rec_document_sub_type;
2682 CLOSE cur_document_sub_type;
2683 igs_as_prod_doc.notify_miss_acad_rec_prod (
2684 p_person_id => new_references.person_id,
2685 p_order_number => new_references.order_number,
2686 p_item_number => new_references.item_number,
2687 p_document_type => rec_document_type.meaning || ' - ' || rec_document_sub_type.meaning,
2688 p_recipient_name => new_references.recip_pers_name,
2689 p_receiving_inst_name => new_references.recip_inst_name,
2690 p_delivery_method => rec_delivery_method.description,
2691 p_fulfillment_date_time => new_references.date_produced
2692 );
2693 END;
2694 END IF;
2695 --Call to update the amount in order header table
2696 igs_as_ss_doc_request.update_order_fee (
2697 p_order_number => x_order_number,
2698 p_item_number => x_item_number,
2699 p_old_sub_doc_type => old_references.document_sub_type,
2700 p_old_deliv_type => old_references.delivery_method_type,
2701 p_old_num_copies => old_references.num_of_copies,
2702 p_new_sub_doc_type => x_document_sub_type,
2703 p_new_deliv_type => x_delivery_method_type,
2704 p_new_num_copies => x_num_of_copies,
2705 p_return_status => x_return_status,
2706 p_msg_data => x_msg_data,
2707 p_msg_count => x_msg_count
2708 );
2709 IF NVL (x_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success THEN
2710 RETURN;
2711 END IF;
2712 -- Call procedure to insert/Update record in IGS_AS_DOC_FEE_PMNT table.
2713 IF new_references.plan_id IS NOT NULL THEN
2714 igs_as_documents_api.upd_doc_fee_pmnt (
2715 p_person_id => new_references.person_id,
2716 p_plan_id => new_references.plan_id,
2717 p_num_copies => new_references.num_of_copies, --(new_references.num_of_copies - old_references.num_of_copies),
2718 p_program_on_file => new_references.programs_on_file,
2719 p_operation => 'U'
2720 );
2721 END IF;
2722 IF (new_references.missing_acad_record_data_ind = 'Y') THEN
2723 IF (old_references.item_status = 'INCOMPLETE'
2724 AND new_references.item_status = 'INPROCESS'
2725 ) THEN
2726 --
2727 -- Added code to fix bug# 3039150
2728 --
2729 DECLARE
2730 CURSOR cur_admin_to_notify IS
2731 SELECT administrator_id
2732 FROM igs_as_docproc_stup;
2733 CURSOR cur_person_details (cp_party_id IN NUMBER) IS
2734 SELECT party_number,
2735 party_name
2736 FROM hz_parties
2737 WHERE party_id = cp_party_id;
2738 l_admin_to_notify igs_as_docproc_stup.administrator_id%TYPE;
2739 rec_person_details cur_person_details%ROWTYPE;
2740 BEGIN
2741 OPEN cur_admin_to_notify;
2742 FETCH cur_admin_to_notify INTO l_admin_to_notify;
2743 CLOSE cur_admin_to_notify;
2744 IF (l_admin_to_notify IS NOT NULL) THEN
2745 OPEN cur_person_details (new_references.person_id);
2746 FETCH cur_person_details INTO rec_person_details;
2747 CLOSE cur_person_details;
2748 igs_as_notify_student.wf_launch_as007 (
2749 p_user => l_admin_to_notify,
2750 p_stud_id => new_references.person_id,
2751 p_stud_number => rec_person_details.party_number,
2752 p_stud_name => rec_person_details.party_name,
2753 p_order_number => new_references.order_number,
2754 p_item_number => new_references.item_number
2755 );
2756 END IF;
2757 END;
2758 END IF;
2759 END IF;
2760 IF (old_references.item_status = 'INPROCESS'
2761 AND new_references.item_status = 'PROCESSED'
2762 ) THEN
2763 --
2764 -- Update the Duplicate Documents History record (if any for the item) when the item is PROCESSED.
2765 --
2766 DECLARE
2767 CURSOR cur_hist_update_needed (cp_order_number IN NUMBER, cp_item_number IN NUMBER) IS
2768 SELECT dd.ROWID,
2769 dd.*
2770 FROM igs_as_dup_docs dd
2771 WHERE dd.order_number = cp_order_number
2772 AND dd.item_number = cp_item_number
2773 AND dd.fulfilled_by IS NULL;
2774 BEGIN
2775 FOR rec_hist_update_needed IN cur_hist_update_needed (new_references.order_number, new_references.item_number) LOOP
2776 igs_as_dup_docs_pkg.update_row (
2777 x_rowid => rec_hist_update_needed.ROWID,
2778 x_order_number => rec_hist_update_needed.order_number,
2779 x_item_number => rec_hist_update_needed.item_number,
2780 x_requested_by => rec_hist_update_needed.requested_by,
2781 x_requested_date => rec_hist_update_needed.requested_date,
2782 x_fulfilled_by => new_references.produced_by,
2783 x_fulfilled_date => new_references.date_produced,
2784 x_return_status => x_return_status,
2785 x_msg_data => x_msg_data,
2786 x_msg_count => x_msg_count
2787 );
2788 END LOOP;
2789 END;
2790 END IF;
2791 -- Initialize API return status to success.
2792 x_return_status := fnd_api.g_ret_sts_success;
2793 -- Standard call to get message count and if count is 1, get message info
2794 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2795 EXCEPTION
2796 WHEN fnd_api.g_exc_error THEN
2797 x_return_status := fnd_api.g_ret_sts_error;
2798 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2799 RETURN;
2800 WHEN fnd_api.g_exc_unexpected_error THEN
2801 x_return_status := fnd_api.g_ret_sts_unexp_error;
2802 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2803 RETURN;
2804 WHEN OTHERS THEN
2805 x_return_status := fnd_api.g_ret_sts_unexp_error;
2806 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
2807 fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
2808 fnd_msg_pub.ADD;
2809 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2810 RETURN;
2811 END update_row;
2812 --
2813 --
2814 --
2815 PROCEDURE add_row (
2816 x_rowid IN OUT NOCOPY VARCHAR2,
2817 x_order_number IN NUMBER,
2818 x_document_type IN VARCHAR2,
2819 x_document_sub_type IN VARCHAR2,
2820 x_item_number IN OUT NOCOPY NUMBER,
2821 x_item_status IN VARCHAR2,
2822 x_date_produced IN DATE,
2823 x_incl_curr_course IN VARCHAR2,
2824 x_num_of_copies IN NUMBER,
2825 x_comments IN VARCHAR2,
2826 x_recip_pers_name IN VARCHAR2,
2827 x_recip_inst_name IN VARCHAR2,
2828 x_recip_addr_line_1 IN VARCHAR2,
2829 x_recip_addr_line_2 IN VARCHAR2,
2830 x_recip_addr_line_3 IN VARCHAR2,
2831 x_recip_addr_line_4 IN VARCHAR2,
2832 x_recip_city IN VARCHAR2,
2833 x_recip_postal_code IN VARCHAR2,
2834 x_recip_state IN VARCHAR2,
2835 x_recip_province IN VARCHAR2,
2836 x_recip_county IN VARCHAR2,
2837 x_recip_country IN VARCHAR2,
2838 x_recip_fax_area_code IN VARCHAR2,
2839 x_recip_fax_country_code IN VARCHAR2,
2840 x_recip_fax_number IN VARCHAR2,
2841 x_delivery_method_type IN VARCHAR2,
2842 x_programs_on_file IN VARCHAR2,
2843 x_missing_acad_record_data_ind IN VARCHAR2,
2844 x_missing_academic_record_data IN VARCHAR2,
2845 x_send_transcript_immediately IN VARCHAR2,
2846 x_hold_release_of_final_grades IN VARCHAR2,
2847 x_fgrade_cal_type IN VARCHAR2,
2848 x_fgrade_seq_num IN NUMBER,
2849 x_hold_degree_expected IN VARCHAR2,
2850 x_deghold_cal_type IN VARCHAR2,
2851 x_deghold_seq_num IN NUMBER,
2852 x_hold_for_grade_chg IN VARCHAR2,
2853 x_special_instr IN VARCHAR2,
2854 x_express_mail_type IN VARCHAR2,
2855 x_express_mail_track_num IN VARCHAR2,
2856 x_ge_certification IN VARCHAR2,
2857 x_external_comments IN VARCHAR2,
2858 x_internal_comments IN VARCHAR2,
2859 x_dup_requested IN VARCHAR2,
2860 x_dup_req_date IN DATE,
2861 x_dup_sent_date IN DATE,
2862 x_enr_term_cal_type IN VARCHAR2,
2863 x_enr_ci_sequence_number IN NUMBER,
2864 x_incl_attempted_hours IN VARCHAR2,
2865 x_incl_class_rank IN VARCHAR2,
2866 x_incl_progresssion_status IN VARCHAR2,
2867 x_incl_class_standing IN VARCHAR2,
2868 x_incl_cum_hours_earned IN VARCHAR2,
2869 x_incl_gpa IN VARCHAR2,
2870 x_incl_date_of_graduation IN VARCHAR2,
2871 x_incl_degree_dates IN VARCHAR2,
2872 x_incl_degree_earned IN VARCHAR2,
2873 x_incl_date_of_entry IN VARCHAR2,
2874 x_incl_drop_withdrawal_dates IN VARCHAR2,
2875 x_incl_hrs_for_curr_term IN VARCHAR2,
2876 x_incl_majors IN VARCHAR2,
2877 x_incl_last_date_of_enrollment IN VARCHAR2,
2878 x_incl_professional_licensure IN VARCHAR2,
2879 x_incl_college_affiliation IN VARCHAR2,
2880 x_incl_instruction_dates IN VARCHAR2,
2881 x_incl_usec_dates IN VARCHAR2,
2882 x_incl_program_attempt IN VARCHAR2,
2883 x_incl_attendence_type IN VARCHAR2,
2884 x_incl_last_term_enrolled IN VARCHAR2,
2885 x_incl_ssn IN VARCHAR2,
2886 x_incl_date_of_birth IN VARCHAR2,
2887 x_incl_disciplin_standing IN VARCHAR2,
2888 x_incl_no_future_term IN VARCHAR2,
2889 x_incl_acurat_till_copmp_dt IN VARCHAR2,
2890 x_incl_cant_rel_without_sign IN VARCHAR2,
2891 x_mode IN VARCHAR2,
2892 x_doc_fee_per_copy IN NUMBER,
2893 x_delivery_fee IN NUMBER,
2894 x_recip_email IN VARCHAR2,
2895 x_overridden_doc_delivery_fee IN NUMBER,
2896 x_overridden_document_fee IN NUMBER,
2897 x_fee_overridden_by IN NUMBER,
2898 x_fee_overridden_date IN DATE,
2899 x_incl_department IN VARCHAR2,
2900 x_incl_field_of_stdy IN VARCHAR2,
2901 x_incl_attend_mode IN VARCHAR2,
2902 x_incl_yop_acad_prd IN VARCHAR2,
2903 x_incl_intrmsn_st_end IN VARCHAR2,
2904 x_incl_hnrs_lvl IN VARCHAR2,
2905 x_incl_awards IN VARCHAR2,
2906 x_incl_award_aim IN VARCHAR2,
2907 x_incl_acad_sessions IN VARCHAR2,
2908 x_incl_st_end_acad_ses IN VARCHAR2,
2909 x_incl_hesa_num IN VARCHAR2,
2910 x_incl_location IN VARCHAR2,
2911 x_incl_program_type IN VARCHAR2,
2912 x_incl_program_name IN VARCHAR2,
2913 x_incl_prog_atmpt_stat IN VARCHAR2,
2914 x_incl_prog_atmpt_end IN VARCHAR2,
2915 x_incl_prog_atmpt_strt IN VARCHAR2,
2916 x_incl_req_cmplete IN VARCHAR2,
2917 x_incl_expected_compl_dt IN VARCHAR2,
2918 x_incl_conferral_dt IN VARCHAR2,
2919 x_incl_thesis_title IN VARCHAR2,
2920 x_incl_program_code IN VARCHAR2,
2921 x_incl_program_ver IN VARCHAR2,
2922 x_incl_stud_no IN VARCHAR2,
2923 x_incl_surname IN VARCHAR2,
2924 x_incl_fore_name IN VARCHAR2,
2925 x_incl_prev_names IN VARCHAR2,
2926 x_incl_initials IN VARCHAR2,
2927 x_doc_purpose_code IN VARCHAR2,
2928 x_plan_id IN NUMBER,
2929 x_produced_by IN NUMBER,
2930 x_person_id IN NUMBER
2931 ) AS
2932 /*
2933 || Created By : manu.srinivasan
2934 || Created On : 28-JAN-2002
2935 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
2936 || Known limitations, enhancements or remarks :
2937 || Change History :
2938 || Who When What
2939 || (reverse chronological order - newest change first)
2940 */
2941 CURSOR c1 IS
2942 SELECT ROWID
2943 FROM igs_as_doc_details
2944 WHERE item_number = x_item_number;
2945 l_return_status VARCHAR2 (10);
2946 l_msg_data VARCHAR2 (2000);
2947 l_msg_count NUMBER (10);
2948 BEGIN
2949 OPEN c1;
2950 FETCH c1 INTO x_rowid;
2951 IF (c1%NOTFOUND) THEN
2952 CLOSE c1;
2953 insert_row (
2954 x_rowid,
2955 x_order_number,
2956 x_document_type,
2957 x_document_sub_type,
2958 x_item_number,
2959 x_item_status,
2960 x_date_produced,
2961 x_incl_curr_course,
2962 x_num_of_copies,
2963 x_comments,
2964 x_recip_pers_name,
2965 x_recip_inst_name,
2966 x_recip_addr_line_1,
2967 x_recip_addr_line_2,
2968 x_recip_addr_line_3,
2969 x_recip_addr_line_4,
2970 x_recip_city,
2971 x_recip_postal_code,
2972 x_recip_state,
2973 x_recip_province,
2974 x_recip_county,
2975 x_recip_country,
2976 x_recip_fax_area_code,
2977 x_recip_fax_country_code,
2978 x_recip_fax_number,
2979 x_delivery_method_type,
2980 x_programs_on_file,
2981 x_missing_acad_record_data_ind,
2982 x_missing_academic_record_data,
2983 x_send_transcript_immediately,
2984 x_hold_release_of_final_grades,
2985 x_fgrade_cal_type,
2986 x_fgrade_seq_num,
2987 x_hold_degree_expected,
2988 x_deghold_cal_type,
2989 x_deghold_seq_num,
2990 x_hold_for_grade_chg,
2991 x_special_instr,
2992 x_express_mail_type,
2993 x_express_mail_track_num,
2994 x_ge_certification,
2995 x_external_comments,
2996 x_internal_comments,
2997 x_dup_requested,
2998 x_dup_req_date,
2999 x_dup_sent_date,
3000 x_enr_term_cal_type,
3001 x_enr_ci_sequence_number,
3002 x_incl_attempted_hours,
3003 x_incl_class_rank,
3004 x_incl_progresssion_status,
3005 x_incl_class_standing,
3006 x_incl_cum_hours_earned,
3007 x_incl_gpa,
3008 x_incl_date_of_graduation,
3009 x_incl_degree_dates,
3010 x_incl_degree_earned,
3011 x_incl_date_of_entry,
3012 x_incl_drop_withdrawal_dates,
3013 x_incl_hrs_for_curr_term,
3014 x_incl_majors,
3015 x_incl_last_date_of_enrollment,
3016 x_incl_professional_licensure,
3017 x_incl_college_affiliation,
3018 x_incl_instruction_dates,
3019 x_incl_usec_dates,
3020 x_incl_program_attempt,
3021 x_incl_attendence_type,
3022 x_incl_last_term_enrolled,
3023 x_incl_ssn,
3024 x_incl_date_of_birth,
3025 x_incl_disciplin_standing,
3026 x_incl_no_future_term,
3027 x_incl_acurat_till_copmp_dt,
3028 x_incl_cant_rel_without_sign,
3029 x_mode,
3030 l_return_status,
3031 l_msg_data,
3032 l_msg_count,
3033 x_doc_fee_per_copy,
3034 x_delivery_fee,
3035 x_recip_email,
3036 x_overridden_doc_delivery_fee,
3037 x_overridden_document_fee,
3038 x_fee_overridden_by,
3039 x_fee_overridden_date,
3040 x_incl_department,
3041 x_incl_field_of_stdy,
3042 x_incl_attend_mode,
3043 x_incl_yop_acad_prd,
3044 x_incl_intrmsn_st_end,
3045 x_incl_hnrs_lvl,
3046 x_incl_awards,
3047 x_incl_award_aim,
3048 x_incl_acad_sessions,
3049 x_incl_st_end_acad_ses,
3050 x_incl_hesa_num,
3051 x_incl_location,
3052 x_incl_program_type,
3053 x_incl_program_name,
3054 x_incl_prog_atmpt_stat,
3055 x_incl_prog_atmpt_end,
3056 x_incl_prog_atmpt_strt,
3057 x_incl_req_cmplete,
3058 x_incl_expected_compl_dt,
3059 x_incl_conferral_dt,
3060 x_incl_thesis_title,
3061 x_incl_program_code,
3062 x_incl_program_ver,
3063 x_incl_stud_no,
3064 x_incl_surname,
3065 x_incl_fore_name,
3066 x_incl_prev_names,
3067 x_incl_initials,
3068 x_doc_purpose_code,
3069 x_plan_id,
3070 x_produced_by,
3071 x_person_id
3072 );
3073 RETURN;
3074 END IF;
3075 CLOSE c1;
3076 update_row (
3077 x_rowid,
3078 x_order_number,
3079 x_document_type,
3080 x_document_sub_type,
3081 x_item_number,
3082 x_item_status,
3083 x_date_produced,
3084 x_incl_curr_course,
3085 x_num_of_copies,
3086 x_comments,
3087 x_recip_pers_name,
3088 x_recip_inst_name,
3089 x_recip_addr_line_1,
3090 x_recip_addr_line_2,
3091 x_recip_addr_line_3,
3092 x_recip_addr_line_4,
3093 x_recip_city,
3094 x_recip_postal_code,
3095 x_recip_state,
3096 x_recip_province,
3097 x_recip_county,
3098 x_recip_country,
3099 x_recip_fax_area_code,
3100 x_recip_fax_country_code,
3101 x_recip_fax_number,
3102 x_delivery_method_type,
3103 x_programs_on_file,
3104 x_missing_acad_record_data_ind,
3105 x_missing_academic_record_data,
3106 x_send_transcript_immediately,
3107 x_hold_release_of_final_grades,
3108 x_fgrade_cal_type,
3109 x_fgrade_seq_num,
3110 x_hold_degree_expected,
3111 x_deghold_cal_type,
3112 x_deghold_seq_num,
3113 x_hold_for_grade_chg,
3114 x_special_instr,
3115 x_express_mail_type,
3116 x_express_mail_track_num,
3117 x_ge_certification,
3118 x_external_comments,
3119 x_internal_comments,
3120 x_dup_requested,
3121 x_dup_req_date,
3122 x_dup_sent_date,
3123 x_enr_term_cal_type,
3124 x_enr_ci_sequence_number,
3125 x_incl_attempted_hours,
3126 x_incl_class_rank,
3127 x_incl_progresssion_status,
3128 x_incl_class_standing,
3129 x_incl_cum_hours_earned,
3130 x_incl_gpa,
3131 x_incl_date_of_graduation,
3132 x_incl_degree_dates,
3133 x_incl_degree_earned,
3134 x_incl_date_of_entry,
3135 x_incl_drop_withdrawal_dates,
3136 x_incl_hrs_for_curr_term,
3137 x_incl_majors,
3138 x_incl_last_date_of_enrollment,
3139 x_incl_professional_licensure,
3140 x_incl_college_affiliation,
3141 x_incl_instruction_dates,
3142 x_incl_usec_dates,
3143 x_incl_program_attempt,
3144 x_incl_attendence_type,
3145 x_incl_last_term_enrolled,
3146 x_incl_ssn,
3147 x_incl_date_of_birth,
3148 x_incl_disciplin_standing,
3149 x_incl_no_future_term,
3150 x_incl_acurat_till_copmp_dt,
3151 x_incl_cant_rel_without_sign,
3152 x_mode,
3153 l_return_status,
3154 l_msg_data,
3155 l_msg_count,
3156 x_doc_fee_per_copy,
3157 x_delivery_fee,
3158 x_recip_email,
3159 x_overridden_doc_delivery_fee,
3160 x_overridden_document_fee,
3161 x_fee_overridden_by,
3162 x_fee_overridden_date,
3163 x_incl_department,
3164 x_incl_field_of_stdy,
3165 x_incl_attend_mode,
3166 x_incl_yop_acad_prd,
3167 x_incl_intrmsn_st_end,
3168 x_incl_hnrs_lvl,
3169 x_incl_awards,
3170 x_incl_award_aim,
3171 x_incl_acad_sessions,
3172 x_incl_st_end_acad_ses,
3173 x_incl_hesa_num,
3174 x_incl_location,
3175 x_incl_program_type,
3176 x_incl_program_name,
3177 x_incl_prog_atmpt_stat,
3178 x_incl_prog_atmpt_end,
3179 x_incl_prog_atmpt_strt,
3180 x_incl_req_cmplete,
3181 x_incl_expected_compl_dt,
3182 x_incl_conferral_dt,
3183 x_incl_thesis_title,
3184 x_incl_program_code,
3185 x_incl_program_ver,
3186 x_incl_stud_no,
3187 x_incl_surname,
3188 x_incl_fore_name,
3189 x_incl_prev_names,
3190 x_incl_initials,
3191 x_doc_purpose_code,
3192 x_plan_id,
3193 x_produced_by,
3194 x_person_id
3195 );
3196 END add_row;
3197 --
3198 --
3199 --
3200 PROCEDURE delete_row (
3201 x_rowid IN VARCHAR2,
3202 x_return_status OUT NOCOPY VARCHAR2,
3203 x_msg_data OUT NOCOPY VARCHAR2,
3204 x_msg_count OUT NOCOPY NUMBER
3205 ) AS
3206 /*
3207 || Created By : manu.srinivasan
3208 || Created On : 28-JAN-2002
3209 || Purpose : Handles the DELETE DML logic for the table.
3210 || Known limitations, enhancements or remarks :
3211 || Change History :
3212 || Who When What
3213 || (reverse chronological order - newest change first)
3214 */
3215 CURSOR c_items_for_this_order (
3216 p_order_number igs_as_doc_details.order_number%TYPE,
3217 p_item_number igs_as_doc_details.item_number%TYPE
3218 ) IS
3219 SELECT a.*,
3220 a.ROWID
3221 FROM igs_as_doc_details a
3222 WHERE order_number = p_order_number
3223 AND item_number <> p_item_number;
3224 l_rowid VARCHAR2 (26);
3225 BEGIN
3226 fnd_msg_pub.initialize;
3227 before_dml (p_action => 'DELETE', x_rowid => x_rowid);
3228 DELETE FROM igs_as_doc_details
3229 WHERE ROWID = x_rowid;
3230 IF (SQL%NOTFOUND) THEN
3231 RAISE NO_DATA_FOUND;
3232 END IF;
3233 igs_as_documents_api.upd_doc_fee_pmnt (
3234 p_person_id => old_references.person_id,
3235 p_plan_id => old_references.plan_id,
3236 p_num_copies => old_references.num_of_copies,
3237 p_program_on_file => old_references.programs_on_file,
3238 p_operation => 'D'
3239 );
3240 --
3241 update_fees_of_remaining_items (
3242 x_person_id => old_references.person_id,
3243 x_document_type => old_references.document_type,
3244 x_document_sub_type => old_references.document_sub_type
3245 );
3246 --Call to update the amount in order header table
3247 igs_as_ss_doc_request.update_order_fee (
3248 p_order_number => old_references.order_number,
3249 p_item_number => old_references.item_number,
3250 p_old_sub_doc_type => old_references.document_sub_type,
3251 p_old_deliv_type => old_references.delivery_method_type,
3252 p_old_num_copies => old_references.num_of_copies,
3253 p_new_sub_doc_type => NULL,
3254 p_new_deliv_type => NULL,
3255 p_new_num_copies => NULL,
3256 p_return_status => x_return_status,
3257 p_msg_data => x_msg_data,
3258 p_msg_count => x_msg_count
3259 );
3260 IF NVL (x_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success THEN
3261 RETURN;
3262 END IF;
3263 -- Call procedure to insert/Update record in IGS_AS_DOC_FEE_PMNT table.
3264 IF old_references.plan_id IS NOT NULL THEN
3265 igs_as_documents_api.upd_doc_fee_pmnt (
3266 p_person_id => old_references.person_id,
3267 p_plan_id => old_references.plan_id,
3268 p_num_copies => old_references.num_of_copies,
3269 p_program_on_file => old_references.programs_on_file,
3270 p_operation => 'D'
3271 );
3272 END IF;
3273 -- Initialize API return status to success.
3274 x_return_status := fnd_api.g_ret_sts_success;
3275 -- Standard call to get message count and if count is 1, get message info
3276 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3277 --
3278 RETURN;
3279 EXCEPTION
3280 WHEN fnd_api.g_exc_error THEN
3281 x_return_status := fnd_api.g_ret_sts_error;
3282 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3283 RETURN;
3284 WHEN fnd_api.g_exc_unexpected_error THEN
3285 x_return_status := fnd_api.g_ret_sts_unexp_error;
3286 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3287 RETURN;
3288 WHEN OTHERS THEN
3289 x_return_status := fnd_api.g_ret_sts_unexp_error;
3290 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
3291 fnd_message.set_token ('NAME', 'Insert_Row : ' || SQLERRM);
3292 fnd_msg_pub.ADD;
3293 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3294 RETURN;
3295 END delete_row;
3296 END igs_as_doc_details_pkg;