APPS.EDR_PSIG_PAGE_FLOW SQL Statements

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

Line: 29

      SELECT TRANSACTION_STATUS into x_transaction_status
        FROM EDR_TRANS_ACKN
       WHERE ERECORD_ID = eRecordId;

Line: 184

    SELECT event_name into l_event_name
    FROM EDR_ERECORDS
    WHERE event_id = p_event_id;

Line: 187

    select display_name into L_event_disp_name
    from wf_events_vl
    where name = l_event_name;

Line: 210

    SELECT count(*)  INTO l_count
    from EDR_ESIGNATURES
    WHERE EVENT_ID = P_event_id
      AND SIGNATURE_STATUS <> 'APPROVED';

Line: 218

      SELECT count(*)  INTO l_count
      from EDR_ESIGNATURES
      WHERE EVENT_ID = p_event_id
      AND SIGNATURE_STATUS = 'REJECTED';

Line: 226

         SELECT count(*)  INTO l_count
         from EDR_ESIGNATURES
         WHERE EVENT_ID = p_event_id
         AND SIGNATURE_STATUS = 'TIMEDOUT';

Line: 257

      select count(*) into l_approver_count
      from edr_esignatures
      where event_id = p_event_id
      and signature_status = 'APPROVED';

Line: 269

    SELECT count(*)  INTO l_count
    from EDR_ESIGNATURES
    WHERE EVENT_ID = P_event_id
    AND SIGNATURE_STATUS <> 'APPROVED';

Line: 277

      SELECT count(*)  INTO l_count
      from EDR_ESIGNATURES
      WHERE EVENT_ID = p_event_id
      AND SIGNATURE_STATUS = 'REJECTED';

Line: 286

         SELECT count(*)  INTO l_count
         from EDR_ESIGNATURES
         WHERE EVENT_ID = p_event_id
         AND SIGNATURE_STATUS = 'TIMEDOUT';

Line: 336

*****    Update record status to cancel assuming EVENT_ID and Workflow ITEM_KEY is Same  ***
********************************************************************************************/
  PROCEDURE CANCEL_RESPONSE(
     /* procedure to signature process response in case of Cancel   */
      p_itemtype   IN VARCHAR2,
      p_itemkey    IN VARCHAR2,
      p_actid      IN NUMBER,
      p_funcmode   IN VARCHAR2,
      p_resultout  OUT NOCOPY VARCHAR2
   )
   IS
     L_doc_id          NUMBER;

Line: 358

           /* Update workflow STATUS to Cancel */

           UPDATE EDR_ERECORDS
           SET ERECORD_SIGNATURE_STATUS = 'CANCEL'
           WHERE  EVENT_ID   =  P_itemkey;

Line: 429

     /* procedure to signature process Incase of all OFFLINE cases and ONLINE when user selects Done    */
      p_itemtype   IN VARCHAR2,
      p_itemkey    IN VARCHAR2,
      p_actid      IN NUMBER,
      p_funcmode   IN VARCHAR2,
      p_resultout  OUT NOCOPY VARCHAR2
   )
   IS
     L_CURR_MODE VARCHAR2(30);

Line: 457

       Select original_recipient from edr_psig_details
       where document_id = l_document_id and USER_RESPONSE is null;

Line: 502

         UPDATE EDR_ERECORDS
         SET ERECORD_SIGNATURE_STATUS = L_SIGNATURE_STATUS
         WHERE  EVENT_ID   =  l_event_id;

Line: 607

	   UPDATE EDR_PSIG_DETAILS
           SET USER_NAME=nvl(l_overriding_approver,l_username),
           SIGNATURE_OVERRIDING_COMMENTS = l_overriding_comments
	   where document_id = l_doc_id
	   and user_response is null
           and ORIGINAL_RECIPIENT = l_username;

Line: 616

           UPDATE EDR_ESIGNATURES
           SET USER_NAME = nvl(l_overriding_approver,l_username) ,
           SIGNATURE_OVERRIDING_COMMENTS = l_overriding_comments
           where event_id = l_event_id
           and ORIGINAL_RECIPIENT = l_username;

Line: 626

           UPDATE EDR_ERECORDS
           SET ERECORD_SIGNATURE_STATUS = 'FAILURE'
           WHERE  EVENT_ID   =  p_itemkey;

Line: 645

           UPDATE EDR_ERECORDS
           SET ERECORD_SIGNATURE_STATUS = 'PENDING'
           WHERE  EVENT_ID   =  p_itemkey;

Line: 681

   PROCEDURE UPDATE_NOTIF_RESPONSE(
      p_itemtype   IN VARCHAR2,
      p_itemkey    IN VARCHAR2,
      p_actid      IN NUMBER,
      p_funcmode   IN VARCHAR2,
      p_resultout  OUT NOCOPY VARCHAR2
   )
   IS
     l_SIGNER_TYPE     VARCHAR2(30);

