How to tune the complex Oracle stored procedure?

I have an Oracle stored procedure that takes a bunch of parameters and heavily does the left outer join like this:

create or replace PROCEDURE "SOME_PROCEDURE" (
	cRECORDSET OUT SYS_REFCURSOR,
	iPHY_YEAR IN SOME_PROCEDURE_TYPE.PHYSICAL_YEAR % TYPE,
	iPHYSICAL_DOC_TYPE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE % TYPE,
	iPHYSICAL_DOC_TYPE_NONSAP IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE_NONSAP % TYPE,
	sREFERENCE_NO IN SOME_PROCEDURE_TYPE.REFERENCE_NO % TYPE,
	sPHYSICAL_NO IN SOME_PROCEDURE_TYPE.PHYSICAL_NO % TYPE,
	iLOCATION_ID IN SOME_PROCEDURE_TYPE.LOCATION_ID % TYPE,
	iCONTAINER_ID IN SOME_PROCEDURE_TYPE.CONTAINER_ID % TYPE,
	sCONTAINER_NUMBER IN SOME_PROCEDURE_TYPE.CONTAINER_NUMBER % TYPE,
	dREGISTERED_DATE_FROM IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_FROM % TYPE,
	dREGISTERED_DATE_TO IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_TO % TYPE,
	iPHYSICAL_STATUS_ID IN SOME_PROCEDURE_TYPE.PHYSICAL_STATUS_ID % TYPE,
	sDRAFT_FLAG IN SOME_PROCEDURE_TYPE.DRAFT_FLAG % TYPE,
	sCANCEL_FLAG IN SOME_PROCEDURE_TYPE.CANCEL_FLAG % TYPE,
	sPHYSICAL_REMARK IN SOME_PROCEDURE_TYPE.PHYSICAL_REMARK % TYPE,
	sPHYSICAL_DOC_TITLE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TITLE % TYPE,
	sFI_DOC_NO_FROM IN SOME_PROCEDURE_TYPE.FI_DOC_NO_FROM % TYPE,
	sFI_DOC_NO_TO IN SOME_PROCEDURE_TYPE.FI_DOC_NO_TO % TYPE,
	iCOMPANY_ID IN SOME_PROCEDURE_TYPE.COMPANY_ID % TYPE,
	iFI_DOC_YEAR IN SOME_PROCEDURE_TYPE.FI_DOC_YEAR % TYPE,
	sFI_DOC_TYPE IN SOME_PROCEDURE_TYPE.FI_DOC_TYPE % TYPE,
	sIS_ASSET IN SOME_PROCEDURE_TYPE.IS_ASSET % TYPE,
	sWORKFLOW_STATUS_CODE IN SOME_PROCEDURE_TYPE.WORKFLOW_STATUS_CODE % TYPE,
	sPO_NO IN SOME_PROCEDURE_TYPE.PO_NO % TYPE,
	sVENDOR_NAME IN SOME_PROCEDURE_TYPE.VENDOR_NAME % TYPE,
	sCUSTOMER_NAME IN SOME_PROCEDURE_TYPE.CUSTOMER_NAME % TYPE,
	dPOSTING_DATE_FROM IN SOME_PROCEDURE_TYPE.POSTING_DATE_FROM % TYPE,
	dPOSTING_DATE_TO IN SOME_PROCEDURE_TYPE.POSTING_DATE_TO % TYPE,
	sCLEARING_DOC_NO IN SOME_PROCEDURE_TYPE.CLEARING_DOC_NO % TYPE,
	iCLEARING_DOC_YEAR IN SOME_PROCEDURE_TYPE.CLEARING_DOC_YEAR % TYPE,
	dCLEARING_DOC_DATE IN SOME_PROCEDURE_TYPE.CLEARING_DOC_DATE % TYPE,
	sPAYMENT_BLOCK IN SOME_PROCEDURE_TYPE.PAYMENT_BLOCK % TYPE,
	iAMOUNT_INC_VAT IN SOME_PROCEDURE_TYPE.AMOUNT_INC_VAT % TYPE,
	sFI_DOC_REFNO IN SOME_PROCEDURE_TYPE.FI_DOC_REFNO % TYPE,
	sSEND_TO IN SOME_PROCEDURE_TYPE.SEND_TO % TYPE,
	sSAP_REVERSE_FLAG IN SOME_PROCEDURE_TYPE.SAP_REVERSE_FLAG % TYPE,
	sCLEARING_FLAG IN SOME_PROCEDURE_TYPE.CLEARING_FLAG % TYPE,
	iPAGE_SIZE IN SOME_PROCEDURE_TYPE.PAGE_SIZE % TYPE,
	iPAGE_INDEX IN SOME_PROCEDURE_TYPE.PAGE_INDEX % TYPE,
	iUSERID IN USR_DETAIL.USER_ID % TYPE
) IS
LAST_INDEX NUMBER;
USR_PERM_TYPE GET_USR_PERM_TYPE;
FIRST_INDEX NUMBER;
BEGIN
	LAST_INDEX := iPAGE_SIZE * iPAGE_INDEX;
	FIRST_INDEX := LAST_INDEX - iPAGE_SIZE + 1;
    SELECT FN_GET_USR_PERM(iUSERID) INTO USR_PERM_TYPE FROM DUAL;
	OPEN cRECORDSET FOR 
    
    WITH CTE_PHD_DETAIL AS (
    SELECT
        *
    FROM
        PHD_DETAIL
    WHERE
        USR_PERM_TYPE.VIS_MODE = 'ALL'
        OR (
            USR_PERM_TYPE.VIS_MODE = 'UNT'
            AND (UNIT_ID = USR_PERM_TYPE.USUB_ID)
        )
        OR (
            USR_PERM_TYPE.VIS_MODE = 'SUB'
            AND SUB_UNIT_ID = USR_PERM_TYPE.USUB_ID
        )
)
    
    SELECT
	PHD_ITEMS.RECORD_NUMBER,
	PHD.PHYSICAL_ID,
	PHD.PHYSICAL_DOC_TYPE,
	PHD.PHYSICAL_NO,
	PHD.REGISTERED_DATE,
	PHD.CANCEL_FLAG,
	PHD.DRAFT_FLAG,
	CASE
			WHEN PHD.REQUESTED_NO IS NOT NULL THEN
			PHD.REQUESTED_NO ELSE PHD.TEXT_FILE_NAME
		END REFERENCE_NO,
	PHDS.PHYSICAL_STATUS_ID,
	PHDS.PHYSICAL_STATUS_NAME,
	CNT.CONTAINER_ID,
