Sunday, December 29, 2013

API to validate a IBAN in Oracle Apps R12 - CE_BANK_AND_ACCOUNT_VALIDATION















In this post, I tried to give a sample execution script for the private API CE_BANK_AND_ACCOUNT_VALIDATION.VALIDATE_IBAN

It can be used to validate whether the given bank account number is an IBAN number or not. It was very helpful for us in the current project. Hence, thought to share. 

SCRIPT:


SET SERVEROUTPUT ON:

DECLARE
   lv_iban_out        VARCHAR2 (200);
   lv_return_status   VARCHAR2 (200);
   p_account_no       VARCHAR2 (35)  DEFAULT 'FI1234567890123';
BEGIN
   ce_bank_and_account_validation.validate_iban
                            (p_iban               => p_account_no,
                             p_iban_out           => lv_iban_out,
                             x_return_status      => lv_return_status
                             );

   IF lv_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line ('Bank Account Type : IBAN');
   ELSE
      DBMS_OUTPUT.put_line ('Bank Account is not a IBAN number');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);
END;




Sunday, December 29, 2013 by Team search · 0

Sunday, December 22, 2013

Java Stored Procedure in ORACLE PLSQL – A simple example















In this post, I tried the below,
  1. Create a simple class via PLSQL
  2. Verify the same in DB_OBJECTS
  3. Create a simple procedure to Invoke the member function of the class created above
  4. Test the script
  5. Drop the Java class created 

Create a simple class via PLSQL

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "HelloWorldApp" AS
class helloworldapp {
    public static void sayhello() {
        system.out.println("hello world!!! this is www.shareoracleapps.com");
    }
};


Verify the same in DB_OBJECTS


Create a simple procedure to Invoke the member function of the class created above

CREATE OR REPLACE PROCEDURE test_hello_world
AS
   LANGUAGE JAVA
   NAME 'HelloWorldApp.sayHello()';

Test the script

SET SERVEROUTPUT ON;
CALL DBMS_JAVA.set_output(2000);
CALL test_hello_world();

Drop the Java class created 




I hope this helps for beginners.


Sunday, December 22, 2013 by Team search · 0

Monday, September 9, 2013

Query to find Profile Option Details at all levels













 

SELECT p.profile_option_name short_name,
       n.user_profile_option_name NAME,
       DECODE(v.level_id,
              10001,'Site',
              10002,'Application',
              10003,'Responsibility',
              10004,'User',
             'UnDef'
             ) level_set,
       v.level_value level_val,
       v.profile_option_value VALUE
  FROM fnd_profile_options p,
       fnd_profile_option_values v,
       fnd_profile_options_tl n
 WHERE p.profile_option_id = v.profile_option_id(+)
   AND p.profile_option_name = n.profile_option_name
   AND UPPER(n.user_profile_option_name)LIKEUPPER('%&ProfileName%');

Monday, September 9, 2013 by Team search · 3

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.