Simple Easy Learning

Basic Concepts in Oracle Financials - Important Tables

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:35:00 +0000

Oracle General Ledger

Gl_code_combinations

 Setup > Accounts > Combinations

This table stores the valid account combinations.

The value in your chart of account segments are stored in the columns segment1 to segment30 depending on your application configuration.

For example, say your chart of accounts is
Company – Cost Centre – Account
then segment1 = company, segment 2 = cost centre and segment3 = account.
However, this sequencing of segments is not guaranteed therefore, its best to check your configuration.

Another important column is the account_type which signifies your account is an Asset, Liability, Revenue, Expense or Owners Equity account.

Gl_je_batches

 Journals > Enter

This table stores the journal entry batches. Journal entries are batched in General Ledger.

Some columns of interest includes :

Name
Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Status
Default_period_name
Posted_date
Posting_run_id

Gl_je_headers

 Journals > Enter

This table stores the journal entry headers. There is always two journal lines for each journal header.

Some columns of interest includes :

Je_category
Period_name
Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Posted_flag
Je_source
Name
Status

Gl_je_lines

 Journals > Enter

This table stores the journal entry lines.

The entered_dr and entered_cr stores the amount in the entered currency whereas the accounted_dr and accounted_cr stores the amount in the functional currency.

Other columns of interest includes :

Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Period_name
Status
Description
Reference_1..reference10 (these columns links back to your Subledgers)

For example, for Purchasing transactions

Reference_1 = ‘PO’
Reference_2 = po_headers_all.po_header_id
Reference_3 = po_distributions_all.po_distribution_id
Reference_4 = po_headers_all.segment? (this is the purchase order number)

Oracle Payables

Ap_invoices_all

 Invoices > Entry > Invoices

This table stores all the invoices you enter. For an invoice to be approved, the total invoice amount must be stored in ap_invoice_distributions_all and ap_payment_schedules_all.

Some columns of interest includes :

Invoice_num
Invoice_date
Amount_paid
Invoice_currency_code
Invoice_type_lookup_code
Payment_status_flag

Ap_invoice_distributions_all

 Invoices > Entry > Invoices

This table stores the accounting information for the invoice you have entered. There is one row for each invoice disribution, that is this table corresponds to the Distributions window.

Some columns of interest includes :

Line_type_lookup_code
Dist_code_combination_id (credit entry)
Accts_pay_code_combination_id (debit_entry)
Base_amount (in functional currency)

Ap_checks_all

 Payments > Entry > Payments

This table stores payments to suppliers.

Some columns of interest includes :

Amount (in functional currency)
Check_date
Bank_account_name
Check_number
Payment_method_lookup_code
Payment_type_flag

Ap_invoice_payments_all

 Payments > Entry > Payments

This table stores invoice payments to suppliers. This table is updated when you confirm an automatic payment batch, enter a manual payment or process a Quick Payment. Void payments are represented as a negative of the original payment line.

Some columns of interest includes :

Accounting_date
Period_name
Amount
Payment_num

Ap_payment_distributions_all

 Payments > Entry > Payments

This table stores accounting information for payments. There is at least one CASH payment distribution for each invoice payment. Additional rows may include DISCOUNT, GAIN and LOSS distributions where appropriate.

Some columns of interest includes :

Line_type_lookup_code (CASH/DISCOUNT/GAIN/LOSS)
Base_amount

Oracle Purchasing

Po_vendors

 Supply Base > Suppliers

This table stores supplier information.

Some columns of interest includes :

Segment1 (supplier number)
Vendor_name
Terms_id
Vendor_type
Ship_to_location (link to hr_locations for location information)
Bill_to_location (link to hr_locations for location information)

Po_vendor_sites_all

 Supply Base > Suppliers

This table stores supplier sites information.

Some columns of interest includes :

Pay_site_flag
Purchasing_site_flag
Address_line1 to address_line3
City
State
Area_code
Zip

Po_headers_all

 Purchase Orders > Purchase Orders

This table stores the seven types of purchasing documents such as Purchase Order and Blanket Agreement.

Segment1 is the document number (i.e. purchase order number)

Some columns of interest includes :

Agent_id (link to per_people_f for the buyer)
Type_lookup_code

Po_lines_all

 Purchase Orders > Purchase Orders

This table stores purchasing document lines.

Some columns of interest includes :

Line_num
Item_description
Unit_price
Unit_meas_lookup_code (unit of measure)
Quantity
Item_id (link to mtl_system_items for the item number)
Category_id (link to mtl_categories for the category name)

Po_line_locations_all

 Purchase Orders > Purchase Orders

This table stores purchase order shipment schedules and blanket agreement price breaks. A purchase order is closed when QUANTITY is equal to QUANTITY_RECEIVED.

Some columns of interest includes :

Quantity
Quantity_accepted
Quantity_received
Quantity_cancelled
Need_by_date
Ship_to_organization_id (link to org_organization_definitions for the organization code)

Po_distributions_all

 Purchase Orders > Purchase Orders

This table stores the accounting information on a purchase order shipment. This table is used for Standard and Planned Purchase Orders and Planned and Blanket Purchase Order Release.

Some columns of interest includes :

Quantity_ordered
Quantity_billed
Amount_billed
Quantity_delivered
Quantity_cancelled
Destination_organization_id (link to org_organization_definitions for the organization code)
Destination_subinventory

Rcv_shipment_headers

 Receiving > Receipts

This table stores the receiving information. The three receipt sources are Supplier, Inventory and Internal Order. There is one receipt header per receipt source.

Some columns of interest includes :

Receipt_num
Shipment_num
Receipt_source_code
Shipped_date
Ship_to_org_id

Rcv_shipment_lines

 Receiving > Receipts

This table stores information about items that have been shipped and/or received from a receipt source.

Some columns of interest includes :

Line_num
Quantity_shipped
Unit_of_measure
Item_id (link to mtl_system_items for item number)
To_organization_id (link to org_organization_definitions for organization code)
To_subinventory
Shipment_line_status_code (EXPECTED, FULLY RECEIVED, PARTIALLY RECEIVED)
Quantity_received
Quantity_shipped

Oracle Inventory

Org_organization_definitions

 Setup > Organizations > Parameters

This view contains basic information on all inventory organisations.

Some columns of interest includes :

Organization_code
Organization_name
Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Inventory_enabled_flag

Mtl_secondary_inventories

 Setup > Organizations > Subinventories

This table stores all subinventory information for an inventory organisation.

Some columns of interest includes :

Secondary_inventory_name
Description

Mtl_material_transactions

 Transactions > Material Transactions (Inquiry)

This table stores all inventory transactions including cost updates.

Some columns of interest includes :

Transaction_quantity
Transaction_type_id
Transaction_source_type_id
Transaction_source_name

Mtl_transaction_accounts

 Transactions > Material Distributions (Inquiry)

This table stores the inventory accounting information. There are two rows in this table for each transaction in mtl_material_transactions.

Some columns of interest includes :

Transaction_date
Gl_batch_id
Accounting_line_type
Base_transaction_value

Mtl_system_items

 Items > Master Items or Items > Organization Items

This table stores the item definition. An item must exist in an inventory organisation.

Your item number is stored in the columns segment1 to segment20 depending on your application configuration. If you have configured your items to have to segments then you may be using segment1 and segment2

Some columns of interest includes :

Segment1 to segment20
Description
Invetory_item_flag
Purchasing_item_flag
Inventory_asset_flag
Stock_enabled_flag
Invoiceable_item_flag
Shippable_item_flag
So_transaction_flag
Mtl_transactions_enabled_flag
Primary_unit_of_measure

Mtl_onhand_quantities

 On-hand, Availability > On-hand Quantities

This table stores quantity on hand in a location for each item.

Some columns of interest includes :

Date_received
Transaction_quantity
Subinventory_code

Cst_item_costs

 Costs > Item Costs

This table stores the item cost information. Note that there can be multiple costs per item and the actual cost is where the cost type is Frozen.

Some columns of interest includes :

Cost_type_id (link to cst_cost_types)
Item_cost

Oracle Receivables

Ra_customers

 Customers > Standard

This table stores customer information.

Some columns of interest includes :

Customer_name
Customer_number
Status
Customer_prospect_code
Customer_type
Orig_system_reference (for imported customers from an external source)

Ra_addresses_all

 Customers > Standard

This table stores customer address information and your remit-to addresses.

Some columns of interest includes :

Status
Orig_system_reference (for imported customer addresses from an external source)
Address1 to address4
City
State
Postal_code

Ra_site_uses_all

 Customers > Standard

This table stores the customer’s site and site purpose. You must have one row for each address. A customer must have one bill to address for Receivables. A customer must have one ship to address and one bill to address for Order Entry.

Some columns of interest includes :

Site_use_code (BILL_TO, SHIP_TO, STMTS, DUN/LEGAL)
Primary_flag
Status
Location

Ra_customer_trx_all

 Transactions > Transactions

This table stores invoice, debit memo, chargeback, commitment and credit memo header information.

Some columns of interest includes :

Cust_trx_type_id (link to ra_cust_trx_types_all)
Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Terms_id (link to ra_terms)
Trx_number (invoice number)
Trx_date (invoice date)

Ra_customer_trx_lines_all

 Transactions > Transactions

This table stores the invoice, debit memo, chargeback, commitment and credit memo line information.

Some columns of interest includes :

Line_number
Description
Quantity_ordered
Quantity_credited
Quantity_invoiced
Unit_standard_price
Unit_selling_price
Line_type
Extended_amount
Revenue_amount

Ra_cust_trx_line_gl_dist_all

 Transactions > Transactions

This table stores the accounting information for revenue, unearned revenue, unbilled receivables, receivables, charges, freight and tax for each invoice or credit memo line.

Some columns of interest includes :

Amount_gl_date
Gl_posted_date
Account_class (CHARGES/FREIGHT/TAX/REC/REV/UNBILL/UNEARN)
Acctd_amount (functional currency)

Ar_cash_receipts

 Receipts > Receipts

This table stores the payment information.

Some columns of interest includes :

Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Status (APP, UNAPP, UNID, NSF, STOP, REV)
Type (CASH, MISC)
Receipt_number
Amount
Currency_code
Pay_from_customer
Receipt_date

Ar_receivable_applications

 Receipts > Receipts

This table stores accounting entries for cash and credit memo applications.

Some columns of interest includes :

Amount_applied
Line_applied
Tax_applied
Application_type
Display
Gl_date
Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)

Ar_payment_schedules

 Transactions > Transactions and Receipts > Receipts

This table stores all transactions except adjustments and miscellaneous cash receipts. This table is updated when a transaction occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.

Some columns of interest includes :

Amount_due_original
Status
Class (DEP, DM, PMT, GUAR, CM, CB, INV)
Due_date
Amount_due_remaining
Invoice_currency_code
Amount_applied
Anmount_credited
Amount_adjusted

Interview Questions on Oracle AR (Account Receivables)

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:34:00 +0000

Questions in Accounts Receivables

1. How do you create a Chargeback Invoice ?
A. Using Receipts window

2. How do you adjust the amount of advance already received against an Invoice ?
A. Create an Invoice for Deposit and receive an amount against this deposit Invoice. Then Create the regular invoice and in the column of Commitments enter the number of Deposit Invoice. The Regular Invoice gets matched with the Deposit Invoice for the amount of Regular invoice or deposit invoice which ever is lower

3. How do you write off small amounts while accounting the receipt against an invoice?
A. Using Receipts window. You have the button for write offs.

4. How do you Account for bank charges deducted from amount received against an invoice?
A. Using Quick cash window also you can enter receipts. Here you have the option of accounting the bank charges deducted on receipt. However, this has to be enabled by putting the Value in profile option AR: Create Bank Charges = YES.

5. How do you create a credit note against an invoice ?
A. You have separate window to create such credit note. Navigation Transactions => Credit Transactions.

6. How do you adjust a regular Invoice with a Credit Note ? Enter the amount 0 in receipt window and in invoice matching window select the invoice as well as the credit note. This will knock off the invoice against the credit note.

7. What are different types of Receipt Reversals ? What is the difference between them ? What are the accounting entries ?
A. Standard Reversal and Debit Note Reversal. Standard Reversal reopens the invoice matched in that receipt. You can match a new receipt against this invoice.
Debit Note Reversal does not reopens the earlier matched invoice but it creates a new debit note which can be matched with another receipt.

8. When are the following accounts are used
Unbilled Receivable
Unearned Revenue
A. When you use Invoicing Rule, the receivables are accounted on different dates as defined in the rule. Till such time receivables are accounted, the amount is debited to Unbilled Receivables.
If you use Accounting Rule, the revenue is accounted on different dates. Till such time revenue is accounted, the amount is credited to Unearned Revenue..

9. What is the difference between Unidentified Receipt, Unapplied Receipts and On Account Receipts ? What are the Accounting Entries for each of this ?
A. Unidentified Receipts: The Customer is yet to be identified and so receipt is not matched.
Unapplied Receipts: The Customer is identified and entered but the amount is not matched with any of his invoice.
On Account Receipts: The Customer is entered and instead of matching the amount to any of his invoice it is matched with the On Account option. This option is available as first item in the pick list of invoices.

10. What is the difference between earned discounts and Unearned discounts ? How do you account the Unearned discount? What are the accounting entries involved ?
A. The cash discounts are mentioned in the payment terms. Considering the due date and the receipt date the discount is automatically calculated while entering the receipt. If however you want to increase the amount of invoice you can manually increase it. The amount automatically calculated as per terms is the Earned Discount. The manually added amount is the Unearned Discount. This however, depends upon the option given in System Options in Setup.

11. What are the Key Flexfields in AR.

A. Sales Tax Location Flexfield
Territory Flexfield

AR Setup Related

12. What is difference between transaction type and transaction source ?

A. Transaction type can either be Invoice, Credit Note, Debit Note, Deposit. Etc. This also decides whether to account in GL, Whether to consider in receivables, the accounts to be debited, credited, the tax calculation options, and some other options which are defauled.
Transaction Source decides whether the source of entry is manual or automatic. It also mentions whether transaction and batch numbering is manual or automatic.
If automatic the last entered number is to be mentioned to start the automatic numbering. IF the source is automatic, that means the transactions are to be uploaded through interface either from OE or legacy system. Then some other options are to be set.

13. What is AutoAccounting ?

A. This is the account generator in Accounts receivables. This decides accounts for
Receivables
Revenue
AutoInvoice Clearing
Freight
Tax
Unbilled Receivable
Unearned Revenue

From where the accounts are defaulted ?
Sales reps
Transaction Lines
Transaction Types
Taxes
Or you can have Constant values

14. What is AutoCashRule Set ?
This decides the sequence of the invoice matching rules for unmatched receipts entered though quich cash or AutoLockBox(interface). The rules are already defined in the system. Like “Match Payment with Invoice”, “Apply to the Oldest Invoice First”, “Clear the Account”, etc.

15. Where do you attach the Set Of Books ? Can you attach more than one set of books ? if you have more then one set of books then how Receivables is configured ?
You attach the set of books in System Options in Setup. Only one set of books can be attached. If you have more then one set of books then AR is to be set in multi org environment. By specifying the “MO Operating Unit” and the “GL Set of Books Name”.

16. How do you define document numbering for receipts ? can you have different sequences for each Payment Method.
In system administration. You can have different sequences for different payment methods.

Interview Questions on Oracle SCM,AOL,Forms

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:32:00 +0000

1. What is order cycle?

Order cycles describe the processing or routing, through which orders progress. Order cycles contain cycle actions such as Enter, Pick Release or Ship Confirm. Each cycle action has at least one result. For example, results for the action ‘Enter’ include ‘Booked’, ‘Partial’ and ‘Entered’. You can customize your order cycle by defining prerequisites for each action so that Oracle Order Entry performs these actions in the sequence you define. For example, the prerequisite for the action Pick Release could be the action Legal Review and the result Pass. You would not be able to pick release the order until it meets this prerequisite. You can define as many order cycles as you want so you can handle different processing requirements for different types of orders or for orders from different sales channels. For example, you may have one order cycle that requires legal approval that you use for international orders and one without legal approval for domestic orders.

If you make any changes to cycle actions in an order cycle you can determine when those changes take effect by setting the profile option OE: Cycle Action Changes Affect Existing Orders

Tables used are SO_CYCLES, SO_CYCLE_ACTIONS, SO_ACTION_PRE_REQS, SO_ACTIONS, SO_RESULTS, SO_ACTION_RESULTS.

2. List the various actions of a typical order cycle?
Order cycles contain cycle actions such as ‘Enter’, ‘Pick Release’ or ‘Ship Confirm’, ‘Backorder Release’, ‘Receivable Interface’, ‘Inventory Interface’, ‘Complete Line’, ‘Complete order’.

Enetered -> Book -> Demand/ATP (Available to Promise) -> Pick Release -> Shipping -> Inventory Interface -> Receivable Interface -> Close order

3. What is the relation between an order type and an order cycle?

Order Cycle can differ depending on the type of an order For example you may have one order cycle that requires legal approval that you use for international orders and one without legal approval for domestic orders
Order cycle is attached to the order type.

4. What does Inventory Interface do?

Inventory Interface Program populates the interface tables with transactions submitted through Confirm shipment forms. It updates order lines with shipped quantities and updates inventory.

5. What are ATO and PTO?
ATO - Assemble to Order - A configuration you make in response to customer order which includes optional items.

PTO - Pick to Order - A configure to order environment where the option and included items in a model appear on pick slips and order pickers gather the options when they ship the order. Alternative to manufacturing the parent item on a work order and then shipping it.

6. What is RMA?

RMA is permission for a customer to return items. OE allows you to authorize the return of your sales orders as well as sales made by other dealers as long as the item are part of your item master and price list. Receipt of goods previously sold to customer, credit to a customer or replace with identical item.

7. What is Hold?
A feature that prevents an order or order line from processing through the order cycle. You can place hold on any order or order line.

8. What are various types of holds?

Credit check hold, Legal Review hold, Sales Review Hold, Customer request Hold. (so_holds.type_code)

Few of logitechs hold types:
CREDIT This hold is automatically placed on an order invoiced to a customer who fails credit check
GSA This hold is automatically placed on an order which is in violation of GSA
CONFIGURATOR This hold is automatically placed on a Sales Order Line that fails Configurator Validation
Order Management Product on Short Supply
Order Management Product on Quality Stop Shipment
Order Management Awaiting clarification on Order Detail
Order Management Incomplete Ship To
Credit Management Customer/Order on Credit Hold
Order Management EDI order with incorrect pricing - hold until pricing issue resolved
Order Management EDI order with incorrect QTY (Case Pack) - hold until QTY issue resolved
Order Management Customer Requested Hold
Credit Management Manual Credit Hold on Backorder
Order Management EDI order waiting for information
Document Services Pre Release Hold to applied to Prevent Pick Release
Document Services Pre Release Hold to applied to Prevent Backorder Release
Order Management RMA Order Waiting For Information
Credit Management Overdue Payment Hold by Credit Managment
Order Management Product on Short Supply

9. What is a hold Parameter?

A criterion you use to place a hold on an order or order line. Valid hold parameters are customer, customer site, order, and item. (so_order_holds_view_hold.hold_level)

Logitech HOLD_LEVEL
-------------------------------
Customer
Item
Order
Site Use

10. What are various types of discounts?
Discount is a reduction of list price for an item. Fixed price discounts - Final price is contractually fixed regardless of fluctuations in list price. For example: item A has a list price of 100, a fixed price discount specifies a selling price of 90, results in a selling price of 90 even if the list price changes to 110.

Price adjustments - The difference between list price of an item and its selling price. Price difference can have positive or negative impact on price list. Price adjustments which lower the list price are called as price adjustment discounts. It can be for order or order line.

Earned discounts -

11. What is scheduling of orders?

Order scheduling includes assigning demand or reservations, warehouses, shipment dates, lots and sub-inventories to order line, it can be don’t on booked or un-booked orders.

12. How do you calculate value of an order (from tables)?
select
sum((sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) * sol.selling_price)
from so_headers_all sh, so_lines_all sol
where sh.order_number = &order_number
and sol.header_id = sh.header_id

13. What is underlying table for discounts?

SO_DISCOUNTS, SO_DISCOUNT_CUSTOMERS, SO_DISCOUNT_LINES, SO_PRICE_ADJUSTMENTS

14. What is auto-accounting?
An Receivable feature that lets you determine how the Accounting Flex-field for your revenue, receivable, freight, tax, unbilled receivables, and unearned revenue account types are created.

15. What is Auto-invoicing?

A program that imports invoices, credit memos, and on account credits from other system into oracle receivables.

