Post date: Apr 10, 2016 5:24:56 PM
CREATE OR REPLACE FUNCTION xxtest_sql(p_qry IN VARCHAR2,p_val IN VARCHAR2,p_name VARCHAR2) RETURN VARCHAR2 IS
lv_qry VARCHAR2(4000) := p_qry;
lv1 VARCHAR2(2000);
sql1 VARCHAR2(4000);
sql2 VARCHAR2(4000);
lv2 VARCHAR2(2000);
lv3 VARCHAR2(2000);
lp1 VARCHAR2(100) := 'INT';
BEGIN
IF ( p_name LIKE 'HR%MENU') THEN
SELECT user_menu_name
INTO lv2
FROM fnd_menus_vl fmv
WHERE fmv.menu_name = p_val;
ELSIF ( p_name = 'PER_SECURITY_PROFILE_ID' ) THEN
SELECT S.SECURITY_PROFILE_NAME
INTO lv2
FROM PER_SECURITY_PROFILES S,
PER_BUSINESS_GROUPS O
WHERE O.BUSINESS_GROUP_ID (+) = S.BUSINESS_GROUP_ID
AND s.security_profile_id = p_val;
ELSE
lv1 := SUBSTR(lv_qry,1,INSTR(lv_qry,'\',1,1)-1)||SUBSTR(lv_qry,INSTR(lv_qry,'\',1,2)+1);
lv1 := replace(lv1,'COLUMN = ','COLUMN=');
lv1 := SUBSTR(lv1,1,CASE WHEN INSTR(lv1,'COLUMN=',1,1) = 0 THEN length(lv1) ELSE INSTR(lv1,'COLUMN=',1,1) -1 END );
lv1 := REPLACE (REPLACE(lv1,'SQL=',null),'SQL = ',null);
lv1 := replace(lv1,'"',null);
lv1 := SUBSTR(lv1,1,INSTR(upper(lv1),'INTO',1,1)-1)||SUBSTR(lv1,INSTR(upper(lv1),'FROM',1,1));
SELECT SUBSTR(lv1,1,DECODE(INSTR(upper(lv1),'ORDER BY',1,1),0,length(lv1),(INSTR(upper(lv1),'ORDER BY',1,1)-1)))
INTO lv1
FROM dual ;
sql1 := lv1||CASE WHEN INSTR(UPPER(lv1),'WHERE',1,1)= 0 THEN ' WHERE ' ELSE' AND ' END
||SUBSTR(lv1,INSTR(lv1,',',1,1)+1,(INSTR(upper(lv1),'FROM',1,1)-INSTR(lv1,',',1,1))-1)||' = '''||p_val||'''';
BEGIN
EXECUTE IMMEDIATE sql1 INTO lv2,lv3;
EXCEPTION
WHEN OTHERS THEN
lv2 := null;
END;
IF(lv2 IS NULL) THEN
sql2 := lv1||CASE WHEN INSTR(UPPER(lv1),'WHERE',1,1)= 0 THEN ' WHERE ' ELSE' AND ' END
||SUBSTR(lv1,INSTR(upper(lv1),'SELECT',1,1)+6,(INSTR(lv1,',',1,1)-INSTR(upper(lv1),'SELECT',1,1)-6))||' = '''||p_val||'''';
EXECUTE IMMEDIATE sql2 INTO lv3,lv2;
END IF;
END IF;
return lv2 ;
EXCEPTION
WHEN OTHERS THEN
return (null);
END;
---EOF
/* Formatted on 2016/04/11 00:20 (Formatter Plus v4.8.8) */
SELECT po.profile_option_name "NAME",
po.user_profile_option_name,
DECODE (TO_CHAR (pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER',
'???'
) "LEVEL",
DECODE (TO_CHAR (pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.NAME,
'10004', usr.user_name,
'???'
) "CONTEXT",
rsp.responsibility_name,
pov.profile_option_value "VALUE",
NVL (xxtest_sql (po.sql_validation, pov.profile_option_value, po.profile_option_name), pov.profile_option_value) val,
po.sql_validation
FROM apps.fnd_profile_options_vl po,
apps.fnd_profile_option_values pov,
apps.fnd_user usr,
apps.fnd_application app,
apps.fnd_responsibility_vl rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
WHERE 1 = 1
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id(+) = pov.level_value
AND rsp.application_id(+) = pov.level_value_application_id
AND rsp.responsibility_id(+) = pov.level_value
AND app.application_id(+) = pov.level_value
AND svr.node_id(+) = pov.level_value
AND org.organization_id(+) = pov.level_value
--AND po.profile_option_name LIKE '%MO%'
and rsp.RESPONSIBILITY_NAME like 'SAK HO PO Engineering Staff'