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