APPS.GMD_SUBSTITUTION_PVT SQL Statements

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

Line: 6

     SELECT i.original_inventory_item_id, i.start_date, i.substitution_status, f.formula_id
     FROM   gmd_item_substitution_hdr_b i, gmd_formula_substitution f
     WHERE  i.substitution_id = vSubstitution_id
     AND    i.substitution_id = f.substitution_id;

Line: 14

     SELECT MIN(i.start_date)
     FROM   gmd_item_substitution_hdr_b i, gmd_formula_substitution f
     WHERE  f.formula_id = V_formula_id
     AND    i.original_inventory_item_id = V_item_id
     AND    i.substitution_id <> p_substitution_id
     AND    i.substitution_id = f.substitution_id
     AND    i.substitution_status BETWEEN 700 AND 799;

Line: 45

      SELECT substitution_name, substitution_version
      FROM   gmd_item_substitution_hdr_b
      WHERE  substitution_id =  vSubstitution_id;

Line: 113

      SELECT primary_uom_code
      FROM   mtl_system_items_b
      WHERE  inventory_item_id = vItem_id;

Line: 134

    GMD_ITEM_SUBSTITUTION_HDR_PKG.INSERT_ROW(
       X_ROWID                    => l_row_id,
       X_SUBSTITUTION_ID          => p_substitution_id,
       X_SUBSTITUTION_NAME        => p_substitution_hdr_rec.substitution_name,
       X_SUBSTITUTION_VERSION     => p_substitution_hdr_rec.substitution_version,
       X_SUBSTITUTION_STATUS      => 100,
       X_ORIGINAL_INVENTORY_ITEM_ID => p_substitution_hdr_rec.original_inventory_item_id,
       X_ORIGINAL_UOM             => l_original_prim_item_um,
       X_ORIGINAL_QTY             => p_substitution_hdr_rec.original_qty,
       X_PREFERENCE               => p_substitution_hdr_rec.preference,
       X_START_DATE               => p_substitution_hdr_rec.start_date,
       X_END_DATE                 => p_substitution_hdr_rec.end_date,
       X_OWNER_ORGANIZATION_ID    => p_substitution_hdr_rec.owner_organization_id,
       X_REPLACEMENT_UOM_TYPE     => p_substitution_hdr_rec.replacement_uom_type,
       X_ATTRIBUTE_CATEGORY       => p_substitution_hdr_rec.attribute_category,
       X_ATTRIBUTE1               => p_substitution_hdr_rec.attribute1,
       X_ATTRIBUTE2               => p_substitution_hdr_rec.attribute2,
       X_ATTRIBUTE3               => p_substitution_hdr_rec.attribute3,
       X_ATTRIBUTE4               => p_substitution_hdr_rec.attribute4,
       X_ATTRIBUTE5               => p_substitution_hdr_rec.attribute5,
       X_ATTRIBUTE6               => p_substitution_hdr_rec.attribute6,
       X_ATTRIBUTE7               => p_substitution_hdr_rec.attribute7,
       X_ATTRIBUTE8               => p_substitution_hdr_rec.attribute8,
       X_ATTRIBUTE9               => p_substitution_hdr_rec.attribute9,
       X_ATTRIBUTE10              => p_substitution_hdr_rec.attribute10,
       X_ATTRIBUTE11              => p_substitution_hdr_rec.attribute11,
       X_ATTRIBUTE12              => p_substitution_hdr_rec.attribute12,
       X_ATTRIBUTE13              => p_substitution_hdr_rec.attribute13,
       X_ATTRIBUTE14              => p_substitution_hdr_rec.attribute14,
       X_ATTRIBUTE15              => p_substitution_hdr_rec.attribute15,
       X_ATTRIBUTE16              => p_substitution_hdr_rec.attribute16,
       X_ATTRIBUTE17              => p_substitution_hdr_rec.attribute17,
       X_ATTRIBUTE18              => p_substitution_hdr_rec.attribute18,
       X_ATTRIBUTE19              => p_substitution_hdr_rec.attribute19,
       X_ATTRIBUTE20              => p_substitution_hdr_rec.attribute20,
       X_ATTRIBUTE21              => p_substitution_hdr_rec.attribute21,
       X_ATTRIBUTE22              => p_substitution_hdr_rec.attribute22,
       X_ATTRIBUTE23              => p_substitution_hdr_rec.attribute23,
       X_ATTRIBUTE24              => p_substitution_hdr_rec.attribute24,
       X_ATTRIBUTE25              => p_substitution_hdr_rec.attribute25,
       X_ATTRIBUTE26              => p_substitution_hdr_rec.attribute26,
       X_ATTRIBUTE27              => p_substitution_hdr_rec.attribute27,
       X_ATTRIBUTE28              => p_substitution_hdr_rec.attribute28,
       X_ATTRIBUTE29              => p_substitution_hdr_rec.attribute29,
       X_ATTRIBUTE30              => p_substitution_hdr_rec.attribute30,
       X_SUBSTITUTION_DESCRIPTION => p_substitution_hdr_rec.substitution_description,
       X_CREATION_DATE            => p_substitution_hdr_rec.creation_date,
       X_CREATED_BY               => p_substitution_hdr_rec.created_by,
       X_LAST_UPDATE_DATE         => p_substitution_hdr_rec.last_update_date,
       X_LAST_UPDATED_BY          => p_substitution_hdr_rec.last_updated_by,
       X_LAST_UPDATE_LOGIN        => p_substitution_hdr_rec.last_update_login);

