APPS.CSI_ML_INTERFACE_TXN_PVT SQL Statements

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

Line: 14

  SELECT instance_id FROM csi_instance_interface
  WHERE inst_interface_id = p_interface_id;

Line: 18

   SELECT cii.instance_id
   FROM csi_item_instances csi,
        csi_instance_interface cii
   WHERE cii.inst_interface_id = p_interface_id
   AND   csi.inventory_item_id = cii.inventory_item_id
   AND   csi.serial_number = cii.serial_number;

Line: 26

   SELECT a.instance_id
   FROM   csi_item_instances a,
          csi_i_parties b,
          csi_instance_interface c,
          csi_i_party_interface d
   WHERE  a.instance_id = b.instance_id
   AND    a.inventory_item_id = c.inventory_item_id
   AND    c.inst_interface_id = d.inst_interface_id
   AND    c.inst_interface_id = p_interface_id
   AND    a.instance_usage_code NOT IN ('IN_RELATIONSHIP','RETURNED')
   AND    ( (a.inventory_revision IS NULL AND c.inventory_revision IS NULL) OR (a.inventory_revision IS NULL AND c.inventory_revision = FND_API.G_MISS_CHAR) OR (a.inventory_revision = c.inventory_revision))
   AND    ( (a.lot_number IS NULL AND c.lot_number IS NULL) OR (a.lot_number IS NULL AND c.lot_number = FND_API.G_MISS_CHAR) OR (a.lot_number = c.lot_number))
   AND    a.inv_organization_id  = c.inv_organization_id
   AND    a.inv_subinventory_name = c.inv_subinventory_name
   AND    ( (a.inv_locator_id IS NULL AND c.inv_locator_id IS NULL) OR (a.inv_locator_id IS NULL AND c.inv_locator_id = FND_API.G_MISS_NUM) OR (a.inv_locator_id = c.inv_locator_id))
   AND    b.party_id  = d.party_id
   AND    b.party_source_table  = d.party_source_table
   AND    b.relationship_type_code = 'OWNER';

Line: 46

       select msi.serial_number_control_code
       from   mtl_system_items msi,
              csi_instance_interface cii
       where  msi.inventory_item_id = cii.inventory_item_id
       and    msi.organization_id   = cii.inv_organization_id
       and    cii.inst_interface_id = p_interface_id;

Line: 84

  SELECT *
  FROM csi_ii_relationships
  WHERE subject_id = p_sub_id
  AND   object_id = p_ob_id;

Line: 206

  SELECT distinct transaction_identifier
  FROM   csi_instance_interface cii
  WHERE  (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch,cii.batch_name)
          OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch,'$CSI_NULL_VALUE$'))
  AND    cii.source_system_name = p_source_system
  AND    trunc(cii.source_transaction_date) BETWEEN nvl(l_txn_from_date,trunc(cii.source_transaction_date)) AND nvl(l_txn_to_date,trunc(cii.source_transaction_date))
  AND    cii.process_status = 'R';

Line: 216

  SELECT cii.*
  FROM   csi_instance_interface cii
  WHERE  cii.transaction_identifier = p_txn_ident
  AND    cii.source_system_name = p_source_system
  AND    cii.process_status = 'R';

Line: 223

  SELECT cpi.*
  FROM   csi_i_party_interface cpi
  WHERE  cpi.inst_interface_id = p_inst_interface_id;

Line: 228

  SELECT ci.*
  FROM   csi_iea_value_interface ci
  WHERE  ci.inst_interface_id = p_inst_interface_id;

Line: 233

  SELECT cia.*
  FROM   csi_i_asset_interface cia
  WHERE  cia.inst_interface_id = p_inst_interface_id; --bnarayan added for open interfaces R12

