[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
IGS_AD_ACT_ASSESSMENTS_S.nextval
INTO batch_id
FROM dual;
SELECT meaning from igs_lookup_values where lookup_code = cp_lookup_code and lookup_type = 'ACT_STATISTIC_CATEGORY';
This function also set the global variables to respective values requried while inserting into Interface Tables. */
FUNCTION Check_Setups (
p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)
RETURN NUMBER AS
l_return NUMBER;
SELECT
PERSON_ID_TYPE
FROM IGS_PE_PERSON_ID_TYP
WHERE S_PERSON_ID_TYPE = 'SSN'
AND CLOSED_IND ='N';
SELECT
PERSON_ID_TYPE
FROM IGS_PE_PERSON_ID_TYP
WHERE S_PERSON_ID_TYPE = 'ACTID'
AND CLOSED_IND ='N';
SELECT
CODE_ID
FROM IGS_AD_CODE_CLASSES
WHERE CLASS = 'SYS_SCORE_SOURCE'
AND NAME = 'ELEC'
AND CLOSED_IND = 'N'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT
CODE_ID
FROM IGS_AD_CODE_CLASSES
WHERE CLASS = 'TRANSCRIPT_SOURCE'
AND CLOSED_IND = 'N'
AND NAME = 'ACT'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT
CODE_ID
FROM IGS_AD_CODE_CLASSES
WHERE CLASS = 'GRADING_SCALE_TYPES'
AND CLOSED_IND = 'N'
AND NAME = '4 POINT'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT
CODE_ID
FROM IGS_AD_CODE_CLASSES
WHERE CLASS = 'UNIT_DIFFICULTY'
AND CLOSED_IND = 'N'
AND NAME = 'STANDARD'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT
DISTINCT Grade_Level LEVEL_OF_QUAL
FROM IGS_AD_ACT_ASSESSMENTS actas
WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
AND actas.Test_Type like nvl(cp_Test_Type,'%')
AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
AND actas.Interface_Transfer_Date IS NULL
AND actas.Grade_Level IS NOT NULL
MINUS
SELECT
NAME LEVEL_OF_QUAL
FROM IGS_AD_CODE_CLASSES
WHERE CLASS ='LEVEL_OF_QUAL'
AND CLOSED_IND = 'N'
AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT
DISTINCT HIGH_SCHOOL_CODE INSTITUTION_CD
FROM IGS_AD_ACT_ASSESSMENTS actas
WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
AND actas.Test_Type like nvl(cp_Test_Type,'%')
AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
AND actas.Interface_Transfer_Date IS NULL
AND actas.High_School_Code IS NOT NULL
MINUS
SELECT INSTITUTION_CD FROM IGS_OR_INSTITUTION WHERE INSTITUTION_STATUS
IN (SELECT INSTITUTION_STATUS FROM IGS_OR_INST_STAT WHERE S_INSTITUTION_STATUS = 'ACTIVE' AND CLOSED_IND = 'N');
SELECT
DISTINCT Decode (actas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE
FROM IGS_AD_ACT_ASSESSMENTS actas
WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
AND actas.Test_Type like nvl(cp_Test_Type,'%')
AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
AND actas.Interface_Transfer_Date IS NULL
MINUS
SELECT
ADMISSION_TEST_TYPE
FROM IGS_AD_TEST_TYPE
WHERE SCORE_TYPE = 'OFFICIAL';
SELECT/*+ no_expand */
DISTINCT Decode (aas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE,
Decode(aas.STATISTIC_TYPE,'TEST','SCORE','TEST_SUB_SCORE','SUB SCORE') SEGMENT_TYPE,
lkv.meaning TEST_SEGMENT_NAME
FROM IGS_AD_ACT_STATISTICS aas ,
IGS_LOOKUP_VALUES lkv
WHERE aas.Reporting_Year like nvl(cp_Reporting_Year,'%')
AND aas.Test_Type like nvl(cp_Test_Type,'%')
AND aas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
AND aas.ACT_Identifier like nvl(cp_ACT_Id,'%')
AND aas.STATISTIC_TYPE IN ('TEST','TEST_SUB_SCORE')
AND STATISTIC_CATEGORY IN ('COMENGWRI','COMPOSITE','ENGLISH','MATHS','READING','SCIENCE','ALGCOGEOM',
'ARTSLIT','ELEMALG','PLGEOTRIG','RHESKILLS','SOCSTSCI' ,'USAGMECH' ,'WRITING')
AND trim(aas.SCORE) <> '--'
AND lkv.Lookup_code = aas.STATISTIC_CATEGORY
AND lkv.lookup_type = 'ACT_STATISTIC_CATEGORY'
AND aas.ACT_Identifier in (Select ACT_Identifier from IGS_AD_ACT_ASSESSMENTS where ACT_Batch_ID = cp_ACT_Batch_Id)
MINUS
SELECT
ADMISSION_TEST_TYPE,
SEGMENT_TYPE,
TEST_SEGMENT_NAME
FROM IGS_AD_TEST_SEGMENTS
WHERE CLOSED_IND = 'N';
PROCEDURE Insert_ACT_to_Interface (
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
p_Source_Type_Id IN NUMBER,
p_Match_Set_Id IN NUMBER,
p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type,
P_ADDR_USAGE_CD IN IGS_AD_ADDRUSAGE_INT_ALL.SITE_USE_CODE%type)
AS
/*----------------------------------------------------------------------------------
|| Created By : stammine
|| Created On : 09-Nov-2004
|| Purpose : This procedure Loads the data into Ad Interface tables from ACT Data Store
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
||--------------------------------------------------------------------------------*/
-- Cusrsor to select the ACT Assessment Recrod
CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
SELECT
actas.ROWID, actas.*,cc.code_id
FROM IGS_AD_ACT_ASSESSMENTS actas,
IGS_AD_CODE_CLASSES cc
WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
AND actas.Test_Type like nvl(cp_Test_Type,'%')
AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')
AND actas.ACT_Identifier like nvl(cp_ACT_Id,'%')
AND actas.Interface_Transfer_Date IS NULL
AND cc.name (+) = actas.grade_level
AND cc.class (+) = 'LEVEL_OF_QUAL'
AND cc.closed_ind (+) = 'N'
AND cc.class_type_code (+)='ADM_CODE_CLASSES'
ORDER BY actas.act_identifier,
actas.TEST_DATE_TXT DESC,
actas.High_School_Code,
actas.High_School_Graduation;
SELECT
TEST_SEGMENT_ID
FROM IGS_AD_TEST_SEGMENTS
WHERE ADMISSION_TEST_TYPE = cp_admission_test_type
AND UPPER(trim(TEST_SEGMENT_NAME)) = UPPER(trim(cp_test_segment_name))
AND SEGMENT_TYPE = cp_segment_type;
SELECT
Description
FROM IGS_LOOKUP_VALUES
WHERE lookup_TYPE = 'ACT_STATISTIC_CATEGORY'
AND Lookup_code = cp_Statistic_category;
SELECT
*
FROM IGS_AD_ACT_STATISTICS
WHERE REPORTING_YEAR = cp_reporting_year
AND TEST_TYPE = cp_test_type
AND TEST_DATE_TXT = cp_test_date
AND ACT_Identifier = cp_act_id
AND STATISTIC_TYPE IN ('TEST','TEST_SUB_SCORE')
AND STATISTIC_CATEGORY IN ('COMENGWRI','COMPOSITE','ENGLISH','MATHS','READING','SCIENCE','ALGCOGEOM',
'ARTSLIT','ELEMALG','PLGEOTRIG','RHESKILLS','SOCSTSCI' ,'USAGMECH' ,'WRITING')
AND trim(SCORE) <> '--';
SELECT
*
FROM IGS_AD_ACT_STATISTICS
WHERE REPORTING_YEAR = cp_reporting_year
AND TEST_TYPE = cp_test_type
AND TEST_DATE_TXT = cp_test_date
AND ACT_Identifier = cp_act_id
AND STATISTIC_TYPE = 'HIGH_SCHOOL'
AND (COURSE_STATUS IN ('1','2') OR COURSE_STATUS IS NULL); -- exam taken(1) or Planned(2) or Numeric Grades
INSERT
INTO IGS_AD_IMP_BATCH_DET
(
BATCH_ID ,
BATCH_DESC ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_INTERFACE_BATCH_ID_S.NEXTVAL,
l_Batch_Desc,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
) returning BATCH_ID into l_Batch_Id
;
INSERT
INTO IGS_AD_INTERFACE_ALL
(
INTERFACE_ID ,
BATCH_ID ,
SOURCE_TYPE_ID ,
SURNAME ,
MIDDLE_NAME ,
GIVEN_NAMES ,
SEX ,
BIRTH_DT ,
LEVEL_OF_QUAL ,
STATUS ,
RECORD_STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_INTERFACE_S.NEXTVAL,
l_Batch_id,
p_Source_Type_Id,
Act_Assessment_rec.last_name,
Act_Assessment_rec.middle_initial,
Act_Assessment_rec.first_name,
Decode(upper(Act_Assessment_rec.gender),'M','MALE','F','FEMALE','UNKNOWN'),
to_date(Act_Assessment_rec.date_of_birth_txt,'YYYYMMDD'),
Act_Assessment_rec.code_id, -- Grade level need to be defined in IGS_AD_CODE_CLASSES
--WHERE CLASS ='LEVEL_OF_QUAL' code_id is retrieved from the code_classes table
'2',
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
) returning INTERFACE_ID into l_interface_id;
INSERT
INTO IGS_AD_ADDR_INT_ALL
(
INTERFACE_ADDR_ID,
INTERFACE_ID ,
ADDR_LINE_1 ,
POSTCODE ,
CITY ,
STATE ,
--COUNTY, -- temp
COUNTRY ,
START_DATE ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_ADDR_INT_S.NEXTVAL,
l_interface_id,
Act_Assessment_rec.street_address,
Act_Assessment_rec.zip_code,
Act_Assessment_rec.city, --'ALBANY',
Act_Assessment_rec.state_abbreviation, --'NY',
--'ALBANY', --temp
'US',
SYSDATE,
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
)returning INTERFACE_ADDR_ID into l_interface_addr_id;
INSERT INTO igs_ad_addrusage_int_all (
interface_addrusage_id,
interface_addr_id,
site_use_code,
status,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
igs_ad_addrusage_int_s.NEXTVAL,
l_interface_addr_id, -- interfaced Id populated in igs_ad_addr_int_all.
p_addr_usage_cd,
'2',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
);
INSERT
INTO IGS_AD_CONTACTS_INT_ALL
(
INTERFACE_CONTACTS_ID ,
INTERFACE_ID ,
CONTACT_POINT_TYPE ,
PHONE_LINE_TYPE ,
PHONE_AREA_CODE ,
PHONE_NUMBER ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_CONTACTS_INT_S.NEXTVAL,
l_interface_id,
'PHONE', -- COMMUNICATION_TYPE
'GEN' ,-- Telephone PHONE_LINE_TYPE
SUBSTR(Act_Assessment_rec.home_phone,1,3),
SUBSTR(Act_Assessment_rec.home_phone,4,7),
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
INSERT
INTO IGS_AD_CONTACTS_INT_ALL
(
INTERFACE_CONTACTS_ID ,
INTERFACE_ID ,
CONTACT_POINT_TYPE ,
PHONE_LINE_TYPE ,
PHONE_NUMBER ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_CONTACTS_INT_S.NEXTVAL,
l_interface_id,
'PHONE', -- COMMUNICATION_TYPE
'GEN' ,-- Telephone PHONE_LINE_TYPE
Act_Assessment_rec.home_phone,
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
INSERT
INTO IGS_AD_API_INT_ALL
(
INTERFACE_API_ID ,
INTERFACE_ID ,
PERSON_ID_TYPE ,
ALTERNATE_ID ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_API_INT_S.nextval,
l_interface_id,
G_SSN_Person_Id_Type, -- 'SSN', -- {get the person_type having the System Person Type = SSN}
Act_Assessment_rec.act_identifier,
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
INSERT
INTO IGS_AD_API_INT_ALL
(
INTERFACE_API_ID ,
INTERFACE_ID ,
PERSON_ID_TYPE ,
ALTERNATE_ID ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_API_INT_S.nextval,
l_interface_id,
G_ACT_Person_Id_Type, -- 'ACTID', -- {get the person_type having the System Person Type = ACT}
SUBSTR(Act_Assessment_rec.act_identifier,(Decode(SUBSTR(Act_Assessment_rec.act_identifier,1,1),'-',2,1))),
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
END IF; -- End Insert Most Recent Person Details
INSERT
INTO IGS_AD_TEST_INT
(
INTERFACE_TEST_ID ,
INTERFACE_ID ,
ADMISSION_TEST_TYPE ,
TEST_DATE ,
SCORE_TYPE ,
SCORE_SOURCE_ID ,
SCORE_REPORT_DATE ,
SPECIAL_CODE ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN,
ACTIVE_IND
)
VALUES
(
IGS_AD_TEST_INT_S.NEXTVAL,
l_interface_id,
Decode (Act_Assessment_rec.test_type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL'),
to_date( Act_Assessment_rec.test_date_txt||'01','YYYYMMDD'),
'OFFICIAL',
G_Score_Source_id,
SYSDATE,
Act_Assessment_rec.corrected_report_ind, -- if the data is ' ' in Data file value in the Assessments table is NULL
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
'Y'
) returning INTERFACE_TEST_ID,ADMISSION_TEST_TYPE into l_interface_test_id,l_admission_test_type;
INSERT
INTO IGS_AD_TEST_SEGS_INT
(
INTERFACE_TESTSEGS_ID ,
INTERFACE_TEST_ID ,
ADMISSION_TEST_TYPE ,
TEST_SEGMENT_ID ,
TEST_SCORE ,
NATIONAL_PERCENTILE ,
STATE_PERCENTILE ,
SCORE_BAND_UPPER ,
SCORE_BAND_LOWER ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_TEST_SEGS_INT_S.NEXTVAL,
l_interface_test_id,
l_admission_test_type,
l_test_segment_id,
l_test_score,
DECODE(INSTR(l_national_percentile,'-'),0,to_number(l_national_percentile),NULL), --l_national_percentile bug#4693325
DECODE(INSTR(l_state_percentile,'-'),0,to_number(l_state_percentile),NULL), --l_state_percentile
l_score_band_upper,
l_score_band_lower,
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
);
END LOOP; --- End Inserting multiple records of Test Details for a Person
INSERT
INTO IGS_AD_ACADHIS_INT_ALL
(
INTERFACE_ACADHIS_ID ,
INTERFACE_ID ,
INSTITUTION_CODE ,
CURRENT_INST ,
PLANNED_COMPLETION_DATE ,
SELFREP_INST_GPA ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_ACADHIS_INT_S.NEXTVAL,
l_interface_id,
Act_Assessment_Rec.high_school_code,
'N',
DECODE(Act_Assessment_Rec.high_school_graduation,'',NULL,to_date(Act_Assessment_Rec.high_school_graduation||'0501','YYYYMMDD')),
-- changed planned completion date from 01-jan to 01-may bug# 4252413
DECODE(INSTR(Act_Assessment_Rec.high_school_average,'-'),0,Act_Assessment_Rec.high_school_average,NULL),
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
) returning INTERFACE_ACADHIS_ID into l_interface_acadhis_id;
INSERT
INTO IGS_AD_TXCPT_INT
(
INTERFACE_TRANSCRIPT_ID ,
TRANSCRIPT_STATUS ,
TRANSCRIPT_TYPE ,
TRANSCRIPT_SOURCE ,
INTERFACE_ACADHIS_ID ,
STATUS ,
ENTERED_GS_ID ,
CONV_GS_ID ,
TERM_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
DATE_OF_ISSUE
)
VALUES
(
IGS_AD_TXCPT_INT_S.NEXTVAL,
'FINAL', -- Lookup code for lookup_type = TRANSCRIPT_STATUS
'OFFICIAL', -- Lookup code for lookup_type = TRANSCRIPT_TYPE
G_Transcript_Source, --User Defined 'ACT' Transcript Info Code Class 'Transcript Source'
l_interface_acadhis_id,
'2',
G_Grading_Scale, -- User Defined '4 POINT' Transcript Info Code Class 'Grading Scale Types'
G_Grading_Scale, -- User Defined '4 POINT' Transcript Info Code Class 'Grading Scale Types'
'S', -- Term Type is Defined as 'S' for Semister in lookup for TERM_TYPE
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD')
) returning INTERFACE_TRANSCRIPT_ID into l_Interface_Transcript_Id ;
INSERT
INTO IGS_AD_TRMDT_INT
(
INTERFACE_TERM_DTLS_ID ,
INTERFACE_TRANSCRIPT_ID ,
START_DATE ,
END_DATE ,
TERM ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
igs_ad_trmdt_int_s.NEXTVAL,
l_Interface_Transcript_Id,
to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD'),
to_date( Act_Assessment_rec.TEST_DATE_TXT||'02','YYYYMMDD'),
'ACT - '||to_char(to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD'),'Mon DD, YYYY'), -- 'ACT Test Date - '||to_char(to_date( Act_Assessment_rec.TEST_DATE_TXT||'01','YYYYMMDD'),'Month DD, YYYY'),
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
)returning INTERFACE_TERM_DTLS_ID into l_interface_term_dtls_id;
INSERT
INTO IGS_AD_TUNDT_INT
(
INTERFACE_TERM_UNITDTLS_ID ,
INTERFACE_TERM_DTLS_ID ,
UNIT ,
UNIT_DIFFICULTY ,
UNIT_NAME ,
GRADE ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_TUNDT_INT_S.NEXTVAL,
l_interface_term_dtls_id,
Act_Statistic_HS_Rec.statistic_category ,
G_Unit_Difficulty,
l_Statistic_Category_desc,
l_grade,
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
) ;
INSERT
INTO IGS_AD_TUNDT_INT
(
INTERFACE_TERM_UNITDTLS_ID ,
INTERFACE_TERM_DTLS_ID ,
UNIT ,
UNIT_DIFFICULTY ,
UNIT_NAME ,
GRADE ,
STATUS ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
)
VALUES
(
IGS_AD_TUNDT_INT_S.NEXTVAL,
l_interface_term_dtls_id,
Act_Statistic_HS_Rec.statistic_category ,
G_Unit_Difficulty,
l_Statistic_Category_desc,
Decode(trim(l_grade),'4','A','3','B','2','C','1','D','0','F','','P',l_grade),
'2',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID
) ;
Update IGS_AD_ACT_ASSESSMENTS set Interface_Transfer_Date = SYSDATE where rowid = Act_Assessment_Rec.rowid;
END; -- END of Inserting a Single Transaction
END LOOP --END of Inserting all Person details
COMMIT;
FND_FILE.PUT_LINE(Fnd_File.LOG,l_rec_fail_count || ' ACT records have failed to insert into the interface tables');
END Insert_ACT_to_Interface;-- End of Procedure