eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_SC_GEN_001 SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 17


gmaheswa        4-May-2006	Modified size of var l_out_string, l_upper_string in Replace String Method to 32000, as select stmt can be 32000.
-----------------------------------------------------------

******************************************************************/
-- -----------------------------------------------------------------
-- Define the global variables to be used in this package.
-- -----------------------------------------------------------------

  TYPE g_grant_cond_rec IS RECORD (obj_attrib_id     igs_sc_grant_conds.obj_attrib_id%TYPE,
                                   user_attrib_id    igs_sc_grant_conds.user_attrib_id%TYPE,
				   condition         igs_sc_grant_conds.condition%TYPE,
				   text_value        igs_sc_grant_conds.text_value%TYPE,
				   user_attrib_value igs_sc_usr_att_vals.attr_value%TYPE,
				   obj_attrib_value  igs_sc_obj_att_vals.attr_value%TYPE,
				   cond_text         VARCHAR2(4000),
                                   obj_const         VARCHAR2(4000),
				   close_part        VARCHAR2(100),
				    z_typ_flag        VARCHAR2(1) --mmkumar
				   );

Line: 100


                            p_select_text VARCHAR2
			   )RETURN BOOLEAN
IS
   l_api_name       CONSTANT VARCHAR2(30)   := 'CHECK_GRANT_TEXT';

Line: 105


   l_select_text VARCHAR(32000);

Line: 108


   l_select_text := replace_string(LTRIM(p_select_text),':PARTY_ID','igs_sc_vars.get_partyid');

Line: 109


   l_select_text := replace_string(LTRIM(l_select_text),':USER_ID','igs_sc_vars.get_userid');

Line: 110


   l_select_text := replace_string(LTRIM(l_select_text),':TBL_ALIAS','tstal');

Line: 111


   EXECUTE IMMEDIATE 'SELECT count(*) FROM ('||' SELECT 1 FROM '||p_table_name||' WHERE '||l_select_text||' )' INTO l_val;

Line: 144


  l_attr_select  VARCHAR2(32000);

Line: 145


  l_usr_select   VARCHAR2(32000);

Line: 146


  l_obj_select   VARCHAR2(32000);

Line: 154


  l_select       VARCHAR2(32000);

Line: 157


  l_SelectStatement VARCHAR2(32000);

Line: 162


  SELECT gr.grant_id ,
         obj.object_id,
	 obj.obj_group_id ,
	 gr.grant_text
  FROM   igs_sc_grants  gr,
         igs_sc_objects obj,
         fnd_objects    fnd,
         wf_local_user_roles rls
  WHERE  gr.grant_insert_flag='Y'
  AND gr.locked_flag ='Y'
  AND obj.obj_group_id =  gr.obj_group_id
  AND obj.object_id = fnd.object_id
  AND fnd.application_id IN (8405,8406)
  AND UPPER( fnd.obj_name) = cp_object
  AND rls.user_orig_system_id = cp_user_id
  AND rls.role_orig_system = 'IGS'
  AND rls.role_orig_system_id = gr.user_group_id
  AND SYSDATE BETWEEN NVL(rls.start_date,SYSDATE-1) AND NVL(rls.expiration_date,SYSDATE+1)
  ORDER BY gr.grant_id ;

Line: 183


  SELECT grant_where
  FROM igs_sc_obj_grants
  WHERE object_id = s_obj_id
  AND grant_id = s_grant_id
  FOR UPDATE OF grant_where;

Line: 191


  SELECT grant_id,
         grant_cond_num,
	 obj_attrib_id,
	 user_attrib_id,
	 condition,
	 text_value
  FROM   igs_sc_grant_conds
  WHERE  grant_id = s_grant_id;

Line: 202


  SELECT *
  FROM igs_sc_usr_attribs
  WHERE user_attrib_id = s_attrib_id;

