troubleshooting Question

Need help deciphering SQL statement

Avatar of maximus1974
maximus1974 asked on
Oracle DatabaseSQL* oracle syntax
3 Comments2 Solutions87 ViewsLast Modified:
Need help figuring out where ba_wo_excusable_delay comes from.

select wodates2."WO_NUMBER",wodates2."PN",wodates2."DESCRIPTION",wodates2."WOO_AUTO_KEY",wodates2."OPEN_FLAG",wodates2."SHOP",wodates2."CUSTOMER_CODE",wodates2."CUSTOMER",wodates2."CUST_TYPE",wodates2."CONTRACT_TYPE",wodates2."CONTRACT_NUMBER",wodates2."SHOP_TYPE",wodates2."WO_TYPE",wodates2."WORK_PERFORMED",wodates2."SYSCM_AUTO_KEY",wodates2."ACCT_COMPANY",wodates2."STATUS",wodates2."RECEIVED_DATE",wodates2."ENTRY_DATE",wodates2."CLOSE_DATE",wodates2."DATE_MOVED_TO_QUOTING",wodates2."CAA_DATE",wodates2."CLOSE_DATE_ORIG",wodates2."SHIP_DATE",wodates2."SHIP_HOLD",wodates2."FIRST_QUOTE_SENT",wodates2."LAST_QUOTE_APPROVED",wodates2."RO_NUMBER",wodates2."DUE_DATE",wodates2."MANUAL_ECD",wodates2."SO_NUMBER",wodates2."EXCHANGE_DATE",wodates2."EXCHANGE_TYPE_CODE",wodates2."CONTRACT_TAT",wodates2."TAT_START_DATE",wodates2."TAT_STOP_DATE",wodates2."TAT_STOP_DATE_CUST"
,round(nvl(tat_stop_date,sysdate)-tat_start_date) gross_tat
,ba_wo_excusable_delay(woo_auto_key,null) excusable_delay
,round(nvl(tat_stop_date,sysdate)-tat_start_date)-nvl(ba_wo_excusable_delay(woo_auto_key,null),0) net_tat
from (
select wodates.*
,trunc(case when received_date is null then trunc(entry_date) else case when received_date<entry_date then trunc(received_date) else trunc(entry_date) end end) tat_start_date
,trunc(case when work_performed is null or work_performed='SCRAP' then close_date_orig else case when caa_date is not null then caa_date else case when wo_type='Internal' then close_date_orig else case when ship_hold='Y' then close_date_orig else case when close_date is not null and trunc(ship_date) is null then close_date else trunc(ship_date) end end end end end) tat_stop_date
,case when exchange_type_code=5 and exchange_date is not null then exchange_date else trunc(case when work_performed is null or work_performed='SCRAP' then close_date_orig else case when caa_date is not null then caa_date else case when wo_type='Internal' then close_date_orig else case when ship_hold='Y' then close_date_orig else trunc(ship_date) end end end end) end TAT_STOP_DATE_CUST
from (
select woo.si_number WO_NUMBER
,pnm.pn
,pnm.description
,woo.woo_auto_key
,woo.open_flag
,dpt.dept_name SHOP
,cmp.company_code CUSTOMER_CODE
,cmp.company_name CUSTOMER
,decode(cmp.cv_udf_002,'T','PBH','TM') CUST_TYPE
,cth.SDF_CTH_001 CONTRACT_TYPE
,cth.contract_number
,case when dpt.dept_name like '%SUB%' then 'SUB' else case when  dpt.dept_name like '%GSE%' then case when (select max(ro_number) from ro_header inner join ro_detail on ro_header.roh_auto_key=ro_detail.roh_auto_key where ro_detail.woo_auto_key=woo.woo_auto_key) is null then 'GSTE' else 'GSTE SUB' end else 'IN-HOUSE' end end SHOP_TYPE
,woo.wo_type
,wfw.code work_performed
,woo.syscm_auto_key
,syscm.company_name ACCT_COMPANY
,wos.description status
, (select min(case when rch.arrival_date is null or rch.arrival_date>rch.entry_date+5 or rch.arrival_date<rch.entry_date-100 then rch.entry_date else rch.arrival_date end) from rc_header rch inner join rc_detail rcd on rch.rch_auto_key=rcd.rch_auto_key where rcd.woo_auto_key=woo.woo_auto_key) received_date
,trunc(woo.entry_date) entry_date
,trunc(woo.close_date) close_date
,(select trunc(min(stamptime)) from audit_trail where source_table='WOO' and source_field='STATUS' and new_value='QUOTING' and source_ak=woo.woo_auto_key) DATE_MOVED_TO_QUOTING
,(select trunc(min(stamptime)) from audit_trail where source_table='WOO' and source_field='STATUS' and new_value='CAA' and source_ak=woo.woo_auto_key) CAA_DATE
,trunc(woo.close_date_orig) close_date_orig
,nvl(woo.MSG_SHIP_TO_CUST_DATE,trunc(nvl((select max(smh.ship_date) from sm_header smh where smh.woo_auto_key=woo.woo_auto_key),Null))) ship_date
,decode((select count(ssc_auto_key) from sm_status_change inner join sm_header on sm_status_change.smh_auto_key=sm_header.smh_auto_key where sm_status_change.sms_auto_key in (9,11,3,5) and sm_header.woo_auto_key=woo.woo_auto_key),0,'N','Y') ship_hold
,nvl(woo.MSG_QUOTE_SENT_DATE,trunc(ba_wo_first_quote_sent(woo.woo_auto_key))) first_quote_sent
,nvl(woo.MSG_QUOTE_APPR_DATE,trunc(ba_wo_last_quote_approved(woo.woo_auto_key))) last_quote_approved
,(select max(ro_number) from ro_header inner join ro_detail on ro_header.roh_auto_key=ro_detail.roh_auto_key where ro_detail.woo_auto_key=woo.woo_auto_key) ro_number
,woo.due_date
,woo.manual_ecd
,(select max(so_number) from so_header inner join so_detail on so_header.soh_auto_key=so_detail.soh_auto_key inner join exchange on so_detail.sod_auto_key=exchange.sod_auto_key where exchange.woo_auto_key=woo.woo_auto_key) so_number
,(select max(ship_date) from so_detail inner join exchange on so_detail.sod_auto_key=exchange.sod_auto_key where exchange.woo_auto_key=woo.woo_auto_key) exchange_date
,(select max(exchange_type_code) from so_header inner join so_detail on so_header.soh_auto_key=so_detail.soh_auto_key inner join exchange on so_detail.sod_auto_key=exchange.sod_auto_key where exchange.woo_auto_key=woo.woo_auto_key) exchange_type_code
,ctd.tat_days contract_TAT
from wo_operation woo
inner join sys_companies syscm
on woo.syscm_auto_key=syscm.syscm_auto_key
left join parts_master pnm
on woo.pnm_auto_key=pnm.pnm_auto_key
left join wo_status wos
on woo.wos_auto_key=wos.wos_auto_key
left join department dpt
on woo.dpt_auto_key=dpt.dpt_auto_key
left join companies cmp
on woo.cmp_auto_key=cmp.cmp_auto_key
left join wo_status wos
on woo.wos_auto_key=wos.wos_auto_key
--left join sm_header smh
--on woo.woo_auto_key=smh.woo_auto_key
left join contract_detail ctd
on woo.ctd_auto_key=ctd.ctd_auto_key
left join contract_header cth
on ctd.cth_auto_key=cth.cth_auto_key
left join work_flows wfw
on woo.wfw_auto_key=wfw.wfw_auto_key
where ((woo.syscm_auto_key=2 and woo.entry_date>sysdate-2*365) or (woo.syscm_auto_key=1 and woo.entry_date>sysdate-500) or (woo.syscm_auto_key=3 and woo.entry_date>sysdate-1*365)) and woo.wo_type in ('Internal','External') and (dpt.dept_name like 'SH %' or dpt.dept_name like 'SUB %') and woo.tear_down='F' and woo.woo_exc_to is null and woo.wob_auto_key is null and wos.status_type<>'Cancel')
wodates
) wodates2
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros