APPS.JTF_FM_REQUEST_GRP SQL Statements

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

Line: 78

    SELECT DECODE(p_request_type,'M', MASS_REQUEST_Q, 'B' , BATCH_REQUEST_Q, 'MP', MASS_PAUSE_Q, BATCH_PAUSE_Q)
	INTO l_queue_name
	FROM JTF_FM_SERVICE_ALL
	WHERE SERVer_ID = p_server_id;

Line: 109

    SELECT FILE_NAME into l_file_name from fnd_lobs where file_id = p_file_id and LANGUAGE = USERENV('LANG') ;

Line: 171

  Procedure to update JTF_FM_REQUESTS_AQ table.
*/

PROCEDURE UPDATE_RESUBMITTED
(
   p_parent_req_id IN NUMBER,
   p_job     IN NUMBER,
   p_request_id     IN VARCHAR2
)
IS

l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESUBMITTED';

Line: 188

     INSERT INTO JTF_FM_RESUBMITTED (
     PARENT_REQ_ID, JOB_ID, REQUEST_ID,
     CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
     LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
     VALUES (p_parent_req_id ,p_job ,p_request_id ,
     FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
     SYSDATE,  FND_GLOBAL.LOGIN_ID);

Line: 196

   JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name,  JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,'JTF_FM_REQUEST_GRP.UPDATE_REQUESTS_AQ');

Line: 198

END UPDATE_RESUBMITTED;

Line: 219

    SELECT decode(count(1),0,'N', 'Y','Y')
    FROM JTF_FM_INT_REQUEST_HEADER
    WHERE  request_id  = p_request_id;

Line: 370

  update JTF_FM_INT_REQUEST_HEADER
   set request_status = 'CANCELLED'
  where REQUEST_ID  = p_request_id ;

Line: 373

   JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name,  JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,'JTF_FM_REQUEST_GRP.UPDATE_REQUESTS_AQ');

Line: 477

   JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name,  JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,'JTF_FM_REQUEST_GRP.UPDATE_REQUESTS_AQ');

Line: 486

PROCEDURE INSERT_QUERY
(
   p_query_id    IN NUMBER,
   x_query_file_id   OUT NOCOPY NUMBER
)
IS
cursor query_c is
  select query_name, query_id, query_string
  from jtf_fm_queries_all
  where query_id = p_query_id;

Line: 511

  l_api_name CONSTANT VARCHAR2(30) := 'INSERT_QUERY';

Line: 534

          select fnd_lobs_s.nextval into x_query_file_id from dual;

Line: 542

          INSERT INTO fnd_lobs (
            FILE_ID,
            FILE_NAME,
            FILE_CONTENT_TYPE,
            FILE_DATA,
            UPLOAD_DATE,
            FILE_FORMAT
          )
          VALUES
          (
            x_query_file_id,
            l_query_name,
            l_file_content_type,
            empty_blob(),
            l_upload_date,
            l_file_format
          );

Line: 571

          select file_data into l_file_data
          from fnd_lobs
          where file_id = x_query_file_id
          for update;

Line: 583

           update jtf_fm_queries_all set file_id = x_query_file_id
           where query_id = p_query_id;

Line: 588

		   JTF_FM_UTL_V.PRINT_MESSAGE('UPDATED Query Tables with FILEID  '|| x_query_file_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,l_full_name);

Line: 601

END INSERT_QUERY;

Line: 608

PROCEDURE CHECK_AND_INSERT_QUERY
(
   p_query_id    IN NUMBER,
   p_query_file_id   IN NUMBER,
   x_query_file_id   OUT NOCOPY NUMBER
)
IS
cursor query_c is
  select query_name, query_id, query_string
  from jtf_fm_queries_all
  where query_id = p_query_id;

Line: 621

  l_api_name CONSTANT VARCHAR2(30) := 'CHECK_AND_INSERT_QUERY';

Line: 628

      Select file_id into x_query_file_id from fnd_lobs where file_id = p_query_file_id;

Line: 635

	   INSERT_QUERY(p_query_id, x_query_file_id);

Line: 645

END CHECK_AND_INSERT_QUERY;

Line: 648

  Procedure to INSERT JTF_FM_REQUEST_CONTENTS table.
*/

PROCEDURE INSERT_REQ_CONTENTS
(
   p_request_id     IN NUMBER,
   x_request_id     IN NUMBER
)
IS

