SELECT /*+ FIRST_ROWS */
       SocialOfficeTransaction.WORK_DATE,
       SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID,
       SocialOfficeTransaction.POI_TIN,
       Wbs.WBS_ID,
       SoTransactionStatus.NAME AS SO_TRANSACTION_STATUS_NAME,
       SoTransactionStatus.SO_TRANSACTION_STATUS_ID,
       SocialOfficeTransaction.CREATION_DATE,
       SocialOfficeTransaction.CREATION_USER_NAME,
       SocialOfficeTransaction.SO_CREATION_NUMBER,
       SocialOfficeTransaction.CANCEL_DATE,
       SocialOfficeTransaction.CANCELLATION_USER_NAME,
       SocialOfficeTransaction.SO_CANCELLATION_NUMBER,
       Person.COMPANY_NR,
       Person.PERSON_ID,
       Person.FIRST_NAME,
       Person.LAST_NAME,
       SocialOfficeTransaction.CREATION_GUI_USER_ID,
       SocialOfficeTransaction.CANCELLATION_GUI_USER_ID,
       SocialOfficeTransaction.PERSON_ID AS PERSON_ID1,
       SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID AS SO_TRANSACTION_STATUS_ID1,
       SocialOfficeTransaction.WBS_ID AS WBS_ID1,
       Wbs.POI_ID,
       TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone),'Dy') AS VIEW_ATTR,
       TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone),'dd') AS VIEW_ATTR,
       OcPoi.NAME,
       OcPoi.POI_ID AS POI_ID1,
       Wbs.REF_NUMBER,
       TOUSERDATE (SocialOfficeTransaction.CREATION_DATE,  OPERATIONCENTER.timezone) AS VIEW_ATTR,
       TOUSERDATE (SocialOfficeTransaction.CANCEL_DATE,  OPERATIONCENTER.timezone) AS VIEW_ATTR,
       OperationCenter.TIMEZONE,
       OperationCenter.OPERATION_CENTER_ID,
       VoPersonCategory.NAME AS NAME1,
       VoPersonCategory.VO_PERSON_CATEGORY_ID,
       VoPersonCategory.GRAPHICAL_SYMBOL,
       P_PERSON.list_person_group_name(Person.PERSON_ID) AS VIEW_ATTR,
       decode (SocialOfficeTransaction.is_start_time_set, 'N' , '',  to_char (TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone), 'HH24:mi')) AS VIEW_ATTR,
       SocialOfficeTransaction.DURATION,
       decode (SocialOfficeTransaction.is_start_time_set, 'N', '', DECODE(SIGN(TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone) + SocialOfficeTransaction.duration /24 -  trunc (TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone)+ 1)), 1, '00:00', to_char  (TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone) + SocialOfficeTransaction.duration /24,  'HH24:mi'))) AS VIEW_ATTR,
       SocialOfficeTransaction.IS_START_TIME_SET,
       TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone) AS VIEW_ATTR,
       SocialOfficeTransaction.SO_COMMENT,
       SocialOfficeTransaction.PLAN_MODE,
       p_person.getCompanyName(Person.person_id) AS VIEW_ATTR,
       decode ((select v.poi_id from  v_gui_user_oc_poi v where v.gui_user_id =         GuPerson.gui_user_id and v.poi_id =  Wbs.poi_id), null, 'true', 'false') AS VIEW_ATTR,
       to_char(TOUSERDATE(SocialOfficeTransaction.work_date,OperationCenter.timezone),'dd/MM/yyyy') AS VIEW_ATTR,
       NVL(Str.STR_VALUE,AdminStatusCategory.NAME) AS VIEW_ATTR,
       Person.IS_ADMIN_STATUS_OK,
       SoTransactionStatus.DESCRIPTION,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 100, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 101, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 102, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 103, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 104, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 105, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 106, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       NVL(Str1.STR_VALUE,SoTransactionStatus.DESCRIPTION) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 107, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 108, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       P_PERSON.IsDocumentValid(Person.PERSON_ID, 109, SocialOfficeTransaction.WORK_DATE) AS VIEW_ATTR,
       Person.RANK1_SC_NAME,
       Person.RANK1_SC_TIN,
       p_person.isAdminProofCatValid(1, Person.PERSON_ID, trunc(TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone))) AS VIEW_ATTR,
       p_person.isAdminProofCatValid(2, Person.PERSON_ID, trunc(TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone))) AS VIEW_ATTR,
       p_person.isAdminProofCatValid(3, Person.PERSON_ID, trunc(TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone))) AS VIEW_ATTR,
       P_PERSON.getAdminProfileCategory(Person.ADMIN_PROFILE_ID) AS VIEW_ATTR,
       Person.IS_BLACKLISTED,
       (Person.ALL_ADMIN_ATTACH_DOC_VALIDATED) AS VIEW_ATTR,
       TO_CHAR(TOUSERDATE (SocialOfficeTransaction.work_date ,  OperationCenter.timezone),'MM') AS VIEW_ATTR
