Keep receiving error when running view: ORA-06553: PLS-306: wrong number or types of arguments in call

maximus1974
maximus1974 used Ask the Experts™
on
Keep receiving this error when running the view below. I have included the SQL from both the view and the function I am calling within the view. Any help will be appreciated.

ORA-06553: PLS-306: wrong number or types of arguments in call to 'RC_EXCUSABLE_DELAY_V2'
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:
Error at Line: 10 Column: 157


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
,RC_EXCUSABLE_DELAY_V2(woo_auto_key) rc_excusable_delay_v2
,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)-nvl(RC_EXCUSABLE_DELAY_V2(woo_auto_key,null),0)-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(v_rc_number IN VARCHAR, v_wo_receiving_date IN date, v_wo_entry_date IN date, p_before IN date)
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
RC_EXCUSABLE_DELAY_V2(woo_auto_key) -- you're calling the function with 1 parameter

RC_EXCUSABLE_DELAY_V2(woo_auto_key,null)  -- you're calling the function with 2 parameters.


create or replace FUNCTION RC_EXCUSABLE_DELAY_V2(v_rc_number IN VARCHAR, v_wo_receiving_date IN date, v_wo_entry_date IN date, p_before IN date)


The function requires 4 parameters

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial