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