Line: 240

    GMD_ITEM_SUBSTITUTION_DTL_PKG.INSERT_ROW(
       X_ROWID                    => l_row_id,
       X_SUBSTITUTION_LINE_ID     => p_substitution_line_id,
       X_SUBSTITUTION_ID          => p_substitution_id,
       X_INVENTORY_ITEM_ID        => p_substitution_dtl_rec.inventory_item_id,
       X_UNIT_QTY                 => p_substitution_dtl_rec.unit_qty,
       X_DETAIL_UOM               => p_substitution_dtl_rec.detail_uom,
       X_CREATION_DATE            => p_substitution_dtl_rec.creation_date,
       X_CREATED_BY               => p_substitution_dtl_rec.created_by,
       X_LAST_UPDATE_DATE         => p_substitution_dtl_rec.last_update_date,
       X_LAST_UPDATED_BY          => p_substitution_dtl_rec.last_updated_by,
       X_LAST_UPDATE_LOGIN        => p_substitution_dtl_rec.last_update_login);

Line: 308

      Select substitution_status, start_date, original_inventory_item_id
      From   gmd_item_substitution_hdr_b
      Where  substitution_id = vSubstitution_id;

Line: 331

      select gmd_formula_substitution_s.nextval
        into l_formula_substitution_id
        from dual;

Line: 336

      GMD_FORMULA_SUBSTITUTION_PKG.insert_row
      (
         X_ROWID                     => l_row_id
       , X_FORMULA_SUBSTITUTION_ID   => l_formula_substitution_id
       , X_SUBSTITUTION_ID           => p_substitution_id
       , X_FORMULA_ID                => p_formula_substitution_tbl(i).formula_id
       , X_ASSOCIATED_FLAG           => l_associated_flag
       , X_CREATION_DATE             => p_formula_substitution_tbl(i).creation_date
       , X_CREATED_BY                => p_formula_substitution_tbl(i).created_by
       , X_LAST_UPDATE_DATE          => p_formula_substitution_tbl(i).last_update_date
       , X_LAST_UPDATED_BY           => p_formula_substitution_tbl(i).last_updated_by
       , X_LAST_UPDATE_LOGIN         => p_formula_substitution_tbl(i).last_update_login
       );

Line: 362

          UPDATE fm_matl_dtl
          SET    ingredient_end_date = l_subs_start_date
          WHERE  formula_id = p_formula_substitution_tbl(i).formula_id
          AND    line_type = -1
          AND    inventory_item_id = l_original_item_id;

