DECLARE
l_req_changes po_req_changes_rec_type;
l_line_changes po_req_lines_rec_type;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (4000);
-- Collections for line changes
l_req_line_id po_tbl_number := po_tbl_number ();
l_unit_price po_tbl_number := po_tbl_number ();
l_curr_unit_price po_tbl_number := po_tbl_number ();
l_quantity po_tbl_number := po_tbl_number ();
l_sec_quantity po_tbl_number := po_tbl_number ();
l_need_by_date po_tbl_date := po_tbl_date ();
l_deliver_to_loc po_tbl_number := po_tbl_number ();
l_assign_start po_tbl_date := po_tbl_date ();
l_assign_end po_tbl_date := po_tbl_date ();
l_amount po_tbl_number := po_tbl_number ();
l_new_quantity NUMBER := 5; -- the new quantity you want to set for all lines
l_idx NUMBER := 0;
CURSOR c_lines
IS
SELECT requisition_line_id
FROM po_requisition_lines_all
WHERE requisition_header_id = 1203243;
BEGIN
mo_global.set_policy_context ('S', 81);
fnd_global.apps_initialize (3699876
, 20707
, 201);
-- Dynamically build collections from all lines under the header
FOR rec IN c_lines
LOOP
l_idx := l_idx + 1;
l_req_line_id.EXTEND;
l_unit_price.EXTEND;
l_curr_unit_price.EXTEND;
l_quantity.EXTEND;
l_sec_quantity.EXTEND;
l_need_by_date.EXTEND;
l_deliver_to_loc.EXTEND;
l_assign_start.EXTEND;
l_assign_end.EXTEND;
l_amount.EXTEND;
l_req_line_id (l_idx) := rec.requisition_line_id;
l_quantity (l_idx) := l_new_quantity;
l_unit_price (l_idx) := NULL;
l_curr_unit_price (l_idx) := NULL;
l_sec_quantity (l_idx) := NULL;
l_need_by_date (l_idx) := NULL;
l_deliver_to_loc (l_idx) := NULL;
l_assign_start (l_idx) := NULL;
l_assign_end (l_idx) := NULL;
l_amount (l_idx) := NULL;
END LOOP;
DBMS_OUTPUT.put_line ( 'Total lines to update: '
|| l_idx);
IF l_idx = 0
THEN
DBMS_OUTPUT.put_line ('No requisition lines found for header 1203243.');
RETURN;
END IF;
-- Build the line changes object
l_line_changes :=
po_req_lines_rec_type (req_line_id => l_req_line_id
, unit_price => l_unit_price
, currency_unit_price => l_curr_unit_price
, quantity => l_quantity
, secondary_quantity => l_sec_quantity
, need_by_date => l_need_by_date
, deliver_to_location_id => l_deliver_to_loc
, assignment_start_date => l_assign_start
, assignment_end_date => l_assign_end
, amount => l_amount);
-- Build the main changes record
l_req_changes :=
po_req_changes_rec_type (req_header_id => 1203243
, line_changes => l_line_changes
, distribution_changes => NULL);
-- Call the API
po_req_document_update_grp.update_requisition (p_api_version => 1.0
, p_req_changes => l_req_changes
, p_update_source => 'PO'
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.put_line ('All requisition lines updated successfully.');
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( 'API failed with status: '
|| l_return_status);
FOR i IN 1 .. l_msg_count
LOOP
DBMS_OUTPUT.put_line (fnd_msg_pub.get (p_msg_index => i, p_encoded => fnd_api.g_false));
END LOOP;
END IF;
END;
/
