APPS.PERCOMPSTATUS dependencies on PER_COMP_ELEMENT_OUTCOMES

Line 17: FROM per_comp_element_outcomes ceo , per_competence_outcomes pco

13: ,p_eff_date in date default trunc(sysdate)
14: ) return VARCHAR2 is
15: CURSOR getEndDatedOutcomes(compEleId IN Number) is
16: Select max(ceo.date_to),ceo.outcome_id
17: FROM per_comp_element_outcomes ceo , per_competence_outcomes pco
18: where competence_element_id = compEleId
19: AND ceo.date_to < p_eff_date
20: and pco.outcome_id = ceo.outcome_id
21: and pco.date_from <= p_eff_date

Line 46: FROM Per_comp_element_outcomes CEO

42: FOR EndDatedRec IN getEndDatedOutcomes(p_competence_element_id)
43: loop
44: -- checking if the end dated outcome has achieved rec for the effective date.
45: Select count(*) INTO l_noof_outcomes
46: FROM Per_comp_element_outcomes CEO
47: Where ceo.outcome_id = EndDatedRec.outcome_id
48: AND ceo.competence_element_id = p_competence_element_id
49: AND ceo.date_from <= p_eff_date
50: AND nvl(ceo.date_to,p_eff_date) >= p_eff_date;

Line 90: Per_comp_element_outcomes CEO

86: IF p_item_type IS NULL OR p_item_key IS NULL then
87: RETURN 'ACHIEVED';
88: ELSE -- p_ietm_type and p_item_key is not null
89: Select count(*) into l_noof_outcomes FROM hr_api_transaction_steps S, hr_api_transaction_values C,
90: Per_comp_element_outcomes CEO
91: Where s.item_type = p_item_type
92: and s.item_key = p_item_key
93: and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
94: and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'

Line 146: AND NOT EXISTS ( (Select 1 FROM per_comp_element_outcomes

142: Select count(*) INTO l_noof_outcomes from
143: per_competence_outcomes pco
144: WHERE competence_id = p_competence_id
145: AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
146: AND NOT EXISTS ( (Select 1 FROM per_comp_element_outcomes
147: Where competence_element_id = p_competence_element_id
148: AND per_comp_element_outcomes.outcome_id = pco.outcome_id
149: AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
150: AND NOT EXISTS

Line 148: AND per_comp_element_outcomes.outcome_id = pco.outcome_id

144: WHERE competence_id = p_competence_id
145: AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
146: AND NOT EXISTS ( (Select 1 FROM per_comp_element_outcomes
147: Where competence_element_id = p_competence_element_id
148: AND per_comp_element_outcomes.outcome_id = pco.outcome_id
149: AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
150: AND NOT EXISTS
151: (Select 1 FROM hr_api_transaction_steps S, hr_api_transaction_values C,
152: Per_comp_element_outcomes CEO

Line 152: Per_comp_element_outcomes CEO

148: AND per_comp_element_outcomes.outcome_id = pco.outcome_id
149: AND nvl(p_eff_date,trunc(sysdate)) BETWEEN date_from AND nvl(date_to,nvl(p_eff_date,trunc(sysdate)))
150: AND NOT EXISTS
151: (Select 1 FROM hr_api_transaction_steps S, hr_api_transaction_values C,
152: Per_comp_element_outcomes CEO
153: Where s.item_type = p_item_type
154: and s.item_key = p_item_key
155: and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
156: and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'

Line 276: (Select 1 FROM Per_comp_element_outcomes CEO

272: And tv.number_value = pco.outcome_id
273: And trunc(p_eff_date) BETWEEN tv1.date_value
274: AND nvl(tv2.date_value,trunc(p_eff_date))
275: Union all
276: (Select 1 FROM Per_comp_element_outcomes CEO
277: Where ceo.Competence_element_id = p_competence_element_id
278: AND ceo.Outcome_id = pco.Outcome_id
279: And ceo.date_from <= trunc(p_eff_date)
280: AND nvl(ceo.date_to,trunc(p_eff_date)) >= trunc(p_eff_date)