eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_AD_ACT_ASSESSMENTS_PKG dependencies on IGS_AD_ACT_ASSESSMENTS

Line 1: PACKAGE BODY IGS_AD_ACT_ASSESSMENTS_PKG AS

1: PACKAGE BODY IGS_AD_ACT_ASSESSMENTS_PKG AS
2: /* $Header: IGSADD1B.pls 120.8 2006/04/12 03:09:52 akadam noship $ */
3: /* ------------------------------------------------------------------------------------------------------------------------
4: || Created By :
5: || Created On :

Line 35: IGS_AD_ACT_ASSESSMENTS_S.nextval

31: FUNCTION get_batch_id RETURN NUMBER AS
32: BEGIN
33: IF batch_id IS NULL THEN
34: SELECT
35: IGS_AD_ACT_ASSESSMENTS_S.nextval
36: INTO batch_id
37: FROM dual;
38: END IF;
39: return batch_id;

Line 67: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,

63: Returns 1 if the Required setup is met.
64: This function also set the global variables to respective values requried while inserting into Interface Tables. */
65:
66: FUNCTION Check_Setups (
67: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
68: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
69: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
70: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
71: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)

Line 68: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

64: This function also set the global variables to respective values requried while inserting into Interface Tables. */
65:
66: FUNCTION Check_Setups (
67: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
68: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
69: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
70: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
71: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)
72: RETURN NUMBER AS

Line 69: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

65:
66: FUNCTION Check_Setups (
67: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
68: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
69: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
70: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
71: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)
72: RETURN NUMBER AS
73:

Line 70: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

66: FUNCTION Check_Setups (
67: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
68: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
69: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
70: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
71: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)
72: RETURN NUMBER AS
73:
74: l_return NUMBER;

Line 71: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)

67: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
68: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
69: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
70: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
71: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type)
72: RETURN NUMBER AS
73:
74: l_return NUMBER;
75: -- l_Country_Code VARCHAR2(30);

Line 134: CURSOR c_Assessment_grade_level ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,

130: AND NAME = 'STANDARD'
131: AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
132:
133: -- Cursor to Check the Assessments Grade Level Setup.
134: CURSOR c_Assessment_grade_level ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
135: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
136: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
137: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
138: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

Line 135: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

131: AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
132:
133: -- Cursor to Check the Assessments Grade Level Setup.
134: CURSOR c_Assessment_grade_level ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
135: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
136: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
137: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
138: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
139: SELECT

Line 136: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

132:
133: -- Cursor to Check the Assessments Grade Level Setup.
134: CURSOR c_Assessment_grade_level ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
135: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
136: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
137: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
138: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
139: SELECT
140: DISTINCT Grade_Level LEVEL_OF_QUAL

Line 137: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

133: -- Cursor to Check the Assessments Grade Level Setup.
134: CURSOR c_Assessment_grade_level ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
135: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
136: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
137: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
138: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
139: SELECT
140: DISTINCT Grade_Level LEVEL_OF_QUAL
141: FROM IGS_AD_ACT_ASSESSMENTS actas

Line 138: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

134: CURSOR c_Assessment_grade_level ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
135: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
136: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
137: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
138: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
139: SELECT
140: DISTINCT Grade_Level LEVEL_OF_QUAL
141: FROM IGS_AD_ACT_ASSESSMENTS actas
142: WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id

Line 141: FROM IGS_AD_ACT_ASSESSMENTS actas

137: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
138: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
139: SELECT
140: DISTINCT Grade_Level LEVEL_OF_QUAL
141: FROM IGS_AD_ACT_ASSESSMENTS actas
142: WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
143: AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
144: AND actas.Test_Type like nvl(cp_Test_Type,'%')
145: AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')

Line 158: CURSOR c_Assessment_Institution_code ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,

154: AND CLOSED_IND = 'N'
155: AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
156:
157: -- Cursor to Check the Institution Codes Setup.
158: CURSOR c_Assessment_Institution_code ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
159: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
160: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
161: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
162: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

