Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Fetching Contract Employee information by Join the JOB_DTL,JOB_DTL1,IQN_BU Tables.

Vemula MuniAug 1 2019 — edited Aug 6 2019

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

Below is my "PS_CTS_JOB_DTL"  table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

EFFDT : this filed use to track when change happens.

EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

ACTION : Will determine which action they taken on employee ( please don't consider action field to achieve output as below. because action codes may different in each time)

JOB Table
EMPLIDBUSINESS_UNITEMPL_RCDEFFDTEFFSEQACTION
733930US410027-May-190ADD
733930US410002-Jun-190DTA
733930US41004-Jun-190TER
733930US41016-Jun-190HIR
733930US410115-Jun-190TER
733930US410215-Jun-191ADD
733930US608219-Jun-190ASC
733930CN512226-Jun-190ASG
733931US410026-Apr-190HIR
733931US41005-Jun-190TER
733931IN20816-Jun-190ADD
733931IN208110-Jun-190TER
733931IN208212-Jun-190HIR
733931IN408218-Jun-190ASC
733931IN408219-Jun-190TER
733931IN208326-Jun-190ADD
733931IN208328-Jun-190DTA

Below is my "PS_CTS_JOB_DTL1"  table structure and data. in this table (EMPLID,EMPL_RCD,EFFDT,EFFSEQ) are key fields.

EMPL_RCD : when ever employee converted from contract to full time and from full time to contract this value will increase. (0,1,2,...)

EFFDT : this filed use to track when change happens.

EFFSEQ : this field will increase from 0 to 1,2 .. when there is more than one transaction in same date.( with same effdt )

HR_STATUS : To identify employee status ( A = Active, I = Inactive ).

PER_ORG : to identify employee type ( CWR = Contract, EMP = Employee )

EMPL_IDEMPL_RCDEFFDTEFFSEQHR_STATUSPER_ORG
733930027-May-190ACWR
733930002-Jun-190ACWR
73393004-Jun-190ICWR
73393016-Jun-190AEMP
733930115-Jun-190IEMP
733930215-Jun-191ACWR
733930219-Jun-190ACWR
733930226-Jun-190ACWR
733931026-Apr-190AEMP
73393105-Jun-190IEMP
73393116-Jun-190ACWR
733931110-Jun-190ICWR
733931212-Jun-190AEMP
733931218-Jun-190AEMP
733931219-Jun-190IEMP
733931326-Jun-190ACWR
733931328-Jun-190ADTA

Below is my "PS_CTS_IQN_BU"  table structure and data.

IQN BU Setp
CountryBusiness_Unit
USAUS410
USAUS412
CANCN512
INDIN208

Based on above three tables i need to fetch below output. When i pass from date and to date in query (month start and end dates i.e like '01-JUN-19' and '30-JUN-19' ) , query should fetch employee active from active to dates as CWR and that employee Business unit should be in PS_CTS_IQN_BU table.

Required output

Note : Reason column for understanding, no need to display reason column

While fetching below out put Please map EMPLID,EMPL_RCD,EFFDT,EFFSEQ Key fields in both JOB_DTL and JOB_DTL1  ( Map  Key fields is very important )

Employee 733930 is active as CWR  in between '19-JUN-19' to '25-JUN-19' in different BU which not in IQN_BU setup table, so i want to exclude this information.

EMPLIDFROM_DATETo_DATEPER_ORGHR_STATUSReason
7339301-Jun-193-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393015-Jun-1918-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393026-Jun-1930-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
7339316-Jun-199-Jun-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table
73393126-Jun-1930-JUN-19CWRAEmployee is active as CWR and BU is in IQN_BU setup table

PS_CTS_JOB_DTL Table Build and Insert Scripts: 

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-19','DD-MON-YY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('02-JUN-19','DD-MON-YY'),0,'DTA');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',0,to_date('04-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('06-JUN-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',1,to_date('15-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US410',2,to_date('15-JUN-19','DD-MON-YY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','US608',2,to_date('19-JUN-19','DD-MON-YY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733930','CN512',2,to_date('26-JUN-19','DD-MON-YY'),0,'ASG');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('26-APR-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','US410',0,to_date('05-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('06-JUN-19','DD-MON-YY'),1,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',1,to_date('10-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',2,to_date('12-JUN-19','DD-MON-YY'),0,'HIR');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('18-JUN-19','DD-MON-YY'),0,'ASC');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN408',2,to_date('19-JUN-19','DD-MON-YY'),0,'TER');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('26-JUN-19','DD-MON-YY'),0,'ADD');
Insert into PS_CTS_JOB_DTL (EMPLID,BUSINESS_UNIT,EMPL_RCD,EFFDT,EFFSEQ,ACTION) values ('733931','IN208',3,to_date('28-JUN-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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-19','DD-MON-YY'),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');
This post has been answered by Frank Kulash on Aug 1 2019
Jump to Answer

Comments

Processing

Post Details

Added on Aug 1 2019
#sql-pl-sql
12 comments
1,645 views