Solution : profile EDR:Enable E-Record and E-Signature set to Yes
ERP Oracle Application(APPS) 11i , R12 and Fusion Cloud R13 (Functional, Technical And DBA)
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;
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
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;
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
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
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.
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
Subscribe to:
Posts (Atom)