This stored procedure accepts 5 arguments. I'm having problem passing the arguments and local variable into the sql statememts. Can anybody help me with this.
Create or Replace Procedure spVersion(
IN_YR IN Varchar2,
IN_Ver IN Varchar2,
IN_Month IN Varchar2,
IN_Dest_Ver IN Varchar2,
IN_Dest_Month IN VarChar2)
AS
Begin
DECLARE
IN_Year NUMBER(8) :=To_Number(IN_YR);
IN_Year_ID NUMBER(8);
IN_VerID NUMBER(8);
IN_Dest_VerID NUMBER(8);
v_ErsID NUMBER :=1;
v_CurrID NUMBER(8);
v_unitid tbl_base.unit_id%TYPE;
v_yearid tbl_base.year_id%TYPE;
v_lineid tbl_base.line_id%TYPE;
v_verid tbl_base.ver_id%TYPE;
v_jan tbl_base.jan%TYPE;
v_feb tbl_base.feb%TYPE;
v_mar tbl_base.mar%TYPE;
v_Month Char(5);
CURSOR c_Ver1 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM tbl_base
WHERE ver_id =(select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year);
CURSOR c_Ver2 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM finper_base
WHERE ver_id =(select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year) and ers_id = v_ErsID and
curr_ID = (select mem_id from curr where mem_name='USD');
CURSOR c_Ver3 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM finytd_base
WHERE ver_id = (select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year) and ers_id = v_ErsID and
curr_ID = (select mem_id from curr where mem_name='USD');
--********* Get all the version ids from ver table ***************
BEGIN
Select mem_ID
into IN_Year_ID
from year
where mem_name = IN_Year;
Select mem_ID
into IN_VerID
from ver
where mem_name like IN_Ver;
Select mem_id
into IN_Dest_VerID
from ver
where mem_name like IN_Dest_Ver;
Select mem_id
into v_currID
from curr
where mem_name like 'USD';
if (IN_Month like 'jan')
then v_Month := 'v_jan';
elsif (IN_Month like 'feb')
then v_Month := 'v_feb';
elsif (IN_Month like 'mar')
then v_Month := 'v_mar';
end if;
OPEN c_Ver1;
LOOP
FETCH c_Ver1 INTO v_unitid, v_yearid, v_lineid, v_verid, v_Month;
EXIT WHEN c_Ver1%NOTFOUND;
EXECUTE IMMEDIATE 'Update tbl_base
set IN_Dest_Month = v_Month
where year_id = v_yearid and unit_id = v_unitid and ver_id=IN_Dest_VerID and line_id=v_lineid';
IF SQL%NOTFOUND THEN
EXECUTE IMMEDIATE 'Insert into tbl_base
(year_id,line_id,unit_id,ver_id,PO, IN_Dest_Month )
values(v_yearid,v_lineid,v_unitid, IN_Dest_VerID,null,v_Month )';
END IF;
END LOOP;
--Free resources used by cursor
CLOSE c_Ver1;
CLOSE c_Ver2;
CLOSE c_Ver3;
--Commit update
COMMIT;
end;
end;
/
Create or Replace Procedure spVersion(
IN_YR IN Varchar2,
IN_Ver IN Varchar2,
IN_Month IN Varchar2,
IN_Dest_Ver IN Varchar2,
IN_Dest_Month IN VarChar2)
AS
Begin
DECLARE
IN_Year NUMBER(8) :=To_Number(IN_YR);
IN_Year_ID NUMBER(8);
IN_VerID NUMBER(8);
IN_Dest_VerID NUMBER(8);
v_ErsID NUMBER :=1;
v_CurrID NUMBER(8);
v_unitid tbl_base.unit_id%TYPE;
v_yearid tbl_base.year_id%TYPE;
v_lineid tbl_base.line_id%TYPE;
v_verid tbl_base.ver_id%TYPE;
v_jan tbl_base.jan%TYPE;
v_feb tbl_base.feb%TYPE;
v_mar tbl_base.mar%TYPE;
v_Month Char(5);
CURSOR c_Ver1 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM tbl_base
WHERE ver_id =(select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year);
CURSOR c_Ver2 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM finper_base
WHERE ver_id =(select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year) and ers_id = v_ErsID and
curr_ID = (select mem_id from curr where mem_name='USD');
CURSOR c_Ver3 IS
SELECT unit_id, year_id, line_id, ver_id, IN_Month
FROM finytd_base
WHERE ver_id = (select mem_id from ver where mem_name = IN_Ver) and year_ID =(select mem_id from year where mem_name = IN_Year) and ers_id = v_ErsID and
curr_ID = (select mem_id from curr where mem_name='USD');
--********* Get all the version ids from ver table ***************
BEGIN
Select mem_ID
into IN_Year_ID
from year
where mem_name = IN_Year;
Select mem_ID
into IN_VerID
from ver
where mem_name like IN_Ver;
Select mem_id
into IN_Dest_VerID
from ver
where mem_name like IN_Dest_Ver;
Select mem_id
into v_currID
from curr
where mem_name like 'USD';
if (IN_Month like 'jan')
then v_Month := 'v_jan';
elsif (IN_Month like 'feb')
then v_Month := 'v_feb';
elsif (IN_Month like 'mar')
then v_Month := 'v_mar';
end if;
OPEN c_Ver1;
LOOP
FETCH c_Ver1 INTO v_unitid, v_yearid, v_lineid, v_verid, v_Month;
EXIT WHEN c_Ver1%NOTFOUND;
EXECUTE IMMEDIATE 'Update tbl_base
set IN_Dest_Month = v_Month
where year_id = v_yearid and unit_id = v_unitid and ver_id=IN_Dest_VerID and line_id=v_lineid';
IF SQL%NOTFOUND THEN
EXECUTE IMMEDIATE 'Insert into tbl_base
(year_id,line_id,unit_id,ver_id,PO, IN_Dest_Month )
values(v_yearid,v_lineid,v_unitid, IN_Dest_VerID,null,v_Month )';
END IF;
END LOOP;
--Free resources used by cursor
CLOSE c_Ver1;
CLOSE c_Ver2;
CLOSE c_Ver3;
--Commit update
COMMIT;
end;
end;
/