APPS.EAM_METR_VALIDATOR SQL Statements

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

Line: 8

    select disable_flag into l_disable_flag
    from eam_meter_readings_interface
    where rowid=p_current_rowid;

Line: 18

    last_updated_by_name(p_current_rowid, p_interface_id);

Line: 21

    last_updated_by(p_current_rowid, p_interface_id);

Line: 49

        select reading_value, reading_change, meter_id, reading_date
        into l_current_reading, l_reading_change, l_meter_id, l_reading_date
        from eam_meter_readings_interface
        where rowid=p_current_rowid;

Line: 59

                  select max(current_reading_date) into l_last_reading_date
                  from eam_meter_readings
                  where meter_id=l_meter_id
                  and current_reading_date < l_reading_date
		  and (disable_flag is null or disable_flag = 'N');

Line: 66

                        select current_reading into l_last_reading
                        from eam_meter_readings
                        where meter_id = l_meter_id
                        and current_reading_date=l_last_reading_date
			and (disable_flag is null or disable_flag = 'N');

Line: 77

              update eam_meter_readings_interface
                set reading_value=l_current_reading
                where rowid=p_current_rowid;

Line: 92

        l_last_updated_by number;

Line: 94

        l_last_update_date date;

Line: 97

        select created_by, last_updated_by, creation_date, last_update_date
        into l_created_by, l_last_updated_by, l_creation_date, l_last_update_date
        from eam_meter_readings_interface
        where rowid=p_current_rowid;

Line: 103

           AND l_last_updated_by IS NOT NULL
           AND l_creation_date IS NOT NULL
           AND l_last_update_date IS NOT NULL)
        THEN
            RETURN;

Line: 114

        if (l_last_updated_by is null) then
                l_last_updated_by:=fnd_global.user_id;

Line: 122

        if (l_last_update_date is null) then
                l_last_update_date:=sysdate;

Line: 126

        update eam_meter_readings_interface
        set created_by=l_created_by,
            last_updated_by=l_last_updated_by,
            creation_date=l_creation_date,
            last_update_date=l_last_update_date
  	WHERE rowid = p_current_rowid;

Line: 140

  procedure last_updated_by_name(p_current_rowid in rowid,
                                 p_interface_id in number) is
  begin
    eam_int_utils.derive_id_from_code(
       p_current_rowid,
       p_interface_id,
       'eam_meter_readings_interface mri',
       'LAST_UPDATED_BY',
       'LAST_UPDATED_BY_NAME',
       '(SELECT USER_ID
           FROM FND_USER
          WHERE USER_NAME = mri.LAST_UPDATED_BY_NAME)'
    );

Line: 153

  end last_updated_by_name;

Line: 156

  procedure last_updated_by(p_current_rowid in rowid,
                            p_interface_id in number) is
  begin
    eam_mri_utils.error_if(
          p_current_rowid,
          p_interface_id,
          '((LAST_UPDATED_BY IS NULL AND LAST_UPDATED_BY_NAME IS NULL)
            OR NOT EXISTS
                (SELECT 1
                 FROM FND_USER FU
                 WHERE USER_ID = mri.LAST_UPDATED_BY
                 AND SYSDATE BETWEEN FU.START_DATE AND
                             NVL(FU.END_DATE,SYSDATE+1)))',
          'WIP',
          'WIP_ML_LAST_UPDATED_BY');

Line: 171

  end last_updated_by;

Line: 183

       '(SELECT USER_ID
           FROM FND_USER
          WHERE USER_NAME = mri.LAST_UPDATED_BY_NAME)'
    );

Line: 198

                (SELECT 1
                 FROM FND_USER FU
                 WHERE USER_ID = mri.CREATED_BY
                 AND SYSDATE BETWEEN FU.START_DATE AND
                             NVL(FU.END_DATE,SYSDATE+1)))',
          'WIP',
          'WIP_ML_CREATED_BY');

Line: 213

    select organization_code into x_org_code
    from eam_meter_readings_interface
    where rowid = p_current_rowid;

Line: 222

    select eam_enabled_flag
    into x_eam_enabled
    from mtl_parameters
    where organization_code = x_org_code;

Line: 240

       '(SELECT ORGANIZATION_ID
           FROM ORG_ORGANIZATION_DEFINITIONS
          WHERE ORGANIZATION_CODE = MRI.ORGANIZATION_CODE)',
       FALSE
    );

Line: 259

    select organization_id into x_org_id
      from eam_meter_readings_interface
     where rowid = p_current_rowid;

Line: 267

    select eam_enabled_flag
    into x_eam_enabled
    from mtl_parameters
    where organization_id = x_org_id;

