1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
| CREATE PROCEDURE [dbo].[Usp_Tie_Tiers_Org_Update]
@TIE_ID int,
@ORG_REF dbo.D_REF_COURTE,
@ORG_NOM_COMMUN NOM_TIERS,
@ORG_NUM_INTRACOM varchar(32),
@TIE_COMPTA_CODE_INDIV varchar(16),
@TIE_COMPTA_CODE_CENTR varchar(16),
@TIE_COMPTA_CLEF varchar(16),
@TIE_COMPTA_SOCIETE varchar(96),
@TIE_ECH_NOMBRE smallint,
@TIE_ECH_JM smallint,
@TIE_ECH_TYPE smallint,
@TIE_REGL_TYPE smallint,
@TIE_IMPR_EXEMP smallint,
@TIE_ESC_TAUX numeric(15, 4),
@TIE_DOMB_ADR varchar(max),
@TIE_RIB_CODE_ETAB varchar(16),
@TIE_RIB_CODE_GUI varchar(16),
@TIE_RIB_NUM_COMPTE varchar(16),
@TIE_RIB_CLEF varchar(16),
@TIE_MEMO varchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@NB INT,
@TRANCOUNT INT,
@ERROR INT,
@ROWCOUNT INT,
@NB_AFF INT,
@NB_COMM INT,
@NB_DEVIS INT,
@NB_FACT INT,
@ORG_REF_OLD dbo.D_REF_COURTE,
@ORG_NOM_COMMUN_OLD NOM_TIERS,
@CURRENT_USER VARCHAR(128);
SELECT
@ORG_REF_OLD = ORG_REF,
@ORG_NOM_COMMUN_OLD = ORG_NOM_COMMUN
FROM dbo.T_ORGANISATION_ORG
WHERE TIE_ID = @TIE_ID;
SET @CURRENT_USER = dbo.Ufn_Sys_User_name();
IF (@ORG_REF_OLD <> @ORG_REF OR @ORG_NOM_COMMUN_OLD <> @ORG_NOM_COMMUN) AND
(@CURRENT_USER NOT IN ('BrunoPetit','Administrateur','JeanMichelImberti'))
BEGIN
SELECT @NB_AFF = NBAFF FROM dbo.V_TIERS_COUNT_AFF WHERE TIE_ID = @TIE_ID;
SELECT @NB_COMM = NBCOMM FROM dbo.V_TIERS_COUNT_COMM WHERE TIE_ID = @TIE_ID;
SELECT @NB_DEVIS = NBDEVIS FROM dbo.V_TIERS_COUNT_DEVIS WHERE TIE_ID = @TIE_ID;
SELECT @NB_FACT = NBFACT FROM dbo.V_TIERS_COUNT_FACT WHERE TIE_ID = @TIE_ID;
IF ISNULL(@NB_AFF,0) > 0 OR
ISNULL(@NB_COMM,0) > 0 OR
ISNULL(@NB_DEVIS,0) > 0 OR
ISNULL(@NB_FACT,0) > 0
BEGIN
RAISERROR( 'Modification impossible de [Ref] ou [Nom commun], le tiers a des documents enregistrés, faites une demande à l''administrateur GPG' , 16, 1);
RETURN(-1);
END
END
BEGIN TRANSACTION
SET @TRANCOUNT = 1;
UPDATE dbo.T_TIERS_TIE
SET
TIE_COMPTA_CODE_INDIV = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_COMPTA_CODE_INDIV),
TIE_COMPTA_CODE_CENTR = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_COMPTA_CODE_CENTR),
TIE_COMPTA_CLEF = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_COMPTA_CLEF),
TIE_COMPTA_SOCIETE = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_COMPTA_SOCIETE),
TIE_ECH_NOMBRE = @TIE_ECH_NOMBRE,
TIE_ECH_JM = @TIE_ECH_JM,
TIE_ECH_TYPE = @TIE_ECH_TYPE,
TIE_REGL_TYPE = @TIE_REGL_TYPE,
TIE_IMPR_EXEMP = @TIE_IMPR_EXEMP,
TIE_ESC_TAUX = @TIE_ESC_TAUX,
TIE_DOMB_ADR = @TIE_DOMB_ADR,
TIE_RIB_CODE_ETAB = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_RIB_CODE_ETAB),
TIE_RIB_CODE_GUI = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_RIB_CODE_GUI),
TIE_RIB_NUM_COMPTE = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_RIB_NUM_COMPTE),
TIE_RIB_CLEF = dbo.Ufn_Gen_StringFilterNullSpace(@TIE_RIB_CLEF),
TIE_MEMO = @TIE_MEMO,
TIE_DAMAJ = Getdate(),
TIE_USEMAJ = dbo.Ufn_Sys_User_name()
WHERE TIE_ID = @TIE_ID;
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT;
IF @ERROR <> 0 OR @ROWCOUNT = 0 GOTO LBL_ERROR;
UPDATE dbo.T_ORGANISATION_ORG
SET
ORG_REF = dbo.Ufn_Gen_StringFilterNullSpace(@ORG_REF),
ORG_NOM_COMMUN = dbo.Ufn_Gen_StringFilterNullSpace(@ORG_NOM_COMMUN),
ORG_NUM_INTRACOM = dbo.Ufn_Gen_StringFilterNullSpace(@ORG_NUM_INTRACOM)
WHERE TIE_ID = @TIE_ID;
SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT;
IF @ERROR <> 0 OR @ROWCOUNT = 0 GOTO LBL_ERROR;
COMMIT TRANSACTION;
RETURN (0);
LBL_ERROR:
IF @@TRANCOUNT > 1
COMMIT TRANSACTION
IF @TRANCOUNT > 0
ROLLBACK TRANSACTION
IF @ROWCOUNT >= 1
ROLLBACK TRANSACTION
RETURN (-1)
END |
Partager