-

Fetch employee's work/personal and manager's email address?

 SELECT

GL.person_id KEY,

'Template Name' TEMPLATE,

'en-US' LOCALE,

'RTF' template_format,

'PDF' output_format,

'EMAIL'  DEL_CHANNEL,

'Document Name'  OUTPUT_NAME,

GL.work_email ||';'|| GL.Personal_Email || PARAMETER1,

'noreply@company.com' PARAMETER3,

'Email Body' AS PARAMETER5,

'true'    PARAMETER6

FROM (

SELECT

papf.person_id,

haou_lemp.name      legal_employer,

hla.address_line_1  work_address_line_1,

hla.address_line_2  work_address_line_2,

hla.address_line_3  work_address_line_3,

hla.address_line_4  work_address_line_4,

hla.region_2        work_state,

hla.town_or_city    work_town_or_city,

hla.postal_code     work_postal_code,

hla.country         work_country,

papf.person_number,

(ppnf.first_name || ' ' || ppnf.last_name) Full_Name,

ppos.actual_termination_date  Termination_Date,

ppos.NOTIFIED_TERMINATION_DATE Resignation_Date,

ppos.LAST_WORKING_DATE Last_Working_Date,

(case when pea.email_type = 'W1' then nvl(pea.email_address, ' ')

                else ' '

            end

        ) as work_email,

       

 

(case when pea.email_type = 'H1' then nvl(pea.email_address, ' ')

                else ' '

            end

        ) as Personal_Email,

 

sysdate Today_date

 

 

FROM

hr_organization_units haou_lemp,

per_all_assignments_m paam,

per_person_names_f ppnf,

per_all_people_f papf,

hr_locations hla,

per_periods_of_service ppos,

per_allocated_tasks_vl pat,

per_allocated_checklists_vl pacl,

per_email_addresses pea,

per_people_f ppf

   

 

where

paam.person_id = papf.person_id

and paam.person_id = ppf.person_id

and paam.location_id = hla.location_id

and ppnf.person_id = papf.person_id

And haou_lemp.organization_id(+) = paam.legal_entity_id

AND paam.period_of_service_id = ppos.period_of_service_id

and ppf.PRIMARY_EMAIL_ID = pea.EMAIL_ADDRESS_ID(+)

and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and trunc(sysdate) between hla.effective_start_date and hla.effective_end_date

And trunc(sysdate) Between haou_lemp.date_from(+) And haou_lemp.date_to(+)

And trunc(sysdate) Between ppnf.effective_start_date And ppnf.effective_end_date

and trunc(sysdate) BETWEEN ppf.effective_Start_Date

    and ppf.effective_end_date

And ppnf.name_type = 'GLOBAL'

And paam.effective_latest_change = 'Y'

and paam.assignment_type = 'E'

and paam.assignment_status_type = 'ACTIVE'

) GL

Comments

Post a Comment

Popular posts from this blog

Importance of Incremental Data in Oracle Fusion

-
 What is incremental data? Incremental Data Loads ----------------------- If you are sending employee data to a third-party payroll provider on every Friday, we don’t want to send all employees instead we only want the ones who changed current week. In this scenario, "LAST_UPDATE_DATE" column in the table plays vital role. LAST_UPDATE_DATE tells us when the record was physically saved. */ --Examples: ------------------------------- select count(1) from ( Select  papf.person_number, ppnf.FULL_NAME from  per_all_people_f papf, per_person_names_f ppnf WHERE 1=1 and papf.person_id=ppnf.person_id  and papf.LAST_UPDATE_DATE >=sysdate-7 --(Incremental) and ppnf.NAME_TYPE='GLOBAL' and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and sysdate between ppnf.EFFECTIVE_START_DATE and ppnf.EFFECTIVE_END_DATE ) Result:  55 Records(Only Changed Records in current week) --------------------------------- select count(1) from ( Select  papf.person_n...
Read more

Mastering Parameters in Oracle Fusion BI Publisher

-
Mastering Parameters in Oracle Fusion BI Publisher: Mandatory, Optional, and "All" Logic When building Data Models in Oracle Fusion, how you handle parameters determines the user experience and report performance. Here is the definitive syntax guide: 1. Mandatory Parameters Use this when a value must be provided for the report to run. Syntax: AND column =:parameter Example: AND Person_Number = :p_Person_Number 2. Optional Parameters (The "All" Logic) Use this to allow users to leave a field blank or select "All" to see all records. Syntax (Recommended):  AND (:parameter IS NULL OR column = :parameter) Example: AND (:p_Person_Number IS NULL OR Person_Number = :p_Person_Number) Note: Using NVL is common but can be slower on large datasets as it may bypass indexes. 3. Multi-Select Parameters Use this when the user can select multiple values from a list. Syntax: AND (column IN (:parameter) OR 'All' IN (:parameter)) Example: AND (Person_Number IN (:p_Pe...
Read more

REST Web Service in Oracle Fusion to manage Banks

-
 REST Web Service  ================= Rest Resources Resources would have 5 end-points(methods) usually.  Ex: Create a Bank Read-Get a/all Banks Update Bank Delete a Bank Fusion Navigation for managing Banks  Setup and Maintenance Tasks Global Tasks Manage Banks Get All Banks  /fscmRestApi/resources/11.13.18.05/cashBanks https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks Get a specific Bank  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Create a Bank  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks patch  Update  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Put https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Delete  https://fa-eubg-saas...
Read more