Target Database Table
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|
|drp||integration_mapping||database||Ensure unique records|
|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|
To View Larger Image, Right Click and Choose Open Image In New Tab.
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|
All content © Daasity 2021. Do not copy, share or distribute.