DBA Data[Home] [Help]

APPS.CSI_INTERFACE_PKG SQL Statements

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

Line: 64

      SELECT sub_type_id
      INTO   x_sub_type_id
      FROM   csi_txn_sub_types
      WHERE  transaction_type_id = p_transaction_type_id
      AND    default_flag        = 'Y';
Line: 117

      SELECT inventory_item_id ,
             link_to_line_id ,
             org_id
      INTO   l_inventory_item_id ,
             l_next_parent_line_id,
             l_org_id
      FROM   oe_order_lines_all
      WHERE  line_id = l_parent_line_id;
Line: 130

      SELECT nvl(msi.comms_nl_trackable_flag, 'N')
      INTO   l_ib_trackable_flag
      FROM   mtl_system_items msi
      WHERE  msi.inventory_item_id = l_inventory_item_id
      AND    msi.organization_id   = l_organization_id;
Line: 251

      SELECT line_id
      FROM   oe_order_lines_all
      WHERE  link_to_line_id   = p_parent_line_id
      ORDER BY line_number, shipment_number;
Line: 323

      l_line_tbl_nxt_lvl.delete;
Line: 335

        SELECT nvl(msi.comms_nl_trackable_flag,'N')
        INTO   l_ib_trackable_flag
        FROM   mtl_system_items msi
        WHERE  msi.inventory_item_id = l_line_tbl(l_ind).inventory_item_id
        AND    msi.organization_id   = l_organization_id;
Line: 372

      l_line_tbl.DELETE;
Line: 608

    SELECT serial_number_control_code,
           lot_control_code,
           location_control_code,
           revision_qty_control_code,
           comms_nl_trackable_flag,
           shippable_item_flag,
           inventory_item_flag,
           stock_enabled_flag,
           bom_item_type,
           pick_components_flag,
           base_item_id,
           primary_uom_code
    INTO   x_item_attrib_rec.serial_control_code,
           x_item_attrib_rec.lot_control_code,
           x_item_attrib_rec.locator_control_code,
           x_item_attrib_rec.revision_control_code,
           x_item_attrib_rec.ib_trackable_flag,
           x_item_attrib_rec.shippable_flag,
           x_item_attrib_rec.inv_item_flag,
           x_item_attrib_rec.stockable_flag,
           x_item_attrib_rec.bom_item_type,
           x_item_attrib_rec.pick_components_flag,
           x_item_attrib_rec.model_item_id,
           x_item_attrib_rec.primary_uom_code
    FROM   mtl_system_items
    WHERE  inventory_item_id = p_inventory_item_id
    AND    organization_id   = p_organization_id;
Line: 651

      select 'X'
      into l_exists
      from all_tab_columns
      where table_name = 'MTL_SYSTEM_ITEMS_B'
      and   column_name = 'IB_ITEM_INSTANCE_CLASS'
      and   OWNER       = l_oracle_schema
      and   rownum < 2;
Line: 662

    l_sql_stmt := 'select ib_item_instance_class from MTL_SYSTEM_ITEMS_B '||
                  'where inventory_item_id = :item_id '||
                  'and   organization_id = :vld_org_id';
Line: 681

      select 'X'
      into l_exists
      from all_tab_columns
      where table_name = 'MTL_SYSTEM_ITEMS_B'
      and   column_name = 'CONFIG_MODEL_TYPE'
      and   OWNER       = l_oracle_schema
      and   rownum < 2;
Line: 692

    l_sql_stmt := 'select config_model_type from MTL_SYSTEM_ITEMS_B '||
                  'where inventory_item_id = :item_id '||
                  'and   organization_id = :vld_org_id';
Line: 708

    SELECT nvl(negative_inv_receipt_code,2)
    INTO   x_item_attrib_rec.negative_balances_code
    FROM   mtl_parameters
    WHERE  organization_id = p_organization_id;
