Report Layouts and Report Queries in Oracle APEX 4.2

Objective:

To create PDF, Excel, RTF, HTML reports using report Layouts and report Queries in Oracle APEX 4.2.

Solution:

Step 1: Go to Shared Components

Step 2: Use below query in report query source.

<<SQL Query >>

<< Begins >>

SELECT

  ROWNUM AS SNO,

  ISIN,

  NOOFRECORDS,

  FUNDID,

  QUANTITY,

  SD

FROM

  (

    SELECT

      FD.FUNDIDENTIFICATIONNUMBER  AS ISIN,

      COUNT(ATD.TRANSACTIONNUMBER) AS NOOFRECORDS,

      ATD.FUNDID,

      SUM(ATD.UNITSCONFIRMED)      AS QUANTITY,

      TO_CHAR(SYSDATE,'DD-MON-YY') AS SD

    FROM

      SMF_APEXRPS.UNITHOLDERADDINFOTBL UAI,

      SMF_APEXRPS.CONSOLIDATEDTXNTBL ATD,

      SMF_APEXRPS.FUNDDEMOGRAPHICSTBL FD,

      SMF_APEXRPS.GROUPDEFINITIONTBL GD

    WHERE

      UAI.UNITHOLDERID   = ATD.UNITHOLDERID

    AND ATD.FUNDID       = GD.FUNDID

    AND ATD.FUNDID       = FD.FUNDID

    AND FD.LATESTRULE    = 1

    AND UAI.OTHERINFO29 IS NOT NULL

    AND UAI.OTHERINFO30 IS NOT NULL

    AND UAI.OTHERINFO30 NOT LIKE 'I%'

    AND ATD.DATEALLOTED =:P128_DATE_ALLOTTED

    AND GD.GROUPID      =:P128_GROUPID

    GROUP BY

      FD.FUNDIDENTIFICATIONNUMBER,

      ATD.FUNDID

  )

<< End >>

Step 4: After creating report queries, create RTF template using OBIP add-ins in word. Template shown below.

Date: SD

To,

Central Depository Services (India) Ltd.

P J Tower 16^th Floor

Dalal Street, Fort

 Mumbai 400 001

We hereby certify that names of the allot tees of the Mutual Fund units of Sundaram Mutual Fund issued consequent to New Fund Offer have been verified with the names provided by CDSL, based on the list of the DP Id and Client Ids of the allot tees provided by us. We hereby certify that the credits are being affected only to those accounts which are in “active” status and where the names and order of names as per the application matched with that of the names and order of the names as provided by CDSL. The details of the Corporate Action are given under:

S NO	ISIN	NO OF RECORDS	QUANTITY
F SNO	ISIN	NOOFRECORDS	E

Yours Faithfully,

For Sundaram BNP Paribas Fund Services Ltd

Authorized Signatory

Step 5: Upload RTF template

 Step 6: Report Layout creation

 Step 7: Create parameters and button

Fig 5: APEX Screen

Step 8: Button Creation

Points:

While creating button, action has to be changed as “Download Printable Report Query”

Ø  Then edit button attributes,  set action as “Redirect URL”

Ø  In URL Target, Paste => 

f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=UNITHOLDER_DETAILS

Step 9: Edit report query

Points:

Ø  Edit report query attributes then set report layout as “COB” (What we have created report layout for this report query)

Output:

Conclusion: We can create RTF, PDF, Excel, HTML reports and custom templates using report queries and report layout in Oracle APEX 4.2

By

Karkuvelraja T