USE [Arizona]
GO
/****** Object:  StoredProcedure [dbo].[aps_Sync_H_PH_Item_Regulation_Info_I3]    Script Date: 28.08.2024 10:36:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[aps_Sync_H_PH_Item_Regulation_Info_I3]
                 @in_source_subsidiary_id int = null,    
                 @in_dest_subsidiary_id int = null,
                 @in_count_new_PHIRI int = null,
                 @in_debug int = null,
                 @out_param_int_1 int = null output
AS
/*===========================================================================

  Synchro horizontale des articles, table des PH_Item_Regulation_Info
  Insert

  Types de traitements: 

    1 = Synchro horizontale articles (insertion all)
        @in_source_subsidiary_id = subsidiary_id source
        @in_dest_subsidiary_id   = subsidiary_id destination
        @in_count_new_PHIRI      = nbre de ligne a inserer

  Creation: 02.09.19 / ceg #TFS54798#

  Modifications :
    xx.xx.xx / xxx :
============================================================================*/

set nocount on

/*------------------ Declaration des variables --------------------*/
        /*--- parametre ---*/
declare @param_source_subsidiary_id          int,
        @param_dest_subsidiary_id            int,
        @param_count_new_PHIRI               int,

        /*--- variable ---*/
        @result_sp                           int,
        @errno                               int,
        @errmsg                              varchar(255),

        /*--- colonne ---*/
        @new_PH_Item_Regulation_Info_id                int

/*--------- Affectation des parametres aux variables --------------*/
select @param_source_subsidiary_id = @in_source_subsidiary_id,
       @param_dest_subsidiary_id   = @in_dest_subsidiary_id,
       @param_count_new_PHIRI      = isnull(@in_count_new_PHIRI,0),

       /* mise a null des variables */
       @out_param_int_1            = 0,
       @result_sp                  = 0

/*------------------- Test des parametres input -------------------*/
if @param_source_subsidiary_id is null                          /* subsidiary_id source*/
or @param_dest_subsidiary_id   is null                          /* subsidiary_id destin.*/
or @param_source_subsidiary_id = @param_dest_subsidiary_id      /* les subsidiary doivent etre differentes */
begin
  select @errno = 70003,
         @errmsg = 'Invalid input parameters ! (aps_Sync_H_PH_Item_Regulation_Info_3)'
  goto error_99
end

if @in_debug = 1
  select 'Début PHIRI-I3', 
         @param_source_subsidiary_id '@param_source_subsidiary_id',
         @param_dest_subsidiary_id   '@param_dest_subsidiary_id',
         @param_count_new_PHIRI      '@param_count_new_PHIRI'

if @param_count_new_PHIRI > 0
begin

  /*------------ reservation plage getnextid pour PHIRI -------------*/
  exec @result_sp = Arizona.dbo.sp_bmc_GetNextID
                    @in_key           = 'PH_Item_Regulation_Info', 
                    @in_id_column     = 'PH_Item_Regulation_Info_id',    
                    @in_nbr_of_record = @param_count_new_PHIRI,
                    @out_id           = @new_PH_Item_Regulation_Info_id output
  if @result_sp <> 0
  begin
    select @errno = 70001,
           @errmsg = 'Error executing IDB_AMA_DEST.Arizona.dbo.sp_bmc_GetNextID ! (PH_Item_Regulation_Info)'
    goto error_99
  end

--if @in_debug = -5
--  begin
--    select 
--        ITK_subsidiary,
--        tphiri.*,
--        '----',
--        @new_PH_Item_Regulation_Info_id + tphiri_id as PH_Item_Regulation_Info_ID,
--         it.Item_id as PHIRI_item,
--         phiri.PHIRI_MD,
--         phiri.PHIRI_SM_B,
--         phiri.PHIRI_SM_B_value,
--         phiri.PHIRI_bulk,
--         phiri.PHIRI_SMVO,
--         phiri.PHIRI_FL,
--         phiri.PHIRI_FM,
--         phiri.PHIRI_web,
--         phiri.PH_Item_Regulation_Info_ID as PHIRI_master_ID
--    from #t_phiri tphiri
--     join PH_Item_Regulation_Info phiri with (nolock)
--      on phiri.PH_item_regulation_info_ID = tphiri.tphiri_phiri_id
--    join item it with (nolock)
--      on it.IT_master_ID = phiri.PHIRI_item
--    join item_key itk with (nolock)
--      on itk.ITK_item = it.Item_id
--     and itk.ITK_Type = 1
--     and itk.ITK_subsidiary = @param_dest_subsidiary_id
--    order by it.item_id
--end

