09-11-2008 2:26 PM
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
-1--
-1--
-1--
-1--
-1--
-1--
-1--
-1--
(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
-1--
-1--
389.00
-1--
893.00
-1--
0
-1--
0
-1--
365.00
-1--
-1--
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
09-11-2008 2:58 PM
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
09-11-2008 2:58 PM
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
09-12-2008 7:57 AM
Hi Kostas,
Thanks for your time
Source System---delta-->DSO---delta--
>Cube
Could you please update me with code
Thanks
09-12-2008 9:10 AM
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
09-15-2008 4:40 PM
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