Oracle apps Technical Interview Question

 1. In sql loader how to constant some value

ANS:

infile 'c:\sandeep.txt' 

truncate

into table emp

fields TERMINATED BY ""|"" 

trailing nullcols(

column_name constant ""abc"",

)

2. table with 100000 line how you identify which column need to be indexed

Ans : 

The column is queried frequently.

A referential integrity constraint exists on the column.

A UNIQUE key integrity constraint exists on the column.

3. Gl interface program name

=> GL Journal Import Program

-parameter 

Short Name Program

Data access set id

Ledger

group id

'N' Post Error to supense 

'N' Create summary Journal

'O' Import DFF

4. Which program we need to run to post GL JV into GL

=> Journal Posting Program

5. Pragma exception 

Ans : 

PL/SQL user named exception. you can define your own error message and error number using Pragma EXCEPTION_INIT or RAISE_APPLICATION_ERROR function.

Pragma is a keyword directive to execute proceed at compile time. pragma EXCEPTION_INIT function take this two argument,

exception_name

error_number

You can define pragrma EXCEPTION_INIT in DECLARE BLOCK on your program.

Ans :Syntax and how to catch

:

DECLARE

    user_define_exception_name EXCEPTION;

    PRAGMA EXCEPTION_INIT(user_define_exception_name,-error_number);

BEGIN

    statement(s);

    IF condition THEN

        RAISE user_define_exception_name;

    END IF;

EXCEPTION

    WHEN user_define_exception_name THEN

        User defined statement (action) will be taken;

END;

6. outbound interface how to create csv file using function

Using UTL_FILE.PUT_LINE

DECLARE

CURSOR C1 IS

SELECT OOHA.ORDER_NUMBER, OOHA.FLOW_STATUS_CODE, OOHA.CREATION_DATE, OOLA.ORDERED_ITEM, OOLA.ORDERED_QUANTITY

FROM OE_ORDER_HEADERS_ALL OOHA, OE_ORDER_LINES_ALL OOLA

WHERE OOHA.HEADER_ID=OOLA.HEADER_ID

AND OOHA.SOLD_TO_ORG_ID=8428

AND OOHA.FLOW_STATUS_CODE<>'CLOSED';

V_FILE  UTL_FILE.FILE_TYPE;

V_COUNT NUMBER;

BEGIN

V_COUNT:=0;

V_FILE:=UTL_FILE.FOPEN('/usr/tmp','XXSEA_ORDER_EXTRACT.csv','W');

FOR I IN C1  

LOOP

V_COUNT:=V_COUNT+1;

UTL_FILE.PUT_LINE(V_FILE,I.ORDER_NUMBER||','||I.FLOW_STATUS_CODE||','||I.CREATION_DATE||','||I.ORDERED_ITEM||','||I.ORDERED_QUANTITY);

DBMS_OUTPUT.PUT_LINE(I.ORDER_NUMBER||','||I.FLOW_STATUS_CODE||','||I.CREATION_DATE||','||I.ORDERED_ITEM||','||I.ORDERED_QUANTITY);

END LOOP;

UTL_FILE.FCLOSE(V_FILE);

DBMS_OUTPUT.PUT_LINE('count :'||V_COUNT);

END;

7. Order management when we create delivery which table impacted

Ans : 

aPPS.wsh_new_deliveries 

APPS.wsh_delivery_assignments 

APPS.wsh_delivery_details 

8. update count , commit in before and after what will be output

Will check

9. profile option level and precedence 

- Site, Application , Responsibility and then user

10. why to use open interface or API

Ans : 

For the Usage of API , we need to study the behavior of API and how to handle it, like error records , success records and how to capture error records,how to debug etc... Needs some program practice....

For the Interface or conversion, is just like playing safe game... we need to know which tables are hitting post running the program, like if error occurred, which table it is hitting and also summary will be displayed after running the program ...

11. workflow 

=> Small customization i have work on where notification 

