REPORT YLWM016A
MESSAGE-ID YL.
*-----------------------------------------------------------------------
** Customs and Excise Report - Sheila Titchener Feb 1998
*-----------------------------------------------------------------------
* TABLES - Database *
*-----------------------------------------------------------------------
TABLES: MARA, "Material Master: General Data.
MAKT, "Material Descriptions
MBEW, "Material Valuation
MARC, "Material Master: C Segment
MKPF, "Header: Material Document
MSEG, "Document Segment: Material
LIPS, "SD document: Delivery: Item data
T001W, "value table for plant
VBKD, "Sales Document: Business Data
VBAK. "Sales Document: Header Data
*-----------------------------------------------------------------------
* DATA - Work Fields
*-----------------------------------------------------------------------
DATA: W_FILENAME LIKE RLGRAP-FILENAME.
*-----------------------------------------------------------------------
* DATA - INTERNAL TABLES
*-----------------------------------------------------------------------
* excel output table
DATA: BEGIN OF ITAB OCCURS 0,
TYPE(3),
MATNR LIKE MARA-MATNR,
QNTY LIKE MBEW-LBKUM,
VOLM LIKE MARA-VOLUM,
MAKTX LIKE MAKT-MAKTX,
ZZPACK LIKE MARA-ZZPACK,
VOLUM LIKE MARA-VOLUM,
ZZSPECGRAV LIKE MARA-ZZSPECGRAV,
VOLEH LIKE MARA-VOLEH,
STAWN LIKE MARC-STAWN,
LIFNR LIKE MSEG-LIFNR,
EBELN LIKE MSEG-EBELN,
KUNNR LIKE MSEG-KUNNR,
LGPLA LIKE MSEG-LGPLA,
BSTNK LIKE VBAK-BSTNK,
MBLNR LIKE MSEG-MBLNR,
ZEILE LIKE MSEG-ZEILE,
BWART LIKE MSEG-BWART,
BUDAT LIKE SY-DATUM,
END OF ITAB.
* table of excel field names
DATA: BEGIN OF FIELD OCCURS 0,
NAME(60),
END OF FIELD.
* selected material data from mara.makt & marc
DATA: BEGIN OF I_MARA OCCURS 0,
MATNR LIKE MARA-MATNR,
ZZPACK LIKE MARA-ZZPACK,
VOLUM LIKE MARA-VOLUM,
ZZSPECGRAV LIKE MARA-ZZSPECGRAV,
VOLEH LIKE MARA-VOLEH,
MAKTX LIKE MAKT-MAKTX,
STAWN LIKE MARC-STAWN,
END OF I_MARA.
* selected documents from mkpf
*ATA: BEGIN OF I_MKPF OCCURS 0,
* MBLNR LIKE MKPF-MBLNR,
* MJAHR LIKE MKPF-MJAHR,
* BUDAT LIKE MKPF-BUDAT,
* END OF I_MKPF.
* joined data from mseg & mkpf
DATA: BEGIN OF I OCCURS 0,
MBLNR LIKE MSEG-MBLNR,
MJAHR LIKE MSEG-MJAHR,
ZEILE LIKE MSEG-ZEILE,
LIFNR LIKE MSEG-LIFNR,
EBELN LIKE MSEG-EBELN,
KUNNR LIKE MSEG-KUNNR,
LGPLA LIKE MSEG-LGPLA,
MENGE LIKE MSEG-MENGE,
BUDAT LIKE SY-DATUM,
BWART LIKE MSEG-BWART,
END OF I.
*-----------------------------------------------------------------------
* SELECTION SCREEN - SELECT OPTIONS AND PARAMETERS
*-----------------------------------------------------------------------
SELECTION-SCREEN SKIP 2.
PARAMETERS: P_WERKS LIKE MBEW-BWKEY OBLIGATORY.
SELECT-OPTIONS: S_RANGE FOR MKPF-BUDAT OBLIGATORY.
SELECTION-SCREEN SKIP 2.
SELECTION-SCREEN ULINE.
PARAMETERS: P_ETHAN RADIOBUTTON GROUP GRP1,
P_HYDRO RADIOBUTTON GROUP GRP1 DEFAULT 'X'.
SELECTION-SCREEN SKIP 2.
SELECTION-SCREEN ULINE.
PARAMETERS: P_FILE(40) LOWER CASE.
SELECTION-SCREEN ULINE.
*-----------------------------------------------------------------------
INITIALIZATION.
*-----------------------------------------------------------------------
* set defaults in parameters
W_FILENAME = 'C:\SAP\ylwm016A'.
P_FILE = 'C:\SAP\ylwm016a'.
S_RANGE-HIGH = SY-DATUM.
P_WERKS = '5020'.
PERFORM SET_UP_EXCEL_FIELD_NAMES.
AT SELECTION-SCREEN.
** validate parameters
SELECT SINGLE * FROM T001W
WHERE WERKS = P_WERKS .
IF SY-SUBRC NE 0 .
MESSAGE E022 WITH 'Plant' P_WERKS.
ENDIF.
*-----------------------------------------------------------------------
* Start of selection processing
*-----------------------------------------------------------------------
START-OF-SELECTION.
*-----------------------------------------------------------------------
W_FILENAME = P_FILE.
*......................................................................
*** 5.1
* select required materials - ethanol or hydrocarbons
IF P_ETHAN = 'X'.
SELECT * FROM MARA INTO CORRESPONDING FIELDS OF TABLE I_MARA
WHERE ZZETHLTYPE = 'F'.
ELSE.
SELECT * FROM MARA INTO CORRESPONDING FIELDS OF TABLE I_MARA
WHERE ZZHYDROIND = 'X'.
ENDIF.
* read additional material data and create balance records
PERFORM CREATE_BAL.
*......................................................................
LOOP AT I_MARA.
*** 5.2
* get material documents for required dates. - MKPF and
* get material document segments - MSEG into table I
PERFORM JOIN_MSEG_MKPF.
*** 5.3
* create type REC output records for each material
LOOP AT I WHERE BWART = 101 OR BWART = 102
OR BWART = 122 OR BWART = 501.
PERFORM CREATE_REC.
ENDLOOP.
*......................................................................
*** 5.4
* create type EXP output records for each material
LOOP AT I WHERE BWART = 601 OR BWART = 451.
PERFORM CREATE_EXP.
ENDLOOP.
*......................................................................
*** 5.5
* create type TFR output records for each material if ethanol requested
IF P_ETHAN = 'X'.
LOOP AT I WHERE BWART = 309.
PERFORM CREATE_TFR.
ENDLOOP.
ENDIF.
ENDLOOP.
*-----------------------------------------------------------------------
* End of selection
*-----------------------------------------------------------------------
END-OF-SELECTION.
* output table to excel
CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'
EXPORTING
FILE_NAME = W_FILENAME
* CREATE_PIVOT = 0
* DATA_SHEET_NAME = ' '
* PIVOT_SHEET_NAME = ' '
* PASSWORD = ' '
* PASSWORD_OPTION = 0
TABLES
* PIVOT_FIELD_TAB =
DATA_TAB = ITAB
FIELDNAMES = FIELD
EXCEPTIONS
FILE_NOT_EXIST = 1
FILENAME_EXPECTED = 2
COMMUNICATION_ERROR = 3
OLE_OBJECT_METHOD_ERROR = 4
OLE_OBJECT_PROPERTY_ERROR = 5
INVALID_FILENAME = 6
INVALID_PIVOT_FIELDS = 7
DOWNLOAD_PROBLEM = 8
OTHERS = 9.
WRITE: / 'Data output to file ', W_FILENAME.
* create 'REC' records for each material
* create 'EXP' records for each material
*&---------------------------------------------------------------------*
*& Form CREATE_BAL
*&---------------------------------------------------------------------*
* create BAL type records in the internal table for writing to
* excel
*----------------------------------------------------------------------*
FORM CREATE_BAL.
CLEAR ITAB.
LOOP AT I_MARA.
CLEAR: MAKT, MBEW, MARC.
* material description
SELECT SINGLE MAKTX FROM MAKT INTO I_MARA-MAKTX
WHERE MATNR = I_MARA-MATNR
AND SPRAS = SY-LANGU.
* Total valuated stock
SELECT SINGLE LBKUM FROM MBEW INTO ITAB-QNTY
WHERE MATNR = I_MARA-MATNR
AND BWKEY = P_WERKS.
* commodity code
SELECT SINGLE STAWN FROM MARC INTO I_MARA-STAWN
WHERE MATNR = I_MARA-MATNR
AND WERKS = P_WERKS.
MODIFY I_MARA.
* create BAL record
ITAB-TYPE = 'BAL'.
MOVE-CORRESPONDING I_MARA TO ITAB.
ITAB-VOLM = ITAB-QNTY * I_MARA-VOLUM.
IF I_MARA-VOLEH = 'ML'.
ITAB-VOLM = ITAB-VOLUM / 1000.
ENDIF.
ITAB-BUDAT = SY-DATUM.
APPEND ITAB.
ENDLOOP.
ENDFORM. " CREATE_BAL
*&---------------------------------------------------------------------*
*& Form CREATE_EXP
*&---------------------------------------------------------------------*
* text *
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM CREATE_EXP.
DATA: BEGIN OF WA,
VGBEL LIKE LIPS-VGBEL,
VGPOS LIKE LIPS-VGPOS,
KDGRP LIKE VBKD-KDGRP,
BSTNK LIKE VBAK-BSTNK,
END OF WA.
CLEAR ITAB.
SELECT SINGLE VGBEL VGPOS FROM LIPS INTO WA
WHERE VBELN = ITAB-LGPLA.
SELECT SINGLE KDGRP FROM VBKD INTO WA
WHERE VBELN = WA-VGBEL
AND POSNR = WA-VGPOS.
SELECT SINGLE BSTNK FROM VBAK INTO WA
WHERE VBELN = WA-VGBEL.
CASE WA-KDGRP.
WHEN 'X'.
ITAB-TYPE = 'EXP'.
WHEN 'D'.
ITAB-TYPE = 'DEA'.
WHEN OTHERS.
ITAB-TYPE = 'HOM'.
ENDCASE.
MOVE-CORRESPONDING I TO ITAB.
MOVE-CORRESPONDING I_MARA TO ITAB.
ITAB-QNTY = I-MENGE.
IF I-BWART = 451.
ITAB-QNTY = ITAB-QNTY * -1.
ENDIF.
ITAB-VOLM = ITAB-QNTY * I_MARA-VOLUM.
IF I_MARA-VOLEH = 'ML'.
ITAB-VOLM = ITAB-VOLUM / 1000.
ENDIF.
APPEND ITAB.
ENDFORM. " CREATE_EXP
*&---------------------------------------------------------------------*
*& Form CREATE_REC
*&---------------------------------------------------------------------*
* text *
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM CREATE_REC.
CLEAR ITAB.
ITAB-TYPE = 'REC'.
MOVE-CORRESPONDING I TO ITAB.
MOVE-CORRESPONDING I_MARA TO ITAB.
ITAB-QNTY = I-MENGE.
IF I-BWART = 102 OR I-BWART = 122.
ITAB-QNTY = ITAB-QNTY * -1.
ENDIF.
ITAB-VOLM = ITAB-QNTY * I_MARA-VOLUM.
IF I_MARA-VOLEH = 'ML'.
ITAB-VOLM = ITAB-VOLUM / 1000.
ENDIF.
ITAB-LGPLA = ' '.
APPEND ITAB.
ENDFORM. " CREATE_REC
*&---------------------------------------------------------------------*
*& Form CREATE_TFR
*&---------------------------------------------------------------------*
* text *
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM CREATE_TFR.
CLEAR ITAB.
ITAB-TYPE = 'TFR'.
MOVE-CORRESPONDING I TO ITAB.
MOVE-CORRESPONDING I_MARA TO ITAB.
ITAB-QNTY = I-MENGE * -1.
ITAB-VOLUM = ITAB-QNTY * I_MARA-VOLUM.
IF I_MARA-VOLEH = 'ML'.
ITAB-VOLUM = ITAB-VOLUM / 1000.
ENDIF.
ITAB-LGPLA = ' '.
APPEND ITAB.
ENDFORM. " CREATE_TFR
*&---------------------------------------------------------------------*
*& Form READ_MKPF
*&---------------------------------------------------------------------*
* read all material documents with date range *
*----------------------------------------------------------------------*
FORM READ_MKPF.
* BUDAT is secondary index of MKPF
* SELECT MBLNR BUDAT MJAHR FROM MKPF
* INTO CORRESPONDING FIELDS OF TABLE I_MKPF
* WHERE BUDAT IN S_RANGE.
*
ENDFORM. " READ_MKPF
*&---------------------------------------------------------------------*
*& Form READ_MSEG
*&---------------------------------------------------------------------*
* text *
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM READ_MSEG.
* LOOP AT I_MARA.
*
* SELECT * FROM MSEG APPENDING TABLE I FOR ALL ENTRIES IN I_MKPF
* WHERE MBLNR = I_MKPF-MBLNR
* AND MJAHR = I_MKPF-MJAHR
* AND MATNR = I_MARA-MATNR .
*
* ENDLOOP.
ENDFORM. " READ_MSEG
*..............TRY THIS JOIN SELECT????????////
FORM JOIN_MSEG_MKPF.
SELECT K~MBLNR K~MJAHR G~LIFNR G~EBELN G~KUNNR G~LGPLA G~ZEILE
G~BWART K~BUDAT G~MENGE
INTO CORRESPONDING FIELDS OF TABLE I
FROM MKPF AS K INNER JOIN MSEG AS G
ON K~MBLNR = G~MBLNR AND
K~MJAHR = G~MJAHR
WHERE K~BUDAT IN S_RANGE
AND G~WERKS = P_WERKS
AND G~MATNR = I_MARA-MATNR.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_UP_EXCEL_FIELD_NAMES
*&---------------------------------------------------------------------*
* text *
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM SET_UP_EXCEL_FIELD_NAMES.
FIELD-NAME = 'Type'.
APPEND FIELD.
FIELD-NAME = 'Material'.
APPEND FIELD.
FIELD-NAME = 'Qnty'.
APPEND FIELD.
FIELD-NAME = 'Litres'.
APPEND FIELD.
FIELD-NAME = 'Desc'.
APPEND FIELD.
FIELD-NAME = 'Pack'.
APPEND FIELD.
FIELD-NAME = 'PackVol'.
APPEND FIELD.
FIELD-NAME = 'SG'.
APPEND FIELD.
FIELD-NAME = 'VolUnit'.
APPEND FIELD.
FIELD-NAME = 'ICT'.
APPEND FIELD.
FIELD-NAME = 'Vendor'.
APPEND FIELD.
FIELD-NAME = 'PO'.
APPEND FIELD.
FIELD-NAME = 'Customer'.
APPEND FIELD.
FIELD-NAME = 'Deliv'.
APPEND FIELD.
FIELD-NAME = 'CustPO'.
APPEND FIELD.
FIELD-NAME = 'MatDoc'.
APPEND FIELD.
FIELD-NAME = 'MDPos'.
APPEND FIELD.
FIELD-NAME = 'MType'.
APPEND FIELD.
FIELD-NAME = 'Date'.
APPEND FIELD.
ENDFORM. " SET_UP_EXCEL_FIELD_NAMES