Complex report generator using huge SQL Query

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_PEND

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)

StackExchange Code Review Q#143971, answer score: 3