[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_DOCUMENTS_API
Source
1 PACKAGE BODY igs_as_documents_api AS
2 /* $Header: IGSAS42B.pls 120.1 2006/01/18 22:57:11 swaghmar noship $ */
3
4 PROCEDURE update_order_item_status (
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER
7 ) IS
8 /*******************************************************************************
9 Created by : rbezawad
10 Date created : 18-Jan-2002
11 Purpose : This procedure updates the order status and the items status of the order.
12 Known limitations/enhancements/remarks:
13 Change History: (who, when, what: NO CREATION RECORDS HERE!)
14 Who When What
15 swaghmar 16-Jan-2006 Bug# 4951054 - Added check for disabling the UI's
16 *******************************************************************************/
17 CURSOR cur_processed_items IS
18 SELECT ROWID row_id,
19 item_status,
20 date_produced,
21 order_number,
22 item_number
23 FROM igs_as_ord_itm_int oii
24 WHERE item_status = 'PROCESSED'
25 ORDER BY order_number,
26 item_number
27 FOR UPDATE NOWAIT;
28 --
29 CURSOR cur_completed_orders IS
30 SELECT ord.ROWID row_id,
31 ord.*
32 FROM igs_as_order_hdr ord
33 WHERE order_status = 'INPROCESS'
34 AND NOT EXISTS ( SELECT item_number
35 FROM igs_as_doc_details itm
36 WHERE ord.order_number = itm.order_number
37 AND itm.item_status <> 'PROCESSED')
38 ORDER BY order_number
39 FOR UPDATE NOWAIT;
40 --
41 CURSOR cur_doc_details (
42 cp_order_number igs_as_doc_details.order_number%TYPE,
43 cp_item_number igs_as_doc_details.item_number%TYPE
44 ) IS
45 SELECT ROWID row_id,
46 dd.*
47 FROM igs_as_doc_details dd
48 WHERE order_number = cp_order_number
49 AND item_number = cp_item_number;
50 --
51 doc_details_rec cur_doc_details%ROWTYPE;
52 --
53 CURSOR cur_doc_date (cp_order_number igs_as_doc_details.order_number%TYPE) IS
54 SELECT MAX (date_produced)
55 FROM igs_as_doc_details
56 WHERE order_number = cp_order_number;
57 --
58 l_date_produced igs_as_doc_details.date_produced%TYPE;
59 l_return_status VARCHAR2 (1);
60 l_msg_count NUMBER;
61 l_msg_data VARCHAR2 (2000);
62 BEGIN
63
64
65 retcode := 0;
66 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
67
68 -- Populate the data into OSS Transcripts tables from Interface Table
69 FOR processed_items_rec IN cur_processed_items LOOP
70 OPEN cur_doc_details (processed_items_rec.order_number, processed_items_rec.item_number);
71 FETCH cur_doc_details INTO doc_details_rec;
72 IF cur_doc_details%FOUND THEN
73 --Update item_status, date_produced Columns of OSS Document Details table with interface table data
74 igs_as_doc_details_pkg.update_row (
75 x_mode => 'R',
76 x_rowid => doc_details_rec.row_id,
77 x_order_number => doc_details_rec.order_number,
78 x_document_type => doc_details_rec.document_type,
79 x_document_sub_type => doc_details_rec.document_sub_type,
80 x_item_number => doc_details_rec.item_number,
81 x_item_status => processed_items_rec.item_status,
82 x_date_produced => processed_items_rec.date_produced,
83 x_incl_curr_course => doc_details_rec.incl_curr_course,
84 x_num_of_copies => doc_details_rec.num_of_copies,
85 x_comments => doc_details_rec.comments,
86 x_recip_pers_name => doc_details_rec.recip_pers_name,
87 x_recip_inst_name => doc_details_rec.recip_inst_name,
88 x_recip_addr_line_1 => doc_details_rec.recip_addr_line_1,
89 x_recip_addr_line_2 => doc_details_rec.recip_addr_line_2,
90 x_recip_addr_line_3 => doc_details_rec.recip_addr_line_3,
91 x_recip_addr_line_4 => doc_details_rec.recip_addr_line_4,
92 x_recip_city => doc_details_rec.recip_city,
93 x_recip_postal_code => doc_details_rec.recip_postal_code,
94 x_recip_state => doc_details_rec.recip_state,
95 x_recip_province => doc_details_rec.recip_province,
96 x_recip_county => doc_details_rec.recip_county,
97 x_recip_country => doc_details_rec.recip_country,
98 x_recip_fax_area_code => doc_details_rec.recip_fax_area_code,
99 x_recip_fax_country_code => doc_details_rec.recip_fax_country_code,
100 x_recip_fax_number => doc_details_rec.recip_fax_number,
101 x_delivery_method_type => doc_details_rec.delivery_method_type,
102 x_programs_on_file => doc_details_rec.programs_on_file,
103 x_missing_acad_record_data_ind => doc_details_rec.missing_acad_record_data_ind,
104 x_missing_academic_record_data => doc_details_rec.missing_academic_record_data,
105 x_send_transcript_immediately => doc_details_rec.send_transcript_immediately,
106 x_hold_release_of_final_grades => doc_details_rec.hold_release_of_final_grades,
107 x_fgrade_cal_type => doc_details_rec.fgrade_cal_type,
108 x_fgrade_seq_num => doc_details_rec.fgrade_seq_num,
109 x_hold_degree_expected => doc_details_rec.hold_degree_expected,
110 x_deghold_cal_type => doc_details_rec.deghold_cal_type,
111 x_deghold_seq_num => doc_details_rec.deghold_seq_num,
112 x_hold_for_grade_chg => doc_details_rec.hold_for_grade_chg,
113 x_special_instr => doc_details_rec.special_instr,
114 x_express_mail_type => doc_details_rec.express_mail_type,
115 x_express_mail_track_num => doc_details_rec.express_mail_track_num,
116 x_ge_certification => doc_details_rec.ge_certification,
117 x_external_comments => doc_details_rec.external_comments,
118 x_internal_comments => doc_details_rec.internal_comments,
119 x_dup_requested => doc_details_rec.dup_requested,
120 x_dup_req_date => doc_details_rec.dup_req_date,
121 x_dup_sent_date => doc_details_rec.dup_sent_date,
122 x_enr_term_cal_type => doc_details_rec.enr_term_cal_type,
123 x_enr_ci_sequence_number => doc_details_rec.enr_ci_sequence_number,
124 x_incl_attempted_hours => doc_details_rec.incl_attempted_hours,
125 x_incl_class_rank => doc_details_rec.incl_class_rank,
126 x_incl_progresssion_status => doc_details_rec.incl_progresssion_status,
127 x_incl_class_standing => doc_details_rec.incl_class_standing,
128 x_incl_cum_hours_earned => doc_details_rec.incl_cum_hours_earned,
129 x_incl_gpa => doc_details_rec.incl_gpa,
130 x_incl_date_of_graduation => doc_details_rec.incl_date_of_graduation,
131 x_incl_degree_dates => doc_details_rec.incl_degree_dates,
132 x_incl_degree_earned => doc_details_rec.incl_degree_earned,
133 x_incl_date_of_entry => doc_details_rec.incl_date_of_entry,
134 x_incl_drop_withdrawal_dates => doc_details_rec.incl_drop_withdrawal_dates,
135 x_incl_hrs_for_curr_term => doc_details_rec.incl_hrs_earned_for_curr_term,
136 x_incl_majors => doc_details_rec.incl_majors,
137 x_incl_last_date_of_enrollment => doc_details_rec.incl_last_date_of_enrollment,
138 x_incl_professional_licensure => doc_details_rec.incl_professional_licensure,
139 x_incl_college_affiliation => doc_details_rec.incl_college_affiliation,
140 x_incl_instruction_dates => doc_details_rec.incl_instruction_dates,
141 x_incl_usec_dates => doc_details_rec.incl_usec_dates,
142 x_incl_program_attempt => doc_details_rec.incl_program_attempt,
143 x_incl_attendence_type => doc_details_rec.incl_attendence_type,
144 x_incl_last_term_enrolled => doc_details_rec.incl_last_term_enrolled,
145 x_incl_ssn => doc_details_rec.incl_ssn,
146 x_incl_date_of_birth => doc_details_rec.incl_date_of_birth,
147 x_incl_disciplin_standing => doc_details_rec.incl_disciplin_standing,
148 x_incl_no_future_term => doc_details_rec.incl_no_future_term,
149 x_incl_acurat_till_copmp_dt => doc_details_rec.incl_acurat_till_copmp_dt,
150 x_incl_cant_rel_without_sign => doc_details_rec.incl_cant_rel_without_sign,
151 x_return_status => l_return_status,
152 x_msg_data => l_msg_data,
153 x_msg_count => l_msg_count,
154 x_doc_fee_per_copy => doc_details_rec.doc_fee_per_copy,
155 x_delivery_fee => doc_details_rec.delivery_fee,
156 x_recip_email => doc_details_rec.recip_email,
157 x_overridden_doc_delivery_fee => doc_details_rec.overridden_doc_delivery_fee,
158 x_overridden_document_fee => doc_details_rec.overridden_document_fee,
159 x_fee_overridden_by => doc_details_rec.fee_overridden_by,
160 x_fee_overridden_date => doc_details_rec.fee_overridden_date,
161 x_incl_department => doc_details_rec.incl_department,
162 x_incl_field_of_stdy => doc_details_rec.incl_field_of_stdy,
163 x_incl_attend_mode => doc_details_rec.incl_attend_mode,
164 x_incl_yop_acad_prd => doc_details_rec.incl_yop_acad_prd,
165 x_incl_intrmsn_st_end => doc_details_rec.incl_intrmsn_st_end,
166 x_incl_hnrs_lvl => doc_details_rec.incl_hnrs_lvl,
167 x_incl_awards => doc_details_rec.incl_awards,
168 x_incl_award_aim => doc_details_rec.incl_award_aim,
169 x_incl_acad_sessions => doc_details_rec.incl_acad_sessions,
170 x_incl_st_end_acad_ses => doc_details_rec.incl_st_end_acad_ses,
171 x_incl_hesa_num => doc_details_rec.incl_hesa_num,
172 x_incl_location => doc_details_rec.incl_location,
173 x_incl_program_type => doc_details_rec.incl_program_type,
174 x_incl_program_name => doc_details_rec.incl_program_name,
175 x_incl_prog_atmpt_stat => doc_details_rec.incl_prog_atmpt_stat,
176 x_incl_prog_atmpt_end => doc_details_rec.incl_prog_atmpt_end,
177 x_incl_prog_atmpt_strt => doc_details_rec.incl_prog_atmpt_strt,
178 x_incl_req_cmplete => doc_details_rec.incl_req_cmplete,
179 x_incl_expected_compl_dt => doc_details_rec.incl_expected_compl_dt,
180 x_incl_conferral_dt => doc_details_rec.incl_conferral_dt,
181 x_incl_thesis_title => doc_details_rec.incl_thesis_title,
182 x_incl_program_code => doc_details_rec.incl_program_code,
183 x_incl_program_ver => doc_details_rec.incl_program_ver,
184 x_incl_stud_no => doc_details_rec.incl_stud_no,
185 x_incl_surname => doc_details_rec.incl_surname,
186 x_incl_fore_name => doc_details_rec.incl_fore_name,
187 x_incl_prev_names => doc_details_rec.incl_prev_names,
188 x_incl_initials => doc_details_rec.incl_initials,
189 x_doc_purpose_code => doc_details_rec.doc_purpose_code,
190 x_plan_id => doc_details_rec.plan_id,
191 x_produced_by => doc_details_rec.produced_by,
192 x_person_id => doc_details_rec.person_id
193 );
194 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
195 fnd_message.set_name ('IGS', 'IGS_AS_ITEM_STATUS');
196 fnd_message.set_token ('ORDER_NUMBER', doc_details_rec.order_number);
197 fnd_message.set_token ('ITEM_NUMBER', doc_details_rec.item_number);
198 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
199 --Delete the Item Number record from Interface table after updating the OSS Document Details Table
200 DELETE igs_as_ord_itm_int
201 WHERE ROWID = processed_items_rec.row_id;
202 ELSE
203 -- IF the RETURN status IS error than LOG a message AND the message
204 -- data that was retunred by the above API
205 fnd_message.set_name ('IGS', 'IGS_AS_ITEM_STATUS_ERR');
206 fnd_message.set_token ('ORDER_NUMBER', doc_details_rec.order_number);
207 fnd_message.set_token ('ITEM_NUMBER', doc_details_rec.item_number);
208 fnd_file.put_line (fnd_file.LOG, l_msg_data);
209 IF l_msg_count = 1 THEN
210 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
211 ELSE
212 FOR l_count IN 1 .. l_msg_count LOOP
213 fnd_message.set_encoded (fnd_msg_pub.get (p_msg_index => l_count, p_encoded => 'T'));
214 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
215 END LOOP;
216 END IF;
217 END IF;
218 END IF;
219 CLOSE cur_doc_details;
220 END LOOP;
221 fnd_file.put_line (fnd_file.LOG, '+---------------------------------------------------------------------------+');
222 --
223 -- Update the order/request status to 'COMPLETED' if all the
224 -- items of the order have status 'PROCESSED'
225 --
226 FOR completed_orders_rec IN cur_completed_orders LOOP
227 OPEN cur_doc_date (completed_orders_rec.order_number);
228 FETCH cur_doc_date INTO l_date_produced;
229 CLOSE cur_doc_date;
230 l_return_status := NULL;
231 l_msg_data := NULL;
232 l_msg_count := NULL;
233 -- Update the Order Status, Date of Completion for the Request in the OSS Order Table.
234 igs_as_order_hdr_pkg.update_row (
235 x_mode => 'R',
236 x_rowid => completed_orders_rec.row_id,
237 x_order_number => completed_orders_rec.order_number,
238 x_order_status => 'COMPLETED',
239 x_date_completed => l_date_produced,
240 x_person_id => completed_orders_rec.person_id,
241 x_addr_line_1 => completed_orders_rec.addr_line_1,
242 x_addr_line_2 => completed_orders_rec.addr_line_2,
243 x_addr_line_3 => completed_orders_rec.addr_line_3,
244 x_addr_line_4 => completed_orders_rec.addr_line_4,
245 x_city => completed_orders_rec.city,
246 x_state => completed_orders_rec.state,
247 x_province => completed_orders_rec.province,
248 x_county => completed_orders_rec.county,
249 x_country => completed_orders_rec.country,
250 x_postal_code => completed_orders_rec.postal_code,
251 x_email_address => completed_orders_rec.email_address,
252 x_phone_country_code => completed_orders_rec.phone_country_code,
253 x_phone_area_code => completed_orders_rec.phone_area_code,
254 x_phone_number => completed_orders_rec.phone_number,
258 x_fax_number => completed_orders_rec.fax_number,
255 x_phone_extension => completed_orders_rec.phone_extension,
256 x_fax_country_code => completed_orders_rec.fax_country_code,
257 x_fax_area_code => completed_orders_rec.fax_area_code,
259 x_delivery_fee => completed_orders_rec.delivery_fee,
260 x_order_fee => completed_orders_rec.order_fee,
261 x_request_type => completed_orders_rec.request_type,
262 x_submit_method => completed_orders_rec.submit_method,
263 x_invoice_id => completed_orders_rec.invoice_id,
264 x_return_status => l_return_status,
265 x_msg_data => l_msg_data,
266 x_msg_count => l_msg_count,
267 x_order_description => completed_orders_rec.order_description,
268 x_order_placed_by => completed_orders_rec.order_placed_by
269 );
270 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
271 fnd_message.set_name ('IGS', 'IGS_AS_ORDER_STATUS');
272 fnd_message.set_token ('ORDER_NUMBER', completed_orders_rec.order_number);
273 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
274 ELSE
275 -- IF the RETURN status IS error than LOG a message AND the message
276 -- data that was retunred by the above API
277 fnd_message.set_name ('IGS', 'IGS_AS_ORDER_STATUS_ERR');
278 fnd_message.set_token ('ORDER_NUMBER', completed_orders_rec.order_number);
279 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
280 IF l_msg_count = 1 THEN
281 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
282 ELSE
283 FOR l_count IN 1 .. l_msg_count LOOP
284 fnd_message.set_encoded (fnd_msg_pub.get (p_msg_index => l_count, p_encoded => 'T'));
285 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
286 END LOOP;
287 END IF;
288 END IF;
289 END LOOP;
290 EXCEPTION
291 WHEN OTHERS THEN
292 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
293 retcode := 2;
294 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
295 fnd_message.set_token ('NAME', 'Update_Order_Item_Status(): ' || SQLERRM);
296 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
297 igs_ge_msg_stack.ADD;
298 igs_ge_msg_stack.conc_exception_hndl;
299 END update_order_item_status;
300
301 PROCEDURE update_document_details (
302 p_order_number IN NUMBER,
303 p_item_number IN NUMBER,
304 p_init_msg_list IN VARCHAR2,
305 p_return_status OUT NOCOPY VARCHAR2,
306 p_msg_count OUT NOCOPY NUMBER,
307 p_msg_data OUT NOCOPY VARCHAR2,
308 p_person_id IN VARCHAR2,
309 p_fee_amt IN NUMBER,
310 p_recorded_by IN NUMBER,
311 p_plan_id IN NUMBER,
312 p_invoice_id IN NUMBER,
313 p_plan_cal_type IN VARCHAR2,
314 p_plan_ci_sequence_number IN NUMBER
315 ) IS
316 /*******************************************************************************
317 Created by : rbezawad
318 Date created : 21-Jan-2002
319 Purpose : This procedure updates Transcipt's Order, Item Statuses to INPROCESS.
320 And also inserts the Transcripts Request record into Interface table.
321 Known limitations/enhancements/remarks:
322 Change History: (who, when, what: NO CREATION RECORDS HERE!)
323 Who When What
324 kdande 29-Nov-2002 Changed the logic to create an interface item.
325 *******************************************************************************/
326 CURSOR cur_order_hdr IS
327 SELECT ord.ROWID row_id,
328 ord.*
329 FROM igs_as_order_hdr ord
330 WHERE order_number = p_order_number;
331 --
332 order_hdr_rec cur_order_hdr%ROWTYPE;
333 --
334 CURSOR cur_doc_details IS
335 SELECT ROWID row_id,
336 dd.*
337 FROM igs_as_doc_details dd
338 WHERE dd.order_number = p_order_number
339 AND dd.item_number = NVL (p_item_number, dd.item_number)
340 ORDER BY dd.item_number;
341 --
342 CURSOR cur_ord_int_details (cp_order_number NUMBER, cp_item_number NUMBER) IS
343 SELECT 'X'
344 FROM igs_as_ord_itm_int
345 WHERE order_number = cp_order_number
346 AND item_number = cp_item_number;
347 --
348 CURSOR cur_ord_int_dupl_doc (cp_order_number NUMBER, cp_item_number NUMBER) IS
349 SELECT 'X'
350 FROM igs_as_ord_itm_int intr,
351 igs_as_dup_docs dup
352 WHERE intr.order_number = cp_order_number
353 AND intr.item_number = cp_item_number
354 AND intr.item_status = 'PROCESSED'
355 AND intr.order_number = dup.order_number
356 AND intr.item_number = dup.item_number;
357 --
358 ord_int_details_rec cur_ord_int_details%ROWTYPE;
359 rec_ord_int_dupl_doc cur_ord_int_dupl_doc%ROWTYPE;
360 l_return_status VARCHAR2 (1);
361 l_msg_count NUMBER;
362 l_msg_data VARCHAR2 (2000);
363 l_person_id igs_as_order_hdr.person_id%TYPE;
364 lv_rowid VARCHAR2 (25);
368 IF fnd_api.to_boolean (p_init_msg_list) THEN
365 l_create_interface_item BOOLEAN;
366 BEGIN
367 --Initialize message list if p_init_msg_list is set to TRUE.
369 fnd_msg_pub.initialize;
370 END IF;
371 OPEN cur_order_hdr;
372 FETCH cur_order_hdr INTO order_hdr_rec;
373 IF p_order_number IS NOT NULL
374 AND p_plan_id IS NULL THEN
375 IF (cur_order_hdr%FOUND) THEN
376 l_person_id := order_hdr_rec.person_id; -- Need the var to pass to the recal procdure
377 --Update the Order status to 'In Process' and other order details.
378 igs_as_order_hdr_pkg.update_row (
379 x_mode => 'R',
380 x_rowid => order_hdr_rec.row_id,
381 x_order_number => order_hdr_rec.order_number,
382 x_order_status => 'INPROCESS',
383 x_date_completed => order_hdr_rec.date_completed,
384 x_person_id => order_hdr_rec.person_id,
385 x_addr_line_1 => order_hdr_rec.addr_line_1,
386 x_addr_line_2 => order_hdr_rec.addr_line_2,
387 x_addr_line_3 => order_hdr_rec.addr_line_3,
388 x_addr_line_4 => order_hdr_rec.addr_line_4,
389 x_city => order_hdr_rec.city,
390 x_state => order_hdr_rec.state,
391 x_province => order_hdr_rec.province,
392 x_county => order_hdr_rec.county,
393 x_country => order_hdr_rec.country,
394 x_postal_code => order_hdr_rec.postal_code,
395 x_email_address => order_hdr_rec.email_address,
396 x_phone_country_code => order_hdr_rec.phone_country_code,
397 x_phone_area_code => order_hdr_rec.phone_area_code,
398 x_phone_number => order_hdr_rec.phone_number,
399 x_phone_extension => order_hdr_rec.phone_extension,
400 x_fax_country_code => order_hdr_rec.fax_country_code,
401 x_fax_area_code => order_hdr_rec.fax_area_code,
402 x_fax_number => order_hdr_rec.fax_number,
403 x_delivery_fee => order_hdr_rec.delivery_fee,
404 x_order_fee => order_hdr_rec.order_fee,
405 x_request_type => order_hdr_rec.request_type,
406 x_submit_method => order_hdr_rec.submit_method,
407 x_invoice_id => order_hdr_rec.invoice_id,
408 x_return_status => l_return_status,
409 x_msg_data => l_msg_data,
410 x_msg_count => l_msg_count,
411 x_order_description => order_hdr_rec.order_description,
412 x_order_placed_by => order_hdr_rec.order_placed_by
413 );
414 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
415 p_return_status := l_return_status;
416 p_msg_count := l_msg_count;
417 p_msg_data := l_msg_data;
418 RETURN;
419 END IF;
420 FOR doc_details_rec IN cur_doc_details LOOP
421 l_return_status := NULL;
422 l_msg_data := NULL;
423 l_msg_count := NULL;
424 --Update the Order's Item status to 'In Process'
425 igs_as_doc_details_pkg.update_row (
426 x_mode => 'R',
427 x_rowid => doc_details_rec.row_id,
428 x_order_number => doc_details_rec.order_number,
429 x_document_type => doc_details_rec.document_type,
430 x_document_sub_type => doc_details_rec.document_sub_type,
431 x_item_number => doc_details_rec.item_number,
432 x_item_status => 'INPROCESS',
433 x_date_produced => doc_details_rec.date_produced,
434 x_incl_curr_course => doc_details_rec.incl_curr_course,
435 x_num_of_copies => doc_details_rec.num_of_copies,
436 x_comments => doc_details_rec.comments,
437 x_recip_pers_name => doc_details_rec.recip_pers_name,
438 x_recip_inst_name => doc_details_rec.recip_inst_name,
439 x_recip_addr_line_1 => doc_details_rec.recip_addr_line_1,
440 x_recip_addr_line_2 => doc_details_rec.recip_addr_line_2,
441 x_recip_addr_line_3 => doc_details_rec.recip_addr_line_3,
442 x_recip_addr_line_4 => doc_details_rec.recip_addr_line_4,
443 x_recip_city => doc_details_rec.recip_city,
444 x_recip_postal_code => doc_details_rec.recip_postal_code,
445 x_recip_state => doc_details_rec.recip_state,
446 x_recip_province => doc_details_rec.recip_province,
447 x_recip_county => doc_details_rec.recip_county,
448 x_recip_country => doc_details_rec.recip_country,
449 x_recip_fax_area_code => doc_details_rec.recip_fax_area_code,
450 x_recip_fax_country_code => doc_details_rec.recip_fax_country_code,
451 x_recip_fax_number => doc_details_rec.recip_fax_number,
452 x_delivery_method_type => doc_details_rec.delivery_method_type,
453 x_programs_on_file => doc_details_rec.programs_on_file,
454 x_missing_acad_record_data_ind => doc_details_rec.missing_acad_record_data_ind,
458 x_fgrade_cal_type => doc_details_rec.fgrade_cal_type,
455 x_missing_academic_record_data => doc_details_rec.missing_academic_record_data,
456 x_send_transcript_immediately => doc_details_rec.send_transcript_immediately,
457 x_hold_release_of_final_grades => doc_details_rec.hold_release_of_final_grades,
459 x_fgrade_seq_num => doc_details_rec.fgrade_seq_num,
460 x_hold_degree_expected => doc_details_rec.hold_degree_expected,
461 x_deghold_cal_type => doc_details_rec.deghold_cal_type,
462 x_deghold_seq_num => doc_details_rec.deghold_seq_num,
463 x_hold_for_grade_chg => doc_details_rec.hold_for_grade_chg,
464 x_special_instr => doc_details_rec.special_instr,
465 x_express_mail_type => doc_details_rec.express_mail_type,
466 x_express_mail_track_num => doc_details_rec.express_mail_track_num,
467 x_ge_certification => doc_details_rec.ge_certification,
468 x_external_comments => doc_details_rec.external_comments,
469 x_internal_comments => doc_details_rec.internal_comments,
470 x_dup_requested => doc_details_rec.dup_requested,
471 x_dup_req_date => doc_details_rec.dup_req_date,
472 x_dup_sent_date => doc_details_rec.dup_sent_date,
473 x_enr_term_cal_type => doc_details_rec.enr_term_cal_type,
474 x_enr_ci_sequence_number => doc_details_rec.enr_ci_sequence_number,
475 x_incl_attempted_hours => doc_details_rec.incl_attempted_hours,
476 x_incl_class_rank => doc_details_rec.incl_class_rank,
477 x_incl_progresssion_status => doc_details_rec.incl_progresssion_status,
478 x_incl_class_standing => doc_details_rec.incl_class_standing,
479 x_incl_cum_hours_earned => doc_details_rec.incl_cum_hours_earned,
480 x_incl_gpa => doc_details_rec.incl_gpa,
481 x_incl_date_of_graduation => doc_details_rec.incl_date_of_graduation,
482 x_incl_degree_dates => doc_details_rec.incl_degree_dates,
483 x_incl_degree_earned => doc_details_rec.incl_degree_earned,
484 x_incl_date_of_entry => doc_details_rec.incl_date_of_entry,
485 x_incl_drop_withdrawal_dates => doc_details_rec.incl_drop_withdrawal_dates,
486 x_incl_hrs_for_curr_term => doc_details_rec.incl_hrs_earned_for_curr_term,
487 x_incl_majors => doc_details_rec.incl_majors,
488 x_incl_last_date_of_enrollment => doc_details_rec.incl_last_date_of_enrollment,
489 x_incl_professional_licensure => doc_details_rec.incl_professional_licensure,
490 x_incl_college_affiliation => doc_details_rec.incl_college_affiliation,
491 x_incl_instruction_dates => doc_details_rec.incl_instruction_dates,
492 x_incl_usec_dates => doc_details_rec.incl_usec_dates,
493 x_incl_program_attempt => doc_details_rec.incl_program_attempt,
494 x_incl_attendence_type => doc_details_rec.incl_attendence_type,
495 x_incl_last_term_enrolled => doc_details_rec.incl_last_term_enrolled,
496 x_incl_ssn => doc_details_rec.incl_ssn,
497 x_incl_date_of_birth => doc_details_rec.incl_date_of_birth,
498 x_incl_disciplin_standing => doc_details_rec.incl_disciplin_standing,
499 x_incl_no_future_term => doc_details_rec.incl_no_future_term,
500 x_incl_acurat_till_copmp_dt => doc_details_rec.incl_acurat_till_copmp_dt,
501 x_incl_cant_rel_without_sign => doc_details_rec.incl_cant_rel_without_sign,
502 x_return_status => l_return_status,
503 x_msg_data => l_msg_data,
504 x_msg_count => l_msg_count,
505 x_doc_fee_per_copy => doc_details_rec.doc_fee_per_copy,
506 x_delivery_fee => doc_details_rec.delivery_fee,
507 x_recip_email => doc_details_rec.recip_email,
508 x_overridden_doc_delivery_fee => doc_details_rec.overridden_doc_delivery_fee,
509 x_overridden_document_fee => doc_details_rec.overridden_document_fee,
510 x_fee_overridden_by => doc_details_rec.fee_overridden_by,
511 x_fee_overridden_date => doc_details_rec.fee_overridden_date,
515 x_incl_yop_acad_prd => doc_details_rec.incl_yop_acad_prd,
512 x_incl_department => doc_details_rec.incl_department,
513 x_incl_field_of_stdy => doc_details_rec.incl_field_of_stdy,
514 x_incl_attend_mode => doc_details_rec.incl_attend_mode,
516 x_incl_intrmsn_st_end => doc_details_rec.incl_intrmsn_st_end,
517 x_incl_hnrs_lvl => doc_details_rec.incl_hnrs_lvl,
518 x_incl_awards => doc_details_rec.incl_awards,
519 x_incl_award_aim => doc_details_rec.incl_award_aim,
520 x_incl_acad_sessions => doc_details_rec.incl_acad_sessions,
521 x_incl_st_end_acad_ses => doc_details_rec.incl_st_end_acad_ses,
522 x_incl_hesa_num => doc_details_rec.incl_hesa_num,
523 x_incl_location => doc_details_rec.incl_location,
524 x_incl_program_type => doc_details_rec.incl_program_type,
525 x_incl_program_name => doc_details_rec.incl_program_name,
526 x_incl_prog_atmpt_stat => doc_details_rec.incl_prog_atmpt_stat,
527 x_incl_prog_atmpt_end => doc_details_rec.incl_prog_atmpt_end,
528 x_incl_prog_atmpt_strt => doc_details_rec.incl_prog_atmpt_strt,
529 x_incl_req_cmplete => doc_details_rec.incl_req_cmplete,
530 x_incl_expected_compl_dt => doc_details_rec.incl_expected_compl_dt,
531 x_incl_conferral_dt => doc_details_rec.incl_conferral_dt,
532 x_incl_thesis_title => doc_details_rec.incl_thesis_title,
533 x_incl_program_code => doc_details_rec.incl_program_code,
534 x_incl_program_ver => doc_details_rec.incl_program_ver,
535 x_incl_stud_no => doc_details_rec.incl_stud_no,
536 x_incl_surname => doc_details_rec.incl_surname,
537 x_incl_fore_name => doc_details_rec.incl_fore_name,
538 x_incl_prev_names => doc_details_rec.incl_prev_names,
539 x_incl_initials => doc_details_rec.incl_initials,
540 x_doc_purpose_code => doc_details_rec.doc_purpose_code,
541 x_plan_id => doc_details_rec.plan_id,
542 x_produced_by => doc_details_rec.produced_by,
543 x_person_id => doc_details_rec.person_id
544 );
545 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
546 p_return_status := l_return_status;
547 p_msg_count := l_msg_count;
548 p_msg_data := l_msg_data;
549 RETURN;
550 END IF;
551 OPEN cur_ord_int_details (doc_details_rec.order_number, doc_details_rec.item_number);
552 FETCH cur_ord_int_details INTO ord_int_details_rec;
553 IF cur_ord_int_details%NOTFOUND THEN
554 l_create_interface_item := TRUE;
555 ELSE
556 l_create_interface_item := FALSE;
557 OPEN cur_ord_int_dupl_doc (doc_details_rec.order_number, doc_details_rec.item_number);
558 FETCH cur_ord_int_dupl_doc INTO rec_ord_int_dupl_doc;
559 IF (cur_ord_int_dupl_doc%FOUND) THEN
560 l_create_interface_item := TRUE;
561 END IF;
562 CLOSE cur_ord_int_dupl_doc;
563 END IF;
564 IF (l_create_interface_item) THEN
565 -- Inserting the Items of Order information into Interface table.
566 INSERT INTO igs_as_ord_itm_int
567 (order_number, person_id, document_type,
568 document_sub_type, item_number, item_status,
569 date_produced, num_of_copies,
570 programs_on_file, comments, recip_pers_name,
571 recip_inst_name, recip_addr_line_1,
572 recip_addr_line_2, recip_addr_line_3,
573 recip_addr_line_4, recip_city,
574 recip_postal_code, recip_state,
575 recip_province, recip_county, recip_country,
576 recip_fax_area_code, recip_fax_country_code,
577 recip_fax_number, delivery_method_type,
578 dup_requested, dup_req_date, dup_sent_date,
579 fgrade_cal_type, fgrade_seq_num,
580 deghold_cal_type, deghold_seq_num,
581 hold_for_grade_chg, hold_degree_expected,
582 hold_release_of_final_grades, incl_curr_course,
583 missing_acad_record_data_ind, missing_academic_record_data,
584 send_transcript_immediately, special_instr,
585 express_mail_type, express_mail_track_num,
586 ge_certification, external_comments,
587 internal_comments, enr_term_cal_type,
588 enr_ci_sequence_number, incl_attempted_hours,
589 incl_class_rank, incl_progresssion_status,
590 incl_class_standing, incl_cum_hours_earned,
591 incl_gpa, incl_date_of_graduation,
592 incl_degree_dates, incl_degree_earned,
593 incl_date_of_entry, incl_drop_withdrawal_dates,
594 incl_hrs_earned_for_curr_term, incl_majors,
595 incl_last_date_of_enrollment, incl_professional_licensure,
599 incl_ssn, incl_date_of_birth,
596 incl_college_affiliation, incl_instruction_dates,
597 incl_usec_dates, incl_program_attempt,
598 incl_attendence_type, incl_last_term_enrolled,
600 incl_disciplin_standing, incl_no_future_term,
601 incl_acurat_till_copmp_dt, incl_cant_rel_without_sign,
602 creation_date, created_by, last_update_date,
603 last_updated_by, last_update_login,
604 request_id, program_id,
605 program_application_id, program_update_date,
606 recip_email)
607 VALUES (order_hdr_rec.order_number, order_hdr_rec.person_id, doc_details_rec.document_type,
608 doc_details_rec.document_sub_type, doc_details_rec.item_number, 'INPROCESS',
609 doc_details_rec.date_produced, doc_details_rec.num_of_copies,
610 doc_details_rec.programs_on_file, doc_details_rec.comments, doc_details_rec.recip_pers_name,
611 doc_details_rec.recip_inst_name, doc_details_rec.recip_addr_line_1,
612 doc_details_rec.recip_addr_line_2, doc_details_rec.recip_addr_line_3,
613 doc_details_rec.recip_addr_line_4, doc_details_rec.recip_city,
614 doc_details_rec.recip_postal_code, doc_details_rec.recip_state,
615 doc_details_rec.recip_province, doc_details_rec.recip_county, doc_details_rec.recip_country,
616 doc_details_rec.recip_fax_area_code, doc_details_rec.recip_fax_country_code,
617 doc_details_rec.recip_fax_number, doc_details_rec.delivery_method_type,
618 doc_details_rec.dup_requested, doc_details_rec.dup_req_date, doc_details_rec.dup_sent_date,
619 doc_details_rec.fgrade_cal_type, doc_details_rec.fgrade_seq_num,
620 doc_details_rec.deghold_cal_type, doc_details_rec.deghold_seq_num,
621 doc_details_rec.hold_for_grade_chg, doc_details_rec.hold_degree_expected,
622 doc_details_rec.hold_release_of_final_grades, doc_details_rec.incl_curr_course,
623 doc_details_rec.missing_acad_record_data_ind, doc_details_rec.missing_academic_record_data,
624 doc_details_rec.send_transcript_immediately, doc_details_rec.special_instr,
625 doc_details_rec.express_mail_type, doc_details_rec.express_mail_track_num,
626 doc_details_rec.ge_certification, doc_details_rec.external_comments,
627 doc_details_rec.internal_comments, doc_details_rec.enr_term_cal_type,
628 doc_details_rec.enr_ci_sequence_number, doc_details_rec.incl_attempted_hours,
629 doc_details_rec.incl_class_rank, doc_details_rec.incl_progresssion_status,
630 doc_details_rec.incl_class_standing, doc_details_rec.incl_cum_hours_earned,
631 doc_details_rec.incl_gpa, doc_details_rec.incl_date_of_graduation,
632 doc_details_rec.incl_degree_dates, doc_details_rec.incl_degree_earned,
633 doc_details_rec.incl_date_of_entry, doc_details_rec.incl_drop_withdrawal_dates,
634 doc_details_rec.incl_hrs_earned_for_curr_term, doc_details_rec.incl_majors,
635 doc_details_rec.incl_last_date_of_enrollment, doc_details_rec.incl_professional_licensure,
636 doc_details_rec.incl_college_affiliation, doc_details_rec.incl_instruction_dates,
637 doc_details_rec.incl_usec_dates, doc_details_rec.incl_program_attempt,
638 doc_details_rec.incl_attendence_type, doc_details_rec.incl_last_term_enrolled,
639 doc_details_rec.incl_ssn, doc_details_rec.incl_date_of_birth,
640 doc_details_rec.incl_disciplin_standing, doc_details_rec.incl_no_future_term,
641 doc_details_rec.incl_acurat_till_copmp_dt, doc_details_rec.incl_cant_rel_without_sign,
642 doc_details_rec.creation_date, doc_details_rec.created_by, doc_details_rec.last_update_date,
643 doc_details_rec.last_updated_by, doc_details_rec.last_update_login,
644 doc_details_rec.request_id, doc_details_rec.program_id,
645 doc_details_rec.program_application_id, doc_details_rec.program_update_date,
646 doc_details_rec.recip_email);
647 END IF;
648 CLOSE cur_ord_int_details;
649 END LOOP;
650 CLOSE cur_order_hdr;
651 -- Added by msrinivi Bug 2407082
652 -- Recal all amounts since lft has been paid
653 igs_as_ss_doc_request.recalc_after_lft_paid (
654 p_person_id => l_person_id,
655 p_order_number => p_order_number,
656 p_return_status => p_return_status,
657 p_msg_data => p_msg_data,
658 p_msg_count => p_msg_count
659 );
660 IF (NVL (l_return_status, fnd_api.g_ret_sts_success) <> fnd_api.g_ret_sts_success) THEN
661 p_return_status := l_return_status;
662 p_msg_count := l_msg_count;
663 p_msg_data := l_msg_data;
664 RETURN;
665 END IF;
666 ELSE
667 --If Order Details are not found for the passed Order Number
668 CLOSE cur_order_hdr;
669 fnd_message.set_name ('IGS', 'IGS_AS_ORDER_NOT_FOUND');
670 fnd_message.set_token ('ORDER_NUMBER', TO_CHAR (p_order_number));
671 fnd_msg_pub.ADD;
672 RAISE fnd_api.g_exc_error;
673 END IF;
674 ELSIF p_order_number IS NULL
675 AND p_plan_id IS NOT NULL THEN
676 -- Insert a record into IGS_AS_DOC_FEE_PMNT table to note that the student has subscribed to the plan.
677 igs_as_doc_fee_pmnt_pkg.insert_row (
678 x_rowid => lv_rowid,
679 x_person_id => p_person_id,
680 x_fee_paid_date => SYSDATE,
681 x_fee_amount => p_fee_amt,
682 x_fee_recorded_date => SYSDATE,
683 x_fee_recorded_by => p_recorded_by,
684 x_mode => 'R',
685 x_plan_id => p_plan_id,
686 x_invoice_id => p_invoice_id,
687 x_plan_discon_from => NULL,
688 x_plan_discon_by => NULL,
689 x_num_of_copies => NULL,
690 x_prev_paid_plan => 'N',
691 x_cal_type => p_plan_cal_type,
692 x_ci_sequence_number => p_plan_ci_sequence_number,
693 x_program_on_file => NULL,
694 x_return_status => l_return_status,
695 x_msg_data => l_msg_data,
696 x_msg_count => l_msg_count
697 );
698 END IF;
699 --Initialize API return status to success.
700 p_return_status := fnd_api.g_ret_sts_success;
701 --Standard call to get message count and if count is 1, get message info.
702 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => p_msg_count, p_data => p_msg_data);
703 EXCEPTION
704 WHEN fnd_api.g_exc_error THEN
705 p_return_status := fnd_api.g_ret_sts_error;
706 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => p_msg_count, p_data => p_msg_data);
707 WHEN fnd_api.g_exc_unexpected_error THEN
708 p_return_status := fnd_api.g_ret_sts_unexp_error;
709 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => p_msg_count, p_data => p_msg_data);
710 WHEN OTHERS THEN
711 p_return_status := fnd_api.g_ret_sts_unexp_error;
712 fnd_message.set_name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
713 fnd_message.set_token ('NAME', 'update_document_details: ' || SQLERRM);
714 fnd_msg_pub.ADD;
715 fnd_msg_pub.count_and_get (p_encoded => fnd_api.g_false, p_count => p_msg_count, p_data => p_msg_data);
716 END update_document_details;
717
718 PROCEDURE upd_doc_fee_pmnt (
719 p_person_id NUMBER,
720 p_plan_id NUMBER,
721 p_num_copies NUMBER,
722 p_program_on_file VARCHAR2,
723 p_operation VARCHAR2
724 ) IS
725 CURSOR cur_doc_fee_upd IS
726 SELECT f.ROWID row_id,
727 f.*
728 FROM igs_as_doc_fee_pmnt f
729 WHERE person_id = p_person_id
730 AND plan_id = p_plan_id
731 AND NVL (program_on_file, 'A') = NVL (p_program_on_file, 'A');
732 /*
733 Bug 2719682
734 cursor to find rec irrespective if program on file for non-free plan
735 */
736 CURSOR cur_doc_fee_upd_non_free IS
737 SELECT f.ROWID row_id,
738 f.*
739 FROM igs_as_doc_fee_pmnt f
740 WHERE person_id = p_person_id
741 AND plan_id = p_plan_id;
742 --
743 CURSOR cur_free_plan IS
744 SELECT 'Y'
745 FROM igs_as_servic_plan asp,
746 igs_lookups_view lkv
747 WHERE asp.plan_type = lkv.meaning
748 AND lkv.lookup_type = 'TRANSCRIPT_SERVICE_PLAN_TYPE'
749 AND lkv.lookup_code = 'FREE_TRANSCRIPT'
750 AND asp.plan_id = p_plan_id;
751 --
752 l_free_plan VARCHAR2 (1) := 'N';
753 non_free_fee_rec cur_doc_fee_upd_non_free%ROWTYPE;
754 fee_rec cur_doc_fee_upd%ROWTYPE;
755 lvrow_id VARCHAR2 (30);
756 l_return_status VARCHAR2 (10);
757 l_msg_data VARCHAR2 (100);
758 l_msg_count NUMBER;
759 lnumcopies NUMBER := 0;
760 BEGIN
761 -- check whether plan is free or non-free
762 OPEN cur_free_plan;
763 FETCH cur_free_plan INTO l_free_plan;
764 CLOSE cur_free_plan;
765 IF p_operation = 'I'
766 OR p_operation = 'U' THEN
767 --See if the record already exists for the student for the given plan ID
768 IF l_free_plan = 'Y' THEN
769 OPEN cur_doc_fee_upd;
770 FETCH cur_doc_fee_upd INTO fee_rec;
771 CLOSE cur_doc_fee_upd;
772 IF fee_rec.person_id IS NULL THEN
773 lvrow_id := NULL;
774 -- No record exists hence insert a record.
775 igs_as_doc_fee_pmnt_pkg.insert_row (
776 x_rowid => lvrow_id,
777 x_person_id => p_person_id,
778 x_fee_paid_date => SYSDATE,
779 x_fee_amount => 0,
780 x_fee_recorded_date => SYSDATE,
781 x_fee_recorded_by => p_person_id,
782 x_mode => 'R',
783 x_plan_id => p_plan_id,
787 x_num_of_copies => p_num_copies,
784 x_invoice_id => NULL,
785 x_plan_discon_from => NULL,
786 x_plan_discon_by => NULL,
788 x_prev_paid_plan => 'N',
789 x_cal_type => NULL,
790 x_ci_sequence_number => NULL,
791 x_program_on_file => p_program_on_file,
792 x_return_status => l_return_status,
793 x_msg_data => l_msg_data,
794 x_msg_count => l_msg_count
795 );
796 ELSE
797 -- Record is already existing hence update the row..
798 igs_as_doc_fee_pmnt_pkg.update_row (
799 x_rowid => fee_rec.row_id,
800 x_person_id => fee_rec.person_id,
801 x_fee_paid_date => fee_rec.fee_paid_date,
802 x_fee_amount => fee_rec.fee_amount,
803 x_fee_recorded_date => fee_rec.fee_recorded_date,
804 x_fee_recorded_by => fee_rec.fee_recorded_by,
805 x_mode => 'R',
806 x_plan_id => fee_rec.plan_id,
807 x_invoice_id => fee_rec.invoice_id,
808 x_plan_discon_from => fee_rec.plan_discon_from,
809 x_plan_discon_by => fee_rec.plan_discon_by,
810 x_num_of_copies => p_num_copies, --NVL (fee_rec.num_of_copies, 0) + p_num_copies,
811 x_prev_paid_plan => fee_rec.prev_paid_plan,
812 x_cal_type => fee_rec.cal_type,
813 x_ci_sequence_number => fee_rec.ci_sequence_number,
814 x_program_on_file => fee_rec.program_on_file,
815 x_return_status => l_return_status,
816 x_msg_data => l_msg_data,
817 x_msg_count => l_msg_count
818 );
819 END IF; -- IF FOR RECORD EXISTS FOR INSERT
820 ELSE -- i.e. l_free_plan = 'N'
821 OPEN cur_doc_fee_upd_non_free;
822 FETCH cur_doc_fee_upd_non_free INTO non_free_fee_rec;
823 CLOSE cur_doc_fee_upd_non_free;
824 IF non_free_fee_rec.person_id IS NULL THEN
825 -- No record exists hence insert a record.
826 lvrow_id := NULL;
827 igs_as_doc_fee_pmnt_pkg.insert_row (
828 x_rowid => lvrow_id,
829 x_person_id => p_person_id,
830 x_fee_paid_date => SYSDATE,
831 x_fee_amount => 0,
832 x_fee_recorded_date => SYSDATE,
833 x_fee_recorded_by => p_person_id,
834 x_mode => 'R',
835 x_plan_id => p_plan_id,
836 x_invoice_id => NULL,
837 x_plan_discon_from => NULL,
838 x_plan_discon_by => NULL,
839 x_num_of_copies => p_num_copies,
840 x_prev_paid_plan => 'N',
841 x_cal_type => NULL,
842 x_ci_sequence_number => NULL,
843 x_program_on_file => p_program_on_file,
844 x_return_status => l_return_status,
845 x_msg_data => l_msg_data,
846 x_msg_count => l_msg_count
847 );
848 ELSE
849 -- Record is already existing hence update the row..
850 igs_as_doc_fee_pmnt_pkg.update_row (
851 x_rowid => non_free_fee_rec.row_id,
852 x_person_id => non_free_fee_rec.person_id,
853 x_fee_paid_date => non_free_fee_rec.fee_paid_date,
854 x_fee_amount => non_free_fee_rec.fee_amount,
855 x_fee_recorded_date => non_free_fee_rec.fee_recorded_date,
856 x_fee_recorded_by => non_free_fee_rec.fee_recorded_by,
857 x_mode => 'R',
858 x_plan_id => non_free_fee_rec.plan_id,
859 x_invoice_id => non_free_fee_rec.invoice_id,
860 x_plan_discon_from => non_free_fee_rec.plan_discon_from,
861 x_plan_discon_by => non_free_fee_rec.plan_discon_by,
862 x_num_of_copies => p_num_copies, --NVL (non_free_fee_rec.num_of_copies, 0) + p_num_copies,
863 x_prev_paid_plan => non_free_fee_rec.prev_paid_plan,
864 x_cal_type => non_free_fee_rec.cal_type,
865 x_ci_sequence_number => non_free_fee_rec.ci_sequence_number,
866 x_program_on_file => non_free_fee_rec.program_on_file,
867 x_return_status => l_return_status,
868 x_msg_data => l_msg_data,
869 x_msg_count => l_msg_count
870 );
871 END IF; -- IF FOR RECORD EXISTS FOR INSERT
872 END IF; -- l_free_plan = 'Y'
873 ELSIF p_operation = 'D' THEN
874 IF l_free_plan = 'Y' THEN
875 OPEN cur_doc_fee_upd;
876 FETCH cur_doc_fee_upd INTO fee_rec;
877 CLOSE cur_doc_fee_upd;
878 IF ((NVL (fee_rec.num_of_copies, 0) - p_num_copies) < 0) THEN
879 lnumcopies := 0;
880 ELSE
881 lnumcopies := fee_rec.num_of_copies - p_num_copies;
882 END IF;
883 IF fee_rec.person_id IS NOT NULL THEN
884 igs_as_doc_fee_pmnt_pkg.update_row (
885 x_rowid => fee_rec.row_id,
886 x_person_id => fee_rec.person_id,
887 x_fee_paid_date => fee_rec.fee_paid_date,
888 x_fee_amount => fee_rec.fee_amount,
889 x_fee_recorded_date => fee_rec.fee_recorded_date,
890 x_fee_recorded_by => fee_rec.fee_recorded_by,
891 x_mode => 'R',
892 x_plan_id => fee_rec.plan_id,
893 x_invoice_id => fee_rec.invoice_id,
894 x_plan_discon_from => fee_rec.plan_discon_from,
895 x_plan_discon_by => fee_rec.plan_discon_by,
896 x_num_of_copies => lnumcopies,
897 x_prev_paid_plan => fee_rec.prev_paid_plan,
898 x_cal_type => fee_rec.cal_type,
899 x_ci_sequence_number => fee_rec.ci_sequence_number,
900 x_program_on_file => fee_rec.program_on_file,
901 x_return_status => l_return_status,
902 x_msg_data => l_msg_data,
903 x_msg_count => l_msg_count
904 );
905 END IF;
906 ELSE -- i.e. l_free_plan = 'N'
907 OPEN cur_doc_fee_upd_non_free;
908 FETCH cur_doc_fee_upd_non_free INTO non_free_fee_rec;
909 CLOSE cur_doc_fee_upd_non_free;
910 IF ((NVL (non_free_fee_rec.num_of_copies, 0) - p_num_copies) < 0) THEN
911 lnumcopies := 0;
912 ELSE
913 lnumcopies := non_free_fee_rec.num_of_copies - p_num_copies;
914 END IF;
915 IF non_free_fee_rec.person_id IS NOT NULL THEN
916 igs_as_doc_fee_pmnt_pkg.update_row (
917 x_rowid => non_free_fee_rec.row_id,
918 x_person_id => non_free_fee_rec.person_id,
919 x_fee_paid_date => non_free_fee_rec.fee_paid_date,
920 x_fee_amount => non_free_fee_rec.fee_amount,
921 x_fee_recorded_date => non_free_fee_rec.fee_recorded_date,
922 x_fee_recorded_by => non_free_fee_rec.fee_recorded_by,
923 x_mode => 'R',
924 x_plan_id => non_free_fee_rec.plan_id,
925 x_invoice_id => non_free_fee_rec.invoice_id,
926 x_plan_discon_from => non_free_fee_rec.plan_discon_from,
927 x_plan_discon_by => non_free_fee_rec.plan_discon_by,
928 x_num_of_copies => lnumcopies,
929 x_prev_paid_plan => non_free_fee_rec.prev_paid_plan,
930 x_cal_type => non_free_fee_rec.cal_type,
931 x_ci_sequence_number => non_free_fee_rec.ci_sequence_number,
932 x_program_on_file => non_free_fee_rec.program_on_file,
933 x_return_status => l_return_status,
934 x_msg_data => l_msg_data,
935 x_msg_count => l_msg_count
936 );
937 END IF;
938 END IF; -- l_free_plan = 'Y'
939 END IF; --IF p_operation
940 END upd_doc_fee_pmnt;
941 END igs_as_documents_api;