Business Requirement: The finance user requests the IT team to change the PO status to OPEN as they are unable to match the incoming vendor invoices. The PO status is set to closed because of the previous legible transactions or some data corruption issues.
Purpose: Set the Purchase Order Status to Open
1. Identify the PO’s whose status needs to be OPENed
2. Insert them into a temporary table e.g: xxrp_po_number_temp
create table xxrp_po_number_temp (po_number varchar2(200));
insert into xxrp_po_number_temp values (’1089442′);
3. Run the script
BEGIN
FOR c_rec in (select po_number from xxrp_po_number_temp )
LOOP
– Update PO Headers
UPDATE po_headers_all
SET closed_date = ”,
closed_code = ‘OPEN’
WHERE segment1 = c_rec.po_number;
– Update PO Lines
UPDATE po_lines_all
SET closed_date = ”,
closed_code = ‘OPEN’,
closed_by = ”,
last_updated_by = 0,
last_update_date = SYSDATE
WHERE po_header_id IN
(SELECT poh.po_header_id
FROM po_headers_all poh
WHERE poh.segment1 = c_rec.po_number);
– Update PO Shipments
UPDATE po_line_locations_all
SET closed_date = ”,
closed_code = ‘OPEN’,
closed_by = ”,
shipment_closed_date = ”,
last_updated_by = 0,
last_update_date = SYSDATE,
closed_for_receiving_date = ”,
closed_for_invoice_date = ”
WHERE po_header_id IN
(SELECT poh.po_header_id
FROM po_headers_all poh
WHERE poh.segment1 = c_rec.po_number);
– PO Distribution needs no update
— Update action history
DELETE
FROM po_action_history
WHERE object_type_code = ‘PO’
AND object_sub_type_code = ‘STANDARD’
AND action_code = ‘FINALLY CLOSE’
AND object_id =
(SELECT poh.po_header_id
FROM po_headers_all poh
WHERE poh.segment1 = c_rec.po_number
AND type_lookup_code = ‘STANDARD’);
END LOOP;
COMMIT;
EXCEPTION
when others then
rollback;
raise_application_error(-20001,’Error -’ || SQLERRM);
END;
/
–(III) Drop the temporary table
DROP TABLE xxrp_po_number_temp;