Overview: This Datafix script is used to delete the work confirmation and the associated receipt data. In this script it will reduce the amount/quantity delivered for the work confirmation in po tables and delink the receiving and po data as if no WC has been created and set the WC status to REJECTED status.
Note :
1.User has to pass the work confirmation number to cancel the WC.
2.In case if invoice is created for the WC then user has to cancel the invoice prior to applying this script by contacting the Payable’s dept.
--Step.1-- set serveroutput on;
--Step.2--Run below script to delete work confirmation
DECLARE
l_count NUMBER := 0;
l_ship_num rcv_shipment_headers.shipment_num%TYPE := '501123_0008';
l_matching_basis po_line_locations_all.matching_basis%TYPE;
CURSOR rt IS
SELECT
rsh.shipment_num,
rt.*
FROM
rcv_transactions rt,
rcv_shipment_headers rsh
WHERE
TRIM(rsh.shipment_num) = TRIM(l_ship_num)
AND rsh.receipt_source_code = 'VENDOR'
AND rsh.asn_type = 'WC'
AND rsh.shipment_header_id = rt.shipment_header_id;
CURSOR rsl IS
SELECT
rsh.shipment_num,
rsl.*
FROM
rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE
TRIM(rsh.shipment_num) = TRIM(l_ship_num)
AND rsh.receipt_source_code = 'VENDOR'
AND rsh.asn_type = 'WC'
AND rsl.shipment_line_status_code = 'EXPECTED'
AND rsl.approval_status IN (
'APPROVED',
'REJECTED'
)
AND rsh.shipment_header_id = rsl.shipment_header_id
AND NOT EXISTS (
SELECT
1
FROM
rcv_transactions rt
WHERE
rt.shipment_header_id = rsh.shipment_header_id
);
BEGIN
FOR rec IN rt LOOP
SELECT
poll.matching_basis
INTO l_matching_basis
FROM
po_distributions_all pod,
po_line_locations_all poll
WHERE
pod.po_distribution_id = rec.po_distribution_id
AND poll.line_location_id = pod.line_location_id;
l_count := l_count + 1;
IF ( l_matching_basis = 'QUANTITY' ) THEN
UPDATE po_distributions_all
SET
quantity_delivered = decode(sign(quantity_delivered - rec.quantity), - 1, 0, quantity_delivered - rec.quantity),
last_update_date = sysdate
WHERE
po_distribution_id = rec.po_distribution_id
AND rec.transaction_type = 'DELIVER';
UPDATE po_line_locations_all
SET
quantity_received = decode(sign(quantity_received - rec.quantity), - 1, 0, quantity_received - rec.quantity),
last_update_date = sysdate
WHERE
line_location_id = rec.po_line_location_id
AND rec.transaction_type = 'RECEIVE'
AND matching_basis = 'QUANTITY';
ELSIF ( l_matching_basis = 'AMOUNT' ) THEN
UPDATE po_distributions_all
SET
amount_delivered = decode(sign(amount_delivered - rec.amount), - 1, 0, amount_delivered - rec.amount),
last_update_date = sysdate
WHERE
po_distribution_id = rec.po_distribution_id
AND rec.transaction_type = 'DELIVER';
UPDATE po_line_locations_all
SET
amount_received = decode(sign(amount_received - rec.amount), - 1, 0, amount_received - rec.amount),
last_update_date = sysdate
WHERE
line_location_id = rec.po_line_location_id
AND rec.transaction_type = 'RECEIVE'
AND matching_basis = 'AMOUNT';
END IF;
IF l_matching_basis IN (
'QUANTITY',
'AMOUNT'
) THEN
UPDATE po_line_locations_all
SET
closed_code = decode(closed_code, 'CLOSED FOR RECEIVING', 'OPEN', 'CLOSED FOR INVOICE'),
closed_for_receiving_date = NULL,
closed_reason = NULL,
closed_date = NULL,
closed_flag = NULL,
last_update_date = sysdate
WHERE
closed_code IN (
'CLOSED',
'CLOSED FOR RECEIVING'
)
AND line_location_id = rec.po_line_location_id;
UPDATE po_lines_all
SET
closed_code = 'OPEN',
closed_reason = NULL,
closed_date = NULL,
closed_flag = NULL,
last_update_date = sysdate
WHERE
closed_code = 'CLOSED'
AND po_line_id = rec.po_line_id;
UPDATE po_headers_all
SET
closed_code = 'OPEN',
closed_date = NULL,
last_update_date = sysdate
WHERE
closed_code = 'CLOSED'
AND po_header_id = rec.po_header_id;
END IF;
END LOOP;
FOR rec IN rsl LOOP
SELECT
poll.matching_basis
INTO l_matching_basis
FROM
po_line_locations_all poll
WHERE
poll.line_location_id = rec.po_line_location_id;
l_count := l_count + 1;
IF ( l_matching_basis = 'QUANTITY' ) THEN
UPDATE po_line_locations_all
SET
quantity_shipped = decode(sign(quantity_shipped - rec.quantity_shipped), - 1, 0, quantity_shipped - rec.quantity_shipped
),
last_update_date = sysdate
WHERE
line_location_id = rec.po_line_location_id;
UPDATE rcv_shipment_lines
SET
quantity_shipped = 0,
last_update_date = sysdate
WHERE
shipment_line_id = rec.shipment_line_id;
DELETE FROM rcv_transactions_interface
WHERE
transaction_type IN (
'SHIP',
'RECEIVE'
)
AND interface_source_code = 'ISP'
AND TRIM(shipment_num) = TRIM(rec.shipment_num)
AND ( ( processing_status_code = 'COMPLETED'
AND transaction_status_code = 'ERROR' )
OR ( processing_status_code = 'ERROR'
AND transaction_status_code = 'PENDING' ) )
AND quantity IS NOT NULL
AND po_line_location_id = rec.po_line_location_id;
ELSIF ( l_matching_basis = 'AMOUNT' ) THEN
UPDATE po_line_locations_all
SET
amount_shipped = decode(sign(amount_shipped - rec.amount_shipped), - 1, 0, amount_shipped - rec.amount_shipped),
last_update_date = sysdate
WHERE
line_location_id = rec.po_line_location_id;
UPDATE rcv_shipment_lines
SET
amount_shipped = 0,
requested_amount = 0,
last_update_date = sysdate
WHERE
shipment_line_id = rec.shipment_line_id;
DELETE FROM rcv_transactions_interface
WHERE
transaction_type IN (
'SHIP',
'RECEIVE'
)
AND interface_source_code = 'ISP'
AND TRIM(shipment_num) = TRIM(rec.shipment_num)
AND ( ( processing_status_code = 'COMPLETED'
AND transaction_status_code = 'ERROR' )
OR ( processing_status_code = 'ERROR'
AND transaction_status_code = 'PENDING' ) )
AND amount IS NOT NULL
AND po_line_location_id = rec.po_line_location_id;
END IF;
END LOOP;
IF ( l_count > 0 ) THEN
UPDATE rcv_transactions
SET
po_line_location_id = - 1 * po_line_location_id,
po_distribution_id = - 1 * po_distribution_id,
last_update_date = sysdate
WHERE
shipment_header_id IN (
SELECT
shipment_header_id
FROM
rcv_shipment_headers
WHERE
TRIM(shipment_num) = TRIM(l_ship_num)
AND asn_type = 'WC'
)
AND po_line_location_id > 0;
UPDATE rcv_shipment_lines
SET
approval_status = 'REJECTED',
po_header_id = - 1 * po_header_id,
po_line_id = - 1 * po_line_id,
po_line_location_id = - 1 * po_line_location_id,
po_distribution_id = - 1 * po_distribution_id,
last_update_date = sysdate
WHERE
shipment_header_id IN (
SELECT
shipment_header_id
FROM
rcv_shipment_headers
WHERE
TRIM(shipment_num) = TRIM(l_ship_num)
AND asn_type = 'WC'
)
AND po_line_location_id > 0;
UPDATE rcv_shipment_headers
SET
shipment_num = to_char('R'
|| '-'
|| TRIM(l_ship_num)),
approval_status = 'REJECTED',
last_update_date = sysdate
WHERE
TRIM(shipment_num) = TRIM(l_ship_num)
AND asn_type = 'WC';
UPDATE rcv_headers_interface
SET
shipment_num = to_char('R'
|| '-'
|| TRIM(l_ship_num)),
last_update_date = sysdate
WHERE
TRIM(shipment_num) = TRIM(l_ship_num)
AND asn_type = 'WC';
DELETE FROM rcv_headers_interface rhi
WHERE
TRIM(rhi.shipment_num) = TRIM(l_ship_num)
AND rhi.processing_status_code = 'ERROR'
AND rhi.asn_type = 'WC';
END IF;
dbms_output.put_line('Pls. check the data again and then commit');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error Occured :');
dbms_output.put_line(' '
|| sqlcode
|| ' -- '
|| sqlerrm);
ROLLBACK;
CLOSE rt;
END;
--Step.3--
--commit;