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.MTL_MATERIAL_STATUSES_PKG SQL Statements

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

Line: 5


X_UPDATE_FLAG VARCHAR2(1) := 'N';

Line: 7


procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_STATUS_ID in NUMBER,
  X_ATTRIBUTE15 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_LOCATOR_CONTROL in NUMBER,
  X_LOT_CONTROL in NUMBER,
  X_SERIAL_CONTROL in NUMBER,
  X_ONHAND_CONTROL in NUMBER,  -- Onhand Material Status Support Bug #6633612
  X_ZONE_CONTROL in NUMBER,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_REQUEST_ID in NUMBER,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ENABLED_FLAG in NUMBER,
  X_STATUS_CODE in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_LPN_CONTROL in NUMBER,
  --INVCONV KKILLAMS
  X_inventory_atp_code  IN NUMBER,
  X_reservable_type     IN NUMBER,
  X_availability_type   IN NUMBER
  --END INVCONV KKILLAMS
) is
  cursor C is select ROWID from MTL_MATERIAL_STATUSES_B
    where STATUS_ID = X_STATUS_ID
    ;

Line: 51


  insert into MTL_MATERIAL_STATUSES_B (
    ATTRIBUTE15,
    ATTRIBUTE2,
    ATTRIBUTE3,
    ATTRIBUTE4,
    ATTRIBUTE5,
    ATTRIBUTE6,
    ATTRIBUTE7,
    ATTRIBUTE8,
    ATTRIBUTE9,
    ATTRIBUTE10,
    ATTRIBUTE11,
    ATTRIBUTE12,
    ATTRIBUTE13,
    LOCATOR_CONTROL,
    LOT_CONTROL,
    SERIAL_CONTROL,
    ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
    STATUS_ID,
    ZONE_CONTROL,
    ATTRIBUTE1,
    ATTRIBUTE14,
    REQUEST_ID,
    ATTRIBUTE_CATEGORY,
    ENABLED_FLAG,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LPN_CONTROL,
    --INVCONV KKILLAMS
    INVENTORY_ATP_CODE,
    RESERVABLE_TYPE,
    AVAILABILITY_TYPE
    --END INVCONV KKILLAMS
  ) values (
    X_ATTRIBUTE15,
    X_ATTRIBUTE2,
    X_ATTRIBUTE3,
    X_ATTRIBUTE4,
    X_ATTRIBUTE5,
    X_ATTRIBUTE6,
    X_ATTRIBUTE7,
    X_ATTRIBUTE8,
    X_ATTRIBUTE9,
    X_ATTRIBUTE10,
    X_ATTRIBUTE11,
    X_ATTRIBUTE12,
    X_ATTRIBUTE13,
    X_LOCATOR_CONTROL,
    X_LOT_CONTROL,
    X_SERIAL_CONTROL,
    X_ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
    X_STATUS_ID,
    X_ZONE_CONTROL,
    X_ATTRIBUTE1,
    X_ATTRIBUTE14,
    X_REQUEST_ID,
    X_ATTRIBUTE_CATEGORY,
    X_ENABLED_FLAG,
    X_CREATION_DATE,
    X_CREATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_LPN_CONTROL,
    --INVCONV KKILLAMS
    X_INVENTORY_ATP_CODE,
    X_RESERVABLE_TYPE,
    X_AVAILABILITY_TYPE
    --END INVCONV KKILLAMS
  );

Line: 125


  insert into MTL_MATERIAL_STATUSES_TL (
    LAST_UPDATE_DATE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATE_LOGIN,
    STATUS_CODE,
    DESCRIPTION,
    STATUS_ID,
    LAST_UPDATED_BY,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_LAST_UPDATE_DATE,
    X_CREATED_BY,
    X_CREATION_DATE,
    X_LAST_UPDATE_LOGIN,
    X_STATUS_CODE,
    X_DESCRIPTION,
    X_STATUS_ID,
    X_LAST_UPDATED_BY,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from MTL_MATERIAL_STATUSES_TL T
    where T.STATUS_ID = X_STATUS_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 162


end INSERT_ROW;

Line: 165


procedure INSERT_TL_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_STATUS_ID in NUMBER,
  X_STATUS_CODE in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_CREATION_DATE in DATE,
  X_CREATED_BY in NUMBER,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER)
   IS
   cursor C is select ROWID from MTL_MATERIAL_STATUSES_TL
    where STATUS_ID = X_STATUS_ID
     ;

