APPS.GME_ERES_PKG SQL Statements

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

Line: 5

    *** Following procedure is used to insert ERES event in GME_ERES_GTMP table for ERES
    *** processing from form
    ****************************************************************************************/

   PROCEDURE INSERT_EVENT(P_EVENT_NAME VARCHAR2,
                          P_EVENT_KEY VARCHAR2,
                          P_USER_KEY_LABEL VARCHAR2,
                          P_USER_KEY_VALUE VARCHAR2,
                          P_POST_OP_API VARCHAR2,
                          P_PARENT_EVENT VARCHAR2,
                          P_PARENT_EVENT_KEY VARCHAR2,
                          P_PARENT_ERECORD_ID NUMBER,
                          X_STATUS OUT NOCOPY VARCHAR2) IS
    CURSOR check_event_already_exisits IS
      select count(*)
      from GME_ERES_GTMP
      where event_name = p_event_name
        and event_key  = p_event_key;

Line: 36

                              ,gme_common_pvt.G_RESOURCE_UPDATE)
          THEN
            select count(*) into l_count
            from GME_ERES_GTMP
            where (event_name = gme_common_pvt.G_BATCHSTEP_ADDED
                   and event_key = substrb(P_EVENT_KEY,1,instrb(P_EVENT_KEY,'-',1,2)-1)) OR
                   (event_name = gme_common_pvt.G_ACTIVITY_ADDED
                      and event_key = substrb(P_EVENT_KEY,1,instrb(P_EVENT_KEY,'-',1,3)-1));

Line: 45

              select count(*) into l_count
              from GME_ERES_GTMP
              where event_name in (gme_common_pvt.G_RESOURCE_ADDED )
                and event_key =   p_event_key;

Line: 52

                  delete GME_ERES_GTMP
                  where  event_name in (gme_common_pvt.G_RESOURCE_ADDED )
                  and event_key =   p_event_key;

Line: 60

                  delete GME_ERES_GTMP
                  where  event_name in (gme_common_pvt.G_RESOURCE_UPDATE)
                  and event_key =   p_event_key;

Line: 71

                            ,gme_common_pvt.G_ACTIVITY_UPDATED)
          THEN
            select count(*) into l_count
            from GME_ERES_GTMP
            where (event_name = gme_common_pvt.G_BATCHSTEP_ADDED
                   and event_key = substrb(P_EVENT_KEY,1,instrb(P_EVENT_KEY,'-',1,2)-1));

Line: 78

              select count(*) into l_count
              from GME_ERES_GTMP
              where event_name in (gme_common_pvt.G_ACTIVITY_ADDED )
                and event_key =   p_event_key;

Line: 85

                  delete GME_ERES_GTMP
                  where  event_name in (gme_common_pvt.G_ACTIVITY_ADDED )
                  and event_key =   p_event_key;

Line: 93

                  delete GME_ERES_GTMP
                  where  (event_name in (gme_common_pvt.G_ACTIVITY_UPDATED)
                  and event_key =   p_event_key) OR
                       (event_name in (gme_common_pvt.G_RESOURCE_ADDED
                            ,gme_common_pvt.G_RESOURCE_REMOVED
                            ,gme_common_pvt.G_RESOURCE_UPDATE)
                          and substrb(EVENT_KEY,1,instrb(EVENT_KEY,'-',1,3)-1) = p_event_key);

Line: 108

                                 gme_common_pvt.G_BATCHSTEP_UPDATE)
          THEN
            select count(*) into l_count
            from GME_ERES_GTMP
            where event_name = gme_common_pvt.G_BATCHSTEP_ADDED
              and event_key =   p_event_key;

Line: 117

                  delete GME_ERES_GTMP
                  where  event_name in (gme_common_pvt.G_BATCHSTEP_ADDED )
                  and event_key =   p_event_key;

