SELECT TO_NUMBER(D.SHPG_LOC_CD, '99999') DC,
       TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy') AS "Appt Date",
       TO_CHAR(DC.CMTD_STRT_DTT,'DY') AS DAY,
       SUM(S.TOT_PCE + S.TOT_SKID) "# of Cartons Scheduled",
       SUM(NVL(CASE WHEN S.RFRC_NUM8 = 'Y' THEN S.TOT_PCE + S.TOT_SKID END,0)) "# of HOT PO CARTONS",
       TC.MAX_QTY "MAX Carton Capacity",
       SUM(NVL(CASE 
                  WHEN D.DOCK_CD LIKE 'DOOR%' AND S.RFRC_NUM15 = 'Collect' THEN S.TOT_SKID + S.TOT_PCE 
                  WHEN D.DOCK_CD LIKE 'DOOR%' AND (S.RFRC_NUM15 IN ('Prepaid','PREPAID') OR S.RFRC_NUM15 IS NULL) THEN S.TOT_PCE + S.TOT_SKID
       END,0)) "LIVE Cartons",
       SUM(NVL(CASE 
                  --WHEN (D.DOCK_CD LIKE 'DROP%' AND S.RFRC_NUM15 = 'Collect' AND S.CUST_CD = 'DECON') THEN S.TOT_PCE + S.TOT_SKID       
                  WHEN D.DOCK_CD LIKE 'DROP%' AND S.RFRC_NUM15 = 'Collect' THEN S.TOT_SKID + S.TOT_PCE
                  WHEN D.DOCK_CD LIKE 'DROP%' AND (S.RFRC_NUM15 IN ('Prepaid','PREPAID') OR S.RFRC_NUM15 IS NULL) THEN S.TOT_PCE + S.TOT_SKID  
       END,0)) "DROP Cartons",
       (SELECT COUNT(*)
        FROM JDATM_2016PRD.DOCK_CMTD_T A
        JOIN JDATM_2016PRD.DOCK_T B ON A.DOCK_ID = B.DOCK_ID
        WHERE TO_CHAR(A.CMTD_STRT_DTT, 'MM/DD/YYYY') = TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy')
          AND B.SHPG_LOC_CD = TO_NUMBER(D.SHPG_LOC_CD, '99999') 
          AND (B.DOCK_CD LIKE ('%DROP%') OR B.DOCK_CD LIKE ('%DOOR%'))
        GROUP BY TO_NUMBER(B.SHPG_LOC_CD), TO_CHAR(A.CMTD_STRT_DTT,'MM/DD/YYYY')
        ) Appt_Count
FROM JDATM_2016PRD.DOCK_CMTD_T DC,
     JDATM_2016PRD.DOCK_T D,
     JDATM_2016PRD.LD_LEG_DETL_T LD,
     JDATM_2016PRD.SHPM_T S,
     JDATM_2016PRD.LOC_THGP_GRP_T TG,
     JDATM_2016PRD.LOC_THGP_GRP_CPTY_T TC
WHERE D.DOCK_ID = DC.DOCK_ID
      AND D.SHPG_LOC_CD IN ('70','77','84','87','717','725','781','1376') -- RDC List
      AND LD.LD_LEG_ID = DC.LD_LEG_ID
      AND S.SHPM_ID = LD.SHPM_ID
      AND TG.SHPG_LOC_CD = D.SHPG_LOC_CD
      AND (TG.LOC_THGP_GRP_ID = TC.LOC_THGP_GRP_ID AND TO_CHAR(TC.STRT_DT,'MM-DD-YYYY') = TO_CHAR(DC.CMTD_STRT_DTT,'MM-DD-YYYY'))
      AND (DOCK_CD LIKE ('%DROP%') OR DOCK_CD LIKE ('%DOOR%'))
      AND TRUNC(DC.CMTD_STRT_DTT) >= TRUNC(SYSDATE)
      AND TRUNC(DC.CMTD_STRT_DTT) <= TRUNC(SYSDATE + 8)
GROUP BY TO_NUMBER(D.SHPG_LOC_CD, '99999'),
                    TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy'),
                    TO_CHAR(DC.CMTD_STRT_DTT,'DY'),
                    TC.MAX_QTY
ORDER BY TO_NUMBER(D.SHPG_LOC_CD, '99999'), TO_CHAR(DC.CMTD_STRT_DTT,'mm/dd/yyyy')