select stage,boh,wip,move,target,rwip,qwip,hwip,bmw,main,amw,cwip,cmove, ct,p1,engwip,cengwip,bwip,
keystage,nvl(wolinenow,0)wolinenow,nvl(wolinenext,0)wolinenext,stageorder,area
from (
SELECT aa.stageorder,aa.stage,nvl(boh,0) boh,wip,rwip,qwip,hwip,
ahand BMW,main,below AMW,move,cmove,nvl(round(ee.ct,2),0) ct ,p1,cwip,engwip,cengwip,bwip,
target keystage,wolinenow,wolinenext,nvl(dd.target,0) target,aa.area
FROM(SELECT /*+use_hash(a b)*/ b.stageorder,b.stage,b.area,
NVL(SUM(DECODE(a.bank,'',a.componentqty,0)),0) wip,
SUM(DECODE(a.adstatus,'Running',a.componentqty,0)) rwip,
SUM(DECODE(a.adstatus,'Waiting',a.componentqty,0)) qwip,
SUM(DECODE(a.adstatus,'Holding',a.componentqty,0)) hwip,
nvl(SUM(DECODE(a.bank, null ,0,a.componentqty)),0) bwip,
nvl(ahand,0) ahand , nvl(main,0) main, nvl(below,0) below ,
NVL(SUM(SUM(DECODE(a.bank,'',a.componentqty,0)))
over(ORDER BY b.stageorder DESC),0) cwip,
SUM(CASE WHEN a.priority=1 AND a.bank IS NULL THEN a.componentqty ELSE 0 END) P1,
nvl(engwip,0) engwip,
nvl(sum(engwip) over (ORDER BY b.stageORDER DESC),0) cengwip,wolinenow,wolinenext
FROM (SELECT priority,bank,componentqty,adstatus,b.stage
FROM SDB_TB_INFO_WIP a,
(select stage,location from sdb_tb_info_stage) b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
WHERE SUBSTR(lottype,1,1) IN ('E','L','T','P','R','M','B','X')
and a.stage=b.stage
AND b.location=c.area
and c.section LIKE :section
and technology like :technology
and b.location like :area
and priority like :priority
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet) a,
(select stageorder,stage,AREA
from (SELECT b1.stageorder,b1.stage,B2.LOCATION AREA
FROM SDB_TB_INFO_STAGE b1,
(select stage,location from SDB_TB_INFO_STAGE
where location like :area
and stagegroup ='ALL') b2,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')b3
WHERE b1.stagegroup =:tech
and b1.stage=b2.stage
AND b2.location=b3.area
and b3.section LIKE :section
UNION ALL
SELECT UNIQUE stageorder,stage,AREA
FROM(SELECT UNIQUE b.stageorder,a.stage,B.LOCATION AREA
FROM SDB_TB_INFO_WIP a,
(select * from sdb_tb_info_stage where stagegroup ='ALL') b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
WHERE lottype NOT IN ('C','V','D','Z','Y')
and a.stage=b.stage
AND b.location=c.area
and c.section LIKE :section
and technology like :technology
and B.location like :area
and priority like :priority
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet
UNION
SELECT UNIQUE b.stageorder,a.stage,B.LOCATION AREA
FROM SDB_TB_MOVE_WIP a,
(select * from sdb_tb_info_stage where stagegroup ='ALL') b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
WHERE SUBSTR(lottype,1,1) IN ('E','L','T','P','R','M','B','X')
and a.stage=b.stage
AND b.location=c.area
and c.section LIKE :section
and technology like :technology
and priority like :priority
and b.location like :area
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet)
WHERE stage NOT IN (SELECT b1.stage
FROM SDB_TB_INFO_STAGE b1,
(select stage,location from SDB_TB_INFO_STAGE
where location like :area
and stagegroup ='ALL') b2,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')b3
WHERE b1.stagegroup =:tech
AND b2.location=b3.area
and b3.section LIKE :section
and b1.stage=b2.stage))
where stage in (select stage from sdb_tb_stg_mfgarea
where processtype like :processtype))b,
(select unique stage,
sum(case when stepno=stepnomain then componentqty else 0 end) main,
sum(case when stepno>stepnomain then componentqty else 0 end) Ahand,
sum(case when stepno<stepnomain then componentqty else 0 end) Below
from (select unique lotid, b.stepno,b.componentqty,b.stage,a.stepno stepnomain,flag
from (select * from sdb_tb_mfg_main_step where flag ='M') a,
(select * from sdb_tb_info_wip
where lottype not in ('V','C','D','Z','Y')
and bank is null
and priority like :priority
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet
and substr(planname,2,5) like :technology
and location like :area) b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
where a.process =b.planname
AND b.location=c.area
and c.section LIKE :section
and a.stage=b.stage)
group by stage) c,
(select unique b.stage,sum(componentqty) engwip
from sdb_tb_info_wip a,
(select stage,location from sdb_tb_info_stage where stagegroup ='ALL') b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
where lottype in ('L','T')
AND b.location=c.area
and c.section LIKE :section
and bank is null
and technology like :technology
and b.location like :area
and a.stage=b.stage
and priority like :priority
and priority <=:priorityf
group by b.stage)d,
(SELECT Max(wolinenow) wolinenow
FROM (SELECT WOLINE wolinenow FROM sdb_tb_mfg_woline
WHERE period='CMPL'
AND productname IN('ALL','NA')
AND tech LIKE :tech
UNION ALL
SELECT '' FROM dual))e,
(SELECT Max(wolinenext) wolinenext
FROM (SELECT WOLINE wolinenext FROM sdb_tb_mfg_woline
WHERE period='N1MPL'
AND productname IN('ALL','NA')
AND tech LIKE :tech
UNION ALL
SELECT '' FROM dual))f
WHERE a.stage(+)=b.stage
and b.stage=d.stage(+)
and b.stage =c.stage(+)
GROUP BY b.stageorder,b.stage,b.area,ahand , main, below,engwip,wolinenow,wolinenext
ORDER BY stageorder) aa,
(SELECT /*+use_hash(a b)+*/b.stage,b.stageorder,NVL(SUM(a.componentqty),0) move,
NVL(sum(SUM(a.componentqty))
over(ORDER BY b.stageorder DESC),0) cmove
FROM (SELECT a.* FROM SDB_TB_MOVE_WIP a,sdb_tb_info_stage b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
WHERE SUBSTR(lottype,1,1) IN ('E','L','T','P','R','M','B','X')
and b.stagegroup='ALL'
and a.stage =b.stage(+)
AND b.location=c.area
and c.section LIKE :section
and technology like :technology
and b.location like :area
and priority like :priority
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet) a,
(select stageorder,stage
from (SELECT b1.stageorder,b1.stage
FROM SDB_TB_INFO_STAGE b1,
(select stage,location from SDB_TB_INFO_STAGE
where location like :area
and stagegroup ='ALL') b2 ,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')b3
WHERE b1.stagegroup =:tech
AND b2.location=b3.area
and b3.section LIKE :section
and b1.stage=b2.stage
UNION ALL
SELECT UNIQUE stageorder,stage
FROM(SELECT UNIQUE b.stageorder,a.stage
FROM SDB_TB_INFO_WIP a,
(select * from sdb_tb_info_stage where stagegroup ='ALL') b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
WHERE lottype NOT IN ('C','V','D','Z','Y')
and a.stage=b.stage
AND b.location=c.area
and c.section LIKE :section
and technology like :technology
and B.location like :area
and priority like :priority
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet
UNION
SELECT UNIQUE b.stageorder,a.stage
FROM SDB_TB_MOVE_WIP a,
(select * from sdb_tb_info_stage where stagegroup ='ALL') b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
WHERE SUBSTR(lottype,1,1) IN ('E','L','T','P','R','M','B','X')
and a.stage=b.stage
AND b.location=c.area
and c.section LIKE :section
and technology like :technology
and priority like :priority
and b.location like :area
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet)
WHERE stage NOT IN (SELECT b1.stage
FROM SDB_TB_INFO_STAGE b1,
(select stage,location from SDB_TB_INFO_STAGE
where location like :area
and stagegroup ='ALL') b2,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')b3
WHERE b1.stagegroup =:tech
AND b2.location=b3.area
and b3.section LIKE :section
and b1.stage=b2.stage))
where stage in (select stage from sdb_tb_stg_mfgarea
where processtype like :processtype)) b
WHERE b.stage = a.stage(+)
GROUP BY b.stage,b.stageorder
ORDER BY stageorder ) bb,
(SELECT b.stageorder,b.stage,b.location,
NVL(SUM(DECODE(bank,'',a.componentqty,0)),0) boh
FROM (SELECT priority,bank,componentqty,adstatus,b.stage
FROM SDB_TB_INFO_WIP_hist a,
(select stage,location from sdb_tb_info_stage) b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
WHERE SUBSTR(lottype,1,1) IN ('E','L','T','P','R','M','B','X')
and technology like :technology
and b.location like :area
and a.stage=b.stage
AND b.location=c.area
and c.section LIKE :section
and priority like :priority
and priority <=:priorityf
and SUBSTR(lottype,1,1) not in :lottypel
and lottype not in :lottypet) a,
(SELECT stageorder,stage,location FROM SDB_TB_INFO_STAGE
WHERE stagegroup=:tech)b
WHERE a.stage(+)=b.stage
GROUP BY b.stageorder,b.stage,b.location ORDER BY stageorder) cc,
(select unique stage,sum(target) target from sdb_tb_mfg_target
where activity ='MoveByTechStage'
and tech like :technology
group by stage) dd,
(select stage,avg(ct) ct from sdb_tb_mfg_ct b,
(SELECT keytype section,keyvalue area
FROM SDB_TB_REPORT_CONFIG WHERE LINKNAME='SECTION_LOCATION')c
where tech like :technology
AND b.location=c.area
and c.section LIKE :section
and location like :area
group by stage)ee
WHERE aa.stageorder=bb.stageorder
AND aa.stage=bb.stage
and aa.stage=dd.stage(+)
and aa.stage=ee.stage(+)
and aa.stageorder =cc.stageorder(+)
and aa.stage=cc.stage(+)
order by stageorder)
where to_char(target) !=:target
Tools 
Help
