Clone programming steps

— list products that might need to be programmed.

SELECT name,revision, product_id FROM qs_products where name like ‘277000-200-%’

— list current programming steps

select p.name,p.revision,steps.* from [dbo].[ACA_swprg_programming_steps] steps
INNER JOIN qs_products p ON p.product_id = steps.mes_product_id

— after updating the TO and FROM UIDs, clone the programming step.

INSERT INTO [dbo].[ACA_swprg_programming_steps]
SELECT
[programming_step_id] = NewId()
,[mes_product_id]=’7CCD041D-352E-48E3-A581-0A99B148568B’
,[mes_route_step_id]
,[mes_station_id]
FROM [dbo].[ACA_swprg_programming_steps]
WHERE [mes_product_id] = ‘FC643AFA-6FCA-405A-867C-A4F22A17C8F0′

— after updating the TO and FROM UIDs, clone the programmed parts.

INSERT INTO [dbo].[ACA_swprg_software_parts]
SELECT
software_part_id=NewID(), programming_step_id=’D076393C-A968-4D50-9A4F-ED80007BACD4′, part_number, CRC, slot, tracked, storage_location, bin_filename, cfg_filename, mes_component_id=NULL
FROM
[dbo].[ACA_swprg_software_parts]
WHERE [programming_step_id]=’9CB96871-0F82-4194-81FB-C22DF950CA57’

–finally, populate the component_ids. No TO/FROM to change; this populates only the component_ids which should not be NULL.

UPDATE [dbo].[ACA_swprg_software_parts]
SET [ACA_swprg_software_parts].mes_component_id=cpn.component_id
FROM [dbo].[ACA_swprg_software_parts] sp
INNER JOIN [dbo].[ACA_swprg_programming_steps] prgstep ON prgstep.[programming_step_id] = sp.[programming_step_id]
INNER JOIN qs_components cpn ON cpn.product_id=prgstep.[mes_product_id] AND cpn.part_number = sp.[part_number]
WHERE sp.mes_component_id IS NULL

This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *