Oracle 11i Application Developer – Technical FAQ – Part 2


Hello Friends, here is the second set of Questions on Application Developers , more of general questions, i am not digging in particular areas , only trying to put question which i normally ask with candidates. If you want help in any specific areas , plz advise, i will try to do that.

Q11 ) What is SET-OF-BOOKS?
Collection of Chat of Accounts and Currency and Calendars is called SOB

Q12 ) What is the interface?
Interface Table is a table which is used as medium for transfer of data between two systems.

Q13 Tell me what is the procedure to develop an interface?
a. First we will get the Requirement document.
b. We will create control file based on that plot file.
c. Then the control files which loads the data into staging tables.
d. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
e. Through the standard programs we will push the data from interface tables to Base tables.

Q14) 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.
c) Operating unit — secures AR, OE, AP, PA and PO Information.
d) Organizations — is a specialize unit of work at particular locations

Q15) What are the User PARAMETERS in the Reports?
P_CONC_REQUEST_ID
P_FLEX_VALUE

Q16) 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

Q17) 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.

Q18.) 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.
n If the list of values needed to be dynamic and ever changing and define a table based values set.

Q19) What r the validation types in Value Set?
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.

Q20) Who information’s?
1) Created by
2) Creation date
3) Last _updated by
4) last_update_date

Thanks  — Shivmohan Purohit

Advertisements

Oracle Financials Accounting – Interview Technical Fucntional Questions


1. What are the application objects that support View Accounting and Drilldown?
GL_Import_Reference_Table (modified) For Example Invoices imported from Payables into GL goes to these tables from GL_Inerface table.
GL_SL_LINK_ID
GL_SL_LINK_TABLE
GL_JE_LINES (modified)

New views in the database:
FA_AEL_GL_V
FA_AEL_SL_MRC_V
FA_AEL_SL_V

2. Where in Oracle General Ledger 11i can Drilldown be accessed?

You can drilldown from GL Account Inquiry window and the GL Journal Entry and GL Journal Inquiry windows. (N) Tools -> Drilldown Open the Journal entry in GL and go to Tools – drilldown, its shows u the origin of the journal entry. It is used only for viewing the origin of the journal entry.

3. What are the Release 11i Sub-ledger drilldown features?

Expanded Subledger drilldown to other subledgers. View Accounting Lines window.

4. What are recurring invoices? What are AP setup steps?

Some times suppliers would not be sending any invoices, but still the payment have to be made to him. Ex: rent, lease rentals. In this situation we have to create invoice every period wise. For that purpose we have to create one recurring invoice template. Template means with one master copy creating the multiple invoices. Here we are creating the one invoice master copy is formally known as recurring invoice or recurring invoice template.
Setup: 1) Create one special calendar
2) Create one full distribution set
3) Enter payment terms in the recurring invoice window
4) Enter the template no., first invoice amount, special invoice amounts.
Recurring Entries are of 3 types-
1. Standard,
2. Skeleton,
3. Fornulae Based
In GL Module
1.Define Formula Batch (e.g. ABC Rent batch)
2.Enter Lines (Here u have both Debit as well as Credit lines)
3.Generate Recurring period
4.Review Journal Batch
5.Post the batch
1. Standard Recurring Journal: It is used for same accounts & same amounts e.g.
Utilities Dr
Cash Cr
2. Skeleton Recurring Journal: It is used for same accounts but for different amounts, e.g.
Recurring Fee Dr
Cash Cr
3.Formule Based Journal: It is used for different accounts with different amounts, e.g.
Salaries Dr
Cash Cr

5. If any conflict occurs in FSG who will override; Column Set or Row Set?
The override component is row set. However some times it depend on the column set also.

6. What is the difference between discounts and adjustments?
Discount refers to the payment terms or on quantity (bulk order discount) ordered. In the invoice received from supplier, discounts term is specifically stated, e.g. “If paid with in 15 days discount @ 2%”, what you need to do is pay 2% less then the original invoice amount, however caution should be taken that the freight amount should not be considered while calculating the discount. Adjustment could be for various reasons- Over priced invoice, short delivery of quantity as per goods received note, poor quality etc. Agreed upon amount will be deducted from the invoice, while making the payments. In AR, discount is given to promote the business and cash flow. Adjustments are made to adjust the account balances, which is not possible in other means.

