APPS.PQH_WF SQL Statements

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

Line: 41

    INSERT INTO ghr_process_log (
       PROCESS_LOG_ID
     , PROGRAM_NAME
     , LOG_TEXT
     , LOG_DATE
     , LAST_UPDATE_DATE
     , LAST_UPDATED_BY
     , CREATION_DATE
     , CREATED_BY
     , LAST_UPDATE_LOGIN
       )
      VALUES (
        ghr_process_log_s.nextval
      , 'PQH_WORKFLOW'
      , p_log_text
      , sysdate
      , sysdate
      , 1
      , sysdate
      , 1
      , 1
      )
      ;

Line: 76

     select routing_history_id
     from pqh_routing_history
     where transaction_id = l_transaction_id
     and transaction_category_id = l_tran_cat_id
     and user_action_cd ='OVERRIDE'
     and nvl(approval_cd,'X') <> 'APPROVED'
     order by routing_history_id desc;

Line: 84

     select approval_cd
     from pqh_routing_history
     where transaction_id = l_transaction_id
     and routing_history_id > p_routing_history_id
     and transaction_category_id = l_tran_cat_id
     order by routing_history_id desc;

Line: 133

          SELECT 'x'
          FROM wf_items wfi
          WHERE wfi.item_type = p_itemtype
          AND   wfi.item_key  = p_itemkey;

Line: 168

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 220

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 271

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 322

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 373

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 424

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 475

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 604

  PROCEDURE update_routing_history(
      p_routing_history_id             IN NUMBER
    , p_user_action_cd                 IN VARCHAR2
  )
  IS
      l_proc            VARCHAR2(61)  := g_package  || 'update_routing_history';

Line: 612

     UPDATE pqh_routing_history
       set
          user_action_cd = p_user_action_cd
     WHERE routing_history_id = p_routing_history_id;

Line: 633

       SELECT '1-USR-TO'                  order_by
             , rht.routing_history_id     routing_history_id
             , user_name                  user_name
       FROM   pqh_routing_history rht
            , fnd_user usr
       WHERE  usr.user_id             = rht.forwarded_by_user_id
         AND  transaction_category_id = p_transaction_category_id
         AND  transaction_id          = p_transaction_id
         AND  user_action_cd         <> 'TIMEOUT'
       UNION
       SELECT '2-POS'
             , rht.routing_history_id
             , wfr.name
       FROM   pqh_routing_history rht
            , wf_roles wfr
       WHERE  wfr.orig_system         = 'POS'
         AND  wfr.orig_system_id      = rht.forwarded_by_position_id
         AND  rht.forwarded_to_user_id IS NULL
         AND  transaction_category_id = p_transaction_category_id
         AND  transaction_id          = p_transaction_id
         AND  user_action_cd         <> 'TIMEOUT'
       UNION
       SELECT '3-RLS'
             , rht.routing_history_id
             , wfr.name
       FROM   pqh_routing_history      rht
            , wf_roles                 wfr
            , pqh_routing_list_members rlm
       WHERE  wfr.orig_system            = 'PQH_ROLE'
         AND  rlm.routing_list_member_id = rht.forwarded_by_member_id
         AND  wfr.orig_system_id         = rlm.role_id
         AND  rht.forwarded_to_user_id IS NULL
         AND  transaction_category_id    = p_transaction_category_id
         AND  transaction_id             = p_transaction_id
         AND  user_action_cd         <> 'TIMEOUT'
       UNION
       SELECT '4-USR-BY'
             , rht.routing_history_id
             , user_name
       FROM   pqh_routing_history rht
            , fnd_user usr
       WHERE  usr.user_id             = rht.forwarded_by_user_id
         AND  rht.forwarded_to_user_id IS NULL
         AND  transaction_category_id = p_transaction_category_id
         AND  transaction_id          = p_transaction_id
         AND  user_action_cd         <> 'TIMEOUT'
       ORDER BY 2 DESC, 1 ASC;

Line: 772

       SELECT user_name
       FROM   pqh_routing_history rht
            , fnd_user usr
       WHERE  usr.user_id = rht.forwarded_by_user_id
         AND  transaction_category_id = p_transaction_category_id
         AND  transaction_id          = p_transaction_id
         AND  approval_cd             = 'APPROVED'
       ORDER BY routing_history_id DESC;

Line: 829

       SELECT user_name
            , forwarded_by_assignment_id
            , forwarded_by_member_id
            , forwarded_by_position_id
            , forwarded_by_user_id
            , forwarded_by_role_id
       FROM   pqh_routing_history rht
            , fnd_user usr
       WHERE  usr.user_id = rht.forwarded_by_user_id
       AND    routing_history_id = (
              SELECT MIN(routing_history_id)
              FROM pqh_routing_history
              WHERE  transaction_category_id = p_transaction_category_id
              AND    transaction_id = p_transaction_id
              );

