DBA Data[Home] [Help]

APPS.WIP_EAM_UTILS SQL Statements

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

Line: 63

      select inventory_item_id into l_inv_item_id
       from mtl_system_items_b_kfv msi, mtl_parameters mp
        where msi.concatenated_segments = p_asset_group
        and msi.organization_id = mp.organization_id
        and mp.maint_organization_id = p_org_id
        and rownum = 1;
Line: 72

            select cii.instance_id
            into l_instance_id
            from csi_item_instances cii
            where cii.inventory_item_id = l_inv_item_id
            and cii.serial_number = p_serial_number;
Line: 94

      select wip_entity_id into l_parent_weid from wip_entities
        where wip_entity_name = p_parent_wo_name
        and organization_id = p_org_id;
Line: 101

      select inventory_item_id into l_asset_activity_id from
        mtl_system_items_b_kfv where
        concatenated_segments = p_asset_activity
        and organization_id = p_org_id;
Line: 109

      select ppv.project_id into l_project_id
        from pjm_projects_v ppv,
        pjm_project_parameters ppp
        where ppv.project_id = ppp.project_id
        and ppp.organization_id = p_org_id
        and ppv.project_number = p_project_number;
Line: 118

      select /*+  leading( ppv ) */ ppv.project_id, mtv.task_id into
        l_project_id, l_task_id
        from  pjm_projects_v ppv,
        pjm_project_parameters ppp,
        mtl_task_v mtv
        where  ppv.project_id = ppp.project_id
        and mtv.project_id(+) = ppp.project_id
        and ppp.organization_id = p_org_id
        and ppv.project_number = p_project_number
        and task_number = p_task_number;
Line: 155

          select count(*)
          into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
          where meaa.asset_activity_id = l_asset_activity_id
          and meaa.maintenance_object_type = 3
          and meaa.maintenance_object_id = l_instance_id
          and eomd.organization_id = p_org_id
          and meaa.activity_association_id = eomd.object_id
          and eomd.object_type = 60
          and eomd.accounting_class_code is not null
          and nvl(meaa.tmpl_flag, 'N') = 'N';
Line: 167

          select accounting_class_code into x_class_code from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
           where meaa.asset_activity_id = l_asset_activity_id
          and meaa.maintenance_object_type = 3
          and meaa.maintenance_object_id = l_instance_id
          and eomd.organization_id = p_org_id
          and meaa.activity_association_id = eomd.object_id
          and eomd.object_type = 60
          and nvl(meaa.tmpl_flag, 'N') = 'N';
Line: 181

        select count(*)
        into l_count
        from eam_org_maint_defaults eomd
        where eomd.organization_id = p_org_id
        and eomd.object_type = 50
        and eomd.object_id = l_instance_id
        and eomd.accounting_class_code is not null;
Line: 189

        select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
        where eomd.organization_id = p_org_id
        and eomd.object_type = 50
        and eomd.object_id = l_instance_id;
Line: 197

      select default_eam_class into x_class_code
        from wip_eam_parameters where
        organization_id = p_org_id;
Line: 207

        select  class_code into x_class_code from
          wip_discrete_jobs where
          wip_entity_id = l_parent_weid
          and organization_id = p_org_id;
Line: 236

          select count(*)
          into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
          where meaa.asset_activity_id = l_asset_activity_id
          and meaa.maintenance_object_type = l_maintenance_object_type
          and meaa.maintenance_object_id = l_instance_id
          and eomd.organization_id = p_org_id
          and meaa.activity_association_id = eomd.object_id
          and eomd.object_type = decode(l_maintenance_object_type, 3, 60, 2, 40 )
          and nvl(meaa.tmpl_flag, 'N') = 'N'
          and eomd.accounting_class_code is not null;
Line: 248

           select accounting_class_code into x_class_code
		   from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
          where meaa.asset_activity_id = l_asset_activity_id
          and meaa.maintenance_object_type = l_maintenance_object_type
          and meaa.maintenance_object_id = l_instance_id
          and eomd.organization_id = p_org_id
          and nvl(meaa.tmpl_flag, 'N') = 'N'
          and meaa.activity_association_id = eomd.object_id
          and eomd.object_type = decode(l_maintenance_object_type, 3, 60, 2, 40
);
Line: 266

        select count(*)
        into l_count
        from eam_org_maint_defaults eomd
        where eomd.organization_id = p_org_id
        and eomd.object_type = 50
        and eomd.object_id = l_instance_id
        and eomd.accounting_class_code is not null;
Line: 275

                select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
                where eomd.organization_id = p_org_id
                and eomd.object_type = 50
                and eomd.object_id = l_instance_id;
Line: 285

        select default_eam_class into x_class_code
          from wip_eam_parameters where
          organization_id = p_org_id;
Line: 298

    select default_eam_class into x_class_code
      from wip_eam_parameters where
      organization_id = p_org_id;
Line: 310

      select default_eam_class into x_class_code
        from wip_eam_parameters where
        organization_id = p_org_id;
Line: 369

            select cii.instance_id
            into l_instance_id
            from csi_item_instances cii
            where cii.inventory_item_id = p_asset_group_id
            and cii.serial_number = p_serial_number;
