In the PO_LINE_LOCATIONS_ALL table, there exists two columns name INSPECTION_REQUIRED_FLAG and RECEIPT_REQUIRED_FLAG. These two column values decide what is the match approval.
INSPECTION_REQUIRED_FLAG | RECEIPT_REQUIRED_FLAG | Match Approval |
---|---|---|
N | N | 2-Way |
N | Y | 3-Way |
Y | Y | 4-Way |
DECLARE
num NUMBER;
CURSOR c1 IS
SELECT h.org_id,
l.line_num "SEQ NUM",
h.segment1 "PO NUM",
h.authorization_status "STATUS",
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = l.item_id
AND organization_id = 112) ITEM,
h.type_lookup_code "TYPE",
ll.match_option,
CASE
WHEN ll.inspection_required_flag = 'N'
AND receipt_required_flag = 'N' THEN '2-Way'
WHEN ll.inspection_required_flag = 'N'
AND receipt_required_flag = 'Y' THEN '3-Way'
WHEN ll.inspection_required_flag = 'Y'
AND receipt_required_flag = 'Y' THEN '4-Way'
END "Match Approval",
receipt_required_flag,
inspection_required_flag,
ll.po_line_id,
ll.line_location_id
FROM po.po_headers_all h,
po.po_lines_all l,
po.po_line_locations_all ll
WHERE h.po_header_id = l.po_header_id
AND ll.po_line_id = l.po_line_id
--AND ll.line_location_id = d.line_location_id
AND h.closed_date IS NULL
AND h.type_lookup_code NOT IN ( 'QUOTATION' )
--AND receipt_required_flag ||'-'||inspection_required_flag <> 'Y-N'
AND h.segment1 = '<PO_NUMBER>';
BEGIN
num := 0;
FOR c_rec IN c1 LOOP
num := num + 1;
UPDATE po_line_locations_all --Suppose we need to change the matching level to 3-way from 4 or 2 -way
SET receipt_required_flag = 'Y',
inspection_required_flag = 'N'
WHERE po_line_id = c_rec.po_line_id
AND line_location_id = c_rec.line_location_id;
END LOOP;
dbms_output.Put_line('Total records updated : '
||num);
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line('Error '
||SQLERRM);
END;