Child pages
  • Eliminate redundant data in the consolidation DSO
Skip to end of metadata
Go to start of metadata

Summary :

Sales order data is extracted from R3 to BW using 3 data sources, 2lis_11_VAHDR, 2lis_11_VAITm and 2lis_11_VASCL. Data is loaded to 3 Staging DSO's.  Some times you may get the requurement to consolidate all 3 into one DSO which is used to calculate 'Count' at very detailed level(scheduline).

Problem : 

The 3 datasources do not cantain all the fields in common, so when we update the data individually from the 3 DSo's into Consolidation, it is going to be stored redundantly because Item DSO does not provide schedule line number, so item load will not overwrite the contents of Schedule line. Similarly header also.

For Ex: when you execute a report on the consolidation DSO you may not see all the values at all the levels, because  The row where the Sales ORg is stored  may not have the schedule line data.  Similarly Schedule line data will not contain overall status of the document. So this is also not possible.

Doc No

Item

Sched line

sales org

Del qty

Netvalue

Overall Status

Goods Issue Date

 

 

10001

10

01

 

50

 

 

01.01.2009

 

 

10001

10

02

 

50

 

 

01.02.2009

 

 

10001

20

01

 

60

 

 

04.01.2009

 

 

10001

10

 

1000

 

200 USD

 

 

 

 

10001

20

 

1000

 

100 USD

 

 

 

 

10001

 

 

1000

 

 

C

 

 

 


one way of avoiding the problem is, getting the all the required fields from all  the datasources by enhancing in CMOD. The other way of resolving this, writing start routines in the Transformations.

Step by Step approach :

Step 1 :

Load the schedule line data first to the Consolidation DSO from schedule line staging DSO. Activate it.

Step 2 :

Add a dummy field 'ZSch_line' to the Staging DSO, Write a start routine at the transformations from Item staging DSO to Consolidation DSO.  having look in the Active data table of Consolidation DSO to get the schedule line numbers for the entries of the item data package.

TYPES : BEGIN OF ty_doc,
        doc_number TYPE /bic/azsd_o0400-/bic/zdoc_num,
        item TYPE /bic/azsd_o0400-/bic/zs_orditm,
        schedule TYPE /bic/azsd_o0400-/bic/zsch_line,
        END OF ty_doc.
DATA : it_doc TYPE STANDARD TABLE OF ty_doc,
       wa_doc type ty_doc,                itm_source_package TYPE STANDARD TABLE OF _ty_s_sc_1,
                wa_source_package TYPE _ty_s_sc_1.
 
TYPES :   BEGIN OF ty_doc,
               doc_number TYPE /bic/azsd_o0400-/bic/zdoc_num,
               item              TYPE /bic/azsd_o0400-/bic/zs_orditm,
               schedule TYPE /bic/azsd_o0400-/bic/zsch_line,
              END OF ty_doc.
 
DATA : it_doc TYPE STANDARD TABLE OF ty_doc,
            wa_doc TYPE ty_doc,
            itm_source_package TYPE STANDARD TABLE OF _ty_s_sc_1,
            wa_source_package TYPE _ty_s_sc_1.
 
itm_source_package[] = SOURCE_PACKAGE[].
FREE SOURCE_PACKAGE[].
 
SORT itm_source_package BY /bic/zdoc_num /bic/zs_orditm.
 
* Get the scheduline data from the consolidation DSO ZSD_O0400 and put in the intenal table it_doc
 
SELECT    /bic/zdoc_num   /bic/zs_orditm    /bic/zsch_line   INTO  TABLE  it_doc  FROM   /bic/azsd_o0400
FOR ALL ENTRIES IN itm_source_package WHERE
/bic/zdoc_num = itm_source_package-/bic/zdoc_num and
/BIC/ZS_ORDITM = itm_source_package-/bic/zs_orditm.
 
SORT it_doc by doc_number item schedule.
 
*Append the source_package with the scheduline data which we got from Consolidation DSO
 
LOOP AT it_doc INTO wa_doc.
      READ TABLE itm_source_package INTO wa_source_package WITH KEY
/bic/zdoc_num = wa_doc-doc_number
/bic/zs_orditm = wa_doc-item.
IF sy-subrc = 0.
wa_source_package-/bic/zsch_line = wa_doc-schedule.
ENDIF.
APPEND wa_source_package TO SOURCE_PACKAGE.
 
CLEAR : wa_doc,
             wa_source_package.
 
ENDLOOP.
 
FREE : itm_source_package[],
           it_doc[].

 

Step 3 :

Now Load the data from Staging DSO to Consolidation DSO, Then Activate this request. Now the item is going to overwrite the contents of schedule line in the Consolidation DSO because in the source package we got the respective schedule lines from the consolidation DSO.  if any field is already updated during schedule line ex: sales org, now it will be overwritten. if any field (blank) is not filled during the scheduline will be filled with item data.

Note : 1. you must activate the scheduline data request in consolidation before loading the item data.

          2. Dummy field (Zsch_line) added in the Staging item DSO must be mapped to the Zsch_line field in the Consolidation DSO.

Step 4 :

After activating the item request in consolidation DSO, you can write a start routine at transformation of Header DSO to consolidation DSo. Which wll have look up in to Active table consoliation and get the item and schedule line fields data. Now you can load header data to consolidation, it is going to overwrite the existing contents of the consolidation. finally you get 3 records in the consolidation insteady of 6 records.

header Dso to Consolidation DSo  Start routine is similar to the above, the only change is we need to append source_package for 2 fields i.e item and scheduline line.

Note : 1.Activation of Item request in consolidation is must before loading header data.

          2. Add 2 dummy fileds item and schedule line to Header DSO and map them to the same fields of Consolidation DSO at transformations.