Query to fetch Domain Name - Oracle HCM

 select external_virtual_host from ask_deployed_domains where deployed_domain_name = 'FADomain'

This will give you the current logged in instance URL of the Oracle HCM pod

Balance Feeds Query - Oracle HCM

 SELECT 

    pbt.BALANCE_NAME,
    pet.ELEMENT_NAME,
    piv.BASE_NAME         AS INPUT_VALUE_NAME,
    pbf.SCALE,
    pbf.EFFECTIVE_START_DATE,
    pbf.EFFECTIVE_END_DATE
FROM PAY_BALANCE_FEEDS_F pbf
JOIN PAY_BALANCE_TYPES_VL pbt
    ON pbf.BALANCE_TYPE_ID = pbt.BALANCE_TYPE_ID
JOIN PAY_INPUT_VALUES_F piv
    ON pbf.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
JOIN PAY_ELEMENT_TYPES_TL pet
    ON piv.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
WHERE TRUNC(SYSDATE) BETWEEN piv.EFFECTIVE_START_DATE 
                         AND piv.EFFECTIVE_END_DATE
AND pet.LANGUAGE = USERENV('LANG')
AND UPPER(pbt.BALANCE_NAME) = UPPER('XX Salary Balance')
ORDER BY pbt.BALANCE_NAME,
         pet.ELEMENT_NAME

Sample HDL for Payroll Balance Name Translation

METADATA|PayrollBalanceDefinitionTranslation|BalanceCode|Language|BalanceName|ReportingName|LegislativeDataGroupName

MERGE|PayrollBalanceDefinitionTranslation|Extended Medical Leave Entitlement Payment|E|Pago del derecho a licencia médica extendida|Pago del derecho a licencia médica extendida|PE Legislative Data Group

Balance Code would be the Base Balance Name from pay_balance_types table

Balance Name and Reporting Name would be the values which you would load in the required language

Get sysdate in fast formula

 L_SYSDATE = '4712/12/31 00:00:00' (date)

 L_SYSDATE = GET_CURRENT_DATE()

HDL Lines

SELECT TEXT

FROM fusion.hrc_dl_file_lines
WHERE data_set_bus_obj_id IN
(SELECT data_set_bus_obj_id
FROM fusion.hrc_dl_data_set_bus_objs
WHERE data_set_id IN
(SELECT data_set_id
FROM fusion.hrc_dl_data_sets
WHERE ucm_content_id = 'UCMFA15238827'))
--and (TEXT like 'METADATA%' or TEXT like '%17019%')
order by SEQ_NUM

HDL errors

Select  fl.text, ml.MSG_TEXT

 from  fusion.hrc_dl_file_lines fl
     , fusion.hrc_dl_data_set_bus_objs dsbo
     , fusion.hrc_dl_file_rows fr
     , fusion.hrc_dl_physical_lines pl
     , fusion.hrc_dl_message_lines ml
where fl.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
  and fr.line_id = fl.line_id
  and pl.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
  and fr.row_id = pl.row_id
  and dsbo.data_set_id in (
       select data_set_id
         from fusion.hrc_dl_data_sets
        where ucm_content_id = 'UCMFA15238827'
--AND ml.MSG_TEXT like '%The values Benefits and Pensions aren't valid for the attribute DeductionGroupId%'
      )
and (ml.MESSAGE_SOURCE_LINE_ID=pl.physical_line_id OR ml.MESSAGE_SOURCE_LINE_ID=fr.logical_line_id)
order by ml.MSG_TEXT

 

Query to pull skills from Job Requisition in Oracle HCM

 SELECT irc.requisition_number
      ,hpb.profile_code
      ,hpt.description profile_desc
      ,hpt.summary,
  hpi.ITEM_TEXT240_1 Job_Requisition_Skill
  FROM HRT_PROFILE_ITEMS hpi
      ,HRT_PROFILES_B hpb
      ,HRT_PROFILES_TL hpt
      ,HRT_PROFILE_RELATIONS hpr
      ,irc_requisitions_vl irc
 WHERE hpi.profile_id = hpb.profile_id
   AND hpb.profile_usage_code = 'R'
   AND hpi.profile_id = hpr.profile_id
   AND hpi.profile_id = hpt.profile_id
   AND irc.requisition_id = hpr.object_id
   AND irc.profile_id = hpt.profile_id
  and irc.requisition_number='12345'
   AND hpt.language = 'US'