[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
fnd_msg_pub.delete_msg;
SELECT *
FROM CSP_REPAIR_PO_HEADERS
WHERE STATUS = l_status
ORDER BY REPAIR_PO_HEADER_ID
FOR UPDATE OF STATUS;
/** Instead of FOR UPDATE selecting rowid which helps to update the current row and do commit inside the loop **/
CURSOR CSP_REPAIR_PO_HEADERS_ROW(l_status NUMBER) IS
SELECT rowid, CRPH.*
FROM CSP_REPAIR_PO_HEADERS CRPH
WHERE STATUS = l_status
ORDER BY REPAIR_PO_HEADER_ID;
SELECT PRIL.authorization_status, PRIL.req_number_segment1
FROM PO_REQUISITIONS_INTERFACE_ALL PRIL
WHERE PRIL.req_number_segment1 = l_requisition_number
AND PRIL.requisition_line_id = l_requisition_line_id;
SELECT PRH.REQUISITION_HEADER_ID,PRH.AUTHORIZATION_STATUS,PRH.segment1
FROM PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL
WHERE PRH.SEGMENT1 = l_requisition_number AND
PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID;
SELECT POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, POH.closed_code,
PLL.line_location_id, PLL.po_line_id
FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
SELECT CRL.*, CRH.dest_organization_id
FROM CSP_REPAIR_PO_HEADERS CRH, CSP_REPAIR_PO_LINES CRL
WHERE CRL.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
AND CRL.REPAIR_PO_HEADER_ID = CRH.REPAIR_PO_HEADER_ID;
SELECT * FROM OE_ORDER_HEADERS_ALL OEH,
OE_ORDER_LINES_ALL OEL, PO_REQUISITION_LINES_ALL PRL
WHERE OEH.HEADER_ID = L_HEADER_ID AND
OEH.HEADER_ID = OEL.HEADER_ID AND
OEL.SOURCE_DOCUMENT_ID = PRL.REQUISITION_HEADER_ID AND
OEL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID;
SELECT PRL.QUANTITY,
PRL.QUANTITY_RECEIVED,
PRL.QUANTITY_DELIVERED,
PRL.ITEM_ID,
PRL.DESTINATION_ORGANIZATION_ID,
PRL.DESTINATION_SUBINVENTORY
FROM OE_ORDER_HEADERS_ALL OEH,
PO_REQUISITION_LINES_ALL PRL
WHERE OEH.HEADER_ID = L_HEADER_ID
AND OEH.SOURCE_DOCUMENT_ID = PRL.REQUISITION_HEADER_ID;
to select quantity_received so far for this PO.
*/
CURSOR PO_REQ_RECEIVED_QTY(l_requisition_line_id NUMBER) IS
SELECT PLL.quantity_received, PRL.closed_code,
POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, -- POH.closed_code,
PLL.line_location_id, PLL.po_line_id
FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
SELECT CRPH.repair_po_header_id,
CRPH.wip_id,
CRPH.inventory_item_id,
CRPH.repair_supplier_org_id,
CRPH.quantity,
CRPH.received_qty,
CRPL.inventory_item_id defect_item_id,
CRPL.defective_organization_id,
CRPL.quantity defect_qty,
CRPL.received_qty defect_received_qty,
CRPL.SCRAP_QTY,
CRPL.ADJUSTED_QTY
FROM CSP_REPAIR_PO_HEADERS CRPH, CSP_REPAIR_PO_LINES CRPL
WHERE CRPH.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
AND CRPH.status = 8
AND CRPH.repair_po_header_id = CRPL.repair_po_header_id
AND CRPL.inventory_item_id = L_SCRAP_ITEM_ID;
SELECT PLL.quantity_received, PRL.closed_code,
POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, -- POH.closed_code,
PLL.line_location_id, PLL.po_line_id
FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
SELECT Sysdate INTO l_today FROM dual;
If that is true or record is moved to PO_REQUISITION_HEADERS_ALL then update the status = 2
else keep status = 1 as it may be still 'IN PROCESS' (or) 'REJECTED' (or) other status
**/
FOR CSP_REPAIR_PO_HEADERS_rec IN C_CSP_REPAIR_PO_HEADERS(1)
LOOP
OPEN PO_REQ_INTERFACE_ALL(CSP_REPAIR_PO_HEADERS_rec.REQUISITION_NUMBER, CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID);
UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 2
WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 2
WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 1
WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
If that is true update the status = 3
else keep status = 2 as it may be still in 'IN PROCESS' (or) 'REJECTED' (or) other status
**/
SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
UPDATE CSP_REPAIR_PO_HEADERS SET STATUS = 3,REQUISITION_HEADER_ID = PO_REQ_HEADERS_ALL_rec.REQUISITION_HEADER_ID
WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
and CLOSED_CODE (or) AUTHORIZATION_STATUS is not 'APPROVED' then update the status = 4.
Else if CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED' then update the status = 5.
**/
SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
UPDATE CSP_REPAIR_PO_HEADERS
SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 4
WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
UPDATE CSP_REPAIR_PO_HEADERS
SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 5
WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
and CLOSED_CODE (or) AUTHORIZATION_STATUS = 'APPROVED' then update the status = 5.
**/
SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
UPDATE CSP_REPAIR_PO_HEADERS
SET PURCHASE_ORDER_HEADER_ID = PO_HEADERS_ALL_rec.PO_HEADER_ID,
PO_NUMBER = PO_HEADERS_ALL_rec.SEGMENT1, STATUS = 5
WHERE CURRENT OF C_CSP_REPAIR_PO_HEADERS;
SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
INTO l_org_id
FROM dual;
csp_sch_int_pvt.DELETE_RESERVATION(p_reservation_id => CSP_RESERVED_LINES_rec.reservation_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data );
SELECT LOCATION_ID
INTO l_ship_to_location_id
FROM MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = l_dest_organization_id
AND SECONDARY_INVENTORY_NAME = l_sec_inv_name; -- 'FldSvc'
SELECT LOCATION_ID
INTO l_ship_to_location_id
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = l_dest_organization_id;
UPDATE CSP_REPAIR_PO_HEADERS
SET INTERNAL_ORDER_HEADER_ID = l_header_rec.order_header_id,
STATUS = 6
-- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
if WIP_JOB NOT created already then insert to interface table and do wip issue transaction
elseif WIP_JOB created already and it is there in WIP_ENTITIES table then do wip issue transaction
**/
--SAVEPOINT RUN_REPAIR_EXECUTION_PVT;
SELECT PLL.quantity_received,
PRL.closed_code,
POH.po_header_id,
POL.Quantity,
PLL.line_location_id,
PLL.po_line_id,
poh.org_id
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
FROM PO_REQUISITION_LINES_ALL PRL,
PO_LINE_LOCATIONS_ALL PLL,
PO_LINES_ALL POL,
PO_HEADERS_ALL POH
WHERE PRL.REQUISITION_LINE_ID =
CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID
AND PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
PO_DOCUMENT_UPDATE_GRP.update_document (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
x_return_status => l_po_return_status,
p_changes => l_changes,
p_run_submission_checks => FND_API.G_TRUE,
p_launch_approvals_flag => FND_API.G_TRUE,
p_buyer_id => NULL,
p_update_source => NULL,
p_override_date => NULL,
x_api_errors => l_po_api_errors
);
insert into PO_UPDATE_ERROR_TABLE(l_po_api_errors.message_text(i));
select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
into l_WIP_BATCH_ID
from dual;
SELECT WIP_ENTITIES_S.NEXTVAL
INTO l_WIP_ENTITY_ID
FROM DUAL;
SELECT CLASS_CODE
INTO L_CLASS_CODE
FROM WIP_NON_STANDARD_CLASSES_VAL_V
WHERE ORGANIZATION_ID = CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id
AND CLASS_TYPE = 4
AND CLASS_CODE = 'Expense';
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
GROUP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_PHASE,
PROCESS_STATUS,
ORGANIZATION_ID,
LOAD_TYPE,
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
CLASS_CODE,
WIP_ENTITY_ID,
JOB_NAME
--FIRM_PLANNED_FLAG
)
VALUES(
sysdate,
l_user_id,
sysdate,
l_user_id,
l_WIP_BATCH_ID,
'CSP',
CSP_REPAIR_PO_HEADERS_rec.INTERNAL_ORDER_HEADER_ID, --> (or) Pass CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
2, --> 2 Validation, 4 Completion
1, --> 1 Pending, 4 Complete
CSP_REPAIR_PO_HEADERS_rec.repair_supplier_org_id,
4, --> LOAD_TYPE: 4 Non-standard discrete jobs, 3 update discrete jobs, 1 standard discrete jobs
CSP_REPAIR_PO_HEADERS_rec.INVENTORY_ITEM_ID,
nvl(IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,0),--CSP_REPAIR_PO_HEADERS_rec.QUANTITY,
3, --> Status type: 3 Released, 4 Complete, 12 closed
SYSDATE, --> FIRST_UNIT_START_DATE
CSP_REPAIR_PO_HEADERS_rec.NEED_BY_DATE, --> FIRST_UNIT_COMPLETION_DATE,
SYSDATE, --> LAST_UNIT_START_DATE
CSP_REPAIR_PO_HEADERS_rec.NEED_BY_DATE, --> LAST_UNIT_COMPLETION_DATE
L_CLASS_CODE, --> 'Expense'
l_WIP_ENTITY_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
'REPAIR_EXECUTION'||l_WIP_ENTITY_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
--1
);
Select wip_entity_id
into l_wip_entity_id
from WIP_ENTITIES
Where wip_entity_id = l_WIP_ENTITY_ID;
UPDATE CSP_REPAIR_PO_HEADERS
SET WIP_ID = l_WIP_ENTITY_ID, STATUS = 8
-- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
UPDATE WIP_REQUIREMENT_OPERATIONS
SET QUANTITY_PER_ASSEMBLY = 1
WHERE INVENTORY_ITEM_ID = IO_QTY_RECEIVED_CHECK_REC.item_id
AND ORGANIZATION_ID = IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
AND WIP_ENTITY_ID = l_WIP_ENTITY_ID ;
/* STATUS = 7 => Inserted into 'WIP_JOB_SCHEDULE_INTERFACE' */
UPDATE CSP_REPAIR_PO_HEADERS
SET WIP_ID = l_WIP_ENTITY_ID_INTERFACE, STATUS = 7
-- WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
UPDATE CSP_REPAIR_PO_LINES
SET RECEIVED_QTY = IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
where repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
SELECT RECEIVED_QTY
INTO l_RECEIVED_QTY
FROM CSP_REPAIR_PO_LINES
WHERE repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
Select wip_entity_id
into l_wip_entity_id
from WIP_ENTITIES
Where wip_entity_id = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
Update WIP_DISCRETE_JOBS
set START_QUANTITY = IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED,
LAST_UPDATE_DATE = SYSDATE
Where WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
UPDATE WIP_REQUIREMENT_OPERATIONS
SET QUANTITY_PER_ASSEMBLY = 1
WHERE INVENTORY_ITEM_ID = IO_QTY_RECEIVED_CHECK_REC.item_id
AND ORGANIZATION_ID = IO_QTY_RECEIVED_CHECK_REC.DESTINATION_ORGANIZATION_ID
AND WIP_ENTITY_ID = l_WIP_ENTITY_ID ;
UPDATE CSP_REPAIR_PO_HEADERS
SET STATUS = 8
--WHERE CURRENT OF CSP_REPAIR_PO_HEADERS;
UPDATE CSP_REPAIR_PO_LINES
SET RECEIVED_QTY = NVL(RECEIVED_QTY,0) + nvl(l_wib_issue_qty,0)
--SET RECEIVED_QTY = NVL(RECEIVED_QTY,0) + IO_QTY_RECEIVED_CHECK_REC.QUANTITY_RECEIVED
where repair_po_header_id = CSP_REPAIR_PO_HEADERS_rec.repair_po_header_id
and inventory_item_id = IO_QTY_RECEIVED_CHECK_REC.item_id;
SELECT PLL.quantity_received, PRL.closed_code, POH.po_header_id, PRL.Quantity, PLL.line_location_id, PLL.po_line_id,poh.org_id
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
FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
WHERE PRL.REQUISITION_LINE_ID = CSP_REPAIR_PO_HEADERS_rec.REQUISITION_LINE_ID AND
PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
--will be automatically taken care of by the update_document API
l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
p_po_line_location_id => null);
--will be automatically taken care of by the update_document API
l_distribution_changes := PO_DISTRIBUTIONS_REC_TYPE.create_object (
p_po_distribution_id => null
);
PO_DOCUMENT_UPDATE_GRP.update_document (p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
x_return_status => l_po_return_status,
p_changes => l_changes,
p_run_submission_checks => FND_API.G_TRUE,
p_launch_approvals_flag => FND_API.G_TRUE,
p_buyer_id => NULL,
p_update_source => NULL,
p_override_date => NULL,
x_api_errors => l_po_api_errors
);
Select sum(nvl(scrap_qty,0) + nvl(adjusted_qty,0))
into l_total_scrap_adjust_qty
from CSP_REPAIR_PO_LINES
where REPAIR_PO_HEADER_ID = CSP_REPAIR_PO_HEADERS_rec.REPAIR_PO_HEADER_ID
group by REPAIR_PO_HEADER_ID;
SELECT START_QUANTITY,QUANTITY_SCRAPPED
INTO L_WIP_START_QUANTITY, L_WIP_QUANTITY_SCRAPPED
FROM WIP_DISCRETE_JOBS
WHERE CSP_REPAIR_PO_HEADERS_REC.WIP_ID = WIP_ENTITY_ID;
/** 1.We can do this insert (only) if FINAL_COMPLETION_FLAG = 'Y'
i.e PO_REQ_RECEIVED_QTY_rec.quantity_received = CSP_REPAIR_PO_HEADERS_rec.quantity - l_total_scrap_adjust_qty
But This Insert may not be needed if FINAL_COMPLETION_FLAG is passed as 'Y' in the above call
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
**/
--------------------- Start comment on Nov-29-2005 -----------------
/*
select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
into l_WIP_BATCH_ID
from dual;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
GROUP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_PHASE,
PROCESS_STATUS,
ORGANIZATION_ID,
LOAD_TYPE,
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE,
--FIRST_UNIT_START_DATE,
--FIRST_UNIT_COMPLETION_DATE,
--LAST_UNIT_START_DATE,
--LAST_UNIT_COMPLETION_DATE,
CLASS_CODE,
WIP_ENTITY_ID,
JOB_NAME
)
Select
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_WIP_BATCH_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
decode(FINAL_COMPLETION_FLAG,'Y',4,2), --> 2 Validation, 4 Completion
decode(FINAL_COMPLETION_FLAG,'Y',4,1), --> 1 Pending, 4 Complete
ORGANIZATION_ID,
3, --> Load type: 4 Create non-standard wip job, 3 Update non-standard wip job
PRIMARY_ITEM_ID,
START_QUANTITY,
decode(FINAL_COMPLETION_FLAG,'Y',4,3), --> Status type: 3 Released, 4 Complete
--FIRST_UNIT_START_DATE,
--FIRST_UNIT_COMPLETION_DATE,
--LAST_UNIT_START_DATE,
--LAST_UNIT_COMPLETION_DATE,
CLASS_CODE,
CSP_REPAIR_PO_HEADERS_rec.WIP_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
'REPAIR_EXECUTION'||CSP_REPAIR_PO_HEADERS_rec.WIP_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
/** If possible update WIP_DISCRETE_JOBS directly to update the quantity completed so far.
If FINAL_COMPLETION_FLAG = 'Y' and not automatically moved to complet status by mass upload
update the STATUS_TYPE = 4(Complete) OR 12(Closed)
**/
/*
Update WIP_DISCRETE_JOBS
set --QUANTITY_COMPLETED = nvl(QUANTITY_COMPLETED,0) + PO_REQ_RECEIVED_QTY_rec.quantity_received,
LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
Where WIP_ENTITY_ID = CSP_REPAIR_PO_HEADERS_rec.WIP_ID;
UPDATE CSP_REPAIR_PO_HEADERS
SET received_qty = nvl(received_qty,0)+L_WIP_COMPLETE_QTY,
STATUS = decode(FINAL_COMPLETION_FLAG,'Y',9,8)
WHERE ROWID = CSP_REPAIR_PO_HEADERS_rec.ROWID;
So we need to insert record to WIP_JOB_SCHEDULE_INTERFACE with status_type as 'COMPLETE' VALUE 4.
Then run the WIP_MASS_LOAD Concurrent program to change the JOB status to "COMPLETE".
Do Miscellaneous transaction or SCRAP/ADJUSTMENT transaction from defective subinv
Call REP_PO_SCRAP_ADJUST_TRANSACT(); ----> SCRAP/ADJUSTMENT transaction
SELECT CRPH.repair_po_header_id,
CRPH.PURCHASE_ORDER_HEADER_ID,
CRPH.REQUISITION_LINE_ID,
CRPH.wip_id,
CRPH.inventory_item_id,
CRPH.DEST_ORGANIZATION_ID,
CRPH.repair_supplier_org_id,
CRPH.quantity,
CRPH.received_qty,
CRPL.inventory_item_id defect_item_id,
CRPL.defective_organization_id,
CRPL.quantity defect_qty,
CRPL.received_qty defect_received_qty,
CRPL.SCRAP_QTY,
CRPL.ADJUSTED_QTY
FROM CSP_REPAIR_PO_HEADERS CRPH, CSP_REPAIR_PO_LINES CRPL
WHERE CRPH.REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
AND CRPH.status = 8 --> WIP_JOB created in WIP_ENTITIES table
AND CRPH.repair_po_header_id = CRPL.repair_po_header_id
AND CRPL.inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
SELECT PLL.quantity_received, PRL.closed_code,
POH.po_header_id, POH.segment1, POH.AUTHORIZATION_STATUS, -- POH.closed_code,
PLL.line_location_id, PLL.po_line_id
FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
WHERE PRL.REQUISITION_LINE_ID = l_requisition_line_id AND
PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
Insert into mtl_transactions_interface table's WIP_ENTITY_TYPE column value must be '1',
if it is '3' then show "WIP_NO_CHARGES_ALLOWED" error.
This transaction is for WIP_JOB quantity Scrap/Adjustment
WIP_JOB qty is returned from WIP JOB to Repair Supplier Org's Defect subinv
by doing material transaction of type "Return Components from WIP" (43)
Passing 'Y' for FINAL_COMPLETION_FLAG is not completing the WIP JOB automatically,
So we need to insert record to WIP_JOB_SCHEDULE_INTERFACE with status_type as 'COMPLETE' VALUE 4.
Then run the WIP_MASS_LOAD Concurrent program to change the JOB status to "COMPLETE".
Do Miscellaneous issue transaction or SCRAP/ADJUSTMENT transaction from
Repair Supplier Org's defective subinv for the scrap qty
**/
SAVEPOINT REPAIR_PO_SCRAP_PVT;
SELECT Sysdate INTO l_today FROM dual;
Select sum(nvl(scrap_qty,0) + nvl(adjusted_qty,0))
into l_total_scrap_adjust_qty
from CSP_REPAIR_PO_LINES
where REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
group by REPAIR_PO_HEADER_ID;
1.We can do this insert (only) if FINAL_COMPLETION_FLAG= 'Y'
i.e CSP_REPAIR_PO_SCRAP_rec.received_qty = CSP_REPAIR_PO_HEADERS_rec.quantity - (L_SCRAP_ADJUST_QTY + l_total_scrap_adjust_qty)
But This Insert may not be needed if FINAL_COMPLETION_FLAG is passed as 'Y' in the above call
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
3.If possible try to update quantity_scrapped column with l_scrap_quanity value instead of updating QUANTITY_COMPLETED column.
**/
---------------End comment on NOV-29-2005 --------
/*
select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
into l_WIP_BATCH_ID
from dual;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE
(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
GROUP_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
PROCESS_PHASE,
PROCESS_STATUS,
ORGANIZATION_ID,
LOAD_TYPE,
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE,
--FIRST_UNIT_START_DATE,
--FIRST_UNIT_COMPLETION_DATE,
--LAST_UNIT_START_DATE,
--LAST_UNIT_COMPLETION_DATE,
CLASS_CODE,
WIP_ENTITY_ID,
JOB_NAME
)
SELECT
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_WIP_BATCH_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
decode(FINAL_COMPLETION_FLAG,'Y',4,2), --> 2 Validation, 4 Completion
decode(FINAL_COMPLETION_FLAG,'Y',4,1), --> 1 Pending, 4 Complete
ORGANIZATION_ID,
3, --> Load type: 4 Create non-standard wip job, 3 Update non-standard wip job
PRIMARY_ITEM_ID,
START_QUANTITY - L_SCRAP_ADJUST_QTY, --> reducing the WIP_JOB qty to (repair_po_qty - scrap or adjustment qty)
decode(FINAL_COMPLETION_FLAG,'Y',4,3), --> Status type: 3 Released, 4 Complete
--FIRST_UNIT_START_DATE,
--FIRST_UNIT_COMPLETION_DATE,
--LAST_UNIT_START_DATE,
--LAST_UNIT_COMPLETION_DATE,
CLASS_CODE,
CSP_REPAIR_PO_SCRAP_rec.WIP_ID, --> Pass existing Wip_Entity_Id for update job status to "Complete"
'REPAIR_EXECUTION'||CSP_REPAIR_PO_SCRAP_rec.WIP_ID --> Pass existing job name(WIP_ENTITY_NAME) for update job status to "Complete"
FROM WIP_DISCRETE_JOBS
WHERE WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
FND_MESSAGE.SET_NAME ('CSP','CSP_INSERT_WIPJOB_ERROR');
/** If possible update WIP_DISCRETE_JOBS directly for scrap transaction**/
Update WIP_DISCRETE_JOBS
set QUANTITY_SCRAPPED = nvl(QUANTITY_SCRAPPED,0) + L_SCRAP_ADJUST_QTY,
LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
Where WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
/** If possible update WIP_DISCRETE_JOBS directly for adjustment transaction**/
Update WIP_DISCRETE_JOBS
set QUANTITY_SCRAPPED = nvl(QUANTITY_SCRAPPED,0) + L_SCRAP_ADJUST_QTY,
--QUANTITY_COMPLETED = nvl(QUANTITY_COMPLETED,0) + L_SCRAP_ADJUST_QTY,
LAST_UPDATE_DATE = SYSDATE, STATUS_TYPE = nvl(l_wip_status_type,STATUS_TYPE),
DATE_COMPLETED = DECODE(FINAL_COMPLETION_FLAG,'Y',SYSDATE,DATE_COMPLETED)
Where WIP_ENTITY_ID = CSP_REPAIR_PO_SCRAP_rec.WIP_ID;
UPDATE CSP_REPAIR_PO_HEADERS
SET STATUS = decode(FINAL_COMPLETION_FLAG,'Y',9,8)
WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID;
UPDATE CSP_REPAIR_PO_LINES
SET scrap_qty = nvl(scrap_qty,0) + L_SCRAP_ADJUST_QTY
,SCRAP_DATE = nvl(p_SCRAP_ADJUST_DATE,sysdate)
,reason_id = l_reason_id
WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
AND inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
UPDATE CSP_REPAIR_PO_LINES
SET adjusted_qty = nvl(adjusted_qty,0) + L_SCRAP_ADJUST_QTY
,ADJUSTMENT_DATE = nvl(p_SCRAP_ADJUST_DATE,sysdate)
,reason_id = l_reason_id
WHERE REPAIR_PO_HEADER_ID = L_REPAIR_PO_HEADER_ID
AND inventory_item_id = L_SCRAP_ADJUST_ITEM_ID;
SELECT PLL.quantity_received, PRL.closed_code, POH.po_header_id, PRL.Quantity, PLL.line_location_id, PLL.po_line_id, poh.org_id
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
FROM PO_REQUISITION_LINES_ALL PRL, PO_LINE_LOCATIONS_ALL PLL, PO_HEADERS_ALL POH
WHERE PRL.REQUISITION_LINE_ID = CSP_REPAIR_PO_SCRAP_rec.REQUISITION_LINE_ID AND
PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND
PLL.PO_HEADER_ID = POH.PO_HEADER_ID;
SELECT PL.line_num
into L_line_num
FROM PO_LINES_ALL PL, PO_HEADERS_ALL POH
WHERE PL.PO_LINE_ID = L_po_line_id AND
PL.PO_HEADER_ID = POH.PO_HEADER_ID;
--will be automatically taken care of by the update_document API
l_shipment_changes := PO_SHIPMENTS_REC_TYPE.create_object (
p_po_line_location_id => null);
--will be automatically taken care of by the update_document API
l_distribution_changes := PO_DISTRIBUTIONS_REC_TYPE.create_object (
p_po_distribution_id => null
);
PO_DOCUMENT_UPDATE_GRP.update_document (p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
x_return_status => l_po_return_status,
p_changes => l_changes,
p_run_submission_checks => FND_API.G_TRUE,
p_launch_approvals_flag => FND_API.G_TRUE,
p_buyer_id => NULL,
p_update_source => NULL,
p_override_date => NULL,
x_api_errors => l_po_api_errors
);
insert into PO_UPDATE_ERROR_TABLE(l_po_api_errors.message_text(i));
Select distribution_account
From mtl_generic_dispositions
Where segment1 = Prof_val--need to change this value based on fnd_profile.value('csp_scar_adjust_account')
And organization_id = p_organization_id
And enabled_flag = 'Y'
And trunc(nvl(effective_date, sysdate-1)) <= trunc(sysdate)
And trunc(nvl(disable_date, sysdate+1)) >= trunc(sysdate);