How to upload past results data in SAP

[former_member1362664]

Hello All SAP GURUS......

Actually earlier there was using a separate system for result recording of inspection lot. Now as upgraded to SAP Client want all old result entries should enter in SAP.

The hierarchy of result recording is:-

Ist Level:- There are too many operation in an inspection lot.

IInd Level:- There are too many sampling point in an operation.

IIIrd Level:- There are too many MIC in a sampling point.

IVth Level:- There are too many dates in a MIC.

Vth Level:- There are too many times in a date.

Now we have all these things as past data e.g. Inspection lot, Operation no, Sampling point, Date, Time, MIC and result.

Actually we are also using a program but is is taking huge time for uploading the entries as 10000 entries in 20 hrs

Please guide how can we make it fast.

[former_member42743 · Accepted solution]

I'm not sure exactly what you want. It sounds like you are uploading data but you want it to go faster?

I would highly recommend that if you have something that is working, don't try to fix it!

Importing historical results is usually not done. I'm not sure I understand why there is a time criticalness to the process. So it takes a week to load them. Or a month! Hopefully you prioritize the load and load the most recent data first which is usually the data your going to most likely want first.

I'd just set it up as a batch job with a low priority and run the data in in chunks that take no more than 20 hrs to run at a low priority.

If it ain't broke, don't fix it.

FF

[former_member1362664 · Accepted solution]

Dear Fire Fighter,

Thanks for your reply....

Whatever you are saying that is absolutely correct. We are also doing in same passion.

But as the data is in huge amount on current situation it would take almost two months if continuous run. Which is not possible because system performance would go down.

I want how can I make it faster?

[former_member42743 · Accepted solution]

If you are loading more than 7 years of data I would strongly suggest you consult with records specialist and look at what you legally need to keep and the reasons why you are keeping it. I can't imagine a need to load records over 7 years. Even in the Pharmacy industry, only select data is brought forward in SAP systems.

To take two months to load you must be talking millions of records. I've done loads with as many as 800,000+ batch value records that only took me a few days to load and that was done mostly just during business hours.

Without seeing your coding, the program logic, the types of records your loading, it is almost impossible to suggest ways to make it run faster. If you are loading 2-3 million records and you can't get them loaded in a week, or two at most, I'd say you have a serious bottleneck in your infrastructure somewhere, maybe some database tables that aren't properly indexed, maybe a really bad program, or maybe some hardware issue limiting the speed.

FF

[anand_rao3 · Accepted solution]

Well Vivek,

I do have a confusion here.

1. I believe inspection lots (excluding manual lots) are created with reference to either purchase order or production order or delivery.

2. You are trying to replicate / import entire results in new SAP system. Does this mean you are creating all orders also?

3. If not then on what basis these results are being imported?

I suppose, it is better to analyze the results of older system separately, as it is assumed whenever you switch on to new system older data never gets replicated; especially when the legacy system is other than SAP system.

Regards,

Anand Rao

[former_member1362664 · Accepted solution]

Hi Mr. Rao,

Yes you are correct at point no 1 that we created one production order to get inspection lot created. but point no 2 is not correct because we are using only one inspection lot for uploading all past records. The basis of that is only client want to see all past result trends w.r.t MIC not inspection lots.

Vivek

[former_member1362664 · Accepted solution]

Dear Fire Fighter,

Please find the coding which I using and program logic:-

REPORT ZBAPI_QE51N_DATA_INSERT NO STANDARD PAGE HEADING.

INITIALIZATION.

TYPE-POOLS SLIS.

DATA: BEGIN OF ichar_results OCCURS 0.

INCLUDE STRUCTURE BAPI2045D2.

DATA: END OF ichar_results.

DATA: BEGIN OF isingle_results OCCURS 0.

INCLUDE STRUCTURE BAPI2045D4.

DATA: END OF isingle_results.

DATA: BEGIN OF bapireturn2 OCCURS 0.

INCLUDE STRUCTURE bapiret2.

DATA: END OF bapireturn2.

DATA: BEGIN OF RET OCCURS 0.

INCLUDE STRUCTURE bapiret2.

DATA: END OF RET.

DATA: BEGIN OF INSPPOINTDATA OCCURS 0.

INCLUDE STRUCTURE BAPI2045L4.

DATA: END OF INSPPOINTDATA.

DATA: BEGIN OF ireturntable OCCURS 0.

INCLUDE STRUCTURE bapiret2.

DATA: END OF ireturntable.

DATA : IT_EXCEL TYPE STANDARD TABLE OF ALSMEX_TABLINE,

