
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  PROCEDURE [dbo].[p_pol_policies_load_data_by_table_id]
    @table_id type_table_id READONLY,
    @return_pol_policies bit,
    @return_pol_policies_situations bit,
    @return_pol_cpl bit,
    @return_pol_policies_situations_introducers bit,
    @return_pol_variables bit,
    @return_pol_variables_taxes bit,
    @return_ris_risks bit,
    @return_ris_risks_situations bit,
    @return_ris_cpl bit,
    @return_ris_variables bit,
    @return_ris_variables_taxes bit
    
AS 
	/*
	DGE
	Chargement des données des contrats à partir de la liste des situations
	*/

	set nocount on
	
	
	----------------------------------------------------------------------------------
	-- partie contrat
	if @return_pol_policies != 0 
		select	p.*
		from	pol_policies p
				inner join pol_policies_situations ps on ps.pols_policy_id = p.pol_policy_id
				inner join @table_id t on t.id = ps.pols_policy_situation_id

	if @return_pol_policies_situations != 0
		select	ps.*
		from	pol_policies_situations ps
				inner join @table_id t on t.id = ps.pols_policy_situation_id
	
	if @return_pol_cpl != 0	
		select	c.*
		from	pol_cpl c
				inner join @table_id t on t.id = c.pcpl_policy_situation_id 
			
	if @return_pol_policies_situations_introducers != 0 
		select	psi.*
		from	pol_policies_situations_introducers psi
				inner join @table_id t on t.id = psi.polsi_policy_situation_id 
			
	if @return_pol_variables != 0 
		select	pv.*
		from	pol_variables pv
				inner join @table_id t on t.id = pv.polv_policy_situation_id
			
	if @return_pol_variables_taxes != 0
		select	pvt.*
		from	pol_variables_taxes pvt
				inner join @table_id t on t.id = pvt.polvt_policy_situation_id
	----------------------------------------------------------------------------------
	
	
	----------------------------------------------------------------------------------
	-- partie flotte (risques)
	
	if @return_ris_risks != 0
		select	r.*
		from	ris_risks r
				inner join pol_policies p on p.pol_policy_id = r.ris_policy_id 
				inner join pol_policies_situations ps on ps.pols_policy_id = p.pol_policy_id
				inner join @table_id t on t.id = ps.pols_policy_situation_id 
			
	if @return_ris_risks_situations != 0 
		select	rs.*
		from	ris_risks_situations rs
				inner join @table_id t on t.id = rs.riss_policy_situation_id 
			
	if @return_ris_cpl != 0
		select	c.*
		from	ris_cpl c 
				inner join ris_risks_situations rs on rs.riss_risk_situation_id = c.rcpl_risk_situation_id 
				inner join @table_id t on t.id = rs.riss_policy_situation_id 
	
	if @return_ris_variables != 0
		select	v.*
		from	ris_variables v
				inner join ris_risks_situations rs on rs.riss_risk_situation_id = v.risv_risk_situation_id 
				inner join @table_id t on t.id = rs.riss_policy_situation_id 
			
	if @return_ris_variables_taxes != 0
		select	vt.*
		from	ris_variables_taxes vt
				inner join ris_risks_situations rs on rs.riss_risk_situation_id = vt.risvt_risk_situation_id 
				inner join @table_id t on t.id = rs.riss_policy_situation_id 
	----------------------------------------------------------------------------------
			
	
			
	

GO
