Table of Contents:
REPORT ZIJALLIANCE LINE-SIZE 135
LINE-COUNT 65 NO STANDARD PAGE HEADING.
*@----------------------------------------------------------------------
*@ Program id : ZALLIANCE
*@ Program Desc : This is a report that gives a summary of each sales
*@ order. All the billing documents are added together for each sales
*@ order and the credits and depits are separated.
*@ Sorted by Job type, job date, and order #.
*@
*@ Transaction Code - ZVALLIANCE
*@ Input files : ID - none
*@ Output files : ID - none
*@
*@ Tables Updated: Table - none
*@
*@
*@ Author : IJADALOW
*@ Date : MAY 29, 2001
*@ Requested by:
*@----------------------------------------------------------------------
*@ Algorithm:
*@
*@----------------------------------------------------------------------
** Change History:
** Correction Mod by Date Description
** LABK931392 IJADALOW December 04, 2001.
** Replacing tables TVV1 and TVV1T, with tables T2502 and
** T25A2 respectively. T2502 contains all job types from
** the past as well as current ones.
** Requestd by Ophelia Tik.
** Look for "IJ1" for the changes made.
*@----------------------------------------------------------------------
*@----------------------------------------------------------------------
** Table Declarations **
TABLES: VBAK,
VBRK,
VBPA,
bkpf,
* tvv1t. "ij1
t25a2. "ij1
TABLES: VBFA.
INCLUDE .
INCLUDE ZSMASTER.
TYPEs:
BEGIN OF t_bills,
kvgr1 LIKE vbak-kvgr1, "job type
aubel like vbrp-aubel, "sales order #
xblnr like bkpf-xblnr, "Billing doc #
Fkart like vbrk-fkart, "Billing Type
netwr like vbrk-netwr, "Net Value
vdatu LIKE vbak-vdatu, "job date=requested delivery date
budat like bkpf-budat, "billing date
auart LIKE vbak-auart, "doc. type
bukrs LIKE bkpf-bukrs, "Company code/sales organization
kunnr LIKE vbak-kunnr, "customer = sold-to-party
vgbel like vbak-vgbel, "Reference Document
credit like vbrk-netwr, "Credit Memo
baschr(4) TYPE c, "base in charge
noncform(12) TYPE c, "non conformance
location TYPE mac_t_so_location, "adrc-name1 / sadr-name1,
formation TYPE mac_t_so_formation, "adrc-city2
* description like tvv1t-bezei, "Job Description "ij1
description like t25a2-bezek, "Job Description "ij1
END OF t_bills.
TYPES: BEGIN OF t_download,
location TYPE mac_t_so_location, "adrc-name1 / sadr-name1,
vdatu LIKE vbak-vdatu, "job date=requested delivery date
aubel like vbrp-aubel, "sales order #
* description like tvv1t-bezei, "Job Description "IJ1
description like t25a2-bezek, "Job Description "IJ1
baschr(4) TYPE c, "base in charge
total like vbrk-netwr,
noncform(12) TYPE c, "non conformance
credit(20) TYPE c, "Credit Memo
END OF t_download.
DATA: wa_bill type t_bills,
bills like standard table of wa_bill,
itab like standard table of wa_bill,
wa_download TYPE t_download,
it_download LIKE STANDARD TABLE OF wa_download.
DATA: BEGIN OF i_auart OCCURS 1,
sign(1) TYPE c,
option(2) TYPE c,
low(18) TYPE c,
high(18) TYPE c,
END OF i_auart.
DATA: sub_total like wa_bill-netwr,
late_msg(35) type c,
first_msg(80),
second_msg(80),
cust_name like kna1-name1. "customer name.
DATA: BEGIN OF it_title OCCURS 10,
title(15) TYPE c,
END OF it_title.
DATA: w_excel_default_path(20) TYPE c,
w_excel_default_file(20) TYPE c,
w_excel_filename LIKE ibipparms-path.
SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_compny FOR bkpf-bukrs
NO INTERVALS NO-EXTENSION DEFAULT '1000'.
SELECT-OPTIONS: s_kunnr FOR vbak-kunnr NO INTERVALS NO-EXTENSION.
SELECT-OPTIONS: s_pstDat FOR bkpf-budat.
SELECT-OPTIONS: s_delDat FOR vbak-vdatu.
SELECTION-SCREEN END OF BLOCK 1.
INITIALIZATION.
* s_delDat-LOW = '20010401'.
* s_delDat-HIGH = '20010430'.
* s_pstDat-LOW = '20010401'.
* s_pstDat-HIGH = '20010412'.
* APPEND s_delDat.
END-OF-SELECTION.
SET PF-STATUS 'STATUS1'.
PERFORM load_data.
PERFORM display_data.
TOP-OF-PAGE.
CONCATENATE 'Alliance Report for_' cust_name
INTO first_msg.
CONCATENATE 'Billing Date:_'
s_pstDat-low(4) '/ '
s_pstDat-low+4(2) '/ '
s_pstDat-low+6(2)
' To_'
s_pstDat-high(4) '/ '
s_pstDat-high+4(2) '/ '
s_pstDat-high+6(2)
INTO second_msg.
* mac_default_report_header mac_c_null.
MAC_GENERIC_REPORT_HEADER first_msg second_msg MAC_C_TRUE MAC_C_TRUE.
IF NOT late_msg is initial.
WRITE: late_msg TO MAC_ZSMDISPL_G_LINE1(SY-LINSZ) CENTERED.
WRITE: MAC_ZSMDISPL_G_LINE1.
ENDIF.
PERFORM print_col_headings.
*/---------------------------------------------------------\*
AT USER-COMMAND.
CASE sy-ucomm.
WHEN 'DOWNLOAD'.
CLEAR w_excel_filename.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_filename = w_excel_default_file
def_path = w_excel_default_path
mask = ',*.* ,*.*.'
mode = 'S'
title = 'Select Output Excel File'
IMPORTING
filename = w_excel_filename
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
IF NOT w_excel_filename IS INITIAL.
PERFORM proc_download.
ENDIF.
ENDCASE.
*&---------------------------------------------------------------------*
*& Form LOAD_DATA
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM LOAD_DATA.
SELECT bkpf~xblnr bkpf~budat vbrp~aubel
into corresponding fields of table bills
from bkpf join vbrp on bkpf~xblnr = vbrp~vbeln
WHERE bkpf~bukrs IN s_compny "Company code
AND bkpf~budat IN s_pstDat "posting date
AND bkpf~blart = 'RV'
AND bkpf~bstat = ''.
LOOP AT bills into wa_bill.
IF wa_bill-aubel(4) = '0051'. "This is a reference document
SELECT SINGLE vgbel "so we get the actual one: '0011*'
INTO wa_bill-aubel
FROM vbak
WHERE vbeln = wa_bill-aubel.
ENDIF.
SELECT vdatu kvgr1 kunnr
FROM vbak
INTO CORRESPONDING FIELDS OF wa_bill
WHERE vbeln = wa_bill-aubel
AND kunnr IN s_kunnr.
**** Get Job description
*** IJ1 Replace Starts
* SELECT SINGLE bezei FROM tVV1t
SELECT SINGLE bezek FROM t25a2
INTO wa_bill-description
WHERE kvgr1 = wa_bill-kvgr1.
* wa_bill-description = wa_bill-description+3.
*** IJ1 Repalce Ends
append wa_bill to itab.
ENDSELECT.
ENDLOOP.
SELECT SINGLE name1 from kna1
INTO cust_name
WHERE kunnr in s_kunnr.
ENDFORM. " LOAD_DATA
*/-------------------------------------------------------------------*\
FORM print_col_headings.
FORMAT COLOR COL_HEADING.
NEW-LINE.
WRITE 01 line_top_left_corner AS LINE.
ULINE 02(135).
WRITE: 135 line_top_right_corner AS LINE NO-GAP.
WRITE: / SY-VLINE NO-GAP,
002 'Location'(002),
032 SY-VLINE NO-GAP,
033 'Job Date'(003),
043 SY-VLINE NO-GAP,
044 'SalesOrder'(004),
054 SY-VLINE NO-GAP,
055 'Job Type'(005),
075 SY-VLINE NO-GAP,
076 'Base'(006),
080 SY-VLINE NO-GAP,
081 'Total'(007),
101 SY-VLINE NO-GAP,
102 'Non_Conf #'(008),
114 SY-VLINE NO-GAP,
115 'Credit'(009),
135 SY-VLINE NO-GAP.
uline.
FORMAT RESET.
ENDFORM.
*/-------------------------------------------------------------------*\
*&---------------------------------------------------------------------*
*& Form display_data
*&---------------------------------------------------------------------*
* - Display all the data that fall in the user's input criteria
* -
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM display_data.
DATA: sub_total like wa_bill-netwr value 0,
grand_total like wa_bill-netwr value 0,
job_type like wa_bill-description.
FORMAT COLOR COL_NORMAL.
CLEAR late_msg.
SORT itab by aubel. "sort by SO #
DELETE ADJACENT DUPLICATES FROM itab COMPARING aubel.
SORT itab BY description vdatu aubel.
*/-- We will separate the Jobs pertaining to Prior months that
*/-- are outside the range and We will print them Below on the
*/-- same report.
LOOP at itab INTO wa_bill.
PERFORM GET_details.
IF wa_bill-vdatu >= s_delDat-low and
wa_bill-vdatu <= s_delDat-high.
sub_total = sub_total + wa_bill-netwr.
PERFORM detail_line.
PERFORM to_download.
ENDIF.
job_type = wa_bill-description.
AT END OF kvgr1.
IF not sub_total is initial.
FORMAT COLOR COL_TOTAL.
ULINE.
WRITE: /001 SY-VLINE NO-GAP,
054 SY-VLINE NO-GAP,
055 job_type,
075 SY-VLINE NO-GAP,
080 SY-VLINE NO-GAP,
081 sub_total,
101 SY-VLINE NO-GAP,
135 SY-VLINE NO-GAP.
ADD sub_total TO grand_total.
CLEAR sub_total.
ULINE.
FORMAT COLOR COL_NORMAL.
ENdIF.
ENDat.
ENDLOOP.
*/--- Grand Total
FORMAT COLOR COL_TOTAL.
WRITE: /001 SY-VLINE NO-GAP,
003 'Grand Total'(010),
080 SY-VLINE NO-GAP,
081 grand_total,
101 SY-VLINE NO-GAP,
135 SY-VLINE NO-GAP.
CLEAR grand_total.
ULINE.
FORMAT COLOR COL_NORMAL.
*/--- The Exception jobs are listed at the bottom of the list.
NEW-PAGE.
late_msg = 'JOBS PERTAINING TO PRIOR MONTHS'.
clear sub_total.
LOOP at itab INTO wa_bill.
IF wa_bill-vdatu < s_delDat-low.
sub_total = sub_total + wa_bill-netwr.
PERFORM detail_line.
ENDIF.
job_type = wa_bill-description.
AT END OF kvgr1.
IF not sub_total is initial.
FORMAT COLOR COL_TOTAL.
ULINE.
WRITE: /001 SY-VLINE NO-GAP,
054 SY-VLINE NO-GAP,
055 job_type,
075 SY-VLINE NO-GAP,
080 SY-VLINE NO-GAP,
081 sub_total,
101 SY-VLINE NO-GAP,
135 SY-VLINE NO-GAP.
CLEAR sub_total.
ULINE.
FORMAT COLOR COL_NORMAL.
ENdIF.
ENDat.
ENDLOOP.
FORMAT RESET.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form GET_details
*&---------------------------------------------------------------------*
* - Calculate the Total net value and credits for each sales order
* -
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM GET_Details.
CLEAR: wa_bill-netwr, wa_bill-credit.
MAC_GET_BILL_DETAILS_FROM_SO wa_bill-aubel
MAC_ZSMABAP4_I_SO_BILL_DETAILS.
LOOP AT MAC_ZSMABAP4_I_SO_BILL_DETAILS WHERE KEY = 'TL'.
IF MAC_ZSMABAP4_I_SO_BILL_DETAILS-type CA 'OS'.
ADD MAC_ZSMABAP4_I_SO_BILL_DETAILS-NETWR TO wa_bill-credit.
ELSEIF MAC_ZSMABAP4_I_SO_BILL_DETAILS-type CA 'PMN'.
ADD MAC_ZSMABAP4_I_SO_BILL_DETAILS-NETWR TO wa_bill-netwr.
ENDIF.
ENDLOOP.
mac_get_so_header_text wa_bill-aubel
mac_c_so_text_base_in_charge wa_bill-baschr.
mac_get_so_header_text wa_bill-aubel
mac_c_text_so_nonconformance wa_bill-noncform.
mac_get_so_well_loc_for wa_bill-aubel
wa_bill-location wa_bill-formation.
MODIFY itab from wa_bill.
ENDFORM. " GET_TOTALS
*&---------------------------------------------------------------------*
*& Form DETAIL_LINE
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM DETAIL_LINE.
write: /001 SY-VLINE NO-GAP,
002 wa_bill-location,
032 SY-VLINE NO-GAP,
033 wa_bill-vdatu,
043 SY-VLINE NO-GAP,
044 wa_bill-aubel,
054 SY-VLINE NO-GAP,
055 wa_bill-description,
075 SY-VLINE NO-GAP,
076 wa_bill-baschr,
080 SY-VLINE NO-GAP,
081 wa_bill-netwr,
101 SY-VLINE NO-GAP,
102 wa_bill-noncform,
114 SY-VLINE NO-GAP,
115 wa_bill-credit,
135 SY-VLINE NO-GAP.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form TO_DOWNLOAD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
FORM TO_DOWNLOAD.
wa_download-location = wa_bill-location.
wa_download-vdatu = wa_bill-vdatu.
wa_download-aubel = wa_bill-aubel.
wa_download-description = wa_bill-description.
wa_download-baschr = wa_bill-baschr.
wa_download-total = wa_bill-netwr.
wa_download-noncform = wa_bill-noncform.
IF wa_bill-credit IS INITIAL.
wa_download-credit = ' '.
ELSEIF wa_bill-credit < 0.
wa_download-credit = wa_bill-credit * -1.
ELSE.
wa_download-credit = wa_bill-credit.
ENDIF.
APPEND wa_download TO it_download .
ENDFORM. " TO_DOWNLOAD
*&---------------------------------------------------------------------*
*& Form proc_DOWNLOAD
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
FORM proc_download.
REFRESH it_title.
DEFINE lmac_append_title.
it_title-title = &1.
append it_title.
END-OF-DEFINITION.
lmac_append_title: text-002, text-003, text-004, text-005,
text-006, text-007, text-008, text-009.
mac_ws_download_titled_itab w_excel_filename it_download it_title.
IF sy-subrc = 0.
MESSAGE i001(zz) WITH 'Report has been downloaded to '
w_excel_filename.
ELSE.
MESSAGE i001(zz) WITH 'Unable to download to ' w_excel_filename.
ENDIF.
ENDFORM. " PROC_DOWNLOAD