Line: 724

       select WMA.NAME,WMA.DISPLAY_NAME, WL.meaning
       from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
         WF_MESSAGE_ATTRIBUTES_VL WMA , WF_LOOKUPS WL
       where WNA.NOTIFICATION_ID = wf_engine.context_nid
         and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
         and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
         and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
         and WNA.NAME = WMA.NAME
         and wma.subtype='RESPOND'
         and wma.format = wl.lookup_type
         and wna.text_value = wl.lookup_code
         and wma.type ='LOOKUP'
         and decode(wma.name,'RESULT','RESULT','NORESULT') = 'NORESULT'
      union
      select WMA.NAME,WMA.DISPLAY_NAME, decode(wma.type,'VARCHAR2',wna.text_value,'NUMBER',
           to_char(wna.number_value),'DATE',to_char(wna.date_value))
      from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
         WF_MESSAGE_ATTRIBUTES_VL WMA
      where WNA.NOTIFICATION_ID = wf_engine.context_nid
        and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
        and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
        and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
        and WNA.NAME = WMA.NAME
        and wma.subtype='RESPOND'
        and wma.type <>'LOOKUP'
        and decode(wma.name,'RESULT','RESULT','NORESULT') = 'NORESULT';

Line: 752

       select WL.LOOKUP_CODE,WMA.DISPLAY_NAME, WL.meaning
       from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
         WF_MESSAGE_ATTRIBUTES_VL WMA , WF_LOOKUPS WL
       where WNA.NOTIFICATION_ID = wf_engine.context_nid
         and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
         and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
         and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
         and WNA.NAME = WMA.NAME
         and wma.subtype='RESPOND'
         and wma.format = wl.lookup_type
         and wna.text_value = wl.lookup_code
         and wma.type ='LOOKUP'
         and decode(wma.name,'RESULT','RESULT','NORESULT') = 'RESULT';

Line: 772

     SELECT distinct USER_NAME from EDR_ESIGNATURES where to_char(event_id)=l_item_key;

Line: 788

     SELECT ORIGINAL_RECIPIENT from EDR_ESIGNATURES where
     signature_id=l_esign_id;

Line: 792

     SELECT  ORIGINAL_RECIPIENT, USER_NAME from EDR_ESIGNATURES where
     signature_id = l_esign_id;

Line: 829

       SELECT event_name, SIGNATURE_ID,NVL(ORIGINAL_RECIPIENT,USER_NAME) USER_NAME
       FROM EDR_ESIGNATURES
       WHERE EVENT_ID = lp_itemkey
         --Bug 4272262: Start
	 --Convert signature sequence to a number value.
         AND  to_number(SIGNATURE_SEQUENCE,'999999999999.999999') = L_CURR_SIGN_LEVEL
	 --Bug 4272262: End
         AND SIGNATURE_STATUS='PENDING';

Line: 884

            WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',
              p_itemtype,p_itemkey,
              FND_MESSAGE.GET_STRING('EDR','EDR_EREC_REASSIGN_ERR'));

Line: 898

        WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
                         'Signer Comment and Signing Reason code can not be null.');

Line: 914

			  	  	 WF_CORE.CONTEXT('EDR_PSIG_PAGE_FLOW', 'UPDATE_NOTIF_RESPONSE',p_itemtype, p_itemkey,
		 		 	 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));

Line: 934

                      SELECT MIN(to_number(SIGNATURE_SEQUENCE,'999999999999.999999')) INTO L_CURR_SIGN_LEVEL
                      from EDR_ESIGNATURES
                      WHERE EVENT_ID =lp_itemkey
                      AND SIGNATURE_STATUS = 'PENDING' ;

Line: 956

                            select notification_id into lp_notification_id
                            from wf_item_activity_statuses where item_type=p_itemtype
                            and item_key = lc_itemkey and notification_id is not null
                            and activity_status ='NOTIFIED';

Line: 965

                            WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,l_error_num,L_ERROR_MESG);

