/*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)
AS
--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 -------*/