[Home] [Help]
PACKAGE BODY: APPS.PQH_RHT_SHD
Source
1 Package Body pqh_rht_shd as
2 /* $Header: pqrhtrhi.pkb 115.7 2002/12/06 18:08:02 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rht_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_ROUTING_HISTORY_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_ROUTING_HISTORY_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_ROUTING_HISTORY_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_ROUTING_HISTORY_FK4') 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_ROUTING_HISTORY_FK5') 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_ROUTING_HISTORY_FK6') 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_ROUTING_HISTORY_FK7') 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_ROUTING_HISTORY_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 Else
62 hr_utility.set_message(801, 'HR_7877_API_INVALID_CONSTRAINT');
63 hr_utility.set_message_token('PROCEDURE', l_proc);
64 hr_utility.set_message_token('CONSTRAINT_NAME', p_constraint_name);
65 hr_utility.raise_error;
66 End If;
67 --
68 hr_utility.set_location(' Leaving:'||l_proc, 10);
69 End constraint_error;
70 --
71 -- ----------------------------------------------------------------------------
72 -- |-----------------------------< api_updating >-----------------------------|
73 -- ----------------------------------------------------------------------------
74 Function api_updating
75 (
76 p_routing_history_id in number,
77 p_object_version_number in number
78 ) Return Boolean Is
79 --
80 --
81 -- Cursor selects the 'current' row from the HR Schema
82 --
83 Cursor C_Sel1 is
84 select
85 routing_history_id,
86 approval_cd,
87 comments,
88 forwarded_by_assignment_id,
89 forwarded_by_member_id,
90 forwarded_by_position_id,
91 forwarded_by_user_id,
92 forwarded_by_role_id,
93 forwarded_to_assignment_id,
94 forwarded_to_member_id,
95 forwarded_to_position_id,
96 forwarded_to_user_id,
97 forwarded_to_role_id,
98 notification_date,
99 pos_structure_version_id,
100 routing_category_id,
101 transaction_category_id,
102 transaction_id,
103 user_action_cd,
104 from_range_name,
105 to_range_name,
106 list_range_name,
107 object_version_number
108 from pqh_routing_history
109 where routing_history_id = p_routing_history_id;
110 --
111 l_proc varchar2(72) := g_package||'api_updating';
112 l_fct_ret boolean;
113 --
114 Begin
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 If (
118 p_routing_history_id is null and
119 p_object_version_number is null
120 ) Then
121 --
122 -- One of the primary key arguments is null therefore we must
123 -- set the returning function value to false
124 --
125 l_fct_ret := false;
126 Else
127 If (
128 p_routing_history_id = g_old_rec.routing_history_id and
129 p_object_version_number = g_old_rec.object_version_number
130 ) Then
131 hr_utility.set_location(l_proc, 10);
132 --
133 -- The g_old_rec is current therefore we must
134 -- set the returning function to true
135 --
136 l_fct_ret := true;
137 Else
138 --
139 -- Select the current row into g_old_rec
140 --
141 Open C_Sel1;
142 Fetch C_Sel1 Into g_old_rec;
143 If C_Sel1%notfound Then
144 Close C_Sel1;
145 --
146 -- The primary key is invalid therefore we must error
147 --
148 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
149 hr_utility.raise_error;
150 End If;
151 Close C_Sel1;
152 If (p_object_version_number <> g_old_rec.object_version_number) Then
153 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
154 hr_utility.raise_error;
155 End If;
156 hr_utility.set_location(l_proc, 15);
157 l_fct_ret := true;
158 End If;
159 End If;
160 hr_utility.set_location(' Leaving:'||l_proc, 20);
161 Return (l_fct_ret);
162 --
163 End api_updating;
164 --
165 -- ----------------------------------------------------------------------------
166 -- |---------------------------------< lck >----------------------------------|
167 -- ----------------------------------------------------------------------------
168 Procedure lck
169 (
170 p_routing_history_id in number,
171 p_object_version_number in number
172 ) is
173 --
174 -- Cursor selects the 'current' row from the HR Schema
175 --
176 Cursor C_Sel1 is
177 select routing_history_id,
178 approval_cd,
179 comments,
180 forwarded_by_assignment_id,
181 forwarded_by_member_id,
182 forwarded_by_position_id,
183 forwarded_by_user_id,
184 forwarded_by_role_id,
185 forwarded_to_assignment_id,
186 forwarded_to_member_id,
187 forwarded_to_position_id,
188 forwarded_to_user_id,
189 forwarded_to_role_id,
190 notification_date,
191 pos_structure_version_id,
192 routing_category_id,
193 transaction_category_id,
194 transaction_id,
195 user_action_cd,
196 from_range_name,
197 to_range_name,
198 list_range_name,
199 object_version_number
200 from pqh_routing_history
201 where routing_history_id = p_routing_history_id
202 for update nowait;
203 --
204 l_proc varchar2(72) := g_package||'lck';
205 --
206 Begin
207 hr_utility.set_location('Entering:'||l_proc, 5);
208 --
209 -- Add any mandatory argument checking here:
210 -- Example:
211 -- hr_api.mandatory_arg_error
212 -- (p_api_name => l_proc,
213 -- p_argument => 'object_version_number',
214 -- p_argument_value => p_object_version_number);
215 --
216 Open C_Sel1;
217 Fetch C_Sel1 Into g_old_rec;
218 If C_Sel1%notfound then
219 Close C_Sel1;
220 --
221 -- The primary key is invalid therefore we must error
222 --
223 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
224 hr_utility.raise_error;
225 End If;
226 Close C_Sel1;
227 If (p_object_version_number <> g_old_rec.object_version_number) Then
228 hr_utility.set_message(801, 'HR_7155_OBJECT_INVALID');
229 hr_utility.raise_error;
230 End If;
231 --
232 hr_utility.set_location(' Leaving:'||l_proc, 10);
233 --
234 -- We need to trap the ORA LOCK exception
235 --
236 Exception
237 When HR_Api.Object_Locked then
238 --
239 -- The object is locked therefore we need to supply a meaningful
240 -- error message.
241 --
242 hr_utility.set_message(801, 'HR_7165_OBJECT_LOCKED');
243 hr_utility.set_message_token('TABLE_NAME', 'pqh_routing_history');
244 hr_utility.raise_error;
245 End lck;
246 --
247 -- ----------------------------------------------------------------------------
248 -- |-----------------------------< convert_args >-----------------------------|
249 -- ----------------------------------------------------------------------------
250 Function convert_args
251 (
252 p_routing_history_id in number,
253 p_approval_cd in varchar2,
254 p_comments in varchar2,
255 p_forwarded_by_assignment_id in number,
256 p_forwarded_by_member_id in number,
257 p_forwarded_by_position_id in number,
258 p_forwarded_by_user_id in number,
259 p_forwarded_by_role_id in number,
260 p_forwarded_to_assignment_id in number,
261 p_forwarded_to_member_id in number,
262 p_forwarded_to_position_id in number,
263 p_forwarded_to_user_id in number,
264 p_forwarded_to_role_id in number,
265 p_notification_date in date,
266 p_pos_structure_version_id in number,
267 p_routing_category_id in number,
268 p_transaction_category_id in number,
269 p_transaction_id in number,
270 p_user_action_cd in varchar2,
271 p_from_range_name in varchar2,
272 p_to_range_name in varchar2,
273 p_list_range_name in varchar2,
274 p_object_version_number in number
275 )
276 Return g_rec_type is
277 --
278 l_rec g_rec_type;
279 l_proc varchar2(72) := g_package||'convert_args';
280 --
281 Begin
282 --
283 hr_utility.set_location('Entering:'||l_proc, 5);
284 --
285 -- Convert arguments into local l_rec structure.
286 --
287 l_rec.routing_history_id := p_routing_history_id;
288 l_rec.approval_cd := p_approval_cd;
289 l_rec.comments := p_comments;
290 l_rec.forwarded_by_assignment_id := p_forwarded_by_assignment_id;
291 l_rec.forwarded_by_member_id := p_forwarded_by_member_id;
292 l_rec.forwarded_by_position_id := p_forwarded_by_position_id;
293 l_rec.forwarded_by_user_id := p_forwarded_by_user_id;
294 l_rec.forwarded_by_role_id := p_forwarded_by_role_id;
295 l_rec.forwarded_to_assignment_id := p_forwarded_to_assignment_id;
296 l_rec.forwarded_to_member_id := p_forwarded_to_member_id;
297 l_rec.forwarded_to_position_id := p_forwarded_to_position_id;
298 l_rec.forwarded_to_user_id := p_forwarded_to_user_id;
299 l_rec.forwarded_to_role_id := p_forwarded_to_role_id;
300 l_rec.notification_date := p_notification_date;
301 l_rec.pos_structure_version_id := p_pos_structure_version_id;
302 l_rec.routing_category_id := p_routing_category_id;
303 l_rec.transaction_category_id := p_transaction_category_id;
304 l_rec.transaction_id := p_transaction_id;
305 l_rec.user_action_cd := p_user_action_cd;
306 l_rec.from_range_name := p_from_range_name;
307 l_rec.to_range_name := p_to_range_name;
308 l_rec.list_range_name := p_list_range_name;
309 l_rec.object_version_number := p_object_version_number;
310 --
311 -- Return the plsql record structure.
312 --
313 hr_utility.set_location(' Leaving:'||l_proc, 10);
314 Return(l_rec);
315 --
316 End convert_args;
317 --
318 end pqh_rht_shd;