How to Cancel Requisition Using Oracle API

   CREATE OR REPLACE PROCEDURE cancel_requisition (errbuff out VARCHAR2,

retcode out VARCHAR2,

p_segment1 VARCHAR2)

IS

l_return_status VARCHAR2 (10);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (1000);

lv_header_id po_tbl_number;

lv_line_id po_tbl_number;

l_msg_dummy VARCHAR2 (2000);

CURSOR c_requisition

IS

SELECT poh.requisition_header_id,

pol.requisition_line_id,

poh.authorization_status,

poh.Segment1 Po_Req_Num,

pol.item_description,

pol.line_num,



quantity

FROM po_requisition_headers_all poh, po_requisition_lines_all pol

WHERE poh.requisition_header_id = pol.requisition_header_id

AND poh.authorization_status = 'APPROVED'

AND pol.line_location_id IS NULL

AND pol.source_type_code = 'VENDOR'

AND NVL (pol.cancel_flag, 'N') = 'N'

AND poh.segment1 = NVL (p_segment1, poh.segment1);

BEGIN

FOR c_requisition_rec IN c_requisition

LOOP

lv_header_id := po_tbl_number (c_requisition_rec.requisition_header_id);

lv_line_id := po_tbl_number (c_requisition_rec.requisition_line_id);

po_req_document_cancel_grp.cancel_requisition (

p_api_version => 1.0,

p_req_header_id => lv_header_id,

p_req_line_id => lv_line_id,

p_cancel_date => SYSDATE,

p_cancel_reason => 'Cancelled Requisition',

p_source => 'REQUISITION',

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data);

END LOOP;

COMMIT;

FND_FILE.PUT_LINE (fnd_file.LOG,

'API Status - ' || l_return_status);

IF (l_return_status = 'S') THEN

FND_FILE.PUT_LINE (fnd_file.output, 'Requisition: '||p_Segment1||' Canceled Successfully');

END IF;

IF ((l_return_status <> 'S') AND (l_msg_count > 0))

THEN

FOR i in 1..l_msg_count

loop

fnd_msg_pub.get (

p_msg_index => i,

p_encoded => fnd_api.g_false,

p_data => l_msg_data,

p_msg_index_out => l_msg_dummy);

fnd_file.put_line (fnd_file.log, 'Errors');

fnd_file.put_line (fnd_file.log, l_msg_data);

end loop;

fnd_msg_pub.delete_msg;

retcode := 1;

END IF;





END cancel_requisition;