create or replace procedure AAA(table_val in varchar2,table_result in varchar2) is --游标结果集 t_fproductid varchar2(50); t_fproductid_fnumber varchar2(50); --动态游标 TYPE My_CurType IS REF CURSOR; CUR_1 My_CurType; --结果表字段定义 FIDENTITYID number; --主键 FPRODUCTID_FNUMBER varchar2(100); --产品编码 FPRODUCTID_FNAME varchar2(300); --产品名称 FSPECIFICATION varchar2(300); --规格 FBASICUNITFIELD_FNAME varchar2(100); --单位 FCURRCOMPLETEQTY number(23,10); --入库数量 Fyuancailiao_cengben number(23,10); --原材料单位成本 Fyuancailiao_zonge number(23,10); --原材料总额 Fbaozhuang_cengben number(23,10); --包材单位成本 Fbaozhuang_zonge number(23,10); --包材总额 Fenery_cengben number(23,10); --动力单位成本 Fenery_zonge number(23,10); --动力总额 Fzhijie_cengben number(23,10); --工资单位成本 Fzhijie_zonge number(23,10); --工资总额 Fzhejiu_cengben number(23,10); --折旧单位成本 Fzhejiu_zonge number(23,10); --折旧总额 Fzulin_cengben number(23,10); --租赁费单位成本 Fzulin_zonge number(23,10); --租赁费总额 Fhuayan_cengben number(23,10); --化验费单位成本 Fhuayan_zonge number(23,10); --化验费总额 Fpaiwu_cengben number(23,10); --排污费单位成本 Fpaiwu_zonge number(23,10); --排污费总额 Fyunsu_cengben number(23,10); --运输费单位成本 Fyunsu_zonge number(23,10); --运输费总额 Fqita_cengben number(23,10); --其它（含仓储费）单位成本 Fqita_zonge number(23,10); --其它（含仓储费）总额 Fweiliechu_cengben number(23,10); --制造费用单位成本 Fweiliechu_zonge number(23,10); --制造费用总额 Fcurrcostamount number(23,10); --单位成本 Fcurrcompleteamount number(23,10); --总成本 --标识变量 loop_state number:=0; --汇总字段 t_FIDENTITYID number; --主键 tt_FPRODUCTID_FNUMBER varchar2(100); --产品编码 t_FPRODUCTID_FNAME varchar2(300); --产品名称 t_FSPECIFICATION varchar2(300); --规格 t_FBASICUNITFIELD_FNAME varchar2(100); --单位 t_FCURRCOMPLETEQTY number(23,10); --入库数量 t_Fyuancailiao_cengben number(23,10); --原材料单位成本 t_Fyuancailiao_zonge number(23,10); --原材料总额 t_Fbaozhuang_cengben number(23,10); --包材单位成本 t_Fbaozhuang_zonge number(23,10); --包材总额 t_Fenery_cengben number(23,10); --动力单位成本 t_Fenery_zonge number(23,10); --动力总额 t_Fzhijie_cengben number(23,10); --工资单位成本 t_Fzhijie_zonge number(23,10); --工资总额 t_Fzhejiu_cengben number(23,10); --折旧单位成本 t_Fzhejiu_zonge number(23,10); --折旧总额 t_Fzulin_cengben number(23,10); --租赁费单位成本 t_Fzulin_zonge number(23,10); --租赁费总额 t_Fhuayan_cengben number(23,10); --化验费单位成本 t_Fhuayan_zonge number(23,10); --化验费总额 t_Fpaiwu_cengben number(23,10); --排污费单位成本 t_Fpaiwu_zonge number(23,10); --排污费总额 t_Fyunsu_cengben number(23,10); --运输费单位成本 t_Fyunsu_zonge number(23,10); --运输费总额 t_Fqita_cengben number(23,10); --其它（含仓储费）单位成本 t_Fqita_zonge number(23,10); --其它（含仓储费）总额 t_Fweiliechu_cengben number(23,10); --制造费用单位成本 t_Fweiliechu_zonge number(23,10); --制造费用总额 t_Fcurrcostamount number(23,10); --单位成本 t_Fcurrcompleteamount number(23,10); --总成本 begin OPEN CUR_1 FOR 'select distinct t.fproductid,max(t.fproductid_fnumber) fproductid_fnumber from '||table_val||' t group by t.fproductid'; LOOP FETCH CUR_1 INTO t_fproductid,t_fproductid_fnumber; EXIT WHEN CUR_1%NOTFOUND; --主键 loop_state:=loop_state+1; FIDENTITYID:=loop_state; --物料信息 execute immediate 'select to_char(max(t.fproductid_fnumber)),to_char(max(t.fproductid_fname)),to_char(max(t.fspecification)),to_char(max(t0.fname)) from '||table_val||' t,T_BD_UNIT_l t0 where t.fbasicunitid=t0.funitid and t0.flocaleid=''2052'' and t.fproductid='''||t_fproductid||'''' into FPRODUCTID_FNUMBER,FPRODUCTID_FNAME,FSPECIFICATION,FBASICUNITFIELD_FNAME ; --入库数量 execute immediate 'select sum(FCURRCOMPLETEQTY) from '||table_val||' t where t.fproductid='||t_fproductid into FCURRCOMPLETEQTY; --直接原材料 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00001_SYS''' into Fyuancailiao_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00001_SYS''' into Fyuancailiao_zonge; --包装材料 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00067''' into Fbaozhuang_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00067''' into Fbaozhuang_zonge; --能源动力 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00068''' into Fenery_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00068''' into Fenery_zonge; --直接工资，奖金等 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber in(''CBXM00069'',''CBXM00070'',''CBXM00071'',''CBXM00072'',''CBXM00073'',''CBXM00074'',''CBXM00075'',''CBXM00076'')' into Fzhijie_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber in(''CBXM00069'',''CBXM00076'')' into Fzhijie_zonge; --折旧费用 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00080''' into Fzhejiu_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00080''' into Fzhejiu_zonge; --租赁费 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00081''' into Fzulin_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00081''' into Fzulin_zonge; --化验费 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00092''' into Fhuayan_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00092''' into Fhuayan_zonge; --排污费 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00093''' into Fpaiwu_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00093''' into Fpaiwu_zonge; --运输费 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00088''' into Fyunsu_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00088''' into Fyunsu_zonge; --其他 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00094''' into Fqita_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber=''CBXM00094''' into Fqita_zonge; --未列出 execute immediate 'select sum(FCURRCOSTAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber not in (''CBXM00001_SYS'',''CBXM00067'',''CBXM00068'',''CBXM00069'',''CBXM00076'',''CBXM00080'',''CBXM00081'',''CBXM00092'',''CBXM00093'',''CBXM00088'',''CBXM00094'')' into Fweiliechu_cengben; execute immediate 'select sum(FCURRCOMPLETEAMOUNT) from '||table_val||' t where t.fproductid='||t_fproductid||' and t.fcostitemid_fnumber not in (''CBXM00001_SYS'',''CBXM00094'')' into Fweiliechu_zonge; --本期单位成本 execute immediate 'select sum(t.Fcurrcostamount) from '||table_val||' t where t.fproductid='''||t_fproductid||'''' into Fcurrcostamount; --本期完工成本 execute immediate 'select sum(t.Fcurrcompleteamount) from '||table_val||' t where t.fproductid='''||t_fproductid ||'''' into Fcurrcompleteamount; --插入结果表 execute immediate 'insert into '||table_result||'( FIDENTITYID,FPRODUCTID_FNUMBER,FBASICUNITFIELD_FNAME,FCURRCOMPLETEQTY,Fyuancailiao_cengben,Fyuancailiao_zonge,Fbaozhuang_cengben,Fbaozhuang_zonge,Fenery_cengben,Fenery_zonge,Fzhijie_cengben,Fzhijie_zonge,Fzhejiu_cengben,Fzhejiu_zonge,Fzulin_cengben,Fzulin_zonge,Fhuayan_cengben,Fhuayan_zonge,Fpaiwu_cengben,Fpaiwu_zonge,Fyunsu_cengben,Fyunsu_zonge,Fqita_cengben,Fqita_zonge,Fweiliechu_cengben,Fweiliechu_zonge,Fcurrcostamount,Fcurrcompleteamount ) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30)' using FIDENTITYID,Fcurrcompleteamount; commit; END LOOP; CLOSE CUR_1; execute immediate 'select sum(FCURRCOMPLETEQTY),sum(Fyuancailiao_cengben),sum(Fyuancailiao_zonge),sum(Fbaozhuang_cengben),sum(Fbaozhuang_zonge),sum(Fenery_cengben),sum(Fenery_zonge),sum(Fzhijie_cengben),sum(Fzhijie_zonge),sum(Fzhejiu_cengben),sum(Fzhejiu_zonge),sum(Fzulin_cengben),sum(Fzulin_zonge),sum(Fhuayan_cengben),sum(Fhuayan_zonge),sum(Fpaiwu_cengben),sum(Fpaiwu_zonge),sum(Fyunsu_cengben),sum(Fyunsu_zonge),sum(Fqita_cengben),sum(Fqita_zonge),sum(Fweiliechu_cengben),sum(Fweiliechu_zonge),sum(Fcurrcostamount),sum(Fcurrcompleteamount) from '||table_result into t_FCURRCOMPLETEQTY,t_Fyuancailiao_cengben,t_Fyuancailiao_zonge,t_Fbaozhuang_cengben,t_Fbaozhuang_zonge,t_Fenery_cengben,t_Fenery_zonge,t_Fzhijie_cengben,t_Fzhijie_zonge,t_Fzhejiu_cengben,t_Fzhejiu_zonge,t_Fzulin_cengben,t_Fzulin_zonge,t_Fhuayan_cengben,t_Fhuayan_zonge,t_Fpaiwu_cengben,t_Fpaiwu_zonge,t_Fyunsu_cengben,t_Fyunsu_zonge,t_Fqita_cengben,t_Fqita_zonge,t_Fweiliechu_cengben,t_Fweiliechu_zonge,t_Fcurrcostamount,t_Fcurrcompleteamount; t_FIDENTITYID:=loop_state+1; tt_FPRODUCTID_FNUMBER:='合计'; --插入结果表 execute immediate 'insert into '||table_result||'( FIDENTITYID,:30)' using t_FIDENTITYID,tt_FPRODUCTID_FNUMBER,t_FPRODUCTID_FNAME,t_FSPECIFICATION,t_FBASICUNITFIELD_FNAME,t_FCURRCOMPLETEQTY,t_Fcurrcompleteamount; commit; end P_DC_SummaryOfProductCost;