16. What does the table ra_cust_trx_line_gl_dist store?
Stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. This table must have one row for each accounting distribution.

17. What are the underlying tables in various transactions in receivables?

RA_CUSTOMER_TRX, RA_CUST_TRX_LINE_SALESREPS, RA_CUSTOMER_TRX_LINE_GL_DIST, RA_CUSTOMER_TRX_LINES, AR_PAYMENT_SCHEDULES AR_PAYMENT_SCHEDULES, AR_CASH_RECEIPTS, AR_MISC_CASH_DISTRIBUTIONS, AR_CASH_RECEIPT_HISTORY, AR_RECEIVABLE_APPLICATION

18. What are interface tables used during autoinvoicing interface?
RA_INTERFACE_LINES, RA_INTERFACE_SALESCREDITS, RA_INTERFACE_DISTRIBUTIONS

19. If invoice, credit memos and debit memos all get stored in the same table then, how do you come to know which transaction is it?
RA_CUSTOMER_TRX table stores cust_trx_type_id, which identifies the user defined transaction type. Which in turn is stored in RA_CUST_TRX_TYPES. The field TYPE defines the type of transaction, which is linked to LOOKUP_CODE of AR_LOOKUPS table. Basic 6 transaction types are hard coded in AR_LOOKUPS table and are not user maintainable.

20. What is a credit memo?

Credit memo is a document, which partially or fully, reverses an original invoice. You can create credit memos through the Oracle Receivables Enter Credit Memos form or through Auto-Invoice.

21. What is a debit memo?
Debits that you assign to your customer for additional charges that you want to collect. You may want to charge your customers for unearned discounts taken, additional freight charges, taxes, and finance charges.

22. What are the underlying tables for credit memos and debit memos?
RA_CUSTOMER_TRX_ALL

23. What is an accounting Rule?

Accounting rule is a rule that Oracle receivables Auto-invoice uses to specify revenue recognition schedules for your transactions. You can define your accounting rule where revenue is recognized over a fixed or variable period of time. For example, you can define a fixed duration accounting rule with monthly revenue recognition for a period of 12 months.

24. What is the difference between an accounting rule and an invoicing rule?
Invoicing Rule: Rules the Oracle receivables uses, to determine when you bill your invoices. You can bill in advance or in arrears.

25. What are the various features of Oracle Receivables?

Multiple System Options

Use optional batching
Customize to meet your needs:
Accounting Flex-field
Item Flex-field
Territory Flex-field
Sales Tax Flex-field
Descriptive Flex-field
Define any chart of accounts using up to 30 segments
Define your own accounting calendar
Choose from a variety of payment terms:
Split terms
Proxima terms
Terms by amount or percent
Multiple discounts
Choose from tax options:
By location or product
Compounding tax
Tax exemptions
Group multiple, conditional taxes
Sales Tax or VAT environment
User-definable tax hierarchy

Customers

Specify customer relationships
Validate customer addresses
Enter addresses in country-specific formats
Enter an unlimited number of:

Addresses, foreign or domestic
Remit-to addresses
Business purposes
Contact names
Telephone numbers

Create customer credit profiles:

Payment terms
Automatic receipt information
Finance charges

Copy credit profiles
Define sales territories
Default pertinent customer site information to transactions
Query customers using variable criteria

Invoicing

Create sign-insensitive transactions
Number invoices and lines automatically
Recognize revenue over multiple periods using invoice rules
Create recurring invoices
Create consolidated billing invoices based on periods defined by payment
terms
Specify transaction type, including:

Deposit
Guarantee
Invoice
Debit memo

Create credit memos, including:

Full and partial credit
Automatic sales credit reversal
On-account credit

Handle write-offs automatically or manually
Set adjustment limits
View transaction balances
View accounting entries on-line
Print invoices at any time

Collections

Record customer calls
Print customer statements
Create dunning letters based on days overdue or dunning levels
Define unlimited number of user-defined dunning letter templates
Produce statements based on user-definable cycles
Reapply cash
Perform inquires on-line or on the web
Create flexible agings
Drill down to detail by aging bucket
Inquire using external references such as project and order number
View correspondence history on-line
Create centralized statement sites
Create centralized dunning sites

Cash Application

Record payments from third parties
Create chargebacks
Process receipts automatically:

Lockbox transmission
Bills of exchange
Direct debits

Match receipts to transactions by:

Transaction Number
Order Number
Purchase Order Number
Consolidated Bill number
Custom Number

Use AutoCash rules:

Apply to oldest invoice first
Exact match on invoice
Clear past due invoices
Clear the account
Clear Past Due Invoices grouped by Payment Term

Apply payments according to user-defined rule sets
Use earned and unearned discounts
Discount on specified line types
Track bank remittances
Clear bank statement items automatically or manually
Use flexible lockbox formats
Create bank charges automatically
Reapply cash
Reverse multiple receipts in a single step
Process Notes Receivable

Future-dated checks
Promissory Note

Global Accounting

Record sales and VAT taxes with complete tax reporting
Issue letters of credit
Handle bills of exchange and automatically eliminate risk
Use optional sequential numbering
Enter multiple currencies using user-defined currency formats
Process cross-currency receipts
Calculate currency gains and losses
Enter cross-currency deposits
Default tax code from revenue segment of natural account
Enter and print line amounts that include tax
Report in multiple currencies

Reporting

View reports on-line
Report by company
Create accounting reports of agings, receipts, cash application, and
adjustments
Use Report eXchange to customize reports:

Query selected information
Export to other applications

Create flexible agings, including:

Customizable aging buckets
Dispute bucket
Customer and invoice summary
By amount and salesperson

Additional reports:

Transaction registers
Tax reports
Credit and collections reports
Key indicators
Supplier/Customer Netting report
Foreign Exchange Gain/Loss report

26. Does an RMA (order return) automatically generate credit memos?
Yes, Receivable Interface of RMA will generate credit memos. If the RMA Interface results Partially Accepted or Completely Accepted are prerequisites to the Receivables Interface in the order cycle, only quantities of the item that have been received in a sub-inventory are credited. Items, which are received for purposes of inspection, are not eligible to be credited unless they pass inspection and are received into a sub-inventory. Thus if the prerequisite for the Receivables Interface includes RMA Interface - Partially Accepted, then the Receivables Interface creates partial credits corresponding to the accepted quantity that has not already been credited. If the prerequisite for the Receivables Interface is only RMA Interface - Completely Accepted, the Receivables Interface waits until the full quantity is accepted and then creates a full credit. If the RMA Interface is not a prerequisite to the Receivables Interface in the order cycle, the full return quantity entered on the RMA line is eligible to be credited.

27. What does form registration do?
28. Why do you need form registration?
29. If I add certain code to a form and don't register it what will happen?
30. If I add a column to a form and don’t register it what will happen?

After you paint your form, register your form with Oracle Application Object Library. In fact, you must register any form you wish to run under Oracle Application Object Library. By registering your form, you assign it to a particular application and let Oracle Application Object Library add form-level triggers your form needs to support EasyForm and such features as Menus, QuickPick, and help text. You should register your forms again after you make any changes to their structures such as adding or deleting fields or zones. You can reregister your form as many times as you want without harming it.

You can use the Update Form Information form to register any changes you make to the blocks and fields of your form, or to change the name that appears in QuickPicks.

31. What are the various steps in developing a report?

After getting a valid specification, decide on the oracle tool in which report could be developed, .if its SQLreport then -
1. Write SQL statement
2. Build layout
3. Put script in CUSTOM_TOP directory
4. Register it

If its Oracle Reports (6i) then -
1. Build a data model. data model includes query, summary columns, formula columns, placeholders, groupings etc
2. Paint the layout
3. Write triggers to enforce business rules
4. Create parameters
5. Put script in CUSTOM_TOP directory
6. Register it

32. How do you call a user exit?

From Forms: - ?
From Reports: - srw.user_exit ("user_exit_name( parameter1,parameter2,...)");

33. What is anchoring?

Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.

Rules:

1. An object may be anchored to only one other object.
2. Matrix objects, anchors, and the margin cannot be anchored to anything (i.e., they may not be the parent or child object for an anchor).
3. A repeating frame that is the vertical or horizontal repeating frame for a matrix cannot be anchored to another object, but other objects may be anchored to it (i.e., it can be the parent but not the child object for an anchor).
4. Nothing can be anchored to a hidden object (an object with Hidden checked).
5. Moving an anchor also cause the two objects it anchors together to move.
6. Objects cannot be anchored together in such a way that they have a circular dependency. For example:
· Assume that object A and object B are anchored together and object A is the parent. Object B cannot be the parent for another anchor between object A and object B. In addition, object B cannot be the parent for an anchor to object C, if object C is the parent for an anchor to object A.
· Assume that frame A contains field B and field B is above frame A in the editor. Furthermore, frame A is anchored to field C, which is not inside of frame A, and field C is the parent. Field C cannot be the parent for an anchor to field B. Because it is inside of frame A, field B cannot be anchored to field C, if field C is the parent.
7. To copy an anchor, you must select the anchor and the two objects it anchors together. If you select the anchor by itself, nothing will be copied to the paste buffer. If you select the anchor and one of the objects, only the object is placed in the clipboard.
8. An anchor cannot be resized.
9. An anchor must always be on top of the objects it anchors together (i.e., it must be one or more layers above the parent and child objects). Oracle Reports prevents you from moving the anchor to a layer below its parent and child objects.
10. You cannot use Align, Align Objects, or Size Objects from the Arrange menu on anchors.

34. What are format triggers?

Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

Rules:

Caution: The PL/SQL in a Format Trigger is executed each time that Oracle Reports attempts to format the layout object. As a result, format triggers should only contain PL/SQL program units that set formatting attributes (e.g., color and highlighting). You should not perform other actions, such as inserting data in a table, because you cannot predict when or how many times the trigger will fire. For example, if you have Page Protect checked for an object, the object might not be formatted on the logical page where the trigger is fired. In addition, the trigger may be executed more than once.

1. Comments inserted directly into the PL/SQL code must use the PL/SQL comment delimiters.
2. In a Format Trigger, you can read the values of Oracle Reports columns and parameters of the correct frequency (look at the rule below), but you cannot directly set their values. For example, you can use the value of a parameter called COUNT1 in a condition (e.g., IF :COUNT1 = 10), but you cannot directly set its value in an assignment statement (e.g., :COUNT1 = 10). (This restriction also applies to user exits called from the Format Trigger.)
3. Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not supported. If you do this, you may get unpredictable results.
4. You cannot reference columns or variables in the Format Trigger of an object that have a different frequency than the object. For example, if you create a master/detail report, the parent group's repeating frame cannot have a Format Trigger that relies on a value in the child group. For each parent, there may be multiple children. Therefore, at the parent record level, Oracle Reports cannot determine which of the child records to use. You also cannot reference any page-dependent columns (i.e., Reset At of Page) or columns that rely on page-dependent columns in a Format Trigger. The reason for this is that it would result in a circular dependency. That is, the value of a page-dependent column cannot be computed until the report is formatted, but the report cannot be formatted until the Format Trigger is executed.
5. If a Format Trigger returns false and the object does not format, this can cause other objects not to print. For example, if a repeating frame does not format, any objects (fields, boilerplate, frames, or other repeating frames) it encloses would not format either.
6. For repeating frames, the Format Trigger is executed for each instance of the repeating frame. To create a Format Trigger that acts upon all instances of a repeating frame at once, create a frame around the repeating frame and enter a Format Trigger for the frame. If the Format Trigger returns FALSE for every instance of a repeating frame on a logical page, the repeating frame will occupy no space on the logical page and anchors to other objects will collapse (if specified).
7. The PL/SQL in a Format Trigger must return consistent results for the same object. For example, say you have a frame whose Format Trigger returns FALSE when a certain condition is met. If the frame spans two pages, the Format Trigger actually fires twice (once for each page on which the frame formats). The condition in your PL/SQL must return the same result both times the Format Trigger fires. Otherwise, only part of the frame will be formatted (e.g., the part of the frame on the first page formats, but the part on the second page does not).
8. If the Format Trigger on a repeating frame in a matrix report returns FALSE, an entire row or column of the matrix will not format. For example, if an instance of the across dimension repeating frame does not format, the entire column will not format in the matrix.
9. If you want to conditionally change the cell of a matrix, you should put a frame around the field inside the matrix and use the Format Trigger for the frame.

35. What is a user Exit?

A user exit is a routine your form calls to perform application processing. Occasionally, you may need to build your own user exit to perform application processing that is beyond the scope of SQL or SQL*Forms commands. Oracle Application Object Library helps you build a user exit in a third generation language (such as Pro*C) and integrate it into a standard user exit library.

36. How can you increase the performance of reports?
1. Writing straight forward and plain data fetching Queries.
2. Utilizing a correct combination of indices on tables to fetch data.
3. In case of Oracle reports (6i) reduce the number of User exits, format triggers, formula columns, summary columns etc.
4. In case of Oracle reports (6i), instead of writing a complex query, break it up into multiple simple queries and link them up with data-links.

38. What is Explain Plan?
Explain Plan analyzes the query and breaks it up in to the steps which oracle follows to fetch the data.

PURPOSE:

To determine the execution plan Oracle follows to execute a specified SQL statement. This command inserts a row describing each step of the execution plan into a specified table. If you are using cost-based optimization, this command also determines the cost of executing the statement.

39. How did you optimize your SQL after using Explain Plan?
0. Eliminate FULL table scans.
1. Forcing appropriate indices.
2. Rearranging the table names in from clause.
3. Doing away with functions in where clause.
4. Use EXISTS where ever possible instead of sub-queries.

40. What is TKPROF?

TKPROF is a toll by Oracle to format the trace file output. By passing different parameters you can control the contents of the output file.

Use the following arguments with TKPROF:

filename1 specifies the input file, a trace file containing statistics produced by the SQL trace facility. This file can be either a trace file produced for a single session or a file produced by appending together individual trace files from multiple sessions.

filename2 specifies the file to which TKPROF writes its formatted output.

EXPLAIN determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. TKPROF determines execution plans by issuing the EXPLAIN PLAN command after connecting to ORACLE with the user and password specified in this parameter. The specified user must have CREATE SESSION system privileges.

TABLE specifies the schema and name of the table into which TKPROF temporarily places execution plans before writing them to the output file. If the specified table already exists, TKPROF deletes its rows, uses it for the EXPLAIN PLAN command, and then deletes its rows. If this table does not exist, TKPROF creates it, uses it, and then drops it.

The specified user must be able to issue INSERT, SELECT, and DELETE statements against the table. If the table does not already exist, the user must also be able to issue CREATE TABLE and DROP TABLE statements.

For these privileges to issue statement, see the ORACLE7 Server SQL Language Reference Manual.

This option allows multiple individuals to concurrently run TKPROF with the same user in the EXPLAIN value. These individuals can specify different TABLE values and avoid destructively interfering with each other's processing on the temporary plan table.

If you use the EXPLAIN parameter without the TABLE parameter, TKPROF uses the table PROF$PLAN_TABLE in the schema of the user specified by the EXPLAIN parameter. If you use the TABLE parameter without the EXPLAIN parameter, TKPROF ignores the TABLE parameter.

INSERT creates a SQL script that stores the trace file statistics in the database. TKPROF creates this script with the name filename3. This script creates a table and inserts a row of statistics for each traced SQL statement into the table.

SYS enables and disables the listing of SQL statements issued by the user SYS, or recursive SQL statements into the output file. The default value of YES causes TKPROF to list these statements. The value of NO causes TKPROF to omit them.

Note that this parameter does not affect the optional SQL script. The SQL script always inserts statistics for all traced SQL statements, including recursive SQL statements.

SORT sorts the traced SQL statements in descending order of the specified sort option before listing them into the output file. If more than one option is specified, the output is sorted in descending order by the sum of the values specified in the sort options. If you omit this parameter, TKPROF lists statements into the output file in ascending order of when each was first issued.

The sort options are:

PRSCNT number of times parsed
PRSCPU CPU time spent parsing
PRSELA elapsed time spent parsing
PRSDSK number of physical reads from disk during parse
PRSQRY number of consistent mode block reads during parse
PRSCU number of current mode block reads during parse
PRSMIS number of library cache misses during parse
EXECNT number of executes
EXECPU CPU time spent executing
EXEELA elapsed time spent executing
EXEDSK number of physical reads from disk during execute
EXEQRY number of consistent mode block reads during execute
EXECU number of current mode block reads during execute
EXEROW number of rows processed during execute
EXEMIS number of library cache misses during execute
FCHCNT number of fetches
FCHCPU CPU time spent fetching
FCHELA elapsed time spent fetching
FCHDSK number of physical reads from disk during fetch
FCHQRY number of consistent mode block reads during fetch
FCHCU number of current mode block reads during fetch
FCHROW number of rows fetched
PRINT lists only the first integer sorted SQL statements into the output file. If you omit this parameter, TKPROF lists all traced SQL statements. Note that this parameter does not affect the optional SQL script. The SQL script always inserts statistics for all traced SQL statements.

Example This example runs TKPROF, accepts a trace file named KERVMS_RPK2_FG_SQLDBA_007.TRC and writes a formatted output file named OUTPUTA.PRF:

TKPROF KERVMS_RPK2_FG_SQLDBA_007.TRC OUTPUTA.PRF

EXPLAIN=SCOTT/TIGER TABLE=SCOTT.TEMP_PLAN_TABLE_A INSERT=STOREA.SQL SYS=NO SORT=(EXECPU,FCHCPU) PRINT=10

41. What is SQL trace?

The SQL trace facility provides performance information on individual SQL statements. The SQL trace facility generates the following statistics for each statement:

· Parse, execute, and fetch counts
· CPU and elapsed times
· Physical reads and logical reads
· Number of rows processed
· Misses on the library cache

42. What does so_picking_batches contain?

A user-defined group of pickslips for orders that you release for picking all at once. You create picking batches when you release your orders for shipping. For example, a batch can contain all shipments for a specific warehouse, or all priority shipments regardless of warehouse.

SO_picking batches contains picking batch name and other information depends on the values entered on pick release screen from the following fields like warehouse, customer, sub-inventory, order etc. user may enter combination of these values and create a picking batch. Which in turn will fire an concurrent program which populates the so_picking_headers, so_picking_lines, so_picking_line_details tables and prints picking slips for the batch.

43. How do you call a flex-field from a report?

44. What all reports have you customized in OE?

1. Printing sales orders in European languages.
2.

45. Mention any report customization you have done?

46. How do you register a report?

1. Create the Executable
2. Create concurrent program
2.1 Assign the executable, which you have created.
2.2 Create the parameters to be passed to the report.
2.3 Attach value set to each and every parameter. If value set not there you have to create one.
2.4 List incompatible program if any.

47. What is a responsibility?

Use this form to define a responsibility. A responsibility determines how much of an application's functionality a user can use, what reports and concurrent programs the user can run, and which applications' data those reports and concurrent programs can access.

48. How do you create a responsibility?
Navigate Security Responsibility Define

1. Specify the application in which you want to create responsibility.
2. Specify the data group by which user will login to database.
3. Specify the main Menu which user will see on the screen.
4. Specify the first screen user would see after the successful login.
5. Assign the report security group which decides which reports user has access to.(Optional)

49. What tables does demand interface update?

MTL_DEMAND_INTERFACE

50. What is canvas?
Canvas-views are the background objects on which you place the interface items (text items, check boxes, radio groups, etc.) and boilerplate objects (boxes, lines, images, etc.) that operators interact with as they run your form. Each canvas-view is displayed in a window.

51. How do you transfer data from legacy system to oracle financials?
· Transfer the data into ASCII files from Legacy system.
· Upload the data from the ASCII files into Interface Tables using SQL*loader.
· Import the data, which is existing in the interface tables to the Oracle's base tables after necessary validations.

53. What are lexical parameters?
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

Rules:

1. You cannot make lexical references in a PL/SQL statement.

2. If a column or parameter is used as a lexical reference in a query, its data type must be Character.

3. If you want to use lexical references in your SELECT clause, you should create a separate lexical for each column you will substitute. In addition, you should assign an alias to each lexical reference. This enables you to use the same layout field and boilerplate label for whatever value you enter for the lexical on the Runtime Parameter Form. See the example below.

4. If you use lexical references in your SELECT clause, you must specify the same number of items at runtime as were specified in the report's data model. In addition, each value you specify for your lexical references at runtime must have the same data type as its Initial Value. Look at the example below.

