Search for:
Oct 6 2022
BIP – Query to extract DOR attachment name
select papf.person_number 
      ,hdor.document_code
      ,hdor.document_name
      ,hdor.document_number
      ,fdt.file_name
      ,fdt.dm_version_number document_id
      ,fdt.dm_document_id UCM_file
 from fnd_attached_documents fad, hr_documents_of_record hdor, fnd_documents_tl fdt
   ,per_all_people_f papf
   ,hr_document_types_tl hdtt
 where ENTITY_NAME = 'HR_DOCUMENTS_OF_RECORD'
  and hdor.documents_of_record_id = fad.PK1_VALUE
  and fad.document_id = fdt.document_id
  and fdt.language = 'US'
  and hdor.person_id = papf.person_id
  and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  and hdor.document_type_id = hdtt.document_type_id
  and hdtt.language = 'US'
  and hdtt.DOCUMENT_TYPE = 'Test Doc'
Mandeep Gupta 0 Comments
Oct 6 2022
HDL – Sample HDL to load Non Catalog Learning items
METADATA|NoncatalogLearningItem|LearningItemId|EffectiveStartDate|EffectiveEndDate|LearningItemNumber|Title|Description|Duration|Price|CurrencyCode|OwnedByPersonId|OwnedByPersonNumber|NoncatalogURL|SourceId|SourceInfo|SourceType|SourceSystemId|SourceSystemOwner

MERGE|NoncatalogLearningItem||2022/05/01||CLASS_EXT_128|External Excel Course||||||12345||1231|CLASS_EXT_128|CLASS_EXT|CLASS_EXT_128|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Oct 6 2022
HDL – Mask Candidate email addresses

Post P2T, there is always a requirement to mask the candidate email addresses in lower environments. Below SQL can be used to generate data masked email data in HDL format:

SELECT 'METADATA|CandidateEmail|EmailAddressId|PersonId|CandidateNumber|EmailAddress|DateFrom|DateTo|SourceSystemOwner|SourceSystemId' "Record"

 FROM DUAL

UNION

SELECT DISTINCT 'MERGE'    || '|'||

'CandidateEmail'        || '|'||

ICEA.EMAIL_ADDRESS_ID          || '|'||

ICEA.PERSON_ID             || '|'||

ICEA.CANDIDATE_NUMBER          || '|'||

replace(PEAD.EMAIL_ADDRESS,'.com','.invalidcomx')           || '|'||

TO_CHAR(PEAD.DATE_FROM,'YYYY/MM/DD')  || '|'||

TO_CHAR(PEAD.DATE_TO,'YYYY/MM/DD')   || '|'||

HIKM.SOURCE_SYSTEM_OWNER      || '|'||

    HIKM.SOURCE_SYSTEM_ID      "Record"

FROM IRC_CAND_EMAIL_ADDRESS_V ICEA,

PER_EMAIL_ADDRESSES PEAD,

HRC_INTEGRATION_KEY_MAP HIKM

WHERE ICEA.EMAIL_ADDRESS_ID = PEAD.EMAIL_ADDRESS_ID

  AND HIKM.SURROGATE_ID = PEAD.EMAIL_ADDRESS_ID

You can update the email address as per your requirement.

Mandeep Gupta 0 Comments
Oct 6 2022
HDL – Sample HDL file to upload DoR for a specific country
METADATA|DocumentsOfRecord|PersonNumber|Country|DocumentType|DocumentCode|DateFrom|DateTo

MERGE|DocumentsOfRecord|78652|United Kingdom|UK Vehicle Information|OVERTIME_AVERAGING_AGREEMENT_2020-10-01|2020/10/01|2022/10/01



METADATA|DocumentAttachment|PersonNumber|Country|DocumentType|DocumentCode|DataTypeCode|Title|URLorTextorFileName|File

MERGE|DocumentAttachment|78652|United Kingdom|UK Vehicle Information|OVERTIME_AVERAGING_AGREEMENT_2020-10-01|FILE|DD test document of record for HDL.pdf|DD test document of record for HDL.pdf|DD test document of record for HDL.pdf

Please note that under attribute country, you will need to pass the full country name. Passing country code like GB, will throw below error:

The values GB aren’t valid for the attribute LegislationCode.

Mandeep Gupta 0 Comments
Oct 6 2022
BIP – Query to extract assignment rules at course level in Oracle Learning Cloud

Use the below SQL to extract assignment rules defined at course level:

