Transaction Code: F.30 (DSO analysis) question

[Former Member]

Hi Experts,

I have a requirement where in I need to create a custom program similar to the functionality of transaction F.30. I need to create a report similar to DSO analysis but the grouping is by another field name (BSEG-SEGMENT).

My question is Where will i get the BALANCE and SALES amount from the DSO analysis. Can somebody tell me which table and field it came from?

Thanks a lot! Points will be rewarded ASAP

Jay

[Former Member]

Hi Experts,

I have a requirement where in I need to create a custom program similar to the functionality of transaction F.30. I need to create a report similar to DSO analysis but the grouping is by another field name (BSEG-SEGMENT).

My question is Where will i get the BALANCE and SALES amount from the DSO analysis. Can somebody tell me which table and field it came from?

Thanks a lot! Points will be rewarded ASAP

Jay

[Former Member]

Hi

Sales Amounts you have to take from<b> VBRK and VBRP</b> tables

and the Balances are taken from <b>BSID and BS</b>AD tables

see the sample DSO report

report zf_rept_dso

no standard page heading

message-id fb

line-count 65

line-size 160.

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

• Purpose : This ABAP program will fetch the Total Sales for the Last

• three months from the date entered on selection screen and

• Calculates the Total open items and Days Sales Outstanding

• (DSO) and Days Overdue Outstanding(DOO).

*----

---

tables: t001, " Company Codes

tvko, " Sales Organizations

t005, " Country Codes

t151, " Customer Groups

t171, " Sales Districts

kna1. " Customer Master

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

• Internal Tables

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

• Internal table for VBRK Sales data

data: begin of it_vbrk occurs 0,

vbeln like vbrk-vbeln, " Billing Doc

fkdat like vbrk-fkdat, " Billing Date

kunnr like vbrk-kunag, " Customer Number

fkart like vbrk-fkart, " Billing Type

vkorg like vbrk-vkorg, " Sales Organization

land1 like vbrk-land1, " Country

bukrs like vbrk-bukrs, " Company Code

netwr like vbrk-netwr, " Net Value

waerk like vbrk-waerk, " Currency

kdgrp like vbrk-kdgrp, " Customer Group

bzirk like vbrk-bzirk, " Sales Employee

end of it_vbrk.

• Internal table for BSID (Open Items) data

data: begin of it_bsid occurs 0,

kunnr like bsid-kunnr, " Customer Number

gjahr like bsid-gjahr, " Fiscal Year

belnr like bsid-belnr, " Acc.Document

buzei like bsid-buzei, " Item

budat like bsid-budat, " Posting Date

blart like bsid-blart, " Doc Type

shkzg like bsid-shkzg, " Dr/Cr Indicator

dmbtr like bsid-dmbtr, " Amount(Local Curr)

dmbe2 like bsid-dmbe2, " Amount(Doc Curr)

waers like bsid-waers, " Currency

zterm like bsid-zterm, " Payment Terms

vbeln like bsid-vbeln, " Billing Doc

zfbdt like bsid-zfbdt, " Baseline Date

zbd1t like bsid-zbd1t, " No of days

end of it_bsid.

• Internal table for BSAD (Closed Items) data

data: begin of it_bsad occurs 0,

kunnr like bsad-kunnr, " Customer Number

gjahr like bsad-gjahr, " Fiscal Year

belnr like bsad-belnr, " Acc.Document

buzei like bsad-buzei, " Item

budat like bsad-budat, " Posting Date

blart like bsid-blart, " Doc Type

shkzg like bsad-shkzg, " Dr/Cr Indicator

dmbtr like bsad-dmbtr, " Amount(Local Curr)

dmbe2 like bsad-dmbe2, " Amount(Doc Curr)

waers like bsad-waers, " Currency

zterm like bsid-zterm, " Payment Terms

vbeln like bsid-vbeln, " Billing Doc

zfbdt like bsid-zfbdt, " Baseline Date

zbd1t like bsid-zbd1t, " No of days

end of it_bsad.

• Internal table for Customer data

data: begin of it_kna1 occurs 0,

kunnr like kna1-kunnr, " Customer Number

name1 like kna1-name1, " Customer Name

land1 like kna1-land1, " Country

end of it_kna1.

• Internal table for Total AR Balances & Overdues

data: begin of it_arbal occurs 0,

kunnr like bsid-kunnr, " Customer Number

vbeln like bsid-vbeln, " Billing Doc

belnr like bsid-belnr, " Acc.Document

name1 like kna1-name1, " Customer Name

land1 like kna1-land1, " Country

shkzg like bsid-shkzg, " Dr/Cr Indicator

budat like bsid-budat, " Posting Date

blart like bsid-blart, " Doc Type

dmbtr like bsid-dmbtr, " Amount(Local Curr)

dmbe2 like bsid-dmbe2, " Amount(Doc Curr)

waers like bsid-waers, " Currency

due_amnt like bsid-dmbtr, " Amount(Over Due)

due_date like bsid-budat, " Due date

