LOCKING ROW FOR ACCESS
SELECT
distinct pck.communication_id,
'В кампании  [{1}] существует предложение, назначенное нескольким сегментам или сплитам.' as error_txt,
83 as RULE_NUM
 from ${TRMMETADB}.CM_COMM_PACKAGE_COLLATERAL as pck
where pck.Communication_Id=:i_communication_id
group by pck.communication_id, pck.collateral_id ,pck.step_id
having count(1) > 1
;

REPLACE VIEW ${TRMVIEWSDB}.V_RESPONSE_MSISDN
AS
LOCKING ROW FOR ACCESS
SELECT
 RH.Communication_Id
, CH.BRANCH_CODE
, CH.SUBS_ID
, SC.MSISDN
, COALESCE(DL.COMMUNICATION_NAME, COM.Name) AS Communication_Name
, CH.Collateral_Id
, COALESCE(DL.Uc_messageName, COL.Name) AS Collateral_Name
,CASE
			WHEN CH.BRANCH_CODE = 'FE' THEN CH.Selection_Dttm - INTERVAL '7' HOUR
			WHEN CH.BRANCH_CODE = 'SB' THEN CH.Selection_Dttm - INTERVAL '3' HOUR
			WHEN CH.BRANCH_CODE = 'UR' THEN CH.Selection_Dttm - INTERVAL '2' HOUR
		ELSE CH.Selection_Dttm
 END AS Start_Dttm
 ,CASE
			WHEN CH.BRANCH_CODE = 'FE' THEN CH.Step_Dttm - INTERVAL '7' HOUR
			WHEN CH.BRANCH_CODE = 'SB' THEN CH.Step_Dttm - INTERVAL '3' HOUR
			WHEN CH.BRANCH_CODE = 'UR' THEN CH.Step_Dttm - INTERVAL '2' HOUR
		ELSE CH.Step_Dttm
 END AS Step_Dttm
 ,CASE
			WHEN CH.BRANCH_CODE = 'FE' THEN CL.Uc_Request_Time - INTERVAL '7' HOUR
			WHEN CH.BRANCH_CODE = 'SB' THEN CL.Uc_Request_Time - INTERVAL '3' HOUR
			WHEN CH.BRANCH_CODE = 'UR' THEN CL.Uc_Request_Time - INTERVAL '2' HOUR
		ELSE CL.Uc_Request_Time
 END AS Uc_Request_Time
, MD.Domain_Value_Name AS Channel_Name
, MIN(CASE WHEN Channel_Name='Unknown' THEN IR.Event_DTTM ELSE NULL END) AS Response0_Time_msk
 ,CASE
			WHEN CH.BRANCH_CODE = 'FE' THEN Response0_Time_msk - INTERVAL '7' HOUR
			WHEN CH.BRANCH_CODE = 'SB' THEN Response0_Time_msk - INTERVAL '3' HOUR
			WHEN CH.BRANCH_CODE = 'UR' THEN Response0_Time_msk - INTERVAL '2' HOUR
		ELSE Response0_Time_msk
 END AS Response0_Time
, MIN(CASE WHEN CR.Uc_FeedBack_type=1 THEN Uc_Response_time ELSE NULL END) AS Response1_Time_msk
 ,CASE
			WHEN CH.BRANCH_CODE = 'FE' THEN Response1_Time_msk - INTERVAL '7' HOUR
			WHEN CH.BRANCH_CODE = 'SB' THEN Response1_Time_msk - INTERVAL '3' HOUR
			WHEN CH.BRANCH_CODE = 'UR' THEN Response1_Time_msk - INTERVAL '2' HOUR
		ELSE Response1_Time_msk
 END AS Response1_Time
, MIN(CASE WHEN CR.Uc_FeedBack_type=2 THEN Uc_Response_time ELSE NULL END) AS Response2_Time_msk
 ,CASE
			WHEN CH.BRANCH_CODE = 'FE' THEN Response2_Time_msk - INTERVAL '7' HOUR
			WHEN CH.BRANCH_CODE = 'SB' THEN Response2_Time_msk - INTERVAL '3' HOUR
			WHEN CH.BRANCH_CODE = 'UR' THEN Response2_Time_msk - INTERVAL '2' HOUR
		ELSE Response2_Time_msk
 END AS Response2_Time
, MIN(CASE WHEN CR.Uc_FeedBack_type=3 THEN Uc_Response_time ELSE NULL END) AS Response3_Time_msk
 ,CASE
			WHEN CH.BRANCH_CODE = 'FE' THEN Response3_Time_msk - INTERVAL '7' HOUR
			WHEN CH.BRANCH_CODE = 'SB' THEN Response3_Time_msk - INTERVAL '3' HOUR
			WHEN CH.BRANCH_CODE = 'UR' THEN Response3_Time_msk - INTERVAL '2' HOUR
		ELSE Response3_Time_msk
 END AS Response3_Time
