topic Re: Quering Archive Data in Application Development and Automation Discussions

Quering Archive Data

Former Member — Thu, 27 Sep 2007 02:22:02 GMT

Dear All

I am trying to retrieve data from 1999 to 2003 using certain Policy numbers from Table BSIS. My SAP Query (SQ00) is based on Logical Database SDF and even though I am selecting in data source Archive data it doesnt give any result. In a nutshell all I need to know is "How can i retrieve Archive Data using SAP Query " . Do i need to query on some other table than BSIS for archive data?

Quick response would be appreciated

THanks

Re: Quering Archive Data

Former Member — Thu, 27 Sep 2007 02:23:59 GMT

Hi sameer,

You can not retrieve this data from BSIS as the archived data is not stored in any table.

It is stored in some hard drive or external systems.

There are separate SAP transactions to retrieve the archived data.

Regards,

Atish

Re: Quering Archive Data

Former Member — Thu, 27 Sep 2007 02:58:54 GMT

good

You have two possibilities to make an ABAP Query.

1. tcode: sq01

but first u have to define the user group and attach it to query

2. write a nornal ABAP Report in the format u stated.

see example

REPORT ZOLAS_ALLETESTING MESSAGE-ID 38 NO STANDARD PAGE HEADING LINE-SIZE 200.

*Tabelle strukture

TABLES:CDHDR,

CDPOS,

VBAK,

VBAP,

MARC,

T024D,

TVKO.

*Eingabe Parameters als Select-options

SELECT-OPTIONS: P_VKORG FOR VBAK-VKORG OBLIGATORY,

P_VETWEG FOR VBAK-VTWEG OBLIGATORY,

P_WERKS FOR VBAP-WERKS OBLIGATORY,

P_DATUM FOR CDHDR-UDATE OBLIGATORY.

*Alternativ zu Ranges << neu Method zu Empfehlen >>

DATA: BEGIN OF RAVKORG OCCURS 0,

SIGN LIKE P_VKORG,

LOW LIKE P_VKORG,

HIGH LIKE P_VKORG,

OPTION LIKE P_VKORG,

END OF RAVKORG.

WRITE TEXT-001.

*Layout der interne Tabelle für die Bearbeitung von Tabellen CDHDR und CDPOS

TYPES: BEGIN OF ITAB_1,

T_OBJECTCLAS LIKE CDHDR-OBJECTCLAS,

T_UDATE LIKE CDHDR-UDATE,

T_OBJECTID LIKE CDPOS-OBJECTID,

T_CHANGENR LIKE CDPOS-CHANGENR,

T_FNAME LIKE CDPOS-FNAME,

T_TABKEY LIKE CDPOS-TABKEY,

END OF ITAB_1,

*Layout der interne Tabelle für die Ausgabeliste definieren

BEGIN OF AUSGABE,

T_UDATE LIKE CDHDR-UDATE,

T_KUNNR LIKE VBAK-KUNNR,

T_PSTYV LIKE VBAP-PSTYV,

T_VBELN LIKE VBAP-VBELN,

T_POSNR LIKE VBAP-POSNR,

T_MATNR LIKE VBAP-MATNR,

T_DSNAM LIKE T024D-DSNAM, "Disponentenname

T_DISPO LIKE MARC-DISPO, "Disponent

T_DISMM LIKE MARC-DISMM, "Dispomerkmal

T_ARKTX LIKE VBAP-ARKTX,

T_KWMENG LIKE VBAP-KWMENG,

T_NETWR LIKE VBAP-NETWR,

T_WAERK LIKE VBAP-WAERK,

END OF AUSGABE.

*interne Tabellen

DATA: IT_1 TYPE ITAB_1 OCCURS 0 WITH HEADER LINE,

IT_AUSGABE TYPE AUSGABE OCCURS 0 WITH HEADER LINE,

*Hilf Variable

HILF LIKE CDPOS-TABKEY,

MERKE_VBELN LIKE VBAP-VBELN,

MERKE_POSNR LIKE VBAP-POSNR.

**heading

TOP-OF-PAGE.

FORMAT COLOR 1 INTENSIFIED OFF.

WRITE:/'*******************************************************************************************************************************************************************************************************',

