[Home] [Help]
PACKAGE BODY: APPS.PQH_BDT_SHD
Source
1 Package Body pqh_bdt_shd as
2 /* $Header: pqbdtrhi.pkb 120.0 2005/05/29 01:28:31 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bdt_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE) Is
15 --
16 l_proc varchar2(72) := g_package||'constraint_error';
17 --
18 Begin
19 hr_utility.set_location('Entering:'||l_proc, 5);
20 --
21 If (p_constraint_name = 'PQH_BUDGET_DETAILS_FK2') Then
22 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
23 hr_utility.set_message_token('PROCEDURE', l_proc);
24 hr_utility.set_message_token('STEP','5');
25 hr_utility.raise_error;
26 ElsIf (p_constraint_name = 'PQH_BUDGET_DETAILS_FK3') Then
27 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
28 hr_utility.set_message_token('PROCEDURE', l_proc);
29 hr_utility.set_message_token('STEP','10');
30 hr_utility.raise_error;
31 ElsIf (p_constraint_name = 'PQH_BUDGET_PERIODS_FK1') Then
32 hr_utility.set_message(8302,'PQH_BUDGET_PERIODS_EXISTS');
33 hr_utility.raise_error;
34 ElsIf (p_constraint_name = 'PQH_BUDGET_DETAILS_FK4') Then
35 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36 hr_utility.set_message_token('PROCEDURE', l_proc);
37 hr_utility.set_message_token('STEP','15');
38 hr_utility.raise_error;
39 ElsIf (p_constraint_name = 'PQH_BUDGET_DETAILS_FK5') Then
40 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
41 hr_utility.set_message_token('PROCEDURE', l_proc);
42 hr_utility.set_message_token('STEP','20');
43 hr_utility.raise_error;
44 ElsIf (p_constraint_name = 'PQH_BUDGET_DETAILS_PK') Then
45 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
46 hr_utility.set_message_token('PROCEDURE', l_proc);
47 hr_utility.set_message_token('STEP','25');
48 hr_utility.raise_error;
49 ElsIf (p_constraint_name = 'PQH_BUDGET_DETAILS_UK') Then
50 --
51 -- Code Changed to provide a more meaningful message on
52 -- unique constraint failure.
53 --
54 hr_utility.set_message(8302, 'PQH_DUPLICATE_WORKSHEET_DETAIL');
55 hr_utility.raise_error;
56 /*
57 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
58 hr_utility.set_message_token('PROCEDURE', l_proc);
59 hr_utility.set_message_token('STEP','25');
60 hr_utility.raise_error;
61 */
62 Else
63 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
64 hr_utility.set_message_token('PROCEDURE', l_proc);
65 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
66 hr_utility.raise_error;
67 End If;
68 --
69 hr_utility.set_location(' Leaving:'||l_proc, 10);
70 End constraint_error;
71 --
72 -- ----------------------------------------------------------------------------
73 -- |-----------------------------< api_updating >-----------------------------|
74 -- ----------------------------------------------------------------------------
75 Function api_updating
76 (
77 p_budget_detail_id in number,
78 p_object_version_number in number
79 ) Return Boolean Is
80 --
81 --
82 -- Cursor selects the 'current' row from the HR Schema
83 --
84 Cursor C_Sel1 is
85 select
86 budget_detail_id,
87 organization_id,
88 job_id,
89 position_id,
90 grade_id,
91 budget_version_id,
92 budget_unit1_percent,
93 budget_unit1_value_type_cd,
94 budget_unit1_value,
95 budget_unit1_available,
96 budget_unit2_percent,
97 budget_unit2_value_type_cd,
98 budget_unit2_value,
99 budget_unit2_available,
100 budget_unit3_percent,
101 budget_unit3_value_type_cd,
102 budget_unit3_value,
103 budget_unit3_available,
104 gl_status,
105 object_version_number
106 from pqh_budget_details
107 where budget_detail_id = p_budget_detail_id;
108 --
109 l_proc varchar2(72) := g_package||'api_updating';
110 l_fct_ret boolean;
111 --
112 Begin
113 hr_utility.set_location('Entering:'||l_proc, 5);
114 --
115 If (
116 p_budget_detail_id is null and
117 p_object_version_number is null
118 ) Then
119 --
120 -- One of the primary key arguments is null therefore we must
121 -- set the returning function value to false
122 --
123 l_fct_ret := false;
124 Else
125 If (
126 p_budget_detail_id = g_old_rec.budget_detail_id and
127 p_object_version_number = g_old_rec.object_version_number
128 ) Then
129 hr_utility.set_location(l_proc, 10);
130 --
131 -- The g_old_rec is current therefore we must
132 -- set the returning function to true
133 --
134 l_fct_ret := true;
135 Else
136 --
137 -- Select the current row into g_old_rec
138 --
139 Open C_Sel1;
140 Fetch C_Sel1 Into g_old_rec;
141 If C_Sel1%notfound Then
142 Close C_Sel1;
143 --
144 -- The primary key is invalid therefore we must error
145 --
146 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
147 hr_utility.raise_error;
148 End If;
149 Close C_Sel1;
150 If (p_object_version_number <> g_old_rec.object_version_number) Then
151 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
152 hr_utility.raise_error;
153 End If;
154 hr_utility.set_location(l_proc, 15);
155 l_fct_ret := true;
156 End If;
157 End If;
158 hr_utility.set_location(' Leaving:'||l_proc, 20);
159 Return (l_fct_ret);
160 --
161 End api_updating;
162 --
163 -- ----------------------------------------------------------------------------
164 -- |---------------------------------< lck >----------------------------------|
165 -- ----------------------------------------------------------------------------
166 Procedure lck
167 (
168 p_budget_detail_id in number,
169 p_object_version_number in number
170 ) is
171 --
172 -- Cursor selects the 'current' row from the HR Schema
173 --
174 Cursor C_Sel1 is
175 select budget_detail_id,
176 organization_id,
177 job_id,
178 position_id,
179 grade_id,
180 budget_version_id,
181 budget_unit1_percent,
182 budget_unit1_value_type_cd,
183 budget_unit1_value,
184 budget_unit1_available,
185 budget_unit2_percent,
186 budget_unit2_value_type_cd,
187 budget_unit2_value,
188 budget_unit2_available,
189 budget_unit3_percent,
190 budget_unit3_value_type_cd,
191 budget_unit3_value,
192 budget_unit3_available,
193 gl_status,
194 object_version_number
195 from pqh_budget_details
196 where budget_detail_id = p_budget_detail_id
197 for update nowait;
198 --
199 l_proc varchar2(72) := g_package||'lck';
200 --
201 Begin
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 --
204 -- Add any mandatory argument checking here:
205 -- Example:
206 -- hr_api.mandatory_arg_error
207 -- (p_api_name => l_proc,
208 -- p_argument => 'object_version_number',
209 -- p_argument_value => p_object_version_number);
210 --
211 Open C_Sel1;
212 Fetch C_Sel1 Into g_old_rec;
213 If C_Sel1%notfound then
214 Close C_Sel1;
215 --
216 -- The primary key is invalid therefore we must error
217 --
218 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
219 hr_utility.raise_error;
220 End If;
221 Close C_Sel1;
222 If (p_object_version_number <> g_old_rec.object_version_number) Then
223 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
224 hr_utility.raise_error;
225 End If;
226 --
227 hr_utility.set_location(' Leaving:'||l_proc, 10);
228 --
229 -- We need to trap the ORA LOCK exception
230 --
231 Exception
232 When HR_Api.Object_Locked then
233 --
234 -- The object is locked therefore we need to supply a meaningful
235 -- error message.
236 --
237 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
238 hr_utility.set_message_token('TABLE_NAME', 'pqh_budget_details');
239 hr_utility.raise_error;
240 End lck;
241 --
242 -- ----------------------------------------------------------------------------
243 -- |-----------------------------< convert_args >-----------------------------|
244 -- ----------------------------------------------------------------------------
245 Function convert_args
246 (
247 p_budget_detail_id in number,
248 p_organization_id in number,
249 p_job_id in number,
250 p_position_id in number,
251 p_grade_id in number,
252 p_budget_version_id in number,
253 p_budget_unit1_percent in number,
254 p_budget_unit1_value_type_cd in varchar2,
255 p_budget_unit1_value in number,
256 p_budget_unit1_available in number,
257 p_budget_unit2_percent in number,
258 p_budget_unit2_value_type_cd in varchar2,
259 p_budget_unit2_value in number,
260 p_budget_unit2_available in number,
261 p_budget_unit3_percent in number,
262 p_budget_unit3_value_type_cd in varchar2,
263 p_budget_unit3_value in number,
264 p_budget_unit3_available in number,
265 p_gl_status in varchar2,
266 p_object_version_number in number
267 )
268 Return g_rec_type is
269 --
270 l_rec g_rec_type;
271 l_proc varchar2(72) := g_package||'convert_args';
272 --
273 Begin
274 --
275 hr_utility.set_location('Entering:'||l_proc, 5);
276 --
277 -- Convert arguments into local l_rec structure.
278 --
279 l_rec.budget_detail_id := p_budget_detail_id;
280 l_rec.organization_id := p_organization_id;
281 l_rec.job_id := p_job_id;
282 l_rec.position_id := p_position_id;
283 l_rec.grade_id := p_grade_id;
284 l_rec.budget_version_id := p_budget_version_id;
285 l_rec.budget_unit1_percent := p_budget_unit1_percent;
286 l_rec.budget_unit1_value_type_cd := p_budget_unit1_value_type_cd;
287 l_rec.budget_unit1_value := p_budget_unit1_value;
288 l_rec.budget_unit1_available := p_budget_unit1_available;
289 l_rec.budget_unit2_percent := p_budget_unit2_percent;
290 l_rec.budget_unit2_value_type_cd := p_budget_unit2_value_type_cd;
291 l_rec.budget_unit2_value := p_budget_unit2_value;
292 l_rec.budget_unit2_available := p_budget_unit2_available;
293 l_rec.budget_unit3_percent := p_budget_unit3_percent;
294 l_rec.budget_unit3_value_type_cd := p_budget_unit3_value_type_cd;
295 l_rec.budget_unit3_value := p_budget_unit3_value;
296 l_rec.budget_unit3_available := p_budget_unit3_available;
297 l_rec.gl_status := p_gl_status;
298 l_rec.object_version_number := p_object_version_number;
299 --
300 -- Return the plsql record structure.
301 --
302 hr_utility.set_location(' Leaving:'||l_proc, 10);
303 Return(l_rec);
304 --
305 End convert_args;
306 --
307 end pqh_bdt_shd;