APPS.HZ_PARTY_USG_ASSIGNMENT_PVT SQL Statements

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

Line: 44

PROCEDURE insert_row (
    p_party_usg_assignment_rec    IN     party_usg_assignment_rec_type
);

Line: 48

PROCEDURE update_row (
    p_party_usg_assignment_id     IN     NUMBER,
    p_party_usg_assignment_rec    IN     party_usg_assignment_rec_type,
    p_object_version_number       IN OUT NOCOPY NUMBER,
    p_old_object_version_number   IN     NUMBER,
    p_status                      IN     VARCHAR2
);

Line: 72

    p_create_update_flag          IN     VARCHAR2,
    p_party_usg_assignment_id     IN     NUMBER
);

Line: 97

    p_create_update_flag          IN     VARCHAR2,
    p_party_usg_assignment_id     IN     NUMBER
) IS

    c_api_name                    CONSTANT VARCHAR2(30) :=
                                    'populate_bot';

Line: 121

        p_operation               => p_create_update_flag,
      P_party_usg_assignment_id   => p_party_usg_assignment_id);

Line: 276

    p_create_update_flag          IN     VARCHAR2,
    p_validation_level            IN     NUMBER,
    p_party_usg_assignment_rec    IN     party_usg_assignment_rec_type,
    p_old_usg_assignment_rec      IN     party_usg_assignment_rec_type,
    x_return_status               IN OUT NOCOPY VARCHAR2
) IS

    c_api_name                    CONSTANT VARCHAR2(30) :=
                                    'validate_party_usg_assignment';

Line: 291

    SELECT party_type,
           party_name
    FROM   hz_parties
    WHERE  party_id = p_party_id
    AND    status IN ('A', 'I');

Line: 302

    SELECT null
    FROM   hz_relationships
    WHERE  relationship_id = p_relationship_id
    AND    status IN ('A', 'I')
    AND    rownum = 1;

Line: 313

    SELECT party_usage_code
    FROM   hz_party_usg_assignments
    WHERE  party_id = p_party_id;

Line: 322

    db_restrict_manual_update     VARCHAR2(30);

Line: 363

      IF p_create_update_flag = 'C' THEN
        --
        -- mandatory: party_id
        --
        hz_utility_v2pub.validate_mandatory (
          p_create_update_flag        => p_create_update_flag,
          p_column                    => 'party_id',
          p_column_value              => p_party_usg_assignment_rec.party_id,
          x_return_status             => x_return_status
        );

Line: 387

          p_create_update_flag        => p_create_update_flag,
          p_column                    => 'party_usage_code',
          p_column_value              => p_party_usg_assignment_rec.party_usage_code,
          x_return_status             => x_return_status
        );

Line: 409

            p_create_update_flag        => p_create_update_flag,
            p_column                    => 'owner_table_id',
            p_column_value              => p_party_usg_assignment_rec.owner_table_id,
            x_return_status             => x_return_status
          );

Line: 420

            p_create_update_flag        => p_create_update_flag,
            p_column                    => 'owner_table_name',
            p_column_value              => p_party_usg_assignment_rec.owner_table_name,
            x_return_status             => x_return_status
          );

Line: 463

      ELSE -- p_create_update_flag = 'U'
        --
        -- non-updateable: party_id
        --
        hz_utility_v2pub.validate_nonupdateable (
          p_column                    => 'party_id',
          p_column_value              => p_party_usg_assignment_rec.party_id,
          p_old_column_value          => p_old_usg_assignment_rec.party_id,
          x_return_status             => x_return_status
        );

Line: 478

            p_message               => 'party_id is a non-updateable column. '||
                                       'x_return_status = '||x_return_status,
            p_msg_level             => fnd_log.level_statement);