Line: 739

      SELECT pricing_context,
             pricing_attribute1,
             pricing_attribute2,
             pricing_attribute3,
             pricing_attribute4,
             pricing_attribute5,
             pricing_attribute6,
             pricing_attribute7,
             pricing_attribute8,
             pricing_attribute9,
             pricing_attribute10,
             pricing_attribute11,
             pricing_attribute12,
             pricing_attribute13,
             pricing_attribute14,
             pricing_attribute15,
             pricing_attribute16,
             pricing_attribute17,
             pricing_attribute18,
             pricing_attribute19,
             pricing_attribute20,
             pricing_attribute21,
             pricing_attribute22,
             pricing_attribute23,
             pricing_attribute24,
             pricing_attribute25,
             pricing_attribute26,
             pricing_attribute27,
             pricing_attribute28,
             pricing_attribute29,
             pricing_attribute30,
             pricing_attribute31,
             pricing_attribute32,
             pricing_attribute33,
             pricing_attribute34,
             pricing_attribute35,
             pricing_attribute36,
             pricing_attribute37,
             pricing_attribute38,
             pricing_attribute39,
             pricing_attribute40,
             pricing_attribute41,
             pricing_attribute42,
             pricing_attribute43,
             pricing_attribute44,
             pricing_attribute45,
             pricing_attribute46,
             pricing_attribute47,
             pricing_attribute48,
             pricing_attribute49,
             pricing_attribute50,
             pricing_attribute51,
             pricing_attribute52,
             pricing_attribute53,
             pricing_attribute54,
             pricing_attribute55,
             pricing_attribute56,
             pricing_attribute57,
             pricing_attribute58,
             pricing_attribute59,
             pricing_attribute60,
             pricing_attribute61,
             pricing_attribute62,
             pricing_attribute63,
             pricing_attribute64,
             pricing_attribute65,
             pricing_attribute66,
             pricing_attribute67,
             pricing_attribute68,
             pricing_attribute69,
             pricing_attribute70,
             pricing_attribute71,
             pricing_attribute72,
             pricing_attribute73,
             pricing_attribute74,
             pricing_attribute75,
             pricing_attribute76,
             pricing_attribute77,
             pricing_attribute78,
             pricing_attribute79,
             pricing_attribute80,
             pricing_attribute81,
             pricing_attribute82,
             pricing_attribute83,
             pricing_attribute84,
             pricing_attribute85,
             pricing_attribute86,
             pricing_attribute87,
             pricing_attribute88,
             pricing_attribute89,
             pricing_attribute90,
             pricing_attribute91,
             pricing_attribute92,
             pricing_attribute93,
             pricing_attribute94,
             pricing_attribute95,
             pricing_attribute96,
             pricing_attribute97,
             pricing_attribute98,
             pricing_attribute99,
             pricing_attribute100
      FROM   oe_order_price_attribs
      WHERE  line_id = p_line_id ;
Line: 1767

        SELECT source_transaction_type_id,
               transaction_line_id,
               source_transaction_table,
               config_session_hdr_id,
               config_session_item_id,
               config_session_rev_num
        INTO   x_txn_line_rec.source_transaction_type_id,
               x_txn_line_rec.transaction_line_id,
               x_txn_line_rec.source_transaction_table,
               x_txn_line_rec.config_session_hdr_id,
               x_txn_line_rec.config_session_item_id,
               x_txn_line_rec.config_session_rev_num
        FROM   csi_t_transaction_lines
        WHERE  transaction_line_id = l_g_line_dtl_tbl(1).transaction_line_id;
Line: 1821

      SELECT config_header_id  config_session_hdr_id,
             config_rev_nbr    config_session_rev_num,
             configuration_id  config_session_item_id
      FROM   oe_order_lines_all
      WHERE  header_id = p_header_id;
Line: 1999

            SELECT processing_status
            INTO   l_processing_status
            FROM   csi_t_transaction_lines
            WHERE  transaction_line_id = l_rltns_tbl(l_ind).transaction_line_id;
Line: 2021

                    SELECT 'Y' INTO l_dummy
                    FROM   csi_item_instances
                    WHERE  config_inst_hdr_id  = l_rltns_tbl(l_ind).obj_config_inst_hdr_id
                    --AND    config_inst_rev_num = l_rltns_tbl(l_ind).obj_config_inst_rev_num
                    AND    config_inst_item_id = l_rltns_tbl(l_ind).obj_config_inst_item_id;
Line: 2031

                          SELECT 'Y' INTO l_dummy
                          FROM CSI_T_TXN_LINE_DETAILS
                          WHERE txn_line_detail_id = l_rltns_tbl(l_ind).object_id
                          AND   instance_id IS NOT NULL;
Line: 2057

                    SELECT 'Y' INTO l_dummy
                    FROM   csi_item_instances
                    WHERE  config_inst_hdr_id  = l_rltns_tbl(l_ind).sub_config_inst_hdr_id
                    --AND    config_inst_rev_num = l_rltns_tbl(l_ind).sub_config_inst_rev_num
                    AND    config_inst_item_id = l_rltns_tbl(l_ind).sub_config_inst_item_id;
Line: 2067

                          SELECT 'Y' INTO l_dummy
                          FROM CSI_T_TXN_LINE_DETAILS
                          WHERE txn_line_detail_id = l_rltns_tbl(l_ind).subject_id
                          AND   instance_id IS NOT NULL;
