Move Worklogs from Issue to another

/**
 * Move Worklogs from an Issue to another
 * Usage :
 * Begin 
 *  moveWorklogs 'FOO-100','FOO-120', '01/01/2008'
 * end;
 */
create or replace procedure moveWorklogs (
	srcIssueKey		in	varchar2,
	destIssueKey	in	varchar2,
	startDate		in 	varchar2) AS
/** 
 * Parameters
 * ------------------------
 * srcIssueId
 * destIssueId
 * startDate
 */

jiId					integer;
jiTimeOriginalEstimate 	number(18);
jiTimeEstimate 			number(18);
jiTimeSpent  			number(18);

qryStartDate		varchar2(15);

movedTimeSpent 			number(18);

destId					integer;
destTimeOriginalEstimate 	number(18);
destTimeEstimate 			number(18);
destTimeSpent  			number(18);

	----------------------------
	-- List of 
	----------------------------
	CURSOR wl_cur IS
		select wl.id , wl.timeworked from worklog wl, jiraissue ji 
		       where wl.issueid = ji.id 
		         and ji.pkey = srcIssueKey
		         and wl.startdate > qryStartDate;

	wl_rec wl_cur%ROWTYPE;
	
BEGIN
	DBMS_OUTPUT.ENABLE(1000000);

	-- Retrieve Issues Time Details for Old Issue
	select ji.id, ji.timespent, ji.timeoriginalestimate,  ji.timeestimate
		into jiId, jiTimeSpent, jiTimeOriginalEstimate,  jiTimeEstimate
		from jiraissue ji 
		where ji.pkey = srcIssueKey;

	-- Retrieve Issues Time Details for Cloned Issue
	select ji.id, ji.timespent, ji.timeoriginalestimate,  ji.timeestimate
		into destId, destTimeSpent, destTimeOriginalEstimate,  destTimeEstimate
		from jiraissue ji 
		where ji.pkey = destIssueKey;

	-- Init 
	movedTimeSpent := 0;
	qryStartDate := to_date(startDate,'DD/MM/YYYY HH24:mi:ss');

	-- DBMS_OUTPUT.put_line('startDate [' || startDate || ','  || qryStartDate || ',' || to_char(to_date(startDate,'DD/MM/YYYY'),'DD/MM/YYYY HH:mm') || ']');

	-- Loop on found Worklog and change owning Issue
	OPEN wl_cur;
	LOOP
    	FETCH wl_cur INTO wl_rec;
    	EXIT WHEN wl_cur%NOTFOUND;

		DBMS_OUTPUT.put_line('Worklog Id [' || wl_rec.id || '] Timeworked [' || wl_rec.timeworked || ']');

		-- Change Owning Issue for current Worklog
		UPDATE worklog wl set wl.issueid = destId where wl.id = wl_rec.id;  
		DBMS_OUTPUT.put_line('Worklog Id [' || wl_rec.id || '] transfered to Issue [' || destId || ']');
		
		-- Calculate Move Timespent
		movedTimeSpent := movedTimeSpent + wl_rec.timeworked; 
		DBMS_OUTPUT.put_line('movedTimeSpent [' || movedTimeSpent || ']');

	END LOOP;
	CLOSE wl_cur;

	if movedTimeSpent <> 0 then
		DBMS_OUTPUT.put_line('Cloned TimeOriginalEstimate [' || destTimeOriginalEstimate || ']');
		DBMS_OUTPUT.put_line('Cloned TimeEstimate [' || destTimeEstimate || ']');
		DBMS_OUTPUT.put_line('Cloned TimeSpent [' || destTimeSpent || ']');
	
		DBMS_OUTPUT.put_line('Old TimeOriginalEstimate [' || jiTimeOriginalEstimate || ']');
		DBMS_OUTPUT.put_line('Old TimeEstimate [' || jiTimeEstimate || ']');
		DBMS_OUTPUT.put_line('Old TimeSpent [' || jiTimeSpent || ']');
	
		if destTimeSpent IS NULL then 
			destTimeSpent:=0;
		end if;
	
		if jiTimeEstimate IS NULL then 
			jiTimeEstimate:=0;
		end if;
		if destTimeOriginalEstimate IS NULL then 
			destTimeOriginalEstimate:=0;
		end if;
	
		-- Update time Details for Cloned Issue
		-- destTimeOriginalEstimate		:= GREATEST(destTimeOriginalEstimate, jiTimeEstimate + movedTimeSpent);   
		destTimeOriginalEstimate		:= jiTimeEstimate + movedTimeSpent;   
		destTimeSpent				    := destTimeSpent + movedTimeSpent;
		destTimeEstimate			    := destTimeOriginalEstimate - destTimeSpent;
	
		DBMS_OUTPUT.put_line('Cloned TimeOriginalEstimate [' || destTimeOriginalEstimate || ']');
		DBMS_OUTPUT.put_line('Cloned TimeEstimate [' || destTimeEstimate || ']');
		DBMS_OUTPUT.put_line('Cloned TimeSpent [' || destTimeSpent || ']');

		UPDATE jiraissue ji 
			set ji.timeoriginalestimate = destTimeOriginalEstimate, 
				ji.timeestimate = destTimeEstimate,
				ji.timespent = destTimeSpent
			where ji.id = destId;
	
		-- Update time Details for Old Issue
		-- jiTimeOriginalEstimate	:= 0;
		jiTimeEstimate 			:= 0;
		jiTimeSpent 			:= jiTimeSpent - movedTimeSpent;
	
		DBMS_OUTPUT.put_line('Old TimeOriginalEstimate [' || jiTimeOriginalEstimate || ']');
		DBMS_OUTPUT.put_line('Old TimeEstimate [' || jiTimeEstimate || ']');
		DBMS_OUTPUT.put_line('Old TimeSpent [' || jiTimeSpent || ']');

		UPDATE jiraissue ji 
			set ji.timeoriginalestimate = jiTimeOriginalEstimate, 
				ji.timeestimate = jiTimeEstimate,
				ji.timespent = jiTimeSpent
			where ji.id = jiId;
	
	end if;


END moveWorklogs;