WA_EXCEL TYPE ALSMEX_TABLINE.

SELECTION-SCREEN BEGIN OF BLOCK BLOCK1 WITH FRAME TITLE TEXT-001.

SELECTION-SCREEN BEGIN OF BLOCK BLOCK2 WITH FRAME.

PARAMETERS : P_FILE LIKE RLGRAP-FILENAME OBLIGATORY.

SELECTION-SCREEN END OF BLOCK BLOCK2.

SELECTION-SCREEN END OF BLOCK BLOCK1.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(83) text-002 MODIF ID COM.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(80) text-005 MODIF ID COM.

SELECTION-SCREEN END OF LINE.

TYPES :

begin of it_type,

p_oprnum type BAPI2045D2-inspoper,

lot_num type BAPI2045L2-INSPLOT,

samp type BAPI2045D4-EXT_NO,

date type char10,

time type char10,

c_code1 type QIBPMERKNR,

char1 type BAPI2045D4-RES_VALUE,

end of it_type.

DATA : IT_ITAB TYPE STANDARD TABLE OF IT_TYPE,

WA_ITAB TYPE IT_TYPE,

IT_ITABM TYPE STANDARD TABLE OF IT_TYPE,

WA_ITABM TYPE IT_TYPE,

charv like WA_ITAB-char1,

sign.

DATA : IT_ITABv TYPE STANDARD TABLE OF IT_TYPE,

WA_ITABv TYPE IT_TYPE.

TYPES : BEGIN OF FTYPE.

INCLUDE TYPE IT_TYPE.

TYPES : MES_TYPE(1),

MESSAGE TYPE STRING.

TYPES: END OF FTYPE.

DATA : IT_FTAB TYPE STANDARD TABLE OF FTYPE,

WA_FTAB TYPE FTYPE,

STR TYPE STRING,

rc type sy-tabix..

data : p_oprnum type BAPI2045D2-inspoper,

lot_num type BAPI2045L2-INSPLOT,

c_code type QIBPMERKNR,

char type BAPI2045D4-RES_VALUE,

samp type BAPI2045D4-EXT_NO.

DATA ITCAT TYPE SLIS_T_FIELDCAT_ALV.

DATA FCAT_WA TYPE SLIS_FIELDCAT_ALV.

AT SELECTION-SCREEN output.

LOOP AT SCREEN.

IF SCREEN-group1 = 'COM'.

SCREEN-INTENSIFIED = 1.

MODIFY SCREEN.

ENDIF.

ENDLOOP.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE .

PERFORM VALUE_HELP .

start-of-selection.

PERFORM GET_DATA1.

perform update_records_in_qe01.

PERFORM ERROR_LIST_ALV.

FORM UPDATE_RECORDS_IN_QE01 .

SORT IT_ITAB BY LOT_NUM P_OPRNUM SAMP date time c_code1.

DELETE ADJACENT DUPLICATES FROM IT_ITAB

COMPARING LOT_NUM P_OPRNUM SAMP date time c_code1.

IT_ITABM[] = IT_ITAB[].

SORT IT_ITABM BY LOT_NUM P_OPRNUM date time.

DELETE ADJACENT DUPLICATES FROM IT_ITABM

COMPARING LOT_NUM P_OPRNUM .

DATA : NUM(2) TYPE C.

FIELD-SYMBOLS : <FS1> TYPE ANY,

<FS2> TYPE ANY.

LOOP AT IT_ITAB INTO WA_ITAB.

refresh: isingle_results,

INSPPOINTDATA.

isingle_results-insplot = WA_ITAB-lot_num.

isingle_results-inspoper = WA_ITAB-p_oprnum.

isingle_results-inspchar = WA_ITAB-C_CODE1.

isingle_results-res_no = '0001'.

isingle_results-RES_VALUAT = 'A'.

clear :sign, charv.

charv = WA_ITAB-CHAR1 .

CONDENSE charv NO-GAPS.

IF CHARV(1) = '<' OR

CHARV(1) = '>'.

SIGN = CHARV(1).

CLEAR CHARV(1).

CONDENSE charv NO-GAPS.

ENDIF.

isingle_results-RES_ATTR = sign.

isingle_results-res_value = charv.

APPEND isingle_results.

INSPPOINTDATA-INSPLOT = WA_ITAB-lot_num.

INSPPOINTDATA-INSPOPER = WA_ITAB-p_oprnum.

INSPPOINTDATA-USERC1 = WA_ITAB-samp.

CONCATENATE WA_ITAB-date6(4) WA_ITAB-date3(2)

