[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| rbezawad 27-Feb-05 Added code to procedure update_parent_record_status() to execute a Dynamic Code block
when IGR functionality is enabled
---------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE logerrormessage(p_record IN VARCHAR2,
p_error IN VARCHAR2,
p_entity_name IN VARCHAR2 DEFAULT NULL,
p_match_ind IN VARCHAR2 DEFAULT NULL) AS
/*****************************************************************
Created By : asbala
Creation date : 9/23/2003
Purpose : This function is to print the statistics from igs_ad_imp_stats.
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
l_context_token_str VARCHAR2(50);
SELECT total_rec_num, total_warn_num, total_success_num, total_error_num, meaning,entity_name
FROM igs_ad_imp_stats imp, igs_lookup_values lk
WHERE imp.src_cat_code = lk.lookup_code
AND lk.lookup_type = cp_lookup_type
AND imp.interface_run_id = cp_interface_run_id
ORDER BY meaning, entity_name;
PROCEDURE update_parent_record_status (p_source_type_id IN NUMBER,
p_batch_id IN NUMBER,
p_interface_run_id IN NUMBER
) AS
/*************************************************************
Created By : knag
Date Created By : 05-NOV-2003
Purpose : This procedure will call all the procedures for admission and inquiry related categories
Know limitations, enhancements or remarks
Change History
Who When What
rbezawad 27-Feb-05 Added code to procedure update_parent_record_status() to execute a Dynamic Code block
when IGR functionality is enabled
(reverse chronological order - newest change first)
***************************************************************/
l_prog_label VARCHAR2(4000);
l_prog_label := 'igs.plsql.igs_ad_imp_001.update_parent_record_status';
l_label := 'igs.plsql.igs_ad_imp_001.update_parent_record_status.';
l_label := 'igs.plsql.igs_ad_imp_001.update_parent_record_status.begin';
UPDATE igs_ad_ps_appl_inst_int apinst
SET status = '4',
error_code = 'E347',
error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE status = '1'
AND interface_run_id = p_interface_run_id
AND (
EXISTS (SELECT 1 FROM igs_ad_insthist_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
OR EXISTS (SELECT 1 FROM igs_ad_notes_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
OR EXISTS (SELECT 1 FROM igs_ad_unitsets_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
OR EXISTS (SELECT 1 FROM igs_ad_edugoal_int WHERE status <> '1' AND interface_ps_appl_inst_id = apinst.interface_ps_appl_inst_id)
);
UPDATE igs_ad_apl_int api
SET status = '4',
error_code = 'E347',
error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE status = '1'
AND interface_run_id = p_interface_run_id
AND (
EXISTS (SELECT 1 FROM igs_ad_ps_appl_inst_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_othinst_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_acadint_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_appint_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_splint_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_spltal_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_perstmt_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_fee_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
OR EXISTS (SELECT 1 FROM igs_ad_apphist_int WHERE status <> '1' AND interface_appl_id = api.interface_appl_id)
);
UPDATE igs_ad_trmdt_int trmdt
SET status = '4',
error_code = 'E347',
error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE status = '1'
AND interface_run_id = p_interface_run_id
AND EXISTS (SELECT 1 FROM igs_ad_tundt_int WHERE status <> '1' AND interface_term_dtls_id = trmdt.interface_term_dtls_id);
UPDATE igs_ad_txcpt_int txcpt
SET status = '4',
error_code = 'E347',
error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE status = '1'
AND interface_run_id = p_interface_run_id
AND EXISTS (SELECT 1 FROM igs_ad_trmdt_int WHERE status <> '1' AND interface_transcript_id = txcpt.interface_transcript_id);
UPDATE igs_ad_acadhis_int_all acadhis
SET status = '4',
error_code = 'E347',
error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE status = '1'
AND interface_run_id = p_interface_run_id
AND EXISTS (SELECT 1 FROM igs_ad_txcpt_int WHERE status <> '1' AND interface_acadhis_id = acadhis.interface_acadhis_id);
UPDATE igs_ad_test_int tst
SET status = '4',
error_code = 'E347',
error_text = igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
WHERE status = '1'
AND interface_run_id = p_interface_run_id
AND EXISTS (SELECT 1 FROM igs_ad_test_segs_int WHERE status <> '1' AND interface_test_id = tst.interface_test_id);
igr_imp_002.update_parent_record_status(:1);
fnd_file.put_line(fnd_file.log,'Error occurred while calling IGR_IMP_002.UPDATE_PARENT_RECORD_STATUS() : '||SQLERRM);
UPDATE igs_ad_interface ad
SET record_status = '3',
status = '4',
error_code = 'E347'
WHERE status = '1'
AND interface_run_id = p_interface_run_id
AND (
EXISTS (SELECT 1 FROM igs_ad_apl_int WHERE status <> '1' AND interface_id = ad.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_test_int WHERE status <> '1' AND interface_id = ad.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_recruit_int WHERE status <> '1' AND interface_id = ad.interface_id)
OR EXISTS (SELECT 1 FROM igs_uc_qual_ints WHERE status <> '1' AND interface_id = ad.interface_id)
OR EXISTS (SELECT 1 FROM igs_ad_acadhis_int_all WHERE status <> '1' AND interface_id = ad.interface_id)
OR EXISTS (SELECT 1 FROM igs_pe_cred_int WHERE status <> '1' AND interface_id = ad.interface_id)
);
UPDATE igs_ad_interface_ctl
SET status = '3'
WHERE interface_run_id = p_interface_run_id
AND EXISTS (SELECT 1
FROM igs_ad_interface
WHERE interface_run_id = p_interface_run_id
AND (record_status <> '1' OR status <> '1'));
UPDATE igs_ad_interface_ctl
SET status = '1'
WHERE interface_run_id = p_interface_run_id;
END update_parent_record_status;
OPEN c_ref_cur FOR 'SELECT status, count(*) reccount FROM ' ||
p_category_entity_table(idx).entity_name ||
' WHERE interface_run_id = :1 GROUP BY status'
USING p_interface_run_id;
INSERT INTO IGS_AD_IMP_STATS (
INTERFACE_RUN_ID,
SRC_CAT_CODE,
ENTITY_NAME,
TOTAL_REC_NUM,
TOTAL_WARN_NUM,
TOTAL_SUCCESS_NUM,
TOTAL_ERROR_NUM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
) VALUES (
p_interface_run_id,
p_category_entity_table(idx).category_name,
p_category_entity_table(idx).entity_name,
l_total_rec,
l_warning,
l_success,
l_error,
1,
sysdate,
1,
sysdate,
NULL,
NULL,
NULL,
NULL,
NULL
);
SELECT 1 INTO l_count1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM igs_ad_interface int,
igs_uc_qual_ints qint
WHERE int.interface_id = qint.interface_id
AND int.source_type_id = p_source_type_id
AND int.batch_id = p_batch_id
AND int.status IN('1','4','2')
AND qint.status ='2');
SELECT 1 INTO l_count2
FROM DUAL
WHERE EXISTS (SELECT 1
FROM igs_ad_interface int,
igs_ad_apl_int aplint,
igs_ad_apphist_int applhist
WHERE int.interface_id = aplint.interface_id
AND aplint.interface_appl_id = applhist.interface_appl_id
AND int.source_type_id = p_source_type_id
AND int.batch_id = p_batch_id
AND int.status IN ( '1', '4', '2')
AND aplint.status IN ('1', '4', '2')
AND applhist.status = '2'
UNION ALL
SELECT 1
FROM igs_ad_interface int,
igs_ad_apl_int aplint,
igs_ad_ps_appl_inst_int aplinst,
igs_ad_insthist_int applinsthist
WHERE int.interface_id = aplint.interface_id
AND aplint.interface_appl_id = aplinst.interface_appl_id
AND applinsthist.interface_ps_appl_inst_id = aplinst.interface_ps_appl_inst_id
AND int.source_type_id = p_source_type_id
AND int.batch_id = p_batch_id
AND int.status IN ( '1', '4', '2')
AND aplint.status IN ('1', '4', '2')
AND aplinst.status IN ('1', '4', '2')
AND applinsthist.status = '2');
UPDATE igs_ad_interface_ctl
SET status = '3'
WHERE interface_run_id = p_interface_run_id;
|| Added delete logic for Residency Details
|| Modified p_interface_run_id to l_interface_run_id so that the interface records can be updated with proper interface run id.
|| gmuralid 4-DEC-2002 Change by gmuralid, removed reference to table igs_ad_intl_int,
|| igs_pe_fund_dep_int.Included references to igs_pe_visa_int,
|| igs_pe_vst_hist_int,igs_pe_passport_int,igs_pe_eit_int in delete logic
|| As a part of BUG 2599109, SEVIS Build
|| npalanis 21-May-2002 Code is added to update interface_run_id in igs_ad_interface records
|| with status '1' ,'2' and '4' .The parameter p_interface_run_id passed
|| to prc_pe_dtls is also removed as no more updation of interface_run_id
|| is required there.
|| rrengara 4-OCT-2002 Changed the ordering of the parameters batch id and source type id for the Build bug 2604395
|| Called IGS_AD_INTERFACE_CTL tables TBH and assigned l_interface_run_id to the value from OUT NOCOPY parameter TBH
||
|| ssawhney 28-oct-2002 SWS104- Jan03 build residency details import added. moved acad honors to person level
|| IGS_AD_REFS_INT table obsoleted.
|| sjalsaut Oct 31, 02 SWSCR012 Bug 2435520 Removed College Activities references
|| and changed extracurr act to PERSON_ACTIVITIES
|| (reverse chronological order - newest change first)
||--------------------------------------------------------------------------------*/
l_prog_label VARCHAR2(4000);
SELECT batch_desc INTO l_batch_desc
FROM igs_ad_imp_batch_det
WHERE batch_id = p_batch_id ;
SELECT system_source_type INTO l_source_type
FROM igs_pe_src_types_all
WHERE source_type_id = p_source_type_id
AND NVL(closed_ind,'N') = 'N'
AND system_source_type IN ('APPLICATION', 'TEST_RESULTS', 'PROSPECT_LIST', 'PROSPECT_SS_WEB_INQUIRY', 'TRANSCRIPT');
SELECT match_set_name INTO l_match_set_name
FROM igs_pe_match_sets_all
WHERE match_set_id = p_match_set_id
AND closed_ind = 'N';
igs_ad_interface_ctl_pkg.insert_row (
x_rowid => l_rowid,
x_interface_run_id => l_interface_run_id ,
x_source_type_id => p_source_type_id,
x_batch_id => p_batch_id,
x_match_set_id => p_match_set_id,
x_status => '2',
x_mode => 'R');
SELECT COUNT (*) INTO l_cnt_dup_process_run
FROM igs_ad_interface_ctl
WHERE batch_id = p_batch_id
AND source_type_id = p_source_type_id
AND status = '2';
UPDATE igs_ad_interface_all
SET interface_run_id = l_interface_run_id
WHERE batch_id = p_batch_id
AND source_type_id = p_source_type_id
AND status IN ('1','2','4');
UPDATE igs_ad_interface_all int1
SET status ='3',
error_code = 'E712'
WHERE EXISTS ( SELECT 1 FROM igs_ad_interface_all
WHERE interface_id = int1.interface_id
AND rowid <> int1.rowid )
AND interface_run_id = l_interface_run_id;
igs_ad_imp_001.update_parent_record_status (p_source_type_id => p_source_type_id,
p_batch_id => p_batch_id,
p_interface_run_id => l_interface_run_id);
DELETE FROM igs_ad_interface_all
WHERE status = '1'
AND record_status ='1'
AND interface_run_id = l_interface_run_id;
UPDATE igs_ad_interface_all
SET record_status = '3'
WHERE interface_run_id = l_interface_run_id
AND status <> '1';
UPDATE igs_ad_interface_ctl
SET status = '3'
WHERE rowid = l_rowid;
SELECT *
FROM igs_ad_source_cat
WHERE source_type_id = p_source_type_id
AND category_name = p_category;