Line: 486

        hz_utility_v2pub.validate_nonupdateable (
          p_column                    => 'party_usage_code',
          p_column_value              => p_party_usg_assignment_rec.party_usage_code,
          p_old_column_value          => p_old_usg_assignment_rec.party_usage_code,
          x_return_status             => x_return_status
        );

Line: 497

            p_message               => 'party_usage_code is a non-updateable column. '||
                                       'x_return_status = '||x_return_status,
            p_msg_level             => fnd_log.level_statement);

Line: 505

        hz_utility_v2pub.validate_nonupdateable (
          p_column                    => 'owner_table_name',
          p_column_value              => p_party_usg_assignment_rec.owner_table_name,
          p_old_column_value          => p_old_usg_assignment_rec.owner_table_name,
          x_return_status             => x_return_status
        );

Line: 516

            p_message               => 'owner_table_name is a non-updateable column. '||
                                       'x_return_status = '||x_return_status,
            p_msg_level             => fnd_log.level_statement);

Line: 524

        hz_utility_v2pub.validate_nonupdateable (
          p_column                    => 'owner_table_id',
          p_column_value              => p_party_usg_assignment_rec.owner_table_id,
          p_old_column_value          => p_old_usg_assignment_rec.owner_table_id,
          x_return_status             => x_return_status
        );

Line: 535

            p_message               => 'owner_table_id is a non-updateable column. '||
                                       'x_return_status = '||x_return_status,
            p_msg_level             => fnd_log.level_statement);

Line: 540

      END IF;   -- p_create_update_flag = 'U'

Line: 569

        p_create_update_flag        => p_create_update_flag,
        p_created_by_module         => p_party_usg_assignment_rec.created_by_module,
        p_old_created_by_module     => p_old_usg_assignment_rec.created_by_module,
        x_return_status             => x_return_status
      );

Line: 584

    IF p_create_update_flag = 'C' THEN
      l_party_usage_code := p_party_usg_assignment_rec.party_usage_code;

Line: 603

        db_restrict_manual_update := l_value_tbl(4);

Line: 614

                                       'db_restrict_manual_update = '||db_restrict_manual_update,
            p_msg_level             => fnd_log.level_statement);

Line: 617

      ELSIF p_create_update_flag = 'C' THEN
        --
        -- invalid foreign key
        --
        fnd_message.set_name('AR', 'HZ_PU_INVALID_PARTY_USAGE_CODE');

Line: 636

      IF p_create_update_flag = 'C' THEN
        --
        -- inactive party usage code
        --
        IF db_party_usage_status <> 'A' THEN
          fnd_message.set_name('AR', 'HZ_PU_INACTIVE_PARTY_USG_CODE');

Line: 749

      ELSE -- p_create_update_flag = 'U'
      --Bug 7149894: Included 121 in user id validation while validating
      --             Party Usage Assignment.
        IF db_restrict_manual_update = 'Y' AND
           G_CALLING_API = 'HZ_PARTY_USG_ASSIGNMENT_PUB' AND
           db_party_usage_created_by IN (0, 1, 2, 120, 121)
        THEN
          fnd_message.set_name('AR', 'HZ_PU_SEED_CBM_UPDATE');

Line: 766

            p_message               => 'manual update is Y. calling from public API. '||
                                       'x_return_status = '||x_return_status,
            p_msg_level             => fnd_log.level_statement);

Line: 778

       p_create_update_flag = 'C'
    THEN
      --
      -- check party usage rules
      --
      IF G_SETUP_LOADED = 3 THEN

        OPEN c_assignments(p_party_usg_assignment_rec.party_id);

Line: 889

    SELECT party_usg_assignment_id
    FROM   hz_party_usg_assignments
    WHERE  owner_table_name = p_owner_table_name
    AND    owner_table_id = p_owner_table_id
    AND    party_id = p_party_id
    AND    party_usage_code = p_party_usage_code
    AND    rownum = 1;

