APPS.FLM_SEQ_UI SQL Statements

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

Line: 6

   * To delete a task and its details in FLM_SEQ_* tables           *
   ******************************************************************/
  PROCEDURE delete_tasks(p_seq_task_id IN NUMBER,
                         p_init_msg_list IN VARCHAR2,
                         x_return_status OUT NOCOPY VARCHAR2,
                         x_msg_count OUT NOCOPY NUMBER,
                         x_msg_data OUT NOCOPY VARCHAR2)
  IS
    l_debug_line NUMBER;

Line: 16

    SAVEPOINT flm_delete_tasks;

Line: 25

    DELETE FROM FLM_SEQ_TASK_EXCEPTIONS
    WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 29

    DELETE FROM FLM_SEQ_TASK_CONSTRAINTS
    WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 33

    DELETE FROM FLM_SEQ_TASK_DEMANDS
    WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 37

    DELETE FROM FLM_SEQ_TASK_LINES
    WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 41

    DELETE FROM FLM_FILTER_CRITERIA
    WHERE CRITERIA_GROUP_ID = (SELECT DEMAND_CRITERIA_GROUP_ID FROM FLM_SEQ_TASKS WHERE SEQ_TASK_ID = p_seq_task_id);

Line: 45

    DELETE FROM FLM_SEQ_TASKS
    WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 53

      ROLLBACK TO flm_delete_tasks;

Line: 56

        FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_tasks('||l_debug_line||')');

Line: 61

  END delete_tasks;

Line: 64

   * To delete a task and its details in FLM_SEQ_* tables. After that it commits           *
   *****************************************************************************************/
  PROCEDURE delete_tasks_commit(p_seq_task_id IN NUMBER,
                         p_init_msg_list IN VARCHAR2,
                         x_return_status OUT NOCOPY VARCHAR2,
                         x_msg_count OUT NOCOPY NUMBER,
                         x_msg_data OUT NOCOPY VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;

Line: 75

    SAVEPOINT flm_delete_tasks_commit;

Line: 84

    delete_tasks(p_seq_task_id,'F', x_return_status, x_msg_count, x_msg_data);

Line: 92

      ROLLBACK TO flm_delete_tasks_commit;

Line: 95

        FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_tasks_commit('||l_debug_line||')');

Line: 100

  END delete_tasks_commit;

Line: 333

   * To insert demand from MRP_UNSCHEDULED_ORDERS_V to FLM_SEQ_TASK_DEMANDS table.  *
   **********************************************************************************/
  PROCEDURE insert_demands(p_seq_task_id IN NUMBER,
                         p_max_rows IN NUMBER,
                         p_init_msg_list IN VARCHAR2,
                         x_return_status OUT NOCOPY VARCHAR2,
                         x_msg_count OUT NOCOPY NUMBER,
                         x_msg_data OUT NOCOPY VARCHAR2)
  IS
    l_criteria_group_id NUMBER;

Line: 358

    l_cursor_insert VARCHAR2(5000);

Line: 366

    SELECT LINE_ID
    FROM FLM_SEQ_TASK_LINES
    WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 370

    SAVEPOINT flm_insert_demands;

Line: 380

    SELECT NVL(DEMAND_CRITERIA_GROUP_ID,-1),NVL(ALTERNATE_ROUTING_FLAG,'N'),SEQ_TASK_TYPE, ORGANIZATION_ID,
           TO_CHAR(DEMAND_START_DATE,'DD-MON-RR HH24:MI:SS'), TO_CHAR(DEMAND_END_DATE,'DD-MON-RR HH24:MI:SS'),
           NVL(HONOR_PLANNING_FLAG,'N')
    INTO l_criteria_group_id,l_alternate_routing_flag,l_seq_task_type,l_org_id,
         l_demand_start_date,l_demand_end_date,l_planning_flag
    FROM FLM_SEQ_TASKS
    WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 399

      l_cursor_insert := NULL;

Line: 443

      l_cursor_cnt := 'SELECT COUNT(*), SUM(ORDER_QUANTITY) '||l_where;

Line: 464

        ROLLBACK TO flm_insert_demands;

Line: 477

        l_cursor_insert := 'INSERT INTO FLM_SEQ_TASK_DEMANDS (SEQ_TASK_ID,ALTERNATE_ROUTING_DESIGNATOR,LINE_ID,'||
                           'DEMAND_ID,SPLIT_NUMBER,OBJECT_VERSION_NUMBER,ORGANIZATION_ID,PRIMARY_ITEM_ID,'||
                           'OPEN_QTY,REQUESTED_QTY,FULFILLED_QTY,'||
                           'CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,'||
                           'LAST_UPDATE_DATE,LAST_UPDATED_BY,REQUEST_ID,PROGRAM_ID,PROGRAM_APPLICATION_ID,'||
                           'PROGRAM_UPDATE_DATE) ';

Line: 484

        l_cursor_insert := l_cursor_insert || 'SELECT :p_seq_task_id,MUOV.ALTERNATE_ROUTING_DESIGNATOR,'||
                           'MUOV.LINE_ID,MUOV.DEMAND_SOURCE_LINE,1,1,MUOV.ORGANIZATION_ID,'||
                           'MUOV.INVENTORY_ITEM_ID,MUOV.ORDER_QUANTITY,MUOV.ORDER_QUANTITY,0,'||
                           'fnd_global.user_id,sysdate,'||
                           'fnd_global.login_id,sysdate,fnd_global.user_id,fnd_global.conc_request_id,'||
                           'fnd_global.conc_program_id,fnd_global.prog_appl_id,sysdate ';

Line: 491

        l_cursor_insert := l_cursor_insert || l_where;

Line: 495

        dbms_sql.parse(l_cursor, l_cursor_insert, dbms_sql.v7);

Line: 511

      ROLLBACK TO flm_insert_demands;

Line: 515

        FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'insert_demands('||l_debug_line||')');