SELECT wlift.NAME

   ,war.DEFAULT_INITIAL_REC_SUBSTA

   ,war.DEFAULT_INITIAL_ACT_SCORE

   ,war.DEFAULT_INITIAL_COMMENTS

   ,war.DEFAULT_INITIAL_COMPL_DATE

   ,war.DEFAULT_INITIAL_TOTACT_EFF

   ,war.DEFAULT_INITIAL_REASON_CODE

   ,war.INITIAL_DUE_DATE_OPTION

   ,war.INITIAL_DUE_DATE

   ,war.INITIAL_DUE_IN_DAYS

   ,war.VALIDITY_OPTION

   ,war.EXPIRY_OPTION

   ,war.EXPIRY_IN_DAYS

   ,war.EXPIRY_DATE

   ,war.EXPIRY_IN_NUM_YRS

   ,war.RENEWAL_OPTIONS

   ,war.RENEWAL_BEFORE_EXPIRY_DAYS

 FROM WLF_ASSIGNMENT_RULES war

   ,WLF_LEARNING_ITEMS_F_TL wlift

   ,WLF_LEARNING_ITEMS_F wlif

 WHERE war.ASSIGNMENT_RULE_ID = wlif.ASSIGNMENT_RULE_ID

  AND wlif.LEARNING_ITEM_ID = wlift.LEARNING_ITEM_ID

  AND wlift.language = 'US'

  AND TRUNC(sysdate) BETWEEN wlif.effective_start_date AND wlif.effective_end_date

  AND TRUNC(sysdate) BETWEEN wlift.effective_start_date AND wlift.effective_end_date
Mandeep Gupta 0 Comments
Oct 6 2022
HDL – Sample HDL to delete schedule assignment data

Below HDL file can be used to DELETE the schedule assignment data in a Fusion environment:

METADATA|ScheduleAssignment|ResourceType|ScheduleAssignmentId|SourceSystemId|SourceSystemOwner
DELETE|ScheduleAssignment|ASSIGN|300000117113937|SCHEDULE_E1000001_30-MAY-2017-31-DEC-2017_0-8-8-8-8-0-0|EBS
DELETE|ScheduleAssignment|ASSIGN|300000117309993|SCHEDULE_E1000002_04-JUN-2018-31-DEC-4712_0-8-8-8-8-0-0|EBS

This data can be extracted from Fusion HCM using a simple BIP query:

SELECT DATA_ROW
FROM (
SELECT 'METADATA|ScheduleAssignment|ResourceType|ScheduleAssignmentId|SourceSystemId|SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE|ScheduleAssignment|',
	   RESOURCE_TYPE,
       SCHEDULE_ASSIGNMENT_ID,
       source_system_id,
       source_system_owner   AS DATA_ROW
  from fusion.per_schedule_assignments psa
      ,hrc_integration_key_map hikm
 where psa.SCHEDULE_ASSIGNMENT_ID = hikm.surrogate_id
)
Mandeep Gupta 0 Comments
Oct 6 2022
HDL – Delete Person Email addresses

Use below SQL query to extract email addresses data in HDL format to DELETE the data:

SELECT DATA_ROW
FROM (
SELECT 'METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|DateTo|EmailType|EmailAddress|SourceSystemId|SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
       'PersonEmail' ||'|'||
       pea.email_address_id||'|'||
       pea.person_id ||'|'||
       to_char(pea.date_from,'RRRR/MM/DD')||'|'||
       to_char(pea.date_to,'RRRR/MM/DD') ||'|'||
       pea.email_type ||'|'||
       pea.email_address ||'|'||
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) ||'|'||
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE pea.email_Address_id = email_hrc.surrogate_id) AS DATA_ROW
  from per_email_addresses pea
)

If you only want to update the email addresses not delete them, then use refer below post:

HDL – Email Data Obfuscation in Test environment – Welcome to Fusion HCM Consulting

Mandeep Gupta 0 Comments
Oct 6 2022
HDL – Delete Element Entries Data

Query to extract Element entry details data in HDL format:

SELECT DATA_ROW
FROM (
SELECT 'METADATA'
|| CHR (124) || 'ElementEntry'
|| CHR (124) || 'ElementEntryId'
|| CHR (124) || 'EffectiveStartDate'
|| CHR (124) || 'SourceSystemId' 
|| CHR (124) || 'SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select  'DELETE'
|| CHR (124) || 'ElementEntry'
|| CHR (124) || peef.element_entry_id
|| CHR (124) || to_char(peef.effective_start_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
FROM PAY_ELEMENT_ENTRIES_F peef,
	 HRC_INTEGRATION_KEY_MAP hikm
WHERE hikm.surrogate_id = peef.element_entry_id

)
Mandeep Gupta 0 Comments