VIEW: APPS.HXTFV_INTERACT_POLICY

Source

View Text - Preformatted

SELECT /* $Header: hxtbis.odf 120.1 2005/09/26 12:49:58 jdupont noship $ */ bgrT.name business_group_name ,peo.full_name person_name ,1 person_count ,epl.name earning_policy ,pip.name premium_interact_policy ,pip.description policy_description ,pip.date_from date_from ,pip.date_to date_to ,earned_pol.element_name policy_base_element ,earned_prem.element_name rules_base_element ,prior_prem.element_name rules_prem_element /********/ /* Ids */ /********/ ,asg.assignment_id assignment_id ,asg.business_group_id business_group_id ,epl.id earning_policy_id ,ipr.elt_earned_prem_id earned_pol_id ,pir.elt_earned_prem_id earned_prem_id ,asg.grade_id grade_id ,pip.id interact_policy_id ,asg.job_id job_id ,asg.organization_id organization_id ,asg.person_id person_id ,asg.position_id position_id ,pir.elt_prior_prem_id prior_prem_id FROM hxt_prem_interact_policies pip ,hxt_prem_interact_pol_rules ipr ,hxt_prem_interact_rules pir ,hxt_add_assign_info_f aai ,hxt_earning_policies epl ,per_assignments_f asg ,per_all_people_f peo ,pay_element_types_f earned_pol ,pay_element_types_f earned_prem ,pay_element_types_f prior_prem ,hr_all_organization_units_tl bgrT WHERE peo.person_id = asg.person_id AND asg.assignment_id = aai.assignment_id AND aai.earning_policy = epl.id AND epl.pip_id = pip.id AND pip.id = ipr.pip_id AND pip.id = pir.pip_id AND ipr.elt_earned_prem_id = earned_pol.element_type_id (+) AND pir.elt_earned_prem_id = earned_prem.element_type_id (+) AND pir.elt_prior_prem_id = prior_prem.element_type_id (+) AND bgrT.organization_id = asg.business_group_id AND bgrT.language = userenv('LANG') AND SYSDATE BETWEEN peo.effective_start_date AND peo.effective_end_date AND SYSDATE BETWEEN asg.effective_start_date AND asg.effective_end_date AND SYSDATE BETWEEN aai.effective_start_date AND aai.effective_end_date AND SYSDATE BETWEEN ipr.effective_start_date AND ipr.effective_end_date AND SYSDATE BETWEEN pir.effective_start_date AND pir.effective_end_date AND SYSDATE BETWEEN epl.effective_start_date AND epl.effective_end_date AND SYSDATE BETWEEN earned_pol.effective_start_date (+) AND earned_pol.effective_end_date (+) AND SYSDATE BETWEEN earned_prem.effective_start_date (+) AND earned_prem.effective_end_date (+) AND SYSDATE BETWEEN prior_prem.effective_start_date (+) AND prior_prem.effective_end_date (+) AND asg.business_group_id = NVL(hr_general.get_business_group_id, asg.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT /* $HEADER: HXTBIS.ODF 120.1 2005/09/26 12:49:58 JDUPONT NOSHIP $ */ BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, 1 PERSON_COUNT
, EPL.NAME EARNING_POLICY
, PIP.NAME PREMIUM_INTERACT_POLICY
, PIP.DESCRIPTION POLICY_DESCRIPTION
, PIP.DATE_FROM DATE_FROM
, PIP.DATE_TO DATE_TO
, EARNED_POL.ELEMENT_NAME POLICY_BASE_ELEMENT
, EARNED_PREM.ELEMENT_NAME RULES_BASE_ELEMENT
, PRIOR_PREM.ELEMENT_NAME RULES_PREM_ELEMENT /********/ /* IDS */ /********/
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EPL.ID EARNING_POLICY_ID
, IPR.ELT_EARNED_PREM_ID EARNED_POL_ID
, PIR.ELT_EARNED_PREM_ID EARNED_PREM_ID
, ASG.GRADE_ID GRADE_ID
, PIP.ID INTERACT_POLICY_ID
, ASG.JOB_ID JOB_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.PERSON_ID PERSON_ID
, ASG.POSITION_ID POSITION_ID
, PIR.ELT_PRIOR_PREM_ID PRIOR_PREM_ID
FROM HXT_PREM_INTERACT_POLICIES PIP
, HXT_PREM_INTERACT_POL_RULES IPR
, HXT_PREM_INTERACT_RULES PIR
, HXT_ADD_ASSIGN_INFO_F AAI
, HXT_EARNING_POLICIES EPL
, PER_ASSIGNMENTS_F ASG
, PER_ALL_PEOPLE_F PEO
, PAY_ELEMENT_TYPES_F EARNED_POL
, PAY_ELEMENT_TYPES_F EARNED_PREM
, PAY_ELEMENT_TYPES_F PRIOR_PREM
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE PEO.PERSON_ID = ASG.PERSON_ID
AND ASG.ASSIGNMENT_ID = AAI.ASSIGNMENT_ID
AND AAI.EARNING_POLICY = EPL.ID
AND EPL.PIP_ID = PIP.ID
AND PIP.ID = IPR.PIP_ID
AND PIP.ID = PIR.PIP_ID
AND IPR.ELT_EARNED_PREM_ID = EARNED_POL.ELEMENT_TYPE_ID (+)
AND PIR.ELT_EARNED_PREM_ID = EARNED_PREM.ELEMENT_TYPE_ID (+)
AND PIR.ELT_PRIOR_PREM_ID = PRIOR_PREM.ELEMENT_TYPE_ID (+)
AND BGRT.ORGANIZATION_ID = ASG.BUSINESS_GROUP_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN AAI.EFFECTIVE_START_DATE
AND AAI.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN IPR.EFFECTIVE_START_DATE
AND IPR.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PIR.EFFECTIVE_START_DATE
AND PIR.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN EPL.EFFECTIVE_START_DATE
AND EPL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN EARNED_POL.EFFECTIVE_START_DATE (+)
AND EARNED_POL.EFFECTIVE_END_DATE (+)
AND SYSDATE BETWEEN EARNED_PREM.EFFECTIVE_START_DATE (+)
AND EARNED_PREM.EFFECTIVE_END_DATE (+)
AND SYSDATE BETWEEN PRIOR_PREM.EFFECTIVE_START_DATE (+)
AND PRIOR_PREM.EFFECTIVE_END_DATE (+)
AND ASG.BUSINESS_GROUP_ID = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY