Wednesday, 6 March 2024

How to Handle NULL value in Fast Formula(Returned From Value Set)

 if isnull(ValueSetVariable) = 'N' then 

  (

ValueSetVariable = 0

/* isnull()='N' means Value Set returns 'N' for NULL Value and 'Y' for any values */

  )

Monday, 31 December 2018

Employee Element Entry Query - Fusion Payroll

select
   papf.person_number empnumber,
   ppnf.full_name empname,
   petf.base_element_name salaryelement,
   pivf.base_name inputvalue,
   peevf.screen_entry_value entryvalue,
   to_char(peevf.effective_start_date, 'DD-MON-YYYY') elementstartdate,
   to_char(peevf.effective_end_date, 'DD-MON-YYYY') elementenddate 
from
   pay_element_entries_f peef,
   pay_element_entry_values_f peevf,
   pay_element_types_f petf,
   pay_input_values_f pivf,
   per_all_people_f papf,
   per_all_assignments_f paaf,
   per_person_names_f ppnf 
where
   1 = 1 
   and peef.element_entry_id = peevf.element_entry_id 
   and peef.element_type_id = petf.element_type_id 
   and peevf.element_entry_id = peef.element_entry_id 
   and pivf.input_value_id = peevf.input_value_id 
   and papf.person_id = peef.person_id 
   and papf.person_id = ppnf.person_id 
   and paaf.person_id = papf.person_id 
   and paaf.primary_flag = 'Y' 
   and paaf.assignment_type = 'E' 
   and ppnf.name_type = 'GLOBAL' 
   and pivf.element_type_id = petf.element_type_id 
   and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date) 
   and trunc(sysdate) between trunc(paaf.effective_start_date) and trunc(paaf.effective_end_date) --and petf.base_element_name like ('Basic%') 
order by
   petf.base_element_name,
   papf.person_number asc

Thursday, 6 December 2018

Retrieve Element List - Fusion Payroll

Select
   PETF.BASE_ELEMENT_NAME ElementName,
   PEC.BASE_CLASSIFICATION_NAME Classification,
   PETF.PROCESSING_TYPE PTYPE,
   PELF.ELEMENT_LINK_NAME LinkName 
from
   PAY_ELEMENT_TYPES_F PETF,
   PAY_ELE_CLASSIFICATIONS PEC,
   PAY_ELEMENT_LINKS_F PELF 
where
   PETF.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID 
   AND PETF.ELEMENT_TYPE_ID = PELF.ELEMENT_TYPE_ID 
order by
   PEC.BASE_CLASSIFICATION_NAME,
   PETF.BASE_ELEMENT_NAME asc

Sunday, 11 November 2018

Current Employee List - Fusion HCM

SELECT
   PAPF.PERSON_ID,
   PAAF.ASSIGNMENT_ID ASSIGNMENT_ID,
   PAAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER,
   PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
   PPNF.FULL_NAME EMPLOYEE_NAME,
   PAPF.START_DATE HIRE_DATE,
   HLE.NAME LEGAL_EMPLOYER,
   FAB.BU_NAME BU_NAME,
   PAAF.ASSIGNMENT_STATUS_TYPE STATUS 
FROM
   PER_ALL_PEOPLE_F PAPF,
   PER_PERSON_NAMES_F PPNF,
   PER_ALL_ASSIGNMENTS_F PAAF,
   HR_LEGAL_ENTITIES HLE,
   FUN_ALL_BUSINESS_UNITS_V FAB 
WHERE
   PAPF.PERSON_ID = PAAF.PERSON_ID 
   AND PAPF.PERSON_ID = PPNF.PERSON_ID 
   AND PAAF.BUSINESS_UNIT_ID = FAB.BU_ID 
   AND PAAF.PRIMARY_FLAG = 'Y' 
   AND PAAF.ASSIGNMENT_TYPE = 'E' 
   AND PPNF.NAME_TYPE = 'GLOBAL' 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE) 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE) 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(PPNF.EFFECTIVE_START_DATE) AND TRUNC(PPNF.EFFECTIVE_END_DATE) 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(HLE.EFFECTIVE_START_DATE) AND TRUNC(HLE.EFFECTIVE_END_DATE) 
   AND PAAF.LEGAL_ENTITY_ID = HLE.ORGANIZATION_ID 
   AND HLE.CLASSIFICATION_CODE = 'HCM_PSU' 
   AND PAAF.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' 
ORDER BY
   4

Thursday, 13 September 2018

Retrieve Payroll Results in Oracle Fusion Payroll

Select
   ppa.effective_date PayrollDate,
   papf.Person_Number PersonNumber,
   PETF.BASE_ELEMENT_NAME ElementName,
   prrv.RESULT_VALUE PayAmount 
from
   PAY_RUN_RESULTS prr,
   PAY_RUN_RESULT_VALUES prrv,
   PAY_ELEMENT_TYPES_F PETF,
   PAY_ELE_CLASSIFICATIONS PEC,
   PAY_INPUT_VALUES_F pivf,
   PAY_PAYROLL_REL_ACTIONS PRREA,
   PAY_PAYROLL_ACTIONS PPA,
   PAY_PAY_RELATIONSHIPS_DN PPRDN,
   PAY_ALL_PAYROLLS_F papsf,
   per_all_people_f papf 
where
   prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID( + ) 
   and prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID 
   AND PETF.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID 
   and pivf.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID 
   and PRREA.RETRO_COMPONENT_ID is Null
   and pivf.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID 
   and pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID( + ) 
   AND pivf.BASE_NAME = 'Pay Value'  --and papsf.PAYROLL_NAME like '%Junior%'
   and PRREA.PAYROLL_REL_ACTION_ID = PRR.PAYROLL_REL_ACTION_ID 
   and PRREA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID 
   and PPA.ACTION_TYPE in 
   (
      'R',
      'Q'
   )
   and PRREA.PAYROLL_RELATIONSHIP_ID = PPRDN.PAYROLL_RELATIONSHIP_ID 
   and papsf.PAYROLL_ID = ppa.PAYROLL_ID 
   and PPRDN.PERSON_ID = Papf.PERSON_ID 
   AND ppa.date_earned BETWEEN pivf.effective_start_date AND pivf.effective_end_date 
   AND ppa.date_earned BETWEEN petf.effective_start_date AND petf.effective_end_date 
   AND ppa.date_earned BETWEEN papsf.effective_start_date AND papsf.effective_end_date 
   AND ppa.date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date 
   and TO_CHAR (ppa.effective_date, 'DD-MM-YYYY') = '31-08-2018'  --and papf.Person_Number = ''
order by
   papf.Person_Number asc