Wednesday, 3 July 2013

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; 


Thursday, 23 May 2013

HOW TO SHRINK A TABLE IN ORACLE

--------------- How to create table to another table reference

create table test121 as select * from fnd_user;


select count(1) from test121;

select * from test121;
------------------Insert data to anoter table reference:


insert into test121  select * from fnd_user;

------------------Check the table size

select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='TEST121';


delete from test121;


alter table test121 enable row movement;


 alter table test121 shrink space;


select segment_name,bytes/1024/1024 as mb from user_segments


------ take table backup

create table test_bak as select * from test1

Sunday, 19 May 2013

"Internal Requisition Order Type" Lov Does Not Have All Order Types

ISSUE:  
Transaction Types have been defined  in Order Management and are not found in the Order type list of values on the Purchasing Options Internal Requisition tab.  The list of values for Order type is missing some values.

REASON:
Irregular population of the po_required_flag and agreement_required_flag in oe_transaction_types (
TRANSACTION TYPES).When the form is saved without checking off the boxes.. the associated column value was saving as NULL instead of N. The Order type LOV to fetch the transaction type does not have an nvl condition.

Per bug 5904753 IRREGULAR POPULATION OF ORDER TYPE LOV IN PURCHASING OPTIONS

SOLUTION TRICKS: 

1. states profile option OE: Set of Books should be set
before the order types are defined in Order Management.
Set the profile option OE: Set of Books and set the profile value as necessary. Save the changes. 

2. Query each Transaction Type to be used for Internal Requisitions toggle the check box value for both PO_REQUIRED and AGREEMENT_REQUIRED  ( check and save, uncheck and save) . The intent is to set the value to N for both PO_REQUIRED and AGREEMENT_REQUIRED the box is unchecked and saved as the last action for this step. 

3. Retest the issue