l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REQ_CONTENTS';

Line: 675

    SELECT  CONTENT_ID, CONTENT_NUMBER,
   CONTENT_NAME, CONTENT_TYPE, DOCUMENT_TYPE,
   BODY, USER_NOTES, QUANTITY,MEDIA_TYPE, CONTENT_SOURCE, FND_FILE_ID
   FROM JTF_FM_REQUEST_CONTENTS
   where request_id = p_request_id;

Line: 696

      INSERT INTO JTF_FM_REQUEST_CONTENTS (
         REQUEST_ID, CONTENT_ID, CONTENT_NUMBER,
         CONTENT_NAME, CONTENT_TYPE, DOCUMENT_TYPE,
         BODY, USER_NOTES, QUANTITY,
          CREATED_BY, CREATION_DATE,
         LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
         MEDIA_TYPE, CONTENT_SOURCE, FND_FILE_ID)
      VALUES ( x_request_id, l_content_id,l_content_number ,
          l_content_name,l_content_type ,l_document_type ,
          l_body,l_user_notes , l_quantity,
           FND_GLOBAL.USER_ID,SYSDATE,
		   FND_GLOBAL.USER_ID,SYSDATE,  FND_GLOBAL.LOGIN_ID,
          l_media_type,l_content_source ,l_file_id );

Line: 718

END INSERT_REQ_CONTENTS;

Line: 721

  Procedure to update JTF_FM_REQUESTS_AQ table.
*/

PROCEDURE UPDATE_REQUESTS_AQ
(
   p_new_msg_handle IN RAW,
   p_request_id     IN NUMBER,
   p_queue_type     IN VARCHAR2,
   p_old_msg_handle In RAW
)
IS

l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REQUESTS_AQ';

Line: 739

   	    --UPDATE JTF_FM_REQUESTS_AQ with the new information
			   UPDATE JTF_FM_REQUESTS_AQ
			   SET QUEUE_TYPE = p_queue_type ,
			   AQ_MSG_ID = p_new_msg_handle
			   WHERE REQUEST_ID = p_request_id
			   AND AQ_MSG_ID = p_old_msg_handle;

Line: 745

     JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name,  JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,'JTF_FM_REQUEST_GRP.UPDATE_REQUESTS_AQ');

Line: 747

END UPDATE_REQUESTS_AQ;

Line: 750

  Procedure to update JTF_FM_STATUS and JTF_FM_REQUEST_HISTORY table.
*/