Line: 2278

                SELECT instance_id
                INTO   l_tld_tbl(l_ind).instance_id
                FROM   csi_item_instances
                WHERE  config_inst_hdr_id =  l_tld_tbl(l_ind).config_inst_hdr_id
                AND    config_inst_item_id = l_tld_tbl(l_ind).config_inst_item_id;
Line: 2354

            SELECT instance_id
            INTO   l_tld_tbl(l_ind).instance_id
            FROM   csi_item_instances
            WHERE  config_inst_hdr_id =  l_tld_tbl(l_ind).config_inst_hdr_id
            AND    config_inst_item_id = l_tld_tbl(l_ind).config_inst_item_id;
Line: 2636

      SELECT * INTO l_line_rec
      FROM   oe_order_lines_all
      WHERE  line_id = p_order_line_id;
Line: 2640

      SELECT * INTO l_hdr_rec
      FROM   oe_order_headers_all
      WHERE  header_id = l_line_rec.header_id;
Line: 2701

          SELECT transaction_date
          INTO   x_source_line_rec.fulfilled_date
          FROM   mtl_material_transactions
          WHERE  transaction_type_id = l_drop_ship_txn_type_id
          AND    trx_source_line_id  = l_line_rec.line_id
          AND    rownum              = 1;
Line: 2870

      SELECT party_id
      INTO   x_source_line_rec.owner_party_id
      FROM   hz_cust_accounts
      WHERE  cust_account_id = x_source_line_rec.owner_party_account_id;
Line: 2883

      SELECT hcas.party_site_id
      INTO   x_source_line_rec.ship_to_party_site_id
      FROM   hz_cust_site_uses_all  hcsu,
             hz_cust_acct_sites_all hcas
      WHERE  hcsu.site_use_id       = x_source_line_rec.ib_current_loc_id -- ship_to_address_id
      AND    hcas.cust_acct_site_id = hcsu.cust_acct_site_id;
Line: 2896

      SELECT hcas.party_site_id
      INTO   x_source_line_rec.install_to_party_site_id
      FROM   hz_cust_site_uses_all  hcsu,
             hz_cust_acct_sites_all hcas
      WHERE  hcsu.site_use_id       = x_source_line_rec.ib_install_loc_id
      AND    hcas.cust_acct_site_id = hcsu.cust_acct_site_id;
Line: 2905

      SELECT hzr.subject_id
      INTO   x_source_line_rec.ship_to_contact_party_id
      FROM   hz_relationships       hzr,
             hz_cust_account_roles  hzar
      WHERE  hzar.cust_account_role_id  = x_source_line_rec.ship_to_contact_id
      AND    hzr.party_id               = hzar.party_id
      AND    hzr.subject_table_name     = 'HZ_PARTIES'
      AND    hzr.object_table_name      = 'HZ_PARTIES'
      AND    hzr.directional_flag       = 'F';
Line: 2917

      SELECT hzr.subject_id
      INTO   x_source_line_rec.bill_to_contact_party_id
      FROM   hz_relationships       hzr,
             hz_cust_account_roles  hzar
      WHERE  hzar.cust_account_role_id  = x_source_line_rec.bill_to_contact_id
      AND    hzr.party_id               = hzar.party_id
      AND    hzr.subject_table_name     = 'HZ_PARTIES'
      AND    hzr.object_table_name      = 'HZ_PARTIES'
      AND    hzr.directional_flag       = 'F';
Line: 2941

      SELECT *
      FROM   oke_k_deliverables_vl
      WHERE  deliverable_id = p_dlv_id;
Line: 2946

      SELECT source_header_id,
             source_line_id,
             source_line_number,
             org_id,
             organization_id,
             customer_id,
             ship_to_site_use_id,
             ship_to_contact_id,
             cust_po_number,
             inventory_item_id,
             revision,
             src_requested_quantity,
             src_requested_quantity_uom,
             date_scheduled,
             top_model_line_id,
             ato_line_id
      FROM   wsh_delivery_details_ob_grp_v
      WHERE  delivery_detail_id = p_src_line_id;
Line: 2978

        SELECT contract_number
        INTO   x_source_header_rec.source_header_ref
        FROM   okc_k_headers_all_b  --fix for bug5358612
        WHERE  id = wsh_rec.source_header_id;
Line: 3003

        SELECT party_id
        INTO   x_source_line_rec.owner_party_id
        FROM   hz_cust_accounts
        WHERE  cust_account_id = x_source_line_rec.owner_party_account_id;
Line: 3011

        SELECT hcas.party_site_id
        INTO   x_source_line_rec.ship_to_party_site_id
        FROM   hz_cust_site_uses_all  hcsu,
               hz_cust_acct_sites_all hcas
        WHERE  hcsu.site_use_id       = x_source_line_rec.ship_to_address_id
        AND    hcas.cust_acct_site_id = hcsu.cust_acct_site_id;
