*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&
*& Developed By - Vivek Nigam
*& Date - 08/11/2024 8:00 AM
*& Modified Date - 27/11/2024 Modified By -- Vivek Nigam
*& Modified Date 27/11/2024 Modified By -- Vivek Nigam
*& LIFNR BELNR BLDAT SGTXT SHKZG WRBTR ZFBDT WAERS HKONT
*FILE NAME - BSIK.csv - output file name
*&**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 Extension 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 By VN Dated 18/03/2025 @ 10:26 AM To add ISSUE 1478226 - ETL4 - AP Open Balance (UK75/0087) - Missing transaction
*https://dev.azure.com/lpl-sources/D365%20HERO/_workitems/edit/1478226
*ISSUE 1478226 - ETL4 - AP Open Balance (UK75/0087) - Missing transaction
**LIFNR BELNR BLDAT SGTXT SHKZG WRBTR ZFBDT WAERS HKONT DMBTR BUZEI XBLNR ZLSPR
***Issue 1495858 - ETL4 AP Open Balances - Payment Block / Supplier Invoice Number
***https://dev.azure.com/lpl-sources/D365%20HERO/_workitems/edit/1495858?src=WorkItemMention&src-action=artifact_link
**Worked On Issue 1495858 - ETL4 AP Open Balances - Payment Block / Supplier Invoice Number
***Dated 01/04/2025 @ 09:55 AM
**LIFNR BELNR BLDAT SGTXT SHKZG WRBTR ZFBDT WAERS HKONT DMBTR BUZEI XBLNR ZLSPR
*Added New Filed By Vivek Nigam Dated 10/04/2025 @14:01
*LIFNR BELNR BLDAT SGTXT SHKZG WRBTR ZFBDT WAERS HKONT DMBTR BUZEI XBLNR ZLSPR ZTERM
REPORT Z_AP_HERO_BSIK.
TYPE-POOLS: vrm.
TYPE-POOLS: truxs.
DATA: BEGIN OF itab OCCURS 0,
LIFNR LIKE BSIK-LIFNR,
BELNR LIKE BSIK-BELNR,
BLDAT LIKE BSIK-BLDAT,
SGTXT LIKE BSIK-SGTXT,
SHKZG LIKE BSIK-SHKZG,
WRBTR LIKE BSIK-WRBTR,
ZFBDT LIKE BSIK-ZFBDT,
WAERS LIKE BSIK-WAERS,
HKONT LIKE BSIK-HKONT,
BUKRS LIKE BSIK-DMBTR,
BUZEI LIKE BSIK-BUZEI,
XBLNR LIKE BSIK-XBLNR,
ZLSPR LIKE BSIK-ZLSPR,
ZTERM LIKE BSIK-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\BSIK.csv'.
PARAMETERS : OPATH TYPE string LOWER CASE DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\BSIK.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 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.
SELECT DISTINCT
BSIK~LIFNR
BSIK~BELNR
BSIK~BLDAT
BSIK~SGTXT
BSIK~SHKZG
BSIK~WRBTR
BSIK~ZFBDT
BSIK~WAERS
BSIK~HKONT
BSIK~DMBTR
BSIK~BUZEI
BSIK~XBLNR
BSIK~ZLSPR
BSIK~ZTERM
FROM BSIK
INTO TABLE itab
WHERE BSIK~BUKRS = ENTITY.
lv_filename = OPATH.
lv_header = 'LIFNR;BELNR;BLDAT;SGTXT;SHKZG;WRBTR;ZFBDT;WAERS;HKONT;DMBTR;BUZEI;XBLNR;ZLSPR;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 LIFNR.
*LOOP AT itab.
LOOP AT itab_t.
CLEAR sum_wrbtr.
LOOP AT itab WHERE LIFNR = itab_t-LIFNR.
"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 Added By VN On 12/12/2024 @09:21
* * 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 To Fix Comma Issue In Amount Value
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\BSIK.csv'.
*PARAMETERS : OPATH TYPE STRING DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\BSIK.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.
*
*TYPE-POOLS: TRUXS.
*DATA: BEGIN OF itab OCCURS 0,
* LIFNR LIKE BSIK-LIFNR,
* BELNR LIKE BSIK-BELNR,
* BLDAT LIKE BSIK-BLDAT,
* SGTXT LIKE BSIK-SGTXT,
* SHKZG LIKE BSIK-SHKZG,
* WRBTR LIKE BSIK-WRBTR,
* ZFBDT LIKE BSIK-ZFBDT,
* WAERS LIKE BSIK-WAERS,
* HKONT LIKE BSIK-HKONT,
* BUKRS LIKE BSIK-DMBTR,
* END OF itab.
*
*DATA: itab1 TYPE TRUXS_T_TEXT_DATA,
* itab_sum LIKE TABLE OF itab WITH HEADER LINE,
* sum_wrbtr TYPE WRBTR.
*
*
*SELECT BSIK~LIFNR
* BSIK~BELNR
* BSIK~BLDAT
* BSIK~SGTXT
* BSIK~SHKZG
* BSIK~WRBTR
* BSIK~ZFBDT
* BSIK~WAERS
* BSIK~HKONT
* BSIK~DMBTR
* FROM BSIK
* INTO TABLE itab
* WHERE BSIK~BUKRS = ENTITY.
*
*LOOP AT itab.
* CLEAR sum_wrbtr.
* LOOP AT itab WHERE LIFNR = itab-LIFNR. "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.
* ENDLOOP.
* IF sum_wrbtr <> 0.
* APPEND itab TO itab_sum.
* ENDIF.
*ENDLOOP.
*
*DATA: lt_output TYPE TABLE OF string,
* lv_header TYPE string,
* lv_line TYPE string,
* lv_filename TYPE string.
*
*lv_filename = opath.
*
*lv_header = 'LIFNR,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.
*
*
*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