[Home] [Help]
PACKAGE BODY: APPS.PQH_WDT_SHD
Source
1 Package Body pqh_wdt_shd as
2 /* $Header: pqwdtrhi.pkb 120.0.12000000.1 2007/01/17 00:29:46 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_wdt_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_WORKSHEET_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_WORKSHEET_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_WORKSHEET_DETAILS_FK4') Then
32 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
33 hr_utility.set_message_token('PROCEDURE', l_proc);
34 hr_utility.set_message_token('STEP','15');
35 hr_utility.raise_error;
36 ElsIf (p_constraint_name = 'PQH_WORKSHEET_DETAILS_FK5') Then
37 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
38 hr_utility.set_message_token('PROCEDURE', l_proc);
39 hr_utility.set_message_token('STEP','20');
40 hr_utility.raise_error;
41 ElsIf (p_constraint_name = 'PQH_WORKSHEET_DETAILS_FK6') Then
42 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
43 hr_utility.set_message_token('PROCEDURE', l_proc);
44 hr_utility.set_message_token('STEP','25');
45 hr_utility.raise_error;
46 ElsIf (p_constraint_name = 'PQH_WORKSHEET_DETAILS_FK7') Then
47 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
48 hr_utility.set_message_token('PROCEDURE', l_proc);
49 hr_utility.set_message_token('STEP','30');
50 hr_utility.raise_error;
51 ElsIf (p_constraint_name = 'PQH_WORKSHEET_DETAILS_FK8') Then
52 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
53 hr_utility.set_message_token('PROCEDURE', l_proc);
54 hr_utility.set_message_token('STEP','35');
55 hr_utility.raise_error;
56 ElsIf (p_constraint_name = 'PQH_WORKSHEET_DETAILS_PK') Then
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','40');
60 hr_utility.raise_error;
61 ElsIf (p_constraint_name = 'PQH_WORKSHEET_DETAILS_UK') Then
62 --
63 -- Code Changed to provide a more meaningful message on
64 -- unique constraint failure.
65 --
66 hr_utility.set_message(8302, 'PQH_DUPLICATE_WORKSHEET_DETAIL');
67 hr_utility.raise_error;
68 /**
69 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
70 hr_utility.set_message_token('PROCEDURE', l_proc);
71 hr_utility.set_message_token('STEP','45');
72 hr_utility.raise_error;
73 **/
74 Else
75 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
76 hr_utility.set_message_token('PROCEDURE', l_proc);
77 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
78 hr_utility.raise_error;
79 End If;
80 --
81 hr_utility.set_location(' Leaving:'||l_proc, 10);
82 End constraint_error;
83 --
84 -- ----------------------------------------------------------------------------
85 -- |-----------------------------< api_updating >-----------------------------|
86 -- ----------------------------------------------------------------------------
87 Function api_updating
88 (
89 p_worksheet_detail_id in number,
90 p_object_version_number in number
91 ) Return Boolean Is
92 --
93 --
94 -- Cursor selects the 'current' row from the HR Schema
95 --
96 Cursor C_Sel1 is
97 select
98 worksheet_detail_id,
99 worksheet_id,
100 organization_id,
101 job_id,
102 position_id,
103 grade_id,
104 position_transaction_id,
105 budget_detail_id,
106 parent_worksheet_detail_id,
107 user_id,
108 action_cd,
109 budget_unit1_percent,
110 budget_unit1_value,
111 budget_unit2_percent,
112 budget_unit2_value,
113 budget_unit3_percent,
114 budget_unit3_value,
115 object_version_number,
116 budget_unit1_value_type_cd,
117 budget_unit2_value_type_cd,
118 budget_unit3_value_type_cd,
119 status,
120 budget_unit1_available,
121 budget_unit2_available,
122 budget_unit3_available,
123 old_unit1_value,
124 old_unit2_value,
125 old_unit3_value,
126 defer_flag,
127 propagation_method
128 from pqh_worksheet_details
129 where worksheet_detail_id = p_worksheet_detail_id;
130 --
131 l_proc varchar2(72) := g_package||'api_updating';
132 l_fct_ret boolean;
133 --
134 Begin
135 hr_utility.set_location('Entering:'||l_proc, 5);
136 --
137 If (
138 p_worksheet_detail_id is null and
139 p_object_version_number is null
140 ) Then
141 --
142 -- One of the primary key arguments is null therefore we must
143 -- set the returning function value to false
144 --
145 l_fct_ret := false;
146 Else
147 If (
148 p_worksheet_detail_id = g_old_rec.worksheet_detail_id and
149 p_object_version_number = g_old_rec.object_version_number
150 ) Then
151 hr_utility.set_location(l_proc, 10);
152 --
153 -- The g_old_rec is current therefore we must
154 -- set the returning function to true
155 --
156 l_fct_ret := true;
157 Else
158 --
159 -- Select the current row into g_old_rec
160 --
161 Open C_Sel1;
162 Fetch C_Sel1 Into g_old_rec;
163 If C_Sel1%notfound Then
164 Close C_Sel1;
165 --
166 -- The primary key is invalid therefore we must error
167 --
168 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
169 hr_utility.raise_error;
170 End If;
171 Close C_Sel1;
172 If (p_object_version_number <> g_old_rec.object_version_number) Then
173 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
174 hr_utility.raise_error;
175 End If;
176 hr_utility.set_location(l_proc, 15);
177 l_fct_ret := true;
178 End If;
179 End If;
180 hr_utility.set_location(' Leaving:'||l_proc, 20);
181 Return (l_fct_ret);
182 --
183 End api_updating;
184 --
185 -- ----------------------------------------------------------------------------
186 -- |---------------------------------< lck >----------------------------------|
187 -- ----------------------------------------------------------------------------
188 Procedure lck
189 (
190 p_worksheet_detail_id in number,
191 p_object_version_number in number
192 ) is
193 --
194 -- Cursor selects the 'current' row from the HR Schema
195 --
196 Cursor C_Sel1 is
197 select worksheet_detail_id,
198 worksheet_id,
199 organization_id,
200 job_id,
201 position_id,
202 grade_id,
203 position_transaction_id,
204 budget_detail_id,
205 parent_worksheet_detail_id,
206 user_id,
207 action_cd,
208 budget_unit1_percent,
209 budget_unit1_value,
210 budget_unit2_percent,
211 budget_unit2_value,
212 budget_unit3_percent,
213 budget_unit3_value,
214 object_version_number,
215 budget_unit1_value_type_cd,
216 budget_unit2_value_type_cd,
217 budget_unit3_value_type_cd,
218 status,
219 budget_unit1_available,
220 budget_unit2_available,
221 budget_unit3_available,
222 old_unit1_value,
223 old_unit2_value,
224 old_unit3_value,
225 defer_flag,
226 propagation_method
227 from pqh_worksheet_details
228 where worksheet_detail_id = p_worksheet_detail_id
229 for update nowait;
230 --
231 l_proc varchar2(72) := g_package||'lck';
232 --
233 Begin
234 hr_utility.set_location('Entering:'||l_proc, 5);
235 --
236 -- Add any mandatory argument checking here:
237 -- Example:
238 -- hr_api.mandatory_arg_error
239 -- (p_api_name => l_proc,
240 -- p_argument => 'object_version_number',
241 -- p_argument_value => p_object_version_number);
242 --
243 Open C_Sel1;
244 Fetch C_Sel1 Into g_old_rec;
245 If C_Sel1%notfound then
246 Close C_Sel1;
247 --
248 -- The primary key is invalid therefore we must error
249 --
250 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
251 hr_utility.raise_error;
252 End If;
253 Close C_Sel1;
254 If (p_object_version_number <> g_old_rec.object_version_number) Then
255 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
256 hr_utility.raise_error;
257 End If;
258 --
259 hr_utility.set_location(' Leaving:'||l_proc, 10);
260 --
261 -- We need to trap the ORA LOCK exception
262 --
263 Exception
264 When HR_Api.Object_Locked then
265 --
266 -- The object is locked therefore we need to supply a meaningful
267 -- error message.
268 --
269 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
270 hr_utility.set_message_token('TABLE_NAME', 'pqh_worksheet_details');
271 hr_utility.raise_error;
272 End lck;
273 --
274 -- ----------------------------------------------------------------------------
275 -- |-----------------------------< convert_args >-----------------------------|
276 -- ----------------------------------------------------------------------------
277 Function convert_args
278 (
279 p_worksheet_detail_id in number,
280 p_worksheet_id in number,
281 p_organization_id in number,
282 p_job_id in number,
283 p_position_id in number,
284 p_grade_id in number,
285 p_position_transaction_id in number,
286 p_budget_detail_id in number,
287 p_parent_worksheet_detail_id in number,
288 p_user_id in number,
289 p_action_cd in varchar2,
290 p_budget_unit1_percent in number,
291 p_budget_unit1_value in number,
292 p_budget_unit2_percent in number,
293 p_budget_unit2_value in number,
294 p_budget_unit3_percent in number,
295 p_budget_unit3_value in number,
296 p_object_version_number in number,
297 p_budget_unit1_value_type_cd in varchar2,
298 p_budget_unit2_value_type_cd in varchar2,
299 p_budget_unit3_value_type_cd in varchar2,
300 p_status in varchar2,
301 p_budget_unit1_available in number,
302 p_budget_unit2_available in number,
303 p_budget_unit3_available in number,
304 p_old_unit1_value in number,
305 p_old_unit2_value in number,
306 p_old_unit3_value in number,
307 p_defer_flag in varchar2,
308 p_propagation_method in varchar2
309 )
310 Return g_rec_type is
311 --
312 l_rec g_rec_type;
313 l_proc varchar2(72) := g_package||'convert_args';
314 --
315 Begin
316 --
317 hr_utility.set_location('Entering:'||l_proc, 5);
318 --
319 -- Convert arguments into local l_rec structure.
320 --
321 l_rec.worksheet_detail_id := p_worksheet_detail_id;
322 l_rec.worksheet_id := p_worksheet_id;
323 l_rec.organization_id := p_organization_id;
324 l_rec.job_id := p_job_id;
325 l_rec.position_id := p_position_id;
326 l_rec.grade_id := p_grade_id;
327 l_rec.position_transaction_id := p_position_transaction_id;
328 l_rec.budget_detail_id := p_budget_detail_id;
329 l_rec.parent_worksheet_detail_id := p_parent_worksheet_detail_id;
330 l_rec.user_id := p_user_id;
331 l_rec.action_cd := p_action_cd;
332 l_rec.budget_unit1_percent := p_budget_unit1_percent;
333 l_rec.budget_unit1_value := p_budget_unit1_value;
334 l_rec.budget_unit2_percent := p_budget_unit2_percent;
335 l_rec.budget_unit2_value := p_budget_unit2_value;
336 l_rec.budget_unit3_percent := p_budget_unit3_percent;
337 l_rec.budget_unit3_value := p_budget_unit3_value;
338 l_rec.object_version_number := p_object_version_number;
339 l_rec.budget_unit1_value_type_cd := p_budget_unit1_value_type_cd;
340 l_rec.budget_unit2_value_type_cd := p_budget_unit2_value_type_cd;
341 l_rec.budget_unit3_value_type_cd := p_budget_unit3_value_type_cd;
342 l_rec.status := p_status;
343 l_rec.budget_unit1_available := p_budget_unit1_available;
344 l_rec.budget_unit2_available := p_budget_unit2_available;
345 l_rec.budget_unit3_available := p_budget_unit3_available;
346 l_rec.old_unit1_value := p_old_unit1_value;
347 l_rec.old_unit2_value := p_old_unit2_value;
348 l_rec.old_unit3_value := p_old_unit3_value;
349 l_rec.defer_flag := p_defer_flag;
350 l_rec.propagation_method := p_propagation_method;
351 --
352 -- Return the plsql record structure.
353 --
354 hr_utility.set_location(' Leaving:'||l_proc, 10);
355 Return(l_rec);
356 --
357 End convert_args;
358 --
359 end pqh_wdt_shd;