Skip to Content
0
Former Member
Oct 27, 2012 at 10:44 AM

Customer Ageing Analysis Report in SAP BI?

487 Views

Dear Experts,

Customer Aging Bucket are not matching with ECC & BI ?

ECC Report Layout is :-

Total Amount 0 - 30 31 - 60 61 - 90 91 - 180 181 - 365 366 - 732 733 - 1098 1099 - 1464 1465 - 1830 >1830

BI Layout is :-

Total Amount 0 - 30 xxxxxxxx xxxxxxxx 31 - 90 91 - 180 181 - 365 366 - 732 733 - 1098 1099- 1464 1465- 1830 > 1830

  • Ageing Calculation Buckets values are not matching .
  • Total Amount of both ECC & BI Report is matching .
  • Customer exit is written & Used in Bex query as Variable to populated the data in the Query output.
** Need to add those missing buckets (31-60 & 61-90 ) in BI Side by replacing 31-90 . Customer Exit Which is used in BI Query -:

*&---------------------------------------------------------------------*

*& Include ZXRSRU01

*&---------------------------------------------------------------------*

DATA: LOC_VAR_RANGE LIKE RRRANGEEXIT.

DATA: L_S_RANGE TYPE RSR_S_RANGESID,

L_S_VAR TYPE RRS0_S_VAR_RANGE,

DOC_KEY_DATE TYPE /BI0/OICALDAY,

L_KEY_DATE_HIGH TYPE /BI0/OICALDAY ,

L_KEY_DATE TYPE /BI0/OICALDAY,

L_KEY_DATES TYPE /BI0/OICALDAY,

L_KEY_DATE1 TYPE /BI0/OICALDAY,

L_KEY_DATE_LOW TYPE /BI0/OICALDAY,

DATE_30 TYPE /BI0/OICALDAY,

DATE_90 TYPE /BI0/OICALDAY,

DATE_90_LOW TYPE /BI0/OICALDAY,

DATE_180 TYPE /BI0/OICALDAY,

DATE_180_LOW TYPE /BI0/OICALDAY,

DATE_365 TYPE /BI0/OICALDAY,

DATE_365_LOW TYPE /BI0/OICALDAY,

DATE_732 TYPE /BI0/OICALDAY,

DATE_732_LOW TYPE /BI0/OICALDAY,

DATE_1098 TYPE /BI0/OICALDAY,

DATE_1098_LOW TYPE /BI0/OICALDAY,

DATE_1464 TYPE /BI0/OICALDAY,

DATE_1464_LOW TYPE /BI0/OICALDAY,

DATE_1830 TYPE /BI0/OICALDAY,

DATE_1830_LOW TYPE /BI0/OICALDAY,

DATE_1830_A TYPE /BI0/OICALDAY.

DATA: l_var_range LIKE LINE OF i_t_var_range.

DATA: gv_year TYPE t009b-bdatj,

gv_date TYPE sy-datum,

lv_date TYPE rschavl.

* DATE_1466 TYPE /BI0/OICALDAY,

* DATE_1466_LOW TYPE /BI0/OICALDAY,

* DATE_1467_A TYPE /BI0/OICALDAY,

* DATE_1467_LOW TYPE /BI0/OICALDAY.

*

CASE I_VNAM.

WHEN 'ZP_KEYDOCDT'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = '0P_KEYDT'.

L_KEY_DATE = LOC_VAR_RANGE-HIGH.

L_KEY_DATE1 = LOC_VAR_RANGE-LOW.

ENDLOOP.

L_S_RANGE-LOW = L_KEY_DATE.

L_S_RANGE-HIGH = L_KEY_DATE.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'EQ'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_0_30'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_30 = L_KEY_DATE_LOW - 29.

L_KEY_DATE_LOW = DATE_30.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_31_90'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_90 = L_KEY_DATE_LOW - 30.

L_KEY_DATE_HIGH = DATE_90.

DATE_90_LOW = DATE_90 - 59.

L_KEY_DATE_LOW = DATE_90_LOW.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_91_180'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_180 = L_KEY_DATE_LOW - 90.

L_KEY_DATE_HIGH = DATE_180.

DATE_180_LOW = DATE_180 - 89.

