[Home] [Help]
PACKAGE BODY: APPS.MSC_SRP_RELEASE
Source
1 PACKAGE BODY MSC_SRP_RELEASE AS
2 /* $Header: MSCPSRPB.pls 120.8.12010000.4 2008/10/17 11:08:12 vsiyer 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 l_acct_id NUMBER;
621 l_acct_number VARCHAR2(2000);
622
623 --Q_REPLN_Rec CSD_REPAIRS_PUB.REPLN_Rec_Type;
624 --p_create_default_logistics VARCHAR2(100) := 'N';
625 --P_REPAIR_LINE_ID NUMBER;
626 P_REPAIR_TYPE NUMBER;
627 X_REPAIR_LINE_ID NUMBER;
628 l_currency_code VARCHAR2(100);
629 --X_REPAIR_NUMBER VARCHAR2(1000);
630 l_customer_contact_id NUMBER;
631 l_user_id NUMBER;
632 l_group_id NUMBER;
633 x_wipEntityID NUMBER;
634
635 l_wip_job_name VARCHAR2(2000);
636 px_REPAIR_JOB_XREF_ID NUMBER;
637 l_return_status VARCHAR2(2000);
638 BEGIN
639 log_output(' Internal Requisition/ISO Release and Reschedule Report');
640 log_output(' ------------------------------------------------------');
641 log_output('');
642 log_output('Order No Load Type ISO No Quantity Schedule Shipment Date Schedule Arrival Date Internal Need By Date');
643 log_output('in PWB Req No. ');
644 log_output('----------- ----------- -------- -------- ---------------------- ---------------------- -------- ----------------------');
645
646
647
648 Delete from MRP_IRO_RELEASE mir
649 where batch_id =p_batch_id
650 And in_req_transaction_id is null
651 AND load_type = IRO_LOAD
652 And exists ( select 1 from mrp_iro_release mir1
653 Where mir1.batch_id =p_batch_id
654 And mir1.in_req_transaction_id is not null
655 And mir1.transaction_id = mir.transaction_id
656 );
657
658 -- Added to process repair orders with no inward movement of defectives
659 -- ie onhand pegged defective supplies
660
661 For l_ro_release in Ro_release_OH_data(p_batch_id) loop
662
663 BEGIN
664 LOG_MESSAGE('Creating MOVE OUT lines');
665
666 Update mrp_org_transfer_release
667 set part_condition ='G'
668 where transaction_id= l_ro_release.Out_req_transaction_id;
669
670 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 );
671 l_iso_header_id1 := l_iso_header_id;
672 l_req_header_id1:= l_req_header_id ;
673 if (retcode=0 ) then
674
675 LOG_MESSAGE('Creating Repair Incident');
676 CS_SERVICEREQUEST_PUB.initialize_rec(p_service_request_rec);
677 p_service_request_rec.request_date := SYSDATE;
678 p_service_request_rec.type_name := '';
679
680 p_service_request_rec.type_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_TYPE'));
681 if (p_service_request_rec.type_id <> 4) Then
682 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Type-');
683 end if;
684 p_service_request_rec.status_id := 1; -- Open
685 p_service_request_rec.status_name := '';
686
687 p_service_request_rec.severity_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_SEVERITY'));
688 if (p_service_request_rec.severity_id <> 4) Then
689 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Severity-');
690 end if;
691
692 p_service_request_rec.severity_name := '';
693
694 p_service_request_rec.urgency_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_URGENCY'));
695 if (p_service_request_rec.urgency_id <> 44) Then
696 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Urgency-');
697 end if;
698
699 p_service_request_rec.urgency_name := '';
700 p_service_request_rec.closed_date := TO_DATE('');
701
702 p_service_request_rec.owner_id := to_number(FND_PROFILE.VALUE('INC_DEFAULT_INCIDENT_OWNER'));
703 if (p_service_request_rec.owner_id is null) Then
704 LOG_MESSAGE('Please set a correct value for Profile :-Service: Default Service Request Owner-');
705 end if;
706
707 -- Get Customer ID
708 BEGIN
709 log_message('Source organization id => ' || l_ro_release.src_organization_id);
710
711 po_customers_sv.get_cust_details(l_ro_release.deliver_to_location_id,
712 l_customer_id,
713 l_address_id,
714 l_site_use_id,
715 l_ro_release.src_organization_id);
716
717 select cust_account_id,account_number
718 into l_acct_id,l_acct_number
719 from hz_cust_accounts_all
720 where party_id = l_customer_id
721 and customer_type ='I'
722 and rownum < 2;
723
724 select party_number
725 into l_customer_NAME
726 from hz_parties --ra_customers
727 where party_id = l_customer_id
728 and rownum < 2;
729 Exception
730 when others then
731 LOG_MESSAGE('Error in Getting Customer Details for the Incident');
732 LOG_MESSAGE(SQLERRM);
733
734 END;
735 p_service_request_rec.owner_group_id := NULL; -- CS_SR_DEFAULT_GROUP_OWNER
736 p_service_request_rec.publish_flag := '';
737 p_service_request_rec.SUMMARY := 'DEPOT REPAIR';
738 p_service_request_rec.caller_type := 'ORGANIZATION';
739 p_service_request_rec.customer_id := l_customer_id ;
740 p_service_request_rec.customer_number := NULL;
741 p_service_request_rec.employee_id := NULL;
742 p_service_request_rec.employee_number := '';
743 p_service_request_rec.verify_cp_flag := FND_API.G_MISS_CHAR;
744 p_service_request_rec.customer_product_id := NULL;
745 p_service_request_rec.platform_id := NULL;
746 p_service_request_rec.platform_version_id := NULL;
747 p_service_request_rec.cp_component_id := NULL;
748 p_service_request_rec.cp_component_version_id := NULL;
749 p_service_request_rec.cp_subcomponent_id := NULL;
750 p_service_request_rec.cp_subcomponent_version_id := NULL;
751 p_service_request_rec.language_id := NULL;
752 Begin
753 select userenv('LANG')
754 Into v_lang from dual;
755
756 p_service_request_rec.LANGUAGE := v_lang;
757 End;
758 p_service_request_rec.cp_ref_number := NULL;
759
760 p_service_request_rec.inventory_item_id := l_ro_release.Inventory_item_id;
761 p_service_request_rec.inventory_item_conc_segs := '';
762 p_service_request_rec.inventory_item_segment1 := '';
763 p_service_request_rec.inventory_item_segment2 := '';
764 p_service_request_rec.inventory_item_segment3 := '';
765 p_service_request_rec.inventory_item_segment4 := '';
766 p_service_request_rec.inventory_item_segment5 := '';
767 p_service_request_rec.inventory_item_segment6 := '';
768 p_service_request_rec.inventory_item_segment7 := '';
769 p_service_request_rec.inventory_item_segment8 := '';
770 p_service_request_rec.inventory_item_segment9 := '';
771 p_service_request_rec.inventory_item_segment10 := '';
772 p_service_request_rec.inventory_item_segment11 := '';
773 p_service_request_rec.inventory_item_segment12 := '';
774 p_service_request_rec.inventory_item_segment13 := '';
775 p_service_request_rec.inventory_item_segment14 := '';
776 p_service_request_rec.inventory_item_segment15 := '';
777 p_service_request_rec.inventory_item_segment16 := '';
778 p_service_request_rec.inventory_item_segment17 := '';
779 p_service_request_rec.inventory_item_segment18 := '';
780 p_service_request_rec.inventory_item_segment19 := '';
781 p_service_request_rec.inventory_item_segment20 := '';
782 p_service_request_rec.inventory_item_vals_or_ids := '';
783
784 p_service_request_rec.inventory_org_id := l_ro_release.Organization_id;
785 p_service_request_rec.current_serial_number := '';
786 p_service_request_rec.original_order_number := NULL;
787 p_service_request_rec.purchase_order_num := '';
788 p_service_request_rec.problem_code := '';
789 p_service_request_rec.exp_resolution_date := TO_DATE('');
790 p_service_request_rec.install_site_use_id := NULL;
791 p_service_request_rec.request_attribute_1 := '';
792 p_service_request_rec.request_attribute_2 := '';
793 p_service_request_rec.request_attribute_3 := '';
794 p_service_request_rec.request_attribute_4 := '';
795 p_service_request_rec.request_attribute_5 := '';
796 p_service_request_rec.request_attribute_6 := '';
797 p_service_request_rec.request_attribute_7 := '';
798 p_service_request_rec.request_attribute_8 := '';
799 p_service_request_rec.request_attribute_9 := '';
800 p_service_request_rec.request_attribute_10 := '';
801 p_service_request_rec.request_attribute_11 := '';
802 p_service_request_rec.request_attribute_12 := '';
803 p_service_request_rec.request_attribute_13 := '';
804 p_service_request_rec.request_attribute_14 := '';
805 p_service_request_rec.request_attribute_15 := '';
806 p_service_request_rec.request_context := '';
807 p_service_request_rec.bill_to_site_use_id := NULL;
808 p_service_request_rec.bill_to_contact_id := NULL;
809 p_service_request_rec.ship_to_site_use_id := NULL;
810 p_service_request_rec.ship_to_contact_id := NULL;
811 p_service_request_rec.resolution_code := '';
812 p_service_request_rec.act_resolution_date := TO_DATE('');
813 p_service_request_rec.public_comment_flag := '';
814 p_service_request_rec.parent_interaction_id := NULL;
815 p_service_request_rec.contract_service_id := NULL;
816 p_service_request_rec.contract_service_number := '';
817 p_service_request_rec.contract_id := NULL;
818 p_service_request_rec.project_number := '';
819 p_service_request_rec.qa_collection_plan_id := NULL;
820
821 p_service_request_rec.account_id := l_acct_id;
822
823 p_service_request_rec.resource_type := 'RS_EMPLOYEE';
824 p_service_request_rec.resource_subtype_id := NULL;
825 p_service_request_rec.cust_po_number := '';
826 p_service_request_rec.cust_ticket_number := '';
827 p_service_request_rec.sr_creation_channel := 'Phone';
828 p_service_request_rec.obligation_date := TO_DATE('');
829 p_service_request_rec.time_zone_id := NULL;
830 p_service_request_rec.time_difference := NULL;
831 p_service_request_rec.site_id := NULL;
832 p_service_request_rec.customer_site_id := NULL;
833 p_service_request_rec.territory_id := NULL;
834 p_service_request_rec.initialize_flag := '';
835 p_service_request_rec.cp_revision_id := NULL;
836 p_service_request_rec.inv_item_revision := '';
837 p_service_request_rec.inv_component_id := NULL;
838 p_service_request_rec.inv_component_version := '';
839 p_service_request_rec.inv_subcomponent_id := NULL;
840 p_service_request_rec.inv_subcomponent_version := '';
841 p_notes(1).note := '';
842 p_notes(1).note_detail := '';
843 p_notes(1).note_type := '';
844 p_notes(1).note_context_type_01 := '';
845 p_notes(1).note_context_type_id_01 := NULL;
846 p_notes(1).note_context_type_02 := '';
847 p_notes(1).note_context_type_id_02 := NULL;
848 p_notes(1).note_context_type_03 := '';
849 p_notes(1).note_context_type_id_03 := NULL;
850 p_contacts(1).sr_contact_point_id := NULL;
851
852 /* For Getting a contact point for this customer */
853 BEGIN
854 SELECT r.party_id
855 INTO l_customer_contact_id
856 FROM Hz_Parties sub,
857 Hz_Relationships r,
858 Hz_Parties obj
859 WHERE r.object_id = l_customer_id
860 --AND r.party_id = p_customer_contact_id
861 AND sub.status = 'A'
862 AND r.status = 'A'
863 AND obj.status = 'A'
864 AND r.subject_id = sub.party_id
865 AND r.object_id = obj.party_id
866 AND sub.party_type = 'PERSON'
867 AND obj.party_type = 'ORGANIZATION'
868 AND NVL(r.start_date, SYSDATE-1) <= SYSDATE
869 AND NVL(r.end_date, SYSDATE+1) > SYSDATE
870 AND ROWNUM < 2
871 ORDER BY r.LAST_UPDATE_DATE Desc;
872 LOG_MESSAGE('Contatc For Cusomer-'||l_customer_contact_id);
873 EXCEPTION
874 When Others Then
875 LOG_MESSAGE('Error in getting the contact for customer id-'||l_customer_id);
876 LOG_MESSAGE(SQLERRM);
877 ROLLBACK;
878 END;
879
880 p_contacts(1).party_id := l_customer_contact_id ;
881 p_contacts(1).contact_point_id := NULL;
882 p_contacts(1).contact_point_type := '';
883 p_contacts(1).primary_flag := 'Y';
884 p_contacts(1).contact_type := 'PARTY_RELATIONSHIP';
885
886 /* Call to CSD API to cretate Incident Id for repair Order */
887 BEGIN
888 cs_servicerequest_pub.create_servicerequest
889 (p_api_version => 3.0,
890 p_init_msg_list => FND_API.G_TRUE,
891 p_commit => FND_API.G_TRUE,
892 x_return_status => x_return_status,
893 x_msg_count => x_msg_count,
894 x_msg_data => x_msg_data,
895 p_resp_appl_id => NULL,
896 p_resp_id => NULL,
897 p_user_id => NULL,
898 p_login_id => NULL,
899 p_org_id => NULL,
900 p_request_id => NULL,
901 p_request_number => '',
902 p_service_request_rec => p_service_request_rec,
903 p_notes => p_notes,
904 p_contacts => p_contacts,
905 p_auto_assign => 'N',
906 x_request_id => x_request_id,
907 x_request_number => x_request_number,
908 x_interaction_id => x_interaction_id,
909 x_workflow_process_id => x_workflow_process_id,
910 x_individual_owner => x_individual_owner,
911 x_group_owner => x_group_owner,
912 x_individual_type => x_individual_type);
913
914 --COMMIT;
915 IF (x_return_status <> 'S') THEN
916 LOG_MESSAGE('Number of Error Messages : '||TO_CHAR(x_msg_count));
917 FOR i IN 1..x_msg_Count LOOP
918 FND_MSG_PUB.Get(p_msg_index => i,
919 p_encoded => 'F',
920 p_data => x_msg_data,
921 p_msg_index_out => x_msg_index_out );
922 LOG_MESSAGE('message data ='||x_msg_data);
923 END LOOP;
924 LOG_MESSAGE('error msg = '||SQLERRM);
925 ELSE
926 -- Output the results
927 LOG_MESSAGE('x_return_status = '||x_return_status);
928 LOG_MESSAGE('x_msg_count = '||TO_CHAR(x_msg_count));
929 LOG_MESSAGE('x_msg_data = '||x_msg_data);
930 LOG_MESSAGE('x_request_id/Incident_id = '||TO_CHAR(x_request_id));
931 LOG_MESSAGE('x_request_number/Incident_number = '||x_request_number);
932 LOG_MESSAGE('x_interaction_id = '||TO_CHAR(x_interaction_id));
933 LOG_MESSAGE('x_workflow_process_id = '||TO_CHAR(x_workflow_process_id));
934
935 log_output('Service Incident Number:'||To_Char(x_request_number));
936 LOG_MESSAGE('-------------------------------------------------------------------');
937
938 LOG_MESSAGE('Trying to Create a Repair Order for this incident');
939
940 Q_REPLN_Rec.REPAIR_NUMBER := NULL;
941 Q_REPLN_Rec.INCIDENT_ID := x_request_id;
942 Q_REPLN_Rec.INVENTORY_ITEM_ID := l_ro_release.Inventory_item_id;
943 Q_REPLN_Rec.CUSTOMER_PRODUCT_ID := NULL;
944 Q_REPLN_Rec.UNIT_OF_MEASURE := l_ro_release.Uom_code;
945 BEGIN
946 select repair_type_id
947 Into P_REPAIR_TYPE
948 from CSD_REPAIR_TYPES_B
949 where repair_mode = 'WIP'
950 and internal_order_flag = 'Y'
951 and rownum < 2;
952 Q_REPLN_Rec.REPAIR_TYPE_ID := P_REPAIR_TYPE;
953 EXCEPTION
954 When others then
955 Q_REPLN_Rec.REPAIR_TYPE_ID := NULL;
956 LOG_MESSAGE('Error while getting a repair Type');
957 LOG_MESSAGE(SQLERRM);
958 END;
959
960
961 Q_REPLN_Rec.RESOURCE_GROUP := NULL;
962 Q_REPLN_Rec.RESOURCE_ID := NULL;
963 Q_REPLN_Rec.PROJECT_ID := NULL;
964 Q_REPLN_Rec.TASK_ID := NULL;
965 Q_REPLN_Rec.UNIT_NUMBER := NULL;
966 Q_REPLN_Rec.CONTRACT_LINE_ID := NULL;
967 Q_REPLN_Rec.AUTO_PROCESS_RMA := NULL;
968 Q_REPLN_Rec.REPAIR_MODE := 'WIP';
969 Q_REPLN_Rec.OBJECT_VERSION_NUMBER := NULL;
970 Q_REPLN_Rec.ITEM_REVISION := NULL;
971 Q_REPLN_Rec.INSTANCE_ID := NULL;
972 Q_REPLN_Rec.STATUS := 'O';
973 Q_REPLN_Rec.STATUS_REASON_CODE := NULL;
974 Q_REPLN_Rec.DATE_CLOSED := NULL;
975 Q_REPLN_Rec.APPROVAL_REQUIRED_FLAG := 'N';
976 Q_REPLN_Rec.APPROVAL_STATUS := NULL;
977 Q_REPLN_Rec.SERIAL_NUMBER := NULL;
978 Q_REPLN_Rec.PROMISE_DATE := NULL;
979 Q_REPLN_Rec.ATTRIBUTE_CATEGORY := NULL;
980 Q_REPLN_Rec.ATTRIBUTE1 := NULL;
981 Q_REPLN_Rec.ATTRIBUTE2 := NULL;
982 Q_REPLN_Rec.ATTRIBUTE3 := NULL;
983 Q_REPLN_Rec.ATTRIBUTE4 := NULL;
984 Q_REPLN_Rec.ATTRIBUTE5 := NULL;
985 Q_REPLN_Rec.ATTRIBUTE6 := NULL;
986 Q_REPLN_Rec.ATTRIBUTE7 := NULL;
987 Q_REPLN_Rec.ATTRIBUTE8 := NULL;
988 Q_REPLN_Rec.ATTRIBUTE9 := NULL;
989 Q_REPLN_Rec.ATTRIBUTE10 := NULL;
990 Q_REPLN_Rec.ATTRIBUTE11 := NULL;
991 Q_REPLN_Rec.ATTRIBUTE12 := NULL;
992 Q_REPLN_Rec.ATTRIBUTE13 := NULL;
993 Q_REPLN_Rec.ATTRIBUTE14 := NULL;
994 Q_REPLN_Rec.ATTRIBUTE15 := NULL;
995 Q_REPLN_Rec.QUANTITY := l_ro_release.Quantity;
996 Q_REPLN_Rec.QUANTITY_IN_WIP := NULL;
997 Q_REPLN_Rec.QUANTITY_RCVD := NULL;
998 Q_REPLN_Rec.QUANTITY_SHIPPED := NULL;
999 BEGIN
1000 select lgr.currency_code
1001 into l_currency_code
1002 from
1003 hr_organization_information hoi,
1004 gl_ledgers lgr
1005 where
1006 hoi.organization_id = l_ro_release.Organization_id
1007 AND (hoi.org_information_context || '') = 'Accounting Information'
1008 AND to_number(decode(RTRIM(TRANSLATE(hoi.org_information1, '0123456789', ' ')), NULL, hoi.org_information1, -99999)) = lgr.ledger_id
1009 AND lgr.object_type_code = 'L'
1010 AND nvl(lgr.complete_flag, 'Y') = 'Y';
1011
1012 Q_REPLN_Rec.CURRENCY_CODE := l_currency_code;
1013 LOG_MESSAGE('Currency Code'||l_currency_code);
1014 Exception
1015 when others then
1016 LOG_MESSAGE('Error while getting the default currecny for depot org');
1017 LOG_MESSAGE(SQLERRM);
1018 Q_REPLN_Rec.CURRENCY_CODE := NULL;
1019 END;
1020
1021 -- Q_REPLN_Rec.CURRENCY_CODE := NULL;--'USD';
1022 Q_REPLN_Rec.DEFAULT_PO_NUM := NULL;
1023 Q_REPLN_Rec.REPAIR_GROUP_ID := NULL;
1024 Q_REPLN_Rec.RO_TXN_STATUS := 'OM_BOOKED';
1025 Q_REPLN_Rec.ORDER_LINE_ID := NULL;
1026 Q_REPLN_Rec.ORIGINAL_SOURCE_REFERENCE := NULL;
1027 Q_REPLN_Rec.ORIGINAL_SOURCE_HEADER_ID := NULL;
1028 Q_REPLN_Rec.ORIGINAL_SOURCE_LINE_ID := NULL;
1029 -- Q_REPLN_Rec.PRICE_LIST_HEADER_ID := 1000;
1030 Q_REPLN_Rec.SUPERCESSION_INV_ITEM_ID := NULL;
1031 -- Q_REPLN_Rec.FLOW_STATUS_ID := 1008;
1032 Q_REPLN_Rec.FLOW_STATUS_CODE := NULL;
1033 Q_REPLN_Rec.FLOW_STATUS := NULL;
1034 Q_REPLN_Rec.INVENTORY_ORG_ID := NULL;
1035
1036 Q_REPLN_Rec.PROBLEM_DESCRIPTION := NULL;
1037 Q_REPLN_Rec.RO_PRIORITY_CODE := NULL;
1038 BEGIN
1039
1040 LOG_MESSAGE('Calling API CSD_Repairs_PUB.Create_Repair_Order');
1041
1042 CSD_Repairs_PUB.Create_Repair_Order(P_Api_Version_Number => 1.0,
1043 P_Init_Msg_List => FND_API.G_FALSE,
1044 P_Commit => FND_API.G_FALSE,
1045 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1046 P_REPAIR_LINE_ID => P_REPAIR_LINE_ID,
1047 P_REPLN_Rec => Q_REPLN_Rec,
1048 p_create_default_logistics => p_create_default_logistics,
1049 X_REPAIR_LINE_ID => X_REPAIR_LINE_ID,
1050 X_REPAIR_NUMBER => X_REPAIR_NUMBER,
1051 X_Return_Status => X_Return_Status,
1052 X_Msg_Count => X_Msg_Count,
1053 X_Msg_Data => X_Msg_Data);
1054 LOG_MESSAGE('Called API CSD_Repairs_PUB.Create_Repair_Order -'||X_Return_Status);
1055 LOG_MESSAGE('Return Status. :'||X_Return_Status);
1056 LOG_MESSAGE('X_REPAIR_LINE_ID :'||X_REPAIR_LINE_ID);
1057 LOG_MESSAGE('X_REPAIR_NUMBER :'||X_REPAIR_NUMBER);
1058
1059 log_output('Repair Order Number:'||To_Char(X_REPAIR_NUMBER));
1060
1061 IF X_Return_Status <> 'S' THEN
1062 FOR i IN 1..X_msg_count
1063 LOOP
1064 l_msg := fnd_msg_pub.get(p_msg_index => 1,
1065 p_encoded => 'F' );
1066 LOG_MESSAGE('Error Message Data...'||X_Msg_Data||l_msg);
1067 END LOOP;
1068 ELSE
1069 LOG_MESSAGE('CSD_Repairs_PUB.Create_Repair_Order Called...');
1070 LOG_MESSAGE('Return Status. :'||X_Return_Status);
1071 LOG_MESSAGE('X_REPAIR_LINE_ID :'||X_REPAIR_LINE_ID);
1072 LOG_MESSAGE('X_REPAIR_NUMBER :'||X_REPAIR_NUMBER);
1073 -- COMMIT;
1074 BEGIN -- CAll to API TO Create a WIP JOb
1075 LOG_MESSAGE('-------------------------------------------------------------------');
1076 l_user_id := fnd_global.user_id();
1077 SELECT wip_job_schedule_interface_s.NEXTVAL
1078 INTO l_group_id FROM dual;
1079
1080 Select To_Char(WIP_JOB_NUMBER_S.NEXTVAL)
1081 into l_wip_job_name from dual;
1082 --l_group_id
1083 BEGIN
1084 -- INSERTING WIP JOB
1085 LOG_MESSAGE('Inserting into wip_job_schedule_interface');
1086
1087 INSERT INTO wip_job_schedule_interface
1088 (
1089 last_update_date,
1090 last_updated_by,
1091 creation_date,
1092 created_by,
1093 load_type,
1094 process_phase,
1095 process_status,
1096 group_id,
1097 source_code,
1098 source_line_id,
1099 job_name,
1100 organization_id,
1101 status_type,
1102 first_unit_start_date,
1103 last_unit_completion_date,
1104 start_quantity,
1105 net_quantity,
1106 class_code,
1107 primary_item_id,
1108 interface_id
1109 )
1110 VALUES
1111 (
1112 SYSDATE,
1113 l_user_id,
1114 SYSDATE,
1115 l_user_id,
1116 4,
1117 2,
1118 1,
1119 l_group_id,
1120 'MSC',
1121 X_REPAIR_LINE_ID,
1122 l_wip_job_name,
1123 l_ro_release.Organization_id,
1124 3,
1125 SYSDATE,
1126 l_ro_release.Promise_date,
1127 l_ro_release.Quantity,
1128 l_ro_release.Quantity,
1129 'Rework',
1130 l_ro_release.Inventory_item_id,
1131 l_group_id
1132 );
1133
1134 LOG_MESSAGE('Inserted into wip_job_schedule_interface group id '||to_char(l_group_id));
1135
1136 LOG_MESSAGE('Calling API WIP_MASSLOAD_PUB.createOneJob');
1137
1138 WIP_MASSLOAD_PUB.createOneJob( p_interfaceID => l_group_id,
1139 p_validationLevel => FND_API.G_VALID_LEVEL_FULL,
1140 x_wipEntityID => x_wipEntityID,
1141 x_returnStatus => x_return_status,
1142 x_errorMsg => x_msg_data );
1143
1144 LOG_MESSAGE('Return Status. :'||X_Return_Status);
1145 LOG_MESSAGE('x_wipEntityID :'||x_wipEntityID);
1146
1147 log_output('Wip Job:'||l_wip_job_name);
1148
1149 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1150 LOG_MESSAGE('Error Rep-work order creation : FND_API.G_RET_STS_UNEXP_ERROR');
1151 -- /* rollback ; */ commit;
1152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1153 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1154 LOG_MESSAGE('Error in Rep-work order creation : FND_API.G_RET_STS_ERROR');
1155 rollback ;
1156 RAISE FND_API.G_EXC_ERROR;
1157 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1158 LOG_MESSAGE('Successful in Creating Rep-work .');
1159 LOG_MESSAGE('wip etity id :' ||x_wipEntityID);
1160 -- commit ;
1161 LOG_MESSAGE('-------------------------------------------------------------------');
1162 LOG_MESSAGE('Calling API To Connect Wip job with the repair Order');
1163
1164 BEGIN -- Code to call API to link WIp job To REpair ORder
1165 csd_to_form_repair_job_xref.validate_and_write(
1166 p_api_version_number => 1.0,
1167 p_init_msg_list => FND_API.G_FALSE,
1168 p_commit => FND_API.G_FALSE,
1169 p_validation_level => NULL,
1170 p_action_code => 0,
1171 px_repair_job_xref_id => px_REPAIR_JOB_XREF_ID,
1172 p_created_by => l_user_id,
1173 p_creation_date => SYSDATE,
1174 p_last_updated_by => l_user_id,
1175 p_last_update_date => SYSDATE,
1176 p_last_update_login => l_user_id,
1177 p_repair_line_id => X_REPAIR_LINE_ID, --10960,
1178 p_wip_entity_id => x_wipEntityID, --760063,
1179 p_group_id => l_group_id, --required
1180 p_organization_id => l_ro_release.Organization_id,
1181 p_quantity => l_ro_release.Quantity,
1182 p_INVENTORY_ITEM_ID => l_ro_release.Inventory_item_id,
1183 p_ITEM_REVISION => null,
1184 p_OBJECT_VERSION_NUMBER => 1,
1185 p_attribute_category => NULL,
1186 p_attribute1 => NULL,
1187 p_attribute2 => NULL,
1188 p_attribute3 => NULL,
1189 p_attribute4 => NULL,
1190 p_attribute5 => NULL,
1191 p_attribute6 => NULL,
1192 p_attribute7 => NULL,
1193 p_attribute8 => NULL,
1194 p_attribute9 => NULL,
1195 p_attribute10 => NULL,
1196 p_attribute11 => NULL,
1197 p_attribute12 => NULL,
1198 p_attribute13 => NULL,
1199 p_attribute14 => NULL,
1200 p_attribute15 => NULL,
1201 p_quantity_completed => NULL,
1202 p_job_name => l_wip_job_name,
1203 p_source_type_code => 'MANUAL',
1204 p_source_id1 => NULL,
1205 p_ro_service_code_id => NULL,
1206 x_return_status => l_return_status,
1207 x_msg_count => X_Msg_Count,
1208 x_msg_data => X_Msg_Data);
1209
1210 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1211 LOG_MESSAGE('Error Rep-work order updation : FND_API.G_RET_STS_UNEXP_ERROR');
1212 rollback ;
1213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1214 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1215 LOG_MESSAGE('Error in Rep-work order updation : FND_API.G_RET_STS_ERROR');
1216 rollback ;
1217 RAISE FND_API.G_EXC_ERROR;
1218 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1219 LOG_MESSAGE('Successful in updating Rep-work .');
1220 LOG_MESSAGE('px_REPAIR_JOB_XREF_ID :' ||px_REPAIR_JOB_XREF_ID);
1221 commit ;
1222 END IF;
1223 LOG_MESSAGE('Ending....');
1224 EXCEPTION
1225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1226 LOG_MESSAGE('Error : FND_API.G_EXC_UNEXPECTED_ERROR');
1227 LOG_MESSAGE(SQLERRM);
1228 -- retcode := 1;
1229 -- rollback ;
1230 LOG_MESSAGE('Transaction rolled back');
1231 LOG_MESSAGE('----------------------------------------------------------');
1232
1233 WHEN FND_API.G_EXC_ERROR THEN
1234 LOG_MESSAGE('Error : FND_API.G_EXC_ERROR');
1235 LOG_MESSAGE(SQLERRM);
1236 -- retcode := 1;
1237 -- rollback ;
1238 LOG_MESSAGE('Transaction rolled back');
1239 LOG_MESSAGE('----------------------------------------------------------');
1240 WHEN OTHERS THEN
1241 LOG_MESSAGE('Error : Err OTHERS');
1242 LOG_MESSAGE(SQLERRM);
1243 -- retcode := 1;
1244 -- rollback ;
1245 LOG_MESSAGE('Transaction rolled back');
1246 LOG_MESSAGE('----------------------------------------------------------');
1247
1248 END;
1249 END IF;
1250 LOG_MESSAGE('x_return_status :'||x_return_status);
1251 LOG_MESSAGE('Ending....');
1252 EXCEPTION
1253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1254 LOG_MESSAGE('Error : FND_API.G_EXC_UNEXPECTED_ERROR');
1255 LOG_MESSAGE(SQLERRM);
1256 -- retcode := 1;
1257 -- rollback ;
1258 LOG_MESSAGE('Transaction rolled back');
1259 LOG_MESSAGE('----------------------------------------------------------');
1260
1261 WHEN FND_API.G_EXC_ERROR THEN
1262 LOG_MESSAGE('Error : FND_API.G_EXC_ERROR');
1263 LOG_MESSAGE(SQLERRM);
1264 -- retcode := 1;
1265 -- rollback ;
1266 LOG_MESSAGE('Transaction rolled back');
1267 LOG_MESSAGE('----------------------------------------------------------');
1268 WHEN OTHERS THEN
1269 LOG_MESSAGE('Error : Err OTHERS');
1270 LOG_MESSAGE(SQLERRM);
1271 -- retcode := 1;
1272 -- rollback ;
1273 LOG_MESSAGE('Transaction rolled back');
1274 LOG_MESSAGE('----------------------------------------------------------');
1275 END;
1276
1277 END;
1278 END IF;
1279
1280 END; -- BEGIN Block of repair order API
1281
1282 END IF; -- outer block of incident
1283
1284
1285 END;
1286
1287 end if ;
1288 END;
1289 END loop;
1290
1291 --======================================================
1292
1293 For l_ro_release in ro_release_data(p_batch_id) loop
1294 BEGIN
1295
1296 Savepoint Before_MOVE_IN ;
1297
1298 Update mrp_org_transfer_release
1299 set part_condition ='B'
1300 where transaction_id= l_ro_release.In_req_transaction_id;
1301
1302
1303 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 );
1304 l_iso_header_id1 := l_iso_header_id;
1305 l_req_header_id1:= l_req_header_id ;
1306
1307 if (retcode=0 ) then
1308 Update mrp_org_transfer_release
1309 set part_condition ='G'
1310 where transaction_id= l_ro_release.out_req_transaction_id;
1311
1312 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 );
1313 l_iso_header_id2 := l_iso_header_id;
1314 l_req_header_id2:= l_req_header_id ;
1315
1316 end if ;
1317
1318 if (retcode=0 ) then
1319 CSD_Refurbish_IRO_GRP.Create_InternalRO
1320 ( 1.0,
1321 FND_API.G_FALSE,
1322 FND_API.G_FALSE,
1323 FND_API.G_VALID_LEVEL_FULL,
1324 x_return_status ,
1325 p_msg_count ,
1326 x_msg_data ,
1327 l_req_header_id1,
1328 l_iso_header_id1,
1329 l_req_header_id2,
1330 l_iso_header_id2,
1331 x_service_request_number);
1332
1333 IF p_msg_count IS NOT NULL THEN
1334 IF p_msg_count = 1 THEN
1335 l_msg := fnd_msg_pub.get(p_msg_index => 1,
1336 p_encoded => 'F' );
1337 log_message(l_msg);
1338 ELSIF p_msg_count > 1 THEN
1339 FOR i IN 1..p_msg_count
1340 LOOP
1341 l_msg := fnd_msg_pub.get(p_msg_index => i,
1342 p_encoded => 'F' );
1343 Fnd_file.put_line(fnd_file.LOG,l_msg);
1344 END LOOP;
1345 END IF;
1346
1347 END IF;
1348
1349 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1350 LOG_MESSAGE('Error in Repair order creation : FND_API.G_RET_STS_UNEXP_ERROR');
1351 rollback ;
1352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1353 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1354 LOG_MESSAGE('Error in Repair order creation : FND_API.G_RET_STS_ERROR');
1355 rollback ;
1356 RAISE FND_API.G_EXC_ERROR;
1357 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1358 log_message('Successful in Creating Repair Req .');
1359 log_message('service request number :' ||x_service_request_number);
1360 log_output('Successful in Creating Repair Req .');
1361 log_output('service request number :' ||x_service_request_number);
1362 commit ;
1363 END IF;
1364
1365 end if ;
1366
1367 if (l_ro_release.in_req_quantity <> l_ro_release.quantity and retcode =0 ) then
1368 select repair_line_id into lv_repair_line_id
1369 from csd_product_transactions
1370 where action_type='MOVE_IN'
1371 and REQ_HEADER_ID= l_req_header_id1;
1372
1373 /* call to update iro */
1374 update_iro(errbuf=>errbuf,
1375 retcode=>retcode,
1376 p_repair_line_id=>lv_repair_line_id,
1377 p_quantity=>l_ro_release.quantity,
1378 p_promise_date=> l_ro_release.Promise_date) ;
1379 if retcode =0 then
1380 commit;
1381 end if ;
1382 end if ;
1383
1384 if retcode <> 0 Then
1385 rollback to savepoint Before_MOVE_IN;
1386 global_retcode := retcode;
1387 end if;
1388
1389 EXCEPTION
1390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1391 LOG_MESSAGE('Error in Release_new_IRO : FND_API.G_EXC_UNEXPECTED_ERROR');
1392 LOG_MESSAGE(SQLERRM);
1393 retcode := 1;
1394 rollback ;
1395 log_message('Transaction rolled back');
1396 log_message('----------------------------------------------------------');
1397
1398 WHEN FND_API.G_EXC_ERROR THEN
1399 LOG_MESSAGE('Error in Release_new_IRO : FND_API.G_EXC_ERROR');
1400 LOG_MESSAGE(SQLERRM);
1401 retcode := 1;
1402 rollback ;
1403 log_message('Transaction rolled back');
1404 log_message('----------------------------------------------------------');
1405 WHEN OTHERS THEN
1406 LOG_MESSAGE('Error in Release_new_IRO : Err OTHERS');
1407 LOG_MESSAGE(SQLERRM);
1408 retcode := 1;
1409 rollback ;
1410 log_message('Transaction rolled back');
1411 log_message('----------------------------------------------------------');
1412 END ;
1413
1414 END loop;
1415 retcode := global_retcode;
1416
1417 DELETE FROM MRP_IRO_RELEASE WHERE BATCH_ID=p_batch_id ;
1418 Commit;
1419 DELETE FROm MRP_ORG_TRANSFER_RELEASE WHERE BATCH_ID=p_batch_id;
1420 Commit;
1421 END Release_new_IRO;
1422
1423
1424 END MSC_SRP_RELEASE;