create or replace procedure P_CREATE_COST_BOM(V_QP_ID QP_REQ_DT_ZC_COST.QP_ID%type, V_MOD_USER QP_REQ_DT_ZC_COST.MOD_USER%type) is
--V_ID QP_REQ_DT_ZC_COST.ID%type ;--由序列seq_sign_id產生
--V_QP_ID QP_REQ_DT_ZC_COST.QP_ID%type := '1234';--需求單ID
V_QP_NO QP_REQ_DT_ZC_COST.QP_NO%type ;--報價單號
V_ITEM_NO QP_REQ_DT_ZC_COST.ITEM_NO%type ;--項次
V_NAME_EN QP_REQ_DT_ZC_COST.NAME_EN%type ;--料件英文名稱
V_FACTORY_NAME QP_REQ_DT_ZC_COST.FACTORY_NAME%type ;--廠部
V_PART_NO_HH QP_REQ_DT_ZC_COST.PART_NO_HH%type ;--鴻海料號
V_PROCESS QP_REQ_DT_ZC_COST.PROCESS%type ;--制程
V_ITEM_NO_DT QP_REQ_DT_ZC_COST.ITEM_NO_DT%type ;--FLOWCHART明細中的項次
V_UNIT_PROCESS QP_REQ_DT_ZC_COST.UNIT_PROCESS%type ;--制程工藝
V_EQUIPMENT_NAME QP_REQ_DT_ZC_COST.EQUIPMENT_NAME%type ;--機臺名稱
V_EQUIPMENT_QUTY QP_REQ_DT_ZC_COST.EQUIPMENT_QUTY%type ;--機臺數量
V_RAW_MAT_NAME QP_REQ_DT_ZC_COST.RAW_MAT_NAME%type ;--原材料名稱
V_PURCHASE_PART_NAME QP_REQ_DT_ZC_COST.PURCHASE_PART_NAME%type ;--外購件名稱
V_SEMI_FINISHED_NAME QP_REQ_DT_ZC_COST.SEMI_FINISHED_NAME%type ;--自制件名稱
V_MAKE_PEOPLE_QTY QP_REQ_DT_ZC_COST.MAKE_PEOPLE_QTY%type ;--作業人數
V_MAKE_TIME QP_REQ_DT_ZC_COST.MAKE_TIME%type ;--節拍工時
V_PERCENT1 QP_REQ_DT_ZC_COST.PERCENT1%type ;--一次良率
V_PERCENT2 QP_REQ_DT_ZC_COST.PERCENT2%type ;--二次良率
V_EQUIPMENT_RATE QP_REQ_DT_ZC_COST.EQUIPMENT_RATE%type ;--機臺工費率
V_PEOPLE_RATE QP_REQ_DT_ZC_COST.PEOPLE_RATE%type ;--人員工費率
V_RAW_MET_COST QP_REQ_DT_ZC_COST.RAW_MET_COST%type := 0 ;--物料費用
V_PURCHASE_COST QP_REQ_DT_ZC_COST.PURCHASE_COST%type := 0 ;--外購件費用
V_SEMI_FINISHED_COST QP_REQ_DT_ZC_COST.SEMI_FINISHED_COST%type := 0 ;--自制品費用
V_EQUIPMENT_COST QP_REQ_DT_ZC_COST.EQUIPMENT_COST%type := 0 ;--機臺費用
V_PEOPLE_COST QP_REQ_DT_ZC_COST.PEOPLE_COST%type := 0 ;--人力費用
V_REMAKE_COST QP_REQ_DT_ZC_COST.REMAKE_COST%type := 0 ;--重工費用
V_LOSS_COST QP_REQ_DT_ZC_COST.LOSS_COST%type := 0 ;--損耗費用
V_PART_COST QP_REQ_DT_ZC_COST.PART_COST%type := 0 ;--小件費用
V_PART_SUM_COST QP_REQ_DT_ZC_COST.PART_SUM_COST%type := 0 ;--小計費用
--V_MOD_USER QP_REQ_DT_ZC_COST.MOD_USER%type := 'F1706330' ;--更新用戶
V_ITEM_SUM qp_req_ma.zc_cost%type := 0;--項次合計費用
V_PART_ITEM_SUM qp_req_ma.zc_cost%type := 0;--制程項次合計費用
V_MN_RATE number := 1;--匯率
cursor sor_zcma is select m.* from QP_REQ_DT_ZC_MA m where m.qp_id = V_QP_ID order by m.item_no;
V_ZC_ID QP_REQ_DT_ZC_MA.Id%type ;--flowchart 主表ID
cursor sor_zcdt is select d.* from QP_REQ_DT_ZC_DT d where d.flowchart_ma_id = V_ZC_ID order by d.item_no;
V_ME_ID QP_REQ_DT_ME.Id%type ;--原物料主表ID
cursor sor_me is select t.PART_NAME_EN,t.PRICE,MONEY_TYPE,CONSUME_PC from QP_REQ_DT_ME t where t.ID = V_ME_ID;
V_BOM_ID QP_REQ_DT_BOM.ID%type ;--BOM表ID
cursor sor_bom is select t.NAME_EN,t.PRICE,MONEY_TYPE,QUTY from QP_REQ_DT_BOM t where t.ID = V_BOM_ID;
V_BZ_COST qp_req_ma.bz_cost%type := 0;--包裝
cursor sor_bz is select t.money_type,t.price,t.quty_pc1 from qp_req_dt_bz t where t.qp_id = V_QP_ID;
V_WL_COST qp_req_ma.wl_cost%type := 0;--物流
cursor sor_wl is select t.money_type,t.ys_cost,t.bg_cost,t.querty from qp_req_dt_wl t where t.qp_id = V_QP_ID;
V_SUM_COST qp_req_ma.sum_cost%type := 0;--合計
begin
--刪除
delete from qp_req_dt_zc_cost t where t.QP_ID = V_QP_ID;
--制程
for v_tmp_m in sor_zcma loop
V_QP_NO := v_tmp_m.QP_NO;
V_ITEM_NO := v_tmp_m.ITEM_NO;
V_NAME_EN := v_tmp_m.NAME_EN;
V_FACTORY_NAME := v_tmp_m.FACTORY_NAME;
V_PART_NO_HH := v_tmp_m.PART_NO_HH;
V_PROCESS := v_tmp_m.PROCESS;
V_ZC_ID := v_tmp_m.ID;
V_PART_ITEM_SUM := 0;
for v_tmp_d in sor_zcdt loop
V_ITEM_NO_DT := v_tmp_d.ITEM_NO;
V_UNIT_PROCESS := v_tmp_d.UNIT_PROCESS;
V_EQUIPMENT_NAME := v_tmp_d.EQUIPMENT_NAME;
V_EQUIPMENT_QUTY := Nvl(v_tmp_d.EQUIPMENT_QUTY, 0);
V_MAKE_PEOPLE_QTY := Nvl(v_tmp_d.MAKE_PEOPLE_QTY, 0);
V_MAKE_TIME := v_tmp_d.MAKE_TIME;
V_PERCENT1 := Nvl(v_tmp_d.PERCENT1, 100);
V_PERCENT2 := Nvl(v_tmp_d.PERCENT2, 100);
--原物料
V_RAW_MET_COST := 0;
V_RAW_MAT_NAME := null;
if v_tmp_d.RAW_MAT_ID is not null then
V_ME_ID := v_tmp_d.RAW_MAT_ID;
for v_tmp_me in sor_me loop
V_RAW_MAT_NAME := v_tmp_me.PART_NAME_EN;
if v_tmp_me.MONEY_TYPE != 'RMB' then
select t.rate into V_MN_RATE from v_rmb_rate t where t.money_type = v_tmp_me.MONEY_TYPE and t.rate_yyyy||t.rate_mm = (select max(d.rate_yyyy||d.rate_mm) from v_rmb_rate d) and rownum = 1;
else
V_MN_RATE := 1;
end if;
V_RAW_MET_COST := v_tmp_me.PRICE * v_tmp_me.CONSUME_PC / V_MN_RATE;
end loop;
end if;
--外購件
V_PURCHASE_COST := 0;
V_PURCHASE_PART_NAME := null;
if v_tmp_d.PURCHASE_PART_ID is not null then
V_BOM_ID := v_tmp_d.PURCHASE_PART_ID;
for v_tmp_bom in sor_bom loop
V_PURCHASE_PART_NAME := v_tmp_bom.NAME_EN;
if v_tmp_bom.MONEY_TYPE != 'RMB' then
select t.rate into V_MN_RATE from v_rmb_rate t where t.money_type = v_tmp_bom.MONEY_TYPE and t.rate_yyyy||t.rate_mm = (select max(d.rate_yyyy||d.rate_mm) from v_rmb_rate d) and rownum = 1;
else
V_MN_RATE := 1;
end if;
V_PURCHASE_COST := v_tmp_bom.PRICE * v_tmp_bom.QUTY / V_MN_RATE;
end loop;
end if;
--自制件
V_SEMI_FINISHED_COST := 0;
V_SEMI_FINISHED_NAME := null;
if v_tmp_d.SEMI_FINISHED_ID is not null then
V_BOM_ID := v_tmp_d.SEMI_FINISHED_ID;
for v_tmp_bom in sor_bom loop
V_SEMI_FINISHED_NAME := v_tmp_bom.NAME_EN;
if v_tmp_bom.MONEY_TYPE != 'RMB' then
select t.rate into V_MN_RATE from v_rmb_rate t where t.money_type = v_tmp_bom.MONEY_TYPE and t.rate_yyyy||t.rate_mm = (select max(d.rate_yyyy||d.rate_mm) from v_rmb_rate d) and rownum = 1;
else
V_MN_RATE := 1;
end if;
V_SEMI_FINISHED_COST := v_tmp_bom.PRICE * v_tmp_bom.QUTY / V_MN_RATE;
end loop;
end if;
--機臺工費率
if V_EQUIPMENT_NAME is not null then
select t.rate into V_EQUIPMENT_RATE from bs_qp_rate_equipment t where t.equipment_name = V_EQUIPMENT_NAME and rownum = 1;
--機臺費用
V_EQUIPMENT_COST := V_EQUIPMENT_QUTY * (V_EQUIPMENT_RATE / (3600 / V_MAKE_TIME));
else
V_EQUIPMENT_COST := 0;
end if;
--人工
select t.rate into V_PEOPLE_RATE from bs_qp_rate_pe t where t.people = '人工' and rownum = 1;
--人力費用
V_PEOPLE_COST := V_MAKE_PEOPLE_QTY * V_PEOPLE_RATE / (3600 / V_MAKE_TIME);
--重工費用
V_REMAKE_COST := (V_EQUIPMENT_COST + V_PEOPLE_COST) * (100 - V_PERCENT1) / V_PERCENT2;
--損耗費用
V_LOSS_COST := (V_RAW_MET_COST + V_PURCHASE_COST + V_EQUIPMENT_COST + V_PEOPLE_COST + V_PART_ITEM_SUM) * (100 - V_PERCENT2) / V_PERCENT2;
--小件費用
V_PART_COST := V_RAW_MET_COST + V_PURCHASE_COST + V_EQUIPMENT_COST + V_PEOPLE_COST + V_REMAKE_COST + V_LOSS_COST;
--小計費用
V_ITEM_SUM := V_PART_COST + V_ITEM_SUM;
V_PART_SUM_COST := V_ITEM_SUM;
--制程項次合計費用
V_PART_ITEM_SUM := V_PART_COST + V_PART_ITEM_SUM;
--新增
insert into qp_req_dt_zc_cost
(qp_no, item_no, name_en, factory_name, part_no_hh, process, item_no_dt, unit_process, equipment_name, equipment_quty, raw_mat_name, purchase_part_name, semi_finished_name, make_people_qty, make_time, percent1, percent2, equipment_rate, people_rate, raw_met_cost, purchase_cost, equipment_cost, people_cost, remake_cost, loss_cost, part_cost, part_sum_cost, mod_user, id, qp_id,SEMI_FINISHED_COST)
values
(v_qp_no, v_item_no, v_name_en, v_factory_name, v_part_no_hh, v_process, v_item_no_dt, v_unit_process, v_equipment_name, v_equipment_quty, v_raw_mat_name, v_purchase_part_name, v_semi_finished_name, v_make_people_qty, v_make_time, v_percent1, v_percent2, v_equipment_rate, v_people_rate, v_raw_met_cost, v_purchase_cost, v_equipment_cost, v_people_cost, v_remake_cost, v_loss_cost, v_part_cost, v_part_sum_cost, v_mod_user, seq_sign_id.nextval, v_qp_id, V_SEMI_FINISHED_COST);
end loop;
end loop;
--包裝
for v_tmp in sor_bz loop
if v_tmp.money_type != 'RMB' then
select t.rate into V_MN_RATE from v_rmb_rate t where t.money_type = v_tmp.MONEY_TYPE and t.rate_yyyy||t.rate_mm = (select max(d.rate_yyyy||d.rate_mm) from v_rmb_rate d) and rownum = 1;
else
V_MN_RATE := 1;
end if;
V_BZ_COST := V_BZ_COST + v_tmp.quty_pc1 * v_tmp.price / V_MN_RATE;
end loop;
--物流
for v_tmp in sor_wl loop
if v_tmp.money_type != 'RMB' then
select t.rate into V_MN_RATE from v_rmb_rate t where t.money_type = v_tmp.MONEY_TYPE and t.rate_yyyy||t.rate_mm = (select max(d.rate_yyyy||d.rate_mm) from v_rmb_rate d) and rownum = 1;
else
V_MN_RATE := 1;
end if;
V_WL_COST := V_WL_COST + ((v_tmp.ys_cost + v_tmp.bg_cost) / v_tmp.querty) / V_MN_RATE;
end loop;
--合計
V_SUM_COST := V_BZ_COST + V_WL_COST + V_ITEM_SUM;
select t.rate into V_MN_RATE from bs_money_type t where t.money_type = 'RMB' and t.rate_yyyy||t.rate_mm = (select max(d.rate_yyyy||d.rate_mm) from v_rmb_rate d) and rownum = 1;
V_SUM_COST := V_SUM_COST / V_MN_RATE;
--更新
update qp_req_ma t set t.bz_cost = V_BZ_COST, t.wl_cost = V_WL_COST, t.zc_cost = V_ITEM_SUM, t.sum_cost = V_SUM_COST where t.id = V_QP_ID;
commit;
end P_CREATE_COST_BOM;