Search for:
Mar 18 2022
BIP – Query to extract Rating Model data in HDL Format

You can use below query to extract Rating Model data from fusion environment for updates.

SELECT 'METADATA|RatingModel|RatingModelCode|DateFrom|DateTo|RatingName|RatingDescription|SourceSystemId|SourceSystemOwner' DATA_LINE, 1 ORDER_NUM
  FROM DUAL
UNION
select
'MERGE'
||'|'||
'RatingModel'
||'|'||
hrmb.rating_model_code
||'|'||
TO_CHAR(hrmb.date_from,'YYYY/MM/DD')
||'|'||
TO_CHAR(hrmb.date_to,'YYYY/MM/DD')
||'|'||
rtl.rating_name
||'|'||
rtl.rating_description
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner  DATA_LINE, 2 ORDER_NUM
FROM hrt_rating_models_b hrmb,
     hrt_rating_models_tl rtl,
     hrc_integration_key_map hikm
WHERE hrmb.rating_model_id = rtl.rating_model_id
  AND hikm.surrogate_id = hrmb.rating_model_id
  AND rtl.language = 'US'
ORDER BY ORDER_NUM

Save the data as RatingModel.dat before uploading to Fusion. You can make use of this query to extract data for reference or use it as a method to extract data from one instance and upload it to a different instance.

Check below post for sample RatingModel HDL file:

https://fusionhcmconsulting.com/2022/03/hdl-sample-file-to-upload-rating-model/

Mandeep Gupta 0 Comments
Mar 18 2022
HDL – Sample file to Upload Rating Model

Rating models can be uploaded in bulk using HDL. Use RatingModel business object.

Below is a sample file:

METADATA|RatingModel|RatingModelCode|DateFrom|DateTo|RatingName|RatingDescription|SourceSystemId|SourceSystemOwner
MERGE|RatingModel|INTEREST_TEST|1900/01/01|4712/12/31|Interest Rating Model - Test|Test Rating model used to indicate level of interest.|HRC_SQLLOADER_101|HRC_SQLLOADER

METADATA|RatingLevel|RatingModelCode|RatingLevelCode|CareerStrDev|RatingDescription|DateFrom|FromPoints|MaximumRatingDistribution|MinimumRatingDistribution|NumericRating|ReviewPoints|ReviewRatingDescr|RatingShortDescr|StarRating|DateTo|ToPoints|SourceSystemId|SourceSystemOwner
MERGE|RatingLevel|INTEREST_TEST|1||Undesirable|1900/01/01|0|||1|1|Undesirable|Undesirable|1|4712/12/31|0|HRC_SQLLOADER_101_1|HRC_SQLLOADER
MERGE|RatingLevel|INTEREST_TEST|2||No Preference|1900/01/01|0|||2|2|No Preference|No Preference|2|4712/12/31|0|HRC_SQLLOADER_101_2|HRC_SQLLOADER
MERGE|RatingLevel|INTEREST_TEST|3||Desirable|1900/01/01|0|||3|3|Desirable|Desirable|3|4712/12/31|0|HRC_SQLLOADER_101_3|HRC_SQLLOADER
MERGE|RatingLevel|INTEREST_TEST|4||Very desirable|1900/01/01|0|||4|4|Very desirable|Very Desirable|4|4712/12/31|0|HRC_SQLLOADER_101_4|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Feb 25 2022
HDL – Sample HDL to Update User Table value

SQL Query to extract data for a particular value of a user table:

SELECT DATA
FROM (
SELECT 'METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 
'MERGE'
||'|'||
'UserDefinedTableColumnInstance'
||'|'||
to_char(fuci.EFFECTIVE_END_DATE,'YYYY/MM/DD')
||'|'||
to_char(fuci.EFFECTIVE_START_DATE,'YYYY/MM/DD')
||'|'||
fuci.LEGISLATIVE_DATA_GROUP_ID
||'|'||
fuci.USER_COLUMN_ID
||'|'||
fuci.USER_COLUMN_INSTANCE_ID
||'|'||
fuci.USER_ROW_ID
||'|'||
fuci.Value
||'|'||
hikm.source_system_owner  
||'|'||
hikm.source_system_id
||'|'||
fuc.USER_TABLE_ID
 DATA, 2 DATA_ROW
FROM 
FF_USER_COLUMN_INSTANCES_F fuci,
FF_USER_COLUMNS fuc,
FF_USER_TABLES ft,
HRC_INTEGRATION_KEY_MAP hikm
WHERE fuci.USER_COLUMN_ID=fuc.USER_COLUMN_ID
AND fuc.USER_TABLE_ID=ft.USER_TABLE_ID
AND hikm.surrogate_id = fuci.USER_COLUMN_INSTANCE_ID
AND ft.BASE_USER_TABLE_NAME = 'TEST_WAGE'
) ORDER BY DATA_ROW

