92 lines
5.8 KiB
Transact-SQL
92 lines
5.8 KiB
Transact-SQL
USE [ArizonaREP]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[aps_Get_Authorization_Data] Script Date: 18.09.2025 15:54:41 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER OFF
|
||
GO
|
||
|
||
CREATE OR ALTER PROCEDURE [dbo].[aps_Get_Authorization_Data]
|
||
@in_Authorization_GUID uniqueidentifier = null,
|
||
@in_Form_number int = null
|
||
AS
|
||
/*===========================================================================
|
||
Recherche par numero ou par ID des donnees d'une autorisation.
|
||
|
||
Types de traitements:
|
||
|
||
Parametres :
|
||
@in_Authorization_GUID = guid de l'autorisation cherchee
|
||
@in_Form_number = numero de formulaire de l'autorisation cherchee
|
||
|
||
Creation: 30.07.08 / florent
|
||
|
||
Modifications :
|
||
============================================================================*/
|
||
declare @result_sp int,
|
||
@errno int,
|
||
@errmsg varchar(255)
|
||
|
||
set nocount on
|
||
|
||
/*----------------- Declaration des variables -------------------*/
|
||
declare @param_Authorization_GUID uniqueidentifier,
|
||
@param_Form_number int
|
||
|
||
/*--------- Affectation des parametres aux variables ------------*/
|
||
select @param_Authorization_GUID = @in_Authorization_GUID,
|
||
@param_Form_number = @in_Form_number
|
||
|
||
/*----------------- Test des parametres input -------------------*/
|
||
if ( @param_Authorization_GUID is null
|
||
and @param_Form_number is null)
|
||
or ( @param_Authorization_GUID is not null
|
||
and @param_Form_number is not null)
|
||
begin
|
||
select @errno = 70003,
|
||
@errmsg = '(APS) Invalid input parameters !'
|
||
goto error_99
|
||
end
|
||
|
||
/* filtering via tables */
|
||
declare @filtForm table(Form_number int null)
|
||
declare @filtAuth table(Authorization_GUID uniqueidentifier null)
|
||
|
||
if @param_Authorization_GUID is not null
|
||
insert into @filtAuth(Authorization_GUID)
|
||
values( @in_Authorization_GUID);
|
||
|
||
if @param_Form_number is not null
|
||
insert into @filtForm(Form_number)
|
||
values(@in_Form_number)
|
||
|
||
/*----------------------- Select final -------------------------*/
|
||
select apsau.Aps_authorization_GUID,
|
||
apsau.APSAU_OU,
|
||
apsau.APSAU_master_address,
|
||
apsau.APSAU_type,
|
||
apsau.APSAU_form_number,
|
||
apsau.APSAU_unlock_code,
|
||
dbo.aps_fn_Get_Master_Address(apsau.APSAU_Master_address) as 'Calc_master_address',
|
||
dbo.aps_fn_Get_Authorization_Last_Status(apsau.APS_Authorization_GUID, 1) as 'Calc_authorization_last_status'
|
||
from Aps_authorization apsau with (nolock)
|
||
where exists(
|
||
select 1
|
||
from @filtAuth fa
|
||
where fa.Authorization_GUID = apsau.Aps_authorization_GUID
|
||
)
|
||
or exists(
|
||
select 1
|
||
from @filtForm ff
|
||
where ff.Form_number = apsau.APSAU_form_number
|
||
)
|
||
|
||
return(@@error)
|
||
|
||
/*---------------------- Gestion des erreurs --------------------*/
|
||
error_99:
|
||
raiserror(@errmsg,16,1)
|
||
return(@errno)
|
||
|
||
|