ERP3

115

Order Management Flow:

Booking Order: Go with the Navigation to open sales order window

Orders, Returns_Sales Orders. Then we will get the Sales Order – (NEW) Form as

shown below.

Note: When we open the sales order form the order is created and the status of the order is in

Entered state after that we update the order only. Don’t think that the order is created when we

click on the Book Order button.

Enter the customer name which exists in the database here we take CDS, INC as

example. In this form there are two mandatory fields those are bolded in the figure

above. Here when we click save then we will get the Order_Number By moving to the

Line-items tab we will get another view of the screen given below.

116

Now enter the Ordered Item and Quantity. To check weather the Ordered_item is

existing then go with Availability button. if we book the order with out the availability of

item then the backorder will be created automatically. The Availability window is shown

below.

In this window the ATP Details button is Available To Promise that means how

many items can we give to the customer and Global Availability button is used for check

weather there is availability of the item in any other ware house or not. By clicking on

the Global Availability button we will get this dialog box.

_ Here in this stage two tables will get affected. The complete header information

will store in the OE_ORDER_HEADERS_ALL and line information will store in the

OE_ORDER_LINES_ALL now the status of the line is awaiting shipping.

Then go with Book Order button to book the Order then the status of your order is

changed to BOOKED from the ENTERED state here WSH_DELIVERY_DETAILS table will

be affected. Copy the Order_Number then close the Sales Order Form. Now go with

navigation to pick Release the order.

Shipping_Release Sales Orders_Release Sales Orders

Then we will get another window “Release Sales Orders for Picking “

117

Enter the Order Number which we created in the Sales Order form above and go

with online button. Then we will get the message that Online completed successfully.

Then go with the navigation. Here the status is in pick release the tables affected are

MTL_RESERVATIONS and MTL_ONHAND_QUANTITIES.

Shipping_Transactions

Then we will get the Query Manager form shown below in the form enter the Order

Numbers fields

118

Then go with the find button we will get now Shipping Transactions Form as shown

below.

Go with Delivery tab and change the Actions to Ship Confirm and go with

Go(B) Button then other dialog box will open.

Now a document set will run to view the result go with view Requests. Here five

programs will run those are Interface Trip Stop, Commercial Invoice, Packing Slip

Report, Bill of Lading, Pick Slip Report. These all programs must be completed normal

other wise our order go wrong.

119

Creating Invoice: To create the invoice after all the five programs completed normal

we have to run manually a request named “Workflow Background Process”.

After we submit the request Workflow Background Process then “Auto Invoice

Import Program” will run automatically and the Invoice will generate based on the

reference of the Order_Number. Here RA_CUSTOMER_TRX_ALL and

RA_CUSTOMER_TRX_LINES_ALL will get affected.

120

After the auto invoice program status come to completed normal then go with the

following navigation to view the invoice.

Receivables_Transactions_Transactions

Then we will get the Transactions(Vision Operations) Form as shown below. By this

window we can retrieve the Invoice Number. Press F11 by enters into the query mode

we have to enter the Order number in the Reference field. Press ctrl+F11 to retrieve the

invoice number.

To view the balance of the customer goes with balances button according to the

requirements. The given below is the balances screen where the customer payment

details are in this window. Based on the amount He pay we made a receipt for him. Here

according to the below form the customer has to pay 100,000 we are making receipt for

that amount only if he pay.

121

Making Receipts: After taking amount from the customer, we have to make a receipt.

Copy the Invoice number and close the Transactions form. Now go with the following

navigation.

Receivables_Receipts_Receipts

Then we get a Receipts form. Enter the Invoice number at the Receipt number field

and Trans number which we round in the below figure observe that because based on

the invoice number only we have to raise the receipt to the customer.

After entering all the mandatory fields in the form then go with Applications

button. Then we get Application window just save it then your receipt is generated based

on the Invoice Number.

Here in the Applications form there Chargebacks and Adjustments buttons. If there

any Adjustments of any reductions in the amount then we go with Adjustments button.

122

Transfer to General Ledger:

Run the request General Ledger Transfer Program to create a general in GL and also

to post it. The navigation for this is Interfaces _ General Ledger in the Receivable

