DECLARE @t_sup TABLE (
sup_id int,
login_name varchar(128),
sup_level varchar(128),
process_code varchar(128)
)
--DECLARE @contact_id AS NVARCHAR(50); SET @contact_id = '7984';
--DECLARE @imp_id AS NVARCHAR(50); SET @imp_id = '202548';
INSERT INTO @t_sup (sup_id, login_name)
SELECT s.sup_id, COALESCE(ls.login_name, 'root')
FROM t_sup_supplier s
LEFT JOIN t_usr_login_securable ls ON ls.sec_id=s.sec_id AND ls.profil_code='sup_owner'
LEFT JOIN t_wfl_process_execution pe ON pe.tdesc_name='t_sup_supplier' AND pe.x_id=CAST(s.sup_id AS varchar(128))
WHERE s.imp_id=@imp_id --AND pe.process_code IS NULL
SELECT * FROM @t_sup
UPDATE ts
SET sup_level=l.lvl_code
FROM @t_sup ts
INNER JOIN (
SELECT ts.sup_id, MAX(lvl_order) AS min_lvl_order
FROM @t_sup ts
INNER JOIN t_sup_supplier_level sl ON sl.sup_id=ts.sup_id
INNER JOIN t_pbi_level l ON l.lvl_code=sl.lvl_code
GROUP BY ts.sup_id
) AS ml ON ml.sup_id=ts.sup_id
INNER JOIN t_pbi_level l ON l.lvl_order=ml.min_lvl_order AND l.dim_code='sup'
-- Déc
DECLARE @process_code AS VARCHAR(50)
SELECT @contact_id = COALESCE(usr.contact_id, 1)
FROM t_sup_supplier s
INNER JOIN t_usr_login as usr ON usr.login_name = s.login_name_created
WHERE s.imp_id=@imp_id
SELECT @process_code = process_code FROM t_wfl_process_x WHERE tdesc_name = 't_sup_supplier' --AND process_code is not null
-- Mise à jour WFL SUPPLIER V2 - Etape Création (INI)
IF NOT EXISTS (SELECT * FROM t_wfl_worklist wfl INNER JOIN t_sup_supplier s on s.sup_id = wfl.x_id WHERE 1=1 AND wfl.process_code = 'SUPPLIER_V2' AND wfl.act_code = 'INI' AND wfl.x_id = s.sup_id AND s.imp_id=@imp_id)
INSERT INTO t_wfl_worklist(process_code, x_id, tdesc_name, act_code, contact_id_performer,wli_date_ini, wli_date_val, wli_comment)
SELECT @process_code, s.sup_id, 't_sup_supplier', 'INI', @contact_id, GETDATE(), GETDATE(), @contact_id+GETDATE()
FROM dbo.t_sup_supplier AS s INNER JOIN t_wfl_worklist wfl on wfl.x_id = s.sup_id WHERE s.imp_id=@imp_id AND contact_id_performer = @contact_id AND process_code = 'SUPPLIER_V2'--AND process_code is not null
ELSE UPDATE t_wfl_worklist SET contact_id_performer = @contact_id, wli_date_val = GETDATE(), wli_comment = @contact_id+GETDATE()
FROM t_wfl_worklist INNER JOIN t_sup_supplier as s on s.sup_id=t_wfl_worklist.x_id WHERE x_id = s.sup_id AND act_code = 'INI' AND s.imp_id= @imp_id AND contact_id_performer = @contact_id
-- Mise à jour WFL SUPPLIER V2 - Etape Validation groupe (RSK)
IF NOT EXISTS (SELECT * FROM t_wfl_worklist wfl INNER JOIN t_sup_supplier s on s.sup_id = wfl.x_id LEFT JOIN t_sup_supplier_level lvl on s.sup_id=lvl.sup_id
WHERE 1=1 AND process_code = 'SUPPLIER_V2' AND act_code = 'RSK' AND x_id = s.sup_id AND s.imp_id=@imp_id AND lvl.lvl_code <> 'ult')
What I have tried:
i tried to debug myself, but... i can't