Line: 994

       /* SELECT count(*)  INTO l_count
        FROM EDR_ESIGNATURES
        WHERE EVENT_ID = l_item_key
        AND SIGNATURE_STATUS = 'REJECTED';

Line: 1026

        EDR_PSIG.updateDocument(P_PSIG_XML          => l_event_xml,
                              P_PSIG_DOCUMENT       => l_event_xml,
                              P_PSIG_DOCUMENTFORMAT => l_doc_format,
                              P_PSIG_REQUESTER      => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER'),
                              P_PSIG_SOURCE         => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SOURCE_APPLICATION_TYPE'),
                              P_EVENT_NAME          => l_event_name,
                              P_EVENT_KEY           => l_event_key,
                              P_WF_NID              => wf_engine.context_nid,
                              P_DOCUMENT_ID         => l_doc_id,
                              P_ERROR               => l_error_num,
                              P_ERROR_MSG           => L_ERROR_MESG);

Line: 1237

        WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
                         'Signer Comment and Signing Reason code can not be null.');

Line: 1241

        WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
                         l_error_num,L_ERROR_MESG);

Line: 1245

  	    WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
                         FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));

Line: 1249

        WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
                         l_error_num,L_ERROR_MESG);

Line: 1252

  END UPDATE_NOTIF_RESPONSE;

Line: 1296

      UPDATE EDR_ESIGNATURES
      SET SIGNATURE_STATUS    = 'APPROVED' ,
        SIGNATURE_TYPE        = l_SIGNER_TYPE,
        SIGNATURE_REASON_CODE = l_signer_reason,
        SIGNATURE_TIMESTAMP   = SYSDATE,
        SIGNER_COMMENTS       = l_signer_comment
      WHERE  SIGNATURE_ID = l_ESIGN_ID;

Line: 1306

      SELECT count(*)  INTO l_count
      from EDR_ESIGNATURES
      WHERE EVENT_ID = l_event_id
        AND SIGNATURE_STATUS <> 'APPROVED' ;

Line: 1319

        UPDATE EDR_ERECORDS
        SET ERECORD_SIGNATURE_STATUS = 'PRESUCCESS'
        WHERE  EVENT_ID              =  l_event_id;

Line: 1327

          update edr_esignatures
          set signature_status = null
          where event_id = l_event_id
          and signature_status = 'PENDING';

Line: 1400

      UPDATE EDR_ESIGNATURES
      SET SIGNATURE_STATUS    = 'REJECTED' ,
        SIGNATURE_TYPE        = l_SIGNER_TYPE,
        SIGNATURE_REASON_CODE = l_signer_reason,
        SIGNATURE_TIMESTAMP   = SYSDATE,
        SIGNER_COMMENTS       = l_signer_comment
      WHERE  SIGNATURE_ID = l_ESIGN_ID;

Line: 1411

      UPDATE EDR_ERECORDS
      SET ERECORD_SIGNATURE_STATUS = 'PREREJECTED'
      WHERE  EVENT_ID              =  l_event_id;

Line: 1432

  SELECT NOTIFICATION_ID
  FROM WF_ITEM_ACTIVITIES_HISTORY_V
  WHERE  ITEM_KEY = p_itemkey
      AND ITEM_TYPE = p_itemtype
      AND ACTIVITY_TYPE = 'NOTICE'
      AND NOTIFICATION_STATUS = 'OPEN'
    ORDER BY BEGIN_DATE DESC;

Line: 1441

  SELECT  NOTIFICATION_ID from  WF_ITEM_ACTIVITY_STATUSES_V
  WHERE  ITEM_KEY = p_itemkey
      AND ITEM_TYPE = p_itemtype
      AND   notification_id is not null
      ORDER BY activity_begin_date, execution_time;

Line: 1472

       SELECT event_name, SIGNATURE_ID,NVL(ORIGINAL_RECIPIENT,USER_NAME) USER_NAME
       FROM EDR_ESIGNATURES
       WHERE EVENT_ID = p_itemkey
         --Bug 4272262: Start
         --Convert signature sequence to a number value.
	 AND  to_number(SIGNATURE_SEQUENCE,'999999999999.999999') = L_CURR_SIGN_LEVEL
         AND SIGNATURE_STATUS = 'PENDING';

Line: 1503

           SELECT MIN( to_number(SIGNATURE_SEQUENCE,'999999999999.999999')) INTO L_CURR_SIGN_LEVEL
           from EDR_ESIGNATURES
           WHERE EVENT_ID = p_itemkey
             AND SIGNATURE_STATUS = 'PENDING' ;

Line: 1828

     /* UPDATE EDR_ERECORDS
      SET EVENT_TIMEZONE = fnd_timezones.GET_SERVER_TIMEZONE_CODE
      Replaced the  profile option to EDR_SERVER_ZONE
      SET EVENT_TIMEZONE = fnd_profile.VALUE('EDR_SERVER_TIMEZONE')
      WHERE event_id = p_itemkey;*/

Line: 1898

    SELECT distinct USER_NAME from EDR_ESIGNATURES where to_char(event_id)=l_item_key;

Line: 1959

               UPDATE EDR_ESIGNATURES
                 SET    SIGNATURE_STATUS    = 'TIMEDOUT'
                 WHERE  SIGNATURE_ID = l_ESIGN_ID;

Line: 1980

      select ITEM_KEY, ITEM_TYPE
      from WF_ITEM_ACTIVITIES_HISTORY_V
      where NOTIFICATION_ID = p_notif;

Line: 2012

  select ERECORD_SIGNATURE_STATUS into l_signature_status
  from EDR_ERECORDS
  WHERE  EVENT_ID =  l_event_id;

Line: 2055

  select user_id into l_requester_id from fnd_user where user_name = trim(l_requester);