WA_ITAB-date(2) INto INSPPOINTDATA-USERD1 .

CONCATENATE WA_ITAB-time(2) WA_ITAB-time3(2) WA_ITAB-time6(2) INTO

INSPPOINTDATA-USERT1 .

append INSPPOINTDATA.

MOVE-CORRESPONDING WA_ITAB TO WA_FTAB.

lot_num = WA_ITAB-lot_num.

p_oprnum = WA_ITAB-p_oprnum.

CALL FUNCTION 'BAPI_INSPOPER_RECORDRESULTS' "DESTINATION DEST

EXPORTING

INSPLOT = lot_num

INSPOPER = p_oprnum

INSPPOINTDATA = INSPPOINTDATA

IMPORTING

RETURN = bapireturn2

TABLES

SINGLE_RESULTS = isingle_results

returntable = ireturntable.

LOOP AT bapireturn2.

WRITE 😕 bapireturn2-TYPE,

bapireturn2-MESSAGE.

ENDLOOP.

READ TABLE ireturntable WITH KEY

TYPE = 'E'.

IF SY-SUBRC = 0.

WA_FTAB-MES_TYPE = ireturntable-TYPE.

WA_FTAB-MESSAGE = ireturntable-MESSAGE.

CALL FUNCTION 'BAPI_TRANSACTION_ROLLBACK' "DESTINATION DEST

IMPORTING

RETURN = RET.

ELSE.

rc = rc + 1.

str = rc.

CONCATENATE '::' str '=>' 'Records updated' into str.

CALL FUNCTION 'BAPI_TRANSACTION_COMMIT' "DESTINATION DEST

EXPORTING

WAIT = 'X'

IMPORTING

RETURN = bapireturn2.

WA_FTAB-MES_TYPE = 'S'.

WA_FTAB-MESSAGE = 'Record successfully created'.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = rc

TEXT = str.

ENDIF.

append wa_ftab to it_ftab.

ENDLOOP.

ENDFORM. " UPDATE_RECORDS_IN_QE01

FORM VALUE_HELP .

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SYST-CPROG

DYNPRO_NUMBER = SYST-DYNNR

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = P_FILE.

ENDFORM. " VALUE_HELP

" GET_DATA

FORM ERROR_LIST_ALV .

DATA : TITLE TYPE LVC_TITLE .

CLEAR : STR.

STR = RC.

CONCATENATE 'Total : ' STR ' records updated' into str.

TITLE = STR.

PERFORM FCAT_FILL.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

FILENAME = 'C:\lims_error_log.xls'

WRITE_FIELD_SEPARATOR = 'X'

TABLES

DATA_TAB = IT_FTAB

.

IF SY-SUBRC <> 0.

ENDIF.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = SY-CPROG

I_GRID_TITLE = TITLE

IT_FIELDCAT = ITCAT

TABLES

T_OUTTAB = IT_FTAB

.

IF SY-SUBRC <> 0.

ENDIF.

ENDFORM. " ERROR_LIST_ALV

FORM FCAT_FILL .

perform append_fcat using 'P_OPRNUM' 'Operation Number'.

perform append_fcat using 'LOT_NUM' 'Lot Number'.

perform append_fcat using 'SAMP' 'Sampling point'.

perform append_fcat using 'DATE' 'Sampling Date'.

perform append_fcat using 'TIME' 'Sampling Time'.

perform append_fcat using 'C_CODE1' 'CODE 1'.

perform append_fcat using 'CHAR1' 'Value 1'.

perform append_fcat using 'MES_TYPE' 'Message Type'.

perform append_fcat using 'MESSAGE' 'Messages'.

ENDFORM. " FCAT_FILL

FORM GET_DATA1 .

data file type string.

file = p_file.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

TEXT = TEXT-111.

CALL FUNCTION 'GUI_UPLOAD'

EXPORTING

FILENAME = file

HAS_FIELD_SEPARATOR = 'X'

TABLES

DATA_TAB = it_itab

.

IF SY-SUBRC <> 0.

ENDIF.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

PERCENTAGE = 0

TEXT = TEXT-112.

ENDFORM. " GET_DATA1

FORM APPEND_FCAT USING P_VAL1

P_VAL2.

FCAT_WA-FIELDNAME = P_VAL1.

FCAT_WA-SELTEXT_M = P_VAL2.

APPEND FCAT_WA TO ITCAT.

CLEAR FCAT_WA.

ENDFORM. " APPEND_FCAT

Please suggest.

Edited by: vivekrastogi on Mar 16, 2011 5:30 AM