*&---------------------------------------------------------------------*
*& Report Z_GLT0_HERO_GL
*&*&---------------------------------------------------------------------*
*& Extract SAP Data For GL GL SAP Data into CSV File GLT0_GL.csv-- FOR ETL 2
*&---------------------------------------------------------------------*
*& Developed By - Vivek Nigam
*& Date - 01/08/2024
*& Developed By - Vivek Nigam
*& Date - 06/08/2024
*&----------https://lpl.sharepoint.com/:x:/r/sites/HEROProgramme-02.Pilot-UK/_layouts/15/Doc.aspx?sourcedoc=%7BE524D52A-40A7-47BB-9FEB-1CEE34F18694%7D&file=02.%20HERO_design_source_schemas_GL_Balances_general_journal.xlsx&action=default&mobileredirect=tru
*&MODIFIED BY VIVEK NIGAM DATED 17/11/2024 @20:48 PM
*&RACCT RPMAX RACCT RYEAR RPMAX RTCUR DRCRK TSLVT TSL01 TSL02 TSL03 TSL04 TSL05 TSL06 TSL07
*&TSL08 TSL09 TSL10 TSL11 TSL12 TSL13 TSL14 TSL15 TSL16
*& RACCT RPMAX RACCT RYEAR RPMAX RTCUR DRCRK TSLVT TSL01 TSL02 TSL03 TSL04 TSL05 TSL06 TSL07 TSL08 TSL09 TSL10 TSL11 TSL12 TSL13 TSL14 TSL15 TSL16
*RACCT RYEAR RTCUR DRCRK TSLVT HSLVT TSL01 HSL01 TSL02 HSL02 TSL03 HSL03 TSL04 HSL04 TSL05 HSL05 TSL06 HSL06 TSL07 HSL07 TSL08 HSL08 TSL09 HSL09 TSL10 HSL10 TSL11 HSL11 TSL12 HSL12 TSL13 HSL13 TSL14 HSL14 TSL15 HSL15 TSL16 HSL16
**https://lpl.sharepoint.com/:x:/r/sites/HEROProgramme-02.Pilot-UK/_layouts/15/Doc.aspx?sourcedoc=%7BE524D52A-40A7-47BB-9FEB-1CEE34F18694%7D&file=02.%20HERO_design_source_schemas_GL_Balances_general_journal.xlsx&action=default&mobileredirect=true
**Created new Transtaion Code To Fix the Pervious Transport Issue- Created New Transtaion Code
*Added By Vivek Nigam Dated 02-12-2024 @ 11:09 AM
*&---------------------------------------------------------------------*
*Updated Again Dated On 09/12/2024 @ 13:45 To Fix By default SAP stores the '-' ve sign at the end of the value. And Format Issue Curreny Issue
*&---------------------------------------------------------------------*
REPORT Z_GLT0_HERO_GL.
TYPE-POOLS: VRM.
DATA: NAME TYPE VRM_ID,
LIST TYPE VRM_VALUES,
VALUE LIKE LINE OF LIST.
PARAMETERS: ENTITY(10) AS LISTBOX VISIBLE LENGTH 10.
*&--PARAMETERS : OPPATH LIKE RLGRAP-FILENAME DEFAULT 'C:\tmp\TT\GLT0_GL.csv'.
*PARAMETERS : OPATH TYPE STRING DEFAULT '\\UKCROAPTST\tmp\Interfaces\HFT\SAP_Hero_Prod\Raw\IN\UK\UK01\GLT0_GL.csv'.
*PARAMETERS : OPATH TYPE STRING DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\GLT0_GL.csv'.
PARAMETERS : OPATH TYPE STRING LOWER CASE DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\GLT0_GL.csv'.
AT SELECTION-SCREEN OUTPUT.
NAME = 'ENTITY'.
VALUE-KEY = 'UK01'.
VALUE-TEXT = 'UK01'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK75'.
VALUE-TEXT = 'UK75'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK21'.
VALUE-TEXT = 'UK21'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK31'.
VALUE-TEXT = 'UK31'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK41'.
VALUE-TEXT = 'UK41'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK02'.
VALUE-TEXT = 'UK02'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK73'.
VALUE-TEXT = 'UK73'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK04'.
VALUE-TEXT = 'UK04'.
APPEND VALUE TO LIST.
VALUE-KEY = 'UK61'.
VALUE-TEXT = 'UK61'.
APPEND VALUE TO LIST.
CALL FUNCTION 'VRM_SET_VALUES'
EXPORTING
ID = NAME
VALUES = LIST.
*&------PARAMETERS p_lname TYPE char5.
*&------PARAMETERS p_oppath TYPE char50.
*&------AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_lname.
*&------AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_oppath.
*&------AT SELECTION-SCREEN ON VALUE-REQUEST FOR OPPATH.
START-OF-SELECTION.
*&------WRITE: / 'LEGAL ENTITY NAME:', ENTITY.
*&------WRITE: / 'OUTPUT FILE PATH:', OPATH.
type-pools:TRUXS.
data: begin of itab occurs 0,
RACCT like GLT0-RACCT,
RYEAR like GLT0-RYEAR,
* *&------ RPMAX like GLT0-RPMAX,
* *&------RPMAX like GLT0-RPMAX,
RTCUR like GLT0-RTCUR,
DRCRK like GLT0-DRCRK,
TSLVT like GLT0-TSLVT,
HSLVT like GLT0-HSLVT,
TSL01 like GLT0-TSL01,
HSL01 like GLT0-HSL01,
TSL02 like GLT0-TSL02,
HSL02 like GLT0-HSL02,
TSL03 like GLT0-TSL03,
HSL03 like GLT0-HSL03,
TSL04 like GLT0-TSL04,
HSL04 like GLT0-HSL04,
TSL05 like GLT0-TSL05,
HSL05 like GLT0-HSL05,
TSL06 like GLT0-TSL06,
HSL06 like GLT0-HSL06,
TSL07 like GLT0-TSL07,
HSL07 like GLT0-HSL07,
TSL08 like GLT0-TSL08,
HSL08 like GLT0-HSL08,
TSL09 like GLT0-TSL09,
HSL09 like GLT0-HSL09,
TSL10 like GLT0-TSL10,
HSL10 like GLT0-HSL10,
TSL11 like GLT0-TSL11,
HSL11 like GLT0-HSL11,
TSL12 like GLT0-TSL12,
HSL12 like GLT0-HSL12,
TSL13 like GLT0-TSL13,
HSL13 like GLT0-HSL13,
TSL14 like GLT0-TSL14,
HSL14 like GLT0-HSL14,
TSL15 like GLT0-TSL15,
HSL15 like GLT0-HSL15,
TSL16 like GLT0-TSL16,
HSL16 like GLT0-HSL16,
* TSL01 like GLT0-TSL01,
* TSL02 like GLT0-TSL02,
*
* TSL03 like GLT0-TSL03,
* TSL04 like GLT0-TSL04,
* TSL05 like GLT0-TSL05,
* TSL06 like GLT0-TSL06,
*
* TSL07 like GLT0-TSL07,
* TSL08 like GLT0-TSL08,
* TSL09 like GLT0-TSL09,
* TSL10 like GLT0-TSL10,
*
* TSL11 like GLT0-TSL11,
* TSL12 like GLT0-TSL12,
* TSL13 like GLT0-TSL13,
* TSL14 like GLT0-TSL14,
*
* TSL15 like GLT0-TSL15,
* TSL16 like GLT0-TSL16,
* BUKRS like GLT0-BUKRS,
end of itab.
*"header data
* itab-RACCT = 'RACCT'.
* itab-RYEAR = 'RYEAR'.
* itab-RTCUR = 'RTCUR'.
* itab-DRCRK = 'DRCRK'.
* itab-TSLVT = 'TSLVT'.
* itab-HSLVT = 'HSLVT'.
* itab-TSL01 = 'TSL01'.
* itab-HSL01 = 'HSL01'.
* itab-TSL02 = 'TSL02'.
* itab-HSL02 = 'HSL02'.
* itab-TSL03 = 'TSL03'.
* itab-HSL03 = 'HSL03'.
* itab-TSL04 = 'TSL04'.
* itab-HSL04 = 'HSL04'.
* itab-TSL05 = 'TSL05'.
* itab-HSL05 = 'HSL05'.
* itab-TSL06 = 'TSL06'.
* itab-HSL06 = 'HSL06'.
* itab-TSL07 = 'TSL07'.
* itab-HSL07 = 'HSL07'.
* itab-TSL08 = 'TSL08'.
* itab-HSL08 = 'HSL08'.
* itab-TSL09 = 'TSL09'.
* itab-HSL09 = 'HSL09'.
* itab-TSL10 = 'TSL10'.
* itab-HSL10 = 'HSL10'.
* itab-TSL11 = 'TSL11'.
* itab-HSL11 = 'HSL11'.
* itab-TSL12 = 'TSL12'.
* itab-HSL12 = 'HSL12'.
* itab-TSL13 = 'TSL13'.
* itab-HSL13 = 'HSL13'.
* itab-TSL14 = 'TSL14'.
* itab-HSL14 = 'HSL14'.
* itab-TSL15 = 'TSL15'.
* itab-HSL15 = 'HSL15'.
* itab-TSL16 = 'TSL16'.
* itab-HSL16 = 'HSL16'.
*itab-BUKRS = 'BUKRS'.
*Insert itab index 1.
data: itab1 type TRUXS_T_TEXT_DATA.
* lv_HSL01 like GLT0-HSL01.
SELECT DISTINCT
GLT0~RACCT
* GLT0~RPMAX
GLT0~RYEAR
* GLT0~RPMAX
GLT0~RTCUR
GLT0~DRCRK
GLT0~TSLVT
GLT0~HSLVT
GLT0~TSL01
GLT0~HSL01
GLT0~TSL02
GLT0~HSL02
GLT0~TSL03
GLT0~HSL03
GLT0~TSL04
GLT0~HSL04
GLT0~TSL05
GLT0~HSL05
GLT0~TSL06
GLT0~HSL06
GLT0~TSL07
GLT0~HSL07
GLT0~TSL08
GLT0~HSL08
GLT0~TSL09
GLT0~HSL09
GLT0~TSL10
GLT0~HSL10
GLT0~TSL11
GLT0~HSL11
GLT0~TSL12
GLT0~HSL12
GLT0~TSL13
GLT0~HSL13
GLT0~TSL14
GLT0~HSL14
GLT0~TSL15
GLT0~HSL15
GLT0~TSL16
GLT0~HSL16
* GLT0~TSL01
* GLT0~TSL02
* GLT0~TSL03
* GLT0~TSL04
*
* GLT0~TSL05
* GLT0~TSL06
* GLT0~TSL07
* GLT0~TSL08
*
* GLT0~TSL09
* GLT0~TSL10
* GLT0~TSL11
* GLT0~TSL12
*
* GLT0~TSL13
* GLT0~TSL14
* GLT0~TSL15
* GLT0~TSL16
* GLT0~BUKRS
from GLT0
into table itab
WHERE GLT0~RYEAR IN (sy-datum(4)) AND GLT0~BUKRS = ENTITY.
DATA: lt_output TYPE TABLE OF string,
lv_header TYPE string,
lv_line TYPE string,
lv_filename TYPE string.
lv_filename = opath.
lv_header = 'RACCT;RYEAR;RTCUR;DRCRK;TSLVT;HSLVT;TSL01;HSL01;TSL02;HSL02;TSL03;HSL03;TSL04;HSL04;TSL05;HSL05;TSL06;HSL06;TSL07;HSL07;TSL08;HSL08;TSL09;HSL09;TSL10;HSL10;TSL11;HSL11;TSL12;HSL12;TSL13;HSL13;TSL14;HSL14;TSL15;HSL15;TSL16;HSL16'.
* Add the header to the output table
APPEND lv_header TO lt_output.
*CLOSE DATASET lv_filename.
* By default SAP stores the '-' ve sign at the end of the value.
* CALL FUNCTION 'CLOI_PUT_SIGN_IN_FRONT'
* CHANGING
* VALUE = itab1-tSL02.
CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
EXPORTING
I_FIELD_SEPERATOR = ','
TABLES
I_TAB_SAP_DATA = itab
CHANGING
I_TAB_CONVERTED_DATA = itab1
EXCEPTIONS
CONVERSION_FAILED = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
* APPEND LINES OF itab1 To lt_output.
DATA:lt_output1 TYPE TABLE OF string, " Output table for corrected rows
lv_row TYPE string, " A single row from itab1
lt_columns TYPE TABLE OF string, " Split columns from the row
lv_column TYPE string. " A single column value
" Process each row in itab1
LOOP AT itab1 INTO lv_row.
" Split the row into columns using semicolon as a delimiter
SPLIT lv_row AT ';' INTO TABLE lt_columns.
" Process each column in the row
LOOP AT lt_columns INTO lv_column.
" Step 1: Remove thousand separators (period)
REPLACE ALL OCCURRENCES OF '.' IN lv_column WITH ''.
" Step 2: Replace decimal separator (comma) with a period
REPLACE ALL OCCURRENCES OF ',' IN lv_column WITH '.'.
" Step 3: Move negative sign to the front
IF lv_column CP '*-*'.
SHIFT lv_column RIGHT DELETING TRAILING '-'.
CONCATENATE '-' lv_column INTO lv_column.
CONDENSE lv_column NO-GAPS.
ENDIF.
" Update the column value in lt_columns
MODIFY lt_columns FROM lv_column INDEX sy-tabix.
ENDLOOP.
" Reassemble the processed columns into a corrected row
CLEAR lv_row.
LOOP AT lt_columns INTO lv_column.
IF lv_row IS INITIAL.
lv_row = lv_column.
ELSE.
CONCATENATE lv_row lv_column INTO lv_row SEPARATED BY ';'.
ENDIF.
ENDLOOP.
" Add the corrected row to the output table
APPEND lv_row TO lt_output1.
ENDLOOP.
APPEND LINES OF lt_output1 TO lt_output.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = OPATH
CODEPAGE = '4110' "4310 codepage 4310 encodes as UTF-8.
TABLES
data_tab = lt_output
EXCEPTIONS
OTHERS = 1.
No comments:
Post a Comment