Skip to Content
0
Former Member
Aug 04, 2010 at 07:44 AM

BAPI for automatic Pr creation witth multiple files from excel sheet

69 Views

I have written a programm for automatic PR creation with the help of bapi , where its picks data from excel sheet and makes PR .It is picking excel files from one folder(Files) for PR creation and moving to another folder(Files sucess).

now the problem is if Folder (Files) contains one excel sheet ,PR is created fine , but if the Folder(Files) have multiple excel sheet ,its is creating 1st PR right, but next PR's contains all the line item of 1st PR , 2nd PR and so on .can anyone suggest me where is the problem in codes.

types : begin of x_file ,

key1(10),

pur_grp(4),

maktx(40),

plant(4) ,

req(10),

s_qty(13),

wbs(24),

gl(10),

trackno(10),

supl(4),

deladd(255).

types : end of x_file .

data : str5 type char10.

data : mm type char2.

data : yyyy type char4.

data : dd type char2.

data : str9 type char10.

data : str6 type char10.

data : month type char2.

data : year type char4.

year = sy-datum+0(4).

month = sy-datum+4(2).

dd = sy-datum+6(2).

yyyy = sy-datum+0(4).

mm = sy-datum+4(2).

dd = sy-datum+6(2).

clear str6 .

clear str5.

concatenate dd'.' month '.' year into str5 .

concatenate yyyy mm dd into str6 .

DATA : file type rlgrap-filename .

data : it_file type table of x_file .

data : wa_file type x_file .

data : it_header type table of x_file .

data : wa_header type x_file .

&*************************************************************

*& Internal Table For Define Row and Coloum Postion

&*************************************************************

data: col_start type i value 1 ,

row_start type i value 2,

col_end type i value 256 ,

row_end type i value 65000 .

&*************************************************************

*& Internal Table For Retrieve Data From Excel

&*************************************************************

*data: excel_bdcdata like kcde_cells occurs 0 with header line.

*

*data: excel_bdcdata1 like kcde_cells occurs 0 with header line.

data: excel_bdcdata like ALSMEX_TABLINE occurs 0 with header line.

data: excel_bdcdata1 like ALSMEX_TABLINE occurs 0 with header line.

data: it_index type i.

DATA : IT_INDEX1 TYPE I.

&*************************************************************

*& Define Field Symbol

&*************************************************************

field-symbols: <fs> .

data : bdcdata like bdcdata occurs 0 with header line.

data : messtab like bdcmsgcoll occurs 0 with header line.

data : req_items type table of bapiebanc .

data : wa_req_items type bapiebanc .

data : req_acc_asg type table of bapiebkn.

data : wa_req_acc_asg type bapiebkn.

DATA : RETURN LIKE BAPIRETURN OCCURS 0 WITH HEADER LINE .

*data : return type table of bapireturn.

*

*data : wa_return type bapireturn .

data : number type ebeln .

*****************************MOVE FILES******************************

data : xsource type string .

data : xdestin type string .

data : destin1 type string .

data : destin2 type string .

DATA : DEST1 TYPE STRING.

DATA : DEST11 TYPE STRING.

DATA : DEST2 TYPE STRING.

DATA : DEST22 TYPE STRING.

data : sou_dir_name like SALFILE-LONGNAME.

data : tar_dir_name like SALFILE-LONGNAME.

data : tar_dir_name1 like SALFILE-LONGNAME.

data : sou_filename like EDI_PATH-PTHNAM .

data : tar_filename like EDI_PATH-PTHNAM .

data : filename1 type string .

data : tar_filename1 like EDI_PATH-PTHNAM .

data : file_itab like SALFLDIR occurs 0 with header line.

data : wa_file_itab like SALFLDIR.

data : file_count type i .

data : dir_count type i.

data : dir_table like sdokpath occurs 0 with header line.

data : file_table like SDOKPATH occurs 0 with header line.

data : wa_file_table like sdokpath.

data : strr type string ,

str1 type string ,

str2 type string ,

str3 type string .

DATA : PA_VAL TYPE CHAR1.

sou_dir_name = 'D:\barcodes\files\'.

tar_dir_name = 'D:\barcodes\files-success\'.

"success folder.

CALL FUNCTION 'TMP_GUI_DIRECTORY_LIST_FILES'

EXPORTING

DIRECTORY = sou_dir_name

FILTER = '.'

IMPORTING

FILE_COUNT = file_count

DIR_COUNT = dir_count

TABLES

FILE_TABLE = file_table

DIR_TABLE = dir_table

EXCEPTIONS

CNTL_ERROR = 1

OTHERS = 2.

IF SY-SUBRC <> 0.

ENDIF.

loop at file_table into wa_file_table.

clear : strr , str1 , str2 , str3 .

strr = wa_file_table-PATHNAME .

concatenate sou_dir_name strr into str1 .

concatenate tar_dir_name strr into str2 . " success

concatenate tar_dir_name1 strr into str3 . " failed

FILE = STR1 .

*start-of-selection.

&*************************************************************

*& Function For Retrieve Data From Excel

&*************************************************************

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = FILE

i_begin_col = col_start

i_begin_row = row_start

i_end_col = col_end

i_end_row = row_end

tables

intern = excel_bdcdata

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

IF sy-subrc NE 0.

WRITE : / 'File Error'.

EXIT.

ENDIF.

.

loop at excel_bdcdata.

translate excel_bdcdata to upper case .

move excel_bdcdata-col to it_index.

assign component it_index of structure wa_file to <fs> .

move excel_bdcdata-value to <fs>.

at end of row.

append wa_file to it_file .

clear wa_file.

endat.

endloop.

********************************************************************

sort it_file by key1. "pur_grp maktx plant .

it_header[] = it_file[].

delete adjacent duplicates from it_header comparing key1 pur_grp maktx

plant .

data : h_item(5) type n .

.

data : h_pack(10) type n .

data : line_no(5) type n .

data : ln_no(5) type n .

loop at it_header into wa_header .

ln_no = 1.

h_item = h_item + 10.

h_pack = h_pack + 1.

wa_req_items-preq_item = h_item .

wa_req_items-doc_type = 'BOM'.

wa_req_items-pur_group = wa_header-pur_grp .

wa_req_items-MATERIAL = wa_header-maktx .

wa_req_items-plant = wa_header-plant .

wa_req_items-pckg_no = h_pack .

wa_req_items-deliv_date = str6 .

wa_req_items-item_cat = '0'.

wa_req_items-acctasscat = 'P'.

*wa_req_items-distrib = '2' .

**wa_req_items-gr_ind = 'X'.

wa_req_items-ir_ind = '2'.

wa_req_items-purch_org = 'TISL' .

wa_req_items-QUANTITY = wa_header-s_qty.

wa_req_items-PREQ_NAME = wa_header-req.

wa_req_items-SUPPL_PLNT = wa_header-supl.

wa_req_items-trackingno = wa_header-trackno.

append wa_req_items to req_items .

clear wa_req_items.

wa_req_acc_asg-preq_item = h_item .

wa_req_acc_asg-g_l_acct = wa_file-gl .

WA_req_acc_asg-wbs_elem = wa_header-wbs .

append wa_req_acc_asg to req_acc_asg .

clear wa_req_acc_asg.

h_pack = h_pack + 1 .

endloop.

clear ln_no .

***BREAK-POINT.

&*********************************************************************

*& BAPI FUNCTION

&*********************************************************************

call function 'BAPI_REQUISITION_CREATE'

importing

number = number

tables

requisition_items = req_items

requisition_account_assignment = req_acc_asg

return = return .