/25 'DATUM: ', SY-DATUM, 60 'HFT: A B G E S A G T E A U F T R A G S P O S I T I O N E N (REPORTNAME: ZVABGESAGTEPOS)',

/'*******************************************************************************************************************************************************************************************************'.

SKIP 2.

ULINE.

**Selection----

START-OF-SELECTION.

CLEAR: RAVKORG,

HILF,

MERKE_VBELN,

MERKE_POSNR.

REFRESH: RAVKORG.

LOOP AT P_VKORG.

RAVKORG-SIGN = P_VKORG-SIGN.

RAVKORG-LOW = P_VKORG-LOW.

RAVKORG-HIGH = P_VKORG-HIGH.

RAVKORG-OPTION = P_VKORG-OPTION.

APPEND RAVKORG.

ENDLOOP.

SELECT * FROM TVKO WHERE VKORG IN P_VKORG. "RAVKORG.

AUTHORITY-CHECK OBJECT 'V_VBAK_VKO'

ID 'VKORG' FIELD TVKO-VKORG.

ID 'VKORG' FIELD TVKO-VKORG

ID 'VTWEG' FIELD 'DUMMY'

ID 'SPART' FIELD 'DUMMY'

ID 'ACTVT' FIELD 'DUMMY'.

IF SY-SUBRC <> 0.

MESSAGE E090(ZK).

EXIT.

ENDIF.

ENDSELECT.

SELECT * FROM CDHDR WHERE OBJECTCLAS = 'VERKBELEG' AND

UDATE IN P_DATUM ORDER BY UDATE.

SELECT * FROM CDPOS WHERE OBJECTCLAS = CDHDR-OBJECTCLAS AND

OBJECTID = CDHDR-OBJECTID AND

CHANGENR = CDHDR-CHANGENR AND

FNAME = 'ABGRU'.

MOVE CDHDR-OBJECTCLAS TO IT_1-T_OBJECTCLAS.

MOVE CDHDR-UDATE TO IT_1-T_UDATE.

MOVE CDPOS-OBJECTID TO IT_1-T_OBJECTID.

MOVE CDPOS-CHANGENR TO IT_1-T_CHANGENR.

MOVE CDPOS-FNAME TO IT_1-T_FNAME.

MOVE CDPOS-TABKEY TO IT_1-T_TABKEY.

APPEND IT_1.

ENDSELECT.

*Ausgabeliste Header

FORMAT COLOR 2 INTENSIFIED OFF.

WRITE:

/1 'AEND-DAT',

15 'AUFT_GEB',

25 'PTYP',

30 'VERKAUFS_BEL',

45 'POS',

55 'MAT-NR',

65 'DISPO',

75 'DS-NAME',

95 'DSMM',

105 'BEZEICHNUNG',

153 ' AUFTRAGSMENGE',

180 ' NETTOWERT',

192 'WAERHG'.

ULINE.

*Ausgabe selection, bearbeitung und listen

LOOP AT IT_1.

*aus IT_1-T_TABKEY wird Positionsnummer und Verkaufsbekeg ermitteln

MOVE IT_1-T_TABKEY TO HILF.

MOVE HILF+3(10) TO MERKE_VBELN. "Offsetting

MOVE HILF+13(6) TO MERKE_POSNR.

MOVE IT_1-T_UDATE TO IT_AUSGABE-T_UDATE.

*Position und kopftabelle lesen----

SELECT * FROM VBAK

WHERE VBAK~VBELN = MERKE_VBELN AND

VBAK~VKORG IN P_VKORG AND

VBAK~VTWEG IN P_VETWEG.

MOVE VBAK-KUNNR TO IT_AUSGABE-T_KUNNR.

SELECT * FROM VBAP WHERE VBAP~VBELN = VBAK-VBELN AND

VBAP~POSNR = MERKE_POSNR.

MOVE VBAP-PSTYV TO IT_AUSGABE-T_PSTYV.

MOVE VBAP-VBELN TO IT_AUSGABE-T_VBELN.

MOVE VBAP-POSNR TO IT_AUSGABE-T_POSNR.

MOVE VBAP-MATNR TO IT_AUSGABE-T_MATNR.