5. An Oracle Reports link should not depend upon a lexical reference. That is, neither the child column of a link or its table name should be determined by a lexical reference. To achieve this functionality, you need to create a link with no columns specified and then enter the SQL clause (e.g., WHERE) for the link directly in the query. For example, your parent and child queries might be written as follows:

Parent Query: SELECT DEPTNO FROM EMP

Child Query: SELECT &PARM_1 COL_1, &PARM2 COL_2
FROM EMP
WHERE &PARM_1 = :DEPTNO

Note how the WHERE clause makes a bind reference to DEPTNO, which was selected in the parent query. Also, this example assumes that you have created a link between the queries in the Data Model editor with no columns specified.

6. A lexical reference cannot be used to create additional bind variables after the After Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference):

SELECT ENAME, SAL FROM EMP

&where_clause

If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you must specify the value in the After Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger:

WHERE SAL = :new_bind

If you supplied this same value in the After Form trigger, the report would run.

Following are some examples of lexical references:

SELECT Clause

SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE FROM EMP

P_ENAME, P_EMPNO, and P_JOB can be used to change the columns selected at runtime. For example, you could enter DEPTNO as the value for P_EMPNO on the Runtime Parameter Form. Note that in this case, you should use aliases for your columns. Otherwise, if you change the columns selected at runtime, the column names in the SELECT list will not match the Oracle Reports columns and the report will not run.

FROM Clause

SELECT ORDID, TOTAL FROM &ATABLE

ATABLE can be used to change the table from which columns are selected at runtime. For example, you could enter ORD for ATABLE at runtime. If you dynamically change the table name in this way, you may also want to use lexical references for the SELECT clause (look at the previous example) in case the column names differ between tables.

WHERE Clause

SELECT ORDID, TOTAL FROM ORD WHERE &CUST

CUST can be used to restrict records retrieved from ORD. Any form of the WHERE clause can be specified at run-time.

GROUP BY Clause

SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL FROM ORD GROUP BY &NEWCOMM

The value of NEWCOMM can be used to define the GROUP BY clause.

HAVING Clause

SELECT CUSTID, SUM(TOTAL) TOTAL FROM ORD GROUP BY CUSTID HAVING &MINTOTAL

The value of MINTOTAL could, for example, be used to select customers with a minimum total of orders.

ORDER BY Clause

SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL FROM ORD ORDER BY &SORT

The value of SORT can be used to select SHIPDATE, ORDERDATE, ORDID, or any combination as the sort criterion. It could also be used to add on to the query, for example to add a CONNECT BY and START WITH clause.

CONNECT BY and START WITH Clauses

Parameters in CONNECT BY and START WITH clauses are used in the same way as they are in the WHERE and HAVING clauses.

Multiple Clauses

SELECT &COLSTABLE

COLSTABLE could be used to change both the SELECT and FROM clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT for COLSTABLE at runtime.

SELECT * FROM EMP &WHEREORD

WHEREORD could be used to change both the WHERE and ORDER BY clauses at runtime. For example, you could enter WHERE SAL > 1000 ORDER BY DEPTNO for &WHEREORD at runtime.

PL/SQL and SQL

SELECT &BREAK_COL C1, MAX(SAL) FROM EMP GROUP BY &BREAK_COL

BREAK_COL is used to change both the SELECT list and the GROUP BY clause at runtime. The Initial Value of the parameter &BREAK_COL is JOB. At runtime, the user of the report can provide a value for a parameter called GROUP_BY_COLUMN (of Data type Character). In the Validation Trigger for GROUP_BY_COLUMN, you call the following PL/SQL procedure and pass it the value of GROUP_BY_COLUMN:

procedure conv_param (in_var IN char) is

begin
if upper(in_var) in ('DEPTNO','EMPNO','HIREDATE') then
:break_col := 'to_char('in_var')' ;
else
:break_col := in_var;
end if;
end;

This PL/SQL ensures that, if necessary, a TO_CHAR is placed around the break column the user chooses. Notice how in SQL, you make a lexical reference to BREAK_COL. In PL/SQL, you must make a bind reference to BREAK_COL because lexical references are not allowed in PL/SQL.

54. How do I populate a POPlist with a record group at runtime?
Syntax:
POPULATE_LIST(list_id, recgrp_id);

POPULATE_LIST(list_id, recgrp_name);
POPULATE_LIST(list_name, recgrp_id);
POPULATE_LIST(list_name, recgrp_name);

Built-in Type

Unrestricted procedure

Enter Query Mode: yes

Description:
Removes the contents of the current list and populates the list with the values from a record group. The record group must be created at runtime and it must have the following two column (CHAR) structure:

Column 1: Column 2:

The list label the list value

Parameters:

List_id Specifies the unique ID that Oracle Forms assigns when it creates the list item. Use the FIND_ITEM built-in to return the ID to an appropriately typed variable. The data type of the ID is ITEM.

list_name The name you gave to the list item when you created it. The data type of the name is CHAR.
recgrp_id Specifies the unique ID that Oracle Forms assigns when it creates the record group. The data type of the ID is RecordGroup.
recgrp_name The CHAR name you gave to the record group when you created it.
Usage Notes:

· Do not use the POPULATE_LIST built-in if the Other Values property is defined and there are queried records in the block. Doing so may cause Oracle Forms to be unable to display records that have already been fetched.

For example, assume that a list item contains the values A, B, and C and the Other Values property is defined. Assume also that these values have been fetched from the database (a query is open). At this point, if you populate the list using POPULATE_LIST, an error will occur because Oracle Forms will attempt to display the previously fetched values (A, B, and C), but will be unable to because these values were removed from the list and replaced with new values.

· Before populating a list, close any open queries. Use the ABORT_QUERY built-in to close an open query.

Restrictions:
POPULATE_LIST returns error FRM-41337: Cannot populate the list from the record group if:

· The record group does not contain either the default value element or the other values element and the list does not meet the criteria specified for deleting these elements with DELETE_LIST_ELEMENT. Refer to the restrictions on DELETE_LIST_ELEMENT for more information.
· The record group contains an other value element but the list does not meet the criteria specified for adding an other value element with ADD_LIST_ELEMENT. Refer to the restrictions on ADD_LIST_ELEMENT for more information.

Example:
/*

** Built-in: POPULATE_LIST
** Example: Retrieves the values from the current list item
** Into record group one, clears the list, and
** Populates the list with values from record group
** Two when a button is pressed.
** Trigger: When-Button-Pressed
*/
BEGIN
Retrieve_List(list_id, 'RECGRP_ONE');
Clear_List(list_id);
Populate_List(list_id, 'RECGRP_TWO');
END;

55. How do I call reports from forms?
56. What are various parameters of Run_product built-in?

RUN_PRODUCT(product, document, commmode, execmode, location,
list, display);

RUN_PRODUCT(product, document, commmode, execmode, location,
name, display);
Parameters:

product Specifies a numeric constant for the Oracle product you want to invoke: FORMS specifies a Runform session. GRAPHICS specifies Oracle Graphics. REPORT specifies Oracle Reports. BOOK specifies Oracle Book.

Document Specifies the CHAR name of the document or module to be executed by the called product. Valid values are the name of a form module, report, Oracle Graphics display, or Oracle Book document. The application looks for the module or document in the default paths defined for the called product.
Commmode Specifies the communication mode to be used when running the called product. Valid numeric constants for this parameter are SYNCHRONOUS and ASYNCHRONOUS.
SYNCHRONOUS specifies that control returns to Oracle Forms only after the called product has been exited. The operator cannot work in the form while the called product is running.

ASYNCHRONOUS specifies that control returns to the calling application immediately, even if the called application has not completed its display.

execmode Specifies the execution mode to be used when running the called product. Valid numeric constants for this parameter are BATCH and RUNTIME. When you run Oracle Reports and Oracle Graphics, execmode can be either BATCH or RUNTIME. When you run Oracle Forms, always set execmode to RUNTIME.
Location Specifies the location of the document or module you want the called product to execute, either the file system or the database. Valid constants for this property are FILESYSTEM and DB.
List or name Specifies the parameter list to be passed to the called product. Valid values for this parameter are the CHAR name of the parameter list, the ID of the parameter list, or NULL. To specify a parameter list ID, use a variable of type PARAMLIST.
Note: You can pass text parameters to called products in both SYNCHRONOUS and ASYNCHRONOUS mode. However, parameter lists that contain parameters of type DATA_PARAMETER (pointers to record groups) can only be passed to Oracle Reports and Oracle Graphics in SYNCHRONOUS mode. (SYNCHRONOUS mode is required when invoking Oracle Graphics to return an Oracle Graphics display that will be displayed in a form chart item.)

Note: You can prevent Oracle Graphics from logging on by passing a parameter list that includes a parameter with key set to LOGON and value set to NO.

Note: You cannot pass a DATA_PARAMETER to a child query in Oracle Reports. Data passing is supported only for master queries.

display Specifies the CHAR name of the Oracle Forms chart item that will contain the display (such as a pie chart, bar chart, or graph) generated by Oracle Graphics. The name of the chart item must be specified in the format block_name.item_name. (This parameter is only required when you are using an Oracle Graphics chart item in a form.)

Add_Parameter(pl_id,'EMP_QUERY',DATA_PARAMETER,'EMP_RECS');
/*
** Run the report synchronously, passing the parameter list
*/
Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME,
FILEYSTEM, pl_id, NULL);

57. If the first field of a form is required, and I want to tab out without entering any value, how do I do that?

For user:
Press Shift + Tab, it allows to navigate out of the required current field.
For developer:
VALIDATE(validation_scope);

Built-in Type: unrestricted procedure

Enter Query Mode: yes

Description:
VALIDATE forces Oracle Forms to immediately execute validation processing for the indicated validation scope.

Parameters:

validation scope Specify one of the following scopes:

DEFAULT_SCOPE Perform normal validation for the default scope, determined by the runtime platform.

Note: If you change the scope via SET_FORM_PROPERTY(VALIDATION UNIT) and then call VALIDATE(DEFAULT_SCOPE), you will override the default scope as defined in the form module. In this case, Oracle Forms will not validate at the default scope but at the scope defined by SET_FORM_PROPERTY.

FORM_SCOPE Perform normal validation for the current form.

BLOCK_SCOPE Perform normal validation for the current block.

RECORD_SCOPE Perform normal validation for the current record.

ITEM_SCOPE Perform normal validation for the current item.

58. What is a complex report you have done?

59. What are the prerequisites of a Matrix report?

Data Model Requirements You must have a matrix, nested matrix, or matrix break data model. The data model must have at least four groups, one of which must be a cross product group. For more information, see Building Reports Manual.

Note: For data models that require a break group, you should create the break group before creating the cross-product group. If the cross-product group already exists, delete it, create the break group(s), and then recreate the cross-product group.

Matrices and Summaries # When creating summaries that will appear within the matrix, the summary must be owned by the cross product group and must have Product Order specified. Oracle Reports uses the Product Order to determine where to place the fields for the summaries in the layout. (Note that summaries owned by the cross-product group cannot have a Reset At or Compute at of Page or rely on columns that do.)

Default Layout Requirements You must select the cross product group, and at least two of the groups inside of it.

Matrix Report Defaulting

This section is divided into six sections:

· Matrix defaulting without summaries
· Matrix defaulting with summaries
· Nested matrix defaulting without summaries
· Nested matrix defaulting with summaries
· Matrix break defaulting without summaries
· Matrix break defaulting with summaries

Matrix Defaulting without Summaries

Assume you only have one group tree, you selected the cross product group, two groups inside of it, and one group below it. Assume that each group contains one column.

Generated Objects: Your Layout editor will contain the following objects:

B_columnname For the label of each selected column in the Default Layout dialog box. (B_ implies it is a boilerplate object.)

F_columnname For each column that you selected in the Default Layout dialog box. (F_ implies it is a field.)

R_groupname For each group that you selected in the Default Layout dialog box. (R_ implies it is a repeating frame.)

M_groupname_HDR For each group that you selected in the Default Layout dialog box. (M_ implies it is a frame; HDR implies it is a header frame. M_groupname_HDR is used to protect field labels from being overwritten by other objects in the report.)

X_groupname For each pair of repeating frames that intersect to form the matrix. By multiplying the number of across dimensions by the number of down dimensions, you can calculate the number of matrix objects created. In this case, one dimension going down multiplied by one dimension going across yields one matrix object.

60. How do I call a backend stored procedure from forms?

Just call the stored procedure in forms by its name and passing required parameters.

61. How do I handle stored Procedure errors from forms?
If error not handled in SP then error will propagate in forms.
1. If u know the error then display appropriate message in on-error.
2. If u don’t know the error, then get error code and text which oracle gives DBMS_ERROR_CODE, DBMS_ERROR_TEXT and display the contents.

62. If I am calling a report from forms & if the report query is ‘select * from emp’ and dynamically at runtime I want to change it to ‘select * from dept’ can I do it? How?

Yes, it is possible.
1) The data model should be prepared in the most generic format possible i.e. using lexical parameters like &QUERY. For ex. Select maximum number of dummy columns with fixed column aliases from dual should be the default value for the lexical parameter “QUERY”.
2) After accepting the report query from the user through a user parameter (say RQ).
3) Write a code in either After parameter form OR Before report triggers to extract table name (say TN) from RQ. Query up the columns from the data dictionary (table ‘COL’)for the table name TN.
4) Build the report query using the column names that are queried up and the table name that is extracted. Don’t forget to add the fixed column aliases that were used to form the data model. Apply the to_char function to all those non character fields that are part of the report query.
5) Pass the newly built report query to the data model via the lexical parameter “QUERY”.
6) The following are the limitations:
a) The number of columns in the report layout is limited by the maximum number of columns for which the data model is designed and the report lay out is painted.
b) The tedious hard coding part involved in building the new query for the report in either of the 2 After parameter form OR Before report triggers.

63. If we have same triggers at multiple levels i.e. Form, Block and Item then what is the default order of firing the triggers?
1. Item
2. Block
3. Form

64. What triggers get fired first? When-new-form-instance or when-new-item-instance?
1. When-new-form-instance
2. When-new-item-instance

65. What all AOL features have you used?

66. What all steps have to be done if I have to add a Quick Pick to a form, register it and run it in Oracle financials Application?
1) Get the registered form from the top directory and add a couple of steps in the FND_STARTUP trigger of the form. The steps are as follows:
a) Define an LOV. E.g.
#FND FKEY DEFINE_LISTVAL
SQL="SELECT LOOKUP_CODE, MEANING \"Unit of Measure \"
INTO :ORDER_LINE.UNIT_OF_MEASURE,
:ORDER_LINE.UNIT_OF_MEASURE_MEANING
FROM OE_LOOKUPS WHERE LOOKUP_TYPE =
'UNIT_OF_MEASURE' AND
LOOKUP_CODE IN (SELECT UNIT_OF_MEASURE FROM
OE_PRODUCT_PRICES
WHERE PRODUCT_ID = :ORDER_LINE.PRODUCT_ID)"
COLUMN="\" Unit of Measure \"(15)"
LOVNAME="UOM_QP"
b) attach it to the Field as one of the steps in FND_STARTUP trigger.
#FND FIELD_INFO
ZONENAME="ORDER_LINE"
FIELDNAME="UNIT_OF_MEASURE"
LOVNAME="UOM_QP"
2)
a) Add a new trigger defining a customized LOV.
XXX_QP (or an unnamed step of your form-level EasyForm declaration trigger at form level)

where XXX are three or more characters you choose

Create user defined trigger as follows :
Syntax, Step 1
#FND FKEY DEFINE_LISTVAL
LOVNAME=”list_of_values_name"
SQL="SQL select statement"
[QVALIDATION="SQL logic for query validation"]
[EVALIDATION="SQL logic for entry validation"]
COLUMN="column1(length), column2(length),..."
[FLDNAME=":block.reference_field"]
[TITLE="{title text*application shortname:message name}"]
[HEADING="{heading1(length), heading2(length),...
*application shortname:message nameN}"]
[CACHE="{YN}"]
[MATCH="{YN}"]
[LONGLIST="{YN}"]
[SKIP="{012...}"]
[AUTOPICK="{YNFORCE_YESFORCE_NO}"]
[WINDOW="xorigin, yorigin, width, height"]

Add a FND_LISTVAL trigger specifying your LOVNAME with the field to which it has to be attached to, at item level as follows :
#FND FKEY LISTVAL
FLDNAME=":first_block.ename" LOVNAME="LOV1";

c) Add FND_FKEY trigger calling #EXEMACRO EXETRG “user trigger name” this trigger is called from a form level standard PRE_FIELD trigger. This FND_FKEY trigger queries and fetches data based on the LOV definition and lights up the PICK lamp in the message line. This FND_FKEY should be at item level.
d) Register the form. Responsibility “Application Developer” and the navigation path is \Navigate Application Form Register

67. How do I attach a form to my own menu?
\Navigate Application Menu
Specify application name, menu name and move to next zone. For a menu item attach a object, specify TYPE as FORM. Specify under which application the form is registered into and Save changes.

68. Tell any complex report you have developed?

69. What all features of reports have you incorporated in your report?
70. Tell any complex form you have developed?

71. What all features of forms you have incorporated in your form?
72. What all are steps required to pass a flex field as a parameter to a report?

A value set of validation type special or pair has to be created, in which we have to actually invoke the flex field. Then when you specify report parameters in concurrent program definition, specify the value set which is to be used for parameter validation. Depending on the value set you use for validation, flex field popup will appear. Parameters are passed to the report as a string. APPS in-built user exit will interpret the string and will return a formatted WHERE CLAUSE in form of a lexical parameter which we have to attach to the query.

73. What technical features (non-functional) have you practically used in Oracle Applications?
Context sensitive help, zoom, quickpick, quickcode, descriptive flex-field, report sets, menu creation, responsibility, concurrent program, registering tables, columns, forms, report, executable

74. What is pick release? Which tables are used in pick release?

Pick Release is an order cycle action to notify warehouse that orders are ready for picking. Picking is a process of withdrawing items from inventory to be shipped to the customer.

Tables used are
SO_PICKING_HEADER,
SO_PICKING_LINES,
SO_PICKING_LINE_DETAILS,
SO_PICKING_BATCHES,

75. How do you release back ordered items?
Backorder is unfulfilled customer order or commitment. When you pick release an order there is a field for backorder if you specify backorder to INCLUDED or ONLY the back order quantity will be picked for shipment.

This value can be either of following :

Include Releases all backordered order lines that meet your criteria and then all unreleased order lines that meet your criteria.

Exclude Releases all unreleased order lines that meet your criteria (excluding backorders).

Only Releases only backordered lines that meet your criteria.
Unfulfilled order line details which have failed to be released at least once by Pick Release or have been backordered by Ship Confirm.

76. What is stored in SO_PICKING_BATCHES?

Stores batch information for the orders, which are due for pick release. It stores item id, warehouse, sub-inventory, shipment method, shipment priority, ship set no, order type, etc.

77. Which tables stored order holds information?
Tables storing hold information are
SO_HOLDS,
SO_HOLD_AUTHORIZATION,
SO_HOLD_RELEASES,
SO_HOLD_SOURCES_ALL,
SO_ORDER_HOLDS_ALL

78. How do you modify descriptive flex field?

1. For changing segments of descriptive flex-field
From AOL select
\Flex-field descriptive segments

You have to UN-FREEZ flex-field and then change the segments. Once you are through with modifications, set it back to FREEZ. This will fire a concurrent request which will in turn change the descriptive flex field to reflect the changes.

2. For changing value sets attached to flex-field
\Flex-field descriptive values.

Specify the application, form, zone, flex-field name and change value sets in the next zone. Save the screen to reflect the new value set.

79. How does explain plan inform that a particular index be used for query execution?
You can pass hint for the optimizer to use the specified index. Hint is passed in the SQL as /* hint */ where hint is the index name to be used.

80. Name the table where demand interface data is stored?
Please refer to question No 48

81. How to register a form, reports?

Refer to question 46 and 27 to 30 for answer.

82. Where will you keep host source code?
$APPL_TOP/bin

83. What is flex field?

Flex-field An Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flex-fields to capture information about your organization. There are two types of flex-fields: key flex-fields and descriptive flex-fields.

84. What is segment qualifier?
Segment Qualifier

A segment qualifier identifies a particular type of value in a single segment of a key flex-field. In the Oracle Applications, only the Accounting Flex-field uses segment qualifiers. You can think of a segment qualifier as an "identification tag" for a value. In the Accounting Flex-field, segment qualifiers can identify the account type for an account segment value, and determine whether detail posting or budgeting are allowed for a particular value.