Sample HDL:

METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|LegislativeDataGroupId|UserColumnId|UserColumnInstanceId|UserRowId|Value|SourceSystemOwner|SourceSystemId|UserTableId

MERGE|UserDefinedTableColumnInstance|4712/12/31|1951/01/01|300000046974912|300000175181219|300000175188123|300000175187989|5.20|FUSION|300000175144123|300000175181333
Mandeep Gupta 0 Comments
Feb 25 2022
BIP – HDL to update Grade Rate values

SQL to extract data in HDL Format:

SELECT DATA
FROM(
SELECT 'METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount' DATA, 1 DATA_ROW
FROM DUAL
UNION ALL
SELECT 'MERGE|GradeRateValue|'
||
hikm.source_system_id 
||'|'||
hikm.source_system_owner
||'|'||
TO_CHAR(prvf.effective_start_date,'YYYY/MM/DD')
||'|'||
TO_CHAR(prvf.effective_end_date,'YYYY/MM/DD')
||'|'||
prvf.rate_id
||'|'||
pgf.grade_code
||'|'||
fssv.set_code
||'|'||
ldg.name
||'|'||
prvf.minimum
||'|'||
prvf.maximum
||'|'||
prvf.mid_value
||'|'||
prvf.value DATA, 2 DATA_ROW
FROM  per_rate_values_f prvf,
      per_legislative_data_groups_tl ldg,
      per_grades_f pgf,
      fnd_setid_sets_vl fssv,
      per_rates_f pr,
      hrc_integration_key_map hikm
where 1=1
and pr.legislative_data_group_id = ldg.legislative_data_group_id
and ldg.language = USERENV('LANG')
and trunc(sysdate) between prvf.effective_start_date and prvf.effective_end_date
and upper(prvf.rate_object_type) = 'GRADE'
and prvf.rate_object_id = pgf.grade_id
and trunc(sysdate) between pgf.effective_start_date and pgf.effective_end_date
AND   pgf.set_id=fssv.set_id
and prvf.rate_id = pr.rate_id
and trunc(sysdate) between pr.effective_start_date and pr.effective_end_date
and hikm.surrogate_id = prvf.rate_value_id

) ORDER BY DATA_ROW

Sample HDL:

METADATA|GradeRateValue|SourceSystemId|SourceSystemOwner|EffectiveStartDate|EffectiveEndDate|RateId|GradeCode|SetCode|LegislativeDataGroup|MinimumAmount|MaximumAmount|MidValueAmount|ValueAmount

MERGE|GradeRateValue|300000107518119|FUSION|1951/01/01|4712/12/31|300000106295381|ADMIN05|COMMON|KZ Legislative Data Group|870|962|913.5|
Mandeep Gupta 0 Comments
Feb 25 2022
BIP – Query to extract Person Address Details
select

distinct

PAPF.PERSON_NUMBER,

PAPF.PERSON_ID,

PPAUF.PERSON_ADDR_USAGE_ID "ADDRESS_ID",

TO_CHAR(PA.EFFECTIVE_START_DATE,'YYYY/MM/DD')"EFFECTIVE_START_DATE",

TO_CHAR(PA.EFFECTIVE_END_DATE,'YYYY/MM/DD')"EFFECTIVE_END_DATE",

PPAUF.ADDRESS_TYPE,

PA.ADDRESS_LINE_1,

PA.ADDRESS_LINE_2,

PA.ADDRESS_LINE_3

from

PER_ADDRESSES_F PA,

PER_ALL_PEOPLE_F PAPF,

PER_PERSON_ADDR_USAGES_F PPAUF

