*&---------------------------------------------------------------------*
*& Report Z_AP_HERO_BKPF
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&
*& Developed By - Vivek Nigam
*& Date - 08/11/2024 8:00 AM
*& Date - 08/11/2024 Modified By -- David Bassey 08/11/2024
*& Mdified 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
*THIS CODE WAS UPDATED WITH THE HELP OF Mohd FAKRUDDIN (EXT) <mohd.fakruddin@chanel.com> dATED 02/12/2024 @ 11:29 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 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
**BELNR XBLNR BKTXT - BKPF
REPORT z_ap_hero_bkpf.
TYPE-POOLS: vrm.
TYPE-POOLS: truxs.
DATA: BEGIN OF itab OCCURS 0,
belnr LIKE bkpf-belnr,
xblnr LIKE bkpf-xblnr,
bktxt LIKE bkpf-bktxt,
lifnr LIKE bsik-lifnr,
shkzg LIKE bsik-shkzg,
wrbtr LIKE bsik-wrbtr,
END OF itab.
TYPES : BEGIN OF itab_final,
belnr LIKE bkpf-belnr,
xblnr LIKE bkpf-xblnr,
bktxt LIKE bkpf-bktxt,
END OF itab_final.
DATA: itab1 TYPE truxs_t_text_data,
it_final TYPE TABLE OF itab_final,
ls_final TYPE itab_final,
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\BKPF.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
bkpf~belnr
bkpf~xblnr
bkpf~bktxt
bsik~lifnr
bsik~shkzg
bsik~wrbtr
FROM bkpf
INNER JOIN bsik
ON bkpf~belnr EQ bsik~belnr
INTO TABLE itab
WHERE bkpf~bukrs = entity.
lv_filename = opath.
lv_header = 'BELNR;XBLNR;BKTXT'.
**BELNR XBLNR BKTXT
* 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.
*Added By MF - With VN Dated On 16/04/2025 @ 13:30
LOOP AT itab_sum.
CLEAR ls_final.
ls_final-belnr = itab_sum-belnr.
ls_final-xblnr = itab_sum-xblnr.
ls_final-bktxt = itab_sum-bktxt.
APPEND ls_final TO it_final.
ENDLOOP.
SORT it_final BY belnr.
DELETE ADJACENT DUPLICATES FROM it_final COMPARING ALL FIELDS.
*Up to Here Added By MF - With VN Dated On 16/04/2025 @ 13:30
CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
EXPORTING
i_field_seperator = ','
TABLES
* i_tab_sap_data = itab_sum
i_tab_sap_data = it_final
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