Oracle Application: #OracleAPI #CreditMemo #AR #ReceivableAPI

Wednesday 20 May 2020

Oracle API - How to Apply Cash Receipt against Standard AR Invoice

/*Following Procedure Used For Applied Receipt against Created AR Invoice Transaction*/

CREATE OR REPLACE PROCEDURE xx_apply_receipt_to_invice (p_cust_trx_id IN NUMBER,

p_cash_receipt_id IN NUMBER,

p_process_type IN VARCHAR2)

--local Variables

g_loc NUMBER := 0;

g_msg VARCHAR2 (500);

g_sysdate DATE := SYSDATE;

l_apl_return_status VARCHAR2 (1);

l_apl_msg_count NUMBER;

l_apl_msg_data VARCHAR2 (240);

l_org_id NUMBER;

l_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;

-- l_rcpt_method_name VARCHAR2 (20);

l_customer_trx_id NUMBER;

l_amount_applied NUMBER;

l_data_txt1 VARCHAR2 (1000) := NULL;

l_error_description1 VARCHAR2 (2000) := NULL;

l_msg_index_num1 NUMBER := 1;

l_resp_id NUMBER := NULL;

l_resp_appl_id NUMBER := NULL;

-- l_org_id NUMBER;

l_user_id NUMBER := NULL;

BEGIN

l_receipt_id := p_cash_receipt_id;

l_customer_trx_id := p_cust_trx_id;

l_msg_index_num1 := NULL;

SELECT CREATED_BY

INTO l_user_id

FROM ar_cash_receipts_all

WHERE cash_receipt_id = l_receipt_id;

SELECT amount

INTO l_amount_applied

FROM ar_cash_receipts_all

WHERE cash_receipt_id = l_receipt_id;

SELECT org_id

INTO l_org_id

FROM ra_customer_trx_all

WHERE customer_trx_id = p_cust_trx_id;

/*To get the initialization attribute values */

l_resp_id := XX_GET_RESP_DTLS (l_org_id);

l_resp_appl_id := XX_GET_APPL_DTLS (l_org_id);

BEGIN

DBMS_OUTPUT.put_line ('Initiaiizing Session ');

mo_global.init ('AR');

mo_global.set_policy_context ('S', l_org_id);

fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);

EXCEPTION

WHEN OTHERS

THEN

l_apl_msg_data := 'Session Initialization failed';

-- l_flag := 'N';

END;

AR_RECEIPT_API_PUB.APPLY (

p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_TRUE,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => l_apl_return_status,

x_msg_count => l_apl_msg_count,

x_msg_data => l_apl_msg_data,

p_cash_receipt_id => l_receipt_id,

p_customer_trx_id => l_customer_trx_id);

COMMIT;

IF l_apl_msg_count = 1

THEN

DBMS_OUTPUT.put_line ('l_apl_msg_data ' || l_apl_msg_data);

ELSIF l_apl_msg_count > 1

THEN

LOOP

l_apl_msg_data :=

