[Home] [Help]
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
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:
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;
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
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: /*
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;
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;
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
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
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
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
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
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;
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;
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
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;
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;
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:
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'
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
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'
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,
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;
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:
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'
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
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;
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:
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:
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;
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:
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)
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
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)
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.
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;
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:
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;
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: **/
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:
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
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;
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
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
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,
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
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"
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: );
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
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
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
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
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
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;
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
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
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:
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
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;
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;
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
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);
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
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
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;
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;
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
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)
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:
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
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:
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
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:
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
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';
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
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
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;
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
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
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
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
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
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
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
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:
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
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;
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
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
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
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
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
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
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;
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:
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;
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;
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.
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