*&---------------------------------------------------------------------*
*& Report Z_EKET_HERO_PO
*&
*&---------------------------------------------------------------------*
*&*&*& Extract SAP Purchase Order EKET Data from SAP Table To csv file called EKET.csv
*&---------------------------------------------------------------------*
*& Developed By - Vivek Nigam
*& Date - 23/12/2024 @ 14:25 PM
*&---------------------------------------------------------------------*
*& Modified by-Vivek Nigam
*& Date -
*& Modified on
**'- Material type
**- Filter where [Del. Compl] = "No"
**- Extract purchase order only last 18 months
***EKET --- EBELN WEMNG EINDT -- EBELN WEMNG EINDT
**'Include materials that:
**Have a 7-digit reference AND
**Start with a 4 AND
**Do not include # in the description (EKPO.TXZ01) AND
**Have had a transaction in the last 18 months (ie date >= 1st October 2023 ;EKKO.EAEDAT) AND
**Have an open purchase order line item associated with it
**DO NOT INCLUDE purchase order line items that have been deleted [Deletion Indicator in Purchasing Document = 'L' (field LOEKZ in table EKPO)
*&---------------------------------------------------------------------*
****& https://lpl.sharepoint.com/:x:/r/sites/HEROProgramme-02.Pilot-UK/_layouts/15/Doc.aspx?sourcedoc=%7BDD491D0A-7C54-4F85-BF3B-9D026F775FB8%7D&file=03.HERO_design_source_schemas_open_purchase_orders.xlsx&action=default&mobileredirect=true
***EKET -- EBELN WEMNG EINDT
****&Legal Entities UK01
***Rules .CSV
*** UTF-8
*** Delimiter separator ;
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
*&EKKO "- Material type
****- Filter where [Del. Compl] = ""No""
****- Extract purchase order only last 18 months" Include PO headers that have an open PO line item (link between line item and header table is EBELN Purchasing Document Number) TBC
****&EKPO "- Material type
***- Filter where [Del. Compl] = ""No""
***- Extract purchase order only last 18 months" "Include materials that:
***Have a 7-digit reference [from MARA or EKPO TBC] AND
***Start with a 4 AND
***Do not include # in the description [EKPO.TXZ01 or MAKT table TBC] AND
***Have had a transaction in the last 18 months (ie date >= 1st October 2023 ;EKKO.EAEDAT) AND
***Have an open purchase order line item associated with it
***DO NOT INCLUDE purchase order line items that have been deleted [Deletion Indicator in Purchasing Document = 'L' (field LOEKZ in table EKPO)"
*&---------------------------------------------------------------------*
*&**This Program Has Been Updated Dated 06/01/2025 @ 13:42 By Vivek Nigam To Fix Run Time Excution Issue
***EBELN MATNR WEMNG EINDT
*&---------------------------------------------------------------------*
**Updated on 17/01/2025 - AND EKPO~ELIKZ = '' --ELIKZ "Delivery Completed" Indicator
**Updated on 19/03/2025 @15:31 PM - AND AND EKPO.EBELP = EKET.EBELP
REPORT Z_EKET_HERO_PO.
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\EKET.csv'.
*PARAMETERS : OPATH TYPE STRING DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\EKET.csv'.
PARAMETERS : OPATH TYPE STRING LOWER CASE DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\EKET.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.
****EBELN WEMNG EINDT -- EKET
type-pools:TRUXS.
data: begin of itab occurs 0,
EBELN like EKET-EBELN,
MATNR like MARA-MATNR,
WEMNG like EKET-WEMNG,
EINDT like EKET-EINDT,
end of itab.
data: itab1 type TRUXS_T_TEXT_DATA.
*START-OF-SELECTION.
*
* BREAK-POINT.
*" NOTE: '_' in SELECT statement means exactly 12 Character
*" NOTE: '%' in SELECT statement means 0 or many Characters
*" Select for all company codes having '12' in second place...
*DATA:lv_date TYPE sy-datum.
* Calculate the date one year before today
*lv_date = sy-datum - 546.
SELECT DISTINCT
EKET~EBELN
MARA~MATNR
EKET~WEMNG
EKET~EINDT
FROM MARA
INNER JOIN MAKT
ON MARA~MATNR EQ MAKT~MATNR
* INNER JOIN MARD
* ON MARA~MATNR EQ MARD~MATNR
INNER JOIN MSEG
ON MARA~MATNR EQ MSEG~MATNR
INNER JOIN EKPO
ON MARA~MATNR EQ EKPO~MATNR
INNER JOIN EKKO
ON EKPO~EBELN EQ EKKO~EBELN
INNER JOIN EKET
ON EKET~EBELN EQ EKKO~EBELN AND EKPO~EBELP EQ EKET~EBELP
into table itab
WHERE MSEG~BUKRS = ENTITY AND MARA~MATNR LIKE '000000000004%' AND MAKT~MAKTX NOT LIKE '#%' AND EKKO~AEDAT >= '20231001' AND EKPO~LOEKZ NOT IN ('L') AND EKPO~ELIKZ = ''.
**AND MARD~LABST > 0
* AND LENGTH(MARA~MATNR) = 8 AND MARA~MATNR LIKE '00000000000%' AND EKKO~AEDAT >= lv_date
*"header data -- ADD HEADER IN THE CSV FILE AFTER THIS
*itab-MATNR = 'MATNR'.
*itab-MEINS = 'MEINS'.
*itab-MAKTX = 'MAKTX'.
*itab-EAN11 = 'EAN11'.
*Insert itab index 1.
DATA: lt_output TYPE TABLE OF string,
lv_header TYPE string,
lv_line TYPE string,
lv_filename TYPE string.
lv_filename = OPATH.
lv_header = 'EBELN;MATNR;WEMNG;EINDT'.
* Add the header to the output table
APPEND lv_header TO lt_output.
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.
*---Below Code Added By Vivek Nigam Dated - 23/12/2024 @ 13:58
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 Code Remove To Format in Currency -----
***APPEND LINES OF itab1 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