PROCEDURE UPDATE_STATUS_HISTORY
(
  p_request_id  IN NUMBER,
  p_outcome_code IN VARCHAR2,
  p_msg_id   IN RAW
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_STATUS_HISTORY';

Line: 767

          UPDATE JTF_FM_STATUS_ALL
          SET
          REQUEST_STATUS =  p_outcome_code
          WHERE
          request_id = p_request_id;

Line: 773

          UPDATE JTF_FM_REQUEST_HISTORY_ALL
          SET
          outcome_code = p_outcome_code,
	      message_id = p_msg_id
          WHERE
          hist_req_id = p_request_id;

Line: 780

   JTF_FM_UTL_V.PRINT_MESSAGE('Begin procedure  UPDATE_STATUS_HISTORY',  JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,'JTF_FM_REQUEST_GRP.UPDATE_STATUS_HISTORY');

Line: 783

END UPDATE_STATUS_HISTORY;

Line: 785

PROCEDURE INSERT_REQUEST_CONTENTS(
   p_request_id  IN NUMBER,
   p_content_id  IN NUMBER,
   p_content_number IN NUMBER,
   p_content_name   IN VARCHAR2,
   p_content_type   IN VARCHAR2,
   p_document_type  IN VARCHAR2,
   p_body           IN VARCHAR2,
   p_user_note      IN VARCHAR2,
   p_quantity       IN NUMBER,
   p_media_type     IN VARCHAR2,
   p_content_source IN VARCHAR2,
   p_file_id        IN NUMBER
)
IS
   l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REQUEST_CONTENTS';

Line: 804

    JTF_FM_UTL_V.PRINT_MESSAGE('Begin PROCEDURE INSERT_REQUEST_CONTENTS',  JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,l_full_name);

Line: 806

   INSERT INTO JTF_FM_REQUEST_CONTENTS (
   REQUEST_ID,
   CONTENT_ID,
   CONTENT_NUMBER,
   CONTENT_NAME,
   CONTENT_TYPE,
   DOCUMENT_TYPE,
   BODY,
   USER_NOTES,
   QUANTITY,
   CREATED_BY,
   CREATION_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATE_LOGIN,
   MEDIA_TYPE,
   CONTENT_SOURCE,
   FND_FILE_ID)
   VALUES (
   p_request_id ,
   p_content_id,
   p_content_number,
   p_content_name,
   p_content_type,
   p_document_type,
   p_body,
   p_user_note,
   p_quantity ,
   FND_GLOBAL.USER_ID ,
   SYSDATE ,
   FND_GLOBAL.USER_ID ,
   SYSDATE,
   FND_GLOBAL.LOGIN_ID ,
   p_media_type ,
   p_content_source ,
   p_file_id );

Line: 843

       JTF_FM_UTL_V.PRINT_MESSAGE('End PROCEDURE INSERT_REQUEST_CONTENTS',  JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,l_full_name);

Line: 848

		JTF_FM_UTL_V.PRINT_MESSAGE('UNEXPECTED ERROR IN PROCEDURE INSERT_REQUEST_CONTENTS', JTF_FM_UTL_V.G_LEVEL_PROCEDURE  ,l_full_name);

Line: 852

END INSERT_REQUEST_CONTENTS;

Line: 867

     SELECT CONTENT_NUMBER into l_content_number FROM JTF_FM_REQUEST_CONTENTS
	 where REQUEST_ID = p_request_id and fnd_file_id = p_file_id;

Line: 1012

    SELECT *
    INTO   l_header_row
    FROM   jtf_fm_int_request_header
    WHERE  request_id = p_request_id
    ;

Line: 1019

    SELECT *
    INTO   l_processed_row
    FROM   jtf_fm_processed
    WHERE  request_id = p_request_id
    AND    job        = p_job_id
    ;

Line: 2497

  SELECT DISTINCT COUNT(REQUEST_ID) INTO l_req_count FROM JTF_FM_TEST_REQUESTS
    WHERE REQUEST_ID = p_request_id ;

Line: 2505

      SELECT ATTACH_FID INTO file_id FROM ibc_citems_v
        WHERE CITEM_ID = p_content_id
        and LANGUAGE = USERENV('LANG')
        and rownum=1;

Line: 2513

      SELECT ATTACH_FID INTO file_id FROM ibc_citems_v
        WHERE CITEM_ID = p_content_id
              and item_status = 'APPROVED'
              and LANGUAGE = USERENV('LANG')
              and rownum=1;

Line: 2563

    SELECT DISTINCT COUNT(REQUEST_ID) INTO l_req_count FROM JTF_FM_TEST_REQUESTS
    WHERE REQUEST_ID = p_request_id ;

Line: 2570

           SELECT CITEM_VER_ID INTO l_cItemVersionId FROM IBC_CITEMS_V
           WHERE CITEM_ID = TO_NUMBER(p_content_id)
     and LANGUAGE = USERENV('LANG');

Line: 2577

            SELECT CITEM_VER_ID INTO l_cItemVersionId FROM IBC_CITEMS_V
            WHERE CITEM_ID = TO_NUMBER(p_content_id) and item_status = 'APPROVED'
      and LANGUAGE = USERENV('LANG');

Line: 2686

  last_updated_by        NUMBER;

Line: 2687

  last_update_date       DATE;

Line: 2733

  SELECT DISTINCT COUNT(REQUEST_ID) INTO l_req_count FROM JTF_FM_TEST_REQUESTS
    WHERE REQUEST_ID = p_request_id ;

Line: 2775

        SELECT MAX(CITEM_VER_ID) INTO x_item_version_id
        FROM IBC_CITEMS_V
        WHERE CITEM_ID = TO_NUMBER(p_content_id)
        AND LANGUAGE = USERENV('LANG') ;

Line: 2793

        SELECT live_citem_version_id INTO x_item_version_id
          FROM ibc_content_items
          WHERE content_item_id = p_content_id;

Line: 2848

        ,x_last_updated_by        => last_updated_by
        ,x_last_update_date       => last_update_date
        ,x_attribute_type_codes   => attribute_type_codes
        ,x_attribute_type_names   => attribute_type_names
        ,x_attributes             => attributes
        ,x_component_citems       => component_citems
        ,x_component_attrib_types => component_attrib_types
        ,x_component_citem_names  => component_citem_names
        ,x_component_owner_ids    => component_owner_ids
        ,x_component_owner_types  => component_owner_types
        ,x_component_sort_orders  => component_sort_orders
        ,x_return_status          => return_status
        ,x_msg_count              => msg_count
        ,x_msg_data               => msg_data
    );

