/* Formatted on 2011/04/21 14:20 (Formatter Plus v4.8.8) */
SELECT oma00, t1.plant, ar_no, ar_dat, cust_no, sal_no, gp_no, gp_nm, ar_amt,
upy_dat, cust_nm, sal_nm, curr, x, t2.occ01, t2.occ02, t2.userid,
t2.username
FROM (SELECT oma00, 'ds01' plant, ar_no, ar_dat, cust_no, sal_no, gp_no,
gp_nm, ar_amt, upy_dat, cust_nm, sal_nm, curr, x
FROM (SELECT oma00, oma01 ar_no, oma11 ar_dat, oma03 cust_no,
oma14 sal_no, occ02 cust_nm, gen02 sal_nm,
occ43 gp_no, oab02 gp_nm,
oma56t - oma57 + ds01.fnd_ar_addamt (oma01) ar_amt,
ROUND (SYSDATE - oma11) upy_dat, oma23 curr,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) x
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 oma11 <= SYSDATE
AND SYSDATE - oma11 > 1
AND ( oma56t - oma57 > 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, 'ds03' plant, ar_no, ar_dat, cust_no, sal_no, gp_no,
gp_nm, ar_amt, upy_dat, cust_nm, sal_nm, curr, x
FROM (SELECT oma00, oma01 ar_no, oma11 ar_dat, oma03 cust_no,
oma14 sal_no, occ02 cust_nm, gen02 sal_nm,
occ43 gp_no, oab02 gp_nm,
oma56t - oma57 + ds03.fnd_ar_addamt (oma01) ar_amt,
ROUND (SYSDATE - oma11) upy_dat, oma23 curr,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) x
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 oma11 <= SYSDATE
AND SYSDATE - oma11 > 1
AND ( oma56t - oma57 > 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, 'ds02' plant, ar_no, ar_dat, cust_no, sal_no, gp_no,
gp_nm, ar_amt, upy_dat, cust_nm, sal_nm, curr, x
FROM (SELECT oma00, oma01 ar_no, oma11 ar_dat, oma03 cust_no,
oma14 sal_no, occ02 cust_nm, gen02 sal_nm,
occ43 gp_no, oab02 gp_nm,
oma56t - oma57 + ds02.fnd_ar_addamt (oma01) ar_amt,
ROUND (SYSDATE - oma11) upy_dat, oma23 curr,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) x
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 oma11 <= SYSDATE
AND SYSDATE - oma11 > 1
AND ( oma56t - oma57 > 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, 'ds08' plant, ar_no, ar_dat, cust_no, sal_no, gp_no,
gp_nm, ar_amt, upy_dat, cust_nm, sal_nm, curr, x
FROM (SELECT oma00, oma01 ar_no, oma11 ar_dat, oma03 cust_no,
oma14 sal_no, occ02 cust_nm, gen02 sal_nm,
occ43 gp_no, oab02 gp_nm,
oma56t - oma57 + ds08.fnd_ar_addamt (oma01) ar_amt,
ROUND (SYSDATE - oma11) upy_dat, oma23 curr,
ROUND (CASE
WHEN SUBSTR (oma00, 1, 1) = '2'
THEN (oma54t - oma55) * (-1)
ELSE (oma54t - oma55)
END,
2
) x
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 oma11 <= SYSDATE
AND SYSDATE - oma11 > 1
AND ( oma56t - oma57 > 0
OR oma01 IN (
SELECT oob06
FROM ds08.ooa_file, ds08.oob_file
WHERE ooa01 = oob01
AND ooaconf = 'Y'
AND ooa02 > SYSDATE)
))
ORDER BY curr) t1,
(SELECT DISTINCT plant, occ01, occ02, userid, username
FROM (SELECT 'ds01' plant, occ01, occ02, occ04, userid,
username
FROM ds01.occ_file, ds.staff_file
WHERE occ04 = userid(+)
UNION ALL
SELECT 'ds02' plant, occ01, occ02, occ04, userid,
username
FROM ds02.occ_file, ds.staff_file
WHERE occ04 = userid(+)
UNION ALL
SELECT 'ds03' plant, occ01, occ02, occ04, userid,
username
FROM ds03.occ_file, ds.staff_file
WHERE occ04 = userid(+)
UNION ALL
SELECT 'ds04' plant, occ01, occ02, occ04, userid,
username
FROM ds04.occ_file, ds.staff_file
WHERE occ04 = userid(+)
UNION ALL
SELECT 'ds07' plant, occ01, occ02, occ04, userid,
username
FROM ds07.occ_file, ds.staff_file
WHERE occ04 = userid(+)
UNION ALL
SELECT 'ds08' plant, occ01, occ02, occ04, userid,
username
FROM ds08.occ_file, ds.staff_file
WHERE occ04 = userid(+))
ORDER BY 1, 2) t2
WHERE t1.plant = t2.plant AND t1.cust_no = t2.occ01
全站熱搜
留言列表
站台介紹 (1)