Line: 283

          '(NOT EXISTS (SELECT 1
                          FROM ORG_ORGANIZATION_DEFINITIONS
                         WHERE ORGANIZATION_ID= MRI.ORGANIZATION_ID)
            OR TRUNC(SYSDATE) > (SELECT NVL(DISABLE_DATE, SYSDATE + 1)
                                 FROM  ORG_ORGANIZATION_DEFINITIONS
                                 WHERE ORGANIZATION_ID = MRI.ORGANIZATION_ID)
          )',
          'WIP',
          'WIP_ML_ORGANIZATION_ID');

Line: 307

    select organization_id,
           work_order_name,
           wip_entity_id
      into x_org_id,
           x_work_order_name,
           x_wip_entity_id
      from eam_meter_readings_interface
     where rowid = p_current_rowid;

Line: 331

       '(SELECT WIP_ENTITY_ID
           FROM WIP_ENTITIES
          WHERE ORGANIZATION_ID = MRI.ORGANIZATION_ID
            AND WIP_ENTITY_NAME = MRI.WORK_ORDER_NAME)',
       FALSE
    );

Line: 340

    select wip_entity_id
      into x_wip_entity_id
      from eam_meter_readings_interface
     where rowid = p_current_rowid;

Line: 352

               'exists (select 1
                          from wip_discrete_jobs wdj, eam_asset_meters eam, wip_entities we
                         where wdj.wip_entity_id = mri.wip_entity_id
                         	and wdj.organization_id = mri.organization_id
                         	and we.organization_id = mri.organization_id
                         	and eam.organization_id = mri.organization_id
                         	and eam.asset_number = wdj.asset_number
                         	and eam.asset_group_id = wdj.asset_group_id
                         	and eam.meter_id = mri.meter_id
                         	and we.wip_entity_id = wdj.wip_entity_id)') ) then
      eam_int_utils.record_invalid_column_error(
                      p_interface_id,
                      'WIP_ENTITY_ID');

Line: 381

       '(SELECT METER_ID
           FROM EAM_METERS
          WHERE METER_NAME = MRI.METER_NAME
            AND MRI.READING_DATE BETWEEN
                NVL(FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
                AND NVL(TO_EFFECTIVE_DATE, MRI.READING_DATE+1))'
    );

Line: 395

           '(SELECT METER_NAME
               FROM EAM_METERS
              WHERE METER_ID = MRI.METER_ID
                AND MRI.READING_DATE BETWEEN
                    NVL(FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
                    AND NVL(TO_EFFECTIVE_DATE, MRI.READING_DATE+1))'
    );

Line: 405

      update eam_meter_readings_interface
         set process_status = WIP_CONSTANTS.ERROR,
             process_phase = WIP_CONSTANTS.ML_VALIDATION
       where rowid = p_current_rowid;

Line: 421

    select meter_name into x_meter_name
    from eam_meter_readings_interface
    where rowid = p_current_rowid;

Line: 435

                 (SELECT METER_ID
                    FROM EAM_METERS
                   WHERE METER_NAME = MRI.METER_NAME
                     AND MRI.READING_DATE BETWEEN
                     NVL(FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
                       AND NVL(TO_EFFECTIVE_DATE, MRI.READING_DATE+1)))',
          'EAM',
          'EAM_MR_INVALID_METER');

Line: 446

      		update eam_meter_readings_interface
        	set process_status = WIP_CONSTANTS.ERROR,
        	process_phase = WIP_CONSTANTS.ML_VALIDATION
       		where rowid = p_current_rowid;

Line: 464

             (SELECT 1
                FROM EAM_METERS EM
               WHERE EM.METER_ID = MRI.METER_ID
                 AND MRI.READING_DATE BETWEEN
                     NVL(EM.FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
                      AND NVL(EM.TO_EFFECTIVE_DATE, MRI.READING_DATE+1)))',
          'EAM',
          'EAM_MR_INVALID_READING_DATE'
          );

Line: 476

         '(EXISTS (SELECT 1
                     FROM EAM_METER_READINGS EM
                    WHERE EM.METER_ID = MRI.METER_ID
                      AND EM.CURRENT_READING_DATE = MRI.READING_DATE
		      AND (EM.DISABLE_FLAG IS NULL OR DISABLE_FLAG = ''N'')))',
         'EAM',
         'EAM_SAME_READING_DATE_EXIST'
         );

Line: 501

              (SELECT 1
                 FROM EAM_METER_READINGS MR
                WHERE MR.METER_ID = MRI.METER_ID
                  AND MR.CURRENT_READING_DATE > MRI.READING_DATE
		  AND (MR.DISABLE_FLAG IS NULL OR MR.DISABLE_FLAG=''N''))))',
          'EAM',
          'EAM_MR_RESET_NOT_ALLOWED'
          );

