APPS.CSL_LOBS_ACC_PKG SQL Statements

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

Line: 12

  PROCEDURE INSERT_ACC_RECORD ( p_task_assignment_id IN NUMBER,
	    	                p_resource_id IN NUMBER)
  IS
    l_task_assignment_id jtf_task_assignments.task_assignment_id%type;

Line: 34

    SELECT distinct lob.file_id, jtrs.user_id
      FROM jtf_task_assignments jtf, csf_debrief_headers dbfhdr,
           jtf_rs_resource_extns jtrs, asg_user, fnd_attached_documents fndatt,
           fnd_documents_tl fnddoc_tl, fnd_documents fnddoc, fnd_lobs lob,
           fnd_document_categories_tl fnddoccat_tl
      WHERE jtf.task_assignment_id = p_task_assignment_id
        AND jtf.task_assignment_id = dbfhdr.task_assignment_id
        AND jtf.resource_id = p_resource_id
        AND jtrs.resource_id = jtf.resource_id
        AND asg_user.resource_id = jtf.resource_id
        AND fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
        AND fndatt.pk1_value = dbfhdr.debrief_header_id
        AND fndatt.document_id = fnddoc_tl.document_id
        AND fnddoc_tl.document_id = fnddoc.document_id
        AND fnddoc_tl.language = asg_user.language
        AND fnddoc_tl.media_id = lob.file_id
        AND fnddoccat_tl.category_id = fnddoc.category_id
        AND fnddoccat_tl.language = asg_user.language
        AND fnddoccat_tl.user_name = 'Signature';

Line: 55

     SELECT resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id;

Line: 97

       l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.INSERT_ACC_RECORD :'
                   || to_char(p_task_assignment_id);

Line: 101

         , 'LOBS_ACC_PKG.INSERT_ACC'
         , l_err_mesg
         , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 108

   	l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.INSERT_ACC_RECORD : '
                    || to_char(p_task_assignment_id);

Line: 112

         , 'LOBS_ACC_PKG.INSERT_ACC'
         , l_err_mesg
         , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 118

  END INSERT_ACC_RECORD;

Line: 122

  PROCEDURE DELETE_ACC_RECORD ( p_task_assignment_id IN NUMBER,
                                p_resource_id IN NUMBER)

  IS
    l_task_assignment_id 	jtf_task_assignments.task_assignment_id%type;

Line: 145

    SELECT distinct lob.file_id, acc.user_id
       FROM csm_task_assignments_acc acc , csf_debrief_headers dbfhdr,
         asg_user, fnd_attached_documents fndatt, fnd_documents_tl fnddoc_tl,
         fnd_documents fnddoc, fnd_document_categories_tl fnddoccat_tl,
         fnd_lobs lob
       WHERE asg_user.resource_id = p_resource_id
         AND acc.task_assignment_id = p_task_assignment_id
         AND acc.task_assignment_id = dbfhdr.task_assignment_id
         AND asg_user.user_id = acc.user_id
         AND fndatt.entity_name = 'CSF_DEBRIEF_HEADERS'
         AND fndatt.pk1_value = dbfhdr.debrief_header_id
         AND fndatt.document_id = fnddoc_tl.document_id
         AND fnddoc_tl.document_id = fnddoc.document_id
         AND fnddoc_tl.language = asg_user.language
         AND fnddoc_tl.media_id = lob.file_id
         AND fnddoccat_tl.category_id = fnddoc.category_id
         AND fnddoccat_tl.language = asg_user.language
         AND fnddoccat_tl.user_name = 'Signature';

Line: 165

     SELECT resource_id FROM jtf_rs_resource_extns WHERE user_id = p_user_id;

Line: 205

      l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.DELETE_ACC_RECORD :'
                  || to_char(p_task_assignment_id);

Line: 209

         , 'LOBS_ACC_PKG.INSERT_ACC'
         , l_err_mesg
         , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 215

      l_err_mesg := 'EXCEPTION IN CSL_LOBS_PKG.DELETE_ACC_RECORD :'
                  || to_char(p_task_assignment_id);

Line: 219

         , 'LOBS_ACC_PKG.INSERT_ACC'
         , l_err_mesg
         , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 224

  END DELETE_ACC_RECORD;

Line: 236

      select LAST_RUN_DATE
      from   JTM_CON_REQUEST_DATA
      where  package_name =  'CSL_LOBS_ACC_PKG'
      AND    procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';

Line: 243

      SELECT  lobs.file_id, acc.resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           csl_cs_incidents_all_acc acc, asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fndattdoc.pk1_value = to_char(acc.incident_id)
      AND asg.resource_id = acc.resource_id
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'CS_INCIDENTS'
      AND fnddoc.datatype_id=6
      AND fndattdoc.last_update_date >= b_lastRundate;

Line: 263

      SELECT  lobs.file_id, acc.resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           csl_jtf_tasks_acc acc, asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fndattdoc.pk1_value = to_char(acc.task_id)
      AND asg.resource_id = acc.resource_id
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'JTF_TASKS_B'
      AND fnddoc.datatype_id=6
      AND fndattdoc.last_update_date >= b_lastRundate;

