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;