while (my @people = $sth->fetchrow_array()) {
		my $person = join ",", @people;
		push @certified_people, $person;
	}

	$sth->finish;
	
	$sth = $dbh->prepare('SELECT TEMP_CLICK_ID.NEXTVAL FROM DUAL')
	or dienice("199 Couldn't prepare statement: " . $dbh->errstr);

	$sth->execute()
	or dienice("202 Couldn't execute statement: " . $sth->errstr);

	while ( my @results = $sth->fetchrow_array()) {$click_id = $results[0]; chomp $click_id;}

	$sth->finish;

	print qq!$javascript_to_select_names_screen_two!;
	
	foreach my $certified_person (@certified_people) {
		my ($fname,$lname,$sc_id) = split ",", $certified_person;
		
		## Prepare the SQL to get the conflicts
		# What we do is test to see if either the start or end of any appointment falls between the start
		# and end of the proposed appointment.
		my $CONFLICT_STATEMENT = q!SELECT SC_BASE_APPT.CALL_ID, SC_BASE_APPT.STATUS
									FROM SC_BASE_APPT , SC_BASE_INFO
									WHERE ( (SC_BASE_INFO.TRANS_ID = SC_BASE_APPT.TRANS_ID) )
									AND SC_BASE_APPT.SC_ID = ? AND
									((to_number(SC_BASE_INFO.AP_START) <= ? AND to_number(SC_BASE_INFO.AP_END) >= ?)
									OR
									(to_number(SC_BASE_INFO.AP_START) <= ? AND to_number(SC_BASE_INFO.AP_END) >= ?))
								!;

		my $sth = $dbh->prepare($CONFLICT_STATEMENT)
		or dienice("230 Couldn't prepare statement: $CONFLICT_STATEMENT" . $dbh->errstr);
		
		# now concatenate the values into start and end times
		my $start_time   = sprintf"%02d%02d%02d%02d%02d", $SMONTH,$SDAY,$SYEAR,$SHOUR,$SMINUTE;
		my $start_day    = sprintf"%02d%02d%02d",         $SMONTH,$SDAY,$SYEAR;

		my $end_time     = sprintf"%02d%02d%02d%02d%02d", $EMONTH,$EDAY,$EYEAR,$EHOUR,$EMINUTE;
		my $end_day      = sprintf"%02d%02d%02d",         $EMONTH,$EDAY,$EYEAR;

		$sth->execute($sc_id,$start_time,$start_time,$end_time,$end_time)
		or dienice("241 Couldn't execute statement: " . $sth->errstr);
		
		if ( $sth->fetchrow_array() ) { next; }

		$sth->finish;
		
		# if we get this far, put in temp records
		my ($trans_id, $call_id);									 #, @returns);				
		my $appt_classes_ref	= \%appt_classes;
		
		#@returns = makeTempRec($click_id, $sc_id, $start_time, $end_time, $appt_classes_ref);
		
		#($trans_id, $call_id) = @returns;
		
		{
			my ($status, @cols, @vals);

			# connect to database
			my $dbh = DBI->connect( $CONNECT, $USERNAME, $PASSWORD )
			or dienice("336 Cant connect to Oracle database: $DBI::errstr\n");

			my $getTransId = $dbh->prepare('SELECT MAKE_APPT.NEXTVAL FROM DUAL')
			or dienice("251 Couldn't prepare statement: " . $dbh->errstr);

			$getTransId->execute()
			or dienice("254 Couldn't execute statement: " . $getTransId->errstr);

			while ( my @results = $getTransId->fetchrow_array()) { $trans_id = $results[0]; }

			$getTransId->finish;

			my $getCallId = $dbh->prepare('SELECT CALL_ID.NEXTVAL FROM DUAL')
			or dienice("341 Couldn't prepare statement: " . $dbh->errstr);

			$getCallId->execute()
			or dienice("344 Couldn't execute statement: " . $getCallId->errstr);

			while ( my @results = $getCallId->fetchrow_array()) { $call_id = $results[0]; }

			$getCallId->finish;

			$status = "SCHEDULING";

			# you need to insert temp records in the different appt tables
			my $TRANS_ID_TEMP_RECORD = qq!
				INSERT INTO SC_TRANS_ID ( TRANS_ID, CLICK_ID ) VALUES ( $trans_id, $click_id )
			!;

			my $BASE_INFO_TEMP_RECORD = qq!
				INSERT INTO SC_BASE_INFO
				( TRANS_ID, CONTACT, REP_EMAIL, ADD_ST, ADD_CITY, ADD_STATE, ADD_ZIP, ADD_FLOOR, CONT_PHN,
				AP_START, AP_END, COMPANY, CON_EMAIL )
				VALUES ( $trans_id, 'contact name', 'email here', 'Warrenville Rd.', 'Naperville', 'IL', 999999999, 'floor', 8003069329,
				'$start_time', '$end_time', 'placeholder', 'jsander\@quest.com' )
			!;

			my $BASE_APPT_TEMP_RECORD = qq!
				INSERT INTO SC_BASE_APPT
				( TRANS_ID, CALL_ID, SC_ID, STATUS )
				VALUES ( $trans_id, $call_id, $sc_id, '$status' )
			!;

			# the APPT_CLASSES record must be built
			push @cols, "CALL_ID";
			push @vals, "$call_id";

			foreach my $key ( keys %{$appt_classes_ref} ) {
					push @cols, $key;
					if (not ref $appt_classes_ref->{$key}) {
							push @vals, $appt_classes_ref->{$key};
					} else {
							push @vals, scalar( join ",", @{$appt_classes_ref->{$key}} );
					}
			}

			my $CLASSES_RECORD;

			$CLASSES_RECORD = 'INSERT INTO SC_APPT_CLASSES (' ;
			$CLASSES_RECORD .= (join ",", @cols) ;
			$CLASSES_RECORD .= ') VALUES (' ;
			$CLASSES_RECORD .= (join ",", map {if ($_ !~ /^\d+$/) {sprintf "'%s'", $_; } else { sprintf "$_"; } } @vals) ;
			$CLASSES_RECORD .= ')';

			$dbh->do("$TRANS_ID_TEMP_RECORD")
			or dienice("320 Couldn't execute statement: $TRANS_ID_TEMP_RECORD" . $dbh->errstr);

			$dbh->do("$BASE_INFO_TEMP_RECORD")
			or dienice("320 Couldn't execute statement: $BASE_INFO_TEMP_RECORD" . $dbh->errstr);

			$dbh->do("$BASE_APPT_TEMP_RECORD")
			or dienice("320 Couldn't execute statement: $BASE_APPT_TEMP_RECORD" . $dbh->errstr);

			$dbh->do("$CLASSES_RECORD")
			or dienice("320 Couldn't execute statement: $CLASSES_RECORD" . $dbh->errstr);
			

			#my @returns = ($trans_id, $call_id);
			#return @returns;
		}
		
		push @temp_call_ids, $call_id;
		push @temp_trans_ids, $trans_id;
		
		print qq!<OPTION VALUE="$sc_id,$fname,$lname">$fname $lname</OPTION>\n!;
		$dummies++;

	}
	dienice("defined @temp_call_ids and @temp_trans_ids");