APPS.FLM_SEQ_READER_WRITER SQL Statements

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

Line: 129

      SELECT
	max(BUILD_SEQUENCE)
      FROM
	WIP_FLOW_SCHEDULES
      WHERE
        ORGANIZATION_ID = p_organization_id AND
        LINE_ID = p_line_id AND
	SCHEDULED_COMPLETION_DATE <= l_date1;

Line: 139

      SELECT
	min(BUILD_SEQUENCE)
      FROM
	WIP_FLOW_SCHEDULES
      WHERE
        ORGANIZATION_ID = p_organization_id AND
        LINE_ID = p_line_id AND
	SCHEDULED_COMPLETION_DATE > l_date2;

Line: 218

  sch_rec_tbl.DELETE;

Line: 219

  g_cto_line_tbl.DELETE;

Line: 316

 * To default the schedule columns and inserting the schedules    *
 ******************************************************************/
PROCEDURE create_schedules (o_return_code OUT NOCOPY NUMBER) IS
l_return_code NUMBER;

Line: 320

l_sch_tbl_to_insert wip_flow_schedule_tbl;

Line: 324

  default_attributes(sch_rec_tbl, l_sch_tbl_to_insert, l_return_code);

Line: 330

  insert_schedules(l_sch_tbl_to_insert, l_return_code);

Line: 336

  explode_all_items(l_sch_tbl_to_insert,l_return_code);

Line: 342

  update_mrp_recommendations(l_sch_tbl_to_insert,l_return_code);

Line: 370

                             l_sch_tbl_to_insert IN OUT NOCOPY wip_flow_schedule_tbl,
                             o_return_code OUT NOCOPY NUMBER) IS
  l_index NUMBER;

Line: 382

    l_sch_tbl_to_insert(l_index).primary_item_id :=
      sch_rec_tbl(l_index).primary_item_id;

Line: 385

    l_sch_tbl_to_insert(l_index).organization_id :=
      sch_rec_tbl(l_index).org_id;

Line: 388

    l_sch_tbl_to_insert(l_index).planned_quantity :=
      sch_rec_tbl(l_index).planned_quantity;

Line: 391

    l_sch_tbl_to_insert(l_index).alternate_routing_designator :=
      sch_rec_tbl(l_index).alt_rtg_designator;

Line: 394

    l_sch_tbl_to_insert(l_index).scheduled_start_date :=
      sch_rec_tbl(l_index).sch_start_date;

Line: 397

    l_sch_tbl_to_insert(l_index).scheduled_completion_date :=
      sch_rec_tbl(l_index).sch_completion_date;

Line: 400

    l_sch_tbl_to_insert(l_index).schedule_group_id :=
      sch_rec_tbl(l_index).sch_group_id;

Line: 404

      l_sch_tbl_to_insert(l_index).build_sequence := null;

Line: 406

      l_sch_tbl_to_insert(l_index).build_sequence :=
        sch_rec_tbl(l_index).build_sequence;

Line: 410

    l_sch_tbl_to_insert(l_index).line_id :=
      sch_rec_tbl(l_index).line_id;

Line: 413

    l_sch_tbl_to_insert(l_index).demand_source_type :=
      sch_rec_tbl(l_index).demand_type;

Line: 418

      l_sch_tbl_to_insert(l_index).demand_source_line :=
        to_char(sch_rec_tbl(l_index).demand_id);

Line: 422

    l_sch_tbl_to_insert(l_index).wip_entity_id :=
      sch_rec_tbl(l_index).wip_entity_id;

Line: 425

    l_sch_tbl_to_insert(l_index).schedule_number :=
      sch_rec_tbl(l_index).schedule_number;

Line: 429

    l_sch_tbl_to_insert(l_index).last_update_date := sysdate;

Line: 430

    l_sch_tbl_to_insert(l_index).last_updated_by := g_user_id;

Line: 431

    l_sch_tbl_to_insert(l_index).creation_date := sysdate;

Line: 432

    l_sch_tbl_to_insert(l_index).created_by := g_user_id;

Line: 433

    l_sch_tbl_to_insert(l_index).last_update_login := g_login_id;

Line: 436

    l_sch_tbl_to_insert(l_index).scheduled_flag := 1;

Line: 437

    l_sch_tbl_to_insert(l_index).status := 1;

Line: 438

    l_sch_tbl_to_insert(l_index).quantity_completed := 0;

Line: 439

    l_sch_tbl_to_insert(l_index).quantity_scrapped := 0;

Line: 442

    l_sch_tbl_to_insert(l_index).date_closed := null;

Line: 445

    l_sch_tbl_to_insert(l_index).project_id := null;

Line: 446

    l_sch_tbl_to_insert(l_index).task_id := null;*/

Line: 447

    l_sch_tbl_to_insert(l_index).kanban_card_id := null;

Line: 450

    l_sch_tbl_to_insert(l_index).attribute1 := g_attribute1;

Line: 451

    l_sch_tbl_to_insert(l_index).attribute2 := g_attribute2;

Line: 452

    l_sch_tbl_to_insert(l_index).attribute3 := g_attribute3;

Line: 453

    l_sch_tbl_to_insert(l_index).attribute4 := g_attribute4;

Line: 454

    l_sch_tbl_to_insert(l_index).attribute5 := g_attribute5;

Line: 455

    l_sch_tbl_to_insert(l_index).attribute6 := g_attribute6;

Line: 456

    l_sch_tbl_to_insert(l_index).attribute7 := g_attribute7;

Line: 457

    l_sch_tbl_to_insert(l_index).attribute8 := g_attribute8;

Line: 458

    l_sch_tbl_to_insert(l_index).attribute9 := g_attribute9;

Line: 459

    l_sch_tbl_to_insert(l_index).attribute10 := g_attribute10;

Line: 460

    l_sch_tbl_to_insert(l_index).attribute11 := g_attribute11;

Line: 461

    l_sch_tbl_to_insert(l_index).attribute12 := g_attribute12;

Line: 462

    l_sch_tbl_to_insert(l_index).attribute13 := g_attribute13;

Line: 463

    l_sch_tbl_to_insert(l_index).attribute14 := g_attribute14;

Line: 464

    l_sch_tbl_to_insert(l_index).attribute15 := g_attribute15;

Line: 467

    l_class_code := get_class_code(l_sch_tbl_to_insert(l_index).organization_id,
                                   l_sch_tbl_to_insert(l_index).primary_item_id);

Line: 469

    l_sch_tbl_to_insert(l_index).class_code := l_class_code;

Line: 472

    get_account_ids (l_sch_tbl_to_insert(l_index).organization_id,
                     l_sch_tbl_to_insert(l_index).class_code,
                     l_sch_tbl_to_insert(l_index).material_account,
                     l_sch_tbl_to_insert(l_index).material_overhead_account,
                     l_sch_tbl_to_insert(l_index).resource_account,
                     l_sch_tbl_to_insert(l_index).outside_processing_account,
                     l_sch_tbl_to_insert(l_index).material_variance_account,
                     l_sch_tbl_to_insert(l_index).resource_variance_account,
                     l_sch_tbl_to_insert(l_index).outside_proc_variance_account,
                     l_sch_tbl_to_insert(l_index).std_cost_adjustment_account,
                     l_sch_tbl_to_insert(l_index).overhead_account,
                     l_sch_tbl_to_insert(l_index).overhead_variance_account);

Line: 486

    get_bom_rev_and_date(l_sch_tbl_to_insert(l_index).organization_id,
                         l_sch_tbl_to_insert(l_index).primary_item_id,
                         l_sch_tbl_to_insert(l_index).scheduled_completion_date,
                         l_sch_tbl_to_insert(l_index).bom_revision,
                         l_sch_tbl_to_insert(l_index).bom_revision_date);

