*&---------------------------------------------------------------------*
*& Report Z_EKKO_HERO_PO
*& Extract SAP Purchase Order EKKO Data from SAP Table To csv file called EKKO.csv
*&---------------------------------------------------------------------*
*& Developed By - Vivek Nigam
*& Date - 20/12/2024 @ 11:10 AM
*&---------------------------------------------------------------------*
*& Modified by-Vivek Nigam
*& Date -
*& Modified on
**'- Material type
**- Filter where [Del. Compl] = "No"
**- Extract purchase order only last 18 months
**'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
***EKKO -- EBELN LIFNR IHREZ WAERS ERNAM BEDAT
****&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
***Issue Found After Deployed The TR in UK2/UKU. Need To Change The Query Dated 30/12/2024 @ 12:48 PM
*&---------------------------------------------------------------------*
**This Program Has Been Updated Dated 06/01/2025 @ 13:07 By Vivek Nigam To Fix Run Time Excution Issue
***EBELN MATNR LIFNR IHREZ WAERS ERNAM BEDAT
**Updated on 17/01/2025 - AND EKPO~ELIKZ = '' --ELIKZ "Delivery Completed" Indicator
REPORT Z_EKKO_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\EKKO.csv'.
*PARAMETERS : OPATH TYPE STRING DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\EKKO.csv'.
PARAMETERS : OPATH TYPE STRING LOWER CASE DEFAULT '\\UKDATWNINTP01\SAP_Hero_OAT\Raw\IN\UK\UK01\EKKO.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,
EBELN like EKKO-EBELN,
MATNR like MARA-MATNR,
LIFNR like EKKO-LIFNR,
IHREZ like EKKO-IHREZ,
WAERS like EKKO-WAERS,
ERNAM like EKKO-ERNAM,
BEDAT like EKKO-BEDAT,
* BUKRS like MSEG-BUKRS,
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
EKKO~EBELN
MARA~MATNR
EKKO~LIFNR
EKKO~IHREZ
EKKO~WAERS
EKKO~ERNAM
EKKO~BEDAT
* MSEG~BUKRS
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
into table itab
** WHERE MSEG~BUKRS = ENTITY AND MARA~MATNR LIKE '000000000004%' AND EKPO~LOEKZ NOT IN ('L') AND MAKT~MAKTX NOT LIKE '#%' AND EKKO~AEDAT >= '20231001'.
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;LIFNR;IHREZ;WAERS;ERNAM;BEDAT'.
* 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.
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