APPS.WSM_JOBPURGE_GRP SQL Statements

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

Line: 6

  PROCEDURE NAME:   delete_osfm_tables

===========================================================================*/


procedure append_report(
                        p_rec           in wip_wictpg.purge_report_type,
                        p_option        in number
                        )
is
begin
   if ( p_option IN (REPORT_ONLY, PURGE_AND_REPORT) ) then
    insert into Wip_temp_reports(
      key1,              /* Group ID */
      program_source,
      last_updated_by,
      organization_id,
      wip_entity_id,
      inventory_item_id,
      key2,              /* line ID */
      key3,              /* Repetitive schedule ID */
      description,       /* Table Name */
      key6,              /* Info Type */
      attribute1        /* Info */,
      date1,            /* Start Date */
      date2,            /* Completion Date */
      date3,            /* Close Date */
      attribute2,       /*Job Name*/
      attribute3        /*Line Code*/
    ) values (
      p_rec.group_id,
      'WICTPG',          /* program_source  */
      -1,                /* Last Updated By */
      p_rec.org_id,
      p_rec.wip_entity_id,
      p_rec.primary_item_id,
      p_rec.line_id,
      p_rec.schedule_id,
      p_rec.table_name,
      p_rec.info_type,
      p_rec.info,
      p_rec.start_date,
      p_rec.complete_date,
      p_rec.close_date,
      p_rec.entity_name,
      p_rec.line_code
    );

Line: 75

procedure delete_wie (
                      p_option        in number,
                      p_header_id     in number,
                      p_request_id    in number,
                      p_wie_num_rows  out NOCOPY number,
                      p_err_num       in out NOCOPY number,
                      p_err_buf       in out NOCOPY varchar2
                      )
is
begin
      if (p_option = REPORT_ONLY) then
         select  count(*)
           into  p_wie_num_rows
           from  WSM_INTERFACE_ERRORS
           where header_id = p_header_id
           and   request_id = nvl(p_request_id, -1);

Line: 92

         DELETE FROM WSM_INTERFACE_ERRORS
           WHERE header_id = p_header_id
           and   request_id = nvl(p_request_id, -1);

Line: 103

end delete_wie;

Line: 106

procedure delete_osfm_tables(
                              p_option        in number,
                              p_group_id      in number,
                              p_purge_request in wip_wictpg.get_purge_requests%rowtype,
                              -- ST Fix for bug 4918553 (Added the parameter p_detail_flag)
                              p_detail_flag   IN BOOLEAN DEFAULT TRUE,
                              p_return_status out NOCOPY varchar2
                              )
is
   x_num_rows        number := 0;

Line: 128

   e_delete_wie_exception      EXCEPTION;

Line: 134

     select wtxni.header_id, wtxni.request_id
     from wsm_split_merge_txn_interface wtxni
     where wtxni.organization_id = pOrgId
     and   wtxni.header_id in  (
                   select sj.header_id
                   from wsm_starting_jobs_interface sj
                   where  sj.wip_entity_id = pWipEntityId
                   union
                   select rj.header_id
                   from wsm_resulting_jobs_interface rj
                   where  rj.wip_entity_name = pEntityName
                   );

Line: 150

      select  wlji.header_id, wlji.request_id
        from  WSM_LOT_JOB_INTERFACE wlji
        where wlji.job_name = pEntityName
        and   wlji.organization_id = pOrgId;

Line: 157

      select  wlmti.header_id, wlmti.request_id
        from  WSM_LOT_MOVE_TXN_INTERFACE wlmti
        where wlmti.wip_entity_id = pWipEntityId
        and   wlmti.organization_id = pOrgId;

Line: 192

      delete_wie(
                 p_option       => p_option,
                 p_header_id    => x_header_id,
                 p_request_id   => x_request_id,
                 p_wie_num_rows => x_tmp_num_rows,
                 p_err_num      => p_err_num,
                 p_err_buf      => p_err_buf
                 );

Line: 202

         raise e_delete_wie_exception;

Line: 208

         select  count(*)
           into  x_tmp_num_rows
           from  WSM_STARTING_JOBS_INTERFACE
           where header_id = x_header_id;

Line: 214

         DELETE FROM WSM_STARTING_JOBS_INTERFACE
           WHERE  header_id = x_header_id;

Line: 223

         select  count(*)
           into  x_tmp_num_rows
           from  WSM_RESULTING_JOBS_INTERFACE
           where header_id = x_header_id;

Line: 229

         DELETE FROM WSM_RESULTING_JOBS_INTERFACE
           WHERE  header_id = x_header_id;

Line: 238

         select  count(*)
           into  x_tmp_num_rows
           from  WSM_SPLIT_MERGE_TXN_INTERFACE
           where header_id = x_header_id;

Line: 244

         DELETE FROM WSM_SPLIT_MERGE_TXN_INTERFACE
           WHERE  header_id = x_header_id;

Line: 254

                SELECT  count(*)
                INTO  x_tmp_num_rows
                FROM  WSM_SERIAL_TXN_INTERFACE
                WHERE header_id = x_header_id
                AND   transaction_type_id = 3;

Line: 260

                DELETE FROM WSM_SERIAL_TXN_INTERFACE
                WHERE  header_id = x_header_id
                AND    transaction_type_id = 3;