Line: 180


  insert into MTL_MATERIAL_STATUSES_TL (
    LAST_UPDATE_DATE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATE_LOGIN,
    STATUS_CODE,
    DESCRIPTION,
    STATUS_ID,
    LAST_UPDATED_BY,
    LANGUAGE,
    SOURCE_LANG
  ) select
    X_LAST_UPDATE_DATE,
    X_CREATED_BY,
    X_CREATION_DATE,
    X_LAST_UPDATE_LOGIN,
    X_STATUS_CODE,
    X_DESCRIPTION,
    X_STATUS_ID,
    X_LAST_UPDATED_BY,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from MTL_MATERIAL_STATUSES_TL T
    where T.STATUS_ID = X_STATUS_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 218


end INSERT_TL_ROW;

Line: 254


  cursor c is select
      ATTRIBUTE15,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      LOCATOR_CONTROL,
      LOT_CONTROL,
      SERIAL_CONTROL,
      ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
      ZONE_CONTROL,
      ATTRIBUTE1,
      ATTRIBUTE14,
      REQUEST_ID,
      ATTRIBUTE_CATEGORY,
      ENABLED_FLAG,
      LPN_CONTROL,
    --INVCONV KKILLAMS
      INVENTORY_ATP_CODE,
      RESERVABLE_TYPE,
      AVAILABILITY_TYPE
    --END INVCONV KKILLAMS
    from MTL_MATERIAL_STATUSES_B
    where STATUS_ID = X_STATUS_ID
    for update of STATUS_ID nowait;

Line: 289


  cursor c1 is select
      STATUS_CODE,
      DESCRIPTION,
      decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
    from MTL_MATERIAL_STATUSES_TL
    where STATUS_ID = X_STATUS_ID
    and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
    for update of STATUS_ID nowait;

Line: 302


    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 379


procedure UPDATE_ROW (
  X_STATUS_ID in NUMBER,
  X_ATTRIBUTE15 in VARCHAR2,
  X_ATTRIBUTE2 in VARCHAR2,
  X_ATTRIBUTE3 in VARCHAR2,
  X_ATTRIBUTE4 in VARCHAR2,
  X_ATTRIBUTE5 in VARCHAR2,
  X_ATTRIBUTE6 in VARCHAR2,
  X_ATTRIBUTE7 in VARCHAR2,
  X_ATTRIBUTE8 in VARCHAR2,
  X_ATTRIBUTE9 in VARCHAR2,
  X_ATTRIBUTE10 in VARCHAR2,
  X_ATTRIBUTE11 in VARCHAR2,
  X_ATTRIBUTE12 in VARCHAR2,
  X_ATTRIBUTE13 in VARCHAR2,
  X_LOCATOR_CONTROL in NUMBER,
  X_LOT_CONTROL in NUMBER,
  X_SERIAL_CONTROL in NUMBER,
  X_ONHAND_CONTROL in NUMBER,  -- Onhand Material Status Support Bug #6633612
  X_ZONE_CONTROL in NUMBER,
  X_ATTRIBUTE1 in VARCHAR2,
  X_ATTRIBUTE14 in VARCHAR2,
  X_REQUEST_ID in NUMBER,
  X_ATTRIBUTE_CATEGORY in VARCHAR2,
  X_ENABLED_FLAG in NUMBER,
  X_STATUS_CODE in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_LAST_UPDATE_DATE in DATE,
  X_LAST_UPDATED_BY in NUMBER,
  X_LAST_UPDATE_LOGIN in NUMBER,
  X_LPN_CONTROL in NUMBER,
  --INVCONV KKILLAMS
  X_INVENTORY_ATP_CODE  IN NUMBER,
  X_RESERVABLE_TYPE     IN NUMBER,
  X_AVAILABILITY_TYPE   IN NUMBER
  --END INVCONV KKILLAMS
) is
  --INVCONV KKILLAMS
  --Cursor is to verify the atp,reservable and available flags are getting modified or not.
  CURSOR cur_status IS SELECT 1 FROM MTL_MATERIAL_STATUSES_B
                                WHERE STATUS_ID = X_STATUS_ID
                                AND (INVENTORY_ATP_CODE    <> X_INVENTORY_ATP_CODE
                                    OR  RESERVABLE_TYPE    <>   X_RESERVABLE_TYPE
                                    OR  AVAILABILITY_TYPE  <>  X_AVAILABILITY_TYPE );

