Oracle Database Links (DB Link)

A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema.

Type of DB Links

There are 3 types of DB links. They are as follows:
1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users.

2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.

3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.

Types of Logins:

In dblink we can use 2 types of login. They are as follows:
1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
-------------
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING<'CONNECTION STRING'>

Code: (Text)
Create public database link daslink connect to current_user using 'ORCL'

2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
-------------
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING<'CONNECTION STRING'>

Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'ORCL'

Note: To create the public DBLINK the user must have create public database link system privileges.

Interface Table Names

ORACLE FINANCIALS – PAYABLES - INVOICES
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

ORACLE FINANCIALS – RECEIVABLES - CUSTOMERS
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
HZ_PARTY_INTERFACE

ORACLE FINANCIALS – RECEIVABLES - INVOICES
RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL

ORACLE FINANCIALS – RECEIVABLES – LOCK BOX
AR_PAYMENTS_INTERFACE_ALL

ORACLE FINANCIALS – RECEIVABLES - TAX
AR_TAX_INTERFACE

ORACLE FINANCIALS - CASH MANAGEMENT – BANK STATEMENTS
CE_STATEMENT_HEADERS_INT_ALL
CE_STATEMENT_LINES_INTERFACE

ORACLE FINANCIALS - FIXED ASSETS
FA_INV_INTERFACE

ORACLE FINANCIALS - GENERAL LEDGER – JOURNAL ENTRY
GL_INTERFACE

ORACLE MANUFACTURING – COST MANAGEMENT
CST_PC_ITEM_COST_INTERFACE
CST_PC_COST_DET_INTERFACE

ORACLE MANUFACTURING - INVENTORY
MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_LINES_INT
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TRANSACTIONS_INTERFACE

ORACLE MANUFACTURING – PURCHASING – PURCHASE ORDERS
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE

ORACLE MANUFACTURING – PURCHASING - RECEIVING
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE

ORACLE MANUFACTURING - ORDER ENTRY – SALES ORDERS
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE

ORACLE MANUFACTURING - MASTER SCHEDULING/MRP
MRP_FORECAST_INTERFACE
MRP_SCHEDULE_INTERFACE

ORACLE MANUFACTURING - SHOP FLOOR MANAGEMENT
WSM_LOT_JOB_INTERFACE
WSM_STARTING_LOTS_INTERFACE
WSM_LOT_MOVE_TXN_INTERFACE

ORACLE MANUFACTURING - QUALITY
QA_RESULTS_INTERFACE

ORACLE MANUFACTURING - WORK IN PROCESS
WIP_MOVE_TXN_INTERFACE
CST_COMP_SNAP_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE

Few important queries for the concurrent programs

Query to find the responsibilities to which the request is been assigned
SELECT DISTINCT *
FROM fnd_responsibility_tl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM fnd_responsibility_vl
WHERE request_group_id IN (
SELECT request_group_id
FROM fnd_request_group_units
WHERE request_unit_id =
(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name
= :Concurrent_Program_name))
AND end_date IS NULL)
AND "LANGUAGE" LIKE 'US'
ORDER BY responsibility_name

Query to find the application name
SELECT * FROM fnd_application "application name"
WHERE application_id IN (SELECT application_id
FROM fnd_request_group_units
WHERE
request_unit_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

Query to find the concurrent program short name
SELECT *
FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name)

Query to find the execution file name for the request
SELECT *
FROM fnd_executables
WHERE executable_id=(SELECT executable_id
FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

Query to find the requests groups
SELECT * FROM fnd_request_groups "requests groups"
WHERE request_group_id IN (SELECT request_group_id
FROM fnd_request_group_units
WHERE
request_unit_id=(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name=:Concurrent_Program_name))

Query for identifying if it is a child process
SELECT *
FROM fnd_concurrent_requests
WHERE parent_request_id IS NOT NULL
AND program_application_id = 20003
AND concurrent_program_id =
(SELECT DISTINCT concurrent_program_id
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name LIKE
:Concurrent_Program_name)

Delete the programs from the application
begin
fnd_program.delete_program('program short name','schema name');
fnd_program.delete_executable('program short name','schema name');
commit;
end;

Sql query to fetch users assigned to a responsibility
select fu.user_name
from FND_USER_RESP_GROUPS fur,
        fnd_responsibility_tl fr,
        fnd_user fu
where fr.language = 'US'
and fr.responsibility_name like :responsibility_name
and fr.responsibility_id = fur.responsibility_id
and fu.user_id = fur.user_id

Sql query to fetch concurrent programs in a request set
SELECT user_concurrent_program_name
FROM fnd_concurrent_programs_tl
WHERE concurrent_program_id IN (
SELECT concurrent_program_id
FROM fnd_request_set_programs
WHERE request_set_id =
(SELECT request_set_id
FROM fnd_request_sets_tl
WHERE UPPER (user_request_set_name) =
UPPER ('&REQUEST_SET_NAME')
AND LANGUAGE = 'US'))
AND LANGUAGE = 'US'

Sql query to fetch all concurrent program in pending status
select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31), fnd_conc_release_classes c where c.class_type = 'S' and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

 Sql query to find scheduled concurrent programs

  SELECT *

    FROM apps.FND_CONC_REQ_SUMMARY_V

   WHERE     phase_code = 'P'

         AND status_code IN ('I', 'Q')

         AND (NVL (request_type, 'X') != 'S')

         AND requested_start_date >= SYSDATE

ORDER BY program_short_name DESC;

P2P Cycle

There are certain things that should be wkown before going indetail about the P2P cycle.

What is PO?
PO is a commercial document issued by a buyer to a seller, indicating the type, quantities and agreed prices for products or services that the seller will provide to the buyer.

Why we need this PO?
This document represents the buyer’s intent to purchase specific quantities of product at specified prices. In the event of non-payment, the seller can use the PO as a legal document in a court of law.

What are the types of Purchase Orders?

1. Standard Purchase Orders:You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require. Each standard purchase order line can have multiple shipments, and you can distribute the quantity of each shipment across multiple accounts.

2.Contract Purchase Orders:A contract purchase agreement is an agreement between you and a supplier for unspecified goods or services. This agreement may include terms and conditions, committed amount, and effective and expiration dates.

3. Blanket Purchase Orders:With blanket purchase agreements and releases, you can negotiate a stable, long-term contract while maintaining flexible delivery schedules and order quantity commitments. You create blanket purchase agreements when you know the details of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules.

4. Planned Purchase Orders:You create a planned purchase order when you want to establish a long-term agreement with a single supplier to buy goods or services. Planned purchase orders include tentative delivery schedules and accounting distributions. You schedule releases against the planned purchase order to actually order the goods or services.

