Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
how to check if the column value is number or character
one example to create a function to return 'Y' if parameter value is number otherwise which is exception in this case to return ('N') ex: [code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE IS l_num NUMBER; BEGIN l_num := TO_NUMBER (p_strinRead more
one example to create a function to return ‘Y’ if parameter value is number otherwise which is exception in this case to return (‘N’)
ex:
[code]CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
PARALLEL_ENABLE
IS
l_num NUMBER;
BEGIN
l_num := TO_NUMBER (p_string);
RETURN ‘Y’;
EXCEPTION
WHEN VALUE_ERROR
THEN
RETURN ‘N’;
END is_number;[/code]
and here you can call the function created above to identify the value passed is number or not like :
[code]SELECT (CASE
WHEN (is_number (mycolumn) = ‘Y’)
THEN
‘your column value is number’
ELSE
‘your column value is not number’
END)
FROM myTable;[/code]
hope this help you.
See lessHow to count specific values from table ?
Hi, try this query : [code]SELECT SUM ( CASE WHEN Col1 = 2 THEN 1 ELSE 0 END + CASE WHEN Col2 = 2 THEN 1 ELSE 0 END + CASE WHEN Col3 = 2 THEN 1 ELSE 0 END) FROM table_name WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]
Hi,
try this query :
[code]SELECT SUM (
See lessCASE WHEN Col1 = 2 THEN 1 ELSE 0 END
+ CASE WHEN Col2 = 2 THEN 1 ELSE 0 END
+ CASE WHEN Col3 = 2 THEN 1 ELSE 0 END)
FROM table_name
WHERE Col1 = 2 OR Col2 = 2 OR Col3 = 2;[/code]
How do i redirect user from page to another in JavaScript ?
Hi Saly, Actually you can achieve this by simulate HTTP redirect by using one of the following : // similar behavior as an HTTP redirect [code]window.location.replace("http://www.oraask.com");[/code] or // similar behavior as clicking on a link [code]window.location.href("http://www.oraask.com");[/cRead more
Hi Saly,
Actually you can achieve this by simulate HTTP redirect by using one of the following :
// similar behavior as an HTTP redirect
[code]window.location.replace(“http://www.oraask.com”);[/code]
or
// similar behavior as clicking on a link
[code]window.location.href(“http://www.oraask.com”);[/code]
at the end this your choice but for my suggestion i prefer using (window.location.replace) because this doesn’t keep originate page in the session history.
hope this help.
See lessHow to add a new column to a table only if not exist?
You can find the following view to access all metadata about the columns :user_tab_cols; -- For all tables owned by the userall_tab_cols ; -- For all tables accessible to the userdba_tab_cols; -- For all tables in the Database.and lets consider you want to add new column only if doesn't exists you cRead more
You can find the following view to access all metadata about the columns :
user_tab_cols; — For all tables owned by the user
all_tab_cols ; — For all tables accessible to the user
dba_tab_cols; — For all tables in the Database.
and lets consider you want to add new column only if doesn’t exists you can use this pl/sql to check and add new column
[code]DECLARE
v_column_exists number := 0;
BEGIN
Select count(*)
into v_column_exists
from user_tab_cols
where column_name = ‘ADD_COLUMN’
and table_name = ‘departments’;
if (v_column_exists = 0) then
execute immediate ‘alter table departments add (ADD_COLUMN NUMBER)’;
end if;
end;[/code]
hope this help.
See lessORA-06550: line , column : PLS-00201: identifier must be declared
ORA-06550: error causes are: You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred. in your example, you selected a value inside the variable (v_last) that is not declared in your block. So to correct your block of code, you canRead more
ORA-06550: error causes are: You tried to execute an invalid block of PLSQL code (like a stored procedure or function), but a compilation error occurred.
in your example, you selected a value inside the variable (v_last) that is not declared in your block.
So to correct your block of code, you can rewrite it like this:
API to create and update Inventory Items in Oracle Apps R12
Hello @Jone, you can create or update inventory item by using API (ego_item_pub.process_item) and this an example : Note : before using both API's be aware that we are not committing the changes. You have to perform explicit commit manually from IDE or set parameter (p_commit = 'T') to commit yoRead more
Hello Jone, you can create or update inventory item by using API (ego_item_pub.process_item) and this an example :
Note : before using both API’s be aware that we are not committing the changes. You have to perform explicit commit manually from IDE or set parameter (p_commit = ‘T’) to commit your changes.
DECLARE l_inventory_item_id NUMBER; l_organization_id NUMBER; l_return_status VARCHAR2 (4000); l_msg_data VARCHAR2 (4000); l_msg_count NUMBER; x_message_list error_handler.error_tbl_type; BEGIN fnd_global.apps_initialize (user_id => 1318, --> OPERATIONS resp_id => 50583, --> Inventory, Vision Operations (USA) resp_appl_id => 401); --> Inventory ego_item_pub.process_item (p_api_version => 1.0 ,p_init_msg_list => 'T' -- (F:- False), (T:- True) ,p_commit => 'F' -- (F:- False), (T:- True) ,p_transaction_type => 'CREATE' -- UPDATE FOR Updating item ,p_segment1 => 'Oraask_Item01' -- ITEM CODE ,p_description => 'Oraask Test Item Description' -- ITEM DESCRIPTION ,p_long_description => 'Oraask Test Long Item Description' -- ITEM LONG DESCRIPTION ,p_organization_id => 204 -- Vision Operations ,p_apply_template => 'ALL' ,p_template_id => 2 -- Purchased Item — select * from mtl_item_templates_vl -- P_TEMPLATE_NAME => '@Purchased Item', -- P_ITEM_TYPE => 'P', ,p_inventory_item_status_code => 'Active' ,p_approval_status => 'A' ,x_inventory_item_id => l_inventory_item_id ,x_organization_id => l_organization_id ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data); IF l_return_status = fnd_api.g_ret_sts_success THEN dbms_output.put_line ('Item is Created Successfully, Inventory Item ID : ' || l_inventory_item_id); ELSE dbms_output.put_line ('Item Creation is Failed'); error_handler.get_message_list (x_message_list => x_message_list); FOR i IN 1 .. x_message_list.count LOOP dbms_output.put_line (x_message_list (i).message_text); END LOOP; ROLLBACK; END IF; --> EXCEPTIONS HANDLING PART EXCEPTION WHEN OTHERS THEN FOR i IN 1 .. l_msg_count LOOP dbms_output.put_line (substr (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255)); dbms_output.put_line ('message is: ' || l_msg_data); END LOOP; END;DECLARE l_inventory_item_id NUMBER; l_organization_id NUMBER; l_return_status VARCHAR2 (4000); l_msg_data VARCHAR2 (4000); l_msg_count NUMBER; x_message_list error_handler.error_tbl_type; BEGIN fnd_global.apps_initialize (user_id => 1318, --> OPERATIONS resp_id => 50583, --> Inventory, Vision Operations (USA) resp_appl_id => 401); --> Inventory ego_item_pub.process_item (p_api_version => 1.0 ,p_init_msg_list => 'T' -- (F:- False), (T:- True) ,p_commit => 'F' -- (F:- False), (T:- True) ,p_transaction_type => 'UPDATE' -- UPDATE FOR Updating item ,p_Inventory_Item_Id => 53899 ,p_description => 'Oraask test description' -- ITEM DESCRIPTION ,p_organization_id => 204 -- Vision Operations ,x_inventory_item_id => l_inventory_item_id ,x_organization_id => l_organization_id ,x_return_status => l_return_status ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data); IF l_return_status = fnd_api.g_ret_sts_success THEN dbms_output.put_line ('Inventory Item has been updated Successfully, Inventory Item ID : ' || l_inventory_item_id); ELSE dbms_output.put_line ('Update inventory item is Failed'); error_handler.get_message_list (x_message_list => x_message_list); FOR i IN 1 .. x_message_list.count LOOP dbms_output.put_line (x_message_list (i).message_text); END LOOP; ROLLBACK; END IF; --> EXCEPTIONS HANDLING PART EXCEPTION WHEN OTHERS THEN FOR i IN 1 .. l_msg_count LOOP dbms_output.put_line (substr (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255)); dbms_output.put_line ('message is: ' || l_msg_data); END LOOP; END;— By executing above code we have updated item description to be “Oraask test description” for inventory item id “53899”
See lessWhat is FNDLOAD command to move Value Sets from one instance to another ?
Hello Matheo, You can use this command to download and upload value sets : FNDLOAD to Download Valueset: $FND_TOP/bin/FNDLOAD username/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name" FNDLOAD to Upload Valueset: $FND_TOP/Read more
Hello Matheo,
You can use this command to download and upload value sets :
FNDLOAD to Download Valueset:
FNDLOAD to Upload Valueset:
Repeat same previous record or item in oracle forms ?
You can use DUPLICATE_RECORD built-in
You can use DUPLICATE_RECORD built-in
See lessHow to show a message in a report at runtime?
hi Albert, You can display a message in Oracle report builder by using the standard built-in srw.message. The basic syntax for using this package is: srw.message(error_no,'YOUR MESSAGE'); the error no will appear along with the message text. and here is the sample to do this in a particular functionRead more
hi Albert,
You can display a message in Oracle report builder by using the standard built-in srw.message.
The basic syntax for using this package is:
the error no will appear along with the message text. and here is the sample to do this in a particular function
See lessHow to add “Auto Refresh” feature in EBS submit request form like version 12.2.6?
Hello Beter,here you will find how to implement this functionality inside 12.1.3 + versions:Click Here : Implement Auto Refresh functionality in EBS 12.1.3+
Hello Beter,
here you will find how to implement this functionality inside 12.1.3 + versions:
Click Here : Implement Auto Refresh functionality in EBS 12.1.3+
See less