CASE
	WHEN PHD.PHYSICAL_STATUS_ID = 2 THEN
	NULL ELSE CNT.CONTAINER_NUMBER
	END CONTAINER_NUMBER,
	CNT.CONTAINER_TYPE,
	CNTU.IS_DOC_HOUSE CONTAINER_IS_DOC_HOUSE,
	CNTU.UNIT_ABBR CONTAINER_UNIT_ABBR,
	LOC.LOCATION_ID,
CASE
	WHEN PHD.PHYSICAL_STATUS_ID = 2
	OR PHD.PHYSICAL_STATUS_ID = 12
	OR PHD.PHYSICAL_STATUS_ID = 14 THEN
	(
	SELECT
		SEND_TO
	FROM
		( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC )
	WHERE
		ROWNUM = 1
		AND PHYSICAL_ID = PHD_ITEMS.PHYSICAL_ID
	) --DECODE( PHDLG.SENT_TO,NULL , '',PHDLG.SENT_TO )
	WHEN CNT.CONTAINER_TYPE = 1 THEN
CASE
		WHEN CNT.CONTAINER_STATUS_ID = 2 THEN
	CASE
			WHEN CNT.CURRENT_UNIT_ID IS NOT NULL THEN
			CNTU.UNIT_FULL_NAME ELSE CCOM.COMPANY_CODE || ' - ' || CCOM.COMPANY_NAME
		END ELSE LOC.LOCATION_NAME
	END ELSE LOC.LOCATION_NAME -- LOC.LOCATION_NAME
	END LOCATION_NAME,
	FIDS.WORKFLOW_STATUS_NAME,
	FID.FI_DOC_ID,
	FID.FI_DOC_NO,
	FID.FI_DOC_YEAR,
