
/***************************************
* STORED stp_SelectRappQualita
*
* Description:	Ritorna i cartellini da AS400 partendo nal numero del rapporto di qualità
*
* Steamware, S.E.L.
*
****************************************/
CREATE PROCEDURE [As400].[stp_SelectRappQualita] 
	-- Add the parameters for the stored procedure here
	@NumRappQual nVarChar(1000) = ''
AS
BEGIN

    -- Cartellini Rapporti qualità Materia Prima

    -- FILE XOGIX00F
    -- LIBRERIA TEST             MAZZT_DTV3
    -- LIBRERIA EFFETTIVA TEKAL  TEKA_DATV3
    -- LIBRERIA EFFETTIVA POSS.  POSN_DATV3

	SET NOCOUNT ON;

    DECLARE @SQL AS NVARCHAR(4000)
	DECLARE @As400File AS NVARCHAR(50) = '';
    DECLARE @As400Linked AS NVARCHAR(50) = '';  -- Linked Server AS400

    -- DECLARE @PAR1 AS nVarChar(1000)
    -- SET @PAR1 = N'903383'
    
    -- In base al nome del DB e della stored recupero il Linked Server e file di AS400
    SET @As400Linked = ( SELECT dbo.f_getLikedAS400(DB_NAME()) );
    SET @As400File   = ( SELECT dbo.f_getLibreriaAS400 ( DB_NAME() , OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) ) )
   
    -- Se rapporto di qualità è 0 non è un rapporto di qualità
    -- Se rapporto di qualità e Blank va in errore quindi lo metto a -1
    IF ( @NumRappQual = '' OR @NumRappQual = 0) SET @NumRappQual = '-1'
    
    SET @SQL = 
    N'
    SELECT 
            XRPQLN AS nRapQual
           ,XRPQLD AS DataRapQual
           ,XNRETI AS ProgUDC
           ,XCDFOR AS CodFor
           ,XCDTER AS DestTerz
           ,XCDPAR AS CodLega
           ,XDTPRO AS DataPrelFus
           ,XTRPRO AS TurnoPrelFus
           ,XQTPRO AS Qta
           ,XDESMT AS DestLega
           ,XMATSC AS LegaScaric
           ,XBENQL AS BenesQual
    FROM OPENQUERY(' + @As400Linked

    SET @SQL = @SQL + N', ''SELECT * FROM ' + @As400File
    SET @SQL = @SQL + ' WHERE XRPQLN = ' + @NumRappQual + ''' )'

    EXEC sp_executesql @SQL

    -- SELECT * FROM OPENQUERY( AS400,  'SELECT * FROM MAZZT_DTV3.XOGIX00F WHERE XRPQLN = 903383' )     

END