-----TSC merge begin
merge into PH_Item_Regulation_Info t
using (
  select @new_PH_Item_Regulation_Info_id + tphiri_id as PH_Item_Regulation_Info_ID,
         it.Item_id as PHIRI_item,
         phiri.PHIRI_MD,
         phiri.PHIRI_SM_B,
         phiri.PHIRI_SM_B_value,
         phiri.PHIRI_bulk,
         phiri.PHIRI_SMVO,
         phiri.PHIRI_FL,
         phiri.PHIRI_FM,
         phiri.PHIRI_web,
         phiri.PH_Item_Regulation_Info_ID as PHIRI_master_ID
    from #t_phiri tphiri
    join PH_Item_Regulation_Info phiri with (nolock)
      on phiri.PH_item_regulation_info_ID = tphiri.tphiri_phiri_id
    join item it with (nolock)
      on it.IT_master_ID = phiri.PHIRI_item
    join item_key itk with (nolock)
      on itk.ITK_item = it.Item_id
     and itk.ITK_Type = 1
     and itk.ITK_subsidiary = @param_dest_subsidiary_id
) s
on (s.PHIRI_item = t.PHIRI_item)
WHEN MATCHED
    THEN UPDATE SET 
        t.PHIRI_MD = s.PHIRI_MD
        ,t.PHIRI_SM_B = s.PHIRI_SM_B
        ,t.PHIRI_SM_B_value = s.PHIRI_SM_B_value
        ,t.PHIRI_bulk = s.PHIRI_bulk
        ,t.PHIRI_SMVO = s.PHIRI_SMVO
        ,t.PHIRI_FL = s.PHIRI_FL
        ,t.PHIRI_FM = s.PHIRI_FM
        ,t.PHIRI_web = s.PHIRI_web
        ,t.PHIRI_master_ID = s.PHIRI_master_ID
WHEN NOT MATCHED BY TARGET 
    THEN INSERT ( 
         PH_Item_Regulation_Info_ID,
         PHIRI_item,
         PHIRI_MD,
         PHIRI_SM_B,
         PHIRI_SM_B_value,
         PHIRI_bulk,
         PHIRI_SMVO,
         PHIRI_FL,
         PHIRI_FM,
         PHIRI_web,
         PHIRI_master_ID
    )
    values (
        s.PH_Item_Regulation_Info_ID
        ,s.PHIRI_item
        ,s.PHIRI_MD
        ,s.PHIRI_SM_B
        ,s.PHIRI_SM_B_value
        ,s.PHIRI_bulk
        ,s.PHIRI_SMVO
        ,s.PHIRI_FL
        ,s.PHIRI_FM
        ,s.PHIRI_web
        ,s.PHIRI_master_ID
    )
;

-----TSC merge end

  /*------------------ insertion des nouveaux phiri -----------------*/
  --insert PH_Item_Regulation_Info
  --      (PH_Item_Regulation_Info_ID,
  --       PHIRI_item,
  --       PHIRI_MD,
  --       PHIRI_SM_B,
  --       PHIRI_SM_B_value,
  --       PHIRI_bulk,
  --       PHIRI_SMVO,
  --       PHIRI_FL,
  --       PHIRI_FM,
  --       PHIRI_web,
  --       PHIRI_master_ID)
  --select @new_PH_Item_Regulation_Info_id + tphiri_id,
  --       it.Item_id,
  --       phiri.PHIRI_MD,
  --       phiri.PHIRI_SM_B,
  --       phiri.PHIRI_SM_B_value,
  --       phiri.PHIRI_bulk,
  --       phiri.PHIRI_SMVO,
  --       phiri.PHIRI_FL,
  --       phiri.PHIRI_FM,
  --       phiri.PHIRI_web,
  --       phiri.PH_Item_Regulation_Info_ID
  --  from #t_phiri tphiri
  --  join PH_Item_Regulation_Info phiri with (nolock)
  --    on phiri.PH_item_regulation_info_ID = tphiri.tphiri_phiri_id
  --  join item it with (nolock)
  --    on it.IT_master_ID = phiri.PHIRI_item
  --  join item_key itk with (nolock)
  --    on itk.ITK_item = it.Item_id
  --   and itk.ITK_Type = 1
  --   and itk.ITK_subsidiary = @param_dest_subsidiary_id;

  if @in_debug = 1
    select 'PH_Item_Regulation_Info' , @@rowcount

end /* @param_count_new_PHIRI */

return(@@error)

/*------------------ traitement des erreurs -----------------------*/
error_99:
  raiserror (@errmsg, 16, 1)
  return(@errno)