create or replace procedure wz.pr_t_342_purchase_insertdata(
ls_purplan_id in varchar2,ls_year_mon in varchar2,ls_plan_type in varchar2)
is
ls_orga_id varchar2(16);
ls_orga_name varchar2(32);
ls_remark varchar2(128);
cursor cursor1 is
select t3.material_id,t3.material_name,t3.specification,t3.measure_id,
sum(t2.req_amount) as req_quantity,
min(t2.need_day) as need_day,
count(*) as dept_num,
max(t2.req_amount) as max_req_amount
from wz.t_wrequirment t1,wz.t_drequirment t2,wz.t_material t3
where t1.status='6' and to_char(t1.apply_day,'yyyymm')>='200710'
and to_char(t1.apply_day,'yyyymm')<=ls_year_mon
and t1.requir_type=ls_plan_type
and t1.requir_id=t2.requir_id and t2.material_id=t3.material_id
and t2.purplan_id is null and t2.material_id is not null
and t2.req_amount>0 AND T2.REQ_AMOUNT IS NOT NULL and nvl(t2.status,'0')<>'B'
group by t3.material_id,t3.material_name,t3.specification,t3.measure_id;
cursor cursor2 is
select t2.material_id,t2.remark from wz.t_wrequirment t1,wz.t_drequirment t2
where t1.status='6' and to_char(t1.apply_day,'yyyymm')>='200710'
and to_char(t1.apply_day,'yyyymm')<=ls_year_mon
and t1.requir_type=ls_plan_type
and t1.requir_id=t2.requir_id
and t2.purplan_id is null and t2.material_id is not null
and t2.req_amount>0 AND T2.REQ_AMOUNT IS NOT NULL and nvl(t2.status,'0')<>'B'
and t2.remark is not null;
begin
for result1 in cursor1 loop
select distinct max(t1.orga_id),max(t1.orga_name) into ls_orga_id,ls_orga_name
from wz.t_wrequirment t1,wz.t_drequirment t2
where t1.status='6' and to_char(t1.apply_day,'yyyymm')>='200710'
and to_char(t1.apply_day,'yyyymm')<=ls_year_mon
and t1.requir_type=ls_plan_type
and t1.requir_id=t2.requir_id AND T2.REQ_AMOUNT IS NOT NULL and nvl(t2.status,'0')<>'B'
and t2.req_amount=result1.max_req_amount
and t2.material_id=result1.material_id
and t2.purplan_id is null;
ls_remark:='';
for result2 in cursor2 loop
if result2.material_id=result1.material_id then
ls_remark:=ls_remark || result2.remark;
end if;
end loop;
insert into wz.t_cg_plan (group_id,plan_type,purplan_id,year_mon,material_id,material_name,
specification,req_quantity,req_day,plan_quantity,remark,dept_num,orga_id,orga_name,measure_id,status)
values('lh',ls_plan_type,ls_purplan_id,ls_year_mon,