Line: 2908

    INSERT_REQUEST_CONTENTS(
                  p_request_id,
                  p_content_id,
                  l_count_total,
                  l_file_name,
                  'TEMPLATE',
                  'TEXT/HTML',
                  'Y',
                  p_user_note,
                  p_quantity,
                  p_media_type,
                  'ocm' ,
                  html_fnd_id);

Line: 2930

      INSERT_REQUEST_CONTENTS(
                  p_request_id,
                  p_content_id,
                  l_count_total,
                  l_file_name,
                  'TEMPLATE',
                  'text/plain',
                  'Y',
                  p_user_note,
                  p_quantity,
                  p_media_type,
                  'ocm' ,
                  text_fnd_id);

Line: 2998

     INSERT_REQUEST_CONTENTS(
                  p_request_id,
                  p_content_id,
                  l_count_total,
                  l_file_name,
                  'ATTACHMENT',
                  'text/html',
                  'N',
                  p_user_note,
                  p_quantity,
                  p_media_type,
                  'ocm' ,
                  x_temp_file_id);

Line: 3021

     INSERT_REQUEST_CONTENTS(
                  p_request_id,
                  p_content_id,
                  l_count_total,
                  l_file_name,
                  'DELIVERABLE',
                  'text/html',
                  'Y',
                  p_user_note,
                  p_quantity,
                  p_media_type,
                  'ocm' ,
                  x_temp_file_id);

Line: 3054

        DELETE FROM JTF_FM_TEST_REQUESTS WHERE REQUEST_ID = p_request_id;

Line: 3161

    SELECT  L.FILE_ID,L.FILE_NAME
    FROM JTF_AMV_ATTACHMENTS A,
         FND_LOBS L
    WHERE  A.ATTACHMENT_USED_BY_ID = p_content_id
    AND    A.FILE_ID = L.FILE_ID AND
           A.ATTACHMENT_USED_BY = 'ITEM';

Line: 3171

   SELECT FILE_ID, FILE_NAME
   FROM FND_LOBS
   WHERE
       FILE_ID = p_content_id;

Line: 3177

   SELECT query_id
   FROM  JTF_FM_QUERY_MES
   WHERE MES_DOC_ID = p_content_id;

Line: 3194

       select nvl(max(content_number),0) into l_count_total from JTF_FM_REQUEST_CONTENTS where request_id = p_request_id;

Line: 3235

   INSERT_REQUEST_CONTENTS(
         p_request_id,
      p_content_id,
            l_count_total,
      l_content_nm,
            'ATTACHMENT',
      l_file_type,
         'N',
             p_user_note,
       p_quantity,
             p_media_type,
      'mes' ,
             l_file_id);

Line: 3299

      INSERT_REQUEST_CONTENTS(
                    p_request_id,
                 p_content_id,
                 l_count_total,
              l_content_nm,
                 'QUERY',
                    'TEXT/HTML',
                       'Y',
                       p_user_note,
                       p_quantity,
                       p_media_type,
                 'mes' ,
                       l_file_id);

Line: 3331

      INSERT_REQUEST_CONTENTS(
                    p_request_id,
                 p_content_id,
                 l_count_total,
              l_content_nm,
                 'QUERY',
                    'APPLICATION/RTF',
                       'Y',
                        p_user_note,
                        p_quantity,
                        p_media_type,
                  'mes' ,
                        l_file_id);

Line: 3347

       INSERT_REQUEST_CONTENTS(
                        p_request_id,
                     p_content_id,
                     l_count_total,
                  l_content_nm,
                     'QUERY',
                        'APPLICATION/PDF',
                           'Y',
                            p_user_note,
                            p_quantity,
                            p_media_type,
                      'mes' ,
                            l_file_id);

Line: 3364

       INSERT_REQUEST_CONTENTS(
                    p_request_id,
                 p_content_id,
                 l_count_total,
              l_content_nm,
                 'QUERY',
                    'TEXT/HTML',
                       'Y',
                        p_user_note,
                        p_quantity,
                        p_media_type,
                  'mes' ,
                        l_file_id);