12. Lookup DFF setup

Create a DFF

Login to System Administrator

Application -> Flexfield -> Descriptive -> Segments

Search for Application = Application Object Library

Title = Common Lookups

You can see referenced field as "LOOKUP_TYPE"

Now if you want to enable your lookup, you need to uncheck "Freeze Flexfield Definitions" checkbox on the top left the DFF Segments screen.

this enables you to enter new DFF entries.

Now, enter create a line under Context Field Values 

Under "Code" enter your lookup ex: XXPHR_DEDUCT_ELEMENTS

Click on Segments

Enter the number, name, prompt, column and value set(if you have one) 

save and compile the DFF.

Now, switch the responsibility to Application Developer to see the DFFs enabled in lookup

Open the common lookups screen and query for the lookup name you have given under "Code" of DFF setup.

and thus, your DFF is enabled.

13. fnd load for lookup will it consider DFF

No , We have to move DFF FND Load , then if any value set attached to it and then lookup

14. xml publisher report output should be in diffrent languae

Ans :

1. Other language should be enabled on your oracle apps instance.

2. Create the concurrent program with output type as XML

3. Create Data Definition using XML Publisher Responsibility

4. Create template. At the time of creation of templete, enable Translatable check box and click Apply

5. Once template is uploaded, you will find two buttons. Export Translation and Upload Translation.

6. Click on Export Translation. XLF file will be opened. 

7. In XLF file - e. g. source-language="en-US" target-language="fr-FR".Source is English and fr is for French language and FR is territory. 

8. Update the target fields in French language. In the below example source field is in English language and target is in French language. This indicates that wherever the Invoice Date is present in RTF template it will be 

replaced by the target field in french language. 

<source>Invoice Date:</source> 

<target>Date de facture:</target> 

9. Update the same for all fields and finally import the updated translation file. 

15. 2 concurrent program are running how you will stop until first program get complete and 2nd to start

Ans : 

l_req_return_status :=

            fnd_concurrent.wait_for_request (request_id      => lv_request_id

                                            ,INTERVAL        => 5 --interval Number of seconds to wait between checks

                                            ,max_wait        => 60 --Maximum number of seconds to wait for the request completion

                                             -- out arguments

                                            ,phase           => lc_phase

                                            ,STATUS          => lc_status

                                            ,dev_phase       => lc_dev_phase

                                            ,dev_status      => lc_dev_status

                                            ,message         => lc_message

                                            );

      EXIT

    WHEN UPPER (lc_phase) = 'COMPLETED' OR UPPER (lc_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');

    END LOOP;

    --

    --

    IF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'ERROR' THEN

      dbms_output.put_line('The XX_PROGRAM_1 completed in error. Oracle request id: '||lv_request_id ||' '||SQLERRM);

    ELSIF UPPER (lc_phase) = 'COMPLETED' AND UPPER (lc_status) = 'NORMAL' THEN

      dbms_output.put_line( 'The XX_PROGRAM_1 request successful for request id: ' || lv_request_id);

      --

      --Submitting Second Concurrent Program XX_PROGRAM_2

      --

  BEGIN

  --

lv_request_id := fnd_request.submit_request ( 

                            application   => 'XXCUST', 

                            program       => 'XX_PROGRAM_2', 

                            description   => 'XX_PROGRAM_2', 

                            start_time    => sysdate, 

                            sub_request   => FALSE);

--

COMMIT;

16 what is group id in oracle GL

Ans : 

Groups lines for journals. Use positive integers. Lines with the same GROUP_ID are grouped into the same journal.

17. If one of concurrent program is running for more than 1 hours how to identfy which query is taking time ?

Ans : 

SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,

b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time

FROM v$process a, v$session b, v$sqltext c

WHERE a.addr=b.paddr

AND b.sql_hash_value = c.hash_value

AND b.STATUS = 'ACTIVE'

AND a.spid = '11696'

ORDER BY a.spid, c.piece