Line: 240

 SELECT ciri.relationship_type_code relationship_type_code,
        ciri.subject_interface_id subject_interface_id,
        ciri.object_interface_id  object_interface_id,
        ciri.position_reference position_reference,
        ciri.relationship_start_date active_start_date,
        ciri.relationship_end_date active_end_date,
        ciri.display_order display_order,
        ciri.mandatory_flag mandatory_flag,
        ciri.context context,
        ciri.attribute1 attribute1,
        ciri.attribute2 attribute2,
        ciri.attribute3 attribute3,
        ciri.attribute4 attribute4,
        ciri.attribute5 attribute5,
        ciri.attribute6 attribute6,
        ciri.attribute7 attribute7,
        ciri.attribute8 attribute8,
        ciri.attribute9 attribute9,
        ciri.attribute10 attribute10,
        ciri.attribute11 attribute11,
        ciri.attribute12 attribute12,
        ciri.attribute13 attribute13,
        ciri.attribute14 attribute14,
        ciri.attribute15 attribute15,
        ciri.relationship_direction,
        ciri.created_by created_by,
        cii1.instance_id new_subject_id,
        cii2.instance_id new_object_id,
        cii1.source_transaction_date source_transaction_date,
        cii1.transaction_identifier transaction_identifier
 FROM   csi_ii_relation_interface ciri,
        csi_instance_interface cii1,
        csi_instance_interface cii2
 WHERE  ciri.subject_interface_id = cii1.inst_interface_id
 AND    cii1.transaction_identifier = p_txn_ident
 AND    cii1.source_system_name = p_source_system
 AND    ciri.object_interface_id = cii2.inst_interface_id
 AND    cii2.transaction_identifier = p_txn_ident
 AND    cii2.source_system_name = p_source_system
 AND    cii1.process_status IN ('P')
 AND    cii2.process_status IN ('P'); */

Line: 283

 SELECT ciri.rel_interface_id rel_interface_id,
        ciri.relationship_type_code relationship_type_code,
        ciri.subject_interface_id subject_interface_id,
        ciri.object_interface_id  object_interface_id,
        ciri.position_reference position_reference,
        ciri.relationship_start_date active_start_date,
        ciri.relationship_end_date active_end_date,
        ciri.display_order display_order,
        ciri.mandatory_flag mandatory_flag,
        ciri.context context,
        ciri.attribute1 attribute1,
        ciri.attribute2 attribute2,
        ciri.attribute3 attribute3,
        ciri.attribute4 attribute4,
        ciri.attribute5 attribute5,
        ciri.attribute6 attribute6,
        ciri.attribute7 attribute7,
        ciri.attribute8 attribute8,
        ciri.attribute9 attribute9,
        ciri.attribute10 attribute10,
        ciri.attribute11 attribute11,
        ciri.attribute12 attribute12,
        ciri.attribute13 attribute13,
        ciri.attribute14 attribute14,
        ciri.attribute15 attribute15,
        ciri.relationship_direction,
        ciri.created_by created_by,
        ciri.subject_id subject_id,
        ciri.object_id object_id
 FROM   csi_ii_relation_interface ciri
 WHERE  ciri.process_status IN ('R')
 AND    (nvl(ciri.source_system_name, '$CSI_NULL_VALUE$') = nvl(p_source_system_name, '$CSI_NULL_VALUE$')
         or nvl(ciri.source_system_name, '$CSI_NULL_VALUE$') = nvl(p_source_system_name, ciri.source_system_name)
        )/*Added for 6443959*/;

Line: 320

 SELECT internal_party_id FROM csi_install_parameters;

Line: 322

PROCEDURE UPDATE_INTERFACE_TBL
   (p_instance_tbl      IN csi_datastructures_pub.instance_tbl
   ,p_grp_error_tbl     IN csi_datastructures_pub.grp_error_tbl)
IS
  --
  l_intf_id_array                 dbms_sql.Number_Table;

Line: 349

        l_upd_stmt := 'UPDATE CSI_INSTANCE_INTERFACE
                     SET error_text = :error_text
                        ,process_status = :status
                     WHERE inst_interface_id = :intf_id';

