Package Studio Tutorial Part 2 - This still doesn't go far enough for us
Internal customers and consumers of data extractions are like children. Easy to please at first, but once they've been engaged with something for a while, the satisfaction threshold is breached and they want more. So it was no surprise that purchasing also got back to me after a few days.
"Hi Nils, the data of the orders is basically great, but we would still need one or two additions. Firstly, we would like to see what changes have been made to these orders and the items. Secondly, we have noticed that the EKKO and EKPO order tables also include contracts and inquiries. In that case, the purchase orders and scheduling agreements would actually be enough for us. However, we would like to have the contracts in a separate table for the purchase orders. In fact, we would like to have all contracts that form the basis of a purchase order for the last year, i.e. also from previous years. Is that possible?"
Not possible, not possible - so let's get to work.
I recognize three requirements:
- Filter the purchase order tables to purchase orders and scheduling agreements only
- Extract the underlying contracts also from previous years into separate tables
- All changes to purchase orders and scheduling agreements
A fixed filter is created in no time
Requirement 1 is relatively simple to implement. This is about the order type in the BSTYP field that we need to filter. Since this is SAP standard setup and usually not subject to customizing, we know that we can filter on the values F for Purchase Order and L for Scheduling Agreement.
To do this, we create a new filter of type FixedValue, enter the two values under Values and assign it to the BSTYP field in EKKO.
This would clean up the scope to the desired order types. Since we extract all other tables using repositories, we do not need to adjust anything else here.
The dependence on the dependence
Filling repositories is not limited to the first or top level of tables. A table that is already the target of a repository can also serve as the source of another repository. We make use of this when selecting the contracts.
The SAP data model hides the reference to a contract item in the order items or the EKPO table. Strictly speaking, this reference is item specific, but we will simplify this and extract the complete contract with all items, if it occurs in the EKPO as a reference.
So first we create another repository in which we collect the contract numbers (EKPO-KONNR).
Since the contracts and their positions are stored in the EKKO and EKPO tables themselves, it now gets a bit tricky. Since the purchasing department wants the contract data in its own tables, we have to create another EKKO and EKPO. Since the Package Studio needs a uniqueness for writing to the SQL server, we have to use a table alias here for the first time.
So we give both tables the suffix '_contracts' and select all fields again.
Now comes the slightly more complicated part. We have collected the field EKPO-KONNR in the repository. But this now refers to the order number field EBELN in the contract tables. I.e. we need to do a field mapping when assigning the repository.
To do this, we add the repository of the contracts as a dependency to the EKKO table, click on the 'A' for the mapping, check the Custom Mapping box and change the field on the right from KONNR to EBELN.
We repeat the same procedure for the EKPO table.
A first date with the change documents
Let's come to the last requirement: all changes to orders and scheduling agreements in the scope. Changes to master and transaction data always lead us to the CDHDR (Change Document Header) and CDPOS (Change Document Position) tables.
With this knowledge we decide to filter both tables CDHDR and CDPOS with a FixValue filter on the object class "EINKBELEG " and to use the already built repository of the purchasing document number to filter only the documents in scope.
Here we need to take a quick look at the data structures of the SAP tables. The field EBELN in EKKO has 10 digits and the field OBJECTID in CDHDR and CDPOS, to which it must be mapped, has 90 digits. So here we have to use a transformation in addition to the mapping.
When creating the tables CDHDR and CDPOS we give them an alias and name each _EINKBELEG to index which object class we have filtered.
We create the filter ObjClass_Einkbeleg and leave the default values at "Fixed Value", "String", "Equal" and "Global". We enter EINKBELEG as the Value. Then we assign it to the two change tables.
In the last step we jump to the Repo_Purchase Document repository and add the CDHDR_EINKBELEG and the CDPOS_EINKBELEG as target tables. For this we change the mapping to Custom. select the OBJECTID field and the Substring transformation with the values 10 and 0 for Length and Offset.
We repeat the same for the CDPOS_EINKBELEG and at the end we get a package extended by the requirements of the purchasing department.
I wonder if that's it. I'll do a test extraction now.
It continues with part 3, because now it becomes slowly complex...