MOVE VBAP-ARKTX TO IT_AUSGABE-T_ARKTX.

MOVE VBAP-KWMENG TO IT_AUSGABE-T_KWMENG.

MOVE VBAP-NETWR TO IT_AUSGABE-T_NETWR.

MOVE VBAP-WAERK TO IT_AUSGABE-T_WAERK.

SELECT SINGLE *

FROM MARC AS MC

WHERE MC~WERKS = VBAP-WERKS AND

MC~MATNR = VBAP-MATNR.

MOVE MARC-DISPO TO IT_AUSGABE-T_DISPO.

MOVE MARC-DISMM TO IT_AUSGABE-T_DISMM.

SELECT SINGLE *

FROM T024D AS TD

WHERE TD~WERKS = VBAP-WERKS AND

TD~DISPO = MARC-DISPO.

MOVE T024D-DSNAM TO IT_AUSGABE-T_DSNAM.

APPEND IT_AUSGABE.

*Ausgabeliste

FORMAT COLOR 2 INTENSIFIED OFF.

WRITE:/1 IT_AUSGABE-T_UDATE,

15 IT_AUSGABE-T_KUNNR,

25 IT_AUSGABE-T_PSTYV,

30 IT_AUSGABE-T_VBELN,

45 IT_AUSGABE-T_POSNR,

55 IT_AUSGABE-T_MATNR,

65 IT_AUSGABE-T_DISPO,

75 IT_AUSGABE-T_DSNAM,

95 IT_AUSGABE-T_DISMM,

105 IT_AUSGABE-T_ARKTX,

150 IT_AUSGABE-T_KWMENG,

170 IT_AUSGABE-T_NETWR,

192 IT_AUSGABE-T_WAERK.

ENDSELECT.

ENDLOOP.

REFRESH: IT_AUSGABE, IT_1.

END-OF-SELECTION.

reward point if helpful.

thanks

mrutyun^

Re: Quering Archive Data

Former Member — Thu, 27 Sep 2007 16:19:44 GMT

Hi,

What is the archive data source that you are selec? Is it 'Archive Information System' or 'Select files manually'?

If you select "Archive Information System" it is required that you activate and populate the appropriate infostructure in SAP AS. If no suitable infostructure is active, the system does not read from the archive at all.

If you wish to use 'Archive Information System' option then I suggest you activate SAP standard infostructure SAP_FI_DOC_002.

Else you could select the option 'Select files manually'. If you select the option 'Select files manually' then you should select the appropriate archive file in which that record is archived.

Hope this helps

Cheers!

Samanjay

Re: Quering Archive Data

Former Member — Thu, 27 Sep 2007 17:08:45 GMT

Hello Sameer,

The entries in table BSIS are not actually "archived" with archive object fi_documnt.

If you have run the FI post processing program, the secondary index table entries are deleted, so, would not be retrievable. See the below help text for fi_documnt:

Secondary Indexes in Financial Accounting

During the posting of a financial accounting document the actual document data is stored in tables BKPF and BSEG, and additionally data from these tables is written redundantly to secondary index tables BSIS, BSAS, BSAD, BSAK, BSIM and BSIP. The purpose of this redundancy is to provide faster access to data in some applications, such as the line item report function. See Display of Archived Accounting Documents (FI).

Many applications only require the data from the secondary indexes instead of the actual document data. Therefore, the secondary indexes are not removed from the database during the delete phase of data archiving (except for data from table BSIM). The secondary indexes are removed subsequently with a special postprocessing program. The BSIM entries are deleted directly together with the document data. For the other secondary indexes you can determine in Customizing when they are supposed to be deleted at the earliest, using the secondary index life. For more information see Secondary Index Life in Financial Accounting (FI).

With the secondary index build program you can reconstruct the secondary indexes from the archived document data and store them again in the database, if necessary (except for BSIM entries).

Regards,

Karin Tillotson

Re: Quering Archive Data

Former Member — Thu, 27 Sep 2007 19:13:36 GMT

You can read the archive using FMs:

ARCHIVE_OPEN_FOR_READ

ARCHIVE_GET_NEXT_OBJECT

ARCHIVE_GET_NEXT_RECORD

ARCHIVE_CLOSE_FILE

Rob