﻿INSERT
INTO
	vap.t_tact_management
(
	tact_id,
	line_id,
	model_cd,
	basic_parts_no,
	derivation,
	tact
)
SELECT
	CASE
		WHEN Q1.tact_id IS NULL THEN 'TA' || :[S]basic_parts_no || '0001'
		ELSE 'TA' || :[S]basic_parts_no || Q1.tact_id
	END AS tact_id,
	:[I]line_id AS line_id,
	:[S]model_cd AS model_cd,
	:[S]basic_parts_no AS basic_parts_no,
	:[S]derivation AS derivation,
	:[I]tact AS tact
FROM
	(
		SELECT 
			LPAD((MAX(SUBSTRING(tact_id FROM '.{4}$')::INTEGER) + 1)::TEXT, 4, '0') AS tact_id
		FROM
			vap.t_tact_management
		WHERE
			LENGTH(tact_id) > 4 AND
			tact_id ~ '\d{4}$' AND   -- 末尾4桁が数字
			LEFT(tact_id, LENGTH(tact_id) - 4) = 'TA' || :[S]basic_parts_no
	) Q1
--INNER JOIN
--	md.m_line L
--ON
--	L.line_name=:line_name
;
