probation alerts

 -------------------------probation alerts--------------------------------------------------

---------bursting

1.probationperiod splitby---/data_ds/g_1/key

deliver BY              ----/data_ds/g_1/key

------------bursting query---

WITH main AS

(

       SELECT emp.person_number                     employee_number,

              emp_name.full_name                    employee_name,

              to_char(ppos.date_start,'DD-MM-YYYY') hire_date,

              ppos.worker_number,

              mgr.person_number             supervisor_emp_num,

              mgr_name.full_name            supervisor_name,

              paam_emp.effective_start_date emp_asgnmt_start_dt,

              paam_emp.effective_end_date   emp_asgnmt_end_dt,

              nvl(ple.NAME,' ')             legal_employer_name,

              paam_emp.probation_period,

              pea_mgr.email_address mngr_email,

              pasf.person_id        KEY,

              paam_emp.date_probation_end,

              trunc(paam_emp.date_probation_end)   - trunc(ppos.date_start) diff,

              trunc(ppos.date_start)               + 80                     pod,

              trunc(paam_emp.effective_start_date) + 80                     podm,

              trunc(sysdate)                       - 83                     podpp,

              trunc(sysdate)                       - trunc(ppos.date_start) diff2

       FROM   per_all_people_f emp,

              per_all_assignments_m paam_emp,

              per_periods_of_service ppos,

              per_legal_employers ple,

              per_assignment_supervisors_f pasf,

              per_all_people_f mgr,

              per_all_assignments_m paam_mgr,

              per_person_names_f emp_name,

              per_person_names_f mgr_name,

              per_email_addresses pea_mgr

       WHERE  emp.person_id = paam_emp.person_id

       AND    paam_emp.primary_flag = 'Y'

       AND    paam_emp.assignment_type IN ('E',

                                           'C')

       AND    paam_emp.assignment_status_type = 'ACTIVE'

       AND    paam_emp.effective_latest_change = 'Y'

       AND    trunc(sysdate) BETWEEN emp.effective_start_date AND    emp.effective_end_date

       AND    trunc(sysdate) BETWEEN paam_emp.effective_start_date AND    paam_emp.effective_end_date

       AND    ppos.period_of_service_id = paam_emp.period_of_service_id

       AND    paam_emp.legal_entity_id = ple.organization_id

       AND    trunc(sysdate) BETWEEN ple.effective_start_date AND    ple.effective_end_date

       AND    ple.status='A'

       AND    emp.person_id = pasf.person_id

       AND    pasf.manager_type = 'LINE_MANAGER'

       AND    trunc(sysdate) BETWEEN pasf.effective_start_date AND    pasf.effective_end_date

       AND    mgr.person_id = pasf.manager_id

       AND    trunc(sysdate) BETWEEN mgr.effective_start_date AND    mgr.effective_end_date

       AND    mgr.person_id = paam_mgr.person_id

       AND    paam_mgr.primary_flag = 'Y'

       AND    paam_mgr.assignment_type IN ('E',

                                           'C')

       AND    paam_mgr.assignment_status_type = 'ACTIVE'

       AND    paam_mgr.effective_latest_change = 'Y'

       AND    trunc(sysdate) BETWEEN paam_mgr.effective_start_date AND    paam_mgr.effective_end_date

       AND    emp.person_id = emp_name.person_id

       AND    emp_name.name_type = 'GLOBAL'

       AND    trunc(sysdate) BETWEEN emp_name.effective_start_date AND    emp_name.effective_end_date

       AND    mgr.person_id = mgr_name.person_id

       AND    mgr_name.name_type = 'GLOBAL'

       AND    trunc(sysdate) BETWEEN mgr_name.effective_start_date AND    mgr_name.effective_end_date

       AND    mgr.person_id=pea_mgr.person_id

              --and

              --and paam_emp.PROBATION_PERIOD = 80

              --and emp.person_number  in ('20028','20029')

       AND    trunc(paam_emp.date_probation_end) = trunc(ppos.date_start) +79

       AND    trunc(ppos.date_start) = trunc(sysdate) - 80 )SELECT DISTINCT main.KEY                                                 AS "KEY",

                'Probationperiod'                                        TEMPLATE,

                'en-US'                                                  LOCALE,

                'html'                                                   OUTPUT_FORMAT,

                ''                                                       OUTPUT_NAME,

                'EMAIL'                                                  DEL_CHANNEL ,

                main.mngr_email                                          AS PARAMETER1, -- TO

                ''                                                       AS PARAMETER2, --CC

                'ewgq-test.fa.sender@workflow.mail.em2.cloud.oracle.com'    PARAMETER3, -- FROM

                'Probation Period'                                       AS PARAMETER4, -- Subject

                'false'                                                  AS PARAMETER6  -- Attachment