It is easy to confuse the two types of qualifiers. You should think of a flex-field qualifier as something the whole flex-field uses to tag its pieces, and you can think of a segment qualifier as something the segment uses to tag its values.

85. What are global segments?

Descriptive flex fields have two different types of segments, global and context-sensitive, that you can decide to use in a descriptive flex field structure. A global segment is a segment that always appears in the descriptive flex field pop-up window, regardless of context (any other information in your form).

86. What are context sensitive segments?
Descriptive flex fields have two different types of segments, global and context-sensitive, that you can decide to use in a descriptive flex field structure. A context-sensitive segment is a segment that may or may not appear depending upon what other information is present in your form.

87. How to implement flex fields in forms, name the triggers and user exits?
For inserting data for a Flex-Field
a) Trigger KEY_PREFIELD calls POPID user exit for invoking flex field. This is written at the field level.
b) Triggers FND_PRE_INSERT and /or FND_PRE_UPDATE calls VALID user-exit for validating the user entered flex field. These are written at the block level.
For queried data for a Flex-field
a) Trigger FND_PRE_QUERY calls POPID user exit for invoking flex field window and FND_POST_QUERY calls LOADID user exit for loading segment descriptions when user Queries.

88. Why is FND CURRENCY used in reports?
It is FND FORMAT_CURRENCY user exit for a report, which displays mixed currency values. Information about the radix character and thousands separator are determined from the location of the user. The special display for negative and positive currency values is specified by two profile options. Hence, a report can appear differently depending upon the location of the user and the profile options values set.

In Germany the radix character and thousand separator are switched from the US counterpart. The negative numbers display in parentheses () or angle brackets <\> depending upon the user's preference.

NOTE: It does not convert one currency into another but just formats the amounts as per location.

89. What is the difference between quick pick and quick code?

Overview of QuickPick and QuickCodes

QuickPick is a powerful Oracle Application Object Library feature that makes your users more productive by letting them enter data in shorthand form and by ensuring that they enter valid data. QuickPick also makes it easy for you to create foreign key relationships in your forms. You can design more intuitive forms by displaying descriptive names and hiding internal identifiers. For example, when entering the sales representative responsible for a client, your user can enter the representative's name, instead of the representative's employee ID or some other identifying code.

You can also use QuickPick to enforce separate validation rules for data entry and data query. For example, you can specify that when entering a currency name, your user can only chose from currency names that are enabled and currently active. When querying data, you users sees all enabled and disabled currency names.

If you define a QuickPick on a field, your form displays a PICK lamp to show your user that QuickPick is available.

QuickCodes let you define general purpose, static lists of values for form fields. QuickCodes allow you to base your program logic on codes while displaying user-friendly names in a QuickPick window. QuickCodes simplify name and language changes by letting you alter the names your users see, while the codes in your underlying forms and programs remain unchanged.

QuickCodes share all the powerful features of QuickPick, such as AutoSelection and AutoReduction. QuickCodes also use separate validation criteria for data entry and data query, although for QuickCodes this validation happens automatically, based on start and end dates and an enabled flag.

From your end user's point of view, a QuickCodes field looks and acts just like a QuickPick field. However, due to the static nature of QuickCodes lists of values, Oracle Application Object Library processes them up to ten times faster than ordinary QuickPick lists, resulting in improved performance for your users.

For example, suppose your form contains several fields that accept the values Yes and No. Instead of writing triggers that explicitly check for each of these values (and their partial values like Y or N), you can write QuickCodes triggers that refer to the QuickCodes type YES_NO. If your end user enters Yes or No (or their partial values), QuickCodes returns a code to your form to indicate the value entered.

You can also use QuickCodes on fields that accept longer lists of values, such as U.S. state names. Your end users can add values to lists you define. Your end users can also specify a starting date, an ending date, and whether a particular QuickCodes value is enabled.

There are two types of QuickCodes. Some QuickCodes are shared among several applications, and are always pre-seeded. We recommend you do not create new QuickCode types for the pre-seeded QuickCodes. You can maintain these QuickCodes using the Define QuickCodes form.

Special QuickCodes, which are maintained from the Define Special QuickCodes form, allow you to create new QuickCode types. In this chapter, we discuss maintaining and developing Special QuickCodes, usually referred to simply as QuickCodes.

90. What is auto reduction?
AutoReduction automatically shortens the list to only those values beginning with the character(s) your users type.

91. What are special triggers created in the form when you register it?

Refer question 27-30.

92. How will you display a message in the form that you have modified for testing the form?

93. How can you see the global parameter values in the form without coding?
You should have developer menu to do this. You have a Examine option which lists all form level, system level and global variables. You can see values of all of them without coding for it.

94. How to debug the form?

You should have developer menu to do this. There is an option to set Debugger On and Off. It will show you which trigger is getting fired and you can debug the relevant trigger.

95. What was the structure of accounting flex field in your previous project?
Company
Region
Cost Center
Account
Product
Sub-account

96. What is balancing segment?

Balancing segment An Accounting Flex-field segment that you define so that Oracle General Ledger automatically balances all journal entries for each value of this segment. For example, if your company segment is a balancing segment, Oracle General Ledger ensures that, within every journal entry, the total debits to company 01 equal the total credits to company 01.

97. What is cost center segment?
It is a segment qualifier. A segment qualifier identifies a particular type of value in a single segment of a key flex-field. In the Oracle Applications, only the Accounting Flex-field uses segment qualifiers. You can think of a segment qualifier as an "identification tag" for a value. In the Accounting Flex-field, segment qualifiers can identify the account type for an account segment value, and determine whether detail posting or budgeting, are allowed for a particular value.

98. Can you have two accounting flex fields in AR?

Yes, you can have as many accounting flex fields as you wish. But, then you have to define one set of books per accounting flex field.

99. What are on-account sales credits?
On account: Payments where you intentionally apply all or part of the payment amount to a customer without reference to an debit item. On-account examples include prepayments and deposits.

On account credits: Credits that you assign to your customer's account that are not related to a specific invoice. You can create on account credits in the Enter Credit Memos form or through AutoInvoice.

100. What is customer merging?

You can merge the business purposes of two different customers in order to assign all transactions to a new customer, or you can merge business purposes for the same customer. Oracle Receivables automatically generates a report after you merge customer information to allow you to review the details of your merge.

101. What happens when you complete an invoice?
You complete an invoice by entering yes in this field. If you only have partial information for the invoice you can always go back to enter more information to complete your invoice.

Oracle Receivables performs several validation tests before it allows you to set the Complete field to Yes for an invoice:

_ Invoice contains at least one line or has freight at the header level.

_ Invoice date is within active date ranges of parameters

_ Each invoice line amount must equal the sum of it's distributions

_ GL date must fall within an open or future-enterable accounting period

_ Invoice Amount sign adheres to the value that you specified for the Creation Sign field of the transaction type for this invoice

If you are entering an invoice against a commitment:

_ Invoice date is equal to or later than the transaction date

_ Invoice's GL date is equal to or later than the commitment's GL date

If you have assigned an automatic payment method to an invoice:

_ Specified a customer bank

_ Specified a customer bank branch

_ Specified a customer bank account

If you assign an invoicing rule to an invoice:

_ Each invoice line has an accounting rule

_ Each invoice line has a rule start date

_ Invoice and tax lines of an invoice have account sets

If you do not assign an invoicing rule to an invoice:

_ Revenue accounts exist for all of the invoice and tax lines

_ Accounts on the Account Assignments are valid Flexfield combinations

If Tax Calculation is set to Yes:

_ Each line has a tax line except charges lines

When you complete an invoice whose transaction type has Open Receivables set to Yes, Oracle Receivables displays the invoice on your agings. If you change the transaction type of a completed invoice where Open Receivables is set to No, Oracle Receivables no longer displays the invoice on your agings.

If you update a completed invoice by changing values on which AutoAccounting depends, and AutoAccounting fails, Oracle Receivables displays a warning message and changes this field back to No.

If you update a completed invoice by changing values on which tax calculation depends, and tax calculation fails, Oracle Receivables displays a warning message and changes this field back to No.

Oracle Receivables also lets you delete incomplete invoices if you enter Yes in the Allow Delete field of the System Options form.

102. What all things are stored in RA_CUSTOMER_TRX?

The RA_CUSTOMER_TRX table stores invoice, debit memo, commitment and credit memo header information. Each of these transactions is stored as a unique record, based on the primary key, customer_trx_id. The transaction number, transaction date and billing customer are stored in the trx_number, trx_date and bill_to_customer_id columns, respectively. Additional information stored in this table includes ship-to customer, document sequence number, currency code and a transaction complete flag. The transaction type for the invoice is stored in the RA_CUST_TRX_TYPES table, but can be referenced via the foreign key cust_trx_type_id.

103. What are charge backs?
Charge-back is a new debit item that you assign to your customer when you close an existing, outstanding debit item.

104. What is lock box?

105. What is address validation?
For each address style you can define country specific validation which controls the information you enter in certain address elements. For example, you may want to restrict the entry of cities for French addresses to a predefined list, or you may want to restrict a postal code to a certain range of numbers. Oracle Applications allow you to define your own country specific validation. Validation can take the form of a simple quickpick or a complex parent-child relationship between address elements.

106. What is finance charge?

Finance charges are additional charges that you assign to your customers for past due items. You specify whether you want to charge your customers finance charges through their customer profiles. Finance charges can be included on your customer's statements and dunning letters.

107. How AR and OE related?
Orders are entered through OE module. Once order is Ship Confirmed i.e. material is delivered to customer, receivable interface is run. This interface ports data from OE to AR tables. AR enables to chase customer for payments. Various kinds of reports are produced. Data remains in AR till Invoice is paid off and then data is passed on to GL.

108. What is credit memo and debit memo?

Ref. Question 20, 21.

109. What is the link between CM and invoice (column name)?
Invoice and CM both go in ra_customer_trx table. CM record will have customer_trx_type_id of credit memo (in training database its set to 1002). This CM record will store the trx_number of Invoice record in previous_customer_trx_number.

Eg.
Invoice record trx_number = 1000
CM record trx_number = 1020086 previuos_customer_trx_number = 1000

110. What is set of books?

Set of books A company or group of companies within Oracle General Ledger that shares a common Accounting Flex-field structure, calendar and functional currency.

111. How to find overdue invoices?
You can find out overdue invoices by firing an aging report. Define aging buckets to list invoices in different buckets like 30 days overdue, 60 days overdue, 90days overdue etc.

112. List the key flex fields in AR?

Territory
Sales tax location

113. Which is the parameter set to validate key flex field value?Syntax

#FND {POPIDLOADIDVALID}
CODE="flexfield code"
APPL_SHORT_NAME="application_short_name"
VALIDATE="{FULLPARTIALNONEQUERY}"
SEG="block.concatenated values field name"
[BLOCK="block_name"]
[FIELD="field_name"]
[DERIVED=":block.field\nSegment qualifier"]
[READ_ONLY="{YN}"]
[DINSERT="{YN}"]
[WINDOW="{YN}"]
[ID="block.unique ID field"]
[REQUIRED="{YN}"]
[DISPLAY="{ALL flexfield qualifier
segment number}"]
[UPDATE="{ALL flexfield qualifier
segment number}"]
[INSERT="{ALL flexfield qualifier
segment number}"]
[DATA_FIELD="concatenated hidden IDs field"]
[DESC="block.concatenated description field name"]
[TITLE="window title"]
[VDATE="date"]
[NAVIGATE="{YN}"]
[AUTOPICK="{YN}"]
[NUM=":structure defining field"]
[COPY=":block.field\n{ALL flexfield qualifier}"]
[VRULE="flexfield qualifier\n
segment qualifier\n
{I[nclude]E[xclude]}\n
NAME=Message Dictionary message name\n
validation value1\n
validation value2..."]
[VALATT=":block.field\n
flexfield qualifier\n
segment qualifier"]
[USEDBFLDS="{YN}"]
[COLUMN="{column1(n) column1 alias(n)
[, column2(n), ...]}]
[WHERE="where clause"]
[SET="set number"]
[ALLOWNULLS="{YN}"]
[QUERY_SECURITY="{YN}"]
[QBE_IN="{YN}"]
[LONGLIST="{YN}"]
[NO_COMBMSG="MESG_NAME"]

114. What is dunning letter?

A letter that you send to your customers to inform them of past due debit items. Oracle Receivables lets you specify the text and format of each letter. You can choose to include unapplied and on-account payments.

115. What is profile?
Profile is a set of optional/mandatory parameters, which a user can set, which decide the way Oracle applications work.

116. What is concurrent program?

It is a unique facility that manages many time-consuming, non-interactive tasks within Oracle Applications for you, so you do not have to wait for their completion. When you submit a request in Oracle Applications that does not require your interaction, such as releasing shipments or running a report, the Concurrent Manager does the work for you, enabling you to complete multiple tasks simultaneously.

117. What are the inputs for pick release?
‘Backorders’ & ‘Prior Reservations Only’ are mandatory fields, rest of them are optional, which include the following:
a) Order Type
b) Order Number
c) Customer Number
d) Between ‘Request Dates’ and or ‘Schedule Dates’.
e) Ware house
f) Sub-inventory
g) Item
h) Shipment priority
i) Freight Carrier

118. After which stage you cannot cancel the order?

The Cancel Orders form enforces some security rules that are not predefined using the Define Security Rules form. For example, the Cancel Orders form prevents you from cancelling order line quantities that have been shipped or invoiced, and from cancelling return line quantities that have been received or credited. The Cancel Orders form honors security rules that you define for the Cancel operation that are stricter than these rules, but if you define any that conflict with these rules, they will be ignored.

119. Name important tables from OE/AR?Following are the list of some tables in OE module:
SO_CYCLES
SO_ACTIONS
SO_CYCLE_ACTIONS
SO_RESULTS
SO_HEADERS
SO_LINESSO_LINE_DETAILS
SO_PRICE_LISTS
SO_PRICE_LIST_LINES
SO_PRICE_ADJUSTMENTS
SO_DISCOUNTS
SO_DISCOUNTS_LINES
SO_PICKING_BATCHESSO_PICKING_HEADERSSO_PICKING_LINES
SO_LINE_APROVALS
SO_ORDER_APROVAL
SO_CREDIT_CHECK_RULES
Following are the list of some tables in AR module:
RA_CUSTOMER_TRX
RA_CUSTOMER_TRX_LINES
RA_CUST_TRX_LINE_GL_DIST
RA_CUSTOMER_TRX_LINE_SALESREPS
AR_PAYMENT_SCHEDULES
AR_CUSTOMER_PROFILES
AR_CUSTOMER_PROFILE_CLASSES
AR_CASH_RECEIPTS
AR_MISC_CASH_DISTRIBUTIONS
AR_CASH_RECEIPT_HISTORY
AR_RECEIVABLE_APPLICATIONS

120. Give complete process cycle for OE/AR?

The process cycle for a general OE is as follows:
ENTER
BOOK
DEMAND INTERFACE
BACK ORDER RELEASE
PICK RELEASE
SHIP CONFIRM
INVENTORY INTERFACE & RECEIVABLE INTERFACECOMPLETE LlNECOMPLETE ORDER

The process cycle for a general OE is as follows:
Import Invoice information from legacy system or from OE module (Receivable Interface) to Autoinvoice the Orders.
Enter Receipt
Apply the Invoices against the receipt received or deposits held or credit memos or prepayments.
Complete invoice.
121. Name interface tables in OE/AR?
Oracle Order Entry inserts information into two of the three AutoInvoice interface tables (RA_INTERFACE_LINES and RA_INTERFACE_SALES_CREDITS). RA_INTERFACE_DISTRIBUTIONS

Oracle Receivables and Oracle Government Receivables uses three tables to temporarily store data you transfer from other systems. These tables are called RA_INTERFACE_LINES_ALL, RA_INTERFACE_SALESCREDITS_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL. The fourth table, RA_INTERFACE_ERRORS_ALL is used only by AutoInvoice to store information about interface data that failed validation. For a detailed description of each column in these tables, please refer to the Table and Column Description section.

122. What tables are hit when an Invoice is entered?

RA_CUSTOMER_TRX
RA_CUSTOMER_TRX_LINES
RA_CUST_TRX_LINE_GL_DIST
RA_CUSTOMER_TRX_LINE_SALESREPS
AR_PAYMENT_SCHEDULES

123. Name important tables of various modules?Following are some of the important tables in AP module:
AP_INVOICES
AP_INVOICE_DISTRIBUTIONS
AP_HOLDS
AP_PAYMENT_SCHEDULES
AP_INVOICE_PREPAYS
AP_CHECKS
AP_INVOICE_PAYMENTS
AP_PAYMENT_DISTRIBUTIONS

Following are some of the important tables in GL module:
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_CODE_COMBINATIONS
GL_BALANCES
GL_IMPORT_REFERENCES
GL_BUDGETS
GL_BUDGET_ENTITIES
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_VERSIONS
GL_INTERFACE

Following are some of the important tables in INV module:
MTL_SYSTEM_ITEMS
MTL_PARAMETERS
MTL_ITEM_ATTRIBUTES
MTL_ITEM_TEMPLATES
MTL_ITEM_TEMPL_ATTRIBUTES
MTL_UNITS_OF_MEASURE
MTL_ITEM_REVISIONS
MTL_SERIAL_NUMBERS
MTL_LOT_NUMBERS
MTL_ITEM_CATALOG_GROUPS
MTL_MATERIAL_TRANSATIONS
MTL_MATERIAL_TRANSATIONS_TEMP
MTL_ONHAND_QUANTITIES
MTL_DEMAND_INTERTFACE

125. What you know about SQL*Loader?
SQL*Loader is a utility used for moving data from external files into the Oracle database.
Its syntax is similar to that of the DB2 Load utility, but comes with more options.
SQL*loader supports various load formats, selective filters, and multi-table loads.

One load data into the Oracle database by using the sqlldr (sqlload on some platforms)
utility. Look at the following example:

sqlldr orauser/passwd control=loader.ctl

This is the control file, loader.ctl:

load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE

Load variable and fix length data records
look at the following control file examples. In the first we will load delimited data
(variable length):

LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

If you need to load positional data (fixed length), look at the following control file
example:

LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

Modify data as it loads into the database:

look at the following examples:

LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)"
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list

FIELDS TERMINATED BY b","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)

Load data into multiple tables at once:
Look at the following control file:

LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
Selectively load only the data that you need :
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:

LOAD DATA
APPEND
INTO TABLE db_trace_19980517
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19980517'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)

You can not get SQL*Loader to commit at the end of the load file, but by setting the ROWS= parameter to a large value, committing can be
reduced. Make sure you have big rollback segments ready when you use a high value for
ROWS=.

To Improve performance of SQL*Loader :
1.A very simple but easily overlooked hint, do not have any indexes and/or constraints
(primary key) on your load tables during the load process. This will significantly
slowdown load times even with ROWS= set to a high value.

2.Add the following option in the command line: DIRECT=TRUE. This will effectively
bypass most of the RDBMS processing. However, there are cases when you can't
use direct load. Refer to chapter 8 on Oracle server Utilities manual.

3.Turn off database logging by specifying the UNRECOVERABLE option. This option
can only be used with direct data loads.

4.Run multiple load jobs concurrently.

126. Name some important interface programs

a) Inventory
b) Customer
c) Item
d) Receivable
e) GL
f) Demand
g) Tax

127. What are object groups?
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or reference them in another module.

Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas-view, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.

You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.

Program units cannot be placed in an object group.

· Objects owned by blocks cannot be placed in an object group. This includes items, item-level triggers, block-level triggers, and relations. These objects cannot exist independently of the block in which they were defined. To include these objects in an object group, you must include the block itself by dragging the block object into the object group in the Navigator.

· The objects in a group must all be defined in the same module; you cannot place objects from two different forms in the same object group.

· An object group cannot contain another object group.

· When an object in an object group is deleted from a module, it is removed from the object group automatically.

· Deleting an object group from a module does not delete the objects it contains from the module.

· Object groups do not store copies of the objects they contain, but rather pointers to the objects; defining object groups does not increase module size significantly.

128. Difference between packages and procedure/function

Packages

A package is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms and so implements the specification.

Unlike subprograms, packages cannot be called, passed parameters, or nested. Still, the format of a package is similar to that of a subprogram:

The specification holds public declarations, which are visible to your application. The body holds implementation details and private declarations, which are hidden from your application.

You can debug, enhance, or replace a package body without changing the interface (package specification) to the package body.

Packages can be created interactively with SQL*Plus or SQL*DBA using the CREATE PACKAGE and CREATE PACKAGE BODY commands.

Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible. So, you can change the body without having to recompile calling programs.

