SQL Queries for Cascade Deletion and Customer Detail Extraction
Queries for Cascade Deletion of Customer Records
The following SQL commands are utilized for the cascade deletion of customer records across various tables:
DELETE FROM mdm_smds.CUSTOMER_HISTORY_DETAILS WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_WF_PROCESS_DETAILS WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_SIC_INFORMATION WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_BVD_INFORMATION WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_STATUS_REASONS WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_EXTERNAL_IDENTIFIERS WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_WEB_BL_DETAIL WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_SEGMENT_DETAIL WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_CBU_DETAIL WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_RELATIONSHIPS WHERE parent_customer_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_TAX_REF_INFO WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_BRANDS WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.ADDRESS_CODES WHERE entity_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
DELETE FROM mdm_smds.CUSTOMER_INFORMATION WHERE customer_cmd_code IN (SELECT customer_cmd_code FROM mdm_smds.customer_information WHERE customer_cmd_code ~ '^[0-9]');
Queries Used to Extract Customer Details
SELECT *
FROM (
SELECT
a.trading_name AS ORGANIZATION_NAME,
'' AS Trading_Partner,
'' AS MDG_VENDOR_CODE,
CONCAT(a.country_code, b.external_system_identifier) AS BE_CODE,
a.customer_cmd_code AS CMD_CODE,
address_line1 AS STREET_NAME,
street_no AS STREET_NUMBER,
address_line2 AS APARTMENT_SUITE_FLOOR,
address_line3 AS CITY_SUBAREA,
district AS DISTRICT,
pobox AS PO_BOX,
postal_code AS POSTAL_CODE,
city AS CITY,
state_name AS STATE,
country_name AS COUNTRY,
phone_num AS PHONE,
customer_website AS URL,
c.taxno1 AS TAX_NUMBER1,
c.taxno2 AS TAX_NUMBER2,
c.taxno3 AS TAX_NUMBER3,
c.VATREG AS VAT_REG,
customer_status_cd AS CMD_STATUS
FROM
parquet.dbfs:/mnt/lob_cmd/Replica/customer_information a
LEFT OUTER JOIN
parquet.dbfs:/mnt/lob_cmd/Replica/customer_external_identifiers/ b
ON
a.customer_cmd_code = b.customer_cmd_code
AND b.external_source_system = 'PTR_ALT_CODES.CSTBE'
AND b.date_part = '2023-04-25'
LEFT JOIN (
SELECT *
FROM (
SELECT
customer_cmd_code,
ref_type_code,
REFERENCE_VALUE
FROM
parquet.dbfs:/mnt/lob_cmd/Replica/CUSTOMER_TAX_REF_INFO/
WHERE
ref_type = 'TAX_NUM'
AND is_deleted = 'N'
AND date_part = '2023-04-25'
AND ref_type_code IN ('TAXNO1', 'TAXNO2', 'TAXNO3', 'VATREG')
)
PIVOT (
MAX(REFERENCE_VALUE)
FOR ref_type_code IN ('TAXNO1', 'TAXNO2', 'TAXNO3', 'VATREG')
)
) c
ON
a.customer_cmd_code = c.customer_cmd_code
WHERE
a.date_part = '2023-04-25'
AND CONCAT(a.country_code, b.external_system_identifier) IN (
SELECT _c0
FROM csv.dbfs:/FileStore/shared_uploads/gopala.rajendran@maersk.com/CW_BE_CODE.csv
WHERE _c0 <> 'CMD Code'
)
)
ORDER BY 5;
Query to Generate the Workflow Report
CREATE OR REPLACE VIEW public.vw_workflow AS SELECT a1.customercode, CASE WHEN a2.trading_name IS NOT NULL THEN a2.trading_name ELSE TRIM('"' FROM (a4.customer_summary::json->'tradingName')::text) END AS trading_name, a1.cluster, CASE WHEN a2.country_code IS NOT NULL THEN a2.country_code ELSE TRIM('"' FROM (customer_summary::json->'countryCode')::text) END AS country_code, CASE WHEN a2.city IS NOT NULL THEN a2.city ELSE TRIM('"' FROM (customer_summary::json->'city')::text) END AS city, a1.creator, a1.user_comments, a1.workflow_type, a1.requesttype, CASE WHEN a1.workflow_status = 'N'::bpchar THEN 'Completed'::text WHEN a1.workflow_status = 'Y'::bpchar THEN 'Open'::text ELSE ''::text END AS workflow_status, a3.ads_owner, CASE WHEN a1.adsapprove = 'Approve'::text THEN 'Approve'::text WHEN a1.adsreject = 'Reject'::text THEN 'Reject'::text ELSE ''::text END AS ads_action, DATE_PART('week'::text, a3.ads_start_time_)::character varying AS week_number, a3.ads_start_time_, a3.ads_end_time_, (a3.ads_start_time_ - a3.ads_end_time_)::character varying AS ads_duration, a1.adscomments AS ads_user_comments, a3.finance_owner, CASE WHEN a1.financeapprove = 'Approve'::text THEN 'Approve'::text WHEN a1.financereject = 'Reject'::text THEN 'Reject'::text ELSE ''::text END AS finance_action, a3.finance_start_time_, a3.finance_end_time_, (a3.finance_start_time_ - a3.finance_end_time_)::character varying AS finance_duration, a1.financecomments AS finance_user_comments, a1.sourcesystemname, a1.process_id FROM (SELECT cwpd.process_id, cwpd.customer_cmd_code AS customercode, cwpd.workflow_state AS workflow_status, cwpd.comments AS user_comments, MAX(CASE WHEN ahv.name_::text = 'ADSRole'::text THEN ahv.text_ ELSE ''::character varying END::text) AS cluster, MAX(CASE WHEN ahv.name_::text = 'requesterId'::text THEN ahv.text_ ELSE ''::character varying END::text) AS creator, MAX(CASE WHEN ahv.name_::text = 'workflowIndicator'::text THEN ahv.text_ ELSE ''::character varying END::text) AS workflow_type, MAX(CASE WHEN ahv.name_::text = 'requestType'::text THEN ahv.text_ ELSE ''::character varying END::text) AS requesttype, MAX(CASE WHEN ahv.name_::text = 'contactCode'::text THEN ahv.text_ ELSE ''::character varying END::text) AS contactcode, MAX(CASE WHEN ahv.name_::text = 'sourceSystemName'::text THEN ahv.text_ ELSE ''::character varying END::text) AS sourcesystemname, MAX(CASE WHEN ahv.name_::text = 'financeRole'::text THEN ahv.text_ ELSE ''::character varying END::text) AS financerole, MAX(CASE WHEN ahv.name_::text = 'ADSComments'::text THEN ahv.text_ ELSE ''::character varying END::text) AS adscomments, MAX(CASE WHEN ahv.name_::text = 'ADSApprove'::text AND ahv.long_ = 1 THEN 'Approve'::text ELSE ''::text END) AS adsapprove, MAX(CASE WHEN ahv.name_::text = 'ADSReject'::text AND ahv.long_ = 1 THEN 'Reject'::text ELSE ''::text END) AS adsreject, MAX(CASE WHEN ahv.name_::text = 'FinanceComments'::text THEN ahv.text_ ELSE ''::character varying END::text) AS financecomments, MAX(CASE WHEN ahv.name_::text = 'FinanceApprove'::text AND ahv.long_ = 1 THEN 'Approve'::text ELSE ''::text END) AS financeapprove, MAX(CASE WHEN ahv.name_::text = 'FinanceReject'::text AND ahv.long_ = 1 THEN 'Reject'::text ELSE ''::text END) AS financereject FROM mdm_smds.customer_wf_process_details cwpd, act_hi_varinst ahv WHERE cwpd.process_id::text = ahv.proc_inst_id_::text GROUP BY cwpd.process_id, cwpd.customer_cmd_code, cwpd.workflow_state, cwpd.comments) a1 JOIN mdm_smds.customer_wf_process_details a4 ON a1.process_id = a4.process_id LEFT OUTER JOIN mdm_smds.customer_information a2 ON a1.customercode::text = a2.customer_cmd_code::text JOIN (SELECT ahh.proc_inst_id_, MAX(CASE WHEN ahh.name_::text = 'ADS Approver'::text THEN ahh.owner_ ELSE ''::character varying END::text) AS ads_owner, MAX(CASE WHEN ahh.name_::text = 'ADS Approver'::text THEN ahh.start_time_ ELSE NULL::timestamp without time zone END) AS ads_start_time_, MAX(CASE WHEN ahh.name_::text = 'ADS Approver'::text THEN ahh.end_time_ ELSE NULL::timestamp without time zone END) AS ads_end_time_, MAX(CASE WHEN ahh.name_::text = 'Finance Approver'::text THEN ahh.owner_ ELSE ''::character varying END::text) AS finance_owner, MAX(CASE WHEN ahh.name_::text = 'Finance Approver'::text THEN ahh.start_time_ ELSE NULL::timestamp without time zone END) AS finance_start_time_, MAX(CASE WHEN ahh.name_::text = 'Finance Approver'::text THEN ahh.end_time_ ELSE NULL::timestamp without time zone END) AS finance_end_time_ FROM act_hi_taskinst ahh GROUP BY ahh.proc_inst_id_) a3 ON a1.process_id::text = a3.proc_inst_id_::text WHERE a3.ads_start_time_ > (CURRENT_DATE - 365) ORDER BY a3.ads_start_time_ DESC;