Apps Interview Ques and Ans

1) What is ERP? Architecture of apps?

A packaged business software system that lets a company automate and integrate the majority of its business processes; share common data and practices across the enterprise; [and] produce and access information in a real-time environment.

2) Tell me some thing about SQL-LOADER.

Sql * loader is a bulk loader utility used for moving data from external files into the oracle database.

Sql * loader supports various load formats, selective loading, and multi-tables loads.

1) conventional

–The conventional path loader essentially loads the data by using standard ‘insert’ statement.

2) direct

— the direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.

EX:-

My data.csv file

1001, “scott tiger”,1000,40

1002,”gvreddy”,2345,50

Load data

Infile ‘c:\data\mydata.csv’

Into table emp

Fields terminated by “,” optionally enclosed by ‘”’

(empno, empname,sal,deptno)

>sqlldr scott/tiger@vis

control=loader.ctl  log= gvlog.log  bad=gvbad.bad  discard=gvdis.dsc .

3)  how do u dump data from pl/sql block to flat files?

Using utl_file package, we can dump data from pl/sql block to flat file.

PRE-REQUIREMENTS for UTL_FILE is specify the accessible directories for the UTL_FILE function in the initialization file (INIT.ORA) Using the UTL_FILE_DIR parameters.

Ex: UTL_FILE_DIR =

EX:-

–remember to update INITSID.ORA,

–utl_file_dir = ‘c:\oradata’

Declare

Fp utl_file.file_type;

Begin

Fp := utl_file.fopen(c:\oradata’,tab1.txt’,’w’);

Utl_file.putf(fp,’%s %s \n ‘text field’, 55);

Utl_file.fclose(fp);

End;

4) What is SET-OF-BOOKS?

Collection of Chat of Accounts and Currency and Calendars is called SOB

5)     What is the interface?

Interface Table is a table which is used as medium for transfer of data between two systems.

6)     What is invoice?

Send you a request for payment

7)     What is INBOUND and OUT BOUND? (Different types of interfaces)

Inbound Interface:

For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.

Outbound Interface:

For outbound interfaces, where these products are the source, database views are provided and the destination application should provide the validation, processing, and maintenance programs

8)      Tell me what r the Base tables in the AR?

hz_parties      (party_id)     (store info about org, groups and people)

HZ_PARTIES stores information about parties such as organizations, people, and groups, including the identifying address information for the party.

 hz_cust_accounts (cust_account_id)

HZ_CUST_ACCOUNTS stores information about customer relationships. If a party becomes a customer, information about the customer account is stored in this table. You can establish multiplecustomer relationships with a single party, so each party can have multiple customer account records in this table.

 hz_cust_acct_sites_all  (cust_acct_site_id)

HZ_CUST_ACCT_SITES_ALL stores information about customer sites.One customer account can have multiple sites. The address is maintained in HZ_LOCATIONS.

 hz_cust_site_uses_all   (site_use_id)

HZ_CUST_SITE_USES_ALL stores information about site uses or business purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table.

hz_party_sites (party_site_id)

HZ_PARTY_SITES stores information about the relationship between Parties and Locations. The same party can have multiple party sites.Physical addresses are stored in HZ_LOCATIONS.

hz_locations (location_id)

HZ_LOCATIONS stores information about physical locations.

hz_Person_Profiles (person_profile_id)

HZ_PERSON_PROFILES stores detail information about people.

hz_Organization_Profiles (organization_profile_id)

HZ_ORGANIZATION_PROFILES stores credit rating, financial statistics, socioeconomic and corporate linkage information for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.

9)      What r the table’s interface tables in the customer interface tables?

1)     Ra_customers_interface_all

This table stores customer, address, and business purpose information. You do not have to enter values in this table if you do not want to import customers, addresses, or business purposes.

 ADDRESS1 through 4

Enter the address for your customer in these four columns. You can enter up to four lines of an address.

Validation:

If you enter a value in ORIG_SYSTEM_ADDRESS_REF, you must enter a value in ADDRESS1. For multiple rows with the same address reference, insert values in address 1–4.

Destination:                      HZ_LOCATIONS.ADDRESS1,

HZ_LOCATIONS.ADDRESS2,

HZ_LOCATIONS.ADDRESS3,

HZ_LOCATIONS.ADDRESS4

2) Ra_customer_profile_int_all

A customer level profile must exist in A_CUSTOMER_PROFILES_INTERFACE for new customers and each bill–to business purpose.

3) Ra_contact_phones_int_all

This table stores telephone numbers for customers, addresses and contacts as well as contacts for customers and addresses.

4) Ra_customer_banks_int_all

This table stores bank information for a customer or for a specific Bill–To address, you must enter a bank account for this customer,

5) Ra_cust_paymethod_int_all