Line: 207


  SELECT *
  FROM igs_sc_obj_attribs
  WHERE obj_group_id = s_obj_group_id
  AND   obj_attrib_id= s_obj_attrib_id;

Line: 214


  SELECT b.DEFAULT_POLICY_TYPE
  FROM igs_sc_objects a,
       igs_sc_obj_groups b,
       fnd_objects c
  WHERE c.obj_name = p_obj_name
  AND c.object_id = a.object_id
  AND b.obj_group_id = a.obj_group_id;

Line: 223


  SELECT null_allow_flag
  FROM igs_sc_obj_att_mths
  WHERE object_id = cp_object_id
  AND obj_attrib_id = cp_obj_attrib_id;

Line: 282


  fnd_dsql.add_text('SELECT 1 FROM DUAL WHERE ');

Line: 335


		      l_usr_select := '';

Line: 339


		      L_OBJ_SELECT := '';

Line: 341


		      -- Construct select stmt from the fetched grant condition
		      IF l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id  IS NOT NULL THEN
			-- Get the object attribute name for the object attribute ID.
			OPEN c_obj_attrib (l_grant_cond(c_grant_cond_rec.grant_cond_num).obj_attrib_id ,grants_rec.obj_group_id );

Line: 357


			   IF(INSTR(P_ATTRIB_TAB.ADVISOR,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.ADVISOR;

Line: 363


			   IF(INSTR(P_ATTRIB_TAB.ADVISOR_PERSON_ID,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.ADVISOR_PERSON_ID;

Line: 369


			   IF(INSTR(P_ATTRIB_TAB.APPLICATION_PROGRAM_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.APPLICATION_PROGRAM_CODE;

Line: 375


			   IF(INSTR(P_ATTRIB_TAB.APPLICATION_TYPE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.APPLICATION_TYPE;

Line: 381


			   IF(INSTR(P_ATTRIB_TAB.INSTRUCTOR_ID,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.INSTRUCTOR_ID;

Line: 387


			   IF(INSTR(P_ATTRIB_TAB.INSTRUCTOR_PERSON_ID,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.INSTRUCTOR_PERSON_ID;

Line: 393


			   IF(INSTR(P_ATTRIB_TAB.LOCATION,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.LOCATION;

Line: 399


			   IF(INSTR(P_ATTRIB_TAB.NOMINATED_COURSE_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.NOMINATED_COURSE_CODE;

Line: 405


			   IF(INSTR(P_ATTRIB_TAB.ORGANIZATIONAL_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.ORGANIZATIONAL_UNIT_CODE;

Line: 411


			   IF(INSTR(P_ATTRIB_TAB.OWNING_ORG_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.OWNING_ORG_UNIT_CODE;

Line: 417


			   IF(INSTR(P_ATTRIB_TAB.PERSON_ID,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PERSON_ID;

Line: 423


			   IF(INSTR(P_ATTRIB_TAB.PERSON_TYPE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PERSON_TYPE;

Line: 429


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATTEMPT_ADVISOR,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATTEMPT_ADVISOR;

Line: 435


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATTEMPT_LOCATION,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATTEMPT_LOCATION;

Line: 441


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATT_OWNING_ORG_UNIT_CD,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATT_OWNING_ORG_UNIT_CD;

Line: 447


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATT_RESP_ORG_UNIT_CD,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATT_RESP_ORG_UNIT_CD;

Line: 453


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_ATTEMPT_TYPE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_ATTEMPT_TYPE;

Line: 459


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_OWNING_ORG_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_OWNING_ORG_UNIT_CODE;

Line: 465


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_RESP_ORG_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_RESP_ORG_UNIT_CODE;

Line: 471


			   IF(INSTR(P_ATTRIB_TAB.PROGRAM_TYPE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.PROGRAM_TYPE;

Line: 477


			   IF(INSTR(P_ATTRIB_TAB.RESPONSIBLE_ORG_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.RESPONSIBLE_ORG_UNIT_CODE;

Line: 483


			   IF(INSTR(P_ATTRIB_TAB.TEACHING_ORG_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.TEACHING_ORG_UNIT_CODE;

Line: 489


			   IF(INSTR(P_ATTRIB_TAB.UNIT_LOCATION,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_LOCATION;

Line: 495


			   IF(INSTR(P_ATTRIB_TAB.UNIT_MODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_MODE;

Line: 501


			   IF(INSTR(P_ATTRIB_TAB.UNIT_ATT_ORG_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATT_ORG_UNIT_CODE;

Line: 507


			   IF(INSTR(P_ATTRIB_TAB.UNIT_ATTEMPT_LOCATION,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATTEMPT_LOCATION;

Line: 513


			   IF(INSTR(P_ATTRIB_TAB.UNIT_ATTEMPT_INSTRUCTOR,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATTEMPT_INSTRUCTOR;

Line: 519


			   IF(INSTR(P_ATTRIB_TAB.UNIT_ATTEMPT_MODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.UNIT_ATTEMPT_MODE;

Line: 525


			   IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_ORG_UNIT_CODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_ORG_UNIT_CODE;

Line: 531


			   IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_LOCATION,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_LOCATION;

Line: 537


			   IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_INSTRUCTOR,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_INSTRUCTOR;

Line: 543


			   IF(INSTR(P_ATTRIB_TAB.OTHER_UNIT_MODE,'SELECT') > 0) THEN
			      L_OBJ_SELECT := P_ATTRIB_TAB.OTHER_UNIT_MODE;

Line: 564


			  -- Check for being multi-value attribute. T Table column name,  S select statement,  F Function call,  M - multy values - select only
			  IF l_user_attrib.static_type = 'D' AND l_user_attrib.user_attrib_type <> 'F' THEN
			      -- Dynamic attribute - we need to append the actual select for this attribute
			      l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');

Line: 568


			      l_attr_select := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');

Line: 570


			      l_usr_select := replace_string(ltrim(l_attr_select),':TBL_ALIAS',l_usr_alias);

Line: 573


			     l_attr_select := replace_string(ltrim(l_user_attrib.select_text),':PARTY_ID','igs_sc_vars.get_partyid');

Line: 574


			     l_usr_const := replace_string(ltrim(l_attr_select),':USER_ID','igs_sc_vars.get_userid');

Line: 578


			     -- Add select from values table
			     l_usr_select :='SELECT '||l_usr_alias||'.attr_value FROM igs_sc_usr_att_vals '||l_usr_alias||' WHERE '||l_usr_alias||'.user_id=igs_sc_vars.get_userid AND '
					    ||l_usr_alias||'.user_attrib_id='||c_grant_cond_rec.user_attrib_id;

Line: 592


		      -- l_obj_select, l_usr_select - select statments.

			    --**  Statement level logging.
			    IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
			      l_label := 'igs.plsql.igs_sc_gen_001.check_ins_security';

Line: 597


			      l_debug_str :=  'l_obj_select is : '|| l_obj_select || ' and l_obj_const is ' || l_obj_const;

Line: 606


			      l_debug_str :=  'l_usr_select is : '|| l_usr_select || ' and l_usr_const is ' || l_usr_const;

Line: 617


		    IF l_obj_select IS NULL THEN
		         --code added my mmkumar
			 IF L_NULL_FLAG = 'Z' THEN
			       --IF isSingleGrantCond(p_grants_rec.grant_text) THEN
			       IF onlyZTypeAttributes THEN
				    l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' 1=1 ';

Line: 631


			      l_debug_str :=  'l_obj_select is not null : '|| l_obj_select;

Line: 637


			     IF  l_obj_const IS NOT NULL AND l_usr_select IS NOT NULL THEN
			         -- User attribute is select of any kind and object attribute is not select
			         -- User Select = Obj CONST
			         l_found_pos := INSTR(UPPER(ltrim(l_usr_select)),'FROM',1,1);

Line: 641


			         l_column_name := substr(ltrim(l_usr_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'

Line: 642


   			         --grant text ' EXISTS (object_select AND Column Condition ( user attrr select))
 			         l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text
				      ||' EXISTS ('||l_usr_select||' AND '||l_column_name||' '||check_operation(l_grant_cond(c_grant_cond_rec.grant_cond_num).condition)||' ';   -- ||l_obj_const||' ))';

Line: 647


			     ELSIF l_obj_const IS NULL  AND l_usr_select IS NOT NULL THEN
			         IF L_NULL_FLAG = 'Y' THEN
				     l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' 1=1 ';                    --mmmkumar, replaced return true

Line: 651


			         -- Colunmn name = User Select
			         l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' '||
			         l_grant_cond(c_grant_cond_rec.grant_cond_num).condition ||' (' ||l_usr_select||') '||l_post_grant;

Line: 660


		    ELSE --Object select is not null
			  --find the name of the select coulmn for attribute
			  l_found_pos := INSTR(UPPER(ltrim(l_obj_select)),'FROM',1,1);

Line: 663


			  l_column_name := substr(ltrim(l_obj_select),8,l_found_pos-9); -- 8 position 'select ' found -9 ' FROM'

Line: 664


			  --grant text ' EXISTS (object_select AND Column Condition ( user attrr select))


		           --code added my mmkumar
                    IF L_NULL_FLAG  = 'Z' THEN
 	                 IF onlyZTypeAttributes THEN
	                    l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text :=  l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' 1=1 ';

Line: 676


			  IF l_usr_select IS NOT NULL THEN
			      --Add user select
			      l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' EXISTS ('||l_obj_select||' AND '||l_column_name||' '||
			      l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' ('||l_usr_select||' )';

Line: 685


				    --operator in the grant text - don't add anything but Object select
				    l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_obj_select;

Line: 688


				    l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text := l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' EXISTS ('||l_obj_select||' AND '||l_column_name||' '||
				    l_grant_cond(c_grant_cond_rec.grant_cond_num).condition||' '||l_usr_const;

Line: 697


			      l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text :=  l_grant_cond(c_grant_cond_rec.grant_cond_num).cond_text||' OR NOT EXISTS (' ||l_obj_select||'))';

Line: 811


	      l_debug_str :=  'Final Select: '|| fnd_dsql.get_text(); --l_statment;

Line: 818


		l_SelectStatement := fnd_dsql.get_text(FALSE);

Line: 823


		      l_debug_str :=  'statement to be executed : '|| l_SelectStatement;

Line: 830


		DBMS_SQL.PARSE (l_ext_cursor, l_SelectStatement, DBMS_SQL.V7);

Line: 838


			    l_debug_str := 'Final Select: TRUE ';

Line: 850


		    l_debug_str := 'Final Select: TRUE ';

Line: 861


		    l_debug_str := 'Final Select: FALSE ';

Line: 975


 P_Action   IN VARCHAR2, --(U/D - Update/Delete)
 P_Msg_data OUT NOCOPY VARCHAR2) -- return the error message in case of any exceptions.
RETURN BOOLEAN IS -- TRUE if update/delete privileges are there else return FALSE
 ------------------------------------------------------------------
  --Updated by  : gmaheswa, Oracle India
  --Date created:  27-MAY-2001
  --
  --Purpose:main wrapper for pre-check security, (only for select,upd, del)
  -- different for insert, as evaluation for insert is different.
  --
  --Change History:
------------------------------------------------------------------

l_obj_exists_cur VARCHAR2(4000);

Line: 990


L_SELECT_STMT VARCHAR2(32000);

Line: 1007


	l_obj_exists_cur := 'SELECT 1 FROM IGS_SC_OBJECTS SC, FND_OBJECTS FND WHERE SC.OBJECT_ID = FND.OBJECT_ID AND FND.OBJ_NAME =  UPPER(:P_Tab_Name) AND ';

Line: 1009


          L_OBJ_EXISTS_CUR := L_OBJ_EXISTS_CUR||' SC.SELECT_FLAG = ''Y''';

Line: 1010


          l_operation := 'SELECT';

Line: 1012


          L_OBJ_EXISTS_CUR := L_OBJ_EXISTS_CUR||' SC.UPDATE_FLAG = ''Y''';

Line: 1013


          l_operation := 'UPDATE';

Line: 1015


          L_OBJ_EXISTS_CUR := L_OBJ_EXISTS_CUR||' SC.DELETE_FLAG = ''Y''';

Line: 1016


          l_operation := 'DELETE';

Line: 1051


       L_SELECT_STMT := 'SELECT 1 FROM '||P_Tab_Name||' WHERE ROWID = :1 AND ('||L_WHERE_CLAUSE||')';

Line: 1054


          l_label := 'igs.plsql.igs_sc_gen_001.check_sel_upd_del_security.finalselect';

Line: 1055


          l_debug_str := 'Final Select: '||L_SELECT_STMT;

Line: 1059


       OPEN Grant_cv FOR L_SELECT_STMT USING P_ROWID;

Line: 1065


            l_debug_str := 'Final Select: TRUE ';

Line: 1073


            l_debug_str := 'Final Select: FALSE ';

Line: 1099


 P_Action   IN VARCHAR2, --(S/U - Select/Update)
 P_Msg_data OUT NOCOPY VARCHAR2) -- return the error message in case of any exceptions.
 ------------------------------------------------------------------
  --Updated by  : gmaheswa, Oracle India
  --Date created:  27-MAY-2001
  --
  --Purpose:main wrapper for person security used by UIs
  --
  --Change History:
------------------------------------------------------------------
RETURN BOOLEAN IS

L_ROWID ROWID;

Line: 1117


L_SELECT_STMT  VARCHAR2(32000);

Line: 1130


   get_row_id_cur := 'SELECT ROWID FROM '||P_Table_Name||' WHERE PARTY_ID = :P_Person_id';

Line: 1159


            L_SELECT_STMT := 'SELECT 1 FROM IGS_PE_PERSON_BASE_V WHERE PERSON_ID = :1 AND ('||L_WHERE_CLAUSE||')';

Line: 1164


		     l_debug_str :=  'L_SELECT_STMT is  : '|| L_SELECT_STMT;

Line: 1169


            OPEN Grant_cv FOR L_SELECT_STMT USING P_Person_id;

Line: 1251


	SELECT role_orig_system_id
	FROM  wf_local_user_roles role, fnd_user use
	WHERE role_orig_system = cp_igs
	AND user_orig_system = cp_fnd
	AND user_orig_system_id  = cp_fnd_user
	AND role.user_name = use.user_name
	AND use.user_id = cp_fnd_user;

Line: 1261


SELECT default_policy_type, obj_group_id
FROM IGS_SC_OBJ_GROUPS
WHERE obj_group_name = cp_bo_name;

Line: 1266


SELECT grant_id,locked_flag ,grant_select_flag,grant_update_flag,grant_insert_flag,grant_delete_flag
FROM IGS_SC_GRANTS
WHERE user_group_id = cp_role_id
AND obj_group_id =cp_bo_id
AND locked_flag= 'Y';

Line: 1309


IF p_action='S' AND ( policy_rec.grant_select_flag='Y')  THEN
   l_return:= 'POLICY_EXIST';

Line: 1311


ELSIF  p_action='I' AND policy_rec.grant_insert_flag='Y' THEN
   l_return:= 'POLICY_EXIST';

Line: 1313


ELSIF  p_action='U' AND policy_rec.grant_update_flag='Y' THEN
   l_return:= 'POLICY_EXIST';

Line: 1315


ELSIF  p_action='D' AND policy_rec.grant_delete_flag='Y' THEN
   l_return:= 'POLICY_EXIST';