7. What are cycles of GL, AP, and AR?
GL Accounting cycle can be further elaborated like this…
1. Open Period.
2. Create Functional and foreign journal entries.(including the journal import from legacy systems and subledgers).
3. Reverse journal entries.
4. Post the journals.
5. Review and correct the balances.
6. Revalue foreign currency balances.
7. Translate foreign currency balances.
8. Consolidate set of books.
9. Run accounting reports.
10. Close the accounting periods.

AP cycle ——– Purchase Order –> Receipt –> AP voucher –> Payment made
AR Cycle ——–Sales Order –> Shipment –> Invoice –> Payment receipt
GL cycle ——– Direct GL JE / Transfer subledger data to GL –> Post

8. What are Summary Accounts and Rollup groups?
Summary Account is an account whose balance represents consolidation of accounts. Rollup group is a collection / consolidation of parent accounts. e.g. Assets is a total of Current assets and Fixed Assets. Current assets / Fixed assets in turn are collection of assets.
Summary Account is an account whose balance represents the sum of other account balances. You can use summary accounts for faster reporting and inquiry as well as in formulas and allocations. Rollup group is a collection of parent segment values for a given segment. You use rollup groups to define summary accounts based on parents in the group. You can use letters as well as numbers to name your rollup groups.
Summary accounts are consolidated balances of accounts and rollup groups is collection of parent accounts.
9. What Sub-ledgers does Oracle General Ledger 11i Drilldown support?
Accounts Receivable, Accounts Payable and Cash Management. Drilldown from Oracle General Ledger 11i also supports Oracle Purchasing module (displays POs and Requisitions) from 11.5.9 version onwards. Drilldown from Oracle General Ledger 11i is supported for Oracle Payables, Oracle Receivables, Oracle Assets (except depreciation), Projects, Purchasing, Inventory, and Work in Process (WIP).
Shivmohan Purohit

Oracle – Technical FAQ – Part 4


 Q18.) What are the OOPS concepts in Oracle?

A18.) Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects.

 

Q19) What is the difference between candidate key, unique key and primary key?

A19) Candidate keys are the columns in the table that could be primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

 

Q20) What is concurrency?

A20) Concurrency is allowing simultaneous access of same data by different users. Locks useful for accessing the database are

a) Exclusive — The exclusive lock is useful for locking the row when an insert , update or delete is being done. This lock should not be applied when we do only select from the row.

b) Share lock — We can do the table as Share_Lock as many share_locks can be put on the same resource.

 

Q21) What are the Previleges and Grants?

A21) Privileges are the right to execute a particular type of SQL statements. The grant has to be given by the owner to object.

 

Q22) What are Table Space, Data files, parameter file, control files?

A22) Table space: The table space is useful for storing the data in the database. When a database is created two table spaces are created.

a) System Table space : This data file stores all the tables related to the system and dba tables.

b) User Table Space: This data file stores all the user related tables.

We should have separate table spaces for storing the tables and indexes so that the access is fast.

Data files : Every oracle Data base has one or more physical data files. They store the data for the database. Every datafile is associated with only one database. Once the data file is created the size can not change. To increase the size of the database to store more data we have to add data file.

Parameter file:Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g. db_block_buffers = 500

db_name = ORA7

db_domain = u.s.acme lang

Control files : Control files record the physical structure of the data files and redo log files . They contain the DB name, name and locations of dbs , data files, redo log files and time stamp.

 

Q23) What is physical storage of the Data?

A23) The finest level of granularity of the data base are the blocks.

Extent : Extent is the number of specific number of contigious data blocks.

Segments : Set of Extents allocated for Extents. There are three types of segments

a) Data Segment : Non clustered tables has data segment data of every table is stored in cluster data segment.

b) Index Segment : Each index has index segment that stores data.

c) Rollback Segment : Temporarily store ‘undo’ information.

 

Q24) What is Normalisation ? Explain 1NF, 2NF, 3NF.

Normalization is a process of eliminating data redudancy.

1NF- No repeating groups.

2NF- Eliminates the functional dependency on the partial key field.

3NF- Eliminates the functional dependency on the non-key field.

Oracle – Technical FAQ – Part 2


Q6) What are the database triggers and stored procedures?
A6) Database triggers are procedures that are automatically executed as a result of insert in, update to or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which can not be enforced using the integrity rules. We can have the trigger as before trigger or after trigger and at statement or row level.
e.g. operations insert, update, delete — 3
before, after 3 * 2 — a total of 6 combinations

At statement level (once for the trigger) or row level (for every execution) 6 * 2 — a total of 12.

Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 onwards

Stored Procedures : Stored procedures are procedures that are stored in complied form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

 

