export const statusSql = (alias: string) => { return `(select CASE WHEN ( ${alias}."ON_TIME_OVERRIDE" = 1) THEN 'On-time' WHEN ( "spli1"."EARLIEST_FILE_SUBMISSION_DATE" < ${alias}."EXPECTED_DATE" ) THEN 'On-time' WHEN ( ( ${alias}."NO_DATA" = 1 ) AND ( ${alias}."NO_DATA_CREATE_DATE" < ${alias}."EXPECTED_DATE")) THEN 'On-time' WHEN ( ${alias}."EXPECTED_DATE" > SYSTIMESTAMP) THEN 'Pending' ELSE 'Late' END from SUBMISSION_PERIOD "sp1" left join SUBMISSION_PERIOD_LINE_ITEM_V "spli1" on "spli1"."SUBMISSION_PERIOD_SID" = "sp1"."SID" AND "spli1"."CUSTOMER_SID" = "sp1"."CUSTOMER_SID" where "sp1"."SID" = ${alias}."SID" AND "sp1"."CUSTOMER_SID" = ${alias}."CUSTOMER_SID")`; }; export const reportedFlagSql = (alias: string) => { return `(select CASE WHEN MAX(${alias}."ON_TIME_OVERRIDE") = 1 THEN 1 WHEN MAX(${alias}."NO_DATA") = 1 THEN 1 WHEN COUNT("df"."ID") > 0 THEN 1 ELSE 0 END from SUBMISSION_SCHEDULE "ss1" left join DATA_FILE_SUMMARY_INFO "dfsi" on "dfsi"."SUBMISSION_PERIOD_SID" = ${alias}."SID" AND "dfsi"."CUSTOMER_SID" = ${alias}."CUSTOMER_SID" left join DATA_TYPE "dt1" on "ss1"."DATA_TYPE_SID" = "dt1"."SID" left join DATA_FILE "df" on "dfsi"."CUSTOMER_SID" = "df"."CUSTOMER_SID" AND "dfsi"."DATA_FILE_SID" = "df"."SID" AND "df"."DATA_TYPE" = "dt1"."TYPE" where "ss1"."SID" = ${alias}."SUBMISSION_SCHEDULE_SID" AND "ss1"."CUSTOMER_SID" = ${alias}."CUSTOMER_SID")` }; export const fileIdsSql = (alias: string) => { return `(select listagg("df"."ID",',') within group (ORDER BY "df"."CREATE_DATE") from SUBMISSION_SCHEDULE "ss1" left join DATA_FILE_SUMMARY_INFO "dfsi" on "dfsi"."SUBMISSION_PERIOD_SID" = ${alias}."SID" AND "dfsi"."CUSTOMER_SID" = ${alias}."CUSTOMER_SID" left join DATA_TYPE "dt1" on "ss1"."DATA_TYPE_SID" = "dt1"."SID" left join DATA_FILE "df" on "dfsi"."CUSTOMER_SID" = "df"."CUSTOMER_SID" AND "dfsi"."DATA_FILE_SID" = "df"."SID" AND "df"."DELETED" = 0 AND "df"."DATA_TYPE" = "dt1"."TYPE" where "ss1"."SID" = ${alias}."SUBMISSION_SCHEDULE_SID" AND "ss1"."CUSTOMER_SID" = ${alias}."CUSTOMER_SID")` }; export const filesCountSql = (alias: string) => { return `(select count(df.id) as number_of_files from SUBMISSION_SCHEDULE ss1 left join DATA_FILE_SUMMARY_INFO dfsi on dfsi.SUBMISSION_PERIOD_SID = ${alias}.SID AND dfsi.CUSTOMER_SID = ${alias}.CUSTOMER_SID left join DATA_TYPE dt1 on ss1.DATA_TYPE_SID = dt1.SID left join DATA_FILE df on dfsi.CUSTOMER_SID = df.CUSTOMER_SID AND dfsi.DATA_FILE_SID = df.SID AND df.DELETED = 0 AND df.DATA_TYPE = dt1.TYPE where ss1.SID = ${alias}.SUBMISSION_SCHEDULE_SID AND ss1.CUSTOMER_SID= ${alias}.CUSTOMER_SID)` } export const filesDataSql = (alias:string) => { return `(select df.id,df.file_name,df.record_count,dfsi.num_slis,dfsi.num_ilis from SUBMISSION_SCHEDULE ss1 left join DATA_FILE_SUMMARY_INFO dfsi on dfsi.SUBMISSION_PERIOD_SID = ${alias}.SID AND dfsi.CUSTOMER_SID = ${alias}.CUSTOMER_SID left join DATA_TYPE dt1 on ss1.DATA_TYPE_SID = dt1.SID left join DATA_FILE df on dfsi.CUSTOMER_SID = df.CUSTOMER_SID AND dfsi.DATA_FILE_SID = df.SID AND df.DELETED = 0 AND df.DATA_TYPE = dt1.TYPE where ss1.SID = ${alias}.SUBMISSION_SCHEDULE_SID AND ss1.CUSTOMER_SID= ${alias}.CUSTOMER_SID)` }
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter