Objective:
To
create PDF, Excel, RTF, HTML reports using report Layouts and report Queries in
Oracle APEX 4.2.
create PDF, Excel, RTF, HTML reports using report Layouts and report Queries in
Oracle APEX 4.2.
Solution:
Step 1: Go to Shared
Components
Components
Step 2: Use below
query in report query source.
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,
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 >>
End >>
Step 4: After
creating report queries, create RTF template using OBIP add-ins in word.
Template shown below.
creating report queries, create RTF template using OBIP add-ins in word.
Template shown below.
To,
Central
Depository Services (India) Ltd.
Depository Services (India) Ltd.
P
J Tower 16th Floor
J Tower 16th Floor
Dalal
Street, Fort
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:
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
|
Yours
Faithfully,
Faithfully,
For Sundaram BNP
Paribas Fund Services Ltd
Paribas Fund Services Ltd
Authorized
Signatory
Signatory
Step 5: Upload RTF
template
template
Step
6: Report Layout creation
Step 7: Create
parameters and button
Fig 5: APEX Screen
Step 8: Button
Creation
Creation
Points:
While
creating button, action has to be changed as “Download Printable Report Query”
Ø Then edit
button attributes, set action as
“Redirect URL”
button attributes, set action as
“Redirect URL”
Ø In URL
Target, Paste =>
Target, Paste =>
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=UNITHOLDER_DETAILS
Step 9: Edit report
query
query
Points:
Ø Edit report
query attributes then set report layout as “COB” (What we have created report
layout for this report query)
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
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
Recommended Posts