Line: 3394

  select file_id into l_query_file_id from jtf_fm_queries_all where query_id = x_query_id;

Line: 3398

      CHECK_AND_INSERT_QUERY(x_query_id, l_query_file_id, x_query_file_id);

Line: 3401

      INSERT_QUERY(x_query_id, x_query_file_id);

Line: 3488

                INSERT_REQUEST_CONTENTS(
                p_request_id,
                p_content_id,
                l_count_total,
                l_content_nm,
                upper(P_CONTENT_TYPE),
                l_file_type,
                l_body2,
              p_user_note,
              p_quantity,
              p_media_type,
              'mes' ,
              l_file_id);

Line: 3633

   SELECT JTF_FM_REQUESTHISTID_S.NEXTVAL INTO x_request_id FROM DUAL;

Line: 3781

   /*   select chr(13) cr, chr(10) lf into b, c from dual;

Line: 4589

SELECT REQUEST_QUEUE_NAME, RESPONSE_QUEUE_NAME
FROM JTF_FM_SERVICE
WHERE
    SERVER_ID = l_server_id;

Line: 4595

SELECT REQUEST, SERVER_ID
FROM JTF_FM_REQUEST_HISTORY_ALL
WHERE
    HIST_REQ_ID = p_request_id;

Line: 4722

          UPDATE JTF_FM_REQUEST_HISTORY_ALL
          SET
          outcome_code = 'PREVIEWED_SUBMITTED',
          outcome_desc = 'JTF_FM_API_PREVIEWED_SUBMITTED',
          last_update_date = sysdate
          WHERE
          hist_req_id = p_request_id
          AND
          submit_dt_tm = l_submit_dt;

Line: 4732

          UPDATE JTF_FM_STATUS_ALL
          SET
          request_status='PREVIEWED_SUBMITTED',
          last_update_date = sysdate
          WHERE
          request_id = p_request_id
          AND
          submit_dt_tm = l_submit_dt;

Line: 4907

SELECT
REQUEST,
PRIORITY,
SERVER_ID,
TEMPLATE_ID,
USER_ID,
SOURCE_CODE_ID,
SOURCE_CODE,
OBJECT_TYPE,
OBJECT_ID,
ORDER_ID,
RESUBMIT_COUNT,
REQUEST_TYPE,
MEDIA_TYPE
FROM JTF_FM_REQUEST_HISTORY_ALL
WHERE
    HIST_REQ_ID = p_request_id;

Line: 4955

   select to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))
   into   l_org_id
   from dual;

Line: 4994

     SELECT JTF_FM_REQUESTHISTID_S.NEXTVAL INTO x_request_id FROM DUAL;

Line: 4997

          SELECT DECODE(l_request_type,'M', MASS_REQUEST_Q, 'B',BATCH_REQUEST_Q,'T',BATCH_REQUEST_Q, REQUEST_QUEUE_NAME)
      INTO l_request_queue
          FROM JTF_FM_SERVICE
          WHERE
          SERVER_ID = l_server_id;

Line: 5050

             INSERT_REQ_CONTENTS(p_request_id,x_request_id);

Line: 5053

             INSERT INTO JTF_FM_REQUEST_HISTORY_ALL
               (
               MESSAGE_ID,
               SUBMIT_DT_TM,
               TEMPLATE_ID,
               USER_ID,
               PRIORITY,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN,
               SOURCE_CODE_ID,
               SOURCE_CODE,
               OBJECT_TYPE,
               OBJECT_ID,
               ORDER_ID,
               SERVER_ID,
               RESUBMIT_COUNT,
               OUTCOME_CODE,
               HIST_REQ_ID,
               REQUEST,
               ORG_ID,
               OBJECT_VERSION_NUMBER,
      REQUEST_TYPE,
      PARENT_REQ_ID,
      MEDIA_TYPE)
             VALUES
             (
               l_message_handle,
               l_submit_dt,
               l_template_id,
               l_req_user_id,
               l_priority,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.CONC_LOGIN_ID,
               l_source_code_id,
               l_source_code,
               l_object_type,
               l_object_id,
               l_order_id,
               l_server_id,
              (l_requeue_count+1),
               l_meaning,
               x_request_id,
               empty_clob(),
               l_org_id,
               1,
      l_request_type,
      p_request_id,
      l_media_type);

Line: 5109

               SELECT REQUEST INTO l_request
               FROM JTF_FM_REQUEST_HISTORY_ALL
               WHERE HIST_REQ_ID = x_request_id
               AND SUBMIT_DT_TM = l_submit_dt
               FOR UPDATE;

Line: 5120

             INSERT INTO JTF_FM_STATUS_ALL
               (
               SUBMIT_DT_TM,
               TEMPLATE_ID,
               USER_ID,
               PRIORITY,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN,
               SOURCE_CODE_ID,
               SOURCE_CODE,
               OBJECT_TYPE,
               OBJECT_ID,
               ORDER_ID,
               SERVER_ID,
               REQUEUE_COUNT,
               MESSAGE_ID,
               REQUEST_STATUS,
               REQUEST_ID,
               ORG_ID,
               OBJECT_VERSION_NUMBER)
             VALUES
             (
               l_submit_dt,
               l_template_id,
               l_req_user_id,
               l_priority,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.CONC_LOGIN_ID,
               l_source_code_id,
               l_source_code,
               l_object_type,
               l_object_id,
               l_order_id,
               l_server_id,
               l_requeue_count,
               l_message_handle,
               l_meaning,
               x_request_id,
               l_org_id,
               1);

Line: 5169

      JTF_FM_UTL_V.INSERT_EMAIL_STATS(x_request_id);

Line: 5404

SELECT
REQUEST,
PRIORITY,
SERVER_ID,
TEMPLATE_ID,
USER_ID,
SOURCE_CODE_ID,
SOURCE_CODE,
OBJECT_TYPE,
OBJECT_ID,
ORDER_ID,
RESUBMIT_COUNT,
REQUEST_TYPE,
MEDIA_TYPE
FROM JTF_FM_REQUEST_HISTORY_ALL
WHERE
    HIST_REQ_ID = p_request_id;

Line: 5450

   select to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))
   into   l_org_id
   from dual;

Line: 5518

     SELECT JTF_FM_REQUESTHISTID_S.NEXTVAL INTO x_request_id FROM DUAL;

Line: 5521

          SELECT  REQUEST_QUEUE_NAME
      INTO l_request_queue
          FROM JTF_FM_SERVICE
          WHERE
          SERVER_ID = l_server_id;

Line: 5669

             INSERT INTO JTF_FM_REQUEST_HISTORY_ALL
               (
               MESSAGE_ID,
               SUBMIT_DT_TM,
               TEMPLATE_ID,
               USER_ID,
               PRIORITY,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN,
               SOURCE_CODE_ID,
               SOURCE_CODE,
               OBJECT_TYPE,
               OBJECT_ID,
               ORDER_ID,
               SERVER_ID,
               RESUBMIT_COUNT,
               OUTCOME_CODE,
               HIST_REQ_ID,
               REQUEST,
               ORG_ID,
               OBJECT_VERSION_NUMBER,
      REQUEST_TYPE,
      PARENT_REQ_ID,
      MEDIA_TYPE)
             VALUES
             (
               l_message_handle,
               l_submit_dt,
               l_template_id,
               l_req_user_id,
               l_priority,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.CONC_LOGIN_ID,
               l_source_code_id,
               l_source_code,
               l_object_type,
               l_object_id,
               l_order_id,
               l_server_id,
               1,
               l_meaning,
               x_request_id,
               empty_clob(),
               l_org_id,
               1,
      l_request_type,
      p_request_id,
      l_media_type);

Line: 5725

               SELECT REQUEST INTO l_request
               FROM JTF_FM_REQUEST_HISTORY_ALL
               WHERE HIST_REQ_ID = x_request_id
               AND SUBMIT_DT_TM = l_submit_dt
               FOR UPDATE;

Line: 5736

             INSERT INTO JTF_FM_STATUS_ALL
               (
               SUBMIT_DT_TM,
               TEMPLATE_ID,
               USER_ID,
               PRIORITY,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN,
               SOURCE_CODE_ID,
               SOURCE_CODE,
               OBJECT_TYPE,
               OBJECT_ID,
               ORDER_ID,
               SERVER_ID,
               REQUEUE_COUNT,
               MESSAGE_ID,
               REQUEST_STATUS,
               REQUEST_ID,
               ORG_ID,
               OBJECT_VERSION_NUMBER)
             VALUES
             (
               l_submit_dt,
               l_template_id,
               l_req_user_id,
               l_priority,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               l_submit_dt,
               FND_GLOBAL.USER_ID,
               FND_GLOBAL.CONC_LOGIN_ID,
               l_source_code_id,
               l_source_code,
               l_object_type,
               l_object_id,
               l_order_id,
               l_server_id,
               l_requeue_count,
               l_message_handle,
               l_meaning,
               x_request_id,
               l_org_id,
               1);

Line: 5783

      INSERT_REQ_CONTENTS(p_request_id,x_request_id);

Line: 5788

      UPDATE JTF_FM_REQUEST_HISTORY_ALL
      set resubmit_count = l_requeue_count+1
      where hist_req_id = p_request_id;

Line: 5792

      UPDATE JTF_FM_EMAIL_STATS
            SET RESUBMITTED_JOB_COUNT = RESUBMITTED_JOB_COUNT+1
            where request_id = p_request_id;

Line: 5796

      JTF_FM_UTL_V.INSERT_EMAIL_STATS(x_request_id);

Line: 5798

      UPDATE_RESUBMITTED(p_request_id,p_job_id,x_request_id);

Line: 5903

   UPDATE JTF_FM_CONTENT_FAILURES
   set corrected_address = p_corrected_address,
   corrected_flag = 'Y'
   where
   request_id = p_request_id
   and job = p_job
   and MEDIA_TYPE = 'EMAIL'
   and FAILURE = 'MALFORMED_ADDRESS';

Line: 6065

   SELECT  JOB, CORRECTED_ADDRESS FROM
   JTF_FM_CONTENT_FAILURES
   WHERE
   REQUEST_ID = p_request_id
   AND CORRECTED_FLAG = 'Y'
   and MEDIA_TYPE = 'EMAIL'
   and FAILURE = 'MALFORMED_ADDRESS';

Line: 6119

    UPDATE JTF_FM_EMAIL_STATS
       SET RESUBMITTED_MALFORMED = RESUBMITTED_MALFORMED+1
       where request_id = l_request_id;

Line: 6238

SELECT MESSAGE_ID, SERVER_ID, REQUEST_TYPE,OUTCOME_CODE
FROM JTF_FM_REQUEST_HISTORY_ALL
WHERE
     HIST_REQ_ID=p_request_id;

Line: 6245

SELECT AQ_MSG_ID, QUEUE_TYPE
FROM JTF_FM_REQUESTS_AQ
WHERE REQUEST_ID = p_request_id;

Line: 6301

     SELECT  DECODE(l_request_type,'M', MASS_REQUEST_Q, 'B',BATCH_REQUEST_Q,'T',BATCH_REQUEST_Q, REQUEST_QUEUE_NAME)
  INTO l_request_queue
     FROM JTF_FM_SERVICE_ALL
     WHERE
     SERVER_ID = l_server_id;

Line: 6394

     DELETE FROM JTF_FM_STATUS_ALL
     WHERE
     request_id = p_request_id;

Line: 6400

     UPDATE JTF_FM_REQUEST_HISTORY_ALL
     SET
     outcome_code = l_meaning
     WHERE
         hist_req_id = p_request_id;

Line: 7395

SELECT AQ_MSG_ID
FROM JTF_FM_REQUESTS_AQ
WHERE REQUEST_ID = p_request_id;

Line: 7401

SELECT outcome_code, request_type, MESSAGE_ID, SERVER_ID
FROM JTF_FM_REQUEST_HISTORY_ALL
WHERE HIST_REQ_ID = p_request_id;

Line: 7445

      UPDATE_STATUS_HISTORY(p_request_id,l_meaning,l_message_handle_new);

Line: 7474

    UPDATE_REQUESTS_AQ(l_message_handle_new,p_request_id ,'BP', l_message_handle);

Line: 7482

    UPDATE_STATUS_HISTORY(p_request_id,l_meaning,l_message_handle_new);

Line: 7519

       UPDATE_REQUESTS_AQ(l_message_handle_new,p_request_id ,'M',l_message_handle);

Line: 7535

     UPDATE_REQUESTS_AQ(l_message_handle_new,p_request_id ,'B',l_message_handle);

Line: 7544

      UPDATE_STATUS_HISTORY(p_request_id,l_meaning,l_message_handle_new);