--3
--fromDate -> Fri Jun 23 00:00:00 CEST 2017
--toDate -> Mon Jun 26 23:59:00 CEST 2017
--language -> FR
--poiName -> 577147
--personCategory -> -1
--personName -> -1
--voGroup : [-1]
--TIN -> -1
--status -> -1
--showAllPpl -> N
--gu_user_id -> 102298

SELECT DISTINCT        (Person.PERSON_ID) p_id,
                        TOUSERDATE(SocialOfficeTransaction.work_date, OperationCenter.timezone) WORK_DATE,
                        SocialOfficeTransaction.SOCIAL_OFFICE_TRANSACTION_ID,
                        SocialOfficeTransaction.POI_TIN,
                        SocialOfficeTransaction.WBS_ID,
                        P_PERSON.getCawCategoryName(Person.PERSON_ID) AS person_category,
                        SocialOfficeTransaction.WORK_DATE,
                        SocialOfficeTransaction.WBS_NAME AS WBS_NAME,
                        SocialOfficeTransaction.SO_TRANSACTION_STATUS_NAME AS SO_TRANSACTION_STATUS_NAME,
                        SocialOfficeTransaction.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,
                        SocialOfficeTransaction.SO_COMMENT,
                        Person.COMPANY_NR,
                        SocialOfficeTransaction.plan_mode,
                        Person.FIRST_NAME,
                        Person.LAST_NAME,
                        SocialOfficeTransaction.CREATION_GUI_USER_ID,
                        SocialOfficeTransaction.CANCELLATION_GUI_USER_ID,
                        SocialOfficeTransaction.PERSON_DATA_ID,
                        SocialOfficeTransaction.PERSON_ID AS PERSON_ID1,
                        SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID AS SO_TRANSACTION_STATUS_ID1,
                        SocialOfficeTransaction.WBS_ID AS WBS_ID1,
                        SocialOfficeTransaction.POI_ID,
                        TO_CHAR(SocialOfficeTransaction.WORK_DATE, 'Dy') AS VIEW_ATTR,
                        TO_CHAR(SocialOfficeTransaction.WORK_DATE, 'dd') AS VIEW_ATTR,
                        SocialOfficeTransaction.NAME,
                        TOUSERDATE(SocialOfficeTransaction.work_date,  OperationCenter.timezone) AS VIEW_ATTR2,
                        SocialOfficeTransaction.REF_NUMBER,
                        TOUSERDATE(SocialOfficeTransaction.CREATION_DATE,                   OPERATIONCENTER.timezone) AS VIEW_ATTR3,
                        TOUSERDATE(SocialOfficeTransaction.CANCEL_DATE,                     OPERATIONCENTER.timezone) AS VIEW_ATTR4
          FROM
               (SELECT SocialOfficeTransaction.*, OcPoi.NAME NAME, Wbs.REF_NUMBER, Wbs.NAME AS WBS_NAME, Wbs.POI_TIN WBS_POI_TIN, Wbs.POI_ID, SoTransactionStatus.NAME AS SO_TRANSACTION_STATUS_NAME
               FROM SOCIAL_OFFICE_TRANSACTION SocialOfficeTransaction, GUI_USERS GuiUsers, OPERATION_CENTER OperationCenter,
               SO_TRANSACTION_STATUS     SoTransactionStatus,
               WBS                                Wbs,
               OC_POI                             OcPoi,
               PERSON                             Person,
               GU_PERSON                          GuPerson
               WHERE GuiUsers.gui_user_id = 102298
               AND SocialOfficeTransaction.PERSON_ID = Person.PERSON_ID
               AND Person.PERSON_ID = GuPerson.person_id
               AND GuPerson.gui_user_id =  GuiUsers.gui_user_id
               AND SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID =  SoTransactionStatus.SO_TRANSACTION_STATUS_ID
               AND SocialOfficeTransaction.WBS_ID  = Wbs.WBS_ID
               AND GuiUsers.Operation_Center_Id = OperationCenter.operation_center_id
               AND Wbs.POI_ID = OcPoi.POI_ID
               AND ((OcPoi.OC_POI_STATUS_ID = 0 AND SocialOfficeTransaction.so_transaction_status_id = 7) OR (SocialOfficeTransaction.so_transaction_status_id <> 7))
               AND nvl (TOUSERDATE(SocialOfficeTransaction.work_date,  OperationCenter.timezone),  toutcdate( to_date ('23/06/2017 00:00','dd/MM/yyyy HH24:mi') , OperationCenter.timezone )) >= toutcdate( to_date ('23/06/2017 00:00','dd/MM/yyyy HH24:mi') , OperationCenter.timezone )
               AND nvl(TOUSERDATE(SocialOfficeTransaction.work_date, OperationCenter.timezone), toutcdate( to_date ('26/06/2017 23:59','dd/MM/yyyy HH24:mi') , OperationCenter.timezone )) <toutcdate( to_date ('26/06/2017 23:59','dd/MM/yyyy HH24:mi') , OperationCenter.timezone ) +1
               AND nvl( SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID, 2) =  nvl( decode(-1, -1, SocialOfficeTransaction.SO_TRANSACTION_STATUS_ID,  -1), 2)
               AND nvl(Wbs.poi_id, -2)  =   nvl (decode(577147, -1,  Wbs.poi_id, 577147), -2)
               AND nvl(  SocialOfficeTransaction.POI_TIN, -2) = nvl( decode('-1', '-1',   SocialOfficeTransaction.POI_TIN, '-1') , -2)
               ) SocialOfficeTransaction,
               PERSON                             Person,
               GUI_USERS                          GuiUsers,
               OPERATION_CENTER                   OperationCenter,
               GU_PERSON                          GuPerson,
               vo_group_person                    vgp
         WHERE SocialOfficeTransaction.PERSON_ID (+) = Person.PERSON_ID
           AND ( 'N'= 'Y'  OR SocialOfficeTransaction.person_id IS NOT NULL)
           AND Person.PERSON_ID = GuPerson.person_id
           AND GuPerson.gui_user_id =  102298
           AND GuiUsers.gui_user_id = GuPerson.gui_user_id
           AND GuiUsers.Operation_Center_Id = OperationCenter.operation_center_id
           AND Person.person_id = vgp.person_id(+)
           AND Person.person_status_id = 0
           AND Person.PERSON_ID = decode(-1, -1, Person.PERSON_ID, -1)
           AND ((-1 IN -1) OR  (Person.PERSON_ID IN (SELECT person_id FROM vo_group_person WHERE vo_group_id IN -1)))
           AND Person.vo_person_category_id =       decode(-1,  -1,   Person.vo_person_category_id,  -1)
           AND UPPER(gu.LANGUAGE) LIKE 'FR'
         ORDER BY P_PERSON.getCawCategoryName(Person.PERSON_ID),
                  SocialOfficeTransaction.WORK_DATE,
                  Person.LAST_NAME,
                  Person.FIRST_NAME,
                  Person.COMPANY_NR,
                  NAME;