[Home] [Help]
PACKAGE BODY: APPS.CSD_RO_BULLETINS_PVT
Source
1 PACKAGE BODY CSD_RO_BULLETINS_PVT as
2 /* $Header: csdvrobb.pls 120.5.12010000.4 2008/11/18 20:46:49 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_RO_BULLETINS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_RO_BULLETINS_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvrobb.pls';
13
14 -- Global variable for storing the debug level
15 G_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16
17
18
19 -- *******************************************************
20 -- Start of Comments
21 -- *******************************************************
22 -- API Name: CREATE_RO_BULLETIN
23 -- Type : Private
24 -- Pre-Req :
25 -- Parameters:
26 -- IN
27 -- p_api_version_number IN NUMBER Required
28 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
29 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
30 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
31 -- P_RO_BULLETIN_Rec IN CSD_RO_BULLETIN_REC_TYPE Required
32 --
33 -- OUT:
34 -- x_return_status OUT NOCOPY VARCHAR2
35 -- x_msg_count OUT NOCOPY NUMBER
36 -- x_msg_data OUT NOCOPY VARCHAR2
37 -- x_RO_BULLETIN_ID OUT NOCOPY NUMBER
38 -- History: Jan-16-2008 rfieldma created
39 -- -------------------
40 -- End of Comments
41 -- -------------------
42 PROCEDURE CREATE_RO_BULLETIN(
43 p_api_version_number IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
45 p_commit IN VARCHAR2 := FND_API.G_FALSE,
46 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
47 p_ro_bulletin_rec IN RO_BULLETIN_REC_TYPE,
48 x_ro_bulletin_id OUT NOCOPY NUMBER,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2
52 ) IS
53 ---- local constants ----
54 c_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_RO_BULLETINS';
55 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
56 BEGIN
57 --* Standard Start of API savepoint
58 SAVEPOINT CREATE_RO_BULLETIN_PVT;
59
60 --* Standard call to check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
62 p_api_version_number,
63 c_API_NAME,
64 G_PKG_NAME)
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 --* Initialize message list if p_init_msg_list is set to TRUE.
70 IF FND_API.to_Boolean( p_init_msg_list )
71 THEN
72 FND_MSG_PUB.initialize;
73 END IF;
74
75 --* Initialize API return status to SUCCESS
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78 --* logic starts here *--
79
80
81 --* Invoke table handler(CSD_RO_BULLETINS_PKG.Insert_Row)
82 CSD_RO_BULLETINS_PKG.INSERT_ROW(
83 px_ro_bulletin_id => x_ro_bulletin_id
84 ,p_repair_line_id => p_ro_bulletin_rec.repair_line_id
85 ,p_bulletin_id => p_ro_bulletin_rec.bulletin_id
86 ,p_last_viewed_date => p_ro_bulletin_rec.last_viewed_date
87 ,p_last_viewed_by => p_ro_bulletin_rec.last_viewed_by
88 ,p_source_type => p_ro_bulletin_rec.source_type
89 ,p_source_id => p_ro_bulletin_rec.source_id
90 ,p_object_version_number => p_ro_bulletin_rec.object_version_number
91 ,p_created_by => FND_GLOBAL.USER_ID
92 ,p_creation_date => sysdate
93 ,p_last_updated_by => FND_GLOBAL.USER_ID
94 ,p_last_update_date => sysdate
95 ,p_last_update_login => FND_GLOBAL.LOGIN_ID);
96
97 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
98 RAISE FND_API.G_EXC_ERROR;
99 END IF;
100
101 --* logic ends here *--
102
103 --* Standard check for p_commit
104 IF FND_API.to_Boolean( p_commit ) THEN
105 COMMIT WORK;
106 END IF;
107
108 --* Standard call to get message count and if count is 1, get message info.
109 FND_MSG_PUB.Count_And_Get(
110 p_count => x_msg_count,
111 p_data => x_msg_data
112 );
113
114 EXCEPTION
115 WHEN FND_API.G_EXC_ERROR THEN
116 x_return_status := FND_API.G_RET_STS_ERROR;
117 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
118 P_API_NAME => c_API_NAME
119 ,P_PKG_NAME => G_PKG_NAME
120 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
121 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
122 ,X_MSG_COUNT => X_MSG_COUNT
123 ,X_MSG_DATA => X_MSG_DATA
124 ,X_RETURN_STATUS => X_RETURN_STATUS);
125
126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
129 P_API_NAME => c_API_NAME
130 ,P_PKG_NAME => G_PKG_NAME
131 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
132 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
133 ,X_MSG_COUNT => X_MSG_COUNT
134 ,X_MSG_DATA => X_MSG_DATA
135 ,X_RETURN_STATUS => X_RETURN_STATUS);
136
137 WHEN OTHERS THEN
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
140 P_API_NAME => c_API_NAME
141 ,P_PKG_NAME => G_PKG_NAME
142 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
143 ,P_SQLCODE => SQLCODE
144 ,P_SQLERRM => SQLERRM
145 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
146 ,X_MSG_COUNT => X_MSG_COUNT
147 ,X_MSG_DATA => X_MSG_DATA
148 ,X_RETURN_STATUS => X_RETURN_STATUS);
149 END CREATE_RO_BULLETIN;
150
151
152 -- *******************************************************
153 -- Start of Comments
154 -- *******************************************************
155 -- API Name: UPDATE_RO_BULLETIN
156 -- Type : Private
157 -- Pre-Req :
158 -- Parameters:
159 -- IN
160 -- p_api_version_number IN NUMBER Required
161 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
162 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
163 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
164 -- P_RO_BULLETIN_Rec IN RO_BULLETIN_REC_TYPE Required
165 --
166 -- OUT:
167 -- x_return_status OUT NOCOPY VARCHAR2
168 -- x_msg_count OUT NOCOPY NUMBER
169 -- x_msg_data OUT NOCOPY VARCHAR2
170 -- History: Jan-16-2008 rfieldma created
171 -- -------------------
172 -- End of Comments
173 -- -------------------
174 PROCEDURE UPDATE_RO_BULLETIN(
175 p_api_version_number IN NUMBER,
176 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
177 p_commit IN VARCHAR2 := FND_API.G_FALSE,
178 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
179 p_ro_bulletin_rec IN RO_BULLETIN_Rec_Type,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2
183 ) IS
184
185 ---- cursors ----
186 CURSOR cur_get_ro_bulletin(p_ro_bulletin_id Number) IS
187 SELECT ro_bulletin_id,
188 repair_line_id,
189 bulletin_id,
190 last_viewed_date,
191 last_viewed_by,
192 source_type,
193 source_id,
194 object_version_number,
195 created_by,
196 creation_date,
197 last_updated_by,
198 last_update_date,
199 last_update_login
200 FROM csd_ro_bulletins
201 WHERE ro_bulletin_id = p_ro_bulletin_id
202 FOR UPDATE NOWAIT;
203
204 ---- local constants ----
205 c_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_RO_BULLETIN';
206 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
207
208 ---- Local Variables ----
209 l_ref_ro_bulletin_rec RO_BULLETIN_REC_TYPE;
210 l_rowid ROWID;
211 BEGIN
212 --* Standard Start of API savepoint
213 SAVEPOINT UPDATE_RO_BULLETIN_PVT;
214
215 --* Standard call to check for call compatibility.
216 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
217 p_api_version_number,
218 c_API_NAME,
219 G_PKG_NAME)
220 THEN
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 END IF;
223
224 --* Initialize message list if p_init_msg_list is set to TRUE.
225 IF FND_API.to_Boolean( p_init_msg_list )
226 THEN
227 FND_MSG_PUB.initialize;
228 END IF;
229
230 --* Initialize API return status to SUCCESS
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 --* logic starts here *--
234
235 OPEN cur_get_ro_bulletin( p_ro_bulletin_rec.RO_BULLETIN_ID);
236 FETCH cur_get_ro_bulletin INTO
237 l_ref_ro_bulletin_rec.ro_bulletin_id,
238 l_ref_ro_bulletin_rec.repair_line_id,
239 l_ref_ro_bulletin_rec.bulletin_id,
240 l_ref_ro_bulletin_rec.last_viewed_date,
241 l_ref_ro_bulletin_rec.last_viewed_by,
242 l_ref_ro_bulletin_rec.source_type,
243 l_ref_ro_bulletin_rec.source_id,
244 l_ref_ro_bulletin_rec.object_version_number,
245 l_ref_ro_bulletin_rec.created_by,
246 l_ref_ro_bulletin_rec.creation_date,
247 l_ref_ro_bulletin_rec.last_updated_by,
248 l_ref_ro_bulletin_rec.last_update_date,
249 l_ref_ro_bulletin_rec.last_update_login;
250
251 IF ( cur_get_ro_bulletin%NOTFOUND) THEN
252 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
253 FND_MESSAGE.Set_Name('CSD', 'CSD_RO_BTNS_NO_RECORD_FOUND'); -- no record is returned for the query
254 FND_MESSAGE.set_token( 'BULLETIN_ID', p_ro_bulletin_rec.RO_BULLETIN_ID);
255 FND_MSG_PUB.Add;
256 END IF;
257 CLOSE cur_get_ro_bulletin;
258 RAISE FND_API.G_EXC_ERROR;
259 END IF;
260 CLOSE cur_get_ro_bulletin;
261
262 --* NOTE: add validation logic here if needed
263
264
265 --* Invoke table handler(CSD_RO_BULLETINS_PKG.Update_Row)
266 CSD_RO_BULLETINS_PKG.UPDATE_ROW(
267 p_ro_bulletin_id => p_ro_bulletin_rec.ro_bulletin_id
268 ,p_repair_line_id => p_ro_bulletin_rec.repair_line_id
269 ,p_bulletin_id => p_ro_bulletin_rec.bulletin_id
270 ,p_last_viewed_date => p_ro_bulletin_rec.last_viewed_date
271 ,p_last_viewed_by => p_ro_bulletin_rec.last_viewed_by
272 ,p_source_type => p_ro_bulletin_rec.source_type
273 ,p_source_id => p_ro_bulletin_rec.source_id
274 ,p_object_version_number => p_ro_bulletin_rec.object_version_number
275 ,p_created_by => FND_API.G_MISS_NUM
276 ,p_creation_date => FND_API.G_MISS_DATE
277 ,p_last_updated_by => FND_GLOBAL.USER_ID
278 ,p_last_update_date => SYSDATE
279 ,p_last_update_login => p_ro_bulletin_rec.last_update_login);
280
281 --* logic ends here *--
282
283 --* Standard check for p_commit
284 IF FND_API.to_Boolean( p_commit ) THEN
285 COMMIT WORK;
286 END IF;
287
288
289 --* Standard call to get message count and if count is 1, get message info.
290 FND_MSG_PUB.Count_And_Get(
291 p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294
295 EXCEPTION
296 WHEN FND_API.G_EXC_ERROR THEN
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
299 P_API_NAME => c_API_NAME
300 ,P_PKG_NAME => G_PKG_NAME
301 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
302 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
303 ,X_MSG_COUNT => X_MSG_COUNT
304 ,X_MSG_DATA => X_MSG_DATA
305 ,X_RETURN_STATUS => X_RETURN_STATUS);
306
307 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
310 P_API_NAME => c_API_NAME
311 ,P_PKG_NAME => G_PKG_NAME
312 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
313 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
314 ,X_MSG_COUNT => X_MSG_COUNT
315 ,X_MSG_DATA => X_MSG_DATA
316 ,X_RETURN_STATUS => X_RETURN_STATUS);
317
318 WHEN OTHERS THEN
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
321 P_API_NAME => c_API_NAME
322 ,P_PKG_NAME => G_PKG_NAME
323 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
324 ,P_SQLCODE => SQLCODE
325 ,P_SQLERRM => SQLERRM
326 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
327 ,X_MSG_COUNT => X_MSG_COUNT
328 ,X_MSG_DATA => X_MSG_DATA
329 ,X_RETURN_STATUS => X_RETURN_STATUS);
330 END UPDATE_RO_BULLETIN;
331
332
333 -- *******************************************************
334 -- Start of Comments
335 -- *******************************************************
336 -- API Name: DELETE_RO_BULLETIN
337 -- Type : Private
338 -- Pre-Req :
339 -- Parameters:
340 -- IN
341 -- p_api_version_number IN NUMBER Required
342 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
343 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
344 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
345 -- p_ro_bulletin_id IN NUMBER Required
346 --
347 -- OUT:
348 -- x_return_status OUT NOCOPY VARCHAR2
349 -- x_msg_count OUT NOCOPY NUMBER
350 -- x_msg_data OUT NOCOPY VARCHAR2
351 -- History: Jan-16-2008 rfieldma created
352 -- -------------------
353 -- End of Comments
354 -- -------------------
355 PROCEDURE DELETE_RO_BULLETIN(
356 p_api_version_number IN NUMBER,
357 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
358 p_commit IN VARCHAR2 := FND_API.G_FALSE,
359 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
360 p_ro_bulletin_id IN NUMBER,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2
364 ) IS
365 ---- local constants ----
366 c_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_RO_BULLETIN';
367 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
368
369 BEGIN
370 --* Standard Start of API savepoint
371 SAVEPOINT DELETE_RO_BULLETIN_PVT;
372
373 --* Standard call to check for call compatibility.
374 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
375 p_api_version_number,
376 c_API_NAME,
377 G_PKG_NAME)
378 THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381
382 --* Initialize message list if p_init_msg_list is set to TRUE.
383 IF FND_API.to_Boolean( p_init_msg_list )
384 THEN
385 FND_MSG_PUB.initialize;
386 END IF;
387
388 --* Initialize API return status to SUCCESS
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 --* logic starts here *--
392
393 --* Invoke table handler(CSD_RO_BULLETINS_PKG.Delete_Row)
394 CSD_RO_BULLETINS_PKG.DELETE_ROW(p_ro_bulletin_id => p_ro_bulletin_id);
395
396
397 -- logic ends here *--
398
399 --* Standard check for p_commit
400 IF FND_API.to_Boolean( p_commit ) THEN
401 COMMIT WORK;
402 END IF;
403
404
405 --* Standard call to get message count and if count is 1, get message info.
406 FND_MSG_PUB.Count_And_Get(
407 p_count => x_msg_count,
408 p_data => x_msg_data
409 );
410
411 EXCEPTION
412 WHEN FND_API.G_EXC_ERROR THEN
413 x_return_status := FND_API.G_RET_STS_ERROR;
414 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
415 P_API_NAME => c_API_NAME
416 ,P_PKG_NAME => G_PKG_NAME
417 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
418 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
419 ,X_MSG_COUNT => X_MSG_COUNT
420 ,X_MSG_DATA => X_MSG_DATA
421 ,X_RETURN_STATUS => X_RETURN_STATUS);
422
423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
426 P_API_NAME => c_API_NAME
427 ,P_PKG_NAME => G_PKG_NAME
428 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
429 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
430 ,X_MSG_COUNT => X_MSG_COUNT
431 ,X_MSG_DATA => X_MSG_DATA
432 ,X_RETURN_STATUS => X_RETURN_STATUS);
433
434 WHEN OTHERS THEN
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
437 P_API_NAME => c_API_NAME
438 ,P_PKG_NAME => G_PKG_NAME
439 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
440 ,P_SQLCODE => SQLCODE
441 ,P_SQLERRM => SQLERRM
442 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
443 ,X_MSG_COUNT => X_MSG_COUNT
444 ,X_MSG_DATA => X_MSG_DATA
445 ,X_RETURN_STATUS => X_RETURN_STATUS);
446 End DELETE_RO_BULLETIN;
447
448 -- *******************************************************
449 -- Start of Comments
450 -- *******************************************************
451 -- API Name: LOCK_RO_BULLETIN
452 -- Type : Private
453 -- Pre-Req :
454 -- Parameters:
455 -- IN
456 -- p_api_version_number IN NUMBER Required
457 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
458 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
459 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
460 -- p_ro_bulletin_rec IN RO_BULLETIN_REC_TYPE Required
461 -- OUT:
462 -- x_return_status OUT NOCOPY VARCHAR2
463 -- x_msg_count OUT NOCOPY NUMBER
464 -- x_msg_data OUT NOCOPY VARCHAR2
465 -- History: Jan-16-2008 rfieldma created
466 -- -------------------
467 -- End of Comments
468 -- -------------------
469 PROCEDURE LOCK_RO_BULLETIN(
470 p_api_version_number IN NUMBER,
471 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
472 p_commit IN VARCHAR2 := FND_API.G_FALSE,
473 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
474 p_ro_bulletin_rec IN RO_BULLETIN_Rec_Type,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2
478 )IS
479 ---- local constants ----
480 c_API_NAME CONSTANT VARCHAR2(30) := 'LOCK_RO_BULLETIN';
481 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
482
483 BEGIN
484 --* Standard Start of API savepoint
485 SAVEPOINT DELETE_RO_BULLETIN_PVT;
486
487 --* Standard call to check for call compatibility.
488 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
489 p_api_version_number,
490 c_API_NAME,
491 G_PKG_NAME)
492 THEN
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494 END IF;
495
496 --* Initialize message list if p_init_msg_list is set to TRUE.
497 IF FND_API.to_Boolean( p_init_msg_list )
498 THEN
499 FND_MSG_PUB.initialize;
500 END IF;
501
502 --* Initialize API return status to SUCCESS
503 x_return_status := FND_API.G_RET_STS_SUCCESS;
504
505 --* logic starts here *--
506
507 -- Invoke table handler(CSD_RO_BULLETINS_PKG.Lock_Row)
508 CSD_RO_BULLETINS_PKG.LOCK_ROW(
509 p_ro_bulletin_id => p_ro_bulletin_rec.ro_bulletin_id
510 ,p_object_version_number => p_ro_bulletin_rec.object_version_number);
511
512 --* logic ends here *--
513
514 --* Standard check for p_commit
515 IF FND_API.to_Boolean( p_commit ) THEN
516 COMMIT WORK;
517 END IF;
518
519
520 --* Standard call to get message count and if count is 1, get message info.
521 FND_MSG_PUB.Count_And_Get(
522 p_count => x_msg_count,
523 p_data => x_msg_data
524 );
525
526 EXCEPTION
527 WHEN FND_API.G_EXC_ERROR THEN
528 x_return_status := FND_API.G_RET_STS_ERROR;
529 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
530 P_API_NAME => c_API_NAME
531 ,P_PKG_NAME => G_PKG_NAME
532 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
533 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
534 ,X_MSG_COUNT => X_MSG_COUNT
535 ,X_MSG_DATA => X_MSG_DATA
536 ,X_RETURN_STATUS => X_RETURN_STATUS);
537
538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
541 P_API_NAME => c_API_NAME
542 ,P_PKG_NAME => G_PKG_NAME
543 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
544 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
545 ,X_MSG_COUNT => X_MSG_COUNT
546 ,X_MSG_DATA => X_MSG_DATA
547 ,X_RETURN_STATUS => X_RETURN_STATUS);
548
549 WHEN OTHERS THEN
550 x_return_status := FND_API.G_RET_STS_ERROR;
551 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
552 P_API_NAME => c_API_NAME
553 ,P_PKG_NAME => G_PKG_NAME
554 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
555 ,P_SQLCODE => SQLCODE
556 ,P_SQLERRM => SQLERRM
557 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
558 ,X_MSG_COUNT => X_MSG_COUNT
559 ,X_MSG_DATA => X_MSG_DATA
560 ,X_RETURN_STATUS => X_RETURN_STATUS);
561 END LOCK_RO_BULLETIN;
562
563
564
565 -- *******************************************************
566 -- Start of Comments
567 -- *******************************************************
568 -- API Name: LINK_BULLETINS_TO_RO
569 -- Type : Private
570 -- Pre-Req :
571 -- Parameters:
572 -- IN
573 -- p_api_version_number IN NUMBER Required
574 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
575 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
576 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
577 -- p_repair_line_id IN NUMBER Required
578 -- px_ro_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE Required
579 -- OUT:
580 -- x_return_status OUT NOCOPY VARCHAR2
581 -- x_msg_count OUT NOCOPY NUMBER
582 -- x_msg_data OUT NOCOPY VARCHAR2
583 -- History: Jan-16-2008 rfieldma created
584 -- -------------------
585 -- End of Comments
586 -- -------------------
587 PROCEDURE LINK_BULLETINS_TO_RO(
588 p_api_version_number IN NUMBER,
589 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
590 p_commit IN VARCHAR2 := FND_API.G_FALSE,
591 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
592 p_repair_line_id IN NUMBER,
593 px_ro_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE,
594 x_return_status OUT NOCOPY VARCHAR2,
595 x_msg_count OUT NOCOPY NUMBER,
596 x_msg_data OUT NOCOPY VARCHAR2
597 ) IS
598 ---- local constants ----
599 c_API_NAME CONSTANT VARCHAR2(30) := 'LINK_BULLETINS_TO_RO';
600 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
601
602 ---- local viariables ----
603 l_ro_bulletin_id NUMBER := NULL;
604 l_rule_matching_rec CSD_RULES_ENGINE_PVT.CSD_RULE_MATCHING_REC_TYPE;
605 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
606 l_bulletin_id NUMBER := NULL;
607 l_rule_id NUMBER := NULL;
608 l_rule_results_tbl CSD_RULES_ENGINE_PVT.CSD_RULE_RESULTS_TBL_TYPE;
609 l_rule_results_rec CSD_RULES_ENGINE_PVT.CSD_RULE_RESULTS_REC_TYPE;
610 l_rec_ind INTEGER := NULL;
611 l_repln_rec CSD_REPAIRS_PUB.REPLN_Rec_Type;
612
613 BEGIN
614 --* Standard Start of API savepoint
615 SAVEPOINT LINK_BULLETINS_TO_RO_PVT;
616
617 --* Standard call to check for call compatibility.
618 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
619 p_api_version_number,
620 c_API_NAME,
621 G_PKG_NAME)
622 THEN
623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
624 END IF;
625
626 --* Initialize message list if p_init_msg_list is set to TRUE.
627 IF FND_API.to_Boolean( p_init_msg_list )
628 THEN
629 FND_MSG_PUB.initialize;
630 END IF;
631
632 --* Initialize API return status to SUCCESS
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 --* logic starts here *--
636
637 --* first update bulletin check date via pvt
638 l_repln_rec.bulletin_check_date := sysdate;
639
640 --** debug starts!!
641 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK bulletin_check_date = ' || l_repln_rec.bulletin_check_date);
642 --** debug ends!!
643
644 l_repln_rec.object_version_number := GET_CSD_REPAIRS_OBJ_VER_NUM(p_repair_line_id);
645 CSD_REPAIRS_PVT.update_repair_order(
646 p_api_version_number => p_api_version_number,
647 p_init_msg_list => p_init_msg_list,
648 p_commit => p_commit,
649 p_validation_level => p_validation_level,
650 p_repair_line_id => p_repair_line_id,
651 p_repln_rec => l_repln_rec,
652 x_return_status => x_return_status,
653 x_msg_count => x_msg_count,
654 x_msg_data => x_msg_data
655 );
656 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
657 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
658 FND_MESSAGE.Set_Name('CSD', 'CSD_UPDATE_REPAIR_FAILED');
659 FND_MSG_PUB.Add;
660 END IF;
661 RAISE FND_API.G_EXC_ERROR;
662 END IF; --* end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) *--
663
664 /* BEGIN: Algorithm:
665 * (1) init l_rule_matching_rec with
666 * l_rule_matching_rec.rule_match_code := CSD_RULES_ENGINE_PVT.G_RULE_MATCH_ALL;
667 * l_rule_matching_rec.rule_type := CSD_RULES_ENGINE_PVT.G_RULE_TYPE_BULLETIN;
668 * l_rule_matching_rec.rule_input_rec := l_rule_input_rec;
669 * l_rule_input_rec.repair_line_Id := p_repair_line_id;
670 * (2) call CSD_RULES_ENGINE_PVT.PROCESS_RULE_MATCHING(
671 * p_api_version => p_api_version,
672 * p_commit => p_commit,
673 * p_init_msg_list => p_init_msg_list,
674 * p_validation_level => p_validation_level,
675 * px_rule_matching_rec => l_rule_matching_rec,
676 * x_return_status => l_return_status,
677 * x_msg_count => x_msg_count,
678 * x_msg_data => x_msg_data);
679 * (3) FOR EACH rule_results_rec in rule_results_tbl LOOP
680 * IF Bulletin is active and published THEN
681 * IF freq_code = ONE_REPAIR THEN
682 * IF (NOT exists in csd_ro_bulletins check based on repair ) THEN
683 * Call Create new link procecure
684 * END IF;
685 * ELSIF freq_code = ONE_INSTANCE THEN
686 * IF (NOT exists in csd_ro_bulletins check based on instance) THEN
687 * Call Create new link proceure
688 * END IF;
689 * END IF;
690 * END IF;
691 * END LOOP;
692 * (4) Create new link procedure:
693 * - adds a new rec in csd_ro_bulletins
694 * - adds ro service codes id to ro services codes table
695 * - check if escalated, if yes, place holder for setting escalated on RO
696 * - check if work flow, if yes, place holder for launching workflow
697 * - add associated SCs to SC list
698 * END: Algorithm */
699 --* init l_rule_matching_rec
700 l_rule_matching_rec.rule_match_code := CSD_RULES_ENGINE_PVT.G_RULE_MATCH_ALL;
701 l_rule_matching_rec.rule_type := CSD_RULES_ENGINE_PVT.G_RULE_TYPE_BULLETIN;
702
703 l_rule_input_rec.repair_line_Id := p_repair_line_id; -- must assign this val to rec first
704 l_rule_matching_rec.rule_input_rec := l_rule_input_rec;
705
706
707 CSD_RULES_ENGINE_PVT.PROCESS_RULE_MATCHING(
708 p_api_version_number => p_api_version_number,
709 p_commit => p_commit,
710 p_init_msg_list => p_init_msg_list,
711 p_validation_level => p_validation_level,
712 px_rule_matching_rec => l_rule_matching_rec,
713 x_return_status => x_return_status,
714 x_msg_count => x_msg_count,
715 x_msg_data => x_msg_data
716 );
717
718 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
719 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
720 FND_MESSAGE.Set_Name('CSD', 'CSD_RULE_MATCH_FAILED');
721 FND_MSG_PUB.Add;
722 END IF;
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;
725
726 --** debug starts!!
727 --dbms_output.put_line('in LINK_BULLETINS_TO_RO x_return_status = ' || x_return_status);
728 --** debug ends!!
729
730
731 l_rule_results_tbl := l_rule_matching_rec.RULE_RESULTS_TBL;
732
733 l_rec_ind := l_rule_results_tbl.FIRST;
734
735 --** debug starts!!
736 --dbms_output.put_line('in LINK_BULLETINS_TO_RO l_rec_ind = ' || l_rec_ind);
737 --** debug ends!!
738
739 LOOP
740 EXIT WHEN l_rec_ind IS NULL;
741 l_rule_results_rec := l_rule_results_tbl(l_rec_ind);
742 --* loop logic begins
743 l_bulletin_id := l_rule_results_rec.defaulting_value;
744 l_rule_id := l_rule_results_rec.rule_id;
745 --** debug starts!!
746 --dbms_output.put_line('in LINK_BULLETINS_TO_RO LOOP p_repair_line_id = ' || p_repair_line_id);
747 --dbms_output.put_line('in LINK_BULLETINS_TO_RO LOOP l_bulletin_id = ' || l_bulletin_id);
748 --dbms_output.put_line('in LINK_BULLETINS_TO_RO LOOP l_rule_id = ' || l_rule_id);
749 --** debug ends!!
750
751 CREATE_NEW_RO_BULLETIN_LINK(
752 p_api_version_number => p_api_version_number,
753 p_commit => p_commit,
754 p_init_msg_list => p_init_msg_list,
755 p_validation_level => p_validation_level,
756 p_repair_line_id => p_repair_line_id,
757 p_bulletin_id => l_bulletin_id,
758 p_rule_id => l_rule_id,
759 px_ro_sc_ids_tbl => px_ro_sc_ids_tbl,
760 x_return_status => x_return_status,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data
763 );
764 --** debug starts!!
765 -- dbms_output.put_line('in LINK_BULLETINS_TO_RO ONE_REPAIR - after create new link, x_return_status = ' || x_return_status);
766 --** debug ends!!
767 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
768 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
769 FND_MESSAGE.Set_Name('CSD', 'CSD_CREATE_RO_BLTN_LINK_FAILED');
770 FND_MSG_PUB.Add;
771 END IF;
772 RAISE FND_API.G_EXC_ERROR;
773 END IF; -- end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN --
774 --* loop logic ends
775
776 l_rec_ind := l_rule_results_tbl.NEXT(l_rec_ind);
777 END LOOP; --* END loop that loops through all recs in the tbl *--
778 --* logic ends here *--
779
780 --* Standard check for p_commit
781 IF FND_API.to_Boolean( p_commit ) THEN
782 COMMIT WORK;
783 END IF;
784
785 --* Standard call to get message count and if count is 1, get message info.
786 FND_MSG_PUB.Count_And_Get(
787 p_count => x_msg_count,
788 p_data => x_msg_data
789 );
790
791 EXCEPTION
792 WHEN FND_API.G_EXC_ERROR THEN
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
795 P_API_NAME => c_API_NAME
796 ,P_PKG_NAME => G_PKG_NAME
797 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
798 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
799 ,X_MSG_COUNT => X_MSG_COUNT
800 ,X_MSG_DATA => X_MSG_DATA
801 ,X_RETURN_STATUS => X_RETURN_STATUS);
802
803 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
804 x_return_status := FND_API.G_RET_STS_ERROR;
805 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
806 P_API_NAME => c_API_NAME
807 ,P_PKG_NAME => G_PKG_NAME
808 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
809 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
810 ,X_MSG_COUNT => X_MSG_COUNT
811 ,X_MSG_DATA => X_MSG_DATA
812 ,X_RETURN_STATUS => X_RETURN_STATUS);
813
814 WHEN OTHERS THEN
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
817 P_API_NAME => c_API_NAME
818 ,P_PKG_NAME => G_PKG_NAME
819 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
820 ,P_SQLCODE => SQLCODE
821 ,P_SQLERRM => SQLERRM
822 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
823 ,X_MSG_COUNT => X_MSG_COUNT
824 ,X_MSG_DATA => X_MSG_DATA
825 ,X_RETURN_STATUS => X_RETURN_STATUS);
826 END LINK_BULLETINS_TO_RO;
827
828
829 -- *******************************************************
830 -- Start of Comments
831 -- *******************************************************
832 -- API Name: CREATE_NEW_RO_BULLETIN_LINK
833 -- Type : Private
834 -- Pre-Req :
835 -- Parameters:
836 -- IN
837 -- p_api_version_number IN NUMBER Required
838 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
839 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
840 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
841 -- p_repair_line_id IN NUMBER Required
842 -- p_bulletin_id IN NUMBER Required
843 -- px_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE Required
844 -- OUT:
845 -- x_return_status OUT NOCOPY VARCHAR2
846 -- x_msg_count OUT NOCOPY NUMBER
847 -- x_msg_data OUT NOCOPY VARCHAR2
848 -- History: Jan-17-2008 rfieldma created
849 -- -------------------
850 -- End of Comments
851 -- -------------------
852 PROCEDURE CREATE_NEW_RO_BULLETIN_LINK(
853 p_api_version_number IN NUMBER,
854 p_commit IN VARCHAR2,
855 p_init_msg_list IN VARCHAR2,
856 p_validation_level IN NUMBER,
857 p_repair_line_id IN NUMBER,
858 p_bulletin_id IN NUMBER,
859 p_rule_id IN NUMBER,
860 px_ro_sc_ids_tbl IN OUT NOCOPY CSD_RO_SC_IDS_TBL_TYPE,
861 x_return_status OUT NOCOPY VARCHAR2,
862 x_msg_count OUT NOCOPY NUMBER,
863 x_msg_data OUT NOCOPY VARCHAR2
864 ) IS
865 ---- local constants ----
866 c_api_name CONSTANT VARCHAR2(30) := 'CREATE_NEW_RO_BULLETIN_LINK';
867 c_api_version_number CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
868 -- For setting WF engine threshold
869 c_wf_negative_threshold CONSTANT NUMBER := -1;
870
871 ---- local variables ----
872 l_ro_bulletin_rec RO_BULLETIN_REC_TYPE;
873 l_ro_bulletin_id NUMBER := NULL;
874 l_tbl_ind NUMBER := NULL;
875 l_escalation_code VARCHAR2(30) := NULL;
876 l_wf_item_type VARCHAR2(8) := NULL;
877 l_wf_process_name VARCHAR2(30) := NULL;
878 l_wf_item_key VARCHAR2(240) := NULL;
879 l_wf_current_threshold NUMBER := NULL;
880 l_repln_rec CSD_REPAIRS_PUB.REPLN_Rec_Type;
881 l_instance_id NUMBER := NULL;
882 l_freq_code VARCHAR2(30) := NULL;
883 l_create_new_ro_bulletin_link VARCHAR2(1) := FND_API.G_FALSE;
884
885 ---- cursors ----
886
887 CURSOR cur_get_bulletin_info(p_bulletin_id NUMBER) IS
888 SELECT escalation_code,
889 wf_item_type,
890 wf_process_name
891 FROM csd_bulletins_b
892 WHERE bulletin_id = p_bulletin_id
893 ; --* end cur_get_bulletin_info *--
894
895 --* get service bulletin ids for this bulletin that has been linked *--
896 --* to an RO *--
897 CURSOR cur_get_ro_sc_ids(p_bulletin_id NUMBER) IS
898 SELECT b.service_code_id
899 FROM csd_bulletins_b a, csd_bulletin_scs b
900 WHERE a.bulletin_id = b.bulletin_id
901 AND a.bulletin_id = p_bulletin_id
902 ; --* end cur_get_ro_sc_ids *--
903 ro_sc_id_rec cur_get_ro_sc_ids%ROWTYPE;
904
905 -- swai: move cursors to this procedure since bulletin check
906 -- has been moved to this procedure.
907
908 --* returns freq_code of only if bulletin is active *--
909 --* and published *--
910 CURSOR cur_get_bulletin_freq_code(p_bulletin_id NUMBER) IS
911 SELECT frequency_code
912 FROM csd_bulletins_b
913 WHERE bulletin_id = p_bulletin_id
914 AND published_flag = FND_API.G_TRUE
915 AND sysdate BETWEEN NVL(active_from,sysdate)
916 AND NVL(active_to, sysdate)
917 ; --* end CURSOR cur_get_bulletin_freq_code *--
918
919 --* returns ro_bulletin_id in csd_ro_bulletins *--
920 --* based on repair_line_id *--
921 CURSOR cur_check_by_repair(p_repair_line_id NUMBER,
922 p_bulletin_id NUMBER) IS
923 SELECT a.ro_bulletin_id
924 FROM csd_ro_bulletins a
925 WHERE a.repair_line_id = p_repair_line_id
926 AND a.bulletin_id = p_bulletin_id
927 ; --* end CURSOR cur_check_by_repair *--
928
929 CURSOR cur_check_by_instance(p_instance_id NUMBER,
930 p_bulletin_id NUMBER) IS
931 SELECT a.ro_bulletin_id
932 FROM csd_ro_bulletins a
933 WHERE a.repair_line_id IN
934 (SELECT repair_line_id
935 FROM csd_repairs
936 WHERE customer_product_id = p_instance_id)
937 AND a.bulletin_id = p_bulletin_id
938 ; --* end cur_check_by_instance *--
939 BEGIN
940 --* Standard Start of API savepoint
941 SAVEPOINT CREATE_NEW_RO_BLTN_LINK_PVT;
942
943 --* Standard call to check for call compatibility.
944 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
945 p_api_version_number,
946 c_API_NAME,
947 G_PKG_NAME)
948 THEN
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950 END IF;
951
952 --* Initialize message list if p_init_msg_list is set to TRUE.
953 IF FND_API.to_Boolean( p_init_msg_list )
954 THEN
955 FND_MSG_PUB.initialize;
956 END IF;
957
958 --* Initialize API return status to SUCCESS
959 x_return_status := FND_API.G_RET_STS_SUCCESS;
960
961 OPEN cur_get_bulletin_freq_code(p_bulletin_id); -- get freq code of active, published bulletins
962 FETCH cur_get_bulletin_freq_code
963 INTO l_freq_code;
964 --** debug starts!!
965 --dbms_output.put_line('in LINK_BULLETINS_TO_RO LOOP l_freq_code = ' || l_freq_code);
966 --** debug ends!!
967
968 -- swai: relocated code for checking bulletin frequency from LINK_BULLETINS_TO_RO
969 -- to CREATE_NEW_RO_BULLETIN_LINK, since this logic is needed whenever linking
970 -- a bulletin to an RO.
971 IF ( cur_get_bulletin_freq_code%NOTFOUND) THEN -- not active, so go on to the next rec
972 CLOSE cur_get_bulletin_freq_code;
973 ELSE
974 CLOSE cur_get_bulletin_freq_code;
975 IF (l_freq_code = G_FREQ_ONE_REPAIR) THEN
976 OPEN cur_check_by_repair(p_repair_line_id, p_bulletin_id);
977 FETCH cur_check_by_repair INTO l_ro_bulletin_id;
978 --** debug starts!!
979 --dbms_output.put_line('in LINK_BULLETINS_TO_RO ONE_REPAIR - before l_ro_bulletin_id' || l_ro_bulletin_id);
980 --** debug ends!!
981
982 IF ( cur_check_by_repair%NOTFOUND) THEN -- does not exist, so create new link
983 --** debug starts!!
984 --dbms_output.put_line('in LINK_BULLETINS_TO_RO ONE_REPAIR - not found');
985 --** debug ends!!
986 l_create_new_ro_bulletin_link := FND_API.G_TRUE;
987 END IF; -- end IF ( cur_check_by_repair%NOTFOUND) THEN --
988 CLOSE cur_check_by_repair; -- close cursor
989 ELSIF (l_freq_code = G_FREQ_ONE_INSTANCE) THEN
990 l_instance_id := CSD_RULES_ENGINE_PVT.GET_RO_INSTANCE_ID(p_repair_line_id);
991 --** debug starts!!
992 --dbms_output.put_line('in LINK_BULLETINS_TO_RO INSTANCE - l_instance_id = ' || l_instance_id);
993 --** debug ends!!
994
995 IF (l_instance_id IS NOT NULL) AND (l_instance_Id <> FND_API.G_MISS_NUM) THEN
996 OPEN cur_check_by_instance(l_instance_id, p_bulletin_id);
997 FETCH cur_check_by_instance INTO l_ro_bulletin_id;
998
999 --** debug starts!!
1000 --dbms_output.put_line('in LINK_BULLETINS_TO_RO INSTANCE - l_ro_bulletin_id = ' || l_ro_bulletin_id );
1001 --** debug ends!!
1002
1003 --** debug starts!!
1004 --dbms_output.put_line('in LINK_BULLETINS_TO_RO found ONE_INSTANCE - l_instance_id = ' || l_instance_id);
1005 --dbms_output.put_line('in LINK_BULLETINS_TO_RO found ONE_INSTANCE - p_bulletin_id = ' || p_bulletin_id);
1006 --dbms_output.put_line('in LINK_BULLETINS_TO_RO found ONE_INSTANCE - before l_ro_bulletin_id = ' || l_ro_bulletin_id);
1007 --** debug ends!!
1008
1009 IF ( cur_check_by_instance%NOTFOUND) THEN
1010 --** debug starts!!
1011 --dbms_output.put_line('in LINK_BULLETINS_TO_RO ONE_INSTANCE - before create_new_ro_bulletin_link ');
1012 --** debug ends!!
1013 l_create_new_ro_bulletin_link := FND_API.G_TRUE;
1014 END IF; --* end IF ( cur_check_by_instance%NOTFOUND) *--
1015 CLOSE cur_check_by_instance; -- close cursor
1016 END IF; --* end (l_instance_id IS NOT NULL)... *--
1017 ELSE --* unrecognized code
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 END IF; --* end IF (l_freq_code = G_FREQ_ONE_REPAIR) *--
1020 END IF; --* end IF ( cur_get_bulletin_freq_code%NOTFOUND) *--
1021
1022 --* logic starts here *--
1023 /* BEGIN: Algorithm
1024 * (1) adds a new rec in csd_ro_bulletins
1025 * create l_ro_bulletin_rec
1026 * create_ro_bulletin
1027 * (2) adds ro service codes id to ro services codes table
1028 * cur_get_ro_sc_ids(service_code_id, repair_line_id)
1029 * (3) check if escalated, if yes, place holder for setting escalated on RO
1030 * cur_get_bulletin_info
1031 * (4) check if work flow, if yes, place holder for launching workflow
1032 * cur_get_bulletin_info
1033 * (5) add associated SCs to SC list
1034 * END: Algorithm */
1035 IF (l_create_new_ro_bulletin_link = FND_API.G_TRUE) THEN
1036 --* link ro bulletin
1037 l_ro_bulletin_rec.repair_line_id := p_repair_line_id;
1038 l_ro_bulletin_rec.bulletin_id := p_bulletin_id;
1039 l_ro_bulletin_rec.source_type := G_SOURCE_TYPE_RULE;
1040 l_ro_bulletin_rec.source_id := p_rule_id;
1041 l_ro_bulletin_rec.object_version_number := G_OBJ_VERSION_NUMBER_1;
1042 l_ro_bulletin_rec.last_update_login := FND_GLOBAL.USER_ID;
1043
1044 CREATE_RO_BULLETIN(
1045 p_api_version_number => p_api_version_number,
1046 p_init_msg_list => p_init_msg_list,
1047 p_commit => p_commit,
1048 p_validation_level => p_validation_level,
1049 p_ro_bulletin_rec => l_ro_bulletin_rec,
1050 x_ro_bulletin_id => l_ro_bulletin_id,
1051 x_return_status => x_return_status,
1052 x_msg_count => x_msg_count,
1053 x_msg_data => x_msg_data
1054 );
1055 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1056 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1057 FND_MESSAGE.Set_Name('CSD', 'CSD_CREATE_RO_BLTN_FAILED');
1058 FND_MSG_PUB.Add;
1059 END IF;
1060 RAISE FND_API.G_EXC_ERROR;
1061 END IF; --* end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) *--
1062
1063 --* ##################Call csd_repairs_pvt to update Bulletin check date
1064
1065 --** debug starts!!
1066 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK x_return_status = ' || x_return_status);
1067 --** debug ends!!
1068
1069 --* get ro service codes (sc) ids and append to table
1070 --* cursor for loop, cursor is implicitly open/closed
1071 l_tbl_ind := px_ro_sc_ids_tbl.COUNT;
1072 --** debug starts!!
1073 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK before scs loop l_tbl_ind = ' || l_tbl_ind);
1074 --** debug ends!!
1075 FOR ro_sc_id_rec IN cur_get_ro_sc_ids(p_bulletin_id)
1076 LOOP
1077 l_tbl_ind := l_tbl_ind+1;
1078 px_ro_sc_ids_tbl(l_tbl_ind) := ro_sc_id_rec.service_code_id;
1079 --** debug starts!!
1080 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK during scs loop sc = ' || ro_sc_id_rec.service_code_id);
1081 --** debug ends!!
1082
1083 END LOOP;
1084
1085 --** debug starts!!
1086 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK after scs loop ');
1087 --** debug ends!!
1088
1089
1090 --* get escalation/workflow info, place holder for logic later
1091 OPEN cur_get_bulletin_info(p_bulletin_id);
1092 FETCH cur_get_bulletin_info
1093 INTO l_escalation_code,
1094 l_wf_item_type,
1095 l_wf_process_name;
1096 CLOSE cur_get_bulletin_info;
1097
1098 --** debug starts!!
1099 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK l_escalation_code = ' || l_escalation_code);
1100 --** debug ends!!
1101
1102 --* update csd_repairs with escalation_code
1103 --* via csd_repairs_pvt
1104
1105
1106 IF (l_escalation_code IS NOT NULL ) THEN --* pass in when not null
1107 l_repln_rec.escalation_code := l_escalation_code;
1108 ELSE
1109 l_repln_rec.escalation_code := FND_API.G_MISS_CHAR; --* don't wipe out old value
1110 END IF; --* end IF (l_escalation_code IS NOT NULL ) *--
1111
1112 l_repln_rec.object_version_number := GET_CSD_REPAIRS_OBJ_VER_NUM(p_repair_line_id);
1113
1114
1115 CSD_REPAIRS_PVT.update_repair_order(
1116 p_api_version_number => p_api_version_number,
1117 p_init_msg_list => p_init_msg_list,
1118 p_commit => p_commit,
1119 p_validation_level => p_validation_level,
1120 p_repair_line_id => p_repair_line_id,
1121 p_repln_rec => l_repln_rec,
1122 x_return_status => x_return_status,
1123 x_msg_count => x_msg_count,
1124 x_msg_data => x_msg_data
1125 );
1126 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1127 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1128 FND_MESSAGE.Set_Name('CSD', 'CSD_UPDATE_REPAIR_FAILED');
1129 FND_MSG_PUB.Add;
1130 END IF;
1131 RAISE FND_API.G_EXC_ERROR;
1132 END IF; --* end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) *--
1133
1134
1135
1136 --* launch workflow
1137 IF (l_wf_item_type IS NOT NULL) THEN
1138 SELECT TO_CHAR(CSD_WF_ITEM_KEY_S.NEXTVAL)
1139 INTO l_wf_item_key
1140 FROM DUAL;
1141
1142 --* Get the current threshold
1143 l_wf_current_threshold := Wf_Engine.threshold;
1144
1145 --* Defer the wf process
1146 Wf_Engine.threshold := c_wf_negative_threshold;
1147
1148
1149 Wf_Engine.CreateProcess(itemtype => l_wf_item_type,
1150 itemkey => l_wf_item_key,
1151 process => l_wf_process_name --,
1152 -- user_key => NULL,
1153 -- owner_role => NULL
1154 );
1155
1156 Wf_Engine.StartProcess(itemtype => l_wf_item_type,
1157 itemkey => l_wf_item_key
1158 );
1159
1160
1161 --* Set engine to orginal threshold.
1162 --* Otherwise all WF process in this session will be deferred.
1163 Wf_Engine.threshold := l_wf_current_threshold;
1164 END IF; --* end IF (l_wf_item_type IS NOT NULL) *--
1165
1166 --** debug starts!!
1167 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK after get_bulletin_info ');
1168 --** debug ends!!
1169
1170 END IF; --* end IF (l_create_new_ro_bulletin_link = FND_API.G_TRUE) *--
1171 --* logic ends here *--
1172
1173 --* Standard check for p_commit
1174 IF FND_API.to_Boolean( p_commit ) THEN
1175 COMMIT WORK;
1176 END IF;
1177
1178 --** debug starts!!
1179 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK after check for p_commit ');
1180 --** debug ends!!
1181
1182 --* Standard call to get message count and if count is 1, get message info.
1183 FND_MSG_PUB.Count_And_Get(
1184 p_count => x_msg_count,
1185 p_data => x_msg_data
1186 );
1187
1188 --** debug starts!!
1189 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK after count_and_get x_return_statu = ' || x_return_status);
1190 --** debug ends!!
1191
1192 EXCEPTION
1193 WHEN FND_API.G_EXC_ERROR THEN
1194 x_return_status := FND_API.G_RET_STS_ERROR;
1195 --** debug starts!!
1196 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK exception 1 ');
1197 --** debug ends!!
1198
1199 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1200 P_API_NAME => c_API_NAME
1201 ,P_PKG_NAME => G_PKG_NAME
1202 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1203 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1204 ,X_MSG_COUNT => X_MSG_COUNT
1205 ,X_MSG_DATA => X_MSG_DATA
1206 ,X_RETURN_STATUS => X_RETURN_STATUS);
1207
1208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1209 x_return_status := FND_API.G_RET_STS_ERROR;
1210 --** debug starts!!
1211 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK exception 2 ');
1212 --** debug ends!!
1213
1214 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1215 P_API_NAME => c_API_NAME
1216 ,P_PKG_NAME => G_PKG_NAME
1217 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1218 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1219 ,X_MSG_COUNT => X_MSG_COUNT
1220 ,X_MSG_DATA => X_MSG_DATA
1221 ,X_RETURN_STATUS => X_RETURN_STATUS);
1222
1223 WHEN OTHERS THEN
1224 x_return_status := FND_API.G_RET_STS_ERROR;
1225 --** debug starts!!
1226 --dbms_output.put_line('in CREATE_NEW_RO_BULLETIN_LINK exception 3 ');
1227 --** debug ends!!
1228
1229 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1230 P_API_NAME => c_API_NAME
1231 ,P_PKG_NAME => G_PKG_NAME
1232 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1233 ,P_SQLCODE => SQLCODE
1234 ,P_SQLERRM => SQLERRM
1235 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1236 ,X_MSG_COUNT => X_MSG_COUNT
1237 ,X_MSG_DATA => X_MSG_DATA
1238 ,X_RETURN_STATUS => X_RETURN_STATUS);
1239 END CREATE_NEW_RO_BULLETIN_LINK;
1240
1241 -- *******************************************************
1242 -- Start of Comments
1243 -- *******************************************************
1244 -- API Name: GET_CSD_REPAIRS_OBJ_VER_NUM
1245 -- Type : Private
1246 -- Pre-Req :
1247 -- Parameters:
1248 -- IN
1249 -- p_repair_line_id IN NUMBER Required
1250 -- OUT:
1251 -- NUMBER obj_ver_num
1252 -- History: Jan-17-2008 rfieldma created
1253 -- -------------------
1254 -- End of Comments
1255 -- -------------------
1256 FUNCTION GET_CSD_REPAIRS_OBJ_VER_NUM(
1257 p_repair_line_id IN NUMBER
1258 ) RETURN NUMBER IS
1259 ---- local vars ----
1260 l_obj_ver_num NUMBER := NULL;
1261 ---- cursors ----
1262 CURSOR cur_get_obj_ver_num(p_repair_line_id NUMBER) IS
1263 SELECT object_version_number
1264 FROM csd_repairs
1265 WHERE repair_line_id = p_repair_line_id
1266 ; --* end cur_get_obj_ver_num *--
1267
1268 BEGIN
1269 OPEN cur_get_obj_ver_num(p_repair_line_id);
1270 FETCH cur_get_obj_ver_num INTO l_obj_ver_num;
1271 CLOSE cur_get_obj_ver_num;
1272
1273 RETURN l_obj_ver_num;
1274 END GET_CSD_REPAIRS_OBJ_VER_NUM;
1275
1276
1277 /*--------------------------------------------------------------------*/
1278 /* procedure name: LINK_BULLETINS_TO_REPAIRS_CP */
1279 /* description : Links all active bulletins to all matching repairs */
1280 /* */
1281 /* STANDARD PARAMETERS */
1282 /* In Parameters : */
1283 /* */
1284 /* Output Parameters: */
1285 /* errbuf VARCHAR2 Error message */
1286 /* retcode VARCHAR2 Error Code */
1287 /* */
1288 /* NON-STANDARD PARAMETERS */
1289 /* In Parameters */
1290 /* p_params RO_BULLETIN_PARAMS_REC_TYPE Req */
1291 /* */
1292 /* */
1293 /*--------------------------------------------------------------------*/
1294 PROCEDURE LINK_BULLETINS_TO_REPAIRS_CP (
1295 errbuf OUT NOCOPY varchar2,
1296 retcode OUT NOCOPY varchar2,
1297 --concurrent program parameters go here
1298 p_BULLETIN_TYPE_CODE IN VARCHAR2 := NULL,
1299 p_RO_FLOW_STATUS_ID IN NUMBER := NULL,
1300 p_RO_INV_ORG_ID IN NUMBER := NULL,
1301 p_RO_REPAIR_ORG_ID IN NUMBER := NULL,
1302 p_RO_INV_ITEM_ID IN NUMBER := NULL
1303 )
1304 IS
1305 -- CURSORS --
1306 CURSOR c_all_active_bulletins (p_bulletin_type_code varchar2)
1307 IS
1308 select bulletin_id
1309 from csd_bulletins_b
1310 where published_flag = 'T'
1311 and sysdate between nvl(active_from, sysdate-1) and nvl(active_to, sysdate+1)
1312 and bulletin_type_code = nvl(p_bulletin_type_code, bulletin_type_code);
1313
1314 CURSOR c_bulletin_rules (p_bulletin_id number)
1315 IS
1316 select rule_id
1317 from CSD_RULES_B
1318 where attribute1 = p_bulletin_id
1319 and rule_type_code = 'BULLETIN';
1320
1321 -- CONCURRENT PROGRAM RETURN STATUSES --
1322 l_success_status CONSTANT VARCHAR2(1) := '0';
1323 l_warning_status CONSTANT VARCHAR2(1) := '1';
1324 l_error_status CONSTANT VARCHAR2(1) := '2';
1325
1326 -- STANDARD API and DEBUG CONSTANTS --
1327 l_api_name CONSTANT VARCHAR2(30) := 'LINK_BULLETINS_TO_REPAIRS_CP';
1328 l_api_version CONSTANT NUMBER := 1.0;
1329
1330 -- VARIABLES FOR FND LOG --
1331 l_error_level NUMBER := FND_LOG.LEVEL_ERROR;
1332 l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_ro_bulletins_pvt.link_bulletins_to_repairs_conc_prog';
1333
1334 -- VARIABLES --
1335 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1336 l_msg_count NUMBER;
1337 l_msg_data VARCHAR2(20000);
1338 l_current_bulletin_id NUMBER;
1339 l_bulletin_rule_id NUMBER;
1340 l_cp_params RO_BULLETIN_PARAMS_REC_TYPE;
1341
1342 BEGIN
1343 -- Initialize the error code and error buffer
1344 retcode := l_success_status;
1345 errbuf := '';
1346
1347 -- Initialize the l_cp_params
1348 l_cp_params.bulletin_type_code := p_bulletin_type_code;
1349 l_cp_params.ro_flow_status_id := p_ro_flow_status_id;
1350 l_cp_params.ro_inv_org_id := p_ro_inv_org_id;
1351 l_cp_params.ro_repair_org_id := p_ro_repair_org_id;
1352 l_cp_params.ro_inv_item_id := p_ro_inv_item_id;
1353
1354 -- Debug messages
1355 --dbms_output.put_line('At the Beginning of link_bulletins_to_repairs_conc_prog');
1356
1357 -- go through each active bulletin to link.
1358 OPEN c_all_active_bulletins(p_bulletin_type_code);
1359 LOOP
1360 FETCH c_all_active_bulletins INTO l_current_bulletin_id;
1361 EXIT when c_all_active_bulletins%NOTFOUND;
1362
1363 -- go through each rule to link.
1364 OPEN c_bulletin_rules(l_current_bulletin_id);
1365 LOOP
1366 FETCH c_bulletin_rules INTO l_bulletin_rule_id;
1367 EXIT when c_bulletin_rules%NOTFOUND;
1368
1369 -- Debug messages
1370 --dbms_output.put_line('Calling LINK_BULLETIN_FOR_RULE');
1371 l_return_status := FND_API.G_RET_STS_SUCCESS;
1372
1373 LINK_BULLETIN_FOR_RULE(
1374 p_api_version_number => l_api_version,
1375 p_commit => FND_API.G_TRUE,
1376 p_init_msg_list => FND_API.G_TRUE,
1377 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1378 x_return_status => l_return_status,
1379 x_msg_count => l_msg_count,
1380 x_msg_data => l_msg_data,
1381 p_bulletin_id => l_current_bulletin_id,
1382 p_bulletin_rule_id => l_bulletin_rule_id,
1383 p_params => l_cp_params
1384 );
1385
1386 -- Debug messages
1387 --dbms_output.put_line('Return Status from LINK_BULLETIN_FOR_RULE :'||l_return_status);
1388
1389 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1390 -- Concatenate the message from the message stack
1391 IF l_msg_count > 1 then
1392 FOR i IN 1..l_msg_count LOOP
1393 l_msg_data := l_msg_data||FND_MSG_PUB.Get(i,FND_API.G_FALSE) ;
1394 END LOOP ;
1395 END IF ;
1396 --dbms_output.put_line(l_msg_data);
1397 -- Do not exit out of loop:
1398 -- keep going, but set the retcode to record error status
1399 retcode := l_error_status;
1400 errbuf := errbuf + l_msg_data;
1401 END IF;
1402
1403 END LOOP;
1404 IF c_bulletin_rules%ISOPEN THEN
1405 close c_bulletin_rules;
1406 END IF;
1407 END LOOP;
1408 IF c_all_active_bulletins%ISOPEN THEN
1409 close c_all_active_bulletins;
1410 END IF;
1411
1412 EXCEPTION
1413 WHEN FND_API.G_EXC_ERROR THEN
1414 retcode := l_error_status;
1415 errbuf := l_msg_data;
1416 WHEN Others then
1417 -- Handle others exception
1418 retcode := l_error_status;
1419 errbuf := l_msg_data;
1420 END LINK_BULLETINS_TO_REPAIRS_CP;
1421
1422
1423 /*--------------------------------------------------------------------*/
1424 /* procedure name: LINK_BULLETIN_FOR_RULE */
1425 /* description : Given a single rule, find all matching repair orders */
1426 /* and link them to the given bulletin, if applicable */
1427 /* */
1428 /* Called from : PROCEDURE LINK_BULLETINS_TO_ALL_REPAIRS */
1429 /* Input Parm : */
1430 /* p_bulletin_id NUMBER Optional */
1431 /* If bulletin id is provided, */
1432 /* it will be used without */
1433 /* validaing against the rule */
1434 /* If p_bulletin_id = null, */
1435 /* bulletin_id will be derrived */
1436 /* from p_bulletin_rule_id */
1437 /* p_bulletin_rule_id NUMBER Req */
1438 /* */
1439 /* */
1440 /* */
1441 /*--------------------------------------------------------------------*/
1442 PROCEDURE LINK_BULLETIN_FOR_RULE (
1443 p_api_version_number IN NUMBER,
1444 p_commit IN VARCHAR2,
1445 p_init_msg_list IN VARCHAR2,
1446 p_validation_level IN NUMBER,
1447 x_return_status OUT NOCOPY VARCHAR2,
1448 x_msg_count OUT NOCOPY NUMBER,
1449 x_msg_data OUT NOCOPY VARCHAR2,
1450 p_bulletin_id IN NUMBER := NULL,
1451 p_bulletin_rule_id IN NUMBER,
1452 p_params IN RO_BULLETIN_PARAMS_REC_TYPE
1453 )
1454 IS
1455 -- TYPE FOR DYNAMIC CURSOR --
1456 TYPE REPAIR_ORDER_CURSOR IS REF CURSOR;
1457
1458 -- CURSORS --
1459 CURSOR c_get_bulletin_id (p_rule_id number)
1460 IS
1461 select to_number(attribute1) bulletin_id
1462 from CSD_RULES_B
1463 where rule_id = p_rule_id;
1464
1465
1466 -- STANDARD CONSTANTS
1467 l_api_name CONSTANT VARCHAR2(30) := 'LINK_BULLETIN_FOR_RULE';
1468 l_api_version CONSTANT NUMBER := 1.0;
1469
1470 -- VARIABLES FOR FND LOG --
1471 l_error_level number := FND_LOG.LEVEL_ERROR;
1472 l_mod_name varchar2(2000) := 'csd.plsql.csd_ro_bulletins_pvt.link_bulletin_for_rule';
1473
1474 --VARIABLES --
1475 l_sql_query VARCHAR2(32767) := null;
1476 l_repair_order_cursor REPAIR_ORDER_CURSOR;
1477 l_repair_line_id NUMBER;
1478 l_service_codes CSD_RO_SC_IDS_TBL_TYPE;
1479 l_bulletin_id NUMBER;
1480
1481 BEGIN
1482
1483 -- Standard Start of API savepoint
1484 SAVEPOINT LINK_BULLETIN_FOR_RULE;
1485
1486 -- Initialize API return status to success
1487 x_return_status := FND_API.G_RET_STS_SUCCESS;
1488
1489 -- Log the api name in the log file
1490 --dbms_output.put_line('At the Beginning of LINK_BULLETIN_FOR_RULE');
1491 --dbms_output.put_line('p_bulletin_id ='||p_bulletin_id);
1492 --dbms_output.put_line('p_bulletin_rule_id ='||p_bulletin_rule_id);
1493
1494 -- Standard call to check for call compatibility.
1495 IF NOT FND_API.Compatible_API_Call (l_api_version,
1496 p_api_version_number,
1497 l_api_name ,
1498 G_PKG_NAME )
1499 THEN
1500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1501 END IF;
1502
1503 -- Initialize message list if p_init_msg_list is set to TRUE.
1504 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1505 FND_MSG_PUB.initialize;
1506 END IF;
1507
1508 -- initialize bulletin_id
1509 IF (p_bulletin_id is null) THEN
1510 OPEN c_get_bulletin_id(p_bulletin_rule_id);
1511 FETCH c_get_bulletin_id into l_bulletin_id;
1512 IF (c_get_bulletin_id%NOTFOUND) THEN
1513 RAISE FND_API.G_EXC_ERROR;
1514 CLOSE c_get_bulletin_id;
1515 END IF;
1516 CLOSE c_get_bulletin_id;
1517 ELSE
1518 l_bulletin_id := p_bulletin_id;
1519 END IF;
1520
1521 -- get all the matching ROs for the given rule
1522 l_sql_query := CSD_RULES_ENGINE_PVT.GET_RULE_SQL_FOR_RO(p_bulletin_rule_id);
1523
1524 -- exclude any repair orders in closed state
1525 l_sql_query := l_sql_query || ' AND dra.status <> ''C''';
1526
1527 -- exclude any repair orders that are already liked to the bulletin
1528 l_sql_query := l_sql_query || ' AND not exists ( select ''X'' from csd_ro_bulletins bul'
1529 || ' where bul.source_id = ' || p_bulletin_rule_id
1530 || ' and bul.repair_line_id = dra.repair_line_id )';
1531
1532 -- Add on additional query criteria from concurrent program params --
1533 IF (p_params.ro_flow_status_id is not null) THEN
1534 l_sql_query := l_sql_query ||
1535 'and dra.FLOW_STATUS_ID = nvl('
1536 || p_params.ro_flow_status_id
1537 ||', dra.FLOW_STATUS_ID)';
1538 END IF;
1539
1540 IF (p_params.ro_inv_org_id is not null) THEN
1541 l_sql_query := l_sql_query ||
1542 'and dra.INVENTORY_ORG_ID = nvl('
1543 || p_params.ro_inv_org_id
1544 ||', dra.INVENTORY_ORG_ID)';
1545 END IF;
1546
1547 IF (p_params.ro_repair_org_id is not null) THEN
1548 l_sql_query := l_sql_query ||
1549 'and dra.OWNING_ORGANIZATION_ID = nvl('
1550 || p_params.ro_repair_org_id
1551 ||', dra.OWNING_ORGANIZATION_ID)';
1552 END IF;
1553
1554 IF (p_params.ro_inv_item_id is not null) THEN
1555 l_sql_query := l_sql_query ||
1556 'and dra.INVENTORY_ITEM_ID = nvl('
1557 || p_params.ro_inv_item_id
1558 ||', dra.INVENTORY_ITEM_ID)';
1559 END IF;
1560 -- END of concurrent program params --
1561
1562 OPEN l_repair_order_cursor FOR l_sql_query; -- (results should go into l_repair_orders)
1563 -- link the matching ROs to the bulletin passed in.
1564 LOOP
1565 FETCH l_repair_order_cursor INTO l_repair_line_id;
1566 EXIT WHEN l_repair_order_cursor%NOTFOUND;
1567 CREATE_NEW_RO_BULLETIN_LINK(
1568 p_api_version_number => 1.0,
1569 p_commit => FND_API.G_FALSE,
1570 p_init_msg_list => FND_API.G_FALSE,
1571 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1572 p_repair_line_id => l_repair_line_id,
1573 p_bulletin_id => l_bulletin_id,
1574 p_rule_id => p_bulletin_rule_id,
1575 px_ro_sc_ids_tbl => l_service_codes,
1576 x_return_status => x_return_status,
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data
1579 );
1580 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1581 --dbms_output.put_line('CREATE_NEW_RO_BULLETIN_LINK failed');
1582 RAISE FND_API.G_EXC_ERROR;
1583 END IF;
1584
1585 -- Link the service codes from the bulletin to the repair order
1586 APPLY_BULLETIN_SCS_TO_RO (
1587 p_api_version_number => 1.0,
1588 p_commit => FND_API.G_FALSE,
1589 p_init_msg_list => FND_API.G_FALSE,
1590 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1591 x_return_status => x_return_status,
1592 x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data,
1594 p_service_codes => l_service_codes,
1595 p_repair_line_id => l_repair_line_id);
1596 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1597 --dbms_output.put_line('APPLY_BULLETIN_SCS_TO_RO failed');
1598 RAISE FND_API.G_EXC_ERROR;
1599 END IF;
1600 END LOOP;
1601
1602 -- Standard check of p_commit.
1603 IF FND_API.To_Boolean( p_commit ) THEN
1604 COMMIT WORK;
1605 END IF;
1606
1607 -- Standard call to get message count and IF count is get message info.
1608 FND_MSG_PUB.Count_And_Get
1609 (p_count => x_msg_count,
1610 p_data => x_msg_data );
1611 EXCEPTION
1612 WHEN FND_API.G_EXC_ERROR THEN
1613 --dbms_output.put_line('In FND_API.G_EXC_ERROR exception');
1614 -- As we are committing the processed records in the inner APIs
1615 -- so we rollback only if the p_commit='F'
1616 IF NOT(FND_API.To_Boolean( p_commit )) THEN
1617 ROLLBACK TO LINK_BULLETIN_FOR_RULE;
1618 END IF;
1619 x_return_status := FND_API.G_RET_STS_ERROR ;
1620 FND_MSG_PUB.Count_And_Get
1621 (p_count => x_msg_count,
1622 p_data => x_msg_data );
1623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624 --dbms_output.put_line('In FND_API.G_EXC_UNEXPECTED_ERROR exception');
1625 IF ( l_error_level >= G_debug_level) THEN
1626 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1627 fnd_message.set_token('SQLERRM',SQLERRM);
1628 fnd_message.set_token('SQLCODE',SQLCODE);
1629 fnd_log.message(l_error_level,l_mod_name,FALSE);
1630 END If;
1631 -- As we are committing the processed records in the inner APIs
1632 -- so we rollback only if the p_commit='F'
1633 IF NOT(FND_API.To_Boolean( p_commit )) THEN
1634 ROLLBACK TO LINK_BULLETIN_FOR_RULE;
1635 END IF;
1636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1637 FND_MSG_PUB.Count_And_Get
1638 ( p_count => x_msg_count,
1639 p_data => x_msg_data );
1640 WHEN OTHERS THEN
1641 --dbms_output.put_line('In OTHERS exception');
1642 IF ( l_error_level >= G_debug_level) THEN
1643 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1644 fnd_message.set_token('SQLERRM',SQLERRM);
1645 fnd_message.set_token('SQLCODE',SQLCODE);
1646 fnd_log.message(l_error_level,l_mod_name,FALSE);
1647 END If;
1648 -- As we are committing the processed records in the inner APIs
1649 -- so we rollback only if the p_commit='F'
1650 IF NOT(FND_API.To_Boolean( p_commit )) THEN
1651 ROLLBACK TO LINK_BULLETIN_FOR_RULE;
1652 END IF;
1653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1654 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1655 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
1656 l_api_name );
1657 END IF;
1658 FND_MSG_PUB.Count_And_Get
1659 ( p_count => x_msg_count,
1660 p_data => x_msg_data );
1661
1662 END LINK_BULLETIN_FOR_RULE;
1663
1664 /*--------------------------------------------------------------------*/
1665 /* procedure name: APPLY_BULLETIN_SCS_TO_RO */
1666 /* description : Given set of service codes from a service bulletin */
1667 /* mark them as applicable for a repair order */
1668 /* */
1669 /* Called from : PROCEDURE LINK_BULLETIN_FOR_RULE */
1670 /* Input Parm : */
1671 /* p_service_codes CSD_RO_SC_IDS_TBL_TYPE Req */
1672 /* p_repair_line_id NUMBER Req */
1673 /* */
1674 /* */
1675 /* */
1676 /*--------------------------------------------------------------------*/
1677 PROCEDURE APPLY_BULLETIN_SCS_TO_RO (
1678 p_api_version_number IN NUMBER,
1679 p_commit IN VARCHAR2,
1680 p_init_msg_list IN VARCHAR2,
1681 p_validation_level IN NUMBER,
1682 x_return_status OUT NOCOPY VARCHAR2,
1683 x_msg_count OUT NOCOPY NUMBER,
1684 x_msg_data OUT NOCOPY VARCHAR2,
1685 p_service_codes IN CSD_RO_SC_IDS_TBL_TYPE,
1686 p_repair_line_id IN NUMBER
1687 )
1688 IS
1689 -- CURSORS --
1690 CURSOR c_validate_sc_domain (p_service_code_id number,
1691 p_repair_line_id number)
1692 IS
1693 SELECT 'X'
1694 FROM
1695 csd_sc_domains_v dom, CSD_REPAIRS dra
1696 WHERE dom.service_code_id = p_service_code_id
1697 AND dra.repair_line_id = p_repair_line_id
1698 AND (dom.inventory_item_id = dra.inventory_item_id
1699 OR (dom.category_set_id = fnd_profile.value('CSD_DEFAULT_CATEGORY_SET')
1700 AND dom.category_id in ( SELECT DISTINCT cat.category_id
1701 FROM mtl_item_categories_v cat
1702 WHERE cat.inventory_item_id = dra.inventory_item_id)
1703 )
1704 );
1705
1706 CURSOR c_get_ro_service_code (p_service_code_id number,
1707 p_repair_line_id number)
1708 IS
1709 SELECT ro_service_code_id
1710 ,object_version_number
1711 ,repair_line_id
1712 ,service_code_id
1713 ,source_type_code
1714 ,source_solution_id
1715 ,applicable_flag
1716 ,applied_to_est_flag
1717 ,applied_to_work_flag
1718 ,attribute_category
1719 ,attribute1
1720 ,attribute2
1721 ,attribute3
1722 ,attribute4
1723 ,attribute5
1724 ,attribute6
1725 ,attribute7
1726 ,attribute8
1727 ,attribute9
1728 ,attribute10
1729 ,attribute11
1730 ,attribute12
1731 ,attribute13
1732 ,attribute14
1733 ,attribute15
1734 ,service_item_id
1735 FROM CSD_RO_SERVICE_CODES
1736 WHERE repair_line_id = p_repair_line_id
1737 AND service_code_id = p_service_code_id;
1738
1739
1740 CURSOR c_get_ro_item (p_repair_line_id number)
1741 IS
1742 SELECT inventory_item_id
1743 FROM CSD_REPAIRS
1744 WHERE repair_line_id = p_repair_line_id;
1745
1746 -- STANDARD CONSTANTS
1747 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_BULLETIN_SCS_TO_RO';
1748 l_api_version CONSTANT NUMBER := 1.0;
1749
1750 -- VARIABLES FOR FND LOG --
1751 l_error_level number := FND_LOG.LEVEL_ERROR;
1752 l_mod_name varchar2(2000) := 'csd.plsql.csd_ro_bulletins_pvt.apply_bulletin_scs_to_ro';
1753
1754 -- VARIABLES --
1755 l_in_domain VARCHAR2(1);
1756 l_ro_service_code_rec CSD_RO_SERVICE_CODES_PVT.RO_SERVICE_CODE_REC_TYPE;
1757 l_ro_service_code_id NUMBER;
1758 l_obj_ver_number NUMBER;
1759
1760 BEGIN
1761
1762 -- Standard Start of API savepoint
1763 SAVEPOINT APPLY_BULLETIN_SCS_TO_RO;
1764
1765 -- Initialize API return status to success
1766 x_return_status := FND_API.G_RET_STS_SUCCESS;
1767
1768 -- Log the api name in the log file
1769 --dbms_output.put_line('At the Beginning of APPLY_BULLETIN_SCS_TO_RO');
1770 --dbms_output.put_line('p_repair_line_id ='||p_repair_line_id);
1771
1772 -- Standard call to check for call compatibility.
1773 IF NOT FND_API.Compatible_API_Call (l_api_version,
1774 p_api_version_number,
1775 l_api_name ,
1776 G_PKG_NAME )
1777 THEN
1778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1779 END IF;
1780
1781 -- Initialize message list if p_init_msg_list is set to TRUE.
1782 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1783 FND_MSG_PUB.initialize;
1784 END IF;
1785
1786 FOR i IN 1.. p_service_codes.count LOOP
1787 -- check if SC is for this repair order item
1788 -- if the cursor returns result, then the repair order item
1789 -- is within the service code domain
1790 OPEN c_validate_sc_domain(p_service_codes(i), p_repair_line_id);
1791 FETCH c_validate_sc_domain INTO l_in_domain;
1792 CLOSE c_validate_sc_domain;
1793
1794 IF (l_in_domain is not null) THEN
1795 -- get the existing record, if there is one
1796 OPEN c_get_ro_service_code(p_service_codes(i), p_repair_line_id);
1797 FETCH c_get_ro_service_code INTO
1798 l_ro_service_code_rec.ro_service_code_id
1799 ,l_ro_service_code_rec.object_version_number
1800 ,l_ro_service_code_rec.repair_line_id
1801 ,l_ro_service_code_rec.service_code_id
1802 ,l_ro_service_code_rec.source_type_code
1803 ,l_ro_service_code_rec.source_solution_id
1804 ,l_ro_service_code_rec.applicable_flag
1805 ,l_ro_service_code_rec.applied_to_est_flag
1806 ,l_ro_service_code_rec.applied_to_work_flag
1807 ,l_ro_service_code_rec.attribute_category
1808 ,l_ro_service_code_rec.attribute1
1809 ,l_ro_service_code_rec.attribute2
1810 ,l_ro_service_code_rec.attribute3
1811 ,l_ro_service_code_rec.attribute4
1812 ,l_ro_service_code_rec.attribute5
1813 ,l_ro_service_code_rec.attribute6
1814 ,l_ro_service_code_rec.attribute7
1815 ,l_ro_service_code_rec.attribute8
1816 ,l_ro_service_code_rec.attribute9
1817 ,l_ro_service_code_rec.attribute10
1818 ,l_ro_service_code_rec.attribute11
1819 ,l_ro_service_code_rec.attribute12
1820 ,l_ro_service_code_rec.attribute13
1821 ,l_ro_service_code_rec.attribute14
1822 ,l_ro_service_code_rec.attribute15
1823 ,l_ro_service_code_rec.service_item_id;
1824 CLOSE c_get_ro_service_code;
1825
1826 -- if there is an existing record,then set it to applicable
1827 -- if it is not already marked as applicable.
1828 IF (l_ro_service_code_rec.ro_service_code_id is not null) THEN
1829 IF (nvl(l_ro_service_code_rec.applicable_flag, 'N') <> 'Y') THEN
1830 l_ro_service_code_rec.applicable_flag := 'Y';
1831 CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code (
1832 p_api_version => l_api_version,
1833 p_commit => FND_API.G_FALSE,
1834 p_init_msg_list => FND_API.G_FALSE,
1835 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1836 x_return_status => x_return_status,
1837 x_msg_count => x_msg_count,
1838 x_msg_data => x_msg_data,
1839 p_ro_service_code_rec => l_ro_service_code_rec,
1840 x_obj_ver_number => l_obj_ver_number
1841 );
1842 END IF;
1843 ELSE -- if there is no existing record, then create one.
1844 l_ro_service_code_rec.repair_line_id := p_repair_line_id;
1845 l_ro_service_code_rec.service_code_id := p_service_codes(i);
1846 l_ro_service_code_rec.source_type_code := 'MANUAL';
1847 l_ro_service_code_rec.applicable_flag := 'Y';
1848 OPEN c_get_ro_item(p_repair_line_id);
1849 FETCH c_get_ro_item INTO l_ro_service_code_rec.service_item_id;
1850 CLOSE c_get_ro_item;
1851
1852 CSD_RO_SERVICE_CODES_PVT.Create_RO_Service_Code (
1853 p_api_version => l_api_version,
1854 p_commit => FND_API.G_FALSE,
1855 p_init_msg_list => FND_API.G_FALSE,
1856 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1857 x_return_status => x_return_status,
1858 x_msg_count => x_msg_count,
1859 x_msg_data => x_msg_data,
1860 p_ro_service_code_rec => l_ro_service_code_rec ,
1861 x_ro_service_code_id => l_ro_service_code_id
1862 );
1863 END IF;
1864 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1865 --dbms_output.put_line('CREATE_RO_SERVICE_CODE failed');
1866 RAISE FND_API.G_EXC_ERROR;
1867 END IF;
1868 END IF; -- end if (l_in_domain is not null)
1869
1870 END LOOP;
1871
1872 -- Standard check of p_commit.
1873 IF FND_API.To_Boolean( p_commit ) THEN
1874 COMMIT WORK;
1875 END IF;
1876
1877 -- Standard call to get message count and IF count is get message info.
1878 FND_MSG_PUB.Count_And_Get
1879 (p_count => x_msg_count,
1880 p_data => x_msg_data );
1881
1882 EXCEPTION
1883 WHEN FND_API.G_EXC_ERROR THEN
1884 --dbms_output.put_line('In FND_API.G_EXC_ERROR exception');
1885 ROLLBACK TO APPLY_BULLETIN_SCS_TO_RO;
1886 x_return_status := FND_API.G_RET_STS_ERROR ;
1887 FND_MSG_PUB.Count_And_Get
1888 (p_count => x_msg_count,
1889 p_data => x_msg_data );
1890 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1891 --dbms_output.put_line('In FND_API.G_EXC_UNEXPECTED_ERROR exception');
1892 IF ( l_error_level >= G_debug_level) THEN
1893 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1894 fnd_message.set_token('SQLERRM',SQLERRM);
1895 fnd_message.set_token('SQLCODE',SQLCODE);
1896 fnd_log.message(l_error_level,l_mod_name,FALSE);
1897 END If;
1898 ROLLBACK TO APPLY_BULLETIN_SCS_TO_RO;
1899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1900 FND_MSG_PUB.Count_And_Get
1901 ( p_count => x_msg_count,
1902 p_data => x_msg_data );
1903 WHEN OTHERS THEN
1904 -- dbms_output.put_line('In OTHERS exception' );
1905 IF ( l_error_level >= G_debug_level) THEN
1906 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1907 fnd_message.set_token('SQLERRM',SQLERRM);
1908 fnd_message.set_token('SQLCODE',SQLCODE);
1909 fnd_log.message(l_error_level,l_mod_name,FALSE);
1910 END If;
1911 ROLLBACK TO APPLY_BULLETIN_SCS_TO_RO;
1912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1913 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1914 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME ,
1915 l_api_name );
1916 END IF;
1917 FND_MSG_PUB.Count_And_Get
1918 ( p_count => x_msg_count,
1919 p_data => x_msg_data );
1920
1921 END APPLY_BULLETIN_SCS_TO_RO;
1922
1923 END CSD_RO_BULLETINS_PVT; /* Package body ends*/