Compilation errors for TRIGGER SSM.APPEND_SSMA01MST01

Error: PLS-00049: bad bind variable 'NEW.WEIGHT_NOW'

Compilation errors for TRIGGER SSM.APPEND_SSMA01MST01

Error: PLS-00049: bad bind variable 'NEW.WEIGHT_NOW'

Compilation errors for TRIGGER SSM.APPEND_SSMA01MST01

Error: PLS-00049: bad bind variable 'NEW.WEIGHT_NOW'

create or replace trigger Append_SSMA01MST01 after insert or update

on ssm401dtl01

referencing new as new old as old

for each row

declare

row_count number(1); /*判斷訂單是否已經存在*/

Lv_Cust_No varchar2(10);

Lv_Cust_Abbr varchar2(10);

Lv_Buyer_No varchar2(10);

Lv_Buyer_Abbr varchar2(10);

Lv_Currency varchar2(5);

Lv_Contract_No varchar2(12);

Lv_Date_Sign_Contract date;

Lv_Sale_Area_Code varchar2(1);

Lv_Sale_Area_Group varchar2(10);

Lv_Sales_ID varchar2(8);

Lv_Sales_Name varchar2(10);

pragma autonomous_transaction;

begin

begin

select cust_abbreviations into Lv_Cust_Abbr

from ssm001mst01

where cust_no = :new.cust_no;

insert into pub002mst01(prono,emp_id,date_log,log_string,log_status)

values('SSM_Trigger','X8899',sysdate,'AAA','X');

select count(*) into row_count from ssma01mst01

where sale_order = :new.sale_order

and sale_item = :new.sale_item;

insert into pub002mst01(prono,emp_id,date_log,log_string,log_status)

values('SSM_Trigger','X8899',sysdate,'QQQ','M');

If row_count = 0 Then

begin

insert into pub002mst01(prono,emp_id,date_log,log_string,log_status)

values('SSM_Trigger','X8899',sysdate,'OOO','Y');

insert into ssma01mst01(sale_order,sale_item,cust_no,cust_abbreviations,mic_no,sale_order_dia,mtrl_no,

sale_order_weight,sale_order_unit_price,sale_order_thick, sale_order_width,

date_delivery_pp,date_delivery_sales,pack_code, certificate_code,

max_of_total_weight,min_of_total_weight,max_weight_of_each_prod, min_weight_of_each_prod,

sale_order_length,cust_special,resell_no,cust_purchase_order, color,

cold_drawn,mech_elmt,mech_elmt_remark,hot_rolled_thick,slab_length,

asign_length,flag_sub_volumes,theory_weight,block_number,thick_max,

thick_min,flag_length_diff,hot_rolled_size,cut_off_times,flag_test_plan,

consignee_no,mic_count,weight_now,billet_size,shave_size,shave_no_order,shave_seq_order,

po_flag,po_date,cycle_no,bom_ver,heat_no,pcs_plan,finish_color,roll_length,

range1_eye,range2_eye,range3_eye,range4_eye,show_lineup_desc,flag_oem,

oem_order_no,oem_shop_code,oem_reserved)

values(:new.sale_order,:new.sale_item,:new.cust_no,Lv_Cust_Abbr,:new.mic_no,:new.sale_order_dia,

:new.mtrl_no,:new.sale_order_weight,:new.sale_order_unit_price,:new.sale_order_thick,

:new.sale_order_width,:new.date_delivery_pp,:new.date_delivery_sales,:new.pack_code,

:new.certificate_code,:new.max_of_total_weight,:new.min_of_total_weight,:new.max_weight_of_each_prod,

:new.min_weight_of_each_prod,:new.sale_order_length,:new.cust_special,:new.resell_no,

:new.cust_purchase_order,:new.color,:new.cold_drawn,:new.mech_elmt,:new.mech_elmt_remark,

:new.hot_rolled_thick,:new.slab_length,:new.asign_length,:new.flag_sub_volumes,:new.theory_weight,

:new.block_number,:new.thick_max,:new.thick_min,:new.flag_length_diff,:new.hot_rolled_size,

:new.cut_off_times,:new.flag_test_plan,:new.consignee_no,:new.mic_count,:new.pp_now_weight,:new.pp_billet_size,

:new.pp_shave_size,:new.pp_shave_no_order,:new.pp_shave_seq_order,:new.pp_flag_po,:new.pp_date_po,

:new.pp_cycle_no,:new.pp_bom_ver,:new.pp_heat_no,:new.pp_plan_pcs,:new.pp_flag_finish_color,

:new.pp_roll_length,:new.pp_eye_dia_range_1,:new.pp_eye_dia_range_2,

:new.pp_eye_dia_range_3,:new.pp_eye_dia_range_4,:new.pp_show_lineup_desc,:new.flag_oem,:new.pp_oem_order_no,

:new.oem_shop_code,:new.pp_oem_reserved);

end;

Elsif row_count <> 0 Then

begin

