Complex report generator using huge SQL Query

Question

As the title say, I have a huge SQL Query which was built over the time (as new requests came, fast workarounds were demanded).More, there were three DB Admins who contributed to this query, and I'm just a poor programmer who has to deal with it.

This query's purpose is to build a report, using some functions and apparently a HUGE SELECT. Now, the problem is that, obviously, it takes 3 hours to complete.

What I'd like is an idea (multiple ideas are welcome) on how could I reduce the run time of this query.

SPOILER ( huge query bellow ):

 CREATE OR REPLACE FORCE VIEW "VDO"."V$RAP_PDM_SI_TRANSITIONS" ("FIRST_TRANS_ID", "TICKET_ID", "TICKET_TYPE", "SERVICE_ID", "New Case at", "FSR(first) at", "FS Done(last) at", "FS Done(last) by", "FS Validation(last) at", "FS validation(last) by", "Contract Definition(first) at", "Contract Signature(last) at", "Config Details(last) at", "Config Details(last) by", "SI(first) at", "SI(first) by", "SI(last) at", "Implementation need by date", "Srv Impl Due Date", "Emergency date", "Project date", "HLD_LL", "WO Generated at", "WO_GEN_FIRST_BY", "WO Closed at", "WO Closed by", "WO pending Stop Clock", "Testing(first) at", "Testing(last) at", "LL_EQP_WO", "START_BILLING_AT", "Start Billing by", "Billing Done at", "Billing Done by", "Closed at", "Cancelled at", "Cancelled by", "Start Porting at", "Porting Req Acc (last) at", "Porting Req Acc (first) at", "START_PORTING_IMP_F_AT", "Porting date", "PORTING_TESTING_L_AT", "First Comment", "First Comment At", "Last Comment", "Comment At", "Test rejected at", "CLIENT_NAME", "IS_CLIENT_WHOLESALER", "FINAL_USER_NAME", "REQUEST_TYPE", "LNO", "SERVICE_TYPE", "SRV_CAPACITY", "COUNTY_A", "LOCALITY_NAME_A", "ADDRESS_A", "COUNTY_Z", "LOCALITY_NAME_Z", "ADDRESS_Z", "CRT_STAT", "PDM_ASSIGNMENT", "BDE_ASSIGNMENT", "DSE_ASSIGNMENT", "EQP_LOC_A", "EQP_LOC_Z", "PACKET_ID", "PACKET_TYPE", "CLIENT_TYPE", "SLA Implementare", "WK_DIF_SI_TESTING", "WK_DIF_SI_PORT_TESTING", "REASON_STOP_CLOCK", "REASON_PENDING_OTHERS", "DURATION_STOP_CLOCK", "DURATION_PENDING_OTHERS", "SLA_IMPL_VALUE", "SLA_DEVIATION", "SLA_STATUS", "IS_INITIATOR_BSR", "CASE_OPENED_BY", "WK_DIF_STOP_CLOCK", "DELIVERY_PENDING_AT_FIRST", "DELIVERY_PENDING_AT_LAST", "DELIVERY_RESUME_DATE_FIRST", "DELIVERY_RESUME_DATE_LAST", "SLA_IMPLEMENTATION_TYPE") AS 
 select first_trans_id, ticket_id, ticket_type, service_id,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,1,'F') "New Case at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,2,'F') "FSR(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'L') "FS Validation(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,16,'L') "FS validation(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'F') "Contract Definition(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,30,'L') "Contract Signature(last) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) by",