zterm like bsid-zterm, " Payment Terms

vkorg like vbrk-vkorg, " Sales Organization

kdgrp like vbrk-kdgrp, " Customer Group

bzirk like vbrk-bzirk, " Sales Employee

end of it_arbal.

• Internal table for BSAD (Closed Items) data for 3 months Sales

data: begin of it_bsad1 occurs 0,

kunnr like bsad-kunnr, " Customer Number

gjahr like bsad-gjahr, " Fiscal Year

belnr like bsad-belnr, " Acc.Document

buzei like bsad-buzei, " Item

budat like bsad-budat, " Posting Date

blart like bsid-blart, " Doc Type

shkzg like bsad-shkzg, " Dr/Cr Indicator

dmbtr like bsad-dmbtr, " Amount(Local Curr)

dmbe2 like bsad-dmbe2, " Amount(Doc Curr)

waers like bsad-waers, " Currency

zterm like bsid-zterm, " Payment Terms

vbeln like bsid-vbeln, " Billing Doc

zfbdt like bsid-zfbdt, " Baseline Date

zbd1t like bsid-zbd1t, " No of days

end of it_bsad1.

• Internal table for Total 3 months Sales (BSID & BSAD)

data: begin of it_arsale occurs 0,

kunnr like bsid-kunnr, " Customer Number

vbeln like bsid-vbeln, " Billing Doc

belnr like bsid-belnr, " Acc.Document

name1 like kna1-name1, " Customer Name

land1 like kna1-land1, " Country

shkzg like bsid-shkzg, " Dr/Cr Indicator

budat like bsid-budat, " Posting Date

blart like bsid-blart, " Doc Type

dmbtr like bsid-dmbtr, " Amount(Local Curr)

dmbe2 like bsid-dmbe2, " Amount(Doc Curr)

waers like bsid-waers, " Currency

zterm like bsid-zterm, " Payment Terms

vkorg like vbrk-vkorg, " Sales Organization

kdgrp like vbrk-kdgrp, " Customer Group

bzirk like vbrk-bzirk, " Sales Employee

amnt1 like bsid-dmbtr, " Amount(Curr Month)

amnt2 like bsid-dmbtr, " Amount(Last Month)

amnt3 like bsid-dmbtr, " Amount(Prev Month)

end of it_arsale.

• Internal table for Total 3 months sales data

data: begin of it_sales occurs 0,

kunnr like bsid-kunnr, " Customer Number

dmbtr like bsad-dmbtr, " Amount(Local Curr)

dmbe2 like bsad-dmbe2, " Amount(Doc Curr)

amnt1 like bsid-dmbtr, " Amount(Curr Month)

amnt2 like bsid-dmbtr, " Amount(Last Month)

amnt3 like bsid-dmbtr, " Amount(Prev Month)

end of it_sales.

• Internal table for final output

data: begin of it_report occurs 0,

kunnr like bsid-kunnr, " Customer Number

name1 like kna1-name1, " Customer Name

land1 like kna1-land1, " Country

dmbtr like bsid-dmbtr, " Amount(Local Curr)

dmbe2 like bsid-dmbe2, " Amount(Doc Curr)

due_amt like bsid-dmbtr, " Amount(Over Due)

end of it_report.

*----

---

• Declaration of Data and Variables

*----

---

data: v_days type i, " Difference days

v_waers like t001-waers, " Co.Code Currency

v_date1 like sy-datum, " Date before 90 days

v_num1 type p decimals 2, " Calculation field for DSO

v_num2 type p decimals 2, " Calculation field for DOO

v_dso type i, " DSO Value

v_d_days type i, " DSO,DDO Days

v_doo type i, " DOO Value

v_avg_dso type i, " Average DSO

v_avg_doo type i, " Average DOO

v_sum1 like bsid-dmbtr, " Totals 1 (Basic List)

v_sum2 like bsid-dmbtr, " Totals 2

v_sum3 like bsid-dmbtr, " Totals 3

v_sum4 like bsid-dmbtr, " Totals 4

v_sum5 like bsid-dmbtr, " Totals 5

v_sum6 like bsid-dmbtr, " Totals 6

v_sum7 like bsid-dmbtr, " Totals 1 (Interactive list)

v_sum8 like bsid-dmbtr, " Totals 2

v_sum9 like bsid-dmbtr, " Totals 3

v_sum10 like bsid-dmbtr, " Totals 4

v_sum11 like bsid-dmbtr, " Totals 5

v_sum12 like bsid-dmbtr, " Totals 6

v_sum13 like bsid-dmbtr, " Totals 7

v_sum14 like bsid-dmbtr, " Totals 8

v_mon1(2) type c, " Current Month

v_mon2(2) type c, " Last Month

v_mon3(2) type c. " Previous Month

• Constants

constants: c_rfbsk type c value 'C',

c_shkzg type c value 'H',

c_blart(2) type c value 'RV'.

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

• Selection Screen

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

selection-screen : begin of block b1 with frame title text-001.

