Create a custom function to use in data models

Question

Summary

Content

I am converting a legacy Discoverer report to a BI Publisher report and I need to know what the best way is to convert the functions used.  Below is the function used in the legacy report.

CREATE OR REPLACE FUNCTION GRP_USER."CNL_PD_PARAM" (

p_period        apps.fa_deprn_periods.calendar_period_close_date%TYPE)

RETURN NUMBER

IS

v_counter NUMBER;

BEGIN

select period_counter

into v_counter

from fa_deprn_periods

where calendar_period_close_date=p_period

and book_type_code='CORPORATE';

return v_counter;

end cnl_pd_param;

Any guidance is greatly appreciated.

Thanks!

Jenn

Jennifer Sherbrook - CNL · Answer

Here is the main query.  You can see in my where clause I use two functions.  Code for the functions is below.

SELECT GSV_RSVLDGR_V.ASSET_NUMBER

, GSV_RSVLDGR_V.DESCRIPTION

, GSV_RSVLDGR_V.DATE_PLACED_IN_SERVICE

, GSV_RSVLDGR_V.MAJOR_CATEGORY

, GSV_RSVLDGR_V.COMPANY

, GSV_RSVLDGR_V.DEPARTMENT

, SUM(GSV_RSVLDGR_V.DEPRN_RESERVE)

, DECODE(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),LEAST(GREATEST(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),( TRUNC(TO_DATE(GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE,'DD-MON-RRRR'),'MONTH') )),GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE),GSV_RSVLDGR_V.DEPRN_AMOUNT,0)

, DECODE(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),LEAST(GREATEST(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),( TRUNC(TO_DATE(GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE,'DD-MON-RRRR'),'YEAR') )),( ADD_MONTHS(LAST_DAY(( TRUNC(TO_DATE(GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE,'DD-MON-RRRR'),'YEAR') )),11) )),GSV_RSVLDGR_V.YTD_DEPRN,0)

, ( GSV_RSVLDGR_V.UNITS_ASSIGNED/GSV_RSVLDGR_V.UNITS )*GSV_RSVLDGR_V.MST_COST

FROM GRP_USER.GSV_RSVLDGR_V GSV_RSVLDGR_V

WHERE ( GSV_RSVLDGR_V.ASSET_TYPE = 'CAPITALIZED' )

AND ( GSV_RSVLDGR_V.BOOK_TYPE_CODE = :BOOK )

AND ( GSV_RSVLDGR_V.PERIOD_CLOSED+1 BETWEEN GSV_RSVLDGR_V.DATE_EFFECTIVE AND GSV_RSVLDGR_V.DATE_INEFFECTIVE

AND GSV_RSVLDGR_V.PERIOD_CLOSED+1 BETWEEN GSV_RSVLDGR_V.HIST_EFF_DATE AND GSV_RSVLDGR_V.HIST_INEFF_DATE

AND GSV_RSVLDGR_V.PERIOD_CLOSED+1 BETWEEN GSV_RSVLDGR_V.CAT_EFF_DATE AND GSV_RSVLDGR_V.CAT_INEFF_DATE )

AND ( GSV_RSVLDGR_V.PERIOD_COUNTER = LEAST(( GRP_USER.CNL_PD_PARAM(TO_DATE(:PERIOD,'DD-MON-YY')) ),GRP_USER.CNL_MAX_PERIOD_COUNTER(GSV_RSVLDGR_V.ASSET_ID,TO_DATE(:PERIOD,'DD-MON-YYYY'),GSV_RSVLDGR_V.BOOK_TYPE_CODE)) )

AND ( GSV_RSVLDGR_V.DEPRN_FLAG = 'YES' )

AND GSV_RSVLDGR_V.ASSET_NUMBER = '332612'--js added for testing

GROUP BY DECODE(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),LEAST(GREATEST(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),( TRUNC(TO_DATE(GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE,'DD-MON-RRRR'),'MONTH') ))

,GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE),GSV_RSVLDGR_V.DEPRN_AMOUNT,0)

, DECODE(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),LEAST(GREATEST(( TO_DATE(:PERIOD,'DD-MON-YYYY') ),( TRUNC(TO_DATE(GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE,'DD-MON-RRRR'),'YEAR') )),( ADD_MONTHS(LAST_DAY(( TRUNC(TO_DATE(GSV_RSVLDGR_V.CALENDAR_PERIOD_CLOSE_DATE,'DD-MON-RRRR'),'YEAR') )),11) )),GSV_RSVLDGR_V.YTD_DEPRN,0)

, ( GSV_RSVLDGR_V.UNITS_ASSIGNED/GSV_RSVLDGR_V.UNITS )*GSV_RSVLDGR_V.MST_COST

, GSV_RSVLDGR_V.ASSET_NUMBER

, GSV_RSVLDGR_V.DESCRIPTION

, GSV_RSVLDGR_V.DATE_PLACED_IN_SERVICE

, GSV_RSVLDGR_V.MAJOR_CATEGORY

, GSV_RSVLDGR_V.COMPANY

, GSV_RSVLDGR_V.DEPARTMENT

ORDER BY GSV_RSVLDGR_V.COMPANY ASC

, GSV_RSVLDGR_V.MAJOR_CATEGORY ASC

, GSV_RSVLDGR_V.DEPARTMENT ASC

, GSV_RSVLDGR_V.ASSET_NUMBER ASC

;

Function1

CREATE OR REPLACE FUNCTION GRP_USER."CNL_PD_PARAM" (

p_period        apps.fa_deprn_periods.calendar_period_close_date%TYPE)

RETURN NUMBER

IS

v_counter NUMBER;

BEGIN

select period_counter

into v_counter

from fa_deprn_periods

where calendar_period_close_date=p_period

and book_type_code='CORPORATE';

return v_counter;

end cnl_pd_param;

/

Function2

CREATE OR REPLACE FUNCTION GRP_USER."CNL_MAX_PERIOD_COUNTER" (

p_asset_id         apps.fa_deprn_summary.asset_id%TYPE,

p_asof_date      date,

p_book             apps.fa_deprn_summary.book_type_code%TYPE)

RETURN NUMBER

IS

v_period_counter NUMBER;

BEGIN

select max(period_counter)

into v_period_counter

from apps.fa_deprn_summary

where asset_id=p_asset_id

and book_type_code=p_book

and period_counter <=(select period_counter from fa_deprn_periods

where p_asof_date between calendar_period_open_date and calendar_period_close_date

and book_type_code=p_book);

return v_period_counter;

end cnl_max_period_counter;

/

Venkat Thota - BIP · Answer

post your main query along with other dependency queries,Thanks

User910243567 · Answer

I am not completely aware of the business logic of this function, I guess you can create as Sub group.