[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
shogan 20-feb-2004 Bug #3438386/3472644 - update added loop to procedure Submit_Event
- update added table updates with error_code.
- New procedure added update_stdnts_err - write error code
and report message.
- Alt. update_request_status (reduced parameters)
- remove call from workflow process. igsdaxmlgen.wft
nmankodi 03-Nov-2004 Bug 3936708 - Changed the udpate_request_status procedure.
- Changed the update_req_students spec and body, added p_error_code
jhanda 17-Jan-2005 Bug 4114100 - Changed code added cursor c_ftr_val to check for sending a single
large xml containing information for all students
bradhakr 01-mar-2005 bug -4210676 - Modify the procedure call igs_pr_cp_gpa.get_sua_all to include uoo_id
or multiple small xml's 1 for each student.
| nmankodi 11-Apr-2005 fnd_user.customer_id column has been changed to
| fnd_user.person_party_id as an ebizsuite wide TCA mandate.
swaghmar 15-Sep-2005 bug# 4491456
nmankodi 16-Sep-2005 Bug# 4613611
ijeddy 05-Dec-2006 Bug 4755785 - Modified the update_req_students procedure and added the IF conditions to
check the variable if not null then do the string operation
****************************************************************************************************************** */
--
--
--
g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_DA_XML_PKG';
SELECT sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number
FROM igs_en_su_attempt sua
WHERE sua.person_id = p_person_id
AND sua.course_cd = p_course_cd
AND sua.uoo_id = p_uoo_id;
SELECT 'X'
FROM igs_as_grd_sch_grade gsg
WHERE cp_grading_schema_cd = gsg.grading_schema_cd
AND cp_version_number = gsg.version_number
AND cp_grade = gsg.grade
AND gsg.show_on_official_ntfctn_ind = 'Y';
SELECT person_type_code
FROM igs_pe_typ_instances_all
WHERE end_date IS NULL
AND person_id = p_person_id;
SELECT suao.grade, gsg.grade valid_grade, gsg.repeat_grade
FROM igs_as_grd_sch_grade gsg, igs_as_su_stmptout_all suao
WHERE
suao.unit_cd = p_unit_cd
AND suao.finalised_outcome_ind = 'Y'
AND suao.grade = gsg.grade
AND suao.grading_schema_cd = gsg.grading_schema_cd
AND suao.person_id = p_person_id
AND suao.unit_cd = p_unit_cd
AND suao.course_cd = p_course_cd
AND suao.version_number = p_version_number
AND suao.cal_type = p_teach_cal_type
AND suao.ci_sequence_number = p_teach_ci_sequence_number;
SELECT uv.repeatable_ind
FROM hz_parties p, igs_en_stdnt_ps_att spa, igs_ps_ver crv, igs_ca_teach_to_load_v ttl,
igs_en_su_attempt sua, igs_ps_unit_ver uv, igs_ps_unit_ofr_opt uoo, IGS_PS_PRG_UNIT_REL pur,
igs_as_su_atmptout_h_all atm
WHERE p.party_id = sua.person_id
AND sua.person_id = p_person_id
AND sua.unit_cd = p_unit_cd
AND sua.course_cd = p_course_cd
AND sua.version_number = p_version_number
AND sua.cal_type = p_teach_cal_type
AND sua.ci_sequence_number = p_teach_ci_sequence_number
AND sua.unit_attempt_status = p_unit_attempt_status
AND sua.person_id = spa.person_id
AND sua.course_cd = spa.course_cd
AND sua.person_id = atm.person_id
AND sua.course_cd = atm.course_cd
AND atm.unit_cd = sua.unit_cd
AND sua.unit_attempt_status IN ('ENROLLED','COMPLETED','DUPLICATE','DISCONTIN')
AND uv.unit_cd = sua.unit_cd
AND uv.version_number = sua.version_number
AND sua.uoo_id = uoo.uoo_id
AND spa.course_cd = crv.course_cd
AND spa.version_number = crv.version_number
AND sua.cal_type = ttl.teach_cal_type
AND sua.ci_sequence_number = ttl.teach_ci_sequence_number
AND (sua.student_career_transcript = 'Y'
OR (sua.student_career_transcript IS NULL
AND pur.unit_type_id = uv.unit_type_id
AND pur.student_career_level = crv.course_type
AND pur.student_career_transcript = 'Y'));
SELECT hp.party_id
FROM hz_parties hp
WHERE hp.party_number = p_person_id_code;
SELECT hp.party_id, hp.party_number
FROM hz_parties hp,
igs_pe_alt_pers_id api,
igs_da_setup ds
WHERE ds.s_control_num = 1
AND api.api_person_id = p_person_id_code
AND api.person_id_type = ds.default_student_id_type
AND api.pe_person_id = hp.party_id
AND (api.end_dt >= SYSDATE or api.end_dt is null);
PROCEDURE update_stdnts_err (
p_batch_id IN igs_da_req_stdnts.batch_id%TYPE,
p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
p_report_text IN igs_da_req_stdnts.report_text%TYPE,
p_error_code IN igs_da_req_stdnts.error_code%TYPE,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_err_msg_pos1 VARCHAR2(30);
SELECT ROWID,drs.*
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = p_batch_id
AND drs.person_id = l_person_id;
ecx_debug.push('IGS_DA_XML_PKG.UPDATE_STDNTS_ERR');
IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
(v_dummy.ROWID,
v_dummy.batch_id,
v_dummy.igs_da_req_stdnts_id,
v_dummy.person_id,
v_dummy.program_code,
v_dummy.wif_program_code,
v_dummy.special_program_code,
v_dummy.major_unit_set_cd,
v_dummy.program_major_code,
p_report_text,
v_dummy.wif_id,
'R',
p_error_code);
update_request_status(p_batch_id);
ecx_debug.pl (0, 'IGS', 'IGS_ERROR_MSG'||'Error Code =' || p_error_code ,'E: Unable to Update');
x_return_status := 'E: Unable to Update';
ecx_debug.pop('IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
x_return_status := 'E: UPDATE FAILED';
ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
PROCEDURE update_req_students (
p_batch_id IN igs_da_req_stdnts.batch_id%TYPE,
p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
p_report_text IN igs_da_req_stdnts.report_text%TYPE,
p_academicsubprogram_codes IN VARCHAR2,
p_program_code IN igs_da_req_stdnts.program_code%TYPE,
p_error_code IN igs_da_req_stdnts.error_code%TYPE,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REQ_STUDENTS';
SELECT dr.program_comparison_type
FROM igs_da_rqst dr
WHERE dr.batch_id = RTRIM(LTRIM(p_batch_id)) ;
SELECT ds.program_definition_ind
FROM igs_da_setup ds
WHERE ds.s_control_num = 1;
SELECT ROWID,drs.*
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
AND drs.person_id = l_person_id
AND drs.program_major_code = RTRIM(LTRIM(p_program_code));
SELECT ROWID,drs.*
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
AND drs.person_id = l_person_id
AND drs.program_major_code = RTRIM(LTRIM(p_program_code))
AND EXISTS (SELECT 'X'
FROM igs_da_req_wif drw
WHERE drw.batch_id = drs.batch_id
AND drw.wif_id = drs.wif_id
AND drw.program_code = drs.wif_program_code
AND drw.major_unit_set_cd1 = drs.major_unit_set_cd
AND (drw.minor_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode1))
AND EXISTS (SELECT 'X'
FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
WHERE eus.unit_set_status=euss.unit_set_status
AND euss.s_unit_set_status = 'ACTIVE'
AND eus.unit_set_cd = drw.minor_unit_set_cd1
AND eus.unit_set_cat = ds.wif_minor_unit_set_cat
AND ds.s_control_num =1)
OR drw.minor_unit_set_cd1 IS NULL)
AND (drw.track_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode2))
AND EXISTS (SELECT 'X'
FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
WHERE eus.unit_set_status=euss.unit_set_status
AND euss.s_unit_set_status = 'ACTIVE'
AND eus.unit_set_cd = drw.track_unit_set_cd1
AND eus.unit_set_cat = ds.wif_track_unit_set_cat
AND ds.s_control_num =1)
OR drw.track_unit_set_cd1 IS NULL));
SELECT ROWID,drs.*
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
AND drs.person_id = l_person_id
AND drs.program_code = RTRIM(LTRIM(p_program_code));
SELECT ROWID,drs.*
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
AND drs.person_id = l_person_id
AND drs.special_program_code = RTRIM(LTRIM(p_program_code));
SELECT ROWID,drs.*
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
AND drs.person_id = l_person_id
AND drs.wif_program_code = RTRIM(LTRIM(p_program_code))
AND exists (SELECT 'X'
FROM igs_da_req_wif drw
WHERE drw.batch_id = drs.batch_id
AND drw.wif_id = drs.wif_id
AND drw.program_code = drs.wif_program_code
AND (drw.major_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode1))
AND EXISTS (SELECT 'X'
FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
WHERE eus.unit_set_status=euss.unit_set_status
AND euss.s_unit_set_status = 'ACTIVE'
AND eus.unit_set_cd = drw.major_unit_set_cd1
AND eus.unit_set_cat = ds.wif_major_unit_set_cat
AND ds.s_control_num =1)
OR drw.major_unit_set_cd1 IS NULL)
AND (drw.minor_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode2))
AND EXISTS (SELECT 'X'
FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
WHERE eus.unit_set_status=euss.unit_set_status
AND euss.s_unit_set_status = 'ACTIVE'
AND eus.unit_set_cd = drw.minor_unit_set_cd1
AND eus.unit_set_cat = ds.wif_minor_unit_set_cat
AND ds.s_control_num =1)
OR drw.minor_unit_set_cd1 IS NULL)
AND (drw.track_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode3))
AND EXISTS (SELECT 'X'
FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
WHERE eus.unit_set_status=euss.unit_set_status
AND euss.s_unit_set_status = 'ACTIVE'
AND eus.unit_set_cd = drw.track_unit_set_cd1
AND eus.unit_set_cat = ds.wif_track_unit_set_cat
AND ds.s_control_num =1)
OR drw.track_unit_set_cd1 IS NULL));
ecx_debug.push('IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
(v_dummy.ROWID,
v_dummy.batch_id,
v_dummy.igs_da_req_stdnts_id,
v_dummy.person_id,
v_dummy.program_code,
v_dummy.wif_program_code,
v_dummy.special_program_code,
v_dummy.major_unit_set_cd,
v_dummy.program_major_code,
p_report_text,
v_dummy.wif_id,
'R',
l_error_code);
IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
(v_dummy.ROWID,
v_dummy.batch_id,
v_dummy.igs_da_req_stdnts_id,
v_dummy.person_id,
v_dummy.program_code,
v_dummy.wif_program_code,
v_dummy.special_program_code,
v_dummy.major_unit_set_cd,
v_dummy.program_major_code,
p_report_text,
v_dummy.wif_id,
'R',
l_error_code);
IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
(v_dummy.ROWID,
v_dummy.batch_id,
v_dummy.igs_da_req_stdnts_id,
v_dummy.person_id,
v_dummy.program_code,
v_dummy.wif_program_code,
v_dummy.special_program_code,
v_dummy.major_unit_set_cd,
v_dummy.program_major_code,
p_report_text,
v_dummy.wif_id,
'R',
l_error_code);
IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
(v_dummy.ROWID,
v_dummy.batch_id,
v_dummy.igs_da_req_stdnts_id,
v_dummy.person_id,
v_dummy.program_code,
v_dummy.wif_program_code,
v_dummy.special_program_code,
v_dummy.major_unit_set_cd,
v_dummy.program_major_code,
p_report_text,
v_dummy.wif_id,
'R',
l_error_code);
IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
(v_dummy.ROWID,
v_dummy.batch_id,
v_dummy.igs_da_req_stdnts_id,
v_dummy.person_id,
v_dummy.program_code,
v_dummy.wif_program_code,
v_dummy.special_program_code,
v_dummy.major_unit_set_cd,
v_dummy.program_major_code,
p_report_text,
v_dummy.wif_id,
'R',
l_error_code);
update_request_status(p_batch_id);
ecx_debug.pl (0, 'IGS', 'IGS_REPORT_UPDATE_FAILED','E: Unable to Update');
x_return_status := 'E: Unable to Update';
ecx_debug.pop('IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
x_return_status := 'E: UPDATE FAILED';
ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
PROCEDURE insert_gpa
(
p_batch_id IN igs_pr_stu_acad_stat_int.batch_id%TYPE,
p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
p_program_code IN igs_da_req_stdnts.program_code%TYPE,
p_alternate_code IN igs_pr_stu_acad_stat_int.alternate_code%TYPE,
p_stat_type IN igs_pr_stu_acad_stat_int.stat_type%TYPE,
p_timeframe IN igs_pr_stu_acad_stat_int.timeframe%TYPE,
p_attempted_credit_points IN igs_pr_stu_acad_stat_int.attempted_credit_points%TYPE,
p_earned_credit_points IN igs_pr_stu_acad_stat_int.earned_credit_points%TYPE,
p_gpa IN igs_pr_stu_acad_stat_int.gpa%TYPE,
p_gpa_credit_points IN igs_pr_stu_acad_stat_int.gpa_credit_points%TYPE,
p_gpa_quality_points IN igs_pr_stu_acad_stat_int.gpa_quality_points%TYPE,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GPA';
SELECT drs.program_code
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
AND drs.person_id = l_person_id
AND ((drs.program_major_code = RTRIM(LTRIM(p_program_code))) OR (drs.program_code = RTRIM(LTRIM(p_program_code))));
ecx_debug.push('IGS_DA_XML_PKG.INSERT_GPA');
INSERT INTO igs_pr_stu_acad_stat_int
(BATCH_ID,
COURSE_CD,
PERSON_NUMBER,
ALTERNATE_CODE,
STAT_TYPE,
TIMEFRAME,
SOURCE_TYPE,
SOURCE_REFERENCE,
ATTEMPTED_CREDIT_POINTS,
EARNED_CREDIT_POINTS,
GPA,
GPA_CREDIT_POINTS,
GPA_QUALITY_POINTS,
ERROR_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
RTRIM(LTRIM(p_batch_id)),
l_program_code,
l_person_number,
RTRIM(LTRIM(p_alternate_code)),
RTRIM(LTRIM(p_stat_type)),
RTRIM(LTRIM(p_timeframe)),
'DEGREE AUDIT',
'DEGREE AUDIT',
RTRIM(LTRIM(p_attempted_credit_points)),
RTRIM(LTRIM(p_earned_credit_points)),
RTRIM(LTRIM(p_gpa)),
RTRIM(LTRIM(p_gpa_credit_points)),
RTRIM(LTRIM(p_gpa_quality_points)),
NULL,
1,
SYSDATE,
1,
SYSDATE,
NULL);
ecx_debug.pop('IGS_DA_XML_PKG.INSERT_GPA');
ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.INSERT_GPA');
ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.INSERT_GPA');
PROCEDURE insert_program_completion
(
p_batch_id IN igs_pr_spa_complete_int.batch_id%TYPE,
p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
p_program_code IN igs_da_req_stdnts.program_code%TYPE,
p_program_complete IN VARCHAR2,
p_program_complete_date IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_PROGRAM_COMPLETION';
SELECT drs.program_code
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
AND drs.person_id = l_person_id
AND ((drs.program_major_code = RTRIM(LTRIM(p_program_code))) OR (drs.program_code = RTRIM(LTRIM(p_program_code))));
ecx_debug.push('IGS_DA_XML_PKG.INSERT_DEGREE_COMPLETION');
INSERT INTO igs_pr_spa_complete_int
(BATCH_ID,
PERSON_NUMBER,
COURSE_CD,
COMPLETE_DT,
ERROR_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
( RTRIM(LTRIM(p_batch_id)),
l_person_number,
l_program_code,
-- Modified by nmankodi, Bug # 4613611
NVL(to_date(substr(RTRIM(LTRIM(p_program_complete_date)),1,10),'YYYY-MM-DD'),SYSDATE),
NULL,
1,
SYSDATE,
1,
SYSDATE,
NULL);
ecx_debug.pop('IGS_DA_XML_PKG.INSERT_DEGREE_COMPLETION');
ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.INSERT_PROGRAM_COMPLETION');
ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.INSERT_PROGRAM_COMPLETION');
SELECT drs.person_id
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = p_batch_id;
SELECT party_id,
party_site_id
FROM ecx_tp_headers
WHERE tp_header_id IN
( SELECT tp_header_id
FROM ecx_tp_details
WHERE ext_process_id IN
( SELECT ext_process_id
FROM ecx_ext_processes
WHERE direction = 'OUT'
AND transaction_id IN
(SELECT et.transaction_id
FROM ecx_transactions et, ecx_ext_processes ep
WHERE transaction_type='DA'
and ep.transaction_id = et.transaction_id
and ep.ext_type =
(select dcry.request_type
from igs_da_cnfg_req_typ dcry, igs_da_rqst dr
where dr.batch_id = p_batch_id
and dcry.request_type_id = dr.request_type_id )
and ep.direction = 'OUT')
)
);
SELECT et.transaction_subtype
FROM ecx_transactions et, ecx_ext_processes ep
WHERE transaction_type='DA'
and ep.transaction_id = et.transaction_id
and ep.direction = 'OUT'
and ep.ext_type = (select dcry.request_type
from igs_da_cnfg_req_typ dcry, igs_da_rqst dr
where dr.batch_id = p_batch_id
and dcry.request_type_id = dr.request_type_id );
SELECT count(drs.wif_program_code)
FROM igs_da_req_stdnts drs
WHERE drs.batch_id = p_batch_id
AND wif_program_code is not null;
SELECT et.transaction_subtype
FROM ecx_transactions et, ecx_ext_processes ep
WHERE transaction_type='DA'
and ep.transaction_id = et.transaction_id
and ep.direction = 'OUT'
and ep.ext_type = 'WF';
SELECT feature_value
FROM igs_da_req_ftrs
WHERE batch_id = p_batch_id AND feature_code = 'SNG' ;
l_parameter_list.DELETE;
l_parameter_list.DELETE;
SELECT 'X'
FROM igs_da_rqst dr
WHERE dr.batch_id = p_batch_id
FOR UPDATE OF dr.request_status NOWAIT;
SELECT dr.requestor_id,fdu.user_name
FROM igs_da_rqst dr, fnd_user fdu
WHERE dr.batch_id = p_batch_id
AND dr.requestor_id = fdu.person_party_id;
SELECT fdu.person_party_id,fdu.user_name
FROM fnd_user fdu
WHERE fdu.person_party_id = l_da_wf_admin_id;
UPDATE igs_da_rqst
SET request_status = 'ERROR'
WHERE CURRENT OF c_request_status;
l_parameter_list.DELETE;
PROCEDURE update_request_status
(p_batch_id IN igs_da_req_stdnts.batch_id%TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REQUEST_STATUS' ;
SELECT ROWID,dr.*
FROM igs_da_rqst dr
WHERE dr.batch_id = p_batch_id;
SAVEPOINT UPDATE_REQUEST_STATUS;
select count(*)
into l_request_err_count
from igs_da_req_stdnts
where batch_id = p_batch_id
and error_code ='PRE- SUBMISSION FAILURE';
select count(*)
into l_reply_err_count
from igs_da_req_stdnts
where batch_id = p_batch_id
and error_code ='REPLY_ERROR';
select count(*)
into l_noClob_count
from igs_da_req_stdnts
where batch_id = p_batch_id
and report_text is null;
select count(*)
into l_clob_count
from igs_da_req_stdnts
where batch_id = p_batch_id
and report_text is not null;
UPDATE igs_da_rqst
SET REQUEST_STATUS = l_request_status
WHERE batch_id = p_batch_id;
ROLLBACK TO UPDATE_REQUEST_STATUS;
SELECT user_name
FROM fnd_user
WHERE person_party_id IN (SELECT requestor_id
FROM igs_da_rqst
WHERE batch_id = cp_batch_id);
SELECT person_id, igs_da_req_stdnts_id
FROM igs_da_req_stdnts
WHERE batch_id = cp_batch_id
AND igs_da_req_stdnts_id=cp_igs_da_req_stdnts_id;
SELECT count(*)
FROM igs_da_req_stdnts
WHERE batch_id = cp_batch_id;
SELECT person_code_qualifier,
person_id_code,
name_type,
name_first,
name_last
FROM igs_da_xml_person_v
WHERE batch_id = cp_batch_id
AND person_id = cp_person_id;
SELECT institution_cd
FROM igs_da_xml_degreeprogram_v
WHERE batch_id = cp_batch_id
AND person_id = cp_person_id;
SELECT program_type,
program_code,
program_catalog_year
FROM igs_da_xml_academicprogram_v
WHERE batch_id = cp_batch_id
AND person_id = cp_person_id;
SELECT rowid, a.*
FROM igs_da_req_stdnts a
WHERE batch_id = cp_batch_id
AND person_id = cp_person_id
AND igs_da_req_stdnts_id = cp_igs_da_req_stdnts_id;
SELECT igs_da_req_stdnts_id
FROM igs_da_req_stdnts
WHERE batch_id = cp_batch_id;
SELECT party_name||' ('||party_number||') '
FROM hz_parties
WHERE party_id = cp_person_id;
-- now update the req_stdnts table
IF v_report_text IS NOT NULL THEN
v_report_text := ' Error Report
'||v_report_text||' '|| ' ';
UPDATE igs_da_req_stdnts
SET report_text = v_report_text,
-- bug fix 3438386 - update error code for internal request failues.
error_code = l_error_code
WHERE batch_id = p_batch_id
AND person_id = rec_list_person_id.person_id
AND igs_da_req_stdnts_id = rec_req_stdnts_id.igs_da_req_stdnts_id;
igs_da_req_stdnts_pkg.update_row (
X_ROWID => rec_req_stdnts.ROWID,
X_BATCH_ID => rec_req_stdnts.batch_id,
X_IGS_DA_REQ_STDNTS_ID => rec_req_stdnts.igs_da_req_stdnts_id,
X_PERSON_ID => rec_req_stdnts.person_id,
X_PROGRAM_CODE => rec_req_stdnts.program_code,
X_WIF_PROGRAM_CODE => rec_req_stdnts.wif_program_code,
X_SPECIAL_PROGRAM_CODE => rec_req_stdnts.special_program_code,
X_MAJOR_UNIT_SET_CD => rec_req_stdnts.major_unit_set_cd,
X_PROGRAM_MAJOR_CODE => rec_req_stdnts.program_major_code,
X_REPORT_TEXT => v_report_text,
X_WIF_ID => rec_req_stdnts.wif_id,
X_MODE => 'R'
);