APPS.AHL_UC_UNITCONFIG_PVT SQL Statements

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

Line: 8

  SELECT uc_header_id,
         object_version_number,
         uc_name,
         uc_status_code,
         active_uc_status_code,
         csi_instance_id,
         instance_number,
         active_start_date,
         active_end_date,
         parent_uc_header_id,
         mc_header_id,
         mc_name,
         mc_revision,
         attribute_category,
         attribute1,
         attribute2,
         attribute3,
         attribute4,
         attribute5,
         attribute6,
         attribute7,
         attribute8,
         attribute9,
         attribute10,
         attribute11,
         attribute12,
         attribute13,
         attribute14,
         attribute15
    FROM ahl_unit_config_headers_v
   WHERE uc_header_id = c_uc_header_id;

Line: 323

    SELECT H.mc_header_id,
           R.relationship_id
      FROM ahl_mc_headers_b H,
           ahl_mc_relationships R
     WHERE H.name = c_mc_name
       AND H.revision = c_mc_revision
       AND H.mc_header_id = R.mc_header_id
       AND R.parent_relationship_id IS NULL
       AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 334

    SELECT H.mc_header_id,
           R.relationship_id
      FROM ahl_mc_headers_b H,
           ahl_mc_relationships R
     WHERE H.mc_header_id = c_mc_header_id
       AND H.mc_header_id = R.mc_header_id
       AND R.parent_relationship_id IS NULL
       AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 403

    SELECT distinct C.instance_id
      FROM csi_item_instances C,
           ahl_mc_relationships R,
           ahl_item_associations_b A
     WHERE C.instance_number = c_instance_num
       AND R.relationship_id = c_relationship_id
       AND R.item_group_id = A.item_group_id
       AND C.inventory_item_id = A.inventory_item_id
       AND C.inv_master_organization_id = A.inventory_org_id
       -- SATHAPLI::FP Bug 7498459, 27-Nov-2008 - Even INVENTORY instances are allowed for UC header creation.
       -- AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
       AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT')
       AND (A.revision IS NULL OR A.revision = C.inventory_revision) --Added by Jerry on 03/31/2005
       AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
       --Added by Jerry on 04/26/2005
       AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

       -- SATHAPLI::Bug# 5347338 fix
       /*
       AND NOT EXISTS (SELECT 'X'
                         FROM csi_ii_relationships I
                        WHERE I.subject_id = C.instance_id
                          AND I.relationship_type_code = 'COMPONENT-OF'
                          AND trunc(nvl(I.active_start_date,SYSDATE)) <= trunc(SYSDATE)
                          AND trunc(nvl(I.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
       */

       AND NOT EXISTS (SELECT 'X'
                         FROM ahl_unit_config_headers H
                        WHERE H.csi_item_instance_id = C.instance_id
                          AND trunc(nvl(H.active_end_date, SYSDATE+1)) > trunc(SYSDATE));

Line: 435

    SELECT distinct C.instance_id
      FROM csi_item_instances C,
           ahl_mc_relationships R,
           ahl_item_associations_b A
     WHERE C.instance_id = c_instance_id
       AND R.relationship_id = c_relationship_id
       AND R.item_group_id = A.item_group_id
       AND C.inventory_item_id = A.inventory_item_id
       AND C.inv_master_organization_id = A.inventory_org_id
       -- SATHAPLI::FP Bug 7498459, 27-Nov-2008 - Even INVENTORY instances are allowed for UC header creation.
       -- AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
       AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT')
       AND (A.revision IS NULL OR A.revision = C.inventory_revision) --Added by Jerry on 03/31/2005
       AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
       --Added by Jerry on 04/26/2005
       AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

       -- SATHAPLI::Bug# 5347338 fix
       /*
       AND NOT EXISTS (SELECT 'X'
                         FROM csi_ii_relationships I
                        WHERE I.subject_id = C.instance_id
                          AND I.relationship_type_code = 'COMPONENT-OF'
                          AND trunc(nvl(I.active_start_date,SYSDATE)) <= trunc(SYSDATE)
                          AND trunc(nvl(I.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
       */

       AND NOT EXISTS (SELECT 'X'
                         FROM ahl_unit_config_headers H
                        WHERE H.csi_item_instance_id = C.instance_id
                          AND trunc(nvl(H.active_end_date, SYSDATE+1)) > trunc(SYSDATE));

Line: 512

    SELECT 'x'
    FROM ahl_unit_config_headers
    WHERE name = c_uc_name;

Line: 516

    SELECT 'x'
    FROM ahl_unit_config_headers
    WHERE name = c_uc_name
    AND unit_config_header_id <> c_uc_header_id;

