001.SELECT 'HED' AS REP_GROUP, WHS_BAL.WAREHOUSE, WHS_BAL.PART_NO, 'ยอดยกมา' AS DOC_NO, ' ' AS SEQ, CAST('3/3/2014' AS TIMESTAMP) AS DOC_DATE, CAST('00' AS INTEGER) AS PROCESS_SEQ, 'AA' AS TRAN_CODE, 'AAA' AS TRAN_TYPE, MIN(WHS_STAT.BEGIN_BAL) AS BEGIN_BAL, MIN(WHS_STAT.BEG_AMT) AS BEG_AMT, SUM(HIS_STAT.REC_QTY) AS REC_QTY, SUM(HIS_STAT.ISS_QTY) AS ISS_QTY, SUM(HIS_STAT.ADJ_INC) AS ADJ_INC, SUM(HIS_STAT.ADJ_DEC) AS ADJ_DEC, 0.00 AS TRAN_QTY, 0.00 AS TRAN_QTYSORT, SUM(HIS_STAT.TRAN_AMT) AS TRAN_AMT, '0' AS POST_FLAG, MIN(WHS_BAL.ACCODE) AS ACCODE, MIN(AC_TAB.ACCOUNT_NO) AS ACCOUNT_NO, MIN(UM_TAB.UM) AS UM, MIN(UM_TAB.UM_DESC) AS UM_DESC, MIN(WHS_BAL.PART_DESC) AS PART_DESC, 0.00 AS UNIT_COST, (
002. SELECT MIN(WHS_TAB.WHS_DESC)
003. FROM WHS_TAB
004. WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
005. AND WHS_TAB.STK_LOCATION = (
006. SELECT MIN(WHB.STK_LOCATION)
007. FROM WHS_TAB WHB
008. WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
009. )
010. ) AS WHS_DESC, (
011. SELECT MIN(TRAN_DESC)
012. FROM PARTHIST
013. WHERE 1 <> 1
014. ) AS TRAN_DESC, MIN(REP1.NOTE) AS NOTE, MIN(REP2.NOTE) AS NOTE2, CAST('0' AS NUMERIC(15, 2)) AS HDL_TRAN_QTY, CAST('0' AS NUMERIC(15, 2)) AS HDL_UNIT_COST
015.FROM WHS_BAL
016.LEFT JOIN WHS_STAT
017. ON WHS_BAL.WAREHOUSE = WHS_STAT.WAREHOUSE
018. AND WHS_BAL.PART_NO = WHS_STAT.PART_NO
019. AND WHS_STAT.curYEAR = '57'
020. AND WHS_STAT.PERIOD = '3'
021.LEFT JOIN HIS_STAT
022. ON WHS_BAL.WAREHOUSE = HIS_STAT.WAREHOUSE
023. AND WHS_BAL.PART_NO = HIS_STAT.PART_NO
024. AND HIS_STAT.DOC_DATE < '3/1/2014'
025.LEFT JOIN AC_TAB
026. ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE
027. AND WHS_BAL.ACCODE = AC_TAB.ACCODE
028.LEFT JOIN PART_STD
029. ON WHS_BAL.PART_NO = PART_STD.PART_NO
030.LEFT JOIN UM_TAB
031. ON PART_STD.UM = UM_TAB.UM
032.LEFT JOIN REP REP1
033. ON REP1.ID = 'ปตท'
034.LEFT JOIN REP REP2
035. ON REP2.ID = 'LAST_POST'
036.WHERE WHS_BAL.WAREHOUSE = 'WH_001'
037. AND WHS_BAL.PART_NO BETWEEN 'DILDO'
038. AND 'DILDO'
039.GROUP BY WHS_BAL.WAREHOUSE, WHS_BAL.PART_NO
040.
041.UNION ALL
042.
043.SELECT 'DET' AS REP_GROUP, PARTHIST.WAREHOUSE, PARTHIST.PART_NO, PARTHIST.DOC_NO, PARTHIST.SEQ, PARTHIST.DOC_DATE, PARTHIST.PROCESS_SEQ, PARTHIST.TRAN_CODE, PARTHIST.TRAN_TYPE, 0.00 AS BEGIN_BAL, 0.00 AS BEG_AMT, 0.00 AS REC_QTY, 0.00 AS ISS_QTY, 0.00 AS ADJ_INC, 0.00 AS ADJ_DEC, PARTHIST.TRAN_QTY, CASE
044. WHEN ABS(PARTHIST.TRAN_QTY) > 0
045. THEN (PARTHIST.TRAN_QTY / (ABS(PARTHIST.TRAN_QTY)))
046. ELSE 0
047. END AS TRAN_QTYSORT, PARTHIST.TRAN_AMT, PARTHIST.POST_FLAG, WHS_BAL.ACCODE, AC_TAB.ACCOUNT_NO, UM_TAB.UM, UM_TAB.UM_DESC, WHS_BAL.PART_DESC, PARTHIST.UNIT_COST, (
048. SELECT MIN(WHS_TAB.WHS_DESC)
049. FROM WHS_TAB
050. WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
051. AND WHS_TAB.STK_LOCATION = (
052. SELECT MIN(WHB.STK_LOCATION)
053. FROM WHS_TAB WHB
054. WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
055. )
056. ) AS WHS_DESC, PARTHIST.TRAN_DESC, REP1.NOTE AS NOTE, REP2.NOTE AS NOTE2, CAST('0' AS NUMERIC(15, 2)) AS HDL_TRAN_QTY, CAST('0' AS NUMERIC(15, 2)) AS HDL_UNIT_COST
057.FROM WHS_BAL
058.LEFT JOIN PARTHIST
059. ON WHS_BAL.WAREHOUSE = PARTHIST.WAREHOUSE
060. AND WHS_BAL.PART_NO = PARTHIST.PART_NO
061. AND PARTHIST.DOC_DATE BETWEEN '3/1/2014'
062. AND '3/3/2014 23:59'
063.LEFT JOIN AC_TAB
064. ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE
065. AND WHS_BAL.ACCODE = AC_TAB.ACCODE
066.LEFT JOIN PART_STD
067. ON WHS_BAL.PART_NO = PART_STD.PART_NO
068.LEFT JOIN UM_TAB
069. ON PART_STD.UM = UM_TAB.UM
070.LEFT JOIN REP REP1
071. ON REP1.ID = 'ปตท'
072.LEFT JOIN REP REP2
073. ON REP2.ID = 'LAST_POST'
074.WHERE WHS_BAL.WAREHOUSE = 'WH_001'
075. AND WHS_BAL.PART_NO BETWEEN 'DILDO'
076. AND 'DILDO'
077.
078.UNION ALL
079.
080.SELECT 'DET' AS REP_GROUP, PARTHIST.WAREHOUSE, PARTHIST.PART_NO, PARTHIST.DOC_NO, PARTHIST.SEQ, PARTHIST.DOC_DATE, PARTHIST.PROCESS_SEQ, PARTHIST.TRAN_CODE, PARTHIST.TRAN_TYPE, 0.00 AS BEGIN_BAL, 0.00 AS BEG_AMT, 0.00 AS REC_QTY, 0.00 AS ISS_QTY, 0.00 AS ADJ_INC, 0.00 AS ADJ_DEC, PARTHIST.TRAN_QTY, CASE
081. WHEN ABS(PARTHIST.TRAN_QTY) > 0
082. THEN (PARTHIST.TRAN_QTY / (ABS(PARTHIST.TRAN_QTY)))
083. ELSE 0
084. END AS TRAN_QTYSORT, PARTHIST.TRAN_AMT, PARTHIST.POST_FLAG, WHS_BAL.ACCODE, AC_TAB.ACCOUNT_NO, UM_TAB.UM, UM_TAB.UM_DESC, WHS_BAL.PART_DESC, PARTHIST.UNIT_COST, (
085. SELECT MIN(WHS_TAB.WHS_DESC)
086. FROM WHS_TAB
087. WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
088. AND WHS_TAB.STK_LOCATION = (
089. SELECT MIN(WHB.STK_LOCATION)
090. FROM WHS_TAB WHB
091. WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
092. )
093. ) AS WHS_DESC, PARTHIST.TRAN_DESC, REP1.NOTE AS NOTE, REP2.NOTE AS NOTE2, CASE
094. WHEN (- HDL.TRAN_QTY) > 0
095. THEN CAST((- HDL.TRAN_QTY) AS NUMERIC(15, 2))
096. ELSE 0
097. END AS HDL_TRAN_QTY, CASE
098. WHEN HDL.UNIT_COST > 0
099. THEN CAST(HDL.UNIT_COST AS NUMERIC(15, 2))
100. ELSE 0
101. END AS HDL_UNIT_COST
102.FROM WHS_BAL
103.LEFT JOIN PARTHIST
104. ON WHS_BAL.WAREHOUSE = PARTHIST.WAREHOUSE
105. AND WHS_BAL.PART_NO = PARTHIST.PART_NO
106. AND PARTHIST.DOC_DATE BETWEEN '3/1/2014'
107. AND '3/3/2014 23:59'
108.LEFT JOIN HIS_DTL HDL
109. ON HDL.WAREHOUSE = PARTHIST.WAREHOUSE
110. AND HDL.DOC_DATE = PARTHIST.DOC_DATE
111. AND HDL.DOC_NO = PARTHIST.DOC_NO
112. AND HDL.SEQ = PARTHIST.SEQ
113.LEFT JOIN AC_TAB
114. ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE
115. AND WHS_BAL.ACCODE = AC_TAB.ACCODE
116.LEFT JOIN PART_STD
117. ON WHS_BAL.PART_NO = PART_STD.PART_NO
118.LEFT JOIN UM_TAB
119. ON PART_STD.UM = UM_TAB.UM
120.LEFT JOIN REP REP1
121. ON REP1.ID = 'ปตท'
122.LEFT JOIN REP REP2
123. ON REP2.ID = 'LAST_POST'
124.WHERE WHS_BAL.WAREHOUSE = 'WH_001'
125. AND HDL.UNIT_COST > 0
126. AND (- HDL.TRAN_QTY) > 0
127. AND WHS_BAL.PART_NO BETWEEN 'DILDO'
128. AND 'DILDO'
129.ORDER BY DOC_DATE