parameters: p_bukrs like t001-bukrs obligatory. " Company Code

select-options: s_vkorg for tvko-vkorg, " Sales Orgn

s_land1 for t005-land1, " Country

s_kunnr for kna1-kunnr, " Customer Number

s_kdgrp for t151-kdgrp, " Customer Group

s_bzirk for t171-bzirk. " Sales Employee

parameters: p_date like sy-datum obligatory. " Report Date

selection-screen: end of block b1.

*********Initialization**********************************************

initialization.

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

• At Selection Screen

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

at selection-screen.

• Checking for the input values of selection screen.

perform screen_check.

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

• Top-Of-Page

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

top-of-page.

• Write the Column Headings for detailed report

perform col_heading.

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

• Top-Of-Page during Line-Selection

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

top-of-page during line-selection.

• Write the Column Headings for Interactive detailed report

perform line_sel_col_heading.

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

• Start-Of-Selection

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

start-of-selection.

• Calculate dates for the last three months

perform get_dates.

• Select the Sales data for the Last 90 days from VBRK

perform get_sales_data.

• Get the Customer Open Items from BSID

perform get_open_data.

• Get the Customer Closed Items from BSAD

perform get_close_data.

• Get the data from BSID,BSAD and KNA1 into one int.table

perform process_data.

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

• End-Of-Selection

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

end-of-selection.

if it_report[] is initial.

write : 'No Documents found'(002).

else.

• Write the Detailed Report

perform write_report.

endif.

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

• At Line-Selection

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

at line-selection.

• When double clicked on KUNNR display the details of AR and Sales

perform line_sel.

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

&----

---

*& Form SCREEN_CHECK

&----

---

• Checking for the input values of selection screen

----

---

form screen_check.

• Validation of Company Code

clear t001.

if not p_bukrs is initial.

select bukrs from t001 up to 1 rows

into t001-bukrs

where bukrs = p_bukrs.

endselect.

if sy-subrc ne 0.

message e000 with 'Invalid Company Code'(003).

endif.

endif.

• Validation of Customer Number

clear kna1.

if not s_kunnr is initial.

select kunnr from kna1 up to 1 rows

into kna1-kunnr

where kunnr in s_kunnr.

endselect.

if sy-subrc ne 0.

message e000 with 'Invalid Customer Number'(004).

endif.

endif.

• Validation of Sales Organization

clear tvko.

if not s_vkorg is initial.

select vkorg from tvko up to 1 rows

into tvko-vkorg

where vkorg in s_vkorg.

endselect.

if sy-subrc ne 0.

message e000 with 'Invalid Sales Organization'(016).

endif.

endif.

• Validation of Country(Region)

clear t005.

if not s_land1 is initial.

select land1 from t005 up to 1 rows

into t005-land1

where land1 in s_land1.

endselect.

if sy-subrc ne 0.

message e000 with 'Invalid Country'(017).

endif.

endif.

• Validation of Customer Group

clear t151.

if not s_kdgrp is initial.

select kdgrp from t151 up to 1 rows

into t151-kdgrp

where kdgrp in s_kdgrp.

endselect.

if sy-subrc ne 0.

message e000 with 'Invalid Customer Group'(025).

endif.

endif.

• Validation of Sales Employee(district)

clear t171.

if not s_bzirk is initial.

select bzirk from t171 up to 1 rows

into t171-bzirk

where bzirk in s_bzirk.

endselect.

if sy-subrc ne 0.

message e000 with 'Invalid Sales Employee'(026).

endif.

endif.

• Validation of Date

if p_date+4(2) = '02'.

if ( p_date+6(2) <> '28' ).

message e000 with 'Enter Last Date of the month'(030).

endif.

elseif ( p_date+4(2) = '04' or

p_date+4(2) = '06' or

p_date+4(2) = '09' or

p_date+4(2) = '11' ).

if p_date+6(2) <> '30'.

message e000 with 'Enter Last Date of the month'(030).

endif.

else.

if p_date+6(2) <> '31' .

message e000 with 'Enter Last Date of the month'(030).

endif.

endif.

endform. " SCREEN_CHECK

&----

---

*& Form COL_HEADING

&----

---

• Write the Column Headings for detailed report

----

---

form col_heading.

skip .

write: /66 'DSO/DOO REPORT AS AT:'(005) centered,

88 p_date centered, 151 sy-pagno.

skip .

write: /2 'CoCode:'(006),13(4) p_bukrs color 3,

20 'Currency:'(020), v_waers color 3.

if not s_vkorg is initial.

if not s_vkorg-high is initial.

write: /2 'SalesOrgn:'(021),13 s_vkorg-low color 3,

'-', s_vkorg-high color 3.

else.

write: /2 'SalesOrgn:'(021),13 s_vkorg-low color 3.

endif.

endif.

if not s_land1 is initial.

if not s_land1-high is initial.

write: /2 'Country:'(022),13 s_land1-low color 3,

'-', s_land1-high color 3.

else.

write: /2 'Country:'(022),13 s_land1-low color 3.