Line: 904

    SELECT party_usg_assignment_id
    FROM   hz_party_usg_assignments
    WHERE  party_id = p_party_id
    AND    party_usage_code = p_party_usage_code
    AND    status_flag = 'A'
    AND    p_effective_start_date BETWEEN
             effective_start_date AND effective_end_date
    AND    effective_end_date >= p_effective_end_date
    AND    rownum = 1;

Line: 1042

    SELECT party_usg_assignment_id,
           party_usage_code,
           effective_start_date
    FROM   hz_party_usg_assignments
    WHERE  party_id = p_party_id
    AND    status_flag = 'A'
    AND    trunc(sysdate) between
             effective_start_date and effective_end_date;

Line: 1115

        p_create_update_flag        => 'C',
        p_validation_level          => p_validation_level,
        p_party_usg_assignment_rec  => p_party_usg_assignment_rec,
        p_old_usg_assignment_rec    => l_party_usg_assignment_rec,
        x_return_status             => x_return_status
      );

Line: 1172

          update_row (
            p_party_usg_assignment_id   => l_party_usg_assignment_id_tbl(i),
            p_party_usg_assignment_rec  => l_party_usg_assignment_rec,
            p_object_version_number     => l_object_version_number,
            p_old_object_version_number => null,
            p_status                    => l_status
          );

Line: 1200

      insert_row (
        p_party_usg_assignment_rec  => p_party_usg_assignment_rec
      );

Line: 1217

 * PRIVATE PROCEDURE do_update_usg_assignment
 *
 * DESCRIPTION
 *     Private procedure to update party usage assignment
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * MODIFICATION HISTORY
 *
 *   05/01/05      Jianying Huang     o Created.
 *
 */

PROCEDURE do_update_usg_assignment (
    p_validation_level            IN     NUMBER,
    p_usg_assignment_id_tbl       IN     NUMBER15_TBL,
    p_party_usg_assignment_rec    IN OUT NOCOPY party_usg_assignment_rec_type,
    p_old_usg_assignment_rec_tbl  IN     ASSIGNMENT_REC_TBL,
    x_return_status               IN OUT NOCOPY VARCHAR2
) IS

    c_api_name                    CONSTANT VARCHAR2(30) := 'do_update_usg_assignment';

Line: 1245

    SELECT party_usg_assignment_id,
           party_usage_code,
           effective_start_date
    FROM   hz_party_usg_assignments
    WHERE  party_id = p_party_id
    AND    status_flag = 'A'
    AND    trunc(sysdate) between
             effective_start_date and effective_end_date;

Line: 1323

          p_create_update_flag        => 'U',
          p_validation_level          => p_validation_level,
          p_party_usg_assignment_rec  => p_party_usg_assignment_rec,
          p_old_usg_assignment_rec    => p_old_usg_assignment_rec_tbl(i),
          x_return_status             => x_return_status
        );

Line: 1373

            update_row (
              p_party_usg_assignment_id   => l_party_usg_assignment_id_tbl(j),
              p_party_usg_assignment_rec  => l_party_usg_assignment_rec,
              p_object_version_number     => l_object_version_number1,
              p_old_object_version_number => null,
              p_status                    => l_status
            );

Line: 1400

        update_row (
          p_party_usg_assignment_id   => p_usg_assignment_id_tbl(i),
          p_party_usg_assignment_rec  => p_party_usg_assignment_rec,
          p_object_version_number     => l_object_version_number,
          p_old_object_version_number => null,
          p_status                    => null
        );

Line: 1418

END do_update_usg_assignment;

Line: 1444

    SELECT party_usage_code,
           party_usage_type,
           status_flag,
           restrict_manual_assign_flag,
           restrict_manual_update_flag,
           created_by
    FROM   hz_party_usages_b;

Line: 1454

    SELECT null
    FROM   hz_party_usage_rules
    WHERE  (party_usage_rule_type = 'EXCLUSIVE' OR
            party_usage_rule_type = 'CANNOT_COEXIST')
    AND    trunc(sysdate) between
             effective_start_date AND effective_end_date
    AND    rownum = 1;