where

  PAPF.PERSON_ID = PPAUF.PERSON_ID(+)

AND PPAUF.ADDRESS_ID = PA.ADDRESS_ID(+)
Mandeep Gupta 0 Comments
Jan 25 2022
HDL – Sample file to update user name to email address
SELECT 'METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner' Headerrow, 1 dataorder
FROM DUAL
UNION
SELECT 'METADATA|User'
      ||'|'||papf.person_number 
      ||'|'||pea.email_address
      ||'|'||hikm.source_system_id
      ||'|'||hikm.source_system_owner Headerrow, 2 dataorder
  FROM per_all_people_f papf
      ,per_email_addresses pea
	  ,per_users pu
	  ,hrc_integration_key_map hikm
 WHERE papf.person_id = pea.person_id
   and pu.person_id = pea.person_id
   and pu.user_id = hikm.surrogate_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
order by dataorder

Sample File:

METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner
MERGE|User|11101|[email protected]|300000120863888|FUSION
Mandeep Gupta 1 Comments
Jan 25 2022
HDL – Query to generate worker email in HDL format
select 'METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner'
  from dual
UNION ALL
select 'MERGE|PersonEmail' 						||'|'||												
       NULL 									||'|'||										
       papf.person_id							||'|'||								
       to_char(ppos.date_start,'RRRR/MM/DD')	||'|'||					    
       'W1' 									||'|'||				
	   'Y'													||'|'||		
       'sendmail-discard_'||papf.person_number||'@xyz.com' 	||'|'||
      'PER_EMAIL_'||papf.person_number						||'|'||
      'HRC_SQLLOADER' 												
  from per_all_people_f papf
      ,per_periods_of_service ppos
 where 1=1
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and papf.person_id = ppos.person_id
   and ppos.date_start = (select MAX(ppos2.date_start) from per_periods_of_service ppos2
                           where ppos2.person_id = ppos.person_id)

Sample HDL File:

METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner
MERGE|PersonEmail||100000015400564|2021/03/15|W1|Y|[email protected]|PER_EMAIL_00603|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Dec 19 2021
HDL – Sample HDL to load rehire Workers

Below is the sample file (tested on an older version) to load rehire worker records but the structure should remain same. Few attributes may be deprecated now, so you may get an error. Please remove those and it should work fine.

METADATA|Worker|PersonId|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|CountryOfBirth|TownOfBirth|CategoryCode|ActionCode|ReasonCode|GUID|SourceSystemOwner|SourceSystemId

MERGE|Worker||2002/10/07|4712/12/31|10090|2002/10/07|1976/01/02||||HIRE|||HRC_SQLLOADER|1190_PERSON



METADATA|PersonName|PersonNameId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|PersonNumber|LegislationCode|NameType|FirstName|MiddleNames|LastName|Honors|KnownAs|PreNameAdjunct|Suffix|MilitaryRank|PreviousLastName|Title|SourceSystemOwner|SourceSystemId

MERGE|PersonName||2002/10/07|4712/12/31|1190_PERSON||US|GLOBAL|Steven|Flord|Smith|||||||MR.|HRC_SQLLOADER|1190_PERSON_NAME


METADATA|WorkRelationship|PersonNumber|LegalEntityId|DateStart|PersonId(SourceSystemId)|PrimaryFlag|LegalEmployerName|ActionCode|WorkerType|ProjectedTerminationDate|ActualTerminationDate|SourceSystemOwner|SourceSystemId

MERGE|WorkRelationship|10090||2002/10/07|1190_PERSON|Y|ABC Limited|HIRE|E||2008/12/07|HRC_SQLLOADER|283_WORK_RELATIONSHIP

MERGE|WorkRelationship|10090||2015/02/02|1190_PERSON|Y|ABC Limited|REHIRE|E|||HRC_SQLLOADER|101062_WORK_RELATIONSHIP



METADATA|WorkTerms|AssignmentId|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PersonNumber|LegalEmployerName|DateStart|AssignmentName|AssignmentStatusTypeCode|AssignmentType|BusinessUnitShortCode|PositionOverrideFlag|PrimaryWorkTermsFlag|ActionCode|WorkerType|PersonTypeCode|SystemPersonType|SourceSystemOwner|SourceSystemId

