ACS Tracking

PrintPack.md

Delete box with backup

• Delete [ame_ClearPackingRecordNUpdateTInformation] PO, box
• Revert amevn_revertdeletedbox PO, box

-- prepare table
select top 10 PONumber, Model, Serial, BoxNumber, PackingDateTime , getdate() ActionDate,'Delete' ActionType, cast('' as varchar(30)) ActionBy
into PackMovementAction
from PackingRecord 


-- truncate table PackMovementAction
select * from PackMovementAction

-- do insert
insert into PackMovementAction
(PONumber, Model, Serial, BoxNumber, PackingDateTime, ActionDate, ActionType, ActionBy)
select  PONumber, Model, Serial, BoxNumber, PackingDateTime , getdate() ActionDate,'Delete' ActionType, cast('' as varchar(30)) ActionBy
--into PackMovementAction
from PackingRecord 
WHERE PONumber = @PONumber AND BoxNumber = @BoxNumber 


-- do revert deleted box
insert into PackingRecord
(PONumber, Model, Serial, BoxNumber, PackingDateTime)
select  PONumber, Model, Serial, BoxNumber, PackingDateTime
from PackMovementAction
WHERE PONumber = @PONumber AND BoxNumber = @BoxNumber 
--cal culate po serial
EXEC ame_UpdatePartRun @PONumber

-- test
select  * from packingrecord
where ponumber ='000100667854'
and boxnumber ='156U442'   

- Delete `[ame_ClearPackingRecordNUpdateTInformation] '000100667854', '156U442'
- Revert `amevn_revertdeletedbox '000100667854', '156U442'

select * from PackMovementAction  
where ponumber ='000100667854'
and boxnumber ='156U442'   
-- ok 

STARTTEST.md

Tìm số DS base scanner vs FFC chạy ở VN

• Order mới nhất nằm trên
• Lấy tất cả trạm start

SELECT rn=(row_number() over(PARTITION BY sap_model, part_number ORDER BY bom_date_time desc)),* FROM (
SELECT distinct PAR.ProdOrder,PAR.Qty
, PAR.SAP_Model, PAR.Part_Number,PAR.Station, PAR.BOM_Date_Time FROM ACSEEClientState..Parts_Level AS PAR 
WHERE station LIKE '%START%'
AND PAR.ProdOrder <>''
AND PAR.SAP_Model <> ''
AND part_number <> 'INFO'
)tb1
 

Dùng LINKED SERVER tìm test data tất cả các base scanner có test

SELECT DISTINCT tl.SAP_Model, tl.Station
  FROM [VNMACSRPT2].[RStaging].[dbo].[DB1_testlog] tl
  WHERE tl.Test_Date_Time >= dateadd(dd,-3, getdate())
  AND sap_model <> ''

Now do Join

; WITH cte AS 
(
SELECT rn=(row_number() over(PARTITION BY sap_model, part_number ORDER BY bom_date_time desc)),* FROM (
SELECT distinct PAR.ProdOrder,PAR.Qty
, PAR.SAP_Model, PAR.Part_Number,PAR.Station, PAR.BOM_Date_Time FROM ACSEEClientState..Parts_Level AS PAR 
WHERE station LIKE '%START%'
AND PAR.ProdOrder <>''
AND PAR.SAP_Model <> ''
AND part_number <> 'INFO'
)tb1

)-- end cte
SELECT sap_model Top_Model, /*part_number,*/ station Start_Station
,tld.*
FROM cte
inner join
(
SELECT DISTINCT tl.SAP_Model Test_Model, tl.Station Test_Station
  FROM [VNMACSRPT2].[RStaging].[dbo].[DB1_testlog] tl
  WHERE tl.Test_Date_Time >= dateadd(mm,-1, getdate())
  AND sap_model <> ''
) tld
on tld.Test_Model =cte.part_number
 WHERE rn =1
 order by top_model

StartwithTest.sql

--start FFC
--test data
-- join
-- auto add

Tra base-> FFC.sql

SELECT 
TFFC_ProdOrder AS Orders , 
TFFC_Material AS SapModel , 
TFFC_SerialNumber AS Sap_serial , 
ACS_Serial AS ACS_Serial , 
Part_No_Name AS Sub_SapModel , 
asylog.Scanned_Serial AS Sub_Serial 
FROM asylog 
INNER JOIN Catalog ON asylog.Added_Part_No = Catalog.Part_No_Count 
INNER JOIN Stations ON asylog.Station = Stations.Station_Count 
INNER JOIN TFFC_SerialNumbers ON TFFC_ACSSErial = ACS_Serial WHERE Scanned_Serial IN 
( -- insert base scanner serial here
    --'G15GALHIX' 
    
)

