[Home] [Help]
53: RETURN 'IN_PROGRESS';
54: else
55: -- checking if the outcome is achieved in current session.
56: Select count(*) INTO l_noof_outcomes
57: FROM hr_api_transaction_steps S, hr_api_transaction_values tv,
58: hr_api_transaction_values tv1, hr_api_transaction_values tv2,
59: hr_api_transaction_values tv3
60: Where s.item_type = p_item_type
61: and s.item_key = p_item_key
85:
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)
101: RETURN 'IN_PROGRESS';
102: ELSE -- l_noof_outcomes = 0
103:
104: Select count(*) INTO l_noof_outcomes
105: FROM hr_api_transaction_steps ts,
106: hr_api_transaction_values tv,
107: hr_api_transaction_values tv1, hr_api_transaction_values tv2,
108: hr_api_transaction_values tv3, per_competence_outcomes pco
109: Where ts.ITEM_TYPE = p_item_type
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
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)
161: AND ceo.Outcome_id = pco.Outcome_id))
162: Union All
163: (Select 1 FROM hr_api_transaction_values tv,
164: hr_api_transaction_values tv1, hr_api_transaction_values tv2,
165: hr_api_transaction_values tv3 , hr_api_transaction_steps ts
166: Where ts.ITEM_TYPE = p_item_type
167: AND ts.item_key = p_item_key
168: And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
169: AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
254: WHERE pco.competence_id = p_competence_id
255: AND nvl(p_eff_date,sysdate) BETWEEN pco.date_from AND nvl(pco.date_to,nvl(p_eff_date,trunc(sysdate)))
256: AND NOT exists (Select 1 FROM hr_api_transaction_values tv,
257: hr_api_transaction_values tv1, hr_api_transaction_values tv2,
258: hr_api_transaction_values tv3 , hr_api_transaction_steps ts
259: Where ts.ITEM_TYPE = p_item_type
260: AND ts.item_key = p_item_key
261: And ts.activity_id = nvl(to_number(p_activity_id),ts.activity_id)
262: AND ts.API_NAME = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
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)
281: AND NOT EXISTS ( SELECT 1 from
282: hr_api_transaction_steps S, hr_api_transaction_values C
283: Where s.item_type = p_item_type
284: and s.item_key = p_item_key
285: and s.activity_id = nvl(to_number(p_activity_id),s.activity_id)
286: and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.DELETE'
289: AND C.NUMBER_VALUE = CEO.COMP_ELEMENT_OUTCOME_ID
290: )));
291: IF l_noof_outcomes = 0 then
292: Select count(*) INTO l_noof_outcomes
293: FROM hr_api_transaction_steps ts,
294: hr_api_transaction_values tv,
295: hr_api_transaction_values tv1, hr_api_transaction_values tv2,
296: hr_api_transaction_values tv3, per_competence_outcomes pco
297: Where ts.ITEM_TYPE = p_item_type