Line: 430


  update MTL_MATERIAL_STATUSES_B set
    ATTRIBUTE15 = X_ATTRIBUTE15,
    ATTRIBUTE2 = X_ATTRIBUTE2,
    ATTRIBUTE3 = X_ATTRIBUTE3,
    ATTRIBUTE4 = X_ATTRIBUTE4,
    ATTRIBUTE5 = X_ATTRIBUTE5,
    ATTRIBUTE6 = X_ATTRIBUTE6,
    ATTRIBUTE7 = X_ATTRIBUTE7,
    ATTRIBUTE8 = X_ATTRIBUTE8,
    ATTRIBUTE9 = X_ATTRIBUTE9,
    ATTRIBUTE10 = X_ATTRIBUTE10,
    ATTRIBUTE11 = X_ATTRIBUTE11,
    ATTRIBUTE12 = X_ATTRIBUTE12,
    ATTRIBUTE13 = X_ATTRIBUTE13,
    LOCATOR_CONTROL = X_LOCATOR_CONTROL,
    LOT_CONTROL = X_LOT_CONTROL,
    SERIAL_CONTROL = X_SERIAL_CONTROL,
    ONHAND_CONTROL = X_ONHAND_CONTROL, -- Onhand Material Status Support Bug #6633612
    ZONE_CONTROL = X_ZONE_CONTROL,
    ATTRIBUTE1 = X_ATTRIBUTE1,
    ATTRIBUTE14 = X_ATTRIBUTE14,
    REQUEST_ID = X_REQUEST_ID,
    ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
    ENABLED_FLAG = X_ENABLED_FLAG,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    LPN_CONTROL = X_LPN_CONTROL,
    --INVCONV KKILLAMS
    INVENTORY_ATP_CODE = X_INVENTORY_ATP_CODE,
    RESERVABLE_TYPE    = X_RESERVABLE_TYPE,
    AVAILABILITY_TYPE  = X_AVAILABILITY_TYPE
    --END INVCONV KKILLAMS
  where STATUS_ID = X_STATUS_ID;

Line: 469


  update MTL_MATERIAL_STATUSES_TL set
    STATUS_CODE = X_STATUS_CODE,
    DESCRIPTION = X_DESCRIPTION,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    SOURCE_LANG = userenv('LANG')
  where STATUS_ID = X_STATUS_ID
  and userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 481


     X_UPDATE_FLAG := 'T';

Line: 488


           UPDATE MTL_LOT_NUMBERS SET   INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
                                        AVAILABILITY_TYPE  =X_RESERVABLE_TYPE,
                                        RESERVABLE_TYPE    =X_AVAILABILITY_TYPE
                                  WHERE STATUS_ID = X_STATUS_ID;

Line: 492


           UPDATE MTL_ITEM_LOCATIONS SET   INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
                                           AVAILABILITY_TYPE  =X_RESERVABLE_TYPE,
                                           RESERVABLE_TYPE    =X_AVAILABILITY_TYPE
                                  WHERE STATUS_ID = X_STATUS_ID;

Line: 496


           UPDATE MTL_SECONDARY_INVENTORIES SET   INVENTORY_ATP_CODE =X_INVENTORY_ATP_CODE,
                                                  AVAILABILITY_TYPE  =X_RESERVABLE_TYPE,
                                                  RESERVABLE_TYPE    =X_AVAILABILITY_TYPE
                                  WHERE STATUS_ID = X_STATUS_ID;

