I received a request to post code for creating a credit Memo and apply it to invoice to nullify the balance. Below is the sample code using ar_credit_memo_api_pub.create_request API
DECLARE
-- This script was tested in 11i instance --
v_return_status VARCHAR2(1);
p_count NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2(2000);
v_request_id NUMBER;
v_context VARCHAR2(2);
l_cm_lines_tbl arw_cmreq_cover.cm_line_tbl_type_cover;
l_customer_trx_id NUMBER;
cm_trx_id NUMBER;
v_interface_header_rec arw_cmreq_cover.pq_interface_rec_type;
ind NUMBER;
l_trx_number VARCHAR2(30);
CURSOR c_inv(p_trx_number VARCHAR2)
IS
SELECT rct.trx_number
, rct.customer_trx_id
, rctl.customer_trx_line_id
, rctl.quantity_invoiced
, unit_selling_price
FROM ra_customer_trx_all rct, ra_customer_trX_lines_all rctl
WHERE rct.customer_trx_id = rctl.customer_trx_id
AND trx_number = p_trx_number
AND line_type = 'LINE';
PROCEDURE set_context
IS
BEGIN
DBMS_APPLICATION_INFO.set_client_info(0);
MO_GLOBAL.SET_POLICY_CONTEXT('S', 0);
END set_context;
BEGIN
-- Setting the context ----
set_context;
DBMS_OUTPUT.put_line('Invoking Credit Memo Creation process');
l_trx_number := '20116773';
FOR lc_inv IN c_inv(l_trx_number)
LOOP
ind := 1;
l_customer_trx_id := lc_inv.customer_trx_id;
l_cm_lines_tbl(ind).customer_trx_line_id := lc_inv.customer_trx_line_id;
l_cm_lines_tbl(ind).quantity_credited := lc_inv.quantity_invoiced * -1;
l_cm_lines_tbl(ind).price := lc_inv.unit_selling_price;
l_cm_lines_tbl(ind).extended_amount := lc_inv.quantity_invoiced * lc_inv.unit_selling_price * -1;
END LOOP;
ar_credit_memo_api_pub.create_request( -- standard api parameters
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
-- credit memo request parameters
, p_customer_trx_id => l_customer_trX_id
, p_line_credit_flag => 'Y'
, P_CM_LINE_TBL => l_cm_lines_tbl
, p_cm_reason_code => 'RETURN'
, p_skip_workflow_flag => 'Y'
, p_batch_source_name => 'XX_ORDER_ENTRY'
, p_interface_attribute_rec => v_interface_header_rec
, p_credit_method_installments => NULL
, p_credit_method_rules => NULL
, x_return_status => v_return_status
, x_msg_count => v_msg_count
, x_msg_data => v_msg_data
, x_request_id => v_request_id);
DBMS_OUTPUT.put_line('Message count ' || v_msg_count);
IF v_msg_count = 1
THEN
DBMS_OUTPUT.put_line('l_msg_data ' || v_msg_data);
ELSIF v_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
v_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
IF v_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line('Message' || p_count || ' ---' || v_msg_data);
END LOOP;
END IF;
IF v_return_status <> 'S'
THEN
DBMS_OUTPUT.put_line('Failed');
ELSE
SELECT cm_customer_trx_id
INTO cm_trx_id
FROM ra_cm_requests_all
WHERE request_id = v_request_id;
DBMS_OUTPUT.put_line(' CM trx_id = ' || cm_trx_id);
-- You can issue a COMMIT; at this point if you want to save the created credit memo to the database
-- COMMIT;
END IF;
END;
No comments:
Post a Comment