FRM-40600: Record has already been inserted.
604449May 23 2008 — edited Jun 2 2008I am using Form 9i.
I have written a procedure to save master-detail form which is called when WHEN-BUTTTON-PRESSED trigger written in submit button..
I have written this procedure because I am confident in writing procedure for save button. But it gives error - 'FRM-40600: Record has already been inserted.'.
When I was using default (form) for saving record, I was getting lot of message (error). I was getting confused seeing this message. That's why I have written save button procedure. I don't know whether I am doing wrong or right.
Before posting, I searched in forum also and found some post regarding this, but still I am unable to find the solution.
Anyway, I am giving you code for save procedure -
PROCEDURE save_workorder IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM workorder_master
WHERE workorder_no = :workorder_master.workorder_no;
IF v_count = 0 THEN
GO_BLOCK('workorder_master');
INSERT INTO workorder_master (
vender_code,
workorder_no,
auth_rep,
workplace,
work_desc,
from_dt,
to_dt,
tot_manpower,
gp_manpower,
lic_no,
lic_recpt_no,
lic_valid_upto,
eic_name,
status,
remarks,
entry_by,
entry_dt)
VALUES(
:workorder_master.vender_code,
:workorder_master.workorder_no,
:workorder_master.auth_rep,
:workorder_master.workplace,
:workorder_master.work_desc,
:workorder_master.from_dt,
:workorder_master.to_dt,
:workorder_master.tot_manpower,
:workorder_master.gp_manpower,
:workorder_master.lic_no,
:workorder_master.lic_recpt_no,
:workorder_master.lic_valid_upto,
:workorder_master.eic_name,
:workorder_master.status,
:workorder_master.remarks,
:workorder_master.entry_by,
:workorder_master.entry_dt);
GO_BLOCK('workorder_dtls');
FIRST_RECORD;
LOOP
EXIT WHEN :workorder_dtls.labour_code IS NULL;
IF :workorder_dtls.pass_no IS NULL THEN
generate_pass_no;
IF :workorder_dtls.pass_no IS NOT NULL THEN
:workorder_dtls.status := 'I';
INSERT INTO workorder_dtls (
labour_code,
workorder_no,
pass_no,
status,
from_dt,
to_dt,
cancil_dt,
issue_dt,
return_dt,
entry_by,
entry_dt)
VALUES (:workorder_dtls.labour_code,
:workorder_dtls.workorder_no,
:workorder_dtls.pass_no,
:workorder_dtls.status,
:workorder_dtls.from_dt,
:workorder_dtls.to_dt,
:workorder_dtls.cancil_dt,
:workorder_dtls.issue_dt,
:workorder_dtls.return_dt,
:workorder_master.entry_by,
:workorder_master.entry_dt);
END IF;
ELSE --if pass no is not null then it is previous record.
UPDATE workorder_dtls SET status = :workorder_dtls.status,
cancil_dt = :workorder_dtls.cancil_dt,
return_dt = :workorder_dtls.return_dt,
entry_by = :workorder_dtls.entry_by,
entry_dt = :workorder_dtls.entry_dt
WHERE labour_code = :workorder_dtls.labour_code
AND workorder_no = :workorder_dtls.workorder_no
AND pass_no = :workorder_dtls.pass_no;
END IF;
IF :workorder_dtls.status = 'I' THEN
UPDATE labour_master SET status = 'I'
WHERE labour_code = :workorder_dtls.labour_code;
END IF;
IF :workorder_dtls.status = 'E' THEN
IF :workorder_dtls.return_dt IS NOT NULL THEN
UPDATE labour_master SET status = 'A'
WHERE labour_code = :workorder_dtls.labour_code;
ELSE
UPDATE labour_master SET status = 'H'
WHERE labour_code = :workorder_dtls.labour_code;
END IF;
END IF;
IF :workorder_dtls.status = 'B' THEN
IF :workorder_dtls.return_dt IS NOT NULL THEN
UPDATE labour_master SET status = 'A'
WHERE labour_code = :workorder_dtls.labour_code;
ELSE
UPDATE labour_master SET status = 'S'
WHERE labour_code = :workorder_dtls.labour_code;
END IF;
END IF;
NEXT_RECORD;
END LOOP;
ELSE --if workorder exist
GO_BLOCK('workorder_master');
UPDATE workorder_master SET auth_rep = :workorder_master.auth_rep,
workplace = :workorder_master.workplace,
work_desc = :workorder_master.work_desc,
from_dt = :workorder_master.from_dt,
to_dt = :workorder_master.to_dt,
tot_manpower = :workorder_master.tot_manpower,
gp_manpower = :workorder_master.gp_manpower,
lic_no = :workorder_master.lic_no,
lic_recpt_no = :workorder_master.lic_recpt_no,
lic_valid_upto= :workorder_master.lic_valid_upto,
eic_name = :workorder_master.eic_name,
status = :workorder_master.status,
remarks = :workorder_master.remarks,
entry_by = :workorder_master.entry_by,
entry_dt = :workorder_master.entry_dt
WHERE vender_code = :workorder_master.vender_code
AND workorder_no = :workorder_master.workorder_no;
GO_BLOCK('workorder_dtls');
FIRST_RECORD;
LOOP
EXIT WHEN :workorder_dtls.labour_code IS NULL;
IF :workorder_dtls.pass_no IS NULL THEN
generate_pass_no;
IF :workorder_dtls.pass_no IS NOT NULL THEN
:workorder_dtls.status := 'I';
INSERT INTO workorder_dtls (
labour_code,
workorder_no,
pass_no,
status,
from_dt,
to_dt,
cancil_dt,
issue_dt,
return_dt,
entry_by,
entry_dt)
VALUES (:workorder_dtls.labour_code,
:workorder_dtls.workorder_no,
:workorder_dtls.pass_no,
:workorder_dtls.status,
:workorder_dtls.from_dt,
:workorder_dtls.to_dt,
:workorder_dtls.cancil_dt,
:workorder_dtls.issue_dt,
:workorder_dtls.return_dt,
:workorder_master.entry_by,
:workorder_master.entry_dt);
END IF;
ELSE --if pass no is not null then it is previous record.
UPDATE workorder_dtls SET status = :workorder_dtls.status,
cancil_dt = :workorder_dtls.cancil_dt,
return_dt = :workorder_dtls.return_dt,
entry_by = :workorder_dtls.entry_by,
entry_dt = :workorder_dtls.entry_dt
WHERE labour_code = :workorder_dtls.labour_code
AND workorder_no = :workorder_dtls.workorder_no
AND pass_no = :workorder_dtls.pass_no;
END IF;
IF :workorder_dtls.status = 'I' THEN
UPDATE labour_master SET status = 'I'
WHERE labour_code = :workorder_dtls.labour_code;
END IF;
IF :workorder_dtls.status = 'E' THEN
IF :workorder_dtls.return_dt IS NOT NULL THEN
UPDATE labour_master SET status = 'A'
WHERE labour_code = :workorder_dtls.labour_code;
ELSE
UPDATE labour_master SET status = 'H'
WHERE labour_code = :workorder_dtls.labour_code;
END IF;
END IF;
IF :workorder_dtls.status = 'B' THEN
IF :workorder_dtls.return_dt IS NOT NULL THEN
UPDATE labour_master SET status = 'A'
WHERE labour_code = :workorder_dtls.labour_code;
ELSE
UPDATE labour_master SET status = 'S'
WHERE labour_code = :workorder_dtls.labour_code;
END IF;
END IF;
NEXT_RECORD;
END LOOP;
END IF;
COMMIT;
END;
Thanks and regards,
Vikas