[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
789:
790: Cursor csr_entries_not_exists(p_assignment_id NUMBER
791: ,p_element_type_id NUMBER) IS
792: Select distinct cer.element_type_id
793: From per_cagr_entitlement_results cer
794: Where cer.ASSIGNMENT_ID = p_assignment_id
795: and cer.end_date is null --fix for bug 5747086
796: And not exists (select 1
797: From pay_element_entries ee
805:
806: Cursor csr_entitlement_items(p_assignment_id NUMBER,
807: p_element_type_id NUMBER) IS
808: select cer.cagr_entitlement_item_id
809: from per_cagr_entitlement_results cer,
810: per_cagr_entitlement_items cei
811: where cer.assignment_id = p_assignment_id
812: and cer.element_type_id = p_element_type_id
813: and cer.cagr_entitlement_item_id = cei.cagr_entitlement_item_id
817:
818: -- Bug 6645756
819: Cursor csr_ineligible_entitlements(p_assignment_id number) IS
820: SELECT DISTINCT cagr_entitlement_item_id
821: FROM per_cagr_entitlement_results cer
822: WHERE ASSIGNMENT_ID = p_assignment_id
823: AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
824: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
825: AND start_date = trunc(p_params.effective_date)
819: Cursor csr_ineligible_entitlements(p_assignment_id number) IS
820: SELECT DISTINCT cagr_entitlement_item_id
821: FROM per_cagr_entitlement_results cer
822: WHERE ASSIGNMENT_ID = p_assignment_id
823: AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
824: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
825: AND start_date = trunc(p_params.effective_date)
826: AND ASSIGNMENT_ID = p_assignment_id)
827: AND EXISTS ( SELECT 1 FROM per_cagr_entitlement_results
823: AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
824: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
825: AND start_date = trunc(p_params.effective_date)
826: AND ASSIGNMENT_ID = p_assignment_id)
827: AND EXISTS ( SELECT 1 FROM per_cagr_entitlement_results
828: WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
829: AND end_date = trunc(p_params.effective_date) - 1
830: AND ASSIGNMENT_ID = p_assignment_id);
831:
830: AND ASSIGNMENT_ID = p_assignment_id);
831:
832: CURSOR csr_entitlement_element_detail(p_entitlement_id number, p_assignment_id number) IS
833: SELECT cei.element_type_id,cer.start_date,cer.end_date
834: FROM per_cagr_entitlement_results cer,PER_CAGR_ENTITLEMENT_ITEMS cei
835: WHERE cer.ASSIGNMENT_ID = p_assignment_id
836: AND cer.end_date = trunc(p_params.effective_date)-1
837: AND cer.cagr_entitlement_item_id=cei.cagr_entitlement_item_id
838: AND cei.cagr_entitlement_item_id = p_entitlement_id;
1176: EXIT WHEN csr_ineligible_entitlements%NOTFOUND;
1177: -- Assignment is ineligible in future for current entitlement, get the element details and end date
1178: -- corresponding assignment element entries.
1179: OPEN csr_entitlement_element_detail(l_entitlement_id, p_params.assignment_id);
1180: /* Get all the records in per_cagr_entitlement_results, which are end dated on the effective date */
1181: loop
1182: fetch csr_entitlement_element_detail into l_element_type_id,l_start_date,l_end_date;
1183: exit when csr_entitlement_element_detail%notfound;
1184:
1227: ,cagr_api_id
1228: ,cagr_api_param_id
1229: ,value
1230: ,units_of_measure
1231: FROM per_cagr_entitlement_results
1232: WHERE category_name = 'ASG'
1233: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1234: AND (chosen_flag = 'Y'
1235: OR (beneficial_flag = 'Y'
1232: WHERE category_name = 'ASG'
1233: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1234: AND (chosen_flag = 'Y'
1235: OR (beneficial_flag = 'Y'
1236: and not exists (select 'X' from per_cagr_entitlement_results res1
1237: where res1.assignment_id = p_params.assignment_id
1238: and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1239: AND res1.chosen_flag = 'Y'
1240: AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
1256: ,from_step_id
1257: ,to_step_id
1258: ,value
1259: ,units_of_measure
1260: FROM per_cagr_entitlement_results
1261: WHERE category_name = 'PYS'
1262: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1263: AND (chosen_flag = 'Y'
1264: OR (beneficial_flag = 'Y'
1261: WHERE category_name = 'PYS'
1262: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1263: AND (chosen_flag = 'Y'
1264: OR (beneficial_flag = 'Y'
1265: and not exists (select 'X' from per_cagr_entitlement_results res1
1266: where res1.assignment_id = p_params.assignment_id
1267: and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1268: AND res1.chosen_flag = 'Y'
1269: AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
1281: ,element_type_id
1282: ,input_value_id
1283: ,value
1284: ,multiple_entries_allowed_flag
1285: FROM per_cagr_entitlement_results
1286: WHERE category_name = 'PAY'
1287: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1288: AND (chosen_flag = 'Y'
1289: OR (beneficial_flag = 'Y'
1286: WHERE category_name = 'PAY'
1287: AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
1288: AND (chosen_flag = 'Y'
1289: OR (beneficial_flag = 'Y'
1290: and not exists (select 'X' from per_cagr_entitlement_results res1
1291: where res1.assignment_id = p_params.assignment_id
1292: and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
1293: AND res1.chosen_flag = 'Y'
1294: AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))