Venky DevMastery

  Why do we get 2 records when we search for per_perosn_names_f table in Oracle HCM Cloud? Answer: Click Here

BIP vs OTBI Click Here

 /*Group by Functions GROUP or AGGREGATE Functions Group or Aggregate functions process similary group of rows and returns one value   1.MAX()  2.MIN()  3.SUM()  4.AVG()  5.COUNT()  6.COUNT(*)  7.count(1) 1.MAX() It returns maximum value.  Syntax: MAX(colname) Eg: */   SELECT MAX(SAL) FROM EMP;      SELECT MAX(HIREDATE) FROM EMP; --It will give us the most recent record which has the recent date.   select hiredate from emp order by hiredate desc;   SELECT MAX(ENAME) FROM EMP; --WARD   select ename from emp order by ename desc; select max(sal) from emp;--5000 select max(hiredate) from emp;--12-JAN-83 select max(ename) from emp;--WARD select max(empno) from emp;--7934 select max(job) from emp;--SALESMAN select max(comm) from emp;--1540 select max(deptno) from emp;--30 select comm from emp; /* MI...

  About DB, Click here

 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...

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)                                  ...