Line: 365

END UPDATE_INTERFACE_TBL;

Line: 367

PROCEDURE UPDATE_INTERFACE_TBL
   (p_instance_tbl      IN csi_datastructures_pub.instance_tbl
   ,p_grp_upd_error_tbl     IN csi_datastructures_pub.grp_upd_error_tbl)
IS
  --
  l_intf_id_array                 dbms_sql.Number_Table;

Line: 394

        l_upd_stmt := 'UPDATE CSI_INSTANCE_INTERFACE
                     SET error_text = :error_text
                        ,process_status = :status
                     WHERE inst_interface_id = :intf_id';

Line: 410

END UPDATE_INTERFACE_TBL;

Line: 443

   UPDATE CSI_INSTANCE_INTERFACE a
   SET a.instance_id = (SELECT b.instance_id
                        FROM csi_item_instances b
                        WHERE a.instance_number = b.instance_number)
   WHERE a.instance_number IS NOT NULL
   and a.instance_id is null
    AND a.SOURCE_SYSTEM_NAME = nvl(p_source_system_name, a.SOURCE_SYSTEM_NAME); --Added this condition for #6443959

Line: 456

     UPDATE CSI_INSTANCE_INTERFACE cii
     SET    error_text =l_error_message , process_status ='E'
     WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
        OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
     AND   cii.source_system_name = p_source_system_name
     AND   trunc(cii.source_transaction_date) BETWEEN nvl(l_txn_from_date,trunc(cii.source_transaction_date)) AND nvl(l_txn_to_date,trunc(cii.source_transaction_date))
     AND   cii.process_status = 'R'
     AND   cii.location_type_code in ('INVENTORY','PO','IN_TRANSIT','WIP','PROJECT');

Line: 496

       UPDATE CSI_INSTANCE_INTERFACE cii
       SET    error_text     =l_error_message
              ,process_status ='E'
        WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
        OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
        AND   cii.source_system_name = p_source_system_name
        AND   trunc(cii.source_transaction_date) BETWEEN nvl(l_txn_from_date,trunc(cii.source_transaction_date)) AND nvl(l_txn_to_date,trunc(cii.source_transaction_date))
        AND   cii.process_status = 'R'
        AND   exists ( SELECT 1
                    FROM   csi_i_party_interface cipi
                           ,csi_i_asset_interface ciai
                     WHERE  cipi.inst_interface_id = ciai.inst_interface_id
                     AND    cipi.inst_interface_id = cii.inst_interface_id
                     AND    nvl(cipi.party_id,0) <> g_int_party
                     AND    cipi.party_relationship_type_code = 'OWNER'
                   );

Line: 523

        UPDATE CSI_INSTANCE_INTERFACE cii
        SET  error_text      =l_error_message
             ,process_status ='E'
        WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
        OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
        AND   cii.source_system_name = p_source_system_name
        AND   trunc(cii.source_transaction_date) BETWEEN nvl(l_txn_from_date,trunc(cii.source_transaction_date)) AND nvl(l_txn_to_date,trunc(cii.source_transaction_date))
        AND   cii.process_status = 'R'
        AND   exists (SELECT 1
                         FROM csi_i_asset_interface ciai
                        WHERE cii.inst_interface_id = ciai.inst_interface_id
                          AND ciai.fa_asset_id IS NULL
                          AND ciai.fa_asset_number IS NULL
                         );

