cancel PO by api & temp table
-- maintain temp table data
delete from sncus.phil_test_temp01 ;
select * from sncus.phil_test_temp01 for update;
-- check data
SELECT a.segment1 po_num
,b.line_num po_line_num
,c.shipment_num po_shipment_num
,e.temp1 quot_price
,b.po_header_id
,b.po_line_id
,c.line_location_id
,b.unit_price pl_unit_price
,b.quantity line_qty
,d.segment1
,c.quantity loc_qty
,c.quantity_received
,c.quantity_cancelled
,c.quantity_billed
,e.*
FROM po_headers_all a
,po_lines_all b
,po_line_locations_all c
,mtl_system_items_b d
,sncus.phil_test_temp01 e
WHERE a.po_header_id = b.po_header_id
AND b.po_line_id = c.po_line_id
AND a.org_id = 82
AND a.segment1 = e.temp0
and b.line_num = e.temp1
AND d.organization_id = 86
AND d.inventory_item_id = b.item_id
-- AND c.quantity-c.quantity_received <> 0
-- AND e.temp1 - b.unit_price > 0
AND b.cancel_flag = 'N'
AND b.closed_code = 'OPEN'
AND c.closed_code = 'OPEN'
-- and a.segment1= 348088
--AND rownum < 101
--AND a.segment1 = '333341'
--AND b.line_num = '12'
order by 1 ;
-- API
declare
CURSOR c1 IS
SELECT a.segment1 po_num
,b.line_num po_line_num
,c.shipment_num po_shipment_num
,e.temp1 quot_price
,b.po_header_id
,b.po_line_id
,c.line_location_id
,b.unit_price pl_unit_price
,b.quantity line_qty
,d.segment1
,c.quantity loc_qty
,c.quantity_received
,c.quantity_cancelled
,c.quantity_billed
,e.*
FROM po_headers_all a
,po_lines_all b
,po_line_locations_all c
,mtl_system_items_b d
,sncus.phil_test_temp01 e
WHERE a.po_header_id = b.po_header_id
AND b.po_line_id = c.po_line_id
AND a.org_id = 82
AND a.segment1 = e.temp0
and b.line_num = e.temp1
AND d.organization_id = 86
AND d.inventory_item_id = b.item_id
-- AND c.quantity-c.quantity_received <> 0
-- AND e.temp1 - b.unit_price > 0
AND b.cancel_flag = 'N'
AND b.closed_code = 'OPEN'
AND c.closed_code = 'OPEN'
-- and a.segment1= 348088
--AND rownum < 101
--AND a.segment1 = '333341'
--AND b.line_num = '12'
order by 1 ;
l_return_status VARCHAR2(1);
x_return_status VARCHAR2(2000);
v_error_mess VARCHAR2(2000);
o_status VARCHAR2(2000);
o_error_mess VARCHAR2(2000);
p_po_header_id NUMBER;
p_po_line_id NUMBER;
p_po_line_location_id NUMBER;
p_reason VARCHAR2(2000);
p_pr_cancel varchar2(10);
BEGIN
fnd_global.apps_initialize(user_id => 10275
,resp_id => 20707
,resp_appl_id => 201);
mo_global.init('PO'); -- need for R12
FOR cr1 IN c1
LOOP
p_po_header_id := cr1.po_header_id; --657443;
p_po_line_id := cr1.po_line_id; --1705963;
p_po_line_location_id := cr1.line_location_id; --2139244;
p_reason := '因需求單0000007642,Cancel Line';
p_pr_cancel :='N';
--call the Cancel API for PO number PO123
po_document_control_pub.control_document(1.0 -- P_API_VERSION
,fnd_api.g_true -- P_INIT_MSG_LIST
,fnd_api.g_true -- P_COMMIT
,x_return_status -- X_RETURN_STATUS
,'PO' -- P_DOC_TYPE
,'STANDARD' -- P_DOC_SUBTYPE
,p_po_header_id -- P_DOC_ID
,NULL -- P_DOC_NUM
,NULL -- P_RELEASE_ID
,NULL -- P_RELEASE_NUM
,p_po_line_id -- P_DOC_LINE_ID
,NULL -- P_DOC_LINE_NUM
,p_po_line_location_id -- P_DOC_LINE_LOC_ID
,NULL -- P_DOC_SHIPMENT_NUM
,'CANCEL' -- P_ACTION
,SYSDATE -- P_ACTION_DATE
,p_reason -- P_CANCEL_REASON
,p_pr_cancel --'Y' PR是否一併取消 P_CANCEL_REQS_FLAG
,NULL -- P_PRINT_FLAG
,NULL -- P_NOTE_TO_VENDOR
,'N' -- P_USE_GLDATE
);
-- Get any messages returned by the Cancel API
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
--put_log(fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F'));
v_error_mess := substr(v_error_mess ||
fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')
,1
,1998);
END LOOP;
IF x_return_status = fnd_api.g_ret_sts_success THEN
--put_log('CASE B :Cancel PO LINE OK => ' || p_po_line_id);
o_status := 'S';
dbms_output.put_line('PO:'||cr1.po_num||'Line:'||cr1.po_line_num ||' Status:'||o_status);
BEGIN
UPDATE po_lines_all
SET attribute5 = p_reason
WHERE po_header_id = p_po_header_id
AND po_line_id = p_po_line_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('CASE B :update Attribute5 error!');
END;
COMMIT;
ELSE
ROLLBACK;
o_status := 'E';
o_error_mess := v_error_mess;
dbms_output.put_line('Error : PO:'||cr1.po_num||'Line:'||cr1.po_line_num ||' Status:'||o_status || '-' || v_error_mess);
END IF;
END LOOP;
END;
留言
張貼留言