Pagination not working using a custom SQL query with a custom type

Hi guys. I’ve been struggling with this problem for 2 days now. I’ve read dozens of posts but can’t get to the solution.

Note: all the var names are in spanish since this is a spanish website.

I’ve created a custom type named “promocion”, but when listing the archives when I try to go to page 2 I get a 404 error.

The structure I’d like to set up is the following:

domain.com/promocion/new-promocion -> this works well
domain.com/promociones -> list of all the promociones, this works well too
domain.com/promociones/page/2 -> Error 404 – Not Found

Name of the archive file in my template: archive-promocion.php
Name of the single page view in my template: single-promocion.php

WordPress version: 3.1
Plugins:
-wp-page-navi
-Posts 2 Posts plugin (http://wordpress.org/extend/plugins/posts-to-posts/), used to create a relation between posts and promociones.

Here’s the custom type created in functions.php

register_post_type('promocion', array(
	'label' => 'Promociones',
	'description' => 'Promociones',
	'public' => true,
	'show_ui' => true,
	'show_in_menu' => true,
	'capability_type' => 'post',
	'hierarchical' => false,
	'rewrite' => array('slug' => 'promocion'),
	'query_var' => true,
	'has_archive' => 'promociones',
	'menu_position' => 4,
	'supports' => array('title','editor',),'labels' => array (
	  'name' => 'promociones',
	  'singular_name' => 'promocion',
	  'menu_name' => 'Promociones',
	  'add_new' => 'Añadir nueva',
	  'add_new_item' => 'Añadir nueva',
	  'edit' => 'Editar',
	  'edit_item' => 'Editar Promoción',
	  'new_item' => 'Nueva Promoción',
	  'view' => 'Ver Promoción',
	  'view_item' => 'Ver Promoción',
	  'search_items' => 'Buscar Promociones',
	  'not_found' => 'No se encontraron promociones',
	  'not_found_in_trash' => 'No se encontraron promociones en la papelera',
	  'parent' => 'Parent Promoción',
),) );

Relations created with p2p plugin also in functions.php

function my_connection_types() {
	if ( !function_exists( 'p2p_register_connection_type' ) )
		return;

	p2p_register_connection_type( array(
		'from' => 'promocion',
		'to' => 'post',
		'reciprocal' => true
	) );
}
add_action( 'init', 'my_connection_types', 100 );

And here’s the beginning of my archive page (archive-promocion.php) where I do a custom SQL query and set the pagination:

if ( $cat != '' ) {
		$cat_filter = 'wp_term_taxonomy.term_id =  "' . $cat . '" AND';
	} else {
		$cat_filter = '';
	}

 $querystr = '
    SELECT DISTINCT
		promociones.ID,
		promociones.post_title
		FROM
		wp_terms
		Inner Join wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
		Inner Join wp_term_relationships AS wpr ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
		Inner Join wp_posts AS comercios ON comercios.ID = wpr.object_id
		Inner Join wp_p2p ON wp_p2p.p2p_to = comercios.ID
		Inner Join wp_posts AS promociones ON promociones.ID = wp_p2p.p2p_from
		WHERE
		wp_term_taxonomy.taxonomy =  "category" AND
		comercios.post_type =  "post" AND
		' . $cat_filter . '
		promociones.post_type =  "promocion"
		ORDER BY
		promociones.menu_order ASC
 ';
	$totalposts = $wpdb->get_results($querystr, OBJECT);
	$ppp = 2;
	$wp_query->found_posts = count($totalposts);
	$wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp);
	$on_page = intval(get_query_var('paged'));

	if($on_page == 0){ $on_page = 1; }
	$offset = ($on_page-1) * $ppp;
	$wp_query->request = $querystr . " LIMIT $ppp OFFSET $offset";
	$pageposts = $wpdb->get_results($wp_query->request, OBJECT);

.htaccess file

# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /sitiodeloschicos/
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /sitiodeloschicos/index.php [L]
</IfModule>

# END WordPress

Please help me, I’m going insane here and I’m already late with this project. Thanks!