Line: 3020

        SELECT hzr.subject_id
        INTO   x_source_line_rec.ship_to_contact_party_id
        FROM   hz_relationships       hzr,
               hz_cust_account_roles  hzar
        WHERE  hzar.cust_account_role_id  = x_source_line_rec.ship_to_contact_id
        AND    hzr.party_id               = hzar.party_id
        AND    hzr.subject_table_name     = 'HZ_PARTIES'
        AND    hzr.object_table_name      = 'HZ_PARTIES'
        AND    hzr.directional_flag       = 'F';
Line: 3115

      SELECT obj_config_inst_hdr_id  inst_hdr_id,
             obj_config_inst_item_id inst_item_id,
             obj_config_inst_rev_num inst_rev_num
      FROM   csi_t_ii_relationships
      WHERE  sub_config_inst_hdr_id  = p_instance_key.inst_hdr_id
      AND    sub_config_inst_item_id = p_instance_key.inst_item_id
      AND    sub_config_inst_rev_num = p_instance_key.inst_rev_num
      UNION
      SELECT sub_config_inst_hdr_id  inst_hdr_id,
             sub_config_inst_item_id inst_item_id,
             sub_config_inst_rev_num inst_rev_num
      FROM   csi_t_ii_relationships
      WHERE  obj_config_inst_hdr_id  = p_instance_key.inst_hdr_id
      AND    obj_config_inst_item_id = p_instance_key.inst_item_id
      AND    obj_config_inst_rev_num = p_instance_key.inst_rev_num;
Line: 3143

        SELECT location_id,
               location_type_code
        INTO   l_location_id,
               l_location_type_code
        FROM   csi_t_txn_line_details
        WHERE  config_inst_hdr_id  = partner_rec.inst_hdr_id
        AND    config_inst_item_id = partner_rec.inst_item_id
        AND    config_inst_rev_num = partner_rec.inst_rev_num;
Line: 3159

            SELECT location_id ,
                   location_type_code
            INTO   l_location_id,
                   l_location_type_code
            FROM   csi_item_instances
            WHERE  config_inst_hdr_id  = partner_rec.inst_hdr_id
            AND    config_inst_item_id = partner_rec.inst_item_id;
Line: 3291

          SELECT instance_usage_code
          INTO   l_instance_usage_code
          FROM   csi_item_instances
          WHERE  instance_id = l_i_rec.instance_id;
Line: 3355

                    SELECT instance_party_id
                    INTO   p_txn_party_tbl(l_pc_ind).contact_party_id
                    FROM   csi_i_parties
                    WHERE  instance_id = l_i_rec.instance_id
                    AND    relationship_type_code = p_txn_party_tbl(l_p_ind).relationship_type_code
                    AND   ((active_end_date is null ) OR
                           (active_end_date > sysdate));
Line: 3536

                  SELECT attribute_value_id
                  INTO   l_eav_tbl(eav_ind).attribute_value_id
                  FROM   csi_iea_values
                  WHERE  attribute_id = p_txn_eav_tbl(l_eav_ind).attribute_source_id
                  AND    instance_id  = l_i_rec.instance_id;
Line: 3617

    SELECT * INTO l_csi_param_rec
    FROM   csi_install_parameters;
Line: 3656

    SELECT object_version_number,
           serial_number ,
           active_end_date
    INTO   px_instance_rec.object_version_number,
           px_instance_rec.serial_number,
           l_active_end_date
    FROM   csi_item_instances
    WHERE  instance_id = px_instance_rec.instance_id;
Line: 3683

            SELECT instance_party_id,
                   object_version_number
            INTO   px_party_tbl(l_p_ind).instance_party_id,
                   px_party_tbl(l_p_ind).object_version_number
            FROM   csi_i_parties
            WHERE  instance_id = px_instance_rec.instance_id
            AND    party_source_table = px_party_tbl(l_p_ind).party_source_table
            AND    relationship_type_code = px_party_tbl(l_p_ind).relationship_type_code;
Line: 3697

                SELECT instance_party_id,
                       object_version_number
                INTO   px_party_tbl(l_p_ind).instance_party_id,
                       px_party_tbl(l_p_ind).object_version_number
                FROM   csi_i_parties
                WHERE  instance_id = px_instance_rec.instance_id
                AND    party_id = px_party_tbl(l_p_ind).party_id
                AND    party_source_table = px_party_tbl(l_p_ind).party_source_table
                AND    relationship_type_code = px_party_tbl(l_p_ind).relationship_type_code;