Line: 521

    SELECT name
    FROM ahl_unit_config_headers
    WHERE unit_config_header_id = c_uc_header_id;

Line: 628

    SELECT serial_number, mfg_serial_number_flag
      FROM csi_item_instances
     WHERE instance_id = c_instance_id
       AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 724

  SELECT AHL_UNIT_CONFIG_HEADERS_S.NEXTVAL
  INTO   p_x_uc_header_rec.uc_header_id
  FROM   DUAL;

Line: 730

  INSERT INTO ahl_unit_config_headers(
    unit_config_header_id,
    object_version_number,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    name,
    master_config_id,
    csi_item_instance_id,
    unit_config_status_code,
    active_start_date,
    active_end_date,
    active_uc_status_code,
    parent_uc_header_id,
    attribute_category,
    attribute1,
    attribute2,
    attribute3,
    attribute4,
    attribute5,
    attribute6,
    attribute7,
    attribute8,
    attribute9,
    attribute10,
    attribute11,
    attribute12,
    attribute13,
    attribute14,
    attribute15)
    VALUES(
    p_x_uc_header_rec.uc_header_id,
    p_x_uc_header_rec.object_version_number,
    sysdate,
    fnd_global.user_id,
    sysdate,
    fnd_global.user_id,
    fnd_global.login_id,
    p_x_uc_header_rec.uc_name,
    p_x_uc_header_rec.mc_header_id,
    p_x_uc_header_rec.instance_id,
    p_x_uc_header_rec.unit_config_status_code,
    p_x_uc_header_rec.active_start_date,
    p_x_uc_header_rec.active_end_date,
    p_x_uc_header_rec.active_uc_status_code,
    p_x_uc_header_rec.parent_uc_header_id,
    p_x_uc_header_rec.attribute_category,
    p_x_uc_header_rec.attribute1,
    p_x_uc_header_rec.attribute2,
    p_x_uc_header_rec.attribute3,
    p_x_uc_header_rec.attribute4,
    p_x_uc_header_rec.attribute5,
    p_x_uc_header_rec.attribute6,
    p_x_uc_header_rec.attribute7,
    p_x_uc_header_rec.attribute8,
    p_x_uc_header_rec.attribute9,
    p_x_uc_header_rec.attribute10,
    p_x_uc_header_rec.attribute11,
    p_x_uc_header_rec.attribute12,
    p_x_uc_header_rec.attribute13,
    p_x_uc_header_rec.attribute14,
    p_x_uc_header_rec.attribute15);

Line: 834

      AHL_Util_UC_Pkg.getcsi_transaction_id('UC_UPDATE', l_transaction_type_id, l_return_val);

Line: 923

PROCEDURE update_uc_header(
  p_api_version        IN            NUMBER     := 1.0,
  p_init_msg_list      IN            VARCHAR2   := FND_API.G_FALSE,
  p_commit             IN            VARCHAR2   := FND_API.G_FALSE,
  p_validation_level   IN            NUMBER     := FND_API.G_VALID_LEVEL_FULL,
  p_module_type        IN            VARCHAR2   := NULL,
  x_return_status      OUT NOCOPY    VARCHAR2,
  x_msg_count          OUT NOCOPY    NUMBER,
  x_msg_data           OUT NOCOPY    VARCHAR2,
  p_x_uc_header_rec    IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type,
  p_uc_instance_rec    IN  ahl_uc_instance_pvt.uc_instance_rec_type)
IS
  l_api_name       CONSTANT   VARCHAR2(30)   := 'update_uc_header';

Line: 945

  SAVEPOINT update_uc_header;

Line: 1005

  UPDATE ahl_unit_config_headers SET
    object_version_number = object_version_number + 1,
    last_update_date = sysdate,
    last_updated_by = fnd_global.user_id,
    last_update_login = fnd_global.login_id,
    name = p_x_uc_header_rec.uc_name,
    master_config_id = p_x_uc_header_rec.mc_header_id,
    csi_item_instance_id = p_x_uc_header_rec.instance_id,
    unit_config_status_code = p_x_uc_header_rec.unit_config_status_code,
    active_start_date = p_x_uc_header_rec.active_start_date,
    active_end_date = p_x_uc_header_rec.active_end_date,
    active_uc_status_code = p_x_uc_header_rec.active_uc_status_code,
    parent_uc_header_id = p_x_uc_header_rec.parent_uc_header_id,
    attribute_category = p_x_uc_header_rec.attribute_category,
    attribute1 = p_x_uc_header_rec.attribute1,
    attribute2 = p_x_uc_header_rec.attribute2,
    attribute3 = p_x_uc_header_rec.attribute3,
    attribute4 = p_x_uc_header_rec.attribute4,
    attribute5 = p_x_uc_header_rec.attribute5,
    attribute6 = p_x_uc_header_rec.attribute6,
    attribute7 = p_x_uc_header_rec.attribute7,
    attribute8 = p_x_uc_header_rec.attribute8,
    attribute9 = p_x_uc_header_rec.attribute9,
    attribute10 = p_x_uc_header_rec.attribute10,
    attribute11 = p_x_uc_header_rec.attribute11,
    attribute12 = p_x_uc_header_rec.attribute12,
    attribute13 = p_x_uc_header_rec.attribute13,
    attribute14 = p_x_uc_header_rec.attribute14,
    attribute15 = p_x_uc_header_rec.attribute15
  WHERE unit_config_header_id = p_x_uc_header_rec.uc_header_id
    AND object_version_number = p_x_uc_header_rec.object_version_number;

