Oracle D2K Report for beginners

We will develop the following reports in this tutorial.

1.  Tabular report, manually.

2.  Tabular report group by – Customer Id.

3.  Master – Detail Report.

4.  Formula Column  (to calculate bonus of an employee which is 10% of salary)

5.  Place holder column (to display Report run date in “DDth MONTH  YYYY” format at top right corner )

6.  Parameter

7.  Lexical parameter
8.  Report to display “No Data found” when the report query doesn't fetch any record.

Tables used in this tutorial are -

S_ORD, 
S_ITEM, 
S_CUSTOMER, 
S_PRODUCT

s_ord:
CREATE TABLE S_ORD
(
  ID                           NUMBER(7) CONSTRAINT S_ORD_ID_NN NOT NULL,
  CUSTOMER_ID    NUMBER(7) CONSTRAINT S_ORD_CUSTOMER_ID_NN NOT NULL,
  DATE_ORDERED      DATE,
  DATE_SHIPPED        DATE,
  SALES_REP_ID         NUMBER(7),
  TOTAL                          NUMBER(11,2),
  PAYMENT_TYPE      VARCHAR2(6 BYTE),
  ORDER_FILLED       VARCHAR2(1 BYTE)
)

s_item:
CREATE TABLE APPS.S_ITEM
(
  ORD_ID                   NUMBER(7) CONSTRAINT S_ITEM_ORD_ID_NN NOT NULL,
  ITEM_ID                  NUMBER(7) CONSTRAINT S_ITEM_ITEM_ID_NN NOT NULL,
  PRODUCT_ID         NUMBER(7) CONSTRAINT S_ITEM_PRODUCT_ID_NN NOT NULL,
  PRICE                        NUMBER(11,2),
  QUANTITY               NUMBER(9),
  QUANTITY_SHIPPED    NUMBER(9)
)

s_customer:
CREATE TABLE APPS.S_CUSTOMER
( ID                          NUMBER(7) CONSTRAINT S_CUSTOMER_ID_NN NOT NULL,
  NAME        VARCHAR2(50 BYTE) CONSTRAINT S_CUSTOMER_NAME_NN NOT NULL,
  PHONE                 VARCHAR2(25 BYTE),
  ADDRESS             VARCHAR2(400 BYTE),
  CITY                       VARCHAR2(30 BYTE),
  STATE                    VARCHAR2(20 BYTE),
  COUNTRY             VARCHAR2(30 BYTE),
  ZIP_CODE             VARCHAR2(75 BYTE),
  CREDIT_RATING  VARCHAR2(9 BYTE),
  SALES_REP_ID     NUMBER(7),
  REGION_ID            NUMBER(7),
  COMMENTS           VARCHAR2(255 BYTE)
)

s_product:
CREATE TABLE S_PRODUCT
(  ID                                      NUMBER(7) CONSTRAINT S_PRODUCT_ID_NN NOT NULL,
   NAME       VARCHAR2(50 BYTE) CONSTRAINT S_PRODUCT_NAME_NN NOT  NULL,
   SHORT_DESC                        VARCHAR2(255 BYTE),
   LONGTEXT_ID                       NUMBER(7),
  IMAGE_ID                                NUMBER(7),
  SUGGESTED_WHLSL_PRICE  NUMBER(11,2),
  WHLSL_UNITS                        VARCHAR2(25 BYTE)
)


1.   Tabular Report 

Repeating frame Source: G_ID

Fields – Sources: F1 = ID, F2= CUSTOMER_ID, F3= DATE_ORDERED, F4= DATE_SHIPPED, F5= TOTAL

Output:

2.   Tabular report group by – Customer Id.

1^ST (outer) Repeating frame source=G_CUSTOMER_ID

2^nd (Inner) Repeating frame source =G_ID

Fields-Sources: F1=CUSTOMER_ID, F2=ID, F3=DATE_ORDERED, F4=DATE_SHIPPED, F5=TOTAL

Output:

3.    Master – Detail Report.

Q1 = select * from s_ord

Q2 = select * from s_item

output:

4.  Formula Column (to calculate bonus of an employee which is 10% of salary)

Q1= select * from emp

Bonus’ field i.e., F_4’s source is the formula columns CF_1

Output:

5.      Place holder column (to display Report run date in “DDth MONTH  YYYY” format at top right corner )

F_5’s source = CP_1

output:

6.  Parameterised report.

Note: 
In the property of parameter p_deptno, we can create LOV value (either static/SQL query) to be passed as parameter. 

If all records are to be shown if no value is passed as parameter we can handle this in query as -   select * from emp where deptno = nvl(:pdeptno, deptno)

7.   Lexical parameter:

Create a report that will ask the user while running to choose either ‘Greater than’ or ‘Less than’ and a customer_id. Query will add the condition according to user input to display the employee order information.

Create 2 more parameters P1, P2 manually.

Add the LOV values to P1 as below.

In the Before Report trigger –

Run with below parameters:

output:

Now run with below parameters:

Now the output is –

7.  To display “No Data found” when the report query doesn't fetch any record.

Source for summary column CS_1 is cnt.

In format trigger of *** No Data Found *** write the below code.

STEPS TO REGISTER THE REPORT IN ORACLE APPS:

1. Develop the rdf report using report builder tool.
(Ex: testrpt.pdf)

2. FTP the rdf from local machine to oracle apps server.
(Ex: $CUST_TOP/reports/US)

3. Define Executable in -
Application Developer Responsibility -> Concurrent -> Program->Executable

4. Define concurrent program in -
Application Developer Responsibility -> Concurrent -> Program
Note: attach proper value set while defining the parameter of concurrent program
and token name is same as the parameter name as defined in rdf.

5. Find the request group of the responsibility to which report is to be attached in -
System Administrator Responsibility ->Security-> Responsibility-> Define
Query with the responsibility name and copy the request group

6. Find the request group in -
Systen Administrator -> Security -> Responsibility -> Request
Attach the concurrent program to this request group.

7. Switch to responsibility and run report.