Line: 326

      delete_wie(
                 p_option       => p_option,
                 p_header_id    => x_header_id,
                 p_request_id   => x_request_id,
                 p_wie_num_rows => x_tmp_num_rows,
                 p_err_num      => p_err_num,
                 p_err_buf      => p_err_buf
                 );

Line: 336

         raise e_delete_wie_exception;

Line: 343

                SELECT  count(*)
                INTO  x_tmp_num_rows
                FROM  WSM_SERIAL_TXN_INTERFACE
                WHERE header_id = x_header_id
                AND   transaction_type_id = 1;

Line: 349

                DELETE FROM WSM_SERIAL_TXN_INTERFACE
                WHERE  header_id = x_header_id
                AND    transaction_type_id = 1;

Line: 372

      select  count(*)
        into  x_num_rows
        from  WSM_LOT_JOB_INTERFACE
        where job_name = x_purge_rec.entity_name
        and   organization_id = x_purge_rec.org_id;

Line: 379

      DELETE FROM WSM_LOT_JOB_INTERFACE
        WHERE job_name =  x_purge_rec.entity_name
        AND   organization_id = x_purge_rec.org_id;

Line: 405

      delete_wie(
                 p_option       => p_option,
                 p_header_id    => x_header_id,
                 p_request_id   => x_request_id,
                 p_wie_num_rows => x_tmp_num_rows,
                 p_err_num      => p_err_num,
                 p_err_buf      => p_err_buf
                 );

Line: 415

         raise e_delete_wie_exception;

Line: 422

                SELECT  count(*)
                INTO  x_tmp_num_rows
                FROM  WSM_SERIAL_TXN_INTERFACE
                WHERE header_id = x_header_id
                AND   transaction_type_id = 2;

Line: 428

                DELETE FROM WSM_SERIAL_TXN_INTERFACE
                WHERE  header_id = x_header_id
                AND    transaction_type_id = 2;

Line: 452

      select  count(*)
        into  x_num_rows
        from  WSM_LOT_MOVE_TXN_INTERFACE
        where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 459

      DELETE FROM WSM_LOT_MOVE_TXN_INTERFACE
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 482

        SELECT  COUNT(*)
        INTO  x_num_rows
        FROM  WSM_RESERVATIONS
        WHERE wip_entity_id = x_purge_rec.wip_entity_id;

Line: 487

        DELETE FROM WSM_RESERVATIONS
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 504

                SELECT  COUNT(*)
                INTO  x_num_rows
                FROM  WSM_OP_REASON_CODES
                WHERE wip_entity_id = x_purge_rec.wip_entity_id;

Line: 509

                DELETE FROM WSM_OP_REASON_CODES
                WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 523

                SELECT  COUNT(*)
                INTO  x_num_rows
                FROM  WSM_OP_SECONDARY_QUANTITIES
                WHERE wip_entity_id = x_purge_rec.wip_entity_id;

Line: 528

                DELETE FROM WSM_OP_SECONDARY_QUANTITIES
                WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 542

                SELECT  COUNT(*)
                INTO  x_num_rows
                FROM  WIP_RESOURCE_ACTUAL_TIMES
                WHERE wip_entity_id = x_purge_rec.wip_entity_id;

Line: 547

                DELETE FROM WIP_RESOURCE_ACTUAL_TIMES
                WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 561

                SELECT  COUNT(*)
                INTO  x_num_rows
                FROM  WSM_JOB_SECONDARY_QUANTITIES
                WHERE wip_entity_id = x_purge_rec.wip_entity_id;

Line: 566

                DELETE FROM WSM_JOB_SECONDARY_QUANTITIES
                WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 582

              select  count(*)
                into  x_num_rows
                from  WIP_OPERATION_YIELDS
                where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 589

              DELETE FROM WIP_OPERATION_YIELDS
                WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 607

      select  count(*)
        into  x_num_rows
        from  WSM_COPY_OPERATIONS
        where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 614

      DELETE FROM WSM_COPY_OPERATIONS
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 630

      select  count(*)
        into  x_num_rows
        from  WSM_COPY_OP_NETWORKS
        where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 637

      DELETE FROM WSM_COPY_OP_NETWORKS
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 653

      select  count(*)
        into  x_num_rows
        from  WSM_COPY_OP_RESOURCES
        where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 660

      DELETE FROM WSM_COPY_OP_RESOURCES
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 676

      select  count(*)
        into  x_num_rows
        from  WSM_COPY_OP_RESOURCE_INSTANCES
        where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 683

      DELETE FROM WSM_COPY_OP_RESOURCE_INSTANCES
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 700

      select  count(*)
        into  x_num_rows
        from  WSM_COPY_OP_RESOURCE_USAGE
        where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 707

      DELETE FROM WSM_COPY_OP_RESOURCE_USAGE
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 724

      select  count(*)
        into  x_num_rows
        from  WSM_COPY_REQUIREMENT_OPS
        where wip_entity_id = x_purge_rec.wip_entity_id;

Line: 731

      DELETE FROM WSM_COPY_REQUIREMENT_OPS
        WHERE  wip_entity_id = x_purge_rec.wip_entity_id;

Line: 747

        FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
                X_entity_name => 'WSM_LOT_BASED_OPERATIONS',
                X_pk1_value => to_char(x_purge_rec.wip_entity_id),
                X_pk2_value => to_char(x_purge_rec.org_id),
                X_delete_document_flag => 'Y' );

Line: 755

   when e_delete_wie_exception then
      append_report(x_purge_rec, p_option);

Line: 766

end delete_osfm_tables;