SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_mpay_bordereaux_select]
	@branch_id			int,					
	@bank_id			int,
	@currency_code		ais_code_short,
	@date_from			date, 
	@date_to			date, 
	@table_banks		type_table_id READONLY		-- liste de banques des clients
as

	set nocount on;


	/*
	DGE 12/01/2017

	Sélectionne les quittances de mensualisation TOCOLL & REPROG à inclure dans un nouveau bordereau.

	Tous les statuts de prélèvement possibles:
	select * from nm_invoices_nm_monthly_payments_statuses

	Le résultat sera affiché dans la grille grid_acc_mpay_bordereaux_invoices.

	*/

	select	i.inv_invoice_id, ps.pols_number, i.inv_operation_code__last, a.a_operation_utc_date, a.a_amount, 
			i.inv_start_date, i.inv_end_date, i.inv_monthly_payment_date, i.inv_monthly_payment_reject_count, i.inv_monthly_payment_status_code,
			a.a_account_id, vact.adr_complete_name
	from	acc_accounts a
			inner join acc_invoices i				on	i.inv_invoice_id = a.a_account_id
			inner join pol_policies_situations ps	on	ps.pols_policy_situation_id = i.inv_policy_situation_id
			inner join pol_policies_accounts pa		on	pa.polacc_policy_id = ps.pols_policy_id
			inner join act_accounts	aa				on	aa.acc_actor_id = pa.polacc_client_id
													and	aa.acc_account_number = pa.polacc_account_number

			inner join @table_banks tb				on	tb.id = aa.acc_bank_id

			inner join vw_act_actors_adresses vact	on	vact.act_actor_id = a.a_actor_id

	where	a.a_branch_id = @branch_id
	and		ps.pols_monthly_payments != 0
	--and		aa.acc_bank_id = @bank_id
	and		a.a_currency_code = @currency_code
	and		i.inv_monthly_payment_date between @date_from and @date_to
	and		i.inv_monthly_payment_status_code in ('TOCOLL','REPROG')
	and		i.inv_operation_code__last = 'DB'
	and		not exists	(
						/*
						select * from acc_mpay_bordereaux_statuses
						A	Archive
						AN	Canceled
						C	Confirmed
						CR	Created
						S	Sent
						*/
						select	1
						from	acc_mpay_bordereaux b
								inner join acc_mpay_bordereaux_details bd on bd.mpaybd_bordereau_id = b.mpayb_bordereau_id
						where	mpayb_status_code not in ('AN', 'C')		
						and		bd.mpaybd_invoice_id = i.inv_invoice_id
						)
	
GO
