SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_detail]
	@policy_situation_id int,
	@return_complement bit = 1,
	@return_variables bit = 1,
	@return_client bit = 1,
	@return_introducers bit = 1,
	@return_invoice_variables bit = 0
as 
	set nocount on;
	
	select p.*, c.cie_treaty
	from pol_policies p
		inner join pol_policies_situations s on	pol_policy_id = pols_policy_id 
		inner join act_cies c on c.cie_actor_id = p.pol_company_id
	where @policy_situation_id > 0 and pols_policy_situation_id = @policy_situation_id -- conditia @situation_id = 0 este necesare ca sa faca scurtcircuit cand dorim doar structure tabelei
	
	select *
	from pol_policies_situations
	where @policy_situation_id > 0 and pols_policy_situation_id = @policy_situation_id 

	select	*
	from	pol_policies_situations_repartitions
	where	@policy_situation_id > 0 and polsr_policy_situation_id = @policy_situation_id

	select	ppa.*
	from	pol_policies_accounts ppa
		inner join pol_policies pp on pp.pol_policy_id = ppa.polacc_policy_id
		inner join pol_policies_situations pps on pp.pol_policy_id = pps.pols_policy_id 
	where	@policy_situation_id > 0 and pps.pols_policy_situation_id = @policy_situation_id
						
	if @return_complement = 1
	begin
		select *
		from pol_cpl 
		where @policy_situation_id > 0 and pcpl_policy_situation_id = @policy_situation_id
	end 

	if @return_variables = 1
	begin
		select v.*, n.*, p.prdv_product_code, p.prdv_variable_code, p.prdv_variable_date_setup
		from pol_variables v
			inner join pr_products_variables p on v.polv_variable_id = prdv_variable_id
			inner join nm_products_variables n on prdv_variable_code = nmpv_variable_code
		where @policy_situation_id > 0 and polv_policy_situation_id = @policy_situation_id
		
		select	v.*, a.*, n.*, p.prdv_product_code, p.prdv_variable_code, p.prdv_variable_date_setup
		from pol_variables_taxes v
			inner join pr_products_variables p on v.polvt_variable_id = p.prdv_variable_id 
			inner join acc_taxes a on	acctax_tax_id = polvt_tax_id
			inner join nm_taxes n on acctax_tax_code = nmtax_tax_code
		where @policy_situation_id > 0 and polvt_policy_situation_id = @policy_situation_id
												 	 
	end
	
	if @return_client = 1
	begin
		select cli.*
		from pol_policies p
			inner join pol_policies_situations s on	pol_policy_id = pols_policy_id
			inner join vw_act_clients cli on cli_actor_id = pol_client_id
		where @policy_situation_id > 0 and pols_policy_situation_id = @policy_situation_id
		
	end
	
	if @return_introducers = 1
	begin
		select ppsi.*, wai.adr_complete_name, wai.act_internal_reference
		from pol_policies_situations_introducers ppsi
			inner join vw_act_introducers wai on ppsi.polsi_introducer_id = wai.int_actor_id
		where @policy_situation_id > 0 and polsi_policy_situation_id = @policy_situation_id
	end

	if @return_invoice_variables = 1
	BEGIN
		select	aipv.*,
				npv.*
		from acc_invoices_policies_variables aipv
			inner join pr_products_variables ppv on invpolv_variable_id = prdv_variable_id
			inner join nm_products_variables npv on prdv_variable_code = nmpv_variable_code
		where @policy_situation_id > 0 and invpolv_policy_situation_id = @policy_situation_id 
		
		select aipvct.*
		from acc_invoices_policies_variables_commission_taxes aipvct
		where @policy_situation_id > 0 and invpolvct_policy_situation_id = @policy_situation_id 
		 
		select aipvt.*
		from acc_invoices_policies_variables_taxes aipvt
		where @policy_situation_id > 0 and invpolvt_policy_situation_id = @policy_situation_id 

		select ai.*
		from acc_invoices ai
		where @policy_situation_id > 0 and ai.inv_policy_situation_id = @policy_situation_id 

		select aa.*
		from acc_invoices ai
			inner join acc_accounts aa on ai.inv_invoice_id = aa.a_account_id
		where @policy_situation_id > 0 and ai.inv_policy_situation_id = @policy_situation_id 

	end

	select	r.* 
	from	pol_policies_situations_repartitions r
	WHERE	@policy_situation_id > 0 and r.polsr_policy_situation_id = @policy_situation_id 
GO