Line: 519

      select meter_id,
             reading_date,
             reset_flag
        into x_meter_id,
             x_reading_date,
             x_reset_flag
        from eam_meter_readings_interface
       where rowid = p_current_rowid;

Line: 532

      select life_to_date_reading
        into x_ltd
        from eam_meter_readings
       where meter_id = x_meter_id
         and current_reading_date =
             (select max(current_reading_date)
               from eam_meter_readings
              where meter_id = x_meter_id
                and current_reading_date < x_reading_date
		and (disable_flag is null or disable_flag='N'))
	 and (disable_flag is null or disable_flag='N');

Line: 553

      update eam_meter_readings_interface
         set life_to_date_reading = x_ltd
       where rowid = p_current_rowid;

Line: 592

    select mri.reading_value,
           mri.life_to_date_reading,
           mri.meter_id,
           nvl(em.meter_type, 1),
           mri.reading_date,
           em.value_change_dir
      into x_reading_value,
           x_life_to_date_reading,
           x_meter_id,
           x_meter_type,
           x_reading_date,
           x_value_change_dir
      from eam_meter_readings_interface mri,
           eam_meters em
     where mri.meter_id = em.meter_id
       and mri.rowid = p_current_rowid;

Line: 617

        select rowid
          into x_pre_rowid
          from eam_meter_readings
         where meter_id = x_meter_id and
               current_reading_date =
               (select max(current_reading_date)
                  from eam_meter_readings
                 where meter_id = x_meter_id
                   and current_reading_date < x_reading_date
		   and (disable_flag is null or disable_flag='N'));

Line: 634

        select current_reading, life_to_date_reading
          into x_pre_reading, x_pre_ltd
          from eam_meter_readings
         where rowid = x_pre_rowid;

Line: 644

        select rowid
          into x_next_rowid
          from eam_meter_readings
         where current_reading_date =
               (select min(current_reading_date)
                  from eam_meter_readings
                 where meter_id = x_meter_id
                   and current_reading_date > x_reading_date
		   and (disable_flag is null or disable_flag = 'N'));

Line: 659

        select current_reading, life_to_date_reading
          into x_next_reading, x_next_ltd
          from eam_meter_readings
         where rowid = x_next_rowid;

Line: 687

        update eam_meter_readings_interface
--           set life_to_date_reading = x_pre_ltd + x_reading_value - x_pre_reading
        set life_to_date_reading = l_ltd
        where rowid = p_current_rowid;

Line: 711

          update eam_meter_readings_interface
             --set life_to_date_reading = x_next_ltd + x_reading_value - x_next_reading
           set life_to_date_reading = l_ltd
           where rowid = p_current_rowid;

Line: 720

        update eam_meter_readings_interface
--           set life_to_date_reading = x_reading_value
        set life_to_date_reading = l_ltd
         where rowid = p_current_rowid;

Line: 765

    select mri.life_to_date_reading,
           mri.reading_date,
           mri.reset_flag,
           mri.meter_id,
           em.value_change_dir
      into x_ltd_reading,
           x_reading_date,
           x_reset_flag,
           x_meter_id,
           x_value_change_dir
      from eam_meter_readings_interface mri,
           eam_meters em
     where mri.meter_id = em.meter_id
       and mri.rowid = p_current_rowid;

Line: 781

      select rowid
        into x_pre_rowid
        from eam_meter_readings
       where current_reading_date =
            (select max(current_reading_date)
               from eam_meter_readings
              where meter_id = x_meter_id
                and current_reading_date < x_reading_date
		and (disable_flag is null or disable_flag='N'));

Line: 796

       select current_reading, life_to_date_reading
         into x_pre_reading, x_pre_ltd
         from eam_meter_readings
        where rowid = x_pre_rowid;

Line: 806

      select rowid
        into x_next_rowid
        from eam_meter_readings
       where current_reading_date =
             (select min(current_reading_date)
                from eam_meter_readings
               where meter_id = x_meter_id
                 and current_reading_date > x_reading_date
	         and (disable_flag is null or disable_flag='N'));

Line: 821

      select life_to_date_reading, current_reading
        into x_next_ltd, x_next_reading
        from eam_meter_readings
       where rowid = x_next_rowid;

Line: 835

        update eam_meter_readings_interface
           set reading_value = x_pre_reading + x_ltd_reading - x_pre_ltd
         where rowid = p_current_rowid;

Line: 848

        update eam_meter_readings_interface
           set reading_value = x_next_reading + x_ltd_reading - x_next_ltd
         where rowid = p_current_rowid;