SQLize Online / PHPize Online  /  SQLtest Online

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! SELECT V760.REGION ,V760.MVS_CNTLCARD ,V760.EOM_BUILD ,CASE WHEN DAY(CURRENT_DATE ()) <= V760.EOM_BUILD THEN DATEADD(MONTH, - 1, CAST(CONCAT ( CAST(YEAR(CURRENT_DATE ()) AS VARCHAR) ,'-' ,CAST(MONTH(CURRENT_DATE ()) AS VARCHAR) ,'-01' ) AS DATE)) ELSE CAST(CONCAT ( CAST(YEAR(CURRENT_DATE ()) AS VARCHAR) ,'-' ,CAST(MONTH(CURRENT_DATE ()) AS VARCHAR) ,'-01' ) AS DATE) END BUILD_START_DT ,V760.LAST_UPDATE_271_TIMESTAMP FROM NTWK_RGLTY_RPTG.TCTD760_271_JOB_CONTROL V760 WHERE V760.REGION = 'SOUTHWEST' AND V760.MVS_CNTLCARD = 'INS260D0'; DELETE FROM NTWK_RGLTY_RPTG.TSTD260_271_POSTED_SVC_ORD_LVL WHERE POSTED_SVC_ORD_POST_DT >= ( SELECT CASE WHEN DAY(CURRENT_DATE ()) <= V760.EOM_BUILD THEN DATEADD(MONTH, - 1, CAST(CONCAT ( CAST(YEAR(CURRENT_DATE ()) AS VARCHAR) ,'-' ,CAST(MONTH(CURRENT_DATE ()) AS VARCHAR) ,'-01' ) AS DATE)) ELSE CAST(CONCAT ( CAST(YEAR(CURRENT_DATE ()) AS VARCHAR) ,'-' ,CAST(MONTH(CURRENT_DATE ()) AS VARCHAR) ,'-01' ) AS DATE) END BUILD_START_DT FROM NTWK_RGLTY_RPTG.TCTD760_271_JOB_CONTROL V760 WHERE V760.REGION = 'SOUTHWEST' AND V760.MVS_CNTLCARD = 'INS260D0' ) OR POSTED_SVC_ORD_MATCHED_DT >= ( SELECT CASE WHEN DAY(CURRENT_DATE ()) <= V760.EOM_BUILD THEN DATEADD(MONTH, - 1, CAST(CONCAT ( CAST(YEAR(CURRENT_DATE ()) AS VARCHAR) ,'-' ,CAST(MONTH(CURRENT_DATE ()) AS VARCHAR) ,'-01' ) AS DATE)) ELSE CAST(CONCAT ( CAST(YEAR(CURRENT_DATE ()) AS VARCHAR) ,'-' ,CAST(MONTH(CURRENT_DATE ()) AS VARCHAR) ,'-01' ) AS DATE) END BUILD_START_DT FROM NTWK_RGLTY_RPTG.TCTD760_271_JOB_CONTROL V760 WHERE V760.REGION = 'SOUTHWEST' AND V760.MVS_CNTLCARD = 'INS260D0' ); INSERT INTO NTWK_RGLTY_RPTG.TSTD260_271_POSTED_SVC_ORD_LVL ( SVC_ORD_POST_DT ,SVC_ORD_DIV_CD ,SVC_ORD_NBR ,TBL_LOAD_TS ,CKT_END_PATRON_LOC ,CKT_END_WFA_WIRE_CTR_CD ,CKLEND ,CTR ,ULS_CD ,WHSLE_IND ,DATA_RSTRCTN_CD ,ORDCLSFN_271 ,LSPID ,AECN_271 ,CLEC_MKT ,SCMG_CD ,SCMG_SUB_CD ,POSTED_SVC_ORD_MATCHED_DT ,EX_CD ,DUE_DATE_CD ,APP_DT ,DUE_DT ,SUBSQNT_DUE_DT ,COMPLTN_DT ,DSRD_DUE_DT ,SM_DT ,SM_CD ,DSL_CONDG_IND ,EXPEDITE_IND ,FW_IND ,COOP_ACCEPT_TSTG_IND ,PURCHASE_ORD_NBR ,APP_TO_DD_WRKDY_DUR ,APP_TO_CD_WRKDY_DUR ,APP_TO_DDD_WRKDY_DUR ,SM_APP_TO_SD_WRKDY_DUR ,PRVSNG_TRBL_RPT_IND ,CUST_DELAY_DAYS_DUR ,CUST_DELAY_WKDYS_DUR ,WKDY5_CUST_DELAY_DUR ,DDCOMP_DDOBJ_CUST_DELAY_DUR ,CSU ,PROJECT_NBR ,CTR2MFC ,DD_TYPE_OF_MISS_CD ,DDMFC ,DDMFC_IND ,DDMFC_IN_IND ,DDMFC_NOTIN_IND ,ACT ,LNP_BYPASS_IND ,BDSL_QTY ,DDS1_QTY ,LDS3_QTY ,DKFB_QTY ,L5DB_QTY ,L8DB_QTY ,LBRI_QTY ,LDS1_QTY ,LDSL_QTY ,LOCN_QTY ,PBRI_QTY ,PPRI_QTY ,PTAL_QTY ,PTAT_QTY ,SUBD_QTY ,VDAT_QTY ,LINE_SPLIT_QTY ,TDID_QTY ) SELECT POSTED.SVC_ORD_POST_DT ,POSTED.SVC_ORD_DIV_CD ,POSTED.SVC_ORD_NBR ,POSTED.TBL_LOAD_TS ,POSTED.V260_PATRON_LOC ,POSTED.V260_WFA_WIRE_CTR_CD ,POSTED.V260_CKLEND ,POSTED.CTR ,POSTED.ULS_CD ,POSTED.WHSLE_IND ,POSTED.DATA_RSTRCTN_CD ,POSTED.ORDCLSFN_271 ,POSTED.LSPID ,POSTED.AECN_271 ,POSTED.CLEC_MKT ,POSTED.SCMG_CD ,POSTED.SCMG_SUB_CD ,POSTED.POSTED_SVC_ORD_MATCHED_DT ,POSTED.EX_CD ,POSTED.DUE_DATE_CD ,POSTED.APP_DT ,POSTED.DUE_DT ,POSTED.SUBSQNT_DUE_DT ,POSTED.COMPLTN_DT ,POSTED.DSRD_DUE_DT ,POSTED.SM_DT ,POSTED.SM_CD ,POSTED.DSL_CONDG_IND ,POSTED.EXPEDITE_IND ,POSTED.FW_IND ,POSTED.COOP_ACCEPT_TSTG_IND ,POSTED.PURCHANSE_ORD_NBR ,POSTED.APP_TO_DD_WRKDY_DUR ,POSTED.APP_TO_CD_WRKDY_DUR ,POSTED.APP_TO_DDD_WRKDY_DUR ,POSTED.SM_APP_TO_SD_WRKDY_DUR ,POSTED.PRVSNG_TRBL_RPT_IND ,POSTED.CUST_DELAY_DAYS_DUR ,POSTED.CUST_DELAY_WKDYS_DUR ,POSTED.WKDY5_CUST_DELAY_DUR ,POSTED.DDCOMP_DDOBJ_CUST_DELAY_DUR ,POSTED.CSU ,POSTED.PROJECT_NBR ,POSTED.CTR2MFC ,RPAD(CASE WHEN RTRIM(SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 1, 1)) = '1' THEN 'F' /* F-LACK OF FACLITIES */ WHEN RTRIM(SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 1, 1)) = '2' THEN 'C' /* C-COMPANY MISS */ WHEN RTRIM(SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 1, 1)) = '3' THEN 'S' /* S-SUBSCRIBER MISS */ ELSE ' ' END, 1) DUE_DATE_TYPE_OF_MISS_CD ,RPAD(SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 2, 3), 3) DUE_DATE_MISSD_FUNC_CD ,CASE WHEN SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 2, 3) IN ( 'B05' ,'B08' ,'B17' ,'B18' ,'B24' ,'B25' ,'B26' ,'B32' ,'H08' ,'H17' ,'H25' ,'H45' ,'J20' ,'K08' ,'L24' ,'M08' ,'M17' ,'M18' ,'M26' ,'M32' ,'N05' ,'N08' ,'N17' ,'N18' ,'N24' ,'N25' ,'N26' ,'N32' ,'N45' ,'P17' ,'Q32' ,'R08' ,'T08' ,'T17' ,'T20' ,'T25' ,'T45' ,'W08' ,'M05' ,'J18' ,'B16' ,'M16' ,'N16' ) THEN 1 ELSE 0 END::BYTEINT DDMFC_IND ,CASE WHEN SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 2, 1) IN ( 'A' ,'C' ,'D' ,'I' ,'O' ) OR SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 3, 2) IN ( '69' ,'70' ,'71' ,'72' ,'73' ) THEN 1 ELSE 0 END::BYTEINT DDMFC_IN_IND ,CASE WHEN SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 2, 1) NOT IN ( 'A' ,'C' ,'D' ,'I' ,'O' ) AND SUBSTR(POSTED.PRRTY_DUE_DATE_MISSD_FUNC_CD, 3, 2) NOT IN ( '69' ,'70' ,'71' ,'72' ,'73' ) THEN 1 ELSE 0 END::BYTEINT DDMFC_NOTIN_IND ,RPAD(CASE WHEN POSTED.ACT = 1 THEN 'A ' WHEN POSTED.ACT = 2 THEN 'C ' WHEN POSTED.ACT = 3 THEN 'R ' WHEN POSTED.ACT = 4 THEN 'CN' WHEN POSTED.ACT = 5 THEN 'RN' WHEN POSTED.ACT = 6 THEN 'D ' WHEN POSTED.ACT = 7 THEN 'CG' WHEN POSTED.ACT = 8 THEN 'RG' ELSE '**' END, 2) ACT ,POSTED.LNP_BYPASS_IND ,POSTED.BDSL_QTY ,POSTED.DDS1_QTY ,POSTED.LDS3_QTY ,POSTED.DKFB_QTY ,POSTED.L5DB_QTY ,POSTED.L8DB_QTY ,POSTED.LBRI_QTY ,POSTED.LDS1_QTY ,POSTED.LDSL_QTY ,POSTED.LOCN_QTY ,POSTED.PBRI_QTY ,POSTED.PPRI_QTY ,POSTED.PTAL_QTY ,POSTED.PTAT_QTY ,POSTED.SUBD_QTY ,POSTED.VDAT_QTY ,POSTED.LINE_SPLIT_QTY ,POSTED.TDID_QTY FROM ( SELECT S261.SVC_ORD_POST_DT ,S261.SVC_ORD_DIV_CD ,S261.SVC_ORD_NBR ,ONE.TBL_LOAD_TS ,RPAD(MAX(CASE WHEN V201A.CKT_END_PATRON_LOC <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201A.CKT_END_PATRON_LOC) ,27 ,40) WHEN V201Z.CKT_END_PATRON_LOC <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201Z.CKT_END_PATRON_LOC) ,27 ,40) ELSE '' END), 40) V260_PATRON_LOC ,RPAD(MAX(CASE WHEN V201A.CKT_END_PATRON_LOC <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201A.CKT_END_WFA_WIRE_CTR_CD) ,27 ,6) WHEN V201Z.CKT_END_PATRON_LOC <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201Z.CKT_END_WFA_WIRE_CTR_CD) ,27 ,6) WHEN V201A.CKT_END_WFA_WIRE_CTR_CD <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201A.CKT_END_WFA_WIRE_CTR_CD) ,27 ,6) WHEN V201Z.CKT_END_WFA_WIRE_CTR_CD <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201Z.CKT_END_WFA_WIRE_CTR_CD) ,27 ,6) ELSE ' ' END), 6) V260_WFA_WIRE_CTR_CD ,RPAD(MAX(CASE WHEN V201A.CKT_END_PATRON_LOC <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201A.CKLEND) ,27 ,1) WHEN V201Z.CKT_END_PATRON_LOC <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201Z.CKLEND) ,27 ,1) WHEN V201A.CKT_END_WFA_WIRE_CTR_CD <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201A.CKLEND) ,27 ,1) WHEN V201Z.CKT_END_WFA_WIRE_CTR_CD <> '' THEN SUBSTR(( (RPAD(TO_VARCHAR(V200.DDCRD), 8)) || ( RPAD(INSERT ( TO_VARCHAR(V200.DDCRT::INTEGER) ,'00000S' ), 6, 1, '') ,5 ) ) || (RPAD(CAST(V200.ITEM AS CHAR(13)), 13)) || V201Z.CKLEND) ,27 ,1) WHEN V201A.CKLEND IS NOT NULL THEN 'A' WHEN V201Z.CKLEND IS NOT NULL THEN 'Z' ELSE ' ' END), 1) V260_CKLEND ,MAX(V200.CTR) CTR ,MAX(S261.ULS_CD) ULS_CD ,MAX(S261.WHSLE_IND) WHSLE_IND ,MAX(S261.DATA_RSTRCTN_CD) DATA_RSTRCTN_CD ,MAX(S261.POSTED_SVC_ORD_MATCHED_DT) POSTED_SVC_ORD_MATCHED_DT ,MAX(S261.CKTCLSFN_271) ORDCLSFN_271 ,MAX(S261.LSPID) LSPID ,MAX(S261.AECN_271) AECN_271 ,MAX(S261.CLEC_MKT) CLEC_MKT ,MAX(S261.SCMG_CD) SCMG_CD ,MAX(S261.SCMG_SUB_CD) SCMG_SUB_CD ,MAX(S261.EX_CD) EX_CD ,MAX(S261.DUE_DATE_CD) DUE_DATE_CD ,MAX(S261.APP_DT) APP_DT ,MAX(S261.DUE_DT) DUE_DT ,MAX(S261.SUBSQNT_DUE_DT) SUBSQNT_DUE_DT ,MAX(S261.COMPLTN_DT) COMPLTN_DT ,MAX(S261.DSRD_DUE_DT) DSRD_DUE_DT ,MAX(S261.SM_DT) SM_DT ,MAX(S261.SM_CD) SM_CD ,MAX(S261.DSL_CONDG_IND) DSL_CONDG_IND ,MAX(S261.EXPEDITE_IND) EXPEDITE_IND ,MAX(S261.FW_IND) FW_IND ,MAX(S261.COOP_ACCEPT_TSTG_IND) COOP_ACCEPT_TSTG_IND ,MAX(S261.PURCHASE_ORD_NBR) PURCHANSE_ORD_NBR ,MAX(S261.APP_TO_DD_WRKDY_DUR) APP_TO_DD_WRKDY_DUR ,MAX(S261.APP_TO_CD_WRKDY_DUR) APP_TO_CD_WRKDY_DUR ,MAX(S261.APP_TO_DDD_WRKDY_DUR) APP_TO_DDD_WRKDY_DUR ,MAX(S261.SM_APP_TO_SD_WRKDY_DUR) SM_APP_TO_SD_WRKDY_DUR ,MAX(S261.PRVSNG_TRBL_RPT_IND) PRVSNG_TRBL_RPT_IND ,MIN(S261.CUST_DELAY_DAYS_DUR) CUST_DELAY_DAYS_DUR ,MIN(S261.CUST_DELAY_WKDYS_DUR) CUST_DELAY_WKDYS_DUR ,MAX(S261.WKDY5_CUST_DELAY_DUR) WKDY5_CUST_DELAY_DUR ,MAX(S261.DDCOMP_DDOBJ_CUST_DELAY_DUR) DDCOMP_DDOBJ_CUST_DELAY_DU9000000 ,MAX(V200.CSU) CSU ,MAX(S261.PROJECT_NBR) PROJECT_NBR ,MAX(CASE WHEN V205_CTR2.EVNT_MISSD_FUNC_CD IN ( 'I53' ,'I58' ) THEN V205_CTR2.EVNT_MISSD_FUNC_CD ELSE ' ' END) CTR2MFC ,RPAD(MIN(CASE WHEN S261.DUE_DT >= S261.COMPLTN_DT OR V205_DD.EVNT_MISSD_FUNC_CD = 'I37' THEN '4' || V205_DD.EVNT_MISSD_FUNC_CD WHEN S261.DDMFC_IND = 1 THEN '1' || V205_DD.EVNT_MISSD_FUNC_CD WHEN S261.DDMFC_IN_IND = 1 THEN '3' || V205_DD.EVNT_MISSD_FUNC_CD WHEN V205_DD.EVNT_MISSD_FUNC_CD = '' AND S261.SUBSQNT_DUE_DT < S261.COMPLTN_DT THEN '3' || V205_DD.EVNT_MISSD_FUNC_CD WHEN S261.DDMFC_NOTIN_IND = 1 AND V205_DD.EVNT_MISSD_FUNC_CD <> '' THEN '2' || V205_DD.EVNT_MISSD_FUNC_CD ELSE '4' || V205_DD.EVNT_MISSD_FUNC_CD END), 4) PRRTY_DUE_DATE_MISSD_FUNC_CD ,MIN(CASE WHEN V200.ACT = 'A' THEN 1 WHEN V200.ACT = 'C' THEN 2 WHEN V200.ACT = 'R' THEN 3 WHEN V200.ACT = 'CN' THEN 4 WHEN V200.ACT = 'RN' THEN 5 WHEN V200.ACT = 'D' THEN 6 WHEN V200.ACT = 'CG' THEN 7 WHEN V200.ACT = 'RG' THEN 8 ELSE 9 END)::BYTEINT ACT ,MAX(CASE WHEN GRETA.CAC IS NOT NULL THEN 1 ELSE 0 END)::BYTEINT LNP_BYPASS_IND ,SUM(CASE WHEN S261.SCMG_CD = 1 THEN 1 ELSE 0 END) BDSL_QTY ,SUM(CASE WHEN S261.SCMG_CD = 2 THEN 1 ELSE 0 END) DDS1_QTY ,SUM(CASE WHEN S261.SCMG_CD = 3 THEN 1 ELSE 0 END) LDS3_QTY ,SUM(CASE WHEN S261.SCMG_CD = 4 THEN 1 ELSE 0 END) DKFB_QTY ,SUM(CASE WHEN S261.SCMG_CD = 5 THEN 1 ELSE 0 END) L5DB_QTY ,SUM(CASE WHEN S261.SCMG_CD = 6 THEN 1 ELSE 0 END) L8DB_QTY ,SUM(CASE WHEN S261.SCMG_CD = 7 THEN 1 ELSE 0 END) LBRI_QTY ,SUM(CASE WHEN S261.SCMG_CD = 8 THEN 1 ELSE 0 END) LDS1_QTY ,SUM(CASE WHEN S261.SCMG_CD = 9 THEN 1 ELSE 0 END) LDSL_QTY ,SUM(CASE WHEN S261.SCMG_CD = 10 THEN 1 ELSE 0 END) LOCN_QTY ,SUM(CASE WHEN S261.SCMG_CD = 11 THEN 1 ELSE 0 END) PBRI_QTY ,SUM(CASE WHEN S261.SCMG_CD = 12 THEN 1 ELSE 0 END) PPRI_QTY ,SUM(CASE WHEN S261.SCMG_CD = 13 THEN 1 ELSE 0 END) PTAL_QTY ,SUM(CASE WHEN S261.SCMG_CD = 14 THEN 1 ELSE 0 END) PTAT_QTY ,SUM(CASE WHEN S261.SCMG_CD = 15 THEN 1 ELSE 0 END) SUBD_QTY ,SUM(CASE WHEN S261.SCMG_CD = 16 THEN 1 ELSE 0 END) VDAT_QTY ,SUM(CASE WHEN S261.SCMG_CD IN ( 17 ,18 ) THEN 1 ELSE 0 END) LINE_SPLIT_QTY ,SUM(CASE WHEN S261.SCMG_CD = 27 THEN 1 ELSE 0 END) TDID_QTY FROM ( SELECT CURRENT_TIMESTAMP(0) TBL_LOAD_TS ) ONE ,( SELECT CASE WHEN DAY_OF_MONTH <= V760.EOM_BUILD THEN DATEADD(MONTH, - 1, ( PUBLIC.INT_TO_DATE_UDF((((TRUNC(CURRENT_DATE () / 100)) * 100) + 1)) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'INT_TO_DATE_UDF' INSERTED. ***/ )) ELSE ( PUBLIC.INT_TO_DATE_UDF((((TRUNC(CURRENT_DATE () / 100)) * 100) + 1)) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'INT_TO_DATE_UDF' INSERTED. ***/ ) END BUILD_START_DT FROM NTWK_RGLTY_RPTG.TCTD760_271_JOB_CONTROL V760 ,SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE = CURRENT_DATE () AND V760.REGION = 'SOUTHWEST' AND V760.MVS_CNTLCARD = 'INS260D0' ) DAILY ,ASKME_CPNI_UNRESTRICTED_VIEWS.VSTD261 S261 JOIN SERVICE_ASSURANCE.TCTD200_INSTLN_CKT V200 ON S261.ITEM = V200.ITEM AND S261.CRD_DATE = V200.CRD_DATE AND S261.SITE = V200.SITE JOIN SERVICE_ASSURANCE.TCTD250_INSTLN_DERVD_ELEMS V250 ON S261.ITEM = V250.ITEM AND S261.CRD_DATE = V250.CRD_DATE AND S261.SITE = V250.SITE LEFT OUTER JOIN SERVICE_ASSURANCE.TCTD205_INSTLN_CKT_EVNT V205_DD ON S261.ITEM = V205_DD.ITEM AND S261.CRD_DATE = V205_DD.CRD_DATE AND S261.SITE = V205_DD.SITE AND 'DD' = V205_DD.EVNT_CD LEFT OUTER JOIN SERVICE_ASSURANCE.TCTD205_INSTLN_CKT_EVNT V205_CTR2 ON V200.SITE = V205_CTR2.SITE AND V200.ITEM = V205_CTR2.ITEM AND V200.CRD_DATE = V205_CTR2.CRD_DATE AND 'CTR2' = V205_CTR2.EVNT_CD LEFT OUTER JOIN SERVICE_ASSURANCE.TCTD201_INSTLN_CKT_END V201A ON V200.SITE = V201A.SITE AND V200.CRD_DATE = V201A.CRD_DATE AND V200.ITEM = V201A.ITEM AND V201A.CKLEND = 'A' LEFT OUTER JOIN SERVICE_ASSURANCE.TCTD201_INSTLN_CKT_END V201Z ON V200.SITE = V201Z.SITE AND V200.CRD_DATE = V201Z.CRD_DATE AND V200.ITEM = V201Z.ITEM AND V201Z.CKLEND = 'Z' LEFT OUTER JOIN COMMON_VIEWS.V3345H_GRETAPRO_LOC_DATA GRETA ON V200.ITEM = GRETA.TRK AND V200.CAC = GRETA.CAC AND V200.CTR = GRETA.CTL LEFT OUTER JOIN NTWK_RGLTY_RPTG.TSTD260_271_POSTED_SVC_ORD_LVL S260 ON S261.SVC_ORD_POST_DT = S260.SVC_ORD_POST_DT AND S261.SVC_ORD_DIV_CD = S260.SVC_ORD_DIV_CD AND S261.SVC_ORD_NBR = S260.SVC_ORD_NBR WHERE ( S261.SVC_ORD_POST_DT >= DATEADD(MONTH, - 13, ( PUBLIC.INT_TO_DATE_UDF((((TRUNC(CURRENT_DATE () / 100)) * 100) + 1)) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'INT_TO_DATE_UDF' INSERTED. ***/ )) OR S261.ADJUSTED_271_RPTG_MONTH_DT >= DAILY.BUILD_START_DT ) AND ( ( V200.ACT IN ( 'A' ,'R' ,'C' ) AND S261.SCMG_CD <> 6 ) OR S261.SCMG_CD = 6 ) /* L8DB */ AND SUBSTR(S261.SVC_ORD_NBR, 1, 1) IN ( 'C' ,'N' ,'T' ) AND S261.SCMG_CD > 0 AND V200.SITE IN ( 'T' ,'C' ,'@' ) AND V250.SITE IN ( 'T' ,'C' ,'@' ) AND S260.SVC_ORD_NBR IS NULL GROUP BY 1 ,2 ,3 ,4 ) POSTED;