SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_pr_products_introducers_commission_sharings]
	@product_code ais_code_short,
	@branch_id int,
	@introducer_id as varchar(15)
AS

	SET NOCOUNT ON;


	SELECT	a.iret_retro_code, a.iret_l1, a.iret_l2, a.iret_l3 
	FROM	int_retrocessions a
			INNER JOIN	(
						SELECT	p.prdintr_retro_code 
						FROM	pr_products_introducers_commission_sharings p
						WHERE	p.prdintr_product_code = @product_code
						AND		p.prdintr_branch_id = @branch_id
						AND		p.prdintr_introducer_id = @introducer_id

						UNION 

						SELECT	i.prdint_retro_code
						FROM	pr_products_introducers i 
						WHERE	i.prdint_product_code = @product_code
						AND		i.prdint_branch_id = @branch_id
						AND		i.prdint_introducer_id = @introducer_id
						) AS t ON t.prdintr_retro_code = a.iret_retro_code 
	ORDER BY a.iret_retro_code
GO