To import payment methods for customers and bill–to business purposes,

10) What r the staging tables in the customer interface?

Ra_customers_stg

Ra_customers_address_stg

Ra_customers_point_stg

Ra_customers_contact points_stg

Ra_customers_relate_stg

Ra_customer_error.

11)Tell me some mandatory columns in the customer interface tables?

a) Ra_customers_interface_all

1) Orig_system_customer_ref

2) insert_update_flag

3) customer_number

4) customer_status

5)last_updated_by

6) last_updated_date

7)created_by

8) creation_date.

b) Ra_customer_profile_int_all

1) customer_profile_class_name

c) Ra_contact_phones_int_all

1)orig_system_telephone_ref

2)telephone

3) telephone_type

         d) Ra_customer_banks_int_all

1) bank_a/c_name

2)bank_a/c_no

3)bank_a/c_currency_code

e) Ra_cust_paymethod_int_all

1) payment_method_name

12)Tell me the Navigation for customer interface?

Receivables à Interface à Customers

13. What tables cannot be updated through customer interface?

No updates will be allowed to be made through customer interface for

Following tables

RA_SITE_USES_ALL

RA_CUSTOMER_RELATIONSHIPS_ALL

RA_CUST_RECEIPT_METHODS

AP_BANK_BRANCHES

AP_BANK_ACCOUNTS_ALL

AP_BANK_ACCOUNT_USES_ALL

14. How to send additional customer and address information through customer interafce 

Here is where attribute columns are used.

* Additional Customer data can be populated in

RA_CUSTOMERS_INTERFACE_ALL.customer_attribute1 to 15

This will go into RA_CUSTOMERS.attribute1 to 15

* Additional address information can be populated in

RA_CUSTOMERS_INTERFACE_ALL.address_attribute1 to 15

This will go into RA_ADDRESSES_ALL.attribute1 to 15

And so on.

15. What should be the batch size (number of customer records) general guidelines for optimal performance

About 10,000 records per bacth is ideal, it is suggested to keep the batch

size small.

   16. Why does customer interface error out if there is mismatch in address information?

Because the code validates against these mismatches.

arplscin.sql and arplbcin.sql 

It validates the address being inserted or updated with respect to the tax

location flexfield structure.  For each row being inserted or updated in

RA_CUSTOMERS_INTERFACE, and that has not already been marked in error,

the set_location_ccid function is called to return either an existing ccid

for the address entered or to create a new entry in AR_LOCATION_VALUES for

this new address.  The RA_CUSTOMERS_INTERFACE record is then updated with the

value of the ccid returned.

17. How do you send records at customer level profile and address/site level profile?

For every customer record in RA_CUSTOMERS_INTERFACE_ALL, insert two records  in table RA_CUSTOMER_PROFILES_INT_ALL.    (Refer Note: 1070800.6 )

18. Does Customer Interface import salesperson data?

No, Refer Enhancement Bug: 147495

19. Customer Interface process can be used for updating the customer information How does the UPDATE work?

Current functionality of Customer Interface is to update all the data.  You cannot run Customer Interface to update only changed data. (Refer Bug: 879121 for the intended functinality)

20 .   Tell me what is the procedure to develop an interface?           

  1. First we will get the Requirement document.
  2. We will create control file based on that plot file.
  3. Then the control files which loads the data into staging tables.
  4. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
  5. Through the standard programs we will push the data from interface tables to Base tables.

21.     What validations u did in the customer interface?

  1. customer name : the same customer reference can’t have different customer names with in this table

HZ_PARTIES.PARTY_NAME

  1. customer number : must be null if your r using automatic customer numbering, must exit if you are not using automatic customer numbering. This value much be unique with in HZ_PARTIES
  2. customer status : must be ‘A’ for active or ‘I’ for inactive

HZ_PARTIES_STATUS

  1. bank account num or bank account currency code :

if the bank a/c already exist do not enter a value

if the bank a/c does not exist  you must enter a value

  1. bank a/c name : it must exist in AP_BANK_ACCOUNTS or if it does not exist values must exist for BANK_A/C_CURRENCY_CODE

BANK_A/C_NUM

BANK_NAME

BANK_BRANCH_NAME

Note : every interface table has two error msg

1)      Error code.

2)      Error msg.

22.      How can u call a standard interface program from sql or pl/sql code?

FND_REQUEST.SUBMIT_REQUEST (‘PO’,’EXECUTABLE NAME’,,,,PARAMETERS)

23.      API’s FOR CUSTOMER INTERFACE?

HZ_CUST_A/C_VZPUB.UPDATE_CUST_A/C

HZ_CUST_A/C_VZPUB.CREATE_CUST_A/C

FND_PROFILES

FND_APPLICATIONS

FND_GLOBAL

FND-FILE

FND_CONCSUB(can submit conc program in host invironment)

