Hierarchial query example in oracle or CONNECT BY PRIOR or LEVEL or Start with

http://welcometooracle.wordpress.com/2012/10/18/hierarchical/

How to set column width (size) in sqlplus

Aging Reports in Oracle Apps Receivables - Quick Overview (Aging Buckets in Oracle Receivables)

Overview

Aging reports are used to review information about your open items so as to know how much funds have been held up and with whom. Aging reports display the customers who have outstanding balances and the amount each customer owes us which also helps to expedite the collection process.  Receivables aging reports Do Not include customers with a zero outstanding balance.

These reports can print both detail and summary information about your customer’s current and past due invoices, debit memos, and charge backs.  Receivables also gives the option to see credit memos, on–account credits, unidentified payments, and on–account and unapplied cash amounts.

As mentioned above, Oracle Receivables ages the transactions according to due date. The aging reports however select transactions according to gl date. 

Receivables aging reports includes all open items whose gl_date is before or the same as the “as of date” entered, and whose gl_date_closed is after the “as of date”. The default value for “as of date” is the current date.

 Categories of Aging Reports 

Receivables aging reports are categorized as follows:

• Aging – By Account
• Aging – By Amount
• Aging – By Collector
• Aging – By Salesperson 

 Report Parameters for Aging Reports 

Aging Bucket Name:  

Specify the bucket set from which the report information needs to be printed. The default bucket set is 'Standard'.

As of Date:

Specify the date as of which the transactions need to be aged. Receivables includes all open items whose GL date is before or the same as this date. The default is the current date.

Order By:

The option you want Receivables to use to sort your information. For example, you can sort by:

·        Customer Name (Aging - 4 and 7 Bucket reports)

·         Transaction Type (Aging - 4 and 7 Bucket reports)

·        Balance Due (only for 7 Buckets - By Amount report)

·        Salesperson (only for 7 Buckets - By Salesperson report)

Report Format:

The “Brief” format prints customer name and customer number with item information while the “Detailed” format prints address and contact address as well.

Report Summary:

The “Invoice Summary” option prints information on all customers' debit items.

The “Customer Summary” option prints customers' names with their total debit item balances.

Show on Account:

Specify whether to print credit items for your customers.

·        Do Not Show: Receivables does not display any of your identified or unidentified payments, or on-account credit memos.

·        Age: Receivables ages your credit items and includes the credit amounts in the appropriate aging bucket columns.

·        Summarize: Receivables displays the sum of your credit items in the Customer Credit Memos, Customer Payments, and the Customer Balance rows. This is the default option.

Show Receipts at Risk:

Receipts at Risk are receipts that have either not been cleared or factored receipts that have not been risk eliminated. Select one of the following values for your report:

·        Age:- Include receipts at risk in this report. Receivables displays the receipts at risk with other open receipts in the appropriate bucket and includes them when determining the customer's balance.

·        Summarize:- Receivables displays the sum of your receipts at risk in the Customer Credit Memos, Customer Payments, and the Customer Balance rows.

Do Not Show :- Receipts at risk will not be included in this report. This value is used as the default.

MY COLLEAGUES

PL/SQL EXCEPTIONS

PL/SQL EXCEPTIONS : WHEN NO_DATA_FOUND

EXAMPLES :

  1  DECLARE

  2    V_NUMBER NUMBER(4);

  3    V_NAME VARCHAR2(20);

  4    V_SAL   NUMBER(5);

  5  BEGIN

  6    SELECT EMPNO,ENAME,SAL INTO V_NUMBER,V_NAME,V_SAL FROM EMP WHERE EMPNO = 7839;

  7  DBMS_OUTPUT.PUT_LINE(V_NUMBER ||  V_NAME ||   V_SAL);

  8  EXCEPTION

  9  WHEN NO_DATA_FOUND

 10  THEN

 11  DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE EXISTS IN THIS NAME');

 12* END;

SQL> /

7839 KING 5000

PL/SQL procedure successfully completed.

(GIVE WRONG EMPLOYEE NUMBER THEN YOU WILL GET THIS ERROR);

  1  DECLARE

  2    V_NUMBER NUMBER(4);

  3    V_NAME VARCHAR2(20);

  4    V_SAL   NUMBER(5);

  5  BEGIN

  6    SELECT EMPNO,ENAME,SAL INTO V_NUMBER,V_NAME,V_SAL FROM EMP WHERE EMPNO = 333;

  7  DBMS_OUTPUT.PUT_LINE(V_NUMBER ||  V_NAME ||   V_SAL);

  8  EXCEPTION

  9  WHEN NO_DATA_FOUND

 10  THEN

 11  DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE EXISTS IN THIS NAME');

 12* END;

SQL> /

NO EMPLOYEE EXISTS IN THIS NAME

PL/SQL procedure successfully completed.

TOO_MANY_ROWS EXCEPTIONS

EXAMPLE:

DECLARE

   V_NUMBER NUMBER(4);

BEGIN

   SELECT EMPNO INTO V_NUMBER FROM EMP E;

DBMS_OUTPUT.PUT_LINE(V_NUMBER);

EXCEPTION

WHEN TOO_MANY_ROWS

THEN

DBMS_OUTPUT.PUT_LINE('THERE ARE TOO MANY ROWS   RETRIVING');

END;

/

To show the top 4 employees salary information from emp table

To find out highest salary of Three Persons in Organization(N th) for Oracle SQL Query :
or We want fin out Top 2 highest salary in company :
Example Query : To show the top 4 employees salary information from emp table

SELECT ENAME, EMPNO,SAL FROM  (SELECT ENAME,EMPNO,SAL FROM EMP  ORDER BY SAL DESC) WHERE ROWNUM <5;