In the first part of the procedure I am looking for a restriction based on a passed in laborcode.
If a restriction existed based on the laborcode I want to pass this to the cursor so that I can prohibit the return if the wonum if a restriction is present based on the cursor. I am basically wanting to use the v_userrestrictions as the filter. In a particluar case the result of the first query could set v_userrestrictions = (wol4 IN (SELECT pc_cc FROM wdw_fbr WHERE gn = 'PLNRST')).
If I do this:
AND ('|| v_userrestrictions ||')'; --I get errors
If I do this:
AND (wol4 IN (SELECT pc_cc FROM wdw_fbr WHERE gn = 'PLNRST')) --filters
So basically I am tryning to pass the v_userrestrictions to the cursor
--------------------------
----------
----------
----------
----------
----------
----------
----------
----------
----------
----------
------
PROCEDURE GetByWonumLaborcode (p_wonum IN VARCHAR2, p_laborcode IN VARCHAR2, p_recordset OUT refcur)
IS
v_userrestrictions VARCHAR2(254);
BEGIN
--Get the userrestrictions so we can use them in the filter below.
SELECT NVL(u.restrictions, '1=1')
INTO v_userrestrictions
FROM userrestrictions u
WHERE u.app = 'WOTRACK' AND
u.username = (SELECT usrname FROM usergroups m WHERE laborcode = p_laborcode);
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_userrestrictions := '1=1';
--this will have about 20 columns to return once resolve is found
OPEN p_recordset FOR
'SELECT w.wonum
FROM workorder w
WHERE w.wonum = p_wonum
AND w.historyflag = ''N''
AND ('|| v_userrestrictions ||')';
--AND (wol4 IN (SELECT pc_cc FROM wdw_fbr WHERE gn = 'PLNRST'))
END GetByWonumLaborcode;
Thanks
Start Free Trial