Package Body

The package body implements the package specification. That is, the package body contains the definition of every cursor and subprogram declared in the package specification. Keep in mind that subprograms defined in a package body are accessible outside the package only if their specifications also appear in the package specification. The package body can also contain private declarations, which define types and objects necessary for the internal workings of the package. The scope of these declarations is local to the package body. Therefore, the declared types and objects are inaccessible except from within the package body. Unlike a package specification, the declarative part of a package body can contain subprogram bodies.

Procedure
A procedure has two parts: the specification and the body. The procedure specification begins with the keyword PROCEDURE and ends

with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.

The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: a declarative part, an executable part, and an optional exception-handling part. The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE, which introduces declarations in an anonymous PL/SQL block, is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement. The exception-handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

· Function
A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause.

Like a procedure, a function has two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the result value. Parameter declarations are optional. Functions that take no parameters are written without parentheses. The function body begins with the keyword IS and ends with the keyword END followed by an optional function name. The function body has three parts: a declarative part, an executable part, and an optional exception-handling part.

The declarative part contains local declarations, which are placed between the keywords IS and BEGIN. The keyword DECLARE is not used. The executable part contains statements, which are placed between the keywords BEGIN and EXCEPTION (or END). One or more RETURN statements must appear in the executable part of a function. The exception- handling part contains exception handlers, which are placed between the keywords EXCEPTION and END.

129. Various ways to call a form from another form and their difference· NEW_FORM
Built-in Type: restricted procedure

Enter Query Mode: no

Description:
Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Oracle Forms keeps the higher call active and treats it as a call to the new form. Oracle Forms releases memory (such as database cursors) that the terminated form was using.

Oracle Forms runs the new form with the same Runform options as the parent form. If the parent form was a called form, Oracle Forms runs the new form with the same options as the parent form.

· CALL_FORM
Built-in Type: unrestricted procedure

Enter Query Mode: yes

Description:
Runs an indicated form while keeping the parent form active. Oracle Forms runs the called form with the same Runform preferences as the parent form. When the called form is exited Oracle Forms processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.

· OPEN_FORM
Built-in Type: restricted procedure (cannot be called in Enter Query mode)

Enter Query Mode: no

Description:
Opens the indicated form. Call OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.

130. Can you base a block in a form on a view?

Yes, You can. If the view is non-updatable or non-insertable then you can not base a block on view.

closed order : An order and its order lines that have completed all activities in its process flow and for which the close activity has been completed.

Sales channel is always associated with the customer. We can define the sales person for the customer also. If sales person not defined at the customer level, we can associate a sales person with an order also. Account specialist and sales manager are always associated with bill to site address of the customer.

Interview Questions on Oracle Purchasing or Oracle PO

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:32:00 +0000

ORACLE PURCHASING - PURCHASE ORDERS FREQUENTLY ASKED QUESTIONS
CONTENTS
--------
1. In the Price type quickpick in the Purchase Order Lines zone in enter
Purchase Order form, there is a value called COST PLUS FEE. What is the
COST PLUS FEE price type? How is Purchase Order treating this
price type?
2. What is 2-way, 3-way, 4-way matching? How is this set up?
3. How do you reflect discount from a supplier on a Purchase orders?
4. How do you change the created date on a Purchase Order?
5. What is the sorting order for requisition lines in autocreation of a
Purchase Order?
6. What does the error 'APP-50022: Oracle Human Resources could not retrieve a
value for User Type profile option' mean?
7. In the Enter Receipts (RCVRCERC) form and using different responsibilities
produces different values in the action required field. The Purchasing
Responsibility shows Direct and Standard while the Inventory Responsibility
shows just Direct.
Why are the action options for creating receipts different in Inventory
compared Purchasing?
8. How do you purge Cancelled Requisitions?
9. On the requisition form in the source details zone, there is a field
labeled supplier item. Where does this information on the quickpick come
from and how can this information default in?
10. You are entering a requisition and are unable to find the header or line
note fields. Was this replaced in GUI by attachments?
11. When you select the Notifications form to review approvals and click on
approve button for a Requisition waiting for approval, the 'Reject' box is
greyed out. How does the approver reject a requisition?
12. What are standard Quotations used for?
13. When do you see the status of a Purchase order as Archived?
14. Where are standard notes in GUI?
15. In Oracle Purchasing, where is the automatic numbering for Purchase Order
defined and maintained?
16. There is a field for the organization code. What is this code?
17. Can the original Purchase Order can be viewed in any way without resorting
to SQL, for a revised Purchase Order?
QUESTIONS & ANSWERS
-------------------
1. In the Price type quickpick in the Purchase Order Lines zone in enter
Purchase Order form, there is a value called COST PLUS FEE. What is the
COST PLUS FEE price type? How is Purchase Order treating this
price type?
Answer
------
The functionality for COST PLUS FEE price type is not in Oracle Purchasing at
this point of time. PO does not do anything with this price type.
It is an enhancement request that is being looked at for future releases.
2. What is 2-way, 3-way, 4-way matching? How is this set-up?
Answer
------
2-way matching verifies that Purchase order and invoice information match within your tolerances as follows:
Quantity billed <= Quantity Ordered Invoice price <= Purchase order price (<= sign is used because of tolerances) 3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined: Quantity billed <= Quantity received 4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined: Quantity billed <= Quantity accepted. (Acceptance is done at the time of Inspecting goods). Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character) Receipt required Inspection required Matching Yes Yes 4-way Yes No 3-way No No 2-way In GUI, Shipments block, alternative region, click on More...this brings up a region with 'Invoice matching' column where you can choose 2-way, 3-way or 4- way match. You can find more detailed information about matching in the Oracle Payables Reference Manual (Volume 3) Topical Essay on Integrating your Payables and Purchasing Information. 3. How do you reflect discount from a supplier on a Purchase orders? Answer ------ The legal document is the Purchase order not the quote and therefore the purchase order should reflect agreed upon price. Secondly if the discount comes after the PO then AP should handle it when invoicing. If you are using a blanket Purchase order then you can use price breaks. 4. How do you change the created date on a Purchase Order? Answer ------ The 'Created' date on the Purchase order is not an updateable field, due to it being a legal document once approved. 5. What is the sorting order for requisition lines in autocreation of a Purchase Order? Answer ------ Requisition lines first meet search criteria determined by "find" screen, and then are grouped together by item and item category, then by need-by date. After you choose an action, enter any search criteria to narrow the selection of requisition lines. Oracle Purchasing chooses requisition lines from the requisition pool that meet your search criteria. All requisition lines for the same item and item category are grouped together, but they are not necessarily in order alphabetically. Within each grouping of item and item category, requisition lines are ordered by need-by date. 6. What does the error 'APP-50022: Oracle Human Resources could not retrieve a value for User Type profile option' mean? Answer ------ The profile HR: User Type needs to be set. Set the profile option 'HR: User Type' at the Responsibility level. 7. In the Enter Receipts (RCVRCERC) form and using different responsibilities produces different values in the action required field. The Purchasing Responsibility shows Direct and Standard while the Inventory Responsibility shows just Direct. Why are the action options for creating receipts different in Inventory compared Purchasing? Answer ------ Set the Profile option RCV: Allow routing override to 'YES' at the application level. 8. How do you purge Cancelled Requisitions? Answer ------ To purge any Purchase orders or Requisitions: - assign the Payables Purge GUI responsibility to the user - have the user choose that responsibility - then choose Purge from the menu - under the Category field do a List of Values and pick Simple Requisitions for cancelled requisitions The choices are listed. 9. On the requisition form in the source details zone, there is a field labeled supplier item. Where does this information on the quickpick come from and how can this information default in? Answer ------ The supplier item field on the requisition can be populated by using either the supplier item catalog or ASL. Take an item from the supplier item catalog that has an associated supplier item, add it to the order pad and then add it to the requisition. After doing this go to the source details zone. The supplier item is defaulted in for that item you choose from the catalog. Navigation: --> Purchasing --> Supplier Item catalog
10. You are entering a requisition and are unable to find the header or line
note fields. Was this replaced in GUI by attachments?
Answer
------
The attachments have replaced notes. The attachments are better because at the
header level, the requisition notes were not coming over to the Purchase Order
whereas the attachment does come over to the Purchase Order.
11. When you select the Notifications form to review approvals and click on
approve button for a Requisition waiting for approval, the 'Reject' box is
greyed out. How does the approver reject a requisition?
Answer
------
You cannot reject Requisitions submitted to yourself. You can only reject those
from others.
12. What are standard Quotations used for?
Answer
------
Standard quote is one that you can tie back to a PO. For example, navigate to
RFQ -> Auto create -> enter a PO and reference it back.
13. When do you see the status of a Purchase order as Archived?
Answer
------
You must approve or print a specific purchase order before you can see it in
the history information.
14. Where are standard notes in GUI?
Answer
------
Go to Setup --> Attachments -->Create attachment. Go to Purchase Order and
attach the attachment to the Purchase Order. In GUI, notes are replaced by
attachments.
15. In Oracle Purchasing, where is the automatic numbering for Purchase Order
defined and maintained?
Answer
------
The navigation path for GUI is:
Setup --> Organizations --> Purchasing Options, Numbering alternate region
The navigation path for Character is:
\ Navigate Setup Purchasing Options Purchasing, Numbering options
16. There is a field for the organization code. What is this code?
Answer
------
This code is a short abbreviation for the organization.
17. Can the original Purchase Order can be viewed in any way without resorting
to SQL, for a revised Purchase Order?
Answer
------
The original version of a revised PO cannot be viewed from the PO form or PO
summary form. Information on the original PO can be obtained from the
PO_HEADERS_ARCHIVE and PO_LINES_ARCHIVE tables using the PO_HEADER_ID column as
a common reference using SQL only.

Interview Questions on Oracle Payables or Oracle AP

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:31:00 +0000

1. What are the types of Invoices , what is prepayment & steps to apply it to an Invoice ?

2. What are the Distribution Type while entering the Invoice?

3. What's the difference between the "Payables Open Interface Import"
Program and the "Payables Invoice Import" program?

4.What is Debit Memo & Credit Memo in Payables?

5. What is Proxima Payment Terms?

6. What will accrue in Payables?

7. What is Tolerance?

8. What is Aging Periods?

9. What is a Payable Document?

10. What are the tables associated with Invoice?

11. Which interface tables are used for Invoice Import , give the important columns?

12. What is 2 way , 3 way and 4 way matching?

13. How you will transfer payables to general ledger?

14. You created a set of book , where you will attach this SOB in AP?

15. What is use of AP Accounting Periods?

16. What is Interest Invoice and how it can be created?

17. What is a Hold? Explain the types of Hold

18. How many key flexfields are there in Payables?

19. Explain the set up used for Automatic or Manual Supplier Numbering.

20. Can you hold the partial payment if yes then how?
21. Can you cancel the invoice? If yes, explain.

Questions in Oracle Accounts Receivables

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:30:00 +0000

Oracle Account Receivables (AR) - Functional Overview

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:29:00 +0000

Enter Invoices

Use this form to enter invoices and debit memos one at a time, or as a group called a batch. Oracle Receivables lets you use batch control totals to ensure that you enter invoices accurately. When
you enter an invoice, Oracle Receivables uses your AutoAccounting rules to determine your default general ledger accounts. Oracle Receivables now lets you assign invoicing and accounting rules to invoices you enter through this form. Use Revenue Recognition Program to control when your accounting distributions are created.You can also use this form to review and update information for your invoice. After your invoice has posted to your general ledger you can still update most information. Oracle Receivables maintains a complete audit trail of all the posted changes you make to your accounting entries. Oracle Receivables does not maintain an audit trail when you change an invoice that has not been posted.

Batch (RA_BATCHES)
- Source, Name, Batch Date, GL Date, Period,
- Status (New,Open,Closed, Out of Balance), Currency, Comments
Invoice (RA_CUSTOMER_TRX)
- Number, Invoice Date, Complete,
- Ship To Customer - Name, Number, Address, Contact,
- Bill To Customer - Name, Number, Address, Contact,
- Commitment (Gaurantee,Deposit), Agreement, Invoice Source,
- Invoice Type, Document No, Transaction Cross Reference To,
- Payment Terms, Due Date, GL Date, Salesperson, Remit To, Currency
- Payment Method (Check,Wire,Direct Deposit),
- Customer Bank - Name, Branch, Account
- Invoice - Default Tax (Standard,Exempt,Require), Print Option, Status,
PO Number, PO Revision, PO Date, Invoicing Rule (Advance,Arrears),
- Frieght - Amount, Carrier, Ship Date, Waybill No, FOB Point, Account,
- Sales - Sold To, Sold To No, Territory,
- Receivable Account, Special Instructions, Comments

Default Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Rep Name, Number, Revenue %, Other %

Invoice Lines (RA_CUSTOMER_TRX_LINES)
- Line Number, Item Code, Description, Line Trans Flexfield (AutoInv)
- Quantity, UOM, Tax(Std,Exempt,Req), Unit Price, Extended Price
- Addl Line Info - Order Number, Date, Revision, Line, Sales Order Source,
Invoicing Rule, Accounting Rule
- Tax - Line Number, Tax Code, Tax Rate, Tax Amount,

Revenue Account Assignment (Lines) (RA_CUST_TRX_LINE_GL_DIST)
- Percent, Amount, Account Code, GL Date
- Account Assignment (Tax) - Percent, Amount, Account Code, GL Date

Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Rep Name, Number, Revenue %, Revenue Amount

Invoice Installments (AR_PAYMENT_SCHEDULES)
- Line, Due Days, Due Date, Original Amount , Balance Amount, Status

Maintain Invoices

Use this form to update your invoices, debit memos, and chargebacks. You can change the due date, PO number, salesperson, and remit-to address of a debit item. You can also place a debit item in dispute by specifying a dispute amount, exclude a debit item from finance charges, or update the bill-to information. Oracle Receivables also lets you enter or update the exchange rate of your debit item as long as your debit item does not have any activity against it.You can also use this form to record additional information in the form of notes about your debit items. The profile option for this form that controls whether you can change invoice due dates is Invoice Due Date. If your system administrator sets this option to No, you cannot update an invoice due date in the Installment zone.

Enter Recurring Invoices

Use this form to create automatically recurring invoices for your customers. For example, you need to bill your customers for services or products provided regularly, but you don't want to create a new invoice every time. Oracle Receivables provides the recurring invoices with the same characteristics as the original invoice, except that all dates such as the invoice date, GL date,and due dates are determined using the recurring rule you specify. You can schedule recurring invoices as often as you like and create recurring invoices from closed invoices.When you create recurring invoices Oracle Receivables does not derive the exchange rates and tax rates from the recurred invoice date. Instead, it copies the exchange rate and tax rate from your model invoice. Consequently, if you are creating recurring invoices in a foreign currency, or have tax rates which change over time, you may need to update the exchange and tax rate manually.You can use the Invoice Entry form to update the rates to the
correct rates for your recurred invoice date.
Model
- Source Batch, Source Invoice Number, Customer, GL Date, Invoice Type,
- Invoice Date, Terms, Due Date
Recurring Schedule
- Rule (Monthly,Daily,Yearly ...), First Invoice Date, First GL Date,
- Number of Days, Number of Times
Recurring Invoices
- Invoice Number, Invoice Date, GL Date, Due Date, Amount

Enter Commitments