MERGE|WorkTerms||ET10090|2002/10/07|2008/12/07|1|Y|283_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2002/10/07||ACTIVE_PROCESS|ET|ABC Business Unit|||HIRE|E|Employee|EMP|HRC_SQLLOADER|283_WORK_TERM

MERGE|WorkTerms||ET10090|2008/12/08|4712/12/31|1|Y|283_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2002/10/07||INACTIVE_PROCESS|ET|ABC Business Unit|||TERMINATION||||HRC_SQLLOADER|283_WORK_TERM

MERGE|WorkTerms||ET10090-2|2015/02/02|2017/06/30|1|Y|101062_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2015/02/02||ACTIVE_PROCESS|ET|ABC Business Unit|||ASG_CHANGE|E|Employee|EMP|HRC_SQLLOADER|12608_WORK_TERM

MERGE|WorkTerms||ET10090-2|2017/07/01|4712/12/31|1|Y|101062_WORK_RELATIONSHIP|1190_PERSON||ABC Limited|2015/02/02||ACTIVE_PROCESS|ET|ABC Business Unit|||ASG_CHANGE|E|Employee|EMP|HRC_SQLLOADER|12608_WORK_TERM



METADATA|Assignment|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|WorkTermsAssignmentId(SourceSystemId)|WorkTermsNumber|AssignmentType|AssignmentName|AssignmentNumber|AssignmentStatusTypeCode|BusinessUnitShortCode|DateProbationEnd|WorkerCategory|AssignmentCategory|PermanentTemporary|FullPartTime|InternalFloor|GradeCode|JobCode|LocationCode|NormalHours|Frequency|DepartmentName|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PositionCode|PrimaryAssignmentFlag|PrimaryFlag|PersonTypeCode|SystemPersonType|EstablishmentId|NoticePeriod|NoticePeriodUOM|ProbationPeriod|ProbationUnit|HourlySalariedCode|LabourUnionMemberFlag|ManagerFlag|EndTime|StartTime|WorkAtHomeFlag|SourceSystemOwner|SourceSystemId

MERGE|Assignment|HIRE|2002/10/07|2008/12/07|1|Y|283_WORK_TERM||E||E10090|ACTIVE_PROCESS|ABC Business Unit||||||||||||ABC Limited|283_WORK_RELATIONSHIP|1190_PERSON||Y|Y|Employee|EMP||||||||||||HRC_SQLLOADER|283_ASSIGNMENT

MERGE|Assignment|TERMINATION|2008/12/08|4712/12/31|1|Y|283_WORK_TERM||E||E10090|INACTIVE_PROCESS|ABC Business Unit|||||||72|128|343|||ABC Maintenance|283_WORK_RELATIONSHIP|1190_PERSON||Y|Y||||||||||||||HRC_SQLLOADER|283_ASSIGNMENT

MERGE|Assignment|REHIRE|2015/02/02|2017/06/30|1|Y|12608_WORK_TERM||E||E10090-2|ACTIVE_PROCESS|ABC Business Unit|||||||67|173|142|||ABC Department|101062_WORK_RELATIONSHIP|1190_PERSON|165061|Y|Y|Employee|EMP|||||||N|N||||HRC_SQLLOADER|12608_ASSIGNMENT

MERGE|Assignment|ASG_CHANGE|2017/07/01|4712/12/31|1|Y|12608_WORK_TERM||E||E10090-2|ACTIVE_PROCESS|ABC Business Unit|||||||67|173|142||ABC Department|101062_WORK_RELATIONSHIP|1190_PERSON|627106|Y|Y|Employee|EMP|||||||N|N||||HRC_SQLLOADER|12608_ASSIGNMENT



METADATA|PersonLegislativeData|PersonLegislativeId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|LegislationCode|HighestEducationLevel|MaritalStatus|MaritalStatusDate|Sex|GUID|SourceSystemOwner|SourceSystemId