Line: 493

    get_rtg_rev_and_date(l_sch_tbl_to_insert(l_index).organization_id,
                         l_sch_tbl_to_insert(l_index).primary_item_id,
                         l_sch_tbl_to_insert(l_index).scheduled_completion_date,
                         l_sch_tbl_to_insert(l_index).routing_revision,
                         l_sch_tbl_to_insert(l_index).routing_revision_date);

Line: 500

    get_alt_bom_designator(l_sch_tbl_to_insert(l_index).organization_id,
                           l_sch_tbl_to_insert(l_index).primary_item_id,
                           l_sch_tbl_to_insert(l_index).alternate_routing_designator,
                           l_sch_tbl_to_insert(l_index).alternate_bom_designator);

Line: 508

    get_completion_subinv_and_loc(l_sch_tbl_to_insert(l_index).organization_id,
                                  l_sch_tbl_to_insert(l_index).primary_item_id,
                                  l_sch_tbl_to_insert(l_index).alternate_routing_designator,
                                  l_sch_tbl_to_insert(l_index).completion_subinventory,
                                  l_sch_tbl_to_insert(l_index).completion_locator_id);

Line: 518

                     l_sch_tbl_to_insert(l_index).demand_class,
                     l_sch_tbl_to_insert(l_index).demand_source_header_id);

Line: 526

                     l_sch_tbl_to_insert(l_index).project_id,
                     l_sch_tbl_to_insert(l_index).task_id);

Line: 530

    l_sch_tbl_to_insert(l_index).request_id := USERENV('SESSIONID');

Line: 556

  SELECT wip_entities_s.nextval
  INTO   l_wip_entity_id
  FROM   dual;

Line: 595

    SELECT demand_class_code,header_id
    INTO   l_demand_class, l_demand_header
    FROM   OE_ORDER_LINES_ALL
    WHERE  line_id = i_demand_id;

Line: 622

    SELECT project_id,task_id
    INTO   l_project_id, l_task_id
    FROM   OE_ORDER_LINES_ALL
    WHERE  line_id = i_demand_id;

Line: 690

    SELECT count(bill_sequence_id)
    INTO   l_bill_count
    FROM   BOM_BILL_OF_MATERIALS
    WHERE  organization_id = i_org_id AND
           assembly_item_id = i_primary_item_id AND
           alternate_bom_designator = i_alt_rtg_designator;

Line: 791

  SELECT MATERIAL_ACCOUNT, MATERIAL_OVERHEAD_ACCOUNT,
         RESOURCE_ACCOUNT, OUTSIDE_PROCESSING_ACCOUNT,
         MATERIAL_VARIANCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT,
         OUTSIDE_PROC_VARIANCE_ACCOUNT, STD_COST_ADJUSTMENT_ACCOUNT,
         OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT
  INTO
         l_material_act, l_material_overhead_act,
         l_resource_act, l_outside_processing_act,
         l_material_variance_act, l_resource_variance_act,
         l_outside_proc_variance_act, l_std_cost_adjustment_act,
         l_overhead_act, l_overhead_variance_act
  FROM   WIP_ACCOUNTING_CLASSES
  WHERE  ORGANIZATION_ID = i_org_id AND
         CLASS_CODE = i_class_code;

Line: 839

    SELECT default_discrete_class
    INTO   l_class_code
    FROM   wip_parameters
    WHERE  organization_id = i_org_id;

Line: 1017

        select msi.build_in_wip_flag, msi.replenish_to_order_flag
        into   l_build_in_wip_flag, l_replenish_to_order_flag
        from   mtl_system_items msi
        where  msi.inventory_item_id = l_primary_item_id
               and msi.organization_id = l_org_id;

Line: 1049

 * To update the mrp_recommendations based on schedules inserted  *
 ******************************************************************/
PROCEDURE update_mrp_recommendations(i_schedules_tbl IN  wip_flow_schedule_tbl,
                                     o_return_code IN OUT NOCOPY NUMBER) IS

l_index NUMBER;

Line: 1055

l_item_tbl_to_update_rec wip_flow_schedule_tbl;

Line: 1065

    if(l_item_tbl_to_update_rec.COUNT > 1) then
      for j in l_item_tbl_to_update_rec.FIRST .. l_item_tbl_to_update_rec.LAST
      LOOP
        if(
            (l_item_tbl_to_update_rec(j).organization_id =
               i_schedules_tbl(i).organization_id) AND
            (l_item_tbl_to_update_rec(j).demand_source_line =
               i_schedules_tbl(i).demand_source_line) AND
            (i_schedules_tbl(i).demand_source_header_id IS NULL )
          )  then
          l_item_tbl_to_update_rec(j).planned_quantity :=
            l_item_tbl_to_update_rec(j).planned_quantity +
            i_schedules_tbl(i).planned_quantity;

Line: 1088

      l_index := l_item_tbl_to_update_rec.COUNT;

Line: 1091

      l_item_tbl_to_update_rec(l_index).organization_id :=
        i_schedules_tbl(i).organization_id;

Line: 1093

      l_item_tbl_to_update_rec(l_index).demand_source_line :=
        i_schedules_tbl(i).demand_source_line;

Line: 1095

      l_item_tbl_to_update_rec(l_index).planned_quantity :=
        i_schedules_tbl(i).planned_quantity;

Line: 1100

  if(l_item_tbl_to_update_rec.COUNT > 0) then
    for k in l_item_tbl_to_update_rec.FIRST .. l_item_tbl_to_update_rec.LAST
    LOOP
      UPDATE mrp_recommendations
      SET quantity_in_process =
          nvl(quantity_in_process,0) + l_item_tbl_to_update_rec(k).planned_quantity
      WHERE transaction_id = l_item_tbl_to_update_rec(k).demand_source_line;

Line: 1115

END update_mrp_recommendations;

Line: 1119

 * Used to insert all the schedule in the table                   *
 ******************************************************************/
PROCEDURE insert_schedules (
        i_schedules_tbl       IN      wip_flow_schedule_tbl,
        o_return_code           OUT NOCOPY     NUMBER) IS
l_index NUMBER;

