Budget Center
Rewrite this two seperate queries using the new methods – joins (left join where necessary )
Select
a.APP_UNIT_CENTER as “Budget Center”,
a.PARENT as “Parent WO”,
par.STATUS
TO_CHAR(a.STATUSDATE, ‘MM/DD/YYYY’) as “PWO status Date”,
to_CHAR(a.TARGCOMPDATE, ‘MM/DD/YYYY’) as “PWO Target Finish Date”,
SUBSTR(a.GLACCOUNT, 27, 44 ) as “GL Account”,
a.WORKTYPE as “Work Type”,
a.APP_SUBTYPE as “Sub Type”,
a.APP_STRUCTURETYPE as “Structure Type”,
to_char(a.WORKLOCATION) as “Assigned Labor”,
a.APP_CUSTNAME as “Customer Name”,
a.APP_ADDSTRTNUM as “Street Number”,
a.APP_ADDSTREET as “Street Name”,
a.APP_ADDCITY as “City”,
a.APP_ADDZIP as “Zipcode”,
a.APP_ADDCOMBO as “Full Address”,
SUBSTR(a.APP_CUSTPHONEPRIM, 1, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 4, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 7, 4) as “Primary Phone”,
par.REPORTEDBY “Reported by”,
to_char(par.REPORTDATE, ‘MM/DD/YYYY’) as “Reported Date”,
a.REPORTEDBY “JP Reported by”,
to_char(a.REPORTDATE, ‘MM/DD/YYYY’) as “JP Reported Date”,
u.WONUM as “Office Analysis Task WO”,
u.STATUS as “Analysis Task Status”,
to_char(u.STATUSDATE, ‘MM/DD/YYYY’) as “Analysis Status Date”,
b.WONUM as “Investigate Task WO”,
b.STATUS as “Investigate Task Status”,
to_char(b.STATUSDATE, ‘MM/DD/YYYY’) as “Investigate Status Date”,
o.WONUM as “Complete OPA Task WO”,
o.STATUS as “OPA Task Status”,
to_char(o.STATUSDATE, ‘MM/DD/YYYY’) as “OPA Status Date”,
v.WONUM as “Engineering Review Task WO”,
v.STATUS as “Engineering Review Task Status”,
to_char(v.STATUSDATE, ‘MM/DD/YYYY’) as “Engineering Review Date”,
p.WONUM as “Obtain Permit Task WO”,
p.STATUS as “Permit Task Status”,
to_char(p.STATUSDATE, ‘MM/DD/YYYY’) as “Permit Status Date”,
c.WONUM as “Prepare Quote for Cust Task WO”,
c.STATUS as “Quote Task Status”,
to_char(c.STATUSDATE, ‘MM/DD/YYYY’) as “Quote Status Date”,
d.WONUM as “Cust Charges Task WO”,
d.STATUS as “Cust Charges Task Status”,
to_char(d.STATUSDATE, ‘MM/DD/YYYY’) as “Cust Charges Status Date”,
e.WONUM as “Released for Const Task WO”,
e.STATUS as “Released for Const Task Status”,
to_char(e.STATUSDATE, ‘MM/DD/YYYY’) as “Released for Const Status Date”,
f.WONUM as “Manifold Task WO”,
f.STATUS as “Manifold Task Status”,
to_char(f.STATUSDATE, ‘MM/DD/YYYY’) as “Manifold Status Date”,
a.WONUM as “CONS Task WO”,
a.STATUS as “CONS Task Status”,
to_char(a.STATUSDATE, ‘MM/DD/YYYY’) as “CONS Status Date”,
a.ACTFINISH as “CONS Actual Finish”,
i.status as “Work Order Status History”,
to_char(i.CHANGEDATE, ‘MM/DD/YYYY’) as “WO History Status Date”,
g.ALNVALUE as “Field Completion Date”,
SUBSTR (A.APP_EXTERNALID, 1, 13) as “CRM/CSB ID”,
a.APP_EXTERNALID as “Premise ID”,
a.APP_EVENTSUBTYPE as “Mat Code”,
a.APP_CUSTTYPE,
a.APP_WORKPLANBY as “Work Planned by ID”,
n.DISPLAYNAME as “Work Planned by Name”,
a.OWNER as “Supervisor”,
nn.DISPLAYNAME as “Owner Name”,
j.JPNUM as “JobPlan”
from workorder a,
workorder par,
(select * from workorder where APP_TASKTYPE =’INVEST’) b,
(select * from workorder where APP_TASKTYPE =’QUOTE’) c,
(select * from workorder where APP_TASKTYPE =’CC’) d,
(select * from workorder where APP_TASKTYPE =’RELEASED’) e,
(select * from workorder where APP_TASKTYPE =’OPA’) o,
(select * from workorder where APP_TASKTYPE =’PERMIT’) p,
(select * from workorder where APP_TASKTYPE =’OFCANL’) u,
(select * from workorder where APP_TASKTYPE =’ENGREV) v,
(select * from workorder where APP_TASKTYPE =’MANDMI’) f,
workorderspec g,
jobplan j,
person n,
person nn,
wostatus i
where
a.ISTASK =’1′
and a.siteid = ‘2280’
and a.APP_TASKTYPE = ‘CONS’
and PAR.APP_UNIT_CENTER IN (‘MICSC’, ‘LYNSC’, ‘ARDCT’)
and PAR.WORKTYPE IN (‘NC’, ‘SA’)
and PAR.STATUS IN ‘WAPPR’
–and a.STATUS NOT IN (‘CLOSE’, ‘COMP’, ‘EBERROR’, ‘FDCOMP’, ‘CAN’, ‘CANDUP’, ‘FDCANDISP’, ‘FLN’, ‘CAN-COMP’, ‘RECOMP’, ‘SADMCAN’)
and trunc(par.reportdate) >= to_date(‘2020-01-01’, ‘YYYY-MM-DD’)
and trunc(par.reportdate) <= to_date(‘2025-12-31’, ‘YYYY-MM-DD’)
and g.assetattrid = ‘ASBFIELDCOMPDATE’
and a.PARENT = b.PARENT(+)
and a.PARENT = c.PARENT(+)
and a.PARENT = d.PARENT(+)
and a.PARENT = e.PARENT(+)
and a.PARENT = o.PARENT(+)
and a.PARENT = p.PARENT(+)
and a.PARENT = u.PARENT(+)
and a.PARENT = v.PARENT(+)
and a.PARENT = f.PARENT(+)
and a.PARENT = par.wonum
and a.WONUM = g.WONUM
and a.wonum = i.wonum
and n.personid(+) = a.APP_WORKPLANBY
and nn.personid(+) = a.OWNER
and par.JPNUM = j.JPNUM (+)
I.status LIKE ‘WAPPR’
order by a.Parent DESC
This is the second query below –
Select
a.APP_UNIT_CENTER as “Budget Center”,
a.wonum as “Parent WO”,
a.STATUS as PARENT WO Status”,
a.WORKTYPE,
a.APP_SUBTYPE,
a.APP_CUSTNAME as “Customer Name”,
SUBSTR(a.APP_CUSTPHONEPRIM, 1, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 4, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 7, 4) as “Primary Phone”,
a.APP_ADDCOMBO as “Full Address”,
SUBSTR(A.APP_EXTERNALID, 1, 13) as “CRM/CSB ID”,
a.APP_ADDEXTERNALID as “Premise ID”,
a.APP_CUSTTYPE,
TO_CHAR(a.REPORTDATE, ‘MM/DD/YYYY’) as “Reported Date”,
TO_CHAR(a.SCHEDSTART, ‘MM/DD/YYYY’) as “Customer Appt Date”,
a.APP_WORKPLANBY as “Work Planned by ID”,
a.DISPLAYNAME as “Work Planned by Name”,
a.JPNUM
from workorder a,
jobplan j,
person n
where a.siteid = ‘2280’
and A.APP_UNIT_CENTER IN (‘MICSC’, ‘LYNSC’, ‘ARDCT’)
and a.ISTASK =’0′
and a.WORKTYPE in (‘SA’, ‘NC’)
and a.STATUS IN ‘WAPPR’
and trunc(a.reportdate) >= to_date(‘2020-01-01’, ‘YYYY-MM-DD’)
and trunc(a.reportdate) <= to_date(‘2025-12-31’, ‘YYYY-MM-DD’)
and n.personid(+) = a.APP_WORKPLANBY
and a.Parent is null
and a.JPNUM is null
and a.APP_JPCATEGORY is null
and j.DESCRIPTION is null
and a.APP_WBS4_SEGMENT is null
and a.JPNUM = j.JPNUM(+)
order by a.wonum