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