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

Writerbay.net

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