endif.

endif.

if not s_kunnr is initial.

if not s_kunnr-high is initial.

write: /2 'Customer:'(023),13 s_kunnr-low color 3,

'-', s_kunnr-high color 3.

else.

write: /2 'Customer:'(023),13 s_kunnr-low color 3.

endif.

endif.

if not s_kdgrp is initial.

if not s_kdgrp-high is initial.

write: /2 'Cust.Grp:'(027),13 s_kdgrp-low color 3,

'-', s_kdgrp-high color 3.

else.

write: /2 'Cust.Grp:'(027),13 s_kdgrp-low color 3.

endif.

endif.

if not s_bzirk is initial.

if not s_bzirk-high is initial.

write: /2 'Sales Emp:'(028),13 s_bzirk-low color 3,

'-', s_bzirk-high color 3.

else.

write: /2 'Sales Emp:'(028),13 s_bzirk-low color 3.

endif.

endif.

write: 135 'Run Date:'(007), sy-datum.

format color col_heading.

write at /1(156) sy-uline .

write: / sy-vline,

12 sy-vline,

43 sy-vline,

48 sy-vline, 49(15) 'Outstanding'(011) centered,

64 sy-vline, 65(15) 'Overdue'(012) centered,

80 sy-vline, 81(15) 'Sales'(014) centered,

96 sy-vline, 97(15) 'Sales'(014) centered,

112 sy-vline,113(15) 'Sales'(014) centered,

128 sy-vline,129(15) 'Total'(032) centered,

144 sy-vline,

150 sy-vline,

156 sy-vline.

write: / sy-vline, 2(10) 'Customer'(008) centered,

12 sy-vline, 13(30) 'Customer Name'(009),

43 sy-vline, 44(4) 'Ctry'(024) centered,

48 sy-vline, 49(15) 'Balance'(010) centered,

64 sy-vline, 65(15) 'Balance'(010) centered,

80 sy-vline, 81(15) 'Previous Month'(013) centered,

96 sy-vline, 97(15) 'Last Month'(029) centered,

112 sy-vline,113(15) 'Current Month'(031) centered,

128 sy-vline,129(15) 'Sales'(014) centered,

144 sy-vline,145(5) 'DSO'(015) centered,

150 sy-vline,151(5) 'DOO'(018) centered,

156 sy-vline.

write at /1(156) sy-uline.

format color off.

endform. " COL_HEADING

&----

---

*& Form GET_DATES

&----

---

• Calculate dates for the last three months

----

---

form get_dates.

v_date1 = p_date.

• 3 months Back Month start date

if v_date1+4(2) = '01'.

v_date1+4(2) = '11'.

v_date10(4) = v_date10(4) - 1.

elseif v_date1+4(2) = '02'.

v_date1+4(2) = '12'.

v_date10(4) = v_date10(4) - 1.

else.

v_date14(2) = v_date14(2) - 2.

endif.

if v_date1+6(2) <> '01'.

v_date1+6(2) = '01'.

endif.

v_d_days = p_date - v_date1.

v_d_days = v_d_days + 1.

• Calculate 3 months

v_mon1 = p_date+4(2).

v_mon2 = p_date+4(2) - 1.

v_mon3 = p_date+4(2) - 2.

endform. " GET_DATES

&----

---

*& Form GET_SALES_DATA

&----

---

• Select the Sales data for the Last 90 days

----

---

form get_sales_data.

select

vbeln " Billing Doc

fkdat " Billing Date

kunag " Customer Number

fkart " Billing Type

vkorg " Sales Organization

land1 " Country

bukrs " Company Code

netwr " Net Value

waerk " Currency

kdgrp " Customer Group

bzirk " Sales Employee

into table it_vbrk

from vbrk

where

bukrs = p_bukrs and

vkorg in s_vkorg and

land1 in s_land1 and

kunag in s_kunnr and

kdgrp in s_kdgrp and

bzirk in s_bzirk and

rfbsk = c_rfbsk.

sort it_vbrk by vbeln fkdat kunnr.

clear v_waers.

select single waers from t001

into v_waers where bukrs = p_bukrs.

endform. " GET_SALES_DATA

&----

---

*& Form GET_OPEN_DATA

&----

---

• Get the Customer Open Items from BSID

----

---

form get_open_data.

select kunnr " Customer Number

gjahr " Fiscal Year

belnr " Acc.Document

buzei " Item

budat " Posting Date

blart " Doc Type

shkzg " Dr/Cr Indicator

dmbtr " Amount(Local Curr)

dmbe2 " Amount(Doc Curr)

waers " Currency

zterm " Payment Terms

vbeln " Billing Doc

zfbdt " Baseline Date

zbd1t " No of days

into table it_bsid

from bsid

where bukrs = p_bukrs and

kunnr in s_kunnr and

budat le p_date.

sort it_bsid by kunnr gjahr belnr buzei budat.

endform. " GET_OPEN_DATA

&----

---

*& Form GET_CLOSE_DATA

&----

---

• Get the Customer closed Items from BSID

----

---

form get_close_data.

select kunnr " Customer Number

gjahr " Fiscal Year

belnr " Acc.Document

buzei " Item

budat " Posting Date

blart " Doc Type

shkzg " Dr/Cr Indicator

dmbtr " Amount(Local Curr)

dmbe2 " Amount(Doc Curr)

waers " Currency

zterm " Payment Terms

vbeln " Billing Doc

zfbdt " Baseline Date

zbd1t " No of days

into table it_bsad

from bsad

where bukrs = p_bukrs and

kunnr in s_kunnr and

augdt > p_date and

budat le p_date.

sort it_bsad by kunnr gjahr belnr buzei budat.

• Get the sales data from BSAD

select kunnr " Customer Number

gjahr " Fiscal Year

belnr " Acc.Document

buzei " Item

budat " Posting Date

blart " Doc Type

shkzg " Dr/Cr Indicator

dmbtr " Amount(Local Curr)

dmbe2 " Amount(Doc Curr)

waers " Currency

zterm " Payment Terms

vbeln " Billing Doc

zfbdt " Baseline Date

zbd1t " No of days

into table it_bsad1

from bsad

for all entries in it_vbrk

where kunnr = it_vbrk-kunnr and

vbeln = it_vbrk-vbeln and

bukrs = it_vbrk-bukrs and

( budat le p_date and

budat ge v_date1 ) and

blart = c_blart.

sort it_bsad1 by kunnr gjahr belnr buzei budat.

• Fetch the Customer Name and Country from KNA1

select kunnr " Customer Number

name1 " Name

land1 " Country

into table it_kna1

from kna1

where kunnr in s_kunnr.

endform. " GET_CLOSE_DATA

&----

---

*& Form PROCESS_DATA

&----

---

• Get the data from BSID,BSAD and KNA1 into one int.table

----

---

form process_data.

****AR********

*Move the data from BSID to it_arbal int table

loop at it_bsid.

move-corresponding it_bsid to it_arbal.

it_arbal-due_date = it_bsid-zfbdt + it_bsid-zbd1t.

• Move VBRK Fields to AR side

read table it_vbrk with key kunnr = it_bsid-kunnr

vbeln = it_bsid-vbeln.

if sy-subrc = 0.

it_arbal-vkorg = it_vbrk-vkorg.

it_arbal-kdgrp = it_vbrk-kdgrp.

it_arbal-bzirk = it_vbrk-bzirk.

endif.

• Move KNA1 Fields to AR side

read table it_kna1 with key kunnr = it_bsid-kunnr.

if sy-subrc = 0.

move-corresponding it_kna1 to it_arbal.

endif.

append it_arbal.

clear it_arbal.

endloop.

*Move the data from BSAD to it_arbal int table

loop at it_bsad.

move-corresponding it_bsad to it_arbal.

it_arbal-due_date = it_bsad-zfbdt + it_bsad-zbd1t.

• Move VBRK Fields to AR side

read table it_vbrk with key kunnr = it_bsad-kunnr

vbeln = it_bsad-vbeln.

if sy-subrc = 0.

it_arbal-vkorg = it_vbrk-vkorg.

it_arbal-kdgrp = it_vbrk-kdgrp.

it_arbal-bzirk = it_vbrk-bzirk.

endif.

• Move KNA1 Fields to AR side

read table it_kna1 with key kunnr = it_bsad-kunnr.

if sy-subrc = 0.

move-corresponding it_kna1 to it_arbal.

endif.

append it_arbal.

clear it_arbal.

endloop.

• Calculate the Overdue amount

sort it_arbal by kunnr vbeln belnr.

loop at it_arbal.

if it_arbal-shkzg = c_shkzg. " Credit

it_arbal-dmbtr = it_arbal-dmbtr * -1.

it_arbal-dmbe2 = it_arbal-dmbe2 * -1.

endif.

v_days = it_arbal-due_date - p_date.

if v_days le 0.

it_arbal-due_amnt = it_arbal-dmbtr.

endif.

modify it_arbal index sy-tabix.

endloop.

sort it_arbal by kunnr vbeln belnr.

• Calculating the Total AR Balance & Overdue

loop at it_arbal where vkorg in s_vkorg and

land1 in s_land1 and

kdgrp in s_kdgrp and

bzirk in s_bzirk.

at end of kunnr.

read table it_arbal index sy-tabix.

it_report-kunnr = it_arbal-kunnr.

it_report-name1 = it_arbal-name1.

it_report-land1 = it_arbal-land1.

sum.

it_report-dmbtr = it_arbal-dmbtr.

it_report-dmbe2 = it_arbal-dmbe2.

it_report-due_amt = it_arbal-due_amnt.

endat.

append it_report.

clear it_report.

endloop.

delete it_report where kunnr = ' ' and dmbtr = 0.

**SALES********

*Move the data from BSID to it_arsale int table

loop at it_bsid where ( budat ge v_date1 and

budat le p_date ) and

blart = c_blart.