(P to P) cycle: There are different steps involved in P to P cycle they are `

1 Define items: Inventory
2 Enter item information: Purchase Order
3 Raise requisition: Purchase Order
4 Request For Quotation(RFQ) raised: Purchase Order
5 Quotation received: Purchase Order
6 Quote analysis: Purchase Order
7 Raise purchase order: Purchase Order
8 Receive materials: Inventory
9 Enter invoice: Accounts payable
10 Payment process: Accounts payable
11 Transfer to general ledger: General ledger

Generating a purchase order
Purchasing order can be generated in two ways
1) Auto generation
2) Procedural generation

Auto generation: The various step that are involved in auto generation of purchase order is as under
1) Click on Switch responsibility and select purchasing module and press enter
2) Click on Supply Base Suppliers, a new form opens
3) Enter Supplier name and click on sites, a new form opens
4) Enter all the required information and also see that Purchasing and Pay options are enabled
5) Click on contacts and enter the required information, save the form and close it.
6) Click on Suppliers list –to create a supplier list and add the suppliers that we have created.
7) Save the form and close it.

Creating a Requisition
1) Click on Requisition Requisitions a new form opens
2) Enter all the required information and save.
3) The approve button is enabled .click on approve button a new form opens.
4) Enter the hierarchal information and click on OK and close the form.

Raising a Purchase order directly
1) Click on the ‘Auto create’ a new form opens enter the requisition number and click on ‘Find’.
2) A new form opens. Check the item line and Select the document type as ‘Standard po’ and click on ‘Automatic’ tab.
3) A new form opens and click on ‘Create’ tab.
4) A window with the message with your purchase order number is displayed click ‘ok’ on it
5) Now click on “tools” and then on “copy Document” a new form opens,
6) click on ‘ok’ tab. And a new window with purchase order appears.
7) A new form opens Click on Approve button on this form
8) Enter the required information (real time) and click on ‘ok’ (submit approval)
9) Click ‘ok’ on new window that appears
10) And now we can see the status as ‘approved’ on the purchase order form.

Procedural generation
The various step that are involved in auto generation of purchase order is as under
1) Click on Switch responsibility and select purchasing module and press enter
2) Click on Supply Base Suppliers, a new form opens
3) Enter Supplier name and click on sites, a new form opens
4) Enter all the required information and also see that Purchasing and Pay options are enabled
5) Click on contacts and enter the required information, save the form and close it.
6) Click on Suppliers list –to create a supplier list and add the suppliers that we have created.
7) Save the form and close it.

Creating a Requisition
1) Click on Requisition
2) Enter all the required information and save.
3) The approve button is enabled .click on approve button a new form opens.
4) Enter the hierarchal information and click on OK and close the form.

Raising A RFQ
1) A new form opens. Select the document type as RFQ and click on ‘Automatic’ tab.
2) A new form opens and clicks on ‘Create’ tab.
3) A new form opens change the status as “Active” and click on “Suppliers” tab a new from opens, enter the required information and save the form and close it.
4) Click on tools and select “copy document” a new from opens
5) Enter the required information and click on “OK” a new form opens

Approving the Quotation
1) A new from opens
2) Change as status as”active” click on “Type” and then on “approve a new form opens
3) Enter the require information and click on “ok” and a message with text ‘quotation has been approved’ will appear, now click on ‘ok’ and close the form
4) Now click on “tools” and then on “copy Document” a new form opens, click ‘ok’ on the document where your purchase order will be created with a number..
5) Click ‘ok’ on the document a new form opens

Raising a Purchase Order
1) A new form opens, Click on Approve button on this form
2) Enter the required information (real time)and click on ‘ok’(submit approval)
3) Click ‘ok’ on new window that appears
4) And now we can see the status as ‘approved’ on the purchase order form.

To view Summary of the purchase order
1) Close all the existing opened forms and click on ‘notification summary’ In the main menu.
2) A new window opens in the internet explorer
3) Enter the user name and password provided by the system admin and login
4) A new window opens with all the purchase order details.
5) Click on the exact purchase order which is created by you. this can be done only if you remember your purchase order number.

Releasing the Order
The step by step process of releasing the order is as under:
1) Click on Purchase Order Releases –A new form opens
2) Enter the Purchase Order number and other required information and click on ‘Save’
3) ‘Approve’ button gets enabled now, hence click on it.
4) A new form opens, enter the required information and click on ‘ok’
5) Now click on ‘ok’ tab that is seen on message window.
6) Now the status changes to ‘Approved’
7) Now go to ‘Notification Summary’ window
8) You can see the ‘Blanket Purchase release document’ approved.
9) Click on it to view the detailed summary.

Receiving the Order
The step by step process of receiving the order is as under:
1) Click on Receiving Receipts –A new form opens select the organisation and click on ‘ok’ simultaneously a new form opens
2) Enter the P.O number in that form and click on ‘find’
3) ‘Receipt Header’ form opens-click on new receipt radio button and close the form
4) A new form opens, check box the item that is available there and save the form. Close it.
5) Click on ‘receiving transactions’, a new form opens.
6) Enter the P.O number and click on ‘Find’, a new form opens.
7) Check box the item that is available and select ‘Stores’ as sub inventory .save and close the form.

Note: The query to retrieve the data which covers the complete p2p cycle is as follows:

http://arunrathod.blogspot.com/2008/08/query-that-covers-p2p-life-cycle.html

Custom Reports Development & Customization Process

Report Builder Components

Report Builder Components are

1. Data Model
2. Layout Model
3. Object Navigator
4. Report Triggers
5. Parameter Form
6. Program Units
7. Attached Libraries

Data Model
The Report Editor's Data Model view enables you to define and modify the data model objects for a report.

Data Model Comprises tool Palette which Comes with several Options They are
1. Select
2. Magnify
3. SQL Query
4. Ref Cursor Query
5. Express Query
6. Summary Column
7. Formula Column
8. Placeholder Column
9. Cross Product
10. Data Link

The tool palette is a set of tools you can use to create and manipulate objects. Click a tool to activate it for a single operation, or double-click a tool to "lock" it for multiple operations. The tools in the tool palette vary depending on the Report Editor view.

Select
The Selector toolbar provides tools to help you select or arrange dimension values that meet your criteria. The Selector toolbar is displayed at the top of the Selector dialog box.

Magnify
To magnify a hard-to-see portion of your report or reduce the image to get a sense of your report's overall layout.

SQL Query
The SQL Query Statement property is a SQL SELECT statement that retrieves data from the database for your report. Enter a valid SELECT statement not to exceed 64K. The upper limit may vary between operating systems All features of the SELECT statement are supported, except the INTO and FOR UPDATE clauses.

In building your SELECT statement, you can do the following:

1. Use the Tables and Columns dialog box
2. Insert comments
3. Insert bind and lexical references
4. Review SQL errors

Required/Optional : Required

Ref Cursor Query
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.

You base a query on a ref cursor when you want to:

1. more easily administer SQL
2. avoid the use of lexical parameters in your reports
3. share data sources with other applications, such as Form Builder
4. encapsulate logic within a subprogram

Express Query
--------

Summary Column
A summary column performs a computation on another column's data.
Using the Report Wizard or Data Wizard, you can create the following summaries:
sum, average, count, minimum, maximum, % total.

You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

Formula Column
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

Placeholder Column
A placeholder is a column for which you set the data type and value in PL/SQL that you define.

You can set the value of a placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder's group or a group below it

Cross Product
The Cross Product Group property is the group that contains the source groups of the Horizontal and Vertical Repeating Frames. The cross product group correlates values between one or more groups to produce the data in the matrix.

Values Enter a valid cross product group name.
Applies to matrices
Required/Optional required
Default blank

Data Link
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group.
When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime.

Layout Model
The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

This is the view where you can design your Report Layout.

Object Navigator
The Object Navigator provides a hierarchical display of all major objects in a report or template, including attached libraries and external queries. Using this view, you can take such actions as select an object, bring up the Property Palette for an object, edit an object's PL/SQL, drag and drop PL/SQL program units, and search for an object by name.

Report Triggers
Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database.

To create or modify a report trigger, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report triggers which are fired in the following sequence.

1. Before Parameter Form trigger
2. After Parameter Form trigger
3. Before Report trigger
4. Between Pages trigger
5. After Report trigger

1. Before Parameter Form trigger
The Before Parameter Form trigger fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
Definition Level Report

On Failure
Displays an error message and then returns to the place from which you ran the report.

2. After Parameter Form trigger
The After Parameter Form trigger fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

Definition Level Report

On Failure
Returns to the Runtime Parameter Form. If the Form is suppressed, then returns to place from which you ran the report.

3. Before Report trigger
The Before Report trigger fires before the report is executed but after queries is parsed and data is fetched.
Definition Level Report

On Failure
Displays an error message and then returns to the place from which you ran the report.

4. Between Pages trigger
Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Definition Level report
On Failure
Displays an error message when you try to go to the page for which the trigger returned FALSE. The Between Pages trigger does not fire before the first page.

5. After Report trigger
The After Report trigger fires after you exit the Runtime Previewer, or after report output is sent to a specified destination, such as a file, a printer, or a mailid. This trigger can be used to clean up any initial processing that was done, such as deleting tables.
Note, however, that this trigger always fires, whether or not your report completed successfully.
Definition Level Report

On Failure
Does not affect formatting because the report is done. You can raise a message, though, to indicate that the report did not run correctly

Note: The After-Report trigger does not fire when you are in the Live Previewer.

Layout Model Properties

In this Layout model, we have several Options

Frames:
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.
For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries.
When you default the layout for a report, Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.

Repeating Frames
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
When you default the layout for a report, Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view.

Text
This allows to embed the text in the layout view.

Field
A field is the layout container for each column in the layout. A field is owned by the object surrounding it, typically a repeating frame, unless the field is a summary (in which case it is owned by a frame).

Anchor
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol on it is attached to the parent object...

Parameter Form
The Report Editor's Parameter Form view enables you to create a Runtime Parameter Form for your report. You can select pre-defined system parameters for your form using the Parameter Form Builder, or you can create your own.

Program Units
Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.

Stored program units (also known as stored subprograms) can be compiled separately and stored permanently in an Oracle database, ready to be executed. Once compiled and stored in the data dictionary, they are schema objects, which can be referenced by any number of applications connected to that database.
Stored program units offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.

Stored program units are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Also, they take advantage of shared memory. So, only one copy of a program unit need be loaded into memory for execution by multiple users.

Attached Libraries
Attached libraries are external PL/SQL libraries that you have associated with a report or another external library. When an external library is attached, you can reference its packages, functions, and procedures from within your report. For example, if you attached an external library name MYLIB to your report and it contained a function named ADDXY, then you could reference ADDXY from any PL/SQL in the report.

Report Customizations
You can customize business intelligence reports by both content and layout. You also have the option to add columns, filter the data and sort the resulting information. For example, you can add another column for a running total, or insert grand totals and subtotals as you need them. In layout, you can change column headings, report titles and the order of columns. You can also display the data in graphical form, such as bar graphs or pie charts.

Customization is enhancement of new features to the Existing Report Content and Layout depending on the new requirement.

Multibyte Sqlload

To SQLLOAD with multi-byte characters when data is in Excel

1. Add a header line at the top of the page (if one doesn’t exist)
2. Save as a Unicode text file
3. Open the saved Unicode file in Word
4. Remove any tabs. Edit->Replace (you may have to cut and paste a tab in from notepad to do this) – Replace All
5. The delimiter used above may be in double quotes - remove the quotes. For instance, Edit->Replace “,” , replace all
6. Save the file as plain text, then Unicode (UTF-8). Note: Saving as just Unicode will not work
7. Binary ftp the file to Unix only using command line
8. Make sure to specify Options (skip =1) in your ctl file to skip the header

Note: the reason for the ensuring there is a header is that some characters are inserted at the beginning of the first line. If we make it the header and then skip it anyway, we don’t need to worry about deleting those characters.

Sample datafile:

Site, Rep
1000,仕切値について,
1001,仕切値について,
1002,仕切値について,
1003,仕切値について,


Sample CTL file (characterset should be optional):

-------------------------------------------------------------------------
--
-- Description: SQL*Loader control file to load data from level 1
--
-- Date:
-------------------------------------------------------------------------
OPTIONS (skip = 1)
Load Data
--Infile 'load2.txt'
Characterset UTF8
REPLACE
INTO TABLE LEVEL1_SALESREP_UPD
Fields terminated BY ','
optionally enclosed BY '"'
trailing nullcols
(
site_number,
lv1_rep
)


Sample load command:

sqlldr userid=apps/ control=load.ctl data=load3.txt

Sample Log output:

QL*Loader: Release 8.0.6.1.0 - Production on Thu Dec 11 16:53:18 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Control File: load.ctl
Character Set UTF8 specified for all input.

Data File: load3.txt
Bad File: load3.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table LEVEL1_SALESREP_UPD, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SITE_NUMBER FIRST * , O(") CHARACTER
LV1_REP NEXT * , O(") CHARACTER


Table LEVEL1_SALESREP_UPD:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 1
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Thu Dec 11 16:53:18 2003
Run ended on Thu Dec 11 16:53:18 2003

Elapsed time was: 00:00:00.23
CPU time was: 00:00:00.04

HRMS API's

Updating the Per_periods_of_service table using
hr_ex_employee_api.update_term_details_emp

Terminating using
hr_ex_employee_api.actual_termination_emp

Applying the final process in the shared instance using hr_ex_employee_api.final_process_emp

Re - Hire
hr_employee_api.re_hire_ex_employee

Updation On Already Existing Records
hr_person_api.update_us_person

New Hire
hr_employee_api.create_us_employee

Costing
pay_cost_allocation_api.create_cost_allocation

Load Update Assign
hr_assignment_api.update_us_emp_asg

Load Update Assign Criteria
hr_assignment_api.update_emp_asg_criteria

IF CONTACT PERSON ALREADY CREATED AND ONLY RELATION SHIP IS TO BE CREATED -
Note : Contact Person Id Is To Be Passed
hr_contact_rel_api.create_contact

If Contact Person Already Not Created
Note : Contact Person Id is passed as Null(default of API)
hr_contact_rel_api.create_contact

Load Phones
hr_phone_api.create_phone
hr_person_address_api.update_person_address

Load Addresses
hr_person_address_api.update_person_address
hr_person_address_api.create_person_address

Load Payment Methods
hr_personal_pay_method_api.create_us_personal_pay_method

Element Loading
py_element_entry_api.create_element_entry
py_element_entry_api.update_element_entry

Load Salaries
hr_upload_proposal_api.upload_salary_proposal

Approve Salary Proposal
hr_maintain_proposal_api.approve_salary_proposal

Starts To Validate/Load Federal Tax For A Person pay_federal_tax_rule_api.update_fed_tax_rule

State tax rules
pay_state_tax_rule_api.create_state_tax_rule
pay_state_tax_rule_api.update_state_tax_rule

County Tax Rules
pay_county_tax_rule_api.create_county_tax_rule
pay_county_tax_rule_api.update_county_tax_rule

City Tax Rules
pay_city_tax_rule_api.create_city_tax_rule
pay_city_tax_rule_api.update_city_tax_rule

Schools and Colleges
per_esa_upd.upd
per_esa_ins.ins

Performance Reviews
hr_perf_review_api.create_perf_review
hr_perf_review_api.update_perf_review

State Information Taxes
hr_sit_api.update_sit
hr_sit_api.create_sit

Qualifications
per_qualifications_api.create_qualification
per_qualifications_api.update_qualification

Locations
hr_location_api.update_location
hr_location_api.create_location

Organization
hr_organization_api.update_organization
hr_organization_api.create_org_classification

If any Change in Organization information.
if information2 = 'Y' then
hr_organization_api.enable_org_classification

If any Change in Organization information.
if information2 = 'N' then
hr_organization_api.disable_org_classification

If Organization does not exist in instance
hr_organization_api.create_organization api

Jobs
hr_job_api.update_job
hr_job_api.create_job

Positions
hr_position_api.update_position
hr_position_api.create_position

The query to get the list of HRMS API's in Oracle is as follows:

select *
from all_objects
where object_name like 'HR%\_API' escape '\'
and object_type = 'PACKAGE'
union
select *
from all_objects
where object_name like 'PAY%\_API' escape '\'
and object_type = 'PACKAGE'
union
select *
from all_objects
where object_name like 'PER%\_API' escape '\'
and object_type = 'PACKAGE'

Creating users in oracle using PL/SQL.

We can use the 'hr_user_acct_internal.create_fnd_user' API to create the users. The sample code is as follows:

BEGIN
apps.hr_user_acct_internal.create_fnd_user
(p_user_name => 'XXX',
p_password => 'XXX',
p_employee_id => 1234(This is the person id from per_all_people_f),
p_user_id => x_user_id,
p_user_start_date => SYSDATE,
p_email_address => 'XXX',
p_description => 'XXX',
p_password_date => NULL
);
COMMIT;
END;

and to add the responsibility to the user, we can use the following code.

BEGIN
fnd_user_pkg.addresp
(username => 'XXX',
resp_app => user_res_rec.application_short_name,
resp_key => user_res_rec.responsibility_key,
security_group => 'STANDARD',
description => 'DESCRIPTION',
start_date => SYSDATE,
end_date => NULL
);
Commit;
END

Sending email using Pl/sql

This Article is basically designed keeping in view of the 2 types of audience
1. Novice in UTL_SMTP
2. Programmer in UTL_SMTP

Abstract:

The EMAIL has become an integral part of almost every human's life. Without it, many things we do would become very tedious, perhaps impossible tasks.
This white paper we present an overview about using email with SQL/PLSQL using SMTP.

Executive Summary:

Oracle’s UTL_SMTP package can be used to create and send e-mails from PL/SQL routines. I wanted to be able to send not only text in an e-mail, but also html for those e-mail systems that could display the html equivalent of the message. I also wanted to be able to include multiple file attachments of various formats (pdf, jpeg, MS Word, etc.), which requires encoding the binary files into an ascii format that could be transmitted with the e-mail, and differentiating them from plain text files that could be copied as-is into the e-mail as attachments. Oracle’s UTL_ENCODE package has a BASE64_ENCODE routine that can be used to encode the binary data into a format that can be included in the e-mail. And, reading the binary file can be done with the READ routine in Oracle’s DBMS_LOB package.

The code written can used to specify any From address, so that our e-mail looks like it’s coming from us instead of from our Oracle server. We can also specify multiple To addresses along with multiple CC (carbon copy) addresses and multiple BCC (blind carbon copy) addresses. The e-mail addresses can be in any format, such as arun (which attaches @), arun@appsassociates.com, < arun@appsassociates.com >, Arun Kumar , and "Arun" < arun@appsassociates.com >, which are separated by commas or semicolons if there are multiple addresses listed.

Both a text message and an html message can be specified, either as a text string or as a file name to be included inline. Then, up to three binary or textual files can be attached to the e-mail of any MIME type, defaulting to text/plain. For the attachments, we need to specify the MIME type, a list of which can be seen here. The MIME types I’ve tried include text/plain, text/html, image/jpeg, image/gif, application/pdf, and application/msword. The attachment is assumed to be a binary file if the MIME type does not begin with “text”, and is encoded as base64 in the e-mail; otherwise, the file is just copied as-is into the e-mail as an attachment. Any file in any directory accessible to the user can be attached; but, see below about running from triggers and the potential for a commit being done.

The complete parameter list for the email procedure is shown below:

from_name - name and e-mail address to put in the From field
to_names - names and e-mail addresses for the To field
(separated by commas or semicolons)
subject - text string for Subject field
message - text string or unix text file name for the message, if any
html_message - html string or unix html file name for the message, if any
cc_names - names and e-mail addresses for the CC field, if any
(separated by commas or semicolons)
bcc_names - names and e-mail addresses for the BCC field, if any
(separated by commas or semicolons)
filename1 - first unix file pathname to attach, if any
filetype1 - MIME type of the first file (defaults to 'text/plain')
filename2 - second unix file pathname to attach, if any
filetype2 - MIME type of the second file (defaults to 'text/plain')
filename3 - third unix file pathname to attach, if any
filetype3 - MIME type of the third file (defaults to 'text/plain')

A sample call in PL/SQL is shown below, which sends a text and html message, plus a text file and two binary files (note: the slash after "end;" must be the first character on it's line):

begin
email_files(from_name => 'XXX@yahoo.com' ,
to_names => 'XXXX@yahoo.com',
subject => 'A test',
message => 'A test message',
html_message => '

A test message

',
filename1 => '/tmp/web_ptrbdca.txt',
filename2 => '/tmp/password_standards.pdf',
filetype2 => 'application/pdf',
filename3 => '/tmp/wb703.jpg',
filetype3 => 'image/jpeg');
end;
/

The user running this must have "create any directory" and "drop any directory" privileges ("create directory" was introduced in Oracle 9iR2), which must be granted from a system or dba account, such as:
grant create any directory to scott;
grant drop any directory to scott;
connect / as sysdba
grant select on dba_directories to scott;
or, for everyone to have directory privileges:
grant create any directory to public;
grant drop any directory to public;
connect / as sysdba
grant select on dba_directories to public;

We may also want to create a public synonym for this procedure, from the procedure's owner:
create or replace public synonym email_files for email_files;
grant execute on email_files to public;

If we use email procedure to send e-mails with attachments from triggers, we will first have to create an Oracle directory entry, such as shown below, in order to keep from getting a commit error in the trigger. If email files procedure doesn't find an existing Oracle directory entry for our attachment file's directory, it creates a temporary one. The "create directory" command is a DDL statement, which causes an implicit commit; but, commits aren't allowed in triggers. Also, if we are sending email attachments from a PL/SQL routine that we don't want a commit done in, we will need to have an Oracle directory entry pre-created as well, such as for /home/common shown below:
create directory CESDIR_COMMON as '/home/common';
grant read on directory CESDIR_COMMON to public;

We will need to have Java installed in our database to use this. To see if we already have it installed, run the following SQL:

select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;

If we get around 9000 for the counts, we have Java installed. If we get zero or a small number, run the following SQL to install Java, making sure that we have around 1 Meg free in the system tablespace first:

connect / as sysdba
select bytes/1024/1000 Meg from dba_free_space where tablespace_name = 'SYSTEM';
shutdown
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
@$ORACLE_HOME/javavm/install/initjvm.sql
@$ORACLE_HOME/xdk/admin/initxml.sql
@$ORACLE_HOME/xdk/admin/xmlja.sql
@$ORACLE_HOME/rdbms/admin/catjava.sql
shutdown immediate
startup


The code for sending the email using UTL_SMTP is as follows:


/**************************************************************************
* PROCEDURE NAME : XX_EMAIL_FILES
*
* DESCRIPTION:
* ==========
* Sends e-mail (text and/or html, either as a string or from a file)
* to one or more recipients (including cc and/or bcc recipients), along with
* up to 3 file attachments (text and/or binary; default is text/plain), using
* the UTL_SMTP package to send the e-mail, the DBMS_LOB package to read
* binary file attachments, and the UTL_ENCODE package to convert the binary
* attachments to BASE64 for character string (non-binary) transmission.
* BE AWARE THAT A COMMIT MAY BE DONE BY THIS ROUTINE.
*
* PARAMETERS:
* ==========
* The complete parameter list for the xx_email_files procedure is shown below:

* NAME TYPE DESCRIPTION
* ----------------- -------- ---------------------------------------------
* from_name IN Name and e-mail address to put in the From field
* to_names IN Names and e-mail addresses for the To field (separated by
* commas or semicolons)
* subject IN Text string for Subject field
* message IN Text string or text file name for Message, if any
* html_message IN Html string or html file name for Message, if any
* cc_names IN Names and e-mail addresses for the Cc field, if any
* (separated by commas or semicolons)
* bcc_names IN Names and e-mail addresses for the Bcc field, if any
* (separated by commas or semicolons)
* filename1 IN First unix file pathname to attach, if any
* filetype1 IN Mime type of first file (defaults to 'text/plain')
* filename2 IN Second unix file pathname to attach, if any
* filetype2 IN Mime type of second file (defaults to 'text/plain')
* filename3 IN Third unix file pathname to attach, if any
* filetype3 IN Mime type of third file (defaults to 'text/plain')
*
* Sample names and e-mail addresses are: arun (attaches @),
* xxx@yahoo.com, , xxx , and
* "xxx"
*
* A sample call in PL/SQL is shown below, which sends a text and html message,
* plus a text file and two binary files (note: the slash after "end;" must be
* the first character on it's line):
*
* begin
* xx_email_files(from_name => 'xxx@yahoo.com' ,
* to_names => 'xxx@yahoo.com',
* subject => 'A test',
* message => 'A TEST MESSAGE',
* html_message => '

A test message

',
* filename1 => '/ora_appl/oracle/11.5.0/data/xxx.pdf',
* filename2 => '/usr/tmp/115apug.pdf',
* filetype2 => 'application/pdf',
* filename3 => '',
* filetype3 => 'image/jpeg'
* );
* end
* /
*
* If the message or html_message string has a file name in it (starting with
* a forward slash), the text or html file is copied into the e-mail as the
* message or html message; otherwise, the message or html_message is copied
* into the e-mail as-is.
*
* Attachment file types (mime types) that I've tested include:
* text/plain,
* text/html,
* image/jpeg,
* image/gif,
* application/pdf,
* application/msword
* A list of mime types can be seen at:
* http://www.webmaster-toolkit.com/mime-types.shtml
* If the mime type does not begin with "text", it is assumed to be a binary
* file that will be encoded as base64 before transmission.
*************************************************************************/

CREATE OR REPLACE PROCEDURE xx_email_files (
from_name VARCHAR2,
to_names VARCHAR2,
subject VARCHAR2,
MESSAGE VARCHAR2 DEFAULT NULL,
html_message VARCHAR2 DEFAULT NULL,
cc_names VARCHAR2 DEFAULT NULL,
bcc_names VARCHAR2 DEFAULT NULL,
filename1 VARCHAR2 DEFAULT NULL,
filetype1 VARCHAR2 DEFAULT 'text/plain',
filename2 VARCHAR2 DEFAULT NULL,
filetype2 VARCHAR2 DEFAULT 'text/plain',
filename3 VARCHAR2 DEFAULT NULL,
filetype3 VARCHAR2 DEFAULT 'text/plain'
)
IS
-- Change the SMTP host name and port number below to your own values,
-- if not localhost on port 25:
smtp_host VARCHAR2 (256) := 'localhost';
smtp_port NUMBER := 25;
-- Change the boundary string, if needed, which demarcates boundaries of
-- parts in a multi-part email, and should not appear inside the body of
-- any part of the e-mail:
boundary CONSTANT VARCHAR2 (256) := 'CES.Boundary.DACA587499938898';
recipients VARCHAR2 (32767);
directory_path VARCHAR2 (256);
file_name VARCHAR2 (256);
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
mesg VARCHAR2 (32767);
conn UTL_SMTP.connection;
l_length NUMBER;
l_sub VARCHAR2 (32767);

TYPE varchar2_table IS TABLE OF VARCHAR2 (256)
INDEX BY BINARY_INTEGER;

file_array varchar2_table;
type_array varchar2_table;
i BINARY_INTEGER;

-- Function to return the next email address in the list of email addresses,
-- separated by either a "," or a ";". From Oracle's demo_mail. The format
-- of mailbox may be in one of these:
-- someone@some-domain
-- "Someone at some domain"
-- Someone at some domain
FUNCTION get_address (addr_list IN OUT VARCHAR2)
RETURN VARCHAR2
IS
addr VARCHAR2 (256);
i PLS_INTEGER;

FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2)
RETURN PLS_INTEGER
IS
c VARCHAR2 (5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;
BEGIN
inside_quote := FALSE;
i := 1;
len := LENGTH (str);

WHILE (i <= len)
LOOP
c := SUBSTR (str, i, 1);

IF (inside_quote)
THEN
IF (c = '"')
THEN
inside_quote := FALSE;
ELSIF (c = '\')
THEN
i := i + 1; -- Skip the quote character
END IF;

GOTO next_char;
END IF;

IF (c = '"')
THEN
inside_quote := TRUE;
GOTO next_char;
END IF;

IF (INSTR (chrs, c) >= 1)
THEN
RETURN i;
END IF;

<>
i := i + 1;
END LOOP;

RETURN 0;
END;
BEGIN
addr_list := LTRIM (addr_list);
i := lookup_unquoted_char (addr_list, ',;');

IF (i >= 1)
THEN
addr := SUBSTR (addr_list, 1, i - 1);
addr_list := SUBSTR (addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;

i := lookup_unquoted_char (addr, '<');

IF (i >= 1)
THEN
addr := SUBSTR (addr, i + 1);
i := INSTR (addr, '>');

IF (i >= 1)
THEN
addr := SUBSTR (addr, 1, i - 1);
END IF;
END IF;

RETURN addr;
END;

-- Procedure to split a file pathname into its directory path and file name
-- components.
PROCEDURE split_path_name (
file_path IN VARCHAR2,
directory_path OUT VARCHAR2,
file_name OUT VARCHAR2
)
IS
pos NUMBER;
BEGIN
-- Separate the filename from the directory name
pos := INSTR (file_path, '/', -1);

IF pos = 0
THEN
pos := INSTR (file_path, '\', -1);
END IF;

IF pos = 0
THEN
directory_path := NULL;
ELSE
directory_path := SUBSTR (file_path, 1, pos - 1);
END IF;

file_name := SUBSTR (file_path, pos + 1);
END;

-- Procedure to append a file's contents to the e-mail
PROCEDURE append_file (
directory_path IN VARCHAR2,
file_name IN VARCHAR2,
file_type IN VARCHAR2,
conn IN OUT UTL_SMTP.connection
)
IS
generated_name VARCHAR2 (30)
:= 'CESDIR' || TO_CHAR (SYSDATE, 'HH24MISS');
directory_name VARCHAR2 (30);
file_handle UTL_FILE.file_type;
bfile_handle BFILE;
bfile_len NUMBER;
pos NUMBER;
read_bytes NUMBER;
line VARCHAR2 (1000);
DATA RAW (200);
my_code NUMBER;
my_errm VARCHAR2 (32767);
BEGIN
BEGIN
-- Grant access to the directory, unless already defined, and open
-- the file (as a bfile for a binary file, otherwise as a text file).
BEGIN
line := directory_path;

SELECT dd.directory_name
INTO directory_name
FROM dba_directories dd
WHERE dd.directory_path = line AND ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
directory_name := generated_name;
END;

IF directory_name = generated_name
THEN
EXECUTE IMMEDIATE 'create or replace directory '
|| directory_name
|| ' as '''
|| directory_path
|| '''';

EXECUTE IMMEDIATE 'grant read on directory '
|| directory_name
|| ' to public';
END IF;

IF SUBSTR (file_type, 1, 4) != 'text'
THEN
bfile_handle := BFILENAME (directory_name, file_name);
bfile_len := DBMS_LOB.getlength (bfile_handle);
pos := 1;
DBMS_LOB.OPEN (bfile_handle, DBMS_LOB.lob_readonly);
ELSE
file_handle := UTL_FILE.fopen (directory_name, file_name, 'r');
END IF;

-- Append the file contents to the end of the message
LOOP
-- If it is a binary file, process it 57 bytes at a time,
-- reading them in with a LOB read, encoding them in BASE64,
-- and writing out the encoded binary string as raw data
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
IF pos + 57 - 1 > bfile_len
THEN
read_bytes := bfile_len - pos + 1;
ELSE
read_bytes := 57;
END IF;

DBMS_LOB.READ (bfile_handle, read_bytes, pos, DATA);
UTL_SMTP.write_raw_data (conn, UTL_ENCODE.base64_encode (DATA));
pos := pos + 57;

IF pos > bfile_len
THEN
EXIT;
END IF;
-- If it is a text file, get the next line of text, append a
-- carriage return / line feed to it, and write it out
ELSE
UTL_FILE.get_line (file_handle, line);
UTL_SMTP.write_data (conn, line || crlf);
END IF;
END LOOP;
-- Output any errors, except at end when no more data is found
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
my_code := SQLCODE;
my_errm := SQLERRM;
DBMS_OUTPUT.put_line ('Error code ' || my_code || ': ' || my_errm);
END;

-- Close the file (binary or text)
IF SUBSTR (file_type, 1, 4) != 'text'
THEN
DBMS_LOB.CLOSE (bfile_handle);
ELSE
UTL_FILE.fclose (file_handle);
END IF;

IF directory_name = generated_name
THEN
EXECUTE IMMEDIATE 'drop directory ' || directory_name;
END IF;
END;
BEGIN
-- Load the three filenames and file (mime) types into an array for
-- easier handling later
file_array (1) := filename1;
file_array (2) := filename2;
file_array (3) := filename3;
type_array (1) := filetype1;
type_array (2) := filetype2;
type_array (3) := filetype3;
-- Open the SMTP connection and set the From and To e-mail addresses
conn := UTL_SMTP.open_connection (smtp_host, smtp_port);
UTL_SMTP.helo (conn, smtp_host);
recipients := from_name;
UTL_SMTP.mail (conn, get_address (recipients));
recipients := to_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, get_address (recipients));
END LOOP;

recipients := cc_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, get_address (recipients));
END LOOP;

recipients := bcc_names;

WHILE recipients IS NOT NULL
LOOP
UTL_SMTP.rcpt (conn, get_address (recipients));
END LOOP;

UTL_SMTP.open_data (conn);
-- Build the start of the mail message
mesg :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf
|| 'From: '
|| from_name
|| crlf
|| 'Subject: '
|| subject
|| crlf
|| 'To: '
|| to_names
|| crlf;

IF cc_names IS NOT NULL
THEN
mesg := mesg || 'Cc: ' || cc_names || crlf;
END IF;

IF bcc_names IS NOT NULL
THEN
mesg := mesg || 'Bcc: ' || bcc_names || crlf;
END IF;

mesg :=
mesg
|| 'Mime-Version: 1.0'
|| crlf
|| 'Content-Type: multipart/mixed; boundary="'
|| boundary
|| '"'
|| crlf
|| crlf
|| 'This is a Mime message, which your current mail reader may not'
|| crlf
|| 'understand. Parts of the message will appear as text. If the remainder'
|| crlf
|| 'appears as random characters in the message body, instead of as'
|| crlf
|| 'attachments, then you''ll have to extract these parts and decode them'
|| crlf
|| 'manually.'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);

-- Write the text message or message file, if any
IF MESSAGE IS NOT NULL
THEN
mesg :=
'--'
|| boundary
|| crlf
|| 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII'
|| crlf
|| 'Content-Disposition: inline; filename="message.txt"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);

IF SUBSTR (MESSAGE, 1, 1) = '/'
THEN
split_path_name (MESSAGE, directory_path, file_name);
append_file (directory_path, file_name, 'text', conn);
UTL_SMTP.write_data (conn, crlf);
ELSE
UTL_SMTP.write_data (conn, MESSAGE || crlf);
END IF;
END IF;

IF html_message IS NOT NULL
THEN
mesg :=
'--'
|| boundary
|| crlf
|| 'Content-Type: text/html; name="message.html"; charset=US-ASCII'
|| crlf
|| 'Content-Disposition: inline; filename="message.html"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
UTL_SMTP.write_data (conn, mesg);

IF SUBSTR (html_message, 1, 1) = '/'
THEN
split_path_name (html_message, directory_path, file_name);
append_file (directory_path, file_name, 'text', conn);
UTL_SMTP.write_data (conn, crlf);
ELSE
UTL_SMTP.write_data (conn, html_message || crlf);
END IF;
END IF;

-- Append the files
FOR i IN 1 .. 3
LOOP
-- If the filename has been supplied ...
IF file_array (i) IS NOT NULL
THEN
split_path_name (file_array (i), directory_path, file_name);
-- Generate the MIME boundary line according to the file (mime) type
-- specified.
mesg := crlf || '--' || boundary || crlf;

SELECT INSTR (file_name, '.')
INTO l_length
FROM DUAL;

SELECT SUBSTR (file_name, 1, l_length - 1) || '.pdf'
INTO l_sub
FROM DUAL;

IF SUBSTR (type_array (i), 1, 4) != 'text'
THEN
mesg :=
mesg
|| 'Content-Type: '
|| type_array (i)
|| '; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: base64'
|| crlf
|| crlf;
ELSE
mesg :=
mesg
|| 'Content-Type: application/octet-stream; name="'
|| file_name
|| '"'
|| crlf
|| 'Content-Disposition: attachment; filename="'
|| file_name
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: 7bit'
|| crlf
|| crlf;
END IF;

UTL_SMTP.write_data (conn, mesg);
-- Append the file contents to the end of the message
append_file (directory_path, file_name, type_array (i), conn);
UTL_SMTP.write_data (conn, crlf);
END IF;
END LOOP;

-- Append the final boundary line
mesg := crlf || '--' || boundary || '--' || crlf;
UTL_SMTP.write_data (conn, mesg);
-- Close the SMTP connection
UTL_SMTP.close_data (conn);
UTL_SMTP.quit (conn);
END;
/

Setting Japanese Language in TOAD

Select My Computer -> Right Click -> Properties -> Advanced -> Environment Variables
Add New Variable NLS_LANG=Japanese_Japan.JA16SJIS at Both User and System level

* Open Toad and connect to the Database required.
* Execute the Query required and select Grid Options.
Data Grid – Visuals -> Font ->
Select Font Style = Arial Unicode MS (If Arial Unicode MS is not available in your fonts lists, then we need to install this font )
Select Script = Japanese.
* Click Ok and Execute the Query.
* Path to Add Font if font is not available:
Navigation -> Control Panel -> fonts (To add fonts)

To export the data from the toad to you local system we need to follow the below procedure..

Start -> Settings -> Control Panel there is an option for Regional and Language Options. By clicking on this, there is a button which will install East Asian languages such as Japanese.

and then when should export the data as 'Other Delim Char' with the file name as doc extenstion (example: extract.doc).

Normalization

So, what is normalization?

Basically, it's the process of efficiently organizing data in a database.

There are two goals of the normalization process: eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't be discussed in this post.
Before we begin our discussion of the normal forms, it's important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it's extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let's explore the normal forms.
1. First normal form (1NF) sets the very basic rules for an organized database: Eliminate duplicative columns from the same table. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
2. Second normal form (2NF) further addresses the concept of removing duplicative data: Meet all the requirements of the first normal form. Remove subsets of data that apply to multiple rows of a table and place them in separate tables. Create relationships between these new tables and their predecessors through the use of foreign keys.
3. Third normal form (3NF) goes one large step further: Meet all the requirements of the second normal form. Remove columns that are not dependent upon the primary key.
4. Finally, fourth normal form (4NF) has one additional requirement: Meet all the requirements of the third normal form. A relation is in 4NF if it has no multi-valued dependencies. Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

Some Intresting Sql Queries

Query to retrieve 1,4,7,10.....rows from a table

SELECT a.* FROM emp a WHERE (ROWID, 1) IN (SELECT ROWID, MOD (ROWNUM, 3) FROM emp);

--------------------------------------------------------------------

Query to print Rupees in words
SELECT sal "Salary ", (' Rs. ' (TO_CHAR (TO_DATE (sal, 'j'), 'Jsp')) ' only.' ) "Sal in Words" FROM emp

--------------------------------------------------------------------

Query to print the calender for the year

SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
"Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,
MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sun",
MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Mon",
MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Tue",
MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Wed",
MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Thu",
MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Fri",
MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sat"
FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y')) GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

--------------------------------------------------------------------

Use of Order by clause for records with datatype as character

We generally cannot order the records for a column with character data type. Please use the following trick to use the order clause:

select employee_number from employees
order by lpad(employee_number,100);

Using the lpad the zeros would be appended and then sql will treat them as numbers and the employee number would be sorted.

--------------------------------------------------------------------

Printing Fibonacci series in PL/SQL


DECLARE
RESULT NUMBER := 1;
previous NUMBER := -1;
l_sum NUMBER;
n NUMBER;
l_in NUMBER := 10;
BEGIN
FOR n IN 1 .. l_in
LOOP
l_sum := RESULT + previous;
previous := RESULT;
RESULT := l_sum;
DBMS_OUTPUT.put_line (l_sum);
END LOOP;
END;

Other ways to print the Fib series is as follows:

CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN -- terminating condition
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2); -- recursive call
END IF;
END fib;

/

-- Test Fibonacci Series:
SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;


--------------------------------------------------------------------

Demonstrate simple encoding and decoding of secret messages

SELECT TRANSLATE(
'HELLO WORLD', -- Message to encode
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/

SELECT TRANSLATE(
'85@@%._%*@4', -- Message to decode
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/

Computing the Factorial of a number (n!)

CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
/

-- Test n!
SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;

sql to print string vertically
SELECT SUBSTR ('&&String', ROWNUM, 1)
FROM all_tables
WHERE ROWNUM <= LENGTH (TRIM ('&STRING'));

Procedure to reverse a string
SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin

5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /

SQL> set serverout on
SQL> exec rev('Java')
avaJ

Display the PL/SQL Dependency Tree
SELECT lvl, u.object_id, u.object_type, LPAD (' ', lvl) || object_name obj
FROM (SELECT LEVEL lvl, object_id
FROM SYS.public_dependency s
START WITH s.object_id =
(SELECT object_id
FROM user_objects
WHERE object_name = UPPER ('&OBJECT_NAME')
AND object_type = UPPER ('&OBJECT_TYPE'))
CONNECT BY s.object_id = PRIOR referenced_object_id
GROUP BY LEVEL, object_id) tree,
user_objects u
WHERE tree.object_id = u.object_id
ORDER BY lvl

When prompted, enter the OBJECT_NAME of the object whose dependencies you want to identify.

The OBJECT_NAME can be a PACKAGE, PACKAGE BODY, or PROCEDURE. The OBJECT_NAME is the name of the object at the root of the tree.

SQL script to lists all the profile settings (all levels)
SELECT pot.user_profile_option_name "Profile"
, DECODE( a.profile_option_value
, '1', '1 (may be "Yes")'
, '2', '2 (may be "No")'
, a.profile_option_value) "Value"
, DECODE( a.level_id
, 10001, 'Site'
, 10002, 'Appl'
, 10003, 'Resp'
, 10004, 'User'
, '????') "Levl"
, DECODE( a.level_id
, 10002, e.application_name
, 10003, c.responsibility_name
, 10004, d.user_name
, '-') "Location"
FROM applsys.fnd_application_tl e
, applsys.fnd_user d , applsys.fnd_responsibility_tl c
, applsys.fnd_profile_option_values a , applsys.fnd_profile_options b
, applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+) AND a.level_value = e.application_id
(+)
AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
ORDER BY "Profile", "Levl", "Location", "Value"

Important questions to be remembered by a Oracle Consultant

What is Set of Books ? What are the four conditions when you change your SOBs?

Chart of Accounts, Currency & Calendar
It is similar to the bank passbook, used to record all the financial transactions. There could be one primary set of books and many reporting set of books. Defining COA, currency and calendar are pre-requisite to define the SOB.

What is an Invoice? How many types of invoices ar...
There are 9 types of Invoices in AP: Standard, Credit Memo, Debit Memo, Prepayment, Withholding Tax, Mixed Invoice, Expense Report, PO default and Quick Match.
There are 6 types of Invoice in AR : Invoice, Credit Memo, Debit Memo, Chargeback, Deposit and Guarantee.

What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

Set ups need to approve invoice in AP
For approving an invoice in AP we have to raise one invoice for raising an invoice we have some mandatory fields like supplier, supplier number, Payment terms, distribution sets, date, payment method, bank, payment document etc. After defining all the above we can raise a invoice, There's no need of defining the approval group it s not a mandatory,

What is a profile option? What are the types?
The profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile's setting.
1) User Level
2) Responsibility Level
3) Application Level
4) Site Level.

What are _ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.

What is descriptive flex field and what is the use
Descriptive Flexfileds r used to add additional informations, and these values r stored to the column attributes. Go to sysadmin application-flex fields-descriptive-segments
What is a FlexField? What are Descriptive and Key...
A flexfield is made up of sub-fields or segments.. A flexfield appers on ur form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values..
Two types of Flex field..
Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want wothout programming.Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities..Oracle app store these codes in key flexfields..
Descriptive Flexfield: They provide customizable "expansion space" on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

What Credit memo / Debit Memo
Both Credit and Debit Memo are used for adjusting the suppliers balance and both is a negative amount. Debit Memo is created by you and send to the supplier and credit memo is recieved from the supplier and record it.

What is a request Sets? how to create a request Se..
Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from System administrator responsibility.

What are the types of Concurrent Managers?Ca...
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

What is Multiple Organization technical architecture?
Multi Organization :Using a single installation of any oracle application product , to support any number of an organization even it has different Set Of Books Bussiness Level(It Secures Human TransactionInformation) Set Of Books(It secures all Transaction Information In Oracle Genral Ledger) Leagel Entry(All leagal Information in the Organization) Operating Unit(It Uses Order management, Cash managment,AR,AP...it may be Sales Office , Division,Department) Inventry Organization(Inventry Details)
before multi org existed, we could have only one bussiness group,set of books, legal entity, operating unit in one installation of oracle E-bussiness suite, and now with the multi organisation structure in place, we can have multiple bussiness groups, set of books, legal entity and operating unit and the best part is once these are set up intercompany accounting is automatically taken care of, say for example i have Two inventory organisation, and these use a common set of books(to start with) , now if we have sales order on one inventory organisation A1 and if that item is not available in A1, and we have inventory for the item in Inventory org A2, we need to take the order in A1 as internal order and run the order import concurrent program and for the item in inventory A1(org assignment) we need to set up the source(purchase tab) as inventory organisation A2,and also specify shipping network between A1 and A2, and once we have run the order import program and Oracle applications now imports the order to organisation A2, with the ship to address as A1 location. and after you perform pick release and pick confirm process, and run auto invoice in A2, it automatically sends invoice to inventory organisation A1 and now we can receive the item in Inventory organisation A1 from organisation A2.
and all transactions are taken care of, courtesy multi organisation structure.

What are different period types ?
Year Quarter Month Week

What are the different types of files used in SQL
Different types of files are Data File,Control File,Discard File,Bad file

What is Oracle Financials?
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
o Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
o Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
o Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
o Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
o Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
o Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
o Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
o Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
o Oracle Revenue Accounting: Gives an organization timely and accurate revenue and flexible commissions reporting.
o Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What is the difference between Fields and FlexFields?
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

Difference between Conversions and Interfaces.
Conversion is to bring the data from other (non-OraApps) system to Oracle-Application system. This is one time activity (bring account details, transactions, orders, receipts and so on). So conversion itself is a project.
Interfaces are Concurrent PRograms/Program sets (pl/sql, pro*C, unix scripts, executables) . Interfaces are basically of two types (Oracle supplied (vanila programs eg:Autoinvoice in AR) and created by developer).
Hope this clarifies the concepts.
Conversion means one time activityinterface means periodic activityexample:- to transfer the data old version to new version it is called conversion to transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ........

Which module is not a multiorg?
General Ledger and CRM Foundation Modules

What are the types of Concurrent Managers?Can we delete a Concurrent Manager?
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

Trading community Architecture(TCA)
ICA (Internet Computing Architecture)

What are AP setup steps ?
setup---->suppliers....>invoices..>payments.....>reports......>periods.....>transfer to GL

What does US mean in appl_top/au/11.5.0/reports/US?
US is the language directory specifying that the source files to be placed under this dir is for English/American Language
This is the standard of apps directory structure that for very language you implement oracle apps there should be a language specific folder

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are the Back ground processes in Oracle and what are they.
There are basically 9 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 Database buffer cache to Data Files.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 log 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 peforms 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)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statments 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 T.C.S in Programming Language.e.g, Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.

Key Words Used in Oracle
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. Savepoints can be used to divide a transactino 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 stament. 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 Explcit cursor for a multi row 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 Buffer 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.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.

What are Procedure,functions and Packages
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

What are Database Triggers and Stored Procedures
Database Triggers :: 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 cannot 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 combinatons
At statment 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 Compiled 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.

How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot 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 cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.

What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
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 execute a query

What are the Different Optimisation Techniques
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 assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Restrict '&' in Sql*Plus
sql> set define off
This will restrict the use of '&' in the sql*plus for that particular session