Line: 3725

                    SELECT ip_account_id,
                           object_version_number
                    INTO   px_party_acct_tbl(l_pa_ind).ip_account_id,
                           px_party_acct_tbl(l_pa_ind).object_version_number
                    FROM   csi_ip_accounts
                    WHERE  instance_party_id      = px_party_acct_tbl(l_pa_ind).instance_party_id
                    AND    relationship_type_code = px_party_acct_tbl(l_pa_ind).relationship_type_code;
Line: 3739

                        SELECT ip_account_id,
                               object_version_number
                        INTO   px_party_acct_tbl(l_pa_ind).ip_account_id,
                               px_party_acct_tbl(l_pa_ind).object_version_number
                        FROM   csi_ip_accounts
                        WHERE  instance_party_id      = px_party_acct_tbl(l_pa_ind).instance_party_id
                        AND    party_account_id       = px_party_acct_tbl(l_pa_ind).party_account_id
                        AND    relationship_type_code = px_party_acct_tbl(l_pa_ind).relationship_type_code;
Line: 3754

                  SELECT object_version_number
                  INTO   px_party_acct_tbl(l_pa_ind).object_version_number
                  FROM   csi_ip_accounts
                  WHERE  ip_account_id = px_party_acct_tbl(l_pa_ind).ip_account_id;
Line: 3762

            SELECT object_version_number
            INTO   px_party_tbl(l_p_ind).object_version_number
            FROM   csi_i_parties
            WHERE instance_party_id = px_party_tbl(l_p_ind).instance_party_id;
Line: 3781

            SELECT instance_ou_id,
                   object_version_number
            INTO   px_org_units_tbl(l_ind).instance_ou_id,
                   px_org_units_tbl(l_ind).object_version_number
            FROM   csi_i_org_assignments
            WHERE  instance_id = px_org_units_tbl(l_ind).instance_id
            AND    operating_unit_id = px_org_units_tbl(l_ind).operating_unit_id
            AND    relationship_type_code = px_org_units_tbl(l_ind).relationship_type_code;
Line: 3795

          SELECT object_version_number
          INTO   px_org_units_tbl(l_ind).instance_ou_id
          FROM   csi_i_org_assignments
          WHERE  instance_ou_id = px_org_units_tbl(l_ind).instance_ou_id;
Line: 3816

          SELECT object_version_number
          INTO   px_eav_tbl(l_ind).object_version_number
          FROM   csi_iea_values
          WHERE  attribute_value_id = px_eav_tbl(l_ind).attribute_value_id;
Line: 3833

                SELECT pricing_attribute_id ,
                       object_version_number
                INTO   px_pricing_tbl(l_ind).pricing_attribute_id ,
                       px_pricing_tbl(l_ind).object_version_number
                FROM   csi_i_pricing_attribs
                WHERE  instance_id       = px_instance_rec.instance_id
                AND    pricing_context   = px_pricing_tbl(l_ind).pricing_context ;
Line: 3847

          SELECT object_version_number
          INTO   px_pricing_tbl(l_ind).object_version_number
          FROM   csi_i_pricing_attribs
          WHERE  pricing_attribute_id = px_pricing_tbl(l_ind).pricing_attribute_id;
Line: 3917

               SELECT instance_id,active_end_date
               INTO   l_sub_instance_id,l_sub_end_date --Fix for bug 5956280
               FROM   csi_item_instances
               WHERE  config_inst_hdr_id  = l_r_tbl(l_ii_ind).sub_config_inst_hdr_id
               --AND    config_inst_rev_num = l_r_tbl(l_ii_ind).sub_config_inst_rev_num
               AND    config_inst_item_id = l_r_tbl(l_ii_ind).sub_config_inst_item_id;
Line: 3926

                     SELECT instance_id,active_end_date
                     INTO   l_sub_instance_id,l_sub_end_date --Fix for bug 5956280
                     FROM CSI_T_TXN_LINE_DETAILS
                     WHERE txn_line_detail_id = l_r_tbl(l_ii_ind).subject_id;
Line: 3941

               SELECT instance_id,active_end_date
               INTO   l_obj_instance_id,l_obj_end_date --Fix for bug 5956280
               FROM   csi_item_instances
               WHERE  config_inst_hdr_id  = l_r_tbl(l_ii_ind).obj_config_inst_hdr_id
               --AND    config_inst_rev_num = l_r_tbl(l_ii_ind).obj_config_inst_rev_num
               AND    config_inst_item_id = l_r_tbl(l_ii_ind).obj_config_inst_item_id;
