/**************************************************************************
** **
** This datafix script deletes the duplicate/dummy Inter-org shipment/ **
** Internal Sales Order shipment lines for which there is no ISSUE OUT **
** transaction happened in the the source/sending organization. **
** **
** **
** Records from the following tables are deleted: **
** 1) rcv_shipment_headers **
** 2) rcv_shipment_lines **
** 3) rcv_lots_supply **
** 4) rcv_lot_transacions **
** 5) rcv_serial_transactions **
** 6) rcv_serials_supply **
** 7) mtl_supply **
** 8) mtl_material_transactions_temp **
** 9) rcv_transactions **
** **
** **
** **
** Documentation detail for the updates need to be added here **
** **
** **
** **
** Note: **
** Please take a backup of the above tables before running script. **
***************************************************************************/
SET SERVEROUTPUT OFF;
DECLARE
l_is_r12_instance NUMBER := 0;
l_shipment_line_id NUMBER;
l_shipment_header_id NUMBER;
l_prev_shipment_header_id NUMBER := -999;
l_shipment_num rcv_shipment_headers.shipment_num%TYPE;
p_patch_level VARCHAR2(30);
l_requisition_line_id NUMBER; --for Req Line Update.
l_quantity_received NUMBER := 0; --for Req Line Update
l_item_id NUMBER; --from rsl
tmp_rt_quantity NUMBER := 0;
tmp_con_quantity NUMBER := 0;
l_req_uom VARCHAR2(30); --requisition line UOM
l_rt_uom VARCHAR2(30); --RT UOM
CURSOR c_shipment IS
SELECT rsl.shipment_header_id,
rsl.shipment_line_id,
rsl.requisition_line_id,
rsl.item_id,
rsh.shipment_num
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE rsl.shipment_header_id = rsh.shipment_header_id
-- AND rsh.creation_date >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
-- AND rsh.creation_date <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
and rsh.SHIPMENT_HEADER_ID=1104033
AND rsl.mmt_transaction_id IS NOT null
AND rsh.receipt_source_code = 'INVENTORY'
AND EXISTS
(
SELECT 1
FROM mtl_parameters mp
WHERE mp.organization_id = rsh.organization_id
)
AND NOT EXISTS--To check ISSUE OUT txn happened in source orgn
(
SELECT 1
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = rsl.mmt_transaction_id
AND mmt.transaction_source_type_id = 13
AND mmt.transaction_action_id in (3,21)
)
AND NOT EXISTS
(
SELECT 1
FROM mtl_material_transactions mmt,
rcv_transactions rt
WHERE mmt.rcv_transaction_id = rt.transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_type = 'DELIVER'
)
UNION
SELECT rsl.shipment_header_id,
rsl.shipment_line_id,
rsl.requisition_line_id,
rsl.item_id,
rsh.shipment_num
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE rsl.shipment_header_id = rsh.shipment_header_id
-- AND rsh.creation_date >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
-- AND rsh.creation_date <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
and rsh.SHIPMENT_HEADER_ID=1104033
AND rsl.mmt_transaction_id IS NOT null
AND rsh.receipt_source_code = 'INTERNAL ORDER'
AND EXISTS
(
SELECT 1
FROM mtl_parameters mp
WHERE mp.organization_id = rsh.organization_id
)
AND NOT EXISTS--To check ISSUE OUT txn happened in source orgn
(
SELECT 1
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = rsl.mmt_transaction_id
AND mmt.transaction_source_type_id = 8
AND mmt.transaction_action_id in (1,3,21, 2)
)
AND NOT EXISTS
(
SELECT 1
FROM mtl_material_transactions mmt,
rcv_transactions rt
WHERE mmt.rcv_transaction_id = rt.transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_type = 'DELIVER'
)
ORDER BY 1;
CURSOR rt_remain(t_requisition_line_id number) is
SELECT rt.quantity,
rt.unit_of_measure
FROM rcv_transactions rt
WHERE rt.requisition_line_id = t_requisition_line_id
AND ( ( rt.TRANSACTION_TYPE = 'RECEIVE' AND rt.INSPECTION_STATUS_CODE = 'NOT INSPECTED')
OR
( rt.TRANSACTION_TYPE = 'CORRECT' AND rt.INSPECTION_STATUS_CODE = 'NOT INSPECTED')
);
BEGIN
OPEN c_shipment;
LOOP
FETCH c_shipment into l_shipment_header_id,l_shipment_line_id,l_requisition_line_id, l_item_id, l_shipment_num;
EXIT WHEN c_shipment%NOTFOUND;
IF l_prev_shipment_header_id <> l_shipment_header_id THEN
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('Datafix for the shipment:'||l_shipment_num);
END IF;
dbms_output.put_line('shipment_line_id : '||l_shipment_line_id);
DELETE FROM rcv_shipment_lines
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_shipment_lines.');
DELETE FROM rcv_lots_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_lots_supply.');
DELETE FROM rcv_lot_transactions
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_lot_transactions.');
DELETE FROM rcv_serials_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_serials_supply.');
DELETE FROM rcv_serial_transactions
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_serial_transactions.');
DELETE FROM mtl_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from mtl_supply.');
DELETE FROM rcv_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_supply.');
DELETE FROM mtl_material_transactions_temp
WHERE rcv_transaction_id IN (SELECT transaction_id
FROM rcv_transactions
WHERE transaction_type = 'DELIVER'
AND shipment_line_id = l_shipment_line_id);
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from mtl_material_transactions_temp.');
DELETE FROM rcv_transactions
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_transactions.');
/*update requisition line */
IF SQL%ROWCOUNT > 0 AND nvl(l_requisition_line_id, -99) <> -99 THEN
BEGIN
l_quantity_received := 0;
SELECT unit_meas_lookup_code into l_req_uom
FROM po_requisition_lines_all
WHERE requisition_line_id = l_requisition_line_id;
OPEN rt_remain(l_requisition_line_id);
LOOP
FETCH rt_remain into tmp_rt_quantity, l_rt_uom;
EXIT WHEN rt_remain%NOTFOUND;
if ( l_rt_uom = l_req_uom ) then
tmp_con_quantity := tmp_rt_quantity;
else
tmp_con_quantity := 0;
po_uom_s.uom_convert(tmp_rt_quantity, l_rt_uom, l_item_id, l_req_uom, tmp_con_quantity);
end if;
l_quantity_received := l_quantity_received + tmp_con_quantity;
END LOOP;
close rt_remain;
UPDATE po_requisition_lines_all
SET quantity_received = l_quantity_received
WHERE requisition_line_id = l_requisition_line_id;
dbms_output.put_line('Updating requisition_line_id ' || l_requisition_line_id || ' quantity_received to ' || l_quantity_received);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error happens during updating requisition_line_id ' || l_requisition_line_id );
END;
END IF;
/*update requisition line */
DELETE FROM rcv_transactions_interface
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_transactions_interface.');
DELETE FROM rcv_shipment_headers
WHERE shipment_header_id = l_shipment_header_id
AND NOT EXISTS
(
SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
);
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_shipment_headers.');
l_prev_shipment_header_id := l_shipment_header_id;
END LOOP;
CLOSE c_shipment;
dbms_output.put_line('Please Commit the transaction manually...');
EXCEPTION
WHEN OTHERS THEN
IF ( c_shipment%ISOPEN ) THEN
CLOSE c_shipment;
END IF;
dbms_output.put_line('Exception Occurred for shipment'||l_shipment_num);
dbms_output.put_line('Exception Occurred for shipment'||l_shipment_line_id);
dbms_output.put_line('Exception Occurred'||SQLERRM);
ROLLBACK;
dbms_output.put_line('Rolled back the transaction...');
END;
/
** **
** This datafix script deletes the duplicate/dummy Inter-org shipment/ **
** Internal Sales Order shipment lines for which there is no ISSUE OUT **
** transaction happened in the the source/sending organization. **
** **
** **
** Records from the following tables are deleted: **
** 1) rcv_shipment_headers **
** 2) rcv_shipment_lines **
** 3) rcv_lots_supply **
** 4) rcv_lot_transacions **
** 5) rcv_serial_transactions **
** 6) rcv_serials_supply **
** 7) mtl_supply **
** 8) mtl_material_transactions_temp **
** 9) rcv_transactions **
** **
** **
** **
** Documentation detail for the updates need to be added here **
** **
** **
** **
** Note: **
** Please take a backup of the above tables before running script. **
***************************************************************************/
SET SERVEROUTPUT OFF;
DECLARE
l_is_r12_instance NUMBER := 0;
l_shipment_line_id NUMBER;
l_shipment_header_id NUMBER;
l_prev_shipment_header_id NUMBER := -999;
l_shipment_num rcv_shipment_headers.shipment_num%TYPE;
p_patch_level VARCHAR2(30);
l_requisition_line_id NUMBER; --for Req Line Update.
l_quantity_received NUMBER := 0; --for Req Line Update
l_item_id NUMBER; --from rsl
tmp_rt_quantity NUMBER := 0;
tmp_con_quantity NUMBER := 0;
l_req_uom VARCHAR2(30); --requisition line UOM
l_rt_uom VARCHAR2(30); --RT UOM
CURSOR c_shipment IS
SELECT rsl.shipment_header_id,
rsl.shipment_line_id,
rsl.requisition_line_id,
rsl.item_id,
rsh.shipment_num
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE rsl.shipment_header_id = rsh.shipment_header_id
-- AND rsh.creation_date >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
-- AND rsh.creation_date <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
and rsh.SHIPMENT_HEADER_ID=1104033
AND rsl.mmt_transaction_id IS NOT null
AND rsh.receipt_source_code = 'INVENTORY'
AND EXISTS
(
SELECT 1
FROM mtl_parameters mp
WHERE mp.organization_id = rsh.organization_id
)
AND NOT EXISTS--To check ISSUE OUT txn happened in source orgn
(
SELECT 1
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = rsl.mmt_transaction_id
AND mmt.transaction_source_type_id = 13
AND mmt.transaction_action_id in (3,21)
)
AND NOT EXISTS
(
SELECT 1
FROM mtl_material_transactions mmt,
rcv_transactions rt
WHERE mmt.rcv_transaction_id = rt.transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_type = 'DELIVER'
)
UNION
SELECT rsl.shipment_header_id,
rsl.shipment_line_id,
rsl.requisition_line_id,
rsl.item_id,
rsh.shipment_num
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE rsl.shipment_header_id = rsh.shipment_header_id
-- AND rsh.creation_date >= To_Date('&from_date', 'DD-MON-YYYY HH24:MI:SS')
-- AND rsh.creation_date <= To_Date('&to_date', 'DD-MON-YYYY HH24:MI:SS')
and rsh.SHIPMENT_HEADER_ID=1104033
AND rsl.mmt_transaction_id IS NOT null
AND rsh.receipt_source_code = 'INTERNAL ORDER'
AND EXISTS
(
SELECT 1
FROM mtl_parameters mp
WHERE mp.organization_id = rsh.organization_id
)
AND NOT EXISTS--To check ISSUE OUT txn happened in source orgn
(
SELECT 1
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = rsl.mmt_transaction_id
AND mmt.transaction_source_type_id = 8
AND mmt.transaction_action_id in (1,3,21, 2)
)
AND NOT EXISTS
(
SELECT 1
FROM mtl_material_transactions mmt,
rcv_transactions rt
WHERE mmt.rcv_transaction_id = rt.transaction_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_type = 'DELIVER'
)
ORDER BY 1;
CURSOR rt_remain(t_requisition_line_id number) is
SELECT rt.quantity,
rt.unit_of_measure
FROM rcv_transactions rt
WHERE rt.requisition_line_id = t_requisition_line_id
AND ( ( rt.TRANSACTION_TYPE = 'RECEIVE' AND rt.INSPECTION_STATUS_CODE = 'NOT INSPECTED')
OR
( rt.TRANSACTION_TYPE = 'CORRECT' AND rt.INSPECTION_STATUS_CODE = 'NOT INSPECTED')
);
BEGIN
OPEN c_shipment;
LOOP
FETCH c_shipment into l_shipment_header_id,l_shipment_line_id,l_requisition_line_id, l_item_id, l_shipment_num;
EXIT WHEN c_shipment%NOTFOUND;
IF l_prev_shipment_header_id <> l_shipment_header_id THEN
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('Datafix for the shipment:'||l_shipment_num);
END IF;
dbms_output.put_line('shipment_line_id : '||l_shipment_line_id);
DELETE FROM rcv_shipment_lines
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_shipment_lines.');
DELETE FROM rcv_lots_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_lots_supply.');
DELETE FROM rcv_lot_transactions
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_lot_transactions.');
DELETE FROM rcv_serials_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_serials_supply.');
DELETE FROM rcv_serial_transactions
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_serial_transactions.');
DELETE FROM mtl_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from mtl_supply.');
DELETE FROM rcv_supply
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_supply.');
DELETE FROM mtl_material_transactions_temp
WHERE rcv_transaction_id IN (SELECT transaction_id
FROM rcv_transactions
WHERE transaction_type = 'DELIVER'
AND shipment_line_id = l_shipment_line_id);
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from mtl_material_transactions_temp.');
DELETE FROM rcv_transactions
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_transactions.');
/*update requisition line */
IF SQL%ROWCOUNT > 0 AND nvl(l_requisition_line_id, -99) <> -99 THEN
BEGIN
l_quantity_received := 0;
SELECT unit_meas_lookup_code into l_req_uom
FROM po_requisition_lines_all
WHERE requisition_line_id = l_requisition_line_id;
OPEN rt_remain(l_requisition_line_id);
LOOP
FETCH rt_remain into tmp_rt_quantity, l_rt_uom;
EXIT WHEN rt_remain%NOTFOUND;
if ( l_rt_uom = l_req_uom ) then
tmp_con_quantity := tmp_rt_quantity;
else
tmp_con_quantity := 0;
po_uom_s.uom_convert(tmp_rt_quantity, l_rt_uom, l_item_id, l_req_uom, tmp_con_quantity);
end if;
l_quantity_received := l_quantity_received + tmp_con_quantity;
END LOOP;
close rt_remain;
UPDATE po_requisition_lines_all
SET quantity_received = l_quantity_received
WHERE requisition_line_id = l_requisition_line_id;
dbms_output.put_line('Updating requisition_line_id ' || l_requisition_line_id || ' quantity_received to ' || l_quantity_received);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error happens during updating requisition_line_id ' || l_requisition_line_id );
END;
END IF;
/*update requisition line */
DELETE FROM rcv_transactions_interface
WHERE shipment_line_id = l_shipment_line_id;
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_transactions_interface.');
DELETE FROM rcv_shipment_headers
WHERE shipment_header_id = l_shipment_header_id
AND NOT EXISTS
(
SELECT 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = l_shipment_header_id
);
dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' rows from rcv_shipment_headers.');
l_prev_shipment_header_id := l_shipment_header_id;
END LOOP;
CLOSE c_shipment;
dbms_output.put_line('Please Commit the transaction manually...');
EXCEPTION
WHEN OTHERS THEN
IF ( c_shipment%ISOPEN ) THEN
CLOSE c_shipment;
END IF;
dbms_output.put_line('Exception Occurred for shipment'||l_shipment_num);
dbms_output.put_line('Exception Occurred for shipment'||l_shipment_line_id);
dbms_output.put_line('Exception Occurred'||SQLERRM);
ROLLBACK;
dbms_output.put_line('Rolled back the transaction...');
END;
/
No comments:
Post a Comment