[Home] [Help]
PACKAGE BODY: APPS.CS_SR_DUP_CHK_PVT
Source
1 PACKAGE BODY CS_SR_DUP_CHK_PVT
2 /* $Header: csdpchkb.pls 120.2.12000000.3 2007/09/08 08:44:12 vpremach ship $ */
3 AS
4
5 /*
6 This is the main procedure that will be called to do duplicate check.
7 Based on the parameter passed this procedure will perform duplicate checking. Calling application
8 will determine and pass parameters accordingly for performing duplicate check.
9 For example:
10 If profile is to do 'Instance/Customer, product, serial number'
11 p_customer_prodct_id (Instance), p_customer_id, p_inventory_item_id, and either of the value for
12 serial number will be passed in:
13 p_instance_serial_number (for search in IB) or
14 p_item_serial_number (for search in Item Serial master) or
15 p_current_serial_number (for free form serial number search)
16 If Instance is passed, duplicate check api will perform dup check on instance info only and will ignore
17 other parameters, i.e., p_customer_id, p_inventory_item_id etc.
18
19 If profile is to do 'All with serial'
20 p_cs_extended_attr (extended attributes), p_incident_address (incident address), including above
21 mentioned parameters
22
23 In case when the duplicate api is called from update, p_incident_id must be passed.
24
25 In return this procedure will return:
26 x_duplicate_flag => Values Y/N, indicating if duplicate was found
27 x_sr_dupl_rec => List of Incident Id and Reason for which duplicate was found
28 x_dup_found_at => Values 'EA', 'SR', 'BOTH', 'NONE', for what duplicate was found (used in iSupport workflow)
29
30 */
31
32 PROCEDURE Duplicate_Check
33 (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
36 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
37 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
38 p_incident_id IN NUMBER,
39 p_incident_type_id IN NUMBER,
40 p_customer_product_ID IN NUMBER,
41 p_instance_serial_number IN VARCHAR2,
42 p_current_serial_number IN VARCHAR2,
43 p_inv_item_serial_number IN VARCHAR2,
44 p_customer_id IN NUMBER,
45 p_inventory_item_id IN NUMBER,
46 p_cs_extended_attr IN cs_extended_attr_tbl,
47 p_incident_address IN cs_incident_address_rec,
48 x_duplicate_flag OUT NOCOPY varchar2,
49 x_sr_dupl_rec OUT NOCOPY Sr_Dupl_Tbl,
50 x_dup_found_at OUT NOCOPY VARCHAR2,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2
54 )
55 IS
56 l_ea_attr_dup_flag VARCHAR2(1) := FND_API.g_false;
57 l_cs_sr_dup_flag VARCHAR2(1) := FND_API.g_false;
58
59 l_api_name CONSTANT VARCHAR2(30) := 'SR_DUPLICATE_CHECK_API';
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_return_status VARCHAR2(1);
62
63 l_cs_ea_dup_rec Sr_Dupl_tbl;
64 l_cs_sr_dup_rec sr_dupl_tbl;
65
66 l_dup_from NUMBER;
67 l_ea_ia_dup VARCHAR2(1);
68 l_ea_ea_dup VARCHAR2(1);
69
70 BEGIN
71
72 IF NOT FND_API.Compatible_API_Call
73 (l_api_version, p_api_version , l_api_name, G_PKG_NAME )
74 THEN
75 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
76 END IF;
77
78 -- Initialize message list if p_init_msg_list is set to TRUE.
79 IF FND_API.to_Boolean( p_init_msg_list ) THEN
80 FND_MSG_PUB.initialize;
81 END IF;
82
83 -- Initialize API return status to success
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86 IF (p_cs_extended_attr.count > 0 or
87 ( p_incident_address.incident_address is not NULL
88 or p_incident_address.incident_city is not NULL
89 or p_incident_address.incident_state is not NULL
90 or p_incident_address.incident_country is not NULL
91 or p_incident_address.incident_postal_code is not NULL)
92 or p_incident_id is not NULL ) then
93
94 Check_EA_Duplicate_Setup
95 ( p_incident_id => p_incident_id,
96 p_incident_type_id => p_incident_type_id,
97 p_cs_extended_attr => p_cs_extended_attr,
98 p_incident_address => p_incident_address,
99 p_ea_attr_dup_flag => l_ea_attr_dup_flag,
100 p_cs_ea_dup_rec => l_cs_ea_dup_rec,
101 p_ea_ia_dup => l_ea_ia_dup,
102 p_ea_ea_dup => l_ea_ea_dup,
103 p_return_status => l_return_status
104 );
105 END IF;
106
107 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
110 RAISE FND_API.G_EXC_ERROR;
111 END IF;
112
113 IF ( ( p_customer_product_id is not NULL
114 or p_current_serial_number is not NULL
115 or p_instance_serial_number is not NULL
116 or p_inv_item_serial_number is not NULL
117 or p_customer_id is not NULL
118 or p_inventory_item_id is not NULL )
119 ) then
120
121 Perform_Dup_on_SR_field
122 ( p_customer_product_id => p_customer_product_id,
123 p_customer_id => p_customer_id,
124 p_inventory_item_id => p_inventory_item_id,
125 p_instance_serial_number=> p_instance_serial_number,
126 p_current_serial_number => p_current_serial_number,
127 p_inv_item_serial_number=> p_inv_item_serial_number,
128 p_incident_id => p_incident_id,
129 p_cs_sr_dup_rec => l_cs_sr_dup_rec,
130 p_cs_sr_dup_flag => l_cs_sr_dup_flag,
131 p_dup_from => l_dup_from,
132 p_return_status => l_return_status
133 );
134 END IF;
135
136 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
139 RAISE FND_API.G_EXC_ERROR;
140 END IF;
141
142 IF l_ea_attr_dup_flag = fnd_api.g_true and l_cs_sr_dup_flag = fnd_api.g_true THEN
143 x_dup_found_at := 'BOTH';
144 Construct_Unique_List_Dup_SR(
145 p_cs_ea_dup_rec => l_cs_ea_dup_rec,
146 p_ea_attr_dup_flag => l_ea_attr_dup_flag,
147 p_cs_sr_dup_rec => l_cs_sr_dup_rec,
148 p_cs_sr_dup_flag => l_cs_sr_dup_flag,
149 p_dup_from => l_dup_from,
150 p_ea_ea_dup => l_ea_ea_dup,
151 p_ea_ia_dup => l_ea_ia_dup,
152 p_sr_dup_rec => x_sr_dupl_rec,
153 p_duplicate_flag => x_duplicate_flag,
154 p_return_status => l_return_status
155 );
156
157 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162
163 ELSIF l_ea_attr_dup_flag = fnd_api.g_true and l_cs_sr_dup_flag = fnd_api.g_false THEN
164 x_dup_found_at := 'EA';
165 x_sr_dupl_rec := l_cs_ea_dup_rec;
166 x_duplicate_flag := fnd_api.g_true;
167
168 ELSIF l_cs_sr_dup_flag = fnd_api.g_true and l_ea_attr_dup_flag = fnd_api.g_false THEN
169 x_dup_found_at := 'SR';
170 x_sr_dupl_rec := l_cs_sr_dup_rec;
171 x_duplicate_flag := fnd_api.g_true;
172
173 ELSE
174 x_dup_found_at := 'NONE';
175 END IF;
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 EXCEPTION
179 WHEN FND_API.G_EXC_ERROR THEN
180 x_return_status := FND_API.G_RET_STS_ERROR ;
181 FND_MSG_PUB.Count_And_Get
182 (p_count => x_msg_count,
183 p_data => x_msg_data);
184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
186 FND_MSG_PUB.Count_And_Get
187 (p_count => x_msg_count,
188 p_data => x_msg_data);
189 WHEN OTHERS THEN
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
191 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
192 THEN
193 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
194 l_api_name );
195 END IF;
196 FND_MSG_PUB.Count_And_Get
197 (p_count => x_msg_count,
198 p_data => x_msg_data);
199 END Duplicate_Check;
200
201
202 /*
203 This procedure checks if duplicate check needs to be performed on extended attributes and
204 calls Perform_EA_Duplicate accordingly.
205
206 */
207
208 PROCEDURE Check_EA_Duplicate_Setup
209 (
210 p_incident_id IN NUMBER,
211 p_incident_type_id IN NUMBER,
212 p_cs_extended_attr IN cs_extended_attr_tbl,
213 p_incident_address IN cs_incident_address_rec,
214 p_ea_attr_dup_flag IN OUT NOCOPY varchar2,
215 p_cs_ea_dup_rec OUT NOCOPY sr_dupl_tbl,
216 p_ea_ia_dup OUT NOCOPY VARCHAR2,
217 p_ea_ea_dup OUT NOCOPY VARCHAR2,
218 p_return_status OUT NOCOPY VARCHAR2
219 )
220 IS
221
222 CURSOR c_CheckIfDupCheckOn_csr IS
223 select SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
224 WHERE INCIDENT_TYPE_ID = p_incident_type_id;
225 c_CheckIfDupCheckOn_rec c_CheckIfDupCheckOn_csr%ROWTYPE;
226
227 l_incident_type_id NUMBER := p_incident_type_id;
228 cs_ea_dup_rec sr_dupl_tbl;
229 cs_dup_prof_value varchar2(100);
230
231 BEGIN
232 p_return_status := FND_API.G_RET_STS_SUCCESS;
233 OPEN c_CheckIfDupCheckOn_Csr;
234 FETCH c_CheckIfDupCheckOn_Csr INTO c_CheckIfDupCheckOn_rec;
235
236 IF (c_CheckIfDupCheckOn_csr%NOTFOUND) THEN
237 p_ea_attr_dup_flag := fnd_api.g_false;
238 return;
239 END IF;
240 CLOSE c_CheckIfDupCheckOn_csr;
241
242 IF c_CheckIfDupCheckOn_rec.sr_dup_check_flag <> 'Y' THEN
243 p_ea_attr_dup_flag := fnd_api.g_false;
244 return;
245 END IF;
246
247 cs_dup_prof_value := fnd_profile.value('CS_SR_DUP_CHK_CRITERIA');
248
249 IF p_incident_id is not null and
250 (cs_dup_prof_value NOT IN ('CS_DUP_CRIT_EA_ADDR', 'CS_DUP_CRIT_WITHNO_SERIAL', 'CS_DUP_CRIT_WITH_SERIAL') or cs_dup_prof_value is null)
251 THEN
252 p_ea_attr_dup_flag := fnd_api.g_false;
253 return;
254 END IF;
255
256 Perform_EA_Duplicate(
257 p_incident_id => p_incident_id,
258 p_incident_type_id => p_incident_type_id,
259 p_cs_extended_attr => p_cs_extended_attr,
260 p_incident_address => p_incident_address,
261 p_ea_attr_dup_flag => p_ea_attr_dup_flag,
262 p_cs_ea_dup_rec => cs_ea_dup_rec,
263 p_ea_ia_dup => p_ea_ia_dup,
264 p_ea_ea_dup => p_ea_ea_dup,
265 p_return_status => p_return_status
266 );
267
268 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
269 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270 return;
271 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
272 p_return_status := FND_API.G_RET_STS_ERROR;
273 return;
274 END IF;
275 p_cs_ea_dup_rec := cs_ea_dup_rec;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 return;
281 END Check_EA_Duplicate_Setup;
282
283
284 /*
285 This procedure performs duplicate check on extended attributes.
286 */
287
288 PROCEDURE Perform_EA_Duplicate
289 (
290 p_incident_id IN NUMBER,
291 p_incident_type_id IN NUMBER,
292 p_cs_extended_attr IN cs_extended_attr_tbl,
293 p_incident_address IN cs_incident_address_rec,
294 p_ea_attr_dup_flag IN OUT NOCOPY varchar2,
295 p_cs_ea_dup_rec OUT NOCOPY sr_dupl_tbl,
296 p_ea_ia_dup OUT NOCOPY VARCHAR2,
297 p_ea_ea_dup OUT NOCOPY VARCHAR2,
298 p_return_status OUT NOCOPY VARCHAR2
299 )
300 IS
301
302 l_duplicate_date date;
303 l_incident_type_id NUMBER := p_incident_type_id;
304 l_incident_id NUMBER := p_incident_id;
305
306 l_ea_dup_found_tbl SR_Dupl_Link_Tbl;
307 l_ea_dup_sr_rec SR_Dupl_Tbl;
308
309 l_incident_address CS_Incident_Address_Rec;
310
311 match_found NUMBER := 0;
312 cnt NUMBER := 0;
313 l_SRAttribute_value_old varchar2(80);
314 l_SRAttribute_value_new varchar2(80);
315
316 CURSOR c_DuplicateTimeInfo_csr IS
317 SELECT duplicate_offset, duplicate_uom, dup_chk_incident_addr_flag
318 FROM CUG_SR_TYPE_DUP_CHK_INFO
319 WHERE INCIDENT_TYPE_ID = l_incident_type_id;
320 l_DuplicateTimeInfo_rec c_DuplicateTimeInfo_csr%ROWTYPE;
321
322 CURSOR l_IncidentId_NoAddr_csr IS
323 SELECT sr.incident_id, sr.incident_number, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
324 FROM cs_incidents_b_sec sr, cs_incident_links sr_link
325 WHERE sr.INCIDENT_TYPE_ID = l_incident_type_id and
326 sr.incident_id = sr_link.subject_id(+) and
327 sr_link.subject_type(+) = 'SR' and
328 sr_link.link_type(+) = 'DUP' and
329 sr_link.end_date_active(+) is null and
330 sr.LAST_UPDATE_DATE > l_duplicate_date and
331 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
332 ORDER BY sr.incident_id DESC;
333
334 l_IncidentId_NoAddr_rec l_IncidentId_NoAddr_csr%ROWTYPE;
335
336
337 -- Fixed bug 3509580, added UNION for party_site_id stored in incident_location_id where incident_location_type is 'HZ_PARTY_SITE'
338 CURSOR l_IncidentId_withAddr_csr IS
339 SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
340 FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link
341 WHERE sr.INCIDENT_LOCATION_ID = loc.LOCATION_ID AND
342 sr.incident_location_type = 'HZ_LOCATION' AND
343 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') =
344 nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
345 nvl(upper(loc.CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
346 nvl(upper(loc.STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
347 nvl(upper(loc.POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
348 nvl(upper(loc.COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.INCIDENT_COUNTRY), 'Not Filled') AND
349 sr.LAST_UPDATE_DATE > l_duplicate_date AND
350 sr.INCIDENT_TYPE_ID = l_incident_type_id AND
351 sr.incident_id = sr_link.subject_id(+) and
352 sr_link.subject_type(+) = 'SR' and
353 sr_link.link_type(+) = 'DUP' and
354 sr_link.end_date_active(+) is null and
355 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
356 UNION
357 SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
358 FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link, hz_party_sites sites
359 WHERE sr.INCIDENT_LOCATION_ID = sites.party_site_id AND
360 sr.incident_location_type = 'HZ_PARTY_SITE' AND
361 sites.location_id = loc.location_id AND
362 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') =
363 nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
364 nvl(upper(loc.CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
365 nvl(upper(loc.STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
366 nvl(upper(loc.POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
367 nvl(upper(loc.COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.INCIDENT_COUNTRY), 'Not Filled') AND
368 sr.LAST_UPDATE_DATE > l_duplicate_date AND
369 sr.INCIDENT_TYPE_ID = l_incident_type_id AND
370 sr.incident_id = sr_link.subject_id(+) and
371 sr_link.subject_type(+) = 'SR' and
372 sr_link.link_type(+) = 'DUP' and
373 sr_link.end_date_active(+) is null and
374 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
375 UNION
376 SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
377 FROM cs_incidents_b_sec sr, cs_incident_links sr_link
378 WHERE nvl(upper(incident_ADDRESS), 'Not Filled') = nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
379 nvl(upper(incident_CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
380 nvl(upper(incident_STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
381 nvl(upper(incident_POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
382 nvl(upper(incident_COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.incident_country), 'Not Filled') AND
383 sr.LAST_UPDATE_DATE > l_duplicate_date AND
384 sr.INCIDENT_TYPE_ID = l_incident_type_id AND
385 sr.incident_id = sr_link.subject_id(+) and
386 sr_link.subject_type(+) = 'SR' and
387 sr_link.link_type(+) = 'DUP' and
388 sr_link.end_date_active(+) is null and
389 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
390 ORDER BY incident_id desc;
391
392 -- end of bug fix 3509580, by aneemuch
393
394 L_INCIDENTID_WITHADDR_REC L_INCIDENTID_WITHADDR_csr%rowtype;
395
396 -- Cursor to find SR when called from iSupport
397 CURSOR l_IncidentId_NoAddrUpd_csr IS
398 SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
399 FROM cs_incidents_b_sec sr, cs_incident_links sr_link
400 WHERE sr.INCIDENT_TYPE_ID = l_incident_type_id and
401 sr.incident_id <> l_incident_id and
402 sr.LAST_UPDATE_DATE > l_duplicate_date and
403 sr.incident_id = sr_link.subject_id(+) and
404 sr_link.subject_type(+) = 'SR' and
405 sr_link.link_type(+) = 'DUP' and
406 sr_link.end_date_active(+) is null and
407 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
408 ORDER BY sr.incident_id desc;
409
410 l_IncidentId_NoAddrUpd_rec l_IncidentId_NoAddrUpd_csr%ROWTYPE;
411
412 -- Fixed bug 3509580, added UNION for party_site_id stored in incident_location_id where incident_location_type is 'HZ_PARTY_SITE'
413 CURSOR l_IncidentId_withAddrUpd_csr IS
414 SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
415 FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link
416 WHERE sr.INCIDENT_LOCATION_ID = loc.LOCATION_ID AND
417 sr.incident_location_type = 'HZ_LOCATION' AND
418 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') =
419 nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
420 nvl(upper(loc.CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
421 nvl(upper(loc.STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
422 nvl(upper(loc.POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
423 nvl(upper(loc.COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.incident_country), 'Not Filled') AND
424 sr.LAST_UPDATE_DATE > l_duplicate_date AND
425 sr.incident_id <> l_incident_id and
426 sr.INCIDENT_TYPE_ID = l_incident_type_id AND
427 sr.incident_id = sr_link.subject_id(+) and
428 sr_link.subject_type(+) = 'SR' and
429 sr_link.link_type(+) = 'DUP' and
430 sr_link.end_date_active(+) is null and
431 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
432 UNION
433 SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
434 FROM cs_incidents_b_sec sr, HZ_LOCATIONS loc, cs_incident_links sr_link, hz_party_sites sites
435 WHERE sr.INCIDENT_LOCATION_ID = sites.party_site_id AND
436 sr.incident_location_type = 'HZ_PARTY_SITE' AND
437 sites.location_id = loc.location_id AND
438 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') =
439 nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
440 nvl(upper(loc.CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
441 nvl(upper(loc.STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
442 nvl(upper(loc.POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
443 nvl(upper(loc.COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.incident_country), 'Not Filled') AND
444 sr.LAST_UPDATE_DATE > l_duplicate_date AND
445 sr.incident_id <> l_incident_id and
446 sr.INCIDENT_TYPE_ID = l_incident_type_id AND
447 sr.incident_id = sr_link.subject_id(+) and
448 sr_link.subject_type(+) = 'SR' and
449 sr_link.link_type(+) = 'DUP' and
450 sr_link.end_date_active(+) is null and
451 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
452 UNION
453 SELECT sr.INCIDENT_ID, sr.INCIDENT_NUMBER, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
454 FROM cs_incidents_b_sec sr, cs_incident_links sr_link
455 WHERE nvl(upper(incident_ADDRESS), 'Not Filled') = nvl(upper(l_incident_address.incident_address), 'Not Filled') AND
456 nvl(upper(incident_CITY), 'Not Filled') = nvl(upper(l_incident_address.incident_city), 'Not Filled') AND
457 nvl(upper(incident_STATE), 'Not Filled') = nvl(upper(l_incident_address.incident_state), 'Not Filled') AND
458 nvl(upper(incident_POSTAL_CODE), 'Not Filled') = nvl(upper(l_incident_address.incident_postal_Code), 'Not Filled') AND
459 nvl(upper(incident_COUNTRY), 'Not Filled') = nvl(upper(l_incident_address.incident_country), 'Not Filled') AND
460 sr.LAST_UPDATE_DATE > l_duplicate_date AND
461 sr.incident_id <> l_incident_id and
462 INCIDENT_TYPE_ID = l_incident_type_id AND
463 sr.incident_id = sr_link.subject_id(+) and
464 sr_link.subject_type(+) = 'SR' and
465 sr_link.link_type(+) = 'DUP' and
466 sr_link.end_date_active(+) is null and
467 sr_link.LAST_UPDATE_DATE(+) > l_duplicate_date
468 ORDER BY incident_id desc;
469
470 -- End of bug fix 3509580, by aneemuch
471
472 l_IncidentId_withAddrUpd_rec l_IncidentId_withAddrUpd_csr%ROWTYPE;
473
474 CURSOR l_DuplicateCheckAttrs_csr IS
475 select SR_ATTRIBUTE_CODE from CUG_SR_TYPE_ATTR_MAPS_VL
476 where INCIDENT_TYPE_ID = l_incident_type_id AND
477 SR_ATTR_DUP_CHECK_FLAG = 'Y' AND
478 ( END_DATE_ACTIVE IS NULL OR
479 to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
480
481 l_DuplicateCheckAttrs_rec l_DuplicateCheckAttrs_csr%ROWTYPE;
482
483 CURSOR l_OldDupAttrValue_csr (p_inc_id NUMBER) IS
484 SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl
485 WHERE sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
486 incident_id = p_inc_Id;
487
488 CURSOR l_DupAttrValueUpd_csr IS
489 SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl
490 WHERE sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
491 incident_id = l_incident_Id;
492
493 BEGIN
494
495 p_return_status := FND_API.G_RET_STS_SUCCESS;
496 OPEN c_DuplicateTimeInfo_csr;
497 FETCH c_DuplicateTimeInfo_csr INTO l_DuplicateTimeInfo_rec;
498
499 CALCULATE_DUPLICATE_TIME_FRAME( p_incident_type_id => l_incident_type_id,
500 p_duplicate_time_frame => l_duplicate_date);
501
502 IF l_incident_id is NULL THEN
503 IF l_DuplicateTimeInfo_rec.dup_chk_incident_addr_flag = 'Y' THEN
504 p_ea_ia_dup := 'Y';
505 l_incident_address := p_incident_address;
506 OPEN l_IncidentId_withAddr_csr;
507 LOOP
508 FETCH l_IncidentId_withAddr_csr INTO l_IncidentId_withAddr_rec;
509 EXIT WHEN l_IncidentId_withAddr_csr%NOTFOUND;
510
511 match_found := 1;
512 OPEN l_DuplicateCheckAttrs_csr;
513 LOOP
514 FETCH l_DuplicateCheckAttrs_csr into l_DuplicateCheckAttrs_rec;
515 EXIT WHEN l_DuplicateCheckAttrs_csr%NOTFOUND;
516
517 OPEN l_OldDupAttrValue_csr (l_IncidentId_withAddr_rec.incident_id);
518 FETCH l_OldDupAttrValue_csr into l_SRAttribute_value_old;
519 IF(l_OldDupAttrValue_csr%NOTFOUND) THEN
520 l_SRAttribute_value_old := ' ';
521 END IF;
522 CLOSE l_OldDupAttrValue_csr;
523
524 IF p_cs_extended_attr.count > 0 THEN
525 FOR i in p_cs_extended_Attr.first..p_cs_extended_attr.last loop
526 IF p_cs_extended_attr(i).sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code THEN
527 l_SRAttribute_value_new := p_cs_extended_attr(i).sr_attribute_value;
528 exit;
529 END IF;
530 END LOOP;
531 END IF;
532
533 IF upper(l_SRAttribute_value_old) = upper(l_SRAttribute_value_new) THEN
534 match_found := 1;
535 ELSE
536 match_found := 0;
537 END IF;
538 END LOOP;
539
540 IF match_found <> 0 OR l_DuplicateCheckAttrs_csr%ROWCOUNT = 0 THEN
541 p_ea_attr_dup_flag := fnd_api.g_true;
542 cnt := cnt + 1;
543 l_ea_dup_found_tbl(cnt).incident_id := l_IncidentId_withAddr_rec.incident_id;
544 l_ea_dup_found_tbl(cnt).incident_link_id := l_IncidentId_withAddr_rec.incident_link_id;
545 l_ea_dup_found_tbl(cnt).incident_link_number := l_IncidentId_withAddr_rec.incident_link_number;
546
547 IF l_DuplicateCheckAttrs_csr%ROWCOUNT = 0 THEN
551 p_ea_ea_dup := 'Y';
548 p_ea_ea_dup := 'N';
549 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_INCIDENT_ADDR_MCH');
550 ELSE
552 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_EA_ADDR_MCH');
553 END IF;
554 END IF;
555 CLOSE l_DuplicateCheckAttrs_csr;
556
557 END LOOP;
558 CLOSE l_IncidentId_WithAddr_csr;
559 ELSE
560 p_ea_ia_dup := 'N';
561 OPEN l_IncidentId_NoAddr_csr;
562 LOOP
563 FETCH l_IncidentId_NoAddr_csr INTO l_IncidentId_NoAddr_rec;
564 EXIT WHEN l_IncidentId_NoAddr_csr%NOTFOUND;
565
566 match_found := 1;
567 OPEN l_DuplicateCheckAttrs_csr;
568 LOOP
569 FETCH l_DuplicateCheckAttrs_csr into l_DuplicateCheckAttrs_rec;
570 EXIT WHEN l_DuplicateCheckAttrs_csr%NOTFOUND;
571
572 OPEN l_OldDupAttrValue_csr(l_IncidentId_NoAddr_rec.incident_id);
573 FETCH l_OldDupAttrValue_csr into l_SRAttribute_value_old;
574 IF(l_OldDupAttrValue_csr%NOTFOUND) THEN
575 l_SRAttribute_value_old := ' ';
576 END IF;
577 CLOSE l_OldDupAttrValue_csr;
578
579 IF p_cs_extended_attr.count > 0 THEN
580 FOR i in p_cs_extended_Attr.first..p_cs_extended_attr.last loop
581 IF p_cs_extended_attr(i).SR_ATTRIBUTE_CODE = l_DuplicateCheckAttrs_rec.sr_attribute_code THEN
582 l_SRAttribute_value_new := p_cs_extended_attr(i).sr_attribute_value;
583 exit ;
584 END IF;
585 END LOOP;
586 END IF;
587
588 IF upper(l_SRAttribute_value_old) = upper(l_SRAttribute_value_new) THEN
589 match_found := 1;
590 ELSE
591 match_found := 0;
592 END IF;
593 END LOOP;
594
595 IF match_found <> 0 OR l_DuplicateCheckAttrs_csr%ROWCOUNT = 0 THEN
596 p_ea_attr_dup_flag := fnd_api.g_true;
597 cnt := cnt + 1;
598 p_ea_ea_dup := 'Y';
599 l_ea_dup_found_tbl(cnt).incident_id := l_IncidentId_NoAddr_rec.incident_id;
600 l_ea_dup_found_tbl(cnt).incident_link_id := l_IncidentId_NoAddr_rec.incident_link_id;
601 l_ea_dup_found_tbl(cnt).incident_link_number := l_IncidentId_NoAddr_rec.incident_link_number;
602 -- l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_EA_MCH');
603 IF l_DuplicateCheckAttrs_csr%ROWCOUNT = 0 THEN
604 -- p_ea_ea_dup := 'N';
605 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_INCIDENT_ADDR_MCH');
606 ELSE
607 p_ea_ea_dup := 'Y';
608 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_EA_MCH');
609 END IF;
610
611 END IF;
612 CLOSE l_DuplicateCheckAttrs_csr;
613
614 END LOOP;
615 CLOSE l_IncidentId_NoAddr_csr;
616 END IF;
617 ELSE
618 -- Provide same logic in update mode for SR created through iSupport and Email...
619 IF l_DuplicateTimeInfo_rec.dup_chk_incident_addr_flag = 'Y' THEN
620 p_ea_ia_dup := 'Y';
621 l_incident_address := p_incident_address;
622 OPEN l_IncidentId_withAddrUpd_csr;
623 LOOP
624 FETCH l_IncidentId_withAddrUpd_csr INTO l_IncidentId_withAddrUpd_rec;
625 EXIT WHEN l_IncidentId_withAddrUpd_csr%NOTFOUND;
626
627 OPEN l_DuplicateCheckAttrs_csr;
628 LOOP
629 FETCH l_DuplicateCheckAttrs_csr into l_DuplicateCheckAttrs_rec;
630 EXIT WHEN l_DuplicateCheckAttrs_csr%NOTFOUND;
631
632 OPEN l_OldDupAttrValue_csr (l_IncidentId_withAddrUpd_rec.incident_id);
633 FETCH l_OldDupAttrValue_csr into l_SRAttribute_value_old;
634 IF(l_OldDupAttrValue_csr%NOTFOUND) THEN
635 l_SRAttribute_value_old := 'XXX';
636 END IF;
637 CLOSE l_OldDupAttrValue_csr;
638
639 OPEN l_DupAttrValueUpd_csr;
640 FETCH l_DupAttrValueUpd_csr into l_SRAttribute_value_new;
641 IF(l_DupAttrValueUpd_csr%NOTFOUND) THEN
642 l_SRAttribute_value_new := 'YYY';
643 END IF;
644 CLOSE l_DupAttrValueUpd_csr;
645
646 IF upper(l_SRAttribute_value_old) = upper(l_SRAttribute_value_new) THEN
647 match_found := 1;
648 ELSE
649 match_found := 0;
650 END IF;
651 END LOOP;
652
653 IF match_found <> 0 OR l_DuplicateCheckAttrs_csr%ROWCOUNT = 0 THEN
654 p_ea_attr_dup_flag := fnd_api.g_true;
655 cnt := cnt + 1;
656 l_ea_dup_found_tbl(cnt).incident_id := l_IncidentId_withAddrUpd_rec.incident_id;
657 l_ea_dup_found_tbl(cnt).incident_link_id := l_IncidentId_withAddrUpd_rec.incident_link_id;
658 l_ea_dup_found_tbl(cnt).incident_link_number := l_IncidentId_withAddrUpd_rec.incident_link_number;
659
660 IF l_DuplicateCheckAttrs_csr%ROWCOUNT = 0 THEN
661 p_ea_ea_dup := 'N';
665 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_EA_ADDR_MCH');
662 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_INCIDENT_ADDR_MCH');
663 ELSE
664 p_ea_ea_dup := 'Y';
666 END IF;
667 END IF;
668 CLOSE l_DuplicateCheckAttrs_csr;
669 END LOOP;
670 CLOSE l_IncidentId_withAddrUpd_csr;
671 ELSE
672 p_ea_ia_dup := 'N';
673 OPEN l_IncidentId_NoAddrUpd_csr;
674 LOOP
675 match_found := 1;
676 FETCH l_IncidentId_NoAddrUpd_csr INTO l_IncidentId_NoAddrUpd_rec;
677 EXIT WHEN l_IncidentId_NoAddrUpd_csr%NOTFOUND;
678 -- match_found := 1;
679 OPEN l_DuplicateCheckAttrs_csr;
680 LOOP
681 FETCH l_DuplicateCheckAttrs_csr into l_DuplicateCheckAttrs_rec;
682 EXIT WHEN l_DuplicateCheckAttrs_csr%NOTFOUND;
683
684 OPEN l_OldDupAttrValue_csr(l_IncidentId_NoAddrUpd_rec.incident_id);
685 FETCH l_OldDupAttrValue_csr into l_SRAttribute_value_old;
686 IF(l_OldDupAttrValue_csr%NOTFOUND) THEN
687 l_SRAttribute_value_old := ' ';
688 END IF;
689 CLOSE l_OldDupAttrValue_csr;
690
691 OPEN l_DupAttrValueUpd_csr;
692 FETCH l_DupAttrValueUpd_csr into l_SRAttribute_value_new;
693 IF(l_DupAttrValueUpd_csr%NOTFOUND) THEN
694 l_SRAttribute_value_new := ' ';
695 END IF;
696 CLOSE l_DupAttrValueUpd_csr;
697
698 IF upper(l_SRAttribute_value_old) = upper(l_SRAttribute_value_new) THEN
699 match_found := 1;
700 ELSE
701 match_found := 0;
702 END IF;
703 END LOOP;
704
705 IF match_found <> 0 THEN
706 p_ea_attr_dup_flag := fnd_api.g_true;
707 cnt := cnt + 1;
708 p_ea_ea_dup := 'Y';
709 l_ea_dup_found_tbl(cnt).incident_id := l_IncidentId_NoAddrUpd_rec.incident_id;
710 l_ea_dup_found_tbl(cnt).incident_link_id := l_IncidentId_NoAddrUpd_rec.incident_link_id;
711 l_ea_dup_found_tbl(cnt).incident_link_number := l_IncidentId_NoAddrUpd_rec.incident_link_number;
712 -- l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_EA_MCH');
713 IF l_DuplicateCheckAttrs_csr%ROWCOUNT = 0 THEN
714 -- p_ea_ea_dup := 'N';
715 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_INCIDENT_ADDR_MCH');
716 ELSE
717 -- p_ea_ea_dup := 'Y';
718 l_ea_dup_found_tbl(cnt).reason_desc := Get_Dup_Message('CS_EA_EA_MCH');
719 END IF;
720 END IF;
721 CLOSE l_DuplicateCheckAttrs_csr;
722
723 END LOOP;
724 CLOSE l_IncidentId_NoAddrUpd_csr;
725
726 END IF;
727 END IF;
728
729 IF l_ea_dup_found_tbl.count > 0 THEN
730 Check_Dup_SR_Link
731 ( p_dup_found_tbl => l_ea_dup_found_tbl,
732 p_dup_tbl => p_cs_ea_dup_rec,
733 p_return_status => p_return_status
734 );
735
736 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
737 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738 return;
739 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
740 p_return_status := FND_API.G_RET_STS_ERROR;
741 return;
742 END IF;
743
744 p_ea_attr_dup_flag := FND_API.g_true;
745 p_cs_ea_dup_rec := p_cs_ea_dup_rec;
746 ELSE
747 p_ea_attr_dup_flag := FND_API.g_false;
748 END IF;
749 EXCEPTION
750 WHEN OTHERS THEN
751 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
752 return;
753
754 END Perform_EA_Duplicate;
755
756
757 /*
758 This procedure is to varifies parameter passed for duplicate checking on Instance/Customer, Product, Serial number
759 and calls respective procedure to do duplicate check .
760 */
761
762 PROCEDURE Perform_Dup_on_SR_field
763 ( p_customer_product_id IN NUMBER,
764 p_customer_id IN NUMBER,
765 p_inventory_item_id IN NUMBER,
766 p_instance_serial_number IN VARCHAR2,
767 p_current_serial_number IN VARCHAR2,
768 p_inv_item_serial_number IN VARCHAR2,
769 p_incident_id IN NUMBER,
770 p_cs_sr_dup_rec IN OUT NOCOPY SR_DUPL_TBL,
771 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
772 p_dup_from IN OUT NOCOPY NUMBER,
773 p_return_status OUT NOCOPY VARCHAR2
774 )
775 IS
776 l_cs_sr_dup_link_rec SR_Dupl_Link_Tbl;
777 BEGIN
778
779 p_return_status := FND_API.G_RET_STS_SUCCESS;
780 IF p_customer_product_id is not NULL THEN
781 p_dup_from := 1;
782 Check_SR_Instance_Dup
783 ( p_customer_product_id => p_customer_product_id,
784 p_incident_id => p_incident_id,
785 p_cs_sr_dup_link_rec => l_cs_sr_dup_link_rec,
786 p_cs_sr_dup_flag => p_cs_sr_dup_flag,
790 or p_instance_serial_number is not NULL
787 p_return_status => p_return_status
788 );
789 ELSIF (p_current_serial_number is not NULL
791 or p_inv_item_serial_number is not NULL)
792 and p_customer_id IS NULL and p_inventory_item_id is NULL THEN
793 p_dup_from := 2;
794 Check_SR_SerialNum_Dup
795 ( p_instance_serial_number=> p_instance_serial_number,
796 p_current_serial_number => p_current_serial_number,
797 p_inv_item_serial_number=> p_inv_item_serial_number,
798 p_incident_id => p_incident_id,
799 p_cs_sr_dup_link_rec => l_cs_sr_dup_link_rec,
800 p_cs_sr_dup_flag => p_cs_sr_dup_flag,
801 p_return_status => p_return_status
802 );
803 ELSIF p_customer_id is not NULL
804 and p_inventory_item_id is not NULL
805 and (p_current_serial_number is NULL
806 and p_instance_serial_number is NULL
807 and p_inv_item_serial_number is NULL
808 )THEN
809 p_dup_from := 3;
810 Check_SR_CustProd_Dup
811 ( p_customer_id => p_customer_id,
812 p_inventory_item_id => p_inventory_item_id,
813 p_incident_id => p_incident_id,
814 p_cs_sr_dup_link_rec => l_cs_sr_dup_link_rec,
815 p_cs_sr_dup_flag => p_cs_sr_dup_flag,
816 p_return_status => p_return_status
817 );
818 ELSIF (p_current_serial_number is not NULL
819 or p_instance_serial_number is not NULL
820 or p_inv_item_serial_number is not NULL)
821 and p_inventory_item_id is not NULL
822 and p_customer_id is not NULL THEN
823 p_dup_from := 4;
824 Check_SR_CustProdSerial_Dup
825 ( p_customer_id => p_customer_id,
826 p_inventory_item_id => p_inventory_item_id,
827 p_instance_serial_number=> p_instance_serial_number,
828 p_current_serial_number => p_current_serial_number,
829 p_inv_item_serial_number=> p_inv_item_serial_number,
830 p_incident_id => p_incident_id,
831 p_cs_sr_dup_link_rec => l_cs_sr_dup_link_rec,
832 p_cs_sr_dup_flag => p_cs_sr_dup_flag,
833 p_return_status => p_return_status
834 );
835 END IF;
836
837 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
838 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
839 return;
840 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
841 p_return_status := FND_API.G_RET_STS_ERROR;
842 return;
843 END IF;
844
845 IF p_cs_sr_dup_flag = FND_API.g_true THEN
846 Check_Dup_SR_Link
847 ( p_dup_found_tbl => l_cs_sr_dup_link_rec,
848 p_dup_tbl => p_cs_sr_dup_rec,
849 p_return_status => p_return_status
850 );
851
852 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
853 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 return;
855 ELSIF p_return_status = FND_API.G_RET_STS_ERROR THEN
856 p_return_status := FND_API.G_RET_STS_ERROR;
857 return;
858 END IF;
859 p_cs_sr_dup_flag := FND_API.g_true;
860 END IF;
861 EXCEPTION
862 WHEN OTHERS THEN
863 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
864 return;
865
866 END Perform_Dup_on_SR_Field;
867
868
869 PROCEDURE Check_SR_SerialNum_Dup
870 (
871 p_instance_serial_number IN VARCHAR2,
872 p_current_serial_number IN VARCHAR2,
873 p_inv_item_serial_number IN VARCHAR2,
874 p_incident_id IN NUMBER,
875 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
876 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
877 p_return_status OUT NOCOPY VARCHAR2
878 )
879 IS
880
881 l_duplicate_date date;
882 l_incident_id number;
883 l_current_serial_number CS_INCIDENTS_ALL_B.current_serial_number%type;
884 l_inv_item_serial_number CS_INCIDENTS_ALL_B.item_serial_number%type;
885 l_instance_serial_number CS_INCIDENTS_ALL_B.current_serial_number%type;
886 l_cs_sr_dup_link_rec sr_dupl_link_tbl;
887
888 Cursor l_dup_sr_serialnum_csr is
889 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
890 from cs_incidents_b_sec sr, cs_incident_links sr_link
891 where sr.incident_id = sr_link.subject_id(+) and
892 sr_link.subject_type(+) = 'SR' and
893 sr_link.link_type(+) = 'DUP' and
894 sr_link.end_date_active(+) is null and
895 sr.last_update_date > l_duplicate_date and
896 sr_link.last_update_date(+) > l_duplicate_date and
897 upper(sr.current_serial_number) = upper(l_current_serial_number)
898 order by sr.incident_id desc;
899 l_dup_sr_serialnum_rec l_dup_sr_serialnum_csr%rowtype;
900
901 Cursor l_dup_sr_serialnumUpd_csr is
902 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
903 from cs_incidents_b_sec sr, cs_incident_links sr_link
904 where sr.incident_id = sr_link.subject_id(+) and
905 sr_link.subject_type(+) = 'SR' and
906 sr_link.link_type(+) = 'DUP' and
907 sr_link.end_date_active(+) is null and
908 sr.incident_id <> l_incident_id and
909 sr.last_update_date > l_duplicate_date and
910 sr_link.last_update_date(+) > l_duplicate_date and
911 upper(sr.current_serial_number) = upper(l_current_serial_number)
912 order by sr.incident_id desc;
913 l_dup_sr_serialnumUpd_rec l_dup_sr_serialnumUpd_csr%rowtype;
914
915
916 Cursor l_dup_sr_InstSerNum_csr is
917 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
918 from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst
919 where sr.incident_id = sr_link.subject_id(+) and
920 sr_link.subject_type(+) = 'SR' and
921 sr_link.link_type(+) = 'DUP' and
922 sr_link.end_date_active(+) is null and
923 sr.last_update_date > l_duplicate_date and
924 sr_link.last_update_date(+) > l_duplicate_date and
925 inst.instance_id = sr.customer_product_id and
926 inst.serial_number = l_instance_serial_number
927 order by sr.incident_id desc;
928
929 l_dup_sr_InstSerNum_rec l_dup_sr_InstSerNum_csr%rowtype;
930
931 Cursor l_dup_sr_InstSerNumUpd_csr is
932 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
933 from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst
934 where sr.incident_id = sr_link.subject_id(+) and
935 sr_link.subject_type(+) = 'SR' and
936 sr_link.link_type(+) = 'DUP' and
937 sr_link.end_date_active(+) is null and
938 sr.incident_id <> l_incident_id and
939 sr.last_update_date > l_duplicate_date and
940 sr_link.last_update_date(+) > l_duplicate_date and
941 sr.customer_product_id = inst.instance_id and
942 inst.serial_number = l_instance_serial_number
943 order by sr.incident_id desc ;
944 l_dup_sr_InstSerNumUpd_rec l_dup_sr_InstSerNumUpd_csr%rowtype;
945
946 Cursor l_dup_sr_ItemSerNum_csr is
947 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
948 from cs_incidents_b_sec sr, cs_incident_links sr_link
949 where sr.incident_id = sr_link.subject_id(+) and
950 sr_link.subject_type(+) = 'SR' and
951 sr_link.link_type(+) = 'DUP' and
952 sr_link.end_date_active(+) is null and
953 sr.last_update_date > l_duplicate_date and
954 sr_link.last_update_date(+) > l_duplicate_date and
955 sr.item_serial_number = l_inv_item_serial_number
956 order by sr.incident_id desc;
957 l_dup_sr_ItemSerNum_rec l_dup_sr_ItemSerNum_csr%rowtype;
958
959 Cursor l_dup_sr_ItemSerNumUpd_csr is
960 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
961 from cs_incidents_b_sec sr, cs_incident_links sr_link
962 where sr.incident_id = sr_link.subject_id(+) and
963 sr_link.subject_type(+) = 'SR' and
964 sr_link.link_type(+) = 'DUP' and
965 sr_link.end_date_active(+) is null and
966 sr.incident_id <> l_incident_id and
967 sr.last_update_date > l_duplicate_date and
968 sr_link.last_update_date(+) > l_duplicate_date and
969 sr.item_serial_number = l_inv_item_serial_number
970 order by sr.incident_id desc;
971
972 l_dup_sr_ItemSerNumUpd_rec l_dup_sr_ItemSerNumUpd_csr%rowtype;
973
974 l_dup_counter number := 0;
975
976 BEGIN
977 p_return_status := FND_API.G_RET_STS_SUCCESS;
978
979 CALCULATE_DUPLICATE_TIME_FRAME( p_duplicate_time_frame => l_duplicate_date);
980
981 l_incident_id := p_incident_id;
982 l_current_serial_number := p_current_serial_number;
983 l_inv_item_serial_number := p_inv_item_serial_number;
984 l_instance_serial_number := p_instance_serial_number;
985
986 IF p_current_serial_number IS NOT NULL THEN
987 IF l_incident_id is not NULL THEN
988
989 Open l_dup_sr_serialnumUpd_csr;
990 LOOP
991 FETCH l_dup_sr_serialnumUpd_csr into l_dup_sr_serialnumUpd_rec;
992 EXIT WHEN l_dup_sr_serialnumUpd_csr%NOTFOUND;
993
994 l_dup_counter := l_dup_counter + 1;
995 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_serialnumUpd_rec.incident_id;
996 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_serialnumUpd_rec.incident_link_id;
997 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_serialnumUpd_rec.incident_link_number;
998 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := 'Serial number match found';
999 END LOOP;
1000 ELSE
1001 Open l_dup_sr_serialnum_csr;
1002 LOOP
1003 FETCH l_dup_sr_serialnum_csr into l_dup_sr_serialnum_rec;
1004 EXIT WHEN l_dup_sr_serialnum_csr%NOTFOUND;
1005
1006 l_dup_counter := l_dup_counter + 1;
1007 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_serialnum_rec.incident_id;
1008 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_serialnum_rec.incident_link_id;
1009 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_serialnum_rec.incident_link_number;
1010 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := 'Serial number match found';
1011 END LOOP;
1012 END IF;
1013 ELSIF p_instance_serial_number IS NOT NULL THEN
1014 IF l_incident_id is not NULL THEN
1015
1016 Open l_dup_sr_InstSerNumUpd_csr;
1017 LOOP
1018 FETCH l_dup_sr_InstSerNumUpd_csr into l_dup_sr_InstSerNumUpd_rec;
1019 EXIT WHEN l_dup_sr_InstSerNumUpd_csr%NOTFOUND;
1020
1021 l_dup_counter := l_dup_counter + 1;
1022 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_InstSerNumUpd_rec.incident_id;
1023 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_InstSerNumUpd_rec.incident_link_id;
1024 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_InstSerNumUpd_rec.incident_link_number;
1025 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := 'Serial number match found';
1026 END LOOP;
1027 ELSE
1028 Open l_dup_sr_InstSerNum_csr;
1029 LOOP
1030 FETCH l_dup_sr_InstSerNum_csr into l_dup_sr_InstSerNum_rec;
1031 EXIT WHEN l_dup_sr_InstSerNum_csr%NOTFOUND;
1032
1033 l_dup_counter := l_dup_counter + 1;
1034 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_InstSerNum_rec.incident_id;
1035 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_InstSerNum_rec.incident_link_id;
1036 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_InstSerNum_rec.incident_link_number;
1037 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := 'Serial number match found';
1038 END LOOP;
1039 END IF;
1040 ELSIF p_inv_item_serial_number IS NOT NULL THEN
1041 IF l_incident_id is not NULL THEN
1042
1043 Open l_dup_sr_ItemSerNumUpd_csr;
1044 LOOP
1045 FETCH l_dup_sr_ItemSerNumUpd_csr into l_dup_sr_ItemSerNumUpd_rec;
1046 EXIT WHEN l_dup_sr_ItemSerNumUpd_csr%NOTFOUND;
1047
1048 l_dup_counter := l_dup_counter + 1;
1049 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_ItemSerNumUpd_rec.incident_id;
1050 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_ItemSerNumUpd_rec.incident_link_id;
1051 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_ItemSerNumUpd_rec.incident_link_number;
1052 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := 'Serial number match found';
1053 END LOOP;
1054 ELSE
1055 Open l_dup_sr_ItemSerNum_csr;
1056 LOOP
1057 FETCH l_dup_sr_ItemSerNum_csr into l_dup_sr_ItemSerNum_rec;
1058 EXIT WHEN l_dup_sr_ItemSerNum_csr%NOTFOUND;
1059
1060 l_dup_counter := l_dup_counter + 1;
1061 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_ItemSerNum_rec.incident_id;
1062 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_ItemSerNum_rec.incident_link_id;
1063 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_ItemSerNum_rec.incident_link_number;
1064 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := 'Serial number match found';
1065 END LOOP;
1066 END IF;
1067 END IF;
1068
1069 IF l_dup_counter > 0 THEN
1070 p_cs_sr_dup_flag := fnd_api.g_true;
1071 ELSE
1072 p_cs_sr_dup_flag := fnd_api.g_false;
1073 END IF;
1074 p_cs_sr_dup_link_rec := l_cs_sr_dup_link_rec;
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 return;
1080 END Check_SR_SerialNum_Dup;
1081
1082
1083 PROCEDURE Check_SR_Instance_Dup
1084 (
1085 p_customer_product_id IN NUMBER,
1086 p_incident_id IN NUMBER,
1087 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
1088 p_cs_sr_dup_flag IN OUT NOCOPY VARCHAR2,
1089 p_return_status OUT NOCOPY VARCHAR2
1090 )
1091 IS
1092 l_duplicate_date date;
1093 l_incident_id number;
1094 l_customer_product_id number;
1095 l_cs_sr_dup_link_rec sr_dupl_link_tbl;
1096
1097 Cursor l_dup_sr_instance_csr is
1098 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1099 from cs_incidents_b_sec sr, cs_incident_links sr_link
1100 where sr.incident_id = sr_link.subject_id(+) and
1101 sr_link.link_type(+) = 'DUP' and
1102 sr_link.subject_type(+) = 'SR' and
1106 sr.customer_product_id = l_customer_product_id
1103 sr_link.end_date_active(+) is null and
1104 sr.last_update_date > l_duplicate_date and
1105 sr_link.last_update_date(+) > l_duplicate_date and
1107 order by sr.incident_id desc;
1108
1109 l_dup_sr_instance_rec l_dup_sr_instance_csr%rowtype;
1110
1111 Cursor l_dup_sr_instanceUpd_csr is
1112 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1113 from cs_incidents_b_sec sr, cs_incident_links sr_link
1114 where sr.incident_id = sr_link.subject_id(+) and
1115 sr_link.subject_type(+) = 'SR' and
1116 sr_link.link_type(+) = 'DUP' and
1117 sr_link.end_date_active(+) is null and
1118 sr.incident_id <> l_incident_id and
1119 sr.last_update_date > l_duplicate_date and
1120 sr_link.last_update_date(+) > l_duplicate_date and
1121 sr.customer_product_id = l_customer_product_id
1122 order by sr.incident_id desc;
1123
1124 l_dup_sr_instanceupd_rec l_dup_sr_instanceUpd_csr%rowtype;
1125 l_dup_counter number := 0;
1126
1127 BEGIN
1128
1129 p_return_status := FND_API.G_RET_STS_SUCCESS;
1130
1131 CALCULATE_DUPLICATE_TIME_FRAME( p_duplicate_time_frame => l_duplicate_date);
1132
1133 l_incident_id := p_incident_id;
1134 l_customer_product_id := p_customer_product_id;
1135
1136 IF l_incident_id is not NULL THEN
1137 Open l_dup_sr_instanceUpd_csr;
1138 LOOP
1139 FETCH l_dup_sr_instanceUpd_csr into l_dup_sr_instanceUpd_rec;
1140 EXIT WHEN l_dup_sr_instanceUpd_csr%NOTFOUND;
1141
1142 l_dup_counter := l_dup_counter + 1;
1143 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_instanceUpd_rec.incident_id;
1144 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_instanceUpd_rec.incident_link_id;
1145 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_instanceUpd_rec.incident_link_number;
1146 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_INSTANCE_MCH');
1147 END LOOP;
1148 ELSE
1149 Open l_dup_sr_instance_csr;
1150 LOOP
1151 FETCH l_dup_sr_instance_csr into l_dup_sr_instance_rec;
1152 EXIT WHEN l_dup_sr_instance_csr%NOTFOUND;
1153
1154 l_dup_counter := l_dup_counter + 1;
1155 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_instance_rec.incident_id;
1156 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_instance_rec.incident_link_id;
1157 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_instance_rec.incident_link_number;
1158 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_INSTANCE_MCH');
1159 END LOOP;
1160 END IF;
1161
1162 if l_dup_counter > 0 then
1163 p_cs_sr_dup_flag := fnd_api.g_true;
1164 else
1165 p_cs_sr_dup_flag := fnd_api.g_false;
1166 end if;
1167 p_cs_sr_dup_link_rec := l_cs_sr_dup_link_rec;
1168
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1172 return;
1173 END Check_SR_Instance_Dup;
1174
1175
1176 PROCEDURE Check_SR_CustProd_Dup
1177 ( p_customer_id IN Number,
1178 p_inventory_item_id IN Number,
1179 p_incident_id IN Number,
1180 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
1181 p_cs_sr_dup_flag IN OUT NOCOPY Varchar2,
1182 p_return_status OUT NOCOPY VARCHAR2
1183 )
1184 IS
1185 l_duplicate_date date;
1186 l_incident_id number;
1187 l_customer_id number;
1188 l_inventory_item_id number;
1189 l_cs_sr_dup_link_rec sr_dupl_link_tbl;
1190 l_dup_counter number := 0;
1191
1192 Cursor l_dup_sr_custprod_csr is
1193 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1194 from cs_incidents_b_sec sr, cs_incident_links sr_link
1195 where sr.incident_id = sr_link.subject_id(+) and
1196 sr_link.subject_type(+) = 'SR' and
1197 sr_link.link_type(+) = 'DUP' and
1198 sr_link.end_date_active(+) is null and
1199 sr.last_update_date > l_duplicate_date and
1200 sr_link.last_update_date(+) > l_duplicate_date and
1201 sr.customer_id = l_customer_id and
1202 sr.inventory_item_id = l_inventory_item_id
1203 order by sr.incident_id desc;
1204
1205 l_dup_sr_custprod_rec l_dup_sr_custprod_csr%rowtype;
1206
1207 Cursor l_dup_sr_custprodUpd_csr is
1208 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1209 from cs_incidents_b_sec sr, cs_incident_links sr_link
1210 where sr.incident_id = sr_link.subject_id(+) and
1211 sr_link.subject_type(+) = 'SR' and
1212 sr_link.link_type(+) = 'DUP' and
1213 sr_link.end_date_active(+) is null and
1214 sr.incident_id <> l_incident_id and
1215 sr.last_update_date > l_duplicate_date and
1216 sr_link.last_update_date(+) > l_duplicate_date and
1217 sr.customer_id = l_customer_id and
1218 sr.inventory_item_id = l_inventory_item_id
1219 order by sr.incident_id desc;
1220
1221 l_dup_sr_custprodupd_rec l_dup_sr_custprodUpd_csr%rowtype;
1222
1226
1223 BEGIN
1224 p_return_status := FND_API.G_RET_STS_SUCCESS;
1225 CALCULATE_DUPLICATE_TIME_FRAME( p_duplicate_time_frame => l_duplicate_date);
1227 l_incident_id := p_incident_id;
1228 l_customer_id := p_customer_id;
1229 l_inventory_item_id := p_inventory_item_id;
1230
1231 IF l_incident_id is not NULL THEN
1232 Open l_dup_sr_custprodUpd_csr;
1233 LOOP
1234 FETCH l_dup_sr_custprodUpd_csr into l_dup_sr_custprodUpd_rec;
1235 EXIT WHEN l_dup_sr_custprodUpd_csr%NOTFOUND;
1236
1237 l_dup_counter := l_dup_counter + 1;
1238 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_custprodUpd_rec.incident_id;
1239 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_custprodUpd_rec.incident_link_id;
1240 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_custprodUpd_rec.incident_link_number;
1241 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_INSTANCE_OR_CUST_PROD_MCH');
1242 END LOOP;
1243 ELSE
1244 Open l_dup_sr_custprod_csr;
1245 LOOP
1246 FETCH l_dup_sr_custprod_csr into l_dup_sr_custprod_rec;
1247 EXIT WHEN l_dup_sr_custprod_csr%NOTFOUND;
1248
1249 l_dup_counter := l_dup_counter + 1;
1250 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_custprod_rec.incident_id;
1251 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_custprod_rec.incident_link_id;
1252 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_custprod_rec.incident_link_number;
1253 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_INSTANCE_OR_CUST_PROD_MCH');
1254 END LOOP;
1255 END IF;
1256
1257 if l_dup_counter > 0 then
1258 p_cs_sr_dup_flag := fnd_api.g_true;
1259 else
1260 p_cs_sr_dup_flag := fnd_api.g_false;
1261 end if;
1262 p_cs_sr_dup_link_rec := l_cs_sr_dup_link_rec;
1263
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1267 return;
1268 END Check_SR_CustProd_Dup;
1269
1270
1271 PROCEDURE Check_SR_CustProdSerial_Dup
1272 ( p_customer_id IN Number,
1273 p_inventory_item_id IN Number,
1274 p_instance_serial_number IN VARCHAR2,
1275 p_current_serial_number IN VARCHAR2,
1276 p_inv_item_serial_number IN VARCHAR2,
1277 p_incident_id IN Number,
1278 p_cs_sr_dup_link_rec IN OUT NOCOPY SR_Dupl_Link_Tbl,
1279 p_cs_sr_dup_flag IN OUT NOCOPY Varchar2,
1280 p_return_status OUT NOCOPY VARCHAR2
1281 )
1282 IS
1283 l_duplicate_date date;
1284 l_incident_id number;
1285 l_customer_id number;
1286 l_inventory_item_id number;
1287 l_current_serial_number CS_INCIDENTS_ALL_B.current_serial_number%type;
1288 l_instance_serial_number CS_INCIDENTS_ALL_B.current_serial_number%type;
1289 l_inv_item_serial_number CS_INCIDENTS_ALL_B.current_serial_number%type;
1290 l_cs_sr_dup_link_rec sr_dupl_link_tbl;
1291 l_dup_counter number := 0;
1292
1293 Cursor l_dup_sr_custprodsr_csr is
1294 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1295 from cs_incidents_b_sec sr, cs_incident_links sr_link
1296 where sr.incident_id = sr_link.subject_id(+) and
1297 sr_link.subject_type(+) = 'SR' and
1298 sr_link.link_type(+) = 'DUP' and
1299 sr_link.end_date_active(+) is null and
1300 sr.last_update_date > l_duplicate_date and
1301 sr_link.last_update_date(+) > l_duplicate_date and
1302 sr.customer_id = l_customer_id and
1303 sr.inventory_item_id = l_inventory_item_id and
1304 upper(sr.current_serial_number) = upper(l_current_serial_number)
1305 order by sr.incident_id desc;
1306
1307 l_dup_sr_custprodsr_rec l_dup_sr_custprodsr_csr%rowtype;
1308
1309 Cursor l_dup_sr_custprodsrUpd_csr is
1310 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1311 from cs_incidents_b_sec sr, cs_incident_links sr_link
1312 where sr.incident_id = sr_link.subject_id(+) and
1313 sr_link.subject_type(+) = 'SR' and
1314 sr_link.link_type(+) = 'DUP' and
1315 sr_link.end_date_active(+) is null and
1316 sr.incident_id <> l_incident_id and
1317 sr.last_update_date > l_duplicate_date and
1318 sr_link.last_update_date(+) > l_duplicate_date and
1319 sr.customer_id = l_customer_id and
1320 sr.inventory_item_id = l_inventory_item_id and
1321 upper(sr.current_serial_number) = upper(l_current_serial_number)
1322 order by sr.incident_id desc;
1323
1324 l_dup_sr_custprodsrupd_rec l_dup_sr_custprodsrUpd_csr%rowtype;
1325
1326 Cursor l_dup_sr_CustProdInsSer_csr is
1327 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1328 from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst
1329 where sr.incident_id = sr_link.subject_id(+) and
1330 sr_link.subject_type(+) = 'SR' and
1331 sr_link.link_type(+) = 'DUP' and
1332 sr_link.end_date_active(+) is null and
1333 sr.last_update_date > l_duplicate_date and
1334 sr_link.last_update_date(+) > l_duplicate_date and
1335 sr.customer_id = l_customer_id and
1339 order by sr.incident_id desc;
1336 sr.inventory_item_id = l_inventory_item_id and
1337 inst.instance_id = sr.customer_product_id and
1338 inst.serial_number = l_instance_serial_number
1340
1341 l_dup_sr_CustProdInsSer_rec l_dup_sr_CustProdInsSer_csr%rowtype;
1342
1343 Cursor l_dup_sr_CustProdInsSerUpd_csr is
1344 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1345 from cs_incidents_b_sec sr, cs_incident_links sr_link, csi_item_instances inst
1346 where sr.incident_id = sr_link.subject_id(+) and
1347 sr_link.subject_type(+) = 'SR' and
1348 sr_link.link_type(+) = 'DUP' and
1349 sr_link.end_date_active(+) is null and
1350 sr.incident_id <> l_incident_id and
1351 sr.last_update_date > l_duplicate_date and
1352 sr_link.last_update_date(+) > l_duplicate_date and
1353 sr.customer_id = l_customer_id and
1354 sr.inventory_item_id = l_inventory_item_id and
1355 sr.customer_product_id = inst.instance_id and
1356 inst.serial_number = l_instance_serial_number
1357 order by sr.incident_id desc;
1358
1359 l_dup_sr_CustProdInsSerUpd_rec l_dup_sr_CustProdInsSerUpd_csr%rowtype;
1360
1361 Cursor l_dup_sr_CustProdItmSer_csr is
1362 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1363 from cs_incidents_b_sec sr, cs_incident_links sr_link
1364 where sr.incident_id = sr_link.subject_id(+) and
1365 sr_link.subject_type(+) = 'SR' and
1366 sr_link.link_type(+) = 'DUP' and
1367 sr_link.end_date_active(+) is null and
1368 sr.last_update_date > l_duplicate_date and
1369 sr_link.last_update_date(+) > l_duplicate_date and
1370 sr.customer_id = l_customer_id and
1371 sr.inventory_item_id = l_inventory_item_id and
1372 sr.item_serial_number = l_inv_item_serial_number
1373 order by sr.incident_id desc;
1374 l_dup_sr_CustProdItmSer_rec l_dup_sr_CustProdItmSer_csr%rowtype;
1375
1376 Cursor l_dup_sr_CustProdItmSerUpd_csr is
1377 select sr.incident_id, sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1378 from cs_incidents_b_sec sr, cs_incident_links sr_link
1379 where sr.incident_id = sr_link.subject_id(+) and
1380 sr_link.subject_type(+) = 'SR' and
1381 sr_link.link_type(+) = 'DUP' and
1382 sr_link.end_date_active(+) is null and
1383 sr.incident_id <> l_incident_id and
1384 sr.last_update_date > l_duplicate_date and
1385 sr_link.last_update_date(+) > l_duplicate_date and
1386 sr.customer_id = l_customer_id and
1387 sr.inventory_item_id = l_inventory_item_id and
1388 sr.item_serial_number = l_inv_item_serial_number
1389 order by sr.incident_id desc;
1390 l_dup_sr_CustProdItmSerUpd_rec l_dup_sr_CustProdItmSerUpd_csr%rowtype;
1391
1392 BEGIN
1393 p_return_status := FND_API.G_RET_STS_SUCCESS;
1394 CALCULATE_DUPLICATE_TIME_FRAME( p_duplicate_time_frame => l_duplicate_date);
1395
1396 l_incident_id := p_incident_id;
1397 l_customer_id := p_customer_id;
1398 l_inventory_item_id := p_inventory_item_id;
1399 l_current_serial_number := p_current_serial_number;
1400 l_inv_item_serial_number := p_inv_item_serial_number;
1401 l_instance_serial_number := p_instance_serial_number;
1402
1403 IF l_current_serial_number IS NOT NULL THEN
1404
1405 IF l_incident_id is not NULL THEN
1406 Open l_dup_sr_custprodsrUpd_csr;
1407 LOOP
1408 FETCH l_dup_sr_custprodsrUpd_csr into l_dup_sr_custprodsrUpd_rec;
1409 EXIT WHEN l_dup_sr_custprodsrUpd_csr%NOTFOUND;
1410
1411 l_dup_counter := l_dup_counter + 1;
1412 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_custprodsrUpd_rec.incident_id;
1413 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_custprodsrUpd_rec.incident_link_id;
1414 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_custprodsrUpd_rec.incident_link_number;
1415 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_CUST_PROD_SERIAL_MCH');
1416 END LOOP;
1417 ELSE
1418 Open l_dup_sr_custprodsr_csr;
1419 LOOP
1420 FETCH l_dup_sr_custprodsr_csr into l_dup_sr_custprodsr_rec;
1421 EXIT WHEN l_dup_sr_custprodsr_csr%NOTFOUND;
1422
1423 l_dup_counter := l_dup_counter + 1;
1424 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_custprodsr_rec.incident_id;
1425 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_custprodsr_rec.incident_link_id;
1426 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_custprodsr_rec.incident_link_number;
1427 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_CUST_PROD_SERIAL_MCH');
1428 END LOOP;
1429 END IF;
1430 ELSIF l_instance_serial_number IS NOT NULL THEN
1431 IF l_incident_id is not NULL THEN
1432 Open l_dup_sr_CustProdInsSerUpd_csr;
1433 LOOP
1434 FETCH l_dup_sr_CustProdInsSerUpd_csr into l_dup_sr_CustProdInsSerUpd_rec;
1435 EXIT WHEN l_dup_sr_CustProdInsSerUpd_csr%NOTFOUND;
1436
1437 l_dup_counter := l_dup_counter + 1;
1438 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_CustProdInsSerUpd_rec.incident_id;
1442 END LOOP;
1439 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_CustProdInsSerUpd_rec.incident_link_id;
1440 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_CustProdInsSerUpd_rec.incident_link_number;
1441 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_CUST_PROD_SERIAL_MCH');
1443 ELSE
1444 Open l_dup_sr_CustProdInsSer_csr;
1445 LOOP
1446 FETCH l_dup_sr_CustProdInsSer_csr into l_dup_sr_CustProdInsSer_rec;
1447 EXIT WHEN l_dup_sr_CustProdInsSer_csr%NOTFOUND;
1448
1449 l_dup_counter := l_dup_counter + 1;
1450 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_CustProdInsSer_rec.incident_id;
1451 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_CustProdInsSer_rec.incident_link_id;
1452 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_CustProdInsSer_rec.incident_link_number;
1453 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_CUST_PROD_SERIAL_MCH');
1454 END LOOP;
1455 END IF;
1456
1457 ELSIF l_inv_item_serial_number IS NOT NULL THEN
1458 IF l_incident_id is not NULL THEN
1459 Open l_dup_sr_CustProdItmSerUpd_csr;
1460 LOOP
1461 FETCH l_dup_sr_CustProdItmSerUpd_csr into l_dup_sr_CustProdItmSerUpd_rec;
1462 EXIT WHEN l_dup_sr_CustProdItmSerUpd_csr%NOTFOUND;
1463
1464 l_dup_counter := l_dup_counter + 1;
1465 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_CustProdItmSerUpd_rec.incident_id;
1466 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_CustProdItmSerUpd_rec.incident_link_id;
1467 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_CustProdItmSerUpd_rec.incident_link_number;
1468 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_CUST_PROD_SERIAL_MCH');
1469 END LOOP;
1470 ELSE
1471 Open l_dup_sr_CustProdItmSer_csr;
1472 LOOP
1473 FETCH l_dup_sr_CustProdItmSer_csr into l_dup_sr_CustProdItmSer_rec;
1474 EXIT WHEN l_dup_sr_CustProdItmSer_csr%NOTFOUND;
1475
1476 l_dup_counter := l_dup_counter + 1;
1477 l_cs_sr_dup_link_rec(l_dup_counter).incident_id := l_dup_sr_CustProdItmSer_rec.incident_id;
1478 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_id := l_dup_sr_CustProdItmSer_rec.incident_link_id;
1479 l_cs_sr_dup_link_rec(l_dup_counter).incident_link_number := l_dup_sr_CustProdItmSer_rec.incident_link_number;
1480 l_cs_sr_dup_link_rec(l_dup_counter).reason_desc := Get_Dup_Message('CS_CUST_PROD_SERIAL_MCH');
1481 END LOOP;
1482 END IF;
1483
1484 END IF;
1485
1486 if l_dup_counter > 0 then
1487 p_cs_sr_dup_flag := fnd_api.g_true;
1488 else
1489 p_cs_sr_dup_flag := fnd_api.g_false;
1490 end if;
1491 p_cs_sr_dup_link_rec := l_cs_sr_dup_link_rec;
1492
1493 EXCEPTION
1494 WHEN OTHERS THEN
1495 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1496 return;
1497 END Check_SR_CustProdSerial_Dup;
1498
1499
1500 PROCEDURE Check_Dup_SR_Link
1501 (
1502 p_dup_found_tbl IN Sr_Dupl_Link_Tbl,
1503 p_dup_tbl IN OUT NOCOPY Sr_Dupl_Tbl,
1504 p_return_status OUT NOCOPY VARCHAR2
1505 )
1506 AS
1507 l_loop_dup_rec_tbl Sr_Dupl_Link_Tbl;
1508 l_link_in_rec VARCHAR2(1) := 'N';
1509 l_already_added VARCHAR2(1) := 'N';
1510 l_dup_tbl_cnt NUMBER := 0;
1511 l_dup_tbl sr_dupl_tbl;
1512 l_rec_count number := 0;
1513 l_dup_rec_id number;
1514 l_message varchar2(2000);
1515
1516 -- Changed to fix bug 3332447
1517 Cursor l_all_dup_for_original_csr (p_original_id number) is
1518 select sr_link.object_id incident_link_id, sr_link.object_number incident_link_number
1519 from cs_incident_links sr_link
1520 where sr_link.subject_id = p_original_id and
1521 sr_link.subject_type = 'SR' and
1522 sr_link.link_type_id = 4 and
1523 sr_link.end_date_active is null
1524 order by sr_link.object_id desc;
1525
1526 l_all_dup_for_original_rec l_all_dup_for_original_csr%rowtype;
1527 -- end of bug fix 3332447
1528
1529 BEGIN
1530
1531 p_return_status := FND_API.G_RET_STS_SUCCESS;
1532 l_loop_dup_rec_tbl := p_dup_found_tbl;
1533
1534 FOR i IN l_loop_dup_rec_tbl.first..l_loop_dup_rec_tbl.last loop
1535 IF l_loop_dup_rec_tbl(i).incident_link_id is not null THEN
1536 l_link_in_rec := 'N';
1537 if p_dup_tbl.count > 0 then
1538 FOR x in p_dup_tbl.first..p_dup_tbl.last loop
1539 IF p_dup_tbl(x).incident_id = l_loop_dup_rec_tbl(i).incident_link_id THEN
1540 l_link_in_rec := 'Y';
1541 exit;
1542 END IF;
1543 END LOOP;
1544 end if;
1545
1546 IF l_link_in_rec = 'N' THEN
1547 l_dup_tbl_cnt := l_dup_tbl_cnt + 1;
1548 p_dup_tbl(l_dup_tbl_cnt).incident_id := l_loop_dup_rec_tbl(i).incident_link_id;
1549 p_dup_tbl(l_dup_tbl_cnt).reason_desc := l_loop_dup_rec_tbl(i).reason_desc;
1550 END IF;
1551
1552 -----
1553 -- To Fix bug 3332447
1554 -----
1555 -- l_loop_dup_rec_tbl(i).reason_desc := Get_Dup_Message('CS_ORIGINAL_OF_DUP_SR') || ' ' || to_char(l_loop_dup_rec_tbl(i).incident_link_number);
1556
1557 IF l_link_in_rec = 'N' THEN
1558 l_dup_rec_id := l_loop_dup_rec_tbl(i).incident_link_id;
1559 ELSE
1563 l_message := NULL;
1560 l_dup_rec_id := l_loop_dup_rec_tbl(i).incident_id;
1561 END IF;
1562
1564 l_rec_count := 0;
1565
1566 OPEN l_all_dup_for_original_csr (l_dup_rec_id);
1567 LOOP
1568 FETCH l_all_dup_for_original_csr into l_all_dup_for_original_rec;
1569 EXIT WHEN l_all_dup_for_original_csr%NOTFOUND;
1570 l_rec_count := l_rec_count + 1;
1571 if l_rec_count = 1 then
1572 l_message := Get_Dup_Message('CS_ORIGINAL_OF_DUP_SR') || ' ' || l_all_dup_for_original_rec.incident_link_number ;
1573 else
1574 l_message := l_message || ' , ' || l_all_dup_for_original_rec.incident_link_number;
1575 end if;
1576 END LOOP;
1577 close l_all_dup_for_original_csr;
1578
1579 IF l_link_in_rec = 'N' and l_message is not null then
1580 p_dup_tbl(l_dup_tbl_cnt).reason_desc := l_message;
1581 ELSE
1582 l_loop_dup_rec_tbl(i).reason_desc := l_message;
1583 END IF;
1584
1585 END IF;
1586
1587 ------
1588 -- End of bug fix 3332447
1589 ------
1590
1591 if p_dup_tbl.count > 0 THEN
1592 -- l_already_added := Check_if_already_in_list(p_dup_tbl => p_dup_tbl,
1593 -- p_sr_link_id => l_loop_dup_rec_tbl(i).incident_link_id);
1594 l_already_added := Check_if_already_in_list(p_dup_tbl => p_dup_tbl,
1595 p_sr_link_id => l_loop_dup_rec_tbl(i).incident_id);
1596 else
1597 l_already_added := 'N';
1598 End if;
1599 IF l_already_added = 'N' THEN
1600 l_dup_tbl_cnt := l_dup_tbl_cnt + 1;
1601 p_dup_tbl(l_dup_tbl_cnt).incident_id := l_loop_dup_rec_tbl(i).incident_id;
1602 p_dup_tbl(l_dup_tbl_cnt).reason_desc := l_loop_dup_rec_tbl(i).reason_desc;
1603 end if;
1604 END LOOP;
1605
1606 EXCEPTION
1607 WHEN OTHERS THEN
1608 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1609 return;
1610 END Check_Dup_SR_Link;
1611
1612
1613 PROCEDURE Construct_Unique_list_dup_sr
1614 (
1615 p_cs_ea_dup_rec IN Sr_Dupl_Tbl,
1616 p_ea_attr_dup_flag IN VARCHAR2,
1617 p_cs_sr_dup_rec IN Sr_Dupl_Tbl,
1618 p_cs_sr_dup_flag IN VARCHAR2,
1619 p_dup_from IN NUMBER,
1620 p_ea_ea_dup IN VARCHAR2,
1621 p_ea_ia_dup IN VARCHAR2,
1622 p_sr_dup_rec IN OUT NOCOPY Sr_Dupl_Tbl,
1623 p_duplicate_flag IN OUT NOCOPY VARCHAR2,
1624 p_return_status OUT NOCOPY VARCHAR2
1625 )
1626 AS
1627 l_counter NUMBER := 0;
1628 l_included_inList VARCHAR2(1) := 'N';
1629 l_sr_dup_rec Sr_Dupl_Tbl;
1630 l_found_incident_id number;
1631
1632 BEGIN
1633 p_return_status := FND_API.G_RET_STS_SUCCESS;
1634 IF p_ea_attr_dup_flag = fnd_api.g_true THEN
1635
1636 FOR i in p_cs_ea_dup_rec.first..p_cs_ea_dup_rec.last LOOP
1637 l_counter := l_counter + 1;
1638 l_sr_dup_rec(l_counter).incident_id := p_cs_ea_dup_rec(i).incident_id;
1639 l_sr_dup_rec(l_counter).reason_desc := p_cs_ea_dup_rec(i).reason_desc;
1640 END LOOP;
1641
1642 END IF;
1643
1644 IF p_cs_sr_dup_flag = fnd_api.g_true THEN
1645 FOR i in p_cs_sr_dup_rec.first..p_cs_sr_dup_rec.last LOOP
1646
1647 l_included_inList := 'N';
1648 FOR x in p_cs_ea_dup_rec.first..p_cs_ea_dup_Rec.last LOOP
1649 IF p_cs_sr_dup_rec(i).incident_id = p_cs_ea_dup_rec(x).incident_id THEN
1650 l_included_inList := 'Y';
1651 exit;
1652 END IF;
1653 END LOOP;
1654 IF l_included_inList = 'N' THEN
1655 l_counter := l_counter + 1;
1656 l_sr_dup_rec(l_counter).incident_id := p_cs_sr_dup_rec(i).incident_id;
1657 l_sr_dup_rec(l_counter).reason_desc := p_cs_sr_dup_rec(i).reason_desc;
1658 ELSE
1659 for y in l_sr_dup_rec.first..l_sr_dup_rec.last loop
1660 IF l_sr_dup_rec(y).incident_id = p_cs_sr_dup_rec(i).incident_id THEN
1661 /*
1662 CASE
1663 WHEN p_dup_from = 1 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1664 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1665 WHEN p_dup_from = 2 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1666 -- Need to get new message info
1667 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1668 WHEN p_dup_from = 3 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1669 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_EA_MCH');
1670 WHEN p_dup_from = 4 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1671 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_SER_EA_MCH');
1672 WHEN p_dup_from = 1 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1673 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_INC_ADDR_MCH');
1674 WHEN p_dup_from = 2 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1675 -- Need to get new message info
1676 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1677 WHEN p_dup_from = 3 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1678 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_EA_ADDR_MCH');
1679 WHEN p_dup_from = 4 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1683 WHEN p_dup_from = 2 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1680 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_SER_EA_ADD_MCH');
1681 WHEN p_dup_from = 1 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1682 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_EA_ADDR_MCH');
1684 -- Need to get new message info
1685 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1686 WHEN p_dup_from = 3 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1687 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_EA_EA_ADDR_MCH');
1688 WHEN p_dup_from = 4 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1689 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_SER_EA_EA_ADD_MCH');
1690 END CASE;
1691 */
1692 IF p_dup_from = 1 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1693 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1694 ELSIF p_dup_from = 2 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1695 -- Need to get new message info
1696 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1697 ELSIF p_dup_from = 3 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1698 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_EA_MCH');
1699 ELSIF p_dup_from = 4 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'N' THEN
1700 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_SER_EA_MCH');
1701 ELSIF p_dup_from = 1 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1702 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_INC_ADDR_MCH');
1703 ELSIF p_dup_from = 2 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1704 -- Need to get new message info
1705 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1706 ELSIF p_dup_from = 3 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1707 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_EA_ADDR_MCH');
1708 ELSIF p_dup_from = 4 and p_ea_ea_dup = 'N' and p_ea_ia_dup = 'Y' THEN
1709 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_SER_EA_ADD_MCH');
1710 ELSIF p_dup_from = 1 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1711 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_EA_ADDR_MCH');
1712 ELSIF p_dup_from = 2 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1713 -- Need to get new message info
1714 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_INSTANCE_EA_MCH');
1715 ELSIF p_dup_from = 3 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1716 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_EA_EA_ADDR_MCH');
1717 ELSIF p_dup_from = 4 and p_ea_ea_dup = 'Y' and p_ea_ia_dup = 'Y' THEN
1718 l_sr_dup_rec(y).reason_desc := Get_Dup_Message('CS_CUST_PROD_SER_EA_EA_ADD_MCH');
1719 END IF;
1720 exit;
1721 END If;
1722 END LOOP;
1723 END IF;
1724 END LOOP;
1725 END IF;
1726
1727 IF l_counter > 0 THEN
1728 p_duplicate_flag := fnd_api.g_true;
1729 ELSE
1730 p_duplicate_flag := fnd_api.g_false;
1731 END IF;
1732 p_sr_dup_rec := l_sr_dup_rec;
1733
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1737 return;
1738 END Construct_Unique_List_Dup_Sr;
1739
1740
1741 FUNCTION Get_Dup_Message
1742 (
1743 p_lookup_code IN VARCHAR2
1744 ) return varchar2
1745 AS
1746 Cursor l_sr_dup_mesg_csr is
1747 SELECT lookup_code, description from cs_lookups
1748 WHERE lookup_type = 'CS_SR_DUPLICATE_REASON_CODE'
1749 and lookup_code = p_lookup_code;
1750 l_sr_dup_mesg_rec l_sr_dup_mesg_csr%ROWTYPE;
1751 BEGIN
1752 OPEN l_sr_dup_mesg_csr;
1753 FETCH l_sr_dup_mesg_csr into l_sr_dup_mesg_rec;
1754 IF (l_sr_dup_mesg_csr%FOUND) THEN
1755 return l_sr_dup_mesg_rec.description;
1756 ELSE
1757 return 'Exception_found';
1758 END If;
1759 END Get_Dup_Message;
1760
1761
1762 FUNCTION Check_if_already_in_list
1763 (p_dup_tbl IN Sr_Dupl_Tbl,
1764 p_sr_link_id IN NUMBER
1765 ) return varchar2
1766 AS
1767 l_dup_tbl Sr_Dupl_Tbl;
1768 BEGIN
1769 l_dup_tbl := p_dup_tbl;
1770
1771 FOR i IN l_dup_tbl.first..l_dup_tbl.last loop
1772 IF l_dup_tbl(i).incident_id = p_sr_link_id THEN
1773 return 'Y';
1774 END IF;
1775 END LOOP;
1776 return 'N';
1777 END Check_if_already_in_list;
1778
1779
1780 PROCEDURE CALCULATE_DUPLICATE_TIME_FRAME
1781 (p_incident_type_id NUMBER,
1782 p_duplicate_time_frame OUT NOCOPY DATE)
1783 IS
1784
1785 l_duplicate_uom VARCHAR2(30);
1786 l_incident_type_id NUMBER;
1787 l_multiple_by NUMBER;
1788
1789 CURSOR c_DuplicateTimeInfo_csr IS
1790 SELECT duplicate_offset, duplicate_uom FROM CUG_SR_TYPE_DUP_CHK_INFO
1791 WHERE INCIDENT_TYPE_ID = l_incident_type_id;
1792 l_DuplicateTimeInfo_rec c_DuplicateTimeInfo_csr%ROWTYPE;
1793
1797 and inventory_item_id = 0;
1794 CURSOR c_UOM_Conversion_Rate_csr IS
1795 SELECT conversion_rate FROM MTL_UOM_CONVERSIONS
1796 WHERE UNIT_OF_MEASURE = l_duplicate_uom
1798 l_UOM_Conversion_Rate_rec c_UOM_Conversion_Rate_csr%ROWTYPE;
1799
1800 BEGIN
1801
1802 l_incident_type_id := p_incident_type_id;
1803
1804 OPEN c_DuplicateTimeInfo_csr;
1805 FETCH c_DuplicateTimeInfo_csr INTO l_DuplicateTimeInfo_rec;
1806 IF (c_DuplicateTimeInfo_csr%NOTFOUND) THEN
1807 RAISE fnd_api.g_exc_unexpected_error;
1808 END IF;
1809
1810 l_duplicate_uom := l_DuplicateTimeInfo_rec.duplicate_uom;
1811
1812 OPEN c_UOM_Conversion_Rate_csr;
1813 FETCH c_UOM_Conversion_Rate_csr into l_UOM_Conversion_Rate_rec;
1814 IF (c_UOM_Conversion_Rate_csr%NOTFOUND) THEN
1815 RAISE fnd_api.g_exc_unexpected_error;
1816 END IF;
1817
1818 IF ( l_DuplicateTimeInfo_rec.duplicate_uom = 'Day') THEN
1819 l_multiple_by := l_DuplicateTimeInfo_rec.duplicate_offset;
1820 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Hour') THEN
1821 l_multiple_by := l_DuplicateTimeInfo_rec.duplicate_offset/24;
1822 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Month') THEN
1823 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset * 720)/24;
1824 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Week') THEN
1825 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*168)/24;
1826 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Year') THEN
1827 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*8760)/24;
1828 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Minute') THEN
1829 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*0.016667)/24;
1830 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Jal') THEN
1831 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*0.016667)/24;
1832 ELSE
1833 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*l_UOM_Conversion_Rate_rec.conversion_rate)/24;
1834 END IF;
1835 p_duplicate_time_frame := sysdate - l_multiple_by;
1836
1837 END CALCULATE_DUPLICATE_TIME_FRAME;
1838
1839 ------------------------------------------------------------
1840 -- Procedure name : CALCULATE_DUPLICATE_TIME_FRAME
1841 --
1842 -- Parameters
1843 -- IN
1844 -- NONE
1845 -- OUT
1846 -- p_duplicate_time_frame : Duplicate Time Frame
1847 --
1848 --
1849 -- Description : This procedure calculates upto date value based on
1850 -- the profile set for CS
1851 --
1852 -- Modification History :
1853 -- Date Name Desc
1854 -- ---------- --------- ----------------------------------
1855 -- 09/01/2005 ANEEMUCH Fixed FP bug 4352458, removed time calculation
1856 -- from hard coded Time.
1857 -- 09/08/2007 VPREMACH Bug 6356257. Added upper clause when getting
1858 -- UOM value from profile.
1859 -- ------------------------------------------------------------
1860
1861 PROCEDURE CALCULATE_DUPLICATE_TIME_FRAME
1862 (p_duplicate_time_frame OUT NOCOPY DATE)
1863 IS
1864 l_duplicate_uom VARCHAR2(30);
1865 l_duplicate_offset NUMBER;
1866 l_multiple_by NUMBER;
1867
1868 CURSOR c_UOM_Conversion_Rate_csr IS
1869 SELECT conversion_rate FROM MTL_UOM_CONVERSIONS
1870 WHERE uom_code = l_duplicate_uom;
1871
1872 l_UOM_Conversion_Rate_rec c_UOM_Conversion_Rate_csr%ROWTYPE;
1873
1874 BEGIN
1875
1876 /* Start : 5686752 */
1877 /*l_duplicate_uom := fnd_profile.value('CS_SR_DUP_TIME_FRAME_UOM');
1878 l_duplicate_offset := fnd_profile.value('CS_SR_DUP_TIME_FRAME');
1879
1880 OPEN c_UOM_Conversion_Rate_csr;
1881 FETCH c_UOM_Conversion_Rate_csr into l_UOM_Conversion_Rate_rec;
1882 IF (c_UOM_Conversion_Rate_csr%NOTFOUND) THEN
1883 RAISE fnd_api.g_exc_unexpected_error;
1884 END IF;
1885
1886 l_multiple_by := (l_duplicate_offset*l_UOM_Conversion_Rate_rec.conversion_rate)/24;
1887
1888 p_duplicate_time_frame := sysdate - l_multiple_by;
1889
1890 END CALCULATE_DUPLICATE_TIME_FRAME;*/
1891
1892 l_duplicate_uom := UPPER(fnd_profile.value('CS_SR_DUP_TIME_FRAME_UOM'));
1893 l_duplicate_offset := fnd_profile.value('CS_SR_DUP_TIME_FRAME');
1894
1895 If l_duplicate_uom = 'HR' Then
1896 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 1/24);
1897 ElsIf l_duplicate_uom = 'DAY' Then
1898 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 1);
1899 ElsIf l_duplicate_uom = 'MTH' Then
1900 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 30);
1901 ElsIf l_duplicate_uom = 'WK' Then
1902 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 7);
1903 ElsIf l_duplicate_uom = 'YR' Then
1904 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 365);
1905 ElsIf l_duplicate_uom = 'MIN' Then
1906 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 1/(24 * 60));
1907 ElsIf l_duplicate_uom = 'QRT' Then
1908 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 30 * 3);
1909 ElsIf l_duplicate_uom = 'SEC' Then
1910 p_duplicate_time_frame := sysdate - (l_duplicate_offset * 1/(24 * 60 * 60));
1911 End If;
1912
1913 /* End : 5686752 */
1914
1915 END CALCULATE_DUPLICATE_TIME_FRAME;
1916
1917
1918 END CS_SR_DUP_CHK_PVT;