DBA Data[Home] [Help]

APPS.CS_SR_PROB_CODE_MAPPING_PKG SQL Statements

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

Line: 36

    select meaning from
    cs_lookups cslkup
    where
        cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
        cslkup.lookup_code = l_problem_code and
        (cslkup.start_date_active is null or trunc(cslkup.start_date_active) <= trunc(sysdate)) and
        (cslkup.end_date_active is null or trunc(cslkup.end_date_active) >= trunc(sysdate));
Line: 51

    select meaning from
    cs_lookups cslkup
    where
        cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
        cslkup.lookup_code = l_problem_code and
        not exists
    	( select 'X' from
          cs_sr_prob_code_mapping_detail cstl
    	  where
          cstl.problem_code = cslkup.lookup_code  and
          (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
          (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
          (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
          (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate)));
Line: 83

select meaning
from
     cs_lookups cslkup
where
       cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
       cslkup.lookup_code = l_problem_code and
       exists
     	( select 'X' from
          cs_sr_prob_code_mapping_detail cstl
    	  where
      	  cstl.problem_code = cslkup.lookup_code and
          (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
          (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
          (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
          (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
 		  and
          (
           (cstl.incident_type_id  = l_service_request_type_id and
		    cstl.inventory_item_id is null and
            cstl.category_id is null)
          or
	      (cstl.incident_type_id = l_service_request_type_id and
           cstl.inventory_item_id = l_inventory_item_id and
           cstl.organization_id = l_organization_id and
           cstl.category_id is null)
          or
 	      (cstl.incident_type_id = l_service_request_type_id and
           cstl.inventory_item_id is null and
           exists (select category_id from mtl_item_categories cmtlc
                   where
                   cmtlc.category_id = cstl.category_id and
                   category_set_id = l_category_set_id and
                   cmtlc.inventory_item_id = l_inventory_item_id and
                   cmtlc.organization_id = l_organization_id))

           )
	  );
Line: 124

select meaning
from
     cs_lookups cslkup
where
       cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
       cslkup.lookup_code = l_problem_code and
       exists
     	( select 'X' from
          cs_sr_prob_code_mapping_detail cstl
    	  where
      	  cstl.problem_code = cslkup.lookup_code and
          (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
          (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
          (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
          (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
 		  and
          (
          (cstl.incident_type_id is null and
           cstl.inventory_item_id = l_inventory_item_id and
           cstl.organization_id = l_organization_id and
           cstl.category_id is null)
          or
          (cstl.incident_type_id is null  and
           cstl.inventory_item_id is null and
           exists (select category_id from mtl_item_categories cmtlc
                   where
                   cmtlc.category_id = cstl.category_id and
                   category_set_id = l_category_set_id and /* value from profile CS_SR_DEFAULT_CATEGORY_SET */
                   cmtlc.inventory_item_id = l_inventory_item_id and
                   cmtlc.organization_id = l_organization_id))


           )
	  );
Line: 174

select meaning
from
     cs_lookups cslkup
where
       cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
       cslkup.lookup_code = l_problem_code and
       exists
       ( select 'X' from
         cs_sr_prob_code_mapping_detail cstl
  	     where
 	     cstl.problem_code = cslkup.lookup_code  and
          (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
          (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
         (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
         (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
         and
         (
          (cstl.incident_type_id  = l_service_request_type_id and
		   cstl.inventory_item_id is null and
           cstl.category_id is null)
         or
	      (cstl.incident_type_id = l_service_request_type_id and
           cstl.inventory_item_id = l_inventory_item_id and
           cstl.organization_id = l_organization_id and
           cstl.category_id is null)
         or
	      (cstl.incident_type_id = l_service_request_type_id and
           cstl.inventory_item_id is null and
           cstl.category_id = l_product_category_id)
         )
	   );
Line: 208

select meaning
from
     cs_lookups cslkup
where
       cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
       cslkup.lookup_code = l_problem_code and
       exists
       ( select 'X' from
         cs_sr_prob_code_mapping_detail cstl
  	     where
 	     cstl.problem_code = cslkup.lookup_code  and
          (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
          (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
         (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
         (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
         and
         (
          cstl.incident_type_id is null  and
          cstl.inventory_item_id = l_inventory_item_id and
          cstl.organization_id = l_organization_id and
          cstl.category_id is null
         )
  	    ) ;
Line: 234

select meaning
from
     cs_lookups cslkup
where
       cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
       cslkup.lookup_code = l_problem_code and
       exists
       ( select 'X' from
         cs_sr_prob_code_mapping_detail cstl
  	     where
 	     cstl.problem_code = cslkup.lookup_code  and
          (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
          (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
         (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
         (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
         and
         (
          cstl.incident_type_id is null  and
          cstl.inventory_item_id is null and
          cstl.organization_id is null and
          cstl.category_id = l_product_category_id
         )
  	    ) ;
Line: 535

    SELECT problem_map_id,start_date_active, end_date_active from cs_sr_prob_code_mapping
    WHERE incident_type_id = l_service_request_type_id
    AND   category_id = l_product_category_id
    AND   inventory_item_id = l_inventory_item_id
    AND   organization_id = l_organization_id
    AND   (start_date_active is null or trunc(start_date_active) <= trunc(sysdate)) and
          (end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
Line: 545

    SELECT  category_id from mtl_category_set_valid_cats
    WHERE category_set_id = to_number(l_product_category_set)
    AND category_id = l_product_category_id;
Line: 550

    SELECT lookup_code from cs_lookups
    WHERE lookup_code = l_problem_code
    AND   lookup_type = 'REQUEST_PROBLEM_CODE'and
          (start_date_active is null or
           trunc(start_date_active) <= trunc(sysdate)) and
          (end_date_active is null or
           trunc(end_date_active) >= trunc(sysdate));
Line: 559

    SELECT problem_map_detail_id,start_date_active, end_date_active from cs_sr_prob_code_mapping_detail
    WHERE problem_map_id = l_problem_map_id
    AND   problem_code = l_problem_code
    AND  (start_date_active is null or trunc(start_date_active) <= trunc(sysdate))
    AND  (end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
Line: 646

  CS_SR_PROBLEM_CODE_MAPPING_PKG.INSERT_ROW (
  PX_PROBLEM_MAP_ID => l_problem_map_id,
  P_INCIDENT_TYPE_ID => l_service_request_type_id,
  P_INVENTORY_ITEM_ID => l_inventory_item_id,
  P_ORGANIZATION_ID => l_organization_id,
  P_CATEGORY_ID => l_product_category_id,
  P_PROBLEM_CODE => null,
  P_START_DATE_ACTIVE => l_start_date_active,
  P_END_DATE_ACTIVE => l_end_date_active,
  P_OBJECT_VERSION_NUMBER => null,
  P_ATTRIBUTE1 => null,
  P_ATTRIBUTE2 => null,
  P_ATTRIBUTE3 => null,
  P_ATTRIBUTE4 => null,
  P_ATTRIBUTE5 => null,
  P_ATTRIBUTE6 => null,
  P_ATTRIBUTE7 => null,
  P_ATTRIBUTE8 => null,
  P_ATTRIBUTE9 => null,
  P_ATTRIBUTE10 => null,
  P_ATTRIBUTE11 => null,
  P_ATTRIBUTE12 => null,
  P_ATTRIBUTE13 => null,
  P_ATTRIBUTE14 => null,
  P_ATTRIBUTE15 => null,
  P_ATTRIBUTE_CATEGORY => null,
  P_CREATION_DATE => l_current_date,
  P_CREATED_BY => l_created_by,
  P_LAST_UPDATE_DATE => l_current_date,
  P_LAST_UPDATED_BY => l_created_by,
  P_LAST_UPDATE_LOGIN => l_login,
  X_RETURN_STATUS	=> l_return_status,
  X_MSG_COUNT	    => l_msg_count,
  X_MSG_DATA	    => l_errmsg);
Line: 730

CS_SR_PROB_CODE_MAP_DETAIL_PKG.INSERT_ROW (
  PX_PROBLEM_MAP_DETAIL_ID => l_problem_map_detail_id,
  P_PROBLEM_MAP_ID => l_problem_map_id,
  P_INCIDENT_TYPE_ID => l_service_request_type_id,
  P_INVENTORY_ITEM_ID => l_inventory_item_id,
  P_ORGANIZATION_ID => l_organization_id,
  P_CATEGORY_ID => l_product_category_id,
  P_MAP_START_DATE_ACTIVE => null,
  P_MAP_END_DATE_ACTIVE => null,
  P_PROBLEM_CODE => null,
  P_START_DATE_ACTIVE => l_start_date_active,
  P_END_DATE_ACTIVE => l_end_date_active,
  P_OBJECT_VERSION_NUMBER => null,
  P_ATTRIBUTE1 => null,
  P_ATTRIBUTE2 => null,
  P_ATTRIBUTE3 => null,
  P_ATTRIBUTE4 => null,
  P_ATTRIBUTE5 => null,
  P_ATTRIBUTE6 => null,
  P_ATTRIBUTE7 => null,
  P_ATTRIBUTE8 => null,
  P_ATTRIBUTE9 => null,
  P_ATTRIBUTE10 => null,
  P_ATTRIBUTE11 => null,
  P_ATTRIBUTE12 => null,
  P_ATTRIBUTE13 => null,
  P_ATTRIBUTE14 => null,
  P_ATTRIBUTE15 => null,
  P_ATTRIBUTE_CATEGORY => null,
  P_CREATION_DATE => l_current_date,
  P_CREATED_BY => l_created_by,
  P_LAST_UPDATE_DATE => l_current_date,
  P_LAST_UPDATED_BY  => l_created_by,
  P_LAST_UPDATE_LOGIN => l_login,
  X_RETURN_STATUS => l_return_status,
  X_MSG_COUNT		 => l_msg_count,
  X_MSG_DATA		=> l_errmsg);
Line: 855

PROCEDURE UPDATE_MAPPING_RULES
( p_api_version			  IN         NUMBER,
  p_init_msg_list		  IN         VARCHAR2 	:= FND_API.G_FALSE,
  p_commit			      IN         VARCHAR2 	:= FND_API.G_FALSE,
  p_probcode_map_criteria_rec IN probcode_map_criteria_rec,
  p_problem_codes_tbl            IN  problem_codes_tbl_type,
  x_return_status		  OUT NOCOPY VARCHAR2,
  x_msg_count			  OUT NOCOPY NUMBER,
  x_msg_data			  OUT NOCOPY VARCHAR2
) IS

 l_api_version     CONSTANT NUMBER := 1.0;
Line: 867

 l_api_name        CONSTANT VARCHAR2(30) := 'UPDATE_MAPPING_RULES';
Line: 894

    SELECT problem_map_id,incident_type_id,category_id,inventory_item_id,organization_id
    FROM CS_SR_PROB_CODE_MAPPING
    WHERE problem_map_id <> l_problem_map_id AND
          incident_type_id = l_service_request_type_id AND
          category_id = l_product_category_id AND
          inventory_item_id = l_inventory_item_id AND
          organization_id = l_organization_id AND
          (start_date_active is null or
           trunc(start_date_active) <= trunc(sysdate)) and
          (end_date_active is null or
           trunc(end_date_active) >= trunc(sysdate));
Line: 908

    SELECT lookup_code from cs_lookups a
    WHERE lookup_code = l_problem_code
    AND   lookup_type = 'REQUEST_PROBLEM_CODE' and
          (start_date_active is null or
           trunc(start_date_active) <= trunc(sysdate)) and
          (end_date_active is null or
           trunc(end_date_active) >= trunc(sysdate));
Line: 917

     SELECT problem_code,start_date_active,end_date_active from CS_SR_PROB_CODE_MAPPING_DETAIL
      WHERE problem_map_id = l_problem_map_id and
            problem_map_detail_id <> nvl(l_problem_map_detail_id,0) and
            problem_code = l_problem_code and
            (start_date_active is null or
             trunc(start_date_active) <= trunc(sysdate)) and
            (end_date_active is null or
             trunc(end_date_active) >= trunc(sysdate));
Line: 931

      SAVEPOINT update_mapping_rules;
Line: 1020

CS_SR_PROB_CODE_MAP_DETAIL_PKG.INSERT_ROW (
  PX_PROBLEM_MAP_DETAIL_ID => l_problem_map_detail_id,
  P_PROBLEM_MAP_ID => l_problem_map_id,
  P_INCIDENT_TYPE_ID => l_service_request_type_id,
  P_INVENTORY_ITEM_ID => l_inventory_item_id,
  P_ORGANIZATION_ID => l_organization_id,
  P_CATEGORY_ID => l_product_category_id,
  P_MAP_START_DATE_ACTIVE => null,
  P_MAP_END_DATE_ACTIVE => null,
  P_PROBLEM_CODE => l_problem_code,
  P_START_DATE_ACTIVE => l_start_date_active,
  P_END_DATE_ACTIVE => l_end_date_active,
  P_OBJECT_VERSION_NUMBER => null,
  P_ATTRIBUTE1 => null,
  P_ATTRIBUTE2 => null,
  P_ATTRIBUTE3 => null,
  P_ATTRIBUTE4 => null,
  P_ATTRIBUTE5 => null,
  P_ATTRIBUTE6 => null,
  P_ATTRIBUTE7 => null,
  P_ATTRIBUTE8 => null,
  P_ATTRIBUTE9 => null,
  P_ATTRIBUTE10 => null,
  P_ATTRIBUTE11 => null,
  P_ATTRIBUTE12 => null,
  P_ATTRIBUTE13 => null,
  P_ATTRIBUTE14 => null,
  P_ATTRIBUTE15 => null,
  P_ATTRIBUTE_CATEGORY => null,
  P_CREATION_DATE => l_current_date,
  P_CREATED_BY => l_created_by,
  P_LAST_UPDATE_DATE => l_current_date,
  P_LAST_UPDATED_BY  => l_created_by,
  P_LAST_UPDATE_LOGIN => l_login,
  X_RETURN_STATUS => l_return_status,
  X_MSG_COUNT		 => l_msg_count,
  X_MSG_DATA		=> l_errmsg);
Line: 1060

CS_SR_PROB_CODE_MAP_DETAIL_PKG.UPDATE_ROW (
  P_PROBLEM_MAP_DETAIL_ID => l_problem_map_detail_id,
  P_PROBLEM_MAP_ID => l_problem_map_id,
  P_INCIDENT_TYPE_ID => l_service_request_type_id,
  P_INVENTORY_ITEM_ID => l_inventory_item_id,
  P_ORGANIZATION_ID => l_organization_id,
  P_CATEGORY_ID => l_product_category_id,
  P_MAP_START_DATE_ACTIVE => null,
  P_MAP_END_DATE_ACTIVE => null,
  P_PROBLEM_CODE => l_problem_code,
  P_START_DATE_ACTIVE => l_start_date_active,
  P_END_DATE_ACTIVE => l_end_date_active,
  P_OBJECT_VERSION_NUMBER => null,
  P_ATTRIBUTE1 => null,
  P_ATTRIBUTE2 => null,
  P_ATTRIBUTE3 => null,
  P_ATTRIBUTE4 => null,
  P_ATTRIBUTE5 => null,
  P_ATTRIBUTE6 => null,
  P_ATTRIBUTE7 => null,
  P_ATTRIBUTE8 => null,
  P_ATTRIBUTE9 => null,
  P_ATTRIBUTE10 => null,
  P_ATTRIBUTE11 => null,
  P_ATTRIBUTE12 => null,
  P_ATTRIBUTE13 => null,
  P_ATTRIBUTE14 => null,
  P_ATTRIBUTE15 => null,
  P_ATTRIBUTE_CATEGORY => null,
  P_LAST_UPDATE_DATE => l_current_date,
  P_LAST_UPDATED_BY  => l_created_by,
  P_LAST_UPDATE_LOGIN => l_login,
  X_RETURN_STATUS => l_return_status,
  X_MSG_COUNT		 => l_msg_count,
  X_MSG_DATA		=> l_errmsg);
Line: 1117

     ROLLBACK TO update_mapping_rules;
Line: 1133

     ROLLBACK TO update_mapping_rules;
Line: 1141

END; -- End of procedure UPDATE_MAPPING_RULES()
Line: 1181

    SELECT problem_map_id, start_date_active, end_date_active
    FROM CS_SR_PROB_CODE_MAPPING;
Line: 1187

    SELECT problem_map_id, problem_map_detail_id,
           map_start_date_active, map_end_date_active,
           start_date_active, end_date_active,
           incident_type_id, inventory_item_id, organization_id,
           category_id, problem_code
    FROM CS_SR_PROB_CODE_MAPPING_DETAIL
    WHERE
        problem_map_id = l_problem_map_id;
Line: 1220

            update CS_SR_PROB_CODE_MAPPING_DETAIL
            set
                map_start_date_active = cs_sr_probmapid_crit_rec.start_date_active,
                map_end_date_active = cs_sr_probmapid_crit_rec.end_date_active
            where
                problem_map_id = l_problem_map_id;
Line: 1230

            update CS_SR_PROB_CODE_MAPPING_DETAIL
            set
                map_start_date_active = cs_sr_probmapid_crit_rec.start_date_active,
                map_end_date_active = cs_sr_probmapid_crit_rec.end_date_active
            where
                problem_map_id = l_problem_map_id;
Line: 1238

            CS_SR_RES_CODE_MAP_DETAIL_PKG.UPDATE_ROW (
              P_RESOLUTION_MAP_DETAIL_ID => cs_sr_resmapid_rules_rec.resolution_map_detail_id,
              P_RESOLUTION_MAP_ID => l_resolution_map_id,
              P_INCIDENT_TYPE_ID => cs_sr_resmapid_rules_rec.incident_type_id,
              P_INVENTORY_ITEM_ID => cs_sr_resmapid_rules_rec.inventory_item_id,
              P_ORGANIZATION_ID => cs_sr_resmapid_rules_rec.organization_id,
              P_CATEGORY_ID => cs_sr_resmapid_rules_rec.category_id,
              P_PROBLEM_CODE => cs_sr_resmapid_rules_rec.problem_code,
              P_MAP_START_DATE_ACTIVE => cs_sr_resmapid_crit_rec.start_date_active,
              P_MAP_END_DATE_ACTIVE => cs_sr_resmapid_crit_rec.end_date_active,
              P_RESOLUTION_CODE => cs_sr_resmapid_rules_rec.resolution_code,
              P_START_DATE_ACTIVE => cs_sr_resmapid_rules_rec.start_date_active,
              P_END_DATE_ACTIVE => cs_sr_resmapid_rules_rec.end_date_active,
              P_OBJECT_VERSION_NUMBER => null,
              P_ATTRIBUTE1 =>  null,
              P_ATTRIBUTE2 =>  null,
              P_ATTRIBUTE3 =>  null,
              P_ATTRIBUTE4 =>  null,
              P_ATTRIBUTE5 =>  null,
              P_ATTRIBUTE6 =>  null,
              P_ATTRIBUTE7 =>  null,
              P_ATTRIBUTE8 =>  null,
              P_ATTRIBUTE9 =>  null,
              P_ATTRIBUTE10 =>  null,
              P_ATTRIBUTE11 =>  null,
              P_ATTRIBUTE12 =>  null,
              P_ATTRIBUTE13 =>  null,
              P_ATTRIBUTE14 =>  null,
              P_ATTRIBUTE15 =>  null,
              P_ATTRIBUTE_CATEGORY =>  null,
              P_LAST_UPDATE_DATE => l_current_date,
              P_LAST_UPDATED_BY => l_created_by,
              P_LAST_UPDATE_LOGIN => l_login,
              X_RETURN_STATUS => l_return_status,
              X_MSG_COUNT => l_msg_count,
              X_MSG_DATA => l_errmsg
            );