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;