Oracle Apps New Custom Form

http://apps2fusion.com/apps/21-technical/46-a-new-custom-form-in-oracle-apps

oracle Forms look and feel

.http://fdegrelle.over-blog.com/30-categorie-265996.htmlhttp://fdegrelle.over-blog.com/ext/http://sheikyerbouti.developpez.com/forms-pjc-bean/LAF/doc/new.htm

How to change HOST NAME/IP in oracle application server

http://download.oracle.com/docs/cd/B14099_19/core.1012/b13995/host.htm#CIHFGICJ

How to aviod locks

Declareis_locked boolean := false;dummy VARCHAR2(2);BeginBeginSelect null INTO dummy from mytablewhere rowid = :myblock.rowidfor update nowait;exception when Others thenIf sqlcode = -00054 Thenis_locked := True;End If; end;IF is_locked thenShowmessage('Another user is updating this record. Try again later.');raise form_trigger_failure;ElseLOCK_RECORD;If Not Form_success ThenRAISE form_trigger_failure;End If;End if;End;

http://forums.oracle.com/forums/thread.jspa?threadID=322077

How to handler special characters

CREATE OR REPLACE FUNCTION strip_special_character ( in_string IN varchar2,in_chars IN varchar2 DEFAULT NULL) RETURN varchar2 IS
TAB CHAR(1) := chr(9);
double_quote char( 1 ) := chr(34);
single_quote char( 1 ) := chr(39);
mask varchar2( 80 ) := '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
v_special_chars varchar2( 80 ) := '`~^\'double_quotesingle_quotetab;
begin
IF in_chars IS NOT NULL then
v_special_chars := in_chars;
end
IF; RETURN translate( in_string, mask v_special_chars, mask );
exception when others then RETURN in_string;
end strip_special_character;

How to remove Unnecessary Spaces

CREATE FUNCTION remove_unwanted_spaces( in_value IN VARCHAR2 )
RETURN VARCHAR2
AS
v_result VARCHAR2(32767);
BEGIN
IF( in_value IS NOT NULL ) THEN
FOR i IN 1 .. ( LENGTH(in_value) - 1 ) LOOP
IF( SUBSTR( in_value, i, 2 ) <> ' ' ) THEN
v_result := v_result SUBSTR( in_value, i, 1 );
END IF;
END LOOP;
v_result := v_result SUBSTR( in_value, -1 );
END IF;
RETURN v_result;
END;

Tutor for ADF developers

http://www.docstoc.com/docs/6245714/Oracle-ADF-Tutorial

ORACLE APPS TRAINING, STRUCTUR AND PRACTIVE

Get registred on http://vis1211.solutionbeacon.net for the practive of Oracle ERP.
We can findout lot of materail for Oracle ERP http://etrm.oracle.com by getting registration on it.

SQL%NOTFOUND

Don't use this inside the for loop LIKE FOR
FOR i IN (SELECT sch_id,CURDATE FROM ORDERS)
LOOP
DON'T USE(EXIT WHEN SQL%NOTFOUND);

Use of NOCOPY with Oracle Function and Procedures.

• NOCOPY will instruct Oracle to pass the argument as fast as possible. This can significantly enhance performance when passing a large value.

• Ussually IN paramter is pass by value and OUT/IN OUT are pass by refererence....

If one wants to send the IN paramter too as pass by reference he could add NOCOPY
parameter....

advantage is always less memory usage

disadvantage is when there is a change happened to the Reffered value and a rollback occurs in the procedure the change of value would be retained.... it could not be rolled back....

so I beleive that it is always safe to work without the NOCOPY parameter.....

UPDATE QUERY WITH TABLE JOINS IN ORACLE

We can write this query as an example given follows
Update(Select location loc, city city from Employee emp, Address addr where emp.empid = addr.empId and emp.location is null) SET loc = city

Barcode on Oracle Reports

http://www.ozgrid.com/Services/oracle-reports-barcode.htm

Digit only in oracle

FUNCTION DIGIONLY(VAL VARCHAR) RETURN BOOLEAN IS
incre number(10) := 0;BEGIN --loop for checking the every elment in of text field enter by user. LOOP incre := incre + 1; IF substr(trim(VAL),incre,1) not in ('0','1','2','3','4','5','6','7','8','9') then message('Please enter numeric value'); return false; end if; EXIT When LENGTH(trim(VAL)) <= incre; END loop;
return true; EXCEPTION WHEN OTHERS THEN message('Please enter numeric digit only value'); RETURN FALSE; END;

Secure Web.Show_Document() calls to Oracle Reports

http://www.oracle.com/technology/products/forms/pdf/10gr2/frmwebshowdoc_rep_10gr2.pdf

ANY SOME AND ALL in Oracle

ANY SOME AND ALL

