Wednesday 12 June 2013

How to find out Apps Instance Login URL from database

Just login to your apps database for DEV/UAT/PROD and run the following query to see corresponding Apps Login URL.


                              SELECT home_url
                                   FROM icx_parameters;

Tuesday 11 June 2013

Easiest way to convert Amount in Word

Here :p_amount is taken as parameter.

1. FOR ORACLE APPS USERS

   SELECT    'Rupees '
       || ap_amount_utilities_pkg.ap_convert_number (TRUNC (:p_amount))
       || ' And '
       || ap_amount_utilities_pkg.ap_convert_number (ROUND (  (  :p_amount
                                                               - TRUNC
                                                                    (:p_amount)
                                                              )
                                                            * 100
                                                           )
                                                    )
       || ' Paise.' amt_in_word
   FROM DUAL;

Note :-  Maximum amount allowed is ( 10 power 12 ) before as well as after decimal.

2. FOR NON ORACLE APPS USERS

  SELECT    'Rupees '
       || TO_CHAR (TO_DATE (TRUNC (:p_amount), 'J'), 'jsp')
       || ' And '
       || TO_CHAR (TO_DATE (ROUND ((:p_amount - TRUNC (:p_amount)) * 100),
                            'J'),
                   'jsp'
                  )
       || ' Paise.' amt_in_word
  FROM DUAL;

Note :- Amount should not exceed 537348

Thursday 6 June 2013

HOW TO CREATE ACCOUNT ALIAS


Before creating Account Alias account field in a form is looks like the following





















Now to create Account Alias following setups are required.
First of all set the profile option 'Flexfields:Shorthand Entry' as 'All Entries'.
Then select Aliases under key flex field menu. Query the accounting structure. Max size is 20. Give a Prompt name. Now give the Alias Name, Account combination, Description, Effective from as below.












































Now save the record and it will be compiled automatically.

Now query the Account structure again from Key flexfield Segments and Compile it again.





















Set up Part ends here.To check the effect you can select any account field in a form like below. Select the alias name then click on OK.


GL Account code and Its description

If CODE_COMBINATION_ID is known then use these FUNCTIONS.
Note :- Here CCID is passed as parameter which gives the value of CODE_COMBINATION_ID.

FOR ACCOUNT CODE

function CF_accountFormula return VARCHAR2 is
v_account VARCHAR2(1000);
v_ccid number;
v_ca_id NUMBER;

begin
    select distinct CHART_OF_ACCOUNTS_ID,
          CODE_COMBINATION_ID
   into  v_ca_id,
         v_ccid
   from  gl_code_combinations_kfv
   where CODE_COMBINATION_ID=:CCID;
 jai_cmn_gl_pkg.get_account_number(v_ca_id, v_ccid, v_account);

RETURN(v_account);
exception
    when others then
    RETURN(NULL);   
end;

FOR ACCOUNT DESCRIPTION

function CF_ACCOUNT_DESCFormula return Char is
v_account VARCHAR2(1000);
v_ccid number;
v_ca_id   number;
begin
   select distinct CHART_OF_ACCOUNTS_ID,
          CODE_COMBINATION_ID
   into  v_ca_id,
         v_ccid
   from  gl_code_combinations_kfv
   where CODE_COMBINATION_ID=:CCID;              
  
   v_account := xla_oa_functions_pkg.get_ccid_description
                                (v_ca_id,
                                 v_ccid
                                );
   return (v_account);
exception
     when others then return(null);
END;