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_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 --(No 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: 

6155 Records(All the records)