Line: 520

  END insert_demands;

Line: 559

   * To delete demands from FLM_SEQ_TASK_DEMANDS table.  *
   *******************************************************/
  PROCEDURE delete_demands(p_seq_task_id IN NUMBER,
                           p_init_msg_list IN VARCHAR2,
                           x_return_status OUT NOCOPY VARCHAR2,
                           x_msg_count OUT NOCOPY NUMBER,
                           x_msg_data OUT NOCOPY VARCHAR2)
  IS
    l_debug_line NUMBER;

Line: 569

    SAVEPOINT flm_delete_demands;

Line: 578

    DELETE FROM FLM_SEQ_TASK_DEMANDS WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 583

      ROLLBACK TO flm_delete_demands;

Line: 587

        FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_demands('||l_debug_line||')');

Line: 592

  END delete_demands;

Line: 595

   * To delete criteria from FLM_FILTER_CRITERIA table.  *
   *******************************************************/
  PROCEDURE delete_criteria(p_seq_task_id IN NUMBER,
                           p_init_msg_list IN VARCHAR2,
                           x_return_status OUT NOCOPY VARCHAR2,
                           x_msg_count OUT NOCOPY NUMBER,
                           x_msg_data OUT NOCOPY VARCHAR2)
  IS
    l_debug_line NUMBER;

Line: 606

    SAVEPOINT flm_delete_criteria;

Line: 616

    SELECT NVL(DEMAND_CRITERIA_GROUP_ID,-1)
      INTO l_criteria_group_id
      FROM FLM_SEQ_TASKS
     WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 622

    DELETE FROM FLM_FILTER_CRITERIA WHERE CRITERIA_GROUP_ID = l_criteria_group_id;

Line: 628

      ROLLBACK TO flm_delete_criteria;

Line: 632

        FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_criteria('||l_debug_line||')');

Line: 637

  END delete_criteria;

Line: 640

   * To insert lines from WIP_LINES into FLM_SEQ_TASK_LINES and all constraints on the   *
   * line default rule from FLM_SEQ_TASK_CONSTRAINTS into FLM_SEQ_TASK_CONSTRAINTS.      *
   ***************************************************************************************/
  PROCEDURE insert_line_constraints(p_seq_task_id IN NUMBER,
                                    p_line_id IN NUMBER,
                                    p_org_id IN NUMBER,
                                    p_init_msg_list IN VARCHAR2,
                                    x_return_status OUT NOCOPY VARCHAR2,
                                    x_msg_count OUT NOCOPY NUMBER,
                                    x_msg_data OUT NOCOPY VARCHAR2)
  IS
    l_debug_line NUMBER;

Line: 653

    SAVEPOINT flm_insert_line_constraints;

Line: 662

    INSERT INTO FLM_SEQ_TASK_LINES (
      SEQ_TASK_ID,
      LINE_ID,
      OBJECT_VERSION_NUMBER,
      ORGANIZATION_ID,
      SEQ_DIRECTION,
      START_TIME,
      STOP_TIME,
      HOURLY_RATE,
      CONNECT_FLAG,
      FIX_SEQUENCE_TYPE,
      FIX_SEQUENCE_AMOUNT,
      COMBINE_SCHEDULE_FLAG,
      AVAILABLE_CAPACITY,
      RESEQUENCED_QTY,
      RULE_ID,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATE_LOGIN,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      REQUEST_ID,
      PROGRAM_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_UPDATE_DATE )
    SELECT
      p_seq_task_id,
      p_line_id,
      1,
      ORGANIZATION_ID,
      NVL(SEQ_DIRECTION,1),
      START_TIME,
      STOP_TIME,
      MAXIMUM_RATE,
      NVL(SEQ_CONNECT_FLAG,'N'),
      NVL(SEQ_FIX_SEQUENCE_TYPE,1),
      SEQ_FIX_SEQUENCE_AMOUNT,
      NVL(SEQ_COMBINE_SCHEDULE_FLAG,'N'),
      0,
      0,
      SEQ_DEFAULT_RULE_ID,
      fnd_global.user_id,
      sysdate,
      fnd_global.login_id,
      sysdate,
      fnd_global.user_id,
      NULL,
      NULL,
      NULL,
      NULL
    FROM WIP_LINES
    WHERE LINE_ID = p_line_id AND ORGANIZATION_ID = p_org_id;