Use this form to enter or change your customer commitments. You can define commitments for any customer, to record deposits or guarantees of future purchases. Oracle Receivables also lets you
update certain information depending on the commitment status.
Commitment (RA_CUSTOMER_TRX)
- Batch Source, Commitment Number, Date, Bill To, Address, Contact,
- Remit To, Commitment Type(Deposit, Guarantee), Document No, GL Date,
- Agreement, Cross Reference To Transaction, Terms, Primary Salesperson,
- Print, Start Date, End Date, Invoice Text, Currency, Amount, Item,
- Debit Account Code, Credit Account Code
- Commitment Addl Info - Payment Method, Customer Bank Name,
Customer Bank Branch, Customer Bank Account, Special Instructions,
Purchase Order, Purchase Order Revision, Purchase Order Date,
Sales Territory, Line Transaction Flexfield (Unique Line # for Auto Invoice)

Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Person, Number, Revenue Credit %, Revenue Credit Amount

Quick Transaction Entry

Use this form to enter simple one-line invoices, debit memos and on-account credits. If you want to review, update, or delete invoices and debit memos use the Enter Invoices form. If you want to review, update, or delete on-account credits use the Enter Credit memo form.
Batch (RA_BATCHES)
- Source, Name, Batch Date, GL Date, Period,
- Status (New,Open,Closed, Out of Balance), Currency, Comments
Invoices (RA_CUSTOMER_TRX)
- Invoice Number, Invoice Date, Customer Number, Source Batch, Invoice Type,
- Currency, Item Code, Description, Quantity, UOM, Unit Price, Extended Amount,
- Ship To Location, Bill To Location, Terms, Freight Amount, Tax Code, Tax Rate,
- Tax Amount, Sales Person, GL Date

Enter Credit Memos

Use this form to enter, update, and review on-account credits or credit memos against specific invoices, debit memos, or commitments. Oracle Receivables creates the appropriate accounting entries and reverses any sales credits you assigned to your salespeople. Oracle Receivables allows you to apply on-account credits to your customers open debit items in the Enter Receipts and Reapply Credits forms. To speed data entry, you can define credit memo lines in the Define Memo Lines form. These credit memo lines display as QuickPick choices in this form. You can also enter credit memo lines directly in the Credit Memo Lines zone of this form. You print your credit memos in the Print Invoices form.
You can specify the debit item to credit in this form, or create an on-account credit by not specifying one. On-account credits are credits that you assign to your customer's account that are not related to a specific invoice. You can assign on-account credits to debit items in the Reapply Credit form or when you enter receipts.
Batch (RA_BATCHES)
- Source, Name, Batch Date, GL Date, Period,
- Status (New,Open,Closed, Out of Balance), Currency, Comments

Invoices (Query) (RA_CUSTOMER_TRX)
- Invoice Number, Invoice Date, Customer Number, Customer Name,
- Source Batch, Commitment Number, Currency

Credit Memo (RA_CUSTOMER_TRX)
- Source, Number, Complete, Credit Memo Date, Credit Memo Type,
- Transaction, Reason, Document No, GL Date, Currency,
- Line/Tax: Amount, Percent, Freight,
- Credit Method For: Invoices With Rules, Split Term Invoices,
- Salesperson, Customer Reference, Print Option, Print Date, Comments,
- Ship To Customer, Number, Address, Contact,
- Bill To Customer, Number, Address, Contact,
- Payment Method, Customer Bank Name, Branch, Account,
- Receivable Account Code, Freight Account Code

Credit Memo Lines (RA_CUSTOMER_TRX_LINES)
- Line Number, Item Code, Description, Amount, Quantity, Unit Price,
- Reference, Credit Reason, Line Transaction Flexfield

Revenue Account Assignment (Lines) (RA_CUST_TRX_LINE_GL_DIST)
- Percent, Amount, Account Code, GL Date

Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Rep Name, Number, Revenue %, Revenue Amount

Reapply Credits

Use this form to apply, reapply, or unapply on-account credits.Oracle Receivables lets you create on-account credits in the Enter Credit Memos and Quick Transactions forms and then apply them here.
Correcting your mistake is easy since you can reapply or unapply applications before or after posting them to your general ledger. Oracle Receivables gives you the information you need to update any unresolved on-account credits you may have.You can only apply items and receipts to commitments or transactions against a commitment which bring its balance closer to zero.

Enter Invoice Adjustments

Use this form to make either positive or negative adjustments to your invoices, debit memos, chargebacks, on-account credits, deposits, and guarantees. Oracle Receivables currently does not permit adjustments to credit memos. Creating adjustments in Oracle Receivables is easy since you can use predefined adjustment activities. Oracle Receivables automatically provides the accounting information for you. Define your adjustment activities in the Define Receivables Activity form.Oracle Receivables automatically verifies that your adjustment is within your adjustment approval limits. Define adjustment approval limits for your users in the Define Adjustment Limits form. If the amount of the adjustment that you are entering is within your assigned adjustment approval limits, then Oracle Receivables automatically creates the adjustment. If the amount of the adjustment that you are entering is outside your adjustment approval limit range, then Oracle Receivables creates a pending adjustment with a status of Waiting Approval in the Approve Adjustments form. Pending adjustments require approval from a user with the appropriate approval limit before they affect the remaining balance of the transaction. If you need to gather more information about your item, you can place your adjustment on hold by assigning it a status of More Research.

Invoice (Query Invoice To Be Adjusted)
- Invoice Number, Customer Number, Customer Name

Adjustments (AR_ADJUSTMENTS)
- Activity Name (OverCharge, WriteOff),
- Adjustment Type (Charge,Invoice,Line,Freight,Tax), Amount,
- GL Date, Adjustment Date, Accounting Code, Adj Reason,
- Comment, Status (Approved,Pending Approval, Research Required)

Sales Credit Adjustment

With this form you can change revenue and non-revenue sales credits for your invoices, credit memos, and debit memos. If AutoAccounting is based on salesperson and you have not made changes to the revenue accounts in the Enter Invoices form, Oracle Receivables automatically updates your revenue accounts when you change sales credit information in this form. When you change the sales credits for a line, Oracle Receivables creates a record that reverses the original accounting when you save. For example, you have an invoice for which Salesperson A is assigned to 100% of the sales revenue. Now you decide to give Salesperson B 25% of the sales revenue. You must first change the revenue percent of Salesperson A to 75% and then enter a new line for Salesperson B. Oracle Receivables creates a record for Salesperson A that reverses the sales credit for 25%.
Invoice (Query Invoice To Be Adjusted)
- Invoice Number, Source Batch, Customer Name, Currency, Date,
- GL Date, Status
Line Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Line Number, Sales Rep Name, Revenue %, Revenue Amount

Enter Exchange Rate Adjustments

Use this form to adjust the rate type, rate date, and exchange rate
for a foreign currency receipt.

Approve Adjustments

Use this form to approve, hold, reject or request more information about pending adjustments. Oracle Receivables automatically enforces the adjustment limits that you define in the Define Adjustment Limits form as you enter your adjustments. You can approve adjustments that are within your individual approval limits. You can create all other actions even if an adjustment is outside your user approval limits. You can also review adjustment histories and record your comments. Adjustment records that require approval do not affect the balance or status of the transaction you are adjusting. When you approve adjustments in this form, Oracle Receivables automatically updates the balance of the transaction you are adjusting. You can only approve a transaction that has been selected and approved for automatic receipt generation, if the user profile option AR: Invoices with Unconfirmed Receipts is set to Adjustment or Adjustment and Credit.
Review
- Creator, Last Reviewer, Order By (Status,Amount)
Adjustments (AR_ADJUSTMENTS)
- Action (Approved, Pending Approval, Rejected, Research Required),
- Adjustment Number, Invoice Number, Type, Amount, Status, Activity Name,
- Customer, GL Date

Submit AutoAdjustment

Use this form to automatically adjust the remaining balances of all open invoices, debit memos, credit memos, and chargebacks that meet specific criteria you define. You can select debit items by remaining amount, remaining percent, due date, transaction type, customer name, or customer number. Additionally, you can specify your adjustment type, activity, GL date, and reason for each AutoAdjustment submission. If the adjustment amount is within your adjustment approval limits, Oracle Receivables automatically approves your adjustment. If the adjustment amount is outside your adjustment approval limits, Oracle Receivables creates pending adjustments with a status of Waiting Approval.

Enter Receipts

Use the Enter Receipts form to enter manual receipts and apply them to transactions in open accounting periods. You can also create chargebacks or adjustments against these transactions in this form.The Enter Receipts form also lets you apply on-account credits to your receipts. If you need to create a new on-account credit while in Enter Receipts, you can use the Zoom feature to go to the Enter Credit Memos form, create your on-account credit, and then return to the Enter Receipts form. You can apply on-account credits to specific debit items in the Reapply Credits form.Your system administrator sets profile options that affect this form. If the Allow Actions profile option is set to No, then you cannot navigate to the Actions zone to create adjustments or chargebacks. The AR: Cash - Default Amount Applied profile option governs the default application amount when you apply your receipts in the receipt Applications zone. Valid choices for this profile option are Remaining Amount of the Invoice and Unapplied Amount of the Receipt. You can override this profile option at the user level in the Update Personal Profile Options form.
Receipt Batch (AR_BATCHES)
- Source, Name, Deposite Date, Currency, Batch Date, GL Date,
- Receipt Class (Manual Receipt, Wire), Payment Method,
- Remittance Bank Account, Status, Comment, Control Count/Amounts
Receipt (AR_CASH_RECEIPTS)
- Receipt Number (Check #,Credit Card #), Receipt Date, GL Date, Currency,
- Amount, Method, Bank Account, Deposit Date, Apply Date, Invoice Number

Mass Receipt Application
Specific Receipt Applications (AR_RECEIVABLE_APPLICATIONS)
- Customer Number, Invoice Number, Amount, Balance Due
Action
- Adjustments and Chargebacks

QuickCash

Use the QuickCash form to quickly enter receipts into Oracle Receivables. You can apply your receipts to one or many transactions, place receipts on-account, and enter receipts as either Unidentified or Unapplied. Oracle Receivables lets you process all your invoice-related receipts with this form. You can simply enter the information you have now, and reconcile any unapplied and unidentified amounts later using either the Enter Receipts form or the Enter Invoice Adjustments form. Since allQuickCash receipts and applications are stored in a temporary area, you can use the QuickCash form as an additional level of security until you submit them through Post Batch. For example, if you hire temporary help during peak periods, have them perform their work in this form. You can review their work prior to updating your customers' balances. Once you process your QuickCash receipt batches, use the Post QuickCash form to update your customer balances.
Receipt Batch (AR_BATCHES)
- Source, Name, Deposite Date, Currency, Batch Date, GL Date,
- Receipt Class (Manual Receipt, Wire), Payment Method,
- Remittance Bank Account, Status, Comment, Control Count/Amounts
Receipts (AR_INTERIM_CASH_RECEIPTS)
- Receipt Number (Check #,Credit Card #), Receipt Date, Currency, Invoice Number,
- Customer Name, Number, Billing Location, Payment Method, Remittance Bank

Enter Miscellaneous Transactions

Use this form to enter your non-invoice related transactions such as investment income, interest income, and refunds into Oracle Receivables. Oracle Receivables simplifies your data entry in thisform by utilizing the distribution sets and the activities that you defined during your set up process for miscellaneous transactions. Use the Miscellaneous Transactions report to review all of these transactions.
Receipt Batch (AR_BATCHES)
- Source, Name, Deposite Date, Currency, Batch Date, GL Date,
- Receipt Class (Manual Receipt, Wire), Payment Method,
- Remittance Bank Account, Status, Comment, Control Count/Amounts
Receipts (AR_CASH_RECEIPTS)
- Activity (Interest Income, Invest Income, Misc Cash, Misc Refund, Stock Option),
- Distribution Set, Receipt Number, Receipt Date, GL Date, Currency, Amount,
- Tax Code, Payment Method, Remittance Bank, Deposit Date,
- Reference Type (Receipt,Payment,Remittance), Reference Number
Accounting Distribution (AR_MISC_CASH_DISTRIBUTIONS)
- Percent, Amount, Accounting Code, Comments, Posted

Reverse Receipts

Use this form to reverse your receipts. You can use this form to reverse both standard invoice-related receipts and non-invoice related receipts entered in the Enter Miscellaneous Transactions form.
To reverse a receipt you can either create a standard reversal or debit memo reversal. When you choose to create a standard reversal, Oracle Receivables automatically creates reversal journal entries for your general ledger, and reopens all of the debit and credit items that you closed with the original receipt. When you choose to create a debit memo reversal for your receipt, Oracle Receivables does not update any of the receipt activity associated with this original receipt. Your new debit memo reversal is actually a new receivable. If you choose to create a standard reversal for a receipt you applied in the Enter Receipts form, Oracle Receivables automatically reverses any adjustments or chargebacks that you created in the Enter Receipts form as long as you have not posted.
Receipt Reversal (Query) (AR_CASH_RECEIPTS)
- Receipt- Receipt Number, Customer, Batch, Bank, Payment Method
- Reversal - Reversal Date, GL Date, Debit Memo,
Category (Unsufficient Funds,Reverse Payment,Stop Payment), Reason

Reapply Receipts

Use this form to apply unidentified, unapplied, or on-account receipts to invoices, debit memos, chargebacks, or on-account credits. You can reapply receipts to both automatic and manually entered receipts. You can also reapply receipts that you previously applied in error, before or after posting these items to your general ledger. You can use the Zoom feature to navigate to the View Transaction History form and review additional details about your receipt, debit item, or credit item.
Receipt (Query) (AR_CASH_RECEIPTS)
- Receipt Number, Method, Date, Amount, Customer, Batch, Category
Receipt Applications (AR_RECEIVABLE_APPLICATIONS)
- Action (Delete, Reapply), Invoice Number, Type, Inv Amount, Balance Due
Reapply Applications
- Customer Number, Invoice Number, Amount, GL Date, Apply Date

Maintain Lockbox Transmission Data

Use this form to review and edit your lockbox transmission information that you have imported into Oracle Receivables from your bank files. Oracle Receivables lets you review, delete, edit,and maintain your lockbox transmission data here before you proceed to the lockbox validation step. Oracle Receivables displays the lockbox and batch records first, followed by the receipts and overflow records for each transmission. Oracle Receivables also lets you edit your lockbox data here when it fails the validation step. Once the lockbox program has successfully validated your transmission data, you can review and edit it in the QuickCash form. If you are updating data, be sure to update only those fields that have data which corresponds to the transmission format that you used when you submitted the import process. You can use AutoCash Rules with Lockbox to automatically apply receipts to open debit items if you do not specify debit item information in your lockbox transmission format.
Transmission Records (AR_PAYMENTS_INTERFACE)
- Name, Creation Date, Lock Box Number, Batch Name, Item Number, Receipt Number,
- Overflow Sequence, Record Identifier, Status, Comments
- Control Info - Bank Origination Number, Destination Account, Deposit Date, GL Date,
Deposit Time, Currency, Transmission Record Count, Transmission Amount,
Lockbox Batch Count, Lockbox Amount, Lockbox Receipt Count,
Batch Receipt Count, Batch Amount,
- Receipt Info - Overflow Indicator, Receipt Date, Receipt Number, Payment Method,
Remittance Amount, Customer Number, Billing Location, Transmit Routing,Account,
- Receipt Applications - Applied Amount, Invoice Number, Status,
- Receipt Attributes - Attribute (1 - 15)

Reconcile Receipts

Use this form to reconcile your bank account and create new batches of cleared receipts for receipts with a receipt class which requires clearance. You perform this step to mark receipts as cleared. When you clear a receipts Oracle Receivables credits your cash account and debits your remittance or factoring account.Oracle Receivables allows you to query up existing batches of cleared receipts, create new batches of cleared receipts, and add cleared receipts to existing batches, in this form. Oracle Receivables only allows you to query up receipts for reconciliation which you have remitted to the bank accounts that you specify in this form, if these receipts have a receipt class which requires remittance.

Batch (AR_BATCHES)
- Batch Name, Batch Date, Bank Name, Branch, Account, Currency, GL Date,
- Receipt Selection Criteria - Remittance Dates Range, Receipt Number Range
Clear Receipts (AR_CASH_RECEIPT_HISTORY)
- Action (Clear, Reverse), Receipt Number, Receipt Amount, Date Cleared,
- Amount Cleared, GL Date, Status, Remit Date, Maturity Date

Create Automatic Remittances

Use this form to mark receipts that you want to include in a remittance batch. You can create unapproved, approved, or approved and formatted remittance batches. If the AR: Automatic Remittance Creation profile is set to Format, then you can create, approve and format the remittance batch in one step. If this profile option is set to Approve, then Oracle Receivables lets you create and approveyour remittance batches in this form. If it is set to Create, then you can only create remittance batches here, and need a user for whom the option is set to Format to approve and format them in the Approve Automatic Remittances form. When you save your work, Oracle Receivables initiates a single concurrent process which produces a report giving you details of the remittance batches which have been created. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information. Oracle Receivables will only include receipts with Payment Methods for which the receipt class requires remittance in the remittance batches that it creates.
Creation Criteria (AR_BATCHES)
- Batch Name, Batch Date, Remittance Method, Payment Method, GL Date, Currency,
- Remittance Bank, Branch, Account, Action (Create,Approve, Format)
- Receipt Selection Criteria - Receipt Dates Range, Receipt Number Range,
Customer Number Range, Maturity Date Range, Document Number Range

Approve Automatic Remittances

Use this form to review your remittance batches for approval and formatting.The actions that you can take depend upon the value that your system administrator has set for the profile option AR: Automatic Remittance Creation. Oracle Receivables will only create remittance batches from receipts with a payment method whose receipt class requires remittance. After you have submitted a remittance batch for approval or formatting you can still review it in this form, but you cannot view receipt level information. If you update a receipt's remittance bank account, Oracle Receivables excludes the receipt from this batch unless you are remitting to the clearing institution for this new remittance bank.When you save your remittance action, Oracle Receivables displays the concurrent request id of the concurrent process you have submitted to approve the batch of remittances that you specify. This concurrent process produces a report giving you details of the batches which have been approved. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information.

Format Automatic Remittances

Create Automatic Receipts
Use this form to create unapproved, approved, or approved and formatted automatic receipts. Whether you can create approved or formatted automatic receipts depends on the value that your system
administrator has set for the AR: Automatic Receipt Creation Authority profile option. When you save your record, Oracle Receivables initiates a single concurrent process which produces a report giving you details of the automatic receipt batches which have been created. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information. If confirmation is not required for the receipt class that you assigned to the payment method you entered for the creation of this automatic receipt batch, then Oracle Receivables automatically confirms all of the receipts within this batch when you approve the batch. If your receipt class requires confirmation then Oracle Receivables requires that you confirm the automatic receipts in this batch in the Confirm Automatic Receipts form, once they are approved. If you have defined customer relationships and you want to create automatic receipts for the parent customer, then you must assign this parent customer's bank accounts to the invoices paid with the automatic receipt.
Creation Criteria (AR_BATCHES)
- Batch Name, Batch Date, Payment Method, GL Date, Currency,
- Action (Create,Approve, Format)
- Invoice Selection Criteria - Invoice Dates Range, Invoice Number Range,
Customer Number Range, Due Date Range, Document Number Range

Approve Automatic Receipts

Use the Approve Automatic Receipts form to approve,delete and format your automatic receipts. The actions that you can take in this form depend upon the value that your system administrator hasset for the AR: Automatic Receipt Creation Authority profile option.Query up the automatic receipts that you want to approve, delete or format, and then select the action you want to take. Oracle Receivables allows you to update the rate date, type and rate of your foreign currency automatic receipt batches if the status of the batch is Completed Creation.When you save your record, Oracle Receivables initiates a single concurrent process which produces a report giving you details of the automatic receipt batches which have been approved. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information. If confirmation is not required for the receipt class that you assigned to the payment method you entered for the creation of thisautomatic receipt batch, then Oracle Receivables automatically confirms all of the receipts within this batch when you approve the batch. If your receipt class requires confirmation then Oracle Receivables requires that you confirm the automatic receipts in this batch in the Confirm Automatic Receipts form, once they are
approved.

Format Automatic Receipts

Confirm Automatic Receipts

Use this form to enter customer confirmations of approved automatic receipts and their applications. Oracle Receivables updates your customer balances when you confirm a receipt. After you query up the automatic receipt you want to confirm, you can review and update the invoices you have selected to apply to the receipt as well as remittance bank and customer bank information. Oracle Receivables allows you to update the invoice application amounts and bank information.Oracle Receivables only requires that you use this form to confirm receipts with payment methods that belong to receipt classes which require confirmation. You can query receipts for receipt classes which do not require confirmation, if they are not yet included in a remittance batch, but you can only update their remittance and factoring information. Oracle Receivables does not allow you to delete a batch of automatic receipts that has a status of Completed Approval if you have confirmed any of the receipts within this batch.
Receipt (Query) (AR_CASH_RECEIPTS)
- Receipt Number, Document Number, Receipt Class, Payment Method,
- Receipt Date, Receipt Amount, Currency, Customer Name, Customer Number,
- Location
- Confirmation Info - Confirm, GL Date, Confirmation Date, Factor,
Confirmed Amount,Customer Reference
- Remittance Detail (Old/New) - Maturity Date, Remittance Bank Name,
Remittance Bank Branch, Remittance Bank Account, Customer Bank Name,
Customer Bank Branch, Customer Bank Account
Invoice Details
- Balance Due, Approved Amount, Invoice Number, Invoice Type, Invoice Date

Post QuickCash

Use this form to recognize and update your customer's accounts with receipts entered in the QuickCash forms or through the Process Lockbox program. Oracle Receivables displays only those batches which have not been posted to update customer accounts. For batches entered through Lockbox, you can skip this step by choosing to post the batch to customer accounts in the Run LockBox Interface
form.
Available Batches (AR_BATCHES)
- Options (Select), Source, Name, Batch Date, Status, Request

Record A Call

Use this form to record information about your customer calls.Oracle Receivables provides collectors with essential call details and makes it easy to record call information and review previous calls. Use this form to log call topics, outcomes, and actions. You can place amounts in dispute in the Call Actions zone. Oracle Receivables also lets you view a summary of your customer calls in the View Call History form. Use \ Other Zoom to quickly review other information in the View Invoice Image, View Transaction History, and View Call History forms while you talk to your customer. You can also review additional customer payment or open invoice information in the View Receipts by Customer and View Customer Account Detail forms. If you enter this form through the Choose Next Screen option from another form, Oracle Receivables defaults information into the Customer, Contact, and Call Topic zones.
Customer
- Name, Address, Contact Who Called
Call Topic
- Call Taken By, Invoice Details
Call Out Come
- Customer Response, Promise, Follow Up, Call Out Come, Memo Pad
Call Action
- Action, Amount, Memo Pad, Notify

View Transaction History

Use this form to review the history of a transaction. The complete history includes receipts, credit memos, on-account credits, adjustments, and debit items. With Choose Next Screen, you can move easily between inquiry screens without using the main menu. Oracle Receivables completes your new form with information about the transaction or receipt you are reviewing when you use Choose Next Screen to review an open debit item, on-account credit, credit memo information, an image of your debit item, on-account credit,or credit memo, and customer calls.

View Invoice Image

Use this form to review detailed information about a transaction. With Choose Next Screen, you can move easily between inquiry screens without using the main menu. When you choose next screen, Oracle Receivables fills in your new form with information regarding the transaction you are reviewing. You can use this to navigate to forms where you can review application and adjustment activity, call history, or quickly find other transactions for the customer.

View Batch Control Amounts

Use this form to quickly determine the status of your receipt batches. Oracle Receivables displays receipt batch information so you can see where the control counts and amounts are not equal to your actual counts and amounts. Use the View Batch Detail and the View Batch Unapplied Amounts forms to further review your receipt batches and the receipts they contain.

View Batch Unapplied Amounts

Use this form to review unidentified and unapplied receipt counts and amounts by receipt batch. You can review receipt batches based on the search criteria you specify. Oracle Receivables lets you quickly access complete information and determine where you need to continue your work. You create receipt batches when you use the QuickCash, Enter Receipts, and Enter Miscellaneous Transactions forms. Use the Review Receipts by Batch form to access more detailed information about any of your receipt batches.

View Batch Detail

Use this form to review all payments within a receipt batch. You query the receipt batch you want to review in the Batch zone, thenyou can use additional criteria to query receipts within that batch.

View Receipts by Customer

Use this form to review your customers' receipts. You can review receipts for a particular customer or all receipts based on the search criteria you enter in the Receipts zone. No matter how you select receipts to review, Oracle Receivables always displays customer information as you navigate to each receipt. Use the ViewCustomer Account Detail form to review the status of a particular customer's account.

View Transmission History

Use this form to review information about lockbox transmissions you submit through the Submit Lockbox Processing form. You can review information about the transmission record set in the form: use the Maintain Lockbox Transmission Data form to review individual records within the transmission.

View Customer Account Detail

Use this form to review the status of a customer's account. Oracle Receivables displays details of all receipts and transactions for your customer. Oracle Receivables also lets you see the current customer account balances in your functional currency using the most recent exchange rate. Oracle Receivables displays different information depending on the option you select in the customer zone.

View Customer Account Summary

Use this form to review your customer accounts. Oracle Receivables lets you choose your customers for review by using a wide range of search criteria. Oracle Receivables displays all customers and customer account balance information that match your search criteria. Use Choose Next Screen to review additional details for each customer account including account summary information by accounting period, credit statistics, aging information, credit profile, and account details. Oracle Receivables displays all amounts in your functional currency. Use \ Other Zoom to navigate directly to the Customer Account Detail form where you can view thedetails of all items for each customer.

View Call History

Use this form to review previous customer call information. Oracle Receivables gives you detailed call history information to make collections more efficient.You can see which customers are not
meeting their commitments on outstanding receivables, and then decide what actions you need to take. Oracle Receivables lets you review the calls here that you entered in the Record A Call form.

Customer Inquiry

Use this form when you need to identify a customer and you only have a minimal amount of information. Oracle Receivables can identify customers from a partial customer name, a payment number, a contact name, an address, a city, a state, a postal code, a transaction number, or a telephone number.

Run Concurrent Programs

- Run Auto Invoice
- Run Customer Interface
- Run Tax Rate Interface
- Run Revenue Recognition

Submit Lockbox Processing

Use this form to submit your lockbox transmission processes and transfer payment information from your bank files into Oracle Receivables. Oracle Receivables lets you submit each step of the lockbox transmission process as a separate request or submit all of the transmission processes as one request, depending upon your specific business needs. For example, you may choose to import data into Oracle Receivables and examine it before validating it within Oracle Receivables. Upon examination and approval, you can submit the validation step and Oracle Receivables automatically validates your data and creates QuickCash payment batches. Oracle Receivables lets you view your payment batches in the QuickCash form before updating your customers' accounts using Post QuickCash. Oracle Receivables gives you the flexibility to either examine and control each step, or submit all of the steps at once. The 'Process Lockboxes' program transfers payments received by your bank to Oracle Receivables. Since this program is entering Payments into Oracle Receivables, the program must be smart enough to enter the primary billing location as well. If your bank is incapable of transmitting the billing location of your payments, then the program should choose the primary billing location of the identified customer. Oracle Receivables will give you the option to determine the fate of your payments when there is no billing location available. This option will be enabled in the 'Define Lockboxes' form. Your options will be to reject the payments when the billing location does not exist or to ignore the missing billing location. If you desire the first option, the Oracle Receivables will issue an error message indicating that the billing location was not available. The 'Process Lockboxes' program must evaluate the status of the 'Bill-To Site'parameter in the 'QuickCash' form to determine if the 'Billing Location' is required.

Open/Close Accounting Periods

Use this form to open and close accounting periods in your receivables calendar. You define your receivables calendar in the Define Calendar form. Oracle Receivables references the statuses of these accounting periods to control transaction entry and journal entry creation to your general ledger. You may not enter an activity in a closed accounting period.

Open Next Accounting Period
- Latest Open Period, Open Next Period
Accounting Periods
- Period Status (Open,Close,Future), Period Number, Fiscal Year,
- Period Name,Begin Date, End Date

Run GL Interface

Use this form to create journal entries to post in detail or summary. Oracle Receivables first transfers transaction data into the GL_INTERFACE table and generates the Posting Execution Report. Use the Posting Execution Report to see a summary of transactions that are imported into the GL_INTERFACE table. Transactions that failed validation are printed in the Unposted Items Report produced from this run. From here, if you have Oracle General Ledger installed you can use Journal Import to create journal entries to post to your general ledger. If you do not have Oracle General Ledger installed you can use your feeder system to import data from the GL_INTERFACE table.
Transfer Crireria

SQL - Injection

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:14:00 +0000

If you take user input through a webpage and insert it into a SQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection.
This lesson will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as PERL Script.
Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQL statement that you will unknowingly run on your database.
Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching.
In the example below, the name is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars (Modify these rules as needed).

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM CUSTOMERS 
                          WHERE name=$matches[0]");
}
else 
{
   echo "user name not accepted";
}

To demonstrate the problem, consider this excerpt:

// supposed input
$name = "Qadir'; DELETE FROM CUSTOMERS;";
mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'");

The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster: the injected DELETE query removes all records from CUSTOMERS.
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking, or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails.
However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.

Preventing SQL Injection:

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

The LIKE Quandary:

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied % and _ characters to literals. Use addcslashes(), a function that let's you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string("%str"), "%_");
// $sub == \%str\_
mysql_query("SELECT * FROM messages 
             WHERE subject LIKE '{$sub}%'");

SQL - Handling Duplicates

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:13:00 +0000

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.
The SQL DISTINCT keyword, which we already have discussed, is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.

Syntax:

The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows:

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

First let us see how the following SELECT query returns duplicate salary records:

SQL> SELECT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

This would produce following result where salary 2000 is coming twice which is a duplicate record from the original table.

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

Now let us use DISTINCT keywork with the above SELECT query and see the result:

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

This would produce following result where we do not have any duplicate entry:

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

SQL - Using Sequences

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:13:00 +0000

A sequence is a set of integers 1, 2, 3, ... that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them.
This chapter describes how to use sequences in MySQL.

Using AUTO_INCREMENT column:

The simplest way in MySQL to use Sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.

Example:

Try out following example. This will create table and after that it will insert few rows in this table where it is not required to give record ID because its auto incremented by MySQL.

mysql> CREATE TABLE INSECT
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM INSECT ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Obtain AUTO_INCREMENT Values:

LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. otherwise PERL and PHH scripts provide exclusive functions to retrieve auto incremented value of last record.

PERL Example:

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle:

$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP Example:

After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling mysql_insert_id( ):

mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence:

There may be a case when you have deleted many records from a table and you want to resequence all the records. This can be done by using a simple trick but you should be very careful to do so if your table is having join with other table.
If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again. The following example shows how to renumber the id values in the insect table using this technique:

mysql> ALTER TABLE INSECT DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

Starting a Sequence at a Particular Value:

By default MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation. Following is the example where MySQL will start sequence from 100.

mysql> CREATE TABLE INSECT
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);

Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

SQL - Sub Queries

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:12:00 +0000

A Subquery or Inner query or Nested query is a query within another SQL query, and embedded within the WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.
There are a few rules that subqueries must follow:

Subqueries must be enclosed within parentheses.
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
A subquery cannot be immediately enclosed in a set function.
The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.

Subqueries with the SELECT Statement:

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now let us check following sub-query with SELECT statement:

SQL> SELECT * 
     FROM CUSTOMERS 
     WHERE ID IN (SELECT ID 
                  FROM CUSTOMERS 
                  WHERE SALARY > 4500) ;

This would produce following result:

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  5 | Hardik   |  27 | Bhopal  |  8500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

Subqueries with the INSERT Statement:

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.
The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ]) ]
           SELECT [ *|column1 [, column2 ]
           FROM table1 [, table2 ]
           [ WHERE VALUE OPERATOR ]

Example:

Consider a table CUSTOMERS_BKP with similar structure as CUSTOMERS table. Now to copy complete CUSTOMERS table into CUSTOMERS_BKP, following is the syntax:

SQL> INSERT INTO CUSTOMERS_BKP
     SELECT * FROM CUSTOMERS 
     WHERE ID IN (SELECT ID 
                  FROM CUSTOMERS) ;

Subqueries with the UPDATE Statement:

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.
The basic syntax is as follows:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example:

Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.
Following example updates SALARY by 0.25 times in CUSTOMERS table for all the customers whose AGE is greater than or equal to 27:

SQL> UPDATE CUSTOMERS
     SET SALARY = SALARY * 0.25
     WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
                   WHERE AGE >= 27 );

