eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_FMP_EFFECTIVITY_DTL_PVT SQL Statements

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

Line: 314


SELECT  DECODE( relationship_id, null,
                                 inventory_item_id,
                                 position_inventory_item_id ),
        DECODE( relationship_id, null,
                                 item_number,
                                 position_item_number ),
        relationship_id,
        position_ref_meaning
FROM    AHL_MR_EFFECTIVITIES_V
WHERE   mr_effectivity_id = c_mr_effectivity_id;

Line: 435


SELECT  DECODE( relationship_id, null,
                                 inventory_item_id,
                                 position_inventory_item_id ),
        DECODE( relationship_id, null,
                                 item_number,
                                 position_item_number ),
        relationship_id,
        position_ref_meaning
FROM    AHL_MR_EFFECTIVITIES_V
WHERE   mr_effectivity_id = c_mr_effectivity_id;

Line: 573


  p_x_effectivity_detail_rec.last_update_date := SYSDATE;

Line: 574


  p_x_effectivity_detail_rec.last_updated_by := FND_GLOBAL.user_id;

Line: 575


  p_x_effectivity_detail_rec.last_update_login := FND_GLOBAL.login_id;

Line: 594


  p_x_effty_ext_detail_rec.last_update_date := SYSDATE;

Line: 595


  p_x_effty_ext_detail_rec.last_updated_by := FND_GLOBAL.user_id;

Line: 596


  p_x_effty_ext_detail_rec.last_update_login := FND_GLOBAL.login_id;

Line: 829


SELECT  exclude_flag,
        serial_number_from,
        serial_number_to,
        manufacturer_id,
        manufacturer,
        manufacture_date_from,
        manufacture_date_to,
        country_code,
        country,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15
FROM    AHL_MR_EFFECTIVITY_DTLS_V
WHERE   mr_effectivity_detail_id = c_mr_effectivity_detail_id;

Line: 1061


SELECT
        EFFECT_EXT_DTL_REC_TYPE,
        EXCLUDE_FLAG,
        OWNER_ID,
        LOCATION_TYPE_CODE,
        CSI_EXT_ATTRIBUTE_CODE,
        CSI_EXT_ATTRIBUTE_VALUE,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15
FROM    AHL_MR_EFFECTIVITY_EXT_DTLS
WHERE   MR_EFFECTIVITY_EXT_DTL_ID = c_mr_effectivity_ext_dtl_id;

Line: 1541


SELECT   serial_number_from,
         serial_number_to,
         manufacturer,
         manufacture_date_from,
         manufacture_date_to,
         country
FROM     AHL_MR_EFFECTIVITY_DTLS_V
WHERE    mr_effectivity_id = c_mr_effectivity_id
GROUP BY serial_number_from,
         serial_number_to,
         manufacturer,
         manufacture_date_from,
         manufacture_date_to,
         country
HAVING   count(*) > 1;

Line: 1559


SELECT   EFFEXT.OWNER_ID,
         OWN.owner_number
         ,EFFEXT.EFFECT_EXT_DTL_REC_TYPE
FROM    AHL_MR_EFFECTIVITY_EXT_DTLS EFFEXT, ahl_owner_details_v OWN
WHERE    EFFEXT.mr_effectivity_id = c_mr_effectivity_id
AND      EFFEXT.OWNER_ID = OWN.owner_id
AND      EFFECT_EXT_DTL_REC_TYPE = 'OWNER'
GROUP BY EFFEXT.OWNER_ID,
         OWN.owner_number,
         EFFEXT.EFFECT_EXT_DTL_REC_TYPE
HAVING   count(*) > 1;

Line: 1573


SELECT   EFFEXT.LOCATION_TYPE_CODE,
         CS.meaning
         ,EFFEXT.EFFECT_EXT_DTL_REC_TYPE
FROM     AHL_MR_EFFECTIVITY_EXT_DTLS EFFEXT, csi_lookups CS
WHERE    EFFEXT.mr_effectivity_id = c_mr_effectivity_id
and CS.lookup_type='CSI_INST_LOCATION_SOURCE_CODE' and CS.lookup_code = EFFEXT.LOCATION_TYPE_CODE
AND      EFFECT_EXT_DTL_REC_TYPE = 'LOCATION'
GROUP BY EFFEXT.LOCATION_TYPE_CODE,
         CS.meaning,
         EFFEXT.EFFECT_EXT_DTL_REC_TYPE
HAVING   count(*) > 1;

Line: 1588