Line: 940

          SELECT name
               , custom_workflow_name
               , custom_wf_process_name
               , timeout_days
               , form_name
               , post_txn_function
               , post_style_cd
               , future_action_cd
               , short_name
          FROM   pqh_transaction_categories tct
          WHERE  transaction_category_id = p_transaction_category_id;

Line: 1460

      SELECT A.NAME activity_name,
             wf_directory.getroledisplayname(IAS.ASSIGNED_USER) role_name ,IAS.ASSIGNED_USER owner
      from WF_ACTIVITIES_VL A, WF_PROCESS_ACTIVITIES PA,
           WF_ITEM_TYPES_VL IT, WF_ITEMS I, WF_ITEM_ACTIVITY_STATUSES IAS
      WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
      and IAS.ITEM_KEY = I.ITEM_KEY
      and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
      and I.ITEM_TYPE = IT.NAME
      and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
      and PA.ACTIVITY_NAME = A.NAME
      and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
      and I.item_type ='PQHGEN'
      and PA.activity_name like 'NTF_%'
      and IAS.activity_status ='NOTIFIED'
      and I.item_key = p_itemkey;

Line: 2039

                SELECT user_action_cd
                FROM   pqh_routing_history
                WHERE  transaction_id          = p_transaction_id
                  AND  transaction_category_id = p_transaction_category_id
                ORDER BY routing_history_id desc;

Line: 2063

       UPDATE pqh_fyi_notify
          SET status            = p_status
            , notification_date = sysdate
       WHERE fyi_notified_id = p_fyi_notified_id;

Line: 2080

                 SELECT fyi_notified_id
                      , notified_name
                      , notification_event_cd
                      , notified_type_cd
                 FROM   pqh_fyi_notify
                 WHERE  transaction_id          = p_transaction_id
                   AND  transaction_category_id = p_transaction_category_id
                   AND  STATUS IS NULL;

Line: 2207

        select short_name into l_short_name
        from pqh_transaction_categories
        where transaction_category_id = l_transaction_category_id;

Line: 2397

      l_dbupdate                VARCHAR2(30) := 'FAILURE';

Line: 2438

          EXECUTE IMMEDIATE l_post_txn_function USING OUT l_dbupdate;

Line: 2455

      IF l_dbupdate = 'SUCCESS' THEN
          wf_engine.SetItemAttrText(
               itemtype => itemtype,
               itemkey  => itemkey,
               aname    => 'TRANSACTION_STATUS',
               avalue   => 'DBSUCCESS');

Line: 2462

      ELSIF l_dbupdate = 'FAILURE' THEN
          wf_engine.SetItemAttrText(
               itemtype => itemtype,
               itemkey  => itemkey,
               aname    => 'TRANSACTION_STATUS',
               avalue   => 'DBERROR');

Line: 2528

           update_routing_history(p_routing_history_id => l_routing_history_id
                                , p_user_action_cd     => 'TIMEOUT');

Line: 2676

             l_dbupdate  varchar2(2000);

Line: 2678

             EXECUTE IMMEDIATE l_post_txn_function USING OUT l_dbupdate;

Line: 2679

             result := 'COMPLETE:'||l_dbupdate;

Line: 2714

       SELECT member_cd, workflow_enable_flag
       FROM   pqh_transaction_categories tct
       WHERE  transaction_category_id = p_transaction_category_id;

Line: 2719

       SELECT role_id
         FROM pqh_role_users_v
        WHERE user_id = p_user_id
          AND nvl(default_role,'X') = 'Y';

Line: 2725

         SELECT asg.assignment_id,asg.position_id
         FROM per_all_assignments asg
            , fnd_user fu
         WHERE asg.person_id = fu.employee_id
           AND fu.user_id = p_user_id
           AND asg.primary_flag = 'Y'
	   AND asg.assignment_type = 'E';         --  added for bug 7708168

Line: 2734

         SELECT rls.role_id
         FROM per_all_assignments asg
            , pqh_position_roles_v rls
         WHERE asg.position_id = rls.position_id;

Line: 2859

     cursor c1 is select post_txn_function,short_name
                  from pqh_transaction_categories
                  where transaction_category_id = l_tran_cat_id ;

Line: 2940

   cursor c1 is select full_name
                from per_all_people_f per, fnd_user usr
                where per.person_id = usr.employee_id
                and usr.user_id = p_user_id;

Line: 2944

   cursor c2 is select full_name
                from per_all_assignments_f asg, per_all_people_f per
                where asg.person_id = per.person_id
                and sysdate between asg.effective_start_date and asg.effective_end_date
                and sysdate between per.effective_start_date and per.effective_end_date
                and asg.assignment_id = p_assignment_id ;