1. Using Enhanced Talent Profiles
  2. Full File and Changes only Extract of Licenses and Certifications

Full File and Changes only Extract of Licenses and Certifications

Certain third-party providers require the initial generation of a complete file, followed by subsequent runs that only incorporate updated data, if any changes in the data are detected compared to the previous run. Use the listed user entities (UEs) and their corresponding database items (DBIs) to retrieve licenses and certifications.

User Entity Root Block Level Hide How to Identify Database Item Identifier under User Entity? Function Connect Data Groups
PER_EXT_SEC_PERSON_UE Y 1 N Extract Person % Extract the person's details. NA
PER_EXT_SEC_ASSIGNMENT_BASIC_UE N 2 Y Extract Assignment %

Extract all the assignments of a given person.

Example: Filter criteria to ensure that all assignments that have at least 1 license for third-party verification based on position/job profile, with preference given to position profile, are only identified.

Assignment status is also another condition. 

EXISTS(SELECT 1 FROM fusion.per_all_assignments_m am,
     FUSION.HRT_PROFILES_VL pq,
      FUSION.HRT_CERTIFICATION_ITEMS_V piq,
      FUSION.HRT_CONTENT_ITEMS_VL ciq      where
      piq.profile_id=pq.profile_id
and ciq.content_item_id=piq.content_item_id
and pq.PROFILE_USAGE_CODE = 'P'
and pq.PROFILE_STATUS_CODE = 'A'
and am.person_id =pq.person_id
and am.assignment_id=asg.assignment_id
and am.ASSIGNMENT_STATUS_TYPE='ACTIVE'
and am.ASSIGNMENT_TYPE in ('E','C','P','N')
and TRUNC(pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE')) between am.effective_start_date and am.effective_end_date
and am.effective_latest_change ='Y'
and pq.BUSINESS_GROUP_ID=piq.BUSINESS_GROUP_ID
and ciq.BUSINESS_GROUP_ID=piq.BUSINESS_GROUP_ID
and am.BUSINESS_GROUP_ID=piq.BUSINESS_GROUP_ID
and exists (select 1 from FUSION.HRT_PROFILES_B p,
      FUSION.HRT_CERTIFICATION_ITEMS_V pi,
      FUSION.HRT_CONTENT_ITEMS_VL ci
      ,FUSION.HRT_PROFILE_RELATIONS pr
where pi.profile_id=p.profile_id
and ci.content_item_id=pi.content_item_id
and pi.content_item_id=piq.content_item_id
and pr.object_id = NVL(am.POSITION_ID,am.job_id)
and P.PROFILE_USAGE_CODE = 'M'
and pr.PROFILE_ID = p.PROFILE_ID
and TRUNC(pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE')) between pr.OBJECT_EFF_START_DATE and nvl(pr.OBJECT_EFF_END_DATE,to_date('31-12-4712','DD-MM-YYYY'))
and p.BUSINESS_GROUP_ID=pi.BUSINESS_GROUP_ID
and ci.BUSINESS_GROUP_ID=pi.BUSINESS_GROUP_ID 
and ci.ITEM_TEXT_9='Y'
and pi.REQUIRED_FLAG='Y'))

Parent Data Group Database Item: Extract Person ID

Database Item: Extract Assignment Person ID
HRT_EXT_ASG_MODEL_CERTIFICATION_UE N 3 Y Extract Assignment Model Certifications % Extract all the licenses that require third-party verification related to the position or job of a given assignment.

Parent Data Group Database Item: Extract Assignment ID

Database Item: Extract Assignment Model Certification Details Assignment ID
HRT_EXT_ASG_PERSON_CERTIFICATION_UE N 4 N Extract Assignment Person Certifications % Extract all the licenses of a person that require third-party verification related to the position or job of a given assignment.

Parent Data Group Database Item: Extract Assignment Model Certification Details

Database Item: Extract Assignment Person Certification Assignment ID

Parent Data Group Database Item: Extract Assignment Model Certification Content Item

Database Item: Extract Assignment Person Certification Content Item ID

In the above approach, all the licenses and certifications belonging to a person that need third-party verification can be brought under one node in the output. This approach is easy to implement when the changes only data is required in the subsequent runs. The extract can be threaded based on person ID. When implementing the changes-only extract, ensure to include only those DBIs in the records, whose value change must trigger the person record to be reported in the extract.Edit filter criteria condition

Also, while implementing this extract, ensure to add additional filter predicates on the root block to extract the person records. This could be based on a legal entity ID, business unit ID, department ID, location ID, and so on. This will ensure the extract doesn't run for all the person records in the Fusion HCM system and would be performance efficient.

When you provide the filter criteria on level 1, note that if the person record undergoes a change filtering it out of this filter criteria, then this record won't be reported in the subsequent changes only run.

For example, if the filter criteria was based on country USA and if there was an employee in USA, the record would be part of the extract. Now, if the employee changes the country, the employee record is no longer part of this extract and the customer must manually remove it from the provider's roster.

Note: It's not recommended to change the structure of the extract.

Any changes made to the 1st, 2nd, 3rd, 4th block will result in changes only data. Attributes from each block is listed below.

Block Name Attribute Tag Name in Extract Definition UI Attribute
PER_EXT_SEC_PERSON_UE Person_First_Name First Name
Person_Last_Name Last Name
Person_Middle_Name Middle Name
Person_Number Person Number
Person_Latest_Termination_Date Termination Date
Extract_Person_ID Person ID
Person_EMail_Address Person Primary Email Address
Person_Primary_National_Identifier_Number Person Primary National Identifier Number
Person_Date_of_Birth Date of Birth
Person_Country Person Address Country Code
Person_Country_Name Person Address Country Name
Person_Postal_Code Person Address Postal Code
Person_Address_Line1 Person Address Line 1
Person_Address_Line2 Person Address Line 2
Person_Address_Line3 Person Address Line 3
Person_Address_Line4 Person Address Line 4
Person_Primary_NID_Legislation_Code

Person National Identifier Country Code
Person_Primary_NID_Legislation_Name Person National Identifier Country Name
Person_Phone_Type Person Primary Phone Type
Person_Address_Region1 Person Address County (For US Legislation for Postal Address format)
Person_Address_Region3
Person_Phone_Number Person Primary Phone Number
Person_Home_Town_or_City Person Address City

Person_Primary_NID_Type

 Person National Identifier Type
Person_Address_Region2 Person Address State (For US Legislation for Postal Address format)
PER_EXT_SEC_ASSIGNMENT_BASIC_UE Extract_Assignment_ID Assignment ID
Extract_Assignment_Name Assignment Name
Extract_Assignment_Number Assignment Number
HRT_EXT_ASG_MODEL_CERTIFICATION_UE Extract_Assignment_Model_Certifications_Certification_Number Certification Number
Extract_Assignment_Model_Certifications_Content_Item_ID Content Item ID
Extract_Assignment_Model_Certifications_Job_Code Job Code (of the respective Job profile, which states this certification needs third-party verification)
Extract_Assignment_Model_Certifications_Job_ID Job ID (of the respective Job profile, which states this certification needs third-party verification)

Extract_Assignment_Model_Certifications_Name

Certification Name
Extract_Assignment_Model_Certifications_Object_ID Job ID or Position ID (based on the model profile from which certification is marked for third-party verification)
Extract_Assignment_Model_Certifications_Position_Code Position Code (of the respective Position profile, which states this certification needs third-party verification)
Extract_Assignment_Model_Certifications_Position_ID Position ID (of the respective Position profile, which states this certification needs third-party verification)
Extract_Assignment_Model_Certifications_Need_Third_Party_Verification
Extract_Assignment_Model_Certifications_Details_Assignment_ID
Extract_Assignment_Model_Certifications_Educational_Establishment_ID
Extract_Assignment_Model_Certifications_Title
Extract_Assignment_Model_Certifications_Country_Code
Extract_Assignment_Model_Certifications_State_or_Province_Code
Extract_Assignment_Model_Certifications_Status
HRT_EXT_ASG_PERSON_CERTIFICATION_UE

Extract_Assignment_Person_Certifications_Assignment_ID
Extract_Assignment_Person_Certifications_Certification_Number
Extract_Assignment_Person_Certifications_Expiration_Date
Extract_Assignment_Person_Certifications_Expired
Extract_Assignment_Person_Certifications_Name
Extract_Assignment_Person_Certifications_Person_ID
Extract_Assignment_Person_Certifications_Profile_ID
Extract_Assignment_Person_Certifications_Profile_Item_ID
Extract_Assignment_Person_Certifications_External_Party_Verified
Extract_Assignment_Person_Certifications_Educational_Establishment
Extract_Assignment_Person_Certifications_Country_Code
Extract_Assignment_Person_Certifications_Completed_Amount
Extract_Assignment_Person_Certifications_Date_To
Extract_Assignment_Person_Certifications_State_or_Province_Code
Extract_Assignment_Person_Certifications_Issued_By
Extract_Assignment_Person_Certifications_Type
Extract_Assignment_Person_Certifications_Status

When a person is no longer required for validation, you must remove them from the provider's licenses and monitoring roster.

When you run this extract in changes-only mode and you get only the person data and no child data, that means one of the following occurred:

  • The person is terminated or doesn't have any active assignments.

  • The person doesn't have any licenses to be validated.

Note: To achieve this, add extra filter criteria in level 2 to verify that only assignments that have at least 1 license for third-party verification based on position or job profile, with preference given to position profile, are identified.

The output of the extract will be in XML format.

Extract Output Mapped to the UI Attributes

Fusion Attribute Name Extract Output XML Tag Name
Person ID Extract_Person_ID
Person Number Person_Number
First Name Person_First_Name
Middle Name Person_Middle_Name
Last Name Person_Last_Name
Actual Termination Date Person_Latest_Termination_Date
Employee Email Address Person_EMail_Address
SSN National_Identifier_Number
NID Legislation Code Person_Primary_NID_Legislation_Code
NID Legislation Name Person_Primary_NID_Legislation_Name
NID Type Person_Primary_NID_Type
Date of Birth Person_Date_of_Birth
Address Line 1 Person_Address_Line1
Address Line 2 Person_Address_Line2
Address Line 3 Person_Address_Line3
Address Line 4 Person_Address_Line4
Address Region 1 Person_Address_Region1
Address Region 2 Person_Address_Region2
Address Region 3 Person_Address_Region3
Home Town or City Person_Home_Town_or_City
Postal Code Person_Postal_Code
Person Country Code Person_Country
Person Country Name Person_Country_Name
Person Phone Type Person_Phone_Type
Person Phone Number Person_Phone_Number
Assignment ID Extract_Assignment_Person_Certifications_Assignment_ID
Certification Number

Extract_Assignment_Person_Certifications_Certification_Number
Expiration Date Extract_Assignment_Person_Certifications_Expiration_Date
Expired (Transient) Extract_Assignment_Person_Certifications_Expired
Certification Name Extract_Assignment_Person_Certifications_Name
Person ID Extract_Assignment_Person_Certifications_Person_ID
Profile ID Extract_Assignment_Person_Certifications_Profile_ID
Profile Item ID Extract_Assignment_Person_Certifications_Profile_Item_ID
External Party Verified Extract_Assignment_Person_Certifications_External_Party_Verified
Educational Establishment Extract_Assignment_Person_Certifications_Educational_Establishment
Certification country code Extract_Assignment_Person_Certifications_Country_Code
Certification State Code Extract_Assignment_Person_Certifications_State_or_Province_Code
Issued By Extract_Assignment_Person_Certifications_Issued_By
Certification Type Extract_Assignment_Person_Certifications_Type
Certification Status Extract_Assignment_Person_Certifications_Status