Responsibility.

Then it will ask for the Request, enter the request name as

General Ledger Transfer Program then it will ask for the parameters the given below

screen will appear. The mandatory parameters for this request are GL Posted Date and

Post in Summary.

By clicking on the OK button then the request will be submitted then go for menu view

requests to view the status of the request which we submitted then the current created

receipt will be submitted to the GL successfully.

123

Returning Order: Enter the company name and other details same like booking the

order as we mentioned above. There is a change in the Line items tab. Change the line

type as Return Receipt because we taking returns from the customer.

Now go with Returns tab in the Line Items tab. The Returns view is shown below.

Here the Return Reason is the mandatory column on what purpose they are returning

the goods we have to enter here we take Damaged goods. Now go to the reference field

here rounded in the figure.

124

Then another dialog box will open given below. Enter the field’s reference type as

Customer PO or Invoice or Sales Order or Serial number. Here we take Customer PO as

reference we have to enter the Customer PO when we book the sales order. At the same

time when we choose the reference as Invoice or any other options we mentioned above

also the same process.

By Pressing ok here and click on the Book Order button then the Return order is

booked successfully message will be displayed.

Now go with the navigation Purchasing _ Receiving _ Receipts then we will ask

for the choose the Organization here we will select M1 as we are working with M1

organization.

Then another window Find Expected Receipts(M1) will open given below. Go to the

Customer tab enter the order number which we create there for the sales return order.

After entering the RMA Num (Return Order Number).

125

Go with the find button then another window Receipts(M1) will open. Check the

rounded check box and enter the mandatory field Sub inventory and other if necessary

then go with save button then automatically the receipt number will be generated.

Click on the Header button then receipt header form will be open. Now the receipt

number is generated then close the form.

When the Receipt was generated then two Requests will run in the background

automatically they are shown below in the Request window.

126

Order to Cash Flow with Order Management and

Process Inventory

Entering and managing orders is performed in Order Management. Shipping Execution

allows you to plan and confirm your shipments. Inventory to satisfy your order

requirements is allocated and picked in Process Inventory.

The following diagram illustrates the components of the Order Management for Process

flow and is described in the following steps. Note that this is a sample flow and can be

modified.

1. Sales orders are entered and booked through Order Management. Reserving the

order creates a high-level allocation in Process Inventory.

2. Before an order can be picked and shipped, it must be pick released. The Pick

Release process can invoke automatic inventory allocation and creates a process

move order.

3. Process move orders allow you to manually assign available Process Inventory to

a move order or pick from a list of available lots. Lines that have been

automatically allocated may be viewed and edited. Once an order line is allocated

(detailed), it is pick confirmed.

4. Deliveries and trips are created in Shipping Execution. Containers can also be

used.

5. Once you have verified the allocation of inventory to an order, it needs to be Pick

Confirmed. This step can be automated or done through the process move orders

form in Process Inventory. After the pick confirm, allocated inventory is marked

as staged.

6. Ship confirm is the final process in Shipping Execution which records the actual

shipped amounts and creates backorders, if necessary. At the completion of ship

confirm, on hand inventory is decremented for the shipped quantity.

7. Auto Invoicing allows billing of confirmed shipments. This process can be setup to

run automatically upon shipment of an order or be invoked manually. Invoicing

and cash receipt are handled within Oracle Receivables.

8. The final step in the process is the running of the subsidiary ledger update to

create the entries for Inventory and Cost of Goods Sold.

127

Diagrammatic Representation

128

Order Management

Table Relation Diagram

1. User Parameters

Item No From

Item No To

Customer From

Customer To

Order No From

Order No To

Customer Class from

Customer Class To

2. HZ_CUST_ACCOUNTS

Account_Number (Customer

No)

Party_id

Customer_class_code

cust_account_id

5.MTL_SYSTEM_ITEMS

Organization_Id

Inventory_Item_Id

Segment1 (Item_Number)

Description (Title)

Inventory_item_status_code

l. Inventory_Item_Id= si.Inventory_Item_Id

l. Ship_from_org_id si.Organization_Id

3.OE_ORDER_HEADERS_ALL

Order_number (Order No)

Header_id

