View: Intermediary DRP Table(s): drp.discount_code_mapping

Source Database Table

drp.discount_code_mapping

Script that Populates Database Table

3020_CHN_BAS_discount_code_mapping.sql

Script Description and Logic

Pulls order-related fields and discount codes for all orders with discount codes and checks against the BSD to check which channel the discount code is mapped to. All discount codes in the primary source table, bsd.discount_code_mapping, are included and reported by unique order id and discount code combination. From the bsd.discount_code_mapping table a lookup is performed to see if any discount codes use a wildcard identifier, this list is stored as wildcard_codes. Next a list of orders is created from uos.order_discount_codes and then joined to wildcard_codes, orders and integration_mapping to map the wildcard codes to orders that had discount codes. The resulting table is stored as wildcard_mapping. Next a list of direct discount codes is generated and duplicates are removed, the resulting table is stored as direct_codes. Direct_codes is then joined to orders, integration_mapping and order_discount_codes to map direct discount codes in the BSD to orders that had discount codes. The resulting table is stored as direct_mapping. Wildcard_mapping and direct_mapping are then combined and inserted into drp_staging.discount_code_mapping. All records are removed from the final table,drp.discount_code_mapping, and updated by way of insertion from the query (upsert). An additional insert into drp_staging.order_channel_source is then performed for use in later processes.

Source Tables Used In Script

Schema	Table (or Derived Table) Name	Table Type	Purpose
bsd	discount_code_mapping	database	Primary
uos	order_discount_codes	database	Lookup
drp	integration_mapping	database	Ensure unique records
drp_staging	discount_code_mapping	database	Pre-insert
N/A	wildcard_codes	derived	Pulls a list of wildcard discount codes from BSD
N/A	wildcard_mapping	derived	Map wildcard codes from the BSD to orders with discount codes
N/A	dedup_direct_codes	derived	Pulls a list of all discount codes from the BSD, duplicates are row ordered
N/A	direct_codes	derived	Pulls a unique list of discount codes from dedup_direct_codes
N/A	direct_mapping	derived	Maps the direct codes from the BSD to orders that had discount codes attached

SQL Flow

Calculated and Derived Fields

Target Column	Target Column Data Type	Source schema.table	Source Column	Transformation/Logic
order_channel_id	CHAR	uos.order_discount_codes	order_id, __shop_id	Combination of __shop_id, order_id and a text label of discount code mapping to identify records