[Home] [Help]
PACKAGE BODY: APPS.CSD_SPLIT_PKG
Source
1 PACKAGE BODY CSD_SPLIT_PKG as
2 /* $Header: csdspltb.pls 120.11.12010000.3 2008/11/06 07:34:15 subhat ship $ */
3 -- Start of Comments
4 -- Package name : CSD_SPLIT_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSD_SPLIT_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(15) := 'csdspltb.pls';
12
13 PROCEDURE Split_Repair_Order (
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2,
16 p_commit IN VARCHAR2,
17 p_validation_level IN NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 p_original_repair_line_id IN NUMBER,
22 p_split_option IN NUMBER,
23 p_copy_attachment IN VARCHAR2,
24 p_attachment_counts IN NUMBER,
25 p_new_quantity IN NUMBER,
26 p_repair_type_id IN NUMBER
27 )
28
29 IS
30 l_api_name CONSTANT VARCHAR2(30) := 'Split_Repair_Order' ;
31 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
32 l_api_version CONSTANT NUMBER := 1.0 ;
33 l_rep_line_rec CSD_REPAIRS_PUB.REPLN_REC_TYPE;
34 l_original_rep_line_rec CSD_REPAIRS_PUB.REPLN_REC_TYPE;
35 l_total_original_quantity NUMBER;
36 l_org_new_quantity NUMBER;
37 l_repair_history_Rec CSD_REPAIR_HISTORY_PVT.REPH_Rec_Type;
38 l_repair_history_id NUMBER;
39 l_repair_type_name VARCHAR2(30);
40 x_repair_line_id NUMBER;
41 l_original_repair_number NUMBER;
42 l_original_repair_type_id NUMBER;
43
44 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
45 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
46 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
47 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
48
49 -- swai: 12.1.1 bug 7176940 service bulletin check
50 l_ro_sc_ids_tbl CSD_RO_BULLETINS_PVT.CSD_RO_SC_IDS_TBL_TYPE;
51 l_return_status VARCHAR2 (1) ;
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2 (2000);
54
55 CURSOR c_repair_type_name(p_original_repair_line_id IN NUMBER) IS
56 SELECT crtv.name
57 from csd_repair_types_vl crtv, csd_repairs cr
58 where cr.repair_line_id = p_original_repair_line_id and crtv.repair_type_id = cr.repair_type_id;
59
60 Begin
61
62 IF(l_procedure_level >= l_debug_level) THEN
63 FND_LOG.STRING(l_procedure_level,
64 'CSD.PLSQL.CSD_SPLIT_PKG.Split_Repair_Order',
65 'Entered Split_Repair_Order API');
66 END IF;
67
68 IF(l_statement_level >= l_debug_level) THEN
69 FND_LOG.STRING(l_statement_level,
70 'CSD.PLSQL.CSD_SPLIT_PKG.Split_Repair_Order',
71 'Enabling the Split_Repair_Order savepoint');
72 END IF;
73
74 -- Standard Start of API Savepoint
75 SAVEPOINT CSD_SPLIT_PKG ;
76
77 -- Standard Call to check API compatibility
78 IF NOT FND_API.Compatible_API_Call( l_api_version,
79 p_api_version,
80 l_api_name,
81 G_PKG_NAME) THEN
82 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
83 END IF;
84
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86 IF FND_API.to_Boolean( p_init_msg_list ) THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89
90 -- Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92
93 --
94 -- API body
95 --
96 -- Local Procedure
97
98
99 Build_Repln_Record(
100 p_repair_line_id => p_original_repair_line_id,
101 x_Repln_Rec => l_rep_line_rec,
102 x_return_status => x_return_status
103 );
104 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
105 IF(l_statement_level >= l_debug_level) THEN
106 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Repln_Record','failed');
107 END IF;
108 RAISE FND_API.G_EXC_ERROR;
109 END IF;
110
111 l_total_original_quantity := l_rep_line_rec.quantity;
112 l_org_new_quantity := l_total_original_quantity - p_new_quantity;
113 l_original_repair_number := l_rep_line_rec.repair_number;
114 l_original_repair_type_id := l_rep_line_rec.repair_type_id;
115
116 if (p_split_option = 1) then
117 --Update the current RO quantity. call update_repair_order
118 if (l_org_new_quantity = 0) then
119 CSD_REPAIRS_UTIL.Convert_to_Repln_Rec_Type
120 (
121 p_QUANTITY => l_org_new_quantity,
122 p_STATUS => 'C',
123 p_object_version_number => l_rep_line_rec.object_version_number,
124 x_repln_rec => l_original_rep_line_rec
125 );
126 else
127 CSD_REPAIRS_UTIL.Convert_to_Repln_Rec_Type
128 (
129 p_QUANTITY => l_org_new_quantity,
130 p_object_version_number => l_rep_line_rec.object_version_number,
131 x_repln_rec => l_original_rep_line_rec
132 );
133 end if;
134
135 CSD_REPAIRS_PVT.Update_Repair_Order
136 (p_API_version_number => 1.0,
137 p_init_msg_list => FND_API.G_TRUE,
138 p_commit => FND_API.G_FALSE,
139 p_validation_level => null,
140 p_repair_line_id => p_original_repair_line_id,
141 p_Repln_Rec => l_original_rep_line_rec,
142 x_return_status => x_return_status,
143 x_msg_count => x_msg_count,
144 x_msg_data => x_msg_data
145 );
146 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
147 IF(l_statement_level >= l_debug_level) THEN
148 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIRS_PVT.Update_Repair_Order','failed');
149 END IF;
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 l_rep_line_rec.repair_number := null;
154 l_rep_line_rec.object_version_number := FND_API.G_MISS_NUM;
155 l_rep_line_rec.quantity := p_new_quantity;
156 l_rep_line_rec.repair_type_id := p_repair_type_id;
157
158 Create_New_Repair_Order (
159 p_api_version => 1.0,
160 p_init_msg_list => p_init_msg_list,
161 p_commit => FND_API.G_FALSE,
162 p_validation_level => null,
163 x_return_status => x_return_status,
164 x_msg_count => x_msg_count,
165 x_msg_data => x_msg_data,
166 x_repair_line_id => x_repair_line_id,
167 p_copy_attachment => p_copy_attachment,
168 p_original_repair_line_id => p_original_repair_line_id,
169 p_rep_line_rec => l_rep_line_rec
170 );
171 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
172 IF(l_statement_level >= l_debug_level) THEN
173 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order','failed');
174 END IF;
175 RAISE FND_API.G_EXC_ERROR;
176 END IF;
177
178 l_repair_history_Rec.repair_line_id := p_original_repair_line_id;
179 l_repair_history_Rec.EVENT_CODE := 'SLT';
180 l_repair_history_Rec.EVENT_DATE := sysdate;
181 l_repair_history_Rec.paramn1 := l_total_original_quantity;
182
183 CSD_REPAIR_HISTORY_PVT.Create_repair_history(
184 P_Api_Version_Number => p_api_version,
185 P_Init_Msg_List => p_init_msg_list,
186 P_Commit => FND_API.G_FALSE,
187 p_validation_level => p_validation_level,
188 P_REPH_REC => l_repair_history_Rec,
189 X_REPAIR_HISTORY_ID => l_repair_history_id,
190 X_Return_Status => x_return_status,
191 X_Msg_Count => x_msg_count,
192 X_Msg_Data => x_msg_data
193 );
194 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
195 IF(l_statement_level >= l_debug_level) THEN
196 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIR_HISTORY_PVT.Create_repair_history','failed');
197 END IF;
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200
201
202 l_repair_history_Rec.repair_line_id := x_repair_line_id;
203 l_repair_history_Rec.EVENT_CODE := 'SLT';
204 l_repair_history_Rec.EVENT_DATE := sysdate;
205 l_repair_history_Rec.paramn1 := l_total_original_quantity;
206 l_repair_history_Rec.paramc1 := l_original_repair_number;
207
208 If (p_copy_attachment = 'Y') and (p_attachment_counts > 0) THEN
209 l_repair_history_Rec.paramc2 := 'Y';
210 elsif (p_attachment_counts > 0) THEN
211 l_repair_history_Rec.paramc2 := 'N';
212 end if;
213 -- if previous repair type id is not the same as the new repair type id, display following info to activity tab.
214 If (l_original_repair_type_id <> p_repair_type_id) then
215 OPEN c_repair_type_name(p_original_repair_line_id);
216 FETCH c_repair_type_name INTO
217 l_repair_type_name;
218 IF c_repair_type_name%isopen then
219 CLOSE c_repair_type_name;
220 END IF;
221 l_repair_history_Rec.paramc3 := l_repair_type_name;
222 l_repair_history_Rec.paramn2 := l_original_repair_type_id;
223 end if;
224
225 CSD_REPAIR_HISTORY_PVT.Create_repair_history(
226 P_Api_Version_Number => p_api_version,
227 P_Init_Msg_List => p_init_msg_list,
228 P_Commit => FND_API.G_FALSE,
229 p_validation_level => p_validation_level,
230 P_REPH_REC => l_repair_history_Rec,
231 X_REPAIR_HISTORY_ID => l_repair_history_id,
232 X_Return_Status => x_return_status,
233 X_Msg_Count => x_msg_count,
234 X_Msg_Data => x_msg_data
235 );
236 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
237 IF(l_statement_level >= l_debug_level) THEN
238 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIR_HISTORY_PVT.Create_repair_history','failed');
239 END IF;
240 RAISE FND_API.G_EXC_ERROR;
241 END IF;
242
243 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
244 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
245 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
246 p_api_version_number => 1.0,
247 p_init_msg_list => Fnd_Api.G_FALSE,
248 p_commit => Fnd_Api.G_FALSE,
249 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
250 p_repair_line_id => x_repair_line_id,
251 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
252 x_return_status => l_return_status,
253 x_msg_count => l_msg_count,
254 x_msg_data => l_msg_data
255 );
256 -- ignore return status for now.
257 END IF;
258
259 elsif (p_split_option = 2) then
260
261 if (l_total_original_quantity <= 1) then
262 --need to display message.
263 return;
264 end if;
265
266 --Update the current RO quantity.
267 CSD_REPAIRS_UTIL.Convert_to_Repln_Rec_Type
268 (
269 p_QUANTITY => 1,
270 p_object_version_number => l_rep_line_rec.object_version_number,
271 x_repln_rec => l_original_rep_line_rec
272 );
273
274 CSD_REPAIRS_PVT.Update_Repair_Order
275 (p_API_version_number => 1.0,
276 p_init_msg_list => FND_API.G_TRUE,
277 p_commit => FND_API.G_FALSE,
278 p_validation_level => null,
279 p_repair_line_id => p_original_repair_line_id,
280 p_Repln_Rec => l_original_rep_line_rec,
281 x_return_status => x_return_status,
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data
284 );
285 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
286 IF(l_statement_level >= l_debug_level) THEN
287 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIRS_PVT.Update_Repair_Order','failed');
288 END IF;
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 l_rep_line_rec.repair_number := null;
293 l_rep_line_rec.object_version_number := FND_API.G_MISS_NUM;
294 for i in 1..(l_total_original_quantity-1) loop
295 l_rep_line_rec.quantity := 1;
296
297 Create_New_Repair_Order (
298 p_api_version => 1.0,
299 p_init_msg_list => p_init_msg_list,
300 p_commit => FND_API.G_FALSE,
301 p_validation_level => null,
302 x_return_status => x_return_status,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data,
305 x_repair_line_id => x_repair_line_id,
306 p_copy_attachment => p_copy_attachment,
307 p_original_repair_line_id => p_original_repair_line_id,
308 p_rep_line_rec => l_rep_line_rec
309 );
310 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
311 IF(l_statement_level >= l_debug_level) THEN
312 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order','failed');
313 END IF;
314 RAISE FND_API.G_EXC_ERROR;
315 END IF;
316
317 l_repair_history_Rec.repair_line_id := x_repair_line_id;
318 l_repair_history_Rec.EVENT_CODE := 'SLT';
319 l_repair_history_Rec.EVENT_DATE := sysdate;
320 l_repair_history_Rec.paramn1 := l_total_original_quantity;
321 If (p_attachment_counts > 0) THEN
322 l_repair_history_Rec.paramc2 := 'Y';
323 end if;
324 l_repair_history_Rec.paramc1 := l_original_repair_number;
325 CSD_REPAIR_HISTORY_PVT.Create_repair_history(
326 P_Api_Version_Number => p_api_version,
327 P_Init_Msg_List => p_init_msg_list,
328 P_Commit => FND_API.G_FALSE,
329 p_validation_level => p_validation_level,
330 P_REPH_REC => l_repair_history_Rec,
331 X_REPAIR_HISTORY_ID => l_repair_history_id,
332 X_Return_Status => x_return_status,
333 X_Msg_Count => x_msg_count,
334 X_Msg_Data => x_msg_data
335 );
336 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
337 IF(l_statement_level >= l_debug_level) THEN
338 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIR_HISTORY_PVT.Create_repair_history','failed');
339 END IF;
340 RAISE FND_API.G_EXC_ERROR;
341 END IF;
342
343
344 end loop;
345
346 l_repair_history_Rec.repair_line_id := p_original_repair_line_id;
347 l_repair_history_Rec.EVENT_CODE := 'SLT';
348 l_repair_history_Rec.EVENT_DATE := sysdate;
349 l_repair_history_Rec.paramn1 := l_total_original_quantity;
350 l_repair_history_Rec.paramc2 := null;
351 l_repair_history_Rec.paramc1 := null;
352
353 CSD_REPAIR_HISTORY_PVT.Create_repair_history(
354 P_Api_Version_Number => p_api_version,
355 P_Init_Msg_List => p_init_msg_list,
356 P_Commit => FND_API.G_FALSE,
357 p_validation_level => p_validation_level,
358 P_REPH_REC => l_repair_history_Rec,
359 X_REPAIR_HISTORY_ID => l_repair_history_id,
360 X_Return_Status => x_return_status,
361 X_Msg_Count => x_msg_count,
362 X_Msg_Data => x_msg_data
363 );
364 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
365 IF(l_statement_level >= l_debug_level) THEN
366 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIR_HISTORY_PVT.Create_repair_history','failed');
367 END IF;
368 RAISE FND_API.G_EXC_ERROR;
369 END IF;
370
371 -- swai: 12.1.1 bug 7176940 - check service bulletins after RO creation
372 IF (nvl(fnd_profile.value('CSD_AUTO_CHECK_BULLETINS'),'N') = 'Y') THEN
373 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO(
374 p_api_version_number => 1.0,
375 p_init_msg_list => Fnd_Api.G_FALSE,
376 p_commit => Fnd_Api.G_FALSE,
377 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
378 p_repair_line_id => x_repair_line_id,
379 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
380 x_return_status => l_return_status,
381 x_msg_count => l_msg_count,
382 x_msg_data => l_msg_data
383 );
384 -- ignore return status for now.
385 END IF;
386 end if;
387
388 -- End of API body
389 --
390 -- Standard check of p_commit.
391 IF FND_API.To_Boolean( p_commit ) THEN
392 COMMIT WORK;
393 END IF;
394
395 -- Standard call to get message count and if count is 1, get message info.
396 FND_MSG_PUB.Count_And_Get
397 ( p_count => x_msg_count,
398 p_data => x_msg_data
399 );
400 EXCEPTION
401 WHEN FND_API.G_EXC_ERROR THEN
402 ROLLBACK TO CSD_SPLIT_PKG;
403 x_return_status := FND_API.G_RET_STS_ERROR;
404 FND_MSG_PUB.Count_And_Get
405 ( p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
409 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
410 'CSD.PLSQL.CSD_SPLIT_PKG.Split_Repair_Order',
411 'EXC_ERROR ['||x_msg_data||']');
412 END IF;
413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
414 ROLLBACK TO CSD_SPLIT_PKG;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
416 FND_MSG_PUB.Count_And_Get
417 ( p_count => x_msg_count,
418 p_data => x_msg_data
419 );
420 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
421 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
422 'CSD.PLSQL.CSD_SPLIT_PKG.Split_Repair_Order',
423 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
424 END IF;
425 WHEN OTHERS THEN
426 ROLLBACK TO CSD_SPLIT_PKG;
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
428 IF FND_MSG_PUB.Check_Msg_Level
429 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
430 THEN
431 FND_MSG_PUB.Add_Exc_Msg
432 ( G_PKG_NAME,
433 l_api_name
434 );
435 END IF;
436 FND_MSG_PUB.Count_And_Get
437 ( p_count => x_msg_count,
438 p_data => x_msg_data
439 );
440 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
441 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
442 'CSD.PLSQL.CSD_SPLIT_PKG.Split_Repair_Order',
443 'SQL Message ['||sqlerrm||']');
444 END IF;
445
446 End Split_Repair_Order;
447
448 PROCEDURE Build_Repln_Record (
449 p_repair_line_id IN NUMBER,
450 x_Repln_Rec OUT NOCOPY CSD_REPAIRS_PUB.Repln_Rec_Type,
451 x_return_status OUT NOCOPY VARCHAR2
452 )
453 IS
454
455 CURSOR c_repair_line_dtls(p_repair_line_id IN NUMBER) IS
456 SELECT
457 repair_number
458 ,incident_id
459 ,inventory_item_id
460 ,customer_product_id
461 ,unit_of_measure
462 ,repair_type_id
463 ,resource_id
464 ,instance_id
465 ,project_id
466 ,task_id
467 ,unit_number
468 ,contract_line_id
469 ,quantity
470 ,status
471 ,approval_required_flag
472 ,date_closed
473 ,quantity_in_wip
474 ,approval_status
475 ,quantity_rcvd
476 ,quantity_shipped
477 ,serial_number
478 ,promise_date
479 ,attribute_category
480 ,attribute1
481 ,attribute2
482 ,attribute3
483 ,attribute4
484 ,attribute5
485 ,attribute6
486 ,attribute7
487 ,attribute8
488 ,attribute9
489 ,attribute10
490 ,attribute11
491 ,attribute12
492 ,attribute13
493 ,attribute14
494 ,attribute15
495 ,security_group_id
496 ,order_line_id
497 ,original_source_reference
498 ,status_reason_code
499 ,auto_process_rma
500 ,repair_mode
501 ,item_revision
502 ,repair_group_id
503 ,ro_txn_status
504 ,currency_code
505 ,default_po_num
506 ,original_source_header_id
507 ,original_source_line_id
508 ,price_list_header_id
509 ,problem_description -- swai: bug 4666344
510 ,ro_priority_code -- swai: R12
511 ,resolve_by_date -- rfieldma: 5355051
512 ,object_version_number
513 ,attribute16 --bug#7497907, 12.1 FP, subhat
514 ,attribute17
515 ,attribute18
516 ,attribute19
517 ,attribute20
518 ,attribute21
519 ,attribute22
520 ,attribute23
521 ,attribute24
522 ,attribute25
523 ,attribute26
524 ,attribute27
525 ,attribute28
526 ,attribute29
527 ,attribute30
528 FROM csd_repairs
529 where repair_line_id = p_repair_line_id;
530
531 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
532 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
533 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
534 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
535
536 begin
537
538 -- Initialize API return status to success
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 IF(l_statement_level >= l_debug_level) THEN
542 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Repln_Record','At the Begin in Build_Repln_Record');
543 END IF;
544
545 IF NVL(p_repair_line_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
546
547 OPEN c_repair_line_dtls(p_repair_line_id);
548 FETCH c_repair_line_dtls INTO
549 x_Repln_Rec.REPAIR_NUMBER
550 ,x_Repln_Rec.INCIDENT_ID
551 ,x_Repln_Rec.INVENTORY_ITEM_ID
552 ,x_Repln_Rec.CUSTOMER_PRODUCT_ID
553 ,x_Repln_Rec.UNIT_OF_MEASURE
554 ,x_Repln_Rec.REPAIR_TYPE_ID
555 ,x_Repln_Rec.RESOURCE_ID
556 ,x_Repln_Rec.INSTANCE_ID
557 ,x_Repln_Rec.PROJECT_ID
558 ,x_Repln_Rec.TASK_ID
559 ,x_Repln_Rec.UNIT_NUMBER
560 ,x_Repln_Rec.CONTRACT_LINE_ID
561 ,x_Repln_Rec.QUANTITY
562 ,x_Repln_Rec.STATUS
563 ,x_Repln_Rec.APPROVAL_REQUIRED_FLAG
564 ,x_Repln_Rec.DATE_CLOSED
565 ,x_Repln_Rec.QUANTITY_IN_WIP
566 ,x_Repln_Rec.APPROVAL_STATUS
567 ,x_Repln_Rec.QUANTITY_RCVD
568 ,x_Repln_Rec.QUANTITY_SHIPPED
569 ,x_Repln_Rec.SERIAL_NUMBER
570 ,x_Repln_Rec.PROMISE_DATE
571 ,x_Repln_Rec.ATTRIBUTE_CATEGORY
572 ,x_Repln_Rec.ATTRIBUTE1
573 ,x_Repln_Rec.ATTRIBUTE2
574 ,x_Repln_Rec.ATTRIBUTE3
575 ,x_Repln_Rec.ATTRIBUTE4
576 ,x_Repln_Rec.ATTRIBUTE5
577 ,x_Repln_Rec.ATTRIBUTE6
578 ,x_Repln_Rec.ATTRIBUTE7
579 ,x_Repln_Rec.ATTRIBUTE8
580 ,x_Repln_Rec.ATTRIBUTE9
581 ,x_Repln_Rec.ATTRIBUTE10
582 ,x_Repln_Rec.ATTRIBUTE11
583 ,x_Repln_Rec.ATTRIBUTE12
584 ,x_Repln_Rec.ATTRIBUTE13
585 ,x_Repln_Rec.ATTRIBUTE14
586 ,x_Repln_Rec.ATTRIBUTE15
587 ,x_Repln_Rec.REPAIR_GROUP_ID
588 ,x_Repln_Rec.ORDER_LINE_ID
589 ,x_Repln_Rec.ORIGINAL_SOURCE_REFERENCE
590 ,x_Repln_Rec.STATUS_REASON_CODE
591 ,x_Repln_Rec.AUTO_PROCESS_RMA
592 ,x_Repln_Rec.REPAIR_MODE
593 ,x_Repln_Rec.ITEM_REVISION
594 ,x_Repln_Rec.REPAIR_GROUP_ID
595 ,x_Repln_Rec.RO_TXN_STATUS
596 ,x_Repln_Rec.CURRENCY_CODE
597 ,x_Repln_Rec.DEFAULT_PO_NUM
598 ,x_Repln_Rec.ORIGINAL_SOURCE_HEADER_ID
599 ,x_Repln_Rec.ORIGINAL_SOURCE_LINE_ID
600 ,x_Repln_Rec.PRICE_LIST_HEADER_ID
601 ,x_Repln_Rec.PROBLEM_DESCRIPTION -- swai: bug 4666344
602 ,x_Repln_Rec.RO_PRIORITY_CODE -- swai: R12
603 ,x_Repln_Rec.RESOLVE_BY_DATE -- rfieldma: 5355051
604 ,x_Repln_Rec.OBJECT_VERSION_NUMBER
605 ,x_Repln_Rec.ATTRIBUTE16 --bug#7497907, 12.1 FP, subhat
606 ,x_Repln_Rec.ATTRIBUTE17
607 ,x_Repln_Rec.ATTRIBUTE18
608 ,x_Repln_Rec.ATTRIBUTE19
609 ,x_Repln_Rec.ATTRIBUTE20
610 ,x_Repln_Rec.ATTRIBUTE21
611 ,x_Repln_Rec.ATTRIBUTE22
612 ,x_Repln_Rec.ATTRIBUTE23
613 ,x_Repln_Rec.ATTRIBUTE24
614 ,x_Repln_Rec.ATTRIBUTE25
615 ,x_Repln_Rec.ATTRIBUTE26
616 ,x_Repln_Rec.ATTRIBUTE27
617 ,x_Repln_Rec.ATTRIBUTE28
618 ,x_Repln_Rec.ATTRIBUTE29
619 ,x_Repln_Rec.ATTRIBUTE30
620 ;
621
622 IF c_repair_line_dtls%notfound then
623 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
624 FND_MESSAGE.SET_TOKEN('REPAIR_LINE_ID',p_repair_line_id);
625 FND_MSG_PUB.ADD;
626 RAISE FND_API.G_EXC_ERROR;
627 END IF;
628
629 IF c_repair_line_dtls%isopen then
630 CLOSE c_repair_line_dtls;
631 END IF;
632
633 END IF;
634
635 EXCEPTION
636 WHEN FND_API.G_EXC_ERROR THEN
637 ROLLBACK TO CSD_SPLIT_PKG;
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
640 ROLLBACK TO CSD_SPLIT_PKG;
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
642 WHEN OTHERS THEN
643 ROLLBACK TO CSD_SPLIT_PKG;
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
645 End Build_Repln_Record;
646
647
648 PROCEDURE Build_Product_TXN_Record (
649 p_product_txn_id IN NUMBER,
650 x_product_txn_Rec OUT NOCOPY CSD_PROCESS_PVT.PRODUCT_TXN_REC,
651 x_return_status OUT NOCOPY VARCHAR2
652 )
653 IS
654
655 CURSOR c_product_txn_line_dtls(p_product_txn_id IN NUMBER) IS
656 SELECT
657 estimate_detail_id
658 ,action_type
659 ,action_code
660 ,interface_to_om_flag
661 ,book_sales_order_flag
662 ,release_sales_order_flag
663 ,ship_sales_order_flag
664 ,sub_inventory
665 ,lot_number
666 ,context
667 ,attribute1
668 ,attribute2
669 ,attribute3
670 ,attribute4
671 ,attribute5
672 ,attribute6
673 ,attribute7
674 ,attribute8
675 ,attribute9
676 ,attribute10
677 ,attribute11
678 ,attribute12
679 ,attribute13
680 ,attribute14
681 ,attribute15
682 ,prod_txn_status
683 ,prod_txn_code
684 --,shipped_serial_number This column is not used in 11.5.10
685 ,object_version_number
686 ,req_header_id
687 ,req_line_id
688 ,order_header_id
689 ,order_line_id
690 ,quantity_received
691 ,quantity_shipped
692 ,source_serial_number
693 ,source_instance_id
694 ,non_source_serial_number
695 ,non_source_instance_id
696 ,locator_id
697 ,sub_inventory_rcvd
698 ,lot_number_rcvd
699 ,picking_rule_id -- Add for R12 pickrule id change.Vijay.
700 ,project_id
701 ,task_id
702 ,unit_number
703 ,internal_po_header_id -- swai: bug 6148019
704 FROM csd_product_transactions
705 where product_transaction_id = p_product_txn_id;
706
707 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
708 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
709 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
710 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
711
712 begin
713
714 -- Initialize API return status to success
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716
717 IF NVL(p_product_txn_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
718 OPEN c_product_txn_line_dtls(p_product_txn_id);
719 FETCH c_product_txn_line_dtls INTO
720 x_product_txn_Rec.estimate_detail_id
721 ,x_product_txn_Rec.action_type
722 ,x_product_txn_Rec.action_code
723 ,x_product_txn_Rec.interface_to_om_flag
724 ,x_product_txn_Rec.book_sales_order_flag
725 ,x_product_txn_Rec.release_sales_order_flag
726 ,x_product_txn_Rec.ship_sales_order_flag
727 ,x_product_txn_Rec.sub_inventory
728 ,x_product_txn_Rec.lot_number
729 ,x_product_txn_Rec.context
730 ,x_product_txn_Rec.attribute1
731 ,x_product_txn_Rec.attribute2
732 ,x_product_txn_Rec.attribute3
733 ,x_product_txn_Rec.attribute4
734 ,x_product_txn_Rec.attribute5
735 ,x_product_txn_Rec.attribute6
736 ,x_product_txn_Rec.attribute7
737 ,x_product_txn_Rec.attribute8
738 ,x_product_txn_Rec.attribute9
739 ,x_product_txn_Rec.attribute10
740 ,x_product_txn_Rec.attribute11
741 ,x_product_txn_Rec.attribute12
742 ,x_product_txn_Rec.attribute13
743 ,x_product_txn_Rec.attribute14
744 ,x_product_txn_Rec.attribute15
745 ,x_product_txn_Rec.prod_txn_status
746 ,x_product_txn_Rec.prod_txn_code
747 -- ,x_product_txn_Rec.shipped_serial_number this column is not used in 11.5.10
748 ,x_product_txn_Rec.object_version_number
749 ,x_product_txn_Rec.req_header_id
750 ,x_product_txn_Rec.req_line_id
751 ,x_product_txn_Rec.order_header_id
752 ,x_product_txn_Rec.order_line_id
753 ,x_product_txn_Rec.prd_txn_qty_received
754 ,x_product_txn_Rec.prd_txn_qty_shipped
755 ,x_product_txn_Rec.source_serial_number
756 ,x_product_txn_Rec.source_instance_id
757 ,x_product_txn_Rec.non_source_serial_number
758 ,x_product_txn_Rec.non_source_instance_id
759 ,x_product_txn_Rec.locator_id
760 ,x_product_txn_Rec.sub_inventory_rcvd
761 ,x_product_txn_Rec.lot_number_rcvd
762 ,x_product_txn_Rec.picking_rule_id -- Add for R12 pickrule id change.Vijay.
763 ,x_product_txn_Rec.project_id
764 ,x_product_txn_Rec.task_id
765 ,x_product_txn_Rec.unit_number
766 ,x_product_txn_Rec.internal_po_header_id -- swai: bug 6148019
767 ;
768
769 IF c_product_txn_line_dtls%notfound then
770 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
771 FND_MESSAGE.SET_TOKEN('p_product_txn_id',p_product_txn_id);
772 FND_MSG_PUB.ADD;
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775
776 IF c_product_txn_line_dtls%isopen then
777 CLOSE c_product_txn_line_dtls;
778 END IF;
779
780 END IF;
781
782 -- Standard call to get message count and if count is 1, get message info.
783 EXCEPTION
784 WHEN FND_API.G_EXC_ERROR THEN
785 ROLLBACK TO CSD_SPLIT_PKG;
786 x_return_status := FND_API.G_RET_STS_ERROR;
787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
788 ROLLBACK TO CSD_SPLIT_PKG;
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
790 WHEN OTHERS THEN
791 ROLLBACK TO CSD_SPLIT_PKG;
792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
793 End Build_Product_TXN_Record;
794
795
796 PROCEDURE Set_Error_Message (
797 p_msg_code IN VARCHAR2,
798 x_return_status OUT NOCOPY VARCHAR2,
799 x_msg_count OUT NOCOPY NUMBER,
800 x_msg_data OUT NOCOPY VARCHAR2
801 )
802
803 IS
804
805 begin
806 -- Initialize API return status to success
807 x_return_status := FND_API.G_RET_STS_SUCCESS;
808
809 -- Initialize message list if p_init_msg_list is set to TRUE.
810 IF FND_API.to_Boolean( fnd_api.g_true ) THEN
811 FND_MSG_PUB.initialize;
812 END IF;
813
814 IF (p_msg_code is not null) ThEN
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 FND_MESSAGE.SET_NAME('CSD',p_msg_code);
817 FND_MSG_PUB.ADD;
818 END IF;
819
820 FND_MSG_PUB.Count_And_Get
821 ( p_count => x_msg_count,
822 p_data => x_msg_data
823 );
824
825 End Set_Error_Message;
826
827
828 PROCEDURE Is_Split_Repair_Order_Allow (
829 p_repair_line_id IN NUMBER,
830 x_return_status OUT NOCOPY VARCHAR2,
831 x_msg_count OUT NOCOPY NUMBER,
832 x_msg_data OUT NOCOPY VARCHAR2
833 )
834
835 IS
836 l_api_name CONSTANT VARCHAR2(30) := 'Is_Split_Repair_Order_Allow' ;
837 l_repair_quantity NUMBER;
838 l_number_product_txn_lines NUMBER;
839 l_total_quantity_rcvd NUMBER;
840 l_total_quantity_in_wip NUMBER;
841 l_repair_mode VARCHAR2(10);
842 l_repair_type_ref VARCHAR2(10);
843 l_wip_job_count NUMBER;
844
845 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
846 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
847 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
848 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
849
850 CURSOR c_rma_product_txn_line_info(p_repair_line_id IN NUMBER) IS
851 SELECT cptv.action_type, cptv.interface_to_om_flag, cptv.repair_quantity, cptv.quantity_in_wip, cptv.quantity_rcvd, cptv.status,
852 cptv.serial_number_control_code, crtv.repair_type_ref, msiv.comms_nl_trackable_flag, crtv.repair_mode
853 FROM csd_product_txns_v cptv, csd_repair_types_vl crtv, mtl_system_items_vl msiv
854 WHERE repair_line_id = p_repair_line_id and crtv.repair_type_id = cptv.repair_type_id and action_type = 'RMA'
855 and msiv.inventory_item_id = cptv.inventory_item_id and msiv.organization_id = cs_std.get_item_valdn_orgzn_id;
856
857
858 CURSOR c_ship_product_txn_line_info(p_repair_line_id IN NUMBER) IS
859 SELECT interface_to_om_flag
860 FROM csd_product_txns_v
861 WHERE repair_line_id = p_repair_line_id and action_type = 'SHIP';
862
863 CURSOR c_repair_type_ref(p_repair_line_id IN NUMBER) IS
864 SELECT crtv.repair_type_ref
865 FROM csd_repairs_v crv, csd_repair_types_vl crtv
866 WHERE repair_line_id = p_repair_line_id and crv.repair_type_id = crtv.repair_type_id;
867
868 CURSOR c_wip_job(p_repair_line_id IN NUMBER) IS
869 SELECT count(*)
870 FROM csd_repair_job_xref
871 WHERE repair_line_id = p_repair_line_id;
872
873
874
875 begin
876 -- Initialize API return status to success
877 x_return_status := FND_API.G_RET_STS_SUCCESS;
878 l_number_product_txn_lines := 0;
879 l_total_quantity_rcvd := 0;
880 l_total_quantity_in_wip := 0;
881 l_repair_mode := null;
882
883 -- Initialize message list if p_init_msg_list is set to TRUE.
884 IF FND_API.to_Boolean( fnd_api.g_true ) THEN
885 FND_MSG_PUB.initialize;
886 END IF;
887
888 IF (p_repair_line_id is null) THEN
889 x_return_status := FND_API.G_RET_STS_ERROR;
890 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_WITHOUT_RO_NUM');
891 FND_MSG_PUB.ADD;
892 FND_MSG_PUB.Count_And_Get
893 ( p_count => x_msg_count,
894 p_data => x_msg_data
895 );
896 return;
897 END IF;
898 --taklam
899 -- return;
900
901 OPEN c_repair_type_ref(p_repair_line_id);
902 FETCH c_repair_type_ref INTO l_repair_type_ref;
903 IF c_repair_type_ref%isopen then
904 CLOSE c_repair_type_ref;
905 END IF;
906
907 IF l_repair_type_ref not in ('SR', 'RR') then
908 x_return_status := FND_API.G_RET_STS_ERROR;
909 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_INCORRECT_REF');
910 FND_MSG_PUB.ADD;
911 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
912 FND_MSG_PUB.Count_And_Get
913 ( p_count => x_msg_count,
914 p_data => x_msg_data
915 );
916 return;
917 end if;
918 END IF;
919
920 OPEN c_wip_job(p_repair_line_id);
921 FETCH c_wip_job INTO l_wip_job_count;
922 IF c_wip_job%isopen then
923 CLOSE c_wip_job;
924 END IF;
925
926 FOR P in c_rma_product_txn_line_info(p_repair_line_id)
927 loop
928 l_number_product_txn_lines := l_number_product_txn_lines + 1;
929 l_repair_mode := P.repair_mode;
930 l_repair_quantity := P.repair_quantity;
931 l_total_quantity_rcvd := l_total_quantity_rcvd + P.quantity_rcvd;
932 l_total_quantity_in_wip := l_total_quantity_in_wip + P.quantity_in_wip;
933 if P.status <> 'O' then
934 x_return_status := FND_API.G_RET_STS_ERROR;
935 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_WRONG_STATUS');
936 FND_MSG_PUB.ADD;
937 elsif P.serial_number_control_code <> 1 then
938 x_return_status := FND_API.G_RET_STS_ERROR;
939 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_PROD_SERIALIZED');
940 FND_MSG_PUB.ADD;
941 elsif P.repair_type_ref not in ('SR', 'RR') then
942 x_return_status := FND_API.G_RET_STS_ERROR;
943 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_INCORRECT_REF');
944 FND_MSG_PUB.ADD;
945 elsif P.comms_nl_trackable_flag = 'Y' then
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_PROD_IB_TRBLE');
948 FND_MSG_PUB.ADD;
949 end if;
950 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
951 FND_MSG_PUB.Count_And_Get
952 ( p_count => x_msg_count,
953 p_data => x_msg_data
954 );
955 return;
956 end if;
957 end loop;
958
959 IF c_rma_product_txn_line_info%isopen then
960 CLOSE c_rma_product_txn_line_info;
961 END IF;
962
963 if (l_number_product_txn_lines < 1) then
964 x_return_status := FND_API.G_RET_STS_ERROR;
965 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_NO_LOGISTIC_LINE');
966 FND_MSG_PUB.ADD;
967 elsif (l_repair_quantity > l_total_quantity_rcvd) then
968 x_return_status := FND_API.G_RET_STS_ERROR;
969 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_ALL_QTYS_NOT_REC');
970 FND_MSG_PUB.ADD;
971 elsif ((l_repair_quantity > l_total_quantity_in_wip) and (l_repair_mode = 'WIP') and (l_wip_job_count > 0)) then
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_JOB_INCOMPLETED');
974 FND_MSG_PUB.ADD;
975 end if;
976
977 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
978 FND_MSG_PUB.Count_And_Get
979 ( p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982 return;
983 end if;
984
985 FOR S in c_ship_product_txn_line_info(p_repair_line_id)
986 loop
987 if S.interface_to_om_flag = 'Y' then
988 x_return_status := FND_API.G_RET_STS_ERROR;
989 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_LINE_INTERFACED');
990 FND_MSG_PUB.ADD;
991 end if;
992 end loop;
993 IF c_ship_product_txn_line_info%isopen then
994 CLOSE c_ship_product_txn_line_info;
995 END IF;
996
997 FND_MSG_PUB.Count_And_Get
998 ( p_count => x_msg_count,
999 p_data => x_msg_data
1000 );
1001
1002 EXCEPTION
1003 WHEN FND_API.G_EXC_ERROR THEN
1004 ROLLBACK TO CSD_SPLIT_PKG;
1005 x_return_status := FND_API.G_RET_STS_ERROR;
1006 FND_MSG_PUB.Count_And_Get
1007 ( p_count => x_msg_count,
1008 p_data => x_msg_data
1009 );
1010 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1011 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1012 'CSD.PLSQL.CSD_SPLIT_PKG.Is_Split_Repair_Order_Allow',
1013 'EXC_ERROR ['||x_msg_data||']');
1014 END IF;
1015 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1016 ROLLBACK TO CSD_SPLIT_PKG;
1017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1018 FND_MSG_PUB.Count_And_Get
1019 ( p_count => x_msg_count,
1020 p_data => x_msg_data
1021 );
1022 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1023 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1024 'CSD.PLSQL.CSD_SPLIT_PKG.Is_Split_Repair_Order_Allow',
1025 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1026 END IF;
1027 WHEN OTHERS THEN
1028 ROLLBACK TO CSD_SPLIT_PKG;
1029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1030 IF FND_MSG_PUB.Check_Msg_Level
1031 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1032 THEN
1033 FND_MSG_PUB.Add_Exc_Msg
1034 ( G_PKG_NAME,
1035 l_api_name
1036 );
1037 END IF;
1038 FND_MSG_PUB.Count_And_Get
1039 ( p_count => x_msg_count,
1040 p_data => x_msg_data
1041 );
1042 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1043 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1044 'CSD.PLSQL.CSD_SPLIT_PKG.Is_Split_Repair_Order_Allow',
1045 'SQL Message ['||sqlerrm||']');
1046 END IF;
1047
1048 End Is_Split_Repair_Order_Allow;
1049
1050
1051 PROCEDURE Create_New_Repair_Order (
1052 p_api_version IN NUMBER,
1053 p_init_msg_list IN VARCHAR2,
1054 p_commit IN VARCHAR2,
1055 p_validation_level IN NUMBER,
1056 x_return_status OUT NOCOPY VARCHAR2,
1057 x_msg_count OUT NOCOPY NUMBER,
1058 x_msg_data OUT NOCOPY VARCHAR2,
1059 x_repair_line_id OUT NOCOPY NUMBER,
1060 p_copy_attachment IN VARCHAR2,
1061 p_original_repair_line_id IN NUMBER,
1062 p_rep_line_rec IN CSD_REPAIRS_PUB.REPLN_REC_TYPE
1063 ) IS
1064
1065 l_api_name CONSTANT VARCHAR2(30) := 'Create_New_Repair_Order' ;
1066 l_original_product_txn_rec CSD_PROCESS_PVT.PRODUCT_TXN_REC;
1067 l_repair_line_id NUMBER;
1068 l_repair_number NUMBER;
1069 x_ship_prod_txn_tbl CSD_PROCESS_PVT.PRODUCT_TXN_TBL;
1070
1071 CURSOR c_rma_product_txns_id(p_original_repair_line_id IN NUMBER) IS
1072 SELECT product_transaction_id
1073 FROM csd_product_txns_v
1074 WHERE repair_line_id = p_original_repair_line_id and ACTION_TYPE = 'RMA';
1075
1076 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1077 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1078 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1079 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1080
1081 Begin
1082
1083
1084 -- Initialize message list if p_init_msg_list is set to TRUE.
1085 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1086 FND_MSG_PUB.initialize;
1087 END IF;
1088
1089 -- Initialize API return status to success
1090 x_return_status := FND_API.G_RET_STS_SUCCESS;
1091
1092 --
1093 -- API body
1094 --
1095 -- Local Procedure
1096
1097 CSD_REPAIRS_PVT.Create_Repair_Order
1098 (p_API_version_number => 1.0,
1099 p_init_msg_list => p_init_msg_list,
1100 p_commit => p_commit,
1101 p_validation_level => null,
1102 p_repair_line_id => null,
1103 p_Repln_Rec => p_rep_line_rec,
1104 x_repair_line_id => l_repair_line_id,
1105 x_repair_number => l_repair_number,
1106 x_return_status => x_return_status,
1107 x_msg_count => x_msg_count,
1108 x_msg_data => x_msg_data
1109 );
1110 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1111 IF(l_statement_level >= l_debug_level) THEN
1112 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIRS_PVT.Create_Repair_Order','failed');
1113 END IF;
1114 RAISE FND_API.G_EXC_ERROR;
1115 END IF;
1116
1117 x_repair_line_id := l_repair_line_id;
1118
1119 FOR P in c_rma_product_txns_id(p_original_repair_line_id)
1120
1121 loop
1122
1123 Build_Product_TXN_Record (
1124 p_product_txn_id => P.product_transaction_id,
1125 x_product_txn_Rec => l_original_product_txn_rec,
1126 x_return_status => x_return_status
1127 );
1128 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1129 IF(l_statement_level >= l_debug_level) THEN
1130 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Product_TXN_Record','failed');
1131 END IF;
1132 RAISE FND_API.G_EXC_ERROR;
1133 END IF;
1134
1135
1136 l_original_product_txn_rec.PRODUCT_TRANSACTION_ID := FND_API.G_MISS_NUM;
1137 CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
1138 ( px_PRODUCT_TRANSACTION_ID => l_original_product_txn_rec.PRODUCT_TRANSACTION_ID,
1139 p_REPAIR_LINE_ID => l_repair_line_id,
1140 p_ESTIMATE_DETAIL_ID => l_original_product_txn_rec.estimate_detail_id,
1141 p_ACTION_TYPE => l_original_product_txn_rec.ACTION_TYPE,
1142 p_ACTION_CODE => l_original_product_txn_rec.ACTION_CODE,
1143 p_LOT_NUMBER => l_original_product_txn_rec.LOT_NUMBER,
1144 p_SUB_INVENTORY => l_original_product_txn_rec.SUB_INVENTORY,
1145 p_INTERFACE_TO_OM_FLAG => l_original_product_txn_rec.INTERFACE_TO_OM_FLAG,
1146 p_BOOK_SALES_ORDER_FLAG => l_original_product_txn_rec.BOOK_SALES_ORDER_FLAG,
1147 p_RELEASE_SALES_ORDER_FLAG => l_original_product_txn_rec.RELEASE_SALES_ORDER_FLAG,
1148 p_SHIP_SALES_ORDER_FLAG => l_original_product_txn_rec.SHIP_SALES_ORDER_FLAG ,
1149 p_PROD_TXN_STATUS => l_original_product_txn_rec.PROD_TXN_STATUS,
1150 p_PROD_TXN_CODE => l_original_product_txn_rec.PROD_TXN_CODE,
1151 p_LAST_UPDATE_DATE => SYSDATE,
1152 p_CREATION_DATE => SYSDATE,
1153 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1154 p_CREATED_BY => FND_GLOBAL.USER_ID,
1155 p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID,
1156 p_ATTRIBUTE1 => l_original_product_txn_rec.ATTRIBUTE1,
1157 p_ATTRIBUTE2 => l_original_product_txn_rec.ATTRIBUTE2,
1158 p_ATTRIBUTE3 => l_original_product_txn_rec.ATTRIBUTE3,
1159 p_ATTRIBUTE4 => l_original_product_txn_rec.ATTRIBUTE4,
1160 p_ATTRIBUTE5 => l_original_product_txn_rec.ATTRIBUTE5,
1161 p_ATTRIBUTE6 => l_original_product_txn_rec.ATTRIBUTE6,
1162 p_ATTRIBUTE7 => l_original_product_txn_rec.ATTRIBUTE7,
1163 p_ATTRIBUTE8 => l_original_product_txn_rec.ATTRIBUTE8,
1164 p_ATTRIBUTE9 => l_original_product_txn_rec.ATTRIBUTE9,
1165 p_ATTRIBUTE10 => l_original_product_txn_rec.ATTRIBUTE10,
1166 p_ATTRIBUTE11 => l_original_product_txn_rec.ATTRIBUTE11,
1167 p_ATTRIBUTE12 => l_original_product_txn_rec.ATTRIBUTE12,
1168 p_ATTRIBUTE13 => l_original_product_txn_rec.ATTRIBUTE13,
1169 p_ATTRIBUTE14 => l_original_product_txn_rec.ATTRIBUTE14,
1170 p_ATTRIBUTE15 => l_original_product_txn_rec.ATTRIBUTE15,
1171 p_CONTEXT => l_original_product_txn_rec.CONTEXT ,
1172 p_OBJECT_VERSION_NUMBER => 1,
1173 -- p_SHIPPED_SERIAL_NUMBER => l_original_product_txn_rec.SHIPPED_SERIAL_NUMBER
1174 p_Req_Header_Id => l_original_product_txn_rec.Req_Header_Id,
1175 p_Req_Line_Id => l_original_product_txn_rec.Req_Line_Id,
1176 p_Order_Header_Id => l_original_product_txn_rec.Order_Header_Id,
1177 p_Order_Line_Id => l_original_product_txn_rec.Order_Line_Id,
1178 p_Prd_txn_Qty_Received => l_original_product_txn_rec.Prd_Txn_Qty_Received,
1179 p_Prd_Txn_Qty_Shipped => l_original_product_txn_rec.Prd_Txn_Qty_Shipped,
1180 p_Source_Serial_Number => l_original_product_txn_rec.Source_Serial_Number,
1181 p_Source_Instance_Id => l_original_product_txn_rec.Source_Instance_Id,
1182 p_Non_Source_Serial_Number => l_original_product_txn_rec.Non_Source_Serial_Number,
1183 p_Non_Source_Instance_Id => l_original_product_txn_rec.Non_Source_Instance_Id,
1184 p_Locator_Id => l_original_product_txn_rec.Locator_Id,
1185 p_Sub_Inventory_Rcvd => l_original_product_txn_rec.Sub_Inventory_Rcvd,
1186 p_Lot_Number_Rcvd => l_original_product_txn_rec.Lot_Number_Rcvd,
1187 p_picking_rule_id => l_original_product_txn_rec.picking_rule_id, -- Add for R12 pickrule id change.Vijay.
1188 p_project_id => l_original_product_txn_rec.project_id,
1189 p_task_id => l_original_product_txn_rec.task_id,
1190 p_unit_number => l_original_product_txn_rec.unit_number,
1191 p_internal_po_header_id => l_original_product_txn_rec.internal_po_header_id -- swai: bug 6148019
1192 );
1193 end loop;
1194
1195 IF c_rma_product_txns_id%isopen then
1196 CLOSE c_rma_product_txns_id;
1197 END IF;
1198
1199
1200 Build_Ship_Prod_Txn_Tbl
1201 ( p_repair_line_id => l_repair_line_id,
1202 x_prod_txn_tbl => x_ship_prod_txn_tbl,
1203 x_return_status => x_return_status );
1204
1205 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1206 IF(l_statement_level >= l_debug_level) THEN
1207 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Ship_Prod_Txn_Tbl','failed');
1208 END IF;
1209 RAISE FND_API.G_EXC_ERROR;
1210 END IF;
1211
1212
1213 IF x_ship_prod_txn_tbl.COUNT > 0 THEN
1214 FOR i IN x_ship_prod_txn_tbl.first..x_ship_prod_txn_tbl.last
1215 LOOP
1216
1217 CSD_PROCESS_PVT.CREATE_PRODUCT_TXN
1218 (p_api_version => 1.0 ,
1219 p_commit => fnd_api.g_false,
1220 p_init_msg_list => 'F',
1221 p_validation_level => fnd_api.g_valid_level_full,
1222 x_product_txn_rec => x_ship_prod_txn_tbl(i),
1223 x_return_status => x_return_status,
1224 x_msg_count => x_msg_count,
1225 x_msg_data => x_msg_data );
1226
1227 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1228 IF(l_statement_level >= l_debug_level) THEN
1229 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_PROCESS_UTIL.CREATE_PRODUCT_TXN','failed');
1230 END IF;
1231 RAISE FND_API.G_EXC_ERROR;
1232 END IF;
1233 END LOOP;
1234 END IF;
1235
1236 Copy_Repair_History (
1237 p_api_version => 1.0,
1238 p_init_msg_list => p_init_msg_list,
1239 p_commit => p_commit,
1240 p_validation_level => p_validation_level,
1241 x_return_status => x_return_status,
1242 x_msg_count => x_msg_count,
1243 x_msg_data => x_msg_data,
1244 p_original_repair_line_id => p_original_repair_line_id,
1245 p_new_repair_line_id => l_repair_line_id);
1246
1247 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1248 IF(l_statement_level >= l_debug_level) THEN
1249 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History','failed');
1250 END IF;
1251 RAISE FND_API.G_EXC_ERROR;
1252 END IF;
1253
1254 Copy_JTF_Notes (
1255 p_api_version => 1.0,
1256 p_init_msg_list => p_init_msg_list,
1257 p_commit => p_commit,
1258 p_validation_level => p_validation_level,
1259 x_return_status => x_return_status,
1260 x_msg_count => x_msg_count,
1261 x_msg_data => x_msg_data,
1262 p_original_repair_line_id => p_original_repair_line_id,
1263 p_new_repair_line_id => l_repair_line_id);
1264
1265 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1266 IF(l_statement_level >= l_debug_level) THEN
1267 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes','failed');
1268 END IF;
1269 RAISE FND_API.G_EXC_ERROR;
1270 END IF;
1271
1272 If (p_copy_attachment = 'Y') THEN
1273 CSD_REPAIRS_PVT.Copy_Attachments
1274 ( p_api_version => 1.0,
1275 p_commit => p_commit,
1276 p_init_msg_list => p_init_msg_list,
1277 p_validation_level => p_validation_level,
1278 p_original_ro_id => p_original_repair_line_id,
1279 p_new_ro_id => l_repair_line_id,
1280 x_return_status => x_return_status,
1281 x_msg_count => x_msg_count,
1282 x_msg_data => x_msg_data);
1283
1284 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1285 IF(l_statement_level >= l_debug_level) THEN
1286 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIRS_PVT.Copy_Attachments','failed');
1287 END IF;
1288 RAISE FND_API.G_EXC_ERROR;
1289 END IF;
1290 END if;
1291
1292 -- End of API body
1293 --
1294 -- Standard check of p_commit.
1295 IF FND_API.To_Boolean( p_commit ) THEN
1296 COMMIT WORK;
1297 END IF;
1298
1299 -- Standard call to get message count and if count is 1, get message info.
1300 FND_MSG_PUB.Count_And_Get
1301 ( p_count => x_msg_count,
1302 p_data => x_msg_data
1303 );
1304
1305 EXCEPTION
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 ROLLBACK TO CSD_SPLIT_PKG;
1308 x_return_status := FND_API.G_RET_STS_ERROR;
1309 FND_MSG_PUB.Count_And_Get
1310 ( p_count => x_msg_count,
1311 p_data => x_msg_data
1312 );
1313 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1314 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1315 'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order',
1316 'EXC_ERROR ['||x_msg_data||']');
1317 END IF;
1318 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1319 ROLLBACK TO CSD_SPLIT_PKG;
1320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1321 FND_MSG_PUB.Count_And_Get
1322 ( p_count => x_msg_count,
1323 p_data => x_msg_data
1324 );
1325 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1326 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1327 'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order',
1328 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1329 END IF;
1330 WHEN OTHERS THEN
1331 ROLLBACK TO CSD_SPLIT_PKG;
1332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1333 IF FND_MSG_PUB.Check_Msg_Level
1334 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1335 THEN
1336 FND_MSG_PUB.Add_Exc_Msg
1337 ( G_PKG_NAME,
1338 l_api_name
1339 );
1340 END IF;
1341 FND_MSG_PUB.Count_And_Get
1342 ( p_count => x_msg_count,
1343 p_data => x_msg_data
1344 );
1345 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1346 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1347 'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order',
1348 'SQL Message ['||sqlerrm||']');
1349 END IF;
1350
1351 End Create_New_Repair_Order;
1352
1353
1354 PROCEDURE Copy_Repair_History (
1355 p_api_version IN NUMBER,
1356 p_init_msg_list IN VARCHAR2,
1357 p_commit IN VARCHAR2,
1358 p_validation_level IN NUMBER,
1359 x_return_status OUT NOCOPY VARCHAR2,
1360 x_msg_count OUT NOCOPY NUMBER,
1361 x_msg_data OUT NOCOPY VARCHAR2,
1362 p_original_repair_line_id IN NUMBER,
1363 p_new_repair_line_id IN NUMBER
1364 ) IS
1365
1366
1367 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Repair_History' ;
1368 l_repair_history_rec CSD_REPAIR_HISTORY_PVT.REPH_Rec_Type;
1369 l_repair_history_id NUMBER;
1370 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1371 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1372 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1373 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1374
1375 CURSOR c_repair_history_id (p_original_repair_line_id IN NUMBER) IS
1376 SELECT repair_history_id
1377 FROM CSD_REPAIR_HISTORY
1378 WHERE repair_line_id = p_original_repair_line_id;
1379
1380 Begin
1381
1382
1383 -- Initialize message list if p_init_msg_list is set to TRUE.
1384 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1385 FND_MSG_PUB.initialize;
1386 END IF;
1387
1388 -- Initialize API return status to success
1389 x_return_status := FND_API.G_RET_STS_SUCCESS;
1390
1391 --
1392 -- API body
1393 --
1394 -- Local Procedure
1395
1396
1397 FOR C in c_repair_history_id(p_original_repair_line_id)
1398 loop
1399
1400 Build_Repair_History_Record (
1401 p_original_repair_history_id => C.repair_history_id,
1402 x_repair_history_Rec => l_repair_history_rec,
1403 x_return_status => x_return_status
1404 );
1405 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1406 IF(l_statement_level >= l_debug_level) THEN
1407 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Repair_History_Record','failed');
1408 END IF;
1409 RAISE FND_API.G_EXC_ERROR;
1410 END IF;
1411
1412
1413 l_repair_history_rec.repair_line_id := p_new_repair_line_id;
1414
1415 CSD_REPAIR_HISTORY_PVT.Create_repair_history(
1416 P_Api_Version_Number => p_api_version,
1417 P_Init_Msg_List => p_init_msg_list,
1418 P_Commit => p_commit,
1419 p_validation_level => p_validation_level,
1420 P_reph_rec => l_repair_history_rec,
1421 X_REPAIR_HISTORY_ID => l_repair_history_id,
1422 X_Return_Status => x_return_status,
1423 X_Msg_Count => x_msg_count,
1424 X_Msg_Data => x_msg_data
1425 );
1426 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1427 IF(l_statement_level >= l_debug_level) THEN
1428 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIR_HISTORY_PVT.Create_repair_history','failed');
1429 END IF;
1430 RAISE FND_API.G_EXC_ERROR;
1431 END IF;
1432
1433 end loop;
1434 IF c_repair_history_id%isopen then
1435 CLOSE c_repair_history_id;
1436 END IF;
1437
1438
1439 -- End of API body
1440 --
1441 -- Standard check of p_commit.
1442 IF FND_API.To_Boolean( p_commit ) THEN
1443 COMMIT WORK;
1444 END IF;
1445
1446 -- Standard call to get message count and if count is 1, get message info.
1447 FND_MSG_PUB.Count_And_Get
1448 ( p_count => x_msg_count,
1449 p_data => x_msg_data
1450 );
1451 EXCEPTION
1452 WHEN FND_API.G_EXC_ERROR THEN
1453 ROLLBACK TO CSD_SPLIT_PKG;
1454 x_return_status := FND_API.G_RET_STS_ERROR;
1455 FND_MSG_PUB.Count_And_Get
1456 ( p_count => x_msg_count,
1457 p_data => x_msg_data
1458 );
1459 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1460 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1461 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History',
1462 'EXC_ERROR ['||x_msg_data||']');
1463 END IF;
1464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1465 ROLLBACK TO CSD_SPLIT_PKG;
1466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1467 FND_MSG_PUB.Count_And_Get
1468 ( p_count => x_msg_count,
1469 p_data => x_msg_data
1470 );
1471 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1472 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1473 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History',
1474 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1475 END IF;
1476 WHEN OTHERS THEN
1477 ROLLBACK TO CSD_SPLIT_PKG;
1478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1479 IF FND_MSG_PUB.Check_Msg_Level
1480 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1481 THEN
1482 FND_MSG_PUB.Add_Exc_Msg
1483 ( G_PKG_NAME,
1484 l_api_name
1485 );
1486 END IF;
1487 FND_MSG_PUB.Count_And_Get
1488 ( p_count => x_msg_count,
1489 p_data => x_msg_data
1490 );
1491 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1492 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1493 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History',
1494 'SQL Message ['||sqlerrm||']');
1495 END IF;
1496
1497 End Copy_Repair_History;
1498
1499
1500 PROCEDURE Build_Repair_History_Record (
1501 p_original_repair_history_id IN NUMBER,
1502 x_repair_history_Rec OUT NOCOPY CSD_REPAIR_HISTORY_PVT.REPH_Rec_Type,
1503 x_return_status OUT NOCOPY VARCHAR2
1504 ) IS
1505
1506 CURSOR c_repair_history_record(p_original_repair_history_id IN NUMBER) IS
1507 SELECT request_id, program_id, program_application_id, program_update_date, event_code, event_date, quantity
1508 ,paramn1, paramn2, paramn3, paramn4, paramn5, paramn6, paramn7, paramn8, paramn9, paramn10
1509 , paramc1, paramc2, paramc3, paramc4, paramc5, paramc6, paramc7, paramc8, paramc9, paramc10
1510 , paramd1, paramd2, paramd3, paramd4, paramd5, paramd6, paramd7, paramd8, paramd9, paramd10
1511 , attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6
1512 , attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
1513 FROM csd_repair_history
1514 WHERE repair_history_id = p_original_repair_history_id;
1515
1516 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1517 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1518 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1519 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1520
1521 begin
1522
1523 -- Initialize API return status to success
1524 x_return_status := FND_API.G_RET_STS_SUCCESS;
1525
1526 OPEN c_repair_history_record(p_original_repair_history_id);
1527 FETCH c_repair_history_record INTO
1528 x_repair_history_Rec.request_id, x_repair_history_Rec.program_id, x_repair_history_Rec.program_application_id,
1529 x_repair_history_Rec.program_update_date, x_repair_history_Rec.event_code, x_repair_history_Rec.event_date,
1530 x_repair_history_Rec.quantity,x_repair_history_Rec.paramn1, x_repair_history_Rec.paramn2, x_repair_history_Rec.paramn3,
1531 x_repair_history_Rec.paramn4, x_repair_history_Rec.paramn5, x_repair_history_Rec.paramn6, x_repair_history_Rec.paramn7,
1532 x_repair_history_Rec.paramn8, x_repair_history_Rec.paramn9, x_repair_history_Rec.paramn10, x_repair_history_Rec.paramc1,
1533 x_repair_history_Rec.paramc2, x_repair_history_Rec.paramc3, x_repair_history_Rec.paramc4, x_repair_history_Rec.paramc5,
1534 x_repair_history_Rec.paramc6, x_repair_history_Rec.paramc7, x_repair_history_Rec.paramc8, x_repair_history_Rec.paramc9,
1535 x_repair_history_Rec.paramc10, x_repair_history_Rec.paramd1, x_repair_history_Rec.paramd2, x_repair_history_Rec.paramd3,
1536 x_repair_history_Rec.paramd4, x_repair_history_Rec.paramd5, x_repair_history_Rec.paramd6, x_repair_history_Rec.paramd7,
1537 x_repair_history_Rec.paramd8, x_repair_history_Rec.paramd9, x_repair_history_Rec.paramd10, x_repair_history_Rec.attribute_category,
1538 x_repair_history_Rec.attribute1, x_repair_history_Rec.attribute2, x_repair_history_Rec.attribute3, x_repair_history_Rec.attribute4,
1539 x_repair_history_Rec.attribute5, x_repair_history_Rec.attribute6, x_repair_history_Rec.attribute7, x_repair_history_Rec.attribute8,
1540 x_repair_history_Rec.attribute9, x_repair_history_Rec.attribute10, x_repair_history_Rec.attribute11, x_repair_history_Rec.attribute12,
1541 x_repair_history_Rec.attribute13, x_repair_history_Rec.attribute14, x_repair_history_Rec.attribute15;
1542
1543 IF c_repair_history_record%notfound then
1544 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
1545 FND_MESSAGE.SET_TOKEN('p_original_repair_history_id',p_original_repair_history_id);
1546 FND_MSG_PUB.ADD;
1547 RAISE FND_API.G_EXC_ERROR;
1548 END IF;
1549
1550 IF c_repair_history_record%isopen then
1551 CLOSE c_repair_history_record;
1552 END IF;
1553
1554 x_repair_history_Rec.CREATED_BY := FND_GLOBAL.USER_ID;
1555 x_repair_history_Rec.CREATION_DATE := sysdate;
1556 x_repair_history_Rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1557 x_repair_history_Rec.LAST_UPDATE_DATE := sysdate;
1558 x_repair_history_Rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
1559
1560 END Build_Repair_History_Record;
1561
1562
1563 PROCEDURE Copy_JTF_Notes (
1564 p_api_version IN NUMBER,
1565 p_init_msg_list IN VARCHAR2,
1566 p_commit IN VARCHAR2,
1567 p_validation_level IN NUMBER,
1568 x_return_status OUT NOCOPY VARCHAR2,
1569 x_msg_count OUT NOCOPY NUMBER,
1570 x_msg_data OUT NOCOPY VARCHAR2,
1571 p_original_repair_line_id IN NUMBER,
1572 p_new_repair_line_id IN NUMBER
1573 ) IS
1574
1575 l_api_name CONSTANT VARCHAR2(30) := 'Copy_JTF_Notes' ;
1576 l_jtf_note_contexts_tab JTF_NOTES_PUB.jtf_note_contexts_tbl_type;
1577 l_jtf_note_id NUMBER;
1578 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1579 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1580 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1581 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1582
1583
1584 CURSOR c_jtf_note_id (p_original_repair_line_id IN NUMBER) IS
1585 SELECT notes, note_status, note_type, entered_by, entered_date, creation_date, created_by,
1586 last_update_date, last_updated_by, last_update_login,
1587 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1588 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1589 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, CONTEXT
1590 FROM jtf_notes_vl
1591 WHERE source_object_id = p_original_repair_line_id and source_object_code = 'DR';
1592
1593 Begin
1594
1595
1596 -- Initialize message list if p_init_msg_list is set to TRUE.
1597 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1598 FND_MSG_PUB.initialize;
1599 END IF;
1600
1601 -- Initialize API return status to success
1602 x_return_status := FND_API.G_RET_STS_SUCCESS;
1603
1604 --
1605 -- API body
1606 --
1607 -- Local Procedure
1608
1609 FOR C in c_jtf_note_id(p_original_repair_line_id)
1610 loop
1611
1612 JTF_NOTES_PUB.create_note
1613 ( p_api_version => 1.0
1614 , p_init_msg_list => 'F'
1615 , p_commit => p_commit
1616 , p_validation_level => 0
1617 , x_return_status => x_return_status
1618 , x_msg_count => x_msg_count
1619 , x_msg_data => x_msg_data
1620 , p_source_object_code => 'DR'
1621 , p_source_object_id => p_new_repair_line_id
1622 , p_notes => C.notes
1623 , p_note_status => C.note_status
1624 , p_note_type => C.note_type
1625 , p_entered_by => C.entered_by
1626 , p_entered_date => C.entered_date
1627 , x_jtf_note_id => l_jtf_note_id
1628 , p_creation_date => C.creation_date
1629 , p_created_by => C.created_by
1630 , p_last_update_date => C.last_update_date
1631 , p_last_updated_by => C.last_updated_by
1632 , p_last_update_login => C.last_update_login
1633 , p_attribute1 => C.ATTRIBUTE1
1634 , p_attribute2 => C.ATTRIBUTE2
1635 , p_attribute3 => C.ATTRIBUTE3
1636 , p_attribute4 => C.ATTRIBUTE4
1637 , p_attribute5 => C.ATTRIBUTE5
1638 , p_attribute6 => C.ATTRIBUTE6
1639 , p_attribute7 => C.ATTRIBUTE7
1640 , p_attribute8 => C.ATTRIBUTE8
1641 , p_attribute9 => C.ATTRIBUTE9
1642 , p_attribute10 => C.ATTRIBUTE10
1643 , p_attribute11 => C.ATTRIBUTE11
1644 , p_attribute12 => C.ATTRIBUTE12
1645 , p_attribute13 => C.ATTRIBUTE13
1646 , p_attribute14 => C.ATTRIBUTE14
1647 , p_attribute15 => C.ATTRIBUTE15
1648 , p_context => C.CONTEXT
1649 , p_jtf_note_contexts_tab => l_jtf_note_contexts_tab
1650 );
1651
1652 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1653 IF(l_statement_level >= l_debug_level) THEN
1654 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.JTF_NOTES_PUB.create_note','failed');
1655 END IF;
1656 RAISE FND_API.G_EXC_ERROR;
1657 END IF;
1658
1659 end loop;
1660 IF c_jtf_note_id%isopen then
1661 CLOSE c_jtf_note_id;
1662 END IF;
1663
1664
1665 -- End of API body
1666 --
1667 -- Standard check of p_commit.
1668 IF FND_API.To_Boolean( p_commit ) THEN
1669 COMMIT WORK;
1670 END IF;
1671
1672 -- Standard call to get message count and if count is 1, get message info.
1673 FND_MSG_PUB.Count_And_Get
1674 ( p_count => x_msg_count,
1675 p_data => x_msg_data
1676 );
1677
1678 EXCEPTION
1679 WHEN FND_API.G_EXC_ERROR THEN
1680 ROLLBACK TO CSD_SPLIT_PKG;
1681 x_return_status := FND_API.G_RET_STS_ERROR;
1682 FND_MSG_PUB.Count_And_Get
1683 ( p_count => x_msg_count,
1684 p_data => x_msg_data
1685 );
1686 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1687 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1688 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes',
1689 'EXC_ERROR ['||x_msg_data||']');
1690 END IF;
1691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1692 ROLLBACK TO CSD_SPLIT_PKG;
1693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1694 FND_MSG_PUB.Count_And_Get
1695 ( p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1699 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1700 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes',
1701 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1702 END IF;
1703 WHEN OTHERS THEN
1704 ROLLBACK TO CSD_SPLIT_PKG;
1705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1706 IF FND_MSG_PUB.Check_Msg_Level
1707 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1708 THEN
1709 FND_MSG_PUB.Add_Exc_Msg
1710 ( G_PKG_NAME,
1711 l_api_name
1712 );
1713 END IF;
1714 FND_MSG_PUB.Count_And_Get
1715 ( p_count => x_msg_count,
1716 p_data => x_msg_data
1717 );
1718 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1719 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1720 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes',
1721 'SQL Message ['||sqlerrm||']');
1722 END IF;
1723 End Copy_JTF_Notes;
1724
1725
1726 PROCEDURE build_ship_prod_txn_tbl
1727 ( p_repair_line_id IN NUMBER,
1728 x_prod_txn_tbl OUT NOCOPY CSD_PROCESS_PVT.PRODUCT_TXN_TBL,
1729 x_return_status OUT NOCOPY VARCHAR2
1730 ) IS
1731
1732 l_repair_type_ref VARCHAR2(3) := '';
1733 l_auto_process_rma VARCHAR2(1) := '';
1734 l_inv_item_id NUMBER := NULL;
1735 l_inv_revision VARCHAR2(3) := '';
1736 l_contract_id NUMBER := NULL;
1737 l_unit_of_measure VARCHAR2(30) := '';
1738 l_quantity NUMBER := NULL;
1739 l_serial_number VARCHAR2(30) := '';
1740 l_instance_id NUMBER := NULL;
1741 l_price_list_id NUMBER := NULL;
1742 l_return_reason VARCHAR2(30) := '';
1743 l_org_id NUMBER := NULL;
1744 l_incident_id NUMBER := NULL;
1745 l_inv_org_id NUMBER := NULL;
1746 l_revision VARCHAR2(30) := '';
1747 l_bus_process_id NUMBER := NULL;
1748 l_price_list_header_id NUMBER := NULL;
1749 l_cps_txn_billing_type_id NUMBER := NULL;
1750 l_cpr_txn_billing_type_id NUMBER := NULL;
1751 l_ls_txn_billing_type_id NUMBER := NULL;
1752 l_lr_txn_billing_type_id NUMBER := NULL;
1753 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1754 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1755 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1756 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1757
1758 l_po_number VARCHAR2(50); -- swai bug fix 4535829
1759 l_project_id NUMBER := null;
1760 l_task_id NUMBER := null;
1761 l_unit_number VARCHAR2(30) :='';
1762
1763 CURSOR repair_line_dtls(p_rep_line_id IN NUMBER) IS
1764 SELECT
1765 crt.repair_type_ref,
1766 cr.auto_process_rma,
1767 cr.inventory_item_id,
1768 cr.item_revision,
1769 cr.contract_line_id,
1770 cr.unit_of_measure,
1771 cr.quantity,
1772 cr.customer_product_id,
1773 cr.serial_number,
1774 crt.cps_txn_billing_type_id ,
1775 crt.cpr_txn_billing_type_id ,
1776 crt.ls_txn_billing_type_id ,
1777 crt.lr_txn_billing_type_id ,
1778 crt.price_list_header_id ,
1779 crt.business_process_id,
1780 cr.incident_id,
1781 cr.default_po_num,
1782 cr.inventory_org_id,
1783 cr.project_id,
1784 cr.task_id,
1785 cr.unit_number
1786 FROM csd_repairs cr,
1787 csd_repair_types_vl crt
1788 WHERE cr.repair_type_id = crt.repair_type_id
1789 and cr.repair_line_id = p_rep_line_id;
1790
1791 CURSOR get_revision(p_inv_item_id IN NUMBER,
1792 p_org_id IN NUMBER) IS
1793 SELECT
1794 revision
1795 FROM mtl_item_revisions
1796 WHERE inventory_item_id = p_inv_item_id
1797 and organization_id = p_org_id;
1798
1799 BEGIN
1800
1801 x_return_status := FND_API.G_RET_STS_SUCCESS;
1802
1803 --taklam
1804 -- return;
1805
1806 -- Initialize the table
1807 x_prod_txn_tbl.delete;
1808
1809
1810 OPEN repair_line_dtls(p_repair_line_id);
1811
1812 FETCH repair_line_dtls INTO
1813 l_repair_type_ref,
1814 l_auto_process_rma,
1815 l_inv_item_id,
1816 l_inv_revision,
1817 l_contract_id,
1818 l_unit_of_measure,
1819 l_quantity,
1820 l_instance_id,
1821 l_serial_number,
1822 l_cps_txn_billing_type_id,
1823 l_cpr_txn_billing_type_id,
1824 l_ls_txn_billing_type_id,
1825 l_lr_txn_billing_type_id,
1826 l_price_list_header_id,
1827 l_bus_process_id,
1828 l_incident_id,
1829 l_po_number, -- swai bug fix 4535829
1830 l_inv_org_id, -- inv_org_change vijay, 3/20/06
1831 l_project_id,
1832 l_task_id,
1833 l_unit_number;
1834
1835 IF repair_line_dtls%notfound then
1836 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
1837 FND_MESSAGE.SET_TOKEN('REPAIR_LINE_ID',p_repair_line_id);
1838 FND_MSG_PUB.ADD;
1839 RAISE FND_API.G_EXC_ERROR;
1840 END IF;
1841
1842 IF repair_line_dtls%isopen then
1843 CLOSE repair_line_dtls;
1844 END IF;
1845
1846 -- Get the price_list
1847 l_price_list_id := NVL(l_price_list_header_id,FND_PROFILE.value('CS_CHARGE_DEFAULT_PRICE_LIST'));
1848
1849 -- Get the return reason
1850 l_return_reason := FND_PROFILE.value('CSD_DEF_RMA_RETURN_REASON');
1851
1852 l_org_id := csd_process_util.get_org_id(l_incident_id);
1853 -- l_inv_org_id := csd_process_util.get_inv_org_id;
1854 l_revision := l_inv_revision;
1855
1856 IF l_repair_type_ref = 'RR' THEN
1857 -- Shipping customer product txn line
1858 x_prod_txn_tbl(1).repair_line_id := p_repair_line_id ;
1859 x_prod_txn_tbl(1).txn_billing_type_id := l_cps_txn_billing_type_id;
1860 x_prod_txn_tbl(1).action_code := 'CUST_PROD';
1861 x_prod_txn_tbl(1).source_instance_id := l_instance_id;
1862 x_prod_txn_tbl(1).source_serial_number := l_serial_number;
1863 x_prod_txn_tbl(1).action_type := 'SHIP' ;
1864 x_prod_txn_tbl(1).organization_id := l_org_id ;
1865 x_prod_txn_tbl(1).business_process_id := l_bus_process_id ;
1866 x_prod_txn_tbl(1).inventory_item_id := l_inv_item_id ;
1867 x_prod_txn_tbl(1).unit_of_measure_code := l_unit_of_measure ;
1868 x_prod_txn_tbl(1).quantity := l_quantity ;
1869 x_prod_txn_tbl(1).lot_number := FND_API.G_MISS_CHAR;
1870 x_prod_txn_tbl(1).price_list_id := l_price_list_id ;
1871 -- x_prod_txn_tbl(1).contract_id := l_contract_id ;
1872 x_prod_txn_tbl(1).sub_inventory := FND_API.G_MISS_CHAR;
1873 x_prod_txn_tbl(1).no_charge_flag := csd_process_util.get_no_chg_flag(l_cps_txn_billing_type_id);
1874 x_prod_txn_tbl(1).interface_to_om_flag := 'N' ;
1875 x_prod_txn_tbl(1).book_sales_order_flag := 'N' ;
1876 x_prod_txn_tbl(1).release_sales_order_flag := 'N' ;
1877 x_prod_txn_tbl(1).ship_sales_order_flag := 'N' ;
1878 x_prod_txn_tbl(1).process_txn_flag := 'N' ;
1879 x_prod_txn_tbl(1).revision := l_revision ;
1880 x_prod_txn_tbl(1).last_update_date := sysdate ;
1881 x_prod_txn_tbl(1).creation_date := sysdate ;
1882 x_prod_txn_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
1883 x_prod_txn_tbl(1).created_by := FND_GLOBAL.USER_ID;
1884 x_prod_txn_tbl(1).last_update_login := FND_GLOBAL.USER_ID;
1885 x_prod_txn_tbl(1).prod_txn_status := 'ENTERED';
1886 x_prod_txn_tbl(1).prod_txn_code := 'POST';
1887 x_prod_txn_tbl(1).project_id := l_project_id;
1888 x_prod_txn_tbl(1).task_id := l_task_id;
1889 x_prod_txn_tbl(1).unit_number := l_unit_number;
1890 x_prod_txn_tbl(1).inventory_org_id := l_inv_org_id;
1891 x_prod_txn_tbl(1).po_number := l_po_number; -- swai bug fix 4535829
1892
1893 END IF;
1894 EXCEPTION
1895 WHEN FND_API.G_EXC_ERROR THEN
1896 x_return_status := FND_API.G_RET_STS_ERROR ;
1897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1899 WHEN OTHERS THEN
1900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1901 END build_ship_prod_txn_tbl;
1902
1903
1904 End CSD_SPLIT_PKG;
1905