[Home] [Help]
PACKAGE BODY: APPS.CSP_REPAIR_PO_PVT
Source
1 PACKAGE BODY CSP_REPAIR_PO_PVT AS
2 /* $Header: cspgrexb.pls 120.21 2006/08/25 23:55:32 ajosephg noship $ */
3
4 -- Purpose: To create Repair execution
5 -- Start of Comments
6 -- Package name : CSP_REPAIR_PO_PVT
7 -- Purpose : This package creates Repair Purchase Order Execution details.
8 -- History : 05-July-2005, Arul Joseph.
9 -- NOTE :
10 -- End of Comments
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_REPAIR_PO_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspgrexb.pls';
14
15 Procedure Add_Err_Msg Is
16 l_msg_index_out NUMBER;
17 x_msg_data_temp Varchar2(2000);
18 x_msg_data Varchar2(4000);
19 Begin
20 If fnd_msg_pub.count_msg > 0 Then
21 FOR i IN REVERSE 1..fnd_msg_pub.count_msg
22 Loop
23 fnd_msg_pub.get(p_msg_index => i,
24 p_encoded => 'F',
25 p_data => x_msg_data_temp,
26 p_msg_index_out => l_msg_index_out);
27 x_msg_data := x_msg_data || x_msg_data_temp;
28 End Loop;
29 FND_FILE.put_line(FND_FILE.log,x_msg_data);
30 fnd_msg_pub.delete_msg;
31 End if;
32 End;
33
34 PROCEDURE RUN_REPAIR_EXECUTION
35 (errbuf OUT NOCOPY VARCHAR2,
36 retcode OUT NOCOPY NUMBER,
37 p_Api_Version_Number IN NUMBER,
38 p_repair_po_header_id IN NUMBER default null
39 )
40 IS
41
42 CURSOR C_CSP_REPAIR_PO_HEADERS(l_status NUMBER) IS
43 SELECT *
44 FROM CSP_REPAIR_PO_HEADERS
45 WHERE STATUS = l_status
46 ORDER BY REPAIR_PO_HEADER_ID
47 FOR UPDATE OF STATUS;
48
49 /** Instead of FOR UPDATE selecting rowid which helps to update the current row and do commit inside the loop **/
50 CURSOR CSP_REPAIR_PO_HEADERS_ROW(l_status NUMBER) IS
51 SELECT rowid, CRPH.*
52 FROM CSP_REPAIR_PO_HEADERS CRPH
53 WHERE STATUS = l_status
54 ORDER BY REPAIR_PO_HEADER_ID;
55
56 CURSOR PO_REQ_INTERFACE_ALL(l_requisition_number NUMBER,l_requisition_line_id NUMBER) IS
57 SELECT PRIL.authorization_status, PRIL.req_number_segment1
58 FROM PO_REQUISITIONS_INTERFACE_ALL PRIL
59 WHERE PRIL.req_number_segment1 = l_requisition_number
60 AND PRIL.requisition_line_id = l_requisition_line_id;
61
62 /** Possible to check only in the PO_REQUISITION_HEADERS_ALL table and no need to join with PO_REQUISITION_LINES_ALL table **/
63 CURSOR PO_REQ_HEADERS_ALL(l_requisition_number NUMBER, l_requisition_line_id NUMBER) IS
64 SELECT PRH.REQUISITION_HEADER_ID,PRH.AUTHORIZATION_STATUS,PRH.segment1
65 FROM PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL
66 WHERE PRH.SEGMENT1 = l_requisition_number AND
67 PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
68 PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID;
69
70 CURSOR PO_HEADERS_ALL(l_requisition_line_id NUMBER) IS
71 SELECT POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, POH.closed_code,
72 PLL.line_location_id, PLL.po_line_id
73 FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
74 WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
75 PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
76 PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
77
78 CURSOR CSP_RESERVED_LINES(L_REPAIR_PO_HEADER_ID NUMBER) IS
79 SELECT CRL.*, CRH.dest_organization_id
80 FROM CSP_REPAIR_PO_HEADERS CRH, CSP_REPAIR_PO_LINES CRL
81 WHERE CRL.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
82 AND CRL.REPAIR_PO_HEADER_ID = CRH.REPAIR_PO_HEADER_ID;
83
84 /*
85 CURSOR IO_QTY_RECEIVED_CHECK (L_HEADER_ID NUMBER)IS
86 SELECT * FROM OE_ORDER_HEADERS_ALL OEH,
87 OE_ORDER_LINES_ALL OEL, PO_REQUISITION_LINES_ALL PRL
88 WHERE OEH.HEADER_ID = L_HEADER_ID AND
89 OEH.HEADER_ID = OEL.HEADER_ID AND
90 OEL.SOURCE_DOCUMENT_ID = PRL.REQUISITION_HEADER_ID AND
91 OEL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID;
92 */
93
94 CURSOR IO_QTY_RECEIVED_CHECK (L_HEADER_ID NUMBER)IS
95 SELECT PRL.QUANTITY_RECEIVED,
96 PRL.ITEM_ID,
97 PRL.DESTINATION_ORGANIZATION_ID,
98 PRL.DESTINATION_SUBINVENTORY
99 FROM OE_ORDER_HEADERS_ALL OEH,
100 PO_REQUISITION_LINES_ALL PRL
101 WHERE OEH.HEADER_ID = L_HEADER_ID
102 AND OEH.SOURCE_DOCUMENT_ID = PRL.REQUISITION_HEADER_ID;
103
104 /*
105 1.PRL.quantity_received is null (and)
106 PRL.quantity_delivered shows the PO received qty.
107
108 2.Based on the above scenario, we have to use
109 PLL.quantity_received (or) PRL.quantity_delivered
110 to select quantity_received so far for this PO.
111 */
112
113 CURSOR PO_REQ_RECEIVED_QTY(l_requisition_line_id NUMBER) IS
114 SELECT PLL.quantity_received, PRL.closed_code,
115 POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, -- POH.closed_code,
116 PLL.line_location_id, PLL.po_line_id
117 FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
118 WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
119 PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
120 PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
121
122 CURSOR CSP_REPAIR_PO_SCRAP(L_REPAIR_PO_HEADER_ID NUMBER,
123 L_SCRAP_ITEM_ID NUMBER,
124 L_QUANTITY NUMBER
125 ) IS
126 SELECT CRPH.repair_po_header_id,
127 CRPH.wip_id,
128 CRPH.inventory_item_id,
129 CRPH.repair_supplier_org_id,
130 CRPH.quantity,
131 CRPH.received_qty,
132 CRPL.inventory_item_id defect_item_id,
133 CRPL.defective_organization_id,
134 CRPL.quantity defect_qty,
135 CRPL.received_qty defect_received_qty,
136 CRPL.SCRAP_QTY,
137 CRPL.ADJUSTED_QTY
138 FROM CSP_REPAIR_PO_HEADERS CRPH, CSP_REPAIR_PO_LINES CRPL
139 WHERE CRPH.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
140 AND CRPH.status = 8
141 AND CRPH.repair_po_header_id = CRPL.repair_po_header_id
142 AND CRPL.inventory_item_id = L_SCRAP_ITEM_ID;
143
144
145 l_api_version_number CONSTANT NUMBER := 1.0;
146 l_api_name CONSTANT VARCHAR2(30) := 'RUN_REPAIR_EXECUTION';
147
148 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
149 l_commit VARCHAR2(1) := FND_API.G_TRUE;
150 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
151
152 x_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
153 x_msg_count NUMBER;
154 x_msg_data VARCHAR2(2000);
155
156 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
157 l_msg_count NUMBER;
158 l_msg_data VARCHAR2(2000);
159
160 l_sqlcode NUMBER;
161 l_sqlerrm VARCHAR2(2000);
162 g_retcode NUMBER := 0;
163 l_Rollback VARCHAR2(1) := 'Y';
164
165 l_today DATE;
166 l_user_id NUMBER;
167 l_login_id NUMBER;
168
169 EXCP_USER_DEFINED EXCEPTION;
170
171 x_relieved_quantity NUMBER;
172 l_reservation_rec CSP_REPAIR_PO_PVT.out_reserve_rec_type;
173
174 x_item_number VARCHAR2(40);
175 x_item_description VARCHAR2(240);
176 l_primary_uom_code VARCHAR2(3);
177 l_org_name VARCHAR2(240);
178 l_sec_inv_name VARCHAR2(240);
179 L_ORGANIZATION_NAME VARCHAR2(240);
180
181 l_header_rec csp_parts_requirement.header_rec_type;
182 l_line_rec csp_parts_requirement.line_rec_type;
183 l_line_tbl csp_parts_requirement.line_Tbl_type;
184 l_dest_organization_id NUMBER;
185 I NUMBER;
186 l_ship_to_location_id NUMBER;
187
188 L_authorization_status VARCHAR2(240);
189 L_req_number_segment1 VARCHAR2(240);
190 l_need_by_date DATE;
191
192 L_CLASS_CODE VARCHAR2(240);
193 l_WIP_BATCH_ID NUMBER;
194 l_WIP_ENTITY_ID NUMBER;
195
196 px_transaction_header_id NUMBER;
197 t_transaction_id NUMBER;
198 l_RECEIVED_QTY NUMBER;
199 l_wib_issue_qty NUMBER;
200 FINAL_COMPLETION_FLAG VARCHAR2(1);
201 l_usable_subinv VARCHAR2(240);
202 l_defective_subinv VARCHAR2(240);
203 l_total_scrap_adjust_qty NUMBER;
204 l_org_id NUMBER;
205 l_wip_status_type NUMBER;
206 l_WIP_ENTITY_ID_INTERFACE NUMBER;
207
208 BEGIN
209
210 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
211
212 /** Standard call to check for call compatibility **/
213 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
214 p_api_version_number,
215 l_api_name,
216 G_PKG_NAME)
217 THEN
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219 END IF;
220
221 /** Initialize message list **/
222 IF fnd_api.to_boolean(l_Init_Msg_List) THEN
223 FND_MSG_PUB.initialize;
224 END IF;
225
226 /** Initialize return status **/
227 x_return_status := FND_API.G_RET_STS_SUCCESS;
228
229 /** User and login information **/
230 SELECT Sysdate INTO l_today FROM dual;
231 l_user_id := fnd_global.user_id;
232 l_login_id := fnd_global.login_id;
233
234 ---- Start Step:1 ----
235
236 /**
237 For all the Repair_po's with status '1'
238 i.e In PO_REQUISITIONS_INTERFACE_ALL table AUTHORIZATION_STATUS = 'INCOMPLETE' (or) 'IN PROCESS' (or) 'REJECTED' (or) other.
239 Check AUTHORIZATION_STATUS in PO_REQUISITIONS_INTERFACE_ALL is moved to 'APPROVED'
240 If that is true or record is moved to PO_REQUISITION_HEADERS_ALL then update the status = 2
241 else keep status = 1 as it may be still 'IN PROCESS' (or) 'REJECTED' (or) other status
242 **/
243
244 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(1)
245 LOOP
246 OPEN PO_REQ_INTERFACE_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_NUMBER, CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID);
247 LOOP
248 FETCH PO_REQ_INTERFACE_ALL INTO L_authorization_status, L_req_number_segment1;
249 IF (PO_REQ_INTERFACE_ALL%ROWCOUNT = 0) THEN
250 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 2
251 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
252
253 EXIT;
254 ELSIF PO_REQ_INTERFACE_ALL%FOUND and NVL(L_authorization_status,'APPROVED') = 'APPROVED' THEN
255 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 2
256 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
257
258 EXIT;
259 ELSE
260 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 1
261 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
262
263 EXIT;
264 END IF;
265 END LOOP;
266 CLOSE PO_REQ_INTERFACE_ALL;
267 END LOOP;
268
269 COMMIT;
270
271 ---- End Step:1 ----
272
273 ---- Start Step:2 ----
274
275 /**
276 For all the Repair_po whose status is '2'
277 i.e In PO_REQUISITION_HEADERS_ALL table AUTHORIZATION_STATUS = 'CREATED' or 'IN PROCESS' or other
278 check AUTHORIZATION_STATUS in PO_REQUISITION_HEADERS_ALL is moved to 'APPROVED'
279 If that is true update the status = 3
280 else keep status = 2 as it may be still in 'IN PROCESS' (or) 'REJECTED' (or) other status
281 **/
282 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
283
284 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(2)
285 LOOP
286 FOR PO_REQ_HEADERS_ALL_rec IN PO_REQ_HEADERS_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_NUMBER, CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID)
287 LOOP
288 If PO_REQ_HEADERS_ALL_rec.AUTHORIZATION_STATUS = 'APPROVED' then
289 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 3,REQUISITION_HEADER_ID = PO_REQ_HEADERS_ALL_rec.REQUISITION_HEADER_ID
290 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
291 End if;
292 END LOOP;
293 END LOOP;
294
295 COMMIT;
296
297 ---- End Step:2 ----
298
299 ---- Start Step:3 ----
300
301 /** For all the repair_po with status '3'
302 If record is created in PO_HEADERS_ALL table
303 and CLOSED_CODE (or) AUTHORIZATION_STATUS is not 'APPROVED' then update the status = 4.
304 Else if CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED' then update the status = 5.
305 **/
306
307 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
308
309 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(3)
310 LOOP
311 FOR PO_HEADERS_ALL_rec IN PO_HEADERS_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID)
312 LOOP
313 If NVL(PO_HEADERS_ALL_rec.CLOSED_CODE,'OPEN') = 'OPEN'
314 OR NVL(PO_HEADERS_ALL_rec.AUTHORIZATION_STATUS,'OPEN') = 'OPEN' then
315 UPDATE CSP_REPAIR_PO_HEADERS
316 SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
317 PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 4
318 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
319 Elsif NVL(PO_HEADERS_ALL_rec.CLOSED_CODE,'OPEN') = 'APPROVED'
320 OR NVL(PO_HEADERS_ALL_rec.AUTHORIZATION_STATUS,'OPEN') = 'APPROVED' then
321 UPDATE CSP_REPAIR_PO_HEADERS
322 SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
323 PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 5
324 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
325 End if;
326 END LOOP;
327 END LOOP;
328
329 COMMIT;
330
331 ---- End Step:3 ----
332
333 ---- Start Step:4 ----
334
335 /** For all the repair_po with status '4'
336 If record is created in PO_HEADERS_ALL table
337 and CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED' then update the status = 5.
338 **/
339
340 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
341
342 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(4)
343 LOOP
344 FOR PO_HEADERS_ALL_rec IN PO_HEADERS_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID)
345 LOOP
346 If NVL(PO_HEADERS_ALL_rec.CLOSED_CODE,'OPEN') = 'APPROVED'
347 OR NVL(PO_HEADERS_ALL_rec.AUTHORIZATION_STATUS,'OPEN') = 'APPROVED' then
348 UPDATE CSP_REPAIR_PO_HEADERS
349 SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
350 PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 5
351 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
352 End if;
353 END LOOP;
354 END LOOP;
355
356 COMMIT;
357
358 ---- End Step:4 ----
359
360 ---- Start Step:5 ----
361 /** For all the repair_po with status '5'
362 i.e Record is created in PO_HEADERS_ALL table and CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED'
363 If no internal order is created and REPAIR_PROGRAM <> 'PRE-POSITIONING' then
364 create an internal order and cancel the existing reservation.
365 **/
366
367 /*
368 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
369 SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
370 INTO l_org_id
371 FROM dual;
372
373 po_moac_utils_pvt.set_org_context(l_org_id);
374
375 */
376
377 MO_GLOBAL.init('CSF');
378
379 --SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
380
381 FOR CSP_REPAIR_PO_HEADERS_rec IN CSP_REPAIR_PO_HEADERS_ROW(5)
382 LOOP
383
384 SAVEPOINT RUN_REPAIR_EXECUTION_PVT; /* Create this save point if the commit not exits the loop */
385
386 If (CSP_REPAIR_PO_HEADERS_rec.STATUS = 5 AND
387 CSP_REPAIR_PO_HEADERS_rec.INTERNAL_ORDER_HEADER_ID IS NULL) Then
388 --AND CSP_REPAIR_PO_HEADERS_rec.REPAIR_PROGRAM ='3') Then -- 'Repair Return'
389
390 I := 1;
391
392 FOR CSP_RESERVED_LINES_rec IN CSP_RESERVED_LINES(CSP_REPAIR_PO_HEADERS_rec.REPAIR_PO_HEADER_ID)
393 LOOP
394
395 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
396 (CSP_RESERVED_LINES_rec.defective_organization_id
397 ,CSP_RESERVED_LINES_rec.inventory_item_id
398 ,x_item_number
399 ,x_item_description
400 ,l_primary_uom_code
401 ,x_return_status
402 ,x_msg_data
403 ,x_msg_count
404 );
405
406 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
407 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_RESERVED_LINES_rec.defective_organization_id);
408 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_DEFECTORG');
409 FND_MESSAGE.SET_TOKEN ('DEFECTIVE_ORG_NAME', L_ORG_NAME,TRUE);
410 FND_MSG_PUB.ADD;
411 Add_Err_Msg;
412 g_retcode := 1;
413 END IF;
414
415 l_reservation_rec.item_uom_code := l_primary_uom_code;
416
417 csp_sch_int_pvt.cancel_reservation(p_reserv_id => CSP_RESERVED_LINES_rec.reservation_id,
418 x_return_status => l_return_status,
419 x_msg_data => l_msg_data,
420 x_msg_count => l_msg_count);
421 /*
422 csp_sch_int_pvt.DELETE_RESERVATION(p_reservation_id => CSP_RESERVED_LINES_rec.reservation_id
423 ,x_return_status => l_return_status
424 ,x_msg_data => l_msg_data );
425 */
426
427 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
428 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
429 Add_Err_Msg;
430 g_retcode := 1;
431 errbuf := X_Msg_Data;
432 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
433
434 Elsif (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
435 l_line_rec.line_num := I; -- 1;
436 l_line_rec.inventory_item_id := CSP_RESERVED_LINES_rec.inventory_item_id;
437 --l_line_rec.item_description := 'Sentinel Standard Desktop';
438 l_line_rec.sourced_from := 'INVENTORY';
439 l_line_rec.ordered_quantity := CSP_RESERVED_LINES_rec.quantity;
440 l_line_rec.unit_of_measure := l_primary_uom_code;
441 l_line_rec.dest_subinventory := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_DEFECT_SUBINV');
442 --l_line_rec.dest_subinventory := 'FldSvc';
443 l_line_rec.source_organization_id := CSP_RESERVED_LINES_rec.defective_organization_id;
444 --l_line_Rec.order_line_id := 50762;
445 --l_line_rec.source_subinventory := 'Stores';
446 --l_line_rec.booked_flag := 'N'; --'Y'
447
448 l_line_tbl(I) := l_line_rec;
449 I := I+1;
450
451 End if;
452 END LOOP;
453
454 l_dest_organization_id := CSP_REPAIR_PO_HEADERS_rec.REPAIR_SUPPLIER_ORG_ID;
455 l_need_by_date := SYSDATE; /* CSP_REPAIR_PO_HEADERS_rec.need_by_date; */
456
457 /** 1.( Need_by_date of repair-to_item at dest org ) -
458 (Transit time between repair supplier org to dest org)
459 = Completion Date of repair-to_item at repair supplier org.
460 2. Completion Date - Repair_Lead_Time = Start date of the wip job
461 Here Internal order Need_by_date should be equal to Start date of the wip job.
462 **/
463
464 l_sec_inv_name := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_DEFECT_SUBINV');
465
466 Begin
467 SELECT LOCATION_ID
468 INTO l_ship_to_location_id
469 FROM MTL_SECONDARY_INVENTORIES
470 WHERE ORGANIZATION_ID = l_dest_organization_id
471 AND SECONDARY_INVENTORY_NAME = l_sec_inv_name; -- 'FldSvc'
472 Exception
473 when no_data_found then
474 l_ship_to_location_id := Null;
475 End;
476
477 If l_ship_to_location_id is null then
478 Begin
479 SELECT LOCATION_ID
480 INTO l_ship_to_location_id
481 FROM HR_ORGANIZATION_UNITS
482 WHERE ORGANIZATION_ID = l_dest_organization_id;
483 Exception
484 when no_data_found then
485 l_ship_to_location_id := Null;
486 End;
487 End if;
488
489 If l_ship_to_location_id is null then
490 L_ORGANIZATION_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(l_dest_organization_id);
491 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_SHIPTO_LOCATION_ID');
492 FND_MESSAGE.SET_TOKEN ('DESTINATION_ORG', L_ORGANIZATION_NAME, TRUE);
493 FND_MSG_PUB.ADD;
494 Add_Err_Msg;
495 g_retcode := 1;
496 End if;
497
498 --l_header_rec.description := 'Test Req';
499 --l_header_rec.order_type_id := 1430;
500 FND_PROFILE.GET('CSP_ORDER_TYPE', l_header_rec.order_type_id);
501 l_header_rec.dest_organization_id := l_dest_organization_id;
502 l_header_rec.operation := csp_parts_order.G_OPR_CREATE;
503 l_header_rec.ship_to_location_id := l_ship_to_location_id;
504 l_header_rec.requisition_number := NULL;
505 l_header_rec.order_header_id := NULL;
506 l_header_rec.requisition_header_id := NULL;
507 l_header_rec.need_by_date := l_need_by_Date;
508
509 csp_parts_order.process_order
510 (
511 p_api_version => 1.0
512 ,p_Init_Msg_List => FND_API.G_FALSE
513 ,p_commit => FND_API.G_FALSE
514 ,px_header_rec => l_header_rec
515 ,px_line_table => l_line_tbl
516 --,p_process_type => 'BOTH'(Default value is 'BOTH')
517 ,x_return_status => x_return_status
518 ,x_msg_count => x_msg_count
519 ,x_msg_data => x_msg_data
520 );
521
522 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
523 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
524 Add_Err_Msg;
525 g_retcode := 1;
526 errbuf := X_Msg_Data;
527 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
528 Elsif (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
529 /* STATUS = 6 => INTERNAL_ORDER CREATED */
530
531 UPDATE CSP_REPAIR_PO_HEADERS
532 SET INTERNAL_ORDER_HEADER_ID = l_header_rec.order_header_id,
533 STATUS = 6
534 -- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
535 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
536
537 COMMIT; /* Do this commit if it is not exits the loop */
538
539 End if;
540
541 End if;
542 END LOOP;
543
544 --COMMIT;
545
546 ---- End Step:5 ----
547
548 ---- Start Step:6 ----
549 /** Loop through each IO Lines for the Internal_order created and then check QTY_RECEIVED > 0
550 Check for existing WIP_JOB in WIP_ENTITIES that wip job is loaded through wip mass load or through API
551 if WIP_JOB NOT created already then insert to interface table and do wip issue transaction
552 elseif WIP_JOB created already and it is there in WIP_ENTITIES table then do wip issue transaction
553 **/
554
555 --SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
556
557 For I in 6..7 Loop
558
559 FOR CSP_REPAIR_PO_HEADERS_rec IN CSP_REPAIR_PO_HEADERS_ROW(I)
560 LOOP
561 /**Loop through each IO Lines for the Internal_order created and
562 then check QTY_RECEIVED > 0
563 **/
564
565 FOR IO_QTY_RECEIVED_CHECK_REC IN IO_QTY_RECEIVED_CHECK(CSP_REPAIR_PO_HEADERS_rec.INTERNAL_ORDER_HEADER_ID)
566 LOOP
567
568 SAVEPOINT RUN_REPAIR_EXECUTION_PVT; /* Create this save point if the commit not exits the loop */
569
570 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
571 (IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
572 ,IO_QTY_RECEIVED_CHECK_REC.item_id
573 ,x_item_number
574 ,x_item_description
575 ,l_primary_uom_code
576 ,x_return_status
577 ,x_msg_data
578 ,x_msg_count
579 );
580
581 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
582 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id);
583 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_REPAIRORG');
584 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_ORG_NAME,TRUE);
585 FND_MSG_PUB.ADD;
586 Add_Err_Msg;
587 g_retcode := 1;
588 END IF;
589
590 If nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) > 0 and
591 CSP_REPAIR_PO_HEADERS_rec.WIP_ID IS NULL THEN
592 /** Create a WIB_JOB if there is no WIB_JOB created for this INTERNAL_ORDER so far **/
593
594 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
595 into l_WIP_BATCH_ID
596 from dual;
597
598 SELECT WIP_ENTITIES_S.NEXTVAL
599 INTO l_WIP_ENTITY_ID
600 FROM DUAL;
601
602 Begin
603 SELECT CLASS_CODE
604 INTO L_CLASS_CODE
605 FROM WIP_NON_STANDARD_CLASSES_VAL_V
606 WHERE ORGANIZATION_ID = CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
607 AND CLASS_TYPE = 4
608 AND CLASS_CODE = 'Expense';
609 Exception
610 WHEN NO_DATA_FOUND THEN
611 L_CLASS_CODE := NULL;
612 End;
613
614 If L_CLASS_CODE is null then
615 L_ORGANIZATION_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id);
616 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_WIP_CLASS_CODE');
617 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG', L_ORGANIZATION_NAME, TRUE);
618 FND_MSG_PUB.ADD;
619 Add_Err_Msg;
620 g_retcode := 1;
621 End if;
622
623 Begin
624 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
625 LAST_UPDATE_DATE,
626 LAST_UPDATED_BY,
627 CREATION_DATE,
628 CREATED_BY,
629 GROUP_ID,
630 SOURCE_CODE,
631 SOURCE_LINE_ID,
632 PROCESS_PHASE,
633 PROCESS_STATUS,
634 ORGANIZATION_ID,
635 LOAD_TYPE,
636 PRIMARY_ITEM_ID,
637 START_QUANTITY,
638 STATUS_TYPE,
639 FIRST_UNIT_START_DATE,
640 FIRST_UNIT_COMPLETION_DATE,
641 LAST_UNIT_START_DATE,
642 LAST_UNIT_COMPLETION_DATE,
643 CLASS_CODE,
644 WIP_ENTITY_ID,
645 JOB_NAME
646 )
647 VALUES(
648 sysdate,
649 l_user_id,
650 sysdate,
651 l_user_id,
652 l_WIP_BATCH_ID,
653 'CSP',
654 CSP_REPAIR_PO_HEADERS_rec.INTERNAL_ORDER_HEADER_ID, --> (or) Pass CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
655 2, --> 2 Validation, 4 Completion
656 1, --> 1 Pending, 4 Complete
657 CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id,
658 4, --> LOAD_TYPE: 4 Non-standard discrete jobs, 3 update discrete jobs, 1 standard discrete jobs
659 CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID,
660 CSP_REPAIR_PO_HEADERS_rec.QUANTITY,
661 3, --> Status type: 3 Released, 4 Complete, 12 closed
662 SYSDATE, --> FIRST_UNIT_START_DATE
663 CSP_REPAIR_PO_HEADERS_rec.NEED_BY_DATE, --> FIRST_UNIT_COMPLETION_DATE,
664 SYSDATE, --> LAST_UNIT_START_DATE
665 CSP_REPAIR_PO_HEADERS_rec.NEED_BY_DATE, --> LAST_UNIT_COMPLETION_DATE
666 L_CLASS_CODE, --> 'Expense'
667 l_WIP_ENTITY_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
668 'REPAIR_EXECUTION'||l_WIP_ENTITY_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
669 );
670 Exception
671 When others then
672 l_sqlcode := SQLCODE;
673 l_sqlerrm := SQLERRM;
674 g_retcode := 1;
675 errbuf := SQLERRM;
676 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
677 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
678 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
679 fnd_msg_pub.add;
680 Add_Err_Msg;
681 Rollback to RUN_REPAIR_EXECUTION_PVT;
682 End;
683
684 l_WIP_ENTITY_ID_INTERFACE := l_WIP_ENTITY_ID;
685
686 /**
687 We could use the WIP api to create the WIP_JOB
688 instead of using WIP_MASS_LOAD program through form
689 **/
690
691 /** Check if WIP_JOB is created by WIP MASS LOAD PROGRAM **/
692
693 Begin
694 Select wip_entity_id
695 into l_wip_entity_id
696 from WIP_ENTITIES
697 Where wip_entity_id = l_WIP_ENTITY_ID;
698 --and wip_entity_name = 'REPAIR_EXECUTION'||l_WIP_ENTITY_ID;
699 Exception
700 when no_data_found then
701 l_wip_entity_id := Null;
702 End;
703
704 If l_wip_entity_id is not null then
705
706 /** Create Wip component issue transaction to the wip job **/
707
708 csp_transactions_pub.transact_material
709 ( p_api_version => 1.0
710 , p_init_msg_list => FND_API.G_FALSE
711 , p_commit => FND_API.G_FALSE
712 , px_transaction_header_id => px_transaction_header_id
713 , px_transaction_id => t_transaction_id
714 , p_inventory_item_id => IO_QTY_RECEIVED_CHECK_REC.item_id
715 , p_organization_id => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
716 , p_subinventory_code => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_SUBINVENTORY
717 , p_locator_id => null
718 , p_lot_number => null
719 , p_lot_expiration_date => NULL
720 , p_revision => null
721 , p_serial_number => null
722 , p_to_serial_number => null
723 , p_quantity => IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
724 , p_uom => l_primary_uom_code
725 , p_source_id => null
726 , p_source_line_id => null
727 , p_transaction_type_id => 35
728 , p_account_id => null
729 , p_transfer_to_subinventory => null
730 , p_transfer_to_locator => null
731 , p_transfer_to_organization => null
732 , p_online_process_flag => TRUE
733 , p_transaction_source_id => l_WIP_ENTITY_ID
734 , p_trx_source_line_id => null
735 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_WIP_ISSUE'
736 , p_waybill_airbill => null
737 , p_shipment_number => null
738 , p_freight_code => null
739 , p_reason_id => null
740 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
741 , p_expected_delivery_date => null
742 , x_return_status => l_return_status
743 , x_msg_count => l_msg_count
744 , x_msg_data => l_msg_data
745 );
746
747 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
748 -- Raise FND_API.G_EXC_UNEXPECTED_ERROR;
749 Add_Err_Msg;
750 g_retcode := 1;
751 errbuf := l_Msg_Data;
752 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
753 End if;
754
755 /* STATUS = 8 => 'WIP_JOB_CREATED' by WIP MASS LOAD PROGRAM */
756 UPDATE CSP_REPAIR_PO_HEADERS
757 SET WIP_ID = l_WIP_ENTITY_ID, STATUS = 8
758 -- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
759 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
760 Else
761 /* STATUS = 7 => Inserted into 'WIP_JOB_SCHEDULE_INTERFACE' */
762 UPDATE CSP_REPAIR_PO_HEADERS
763 SET WIP_ID = l_WIP_ENTITY_ID_INTERFACE, STATUS = 7
764 -- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
765 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
766 End if;
767
768 UPDATE CSP_REPAIR_PO_LINES
769 SET RECEIVED_QTY = IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
770 where repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
771 and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
772
773 COMMIT; /* Do this commit if it is not exits the loop */
774
775 /** Elseif WIB_JOB already created **/
776 Elsif CSP_REPAIR_PO_HEADERS_rec.WIP_ID IS NOT NULL THEN
777 --> Check if more parts are received by the following condition
778
779 Begin
780 SELECT RECEIVED_QTY
781 INTO l_RECEIVED_QTY
782 FROM CSP_REPAIR_PO_LINES
783 WHERE repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
784 and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
785 Exception
786 when no_data_found then
787 l_RECEIVED_QTY := Null;
788 End;
789
790 Begin
791 Select wip_entity_id
792 into l_wip_entity_id
793 from WIP_ENTITIES
794 Where wip_entity_id = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
795 --and wip_entity_name = 'REPAIR_EXECUTION'||l_WIP_ENTITY_ID;
796 Exception
797 when no_data_found then
798 l_wip_entity_id := Null;
799 End;
800
801 /** 1.Check if
802 (Parts qty issued to WIP_JOB before <
803 Current received qty in the "PO_REQUISITION_LINES_ALL" table for this internal order line)
804 2.Check if WIP_JOB is created by WIP MASS LOAD PROGRAM
805 */
806
807 If ( nvl(l_RECEIVED_QTY,0) < nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0)
808 or
809 (nvl(l_RECEIVED_QTY,0) <= nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0)
810 and CSP_REPAIR_PO_HEADERS_rec.STATUS = 7)
811 )
812 and l_wip_entity_id is not null then
813
814 /** Create Wip component issue transaction to the job
815 and issue parts qty as
816 (current received qty for this part in PO_REQ_LINES -
817 Qty received for this part before in CSP_REPAIR_PO_LINES)
818 to the existing WIP JOB by Calling CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL
819 */
820
821 l_wib_issue_qty := 0;
822
823 If CSP_REPAIR_PO_HEADERS_rec.STATUS = 7 then
824 If nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) > nvl(l_RECEIVED_QTY,0) then
825 l_wib_issue_qty := nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0);
826 Elsif nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) = nvl(l_RECEIVED_QTY,0) then
827 l_wib_issue_qty := nvl(l_RECEIVED_QTY,0);
828
829 End if;
830 Else
831 l_wib_issue_qty := nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) - nvl(l_RECEIVED_QTY,0);
832 End if;
833
834 If nvl(l_wib_issue_qty,0) > 0 then
835
836 csp_transactions_pub.transact_material
837 ( p_api_version => 1.0
838 , p_init_msg_list => FND_API.G_FALSE
839 , p_commit => FND_API.G_FALSE
840 , px_transaction_header_id => px_transaction_header_id
841 , px_transaction_id => t_transaction_id
842 , p_inventory_item_id => IO_QTY_RECEIVED_CHECK_REC.item_id
843 , p_organization_id => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
844 , p_subinventory_code => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_SUBINVENTORY
845 , p_locator_id => null
846 , p_lot_number => null
847 , p_lot_expiration_date => NULL
848 , p_revision => null
849 , p_serial_number => null
850 , p_to_serial_number => null
851 , p_quantity => l_wib_issue_qty
852 , p_uom => l_primary_uom_code
853 , p_source_id => null
854 , p_source_line_id => null
855 , p_transaction_type_id => 35
856 , p_account_id => null
857 , p_transfer_to_subinventory => null
858 , p_transfer_to_locator => null
859 , p_transfer_to_organization => null
860 , p_online_process_flag => TRUE
861 , p_transaction_source_id => l_WIP_ENTITY_ID
862 , p_trx_source_line_id => null
863 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_WIP_ISSUE'
864 , p_waybill_airbill => null
865 , p_shipment_number => null
866 , p_freight_code => null
867 , p_reason_id => null
868 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
869 , p_expected_delivery_date => null
870 , x_return_status => l_return_status
871 , x_msg_count => l_msg_count
872 , x_msg_data => l_msg_data
873 );
874
875 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
876 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
877 Add_Err_Msg;
878 g_retcode := 1;
879 errbuf := l_Msg_Data;
880 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
881 End if;
882 End if;
883
884 If CSP_REPAIR_PO_HEADERS_rec.STATUS = 7 THEN
885 If nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) > nvl(l_RECEIVED_QTY,0) then
886 l_wib_issue_qty := nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) - nvl(l_RECEIVED_QTY,0);
887 Elsif nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) = nvl(l_RECEIVED_QTY,0) then
888 l_wib_issue_qty := 0;
889 End if;
890
891 UPDATE CSP_REPAIR_PO_HEADERS
892 SET STATUS = 8
893 --WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
894 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
895 End if;
896
897 UPDATE CSP_REPAIR_PO_LINES
898 SET RECEIVED_QTY = NVL(RECEIVED_QTY,0) + nvl(l_wib_issue_qty,0)
899 --SET RECEIVED_QTY = NVL(RECEIVED_QTY,0) + IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
900 where repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
901 and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
902
903 COMMIT; /* Do this commit if it is not exits the loop */
904
905 End if;
906 END IF;
907
908 END LOOP;
909 END LOOP;
910
911 END LOOP;
912
913 -- COMMIT;
914 ---- End Step:6 ----
915
916
917 ---- Start Step:7 ----
918 /** We need to check the PO REQ received qty with the remaining job qty
919 IF it is equal then pass 'Y' else pass 'N' for final_completion_flag and do WIP Assembly Completion transaction
920 (WIP Assembly Completion: Tansaction_type_id: 44)
921
922 IF the value is 'Y' then it should automatically changes WIP_JOB to "Complete" state STATUS_TYPE 4.
923 IF the value is 'N' then WIP_JOB should be in the same "Released" state STATUS_TYPE 3.
924
925 At the end do the Miscellaneous Issue transaction (Miscellaneous Issue: Tansaction_type_id: 32)
926 **/
927
928 --SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
929
930 FOR CSP_REPAIR_PO_HEADERS_rec IN CSP_REPAIR_PO_HEADERS_ROW(8)
931 LOOP
932
933 FOR PO_REQ_RECEIVED_QTY_rec IN PO_REQ_RECEIVED_QTY(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID)
934 LOOP
935
936 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
937
938 If (NVL(PO_REQ_RECEIVED_QTY_rec.CLOSED_CODE,'OPEN') = 'APPROVED'
939 OR NVL(PO_REQ_RECEIVED_QTY_rec.AUTHORIZATION_STATUS,'OPEN') = 'APPROVED'
940 )
941 AND nvl(CSP_REPAIR_PO_HEADERS_rec.received_qty,0) < nvl(PO_REQ_RECEIVED_QTY_rec.quantity_received,0)
942 AND nvl(PO_REQ_RECEIVED_QTY_rec.quantity_received,0) > 0 then
943
944 Select sum(nvl(scrap_qty,0) + nvl(adjusted_qty,0))
945 into l_total_scrap_adjust_qty
946 from CSP_REPAIR_PO_LINES
947 where REPAIR_PO_HEADER_ID = CSP_REPAIR_PO_HEADERS_rec.REPAIR_PO_HEADER_ID
948 group by REPAIR_PO_HEADER_ID;
949
950 l_usable_subinv := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_USABLE_SUBINV');
951
952 If PO_REQ_RECEIVED_QTY_rec.quantity_received >= CSP_REPAIR_PO_HEADERS_rec.quantity - l_total_scrap_adjust_qty then
953 -- nvl(CSP_REPAIR_PO_HEADERS_rec.received_qty,0) = PO_REQ_RECEIVED_QTY_rec.quantity_received
954 FINAL_COMPLETION_FLAG := 'Y';
955 l_wip_status_type := 4;
956 Else
957 FINAL_COMPLETION_FLAG := 'N';
958 End if;
959
960 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
961 (CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
962 ,CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID
963 ,x_item_number
964 ,x_item_description
965 ,l_primary_uom_code
966 ,x_return_status
967 ,x_msg_data
968 ,x_msg_count
969 );
970
971 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
972 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id);
973 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIRITEM_AT_REPAIRORG');
974 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_ORG_NAME,TRUE);
975 FND_MSG_PUB.ADD;
976 Add_Err_Msg;
977 g_retcode := 1;
978 END IF;
979
980 csp_transactions_pub.transact_material
981 ( p_api_version => 1.0
982 , p_init_msg_list => FND_API.G_FALSE
983 , p_commit => FND_API.G_FALSE
984 , px_transaction_header_id => px_transaction_header_id
985 , px_transaction_id => t_transaction_id
986 , p_inventory_item_id => CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID
987 , p_organization_id => CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
988 , p_subinventory_code => l_usable_subinv
989 , p_locator_id => null
990 , p_lot_number => null
991 , p_lot_expiration_date => NULL
992 , p_revision => null
993 , p_serial_number => null
994 , p_to_serial_number => NULL
995 , p_quantity => PO_REQ_RECEIVED_QTY_rec.quantity_received - nvl(CSP_REPAIR_PO_HEADERS_rec.received_qty,0)
996 , p_uom => l_primary_uom_code
997 , p_source_id => null
998 , p_source_line_id => null
999 , p_transaction_type_id => 44
1000 , p_account_id => null
1001 , p_transfer_to_subinventory => null
1002 , p_transfer_to_locator => null
1003 , p_transfer_to_organization => null
1004 , p_online_process_flag => TRUE
1005 , p_transaction_source_id => CSP_REPAIR_PO_HEADERS_rec.wip_id
1006 , p_trx_source_line_id => null
1007 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_WIP_COMPLETE'
1008 , p_waybill_airbill => NULL
1009 , p_shipment_number => NULL
1010 , p_freight_code => NULL
1011 , p_reason_id => NULL
1012 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
1013 , p_expected_delivery_date => NULL
1014 , p_FINAL_COMPLETION_FLAG => FINAL_COMPLETION_FLAG -- May need to add this parameter for wip complete
1015 , x_return_status => l_return_status
1016 , x_msg_count => l_msg_count
1017 , x_msg_data => l_msg_data
1018 );
1019
1020 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1021 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1022 Add_Err_Msg;
1023 g_retcode := 1;
1024 errbuf := l_msg_Data;
1025 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
1026 End if;
1027
1028 /** 1.We can do this insert (only) if FINAL_COMPLETION_FLAG = 'Y'
1029 i.e PO_REQ_RECEIVED_QTY_rec.quantity_received = CSP_REPAIR_PO_HEADERS_rec.quantity - l_total_scrap_adjust_qty
1030 But This Insert may not be needed if FINAL_COMPLETION_FLAG is passed as 'Y' in the above call
1031
1032 2.If we do this insert then we could use the WIP api to update the WIP_JOB
1033 instead of using WIP_MASS_LOAD program form
1034 **/
1035
1036 --------------------- Start comment on Nov-29-2005 -----------------
1037 /*
1038
1039 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
1040 into l_WIP_BATCH_ID
1041 from dual;
1042
1043 Begin
1044 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
1045 LAST_UPDATE_DATE,
1046 LAST_UPDATED_BY,
1047 CREATION_DATE,
1048 CREATED_BY,
1049 GROUP_ID,
1050 SOURCE_CODE,
1051 SOURCE_LINE_ID,
1052 PROCESS_PHASE,
1053 PROCESS_STATUS,
1054 ORGANIZATION_ID,
1055 LOAD_TYPE,
1056 PRIMARY_ITEM_ID,
1057 START_QUANTITY,
1058 STATUS_TYPE,
1059 --FIRST_UNIT_START_DATE,
1060 --FIRST_UNIT_COMPLETION_DATE,
1061 --LAST_UNIT_START_DATE,
1062 --LAST_UNIT_COMPLETION_DATE,
1063 CLASS_CODE,
1064 WIP_ENTITY_ID,
1065 JOB_NAME
1066 )
1067 Select
1068 SYSDATE,
1069 l_user_id,
1070 SYSDATE,
1071 l_user_id,
1072 l_WIP_BATCH_ID,
1073 SOURCE_CODE,
1074 SOURCE_LINE_ID,
1075 decode(FINAL_COMPLETION_FLAG,'Y',4,2), --> 2 Validation, 4 Completion
1076 decode(FINAL_COMPLETION_FLAG,'Y',4,1), --> 1 Pending, 4 Complete
1077 ORGANIZATION_ID,
1078 3, --> Load type: 4 Create non-standard wip job, 3 Update non-standard wip job
1079 PRIMARY_ITEM_ID,
1080 START_QUANTITY,
1081 decode(FINAL_COMPLETION_FLAG,'Y',4,3), --> Status type: 3 Released, 4 Complete
1082 --FIRST_UNIT_START_DATE,
1083 --FIRST_UNIT_COMPLETION_DATE,
1084 --LAST_UNIT_START_DATE,
1085 --LAST_UNIT_COMPLETION_DATE,
1086 CLASS_CODE,
1087 CSP_REPAIR_PO_HEADERS_rec.WIP_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
1088 'REPAIR_EXECUTION'||CSP_REPAIR_PO_HEADERS_rec.WIP_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
1089 FROM WIP_DISCRETE_JOBS
1090 WHERE WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
1091
1092 Exception
1093 When others then
1094 l_sqlcode := SQLCODE;
1095 l_sqlerrm := SQLERRM;
1096 g_retcode := 1;
1097 errbuf := SQLERRM;
1098 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1099 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1100 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
1101 fnd_msg_pub.add;
1102 Add_Err_Msg;
1103 Rollback to RUN_REPAIR_EXECUTION_PVT;
1104 End;
1105 --------------------- End comment on Nov-29-2005 -----------------
1106 */
1107
1108 /** If possible update WIP_DISCRETE_JOBS directly to update the quantity completed so far.
1109 If FINAL_COMPLETION_FLAG = 'Y' and not automatically moved to complet status by mass upload
1110 update the STATUS_TYPE = 4(Complete) OR 12(Closed)
1111 **/
1112
1113 /*
1114 Update WIP_DISCRETE_JOBS
1115 set --QUANTITY_COMPLETED = nvl(QUANTITY_COMPLETED,0) + PO_REQ_RECEIVED_QTY_rec.quantity_received,
1116 LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
1117 DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
1118 Where WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
1119 */
1120 /** "MISCELLANEOUS ISSUE" : MATERIAL TRANSACTION : TRANSACTION_TYPE_ID (32): ----
1121 When PO qty is received and wip job qty is transacted to Usable Subinv through WIP Assembly Completion then
1122 From Repair Supplier Org's Usable Subinv do this "MISCELLANEOUS_ISSUE" MATERIAL TRANSACTION
1123 **/
1124
1125 csp_transactions_pub.transact_material
1126 ( p_api_version => 1.0
1127 , p_init_msg_list => FND_API.G_FALSE
1128 , p_commit => FND_API.G_FALSE
1129 , px_transaction_header_id => px_transaction_header_id
1130 , px_transaction_id => t_transaction_id
1131 , p_inventory_item_id => CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID
1132 , p_organization_id => CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
1133 , p_subinventory_code => l_usable_subinv
1134 , p_locator_id => null
1135 , p_lot_number => null
1136 , p_lot_expiration_date => NULL
1137 , p_revision => null
1138 , p_serial_number => null
1139 , p_to_serial_number => null
1140 , p_quantity => PO_REQ_RECEIVED_QTY_rec.quantity_received - nvl(CSP_REPAIR_PO_HEADERS_rec.received_qty,0)
1141 , p_uom => l_primary_uom_code
1142 , p_source_id => null
1143 , p_source_line_id => null
1144 , p_transaction_type_id => 32
1145 , p_account_id => null
1146 , p_transfer_to_subinventory => null
1147 , p_transfer_to_locator => null
1148 , p_transfer_to_organization => null
1149 , p_online_process_flag => TRUE
1150 , p_transaction_source_id => null
1151 , p_trx_source_line_id => null
1152 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_MISC_ISSUE'
1153 , p_waybill_airbill => null
1154 , p_shipment_number => null
1155 , p_freight_code => null
1156 , p_reason_id => null
1157 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
1158 , p_expected_delivery_date => null
1159 , x_return_status => l_return_status
1160 , x_msg_count => l_msg_count
1161 , x_msg_data => l_msg_data
1162 );
1163
1164 IF l_return_status <> FND_API.G_RET_STS_SUCCESS and nvl(l_msg_count, 0) > 0 THEN
1165 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166 Add_Err_Msg;
1167 g_retcode := 1;
1168 errbuf := l_msg_Data;
1169 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
1170 End if;
1171
1172 /** FINAL_COMPLETION_FLAG = 'Y' => Repair PO is Closed, WIP_JOB is Complete and ready to close
1173 FINAL_COMPLETION_FLAG = 'N' => Repair PO is not Closed, WIP_JOB is still open in released status_type
1174 **/
1175
1176 UPDATE CSP_REPAIR_PO_HEADERS
1177 SET received_qty = PO_REQ_RECEIVED_QTY_rec.quantity_received,
1178 STATUS = decode(FINAL_COMPLETION_FLAG,'Y',9,8)
1179 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
1180 --WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
1181
1182 COMMIT; /* Do this commit if it is not exits the loop */
1183
1184 End if;
1185 End loop;
1186 End loop;
1187
1188 --COMMIT;
1189 ---- End: Step7 ----
1190
1191 ---- Start Scrap/Adjustment ----
1192 /** WIP_ENTITY_TYPE should be '1', If it is '3' then show "WIP_NO_CHARGES_ALLOWED" error
1193 This transaction is for WIP job qty scrap
1194 Do "Return Components from WIP" (43) transaction and wip job qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1195
1196 Passing 'Y' for FINAL_COMPLETION_FLAG is not completing the WIP JOB automatically,
1197 So we need to insert record to WIP_JOB_SCHEDULE_INTERFACE with status_type as 'COMPLETE' VALUE 4.
1198 Then run the WIP_MASS_LOAD Concurrent program to change the JOB status to "COMPLETE".
1199
1200 Do Miscellaneous transaction or SCRAP/ADJUSTMENT transaction from defective subinv
1201
1202 Call REP_PO_SCRAP_ADJUST_TRANSACT(); ----> SCRAP/ADJUSTMENT transaction
1203 **/
1204
1205 IF FND_API.to_Boolean(l_commit) THEN
1206 COMMIT WORK;
1207 END IF;
1208
1209 /** Standard call to get message count and if count is 1, get message info. **/
1210 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1211 p_data => x_msg_data
1212 );
1213 /** Errbuf returns error messages and
1214 Retcode returns 0 = Success, 1 = Success with warnings, 2 = Error
1215 **/
1216
1217 errbuf := X_Msg_Data;
1218 retcode := g_retcode;
1219
1220 EXCEPTION
1221 WHEN FND_API.G_EXC_ERROR THEN
1222 Add_Err_Msg;
1223 retcode := 2;
1224 errbuf := X_Msg_Data;
1225
1226 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1227 P_API_NAME => L_API_NAME
1228 ,P_PKG_NAME => G_PKG_NAME
1229 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1230 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1231 ,P_ROLLBACK_FLAG => l_Rollback
1232 ,X_MSG_COUNT => X_MSG_COUNT
1233 ,X_MSG_DATA => X_MSG_DATA
1234 ,X_RETURN_STATUS => X_RETURN_STATUS);
1235
1236 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1237 Add_Err_Msg;
1238 retcode := 2;
1239 errbuf := X_Msg_Data;
1240
1241 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1242 P_API_NAME => L_API_NAME
1243 ,P_PKG_NAME => G_PKG_NAME
1244 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1245 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1246 ,P_ROLLBACK_FLAG => l_Rollback
1247 ,X_MSG_COUNT => X_MSG_COUNT
1248 ,X_MSG_DATA => X_MSG_DATA
1249 ,X_RETURN_STATUS => X_RETURN_STATUS);
1250
1251 Add_Err_Msg;
1252
1253 WHEN OTHERS THEN
1254 Rollback to RUN_REPAIR_EXECUTION_PVT;
1255
1256 l_sqlcode := SQLCODE;
1257 l_sqlerrm := SQLERRM;
1258 retcode := 2;
1259 errbuf := SQLERRM;
1260
1261 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1262 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1263 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
1264 fnd_msg_pub.add;
1265 Add_Err_Msg;
1266
1267 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1268 P_API_NAME => L_API_NAME
1269 ,P_PKG_NAME => G_PKG_NAME
1270 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1271 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1272 ,P_SQLCODE => l_sqlcode
1273 ,P_SQLERRM => l_sqlerrm
1274 ,P_ROLLBACK_FLAG => l_Rollback
1275 ,X_MSG_COUNT => X_MSG_COUNT
1276 ,X_MSG_DATA => X_MSG_DATA
1277 ,X_RETURN_STATUS => X_RETURN_STATUS);
1278
1279 errbuf := sqlerrm;
1280 retcode := 2;
1281 Add_Err_Msg;
1282 END RUN_REPAIR_EXECUTION;
1283
1284
1285 /** REPAIR_PO SCRAP/ADJUSTMENT TRANSACTION **/
1286
1287 PROCEDURE REP_PO_SCRAP_ADJUST_TRANSACT
1288 (p_Api_Version_Number IN NUMBER
1289 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1290 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1291 ,p_REPAIR_PO_HEADER_ID IN NUMBER
1292 ,p_SCRAP_ADJUST_FLAG IN VARCHAR2
1293 ,p_SCRAP_ADJUST_ITEM_ID IN NUMBER
1294 ,p_SCRAP_ADJUST_QTY IN NUMBER
1295 ,p_SCRAP_ADJUST_DATE IN DATE
1296 ,x_return_status OUT NOCOPY VARCHAR2
1297 ,x_msg_count OUT NOCOPY NUMBER
1298 ,x_msg_data OUT NOCOPY VARCHAR2
1299 ) IS
1300
1301 CURSOR CSP_REPAIR_PO_SCRAP(L_REPAIR_PO_HEADER_ID NUMBER,
1302 L_SCRAP_ADJUST_ITEM_ID NUMBER,
1303 L_QUANTITY NUMBER
1304 ) IS
1305 SELECT CRPH.repair_po_header_id,
1306 CRPH.wip_id,
1307 CRPH.inventory_item_id,
1308 CRPH.repair_supplier_org_id,
1309 CRPH.quantity,
1310 CRPH.received_qty,
1311 CRPL.inventory_item_id defect_item_id,
1312 CRPL.defective_organization_id,
1313 CRPL.quantity defect_qty,
1314 CRPL.received_qty defect_received_qty,
1315 CRPL.SCRAP_QTY,
1316 CRPL.ADJUSTED_QTY
1317 FROM CSP_REPAIR_PO_HEADERS CRPH, CSP_REPAIR_PO_LINES CRPL
1318 WHERE CRPH.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
1319 AND CRPH.status = 8 --> WIP_JOB created in WIP_ENTITIES table
1320 AND CRPH.repair_po_header_id = CRPL.repair_po_header_id
1321 AND CRPL.inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
1322
1323 l_api_version_number CONSTANT NUMBER := 1.0;
1324 l_api_name CONSTANT VARCHAR2(20) := 'REPAIR_PO_SCRAP';
1325
1326 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1327 l_msg_count NUMBER;
1328 l_msg_data VARCHAR2(2000);
1329 l_Rollback VARCHAR2(1) := 'Y';
1330
1331 l_today DATE;
1332 l_user_id NUMBER;
1333 l_login_id NUMBER;
1334
1335 L_REPAIR_PO_HEADER_ID NUMBER;
1336 L_SCRAP_ADJUST_FLAG VARCHAR2(240);
1337 L_SCRAP_ADJUST_ITEM_ID NUMBER;
1338 L_SCRAP_ADJUST_QTY NUMBER;
1339
1340 Balance_due_qty NUMBER;
1341 Available_scrap_qty NUMBER;
1342
1343 x_item_number VARCHAR2(40);
1344 x_item_description VARCHAR2(240);
1345 l_primary_uom_code VARCHAR2(3);
1346 l_org_name VARCHAR2(240);
1347
1348 EXCP_USER_DEFINED EXCEPTION;
1349
1350 l_WIP_BATCH_ID NUMBER;
1351 l_defective_subinv VARCHAR2(240);
1352 l_total_scrap_adjust_qty NUMBER;
1353 FINAL_COMPLETION_FLAG VARCHAR2(1);
1354 l_sqlcode NUMBER;
1355 l_sqlerrm VARCHAR2(2000);
1356
1357 px_transaction_header_id NUMBER;
1358 t_transaction_id NUMBER;
1359 l_transaction_type_id NUMBER;
1360 l_wip_status_type NUMBER;
1361
1362 BEGIN
1363 ---- Start Scrap / Adjustment ----
1364
1365 /**
1366 In CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL procedure,
1367 Insert into mtl_transactions_interface table's WIP_ENTITY_TYPE column value must be '1',
1368 if it is '3' then show "WIP_NO_CHARGES_ALLOWED" error.
1369
1370 This transaction is for WIP_JOB quantity Scrap/Adjustment
1371
1372 WIP_JOB qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1373 by doing material transaction of type "Return Components from WIP" (43)
1374
1375 Passing 'Y' for FINAL_COMPLETION_FLAG is not completing the WIP JOB automatically,
1376 So we need to insert record to WIP_JOB_SCHEDULE_INTERFACE with status_type as 'COMPLETE' VALUE 4.
1377 Then run the WIP_MASS_LOAD Concurrent program to change the JOB status to "COMPLETE".
1378
1379 Do Miscellaneous issue transaction or SCRAP/ADJUSTMENT transaction from
1380 Repair Supplier Org's defective subinv for the scrap qty
1381 **/
1382
1383 SAVEPOINT REPAIR_PO_SCRAP_PVT;
1384
1385 /** Standard call to check for call compatibility **/
1386 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1387 p_api_version_number,
1388 l_api_name,
1389 G_PKG_NAME)
1390 THEN
1391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1392 END IF;
1393
1394 /** Initialize message list **/
1395 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1396 FND_MSG_PUB.initialize;
1397 END IF;
1398
1399 /** Initialize return status **/
1400 x_return_status := FND_API.G_RET_STS_SUCCESS;
1401
1402 /** User and login information **/
1403 SELECT Sysdate INTO l_today FROM dual;
1404 l_user_id := fnd_global.user_id;
1405 l_login_id := fnd_global.login_id;
1406
1407 L_REPAIR_PO_HEADER_ID := P_REPAIR_PO_HEADER_ID;
1408 L_SCRAP_ADJUST_ITEM_ID := P_SCRAP_ADJUST_ITEM_ID;
1409 L_SCRAP_ADJUST_QTY := P_SCRAP_ADJUST_QTY;
1410 L_SCRAP_ADJUST_FLAG := p_SCRAP_ADJUST_FLAG;
1411
1412 FOR CSP_REPAIR_PO_SCRAP_rec IN CSP_REPAIR_PO_SCRAP(L_REPAIR_PO_HEADER_ID, L_SCRAP_ADJUST_ITEM_ID, L_SCRAP_ADJUST_QTY)
1413 LOOP
1414 Balance_due_qty := nvl(CSP_REPAIR_PO_SCRAP_rec.quantity,0) - nvl(CSP_REPAIR_PO_SCRAP_rec.received_qty,0);
1415 Available_scrap_qty := nvl(CSP_REPAIR_PO_SCRAP_rec.defect_received_qty,0) -
1416 (nvl(CSP_REPAIR_PO_SCRAP_rec.scrap_qty,0) + nvl(CSP_REPAIR_PO_SCRAP_rec.adjusted_qty,0));
1417
1418 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
1419 (CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id
1420 ,L_SCRAP_ADJUST_ITEM_ID
1421 ,x_item_number
1422 ,x_item_description
1423 ,l_primary_uom_code
1424 ,x_return_status
1425 ,x_msg_data
1426 ,x_msg_count
1427 );
1428 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1429 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id);
1430 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_REPAIRORG');
1431 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_ORG_NAME,TRUE);
1432 FND_MSG_PUB.ADD;
1433 RAISE EXCP_USER_DEFINED;
1434 END IF;
1435
1436 If nvl(L_SCRAP_ADJUST_QTY,0) > least(Balance_due_qty,Available_scrap_qty) then
1437 FND_MESSAGE.SET_NAME ('CSP','CSP_NOAVAIL_QTY_TO_SCRAP_ADJUST'); /* Not enough quantity to do scrap or adjustment transaction from WIP job*/
1438 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', X_ITEM_NUMBER,TRUE);
1439 FND_MSG_PUB.ADD;
1440 RAISE EXCP_USER_DEFINED;
1441 Else
1442 l_defective_subinv := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_DEFECT_SUBINV');
1443
1444 Select sum(nvl(scrap_qty,0) + nvl(adjusted_qty,0))
1445 into l_total_scrap_adjust_qty
1446 from CSP_REPAIR_PO_LINES
1447 where REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
1448 group by REPAIR_PO_HEADER_ID;
1449
1450 If CSP_REPAIR_PO_SCRAP_rec.received_qty = CSP_REPAIR_PO_SCRAP_rec.quantity - (L_SCRAP_ADJUST_QTY + l_total_scrap_adjust_qty) then
1451 FINAL_COMPLETION_FLAG := 'Y';
1452 l_wip_status_type := 4;
1453 Else
1454 FINAL_COMPLETION_FLAG := 'N';
1455 End if;
1456
1457 /** Do material transaction of type Return Components from WIP (43)
1458 i.e WIP_JOB qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1459 **/
1460
1461 csp_transactions_pub.transact_material
1462 ( p_api_version => 1.0
1463 , p_init_msg_list => FND_API.G_FALSE
1464 , p_commit => FND_API.G_FALSE
1465 , px_transaction_header_id => px_transaction_header_id
1466 , px_transaction_id => t_transaction_id
1467 , p_inventory_item_id => CSP_REPAIR_PO_SCRAP_rec.defect_item_id
1468 , p_organization_id => CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id
1469 , p_subinventory_code => l_defective_subinv
1470 , p_locator_id => null
1471 , p_lot_number => null
1472 , p_lot_expiration_date => null
1473 , p_revision => null
1474 , p_serial_number => null
1475 , p_to_serial_number => null
1476 , p_quantity => L_SCRAP_ADJUST_QTY
1477 , p_uom => l_primary_uom_code
1478 , p_source_id => null
1479 , p_source_line_id => null
1480 , p_transaction_type_id => 43
1481 , p_account_id => null
1482 , p_transfer_to_subinventory => null
1483 , p_transfer_to_locator => null
1484 , p_transfer_to_organization => null
1485 , p_online_process_flag => TRUE
1486 , p_transaction_source_id => CSP_REPAIR_PO_SCRAP_rec.wip_id
1487 , p_trx_source_line_id => null
1488 , p_transaction_source_name => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id ||'REPAIR_PO_WIP_RETURN'
1489 , p_waybill_airbill => null
1490 , p_shipment_number => null
1491 , p_freight_code => null
1492 , p_reason_id => null
1493 , p_transaction_reference => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id
1494 , p_expected_delivery_date => null
1495 , p_FINAL_COMPLETION_FLAG => FINAL_COMPLETION_FLAG -- May need to add this parameter for wip return
1496 , x_return_status => l_return_status
1497 , x_msg_count => l_msg_count
1498 , x_msg_data => l_msg_data
1499 );
1500
1501 IF l_return_status <> FND_API.G_RET_STS_SUCCESS and l_msg_count <> 0 THEN
1502 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1503 End if;
1504
1505 /**
1506 After WIP_JOB qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1507 by doing material transaction of type "Return Components from WIP" (43),
1508
1509 Do "MISCELLANEOUS ISSUE": 32 transaction (or)
1510 SCRAP transaction (WIP assembly scrap: 90) (or)
1511 Adjustment transaction : ??????? from
1512 Repair Supplier Org's defective subinv for the scrap qty
1513 **/
1514 If L_SCRAP_ADJUST_FLAG = 'SCRAP' then
1515 l_transaction_type_id := 32; --90;
1516 elsif L_SCRAP_ADJUST_FLAG = 'ADJUSTMENT' then
1517 l_transaction_type_id := 32;
1518 End if;
1519
1520 csp_transactions_pub.transact_material
1521 ( p_api_version => 1.0
1522 , p_init_msg_list => FND_API.G_FALSE
1523 , p_commit => FND_API.G_FALSE
1524 , px_transaction_header_id => px_transaction_header_id
1525 , px_transaction_id => t_transaction_id
1526 , p_inventory_item_id => CSP_REPAIR_PO_SCRAP_rec.defect_item_id
1527 , p_organization_id => CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id
1528 , p_subinventory_code => l_defective_subinv
1529 , p_locator_id => null
1530 , p_lot_number => null
1531 , p_lot_expiration_date => NULL
1532 , p_revision => null
1533 , p_serial_number => null
1534 , p_to_serial_number => null
1535 , p_quantity => L_SCRAP_ADJUST_QTY
1536 , p_uom => l_primary_uom_code
1537 , p_source_id => null
1538 , p_source_line_id => null
1539 , p_transaction_type_id => l_transaction_type_id
1540 , p_account_id => null
1541 , p_transfer_to_subinventory => null
1542 , p_transfer_to_locator => null
1543 , p_transfer_to_organization => null
1544 , p_online_process_flag => TRUE
1545 , p_transaction_source_id => null
1546 , p_trx_source_line_id => null
1547 , p_transaction_source_name => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id ||'REP_PO_'||L_SCRAP_ADJUST_FLAG||'_MISC'
1548 , p_waybill_airbill => null
1549 , p_shipment_number => null
1550 , p_freight_code => null
1551 , p_reason_id => null
1552 , p_transaction_reference => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id
1553 , p_expected_delivery_date => null
1554 , x_return_status => l_return_status
1555 , x_msg_count => l_msg_count
1556 , x_msg_data => l_msg_data
1557 );
1558
1559 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1561 End if;
1562
1563 /** FINAL_COMPLETION_FLAG = 'Y' => Repair PO is Closed, WIP_JOB is Complete and ready to close
1564 FINAL_COMPLETION_FLAG = 'N' => Repair PO is not Closed, WIP_JOB is still open in released status_type
1565 **/
1566
1567 /**
1568 1.We can do this insert (only) if FINAL_COMPLETION_FLAG= 'Y'
1569 i.e CSP_REPAIR_PO_SCRAP_rec.received_qty = CSP_REPAIR_PO_HEADERS_rec.quantity - (L_SCRAP_ADJUST_QTY + l_total_scrap_adjust_qty)
1570 But This Insert may not be needed if FINAL_COMPLETION_FLAG is passed as 'Y' in the above call
1571
1572 2.If we do this insert then we could use the WIP api to update the WIP_JOB instead of using WIP_MASS_LOAD program form
1573 3.If possible try to update quantity_scrapped column with l_scrap_quanity value instead of updating QUANTITY_COMPLETED column.
1574 **/
1575 ---------------End comment on NOV-29-2005 --------
1576 /*
1577 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
1578 into l_WIP_BATCH_ID
1579 from dual;
1580
1581 Begin
1582 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
1583 (
1584 LAST_UPDATE_DATE,
1585 LAST_UPDATED_BY,
1586 CREATION_DATE,
1587 CREATED_BY,
1588 GROUP_ID,
1589 SOURCE_CODE,
1590 SOURCE_LINE_ID,
1591 PROCESS_PHASE,
1592 PROCESS_STATUS,
1593 ORGANIZATION_ID,
1594 LOAD_TYPE,
1595 PRIMARY_ITEM_ID,
1596 START_QUANTITY,
1597 STATUS_TYPE,
1598 --FIRST_UNIT_START_DATE,
1599 --FIRST_UNIT_COMPLETION_DATE,
1600 --LAST_UNIT_START_DATE,
1601 --LAST_UNIT_COMPLETION_DATE,
1602 CLASS_CODE,
1603 WIP_ENTITY_ID,
1604 JOB_NAME
1605 )
1606 SELECT
1607 SYSDATE,
1608 l_user_id,
1609 SYSDATE,
1610 l_user_id,
1611 l_WIP_BATCH_ID,
1612 SOURCE_CODE,
1613 SOURCE_LINE_ID,
1614 decode(FINAL_COMPLETION_FLAG,'Y',4,2), --> 2 Validation, 4 Completion
1615 decode(FINAL_COMPLETION_FLAG,'Y',4,1), --> 1 Pending, 4 Complete
1616 ORGANIZATION_ID,
1617 3, --> Load type: 4 Create non-standard wip job, 3 Update non-standard wip job
1618 PRIMARY_ITEM_ID,
1619 START_QUANTITY - L_SCRAP_ADJUST_QTY, --> reducing the WIP_JOB qty to (repair_po_qty - scrap or adjustment qty)
1620 decode(FINAL_COMPLETION_FLAG,'Y',4,3), --> Status type: 3 Released, 4 Complete
1621 --FIRST_UNIT_START_DATE,
1622 --FIRST_UNIT_COMPLETION_DATE,
1623 --LAST_UNIT_START_DATE,
1624 --LAST_UNIT_COMPLETION_DATE,
1625 CLASS_CODE,
1626 CSP_REPAIR_PO_SCRAP_rec.WIP_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
1627 'REPAIR_EXECUTION'||CSP_REPAIR_PO_SCRAP_rec.WIP_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
1628 FROM WIP_DISCRETE_JOBS
1629 WHERE WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
1630 Exception
1631 When others then
1632 /*
1633 FND_MESSAGE.SET_NAME ('CSP','CSP_INSERT_WIPJOB_ERROR');
1634 FND_MESSAGE.SET_TOKEN ('WIP_ID',CSP_REPAIR_PO_SCRAP_rec.WIP_ID ,TRUE);
1635 FND_MSG_PUB.ADD;
1636 RAISE EXCP_USER_DEFINED;
1637 */
1638 /*
1639 l_sqlcode := SQLCODE;
1640 l_sqlerrm := SQLERRM;
1641 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1642 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1643 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
1644 fnd_msg_pub.add;
1645 RAISE EXCP_USER_DEFINED;
1646 End;
1647 ---------------End comment on NOV-29-2005 --------
1648 */
1649
1650 ------------** Need to check whether this works or not **-------------
1651
1652 If L_SCRAP_ADJUST_FLAG = 'SCRAP' then
1653
1654 /** If possible update WIP_DISCRETE_JOBS directly for scrap transaction**/
1655 Update WIP_DISCRETE_JOBS
1656 set QUANTITY_SCRAPPED = nvl(QUANTITY_SCRAPPED,0) + L_SCRAP_ADJUST_QTY,
1657 LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
1658 DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
1659 Where WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
1660
1661 Elsif L_SCRAP_ADJUST_FLAG = 'ADJUSTMENT' then
1662
1663 /** If possible update WIP_DISCRETE_JOBS directly for adjustment transaction**/
1664 Update WIP_DISCRETE_JOBS
1665 set QUANTITY_SCRAPPED = nvl(QUANTITY_SCRAPPED,0) + L_SCRAP_ADJUST_QTY,
1666 --QUANTITY_COMPLETED = nvl(QUANTITY_COMPLETED,0) + L_SCRAP_ADJUST_QTY,
1667 LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
1668 DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
1669 Where WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
1670
1671 End if;
1672 -------------------------------------------------------------------------
1673
1674 /** Status 9 = Repair po is closed, 8 = Repair po is not closed and has a open WIP_JOB **/
1675 UPDATE CSP_REPAIR_PO_HEADERS
1676 SET STATUS = decode(FINAL_COMPLETION_FLAG,'Y',9,8)
1677 WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID;
1678
1679 If L_SCRAP_ADJUST_FLAG = 'SCRAP' then
1680
1681 UPDATE CSP_REPAIR_PO_LINES
1682 SET scrap_qty = nvl(scrap_qty,0) + L_SCRAP_ADJUST_QTY
1683 ,SCRAP_DATE = nvl(p_SCRAP_ADJUST_DATE,sysdate)
1684 WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
1685 AND inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
1686
1687 Elsif L_SCRAP_ADJUST_FLAG = 'ADJUSTMENT' then
1688
1689 UPDATE CSP_REPAIR_PO_LINES
1690 SET adjusted_qty = nvl(adjusted_qty,0) + L_SCRAP_ADJUST_QTY
1691 ,ADJUSTMENT_DATE = nvl(p_SCRAP_ADJUST_DATE,sysdate)
1692 WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
1693 AND inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
1694
1695 End if;
1696
1697 End if;
1698 End loop;
1699
1700 IF FND_API.to_Boolean(p_commit) THEN
1701 COMMIT WORK;
1702 END IF;
1703
1704 x_return_status := l_return_status;
1705
1706 /** Standard call to get message count and if count is 1, get message info */
1707 FND_MSG_PUB.Count_And_Get
1708 (p_count => x_msg_count,
1709 p_data => x_msg_data
1710 );
1711
1712 /**
1713 G_RET_STS_SUCCESS means that the API was successful in performing all the operation requested by its caller.
1714 G_RET_STS_ERROR means that the API failed to perform one or more of the operations requested by its caller.
1715 G_RET_STS_UNEXP_ERROR means that the API was not able to perform any of the operations requested by its callers because of an unexpected error.
1716
1717 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
1718 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := 'E';
1719 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := 'U';
1720 **/
1721
1722 EXCEPTION
1723 WHEN EXCP_USER_DEFINED THEN
1724 Rollback to REPAIR_PO_SCRAP_PVT;
1725
1726 /** This returns 'E' as status **/
1727 x_return_status := FND_API.G_RET_STS_ERROR;
1728
1729 fnd_msg_pub.count_and_get
1730 (p_count => x_msg_count
1731 ,p_data => x_msg_data);
1732
1733 WHEN FND_API.G_EXC_ERROR THEN
1734 /** This returns 'E' as status **/
1735
1736 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1737 P_API_NAME => L_API_NAME
1738 ,P_PKG_NAME => G_PKG_NAME
1739 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1740 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1741 ,P_ROLLBACK_FLAG => l_Rollback
1742 ,X_MSG_COUNT => X_MSG_COUNT
1743 ,X_MSG_DATA => X_MSG_DATA
1744 ,X_RETURN_STATUS => X_RETURN_STATUS);
1745
1746 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1747 /** This returns 'U' as status **/
1748
1749 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1750 P_API_NAME => L_API_NAME
1751 ,P_PKG_NAME => G_PKG_NAME
1752 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1753 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1754 ,P_ROLLBACK_FLAG => l_Rollback
1755 ,X_MSG_COUNT => X_MSG_COUNT
1756 ,X_MSG_DATA => X_MSG_DATA
1757 ,X_RETURN_STATUS => X_RETURN_STATUS);
1758
1759 WHEN OTHERS THEN
1760 Rollback to REPAIR_PO_SCRAP_PVT;
1761
1762 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1763 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1764 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1765 FND_MSG_PUB.ADD;
1766 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, l_api_name);
1767
1768 fnd_msg_pub.count_and_get
1769 (p_count => x_msg_count
1770 ,p_data => x_msg_data);
1771
1772 /** This returns 'U' as status **/
1773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1774
1775 END REP_PO_SCRAP_ADJUST_TRANSACT;
1776
1777 END CSP_REPAIR_PO_PVT;