Line: 125

                  delete GME_ERES_GTMP
                  where  (event_name in (gme_common_pvt.G_BATCHSTEP_UPDATE)
                  and event_key =   p_event_key) OR
                       (event_name in (gme_common_pvt.G_RESOURCE_ADDED
                            ,gme_common_pvt.G_RESOURCE_REMOVED
                            ,gme_common_pvt.G_RESOURCE_UPDATE
                            ,gme_common_pvt.G_ACTIVITY_ADDED
                            ,gme_common_pvt.G_ACTIVITY_REMOVED
                            ,gme_common_pvt.G_ACTIVITY_UPDATED)
                          and substrb(EVENT_KEY,1,instrb(EVENT_KEY,'-',1,2)-1) = p_event_key);

Line: 141

                                 ,gme_common_pvt.G_BATCHMTL_UPDATED)
          THEN

            select count(*) into l_count
            from GME_ERES_GTMP
            where event_name = gme_common_pvt.G_BATCHMTL_ADDED
              and event_key =   p_event_key;

Line: 151

                  delete GME_ERES_GTMP
                  where  event_name in (gme_common_pvt.G_BATCHMTL_ADDED)
                  and event_key =   p_event_key;

Line: 159

                  delete GME_ERES_GTMP
                  where  (event_name in (gme_common_pvt.G_BATCHMTL_UPDATED)
                  and event_key =   p_event_key);

Line: 179

         INSERT INTO GME_ERES_GTMP ( Event_name
                                    ,Event_key
                                    ,Task
                                    ,Action_code
                                    ,User_KEY_LABEL
                                    ,USER_KEY_VALUE
                                    ,POST_OP_API
                                    ,PARENT_EVENT
                                    ,PARENT_EVENT_KEY
                                    ,PARENT_ERECORD_ID
                                    ,XML_GENERATION_API )
                  VALUES  (P_EVENT_NAME,
                           P_EVENT_KEY,
                           null,
                           null,
                           P_USER_KEY_LABEL,
                           P_USER_KEY_VALUE,
                           P_POST_OP_API,
                           P_PARENT_EVENT,
                           P_PARENT_EVENT_KEY,
                           P_PARENT_ERECORD_ID,
                           l_XML_GEN_API);

Line: 203

   END INSERT_EVENT;

Line: 212

     SELECT CONCATENATED_SEGMENTS
     FROM MTL_SYSTEM_ITEMS_B_KFV
     WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
       AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;

Line: 231

     SELECT OPRN_NO
     FROM GMD_OPERATIONS_B
     WHERE OPRN_ID = P_OPRN_ID;

Line: 250

       SELECT DISTINCT b.STATUS,
              EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_XML_MAP_CODE',b.GUID) map_code
       FROM wf_events_vl a,
            wf_event_subscriptions b
       WHERE a.guid=b.EVENT_FILTER_GUID
         -- Namit S. Bug#4917171 Added the following 2 clauses.
         -- Table wf_event_subscriptions has index on EVENT_FILTER_GUID, source_type, system_guid.
         -- Adding the 2 clauses removes Full Table Scan of wf_event_subscriptions.
         AND b.source_type = 'LOCAL'
         AND b.system_guid = HEXTORAW(wf_core.TRANSLATE('WF_SYSTEM_GUID'))
         AND b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
         AND a.name = p_event_name
         AND b.status = 'ENABLED'
       ORDER BY b.STATUS DESC;

Line: 265

    SELECT COUNT(*) INTO l_cnt
    FROM   wf_events_vl a,
           wf_event_subscriptions b
    WHERE a.guid=b.EVENT_FILTER_GUID
      -- Namit S. Bug#4917171 Added the following 2 clauses.
      AND b.source_type = 'LOCAL'
      AND b.system_guid = HEXTORAW(wf_core.TRANSLATE('WF_SYSTEM_GUID'))
      AND b.RULE_FUNCTION ='EDR_PSIG_RULE.PSIG_RULE'
      AND b.status = 'ENABLED'
      AND a.name = p_event_name;