infotick.GETDATEFORSTATECORR(ticket_id, ticket_type,18,'L') "SI(last) at",
infotick.getDateValueForFieldState(ticket_id, ticket_type,'IMPL_NEED_BY_DATE', 'S$START_IMPL', 18, 'L') "Implementation need by date",
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'SRV_IMPL_DUE_TIME', 'S$START_IMPL', 18, 'L') "Srv Impl Due Date",
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'EMERGENCY_DATE', 'S$START_IMPL', 18, 'L') "Emergency date",
infotick.getdatecorrvalueforfieldstate(ticket_id, ticket_type, 'PROJECT_DATE', 'S$START_IMPL', 18, 'L') "Project date",
get_ll_eqp(infotick.getStringValueForFieldState(ticket_id, ticket_type, 'LL_EQUIP_ID', 'S$HLD', 167, 'L')) hld_ll,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,17,'F') "WO Generated at",
infotick.getUserForState(ticket_id, ticket_type, 17, 'F') wo_gen_first_by,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed by",
wo_pend_stop_clock "WO pending Stop Clock",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'F') "Testing(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'L') "Testing(last) at",
get_ll_eqp(get_ll_for_case(ticket_id)) ll_eqp_wo,
start_billing_at,
infotick.GETUSERFORSTATE(ticket_id, ticket_type,40,'F') "Start Billing by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,21,'F') "Closed at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,76,'F') "Start Porting at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'L') "Porting Req Acc (last) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'F') "Porting Req Acc (first) at",
start_porting_imp_f_at,
infotick.getDateValueForFieldState(ticket_id, ticket_type,'PORTING_DATE', 'S$START_PORTING_IMPL', 80, 'L') "Porting date",
porting_testing_l_at,
c_first.comments "First Comment",
c_first.added_at "First Comment At",
c_last.comments "Last Comment",
c_last.added_at "Comment At",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,39,'L') "Test rejected at",
client_name, is_client_wholesaler, final_user_name, request_type, lno, service_type, srv_capacity, county_a, 
locality_name_a, address_a,
county_z, locality_name_z, address_z, crt_stat,
getgroupassignment (68, ticket_id, ticket_type) pdm_assignment,
getgroupassignment (41, ticket_id, ticket_type) bde_assignment,
getgroupassignment (43, ticket_id, ticket_type) dse_assignment,
get_ll_eqp(eqp_a) eqp_loc_a,
get_ll_eqp(eqp_z) eqp_loc_z,
packet_id,
packet_type,
get_client_type_desc_for_case(ticket_id) client_type,
decode ((infotick.getStringValueForFieldState(ticket_id, ticket_type, 'SLA_IMPLEMENTARE', 'S$START_IMPL', 18, 'L')),1, 'Standard',2, 'Non-standard', '-') "SLA Implementare",
decode (si_f_at,null,null,decode(testing_f_at,null,null,wkdays(si_f_at,testing_f_at))) wk_dif_si_testing,
decode (start_porting_imp_f_at,null,null,decode(porting_testing_l_at,null,null,wkdays(start_porting_imp_f_at,porting_testing_l_at))) wk_dif_si_port_testing,
get_pending_ext_reason (ticket_id) reason_stop_clock,
get_pending_reason (ticket_id) reason_pending_others,
get_pending_duration (ticket_id, 44) duration_stop_clock,
get_pending_duration (ticket_id, 23) duration_pending_others,
sla.getSLAImpl_new(ticket_id) sla_impl_value,
decode(sla.getSLAImpl_new(ticket_id),null,null,
 decode(si_f_at,null,null,
 decode(testing_f_at,null,null,sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)) )) sla_deviation,
decode(sla.getSLAImpl_new(ticket_id),null,null,
 decode(si_f_at,null,null,
 decode(testing_f_at,null,null,
 decode(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at) - 
 abs(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)),0,'in SLA', 
 decode((infotick.getDateValueForFieldState(ticket_id, ticket_type, 'SRV_IMPL_DUE_TIME', 'S$START_IMPL' , 18, 'L') - trunc(testing_f_at)), 0, 'in SLA','out SLA'))))) sla_status, 
is_user_in_group(opened_by,66) is_initiator_bsr,
case_opened_by,
decode (wo_pend_stop_clock,null,null,decode(due_date,null,null,wkdays(due_date, wo_pend_stop_clock))) wk_dif_stop_clock,
infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'F') delivery_pending_at_first,
infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'L') delivery_pending_at_last,
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'F') delivery_resume_date_first,
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'L') delivery_resume_date_last,
sla_impl.type_name as sla_implementation_type
from (
select trans.first_trans_id, wk.ticket_id, wk.ticket_type, va.service_id, (select max(asrq_id_loc_a) from s$case_asrq sc where sc.case_id = wk.ticket_id) 
asrqid_loc_a_last ,
(select max(asrq_id_loc_z) from s$case_asrq sc where sc.case_id = wk.ticket_id) asrqid_loc_z_last ,
va.client_name, va.is_client_wholesaler, va.final_user_name, va.request_type,
va.lno, va.service_type, va.capacity||' '||va.capacity_um srv_capacity,
va.county_a, va.locality_name_a, va.address_a,
va.county_z, va.locality_name_z, va.address_z, va.crt_stat,
FSD.LL_EQP_A eqp_a,
FSD.LL_EQP_z eqp_z,
va.packet_id,
va.packet_type,
FSD.CLIENT_APPRV_NEEDED_LOC_A Client_Approval_Needed_a,
FSD.CLIENT_APPRV_NEEDED_LOC_Z Client_Approval_Needed_z,
FSD.ORO_APPRV_NEEDED_LOC_A Oro_Approval_Needed_a,
FSD.ORO_APPRV_NEEDED_LOC_Z Oro_Approval_Needed_z,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,18,'F') si_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,38,'F') testing_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,83,'L') porting_testing_l_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,80,'F') start_porting_imp_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type,40,'F') start_billing_at,
va.opened_by opened_by,
va.case_opened_by,
infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type, 44,'L') wo_pend_stop_clock,
infotick.getDateValueForFieldState(wk.ticket_id, wk.ticket_type,'DUE_DATE', 'S$WO_GENERATION', 17, 'L') due_date
from wk$transitions wk, v$all_cases_for_pdm_rap va, s$fs_done fsd, 
(SELECT MAX (x.trans_id) trid, x.ticket_id tid
FROM wk$transitions x
WHERE x.ticket_type = 'C'
 AND x.to_stat_id = 7
 /*AND x.is_correction IS NULL*/
 AND x.in_pending IS NULL 
 group by x.ticket_id ) trans_id_max_to_fs_done,
 (select min(trans_id) first_trans_id, ticket_id
 from wk$transitions
 where ticket_type = 'C'
 group by ticket_id) trans