L_KEY_DATE_LOW = DATE_180_LOW.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_181_365'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_365 = L_KEY_DATE_LOW - 180.

L_KEY_DATE_HIGH = DATE_365.

DATE_365_LOW = DATE_365 - 184.

L_KEY_DATE_LOW = DATE_365_LOW.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_366_732'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_732 = L_KEY_DATE_LOW - 365.

L_KEY_DATE_HIGH = DATE_732.

DATE_732_LOW = DATE_732 - 366.

L_KEY_DATE_LOW = DATE_732_LOW.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_733_1098'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_1098 = L_KEY_DATE_LOW - 732.

L_KEY_DATE_HIGH = DATE_1098.

DATE_1098_LOW = DATE_1098 - 365.

L_KEY_DATE_LOW = DATE_1098_LOW.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

* BREAK-POINT.

WHEN 'ZTR_1099_1464'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_1464 = L_KEY_DATE_LOW - 1098.

L_KEY_DATE_HIGH = DATE_1464.

DATE_1464_LOW = DATE_1464 - 365.

L_KEY_DATE_LOW = DATE_1464_LOW.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_1465_1830'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_1830 = L_KEY_DATE_LOW - 1464.

L_KEY_DATE_HIGH = DATE_1830.

DATE_1830_LOW = DATE_1830 - 365.

L_KEY_DATE_LOW = DATE_1830_LOW.

L_S_RANGE-LOW = L_KEY_DATE_LOW.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_1830'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

DATE_1830_A = L_KEY_DATE_LOW - 1830.

L_KEY_DATE_HIGH = DATE_1830_A.

L_S_RANGE-LOW = '10000101'.

L_S_RANGE-HIGH = L_KEY_DATE_HIGH.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

WHEN 'ZTR_CLR_DATE'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO

LOC_VAR_RANGE WHERE VNAM = 'ZDATE'.

L_KEY_DATE_HIGH = LOC_VAR_RANGE-LOW.

L_KEY_DATE_LOW = LOC_VAR_RANGE-LOW.

ENDLOOP.

L_S_RANGE-LOW = LOC_VAR_RANGE-LOW.

L_S_RANGE-HIGH = '99991231'.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

*"ENDCASE.

*************Customer Exit Variable Code For Document Wise******************

**DATA: LOC_VAR_RANGE LIKE RRRANGEEXIT.

*

*DATA: "L_S_RANGE TYPE RSR_S_RANGESID,

*"L_S_VAR TYPE RRS0_S_VAR_RANGE,

*"DOC_KEY_DATE TYPE /BI0/OICALDAY.

*

*

*"CASE I_VNAM.

WHEN 'ZP_KEYDOCDT'.

IF I_STEP = 2.

LOOP AT I_T_VAR_RANGE INTO LOC_VAR_RANGE WHERE VNAM = '0P_KEYDT'.

DOC_KEY_DATE = LOC_VAR_RANGE-LOW.

ENDLOOP.

L_S_RANGE-LOW = DOC_KEY_DATE.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'EQ'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

*******************************************************************************************

* Calculate "Fiscal Year/Period range (from Start of Fiscal Year) till entered Month(YTD).

*******************************************************************************************

WHEN 'YVAR_FISCYEAR_YTD'.

IF I_STEP = 2. "after the popup

BREAK POINT.

LOOP AT I_T_VAR_RANGE INTO LOC_VAR_RANGE

WHERE VNAM = '0P_FPER'.

CLEAR L_S_RANGE.

L_S_RANGE-LOW+0(4) = LOC_VAR_RANGE-LOW(4).

L_S_RANGE-LOW+4(3) = '001'.

L_S_RANGE-HIGH = LOC_VAR_RANGE-LOW. "high value = input

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

EXIT.

ENDLOOP.

ENDIF.

*******************************************************************************************

* Calculate "Fiscal Year/Period range (from Start of Fiscal Year) till entered Month(YTD).

*******************************************************************************************

***************************************************************

* to calculate "Monthly Cumulative till date" user input is "Cal day"

*-----------------------------------------------------------

WHEN 'ZCUM_TILL_DATE'.

*break-point.

DATA a(8) TYPE c.

* DATA a TYPE sy-datum. "c.

IF i_step = 2.

CLEAR : a.

