Wednesday, 21 December 2011

Interview Questions on Oracle Payables or Oracle AP

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

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.



General Questions :

1. Tell me something about yourself?
2. What is your functional experience? Brief about last two companies..
3. Why did you opt for this field ERP?
Why not software development?
Do you have interest in software development?
Have you ever worked on databases like dbase, foxpro or MSAccess?
4. Which implementations you have worked so far ?
Where ?
How much of time ?
What was your role in implementation ?
5. Have you ever done set up for any Module or modules? Which modules ?
6. What is the difference between key flex field and Descriptive flexfield
7. Have you ever defined descriptive flexfield ? Where and how ?
8. How many segments are available for defining descriptivce flexfileds ? What if you have more ?
9. Have you ever defined document sequences ? Where and how ?
10. How good are you in MS Office Tool?
MS Word Drafting…..
MS Excel macros….
11. Have you ever used VB Macros for uploading Values into Apps ? Where and how ?
12. Do you know SQL? On a scale of 1 to 10 how do you rate yourself in SQL.
13. Do you have any software development experience?
Live Project or Dummy ? Which one?
Explain the project in terms of tables, forms and reports, team members and your role? How did you structure your tables?
What is normalization ? What are the types of relationships between tables and what role they play in structuring the tables ?
14. Total functional Experience
15. Total IT experience
16. In which modules you are strong skills ?
(Select for Module Questionnaires ………)
(If technical knowledge claimed then relevant technical questionnaires…….)

Oracle Account Receivables (AR) - Functional Overview

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

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

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

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

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

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.
  1. Use SHOW CREATE TABLE command to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.
  2. 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.
  3. 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

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