Q7) How many integrity rules are there and what are they?

A7) There are three integrity rules and they are as follows

a)Entity integrity Rule : The entity integrity rule enforces that the primary key can not be Null

b) Foreign key integrity rule : The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in child tables the master tables can not be deleted.

c) Business integrity rules : The third integrity rule is about the complex business processes which can not be implemented by the above 2 rules.

 

Q.8.) What are the various Master and Detail relationships?

A.8.) The various Master and Detail relationship are

a) Non Isolated : The Master can not be deleted when a child is existing

b) Isolated : The master can be deleted when the child is existing

c) Cascading : The child gets deleted when the master is deleted.

 

Q9) What are the various Block Coordination properties ?.

A9) The various block coordination properties are

a) Immediate – Default setting. The detail records are shown when the Master Record are shown.

b) Differed with AutoQuery — Oracle forms defer fetching the detail records until the operator navigates to the detail block.

C) Deffered with no auto query — The operator must navigate to the detail block and explicitly executes a query.

 

Q10) What are the different Optimisation techniques ?

A10) The various optimisation techniques are

a) Execute plan : We can see the plan of the query and change it accordingly based on the indexes

b) Optimizer_hint : Set_item_property (‘DeptBlock’,OPTIMIZER_HINT,’FIRST_ROWS’);

Select /* + First Rows */ Deptno, Dname, Loc, Rowid from dept

where (Deptno > 25)

c) Optimize_sql : By setting the optimize_sql = No, Oracle Forms assign a single cursor for all SQL statements. This slow downs the processing because for everytime the SQL must be parsed whenever they are executed. f45run module = my_firstform useid = scott/tiger optimize_Tp = No

 

  more questions are here : https://knoworacle.wordpress.com/2008/06/08/oracle-technical-faq-part-2/

Part 3 is here :   https://knoworacle.wordpress.com/2008/06/09/oracle-technical-faq-part-3/

Part 1 is here : https://knoworacle.wordpress.com/2008/08/11/oracle-technical-faq-part-1

 

 

 

Oracle – Technical FAQ – Part 1


Q1) What are the background processes in Oracle and what are they?
Ans: This is one of the most frequently asked question. There are basically 9 processes but in a general system we need t mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system
The various background processes in oracle are
a) Data base writer (DBWR) : Data Base Writer writes modified blocks from databas buffer cache to Datafiles. This is required since the data is not written whenever a transaction is commited.

b) LogWriter(LGWR) : LogWriter writes the redo log entries to disk. Redo log data is generated in redo log buffer of SGA. As transaction commits and logs buffer fills, LGWR writes log entries into a online redo log file.

c) System Monitor (SMON) : The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure.

d) Process Monitor (PMON) : The Process Monitor performs process recovery when user process fails. Pmon clears and frees resources that process was using.

e) CheckPoint (CKPT): At specified times, all modified database buffers in SGA are written to data files by DBWR at checkpoints and updating all data files and control files of database to indicate the most recent checkpoint.

f) Archiever (ARCH) : The archiever copies online redo log files to archival storal when they are busy.

g) recoveror (RECO) : The recoveror is used to reslove the distributed transaction in network.

h) Dispatcher (Dnnn) : The Dispatcher is useful in Multi Threaded Architecture.

i) Lckn : We can have upto 10 lock processed for inter instance locking in parallel sql.

Q2) How many types of sql statements are there in Oracle?

A2) There are basically 6 types of sql statements. They are

a) Data defination Language (DDL) : The DDL statements define and maintain objects and drop objects.

b) Data Manipulation Language (DML) : The DML statements manipulate database data

c) Transaction Control Statements : Manage change by DML

d) Session Control : Used to control the properties of current session enabling and disabling roles and changing e.g. Alter statements , Set Role

e) System Control Statements : Change properties of Oracle Instance e.g. Alter System

f) Embedded Sql : Incorporate DDL, DML and TCS in processing Languagel e.g. Using the SQL statements in language such as ‘C’, Open, Fetch, Execute and close

Q3) What is a transaction in Oracle ?

A3) A transaction is a logical unit of work that compromises one or more SQL statements executed by a simple user. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly commited or rolled back.

Q4) What are the key words used in Oracle?

A4) The key words that are used in Oracle are

a) Commiting : A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements

b) Rollback : A transaction that retracts any of the changes resulting from SQL statements in Transaction.