FROM MAIN

------------------------main query

SELECT emp.person_number

       employee_number,

       emp_name.full_name                                          employee_name

       ,

       To_char(ppos.date_start, 'DD-MM-YYYY')

       Hire_date,

       ppos.worker_number,

       mgr.person_number

       supervisor_emp_num,

       mgr_name.full_name

       supervisor_name,

       paam_emp.effective_start_date

       Emp_Asgnmt_start_dt,

       paam_emp.effective_end_date

       Emp_Asgnmt_end_dt,

       Nvl(PLE.NAME, ' ')

       LEGAL_EMPLOYER_NAME,

       paam_emp.probation_period,

       pea_mgr.email_address                                       mngr_email,

       pasf.person_id                                              KEY,

       paam_emp.date_probation_end,

       Trunc(paam_emp.date_probation_end) - Trunc(ppos.date_start) diff,

       Trunc(ppos.date_start) + 80                                 POD,

       Trunc(paam_emp.effective_start_date) + 80                   PODM,

       Trunc(sysdate) - 83                                         PODPP,

       Trunc(sysdate) - Trunc(ppos.date_start)                     Diff2

FROM   per_all_people_f emp,

       per_all_assignments_m paam_emp,

       per_periods_of_service ppos,

       per_legal_employers ple,

       per_assignment_supervisors_f pasf,

       per_all_people_f mgr,

       per_all_assignments_m paam_mgr,

       per_person_names_f emp_name,

       per_person_names_f mgr_name,

       per_email_addresses pea_mgr

WHERE  emp.person_id = paam_emp.person_id

       AND paam_emp.primary_flag = 'Y'

       AND paam_emp.assignment_type IN ( 'E', 'C' )

       AND paam_emp.assignment_status_type = 'ACTIVE'

       AND paam_emp.effective_latest_change = 'Y'

       AND Trunc(sysdate) BETWEEN emp.effective_start_date AND

                                  emp.effective_end_date

       AND Trunc(sysdate) BETWEEN paam_emp.effective_start_date AND

                                  paam_emp.effective_end_date

       AND ppos.period_of_service_id = paam_emp.period_of_service_id

       AND paam_emp.legal_entity_id = PLE.organization_id

       AND Trunc(sysdate) BETWEEN PLE.effective_start_date AND

                                  PLE.effective_end_date

       AND PLE.status = 'A'

       AND emp.person_id = pasf.person_id

       AND pasf.manager_type = 'LINE_MANAGER'

       AND Trunc(sysdate) BETWEEN pasf.effective_start_date AND

                                  pasf.effective_end_date

       AND mgr.person_id = pasf.manager_id

       AND Trunc(sysdate) BETWEEN mgr.effective_start_date AND

                                  mgr.effective_end_date

       AND mgr.person_id = paam_mgr.person_id

       AND paam_mgr.primary_flag = 'Y'

       AND paam_mgr.assignment_type IN ( 'E', 'C' )

       AND paam_mgr.assignment_status_type = 'ACTIVE'

       AND paam_mgr.effective_latest_change = 'Y'

       AND Trunc(sysdate) BETWEEN paam_mgr.effective_start_date AND

                                  paam_mgr.effective_end_date

       AND emp.person_id = emp_name.person_id

       AND emp_name.name_type = 'GLOBAL'

       AND Trunc(sysdate) BETWEEN emp_name.effective_start_date AND

                                  emp_name.effective_end_date

       AND mgr.person_id = mgr_name.person_id

       AND mgr_name.name_type = 'GLOBAL'

       AND Trunc(sysdate) BETWEEN mgr_name.effective_start_date AND

                                  mgr_name.effective_end_date

       AND mgr.person_id = pea_mgr.person_id

       --and 

       --and paam_emp.PROBATION_PERIOD = 80

       --and emp.person_number  in ('20028','20029')

       --and  trunc(paam_emp.date_probation_end)  = trunc(ppos.Date_start) +79

       AND Trunc(ppos.date_start) = Trunc(sysdate) - 80