MONTH : 202504
SELECT oh.OAORDT as ORDER_DATE,oh.OARLDT as SEND_DATE,oh.OAORNO as ORDER_NO,
ol.OBITNO AS ITEMNO, it.MMFUDS as ITEM_NAME,
CONVERT(DECIMAL(10,2),(sum(ol.OBORQT)/(select max(un.MUCOFA) from MVXJDTA.MITAUN as un where ol.OBITNO=un.MUITNO AND un.MUCONO = 410 AND ol.OBCONO = 410))) as qty_ctn,
CONVERT(DECIMAL(10,2),sum(ol.OBORQA*ol.OBNEPR)) AS amount,
oh.OACUNO as CUS_CODE,
CASE
WHEN OKSMCD IN ('S0019','EX001') THEN 'EX'
WHEN OKCUNO IN ('V03000001','V03000002','V03000003','V03000004') THEN N'cpt'
WHEN OKCUCL = '105' AND OKCUNO NOT IN ('V03000001','V03000002','V03000003','V03000004') THEN 'PC'
WHEN OKCUCL = '101' THEN 'MT'
WHEN OKCUCL = '102' THEN 'CR'
WHEN OKCUCL = '103' THEN 'CA'
WHEN OKCUCL = '104' THEN 'IDS'
WHEN OKCUCL = '200' THEN 'EX'
WHEN OKCUCL = '107' THEN 'Online'
ELSE OKCUCL END AS CUS_CHANEL,
oh.OAADID as SEND_CODE,
cu.OKCFC1 as CUS_AREA,
ol.OBSMCD AS SALE_COSE,
oh.OANTLA-(oh.OANTLA*oh.OAOTDP/100) as TOT_AMT
FROM MVXJDTA.OOHEAD AS oh
INNER JOIN MVXJDTA.OOLINE AS ol ON oh.OAORNO = ol.OBORNO
INNER JOIN MVXJDTA.OCUSMA AS cu ON oh.OACUNO = cu.OKCUNO AND cu.OKCONO = 410
INNER JOIN MVXJDTA.MITMAS AS it ON ol.OBITNO = it.MMITNO AND it.MMCONO = 410
INNER JOIN MVXJDTA.CMNUSR as cm ON ol.OBSMCD = cm.JUUSID
WHERE (left(oh.OAORDT,6) =('202504'))
AND (oh.OAORTP IN ('281','021','A11','011'))
AND (((oh.OAORST between '22' and '77') OR (oh.OAORST in ('99'))) and (oh.OAORSL not in('99')))
AND (ol.OBORST not in ('90','99'))
AND (ol.OBSAPR>=1)
AND ol.OBORNO not in('1972100017')
AND OBCONO = 410
AND OKCONO = 410
AND OKCUCL = '101'
AND OKCUNO NOT LIKE '%L100000001%'
GROUP BY oh.OAORNO,oh.OAORDT,oh.OACUNO,ol.OBSMCD,ol.OBITNO,cu.OKSDST,cu.OKCFC1,oh.OANTLA,oh.OAOTDP,it.MMFUDS,ol.OBPIDE,cu.OKCUCL,oh.OAADID,ol.OBCONO,oh.OARLDT,OKSMCD,OKCUNO
ORDER BY oh.OAORNO,OAORDT ASC
Data prepare Complete . . . All Rows For Upload -> [ 3671 ]