*&---------------------------------------------------------------------*
*&
*& Developed By - Vivek Nigam / David Bassey
*& Date - 27/11/2024 10:47 AM
* Modified By Vivek Nigam Dated - 27/11/2024
*& KUNNR BELNR BLDAT SGTXT SHKZG WRBTR ZFBDT WAERS HKONT
*FILE NAME - BSID.csv - output file name
*& Modified By VN Dated 29/11/2024 with the help of Mohd F.
**Created new Transaction Code To Fix the Pervious Transport Issue- Created New Transaction Code
*Added By Vivek Nigam Dated 02-12-2024 @ 11:09 AM
*Modified By Vivek Nigam Dated On 05/12/2024 @ 09:48 AM To Fix Lover Case File Extention Issue & UTF-8 Encoding: 4110
*&---------------------------------------------------------------------
**Updated By VN Dated 12/12/2024 @ 09:17 AM To Fix fields DMBTR / WRBTR : the decimal comma , separator must be converted in dot . as we have always done in the past :
*Updated Program On 19/03/2025 @11:19 AM For https://dev.azure.com/lpl-sources/D365%20HERO/_workitems/edit/1483837?src=WorkItemMention&src-action=artifact_link
*https://dev.azure.com/lpl-sources/D365%20HERO/_workitems/edit/1483837?src=WorkItemMention&src-action=artifact_link
*ISSUE 1483837 - ETL4 - AR Open Balance (UK01/C001) - Missing Transactions
*Updated By Vivek Nigam Dated - 10/04/2025 - Added New Field
*KUNNR BELNR BLDAT SGTXT SHKZG WRBTR ZFBDT WAERS HKONT DMBTR BUZEI ZTERM
REPORT Z_AR_HERO_BSID.
TYPE-POOLS: vrm.
TYPE-POOLS: truxs.
DATA: BEGIN OF itab OCCURS 0,
kunnr LIKE bsid-kunnr,
belnr LIKE bsid-belnr,
bldat LIKE bsid-bldat,
sgtxt LIKE bsid-sgtxt,
shkzg LIKE bsid-shkzg,
wrbtr LIKE bsid-wrbtr,
zfbdt LIKE bsid-zfbdt,
waers LIKE bsid-waers,
hkont LIKE bsid-hkont,
bukrs LIKE bsid-dmbtr,
BUZEI LIKE bsid-BUZEI,
ZTERM LIKE bsid-ZTERM,
END OF itab.
DATA: itab1 TYPE truxs_t_text_data,
itab_sum LIKE TABLE OF itab WITH HEADER LINE,
itab_t LIKE TABLE OF itab WITH HEADER LINE,
itab_l LIKE TABLE OF itab WITH HEADER LINE,
itab_f LIKE TABLE OF itab,"" WITH HEADER LINE,
sum_wrbtr TYPE wrbtr.
DATA: name TYPE vrm_id,
list TYPE vrm_values,
value LIKE LINE OF list.
DATA: lt_output TYPE TABLE OF string,
lv_header TYPE string,
lv_line TYPE string,
lv_filename TYPE string.
PARAMETERS: entity(10) AS LISTBOX VISIBLE LENGTH 10.
*PARAMETERS : OPATH TYPE STRING DEFAULT '\\UKCROAPTST\tmp\Interfaces\HFT\SAP_Hero_Prod\Raw\IN\UK\UK01\BSID.csv'.
PARAMETERS : OPATH TYPE string LOWER CASE DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\BSID.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.
START-OF-SELECTION.
SELECT DISTINCT
bsid~kunnr
bsid~belnr
bsid~bldat
bsid~sgtxt
bsid~shkzg
bsid~wrbtr
bsid~zfbdt
bsid~waers
bsid~hkont
bsid~dmbtr
bsid~BUZEI
bsid~ZTERM
FROM bsid
INTO TABLE itab
WHERE bsid~bukrs = entity.
lv_filename = OPATH.
***KUNNR BELNR BLDAT SGTXT SHKZG WRBTR ZFBDT WAERS HKONT DMBTR
lv_header = 'KUNNR;BELNR;BLDAT;SGTXT;SHKZG;WRBTR;ZFBDT;WAERS;HKONT;DMBTR;BUZEI;ZTERM'.
* Add the header to the output table
APPEND lv_header TO lt_output.
*CLOSE DATASET lv_filename.
***Delete duplicate entries
itab_t[] = itab[].
DELETE ADJACENT DUPLICATES FROM itab_t COMPARING kunnr.
*LOOP AT itab.
LOOP AT itab_t.
CLEAR sum_wrbtr.
LOOP AT itab WHERE kunnr = itab_t-kunnr.
"AND BELNR = itab-BELNR.
IF itab-shkzg = 'S'. " Debit
sum_wrbtr = sum_wrbtr + itab-wrbtr.
ELSEIF itab-shkzg = 'H'. " Credit
sum_wrbtr = sum_wrbtr - itab-wrbtr.
ENDIF.
APPEND itab TO itab_l.
ENDLOOP.
IF sum_wrbtr <> 0.
* APPEND itab TO itab_sum.
APPEND LINES OF itab_l[] TO itab_sum[].
REFRESH itab_l.
ELSEIF sum_wrbtr = 0.
REFRESH itab_l.
ENDIF.
ENDLOOP.
CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
EXPORTING
i_field_seperator = ','
TABLES
i_tab_sap_data = itab_sum
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.
* Code below Addred By Vivek Nigam To Fix *Updated By VN Dated 12/12/2024 @ 09:17 AM To Fix fields DMBTR / WRBTR : the decimal comma , separator must be converted in dot . as we have always done in the past :
* 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.
*UPto Here The Code Was Added
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = OPATH
CODEPAGE = '4110' "4310 codepage 4310 encodes as UTF-8.
TABLES
* data_tab = itab1
data_tab = lt_output
EXCEPTIONS
OTHERS = 1.
*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 : OPATH TYPE STRING DEFAULT '\\UKCROAPTST\tmp\Interfaces\HFT\SAP_Hero_Prod\Raw\IN\UK\UK01\BSID.csv'.
*PARAMETERS : OPATH TYPE STRING DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\BSID.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.
*
*START-OF-SELECTION.
*
*TYPE-POOLS: TRUXS.
*DATA: BEGIN OF itab OCCURS 0,
* KUNNR LIKE BSID-KUNNR,
* BELNR LIKE BSID-BELNR,
* BLDAT LIKE BSID-BLDAT,
* SGTXT LIKE BSID-SGTXT,
* SHKZG LIKE BSID-SHKZG,
* WRBTR LIKE BSID-WRBTR,
* ZFBDT LIKE BSID-ZFBDT,
* WAERS LIKE BSID-WAERS,
* HKONT LIKE BSID-HKONT,
* BUKRS LIKE BSID-DMBTR,
* END OF itab.
*
**DATA: BEGIN OF csv_data OCCURS 0,
** KUNNR LIKE BSID-KUNNR,
** BELNR LIKE BSID-BELNR,
** BLDAT LIKE BSID-BLDAT,
** SGTXT LIKE BSID-SGTXT,
** SHKZG LIKE BSID-SHKZG,
** WRBTR LIKE BSID-WRBTR,
** ZFBDT LIKE BSID-ZFBDT,
** WAERS LIKE BSID-WAERS,
** HKONT LIKE BSID-HKONT,
** BUKRS LIKE BSID-DMBTR,
** END OF csv_data.
*
**DATA: itab1 TYPE TRUXS_T_TEXT_DATA,
** sum_wrbtr TYPE p DECIMALS 2,
** temp_itab LIKE itab.
*
**DATA: itab1 TYPE TRUXS_T_TEXT_DATA,
** itab_sum LIKE TABLE OF itab WITH HEADER LINE,
** sum_wrbtr TYPE p DECIMALS 2.
*
*DATA: itab1 TYPE TRUXS_T_TEXT_DATA,
* itab_sum LIKE TABLE OF itab WITH HEADER LINE,
* sum_wrbtr TYPE WRBTR.
*
*
*SELECT BSID~KUNNR
* BSID~BELNR
* BSID~BLDAT
* BSID~SGTXT
* BSID~SHKZG
* BSID~WRBTR
* BSID~ZFBDT
* BSID~WAERS
* BSID~HKONT
* BSID~DMBTR
* FROM BSID
* INTO TABLE itab
* WHERE BSID~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 = 'KUNN,BELNR,BLDAT,SGTXT,SHKZG,WRBTR,ZFBDT,WAERS,HKONT,DMBTR'.
** Add the header to the output table
*APPEND lv_header TO lt_output.
*
*
**CLOSE DATASET lv_filename.
*
** AND BELNR = itab-BELNR
*
*LOOP AT itab.
* CLEAR sum_wrbtr.
* LOOP AT itab WHERE KUNNR = itab-KUNNR.
* IF itab-SHKZG = 'S'. " Debit
* sum_wrbtr = sum_wrbtr + itab-WRBTR.
* ELSEIF itab-SHKZG = 'H'. " Credit
* sum_wrbtr = sum_wrbtr - itab-WRBTR.
* ENDIF.
* ENDLOOP.
* IF sum_wrbtr <> 0.
* APPEND itab TO itab_sum.
* ENDIF.
*ENDLOOP.
*
**LOOP AT itab INTO temp_itab.
** CLEAR sum_wrbtr.
** LOOP AT itab WHERE BELNR = temp_itab-BELNR.
** IF itab-SHKZG = 'S'. " Debit
** sum_wrbtr = sum_wrbtr + itab-WRBTR.
** ELSEIF itab-SHKZG = 'H'. " Credit
** sum_wrbtr = sum_wrbtr - itab-WRBTR.
** ENDIF.
** ENDLOOP.
*** IF sum_wrbtr <> 0.
*** APPEND temp_itab TO csv_data.
*** ENDIF.
** IF sum_wrbtr <> 0.
** APPEND itab TO itab_sum.
** ENDIF.
***ENDLOOP.
**ENDLOOP.
*
*CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
* EXPORTING
* I_FIELD_SEPERATOR = ','
* TABLES
* I_TAB_SAP_DATA = itab_sum
* 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.
*
*CALL FUNCTION 'GUI_DOWNLOAD'
* EXPORTING
* filename = OPATH
* TABLES
** data_tab = itab1
*data_tab = lt_output
* EXCEPTIONS
* OTHERS = 1.
*
No comments:
Post a Comment