ALTER PROCEDURE [dbo].[New_Extract_Translate_export_PMA] 
	
AS
BEGIN
	
	SET NOCOUNT ON;
	
	declare 
		@tmpexportid		VARCHAR(50),
		@TMPSTREAMTAG		VARCHAR(50),		
		@tmpcount			int,
		@v_counter			int,
		@SampleTotal		int,
		@TMPPRO_JOB			VARCHAR(50),
		@TMPCUID			VARCHAR(50),
		@TMPSCH_CODE		VARCHAR(50),
		@TMPSAMPLEIDENT		VARCHAR(50),
		@TMPANALYTECODE		VARCHAR(50),
		@TMPANALYTESTATUS	VARCHAR(50),
		@TMPRESULT			VARCHAR(50),
		@TMPCLI_CODE		VARCHAR(50),
		@TMPPROJ_CODE		VARCHAR(50),
		@TMPEXPORTED		DATETIME,
		@TMPREGISTERED		DATETIME,
		@TMPLBATCHJOB		VARCHAR(50),
		@TMPVALIDATEDBY		VARCHAR(50)
		
	declare @tab table (i int identity , EXPORT_ID  VARCHAR(50), STREAM_TAG  VARCHAR(50), PRO_JOB  VARCHAR(50), 
				    CUID  VARCHAR(50), SCH_CODE  VARCHAR(50), SAMPLEIDENT  VARCHAR(50), ANALYTECODE  VARCHAR(50), 
					ANALYTESTATUS  VARCHAR(50), RESULT  VARCHAR(50), CLI_CODE  VARCHAR(50), PROJ_CODE  VARCHAR(50), 
					EXPORTED  VARCHAR(50), REGISTERED  VARCHAR(50), LBATCHJOB  VARCHAR(50),	VALIDATEDBY  VARCHAR(50)) 

	declare @Rows int 

	INSERT INTO @Tab 
	SELECT  EXPORT_ID, STREAM_TAG, PRO_JOB, CUID, SCH_CODE, SAMPLEIDENT, ANALYTECODE, 
		ANALYTESTATUS, RESULT, CLI_CODE, PROJ_CODE, EXPORTED, REGISTERED, LBATCHJOB, 
		VALIDATEDBY
	  FROM CCLS_EXPORT 
	  WHERE PMA_translate is null 
	  AND SCH_CODE NOT LIKE '%_A' 
	  AND SCH_CODE NOT LIKE '%_B'
	  AND ANALYTESTATUS = 'CPL'
	  AND EXPORTED >= getdate()- 45 
	  AND SCH_CODE NOT IN (SELECT NAME FROM PMAIGNORE 
							WHERE SCHEME='Y')	
	  AND ANALYTECODE NOT IN (SELECT NAME FROM PMAIGNORE 
							WHERE ANALYTE='Y')
	  AND STREAM_TAG IN (SELECT STREAMTAG FROM TRANSLATE)
	  AND CLI_CODE <> 'LE_PRP' AND SUBSTRING(SAMPLEIDENT,32,1) <> 'C'

	  ORDER BY STREAM_TAG

	SELECT @Rows = @@ROWCOUNT

	WHILE @Rows > 0 
	BEGIN
		SELECT @TMPEXPORTID=EXPORT_ID, @TMPSTREAMTAG=STREAM_TAG, @TMPPRO_JOB=PRO_JOB, @TMPCUID=CUID, @TMPSCH_CODE=SCH_CODE, 
			   @TMPSAMPLEIDENT=SAMPLEIDENT, @TMPANALYTECODE=ANALYTECODE, @TMPANALYTESTATUS=ANALYTESTATUS, @TMPRESULT=RESULT, 
			   @TMPCLI_CODE=CLI_CODE, @TMPPROJ_CODE=PROJ_CODE, @TMPEXPORTED=EXPORTED, @TMPREGISTERED=REGISTERED, 
			   @TMPLBATCHJOB=LBATCHJOB, @TMPVALIDATEDBY=VALIDATEDBY  
		FROM @Tab 
		WHERE i = @Rows
	
		IF @TMPANALYTESTATUS = 'CPL'
		BEGIN
					print @TMPSAMPLEIDENT
					
					EXEC NEW_PMATranslate @tmpexportid, @TMPPRO_JOB, @TMPCUID, @TMPSCH_CODE , @TMPSAMPLEIDENT, @TMPANALYTECODE, @TMPRESULT, @TMPCLI_CODE, @TMPPROJ_CODE, @TMPEXPORTED, @TMPREGISTERED, @TMPLBATCHJOB, @TMPVALIDATEDBY, @TMPSTREAMTAG
		END	  	 
		SET @Rows = @Rows -1
	END
END