Line: 401

  /*   Update_substitution_header                                    */
  /*                                                                 */
  /* DESCRIPTION:                                                    */
  /*                                                                 */
  /*                                                                 */
  /* History :                                                       */
  /* Rajender Nalla    09-OCT-06   Initial implementation.           */
  /* =============================================================== */
  PROCEDURE Update_substitution_header
  ( p_substitution_hdr_rec IN          gmd_item_substitution_hdr%ROWTYPE
  , x_message_count        OUT NOCOPY  NUMBER
  , x_message_list         OUT NOCOPY  VARCHAR2
  , x_return_status        OUT NOCOPY  VARCHAR2
  ) IS

    /* Local variable section */
    l_api_name    CONSTANT     VARCHAR2(30) := 'Update_substitution_header';

Line: 423

      Select *
      From   gmd_item_substitution_hdr_b
      Where  substitution_id = vSubstitution_id;

Line: 432

      SELECT 1
      FROM  gmd_item_substitution_hdr_b
      WHERE substitution_id      <> vSubstitution_id
      AND   original_inventory_item_id = vOriginal_item_id
      AND   preference           = vPreference
      AND   vStart_date          >= start_date
      AND   substitution_status  < 1000
      AND   (end_date IS NULL OR vEnd_date <= end_date);

Line: 442

    substitution_update_failure      EXCEPTION;

Line: 465

      RAISE substitution_update_failure;

Line: 469

    GMD_ITEM_SUBSTITUTION_HDR_PKG.UPDATE_ROW(
       X_SUBSTITUTION_ID           => p_substitution_hdr_rec.substitution_id,
       X_SUBSTITUTION_NAME         => p_substitution_hdr_rec.substitution_name,
       X_SUBSTITUTION_VERSION      => p_substitution_hdr_rec.substitution_version,
       X_SUBSTITUTION_STATUS       => p_substitution_hdr_rec.substitution_status,
       X_ORIGINAL_INVENTORY_ITEM_ID => p_substitution_hdr_rec.original_inventory_item_id,
       X_ORIGINAL_UOM              => p_substitution_hdr_rec.original_uom,
       X_ORIGINAL_QTY              => p_substitution_hdr_rec.original_qty,
       X_PREFERENCE                => p_substitution_hdr_rec.preference,
       X_START_DATE                => p_substitution_hdr_rec.start_date,
       X_END_DATE                  => p_substitution_hdr_rec.end_date,
       X_OWNER_ORGANIZATION_ID     => p_substitution_hdr_rec.owner_organization_id,
       X_REPLACEMENT_UOM_TYPE      => p_substitution_hdr_rec.replacement_uom_type,
       X_ATTRIBUTE_CATEGORY        => p_substitution_hdr_rec.attribute_category,
       X_ATTRIBUTE1                => p_substitution_hdr_rec.attribute1,
       X_ATTRIBUTE2                => p_substitution_hdr_rec.attribute2,
       X_ATTRIBUTE3                => p_substitution_hdr_rec.attribute3,
       X_ATTRIBUTE4                => p_substitution_hdr_rec.attribute4,
       X_ATTRIBUTE5                => p_substitution_hdr_rec.attribute5,
       X_ATTRIBUTE6                => p_substitution_hdr_rec.attribute6,
       X_ATTRIBUTE7                => p_substitution_hdr_rec.attribute7,
       X_ATTRIBUTE8                => p_substitution_hdr_rec.attribute8,
       X_ATTRIBUTE9                => p_substitution_hdr_rec.attribute9,
       X_ATTRIBUTE10               => p_substitution_hdr_rec.attribute10,
       X_ATTRIBUTE11               => p_substitution_hdr_rec.attribute11,
       X_ATTRIBUTE12               => p_substitution_hdr_rec.attribute12,
       X_ATTRIBUTE13               => p_substitution_hdr_rec.attribute13,
       X_ATTRIBUTE14               => p_substitution_hdr_rec.attribute14,
       X_ATTRIBUTE15               => p_substitution_hdr_rec.attribute15,
       X_ATTRIBUTE16               => p_substitution_hdr_rec.attribute16,
       X_ATTRIBUTE17               => p_substitution_hdr_rec.attribute17,
       X_ATTRIBUTE18               => p_substitution_hdr_rec.attribute18,
       X_ATTRIBUTE19               => p_substitution_hdr_rec.attribute19,
       X_ATTRIBUTE20               => p_substitution_hdr_rec.attribute20,
       X_ATTRIBUTE21               => p_substitution_hdr_rec.attribute21,
       X_ATTRIBUTE22               => p_substitution_hdr_rec.attribute22,
       X_ATTRIBUTE23               => p_substitution_hdr_rec.attribute23,
       X_ATTRIBUTE24               => p_substitution_hdr_rec.attribute24,
       X_ATTRIBUTE25               => p_substitution_hdr_rec.attribute25,
       X_ATTRIBUTE26               => p_substitution_hdr_rec.attribute26,
       X_ATTRIBUTE27               => p_substitution_hdr_rec.attribute27,
       X_ATTRIBUTE28               => p_substitution_hdr_rec.attribute28,
       X_ATTRIBUTE29               => p_substitution_hdr_rec.attribute29,
       X_ATTRIBUTE30               => p_substitution_hdr_rec.attribute30,
       X_SUBSTITUTION_DESCRIPTION  => p_substitution_hdr_rec.substitution_description,
       X_LAST_UPDATE_DATE          => p_substitution_hdr_rec.last_update_date,
       X_LAST_UPDATED_BY           => p_substitution_hdr_rec.last_updated_by,
       X_LAST_UPDATE_LOGIN         => p_substitution_hdr_rec.last_update_login);