Line: 3950

                     SELECT instance_id,active_end_date
                     INTO   l_obj_instance_id,l_obj_end_date --Fix for bug 5956280
                     FROM CSI_T_TXN_LINE_DETAILS
                     WHERE txn_line_detail_id = l_r_tbl(l_ii_ind).object_id;
Line: 3962

            SELECT relationship_id,
                   object_version_number,
                   active_end_date
            INTO   l_r_tbl(l_ii_ind).csi_inst_relationship_id,
                   l_r_tbl(l_ii_ind).object_version_number,
                   l_rel_end_date
            FROM   csi_ii_relationships
            WHERE  subject_id = l_sub_instance_id
            AND    object_id  = l_obj_instance_id;
Line: 4020

    debug('  update_ii_rltns :'||x_u_ii_rltns_tbl.count);
Line: 4027

  PROCEDURE update_td_status(
    p_txn_line_rec         IN     csi_t_datastructures_grp.txn_line_rec,
    p_txn_line_dtl_tbl     IN     csi_t_datastructures_grp.txn_line_detail_tbl,
    p_processing_status    IN     varchar2,
    x_return_status        OUT NOCOPY    varchar2)
  IS

    l_tl_rec               csi_t_datastructures_grp.txn_line_rec;
Line: 4048

    api_log('update_td_status');
Line: 4070

      csi_t_txn_details_grp.update_txn_line_dtls(
        p_api_version              => 1.0,
        p_commit                   => fnd_api.g_false,
        p_init_msg_list            => fnd_api.g_true,
        p_validation_level         => fnd_api.g_valid_level_full,
        p_txn_line_rec             => l_tl_rec,
        p_txn_line_detail_tbl      => l_td_tbl,
        px_txn_party_detail_tbl    => l_pd_tbl,
        px_txn_pty_acct_detail_tbl => l_pa_tbl,
        px_txn_org_assgn_tbl       => l_oa_tbl,
        px_txn_ext_attrib_vals_tbl => l_ea_tbl,
        px_txn_ii_rltns_tbl        => l_ir_tbl,
        x_return_status            => l_return_status,
        x_msg_count                => l_msg_count,
        x_msg_data                 => l_msg_data);
Line: 4094

  END update_td_status;
Line: 4242

              SELECT active_end_date
              INTO l_rlt_active_end_date
              FROM csi_ii_relationships
              WHERE relationship_id=
              (SELECT max(relationship_id)
              FROM csi_ii_relationships
              WHERE subject_id = px_txn_line_dtl_tbl(l_td_ind).instance_id
              AND relationship_type_code = l_rel_type_code);  -- srramakr added
Line: 4284

          SELECT *
          INTO   l_sub_type_rec
          FROM   csi_txn_sub_types
          WHERE  transaction_type_id = px_txn_line_rec.source_transaction_type_id
          AND    sub_type_id         = px_txn_line_dtl_tbl(l_td_ind).sub_type_id;
Line: 4325

          /* moved this update_relationship set above to address the issue with
             expire the relationship first before processing the children. The
             core API while expiring the parent instance automatically end dates
             the child instances. We are stopping that by expiring the relationsip
             first thus by hiding the shildren from the API.
          */

          IF px_txn_ii_rltns_tbl.COUNT > 0 THEN

            build_relationship_tbl(
              p_txn_ii_rltns_tbl    => px_txn_ii_rltns_tbl,
              p_txn_line_dtl_tbl    => px_txn_line_dtl_tbl,
              x_c_ii_rltns_tbl      => l_c_ii_rltns_tbl,
              x_u_ii_rltns_tbl      => l_u_ii_rltns_tbl,
              x_return_status       => l_return_status);
Line: 4345

            /* update the existing relationships */
            IF l_u_ii_rltns_tbl.COUNT > 0 THEN

              /* to stamp the source transaction date as end date */
              FOR l_ind IN l_u_ii_rltns_tbl.FIRST .. l_u_ii_rltns_tbl.LAST
              LOOP
                debug(' Relationship_ID      :'|| l_u_ii_rltns_tbl(l_ind).relationship_id);
Line: 4364

                p_api_name => 'update_relationship');
Line: 4366

              csi_ii_relationships_pub.update_relationship(
                p_api_version        => 1.0,
                p_commit             => fnd_api.g_false,
                p_init_msg_list      => fnd_api.g_true,
                p_validation_level   => fnd_api.g_valid_level_full,
                p_relationship_tbl   => l_u_ii_rltns_tbl,
                p_txn_rec            => l_csi_txn_rec,
                x_return_status      => l_return_status,
                x_msg_count          => l_msg_count,
                x_msg_data           => l_msg_data);
