APPS.CSI_T_TXN_ATTRIBS_PVT SQL Statements

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

Line: 225

    SELECT decode( nvl(p_txn_ext_attrib_vals_rec.preserve_detail_flag,fnd_api.g_miss_char),
             fnd_api.g_miss_char, 'Y', p_txn_ext_attrib_vals_rec.preserve_detail_flag)
    INTO   l_preserve_detail_flag
    FROM   sys.dual;

Line: 247

        p_api_name => 'insert_row',
        p_pkg_name => 'csi_t_extend_attribs_pkg');

Line: 250

      csi_t_extend_attribs_pkg.insert_row(
        px_txn_attrib_detail_id => l_txn_attrib_detail_id,
        p_txn_line_detail_id    => p_txn_ext_attrib_vals_rec.txn_line_detail_id,
        p_attrib_source_id      => p_txn_ext_attrib_vals_rec.attribute_source_id,
        p_attrib_source_table   => p_txn_ext_attrib_vals_rec.attrib_source_table,
        p_attribute_value       => p_txn_ext_attrib_vals_rec.attribute_value,
        p_process_flag          => l_process_flag,
        p_active_start_date     => p_txn_ext_attrib_vals_rec.active_start_date,
        p_active_end_date       => p_txn_ext_attrib_vals_rec.active_end_date,
        p_preserve_detail_flag  => l_preserve_detail_flag,
        p_attribute1            => p_txn_ext_attrib_vals_rec.attribute1,
        p_attribute2            => p_txn_ext_attrib_vals_rec.attribute2,
        p_attribute3            => p_txn_ext_attrib_vals_rec.attribute3,
        p_attribute4            => p_txn_ext_attrib_vals_rec.attribute4,
        p_attribute5            => p_txn_ext_attrib_vals_rec.attribute5,
        p_attribute6            => p_txn_ext_attrib_vals_rec.attribute6,
        p_attribute7            => p_txn_ext_attrib_vals_rec.attribute7,
        p_attribute8            => p_txn_ext_attrib_vals_rec.attribute8,
        p_attribute9            => p_txn_ext_attrib_vals_rec.attribute9,
        p_attribute10           => p_txn_ext_attrib_vals_rec.attribute10,
        p_attribute11           => p_txn_ext_attrib_vals_rec.attribute11,
        p_attribute12           => p_txn_ext_attrib_vals_rec.attribute12,
        p_attribute13           => p_txn_ext_attrib_vals_rec.attribute13,
        p_attribute14           => p_txn_ext_attrib_vals_rec.attribute14,
        p_attribute15           => p_txn_ext_attrib_vals_rec.attribute15,
        p_created_by            => g_user_id,
        p_creation_date         => sysdate,
        p_last_updated_by       => g_user_id,
        p_last_update_date      => sysdate,
        p_last_update_login     => g_login_id,
        p_object_version_number => 1.0,
        p_context               => p_txn_ext_attrib_vals_rec.context);

Line: 287

           'csi_t_extend_attribs_pkg.insert_row Failed. '||substr(sqlerrm,1,200));

Line: 346

  PROCEDURE update_txn_ext_attrib_dtls(
    p_api_version             IN  number,
    p_commit                  IN  varchar2 := fnd_api.g_false,
    p_init_msg_list           IN  varchar2 := fnd_api.g_false,
    p_validation_level        IN  number   := fnd_api.g_valid_level_full,
    p_txn_ext_attrib_vals_tbl IN  csi_t_datastructures_grp.txn_ext_attrib_vals_tbl,
    x_return_status           OUT NOCOPY varchar2,
    x_msg_count               OUT NOCOPY number,
    x_msg_data                OUT NOCOPY varchar2)
  IS

    l_ea_rec                  csi_t_datastructures_grp.txn_ext_attrib_vals_rec;

Line: 361

    l_api_name       CONSTANT varchar2(30)  := 'update_txn_ext_attrib_dtls';

Line: 366

    l_update_ext_attribs      BOOLEAN ;

Line: 371

      SELECT *
      FROM   csi_t_extend_attribs
      where  txn_attrib_detail_id = p_attrib_dtl_id;

Line: 379

    SELECT txn_attrib_detail_id
    FROM   csi_t_extend_attribs
    WHERE  txn_line_detail_id  = c_txn_line_detail_id
    AND    attrib_source_id    = c_attrib_source_id
    AND    attrib_source_table = c_attrib_source_table ;

