topic Re: ABAP (Routine) Error in BW Transformations in Application Development and Automation Discussions

ABAP (Routine) Error in BW Transformations

Former Member — Thu, 11 Sep 2008 13:26:18 GMT

Hi Experts,

As i am new(Learner) to BW Please advise me on how can i achieve this and update me with Releavent Start Routine and Field Routine....please

My Requirment is

Employee is Compounded on Location.

On Weekly or Monthly basis (dependending on Employee Payroll run) Employee will be assigned with the Wage Type and Amount for that Wage Type and Payroll Date (When the payroll was run)

Data Currently i have/Data comming from Source System

Loc_ID--~~Emp_IDWage_ID~~

Payroll_Date-Amount

-1--

999
~~001~~
~~08.08.2008~~-----100.00

-1--

999
~~088~~
~~08.08.2008~~-----560.00

-1--

999
~~345~~
~~08.08.2008~~-----437.00

-1--

999
~~001~~
~~08.07.2008~~-----654.00

-1--

999
~~088~~
~~08.07.2008~~-----389.00

-1--

999
~~345~~
~~08.07.2008~~-----893.00

-1--

999
~~264~~
~~08.06.2008~~-----600.00

-1--

999
~~345~~
~~08.08.2008~~-----365.00

(Employee may have Different Wage_ID and Amount for each payroll)

My requirment is to include a new key figure 'Previous_Amount' which will be populated previous Wage_ID Amount.

Loc_ID--~~Emp_IDWage_ID~~

Payroll_Date-Amount---Previous_Amount

-1--

999
~~001~~
~~08.08.2008~~--~~-100.00~~-----654.00

-1--

999
~~088~~
~~08.08.2008~~--~~-560.00~~--

389.00

-1--

999
~~345~~
~~08.08.2008~~--~~-437.00~~--

893.00

-1--

999
~~001~~
~~08.07.2008~~---654.00--

-1--

999
~~088~~
~~08.07.2008~~---389.00--

-1--

999
~~345~~
~~08.07.2008~~---893.00--

365.00

-1--

999
~~264~~
~~08.06.2008~~-----600.00

-1--

999
~~345~~
~~08.08.2008~~-----365.00

As i am a starter in BW i am struggling to write start routine in transformations (DSO-->CUBE) to transfer the data in DSO Active Table to a internal table and a field routine to update Previous_Amount field by sorting the internal table data and to pick employee's latest record less than the current payroll for that particular wage_id and populate that amout to Previous_Amount field.