Line: 408

          select count(*)
          into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
          where meaa.asset_activity_id = l_asset_activity_id
          and meaa.maintenance_object_type = 3
          and meaa.maintenance_object_id = l_instance_id
          and nvl(meaa.tmpl_flag, 'N') = 'N'
          and eomd.organization_id = p_org_id
          and meaa.activity_association_id = eomd.object_id
          and eomd.object_type = 60
          and eomd.accounting_class_code is not null ;
Line: 420

           select accounting_class_code into x_class_code
		   from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
           where meaa.asset_activity_id = l_asset_activity_id
           and meaa.maintenance_object_type = 3
           and meaa.maintenance_object_id = l_instance_id
           and nvl(meaa.tmpl_flag, 'N') = 'N'
           and eomd.organization_id = p_org_id
           and meaa.activity_association_id = eomd.object_id
           and eomd.object_type = 60;
Line: 435

        select count(*)
        into l_count
        from eam_org_maint_defaults eomd
        where eomd.organization_id = p_org_id
        and eomd.object_type = 50
        and eomd.object_id = l_instance_id
        and eomd.accounting_class_code is not null;
Line: 444

        select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
                where eomd.organization_id = p_org_id
                and eomd.object_type = 50
                and eomd.object_id = l_instance_id;
Line: 452

      select default_eam_class into x_class_code
        from wip_eam_parameters where
        organization_id = p_org_id;
Line: 462

        select  class_code into x_class_code from
          wip_discrete_jobs where
          wip_entity_id = l_parent_weid
          and organization_id = p_org_id;
Line: 490

         select count(*)
          into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
          where meaa.asset_activity_id = l_asset_activity_id
          and meaa.maintenance_object_type = l_maintenance_object_type
          and meaa.maintenance_object_id = l_instance_id
          and eomd.organization_id = p_org_id
          and meaa.activity_association_id = eomd.object_id
          and eomd.object_type in (40, 60)
          and nvl(meaa.tmpl_flag, 'N') = 'N'
          and eomd.accounting_class_code is not null ;
Line: 502

            select accounting_class_code into x_class_code from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
          where meaa.asset_activity_id = l_asset_activity_id
          and meaa.maintenance_object_type = l_maintenance_object_type
          and meaa.maintenance_object_id = l_instance_id
          and nvl(meaa.tmpl_flag, 'N') = 'N'
          and eomd.organization_id = p_org_id
          and meaa.activity_association_id = eomd.object_id
          and eomd.object_type in (40, 60);
Line: 518

        select count(*)
        into l_count
        from eam_org_maint_defaults eomd
        where eomd.organization_id = p_org_id
        and eomd.object_type = 50
        and eomd.object_id = l_instance_id
        and eomd.accounting_class_code is not null;
Line: 526

                select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
                where eomd.organization_id = p_org_id
                and eomd.object_type = 50
                and eomd.object_id = l_instance_id;
Line: 538

    select default_eam_class into x_class_code
      from wip_eam_parameters where
      organization_id = p_org_id;
Line: 550

      select default_eam_class into x_class_code
        from wip_eam_parameters where
        organization_id = p_org_id;
Line: 585

   cursor op_table is select operation_sequence_id, operation_seq_num
     from bom_operation_sequences
     where routing_sequence_id = p_common_routing_sequence_id ;
Line: 601

      select nvl(asset_number,rebuild_serial_number), nvl(asset_group_id,rebuild_item_id)
             into l_asset_number, l_inv_item_id
             from wip_discrete_jobs
             where wip_entity_id = p_wip_entity_id
             and organization_id = p_organization_id;
Line: 626

                X_last_update_login     =>  fnd_global.login_id
                -- X_program_application_id=>  '',
                -- X_program_id            =>  '',
                -- X_request_id            =>  ''
             );
Line: 657

                X_last_update_login     =>  fnd_global.login_id
                -- X_program_application_id=>  '',
                -- X_program_id            =>  '',
                -- X_request_id            =>  ''
                 );
Line: 687

                X_last_update_login     =>  fnd_global.login_id
                -- X_program_application_id=>  '',
                -- X_program_id            =>  '',
                -- X_request_id            =>  ''
                 );
Line: 715

                X_last_update_login     =>  fnd_global.login_id
                -- X_program_application_id=>  '',
                -- X_program_id            =>  '',
                -- X_request_id            =>  ''
                 );
Line: 749

                X_last_update_login     =>  fnd_global.login_id
                -- X_program_application_id=>  '',
                -- X_program_id            =>  '',
                -- X_request_id            =>  ''
                 );
Line: 805

    SELECT  nvl(COUNT(*),0)
    into    l_operation_exist
    FROM    WIP_OPERATIONS WO
    WHERE   WO.WIP_ENTITY_ID = l_wip_entity_id;
Line: 813

    select scheduled_start_date,
           scheduled_completion_date,
           owning_department
    into   l_start_date,
           l_completion_date,
           l_owning_department_id
    from wip_discrete_jobs
    where wip_entity_id = l_wip_entity_id
    and organization_id = l_organization_id;
Line: 825