Line: 1056

                   'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','before calling update_instance_attr');

Line: 1067

    ahl_uc_instance_pvt.update_instance_attr(
      p_api_version        => 1.0,
      p_init_msg_list      => FND_API.G_FALSE,
      p_commit             => FND_API.G_FALSE,
      p_validation_level   => FND_API.G_VALID_LEVEL_FULL,
      x_return_status      => x_return_status,
      x_msg_count          => x_msg_count,
      x_msg_data           => x_msg_data,
      p_uc_header_id       => p_x_uc_header_rec.uc_header_id,
      p_uc_instance_rec    => p_uc_instance_rec,
      p_prod_user_flag     => 'N');

Line: 1081

                     'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'After calling update_instance_attr');

Line: 1128

    ROLLBACK TO update_uc_header;

Line: 1135

    ROLLBACK TO update_uc_header;

Line: 1142

    ROLLBACK TO update_uc_header;

Line: 1155

END update_uc_header;

Line: 1159

PROCEDURE delete_uc_header(
  p_api_version           IN            NUMBER     := 1.0,
  p_init_msg_list         IN            VARCHAR2   := FND_API.G_FALSE,
  p_commit                IN            VARCHAR2   := FND_API.G_FALSE,
  p_validation_level      IN            NUMBER     := FND_API.G_VALID_LEVEL_FULL,
  x_return_status         OUT NOCOPY    VARCHAR2,
  x_msg_count             OUT NOCOPY    NUMBER,
  x_msg_data              OUT NOCOPY    VARCHAR2,
  p_uc_header_id          IN            NUMBER,
  p_object_version_number IN            NUMBER,
  p_csi_instance_ovn      IN            NUMBER)
IS
  l_api_name       CONSTANT   VARCHAR2(30)   := 'delete_uc_header';

Line: 1191

    SELECT object_version_number
      FROM csi_item_instances
     WHERE instance_id = c_instance_id
       AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1197

    SELECT parent_uc_header_id, unit_config_header_id
      FROM ahl_unit_config_headers
START WITH unit_config_header_id = c_uc_header_id
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY parent_uc_header_id = PRIOR unit_config_header_id
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
FOR UPDATE OF unit_config_header_id;

Line: 1205

    SELECT unit_config_header_id, unit_config_status_code
      FROM ahl_unit_config_headers
     WHERE parent_uc_header_id IS NULL
START WITH unit_config_header_id = c_uc_header_id
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1214

    SELECT relationship_id,
           object_version_number
      FROM csi_ii_relationships
     WHERE subject_id = c_instance_id
       AND relationship_type_code='COMPONENT-OF'
       AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
       AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line: 1226

  SAVEPOINT delete_uc_header;

Line: 1289

  UPDATE ahl_unit_config_headers
  SET active_end_date = SYSDATE,
      parent_uc_header_id = NULL,
      --Suitable for both standalone units and sub units
      object_version_number = object_version_number + 1,
      last_update_date = SYSDATE,
      last_updated_by = fnd_global.user_id,
      last_update_login = fnd_global.login_id
  WHERE unit_config_header_id = p_uc_header_id
  AND object_version_number = p_object_version_number;

Line: 1318

  AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);

Line: 1337

    FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_DELETED');

Line: 1406

    UPDATE ahl_unit_config_headers
       SET active_end_date = sysdate,
           object_version_number = object_version_number + 1,
           last_update_date = sysdate,
           last_updated_by = fnd_global.user_id,
           last_update_login = fnd_global.login_id
     WHERE unit_config_header_id = l_subunits.unit_config_header_id;

Line: 1440

    ROLLBACK TO delete_uc_header;

Line: 1447

    ROLLBACK TO delete_uc_header;

Line: 1454

    ROLLBACK TO delete_uc_header;

Line: 1467

END delete_uc_header;