Creating PO from External File, With Limits
This is a very common scenario to create PO from external files.But having right set of code makes it much more easier.
| Source | Unique ID | Purchase Order Type | Company Code | Purchasing Organisation | Purchasing Group | Vendor | Local End Market Vendor/Customer number | Local End Market Trade Agreement reference/number | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SAP Fields description | Purchase Order Doc.Type | Legal entity (= SAP Company Code) | Purchasing Organisation | Purchasing Group | SAP Vendor Number | Your Reference | Our Reference | |||||||||||||
| Target | EKKO – BSART | EKKO – BUKRS | EKKO – EKORG | EKKO – EKGRP | EKKO – LIFNR | EKKO – IHREZ | EKKO – UNSEZ | |||||||||||||
| Source | Item | Currency | Account assignment category: Cost Center or WBS | Item category | Description of item | Item Quantity | Order Unit | Delivery date | Net Price of the item | Price Unit | VAT Code | Purchase Order Price Unit | Material Group | Plant | G/L account | WBS element | Cost Center | Overall Limit (Amount) | Expected value (Amount) | |
| SAP Fields description | Line Item | Currency | Account assignment category | Item category | Short Text | Item Quantity | Order Unit | Delivery date | Net Price of the item | Price Unit | Tax Code | Purchase Order Price Unit | Material Group | Plant | G/L account | WBS element | Cost Center | Overall Limit (Amount) | Expected value (Amount) | |
| Target | EKPO-EBELP | EKKO – WAERS | EKPO – KNTTP | EKPO – PSTYP | EKPO – TXZ01 | EKPO – MENGE | EKPO – MEINS | MEPO1211 – EEIND | EKPO – NETPR | EKPO – PEINH | EKPO – MWSKZ | EKPO – BPRME | EKPO – MATKL | EKPO – WERKS | EKKN – SAKNR | EKKN – PS_PSP_PNR | EKKN – KOSTL | ESUH-SUMLIMIT | ESUH-COMMITMENT | |
| X | 001 | NB | ||||||||||||||||||
| 10 | ||||||||||||||||||||
| X | 002 | NB | ||||||||||||||||||
| 10 |
For every new document, Enter the source column with X , and unique id signifies for one particular purchase order,and maintain the line items accordingly.
1.Create the selection screen, to take the file path.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text–001.
PARAMETERS:
p_path TYPE localfile, “Local PC
cb_sim AS CHECKBOX DEFAULT c_checked.“SIMULATION Checkbox
SELECTION-SCREEN END OF BLOCK b1.
2.Give F4 help for file path.
CALL FUNCTION ‘F4_FILENAME’
EXPORTING
program_name = syst–cprog ” Program name
IMPORTING
file_name = p_path. ” Local File Path
3. Data declaration for holding the file data.
TYPES:
*Input file as it is
BEGIN OF typ_input_file, “Input File as it is
col1 TYPE string, “Column1
col2 TYPE string, “Column2
col3 TYPE string, “Column3
col4 TYPE string, “Column4
col5 TYPE string, “Column5
col6 TYPE string, “Column6
col7 TYPE string, “Column7
col8 TYPE string, “Column8
col9 TYPE string, “Column9
col10 TYPE string, “Column10
col11 TYPE string, “Column11
col12 TYPE string, “Column12
col13 TYPE string, “Column13
col14 TYPE string, “Column14
col15 TYPE string, “Column15
col16 TYPE string, “Column16
col17 TYPE string, “Column17
col18 TYPE string, “Column18
col19 TYPE string, “Column19
col20 TYPE string, “Column20
col21 TYPE string, “Column21
END OF typ_input_file,
typ_t_input_file TYPE STANDARD TABLE OF typ_input_file,
*Input File in formatted form field wise
BEGIN OF typ_inp_file_head, “Input File formatted field wise
*Common to Header and Line Item Record
indicator TYPE char1, “Indicator for header – X, Line -space
unique_id TYPE char15, “Unique ID
*Header Record
po_type TYPE esart, “Purchase Order Type
comp_code TYPE bukrs, “Company Code
pur_org TYPE ekorg, “Purchasing Organisation
pur_grp TYPE bkgrp, “Purchasing Group
vendor TYPE elifn, “Vendor
yref TYPE ihrez, “Your Reference
oref TYPE unsez, “Our Reference
END OF typ_inp_file_head,
typ_t_inp_file_head TYPE STANDARD TABLE OF typ_inp_file_head, “Inp File
BEGIN OF typ_inp_file_line,
*Common to Header and Line Item Record
indicator TYPE char1, “Indicator for header – X, Line -space
unique_id TYPE char15, “Unique ID
*Line Item Record
item TYPE ebelp, “Item
cur TYPE waers, “Currency
aacat TYPE knttp, “Acco asgnmnt cat: Cost Center or WBS
item_cat TYPE pstyp, “Item category
desc_line TYPE txz01, “Description of item
item_quan TYPE ktmng, “Item Quantity
ord_unit TYPE bstme, “Order Unit
del_date TYPE eeind, “Delivery date
net_price TYPE bprei, “Net Price of the item
price_unit TYPE epein, “Price Unit
vat_code TYPE mwskz, “VAT Code
po_price_unit TYPE bbprm, “Purchase Order Price Unit
mat_grp TYPE matkl, “Material Group
plant TYPE ewerk, “Plant
glacc TYPE saknr, “G/L account
wbs TYPE ps_posid,“WBS element
cost_cent TYPE kostl, “Cost Center
ov_limit TYPE sumlimit, “Overall Limit (Amount)
exp_val TYPE commitment,“Expected value (Amount)
END OF typ_inp_file_line,
typ_t_inp_file_line TYPE STANDARD TABLE OF typ_inp_file_line, “Inp File
*Log
BEGIN OF typ_log,
unique_id TYPE char15, “Unique Id
message(150) TYPE c, “Message Content
END OF typ_log,
typ_t_log TYPE STANDARD TABLE OF typ_log. “Log
*———————————————————————-*
* GLOBAL DATA DECLARATION *
*———————————————————————-*
DATA :
*Input File Header in formatted formatted form field wise
li_inp_file_head TYPE STANDARD TABLE OF typ_inp_file_head
INITIAL SIZE 0,
*Input File Line Item in formatted formatted form field wise
li_inp_file_line TYPE STANDARD TABLE OF typ_inp_file_line
INITIAL SIZE 0,
*Output Log
li_log TYPE STANDARD TABLE OF typ_log
INITIAL SIZE 0.
*———————————————————————-*
* CONSTANTS DECLARATION *
*———————————————————————-*
CONSTANTS :c_type_e TYPE char1 VALUE ‘E’, “E
c_line TYPE char1 VALUE ‘|’, “|
c_checked TYPE char1 VALUE ‘X’, “Checked X
c_tab TYPE char1 “Tab in o/p file
VALUE cl_abap_char_utilities=>horizontal_tab,
c_uline_30 TYPE char30 VALUE “uline for 30 uline
‘______________________________’.
4.Read data from File.
DATA:
lw_filepath TYPE string, ” Filepath in string format
lw_item_quan TYPE char17, ” Item Qnty on string format
lw_net_price TYPE char14, ” Net price in string format
lw_price_unit TYPE char5, ” Price Unit in string format
lw_ov_limit TYPE char17, ” Ov Limit in string format
lw_exp_val TYPE char17, ” Exp Val in string format
lw_unique_id TYPE char15, ” Unique Id
ls_input_file TYPE typ_input_file,
“Input File string struct
ls_inp_file_head TYPE typ_inp_file_head, “Input File Header Field
“wise structure
ls_inp_file_line TYPE typ_inp_file_line, “Input File Line Field
“wise structure
lt_raw TYPE truxs_t_text_data, “Raw Data
lt_input_file TYPE STANDARD TABLE OF typ_input_file
INITIAL SIZE 0, “Input File
ls_log TYPE typ_log.
* Constants
CONSTANTS: lc_mtype TYPE bapi_mtype VALUE ‘E’,
lc_actvt TYPE activ_auth VALUE ’03’.” Activity
MOVE : p_path TO lw_filepath.
*Convertion of .xls data to sap converted text data
CALL FUNCTION ‘TEXT_CONVERT_XLS_TO_SAP’
EXPORTING
i_field_seperator = c_tab ” Tab Delimeter
* I_LINE_HEADER =
i_tab_raw_data = lt_raw
i_filename = p_path ” File Path of Local PC
TABLES
i_tab_converted_data = lt_input_file[]
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
*Check Sy-Subrc and display a message if the file is not successfully
* uploaded from local PC
IF sy–subrc NE 0.
MESSAGE ‘Exception raised during uploading file from local pc’
TYPE c_type_e.
ENDIF.“IF sy-subrc NE 0.
*Format input file to field wise mapping
IF lt_input_file[] IS NOT INITIAL.
LOOP AT lt_input_file INTO ls_input_file.
* Checking the sy-tabix value for the row number greater than eqal to 7
IF sy–tabix GE 7.
IF ls_input_file–col1 EQ c_checked. ” Header Record – X
CLEAR : lw_unique_id. ” Unique Id for the data
*Common to Header and Line Item Record
MOVE:
ls_input_file–col1 TO ls_inp_file_head–indicator,” Header Indicat
ls_input_file–col2 TO ls_inp_file_head–unique_id,” Unique ID
*Header Record
ls_input_file–col3 TO ls_inp_file_head–po_type, ” PO Type
ls_input_file–col4 TO ls_inp_file_head–comp_code,” Company Code
ls_input_file–col5 TO ls_inp_file_head–pur_org, ” Purchasing Org
ls_input_file–col6 TO ls_inp_file_head–pur_grp, ” Purchasing Grp
ls_input_file–col7 TO ls_inp_file_head–vendor, ” Vendor
ls_input_file–col8 TO ls_inp_file_head–yref, ” Your Reference
ls_input_file–col9 TO ls_inp_file_head–oref. ” Our Reference
MOVE : ls_inp_file_head–unique_id TO lw_unique_id.
APPEND ls_inp_file_head TO li_inp_file_head[].
CLEAR ls_inp_file_head.
ELSE. “Line Item Record
MOVE :
*Common to Header and Line Item Record – Space
ls_input_file–col1 TO ls_inp_file_line–indicator,” Line ind.space
ls_input_file–col2 TO ls_inp_file_line–unique_id, “Unique ID –
*Line Item Record
ls_input_file–col3 TO ls_inp_file_line–item, “Item
ls_input_file–col4 TO ls_inp_file_line–cur, “Currency
ls_input_file–col5 TO ls_inp_file_line–aacat, “Acco asgnmnt
ls_input_file–col6 TO ls_inp_file_line–item_cat, “Item category
ls_input_file–col7 TO ls_inp_file_line–desc_line, “Description it
ls_input_file–col8 TO lw_item_quan, “Item Quantity
ls_input_file–col9 TO ls_inp_file_line–ord_unit, “Order Unit
ls_input_file–col10 TO ls_inp_file_line–del_date, “Delivery date
ls_input_file–col11 TO lw_net_price, “Net Price of the item
ls_input_file–col12 TO lw_price_unit, “Price Unit
ls_input_file–col13 TO ls_inp_file_line–vat_code, “VAT Code
ls_input_file–col14 TO ls_inp_file_line–po_price_unit,“PO PU
ls_input_file–col15 TO ls_inp_file_line–mat_grp, “Material Grp
ls_input_file–col16 TO ls_inp_file_line–plant, “Plant
ls_input_file–col17 TO ls_inp_file_line–glacc, “G/L account
ls_input_file–col18 TO ls_inp_file_line–wbs, “WBS element
ls_input_file–col19 TO ls_inp_file_line–cost_cent, “Cost Center
ls_input_file–col20 TO lw_ov_limit, “Overall Limit (Amount)
ls_input_file–col21 TO lw_exp_val. “Expected value (Amount)
MOVE: lw_unique_id TO ls_inp_file_line–unique_id, “Header Unique Id
“move here also
lw_item_quan TO ls_inp_file_line–item_quan, ” Item Quantity
lw_net_price TO ls_inp_file_line–net_price, ” Net Price
lw_price_unit TO ls_inp_file_line–price_unit,” Unit Price
lw_ov_limit TO ls_inp_file_line–ov_limit, ” Overall Limits
lw_exp_val TO ls_inp_file_line–exp_val. ” Expected val.
APPEND ls_inp_file_line TO li_inp_file_line[].
CLEAR ls_inp_file_line.
ENDIF. “IF ls_input_file-col1 EQ c_checked.
ENDIF.“IF sy-tabix GE 7.
CLEAR :
ls_input_file,
lw_item_quan,
lw_net_price,
lw_price_unit,
lw_ov_limit,
lw_exp_val.
ENDLOOP.” LOOP AT lt_input_file INTO ls_input_file.
ENDIF.“IF lt_input_file[] IS NOT INITIAL.
* Check if there is any record present
IF li_inp_file_head IS INITIAL.
MESSAGE ‘File does not contain any record’ type ‘I’.
LEAVE LIST–PROCESSING.
ENDIF.” IF ct_inp_file_head IS INITIAL.
5.Arrange the data and call the BAPI.
DATA:
lw_tabix TYPE sy–tabix VALUE 1, “Table Index
ls_inp_file_head TYPE typ_inp_file_head, “inp file head data
ls_inp_file_line TYPE typ_inp_file_line, “inp file line item
ls_return TYPE bapiret2, “Return Message
ls_poheader TYPE bapimepoheader, “header
ls_poheaderx TYPE bapimepoheaderx, “header x
ls_poitem TYPE bapimepoitem, “Item Data
ls_poitemx TYPE bapimepoitemx, “Item Datax
ls_poschedule TYPE bapimeposchedule, “Delivery Schedule
ls_poschedulx TYPE bapimeposchedulx, “Delivery Schedulex
ls_log TYPE typ_log, “Log
ls_account TYPE bapimepoaccount, “Account data
ls_accountx TYPE bapimepoaccountx, “header for Account Data
ls_limits TYPE bapiesuhc, “Limit data
ls_posrvaccessvalues TYPE bapiesklc,
lt_return TYPE STANDARD TABLE OF bapiret2
INITIAL SIZE 0, “Return Message
lt_poitem TYPE STANDARD TABLE OF bapimepoitem
INITIAL SIZE 0, “Item Data
lt_poitemx TYPE STANDARD TABLE OF bapimepoitemx
INITIAL SIZE 0, “Item Datax
lt_poschedule TYPE STANDARD TABLE OF bapimeposchedule
INITIAL SIZE 0, “Delivery Schedule
lt_poschedulx TYPE STANDARD TABLE OF bapimeposchedulx
INITIAL SIZE 0, ” Delivery Schedulex
lt_account TYPE STANDARD TABLE OF bapimepoaccount
INITIAL SIZE 0, ” Account data
lt_accountx TYPE STANDARD TABLE OF bapimepoaccountx
INITIAL SIZE 0, ” Account Header
lt_limits TYPE STANDARD TABLE OF bapiesuhc
INITIAL SIZE 0, ” limits
lt_posrvaccessvalues TYPE STANDARD TABLE OF bapiesklc
INITIAL SIZE 0,“Acct Assgt Distr.for Ser.Ln.
lw_pckg TYPE packno, ” To increase the package no.
lw_ponum TYPE ebeln.
CONSTANTS : lc_num TYPE etenr VALUE ‘0001’ , ” Schedule Line Number
lc_per TYPE char3 VALUE ‘100’ ,
lc_no TYPE char2 VALUE ’01’.
LOOP AT li_inp_file_head INTO ls_inp_file_head.
***Store Header details.
MOVE :
ls_inp_file_head–comp_code TO ls_poheader–comp_code,
c_checked TO ls_poheaderx–comp_code,
ls_inp_file_head–po_type TO ls_poheader–doc_type,
c_checked TO ls_poheaderx–doc_type,
sy–datum TO ls_poheader–creat_date,
c_checked TO ls_poheaderx–creat_date.
CALL FUNCTION ‘CONVERSION_EXIT_ALPHA_INPUT’
EXPORTING
input = ls_inp_file_head–vendor
IMPORTING
output = ls_poheader–vendor.
MOVE:
c_checked TO ls_poheaderx–vendor,
ls_inp_file_head–pur_org TO ls_poheader–purch_org,
c_checked TO ls_poheaderx–purch_org,
ls_inp_file_head–pur_grp TO ls_poheader–pur_group,
c_checked TO ls_poheaderx–pur_group,
sy–datum TO ls_poheader–doc_date,
c_checked TO ls_poheaderx–doc_date,
ls_inp_file_head–yref TO ls_poheader–ref_1,
c_checked TO ls_poheaderx–ref_1,
ls_inp_file_head–oref TO ls_poheader–our_ref,
c_checked TO ls_poheaderx–our_ref.
***Store Line Item Details
LOOP AT li_inp_file_line INTO ls_inp_file_line
FROM lw_tabix
WHERE unique_id = ls_inp_file_head–unique_id.
lw_tabix = sy–tabix + 1.
MOVE :
ls_inp_file_line–item TO ls_poitem–po_item,
ls_inp_file_line–item TO ls_poitemx–po_item,
ls_inp_file_line–desc_line TO ls_poitem–short_text,
c_checked TO ls_poitemx–short_text,
ls_inp_file_line–plant TO ls_poitem–plant,
c_checked TO ls_poitemx–plant,
ls_inp_file_line–mat_grp TO ls_poitem–matl_group,
c_checked TO ls_poitemx–matl_group,
ls_inp_file_line–item_quan TO ls_poitem–quantity,
c_checked TO ls_poitemx–quantity,
ls_inp_file_line–ord_unit TO ls_poitem–po_unit,
ls_inp_file_line–ord_unit TO ls_poitem–orderpr_un,
c_checked TO ls_poitemx–po_unit,
c_checked TO ls_poitemx–orderpr_un,
ls_inp_file_line–net_price TO ls_poitem–net_price,
c_checked TO ls_poitemx–net_price,
ls_inp_file_line–price_unit TO ls_poitem–price_unit,
c_checked TO ls_poitemx–price_unit,
ls_inp_file_line–vat_code TO ls_poitem–tax_code,
c_checked TO ls_poitemx–tax_code,
ls_inp_file_line–aacat TO ls_poitem–acctasscat,
c_checked TO ls_poitemx–acctasscat,
ls_inp_file_line–item_cat TO ls_poitem–item_cat,
c_checked TO ls_poitemx–item_cat.
lw_pckg = lw_pckg + 1.
MOVE: lw_pckg TO ls_poitem–pckg_no,
c_checked TO ls_poitemx–pckg_no.
* Populate scheduled lines
MOVE: ls_inp_file_line–item TO ls_poschedule–po_item,
ls_inp_file_line–item TO ls_poschedulx–po_item,
lc_num TO ls_poschedule–sched_line,
lc_num TO ls_poschedulx–sched_line,
ls_inp_file_line–del_date TO ls_poschedule–delivery_date,
c_checked TO ls_poschedulx–delivery_date,
ls_inp_file_line–cur TO ls_poheader–currency,
c_checked TO ls_poheaderx–currency,
* Populate POACCOUNT
ls_inp_file_line–item TO ls_account–po_item,
ls_inp_file_line–item TO ls_accountx–po_item,
lc_no TO ls_account–serial_no,
lc_no TO ls_accountx–serial_no.
IF ls_inp_file_line–glacc IS NOT INITIAL.
MOVE: ls_inp_file_line–glacc TO ls_account–gl_account,
c_checked TO ls_accountx–gl_account.
ENDIF.” IF ls_inp_file_line-glacc IS NOT INITIAL.
IF ls_inp_file_line–cost_cent IS NOT INITIAL.
MOVE: ls_inp_file_line–cost_cent TO ls_account–costcenter,
c_checked TO ls_accountx–costcenter.
ENDIF.” IF ls_inp_file_line-cost_cent IS NOT INITIAL.
IF ls_inp_file_line–wbs IS NOT INITIAL.
MOVE: ls_inp_file_line–wbs TO ls_account–wbs_element,
c_checked TO ls_accountx–wbs_element.
ENDIF.” IF ls_inp_file_line-wbs IS NOT INITIAL.
* Populate limits
MOVE: lw_pckg TO ls_limits–pckg_no,
ls_inp_file_line–ov_limit TO ls_limits–limit,
ls_inp_file_line–exp_val TO ls_limits–exp_value,
c_checked TO ls_limits–no_frlimit,
* Populate POSRVACCESSVALUES
lw_pckg TO ls_posrvaccessvalues–pckg_no,
lc_per TO ls_posrvaccessvalues–percentage,
lc_no TO ls_posrvaccessvalues–serial_no.
* Append the work area to the internal table
APPEND :
ls_poitem TO lt_poitem[],
ls_poitemx TO lt_poitemx[],
ls_poschedule TO lt_poschedule[],
ls_poschedulx TO lt_poschedulx[],
ls_account TO lt_account[],
ls_accountx TO lt_accountx[],
ls_limits TO lt_limits[],
ls_posrvaccessvalues TO lt_posrvaccessvalues[].
* Clear the work areas
CLEAR :
ls_poitem,
ls_poitemx ,
ls_poschedule,
ls_poschedulx,
ls_account,
ls_accountx,
ls_limits,
ls_posrvaccessvalues,
ls_inp_file_line.
ENDLOOP.
*Call BAPI to Update PO Data in SAP
CALL FUNCTION ‘BAPI_PO_CREATE1’
EXPORTING
poheader = ls_poheader
poheaderx = ls_poheaderx
testrun = cb_sim
IMPORTING
exppurchaseorder = lw_ponum
TABLES
return = lt_return[]
poitem = lt_poitem[]
poitemx = lt_poitemx[]
poschedule = lt_poschedule[]
poschedulex = lt_poschedulx[]
poaccount = lt_account[]
poaccountx = lt_accountx[]
polimits = lt_limits[]
posrvaccessvalues = lt_posrvaccessvalues[].
IF lw_ponum IS NOT INITIAL AND cb_sim IS INITIAL.
CALL FUNCTION ‘BAPI_TRANSACTION_COMMIT’
EXPORTING
wait = c_checked.
ENDIF.“IF lw_ponum IS NOT INITIAL AND cb_sim IS INITIAL.
LOOP AT lt_return INTO ls_return.
ls_log–unique_id = ls_inp_file_head–unique_id.
CONCATENATE ls_return–type
ls_return–message
INTO ls_log–message
SEPARATED BY space.
APPEND ls_log TO li_log[].
CLEAR ls_log.
ENDLOOP.“LOOP AT lt_return INTO ls_return.
CLEAR : ls_inp_file_head,
ls_poheader,
ls_poheaderx,
lw_pckg,
lw_ponum.
* Refresh the Internal Tables
REFRESH : lt_return[],
lt_poitem[],
lt_poitemx[],
lt_poschedule[],
lt_poschedulx[],
lt_account[],
lt_accountx[],
lt_limits[],
lt_posrvaccessvalues[].
ENDLOOP.“LOOP AT ut_inp_file_head INTO ls_inp_file_head.
6.Finally display the log
DATA : ls_log TYPE typ_log. ” Local Data Declaration for Log
WRITE :/1 c_uline_30,
31 c_uline_30,
61 c_uline_30,
91 c_uline_30,
121 c_uline_30,
151 c_uline_30.
WRITE :/1 c_line,
5 ‘Unique Id’(003) CENTERED,
25 c_line,
27 ‘Message’(004) CENTERED,
180 c_line.
WRITE :/1 c_uline_30,
31 c_uline_30,
61 c_uline_30,
91 c_uline_30,
121 c_uline_30,
151 c_uline_30.
LOOP AT li_log INTO ls_log.
WRITE :/1 c_line,
5 ls_log–unique_id RIGHT–JUSTIFIED,
25 c_line,
27 ls_log–message LEFT–JUSTIFIED,
180 c_line.
CLEAR ls_log.
ENDLOOP.
WRITE :/1 c_uline_30,
31 c_uline_30,
61 c_uline_30,
91 c_uline_30,
121 c_uline_30,
151 c_uline_30.