1 PACKAGE BODY PAY_US_ELEM_ENT_CHK_LEG_HOOK AS
2 /* $Header: pyuschee.pkb 120.0 2005/05/29 09:19:15 appldev noship $ */
3
4 /*******************************************************************************
5 Name : CHK_ELEM_ENTRY
6 Purpose : This procedure is used to make sure that in the element entry
7 screen Involuntary deduction elements of same architecture are
8 entered. Element of two different architectures are not allowed.
9
10 *******************************************************************************/
11
12 PROCEDURE CHK_ELEM_ENTRY(
13 p_assignment_id number,
14 p_effective_start_date date,
15 p_element_link_id number
16 ) IS
17
18 CURSOR c_get_elem_entries IS
19 select pet.element_name,
20 pet.element_information4,
21 pet.business_group_id
22 from pay_element_entries_f pee,
23 pay_element_links_f pel,
24 pay_element_types_f pet,
25 pay_element_classifications pec,
26 per_assignments_f paf,
27 per_time_periods ptp
28 where paf.assignment_id = p_assignment_id
29 and pee.assignment_id = paf.assignment_id
30 and ptp.payroll_id = paf.payroll_id
31 and p_effective_start_date between ptp.start_date and ptp.end_date
32 and ptp.start_date < pee.effective_end_date
33 and pee.element_link_id = pel.element_link_id
34 and pel.element_link_id <> p_element_link_id
35 and pel.element_type_id = pet.element_type_id
36 and pet.classification_id = pec.classification_id
37 and pec.classification_name = 'Involuntary Deductions';
38
39
40 CURSOR c_curr_elem_arch IS
41 select pet.element_name,
42 pet.element_information4,
43 pet.business_group_id
44 from pay_element_links_f pel,
45 pay_element_types_f pet,
46 pay_element_classifications pec
47 where pel.element_link_id = p_element_link_id
48 and pel.element_type_id = pet.element_type_id
49 and pet.classification_id = pec.classification_id
50 and pec.classification_name = 'Involuntary Deductions';
51
52
53 -- Cursor to check the architecture used for the element created
54 CURSOR c_get_template_id(cp_element_name varchar2,
55 cp_business_group_id number) IS
56 select pset.template_id
57 from pay_shadow_element_types pset,
58 pay_element_templates pet
59 where pet.template_id = pset.template_id
60 and pet.template_type = 'U'
61 and pset.element_name = cp_element_name
62 and pet.business_group_id = cp_business_group_id;
63
64 lv_element_name VARCHAR2(100);
65 ln_business_group_id NUMBER;
66 ln_template_id NUMBER;
67
68 ln_architecture_flag NUMBER;
69 lb_status BOOLEAN;
70 lv_elem_upg VARCHAR2(50);
71
72 lv_procedure_name VARCHAR2(100);
73
74 BEGIN
75
76 lv_procedure_name := 'PAY_US_ELEM_ENT_CHK_LEG_HOOK.CHK_ELEM_ENTRY';
77 hr_utility.trace('Entering '|| lv_procedure_name);
78 hr_utility.set_location (lv_procedure_name,10);
79 hr_utility.trace('Input parameters....');
80 hr_utility.trace('P_ASSIGNMENT_ID = '||P_ASSIGNMENT_ID);
81 hr_utility.trace('p_effective_start_date = ' || p_effective_start_date);
82 hr_utility.trace('p_element_entry_link_id = '|| p_element_link_id);
83
84 ln_architecture_flag := -1; /* No architecture selected currently */
85 lb_status := TRUE;
86 lv_element_name := null;
87 ln_business_group_id := null;
88
89 -- Code to get the architecture used for creating the element
90 -- currently being entered.
91 -- If we are able to find a template in the
92 -- PAY_SHADOW_ELEMENT_TYPES for the Involuntary Deduction element
93 -- being added, then the element was created using the new architecture.
94 open c_curr_elem_arch();
95 fetch c_curr_elem_arch into lv_element_name,
96 lv_elem_upg,
97 ln_business_group_id;
98 if c_curr_elem_arch%NOTFOUND then
99 close c_curr_elem_arch;
100 return; /* Element does not belong to Involuntary Deductions category */
101 end if;
102
103 if c_curr_elem_arch%FOUND then
104 if lv_elem_upg is NULL then
105 open c_get_template_id(lv_element_name, ln_business_group_id);
106 fetch c_get_template_id into ln_template_id;
107 if c_get_template_id%FOUND then
108 ln_architecture_flag := 1; /* New architecture */
109 else
110 ln_architecture_flag := 0; /* Old architecture */
111 end if;
112 close c_get_template_id;
113 else
114 ln_architecture_flag := 1; /* New architecture */
115 end if;
116 end if;
117 close c_curr_elem_arch;
118
119 -- Below we find the architecture for the element entry.
120 -- Check for element entries for the assignment.
121 lv_element_name := null;
122 ln_business_group_id := null;
123 open c_get_elem_entries();
124 loop
125 fetch c_get_elem_entries into lv_element_name,
126 lv_elem_upg,
127 ln_business_group_id;
128 exit when c_get_elem_entries%NOTFOUND;
129
130 if lv_elem_upg is not NULL then -- Check for element upgraded Bug 3549298
131 if ln_architecture_flag = 0 then
132 lb_status := FALSE;
133 end if;
134 else
135 open c_get_template_id(lv_element_name,ln_business_group_id);
136 fetch c_get_template_id into ln_template_id;
137 if c_get_template_id%FOUND then
138 if ln_architecture_flag = 0 then
139 lb_status := FALSE;
140 end if;
141 else
142 if ln_architecture_flag = 1 then
143 lb_status := FALSE;
144 end if;
145 end if;
146 close c_get_template_id;
147 end if;
148 exit when lb_status = FALSE;
149 end loop;
150 close c_get_elem_entries;
151
152 if lb_status then
153 hr_utility.set_location (lv_procedure_name,20);
154 hr_utility.trace('Leaving '||lv_procedure_name);
155 return;
156 else
157 hr_utility.set_location (lv_procedure_name,30);
158 hr_utility.set_message(801,'PAY_US_WAGE_ARCH_DIFF');
159 hr_utility.raise_error;
160 end if;
161
162 END CHK_ELEM_ENTRY;
163
164 END PAY_US_ELEM_ENT_CHK_LEG_HOOK;
[Home] [Help]