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.
–The conventional path loader essentially loads the data by using standard ‘insert’ statement.
— the direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.
My data.csv file
1001, “scott tiger”,1000,40
Into table emp
Fields terminated by “,” optionally enclosed by ‘”’
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 = <Directory name>
–remember to update INITSID.ORA,
–utl_file_dir = ‘c:\oradata’
Fp := utl_file.fopen(c:\oradata’,tab1.txt’,’w’);
Utl_file.putf(fp,’%s %s \n ‘text field’, 55);
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)
For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.
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 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 stores information about customer sites.One customer account can have multiple sites. The address is maintained in HZ_LOCATIONS.
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 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 stores information about physical locations.
HZ_PERSON_PROFILES stores detail information about people.
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?
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.
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.
A customer level profile must exist in A_CUSTOMER_PROFILES_INTERFACE for new customers and each bill–to business purpose.
This table stores telephone numbers for customers, addresses and contacts as well as contacts for customers and addresses.
This table stores bank information for a customer or for a specific Bill–To address, you must enter a bank account for this customer,
To import payment methods for customers and bill–to business purposes,
10) What r the staging tables in the customer interface?
11)Tell me some mandatory columns in the customer interface tables?
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
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
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?
- First we will get the Requirement document.
- We will create control file based on that plot file.
- Then the control files which loads the data into staging tables.
- Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
- Through the standard programs we will push the data from interface tables to Base tables.
21. What validations u did in the customer interface?
- customer name : the same customer reference can’t have different customer names with in this table
- 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
- customer status : must be ‘A’ for active or ‘I’ for inactive
- 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
- 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
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?
FND_CONCSUB(can submit conc program in host invironment)
24. Tell me some API?
Is the Program exits, delete conc program and its executables.
IF FND_PROGRAM.PROGRAM_EXITS(‘EMP’,APPLICATION_NAME_IN) THEN
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
26. Oracle E-Business suite?
Oracle apps + analytical components software.
(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.
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
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?
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
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
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 <your form>.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?
- 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
- Open the .rdf file in Report builder and change the name of the module.
- Open the data module and modify the query (what is client requirements) assign the columns to the attributes.
- Go to report wizard and select, what r the newly created columns.
- 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.
- Then Register in the AOL Concurrent àexecutable.
Concurrent à program.
- 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?
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.