move-corresponding it_bsid to it_arsale.

if it_arsale-shkzg = c_shkzg. " Credit

it_arsale-dmbtr = it_arsale-dmbtr * -1.

it_arsale-dmbe2 = it_arsale-dmbe2 * -1.

endif.

• Seperate Three months sales Amounts

if it_arsale-budat+4(2) = v_mon1.

it_arsale-amnt1 = it_arsale-dmbtr.

elseif it_arsale-budat+4(2) = v_mon2.

it_arsale-amnt2 = it_arsale-dmbtr.

elseif it_arsale-budat+4(2) = v_mon3.

it_arsale-amnt3 = it_arsale-dmbtr.

endif.

• Move VBRK Fields to AR side

read table it_vbrk with key kunnr = it_bsid-kunnr

vbeln = it_bsid-vbeln.

if sy-subrc = 0.

it_arsale-vkorg = it_vbrk-vkorg.

it_arsale-kdgrp = it_vbrk-kdgrp.

it_arsale-bzirk = it_vbrk-bzirk.

endif.

• Move KNA1 Fields to Sales side

read table it_kna1 with key kunnr = it_bsid-kunnr.

if sy-subrc = 0.

move-corresponding it_kna1 to it_arsale.

endif.

append it_arsale.

clear it_arsale.

endloop.

*Move the data from BSAD1 to it_arsale int table

loop at it_bsad1.

move-corresponding it_bsad1 to it_arsale.

if it_arsale-shkzg = c_shkzg. " Credit

it_arsale-dmbtr = it_arsale-dmbtr * -1.

it_arsale-dmbe2 = it_arsale-dmbe2 * -1.

endif.

• Seperate Three months sales Amounts

if it_arsale-budat+4(2) = v_mon1.

it_arsale-amnt1 = it_arsale-dmbtr.

elseif it_arsale-budat+4(2) = v_mon2.

it_arsale-amnt2 = it_arsale-dmbtr.

elseif it_arsale-budat+4(2) = v_mon3.

it_arsale-amnt3 = it_arsale-dmbtr.

endif.

• Move VBRK Fields to Sales side

read table it_vbrk with key kunnr = it_bsad1-kunnr

vbeln = it_bsad1-vbeln.

if sy-subrc = 0.

it_arsale-vkorg = it_vbrk-vkorg.

it_arsale-kdgrp = it_vbrk-kdgrp.

it_arsale-bzirk = it_vbrk-bzirk.

endif.

• Move KNA1 Fields to Sales side

read table it_kna1 with key kunnr = it_bsad1-kunnr.

if sy-subrc = 0.

move-corresponding it_kna1 to it_arsale.

endif.

append it_arsale.

clear it_arsale.

endloop.

• Calculating the Total 3 months Sales

loop at it_arsale.

at end of kunnr.

read table it_arsale index sy-tabix.

it_sales-kunnr = it_arsale-kunnr.

sum.

it_sales-dmbtr = it_arsale-dmbtr.

it_sales-dmbe2 = it_arsale-dmbe2.

it_sales-amnt1 = it_arsale-amnt1.

it_sales-amnt2 = it_arsale-amnt2.

it_sales-amnt3 = it_arsale-amnt3.

endat.

append it_sales.

clear it_sales.

endloop.

delete it_sales where kunnr = ' ' and dmbtr = 0.

endform. " PROCESS_DATA

&----

---

*& Form WRITE_REPORT

&----

---

• Write the Summary Report Output

----

---

form write_report.

clear: v_sum1,v_sum2,v_sum3.

loop at it_report.

at new kunnr.

read table it_report index sy-tabix.

write: / sy-vline, 2(10) it_report-kunnr color 1,

12 sy-vline, 13(30) it_report-name1,

43 sy-vline, 44(4) it_report-land1,

48 sy-vline.

endat.

at end of kunnr.

sum.

write: 1 sy-vline, 12 sy-vline,

43 sy-vline, 48 sy-vline,

49(15) it_report-dmbtr currency v_waers,

64 sy-vline,

65(15) it_report-due_amt currency v_waers,

80 sy-vline.

read table it_sales with key kunnr = it_report-kunnr.

write: 81(15) it_sales-amnt3 currency v_waers,

96 sy-vline,

97(15) it_sales-amnt2 currency v_waers,

112 sy-vline,

113(15) it_sales-amnt1 currency v_waers,

128 sy-vline,

129(15) it_sales-dmbtr currency v_waers.

if it_sales-dmbtr <> 0.

v_num1 = it_report-dmbtr / it_sales-dmbtr .

v_num2 = it_report-due_amt / it_sales-dmbtr .

endif.

v_dso = floor( v_num1 * v_d_days ).

v_doo = floor( v_num2 * v_d_days ).

write: 144 sy-vline,

145(5) v_dso no-sign,

150 sy-vline,

151(5) v_doo no-sign,

156 sy-vline.

endat.

• Totals

v_sum1 = v_sum1 + it_report-dmbtr.

v_sum2 = v_sum2 + it_report-due_amt.

