asked on

Need help on how to call a function within a view with four parameters

What is the correct syntax to call a function with four parameters within a view?

View:

select wo_sum2."WO_NUMBER",wo_sum2."WO_TYPE",wo_sum2."RC_NUMBER",wo_sum2."NOTES",wo_sum2."PEL_TAIL_NUMBER",wo_sum2."MANUAL_TAIL_NUMBER",wo_sum2."AMDT_MOD_IN", wo_sum2."AMDT_MOD_OUT",wo_sum2."RC_EXCUSABLE_DELAY",wo_sum2."CV_UDF_017",wo_sum2."PNM_AUTO_KEY",wo_sum2."WO_DISP",wo_sum2."TEARDOWN",wo_sum2."ACCT_COMPANY",wo_sum2."DEPT_NAME",wo_sum2."SHOP_TYPE",wo_sum2."ENTRY_DATE",wo_sum2."PN",wo_sum2."DESCRIPTION",wo_sum2."ACT_AUTO_KEY",wo_sum2."SERIAL_NUMBER",wo_sum2."MFG_CODE",wo_sum2."LAST_STATUS_CHG",wo_sum2."STATUS",wo_sum2."SEVERITY",wo_sum2."RELEASED",wo_sum2."RELEASE_DATE",wo_sum2."CUSTOMER_NAME",wo_sum2."CUSTOMER_CODE",wo_sum2."CUSTOMER_PO",wo_sum2."PBH_CUSTOMER",wo_sum2."PART_NUMBER_OUT",wo_sum2."SERIAL_NUMBER_OUT",wo_sum2."ECC_NUMBER",wo_sum2."ITAR_NUMBER",wo_sum2."RECEIVED_DATE",wo_sum2."TAIL_NUMBER",wo_sum2."DUE_DATE",wo_sum2."WORK_REQUESTED",wo_sum2."WORK_PERFORMED",wo_sum2."BLOCK_12_8130",wo_sum2."CONTRACT_TAT",wo_sum2."MANUAL_ECD",wo_sum2."CLOSE_DATE",wo_sum2."DATE_MOVED_TO_QUOTING",wo_sum2."CAA_DATE",wo_sum2."CLOSE_DATE_ORIG",wo_sum2."INVC_DATE",wo_sum2."SHIP_DATE",wo_sum2."SHIPPING_ORDER",wo_sum2."SMH_AUTO_KEY",wo_sum2."TRACKING_NUMBER",wo_sum2."SHIP_HOLD",wo_sum2."RO_NUMBER",wo_sum2."CAPABILITY_CODE",wo_sum2."CONTRACT_ITEM",wo_sum2."CONTRACT_NUMBER",wo_sum2."CONTRACT_TYPE",wo_sum2."SO_NUMBER",wo_sum2."EXCHANGE_DATE",wo_sum2."EXCHANGE_TYPE_CODE",wo_sum2."FIRST_QUOTE_SENT",wo_sum2."LAST_QUOTE_SENT",wo_sum2."LAST_QUOTE_APPROVED",wo_sum2."QUOTE_SENT_OVERRIDE",wo_sum2."QUOTE_APPROVED_OVERRIDE",wo_sum2."LAST_QUOTE",wo_sum2."LAST_QUOTE_STATUS",wo_sum2."QUOTED_AMOUNT",wo_sum2."LAST_RO",wo_sum2."LAST_RO_VENDOR_INFO",wo_sum2."WARRANTY_REQUESTED",wo_sum2."WARRANTY_GRANTED",wo_sum2."WARRANTY_COMMENTS",wo_sum2."WARRANTY_CODE",wo_sum2."SCHEDULED_REMOVAL",wo_sum2."NFF_FLAG",wo_sum2."HRC_FLAG",wo_sum2."LRC_FLAG",wo_sum2."HRC_CODE",wo_sum2."WAS_PARTS_SHORTAGE",wo_sum2."TIME_IN_PARTS_SHORTAGE",wo_sum2."INTERNAL_REJECTION",wo_sum2."CSM",wo_sum2."WOO_AUTO_KEY",wo_sum2."SYSCM_AUTO_KEY",wo_sum2."PRIORITY",wo_sum2."TAT_START_DATE",wo_sum2."TAT_STOP_DATE",wo_sum2."TAT_STOP_DATE_CUST",wo_sum2."TAT_STOP_DATE_CUST_V2",wo_sum2."WON_AUTO_KEY",wo_sum2."SYSUR_MANAGER",wo_sum2."CMP_AUTO_KEY", wo_sum2."OPM_AUTO_KEY",wo_sum2."INSPECTOR", wo_sum2."TAT_STOP_DATE_V2",wo_sum2."OPEN_FLAG"
,round(nvl(tat_stop_date,sysdate)-tat_start_date) gross_tat
,ba_wo_excusable_delay(woo_auto_key,null) wo_excusable_delay
,ba_sh_excusable_delay(smh_auto_key,null) shipping_excusable_delay
,ba_sh_excusable_delay_v2(smh_auto_key,null) shipping_excusable_delay_v2
,ba_wq_excusable_delay(woo_auto_key,null) wq_excusable_delay
,ba_wo_tat_correction(woo_auto_key) tat_correction
[b],RC_EXCUSABLE_DELAY_V2 rc_excusable_delay_v2[/b]
,round(nvl(tat_stop_date,sysdate)-tat_start_date)-nvl(ba_wo_excusable_delay(woo_auto_key,null),0)-nvl(ba_sh_excusable_delay(smh_auto_key,null),0)-nvl(wo_sum2.RC_EXCUSABLE_DELAY,0)-nvl(ba_wo_tat_correction(woo_auto_key),0) net_tat
,round(nvl(tat_stop_date_v2,sysdate)-tat_start_date)-nvl(ba_wq_excusable_delay(woo_auto_key,null),0)-nvl(ba_sh_excusable_delay_v2(smh_auto_key,null),0)-[b]nvl(RC_EXCUSABLE_DELAY_V2)[/b]-nvl(ba_wo_tat_correction(woo_auto_key),0) net_tat_V2
from (
select wo_sum.*
,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 trunc(ship_date) end end end end) tat_stop_date
,trunc(case when work_performed is null or work_performed='SCRAP' then close_date_orig else case when wo_type='Internal' then close_date_orig else trunc(ship_date) end end) tat_stop_date_v2
,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
,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 wo_type='Internal' then close_date_orig else trunc(ship_date) end end) end TAT_STOP_DATE_CUST_V2
from
(select woo.si_number wo_number
, woo.wo_type
, woo.wo_disp
, woo.pnm_auto_key
, decode(woo.tear_down,'T','Y','N') teardown
, syscm.company_name acct_company
, dpt.dept_name
, 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
, trunc(woo.entry_date) entry_date
, pnm.pn
, rch.rc_number
, BA_VIEW_QUARANTINE_DAYS.days_between RC_EXCUSABLE_DELAY
, pnm.description
, pnm.ecc_number
, PNM.ITAR_NUMBER
, woo.act_auto_key
, woo.wo_udf_001 as PEL_TAIL_NUMBER
, woo.wo_udf_001 as MANUAL_TAIL_NUMBER
, woo.won_auto_key
, cmp.cv_udf_017
, WOO.NOTES
, woo.cmp_auto_key
, woo.sysur_manager
, woo.wo_udf_003 AMDT_MOD_IN
, woo.wo_udf_004 AMDT_MOD_OUT
,nvl((select min(serial_number) from stock where rcd_auto_key=(select min(rcd_auto_key) from rc_detail where woo_auto_key=woo.woo_auto_key and rec_type in ('SOE','WOC'))),
nvl((select max(serial_number) from stock,stock_reservations where stock.stm_auto_key=stock_reservations.stm_auto_key and stock_reservations.woo_auto_key=woo.woo_auto_key and stock_reservations.qty_reserved=1),
nvl((select max(stock.serial_number) from stock,stock_reservations,ro_detail where stock.stm_auto_key=stock_reservations.stm_auto_key and stock_reservations.rod_auto_key=ro_detail.rod_auto_key and ro_detail.woo_auto_key=woo.woo_auto_key and stock_reservations.qty_reserved=1),
(select max(serial_number) from stock,wo_stm_complete where stock.stm_auto_key=wo_stm_complete.stm_auto_key and wo_stm_complete.woo_auto_key=woo.woo_auto_key)))) serial_number
, mfg.mfg_code
, woo.last_status_chg
, wos.description status
, wos.severity
, decode(woo.released,'T','Y','N') RELEASED
, (select min(stamptime) from audit_trail where source_table='WOO' and source_field='RELEASED' and orig_value='F' and new_value='T' and source_ak=woo.woo_auto_key) RELEASE_DATE
, cmp.company_name customer_name
, cmp.company_code customer_code
, woo.company_ref_number CUSTOMER_PO
, decode(cmp.cv_udf_002,'T','Y','N') PBH_CUSTOMER
, case when (select count(stm_complete.pn) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key)<=1 then (select max(stm_complete.pn) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key) else 'MULTIPLE' end part_number_out
, case when (select count(stm_complete.serial_number) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key)<=1 then (select max(stm_complete.serial_number) from stock stm_complete left join wo_stm_complete
on wo_stm_complete.stm_auto_key=stm_complete.stm_auto_key where wo_stm_complete.woo_auto_key=woo.woo_auto_key) else 'MULTIPLE' end serial_number_out
, (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
, nvl((select tail_number from aircraft where aircraft.act_auto_key=woo.act_auto_key),nvl((select min(tail_number) from aircraft inner join rc_detail on rc_detail.act_auto_key=aircraft.act_auto_key where woo_auto_key=woo.woo_auto_key),(select min(tail_number) from aircraft inner join so_detail on so_detail.act_auto_key=aircraft.act_auto_key inner join exchange on so_detail.sod_auto_key=exchange.sod_auto_key where exchange.woo_auto_key=woo.woo_auto_key))) tail_number
, woo.due_date
, wwt.work_type work_requested
, wfw.code work_performed
, woo.block_13_8130 block_12_8130
, case when ctd.tat_days is not null then ctd.tat_days else case when dept_name like '%GSE%' then 18 else null end end contract_tat
, woo.manual_ecd
, 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
, trunc((select min(STATUS_CHANGE_DATE) from wo_quote_header inner join wo_quote_detail on wo_quote_header.wqh_auto_key=wo_quote_detail.wqh_auto_key where wo_quote_detail.woo_ref=woo.woo_auto_key and wo_quote_header.POST_STATUS = 3)) INVC_DATE
, trunc(nvl((select max(smh.ship_date) from sm_header smh where smh.woo_auto_key=woo.woo_auto_key),Null)) ship_date
, nvl((select max(smh.sm_number) from sm_header smh where smh.woo_auto_key=woo.woo_auto_key),Null) shipping_order
, nvl((select max(smh.smh_auto_key) from sm_header smh where smh.woo_auto_key=woo.woo_auto_key),Null) smh_auto_key
, nvl((select max(smh.tracking_number) from sm_header smh where smh.woo_auto_key=woo.woo_auto_key),Null) tracking_number
,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=11 and sm_header.woo_auto_key=woo.woo_auto_key),0,'N','Y') ship_hold
,(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
, cap.capability_code
, ctd.line_item contract_item
, cth.contract_number
, cth.SDF_CTH_001 CONTRACT_TYPE
,(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
, ba_wo_first_quote_sent(woo.woo_auto_key) first_quote_sent
, BA_WO_LAST_QUOTE_SENT(woo.woo_auto_key) last_quote_sent
, ba_wo_last_quote_approved(woo.woo_auto_key) last_quote_approved
, woo.MSG_QUOTE_SENT_DATE quote_sent_override
, woo.MSG_QUOTE_APPR_DATE quote_approved_override
, BA_WO_LAST_QUOTE(WOO.WOO_AUTO_KEY) LAST_QUOTE
, BA_WO_LAST_QUOTE_STATUS(WOO.WOO_AUTO_KEY) LAST_QUOTE_STATUS
, BA_WO_QUOTE_PRICE(WOO.WOO_AUTO_KEY) QUOTED_AMOUNT
, BA_WO_LAST_RO(WOO.WOO_AUTO_KEY) LAST_RO
, BA_WO_LAST_RO_VENDOR_INFO(WOO.WOO_AUTO_KEY) LAST_RO_VENDOR_INFO
, WOO.WO_UDF_010 WARRANTY_REQUESTED
, WOO.WARRANTEE_FLAG WARRANTY_GRANTED
, WOO.WO_UDF_016 WARRANTY_COMMENTS
, WOO.WO_UDF_017 WARRANTY_CODE
, WOO.WO_UDF_007 SCHEDULED_REMOVAL
, WOO.WO_UDF_008 NFF_FLAG
, WOO.WO_UDF_012 HRC_FLAG
, WOO.WO_UDF_011 LRC_FLAG
, WOO.WO_UDF_015 HRC_CODE
, WOO.OPEN_FLAG
, decode((select count(*) from audit_trail where source_table='WOO' and source_field='STATUS' and new_value='PARTS SHORTAGE' and source_ak=woo.woo_auto_key),0,'N','Y') WAS_PARTS_SHORTAGE
, ba_time_in_parts_shortage(woo.woo_auto_key)+decode(wos.description,'PARTS_SHORTAGE',round(sysdate-woo.last_status_chg),0) time_in_parts_shortage
, decode((SELECT COUNT(*) FROM WO_TASK WOT WHERE WOT.WOO_AUTO_KEY = WOO.WOO_AUTO_KEY AND WOT.REWORK_FLAG = 'T'),0,'N','Y') INTERNAL_REJECTION
, sysur.first_name||' '||sysur.last_name CSM
, woo.woo_auto_key
, woo.syscm_auto_key
, woo.PRIORITY
, woo.opm_auto_key
, woo.iq_sysur_notify INSPECTOR
from wo_operation woo
inner join parts_master pnm
on woo.pnm_auto_key=pnm.pnm_auto_key
left join manufacturer mfg
on pnm.mfg_auto_key=mfg.mfg_auto_key
left join wo_status wos
on woo.wos_auto_key=wos.wos_auto_key
left join companies cmp
on woo.cmp_auto_key=cmp.cmp_auto_key
left join department dpt
on woo.dpt_auto_key=dpt.dpt_auto_key
left join rc_detail rcd
on rcd.woo_auto_key=woo.woo_auto_key
left join rc_header rch
on rch.rch_auto_key=rcd.rch_auto_key
left join qctl.BA_VIEW_QUARANTINE_DAYS
on BA_VIEW_QUARANTINE_DAYS.rc_number=RCH.RC_NUMBER
inner join sys_companies syscm
on woo.syscm_auto_key=syscm.syscm_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 WO_WORK_TYPE wwt
on woo.wwt_auto_key=wwt.wwt_auto_key
left join work_flows wfw
on woo.wfw_auto_key=wfw.wfw_auto_key
left join capability_codes cap
on woo.cap_auto_key=cap.cap_auto_key
left join sys_users sysur
on case when dept_name in ('SH ATEC ARO','SH AVIONIC ARO','SH ELE/FUE ARO','SH HARNESS ARO','SH HYD/PDU ARO','SH IDG ARO','SH PNE/TRA ARO','SH REWIND ARO','SH SEATS ARO','SH SLI/OXY ARO','SH YT  ARO','SUB ARO') then cmp.cv_udf_016 else case when dept_name in ('SUB BIC','SH 1 INSTRUMENT BIC','SH 10 GSE REPAI','SH 12 ATEC BIC','SH 12 ATEC PH','SH 2 AVIONICS B','SH 3 ELE/MEC BI','SH 3 ELE/MEC PHX','SH 4 CREW SEATS BIC','SH 6 HYD BIC','SH 7 OXY BIC','SH ATEC 
SDF','SH GSE MANUF','SH GSE PHX','SH PAINT BIC','SH SEATS PHX','SH SEATS SDF','SH SUPERV PH','SH ATEC SDF') then cmp.cv_udf_017 else case when woo.syscm_auto_key=3 then cmp.cv_udf_018 else '' end end end = sysur.user_name
where woo.wo_type not in ('Mfg','Kit') and ((woo.syscm_auto_key=1 and woo.entry_date>sysdate-1*720) or (woo.syscm_auto_key=3 and woo.entry_date>sysdate-1*720))) wo_sum
) wo_sum2

Open in new window

Function:

create or replace FUNCTION RC_EXCUSABLE_DELAY_V2[b](v_rc_number IN VARCHAR, v_wo_receiving_date IN date, v_wo_entry_date IN date, p_before IN date)[/b]
RETURN NUMBER IS
     v_days NUMBER(11,2) :=0 ;
BEGIN
-- THIS FUNCTION RETURNS THE NUMBER OF DAYS A RC SPENT IN QUARANTINE

select round(sum(case when time_in_quarantine.start_time>nvl(p_before,sysdate) then 0
	else case when time_in_quarantine.stop_time<=nvl(p_before,sysdate) then time_in_quarantine.stop_time-time_in_quarantine.start_time
	else nvl(p_before,sysdate)-time_in_quarantine.start_time end end))
into v_days

from(
	select RC_NUMBER,
		trunc(case when rca.time_stamp>=nvl(v_wo_receiving_date,rca.time_stamp) then 
			case when rca.time_stamp<=nvl(v_wo_entry_date,rca.time_stamp) then rca.time_stamp else v_wo_entry_date end
			else case when v_wo_receiving_date<=nvl(v_wo_entry_date,rca.time_stamp) then v_wo_receiving_date else v_wo_entry_date end
		end) start_time,
		trunc((select case when nvl(min(time_stamp),sysdate)>=nvl(v_wo_receiving_date,nvl(min(time_stamp),sysdate)) then
				case when nvl(min(time_stamp),sysdate)<=nvl(v_wo_entry_date,nvl(min(time_stamp),sysdate)) then nvl(min(time_stamp),sysdate) else v_wo_entry_date end
				else case when v_wo_receiving_date<=nvl(v_wo_entry_date,nvl(min(time_stamp),sysdate)) then v_wo_receiving_date else v_wo_entry_date end
				end
			from rc_audit_log
			where rc_number = rca.rc_number and disp_code='OK' and time_stamp>rca.time_stamp)) stop_time
	from rc_audit_log rca
	where rca.rc_number = v_rc_number and rca.disp_code = 'QUARANTINE'
) time_in_quarantine;

RETURN(v_days);
END RC_EXCUSABLE_DELAY_V2;

Open in new window

slightwv (䄆 Netminder)

How is this different from your previous question?
https://www.experts-exchange.com/questions/29165531/Keep-receiving-error-when-running-view-ORA-06553-PLS-306-wrong-number-or-types-of-arguments-in-call.html

If you didn't get an answer there, why did you close it?

We cannot tell you what parameters to pass because we don't have your data.

The parameters MUST match the data types in the function declaration so something likes this:
select RC_EXCUSABLE_DELAY_V2('some_string', some_date_variable, some_other_date, yet_another_date)
from dual;

ASKER CERTIFIED SOLUTION

maximus1974

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

Why you were receiving the error and how to call it were basically still the same question.

>>I figured it out on my own.

Calling it without error and calling it correctly to get the proper results are two different things. Make sure the null parameters you are passing is the correct way to get the data you desire.

If the parameters are truly optional, declare them as such so you don't need them.

create or replace FUNCTION RC_EXCUSABLE_DELAY_V2[b](v_rc_number IN VARCHAR, v_wo_receiving_date IN date default null, v_wo_entry_date IN date default null, p_before IN date default null)[/b]
RETURN NUMBER IS

Open in new window

Then you can just do:

RC_EXCUSABLE_DELAY_V2(woo_auto_key)

Open in new window