Line: 284

      SELECT  lobs.file_id, acc.resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           csl_hz_parties_acc acc, asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fndattdoc.pk1_value = to_char(acc.party_id)
      AND asg.resource_id = acc.resource_id
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'HZ_PARTIES'
      AND fnddoc.datatype_id=6
      AND fndattdoc.last_update_date >= b_lastRundate;

Line: 304

      SELECT  lobs.file_id, acc.resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           csl_csi_item_instances_acc acc, asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fndattdoc.pk1_value = to_char(acc.instance_id)
      AND asg.resource_id = acc.resource_id
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'CS_CUSTOMER_PRODUCTS_ALL'
      AND fnddoc.datatype_id=6
      AND fndattdoc.last_update_date >= b_lastRundate;

Line: 324

      SELECT  lobs.file_id, acc.resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           JTM_CSF_DEBRIEF_HEADERS_ACC acc, asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fndattdoc.pk1_value = to_char(acc.debrief_header_id)
      AND asg.resource_id = acc.resource_id
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'CSF_DEBRIEF_HEADERS'
      AND fnddoc.datatype_id=6
      AND fndattdoc.last_update_date >= b_lastRundate;

Line: 372

    l_tab_access_id.delete;

Line: 373

    l_tab_resource_id.delete;

Line: 378

      /*Update the last run date*/
      UPDATE jtm_con_request_data SET last_run_date = l_current_run_date
       WHERE package_name =  'CSL_LOBS_ACC_PKG'
       AND   procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';

Line: 410

        , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
       );

Line: 426

    l_tab_access_id.delete;

Line: 427

    l_tab_resource_id.delete;

Line: 438

        , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
       );

Line: 454

    l_tab_access_id.delete;

Line: 455

    l_tab_resource_id.delete;

Line: 466

        , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
       );

Line: 481

    l_tab_access_id.delete;

Line: 482

    l_tab_resource_id.delete;

Line: 493

        , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
       );

Line: 509

    l_tab_access_id.delete;

Line: 510

    l_tab_resource_id.delete;

Line: 521

        , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
       );

Line: 536

    /*Update the last run date*/
    UPDATE jtm_con_request_data SET last_run_date = l_current_run_date
     WHERE package_name =  'CSL_LOBS_ACC_PKG'
     AND   procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';

Line: 585

      SELECT  lobs.file_id, acc.resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           csl_cs_incidents_all_acc acc, asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fndattdoc.pk1_value = to_char(acc.incident_id)
      AND asg.resource_id = acc.resource_id
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'CS_INCIDENTS'
      AND fnddoc.datatype_id=6
      AND acc.incident_id = b_incident_id;

Line: 621

    l_tab_access_id.delete;

Line: 622

    l_tab_resource_id.delete;

Line: 627

      /*Update the last run date*/

      IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
        jtm_message_log_pkg.Log_Msg
        ( 0
        , g_table_name
        , 'Leaving DOWNLOAD_SR_ATTACHMENTS - Max att download size is less than 1'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
        );

Line: 650

        , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
       );

Line: 698

      SELECT  lobs.file_id, acc.resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           csl_jtf_tasks_acc acc, asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fndattdoc.pk1_value = to_char(acc.task_id)
      AND asg.resource_id = acc.resource_id
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'JTF_TASKS_B'
      AND fnddoc.datatype_id=6
      AND acc.task_id = b_task_id;

Line: 749

    l_tab_access_id.delete;

Line: 750

    l_tab_resource_id.delete;

Line: 761

        , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
       );

Line: 801

PROCEDURE DELETE_ATTACHMENTS ( p_entity_name IN VARCHAR2,
                                p_primary_key IN NUMBER,
                                p_resource_id IN NUMBER)

  IS

    CURSOR c_SRAtt ( b_incident_id IN NUMBER,
                     b_resource_id IN NUMBER) IS
      SELECT  lobs.file_id, b_resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'CS_INCIDENTS'
      AND fnddoc.datatype_id=6
      AND fndattdoc.pk1_value = to_char(b_incident_id)
      AND asg.resource_id = b_resource_id;

Line: 828

      SELECT  lobs.file_id, b_resource_id
      FROM fnd_documents_tl fnddoc_tl, fnd_documents fnddoc,
           fnd_document_categories_tl fnddoccat_tl,
           fnd_attached_documents fndattdoc, fnd_lobs lobs,
           asg_user asg
      WHERE fndattdoc.document_id = fnddoc_tl.document_id
      AND fnddoc_tl.language = asg.language
      AND fnddoc_tl.document_id = fnddoc.document_id
      AND fnddoc_tl.media_id = lobs.file_id
      AND fnddoccat_tl.category_id = fnddoc.category_id
      AND fnddoccat_tl.language = asg.language
      AND fnddoccat_tl.name = 'MISC'
      AND fndattdoc.entity_name = 'JTF_TASKS_B'
      AND fnddoc.datatype_id=6
      AND fndattdoc.pk1_value = to_char(b_task_id)
      AND asg.resource_id = b_resource_id;

Line: 861

      , 'Entering Delete_Attachments'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 884

          , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
          , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
         );

Line: 920

          , 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s)'
          , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
         );

Line: 939

      , 'Exiting Delete_Attachments'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 950

         , 'LOBS_ACC_PKG.DELETE_ATTACHMENTS'
         , l_err_mesg
         , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 955

  END DELETE_ATTACHMENTS;