Home » Developer & Programmer » Forms » details shoul be updated from one table to other table (form 6i)
details shoul be updated from one table to other table [message #500310] |
Sat, 19 March 2011 02:46 |
|
when the tables are updated, the following detals must be correct to ensure
that the links in the affected tables are in place.
PLUPDATE_NEW(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_NEW (PLUP_NEW_BENEF_SAVE_SEQ field value)
PLUPDATE_OLD(PLUP_SAVE_SEQ field value) must be the same with PLUPDATE_BENEF_OLD (PLUP_OLD_BENEF_SAVE_SEQ field value)?
code for plupdate_new
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;
code for plupdate_benef_new
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;
i tried this code, what should i do in the link for this tables?
|
|
|
Re: details shoul be updated from one table to other table [message #500317 is a reply to message #500310] |
Sat, 19 March 2011 04:32 |
John Watson
Messages: 8950 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I think your code will fail if more than one person runs the procedures concurrently, because of this
SELECT MAX(PLUP_SAVE_SEQ) INTO V_COUNT FROM PLUPDATE_NEW
where PLUP_CONTR = :cust_contr;
and this
SELECT MAX(PLUP_NEW_BENE_SAVE_SEQ)
INTO V_COUNT FROM plupdate_benef_NEW
where PLUP_NEW_BENE_CONTR = :cust_contr;
I see nothing to prevent several sessions selecting the same value. Or, if one session commits both its inserts, while another session has done the first but not the second, I think it will be a total mess.
Of course I could be wrong, but are you sure your mechanism of generating unique numbers will work?
|
|
|
Re: details shoul be updated from one table to other table [message #500544 is a reply to message #500317] |
Tue, 22 March 2011 02:33 |
|
help me to find the solution for
FRM 41316 Cannot insert Allowed property of disables item benef.bene_last_name
FRM 41017 Cannot set update allowed attribute of non enable item bene_last_name
and FRM 40735 On Error trigger raised unhandled exception ORA 06502
the thing that ive done in the prgram was i just edit the payor info and not in benef_canvass.
thank you
|
|
|
Re: details shoul be updated from one table to other table [message #500561 is a reply to message #500544] |
Tue, 22 March 2011 04:22 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
First two messages say that you are trying to modify INSERT_ALLOWED and UPDATE_ALLOWED properties of an item that is disabled. That's nonsense because if an item is disabled, you can't modify it anyway.
ON-ERROR trigger contains code that raised ORA-06502:
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. So - fix it.
CM: fixed tags
[Updated on: Tue, 22 March 2011 05:40] by Moderator Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 19 23:34:08 CDT 2024
|