if isnull(ValueSetVariable) = 'N' then
(
ValueSetVariable = 0
/* isnull()='N' means Value Set returns 'N' for NULL Value and 'Y' for any values */
)
if isnull(ValueSetVariable) = 'N' then
(
ValueSetVariable = 0
/* isnull()='N' means Value Set returns 'N' for NULL Value and 'Y' for any values */
)
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
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