[Home] [Help]
PACKAGE BODY: APPS.IGS_CO_GEN_002
Source
1 PACKAGE BODY IGS_CO_GEN_002 AS
2 /* $Header: IGSCO02B.pls 120.1 2006/01/06 04:10:11 gmaheswa noship $ */
3
4 /*
5 Change History
6 Who When What
7 pkpatel 24-APR-2003 Bug 2908844
8 Stubbed the procedure corp_ins_spl_detail since its no longer used.
9 gmaheswa 5-Jan-2004 Bug 4869737 Added a call to SET_ORG_ID in CORP_UPD_OC_DT_SENT to disable OSS for R12.
10 */
11
12 FUNCTION corp_del_cori_spl(
13 p_correspondence_type IN VARCHAR2 ,
14 p_reference_number IN NUMBER ,
15 p_letter_delete IN VARCHAR2 DEFAULT 'N',
16 p_message_name OUT NOCOPY varchar2 )
17 RETURN BOOLEAN AS
18 e_resource_busy EXCEPTION;
19 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
20 lv_param_values VARCHAR2(1080);
21 BEGIN -- corp_del_cori_spl
22 -- This module deletes all records related to a correspondence item.
23 -- It also deletes any system IGS_PE_PERSON letter details related to the
24 -- correspondence item.
25 -- If any records are locked then we rollback and return false.
26 DECLARE
27 cst_spl_seqnum CONSTANT VARCHAR2(10) := 'SPL_SEQNUM';
28 v_spl_sequence_number NUMBER(10);
29 v_dummy VARCHAR2(1);
30 v_sys_generated_ind IGS_CO_TYPE.sys_generated_ind%TYPE;
31 CURSOR c_corit(
32 cp_correspondence_type IGS_CO_ITM.CORRESPONDENCE_TYPE%TYPE,
33 cp_reference_number IGS_CO_ITM.reference_number%TYPE) IS
34 SELECT cort.sys_generated_ind
35 FROM IGS_CO_ITM cori,
36 IGS_CO_TYPE cort
37 WHERE cori.CORRESPONDENCE_TYPE = cp_correspondence_type
38 AND cori.reference_number = cp_reference_number
39 AND cort.CORRESPONDENCE_TYPE = cori.CORRESPONDENCE_TYPE;
40 CURSOR c_sl(
41 cp_correspondence_type IGS_CO_ITM.CORRESPONDENCE_TYPE%TYPE) IS
42 SELECT 'x'
43 FROM IGS_CO_S_LTR sl
44 WHERE sl.CORRESPONDENCE_TYPE = cp_correspondence_type;
45 CURSOR c_cdo(
46 cp_correspondence_type IGS_CO_DTL_OLE.CORRESPONDENCE_TYPE%TYPE,
47 cp_reference_number IGS_CO_DTL_OLE.reference_number%TYPE) IS
48 SELECT ROWID
49 FROM IGS_CO_DTL_OLE cdo
50 WHERE cdo.CORRESPONDENCE_TYPE = cp_correspondence_type
51 AND cdo.reference_number = cp_reference_number
52 FOR UPDATE OF cdo.CORRESPONDENCE_TYPE NOWAIT;
53 CURSOR c_cd(
54 cp_correspondence_type IGS_CO_DTL.CORRESPONDENCE_TYPE%TYPE,
55 cp_reference_number IGS_CO_DTL.reference_number%TYPE) IS
56 SELECT ROWID
57 FROM IGS_CO_DTL cd
58 WHERE cd.CORRESPONDENCE_TYPE = cp_correspondence_type
59 AND cd.reference_number = cp_reference_number
60 FOR UPDATE OF cd.CORRESPONDENCE_TYPE NOWAIT;
61 CURSOR c_ocr(
62 cp_correspondence_type IGS_CO_OU_CO_REF.CORRESPONDENCE_TYPE%TYPE,
63 cp_reference_number IGS_CO_OU_CO_REF.reference_number%TYPE) IS
64 SELECT ocr.rowid,
65 ocr.other_reference,
66 ocr.person_id
67 FROM IGS_CO_OU_CO_REF ocr
68 WHERE ocr.CORRESPONDENCE_TYPE = cp_correspondence_type
69 AND ocr.reference_number = cp_reference_number
70 AND ocr.S_OTHER_REFERENCE_TYPE = cst_spl_seqnum
71 FOR UPDATE OF ocr.other_reference NOWAIT;
72 CURSOR c_spl(
73 cp_sequence_number IGS_CO_S_PER_LTR.sequence_number%TYPE,
74 cp_person_id IGS_CO_S_PER_LTR.person_id%TYPE) IS
75 SELECT ROWID
76 FROM IGS_CO_S_PER_LTR spl
77 WHERE spl.sequence_number = cp_sequence_number
78 AND spl.person_id = cp_person_id
79 FOR UPDATE OF spl.sequence_number NOWAIT;
80 CURSOR c_splp(
81 cp_sequence_number IGS_CO_S_PER_LT_PARM.sequence_number%TYPE,
82 cp_person_id IGS_CO_S_PER_LT_PARM.person_id%TYPE) IS
83 SELECT ROWID
84 FROM IGS_CO_S_PER_LT_PARM splp
85 WHERE splp.spl_sequence_number = cp_sequence_number
86 AND splp.person_id = cp_person_id
87 FOR UPDATE OF splp.spl_sequence_number NOWAIT;
88 CURSOR c_splrg(
89 cp_sequence_number IGS_CO_S_PERLT_RPTGP.sequence_number%TYPE,
90 cp_person_id IGS_CO_S_PERLT_RPTGP.person_id%TYPE) IS
91 SELECT ROWID
92 FROM IGS_CO_S_PERLT_RPTGP splrg
93 WHERE splrg.spl_sequence_number = cp_sequence_number
94 AND splrg.person_id = cp_person_id
95 ORDER BY splrg.sequence_number DESC, splrg.sup_repeating_group_cd
96 FOR UPDATE OF splrg.spl_sequence_number NOWAIT;
97 CURSOR c_aal(
98 cp_sequence_number IGS_AD_APPL_LTR.spl_sequence_number%TYPE,
99 cp_person_id IGS_AD_APPL_LTR.person_id%TYPE) IS
100 SELECT ROWID,
101 PERSON_ID,
102 ADMISSION_APPL_NUMBER,
103 CORRESPONDENCE_TYPE,
104 SEQUENCE_NUMBER,
105 COMPOSED_IND,
106 LETTER_REFERENCE_NUMBER,
107 SPL_SEQUENCE_NUMBER
108 FROM IGS_AD_APPL_LTR aal
109 WHERE aal.spl_sequence_number = cp_sequence_number
110 AND aal.person_id = cp_person_id
111 FOR UPDATE OF aal.letter_reference_number, aal.spl_sequence_number NOWAIT;
112 CURSOR c_ocr_1(
113 cp_correspondence_type IGS_CO_OU_CO_REF.CORRESPONDENCE_TYPE%TYPE,
114 cp_reference_number IGS_CO_OU_CO_REF.reference_number%TYPE) IS
115 SELECT ROWID
116 FROM IGS_CO_OU_CO_REF ocr
117 WHERE ocr.CORRESPONDENCE_TYPE = cp_correspondence_type
118 AND ocr.reference_number = cp_reference_number
119 FOR UPDATE OF ocr.CORRESPONDENCE_TYPE NOWAIT;
120 CURSOR c_oc(
121 cp_correspondence_type IGS_CO_OU_CO.CORRESPONDENCE_TYPE%TYPE,
122 cp_reference_number IGS_CO_OU_CO.reference_number%TYPE) IS
123 SELECT ROWID
124 FROM IGS_CO_OU_CO oc
125 WHERE oc.CORRESPONDENCE_TYPE = cp_correspondence_type
126 AND oc.reference_number = cp_reference_number
127 FOR UPDATE OF oc.CORRESPONDENCE_TYPE NOWAIT;
128 CURSOR c_cit(
129 cp_correspondence_type IGS_CO_ITM.CORRESPONDENCE_TYPE%TYPE,
130 cp_reference_number IGS_CO_ITM.reference_number%TYPE) IS
131 SELECT ROWID
132 FROM IGS_CO_ITM cit
133 WHERE cit.CORRESPONDENCE_TYPE = cp_correspondence_type
134 AND cit.reference_number = cp_reference_number
135 FOR UPDATE OF cit.CORRESPONDENCE_TYPE NOWAIT;
136
137 BEGIN
138 COMMIT;
139 SAVEPOINT sp_before_delete;
140 p_message_name := Null;
141 OPEN c_corit(
142 p_correspondence_type,
143 p_reference_number);
144 FETCH c_corit INTO v_sys_generated_ind;
145 IF(c_corit%NOTFOUND) THEN
146 CLOSE c_corit;
147 ROLLBACK TO sp_before_delete;
148 p_message_name := 'IGS_CO_CORITEM_DOESNOT_EXIST';
149 RETURN FALSE;
150 END IF;
151 CLOSE c_corit;
152 -- If this is for the deletion of a letter check it is system generated and
153 -- there is a letter with this correspondence type.
154 IF(p_letter_delete = 'Y') THEN
155 IF(v_sys_generated_ind = 'N') THEN
156 ROLLBACK TO sp_before_delete;
157 p_message_name := 'IGS_CO_CORTYPE_ISNOT_SYSGEN';
158 RETURN FALSE;
159 END IF;
160 OPEN c_sl(
161 p_correspondence_type);
162 FETCH c_sl INTO v_dummy;
163 IF(c_sl%NOTFOUND) THEN
164 CLOSE c_sl;
165 ROLLBACK TO sp_before_delete;
166 p_message_name := 'IGS_AD_DFLT_FEECAT_MAPPING';
167 RETURN FALSE;
168 END IF;
169 CLOSE c_sl;
170 END IF;
171 -- delete any IGS_CO_DTL_OLE records for this correspondence item
172 FOR v_cdo_rec IN c_cdo(
173 p_correspondence_type,
174 p_reference_number) LOOP
175 IGS_CO_DTL_OLE_PKG.DELETE_ROW(X_ROWID=>v_cdo_rec.ROWID);
176 END LOOP;
177 -- delete any IGS_CO_DTL records for this IGS_CO_ITM
178 FOR vcd_rec IN c_cd(
179 p_correspondence_type,
180 p_reference_number) LOOP
181 IGS_CO_DTL_PKG.DELETE_ROW(X_ROWID=>vcd_rec.ROWID);
182 END LOOP;
183 IF(p_letter_delete = 'Y') THEN
184 -- find related IGS_CO_S_PER_LTR records from IGS_CO_OU_CO_REF
185 FOR v_ocr_rec IN c_ocr(
186 p_correspondence_type,
187 p_reference_number) LOOP
188 v_spl_sequence_number := TO_NUMBER(v_ocr_rec.other_reference);
189 FOR v_spl_rec IN c_spl(
190 v_spl_sequence_number,
191 v_ocr_rec.person_id) LOOP
192 -- delete any parameters for this letter
193 FOR v_splp_rec IN c_splp(
194 v_spl_sequence_number,
195 v_ocr_rec.person_id) LOOP
196 IGS_CO_S_PER_LT_PARM_PKG.DELETE_ROW(X_ROWID=>v_splp_rec.ROWID);
197 END LOOP;
198 -- delete the repeating groups. Must delete in the correct order as
199 -- table can contain parent/child relationship
200 FOR v_splrg_rec IN c_splrg(
201 v_spl_sequence_number,
202 v_ocr_rec.person_id) LOOP
203 IGS_CO_S_PERLT_RPTGP_PKG.DELETE_ROW(X_ROWID=>v_splrg_rec.ROWID);
204 END LOOP;
205 -- remove the reference to the IGS_CO_S_PER_LTR sequence number
206 -- from the IGS_AD_APPL_LTR record
207 FOR v_aal_rec IN c_aal(
208 v_spl_sequence_number,
209 v_ocr_rec.person_id) LOOP
210 IGS_AD_APPL_LTR_PKG.UPDATE_ROW(
211 X_ROWID => v_aal_rec.ROWID,
212 X_PERSON_ID => v_aal_rec.PERSON_ID,
213 X_ADMISSION_APPL_NUMBER =>v_aal_rec.ADMISSION_APPL_NUMBER ,
214 X_CORRESPONDENCE_TYPE =>v_aal_rec.CORRESPONDENCE_TYPE,
215 X_SEQUENCE_NUMBER =>v_aal_rec.SEQUENCE_NUMBER,
216 X_COMPOSED_IND =>v_aal_rec.COMPOSED_IND,
217 X_LETTER_REFERENCE_NUMBER =>NULL,
218 X_SPL_SEQUENCE_NUMBER =>NULL,
219 X_MODE => 'R'
220 );
221 END LOOP;
222 -- delete the IGS_CO_S_PER_LTR record
223 IGS_CO_S_PER_LTR_PKG.DELETE_ROW(X_ROWID => v_spl_rec.rowid);
224 END LOOP;
225 -- delete current IGS_CO_OU_CO_REF record
226 IGS_CO_OU_CO_REF_PKG.DELETE_ROW(X_ROWID => v_ocr_rec.ROWID );
227 END LOOP;
228 ELSE -- p_letter_delete = 'N'
229 -- delete all the out NOCOPY correspondence ref records for this correspondence item
230 FOR v_ocr_1_rec IN c_ocr_1(
231 p_correspondence_type,
232 p_reference_number) LOOP
233 IGS_CO_OU_CO_REF_PKG.DELETE_ROW(X_ROWID=>v_ocr_1_rec.ROWID);
234 END LOOP;
235 END IF;
236 -- delete all the outgoing correspondence records for this correspondence item.
237 FOR v_oc_rec IN c_oc(
238 p_correspondence_type,
239 p_reference_number) LOOP
240 IGS_CO_OU_CO_PKG.DELETE_ROW(X_ROWID=>v_oc_rec.ROWID);
241 END LOOP;
242 -- delete correspondence item
243 FOR v_cit_rec IN c_cit(
244 p_correspondence_type,
245 p_reference_number) LOOP
246 IGS_CO_ITM_PKG.DELETE_ROW(X_ROWID=>v_cit_rec.ROWID);
247 END LOOP;
248 COMMIT;
249 RETURN TRUE;
250 END;
251 EXCEPTION
252 WHEN e_resource_busy THEN
253 ROLLBACK TO sp_before_delete;
254 p_message_name := 'IGS_CO_CORITEM_REC_LOCKED';
255 RETURN FALSE;
256 WHEN OTHERS THEN
257 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
258 Fnd_Message.Set_Token('NAME','IGS_CO_GEN_002.corp_del_cori_spl');
259 IGS_GE_MSG_STACK.ADD;
260 lv_param_values := p_correspondence_type||','||TO_CHAR(p_reference_number)||','||p_letter_delete;
261 Fnd_Message.Set_Name('IGS','IGS_GE_PARAMETERS');
262 Fnd_Message.Set_Token('VALUE','lv_param_values');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END corp_del_cori_spl;
266
267 FUNCTION corp_ins_splp(
268 p_person_id IN NUMBER ,
269 p_correspondence_type IN VARCHAR2 ,
270 p_letter_reference_number IN NUMBER ,
271 p_spl_sequence_number IN NUMBER ,
272 p_letter_parameter_type IN VARCHAR2 ,
273 p_letter_repeating_group_cd IN VARCHAR2 ,
274 p_splrg_sequence_number IN NUMBER ,
275 p_record_number IN NUMBER ,
276 p_letter_context_parameter IN VARCHAR2 ,
277 p_extra_context OUT NOCOPY VARCHAR2 ,
278 p_stored_ind OUT NOCOPY VARCHAR2 ,
279 p_message_name OUT NOCOPY varchar2,
280 p_letter_order_number IN number)
281 RETURN BOOLEAN AS
282 BEGIN -- corp_ins_splp
283 -- This module calculates the value for a IGS_CO_LTR_PARAM and inserts
284 -- a record into the IGS_CO_S_PER_LT_PARM table.
285 DECLARE
286 cst_in CONSTANT VARCHAR2(2) := 'IN';
287 cst_out CONSTANT VARCHAR2(3) := 'OUT';
288 cst_phrase CONSTANT VARCHAR2(6) := 'PHRASE';
289 cst_adm CONSTANT VARCHAR2(3) := 'ADM';
290 cst_person_id CONSTANT VARCHAR2(11) := 'p_person_id';
291 cst_rec_num CONSTANT VARCHAR2(15) := 'p_record_number';
292 cst_let_context_param CONSTANT VARCHAR2(26) := 'p_letter_context_parameter';
293 cst_cor_type CONSTANT VARCHAR2(21) := 'p_correspondence_type';
294 cst_let_ref_num CONSTANT VARCHAR2(25) := 'p_letter_reference_number';
295 cst_s_let_parm_type CONSTANT VARCHAR2(25) := 'p_s_letter_parameter_type';
296 cst_p_let_ref_num CONSTANT VARCHAR2(25) := 'p_letter_reference_number';
297 cst_s_let_param_type CONSTANT VARCHAR2(26) := 'v_s_letter_parameter_type';
298 cst_v_extra_context CONSTANT VARCHAR2(15) := 'v_extra_context';
299 cst_v_value CONSTANT VARCHAR2(7) := 'v_value';
300 v_dbms INTEGER;
301 v_dbms_return INTEGER;
302 v_value VARCHAR2(2000);
303 v_sequence_number IGS_CO_S_PER_LT_PARM.sequence_number%TYPE;
304 v_lpt_s_letter_parameter_type
305 IGS_CO_LTR_PARM_TYPE.S_LETTER_PARAMETER_TYPE%TYPE;
306 v_letter_text IGS_CO_LTR_PARM_TYPE.letter_text%TYPE;
307 v_code_block IGS_CO_S_LTR_PARAM.code_block%TYPE;
308 v_slpt_s_letter_parameter_type
309 IGS_CO_S_LTR_PARAM.S_LETTER_PARAMETER_TYPE%TYPE;
310 v_adm_appl_num IGS_AD_APPL.admission_appl_number%TYPE;
311 v_aal_sequence_number IGS_CO_S_PER_LT_PARM.sequence_number%TYPE;
312 v_extra_context VARCHAR2(100);
313 v_message_name varchar2(30);
314 X_ROWID VARCHAR2(25);
315 CURSOR c_get_nxt_seq IS
316 SELECT IGS_CO_S_PER_LT_PARM_SEQ_NUM_S.NEXTVAL
317 FROM DUAL;
318 CURSOR c_lpt IS
319 SELECT lpt.S_LETTER_PARAMETER_TYPE,
320 lpt.letter_text,
321 slpt.code_block,
322 slpt.S_LETTER_PARAMETER_TYPE
323 FROM IGS_CO_LTR_PARM_TYPE lpt,
324 IGS_CO_S_LTR_PARAM slpt
325 WHERE lpt.LETTER_PARAMETER_TYPE = p_letter_parameter_type AND
326 slpt.S_LETTER_PARAMETER_TYPE = lpt.S_LETTER_PARAMETER_TYPE;
327 CURSOR c_slpta_in (
328 cp_s_letter_parameter_type
329 IGS_CO_S_LTR_PARAM.S_LETTER_PARAMETER_TYPE%TYPE) IS
330 SELECT slpta.bind_variable
331 FROM IGS_CO_S_LTR_PR_ARG slpta
332 WHERE slpta.S_LETTER_PARAMETER_TYPE = cp_s_letter_parameter_type AND
333 slpta.direction = cst_in;
334 CURSOR c_slpta_out(
335 cp_s_letter_parameter_type
336 IGS_CO_S_LTR_PARAM.S_LETTER_PARAMETER_TYPE%TYPE) IS
337 SELECT slpta.bind_variable
338 FROM IGS_CO_S_LTR_PR_ARG slpta
339 WHERE slpta.S_LETTER_PARAMETER_TYPE = cp_s_letter_parameter_type AND
340 slpta.direction = cst_out;
341 BEGIN
342 -- Initialise output parameters
343 p_message_name := Null;
344 p_stored_ind := 'N';
345 -- Get IGS_CO_S_LTR_PARAM from IGS_CO_LTR_PARM_TYPE
346 OPEN c_lpt;
347 FETCH c_lpt INTO v_lpt_s_letter_parameter_type,
348 v_letter_text,
349 v_code_block,
350 v_slpt_s_letter_parameter_type;
351 IF(c_lpt%NOTFOUND) THEN
352 CLOSE c_lpt;
353 p_message_name := 'IGS_GE_VAL_DOES_NOT_XS';
354 RETURN FALSE;
355 END IF;
356 CLOSE c_lpt;
357 -- Set up all the bind variables that could be passed into the dynamic SQL
358 IF(v_lpt_s_letter_parameter_type <> cst_phrase) THEN
359 IF(v_letter_text IS NOT NULL) THEN
360 IF(p_record_number <> 1) THEN
361 p_stored_ind := 'N';
362 RETURN TRUE;
363 ELSE
364 v_value := v_letter_text;
365 END IF;
366 ELSE
367 IF v_code_block IS NULL THEN
368 p_stored_ind := 'N';
369 RETURN TRUE;
370 END IF;
371 -- Open a cursor for dynamic SQL
372 v_dbms := DBMS_SQL.OPEN_CURSOR;
373 -- Put the block of code from the IGS_CO_S_LTR_PARAM table
374 -- into the dynmaic SQL cursor
375 DBMS_SQL.PARSE(
376 v_dbms,
377 v_code_block,
378 DBMS_SQL.NATIVE);
379 -- Set up all the bind variables that could be passed into the
380 -- dynamic SQL
381 FOR v_slpta_in_rec IN c_slpta_in (
382 v_slpt_s_letter_parameter_type) LOOP
383 IF v_slpta_in_rec.bind_variable = cst_rec_num THEN
384 DBMS_SQL.BIND_VARIABLE(
385 v_dbms,
386 'p_record_number',
387 p_record_number);
388 ELSIF v_slpta_in_rec.bind_variable = cst_let_context_param THEN
389 DBMS_SQL.BIND_VARIABLE(
390 v_dbms,
391 'p_letter_context_parameter',
392 p_letter_context_parameter);
393 ELSIF v_slpta_in_rec.bind_variable = cst_person_id THEN
394 DBMS_SQL.BIND_VARIABLE(
395 v_dbms,
396 'p_person_id',
397 p_person_id);
398 ELSIF v_slpta_in_rec.bind_variable = cst_cor_type THEN
399 DBMS_SQL.BIND_VARIABLE(
400 v_dbms,
401 'p_correspondence_type',
402 p_correspondence_type);
403 ELSIF v_slpta_in_rec.bind_variable = cst_let_ref_num THEN
404 DBMS_SQL.BIND_VARIABLE(
405 v_dbms,
406 'p_letter_reference_number',
407 p_letter_reference_number);
408 ELSIF v_slpta_in_rec.bind_variable = cst_s_let_param_type then
409 DBMS_SQL.BIND_VARIABLE(
410 v_dbms,
411 'v_s_letter_parameter_type',
412 v_lpt_s_letter_parameter_type);
413 END IF;
414 END LOOP;
415 -- set up all the bind variables that may be passed out NOCOPY of the dynamic SQL
416 FOR v_slpta_out_rec IN c_slpta_out (
417 v_slpt_s_letter_parameter_type) LOOP
418 IF v_slpta_out_rec.bind_variable = cst_v_value THEN
419 DBMS_SQL.BIND_VARIABLE(
420 v_dbms,
421 'v_value',
422 NULL,
423 2000);
424 ELSIF v_slpta_out_rec.bind_variable = cst_v_extra_context THEN
425 DBMS_SQL.BIND_VARIABLE(
426 v_dbms,
427 'v_extra_context',
428 NULL,
429 2000);
430 END IF;
431 END LOOP;
432 -- execute the dynmaic SQL block of code.
433 v_dbms_return := DBMS_SQL.EXECUTE(v_dbms);
434 -- Copy values of bind variables to program variables.
435 FOR v_slpta_out_rec IN c_slpta_out (
436 v_slpt_s_letter_parameter_type) LOOP
437 IF v_slpta_out_rec.bind_variable = cst_v_value THEN
438 DBMS_SQL.VARIABLE_VALUE(
439 v_dbms,
440 'v_value',
441 v_value);
442 ELSIF v_slpta_out_rec.bind_variable = cst_v_extra_context THEN
443 DBMS_SQL.VARIABLE_VALUE(
444 v_dbms,
445 'v_extra_context',
446 p_extra_context);
447 END IF;
448 END LOOP;
449 -- Close the dynamic SQL cursor
450 DBMS_SQL.CLOSE_CURSOR(v_dbms);
451 END IF; -- v_letter_text IS NULL
452 -- After all the tests are done check if v_value is NULL
453 IF(v_value IS NOT NULL) THEN
454 p_stored_ind := 'Y';
455 -- splp_sequence_number.NEXTVAL
456 OPEN c_get_nxt_seq;
457 FETCH c_get_nxt_seq INTO v_sequence_number;
458 CLOSE c_get_nxt_seq;
459 IGS_CO_S_PER_LT_PARM_PKG.INSERT_ROW(X_ROWID=>X_ROWID,
460 X_PERSON_ID=>p_person_id,
461 X_CORRESPONDENCE_TYPE=>p_correspondence_type,
462 X_LETTER_REFERENCE_NUMBER=>p_letter_reference_number,
463 X_SPL_SEQUENCE_NUMBER=>p_spl_sequence_number,
464 X_LETTER_PARAMETER_TYPE=>p_letter_parameter_type,
465 X_SEQUENCE_NUMBER=>v_sequence_number,
466 X_PARAMETER_VALUE=>v_value,
467 X_LETTER_REPEATING_GROUP_CD=>p_letter_repeating_group_cd,
468 X_SPLRG_SEQUENCE_NUMBER=>p_splrg_sequence_number,
469 X_MODE=>'R',
470 x_letter_order_number => p_letter_order_number,
471 X_ORG_ID => FND_PROFILE.value('ORG_ID'));
472 END IF;
473 ELSE -- IGS_CO_S_LTR_PARAM = 'PHRASE'
474 IF p_record_number <> 1 THEN
475 p_stored_ind := 'N';
476 RETURN TRUE;
477 END IF;
478 IF(igs_ad_val_apcl.corp_val_slet_slrt(
479 p_correspondence_type,
480 p_letter_reference_number,
481 cst_adm,
482 v_message_name) = TRUE) THEN
483 v_adm_appl_num := TO_NUMBER(IGS_GE_GEN_002.genp_get_delimit_str(
484 p_letter_context_parameter,1,'|'));
485 v_aal_sequence_number := TO_NUMBER(IGS_GE_GEN_002.genp_get_delimit_str(
486 p_letter_context_parameter,2,'|'));
487 declare
488 p_letter_order_number NUMBER(3);
489 cursor get_lon IS SELECT letter_order_number
490 from igs_co_ltr_param
491 where correspondence_type = p_correspondence_type and letter_parameter_type = p_letter_parameter_type and
492 letter_reference_number = p_letter_reference_number;
493
494 begin
495 open get_lon;
496 fetch get_lon into p_letter_order_number;
497 close get_lon;
498
499 IF(IGS_AD_GEN_011.admp_ins_phrase_splp(
500 p_person_id,
501 v_adm_appl_num,
502 p_correspondence_type,
503 v_aal_sequence_number,
504 p_letter_parameter_type,
505 p_letter_reference_number,
506 p_spl_sequence_number,
507 p_letter_repeating_group_cd,
508 p_splrg_sequence_number, p_letter_order_number ) = TRUE) THEN
509 p_stored_ind := 'Y';
510 END IF;
511 end;
512 END IF;
513 END IF;
514 RETURN TRUE;
515 EXCEPTION
516 WHEN OTHERS THEN
517 IF DBMS_SQL.IS_OPEN(v_dbms) THEN
518 DBMS_SQL.CLOSE_CURSOR(v_dbms);
519 END IF;
520 IF c_get_nxt_seq%ISOPEN THEN
521 CLOSE c_get_nxt_seq;
522 END IF;
523 IF c_lpt%ISOPEN THEN
524 CLOSE c_lpt;
525 END IF;
526 IF c_slpta_in%ISOPEN THEN
527 CLOSE c_slpta_in;
528 END IF;
529 IF c_slpta_out%ISOPEN THEN
530 CLOSE c_slpta_out;
531 END IF;
532 RAISE;
533 END;
534 EXCEPTION
535 WHEN OTHERS THEN
536 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
537 Fnd_Message.Set_Token('NAME','IGS_CO_GEN_002.corp_ins_splp');
538 IGS_GE_MSG_STACK.ADD;
539 App_Exception.Raise_Exception;
540 END corp_ins_splp;
541 --
542 FUNCTION corp_ins_spl_detail(
543 p_person_id IN NUMBER ,
544 p_correspondence_type IN VARCHAR2 ,
545 p_letter_reference_number IN NUMBER ,
546 p_letter_context_parameter IN VARCHAR2 ,
547 p_spl_sequence_number OUT NOCOPY NUMBER ,
548 p_message_name OUT NOCOPY varchar2)
549 RETURN BOOLEAN AS
550 /*
551 Change History
552 Who When What
553 pkpatel 24-APR-2003 Bug 2908844
554 The procedure is no longer used. Hence stubbed.
555 */
556 BEGIN
557
558 RETURN TRUE;
559
560 END corp_ins_spl_detail;
561 --
562 PROCEDURE CORP_UPD_OC_DT_SENT(
563 errbuf out NOCOPY varchar2,
564 retcode out NOCOPY varchar2,
565 p_reference_number IN NUMBER ,
566 p_person_id IN NUMBER ,
567 p_issue_dt_c IN VARCHAR2 ,
568 p_dt_sent_c IN varchar2 )
569 AS
570 e_no_records_found EXCEPTION;
571 e_resource_busy EXCEPTION;
572 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
573 CURSOR c_outgoing_correspondence (
574 cp_reference_number IGS_CO_OU_CO.reference_number%TYPE,
575 cp_person_id IGS_CO_OU_CO.person_id%TYPE,
576 cp_issue_dt IGS_CO_OU_CO.issue_dt%TYPE) IS
577 SELECT oc.ROWID,
578 oc.PERSON_ID ,
579 oc.CORRESPONDENCE_TYPE,
580 oc.REFERENCE_NUMBER,
581 oc.ISSUE_DT,
582 oc.DT_SENT ,
583 oc.UNKNOWN_RETURN_DT,
584 oc.ADDR_TYPE,
585 oc.TRACKING_ID,
586 oc.COMMENTS,
587 oc.LETTER_REFERENCE_NUMBER ,
588 oc.SPL_SEQUENCE_NUMBER
589 FROM IGS_CO_OU_CO oc
590 WHERE oc.reference_number = cp_reference_number AND
591 ((oc.person_id = cp_person_id AND
592 (cp_person_id IS NOT NULL OR cp_person_id <> 0)) OR
593 (cp_person_id IS NULL OR cp_person_id = 0))AND
594 TRUNC(oc.issue_dt) = cp_issue_dt AND
595 oc.dt_sent IS NULL
596 FOR UPDATE OF oc.dt_sent NOWAIT;
597 v_record_found BOOLEAN;
598 v_other_detail VARCHAR2(255);
599 p_issue_dt DATE;
600 p_dt_sent DATE;
601 BEGIN
602 igs_ge_gen_003.set_org_id;
603
604 retcode:=0;
605 p_issue_dt := TO_DATE(p_issue_dt_c,'YYYY/MM/DD HH24:MI:SS');
606 p_dt_sent := TO_DATE(p_dt_sent_c,'YYYY/MM/DD HH24:MI:SS');
607 -- This module updates outgoing correspondence records with date sent.
608 -- IGS_GE_NOTE: person_id parameter may be optionally provided.
609 -- If a lock is encountered at any time, then its handled as an exception,
610 -- by sending a message via DBMS_OUTPUT and re-raising the exception.
611 v_record_found := FALSE;
612 FOR v_outgoing_correspondence_rec IN c_outgoing_correspondence(
613 p_reference_number,
614 p_person_id,
615 p_issue_dt) LOOP
616 v_record_found := TRUE;
617 IGS_CO_OU_CO_PKG.update_row(
618 X_ROWID => v_outgoing_correspondence_rec.ROWID,
619 X_PERSON_ID =>v_outgoing_correspondence_rec.PERSON_ID ,
620 X_CORRESPONDENCE_TYPE =>v_outgoing_correspondence_rec.CORRESPONDENCE_TYPE,
621 X_REFERENCE_NUMBER => v_outgoing_correspondence_rec.REFERENCE_NUMBER,
622 X_ISSUE_DT => v_outgoing_correspondence_rec.ISSUE_DT,
623 X_DT_SENT =>P_DT_SENT,
624 X_UNKNOWN_RETURN_DT => v_outgoing_correspondence_rec.UNKNOWN_RETURN_DT,
625 X_ADDR_TYPE => v_outgoing_correspondence_rec.ADDR_TYPE ,
626 X_TRACKING_ID => v_outgoing_correspondence_rec.TRACKING_ID ,
627 X_COMMENTS => v_outgoing_correspondence_rec.COMMENTS ,
628 X_LETTER_REFERENCE_NUMBER =>v_outgoing_correspondence_rec.letter_REFERENCE_NUMBER,
629 X_SPL_SEQUENCE_NUMBER =>v_outgoing_correspondence_rec.spl_sequence_NUMBER,
630 X_MODE=> 'R'
631 );
632 END LOOP;
633 IF(v_record_found = FALSE) THEN
634 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('FND', 'FORM_RECORD_DELETED')
635 || ' ' || TO_CHAR(p_reference_number)
636 || ' ' || TO_CHAR(p_person_id)
637 || ' ' || TO_CHAR(p_issue_dt)
638 || ' ' || TO_CHAR(p_dt_sent));
639 ELSE
640 COMMIT;
641 END IF;
642 EXCEPTION
643 WHEN e_resource_busy THEN
644 errbuf:= FND_MESSAGE.get_string('IGS','IGS_CO_CORREC_LOCK_ANOTHERUSR');
645 retcode :=2;
646 WHEN OTHERS THEN
647 retcode :=2;
648 errbuf:= FND_MESSAGE.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
649 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
650 END corp_upd_oc_dt_sent;
651 --
652 PROCEDURE corp_get_ocv_details(
653 p_person_id IN OUT NOCOPY IGS_CO_OU_CO.person_id%TYPE ,
654 p_correspondence_type IN OUT NOCOPY IGS_CO_ITM.CORRESPONDENCE_TYPE%TYPE ,
655 p_cal_type IN OUT NOCOPY IGS_CO_OU_CO_REF.CAL_TYPE%TYPE ,
656 p_ci_sequence_number IN OUT NOCOPY IGS_CO_OU_CO_REF.ci_sequence_number%TYPE,
657 p_course_cd IN OUT NOCOPY IGS_CO_OU_CO_REF.course_cd%TYPE ,
658 p_cv_version_number IN OUT NOCOPY IGS_CO_OU_CO_REF.cv_version_number%TYPE ,
659 p_unit_cd IN OUT NOCOPY IGS_CO_OU_CO_REF.unit_cd%TYPE ,
660 p_uv_version_number IN OUT NOCOPY IGS_CO_OU_CO_REF.uv_version_number%TYPE ,
661 p_s_other_reference_type IN OUT NOCOPY IGS_CO_OU_CO_REF.S_OTHER_REFERENCE_TYPE%TYPE ,
662 p_other_reference IN OUT NOCOPY IGS_CO_OU_CO_REF.other_reference%TYPE ,
663 p_addr_type IN OUT NOCOPY IGS_CO_OU_CO.ADDR_TYPE%TYPE ,
664 p_tracking_id IN OUT NOCOPY IGS_CO_OU_CO.tracking_id%TYPE ,
665 p_request_num IN OUT NOCOPY IGS_CO_ITM.request_num%TYPE ,
666 p_s_job_name IN OUT NOCOPY IGS_CO_ITM.s_job_name%TYPE ,
667 p_request_job_id IN OUT NOCOPY IGS_CO_ITM.request_job_id%TYPE ,
668 p_request_job_run_id IN OUT NOCOPY IGS_CO_ITM.request_job_run_id%TYPE,
669 p_correspondence_cat OUT NOCOPY VARCHAR2 ,
670 p_reference_number OUT NOCOPY IGS_CO_ITM.reference_number%TYPE ,
671 p_issue_dt OUT NOCOPY IGS_CO_OU_CO.issue_dt%TYPE ,
672 p_dt_sent OUT NOCOPY IGS_CO_OU_CO.dt_sent%TYPE ,
673 p_unknown_return_dt OUT NOCOPY IGS_CO_OU_CO.unknown_return_dt%TYPE ,
674 p_adt_description OUT NOCOPY varchar2,
675 p_create_dt OUT NOCOPY IGS_CO_ITM.create_dt%TYPE ,
676 p_originator_person_id OUT NOCOPY IGS_CO_ITM.originator_person_id%TYPE ,
677 p_output_num OUT NOCOPY IGS_CO_ITM.output_num%TYPE ,
678 p_oc_comments OUT NOCOPY IGS_CO_OU_CO.comments%TYPE ,
679 p_cori_comments OUT NOCOPY IGS_CO_ITM.comments%TYPE ,
680 p_message_name OUT NOCOPY varchar2 )
681 AS
682 BEGIN -- corp_get_ocv_details
683 -- This module gets information from the latest record in the outgoing
684 -- correspondence view for a set of variable parameters.
685 DECLARE
686 CURSOR c_ocv IS
687 SELECT person_id,
688 CORRESPONDENCE_TYPE,
689 CAL_TYPE,
690 ci_sequence_number,
691 course_cd,
692 cv_version_number,
693 unit_cd,
694 uv_version_number,
695 S_OTHER_REFERENCE_TYPE,
696 other_reference,
697 ADDR_TYPE,
698 tracking_id,
699 request_num,
700 s_job_name,
701 request_job_id,
702 request_job_run_id,
703 CORRESPONDENCE_CAT,
704 reference_number,
705 issue_dt,
706 dt_sent,
707 unknown_return_dt,
708 adt_description,
709 create_dt,
710 originator_person_id,
711 output_num,
712 oc_comments,
713 cori_comments
714 FROM IGS_CO_OU_CO_V
715 WHERE (p_person_id IS NULL OR
716 person_id = p_person_id) AND
717 (p_correspondence_type IS NULL OR
718 CORRESPONDENCE_TYPE = p_correspondence_type) AND
719 (p_cal_type IS NULL OR
720 CAL_TYPE = p_cal_type) AND
721 (p_ci_sequence_number IS NULL OR
722 ci_sequence_number = p_ci_sequence_number) AND
723 (p_course_cd IS NULL OR
724 course_cd = p_course_cd) AND
725 (p_cv_version_number IS NULL OR
726 cv_version_number = p_cv_version_number) AND
727 (p_unit_cd IS NULL OR
728 unit_cd = p_unit_cd) AND
729 (p_uv_version_number IS NULL OR
730 uv_version_number = p_uv_version_number) AND
731 (p_s_other_reference_type IS NULL OR
732 S_OTHER_REFERENCE_TYPE = p_s_other_reference_type) AND
733 (p_other_reference IS NULL OR
734 other_reference = p_other_reference) AND
735 (p_addr_type IS NULL OR
736 ADDR_TYPE = p_addr_type) AND
737 (p_tracking_id IS NULL OR
738 tracking_id = p_tracking_id) AND
739 (p_request_num IS NULL OR
740 request_num = p_request_num) AND
741 (p_s_job_name IS NULL OR
742 s_job_name = p_s_job_name) AND
743 (p_request_job_id IS NULL OR
744 request_job_id = p_request_job_id) AND
745 (p_request_job_run_id IS NULL OR
746 request_job_run_id = p_request_job_run_id)
747 ORDER BY issue_dt DESC,
748 reference_number DESC;
749 v_ocv_rec c_ocv%ROWTYPE;
750 BEGIN
751 -- Set the default message number
752 p_message_name := Null;
753 -- Cursor handling
754 OPEN c_ocv;
755 FETCH c_ocv INTO v_ocv_rec;
756 IF c_ocv%NOTFOUND THEN
757 CLOSE c_ocv;
758 -- Set the out NOCOPY parameters to null
759 p_person_id := NULL;
760 p_correspondence_type := NULL;
761 p_cal_type := NULL;
762 p_ci_sequence_number := NULL;
763 p_course_cd := NULL;
764 p_cv_version_number := NULL;
765 p_unit_cd := NULL;
766 p_uv_version_number := NULL;
767 p_s_other_reference_type := NULL;
768 p_other_reference := NULL;
769 p_addr_type := NULL;
770 p_tracking_id := NULL;
771 p_request_num := NULL;
772 p_s_job_name := NULL;
773 p_request_job_id := NULL;
774 p_request_job_run_id := NULL;
775 p_correspondence_cat := NULL;
776 p_reference_number := NULL;
777 p_issue_dt := NULL;
778 p_dt_sent := NULL;
779 p_unknown_return_dt := NULL;
780 p_adt_description := NULL;
781 p_create_dt := NULL;
782 p_originator_person_id := NULL;
783 p_output_num := NULL;
784 p_oc_comments := NULL;
785 p_cori_comments := NULL;
786 p_message_name := 'IGS_AS_OUTGOING_CORREC_NOTFND';
787 RETURN;
788 END IF;
789 CLOSE c_ocv;
790 p_person_id := v_ocv_rec.person_id;
791 p_correspondence_type := v_ocv_rec.CORRESPONDENCE_TYPE;
792 p_cal_type := v_ocv_rec.CAL_TYPE;
793 p_ci_sequence_number := v_ocv_rec.ci_sequence_number;
794 p_course_cd := v_ocv_rec.course_cd;
795 p_cv_version_number := v_ocv_rec.cv_version_number;
796 p_unit_cd := v_ocv_rec.unit_cd;
797 p_uv_version_number := v_ocv_rec.uv_version_number;
798 p_s_other_reference_type := v_ocv_rec.S_OTHER_REFERENCE_TYPE;
799 p_other_reference := v_ocv_rec.other_reference;
800 p_addr_type := v_ocv_rec.ADDR_TYPE;
801 p_tracking_id := v_ocv_rec.tracking_id;
802 p_request_num := v_ocv_rec.request_num;
803 p_s_job_name := v_ocv_rec.s_job_name;
804 p_request_job_id := v_ocv_rec.request_job_id;
805 p_request_job_run_id := v_ocv_rec.request_job_run_id;
806 p_correspondence_cat := v_ocv_rec.CORRESPONDENCE_CAT;
807 p_reference_number := v_ocv_rec.reference_number;
808 p_issue_dt := v_ocv_rec.issue_dt;
809 p_dt_sent := v_ocv_rec.dt_sent;
810 p_unknown_return_dt := v_ocv_rec.unknown_return_dt;
811 p_adt_description := v_ocv_rec.adt_description;
812 p_create_dt := v_ocv_rec.create_dt;
813 p_originator_person_id := v_ocv_rec.originator_person_id;
814 p_output_num := v_ocv_rec.output_num;
815 p_oc_comments := v_ocv_rec.oc_comments;
816 p_cori_comments := v_ocv_rec.cori_comments;
817 RETURN;
818 EXCEPTION
819 WHEN OTHERS THEN
820 IF c_ocv%ISOPEN THEN
821 CLOSE c_ocv;
822 END IF;
823 RAISE;
824 END;
825 EXCEPTION
826 WHEN OTHERS THEN
827 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
828 Fnd_Message.Set_Token('NAME','IGS_CO_GEN_002.corp_get_ocv_details');
829 IGS_GE_MSG_STACK.ADD;
830 App_Exception.Raise_Exception;
831 END corp_get_ocv_details;
832
833 END IGS_CO_GEN_002;