First, add the following part to the package specification of m_pck_verify_custom:
PROCEDURE alte_ide
(p_lp_id IN m_list_pos.lp_id%TYPE);
-- This procedure finds alternate idents for a spec item
-- based on other option codes and put the list in
-- a position attribute ALTERN_IDENT of the BOM
The only parameter needed for this procedure is the BOM position unique key, m_list_pos.lp_id. The procedure is executed once for each position under the selected BOM node.
Next, add the second part of the code to the package body of m_pck_verify_custom:
PROCEDURE alte_ide
(p_lp_id IN m_list_pos.lp_id%TYPE)
IS
-- first declare variables to hold temporary results
spec_id m_list_pos.spec_header_id%type;
short_c m_list_pos.short_code%type;
help varchar(2000);
size1 m_list_pos.input_1%type;
alt_ident varchar(2000);
option_pos m_list_pos.option_code%type;
intident m_idents.ident%type ;
CURSOR optionc IS
SELECT option_code
FROM m_spec_items si
WHERE si.header_id=spec_id
and si.short_code=short_c
and size1 between si.from_size1 and si.to_size1
and si.option_code <> option_pos;
op_code optionc%rowtype;
BEGIN
select lp.spec_header_id into spec_id
from m_list_pos lp
where lp.lp_id = p_lp_id;
select option_code into option_pos
from m_list_pos lp
where lp.lp_id = p_lp_id;
select short_code into short_c
from m_list_pos lp
where lp.lp_id = p_lp_id;
select input_1 into size1
from m_list_pos lp
where lp.lp_id=p_lp_id;
m_pck_m.ml(‘option_pos: ‘ || option_pos, 5,true);
-- this procedure writes its first argument to a log file
-- which is created on the DB server.
-- It is here only for debugging purposes and could be
-- commented out in a production environment
m_pck_m.ml('short_c: ' || short_c, 5,true);
m_pck_m.ml('p_lp_id: ' || p_lp_id, 5,true);
-- select the possible option codes from the spec
OPEN optionc;
FETCH optionc INTO op_code;
WHILE optionc%FOUND LOOP
m_pck_m.ml('looping option_code: ' || op_code.option_code, 5,true);
intident := m_pck_idents.find_ident_org(spec_id,short_c,size1,'0','0',op_code.option_code,'0','0');
-- this procedure fetches the ident from the spec
-- it will return 0 if there is no corresponding ident
m_pck_m.ml('intident: ' || to_char(intident), 5,true);
if ( intident <> 0 ) then
SELECT IDENT_CODE into help
FROM M_IDENTS I WHERE I.IDENT = intident;
-- this gives the ident code from the ident
if (alt_ident is not NULL )
then
alt_ident:=alt_ident||';'||help;
else
alt_ident:= help;
end if ;
-- this concatenates the ident codes
end if;
m_pck_m.ml('alt_ident: ' || alt_ident, 5,true);
fetch optionc into op_code;
end loop;
close optionc;
m_pck_m.ml('value: ' || alt_ident, 5,true);
update m_list_pos_values lpv
set attr_value = alt_ident
where lpv.lp_id = p_lp_id
and lpv.attr_id IN (select attr_id from mvp_attrs a where a.attr_code = 'ALTERN_IDENT');
-- here the ALTERN_IDENT attribute is updated in BOM position
END alte_ide;
Now save and compile the package.