Line: 4559

 	          SELECT 'N'
 	          INTO l_item_instance_expired
 	          FROM csi_item_instances
 	          WHERE instance_id = l_u_instance_rec.instance_id
 	          AND ((active_end_date IS NULL) OR (active_end_date >= SYSDATE));
Line: 4578

	            p_api_name => 'update_item_instance');
Line: 4580

	          csi_item_instance_pub.update_item_instance(
	            p_api_version           => 1.0,
	            p_commit                => fnd_api.g_false,
	            p_init_msg_list         => fnd_api.g_true,
	            p_validation_level      => fnd_api.g_valid_level_full,
	            p_txn_rec               => l_csi_txn_rec,
	            p_instance_rec          => l_u_instance_rec,
	            p_party_tbl             => l_u_party_tbl,
	            p_account_tbl           => l_u_party_acct_tbl,
	            p_org_assignments_tbl   => l_u_org_units_tbl,
	            p_ext_attrib_values_tbl => l_u_eav_tbl,
	            p_pricing_attrib_tbl    => l_u_pricing_tbl,
	            p_asset_assignment_tbl  => l_u_inst_asset_tbl,
	            x_instance_id_lst       => l_u_inst_id_tbl,
	            x_return_status         => l_return_status,
	            x_msg_count             => l_msg_count,
	            x_msg_data              => l_msg_data );
Line: 4611

          /* Setting values for Explode BOM in UPDATE */

          IF l_u_instance_rec.quantity = 1 THEN
            l_bom_std_item_rec.instance_id         := l_u_instance_rec.instance_id ;
Line: 4824

    update_td_status(
      p_txn_line_rec      => px_txn_line_rec,
      p_txn_line_dtl_tbl  => px_txn_line_dtl_tbl,
      p_processing_status => 'PROCESSED',
      x_return_status     => l_return_status);
Line: 4884

        SELECT 'Y' INTO l_td_found
        FROM   csi_t_transaction_lines
        WHERE  source_transaction_table = 'CONFIGURATOR'
        AND    config_session_hdr_id    = p_config_session_key.session_hdr_id
        AND    config_session_rev_num   = p_config_session_key.session_rev_num
        AND    config_session_item_id   = p_config_session_key.session_item_id;
Line: 4901

          SELECT model_instantiation_type
          INTO   l_mdl_instantiation_type
          FROM   cz_config_items_v
          WHERE  config_hdr_id  = p_config_session_key.session_hdr_id
          AND    config_rev_nbr = p_config_session_key.session_rev_num
          AND    config_item_id = p_config_session_key.session_item_id;
Line: 4974

      SELECT 'Y'
      INTO   l_bom_found
      FROM   bom_bill_of_materials
      WHERE  assembly_item_id = p_std_item_rec.inventory_item_id
      AND    organization_id  = p_std_item_rec.vld_organization_id
      AND    alternate_bom_designator is NULL;   -- added for bug 2443204. Checking only primary bom when multiple could have been defined.
Line: 4990

      SELECT 'Y'
      INTO   l_found_child
      FROM   csi_ii_relationships
      WHERE  object_id = p_instance_id
      AND    relationship_type_code = 'COMPONENT-OF';
Line: 5028

      SELECT mmt.trx_source_line_id         trx_source_line_id,
             mmt.inventory_item_id          inventory_item_id,
             mmt.organization_id            organization_id,
             mmt.revision                   revision,
             mmt.subinventory_code          subinventory_code,
             mmt.locator_id                 locator_id,
             null                           lot_number,
             mut.serial_number              serial_number,
             abs(mmt.transaction_quantity)  transaction_quantity,
             mmt.transaction_uom            transaction_uom,
             mmt.transaction_date           transaction_date,
             msi.lot_control_code           lot_control_code,
             msi.serial_number_control_code serial_control_code,
             msi.primary_uom_code           primary_uom,
             abs(mmt.primary_quantity)      primary_quantity,
             mmt.transaction_type_id        transaction_type_id,
             mmt.transaction_action_id      transaction_action_id
      FROM   mtl_system_items               msi,
             mtl_unit_transactions          mut,
             mtl_material_transactions      mmt
      WHERE  mmt.transaction_id       = p_txn_id
      AND    mmt.transaction_id       = mut.transaction_id(+)
      AND    msi.organization_id      = mmt.organization_id
      AND    msi.inventory_item_id    = mmt.inventory_item_id
      AND    msi.lot_control_code     = 1   -- no lot case
      UNION
      SELECT mmt.trx_source_line_id         trx_source_line_id,
             mmt.inventory_item_id          inventory_item_id,
             mmt.organization_id            organization_id,
             mmt.revision                   revision,
             mmt.subinventory_code          subinventory_code,
             mmt.locator_id                 locator_id,
             mtln.lot_number                lot_number,
             mut.serial_number              serial_number,
             abs(mtln.transaction_quantity) transaction_quantity,
             mmt.transaction_uom            transaction_uom,
             mmt.transaction_date           transaction_date,
             msi.lot_control_code           lot_control_code,
             msi.serial_number_control_code serial_control_code,
             msi.primary_uom_code           primary_uom,
             abs(mtln.primary_quantity)     primary_quantity,
             mmt.transaction_type_id        transaction_type_id,
             mmt.transaction_action_id      transaction_action_id
      FROM   mtl_system_items               msi,
             mtl_unit_transactions          mut,
             mtl_transaction_lot_numbers    mtln,
             mtl_material_transactions      mmt
      WHERE  mmt.transaction_id         = p_txn_id
      AND    mmt.transaction_id         = mtln.transaction_id(+)
      AND    mtln.serial_transaction_id = mut.transaction_id(+)
      AND    msi.organization_id        = mmt.organization_id
      AND    msi.inventory_item_id      = mmt.inventory_item_id
      AND    msi.lot_control_code       = 2;   -- lot control case