Line: 503


end UPDATE_ROW;

Line: 505


procedure DELETE_ROW (
  X_STATUS_ID in NUMBER
) is
begin
  delete from MTL_MATERIAL_STATUSES_TL
  where STATUS_ID = X_STATUS_ID;

Line: 516


  delete from MTL_MATERIAL_STATUSES_B
  where STATUS_ID = X_STATUS_ID;

Line: 522


end DELETE_ROW;

Line: 532


    update mtl_material_statuses_tl set
	        status_code = X_STATUS_CODE,
	        description = X_DESCRIPTION,
	        last_update_date = sysdate,
		     last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
           last_update_login = 0,
           source_lang     = userenv('LANG')
	    where status_id = fnd_number.canonical_to_number(x_status_id)
 	    and userenv('LANG') IN (language, source_lang);

Line: 601


      SELECT SYSDATE INTO l_sysdate FROM dual;

Line: 617


      MTL_MATERIAL_STATUSES_PKG.update_row
       (
         X_STATUS_ID    => l_status_id,
         X_ATTRIBUTE15  => X_ATTRIBUTE15,
         X_ATTRIBUTE2   => X_ATTRIBUTE2,
         X_ATTRIBUTE3   => X_ATTRIBUTE3,
         X_ATTRIBUTE4   => X_ATTRIBUTE4,
         X_ATTRIBUTE5   => X_ATTRIBUTE5,
         X_ATTRIBUTE6   => X_ATTRIBUTE6,
         X_ATTRIBUTE7   => X_ATTRIBUTE7,
         X_ATTRIBUTE8   => X_ATTRIBUTE8,
         X_ATTRIBUTE9   => X_ATTRIBUTE9,
         X_ATTRIBUTE10  => X_ATTRIBUTE10,
         X_ATTRIBUTE11  => X_ATTRIBUTE11,
         X_ATTRIBUTE12  => X_ATTRIBUTE12,
         X_ATTRIBUTE13  => X_ATTRIBUTE13,
         X_LOCATOR_CONTROL => l_locator_control,
         X_LOT_CONTROL     => l_lot_control,
         X_SERIAL_CONTROL  => l_serial_control,
	 X_ONHAND_CONTROL  => l_onhand_control, -- Onhand Material Status Support Bug #6633612
         X_ZONE_CONTROL    => l_zone_control,
         X_ATTRIBUTE1         => X_ATTRIBUTE1,
         X_ATTRIBUTE14        => X_ATTRIBUTE14,
         X_REQUEST_ID         => NULL,
         X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE15,
         X_ENABLED_FLAG       => l_enabled_flag,
         X_STATUS_CODE        => x_status_code,
         X_DESCRIPTION        => x_description,
         X_LAST_UPDATE_DATE   => l_sysdate,
         X_LAST_UPDATED_BY    => l_user_id,
         X_LAST_UPDATE_LOGIN  => 0,
         X_LPN_CONTROL        => l_lpn_control,
         --INVCONV kkillams
         X_INVENTORY_ATP_CODE  => l_inventory_atp_code,
         X_RESERVABLE_TYPE     => l_reservable_type,
         X_AVAILABILITY_TYPE   => l_availability_type
         --END INVCONV kkillams
         );

Line: 660


        IF X_UPDATE_FLAG = 'T' THEN

          MTL_MATERIAL_STATUSES_PKG.insert_tl_row
          (
           X_ROWID              => l_row_id,
           X_STATUS_ID          => l_status_id,
           X_STATUS_CODE        => x_status_code,
           X_DESCRIPTION        => x_description,
           X_CREATION_DATE      => l_sysdate,
           X_CREATED_BY         => l_user_id,
           X_LAST_UPDATE_DATE   => l_sysdate,
           X_LAST_UPDATED_BY    => l_user_id,
           X_LAST_UPDATE_LOGIN  => 0
          );