Tra FFC ra BASE SCANNER.sql

 ---ffc 2 base - assy data
	SELECT        TFFC_ProdOrder as Orders, TFFC_Material as SapModel, TFFC_SerialNumber as Sap_serial, 
 a.ACS_Serial as ACS_Serial,  Part_No_Name as Sub_SapModel, a.Scanned_Serial as Sub_Serial, a.Action_Date
 FROM            asylog a INNER JOIN
                         Catalog ON a.Added_Part_No = Catalog.Part_No_Count INNER JOIN
                         Stations ON a.Station = Stations.Station_Count INNER JOIN
						 TFFC_SerialNumbers ON TFFC_ACSSErial=ACS_Serial
						                                         where 
		dbo.TFFC_SerialNumbers.TFFC_ProdOrder IN
										(
										'','000100697572' 
										)
										and TFFC_SerialNumber in
										(
										''
										)
										
										and a.Scanned_Serial<>''

Track AssyTracking.sql

SELECT        asylog.ACS_Serial, Catalog.Part_No_Name,  asylog.Scanned_Serial, asylog.Rev, asylog.Quantity, Stations.Station_Name, asylog.Action_Date
FROM            asylog INNER JOIN
                         Catalog ON asylog.Added_Part_No = Catalog.Part_No_Count INNER JOIN
                         Stations ON asylog.Station = Stations.Station_Count

						 where acs_serial in
						 (
						'SN1',	'SN2'
						 )

						 --AND dbo.Catalog.Part_No_Name ='SE-1500ER    '

YIELD.sql

select *, 'FirstRun' RS from
( SELECT * ,
row_number() over (partition by acs_serial order by test_date_time asc) rn
FROM testlog WHERE sap_model ='740199300'
AND test_date_time >= '02/14/2017' AND test_date_time <= '02/16/2017'
and  FirstRun ='Y' )fr
where rn=1
--
select *, 'FirstPass' RS from
( SELECT * ,
row_number() over (partition by acs_serial order by test_date_time asc) rn
FROM testlog WHERE sap_model ='740199300'
AND test_date_time >= '02/14/2017' AND test_date_time <= '02/16/2017'
and  FirstRun ='Y' AND pass_fail ='P')fr
where rn=1


select *, 'FirstFail' RS from
( SELECT * ,
row_number() over (partition by acs_serial order by test_date_time asc) rn
FROM testlog WHERE sap_model ='740199300'
AND test_date_time >= '02/14/2017' AND test_date_time <= '02/16/2017'
and  FirstRun ='Y' AND pass_fail ='F')fr
where rn=1


--- 

select *, 'TotalPass' RS from
( SELECT * ,
row_number() over (partition by acs_serial order by test_date_time desc) rn
FROM testlog WHERE sap_model ='740199300'
AND test_date_time >= '02/14/2017' AND test_date_time <= '02/16/2017'
--and  pass_fail ='P'
)fr
where rn=1 AND  pass_fail ='P'


select *, 'TotalFAIL' RS from
( SELECT * ,
row_number() over (partition by acs_serial order by test_date_time desc) rn
FROM testlog WHERE sap_model ='740199300'
AND test_date_time >= '02/14/2017' AND test_date_time < '02/16/2017'
--and  pass_fail ='F'
)fr
where rn=1 AND pass_fail ='F'


SELECT * FROM testlog WHERE acs_serial ='G17BAGPTQ'


-- report2

select  * from subtestlog_view
WHERE dbo.subtestlog_view.Test_ID IN
(
select Test_ID from
( SELECT * ,
row_number() over (partition by acs_serial order by test_date_time desc) rn
FROM db1_testlog WHERE sap_model ='740199300'
AND test_date_time >= '02/14/2017' AND test_date_time < '02/16/2017'
--and  pass_fail ='F'
)fr
where rn=1 AND pass_fail ='F' 

) AND dbo.subtestlog_view.Test_Pass_Fail ='F' AND dbo.subtestlog_view.Pass_Fail ='F'

SELECT * FROM dbo.subtestlog_view AS SUB WHERE SUB.ACS_Serial ='G17BAGPTQ'







----
select  * from subtestlog_view
WHERE Test_ID IN
(
select Test_ID from
( SELECT * ,
row_number() over (partition by acs_serial order by test_date_time desc) rn
FROM testlog WHERE sap_model ='740199300'
AND test_date_time >= '02/14/2017' AND test_date_time < '02/16/2017'
--and  pass_fail ='F'
)fr
where rn=1 AND pass_fail ='F' 

) AND Test_Pass_Fail ='F' AND Pass_Fail ='F'