Line: 534

          UPDATE fm_matl_dtl
          SET    ingredient_end_date = p_substitution_hdr_rec.start_date
          WHERE  formula_id = l_rec.formula_id
          AND    line_type = -1
          AND    inventory_item_id = l_rec.original_inventory_item_id;

Line: 559

    WHEN substitution_update_failure THEN
         x_return_status := FND_API.G_RET_STS_ERROR;

Line: 572

  END Update_substitution_header;

Line: 576

  /*   Update_substitution_detail                                    */
  /*                                                                 */
  /* DESCRIPTION:                                                    */
  /*                                                                 */
  /*                                                                 */
  /* History :                                                       */
  /* Rajender Nalla    09-OCT-06   Initial implementation.           */
  /* =============================================================== */
  PROCEDURE Update_substitution_detail
  ( p_substitution_dtl_rec  IN          gmd_item_substitution_dtl%ROWTYPE
  , x_message_count         OUT NOCOPY  NUMBER
  , x_message_list          OUT NOCOPY  VARCHAR2
  , x_return_status         OUT NOCOPY  VARCHAR2
  ) IS
    /* Local variable section */
    l_api_name    CONSTANT VARCHAR2(30) := 'Update_substitution_detail';

Line: 598

    GMD_ITEM_SUBSTITUTION_DTL_PKG.UPDATE_ROW(
       X_SUBSTITUTION_LINE_ID      => p_substitution_dtl_rec.substitution_line_id,
       X_SUBSTITUTION_ID           => p_substitution_dtl_rec.substitution_id,
       X_INVENTORY_ITEM_ID         => p_substitution_dtl_rec.inventory_item_id,
       X_UNIT_QTY                  => p_substitution_dtl_rec.unit_qty,
       X_DETAIL_UOM                => p_substitution_dtl_rec.detail_uom,
       X_LAST_UPDATE_DATE          => p_substitution_dtl_rec.last_update_date,
       X_LAST_UPDATED_BY           => p_substitution_dtl_rec.last_updated_by,
       X_LAST_UPDATE_LOGIN         => p_substitution_dtl_rec.last_update_login);

Line: 631

  END Update_substitution_detail;

