[Home] [Help]
27: nominated_course_cd = cp_nominated_course_cd AND
28: sequence_number = cp_sequence_number;
29:
30: PROCEDURE logdetail(l_id IN igs_ad_offresp_int.offresp_int_id%TYPE,
31: l_err_code IN igs_ad_offresp_err.error_code%TYPE,
32: l_err_msg IN igs_ad_offresp_err.error_text%TYPE,
33: l_debug_msg IN VARCHAR2,
34: l_first_flag IN VARCHAR2) IS
35: ---------------------------------------------------------------------------------------------------------------------------------------
28: sequence_number = cp_sequence_number;
29:
30: PROCEDURE logdetail(l_id IN igs_ad_offresp_int.offresp_int_id%TYPE,
31: l_err_code IN igs_ad_offresp_err.error_code%TYPE,
32: l_err_msg IN igs_ad_offresp_err.error_text%TYPE,
33: l_debug_msg IN VARCHAR2,
34: l_first_flag IN VARCHAR2) IS
35: ---------------------------------------------------------------------------------------------------------------------------------------
36: -- Created By : rboddu
44: l_full_string VARCHAR2(400);
45: l_meaning VARCHAR2(400);
46: l_bat_string VARCHAR2(100);
47: l_text VARCHAR2(400);
48: CURSOR c_lkup_meaning(l_code igs_ad_offresp_err.error_code%TYPE) IS
49: SELECT meaning
50: FROM igs_lookups_view
51: WHERE lookup_type = 'IMPORT_ERROR_CODE' AND
52: lookup_code = l_code;
119: END IF;
120: FND_FILE.PUT_LINE(FND_FILE.LOG,'logdetail: '||FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' '||SQLERRM);
121: END logdetail;
122:
123: --Procedure to Update igs_ad_offresp_int with the Error status and insert the corresponding Error record into igs_ad_offresp_err.
124: PROCEDURE insert_int_error( p_offresp_int_id IN igs_ad_offresp_int.offresp_int_id%TYPE,
125: p_error_code IN igs_ad_offresp_err.error_code%TYPE,
126: p_message_name IN VARCHAR2
127: ) IS
121: END logdetail;
122:
123: --Procedure to Update igs_ad_offresp_int with the Error status and insert the corresponding Error record into igs_ad_offresp_err.
124: PROCEDURE insert_int_error( p_offresp_int_id IN igs_ad_offresp_int.offresp_int_id%TYPE,
125: p_error_code IN igs_ad_offresp_err.error_code%TYPE,
126: p_message_name IN VARCHAR2
127: ) IS
128: ---------------------------------------------------------------------------------------------------------------------------------------
129: -- Created By : rboddu
127: ) IS
128: ---------------------------------------------------------------------------------------------------------------------------------------
129: -- Created By : rboddu
130: -- Date Created On : 09-SEP-2002
131: -- Purpose : Bug 2395510. Procedure to insert Error Code / Error Message text into igs_ad_offresp_err, for the failed validations.
132: -- Know limitations, enhancements or remarks
133: -- Change History
134: -- Who When What
135: ---------------------------------------------------------------------------------------------------------------------------------------
145: l_message_text := fnd_message.get();
146: END IF;
147: END IF;
148:
149: INSERT INTO igs_ad_offresp_err(
150: offresp_err_id,
151: offresp_int_id,
152: error_code,
153: error_text,
160: program_application_id,
161: program_update_date,
162: program_id)
163: VALUES(
164: igs_ad_offresp_err_s.nextval,
165: p_offresp_int_id,
166: p_error_code,
167: l_message_text,
168: fnd_global.user_id,
867: -- Purpose : Bug 2395510. Main procedure of this package.
868: -- Process flow
869: -- 1. Import all the Pending Interface Offer Response records into PL/SQL tables using BULK COLLECT.
870: -- 2. Perform all the validations on each Interface record by calling local procedure validate_off_resp_dtls.
871: -- 3. If any of the validations fail then insert Error record into the interface error table IGS_AD_OFFRESP_ERR.
872: -- 3. If validations Successful then Update the OSS table IGS_AD_PS_APPL_INST_ALL with Interface Offer Response details.
873: -- 4. If the Offer Response status of Application Instance is updated to 'ACCEPTED' then perform the Pre enrollment by calling
874: -- the corresponding job.
875: -- 5. If the record is succesfully imported then Delete the interface record from IGS_AD_OFFRESP_INT.
1022: BEGIN
1023: l_gather_return := fnd_installation.get_app_info('IGS', l_gather_status, l_industry, l_schema);
1024: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_OFFRESP_BATCH', cascade => TRUE);
1025: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_OFFRESP_INT', cascade => TRUE);
1026: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema, tabname => 'IGS_AD_OFFRESP_ERR', cascade => TRUE);
1027: EXCEPTION WHEN OTHERS THEN
1028: NULL;
1029: END;
1030:
1029: END;
1030:
1031:
1032: -- Delete all the records from the error table for the currently processed Batch ID
1033: DELETE igs_ad_offresp_err WHERE offresp_int_id IN (SELECT offresp_int_id FROM igs_ad_offresp_int WHERE batch_id = p_batch_id);
1034:
1035: --Fetch All the Pending (Status = '2) Offer Response Interface records from IGS_AD_OFFRESP_INT (for the given batch id.)
1036: OPEN int_off_resp_cur;
1037: FETCH int_off_resp_cur BULK COLLECT INTO