notes-kanban/OI-Metrology/tasks/three-new-oi-api-endpoints.md

assigned, progress, started, type, created, updated

assigned	progress	started	type	created	updated
	0	2023-10-03T15:47:01.195Z	note	2024-01-06T01:22:07.250Z	2024-01-06T01:22:07.250Z

Three new OI API Endpoints

--- # 2.	GetUCL
--- ProductDataAverageSumOfDefectsProcessMeanProcessSigma
--- product-data-average-sum-of-defects-process-mean-process-sigma
--- Parameters: ReactorKey (41), TencorRecipe (8IN_THIN ROTR)
SELECT [f_mean] AS ProcessMean,
       [f_sp] AS ProcessSigma
FROM [spcepiworld].[dbo].[test_dat] test
JOIN [spcepiworld].[dbo].[ctrl_lim] ctrl
  ON test.f_test = ctrl.f_test
  AND test.f_tsgp = 1104848523 /* Product Data */
JOIN [spcepiworld].[dbo].[part_dat] part
  ON part.f_part = ctrl.f_part
 AND ctrl.f_test = 1125073605 /* Average Sum of Defects */
JOIN [spcepiworld].[dbo].[prcs_dat] process
  ON process.f_prcs = ctrl.f_prcs
WHERE test.f_name = 'Average Sum of Defects'
  AND process.f_name = '41'
  AND part.f_name = '8IN_THIN ROTR'

--- Parameters: RDSKey (608325), TencorRecipe (8IN_THIN ROTR) , ScanDTM (09/11/2023 01:34:11PM)
--- DECLARE @RDS         varchar(10)
--- DECLARE @RECIPE      varchar(30)
--- DECLARE @INSERT_DT   datetime SET @RDS = '608325' SET @RECIPE = '8IN_THIN ROTR' SET @INSERT_DT = '09/11/2023 01:34:11PM'
SELECT child.slot,
       child.sumOfDefects,
       child.sort
FROM Metrology.dbo.TencorRunHeader header
INNER JOIN Metrology.dbo.TencorRunData child
  ON header.id = child.headerId
WHERE header.rds = @RDS
  AND header.recipe LIKE @RECIPE + '%'
  AND child.InsertDate BETWEEN (SELECT(DATEADD(MINUTE, -5, @INSERT_DT)))
  AND (SELECT(DATEADD(MINUTE, 5, @INSERT_DT)))
ORDER BY header.insertDate DESC,
         child.slot ASC

--- 3.	GetWaferImagePath
--- Parameters: RDSKey (608325), TencorRecipe (8IN_THIN ROTR), WaferNo (*01)
--- DECLARE @RDS         varchar(10)
--- DECLARE @RECIPE      varchar(30)
--- DECLARE @WFRID       varchar(10) SET @RDS = '608325' SET @RECIPE = '8IN_THIN ROTR' SET @WFRID  = '*01'
SELECT TOP (1)
       child.AttachmentID,
       child.insertDate
FROM metrology.dbo.TencorRunData header
INNER JOIN metrology.dbo.TencorRunData child
  ON header.id = child.headerid
WHERE header.rds = @RDS
  AND header.recipe LIKE @RECIPE + '%'
  AND child.slot =