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_HE_FTE_CALC_PKG dependencies on FND_DSQL

Line 1077: -- initialize fnd_dsql data-structures

1073: log_messages('P_SELECTION_DT_TO ',p_selection_dt_to);
1074: log_messages('P_ASS_RES_ST_FTE ',p_app_res_st_fte);
1075: log_messages('P_ATT_PRC_ST_FTE ',p_att_prc_st_fte);
1076:
1077: -- initialize fnd_dsql data-structures
1078: fnd_dsql.init;
1079:
1080: -- Construct Initial SPA Selection SQL statement.
1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');

Line 1078: fnd_dsql.init;

1074: log_messages('P_ASS_RES_ST_FTE ',p_app_res_st_fte);
1075: log_messages('P_ATT_PRC_ST_FTE ',p_att_prc_st_fte);
1076:
1077: -- initialize fnd_dsql data-structures
1078: fnd_dsql.init;
1079:
1080: -- Construct Initial SPA Selection SQL statement.
1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');
1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');

Line 1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');

1077: -- initialize fnd_dsql data-structures
1078: fnd_dsql.init;
1079:
1080: -- Construct Initial SPA Selection SQL statement.
1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');
1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');
1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');

Line 1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');

1078: fnd_dsql.init;
1079:
1080: -- Construct Initial SPA Selection SQL statement.
1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');
1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');
1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');

Line 1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');

1079:
1080: -- Construct Initial SPA Selection SQL statement.
1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');
1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');
1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');
1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');

Line 1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');

1080: -- Construct Initial SPA Selection SQL statement.
1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');
1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');
1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');
1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');
1088: fnd_dsql.add_text(' AND pe.person_id = spa.person_id ');

Line 1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');

1081: fnd_dsql.add_text('SELECT pe.person_number,spa.person_id, spa.course_cd ,spa.version_number,spa.coo_id,');
1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');
1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');
1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');
1088: fnd_dsql.add_text(' AND pe.person_id = spa.person_id ');
1089:

Line 1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');

1082: fnd_dsql.add_text(' spa.course_attempt_status, spa.discontinued_dt,');
1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');
1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');
1088: fnd_dsql.add_text(' AND pe.person_id = spa.person_id ');
1089:
1090: -- Include person id criteria if required

Line 1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');

1083: fnd_dsql.add_text(' spa.course_rqrmnts_complete_dt, spa.commencement_dt,');
1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');
1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');
1088: fnd_dsql.add_text(' AND pe.person_id = spa.person_id ');
1089:
1090: -- Include person id criteria if required
1091: IF p_person_id IS NOT NULL THEN

Line 1088: fnd_dsql.add_text(' AND pe.person_id = spa.person_id ');

1084: fnd_dsql.add_text(' spa.course_rqrmnt_complete_ind, hspa.student_inst_number');
1085: fnd_dsql.add_text(' FROM igs_en_stdnt_ps_att_all spa, igs_he_st_spa_all hspa, igs_pe_person_base_v pe ');
1086: fnd_dsql.add_text(' WHERE hspa.person_id = spa.person_id ');
1087: fnd_dsql.add_text(' AND hspa.course_cd = spa.course_cd ');
1088: fnd_dsql.add_text(' AND pe.person_id = spa.person_id ');
1089:
1090: -- Include person id criteria if required
1091: IF p_person_id IS NOT NULL THEN
1092:

Line 1093: fnd_dsql.add_text(' AND spa.person_id = ');

1089:
1090: -- Include person id criteria if required
1091: IF p_person_id IS NOT NULL THEN
1092:
1093: fnd_dsql.add_text(' AND spa.person_id = ');
1094: fnd_dsql.add_bind(p_person_id);
1095:
1096: END IF;
1097:

Line 1094: fnd_dsql.add_bind(p_person_id);

1090: -- Include person id criteria if required
1091: IF p_person_id IS NOT NULL THEN
1092:
1093: fnd_dsql.add_text(' AND spa.person_id = ');
1094: fnd_dsql.add_bind(p_person_id);
1095:
1096: END IF;
1097:
1098: -- Include program code criteria if required

Line 1101: fnd_dsql.add_text(' AND spa.course_cd = ');

