PL/SQL: function that will check if the NUMBER fields entered are valid numbers

-- A function that will check if the NUMBER fields entered are valid numbers
--- To Make sure that all the Number Fields are valid numbers
---------------------------------------------------------------------------------
FUNCTION MCIBE_ISNUMBER( p_string IN VARCHAR2) RETURN VARCHAR2 IS
l_number NUMBER;
BEGIN
l_number := to_number( NVL(TRIM(p_string),'-9999') );
IF ( CEIL(l_number) - (l_number) ) > 0 THEN
RETURN '2'; --- A number with decimal point
ELSIF l_number = 0 THEN
RETURN '1';
ELSE
RETURN '0'; ---A whole number
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN '1';
END MCIBE_ISNUMBER;

PL/SQL: function that will check if the NUMBER fields entered are valid numbers

-- A function that will check if the NUMBER fields entered are valid numbers
--- To Make sure that all the Number Fields are valid numbers
---------------------------------------------------------------------------------
FUNCTION MCIBE_ISNUMBER( p_string IN VARCHAR2) RETURN VARCHAR2 IS
l_number NUMBER;
BEGIN
l_number := to_number( NVL(TRIM(p_string),'-9999') );
IF ( CEIL(l_number) - (l_number) ) > 0 THEN
RETURN '2'; --- A number with decimal point
ELSIF l_number = 0 THEN
RETURN '1';
ELSE
RETURN '0'; ---A whole number
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN '1';
END MCIBE_ISNUMBER;

PL/SQL: A procedure to validate an email id

PROCEDURE MCIBE_VALID_EMAIL_ID (p_email_id VARCHAR2
,p_partner_enduser IN VARCHAR2
,p_err_code IN OUT NUMBER
,p_err_msg IN OUT VARCHAR2) IS
----Variable Declaration
l_first_char VARCHAR2(1);
l_char_in_id VARCHAR2(100);
l_check_dot VARCHAR2(1);
l_email_id VARCHAR2(100);
l_error_flag VARCHAR2(50) := 0;
l_position_of_at NUMBER := -1;
l_position_of_dot NUMBER := -1;
l_len NUMBER;
l_no_of_at NUMBER :=0;
BEGIN
--p_err_code := 0;
l_len := length(TRIM(p_email_id));
IF (l_len <> 0) THEN
----Read Id character by Character
FOR i IN 1 .. l_len
LOOP
l_char_in_id := SUBSTR(p_email_id,i,1);
---Position of the '@'
IF l_char_in_id = '@' THEN
l_position_of_at := i;
l_no_of_at := l_no_of_at +1;
END IF;
---position of the '.'
IF l_char_in_id = '.' THEN
l_position_of_dot := i;
END IF;
END LOOP;
l_first_char := substr(p_email_id,1,1);
---IF '@' or '.' doesn't exists at all
IF (l_position_of_at = -1 OR l_position_of_dot = -1 ) THEN
l_error_flag := 1;
----Check if there are multiple '@'
ELSIF l_no_of_at >1 AND l_error_flag = 0 THEN
l_error_flag := 1;
---Check if first letter is '@' or '.'
ELSIF l_first_char ='.' OR l_first_char = '@' THEN
l_error_flag := 1;
---Check if last letter is '@' or '.'
ELSIF (l_position_of_at = l_len OR l_position_of_dot = l_len) THEN
l_error_flag := 1;
---Check IF '@' never occurs before '.'
ELSIF (l_position_of_at > l_position_of_dot)THEN
l_error_flag := 1;
---Check if '@' occurs one position before '.'
ELSIF (l_position_of_dot - l_position_of_at) = 1 THEN
l_error_flag := 1;
ELSE
l_check_dot := SUBSTR(p_email_id,(l_position_of_at)-1,1) ;
IF(l_check_dot = '.')THEN
l_error_flag := 1;
ELSE
---Check if '.' comes right after '.'
l_check_dot := SUBSTR(p_email_id,(l_position_of_dot)-1,1) ;
IF(l_check_dot = '.')THEN
l_error_flag := 1;
END IF;
END IF;
END IF;
END IF;

----return 1 for error and 0 for no error
IF( l_error_flag = 1) THEN
p_err_code := 1;
p_err_msg := p_err_msg|| ' ; '||'Invalid ' || p_partner_enduser ||' contact Email Id';
END IF;

EXCEPTION
WHEN OTHERS THEN
p_err_code := 1;
p_err_msg := p_err_Msg || ' ; ' ||(SQLCODE || ' ' || substr(SQLERRM,1,100));

END MCIBE_VALID_EMAIL_ID;

PL/SQL: DB Links

DB1: ED01 --This has the table oe_order_stg
DB2: CRD01--Need to access that table in ED01 from this DB.

1. Create a DB link(O11i_TO_CAT.ISUS.MC.COM) ..CRD01 -> ED01
2. Create a Synonym for the ED01 table(oe_order_stg) in CRD01:

CREATE OR REPLACE SYNONYM oe_order_stg
FOR oe_order_stg@O11i_TO_CAT.ISUS.MC.COM
/

3. Now in CRD01. you can access oe_order_stg directly.

Select * from oe_order_stg.

------------------

without synonym:

Select * from oe_order_stg@O11i_TO_CAT.ISUS.MC.COM (In CRD01).

Oracle Apps: Profile Option

Create a new Profile Option:

1. Login as "Application Developer" Responsibility. Go to Profile and create a new profile and save.

2. Login as "System Administrator" Responsibility. Go to Profile -> System. Serch for the name entered in the "user profile field" in step 1. Enter the value and save.

Getting the Profile option value in SQL:

Value := fnd_profile.value('Profile option name');