TS Mapping Configuration¶
The TS Mapping configuration is used to export and import the data from and to qty_staging table in ALDH database. The table can be configured to get the data from any database and table based on the external logical system and external table name provided. In the same way the data can be exported to the specified application like Smoothie, ESOPT etc.
Following is the detailed description of each field:
1. MappingScn
The mapping scenario which combines a set of TS Mapping configuration to be passed as an input parameter to the external program which imports or exports the data to and from Qty_Staging in ALDH database.
2. IODirection
The IODirection governs the importing or exporting the data from Qty_Staging table.
For Importing the data IODirection = I and for exporting the data IODirection = O
3. QtyStgRecordType
The RecordType which will be used to populate or select the records in the Qty_Staging table.
4. QtyStgPlanID
The PlanID which will be used to populate or select the records in the Qty_Staging table.
While exporting the data to the external application, following will be the selection behavior:
If QtyStgPlanID = 00000000, all the data which have the Tdate prior to the Process date If QtyStgPlanID = 00000001, all the data which have the Tdate same or after the Process date If QtyStgPlanID = any other value, all the records will be selected
Does not have any relevance when PeriodOffsetBackward or PeriodOffsetForward is configured to -1.
5. QtyClass
The Class which will be used to populate or select the records in the Qty_Staging table.
6. ExtLogSys
This field has significance only when importing the records into Qty_Staging table. Points to the external logical system from where the data is to be imported.
7. ExtTable
This field has significance only when importing the records into Qty_Staging table. Points to the external database table from where the data is to be imported.
8. ExtTMapping
This field has significance only when exporting the records from Qty_Staging table.
8A. QtyStgTable {@Since 5.4.3}
This field has significance only when exporting the records from Qty_Staging table. The program uses the value as the table name from where the data should be exported. Default table name “qty_staging” is assumed if the field is left empty.
9. ExtPlanIDFieldName
This field has significance only when importing the records into Qty_Staging table. Specifies the RecordType field name in the external database table while importing the data.
10. ExtRecordTypeFieldName
This field has significance only when importing the records into Qty_Staging table. Specifies the RecordType field name in the external database table while importing the data.
11. ExtPlanID
This field has significance only when importing the records into Qty_Staging table. Selects the records with this value of PlanID while importing the data from external database table.
12. ExtRecordType
Indicates the RecordType for the external application (for e.g. Smoothie) while exporting the data.
Selects the records with this value of RecordType while importing the data from external database table.
13. PeriodOffsetBackward
This field has significance only when exporting the records from Qty_Staging table.
If the value is positive, sets the backward offset with the configured value and selects and summarizes the quantity data in that period while exporting.
If the value is -1, selects and summarizes all the data which have the Tdate prior to the Process date.
14. PeriodOffsetForward
This field has significance only when exporting the records from Qty_Staging table.
If the value is positive, sets the forward offset with the configured value and selects and summarizes the quantity data in that period while exporting.
If the value is -1, selects and summarizes all the data which have the Tdate same or after the Process date.
15. IncludeZero
This field has significance only when importing the records into Qty_Staging table. Indicates whether to import the zero values from the external table.
Importing into Qty_Staging¶
The External Program com.adaplink.aldh.ib.ss0004.qtystaging.builder.QtyStagingBuilder uses the TS Mapping configuration to populate the Qty_Staging table in ALDH database.
Make sure to set the Tdate as Process Date while importing the non transaction data like Inventory [RecordType = INV].
Following is the example configuration for importing the data:
Column (1 - 10)
| Mapping Scn | IO Direction | QtyStg RecordType | QtyStg PlanID | Qty Class | Ext LogSys | Ext Table | Ext TMapping | Qty StgTable | ExtPlanID FieldName |
|---|---|---|---|---|---|---|---|---|---|
| ACTIVE | I | DEMH | 00000000 | 20 | A_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | DEMF | 00000001 | 20 | A_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | REF1 | 00000002 | 20 | A_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | REF2 | 00000002 | 20 | A_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | REF3 | 00000002 | 20 | A_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | OPNSO | 00000002 | 20 | B_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | OPNPO | 00000002 | 20 | B_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | INTRS | 00000002 | 30 | B_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | PROMO | 00000000 | 30 | C_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | ADHIS | 00000000 | 30 | D_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | INV | 00000000 | 20 | D_STAGE | sapqtystaging | PlanID | ||
| ACTIVE | I | CURSH | 00000001 | 20 | D_STAGE | sapqtystaging | PlanID |
Column (11 - 16)
ExtRecordType FieldName Ext PlanID Ext RecordType Period Offset Backward Period Offset Forward Include Zero RecordType 00000000 DEMH 0 0 X RecordType 00000001 DEMF 0 0 X RecordType 00000002 REF1 0 0 X RecordType 00000002 REF2 0 0 X RecordType 00000002 REF3 0 0 RecordType 00000002 OPNSO 0 0 RecordType 00000002 OPNPO 0 0 X RecordType 00000002 INTRS 0 0 X RecordType 00000000 PROMO 0 0 RecordType 00000000 ADHIS 0 0 X RecordType 00000000 INV 0 0 X RecordType 00000001 CURSH 0 0 X
In the above mentioned configuration,
Mapping Scenario = ACTIVE IODirection = I
Following are the import actions which will be performed by the builder program,
- Import all the records (including zero quantities) with RecordType = DEMH and PlanID = 00000000 from sapqtystaging table in A_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000000 and RecordType = DEMH
- Import all the records (including zero quantities) with RecordType = DEMF and PlanID = 00000001 from sapqtystaging table in A_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000001 and RecordType = DEMF
- Import all the records (including zero quantities) with RecordType = REF1 and PlanID = 00000002 from sapqtystaging table in A_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000002 and RecordType = REF1
- Import all the records (including zero quantities) with RecordType = REF2 and PlanID = 00000002 from sapqtystaging table in A_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000002 and RecordType = REF2
- Import all the records (only the non zero quantities) with RecordType = REF3 and PlanID = 00000002 from sapqtystaging table in A_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000002 and RecordType = REF3
- Import all the records (only the non zero quantities) with RecordType = OPNSO and PlanID = 00000002 from sapqtystaging table in B_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000002 and RecordType = OPNSO
- Import all the records (including zero quantities) with RecordType = OPNPO and PlanID = 00000002 from sapqtystaging table in B_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000002 and RecordType = OPNPO
- Import all the records (including zero quantities) with RecordType = INTRS and PlanID = 00000002 from sapqtystaging table in B_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 30, PlanID = 00000002 and RecordType = INTRS
- Import all the records (only the non zero quantities) with RecordType = PROMO and PlanID = 00000000 from sapqtystaging table in C_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 30, PlanID = 00000000 and RecordType = PROMO
- Import all the records (including zero quantities) with RecordType = ADHIS and PlanID = 00000000 from sapqtystaging table in D_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 30, PlanID = 00000000 and RecordType = ADHIS
- Import all the records (including zero quantities) with RecordType = INV and PlanID = 00000000 from sapqtystaging table in D_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000000 and RecordType = INV
- Import all the records (including zero quantities) with RecordType = CURSH and PlanID = 00000001 from sapqtystaging table in D_STAGE logical system and populates the Qty_Staging table in ALDH with Class = 20, PlanID = 00000001 and RecordType = CURSH
Exporting from Qty_Staging¶
The application specific External Programs are used to export the data from Qty_Staging table in ALDH database based on TS Mapping configuration. The exported data can be in the form of excel, database etc. as defined by the application where the data needs to be imported again for optimization, forecasting or any other actions.
Following are the partner’s application software for which the programs are developed in AdapLink:
Smoothie¶
Smoothie accepts data in both excel and database format. Whereas the data in excel can be in tabular or cross-tabular format, the database should always be in tabular format.
The external programs com.adaplink.ep.aldh.ob.smoothie.export.tabular.ExportTabularDataToDB and com.adaplink.ep.aldh.ob.smoothie.export.cross.ExportCrossTabDataToExcel are responsible for exporting the data from Qty_Staging table to database and excel file respectively based on the TS Mapping configuration.
Following is an example of TS Mapping configuration for exporting the data,
Column (1 - 10)
| Mapping Scn | IO Direction | QtyStg RecordType | QtyStg PlanID | Qty Class | Ext LogSys | Ext Table | Ext TMapping | Qty StgTable | ExtPlanID FieldName |
|---|---|---|---|---|---|---|---|---|---|
| ACTIVE | O | DEMH | 00000000 | 20 | |||||
| ACTIVE | O | DEMF | 00000001 | 20 | |||||
| ACTIVE | O | REF1 | 00000002 | 20 | |||||
| ACTIVE | O | REF2 | 00000002 | 20 | |||||
| ACTIVE | O | REF3 | 00000002 | 20 | |||||
| ACTIVE | O | OPNPO | 00000002 | 20 | |||||
| ACTIVE | O | OPNSO | 00000002 | 20 | |||||
| ACTIVE | O | INTRS | 00000002 | 20 | qty_staging_detail | ||||
| ACTIVE | O | INV | 00000000 | 20 | qty_staging_detail | ||||
| ACTIVE | O | CURSH | 00000001 | 20 |
Column (11 - 16)
ExtRecordType FieldName Ext PlanID Ext RecordType Period Offset Backward Period Offset Forward Include Zero RecordType DEMH 0 0 RecordType DEMF 0 0 RecordType REF1 0 0 RecordType REF2 0 3 RecordType REF3 2 0 RecordType OPNPO 0 0 RecordType OPNSO 0 0 RecordType INTRS 0 0 RecordType INV -1 -1 RecordType CURSH 0 -1 The following types of records will be exported from Qty_Staging table to Smoothie database for the Mapping Scenario = ACTIVE,
- All the records in Qty_Staging prior to the process date with RecordType = DEMH, Class = 20 and PlanID = 00000000 exported to Smoothie as RecordType DEMH
- All the records in Qty_Staging having Tdate same as process date and after the process date with RecordType = DEMF, Class = 20 and PlanID = 00000001 exported to Smoothie as RecordType DEMF
- All the records in Qty_Staging with RecordType = REF1, Class = 20 and PlanID = 00000002 exported to Smoothie as RecordType REF1
- All the records in Qty_Staging prior to the process date and summarized quantities of 3 time bucket after the process date with RecordType = REF2, Class = 20 and PlanID = 00000002 exported to Smoothie as RecordType REF2
- All the records in Qty_Staging having Tdate same as process date and after the process date and summarized quantities of 2 time bucket prior to the process date with RecordType = REF3, Class = 20 and PlanID = 00000002 exported to Smoothie as RecordType REF3
- All the records in Qty_Staging with RecordType = OPNPO, Class = 20 and PlanID = 00000002 exported to Smoothie as RecordType OPNPO
- All the records in Qty_Staging with RecordType = OPNSO, Class = 20 and PlanID = 00000002 exported to Smoothie as RecordType OPNSO
- All the records in Qty_Staging_Detail with with individual quantities for Detail having RecordType = INTRS, Class = 20 and PlanID = 00000002 exported to Smoothie as RecordType INTRS
- All the records in Qty_Staging_Detail with individual quantities for Detail having any value for Tdate with RecordType = INV, Class = 20 and PlanID = 00000000 exported to Smoothie as RecordType INV
- All the records in Qty_Staging with summarized quantities having Tdate same as process date and after the process date with RecordType = CURSH, Class = 20 and PlanID = 00000001 exported to Smoothie as RecordType CURSH
Key Points¶
1. TS Mapping offset logic for exporting non transactional data to Smoothie:
OffsetBackward = -1 and OffsetForward = 0
Exports all the records from qty_staging where Tdate < ProcessDate
OffsetBackward = 0 and OffsetForward = -1
Exports all the records from qty_staging where Tdate >= ProcessDate
OffsetBackward = -1 and OffsetForward = -1
Exports all the records from qty_staging irrespective of Tdate
