with recursive nomentree (refse,
refcomp,
depth) as (
select et_refakf_se,et_refakf_comp,1 as depth
from t_nomen
inner join t_simo on t_simo.et_nomen = t_nomen.id_nomen
inner join t_modeop on t_modeop.id_modeop = t_simo.et_modeop
inner join u_prod on u_prod.id_prod = t_modeop.et_prod_poste
where et_prod_pere = '6010'
and nomen_deb_validite <= '20230121110539'
and nomen_fin_validite is null
and t_simo.simo_deb_validite <= '20230121110539'
and t_simo.simo_fin_validite is null
and t_modeop.modeop_deb_validite <= '20230121110539'
and t_modeop.modeop_fin_validite is null
union all
select
p.et_refakf_se, p.et_refakf_comp,depth + 1
from t_nomen p
inner join nomentree as sp on
p.et_refakf_comp = sp.refse
where
p.nomen_deb_validite <= '20230121110539'
and p.nomen_fin_validite is null
and depth + 1 <= 10 ),
refFromSameLevel (refse) as (
select
distinct et_refakf_se
from
t_nomen
inner join t_simo on
et_nomen = id_nomen
inner join t_modeop on
et_modeop = id_modeop
where
id_modeop = '11040' )
select et_refcmp,id_stocpf,ts,numorder,typorder,racknum,rackpos,seqnum,amoticline,qualpf
from
(
select
et_refcmp,
et_qualpf as qualpf,
id_stocpf,
t1.time_stamp as ts,
stocpf_numorder as numorder,
stocpf_typorder as typorder,
racknum.trace_order_value as racknum,
rackpos.trace_order_value as rackpos,
seqnum.trace_order_value as seqnum,
amoticline.trace_order_value as amoticline
from
t_stocpf
inner join t_operat t1 on
t1.et_stocpf = t_stocpf.id_stocpf
and t1.operat_no_passage = (
select
max(t2.operat_no_passage)
from
t_operat t2
where
et_stocpf = t1.et_stocpf
and t2.et_simo = t1.et_simo )
inner join t_simo on
t_simo.id_simo = t1.et_simo
inner join t_modeop modeop1 on
modeop1.id_modeop = t_simo.et_modeop
inner join u_prod on
u_prod.id_prod = modeop1.et_prod_poste
inner join t_operat ope2 on
t1.et_stocpf = ope2.et_stocpf
inner join t_simo simo2 on
simo2.id_simo = ope2.et_simo
inner join t_modeop modeop2 on
modeop2.id_modeop = simo2.et_modeop
left join t_trace_order racknum on
stocpf_numorder = trace_order_card
and stocpf_typorder = trace_order_type
and racknum.et_order_carord = 11
left join t_trace_order rackpos on
racknum.id_trace_order = rackpos.id_trace_order
and rackpos.et_order_carord = 12
left join t_trace_order seqnum on
racknum.id_trace_order = seqnum.id_trace_order
and seqnum.et_order_carord = 15
left join t_trace_order amoticline on
racknum.id_trace_order = amoticline.id_trace_order
and amoticline.et_order_carord = -2
where
t1.operat_nscomp = '#10D857739A MNB#1#654335579#210123*667 5BEQM1FR09F*='
and modeop2.et_prod_poste = modeop1.et_prod_poste
and ope2.time_stamp >= t1.time_stamp
and ope2.et_etatop not in ('Z', 'Y')
and t_simo.simo_deb_validite <= '20230121110539'
and t_simo.simo_fin_validite is null
and modeop1.modeop_deb_validite <= '20230121110539'
and modeop1.modeop_fin_validite is null
and u_prod.prod_deb_validite <= '20230121110539'
and u_prod.prod_fin_validite is null
and modeop1.id_modeop != '11040'
and et_refcmp in (
select
distinct refcomp
from
nomentree)
and et_refcmp not in (
select
refse
from
refFromSameLevel)
union
select
et_refcmp,
et_qualpf as qualpf,
id_stocpf,
t1.time_stamp as ts,
stocpf_numorder as numorder,
stocpf_typorder as typorder,
racknum.trace_order_value as racknum,
rackpos.trace_order_value as rackpos,
seqnum.trace_order_value as seqnum,
amoticline.trace_order_value as amoticline
from
t_stocpf
inner join t_operat t1 on
t1.et_stocpf = t_stocpf.id_stocpf
and t1.operat_no_passage = (
select
max(t2.operat_no_passage)
from
t_operat t2
where
et_stocpf = t1.et_stocpf
and t2.et_simo = t1.et_simo)
inner join t_simo on
t_simo.id_simo = t1.et_simo
inner join t_modeop modeop1 on
modeop1.id_modeop = t_simo.et_modeop
inner join u_prod on
u_prod.id_prod = modeop1.et_prod_poste
left join t_trace_order racknum on
stocpf_numorder = trace_order_card
and stocpf_typorder = trace_order_type
and racknum.et_order_carord = 11
left join t_trace_order rackpos on
racknum.id_trace_order = rackpos.id_trace_order
and rackpos.et_order_carord = 12
left join t_trace_order seqnum on
racknum.id_trace_order = seqnum.id_trace_order
and seqnum.et_order_carord = 15
left join t_trace_order amoticline on
racknum.id_trace_order = amoticline.id_trace_order
and amoticline.et_order_carord = -2
where
stocpf_no_cli = '#10D857739A MNB#1#654335579#210123*667 5BEQM1FR09F*='
and t_simo.simo_deb_validite <= '20230121110539'
and t_simo.simo_fin_validite is null
and modeop1.modeop_deb_validite <= '20230121110539'
and modeop1.modeop_fin_validite is null
and u_prod.prod_deb_validite <= '20230121110539'
and u_prod.prod_fin_validite is null
and modeop1.id_modeop != '11040'
and et_refcmp in (
select distinct refcomp from nomentree)
and et_refcmp not in (
select
refse
from
refFromSameLevel)
union
select
et_refcmp,
et_qualpf as qualpf,
id_stocpf,
t1.time_stamp as ts,
stocpf_numorder as numorder,
stocpf_typorder as typorder,
racknum.trace_order_value as racknum,
rackpos.trace_order_value as rackpos,
seqnum.trace_order_value as seqnum,
amoticline.trace_order_value as amoticline
from
t_stocpf
inner join t_operat t1 on
t1.et_stocpf = t_stocpf.id_stocpf
and t1.operat_no_passage = (
select
max(t2.operat_no_passage)
from
t_operat t2
where
et_stocpf = t1.et_stocpf
and t2.et_simo = t1.et_simo)
inner join t_simo on
t_simo.id_simo = t1.et_simo
inner join t_modeop modeop1 on
modeop1.id_modeop = t_simo.et_modeop
inner join u_prod on
u_prod.id_prod = modeop1.et_prod_poste
inner join t_clipf cli on
cli.et_stocpf = id_stocpf
inner join t_carcli ccli on
ccli.id_carcli = et_carcli
and ccli.identify_pf = 1
left join t_trace_order racknum on
stocpf_numorder = trace_order_card
and stocpf_typorder = trace_order_type
and racknum.et_order_carord = 11
left join t_trace_order rackpos on
racknum.id_trace_order = rackpos.id_trace_order
and rackpos.et_order_carord = 12
left join t_trace_order seqnum on
racknum.id_trace_order = seqnum.id_trace_order
and seqnum.et_order_carord = 15
left join t_trace_order amoticline on
racknum.id_trace_order = amoticline.id_trace_order
and amoticline.et_order_carord = -2
where
clipf_valeur = '#10D857739A MNB#1#654335579#210123*667 5BEQM1FR09F*='
and t_simo.simo_deb_validite <= '20230121110539'
and t_simo.simo_fin_validite is null
and modeop1.modeop_deb_validite <= '20230121110539'
and modeop1.modeop_fin_validite is null
and u_prod.prod_deb_validite <= '20230121110539'
and u_prod.prod_fin_validite is null
and modeop1.id_modeop != '11040'
and et_refcmp in (
select
distinct refcomp
from
nomentree)
and et_refcmp not in (
select
refse
from
refFromSameLevel)
union
select
et_refcmp,
et_qualpf as qualpf,
id_stocpf,
(tv1.datemodif || tv1.heuremodif) as ts,
stocpf_numorder as numorder,
stocpf_typorder as typorder,
racknum.trace_order_value as racknum,
rackpos.trace_order_value as rackpos,
seqnum.trace_order_value as seqnum,
amoticline.trace_order_value as amoticline
from
t_tracea_value tv1
inner join t_tracea_type_carac ttc_id on
ttc_id.id_tracea_type_carac = tv1.et_tracea_type_carac
inner join t_tracea_carac tc_id on
tc_id.id_tracea_carac = ttc_id.et_tracea_carac
and tc_id.tracea_carac_libelle = 'ID'
inner join t_tracea_type tt_id on
tt_id.id_tracea_type = ttc_id.et_tracea_type
and tt_id.tracea_type_name = 'PRODUCT'
inner join t_tracea t_id on
tv1.et_tracea = t_id.id_tracea
left join t_tracea t_nocli on
t_nocli.id_tracea = t_id.id_tracea
left join t_tracea_value ttv_nocli on
ttv_nocli.et_tracea = t_nocli.id_tracea
left join t_tracea_type_carac ttc_nocli on
ttc_nocli.id_tracea_type_carac = tv1.et_tracea_type_carac
left join t_tracea_carac tc_nocli on
tc_nocli.id_tracea_carac = ttc_nocli.et_tracea_carac
and tc_nocli.tracea_carac_libelle = 'SERIAL_NUMBER'
left join t_tracea_type tt_nocli on
tt_nocli.id_tracea_type = ttc_nocli.et_tracea_type
and tt_nocli.tracea_type_name = 'PRODUCT'
left join t_tracea t_nocomp on
t_nocomp.et_tracea_pere = t_id.id_tracea
left join t_tracea_value ttv_nocomp on
ttv_nocomp.et_tracea = t_nocomp.id_tracea
left join t_tracea_type_carac ttc_nocomp on
ttc_nocomp.id_tracea_type_carac = tv1.et_tracea_type_carac
left join t_tracea_carac tc_nocomp on
tc_nocomp.id_tracea_carac = ttc_nocomp.et_tracea_carac
and tc_nocomp.tracea_carac_libelle = 'SERIAL_NUMBER'
left join t_tracea_type tt_nocomp on
tt_nocomp.id_tracea_type = ttc_nocomp.et_tracea_type
and tt_nocomp.tracea_type_name = 'COMPONENT'
inner join t_stocpf stocpf on
tv1.tracea_carac_value = stocpf.id_stocpf
left join t_trace_order racknum on
stocpf_numorder = trace_order_card
and stocpf_typorder = trace_order_type
and racknum.et_order_carord = 11
left join t_trace_order rackpos on
racknum.id_trace_order = rackpos.id_trace_order
and rackpos.et_order_carord = 12
left join t_trace_order seqnum on
racknum.id_trace_order = seqnum.id_trace_order
and seqnum.et_order_carord = 15
left join t_trace_order amoticline on
racknum.id_trace_order = amoticline.id_trace_order
and amoticline.et_order_carord = -2
where
(ttv_nocli.tracea_carac_value = '#10D857739A MNB#1#654335579#210123*667 5BEQM1FR09F*='
or ttv_nocomp.tracea_carac_value = '#10D857739A MNB#1#654335579#210123*667 5BEQM1FR09F*=')
and et_refcmp in (
select
distinct refcomp
from
nomentree)
and et_refcmp not in (
select
refse
from
refFromSameLevel) ) A
order by
ts desc
limit 1