SQL & PL/SQL

select * from (
select distinct OTI.REQUEST_KEY,
(Select distinct rbe.RBE_ENTITY_NAME from  DEVT_OIM.REQUEST_BENEFICIARY_ENTITIES rbe where rbe.RBE_REQUEST_KEY = OTI.REQUEST_KEY) as Application,
(select
distinct REGEXP_SUBSTR(a.orc_tos_instance_key, '[^:]+')from DEVT_OIM.ORC a, DEVT_OIM.OTI b where a.ORC_KEY = b.ORC_KEY and b.REQUEST_KEY = OTI.REQUEST_KEY )
as MODULE,
(select
distinct TRIM(REPLACE((REGEXP_SUBSTR(a.orc_tos_instance_key, ':[^:]+')),':','')) from DEVT_OIM.ORC a, DEVT_OIM.OTI b where a.ORC_KEY = b.ORC_KEY and b.REQUEST_KEY = OTI.REQUEST_KEY) as SITE,
(select
distinct TRIM(REPLACE((REGEXP_SUBSTR(a.orc_tos_instance_key, ':[^:]+',1,2)),':','')) from DEVT_OIM.ORC a, DEVT_OIM.OTI b where a.ORC_KEY = b.ORC_KEY and b.REQUEST_KEY = OTI.REQUEST_KEY)
as RESPONSIBILITY,
(select distinct usr.usr_login from DEVT_OIM.usr usr,DEVT_OIM.REQUEST_BENEFICIARY rb where usr.usr_key = rb.BENEFICIARY_KEY and rb.REQUEST_KEY=OTI.REQUEST_KEY) as BENEFICIARY_GID,
(select distinct usr.usr_display_name from DEVT_OIM.usr usr,DEVT_OIM.REQUEST_BENEFICIARY rb where usr.usr_key = rb.BENEFICIARY_KEY and rb.REQUEST_KEY=OTI.REQUEST_KEY) as BENEFICIARY_NAME,
(select distinct usr.usr_status from DEVT_OIM.usr usr,DEVT_OIM.REQUEST_BENEFICIARY rb where usr.usr_key = rb.BENEFICIARY_KEY and rb.REQUEST_KEY=OTI.REQUEST_KEY) as BENEFICIARY_STATUS,
(Select TO_CHAR(request.request_creation_date,'DD-Mon-YYYY HH:MI:SS AM') from DEVT_OIM.request request where request.request_key = OTI.REQUEST_KEY) as REQUEST_DATE,
TO_CHAR(oti.osi_assigned_date,'DD-Mon-YYYY HH:MI:SS AM')  as ASSIGNED_DATE,
(Select request.request_model_name from DEVT_OIM.request request where request.request_key = OTI.REQUEST_KEY) as REQUEST_TYPE,
(Select distinct ugp.ugp_name from  DEVT_OIM.UGP ugp where ugp.ugp_key = OTI.osi_assigned_to_ugp_key) as ASSIGNED_TO,
to_char(trunc(sysdate - OTI.osi_assigned_date, 0)) as DAYS_PENDING
from DEVT_OIM.OTI OTI, DEVT_OIM.OBJ obj, DEVT_OIM.MIL mil, DEVT_OIM.REQUEST_BENEFICIARY_ENTITIES rbe
WHERE OTI.PKG_TYPE = 'Provisioning'
and OTI.STA_BUCKET = 'Pending'
and OTI.OSI_ASSIGNED_TO_UGP_KEY <> 1 and OTI.SCH_STATUS <> 'R'
and OTI.MIL_KEY in (select mil_key from DEVT_OIM.mil where mil_name = 'Revoke Entitlement')
AND OTI.REQUEST_KEY IS NOT NULL
and oti.mil_key = mil.mil_key
and oti.obj_key = obj.obj_key and obj.obj_name not like ('% Base') and obj.obj_name not like ('%eBusiness Suite User%')
and rbe.RBE_ENTITY_NAME = (select EXTRA_FIELD_2 from EC_ADMIN.APPLICATION EC where  EC.EXTRA_FIELD_2 =rbe.RBE_ENTITY_NAME and UPPER(EC.AUTO_PROVISIONING) = 'FALSE')
and  rbe.RBE_REQUEST_KEY = OTI.REQUEST_KEY
and (:P_APPLICATION = ' ' OR :P_APPLICATION IS NULL OR :P_APPLICATION  = 'All' OR  upper(rbe.RBE_ENTITY_NAME) like upper (NVL(:P_APPLICATION,'%')))
and (:P_REQUESTID = ' ' OR :P_REQUESTID is NULL OR OTI.REQUEST_KEY=:P_REQUESTID) 
 and
(:P_BENEFICIARY = ' ' OR :P_BENEFICIARY IS NULL OR :P_BENEFICIARY  = ' ' or exists
(Select 1 from DEVT_OIM.REQUEST_BENEFICIARY rb,
                    DEVT_OIM.usr u
                    where rb.beneficiary_key = u.usr_key
                    and rb.REQUEST_KEY = OTI.REQUEST_KEY
                    and upper(u.usr_login) like upper(trim(NVL(:P_BENEFICIARY,'%')))))
and
(:P_ITPROCESSOR = ' ' OR :P_ITPROCESSOR IS NULL OR :P_ITPROCESSOR  = ' ' or exists
(Select 1 from DEVT_OIM.OTI OTI,DEVT_OIM.UGP UGP where
                                        UGP.ugp_key = OTI.osi_assigned_to_ugp_key
                                        and OTI.REQUEST_KEY = OTI.REQUEST_KEY
                    and UGP.UGP_ROLENAME like 'Pwd%'
                    and upper(UGP.UGP_ROLENAME) = upper(trim(NVL
(:P_ITPROCESSOR,'%')))))
) where Application is not null 
--and (:P_MODULE = ' ' or :P_MODULE is null or exists(MODULE like :P_MODULE))

above is my full query and dont want to bother about all i just have a problem with my last commented condition

--and (:P_MODULE = ' ' or :P_MODULE is null or exists(MODULE like :P_MODULE))

when i remove the comments iam getting error as

ORA-00928: missing SELECT keyword
00928. 00000 - "missing SELECT keyword"
*Cause:
*Action:
Error at Line: 48 Column: 62

i just want to check the condition as P_module is whether empty or null if not have to parameter which i pass in to :P_MODULE like MODULE.

i am just trying to do it as this condition

(:P_BENEFICIARY = ' ' OR :P_BENEFICIARY IS NULL OR :P_BENEFICIARY  = ' ' or exists
(Select 1 from DEVT_OIM.REQUEST_BENEFICIARY rb,
                    DEVT_OIM.usr u
                    where rb.beneficiary_key = u.usr_key
                    and rb.REQUEST_KEY = OTI.REQUEST_KEY
                    and upper(u.usr_login) like upper(trim(NVL(:P_BENEFICIARY,'%')))))

but in my requirement there is no select statement.