Line: 677


        MTL_MATERIAL_STATUSES_PKG.insert_row
        (
         X_ROWID        => l_row_id,
         X_STATUS_ID    => l_status_id,
         X_ATTRIBUTE15  => X_ATTRIBUTE15,
         X_ATTRIBUTE2   => X_ATTRIBUTE2,
         X_ATTRIBUTE3   => X_ATTRIBUTE3,
         X_ATTRIBUTE4   => X_ATTRIBUTE4,
         X_ATTRIBUTE5   => X_ATTRIBUTE5,
         X_ATTRIBUTE6   => X_ATTRIBUTE6,
         X_ATTRIBUTE7   => X_ATTRIBUTE7,
         X_ATTRIBUTE8   => X_ATTRIBUTE8,
         X_ATTRIBUTE9   => X_ATTRIBUTE9,
         X_ATTRIBUTE10  => X_ATTRIBUTE10,
         X_ATTRIBUTE11  => X_ATTRIBUTE11,
         X_ATTRIBUTE12  => X_ATTRIBUTE12,
         X_ATTRIBUTE13  => X_ATTRIBUTE13,
         X_LOCATOR_CONTROL => l_locator_control,
         X_LOT_CONTROL     => l_lot_control,
         X_SERIAL_CONTROL  => l_serial_control,
 	 X_ONHAND_CONTROL  => l_onhand_control, -- Onhand Material Status Support Bug #6633612
         X_ZONE_CONTROL    => l_zone_control,
         X_ATTRIBUTE1         => X_ATTRIBUTE1,
         X_ATTRIBUTE14        => X_ATTRIBUTE14,
         X_REQUEST_ID         => NULL,
         X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE15,
         X_ENABLED_FLAG       => l_enabled_flag,
         X_STATUS_CODE        => x_status_code,
         X_DESCRIPTION        => x_description,
         X_CREATION_DATE      => l_sysdate,
         X_CREATED_BY         => l_user_id,
         X_LAST_UPDATE_DATE   => l_sysdate,
         X_LAST_UPDATED_BY    => l_user_id,
         X_LAST_UPDATE_LOGIN  => 0,
         X_LPN_CONTROL        => l_lpn_control,
         --INVCONV kkillams
         X_INVENTORY_ATP_CODE  => l_inventory_atp_code,
         X_RESERVABLE_TYPE     => l_reservable_type,
         X_AVAILABILITY_TYPE   => l_availability_type
         --END INVCONV kkillams
        );

Line: 728


  delete from MTL_MATERIAL_STATUSES_TL T
  where not exists
    (select NULL
    from MTL_MATERIAL_STATUSES_B B
    where B.STATUS_ID = T.STATUS_ID
    );

Line: 735


  update MTL_MATERIAL_STATUSES_TL T set (
      STATUS_CODE,
      DESCRIPTION
    ) = (select
      B.STATUS_CODE,
      B.DESCRIPTION
    from MTL_MATERIAL_STATUSES_TL B
    where B.STATUS_ID = T.STATUS_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.STATUS_ID,
      T.LANGUAGE
  ) in (select
      SUBT.STATUS_ID,
      SUBT.LANGUAGE
    from MTL_MATERIAL_STATUSES_TL SUBB, MTL_MATERIAL_STATUSES_TL SUBT
    where SUBB.STATUS_ID = SUBT.STATUS_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.STATUS_CODE <> SUBT.STATUS_CODE
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
  ));

Line: 759


  insert into MTL_MATERIAL_STATUSES_TL (
    LAST_UPDATE_DATE,
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATE_LOGIN,
    STATUS_CODE,
    DESCRIPTION,
    STATUS_ID,
    LAST_UPDATED_BY,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.LAST_UPDATE_DATE,
    B.CREATED_BY,
    B.CREATION_DATE,
    B.LAST_UPDATE_LOGIN,
    B.STATUS_CODE,
    B.DESCRIPTION,
    B.STATUS_ID,
    B.LAST_UPDATED_BY,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from MTL_MATERIAL_STATUSES_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from MTL_MATERIAL_STATUSES_TL T
    where T.STATUS_ID = B.STATUS_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);