[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
In case when the duplicate api is called from update, p_incident_id must be passed.
In return this procedure will return:
x_duplicate_flag => Values Y/N, indicating if duplicate was found
x_sr_dupl_rec => List of Incident Id and Reason for which duplicate was found
x_dup_found_at => Values 'EA', 'SR', 'BOTH', 'NONE', for what duplicate was found (used in iSupport workflow)
*/
PROCEDURE Duplicate_Check
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
p_incident_id IN NUMBER,
p_incident_type_id IN NUMBER,
p_customer_product_ID IN NUMBER,
p_instance_serial_number IN VARCHAR2,
p_current_serial_number IN VARCHAR2,
p_inv_item_serial_number IN VARCHAR2,
p_customer_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_cs_extended_attr IN cs_extended_attr_tbl,
p_incident_address IN cs_incident_address_rec,
x_duplicate_flag OUT NOCOPY varchar2,
x_sr_dupl_rec OUT NOCOPY Sr_Dupl_Tbl,
x_dup_found_at OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_ea_attr_dup_flag VARCHAR2(1) := FND_API.g_false;
select SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
WHERE INCIDENT_TYPE_ID = p_incident_type_id;
SELECT duplicate_offset, duplicate_uom, dup_chk_incident_addr_flag
FROM CUG_SR_TYPE_DUP_CHK_INFO
WHERE INCIDENT_TYPE_ID = l_incident_type_id;
SELECT sr.incident_id, sr.incident_number, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE sr.INCIDENT_TYPE_ID = l_incident_type_id and
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.LAST_UPDATE_DATE > l_duplicate_date and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
ORDER BY sr.incident_id DESC;
SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE sr.INCIDENT_LOCATION_ID = loc.LOCATION_ID AND
sr.incident_location_type = 'HZ_LOCATION' AND
nvl(upper(loc.ADDRESS1||decode(loc.address2,null,null,';'||loc.address2) ||decode(loc.address3,null,null,';'||loc.address3)||decode(loc.address4,null,null,';'||loc.address4)), 'Not Filled') =
sr.LAST_UPDATE_DATE > l_duplicate_date AND
sr.INCIDENT_TYPE_ID = l_incident_type_id AND
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
UNION
SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link, hz_party_sites sites , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE sr.INCIDENT_LOCATION_ID = sites.party_site_id AND
sr.incident_location_type = 'HZ_PARTY_SITE' AND
sites.location_id = loc.location_id AND
nvl(upper(loc.ADDRESS1||decode(loc.address2,null,null,';'||loc.address2) ||decode(loc.address3,null,null,';'||loc.address3)||decode(loc.address4,null,null,';'||loc.address4)), 'Not Filled') =
sr.LAST_UPDATE_DATE > l_duplicate_date AND
sr.INCIDENT_TYPE_ID = l_incident_type_id AND
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
UNION
SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE nvl(upper(incident_ADDRESS), 'Not Filled') = nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
nvl(upper(incident_CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
nvl(upper(incident_STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
nvl(upper(incident_POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
nvl(upper(incident_COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.incident_country), 'Not Filled') AND
sr.LAST_UPDATE_DATE > l_duplicate_date AND
sr.INCIDENT_TYPE_ID = l_incident_type_id AND
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
ORDER BY incident_id desc;
SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE sr.INCIDENT_TYPE_ID = l_incident_type_id and
sr.incident_id <> l_incident_id and
sr.LAST_UPDATE_DATE > l_duplicate_date and
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
ORDER BY sr.incident_id desc;
SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE sr.INCIDENT_LOCATION_ID = loc.LOCATION_ID AND
sr.incident_location_type = 'HZ_LOCATION' AND
nvl(upper(loc.ADDRESS1||decode(loc.address2,null,null,';'||loc.address2) ||decode(loc.address3,null,null,';'||loc.address3)||decode(loc.address4,null,null,';'||loc.address4)), 'Not Filled') =
sr.LAST_UPDATE_DATE > l_duplicate_date AND
sr.incident_id <> l_incident_id and
sr.INCIDENT_TYPE_ID = l_incident_type_id AND
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
UNION
SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link, hz_party_sites sites , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE sr.INCIDENT_LOCATION_ID = sites.party_site_id AND
sr.incident_location_type = 'HZ_PARTY_SITE' AND
sites.location_id = loc.location_id AND
nvl(upper(loc.ADDRESS1||decode(loc.address2,null,null,';'||loc.address2) ||decode(loc.address3,null,null,';'||loc.address3)||decode(loc.address4,null,null,';'||loc.address4)), 'Not Filled') =
sr.LAST_UPDATE_DATE > l_duplicate_date AND
sr.incident_id <> l_incident_id and
sr.INCIDENT_TYPE_ID = l_incident_type_id AND
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
UNION
SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
FROM cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
WHERE nvl(upper(incident_ADDRESS), 'Not Filled') = nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
nvl(upper(incident_CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
nvl(upper(incident_STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
nvl(upper(incident_POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
nvl(upper(incident_COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.incident_country), 'Not Filled') AND
sr.LAST_UPDATE_DATE > l_duplicate_date AND
sr.incident_id <> l_incident_id and
INCIDENT_TYPE_ID = l_incident_type_id AND
sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
ORDER BY incident_id desc;
select SR_ATTRIBUTE_CODE from CUG_SR_TYPE_ATTR_MAPS_VL
where INCIDENT_TYPE_ID = l_incident_type_id AND
SR_ATTR_DUP_CHECK_FLAG = 'Y' AND
( END_DATE_ACTIVE IS NULL OR
to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl
WHERE sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
incident_id = p_inc_Id;
SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl
WHERE sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
incident_id = l_incident_Id;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
upper(sr.current_serial_number) = upper(l_current_serial_number)
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
upper(sr.current_serial_number) = upper(l_current_serial_number)
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
inst.instance_id = sr.customer_product_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
inst.serial_number = l_instance_serial_number
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst ,cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_product_id = inst.instance_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
inst.serial_number = l_instance_serial_number
order by sr.incident_id desc ;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.item_serial_number = l_inv_item_serial_number
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.item_serial_number = l_inv_item_serial_number
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.link_type(+) = 'DUP' and
sr_link.subject_type(+) = 'SR' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.customer_product_id = l_customer_product_id
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.customer_product_id = l_customer_product_id
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.inventory_item_id = l_inventory_item_id
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.inventory_item_id = l_inventory_item_id
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link, cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr.inventory_item_id = l_inventory_item_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
upper(sr.current_serial_number) = upper(l_current_serial_number)
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr.inventory_item_id = l_inventory_item_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
upper(sr.current_serial_number) = upper(l_current_serial_number)
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr.inventory_item_id = l_inventory_item_id and
inst.instance_id = sr.customer_product_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
inst.serial_number = l_instance_serial_number
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr.inventory_item_id = l_inventory_item_id and
sr.customer_product_id = inst.instance_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
inst.serial_number = l_instance_serial_number
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr.inventory_item_id = l_inventory_item_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.item_serial_number = l_inv_item_serial_number
order by sr.incident_id desc;
select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incidents_b_sec sr, cs_incident_links sr_link , cs_incident_statuses_vl sr_stat -- 12.1.2 - DUP CHECK
where sr.incident_id = sr_link.subject_id(+) and
sr_link.subject_type(+) = 'SR' and
sr_link.link_type(+) = 'DUP' and
sr_link.end_date_active(+) is null and
sr.incident_id <> l_incident_id and
sr.last_update_date > l_duplicate_date and
sr_link.last_update_date(+) > l_duplicate_date and
sr.customer_id = l_customer_id and
sr.inventory_item_id = l_inventory_item_id and
sr_stat.incident_status_id = sr.incident_status_id and -- 12.1.2 - DUP CHECK
sr_stat.DUP_CHK_FLAG = 'Y' and -- 12.1.2 - DUP CHECK
sr.item_serial_number = l_inv_item_serial_number
order by sr.incident_id desc;
select sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
from cs_incident_links sr_link
where sr_link.subject_id = p_original_id and
sr_link.subject_type = 'SR' and
sr_link.link_type_id = 4 and
sr_link.end_date_active is null
order by sr_link.object_id desc;
SELECT lookup_code, description from cs_lookups
WHERE lookup_type = 'CS_SR_DUPLICATE_REASON_CODE'
and lookup_code = p_lookup_code;
SELECT duplicate_offset, duplicate_uom FROM CUG_SR_TYPE_DUP_CHK_INFO
WHERE INCIDENT_TYPE_ID = l_incident_type_id;
SELECT conversion_rate FROM MTL_UOM_CONVERSIONS
WHERE UNIT_OF_MEASURE = l_duplicate_uom
and inventory_item_id = 0;
SELECT conversion_rate FROM MTL_UOM_CONVERSIONS
WHERE uom_code = l_duplicate_uom;