Line 159: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

155: AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
156:
157: -- Cursor to Check the Institution Codes Setup.
158: CURSOR c_Assessment_Institution_code ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
159: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
160: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
161: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
162: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
163: SELECT

Line 160: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

156:
157: -- Cursor to Check the Institution Codes Setup.
158: CURSOR c_Assessment_Institution_code ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
159: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
160: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
161: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
162: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
163: SELECT
164: DISTINCT HIGH_SCHOOL_CODE INSTITUTION_CD

Line 161: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

157: -- Cursor to Check the Institution Codes Setup.
158: CURSOR c_Assessment_Institution_code ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
159: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
160: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
161: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
162: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
163: SELECT
164: DISTINCT HIGH_SCHOOL_CODE INSTITUTION_CD
165: FROM IGS_AD_ACT_ASSESSMENTS actas

Line 162: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

158: CURSOR c_Assessment_Institution_code ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
159: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
160: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
161: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
162: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
163: SELECT
164: DISTINCT HIGH_SCHOOL_CODE INSTITUTION_CD
165: FROM IGS_AD_ACT_ASSESSMENTS actas
166: WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id

Line 165: FROM IGS_AD_ACT_ASSESSMENTS actas

161: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
162: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
163: SELECT
164: DISTINCT HIGH_SCHOOL_CODE INSTITUTION_CD
165: FROM IGS_AD_ACT_ASSESSMENTS actas
166: WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
167: AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
168: AND actas.Test_Type like nvl(cp_Test_Type,'%')
169: AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')

Line 179: CURSOR c_Assessment_Test_Type ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,

175: IN (SELECT INSTITUTION_STATUS FROM IGS_OR_INST_STAT WHERE S_INSTITUTION_STATUS = 'ACTIVE' AND CLOSED_IND = 'N');
176:
177:
178: -- Cursor to Check the Assessments Test Type Setup.
179: CURSOR c_Assessment_Test_Type ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
180: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
181: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
182: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
183: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

Line 180: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

176:
177:
178: -- Cursor to Check the Assessments Test Type Setup.
179: CURSOR c_Assessment_Test_Type ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
180: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
181: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
182: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
183: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
184: SELECT

Line 181: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

