APPS.AHL_PRD_SERN_CHANGE_PVT SQL Statements

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

Line: 20

     SELECT  ctxn.transaction_type_id
     FROM csi_txn_types ctxn, fnd_application app
     WHERE ctxn.source_application_id = app.application_id
      AND app.APPLICATION_SHORT_NAME = 'AHL'
      AND ctxn.source_transaction_type = p_txn_code;

Line: 56

    SELECT attribute_id
    FROM csi_i_extended_attribs
    WHERE attribute_level = 'GLOBAL'
    AND attribute_code = p_attribute_code;

Line: 92

    SELECT iea.attribute_value, iea.attribute_value_id, iea.object_version_number
    FROM csi_i_extended_attribs attb, csi_iea_values iea
    WHERE attb.attribute_id = iea.attribute_id
      AND attb.attribute_code = p_attribute_code
      AND iea.instance_id = p_csi_instance_id
      AND trunc(sysdate) >= trunc(nvl(iea.active_start_date, sysdate))
      AND trunc(sysdate) < trunc(nvl(iea.active_end_date, sysdate+1));

Line: 135

      SELECT lookup_code
      FROM fnd_lookup_values_vl
      WHERE lookup_type = p_lookup_type
          AND  meaning = p_lookup_meaning
          AND TRUNC(SYSDATE) >= TRUNC(NVL(start_date_active, SYSDATE))
          AND TRUNC(SYSDATE) < TRUNC(NVL(end_date_active, SYSDATE+1));

Line: 169

    SELECT  1
    FROM   mtl_serial_numbers
    WHERE  inventory_item_id = c_Inventory_id
          AND Serial_Number = c_Serial_Number;

Line: 227

    SELECT cii.attribute_value attribute_value
     FROM  csi_item_instances csi,csi_iea_values cii
     WHERE csi.instance_id = cii.instance_id
       AND csi.instance_id = c_instance_id
       AND cii.rowid  in ( select max(rowid) from csi_iea_values
  			   	 where  instance_id = csi.instance_id);

Line: 236

    SELECT cii.attribute_value attribute_value
     FROM  csi_item_instances csi,csi_iea_values cii
     WHERE csi.instance_id = cii.instance_id(+)
       AND csi.instance_id = c_instance_id;

Line: 296

    SELECT decode(mfg_Serial_number_flag, 'N',cii.attribute_value,NULL,cii.attribute_value,'INVENTORY') attribute_value
     FROM  csi_item_instances csi,csi_iea_values cii
     WHERE csi.instance_id = cii.instance_id
       AND csi.instance_id = c_instance_id
       AND cii.rowid  in ( select max(rowid) from csi_iea_values
  			   	 where  instance_id = csi.instance_id);

Line: 305

    SELECT decode(mfg_Serial_number_flag, 'N',cii.attribute_value,NULL,cii.attribute_value,'INVENTORY') attribute_value
     FROM  csi_item_instances csi,csi_iea_values cii
     WHERE csi.instance_id = cii.instance_id(+)
       AND csi.instance_id = c_instance_id;

Line: 312

	SELECT meaning
	  FROM fnd_lookup_values_vl
	 WHERE lookup_type = 'AHL_SERIALNUMBER_TAG'
	   AND lookup_code = c_lookup_code;

Line: 393

    SELECT
      workorder_id,
      wip_entity_id,
      job_number,
      item_instance_id,
      item_instance_number,
      organization_id,
      inventory_item_id
    FROM
      ahl_workorders_v
    WHERE
      (workorder_id = c_workorder_id
      OR job_number = c_job_number)
      AND job_status_code not in (1,4,5,7,12,14,17);

Line: 412

	SELECT csi_item_instance_id
    FROM ahl_unit_config_headers uc
    WHERE csi_item_instance_id in ( SELECT object_id
                                    FROM csi_ii_relationships
                                    START WITH object_id = c_item_instance_id
                                      AND relationship_type_code = 'COMPONENT-OF'
                                      AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
                                      AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
                                    CONNECT BY PRIOR subject_id = object_id
                                      AND relationship_type_code = 'COMPONENT-OF'
                                      AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
                                      AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
                                   )
         AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
         AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));

Line: 432

   SELECT
     instance_number,
     instance_id,
     object_version_number,
     inventory_item_id,
     serial_number,
     wip_job_id,
     location_type_code
   FROM
     csi_item_instances
   WHERE
    instance_id = c_instance_id;

Line: 448

  SELECT
    serial_number_control_code,
    lot_control_code,
    concatenated_segments
  FROM
    mtl_system_items_vl
  WHERE
    inventory_item_id   = c_Inventory_id
  AND organization_id = c_Organization_id;

Line: 461

   SELECT
    inventory_item_id
   FROM
     MTL_SYSTEM_ITEMS_KFV
   WHERE
     CONCATENATED_SEGMENTS = c_item_number;

Line: 471

   SELECT
    inventory_item_id
   FROM
     MTL_SYSTEM_ITEMS_KFV
   WHERE
     CONCATENATED_SEGMENTS = c_item_number
     AND organization_id = c_organization_id
     AND SERIAL_NUMBER_CONTROL_CODE in (2,5,6);

Line: 940

			-- changed value. update attribute record.
			l_csi_extend_attrib_rec.attribute_value_id := l_attribute_value_id;

Line: 1015

      CSI_Item_Instance_PUB.Update_Item_Instance(
               p_api_version            => 1.0,
               p_instance_rec           => l_csi_instance_rec,
               p_txn_rec                => l_csi_transaction_rec,
               p_ext_attrib_values_tbl  => l_csi_ext_attrib_values_tbl,
               p_party_tbl              => l_csi_party_tbl,
               p_account_tbl            => l_csi_account_tbl,
               p_pricing_attrib_tbl     => l_csi_pricing_attrib_tbl,
               p_org_assignments_tbl    => l_csi_org_assignments_tbl,
               p_asset_assignment_tbl   => l_csi_asset_assignment_tbl,
               x_instance_id_lst        => l_csi_instance_id_lst,
               x_return_status          => l_return_status,
               x_msg_count              => l_msg_count,
               x_msg_data               => l_msg_data );