BI - Email Notifications

Burst SQL

SELECT DISTINCT PAPF.PERSON_ID AS "KEY"

    , 'XX_Email_PDF_POC_Template' TEMPLATE

    , 'en_US' LOCALE

    , 'PDF' OUTPUT_FORMAT

    , 'Payslip_PDF' OUTPUT_NAME

    , 'EMAIL' DEL_CHANNEL

    , PEA.EMAIL PARAMETER1

    , '' PARAMETER2

    , 'hcko-test.bi.sender@workflow.mail.us2.cloud.oracle.com'  PARAMETER3

    , 'Test Email - Sample PDF attached to the email' PARAMETER4

    , 'Email PDF POC' PARAMETER5

    , 'true' PARAMETER6

    , 'donotreply@lb.com' PARAMETER7

FROM PER_ALL_PEOPLE_F PAPF

    , (SELECT PEA.PERSON_ID, PEA.EMAIL_ADDRESS EMAIL, ROW_NUMBER() OVER (PARTITION BY PEA.PERSON_ID ORDER BY PEA.DATE_FROM DESC) ROW_ID FROM PER_EMAIL_ADDRESSES PEA WHERE PEA.EMAIL_TYPE = 'H1') PEA

WHERE PAPF.PERSON_ID = PEA.PERSON_ID(+)

    AND PEA.ROW_ID(+) = 1

and PAPF.PERSON_NUMBER IN (:Per_num)

----------------------------------------------------------------------------

PARAMETER1: Email address
PARAMETER2: cc
PARAMETER3: From
PARAMETER4: Subject
PARAMETER5: Message body
PARAMETER6: Attachment value ('true' or 'false'). If your output format is PDF, you must set this parameter to "true" to attach the PDF to the e-mail.
PARAMETER7: Reply-To
PARAMETER8: Bcc (PARAMETER 9-10 are not used)

Data Model

SELECT PAPF.PERSON_ID
        , PAPF.PERSON_NUMBER PER_NUM
        , PPNF.FIRST_NAME FNAME
        , PPNF.LAST_NAME LNAME
        , PPNF.MIDDLE_NAMES MNAME
        , PPNF.SUFFIX
        , PEA.EMAIL
    FROM PER_ALL_PEOPLE_F PAPF
        , PER_PERSON_NAMES_F PPNF
        , (SELECT PEA.PERSON_ID, PEA.EMAIL_ADDRESS EMAIL, ROW_NUMBER() OVER (PARTITION BY PEA.PERSON_ID ORDER BY PEA.DATE_FROM DESC) ROW_ID FROM PER_EMAIL_ADDRESSES PEA WHERE PEA.EMAIL_TYPE = 'H1') PEA
    WHERE :AsOfDate BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
        AND PAPF.PERSON_ID = PPNF.PERSON_ID
        AND PPNF.NAME_TYPE = 'GLOBAL'
        AND :AsOfDate BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
        AND PAPF.PERSON_ID = PEA.PERSON_ID(+)
        AND PEA.ROW_ID(+) = 1
and papf.person_id in (100000016437833,100000016441303)