Untitled

EXPLAIN 
INSERT
INTO
	BITEAM_EXP.TRANS_VH ( ID,
	STATE,
	CORE_RESULT,
	BANKCODE,
	AGENT_,
	NAME_,
	PARTNER_RESULT,
	DESCRIPTION,
	TYPE_,
	CREATED,
	GROUP_,
	USER_PAYMENT,
	AMOUNT,
	TECH_DESC,
	ERROR_TYPE,
	ERROR_DESCRIPTION,
	REFERENCE,
	ETL_DATE ) WITH PAID_V4_FILTER AS (
	SELECT
		ID,
		STATE,
		RESULT,
		BANKCODE,
		TYPE_,
		CREATED,
		DEBITOR,
		CREDITOR
	FROM
		UMARKETADM.MS_ALL_TRANS_PAID_V4
	WHERE
		TRUE
		AND STATE IN (2, 6)
		AND ETL_DATE = '2022-09-22'
	ORDER BY
		ID ),
	TAM1 AS (
	SELECT
		T1.ID,
		T1.STATE,
		T1.RESULT,
		T1.BANKCODE,
		T3.AGENT_,
		T3.NAME_,
		CASE
			WHEN T2.PARTNER_RESULT IS NULL THEN 'NULL'
			ELSE T2.PARTNER_RESULT
		END AS PARTNER_RESULT,
		T2.PARTNER_DES,
		T1.TYPE_,
		T1.CREATED,
		T3.GROUP_,
		CASE
			WHEN T3.CREDIT_DEBIT = '+' THEN T1.DEBITOR
			WHEN T3.CREDIT_DEBIT = '-' THEN T1.CREDITOR
		END AS USER_INITIATOR,
		T4.USER_PAYMENT,
		T4.SERVICE_CODE,
		T5.SERVICE_DESCRIPTION,
		T4.AMOUNT,
		T6.REFERENCE
	FROM
		PAID_V4_FILTER AS T1
	LEFT JOIN (
		SELECT
			*
		FROM
			(
			SELECT
				MS_TRANS,
				PARTNER_RESULT,
				PARTNER_DES,
				ROW_NUMBER() OVER (PARTITION BY CAST(MS_TRANS AS INT)
			ORDER BY
				TIME_ DESC) ROWNUM_
			FROM
				OPERATOR_PROD.PARTNER_RESULT_TOTAL
			WHERE
				TRUE
				AND DATE((TIME_)) >= DATE('2022-09-22') - INTERVAL '1 MONTH'
					AND ETL_DATE = DATE('2022-09-22')
				ORDER BY
					MS_TRANS ) AS PARTNER_RESULT_TOTAL
		WHERE
			ROWNUM_ = 1 ) AS T2 ON
		T1.ID = T2.MS_TRANS
	LEFT JOIN (
		SELECT
			TYPE_,
			CREDIT_DEBIT,
			AGENT_,
			NAME_,
			GROUP_
		FROM
			OPERATOR_PROD.DIM_VH_SQI_AGENT_SERVICE_PROD
		WHERE
			'2022-09-22' BETWEEN EFF_START_DATE AND EFF_END_DATE
		ORDER BY
			TYPE_,
			AGENT_ ) AS T3 ON
		TRUE
		AND T3.TYPE_ = T1.TYPE_
		AND SIP_HASH(T3.AGENT_) = (CASE
			WHEN T3.CREDIT_DEBIT = '+' THEN T1.CREDITOR
			WHEN T3.CREDIT_DEBIT = '-' THEN T1.DEBITOR
		END)
	LEFT JOIN (
		SELECT
			ID,
			USER_PAYMENT,
			SERVICE_CODE,
			AMOUNT
		FROM
			BITEAM_EXP.PAID_USER_TRANS
		WHERE
			ETL_DATE = '2022-09-22'
		ORDER BY
			ID ) AS T4
			USING(ID)
	LEFT JOIN BITEAM_EXP.D_SERVICE_LIST AS T5
			USING(SERVICE_CODE)
	LEFT JOIN (
		SELECT
			T.*
		FROM
			(
			SELECT
				*
			FROM
				UMARKETADM.DIM_AGENT_REF
			WHERE
				TRUE
				AND DATE('2022-09-22') BETWEEN EFF_START_DATE AND EFF_END_DATE
					AND NOT IS_DELETED ) AS T ) AS T6 ON
		T4.USER_PAYMENT = T6.BODYID )
SELECT
	TAM1.ID,
	TAM1.STATE,
	TAM1.RESULT,
	TAM1.BANKCODE,
	COALESCE(TAM1.AGENT_,
	TAM1.SERVICE_CODE),
	COALESCE(TAM1.NAME_,
	TAM1.SERVICE_DESCRIPTION),
	TAM1.PARTNER_RESULT,
	(CASE
		WHEN COALESCE(T3.TECH_DESC,
		T4.TECH_DESC) IS NULL THEN TAM1.PARTNER_DES
		ELSE COALESCE(T3.TECH_DESC,
		T4.TECH_DESC)
	END),
	TAM1.TYPE_,
	EXTRACT(EPOCH
FROM
	TAM1.CREATED)::INT,
	TAM1.GROUP_,
	TAM1.USER_PAYMENT,
	TAM1.AMOUNT,
	COALESCE(T3.TECH_DESC,
	T4.TECH_DESC),
	COALESCE(T3.ERROR_TYPE,
	T4.ERROR_TYPE,
	4),
	CASE
		WHEN COALESCE(T3.ERROR_TYPE,
		T4.ERROR_TYPE,
		4) = 1 THEN 'MService'
		WHEN COALESCE(T3.ERROR_TYPE,
		T4.ERROR_TYPE,
		4) = 2 THEN 'Partner'
		WHEN COALESCE(T3.ERROR_TYPE,
		T4.ERROR_TYPE,
		4) = 3 THEN 'Customer'
		WHEN COALESCE(T3.ERROR_TYPE,
		T4.ERROR_TYPE,
		4) = 4 THEN 'Other'
		WHEN COALESCE(T3.ERROR_TYPE,
		T4.ERROR_TYPE,
		4) = 0 THEN 'Success'
	END,
	COALESCE(SUBSTRING(TAM1.REFERENCE::VARCHAR, 0, 4),
	'') || 'xxx' || COALESCE(RIGHT(TAM1.REFERENCE::VARCHAR,
	3),
	''),
	'2022-09-22'
FROM
	TAM1
LEFT JOIN (
	SELECT
		TECH_DESC,
		ERROR_TYPE,
		MSCODE
	FROM
		OPERATOR_PROD.VH_SQI_MAPCODE
	WHERE
		TRUE
		AND ETL_DATE = '2022-09-22'
		AND AGENT = 'UhlPANHkpTapgILgOmC9mQ=='
		AND FUNCTION = 'version2' ) AS T3 ON
	TAM1.RESULT = T3.MSCODE
LEFT JOIN (
	SELECT
		AGENT,
		PARTNERCODE,
		ERROR_TYPE,
		TECH_DESC
	FROM
		OPERATOR_PROD.VH_SQI_MAPCODE
	WHERE
		TRUE
		AND agent <> 'core'
		AND ERROR_TYPE <> 0
		AND FUNCTION = 'version2'
		AND ETL_DATE = '2022-09-22' ) AS T4 ON
	TAM1.PARTNER_RESULT = T4.PARTNERCODE
	AND ENCRYPT_HIGH(TAM1.AGENT_) = T4.AGENT
WHERE
	TRUE