SELECT
PERSON_NUMBER,
assignment_number,
Date_of_Joining,
grade,
Job,
Position,
Department,
BU_Name,
payroll_name,
PERIOD_NAME,
DEFAULT_PAYDATE,
Order_date,
Amount,
entitle_amount,
child_name_age,
child_school_type,
child_academic_year,
element_name,
base_element_name,
reporting_name
FROM (
SELECT
(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME = 'Pay Value'
AND pivf.uom = 'M'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)aMOUNT
,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)entitle_amount
,((select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)||'_'||SUBSTR(pett.element_name,20))child_name_age
,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)child_school_type
,(SELECT MEANING FROM HCM_LOOKUPS WHERE LOOKUP_TYPE = 'XXXXXXXXX' AND LOOKUP_CODE = (select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID))child_academic_year
,ptp.PERIOD_NAME
,TO_CHAR(ptp.DEFAULT_PAYDATE,'DD-Mon-YYYY') DEFAULT_PAYDATE
,Trim(DEFAULT_PAYDATE) Order_date
,petf.base_element_name
,petT.reporting_name
,to_char(PAPF.PERSON_NUMBER) PERSON_NUMBER
,to_char(ASG.assignment_number) assignment_number
,To_char(papf.start_date,'DD-Mon-YYYY') Date_of_Joining
,peTt.element_name
,pgft.name grade
,pjft.name Job
,hapft.name Position
,pd.name Department
,haou.name BU_Name
,pay.PAYROLL_NAME
FROM
PAY_PAYROLL_REL_ACTIONS ppra
,PAY_PAYROLL_ACTIONS ppa
,PAY_PAY_RELATIONSHIPS_DN pprd
,PAY_REQUESTS pr
,PAY_FLOW_TASK_INSTANCES pfti
,PER_ALL_ASSIGNMENTS_M asg
,PAY_TIME_PERIODS ptp
,PAY_ELEMENT_TYPES_F petf
,PAY_ELEMENT_TYPES_TL pett
,PAY_ELE_CLASSIFICATIONS pec
,PAY_ELE_CLASSIFICATIONS pec1
,PAY_ELE_CLASSIFICATIONS_TL pect
,PAY_TIME_DEFINITIONS ptd
,PAY_TIME_DEFINITIONS ptd1
,PER_LEGISLATIVE_DATA_GROUPS pldg
,pay_all_payrolls_f pay
,per_all_people_f papf
,per_grades_f pgf
,per_grades_f_tl pgft
,per_jobs_f_tl pjft
,hr_all_positions_f_tl hapft
,per_departments PD
,hr_all_organization_units haou
,pay_element_entries_f peef
WHERE 1=1
AND ppra.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
AND ppra.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
AND ppa.PAY_REQUEST_ID = pr.PAY_REQUEST_ID(+)
AND pr.FLOW_TASK_INSTANCE_ID = pfti.FLOW_TASK_INSTANCE_ID(+)
AND asg.PERSON_ID = pprd.PERSON_ID
AND asg.PRIMARY_FLAG = 'Y'
AND asg.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL
AND ppa.EFFECTIVE_DATE BETWEEN asg.effective_start_Date AND asg.effective_end_date
AND asg.assignment_status_type <> 'INACTIVE'
AND ppra.RETRO_COMPONENT_ID IS NULL
AND ppa.EARN_TIME_PERIOD_ID = ptp.TIME_PERIOD_ID(+)
AND petf.ELEMENT_TYPE_ID = pett.ELEMENT_TYPE_ID(+)
AND (USERENV('LANG')) = pett.LANGUAGE (+)
AND petf.CLASSIFICATION_ID = pec1.CLASSIFICATION_ID
AND pec1.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID
AND petf.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID
AND pec.CLASSIFICATION_ID = pect.CLASSIFICATION_ID(+)
AND (USERENV('LANG')) = pect.LANGUAGE (+)
AND petf.STARTING_TIME_DEF_ID = ptd.TIME_DEFINITION_ID(+)
AND petf.ENDING_TIME_DEF_ID = ptd1.TIME_DEFINITION_ID(+)
AND pec.LEGISLATION_CODE = NVL(petf.LEGISLATION_CODE, pldg.LEGISLATION_CODE)
AND petf.LEGISLATIVE_DATA_GROUP_ID = pldg.LEGISLATIVE_DATA_GROUP_ID(+)
AND sysdate BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE
AND pett.ELEMENT_NAME IN (
'XXXXXXXXXXX'
)
AND pay.payroll_id = ptp.payroll_id(+)
AND pay.payroll_id = ppa.payroll_id(+)
AND ASG.person_id = papf.person_id
--AND papf.person_id = ppnf.person_id
AND pgf.grade_id = ASG.grade_id
AND pgft.grade_id = pgf.grade_id
AND pgft.LANGUAGE = 'US'
AND pjft.job_id(+) = ASG.job_id
AND pjft.LANGUAGE = 'US'
AND hapft.position_id(+) = ASG.position_id
AND hapft.LANGUAGE = 'US'
AND ASG.organization_id = pd.organization_id(+)
AND haou.organization_id(+) = ASG.business_unit_id
AND sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between hapft.effective_start_date and hapft.effective_end_date
AND ASG.assignment_type IN ( 'E' )
AND papf.person_id = peef.PERSON_ID
AND peef.element_type_id = petf.element_type_id
AND peef.EFFECTIVE_START_DATE >= ptp.START_DATE
AND peef.EFFECTIVE_END_DATE <= ptp.END_DATE
AND (COALESCE(NULL, :P_Emp_No) IS null OR PAPF.PERSON_NUMBER IN (:P_Emp_No))
AND (COALESCE(NULL, :p_ass_no) IS null OR ASG.assignment_number IN (:p_ass_no))
AND (COALESCE(NULL, :p_payroll) IS null OR pay.payroll_name IN (:p_payroll))
AND (COALESCE(NULL, :P_Element_Name) IS null OR petT.element_name IN (:P_Element_Name))
AND ptp.DEFAULT_PAYDATE between (:P_From) and (:P_To)
)
WHERE 1=1
AND (COALESCE(NULL, :P_child_academic_year) IS null OR child_academic_year IN (:P_child_academic_year))
AND (Amount IS NOT NULL OR Amount >0)
--and entitle_amount is not null
ORDER BY PERSON_NUMBER,Order_date desc
No comments:
Post a Comment