[Home] [Help]
PACKAGE BODY: APPS.CSP_REPAIR_PO_PVT
Source
1 PACKAGE BODY CSP_REPAIR_PO_PVT AS
2 /* $Header: cspgrexb.pls 120.21.12020000.12 2013/04/10 22:58:34 vidsrini ship $ */
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,
96 PRL.QUANTITY_RECEIVED,
97 PRL.QUANTITY_DELIVERED,
98 PRL.ITEM_ID,
99 PRL.DESTINATION_ORGANIZATION_ID,
100 PRL.DESTINATION_SUBINVENTORY
101 FROM OE_ORDER_HEADERS_ALL OEH,
102 PO_REQUISITION_LINES_ALL PRL
103 WHERE OEH.HEADER_ID = L_HEADER_ID
104 AND OEH.SOURCE_DOCUMENT_ID = PRL.REQUISITION_HEADER_ID;
105
106 /*
107 1.PRL.quantity_received is null (and)
108 PRL.quantity_delivered shows the PO received qty.
109
110 2.Based on the above scenario, we have to use
111 PLL.quantity_received (or) PRL.quantity_delivered
112 to select quantity_received so far for this PO.
113 */
114
115 CURSOR PO_REQ_RECEIVED_QTY(l_requisition_line_id NUMBER) IS
116 SELECT PLL.quantity_received, PRL.closed_code,
117 POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, -- POH.closed_code,
118 PLL.line_location_id, PLL.po_line_id
119 FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
120 WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
121 PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
122 PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
123
124 CURSOR CSP_REPAIR_PO_SCRAP(L_REPAIR_PO_HEADER_ID NUMBER,
125 L_SCRAP_ITEM_ID NUMBER,
126 L_QUANTITY NUMBER
127 ) IS
128 SELECT CRPH.repair_po_header_id,
129 CRPH.wip_id,
130 CRPH.inventory_item_id,
131 CRPH.repair_supplier_org_id,
132 CRPH.quantity,
133 CRPH.received_qty,
134 CRPL.inventory_item_id defect_item_id,
135 CRPL.defective_organization_id,
136 CRPL.quantity defect_qty,
137 CRPL.received_qty defect_received_qty,
138 CRPL.SCRAP_QTY,
139 CRPL.ADJUSTED_QTY
140 FROM CSP_REPAIR_PO_HEADERS CRPH, CSP_REPAIR_PO_LINES CRPL
141 WHERE CRPH.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
142 AND CRPH.status = 8
143 AND CRPH.repair_po_header_id = CRPL.repair_po_header_id
144 AND CRPL.inventory_item_id = L_SCRAP_ITEM_ID;
145
146
147 l_api_version_number CONSTANT NUMBER := 1.0;
148 l_api_name CONSTANT VARCHAR2(30) := 'RUN_REPAIR_EXECUTION';
149
150 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
151 l_commit VARCHAR2(1) := FND_API.G_TRUE;
152 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
153
154 x_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
155 x_msg_count NUMBER;
156 x_msg_data VARCHAR2(2000);
157
158 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
159 l_msg_count NUMBER;
160 l_msg_data VARCHAR2(2000);
161
162 l_sqlcode NUMBER;
163 l_sqlerrm VARCHAR2(2000);
164 g_retcode NUMBER := 0;
165 l_Rollback VARCHAR2(1) := 'Y';
166
167 l_today DATE;
168 l_user_id NUMBER;
169 l_login_id NUMBER;
170
171 EXCP_USER_DEFINED EXCEPTION;
172
173 x_relieved_quantity NUMBER;
174 l_reservation_rec CSP_REPAIR_PO_PVT.out_reserve_rec_type;
175
176 x_item_number VARCHAR2(40);
177 x_item_description VARCHAR2(240);
178 l_primary_uom_code VARCHAR2(3);
179 l_org_name VARCHAR2(240);
180 l_sec_inv_name VARCHAR2(240);
181 L_ORGANIZATION_NAME VARCHAR2(240);
182
183 l_header_rec csp_parts_requirement.header_rec_type;
184 l_line_rec csp_parts_requirement.line_rec_type;
185 l_line_tbl csp_parts_requirement.line_Tbl_type;
186 l_dest_organization_id NUMBER;
187 I NUMBER;
188 l_ship_to_location_id NUMBER;
189
190 L_authorization_status VARCHAR2(240);
191 L_req_number_segment1 VARCHAR2(240);
192 l_need_by_date DATE;
193
194 L_CLASS_CODE VARCHAR2(240);
195 l_WIP_BATCH_ID NUMBER;
196 l_WIP_ENTITY_ID NUMBER;
197
198 px_transaction_header_id NUMBER;
199 t_transaction_id NUMBER;
200 l_RECEIVED_QTY NUMBER;
201 l_wib_issue_qty NUMBER;
202 FINAL_COMPLETION_FLAG VARCHAR2(1);
203 l_usable_subinv VARCHAR2(240);
204 l_defective_subinv VARCHAR2(240);
205 l_total_scrap_adjust_qty NUMBER;
206 l_org_id NUMBER;
207 l_wip_status_type NUMBER;
208 l_WIP_ENTITY_ID_INTERFACE NUMBER;
209
210 L_WIP_START_QUANTITY NUMBER;
211 L_WIP_QUANTITY_SCRAPPED NUMBER;
212 L_WIP_REMAIN_QTY NUMBER;
213 L_WIP_COMPLETE_QTY NUMBER;
214
215 l_po_line_changes po_lines_rec_type;
216 l_shipment_changes PO_SHIPMENTS_REC_TYPE;
217 l_distribution_changes PO_DISTRIBUTIONS_REC_TYPE;
218 l_changes PO_CHANGES_REC_TYPE;
219 l_po_release_id NUMBER;
220 l_po_header_id NUMBER;
221 lc_org_id NUMBER;
222 l_po_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
223 l_po_api_errors PO_API_ERRORS_REC_TYPE;
224
225 poLineIdTbl po_tbl_number := po_tbl_number();
226 l_qtys po_tbl_number := po_tbl_number();
227
228 /*
229 CURSOR PO_REQ_RECEIVED_QTY(l_requisition_line_id NUMBER) IS
230 SELECT PLL.quantity_received, PRL.closed_code,
231 POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, -- POH.closed_code,
232 PLL.line_location_id, PLL.po_line_id
233 FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
234 WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
235 PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
236 PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
237 */
238
239 L_quantity_received NUMBER;
240 L_closed_code VARCHAR2(25);
241 LC_po_header_id NUMBER;
242 L_PO_Quantity NUMBER;
243 L_line_location_id NUMBER;
244 L_po_line_id NUMBER;
245
246
247 BEGIN
248
249 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
250
251 /** Standard call to check for call compatibility **/
252 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
253 p_api_version_number,
254 l_api_name,
255 G_PKG_NAME)
256 THEN
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 END IF;
259
260 /** Initialize message list **/
261 IF fnd_api.to_boolean(l_Init_Msg_List) THEN
262 FND_MSG_PUB.initialize;
263 END IF;
264
265 /** Initialize return status **/
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267
268 /** User and login information **/
269 SELECT Sysdate INTO l_today FROM dual;
270 l_user_id := fnd_global.user_id;
271 l_login_id := fnd_global.login_id;
272
273 ---- Start Step:1 ----
274
275 /**
276 For all the Repair_po's with status '1'
277 i.e In PO_REQUISITIONS_INTERFACE_ALL table AUTHORIZATION_STATUS = 'INCOMPLETE' (or) 'IN PROCESS' (or) 'REJECTED' (or) other.
278 Check AUTHORIZATION_STATUS in PO_REQUISITIONS_INTERFACE_ALL is moved to 'APPROVED'
279 If that is true or record is moved to PO_REQUISITION_HEADERS_ALL then update the status = 2
280 else keep status = 1 as it may be still 'IN PROCESS' (or) 'REJECTED' (or) other status
281 **/
282
283 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(1)
284 LOOP
285 OPEN PO_REQ_INTERFACE_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_NUMBER, CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID);
286 LOOP
287 FETCH PO_REQ_INTERFACE_ALL INTO L_authorization_status, L_req_number_segment1;
288 IF (PO_REQ_INTERFACE_ALL%ROWCOUNT = 0) THEN
289 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 2
290 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
291
292 EXIT;
293 ELSIF PO_REQ_INTERFACE_ALL%FOUND and NVL(L_authorization_status,'APPROVED') = 'APPROVED' THEN
294 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 2
295 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
296
297 EXIT;
298 ELSE
299 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 1
300 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
301
302 EXIT;
303 END IF;
304 END LOOP;
305 CLOSE PO_REQ_INTERFACE_ALL;
306 END LOOP;
307
308 COMMIT;
309
310 ---- End Step:1 ----
311
312 ---- Start Step:2 ----
313
314 /**
315 For all the Repair_po whose status is '2'
316 i.e In PO_REQUISITION_HEADERS_ALL table AUTHORIZATION_STATUS = 'CREATED' or 'IN PROCESS' or other
317 check AUTHORIZATION_STATUS in PO_REQUISITION_HEADERS_ALL is moved to 'APPROVED'
318 If that is true update the status = 3
319 else keep status = 2 as it may be still in 'IN PROCESS' (or) 'REJECTED' (or) other status
320 **/
321 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
322
323 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(2)
324 LOOP
325 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)
326 LOOP
327 If PO_REQ_HEADERS_ALL_rec.AUTHORIZATION_STATUS = 'APPROVED' then
328 UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 3,REQUISITION_HEADER_ID = PO_REQ_HEADERS_ALL_rec.REQUISITION_HEADER_ID
329 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
330 End if;
331 END LOOP;
332 END LOOP;
333
334 COMMIT;
335
336 ---- End Step:2 ----
337
338 ---- Start Step:3 ----
339
340 /** For all the repair_po with status '3'
341 If record is created in PO_HEADERS_ALL table
342 and CLOSED_CODE (or) AUTHORIZATION_STATUS is not 'APPROVED' then update the status = 4.
343 Else if CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED' then update the status = 5.
344 **/
345
346 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
347
348 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(3)
349 LOOP
350 FOR PO_HEADERS_ALL_rec IN PO_HEADERS_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID)
351 LOOP
352 If NVL(PO_HEADERS_ALL_rec.CLOSED_CODE,'OPEN') = 'OPEN'
353 OR NVL(PO_HEADERS_ALL_rec.AUTHORIZATION_STATUS,'OPEN') = 'OPEN' then
354 UPDATE CSP_REPAIR_PO_HEADERS
355 SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
356 PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 4
357 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
358 Elsif NVL(PO_HEADERS_ALL_rec.CLOSED_CODE,'OPEN') = 'APPROVED'
359 OR NVL(PO_HEADERS_ALL_rec.AUTHORIZATION_STATUS,'OPEN') = 'APPROVED' then
360 UPDATE CSP_REPAIR_PO_HEADERS
361 SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
362 PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 5
363 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
364 End if;
365 END LOOP;
366 END LOOP;
367
368 COMMIT;
369
370 ---- End Step:3 ----
371
372 ---- Start Step:4 ----
373
374 /** For all the repair_po with status '4'
375 If record is created in PO_HEADERS_ALL table
376 and CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED' then update the status = 5.
377 **/
378
379 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
380
381 FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(4)
382 LOOP
383 FOR PO_HEADERS_ALL_rec IN PO_HEADERS_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID)
384 LOOP
385 If NVL(PO_HEADERS_ALL_rec.CLOSED_CODE,'OPEN') = 'APPROVED'
386 OR NVL(PO_HEADERS_ALL_rec.AUTHORIZATION_STATUS,'OPEN') = 'APPROVED' then
387 UPDATE CSP_REPAIR_PO_HEADERS
388 SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
389 PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 5
390 WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
391 End if;
392 END LOOP;
393 END LOOP;
394
395 COMMIT;
396
397 ---- End Step:4 ----
398
399 ---- Start Step:5 ----
400 /** For all the repair_po with status '5'
401 i.e Record is created in PO_HEADERS_ALL table and CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED'
402 If no internal order is created and REPAIR_PROGRAM <> 'PRE-POSITIONING' then
403 create an internal order and cancel the existing reservation.
404 **/
405
406 /*
407 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
408 SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
409 INTO l_org_id
410 FROM dual;
411
412 po_moac_utils_pvt.set_org_context(l_org_id);
413
414 */
415
416 MO_GLOBAL.init('CSF');
417
418 --SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
419
420 FOR CSP_REPAIR_PO_HEADERS_rec IN CSP_REPAIR_PO_HEADERS_ROW(5)
421 LOOP
422
423 SAVEPOINT RUN_REPAIR_EXECUTION_PVT; /* Create this save point if the commit not exits the loop */
424
425 If (CSP_REPAIR_PO_HEADERS_rec.STATUS = 5 AND
426 CSP_REPAIR_PO_HEADERS_rec.INTERNAL_ORDER_HEADER_ID IS NULL) Then
427 --AND CSP_REPAIR_PO_HEADERS_rec.REPAIR_PROGRAM ='3') Then -- 'Repair Return'
428
429 I := 1;
430
431 FOR CSP_RESERVED_LINES_rec IN CSP_RESERVED_LINES(CSP_REPAIR_PO_HEADERS_rec.REPAIR_PO_HEADER_ID)
432 LOOP
433
434 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
435 (CSP_RESERVED_LINES_rec.defective_organization_id
436 ,CSP_RESERVED_LINES_rec.inventory_item_id
437 ,x_item_number
438 ,x_item_description
439 ,l_primary_uom_code
440 ,x_return_status
441 ,x_msg_data
442 ,x_msg_count
443 );
444
445 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
446 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_RESERVED_LINES_rec.defective_organization_id);
447 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_DEFECTORG');
448 FND_MESSAGE.SET_TOKEN ('DEFECTIVE_ORG_NAME', L_ORG_NAME,TRUE);
449 FND_MSG_PUB.ADD;
450 Add_Err_Msg;
451 g_retcode := 1;
452 END IF;
453
454 l_reservation_rec.item_uom_code := l_primary_uom_code;
455
456 csp_sch_int_pvt.cancel_reservation(p_reserv_id => CSP_RESERVED_LINES_rec.reservation_id,
457 x_return_status => l_return_status,
458 x_msg_data => l_msg_data,
459 x_msg_count => l_msg_count);
460 /*
461 csp_sch_int_pvt.DELETE_RESERVATION(p_reservation_id => CSP_RESERVED_LINES_rec.reservation_id
462 ,x_return_status => l_return_status
463 ,x_msg_data => l_msg_data );
464 */
465
466 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
467 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
468 Add_Err_Msg;
469 g_retcode := 1;
470 errbuf := X_Msg_Data;
471 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
472
473 Elsif (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
474 l_line_rec.line_num := I; -- 1;
475 l_line_rec.inventory_item_id := CSP_RESERVED_LINES_rec.inventory_item_id;
476 --l_line_rec.item_description := 'Sentinel Standard Desktop';
477 l_line_rec.sourced_from := 'INVENTORY';
478 l_line_rec.ordered_quantity := CSP_RESERVED_LINES_rec.quantity;
479 l_line_rec.unit_of_measure := l_primary_uom_code;
480 l_line_rec.dest_subinventory := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_DEFECT_SUBINV');
481 --l_line_rec.dest_subinventory := 'FldSvc';
482 l_line_rec.source_organization_id := CSP_RESERVED_LINES_rec.defective_organization_id;
483 --l_line_Rec.order_line_id := 50762;
484 --l_line_rec.source_subinventory := 'Stores';
485 --l_line_rec.booked_flag := 'N'; --'Y'
486
487 l_line_tbl(I) := l_line_rec;
488 I := I+1;
489
490 End if;
491 END LOOP;
492
493 l_dest_organization_id := CSP_REPAIR_PO_HEADERS_rec.REPAIR_SUPPLIER_ORG_ID;
494 l_need_by_date := SYSDATE; /* CSP_REPAIR_PO_HEADERS_rec.need_by_date; */
495
496 /** 1.( Need_by_date of repair-to_item at dest org ) -
497 (Transit time between repair supplier org to dest org)
498 = Completion Date of repair-to_item at repair supplier org.
499 2. Completion Date - Repair_Lead_Time = Start date of the wip job
500 Here Internal order Need_by_date should be equal to Start date of the wip job.
501 **/
502
503 l_sec_inv_name := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_DEFECT_SUBINV');
504
505 Begin
506 SELECT LOCATION_ID
507 INTO l_ship_to_location_id
508 FROM MTL_SECONDARY_INVENTORIES
509 WHERE ORGANIZATION_ID = l_dest_organization_id
510 AND SECONDARY_INVENTORY_NAME = l_sec_inv_name; -- 'FldSvc'
511 Exception
512 when no_data_found then
513 l_ship_to_location_id := Null;
514 End;
515
516 If l_ship_to_location_id is null then
517 Begin
518 SELECT LOCATION_ID
519 INTO l_ship_to_location_id
520 FROM HR_ORGANIZATION_UNITS
521 WHERE ORGANIZATION_ID = l_dest_organization_id;
522 Exception
523 when no_data_found then
524 l_ship_to_location_id := Null;
525 End;
526 End if;
527
528 If l_ship_to_location_id is null then
529 L_ORGANIZATION_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(l_dest_organization_id);
530 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_SHIPTO_LOCATION_ID');
531 FND_MESSAGE.SET_TOKEN ('DESTINATION_ORG', L_ORGANIZATION_NAME, TRUE);
532 FND_MSG_PUB.ADD;
533 Add_Err_Msg;
534 g_retcode := 1;
535 End if;
536
537 --l_header_rec.description := 'Test Req';
538 --l_header_rec.order_type_id := 1430;
539 FND_PROFILE.GET('CSP_ORDER_TYPE', l_header_rec.order_type_id);
540 l_header_rec.dest_organization_id := l_dest_organization_id;
541 l_header_rec.operation := csp_parts_order.G_OPR_CREATE;
542 l_header_rec.ship_to_location_id := l_ship_to_location_id;
543 l_header_rec.requisition_number := NULL;
544 l_header_rec.order_header_id := NULL;
545 l_header_rec.requisition_header_id := NULL;
546 l_header_rec.need_by_date := l_need_by_Date;
547
548 csp_parts_order.process_order
549 (
550 p_api_version => 1.0
551 ,p_Init_Msg_List => FND_API.G_FALSE
552 ,p_commit => FND_API.G_FALSE
553 ,px_header_rec => l_header_rec
554 ,px_line_table => l_line_tbl
555 --,p_process_type => 'BOTH'(Default value is 'BOTH')
556 ,x_return_status => x_return_status
557 ,x_msg_count => x_msg_count
558 ,x_msg_data => x_msg_data
559 );
560
561 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
562 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
563 Add_Err_Msg;
564 g_retcode := 1;
565 errbuf := X_Msg_Data;
566 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
567 Elsif (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
568 /* STATUS = 6 => INTERNAL_ORDER CREATED */
569
570 UPDATE CSP_REPAIR_PO_HEADERS
571 SET INTERNAL_ORDER_HEADER_ID = l_header_rec.order_header_id,
572 STATUS = 6
573 -- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
574 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
575
576 COMMIT; /* Do this commit if it is not exits the loop */
577
578 End if;
579
580 End if;
581 END LOOP;
582
583 --COMMIT;
584
585 ---- End Step:5 ----
586
587 ---- Start Step:6 ----
588 /** Loop through each IO Lines for the Internal_order created and then check QTY_RECEIVED > 0
589 Check for existing WIP_JOB in WIP_ENTITIES that wip job is loaded through wip mass load or through API
590 if WIP_JOB NOT created already then insert to interface table and do wip issue transaction
591 elseif WIP_JOB created already and it is there in WIP_ENTITIES table then do wip issue transaction
592 **/
593
594 --SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
595
596 For I in 6..8 Loop
597
598 FOR CSP_REPAIR_PO_HEADERS_rec IN CSP_REPAIR_PO_HEADERS_ROW(I)
599 LOOP
600 /**Loop through each IO Lines for the Internal_order created and
601 then check QTY_RECEIVED > 0
602 **/
603
604 FOR IO_QTY_RECEIVED_CHECK_REC IN IO_QTY_RECEIVED_CHECK(CSP_REPAIR_PO_HEADERS_rec.INTERNAL_ORDER_HEADER_ID)
605 LOOP
606
607 SAVEPOINT RUN_REPAIR_EXECUTION_PVT; /* Create this save point if the commit not exits the loop */
608
609 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
610 (IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
611 ,IO_QTY_RECEIVED_CHECK_REC.item_id
612 ,x_item_number
613 ,x_item_description
614 ,l_primary_uom_code
615 ,x_return_status
616 ,x_msg_data
617 ,x_msg_count
618 );
619
620 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
621 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id);
622 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_REPAIRORG');
623 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_ORG_NAME,TRUE);
624 FND_MSG_PUB.ADD;
625 Add_Err_Msg;
626 g_retcode := 1;
627 END IF;
628
629 IF ((NVL (IO_QTY_RECEIVED_CHECK_REC.QUANTITY_DELIVERED, 0) > 0
630 OR NVL (IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED, 0) > 0) and CSP_REPAIR_PO_HEADERS_rec.status = 6)
631 THEN
632 SELECT PLL.quantity_received,
633 PRL.closed_code,
634 POH.po_header_id,
635 POL.Quantity,
636 PLL.line_location_id,
637 PLL.po_line_id,
638 poh.org_id
639 INTO L_quantity_received,
640 L_closed_code,
641 LC_po_header_id,
642 L_PO_Quantity,
643 L_line_location_id,
644 L_po_line_id,
645 lc_org_id
646 FROM PO_REQUISITION_LINES_ALL PRL,
647 PO_LINE_LOCATIONS_ALL PLL,
648 PO_LINES_ALL POL,
649 PO_HEADERS_ALL POH
650 WHERE PRL.REQUISITION_LINE_ID =
651 CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID
652 AND PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
653 AND PLL.PO_LINE_ID = POL.PO_LINE_ID
654 AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
655
656 mo_global.init ('PO');
657 mo_global.set_policy_context ('S', lc_org_id);
658
659 l_po_header_id :=
660 CSP_REPAIR_PO_HEADERS_rec.PURCHASE_ORDER_HEADER_ID;
661 l_po_release_id := NULL;
662
663 poLineIdTbl.EXTEND;
664 poLineIdTbl (1) := L_po_line_id; --Plug in the appropriate PO Line Id here
665
666 l_qtys.EXTEND;
667 l_qtys (1) := NVL ( IO_QTY_RECEIVED_CHECK_REC.QUANTITY_DELIVERED,NVL (IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED, 0)); --Plug in the new Quantity that is needed in the PO
668
669 --Create the Line Change Object passing in the Line ID and the New Quantity
670 l_po_line_changes :=
671 po_lines_rec_type.create_object (
672 p_po_line_id => poLineIdTbl,
673 p_quantity => l_qtys
674 );
675
676 --Shipment Change - Need not do anything here as the change in Shipment Quantity
677 --will be automatically taken care of by the update_document API
678 l_shipment_changes :=
679 PO_SHIPMENTS_REC_TYPE.create_object (
680 p_po_line_location_id => NULL
681 );
682
683 --Distribution Change - Need not do anything here as the change in Distribution
684 --will be automatically taken care of by the update_document API
685 l_distribution_changes :=
686 PO_DISTRIBUTIONS_REC_TYPE.create_object (
687 p_po_distribution_id => NULL
688 );
689
690 --Change Object containing all the changes needed to perform on the PO
691 l_changes :=
692 PO_CHANGES_REC_TYPE.create_object (
693 p_po_header_id => l_po_header_id,
694 p_po_release_id => l_po_release_id,
695 p_line_changes => l_po_line_changes,
696 p_shipment_changes => l_shipment_changes,
697 p_distribution_changes => l_distribution_changes
698 );
699
700 --Call to this API will change the Quantity in the PO
701 PO_DOCUMENT_UPDATE_GRP.update_document (
702 p_api_version => 1.0,
703 p_init_msg_list => FND_API.G_TRUE,
704 x_return_status => l_po_return_status,
705 p_changes => l_changes,
706 p_run_submission_checks => FND_API.G_TRUE,
707 p_launch_approvals_flag => FND_API.G_TRUE,
708 p_buyer_id => NULL,
709 p_update_source => NULL,
710 p_override_date => NULL,
711 x_api_errors => l_po_api_errors
712 );
713
714 IF l_po_return_status <> fnd_api.g_ret_sts_success
715 THEN
716
717 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
718 /*
719 FOR i IN 1..l_po_api_errors.message_text.COUNT LOOP
720 insert into PO_UPDATE_ERROR_TABLE(l_po_api_errors.message_text(i));
721 END LOOP;
722 */
723
724 Add_Err_Msg;
725 g_retcode := 1;
726 errbuf := l_Msg_Data;
727 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
728 END IF;
729 END IF;
730
731 If nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) > 0 and
732 CSP_REPAIR_PO_HEADERS_rec.WIP_ID IS NULL THEN
733 /** Create a WIB_JOB if there is no WIB_JOB created for this INTERNAL_ORDER so far **/
734
735 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
736 into l_WIP_BATCH_ID
737 from dual;
738
739 SELECT WIP_ENTITIES_S.NEXTVAL
740 INTO l_WIP_ENTITY_ID
741 FROM DUAL;
742
743 Begin
744 SELECT CLASS_CODE
745 INTO L_CLASS_CODE
746 FROM WIP_NON_STANDARD_CLASSES_VAL_V
747 WHERE ORGANIZATION_ID = CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
748 AND CLASS_TYPE = 4
749 AND CLASS_CODE = 'Expense';
750 Exception
751 WHEN NO_DATA_FOUND THEN
752 L_CLASS_CODE := NULL;
753 End;
754
755 If L_CLASS_CODE is null then
756 L_ORGANIZATION_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id);
757 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_WIP_CLASS_CODE');
758 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG', L_ORGANIZATION_NAME, TRUE);
759 FND_MSG_PUB.ADD;
760 Add_Err_Msg;
761 g_retcode := 1;
762 End if;
763
764 Begin
765 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
766 LAST_UPDATE_DATE,
767 LAST_UPDATED_BY,
768 CREATION_DATE,
769 CREATED_BY,
770 GROUP_ID,
771 SOURCE_CODE,
772 SOURCE_LINE_ID,
773 PROCESS_PHASE,
774 PROCESS_STATUS,
775 ORGANIZATION_ID,
776 LOAD_TYPE,
777 PRIMARY_ITEM_ID,
778 START_QUANTITY,
779 STATUS_TYPE,
780 FIRST_UNIT_START_DATE,
781 FIRST_UNIT_COMPLETION_DATE,
782 LAST_UNIT_START_DATE,
783 LAST_UNIT_COMPLETION_DATE,
784 CLASS_CODE,
785 WIP_ENTITY_ID,
786 JOB_NAME
787 --FIRM_PLANNED_FLAG
788 )
789 VALUES(
790 sysdate,
791 l_user_id,
792 sysdate,
793 l_user_id,
794 l_WIP_BATCH_ID,
795 'CSP',
796 CSP_REPAIR_PO_HEADERS_rec.INTERNAL_ORDER_HEADER_ID, --> (or) Pass CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
797 2, --> 2 Validation, 4 Completion
798 1, --> 1 Pending, 4 Complete
799 CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id,
800 4, --> LOAD_TYPE: 4 Non-standard discrete jobs, 3 update discrete jobs, 1 standard discrete jobs
801 CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID,
802 nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0),--CSP_REPAIR_PO_HEADERS_rec.QUANTITY,
803 3, --> Status type: 3 Released, 4 Complete, 12 closed
804 SYSDATE, --> FIRST_UNIT_START_DATE
805 CSP_REPAIR_PO_HEADERS_rec.NEED_BY_DATE, --> FIRST_UNIT_COMPLETION_DATE,
806 SYSDATE, --> LAST_UNIT_START_DATE
807 CSP_REPAIR_PO_HEADERS_rec.NEED_BY_DATE, --> LAST_UNIT_COMPLETION_DATE
808 L_CLASS_CODE, --> 'Expense'
809 l_WIP_ENTITY_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
810 'REPAIR_EXECUTION'||l_WIP_ENTITY_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
811 --1
812 );
813 Exception
814 When others then
815 l_sqlcode := SQLCODE;
816 l_sqlerrm := SQLERRM;
817 g_retcode := 1;
818 errbuf := SQLERRM;
819 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
820 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
821 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
822 fnd_msg_pub.add;
823 Add_Err_Msg;
824 Rollback to RUN_REPAIR_EXECUTION_PVT;
825 End;
826
827 l_WIP_ENTITY_ID_INTERFACE := l_WIP_ENTITY_ID;
828
829 /**
830 We could use the WIP api to create the WIP_JOB
831 instead of using WIP_MASS_LOAD program through form
832 **/
833
834 /** Check if WIP_JOB is created by WIP MASS LOAD PROGRAM **/
835
836 Begin
837 Select wip_entity_id
838 into l_wip_entity_id
839 from WIP_ENTITIES
840 Where wip_entity_id = l_WIP_ENTITY_ID;
841 --and wip_entity_name = 'REPAIR_EXECUTION'||l_WIP_ENTITY_ID;
842 Exception
843 when no_data_found then
844 l_wip_entity_id := Null;
845 End;
846
847 If l_wip_entity_id is not null then
848
849 /** Create Wip component issue transaction to the wip job **/
850
851 csp_transactions_pub.transact_material
852 ( p_api_version => 1.0
853 , p_init_msg_list => FND_API.G_FALSE
854 , p_commit => FND_API.G_FALSE
855 , px_transaction_header_id => px_transaction_header_id
856 , px_transaction_id => t_transaction_id
857 , p_inventory_item_id => IO_QTY_RECEIVED_CHECK_REC.item_id
858 , p_organization_id => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
859 , p_subinventory_code => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_SUBINVENTORY
860 , p_locator_id => null
861 , p_lot_number => null
862 , p_lot_expiration_date => NULL
863 , p_revision => null
864 , p_serial_number => null
865 , p_to_serial_number => null
866 , p_quantity => IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
867 , p_uom => l_primary_uom_code
868 , p_source_id => null
869 , p_source_line_id => null
870 , p_transaction_type_id => 35
871 , p_account_id => null
872 , p_transfer_to_subinventory => null
873 , p_transfer_to_locator => null
874 , p_transfer_to_organization => null
875 , p_online_process_flag => TRUE
876 , p_transaction_source_id => l_WIP_ENTITY_ID
877 , p_trx_source_line_id => null
878 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_WIP_ISSUE'
879 , p_waybill_airbill => null
880 , p_shipment_number => null
881 , p_freight_code => null
882 , p_reason_id => null
883 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
884 , p_expected_delivery_date => null
885 , x_return_status => l_return_status
886 , x_msg_count => l_msg_count
887 , x_msg_data => l_msg_data
888 );
889
890 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
891 -- Raise FND_API.G_EXC_UNEXPECTED_ERROR;
892 Add_Err_Msg;
893 g_retcode := 1;
894 errbuf := l_Msg_Data;
895 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
896 End if;
897
898 /* STATUS = 8 => 'WIP_JOB_CREATED' by WIP MASS LOAD PROGRAM */
899 UPDATE CSP_REPAIR_PO_HEADERS
900 SET WIP_ID = l_WIP_ENTITY_ID, STATUS = 8
901 -- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
902 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
903
904 UPDATE WIP_REQUIREMENT_OPERATIONS
905 SET QUANTITY_PER_ASSEMBLY = 1
906 WHERE INVENTORY_ITEM_ID = IO_QTY_RECEIVED_CHECK_REC.item_id
907 AND ORGANIZATION_ID = IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
908 AND WIP_ENTITY_ID = l_WIP_ENTITY_ID ;
909
910 Else
911 /* STATUS = 7 => Inserted into 'WIP_JOB_SCHEDULE_INTERFACE' */
912 UPDATE CSP_REPAIR_PO_HEADERS
913 SET WIP_ID = l_WIP_ENTITY_ID_INTERFACE, STATUS = 7
914 -- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
915 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
916 End if;
917
918 UPDATE CSP_REPAIR_PO_LINES
919 SET RECEIVED_QTY = IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
920 where repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
921 and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
922
923 COMMIT; /* Do this commit if it is not exits the loop */
924
925 /** Elseif WIB_JOB already created **/
926 Elsif CSP_REPAIR_PO_HEADERS_rec.WIP_ID IS NOT NULL THEN
927 --> Check if more parts are received by the following condition
928
929 Begin
930 SELECT RECEIVED_QTY
931 INTO l_RECEIVED_QTY
932 FROM CSP_REPAIR_PO_LINES
933 WHERE repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
934 and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
935 Exception
936 when no_data_found then
937 l_RECEIVED_QTY := Null;
938 End;
939
940 Begin
941 Select wip_entity_id
942 into l_wip_entity_id
943 from WIP_ENTITIES
944 Where wip_entity_id = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
945 --and wip_entity_name = 'REPAIR_EXECUTION'||l_WIP_ENTITY_ID;
946 Exception
947 when no_data_found then
948 l_wip_entity_id := Null;
949 End;
950
951 /*** Added for bug 12621761 ***/
952 If nvl(l_RECEIVED_QTY,0) <= nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0)
953 and CSP_REPAIR_PO_HEADERS_rec.STATUS = 8
954 and l_wip_entity_id is not null then
955
956 Update WIP_DISCRETE_JOBS
957 set START_QUANTITY = IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,
958 LAST_UPDATE_DATE = SYSDATE
959 Where WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
960 End if;
961 /*** End for bug 12621761 ***/
962
963 /** 1.Check if
964 (Parts qty issued to WIP_JOB before <
965 Current received qty in the "PO_REQUISITION_LINES_ALL" table for this internal order line)
966 2.Check if WIP_JOB is created by WIP MASS LOAD PROGRAM
967 */
968
969 If ( nvl(l_RECEIVED_QTY,0) < nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0)
970 or
971 (nvl(l_RECEIVED_QTY,0) <= nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0)
972 and CSP_REPAIR_PO_HEADERS_rec.STATUS = 7)
973 )
974 and l_wip_entity_id is not null then
975
976 /** Create Wip component issue transaction to the job
977 and issue parts qty as
978 (current received qty for this part in PO_REQ_LINES -
979 Qty received for this part before in CSP_REPAIR_PO_LINES)
980 to the existing WIP JOB by Calling CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL
981 */
982
983 l_wib_issue_qty := 0;
984
985 If CSP_REPAIR_PO_HEADERS_rec.STATUS = 7 then
986 If nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) > nvl(l_RECEIVED_QTY,0) then
987 l_wib_issue_qty := nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0);
988 Elsif nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) = nvl(l_RECEIVED_QTY,0) then
989 l_wib_issue_qty := nvl(l_RECEIVED_QTY,0);
990
991 End if;
992 Else
993 l_wib_issue_qty := nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) - nvl(l_RECEIVED_QTY,0);
994 End if;
995
996 If nvl(l_wib_issue_qty,0) > 0 then
997
998 csp_transactions_pub.transact_material
999 ( p_api_version => 1.0
1000 , p_init_msg_list => FND_API.G_FALSE
1001 , p_commit => FND_API.G_FALSE
1002 , px_transaction_header_id => px_transaction_header_id
1003 , px_transaction_id => t_transaction_id
1004 , p_inventory_item_id => IO_QTY_RECEIVED_CHECK_REC.item_id
1005 , p_organization_id => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
1006 , p_subinventory_code => IO_QTY_RECEIVED_CHECK_REC.DESTINATION_SUBINVENTORY
1007 , p_locator_id => null
1008 , p_lot_number => null
1009 , p_lot_expiration_date => NULL
1010 , p_revision => null
1011 , p_serial_number => null
1012 , p_to_serial_number => null
1013 , p_quantity => l_wib_issue_qty
1014 , p_uom => l_primary_uom_code
1015 , p_source_id => null
1016 , p_source_line_id => null
1017 , p_transaction_type_id => 35
1018 , p_account_id => null
1019 , p_transfer_to_subinventory => null
1020 , p_transfer_to_locator => null
1021 , p_transfer_to_organization => null
1022 , p_online_process_flag => TRUE
1023 , p_transaction_source_id => l_WIP_ENTITY_ID
1024 , p_trx_source_line_id => null
1025 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_WIP_ISSUE'
1026 , p_waybill_airbill => null
1027 , p_shipment_number => null
1028 , p_freight_code => null
1029 , p_reason_id => null
1030 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
1031 , p_expected_delivery_date => null
1032 , x_return_status => l_return_status
1033 , x_msg_count => l_msg_count
1034 , x_msg_data => l_msg_data
1035 );
1036
1037 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1038 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 Add_Err_Msg;
1040 g_retcode := 1;
1041 errbuf := l_Msg_Data;
1042 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
1043 End if;
1044
1045 UPDATE WIP_REQUIREMENT_OPERATIONS
1046 SET QUANTITY_PER_ASSEMBLY = 1
1047 WHERE INVENTORY_ITEM_ID = IO_QTY_RECEIVED_CHECK_REC.item_id
1048 AND ORGANIZATION_ID = IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
1049 AND WIP_ENTITY_ID = l_WIP_ENTITY_ID ;
1050
1051 End if;
1052
1053 If CSP_REPAIR_PO_HEADERS_rec.STATUS = 7 THEN
1054 If nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) > nvl(l_RECEIVED_QTY,0) then
1055 l_wib_issue_qty := nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) - nvl(l_RECEIVED_QTY,0);
1056 Elsif nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) = nvl(l_RECEIVED_QTY,0) then
1057 l_wib_issue_qty := 0;
1058 End if;
1059
1060 UPDATE CSP_REPAIR_PO_HEADERS
1061 SET STATUS = 8
1062 --WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
1063 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
1064 End if;
1065
1066 UPDATE CSP_REPAIR_PO_LINES
1067 SET RECEIVED_QTY = NVL(RECEIVED_QTY,0) + nvl(l_wib_issue_qty,0)
1068 --SET RECEIVED_QTY = NVL(RECEIVED_QTY,0) + IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
1069 where repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
1070 and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
1071
1072 COMMIT; /* Do this commit if it is not exits the loop */
1073
1074 End if;
1075 END IF;
1076
1077 /*
1078 If nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_DELIVERED,0) > 0
1079 OR
1080 nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0) > 0 THEN
1081
1082 SELECT PLL.quantity_received, PRL.closed_code, POH.po_header_id, PRL.Quantity, PLL.line_location_id, PLL.po_line_id,poh.org_id
1083 into L_quantity_received, L_closed_code, LC_po_header_id, L_PO_Quantity, L_line_location_id, L_po_line_id,lc_org_id
1084 FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
1085 WHERE PRL.REQUISITION_LINE_ID = CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID AND
1086 PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1087 PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
1088
1089 mo_global.init('PO');
1090 mo_global.set_policy_context('S',lc_org_id);
1091
1092 l_po_header_id := CSP_REPAIR_PO_HEADERS_rec.PURCHASE_ORDER_HEADER_ID;
1093 l_po_release_id := NULL;
1094
1095 poLineIdTbl.extend;
1096 poLineIdTbl(1) := L_po_line_id; --Plug in the appropriate PO Line Id here
1097
1098 l_qtys.extend;
1099 l_qtys(1) := nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_DELIVERED,nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0)); --Plug in the new Quantity that is needed in the PO
1100
1101 --Create the Line Change Object passing in the Line ID and the New Quantity
1102 l_po_line_changes := po_lines_rec_type.create_object(
1103 p_po_line_id => poLineIdTbl,
1104 p_quantity => l_qtys
1105 );
1106
1107 --Shipment Change - Need not do anything here as the change in Shipment Quantity
1108 --will be automatically taken care of by the update_document API
1109 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
1110 p_po_line_location_id => null);
1111
1112 --Distribution Change - Need not do anything here as the change in Distribution
1113 --will be automatically taken care of by the update_document API
1114 l_distribution_changes := PO_DISTRIBUTIONS_REC_TYPE.create_object (
1115 p_po_distribution_id => null
1116 );
1117
1118 --Change Object containing all the changes needed to perform on the PO
1119 l_changes := PO_CHANGES_REC_TYPE.create_object (p_po_header_id => l_po_header_id,
1120 p_po_release_id => l_po_release_id,
1121 p_line_changes => l_po_line_changes,
1122 p_shipment_changes => l_shipment_changes,
1123 p_distribution_changes => l_distribution_changes
1124 );
1125
1126 --Call to this API will change the Quantity in the PO
1127 PO_DOCUMENT_UPDATE_GRP.update_document (p_api_version => 1.0,
1128 p_init_msg_list => FND_API.G_TRUE,
1129 x_return_status => l_po_return_status,
1130 p_changes => l_changes,
1131 p_run_submission_checks => FND_API.G_TRUE,
1132 p_launch_approvals_flag => FND_API.G_TRUE,
1133 p_buyer_id => NULL,
1134 p_update_source => NULL,
1135 p_override_date => NULL,
1136 x_api_errors => l_po_api_errors
1137 );
1138
1139 If l_po_return_status <> fnd_api.g_ret_sts_success then
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1141 End if;
1142
1143 End if;
1144 */
1145 END LOOP;
1146 END LOOP;
1147
1148 END LOOP;
1149
1150 -- COMMIT;
1151 ---- End Step:6 ----
1152
1153
1154 ---- Start Step:7 ----
1155 /** We need to check the PO REQ received qty with the remaining job qty
1156 IF it is equal then pass 'Y' else pass 'N' for final_completion_flag and do WIP Assembly Completion transaction
1157 (WIP Assembly Completion: Tansaction_type_id: 44)
1158
1159 IF the value is 'Y' then it should automatically changes WIP_JOB to "Complete" state STATUS_TYPE 4.
1160 IF the value is 'N' then WIP_JOB should be in the same "Released" state STATUS_TYPE 3.
1161
1162 At the end do the Miscellaneous Issue transaction (Miscellaneous Issue: Tansaction_type_id: 32)
1163 **/
1164
1165 --SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
1166
1167 FOR CSP_REPAIR_PO_HEADERS_rec IN CSP_REPAIR_PO_HEADERS_ROW(8)
1168 LOOP
1169
1170 FOR PO_REQ_RECEIVED_QTY_rec IN PO_REQ_RECEIVED_QTY(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID)
1171 LOOP
1172
1173 SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
1174
1175 If (NVL(PO_REQ_RECEIVED_QTY_rec.CLOSED_CODE,'OPEN') = 'APPROVED'
1176 OR NVL(PO_REQ_RECEIVED_QTY_rec.AUTHORIZATION_STATUS,'OPEN') = 'APPROVED')
1177 AND nvl(PO_REQ_RECEIVED_QTY_rec.quantity_received,0) > 0 then
1178
1179 /*
1180 Select sum(nvl(scrap_qty,0) + nvl(adjusted_qty,0))
1181 into l_total_scrap_adjust_qty
1182 from CSP_REPAIR_PO_LINES
1183 where REPAIR_PO_HEADER_ID = CSP_REPAIR_PO_HEADERS_rec.REPAIR_PO_HEADER_ID
1184 group by REPAIR_PO_HEADER_ID;
1185 */
1186
1187 SELECT START_QUANTITY,QUANTITY_SCRAPPED
1188 INTO L_WIP_START_QUANTITY, L_WIP_QUANTITY_SCRAPPED
1189 FROM WIP_DISCRETE_JOBS
1190 WHERE CSP_REPAIR_PO_HEADERS_REC.WIP_ID = WIP_ENTITY_ID;
1191
1192 L_WIP_REMAIN_QTY := NVL(L_WIP_START_QUANTITY,0) - (NVL(CSP_REPAIR_PO_HEADERS_REC.RECEIVED_QTY,0) + NVL(L_WIP_QUANTITY_SCRAPPED,0));
1193
1194 l_usable_subinv := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_USABLE_SUBINV');
1195
1196 IF ( L_WIP_REMAIN_QTY - (NVL(PO_REQ_RECEIVED_QTY_REC.QUANTITY_RECEIVED,0) - NVL(CSP_REPAIR_PO_HEADERS_REC.RECEIVED_QTY,0)) <= 0 )
1197 OR
1198 ( CSP_REPAIR_PO_HEADERS_rec.quantity - (NVL(PO_REQ_RECEIVED_QTY_REC.QUANTITY_RECEIVED,0) + NVL(L_WIP_QUANTITY_SCRAPPED,0)) <= 0 )
1199 THEN
1200 -- PO_REQ_RECEIVED_QTY_rec.quantity_received >= CSP_REPAIR_PO_HEADERS_rec.quantity - l_total_scrap_adjust_qty then
1201 -- nvl(CSP_REPAIR_PO_HEADERS_rec.received_qty,0) = PO_REQ_RECEIVED_QTY_rec.quantity_received
1202
1203 FINAL_COMPLETION_FLAG := 'Y';
1204 l_wip_status_type := 4;
1205 Else
1206 FINAL_COMPLETION_FLAG := 'N';
1207 End if;
1208
1209 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
1210 (CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
1211 ,CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID
1212 ,x_item_number
1213 ,x_item_description
1214 ,l_primary_uom_code
1215 ,x_return_status
1216 ,x_msg_data
1217 ,x_msg_count
1218 );
1219
1220 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1221 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id);
1222 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIRITEM_AT_REPAIRORG');
1223 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_ORG_NAME,TRUE);
1224 FND_MSG_PUB.ADD;
1225 Add_Err_Msg;
1226 g_retcode := 1;
1227 END IF;
1228
1229 IF (L_WIP_REMAIN_QTY - (NVL(PO_REQ_RECEIVED_QTY_REC.QUANTITY_RECEIVED,0) - NVL(CSP_REPAIR_PO_HEADERS_REC.RECEIVED_QTY,0)) ) > 0 THEN
1230 L_WIP_COMPLETE_QTY := NVL(PO_REQ_RECEIVED_QTY_REC.QUANTITY_RECEIVED,0) - NVL(CSP_REPAIR_PO_HEADERS_REC.RECEIVED_QTY,0);
1231
1232 ELSIF (L_WIP_REMAIN_QTY - (NVL(PO_REQ_RECEIVED_QTY_REC.QUANTITY_RECEIVED,0) - NVL(CSP_REPAIR_PO_HEADERS_REC.RECEIVED_QTY,0)) ) <= 0 THEN
1233 L_WIP_COMPLETE_QTY := L_WIP_REMAIN_QTY;
1234
1235 END IF;
1236
1237 csp_transactions_pub.transact_material
1238 ( p_api_version => 1.0
1239 , p_init_msg_list => FND_API.G_FALSE
1240 , p_commit => FND_API.G_FALSE
1241 , px_transaction_header_id => px_transaction_header_id
1242 , px_transaction_id => t_transaction_id
1243 , p_inventory_item_id => CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID
1244 , p_organization_id => CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
1245 , p_subinventory_code => l_usable_subinv
1246 , p_locator_id => null
1247 , p_lot_number => null
1248 , p_lot_expiration_date => NULL
1249 , p_revision => null
1250 , p_serial_number => null
1251 , p_to_serial_number => NULL
1252 , p_quantity => L_WIP_COMPLETE_QTY
1253 , p_uom => l_primary_uom_code
1254 , p_source_id => null
1255 , p_source_line_id => null
1256 , p_transaction_type_id => 44
1257 , p_account_id => null
1258 , p_transfer_to_subinventory => null
1259 , p_transfer_to_locator => null
1260 , p_transfer_to_organization => null
1261 , p_online_process_flag => TRUE
1262 , p_transaction_source_id => CSP_REPAIR_PO_HEADERS_rec.wip_id
1263 , p_trx_source_line_id => null
1264 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_WIP_COMPLETE'
1265 , p_waybill_airbill => NULL
1266 , p_shipment_number => NULL
1267 , p_freight_code => NULL
1268 , p_reason_id => NULL
1269 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
1270 , p_expected_delivery_date => NULL
1271 , p_FINAL_COMPLETION_FLAG => 'N' --FINAL_COMPLETION_FLAG -- May need to add this parameter for wip complete
1272 , x_return_status => l_return_status
1273 , x_msg_count => l_msg_count
1274 , x_msg_data => l_msg_data
1275 );
1276
1277 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1278 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279 Add_Err_Msg;
1280 g_retcode := 1;
1281 errbuf := l_msg_Data;
1282 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
1283 End if;
1284
1285 /** 1.We can do this insert (only) if FINAL_COMPLETION_FLAG = 'Y'
1286 i.e PO_REQ_RECEIVED_QTY_rec.quantity_received = CSP_REPAIR_PO_HEADERS_rec.quantity - l_total_scrap_adjust_qty
1287 But This Insert may not be needed if FINAL_COMPLETION_FLAG is passed as 'Y' in the above call
1288
1289 2.If we do this insert then we could use the WIP api to update the WIP_JOB
1290 instead of using WIP_MASS_LOAD program form
1291 **/
1292
1293 --------------------- Start comment on Nov-29-2005 -----------------
1294 /*
1295
1296 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
1297 into l_WIP_BATCH_ID
1298 from dual;
1299
1300 Begin
1301 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
1302 LAST_UPDATE_DATE,
1303 LAST_UPDATED_BY,
1304 CREATION_DATE,
1305 CREATED_BY,
1306 GROUP_ID,
1307 SOURCE_CODE,
1308 SOURCE_LINE_ID,
1309 PROCESS_PHASE,
1310 PROCESS_STATUS,
1311 ORGANIZATION_ID,
1312 LOAD_TYPE,
1313 PRIMARY_ITEM_ID,
1314 START_QUANTITY,
1315 STATUS_TYPE,
1316 --FIRST_UNIT_START_DATE,
1317 --FIRST_UNIT_COMPLETION_DATE,
1318 --LAST_UNIT_START_DATE,
1319 --LAST_UNIT_COMPLETION_DATE,
1320 CLASS_CODE,
1321 WIP_ENTITY_ID,
1322 JOB_NAME
1323 )
1324 Select
1325 SYSDATE,
1326 l_user_id,
1327 SYSDATE,
1328 l_user_id,
1329 l_WIP_BATCH_ID,
1330 SOURCE_CODE,
1331 SOURCE_LINE_ID,
1332 decode(FINAL_COMPLETION_FLAG,'Y',4,2), --> 2 Validation, 4 Completion
1333 decode(FINAL_COMPLETION_FLAG,'Y',4,1), --> 1 Pending, 4 Complete
1334 ORGANIZATION_ID,
1335 3, --> Load type: 4 Create non-standard wip job, 3 Update non-standard wip job
1336 PRIMARY_ITEM_ID,
1337 START_QUANTITY,
1338 decode(FINAL_COMPLETION_FLAG,'Y',4,3), --> Status type: 3 Released, 4 Complete
1339 --FIRST_UNIT_START_DATE,
1340 --FIRST_UNIT_COMPLETION_DATE,
1341 --LAST_UNIT_START_DATE,
1342 --LAST_UNIT_COMPLETION_DATE,
1343 CLASS_CODE,
1344 CSP_REPAIR_PO_HEADERS_rec.WIP_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
1345 'REPAIR_EXECUTION'||CSP_REPAIR_PO_HEADERS_rec.WIP_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
1346 FROM WIP_DISCRETE_JOBS
1347 WHERE WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
1348
1349 Exception
1350 When others then
1351 l_sqlcode := SQLCODE;
1352 l_sqlerrm := SQLERRM;
1353 g_retcode := 1;
1354 errbuf := SQLERRM;
1355 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1356 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1357 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
1358 fnd_msg_pub.add;
1359 Add_Err_Msg;
1360 Rollback to RUN_REPAIR_EXECUTION_PVT;
1361 End;
1362 --------------------- End comment on Nov-29-2005 -----------------
1363 */
1364
1365 /** If possible update WIP_DISCRETE_JOBS directly to update the quantity completed so far.
1366 If FINAL_COMPLETION_FLAG = 'Y' and not automatically moved to complet status by mass upload
1367 update the STATUS_TYPE = 4(Complete) OR 12(Closed)
1368 **/
1369
1370 /*
1371 Update WIP_DISCRETE_JOBS
1372 set --QUANTITY_COMPLETED = nvl(QUANTITY_COMPLETED,0) + PO_REQ_RECEIVED_QTY_rec.quantity_received,
1373 LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
1374 DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
1375 Where WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
1376 */
1377 /** "MISCELLANEOUS ISSUE" : MATERIAL TRANSACTION : TRANSACTION_TYPE_ID (32): ----
1378 When PO qty is received and wip job qty is transacted to Usable Subinv through WIP Assembly Completion then
1379 From Repair Supplier Org's Usable Subinv do this "MISCELLANEOUS_ISSUE" MATERIAL TRANSACTION
1380 **/
1381
1382 csp_transactions_pub.transact_material
1383 ( p_api_version => 1.0
1384 , p_init_msg_list => FND_API.G_FALSE
1385 , p_commit => FND_API.G_FALSE
1386 , px_transaction_header_id => px_transaction_header_id
1387 , px_transaction_id => t_transaction_id
1388 , p_inventory_item_id => CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID
1389 , p_organization_id => CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
1390 , p_subinventory_code => l_usable_subinv
1391 , p_locator_id => null
1392 , p_lot_number => null
1393 , p_lot_expiration_date => NULL
1394 , p_revision => null
1395 , p_serial_number => null
1396 , p_to_serial_number => null
1397 , p_quantity => L_WIP_COMPLETE_QTY
1398 , p_uom => l_primary_uom_code
1399 , p_source_id => null
1400 , p_source_line_id => null
1401 , p_transaction_type_id => 32
1402 , p_account_id => null
1403 , p_transfer_to_subinventory => null
1404 , p_transfer_to_locator => null
1405 , p_transfer_to_organization => null
1406 , p_online_process_flag => TRUE
1407 , p_transaction_source_id => null
1408 , p_trx_source_line_id => null
1409 , p_transaction_source_name => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id ||'REPAIR_PO_MISC_ISSUE'
1410 , p_waybill_airbill => null
1411 , p_shipment_number => null
1412 , p_freight_code => null
1413 , p_reason_id => null
1414 , p_transaction_reference => CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
1415 , p_expected_delivery_date => null
1416 , x_return_status => l_return_status
1417 , x_msg_count => l_msg_count
1418 , x_msg_data => l_msg_data
1419 );
1420
1421 IF l_return_status <> FND_API.G_RET_STS_SUCCESS and nvl(l_msg_count, 0) > 0 THEN
1422 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1423 Add_Err_Msg;
1424 g_retcode := 1;
1425 errbuf := l_msg_Data;
1426 ROLLBACK TO RUN_REPAIR_EXECUTION_PVT;
1427 End if;
1428
1429 /** FINAL_COMPLETION_FLAG = 'Y' => Repair PO is Closed, WIP_JOB is Complete and ready to close
1430 FINAL_COMPLETION_FLAG = 'N' => Repair PO is not Closed, WIP_JOB is still open in released status_type
1431 **/
1432
1433 UPDATE CSP_REPAIR_PO_HEADERS
1434 SET received_qty = nvl(received_qty,0)+L_WIP_COMPLETE_QTY,
1435 STATUS = decode(FINAL_COMPLETION_FLAG,'Y',9,8)
1436 WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
1437 --WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
1438
1439 COMMIT; /* Do this commit if it is not exits the loop */
1440 --End if;
1441
1442 End if;
1443 End loop;
1444 End loop;
1445
1446 --COMMIT;
1447 ---- End: Step7 ----
1448
1449 ---- Start Scrap/Adjustment ----
1450 /** WIP_ENTITY_TYPE should be '1', If it is '3' then show "WIP_NO_CHARGES_ALLOWED" error
1451 This transaction is for WIP job qty scrap
1452 Do "Return Components from WIP" (43) transaction and wip job qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1453
1454 Passing 'Y' for FINAL_COMPLETION_FLAG is not completing the WIP JOB automatically,
1455 So we need to insert record to WIP_JOB_SCHEDULE_INTERFACE with status_type as 'COMPLETE' VALUE 4.
1456 Then run the WIP_MASS_LOAD Concurrent program to change the JOB status to "COMPLETE".
1457
1458 Do Miscellaneous transaction or SCRAP/ADJUSTMENT transaction from defective subinv
1459
1460 Call REP_PO_SCRAP_ADJUST_TRANSACT(); ----> SCRAP/ADJUSTMENT transaction
1461 **/
1462
1463 IF FND_API.to_Boolean(l_commit) THEN
1464 COMMIT WORK;
1465 END IF;
1466
1467 /** Standard call to get message count and if count is 1, get message info. **/
1468 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1469 p_data => x_msg_data
1470 );
1471 /** Errbuf returns error messages and
1472 Retcode returns 0 = Success, 1 = Success with warnings, 2 = Error
1473 **/
1474
1475 errbuf := X_Msg_Data;
1476 retcode := g_retcode;
1477
1478 EXCEPTION
1479 WHEN FND_API.G_EXC_ERROR THEN
1480 Add_Err_Msg;
1481 retcode := 2;
1482 errbuf := X_Msg_Data;
1483
1484 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1485 P_API_NAME => L_API_NAME
1486 ,P_PKG_NAME => G_PKG_NAME
1487 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1488 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1489 ,P_ROLLBACK_FLAG => l_Rollback
1490 ,X_MSG_COUNT => X_MSG_COUNT
1491 ,X_MSG_DATA => X_MSG_DATA
1492 ,X_RETURN_STATUS => X_RETURN_STATUS);
1493
1494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1495 Add_Err_Msg;
1496 retcode := 2;
1497 errbuf := X_Msg_Data;
1498
1499 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1500 P_API_NAME => L_API_NAME
1501 ,P_PKG_NAME => G_PKG_NAME
1502 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1503 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1504 ,P_ROLLBACK_FLAG => l_Rollback
1505 ,X_MSG_COUNT => X_MSG_COUNT
1506 ,X_MSG_DATA => X_MSG_DATA
1507 ,X_RETURN_STATUS => X_RETURN_STATUS);
1508
1509 Add_Err_Msg;
1510
1511 WHEN OTHERS THEN
1512 Rollback to RUN_REPAIR_EXECUTION_PVT;
1513
1514 l_sqlcode := SQLCODE;
1515 l_sqlerrm := SQLERRM;
1516 retcode := 2;
1517 errbuf := SQLERRM;
1518
1519 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1520 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1521 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
1522 fnd_msg_pub.add;
1523 Add_Err_Msg;
1524
1525 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1526 P_API_NAME => L_API_NAME
1527 ,P_PKG_NAME => G_PKG_NAME
1528 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1529 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1530 ,P_SQLCODE => l_sqlcode
1531 ,P_SQLERRM => l_sqlerrm
1532 ,P_ROLLBACK_FLAG => l_Rollback
1533 ,X_MSG_COUNT => X_MSG_COUNT
1534 ,X_MSG_DATA => X_MSG_DATA
1535 ,X_RETURN_STATUS => X_RETURN_STATUS);
1536
1537 errbuf := sqlerrm;
1538 retcode := 2;
1539 Add_Err_Msg;
1540 END RUN_REPAIR_EXECUTION;
1541
1542
1543 /** REPAIR_PO SCRAP/ADJUSTMENT TRANSACTION **/
1544
1545 PROCEDURE REP_PO_SCRAP_ADJUST_TRANSACT
1546 (p_Api_Version_Number IN NUMBER
1547 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
1548 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1549 ,p_REPAIR_PO_HEADER_ID IN NUMBER
1550 ,p_SCRAP_ADJUST_FLAG IN VARCHAR2
1551 ,p_SCRAP_ADJUST_ITEM_ID IN NUMBER
1552 ,p_SCRAP_ADJUST_QTY IN NUMBER
1553 ,p_SCRAP_ADJUST_DATE IN DATE
1554 , p_reason_id IN NUMBER --VIDYA ADDED HERE
1555 ,x_return_status OUT NOCOPY VARCHAR2
1556 ,x_msg_count OUT NOCOPY NUMBER
1557 ,x_msg_data OUT NOCOPY VARCHAR2
1558 ) IS
1559
1560 CURSOR CSP_REPAIR_PO_SCRAP(L_REPAIR_PO_HEADER_ID NUMBER,
1561 L_SCRAP_ADJUST_ITEM_ID NUMBER,
1562 L_QUANTITY NUMBER
1563 ) IS
1564 SELECT CRPH.repair_po_header_id,
1565 CRPH.PURCHASE_ORDER_HEADER_ID,
1566 CRPH.REQUISITION_LINE_ID,
1567 CRPH.wip_id,
1568 CRPH.inventory_item_id,
1569 CRPH.DEST_ORGANIZATION_ID,
1570 CRPH.repair_supplier_org_id,
1571 CRPH.quantity,
1572 CRPH.received_qty,
1573 CRPL.inventory_item_id defect_item_id,
1574 CRPL.defective_organization_id,
1575 CRPL.quantity defect_qty,
1576 CRPL.received_qty defect_received_qty,
1577 CRPL.SCRAP_QTY,
1578 CRPL.ADJUSTED_QTY
1579 FROM CSP_REPAIR_PO_HEADERS CRPH, CSP_REPAIR_PO_LINES CRPL
1580 WHERE CRPH.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
1581 AND CRPH.status = 8 --> WIP_JOB created in WIP_ENTITIES table
1582 AND CRPH.repair_po_header_id = CRPL.repair_po_header_id
1583 AND CRPL.inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
1584
1585 l_api_version_number CONSTANT NUMBER := 1.0;
1586 l_api_name CONSTANT VARCHAR2(20) := 'REPAIR_PO_SCRAP';
1587
1588 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1589 l_msg_count NUMBER;
1590 l_msg_data VARCHAR2(2000);
1591 l_Rollback VARCHAR2(1) := 'Y';
1592
1593 l_today DATE;
1594 l_user_id NUMBER;
1595 l_login_id NUMBER;
1596
1597 L_REPAIR_PO_HEADER_ID NUMBER;
1598 L_SCRAP_ADJUST_FLAG VARCHAR2(240);
1599 L_SCRAP_ADJUST_ITEM_ID NUMBER;
1600 L_SCRAP_ADJUST_QTY NUMBER;
1601
1602 Balance_due_qty NUMBER;
1603 Available_scrap_qty NUMBER;
1604
1605 x_item_number VARCHAR2(40);
1606 x_item_description VARCHAR2(240);
1607 l_primary_uom_code VARCHAR2(3);
1608 l_org_name VARCHAR2(240);
1609
1610 EXCP_USER_DEFINED EXCEPTION;
1611
1612 l_WIP_BATCH_ID NUMBER;
1613 l_defective_subinv VARCHAR2(240);
1614 l_total_scrap_adjust_qty NUMBER;
1615 FINAL_COMPLETION_FLAG VARCHAR2(1);
1616 l_sqlcode NUMBER;
1617 l_sqlerrm VARCHAR2(2000);
1618
1619 px_transaction_header_id NUMBER;
1620 t_transaction_id NUMBER;
1621 l_transaction_type_id NUMBER;
1622 l_wip_status_type NUMBER;
1623
1624 l_po_con_doc_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1625
1626 l_po_line_changes po_lines_rec_type;
1627 l_shipment_changes PO_SHIPMENTS_REC_TYPE;
1628 l_distribution_changes PO_DISTRIBUTIONS_REC_TYPE;
1629 l_changes PO_CHANGES_REC_TYPE;
1630 l_po_release_id NUMBER;
1631 l_po_header_id NUMBER;
1632 l_org_id NUMBER;
1633 l_po_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1634 l_po_api_errors PO_API_ERRORS_REC_TYPE;
1635
1636 poLineIdTbl po_tbl_number := po_tbl_number();
1637 l_qtys po_tbl_number := po_tbl_number();
1638
1639 /*
1640 CURSOR PO_REQ_RECEIVED_QTY(l_requisition_line_id NUMBER) IS
1641 SELECT PLL.quantity_received, PRL.closed_code,
1642 POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, -- POH.closed_code,
1643 PLL.line_location_id, PLL.po_line_id
1644 FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
1645 WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
1646 PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
1647 PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
1648 */
1649
1650 L_quantity_received NUMBER;
1651 L_closed_code VARCHAR2(25);
1652 LC_po_header_id NUMBER;
1653 L_PO_Quantity NUMBER;
1654 L_line_location_id NUMBER;
1655 L_po_line_id NUMBER;
1656 l_account_id number;
1657 Prof_val varchar2(100);
1658 l_reason_id number;
1659
1660 BEGIN
1661 ---- Start Scrap / Adjustment ----
1662
1663 /**
1664 In CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL procedure,
1665 Insert into mtl_transactions_interface table's WIP_ENTITY_TYPE column value must be '1',
1666 if it is '3' then show "WIP_NO_CHARGES_ALLOWED" error.
1667
1668 This transaction is for WIP_JOB quantity Scrap/Adjustment
1669
1670 WIP_JOB qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1671 by doing material transaction of type "Return Components from WIP" (43)
1672
1673 Passing 'Y' for FINAL_COMPLETION_FLAG is not completing the WIP JOB automatically,
1674 So we need to insert record to WIP_JOB_SCHEDULE_INTERFACE with status_type as 'COMPLETE' VALUE 4.
1675 Then run the WIP_MASS_LOAD Concurrent program to change the JOB status to "COMPLETE".
1676
1677 Do Miscellaneous issue transaction or SCRAP/ADJUSTMENT transaction from
1678 Repair Supplier Org's defective subinv for the scrap qty
1679 **/
1680
1681 SAVEPOINT REPAIR_PO_SCRAP_PVT;
1682
1683 /** Standard call to check for call compatibility **/
1684 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1685 p_api_version_number,
1686 l_api_name,
1687 G_PKG_NAME)
1688 THEN
1689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1690 END IF;
1691
1692 /** Initialize message list **/
1693 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1694 FND_MSG_PUB.initialize;
1695 END IF;
1696
1697 /** Initialize return status **/
1698 x_return_status := FND_API.G_RET_STS_SUCCESS;
1699
1700 /** User and login information **/
1701 SELECT Sysdate INTO l_today FROM dual;
1702 l_user_id := fnd_global.user_id;
1703 l_login_id := fnd_global.login_id;
1704
1705 L_REPAIR_PO_HEADER_ID := P_REPAIR_PO_HEADER_ID;
1706 L_SCRAP_ADJUST_ITEM_ID := P_SCRAP_ADJUST_ITEM_ID;
1707 L_SCRAP_ADJUST_QTY := P_SCRAP_ADJUST_QTY;
1708 L_SCRAP_ADJUST_FLAG := p_SCRAP_ADJUST_FLAG;
1709 l_reason_id := p_reason_id;
1710 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)
1711 LOOP
1712 Balance_due_qty := nvl(CSP_REPAIR_PO_SCRAP_rec.quantity,0) - nvl(CSP_REPAIR_PO_SCRAP_rec.received_qty,0);
1713 Available_scrap_qty := nvl(CSP_REPAIR_PO_SCRAP_rec.defect_received_qty,0) -
1714 (nvl(CSP_REPAIR_PO_SCRAP_rec.received_qty,0) + nvl(CSP_REPAIR_PO_SCRAP_rec.scrap_qty,0) + nvl(CSP_REPAIR_PO_SCRAP_rec.adjusted_qty,0));
1715
1716 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
1717 (CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id
1718 ,L_SCRAP_ADJUST_ITEM_ID
1719 ,x_item_number
1720 ,x_item_description
1721 ,l_primary_uom_code
1722 ,x_return_status
1723 ,x_msg_data
1724 ,x_msg_count
1725 );
1726 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1727 L_ORG_NAME := CSP_REPAIR_PO_GRP.GET_ORGANIZATION_NAME(CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id);
1728 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_REPAIRORG');
1729 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_ORG_NAME,TRUE);
1730 FND_MSG_PUB.ADD;
1731 RAISE EXCP_USER_DEFINED;
1732 END IF;
1733
1734 If nvl(L_SCRAP_ADJUST_QTY,0) > least(Balance_due_qty,Available_scrap_qty) then
1735 FND_MESSAGE.SET_NAME ('CSP','CSP_NOAVAIL_QTY_TO_SCRAP_ADJUST'); /* Not enough quantity to do scrap or adjustment transaction from WIP job*/
1736 FND_MESSAGE.SET_TOKEN ('ITEM_NUMBER', X_ITEM_NUMBER,TRUE);
1737 FND_MSG_PUB.ADD;
1738 RAISE EXCP_USER_DEFINED;
1739 Else
1740 l_defective_subinv := FND_PROFILE.value(NAME => 'CSP_REPAIR_SUP_ORG_DEFECT_SUBINV');
1741
1742 Select sum(nvl(scrap_qty,0) + nvl(adjusted_qty,0))
1743 into l_total_scrap_adjust_qty
1744 from CSP_REPAIR_PO_LINES
1745 where REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
1746 group by REPAIR_PO_HEADER_ID;
1747
1748 If NVL(CSP_REPAIR_PO_SCRAP_rec.received_qty,0) = CSP_REPAIR_PO_SCRAP_rec.quantity - (L_SCRAP_ADJUST_QTY + l_total_scrap_adjust_qty) then
1749 FINAL_COMPLETION_FLAG := 'Y';
1750 l_wip_status_type := 4;
1751 Else
1752 FINAL_COMPLETION_FLAG := 'N';
1753 End if;
1754
1755 /** Do material transaction of type Return Components from WIP (43)
1756 i.e WIP_JOB qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1757 **/
1758
1759 If p_SCRAP_ADJUST_FLAG = 'SCRAP' then
1760 Prof_val := fnd_profile.value('CSP_ACCOUNT_SCRAP');
1761 l_account_id := Get_CSP_Acccount_ID (CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id, p_SCRAP_ADJUST_FLAG,Prof_val );
1762 elsif p_SCRAP_ADJUST_FLAG = 'NEGATIVE ADJUSTMENT' then
1763 Prof_val := fnd_profile.value('CSP_ACCT_ADJUSTMENT');
1764 l_account_id := Get_CSP_Acccount_ID (CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id, p_SCRAP_ADJUST_FLAG,Prof_val );
1765 End if;
1766 csp_transactions_pub.transact_material
1767 ( p_api_version => 1.0
1768 , p_init_msg_list => FND_API.G_FALSE
1769 , p_commit => FND_API.G_FALSE
1770 , px_transaction_header_id => px_transaction_header_id
1771 , px_transaction_id => t_transaction_id
1772 , p_inventory_item_id => CSP_REPAIR_PO_SCRAP_rec.defect_item_id
1773 , p_organization_id => CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id
1774 , p_subinventory_code => l_defective_subinv
1775 , p_locator_id => null
1776 , p_lot_number => null
1777 , p_lot_expiration_date => null
1778 , p_revision => null
1779 , p_serial_number => null
1780 , p_to_serial_number => null
1781 , p_quantity => L_SCRAP_ADJUST_QTY
1782 , p_uom => l_primary_uom_code
1783 , p_source_id => null
1784 , p_source_line_id => null
1785 , p_transaction_type_id => 43
1786 , p_account_id => null
1787 , p_transfer_to_subinventory => null
1788 , p_transfer_to_locator => null
1789 , p_transfer_to_organization => null
1790 , p_online_process_flag => TRUE
1791 , p_transaction_source_id => CSP_REPAIR_PO_SCRAP_rec.wip_id
1792 , p_trx_source_line_id => null
1793 , p_transaction_source_name => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id ||'REPAIR_PO_WIP_RETURN'
1794 , p_waybill_airbill => null
1795 , p_shipment_number => null
1796 , p_freight_code => null
1797 , p_reason_id => null
1798 , p_transaction_reference => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id
1799 , p_expected_delivery_date => null
1800 , p_FINAL_COMPLETION_FLAG => FINAL_COMPLETION_FLAG -- May need to add this parameter for wip return
1801 , x_return_status => l_return_status
1802 , x_msg_count => l_msg_count
1803 , x_msg_data => l_msg_data
1804 );
1805
1806 IF l_return_status <> FND_API.G_RET_STS_SUCCESS and l_msg_count <> 0 THEN
1807 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1808 End if;
1809
1810 /**
1811 After WIP_JOB qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
1812 by doing material transaction of type "Return Components from WIP" (43),
1813
1814 Do "MISCELLANEOUS ISSUE": 32 transaction (or)
1815 SCRAP transaction (WIP assembly scrap: 90) (or)
1816 Adjustment transaction : ??????? from
1817 Repair Supplier Org's defective subinv for the scrap qty
1818 **/
1819 If L_SCRAP_ADJUST_FLAG = 'SCRAP' then
1820 l_transaction_type_id := 32; --90;
1821 elsif L_SCRAP_ADJUST_FLAG = 'NEGATIVE ADJUSTMENT' then
1822 l_transaction_type_id := 32;
1823 End if;
1824
1825 csp_transactions_pub.transact_material
1826 ( p_api_version => 1.0
1827 , p_init_msg_list => FND_API.G_FALSE
1828 , p_commit => FND_API.G_FALSE
1829 , px_transaction_header_id => px_transaction_header_id
1830 , px_transaction_id => t_transaction_id
1831 , p_inventory_item_id => CSP_REPAIR_PO_SCRAP_rec.defect_item_id
1832 , p_organization_id => CSP_REPAIR_PO_SCRAP_rec.repair_supplier_org_id
1833 , p_subinventory_code => l_defective_subinv
1834 , p_locator_id => null
1835 , p_lot_number => null
1836 , p_lot_expiration_date => NULL
1837 , p_revision => null
1838 , p_serial_number => null
1839 , p_to_serial_number => null
1840 , p_quantity => L_SCRAP_ADJUST_QTY
1841 , p_uom => l_primary_uom_code
1842 , p_source_id => null
1843 , p_source_line_id => null
1844 , p_transaction_type_id => l_transaction_type_id
1845 , p_account_id => l_account_id
1846 , p_transfer_to_subinventory => null
1847 , p_transfer_to_locator => null
1848 , p_transfer_to_organization => null
1849 , p_online_process_flag => TRUE
1850 , p_transaction_source_id => null
1851 , p_trx_source_line_id => null
1852 , p_transaction_source_name => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id ||'REP_PO_'||L_SCRAP_ADJUST_FLAG||'_MISC'
1853 , p_waybill_airbill => null
1854 , p_shipment_number => null
1855 , p_freight_code => null
1856 , p_reason_id => l_reason_id --null
1857 , p_transaction_reference => CSP_REPAIR_PO_SCRAP_rec.repair_po_header_id
1858 , p_expected_delivery_date => null
1859 , x_return_status => l_return_status
1860 , x_msg_count => l_msg_count
1861 , x_msg_data => l_msg_data
1862 );
1863
1864 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1866 End if;
1867
1868 /** FINAL_COMPLETION_FLAG = 'Y' => Repair PO is Closed, WIP_JOB is Complete and ready to close
1869 FINAL_COMPLETION_FLAG = 'N' => Repair PO is not Closed, WIP_JOB is still open in released status_type
1870 **/
1871
1872 /**
1873 1.We can do this insert (only) if FINAL_COMPLETION_FLAG= 'Y'
1874 i.e CSP_REPAIR_PO_SCRAP_rec.received_qty = CSP_REPAIR_PO_HEADERS_rec.quantity - (L_SCRAP_ADJUST_QTY + l_total_scrap_adjust_qty)
1875 But This Insert may not be needed if FINAL_COMPLETION_FLAG is passed as 'Y' in the above call
1876
1877 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
1878 3.If possible try to update quantity_scrapped column with l_scrap_quanity value instead of updating QUANTITY_COMPLETED column.
1879 **/
1880 ---------------End comment on NOV-29-2005 --------
1881 /*
1882 select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
1883 into l_WIP_BATCH_ID
1884 from dual;
1885
1886 Begin
1887 INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
1888 (
1889 LAST_UPDATE_DATE,
1890 LAST_UPDATED_BY,
1891 CREATION_DATE,
1892 CREATED_BY,
1893 GROUP_ID,
1894 SOURCE_CODE,
1895 SOURCE_LINE_ID,
1896 PROCESS_PHASE,
1897 PROCESS_STATUS,
1898 ORGANIZATION_ID,
1899 LOAD_TYPE,
1900 PRIMARY_ITEM_ID,
1901 START_QUANTITY,
1902 STATUS_TYPE,
1903 --FIRST_UNIT_START_DATE,
1904 --FIRST_UNIT_COMPLETION_DATE,
1905 --LAST_UNIT_START_DATE,
1906 --LAST_UNIT_COMPLETION_DATE,
1907 CLASS_CODE,
1908 WIP_ENTITY_ID,
1909 JOB_NAME
1910 )
1911 SELECT
1912 SYSDATE,
1913 l_user_id,
1914 SYSDATE,
1915 l_user_id,
1916 l_WIP_BATCH_ID,
1917 SOURCE_CODE,
1918 SOURCE_LINE_ID,
1919 decode(FINAL_COMPLETION_FLAG,'Y',4,2), --> 2 Validation, 4 Completion
1920 decode(FINAL_COMPLETION_FLAG,'Y',4,1), --> 1 Pending, 4 Complete
1921 ORGANIZATION_ID,
1922 3, --> Load type: 4 Create non-standard wip job, 3 Update non-standard wip job
1923 PRIMARY_ITEM_ID,
1924 START_QUANTITY - L_SCRAP_ADJUST_QTY, --> reducing the WIP_JOB qty to (repair_po_qty - scrap or adjustment qty)
1925 decode(FINAL_COMPLETION_FLAG,'Y',4,3), --> Status type: 3 Released, 4 Complete
1926 --FIRST_UNIT_START_DATE,
1927 --FIRST_UNIT_COMPLETION_DATE,
1928 --LAST_UNIT_START_DATE,
1929 --LAST_UNIT_COMPLETION_DATE,
1930 CLASS_CODE,
1931 CSP_REPAIR_PO_SCRAP_rec.WIP_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
1932 'REPAIR_EXECUTION'||CSP_REPAIR_PO_SCRAP_rec.WIP_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
1933 FROM WIP_DISCRETE_JOBS
1934 WHERE WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
1935 Exception
1936 When others then
1937 /*
1938 FND_MESSAGE.SET_NAME ('CSP','CSP_INSERT_WIPJOB_ERROR');
1939 FND_MESSAGE.SET_TOKEN ('WIP_ID',CSP_REPAIR_PO_SCRAP_rec.WIP_ID ,TRUE);
1940 FND_MSG_PUB.ADD;
1941 RAISE EXCP_USER_DEFINED;
1942 */
1943 /*
1944 l_sqlcode := SQLCODE;
1945 l_sqlerrm := SQLERRM;
1946 fnd_message.set_name ('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1947 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1948 fnd_message.set_token('SQLERRM', l_sqlcode || ': ' || l_sqlerrm, TRUE);
1949 fnd_msg_pub.add;
1950 RAISE EXCP_USER_DEFINED;
1951 End;
1952 ---------------End comment on NOV-29-2005 --------
1953 */
1954
1955 ------------** Need to check whether this works or not **-------------
1956
1957 If L_SCRAP_ADJUST_FLAG = 'SCRAP' then
1958
1959 /** If possible update WIP_DISCRETE_JOBS directly for scrap transaction**/
1960 Update WIP_DISCRETE_JOBS
1961 set QUANTITY_SCRAPPED = nvl(QUANTITY_SCRAPPED,0) + L_SCRAP_ADJUST_QTY,
1962 LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
1963 DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
1964 Where WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
1965
1966 Elsif L_SCRAP_ADJUST_FLAG = 'NEGATIVE ADJUSTMENT' then
1967
1968 /** If possible update WIP_DISCRETE_JOBS directly for adjustment transaction**/
1969 Update WIP_DISCRETE_JOBS
1970 set QUANTITY_SCRAPPED = nvl(QUANTITY_SCRAPPED,0) + L_SCRAP_ADJUST_QTY,
1971 --QUANTITY_COMPLETED = nvl(QUANTITY_COMPLETED,0) + L_SCRAP_ADJUST_QTY,
1972 LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
1973 DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
1974 Where WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
1975
1976 End if;
1977 -------------------------------------------------------------------------
1978
1979 /** Status 9 = Repair po is closed, 8 = Repair po is not closed and has a open WIP_JOB **/
1980 UPDATE CSP_REPAIR_PO_HEADERS
1981 SET STATUS = decode(FINAL_COMPLETION_FLAG,'Y',9,8)
1982 WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID;
1983
1984 If L_SCRAP_ADJUST_FLAG = 'SCRAP' then
1985
1986 UPDATE CSP_REPAIR_PO_LINES
1987 SET scrap_qty = nvl(scrap_qty,0) + L_SCRAP_ADJUST_QTY
1988 ,SCRAP_DATE = nvl(p_SCRAP_ADJUST_DATE,sysdate)
1989 ,reason_id = l_reason_id
1990 WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
1991 AND inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
1992
1993 Elsif L_SCRAP_ADJUST_FLAG = 'NEGATIVE ADJUSTMENT' then
1994
1995 UPDATE CSP_REPAIR_PO_LINES
1996 SET adjusted_qty = nvl(adjusted_qty,0) + L_SCRAP_ADJUST_QTY
1997 ,ADJUSTMENT_DATE = nvl(p_SCRAP_ADJUST_DATE,sysdate)
1998 ,reason_id = l_reason_id
1999 WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
2000 AND inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
2001
2002 End if;
2003 --select org_id into l_po_operating_unit from po_headers_all where PO_HEADER_ID= l_po_header_id;
2004 --change_operating_unit( l_po_operating_unit );
2005 --fnd_global.apps_initialize(7985, 52124, 201);
2006
2007 SELECT PLL.quantity_received, PRL.closed_code, POH.po_header_id, PRL.Quantity, PLL.line_location_id, PLL.po_line_id, poh.org_id
2008 into L_quantity_received, L_closed_code, LC_po_header_id, L_PO_Quantity, L_line_location_id, L_po_line_id, l_org_id
2009 FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
2010 WHERE PRL.REQUISITION_LINE_ID = CSP_REPAIR_PO_SCRAP_rec.REQUISITION_LINE_ID AND
2011 PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
2012 PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
2013
2014 If FINAL_COMPLETION_FLAG = 'Y' Then
2015 mo_global.init('PO');
2016 mo_global.set_policy_context('S',l_org_id);
2017
2018 /*
2019 SELECT PL.line_num
2020 into L_line_num
2021 FROM PO_LINES_ALL PL, PO_HEADERS_ALL POH
2022 WHERE PL.PO_LINE_ID = L_po_line_id AND
2023 PL.PO_HEADER_ID = POH.PO_HEADER_ID;
2024 */
2025
2026 po_document_control_pub.control_document
2027 (p_api_version =>1.0,
2028 p_init_msg_list =>fnd_api.g_true,
2029 p_commit =>fnd_api.g_true,
2030 x_return_status =>l_po_con_doc_return_status,
2031 p_doc_type =>'PO',
2032 p_doc_subtype =>'STANDARD',
2033 p_doc_id =>CSP_REPAIR_PO_SCRAP_rec.PURCHASE_ORDER_HEADER_ID,
2034 p_doc_num =>null,
2035 p_release_id =>null,
2036 p_release_num =>null,
2037 p_doc_line_id =>L_po_line_id,
2038 p_doc_line_num =>null, --L_line_num,
2039 p_doc_line_loc_id =>null, --L_line_location_id,
2040 p_doc_shipment_num =>null,
2041 p_action =>'CANCEL',
2042 p_action_date =>sysdate,
2043 p_cancel_reason =>'No more repaired parts available from WIP as part of ERO',
2044 p_cancel_reqs_flag =>'N',
2045 p_print_flag =>null,
2046 p_note_to_vendor =>null
2047 --p_use_gldate =>null,-- < ENCUMBRANCE FPJ >
2048 --p_org_id =>6748 -- < Bug#4581621
2049 );
2050
2051
2052 If l_po_con_doc_return_status <> fnd_api.g_ret_sts_success then
2053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2054 End if;
2055
2056 Else
2057
2058 --fnd_global.apps_initialize('1318','50578','201'); --Set the User ID, Application Id and the Responsibility ID
2059 --PO_MOAC_UTILS_PVT.set_org_context(204); --Set the Org Context
2060
2061 mo_global.init('PO');
2062 mo_global.set_policy_context('S',l_org_id);
2063
2064 l_po_header_id := CSP_REPAIR_PO_SCRAP_rec.PURCHASE_ORDER_HEADER_ID;
2065 l_po_release_id := NULL;
2066
2067 poLineIdTbl.extend;
2068 poLineIdTbl(1) := L_po_line_id; --Plug in the appropriate PO Line Id here
2069
2070 l_qtys.extend;
2071 l_qtys(1) := L_PO_Quantity - L_SCRAP_ADJUST_QTY; --Plug in the new Quantity that is needed in the PO
2072
2073 --Create the Line Change Object passing in the Line ID and the New Quantity
2074 l_po_line_changes := po_lines_rec_type.create_object(
2075 p_po_line_id => poLineIdTbl,
2076 p_quantity => l_qtys
2077 );
2078
2079 --Shipment Change - Need not do anything here as the change in Shipment Quantity
2080 --will be automatically taken care of by the update_document API
2081 l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
2082 p_po_line_location_id => null);
2083
2084 --Distribution Change - Need not do anything here as the change in Distribution
2085 --will be automatically taken care of by the update_document API
2086 l_distribution_changes := PO_DISTRIBUTIONS_REC_TYPE.create_object (
2087 p_po_distribution_id => null
2088 );
2089
2090 --Change Object containing all the changes needed to perform on the PO
2091 l_changes := PO_CHANGES_REC_TYPE.create_object (p_po_header_id => l_po_header_id,
2092 p_po_release_id => l_po_release_id,
2093 p_line_changes => l_po_line_changes,
2094 p_shipment_changes => l_shipment_changes,
2095 p_distribution_changes => l_distribution_changes
2096 );
2097
2098 --Call to this API will change the Quantity in the PO
2099 PO_DOCUMENT_UPDATE_GRP.update_document (p_api_version => 1.0,
2100 p_init_msg_list => FND_API.G_TRUE,
2101 x_return_status => l_po_return_status,
2102 p_changes => l_changes,
2103 p_run_submission_checks => FND_API.G_TRUE,
2104 p_launch_approvals_flag => FND_API.G_TRUE,
2105 p_buyer_id => NULL,
2106 p_update_source => NULL,
2107 p_override_date => NULL,
2108 x_api_errors => l_po_api_errors
2109 );
2110
2111 If l_po_return_status <> fnd_api.g_ret_sts_success then
2112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2113 /*
2114 FOR i IN 1..l_po_api_errors.message_text.COUNT LOOP
2115 insert into PO_UPDATE_ERROR_TABLE(l_po_api_errors.message_text(i));
2116 END LOOP;
2117 */
2118 End if;
2119
2120
2121 End if;
2122
2123 End if;
2124 End loop;
2125
2126 IF FND_API.to_Boolean(p_commit) THEN
2127 COMMIT WORK;
2128 END IF;
2129
2130 x_return_status := l_return_status;
2131
2132 /** Standard call to get message count and if count is 1, get message info */
2133 FND_MSG_PUB.Count_And_Get
2134 (p_count => x_msg_count,
2135 p_data => x_msg_data
2136 );
2137
2138 /**
2139 G_RET_STS_SUCCESS means that the API was successful in performing all the operation requested by its caller.
2140 G_RET_STS_ERROR means that the API failed to perform one or more of the operations requested by its caller.
2141 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.
2142
2143 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
2144 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := 'E';
2145 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := 'U';
2146 **/
2147
2148 EXCEPTION
2149 WHEN EXCP_USER_DEFINED THEN
2150 Rollback to REPAIR_PO_SCRAP_PVT;
2151
2152 /** This returns 'E' as status **/
2153 x_return_status := FND_API.G_RET_STS_ERROR;
2154
2155 fnd_msg_pub.count_and_get
2156 (p_count => x_msg_count
2157 ,p_data => x_msg_data);
2158
2159 WHEN FND_API.G_EXC_ERROR THEN
2160 /** This returns 'E' as status **/
2161
2162 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2163 P_API_NAME => L_API_NAME
2164 ,P_PKG_NAME => G_PKG_NAME
2165 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2166 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2167 ,P_ROLLBACK_FLAG => l_Rollback
2168 ,X_MSG_COUNT => X_MSG_COUNT
2169 ,X_MSG_DATA => X_MSG_DATA
2170 ,X_RETURN_STATUS => X_RETURN_STATUS);
2171
2172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2173 /** This returns 'U' as status **/
2174
2175 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2176 P_API_NAME => L_API_NAME
2177 ,P_PKG_NAME => G_PKG_NAME
2178 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2179 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2180 ,P_ROLLBACK_FLAG => l_Rollback
2181 ,X_MSG_COUNT => X_MSG_COUNT
2182 ,X_MSG_DATA => X_MSG_DATA
2183 ,X_RETURN_STATUS => X_RETURN_STATUS);
2184
2185 WHEN OTHERS THEN
2186 Rollback to REPAIR_PO_SCRAP_PVT;
2187
2188 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2189 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
2190 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
2191 FND_MSG_PUB.ADD;
2192 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, l_api_name);
2193
2194 fnd_msg_pub.count_and_get
2195 (p_count => x_msg_count
2196 ,p_data => x_msg_data);
2197
2198 /** This returns 'U' as status **/
2199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2200
2201 END REP_PO_SCRAP_ADJUST_TRANSACT;
2202 --Vidya added this function 12/3/2013
2203 Function Get_CSP_Acccount_ID (p_organization_id NUMBER , p_SCRAP_ADJUST_FLAG varchar2 , Prof_val varchar2) Return NUMBER
2204 Is
2205 l_account_id NUMBER;
2206 Cursor l_Get_Account_Id_Csr IS
2207 Select distribution_account
2208 From mtl_generic_dispositions
2209 Where segment1 = Prof_val--need to change this value based on fnd_profile.value('csp_scar_adjust_account')
2210 And organization_id = p_organization_id
2211 And enabled_flag = 'Y'
2212 And trunc(nvl(effective_date, sysdate-1)) <= trunc(sysdate)
2213 And trunc(nvl(disable_date, sysdate+1)) >= trunc(sysdate);
2214 Begin
2215 Open l_Get_Account_Id_Csr;
2216 Fetch l_Get_Account_Id_Csr Into l_account_id;
2217 Close l_Get_Account_Id_Csr;
2218 Return l_account_id;
2219 End Get_CSP_Acccount_ID;
2220
2221 END CSP_REPAIR_PO_PVT;