c) Savepoint : For long transactions that contain many SQL statements , intermediate markers or savepoints are declared. Savepointes can be used to divide a transaction into smaller points.

d) Rolling forward : Process of applying redo log during recovery is called rolling forward.

e) Cursor : A cursor is a handle (name or a pointer) for the memory associated with a specific statement. A cursor is basically an area allocated by Oracle for executing the SQL statement. Oracle uses an implicit cursor statement for single row query and uses Explicit cursor for a multirow query.

f) System Global Area (SGA) : The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance. It consists of Database buffre cache and redo log buffer.

g) Program Global Area (PGA) : The PGA is a memory buffer that contains data and control information for server process.

h) Database Buffer Cache : Database buffer of SGA stores he most recently used blcoks of database data. The set of database buffers in an instance is called Database Buffer Cache.

i) Redo log buffer : Redo log buffer of SGA stores all the redo log entries.

j) Redo log files :Redo log files are set of files that protect altered database in memory that has been not written to Data files. They are basically used for backup when a database crashes.

k) Process : A process is a ‘thread of control’ or mechanism in Operating System that executes series of steps

Q5) What are procedures, functions and packages?

A5) Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

Procedure do not return values while functions return one and only one value.

Packages provide a method of encapsulating and storing related procedures, functions and other package contents.

 more questions are here : https://knoworacle.wordpress.com/2008/06/08/oracle-technical-faq-part-2/

Part 3 is here :   https://knoworacle.wordpress.com/2008/06/09/oracle-technical-faq-part-3/

 

 

 

Oracle Forms Development (Developer 2000): Part 2


Question: What are ALERT?


Answer: An ALERT is a modal window that displays a message notifiying operator of some application condition.
Question: Can a button have icon and label at the same time?


Answer: NO
Question: What is mouse navigate property of button?


Answer: When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to move the focus to the item when the operator activates the item with the mouse.
When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulting validation) to move to the item when an operator activates the item with the mouse.
Question: What is FORMS_MDI_WINDOW?


Answer: forms run inside the MDI application window. This property is useful for calling a form from another one.
Question: What are timers? When when-timer-expired does not fire?


Answer: The When-Timer-Expired trigger can not fire during trigger, navigation, or transaction processing.
Question: Can object group have a block?


Answer: Yes , object group can have block as well as program units.
Question:  How many types of canvases are there?
Answer: There are 2 types of canvases called as Content and Stack Canvas. Content canvas is the default and the one that is used mostly for giving the base effect. Its like a plate on which we add items and stacked canvas is used for giving 3 dimensional effect.

Question: What is a visual attribute?
Answer: Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application’s interface.

Question: Diff. between VAT and Property Class?
Answer: Named visual attributes define only font, color, and pattern attributes; property classes can contain these and  any other properties. You can change the appearance of objects at runtime by changing the named visual attribute
programmatically; property class assignment cannot be changed programmatically. When an object is inheriting from both a property class and a named visual attribute, the named visual attribute settings take precedence, and any visual attribute properties in the class are ignored.

Question: Which trigger related to mouse?


Answer: When-Mouse-Click
When-Mouse-DoubleClick
When-Mouse-Down
When-Mouse-Enter
When-Mouse-Leave
When-Mouse-Move
When-Mouse-Up
Question: What is Current record attribute property?

Answer: Specifies the named visual attribute used when an item is part of the current record.
Current Record Attribute is frequently used at the block level to display the current row in a multi-record If you define an item-level Current Record Attribute, you can display a pre-determined item in a special color when it is part of the current record, but you cannot dynamically highlight the current item, as the input focus  changes.
Question: Can u change VAT at run time?


Answer: Yes. You can programmatically change an object’s named visual attribute setting to change the font, color, and pattern of the object at runtime.
Question: Can u set default font in forms?


Answer: Yes. Change windows registry (regedit). Set form45_font to the desired font.
Question: Can u have OLE objects in forms?


Answer: Yes.
Question: Can u have VBX and OCX controls in forms?


Answers: Yes.
Question: What r the types of windows (Window style)?


Answer:  Specifies whether the window is a Document window or a Dialog window.
10) What is OLE Activation style property?
10) Specifies the event that will activate the OLE containing item.
Question: Can u change the mouse pointer? How?


Answer: Yes. Specifies the mouse cursor style.  Use this property to dynamically change the shape of the cursor.

Question: What are savepoint mode and cursor mode properties? Level?