4.OE_ORDER_LINES_ALL

Inventory_item_id

Cust_Po_Number (Cust PO NO)

Sold_to_org_id

Line_id

Open_Flag=’Y’

Party_id

Sysdate-requested_date (Days Old)

Ship_from_org_id

Header_id

Order_No

5. WSH_DELIVERY_DETAILS

Organization_Id

Line_Id

Requested_Quantity (BO Qty)

Release_Status = ‘B’

Requested_quantity*l.unit_selling_pr

ice (BO Amt)

Line_Id

Organization_Id

sold_to_org_id=hca.cust_account_id

Customer Number

Customer_cl

ass

OE_PRICE_ADJUSTMENTS

Line_id

List_header_id

Line_id

QP_LIST_HEADERS_B

Attribute1=’DM’

List_header_id

List_header_id

List_header_id

QP_LIST_HEADERS_TL

List_header_id

Name (Ad Key)

MTL_ITEM_STATUS

Inventory_item_status_code

Description (Pb Status)

Item

no

Inventory_item_status_code

HZ_PARTIES

Party_Name (Customer

name)

Party_id

Party_Id

OE_ORDER_HOLDS_ALL

Released_flag='N'

Hold_id

Line_Id

OE_HOLD_DEFINITIONS

ATTRIBUTE1='YES'

hold_id

Line_id=line_id

header_id=header_id

OE_HOLD_SOURCES_ALL

Hold_source_id

Hold_id

Hold_source_id= Hold_source_id

Hold_id=hold_id

Party_Id

129

Flow How the Tables Effected

ENTERED:

NAV-> ORDERS. RETURNS -> SALES ORDERS

SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = 56728

-- HEADER_ID = 94100 (FLOW_STATUS_CODE = ENTERED, OPEN_FLAG=Y,

BOOKED_FLAG=N)

SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID = 94187

-- LINE_ID = 195947(FLOW_STATUS_CODE=ENTERED)

NOTE: - If U have set the auto schedule option your order should have a schedule date at this

point.

BOOKED:

NAV-> ORDERS. RETURNS -> SALES ORDERS -> HIT BOOKED TAB

SELECT * FROM OE_ORDER_HEADERS_ALL WHERE ORDER_NUMBER = 56728

-- HEADER_ID = 94100 (FLOW_STATUS_CODE = BOOKED, OPEN_FLAG=Y,

BOOKED_FLAG=Y)

SELECT * FROM OE_ORDER_LINES_ALL WHERE HEADER_ID = 94100

-- LINE_ID = 195947(FLOW_STATUS_CODE=AWAITING_SHIPPING)

SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100

-- (RELEASED_STATUS = R)

PICK RELEASED:

NAV-> SHIPPING -> RELEASE SALES ORDERS -> RELEASE SALES ORDERS

SELECT “BASE ON RULE” AS –> STANDARD

PROVIDE THE “ORDER NUMBER”

THEN HIT “EXECUTE NOW”

HERE U CAN CHECK THROUGH THE FRONT END IN LINE LEVEL STATUS WILL BE

“PICKED”.

SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100

-- (RELEASED_STATUS = Y)

SELECT * FROM WSH_PICKING_BATCHES WHERE ORDER_HEADER_ID = 94100

SELECT * FROM WSH_NEW_DELIVERIES WHERE SOURCE_HEADER_ID = 94100

SHIP CONFIRMED:

NAV-> SHIPPING -> TRANSACTIONS

SELECT * FROM WSH_DELIVERY_DETAILS WHERE SOURCE_HEADER_ID = 94100

-- (RELEASED_STATUS = C)

130

EXAMPLE:

select

ooh.order_number,

ooh.ORDERED_DATE,

ooh.FLOW_STATUS_CODE,

ool.line_number,

msi.SEGMENT1,

msi.DESCRIPTION,

ool.ordered_quantity,

ool.UNIT_SELLING_price,

ool.UNIT_SELLING_price*ool.ordered_quantity total,

ooh.sold_to_org_id,

ooh.ship_to_org_id,

ooh.invoice_to_org_id,

ool.inventory_item_id

from

oe_order_headers_all ooh,

oe_order_lines_all ool,