CASE
	WHEN COM_FID.COMPANY_ID IS NOT NULL THEN
	COM_FID.COMPANY_CODE || ' - ' || COM_FID.COMPANY_NAME ELSE
CASE
		WHEN COM_PHD.COMPANY_ID IS NOT NULL THEN
		COM_PHD.COMPANY_CODE || ' - ' || COM_PHD.COMPANY_NAME ELSE ''
	END
	END AS COMPANY_FULL_NAME,
	FID.FI_DOC_REFNO,
CASE
		WHEN FID.FI_DOC_ID IS NULL THEN
		PTEMP.AMOUNT_INC_VAT ELSE FID.AMOUNT_INC_VAT
	END AMOUNT_INC_VAT,
CASE
	WHEN FID.FI_DOC_ID IS NULL THEN
	PTEMP.AMOUNT_CURRENCY ELSE FID.AMOUNT_CURRENCY
	END AMOUNT_CURRENCY,
	FID.IS_ASSET,
CASE
	WHEN PHD_ITEMS.FI_DOC_ID IS NULL THEN
CASE
		WHEN PTEMP.CUSTOMER_CODE IS NOT NULL THEN
		PTCUS.CUSTOMER_FULL_NAME ELSE PTVEN.VENDOR_FULL_NAME
END ELSE
CASE
		WHEN FID.FIRST_ITEMS = 'CUSTOMER' THEN
		FID.CUSTOMER_NAME ELSE FID.VENDOR_NAME
	END
	END AS CUSTOMER_VENDOR_NAME,
	PHD.PHYSICAL_REMARK