1097:
1098: -- Include program code criteria if required
1099: IF p_course_cd IS NOT NULL THEN
1100:
1101: fnd_dsql.add_text(' AND spa.course_cd = ');
1102: fnd_dsql.add_bind(p_course_cd);
1103:
1104: END IF;
1105:

Line 1102: fnd_dsql.add_bind(p_course_cd);

1098: -- Include program code criteria if required
1099: IF p_course_cd IS NOT NULL THEN
1100:
1101: fnd_dsql.add_text(' AND spa.course_cd = ');
1102: fnd_dsql.add_bind(p_course_cd);
1103:
1104: END IF;
1105:
1106: -- Include person ID group criteria if required (person_id cannot be entered)

Line 1116: fnd_dsql.add_text(' AND EXISTS ' );

1112: CLOSE c_group_type;
1113:
1114: IF l_group_type = 'STATIC' THEN
1115:
1116: fnd_dsql.add_text(' AND EXISTS ' );
1117: fnd_dsql.add_text(' (SELECT ''X'' ');
1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );
1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');

Line 1117: fnd_dsql.add_text(' (SELECT ''X'' ');

1113:
1114: IF l_group_type = 'STATIC' THEN
1115:
1116: fnd_dsql.add_text(' AND EXISTS ' );
1117: fnd_dsql.add_text(' (SELECT ''X'' ');
1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );
1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');
1121: fnd_dsql.add_bind(p_person_id_grp);

Line 1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );

1114: IF l_group_type = 'STATIC' THEN
1115:
1116: fnd_dsql.add_text(' AND EXISTS ' );
1117: fnd_dsql.add_text(' (SELECT ''X'' ');
1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );
1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');
1121: fnd_dsql.add_bind(p_person_id_grp);
1122: fnd_dsql.add_text(' AND (a.end_date IS NULL OR a.end_date > sysdate) ');

Line 1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );

1115:
1116: fnd_dsql.add_text(' AND EXISTS ' );
1117: fnd_dsql.add_text(' (SELECT ''X'' ');
1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );
1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');
1121: fnd_dsql.add_bind(p_person_id_grp);
1122: fnd_dsql.add_text(' AND (a.end_date IS NULL OR a.end_date > sysdate) ');
1123: fnd_dsql.add_text( ')');

Line 1120: fnd_dsql.add_text(' AND a.group_id = ');

1116: fnd_dsql.add_text(' AND EXISTS ' );
1117: fnd_dsql.add_text(' (SELECT ''X'' ');
1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );
1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');
1121: fnd_dsql.add_bind(p_person_id_grp);
1122: fnd_dsql.add_text(' AND (a.end_date IS NULL OR a.end_date > sysdate) ');
1123: fnd_dsql.add_text( ')');
1124:

Line 1121: fnd_dsql.add_bind(p_person_id_grp);

1117: fnd_dsql.add_text(' (SELECT ''X'' ');
1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );
1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');
1121: fnd_dsql.add_bind(p_person_id_grp);
1122: fnd_dsql.add_text(' AND (a.end_date IS NULL OR a.end_date > sysdate) ');
1123: fnd_dsql.add_text( ')');
1124:
1125: ELSE

Line 1122: fnd_dsql.add_text(' AND (a.end_date IS NULL OR a.end_date > sysdate) ');

1118: fnd_dsql.add_text(' FROM igs_pe_prsid_grp_mem_all a ' );
1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');
1121: fnd_dsql.add_bind(p_person_id_grp);
1122: fnd_dsql.add_text(' AND (a.end_date IS NULL OR a.end_date > sysdate) ');
1123: fnd_dsql.add_text( ')');
1124:
1125: ELSE
1126: -- Use library to get dynamic person id group members

Line 1123: fnd_dsql.add_text( ')');

1119: fnd_dsql.add_text(' WHERE a.person_id = spa.person_id ' );
1120: fnd_dsql.add_text(' AND a.group_id = ');
1121: fnd_dsql.add_bind(p_person_id_grp);
1122: fnd_dsql.add_text(' AND (a.end_date IS NULL OR a.end_date > sysdate) ');
1123: fnd_dsql.add_text( ')');
1124:
1125: ELSE
1126: -- Use library to get dynamic person id group members
1127: l_prs_grp_sql := IGS_PE_DYNAMIC_PERSID_GROUP.IGS_GET_DYNAMIC_SQL(p_person_id_grp, l_prs_grp_status);