SELECT   EFFEXT.CSI_EXT_ATTRIBUTE_CODE
         , EFFEXT.CSI_EXT_ATTRIBUTE_VALUE
         ,EFFEXT.EFFECT_EXT_DTL_REC_TYPE
         ,(Select CIEA.ATTRIBUTE_NAME from CSI_I_EXTENDED_ATTRIBS CIEA
          WHERE CIEA.ATTRIBUTE_CODE = EFFEXT.CSI_EXT_ATTRIBUTE_CODE AND rownum < 2) CSI_EXT_ATTRIBUTE_NAME
FROM    AHL_MR_EFFECTIVITY_EXT_DTLS EFFEXT
WHERE    EFFEXT.mr_effectivity_id = c_mr_effectivity_id
AND     EFFEXT.EFFECT_EXT_DTL_REC_TYPE = 'CSIEXTATTR'
GROUP BY EFFEXT.CSI_EXT_ATTRIBUTE_CODE,
         EFFEXT.CSI_EXT_ATTRIBUTE_VALUE,
         EFFEXT.EFFECT_EXT_DTL_REC_TYPE
HAVING   count(*) > 1;

Line: 1731


SELECT   serial_number_from,
         serial_number_to,
         MR_EFFECTIVITY_DETAIL_ID
FROM     AHL_MR_EFFECTIVITY_DTLS_V
WHERE    mr_effectivity_id = c_mr_effectivity_id;

Line: 1740


select distinct MIN(CSI.serial_number) , MAX(CSI.serial_number)
from csi_item_instances CSI,
AHL_MR_EFFECTIVITIES EFF
where
EFF.MR_EFFECTIVITY_ID  = c_mr_effectivity_id and
CSI.inventory_item_id = EFF.inventory_item_id
UNION
select distinct MIN(CSI.serial_number) , MAX(CSI.serial_number)
from csi_item_instances CSI,
ahl_position_alternates_v PA,
AHL_MR_EFFECTIVITIES EFF
where
EFF.MR_EFFECTIVITY_ID  = c_mr_effectivity_id  and
EFF.RELATIONSHIP_ID = PA.relationship_id and
CSI.inventory_item_id = PA.inventory_item_id;

Line: 2039


      INSERT INTO AHL_MR_EFFECTIVITY_DTLS
      (
        MR_EFFECTIVITY_DETAIL_ID,
        OBJECT_VERSION_NUMBER,
        MR_EFFECTIVITY_ID,
        EXCLUDE_FLAG,
        SERIAL_NUMBER_FROM,
        SERIAL_NUMBER_TO,
        MANUFACTURER_ID,
        MANUFACTURE_DATE_FROM,
        MANUFACTURE_DATE_TO,
        COUNTRY_CODE,
        ATTRIBUTE_CATEGORY,
        ATTRIBUTE1,
        ATTRIBUTE2,
        ATTRIBUTE3,
        ATTRIBUTE4,
        ATTRIBUTE5,
        ATTRIBUTE6,
        ATTRIBUTE7,
        ATTRIBUTE8,
        ATTRIBUTE9,
        ATTRIBUTE10,
        ATTRIBUTE11,
        ATTRIBUTE12,
        ATTRIBUTE13,
        ATTRIBUTE14,
        ATTRIBUTE15,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN
      ) VALUES
      (
        AHL_MR_EFFECTIVITY_DTLS_S.NEXTVAL,
        p_x_effectivity_detail_tbl(i).object_version_number,
        p_mr_effectivity_id,
        p_x_effectivity_detail_tbl(i).exclude_flag,
        p_x_effectivity_detail_tbl(i).serial_number_from,
        p_x_effectivity_detail_tbl(i).serial_number_to,
        p_x_effectivity_detail_tbl(i).manufacturer_id,
        p_x_effectivity_detail_tbl(i).manufacture_date_from,
        p_x_effectivity_detail_tbl(i).manufacture_date_to,
        p_x_effectivity_detail_tbl(i).country_code,
        p_x_effectivity_detail_tbl(i).attribute_category,
        p_x_effectivity_detail_tbl(i).attribute1,
        p_x_effectivity_detail_tbl(i).attribute2,
        p_x_effectivity_detail_tbl(i).attribute3,
        p_x_effectivity_detail_tbl(i).attribute4,
        p_x_effectivity_detail_tbl(i).attribute5,
        p_x_effectivity_detail_tbl(i).attribute6,
        p_x_effectivity_detail_tbl(i).attribute7,
        p_x_effectivity_detail_tbl(i).attribute8,
        p_x_effectivity_detail_tbl(i).attribute9,
        p_x_effectivity_detail_tbl(i).attribute10,
        p_x_effectivity_detail_tbl(i).attribute11,
        p_x_effectivity_detail_tbl(i).attribute12,
        p_x_effectivity_detail_tbl(i).attribute13,
        p_x_effectivity_detail_tbl(i).attribute14,
        p_x_effectivity_detail_tbl(i).attribute15,
        p_x_effectivity_detail_tbl(i).last_update_date,
        p_x_effectivity_detail_tbl(i).last_updated_by,
        p_x_effectivity_detail_tbl(i).creation_date,
        p_x_effectivity_detail_tbl(i).created_by,
        p_x_effectivity_detail_tbl(i).last_update_login
      ) RETURNING mr_effectivity_detail_id INTO l_mr_effectivity_detail_id;