Line: 388

    SAVEPOINT update_txn_ext_attrib_dtls;

Line: 676

              p_api_name => 'update_row',
              p_pkg_name => 'csi_t_extend_attribs_pkg');

Line: 679

            csi_t_extend_attribs_pkg.update_row(
              p_txn_attrib_detail_id      => l_ea_rec.txn_attrib_detail_id,
              p_txn_line_detail_id        => l_ea_rec.txn_line_detail_id,
              p_attrib_source_id          => l_ea_rec.attribute_source_id,
              p_attrib_source_table       => l_ea_rec.attrib_source_table,
              p_attribute_value           => l_ea_rec.attribute_value,
              p_process_flag              => l_ea_rec.process_flag,
              p_active_start_date         => l_ea_rec.active_start_date,
              p_active_end_date           => l_ea_rec.active_end_date,
              p_preserve_detail_flag      => l_ea_rec.preserve_detail_flag,
              p_attribute1                => l_ea_rec.attribute1 ,
              p_attribute2                => l_ea_rec.attribute2 ,
              p_attribute3                => l_ea_rec.attribute3 ,
              p_attribute4                => l_ea_rec.attribute4 ,
              p_attribute5                => l_ea_rec.attribute5 ,
              p_attribute6                => l_ea_rec.attribute6 ,
              p_attribute7                => l_ea_rec.attribute7 ,
              p_attribute8                => l_ea_rec.attribute8 ,
              p_attribute9                => l_ea_rec.attribute9 ,
              p_attribute10               => l_ea_rec.attribute10 ,
              p_attribute11               => l_ea_rec.attribute11 ,
              p_attribute12               => l_ea_rec.attribute12 ,
              p_attribute13               => l_ea_rec.attribute13 ,
              p_attribute14               => l_ea_rec.attribute14 ,
              p_attribute15               => l_ea_rec.attribute15 ,
              p_created_by                => fnd_api.g_miss_num,
              p_creation_date             => fnd_api.g_miss_date,
              p_last_updated_by           => g_user_id,
              p_last_update_date          => sysdate,
              p_last_update_login         => g_login_id,
              p_object_version_number     => l_ea_rec.object_version_number,
              p_context                   => l_ea_rec.context);

Line: 716

                 'csi_t_extend_attribs_pkg.update_row Failed. '||substr(sqlerrm,1,200));

Line: 737

      ROLLBACK TO update_txn_ext_attrib_dtls;

Line: 745

      ROLLBACK TO update_txn_ext_attrib_dtls;

Line: 754

      ROLLBACK TO update_txn_ext_attrib_dtls;

Line: 770

  END update_txn_ext_attrib_dtls;

Line: 772

  PROCEDURE delete_txn_ext_attrib_dtls
  (
     p_api_version             IN  NUMBER
    ,p_commit                  IN  VARCHAR2 := fnd_api.g_false
    ,p_init_msg_list           IN  VARCHAR2 := fnd_api.g_false
    ,p_validation_level        IN  NUMBER   := fnd_api.g_valid_level_full
    ,p_txn_ext_attrib_ids_tbl  IN  csi_t_datastructures_grp.txn_ext_attrib_ids_tbl
    ,x_return_status           OUT NOCOPY VARCHAR2
    ,x_msg_count               OUT NOCOPY NUMBER
    ,x_msg_data                OUT NOCOPY VARCHAR2
  )
  IS

    l_api_name       CONSTANT VARCHAR2(30)  := 'delete_txn_ext_attrib_dtls';

Line: 795

      SELECT txn_attrib_detail_id
      FROM   csi_t_extend_attribs
      WHERE  txn_line_detail_id = p_line_dtl_id;

Line: 802

    SAVEPOINT delete_txn_ext_attrib_dtls;

Line: 863

            p_api_name => 'delete_row',
            p_pkg_name => 'csi_t_extend_attribs_pkg');

Line: 866

          csi_t_extend_attribs_pkg.delete_row(
            p_txn_attrib_detail_id => l_ext_att_id);

Line: 896

              p_api_name => 'delete_row',
              p_pkg_name => 'csi_t_extend_attribs_pkg');

Line: 899

            csi_t_extend_attribs_pkg.delete_row(
              p_txn_attrib_detail_id => ea_rec.txn_attrib_detail_id);

Line: 923

      ROLLBACK TO delete_txn_ext_attrib_dtls;

Line: 931

      ROLLBACK TO delete_txn_ext_attrib_dtls;

Line: 940

      ROLLBACK TO delete_txn_ext_attrib_dtls;

Line: 956

  END delete_txn_ext_attrib_dtls;

Line: 964

    l_select_stmt      varchar2(2000);

Line: 972

    l_select_stmt :=
     'select attribute_value_id, instance_id,attribute_id, attribute_value,'||
     ' active_start_date, active_end_date, context, attribute1, attribute2,'||
     ' attribute3, attribute4, attribute5, attribute6, attribute7,'||
     ' attribute8, attribute9, attribute10, attribute11, attribute12,'||
     ' attribute13, attribute14, attribute15, object_version_number '||
     'from csi_iea_values '||
     'where instance_id = :instance_id';

Line: 983

    dbms_sql.parse(l_iea_cur_id, l_select_stmt , dbms_sql.native);

Line: 1062

    l_select_stmt      varchar2(2000);

Line: 1073

    l_select_stmt :=
     'select attribute_id, attribute_level, master_organization_id,'||
     ' inventory_item_id, item_category_id, instance_id, attribute_code,'||
     ' attribute_name, attribute_category, description, active_start_date,'||
     ' active_end_date, context, attribute1, attribute2, attribute3,'||
     ' attribute4, attribute5, attribute6, attribute7, attribute8,'||
     ' attribute9, attribute10, attribute11, attribute12, attribute13,'||
     ' attribute14, attribute15, object_version_number '||
     'from csi_i_extended_attribs '||
     'where attribute_level = ''GLOBAL'' '||
     'union '||
     'select attribute_id, attribute_level, master_organization_id,'||
     ' inventory_item_id, item_category_id, instance_id, attribute_code,'||
     ' attribute_name, attribute_category, description, active_start_date,'||
     ' active_end_date, context, attribute1, attribute2, attribute3,'||
     ' attribute4, attribute5, attribute6, attribute7, attribute8,'||
     ' attribute9, attribute10, attribute11, attribute12, attribute13,'||
     ' attribute14, attribute15, object_version_number '||
     'from csi_i_extended_attribs '||
     'where (inventory_item_id, master_organization_id) in '||
     '      (select inventory_item_id,inv_organization_id '||
     '       from   csi_t_txn_line_details'||
     '       where  txn_line_detail_id = :line_dtl_id '||
     '       and    instance_exists_flag = ''N'') ';

Line: 1100

     'select attribute_id, attribute_level, master_organization_id,'||
     ' inventory_item_id, item_category_id, instance_id, attribute_code,'||
     ' attribute_name, attribute_category, description, active_start_date,'||
     ' active_end_date, context, attribute1, attribute2, attribute3,'||
     ' attribute4, attribute5, attribute6, attribute7, attribute8,'||
     ' attribute9, attribute10, attribute11, attribute12, attribute13,'||
     ' attribute14, attribute15, object_version_number '||
     'from csi_i_extended_attribs '||
     'where instance_id = :instance_id';

Line: 1111

      l_select_stmt := l_select_stmt||l_inst_based_stmt;

Line: 1115

    dbms_sql.parse(l_ea_cur_id, l_select_stmt , dbms_sql.native);

Line: 1211

    l_select_stmt      varchar2(2000);

Line: 1219

    l_select_stmt :=
     'select txn_attrib_detail_id, txn_line_detail_id, attrib_source_table,
             attrib_source_id, attribute_value, process_flag,
             active_start_date, active_end_date, preserve_detail_flag,
             context, attribute1, attribute2, attribute3, attribute4,
             attribute5, attribute6, attribute7, attribute8, attribute9,
             attribute10, attribute11, attribute12, attribute13, attribute14,
             attribute15, object_version_number
      from   csi_t_extend_attribs
      where txn_line_detail_id = :line_dtl_id';

Line: 1232

    dbms_sql.parse(l_ea_cur_id, l_select_stmt , dbms_sql.native);

Line: 1333

           l_tmp_ceav_tbl.delete;

Line: 1391

        l_tmp_cea_tbl.DELETE;

Line: 1438

        l_tmp_teav_tbl.delete;

Line: 1484

SELECT attribute_id
FROM csi_i_extended_attribs a
WHERE attribute_level = 'GLOBAL'
AND   attribute_code = c_attribute_code
AND  (a.active_end_date is NULL  OR
      trunc(a.active_end_date) > l_sysdate) ;

Line: 1493