Answer: Specifies whether Oracle Forms should issue savepoints during a session. This property is included primarily  for applications that will run against non-ORACLE data sources.   For applications that will run against ORACLE, use the default setting.
Cursor mode – define cursur state across transaction Open/close.
Question: What are OPEN_FORM, CALL_FORM,NEW_FORM? Diff?


Answer: CALL_FORM : It calls the other form. but parent remains active, when called form completes the operation ,  it releases lock and control goes back to the calling form.
When you call a form, Oracle Forms issues a savepoint for the called form.  If the CLEAR_FORM function causes a rollback when the called form is current, Oracle Forms rolls back uncommitted changes to this savepoint.
OPEN_FORM: When you call a form, Oracle Forms issues a savepoint for the called form.  If the CLEAR_FORM function causes a rollback when the called form is current, Oracle Forms rolls back uncommitted changes to this savepoint.
NEW_FORM: Exits the current form and enters the indicated form.  The calling form is terminated as the parent form.  If the calling form had been called by a higher form, Oracle Forms keeps the higher call active and treats it as a call to the new form.  Oracle Forms releases memory (such as database cursors) that the terminated form was using.
Oracle Forms runs the new form with the same Runform options as the parent form.  If the parent form was a called form.
Question: What is call form stack?


Answer: When successive forms are loaded via the CALL_FORM procedure, the resulting module hierarchy is known as the call form stack.
Question: Can u port applictions across the platforms? how?
Answer: Yes, we can port applications across platforms.Consider the form developed in windows system.The form would be generated in unix system by using f60gen my_form.fmb scott/tiger

Mostly asked questions in Account Payables Modules


Hello, Some of the very much interview oriented, one of mostly asked questions in Account Payables Modules. here they are 

Questions: How many types of Invoices in Oracle Account Payables
1)Standard Invoice
2)Debit Memo
3)Credit Memo
4)Withholding tax invoice5)PO Default
6)Prepayment Invoice
7)Expense Reports
8)Recoring Invoices
9)mixed Invoices

 

  
 
 

 

 

 

Prepayment Invoice: Whenever we want make payments to the suppiler in advace that time,we create this prepayment invoice and we will make the payment.

Credit Memo,Debit Memo:Both invoices are got -ve amount,and adjusted against standard invoice.
Credit memo will be created, when ever suppiler giving discount.
Debit Memo will be created,if buyer is going to deduct the amount.
  

 

 
 
 
 

 

 

 

 

 

Question :How many key flexfields are there in Payables?

Answer: 0 (No key flexfields in PO,AP)

 

Question: Name few Account Payables Tables

AP_INVOICES_ALL
AP_INVOICE_DISTRUBUTIONS_ALL
AP_PAYMENTS_ALL
AP_PAYMENT_SCHEDULES_ALL
AP_INVOICE_PAYMENTS_ALLAP_TERMS_ALL
AP_TERM_LINES_ALL
AP_CHECKS_ALL
AP_CHECK_FORMATS
AP_HOLDS_ALL

 

 

 
 
 
 

 

 

 

 

 

Question: What is 2 way , 3 way and 4 way matching?

While creating the purchase order,we will mention the match approval level at shippments
we will have 3 types
1)two way: PO & Invoice quantities must match with in the tolerance before the corresponding invoice can be paid.
2)three way: PO, Receipt and invoice quantities must match with in the tolerance before the corresponding invoice can be paid.3)four way: PO, Receipt, Inspection and invoice quantities must match with in the tolerance before the corresponding invoice can be paid
 
 

 

 
 
 
 

 

 

 

 

 

What is a Hold? Explain the types of Hold

Invoice holds:If invoice is not approve then that invoice will be keeping under hold status.By selecting holds button in invoice form,we can see the hold details.
Select * from ap_hold_all
 
 Which interface tables are used for Invoice Import , give the important columns?
BASE TABLES:
AP_INVOICES_ALL
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_SCHEDULES_ALL
AP_INVOICE_DISTRUBUTIONS_ALLAP_INVOICES_INTERFACE COLUMNS:
Invoice_id
Invoice_num
Po_number
vendor_id

vendor_num

vendor_site_id

vendor_site_code

invoice_amount

Ap_invoice_lines_interface columns:
Invoice_id
Invoice_line_id
line_number
line_type_lookup_codeamount
po_header_id
po_number
po_line_id
po_line_number

po_line_location_id

po_shipment_num

po_distrubution_id

po_distrubution_num

Inventory_item_id

 

 

 
 
 
 
 

 

 

 

 

 

Thanks – Shivmohan Purohit