177:
178: -- Cursor to Check the Assessments Test Type Setup.
179: CURSOR c_Assessment_Test_Type ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
180: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
181: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
182: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
183: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
184: SELECT
185: DISTINCT Decode (actas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',

Line 182: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

178: -- Cursor to Check the Assessments Test Type Setup.
179: CURSOR c_Assessment_Test_Type ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
180: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
181: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
182: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
183: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
184: SELECT
185: DISTINCT Decode (actas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
186: 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE

Line 183: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

179: CURSOR c_Assessment_Test_Type ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
180: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
181: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
182: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
183: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
184: SELECT
185: DISTINCT Decode (actas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
186: 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE
187: FROM IGS_AD_ACT_ASSESSMENTS actas

Line 187: FROM IGS_AD_ACT_ASSESSMENTS actas

183: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
184: SELECT
185: DISTINCT Decode (actas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
186: 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE
187: FROM IGS_AD_ACT_ASSESSMENTS actas
188: WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
189: AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
190: AND actas.Test_Type like nvl(cp_Test_Type,'%')
191: AND actas.TEST_DATE_TXT like nvl(cp_Test_Date,'%')

Line 202: cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,

198: WHERE SCORE_TYPE = 'OFFICIAL';
199:
200: -- Cursor to Check the Assessments Test Segments Setup.
201: CURSOR c_Test_Segments (
202: cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
203: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
204: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
205: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
206: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

Line 203: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

199:
200: -- Cursor to Check the Assessments Test Segments Setup.
201: CURSOR c_Test_Segments (
202: cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
203: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
204: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
205: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
206: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
207: SELECT/*+ no_expand */

Line 204: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

200: -- Cursor to Check the Assessments Test Segments Setup.
201: CURSOR c_Test_Segments (
202: cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
203: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
204: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
205: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
206: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
207: SELECT/*+ no_expand */
208: DISTINCT Decode (aas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',

Line 205: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

201: CURSOR c_Test_Segments (
202: cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
203: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
204: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
205: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
206: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
207: SELECT/*+ no_expand */
208: DISTINCT Decode (aas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
209: 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE,

Line 206: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

202: cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
203: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
204: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
205: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
206: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
207: SELECT/*+ no_expand */
208: DISTINCT Decode (aas.Test_Type,'D','ACT-DANTES','F','ACT-INTERNATIONAL','I',
209: 'ACT-INSTITUTION','R','ACT-RESIDUAL','S','ACT-STATE','Z','ACT-ARRANGED','N','ACT-NATIONAL') ADMISSION_TEST_TYPE,
210: Decode(aas.STATISTIC_TYPE,'TEST','SCORE','TEST_SUB_SCORE','SUB SCORE') SEGMENT_TYPE,

Line 224: AND aas.ACT_Identifier in (Select ACT_Identifier from IGS_AD_ACT_ASSESSMENTS where ACT_Batch_ID = cp_ACT_Batch_Id)

220: 'ARTSLIT','ELEMALG','PLGEOTRIG','RHESKILLS','SOCSTSCI' ,'USAGMECH' ,'WRITING')
221: AND trim(aas.SCORE) <> '--'
222: AND lkv.Lookup_code = aas.STATISTIC_CATEGORY
223: AND lkv.lookup_type = 'ACT_STATISTIC_CATEGORY'
224: AND aas.ACT_Identifier in (Select ACT_Identifier from IGS_AD_ACT_ASSESSMENTS where ACT_Batch_ID = cp_ACT_Batch_Id)
225: MINUS
226: SELECT
227: ADMISSION_TEST_TYPE,
228: SEGMENT_TYPE,

Line 350: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,

346:
347: PROCEDURE Insert_ACT_to_Interface (
348: ERRBUF OUT NOCOPY VARCHAR2,
349: RETCODE OUT NOCOPY NUMBER,
350: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
351: p_Source_Type_Id IN NUMBER,
352: p_Match_Set_Id IN NUMBER,
353: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
354: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

Line 353: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

349: RETCODE OUT NOCOPY NUMBER,
350: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
351: p_Source_Type_Id IN NUMBER,
352: p_Match_Set_Id IN NUMBER,
353: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
354: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
355: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
356: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type,
357: P_ADDR_USAGE_CD IN IGS_AD_ADDRUSAGE_INT_ALL.SITE_USE_CODE%type)

Line 354: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

350: p_ACT_Batch_Id IN IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
351: p_Source_Type_Id IN NUMBER,
352: p_Match_Set_Id IN NUMBER,
353: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
354: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
355: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
356: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type,
357: P_ADDR_USAGE_CD IN IGS_AD_ADDRUSAGE_INT_ALL.SITE_USE_CODE%type)
358:

Line 355: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

351: p_Source_Type_Id IN NUMBER,
352: p_Match_Set_Id IN NUMBER,
353: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
354: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
355: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
356: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type,
357: P_ADDR_USAGE_CD IN IGS_AD_ADDRUSAGE_INT_ALL.SITE_USE_CODE%type)
358:
359: AS

Line 356: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type,

352: p_Match_Set_Id IN NUMBER,
353: p_Reporting_Year IN IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
354: p_Test_Type IN IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
355: p_Test_Date IN IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
356: p_ACT_Id IN IGS_AD_ACT_ASSESSMENTS.act_identifier%type,
357: P_ADDR_USAGE_CD IN IGS_AD_ADDRUSAGE_INT_ALL.SITE_USE_CODE%type)
358:
359: AS
360:

Line 373: CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,

369: ||--------------------------------------------------------------------------------*/
370:
371:
372: -- Cusrsor to select the ACT Assessment Recrod
373: CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
374: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
375: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
376: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
377: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

Line 374: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

370:
371:
372: -- Cusrsor to select the ACT Assessment Recrod
373: CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
374: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
375: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
376: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
377: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
378: SELECT

Line 375: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

371:
372: -- Cusrsor to select the ACT Assessment Recrod
373: CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
374: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
375: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
376: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
377: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
378: SELECT
379: actas.ROWID, actas.*,cc.code_id

Line 376: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

372: -- Cusrsor to select the ACT Assessment Recrod
373: CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
374: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
375: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
376: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
377: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
378: SELECT
379: actas.ROWID, actas.*,cc.code_id
380: FROM IGS_AD_ACT_ASSESSMENTS actas,

Line 377: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

373: CURSOR c_Act_Assessment ( cp_ACT_Batch_Id IGS_AD_ACT_ASSESSMENTS.ACT_BATCH_ID%type,
374: cp_Reporting_Year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
375: cp_Test_Type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
376: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
377: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
378: SELECT
379: actas.ROWID, actas.*,cc.code_id
380: FROM IGS_AD_ACT_ASSESSMENTS actas,
381: IGS_AD_CODE_CLASSES cc

Line 380: FROM IGS_AD_ACT_ASSESSMENTS actas,

376: cp_Test_Date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
377: cp_ACT_Id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
378: SELECT
379: actas.ROWID, actas.*,cc.code_id
380: FROM IGS_AD_ACT_ASSESSMENTS actas,
381: IGS_AD_CODE_CLASSES cc
382: WHERE actas.ACT_Batch_ID = cp_ACT_Batch_Id
383: AND actas.Reporting_Year like nvl(cp_Reporting_Year,'%')
384: AND actas.Test_Type like nvl(cp_Test_Type,'%')

Line 419: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

415: AND Lookup_code = cp_Statistic_category;
416:
417: -- Cusrsor to select the ACT Statistics Recrods for Test and Sub Test Statistic Types
418: CURSOR c_Act_Statistic_Test (
419: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
420: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
421: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
422: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
423: SELECT

Line 420: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

416:
417: -- Cusrsor to select the ACT Statistics Recrods for Test and Sub Test Statistic Types
418: CURSOR c_Act_Statistic_Test (
419: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
420: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
421: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
422: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
423: SELECT
424: *

Line 421: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

417: -- Cusrsor to select the ACT Statistics Recrods for Test and Sub Test Statistic Types
418: CURSOR c_Act_Statistic_Test (
419: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
420: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
421: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
422: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
423: SELECT
424: *
425: FROM IGS_AD_ACT_STATISTICS

Line 422: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

418: CURSOR c_Act_Statistic_Test (
419: cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
420: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
421: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
422: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
423: SELECT
424: *
425: FROM IGS_AD_ACT_STATISTICS
426: WHERE REPORTING_YEAR = cp_reporting_year

Line 437: CURSOR c_Act_Statistic_High_School (cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,

433: AND trim(SCORE) <> '--';
434:
435:
436: -- Cusrsor to select the ACT Statistics Recrods for 'HIGH_SCHOOL' Statistic Types
437: CURSOR c_Act_Statistic_High_School (cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
438: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
439: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
440: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
441: SELECT

Line 438: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,

434:
435:
436: -- Cusrsor to select the ACT Statistics Recrods for 'HIGH_SCHOOL' Statistic Types
437: CURSOR c_Act_Statistic_High_School (cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
438: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
439: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
440: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
441: SELECT
442: *

Line 439: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,

435:
436: -- Cusrsor to select the ACT Statistics Recrods for 'HIGH_SCHOOL' Statistic Types
437: CURSOR c_Act_Statistic_High_School (cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
438: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
439: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
440: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
441: SELECT
442: *
443: FROM IGS_AD_ACT_STATISTICS

Line 440: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS

436: -- Cusrsor to select the ACT Statistics Recrods for 'HIGH_SCHOOL' Statistic Types
437: CURSOR c_Act_Statistic_High_School (cp_reporting_year IGS_AD_ACT_ASSESSMENTS.REPORTING_YEAR%type,
438: cp_test_type IGS_AD_ACT_ASSESSMENTS.TEST_TYPE%type,
439: cp_test_date IGS_AD_ACT_ASSESSMENTS.TEST_DATE_TXT%type,
440: cp_act_id IGS_AD_ACT_ASSESSMENTS.act_identifier%type) IS
441: SELECT
442: *
443: FROM IGS_AD_ACT_STATISTICS
444: WHERE REPORTING_YEAR = cp_reporting_year

Line 462: l_ACT_Exist igs_Ad_Act_Assessments.act_identifier%type;

458: l_interface_acadhis_id igs_ad_acadhis_int_all.interface_acadhis_id%type;
459: l_Interface_Transcript_Id igs_ad_txcpt_int.interface_transcript_id%type;
460: l_interface_term_dtls_id igs_ad_trmdt_int.interface_term_dtls_id%type;
461:
462: l_ACT_Exist igs_Ad_Act_Assessments.act_identifier%type;
463: l_rec_count NUMBER;
464: l_rec_fail_count NUMBER;
465: l_ERRBUF VARCHAR2(1000);
466: l_RETCODE NUMBER;

Line 468: l_High_School_Code igs_Ad_Act_Assessments.High_School_Code%type;

464: l_rec_fail_count NUMBER;
465: l_ERRBUF VARCHAR2(1000);
466: l_RETCODE NUMBER;
467: l_Interface_Id_exist igs_ad_interface_all.Interface_Id%type;
468: l_High_School_Code igs_Ad_Act_Assessments.High_School_Code%type;
469: l_High_School_Graduation igs_Ad_Act_Assessments.High_School_Graduation%type;
470:
471: l_test_segment_id igs_ad_test_segs_int.test_segment_id%type;
472: l_test_score igs_ad_test_segs_int.test_score%type;

Line 469: l_High_School_Graduation igs_Ad_Act_Assessments.High_School_Graduation%type;

465: l_ERRBUF VARCHAR2(1000);
466: l_RETCODE NUMBER;
467: l_Interface_Id_exist igs_ad_interface_all.Interface_Id%type;
468: l_High_School_Code igs_Ad_Act_Assessments.High_School_Code%type;
469: l_High_School_Graduation igs_Ad_Act_Assessments.High_School_Graduation%type;
470:
471: l_test_segment_id igs_ad_test_segs_int.test_segment_id%type;
472: l_test_score igs_ad_test_segs_int.test_score%type;
473: l_national_percentile igs_ad_act_statistics.national_norm%type;

Line 1298: Update IGS_AD_ACT_ASSESSMENTS set Interface_Transfer_Date = SYSDATE where rowid = Act_Assessment_Rec.rowid;

1294: END IF;
1295: END LOOP;
1296: END IF; -- Academic History Details
1297: -- Update the IGS_AD_ACT_ASSSESSMENT table Interface_Transfer_Date with Sysdate
1298: Update IGS_AD_ACT_ASSESSMENTS set Interface_Transfer_Date = SYSDATE where rowid = Act_Assessment_Rec.rowid;
1299:
1300: EXCEPTION
1301: WHEN OTHERS THEN
1302: -- Insert a log message that Person Record failed to populate into Interface Tables.

Line 1353: END IGS_AD_ACT_ASSESSMENTS_PKG;

1349: errbuf:=SQLERRM;
1350:
1351: END Insert_ACT_to_Interface;-- End of Procedure
1352:
1353: END IGS_AD_ACT_ASSESSMENTS_PKG;