Database Structures FAQ's

1. What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

2. What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.

3. What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

4. What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

5. Explain the relationship among database, tablespace and data file.
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

6. What is schema?
A schema is collection of database objects of a user.

7. What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

8. Can objects of the same schema reside in different table spaces?
Yes.

9. Can a tablespace hold objects from different schemes?
Yes.

10. What is Oracle table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

11. What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

12. Do a view contain data?
Views do not contain or store data.

13. Can a view based on another view?
Yes.

14. What are the advantages of views?
- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.

15. What is an Oracle sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

16. What is a synonym?
A synonym is an alias for a table, view, sequence or program unit.

17. What are the types of synonyms?
There are two types of synonyms private and public.

18. What is a private synonym?
Only its owner can access a private synonym.

19. What is a public synonym?
Any database user can access a public synonym.

20. What are synonyms used for?
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.

21. What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

22. How are the index updates?
Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

23. What are clusters?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

24. What is cluster key?
The related columns of the tables in a cluster are called the cluster key.

25. What is index cluster?
A cluster with an index on the cluster key.

26. What is hash cluster?
A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

27. When can hash cluster used?
Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

28. What is database link?
A database link is a named object that describes a "path" from one database to another.

29. What are the types of database links?
Private database link, public database link & network database link.

30. What is private database link?
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

31. What is public database link?
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

32. What is network database link?
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

33. What is data block?
Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

34. How to define data block size?
A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

35. What is row chaining?
In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

36. What is an extent?
An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

37. What is a segment?
A segment is a set of extents allocated for a certain logical structure.

38. What are the different types of segments?
Data segment, index segment, rollback segment and temporary segment.

39. What is a data segment?
Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

40. What is an index segment?
Each index has an index segment that stores all of its data.

41. What is rollback segment?
A database contains one or more rollback segments to temporarily store "undo" information.

42. What are the uses of rollback segment?
To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

43. What is a temporary segment?
Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

44. What is a datafile?
Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

45. What are the characteristics of data files?
A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

46. What is a redo log?
The set of redo log files for a database is collectively known as the database redo log.

47. What is the function of redo log?
The primary function of the redo log is to record all changes made to data.

48. What is the use of redo log information?
The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

49. What does a control file contains?
- Database name
- Names and locations of a database's files and redolog files.
- Time stamp of database creation.

50. What is the use of control file?
When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Note: Some of the FAQ's might have been repeated, if it is so kinldy excuse me.

Project Accounting

Project Accounting

Costing

Items not costed
The output of the labor cost distribution cost distribution shows the error as Missing Cost Rate. This happens for Sub-Contractors when they do not have a Cost Rate setup. For employees the costing is based on the Job Grade but for Sub-contractors the costing is based on the Cost Rate setup. All Sub-contractors should have a Cost Rate set up.

Labor Cost Distribution Program does not pick up items
When giving a PA Date for the Cost distribution program, the date always has to be a Friday, if otherwise, the process will not pick up any items. This is applicable even if the week is a split week, i.e., the month end falls in midweek and the costing needs to be done for that month.

Items wrongly costed
The costing for employees is based on the business rule that ‘Maximum available hours per person per week unless the total hours that are billable are greater than the available hours’. This implies that the Costing for Billable Tasks will be as per the Cost Rate and any non-billable cost will be pro-rated as per the billable time entered and the maximum available hours.

E.g.
If an employee has coded 40 hours on billable task and 10 hours on non-billable task, then the non-billable time will be zero-costed (assuming that the maximum available hours for the week is 40)

If an employee has coded 30 hours on billable task and 15 hours on non-billable task, then the 15 hours on non-billable will be pro-rated for 10 hours. This will result in the Cost Rate for these items being less than the Standard Cost Rate.

Signal-11 error when running the Distribute Labor Cost program
This happens sometimes when the number of items that are being picked for distribution is very high. To avoid this, run the cost distribution in batches for one week at a time or one project at a time.

Reversed Time Items zero costed
The original items are cost distributed and the Project burden shows the correct value but the reversed items shows zero cost.
This happens when the items are reversed before they are cost distributed. Hence the reversed items will be zero-costed, as there is no cost rate attached to the original items. To rectify this, negative items have to be created through pre-approved expenditure batch, then before costing this batch has to be reversed. Finally, the cost should be distributed. This will correct the cost and also the hours charged so that the URVE is not affected.

Expense Report / Supplier Invoice items not costed
Expense Report / Supplier Invoice items are already costed when they are imported into PA. But when these items are adjusted i.e. transferred or reversed, the adjusted items will not be cost distributed. To do this, the Distribute Expense Report Cost process or the Distribute Supplier Invoice Adjustment Costs should be executed.


Agreements, Funding and Budgets

Budget cannot be baselined - Funding not equal to budget
The Funding amount is not the same as the budget. Specify the correct amount as in funding. Also, the budget should be at the same level as the funding, e.g. if the funding is at project level then the budget should also be at project level or if the funding is at task level then the budget should also be at task level

Also, sometimes when a funding line is created and then deleted, the budget amount will not match the funds. In these cases, the funding lines will have to be checked in the database and the amount equal to funding will have to be entered in the budget.

Note: When the Project start or end date is changed, the budget has to be re-baselined.

Revenue Generation

Items are not picked up for revenue
The time and expense items coded to a Billable task will only be picked up for Revenue Generation. All items coded to Non-Billable Task will not generate revenue. The billable flag for an item can be checked in the expenditure inquiry screen by choosing the Billable_Flag field from Show Field in the Folder Menu. This has to be checked for the item to be eligible for revenue generation.

The items have to be Cost distributed before they can generate revenue.
Also, the item date should be before the “accrue through date” when the generate revenue process is executed.

