l've tested my script and it does not work.If l insert records the first time around say from the 1st to the 2nd of
Nov 2001.It inserts into the hist table the same number of records.Then from the 3rd to the 4th, then the 5th to the
8th. If l insert the the 1st to the 2nd again l get duplicates.Where am l going wrong or how can l correct
my procedure so that it does not allow duplicates into the history table
CREATE OR REPLACE PROCEDURE Mis_Workflow_Capital (
v_start_date DATE,
v_end_date DATE,
v_num NUMBER,
v_change_date DATE
)
IS
v_cnt NUMBER;
BEGIN
insert mis_wrkflw_capital(
.....etc
-----------------------------------------------------------------------------------------------
--- Check The Mis_Wrkflw_Capital Table For Duplicates Before Insertion Into The History Table
-----------------------------------------------------------------------------------------------
v_cnt := 0;
SELECT COUNT (*)
INTO v_cnt
FROM Mis_Wrkflw_Cap_Hist
WHERE TO_DATE (v_change_date, 'yyyy-mm-dd HH24:MI:SS') =
TO_DATE (change_date, 'yyyy-mm-dd HH24:MI:SS')
AND SYSDATE = TO_DATE (summary_date, 'yyyy-mm-dd HH24:MI:SS');
IF v_cnt = 0
THEN
INSERT INTO Mis_Wrkflw_Cap_Hist
(num, branch, sbrokercd, received, rec_count, prequota,
prequota_count, batched, batched_count, scanned_received,
scanrec_count, captured, cpa_count, checked, chkd_count,
rules, rules_count, returned, ret_count, disburse,
dis_count, active, active_count, pre_reject, prerej_count,
rejected, rej_count, summary_date, change_date)
SELECT v_num, branch, sbrokercd, received, rec_count, prequota,
prequota_count, batched, batched_count, scanned_received,
scanrec_count, captured, cpa_count, checked, chkd_count,
rules, rules_count, returned, ret_count, disburse, dis_count,
active, active_count, pre_reject, prerej_count, rejected,
rej_count, summary_date, change_date
FROM Mis_Wrkflw_Capital;
END IF;
COMMIT;
END;
|
] 
Blog
Search
Help
Members
Register
Login
Home
