Hi All,
I have developed a package body which will validate the customer details and then load into the staging tables if there is no errors in validation. But if it is errored out, i want to show the error messages in the excel sheet, right now i m using raise_application_error('-20001',message value);
But this method worked successfully before we have done bounce OAcore and Apache Server. After once we have done bounce, the custom messages are replaced by other messages and displaying in excel sheet.
i mean, the message which i have used in my package body is for ex:- "Invalid Customer Name". But the message which i can see in excel sheet is "Enter a valid customer name"
Why this is happening. What is main difference between raise_application_error and fnd_messages.set_name,fnd_messages.set_token those packages.
Which among these 2 are mainly used for displaying the messages. And what is individual functionality.
Please refer the Package body below.
PROCEDURE XXCCG_VALIDATE_CUST_DETAILS( i_customer_num IN VARCHAR2
, i_customer_name IN VARCHAR2
, i_send_statement_flag IN VARCHAR2
, i_batch_id IN NUMBER)
AS
x_customer_num VARCHAR2(20) := NULL;
x_customer_name VARCHAR2(240) := NULL;
x_send_statement_flag VARCHAR2(1) := NULL;
x_str VARCHAR2(1000) := NULL;
x_main_err_stg VARCHAR2(4000) := NULL;
x_process_flag VARCHAR2(1) := NULL;
l_mesg VARCHAR2(4000) := NULL;
x_count NUMBER := 0;
BEGIN
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'================================================='
|| 'Customer Number # '
|| i_customer_num
|| '======================================================='
);
---------------------------------------
--Validate customer Number provided
-----------------------------------------
IF i_customer_num IS NOT NULL
THEN
BEGIN
SELECT account_number
INTO x_customer_num
FROM hz_cust_accounts
WHERE account_number = i_customer_num;
EXCEPTION WHEN OTHERS THEN
x_str := 'Invalid Customer Number Entered-->'|| i_customer_num;
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg:= x_main_err_stg||' '||x_str;
END;
ELSE
x_str := 'Customer Number Should Not Be Blank.';
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg := x_main_err_stg||' '||x_str;
END IF;
---------------------------------------
--Validate customer Name provided
-----------------------------------------
IF i_customer_name IS NOT NULL
THEN
BEGIN
SELECT Party_name
INTO x_customer_name
FROM hz_parties hz
, hz_cust_accounts hca
WHERE hz.party_name = i_customer_name
AND hca.account_number = x_customer_num
AND hz.party_id = hca.party_id ;
EXCEPTION WHEN OTHERS THEN
x_str := 'Invalid Customer Name Entered-->'|| i_customer_name;
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg:= x_main_err_stg||' '||x_str;
/* xx_emf_pkg.ERROR ( p_severity => xx_emf_cn_pkg.cn_low
, p_category => xx_emf_cn_pkg.cn_valid
, p_error_text => x_str
, p_record_identifier_1 => NULL
, p_record_identifier_2 => i_customer_name
, p_record_identifier_3 => NULL
);*/
END;
ELSE
x_str := 'Customer Name Should Not Be Blank.';
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg := x_main_err_stg||' '||x_str;
END IF;
------------------------------------------------------------------------------------------------------------
--Validate Whether Customer Number and Customer Name provided are belonging to one Customer Only
------------------------------------------------------------------------------------------------------------
IF (x_customer_num IS NOT NULL AND x_customer_name IS NOT NULL) THEN
BEGIN
SELECT count(1)
INTO x_count
FROM hz_cust_accounts hca
, hz_parties hp
WHERE hp.party_id = hca.party_id
AND hca.account_number = x_customer_num
AND hp.party_name = x_customer_name;
IF(x_count = 0) THEN
x_str := 'Customer number and Name are not belonging to the single customer, please recheck and modify it'||'Customer Number--> '|| i_customer_num ||' and Customer Name-->'|| i_customer_name;
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg:= x_main_err_stg||' '||x_str;
END IF;
EXCEPTION WHEN OTHERS THEN
x_str := 'Invalid Customer Number--> '|| x_customer_num ||' '|| 'or Invalid Customer Name--> '|| x_customer_name ;
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg:= x_main_err_stg||' '||x_str;
END;
ELSE
x_str := 'Customer Number or Customer Name is blank or Wrongly provided.';
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg := x_main_err_stg||' '||x_str;
END IF;
-----------------------------------------------
--Validate Send Statement Flag provided
-----------------------------------------------
IF (i_send_statement_flag IN ('Y', 'N') AND i_send_statement_flag is NOT NULL) THEN
BEGIN
x_send_statement_flag := i_send_statement_flag;
-- DBMS_OUTPUT.put_line('The Send Statement Flag for Customer Name '||x_customer_name||' is '||i_send_statement_flag);
END;
ELSE
x_str := 'Send Statement Flag Should Not Be Blank or Wrong value was been passed';
fnd_file.put_line(fnd_file.LOG,x_str);
x_main_err_stg := x_main_err_stg||' '||x_str;
END IF;
------------------------------------------------------------------------------------------
-- Insert the Succesful Record in the Staging TABLE
------------------------------------------------------------------------------------------
IF(x_main_err_stg IS NOT NULL) THEN
raise_application_error(-20001,x_main_err_stg);
ELSE
x_process_flag := 'V';
BEGIN
INSERT INTO xxccg.xxccg_upd_cust_flag_webadi_stg
(
BATCH_ID,
CUSTOMER_NUMBER,
CUSTOMER_NAME,
SEND_STATEMENT_FLAG,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
PROCESS_FLAG,
ERROR_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID
)
VALUES
(
i_batch_id,--xxccg.xxccg_ext_ar_003_seq, -- BATCH_ID
x_customer_num,
x_customer_name,
x_send_statement_flag,
NULL,
NULL,
NULL,
NULL,
NULL,
x_process_flag,
x_main_err_stg,
SYSDATE,
fnd_profile.VALUE('USER_ID'),
SYSDATE,
fnd_profile.VALUE('USER_ID') ,
fnd_profile.VALUE('LOGIN_ID'),
g_request_id
);
COMMIT;
EXCEPTION WHEN OTHERS THEN
x_str := 'EXCEPTION OCCURRED while inserting into XXCCG_UPD_CUST_FLAG_WEBADI_STG_TABLE table '||SQLERRM;
x_main_err_stg := x_main_err_stg||' '||x_str;
raise_application_error(-20001, x_main_err_stg);
END;
END IF;
END;
EXCEPTION WHEN OTHERS THEN
raise_application_error (-20016, 'EXCEPTION in XXCCG_VALIDATE_CUST_DETAILS procedure : '||SQLERRM);
END XXCCG_VALIDATE_CUST_DETAILS;
-------------------------------------------------------------------------------
/*
Procedure Name : launch Program
Author's name : kodakandla Vijay
Date written : 04-Sep-2019
RICEW Object id : EXT_AR_003
Description : Procedure to Submit Concurrent Program for EMF.
Program Style : Subordinate
Maintenance History:
-- Date Issue# Name Remarks
------------- ------- ---------- ---------------------------------------
-- 04-Sep-2019 1.0 kodakandla Vijay Initial Development
*/
-------------------------------------------------------------------------
PROCEDURE launch_program(i_batch_id NUMBER)
IS
----------------------------------
-- Local variable
----------------------------------
x_request_id NUMBER;
x_error_message VARCHAR2 (2000);
x_resp_id NUMBER;
x_req_return_status BOOLEAN := FALSE;
x_phase VARCHAR2 (100);
x_status VARCHAR2 (100);
x_dev_phase VARCHAR2 (100);
x_dev_status VARCHAR2 (100);
x_message VARCHAR2 (2000);
x_user_id NUMBER;
x_resp_appl_id NUMBER;
x_valid_cnt NUMBER;
BEGIN
x_valid_cnt := 0;
SELECT COUNT(1)
INTO x_valid_cnt
FROM XXCCG.XXCCG_UPD_CUST_FLAG_WEBADI_STG
WHERE batch_id = i_batch_id
AND process_flag = 'V'
AND error_description IS NULL;
IF x_valid_cnt > 0 THEN
-----------------------------------------------------------------------
-- SUBMIT CUSTOM CONCURRENT PROGRAM
-----------------------------------------------------------------------
x_request_id := fnd_request.submit_request(application => 'XXCCG'
,program => 'XXCCG_EXT_AR_003'
-- ,description => 'CCG Update Send Statement Flag for Customers Using WebADI'
,start_time => TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
,sub_request => FALSE
,argument1 => i_batch_id -- Import Batch ID
);
COMMIT;
fnd_file.put_line(fnd_file.LOG,'The CCG Update Send Statement Flag for AR Customers program REQUEST ID is ::'||x_request_id);
IF x_request_id = 0 THEN
fnd_file.put_line (fnd_file.log,'Concurrent request failed to submit');
END IF;
IF x_request_id > 0 THEN
LOOP
--To make process execution to wait for 1st program to complete
x_req_return_status :=
fnd_concurrent.wait_for_request (request_id => x_request_id
,INTERVAL => 2
,max_wait => 60 --in seconds
-- out arguments
,phase => x_phase
,STATUS => x_status
,dev_phase => x_dev_phase
,dev_status => x_dev_status
,message => x_message
);
EXIT WHEN UPPER (x_phase) = 'COMPLETED' OR UPPER (x_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');
END LOOP;
END IF ;
END IF;
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line(fnd_file.LOG,'WHEN OTHERS EXCEPTION in LAUNCH_PROGRAM ::'||SQLERRM);
END LAUNCH_PROGRAM;
Thanks in Advance.
Vijay