v_sum3 = v_sum3 + it_sales-dmbtr.

v_sum4 = v_sum4 + it_sales-amnt1.

v_sum5 = v_sum5 + it_sales-amnt2.

v_sum6 = v_sum6 + it_sales-amnt3.

• Hide the Customer for interactive purpose.

hide it_report-kunnr.

clear it_report-kunnr.

endloop.

write at /1(156) sy-uline.

• Calculation of Grand Totals

format color 3.

write: /1 sy-vline, 2 'Gr.Total'(019),

12 sy-vline, 13 'As At:'(033),p_date,

43 sy-vline, 48 sy-vline,

49(15) v_sum1 currency it_bsid-waers,

64 sy-vline,

65(15) v_sum2 currency it_bsid-waers,

80 sy-vline,

81(15) v_sum6 currency it_bsid-waers,

96 sy-vline,

97(15) v_sum5 currency v_waers,

112 sy-vline,

113(15) v_sum4 currency v_waers,

128 sy-vline,

129(15) v_sum3 currency v_waers.

if v_sum3 <> 0.

v_avg_dso = ( v_sum1 / v_sum3 ) * v_d_days.

v_avg_doo = ( v_sum2 / v_sum3 ) * v_d_days.

endif.

write: 144 sy-vline,145(5) v_avg_dso no-sign,

150 sy-vline,151(5) v_avg_doo no-sign,

156 sy-vline.

format color off.

clear: v_sum4,v_sum5,v_d_days,

v_avg_dso,v_avg_doo.

write at /1(156) sy-uline.

endform. " WRITE_REPORT1

&----

---

*& Form LINE_SEL_COL_HEADING

&----

---

• Column headings for the Interactive Report

----

---

form line_sel_col_heading.

skip.

write:/1(121) 'CUSTOMER WISE DOCUMENT DETAILS'(046) centered.

write:/2 'AR DETAILS'(049) color 1.

format color col_heading.

write at /1(121) sy-uline .

write: / sy-vline, 2(10) 'Acc.Doc.No'(034) centered,

12 sy-vline, 13(10) 'Post.Date'(035),

23 sy-vline, 24(4) 'DTyp'(036) centered,

28 sy-vline, 29(10) 'BillDoc.No'(037) centered,

39 sy-vline, 40(15) 'Amount(Local)'(038) centered,

55 sy-vline, 56(15) 'Amount(USD)'(039) centered,

71 sy-vline, 72(15) 'Overdue Amt'(040) centered,

87 sy-vline, 88(10) 'Due Date'(041) centered,

98 sy-vline, 99(5) 'PTerm'(042) centered,

104 sy-vline,105(5) 'SOrgn'(043) centered,

110 sy-vline,111(4) 'CGrp'(044) centered,

115 sy-vline,116(5) 'SEmp'(045) centered,

121 sy-vline.

write at /1(121) sy-uline.

format color off.

endform. " LINE_SEL_COL_HEADING

&----

---

*& Form LINE_SEL

&----

---

• When double clicked on KUNNR display the details of AR and Sales

----

---

form line_sel.

case sy-lsind.

when '1'.

data: lv_field(20),

lv_value(10).

get cursor field lv_field value lv_value.

if lv_field = 'IT_REPORT-KUNNR'.

if not lv_value is initial.

set parameter id 'KUN' field lv_value.

• Writing the AR Details

format color 3.

write:/1 sy-vline, 2(10) 'Customer:'(048),

it_report-kunnr no-gap, '-',

it_report-name1 no-gap, 'Country:'(047) no-gap,

it_report-land1, 121 sy-vline.

format color off.

write at /1(121) sy-uline.

loop at it_arbal where kunnr = it_report-kunnr.

write: / sy-vline, 2(10) it_arbal-belnr color 2,

12 sy-vline, 13(10) it_arbal-budat,

23 sy-vline, 24(4) it_arbal-blart,

28 sy-vline, 29(10) it_arbal-vbeln,

39 sy-vline,

40(15) it_arbal-dmbtr currency it_arbal-waers,

55 sy-vline,

56(15) it_arbal-dmbe2 currency it_arbal-waers,

71 sy-vline,

72(15) it_arbal-due_amnt currency it_arbal-waers,

87 sy-vline, 88(10) it_arbal-due_date,

98 sy-vline, 99(5) it_arbal-zterm,

104 sy-vline,105(5) it_arbal-vkorg,

110 sy-vline,111(4) it_arbal-kdgrp,

115 sy-vline,116(5) it_arbal-bzirk,

121 sy-vline.

• Totals for amount fields

v_sum7 = v_sum7 + it_arbal-dmbtr.

v_sum8 = v_sum8 + it_arbal-dmbe2.

v_sum9 = v_sum9 + it_arbal-due_amnt.

• Hide the field BELNR to go to the document.

hide it_arbal-belnr.

clear it_arbal-belnr.

endloop.

endif.

endif.

write at /1(121) sy-uline.

format color 3.

