APPS.EAM_GENEALOGY_IMPORT_PVT SQL Statements

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

Line: 94

      UPDATE  mtl_object_genealogy_interface mogi
      SET     mogi.error_code = 9999,
              mogi.process_status = 'E',
              mogi.error_message = 'Failed to submit worker for Group ID = '||p_group_id
      WHERE   mogi.process_status = 'P'
      AND     mogi.batch_id = p_batch_id
      AND     mogi.group_id = p_group_id;

Line: 140

        SELECT
                mogi.organization_id,
                mogi.inventory_item_id,
                mogi.serial_number,
                count(*) as total
        FROM    mtl_object_genealogy_interface mogi
        WHERE   mogi.batch_id = p_batch_id
        AND     mogi.group_id IS NULL
        AND     mogi.process_status = 'P'
        AND     mogi.error_code IS NULL
        AND     mogi.error_message IS NULL
        GROUP BY
                mogi.organization_id,
                mogi.inventory_item_id,
                mogi.serial_number
        ORDER BY
                mogi.organization_id,
                mogi.inventory_item_id,
                mogi.serial_number;

Line: 178

      UPDATE    mtl_object_genealogy_interface mogi
      SET       mogi.process_status = 'E',
                mogi.error_code = 9999,
                mogi.error_message = 'Incorrect Mode Value'
      WHERE     mogi.batch_id = p_batch_id
      AND       mogi.import_mode NOT IN (0,1)
      AND       mogi.process_status = 'P';

Line: 192

    UPDATE MTL_OBJECT_GENEALOGY_INTERFACE
    SET process_status = 'E',
    error_code = 9999,
    error_message = 'Incorrent Genealogy Origin'
    WHERE batch_id = p_batch_id
    and   genealogy_origin <> 3
    and   process_status = 'P';

Line: 205

    UPDATE MTL_OBJECT_GENEALOGY_INTERFACE
    SET process_status = 'E',
    error_code = 9999,
    error_message = 'Incorrent Object Type'
    WHERE batch_id = p_batch_id
    and   (object_type <> 2
    or    parent_object_type <> 2)
    and   process_status = 'P';

Line: 221

    UPDATE MTL_OBJECT_GENEALOGY_INTERFACE
    SET process_status = 'E',
    error_code = 9999,
    error_message = 'Incorrent Genealogy Type'
    WHERE batch_id = p_batch_id
    and   genealogy_type <> 5
    and   process_status = 'P';

Line: 234

    UPDATE    mtl_object_genealogy_interface mogi
    SET       process_status = 'E' , error_code = 9999, error_message = 'Invalid Organization. Organization and Parent Organization have to be the same.'
    WHERE     batch_id = p_batch_id
    AND       ( select mp.maint_organization_id from mtl_parameters mp
               where mp.organization_code = mogi.organization_code )
	       <>
	      ( select mp.maint_organization_id from mtl_parameters mp
               where mp.organization_code = mogi.parent_organization_code )
    AND       process_status = 'P';

Line: 255

    UPDATE    mtl_object_genealogy_interface mogi
    SET       organization_code= (select organization_code from mtl_parameters
               where organization_id = mogi.organization_id
	       and maint_organization_id is not null)
    WHERE     batch_id = p_batch_id
    AND       organization_id is not null
    AND       process_status = 'P';

Line: 263

    UPDATE   mtl_object_genealogy_interface mogi
    SET       organization_id= (select organization_id from mtl_parameters
               where organization_code = mogi.organization_code
       	       and maint_organization_id is not null)
    WHERE     batch_id = p_batch_id
    AND       organization_id is null
    AND       organization_code is not null
    AND       process_status = 'P';

Line: 272

    UPDATE    mtl_object_genealogy_interface mogi
    SET       process_status = 'E' , error_code = 9999, error_message = 'Invalid Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
    WHERE     batch_id = p_batch_id
    AND       (organization_id is null
               OR  organization_code is null)
    AND       process_status = 'P';

Line: 289

    UPDATE    mtl_object_genealogy_interface mogi
    SET       parent_organization_code= (select organization_code from mtl_parameters
               where organization_id = mogi.parent_organization_id
	       and maint_organization_id is not null)
    WHERE     batch_id = p_batch_id
    AND       parent_organization_id is not null
    AND       process_status = 'P';

Line: 297

    UPDATE   mtl_object_genealogy_interface mogi
    SET       parent_organization_id= (select organization_id from mtl_parameters
               where organization_code = mogi.parent_organization_code
	       and maint_organization_id is not null)
    WHERE     batch_id = p_batch_id
    AND       parent_organization_id is null
    AND       parent_organization_code is not null
    AND       process_status = 'P';

Line: 306

    UPDATE    mtl_object_genealogy_interface mogi
    SET       process_status = 'E' , error_code = 9999, error_message = 'Invalid Parent Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
    WHERE     batch_id = p_batch_id
    AND       (parent_organization_id is null
               OR  parent_organization_code is null)
    AND       process_status = 'P';

Line: 324

    UPDATE    mtl_object_genealogy_interface mogi
    SET       object_id = (select gen_object_id from mtl_serial_numbers
               where current_organization_id = mogi.organization_id
               and   inventory_item_id = mogi.inventory_item_id
               and   serial_number = mogi.serial_number)
    WHERE     batch_id = p_batch_id
    AND       process_status = 'P';

Line: 332

    UPDATE    mtl_object_genealogy_interface mogi
    SET       parent_object_id = (select gen_object_id from mtl_serial_numbers
               where current_organization_id = mogi.parent_organization_id
               and   inventory_item_id = mogi.parent_inventory_item_id
               and   serial_number = mogi.parent_serial_number)
    WHERE     batch_id = p_batch_id
    AND       process_status = 'P';

Line: 340

    UPDATE    mtl_object_genealogy_interface mogi
    SET       process_status = 'E' , error_code = 9999, error_message = 'Invalid Serial Number. Check that it exists in the organization.'
    WHERE     batch_id = p_batch_id
    AND       (object_id is null
               OR  parent_object_id is null)
    AND       process_status = 'P';

Line: 358

        SELECT MTL_OBJECT_GEN_INTERFACE_S.nextval
        INTO  l_group_id
        FROM  dual;

Line: 364

    UPDATE  mtl_object_genealogy_interface mogi
    SET     mogi.group_id = l_group_id,
            mogi.process_status = 'R'
    WHERE   mogi.process_status = 'P'
    AND     mogi.organization_id = genealogy.organization_id
    AND     mogi.inventory_item_id = genealogy.inventory_item_id
    AND     mogi.serial_number = genealogy.serial_number
    AND     mogi.batch_id = p_batch_id
    AND     mogi.group_id IS NULL;