Line 1138: fnd_dsql.add_text( 'AND spa.person_id IN (');

1134: retcode := '2';
1135: RETURN;
1136: END IF;
1137:
1138: fnd_dsql.add_text( 'AND spa.person_id IN (');
1139: fnd_dsql.add_text(l_prs_grp_sql);
1140: fnd_dsql.add_text( ')');
1141:
1142: END IF; -- Static / Dynamic

Line 1139: fnd_dsql.add_text(l_prs_grp_sql);

1135: RETURN;
1136: END IF;
1137:
1138: fnd_dsql.add_text( 'AND spa.person_id IN (');
1139: fnd_dsql.add_text(l_prs_grp_sql);
1140: fnd_dsql.add_text( ')');
1141:
1142: END IF; -- Static / Dynamic
1143:

Line 1140: fnd_dsql.add_text( ')');

1136: END IF;
1137:
1138: fnd_dsql.add_text( 'AND spa.person_id IN (');
1139: fnd_dsql.add_text(l_prs_grp_sql);
1140: fnd_dsql.add_text( ')');
1141:
1142: END IF; -- Static / Dynamic
1143:
1144: END IF; -- Person ID Group Criteria

Line 1147: fnd_dsql.add_text(' AND spa.commencement_dt < ' );

1143:
1144: END IF; -- Person ID Group Criteria
1145:
1146: -- Finish constructing SPA Selection SQL statement WHERE CLAUSE
1147: fnd_dsql.add_text(' AND spa.commencement_dt < ' );
1148: fnd_dsql.add_bind(p_fte_end_dt);
1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );
1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');

Line 1148: fnd_dsql.add_bind(p_fte_end_dt);

1144: END IF; -- Person ID Group Criteria
1145:
1146: -- Finish constructing SPA Selection SQL statement WHERE CLAUSE
1147: fnd_dsql.add_text(' AND spa.commencement_dt < ' );
1148: fnd_dsql.add_bind(p_fte_end_dt);
1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );
1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );

Line 1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );

1145:
1146: -- Finish constructing SPA Selection SQL statement WHERE CLAUSE
1147: fnd_dsql.add_text(' AND spa.commencement_dt < ' );
1148: fnd_dsql.add_bind(p_fte_end_dt);
1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );
1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);

Line 1150: fnd_dsql.add_bind(p_fte_start_dt);

1146: -- Finish constructing SPA Selection SQL statement WHERE CLAUSE
1147: fnd_dsql.add_text(' AND spa.commencement_dt < ' );
1148: fnd_dsql.add_bind(p_fte_end_dt);
1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );
1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);
1154: fnd_dsql.add_text( ')');

Line 1151: fnd_dsql.add_text( ')');

1147: fnd_dsql.add_text(' AND spa.commencement_dt < ' );
1148: fnd_dsql.add_bind(p_fte_end_dt);
1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );
1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);
1154: fnd_dsql.add_text( ')');
1155: fnd_dsql.add_text(' AND spa.course_attempt_status IN ');

Line 1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );

1148: fnd_dsql.add_bind(p_fte_end_dt);
1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );
1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);
1154: fnd_dsql.add_text( ')');
1155: fnd_dsql.add_text(' AND spa.course_attempt_status IN ');
1156: fnd_dsql.add_text(' (''ENROLLED'',''DISCONTIN'',''COMPLETED'',''INTERMIT'',''INACTIVE'',''LAPSED'')');

Line 1153: fnd_dsql.add_bind(p_fte_start_dt);

1149: fnd_dsql.add_text(' AND (spa.discontinued_dt IS NULL OR spa.discontinued_dt > ' );
1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);
1154: fnd_dsql.add_text( ')');
1155: fnd_dsql.add_text(' AND spa.course_attempt_status IN ');
1156: fnd_dsql.add_text(' (''ENROLLED'',''DISCONTIN'',''COMPLETED'',''INTERMIT'',''INACTIVE'',''LAPSED'')');
1157:

Line 1154: fnd_dsql.add_text( ')');

1150: fnd_dsql.add_bind(p_fte_start_dt);
1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);
1154: fnd_dsql.add_text( ')');
1155: fnd_dsql.add_text(' AND spa.course_attempt_status IN ');
1156: fnd_dsql.add_text(' (''ENROLLED'',''DISCONTIN'',''COMPLETED'',''INTERMIT'',''INACTIVE'',''LAPSED'')');
1157:
1158: -- If Selection dates from and to are specified append additional condition to WHERE clause

Line 1155: fnd_dsql.add_text(' AND spa.course_attempt_status IN ');

1151: fnd_dsql.add_text( ')');
1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);
1154: fnd_dsql.add_text( ')');
1155: fnd_dsql.add_text(' AND spa.course_attempt_status IN ');
1156: fnd_dsql.add_text(' (''ENROLLED'',''DISCONTIN'',''COMPLETED'',''INTERMIT'',''INACTIVE'',''LAPSED'')');
1157:
1158: -- If Selection dates from and to are specified append additional condition to WHERE clause
1159: IF l_selection_dt_from IS NOT NULL THEN

Line 1156: fnd_dsql.add_text(' (''ENROLLED'',''DISCONTIN'',''COMPLETED'',''INTERMIT'',''INACTIVE'',''LAPSED'')');

1152: fnd_dsql.add_text(' AND (spa.course_rqrmnts_complete_dt IS NULL OR spa.course_rqrmnts_complete_dt > ' );
1153: fnd_dsql.add_bind(p_fte_start_dt);
1154: fnd_dsql.add_text( ')');
1155: fnd_dsql.add_text(' AND spa.course_attempt_status IN ');
1156: fnd_dsql.add_text(' (''ENROLLED'',''DISCONTIN'',''COMPLETED'',''INTERMIT'',''INACTIVE'',''LAPSED'')');
1157:
1158: -- If Selection dates from and to are specified append additional condition to WHERE clause
1159: IF l_selection_dt_from IS NOT NULL THEN
1160:

Line 1161: fnd_dsql.add_text(' AND EXISTS (');

1157:
1158: -- If Selection dates from and to are specified append additional condition to WHERE clause
1159: IF l_selection_dt_from IS NOT NULL THEN
1160:
1161: fnd_dsql.add_text(' AND EXISTS (');
1162: fnd_dsql.add_text(' SELECT b.person_id, b.course_cd');
1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');
1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');

Line 1162: fnd_dsql.add_text(' SELECT b.person_id, b.course_cd');

1158: -- If Selection dates from and to are specified append additional condition to WHERE clause
1159: IF l_selection_dt_from IS NOT NULL THEN
1160:
1161: fnd_dsql.add_text(' AND EXISTS (');
1162: fnd_dsql.add_text(' SELECT b.person_id, b.course_cd');
1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');
1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');

Line 1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');

1159: IF l_selection_dt_from IS NOT NULL THEN
1160:
1161: fnd_dsql.add_text(' AND EXISTS (');
1162: fnd_dsql.add_text(' SELECT b.person_id, b.course_cd');
1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');
1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);

Line 1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');

1160:
1161: fnd_dsql.add_text(' AND EXISTS (');
1162: fnd_dsql.add_text(' SELECT b.person_id, b.course_cd');
1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');
1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);
1168: fnd_dsql.add_text( ' AND ');

Line 1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');

1161: fnd_dsql.add_text(' AND EXISTS (');
1162: fnd_dsql.add_text(' SELECT b.person_id, b.course_cd');
1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');
1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);
1168: fnd_dsql.add_text( ' AND ');
1169: fnd_dsql.add_bind(l_selection_dt_to);

Line 1166: fnd_dsql.add_text(' AND b.selection_dt between ');

1162: fnd_dsql.add_text(' SELECT b.person_id, b.course_cd');
1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');
1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);
1168: fnd_dsql.add_text( ' AND ');
1169: fnd_dsql.add_bind(l_selection_dt_to);
1170: fnd_dsql.add_text( ')');

Line 1167: fnd_dsql.add_bind(l_selection_dt_from);

