SELECT plant,XPATH,x1,x2,x3,x4,x5,x6,to_char(cdate,'yyyy/mm/dd') cdate,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,p0a,p1a,1,p2a,p3a,p4a,p5a,p6a FROM ( SELECT DISTINCT 'ds' plant,XPATH,x1,x2, x3, x4, x5,x6,cdate, p0a, p1a, p2a, p3a, p4a, p5a, p6a,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12
FROM (SELECT bmb02 as x1,XPATH,to_char(bmb02) AS x2, bmb03 AS x3, x21 AS x4, x22 AS x5,x23 AS x6,bmb04 cdate,bmb06 AS p0a,
D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,
ds.fnd_last_pmj07 ('ds',
bmb03,
'2011/04/21',
31.837,
'1',
'2'
) p1a,
ds.fnd_last_pmj07 ('ds',
bmb03,
'2011/04/21',
31.837,
'1',
'1'
) p2a,
ds.fnd_last_pmj07x (
bmb03,
31.837,
'pmc03'
) p3a,
ds.fnd_last_pmj07x (
bmb03,
31.837,
'pmj07'
) p4a,
ds.fnd_last_pmn31x (
bmb03,
31.837,
'pmc03'
) p5a,
ds.fnd_last_pmn31x (
bmb03,
31.837,
'pmn31'
) p6a
FROM (SELECT XPATH,bmb02, bmb03,bmb04,bmb06, bmb16,
DECODE (bmb16,
'0', '不可取替代',
'1', '取代料',
'2', '替代料',
'不可取替代'
) xx,
isleaf
FROM (SELECT LEVEL, bmb01, bmb02, bmb03,bmb04,SYS_CONNECT_BY_PATH(bmb01,'/') XPATH,
bmb07, ((bmb06/bmb07)*(1+bmb08/100) ) AS bmb06, bmb16,
CONNECT_BY_ISLEAF AS isleaf
FROM ds.bmb_file
WHERE CONNECT_BY_ISLEAF > 0
AND (bmb04 <= sysdate OR bmb04 IS NULL)
AND (bmb05 > sysdate OR bmb05 IS NULL)
START WITH bmb01 = 'P0022015000'
CONNECT BY bmb01 = PRIOR bmb03
ORDER BY 1, 2, 3)
WHERE isleaf > 0
ORDER BY 1, 2, 3) x1,
(SELECT ima01, ima02 x21, ima021 x22,azf03 x23,
imaud01 D1,imaud02 D2,imaud03 D3,imaud04 D4,imaud05 D5,imaud06 D6,imaud07 D7,imaud08 D8,imaud09 D9,imaud10 D10,
imaud11 D11,imaud12 D12
FROM ds.ima_file,ds.azf_file
WHERE ima09 = azf01(+)
) x2
WHERE x1.bmb03 = x2.ima01
ORDER BY 4,5)
UNION ALL
SELECT 'ds' plant,XPATH,bmb02 as x1, (bmb02 ||'-'|| xx ) AS x2,x AS x3, x31 AS x4, x32 AS x5,x33 AS x6,bmd05 as cdate,bmb06 AS p0a,
ds.fnd_last_pmj07 ('ds', x, '2011/04/21', 31.837, '1', '2') p1a,
ds.fnd_last_pmj07 ('ds', x, '2011/04/21', 31.837, '1', '1') p2a,
ds.fnd_last_pmj07x ( x, 31.837, 'pmc03') p3a,
ds.fnd_last_pmj07x ( x, 31.837, 'pmj07') p4a,
ds.fnd_last_pmn31x ( x, 31.837, 'pmc03') p5a,
ds.fnd_last_pmn31x ( x, 31.837, 'pmn31') p6a,
D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12
FROM (SELECT XPATH,bmd01,bmb02, bmb03,bmd05, bmb06, bmb16,
DECODE (bmb16,
'0', '不可取替代',
'1', '取代料',
'2', '替代料',
'不可取替代'
) xx,
DECODE (bmb16,
'1', NVL (bmd04, bmb03),
'2', NVL (bmd04, bmb03),
bmb03
) x,isleaf
FROM (SELECT LEVEL, bmb01, bmb02, bmb03,bmb04, bmb07,SYS_CONNECT_BY_PATH(bmb01,'/') XPATH, ((bmb06/bmb07)*(1+bmb08/100) ) AS bmb06,
bmb16, CONNECT_BY_ISLEAF AS isleaf
FROM ds.bmb_file
WHERE CONNECT_BY_ISLEAF > 0
AND (bmb04 <= sysdate OR bmb04 IS NULL)
AND (bmb05 > sysdate OR bmb05 IS NULL)
START WITH bmb01 = 'P0022015000'
CONNECT BY bmb01 = PRIOR bmb03
ORDER BY 1, 2, 3) t1,
(SELECT bmd08, bmd01, bmd02, bmd04,bmd05, bmd07
FROM ds.bmd_file
WHERE bmdacti = 'Y' AND bmd05 <= sysdate AND (bmd06 is null OR bmd06 > sysdate) ) t2
WHERE t1.bmb01 = t2.bmd08(+)
AND t1.bmb03 = t2.bmd01(+)
AND t1.bmb16 = t2.bmd02(+)
AND isleaf > 0
ORDER BY 4,5) x1,
( SELECT ima01, ima02 x31, ima021 x32,azf03 x33,
imaud01 D1,imaud02 D2,imaud03 D3,imaud04 D4,imaud05 D5,imaud06 D6,imaud07 D7,imaud08 D8,imaud09 D9,imaud10 D10,
imaud11 D11,imaud12 D12
FROM ds.ima_file,ds.azf_file
WHERE ima09 = azf01(+)
) x3
WHERE x1.x = x3.ima01 and x1.xx not like '%不可取替代%' AND bmd01 is not null
ORDER BY 2 ) WHERE 1 = 1 order by 3,6,4
全站熱搜
留言列表
站台介紹 (1)