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