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, ‘M
Ace your studies with our custom writing services! We've got your back for top grades and timely submissions, so you can say goodbye to the stress. Trust us to get you there!
Order a Similar Paper Order a Different Paper
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
Looking for top-notch essay writing services? We've got you covered! Connect with our writing experts today. Placing your order is easy, taking less than 5 minutes. Click below to get started.
Order a Similar Paper Order a Different Paper