Monday, December 19, 2011

How to make an Oracle Report Type concurrent program produce an excel friendly output?


Comma can be concatenated between the column values, however a better option is to create tab delimited file, as it takes care of commas within the string.

For this, use SQL similar to below in the report
select 'a' || chr(9) || 'b' from dual;

Can you do fnd_request.submit_request from SQL Plus/Toad in Oracle?


You will need to initialize the global variables first using

fnd_global.apps_initialize(user_id => :user_id,
resp_id => :resp_id,
resp_appl_id => :resp_appl_id,
security_id => :security_id,
server_id => :server_id);

By doing the above, your global variables upon which Concurrent Managers depend upon will be populated. This will be equivalent to logging into Oracle Apps and submitting the concurrent request from a responsibility.

How do you link a Host type concurrent program to Concurrent Manager?


Assuming your executable script is LOADPO.prog, then use the commands below
cd $XXPO_TOP/bin
ln -s $FND_TOP/bin/fndcpesr $XXPO_TOP/bin/LOADPO

How to make concurrent program end with warning?


If the concurrent program is of type PL/SQL, you can assign a value of 1 to the retcode OUT Parameter.

For a Java Concurrent program, use the code similar to below
ReqCompletion lRC;
//get handle on request completion object for reporting status
lRC = pCpContext.getReqCompletion();
lRC.setCompletion(ReqCompletion.WARNING, "WARNING");

In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?


You will need to do the below steps
Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
Enter below text( change colours as needed)


cd $OA_HTML/cabo/styles/cache
Take a backup of all the css files.
Delete all the files of following pattern oracle-desktop*.css
The idea here is to delete the cache. Next time when you logon to Oracle Apps Self Service, the Framework will rebuild the css file if found missing for your browser.

In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?

Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Which table is used to provide drill down from Oracle GL into sub-ledger

GL_IMPORT_REFERENCES column GL_SL_LINK_ID

Can we define cursor inside package if so how to call cursors inside the package?

Yes, we can. call using packagename.cursorname.
Ex:  OPEN XX_INVOICE_VAL_PKG.c_eligible_invoice
       LOOP XX_INVOICE_VAL_PKG.c_eligible_invoice..

       END LOOP;

When you query Multi-Org tables in SQL*Plus or any tool (like TOAD), we get zero rows, though actually those tables got data? What is the reason? What do you do then?

Nee to set org context to the current user session.
Single-org:
Execute mo_global.set_policy_context('S',&org_id);
Multi-org:
Begin
FND_GLOBAL.apps_initialize(
   l_user_id,    -- User id
   l_resp_id,    -- Responsibility Id
   200);           -- Application Id
MO_GLOBAL.init('SQLAP');
End;

How do you stop inserting records after 50 records are inserted from flat file through sql*loader


This can be done using option LOAD

OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
    item_number     "trim(:item_number)"
  , vendor_name "trim(:vendor_name)"
  , vendor_site_name "trim(:vendor_site_name)"
  , supplier_item   "trim(:supplier_item)"
  , process_flag  Constant 'UNPROCESSED'
)

In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows..

sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5  skip=1

Can we set Index for primary key? If so what is the name of the Index?


Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. For example, if you create a table foo as follows:

create table foo (a int primary key,
                          b varchar(20) unique);

Oracle will automatically create one index on foo.a and another on foo.b. Note that you cannot drop indexes for UNIQUE and PRIMARY KEY attributes. These indexes are dropped automatically when you drop the table or the key constraints (see the section on Constraints).

To find out what indexes you have, use
    select index_name from user_indexes;

USER_INDEXES is another system table just like USER_TABLES. This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. You might also see weird names of the indexes created by Oracle for UNIQUE and PRIMARY KEY attributes, but you will not be able to drop these indexes.

How do you track a Request Set? If an error occurs in the first program in a Request Set, I need to control the execution of other programs in the Request Set, How do you achieve this?

While creating Request Set, you can select execution sequance as Parallel or Sequancially. In this case u select as Sequance, so one fails other wont be exceuted.

How will you debug your reports?

SRW.MESSAGE (msg_number NUMBER, msg_text CHAR);

Decode vs Case

Example for Decode and Case:

SQL> SELECT ename,
  2         DECODE(deptno, 10, 'ACCOUNTING',
  3                        20, 'RESEARCH',
  4                        30, 'SALES',
  5                        40, 'OPERATIONS',
  6                            'UNKNOWN') AS department
  7    FROM emp
  8   WHERE rownum < 4
  9  /

SQL> SELECT ename,
  2         CASE deptno
  3           WHEN 10 THEN 'ACCOUNTING'
  4           WHEN 20 THEN 'RESEARCH'
  5           WHEN 30 THEN 'SALES'
  6           WHEN 40 THEN 'OPERATIONS'
  7           ELSE
  8                        'UNKNOWN'
  9         END AS department
 10    FROM emp
 11   WHERE rownum < 4
 12  /

Difference:
1.
DECODE works with expressions which are scalar values.
CASE can work with predicates and subqueries in searchable form

SQL> select ename,
  2   case
  3     when ename in ('KING','SMITH','ALLEN') then
  4        'Managers'
  5     when exists (select 1 from dept where deptno = emp.deptno and deptno = 10) then
  6        'Guy from 10th'
  7     else
  8        'Another person'
  9   end blah_blah
 10  from emp  
 11  /

2.
CASE executes faster in the optimizer than does DECODE.

3.
DECODE will return "true" if you compare NULL to NULL. CASE will not.

DECODE(NULL, NULL, 1, 0)
will return '1'.

CASE NULL
    WHEN NULL THEN 1
    ELSE 0
END
will return '0'.

You would have to write it as:
CASE
    WHEN NULL IS NULL THEN 1
    ELSE 0
END
will return '1'

4.
DECODE can be used Only inside SQL statement....
But CASE can be used any where even as a paramtre of a function/procedure

SQL> Declare
  1  a varchar2 := 'ONE';
  2  Begin
  3    procedure_01(case :a when 'ONE' then 1 else 0 end);
  4  End;
  5  /
The number  = 1

What is the location for concurrent request or manager log files and output files?

$APPLCSF


What is $FLEX$ and $PROFILES$?

$FLEX$ and $PROFILES$ are Special Variables in oracle Apps environment and are used to hold values at runtime. Whenever we are working with value sets, we will be using both of these for modifying the data fetched, for basing the values of a parameter on the other parameter and also fetching the Profile Option values.

To segregate this based on the functionality
$FLEX$: Used for basing the value of a parameter on another parameter.
$PROFILES$: used for fetching the data stored in the specified profile option value which is currently active.

Where is it used?

Both these variables are used in the Parameter form of a Concurrent Program and are used at the Where Clause window in the value set of Table type.

Syntax:

:$FLEX$.previous_value_set_name

Important:

    * $FLEX$ must always be in capitals.
    * A ':' must precede the declaration of $FLEX$.
    * The previous value set name must have already been assigned and saved on a different parameter.


:$PROFILES$.Profile_option_name

Important:

    * $PROFILES$ must be always in capitals.
    * ':' must always precede the declaration.
    * Profile option name is the Profile Name and not to be confused with the User profile Name.