FROM
	(
	SELECT
		ROW_NUMBER ( ) over ( ORDER BY PHD.PHYSICAL_NO DESC, FID_ID.FI_DOC_NO, PHDTEMP.PHD_TEMP_DATA_ID DESC ) RECORD_NUMBER,
		PHD.PHYSICAL_ID,
		PHD.PHYSICAL_NO,
		FID_ID.FI_DOC_ID,
		FID_ID.FI_DOC_NO,
		PHDTEMP.PHD_TEMP_DATA_ID
	FROM
		(
		SELECT DISTINCT
			PHD.PHYSICAL_ID,
			PHD.PHYSICAL_NO,
			FID_ID.FI_DOC_ID,
			PHDTEMP.PHD_TEMP_DATA_ID
		FROM
			CTE_PHD_DETAIL PHD
			LEFT JOIN (
			SELECT
				t1.FI_DOC_ID,
				t1.PHYSICAL_ID,
				t2.fi_Doc_no,
				t2.fi_doc_year
			FROM
				PHD_FID_RELATE_DOC t1
				INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID UNION ALL
			SELECT
				t1.FI_DOC_ID,
				t1.PHYSICAL_ID,
				t2.fi_Doc_no,
				t2.fi_doc_year
			FROM
				FID_SAP_PHYSICAL_HISTORY t1
				INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID
			) FID_ID ON FID_ID.PHYSICAL_ID = PHD.PHYSICAL_ID
			LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHYSICAL_ID = PHDTEMP.PHYSICAL_ID
			AND FID_ID.FI_DOC_ID
			IS NULL LEFT JOIN PHD_STATUS_LOGS PHDSL ON PHD.PHYSICAL_ID = PHDSL.PHYSICAL_ID
			LEFT JOIN PHD_TEMP_DOCTITLE PHDT ON PHD.PHYSICAL_ID = PHDT.PHYSICAL_ID
			LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
			LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
			LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
			LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
			LEFT JOIN FID_SAP_DETAIL FID ON FID_ID.FI_DOC_ID = FID.FI_DOC_ID
			LEFT JOIN COM_DETAIL COM ON FID.COMPANY_CODE = COM.COMPANY_CODE
			LEFT JOIN FID_SAP_CUSTOMER FCUS ON FID.FI_DOC_ID = FCUS.FI_DOC_ID
			LEFT JOIN FID_SAP_VENDOR FVEN ON FID.FI_DOC_ID = FVEN.FI_DOC_ID
			LEFT JOIN FID_SAP_CLEARING_DOC CLR ON FID.FI_DOC_ID = CLR.FI_DOC_ID
			LEFT JOIN FID_SAP_PO PO ON FID.FI_DOC_ID = PO.FI_DOC_ID
			LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
			LEFT JOIN MAS_VENDOR PTVEN ON PHDTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
			AND COM_PHD.SERVER_ID = COM_PHD.SERVER_ID
			LEFT JOIN MAS_CUSTOMER PTCUS ON PHDTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
			AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
		WHERE
			PHD.PHYSICAL_NO IS NOT NULL
			AND (
				(
					(
						( PHD.PHYSICAL_DOC_TYPE = 1 AND iPHYSICAL_DOC_TYPE = 1 )
						OR ( ( PHD.PHYSICAL_DOC_TYPE = 2 OR PHD.PHYSICAL_DOC_TYPE = 4 ) AND iPHYSICAL_DOC_TYPE = 2 )
					)
					AND iPHYSICAL_DOC_TYPE_NONSAP IS NULL
				)
				OR ( PHD.PHYSICAL_DOC_TYPE = iPHYSICAL_DOC_TYPE_NONSAP AND iPHYSICAL_DOC_TYPE_NONSAP IS NOT NULL )
			)
			AND (
				sREFERENCE_NO IS NULL
				OR (
					UPPER( PHD.REQUESTED_NO ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
					OR UPPER( PHD.TEXT_FILE_NAME ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
				)
			)
			AND ( sPHYSICAL_NO IS NULL OR ( PHD.PHYSICAL_NO = sPHYSICAL_NO ) )
			AND ( iLOCATION_ID IS NULL OR ( LOC.LOCATION_ID = iLOCATION_ID ) )
			AND ( iPHYSICAL_STATUS_ID IS NULL OR ( PHD.PHYSICAL_STATUS_ID = iPHYSICAL_STATUS_ID ) )
			AND ( iCONTAINER_ID IS NULL OR ( CNT.CONTAINER_ID = iCONTAINER_ID AND CNT.CONTAINER_TYPE = 2 ) )
			AND (
				sCONTAINER_NUMBER IS NULL
				OR ( UPPER( CNT.CONTAINER_NUMBER ) LIKE '%' || UPPER( sCONTAINER_NUMBER ) || '%' AND CNT.CONTAINER_TYPE = 1 )
			)
			AND ( TRUNC( dREGISTERED_DATE_FROM ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) >= TRUNC( dREGISTERED_DATE_FROM ) ) )
			AND ( TRUNC( dREGISTERED_DATE_TO ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) = sFI_DOC_NO_FROM ) )
			AND ( sFI_DOC_NO_TO IS NULL OR ( FID.FI_DOC_NO = TRUNC( dPOSTING_DATE_FROM ) OR TRUNC( PHDTEMP.CREATE_DATE ) = TRUNC( dPOSTING_DATE_FROM ) )
			)
			AND ( sCLEARING_DOC_NO IS NULL OR ( UPPER( CLR.CLEARING_DOC_NO ) LIKE '%' || UPPER( sCLEARING_DOC_NO ) || '%' ) )
			AND ( iCLEARING_DOC_YEAR IS NULL OR ( CLR.CLEARING_DOC_YEAR = iCLEARING_DOC_YEAR ) )
			AND ( dCLEARING_DOC_DATE IS NULL OR ( CLR.CLEARING_DATE = dCLEARING_DOC_DATE ) ) --AND ( sPAYMENT_BLOCK      IS NULL
-- OR ( UPPER(FID.PAYMENT_BLOCK) LIKE '%'
--   || UPPER(sPAYMENT_BLOCK)
--  || '%' ) )
			AND ( sFI_DOC_REFNO IS NULL OR ( UPPER( FID.FI_DOC_REFNO ) LIKE '%' || UPPER( sFI_DOC_REFNO ) || '%' ) )
			AND ( ( sSAP_REVERSE_FLAG IS NULL AND ( FID.SAP_REVERSE_FLAG IS NULL OR FID.FI_DOC_ID IS NULL ) ) OR ( sSAP_REVERSE_FLAG IS NOT NULL ) )
			AND ( iAMOUNT_INC_VAT IS NULL OR ( FID.AMOUNT_INC_VAT = iAMOUNT_INC_VAT OR PHDTEMP.AMOUNT_INC_VAT = iAMOUNT_INC_VAT ) )
			AND (
				sCUSTOMER_NAME IS NULL
				OR UPPER( PTCUS.CUSTOMER_FULL_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
				OR UPPER( FCUS.CUSTOMER_CODE || ' ' || FCUS.CUSTOMER_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
			)
			AND (
				sVENDOR_NAME IS NULL
				OR UPPER( PTVEN.VENDOR_FULL_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
				OR UPPER( FVEN.VENDOR_CODE || ' ' || FVEN.VENDOR_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
			) --AND FID_ID.FI_DOC_ID IS NULL AND PHDTEMP.PHD_TEMP_DATA_ID IS NOT NULL
		) PHD
		LEFT JOIN FID_SAP_DETAIL FID_ID ON FID_ID.FI_DOC_ID = PHD.FI_DOC_ID
		LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHD_TEMP_DATA_ID = PHDTEMP.PHD_TEMP_DATA_ID
		AND PHD.FI_DOC_ID IS NULL
	) PHD_ITEMS
	INNER JOIN CTE_PHD_DETAIL PHD ON PHD_ITEMS.PHYSICAL_ID = PHD.PHYSICAL_ID
	LEFT JOIN PHD_STATUS PHDS ON PHD.PHYSICAL_STATUS_ID = PHDS.PHYSICAL_STATUS_ID
	LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
	LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
	LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
	LEFT JOIN COM_DETAIL CCOM ON CNT.CURRENT_COMPANY_ID = CCOM.COMPANY_ID
	LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
	LEFT JOIN FID_SAP_DETAIL FID ON PHD_ITEMS.FI_DOC_ID = FID.FI_DOC_ID
	LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
	LEFT JOIN COM_DETAIL COM_FID ON COM_FID.COMPANY_CODE = FID.COMPANY_CODE
	LEFT JOIN PHD_TEMP_DATA PTEMP ON PHD_ITEMS.PHD_TEMP_DATA_ID = PTEMP.PHD_TEMP_DATA_ID
	LEFT JOIN MAS_VENDOR PTVEN ON PTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
	AND PHD_ITEMS.FI_DOC_ID IS NULL
	AND PTVEN.SERVER_ID = COM_PHD.SERVER_ID
	LEFT JOIN MAS_CUSTOMER PTCUS ON PTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
	AND PHD_ITEMS.FI_DOC_ID IS NULL
	AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
WHERE
	PHD_ITEMS.RECORD_NUMBER BETWEEN FIRST_INDEX
	AND LAST_INDEX
ORDER BY
	RECORD_NUMBER;
END;

This query takes around 1:30 minutes before issuing the ~4000 records query result. It is hard to extract the execution plan for this long query. What suggestion or strategy or should I do something to get the plan? In my opinion, I currently have 3 options: ## Dynamic Query The first option is escalating the procedure to be ORM statement with some if statement to compose the proper statement with inner join instead of left outer join (if the parameter is null, no join). I think this option is smart when I think about at a first glance. However, it might took me so long time to accomplish the task. ## Materialized View One of option that I think about is pulling the left join clauses to be rewritten as Materialized View. Unfortunately, this store procedure is involved in application so the Materialized View will be updated frequently when combine with ON COMMIT or ON STATEMENT. Is this option considered dangerous for deadlock or bad performance? ## Remove some search criteria (stored procedure parameters) Suppose that I can remove some criteria, it means I can remove some left outer join clauses. However, this option is not possible after I discuss with teammate. How do I suppose to do to tackle with performance problem or what other strategies or options do I have? Any advices are all welcomed.