This would impact two rows and finally CUSTOMERS table would have following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |   125.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  2125.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Subqueries with the DELETE Statement:

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.
The basic syntax is as follows:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example:

Assuming, we have CUSTOMERS_BKP table available which is backup of CUSTOMERS table.
Following example deletes records from CUSTOMERS table for all the customers whose AGE is greater than or equal to 27:

SQL> DELETE FROM CUSTOMERS
     WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
                   WHERE AGE > 27 );

This would impact two rows and finally CUSTOMERS table would have following records:

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  2 | Khilan   |  25 | Delhi   |  1500.00 |
|  3 | kaushik  |  23 | Kota    |  2000.00 |
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  6 | Komal    |  22 | MP      |  4500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

SQL - Clone Tables

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:11:00 +0000

There may be a situation when you need an exact copy of a table, and CREATE TABLE ... SELECT... doesn't suit your purposes because the copy must include the same indexes, default values, and so forth.
If you are using MySQL RDBMS, you can handle this situation by following steps.

Use SHOW CREATE TABLE command to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.
Modify the statement to change the table name to that of the clone table and execute the statement. This way you will have exact clone table.
Optionally, If you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.

Example:

Try out following example to create a clone table for TUTORIALS_TBL whose structure is as follows:

Step 1:

Get complete structure about table

SQL> SHOW CREATE TABLE TUTORIALS_TBL \G;
*************************** 1. row ***************************
       Table: TUTORIALS_TBL
Create Table: CREATE TABLE `TUTORIALS_TBL` (
  `tutorial_id` int(11) NOT NULL auto_increment,
  `tutorial_title` varchar(100) NOT NULL default '',
  `tutorial_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`tutorial_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE=MyISAM
1 row in set (0.00 sec)

Step 2:

Rename this table and create another table

SQL> CREATE TABLE `CLONE_TBL` (
  -> `tutorial_id` int(11) NOT NULL auto_increment,
  -> `tutorial_title` varchar(100) NOT NULL default '',
  -> `tutorial_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`tutorial_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (1.80 sec)

Step 3:

After executing step 2 you will a clone table in your database. If you want to copy data from old table then you can do it by using INSERT INTO... SELECT statement.

SQL> INSERT INTO CLONE_TBL (tutorial_id,
    ->                        tutorial_title,
    ->                        tutorial_author,
    ->                        submission_date)
    -> SELECT tutorial_id,tutorial_title,
    ->        tutorial_author,submission_date,
    -> FROM TUTORIALS_TBL;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

Finally you will have exact clone table as you wanted to have.

SQL - Temporary Tables

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:10:00 +0000

There are RDBMS which support temporary tables. Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables.
The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates.
Temporary tables are available in MySQL version 3.23 onwards. If you use an older version of MySQL than 3.23 you can't use temporary tables, but you can use heap tables.
As stated earlier temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Example

Here is an example showing you usage of temporary table:

mysql> CREATE TEMPORARY TABLE SALESSUMMARY (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SALESSUMMARY
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SALESSUMMARY;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

When you issue a SHOW TABLES command then your temporary table would not be listed out in the list. Now if you will log out of the MySQL session and then you will issue a SELECT command then you will find no data available in the database. Even your temporary table would also not exist.

Dropping Temporary Tables:

By default all the temporary tables are deleted by MySQL when your database connection gets terminated. Still you want to delete them in between then you do so by issuing DROP TABLE command.
Following is the example on dropping a temproary table.

mysql> CREATE TEMPORARY TABLE SALESSUMMARY (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SALESSUMMARY
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SALESSUMMARY;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SALESSUMMARY;
mysql>  SELECT * FROM SALESSUMMARY;
ERROR 1146: Table 'TUTORIALS.SALESSUMMARY' doesn't exist

SQL - Date Functions

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 06:02:00 +0000

Following is the list of all important Date and Time related functions available through SQL. There are various other functions supported by your RDBMS. Given list is based on MySQL RDBMS.

Name	Description
ADDDATE()	Add dates
ADDTIME()	Add time
CONVERT_TZ()	Convert from one timezone to another
CURDATE()	Return the current date
CURRENT_DATE(), CURRENT_DATE	Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME	Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	Synonyms for NOW()
CURTIME()	Return the current time
DATE_ADD()	Add two dates
DATE_FORMAT()	Format date as specified
DATE_SUB()	Subtract two dates
DATE()	Extract the date part of a date or datetime expression
DATEDIFF()	Subtract two dates
DAY()	Synonym for DAYOFMONTH()
DAYNAME()	Return the name of the weekday
DAYOFMONTH()	Return the day of the month (1-31)
DAYOFWEEK()	Return the weekday index of the argument
DAYOFYEAR()	Return the day of the year (1-366)
EXTRACT	Extract part of a date
FROM_DAYS()	Convert a day number to a date
FROM_UNIXTIME()	Format date as a UNIX timestamp
HOUR()	Extract the hour
LAST_DAY	Return the last day of the month for the argument
LOCALTIME(), LOCALTIME	Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()	Synonym for NOW()
MAKEDATE()	Create a date from the year and day of year
MAKETIME	MAKETIME()
MICROSECOND()	Return the microseconds from argument
MINUTE()	Return the minute from the argument
MONTH()	Return the month from the date passed
MONTHNAME()	Return the name of the month
NOW()	Return the current date and time
PERIOD_ADD()	Add a period to a year-month
PERIOD_DIFF()	Return the number of months between periods
QUARTER()	Return the quarter from a date argument
SEC_TO_TIME()	Converts seconds to 'HH:MM:SS' format
SECOND()	Return the second (0-59)
STR_TO_DATE()	Convert a string to a date
SUBDATE()	When invoked with three arguments a synonym for DATE_SUB()
SUBTIME()	Subtract times
SYSDATE()	Return the time at which the function executes
TIME_FORMAT()	Format as time
TIME_TO_SEC()	Return the argument converted to seconds
TIME()	Extract the time portion of the expression passed
TIMEDIFF()	Subtract time
TIMESTAMP()	With a single argument, this function returns the date or datetime expression. With two arguments, the sum of the arguments
TIMESTAMPADD()	Add an interval to a datetime expression
TIMESTAMPDIFF()	Subtract an interval from a datetime expression
TO_DAYS()	Return the date argument converted to days
UNIX_TIMESTAMP()	Return a UNIX timestamp
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time
WEEK()	Return the week number
WEEKDAY()	Return the weekday index
WEEKOFYEAR()	Return the calendar week of the date (1-53)
YEAR()	Return the year
YEARWEEK()	Return the year and week

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr.

mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1998-02-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ADDTIME(expr1,expr2)

ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.

mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 1998-01-02 01:01:01.000001                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONVERT_TZ(dt,from_tz,to_tz)

This converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')           |
+---------------------------------------------------------+
| 2004-01-01 13:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00')     |
+---------------------------------------------------------+
| 2004-01-01 22:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURDATE()

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

mysql> SELECT CURDATE();
+---------------------------------------------------------+
| CURDATE()                                               |
+---------------------------------------------------------+
| 1997-12-15                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() + 0;
+---------------------------------------------------------+
| CURDATE() + 0                                           |
+---------------------------------------------------------+
| 19971215                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURRENT_DATE and CURRENT_DATE()

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE()

CURTIME()

Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

mysql> SELECT CURTIME();
+---------------------------------------------------------+
| CURTIME()                                               |
+---------------------------------------------------------+
| 23:50:26                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME() + 0;
+---------------------------------------------------------+
| CURTIME() + 0                                           |
+---------------------------------------------------------+
| 235026                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CURRENT_TIME and CURRENT_TIME()

CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03')                             |
+---------------------------------------------------------+
|  2003-12-31                                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATEDIFF(expr1,expr2)

DATEDIFF() returns expr1 . expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+---------------------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30')            |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

These functions perform date arithmetic. date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a .-. for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted.
The INTERVAL keyword and the unit specifier are not case sensitive.
The following table shows the expected form of the expr argument for each unit value;

unit Value	ExpectedexprFormat
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

The values QUARTER and WEEK are available beginning with MySQL 5.0.0.

mysql> SELECT DATE_ADD('1997-12-31 23:59:59', 
    -> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL...             |
+---------------------------------------------------------+
| 1998-01-01 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR)                 |
+---------------------------------------------------------+
| 1999-01-01 01:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT(date,format)

Formats the date value according to the format string.
The following specifiers may be used in the format string. The .%. character is required before format specifier characters.

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, .)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal .%. character
%x	x, for any.x. not listed above

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y')          |
+---------------------------------------------------------+
| Saturday October 1997                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
    -> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00.......                 |
+---------------------------------------------------------+
|  22 22 10 10:23:00 PM 22:23:00 00 6                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DATE_SUB(date,INTERVAL expr unit)

This is similar to DATE_ADD() function.

DAY(date)

DAY() is a synonym for DAYOFMONTH().

DAYNAME(date)

Returns the name of the weekday for date.

mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05')                                   |
+---------------------------------------------------------+
| Thursday                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFMONTH(date)

Returns the day of the month for date, in the range 0 to 31.

mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03')                                |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFWEEK(date)

Returns the weekday index for date (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values correspond to the ODBC standard.

mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03')                                  |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DAYOFYEAR(date)

Returns the day of the year for date, in the range 1 to 366.

mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03')                                 |
+---------------------------------------------------------+
| 34                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

EXTRACT(unit FROM date)

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
+---------------------------------------------------------+
| EXTRACT(YEAR FROM '1999-07-02')                         |
+---------------------------------------------------------+
| 1999                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
+---------------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03')          |
+---------------------------------------------------------+
| 199907                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FROM_DAYS(N)

Given a day number N, returns a DATE value.

mysql> SELECT FROM_DAYS(729669);
+---------------------------------------------------------+
| FROM_DAYS(729669)                                       |
+---------------------------------------------------------+
| 1997-10-07                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Use FROM_DAYS() with caution on old dates. It is not intended for use with values that precede the advent of the Gregorian calendar (1582).

FROM_UNIXTIME(unix_timestamp)

FROM_UNIXTIME(unix_timestamp,format)

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.
If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT() function.

mysql> SELECT FROM_UNIXTIME(875996580);
+---------------------------------------------------------+
| FROM_UNIXTIME(875996580)                                |
+---------------------------------------------------------+
| 1997-10-04 22:23:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

HOUR(time)

Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.

mysql> SELECT HOUR('10:05:03');
+---------------------------------------------------------+
| HOUR('10:05:03')                                        |
+---------------------------------------------------------+
| 10                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LAST_DAY(date)

Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

mysql> SELECT LAST_DAY('2003-02-05');
+---------------------------------------------------------+
| LAST_DAY('2003-02-05')                                  |
+---------------------------------------------------------+
| 2003-02-28                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LOCALTIME and LOCALTIME()

LOCALTIME and LOCALTIME() are synonyms for NOW().

LOCALTIMESTAMP and LOCALTIMESTAMP()

LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().

MAKEDATE(year,dayofyear)

Returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result is NULL.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
+---------------------------------------------------------+
| MAKEDATE(2001,31), MAKEDATE(2001,32)                    |
+---------------------------------------------------------+
| '2001-01-31', '2001-02-01'                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MAKETIME(hour,minute,second)

Returns a time value calculated from the hour, minute, and second arguments.

mysql> SELECT MAKETIME(12,15,30);
+---------------------------------------------------------+
| MAKETIME(12,15,30)                                      |
+---------------------------------------------------------+
| '12:15:30'                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MICROSECOND(expr)

Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.

mysql> SELECT MICROSECOND('12:00:00.123456');
+---------------------------------------------------------+
| MICROSECOND('12:00:00.123456')                          |
+---------------------------------------------------------+
| 123456                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MINUTE(time)

Returns the minute for time, in the range 0 to 59.

mysql> SELECT MINUTE('98-02-03 10:05:03');
+---------------------------------------------------------+
| MINUTE('98-02-03 10:05:03')                             |
+---------------------------------------------------------+
| 5                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MONTH(date)

Returns the month for date, in the range 0 to 12.

mysql> SELECT MONTH('1998-02-03')
+---------------------------------------------------------+
| MONTH('1998-02-03')                                     |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MONTHNAME(date)

Returns the full name of the month for date.

mysql> SELECT MONTHNAME('1998-02-05');
+---------------------------------------------------------+
| MONTHNAME('1998-02-05')                                 |
+---------------------------------------------------------+
| February                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

NOW()

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

mysql> SELECT NOW();
+---------------------------------------------------------+
| NOW()                                                   |
+---------------------------------------------------------+
| 1997-12-15 23:50:26                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

PERIOD_ADD(P,N)

Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value.

mysql> SELECT PERIOD_ADD(9801,2);
+---------------------------------------------------------+
| PERIOD_ADD(9801,2)                                      |
+---------------------------------------------------------+
| 199803                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

mysql> SELECT PERIOD_DIFF(9802,199703);
+---------------------------------------------------------+
| PERIOD_DIFF(9802,199703)                                |
+---------------------------------------------------------+
| 11                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

QUARTER(date)

Returns the quarter of the year for date, in the range 1 to 4.

mysql> SELECT QUARTER('98-04-01');
+---------------------------------------------------------+
| QUARTER('98-04-01')                                     |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SECOND(time)

Returns the second for time, in the range 0 to 59.

mysql> SELECT SECOND('10:05:03');
+---------------------------------------------------------+
| SECOND('10:05:03')                                      |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SEC_TO_TIME(seconds)

Returns the seconds argument, converted to hours, minutes, and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

mysql> SELECT SEC_TO_TIME(2378);
+---------------------------------------------------------+
| SEC_TO_TIME(2378)                                       |
+---------------------------------------------------------+
| 00:39:38                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

STR_TO_DATE(str,format)

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
+---------------------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y')                   |
+---------------------------------------------------------+
| 2004-04-31                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days)

When invoked with the INTERVAL form of the second argument, SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY)                 |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY)                  |
+---------------------------------------------------------+
| 1997-12-02                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SUBTIME(expr1,expr2)

SUBTIME() returns expr1 . expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time.

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999',
    -> '1 1:1:1.000002');