Line: 2113


      UPDATE AHL_MR_EFFECTIVITY_DTLS SET
        object_version_number = object_version_number + 1,
        exclude_flag          = p_x_effectivity_detail_tbl(i).exclude_flag,
        serial_number_from    = p_x_effectivity_detail_tbl(i).serial_number_from,
        serial_number_to      = p_x_effectivity_detail_tbl(i).serial_number_to,
        manufacturer_id       = p_x_effectivity_detail_tbl(i).manufacturer_id,
        manufacture_date_from = p_x_effectivity_detail_tbl(i).manufacture_date_from,
        manufacture_date_to   = p_x_effectivity_detail_tbl(i).manufacture_date_to,
        country_code          = p_x_effectivity_detail_tbl(i).country_code,
        attribute_category    = p_x_effectivity_detail_tbl(i).attribute_category,
        attribute1            = p_x_effectivity_detail_tbl(i).attribute1,
        attribute2            = p_x_effectivity_detail_tbl(i).attribute2,
        attribute3            = p_x_effectivity_detail_tbl(i).attribute3,
        attribute4            = p_x_effectivity_detail_tbl(i).attribute4,
        attribute5            = p_x_effectivity_detail_tbl(i).attribute5,
        attribute6            = p_x_effectivity_detail_tbl(i).attribute6,
        attribute7            = p_x_effectivity_detail_tbl(i).attribute7,
        attribute8            = p_x_effectivity_detail_tbl(i).attribute8,
        attribute9            = p_x_effectivity_detail_tbl(i).attribute9,
        attribute10           = p_x_effectivity_detail_tbl(i).attribute10,
        attribute11           = p_x_effectivity_detail_tbl(i).attribute11,
        attribute12           = p_x_effectivity_detail_tbl(i).attribute12,
        attribute13           = p_x_effectivity_detail_tbl(i).attribute13,
        attribute14           = p_x_effectivity_detail_tbl(i).attribute14,
        attribute15           = p_x_effectivity_detail_tbl(i).attribute15,
        last_update_date      = p_x_effectivity_detail_tbl(i).last_update_date,
        last_updated_by       = p_x_effectivity_detail_tbl(i).last_updated_by,
        last_update_login     = p_x_effectivity_detail_tbl(i).last_update_login
      WHERE mr_effectivity_detail_id  = p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id
      AND   object_version_number     = p_x_effectivity_detail_tbl(i).object_version_number;

Line: 2158


      DELETE AHL_MR_EFFECTIVITY_DTLS
      WHERE mr_effectivity_detail_id = p_x_effectivity_detail_tbl(i).mr_effectivity_detail_id
      AND   object_version_number    = p_x_effectivity_detail_tbl(i).object_version_number;

