Infolinks

Thursday 24 May 2012

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.

No comments:

Post a Comment