[Home] [Help]
111: -- Called by initialise during SC mode, to update the correct requests logs .
112:
113: CURSOR csr_request IS
114: SELECT cagr_request_id
115: FROM per_cagr_entitlement_results res
116: WHERE res.assignment_id = p_assignment_id
117: AND p_effective_date BETWEEN res.start_date and nvl(res.end_date,hr_general.end_of_time)
118: AND exists (select 'x'
119: from per_cagr_requests req
797:
798: Cursor csr_entries_not_exists(p_assignment_id NUMBER
799: ,p_element_type_id NUMBER) IS
800: Select distinct cer.element_type_id
801: From per_cagr_entitlement_results cer
802: Where cer.ASSIGNMENT_ID = p_assignment_id
803: and cer.end_date is null --fix for bug 5747086
804: and cer.element_type_id = p_element_type_id -- Bug 14096456
805: And not exists (select 1
814:
815: Cursor csr_entitlement_items(p_assignment_id NUMBER,
816: p_element_type_id NUMBER) IS
817: select cer.cagr_entitlement_item_id
818: from per_cagr_entitlement_results cer,
819: per_cagr_entitlement_items cei
820: where cer.assignment_id = p_assignment_id
821: and cer.element_type_id = p_element_type_id
822: and cer.cagr_entitlement_item_id = cei.cagr_entitlement_item_id
826:
827: -- Bug 6645756
828: Cursor csr_ineligible_entitlements(p_assignment_id number) IS
829: SELECT DISTINCT cagr_entitlement_item_id
830: FROM per_cagr_entitlement_results cer
831: WHERE ASSIGNMENT_ID = p_assignment_id
832: AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
833: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
834: AND start_date = trunc(p_params.effective_date)
828: Cursor csr_ineligible_entitlements(p_assignment_id number) IS
829: SELECT DISTINCT cagr_entitlement_item_id
830: FROM per_cagr_entitlement_results cer
831: WHERE ASSIGNMENT_ID = p_assignment_id
832: AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
833: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
834: AND start_date = trunc(p_params.effective_date)
835: AND ASSIGNMENT_ID = p_assignment_id)
836: AND EXISTS ( SELECT 1 FROM per_cagr_entitlement_results
832: AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
833: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
834: AND start_date = trunc(p_params.effective_date)
835: AND ASSIGNMENT_ID = p_assignment_id)
836: AND EXISTS ( SELECT 1 FROM per_cagr_entitlement_results
837: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
838: AND end_date = trunc(p_params.effective_date) - 1
839: AND ASSIGNMENT_ID = p_assignment_id);
840:
839: AND ASSIGNMENT_ID = p_assignment_id);
840:
841: CURSOR csr_entitlement_element_detail(p_entitlement_id number, p_assignment_id number) IS
842: SELECT cei.element_type_id,cer.start_date,cer.end_date
843: FROM per_cagr_entitlement_results cer,PER_CAGR_ENTITLEMENT_ITEMS cei
844: WHERE cer.ASSIGNMENT_ID = p_assignment_id
845: AND cer.end_date = trunc(p_params.effective_date)-1
846: AND cer.cagr_entitlement_item_id=cei.cagr_entitlement_item_id
847: AND cei.cagr_entitlement_item_id = p_entitlement_id;
1186: EXIT WHEN csr_ineligible_entitlements%NOTFOUND;
1187: -- Assignment is ineligible in future for current entitlement, get the element details and end date
1188: -- corresponding assignment element entries.
1189: OPEN csr_entitlement_element_detail(l_entitlement_id, p_params.assignment_id);
1190: /* Get all the records in per_cagr_entitlement_results, which are end dated on the effective date */
1191: loop
1192: fetch csr_entitlement_element_detail into l_element_type_id,l_start_date,l_end_date;
1193: exit when csr_entitlement_element_detail%notfound;
1194:
1237: ,cagr_api_id
1238: ,cagr_api_param_id
1239: ,value
1240: ,units_of_measure
1241: FROM per_cagr_entitlement_results
1242: WHERE category_name = 'ASG'
1243: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1244: AND (chosen_flag = 'Y'
1245: OR (beneficial_flag = 'Y'
1242: WHERE category_name = 'ASG'
1243: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1244: AND (chosen_flag = 'Y'
1245: OR (beneficial_flag = 'Y'
1246: and not exists (select 'X' from per_cagr_entitlement_results res1
1247: where res1.assignment_id = p_params.assignment_id
1248: and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1249: AND res1.chosen_flag = 'Y'
1250: AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
1266: ,from_step_id
1267: ,to_step_id
1268: ,value
1269: ,units_of_measure
1270: FROM per_cagr_entitlement_results
1271: WHERE category_name = 'PYS'
1272: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1273: AND (chosen_flag = 'Y'
1274: OR (beneficial_flag = 'Y'
1271: WHERE category_name = 'PYS'
1272: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1273: AND (chosen_flag = 'Y'
1274: OR (beneficial_flag = 'Y'
1275: and not exists (select 'X' from per_cagr_entitlement_results res1
1276: where res1.assignment_id = p_params.assignment_id
1277: and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1278: AND res1.chosen_flag = 'Y'
1279: AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
1291: ,element_type_id
1292: ,input_value_id
1293: ,value
1294: ,multiple_entries_allowed_flag
1295: FROM per_cagr_entitlement_results
1296: WHERE category_name = 'PAY'
1297: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1298: AND (chosen_flag = 'Y'
1299: OR (beneficial_flag = 'Y'
1296: WHERE category_name = 'PAY'
1297: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1298: AND (chosen_flag = 'Y'
1299: OR (beneficial_flag = 'Y'
1300: and not exists (select 'X' from per_cagr_entitlement_results res1
1301: where res1.assignment_id = p_params.assignment_id
1302: and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1303: AND res1.chosen_flag = 'Y'
1304: AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))