where wk.ticket_type='C' and wk.to_stat_id = 1
--and trunc(updated_at) between to_date(:P19_FROM_DATE,'dd-mm-yyyy') and to_date(:P19_TO_DATE,'dd-mm-yyyy')
 and wk.ticket_id = va.case_id
 and trans_id_max_to_fs_done.tid (+)= wk.ticket_id
 AND fsd.trans_id (+) = trans_id_max_to_fs_done.trid
 and trans.ticket_id = wk.ticket_id 
) aux,
 -- comments
 (select case_id, min(id) id
 from wk$comments c
 group by case_id) c_min,
 wk$comments c_first,
 (select case_id, max(id) id
 from wk$comments c
 group by case_id) c_max,
 wk$comments c_last,
-- Lookup table for SLA implementation
lk$implementation_type sla_impl
where ticket_id = c_min.case_id(+)
 and c_min.id = c_first.id(+) 
 and ticket_id = c_max.case_id(+)
 and c_max.id = c_last.id(+)
 -- Join Condition for SLA implementation
 and sla_impl.id (+) = infotick.getStringValueForFieldStateCor(ticket_id, 'C', 'SLA_IMPLEMENTARE','S$START_IMPL',18, 'L');

Some of the function definitions that were used in this query:

create or replace package infotick as
 function getDateForState(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return date;
 function getDateForStatecorr(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return date;
 function getUserForState(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return varchar2;
 function getUserForStateCorr(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return varchar2;
 function getDateValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return date;
 function getDateCorrValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return date;
 function getDateValueForFieldTrans(p_transid number, p_fieldname varchar2) return date;
 function getStringValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return varchar2;
 function getStringValueForFieldStateCor(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return varchar2;
 function getStringValueForFieldTrans(p_transid number, p_fieldname varchar2) return varchar2;
 function getPortingDateforState(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return date;
 function getNotPortingDateforState(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return date;
 function getWOPortingTransID(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return pls_integer;
 function getWONotPortingTransID(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return pls_integer;
end;

I'll provide any other information if needed.

---

EDIT: Just to be sure this won't be flagged for not being the maintainer of the code, I'll have to maintain it and add new functionalities to it as new requests come. I've got some experience with SQL but this simply looks like a spaghetti query to me. I'm sure it can be refactored.

• Cajuu': @BCdotWEB that huge SELECT uses SQL Server syntax and as far as I know, the functions are PL/SQL.
• paparazzo: Need to look at the query plan and see what is taking time. I suspect you need to materialize some of the sub queries.
• Cajuu': @Paparazzi sure, take your time to analyse the code. There's no hurry.
• paparazzo: Take my time? Need to look at the query plan and see what is taking time.
• dnoeth: This is not SQL Server (of course you should know which DBMS you're using), but Oracle code using ancient Outer Join Syntax & unknown functions. It's probably easier to start from scratch.

Answer 1

The first step to cleaning up this beast is to clean up the format of the code that you currently have into something that's readable. I've taken a stab at making it a little easier to read:

select 
 first_trans_id, 
 ticket_id, 
 ticket_type, 
 service_id,
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,1,'F') "New Case at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,2,'F') "FSR(first) at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) at",
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) by",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'L') "FS Validation(last) at",
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,16,'L') "FS validation(last) by",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'F') "Contract Definition(first) at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,30,'L') "Contract Signature(last) at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) at",
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) by",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) at",
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) by",
 infotick.GETDATEFORSTATECORR(ticket_id, ticket_type,18,'L') "SI(last) at",
 infotick.getDateValueForFieldState(ticket_id, ticket_type,'IMPL_NEED_BY_DATE', 'S$START_IMPL', 18, 'L') "Implementation need by date",
 infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'SRV_IMPL_DUE_TIME', 'S$START_IMPL', 18, 'L') "Srv Impl Due Date",
 infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'EMERGENCY_DATE', 'S$START_IMPL', 18, 'L') "Emergency date",
 infotick.getdatecorrvalueforfieldstate(ticket_id, ticket_type, 'PROJECT_DATE', 'S$START_IMPL', 18, 'L') "Project date",
 get_ll_eqp(infotick.getStringValueForFieldState(ticket_id, ticket_type, 'LL_EQUIP_ID', 'S$HLD', 167, 'L')) hld_ll,
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,17,'F') "WO Generated at",
 infotick.getUserForState(ticket_id, ticket_type, 17, 'F') wo_gen_first_by,
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed at",
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed by",
 wo_pend_stop_clock "WO pending Stop Clock",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'F') "Testing(first) at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'L') "Testing(last) at",
 get_ll_eqp(get_ll_for_case(ticket_id)) ll_eqp_wo,
 start_billing_at,
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,40,'F') "Start Billing by",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done at",
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done by",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,21,'F') "Closed at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled at",
 infotick.GETUSERFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled by",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,76,'F') "Start Porting at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'L') "Porting Req Acc (last) at",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'F') "Porting Req Acc (first) at",
 start_porting_imp_f_at,
 infotick.getDateValueForFieldState(ticket_id, ticket_type,'PORTING_DATE', 'S$START_PORTING_IMPL', 80, 'L') "Porting date",
 porting_testing_l_at,
 c_first.comments "First Comment",
 c_first.added_at "First Comment At",
 c_last.comments "Last Comment",
 c_last.added_at "Comment At",
 infotick.GETDATEFORSTATE(ticket_id, ticket_type,39,'L') "Test rejected at",
 client_name, is_client_wholesaler, final_user_name, request_type, lno, service_type, srv_capacity, county_a, 
 locality_name_a, address_a,
 county_z, locality_name_z, address_z, crt_stat,
 getgroupassignment (68, ticket_id, ticket_type) pdm_assignment,
 getgroupassignment (41, ticket_id, ticket_type) bde_assignment,
 getgroupassignment (43, ticket_id, ticket_type) dse_assignment,
 get_ll_eqp(eqp_a) eqp_loc_a,
 get_ll_eqp(eqp_z) eqp_loc_z,
 packet_id,
 packet_type,
 get_client_type_desc_for_case(ticket_id) client_type,
 decode ((infotick.getStringValueForFieldState(ticket_id, ticket_type, 'SLA_IMPLEMENTARE', 'S$START_IMPL', 18, 'L')),1, 'Standard',2, 'Non-standard', '-') "SLA Implementare",
 decode (si_f_at,null,null,decode(testing_f_at,null,null,wkdays(si_f_at,testing_f_at))) wk_dif_si_testing,
 decode (start_porting_imp_f_at,null,null,decode(porting_testing_l_at,null,null,wkdays(start_porting_imp_f_at,porting_testing_l_at))) wk_dif_si_port_testing,
 get_pending_ext_reason (ticket_id) reason_stop_clock,
 get_pending_reason (ticket_id) reason_pending_others,
 get_pending_duration (ticket_id, 44) duration_stop_clock,
 get_pending_duration (ticket_id, 23) duration_pending_others,
 sla.getSLAImpl_new(ticket_id) sla_impl_value,
 decode(sla.getSLAImpl_new(ticket_id),null,null,
 decode(si_f_at,null,null,
 decode(testing_f_at,null,null,sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)) )) sla_deviation,
 decode(sla.getSLAImpl_new(ticket_id),null,null,
 decode(si_f_at,null,null,
 decode(testing_f_at,null,null,
 decode(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at) - 
 abs(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)),0,'in SLA', 
 decode((infotick.getDateValueForFieldState(ticket_id, ticket_type, 'SRV_IMPL_DUE_TIME', 'S$START_IMPL' , 18, 'L') - trunc(testing_f_at)), 0, 'in SLA','out SLA'))))) sla_status, 
 is_user_in_group(opened_by,66) is_initiator_bsr,
 case_opened_by,
 decode (wo_pend_stop_clock,null,null,decode(due_date,null,null,wkdays(due_date, wo_pend_stop_clock))) wk_dif_stop_clock,
 infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'F') delivery_pending_at_first,
 infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'L') delivery_pending_at_last,
 infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'F') delivery_resume_date_first,
 infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'L') delivery_resume_date_last,
 sla_impl.type_name as sla_implementation_type