insert into pub002mst01(prono,emp_id,date_log,log_string,log_status)

values('SSM_Trigger','X8899',sysdate,'ZZZ','Y');

select decode(substr(a.sale_order,1,2),'0X',b.cust_no,'0Y',b.cust_no,a.cust_no),

c.cust_abbreviations,

decode(substr(a.sale_order,1,2),'0X',b.cust_no,'0Y',b.cust_no,a.consignee_sale_order),

d.cust_abbreviations,a.sale_order_currency,a.contract_no,e.date_sign_contract,

c.sale_area_code,f.sale_area_group,a.sales_id,g.sales_name

into Lv_Cust_No,Lv_Cust_Abbr,Lv_Buyer_No,Lv_Buyer_Abbr,Lv_Currency,Lv_Contract_No,

Lv_Date_Sign_Contract,Lv_Sale_Area_Code,Lv_Sale_Area_Group,Lv_Sales_ID,Lv_Sales_Name

from ssm401mst01 a,ssm401dtl01 b,ssm001mst01 c,ssm001mst01 d,ssm301mst01 e,

ssm001map02 f,ssm001map06 g

where a.sale_order = b.sale_order( )

and a.cust_no = c.cust_no( )

and a.consignee_sale_order = d.cust_no( )

and a.contract_no = e.contract_no( )

and c.sale_area_code = f.sale_area_code( )

and a.sales_id = g.sales_id( )

and a.sale_order = :new.sale_order

and b.sale_item = :new.sale_item;

update ssma01mst01 set cust_no = Lv_Cust_No,cust_abbreviations = Lv_Cust_Abbr,

consignee_sale_order = Lv_Buyer_No,consignee = Lv_Buyer_Abbr,

currency = Lv_Currency,contract_no = Lv_Contract_No,

contract_no_sign_date = Lv_Date_Sign_Contract,

sale_area_code = Lv_Sale_Area_Code,sale_area_group = Lv_Sale_Area_Group,

sales_id = Lv_Sales_ID,sales_name = Lv_Sales_Name,mic_no = :new.mic_no,

sale_order_dia = :new.sale_order_dia,mtrl_no = :new.mtrl_no,

sale_order_weight = :new.sale_order_weight,

sale_order_unit_price = :new.sale_order_unit_price,

sale_order_thick = :new.sale_order_thick,sale_order_width = :new.sale_order_width,

date_delivery_pp = :new.date_delivery_pp,date_delivery_sales = :new.date_delivery_sales,

pack_code = :new.pack_code,certificate_code = :new.certificate_code,

max_of_total_weight = :new.max_of_total_weight,min_of_total_weight = :new.min_of_total_weight,

max_weight_of_each_prod = :new.max_weight_of_each_prod,

min_weight_of_each_prod = :new.min_weight_of_each_prod,

sale_order_length = :new.sale_order_length,cust_special = :new.cust_special,

resell_no = :new.resell_no,cust_purchase_order = :new.cust_purchase_order,

color = :new.color,cold_drawn = :new.cold_drawn,mech_elmt = :new.mech_elmt,

mech_elmt_remark = :new.mech_elmt_remark,hot_rolled_thick = :new.hot_rolled_thick,

slab_length = :new.slab_length,asign_length = :new.asign_length,

flag_sub_volumes = :new.flag_sub_volumes,theory_weight = :new.theory_weight,

block_number = :new.block_number,thick_max = :new.thick_max,thick_min = :new.thick_min,

flag_length_diff = :new.flag_length_diff,hot_rolled_size = :new.hot_rolled_size,

cut_off_times = :new.cut_off_times,flag_test_plan = :new.flag_test_plan,

consignee_no = :new.consignee_no,mic_count = :new.mic_count,weight_now = :new.pp_now_weight,

billet_size = :new.pp_billet_size,shave_size = :new.pp_shave_size,

shave_no_order = :new.pp_shave_no_order,shave_seq_order = :new.pp_shave_seq_order,

po_flag = :new.pp_flag_po,po_date = :new.pp_date_po,cycle_no = :new.pp_cycle_no,

bom_ver = :new.pp_bom_ver,heat_no = :new.pp_heat_no,pcs_plan = :new.pp_plan_pcs,

finish_color = :new.pp_flag_finish_color,roll_length = :new.pp_roll_length,range1_eye = :new.pp_eye_dia_range_1,

range2_eye = :new.pp_eye_dia_range_2,range3_eye = :new.pp_eye_dia_range_3,range4_eye = :new.pp_eye_dia_range_4,

show_lineup_desc = :new.pp_show_lineup_desc,flag_oem = :new.flag_oem,oem_order_no = :new.pp_oem_order_no,

oem_shop_code = :new.oem_shop_code,oem_reserved = :new.pp_oem_reserved,flag_status = :new.flag_pp,

flag_final_status = :new.flag_done

where sale_order = :new.sale_order

and sale_item = :new.sale_item;

end;

End if;

Commit;

Exception when others then null;

end;

end;