24.      Tell me some API?

FND_FILE.PUTLINE(FND_FILE.LOG)

FND_FILE.PUTLINE(FND_FILE.OUTPUT)

Is the Program exits, delete conc program and its executables.

IF      FND_PROGRAM.PROGRAM_EXITS(‘EMP’,APPLICATION_NAME_IN) THEN

FND_PROGRAM.DELETE_PROGRAM(‘EMP’,APPLICATION_NAME_IN)

FND_PROGRAM.DELETE_EXECUTABLE(‘EMP’,APPLICATION_NAME_IN)

END;

25.  What are profile options?

Is the Functional and Technical behavior of Oracle Applications Package.

EX: – I want to assign the user3 responsibility to p4 printer then

System Administrator àProfile àSystem

                                    (FND_PROFILE_OPTIONS)

26.  Oracle E-Business suite?

Oracle apps + analytical components software.

(Oracle discover)

(Oracle sales analyzer)

(Oracle financial analyzer)

(Oracle marketing analyzer)

27. What are some of the important fields that Customer Interface does not load?

Not all fields in RA_CUSTOMERS are being loaded by the interface.

SIC_CODE

GSA_INDICATOR

FOB_POINT

SALES_CHANNEL_CODE

FREIGHT_TERM

WAREHOUSE_ID

PRICE LIST

SHIP_PARTIAL

PAYMENT_TERM_ID   in RA_SITE_USES.PAYMENT_TERM_ID

(Refer Enhancement Request Bug: 245300)

28. New TCA/Customer Model, how to load customer as PERSON or ORGANIZATION through Customer Interface?

a) Populate ra_customers_interface_all . person_flag = ‘Y’

Run Customer Interface process will load this record as PERSON

b) Populate ra_customers_interface_all . person_flag = ‘N’ ( or NULL )

Run Customer Interface process will load this record as ORGANIZATION

29. What is multi org?

“Legal entity has more than one operating unit is called as multi org”

          a) Business group — Human resources information is secured by

Business group

b) Legal entity.    — inter-company and fiscal/tax reporting.

Security à responsibility à operating unit.        

c) Operating unit — secures AR, OE, AP, PA and PO Information.

d) Organizations  — is a specialize unit of work at particular locations

30. What are the User PARAMETERS in the Reports?

              P_CONC_REQUEST_ID

              P_FLEX_VALUE

31.  FND USER EXITS:-

FND SRWINIT        sets your profile option values, multiple organizations and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program.

FND SRWEXIT       ensures that all the memory allocated for AOL user exits have been freed up properly.

FND FLEXIDVAL   are used to display flex field information like prompt, value etc

FND FLEXSQL      these user exits allow you to use flex fields in your reports

FND FORMAT_CURRENCY   is used to print currency in various formats by using formula column

32. PL/SQL stored procedure parameters?

       or      what are the two parameters that are mandatory for pl/sql type concurrent program?

Procedure/function (ERRBUF   OUT

RETCODE OUT

………………….)

ERRBUF :-    Used to write the error message to log or request file.

RETCODE :- Populate log request file with program submission details info.

33) What is Value Set?

–The value set is a collection (or) container of values.

–When ever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.

— If the list of values needed to be dynamic and ever changing and define a table based values set.

 34) What r the validation types?

1) None          ——– validation is minimal.

2) Independent ——input must exist on previously defined list of values

3) Dependent    ——input is checked against a subset of values based on a

prior value.

3) Table             —– input is checked against values in an application table

4) Special          ——values set uses a flex field itself.

5) Pair              —— two flex fields together specify a range of valid values.

6) Translatable independent —– input must exist on previously defined list

of values; translated values can be used.

7) Translatable dependent ——- input is checked against a subset of values

based on a prior values; translated value can be used.

35) Form development process?

a) open template form

b) Save as .fmb

c) Change the form module name as form name.

d) Delete the default blocks, window, and canvas

e) Create a window.

f) Assign the window property class to window

g) Create a canvas   (subclass info)

h) Assign canvas property class to the canvas

I) assign the window to the canvas and canvas to the window

j) Create a data block

k) Modify the form level properties. (sub class item à Text item)

l)  Modify the app_cusom package. In the program unit.

m) Modify the pre-form trigger (form level)

n) Modify the module level properties ((console window, First navigation

p) Save and compile the form.

Place the .fmx in the server directory.

Q) Register in the AOL

                                    APPLICATION à FORM

                                    APPLICATION à FUNCTION

                                    APPLICATION à MENU

36)How do u customize the Reports?

  1. Identify the Short name of the standard report in which module we have to customize

