/* Formatted on 2011/04/21 14:24 (Formatter Plus v4.8.8) */
SELECT oma00, 'ds01' plant, ar_no, ar_dat, cust_no, sal_no, gp_no, gp_nm,
ar_amt x, upy_dat, cust_nm, sal_nm, curr
FROM (SELECT oma00, oma01 ar_no, oma02 ar_dat, oma03 cust_no, oma14 sal_no,
occ02 cust_nm, gen02 sal_nm, occ43 gp_no, oab02 gp_nm,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) ar_amt,
ROUND (SYSDATE - oma02) upy_dat, oma23 curr
FROM ds01.oma_file,
ds01.occ_file,
ds01.gen_file,
ds01.oab_file,
ds01.oga_file,
ds01.oag_file
WHERE
oma14 = gen01(+)
AND oma03 = occ01(+)
AND occ43 = oab01(+)
AND oma16 = oga01(+)
AND oma32 = oag01(+)
AND omaconf = 'Y'
AND omavoid = 'N'
AND SUBSTR (oma03, 1, 1) <> 'E'
AND oma02 <= SYSDATE
AND SYSDATE - oma02 > 1
AND ( oma61 > 0
OR oma01 IN (
SELECT oob06
FROM ds01.ooa_file, ds01.oob_file
WHERE ooa01 = oob01 AND ooaconf = 'Y'
AND ooa02 > SYSDATE)
))
UNION ALL
SELECT oma00, 'ds02' plant, ar_no, ar_dat, cust_no, sal_no, gp_no, gp_nm,
ar_amt x, upy_dat, cust_nm, sal_nm, curr
FROM (SELECT oma00, oma01 ar_no, oma02 ar_dat, oma03 cust_no, oma14 sal_no,
occ02 cust_nm, gen02 sal_nm, occ43 gp_no, oab02 gp_nm,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) ar_amt,
ROUND (SYSDATE - oma02) upy_dat, oma23 curr
FROM ds02.oma_file,
ds02.occ_file,
ds02.gen_file,
ds02.oab_file,
ds02.oga_file,
ds02.oag_file
WHERE
oma14 = gen01(+)
AND oma03 = occ01(+)
AND occ43 = oab01(+)
AND oma16 = oga01(+)
AND oma32 = oag01(+)
AND omaconf = 'Y'
AND omavoid = 'N'
AND SUBSTR (oma03, 1, 1) <> 'E'
AND oma02 <= SYSDATE
AND SYSDATE - oma02 > 1
AND ( oma61 > 0
OR oma01 IN (
SELECT oob06
FROM ds02.ooa_file, ds02.oob_file
WHERE ooa01 = oob01 AND ooaconf = 'Y'
AND ooa02 > SYSDATE)
))
UNION ALL
SELECT oma00, 'ds03' plant, ar_no, ar_dat, cust_no, sal_no, gp_no, gp_nm,
ar_amt x, upy_dat, cust_nm, sal_nm, curr
FROM (SELECT oma00, oma01 ar_no, oma02 ar_dat, oma03 cust_no, oma14 sal_no,
occ02 cust_nm, gen02 sal_nm, occ43 gp_no, oab02 gp_nm,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) ar_amt,
ROUND (SYSDATE - oma02) upy_dat, oma23 curr
FROM ds03.oma_file,
ds03.occ_file,
ds03.gen_file,
ds03.oab_file,
ds03.oga_file,
ds03.oag_file
WHERE
oma14 = gen01(+)
AND oma03 = occ01(+)
AND occ43 = oab01(+)
AND oma16 = oga01(+)
AND oma32 = oag01(+)
AND omaconf = 'Y'
AND omavoid = 'N'
AND SUBSTR (oma03, 1, 1) <> 'E'
AND oma02 <= SYSDATE
AND SYSDATE - oma02 > 1
AND ( oma61 > 0
OR oma01 IN (
SELECT oob06
FROM ds03.ooa_file, ds03.oob_file
WHERE ooa01 = oob01 AND ooaconf = 'Y'
AND ooa02 > SYSDATE)
))
UNION ALL
SELECT oma00, 'ds08' plant, ar_no, ar_dat, cust_no, sal_no, gp_no, gp_nm,
ar_amt x, upy_dat, cust_nm, sal_nm, curr
FROM (SELECT oma00, oma01 ar_no, oma02 ar_dat, oma03 cust_no, oma14 sal_no,
occ02 cust_nm, gen02 sal_nm, occ43 gp_no, oab02 gp_nm,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) ar_amt,
ROUND (SYSDATE - oma02) upy_dat, oma23 curr
FROM ds08.oma_file,
ds08.occ_file,
ds08.gen_file,
ds08.oab_file,
ds08.oga_file,
ds08.oag_file
WHERE
oma14 = gen01(+)
AND oma03 = occ01(+)
AND occ43 = oab01(+)
AND oma16 = oga01(+)
AND oma32 = oag01(+)
AND omaconf = 'Y'
AND omavoid = 'N'
AND SUBSTR (oma03, 1, 1) <> 'E'
AND oma02 <= SYSDATE
AND SYSDATE - oma02 > 1
AND ( oma61 > 0
OR oma01 IN (
SELECT oob06
FROM ds08.ooa_file, ds08.oob_file
WHERE ooa01 = oob01 AND ooaconf = 'Y'
AND ooa02 > SYSDATE)
))
全站熱搜
留言列表
站台介紹 (1)