/*    select distinct msn.owning_department_id
    into l_owning_department_id
    from wip_discrete_jobs wdj,mtl_serial_numbers msn
    where wdj.asset_group_id  = msn.inventory_item_id (+)
    and wdj.organization_id = msn.current_organization_id (+)
    and wdj.asset_number  = msn.serial_number (+)
    and wdj.wip_entity_id = l_wip_entity_id
    and wdj.organization_id = l_organization_id;*/
Line: 834

    select eomd.owning_department_id
    into l_owning_department_id
    from eam_org_maint_defaults eomd, wip_discrete_jobs wdj
    where wdj.maintenance_object_type = 3
    and wdj.organization_id = eomd.organization_id (+)
    and eomd.object_type (+) = 50
    and eomd.object_id (+) = wdj.maintenance_object_id
    and wdj.wip_entity_id = l_wip_entity_id
    and wdj.organization_id = l_organization_id;
Line: 848

    insert into wip_operations
    (
       wip_entity_id
      ,operation_seq_num
      ,organization_id
      ,repetitive_schedule_id
      ,last_update_date
      ,last_updated_by
      ,creation_date
      ,created_by
      ,last_update_login
      ,operation_sequence_id
      ,standard_operation_id
      ,department_id
      ,description
      ,scheduled_quantity
      ,quantity_in_queue
      ,quantity_running
      ,quantity_waiting_to_move
      ,quantity_rejected
      ,quantity_scrapped
      ,quantity_completed
      ,first_unit_start_date
      ,first_unit_completion_date
      ,last_unit_start_date
      ,last_unit_completion_date
      ,previous_operation_seq_num
      ,next_operation_seq_num
      ,count_point_type
      ,backflush_flag
      ,minimum_transfer_quantity
      ,date_last_moved
      ,wf_itemtype
      ,wf_itemkey
      ,operation_yield
      ,operation_yield_enabled
      ,pre_split_quantity
      ,operation_completed
      ,shutdown_type
      ,x_pos
      ,y_pos
    )
    values
    (
       l_wip_entity_id
      ,10
      ,l_organization_id
      ,null  -- repetitive schedule id
      ,sysdate  -- last_update_date
      ,FND_GLOBAL.USER_ID
      ,sysdate  -- creation_date
      ,FND_GLOBAL.USER_ID
      ,FND_GLOBAL.LOGIN_ID
      ,null  -- operation_sequence_id
      ,null  -- standard_operation_id
      ,l_owning_department_id
      ,l_description
      ,1  -- scheduled_quantity
      ,1  -- quantity_in_queue
      ,1  -- quantity_running
      ,1  -- quantity_waiting_to_move
      ,0  -- quantity_rejected
      ,1  -- quantity_scrapped
      ,1  -- quantity_completed
      ,l_start_date
      ,l_completion_date
      ,l_start_date
      ,l_completion_date
      ,null -- previous_operation_seq_num
      ,null -- next_operation_seq_num
      ,1  -- count_point_type
      ,1  -- backflush_flag
      ,1  -- minimum_transfer_quantity
      ,null -- date_last_moved
      ,null  -- wf_itemtype
      ,null  -- wf_itemkey
      ,null  -- operation_yield
      ,null  -- operation_yield_enabled
      ,null  -- pre_split_quantity
      ,null  -- operation_completed
      ,null  -- shutdown_type
      ,null  -- x_pos
      ,null  -- y_pos
    );
Line: 942

     select count(*)
     into l_count
     from wip_requirement_operations_v
     where organization_id = p_organization_id
     and  wip_entity_id = p_wip_entity_id
     and  operation_seq_num = 1;
Line: 950

       select min(operation_seq_num)
       into l_min_op_seq_num
      from wip_operations
     where    organization_id = p_organization_id and
              wip_entity_id = p_wip_entity_id;
Line: 957

        select department_id into l_department_id
      from wip_operations
     where    organization_id = p_organization_id and
              wip_entity_id = p_wip_entity_id
          and   operation_seq_num = l_min_op_seq_num;
Line: 964

       update wip_requirement_operations
              set operation_seq_num = l_min_op_seq_num,
                 department_id = l_department_id
          where operation_seq_num = 1 and
              organization_id = p_organization_id and
              wip_entity_id = p_wip_entity_id;
Line: 974

     select count(*)
     into l_count
     from wip_eam_direct_items
     where organization_id = p_organization_id
     and  wip_entity_id = p_wip_entity_id
     and  operation_seq_num = 1;
Line: 982

       select min(operation_seq_num)
       into l_min_op_seq_num
      from wip_operations
     where    organization_id = p_organization_id and
              wip_entity_id = p_wip_entity_id;
Line: 989

        select department_id into l_department_id
      from wip_operations
     where    organization_id = p_organization_id and
              wip_entity_id = p_wip_entity_id
          and   operation_seq_num = l_min_op_seq_num;
Line: 996

       update wip_eam_direct_items
              set operation_seq_num = l_min_op_seq_num,
                 department_id = l_department_id
          where operation_seq_num = 1 and
              organization_id = p_organization_id and
              wip_entity_id = p_wip_entity_id;