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