Line: 635

  /*   Delete_formula_association                                    */
  /*                                                                 */
  /* DESCRIPTION:                                                    */
  /*                                                                 */
  /*                                                                 */
  /* History :                                                       */
  /* Rajender Nalla    09-OCT-06   Initial implementation.           */
  /* =============================================================== */
  PROCEDURE Delete_formula_association
  ( p_formula_substitution_id  IN          NUMBER
  , x_message_count            OUT NOCOPY  NUMBER
  , x_message_list             OUT NOCOPY  VARCHAR2
  , x_return_status            OUT NOCOPY  VARCHAR2
  ) IS
    l_api_name    CONSTANT VARCHAR2(30) := 'Delete_formula_association';

Line: 656

      Select formula_id, original_inventory_item_id, i.substitution_id
      From   gmd_item_substitution_hdr_b i, gmd_formula_substitution f
      Where  f.formula_substitution_id = vformula_substitution_id
      AND    i.substitution_id = f.substitution_id;

Line: 664

      SELECT MIN(z.start_date)
      FROM   gmd_item_substitution_hdr_b z,
             gmd_formula_substitution y
      WHERE  y.substitution_id <> vSubstitution_id
      AND    z.substitution_id  = y.substitution_id
      AND    z.original_inventory_item_id = vOriginal_item_id
      AND    y.formula_id = vformula_id
      AND    z.substitution_status BETWEEN 700 AND 799;

Line: 683

    GMD_FORMULA_SUBSTITUTION_PKG.DELETE_ROW
    (
      X_FORMULA_SUBSTITUTION_ID => p_formula_substitution_id
    );

Line: 695

      UPDATE fm_matl_dtl
      SET    ingredient_end_date = l_end_date
      WHERE  formula_id = l_formula_id
      AND    inventory_item_id = l_original_item_id
      AND    line_type = -1;

Line: 725

  END Delete_formula_association;

Line: 759

      SELECT max(substitution_version) + 1
      FROM   gmd_item_substitution_hdr_b
      WHERE  substitution_name = vSubstitution_name;

Line: 764

      SELECT max(preference) + 1
      FROM   gmd_item_substitution_hdr_b
      WHERE  original_inventory_item_id = vOriginal_item_id;

Line: 769

      SELECT substitution_name
           , substitution_descriptiOn
           , substitution_version
           , original_inventory_item_id
           , ' ' original_item_no
           , original_qty
           , preference
           , start_date
           , end_date
           , owner_organization_id
           , replacement_uom_type
           , attribute_category
           , attribute1
           , attribute2
           , attribute3
           , attribute4
           , attribute5
           , attribute6
           , attribute7
           , attribute8
           , attribute9
           , attribute10
           , attribute11
           , attribute12
           , attribute13
           , attribute14
           , attribute15
           , attribute16
           , attribute17
           , attribute18
           , attribute19
           , attribute20
           , attribute21
           , attribute22
           , attribute23
           , attribute24
           , attribute25
           , attribute26
           , attribute27
           , attribute28
           , attribute29
           , attribute30
           , creation_date
           , created_by
           , last_update_date
           , last_updated_by
           , last_update_login
      From   gmd_item_substitution_hdr
      Where  substitution_id = vSubstitution_id;

Line: 820

      SELECT inventory_item_id
           , ' ' item_no
           , unit_qty
           , detail_uom
           , creation_date
           , created_by
           , last_update_date
           , last_updated_by
           , last_update_login
      FROM   gmd_item_substitution_dtl
      WHERE  substitution_id = vSubstitution_id;

Line: 833

      SELECT formula_id
           , ' ' formula_no
           , 1 formula_vers
           , creation_date
           , created_by
           , last_update_date
           , last_updated_by
           , last_update_login
      FROM   gmd_formula_substitution
      WHERE  substitution_id = vSubstitution_id;

Line: 873

    select  gmd_item_substitution_hdr_s.nextval
      into  l_substitution_id
     from   dual;

Line: 901

    select  gmd_item_substitution_dtl_s.nextval
      into  l_substitution_line_id
      from  dual;