hz_cust_accounts hca,

mtl_system_items msi

where

ooh.header_id=ool.header_id

and ooh.sold_to_org_id=hca.cust_account_id

and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID

and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID

and ooh.order_number=56308

order by ool.line_number

131

TCA – Trading Community Architecture

Parties are entities, of type Person, Organization, or Group, that can enter into business

relationships.

A relationship represents the way two entities interact with each other, based on the role

that each entity takes with respect to the other. For example, the employment

relationship between a person and an organization is defined by the role of the person as

the employee and the organization as the employer.

Relation ship type

Each relationship phrase and role pair belongs to a relationship type, which categorizes

the types of relationships that you can create.

Relationship Group

Relationship groups are used to determine which relationship roles and phrases are

displayed in specific application user interfaces. Groups can also be used to categorize

roles and phrases for other functional uses.

TCA Registry

The TCA Registry is the central repository of party and other information for all Oracle

applications. The party information includes details about organizations and people, the

relationships among the parties, and the places where the parties do business.

Hz_parties –

Party_id -- This table mainly stores the party_id, party_number,

Party_type(Person/Organization) and the name(First Name/Last Name and

Party_name(first-last name together)) of the party/customer. The first name or last

name should be given to create a party.

Hz_cust_accounts/Hz_cust_accounts_all –

This has mainly the party_id and Cust_account_id -- (called as Customer_id)

Account_number -- (called as Customer_number)

(The hz_cust_accounts_all(till version 11.5.9), is not populating the org_id column. So

this table is anyway meaningless for these versions. Not sure though there is a set up

related to it which can enable this field.)

This table can have multiple records pertaining to one party_id. This means

a party can have multiple accounts. I.e.; ,we can have multiple cust_account_ids created

for a single party in hz_cust_account table. And also under each cust_account_id we can

have multiple addresses,

To create another customer_account(different cust_account_id), select the customer

