[Home] [Help]
PACKAGE BODY: APPS.MSC_SRP_RELEASE
Source
1 PACKAGE BODY MSC_SRP_RELEASE AS
2 /* $Header: MSCPSRPB.pls 120.14.12020000.3 2012/09/05 05:45:01 neelredd ship $ */
3
4 PROCEDURE log_message( p_user_info IN VARCHAR2) IS
5 BEGIN
6 FND_FILE.PUT_LINE(FND_FILE.LOG, p_user_info);
7 EXCEPTION
8 WHEN OTHERS THEN
9 RAISE;
10 END log_message;
11
12 PROCEDURE log_output( p_user_info IN VARCHAR2) IS
13 BEGIN
14 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_user_info);
15 EXCEPTION
16 WHEN OTHERS THEN
17 RAISE;
18 END log_output;
19
20 PROCEDURE Release_new_ERO ( errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY VARCHAR2,
22 p_batch_id IN number) IS
23
24 Cursor Ro_release_data( p_batch_id number) is
25 Select *
26 from MRP_ERO_RELEASE
27 where batch_id =p_batch_id
28 order by transaction_id ;
29
30
31 new_transaction_id boolean := FALSE;
32 lv_transaction_id number := -999 ;
33 lv_count number:=0 ;
34 lv_tran_count number := 0;
35 p_defective_parts_tbl Csp_repair_po_grp.defective_parts_tbl_Type; /* Table of records identifying
36 all the defective parts and
37 their quantities */
38 X_Msg_Data VARCHAR2(5000);
39 X_Return_Status VARCHAR2(1000);
40 X_Msg_Count NUMBER;
41 x_requisition_header_id NUMBER;
42 x_msg_index_out NUMBER;
43 p_msg_count NUMBER;
44 /* For security */
45 l_user_id NUMBER;
46 l_appl_id NUMBER;
47 l_src_org_id number := 0;
48 l_prev_src_org_id number := -99999;
49 l_level_id number;
50 l_level_value number;
51 lv_req_number number ;
52
53 Lv_repair_supplier_id MRP_ERO_RELEASE.REPAIR_SUPPLIER_ID%Type;
54 Lv_repair_org_id MRP_ERO_RELEASE.REPAIR_SUPPLIER_ORG_ID%Type;
55 Lv_repair_program MRP_ERO_RELEASE.REPAIR_PROGRAM%Type;
56 Lv_destination_org_id MRP_ERO_RELEASE.DESTINATION_ORG_ID%Type;
57 Lv_source_org_id MRP_ERO_RELEASE.SOURCE_ORG_ID%Type;
58 Lv_inventory_item_id MRP_ERO_RELEASE.INVENTORY_ITEM_ID%Type;
59 Lv_quantity MRP_ERO_RELEASE.QUANTITY%Type;
60 Lv_promise_date MRP_ERO_RELEASE.PROMISE_DATE%Type;
61 l_msg VARCHAR2(5000);
62 BEGIN
63 log_output(' Repair Purchase Order Release Report');
64 log_output('--------------------------------------------------------------------------------------------------');
65 log_output('');
66 log_output('Document Header ');
67 --log_output('in PWB Req No. ');
68 log_output('--------------------------------------------------------------------------------------------------');
69
70 begin
71 l_user_id := fnd_global.user_id();
72 l_appl_id := 724; -- Application id for Advanced Supply Chain Planning
73
74
75 For l_ero_release in Ro_release_data(p_batch_id) loop
76 Begin
77 New_transaction_id := FALSE;
78
79 If lv_count <> 0 and lv_transaction_id <> l_ero_release.transaction_id then
80 New_transaction_id := TRUE;
81 lv_tran_count := 0;
82 END IF ;
83
84 if new_transaction_id then
85 -- Get responsibility id
86 log_message(' l_ero_release.SOURCE_ORG_ID-'||l_ero_release.SOURCE_ORG_ID);
87
88 log_message(' l_user_id-'||l_user_id);
89 log_message(' l_appl_id-'||l_appl_id);
90
91 -- fnd_global.apps_initialize(l_user_id, l_level_value, l_appl_id);
92
93
94
95 LOG_MESSAGE('p_api_version :'|| 1.0);
96 LOG_MESSAGE('p_Init_Msg_List :'|| 'FND_API.G_FALSE');
97 LOG_MESSAGE('p_commit :'|| 'FND_API.G_FALSE');
98 LOG_MESSAGE('P_repair_supplier_id :'|| Lv_repair_supplier_id);
99 LOG_MESSAGE('P_repair_supplier_org_id :'|| Lv_repair_org_id);
100 LOG_MESSAGE('P_repair_program :'|| Lv_repair_program);
101 LOG_MESSAGE('P_dest_organization_id :'|| Lv_destination_org_id);
102 LOG_MESSAGE('P_source_organization_id :'|| Lv_source_org_id);
103 LOG_MESSAGE('P_repair_to_item_id :'|| Lv_inventory_item_id);
104 LOG_MESSAGE('P_quantity :'|| Lv_quantity);
105 LOG_MESSAGE('P_need_by_date :'|| Lv_promise_date);
106
107 For i in 1 .. P_defective_parts_tbl.COUNT loop
108 LOG_MESSAGE('P_defective_parts_tbl item :-'||i||' - '|| P_defective_parts_tbl(i).defective_item_id) ;
109 LOG_MESSAGE('P_defective_parts_tbl qty :-'||i||' - '|| P_defective_parts_tbl(i).defective_quantity) ;
110 end loop;
111
112 CSP_REPAIR_PO_GRP.CREATE_REPAIR_PO
113 (p_api_version => 1.0
114 ,p_Init_Msg_List => FND_API.G_FALSE
115 ,p_commit => FND_API.G_FALSE
116 ,P_repair_supplier_id => Lv_repair_supplier_id
117 ,P_repair_supplier_org_id => Lv_repair_org_id
118 ,P_repair_program => Lv_repair_program
119 ,P_dest_organization_id => Lv_destination_org_id
120 ,P_source_organization_id => Lv_source_org_id
121 ,P_repair_to_item_id => Lv_inventory_item_id
122 ,P_quantity => Lv_quantity
123 ,P_need_by_date => Lv_promise_date
124 ,P_defective_parts_tbl => P_defective_parts_tbl
125 ,x_requisition_header_id => X_requisition_header_id
126 ,x_return_status => X_Return_Status
127 ,x_msg_count => X_Msg_Count
128 ,x_msg_data => X_Msg_Data
129 );
130
131
132 --COMMIT;
133 IF (X_Return_Status <> 'S') THEN
134
135 log_message('Number of Error Messages : '||TO_CHAR(x_msg_count));
136
137 FOR i IN 1..X_Msg_Count LOOP
138 FND_MSG_PUB.Get(p_msg_index => i,
139 p_encoded => 'F',
140 p_data => x_msg_data,
141 p_msg_index_out => x_msg_index_out );
142 log_message('message data ='||X_Msg_Data);
143 END LOOP;
144 END IF ;
145
146 IF X_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
147 LOG_MESSAGE('Error in Repair Req creation : FND_API.G_RET_STS_UNEXP_ERROR');
148 rollback ;
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
151 LOG_MESSAGE('Error in Repair Req creation : FND_API.G_RET_STS_ERROR');
152 rollback ;
153 RAISE FND_API.G_EXC_ERROR;
154 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
155 log_message('Successful in Creating Repair Req .');
156 log_output('Successful in Creating Repair Req .');
157 select requisition_number into lv_req_number from csp_repair_po_headers
158 where REQUISITION_HEADER_ID=X_requisition_header_id;
159 log_output('External Repair Requisition number :' ||lv_req_number);
160 Commit ;
161 END IF;
162
163 lv_transaction_id := NULL;
164
165 For i in 1 .. P_defective_parts_tbl.COUNT loop
166 P_defective_parts_tbl(i).defective_item_id := NULL;
167 P_defective_parts_tbl(i).defective_quantity := NULL;
168 end loop;
169
170 Lv_repair_supplier_id :=NULL;
171 Lv_repair_org_id :=NULL;
172 Lv_repair_program := NULL;
173 Lv_destination_org_id :=NULL;
174 Lv_source_org_id := NULL;
175 Lv_inventory_item_id :=NULL;
176 Lv_quantity := NULL;
177 Lv_promise_date:=NULL;
178
179
180
181 END IF ;
182 EXCEPTION
183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
184 LOG_MESSAGE('Error in Release_new_ERO : FND_API.G_EXC_UNEXPECTED_ERROR');
185 LOG_MESSAGE(SQLERRM);
186 retcode := 1;
187 rollback ;
188 log_message('Transaction rolled back');
189 log_message('----------------------------------------------------------');
190
191 WHEN FND_API.G_EXC_ERROR THEN
192 LOG_MESSAGE('Error in Release_new_ERO : FND_API.G_EXC_ERROR');
193 LOG_MESSAGE(SQLERRM);
194 retcode := 1;
195 rollback ;
196 log_message('Transaction rolled back');
197 log_message('----------------------------------------------------------');
198 WHEN OTHERS THEN
199 LOG_MESSAGE('Error in Release_new_ERO : Err OTHERS');
200 LOG_MESSAGE(SQLERRM);
201 retcode := 1;
202 log_message('Transaction rolled back');
203 log_message('----------------------------------------------------------');
204 END ; -- Begin at For Loop
205
206 Lv_count:= lv_count+1;
207 lv_tran_count := lv_tran_count+1;
208
209 lv_transaction_id := l_ero_release.transaction_id;
210
211 P_defective_parts_tbl(lv_tran_count).defective_item_id := l_ero_release.defective_item_id;
212 P_defective_parts_tbl(lv_tran_count).defective_quantity := l_ero_release.DEFECTIVE_ITEM_QTY;
213
214
215 Lv_repair_supplier_id :=l_ero_release.repair_supplier_id;
216 Lv_repair_org_id :=l_ero_release.repair_supplier_org_id;
217 Lv_repair_program := l_ero_release.repair_program;
218 Lv_destination_org_id :=l_ero_release.destination_org_id;
219 Lv_source_org_id := l_ero_release.source_org_id;
220 Lv_inventory_item_id :=l_ero_release.inventory_item_id;
221 Lv_quantity := l_ero_release.quantity;
222 Lv_promise_date:=l_ero_release.promise_date;
223
224
225 End loop;
226
227 /* for last record */
228 -- Get responsibility id
229 log_message('Lv_source_org_id-'||Lv_source_org_id);
230
231 log_message('l_user_id-'||l_user_id);
232 log_message('l_appl_id-'||l_appl_id);
233
234 --fnd_global.apps_initialize(l_user_id, l_level_value, l_appl_id);
235
236 LOG_MESSAGE('p_api_version :'|| 1.0);
237 LOG_MESSAGE('p_Init_Msg_List :'|| 'FND_API.G_FALSE');
238 LOG_MESSAGE('p_commit :'|| 'FND_API.G_FALSE');
239 LOG_MESSAGE('P_repair_supplier_id :'|| Lv_repair_supplier_id);
240 LOG_MESSAGE('P_repair_supplier_org_id :'|| Lv_repair_org_id);
241 LOG_MESSAGE('P_repair_program :'|| Lv_repair_program);
242 LOG_MESSAGE('P_dest_organization_id :'|| Lv_destination_org_id);
243 LOG_MESSAGE('P_source_organization_id :'|| Lv_source_org_id);
244 LOG_MESSAGE('P_repair_to_item_id :'|| Lv_inventory_item_id);
245 LOG_MESSAGE('P_quantity :'|| Lv_quantity);
246 LOG_MESSAGE('P_need_by_date :'|| Lv_promise_date);
247
248 For i in 1 .. P_defective_parts_tbl.COUNT loop
249
250 LOG_MESSAGE('P_defective_parts_tbl item :-'||i||'-' ||P_defective_parts_tbl(i).defective_item_id) ;
251 LOG_MESSAGE('P_defective_parts_tbl qty :-'||i||'-'|| P_defective_parts_tbl(i).defective_quantity) ;
252 end loop;
253
254 CSP_REPAIR_PO_GRP.CREATE_REPAIR_PO
255 (p_api_version => 1.0
256 ,p_Init_Msg_List => FND_API.G_FALSE
257 ,p_commit => FND_API.G_FALSE
258 ,P_repair_supplier_id => Lv_repair_supplier_id
259 ,P_repair_supplier_org_id => Lv_repair_org_id
260 ,P_repair_program => Lv_repair_program
261 ,P_dest_organization_id => Lv_destination_org_id
262 ,P_source_organization_id => Lv_source_org_id
263 ,P_repair_to_item_id => Lv_inventory_item_id
264 ,P_quantity => Lv_quantity
265 ,P_need_by_date => Lv_promise_date
266 ,P_defective_parts_tbl => P_defective_parts_tbl
267 ,x_requisition_header_id => X_requisition_header_id
268 ,x_return_status => X_Return_Status
269 ,x_msg_count => X_Msg_Count
270 ,x_msg_data => X_Msg_Data
271 );
272
273 -- COMMIT;
274
275 IF (X_Return_Status <> 'S') THEN
276
277 log_message('Number of Error Messages : '||TO_CHAR(x_msg_count));
278
279 FOR i IN 1..X_Msg_Count LOOP
280 FND_MSG_PUB.Get(p_msg_index => i,
281 p_encoded => 'F',
282 p_data => x_msg_data,
283 p_msg_index_out => x_msg_index_out );
284 log_message('message data ='||X_Msg_Data);
285 END LOOP;
286 END IF ;
287
288 IF X_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
289 LOG_MESSAGE('Error in Repair req creation : FND_API.G_RET_STS_UNEXP_ERROR');
290 rollback ;
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
293 LOG_MESSAGE('Error in Repair Req creation : FND_API.G_RET_STS_ERROR');
294 rollback ;
295 RAISE FND_API.G_EXC_ERROR;
296 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
297 log_message('Successful in Creating Repair Req .');
298 log_output('Successful in Creating Repair Req .');
299 select requisition_number into lv_req_number from csp_repair_po_headers
300 where REQUISITION_HEADER_ID=X_requisition_header_id;
301 log_output('External Repair Requisition number :' ||lv_req_number);
302 Commit ;
303 END IF;
304 EXCEPTION
305 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
306 LOG_MESSAGE('Error in Release_new_ERO : FND_API.G_EXC_UNEXPECTED_ERROR');
307 LOG_MESSAGE(SQLERRM);
308 retcode := 1;
309 rollback ;
310 log_message('Transaction rolled back');
311 log_message('----------------------------------------------------------');
312
313 WHEN FND_API.G_EXC_ERROR THEN
314 LOG_MESSAGE('Error in Release_new_ERO : FND_API.G_EXC_ERROR');
315 LOG_MESSAGE(SQLERRM);
316 retcode := 1;
317 rollback ;
318 log_message('Transaction rolled back');
319 log_message('----------------------------------------------------------');
320 WHEN OTHERS THEN
321 LOG_MESSAGE('Error in Release_new_ERO : Err OTHERS');
322 LOG_MESSAGE(SQLERRM);
323 retcode := 1;
324 log_message('Transaction rolled back');
325 log_message('----------------------------------------------------------');
326 End;
327
328 DELETE FROM MRP_ERO_RELEASE WHERE BATCH_ID=p_batch_id ;
329 Commit;
330
331 END Release_new_ERO;
332
333 Procedure update_iro (errbuf OUT NOCOPY VARCHAR2,
334 retcode OUT NOCOPY VARCHAR2,
335 p_repair_line_id IN NUMBER,
336 p_quantity IN NUMBER:= NULL,
337 p_promise_date IN DATE:= NULL)
338 IS
339 Cursor c1(p_repair_line_id number) is
340 select * from csd_repairs
341 where repair_line_id = p_repair_line_id ;
342
343 l_repair_rec CSD_REPAIRS%rowtype;
344 l_Init_Msg_List VARCHAR2(1000):= FND_API.G_FALSE;
345 l_Commit VARCHAR2(1000):= FND_API.G_FALSE;
346 l_validation_level NUMBER:=FND_API.G_VALID_LEVEL_FULL;
347 Q_REPLN_Rec CSD_REPAIRS_PUB.REPLN_Rec_Type;
348 X_Return_Status VARCHAR2(1000);
349 p_Msg_Count NUMBER;
350 X_Msg_Data VARCHAR2(5000);
351 l_msg VARCHAR2(5000);
352 BEGIN
353 retcode:=0 ;
354 open c1(p_repair_line_id);
355 fetch c1 into l_repair_rec;
356 close c1;
357 Q_REPLN_Rec.REPAIR_NUMBER:= l_repair_rec.REPAIR_NUMBER;
358 Q_REPLN_Rec.INCIDENT_ID:= l_repair_rec.INCIDENT_ID;
359 Q_REPLN_Rec.INVENTORY_ITEM_ID:= l_repair_rec.INVENTORY_ITEM_ID;
360 Q_REPLN_Rec.CUSTOMER_PRODUCT_ID:= l_repair_rec.CUSTOMER_PRODUCT_ID;
361 Q_REPLN_Rec.UNIT_OF_MEASURE:=l_repair_rec.UNIT_OF_MEASURE;
362 Q_REPLN_Rec.REPAIR_TYPE_ID:=l_repair_rec.REPAIR_TYPE_ID;
363 Q_REPLN_Rec.RESOURCE_GROUP:=NULL;
364 Q_REPLN_Rec.RESOURCE_ID:=l_repair_rec.RESOURCE_ID;
365 Q_REPLN_Rec.PROJECT_ID:=l_repair_rec.PROJECT_ID;
366 Q_REPLN_Rec.TASK_ID:= l_repair_rec.TASK_ID;
367 Q_REPLN_Rec.UNIT_NUMBER:= l_repair_rec.UNIT_NUMBER;
368 Q_REPLN_Rec.CONTRACT_LINE_ID:=l_repair_rec.CONTRACT_LINE_ID;
369 Q_REPLN_Rec.AUTO_PROCESS_RMA:=l_repair_rec.AUTO_PROCESS_RMA;
370 Q_REPLN_Rec.REPAIR_MODE:= l_repair_rec.REPAIR_MODE;
371 Q_REPLN_Rec.OBJECT_VERSION_NUMBER:=l_repair_rec.OBJECT_VERSION_NUMBER;
372 Q_REPLN_Rec.ITEM_REVISION:=l_repair_rec.ITEM_REVISION;
373 Q_REPLN_Rec.INSTANCE_ID:=l_repair_rec.INSTANCE_ID;
374 Q_REPLN_Rec.STATUS:= l_repair_rec.STATUS;
375 Q_REPLN_Rec.STATUS_REASON_CODE:=l_repair_rec.STATUS_REASON_CODE;
376 Q_REPLN_Rec.DATE_CLOSED:=l_repair_rec.DATE_CLOSED;
377 Q_REPLN_Rec.APPROVAL_REQUIRED_FLAG:=l_repair_rec.APPROVAL_REQUIRED_FLAG;
378 Q_REPLN_Rec.APPROVAL_STATUS:=l_repair_rec.APPROVAL_STATUS;
379 Q_REPLN_Rec.SERIAL_NUMBER:=l_repair_rec.SERIAL_NUMBER;
380 IF p_promise_date is not null then
381 Q_REPLN_Rec.PROMISE_DATE:= p_promise_date;-- NULL;-- '2/20/2007'
382 ELSE
383 Q_REPLN_Rec.PROMISE_DATE:=l_repair_rec.PROMISE_DATE;
384 END IF;
385 Q_REPLN_Rec.ATTRIBUTE_CATEGORY:=l_repair_rec.ATTRIBUTE_CATEGORY;
386 Q_REPLN_Rec.ATTRIBUTE1:=l_repair_rec.ATTRIBUTE1;
387 Q_REPLN_Rec.ATTRIBUTE2:=l_repair_rec.ATTRIBUTE2;
388 Q_REPLN_Rec.ATTRIBUTE3:=l_repair_rec.ATTRIBUTE3;
389 Q_REPLN_Rec.ATTRIBUTE4:=l_repair_rec.ATTRIBUTE4;
390 Q_REPLN_Rec.ATTRIBUTE5:=l_repair_rec.ATTRIBUTE5;
391 Q_REPLN_Rec.ATTRIBUTE6:=l_repair_rec.ATTRIBUTE6;
392 Q_REPLN_Rec.ATTRIBUTE7:=l_repair_rec.ATTRIBUTE7;
393 Q_REPLN_Rec.ATTRIBUTE8:=l_repair_rec.ATTRIBUTE8;
394 Q_REPLN_Rec.ATTRIBUTE9:=l_repair_rec.ATTRIBUTE9;
395 Q_REPLN_Rec.ATTRIBUTE10:=l_repair_rec.ATTRIBUTE10;
396 Q_REPLN_Rec.ATTRIBUTE11:=l_repair_rec.ATTRIBUTE11;
397 Q_REPLN_Rec.ATTRIBUTE12:=l_repair_rec.ATTRIBUTE12;
398 Q_REPLN_Rec.ATTRIBUTE13:=l_repair_rec.ATTRIBUTE13;
399 Q_REPLN_Rec.ATTRIBUTE14:=l_repair_rec.ATTRIBUTE14;
400 Q_REPLN_Rec.ATTRIBUTE15:=l_repair_rec.ATTRIBUTE15;
401 IF p_quantity is not null then
402 Q_REPLN_Rec.QUANTITY:=p_quantity;
403 ELSE
404 Q_REPLN_Rec.QUANTITY:=l_repair_rec.quantity ;
405 END IF ;
406 Q_REPLN_Rec.QUANTITY_IN_WIP:=l_repair_rec.QUANTITY_IN_WIP;
407 Q_REPLN_Rec.QUANTITY_RCVD:=l_repair_rec.QUANTITY_RCVD;
408 Q_REPLN_Rec.QUANTITY_SHIPPED:= l_repair_rec.QUANTITY_SHIPPED;
409 Q_REPLN_Rec.CURRENCY_CODE:=l_repair_rec.CURRENCY_CODE;
410 Q_REPLN_Rec.DEFAULT_PO_NUM:=l_repair_rec.DEFAULT_PO_NUM;
411 Q_REPLN_Rec.REPAIR_GROUP_ID:=l_repair_rec.REPAIR_GROUP_ID;
412 Q_REPLN_Rec.RO_TXN_STATUS:=l_repair_rec.RO_TXN_STATUS;
413 Q_REPLN_Rec.ORDER_LINE_ID:=l_repair_rec.ORDER_LINE_ID;
414 Q_REPLN_Rec.ORIGINAL_SOURCE_REFERENCE :=l_repair_rec.ORIGINAL_SOURCE_REFERENCE;
415 Q_REPLN_Rec.ORIGINAL_SOURCE_HEADER_ID :=l_repair_rec.ORIGINAL_SOURCE_HEADER_ID;
416 Q_REPLN_Rec.ORIGINAL_SOURCE_LINE_ID :=l_repair_rec.ORIGINAL_SOURCE_LINE_ID;
417 Q_REPLN_Rec.PRICE_LIST_HEADER_ID :=l_repair_rec.PRICE_LIST_HEADER_ID;
418 Q_REPLN_Rec.SUPERCESSION_INV_ITEM_ID :=l_repair_rec.SUPERCESSION_INV_ITEM_ID;
419 Q_REPLN_Rec.FLOW_STATUS_ID:=l_repair_rec.FLOW_STATUS_ID;
420 Q_REPLN_Rec.FLOW_STATUS_CODE:=null;
421 Q_REPLN_Rec.FLOW_STATUS:=null;
422 Q_REPLN_Rec.INVENTORY_ORG_ID:=l_repair_rec.INVENTORY_ORG_ID;
423 Q_REPLN_Rec.PROBLEM_DESCRIPTION:=l_repair_rec.PROBLEM_DESCRIPTION;
424 Q_REPLN_Rec.RO_PRIORITY_CODE:=l_repair_rec.RO_PRIORITY_CODE;
425
426 CSD_Repairs_PUB.Update_Repair_Order(
427 P_Api_Version_Number => 1.0,
428 P_Init_Msg_List => l_Init_Msg_List,
429 P_Commit => l_Commit,
430 p_validation_level => l_validation_level,
431 P_REPAIR_LINE_ID => P_REPAIR_LINE_ID,
432 P_REPLN_Rec => Q_REPLN_Rec,
433 X_Return_Status => X_Return_Status,
434 X_Msg_Count => p_Msg_Count,
435 X_Msg_Data => X_Msg_Data);
436
437 IF p_msg_count IS NOT NULL THEN
438 IF p_msg_count = 1 THEN
439 l_msg := fnd_msg_pub.get(p_msg_index => 1,
440 p_encoded => 'F' );
441 log_message(l_msg);
442 ELSIF p_msg_count > 1 THEN
443 FOR i IN 1..p_msg_count
444 LOOP
445 l_msg := fnd_msg_pub.get(p_msg_index => i,
446 p_encoded => 'F' );
447 Fnd_file.put_line(fnd_file.LOG,l_msg);
448 END LOOP;
449 END IF;
450 END IF;
451 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
452 LOG_MESSAGE('Error in updating reapir order : FND_API.G_RET_STS_UNEXP_ERROR');
453 rollback ;
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
456 LOG_MESSAGE('Error in updating reapir order : FND_API.G_RET_STS_ERROR');
457 rollback ;
458 RAISE FND_API.G_EXC_ERROR;
459 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
460 log_message('Successful in Updating repair order .');
461 log_message('repair number :' ||l_repair_rec.repair_number);
462 END IF;
463 EXCEPTION
464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
465 LOG_MESSAGE('Error in updating reapir order : FND_API.G_EXC_UNEXPECTED_ERROR');
466 LOG_MESSAGE(SQLERRM);
467 retcode := 1;
468 rollback ;
469 log_message('Transaction rolled back');
470 log_message('----------------------------------------------------------');
471
472 WHEN FND_API.G_EXC_ERROR THEN
473 LOG_MESSAGE('Error in updating reapir order : FND_API.G_EXC_ERROR');
474 LOG_MESSAGE(SQLERRM);
475 retcode := 1;
476 rollback ;
477 log_message('Transaction rolled back');
478 log_message('----------------------------------------------------------');
479 WHEN OTHERS THEN
480 LOG_MESSAGE('Error in updating reapir order : Err OTHERS');
481 LOG_MESSAGE(SQLERRM);
482 retcode := 1;
483 rollback ;
484 log_message('Transaction rolled back');
485 log_message('----------------------------------------------------------');
486 END update_iro;
487
488
489 PROCEDURE MSC_RELEASE_IRO( p_user_name IN VARCHAR2,
490 p_resp_name IN VARCHAR2,
491 p_application_name IN VARCHAR2,
492 p_application_id IN NUMBER,
493 p_batch_id IN number,
494 p_load_type IN number,
495 arg_iro_load_id IN OUT NOCOPY Number
496 ) IS
497
498
499 l_request number;
500 l_result BOOLEAN;
501 lv_user_name VARCHAR2(100);
502 lv_resp_name VARCHAR2(100);
503
504 l_user_id NUMBER;
505 lv_log_msg varchar2(500);
506
507 BEGIN
508 BEGIN
509 SELECT USER_ID
510 INTO l_user_id
511 FROM FND_USER
512 WHERE USER_NAME = p_user_name;
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515 LOG_MESSAGE('Error in MSC_RELEASE_IRO : NO_USER_DEFINED');
516 raise_application_error (-20001, 'NO_USER_DEFINED');
517 WHEN OTHERS THEN RAISE;
518 END;
519
520 IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_RELEASE,lv_log_msg) THEN
521 MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_RELEASE,
522 l_user_id,
523 -1, --l_resp_id,
524 -1 --l_application_id
525 );
526 ELSE
527 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
528 raise_application_error (-20001, lv_log_msg);
529 END IF;
530
531 l_result := FND_REQUEST.SET_MODE(TRUE);
532
533 --=====================================
534 l_request := FND_REQUEST.SUBMIT_REQUEST
535 ('MSC',
536 'MSCRLIRO',
537 'Release IRO to Source',
538 null,
539 FALSE,
540 p_batch_id);
541
542 IF nvl(l_request,0) = 0 THEN
543 LOG_MESSAGE('Error in MSC_RELEASE_IRO');
544 ELSE
545 IF p_load_type = IRO_LOAD THEN
546 arg_iro_load_id := l_request;
547 LOG_MESSAGE('Concurrent Request ID For IRO Load : ' || arg_iro_load_id);
548 END IF;
549 END IF;
550 LOG_MESSAGE('MSC_RELEASE_IRO completed successfully');
551
552 END MSC_RELEASE_IRO;
553
554
555 Procedure Release_new_IRO (
556 errbuf OUT NOCOPY VARCHAR2,
557 retcode OUT NOCOPY VARCHAR2,
558 p_batch_id IN number) IS
559 l_iso_header_id Number ;
560 l_req_header_id Number ;
561 l_iso_header_id1 Number ;
562 l_iso_header_id2 Number ;
563 l_req_header_id1 Number ;
564 l_req_header_id2 Number ;
565 p_msg_count NUMBER;
566 l_msg VARCHAR2(5000);
567 x_return_status VARCHAR2(1000);
568 x_msg_data VARCHAR2(2000);
569
570 lv_repair_line_id number;
571 x_service_request_number VARCHAR2(3000);
572
573 P_Init_Msg_List VARCHAR2(1000);
574 P_Commit VARCHAR2(1000);
575 p_validation_level NUMBER;
576 P_REPAIR_LINE_ID NUMBER;
577 Q_REPLN_Rec CSD_REPAIRS_PUB.REPLN_Rec_Type;
578 p_create_default_logistics VARCHAR2(100) := 'N';
579
580 X_REPAIR_NUMBER VARCHAR2(1000);
581 X_Msg_Count NUMBER;
582 global_retcode NUMBER := 0;
583
584 Cursor Ro_release_data( p_batch_id number) is
585 Select Transaction_id,Quantity,in_req_quantity,out_req_quantity,In_req_transaction_id,
586 Out_req_transaction_id,Batch_id,Inventory_item_id
587 Uom_code,Organization_id,Promise_date
588 from MRP_IRO_RELEASE
589 where batch_id =p_batch_id
590 and load_type = IRO_LOAD
591 AND In_req_transaction_id is not NULL;
592
593 Cursor Ro_release_OH_data( p_batch_id number) is
594 Select distinct Transaction_id, Quantity,
595 Out_req_transaction_id, Batch_id, Inventory_item_id,
596 Uom_code, Organization_id, Promise_date ,deliver_to_location_id,
597 src_organization_id,LOAD_TYPE
598 from MRP_IRO_RELEASE
599 where batch_id =p_batch_id
600 and load_type =IRO_LOAD
601 and in_req_transaction_id is null ;
602
603 p_service_request_rec CS_SERVICEREQUEST_PUB.SERVICE_REQUEST_REC_TYPE;
604 p_notes CS_SERVICEREQUEST_PUB.NOTES_TABLE;
605 p_contacts CS_SERVICEREQUEST_PUB.CONTACTS_TABLE;
606 x_request_id NUMBER;
607 x_request_number VARCHAR2(2000);
608 x_interaction_id NUMBER;
609 x_workflow_process_id NUMBER := NULL;--2271;
610 x_msg_index_out NUMBER;
611 x_individual_owner NUMBER;
612 x_group_owner NUMBER;
613 x_individual_type VARCHAR2(30);
614 v_lang VARCHAR2(30);
615 l_customer_id NUMBER;
616 l_address_id NUMBER;
617 l_site_use_id NUMBER;
618 l_customer_NAME VARCHAR2(2000);
619 l_party_id NUMBER;
620
621
622 --Q_REPLN_Rec CSD_REPAIRS_PUB.REPLN_Rec_Type;
623 --p_create_default_logistics VARCHAR2(100) := 'N';
624 --P_REPAIR_LINE_ID NUMBER;
625 P_REPAIR_TYPE NUMBER;
626 X_REPAIR_LINE_ID NUMBER;
627 l_currency_code VARCHAR2(100);
628 --X_REPAIR_NUMBER VARCHAR2(1000);
629 l_customer_contact_id NUMBER;
630 l_user_id NUMBER;
631 l_group_id NUMBER;
632 x_wipEntityID NUMBER;
633
634 l_wip_job_name VARCHAR2(2000);
635 px_REPAIR_JOB_XREF_ID NUMBER;
636 l_return_status VARCHAR2(2000);
637 BEGIN
638 log_output(' Internal Requisition/ISO Release and Reschedule Report');
639 log_output(' ------------------------------------------------------');
640 log_output('');
641 log_output('Order No Load Type ISO No Quantity Schedule Shipment Date Schedule Arrival Date Internal Need By Date');
642 log_output('in PWB Req No. ');
643 log_output('----------- ----------- -------- -------- ---------------------- ---------------------- -------- ----------------------');
644
645
646
647 Delete from MRP_IRO_RELEASE mir
648 where batch_id =p_batch_id
649 And in_req_transaction_id is null
650 AND load_type = IRO_LOAD
651 And exists ( select 1 from mrp_iro_release mir1
652 Where mir1.batch_id =p_batch_id
653 And mir1.in_req_transaction_id is not null
654 And mir1.transaction_id = mir.transaction_id
655 );
656
657 -- Added to process repair orders with no inward movement of defectives
658 -- ie onhand pegged defective supplies
659
660 For l_ro_release in Ro_release_OH_data(p_batch_id) loop
661
662 BEGIN
663 LOG_MESSAGE('Creating MOVE OUT lines');
664
665 Update mrp_org_transfer_release
666 set part_condition ='G'
667 where transaction_id= l_ro_release.Out_req_transaction_id;
668
669 mrp_create_schedule_iso.Create_IR_ISO(errbuf,retcode ,l_req_header_id,l_iso_header_id, l_ro_release.Out_req_transaction_id, l_ro_release.batch_id );
670 l_iso_header_id1 := l_iso_header_id;
671 l_req_header_id1:= l_req_header_id ;
672 if (retcode=0 ) then
673
674 LOG_MESSAGE('Creating Repair Incident');
675 CS_SERVICEREQUEST_PUB.initialize_rec(p_service_request_rec);
676 p_service_request_rec.request_date := SYSDATE;
677 p_service_request_rec.type_name := '';
678
679 p_service_request_rec.type_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_TYPE'));
680 if (p_service_request_rec.type_id <> 4) Then
681 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Type-');
682 end if;
683 p_service_request_rec.status_id := 1; -- Open
684 p_service_request_rec.status_name := '';
685
686 p_service_request_rec.severity_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_SEVERITY'));
687 if (p_service_request_rec.severity_id <> 4) Then
688 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Severity-');
689 end if;
690
691 p_service_request_rec.severity_name := '';
692
693 p_service_request_rec.urgency_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_URGENCY'));
694 if (p_service_request_rec.urgency_id <> 44) Then
695 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Urgency-');
696 end if;
697
698 p_service_request_rec.urgency_name := '';
699 p_service_request_rec.closed_date := TO_DATE('');
700
701 p_service_request_rec.owner_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_OWNER'));
702 if (p_service_request_rec.owner_id is null) Then
703 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Owner-');
704 end if;
705
706 -- Get Customer ID
707 BEGIN
708 log_message('Source organization id => ' || l_ro_release.src_organization_id);
709
710 po_customers_sv.get_cust_details(l_ro_release.deliver_to_location_id,
711 l_customer_id,
712 l_address_id,
713 l_site_use_id,
714 l_ro_release.src_organization_id);
715
716 /* select cust_account_id,account_number
717 into l_acct_id,l_acct_number
718 from hz_cust_accounts_all
719 where cust_account_id = l_customer_id -- bug # 8299478
720 and customer_type ='I'
721 and rownum < 2;
722 */
723
724 select hp.party_number, hp.party_id
725 into l_customer_NAME,l_party_id
726 from hz_parties hp,hz_cust_accounts_all hca --ra_customers
727 where hp.party_id = hca.party_id
728 and hca.cust_account_id = l_customer_id -- bug # 8299478
729 and hca.customer_type ='I'
730 and rownum < 2;
731 Exception
732 when others then
733 LOG_MESSAGE('Error in Getting Customer Details for the Incident' ||l_customer_id);
734 LOG_MESSAGE(SQLERRM);
735 RAISE;
736 END;
737 p_service_request_rec.owner_group_id := NULL; -- CS_SR_DEFAULT_GROUP_OWNER
738 p_service_request_rec.publish_flag := '';
739 p_service_request_rec.SUMMARY := 'DEPOT REPAIR';
740 p_service_request_rec.caller_type := 'ORGANIZATION';
741 p_service_request_rec.customer_id := l_party_id ; -- bug # 8299478
742 p_service_request_rec.customer_number := NULL;
743 p_service_request_rec.employee_id := NULL;
744 p_service_request_rec.employee_number := '';
745 p_service_request_rec.verify_cp_flag := FND_API.G_MISS_CHAR;
746 p_service_request_rec.customer_product_id := NULL;
747 p_service_request_rec.platform_id := NULL;
748 p_service_request_rec.platform_version_id := NULL;
749 p_service_request_rec.cp_component_id := NULL;
750 p_service_request_rec.cp_component_version_id := NULL;
751 p_service_request_rec.cp_subcomponent_id := NULL;
752 p_service_request_rec.cp_subcomponent_version_id := NULL;
753 p_service_request_rec.language_id := NULL;
754 Begin
755 select userenv('LANG')
756 Into v_lang from dual;
757
758 p_service_request_rec.LANGUAGE := v_lang;
759 End;
760 p_service_request_rec.cp_ref_number := NULL;
761
762 p_service_request_rec.inventory_item_id := l_ro_release.Inventory_item_id;
763 p_service_request_rec.inventory_item_conc_segs := '';
764 p_service_request_rec.inventory_item_segment1 := '';
765 p_service_request_rec.inventory_item_segment2 := '';
766 p_service_request_rec.inventory_item_segment3 := '';
767 p_service_request_rec.inventory_item_segment4 := '';
768 p_service_request_rec.inventory_item_segment5 := '';
769 p_service_request_rec.inventory_item_segment6 := '';
770 p_service_request_rec.inventory_item_segment7 := '';
771 p_service_request_rec.inventory_item_segment8 := '';
772 p_service_request_rec.inventory_item_segment9 := '';
773 p_service_request_rec.inventory_item_segment10 := '';
774 p_service_request_rec.inventory_item_segment11 := '';
775 p_service_request_rec.inventory_item_segment12 := '';
776 p_service_request_rec.inventory_item_segment13 := '';
777 p_service_request_rec.inventory_item_segment14 := '';
778 p_service_request_rec.inventory_item_segment15 := '';
779 p_service_request_rec.inventory_item_segment16 := '';
780 p_service_request_rec.inventory_item_segment17 := '';
781 p_service_request_rec.inventory_item_segment18 := '';
782 p_service_request_rec.inventory_item_segment19 := '';
783 p_service_request_rec.inventory_item_segment20 := '';
784 p_service_request_rec.inventory_item_vals_or_ids := '';
785
786 p_service_request_rec.inventory_org_id := l_ro_release.Organization_id;
787 p_service_request_rec.current_serial_number := '';
788 p_service_request_rec.original_order_number := NULL;
789 p_service_request_rec.purchase_order_num := '';
790 p_service_request_rec.problem_code := '';
791 p_service_request_rec.exp_resolution_date := TO_DATE('');
792 p_service_request_rec.install_site_use_id := NULL;
793 p_service_request_rec.request_attribute_1 := '';
794 p_service_request_rec.request_attribute_2 := '';
795 p_service_request_rec.request_attribute_3 := '';
796 p_service_request_rec.request_attribute_4 := '';
797 p_service_request_rec.request_attribute_5 := '';
798 p_service_request_rec.request_attribute_6 := '';
799 p_service_request_rec.request_attribute_7 := '';
800 p_service_request_rec.request_attribute_8 := '';
801 p_service_request_rec.request_attribute_9 := '';
802 p_service_request_rec.request_attribute_10 := '';
803 p_service_request_rec.request_attribute_11 := '';
804 p_service_request_rec.request_attribute_12 := '';
805 p_service_request_rec.request_attribute_13 := '';
806 p_service_request_rec.request_attribute_14 := '';
807 p_service_request_rec.request_attribute_15 := '';
808 p_service_request_rec.request_context := '';
809 p_service_request_rec.bill_to_site_use_id := NULL;
810 p_service_request_rec.bill_to_contact_id := NULL;
811 p_service_request_rec.ship_to_site_use_id := NULL;
812 p_service_request_rec.ship_to_contact_id := NULL;
813 p_service_request_rec.resolution_code := '';
814 p_service_request_rec.act_resolution_date := TO_DATE('');
815 p_service_request_rec.public_comment_flag := '';
816 p_service_request_rec.parent_interaction_id := NULL;
817 p_service_request_rec.contract_service_id := NULL;
818 p_service_request_rec.contract_service_number := '';
819 p_service_request_rec.contract_id := NULL;
820 p_service_request_rec.project_number := '';
821 p_service_request_rec.qa_collection_plan_id := NULL;
822
823 p_service_request_rec.account_id := l_customer_id;
824
825 p_service_request_rec.resource_type := 'RS_EMPLOYEE';
826 p_service_request_rec.resource_subtype_id := NULL;
827 p_service_request_rec.cust_po_number := '';
828 p_service_request_rec.cust_ticket_number := '';
829 p_service_request_rec.sr_creation_channel := 'Phone';
830 p_service_request_rec.obligation_date := TO_DATE('');
831 p_service_request_rec.time_zone_id := NULL;
832 p_service_request_rec.time_difference := NULL;
833 p_service_request_rec.site_id := NULL;
834 p_service_request_rec.customer_site_id := NULL;
835 p_service_request_rec.territory_id := NULL;
836 p_service_request_rec.initialize_flag := '';
837 p_service_request_rec.cp_revision_id := NULL;
838 p_service_request_rec.inv_item_revision := '';
839 p_service_request_rec.inv_component_id := NULL;
840 p_service_request_rec.inv_component_version := '';
841 p_service_request_rec.inv_subcomponent_id := NULL;
842 p_service_request_rec.inv_subcomponent_version := '';
843 p_notes(1).note := '';
844 p_notes(1).note_detail := '';
845 p_notes(1).note_type := '';
846 p_notes(1).note_context_type_01 := '';
847 p_notes(1).note_context_type_id_01 := NULL;
848 p_notes(1).note_context_type_02 := '';
849 p_notes(1).note_context_type_id_02 := NULL;
850 p_notes(1).note_context_type_03 := '';
851 p_notes(1).note_context_type_id_03 := NULL;
852 p_contacts(1).sr_contact_point_id := NULL;
853
854 /* For Getting a contact point for this customer */
855 BEGIN
856 SELECT r.party_id
857 INTO l_customer_contact_id
858 FROM Hz_Parties sub,
859 Hz_Relationships r,
860 Hz_Parties obj
861 WHERE r.object_id = l_party_id
862 --AND r.party_id = p_customer_contact_id
863 AND sub.status = 'A'
864 AND r.status = 'A'
865 AND obj.status = 'A'
866 AND r.subject_id = sub.party_id
867 AND r.object_id = obj.party_id
868 AND sub.party_type = 'PERSON'
869 AND obj.party_type = 'ORGANIZATION'
870 AND NVL(r.start_date, SYSDATE-1) <= SYSDATE
871 AND NVL(r.end_date, SYSDATE+1) > SYSDATE
872 AND ROWNUM < 2
873 ORDER BY r.LAST_UPDATE_DATE Desc;
874 LOG_MESSAGE('Contatc For Cusomer-'||l_customer_contact_id);
875 EXCEPTION
876 When Others Then
877 LOG_MESSAGE('Error in getting the contact for customer id-'||l_customer_id);
878 LOG_MESSAGE(SQLERRM);
879 ROLLBACK;
880 END;
881
882 p_contacts(1).party_id := l_customer_contact_id ;
883 p_contacts(1).contact_point_id := NULL;
884 p_contacts(1).contact_point_type := '';
885 p_contacts(1).primary_flag := 'Y';
886 p_contacts(1).contact_type := 'PARTY_RELATIONSHIP';
887
888 /* Call to CSD API to cretate Incident Id for repair Order */
889 BEGIN
890 cs_servicerequest_pub.create_servicerequest
891 (p_api_version => 3.0,
892 p_init_msg_list => FND_API.G_TRUE,
893 p_commit => FND_API.G_TRUE,
894 x_return_status => x_return_status,
895 x_msg_count => x_msg_count,
896 x_msg_data => x_msg_data,
897 p_resp_appl_id => NULL,
898 p_resp_id => NULL,
899 p_user_id => NULL,
900 p_login_id => NULL,
901 p_org_id => NULL,
902 p_request_id => NULL,
903 p_request_number => '',
904 p_service_request_rec => p_service_request_rec,
905 p_notes => p_notes,
906 p_contacts => p_contacts,
907 p_auto_assign => 'N',
908 x_request_id => x_request_id,
909 x_request_number => x_request_number,
910 x_interaction_id => x_interaction_id,
911 x_workflow_process_id => x_workflow_process_id,
912 x_individual_owner => x_individual_owner,
913 x_group_owner => x_group_owner,
914 x_individual_type => x_individual_type);
915
916 --COMMIT;
917 IF (x_return_status <> 'S') THEN
918 LOG_MESSAGE('Number of Error Messages : '||TO_CHAR(x_msg_count));
919 FOR i IN 1..x_msg_Count LOOP
920 FND_MSG_PUB.Get(p_msg_index => i,
921 p_encoded => 'F',
922 p_data => x_msg_data,
923 p_msg_index_out => x_msg_index_out );
924 LOG_MESSAGE('message data ='||x_msg_data);
925 END LOOP;
926 LOG_MESSAGE('error msg = '||SQLERRM);
927 ELSE
928 -- Output the results
929 LOG_MESSAGE('x_return_status = '||x_return_status);
930 LOG_MESSAGE('x_msg_count = '||TO_CHAR(x_msg_count));
931 LOG_MESSAGE('x_msg_data = '||x_msg_data);
932 LOG_MESSAGE('x_request_id/Incident_id = '||TO_CHAR(x_request_id));
933 LOG_MESSAGE('x_request_number/Incident_number = '||x_request_number);
934 LOG_MESSAGE('x_interaction_id = '||TO_CHAR(x_interaction_id));
935 LOG_MESSAGE('x_workflow_process_id = '||TO_CHAR(x_workflow_process_id));
936
937 log_output('Service Incident Number:'||To_Char(x_request_number));
938 LOG_MESSAGE('-------------------------------------------------------------------');
939
940 LOG_MESSAGE('Trying to Create a Repair Order for this incident');
941
942 Q_REPLN_Rec.REPAIR_NUMBER := NULL;
943 Q_REPLN_Rec.INCIDENT_ID := x_request_id;
944 Q_REPLN_Rec.INVENTORY_ITEM_ID := l_ro_release.Inventory_item_id;
945 Q_REPLN_Rec.CUSTOMER_PRODUCT_ID := NULL;
946 Q_REPLN_Rec.UNIT_OF_MEASURE := l_ro_release.Uom_code;
947 BEGIN
948 select repair_type_id
949 Into P_REPAIR_TYPE
950 from CSD_REPAIR_TYPES_B
951 where repair_mode = 'WIP'
952 and internal_order_flag = 'Y'
953 and rownum < 2;
954 Q_REPLN_Rec.REPAIR_TYPE_ID := P_REPAIR_TYPE;
955 EXCEPTION
956 When others then
957 Q_REPLN_Rec.REPAIR_TYPE_ID := NULL;
958 LOG_MESSAGE('Error while getting a repair Type');
959 LOG_MESSAGE(SQLERRM);
960 END;
961
962
963 Q_REPLN_Rec.RESOURCE_GROUP := NULL;
964 Q_REPLN_Rec.RESOURCE_ID := NULL;
965 Q_REPLN_Rec.PROJECT_ID := NULL;
966 Q_REPLN_Rec.TASK_ID := NULL;
967 Q_REPLN_Rec.UNIT_NUMBER := NULL;
968 Q_REPLN_Rec.CONTRACT_LINE_ID := NULL;
969 Q_REPLN_Rec.AUTO_PROCESS_RMA := NULL;
970 Q_REPLN_Rec.REPAIR_MODE := 'WIP';
971 Q_REPLN_Rec.OBJECT_VERSION_NUMBER := NULL;
972 Q_REPLN_Rec.ITEM_REVISION := NULL;
973 Q_REPLN_Rec.INSTANCE_ID := NULL;
974 Q_REPLN_Rec.STATUS := 'O';
975 Q_REPLN_Rec.STATUS_REASON_CODE := NULL;
976 Q_REPLN_Rec.DATE_CLOSED := NULL;
977 Q_REPLN_Rec.APPROVAL_REQUIRED_FLAG := 'N';
978 Q_REPLN_Rec.APPROVAL_STATUS := NULL;
979 Q_REPLN_Rec.SERIAL_NUMBER := NULL;
980 --Q_REPLN_Rec.PROMISE_DATE := NULL;
981 Q_REPLN_Rec.PROMISE_DATE := l_ro_release.Promise_date; -- modified for bug 9850811
982 Q_REPLN_Rec.ATTRIBUTE_CATEGORY := NULL;
983 Q_REPLN_Rec.ATTRIBUTE1 := NULL;
984 Q_REPLN_Rec.ATTRIBUTE2 := NULL;
985 Q_REPLN_Rec.ATTRIBUTE3 := NULL;
986 Q_REPLN_Rec.ATTRIBUTE4 := NULL;
987 Q_REPLN_Rec.ATTRIBUTE5 := NULL;
988 Q_REPLN_Rec.ATTRIBUTE6 := NULL;
989 Q_REPLN_Rec.ATTRIBUTE7 := NULL;
990 Q_REPLN_Rec.ATTRIBUTE8 := NULL;
991 Q_REPLN_Rec.ATTRIBUTE9 := NULL;
992 Q_REPLN_Rec.ATTRIBUTE10 := NULL;
993 Q_REPLN_Rec.ATTRIBUTE11 := NULL;
994 Q_REPLN_Rec.ATTRIBUTE12 := NULL;
995 Q_REPLN_Rec.ATTRIBUTE13 := NULL;
996 Q_REPLN_Rec.ATTRIBUTE14 := NULL;
997 Q_REPLN_Rec.ATTRIBUTE15 := NULL;
998 Q_REPLN_Rec.QUANTITY := l_ro_release.Quantity;
999 Q_REPLN_Rec.QUANTITY_IN_WIP := NULL;
1000 Q_REPLN_Rec.QUANTITY_RCVD := NULL;
1001 Q_REPLN_Rec.QUANTITY_SHIPPED := NULL;
1002 BEGIN
1003 select lgr.currency_code
1004 into l_currency_code
1005 from
1006 hr_organization_information hoi,
1007 gl_ledgers lgr
1008 where
1009 hoi.organization_id = l_ro_release.Organization_id
1010 AND (hoi.org_information_context || '') = 'Accounting Information'
1011 AND to_number(decode(RTRIM(TRANSLATE(hoi.org_information1, '0123456789', ' ')), NULL, hoi.org_information1, -99999)) = lgr.ledger_id
1012 AND lgr.object_type_code = 'L'
1013 AND nvl(lgr.complete_flag, 'Y') = 'Y';
1014
1015 Q_REPLN_Rec.CURRENCY_CODE := l_currency_code;
1016 LOG_MESSAGE('Currency Code'||l_currency_code);
1017 Exception
1018 when others then
1019 LOG_MESSAGE('Error while getting the default currecny for depot org');
1020 LOG_MESSAGE(SQLERRM);
1021 Q_REPLN_Rec.CURRENCY_CODE := NULL;
1022 END;
1023
1024 -- Q_REPLN_Rec.CURRENCY_CODE := NULL;--'USD';
1025 Q_REPLN_Rec.DEFAULT_PO_NUM := NULL;
1026 Q_REPLN_Rec.REPAIR_GROUP_ID := NULL;
1027 Q_REPLN_Rec.RO_TXN_STATUS := 'OM_BOOKED';
1028 Q_REPLN_Rec.ORDER_LINE_ID := NULL;
1029 Q_REPLN_Rec.ORIGINAL_SOURCE_REFERENCE := NULL;
1030 Q_REPLN_Rec.ORIGINAL_SOURCE_HEADER_ID := NULL;
1031 Q_REPLN_Rec.ORIGINAL_SOURCE_LINE_ID := NULL;
1032 -- Q_REPLN_Rec.PRICE_LIST_HEADER_ID := 1000;
1033 Q_REPLN_Rec.SUPERCESSION_INV_ITEM_ID := NULL;
1034 -- Q_REPLN_Rec.FLOW_STATUS_ID := 1008;
1035 Q_REPLN_Rec.FLOW_STATUS_CODE := NULL;
1036 Q_REPLN_Rec.FLOW_STATUS := NULL;
1037 Q_REPLN_Rec.INVENTORY_ORG_ID := l_ro_release.Organization_id; -- bug 9412508
1038
1039 Q_REPLN_Rec.PROBLEM_DESCRIPTION := NULL;
1040 Q_REPLN_Rec.RO_PRIORITY_CODE := NULL;
1041 BEGIN
1042
1043 LOG_MESSAGE('Calling API CSD_Repairs_PUB.Create_Repair_Order');
1044
1045 CSD_Repairs_PUB.Create_Repair_Order(P_Api_Version_Number => 1.0,
1046 P_Init_Msg_List => FND_API.G_FALSE,
1047 P_Commit => FND_API.G_FALSE,
1048 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1049 P_REPAIR_LINE_ID => P_REPAIR_LINE_ID,
1050 P_REPLN_Rec => Q_REPLN_Rec,
1051 p_create_default_logistics => p_create_default_logistics,
1052 X_REPAIR_LINE_ID => X_REPAIR_LINE_ID,
1053 X_REPAIR_NUMBER => X_REPAIR_NUMBER,
1054 X_Return_Status => X_Return_Status,
1055 X_Msg_Count => X_Msg_Count,
1056 X_Msg_Data => X_Msg_Data);
1057 LOG_MESSAGE('Called API CSD_Repairs_PUB.Create_Repair_Order -'||X_Return_Status);
1058 LOG_MESSAGE('Return Status. :'||X_Return_Status);
1059 LOG_MESSAGE('X_REPAIR_LINE_ID :'||X_REPAIR_LINE_ID);
1060 LOG_MESSAGE('X_REPAIR_NUMBER :'||X_REPAIR_NUMBER);
1061
1062 log_output('Repair Order Number:'||To_Char(X_REPAIR_NUMBER));
1063
1064 IF X_Return_Status <> 'S' THEN
1065 FOR i IN 1..X_msg_count
1066 LOOP
1067 l_msg := fnd_msg_pub.get(p_msg_index => 1,
1068 p_encoded => 'F' );
1069 LOG_MESSAGE('Error Message Data...'||X_Msg_Data||l_msg);
1070 END LOOP;
1071 ELSE
1072 LOG_MESSAGE('CSD_Repairs_PUB.Create_Repair_Order Called...');
1073 LOG_MESSAGE('Return Status. :'||X_Return_Status);
1074 LOG_MESSAGE('X_REPAIR_LINE_ID :'||X_REPAIR_LINE_ID);
1075 LOG_MESSAGE('X_REPAIR_NUMBER :'||X_REPAIR_NUMBER);
1076 -- COMMIT;
1077 BEGIN -- CAll to API TO Create a WIP JOb
1078 LOG_MESSAGE('-------------------------------------------------------------------');
1079 l_user_id := fnd_global.user_id();
1080 SELECT wip_job_schedule_interface_s.NEXTVAL
1081 INTO l_group_id FROM dual;
1082
1083 Select To_Char(WIP_JOB_NUMBER_S.NEXTVAL)
1084 into l_wip_job_name from dual;
1085 --l_group_id
1086 BEGIN
1087 -- INSERTING WIP JOB
1088 LOG_MESSAGE('Inserting into wip_job_schedule_interface');
1089
1090 INSERT INTO wip_job_schedule_interface
1091 (
1092 last_update_date,
1093 last_updated_by,
1094 creation_date,
1095 created_by,
1096 load_type,
1097 process_phase,
1098 process_status,
1099 group_id,
1100 source_code,
1101 source_line_id,
1102 job_name,
1103 organization_id,
1104 status_type,
1105 first_unit_start_date,
1106 last_unit_completion_date,
1107 start_quantity,
1108 net_quantity,
1109 class_code,
1110 primary_item_id,
1111 interface_id
1112 )
1113 VALUES
1114 (
1115 SYSDATE,
1116 l_user_id,
1117 SYSDATE,
1118 l_user_id,
1119 4,
1120 2,
1121 1,
1122 l_group_id,
1123 'MSC',
1124 X_REPAIR_LINE_ID,
1125 l_wip_job_name,
1126 l_ro_release.Organization_id,
1127 3,
1128 SYSDATE,
1129 l_ro_release.Promise_date,
1130 l_ro_release.Quantity,
1131 l_ro_release.Quantity,
1132 'Rework',
1133 l_ro_release.Inventory_item_id,
1134 l_group_id
1135 );
1136
1137 LOG_MESSAGE('Inserted into wip_job_schedule_interface group id '||to_char(l_group_id));
1138
1139 LOG_MESSAGE('Calling API WIP_MASSLOAD_PUB.createOneJob');
1140
1141 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_group_id,
1142 p_validationLevel => FND_API.G_VALID_LEVEL_FULL,
1143 x_wipEntityID => x_wipEntityID,
1144 x_returnStatus => x_return_status,
1145 x_errorMsg => x_msg_data );
1146
1147 LOG_MESSAGE('Return Status. :'||X_Return_Status);
1148 LOG_MESSAGE('x_wipEntityID :'||x_wipEntityID);
1149
1150 log_output('Wip Job:'||l_wip_job_name);
1151
1152 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1153 LOG_MESSAGE('Error Rep-work order creation : FND_API.G_RET_STS_UNEXP_ERROR');
1154 -- /* rollback ; */ commit;
1155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1156 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1157 LOG_MESSAGE('Error in Rep-work order creation : FND_API.G_RET_STS_ERROR');
1158 rollback ;
1159 RAISE FND_API.G_EXC_ERROR;
1160 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1161 LOG_MESSAGE('Successful in Creating Rep-work .');
1162 LOG_MESSAGE('wip etity id :' ||x_wipEntityID);
1163 -- commit ;
1164 LOG_MESSAGE('-------------------------------------------------------------------');
1165 LOG_MESSAGE('Calling API To Connect Wip job with the repair Order');
1166
1167 BEGIN -- Code to call API to link WIp job To REpair ORder
1168 csd_to_form_repair_job_xref.validate_and_write(
1169 p_api_version_number => 1.0,
1170 p_init_msg_list => FND_API.G_FALSE,
1171 p_commit => FND_API.G_FALSE,
1172 p_validation_level => NULL,
1173 p_action_code => 0,
1174 px_repair_job_xref_id => px_REPAIR_JOB_XREF_ID,
1175 p_created_by => l_user_id,
1176 p_creation_date => SYSDATE,
1177 p_last_updated_by => l_user_id,
1178 p_last_update_date => SYSDATE,
1179 p_last_update_login => l_user_id,
1180 p_repair_line_id => X_REPAIR_LINE_ID, --10960,
1181 p_wip_entity_id => x_wipEntityID, --760063,
1182 p_group_id => l_group_id, --required
1183 p_organization_id => l_ro_release.Organization_id,
1184 p_quantity => l_ro_release.Quantity,
1185 p_INVENTORY_ITEM_ID => l_ro_release.Inventory_item_id,
1186 p_ITEM_REVISION => null,
1187 p_OBJECT_VERSION_NUMBER => 1,
1188 p_attribute_category => NULL,
1189 p_attribute1 => NULL,
1190 p_attribute2 => NULL,
1191 p_attribute3 => NULL,
1192 p_attribute4 => NULL,
1193 p_attribute5 => NULL,
1194 p_attribute6 => NULL,
1195 p_attribute7 => NULL,
1196 p_attribute8 => NULL,
1197 p_attribute9 => NULL,
1198 p_attribute10 => NULL,
1199 p_attribute11 => NULL,
1200 p_attribute12 => NULL,
1201 p_attribute13 => NULL,
1202 p_attribute14 => NULL,
1203 p_attribute15 => NULL,
1204 p_quantity_completed => NULL,
1205 p_job_name => l_wip_job_name,
1206 p_source_type_code => 'MANUAL',
1207 p_source_id1 => NULL,
1208 p_ro_service_code_id => NULL,
1209 x_return_status => l_return_status,
1210 x_msg_count => X_Msg_Count,
1211 x_msg_data => X_Msg_Data);
1212
1213 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1214 LOG_MESSAGE('Error Rep-work order updation : FND_API.G_RET_STS_UNEXP_ERROR');
1215 rollback ;
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1218 LOG_MESSAGE('Error in Rep-work order updation : FND_API.G_RET_STS_ERROR');
1219 rollback ;
1220 RAISE FND_API.G_EXC_ERROR;
1221 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1222 LOG_MESSAGE('Successful in updating Rep-work .');
1223 LOG_MESSAGE('px_REPAIR_JOB_XREF_ID :' ||px_REPAIR_JOB_XREF_ID);
1224 commit ;
1225 END IF;
1226 LOG_MESSAGE('Ending....');
1227 EXCEPTION
1228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1229 LOG_MESSAGE('Error : FND_API.G_EXC_UNEXPECTED_ERROR');
1230 LOG_MESSAGE(SQLERRM);
1231 -- retcode := 1;
1232 -- rollback ;
1233 LOG_MESSAGE('Transaction rolled back');
1234 LOG_MESSAGE('----------------------------------------------------------');
1235
1236 WHEN FND_API.G_EXC_ERROR THEN
1237 LOG_MESSAGE('Error : FND_API.G_EXC_ERROR');
1238 LOG_MESSAGE(SQLERRM);
1239 -- retcode := 1;
1240 -- rollback ;
1241 LOG_MESSAGE('Transaction rolled back');
1242 LOG_MESSAGE('----------------------------------------------------------');
1243 WHEN OTHERS THEN
1244 LOG_MESSAGE('Error : Err OTHERS');
1245 LOG_MESSAGE(SQLERRM);
1246 -- retcode := 1;
1247 -- rollback ;
1248 LOG_MESSAGE('Transaction rolled back');
1249 LOG_MESSAGE('----------------------------------------------------------');
1250
1251 END;
1252 END IF;
1253 LOG_MESSAGE('x_return_status :'||x_return_status);
1254 LOG_MESSAGE('Ending....');
1255 EXCEPTION
1256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1257 LOG_MESSAGE('Error : FND_API.G_EXC_UNEXPECTED_ERROR');
1258 LOG_MESSAGE(SQLERRM);
1259 -- retcode := 1;
1260 -- rollback ;
1261 LOG_MESSAGE('Transaction rolled back');
1262 LOG_MESSAGE('----------------------------------------------------------');
1263
1264 WHEN FND_API.G_EXC_ERROR THEN
1265 LOG_MESSAGE('Error : FND_API.G_EXC_ERROR');
1266 LOG_MESSAGE(SQLERRM);
1267 -- retcode := 1;
1268 -- rollback ;
1269 LOG_MESSAGE('Transaction rolled back');
1270 LOG_MESSAGE('----------------------------------------------------------');
1271 WHEN OTHERS THEN
1272 LOG_MESSAGE('Error : Err OTHERS');
1273 LOG_MESSAGE(SQLERRM);
1274 -- retcode := 1;
1275 -- rollback ;
1276 LOG_MESSAGE('Transaction rolled back');
1277 LOG_MESSAGE('----------------------------------------------------------');
1278 END;
1279
1280 END;
1281 END IF;
1282
1283 END; -- BEGIN Block of repair order API
1284
1285 END IF; -- outer block of incident
1286
1287
1288 END;
1289
1290 end if ;
1291 END;
1292 END loop;
1293
1294 --======================================================
1295
1296 For l_ro_release in ro_release_data(p_batch_id) loop
1297 BEGIN
1298
1299 Savepoint Before_MOVE_IN ;
1300
1301 Update mrp_org_transfer_release
1302 set part_condition ='B'
1303 where transaction_id= l_ro_release.In_req_transaction_id;
1304
1305
1306 mrp_create_schedule_iso.Create_IR_ISO(errbuf,retcode ,l_req_header_id,l_iso_header_id, l_ro_release.In_req_transaction_id, l_ro_release.batch_id );
1307 l_iso_header_id1 := l_iso_header_id;
1308 l_req_header_id1:= l_req_header_id ;
1309
1310 if (retcode=0 ) then
1311 Update mrp_org_transfer_release
1312 set part_condition ='G'
1313 where transaction_id= l_ro_release.out_req_transaction_id;
1314
1315 mrp_create_schedule_iso.Create_IR_ISO(errbuf,retcode ,l_req_header_id,l_iso_header_id, l_ro_release.out_req_transaction_id, l_ro_release.batch_id );
1316 l_iso_header_id2 := l_iso_header_id;
1317 l_req_header_id2:= l_req_header_id ;
1318
1319 end if ;
1320
1321 if (retcode=0 ) then
1322 CSD_Refurbish_IRO_GRP.Create_InternalRO
1323 ( 1.0,
1324 FND_API.G_FALSE,
1325 FND_API.G_FALSE,
1326 FND_API.G_VALID_LEVEL_FULL,
1327 x_return_status ,
1328 p_msg_count ,
1329 x_msg_data ,
1330 l_req_header_id1,
1331 l_iso_header_id1,
1332 l_req_header_id2,
1333 l_iso_header_id2,
1334 x_service_request_number);
1335
1336 IF p_msg_count IS NOT NULL THEN
1337 IF p_msg_count = 1 THEN
1338 l_msg := fnd_msg_pub.get(p_msg_index => 1,
1339 p_encoded => 'F' );
1340 log_message(l_msg);
1341 ELSIF p_msg_count > 1 THEN
1342 FOR i IN 1..p_msg_count
1343 LOOP
1344 l_msg := fnd_msg_pub.get(p_msg_index => i,
1345 p_encoded => 'F' );
1346 Fnd_file.put_line(fnd_file.LOG,l_msg);
1347 END LOOP;
1348 END IF;
1349
1350 END IF;
1351
1352 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1353 LOG_MESSAGE('Error in Repair order creation : FND_API.G_RET_STS_UNEXP_ERROR');
1354 rollback ;
1355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1356 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1357 LOG_MESSAGE('Error in Repair order creation : FND_API.G_RET_STS_ERROR');
1358 rollback ;
1359 RAISE FND_API.G_EXC_ERROR;
1360 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1361 log_message('Successful in Creating Repair Req .');
1362 log_message('service request number :' ||x_service_request_number);
1363 log_output('Successful in Creating Repair Req .');
1364 log_output('service request number :' ||x_service_request_number);
1365 commit ;
1366 END IF;
1367
1368 end if ;
1369
1370 if (l_ro_release.in_req_quantity <> l_ro_release.quantity and retcode =0 ) then
1371 select repair_line_id into lv_repair_line_id
1372 from csd_product_transactions
1373 where action_type='MOVE_IN'
1374 and REQ_HEADER_ID= l_req_header_id1;
1375
1376 /* call to update iro */
1377 update_iro(errbuf=>errbuf,
1378 retcode=>retcode,
1379 p_repair_line_id=>lv_repair_line_id,
1380 p_quantity=>l_ro_release.quantity,
1381 p_promise_date=> l_ro_release.Promise_date) ;
1382 if retcode =0 then
1383 commit;
1384 end if ;
1385 end if ;
1386
1387 if retcode <> 0 Then
1388 rollback to savepoint Before_MOVE_IN;
1389 global_retcode := retcode;
1390 end if;
1391
1392 EXCEPTION
1393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1394 LOG_MESSAGE('Error in Release_new_IRO : FND_API.G_EXC_UNEXPECTED_ERROR');
1395 LOG_MESSAGE(SQLERRM);
1396 retcode := 1;
1397 rollback ;
1398 log_message('Transaction rolled back');
1399 log_message('----------------------------------------------------------');
1400
1401 WHEN FND_API.G_EXC_ERROR THEN
1402 LOG_MESSAGE('Error in Release_new_IRO : FND_API.G_EXC_ERROR');
1403 LOG_MESSAGE(SQLERRM);
1404 retcode := 1;
1405 rollback ;
1406 log_message('Transaction rolled back');
1407 log_message('----------------------------------------------------------');
1408 WHEN OTHERS THEN
1409 LOG_MESSAGE('Error in Release_new_IRO : Err OTHERS');
1410 LOG_MESSAGE(SQLERRM);
1411 retcode := 1;
1412 rollback ;
1413 log_message('Transaction rolled back');
1414 log_message('----------------------------------------------------------');
1415 END ;
1416
1417 END loop;
1418 retcode := global_retcode;
1419
1420 DELETE FROM MRP_IRO_RELEASE WHERE BATCH_ID=p_batch_id ;
1421 Commit;
1422 DELETE FROm MRP_ORG_TRANSFER_RELEASE WHERE BATCH_ID=p_batch_id;
1423 Commit;
1424 END Release_new_IRO;
1425
1426
1427 END MSC_SRP_RELEASE;