Variable for fiscal year question

[Former Member]

Hello,

I have a challenging query requirement for variable entry. This report needs to be run for the last day of the fiscal year. The user may run it for any fiscal year. The request is for the user to simply enter the year at run time and for Bex to restrict the query for the last day of the year on a date field in the query results. In other words, the user enters 2006 somewhere in the variable prompt screen, from this Bex knows to populate 12/31/06 in the cancellation date field because it is the last day of the year for 2006.

The challenge is that the user is entering a value for fiscal year and wants the system to be filtered for the last day of the year for the cancellation date field.

I was thinking the best way to do this would be on the front end, but maybe someone even has a more appropriate back end solution. I am open to all suggestions.

Any ideas how to do this?

Regards,

TMS

[Former Member · Accepted solution]

Hi TMS

1. Create a user input variable for fiscal year

2. Make it input ready and processing type as customer exit

3. Write a code in CMOD to get last day of fiscal year

4 Use standard FM

'LAST_DAY_IN_PERIOD_GET'

pass value of fiscal year and period as constant ( your last period 012 or 016 whatever)

5. Restrict your KF with this customer exit variable

Thanks

Tripple k

[Former Member · Accepted solution]

I like this option but i need some more details. Will this strategy pass a value to the cancelation date in some way. I guess i dont understand steps 4 & 5 completely. Since the requirement is to filter the query by the last day of the year on the cancellation date, does this cmod allow you to calculate that day and then pass it to cancellation date? Or is a date field not necessary?

Thanks,

TMS

[Former Member · Accepted solution]

the suggested method wont work. As the value deteremined by the exit cannot be passed to fiscalyr/period or the cancellation date

Infact u need to create a customer exit variable on cancellation date. And the input in the logic (cmod) should be the user entered fiscalyear. that warrants a user input variable on fiscalyear. And do the logic to determine the last working day, and that value willbe /should be passed back to cancellation date.

[Former Member · Accepted solution]

Hello TMS

sorry didnt read your question properly

1. u r getting user input value for fiscal year Say var. "ZFISCYEAR"

2. create a customer exit ZCCANCELDT and use reference as ur cancel date IO

3. Restrict ur cancel date field from this customer exit.

4. Go to CMOD to caluclate the last day of period

5. Write the code..i dont know direct FM to get last day from fiscyear but we can use following FM

LAST_DAY_IN_PERIOD_GET

READ TABLE i_t_var_range INTO l_var_range WITH KEY vnam = 'ZFISCYEAR'.

c_period = '012' " It may be 016 is your fiscal year has 16 period " use last period

c_variant = 'your fiscal year variant'

CALL FUNCTION 'LAST_DAY_IN_PERIOD_GET'

EXPORTING

i_gjahr = v_fiscyear

• I_MONMIT = 00

i_periv = c_variant

i_poper = c_period

IMPORTING

e_date = v_end_day

EXCEPTIONS

input_false = 1

t009_notfound = 2

t009b_notfound = 3

OTHERS = 4.

s_range-sign = I.

s_range-opt = EQ.

s_range-low = v_end_day.

APPEND s_range TO e_t_range.

ENDIF.

This will give you last day of period and your cancel date IO will be restricted by this date

Hope i could make myself clear now

Thanks

Tripple k

[Former Member · Accepted solution]

Hello Tripple K,

I am trying your approach but am getting an error. I wonder if you can look at my code pasted below. The error says "the field l_var_range is unknown but there is a field with a similar name i_t_var_range." However if i change the table name as the error suggests I get another error "i_t_var_range cannot be converted to the line type of i_t_var_range". Any suggestions?

Also, I deleted the code related to fiscal year variant because we are hard coding fiscal year perid = 12; does that sound right?

WHEN 'ZMECANDT'.

READ TABLE i_t_var_range INTO l_var_range WITH KEY vnam = 'ZMSFY'.

c_period = '012'

CALL FUNCTION 'LAST_DAY_IN_PERIOD_GET'

EXPORTING

i_gjahr = v_fiscyear

• I_MONMIT = 00

i_periv = c_variant

i_poper = c_period

IMPORTING

e_date = v_end_day

EXCEPTIONS

input_false = 1

t009_notfound = 2

t009b_notfound = 3

OTHERS = 4.

s_range-sign = I.

s_range-opt = EQ.

s_range-low = v_end_day.

APPEND s_range TO e_t_range.

ENDIF.