chalie001
asked on
not able to insert into temp table
hi am not able to insert into in my temp table when i select values from my table using oracle forms i have procedure i can show
the file are hear https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
this is my procedure am in oracle database 11g and am using forms 11gR2
the file are hear https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
this is my procedure am in oracle database 11g and am using forms 11gR2
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
BEGIN
ws_unit_cde := 0;
IF :key.wrk_org_func = 'HQ' or
(:key.wrk_org_func = 'CM' and
:key.scr_all_units = 'Y') THEN
ws_unit_cde := 0;
ws_eng_abbr_nme := '';
ELSE
ws_unit_cde := :key.scr_req_unit;
ws_eng_abbr_nme := :key.scr_unit_desc;
END IF;
-- message('testt09');
-- message(:key.scr_ser_no);
IF :key.scr_ser_no is not null then
DECLARE
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde||me_var_cde mec,
i.nsc||'-'||substr(i.niin,1,2)||'-'||
substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where scin = rec1.scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));
ws_found := 'Y';
EXCEPTION
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
END;
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found THEN
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
BEGIN
select ser_no
into ws_ser_no
from ils.sss_entry_index
where scin = rec1.scin
and SIN_CHANGE_DTE is null;
EXCEPTION
when no_data_found then
message('No serial number for this ICN');
raise form_trigger_failure;
END;
wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
wrk_nsn_desc := null;
-- wrk_nsn := nvl(wrk_nsn,'');
-- message('error12');
if :key.SCR_ICN_2ND_2 is not null then
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
end if;
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
-- raise form_trigger_failure;
END;
-- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
null;
o_cnt := ws_cnt;
--message('ws_cnt:'||ws_cnt);pause;
END LOOP;
END;
ELSE
BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
For rec1 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where rec1.scin = scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));
ws_found := 'Y';
EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;
END;
--message('ws_scin from table: '||ws_scin);pause;
ws_ser_no := '';
ws_inst_ser_no := '';
IF rec1.srv_status_cde = 'C02' then
ws_ser_no := rec1.nha_ser_no;
ws_inst_ser_no := rec1.ser_no;
ws_pers_located_at := rec1.pers_located_at;
BEGIN
select a.scin, a.sin,
b.srv_status_cde,
b.unit_holding,
nvl(b.dept_holding,0),
nvl(b.unit_located_at,0),
b.cond_cde,
lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
c.cntr_body_cde,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
into ws_scin, ws_sin,
ws_srv_status_cde,
ws_unit_holding,
ws_dept_holding,
ws_unit_located_at,
ws_cond_cde,
ws_nsn,
ws_itm_type_cde,
ws_struc_lev_cde,
ws_agency_ser_cntrl_ind,
ws_cntr_body_cde,
ws_mec,
ws_me_descr
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.ser_no = rec1.nha_ser_no
and a.sin = rec1.nha_sin
and a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'));
exception
when no_data_found then
ws_ser_no := 'INVALID - '||ws_ser_no;
ws_srv_status_cde := '';
END;
ELSE
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_srv_status_cde := rec1.srv_status_cde;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_dept_holding := nvl(rec1.dept_holding,0);
ws_unit_located_at := nvl(rec1.unit_located_at,0);
ws_cond_cde := rec1.cond_cde;
ws_pers_located_at := rec1.pers_located_at;
ws_nsn := rec1.nsn;
ws_itm_type_cde := rec1.itm_type_cde;
ws_struc_lev_cde := rec1.struc_lev_cde;
ws_agency_ser_cntrl_ind := rec1.agency_ser_cntrl_ind;
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
END IF;
-- Set up values for print table
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found then
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
-- NSN Description
wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
wrk_nsn_desc := null;
/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
message('error12344');
-- wrk_nsn := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not null then
-- message('insideif');
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
end if;
message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
if ws_found = 'N' then
IF :key.scr_depr_sched in ('N','A') then
IF :key.scr_purch_price in ('N','A')
AND :key.scr_fair_val in ('N','A')
AND :key.scr_asset_cat = 'A'
AND :key.scr_herit_asset in ('N','A') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'Y' then
IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSIF :key.scr_depr_sched = 'C'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
/* IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N')
OR ws_found = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I' then
IF ws_found = 'Y'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'C' then
IF ws_found = 'Y'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF; */
IF :key.scr_rec_dte_fr is not null then
IF (:key.scr_rec_dte_fr = ws_act_rv_dte
OR :key.scr_rec_dte_fr < ws_act_rv_dte)
AND ws_found = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
IF :key.scr_rec_dte_to is not null then
IF (:key.scr_rec_dte_to >= ws_act_rv_dte
AND ws_found = 'Y') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
IF ws_found = 'Y' then
IF :key.scr_purch_price = 'A' then
null;
ELSIF
:key.scr_purch_price = 'Y' then
IF ws_purch_price is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_purch_price = 'N' then
IF ws_purch_price is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_purch_price in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_fair_val = 'A' then
null;
ELSIF
:key.scr_fair_val = 'Y' then
IF ws_fair_val is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_fair_val = 'N' then
IF ws_fair_val is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_fair_val in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_asset_cat = 'A' then
IF ws_ass_cat ='MJ' or
ws_ass_cat = 'MN' then
null;
END IF;
ELSIF
:key.scr_asset_cat = 'MJ' then
IF ws_ass_cat ='MJ' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_asset_cat = 'MN' then
IF ws_ass_cat = 'MN' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset = 'A' then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_herit_asset = 'A' then
null;
ELSIF
:key.scr_herit_asset = 'Y' then
IF ws_heritage_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_herit_asset = 'N' then
IF ws_heritage_ind = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
ws_ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
-- raise form_trigger_failure; */
END;
-- end if;
<< nxt_rec >>
null;
ws_cnt:= ws_cnt + 1;
o_cnt := ws_cnt;
END LOOP;
END;
END;
END IF;
END;
END;
ASKER
there is no error message am just not able to insert into temp table after executing the procedure there is still no data in temp table
ASKER
the error is the report is not printing because the temp table is empty so i have to sort this out first the report select value from temp table
Capture12321.PNG
Capture12321.PNG
You are likely hitting one of the exceptions.
You have a null exception handler.
When the insert fails, the entire insert fails and is rolled back not just the duplicate values.
Comment out the exception handlers for the inserts and see if it raises an exception.
You have a null exception handler.
When the insert fails, the entire insert fails and is rolled back not just the duplicate values.
Comment out the exception handlers for the inserts and see if it raises an exception.
ASKER
ASKER
the exception in the insert does not give any error
That message would indicate that one of your select statements returns no rows.
ASKER
but when I run the sql in sql developer I do get row
ASKER
this is the sql
BEGIN
For rec1 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where rec1.scin = scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte _fr,'1900/ 01/01'))
AND TRUNC(NVL(:key.scr_rec_dte _to,SYSDAT E));*/
and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr _rec_dte_f r, 'yyyy/mm/dd'),to_date('190 0/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr _rec_dte_t o, 'yyyy/mm/dd'),SYSDATE));
ws_found := 'Y';
EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;
END;
BEGIN
For rec1 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where rec1.scin = scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte
AND TRUNC(NVL(:key.scr_rec_dte
and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr
AND TRUNC(NVL(to_date(:key.scr
ws_found := 'Y';
EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;
END;
That select statement has an exception that handles a NO_DATA_FOUND exception. It would have to be a statement that doesn't have a handler for that error.
ASKER
am not passing this value althou
(:key.scr_rec_dte_fr
(:key.scr_rec_dte_to
when I test I put null and it return value
(:key.scr_rec_dte_fr
(:key.scr_rec_dte_to
when I test I put null and it return value
ASKER
what do you mean It would have to be a statement that doesn't have a handler for that error.
It has to be a select statement that doesn't have an exception block that handles a NO_DATA_FOUND exception. The statement you posted has a exception block that handles that error, so that is not the statement that is causing the error. By my quick count, I see about 12 select statements in what you posted, check each one for a NO_DATA_FOUND exception. I know I saw at least 2 that didn't seem to be covered by an exception handler.
ASKER
I did this
nothing happen no error it just go through but does not insert
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
exception
when no_data_found then
null;
END;
nothing happen no error it just go through but does not insert
So that would be the select that returns no rows. You are just ignoring the error. Is that the intended result? If not, then there is likely an error in your logic.
ASKER
am not ignoring the error is just i don't know where is the error this select statements return rows in my sql developer
You are ignoring the error:
When you have a null exception handler, you tell Oracle that you don't care about that error and to just keep processing.
>>select statements return rows in my sql developer
To return rows in SQL Deverloper means that you have likely hard-coded the values for the variables. When the code runs in the form, one or more of the variables you think has a value, probably does not.
exception
when no_data_found then
null;
When you have a null exception handler, you tell Oracle that you don't care about that error and to just keep processing.
>>select statements return rows in my sql developer
To return rows in SQL Deverloper means that you have likely hard-coded the values for the variables. When the code runs in the form, one or more of the variables you think has a value, probably does not.
ASKER
what do you mean am ignoring the error
this was not there before I just put it now but still not able to insert what exception must I put
exception
when no_data_found then
null;
this was not there before I just put it now but still not able to insert what exception must I put
ASKER
hi I know where is the problem now is in this second block
it does select value in cursor but the second select is not getting value from curso I don't know why is there a why I can check how or why
BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
For rec01 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where rec01.scin = a.scin
AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
ws_found := 'Y';
exception
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;
END;
it does select value in cursor but the second select is not getting value from curso I don't know why is there a why I can check how or why
ASKER
somehow is selecting the value from the coursor but not selecting tham in the second query,because when i deburg i get 260 record which are value from the corsor but the second select only have 8 records
By "ignoring an error" we mean that you are trapping the error and not doing anything with it. Typically that is not what you want to do, but this is your logic, we don't know what the right action is. If doing nothing when that query returns no rows is the correct thing to do, then fine.
Usually, in a NO_DATA_FOUND exception, you are setting some or all of the variables being selected into to some default value or NULL.
Usually, in a NO_DATA_FOUND exception, you are setting some or all of the variables being selected into to some default value or NULL.
ASKER
ok the exception is not a problem the block above is the problem
I see you've opened a new question on this. Where do you want to continue the discussion, here or in the new one?
ASKER
we can continue hear must I close the other one but I have post something there
I'll repost my responses here. Post in the other question when you would like it deleted and I can take care of that.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi this is the desc of table
SQL> desc ils.sss_entry_index
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
SCIN NUMBER(11)
SIN NUMBER(11)
SER_NO VARCHAR2(30)
SIN_CHANGE_REAS VARCHAR2(6) Y
SIN_CHANGE_DTE DATE Y
all field in the cursor select are been used they may not be be used in the second select statement but they are been used in logic like belo you can check the whole procedure
IF rec01.srv_status_cde = 'C02' then
ws_ser_no := rec01.nha_ser_no;
ws_inst_ser_no := rec01.ser_no;
ws_pers_located_at := rec01.pers_located_at;
if you what other table desc you can check hear
https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
SQL> desc ils.sss_entry_index
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
SCIN NUMBER(11)
SIN NUMBER(11)
SER_NO VARCHAR2(30)
SIN_CHANGE_REAS VARCHAR2(6) Y
SIN_CHANGE_DTE DATE Y
all field in the cursor select are been used they may not be be used in the second select statement but they are been used in logic like belo you can check the whole procedure
IF rec01.srv_status_cde = 'C02' then
ws_ser_no := rec01.nha_ser_no;
ws_inst_ser_no := rec01.ser_no;
ws_pers_located_at := rec01.pers_located_at;
if you what other table desc you can check hear
https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi thanks for helping i have found something when i hard code this scin 100027816 am geting no data found but when i hard code other scin it goes through but does not insert in tem table
i have created this procedure for testing to see whats happening
PROCEDURE PROC_SELECT_DATA_test (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde| |me_var_cd e mec,
i.nsc||'-'||substr(i.niin, 1,2)||'-'| |
substr(i.niin,3,3)||'-'||s ubstr(i.ni in,6,4) nsn,
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(a.scin) = trim(rec1.scin)
-- where scin = 102672956
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte _fr,'1900/ 01/01'))
AND TRUNC(NVL(:key.scr_rec_dte _to,SYSDAT E));*/
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr _rec_dte_f r, 'yyyy/mm/dd'),to_date('190 0/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr _rec_dte_t o, 'yyyy/mm/dd'),SYSDATE))and (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/
AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/ 01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to _date('190 0/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SY SDATE);
ws_found := 'Y';
/*EXCEPTION
when others then
null;*/
exception
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
('l01075408',
20160909,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
END;
this procedure is not using anything from form you can test it mybe you will see what is the problem the database script are at
https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
i have created this procedure for testing to see whats happening
PROCEDURE PROC_SELECT_DATA_test (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde|
i.nsc||'-'||substr(i.niin,
substr(i.niin,3,3)||'-'||s
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(a.scin) = trim(rec1.scin)
-- where scin = 102672956
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte
AND TRUNC(NVL(:key.scr_rec_dte
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr
AND TRUNC(NVL(to_date(:key.scr
AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to
ws_found := 'Y';
/*EXCEPTION
when others then
null;*/
exception
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
('l01075408',
20160909,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
END;
this procedure is not using anything from form you can test it mybe you will see what is the problem the database script are at
https://drive.google.com/open?id=0B3dKl5pXBYMqdXM2ZXduRUU4VTA
You shouldn't need the TRIM function. I was thinking that if the column was a CHAR or VARCHAR2, you might has some spaces in the data. Since it a number, no need to TRIM.
I missed this initially, you have another variable in the where clause: s.ser_no = :key.scr_ser_no Make sure that value is what you think it is.
Anyway, back to the question:
The 'blank' record for that scin could be caused by bad data. Something that needs to join up for that specific record doesn't have the correct foreign key references in the other tables.
I missed this initially, you have another variable in the where clause: s.ser_no = :key.scr_ser_no Make sure that value is what you think it is.
Anyway, back to the question:
The 'blank' record for that scin could be caused by bad data. Something that needs to join up for that specific record doesn't have the correct foreign key references in the other tables.
ASKER
this where s.ser_no = :key.scr_ser_no you can leave it null for now am not even passing it when testing
you can do this where s.ser_no is null
you can do this where s.ser_no is null
First: I'm not testing anything.
>>you can leave it null for now am not even passing it when testing
That could be the issue when you test. If you don't pass in the value and it is null and you have the '=' in your test code, then nothing will be returned.
>>you can leave it null for now am not even passing it when testing
That could be the issue when you test. If you don't pass in the value and it is null and you have the '=' in your test code, then nothing will be returned.
ASKER
so i must do like this
where s.ser_no = :key.scr_ser_no or :key.scr_ser_no is null
where s.ser_no = :key.scr_ser_no or :key.scr_ser_no is null
I don't know your system so I cannot say what you need to do.
If :key.scr_ser_no can be null then yes, you need you check for null in addition to a value.
Be careful of your operator precedence. You'll need to group the OR:
where ( s.ser_no = :key.scr_ser_no or :key.scr_ser_no is null) and ...
If :key.scr_ser_no can be null then yes, you need you check for null in addition to a value.
Be careful of your operator precedence. You'll need to group the OR:
where ( s.ser_no = :key.scr_ser_no or :key.scr_ser_no is null) and ...
ASKER
that not a problem the cursor which making problem problem is this on
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e,
ils.lsst_asset_fin fin
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and fin.scin = b.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and b.scin = 106235476
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
I thought you said above the cursor wasn't the problem that it was the select inside the cursor loop that was the problem?
ASKER
the problem is in this part
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e,
ils.lsst_asset_fin fin
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and fin.scin = b.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and b.scin = 106235476
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(a.scin) = trim(rec1.scin)
-- where scin = 102672956
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/
AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
ws_found := 'Y';
/*EXCEPTION
when others then
null;*/
exception
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
ASKER
the original procedure
is
must i hire you to help me
is
BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
For rec1 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where rec1.scin = scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));
ws_found := 'Y';
EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;
END;
must i hire you to help me
>>must i hire you to help me
I've provided all the help I can. I don't have Forms. I haven't coded Forms for over 20 years.
I can help from a general SQL aspect and I've done all of that I can.
The issue has to be one of the two things I mentioned above. I cannot think of any other possibility.
I'll send out a call for help to see if I can get additional Experts to see if they can think of anything else.
I've provided all the help I can. I don't have Forms. I haven't coded Forms for over 20 years.
I can help from a general SQL aspect and I've done all of that I can.
The issue has to be one of the two things I mentioned above. I cannot think of any other possibility.
I'll send out a call for help to see if I can get additional Experts to see if they can think of anything else.
If the problem is in the block you mentioned , debug it. Put messages on strategic places (or use a procedure that write to os-file or using a discrete transaction to a database file) and check where the flow does not behave like you expect.
Are you sure that the problem is not the fact the insert into a temporary table happens in one database session (from an Oracle Form) and your report may be running in a separate Oracle Session. If that is true, the insert in the Forms session may succeed but the report will never see it, because the contents of global temporary tables are never visible across database sessions.
When I use global temporary tables with Oracle Reports, I only use them inside the Oracle report (like in a before-report or after pform trigger).
When I use global temporary tables with Oracle Reports, I only use them inside the Oracle report (like in a before-report or after pform trigger).
Unless something changed, I don't think it a global temp table.
Maybe I am blind but in your code (post ID: 41541578) the cursor ser_data is not opened.
ASKER
you mean I must do this
open ser_data ;
For rec1 in ser_data LOOP
open ser_data ;
For rec1 in ser_data LOOP
No, I would try it with impilcit cursor (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS553)
FOR rec1 IN (select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin, 1,2)||'-'| |
substr(c.niin,3,3)||'-'||s ubstr(c.ni in,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_ cde||e.me_ var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no)
LOOP
...
END LOOP;
FOR rec1 IN (select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,
substr(c.niin,3,3)||'-'||s
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no)
LOOP
...
END LOOP;
ASKER
am getting this error
this is the whole code where must I put rec01
this is the whole code where must I put rec01
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
BEGIN
ws_unit_cde := 0;
IF :key.wrk_org_func = 'HQ' or
(:key.wrk_org_func = 'CM' and
:key.scr_all_units = 'Y') THEN
ws_unit_cde := 0;
ws_eng_abbr_nme := '';
ELSE
ws_unit_cde := :key.scr_req_unit;
ws_eng_abbr_nme := :key.scr_unit_desc;
END IF;
-- message('testt09');
-- message(:key.scr_ser_no);
message('cmd = '||:key.wrk_comnd_cde );
message('unitcde = '||ws_unit_cde);
IF :key.scr_ser_no is not null then
DECLARE
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde||me_var_cde mec,
i.nsc||'-'||substr(i.niin,1,2)||'-'||
substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(a.scin) = trim(rec1.scin)
-- where scin = 102672956
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/
AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
ws_found := 'Y';
/*EXCEPTION
when others then
null;*/
exception
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
END;
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found THEN
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
BEGIN
select ser_no
into ws_ser_no
from ils.sss_entry_index
where scin = rec1.scin
and SIN_CHANGE_DTE is null;
EXCEPTION
when no_data_found then
message('No serial number for this ICN');
raise form_trigger_failure;
END;
wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
wrk_nsn_desc := null;
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);
-- wrk_nsn := nvl(wrk_nsn,'');
message('error12');
if :key.SCR_ICN_2ND_2 is not null then
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
end if;
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);
/*,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);*/
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
-- raise form_trigger_failure;
END;
-- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
null;
o_cnt := ws_cnt;
--message('ws_cnt:'||ws_cnt);pause;
END LOOP;
END;
ELSE
[b]BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e,
ils.lsst_asset_fin fin
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and fin.scin = b.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
--and b.scin = 20202029920
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
For rec01 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
nvl(close_reason,'2222')
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(rec01.scin) = trim(a.scin)
AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
[/b]
ws_found := 'Y';
/* EXCEPTION
when others then
null;*/
/* exception
when no_data_found then
ws_found := 'N';
goto nxt_rec;
null;*/
END;
--message('ws_scin from table: '||ws_scin);pause;
ws_ser_no := '';
ws_inst_ser_no := '';
IF rec01.srv_status_cde = 'C02' then
ws_ser_no := rec01.nha_ser_no;
ws_inst_ser_no := rec01.ser_no;
ws_pers_located_at := rec01.pers_located_at;
BEGIN
select a.scin,
a.sin,
b.srv_status_cde,
b.unit_holding,
nvl(b.dept_holding,0),
nvl(b.unit_located_at,0),
b.cond_cde,
lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
c.cntr_body_cde,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
into ws_scin,
ws_sin,
ws_srv_status_cde,
ws_unit_holding,
ws_dept_holding,
ws_unit_located_at,
ws_cond_cde,
ws_nsn,
ws_itm_type_cde,
ws_struc_lev_cde,
ws_agency_ser_cntrl_ind,
ws_cntr_body_cde,
ws_mec,
ws_me_descr
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.ser_no = rec01.nha_ser_no
and a.sin = rec01.nha_sin
and a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'));
exception
when no_data_found then
ws_ser_no := 'INVALID - '||ws_ser_no;
ws_srv_status_cde := '';
END;
ELSE
ws_scin := rec01.scin;
ws_sin := rec01.sin;
ws_srv_status_cde := rec01.srv_status_cde;
ws_unit_holding := nvl(rec01.unit_holding,0);
ws_dept_holding := nvl(rec01.dept_holding,0);
ws_unit_located_at := nvl(rec01.unit_located_at,0);
ws_cond_cde := rec01.cond_cde;
ws_pers_located_at := rec01.pers_located_at;
ws_nsn := rec01.nsn;
ws_itm_type_cde := rec01.itm_type_cde;
ws_struc_lev_cde := rec01.struc_lev_cde;
ws_agency_ser_cntrl_ind := rec01.agency_ser_cntrl_ind;
ws_mec := rec01.mec;
ws_me_descr := rec01.me_desc;
ws_ser_no := rec01.ser_no;
ws_inst_ser_no := '';
END IF;
-- Set up values for print table
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found then
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
-- NSN Description
wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
wrk_nsn_desc := null;
/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not null then
-- message('insideif');
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
-- end if;
--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
elsif ws_found = 'N' then
IF :key.scr_depr_sched in ('N','A') then
IF :key.scr_purch_price in ('N','A')
AND :key.scr_fair_val in ('N','A')
AND :key.scr_asset_cat = 'A'
AND :key.scr_herit_asset in ('N','A') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'Y' then
IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSIF :key.scr_depr_sched = 'C'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted32');
/* IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N')
OR ws_found = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I' then
IF ws_found = 'Y'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'C' then
IF ws_found = 'Y'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF; */
IF :key.scr_rec_dte_fr is not null then
IF (:key.scr_rec_dte_fr = ws_act_rv_dte
OR :key.scr_rec_dte_fr < ws_act_rv_dte)
AND ws_found = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
message('inserted123');
IF :key.scr_rec_dte_to is not null then
IF (:key.scr_rec_dte_to >= ws_act_rv_dte
AND ws_found = 'Y') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
message('inserted12');
IF ws_found = 'Y' then
IF :key.scr_purch_price = 'A' then
null;
ELSIF
:key.scr_purch_price = 'Y' then
IF ws_purch_price is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_purch_price = 'N' then
IF ws_purch_price is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_purch_price in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted1');
IF ws_found = 'Y' then
IF :key.scr_fair_val = 'A' then
null;
ELSIF
:key.scr_fair_val = 'Y' then
IF ws_fair_val is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_fair_val = 'N' then
IF ws_fair_val is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_fair_val in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted');
IF ws_found = 'Y' then
IF :key.scr_asset_cat = 'A' then
IF ws_ass_cat ='MJ' or
ws_ass_cat = 'MN' then
null;
END IF;
ELSIF
:key.scr_asset_cat = 'MJ' then
IF ws_ass_cat ='MJ' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_asset_cat = 'MN' then
IF ws_ass_cat = 'MN' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset = 'A' then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_herit_asset = 'A' then
null;
ELSIF
:key.scr_herit_asset = 'Y' then
IF ws_heritage_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_herit_asset = 'N' then
IF ws_heritage_ind = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted');
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
ws_ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
-- raise form_trigger_failure; */
END;
-- end if;
message('itcomes');
message('scin1 = '||rec01.scin);
<< nxt_rec >>
null;
ws_cnt:= ws_cnt + 1;
o_cnt := ws_cnt;
END LOOP;
END;
END;
-- message('itcomes22');
END IF;
-- message('itcomes9999');
END;
--message('itcome78855s');
END;
Please, don't use INTO here
FOR rec1 IN (scin,
itm_purch_prc ,
It must be done this way:
FOR rec1 IN (scin,
itm_purch_prc,
...
LOOP
ws_scin:=rec1.scin;
ws_itm_purch_prc:=rec1.itm _purch_prc ;
...
END LOOP;
FOR rec1 IN (scin,
itm_purch_prc ,
It must be done this way:
FOR rec1 IN (scin,
itm_purch_prc,
...
LOOP
ws_scin:=rec1.scin;
ws_itm_purch_prc:=rec1.itm
...
END LOOP;
ASKER
am getting this error
this is the latest code
this is the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
BEGIN
ws_unit_cde := 0;
IF :key.wrk_org_func = 'HQ' or
(:key.wrk_org_func = 'CM' and
:key.scr_all_units = 'Y') THEN
ws_unit_cde := 0;
ws_eng_abbr_nme := '';
ELSE
ws_unit_cde := :key.scr_req_unit;
ws_eng_abbr_nme := :key.scr_unit_desc;
END IF;
-- message('testt09');
-- message(:key.scr_ser_no);
message('cmd = '||:key.wrk_comnd_cde );
message('unitcde = '||ws_unit_cde);
IF :key.scr_ser_no is not null then
DECLARE
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde||me_var_cde mec,
i.nsc||'-'||substr(i.niin,1,2)||'-'||
substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(a.scin) = trim(rec1.scin)
-- where scin = 102672956
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/
AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
ws_found := 'Y';
/*EXCEPTION
when others then
null;*/
exception
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
END;
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found THEN
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
BEGIN
select ser_no
into ws_ser_no
from ils.sss_entry_index
where scin = rec1.scin
and SIN_CHANGE_DTE is null;
EXCEPTION
when no_data_found then
message('No serial number for this ICN');
raise form_trigger_failure;
END;
wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
wrk_nsn_desc := null;
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);
-- wrk_nsn := nvl(wrk_nsn,'');
message('error12');
if :key.SCR_ICN_2ND_2 is not null then
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
end if;
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);
/*,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);*/
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
-- raise form_trigger_failure;
END;
-- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
null;
o_cnt := ws_cnt;
--message('ws_cnt:'||ws_cnt);pause;
END LOOP;
END;
ELSE
BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e,
ils.lsst_asset_fin fin
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and fin.scin = b.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
--and b.scin = 20202029920
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
for rec1 in (scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
--nvl(close_reason,'2222')
-- from ils.lsst_asset_fin a
-- where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE)
-- where trim(ser_data.scin) = trim(a.scin);
loop
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1. accum_impair_val;
ws_accum_impair_val := rec1. useful_lfe;
ws_useful_lfe ;= rec1.ass_cat;
ws_rem_useful_lfe := rec1.act_rv_dte;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
/* For rec01 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
nvl(close_reason,'2222')
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(rec01.scin) = trim(a.scin)
-- where scin = 20202029920
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
/* and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));*/
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
/* and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/
message('scin03212+++ = '||rec01.scin);
message('ws_close_reason = '||ws_close_reason);
message('ws_itm_purch_prc = '||ws_itm_purch_prc);
ws_found := 'Y';
/* EXCEPTION
when others then
null;*/
/* exception
when no_data_found then
ws_found := 'N';
goto nxt_rec;
null;*/
END;
--message('ws_scin from table: '||ws_scin);pause;
ws_ser_no := '';
ws_inst_ser_no := '';
IF rec01.srv_status_cde = 'C02' then
ws_ser_no := rec01.nha_ser_no;
ws_inst_ser_no := rec01.ser_no;
ws_pers_located_at := rec01.pers_located_at;
BEGIN
select a.scin,
a.sin,
b.srv_status_cde,
b.unit_holding,
nvl(b.dept_holding,0),
nvl(b.unit_located_at,0),
b.cond_cde,
lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
c.cntr_body_cde,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
into ws_scin,
ws_sin,
ws_srv_status_cde,
ws_unit_holding,
ws_dept_holding,
ws_unit_located_at,
ws_cond_cde,
ws_nsn,
ws_itm_type_cde,
ws_struc_lev_cde,
ws_agency_ser_cntrl_ind,
ws_cntr_body_cde,
ws_mec,
ws_me_descr
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.ser_no = rec01.nha_ser_no
and a.sin = rec01.nha_sin
and a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'));
exception
when no_data_found then
ws_ser_no := 'INVALID - '||ws_ser_no;
ws_srv_status_cde := '';
END;
ELSE
ws_scin := rec01.scin;
ws_sin := rec01.sin;
ws_srv_status_cde := rec01.srv_status_cde;
ws_unit_holding := nvl(rec01.unit_holding,0);
ws_dept_holding := nvl(rec01.dept_holding,0);
ws_unit_located_at := nvl(rec01.unit_located_at,0);
ws_cond_cde := rec01.cond_cde;
ws_pers_located_at := rec01.pers_located_at;
ws_nsn := rec01.nsn;
ws_itm_type_cde := rec01.itm_type_cde;
ws_struc_lev_cde := rec01.struc_lev_cde;
ws_agency_ser_cntrl_ind := rec01.agency_ser_cntrl_ind;
ws_mec := rec01.mec;
ws_me_descr := rec01.me_desc;
ws_ser_no := rec01.ser_no;
ws_inst_ser_no := '';
END IF;
-- Set up values for print table
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found then
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
-- NSN Description
wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
wrk_nsn_desc := null;
/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not null then
-- message('insideif');
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
-- end if;
--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
elsif ws_found = 'N' then
IF :key.scr_depr_sched in ('N','A') then
IF :key.scr_purch_price in ('N','A')
AND :key.scr_fair_val in ('N','A')
AND :key.scr_asset_cat = 'A'
AND :key.scr_herit_asset in ('N','A') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'Y' then
IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSIF :key.scr_depr_sched = 'C'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted32');
/* IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N')
OR ws_found = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I' then
IF ws_found = 'Y'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'C' then
IF ws_found = 'Y'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF; */
IF :key.scr_rec_dte_fr is not null then
IF (:key.scr_rec_dte_fr = ws_act_rv_dte
OR :key.scr_rec_dte_fr < ws_act_rv_dte)
AND ws_found = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
message('inserted123');
IF :key.scr_rec_dte_to is not null then
IF (:key.scr_rec_dte_to >= ws_act_rv_dte
AND ws_found = 'Y') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
message('inserted12');
IF ws_found = 'Y' then
IF :key.scr_purch_price = 'A' then
null;
ELSIF
:key.scr_purch_price = 'Y' then
IF ws_purch_price is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_purch_price = 'N' then
IF ws_purch_price is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_purch_price in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted1');
IF ws_found = 'Y' then
IF :key.scr_fair_val = 'A' then
null;
ELSIF
:key.scr_fair_val = 'Y' then
IF ws_fair_val is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_fair_val = 'N' then
IF ws_fair_val is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_fair_val in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted');
IF ws_found = 'Y' then
IF :key.scr_asset_cat = 'A' then
IF ws_ass_cat ='MJ' or
ws_ass_cat = 'MN' then
null;
END IF;
ELSIF
:key.scr_asset_cat = 'MJ' then
IF ws_ass_cat ='MJ' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_asset_cat = 'MN' then
IF ws_ass_cat = 'MN' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset = 'A' then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_herit_asset = 'A' then
null;
ELSIF
:key.scr_herit_asset = 'Y' then
IF ws_heritage_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_herit_asset = 'N' then
IF ws_heritage_ind = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted');
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
ws_ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
-- raise form_trigger_failure; */
END;
-- end if;
message('itcomes');
message('scin1 = '||rec01.scin);
<< nxt_rec >>
null;
ws_cnt:= ws_cnt + 1;
o_cnt := ws_cnt;
END LOOP;
/* message('itcomes2233');
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);*/
END;
/* message('itcomes112');
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);*/
END;
-- message('itcomes22');
END IF;
-- message('itcomes9999');
END;
--message('itcome78855s');
END;
There is missing SELECT and FROM:
for rec1 in (SELECT scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
from ils.lsst_asset_fin a)
--nvl(close_reason,'2222')
-- from ils.lsst_asset_fin a
-- where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/ 01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE)
-- where trim(ser_data.scin) = trim(a.scin);
loop
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1. accum_impair_val;
ws_accum_impair_val := rec1. useful_lfe;
ws_useful_lfe ;= rec1.ass_cat;
ws_rem_useful_lfe := rec1.act_rv_dte;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
for rec1 in (SELECT scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
from ils.lsst_asset_fin a)
--nvl(close_reason,'2222')
-- from ils.lsst_asset_fin a
-- where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/
-- where trim(ser_data.scin) = trim(a.scin);
loop
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1. accum_impair_val;
ws_accum_impair_val := rec1. useful_lfe;
ws_useful_lfe ;= rec1.ass_cat;
ws_rem_useful_lfe := rec1.act_rv_dte;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
ASKER
where do I close the loop am getting this error now
this si the latest code
this si the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
BEGIN
ws_unit_cde := 0;
IF :key.wrk_org_func = 'HQ' or
(:key.wrk_org_func = 'CM' and
:key.scr_all_units = 'Y') THEN
ws_unit_cde := 0;
ws_eng_abbr_nme := '';
ELSE
ws_unit_cde := :key.scr_req_unit;
ws_eng_abbr_nme := :key.scr_unit_desc;
END IF;
-- message('testt09');
-- message(:key.scr_ser_no);
message('cmd = '||:key.wrk_comnd_cde );
message('unitcde = '||ws_unit_cde);
IF :key.scr_ser_no is not null then
DECLARE
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde||me_var_cde mec,
i.nsc||'-'||substr(i.niin,1,2)||'-'||
substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(a.scin) = trim(rec1.scin)
-- where scin = 102672956
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/
AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
ws_found := 'Y';
/*EXCEPTION
when others then
null;*/
exception
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
END;
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found THEN
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
BEGIN
select ser_no
into ws_ser_no
from ils.sss_entry_index
where scin = rec1.scin
and SIN_CHANGE_DTE is null;
EXCEPTION
when no_data_found then
message('No serial number for this ICN');
raise form_trigger_failure;
END;
wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
wrk_nsn_desc := null;
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);
-- wrk_nsn := nvl(wrk_nsn,'');
message('error12');
if :key.SCR_ICN_2ND_2 is not null then
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
end if;
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);
/*,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);*/
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
-- raise form_trigger_failure;
END;
-- end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
null;
o_cnt := ws_cnt;
--message('ws_cnt:'||ws_cnt);pause;
END LOOP;
END;
ELSE
BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e,
ils.lsst_asset_fin fin
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and fin.scin = b.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
--and b.scin = 20202029920
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
for rec1 in (select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
--nvl(close_reason,'2222')
from ils.lsst_asset_fin a
where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE))
-- where trim(ser_data.scin) = trim(a.scin)
loop
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1. accum_impair_val;
ws_accum_impair_val := rec1. useful_lfe;
ws_useful_lfe := rec1.ass_cat;
ws_rem_useful_lfe := rec1.act_rv_dte;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
/* For rec01 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
nvl(close_reason,'2222')
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where trim(rec01.scin) = trim(a.scin)
-- where scin = 20202029920
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
/* and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));*/
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
-- AND TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE);
/* and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE))and (:key.scr_rec_dte_fr is null or :key.scr_rec_dte_to is null);*/
message('scin03212+++ = '||rec01.scin);
message('ws_close_reason = '||ws_close_reason);
message('ws_itm_purch_prc = '||ws_itm_purch_prc);
ws_found := 'Y';
/* EXCEPTION
when others then
null;*/
/* exception
when no_data_found then
ws_found := 'N';
goto nxt_rec;
null;*/
END;
--message('ws_scin from table: '||ws_scin);pause;
ws_ser_no := '';
ws_inst_ser_no := '';
IF rec01.srv_status_cde = 'C02' then
ws_ser_no := rec01.nha_ser_no;
ws_inst_ser_no := rec01.ser_no;
ws_pers_located_at := rec01.pers_located_at;
BEGIN
select a.scin,
a.sin,
b.srv_status_cde,
b.unit_holding,
nvl(b.dept_holding,0),
nvl(b.unit_located_at,0),
b.cond_cde,
lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
c.cntr_body_cde,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
into ws_scin,
ws_sin,
ws_srv_status_cde,
ws_unit_holding,
ws_dept_holding,
ws_unit_located_at,
ws_cond_cde,
ws_nsn,
ws_itm_type_cde,
ws_struc_lev_cde,
ws_agency_ser_cntrl_ind,
ws_cntr_body_cde,
ws_mec,
ws_me_descr
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.ser_no = rec01.nha_ser_no
and a.sin = rec01.nha_sin
and a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'));
exception
when no_data_found then
ws_ser_no := 'INVALID - '||ws_ser_no;
ws_srv_status_cde := '';
END;
ELSE
ws_scin := rec01.scin;
ws_sin := rec01.sin;
ws_srv_status_cde := rec01.srv_status_cde;
ws_unit_holding := nvl(rec01.unit_holding,0);
ws_dept_holding := nvl(rec01.dept_holding,0);
ws_unit_located_at := nvl(rec01.unit_located_at,0);
ws_cond_cde := rec01.cond_cde;
ws_pers_located_at := rec01.pers_located_at;
ws_nsn := rec01.nsn;
ws_itm_type_cde := rec01.itm_type_cde;
ws_struc_lev_cde := rec01.struc_lev_cde;
ws_agency_ser_cntrl_ind := rec01.agency_ser_cntrl_ind;
ws_mec := rec01.mec;
ws_me_descr := rec01.me_desc;
ws_ser_no := rec01.ser_no;
ws_inst_ser_no := '';
END IF;
-- Set up values for print table
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found then
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
-- NSN Description
wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
wrk_nsn_desc := null;
/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn := nvl( wrk_nsn ,'');-message('error12');
if :key.SCR_ICN_2ND_2 is not null then
-- message('insideif');
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
-- end if;
--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
elsif ws_found = 'N' then
IF :key.scr_depr_sched in ('N','A') then
IF :key.scr_purch_price in ('N','A')
AND :key.scr_fair_val in ('N','A')
AND :key.scr_asset_cat = 'A'
AND :key.scr_herit_asset in ('N','A') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'Y' then
IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSIF :key.scr_depr_sched = 'C'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted32');
/* IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N')
OR ws_found = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I' then
IF ws_found = 'Y'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'C' then
IF ws_found = 'Y'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF; */
IF :key.scr_rec_dte_fr is not null then
IF (:key.scr_rec_dte_fr = ws_act_rv_dte
OR :key.scr_rec_dte_fr < ws_act_rv_dte)
AND ws_found = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
message('inserted123');
IF :key.scr_rec_dte_to is not null then
IF (:key.scr_rec_dte_to >= ws_act_rv_dte
AND ws_found = 'Y') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
message('inserted12');
IF ws_found = 'Y' then
IF :key.scr_purch_price = 'A' then
null;
ELSIF
:key.scr_purch_price = 'Y' then
IF ws_purch_price is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_purch_price = 'N' then
IF ws_purch_price is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_purch_price in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted1');
IF ws_found = 'Y' then
IF :key.scr_fair_val = 'A' then
null;
ELSIF
:key.scr_fair_val = 'Y' then
IF ws_fair_val is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_fair_val = 'N' then
IF ws_fair_val is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_fair_val in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted');
IF ws_found = 'Y' then
IF :key.scr_asset_cat = 'A' then
IF ws_ass_cat ='MJ' or
ws_ass_cat = 'MN' then
null;
END IF;
ELSIF
:key.scr_asset_cat = 'MJ' then
IF ws_ass_cat ='MJ' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_asset_cat = 'MN' then
IF ws_ass_cat = 'MN' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset = 'A' then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_herit_asset = 'A' then
null;
ELSIF
:key.scr_herit_asset = 'Y' then
IF ws_heritage_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_herit_asset = 'N' then
IF ws_heritage_ind = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
message('inserted');
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
ws_ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
-- raise form_trigger_failure; */
END;
-- end if;
message('itcomes');
message('scin1 = '||rec01.scin);
<< nxt_rec >>
null;
ws_cnt:= ws_cnt + 1;
o_cnt := ws_cnt;
END LOOP;
/* message('itcomes2233');
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);*/
END;
/* message('itcomes112');
message('values = '||:global.ils_force_no);
message('values = '||:key.wrk_dte);
message('value122 = '||ws_unit);
message('value123 = '|| wrk_nsn);*/
END;
-- message('itcomes22');
END IF;
-- message('itcomes9999');
END;
--message('itcome78855s');
END;
The code in the image that shows the error doesn't look like the code you posted in the code window.
There is no select and from in the code in the image.
There is no select and from in the code in the image.
There is rec01, but it is uncommented. I would recommend remove all uncommented parts.
ASKER
there is select
check attachment this is the latest code
I remove unnecessary comment parts
check attachment this is the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
BEGIN
ws_unit_cde := 0;
IF :key.wrk_org_func = 'HQ' or
(:key.wrk_org_func = 'CM' and
:key.scr_all_units = 'Y') THEN
ws_unit_cde := 0;
ws_eng_abbr_nme := '';
ELSE
ws_unit_cde := :key.scr_req_unit;
ws_eng_abbr_nme := :key.scr_unit_desc;
END IF;
-- message('testt09');
-- message(:key.scr_ser_no);
IF :key.scr_ser_no is not null then
DECLARE
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde||me_var_cde mec,
i.nsc||'-'||substr(i.niin,1,2)||'-'||
substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where scin = rec1.scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));
ws_found := 'Y';
EXCEPTION
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
END;
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found THEN
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
BEGIN
select ser_no
into ws_ser_no
from ils.sss_entry_index
where scin = rec1.scin
and SIN_CHANGE_DTE is null;
EXCEPTION
when no_data_found then
message('No serial number for this ICN');
raise form_trigger_failure;
END;
wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
wrk_nsn_desc := null;
-- wrk_nsn := nvl(wrk_nsn,'');
-- message('error12');
if wrk_nsn is not null then
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
-- raise form_trigger_failure;
END;
end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
null;
o_cnt := ws_cnt;
--message('ws_cnt:'||ws_cnt);pause;
END LOOP;
END;
ELSE
BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
for rec1 in (select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
--nvl(close_reason,'2222')
from ils.lsst_asset_fin a
where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE))
/* and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE)); */
-- where trim(ser_data.scin) = trim(a.scin)
loop
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1. accum_impair_val;
ws_accum_impair_val := rec1. useful_lfe;
ws_useful_lfe := rec1.ass_cat;
ws_rem_useful_lfe := rec1.act_rv_dte;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
ws_found := 'Y';
/*EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;*/
END;
--message('ws_scin from table: '||ws_scin);pause;
ws_ser_no := '';
ws_inst_ser_no := '';
IF rec1.srv_status_cde = 'C02' then
ws_ser_no := rec1.nha_ser_no;
ws_inst_ser_no := rec1.ser_no;
ws_pers_located_at := rec1.pers_located_at;
BEGIN
select a.scin, a.sin,
b.srv_status_cde,
b.unit_holding,
nvl(b.dept_holding,0),
nvl(b.unit_located_at,0),
b.cond_cde,
lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
c.cntr_body_cde,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
into ws_scin, ws_sin,
ws_srv_status_cde,
ws_unit_holding,
ws_dept_holding,
ws_unit_located_at,
ws_cond_cde,
ws_nsn,
ws_itm_type_cde,
ws_struc_lev_cde,
ws_agency_ser_cntrl_ind,
ws_cntr_body_cde,
ws_mec,
ws_me_descr
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.ser_no = rec1.nha_ser_no
and a.sin = rec1.nha_sin
and a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'));
exception
when no_data_found then
ws_ser_no := 'INVALID - '||ws_ser_no;
ws_srv_status_cde := '';
END;
ELSE
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_srv_status_cde := rec1.srv_status_cde;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_dept_holding := nvl(rec1.dept_holding,0);
ws_unit_located_at := nvl(rec1.unit_located_at,0);
ws_cond_cde := rec1.cond_cde;
ws_pers_located_at := rec1.pers_located_at;
ws_nsn := rec1.nsn;
ws_itm_type_cde := rec1.itm_type_cde;
ws_struc_lev_cde := rec1.struc_lev_cde;
ws_agency_ser_cntrl_ind := rec1.agency_ser_cntrl_ind;
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
END IF;
-- Set up values for print table
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found then
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
-- NSN Description
wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
wrk_nsn_desc := null;
if wrk_nsn is null then
-- message('insideif');
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
if ws_found = 'N' then
IF :key.scr_depr_sched in ('N','A') then
IF :key.scr_purch_price in ('N','A')
AND :key.scr_fair_val in ('N','A')
AND :key.scr_asset_cat = 'A'
AND :key.scr_herit_asset in ('N','A') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'Y' then
IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSIF :key.scr_depr_sched = 'C'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
/* IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N')
OR ws_found = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I' then
IF ws_found = 'Y'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'C' then
IF ws_found = 'Y'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF; */
IF :key.scr_rec_dte_fr is not null then
IF (:key.scr_rec_dte_fr = ws_act_rv_dte
OR :key.scr_rec_dte_fr < ws_act_rv_dte)
AND ws_found = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
IF :key.scr_rec_dte_to is not null then
IF (:key.scr_rec_dte_to >= ws_act_rv_dte
AND ws_found = 'Y') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
IF ws_found = 'Y' then
IF :key.scr_purch_price = 'A' then
null;
ELSIF
:key.scr_purch_price = 'Y' then
IF ws_purch_price is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_purch_price = 'N' then
IF ws_purch_price is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_purch_price in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_fair_val = 'A' then
null;
ELSIF
:key.scr_fair_val = 'Y' then
IF ws_fair_val is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_fair_val = 'N' then
IF ws_fair_val is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_fair_val in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_asset_cat = 'A' then
IF ws_ass_cat ='MJ' or
ws_ass_cat = 'MN' then
null;
END IF;
ELSIF
:key.scr_asset_cat = 'MJ' then
IF ws_ass_cat ='MJ' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_asset_cat = 'MN' then
IF ws_ass_cat = 'MN' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset = 'A' then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_herit_asset = 'A' then
null;
ELSIF
:key.scr_herit_asset = 'Y' then
IF ws_heritage_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_herit_asset = 'N' then
IF ws_heritage_ind = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
ws_ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
-- raise form_trigger_failure; */
END;
end if;
<< nxt_rec >>
null;
ws_cnt:= ws_cnt + 1;
o_cnt := ws_cnt;
END LOOP;
END;
END;
END IF;
END;
END;
I remove unnecessary comment parts
I'm not going to walk through about 1000 lines of code looking for a syntax error.
Look at the error message provided: It says it found the ';' when it expected 'IF'. That means that somewhere in the code you have an IF statement without and END IF and the compiler ran out of code to process before it found it.
Look at the error message provided: It says it found the ';' when it expected 'IF'. That means that somewhere in the code you have an IF statement without and END IF and the compiler ran out of code to process before it found it.
problem is END;
END LOOP; ought to be before END;, but i think that this END ought to be removed.
BEGIN
for rec1 in (select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
--nvl(close_reason,'2222')
from ils.lsst_asset_fin a
where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/ 01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE))
/* and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr _rec_dte_f r, 'yyyy/mm/dd'),to_date('190 0/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr _rec_dte_t o, 'yyyy/mm/dd'),SYSDATE)); */
-- where trim(ser_data.scin) = trim(a.scin)
loop
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1. accum_impair_val;
ws_accum_impair_val := rec1. useful_lfe;
ws_useful_lfe := rec1.ass_cat;
ws_rem_useful_lfe := rec1.act_rv_dte;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
ws_found := 'Y';
/*EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;*/
END;
slightwv is right, you have to do your job :)
END LOOP; ought to be before END;, but i think that this END ought to be removed.
BEGIN
for rec1 in (select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
--nvl(close_reason,'2222')
from ils.lsst_asset_fin a
where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/
/* and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr
AND TRUNC(NVL(to_date(:key.scr
-- where trim(ser_data.scin) = trim(a.scin)
loop
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1. accum_impair_val;
ws_accum_impair_val := rec1. useful_lfe;
ws_useful_lfe := rec1.ass_cat;
ws_rem_useful_lfe := rec1.act_rv_dte;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
ws_found := 'Y';
/*EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;*/
END;
slightwv is right, you have to do your job :)
ASKER
thats fair enough i did fix the error but its seems this solution its not going to solve my problem
this is the latest code
this is the latest code
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06);
wrk_gsb_unit number(06);
ws_unit number(06);
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
BEGIN
ws_unit_cde := 0;
IF :key.wrk_org_func = 'HQ' or
(:key.wrk_org_func = 'CM' and
:key.scr_all_units = 'Y') THEN
ws_unit_cde := 0;
ws_eng_abbr_nme := '';
ELSE
ws_unit_cde := :key.scr_req_unit;
ws_eng_abbr_nme := :key.scr_unit_desc;
END IF;
-- message('testt09');
-- message(:key.scr_ser_no);
IF :key.scr_ser_no is not null then
DECLARE
CURSOR sel_ser_no is
select s.sin,
s.ser_no,
e.scin,
e.unit_holding,
me_cat_cde||me_family_cde||me_var_cde mec,
i.nsc||'-'||substr(i.niin,1,2)||'-'||
substr(i.niin,3,3)||'-'||substr(i.niin,6,4) nsn,
i.agency_ser_cntrl_ind,
me_desc
from ils.me_master_rec m,
ils.sss_entry_index s,
ils.sss_er_core e,
ils.item_data i
where s.ser_no = :key.scr_ser_no
and e.scin = s.scin
and s.sin_change_reas is null
and s.sin_change_dte is null
and ((nvl(e.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and m.sin (+) = s.sin
and i.sin = s.sin
-- or s.sin = :key.wrk_sin)
and (m.me_life_cyc (+) != '08' and
m.me_rec_stat (+) not in ('C','X'));
BEGIN
FOR rec1 IN sel_ser_no
LOOP
BEGIN
select itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where scin = rec1.scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(to_date(:key.scr_rec_dte_fr, 'yyyy/mm/dd'),to_date('1900/01/01', 'yyyy/mm/dd')))
AND TRUNC(NVL(to_date(:key.scr_rec_dte_to, 'yyyy/mm/dd'),SYSDATE));
ws_found := 'Y';
EXCEPTION
when no_data_found THEN
ws_found := 'N';
-- goto nxt_rec;
null;
END;
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found THEN
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
BEGIN
select ser_no
into ws_ser_no
from ils.sss_entry_index
where scin = rec1.scin
and SIN_CHANGE_DTE is null;
EXCEPTION
when no_data_found then
message('No serial number for this ICN');
raise form_trigger_failure;
END;
wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
wrk_nsn_desc := null;
-- wrk_nsn := nvl(wrk_nsn,'');
-- message('error12');
if wrk_nsn is not null then
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
rec1.ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);
-- raise form_trigger_failure;
END;
end if;
ws_cnt := ws_cnt + 1;
<< nxt_rec >>
null;
o_cnt := ws_cnt;
--message('ws_cnt:'||ws_cnt);pause;
END LOOP;
END;
ELSE
BEGIN
DECLARE
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
For rec1 in (select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
from ils.lsst_asset_fin a
where TRUNC(a.act_rv_dte) BETWEEN NVL(null,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(null,SYSDATE))
LOOP
-- BEGIN
ws_scin := rec1.scin;
ws_itm_purch_prc := rec1.itm_purch_prc;
ws_heritage_ind := rec1.res_val;
ws_res_val := rec1.fair_val;
ws_fair_val := rec1.current_val;
ws_current_val := rec1.accum_improve_val;
ws_accum_improve_val := rec1.accum_impair_val;
ws_accum_impair_val := rec1.accum_impair_val;
ws_useful_lfe := rec1.useful_lfe;
ws_rem_useful_lfe := rec1.rem_useful_lfe;
ws_ass_cat := rec1.ass_cat;
ws_act_rv_dte := rec1.act_rv_dte;
ws_dep_active_ind := rec1.dep_active_ind;
ws_dep_closed_ind := rec1.dep_closed_ind;
ws_start_dep_dte := rec1.start_dep_dte;
ws_lst_dep_fin_YYYYMM := rec1.lst_dep_fin_YYYYMM;
ws_close_reason := rec1.close_reason;
ws_found := 'Y';
/*EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;*/
END;
--message('ws_scin from table: '||ws_scin);pause;
ws_ser_no := '';
ws_inst_ser_no := '';
IF rec1.srv_status_cde = 'C02' then
ws_ser_no := rec1.nha_ser_no;
ws_inst_ser_no := rec1.ser_no;
ws_pers_located_at := rec1.pers_located_at;
BEGIN
select a.scin, a.sin,
b.srv_status_cde,
b.unit_holding,
nvl(b.dept_holding,0),
nvl(b.unit_located_at,0),
b.cond_cde,
lpad(to_char(c.nsc),4,'0')||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
c.cntr_body_cde,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
into ws_scin, ws_sin,
ws_srv_status_cde,
ws_unit_holding,
ws_dept_holding,
ws_unit_located_at,
ws_cond_cde,
ws_nsn,
ws_itm_type_cde,
ws_struc_lev_cde,
ws_agency_ser_cntrl_ind,
ws_cntr_body_cde,
ws_mec,
ws_me_descr
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.ser_no = rec1.nha_ser_no
and a.sin = rec1.nha_sin
and a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'));
exception
when no_data_found then
ws_ser_no := 'INVALID - '||ws_ser_no;
ws_srv_status_cde := '';
END;
ELSE
ws_scin := rec1.scin;
ws_sin := rec1.sin;
ws_srv_status_cde := rec1.srv_status_cde;
ws_unit_holding := nvl(rec1.unit_holding,0);
ws_dept_holding := nvl(rec1.dept_holding,0);
ws_unit_located_at := nvl(rec1.unit_located_at,0);
ws_cond_cde := rec1.cond_cde;
ws_pers_located_at := rec1.pers_located_at;
ws_nsn := rec1.nsn;
ws_itm_type_cde := rec1.itm_type_cde;
ws_struc_lev_cde := rec1.struc_lev_cde;
ws_agency_ser_cntrl_ind := rec1.agency_ser_cntrl_ind;
ws_mec := rec1.mec;
ws_me_descr := rec1.me_desc;
ws_ser_no := rec1.ser_no;
ws_inst_ser_no := '';
END IF;
-- Set up values for print table
IF :key.scr_req_unit is null or
:key.wrk_org_func = 'HQ' then
ws_unit := ws_unit_holding;
ELSE
IF :key.scr_all_units = 'Y' then
ws_unit := 0;
BEGIN
select 'X'
into ws_dummy
from dual
where ws_unit_holding in (select unit_cde
from ils.unt
where comnd_cde = :key.wrk_comnd_cde);
ws_unit := ws_unit_holding;
exception
when no_data_found then
ws_unit := 0;
goto nxt_rec;
END;
ELSE
ws_unit := ws_unit_cde;
END IF;
END IF;
IF :key.scr_all_units = 'Y' then
wrk_gsb_unit := :key.scr_req_unit;
wrk_gsb_desc := :key.scr_unit_name;
ELSE
BEGIN
select unit_cde,
eng_abbr_nme
into wrk_gsb_unit,
wrk_gsb_desc
from ils.unt
where comnd_cde = :key.wrk_comnd_cde
and org_func = 'CM';
END;
END IF;
IF :key.wrk_full_nsn is not null then
IF :key.wrk_full_nsn = ws_nsn then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
IF :key.scr_me_cde is not null then
IF :key.scr_var_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde||:key.scr_var_cde = ws_mec then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_fam_cde is not null then
IF :key.scr_cat_cde||:key.scr_fam_cde = substr(ws_mec,1,2) then
null;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_cat_cde is not null then
IF :key.scr_cat_cde = substr(ws_mec,1,1) then
null;
ELSE
goto nxt_rec;
END IF;
END IF;
ELSE
:key.scr_me_cde := ws_mec;
:key.scr_mec_desc := ws_me_descr;
END IF;
-- Unit Description
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
-- NSN Description
wrk_nsn := substr(ws_nsn,1,4)||substr(ws_nsn,6,2)||
substr(ws_nsn,9,3)||substr(ws_nsn,13,4);
wrk_nsn_desc := null;
/*message('wrk_nsn = '||wrk_nsn);
message('wrk_nsn_desc = '||wrk_nsn_desc);
message('wrk_err = '||wrk_err);
message('wrk_msg = '||wrk_msg);*/
--message('error12344');
-- wrk_nsn := nvl( wrk_nsn ,'');
--message('error12');
if wrk_nsn is null then
-- message('insideif');
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
--message('afterif');
/** New additions/changes to Spec: 22/11/2010 - Marius**/
if ws_found = 'N' then
IF :key.scr_depr_sched in ('N','A') then
IF :key.scr_purch_price in ('N','A')
AND :key.scr_fair_val in ('N','A')
AND :key.scr_asset_cat = 'A'
AND :key.scr_herit_asset in ('N','A') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'Y' then
IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSIF :key.scr_depr_sched = 'C'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
/* IF :key.scr_depr_sched = 'A' then
null;
ELSIF :key.scr_depr_sched = 'N' then
IF (ws_dep_active_ind = 'N'
AND ws_dep_closed_ind = 'N')
OR ws_found = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'I' then
IF ws_found = 'Y'
AND ws_dep_active_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF :key.scr_depr_sched = 'C' then
IF ws_found = 'Y'
AND ws_dep_closed_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF; */
IF :key.scr_rec_dte_fr is not null then
IF (:key.scr_rec_dte_fr = ws_act_rv_dte
OR :key.scr_rec_dte_fr < ws_act_rv_dte)
AND ws_found = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
IF :key.scr_rec_dte_to is not null then
IF (:key.scr_rec_dte_to >= ws_act_rv_dte
AND ws_found = 'Y') then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
null;
END IF;
IF ws_found = 'Y' then
IF :key.scr_purch_price = 'A' then
null;
ELSIF
:key.scr_purch_price = 'Y' then
IF ws_purch_price is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_purch_price = 'N' then
IF ws_purch_price is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_purch_price in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_fair_val = 'A' then
null;
ELSIF
:key.scr_fair_val = 'Y' then
IF ws_fair_val is not null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_fair_val = 'N' then
IF ws_fair_val is null then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_fair_val in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_asset_cat = 'A' then
IF ws_ass_cat ='MJ' or
ws_ass_cat = 'MN' then
null;
END IF;
ELSIF
:key.scr_asset_cat = 'MJ' then
IF ws_ass_cat ='MJ' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_asset_cat = 'MN' then
IF ws_ass_cat = 'MN' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset = 'A' then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
IF ws_found = 'Y' then
IF :key.scr_herit_asset = 'A' then
null;
ELSIF
:key.scr_herit_asset = 'Y' then
IF ws_heritage_ind = 'Y' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSIF
:key.scr_herit_asset = 'N' then
IF ws_heritage_ind = 'N' then
NULL;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
ELSIF ws_found = 'N' then
IF :key.scr_herit_asset in ('A','N') then
null;
ELSE
goto nxt_rec;
END IF;
ELSE
goto nxt_rec;
END IF;
BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
ws_ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
EXCEPTION
when dup_val_on_index then
null;
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
-- raise form_trigger_failure; */
END;
end if;
<< nxt_rec >>
null;
ws_cnt:= ws_cnt + 1;
o_cnt := ws_cnt;
END LOOP;
END;
END;
END IF;
END;
END;
Line 419: For rec1 in (select scin,
In that cursor named rec1 there is no column srv_status_cde selected.
In that cursor named rec1 there is no column srv_status_cde selected.
ASKER
yes that true that's why am saying to henka this solution will not work because in table lsst_asset_fin there is no column srv_status_cde
I believe what Henka was suggestion was not declaring the cursor sel_ser_no in the declaration section of the procedure but moving it directly into the FOR loop. To me, it doesn't make any difference either way.
If you moved the cursor as it was into the FOR loop, all the columns would be there.
If you moved the cursor as it was into the FOR loop, all the columns would be there.
ASKER
ok than this solution fail I will keep on trying if you have time maybe you can come with new suggestion this si what I was trying to do today
And keep in mind a COMMIT in Oracle Forms is not a always a commit in the database.
Oracle Forms keeps track of changed blocks in the form: a "forms"-commit checks if there are such changes : if there are non, the "forms"-commit is considered ready and there wil be no database-commit. An insert-statement within an oracle-forms procedure does not change a block.
If you want todo a COMMIT of inserted records without a change in the blocks you could issue a call to standard.commit from within an oracle procedure (from exampe the on_commit trigger) in previous forms versions, but that is not possible in the later versions. So for myself I created a package xxx with a procedure do_commit in the database. The database procedure issues a commit and I call that procedure from oracle-forms.
If you do have a forms block based on a database-record within the form you can also force a real commit by changing such a record whenever you do insert records not from a block but from a procedure. It can be a dummy change: :block1.item1 := :block1.item1;
But considering you where inserting into a temporary table a real commit could cause the temporary data to be lost , depending on the way you defined your 'global temporary table'. (will that table be empty at a commit or at the end of the session).
Oracle Forms keeps track of changed blocks in the form: a "forms"-commit checks if there are such changes : if there are non, the "forms"-commit is considered ready and there wil be no database-commit. An insert-statement within an oracle-forms procedure does not change a block.
If you want todo a COMMIT of inserted records without a change in the blocks you could issue a call to standard.commit from within an oracle procedure (from exampe the on_commit trigger) in previous forms versions, but that is not possible in the later versions. So for myself I created a package xxx with a procedure do_commit in the database. The database procedure issues a commit and I call that procedure from oracle-forms.
If you do have a forms block based on a database-record within the form you can also force a real commit by changing such a record whenever you do insert records not from a block but from a procedure. It can be a dummy change: :block1.item1 := :block1.item1;
But considering you where inserting into a temporary table a real commit could cause the temporary data to be lost , depending on the way you defined your 'global temporary table'. (will that table be empty at a commit or at the end of the session).
ASKER
I cant move my procedure to database there are block item which am using in my logic
You can move your procedure to the database, block items can be passed to the procedure as parameters.
ASKER
In this answer (ID: 41542303) markgeer spoke about different sessions in forms and reports. Have you test it ? E.g. instead of using PROC_SELECT_DATA in KEY-COMMIT trigger you can try insert record directly to the template table.
If this insert will be successfull then you have to debug PROC_SELECT_DATA, put messages there (as it was recommended in previous posts) with relevant block item values.
If this insert will be successfull then you have to debug PROC_SELECT_DATA, put messages there (as it was recommended in previous posts) with relevant block item values.
ASKER
am trying that I create this procedure currently is selecting all values from the cursor instead of the link values rec1.scin = scin I what to see if I will be able to insert without other logic
PROCEDURE PROC_SELECT_DATA (o_cnt OUT NUMBER)IS
BEGIN
DECLARE
ws_unit_cde number(06) := 10223;
wrk_gsb_unit number(06);
ws_unit number(06) := 10223;
ws_char_unit_cde varchar2(06);
ws_eng_abbr_nme varchar2(30);
wrk_gsb_desc varchar2(30);
ws_unit_line_ind varchar2(01);
ws_act_des varchar2(01);
ws_origin varchar2(100);
ws_ser_no varchar2(30);
ws_inst_ser_no varchar2(30);
ws_scin number(11);
ws_sin number(11);
ws_unit_holding number(06);
ws_dept_holding number(06);
ws_unit_located_at number(06);
ws_cond_cde varchar2(01);
ws_srv_status_cde varchar2(3);
ws_pers_located_at varchar2(15);
ws_itm_purch_prc number(15,4);
wrk_purch_prc varchar2(3);
ws_heritage_ind varchar2(1);
wrk_herit_asset varchar2(3);
ws_res_val number(15,4);
ws_fair_val number(15,4);
wrk_fair_val varchar2(3);
ws_current_val number(15,4);
ws_accum_improve_val number(15,4);
ws_accum_impair_val number(15,4);
ws_useful_lfe number(4);
ws_rem_useful_lfe number(4);
ws_ass_cat varchar2(2);
wrk_ass_cat varchar2(5);
ws_dep_sched varchar2(1);
wrk_dep_sched varchar2(15);
ws_start_dep_dte date;
ws_close_reason varchar2(20);
ws_lst_dep_fin_YYYYMM number(6);
ws_prov_reg_no varchar2(20);
ws_nsn varchar2(16);
ws_itm_type_cde varchar2(01);
ws_struc_lev_cde varchar2(01);
ws_agency_ser_cntrl_ind varchar2(01);
ws_cntr_body_cde varchar2(02);
ws_org_func varchar2(02);
ws_nsn_descr varchar2(200);
ws_mec varchar2(04);
ws_me_descr varchar2(50);
ws_sort_cc number(02);
ws_sort_srv number(02);
ws_err_ind varchar2(01);
ws_err_msg varchar2(80);
ws_ldesc varchar2(50);
ws_sdesc varchar2(25);
ws_short_nme varchar2(30);
ws_long_nme varchar2(50);
ws_stat_ind varchar2(01);
ws_oli_ind varchar2(01);
ws_pers_details varchar2(100);
ws_desc varchar2(150);
ws_rank varchar2(15);
ws_pers_found varchar2(01);
ws_un_no varchar2(10);
ws_dummy varchar2(01);
ws_m_nsn varchar2(16);
proc_cfg_tab_no number;
proc_mec varchar2(04);
proc_mec_nsn varchar2(16);
proc_cfg_tab_desc varchar2(200);
proc_mec_desc varchar2(200);
proc_mec_nsn_desc varchar2(200);
proc_exist_corp_ind varchar2(01);
proc_err_ind varchar2(01);
proc_err_msg varchar2(80);
proc_me_niin varchar2(13);
wrk_nsn_desc varchar2(200);
wrk_err varchar2(1);
wrk_msg varchar2(50);
wrk_nsn varchar2(16);
v_nsn varchar2(16);
wrk_totals varchar2(3);
wrk_all_units varchar2(3);
ws_found varchar2(1);
ws_dep_active_ind varchar2(1);
ws_dep_closed_ind varchar2(1);
ws_act_rv_dte date;
ws_purch_price number(15,4);
ws_cnt number := 0;
cursor ser_data is
select a.ser_no,
a.scin,
a.sin,
nvl(b.unit_holding,0) unit_holding,
nvl(b.dept_holding,0) dept_holding,
nvl(b.unit_located_at,0) unit_located_at,
b.cond_cde,
b.srv_status_cde,
b.pers_located_at,
b.nha_sin,
b.nha_ser_no,
c.nsc||'-'||substr(c.niin,1,2)||'-'||
substr(c.niin,3,3)||'-'||substr(c.niin,6,4) nsn,
c.itm_type_cde,
c.struc_lev_cde,
c.agency_ser_cntrl_ind,
d.itm_nme,
e.me_cat_cde||e.me_family_cde||e.me_var_cde mec,
e.me_desc
from ils.sss_entry_index a,
ils.sss_er_core b,
ils.item_data c,
ils.names d,
ils.me_master_rec e
where a.sin_change_reas is null
and a.sin_change_dte is null
and b.scin = a.scin
and ((nvl(b.unit_holding,0) = nvl(ws_unit_cde,0) and
nvl(ws_unit_cde,0) > 0) or
nvl(ws_unit_cde,0) = 0)
and c.sin = a.sin
and d.sin = c.sin
and d.itm_nme_type = '1'
and d.name_seq = '1'
and e.sin (+) = a.sin
and (e.me_life_cyc (+) != '08' and
e.me_rec_stat (+) not in ('C','X'))
order by b.unit_holding, a.ser_no;
BEGIN
For rec1 in ser_data LOOP
BEGIN
Select scin,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
act_rv_dte,
dep_active_ind,
dep_closed_ind,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason
into ws_scin,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_act_rv_dte,
ws_dep_active_ind,
ws_dep_closed_ind,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason
from ils.lsst_asset_fin a
where rec1.scin = scin
/*and TRUNC(a.act_rv_dte) BETWEEN TRUNC(NVL(:key.scr_rec_dte_fr,'1900/01/01'))
AND TRUNC(NVL(:key.scr_rec_dte_to,SYSDATE));*/
and TRUNC(a.act_rv_dte) BETWEEN NVL(:key.scr_rec_dte_fr,to_date('1900/01/01', 'yyyy/mm/dd')) AND NVL(:key.scr_rec_dte_to,SYSDATE);
EXCEPTION
when no_data_found then
ws_found := 'N';
-- goto nxt_rec;
null;
END;
wrk_nsn := substr(rec1.nsn,1,4)||substr(rec1.nsn,6,2)||
substr(rec1.nsn,9,3)||substr(rec1.nsn,13,4);
ws_ser_no := rec1.ser_no;
IF nvl(ws_unit,0) > 0 then
BEGIN
select eng_abbr_nme
into ws_eng_abbr_nme
from ils.unt
where unit_cde = ws_unit;
exception
when no_data_found then
ws_eng_abbr_nme := ws_unit ||' Unit not Found';
END;
END IF;
MESSAGE('NSN = '||wrk_nsn);
message('serno = '||ws_ser_no);
message('unit desc = '||ws_eng_abbr_nme);
message('nsn desc = '||wrk_nsn_desc);
PROC_NSN_DESC(wrk_nsn,
wrk_nsn_desc,
wrk_err,
wrk_msg);
-- BEGIN
insert into ils.lsst_prt_asset_fin
(user_id,
dte_tme_stamp,
unit_cde,
nsn,
ser_no,
unit_desc,
nsn_desc,
me_cde,
me_desc,
gsb_unit_cde,
gsb_desc,
itm_purch_prc,
heritage_ind,
res_val,
fair_val,
current_val,
accum_improve_val,
accum_impair_val,
useful_lfe,
rem_useful_lfe,
ass_cat,
dep_sched,
start_dep_dte,
lst_dep_fin_YYYYMM,
close_reason)
values
(:global.ils_force_no,
:key.wrk_dte,
ws_unit,
wrk_nsn,
ws_ser_no,
ws_eng_abbr_nme,
wrk_nsn_desc,
ws_mec,
ws_me_descr,
wrk_gsb_unit,
wrk_gsb_desc,
ws_itm_purch_prc,
ws_heritage_ind,
ws_res_val,
ws_fair_val,
ws_current_val,
ws_accum_improve_val,
ws_accum_impair_val,
ws_useful_lfe,
ws_rem_useful_lfe,
ws_ass_cat,
ws_dep_sched,
ws_start_dep_dte,
ws_lst_dep_fin_YYYYMM,
ws_close_reason);
message('scin = '||ws_scin);
message('itm_purch = '||ws_itm_purch_prc);
message('ws_heritage_ind = '||ws_heritage_ind);
message('ws_res_val = '||ws_res_val);
message('ws_fair_val = '||ws_fair_val);
message('ws_current_val = '||ws_current_val);
message('ws_accum_improve_val = '||ws_accum_improve_val);
message('ws_accum_impair_val = '||ws_accum_impair_val);
message('ws_useful_lfe = '||ws_useful_lfe);
message('ws_rem_useful_lfe = '||ws_rem_useful_lfe);
message('ws_ass_cat = '||ws_ass_cat);
message('ws_act_rv_dte = '||ws_act_rv_dte);
message('ws_dep_active_ind = '||ws_dep_active_ind);
message('ws_dep_closed_ind = '||ws_dep_closed_ind);
message('ws_start_dep_dte = '||ws_start_dep_dte);
message('ws_lst_dep_fin_YYYYMM = '||ws_lst_dep_fin_YYYYMM);
message('ws_close_reason = '||ws_close_reason);
/*EXCEPTION
when dup_val_on_index then
null;*/
--message('Duplicate entry on asset print fin table:- ser_no= '||ws_ser_no);pause;
-- raise form_trigger_failure; */
END LOOP;
--END;
END;
END;
ASKER
this error has nothing to do with report am not yet calling the report yet just doing insert in form
ASKER
I have created exception to handle no data
can u send me the error msg