fnd_msg_pub.GET (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_apl_msg_data IS NULL

THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line (

'Message' || '---' || ' ---' || l_apl_msg_data);

END LOOP;

END IF;

IF l_apl_return_status = 'S'

THEN

DBMS_OUTPUT.put_line (

'Receipt applied successfully for customer trx id '

|| l_customer_trx_id);

/*As per Zohaib requested Custom Table Not Required we remove the Custom Table Logic 17MAY2018*/

-- UPDATE xx_ar_invoice_recipt_mapping

-- SET RECEIPT_APPLIED_FLAG = 'Y',

-- ERROR_MSG = 'Receipt Applied Succussfully'

-- WHERE customer_trx_id = p_cust_trx_id;

IF p_process_type IN ('REGISTRATION', 'RENEW')

THEN

UPDATE xxag_pos_edirham_reg_ren_t

SET RECEIPT_APPLIED_FLAG = 'Y',

ar_payment_status = 'Y',

ERROR_MSG = 'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

-- COMMIT;

ELSIF p_process_type = 'TENDER'

THEN

UPDATE xxag_pon_edirham_rfq_pay_t

SET RECEIPT_APPLIED_FLAG = 'Y',

ar_payment_status = 'Y',

ERROR_MSG = 'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

-- COMMIT;

END IF;

COMMIT;

DBMS_OUTPUT.put_line ('Receipt Applied Succussfully!!');

FND_FILE.PUT_LINE (FND_FILE.LOG, 'Receipt Applied Succussfully!!');

ELSE

FOR i IN 1 .. l_apl_msg_count

LOOP

FND_MSG_PUB.GET (p_msg_index => i,

p_encoded => 'F',

p_data => l_data_txt1,

p_msg_index_out => l_msg_index_num1);

l_error_description1 :=

SUBSTR (l_error_description1 || l_data_txt1, 1, 400);

DBMS_OUTPUT.put_line (l_error_description1);

END LOOP;

l_error_description1 :=

'Error in Apply Receipt' || l_error_description1;

/*As per Zohaib requested Custom Table Not Required we remove the Custom Table Logic 17MAY2018*/

-- UPDATE xx_ar_invoice_recipt_mapping

-- SET RECEIPT_APPLIED_FLAG = 'N', ERROR_MSG = l_error_description1

-- WHERE customer_trx_id = p_cust_trx_id;

IF p_process_type IN ('REGISTRATION', 'RENEW')

THEN

UPDATE xxag_pos_edirham_reg_ren_t

SET RECEIPT_APPLIED_FLAG = 'N',

ERROR_MSG = l_error_description1 --'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

ELSIF p_process_type = 'TENDER'

THEN

UPDATE xxag_pon_edirham_rfq_pay_t

SET RECEIPT_APPLIED_FLAG = 'N',

ERROR_MSG = l_error_description1 --- 'Receipt Applied Succussfully'

WHERE customer_trx_id = p_cust_trx_id;

END IF;

COMMIT;

DBMS_OUTPUT.put_line (

'Apply Receipt Failed for customer trx id '

|| l_customer_trx_id

|| ' - '

|| l_error_description1);

FND_FILE.PUT_LINE (

FND_FILE.LOG,

'Apply Receipt Failed for customer trx id '

|| l_customer_trx_id

|| ' - '

|| l_error_description1);

FND_FILE.PUT_LINE (

FND_FILE.LOG,

'---------------------------------------------------------------------------------------');

FND_FILE.PUT_LINE (

FND_FILE.LOG,

'---------------------------------------------------------------------------------------');

END IF;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error in procedure ' || SQLERRM);

FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error in procedure ');

END;

/*---------END of Procedure xx_apply_receipt_to_invice -------*/

Oracle API - How to Create Credit Memo & Apply it against Standard AR Invoice

CREATE OR REPLACE PROCEDURE xx_create_cm_inv (p_cust_trx_id IN NUMBER)

-- 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:=0;

l_trx_number VARCHAR2(30);

CURSOR c_inv(p_cust_trx_id NUMBER)

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 rct.customer_trx_id=p_cust_trx_id

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;

-- fnd_global.apps_initialize (user_id => 0,

-- resp_id => v_resp_id,

-- resp_appl_id => 222

-- );

fnd_global.apps_initialize (0,51967, 222);

mo_global.init ('AR');

DBMS_OUTPUT.put_line('Invoking Credit Memo Creation process');

-- l_trx_number := '19445';

FOR lc_inv IN c_inv(p_cust_trx_id)

LOOP

ind := 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;

DBMS_OUTPUT.put_line('process'||ind);

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

-- , p_validation_level => FND_API.G_VALID_LEVEL_FULL

-- 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 => 'MANUAL-OTHER'

, 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 xx_create_cm_inv;