Introduction:
To create PDF, Excel, RTF, HTML reports using report Layouts and report Queries in Oracle APEX 4.2.
Procedures:
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.
To,
xxxx Ltd.
Xxxx
Xxx
Xxx
We hereby certify that names of the allot tees of the Mutual Fund units of xxx 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 xxx. The details of the Corporate Action are given under:
| S NO | ISIN | NO OF RECORDS | QUANTITY |
| F SNO | ISIN | NOOFRECORDS | E |
Yours Faithfully,
For xxx 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:
Summary
We can create RTF, PDF, Excel, HTML reports and custom templates using report queries and report layout in Oracle APEX 4.2
