[Home] [Help]
9:
10:
11: --- CURSOR TO VAIDATE TEH CONTRACT_ID , ONE CONTRACT SHOULD BE LINKED WITH ONLY ONE ASSIGNMENT
12: CURSOR csr_assignment_contract is
13: select 'x' from per_all_assignments_f paaf
14: where paaf.person_id = p_person_id
15: and paaf.contract_id = p_contract_id
16: and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
17: l_proc VARCHAR2(60);
51: ,P_contract_id in number
52: ) IS
53:
54: CURSOR csr_assignment_contract is
55: select 'x' from per_all_assignments_f paaf
56: where paaf.person_id = (select paaf1.person_id from per_all_assignments_f paaf1 where paaf1.ASSIGNMENT_ID = p_assignment_id
57: and p_effective_date between paaf1.effective_start_date and paaf1.effective_end_date)
58: and paaf.contract_id = p_contract_id
59: AND paaf.ASSIGNMENT_ID <> p_assignment_id
52: ) IS
53:
54: CURSOR csr_assignment_contract is
55: select 'x' from per_all_assignments_f paaf
56: where paaf.person_id = (select paaf1.person_id from per_all_assignments_f paaf1 where paaf1.ASSIGNMENT_ID = p_assignment_id
57: and p_effective_date between paaf1.effective_start_date and paaf1.effective_end_date)
58: and paaf.contract_id = p_contract_id
59: AND paaf.ASSIGNMENT_ID <> p_assignment_id
60: and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
61:
62: l_proc VARCHAR2(60);
63: l_exist varchar2(1);
64: l_contract_start_date per_contracts_f.effective_start_date%type;
65: l_assignment_start_date per_all_assignments_f.effective_start_date%type := p_effective_date;
66: l_contract_signed_date PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION1%type;
67:
68: BEGIN
69: l_proc := g_package||'update_asg_validate';