FROM OF_OWNER.SOCIAL_OFFICE_TRANSACTION SocialOfficeTransaction, OF_OWNER.SO_TRANSACTION_STATUS SoTransactionStatus, WBS Wbs, PERSON Person, OC_POI OcPoi, GUI_USERS GuiUsers, OPERATION_CENTER OperationCenter, GU_PERSON GuPerson, VO_PERSON_CATEGORY VoPersonCategory, OF_OWNER.ADMIN_STATUS_CATEGORY AdminStatusCategory, OF_OWNER.STR Str, OF_OWNER.STR Str1
WHERE SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID = SoTransactionStatus.SO_TRANSACTION_STATUS_ID
AND SoTransactionStatus.STR_ID = Str1.STR_ID(+)
AND GuiUsers.LANGUAGE = Str1.LANGUAGE(+)
AND (p_checkinatwork.getBestWbsIdToDisplay(SocialOfficeTransaction.social_office_transaction_id, guiusers.operation_center_id, NULL) = Wbs.WBS_ID (+))
AND (SocialOfficeTransaction.PERSON_ID = Person.PERSON_ID)
AND (Wbs.POI_ID = OcPoi.POI_ID(+))
and SocialOfficeTransaction.PERSON_ID = GuPerson.person_id
and GuPerson.gui_user_id = ?
and GuiUsers.gui_user_id = GuPerson.gui_user_id
and GuiUsers.Operation_Center_Id = OperationCenter.operation_center_id
and Person.vo_person_category_id = VoPersonCategory.vo_person_category_id
and Person.ADMIN_STATUS_CATEGORY_ID = AdminStatusCategory.ADMIN_STATUS_CATEGORY_ID(+)
and AdminStatusCategory.STR_ID = Str.STR_ID(+)
and GuiUsers.LANGUAGE = Str.LANGUAGE(+)
and SocialOfficeTransaction.work_date between toutcdate(?, OPERATIONCENTER.timezone) and  toutcdate(?, OPERATIONCENTER.timezone)
and ( NVL(GuPerson.CAW_LIMITED,'N') = 'N' or (GuPerson.caw_limited = 'Y' and  OcPoi.oc_poi_category_id in ( select oc_poi_category_id from oc_poi_category where operation_center_id =   guiusers.operation_center_id)))
and (? = -1 or SocialOfficeTransaction.PERSON_ID = ?)
and (? = -1 or Person.Vo_PERSON_CATEGORY_ID = ?)
and ((NVL(?,'N') = 'N' AND NVL(?,'N') = 'N') OR INSTR(DECODE(NVL(?,'N'),'N','','6') || DECODE(NVL(?,'N'),'N','','25'),SoTransactionStatus.SO_TRANSACTION_STATUS_ID) > 0)
and (NVL(?,'N') = 'N' or NVL(Person.IS_ADMIN_STATUS_OK,'N') = 'N')
and upper(NVL(Str.STR_VALUE,AdminStatusCategory.NAME) || ' ' ||
SocialOfficeTransaction.CANCELLATION_USER_NAME || ' ' ||
P_PERSON.getCompanyName(Person.PERSON_ID) || ' ' ||
Person.COMPANY_NR || ' ' ||
SocialOfficeTransaction.CREATION_USER_NAME || ' ' ||
NVL(Str1.STR_VALUE,SoTransactionStatus.DESCRIPTION) || ' ' ||
Person.FIRST_NAME || ' ' ||
Person.LAST_NAME || ' ' ||
Person.FIRST_NAME || ' ' ||
VoPersonCategory.NAME || ' ' ||
P_PERSON.list_person_group_name(Person.PERSON_ID) || ' ' ||
SocialOfficeTransaction.PLAN_MODE || ' ' ||
OcPoi.NAME || ' ' ||
SocialOfficeTransaction.POI_TIN || ' ' ||
Wbs.REF_NUMBER || ' ' ||
SocialOfficeTransaction.SO_COMMENT || ' ' ||
SoTransactionStatus.NAME) like '%' ||  upper(?) || '%'
ORDER BY SocialOfficeTransaction.WORK_DATE, Person.COMPANY_NR, Person.LAST_NAME, Person.FIRST_NAME, OcPoi.NAME