Line: 1463

    SELECT party_usage_rule_type||'##'||
           party_usage_code||'##'||
           related_party_usage_code
    FROM   hz_party_usage_rules
    WHERE  trunc(sysdate) between
             effective_start_date AND effective_end_date;

Line: 1472

    SELECT lookup_code, created_by
    FROM   fnd_lookup_values
    WHERE  lookup_type = 'HZ_CREATED_BY_MODULES'
    AND    view_application_id = 222
    AND    language = userenv('LANG')
    AND    enabled_flag = 'Y'
    AND    trunc(sysdate) BETWEEN
            trunc(nvl(start_date_active, sysdate)) AND
            trunc(nvl(end_date_active, sysdate));

Line: 1486

    l_restrict_manual_update_tbl  VARCHAR100_TBL;

Line: 1505

    G_PARTY_USAGE_CODES.delete;

Line: 1506

    G_CREATED_BY_MODULES.delete;

Line: 1507

    G_PARTY_USAGE_RULES.delete;

Line: 1518

      l_restrict_manual_update_tbl, l_party_usage_created_by_tbl;

Line: 1526

        l_restrict_manual_update_tbl(i)||'##'||
        l_party_usage_created_by_tbl(i);

Line: 1587

 * PROCEDURE insert_row
 *
 * DESCRIPTION
 *     Insert a new assignment.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   05/01/05      Jianying Huang     o Created.
 *
 */

PROCEDURE insert_row (
    p_party_usg_assignment_rec    IN     party_usg_assignment_rec_type
) IS

    c_api_name                    CONSTANT VARCHAR2(30) := 'insert_row';

Line: 1622

    HZ_PARTY_USG_ASSIGNMENTS_PKG.insert_row (
      x_party_id                  => p_party_usg_assignment_rec.party_id,
      x_party_usage_code          => p_party_usg_assignment_rec.party_usage_code,
      x_effective_start_date      => p_party_usg_assignment_rec.effective_start_date,
      x_effective_end_date        => p_party_usg_assignment_rec.effective_end_date,
      x_status_flag               => 'A',
      x_comments                  => p_party_usg_assignment_rec.comments,
      x_owner_table_name          => p_party_usg_assignment_rec.owner_table_name,
      x_owner_table_id            => p_party_usg_assignment_rec.owner_table_id,
      x_attribute_category        => p_party_usg_assignment_rec.attribute_category,
      x_attribute1                => p_party_usg_assignment_rec.attribute1,
      x_attribute2                => p_party_usg_assignment_rec.attribute2,
      x_attribute3                => p_party_usg_assignment_rec.attribute3,
      x_attribute4                => p_party_usg_assignment_rec.attribute4,
      x_attribute5                => p_party_usg_assignment_rec.attribute5,
      x_attribute6                => p_party_usg_assignment_rec.attribute6,
      x_attribute7                => p_party_usg_assignment_rec.attribute7,
      x_attribute8                => p_party_usg_assignment_rec.attribute8,
      x_attribute9                => p_party_usg_assignment_rec.attribute9,
      x_attribute10               => p_party_usg_assignment_rec.attribute10,
      x_attribute11               => p_party_usg_assignment_rec.attribute11,
      x_attribute12               => p_party_usg_assignment_rec.attribute12,
      x_attribute13               => p_party_usg_assignment_rec.attribute13,
      x_attribute14               => p_party_usg_assignment_rec.attribute14,
      x_attribute15               => p_party_usg_assignment_rec.attribute15,
      x_attribute16               => p_party_usg_assignment_rec.attribute16,
      x_attribute17               => p_party_usg_assignment_rec.attribute17,
      x_attribute18               => p_party_usg_assignment_rec.attribute18,
      x_attribute19               => p_party_usg_assignment_rec.attribute19,
      x_attribute20               => p_party_usg_assignment_rec.attribute20,
      x_object_version_number     => 1,
      x_created_by_module         => p_party_usg_assignment_rec.created_by_module,
      x_application_id            => fnd_global.resp_appl_id,
      x_party_usg_assignment_id   => l_party_usg_assignment_id
    );