Events are not generated
The event date should be before the “accrue through date” when the generate revenue process is executed.

No Funding
An agreement and funding are not created for this project. Before generating revenue for a project, an agreement and funding have to be created and an approved revenue budget should be baselined.

Revenue has reached hard limit
Revenue generation is based on the agreement. If the agreement has a hard limit defined (the hard limit check box will be ticked), then revenue cannot be generated beyond the amount specified. To rectify this, either remove the hard limit or increase the funding.

Invoice Generation

No active event or expenditure items
The time and expense items coded to a Billable task will only be picked up for Invoice Generation. All items coded to Non-Billable Task will not be picked up for invoicing. The billable flag for an item can be checked in the expenditure inquiry screen by choosing the Billable_Flag field from Show Field in the Folder Menu.

The items have to be Cost distributed before they can generate revenue.
Also, the item date should be before the “bill through date” when the generate invoice process is executed.

For an event, the event date should be before the “ bill through date “ and should not have a bill hold.

Project not picked up when the Invoices are generated for a Range of Projects
When generating invoices for a range of projects, a project will not be picked for invoicing if the “next billing date” is after the current date. This can be checked in the Project Options -> Billing Set up -> Billing Assignments.


Interfacing Cost to GL

No Open Period
The GL period to which the items belong is not open in GL. Open the period and run the interface program again.


Interfacing Revenue to GL

No Open Period
The GL period to which the revenue lines belong is not open in GL. Open the period and run the interface program again.


Interfacing Invoices to AR

No Output Tax Code
The Tax Code in the Invoice lines is not available. Attach a tax code to the invoice lines and then run the interface program again.

No Open Period
The GL period to which the invoice lines belong is not open in GL and/or AR. Open the period and run the interface program again.


Interfacing Expense Report / Supplier Invoice Adjustments from and to AP

Project Status does not allow transactions
The project to which an expense item belongs should be ACTIVE. All items charged to Project, which is CLOSED or PENDING CLOSE would not be picked up by the interface program. To interface these items to PA, the project statuses have to be changed to ACTIVE and then the interface program needs to be run.

Transaction Exceptions

No Open Period
The GL Period in which the cost or revenue items belong might be closed. In case of invoices, the GL Period and / or the AR Period might be closed.

Costing Exceptions
Undefined
The journal import for the cost items is not done. The journal import in GL should be executed and then the tieback labor cost from GL program in PA has to be executed.

Revenue Exceptions
Undefined
The journal import for the revenue items is not done. The journal import in GL should be executed and then the tieback revenue from GL program in PA has to be executed.

Invoicing Exceptions

New Line not Processed in PA
The supplier invoice or expense reports in AP have not been interfaced to PA. To get these invoices / expense reports into PA, they have to approved and accounted and then the interface program in PA has to be executed.

No Assignment
The employee who has created an expense report might have been end dated. Hence these items will not be interfaced to PA from AP. To rectify this error, change the Supplier Type in AP of the employee from employee to supplier and then run the interface supplier invoice interface program.

Reports

The Project related reports – PDR, PCR and WIP & AR Reports will not pick a project if a Project Manager or Account Manager is not attached to the report.

The project related reports – PDR, PCR and WIP & AR Reports will pick data as per the GL Date of the Expenditure Items and not as per the Expenditure Item Date. Hence the reports might not match with the extract taken from Expenditure Inquiry screen if the item date has a different GL Date.

The Emailing program will not run for Internal Projects. The reports will be emailed to the concerned employee only if the person is set up as the Project / Account Manager / Project Controller of the Project.

ITime

ITime

Timecard Issues
Timecard Status shows SUBMITTED and not APPROVED
This issue happens in two cases –
An Oracle Bug in iTime Workflow that makes the Timecards to be stuck in the Approval process. A TAR has been raised with Oracle and resolution expected from them.

The Timecard is Submitted by the Secretary / Personal Assistant on behalf of the Sub-Contractor. In this case the Approval process gets stuck if the Sub-Contractor does not have a Renaissance userid. To rectify this, userids need to be created for All Sub-Contractor

A lot of timecards get stuck in the Approval Process since the Sub-Contractor is not created as a user and hence the need for manually pushing these timecards. To avoid this, it is imperative that the userid is created for Sub-Contractors also.

Timecard not imported
The Timecard might be in SUBMITTED or WORKING status. The Transaction Import will pick up only those timecards, which are APPROVED.

Missing Timecard Report showing the employee’s name even though the timecard was submitted
The Missing Timecard report will show an employee’s name if any of his Timecard is in WORKING status not necessarily the latest one.

Project not appearing in the Drop down list
When a project does not appear in the dropdown list, it means that the project is closed / pending close or the project completion date is before the current date. This Project is hence not available for entering new transactions. Please contact the respective project controller / project manager to change the project start or end dates.


Error Messages when submitting timecard

End-of-File on Communication Channel
This error occurs when there is a problem in the connectivity. Try to submit after sometime and if still unable log an issue with the renaissance helpdesk. Please find below the screen shot for this error.

Transaction Control Violated
This error occurs when there is a Transaction Control on a particular Project / Task, which stops the employee from entering timecard to the project / task. The employee needs to check with the Project Manager / Project Controller if he / she is entitled to code to the Project / Task. Please find below the screen shot for this error

For the above-mentioned errors, the users are requested to check this before they log an issue with Renaissance AM.

Item dates do not fall with Active Dates of the Project / Tasks
The time charged to the Task / Project is not within the active dates of the projects. Please contact your project controller / project manager to change the project start or end dates.
End-of-file on Communication Channel
Transaction Control Violation