SELECT a.attribute_id, b.inventory_item_id, b.inv_organization_id
FROM csi_i_extended_attribs a ,
     csi_t_txn_line_details b,
     mtl_parameters mtp       -- Added for bug 4118180
WHERE a.attribute_level = 'ITEM'
AND  a.attribute_code = c_attribute_code
AND  a.inventory_item_id = b.inventory_item_id
AND  b.inv_organization_id = mtp.organization_id           -- Modified for bug 4118180
AND  a.master_organization_id = mtp.master_organization_id -- Modified for bug 4118180
AND  b.txn_line_detail_id = p_txn_line_detail_id
AND  (a.active_end_date is NULL  OR
      trunc(a.active_end_date) > l_sysdate) ;

Line: 1509

SELECT  a.attribute_id, b.inventory_item_id, b.inv_organization_id
	, a.instance_id
FROM csi_i_extended_attribs a,
     csi_t_txn_line_details b
WHERE a.attribute_level = 'INSTANCE'
AND  a.attribute_code = c_attribute_code
AND  b.config_inst_hdr_id IS NULL
AND  b.txn_line_detail_id = p_txn_line_detail_id
AND  a.instance_id = b.instance_id
AND  (a.active_end_date is NULL  OR
      trunc(a.active_end_date) > l_sysdate)
UNION
---FOR CZ
SELECT a.attribute_id, b.inventory_item_id, b.inv_organization_id
       , c.instance_id
FROM csi_i_extended_attribs a,
     csi_t_txn_line_details b,
     csi_item_instances c
WHERE a.attribute_level = 'INSTANCE'
AND  a.attribute_code = c_attribute_code
AND  b.config_inst_hdr_id IS NOT NULL
AND  b.config_inst_hdr_id = c.config_inst_hdr_id
AND  b.config_inst_rev_num = c.config_inst_rev_num
AND  b.config_inst_item_id = c.config_inst_item_id
AND b.txn_line_detail_id = p_txn_line_detail_id
AND  (a.active_end_date is NULL  OR
      trunc(a.active_end_date) > l_sysdate) ;

Line: 1539

SELECT csie.attribute_id, csie.item_category_id,
       ctld.inventory_item_id, ctld.inv_organization_id
FROM mtl_item_categories mti,
     mtl_categories_b mtc ,
     --csi_install_parameters csii,
     csi_i_extended_attribs csie ,
     csi_t_txn_line_details ctld
WHERE mti.organization_id = ctld.inv_organization_id -- Bug 4306650. Typo:inv_orgn and item_id swap
AND   mti.inventory_item_id = ctld.inventory_item_id -- Bug 4306650. Typo:inv_orgn and item_id swap
AND   mti.category_set_id = c_category_set_id -- csii.category_set_id
AND   mti.category_id = mtc.category_id
AND   (mtc.disable_date is NULL OR TRUNC(mtc.disable_date) > TRUNC(SYSDATE))
AND   csie.item_category_id = mti.category_id
AND   csie.attribute_level = 'CATEGORY'
AND   attribute_code = c_attribute_code
AND   ctld.txn_line_detail_id = p_txn_line_detail_id
AND  (csie.active_end_date is NULL  OR
      trunc(csie.active_end_date) > l_sysdate) ;

Line: 1560

SELECT instance_id
FROM   csi_t_txn_line_details
WHERE  txn_line_detail_id = p_txn_line_detail_id
AND    config_inst_hdr_id IS NULL
UNION
SELECT b.instance_id
FROM   csi_t_txn_line_details a,
       csi_item_instances b
WHERE  a.txn_line_detail_id = p_txn_line_detail_id
AND    a.config_inst_hdr_id IS NOT NULL
AND    a.config_inst_hdr_id  = b.config_inst_hdr_id
AND    a.config_inst_rev_num = b.config_inst_rev_num
AND    a.config_inst_item_id = b.config_inst_item_id
AND    a.txn_line_detail_id = p_txn_line_detail_id
AND  (b.active_end_date is NULL  OR
      trunc(b.active_end_date) > l_sysdate) ;

Line: 1580

SELECT attribute_value_id
FROM   csi_iea_values
WHERE  instance_id = c_instance_id
AND    attribute_id = c_attrib_source_id
AND  (active_end_date is NULL  OR
      trunc(active_end_date) > l_sysdate) ;

Line: 1603

   SELECT TRUNC(SYSDATE) INTO l_sysdate FROM dual ;