Line: 715

    INSERT INTO FLM_SEQ_TASK_CONSTRAINTS (
      SEQ_TASK_ID,
      LINE_ID,
      PARENT_CONSTRAINT_NUMBER,
      CONSTRAINT_NUMBER,
      OBJECT_VERSION_NUMBER,
      ORGANIZATION_ID,
      PRIORITY,
      CONSTRAINT_TYPE,
      CONSTRAINT_TYPE_VALUE1,
      CONSTRAINT_TYPE_VALUE2,
      CONSTRAINT_TYPE_VALUE3,
      ATTRIBUTE_ID,
      ATTRIBUTE_VALUE1_NAME,
      ATTRIBUTE_VALUE2_NAME,
      ATTRIBUTE_VALUE1_NUM,
      ATTRIBUTE_VALUE2_NUM,
      ATTRIBUTE_VALUE1_DATE,
      ATTRIBUTE_VALUE2_DATE,
      FULFILLED_TO_QTY,
      VIOLATION_COUNT,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATE_LOGIN,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      REQUEST_ID,
      PROGRAM_ID,
      PROGRAM_APPLICATION_ID,
      PROGRAM_UPDATE_DATE )
    SELECT
      p_seq_task_id,
      p_line_id,
      C.PARENT_CONSTRAINT_NUMBER,
      C.CONSTRAINT_NUMBER,
      1,
      C.ORGANIZATION_ID,
      C.PRIORITY,
      C.CONSTRAINT_TYPE,
      C.CONSTRAINT_TYPE_VALUE1,
      C.CONSTRAINT_TYPE_VALUE2,
      C.CONSTRAINT_TYPE_VALUE3,
      C.ATTRIBUTE_ID,
      C.ATTRIBUTE_VALUE1_NAME,
      C.ATTRIBUTE_VALUE2_NAME,
      C.ATTRIBUTE_VALUE1_NUM,
      C.ATTRIBUTE_VALUE2_NUM,
      C.ATTRIBUTE_VALUE1_DATE,
      C.ATTRIBUTE_VALUE2_DATE,
      0,
      0,
      fnd_global.user_id,
      sysdate,
      fnd_global.login_id,
      sysdate,
      fnd_global.user_id,
      NULL,
      NULL,
      NULL,
      NULL
    FROM FLM_SEQ_RULE_CONSTRAINTS C, WIP_LINES L
    WHERE C.RULE_ID = L.SEQ_DEFAULT_RULE_ID
      AND L.LINE_ID = p_line_id
      AND L.ORGANIZATION_ID = p_org_id;

Line: 782

      ROLLBACK TO flm_insert_line_constraints;

Line: 786

        FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'insert_line_constraints('||l_debug_line||')');

Line: 791

  END insert_line_constraints;

Line: 794

   * To delete lines from FLM_SEQ_TASK_LINES and line contraints from FLM_SEQ_TASK_CONSTRAINTS table.  *
   *****************************************************************************************************/
  PROCEDURE delete_line_constraints(p_seq_task_id IN NUMBER,
                                    p_init_msg_list IN VARCHAR2,
                                    x_return_status OUT NOCOPY VARCHAR2,
                                    x_msg_count OUT NOCOPY NUMBER,
                                    x_msg_data OUT NOCOPY VARCHAR2)
  IS
    l_debug_line NUMBER;

Line: 804

    SAVEPOINT flm_delete_line_constraints;

Line: 813

    DELETE FROM FLM_SEQ_TASK_LINES WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 816

    DELETE FROM FLM_SEQ_TASK_CONSTRAINTS WHERE SEQ_TASK_ID = p_seq_task_id;

Line: 822

      ROLLBACK TO flm_delete_line_constraints;

Line: 826

        FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_line_constraints('||l_debug_line||')');

Line: 831

  END delete_line_constraints;

Line: 855

    SELECT min(wip_entity_id)
    INTO x_wip_entity_id
    FROM WIP_FLOW_SCHEDULES
    WHERE scheduled_completion_date >= flm_timezone.client00_in_server(p_start_date) --fix bug#3170105
      AND organization_id = p_org_id;

Line: 884

    SELECT seq_task_id
    FROM FLM_SEQ_TASKS
    WHERE seq_request_id = -1
      AND creation_date < sysdate-2;

Line: 905

      delete_tasks(l_task_rec.seq_task_id, 'F', l_return_status, l_msg_count, l_msg_data);