Bitte auf english die folgende Reconciliation der Buchungen Beschreiben für die Wiki…
- User
Bitte auf english die folgende Reconciliation der Buchungen Beschreiben für die Wiki Dokumentation: WITH il_recon_src AS (
SELECT tl.tech_supply_id, tl.tech_log_id, t.fiscal_period, t.tech_period_lock, count(*) AS src_count, sum(transaction_currency_amount) AS src_sum FROM DEV_GAD_DB.TRANSITION_LAYER.TL_GERAS_PREMIUM AS tl INNER JOIN DEV_GAD_DB.TECH_UTIL.TU_SUPPLY_IDS_CURRENT_STATUS AS t ON t.tech_source_system = 'GERAS_PREMIUM' AND t.tech_status = 3010 AND tl.tech_supply_id = t.tech_supply_id GROUP BY tl.tech_supply_id, tl.tech_log_id, t.fiscal_period, t.tech_period_lock),
il_recon_tar AS (
SELECT b.tech_supply_id, b.tech_log_id, t.fiscal_period, count(*) AS tar_count, sum(transaction_currency_amount) AS tar_sum FROM DEV_GAD_DB.INTEGRATION_LAYER.TMP_IL_GERAS_PREMIUM_BASE AS b INNER JOIN DEV_GAD_DB.TECH_UTIL.TU_SUPPLY_IDS_CURRENT_STATUS AS t ON t.tech_source_system = 'GERAS_PREMIUM' AND t.tech_status = 3010 AND b.tech_supply_id = t.tech_supply_id GROUP BY b.tech_supply_id, b.tech_log_id, t.fiscal_period),
recon_il_joined AS (
SELECT src.tech_supply_id, src.tech_log_id, src.fiscal_period, src.tech_period_lock, src.src_count, src.src_sum, tar.tar_count, tar.tar_sum FROM il_recon_src AS src LEFT JOIN il_recon_tar AS tar ON src.tech_supply_id = tar.tech_supply_id),
dqm_reconcile AS (
SELECT tech_supply_id, tech_log_id, fiscal_period, tech_period_lock, src_count, src_sum, tar_count, tar_sum, CASE WHEN src_count = tar_count THEN 100 ELSE -1 END AS row_num_qs, CASE WHEN src_sum = tar_sum THEN 100 ELSE -1 END AS amount_qs, least(row_num_qs, amount_qs) as total_qs, CASE WHEN total_qs = 100 THEN 3100 ELSE 3099 END AS tech_status FROM recon_il_joined ORDER BY 1),
final as (
SELECT TECH_UTIL.SEQ_RECON.nextval as tech_recon_id, tech_supply_id, tech_log_id, 'GERAS_PREMIUM' as tech_source_system, fiscal_period, tech_status, current_timestamp() as tech_inserted_at_ts, null as tech_delete_flag, null as tech_calculation_type, tech_period_lock, src_count, src_sum, tar_count, tar_sum, row_num_qs , amount_qs , total_qs FROM dqm_reconcile)
SELECT * FROM final
Assistant
Here's a clear documentation of the reconciliation query for the Wiki:
Booking Reconciliation Process Documentation
Overview
This query performs a reconciliation check between source data in the TRANSITION_LAYER and the enriched data in the INTEGRATION_LAYER for GERAS_PREMIUM bookings. It verifies both record counts and sum totals to ensure data integrity during the currency enrichment process.
Process Flow
1. Source Data Collection (
il_recon_src)- Extracts booking records from
TL_GERAS_PREMIUM(TRANSITION_LAYER) - Filters for records with status 3010 ("ready for reconciliation")
- Groups by
tech_supply_id,tech_log_id, andfiscal_period - Calculates:
- Record count (
src_count) - Sum of transaction amounts (
src_sum)
- Record count (
2. Target Data Collection (
il_recon_tar)- Extracts enriched records from
TMP_IL_GERAS_PREMIUM_BASE(INTEGRATION_LAYER) - Applies the same status filter (3010)
- Groups by the same technical identifiers
- Calculates:
- Record count (
tar_count) - Sum of transaction amounts (
tar_sum)
- Record count (
3. Comparison and Quality Scoring (
dqm_reconcile)Assigns quality scores based on two criteria:
- Row Number Check (
row_num_qs):- 100 if source count equals target count
- -1 if counts differ
- Amount Check (
amount_qs):- 100 if source sum equals target sum
- -1 if sums differ
- Total Quality Score (
total_qs):- Takes the minimum of both quality scores
- Status Assignment (
tech_status):- 3100 if all checks pass (total_qs = 100)
- 3099 if any check fails
4. Final Record Creation (
final)Creates reconciliation records with:
- Unique reconciliation ID (
tech_recon_id) - Technical identifiers (
tech_supply_id,tech_log_id) - Source system identifier ('GERAS_PREMIUM')
- Fiscal period and lock status
- Source and target counts/sums
- Quality scores
- Timestamp and technical flags
Status Codes
- 3010: Records ready for reconciliation
- 3099: Reconciliation failed
- 3100: Reconciliation successful
Key Metrics
The reconciliation checks two key aspects:
- Record Completeness: Ensures no records are lost or duplicated
- Amount Accuracy: Verifies that transaction amounts remain unchanged
Usage
This reconciliation step is crucial for:
- Validating the currency enrichment process
- Ensuring data integrity
- Tracking processing status
- Quality assurance of financial data
Technical Notes
- Uses sequence
SEQ_RECONfor unique reconciliation IDs - Maintains audit trail through technical timestamps
- Preserves period lock information
- Allows for future extension through nullable technical fields
Would you like me to expand on any particular aspect of this documentation?
- Extracts booking records from
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.