ANY or SOME:

Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to FALSE if the query returns no rows.

Using Any

select ename, sal from emp where sal > any (1600, 2999);
Alternatively

select ename, sal from emp where sal > 1600 or sal > 2999;

ALL:

Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Evaluates to TRUE if the query returns no rows.

Using All

select ename, sal from emp where sal > all (1600, 2999);

Alternatively

select ename, sal from emp where sal > 1600 and sal > 2999;

DECODE VS CASE in SQL

DECODE
It used in select state for the comparing values return by select statement.

Case
It is work look like Decode but it brought some power feature.
1. DECODE only work with select statement while CASE work in select and function/procedure for as passing parameters.
2. DECODE is poor in performance while CASE is efficient.
3. DECODE can't include any other select statement in it body! While CASE can do it.

IN VS EXIST IN ORACLE

1.
To determine which clause offers better performance in rule-based optimization, consider how many rows the inner query will return in comparison to the outer query. In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.
2.
The IN and EXISTS are logically the same. The IN clause compares values returned by the subquery and filters out rows in the outer query; the EXISTS clause compares values and filters out rows inside the subquery. In the case of NULL values, the resulting set of rows is the same.

Cursors in oracles

declare
cursor testv is
select code_combination_id,account_type
from gl_code_combinations;
Type rec_gl_periods is Record(ccid gl_code_combinations.code_combination_id%Type,
descr gl_code_combinations.account_type%Type
);
recs rec_gl_periods;
testrow testv%rowtype;
begin
open testv;
loop
fetch testv into testrow;
exit when testv%notfound;
message (testrow.code_combination_id);
message(testrow.account_type);

end loop;
null;
end;

Attaching reports with Forms

DECLARE
PL_ID PARAMLIST;
BEGIN

PL_ID := GET_PARAMETER_LIST ('report_plist');
IF NOT ID_NULL(PL_ID) THEN
-- this is just to make sure it clean
DESTROY_PARAMETER_LIST ('report_plist');
END IF;

PL_ID := CREATE_PARAMETER_LIST('report_plist');
Add_Parameter(pl_id,'DESTYPE',TEXT_PARAMETER,'SCREEN');
--Add_Parameter(pl_id,'DESNAME',TEXT_PARAMETER,'printer name');
--Add_Parameter(pl_id,'ORIENTATION',TEXT_PARAMETER,'LANDSCAPE');
--add_parameter (pl_id, 'BATCHID',text_parameter,'124');
--add_parameter (pl_id, 'COMPNAME' , text_parameter ,'Company Name' );
Add_Parameter(pl_id,'PARAMFORM',TEXT_PARAMETER,'NO');
RUN_PRODUCT (REPORTS,'c:\repo\manualque.rdf',SYNCHRONOUS,RUNTIME,FILESYSTEM, pl_id,NULL);
DESTROY_PARAMETER_LIST ('report_plist');
END;

Ledger's Opening Balancs

Select period_name,
period_year,
period_num,
to_char(start_date,'yyyy/mm/dd') ||' 00:00:00'
into lv_period_name,
ln_period_year,
ln_period_num,
lv_start_date
from gl_periods
where upper(period_set_name) = 'Dec-95'
and adjustment_period_flag <> 'Y'
and :p_date_from between (to_char(start_date,'yyyy/mm/dd') ||' 00:00:00') and (to_char(end_date,'yyyy/mm/dd') ||' 00:00:00') ;

--- Getting Op Bal for the period

Select sum(nvl(gb.begin_balance_dr,0)-nvl(gb.begin_balance_cr,0))
into ln_month_op_bal
from gl_Balances gb,
gl_code_combinations gcc
where gb.period_name = lv_period_name
and gb.code_combination_id = gcc.code_combination_id
and gb.code_combination_id = :ccid
and gb.Ledger_id = :p_sob
and gb.currency_code = 'PKR'
and gb.actual_fLag = 'A' ;


Select nvl(sum(nvl(jgl.accounted_dr,0) - nvl(jgl.accounted_cr,0)),0)
into ln_amt
from gl_je_headers jgh ,
gl_je_lines jgl
where
jgh.Ledger_id = :p_sob
and jgh.Ledger_id =jgl.ledger_id
and jgh.status = 'P'
and jgh.actual_flag = 'A'
and jgh.je_header_id = jgl.je_header_id
and jgl.code_combination_id = :ccid
and ((to_char(jgh.default_effective_date,'yyyy/mm/dd') ||' 00:00:00') >= lv_start_date
and (to_char(jgh.default_effective_date,'yyyy/mm/dd') ||' 00:00:00') < :p_date_from) ;

return nvl(ln_amt,0) + nvl(ln_month_op_bal,0) ;

Exception
when no_data_found then
return 0 ;
when too_many_rows then
return 0 ;
when others then
return 0 ;