Line: 549

        UPDATE CSI_INSTANCE_INTERFACE cii
        SET  error_text      =l_error_message
             ,process_status ='E'
        WHERE (NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,cii.batch_name)
        OR NVL(cii.batch_name,'$CSI_NULL_VALUE$')=NVL(p_batch_name,'$CSI_NULL_VALUE$'))
        AND   cii.source_system_name = p_source_system_name
        AND   trunc(cii.source_transaction_date) BETWEEN nvl(l_txn_from_date,trunc(cii.source_transaction_date)) AND nvl(l_txn_to_date,trunc(cii.source_transaction_date))
        AND   cii.process_status = 'R'
        AND   cii.location_type_code  IN ('HZ_PARTY_SITES','HZ_LOCATIONS')
        AND   (exists (SELECT 1
                    FROM   csi_i_party_interface cipi
                    WHERE  cipi.inst_interface_id = cii.inst_interface_id
                    AND    nvl(cipi.party_id,0)   = g_int_party
                    AND    cipi.party_relationship_type_code = 'OWNER'
                   )
              AND   not exists (SELECT 1
                                FROM csi_i_asset_interface ciai
                                WHERE  cii.inst_interface_id = ciai.inst_interface_id
                         ));

Line: 582

     c_instance_tbl.DELETE;

Line: 583

     c_ext_attrib_tbl.DELETE;

Line: 584

     c_party_tbl.DELETE;

Line: 585

     c_party_contact_tbl.DELETE;

Line: 586

     c_account_tbl.DELETE;

Line: 587

     c_price_tbl.DELETE;

Line: 588

     c_org_assign_tbl.DELETE;

Line: 589

     c_asset_assignment_tbl.DELETE;

Line: 590

     c_txn_tbl.DELETE;

Line: 591

     c_grp_error_tbl.DELETE;

Line: 594

     u_instance_tbl.DELETE;

Line: 595

     u_ext_attrib_tbl.DELETE;

Line: 596

     u_party_tbl.DELETE;

Line: 597

     u_account_tbl.DELETE;

Line: 598

     u_price_tbl.DELETE;

Line: 599

     u_org_assignments_tbl.DELETE;

Line: 600

     u_asset_assignment_tbl.DELETE;

Line: 603

     u_grp_error_tbl.DELETE;

Line: 604

     u_instance_id_lst.DELETE;

Line: 1794

            IF iasset_iface_rec.update_status IS NULL THEN
               c_asset_assignment_tbl( asset_idx ).update_status := l_fnd_g_char;

Line: 1797

               c_asset_assignment_tbl( asset_idx ).update_status := iasset_iface_rec.update_status ;

Line: 2768

     ELSE -- update candidate
       IF(l_debug_level>1) THEN
        FND_File.Put_Line(Fnd_File.LOG,'CASE -update item instance id: '||l_instance_id);

Line: 2776

        update csi_instance_interface
        set instance_id = l_instance_id
        where inst_interface_id = iface_Det_rec.inst_interface_id;

Line: 2801

        FND_File.Put_Line(Fnd_File.LOG,'Resolving Update Related Ids:');

Line: 2803

        csi_ml_util_pvt.resolve_update_ids
             (p_source_system_name => p_source_system_name,
              p_txn_identifier => get_txns_rec.transaction_identifier,
              x_return_status => l_return_status,
              x_error_message => l_error_message);

Line: 2811

            FND_File.Put_Line(Fnd_File.LOG,'Error from csi_ml_util_pvt.resolve_update_ids');

Line: 2817

        csi_ml_update_pvt.populate_recs(
                  p_txn_identifier =>u_txn_rec.source_group_ref, --iface_det_rec.transaction_identifier,
                  p_source_system_name =>p_source_system_name,   -- modified txn_identifer for open
                  x_instance_tbl => u_instance_tbl,
                  x_party_tbl => u_party_tbl,
                  x_account_tbl => u_account_tbl,
                  x_ext_attrib_value_tbl => u_ext_attrib_tbl,
                  x_price_tbl => u_price_tbl,
                  x_org_assign_tbl => u_org_assignments_tbl,
                  x_asset_assignment_tbl => u_asset_assignment_tbl, -- bnarayan added for R12
                  x_return_status => l_return_status,
                  x_error_message=> l_error_message);

Line: 2834

            FND_File.Put_Line(Fnd_File.LOG,'Error from csi_ml_update_pvt.populate_recs ');