1163: fnd_dsql.add_text(' FROM igs_as_su_setatmpt b ');
1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);
1168: fnd_dsql.add_text( ' AND ');
1169: fnd_dsql.add_bind(l_selection_dt_to);
1170: fnd_dsql.add_text( ')');
1171:

Line 1168: fnd_dsql.add_text( ' AND ');

1164: fnd_dsql.add_text(' WHERE b.person_id = spa.person_id ');
1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);
1168: fnd_dsql.add_text( ' AND ');
1169: fnd_dsql.add_bind(l_selection_dt_to);
1170: fnd_dsql.add_text( ')');
1171:
1172: END IF;

Line 1169: fnd_dsql.add_bind(l_selection_dt_to);

1165: fnd_dsql.add_text(' AND b.course_cd = spa.course_cd ');
1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);
1168: fnd_dsql.add_text( ' AND ');
1169: fnd_dsql.add_bind(l_selection_dt_to);
1170: fnd_dsql.add_text( ')');
1171:
1172: END IF;
1173:

Line 1170: fnd_dsql.add_text( ')');

1166: fnd_dsql.add_text(' AND b.selection_dt between ');
1167: fnd_dsql.add_bind(l_selection_dt_from);
1168: fnd_dsql.add_text( ' AND ');
1169: fnd_dsql.add_bind(l_selection_dt_to);
1170: fnd_dsql.add_text( ')');
1171:
1172: END IF;
1173:
1174: -- Finish constructing SPA Selection SQL statement with ORDER BY

Line 1175: fnd_dsql.add_text(' ORDER BY spa.person_id, hspa.student_inst_number, discontinued_dt DESC,');

1171:
1172: END IF;
1173:
1174: -- Finish constructing SPA Selection SQL statement with ORDER BY
1175: fnd_dsql.add_text(' ORDER BY spa.person_id, hspa.student_inst_number, discontinued_dt DESC,');
1176: fnd_dsql.add_text(' course_rqrmnts_complete_dt DESC, spa.commencement_dt DESC');
1177:
1178: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1179: fnd_dsql.set_cursor(l_cursor_id);

Line 1176: fnd_dsql.add_text(' course_rqrmnts_complete_dt DESC, spa.commencement_dt DESC');

1172: END IF;
1173:
1174: -- Finish constructing SPA Selection SQL statement with ORDER BY
1175: fnd_dsql.add_text(' ORDER BY spa.person_id, hspa.student_inst_number, discontinued_dt DESC,');
1176: fnd_dsql.add_text(' course_rqrmnts_complete_dt DESC, spa.commencement_dt DESC');
1177:
1178: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1179: fnd_dsql.set_cursor(l_cursor_id);
1180:

Line 1179: fnd_dsql.set_cursor(l_cursor_id);

1175: fnd_dsql.add_text(' ORDER BY spa.person_id, hspa.student_inst_number, discontinued_dt DESC,');
1176: fnd_dsql.add_text(' course_rqrmnts_complete_dt DESC, spa.commencement_dt DESC');
1177:
1178: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1179: fnd_dsql.set_cursor(l_cursor_id);
1180:
1181: l_fte_calc_sql := fnd_dsql.get_text(FALSE);
1182:
1183: DBMS_SQL.PARSE(l_cursor_id, l_fte_calc_sql, DBMS_SQL.NATIVE);

Line 1181: l_fte_calc_sql := fnd_dsql.get_text(FALSE);

1177:
1178: l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1179: fnd_dsql.set_cursor(l_cursor_id);
1180:
1181: l_fte_calc_sql := fnd_dsql.get_text(FALSE);
1182:
1183: DBMS_SQL.PARSE(l_cursor_id, l_fte_calc_sql, DBMS_SQL.NATIVE);
1184: fnd_dsql.do_binds;
1185:

Line 1184: fnd_dsql.do_binds;

1180:
1181: l_fte_calc_sql := fnd_dsql.get_text(FALSE);
1182:
1183: DBMS_SQL.PARSE(l_cursor_id, l_fte_calc_sql, DBMS_SQL.NATIVE);
1184: fnd_dsql.do_binds;
1185:
1186: DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, c_sca_rec.person_number,30);
1187: DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, c_sca_rec.person_id);
1188: DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 3, c_sca_rec.course_cd, 6);