DATA : FTAB TYPE SLIS_T_FIELDCAT_ALV,
LAYOUT TYPE SLIS_LAYOUT_ALV,REPID TYPE SY-REPID,
IT_SORT TYPE SLIS_T_SORTINFO_ALV WITH HEADER LINE,
L1 TYPE I,
L2 TYPE I.
DATA: IT_LISTSAL TYPE SLIS_T_LISTHEADER,
WA_LISTSAL TYPE SLIS_LISTHEADER.
DATA: IDD07V TYPE TABLE OF DD07V WITH HEADER LINE,
WA_IDD07V TYPE DD07V.
SELECT-OPTIONS :
S_VKORG FOR VBRK-VKORG,
S_VTWEG FOR VBRK-VTWEG,
S_SPART FOR VBRK-SPART,
S_FKDAT FOR VBRK-FKDAT.
DATA : BEGIN OF TY_FINAL,
VBELN TYPE VBRP-VBELN,
AUBEL TYPE VBRP-AUBEL,
AUDAT TYPE VBAK-AUDAT,
VBTYP TYPE VBRK-VBTYP,
FKIMG TYPE VBRP-FKIMG,
KDGRP TYPE VBRK-KDGRP,
VKBUR TYPE VBRP-VKBUR,
MATNR TYPE VBRP-MATNR,
ARKTX TYPE VBRP-ARKTX,
FKDAT TYPE VBRK-FKDAT,
PRSDT TYPE VBRP-PRSDT,
VRKME TYPE VBRP-VRKME,
NETWR TYPE VBRP-NETWR,
MWSBP TYPE VBRP-MWSBP,
VKORG TYPE VBRK-VKORG,
VTWEG TYPE VBRK-VTWEG,
SPART TYPE VBRK-SPART,
DDTEXT TYPE DD07V-DDTEXT,
KTEXT TYPE T151T-KTEXT,
BEZEI TYPE TVKBT-BEZEI,
YRR TYPE C LENGTH 4,
MTH TYPE C LENGTH 2,
KUNNR TYPE VBPA-KUNNR,
NAME1 TYPE ADRC-NAME1,
MC_CITY1 TYPE ADRC-MC_CITY1,
WERKS TYPE VBRP-WERKS,
P_NAME1 TYPE T001W-NAME1,
KUNRG TYPE VBRK-KUNRG,
END OF TY_FINAL.
DATA : IT_FINAL LIKE STANDARD TABLE OF TY_FINAL,
WA_FINAL LIKE TY_FINAL.
DATA : LV_KUNNR TYPE VBPA-KUNNR,
LV_NAME1 TYPE ADRC-NAME1,
LV_MC_CITY1 TYPE ADRC-MC_CITY1,
LV_ADRNR TYPE ADRC-ADDRNUMBER,
LV_P_NAME1 TYPE T001W-NAME1.
*&---------------------------------------------------------------------*
*& UPLOAD TO FTP DECLARATION ( START)
*&---------------------------------------------------------------------*
TYPES:
BEGIN OF X_CMDOUT,
LINE(100) TYPE C,
END OF X_CMDOUT,
BEGIN OF TY_FTP,
FILE_NM(110) TYPE C,
LOG_ID(4) TYPE N,
END OF TY_FTP.
*Data specifications of FTP server
*Handler and Key
DATA:
W_CMD(40) TYPE C,
GV_PWD1(30) TYPE C,
W_LOGID(4) TYPE N VALUE 1,
GV_KEY TYPE I VALUE 26101957,
GV_SLEN TYPE I,
WA_IPLFTP TYPE TY_FTP,
IT_IPLFTP TYPE STANDARD TABLE OF TY_FTP,
WA_BINARY_CONTENT TYPE X_CMDOUT,
IT_CMDOUT TYPE STANDARD TABLE OF X_CMDOUT,
GV_DATUM TYPE SY-DATUM,
GV_PATH LIKE EPSF-EPSPATH,
GV_HOST(30) TYPE C VALUE '139.59.93.36',
GV_USER(30) TYPE C VALUE 'symphony',
GV_PWD(30) TYPE C VALUE 'hqhrhwasB'.
DATA FNAME TYPE C LENGTH 40.
DATA CHARACTER_MODE TYPE C VALUE 'X'.
DATA : RESULT TYPE TABLE OF TEXT WITH HEADER LINE.
DATA: I_TEXT(4096) TYPE C OCCURS 0.
DATA : IT_FTP TYPE TABLE OF ZFI_EBS_FTP,
WA_FTP TYPE ZFI_EBS_FTP.
DATA: IT_BINARY_CONTENT TYPE SOLIX_TAB,
G_SIZE TYPE I,
W_HDL TYPE I.
DATA: C_DEST TYPE RFCDES-RFCDEST VALUE 'SAPFTPA',
C_SAP(84) TYPE C VALUE 'sympark_ca_value123456.xlsx',
V_DEST LIKE RFCDES-RFCDEST VALUE 'SAPFTPA'. "Background RFC destination
START-OF-SELECTION.
CALL FUNCTION 'DD_DOMVALUES_GET'
EXPORTING
DOMNAME = 'VBTYP' "<-- Your Domain Here
TEXT = 'X'
LANGU = SY-LANGU
TABLES
DD07V_TAB = IDD07V
EXCEPTIONS
WRONG_TEXTFLAG = 1
OTHERS = 2.
SELECT * FROM T151T INTO TABLE @DATA(IT_T151).
SELECT * FROM TVKBT INTO TABLE @DATA(IT_TVKBT).
SELECT A~AUBEL , C~AUDAT, A~VBELN, B~VBTYP , A~FKIMG , B~KDGRP, A~VKBUR, A~MATNR ,A~ARKTX, B~FKDAT, A~PRSDT,A~VRKME, A~NETWR, A~MWSBP, B~VKORG, B~VTWEG, B~SPART, A~WERKS,B~KUNRG INTO CORRESPONDING FIELDS OF TABLE @IT_FINAL
FROM VBRP AS A INNER JOIN VBRK AS B ON A~VBELN = B~VBELN INNER JOIN VBAK AS C ON A~AUBEL = C~VBELN
WHERE B~VKORG IN @S_VKORG AND B~VTWEG IN @S_VTWEG AND B~SPART IN @S_SPART AND B~FKDAT IN @S_FKDAT AND B~KDGRP NOT IN ('MT','OM','YW')
ORDER BY B~FKDAT.
IF SY-SUBRC = 0.
LOOP AT IT_FINAL INTO WA_FINAL.
READ TABLE IDD07V INTO WA_IDD07V WITH KEY DDLANGUAGE = 'E' DOMVALUE_L = WA_FINAL-VBTYP.
IF SY-SUBRC = 0.
WA_FINAL-DDTEXT = WA_IDD07V-DDTEXT.
ENDIF.
READ TABLE IT_T151 INTO DATA(WA_T151) WITH KEY SPRAS = 'E' KDGRP = WA_FINAL-KDGRP.
IF SY-SUBRC = 0.
WA_FINAL-KTEXT = WA_T151-KTEXT.
ENDIF.
READ TABLE IT_TVKBT INTO DATA(WA_TVKBT) WITH KEY SPRAS = 'E' VKBUR = WA_FINAL-VKBUR.
IF SY-SUBRC = 0.
WA_FINAL-BEZEI = WA_TVKBT-BEZEI.
ENDIF.
CLEAR : LV_KUNNR,LV_ADRNR,LV_NAME1,LV_MC_CITY1,LV_P_NAME1.
SELECT SINGLE KUNNR ADRNR INTO (LV_KUNNR,LV_ADRNR) FROM VBPA WHERE PARVW = 'WE' AND VBELN = WA_FINAL-AUBEL.
IF SY-SUBRC = 0.
WA_FINAL-KUNNR = LV_KUNNR.
SELECT SINGLE NAME1 MC_CITY1 INTO (LV_NAME1,LV_MC_CITY1) FROM ADRC WHERE ADDRNUMBER = LV_ADRNR.
IF SY-SUBRC = 0.
WA_FINAL-NAME1 = LV_NAME1.
WA_FINAL-MC_CITY1 = LV_MC_CITY1.
ENDIF.
ENDIF.
SELECT SINGLE NAME1 INTO (LV_P_NAME1) FROM T001W WHERE WERKS = WA_FINAL-WERKS.
IF SY-SUBRC = 0.
WA_FINAL-P_NAME1 = LV_P_NAME1.
ENDIF.
CONCATENATE WA_FINAL-FKDAT+0(4) '' INTO WA_FINAL-YRR.
CONCATENATE WA_FINAL-FKDAT+4(2) '' INTO WA_FINAL-MTH.
MODIFY IT_FINAL FROM WA_FINAL.
ENDLOOP.
PERFORM FIELD_CATLOG.
IF SY-BATCH = 'X'.
PERFORM CONVERT_TO_EXCEL.
PERFORM UPLOAD_ON_FTP.
ELSE.
PERFORM DISPLAY.
ENDIF.
ENDIF.
CALL FUNCTION 'DD_DOMVALUES_GET'
EXPORTING
DOMNAME = 'VBTYP' "<-- Your Domain Here
TEXT = 'X'
LANGU = SY-LANGU
TABLES
DD07V_TAB = IDD07V
EXCEPTIONS
WRONG_TEXTFLAG = 1
OTHERS = 2.
SELECT * FROM T151T INTO TABLE @DATA(IT_T151).
SELECT * FROM TVKBT INTO TABLE @DATA(IT_TVKBT).
SELECT A~AUBEL , C~AUDAT, A~VBELN, B~VBTYP , A~FKIMG , B~KDGRP, A~VKBUR, A~MATNR ,A~ARKTX, B~FKDAT, A~PRSDT,A~VRKME, A~NETWR, A~MWSBP, B~VKORG, B~VTWEG, B~SPART, A~WERKS,B~KUNRG INTO CORRESPONDING FIELDS OF TABLE @IT_FINAL
FROM VBRP AS A INNER JOIN VBRK AS B ON A~VBELN = B~VBELN INNER JOIN VBAK AS C ON A~AUBEL = C~VBELN
WHERE B~VKORG IN @S_VKORG AND B~VTWEG IN @S_VTWEG AND B~SPART IN @S_SPART AND B~FKDAT IN @S_FKDAT AND B~KDGRP NOT IN ('MT','OM','YW')
ORDER BY B~FKDAT.
IF SY-SUBRC = 0.
LOOP AT IT_FINAL INTO WA_FINAL.
READ TABLE IDD07V INTO WA_IDD07V WITH KEY DDLANGUAGE = 'E' DOMVALUE_L = WA_FINAL-VBTYP.
IF SY-SUBRC = 0.
WA_FINAL-DDTEXT = WA_IDD07V-DDTEXT.
ENDIF.
READ TABLE IT_T151 INTO DATA(WA_T151) WITH KEY SPRAS = 'E' KDGRP = WA_FINAL-KDGRP.
IF SY-SUBRC = 0.
WA_FINAL-KTEXT = WA_T151-KTEXT.
ENDIF.
READ TABLE IT_TVKBT INTO DATA(WA_TVKBT) WITH KEY SPRAS = 'E' VKBUR = WA_FINAL-VKBUR.
IF SY-SUBRC = 0.
WA_FINAL-BEZEI = WA_TVKBT-BEZEI.
ENDIF.
CLEAR : LV_KUNNR,LV_ADRNR,LV_NAME1,LV_MC_CITY1,LV_P_NAME1.
SELECT SINGLE KUNNR ADRNR INTO (LV_KUNNR,LV_ADRNR) FROM VBPA WHERE PARVW = 'WE' AND VBELN = WA_FINAL-AUBEL.
IF SY-SUBRC = 0.
WA_FINAL-KUNNR = LV_KUNNR.
SELECT SINGLE NAME1 MC_CITY1 INTO (LV_NAME1,LV_MC_CITY1) FROM ADRC WHERE ADDRNUMBER = LV_ADRNR.
IF SY-SUBRC = 0.
WA_FINAL-NAME1 = LV_NAME1.
WA_FINAL-MC_CITY1 = LV_MC_CITY1.
ENDIF.
ENDIF.
SELECT SINGLE NAME1 INTO (LV_P_NAME1) FROM T001W WHERE WERKS = WA_FINAL-WERKS.
IF SY-SUBRC = 0.
WA_FINAL-P_NAME1 = LV_P_NAME1.
ENDIF.
CONCATENATE WA_FINAL-FKDAT+0(4) '' INTO WA_FINAL-YRR.
CONCATENATE WA_FINAL-FKDAT+4(2) '' INTO WA_FINAL-MTH.
MODIFY IT_FINAL FROM WA_FINAL.
ENDLOOP.
PERFORM FIELD_CATLOG.
IF SY-BATCH = 'X'.
PERFORM CONVERT_TO_EXCEL.
PERFORM UPLOAD_ON_FTP.
ELSE.
PERFORM DISPLAY.
ENDIF.
ENDIF.
*&---------------------------------------------------------------------*
*& Form FIELD_CATLOG
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FIELD_CATLOG .
DATA : WFTAB LIKE LINE OF FTAB.
REFRESH FTAB.
DEFINE FCAT.
wftab-fieldname = &1.
wftab-seltext_m = &2.
wftab-COL_POS = &3.
wftab-DO_SUM = &4.
* wftab-hotspot = &3.
APPEND wftab to ftab.
clear wftab.
END-OF-DEFINITION.
FCAT:
'KUNRG' 'CUSTOMER' '1' '',
'AUBEL ' 'SALESORDERNO' '1' '',
'AUDAT' 'SALESORDERDATE' '2' '',
'VBELN' 'SALES INVOICE' '3' '',
'VBTYP' 'VBTYP' '4' '',
'DDTEXT' 'Invoice Type' '4' '',
'FKIMG' 'INV.QTY' '5' '',
'KDGRP' 'Channel code' '6' '',
'KTEXT' 'Channel' '6' '',
'VKBUR' 'Organization Level C' '7' '',
'BEZEI' 'Organization Level Value Name' '7' '',
'MATNR' 'Product Level Code' '8' '',
'ARKTX' 'Product Level Value Name' '9' '',
'FKDAT' 'Invoice date' '10' '',
'PRSDT' 'Pricing date' '11' '',
'YRR' 'YEAR' '11' '',
'MTH' 'MONTH' '12' '',
'VRKME' 'Unit' '12' '',
'NETWR' 'Net Value' '12' '',
'MWSBP' 'Tax value' '13' '',
'VKORG' 'Sales org' '14' '',
'VTWEG' 'Dist.channel' '15' '',
'SPART' 'Division' '16' '',
'NAME1' 'Ship to party name' '17' '',
'KUNNR' 'Ship to party' '18' '',
'MC_CITY1' 'Ship to city' '19' '',
'WERKS' 'Plant code' '20' '',
'P_NAME1' 'Plant name' '21' ''.
LAYOUT-ZEBRA = 'X'.
LAYOUT-COLWIDTH_OPTIMIZE = 'X'.
LAYOUT-INFO_FIELDNAME = 'COLOR'.
REPID = SY-REPID.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DISPLAY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM DISPLAY .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = REPID
IS_LAYOUT = LAYOUT
IT_FIELDCAT = FTAB
I_SAVE = 'X'
TABLES
T_OUTTAB = IT_FINAL
EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CONVERT_TO_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM CONVERT_TO_EXCEL .
GET REFERENCE OF IT_FINAL INTO DATA(LU_DATA).
DATA(LV_XSTRING) = NEW ZCL_ITAB_TO_EXCEL( )->ITAB_TO_XSTRING( LU_DATA ).
************************************** Xstring to binary
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
BUFFER = LV_XSTRING
IMPORTING
OUTPUT_LENGTH = G_SIZE
TABLES
BINARY_TAB = IT_BINARY_CONTENT.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form UPLOAD_ON_FTP
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM UPLOAD_ON_FTP .
*&-------------------------------------------------------&*
*& FTP_CONNECTION
*&-------------------------------------------------------&*
GV_SLEN = STRLEN( GV_PWD ).
CALL FUNCTION 'HTTP_SCRAMBLE'
EXPORTING
SOURCE = GV_PWD
SOURCELEN = GV_SLEN
KEY = GV_KEY
IMPORTING
DESTINATION = GV_PWD1.
CALL FUNCTION 'FTP_CONNECT'
EXPORTING
USER = GV_USER
PASSWORD = GV_PWD1
HOST = GV_HOST
RFC_DESTINATION = C_DEST
IMPORTING
HANDLE = W_HDL
EXCEPTIONS
NOT_CONNECTED = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
*Message will arise in case of any issues in connecting to the FTP server.
MESSAGE 'FTP is not connected' TYPE 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL FUNCTION 'FTP_R3_TO_SERVER'
EXPORTING
HANDLE = W_HDL
FNAME = C_SAP
BLOB_LENGTH = G_SIZE
* CHARACTER_MODE = CHARACTER_MODE
TABLES
BLOB = IT_BINARY_CONTENT
* TEXT = IT_BINARY_CONTENT
EXCEPTIONS
TCPIP_ERROR = 1
COMMAND_ERROR = 2
DATA_ERROR = 3
OTHERS = 4.
IF SY-SUBRC = 0.
MESSAGE 'File Uploaded Successfully' TYPE 'S'.
ELSE.
MESSAGE 'Uploaded not done' TYPE 'E'.
ENDIF.
CALL FUNCTION 'FTP_DISCONNECT'
EXPORTING
HANDLE = W_HDL.
CALL FUNCTION 'RFC_CONNECTION_CLOSE'
EXPORTING
DESTINATION = V_DEST
EXCEPTIONS
OTHERS = 1.
ENDFORM.
*& Form FIELD_CATLOG
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM FIELD_CATLOG .
DATA : WFTAB LIKE LINE OF FTAB.
REFRESH FTAB.
DEFINE FCAT.
wftab-fieldname = &1.
wftab-seltext_m = &2.
wftab-COL_POS = &3.
wftab-DO_SUM = &4.
* wftab-hotspot = &3.
APPEND wftab to ftab.
clear wftab.
END-OF-DEFINITION.
FCAT:
'KUNRG' 'CUSTOMER' '1' '',
'AUBEL ' 'SALESORDERNO' '1' '',
'AUDAT' 'SALESORDERDATE' '2' '',
'VBELN' 'SALES INVOICE' '3' '',
'VBTYP' 'VBTYP' '4' '',
'DDTEXT' 'Invoice Type' '4' '',
'FKIMG' 'INV.QTY' '5' '',
'KDGRP' 'Channel code' '6' '',
'KTEXT' 'Channel' '6' '',
'VKBUR' 'Organization Level C' '7' '',
'BEZEI' 'Organization Level Value Name' '7' '',
'MATNR' 'Product Level Code' '8' '',
'ARKTX' 'Product Level Value Name' '9' '',
'FKDAT' 'Invoice date' '10' '',
'PRSDT' 'Pricing date' '11' '',
'YRR' 'YEAR' '11' '',
'MTH' 'MONTH' '12' '',
'VRKME' 'Unit' '12' '',
'NETWR' 'Net Value' '12' '',
'MWSBP' 'Tax value' '13' '',
'VKORG' 'Sales org' '14' '',
'VTWEG' 'Dist.channel' '15' '',
'SPART' 'Division' '16' '',
'NAME1' 'Ship to party name' '17' '',
'KUNNR' 'Ship to party' '18' '',
'MC_CITY1' 'Ship to city' '19' '',
'WERKS' 'Plant code' '20' '',
'P_NAME1' 'Plant name' '21' ''.
LAYOUT-ZEBRA = 'X'.
LAYOUT-COLWIDTH_OPTIMIZE = 'X'.
LAYOUT-INFO_FIELDNAME = 'COLOR'.
REPID = SY-REPID.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form DISPLAY
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM DISPLAY .
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
I_CALLBACK_PROGRAM = REPID
IS_LAYOUT = LAYOUT
IT_FIELDCAT = FTAB
I_SAVE = 'X'
TABLES
T_OUTTAB = IT_FINAL
EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form CONVERT_TO_EXCEL
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM CONVERT_TO_EXCEL .
GET REFERENCE OF IT_FINAL INTO DATA(LU_DATA).
DATA(LV_XSTRING) = NEW ZCL_ITAB_TO_EXCEL( )->ITAB_TO_XSTRING( LU_DATA ).
************************************** Xstring to binary
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
BUFFER = LV_XSTRING
IMPORTING
OUTPUT_LENGTH = G_SIZE
TABLES
BINARY_TAB = IT_BINARY_CONTENT.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form UPLOAD_ON_FTP
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM UPLOAD_ON_FTP .
*&-------------------------------------------------------&*
*& FTP_CONNECTION
*&-------------------------------------------------------&*
GV_SLEN = STRLEN( GV_PWD ).
CALL FUNCTION 'HTTP_SCRAMBLE'
EXPORTING
SOURCE = GV_PWD
SOURCELEN = GV_SLEN
KEY = GV_KEY
IMPORTING
DESTINATION = GV_PWD1.
CALL FUNCTION 'FTP_CONNECT'
EXPORTING
USER = GV_USER
PASSWORD = GV_PWD1
HOST = GV_HOST
RFC_DESTINATION = C_DEST
IMPORTING
HANDLE = W_HDL
EXCEPTIONS
NOT_CONNECTED = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
*Message will arise in case of any issues in connecting to the FTP server.
MESSAGE 'FTP is not connected' TYPE 'I'.
LEAVE LIST-PROCESSING.
ENDIF.
CALL FUNCTION 'FTP_R3_TO_SERVER'
EXPORTING
HANDLE = W_HDL
FNAME = C_SAP
BLOB_LENGTH = G_SIZE
* CHARACTER_MODE = CHARACTER_MODE
TABLES
BLOB = IT_BINARY_CONTENT
* TEXT = IT_BINARY_CONTENT
EXCEPTIONS
TCPIP_ERROR = 1
COMMAND_ERROR = 2
DATA_ERROR = 3
OTHERS = 4.
IF SY-SUBRC = 0.
MESSAGE 'File Uploaded Successfully' TYPE 'S'.
ELSE.
MESSAGE 'Uploaded not done' TYPE 'E'.
ENDIF.
CALL FUNCTION 'FTP_DISCONNECT'
EXPORTING
HANDLE = W_HDL.
CALL FUNCTION 'RFC_CONNECTION_CLOSE'
EXPORTING
DESTINATION = V_DEST
EXCEPTIONS
OTHERS = 1.
ENDFORM.
No comments:
Post a Comment