MERGE|PersonLegislativeData||2002/10/07|4712/12/31|1190_PERSON|US||M||M||HRC_SQLLOADER|1190_PERSON_LEGISLATIVE
Mandeep Gupta 0 Comments
Nov 25 2021
HDL – Sample file for Dependent Enrollments
METADATA|DependentEnrollment|PersonNumber|ParticipantLastName|ParticipantFirstName|BenefitRelationship|LifeEvent|LifeEventOccuredDate|CloseLifeEvent|CloseLifeEventDate|EffectiveDate|LegalEmployer

MERGE|DependentEnrollment|22211|Singh|Manu|DFLT|New Hire|2021/01/01|Y|2021/01/01|2021/01/01|XYZ Legal Employer



METADATA|DesignateDependent|PersonNumber|Plan|Program|Option|OriginalEnrollmentDate|DependentPersonNumber|LineNumber|DependentFirstName|DependentLastName|DependentDateOfBirth
MERGE|DesignateDependent|22211|Benefit Plan 1|Benefit Program|Employee Only|2020/01/01|122211|1|Singh|Test 1|1992/01/31
MERGE|DesignateDependent|22211|Benefit Plan 1|Benefit Program|Employee Only|2020/01/01|122212|2|Singh|Test 2|2015/01/01
MERGE|DesignateDependent|22211|Benefit Plan 1|Benefit Program|Employee Only|2020/01/01|122213|3|Singh|Test 3|2017/01/05
Mandeep Gupta 0 Comments
Nov 20 2021
HDL – Sample file to delete element eligibility

HDL supports deletion of element eligibilities. You can make use of PayrollElementDefinition business object. Child object ElementEligibility supports create, update and delete operations.

You can create a simple BIP query to extract the requisite data and create an HDL file out of it:

select pelf.element_link_id
      ,petf.element_type_id
      ,petf.base_element_name
      ,pelf.effective_start_date 
  from pay_element_types_f petf
     , pay_element_links_f pelf
 where petf.element_type_id = pelf.element_type_id
   and element_link_id=300000175215375   
   and TRUNC(sysdate) BETWEEN petf.effective_start_date and petf.effective_end_date

Sample HDL file:

METADATA|ElementEligibility|ElementLinkId|ElementId|EffectiveStartDate
DELETE|ElementEligibility|300000175215375|300000175200627|1951/01/01

Sometime you may get an error that You can’t delete element eligibility as element entries exist for this eligibility. In this case first you need to identify the corresponding element entries and Delete those and then retry deleting element eligibility.

Please check mos note – When Attempting to Delete Element Eligibility Get Error ‘The element eligibility record can’t be deleted because it would invalidate existing element entries with effective start dates in the future. (PAY-1635756)’ (Doc ID 2686914.1) for sample query and sample file for element entry deletion.

Version Validated : 21C

Mandeep Gupta 0 Comments
Nov 5 2021
BIP – Query to find Workers with missing Payrolls
SELECT papf.person_number 
      ,ppnf.full_name
      ,paam.assignment_number
  FROM per_all_people_f papf,
       per_person_names_f ppnf,
       per_all_assignments_m paam
 WHERE paam.person_id = ppnf.person_id
   AND papf.person_id = ppnf.person_id
   AND papf.person_id = paam.person_id
   AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND paam.effective_latest_change = 'Y'
   AND paam.primary_flag = 'Y'
   AND paam.assignment_type ='E'
   AND paam.assignment_status_type = 'ACTIVE' 
   AND ppnf.name_type = 'GLOBAL'
   AND NOT EXISTS (SELECT 1
                     FROM pay_rel_groups_dn prgd
					     ,pay_pay_relationships_dn prrd 
						 ,pay_assigned_payrolls_dn papd
	                     ,pay_payroll_terms ppt
					WHERE prgd.assignment_id = paam.assignment_id
					  AND prrd.payroll_relationship_id = prgd.payroll_relationship_id
					  AND papd.payroll_term_id = ppt.payroll_term_id
					  AND ppt.payroll_relationship_id = prrd.payroll_relationship_id) 
