Report Query:
---------------------
SELECT PAPF.PERSON_ID ,PAPF.PERSON_NUMBER,PPNF.DISPLAY_NAME PERSON_NAME,HOUFT.NAME LEGALEMPLOYERFROM PER_ALL_PEOPLE_F PAPF,PER_PERSON_NAMES_F PPNF,PER_ALL_ASSIGNMENTS_M PAAM,HR_ORGANIZATION_UNITS_F_TL HOUFTWHERE 1 = 1AND PPNF.PERSON_ID = PAPF.PERSON_IDAND PPNF.NAME_TYPE(+) = 'GLOBAL'AND PAPF.PERSON_ID = PAAM.PERSON_IDAND PAAM.PRIMARY_FLAG = 'Y'AND PAAM.ASSIGNMENT_TYPE = 'E'AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'AND HOUFT.ORGANIZATION_ID = PAAM.LEGAL_ENTITY_IDAND HOUFT.LANGUAGE = 'US'AND HOUFT.ORGANIZATION_ID in (NVL(:P_LEGAL_EMPLOYER, HOUFT.ORGANIZATION_ID))--265 VALUESAND :P_AS_OF_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATEAND :P_AS_OF_DATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE AND :P_AS_OF_DATE BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATEAND :P_AS_OF_DATE BETWEEN HOUFT.EFFECTIVE_START_DATE AND HOUFT.EFFECTIVE_END_DATEORDER BY 3
Bursting Query:
---------------------------
Select PER.PERSON_ID KEY, --The Delivery key and must match the Deliver By element. The bursting engine uses the key to link delivery criteria to a specific section of the burst data. Ensure that you use double quotes around "KEY" in the select statement, for example:'Default' TEMPLATE, -- The name of the Layout to apply. Note that the value is the Layout name (for example, 'Customer Invoice'), not the template file name (for example, invoice.rtf).'N/A' TEMPLATE_FORMAT,'en-US' LOCALE, --The template locale, for example, 'en-US'.'PDF' OUTPUT_FORMAT, 'EMAIL' DEL_CHANNEL,'[email protected]' PARAMETER1, -- To'[email protected]' PARAMETER2, -- CC'[email protected]' PARAMETER3, -- From'Enrollment Reminder' PARAMETER4, -- Subject'Please see the attachment' PARAMETER5, -- Message body'true' 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.'[email protected]' PARAMETER7, -- Reply E-mail'[email protected]' PARAMETER8 -- Bcc (PARAMETER 9-10 are not used)from per_all_people_f perwhere sysdate between per.effective_start_date and per.effective_end_dateand per.person_number in ('10', '100', '1')

To set default Value for Date Parameter: {$SYSDATE()$}

Reference Bursting URL: https://docs.oracle.com/middleware/12...


#oraclefusion #BIPBurstin #bursting #oraclefusiontechnical #oracle