Balance Dimensions and Attribute Contexts for an Extract Record
When you create an HCM Extract Definition for payroll balance reporting, create extract attribute records that extract the correct balance dimensions.
Attributes are the individual fields inside the extract record. To retrieve the balance dimension values, select the attribute Type in the record as Procedure element.
Define a Run dimension in the Balance Group Usage you are using. It can be any Run dimension, for example, Assignment Tax Unit Run.
When balance group usage is of matrix type all dimensions are included in the same Data Group. However, if you have period-to-date (PTD) dimensions, they are included in a separate XML data group. You must include this data group in the extract definition and use short codes for the different periods, such as, run, month, quarter, year, and so on.
Short Code Details
Create an attribute for each context within the balance group usage. The created attributes should have a name and a short code. For example, Name of Attribute is Payroll Action ID and the corresponding short code is PAYROLL_ACTION_ID.
| Name of Attribute | Short Code |
|---|---|
| Payroll Relationship Action Identifier | PAYROLL_REL_ACTION_ID |
| Calculation Breakdown Identifier | CALC_BREAKDOWN_ID |
| Base Balance Name | BASE_BALANCE_NAME |
| Run | RUN |
| Reporting Name | REPORTING_NAME |
| Base Category Name | BASE_CATEGORY_NAME |
| Tax Unit Identifier | TAX_UNIT_ID |
| Balance Type Identifier | BALANCE_TYPE_ID |
| Year | YEAR |
| Location | LOCATION |
| Department | DEPARTMENT |
| Dimension Level | DIMENSION_LEVEL |
| GUID | GUID |
| Balance Category Identifier | BALANCE_CATEGORY_ID |
| Month | MONTH |
| Balance Value | BALANCE_VALUE |
| Base DB Item Suffix | BASE_DB_ITEM_SUFFIX |
| Base Dimension Name | BASE_DIMENSION_NAME |
| Period | PERIOD |
| Payroll Assignment Identifier | PAYROLL_ASSIGNMENT_ID |
| Reference Code | REFERENCE_CODE |
| Quarter | QUARTER |
| Payroll Term Identifier | PAYROLL_TERM_ID |
| Other | OTHER |
| Fiscal Quarter | FQUARTER |
| Fiscal Year | FYEAR |
| Half Year | HYear |
| Lifetime | LIFETIME |
| Payment | PAYMENT |
| Rolling 12 Months | R12MTH |
| Balance T2MTD | T2MONTHS |
| Balance T2WTD | T2WEEKS |
| Balance THYTD | THYEAR |
| Balance T4WTD | T4WEEKS |
| Balance TMTD | TMONTH |
| Balance TQTD | TQUARTER |
| Balance TWTD | TWEEK |
| Balance TYTD | TYEAR |
| Balance Unit Year to Date | UYEAR |
| Area1 | AREA1 |
| Area2 | AREA2 |
| Area3 | AREA3 |
| Area4 | AREA4 |
| Balance Date | BALANCE DATE |
| Date Earned | DATE_EARNED |
| Deduction Type | DEDUCTION_TYPE |
| Element Entry Identifier | ELEMENT_ENTRY_ID |
| Deduction Card Identifier | DEDUCTION_CARD_ID |
| Insurance Type | INSURANCE_TYPE |
| Pension Type | PENSION_TYPE |
| Process Type | PROCESS_TYPE |
| Rate Type | RATE_TYPE |
| Reference Code2 | REFERENCE_CODE2 |
| Reference Number | REFERENCE_NUMBER |
| Reporting Time Period Identifier | REPORTING_TIME_PERIOD_ID |
| Resident Area1 | RESIDENT_AREA1 |
| Resident Area2 | RESIDENT_AREA2 |
| Resident Area3 | RESIDENT_AREA3 |
| Statutory Report Code | STATUTORY_REPORT_CODE |
| Statutory Report Type | STATUTORY_REPORT_TYPE |
| Third Party Payee Identifier | THIRD_PARTY_PAYEE_ID |
| Time Definition Identifier | TIME_DEFINITION_ID |
| HR Assignment Identifier | HR_ASSIGNMENT_ID |
| Assignment Number | ASSIGNMENT_NUMBER |
Contexts for a Balance Group Usage
Use this SQL query to know the contexts required for a particular balance group usage and create attribute for each context. Attributes created should have attribute name and short code as BASE_CONTEXT_NAME in this query output.
SELECT DISTINCT
ctx.base_context_name, pbgu.base_group_usage_nameFROM
pay_bal_grp_usages pbgu pay_bal_grp_inclusions pbgi pay_balance_attributes pba, pay_defined_balances pdb, pay_balance_dimensions pbd, pay_dim_context_usages pdcu, ff_contexts_b ctx pbgu.balance_group_id = pbgi.balance_group_id AND pbgi.attribute_id = pba.attribute_id AND pba.defined_balance_id = pdb.defined_balance_id AND pdb.balance_dimension_id = pbd.balance_dimension_id AND pbd.balance_dimension_id = pdcu.balance_dimension_id AND pdcu.context_id = ctx.context_id AND pbgu.base_group_usage_name = <base balance group usage name>;