+---------------------------------------------------------+
| SUBTIME('1997-12-31 23:59:59.999999'...                 |
+---------------------------------------------------------+
| 1997-12-30 22:58:58.999997                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SYSDATE()

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

mysql> SELECT SYSDATE();
+---------------------------------------------------------+
| SYSDATE()                                               |
+---------------------------------------------------------+
| 2006-04-12 13:47:44                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIME(expr)

Extracts the time part of the time or datetime expression expr and returns it as a string.

mysql> SELECT TIME('2003-12-31 01:02:03');
+---------------------------------------------------------+
| TIME('2003-12-31 01:02:03')                             |
+---------------------------------------------------------+
| 01:02:03                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMEDIFF(expr1,expr2)

TIMEDIFF() returns expr1 . expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
    -> '1997-12-30 01:01:01.000002');
+---------------------------------------------------------+
| TIMEDIFF('1997-12-31 23:59:59.000001'.....              |
+---------------------------------------------------------+
|  46:58:57.999999                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

mysql> SELECT TIMESTAMP('2003-12-31');
+---------------------------------------------------------+
| TIMESTAMP('2003-12-31')                                 |
+---------------------------------------------------------+
| 2003-12-31 00:00:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMPADD(unit,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
The unit value may be specified using one of keywords as shown, or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+---------------------------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02')                     |
+---------------------------------------------------------+
| 2003-01-02 00:01:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The unit for the result is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+---------------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')          |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIME_FORMAT(time,format)

This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, and seconds.
If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+---------------------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l')              |
+---------------------------------------------------------+
| 100 100 04 04 4                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TIME_TO_SEC(time)

Returns the time argument, converted to seconds.

mysql> SELECT TIME_TO_SEC('22:23:00');
+---------------------------------------------------------+
| TIME_TO_SEC('22:23:00')                                 |
+---------------------------------------------------------+
| 80580                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TO_DAYS(date)

Given a date date, returns a day number (the number of days since year 0).

mysql> SELECT TO_DAYS(950501);
+---------------------------------------------------------+
| TO_DAYS(950501)                                         |
+---------------------------------------------------------+
| 728779                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.

mysql> SELECT UNIX_TIMESTAMP();
+---------------------------------------------------------+
| UNIX_TIMESTAMP()                                        |
+---------------------------------------------------------+
| 882226357                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00')                   |
+---------------------------------------------------------+
| 875996580                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UTC_DATE, UTC_DATE()

Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
+---------------------------------------------------------+
| UTC_DATE(), UTC_DATE() + 0                              |
+---------------------------------------------------------+
| 2003-08-14, 20030814                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UTC_TIME, UTC_TIME()

Returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
+---------------------------------------------------------+
| UTC_TIME(), UTC_TIME() + 0                              |
+---------------------------------------------------------+
| 18:07:53, 180753                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UTC_TIMESTAMP, UTC_TIMESTAMP()

Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------------------------------------------+
| UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0                    |
+---------------------------------------------------------+
| 2003-08-14 18:08:04, 20030814180804                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

WEEK(date[,mode])

This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used

Mode	First Day of week	Range	Week 1 is the first week .
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with more than 3 days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with more than 3 days this year
4	Sunday	0-53	with more than 3 days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with more than 3 days this year
7	Monday	1-53	with a Monday in this year

mysql> SELECT WEEK('1998-02-20');
+---------------------------------------------------------+
| WEEK('1998-02-20')                                      |
+---------------------------------------------------------+
| 7                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, . 6 = Sunday).

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
+---------------------------------------------------------+
| WEEKDAY('1998-02-03 22:23:00')                          |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

WEEKOFYEAR(date)

Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3).

mysql> SELECT WEEKOFYEAR('1998-02-20');
+---------------------------------------------------------+
| WEEKOFYEAR('1998-02-20')                                |
+---------------------------------------------------------+
| 8                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

YEAR(date)

Returns the year for date, in the range 1000 to 9999, or 0 for the .zero. date.

mysql> SELECT YEAR('98-02-03');
+---------------------------------------------------------+
| YEAR('98-02-03')                                        |
+---------------------------------------------------------+
| 1998                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

YEARWEEK(date), YEARWEEK(date,mode)

Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.

mysql> SELECT YEARWEEK('1987-01-01');
+---------------------------------------------------------+
| YEAR('98-02-03')YEARWEEK('1987-01-01')                  |
+---------------------------------------------------------+
| 198653                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Note that the week number is different from what the WEEK() function would return (0) for optional arguments 0 or 1, as WEEK() then returns the week in the context of the given year.
For more information check MySQL Official Website - Date and Time Functions

SQL - Wildcard Operators

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:58:00 +0000

We already have discussed SQL LIKE operator which is used to compare a value to similar values using wildcard operators.
SQL supports following two wildcard operators in conjunction with the LIKE operator:

Wildcards	Description
The percent sign (%)	Matches one or more characters. Note that MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.
The underscore (_)	Matches one character. Note that MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

Syntax:

The basic syntax of % and _ is as follows:

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

You can combine N number of conditions using AND or OR operators. Here XXXX could be any numberic or string value.

Example:

Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:

Statement	Description
WHERE SALARY LIKE '200%'	Finds any values that start with 200
WHERE SALARY LIKE '%200%'	Finds any values that have 200 in any position
WHERE SALARY LIKE '_00%'	Finds any values that have 00 in the second and third positions
WHERE SALARY LIKE '2_%_%'	Finds any values that start with 2 and are at least 3 characters in length
WHERE SALARY LIKE '%2'	Finds any values that end with 2
WHERE SALARY LIKE '_2%3'	Finds any values that have a 2 in the second position and end with a 3
WHERE SALARY LIKE '2___3'	Finds any values in a five-digit number that start with 2 and end with 3

Let us take a real example, consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example which would display all the records from CUSTOMERS table where SALARY starts with 200:

SQL> SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

This would produce following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

SQL - Transactions

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:58:00 +0000

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.
Practically you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions:

Transactions have the following four standard properties, usually referred to by the acronym ACID:

Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
Consistency: ensures that the database properly changes states upon a successfully committed transaction.
Isolation: enables transactions to operate independently of and transparent to each other.
Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control:

There are following commands used to control transactions:

COMMIT: to save the changes.
ROLLBACK: to rollback the changes.
SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
SET TRANSACTION: Places a name on a transaction.

Transactional control commands are only used with the DML commands INSERT, UPDATE, and DELETE only. They can not be used while creating tables or dropping them because these operations are automatically commited in the database.

The COMMIT Command:

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
The syntax for COMMIT command is as follows:

COMMIT;

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the example which would delete records from the table having age = 25, and then COMMIT the changes in the database.

SQL> DELETE FROM CUSTOMERS
     WHERE AGE = 25;
SQL> COMMIT;

As a result, two rows from the table would be deleted and SELECT statement would produce following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The ROLLBACK Command:

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
The syntax for ROLLBACK command is as follows:

ROLLBACK;

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the example which would delete records from the table having age = 25, and then ROLLBACK the changes in the database.

SQL> DELETE FROM CUSTOMERS
     WHERE AGE = 25;
SQL> ROLLBACK;

As a result, delete operation would not impact the table and SELECT statement would produce following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The SAVEPOINT Command:

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
The syntax for SAVEPOINT command is as follows:

SAVEPOINT SAVEPOINT_NAME;

This command serves only in the creation of a SAVEPOINT among transactional statements. The ROLLBACK command is used to undo a group of transactions.
The syntax for rolling back to a SAVEPOINT is as follows:

ROLLBACK TO SAVEPOINT_NAME;

Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state:

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now here is the series of operations:

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

Now that the three deletions have taken place, say you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone:

SQL> ROLLBACK TO SP2;
Rollback complete.

Notice that only the first deletion took place since you rolled back to SP2:

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

The RELEASE SAVEPOINT Command:

The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.
The syntax for RELEASE SAVEPOINT is as follows:

RELEASE SAVEPOINT SAVEPOINT_NAME;

Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the SAVEPOINT.

The SET TRANSACTION Command:

The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows.
For example, you can specify a transaction to be read only, or read write.
The syntax for SET TRANSACTION is as follows:

SET TRANSACTION [ READ WRITE | READ ONLY ];

SQL - Having Clause

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:49:00 +0000

The HAVING clause enables you to specify conditions that filter which group results appear in the final results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax:

The following is the position of the HAVING clause in a query:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the example which would display record for which similar age count would be more than or equal to 2:

SQL > SELECT *
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

This would produce following result:

+----+--------+-----+---------+---------+
| ID | NAME   | AGE | ADDRESS | SALARY  |
+----+--------+-----+---------+---------+
|  2 | Khilan |  25 | Delhi   | 1500.00 |
+----+--------+-----+---------+---------+

SQL - Using Views

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:48:00 +0000

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.
A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.
Views which are kind of virtual tables, allow users to do the following:

Structure data in a way that users or classes of users find natural or intuitive.
Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
Summarize data from various tables which can be used to generate reports.

Creating Views:

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.
To create a view, a user must have the appropriate system privilege according to the specific implementation.
The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL SELECT query.

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now, following is the example to create a view from CUSTOMERS table. This view would be used to have customer name and age from CUSTOMERS table:

SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS;

Now you can query CUSTOMERS_VIEW in similar way as you query an actual table. Following is the example:

SQL > SELECT * FROM CUSTOMERS_VIEW;

This would produce following result:

+----------+-----+
| name     | age |
+----------+-----+
| Ramesh   |  32 |
| Khilan   |  25 |
| kaushik  |  23 |
| Chaitali |  25 |
| Hardik   |  27 |
| Komal    |  22 |
| Muffy    |  24 |
+----------+-----+

The WITH CHECK OPTION:

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.
If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.
The following is an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION:

CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry of any NULL values in the view's AGE column, because the view is defined by data that does not have a NULL value in the AGE column.

Updating a View:

A view can be updated under certain conditions:

The SELECT clause may not contain the keyword DISTINCT.
The SELECT clause may not contain summary functions.
The SELECT clause may not contain set functions.
The SELECT clause may not contain set operators.
The SELECT clause may not contain an ORDER BY clause.
The FROM clause may not contain multiple tables.
The WHERE clause may not contain subqueries.
The query may not contain GROUP BY or HAVING.
Calculated columns may not be updated.
All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.

So if a view satisties all the above mentioned rules then you can update a view. Following is an example to update the age of Ramesh:

SQL > UPDATE CUSTOMERS_VIEW
      SET AGE = 35
      WHERE name='Ramesh';

This would ultimately update the base table CUSTOMERS and same would reflect in the view itself. Now try to query base table, and SELECT statement would produce following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Inserting Rows into a View:

Rows of data can be inserted into a view. The same rules that apply to the UPDATE command also apply to the INSERT command.
Here we can not insert rows in CUSTOMERS_VIEW because we have not included all the NOT NULL columns in this view, otherwise you can insert rows in a view in similar way as you insert them in a table.

Deleting Rows into a View:

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT commands apply to the DELETE command.
Following is an example to delete a record having AGE= 22.

SQL > DELETE FROM CUSTOMERS_VIEW
      WHERE age = 22;

This would ultimately delete a row from the base table CUSTOMERS and same would reflect in the view itself. Now try to query base table, and SELECT statement would produce following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Dropping Views:

Obviously, where you have a view, you need a way to drop the view if it is no longer needed. The syntax is very simple as given below:

DROP VIEW view_name;

Following is an example to drop CUSTOMERS_VIEW from CUSTOMERS table:

DROP VIEW CUSTOMERS_VIEW;

SQL - TRUNCATE TABLE Command

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:48:00 +0000

The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.
You can also use DROP TABLE command to delete complete table but it would remove complete table structure form the database and you would need to re-create this table once again if you wish you store some data.

Syntax:

The basic syntax of TRUNCATE TABLE is as follows:

TRUNCATE TABLE  table_name;

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the example to turncate:

SQL > TRUNCATE TABLE CUSTOMERS;

Now CUSTOMERS table is truncated and following would be output from SELECT statement:

SQL> SELECT * FROM CUSTOMERS;
Empty set (0.00 sec)

SQL - ALTER TABLE Command

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:47:00 +0000

The SQL ALTER TABLE command is used to add, delete, or modify columns in an existing table.
You would also use ALTER TABLE command to add and drop various constraints on a an existing table.

Syntax:

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:

ALTER TABLE table_name ADD column_name datatype;

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows:

ALTER TABLE table_name ALTER COLUMN column_name datatype;

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows:

ALTER TABLE table_name 
DROP CONSTRAINT MyUniqueConstraint;

If you're using MySQL, the code is as follows:

ALTER TABLE table_name 
DROP INDEX MyUniqueConstraint;

The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows:

ALTER TABLE table_name 
DROP CONSTRAINT MyPrimaryKey;

If you're using MySQL, the code is as follows:

ALTER TABLE table_name 
DROP PRIMARY KEY;

Example:

Consider CUSTOMERS table is having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the example to ADD a new column in an existing table:

ALTER TABLE CUSTOMERS ADD SEX char(1);

Now CUSTOMERS table is changed and following would be output from SELECT statement:

+----+---------+-----+-----------+----------+------+
| ID | NAME    | AGE | ADDRESS   | SALARY   | SEX  |
+----+---------+-----+-----------+----------+------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 | NULL |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 | NULL |
|  3 | kaushik |  23 | Kota      |  2000.00 | NULL |
|  4 | kaushik |  25 | Mumbai    |  6500.00 | NULL |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 | NULL |
|  6 | Komal   |  22 | MP        |  4500.00 | NULL |
|  7 | Muffy   |  24 | Indore    | 10000.00 | NULL |
+----+---------+-----+-----------+----------+------+

Following is the example to DROP sex column from existing table:

ALTER TABLE CUSTOMERS DROP SEX;

Now CUSTOMERS table is changed and following would be output from SELECT statement:

+----+---------+-----+-----------+----------+
| ID | NAME    | AGE | ADDRESS   | SALARY   |
+----+---------+-----+-----------+----------+
|  1 | Ramesh  |  32 | Ahmedabad |  2000.00 |
|  2 | Ramesh  |  25 | Delhi     |  1500.00 |
|  3 | kaushik |  23 | Kota      |  2000.00 |
|  4 | kaushik |  25 | Mumbai    |  6500.00 |
|  5 | Hardik  |  27 | Bhopal    |  8500.00 |
|  6 | Komal   |  22 | MP        |  4500.00 |
|  7 | Muffy   |  24 | Indore    | 10000.00 |
+----+---------+-----+-----------+----------+

SQL - Indexes

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:46:00 +0000

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically, and are then referred to one or more specific page numbers.
An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.
Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.
Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

The CREATE INDEX Command:

The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

Single-Column Indexes:

A single-column index is one that is created based on only one table column. The basic syntax is as follows:

CREATE INDEX index_name
ON table_name (column_name);

Unique Indexes:

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (column_name);

Composite Indexes:

A composite index is an index on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name
on table_name (column1, column2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.
Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.
The basic syntax is as follows:

DROP INDEX index_name;

You can check INDEX Constraint chapter to see actual examples on Indexes.

When should indexes be avoided?

Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:

Indexes should not be used on small tables.
Tables that have frequent, large batch update or insert operations.
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.

SQL - Alias Syntax

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:44:00 +0000

You can rename a table or a column temporarily by giving another name known as alias.
The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database.
The column aliases are used to rename a table's columns for the purpose of a particular SQL query.

Syntax:

The basic syntax of table alias is as follows:

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

The basic syntax of column alias is as follows:

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now following is the usage of table alias:

SQL> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT 
        FROM CUSTOMERS AS C, ORDERS AS O
        WHERE  C.ID = O.CUSTOMER_ID;

This would produce following result:

+----+---------+-----+--------+
| ID | NAME    | AGE | AMOUNT |
+----+---------+-----+--------+
|  3 | kaushik |  23 |   3000 |
|  3 | kaushik |  23 |   1500 |
|  2 | Ramesh  |  25 |   1560 |
|  4 | kaushik |  25 |   2060 |
+----+---------+-----+--------+

Following is the usage of column alias:

SQL> SELECT  ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
     FROM CUSTOMERS
     WHERE SALARY IS NOT NULL;

This would produce following result:

+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
|           1 | Ramesh        |
|           2 | Ramesh        |
|           3 | kaushik       |
|           4 | kaushik       |
|           5 | Hardik        |
|           6 | Komal         |
|           7 | Muffy         |
+-------------+---------------+

SQL - NULL Values

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:44:00 +0000

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.
A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

Syntax:

The basic syntax of NULL while creating a table:

SQL> CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Here NOT NULL signifies that column should always accept an explicit value of the given data type. There are two column where we did not use NOT NULL which means these column could be NULL.
A field with a NULL value is one that has been left blank during record creation.

Example:

The NULL value can cause problems when selecting data, however, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results.
You must use the IS NULL or IS NOT NULL operators in order to check for a NULL value.
Consider following table, CUSTOMERS having following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+

Now following is the usage of IS NOT NULL operator:

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
     FROM CUSTOMERS
     WHERE SALARY IS NOT NULL;

This would produce following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
+----+----------+-----+-----------+----------+

Now following is the usage of IS NULL operator:

SQL> SELECT  ID, NAME, AGE, ADDRESS, SALARY
     FROM CUSTOMERS
     WHERE SALARY IS NULL;

This would produce following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  6 | Komal    |  22 | MP        |          |
|  7 | Muffy    |  24 | Indore    |          |
+----+----------+-----+-----------+----------+

SQL - UNIONS CLAUSE

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:43:00 +0000

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.
To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order but they do not have to be the same length.

Syntax:

The basic syntax of UNION is as follows:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here given condition could be any given expression based on your requirement.

Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now let us join these two tables in our SELECT statement as follows:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
     SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce following result:

+------+---------+--------+---------------------+
| ID   | NAME    | AMOUNT | DATE                |
+------+---------+--------+---------------------+
|    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik |   1500 | 2009-10-08 00:00:00 |
|    2 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
|    4 | kaushik |   2060 | 2008-05-20 00:00:00 |
|    1 | Ramesh  |   NULL | NULL                |
|    5 | Hardik  |   NULL | NULL                |
|    6 | Komal   |   NULL | NULL                |
|    7 | Muffy   |   NULL | NULL                |
+------+---------+--------+---------------------+

The UNION ALL Clause:

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.
The same rules that apply to UNION apply to the UNION ALL operator.

Syntax:

The basic syntax of UNION ALL is as follows:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here given condition could be any given expression based on your requirement.

Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now let us join these two tables in our SELECT statement as follows:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
     SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce following result:

+------+---------+--------+---------------------+
| ID   | NAME    | AMOUNT | DATE                |
+------+---------+--------+---------------------+
|    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik |   1500 | 2009-10-08 00:00:00 |
|    2 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
|    4 | kaushik |   2060 | 2008-05-20 00:00:00 |
|    1 | Ramesh  |   NULL | NULL                |
|    2 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik |   1500 | 2009-10-08 00:00:00 |
|    4 | kaushik |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik  |   NULL | NULL                |
|    6 | Komal   |   NULL | NULL                |
|    7 | Muffy   |   NULL | NULL                |
+------+---------+--------+---------------------+

There are two other clauses (i.e operators ) which are very similar to UNION clause:

SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

SQL - EXCEPT Clause

noreply@blogger.com (naguakkina) — Thu, 22 Dec 2011 05:43:00 +0000

The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows which are not available in second SELECT statement.
Just as with the UNION operator, the same rules apply when using the EXCEPT operator.

Syntax:

The basic syntax of INTERSECT is as follows:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here given condition could be any given expression based on your requirement.

Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now let us join these two tables in our SELECT statement as follows:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
EXCEPT
     SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce following result:

+----+---------+--------+---------------------+
| ID | NAME    | AMOUNT | DATE                |
+----+---------+--------+---------------------+
|  1 | Ramesh  |   NULL | NULL                |
|  5 | Hardik  |   NULL | NULL                |
|  6 | Komal   |   NULL | NULL                |
|  7 | Muffy   |   NULL | NULL                |
+----+---------+--------+---------------------+