Line: 1131

     insert into wip_flow_schedules
     (
       scheduled_flag,
       wip_entity_id,
       organization_id,
       last_update_date,
       last_updated_by,
       creation_date,
       created_by,
       last_update_login,
       request_id,
       program_application_id,
       program_id,
       program_update_date,
       primary_item_id,
       class_code,
       scheduled_start_date,
       date_closed,
       planned_quantity,
       quantity_completed,
       mps_scheduled_completion_date,
       mps_net_quantity,
       bom_revision,
       routing_revision,
       bom_revision_date,
       routing_revision_date,
       alternate_bom_designator,
       alternate_routing_designator,
       completion_subinventory,
       completion_locator_id,
       material_account,
       material_overhead_account,
       resource_account,
       outside_processing_account,
       material_variance_account,
       resource_variance_account,
       outside_proc_variance_account,
       std_cost_adjustment_account,
       overhead_account,
       overhead_variance_account,
       demand_class,
       scheduled_completion_date,
       schedule_group_id,
       build_sequence,
       line_id,
       project_id,
       task_id,
       status,
       schedule_number,
       attribute_category,
       attribute1,
       attribute2,
       attribute3,
       attribute4,
       attribute5,
       attribute6,
       attribute7,
       attribute8,
       attribute9,
       attribute10,
       attribute11,
       attribute12,
       attribute13,
       attribute14,
       attribute15,
       demand_source_header_id,
       demand_source_line,
       demand_source_delivery,
       demand_source_type,
       kanban_card_id,
       quantity_scrapped
     )
     values
     (
       i_schedules_tbl(l_index).scheduled_flag,
       i_schedules_tbl(l_index).wip_entity_id,
       i_schedules_tbl(l_index).organization_id,
       i_schedules_tbl(l_index).last_update_date,
       i_schedules_tbl(l_index).last_updated_by,
       i_schedules_tbl(l_index).creation_date,
       i_schedules_tbl(l_index).created_by,
       i_schedules_tbl(l_index).last_update_login,
       i_schedules_tbl(l_index).request_id,
       i_schedules_tbl(l_index).program_application_id,
       i_schedules_tbl(l_index).program_id,
       i_schedules_tbl(l_index).program_update_date,
       i_schedules_tbl(l_index).primary_item_id,
       i_schedules_tbl(l_index).class_code,
       i_schedules_tbl(l_index).scheduled_start_date,
       i_schedules_tbl(l_index).date_closed,
       i_schedules_tbl(l_index).planned_quantity,
       i_schedules_tbl(l_index).quantity_completed,
       i_schedules_tbl(l_index).mps_scheduled_completion_date,
       i_schedules_tbl(l_index).mps_net_quantity,
       i_schedules_tbl(l_index).bom_revision,
       i_schedules_tbl(l_index).routing_revision,
       i_schedules_tbl(l_index).bom_revision_date,
       i_schedules_tbl(l_index).routing_revision_date,
       i_schedules_tbl(l_index).alternate_bom_designator,
       i_schedules_tbl(l_index).alternate_routing_designator,
       i_schedules_tbl(l_index).completion_subinventory,
       i_schedules_tbl(l_index).completion_locator_id,
       i_schedules_tbl(l_index).material_account,
       i_schedules_tbl(l_index).material_overhead_account,
       i_schedules_tbl(l_index).resource_account,
       i_schedules_tbl(l_index).outside_processing_account,
       i_schedules_tbl(l_index).material_variance_account,
       i_schedules_tbl(l_index).resource_variance_account,
       i_schedules_tbl(l_index).outside_proc_variance_account,
       i_schedules_tbl(l_index).std_cost_adjustment_account,
       i_schedules_tbl(l_index).overhead_account,
       i_schedules_tbl(l_index).overhead_variance_account,
       i_schedules_tbl(l_index).demand_class,
       i_schedules_tbl(l_index).scheduled_completion_date,
       i_schedules_tbl(l_index).schedule_group_id,
       i_schedules_tbl(l_index).build_sequence,
       i_schedules_tbl(l_index).line_id,
       i_schedules_tbl(l_index).project_id,
       i_schedules_tbl(l_index).task_id,
       i_schedules_tbl(l_index).status,
       i_schedules_tbl(l_index).schedule_number,
       i_schedules_tbl(l_index).attribute_category,
       i_schedules_tbl(l_index).attribute1,
       i_schedules_tbl(l_index).attribute2,
       i_schedules_tbl(l_index).attribute3,
       i_schedules_tbl(l_index).attribute4,
       i_schedules_tbl(l_index).attribute5,
       i_schedules_tbl(l_index).attribute6,
       i_schedules_tbl(l_index).attribute7,
       i_schedules_tbl(l_index).attribute8,
       i_schedules_tbl(l_index).attribute9,
       i_schedules_tbl(l_index).attribute10,
       i_schedules_tbl(l_index).attribute11,
       i_schedules_tbl(l_index).attribute12,
       i_schedules_tbl(l_index).attribute13,
       i_schedules_tbl(l_index).attribute14,
       i_schedules_tbl(l_index).attribute15,
       i_schedules_tbl(l_index).demand_source_header_id,
       i_schedules_tbl(l_index).demand_source_line,
       i_schedules_tbl(l_index).demand_source_delivery,
       i_schedules_tbl(l_index).demand_source_type,
       i_schedules_tbl(l_index).kanban_card_id,
       i_schedules_tbl(l_index).quantity_scrapped
     );

Line: 1287

END insert_schedules;

Line: 1312

    SELECT distinct(ATTRIBUTE_VALUE1_NUM) inventory_item_id
    FROM FLM_SEQ_TASK_CONSTRAINTS
    WHERE SEQ_TASK_ID = p_seq_task_id
      AND ORGANIZATION_ID = p_organization_id
      AND CONSTRAINT_TYPE = 7;

Line: 1319

    SELECT line_id
    FROM FLM_SEQ_TASK_LINES
    WHERE SEQ_TASK_ID = p_seq_task_id;