[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_letter_delete IN VARCHAR2 DEFAULT 'N',
p_message_name OUT NOCOPY varchar2 )
RETURN BOOLEAN AS
e_resource_busy EXCEPTION;
SELECT cort.sys_generated_ind
FROM IGS_CO_ITM cori,
IGS_CO_TYPE cort
WHERE cori.CORRESPONDENCE_TYPE = cp_correspondence_type
AND cori.reference_number = cp_reference_number
AND cort.CORRESPONDENCE_TYPE = cori.CORRESPONDENCE_TYPE;
SELECT 'x'
FROM IGS_CO_S_LTR sl
WHERE sl.CORRESPONDENCE_TYPE = cp_correspondence_type;
SELECT ROWID
FROM IGS_CO_DTL_OLE cdo
WHERE cdo.CORRESPONDENCE_TYPE = cp_correspondence_type
AND cdo.reference_number = cp_reference_number
FOR UPDATE OF cdo.CORRESPONDENCE_TYPE NOWAIT;
SELECT ROWID
FROM IGS_CO_DTL cd
WHERE cd.CORRESPONDENCE_TYPE = cp_correspondence_type
AND cd.reference_number = cp_reference_number
FOR UPDATE OF cd.CORRESPONDENCE_TYPE NOWAIT;
SELECT ocr.rowid,
ocr.other_reference,
ocr.person_id
FROM IGS_CO_OU_CO_REF ocr
WHERE ocr.CORRESPONDENCE_TYPE = cp_correspondence_type
AND ocr.reference_number = cp_reference_number
AND ocr.S_OTHER_REFERENCE_TYPE = cst_spl_seqnum
FOR UPDATE OF ocr.other_reference NOWAIT;
SELECT ROWID
FROM IGS_CO_S_PER_LTR spl
WHERE spl.sequence_number = cp_sequence_number
AND spl.person_id = cp_person_id
FOR UPDATE OF spl.sequence_number NOWAIT;
SELECT ROWID
FROM IGS_CO_S_PER_LT_PARM splp
WHERE splp.spl_sequence_number = cp_sequence_number
AND splp.person_id = cp_person_id
FOR UPDATE OF splp.spl_sequence_number NOWAIT;
SELECT ROWID
FROM IGS_CO_S_PERLT_RPTGP splrg
WHERE splrg.spl_sequence_number = cp_sequence_number
AND splrg.person_id = cp_person_id
ORDER BY splrg.sequence_number DESC, splrg.sup_repeating_group_cd
FOR UPDATE OF splrg.spl_sequence_number NOWAIT;
SELECT ROWID,
PERSON_ID,
ADMISSION_APPL_NUMBER,
CORRESPONDENCE_TYPE,
SEQUENCE_NUMBER,
COMPOSED_IND,
LETTER_REFERENCE_NUMBER,
SPL_SEQUENCE_NUMBER
FROM IGS_AD_APPL_LTR aal
WHERE aal.spl_sequence_number = cp_sequence_number
AND aal.person_id = cp_person_id
FOR UPDATE OF aal.letter_reference_number, aal.spl_sequence_number NOWAIT;
SELECT ROWID
FROM IGS_CO_OU_CO_REF ocr
WHERE ocr.CORRESPONDENCE_TYPE = cp_correspondence_type
AND ocr.reference_number = cp_reference_number
FOR UPDATE OF ocr.CORRESPONDENCE_TYPE NOWAIT;
SELECT ROWID
FROM IGS_CO_OU_CO oc
WHERE oc.CORRESPONDENCE_TYPE = cp_correspondence_type
AND oc.reference_number = cp_reference_number
FOR UPDATE OF oc.CORRESPONDENCE_TYPE NOWAIT;
SELECT ROWID
FROM IGS_CO_ITM cit
WHERE cit.CORRESPONDENCE_TYPE = cp_correspondence_type
AND cit.reference_number = cp_reference_number
FOR UPDATE OF cit.CORRESPONDENCE_TYPE NOWAIT;
SAVEPOINT sp_before_delete;
ROLLBACK TO sp_before_delete;
IF(p_letter_delete = 'Y') THEN
IF(v_sys_generated_ind = 'N') THEN
ROLLBACK TO sp_before_delete;
ROLLBACK TO sp_before_delete;
IGS_CO_DTL_OLE_PKG.DELETE_ROW(X_ROWID=>v_cdo_rec.ROWID);
IGS_CO_DTL_PKG.DELETE_ROW(X_ROWID=>vcd_rec.ROWID);
IF(p_letter_delete = 'Y') THEN
-- find related IGS_CO_S_PER_LTR records from IGS_CO_OU_CO_REF
FOR v_ocr_rec IN c_ocr(
p_correspondence_type,
p_reference_number) LOOP
v_spl_sequence_number := TO_NUMBER(v_ocr_rec.other_reference);
IGS_CO_S_PER_LT_PARM_PKG.DELETE_ROW(X_ROWID=>v_splp_rec.ROWID);
IGS_CO_S_PERLT_RPTGP_PKG.DELETE_ROW(X_ROWID=>v_splrg_rec.ROWID);
IGS_AD_APPL_LTR_PKG.UPDATE_ROW(
X_ROWID => v_aal_rec.ROWID,
X_PERSON_ID => v_aal_rec.PERSON_ID,
X_ADMISSION_APPL_NUMBER =>v_aal_rec.ADMISSION_APPL_NUMBER ,
X_CORRESPONDENCE_TYPE =>v_aal_rec.CORRESPONDENCE_TYPE,
X_SEQUENCE_NUMBER =>v_aal_rec.SEQUENCE_NUMBER,
X_COMPOSED_IND =>v_aal_rec.COMPOSED_IND,
X_LETTER_REFERENCE_NUMBER =>NULL,
X_SPL_SEQUENCE_NUMBER =>NULL,
X_MODE => 'R'
);
IGS_CO_S_PER_LTR_PKG.DELETE_ROW(X_ROWID => v_spl_rec.rowid);
IGS_CO_OU_CO_REF_PKG.DELETE_ROW(X_ROWID => v_ocr_rec.ROWID );
ELSE -- p_letter_delete = 'N'
-- delete all the out NOCOPY correspondence ref records for this correspondence item
FOR v_ocr_1_rec IN c_ocr_1(
p_correspondence_type,
p_reference_number) LOOP
IGS_CO_OU_CO_REF_PKG.DELETE_ROW(X_ROWID=>v_ocr_1_rec.ROWID);
IGS_CO_OU_CO_PKG.DELETE_ROW(X_ROWID=>v_oc_rec.ROWID);
IGS_CO_ITM_PKG.DELETE_ROW(X_ROWID=>v_cit_rec.ROWID);
ROLLBACK TO sp_before_delete;
lv_param_values := p_correspondence_type||','||TO_CHAR(p_reference_number)||','||p_letter_delete;
SELECT IGS_CO_S_PER_LT_PARM_SEQ_NUM_S.NEXTVAL
FROM DUAL;
SELECT lpt.S_LETTER_PARAMETER_TYPE,
lpt.letter_text,
slpt.code_block,
slpt.S_LETTER_PARAMETER_TYPE
FROM IGS_CO_LTR_PARM_TYPE lpt,
IGS_CO_S_LTR_PARAM slpt
WHERE lpt.LETTER_PARAMETER_TYPE = p_letter_parameter_type AND
slpt.S_LETTER_PARAMETER_TYPE = lpt.S_LETTER_PARAMETER_TYPE;
SELECT slpta.bind_variable
FROM IGS_CO_S_LTR_PR_ARG slpta
WHERE slpta.S_LETTER_PARAMETER_TYPE = cp_s_letter_parameter_type AND
slpta.direction = cst_in;
SELECT slpta.bind_variable
FROM IGS_CO_S_LTR_PR_ARG slpta
WHERE slpta.S_LETTER_PARAMETER_TYPE = cp_s_letter_parameter_type AND
slpta.direction = cst_out;
IGS_CO_S_PER_LT_PARM_PKG.INSERT_ROW(X_ROWID=>X_ROWID,
X_PERSON_ID=>p_person_id,
X_CORRESPONDENCE_TYPE=>p_correspondence_type,
X_LETTER_REFERENCE_NUMBER=>p_letter_reference_number,
X_SPL_SEQUENCE_NUMBER=>p_spl_sequence_number,
X_LETTER_PARAMETER_TYPE=>p_letter_parameter_type,
X_SEQUENCE_NUMBER=>v_sequence_number,
X_PARAMETER_VALUE=>v_value,
X_LETTER_REPEATING_GROUP_CD=>p_letter_repeating_group_cd,
X_SPLRG_SEQUENCE_NUMBER=>p_splrg_sequence_number,
X_MODE=>'R',
x_letter_order_number => p_letter_order_number,
X_ORG_ID => FND_PROFILE.value('ORG_ID'));
cursor get_lon IS SELECT letter_order_number
from igs_co_ltr_param
where correspondence_type = p_correspondence_type and letter_parameter_type = p_letter_parameter_type and
letter_reference_number = p_letter_reference_number;
SELECT oc.ROWID,
oc.PERSON_ID ,
oc.CORRESPONDENCE_TYPE,
oc.REFERENCE_NUMBER,
oc.ISSUE_DT,
oc.DT_SENT ,
oc.UNKNOWN_RETURN_DT,
oc.ADDR_TYPE,
oc.TRACKING_ID,
oc.COMMENTS,
oc.LETTER_REFERENCE_NUMBER ,
oc.SPL_SEQUENCE_NUMBER
FROM IGS_CO_OU_CO oc
WHERE oc.reference_number = cp_reference_number AND
((oc.person_id = cp_person_id AND
(cp_person_id IS NOT NULL OR cp_person_id <> 0)) OR
(cp_person_id IS NULL OR cp_person_id = 0))AND
TRUNC(oc.issue_dt) = cp_issue_dt AND
oc.dt_sent IS NULL
FOR UPDATE OF oc.dt_sent NOWAIT;
IGS_CO_OU_CO_PKG.update_row(
X_ROWID => v_outgoing_correspondence_rec.ROWID,
X_PERSON_ID =>v_outgoing_correspondence_rec.PERSON_ID ,
X_CORRESPONDENCE_TYPE =>v_outgoing_correspondence_rec.CORRESPONDENCE_TYPE,
X_REFERENCE_NUMBER => v_outgoing_correspondence_rec.REFERENCE_NUMBER,
X_ISSUE_DT => v_outgoing_correspondence_rec.ISSUE_DT,
X_DT_SENT =>P_DT_SENT,
X_UNKNOWN_RETURN_DT => v_outgoing_correspondence_rec.UNKNOWN_RETURN_DT,
X_ADDR_TYPE => v_outgoing_correspondence_rec.ADDR_TYPE ,
X_TRACKING_ID => v_outgoing_correspondence_rec.TRACKING_ID ,
X_COMMENTS => v_outgoing_correspondence_rec.COMMENTS ,
X_LETTER_REFERENCE_NUMBER =>v_outgoing_correspondence_rec.letter_REFERENCE_NUMBER,
X_SPL_SEQUENCE_NUMBER =>v_outgoing_correspondence_rec.spl_sequence_NUMBER,
X_MODE=> 'R'
);
FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('FND', 'FORM_RECORD_DELETED')
|| ' ' || TO_CHAR(p_reference_number)
|| ' ' || TO_CHAR(p_person_id)
|| ' ' || TO_CHAR(p_issue_dt)
|| ' ' || TO_CHAR(p_dt_sent));
SELECT person_id,
CORRESPONDENCE_TYPE,
CAL_TYPE,
ci_sequence_number,
course_cd,
cv_version_number,
unit_cd,
uv_version_number,
S_OTHER_REFERENCE_TYPE,
other_reference,
ADDR_TYPE,
tracking_id,
request_num,
s_job_name,
request_job_id,
request_job_run_id,
CORRESPONDENCE_CAT,
reference_number,
issue_dt,
dt_sent,
unknown_return_dt,
adt_description,
create_dt,
originator_person_id,
output_num,
oc_comments,
cori_comments
FROM IGS_CO_OU_CO_V
WHERE (p_person_id IS NULL OR
person_id = p_person_id) AND
(p_correspondence_type IS NULL OR
CORRESPONDENCE_TYPE = p_correspondence_type) AND
(p_cal_type IS NULL OR
CAL_TYPE = p_cal_type) AND
(p_ci_sequence_number IS NULL OR
ci_sequence_number = p_ci_sequence_number) AND
(p_course_cd IS NULL OR
course_cd = p_course_cd) AND
(p_cv_version_number IS NULL OR
cv_version_number = p_cv_version_number) AND
(p_unit_cd IS NULL OR
unit_cd = p_unit_cd) AND
(p_uv_version_number IS NULL OR
uv_version_number = p_uv_version_number) AND
(p_s_other_reference_type IS NULL OR
S_OTHER_REFERENCE_TYPE = p_s_other_reference_type) AND
(p_other_reference IS NULL OR
other_reference = p_other_reference) AND
(p_addr_type IS NULL OR
ADDR_TYPE = p_addr_type) AND
(p_tracking_id IS NULL OR
tracking_id = p_tracking_id) AND
(p_request_num IS NULL OR
request_num = p_request_num) AND
(p_s_job_name IS NULL OR
s_job_name = p_s_job_name) AND
(p_request_job_id IS NULL OR
request_job_id = p_request_job_id) AND
(p_request_job_run_id IS NULL OR
request_job_run_id = p_request_job_run_id)
ORDER BY issue_dt DESC,
reference_number DESC;