Hypercare Support handbook(MVP 1.0)

SQL Queries

1. Introduction

• Purpose: This section contains SQL queries which can be used during the Hypercare period for the Alibaba release.

2. Countries in Scope

• List of Countries: CN, US, GB, MX, DE, BR

3. SQL Queries

3.1 Country Generic Rules

• Country Generic Rules Table: SELECT * FROM mdm_smdsmd.SMDS_generic_country_rules WHERE country_iso_code = 'CN';
• SCPI Rules Table: SELECT * FROM mdm_smdsmd.scpi_country_rules WHERE iso_country_code = 'CN';

3.2 SAP Tax Code Mappings

• Query to Check SAP Tax Code Mappings: SELECT * FROM mdm_smds.ref_tax_type_local_name WHERE country_iso_code = 'CN';

3.3 Phone Validation

• Query to Check Phone Validation Tracker Table: SELECT * FROM mdm_smdsmd.phone_validation_tracker WHERE phone_num = '1985346578';

3.4 ALB Code and Customer Data

• Query to Find the ALB Code Linked to a Customer: SELECT * FROM mdm_smds.customer_external_identifiers WHERE external_source_system = 'PTR_ALT_CODES.CSTALB' AND customer_cmd_code = 'US04577934' AND is_deleted = 'N';
• Query to Know a Customer Linked to an ALB ID: SELECT * FROM mdm_smds.customer_external_identifiers WHERE external_system_identifier = '5111028' AND external_source_system = 'PTR_ALT_CODES.CSTALB' AND is_deleted = 'N';
• Query to Check the Total Count of ALB IDs Created: SELECT COUNT(*) FROM mdm_smds.customer_external_identifiers WHERE external_source_system = 'PTR_ALT_CODES.CSTALB' AND is_deleted = 'N';

3.5 Customer EMP Outbound

• Query to Check Customer EMP Outbound: SELECT * FROM mdm_smdsmd.Producer_customer_traffic WHERE customer_code = 'HK00959932';

3.6 Customer Publication Status

• Query to Check if a Customer Failed to Publish: SELECT * FROM mdm_smdsmd.producer_sync_track WHERE code = 'HK00959932' AND entity_type = 'CUST';

3.7 Workflow and Notification Tracking

• Query to Check the Workflow Notification Tracker: SELECT * FROM mdm_smdsmd.notification_tracker WHERE entity_code = 'DE01477102';
• Query to Check if a Customer is Processed to Elastic: SELECT * FROM mdm_smdsmd.pg_elk_sync_trck WHERE code = 'DE01477102' AND entity_type = 'CUST';
• Query to Check the Workflow Status of a Customer: SELECT * FROM vw_workflow_details WHERE customer_cmd_code = 'DE01477102';

3.8 ALB Code De-linking

• Steps to De-link an ALB Code from a Customer:
  1. Update the is_deleted Flag: UPDATE mdm_smds.customer_external_identifiers SET is_deleted = 'Y', update_time = current_timestamp, update_user = {cx person email}, source_of_last_update = 'PRTL' WHERE row_id = '74206862' AND customer_cmd_code = 'US04577934' AND external_source_system = 'PTR_ALT_CODES.CSTALB' AND external_system_identifier = 'FD3300' AND is_deleted = 'N';
  2. Update Audit Information: UPDATE mdm_smds.customer_information SET update_time = current_timestamp, update_user = {cx person email}, source_of_last_update = 'PRTL' WHERE customer_cmd_code = 'US04577934';
  3. Run Auto-sync and Publish API:
     • Ensure the auto-sync process is triggered and the Publish API is called to reflect the changes.