Line: 2247


      INSERT INTO AHL_MR_EFFECTIVITY_EXT_DTLS
      (
        MR_EFFECTIVITY_EXT_DTL_ID,
        OBJECT_VERSION_NUMBER,
        MR_EFFECTIVITY_ID,
        EXCLUDE_FLAG,
        EFFECT_EXT_DTL_REC_TYPE,
        OWNER_ID,
        LOCATION_TYPE_CODE,
        CSI_EXT_ATTRIBUTE_CODE,
        CSI_EXT_ATTRIBUTE_VALUE,
        ATTRIBUTE_CATEGORY,
        ATTRIBUTE1,
        ATTRIBUTE2,
        ATTRIBUTE3,
        ATTRIBUTE4,
        ATTRIBUTE5,
        ATTRIBUTE6,
        ATTRIBUTE7,
        ATTRIBUTE8,
        ATTRIBUTE9,
        ATTRIBUTE10,
        ATTRIBUTE11,
        ATTRIBUTE12,
        ATTRIBUTE13,
        ATTRIBUTE14,
        ATTRIBUTE15,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN
      ) VALUES
      (
        AHL_MR_EFFECTIVITY_EXT_DTLS_S.NEXTVAL,
        p_x_effty_ext_detail_tbl(i).object_version_number,
        p_mr_effectivity_id,
        p_x_effty_ext_detail_tbl(i).exclude_flag,
        p_x_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE,
        p_x_effty_ext_detail_tbl(i).OWNER_ID,
        p_x_effty_ext_detail_tbl(i).LOCATION_TYPE_CODE,
        p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_CODE,
        p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_VALUE,
        p_x_effty_ext_detail_tbl(i).attribute_category,
        p_x_effty_ext_detail_tbl(i).attribute1,
        p_x_effty_ext_detail_tbl(i).attribute2,
        p_x_effty_ext_detail_tbl(i).attribute3,
        p_x_effty_ext_detail_tbl(i).attribute4,
        p_x_effty_ext_detail_tbl(i).attribute5,
        p_x_effty_ext_detail_tbl(i).attribute6,
        p_x_effty_ext_detail_tbl(i).attribute7,
        p_x_effty_ext_detail_tbl(i).attribute8,
        p_x_effty_ext_detail_tbl(i).attribute9,
        p_x_effty_ext_detail_tbl(i).attribute10,
        p_x_effty_ext_detail_tbl(i).attribute11,
        p_x_effty_ext_detail_tbl(i).attribute12,
        p_x_effty_ext_detail_tbl(i).attribute13,
        p_x_effty_ext_detail_tbl(i).attribute14,
        p_x_effty_ext_detail_tbl(i).attribute15,
        p_x_effty_ext_detail_tbl(i).last_update_date,
        p_x_effty_ext_detail_tbl(i).last_updated_by,
        p_x_effty_ext_detail_tbl(i).creation_date,
        p_x_effty_ext_detail_tbl(i).created_by,
        p_x_effty_ext_detail_tbl(i).last_update_login
      ) RETURNING MR_EFFECTIVITY_EXT_DTL_ID INTO l_MR_EFFECTIVITY_EXT_DTL_ID;

Line: 2319


      UPDATE AHL_MR_EFFECTIVITY_EXT_DTLS SET
        object_version_number = object_version_number + 1,
        exclude_flag          = p_x_effty_ext_detail_tbl(i).exclude_flag,
        EFFECT_EXT_DTL_REC_TYPE    = p_x_effty_ext_detail_tbl(i).EFFECT_EXT_DTL_REC_TYPE,
        OWNER_ID      = p_x_effty_ext_detail_tbl(i).OWNER_ID,
        LOCATION_TYPE_CODE       = p_x_effty_ext_detail_tbl(i).LOCATION_TYPE_CODE,
        CSI_EXT_ATTRIBUTE_CODE = p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_CODE,
        CSI_EXT_ATTRIBUTE_VALUE   = p_x_effty_ext_detail_tbl(i).CSI_EXT_ATTRIBUTE_VALUE,
        attribute_category    = p_x_effty_ext_detail_tbl(i).attribute_category,
        attribute1            = p_x_effty_ext_detail_tbl(i).attribute1,
        attribute2            = p_x_effty_ext_detail_tbl(i).attribute2,
        attribute3            = p_x_effty_ext_detail_tbl(i).attribute3,
        attribute4            = p_x_effty_ext_detail_tbl(i).attribute4,
        attribute5            = p_x_effty_ext_detail_tbl(i).attribute5,
        attribute6            = p_x_effty_ext_detail_tbl(i).attribute6,
        attribute7            = p_x_effty_ext_detail_tbl(i).attribute7,
        attribute8            = p_x_effty_ext_detail_tbl(i).attribute8,
        attribute9            = p_x_effty_ext_detail_tbl(i).attribute9,
        attribute10           = p_x_effty_ext_detail_tbl(i).attribute10,
        attribute11           = p_x_effty_ext_detail_tbl(i).attribute11,
        attribute12           = p_x_effty_ext_detail_tbl(i).attribute12,
        attribute13           = p_x_effty_ext_detail_tbl(i).attribute13,
        attribute14           = p_x_effty_ext_detail_tbl(i).attribute14,
        attribute15           = p_x_effty_ext_detail_tbl(i).attribute15,
        last_update_date      = p_x_effty_ext_detail_tbl(i).last_update_date,
        last_updated_by       = p_x_effty_ext_detail_tbl(i).last_updated_by,
        last_update_login     = p_x_effty_ext_detail_tbl(i).last_update_login
      WHERE MR_EFFECTIVITY_EXT_DTL_ID  = p_x_effty_ext_detail_tbl(i).MR_EFFECTIVITY_EXT_DTL_ID
      AND   object_version_number     = p_x_effty_ext_detail_tbl(i).object_version_number;

Line: 2363


      DELETE AHL_MR_EFFECTIVITY_EXT_DTLS
      WHERE MR_EFFECTIVITY_EXT_DTL_ID = p_x_effty_ext_detail_tbl(i).MR_EFFECTIVITY_EXT_DTL_ID
      AND   object_version_number    = p_x_effty_ext_detail_tbl(i).object_version_number;