Please make necessary corrections to the start routine by fixing where i went wrong and update me with the required field routine (which will read data from internal table used in start routine.

With the help of Vamsi i can able to write this code,But still the Previous Price is filled with 0

Please correct me where it went wrong

Start Routine

Global declaration

Types :

Begin of ITABtype,

ORG TYPE /BIC/OIZORG,

CYEMPNO TYPE /BIC/OIZCYEMPNO,

HED_ID TYPE /BIC/OIZHED_ID,

RHED_ID TYPE /BIC/OIZRHED_ID,

CHNG_DTE TYPE /BIC/OIZCHNG_DTE,

HED_AMT TYPE /BIC/OIZHED_AMT,

HED_PAMT TYPE /BIC/OIZHED_PAMT,

End of ITABtype.

Data : ITAB type standard table of ITABtype

with key

ORG

CYEMPNO

HED_ID

RHED_ID

CHNG_DTE

HED_AMT

HED_PAMT,

wa_itab like line of itab.

Data : ITAB1 type standard table of ITABtype

with key

ORG

CYEMPNO

HED_ID

RHED_ID

CHNG_DTE

HED_AMT

HED_PAMT,

wa_itab1 like line of itab1.

*Create an internal table with all the field types with u want to have

*in the output */

Data : wa_SOURCE_PACKAGE type tys_SC_1.

Data : tmp(2) type n value 1.

\ The above loop is to get all the values into the internal table*/

Loop at SOURCE_PACKAGE into wa_SOURCE_PACKAGE.

Move wa_SOURCE_PACKAGE-/BIC/ZORG to wa_itab-ORG.

Move wa_SOURCE_PACKAGE-/BIC/ZCYEMPNO to wa_itab-CYEMPNO.

Move wa_SOURCE_PACKAGE-/BIC/ZHED_ID to wa_itab-HED_ID.

Move wa_SOURCE_PACKAGE-/BIC/ZHED_ID/BIC/ZRHED_ID to wa_itab-RHED_ID.

Move wa_SOURCE_PACKAGE-/BIC/ZCHNG_DTE to wa_itab-CHNG_DTE.

Move wa_SOURCE_PACKAGE-/BIC/ZHED_AMT to wa_itab-HED_AMT.

*Move wa_SOURCE_PACKAGE-/BIC/ZHED_PAMT to wa_itab-HED_PAMT.

Append wa_itab to itab.

Endloop.

Sort itab by

ORG Ascending

CYEMPNO Ascending

HED_ID Ascending

RHED_ID Ascending

CHNG_DTE Descending.

Sort SOURCE_PACKAGE by

/BIC/ZORG Ascending

/BIC/ZCYEMPNO Ascending

/BIC/ZHED_ID Ascending

/BIC/ZHED_ID/BIC/ZRHED_ID Ascending

/BIC/ZCHNG_DTE Descending.

Loop at itab into wa_itab.

tmp = '1' .

Loop at SOURCE_PACKAGE into wa_SOURCE_PACKAGE from tmp.

If wa_itab-ORG = wa_SOURCE_PACKAGE-/BIC/ZORG.

wa_itab-CYEMPNO = wa_SOURCE_PACKAGE-/BIC/ZCYEMPNO.

wa_itab-HED_ID = wa_SOURCE_PACKAGE-/BIC/ZHED_ID.

wa_itab-RHED_ID = wa_SOURCE_PACKAGE-/BIC/ZHED_ID/BIC/ZRHED_ID.

wa_itab-CHNG_DTE = wa_SOURCE_PACKAGE-/BIC/ZCHNG_DTE.

wa_itab-HED_PAMT = wa_SOURCE_PACKAGE-/BIC/ZHED_AMT.

wa_itab-CHNG_DTE gt wa_SOURCE_PACKAGE-/BIC/ZCHNG_DTE.

tmp = tmp + 1.

Exit.

Endif.

Endloop.

Modify itab from wa_itab.

Endloop.

itab1[] = itab[].

Sort itab1 by

ORG Ascending

CYEMPNO Ascending

HED_ID Ascending

RHED_ID Ascending

CHNG_DTE Descending.

Delete adjacent duplicates from itab1 comparing

ORG

CYEMPNO

HED_ID

RHED_ID

CHNG_DTE.

Loop at itab1 into wa_itab1.

wa_itab-HED_PAMT = '0'.

Modify itab1 from wa_itab1.

Endloop.

Loop at itab into wa_itab.

Loop at itab1 into wa_itab1.

If wa_itab1-ORG = wa_itab-ORG.

wa_itab1-CYEMPNO = wa_itab-CYEMPNO.

wa_itab1-HED_ID = wa_itab-HED_ID.

wa_itab1-RHED_ID = wa_itab-RHED_ID.

wa_itab1-CHNG_DTE = wa_itab-CHNG_DTE.

wa_itab1-HED_PAMT = wa_itab-HED_PAMT.

Exit.

Endif.

Endloop.

Modify itab from wa_itab.

Endloop.

Field Routine

Read table ITAB into wa_itab

with key

ORG = SOURCE_FIELDS-/BIC/ZORG

CYEMPNO = SOURCE_FIELDS-/BIC/ZCYEMPNO

HED_ID = SOURCE_FIELDS-/BIC/ZHED_ID

RHED_ID = SOURCE_FIELDS-/BIC/ZHED_ID/BIC/ZRHED_ID

CHNG_DTE = SOURCE_FIELDS-/BIC/ZCHNG_DTE.

if sy-subrc = 0.

RESULT = wa_itab-HED_PAMT.

endif.

Please update me where it was wrong

Thanks in advance

Re: ABAP (Routine) Error in BW Transformations

kostas_tsioubris — Thu, 11 Sep 2008 13:58:41 GMT

Hi,

the ODS is loading with deltas or full upload? In case of a delta in the field update routine you can read all the active data from the ODS for this Emp_ID and Wage_ID into an internal table, then sort the internal table by Payroll_Date descending and read the first line of the table. This way you can have the previous payroll date.

In case of a full update you have to change the source package and fill the previous amount field in the start routine.

1. Declare an internal table similar as table source package

2. move contents of source package to int table

2. sort and loop at source package and use

at new payroll date.\

loop at int1 where Loc_ID = source-loc_id

and Emp_ID = source-emp_id

and wage_id = source--Wage_ID

and payroll_date < source-payrol_date.

remember to exit the loop after the first loop pass.

get the previous payroll date and move the source data and previous date and modify source package.

Regards

Kostas

Re: ABAP (Routine) Error in BW Transformations

Former Member — Fri, 12 Sep 2008 06:57:04 GMT

Hi Kostas,

Thanks for your time

Source System--~~-delta~~--~~>DSO~~--~~-delta~~--

>Cube

Could you please update me with code

Thanks

Re: ABAP (Routine) Error in BW Transformations

kostas_tsioubris — Fri, 12 Sep 2008 08:10:21 GMT

Hi again,

i don't have a BW system available now so the code is more as a how to and needs corrections but i hope you will figure out the logic.

declare an internal table and working area similar with the ods object.

data itab type table of ods_type.

data wa_itab type ods_type.

declare a working area for the source package.

data wa_source type type_source_package.

data l_tabix like sy-tabix.

select all active entries from ods .

if you figure out a performance issue here move this select into the loop and use where clauses or use for all entries in source_package

select * from ods_object into table itab .

sort source_package by payroll_date descending.

loop at source_package into wa_source.

l_tabix = sy-tabix.

read table itab into wa_itab with key Loc_ID = wa_source_package-loc_id

Emp_ID = wa_source_package-emp_id

wage_id = wa_source_package-Wage_ID.

wa_source-previous_amount = wa_itab-amount.

modify source_package from wa_source index l_tabix.

endloop.

This code should be in the start routine of the ods update.

Hope this helps

Kostas

Re: ABAP (Routine) Error in BW Transformations

Former Member — Mon, 15 Sep 2008 15:40:20 GMT

Hi Kostas,

Thanks fro the update..really useful.

I shall be greatful if you can rectify few queries i had.

As you mentioned...in ODS start routine i defined

Internal Table similar to ODS Active Table and passing all the active entries of that ODAS to the Internal Table and sorting on Payroll Date

This is the Start Routine logic

Can you please update me how can i write Field routine that will read only the First record of each employee

Thanks