[Home] [Help]
PACKAGE BODY: APPS.CSD_SPLIT_PKG
Source
1 PACKAGE BODY CSD_SPLIT_PKG as
2 /* $Header: csdspltb.pls 120.13.12020000.2 2013/01/23 20:47:26 vicli 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 ,parent_repair_line_id --bug#13698799 auto create ro, parent ro
529 FROM csd_repairs
530 where repair_line_id = p_repair_line_id;
531
532 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
533 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
534 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
535 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
536
537 begin
538
539 -- Initialize API return status to success
540 x_return_status := FND_API.G_RET_STS_SUCCESS;
541
542 IF(l_statement_level >= l_debug_level) THEN
543 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Repln_Record','At the Begin in Build_Repln_Record');
544 END IF;
545
546 IF NVL(p_repair_line_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
547
548 OPEN c_repair_line_dtls(p_repair_line_id);
549 FETCH c_repair_line_dtls INTO
550 x_Repln_Rec.REPAIR_NUMBER
551 ,x_Repln_Rec.INCIDENT_ID
552 ,x_Repln_Rec.INVENTORY_ITEM_ID
553 ,x_Repln_Rec.CUSTOMER_PRODUCT_ID
554 ,x_Repln_Rec.UNIT_OF_MEASURE
555 ,x_Repln_Rec.REPAIR_TYPE_ID
556 ,x_Repln_Rec.RESOURCE_ID
557 ,x_Repln_Rec.INSTANCE_ID
558 ,x_Repln_Rec.PROJECT_ID
559 ,x_Repln_Rec.TASK_ID
560 ,x_Repln_Rec.UNIT_NUMBER
561 ,x_Repln_Rec.CONTRACT_LINE_ID
562 ,x_Repln_Rec.QUANTITY
563 ,x_Repln_Rec.STATUS
564 ,x_Repln_Rec.APPROVAL_REQUIRED_FLAG
565 ,x_Repln_Rec.DATE_CLOSED
566 ,x_Repln_Rec.QUANTITY_IN_WIP
567 ,x_Repln_Rec.APPROVAL_STATUS
568 ,x_Repln_Rec.QUANTITY_RCVD
569 ,x_Repln_Rec.QUANTITY_SHIPPED
570 ,x_Repln_Rec.SERIAL_NUMBER
571 ,x_Repln_Rec.PROMISE_DATE
572 ,x_Repln_Rec.ATTRIBUTE_CATEGORY
573 ,x_Repln_Rec.ATTRIBUTE1
574 ,x_Repln_Rec.ATTRIBUTE2
575 ,x_Repln_Rec.ATTRIBUTE3
576 ,x_Repln_Rec.ATTRIBUTE4
577 ,x_Repln_Rec.ATTRIBUTE5
578 ,x_Repln_Rec.ATTRIBUTE6
579 ,x_Repln_Rec.ATTRIBUTE7
580 ,x_Repln_Rec.ATTRIBUTE8
581 ,x_Repln_Rec.ATTRIBUTE9
582 ,x_Repln_Rec.ATTRIBUTE10
583 ,x_Repln_Rec.ATTRIBUTE11
584 ,x_Repln_Rec.ATTRIBUTE12
585 ,x_Repln_Rec.ATTRIBUTE13
586 ,x_Repln_Rec.ATTRIBUTE14
587 ,x_Repln_Rec.ATTRIBUTE15
588 ,x_Repln_Rec.REPAIR_GROUP_ID
589 ,x_Repln_Rec.ORDER_LINE_ID
590 ,x_Repln_Rec.ORIGINAL_SOURCE_REFERENCE
591 ,x_Repln_Rec.STATUS_REASON_CODE
592 ,x_Repln_Rec.AUTO_PROCESS_RMA
593 ,x_Repln_Rec.REPAIR_MODE
594 ,x_Repln_Rec.ITEM_REVISION
595 ,x_Repln_Rec.REPAIR_GROUP_ID
596 ,x_Repln_Rec.RO_TXN_STATUS
597 ,x_Repln_Rec.CURRENCY_CODE
598 ,x_Repln_Rec.DEFAULT_PO_NUM
599 ,x_Repln_Rec.ORIGINAL_SOURCE_HEADER_ID
600 ,x_Repln_Rec.ORIGINAL_SOURCE_LINE_ID
601 ,x_Repln_Rec.PRICE_LIST_HEADER_ID
602 ,x_Repln_Rec.PROBLEM_DESCRIPTION -- swai: bug 4666344
603 ,x_Repln_Rec.RO_PRIORITY_CODE -- swai: R12
604 ,x_Repln_Rec.RESOLVE_BY_DATE -- rfieldma: 5355051
605 ,x_Repln_Rec.OBJECT_VERSION_NUMBER
606 ,x_Repln_Rec.ATTRIBUTE16 --bug#7497907, 12.1 FP, subhat
607 ,x_Repln_Rec.ATTRIBUTE17
608 ,x_Repln_Rec.ATTRIBUTE18
609 ,x_Repln_Rec.ATTRIBUTE19
610 ,x_Repln_Rec.ATTRIBUTE20
611 ,x_Repln_Rec.ATTRIBUTE21
612 ,x_Repln_Rec.ATTRIBUTE22
613 ,x_Repln_Rec.ATTRIBUTE23
614 ,x_Repln_Rec.ATTRIBUTE24
615 ,x_Repln_Rec.ATTRIBUTE25
616 ,x_Repln_Rec.ATTRIBUTE26
617 ,x_Repln_Rec.ATTRIBUTE27
618 ,x_Repln_Rec.ATTRIBUTE28
619 ,x_Repln_Rec.ATTRIBUTE29
620 ,x_Repln_Rec.ATTRIBUTE30
621 ,x_Repln_Rec.PARENT_REPAIR_LINE_ID --bug#13698799 auto create ro, parent ro
622 ;
623
624 IF c_repair_line_dtls%notfound then
625 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
626 FND_MESSAGE.SET_TOKEN('REPAIR_LINE_ID',p_repair_line_id);
627 FND_MSG_PUB.ADD;
628 RAISE FND_API.G_EXC_ERROR;
629 END IF;
630
631 IF c_repair_line_dtls%isopen then
632 CLOSE c_repair_line_dtls;
633 END IF;
634
635 END IF;
636
637 EXCEPTION
638 WHEN FND_API.G_EXC_ERROR THEN
639 ROLLBACK TO CSD_SPLIT_PKG;
640 x_return_status := FND_API.G_RET_STS_ERROR;
641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
642 ROLLBACK TO CSD_SPLIT_PKG;
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
644 WHEN OTHERS THEN
645 ROLLBACK TO CSD_SPLIT_PKG;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
647 End Build_Repln_Record;
648
649
650 PROCEDURE Build_Product_TXN_Record (
651 p_product_txn_id IN NUMBER,
652 x_product_txn_Rec OUT NOCOPY CSD_PROCESS_PVT.PRODUCT_TXN_REC,
653 x_return_status OUT NOCOPY VARCHAR2
654 )
655 IS
656
657 CURSOR c_product_txn_line_dtls(p_product_txn_id IN NUMBER) IS
658 SELECT
659 estimate_detail_id
660 ,action_type
661 ,action_code
662 ,interface_to_om_flag
663 ,book_sales_order_flag
664 ,release_sales_order_flag
665 ,ship_sales_order_flag
666 ,sub_inventory
667 ,lot_number
668 ,context
669 ,attribute1
670 ,attribute2
671 ,attribute3
672 ,attribute4
673 ,attribute5
674 ,attribute6
675 ,attribute7
676 ,attribute8
677 ,attribute9
678 ,attribute10
679 ,attribute11
680 ,attribute12
681 ,attribute13
682 ,attribute14
683 ,attribute15
684 ,prod_txn_status
685 ,prod_txn_code
686 --,shipped_serial_number This column is not used in 11.5.10
687 ,object_version_number
688 ,req_header_id
689 ,req_line_id
690 ,order_header_id
691 ,order_line_id
692 ,quantity_received
693 ,quantity_shipped
694 ,source_serial_number
695 ,source_instance_id
696 ,non_source_serial_number
697 ,non_source_instance_id
698 ,locator_id
699 ,sub_inventory_rcvd
700 ,lot_number_rcvd
701 ,picking_rule_id -- Add for R12 pickrule id change.Vijay.
702 ,project_id
703 ,task_id
704 ,unit_number
705 ,internal_po_header_id -- swai: bug 6148019
706 FROM csd_product_transactions
707 where product_transaction_id = p_product_txn_id;
708
709 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
710 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
711 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
712 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
713
714 begin
715
716 -- Initialize API return status to success
717 x_return_status := FND_API.G_RET_STS_SUCCESS;
718
719 IF NVL(p_product_txn_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
720 OPEN c_product_txn_line_dtls(p_product_txn_id);
721 FETCH c_product_txn_line_dtls INTO
722 x_product_txn_Rec.estimate_detail_id
723 ,x_product_txn_Rec.action_type
724 ,x_product_txn_Rec.action_code
725 ,x_product_txn_Rec.interface_to_om_flag
726 ,x_product_txn_Rec.book_sales_order_flag
727 ,x_product_txn_Rec.release_sales_order_flag
728 ,x_product_txn_Rec.ship_sales_order_flag
729 ,x_product_txn_Rec.sub_inventory
730 ,x_product_txn_Rec.lot_number
731 ,x_product_txn_Rec.context
732 ,x_product_txn_Rec.attribute1
733 ,x_product_txn_Rec.attribute2
734 ,x_product_txn_Rec.attribute3
735 ,x_product_txn_Rec.attribute4
736 ,x_product_txn_Rec.attribute5
737 ,x_product_txn_Rec.attribute6
738 ,x_product_txn_Rec.attribute7
739 ,x_product_txn_Rec.attribute8
740 ,x_product_txn_Rec.attribute9
741 ,x_product_txn_Rec.attribute10
742 ,x_product_txn_Rec.attribute11
743 ,x_product_txn_Rec.attribute12
744 ,x_product_txn_Rec.attribute13
745 ,x_product_txn_Rec.attribute14
746 ,x_product_txn_Rec.attribute15
747 ,x_product_txn_Rec.prod_txn_status
748 ,x_product_txn_Rec.prod_txn_code
749 -- ,x_product_txn_Rec.shipped_serial_number this column is not used in 11.5.10
750 ,x_product_txn_Rec.object_version_number
751 ,x_product_txn_Rec.req_header_id
752 ,x_product_txn_Rec.req_line_id
753 ,x_product_txn_Rec.order_header_id
754 ,x_product_txn_Rec.order_line_id
755 ,x_product_txn_Rec.prd_txn_qty_received
756 ,x_product_txn_Rec.prd_txn_qty_shipped
757 ,x_product_txn_Rec.source_serial_number
758 ,x_product_txn_Rec.source_instance_id
759 ,x_product_txn_Rec.non_source_serial_number
760 ,x_product_txn_Rec.non_source_instance_id
761 ,x_product_txn_Rec.locator_id
762 ,x_product_txn_Rec.sub_inventory_rcvd
763 ,x_product_txn_Rec.lot_number_rcvd
764 ,x_product_txn_Rec.picking_rule_id -- Add for R12 pickrule id change.Vijay.
765 ,x_product_txn_Rec.project_id
766 ,x_product_txn_Rec.task_id
767 ,x_product_txn_Rec.unit_number
768 ,x_product_txn_Rec.internal_po_header_id -- swai: bug 6148019
769 ;
770
771 IF c_product_txn_line_dtls%notfound then
772 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
773 FND_MESSAGE.SET_TOKEN('p_product_txn_id',p_product_txn_id);
774 FND_MSG_PUB.ADD;
775 RAISE FND_API.G_EXC_ERROR;
776 END IF;
777
778 IF c_product_txn_line_dtls%isopen then
779 CLOSE c_product_txn_line_dtls;
780 END IF;
781
782 END IF;
783
784 -- Standard call to get message count and if count is 1, get message info.
785 EXCEPTION
786 WHEN FND_API.G_EXC_ERROR THEN
787 ROLLBACK TO CSD_SPLIT_PKG;
788 x_return_status := FND_API.G_RET_STS_ERROR;
789 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
790 ROLLBACK TO CSD_SPLIT_PKG;
791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
792 WHEN OTHERS THEN
793 ROLLBACK TO CSD_SPLIT_PKG;
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
795 End Build_Product_TXN_Record;
796
797
798 PROCEDURE Set_Error_Message (
799 p_msg_code IN VARCHAR2,
800 x_return_status OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2
803 )
804
805 IS
806
807 begin
808 -- Initialize API return status to success
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 -- Initialize message list if p_init_msg_list is set to TRUE.
812 IF FND_API.to_Boolean( fnd_api.g_true ) THEN
813 FND_MSG_PUB.initialize;
814 END IF;
815
816 IF (p_msg_code is not null) ThEN
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 FND_MESSAGE.SET_NAME('CSD',p_msg_code);
819 FND_MSG_PUB.ADD;
820 END IF;
821
822 FND_MSG_PUB.Count_And_Get
823 ( p_count => x_msg_count,
824 p_data => x_msg_data
825 );
826
827 End Set_Error_Message;
828
829
830 PROCEDURE Is_Split_Repair_Order_Allow (
831 p_repair_line_id IN NUMBER,
832 x_return_status OUT NOCOPY VARCHAR2,
833 x_msg_count OUT NOCOPY NUMBER,
834 x_msg_data OUT NOCOPY VARCHAR2
835 )
836
837 IS
838 l_api_name CONSTANT VARCHAR2(30) := 'Is_Split_Repair_Order_Allow' ;
839 l_repair_quantity NUMBER;
840 l_number_product_txn_lines NUMBER;
841 l_total_quantity_rcvd NUMBER;
842 l_total_quantity_in_wip NUMBER;
843 l_repair_mode VARCHAR2(10);
844 l_repair_type_ref VARCHAR2(10);
845 l_wip_job_count NUMBER;
846
847 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
848 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
849 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
850 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
851
852 CURSOR c_rma_product_txn_line_info(p_repair_line_id IN NUMBER) IS
853 SELECT cptv.action_type, cptv.interface_to_om_flag, cptv.repair_quantity, cptv.quantity_in_wip, cptv.quantity_rcvd, cptv.status,
854 cptv.serial_number_control_code, crtv.repair_type_ref, msiv.comms_nl_trackable_flag, crtv.repair_mode
855 FROM csd_product_txns_v cptv, csd_repair_types_vl crtv, mtl_system_items_vl msiv
856 WHERE repair_line_id = p_repair_line_id and crtv.repair_type_id = cptv.repair_type_id and action_type = 'RMA'
857 and msiv.inventory_item_id = cptv.inventory_item_id and msiv.organization_id = cs_std.get_item_valdn_orgzn_id;
858
859
860 CURSOR c_ship_product_txn_line_info(p_repair_line_id IN NUMBER) IS
861 SELECT interface_to_om_flag
862 FROM csd_product_txns_v
863 WHERE repair_line_id = p_repair_line_id and action_type = 'SHIP';
864
865 CURSOR c_repair_type_ref(p_repair_line_id IN NUMBER) IS
866 SELECT crtv.repair_type_ref
867 FROM csd_repairs_v crv, csd_repair_types_vl crtv
868 WHERE repair_line_id = p_repair_line_id and crv.repair_type_id = crtv.repair_type_id;
869
870 CURSOR c_wip_job(p_repair_line_id IN NUMBER) IS
871 SELECT count(*)
872 FROM csd_repair_job_xref
873 WHERE repair_line_id = p_repair_line_id;
874
875
876
877 begin
878 -- Initialize API return status to success
879 x_return_status := FND_API.G_RET_STS_SUCCESS;
880 l_number_product_txn_lines := 0;
881 l_total_quantity_rcvd := 0;
882 l_total_quantity_in_wip := 0;
883 l_repair_mode := null;
884
885 -- Initialize message list if p_init_msg_list is set to TRUE.
886 IF FND_API.to_Boolean( fnd_api.g_true ) THEN
887 FND_MSG_PUB.initialize;
888 END IF;
889
890 IF (p_repair_line_id is null) THEN
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_WITHOUT_RO_NUM');
893 FND_MSG_PUB.ADD;
894 FND_MSG_PUB.Count_And_Get
895 ( p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898 return;
899 END IF;
900 --taklam
901 -- return;
902
903 OPEN c_repair_type_ref(p_repair_line_id);
904 FETCH c_repair_type_ref INTO l_repair_type_ref;
905 IF c_repair_type_ref%isopen then
906 CLOSE c_repair_type_ref;
907 END IF;
908
909 IF l_repair_type_ref not in ('SR', 'RR') then
910 x_return_status := FND_API.G_RET_STS_ERROR;
911 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_INCORRECT_REF');
912 FND_MSG_PUB.ADD;
913 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
914 FND_MSG_PUB.Count_And_Get
915 ( p_count => x_msg_count,
916 p_data => x_msg_data
917 );
918 return;
919 end if;
920 END IF;
921
922 OPEN c_wip_job(p_repair_line_id);
923 FETCH c_wip_job INTO l_wip_job_count;
924 IF c_wip_job%isopen then
925 CLOSE c_wip_job;
926 END IF;
927
928 FOR P in c_rma_product_txn_line_info(p_repair_line_id)
929 loop
930 l_number_product_txn_lines := l_number_product_txn_lines + 1;
931 l_repair_mode := P.repair_mode;
932 l_repair_quantity := P.repair_quantity;
933 l_total_quantity_rcvd := l_total_quantity_rcvd + P.quantity_rcvd;
934 l_total_quantity_in_wip := l_total_quantity_in_wip + P.quantity_in_wip;
935 if P.status <> 'O' then
936 x_return_status := FND_API.G_RET_STS_ERROR;
937 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_WRONG_STATUS');
938 FND_MSG_PUB.ADD;
939 elsif P.serial_number_control_code <> 1 then
940 x_return_status := FND_API.G_RET_STS_ERROR;
941 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_PROD_SERIALIZED');
942 FND_MSG_PUB.ADD;
943 elsif P.repair_type_ref not in ('SR', 'RR') then
944 x_return_status := FND_API.G_RET_STS_ERROR;
945 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_INCORRECT_REF');
946 FND_MSG_PUB.ADD;
947 elsif P.comms_nl_trackable_flag = 'Y' then
948 x_return_status := FND_API.G_RET_STS_ERROR;
949 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_PROD_IB_TRBLE');
950 FND_MSG_PUB.ADD;
951 end if;
952 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
953 FND_MSG_PUB.Count_And_Get
954 ( p_count => x_msg_count,
955 p_data => x_msg_data
956 );
957 return;
958 end if;
959 end loop;
960
961 IF c_rma_product_txn_line_info%isopen then
962 CLOSE c_rma_product_txn_line_info;
963 END IF;
964
965 if (l_number_product_txn_lines < 1) then
966 x_return_status := FND_API.G_RET_STS_ERROR;
967 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_NO_LOGISTIC_LINE');
968 FND_MSG_PUB.ADD;
969 elsif (l_repair_quantity > l_total_quantity_rcvd) then
970 x_return_status := FND_API.G_RET_STS_ERROR;
971 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_ALL_QTYS_NOT_REC');
972 FND_MSG_PUB.ADD;
973 elsif ((l_repair_quantity > l_total_quantity_in_wip) and (l_repair_mode = 'WIP') and (l_wip_job_count > 0)) then
974 x_return_status := FND_API.G_RET_STS_ERROR;
975 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_JOB_INCOMPLETED');
976 FND_MSG_PUB.ADD;
977 end if;
978
979 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
980 FND_MSG_PUB.Count_And_Get
981 ( p_count => x_msg_count,
982 p_data => x_msg_data
983 );
984 return;
985 end if;
986
987 FOR S in c_ship_product_txn_line_info(p_repair_line_id)
988 loop
989 if S.interface_to_om_flag = 'Y' then
990 x_return_status := FND_API.G_RET_STS_ERROR;
991 FND_MESSAGE.SET_NAME('CSD','CSD_NOT_SPLIT_LINE_INTERFACED');
992 FND_MSG_PUB.ADD;
993 end if;
994 end loop;
995 IF c_ship_product_txn_line_info%isopen then
996 CLOSE c_ship_product_txn_line_info;
997 END IF;
998
999 FND_MSG_PUB.Count_And_Get
1000 ( p_count => x_msg_count,
1001 p_data => x_msg_data
1002 );
1003
1004 EXCEPTION
1005 WHEN FND_API.G_EXC_ERROR THEN
1006 ROLLBACK TO CSD_SPLIT_PKG;
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 FND_MSG_PUB.Count_And_Get
1009 ( p_count => x_msg_count,
1010 p_data => x_msg_data
1011 );
1012 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1013 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1014 'CSD.PLSQL.CSD_SPLIT_PKG.Is_Split_Repair_Order_Allow',
1015 'EXC_ERROR ['||x_msg_data||']');
1016 END IF;
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 ROLLBACK TO CSD_SPLIT_PKG;
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1020 FND_MSG_PUB.Count_And_Get
1021 ( p_count => x_msg_count,
1022 p_data => x_msg_data
1023 );
1024 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1025 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1026 'CSD.PLSQL.CSD_SPLIT_PKG.Is_Split_Repair_Order_Allow',
1027 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1028 END IF;
1029 WHEN OTHERS THEN
1030 ROLLBACK TO CSD_SPLIT_PKG;
1031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1032 IF FND_MSG_PUB.Check_Msg_Level
1033 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1034 THEN
1035 FND_MSG_PUB.Add_Exc_Msg
1036 ( G_PKG_NAME,
1037 l_api_name
1038 );
1039 END IF;
1040 FND_MSG_PUB.Count_And_Get
1041 ( p_count => x_msg_count,
1042 p_data => x_msg_data
1043 );
1044 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1045 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1046 'CSD.PLSQL.CSD_SPLIT_PKG.Is_Split_Repair_Order_Allow',
1047 'SQL Message ['||sqlerrm||']');
1048 END IF;
1049
1050 End Is_Split_Repair_Order_Allow;
1051
1052
1053 PROCEDURE Create_New_Repair_Order (
1054 p_api_version IN NUMBER,
1055 p_init_msg_list IN VARCHAR2,
1056 p_commit IN VARCHAR2,
1057 p_validation_level IN NUMBER,
1058 x_return_status OUT NOCOPY VARCHAR2,
1059 x_msg_count OUT NOCOPY NUMBER,
1060 x_msg_data OUT NOCOPY VARCHAR2,
1061 x_repair_line_id OUT NOCOPY NUMBER,
1062 p_copy_attachment IN VARCHAR2,
1063 p_original_repair_line_id IN NUMBER,
1064 p_rep_line_rec IN CSD_REPAIRS_PUB.REPLN_REC_TYPE
1065 ) IS
1066
1067 l_api_name CONSTANT VARCHAR2(30) := 'Create_New_Repair_Order' ;
1068 l_original_product_txn_rec CSD_PROCESS_PVT.PRODUCT_TXN_REC;
1069 l_repair_line_id NUMBER;
1070 l_repair_number NUMBER;
1071 x_ship_prod_txn_tbl CSD_PROCESS_PVT.PRODUCT_TXN_TBL;
1072
1073 CURSOR c_rma_product_txns_id(p_original_repair_line_id IN NUMBER) IS
1074 SELECT product_transaction_id
1075 FROM csd_product_txns_v
1076 WHERE repair_line_id = p_original_repair_line_id and ACTION_TYPE = 'RMA';
1077
1078 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1079 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1080 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1081 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1082
1083 Begin
1084
1085
1086 -- Initialize message list if p_init_msg_list is set to TRUE.
1087 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1088 FND_MSG_PUB.initialize;
1089 END IF;
1090
1091 -- Initialize API return status to success
1092 x_return_status := FND_API.G_RET_STS_SUCCESS;
1093
1094 --
1095 -- API body
1096 --
1097 -- Local Procedure
1098
1099 CSD_REPAIRS_PVT.Create_Repair_Order
1100 (p_API_version_number => 1.0,
1101 p_init_msg_list => p_init_msg_list,
1102 p_commit => p_commit,
1103 p_validation_level => null,
1104 p_repair_line_id => null,
1105 p_Repln_Rec => p_rep_line_rec,
1106 x_repair_line_id => l_repair_line_id,
1107 x_repair_number => l_repair_number,
1108 x_return_status => x_return_status,
1109 x_msg_count => x_msg_count,
1110 x_msg_data => x_msg_data
1111 );
1112 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1113 IF(l_statement_level >= l_debug_level) THEN
1114 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIRS_PVT.Create_Repair_Order','failed');
1115 END IF;
1116 RAISE FND_API.G_EXC_ERROR;
1117 END IF;
1118
1119 x_repair_line_id := l_repair_line_id;
1120
1121 FOR P in c_rma_product_txns_id(p_original_repair_line_id)
1122
1123 loop
1124
1125 Build_Product_TXN_Record (
1126 p_product_txn_id => P.product_transaction_id,
1127 x_product_txn_Rec => l_original_product_txn_rec,
1128 x_return_status => x_return_status
1129 );
1130 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1131 IF(l_statement_level >= l_debug_level) THEN
1132 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Product_TXN_Record','failed');
1133 END IF;
1134 RAISE FND_API.G_EXC_ERROR;
1135 END IF;
1136
1137
1138 l_original_product_txn_rec.PRODUCT_TRANSACTION_ID := FND_API.G_MISS_NUM;
1139 CSD_PRODUCT_TRANSACTIONS_PKG.INSERT_ROW
1140 ( px_PRODUCT_TRANSACTION_ID => l_original_product_txn_rec.PRODUCT_TRANSACTION_ID,
1141 p_REPAIR_LINE_ID => l_repair_line_id,
1142 p_ESTIMATE_DETAIL_ID => l_original_product_txn_rec.estimate_detail_id,
1143 p_ACTION_TYPE => l_original_product_txn_rec.ACTION_TYPE,
1144 p_ACTION_CODE => l_original_product_txn_rec.ACTION_CODE,
1145 p_LOT_NUMBER => l_original_product_txn_rec.LOT_NUMBER,
1146 p_SUB_INVENTORY => l_original_product_txn_rec.SUB_INVENTORY,
1147 p_INTERFACE_TO_OM_FLAG => l_original_product_txn_rec.INTERFACE_TO_OM_FLAG,
1148 p_BOOK_SALES_ORDER_FLAG => l_original_product_txn_rec.BOOK_SALES_ORDER_FLAG,
1149 p_RELEASE_SALES_ORDER_FLAG => l_original_product_txn_rec.RELEASE_SALES_ORDER_FLAG,
1150 p_SHIP_SALES_ORDER_FLAG => l_original_product_txn_rec.SHIP_SALES_ORDER_FLAG ,
1151 p_PROD_TXN_STATUS => l_original_product_txn_rec.PROD_TXN_STATUS,
1152 p_PROD_TXN_CODE => l_original_product_txn_rec.PROD_TXN_CODE,
1153 p_LAST_UPDATE_DATE => SYSDATE,
1154 p_CREATION_DATE => SYSDATE,
1155 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1156 p_CREATED_BY => FND_GLOBAL.USER_ID,
1157 p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID,
1158 p_ATTRIBUTE1 => l_original_product_txn_rec.ATTRIBUTE1,
1159 p_ATTRIBUTE2 => l_original_product_txn_rec.ATTRIBUTE2,
1160 p_ATTRIBUTE3 => l_original_product_txn_rec.ATTRIBUTE3,
1161 p_ATTRIBUTE4 => l_original_product_txn_rec.ATTRIBUTE4,
1162 p_ATTRIBUTE5 => l_original_product_txn_rec.ATTRIBUTE5,
1163 p_ATTRIBUTE6 => l_original_product_txn_rec.ATTRIBUTE6,
1164 p_ATTRIBUTE7 => l_original_product_txn_rec.ATTRIBUTE7,
1165 p_ATTRIBUTE8 => l_original_product_txn_rec.ATTRIBUTE8,
1166 p_ATTRIBUTE9 => l_original_product_txn_rec.ATTRIBUTE9,
1167 p_ATTRIBUTE10 => l_original_product_txn_rec.ATTRIBUTE10,
1168 p_ATTRIBUTE11 => l_original_product_txn_rec.ATTRIBUTE11,
1169 p_ATTRIBUTE12 => l_original_product_txn_rec.ATTRIBUTE12,
1170 p_ATTRIBUTE13 => l_original_product_txn_rec.ATTRIBUTE13,
1171 p_ATTRIBUTE14 => l_original_product_txn_rec.ATTRIBUTE14,
1172 p_ATTRIBUTE15 => l_original_product_txn_rec.ATTRIBUTE15,
1173 p_CONTEXT => l_original_product_txn_rec.CONTEXT ,
1174 p_OBJECT_VERSION_NUMBER => 1,
1175 -- p_SHIPPED_SERIAL_NUMBER => l_original_product_txn_rec.SHIPPED_SERIAL_NUMBER
1176 p_Req_Header_Id => l_original_product_txn_rec.Req_Header_Id,
1177 p_Req_Line_Id => l_original_product_txn_rec.Req_Line_Id,
1178 p_Order_Header_Id => l_original_product_txn_rec.Order_Header_Id,
1179 p_Order_Line_Id => l_original_product_txn_rec.Order_Line_Id,
1180 p_Prd_txn_Qty_Received => l_original_product_txn_rec.Prd_Txn_Qty_Received,
1181 p_Prd_Txn_Qty_Shipped => l_original_product_txn_rec.Prd_Txn_Qty_Shipped,
1182 p_Source_Serial_Number => l_original_product_txn_rec.Source_Serial_Number,
1183 p_Source_Instance_Id => l_original_product_txn_rec.Source_Instance_Id,
1184 p_Non_Source_Serial_Number => l_original_product_txn_rec.Non_Source_Serial_Number,
1185 p_Non_Source_Instance_Id => l_original_product_txn_rec.Non_Source_Instance_Id,
1186 p_Locator_Id => l_original_product_txn_rec.Locator_Id,
1187 p_Sub_Inventory_Rcvd => l_original_product_txn_rec.Sub_Inventory_Rcvd,
1188 p_Lot_Number_Rcvd => l_original_product_txn_rec.Lot_Number_Rcvd,
1189 p_picking_rule_id => l_original_product_txn_rec.picking_rule_id, -- Add for R12 pickrule id change.Vijay.
1190 p_project_id => l_original_product_txn_rec.project_id,
1191 p_task_id => l_original_product_txn_rec.task_id,
1192 p_unit_number => l_original_product_txn_rec.unit_number,
1193 p_internal_po_header_id => l_original_product_txn_rec.internal_po_header_id -- swai: bug 6148019
1194 );
1195 end loop;
1196
1197 IF c_rma_product_txns_id%isopen then
1198 CLOSE c_rma_product_txns_id;
1199 END IF;
1200
1201
1202 Build_Ship_Prod_Txn_Tbl
1203 ( p_repair_line_id => l_repair_line_id,
1204 x_prod_txn_tbl => x_ship_prod_txn_tbl,
1205 x_return_status => x_return_status );
1206
1207 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1208 IF(l_statement_level >= l_debug_level) THEN
1209 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Ship_Prod_Txn_Tbl','failed');
1210 END IF;
1211 RAISE FND_API.G_EXC_ERROR;
1212 END IF;
1213
1214
1215 IF x_ship_prod_txn_tbl.COUNT > 0 THEN
1216 FOR i IN x_ship_prod_txn_tbl.first..x_ship_prod_txn_tbl.last
1217 LOOP
1218
1219 CSD_PROCESS_PVT.CREATE_PRODUCT_TXN
1220 (p_api_version => 1.0 ,
1221 p_commit => fnd_api.g_false,
1222 p_init_msg_list => 'F',
1223 p_validation_level => fnd_api.g_valid_level_full,
1224 x_product_txn_rec => x_ship_prod_txn_tbl(i),
1225 x_return_status => x_return_status,
1226 x_msg_count => x_msg_count,
1227 x_msg_data => x_msg_data );
1228
1229 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1230 IF(l_statement_level >= l_debug_level) THEN
1231 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_PROCESS_UTIL.CREATE_PRODUCT_TXN','failed');
1232 END IF;
1233 RAISE FND_API.G_EXC_ERROR;
1234 END IF;
1235 END LOOP;
1236 END IF;
1237
1238 Copy_Repair_History (
1239 p_api_version => 1.0,
1240 p_init_msg_list => p_init_msg_list,
1241 p_commit => p_commit,
1242 p_validation_level => p_validation_level,
1243 x_return_status => x_return_status,
1244 x_msg_count => x_msg_count,
1245 x_msg_data => x_msg_data,
1246 p_original_repair_line_id => p_original_repair_line_id,
1247 p_new_repair_line_id => l_repair_line_id);
1248
1249 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1250 IF(l_statement_level >= l_debug_level) THEN
1251 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History','failed');
1252 END IF;
1253 RAISE FND_API.G_EXC_ERROR;
1254 END IF;
1255
1256 Copy_JTF_Notes (
1257 p_api_version => 1.0,
1258 p_init_msg_list => p_init_msg_list,
1259 p_commit => p_commit,
1260 p_validation_level => p_validation_level,
1261 x_return_status => x_return_status,
1262 x_msg_count => x_msg_count,
1263 x_msg_data => x_msg_data,
1264 p_original_repair_line_id => p_original_repair_line_id,
1265 p_new_repair_line_id => l_repair_line_id);
1266
1267 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1268 IF(l_statement_level >= l_debug_level) THEN
1269 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes','failed');
1270 END IF;
1271 RAISE FND_API.G_EXC_ERROR;
1272 END IF;
1273
1274 If (p_copy_attachment = 'Y') THEN
1275 CSD_REPAIRS_PVT.Copy_Attachments
1276 ( p_api_version => 1.0,
1277 p_commit => p_commit,
1278 p_init_msg_list => p_init_msg_list,
1279 p_validation_level => p_validation_level,
1280 p_original_ro_id => p_original_repair_line_id,
1281 p_new_ro_id => l_repair_line_id,
1282 x_return_status => x_return_status,
1283 x_msg_count => x_msg_count,
1284 x_msg_data => x_msg_data);
1285
1286 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1287 IF(l_statement_level >= l_debug_level) THEN
1288 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIRS_PVT.Copy_Attachments','failed');
1289 END IF;
1290 RAISE FND_API.G_EXC_ERROR;
1291 END IF;
1292 END if;
1293
1294 -- End of API body
1295 --
1296 -- Standard check of p_commit.
1297 IF FND_API.To_Boolean( p_commit ) THEN
1298 COMMIT WORK;
1299 END IF;
1300
1301 -- Standard call to get message count and if count is 1, get message info.
1302 FND_MSG_PUB.Count_And_Get
1303 ( p_count => x_msg_count,
1304 p_data => x_msg_data
1305 );
1306
1307 EXCEPTION
1308 WHEN FND_API.G_EXC_ERROR THEN
1309 ROLLBACK TO CSD_SPLIT_PKG;
1310 x_return_status := FND_API.G_RET_STS_ERROR;
1311 FND_MSG_PUB.Count_And_Get
1312 ( p_count => x_msg_count,
1313 p_data => x_msg_data
1314 );
1315 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1316 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1317 'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order',
1318 'EXC_ERROR ['||x_msg_data||']');
1319 END IF;
1320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1321 ROLLBACK TO CSD_SPLIT_PKG;
1322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1323 FND_MSG_PUB.Count_And_Get
1324 ( p_count => x_msg_count,
1325 p_data => x_msg_data
1326 );
1327 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1328 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1329 'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order',
1330 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1331 END IF;
1332 WHEN OTHERS THEN
1333 ROLLBACK TO CSD_SPLIT_PKG;
1334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1335 IF FND_MSG_PUB.Check_Msg_Level
1336 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1337 THEN
1338 FND_MSG_PUB.Add_Exc_Msg
1339 ( G_PKG_NAME,
1340 l_api_name
1341 );
1342 END IF;
1343 FND_MSG_PUB.Count_And_Get
1344 ( p_count => x_msg_count,
1345 p_data => x_msg_data
1346 );
1347 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1348 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1349 'CSD.PLSQL.CSD_SPLIT_PKG.Create_New_Repair_Order',
1350 'SQL Message ['||sqlerrm||']');
1351 END IF;
1352
1353 End Create_New_Repair_Order;
1354
1355
1356 PROCEDURE Copy_Repair_History (
1357 p_api_version IN NUMBER,
1358 p_init_msg_list IN VARCHAR2,
1359 p_commit IN VARCHAR2,
1360 p_validation_level IN NUMBER,
1361 x_return_status OUT NOCOPY VARCHAR2,
1362 x_msg_count OUT NOCOPY NUMBER,
1363 x_msg_data OUT NOCOPY VARCHAR2,
1364 p_original_repair_line_id IN NUMBER,
1365 p_new_repair_line_id IN NUMBER
1366 ) IS
1367
1368
1369 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Repair_History' ;
1370 l_repair_history_rec CSD_REPAIR_HISTORY_PVT.REPH_Rec_Type;
1371 l_repair_history_id NUMBER;
1372 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1373 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1374 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1375 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1376
1377 CURSOR c_repair_history_id (p_original_repair_line_id IN NUMBER) IS
1378 SELECT repair_history_id
1379 FROM CSD_REPAIR_HISTORY
1380 WHERE repair_line_id = p_original_repair_line_id;
1381
1382 Begin
1383
1384
1385 -- Initialize message list if p_init_msg_list is set to TRUE.
1386 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1387 FND_MSG_PUB.initialize;
1388 END IF;
1389
1390 -- Initialize API return status to success
1391 x_return_status := FND_API.G_RET_STS_SUCCESS;
1392
1393 --
1394 -- API body
1395 --
1396 -- Local Procedure
1397
1398
1399 FOR C in c_repair_history_id(p_original_repair_line_id)
1400 loop
1401
1402 Build_Repair_History_Record (
1403 p_original_repair_history_id => C.repair_history_id,
1404 x_repair_history_Rec => l_repair_history_rec,
1405 x_return_status => x_return_status
1406 );
1407 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1408 IF(l_statement_level >= l_debug_level) THEN
1409 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_SPLIT_PKG.Build_Repair_History_Record','failed');
1410 END IF;
1411 RAISE FND_API.G_EXC_ERROR;
1412 END IF;
1413
1414
1415 l_repair_history_rec.repair_line_id := p_new_repair_line_id;
1416
1417 CSD_REPAIR_HISTORY_PVT.Create_repair_history(
1418 P_Api_Version_Number => p_api_version,
1419 P_Init_Msg_List => p_init_msg_list,
1420 P_Commit => p_commit,
1421 p_validation_level => p_validation_level,
1422 P_reph_rec => l_repair_history_rec,
1423 X_REPAIR_HISTORY_ID => l_repair_history_id,
1424 X_Return_Status => x_return_status,
1425 X_Msg_Count => x_msg_count,
1426 X_Msg_Data => x_msg_data
1427 );
1428 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1429 IF(l_statement_level >= l_debug_level) THEN
1430 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.CSD_REPAIR_HISTORY_PVT.Create_repair_history','failed');
1431 END IF;
1432 RAISE FND_API.G_EXC_ERROR;
1433 END IF;
1434
1435 end loop;
1436 IF c_repair_history_id%isopen then
1437 CLOSE c_repair_history_id;
1438 END IF;
1439
1440
1441 -- End of API body
1442 --
1443 -- Standard check of p_commit.
1444 IF FND_API.To_Boolean( p_commit ) THEN
1445 COMMIT WORK;
1446 END IF;
1447
1448 -- Standard call to get message count and if count is 1, get message info.
1449 FND_MSG_PUB.Count_And_Get
1450 ( p_count => x_msg_count,
1451 p_data => x_msg_data
1452 );
1453 EXCEPTION
1454 WHEN FND_API.G_EXC_ERROR THEN
1455 ROLLBACK TO CSD_SPLIT_PKG;
1456 x_return_status := FND_API.G_RET_STS_ERROR;
1457 FND_MSG_PUB.Count_And_Get
1458 ( p_count => x_msg_count,
1459 p_data => x_msg_data
1460 );
1461 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1462 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1463 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History',
1464 'EXC_ERROR ['||x_msg_data||']');
1465 END IF;
1466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1467 ROLLBACK TO CSD_SPLIT_PKG;
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1469 FND_MSG_PUB.Count_And_Get
1470 ( p_count => x_msg_count,
1471 p_data => x_msg_data
1472 );
1473 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1474 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1475 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History',
1476 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1477 END IF;
1478 WHEN OTHERS THEN
1479 ROLLBACK TO CSD_SPLIT_PKG;
1480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1481 IF FND_MSG_PUB.Check_Msg_Level
1482 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1483 THEN
1484 FND_MSG_PUB.Add_Exc_Msg
1485 ( G_PKG_NAME,
1486 l_api_name
1487 );
1488 END IF;
1489 FND_MSG_PUB.Count_And_Get
1490 ( p_count => x_msg_count,
1491 p_data => x_msg_data
1492 );
1493 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1494 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1495 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_Repair_History',
1496 'SQL Message ['||sqlerrm||']');
1497 END IF;
1498
1499 End Copy_Repair_History;
1500
1501
1502 PROCEDURE Build_Repair_History_Record (
1503 p_original_repair_history_id IN NUMBER,
1504 x_repair_history_Rec OUT NOCOPY CSD_REPAIR_HISTORY_PVT.REPH_Rec_Type,
1505 x_return_status OUT NOCOPY VARCHAR2
1506 ) IS
1507
1508 CURSOR c_repair_history_record(p_original_repair_history_id IN NUMBER) IS
1509 SELECT request_id, program_id, program_application_id, program_update_date, event_code, event_date, quantity
1510 ,paramn1, paramn2, paramn3, paramn4, paramn5, paramn6, paramn7, paramn8, paramn9, paramn10
1511 , paramc1, paramc2, paramc3, paramc4, paramc5, paramc6, paramc7, paramc8, paramc9, paramc10
1512 , paramd1, paramd2, paramd3, paramd4, paramd5, paramd6, paramd7, paramd8, paramd9, paramd10
1513 , attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6
1514 , attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
1515 FROM csd_repair_history
1516 WHERE repair_history_id = p_original_repair_history_id;
1517
1518 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1519 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1520 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1521 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1522
1523 begin
1524
1525 -- Initialize API return status to success
1526 x_return_status := FND_API.G_RET_STS_SUCCESS;
1527
1528 OPEN c_repair_history_record(p_original_repair_history_id);
1529 FETCH c_repair_history_record INTO
1530 x_repair_history_Rec.request_id, x_repair_history_Rec.program_id, x_repair_history_Rec.program_application_id,
1531 x_repair_history_Rec.program_update_date, x_repair_history_Rec.event_code, x_repair_history_Rec.event_date,
1532 x_repair_history_Rec.quantity,x_repair_history_Rec.paramn1, x_repair_history_Rec.paramn2, x_repair_history_Rec.paramn3,
1533 x_repair_history_Rec.paramn4, x_repair_history_Rec.paramn5, x_repair_history_Rec.paramn6, x_repair_history_Rec.paramn7,
1534 x_repair_history_Rec.paramn8, x_repair_history_Rec.paramn9, x_repair_history_Rec.paramn10, x_repair_history_Rec.paramc1,
1535 x_repair_history_Rec.paramc2, x_repair_history_Rec.paramc3, x_repair_history_Rec.paramc4, x_repair_history_Rec.paramc5,
1536 x_repair_history_Rec.paramc6, x_repair_history_Rec.paramc7, x_repair_history_Rec.paramc8, x_repair_history_Rec.paramc9,
1537 x_repair_history_Rec.paramc10, x_repair_history_Rec.paramd1, x_repair_history_Rec.paramd2, x_repair_history_Rec.paramd3,
1538 x_repair_history_Rec.paramd4, x_repair_history_Rec.paramd5, x_repair_history_Rec.paramd6, x_repair_history_Rec.paramd7,
1539 x_repair_history_Rec.paramd8, x_repair_history_Rec.paramd9, x_repair_history_Rec.paramd10, x_repair_history_Rec.attribute_category,
1540 x_repair_history_Rec.attribute1, x_repair_history_Rec.attribute2, x_repair_history_Rec.attribute3, x_repair_history_Rec.attribute4,
1541 x_repair_history_Rec.attribute5, x_repair_history_Rec.attribute6, x_repair_history_Rec.attribute7, x_repair_history_Rec.attribute8,
1542 x_repair_history_Rec.attribute9, x_repair_history_Rec.attribute10, x_repair_history_Rec.attribute11, x_repair_history_Rec.attribute12,
1543 x_repair_history_Rec.attribute13, x_repair_history_Rec.attribute14, x_repair_history_Rec.attribute15;
1544
1545 IF c_repair_history_record%notfound then
1546 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
1547 FND_MESSAGE.SET_TOKEN('p_original_repair_history_id',p_original_repair_history_id);
1548 FND_MSG_PUB.ADD;
1549 RAISE FND_API.G_EXC_ERROR;
1550 END IF;
1551
1552 IF c_repair_history_record%isopen then
1553 CLOSE c_repair_history_record;
1554 END IF;
1555
1556 x_repair_history_Rec.CREATED_BY := FND_GLOBAL.USER_ID;
1557 x_repair_history_Rec.CREATION_DATE := sysdate;
1558 x_repair_history_Rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1559 x_repair_history_Rec.LAST_UPDATE_DATE := sysdate;
1560 x_repair_history_Rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
1561
1562 END Build_Repair_History_Record;
1563
1564
1565 PROCEDURE Copy_JTF_Notes (
1566 p_api_version IN NUMBER,
1567 p_init_msg_list IN VARCHAR2,
1568 p_commit IN VARCHAR2,
1569 p_validation_level IN NUMBER,
1570 x_return_status OUT NOCOPY VARCHAR2,
1571 x_msg_count OUT NOCOPY NUMBER,
1572 x_msg_data OUT NOCOPY VARCHAR2,
1573 p_original_repair_line_id IN NUMBER,
1574 p_new_repair_line_id IN NUMBER
1575 ) IS
1576
1577 l_api_name CONSTANT VARCHAR2(30) := 'Copy_JTF_Notes' ;
1578 l_jtf_note_contexts_tab JTF_NOTES_PUB.jtf_note_contexts_tbl_type;
1579 l_jtf_note_id NUMBER;
1580 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1581 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1582 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1583 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1584
1585
1586 CURSOR c_jtf_note_id (p_original_repair_line_id IN NUMBER) IS
1587 SELECT notes, note_status, note_type, entered_by, entered_date, creation_date, created_by,
1588 last_update_date, last_updated_by, last_update_login,
1589 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1590 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
1591 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, CONTEXT
1592 FROM jtf_notes_vl
1593 WHERE source_object_id = p_original_repair_line_id and source_object_code = 'DR';
1594
1595 Begin
1596
1597
1598 -- Initialize message list if p_init_msg_list is set to TRUE.
1599 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1600 FND_MSG_PUB.initialize;
1601 END IF;
1602
1603 -- Initialize API return status to success
1604 x_return_status := FND_API.G_RET_STS_SUCCESS;
1605
1606 --
1607 -- API body
1608 --
1609 -- Local Procedure
1610
1611 FOR C in c_jtf_note_id(p_original_repair_line_id)
1612 loop
1613
1614 JTF_NOTES_PUB.create_note
1615 ( p_api_version => 1.0
1616 , p_init_msg_list => 'F'
1617 , p_commit => p_commit
1618 , p_validation_level => 0
1619 , x_return_status => x_return_status
1620 , x_msg_count => x_msg_count
1621 , x_msg_data => x_msg_data
1622 , p_source_object_code => 'DR'
1623 , p_source_object_id => p_new_repair_line_id
1624 , p_notes => C.notes
1625 , p_note_status => C.note_status
1626 , p_note_type => C.note_type
1627 , p_entered_by => C.entered_by
1628 , p_entered_date => C.entered_date
1629 , x_jtf_note_id => l_jtf_note_id
1630 , p_creation_date => C.creation_date
1631 , p_created_by => C.created_by
1632 , p_last_update_date => C.last_update_date
1633 , p_last_updated_by => C.last_updated_by
1634 , p_last_update_login => C.last_update_login
1635 , p_attribute1 => C.ATTRIBUTE1
1636 , p_attribute2 => C.ATTRIBUTE2
1637 , p_attribute3 => C.ATTRIBUTE3
1638 , p_attribute4 => C.ATTRIBUTE4
1639 , p_attribute5 => C.ATTRIBUTE5
1640 , p_attribute6 => C.ATTRIBUTE6
1641 , p_attribute7 => C.ATTRIBUTE7
1642 , p_attribute8 => C.ATTRIBUTE8
1643 , p_attribute9 => C.ATTRIBUTE9
1644 , p_attribute10 => C.ATTRIBUTE10
1645 , p_attribute11 => C.ATTRIBUTE11
1646 , p_attribute12 => C.ATTRIBUTE12
1647 , p_attribute13 => C.ATTRIBUTE13
1648 , p_attribute14 => C.ATTRIBUTE14
1649 , p_attribute15 => C.ATTRIBUTE15
1650 , p_context => C.CONTEXT
1651 , p_jtf_note_contexts_tab => l_jtf_note_contexts_tab
1652 );
1653
1654 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1655 IF(l_statement_level >= l_debug_level) THEN
1656 FND_LOG.STRING(l_statement_level,'CSD.PLSQL.JTF_NOTES_PUB.create_note','failed');
1657 END IF;
1658 RAISE FND_API.G_EXC_ERROR;
1659 END IF;
1660
1661 end loop;
1662 IF c_jtf_note_id%isopen then
1663 CLOSE c_jtf_note_id;
1664 END IF;
1665
1666
1667 -- End of API body
1668 --
1669 -- Standard check of p_commit.
1670 IF FND_API.To_Boolean( p_commit ) THEN
1671 COMMIT WORK;
1672 END IF;
1673
1674 -- Standard call to get message count and if count is 1, get message info.
1675 FND_MSG_PUB.Count_And_Get
1676 ( p_count => x_msg_count,
1677 p_data => x_msg_data
1678 );
1679
1680 EXCEPTION
1681 WHEN FND_API.G_EXC_ERROR THEN
1682 ROLLBACK TO CSD_SPLIT_PKG;
1683 x_return_status := FND_API.G_RET_STS_ERROR;
1684 FND_MSG_PUB.Count_And_Get
1685 ( p_count => x_msg_count,
1686 p_data => x_msg_data
1687 );
1688 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1689 FND_LOG.STRING(FND_LOG.LEVEL_ERROR,
1690 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes',
1691 'EXC_ERROR ['||x_msg_data||']');
1692 END IF;
1693 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1694 ROLLBACK TO CSD_SPLIT_PKG;
1695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1696 FND_MSG_PUB.Count_And_Get
1697 ( p_count => x_msg_count,
1698 p_data => x_msg_data
1699 );
1700 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1701 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1702 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes',
1703 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
1704 END IF;
1705 WHEN OTHERS THEN
1706 ROLLBACK TO CSD_SPLIT_PKG;
1707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1708 IF FND_MSG_PUB.Check_Msg_Level
1709 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1710 THEN
1711 FND_MSG_PUB.Add_Exc_Msg
1712 ( G_PKG_NAME,
1713 l_api_name
1714 );
1715 END IF;
1716 FND_MSG_PUB.Count_And_Get
1717 ( p_count => x_msg_count,
1718 p_data => x_msg_data
1719 );
1720 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1721 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
1722 'CSD.PLSQL.CSD_SPLIT_PKG.Copy_JTF_Notes',
1723 'SQL Message ['||sqlerrm||']');
1724 END IF;
1725 End Copy_JTF_Notes;
1726
1727
1728 PROCEDURE build_ship_prod_txn_tbl
1729 ( p_repair_line_id IN NUMBER,
1730 x_prod_txn_tbl OUT NOCOPY CSD_PROCESS_PVT.PRODUCT_TXN_TBL,
1731 x_return_status OUT NOCOPY VARCHAR2
1732 ) IS
1733
1734 l_repair_type_ref VARCHAR2(3) := '';
1735 l_auto_process_rma VARCHAR2(1) := '';
1736 l_inv_item_id NUMBER := NULL;
1737 l_inv_revision VARCHAR2(3) := '';
1738 l_contract_id NUMBER := NULL;
1739 l_unit_of_measure VARCHAR2(30) := '';
1740 l_quantity NUMBER := NULL;
1741 l_serial_number VARCHAR2(30) := '';
1742 l_instance_id NUMBER := NULL;
1743 l_price_list_id NUMBER := NULL;
1744 l_return_reason VARCHAR2(30) := '';
1745 l_org_id NUMBER := NULL;
1746 l_incident_id NUMBER := NULL;
1747 l_inv_org_id NUMBER := NULL;
1748 l_revision VARCHAR2(30) := '';
1749 l_bus_process_id NUMBER := NULL;
1750 l_price_list_header_id NUMBER := NULL;
1751 l_cps_txn_billing_type_id NUMBER := NULL;
1752 l_cpr_txn_billing_type_id NUMBER := NULL;
1753 l_ls_txn_billing_type_id NUMBER := NULL;
1754 l_lr_txn_billing_type_id NUMBER := NULL;
1755 l_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1756 l_procedure_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1757 l_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
1758 l_event_level NUMBER := FND_LOG.LEVEL_EVENT;
1759
1760 l_po_number VARCHAR2(50); -- swai bug fix 4535829
1761 l_project_id NUMBER := null;
1762 l_task_id NUMBER := null;
1763 l_unit_number VARCHAR2(30) :='';
1764
1765 CURSOR repair_line_dtls(p_rep_line_id IN NUMBER) IS
1766 SELECT
1767 crt.repair_type_ref,
1768 cr.auto_process_rma,
1769 cr.inventory_item_id,
1770 cr.item_revision,
1771 cr.contract_line_id,
1772 cr.unit_of_measure,
1773 cr.quantity,
1774 cr.customer_product_id,
1775 cr.serial_number,
1776 crt.cps_txn_billing_type_id ,
1777 crt.cpr_txn_billing_type_id ,
1778 crt.ls_txn_billing_type_id ,
1779 crt.lr_txn_billing_type_id ,
1780 crt.price_list_header_id ,
1781 crt.business_process_id,
1782 cr.incident_id,
1783 cr.default_po_num,
1784 cr.inventory_org_id,
1785 cr.project_id,
1786 cr.task_id,
1787 cr.unit_number
1788 FROM csd_repairs cr,
1789 csd_repair_types_vl crt
1790 WHERE cr.repair_type_id = crt.repair_type_id
1791 and cr.repair_line_id = p_rep_line_id;
1792
1793 CURSOR get_revision(p_inv_item_id IN NUMBER,
1794 p_org_id IN NUMBER) IS
1795 SELECT
1796 revision
1797 FROM mtl_item_revisions
1798 WHERE inventory_item_id = p_inv_item_id
1799 and organization_id = p_org_id;
1800
1801 BEGIN
1802
1803 x_return_status := FND_API.G_RET_STS_SUCCESS;
1804
1805 --taklam
1806 -- return;
1807
1808 -- Initialize the table
1809 x_prod_txn_tbl.delete;
1810
1811
1812 OPEN repair_line_dtls(p_repair_line_id);
1813
1814 FETCH repair_line_dtls INTO
1815 l_repair_type_ref,
1816 l_auto_process_rma,
1817 l_inv_item_id,
1818 l_inv_revision,
1819 l_contract_id,
1820 l_unit_of_measure,
1821 l_quantity,
1822 l_instance_id,
1823 l_serial_number,
1824 l_cps_txn_billing_type_id,
1825 l_cpr_txn_billing_type_id,
1826 l_ls_txn_billing_type_id,
1827 l_lr_txn_billing_type_id,
1828 l_price_list_header_id,
1829 l_bus_process_id,
1830 l_incident_id,
1831 l_po_number, -- swai bug fix 4535829
1832 l_inv_org_id, -- inv_org_change vijay, 3/20/06
1833 l_project_id,
1834 l_task_id,
1835 l_unit_number;
1836
1837 IF repair_line_dtls%notfound then
1838 FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_REP_LINE_ID');
1839 FND_MESSAGE.SET_TOKEN('REPAIR_LINE_ID',p_repair_line_id);
1840 FND_MSG_PUB.ADD;
1841 RAISE FND_API.G_EXC_ERROR;
1842 END IF;
1843
1844 IF repair_line_dtls%isopen then
1845 CLOSE repair_line_dtls;
1846 END IF;
1847
1848 -- Get the price_list
1849 l_price_list_id := NVL(l_price_list_header_id,FND_PROFILE.value('CS_CHARGE_DEFAULT_PRICE_LIST'));
1850
1851 -- Get the return reason
1852 l_return_reason := FND_PROFILE.value('CSD_DEF_RMA_RETURN_REASON');
1853
1854 l_org_id := csd_process_util.get_org_id(l_incident_id);
1855 -- l_inv_org_id := csd_process_util.get_inv_org_id;
1856 l_revision := l_inv_revision;
1857
1858 IF l_repair_type_ref = 'RR' THEN
1859 -- Shipping customer product txn line
1860 x_prod_txn_tbl(1).repair_line_id := p_repair_line_id ;
1861 x_prod_txn_tbl(1).txn_billing_type_id := l_cps_txn_billing_type_id;
1862 x_prod_txn_tbl(1).action_code := 'CUST_PROD';
1863 x_prod_txn_tbl(1).source_instance_id := l_instance_id;
1864 x_prod_txn_tbl(1).source_serial_number := l_serial_number;
1865 x_prod_txn_tbl(1).action_type := 'SHIP' ;
1866 x_prod_txn_tbl(1).organization_id := l_org_id ;
1867 x_prod_txn_tbl(1).business_process_id := l_bus_process_id ;
1868 x_prod_txn_tbl(1).inventory_item_id := l_inv_item_id ;
1869 x_prod_txn_tbl(1).unit_of_measure_code := l_unit_of_measure ;
1870 x_prod_txn_tbl(1).quantity := l_quantity ;
1871 x_prod_txn_tbl(1).lot_number := FND_API.G_MISS_CHAR;
1872 x_prod_txn_tbl(1).price_list_id := l_price_list_id ;
1873 -- x_prod_txn_tbl(1).contract_id := l_contract_id ;
1874 x_prod_txn_tbl(1).sub_inventory := FND_API.G_MISS_CHAR;
1875 x_prod_txn_tbl(1).no_charge_flag := csd_process_util.get_no_chg_flag(l_cps_txn_billing_type_id);
1876 x_prod_txn_tbl(1).interface_to_om_flag := 'N' ;
1877 x_prod_txn_tbl(1).book_sales_order_flag := 'N' ;
1878 x_prod_txn_tbl(1).release_sales_order_flag := 'N' ;
1879 x_prod_txn_tbl(1).ship_sales_order_flag := 'N' ;
1880 x_prod_txn_tbl(1).process_txn_flag := 'N' ;
1881 x_prod_txn_tbl(1).revision := l_revision ;
1882 x_prod_txn_tbl(1).last_update_date := sysdate ;
1883 x_prod_txn_tbl(1).creation_date := sysdate ;
1884 x_prod_txn_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
1885 x_prod_txn_tbl(1).created_by := FND_GLOBAL.USER_ID;
1886 x_prod_txn_tbl(1).last_update_login := FND_GLOBAL.USER_ID;
1887 x_prod_txn_tbl(1).prod_txn_status := 'ENTERED';
1888 x_prod_txn_tbl(1).prod_txn_code := 'POST';
1889 x_prod_txn_tbl(1).project_id := l_project_id;
1890 x_prod_txn_tbl(1).task_id := l_task_id;
1891 x_prod_txn_tbl(1).unit_number := l_unit_number;
1892 x_prod_txn_tbl(1).inventory_org_id := l_inv_org_id;
1893 x_prod_txn_tbl(1).po_number := l_po_number; -- swai bug fix 4535829
1894
1895 END IF;
1896 EXCEPTION
1897 WHEN FND_API.G_EXC_ERROR THEN
1898 x_return_status := FND_API.G_RET_STS_ERROR ;
1899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1901 WHEN OTHERS THEN
1902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1903 END build_ship_prod_txn_tbl;
1904
1905
1906 End CSD_SPLIT_PKG;
1907