Line: 2918

        csi_item_instance_grp.update_item_instance (
 	  p_api_version           => l_api_version
   	 ,p_commit                => l_commit
         ,p_init_msg_list         => l_init_msg_list
         ,p_validation_level      => l_validation_level
   	 ,p_instance_tbl          => u_instance_tbl
   	 ,p_ext_attrib_values_tbl => u_ext_attrib_tbl
   	 ,p_party_tbl             => u_party_tbl
   	 ,p_account_tbl           => u_account_tbl
   	 ,p_pricing_attrib_tbl    => u_price_tbl
   	 ,p_org_assignments_tbl   => u_org_assignments_tbl
   	 ,p_asset_assignment_tbl  => u_asset_assignment_tbl
   	 ,p_txn_rec               => u_txn_rec
         ,x_instance_id_lst       => u_instance_id_lst
         ,p_grp_upd_error_tbl     => u_grp_error_tbl
   	 ,x_return_status         => l_return_status
   	 ,x_msg_count             => l_msg_count
   	 ,x_msg_data              => l_msg_data);

Line: 2963

             UPDATE csi_instance_interface
             SET   process_status = 'P'
             WHERE instance_id = u_instance_tbl(i).instance_id;

Line: 2972

     c_relationship_tbl.DELETE;

Line: 2973

     u_relationship_tbl.DELETE;

Line: 3148

         csi_ii_relationships_pub.update_relationship(
 	  p_api_version           => l_api_version
   	 ,p_commit                => l_commit
         ,p_init_msg_list         => l_init_msg_list
         ,p_validation_level      => l_validation_level
   	 ,p_relationship_tbl      => u_relationship_tbl
   	 ,p_txn_rec               => u_rel_txn_rec
   	 ,x_return_status         => l_return_status
   	 ,x_msg_count             => l_msg_count
   	 ,x_msg_data              => l_msg_data);

Line: 3178

      UPDATE_INTERFACE_TBL(c_instance_tbl,
                           c_grp_error_tbl);

Line: 3184

      UPDATE_INTERFACE_TBL(u_instance_tbl,
                           u_grp_error_tbl);

Line: 3192

      UPDATE csi_instance_interface
      SET process_Status = 'E',
          error_text = l_error_message
      WHERE transaction_identifier = get_txns_rec.transaction_identifier
      AND   source_system_name = p_source_system_name;

Line: 3211

     SELECT count(*)
       INTO l_found
       FROM csi_ii_relation_interface
      WHERE process_status='R';

Line: 3236

     SAVEPOINT create_update_relship;

Line: 3240

          c_relationship_tbl.DELETE;

Line: 3284

             UPDATE csi_ii_relation_interface
                SET process_status ='E'
                   ,error_text = l_Error_Message
              WHERE rel_interface_id = irel_det_rec.rel_interface_id;

Line: 3290

             UPDATE csi_ii_relation_interface
                SET process_status ='P'
              WHERE rel_interface_id = irel_det_rec.rel_interface_id;

Line: 3296

        u_relationship_tbl.DELETE;

Line: 3402

         csi_ii_relationships_pvt.update_relationship
          ( p_api_version        => l_api_version
           ,p_commit             => l_commit
           ,p_init_msg_list      => l_init_msg_list
           ,p_validation_level   => l_validation_level
           ,p_relationship_tbl   => u_relationship_tbl
           ,p_txn_rec            => u_rel_txn_rec
           ,x_return_status      => l_return_status
           ,x_msg_count          => l_msg_count
           ,x_msg_data           => l_msg_data
           );

Line: 3425

             UPDATE csi_ii_relation_interface
                SET process_status ='E'
                   ,error_text = l_Error_Message
              WHERE rel_interface_id = irel_det_rec.rel_interface_id;

Line: 3431

             UPDATE csi_ii_relation_interface
                SET process_status ='P'
              WHERE rel_interface_id = irel_det_rec.rel_interface_id;

Line: 3451

       ROLLBACK TO create_update_relship;