Ex: – if u want to customize in the AR module path is

              Appl top\ar\11.5.0\reports\US\ .rdf

  1. Open the .rdf file in Report builder and change the name of the module.
  2. Open the data module and modify the query (what is client requirements) assign the columns to the attributes.
  3. Go to report wizard and select, what r the newly created columns.
  4.  Then Compile it. Then u will get a .rep file in the specified module. If it is not in the specified directory then we have to put in the server directory.
  5. Then Register in the AOL   Concurrent àexecutable.

                                                  Concurrent à program.

  1. g.      go to system administrator  SecurityàResponsibilityàrequest

h)  Add and assign a concurrent program to a request group

37.    Difference b/w procedure and function?
A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition. Function can be called in select statements but procedure can only be called in a pl/sql block.  Procedure’s parameters can have IN or OUT or INOUT parameters. But function’s parameters can only have IN parameters.
38.    Difference b/w ROWID and ROWNUM?
ROWID: It gives the hexadecimal string representing the address of a row. It gives the location in database where row is physically stored. ROWNUM: It gives a sequence number in which rows are retrieved from the database.
39.    Give some examples of pseudo columns?

NEXTVAL, CURRVAL, LEVEL, SYSDATE
40.    Difference b/w implicit cursor and explicit cursor?

Implicit cursors are automatically created by oracle for all its DML stmts. Examples of implicit cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN; Explicit cursors are created by the users for multi row select stmts.
41.    How to create a table in a procedure or function?
See the below piece of code:  Since create stmt can be used only at the sql prompt, we have used dynamic sql to create a table.

DECLARE
                    L_STMT VARCHAR2(100);
BEGIN
                    DBMS_OUTPUT.PUT_LINE('STARTING ');
                    L_STMT := 'create table dummy1 (X VARCHAR2(10) , Y NUMBER)';
                    EXECUTE IMMEDIATE L_STMT;
                    DBMS_OUTPUT.PUT_LINE('end ');
END;

The above piece of code can be written In procedure and function DDL’s can be used in function provided that function should be invoked in Begin-End block not from Select statement.

42.    Explain the usage of WHERE CURRENT OF clause in cursors?

Look at the following pl/sql code:

DECLARE
                   CURSOR wip_cur IS
                   SELECT acct_no, enter_date
                   FROM wip
WHERE enter_date < SYSDATE -7
FOR UPDATE;
BEGIN
FOR wip_rec IN wip_cur
LOOP
                   INSERT INTO acct_log (acct_no, order_date)
                   VALUES (wip_rec.acct_no, wip_rec.enter_date);

                   DELETE FROM wip
WHERE CURRENT OF wip_cur;
          END LOOP;
END;

“WHERE CURRENT OF” has to be used in concurrence with “FOR UPDATE”  in the cursor select stmt.
“WHERE CURRENT OF” used in delete or update stmts means, delete/update the current record specified by the cursor.
By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.

43.    What is the purpose of FORUPDATE?
Selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s). If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

                                                                                            
44.    What is RAISE_APPLICATION_ERROR?
The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors. Raising an Application Error With raise_application_error

DECLARE
   num_tables NUMBER;
BEGIN
   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
   IF num_tables < 1000 THEN
      /* Issue your own error code (ORA-20101) with your own error message. 
         Note that you do not need to qualify raise_application_error with 
         DBMS_STANDARD */
      raise_application_error(-20101, 'Expecting at least 1000 tables');
   ELSE
      NULL; -- Do the rest of the processing (for the non-error case).
   END IF;
END;
/

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

45.    What is mutating error?
Mutating error occurs in the following scenario:
WHEN WE ARE UPDATING A TABLE (TRIGGER WRITTEN ON A TABLE FOR UPDATE) AND AT THE SAME TIME TRYING TO RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTATING TABLE AND IT WILL RESULT INTO MUTATING ERROR.
46.    Can we have commit/rollback in DB triggers?
Having Commit / Rollback inside a trigger defeats the standard of whole transaction’s commit / rollback all together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place. If we still want to carry out some action which should be initiated from trigger but should be committed irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous transaction block we can have Commit and it will act as actual commit.

47.    Can we make the trigger an autonomous transaction?
This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist.

48.    What is autonomous transaction?
Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.

49.    What is a REF Cursor?
The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.

50.    What is the difference between ref cursors and normal pl/sql cursors?

Declare

          type rc is ref cursor;
          cursor c is
          select * from dual;
          l_cursor rc;
       begin
          if ( to_char(sysdate,'dd') = 30 ) then
                   open l_cursor
                             for select * from emp;
          elsif ( to_char(sysdate,'dd') = 29 ) then
                   open l_cursor
                             for select * from dept;
          else
                   open l_cursor
                             for select * from dual;
          end if;
          open c;
      end;

Given that block of code you see perhaps the most “salient” difference, no matter how many times you run that block The cursor C will always be select * from dual.  The ref cursor can be anything.

Advertisements