, MIN(CASE WHEN Channel_Name='Unknown' THEN GR.LAST_EFF_DATE ELSE NULL END) AS Response3_Product_Time --LAST_EFF_DATE has been converted before
FROM
 ${TRMLEADSDB}.LH_RUN_HISTORY RH
 INNER JOIN ${TRMMETADB}.CM_COMMUNICATION COM ON COM.Communication_Id = RH.Communication_Id
 INNER JOIN ${TRMLEADSDB}.LH_COLLATERAL_HISTORY CH ON
  CH.Communication_Id = RH.Communication_Id AND
  CH.Step_Dttm = RH.Process_Dttm
 INNER JOIN ${TRMMETADB}.CM_COLLATERAL COL ON
  COL.Collateral_Id = CH.Collateral_Id
 INNER JOIN ${TRMVIEWSDB}.DMV_SUBS_CURRENT SC ON
  SC.BRANCH_CODE = CH.BRANCH_CODE AND
  SC.SUBS_ID = CH.SUBS_ID
 LEFT JOIN ${TRMLEADSDB}.LHCI_CURRENT_DELIVERY DL ON
  DL.Communication_Id = RH.Communication_Id AND
  DL.Communication_Process_Dttm = RH.Process_Dttm AND
  DL.Run_Id = RH.Run_Id AND
  DL.Collateral_Id = CH.Collateral_Id AND
  DL.Comm_Plan_Id = CH.Comm_Plan_Id AND
  DL.Step_Id = CH.Step_Id
 LEFT JOIN ${TRMLEADSDB}.LHCI_CURRENT_LEAD CL ON
  CL.Uc_Job_Id = DL.Uc_Job_Id AND
  CL.Collateral_Id = DL.Collateral_Id AND
  CL.Subs_Id = CH.Subs_Id AND
  CL.Branch_Code = CH.Branch_Code AND
  CL.Selection_Group_Ord = CH.Selection_Group_Ord
  JOIN ${TRMMETADB}.MDCI_DOMAIN MD ON MD.Domain_Name='Uc_Channel_type'
  LEFT JOIN ${TRMLEADSDB}.LHCI_INCOMING_RECEIPT IR ON
   IR.MSISDN = CL.MSISDN AND
   IR.Uc_Job_Id = DL.Uc_Job_Id 
 LEFT JOIN ${TRMLEADSDB}.LHCI_INCOMING_RESPONSE CR ON
   CR.MSISDN = CL.MSISDN AND
   CR.Uc_Job_Id = DL.Uc_Job_Id AND
   CR.Uc_Channel_Type = MD.Domain_Value_Name
  LEFT JOIN ${TRMLEADSDB}.LHCI_GENERATED_RESPONSES GR ON
   GR.BRANCH_CODE=CH.BRANCH_CODE AND
   GR.SUBS_ID=CH.SUBS_ID AND
   GR.Selection_Dttm=CH.Selection_Dttm AND
   GR.Step_Id=CH.Step_Id AND
   GR.Selection_Group_Ord=CH.Selection_Group_Ord AND
   GR.Communication_Id=ch.Communication_Id
WHERE ch.branch_code = 'FE'   
AND  CAST((ch.Step_Dttm ) AS DATE AT TIME ZONE INTERVAL '3:00' HOUR TO MINUTE ) BETWEEN CAST(('2016-04-19') AS DATE) AND CAST(('2016-04-19') AS DATE)
AND ch.subs_id = 19658120   
HAVING COALESCE(Response0_Time,Response1_Time,Response2_Time,Response3_Time) IS NOT NULL OR (Channel_Name='Unknown')
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
;

REPLACE VIEW ${TRMVIEWSDB}.DIMV_INQGR
AS
LOCKING ROW FOR ACCESS
SELECT 
	DICT_ID AS INQGR_ID,
	DICT_NAME AS INQGR_NAME
FROM
	${TRMDATASRCDB}.DMT_DICTIONARY
WHERE
	DICT_TYPE_ID=19
	AND data_end_date=DATE'9999-12-31'
	AND end_date>CURRENT_DATE
QUALIFY ROW_NUMBER() OVER(PARTITION BY INQGR_ID ORDER BY INQGR_NAME) = 1;


INSERT INTO ${TRMDATASRCDB}.DMT_MSISDN_SUBS_NEW
SELECT	m.DATA_BEGIN_DATE, m.DATA_END_DATE, m.BRANCH_CODE, m.SUBS_ID, m.MSISDN,
		m.NCAT_ID, m.UPDATE_TS, m.FILE_SK, m.BILLING_SUBS_ID, COALESCE(t.billing_filial_id, -1) AS billing_filial_id
FROM	${TRMDATASRCDB}.DMT_MSISDN_SUBS m 
LEFT OUTER JOIN (SELECT branch_code, subs_id, billing_filial_id 
									FROM ${TRMDATASRCDB}.DMT_SUBS_DAILY
									QUALIFY ROW_NUMBER()  OVER (PARTITION BY branch_code, subs_id ORDER BY data_end_date DESC) = 1
									) AS t
				ON m.branch_code  = t.branch_code 
					AND m.subs_id = t.subs_id;
.IF ERRORCODE != 0 THEN .EXIT ERRORCODE;