write: / sy-vline, 2(10) 'Total'(032),

12 sy-vline, 23 sy-vline,

28 sy-vline, 39 sy-vline,

40(15) v_sum7 currency it_arbal-waers,

55 sy-vline,

56(15) v_sum8 currency it_arbal-waers,

71 sy-vline,

72(15) v_sum9 currency it_arbal-waers,

87 sy-vline, 98 sy-vline,

104 sy-vline, 110 sy-vline,

115 sy-vline, 121 sy-vline.

write at /1(121) sy-uline.

format color off.

clear: v_sum7, v_sum8, v_sum9.

• Writing the sales Details

write:/2 'SALES DETAILS'(050) color 1.

write at /1(136) sy-uline.

format color col_heading.

write: / sy-vline, 2(10) 'Acc.Doc.No'(034) centered,

12 sy-vline, 13(10) 'Post.Date'(035),

23 sy-vline, 24(4) 'DTyp'(036) centered,

28 sy-vline, 29(10) 'BillDoc.No'(037) centered,

39 sy-vline, 40(15) 'Total Sales Amt'(051) centered,

55 sy-vline, 56(15) 'Amount(USD)'(039) centered,

71 sy-vline, 72(15) 'Current Month'(052) centered,

87 sy-vline, 88(15) 'Last Month'(053) centered,

103 sy-vline,104(15) 'Previous Month'(054) centered,

119 sy-vline,120(5) 'SOrgn'(043) centered,

125 sy-vline,126(4) 'CGrp'(044) centered,

130 sy-vline,131(5) 'SEmp'(045) centered,

136 sy-vline.

write at /1(136) sy-uline.

format color off.

loop at it_arsale where kunnr = it_report-kunnr.

write: / sy-vline, 2(10) it_arsale-belnr ,

12 sy-vline, 13(10) it_arsale-budat,

23 sy-vline, 24(4) it_arsale-blart,

28 sy-vline,

29(10) it_arsale-vbeln color 2,

39 sy-vline,

40(15) it_arsale-dmbtr currency it_arsale-waers,

55 sy-vline,

56(15) it_arsale-dmbe2 currency it_arsale-waers,

71 sy-vline,

72(15) it_arsale-amnt1 currency it_arsale-waers,

87 sy-vline,

88(15) it_arsale-amnt2 currency it_arsale-waers,

103 sy-vline,

104(15) it_arsale-amnt3 currency it_arsale-waers,

119 sy-vline,120(5) it_arsale-vkorg,

125 sy-vline,126(4) it_arsale-kdgrp,

130 sy-vline,131(5) it_arsale-bzirk,

136 sy-vline.

• Totals for amount fields

v_sum10 = v_sum10 + it_arsale-dmbtr.

v_sum11 = v_sum11 + it_arsale-dmbe2.

v_sum12 = v_sum12 + it_arsale-amnt1.

v_sum13 = v_sum13 + it_arsale-amnt2.

v_sum14 = v_sum14 + it_arsale-amnt3.

• Hide the field VBELN to go to the document.

hide it_arsale-vbeln.

clear it_arsale-vbeln.

endloop.

write at /1(136) sy-uline.

format color 3.

write: / sy-vline, 2(10) 'Total'(032),

12 sy-vline, 23 sy-vline,

28 sy-vline, 39 sy-vline,

40(15) v_sum10 currency it_arsale-waers,

55 sy-vline,

56(15) v_sum11 currency it_arsale-waers,

71 sy-vline,

72(15) v_sum12 currency it_arsale-waers,

87 sy-vline,

88(15) v_sum13 currency it_arsale-waers,

103 sy-vline,

104(15) v_sum14 currency it_arsale-waers,

119 sy-vline, 125 sy-vline,

130 sy-vline, 136 sy-vline.

format color off.

clear: v_sum10, v_sum11, v_sum12, v_sum13, v_sum14.

write at /1(136) sy-uline.

when '2'.

data: lv_field1(20),

lv_value1(10),

lv_date(10),

lv_gjahr like bsad-gjahr.

get cursor field lv_field1 value lv_value1.

• When double clicked on BELNR leave to FB03 Transaction

if lv_field1 = 'IT_ARBAL-BELNR'.

if not lv_value1 is initial.

read current line field value it_arbal-budat into lv_date.

lv_gjahr = lv_date+6(4).

set parameter id 'BUK' field p_bukrs.

set parameter id 'GJR' field lv_gjahr.

set parameter id 'BLN' field lv_value1.

call transaction 'FB03' and skip first screen.

endif.

• When double clicked on VBELN leave to VF03 Transaction

elseif lv_field1 = 'IT_ARSALE-VBELN'.

if not lv_value1 is initial.

set parameter id 'VF' field lv_value1.

call transaction 'VF03' and skip first screen.

endif.

endif.

endcase.

endform. " LINE_SEL

Reward points if useful

Regards

Anji

[Former Member]

That was a big help! Thanks a lot

[Former Member]

Very good report. Thanks anji & cheers for such contributors.

annie