READ TABLE i_t_var_range INTO l_var_range

WITH KEY vnam = 'ZCALDAY_MAND'.

IF sy-subrc = 0 .

CONCATENATE l_var_range-low+0(4)

l_var_range-low+4(2) '01' INTO a."lv_date.

CLEAR : l_s_range.

l_s_range-low = a. "lv_date.

* l_s_range-high = lv_date + 9.

l_s_range-high = l_var_range-low.

l_s_range-sign = 'I'.

l_s_range-opt = 'BT'.

APPEND l_s_range TO e_t_range.

ENDIF.

ENDIF.

*-----------------------------------------------------------

* to calculate "Cum till year" user input is "Calday"

*-----------------------------------------------------------

WHEN 'ZCUM_TILL_YEAR'.

IF i_step = 2.

READ TABLE i_t_var_range INTO l_var_range

WITH KEY vnam = 'ZCALDAY_MAND'.

IF sy-subrc = 0 .

gv_date = l_var_range-low.

CALL FUNCTION 'DATE_TO_PERIOD_CONVERT'

EXPORTING

i_date = gv_date

* I_MONMIT = 00

i_periv = 'V3'

IMPORTING

* E_BUPER =

e_gjahr = gv_year

EXCEPTIONS

input_false = 1

t009_notfound = 2

t009b_notfound = 3

OTHERS = 4.

CONCATENATE gv_year '04' '01'

INTO lv_date.

* preyear = l_var_range-low+0(4) - 1.

CLEAR : l_s_range.

l_s_range-low = lv_date.

l_s_range-sign = 'I'.

l_s_range-opt = 'BT'.

l_s_range-high = l_var_range-low.

APPEND l_s_range TO e_t_range.

ENDIF.

ENDIF.

***************************************************************************************

* Restrict Fiscal year Period upto current date from User input Year

****************************************************************************************

WHEN 'YFISCPER_SY'.

DATA: LV_BDATJ TYPE BDATJ,

LV_BDATJ1 TYPE BDATJ,

LV_BDATJ2 TYPE BDATJ, "Posting date YYYY

LV_POPER TYPE POPER, "Posting period

LV_INDATE TYPE SYDATUM. "Current Date

CONSTANTS: C_V3 TYPE PERIV VALUE 'V3'. "Fiscal Year Variant

IF I_STEP = 2.

READ TABLE I_T_VAR_RANGE INTO LOC_VAR_RANGE WITH KEY VNAM = '0P_FYEAR'.

IF SY-SUBRC = 0.

* BREAK-POINT.

LV_INDATE = SY-DATUM.

* CONCATENATE LOC_VAR_RANGE-LOW+6(4) LOC_VAR_RANGE-LOW+3(2) LOC_VAR_RANGE-LOW+0(2) INTO LV_INDATE.

CALL FUNCTION 'DATE_TO_PERIOD_CONVERT'

EXPORTING

I_DATE = LV_INDATE

I_MONMIT = 00

I_PERIV = C_V3

IMPORTING

E_BUPER = LV_POPER

E_GJAHR = LV_BDATJ

EXCEPTIONS

INPUT_FALSE = 1

T009_NOTFOUND = 2

T009B_NOTFOUND = 3

OTHERS = 4.

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ELSE.

LV_BDATJ2 = LOC_VAR_RANGE.

* LV_BDATJ1 = LV_BDATJ2 - 1.

IF LV_BDATJ2 LT LV_BDATJ.

CONCATENATE LV_BDATJ2 '001' INTO L_S_RANGE-LOW.

CONCATENATE LV_BDATJ2 '012' INTO L_S_RANGE-HIGH.

ELSEIF LV_BDATJ2 EQ LV_BDATJ .

CONCATENATE LV_BDATJ2 '001' INTO L_S_RANGE-LOW.

CONCATENATE LV_BDATJ2 LV_POPER INTO L_S_RANGE-HIGH.

ENDIF.

* L_S_RANGE-LOW = LV_BDATJ.

L_S_RANGE-SIGN = 'I'.

L_S_RANGE-OPT = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

ENDIF.

ENDIF.

ENDIF.

******************************************************************************

ENDCASE.


Pls suggest !!!!!

Regards,

Asit kumar