Model not being merged into certificate.

This happened recently where a Model is linked to an old TC project that has been deleted.

( BUG 200255)

So Rows existing in TA_APPL and typ_aprv_mdl are therefor messing the larger sql up.

Where clause links

Nvl( ta_appl.aprv_stat_cd, 1) IN ( 1, 2)

and mod_aprv_mdl.mdl_seqnum = typ_aprv_mdl.mdl_seqnum (+)
and typ_aprv_mdl.ta_id_num = ta_appl.ta_id_num (+)
and typ_aprv_mdl.proj_num = ta_appl.proj_num (+)

The SQL that retrieves the models/ serial numbers and TC/TA approval numbers is not finding the rows.

SELECT
Nvl( Decode( prod_make_lkp.prod_make_display, NULL, prod_make_lkp.make, prod_make_lkp.prod_make_display) ||' '||
Decode( prod_mdl_lkp.prod_mdl_display, NULL, prod_mdl_lkp.mdl, prod_mdl_lkp.prod_mdl_display), '') AS model,
Nvl( Decode( prod_make_lkp.prod_make_display, NULL, prod_make_lkp.make, prod_make_lkp.prod_make_display), '') AS make,
Nvl( Decode( prod_mdl_lkp.prod_mdl_display, NULL, prod_mdl_lkp.mdl, prod_mdl_lkp.prod_mdl_display), '') AS mdl,
Nvl( Decode( ta_appl.ta_num, NULL, Nvl( ta_appl.frgn_num, ' '), ta_appl.ta_num), ' ') AS ta_num,
Nvl( ta_appl.ta_id_num, 0) as ta_id_num
FROM
mod_aprv_mdl,
mod_proj,
mod_aprv_cert,
prod_mdl_lkp,
prod_make_lkp,
ta_appl,
typ_aprv_mdl
WHERE
Nvl( ta_appl.aprv_stat_cd, 1) IN ( 1, 2)
-- and mod_aprv_mdl.rvs_num_added <= @an_rvs_num
-- and Nvl( mod_aprv_mdl.rvs_num_dropped, 999) > @an_rvs_num
and mod_aprv_mdl.mod_id_num = 60885
and mod_aprv_mdl.rgn_cd = 'O'
and prod_make_lkp.make_seqnum = mod_aprv_mdl.make_seqnum
and prod_mdl_lkp.mdl_seqnum = mod_aprv_mdl.mdl_seqnum
and prod_mdl_lkp.make_seqnum = mod_aprv_mdl.make_seqnum
and mod_aprv_mdl.mdl_seqnum = typ_aprv_mdl.mdl_seqnum (+)
and typ_aprv_mdl.ta_id_num = ta_appl.ta_id_num (+)
and typ_aprv_mdl.proj_num = ta_appl.proj_num (+)
and mod_aprv_mdl.mod_id_num = mod_proj.mod_id_num
and mod_aprv_mdl.mod_rvs_dt = mod_proj.mod_rvs_dt
and mod_proj.proj_stat <> 4
and mod_aprv_mdl.mod_aprv_mdl_date_dropped = mod_aprv_cert.mod_rvs_dt (+)
and ( mod_aprv_mdl.mod_aprv_mdl_date_dropped IS NULL
or mod_aprv_cert.isu_num > (
SELECT Nvl( isu_num, 999)
FROM mod_aprv_cert
WHERE mod_id_num = 60885
-- and mod_rvs_dt = :adt_mod_rvs_dt))
))
ORDER BY
make,
ta_num,
model

________________________

To Resolve delete the respective Model row from the table typ_aprv_mdl for that deleted project.