Admin.~sql

--Admin Order Data
SELECT s.SH_SH_ORDER, s.SH_SH_CUST AS "Customer", COUNT(*) AS "NUMLINE", s.SH_SH_ADDRESS, s.SH_SH_SUBURB, s.SH_SH_CITY, s.SH_SH_STATE, s.SH_SH_POST_CODE, s.SH_SH_NOTE_1,
s.SH_SH_NOTE_2 , sd2.SD_LINE, sd2.SD_STOCK AS Stock, sd2.SD_QTY_ORDER, sd2.SD_DESC, sd2.SD_EXCL, sd2.SD_INCL ,
      CASE
	      WHEN SH_SPARE_DBL_9 = 1 OR SH_SPARE_DBL_9 = 3 OR SH_SPARE_DBL_9 = 2 OR SH_SPARE_DBL_9 = 4 THEN 'OrderFee' /*'OrderEntryFee'   */
        ELSE 'NoOrderEntryFee'
      END AS SH_SPARE_DBL_9 , SH_ADD_DATE  ,
      CASE
         WHEN SD_STOCK like 'COURIER%' THEN 'Freight Fee is ' || CAST(SD_SELL_PRICE AS VARCHAR(20))
         --WHEN SD_STOCK NOT lIKE 'COURIER%' AND SL_PICK_QTY >= 1 THEN 'PickFee is '   ||  (Select RM_XX_FEE16 from RM where RM_CUST = 'LINK')
         WHEN SD_STOCK NOT lIKE 'COURIER%' AND (SELECT SL_PICK_QTY FROM SL,SH INNER JOIN PWIN175.SL ON PWIN175.s.SH_SH_ORDER = SL.SL_ORDER WHERE SH_ORDER >= 1 THEN 'PickFee is '   ||  (Select RM_XX_FEE16 from RM where RM_CUST = 'LINK')

      END AS "Charge",r.RM_CUST, r.RM_XX_FEE16, r2.RM_CUST, r2.RM_XX_FEE16
FROM pwin175.SH s
  left join pwin175.RM r ON r.RM_CUST = s.SH_CUST
  left join pwin175.RM r2 ON r.RM_PARENT = r2.RM_CUST
  left join pwin175.SD sd2 ON sd2.SD_ORDER = s.SH_ORDER
WHERE LTRIM(RTRIM(ST_PICK)) = LTRIM(RTRIM(SL_PICK))
AND SH_ORDER = ST_ORDER
AND SH_ORDER = SD_ORDER
AND SL_ORDER = ST_ORDER
AND RM_CUST = SH_CUST
AND (SL_PICK_QTY >= 1)
AND (RM_SOURCE = 'BSPRINTNSW')
AND ST_DESP_DATE >= '1-JAN-13'
AND ST_DESP_DATE <= '31-JAN-13'
AND SH_ORDER = '   1267078'
GROUP BY SH_ORDER, SH_CUST, SH_ADDRESS, SH_SUBURB, SH_CITY, SH_STATE, SH_POST_CODE, SH_NOTE_1, SH_NOTE_2, SD_LINE,
SD_STOCK, SD_QTY_ORDER, SD_DESC, SD_EXCL, SD_INCL, SH_SPARE_DBL_9, SH_ADD_DATE , SD_STOCK, SD_SELL_PRICE, SL_PICK_QTY,RM_CUST, RM_XX_FEE16, RM_CUST, RM_XX_FEE16
ORDER BY SD_LINE, SH_CUST


SELECT l.SL_PICK_QTY AS "PickQTY"
FROM PWIN175.SL AS l
INNER JOIN PWIN175.SH AS o ON o.SH_ORDER = l.SL_ORDER
WHERE SH_ORDER = '   1267078'
AND SH_CUST = (Select RM_XX_FEE16 from RM where RM_CUST = 'LINK'


SELECT c1.RM_PARENT AS "Parent", o.SH_CUST AS "Cust"
FROM RM c1, SH o
WHERE SH_ORDER = '   1267078'
AND o.SH_CUST = c1.RM_CUST
AND c1.RM_PARENT IN ( SELECT c2.RM_PARENT FROM RM c2 WHERE c1.RM_CUST = o.SH_CUST AND c2.RM_TYPE = 0 AND c2.RM_SOURCE = 'BSPRINTNSW' AND c2.RM_PARENT IS NULL)