execute_query;
set_block_property(’t_efficiency’,default_where,’username in (select username from t_hrmuser where department = :global.user_dept)’);
END IF;
END;
--生成序号
declare
vlpadnum number;
maxno varchar2(6);
cursor cerpcode is select sequence,lpadnum from t_erpcode
where erpcode=’PURAPPNO’ and prifix=to_char(sysdate,’yy’)||to_char(sysdate,’mm’) for update nowait; [Page]
begin
if :t_purapp.appno is null then
if :SYSTEM.MODE = ’NORMAL’ THEN
begin
begin
for c1 in cerpcode loop
update t_erpcode set sequence=sequence+1 where erpcode=’PURAPPNO’ and prifix=to_char(sysdate,’yy’)||to_char(sysdate,’mm’);
end loop;
exception
when others then
display_note(’SYS071’,:global.logon_user);
raise form_trigger_failure;
end;
select sequence,lpadnum into maxno,vlpadnum from t_erpcode where erpcode=’PURAPPNO’ and prifix=to_char(sysdate,’yy’)||to_char(sysdate,’mm’);
exception
when NO_DATA_FOUND then
select count(*)+1 into maxno from t_purapp where appno like ’%’||to_char(sysdate,’yy’)||to_char(sysdate,’mm’)||’%’;
--select max()
vlpadnum:=4;
insert into t_erpcode(erpcode,prifix,sequence,lpadnum)
values (’PURAPPNO’,to_char(sysdate,’yy’)||to_char(sysdate,’mm’),maxno,vlpadnum);
end;
:t_purapp.appno := ’Q’||:global.logon_user||to_char(sysdate,’yy’)||to_char(sysdate,’mm’)||lpad(maxno,vlpadnum,’0’);
end if;
end if;
end;
--遍历树
select t_prtstrudef.prtno,
t_prtbasdef.prtdesc,
t_prtbasdef.mtltmrk,
t_prtstrudef.prtpqty,
t_prtbasdef.prtum,
t_prtstrudef.tranprtno
from t_prtstrudef,t_prtbasdef
where t_prtstrudef.prtno = t_prtbasdef.prtno
connect by prior t_prtstrudef.prtno=t_prtstrudef.prntno
start with t_prtstrudef.prntno=:BLOCK_BUTTON.PRTNO;
--
/*
** Built-in: FORMS_DDL
** Example: The expression can be a string literal.
*/
BEGIN
Forms_DDL(’create table temp(n NUMBER)’);
IF NOT Form_Success THEN
Message (’Table Creation Failed’);
ELSE
Message (’Table Created’);
END IF;
END;
--物料所属大类判断
and (exists (select ’’a’’ from t_prtinclass c where c.classid=’’’||:blk_query.classid||’’’ and c.prtno=v_totqry.prtno ) or :blk_query.classid is null)
--给值列表赋初值
declare
cursor v_cur2 is select machtype A ,machtype B from t_gkdept order by machtype;
N_INDEX NUMBER :=1; [Page]
begin
DELETE_LIST_ELEMENT(’T_TPMSCKMTN.MACHTYPE’,1);
for c2 in v_cur2 loop
Add_List_Element(’T_TPMSCKMTN.MACHTYPE’,N_INDEX,C2.A,C2.B);
N_INDEX := N_INDEX + 1;
end loop;
end;
--
--查询重复数据
select rpno from t_purtrace group by rpno having count(rpno) >18;
--
--生成单据号
PROCEDURE GET_PRTNO is
v_sysdate varchar2(12);
v_Svdictateno varchar2(24);
v_DbodNo varchar2(12);
n_count integer;
v_count varchar2(10);
n_count1 integer;
n_count2 integer;
v_count2 varchar2(10);
n_num number;
v_num varchar2(8);
BEGIN
v_DbodNo:= ’ZS’;
v_sysdate:=to_char(sysdate,’YYMMDD’);
--获取满足条件的记录数
select count(*)+1 into n_num
from bas_prtbasdef
where prtno like v_DbodNo||v_sysdate||’%’;
v_num := to_char(n_num);
--填充
v_count:=lpad(v_num,’5’,’0’);
--合成为物料号
v_Svdictateno:=v_DbodNo||v_sysdate||v_count;
--判断是否存在该号码
select count(*) into n_count from bas_prtbasdef
where prtno=v_Svdictateno;
--不存在
if n_count=0 then
--把新物料号赋给参数p_prtno
:parameter.p_prtno := v_Svdictateno;
--若存在
elsif n_count>0 then
--递增
n_count1:=v_count+1;
loop
v_count2:=lpad(n_count1,’5’,’0’);
v_Svdictateno:=v_DbodNo||v_sysdate||v_count2;
select count(*) into n_count2 from bas_prtbasdef
where prtno=v_Svdictateno;
if n_count2=0 then
