PROCEDURE PLUPDATE_NEW IS

  V_COUNT NUMBER(12) := 0;

BEGIN
	 SELECT MAX(PLUP_SAVE_SEQ) INTO V_COUNT FROM PLUPDATE_NEW
    where PLUP_CONTR = :cust_contr;
     
			/* inserting old ph information to plupdate_new table*/
			insert into plupdate_new
            (plup_contr, plup_date, plup_new_first_name, plup_new_last_name,
             plup_new_middle_initial, plup_new_home_street,
             plup_new_home_city, plup_new_home_province, plup_new_home_zip,
             plup_new_home_phone, plup_new_email, plup_new_birth_date,
             --
             plup_new_civil_status, plup_new_sex, plup_new_credit_card_no,
             plup_new_credit_valid_thru, plup_new_ccard_comp_no,
             plup_new_ccard_holder, plup_new_payr_lname, plup_new_payr_fname,
             plup_new_payr_minitial, plup_new_bill_street,
             plup_new_bill_city, plup_new_bill_province, plup_new_bill_zip,
             plup_new_bill_phone, plup_new_schedule, plup_new_payment_optn,
             plup_new_program, plup_new_pay_out_optn, plup_new_maturity_date,
             plup_new_total_benefit, plup_new_monthly_payout,
             plup_new_lumpsum_amt, plup_new_eab_amt, plup_created_by,
             plup_created_dt, plup_last_user, plup_last_update,
             plup_new_enro_contr, plup_new_enro_last_name,
             plup_new_enro_first_name, plup_new_enro_middle_initial,
             plup_new_enro_relationship, plup_new_enro_home_street,
             plup_new_enro_home_city, plup_new_enro_home_province,
             plup_new_enro_home_zip, plup_new_enro_home_phone_nr,
             plup_new_enro_birthdate, plup_new_enro_sex,
             plup_new_enro_school_level, plup_save_seq,
             plup_authorizing_smd_esd, plup_authorizing_rfs,
						 PLUP_NEW_CUST_SP_LAST_NAME, PLUP_NEW_CUST_SP_FIRST_NAME, 
             PLUP_NEW_CUST_SP_MIDDLE_NAME, PLUP_NEW_CUST_SP_BIRTHDATE, 
             PLUP_NEW_CUST_SP_EMAIL, PLUP_NEW_CUST_SP_OCCUPATION, 
             PLUP_NEW_CUST_SP_TELEPHONE, PLUP_NEW_CUST_SP_CELLPHONE, 
             PLUP_NEW_PAYR_CIVIL_STATUS, PLUP_NEW_CELLPHONE             
            )
     values (:cust_contr, sysdate, :cust_first_name, :cust_last_name,
             :cust_middle_initial, :cust_home_street,
             :cust_home_city, :cust_home_province, :cust_home_zip,
             :cust_home_phone, :cust_email, :cust_birth_date,

             :cust_civil_status, :cust_sex, :cust_credit_card_no,
             :cust_credit_valid_thru, :cust_ccard_comp_no,
             :cust_ccard_holder, :cust_payr_lname, :cust_payr_fname,
             :cust_payr_minitial, :cust_bill_street,
             :cust_bill_city, :cust_bill_province, :cust_bill_zip,
             :cust_bill_phone, :cust_schedule, :cust_payment_optn,
             :cust_program, :cust_pay_out_optn, :cust_maturity_date,
             :cust_total_benefit, :cust_monthly_payout,
             :cust_lumpsum_amt, :cust_eab_amt, nvl  (:global.emp_code, user),
             sysdate, nvl (:global.emp_code, user), sysdate,
             :enro_contr, :enro_last_name,
             :enro_first_name, :enro_middle_initial,
             :enro_relationship, :enro_home_street,
             :enro_home_city, :enro_home_province,
             :enro_home_zip, :enro_home_phone_nr,
             :enro_birthdate, :enro_sex,
             null, nvl (v_count, 0) + 1,
             :emp_code, :emp_code2,
             :CUST_SP_LAST_NAME, :CUST_SP_FIRST_NAME,
             :CUST_SP_MIDDLE_NAME, :CUST_SP_BIRTHDATE,
             :CUST_SP_EMAIL, :CUST_SP_OCCUPATION,
             :CUST_SP_TELEPHONE, :CUST_SP_CELLPHONE,
             :CUST_PAYR_CIVIL_STATUS, :CUST_CELLPHONE
             );
  --end if;
END;

PROCEDURE PROC_PLUP_BENEF_NEW IS

V_COUNT NUMBER(12) := 0;

BEGIN
 SELECT MAX(PLUP_NEW_BENE_SAVE_SEQ) 
   INTO V_COUNT FROM plupdate_benef_NEW
    where PLUP_NEW_BENE_CONTR = :cust_contr;
 
	insert into plupdate_benef_new
   (select bene_contr, bene_seq, bene_last_name, bene_first_name,
           bene_middle_initial, bene_relation, bene_street, bene_city,
           bene_province, bene_zip, bene_phone, bene_email, bene_country_code,
           bene_birth_date, v_count, bene_created_by, bene_created_dt,
           substr (nvl (:global.emp_code, user), 1, 8), sysdate, null
      from benef
     where bene_contr = :main.cust_contr);  			
END;

SELECT MAX(PLUP_SAVE_SEQ) INTO V_COUNT FROM PLUPDATE_NEW
    where PLUP_CONTR = :cust_contr;

SELECT MAX(PLUP_NEW_BENE_SAVE_SEQ) 
   INTO V_COUNT FROM plupdate_benef_NEW
    where PLUP_NEW_BENE_CONTR = :cust_contr;

ORA-06502: PL/SQL: numeric or value errorstring

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.