from 
 (
 select 
 trans.first_trans_id, 
 wk.ticket_id, 
 wk.ticket_type, 
 va.service_id, 
 (
 select 
 max(asrq_id_loc_a) 
 from s$case_asrq sc 
 where sc.case_id = wk.ticket_id
 ) 
 asrqid_loc_a_last ,
 (
 select 
 max(asrq_id_loc_z) 
 from s$case_asrq sc 
 where sc.case_id = wk.ticket_id
 ) asrqid_loc_z_last ,
 va.client_name, 
 va.is_client_wholesaler, 
 va.final_user_name, 
 va.request_type,
 va.lno, 
 va.service_type, 
 va.capacity||' '||va.capacity_um srv_capacity,
 va.county_a, 
 va.locality_name_a, 
 va.address_a,
 va.county_z, 
 va.locality_name_z, 
 va.address_z, 
 va.crt_stat,
 FSD.LL_EQP_A eqp_a,
 FSD.LL_EQP_z eqp_z,
 va.packet_id,
 va.packet_type,
 FSD.CLIENT_APPRV_NEEDED_LOC_A Client_Approval_Needed_a,
 FSD.CLIENT_APPRV_NEEDED_LOC_Z Client_Approval_Needed_z,
 FSD.ORO_APPRV_NEEDED_LOC_A Oro_Approval_Needed_a,
 FSD.ORO_APPRV_NEEDED_LOC_Z Oro_Approval_Needed_z,
 infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,18,'F') si_f_at,
 infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,38,'F') testing_f_at,
 infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,83,'L') porting_testing_l_at,
 infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,80,'F') start_porting_imp_f_at,
 infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type,40,'F') start_billing_at,
 va.opened_by opened_by,
 va.case_opened_by,
 infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type, 44,'L') wo_pend_stop_clock,
 infotick.getDateValueForFieldState(wk.ticket_id, wk.ticket_type,'DUE_DATE', 'S$WO_GENERATION', 17, 'L') due_date
 from wk$transitions wk, 
 v$all_cases_for_pdm_rap va, 
 s$fs_done fsd, 
 (
 SELECT 
 MAX (x.trans_id) trid, 
 x.ticket_id tid
 FROM wk$transitions x
 WHERE x.ticket_type = 'C'
 AND x.to_stat_id = 7
 AND x.in_pending IS NULL 
 group by x.ticket_id 
 ) trans_id_max_to_fs_done,
 (
 select 
 min(trans_id) first_trans_id, 
 ticket_id
 from wk$transitions
 where ticket_type = 'C'
 group by ticket_id
 ) trans
 where wk.ticket_type='C' and wk.to_stat_id = 1
 and wk.ticket_id = va.case_id
 and trans_id_max_to_fs_done.tid (+)= wk.ticket_id
 AND fsd.trans_id (+) = trans_id_max_to_fs_done.trid
 and trans.ticket_id = wk.ticket_id 
 ) aux,
 (
 select 
 case_id, 
 min(id) id
 from wk$comments c
 group by case_id
 ) c_min,
 wk$comments c_first,
 (
 select 
 case_id, 
 max(id) id
 from wk$comments c
 group by case_id
 ) c_max,
 wk$comments c_last,
 lk$implementation_type sla_impl
where ticket_id = c_min.case_id(+)
 and c_min.id = c_first.id(+) 
 and ticket_id = c_max.case_id(+)
 and c_max.id = c_last.id(+)
 and sla_impl.id (+) = infotick.getStringValueForFieldStateCor(ticket_id, 'C', 'SLA_IMPLEMENTARE','S$START_IMPL',18, 'L');

If I were maintaining this, the first place I would start would be to clean up the from and where clauses of the main part of the query. Turn all of these into proper inner joins and left outer joins (you will have to work out which ones are appropriate where, start with left outer joins and turn them into inner joins when you want the join to be more strict).

After that, I would dig into the functions to figure out what they're doing, and whether they could just be turned into joins in the from clause.

Another route might be to just start from scratch. Gather all of the business requirements for the report and work from there, rather than supporting this hot mess.