ORDER BY 1,2
Mandeep Gupta 0 Comments
Nov 5 2021
BIP – Report to get basic employee details
SELECT papf.person_number 
      ,ppnf.first_name
	  ,ppnf.middle_names
      ,ppnf.last_name
      ,ppnf.title
      ,pjft.name job_name
      ,hauft.name department_name
      ,haouf.attribute1 	  
      ,haouf.attribute2 	  
      ,haouf.attribute3 	  
      ,haouf.attribute4 	  
      ,to_char(ppos.date_start,'DD-MM-RRRR', 'nls_date_language=American') date_of_joining
      ,to_char(pp.date_of_birth,'DD-MM-RRRR', 'nls_date_language=American') date_of_birth
  FROM per_all_people_f papf,
       per_person_names_f ppnf,
       per_all_assignments_m paam,
       per_jobs_f_tl pjft,
       per_periods_of_service ppos,
       per_persons pp,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE paam.person_id = ppnf.person_id
   AND papf.person_id = ppnf.person_id
   AND papf.person_id = paam.person_id
   AND paam.job_id = pjft.job_id (+)
   AND pjft.language = 'US'
   AND hauft.language = 'US'
   AND paam.period_of_service_id  = ppos.period_of_service_id
   AND paam.person_id = pp.person_id 
   AND haouf.organization_id = paam.organization_id
   AND haouf.organization_id = hauft.organization_id 
   AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
   AND trunc(sysdate) BETWEEN haouf.effective_start_date AND haouf.effective_end_date
   AND trunc(sysdate) BETWEEN hauft.effective_start_date AND hauft.effective_end_date
   AND trunc(sysdate) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
   AND paam.effective_latest_change = 'Y'
   AND paam.primary_flag = 'Y'
   AND paam.assignment_type ='E'
   AND paam.assignment_status_type = 'ACTIVE' 
   AND ppnf.name_type = 'GLOBAL'
ORDER BY 1,2
Mandeep Gupta 0 Comments
Jan 2 2021
HRHD – Generate list of published changes

In HR Helpdesk module, generally a lot of customizations are done i.e. adding a new field on standard screen, new triggers etc. Once the solution is finalized, a report can be generated which will list down all the customizations. This is an Out of the box functionality provided by Fusion applications.

Navigate to Home -> Configuration -> Application Composer -> Metadata Manager

Click on generate button:

Choose the objects and Metadata types to be included in report:

Click on generate button and wait while the configuration report is getting generated.

Once the report is generated, click on Export to generate the report in excel or HTML format.

Mandeep Gupta 0 Comments
Dec 31 2020
Reports (BIP) – Customizing a standard BIP report

Many a times, consultants are required to modify the standard BIP reports. This is very much applicable in case of workflow notifications which are based on BIP reports. Both data model as well report can be customized to include/exclude any additional fields.

Follow the below steps:

  1. Login to your pod using below url:

https://xxzzaa.fa.bb.oraclecloud.com/xmlpserver/

(note the xmlpserver in the end).

2. Then navigate to /Shared Folders/Human Capital Management/Folder

3. Click on More and you will see Customize option. Click on Customize:

This will create a new report under:

/Shared Folders/Custom/Human Capital Management/Folder (notice the change in path).

You can upload your customized templates here.

Click on edit and choose the data model from standard path.

Similar approach will be followed for data model customizations as well.

In order to display the changes asap, please configure the below profile option for global scope:

BIP_CLIENT_REFRESH_TIME – Oracle Middleware Extensions for Applications – Application Core

Provide a value of 15 min.

Check below document for more details:

https://docs.oracle.com/en/cloud/saas/applications-common/20b/facia/approval-management.html#FACIA3819777

Mandeep Gupta 0 Comments
Dec 30 2020
HCM Extract – Query to get last extract run date

While creating an HCM extract based on a BIP report, there is a common requirement where the BIP should retrieve “Changed Only” data. This can be achieved using a BIP report data model which is based on globalReportsDataModel (/shared/Human Capital Management/Payroll/Data Model).

Use the below query in the data set to extract last run date of HCM extract and based on this date create a filter on last_update_date of the standard table from which we are extracting the data.

WITH pLastRunDate AS
(SELECT MAX(pfi.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM pay_flows pf
,pay_flow_instances pfi
WHERE 1=1
AND pf.base_flow_id = pfi.base_flow_id
AND pfi.LAST_UPDATE_DATE < sysdate
AND pf.base_flow_name ='XX Custom Integration Extract' -- - give the name of your HCM extract
AND pfi.status = 'COMPLETED'
)
Mandeep Gupta 0 Comments