How to send Payslip attachments to 5000 employees using Bursting concept? SELECT papf.person_number AS "KEY", papf.person_number AS "DELIVERY_KEY", 'burst_1000rec' AS TEMPLATE, 'en-US' AS LOCALE, 'PDF' AS OUTPUT_FORMAT, 'EMAIL' AS DEL_CHANNEL, (SELECT pea.email_address FROM per_email_addresses pea WHERE pea.person_id = papf.person_id AND pea.email_type = 'W1') AS PARAMETER1, NULL AS PARAMETER2, 'bipublisher-report@oracle.com' AS PARAMETER3, 'Your Custom Report' AS PARAMETER4, 'Hello, Please find your Payslip report attached.' AS PARAMETER5, 'true' AS PARAMETER6, 'donotreply@oracle.com' AS PARAMETER7 FROM per_all_people_f papf WHERE trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date AND ROWNUM <=5000...
Posts
Real-Time Scenario: Requirement: If any changes like first name Last Name/First Name got changed in a day then we need to capture those details and send to employees at the end of the day. Tables required for this: Per_all_pople_f Per_person_names_f Lets build the report Select papf.person_number, ppnf.first_name, ppnf.last_name, ppnf.last_update_date From Per_all_pople_f papf, Per_person_names_f ppnf where papf.person_id=ppnf.person_id and ppnf.name_type='GLOBAL' and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date and trunc(ppnf.last_update_date) between trunc(sysdate-1) and trunc(sysdate) ...
Fast Formula Requirements: Requirement is that write Fast Formula that can display leave days 25 if employee service is 5 or more else leave days 20 for other employee (less than 5 years’ service) STEPS: 1) Fast Formula Creation: First we need to create a fast formula and compile it for the requirement ***************************************************************************** FORMULA NAME: Service_Leave FORMULA TYPE: Global Absence Accrual DESCRIPTION: Returns 25 days if service >= 5 years, else 20, based on dynamic employee data. ***************************************************************************** */ DEFAULT FOR PER_REL_ORIGINAL_DATE_OF_HIRE IS '4712/12/31 00:00:00'(date) DEFAULT FOR PER_ASG_FTE IS 1 ld_hire_date = PER_REL_ORIGINAL_DATE_OF_HIRE ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE, '4712/12/31 00:00:00'(date)) ln_fte = PER_ASG_FTE ln_service_years = FLOOR(MONTHS_BETWEEN(ld_effective_date, ld_hire_date) / 12) IF (ln_servi...
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.NOTIFI...
Q: How to include multiple email addresses in Bursting the report? Created a new custom lookup “Venkat_EMAIL_IDS” to maintain email ids. As length of email id can be more than 30 characters, we maintained them in Description field. 1. Create a new data model. 2. Create a LOV for email ids using SQL Query. NOTE: Please note that we could not find any solution if we use static list of email ids in the data model LOV. Hence, email ids should come from a SQL query. 3. Create a menu type parameter as shown below. Please make a note of highlighted fields. 4. Create a sample data model as below. For demo purpose, I used below sample query. 5. Add bursting query as shown below. Here is full bursting query: SELECT ...