SQL & PL/SQL

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)

Note : Data Volume of this table is more than 50 million rows

PS_CTS_JOB_DTL1 Table :

Note : Data Volume of this table is more than 50 million rows

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 )

Note : employee may contains more then one row with HR_STATUS = 'A' before he is going to inactive ( HR_STSTUS = 'I' )

Below is my "PS_CTS_IQN_BU"  table structure and data.

Required Output based on above data :

When i pass the start date as TO_DATE ('01-JUN-2019', 'DD-MON-YYYY') and end date as  TO_DATE ('30-JUN-2019', 'DD-MON-YYYY') Query should fetch the below data.

1. When ever employee is active as CWR and business unit value is available in IQN_BU table We need to fetch that employee information.
2. In below output first row from date and to date will be '01-JUN-2019' because there is a row in JOB_DTL table with effective date '02-JUN-2019'.
3. in second row employee active as CWR with new row 02-JUN-2019 to 03-JUN-2019  because he is inactive on 04-JUN-2019.
4. When there is consecutive rows with HR_STATUS ='A' then we need to break from date and to dates for each row.