Line: 1660

      p_create_update_flag        => 'I',
      p_party_usg_assignment_id   => l_party_usg_assignment_id);

Line: 1671

END insert_row;

Line: 1675

 * PROCEDURE update_row
 *
 * DESCRIPTION
 *     Update a new assignment.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   05/01/05      Jianying Huang     o Created.
 *
 */

PROCEDURE update_row (
    p_party_usg_assignment_id     IN     NUMBER,
    p_party_usg_assignment_rec    IN     party_usg_assignment_rec_type,
    p_object_version_number       IN OUT NOCOPY NUMBER,
    p_old_object_version_number   IN     NUMBER,
    p_status                      IN     VARCHAR2
) IS

    c_api_name                    CONSTANT VARCHAR2(30) := 'update_row';

Line: 1704

    SELECT object_version_number
    FROM   hz_party_usg_assignments
    WHERE  party_usg_assignment_id = p_party_usg_assignment_id
    FOR UPDATE NOWAIT;

Line: 1742

    HZ_PARTY_USG_ASSIGNMENTS_PKG.update_row (
      x_party_usg_assignment_id   => p_party_usg_assignment_id,
      x_party_id                  => null,
      x_party_usage_code          => null,
      x_effective_start_date      => p_party_usg_assignment_rec.effective_start_date,
      x_effective_end_date        => p_party_usg_assignment_rec.effective_end_date,
      x_status_flag               => p_status,
      x_comments                  => p_party_usg_assignment_rec.comments,
      x_owner_table_name          => null,
      x_owner_table_id            => null,
      x_attribute_category        => p_party_usg_assignment_rec.attribute_category,
      x_attribute1                => p_party_usg_assignment_rec.attribute1,
      x_attribute2                => p_party_usg_assignment_rec.attribute2,
      x_attribute3                => p_party_usg_assignment_rec.attribute3,
      x_attribute4                => p_party_usg_assignment_rec.attribute4,
      x_attribute5                => p_party_usg_assignment_rec.attribute5,
      x_attribute6                => p_party_usg_assignment_rec.attribute6,
      x_attribute7                => p_party_usg_assignment_rec.attribute7,
      x_attribute8                => p_party_usg_assignment_rec.attribute8,
      x_attribute9                => p_party_usg_assignment_rec.attribute9,
      x_attribute10               => p_party_usg_assignment_rec.attribute10,
      x_attribute11               => p_party_usg_assignment_rec.attribute11,
      x_attribute12               => p_party_usg_assignment_rec.attribute12,
      x_attribute13               => p_party_usg_assignment_rec.attribute13,
      x_attribute14               => p_party_usg_assignment_rec.attribute14,
      x_attribute15               => p_party_usg_assignment_rec.attribute15,
      x_attribute16               => p_party_usg_assignment_rec.attribute16,
      x_attribute17               => p_party_usg_assignment_rec.attribute17,
      x_attribute18               => p_party_usg_assignment_rec.attribute18,
      x_attribute19               => p_party_usg_assignment_rec.attribute19,
      x_attribute20               => p_party_usg_assignment_rec.attribute20,
      x_object_version_number     => p_object_version_number
    );

Line: 1778

      p_create_update_flag        => 'U',
      p_party_usg_assignment_id   => p_party_usg_assignment_id);

Line: 1789

END update_row;

Line: 2138

        SELECT *
        FROM   hz_party_usg_assignments
        WHERE  party_usg_assignment_id = p_party_usg_assignment_id;

Line: 2151

        SELECT *
        FROM   hz_party_usg_assignments
        WHERE  party_id = p_party_usg_assignment_rec.party_id
        AND    party_usage_code = p_party_usg_assignment_rec.party_usage_code
        AND    status_flag = 'A'
        AND    effective_end_date > trunc(sysdate);

Line: 2167

        SELECT *
        FROM   hz_party_usg_assignments
        WHERE  owner_table_name = p_party_usg_assignment_rec.owner_table_name
        AND    owner_table_id = p_party_usg_assignment_rec.owner_table_id;

Line: 2359

 * PROCEDURE update_usg_assignment
 *
 * DESCRIPTION
 *     Update party usage assignment.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *     p_init_msg_list            Initialize message stack if it is set to
 *                                fnd_api.G_TRUE. Default is fnd_api.G_FALSE.
 *     p_validation_level         Validation level. Default is full validation.
 *     p_party_usg_assignment_id  Party usage assignment Id.
 *     p_party_usg_assignment_rec Party usage assignment record.
 *   IN/OUT:
 *   OUT:
 *     x_return_status            Return status after the call. The status can
 *                                be fnd_api.G_RET_STS_SUCCESS (success),
 *                                fnd_api.G_RET_STS_ERROR (error),
 *                                fnd_api.G_RET_STS_UNEXP_ERROR (unexpected error).
 *     x_msg_count                Number of messages in message stack.
 *     x_msg_data                 Message text if x_msg_count is 1.
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   05/01/05      Jianying Huang     o Created.
 *
 */

PROCEDURE update_usg_assignment (
    p_init_msg_list               IN     VARCHAR2,
    p_validation_level            IN     NUMBER,
    p_party_usg_assignment_id     IN     NUMBER,
    p_party_usg_assignment_rec    IN     party_usg_assignment_rec_type,
    x_return_status               OUT    NOCOPY VARCHAR2,
    x_msg_count                   OUT    NOCOPY NUMBER,
    x_msg_data                    OUT    NOCOPY VARCHAR2
) IS

    c_api_name                    CONSTANT VARCHAR2(30) := 'update_usg_assignment';

Line: 2410

    SAVEPOINT update_usg_assignment;

Line: 2458

    do_update_usg_assignment (
      p_validation_level           => l_validation_level,
      p_usg_assignment_id_tbl      => l_usg_assignment_id_tbl,
      p_party_usg_assignment_rec   => l_party_usg_assignment_rec,
      p_old_usg_assignment_rec_tbl => l_usg_assignment_rec_tbl,
      x_return_status              => x_return_status
    );

Line: 2492

      ROLLBACK TO update_usg_assignment;

Line: 2520

      ROLLBACK TO update_usg_assignment;

Line: 2547

      ROLLBACK TO update_usg_assignment;

Line: 2578

END update_usg_assignment;

Line: 2646

    update_usg_assignment (
      p_init_msg_list             => p_init_msg_list,
      p_validation_level          => p_validation_level,
      p_party_usg_assignment_id   => p_party_usg_assignment_id,
      p_party_usg_assignment_rec  => l_party_usg_assignment_rec,
      x_return_status             => x_return_status,
      x_msg_count                 => x_msg_count,
      x_msg_data                  => x_msg_data
    );

Line: 2767

    SELECT UNIQUE party_usage_code
    FROM   hz_party_usg_assignments
    WHERE  party_id = p_party_id;

Line: 3013

    SELECT null
    FROM   hz_parties p
    WHERE  p.party_name = p_party_name
    AND    p.party_type = 'ORGANIZATION'
    AND    p.party_id <> p_party_id
    AND    p.status IN ('A', 'I')
    AND    EXISTS (
             SELECT null
             FROM   hz_party_usg_assignments pu
             WHERE  pu.party_usage_code = c_supplier_code
             AND    pu.party_id = p.party_id
             AND    ROWNUM = 1)
    AND    ROWNUM = 1;