Clarity

Hello ,

We run the below process to shut off the notifications for all users. The process is finishing successfully but in the UI under personal setting the notifications are still selected. Shouldn't they be unchecked after the query is run.

Appreciate all help.

<gel:script xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"

    xmlns:core="jelly:core"

    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"

    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"

    xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <gel:setDataSource dbId="Niku"/>

    <!-- SQL UPDATE TO TURN OFF NOTIFICATIONS -->

    <core:catch var="sqlerr">

        <sql:update var="result"><![CDATA[

            INSERT INTO CLB_NOTIFICATION_PREFS (USER_ID, NOTIFICATION_TYPE, METHOD, CREATED_BY, CREATED_DATE, LAST_UPDATED_BY, LAST_UPDATED_DATE)

            select usr.id "USER_ID", NTF.NOTIFICATION_TYPE "NOTIFICATION_TYPE", MTH.METHOD "METHOD",

                   1 "CREATED_BY", sysdate "CREATED_DATE",

                   1 "LAST_UPDATED_BY", sysdate "LAST_UPDATED_DATE"

              from cmn_sec_users usr

                cross join ( select METHOD

                               from (select 2 B, 3 C from dual)

                            UNPIVOT (X for METHOD in (B AS 'EMAIL', C AS 'SMS'))

                              where METHOD in ('EMAIL', 'SMS')) MTH

                cross join ( select NOTIFICATION_TYPE

                               from (select 1 A, 2 B, 3 C, 4 D, 5 E, 6 F, 7 G, 8 H, 9 I, 10 J, 11 K, 12 L, 13 M, 14 N from dual)

                            UNPIVOT (X for NOTIFICATION_TYPE in (A AS 'NOTIFICATION_CHANGES',

                                                                 B AS 'NOTIFICATION_DISCUSSION_TYPE',

                                                                 C AS 'NOTIFICATION_DOCUMENT_TYPE',

                                                                 D AS 'NOTIFICATION_ESCALATION',

                                                                 E AS 'NOTIFICATION_FIN_MANAGEMENT',

                                                                 F AS 'NOTIFICATION_INCIDENTS',

                                                                 G AS 'NOTIFICATION_ISSUES',

                                                                 H AS 'NOTIFICATION_PROJ_PARTICIPANTS',

                                                                 I AS 'NOTIFICATION_REQUISITIONS',

                                                                 J AS 'NOTIFICATION_RISKS',

                                                                 K AS 'NOTIFICATION_TIMESHEETS_TYPE',

                                                                 L AS 'NOTIFICATION_TO_DO_TYPE',

                                                                 M AS 'NOTIFICATION_WORKFLOW',

                                                                 N AS 'SCHEDULER_TYPE'))) NTF

             --where usr.user_name = 'admin'

             order by usr.id, NTF.NOTIFICATION_TYPE, MTH.METHOD

            ]]>

        </sql:update>

    </core:catch>

    <!-- ERROR LOG IF DEBUG IS TURNED ON -->

    <core:if test="${v_DEBUG == 'Y'}">

        <core:choose>

            <core:when test="${sqlerr != null}">

                 <gel:log level="error">Error with the SQL statement, notifications not deleted. sqlerr = ${sqlerr}</gel:log>

            </core:when>

            <core:otherwise>

                 <gel:log level="info">SQL update successful.</gel:log>

            </core:otherwise>

        </core:choose>

    </core:if>

</gel:script>

Obviously direct SQL inserts are unsupported (but I recognise that this is a common requirement and the solution is "well-known" ).

You don't trap any SQL error that may be returned from your insert statement* (i.e. what would be in your 'sqlerr' variable) - so I suspect that the SQL is not doing exactly what you expect of it - is the right data you expect present on the database table after your process run?

What I would be concerned about is that your SQL does not check for the existence of the relevant CLB_NOTIFICATION_PREF record before you try to insert it, so perhaps it is failing because of a unique constraint violation (you could add a "AND NOT EXISTS...." clause to the SQL to fix that).

* - the code that would trap any sql error only gets run if v_DEBUG is set ; perhaps just removing that trap and allowing the error or success message to be reported to the process log would also be a good thing.

Regarding supported solutions, 14.3 now lets you (via Administration > Notifications and the Notification Default Settings tab) toggle these settings with a 'publish' that can be used to push the changes down to all users, which would successfully turn them on/off as needed.

You guys should have called it 15.1, because it really is a great release, deserving its own version.

Thank you. Added the err to trap still it is not showing the error. it is finishing completely with no error but not updating the UI

Try changing the query so that the CREATED_BY and LAST_UPDATED_BY are the CMN_SEC_USERS.ID value. You should probably delete all the screwed up records first.

NSERT INTO CLB_NOTIFICATION_PREFS (USER_ID, NOTIFICATION_TYPE, METHOD, CREATED_BY, CREATED_DATE, LAST_UPDATED_BY, LAST_UPDATED_DATE)
            select usr.id "USER_ID", NTF.NOTIFICATION_TYPE "NOTIFICATION_TYPE", MTH.METHOD "METHOD",
                  usr.id "CREATED_BY", sysdate "CREATED_DATE",
                  usr.id "LAST_UPDATED_BY", sysdate "LAST_UPDATED_DATE"
              from cmn_sec_users usr
                cross join ( select METHOD
                              from (select 2 B, 3 C from dual)
                            UNPIVOT (X for METHOD in (B AS 'EMAIL', C AS 'SMS'))
                              where METHOD in ('EMAIL', 'SMS')) MTH
                cross join ( select NOTIFICATION_TYPE
                              from (select 1 A, 2 B, 3 C, 4 D, 5 E, 6 F, 7 G, 8 H, 9 I, 10 J, 11 K, 12 L, 13 M, 14 N from dual)
                            UNPIVOT (X for NOTIFICATION_TYPE in (A AS 'NOTIFICATION_CHANGES',
                                                                B AS 'NOTIFICATION_DISCUSSION_TYPE',
                                                                C AS 'NOTIFICATION_DOCUMENT_TYPE',
                                                                D AS 'NOTIFICATION_ESCALATION',
                                                                E AS 'NOTIFICATION_FIN_MANAGEMENT',
                                                                F AS 'NOTIFICATION_INCIDENTS',
                                                                G AS 'NOTIFICATION_ISSUES',
                                                                H AS 'NOTIFICATION_PROJ_PARTICIPANTS',
                                                                I AS 'NOTIFICATION_REQUISITIONS',
                                                                J AS 'NOTIFICATION_RISKS',
                                                                K AS 'NOTIFICATION_TIMESHEETS_TYPE',
                                                                L AS 'NOTIFICATION_TO_DO_TYPE',
                                                                M AS 'NOTIFICATION_WORKFLOW',
                                                                N AS 'SCHEDULER_TYPE'))) NTF
            --where usr.user_name = 'admin'
            order by usr.id, NTF.NOTIFICATION_TYPE, MTH.METHOD

Thank you . Could you help in the query where to update? I have inherited this query and trying to fix that is broken

I already did in my first reply. :-)

But you should also run the following to clean up the invalid records.

DELETE
FROM          CLB_NOTIFICATION_PREFS
WHERE      USER_ID <> 1 AND 
                    CREATED_BY = 1;      

Apologies. I did add to the query and since nothing changed was not sure.

I will run the clean up and then try to run again the updated query.

Thank you,

You still need to cater for attempted duplicate insertions - otherwise, even if your process runs OK once, it will fail the next time that it runs.

(The unique index being on USER_ID, NOTIFICATION_TYPE, METHOD)