SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_mpay_bordereaux_find_by_id]
	@bordereau_id		int,
	@return_details		bit
as

	set nocount on;

	/*
	DGE 14/01/2017
	
	*/
	

	-- acc_mpay_bordereaux
	select	b.*
	from	acc_mpay_bordereaux b
	where	b.mpayb_bordereau_id = @bordereau_id
	

	if @return_details != 0
	begin
		-- liste des quitances associées (sélection affichée 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, a.a_branch_id, a.a_currency_code,
				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, d.mpaybd_monthly_payment_status_code, ps.pols_policy_id, a.a_actor_id, pa.polacc_account_number, vact.adr_complete_name
		from	acc_mpay_bordereaux_details d
				inner join acc_invoices i on i.inv_invoice_id = d.mpaybd_invoice_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 acc_accounts a				on	a.a_account_id = i.inv_invoice_id

				inner join vw_act_actors_adresses vact	on	vact.act_actor_id = a.a_actor_id
		where	d.mpaybd_bordereau_id = @bordereau_id

		-- liste des banques des clients
		select	*
		from	vw_act_banks v
				inner join acc_mpay_bordereaux_banks b on b.mpaybb_bank_id = v.act_actor_id
		where	b.mpaybb_bordereau_id = @bordereau_id
	end




GO