Line: 5254

    l_create_update_flag     varchar2(1);
Line: 5273

      l_create_update_flag := 'U';
Line: 5275

      l_create_update_flag := 'C';
Line: 5283

        px_mtl_txn_tbl(l_ind).create_update_flag := l_create_update_flag;
Line: 5303

      SELECT location_id
      INTO   l_location_id
      FROM   mtl_secondary_inventories
      WHERE  organization_id = p_organization_id
      AND    secondary_inventory_name = p_subinventory_code;
Line: 5319

        SELECT location_id
        INTO   l_location_id
        FROM   hr_organization_units
        WHERE  organization_id = p_organization_id;
Line: 5463

    SELECT object_version_number,
           quantity - p_quantity,
           active_end_date
    INTO   l_instance_rec.object_version_number,
           l_instance_rec.quantity,
           l_instance_rec.active_end_date
    FROM   csi_item_instances
    WHERE  instance_id = l_instance_rec.instance_id;
Line: 5475

        SELECT instance_status_id
        INTO   l_instance_rec.instance_status_id
        FROM   csi_instance_statuses
        WHERE  name = fnd_profile.value('csi_default_instance_status');
Line: 5492

      p_api_name => 'update_item_instance',
      p_pkg_name => 'csi_item_instance_pub');
Line: 5496

    csi_item_instance_pub.update_item_instance(
      p_api_version           => 1.0,
      p_commit                => fnd_api.g_false,
      p_init_msg_list         => fnd_api.g_true,
      p_validation_level      => fnd_api.g_valid_level_full,
      p_instance_rec          => l_instance_rec,
      p_ext_attrib_values_tbl => l_ext_attrib_val_tbl,
      p_party_tbl             => l_party_tbl,
      p_account_tbl           => l_party_acct_tbl,
      p_pricing_attrib_tbl    => l_pricing_attribs_tbl,
      p_org_assignments_tbl   => l_org_units_tbl,
      p_txn_rec               => px_txn_rec,
      p_asset_assignment_tbl  => l_inst_asset_tbl,
      x_instance_id_lst       => l_inst_id_lst,
      x_return_status         => l_return_status,
      x_msg_count             => l_msg_count,
      x_msg_data              => l_msg_data);
Line: 5570

          IF p_mtl_txn_tbl(l_ind).create_update_flag = 'C' THEN

            IF nvl(p_mtl_txn_tbl(l_ind).instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
              decrement_inventory_instnace(
                p_instance_id    => p_mtl_txn_tbl(l_ind).instance_id,
                p_quantity       => p_mtl_txn_tbl(l_ind).primary_quantity,
                px_txn_rec       => px_txn_rec,
                x_return_status  => l_return_status);
Line: 6117

      SELECT owner_party_account_id,
             owner_party_id
      INTO   l_owner_party_acct_id,
             l_owner_party_id
      FROM   csi_item_instances
      WHERE  instance_id = p_instance_id;
Line: 6127

        SELECT party_source_table
        INTO   l_party_source_table
        FROM   csi_i_parties
        WHERE  instance_id = p_instance_id
        AND    relationship_type_code = 'OWNER';
Line: 6254

                SELECT instance_id
                INTO   l_td_tbl(l_td_ind).instance_id
                FROM   csi_item_instances
                WHERE  config_inst_hdr_id = l_td_tbl(l_td_ind).config_inst_hdr_id
                AND    config_inst_item_id = l_td_tbl(l_td_ind).config_inst_item_id;