with the Name from the standard customer creation search screen,(Take care not to

select the customer from LOV, since it directly selects the

cust_account_id/account_number and once you press OK, you get only that

cust_account_id/account number and not all the customer accounts. So enter manually

the customer name, press OK and this takes you to the screen,

132

Select the top most records. Here we can see 4 records. 1st is the Main Line, which needs

to be selected for creating new customer accounts for this customer. We can see that

there are 2 different customer numbers(account number, in this screen cust_account_ids

won’t be shown) here(1441, and 1442). 1442 is repeating because that account is

having 2 different sites(address sites).

Hz_cust_acct_sites_all –

HZ_CUST_ACCT_SITES_ALL stores information about customer sites. One customer

account can have multiple sites. The physical address is maintained in HZ_LOCATIONS.

Gives you all the sites or address defined pertaining to an account.

(although the cust_account_id is same, the org_id's might be different.)

[ If the records are related to an order, you can take the ship_to_org_id, which is

nothing but the cust_account_id related to the Bill_to address and link it with the table,

hz_cust_accounts (using cust_account_id) and then link it with the cust_account_id of

hz_cust_acct_sites_all table like this,

FROM OE_ORDER_HEADERS_ALL OH,

HZ_CUST_ACCOUNTS HCA,

HZ_CUST_ACCT_SITES_ALL HCAS,

hz_cust_site_uses_all HCSU

WHERE OH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID

AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID

AND HCAS. CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID

AND HCAS.BILL_TO_FLAG = ‘P’

Hz_cust_site_uses_all --

HZ_CUST_SITE_USES_ALL stores information about site uses or business purposes. A

single customer site can have multiple site uses, such as bill to or ship to, and each site

use is stored as a record in this table.

Link this table with hz_cust_account_site_all with the field, CUST_ACCT_SITE_ID. Here

is where the and each address can have one(only one primary) Bill_to or Ship_to

address. The primary bill_to, ship_to will be the ones which are normally used for ‘site

use’ level transactions. For one cust_account_id with different addresses,(see it in

133

hz_cust_acct_sites_all table for these different address lines), we can make only one

bill_to or ship_to as primary.

If we get multiple records pertaining to an account, for a Bill_to, check for the field,

BILL_TO_FLAG = 'P' (Means primary). IF we have multiple address/site for a particular

cust_account_id, Only one bill_to address can be 'P', under a cust_account_id, although

we can define multiple bill_to addresses(Note : one address/site can have only one

bill_to)and all the transactions through the system will be done with the primary bill_to

address.

Hz_contact_points –

This table stores the contact details of the customer. The contact details are stored

against a site, if owner_table_name is equated against the hz_party_sites table. If it is

equated against the hz_parties then the contacts are against the customer not against

the customer site.

If a contact point is created against a customer site(address).

SELECT LTRIM(hcp.phone_country_code||'-'||hcp.phone_area_code||'-

'||hcp.phone_number,'-') Home_Phone, hcp.PRIMARY_BY_PURPOSE,hcas.org_id,

hcas.party_site_id,hca.cust_account_id,hcas.cust_acct_site_id,

hcp.CONTACT_POINT_ID,hcp.CONTACT_POINT_TYPE,hcp.PHONE_LINE_TYPE,

hcp.OBJECT_VERSION_NUMBER

FROM hz_parties hp,

hz_party_sites hps,

hz_cust_accounts hca,

hz_cust_acct_sites_all hcas,

hz_contact_points hcp

WHERE hp.party_id = hps.party_id

AND hca.party_id = hp.party_id

AND hp.party_type IN ('ORGANIZATION', 'PERSON')

AND hp.status = 'A'

AND hcas.party_site_id = hps.party_site_id

AND hca.cust_account_id = hcas.cust_account_id

AND hcp.contact_point_type = 'PHONE'

AND (hcp.OWNER_TABLE_NAME = 'HZ_PARTY_SITES' AND hps.party_site_id =

hcp.owner_table_id)

AND hcp.phone_line_type = 'TELHOME'

AND hcp.status = 'A'

If a contact point is create against a customer.

SELECT LTRIM(hcp.phone_country_code||'-'||hcp.phone_area_code||'-

'||hcp.phone_number,'-') Home_Phone, hcp.PRIMARY_BY_PURPOSE,

hca.cust_account_id,hcp.CONTACT_POINT_ID,hcp.CONTACT_POINT_TYPE,hcp.PHONE_L

INE_TYPE,hcp.OBJECT_VERSION_NUMBER

FROM hz_parties hp,

hz_cust_accounts hca,

hz_contact_points hcp

WHERE hca.party_id = hp.party_id

AND hp.party_type IN ('ORGANIZATION', 'PERSON')

AND hp.status = 'A'

AND hcp.contact_point_type = 'PHONE'

AND (hcp.OWNER_TABLE_NAME = 'HZ_PARTIES' AND hp.party_id =

hcp.owner_table_id)

AND hcp.status = 'A'

HZ_customer_profiles --

There will normally be a default profile attached against a customer as soon as the

customer is created.

134

Since the default profile is attached against the customer(and not against the site_use

level), when you query the hz_customer_profiles tables we can see that that site_use_id

for this particular profile would be null.

If the profile is created against the customer site_use level, then the corresponding

site_use_id will be populated for this customer profile record in the hz_customer_profiles

table.

See the below query for reference,

[ select hcp.CUST_ACCOUNT_PROFILE_ID,

hcp.OBJECT_VERSION_NUMBER,

hcp.cust_account_id,

hcp.ATTRIBUTE1 LTV_Veh_sales,

hcp.ATTRIBUTE2 LTV_Number_Of_Veh,

hcp.ATTRIBUTE3 LTV_Service_Sales,

hcp.attribute4 LTV_Service_In,

hcp.attribute5 Cust_Cate_RFM_Veh,

hcp.attribute6 Cust_Cate_RFM_Service_In,

hcp.ATTRIBUTE7 Customer_Remark,

hcp.ATTRIBUTE8 Latest_Vehicle_Sales_Date,

hcp.ATTRIBUTE9 Latest_Service_In_date,

hcp.ATTRIBUTE10 Latest_Contact_Date,

hcp.ATTRIBUTE11 Expected_Contact_Date,

hcp.ATTRIBUTE12 Expected_Sales_Date,

hcp.ATTRIBUTE13 Expected_service_Date,

hcp.ATTRIBUTE14 Hobby1,

hcp.ATTRIBUTE15 Hobby2

FROM

hz_customer_profiles hcp, hz_cust_acct_sites_all hcas,

hz_cust_site_uses_all hcsu where hcas.cust_account_id = hcp.cust_account_id and

hcas.cust_acct_site_id = hcsu.cust_acct_site_id

and hcp.site_use_id = hcsu.site_use_id

and hcp.status = 'A' ]

135

Process that how to Create an Item:

Creating an Item: As the first step in creating the item go to the Inventory

responsibility and in that master items screen. The navigation is

Items _ Master Items. The given below is the Master items window.

Note: Before you got the master items screen you will ask for the choose organization screen then

you get this screen. Here we choose the “V1” so, the items was set to V1 by default as the master

organization. If we want we can assign this item to any other child organizations see the process in

the coming steps.

Enter all the fields like item, description, Long Description such fields as per the

requirement. After go to the Tools menu and in that go with Copy From option in that as

we shown in the below figure. Then we get the Copy from window.

136

Enter the Template name from the list which we want to assign to the current item

BAGS and go with the Apply after that Done. Then the template was copied to the

current item. Then go with the Organization Assignment option as we round in the given

below figure then the view of the window will be changed as below.

Assign the item to the child organizations what ever you want. Then go with the save

button then the item was created successfully. Now we have to set the price list for the

item then only we can view the item in the sales order like that. Now go with the

Order Management responsibility. The navigation for the price list setup is

Pricing _ Price Lists _ Price List Setup. Then you get the Advanced Pricing window.

Note: There are different price lists “Corporate” is default when we are creating the sales order so,

we are adding our item to the “Corporate” price list. If we want we can create new price list as per

the requirements.

After getting the Pricelist window then go with F11 query mode gets the price list

whatever you want and add the item and enter all the mandatory fields such as

Product Context, Product Attribute, Product Value(Item Name), price, to it after that go

with save button. Then the item was successfully added to the price list.

137

Enter all the details correctly before saving. Here in the form the rate of the item is

described as value in that field we have to enter the rate of the item.

Now at this stage the item was created and added to the price list was successful but

the on hand quantity of the particular item is ZERO (0). That means we can’t book the

orders on that item. To increase the on hand quantity of any particular item is given

below. We have to go with the Inventory responsibility and the navigation is

Transactions _ Miscellaneous Transaction. Then you will ask for the organization

138

then select the child organization which we have selected for the item created above.

Then we get the window as below.

Enter the mandatory fields and then go with the transaction lines. Then we get the

window given below. Enter the item name, sub inventory, Amount and quantity as per

the requirement. Here we entered 120 as ex.

When we enter the tab position to the amount then we get the Operations

Accounting window given below. Select the appropriate option from the list of options.

Here we selected the Cash as per our Ex.

After entering the amount field then go with the save button. Then the items quantity

will be increased. Now we can go with the orders now.

139

To check the on-hand-quantity of any item we have to go with the Inventory

responsibility and the navigation is On-Hand, Availability _ On-Hand Quantity. Then

we get the Query Material window we have to enter the item name which we want to

check the quantity and go with the find button below the screen.

After that we get other window in that we get the details of the item on-hand and other.

Here there is the Availability button when we go with that we get the same details in

the short window.

140

SQL QUERY BANK

1. Difference between DCL and DML commands?

DCL commands will commit implicitly where in DML commands we have to

commit explicitly.

2. Table, Rowid, Rownum

Table is a database object, which is having more than one column associated with

its data type.

Rowid is the unique binary address of the row in a table

Rownum it is a temporary number in a memory and was assigned to each row

selected by the statement.

3. Pseudo-Columns

Columns that are not created explicitly by the user and can be used explicitly in

queries. The pseudo-Columns are rowid, rownum, currval, nextval, sysdate, and

level

4. What is a View?

View is Virtual Table, which hold the data at runtime

5. Difference between ordinary view and complex view?

Simple views can be modified easily and these can’t hold the data where as

complex views can’t be modified directly and complex view can hold the data to

modify a complex view we have to use INSTEAD OF TRIGGERS.

6. Forced view

Creating a view on a table, which is not there in the database.

7. Inline view

Inline view is basically a subquery with an alias that you can use like a view

inside a SQL statement.

8. What is an INDEX and what are the types?

INDEX is a database object used in oracle to provide quick access to rows.

9. Synonym

Synonym is an alias name for any database object like tables, views, functions,

procedures.

10.SELECT statement syntax?

SELECT <Column name>

From <table name>

Where <condition>

Having <statement>

Group by <column name>

Connect prior

141

11.What is Constraint? Different Constraints?

Constraints are representators of the columns to enforce data entity and

consistency. UNIQUE, NOT NULL, Primary key, foreign key, Check.

12.Difference between Primary key and Unique + Not Null constraints?

Unique + Not Null is a combination of two constraints and we can use more than

one Unique + Not Null in any table. Primary Key is a single constraint we can use

only one time for a table. It can be a referential key for any column in any table.

13.What is NULL?

Default Value.

14.Dual Table

It is a one row, one column table with value X.

15.Difference between Truncate and Delete?

Truncate will delete all the rows from the table without any condition. It will

commit automatically when it fires Where delete will delete all or specified rows

based upon the condition here we have to commit explicitly.

16.Difference between Char and Varchar2?

Varchar2 is similar to char but can store available number of characters and while

querying the table varchar2 will trims the extra spaces and fetches the rows that

exactly match the criteria.

17.Difference between LOB and LONG data types?

The maximum size of an LOB is 4GB. It will support random access to data where

in LONG maximum size is 2GB. It will support sequential access to data.

18.Single Row functions: It will work on single row and give result for all the rows.

Ex: to_char, to_date etc.

19.Group Functions: It will work on group of rows in a table and gives a single row

result. Ex: Sum(), Avg(), min(), max().. Etc.

20.String Handling Functions?

Instr – it returns the position of the string where it occur according to the

parameters.

Instrb – instr and instrb returns same but in the form of bytes.

Substr – It returns the portion of a string depending on the parameters from and

to.

Substrb – Substr and Substrb returns the same thing but Substrb returns in the

form of bytes

21.Sign: Sign is a function it will take numbers, as inputs and it will give

i. 1 for positive integer

ii. -1 for negative integer

iii. 0 for ZERO

SQL> Select sign(-1234) from dual; O/P: -1

142

22.Differences between UNION and UNION ALL?

Union: The values of the first query are returned with the values of the second

query eliminating the duplicates.

Union All: The values of the first query are returned with the values of the second

query including the duplicates.

23.Difference between NVL and NVL2 functions?

NVL is used to fill a NULL value to known value. NVL2 will identify the NULL

values and Filled values it returns exp3 if it is null otherwise it returns exp2. We

have to pass 3 parameters for NVL2 and 2 parameters for NVL.

24.How can we compare range of values without using the CASE?

By using Decode with in Decode.

25.Can we Decode with in a Decode?

YES

26.Decode and Case Difference?

Case compares a Range of values and Decode will work as if else statement.

27.Difference between Replace and Translate?

Replace is used to replace the whole string and we can pass null values in

replace. Translate is used to translate character-by-character here we have to

pass the three parameters.

28.Difference between where and having clause?

Where used to specify condition and used to restrict the data. Having used to

specify the condition on grouped results and used to filter the data.

29.Difference between IN and EXISTS clause?

EXISTS gives the status of the inner query. If the inner query is success then it

returns true otherwise it returns false and IN will compare the list of values.

30.Difference between subquery and correlated subquery?

Query with in a query is subquery. Inner query will executes first and based on

the result the outer query will be displayed. Correlated subquery outer query will

executes first and then inner query will be executed.

31. What is EXPLAIN PLAN? Syntax?

Explain plan gives the execution plan of the statement. For using this explain plan

we have the plan_table in the database.

Syntax: SQL> Explain plan

Set statemet_id = ‘xyx’

[Into plan_table]

for

DML STATEMENTS;

143

32.SQL TRACE Definition, Process and Query to get the Trace file path?

SQL trace gives the wide range of information and statistics that used to tune a

group of SQL operations. To get the trace file we have to follow some steps

_ Enable the Trace.

_ Run DML statements.

_ Disable the Trace.

_ Convert the Trace file into readable format using the TKPROF.

Trace file Path: SQL> select value from v$parameter

Where

Name = ‘user_dump_dest’;

33.PL/SQL Block structure?

DECLARE

Variable declarations;

BEGIN

Statements;

Exception

Exception block;

END;

34.Anonymous Block?

It is a PL/SQL block with no name and set of statements and cannot be stored in

the database.

35.Procedure and Function Differences?

The procedure may or may not return a value. It will return the values by its own

name and function must and should return a value and it will return values with

function name.

36.Package procedure and Procedure differences?

The package procedure is faster than the ordinary procedure because if we call

the package in a procedure it will load into the SGA memory and then it starts

functioning. The ordinary procedures use this SGA memory at runtime only.

37.Global Variable?

Declaring the variable in the package specification is the Global variable.

38.Triggers and Procedures differences?

Triggers is a database object which will fire implicitly when the event occur and

TCL command will not work in the triggers by using the PRAGMA AUTONOMOUS

TRANSACTION only we can use the TCL commands in triggers.

Procedure is a database object where we have to raise explicitly and TCL

commands will work in the Procedures.

144

39.Dynamic SQL?

If we want to do DML and DDL operations in the PL/SQL block we use this

dynamic SQL. Two types are there in the Dynamic SQL

DBMS_SQL

Execute immediate

40.Difference between DBMS_SQL and execute immediate?

DBMS_SQL is slower process when compare to execute immediate because in the

DBMS_SQL we have to open, parse, fetch and execute to do the DML operations.

It is the version of 7.3.

Execute immediate is earlier version 9i it is a one step process used to execute

fastly.

41.Define a Cursor, Types and its attributes?

Cursor is a private SQL area provided by the oracle engine its main use is to fetch

more than one row.

_ Implicit

_ Explicit

Implicit cursor rises whenever we use DML statements in the PL/SQL block. User

does not have control over it

Explicit cursor used to process select statement, which is used to retrieve more

than one row. We have to open, fetch, close the cursor manually. User has

control over it.

Cursor Attributes:

%ROWCOUNT – It returns the integer that how many rows that previous fetch

returns.

%FOUND – It returns TRUE if the previous fetch returns a row otherwise FALSE.

%NOTFOUND – It returns FALSE if the previous fetch returns a row otherwise

TRUE.

%ISOPEN – It returns TRUE if the named cursor is open otherwise FALSE.

42.PL/SQL Tables

It is an object type table and is used to hold the data at runtime and the size of

the PL/TABLE is dynamic.

43.VARRAYS and PL/SQL Table Differences?

VARRAYS size is fixed and PL/SQL table size is dynamic.

44.Difference between PL/SQL table and Cursor?

Cursor will not use index to fetch the fifth row it has to fetch all the five rows to

give the fifth row.

Cursor will not work on multiple tables.

145

PL/SQL table will use the index. By using the index we can directly fetch whatever

row we want in the table

PL/SQL table can work on multiple tables.

45.What is Pragma?

Pragma is a precompiler directive, which directs the compiler before compilation

of the program.

46.Pragma Autonomous Transaction

Used to split the whole transaction into two parts parent and child, which run

parallely and both have the relation ship

Ex: ITEM ATTCHMENTS CONVERSION

47.Pragma Exception_Init?

Used to associate a user-defined exception with oracle-defined error.

Ex: Bank Account Min Balance

48.Raise_Application_Error?

It is used to create our own error messages, which can be more descriptive than

named exceptions.

Syn: RAISE_APPLICATION_ERROR (error_number, error_message,

[Keep_errors]);

Where error_number is between –20,000 and –20,999, error_message is

the text associated with this error, and keep_errors is a Boolean value.

49.Mutating Error, Mutating table?

It will occur whenever we try to do DML operations on a table, which is effected

by a trigger. The table, which is affected by trigger, is known as Mutating Error.

50.Difference between Package Procedure and Procedure?

Whenever we create a procedure in the package it will compile at that time. When

we call it will execute directly. Where in the ordinary procedure each and every

time it compiles and execute. The main difference is package procedure better in

performance.