Getting Missing IN or OUT parameter at index :: 1 Vendor code 17041 error

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Getting Missing IN or OUT parameter at index :: 1 Vendor code 17041 error

Vemula Muni Aug 7 2019 — edited Aug 9 2019

SQL Developer Version : 1.5.5

DBA Version : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Query : When i am executing below query i am getting Missing IN or OUT parameter at index :: 1 Vendor code 17041 error . Please Help on this.

WITH parameters AS

(

SELECT TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') AS pstartdt -- Always use 4 digits for years

, TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') AS penddt

, 'CWR' AS per_org

FROM dual

)

, got_business_unit AS

(

SELECT p.pstartdt, p.penddt

, d1.emplid, d1.per_org, d1.effdt

, CASE

WHEN b.business_unit IS NULL

THEN 'I'

ELSE d1.hr_status

END AS hr_status

, b.business_unit

FROM parameters p

JOIN ps_cts_job_dtl1 d1 ON d1.per_org = p.per_org

AND d1.effdt <= p.penddt

OUTER APPLY (

SELECT MAX (b.business_unit) AS business_unit

FROM ps_cts_job_dtl d

JOIN ps_cts_iqn_bu b ON b.business_unit = d.business_unit

WHERE d.emplid = d1.emplid

AND d.effdt = d1.effdt

) b

WHERE d1.hr_status IN ('A', 'I') -- if necessary

)

SELECT emplid

, GREATEST (sdt, pstartdt) AS startdt

, LEAST ( NVL (edt - 1, penddt)

, penddt

) AS enddt

, per_org

, 'A' AS hr_status

, 'Employee is active as CWR and BU is in IQN_BU setup table'

AS reason

FROM got_business_unit

MATCH_RECOGNIZE

(

PARTITION BY emplid, pstartdt, penddt, per_org

ORDER BY effdt

MEASURES MIN (effdt) AS sdt

, i.effdt AS edt

ONE ROW PER MATCH

PATTERN (a+ i?)

DEFINE a AS hr_status = 'A'

, i AS hr_status = 'I'

)

WHERE NVL (edt - 1, penddt) > pstartdt

ORDER BY emplid

, sdt

;

Create Table and Insert scripts to execute above query.

CREATE TABLE PS_CTS_JOB_DTL (EMPLID VARCHAR2(11) NOT NULL,
   BUSINESS_UNIT VARCHAR2(5) NOT NULL,
   EMPL_RCD INT NOT NULL,
   EFFDT DATE,
   EFFSEQ INT NOT NULL,
   ACTION VARCHAR2(3) NOT NULL) ;
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('27-MAY-2019','DD-MON-YYYY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-2019','DD-MON-YYYY'),0,'DTA');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('04-JUN-2019','DD-MON-YYYY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('15-JUN-2019','DD-MON-YYYY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',2,to_date('15-JUN-2019','DD-MON-YYYY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US608',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','CN512',2,to_date('26-JUN-2019','DD-MON-YYYY'),0,'ASG');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('26-APR-2019','DD-MON-YYYY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('05-JUN-2019','DD-MON-YYYY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('06-JUN-2019','DD-MON-YYYY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('10-JUN-2019','DD-MON-YYYY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',2,to_date('12-JUN-2019','DD-MON-YYYY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('18-JUN-2019','DD-MON-YYYY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('26-JUN-2019','DD-MON-YYYY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-2019','DD-MON-YYYY'),0,'DTA'); 
PS_CTS_JOB_DTL1 Table Build and Insert Scripts:




    
CREATE TABLE PS_CTS_JOB_DTL1 (EMPLID VARCHAR2(11) NOT NULL,
   EMPL_RCD INT NOT NULL,
   EFFDT DATE,
   EFFSEQ INT NOT NULL,
   HR_STATUS VARCHAR2(1) NOT NULL,
   PER_ORG VARCHAR2(3) NOT NULL) ;
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('27-MAY-2019','DD-MON-YYYY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('02-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',0,to_date('04-JUN-2019','DD-MON-YYYY'),0,'I','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',1,to_date('15-JUN-2019','DD-MON-YYYY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('15-JUN-2019','DD-MON-YYYY'),1,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733930',2,to_Date('26-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('26-APR-2019','DD-MON-YYYY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',0,to_date('05-JUN-2019','DD-MON-YYYY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('06-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',1,to_date('10-JUN-2019','DD-MON-YYYY'),0,'I','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('12-JUN-2019','DD-MON-YYYY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('18-JUN-2019','DD-MON-YYYY'),0,'A','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',2,to_date('19-JUN-2019','DD-MON-YYYY'),0,'I','EMP');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('26-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
Insert into PS_CTS_JOB_DTL1 (EMPLID,EMPL_RCD,EFFDT,EFFSEQ,HR_STATUS,PER_ORG) values ('733931',3,to_date('28-JUN-2019','DD-MON-YYYY'),0,'A','CWR');
PS_CTS_IQN_BU Table Build and Insert Scripts: ( IQN business unit setup )
CREATE TABLE PS_CTS_IQN_BU (COUNTRY VARCHAR2(3) NOT NULL,
   BUSINESS_UNIT VARCHAR2(5) NOT NULL) ;
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US410');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('USA','US412');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('CAN','CN512');
Insert into PS_CTS_IQN_BU (COUNTRY,BUSINESS_UNIT) values ('IND','IN208');