--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;
Telegram: DB developers channel