[Home] [Help]
PACKAGE BODY: APPS.PSP_ERD_SHD
Source
1 Package Body psp_erd_shd as
2 /* $Header: PSPEDRHB.pls 120.2 2006/01/25 01:49 dpaudel noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' psp_erd_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'SYS_C00145225') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 Else
27 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30 fnd_message.raise_error;
31 End If;
32 --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39 (p_effort_report_detail_id in number
40 ,p_object_version_number in number
41 )
42 Return Boolean Is
43 --
44 --
45 -- Cursor selects the 'current' row from the HR Schema
46 --
47 Cursor C_Sel1 is
48 select
49 effort_report_detail_id
50 ,effort_report_id
51 ,object_version_number
52 ,assignment_id
53 ,assignment_number
54 ,gl_sum_criteria_segment_name
55 ,gl_segment1
56 ,gl_segment2
57 ,gl_segment3
58 ,gl_segment4
59 ,gl_segment5
60 ,gl_segment6
61 ,gl_segment7
62 ,gl_segment8
63 ,gl_segment9
64 ,gl_segment10
65 ,gl_segment11
66 ,gl_segment12
67 ,gl_segment13
68 ,gl_segment14
69 ,gl_segment15
70 ,gl_segment16
71 ,gl_segment17
72 ,gl_segment18
73 ,gl_segment19
74 ,gl_segment20
75 ,gl_segment21
76 ,gl_segment22
77 ,gl_segment23
78 ,gl_segment24
79 ,gl_segment25
80 ,gl_segment26
81 ,gl_segment27
82 ,gl_segment28
83 ,gl_segment29
84 ,gl_segment30
85 ,project_id
86 ,project_number
87 ,project_name
88 ,expenditure_organization_id
89 ,exp_org_name
90 ,expenditure_type
91 ,task_id
92 ,task_number
93 ,task_name
94 ,award_id
95 ,award_number
96 ,award_short_name
97 ,actual_salary_amt
98 ,payroll_percent
99 ,proposed_salary_amt
100 ,proposed_effort_percent
101 ,committed_cost_share
102 ,schedule_start_date
103 ,schedule_end_date
104 ,ame_transaction_id
105 ,investigator_name
106 ,investigator_person_id
107 ,investigator_org_name
108 ,investigator_primary_org_id
109 ,value1
110 ,value2
111 ,value3
112 ,value4
113 ,value5
114 ,value6
115 ,value7
116 ,value8
117 ,value9
118 ,value10
119 ,attribute1
120 ,attribute2
121 ,attribute3
122 ,attribute4
123 ,attribute5
124 ,attribute6
125 ,attribute7
126 ,attribute8
127 ,attribute9
128 ,attribute10
129 ,grouping_category
130 from psp_eff_report_details
131 where effort_report_detail_id = p_effort_report_detail_id;
132 --
133 l_fct_ret boolean;
134 --
135 Begin
136 --
137 If (p_effort_report_detail_id is null and
138 p_object_version_number is null
139 ) Then
140 --
141 -- One of the primary key arguments is null therefore we must
142 -- set the returning function value to false
143 --
144 l_fct_ret := false;
145 Else
146 If (p_effort_report_detail_id
147 = psp_erd_shd.g_old_rec.effort_report_detail_id and
148 p_object_version_number
149 = psp_erd_shd.g_old_rec.object_version_number
150 ) Then
151 --
152 -- The g_old_rec is current therefore we must
153 -- set the returning function to true
154 --
155 l_fct_ret := true;
156 Else
157 --
158 -- Select the current row into g_old_rec
159 --
160 Open C_Sel1;
161 Fetch C_Sel1 Into psp_erd_shd.g_old_rec;
162 If C_Sel1%notfound Then
163 Close C_Sel1;
164 --
165 -- The primary key is invalid therefore we must error
166 --
167 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
168 fnd_message.raise_error;
169 End If;
170 Close C_Sel1;
171 If (p_object_version_number
172 <> psp_erd_shd.g_old_rec.object_version_number) Then
173 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
174 fnd_message.raise_error;
175 End If;
176 l_fct_ret := true;
177 End If;
178 End If;
179 Return (l_fct_ret);
180 --
181 End api_updating;
182 --
183 -- ----------------------------------------------------------------------------
184 -- |---------------------------------< lck >----------------------------------|
185 -- ----------------------------------------------------------------------------
186 Procedure lck
187 (p_effort_report_detail_id in number
188 ,p_object_version_number in number
189 ) is
190 --
191 -- Cursor selects the 'current' row from the HR Schema
192 --
193 Cursor C_Sel1 is
194 select
195 effort_report_detail_id
196 ,effort_report_id
197 ,object_version_number
198 ,assignment_id
199 ,assignment_number
200 ,gl_sum_criteria_segment_name
201 ,gl_segment1
202 ,gl_segment2
203 ,gl_segment3
204 ,gl_segment4
205 ,gl_segment5
206 ,gl_segment6
207 ,gl_segment7
208 ,gl_segment8
209 ,gl_segment9
210 ,gl_segment10
211 ,gl_segment11
212 ,gl_segment12
213 ,gl_segment13
214 ,gl_segment14
215 ,gl_segment15
216 ,gl_segment16
217 ,gl_segment17
218 ,gl_segment18
219 ,gl_segment19
220 ,gl_segment20
221 ,gl_segment21
222 ,gl_segment22
223 ,gl_segment23
224 ,gl_segment24
225 ,gl_segment25
226 ,gl_segment26
227 ,gl_segment27
228 ,gl_segment28
229 ,gl_segment29
230 ,gl_segment30
231 ,project_id
232 ,project_number
233 ,project_name
234 ,expenditure_organization_id
235 ,exp_org_name
236 ,expenditure_type
237 ,task_id
238 ,task_number
239 ,task_name
240 ,award_id
241 ,award_number
242 ,award_short_name
243 ,actual_salary_amt
244 ,payroll_percent
245 ,proposed_salary_amt
246 ,proposed_effort_percent
247 ,committed_cost_share
248 ,schedule_start_date
249 ,schedule_end_date
250 ,ame_transaction_id
251 ,investigator_name
252 ,investigator_person_id
253 ,investigator_org_name
254 ,investigator_primary_org_id
255 ,value1
256 ,value2
257 ,value3
258 ,value4
259 ,value5
260 ,value6
261 ,value7
262 ,value8
263 ,value9
264 ,value10
265 ,attribute1
266 ,attribute2
267 ,attribute3
268 ,attribute4
269 ,attribute5
270 ,attribute6
271 ,attribute7
272 ,attribute8
273 ,attribute9
274 ,attribute10
275 ,grouping_category
276 from psp_eff_report_details
277 where effort_report_detail_id = p_effort_report_detail_id
278 for update nowait;
279 --
280 l_proc varchar2(72) := g_package||'lck';
281 --
282 Begin
283 hr_utility.set_location('Entering:'||l_proc, 5);
284 --
285 hr_api.mandatory_arg_error
286 (p_api_name => l_proc
287 ,p_argument => 'EFFORT_REPORT_DETAIL_ID'
288 ,p_argument_value => p_effort_report_detail_id
289 );
290 hr_utility.set_location(l_proc,6);
291 hr_api.mandatory_arg_error
292 (p_api_name => l_proc
293 ,p_argument => 'OBJECT_VERSION_NUMBER'
294 ,p_argument_value => p_object_version_number
295 );
296 --
297 Open C_Sel1;
298 Fetch C_Sel1 Into psp_erd_shd.g_old_rec;
299 If C_Sel1%notfound then
300 Close C_Sel1;
301 --
302 -- The primary key is invalid therefore we must error
303 --
304 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
305 fnd_message.raise_error;
306 End If;
307 Close C_Sel1;
308 If (p_object_version_number
309 <> psp_erd_shd.g_old_rec.object_version_number) Then
310 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
311 fnd_message.raise_error;
312 End If;
313 --
314 hr_utility.set_location(' Leaving:'||l_proc, 10);
315 --
316 -- We need to trap the ORA LOCK exception
317 --
318 Exception
319 When HR_Api.Object_Locked then
320 --
321 -- The object is locked therefore we need to supply a meaningful
322 -- error message.
323 --
324 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
325 fnd_message.set_token('TABLE_NAME', 'psp_eff_report_details');
326 fnd_message.raise_error;
327 End lck;
328 --
329 -- ----------------------------------------------------------------------------
330 -- |-----------------------------< convert_args >-----------------------------|
331 -- ----------------------------------------------------------------------------
332 Function convert_args
333 (p_effort_report_detail_id in number
334 ,p_effort_report_id in number
335 ,p_object_version_number in number
336 ,p_assignment_id in number
337 ,p_assignment_number in varchar2
338 ,p_gl_sum_criteria_segment_name in varchar2
339 ,p_gl_segment1 in varchar2
340 ,p_gl_segment2 in varchar2
341 ,p_gl_segment3 in varchar2
342 ,p_gl_segment4 in varchar2
343 ,p_gl_segment5 in varchar2
344 ,p_gl_segment6 in varchar2
345 ,p_gl_segment7 in varchar2
346 ,p_gl_segment8 in varchar2
347 ,p_gl_segment9 in varchar2
348 ,p_gl_segment10 in varchar2
349 ,p_gl_segment11 in varchar2
350 ,p_gl_segment12 in varchar2
351 ,p_gl_segment13 in varchar2
352 ,p_gl_segment14 in varchar2
353 ,p_gl_segment15 in varchar2
354 ,p_gl_segment16 in varchar2
355 ,p_gl_segment17 in varchar2
356 ,p_gl_segment18 in varchar2
357 ,p_gl_segment19 in varchar2
358 ,p_gl_segment20 in varchar2
359 ,p_gl_segment21 in varchar2
360 ,p_gl_segment22 in varchar2
361 ,p_gl_segment23 in varchar2
365 ,p_gl_segment27 in varchar2
362 ,p_gl_segment24 in varchar2
363 ,p_gl_segment25 in varchar2
364 ,p_gl_segment26 in varchar2
366 ,p_gl_segment28 in varchar2
367 ,p_gl_segment29 in varchar2
368 ,p_gl_segment30 in varchar2
369 ,p_project_id in number
370 ,p_project_number in varchar2
371 ,p_project_name in varchar2
372 ,p_expenditure_organization_id in number
373 ,p_exp_org_name in varchar2
374 ,p_expenditure_type in varchar2
375 ,p_task_id in number
376 ,p_task_number in varchar2
377 ,p_task_name in varchar2
378 ,p_award_id in number
379 ,p_award_number in varchar2
380 ,p_award_short_name in varchar2
381 ,p_actual_salary_amt in number
382 ,p_payroll_percent in number
383 ,p_proposed_salary_amt in number
384 ,p_proposed_effort_percent in number
385 ,p_committed_cost_share in number
386 ,p_schedule_start_date in date
387 ,p_schedule_end_date in date
388 ,p_ame_transaction_id in varchar2
389 ,p_investigator_name in varchar2
390 ,p_investigator_person_id in number
391 ,p_investigator_org_name in varchar2
392 ,p_investigator_primary_org_id in number
393 ,p_value1 in number
394 ,p_value2 in number
395 ,p_value3 in number
396 ,p_value4 in number
397 ,p_value5 in number
398 ,p_value6 in number
399 ,p_value7 in number
400 ,p_value8 in number
401 ,p_value9 in number
402 ,p_value10 in number
403 ,p_attribute1 in varchar2
404 ,p_attribute2 in varchar2
405 ,p_attribute3 in varchar2
406 ,p_attribute4 in varchar2
407 ,p_attribute5 in varchar2
408 ,p_attribute6 in varchar2
409 ,p_attribute7 in varchar2
410 ,p_attribute8 in varchar2
411 ,p_attribute9 in varchar2
412 ,p_attribute10 in varchar2
413 ,p_grouping_category in varchar2
414 )
415 Return g_rec_type is
416 --
417 l_rec g_rec_type;
418 --
419 Begin
420 --
421 -- Convert arguments into local l_rec structure.
422 --
423 l_rec.effort_report_detail_id := p_effort_report_detail_id;
424 l_rec.effort_report_id := p_effort_report_id;
425 l_rec.object_version_number := p_object_version_number;
426 l_rec.assignment_id := p_assignment_id;
427 l_rec.assignment_number := p_assignment_number;
428 l_rec.gl_sum_criteria_segment_name := p_gl_sum_criteria_segment_name;
429 l_rec.gl_segment1 := p_gl_segment1;
430 l_rec.gl_segment2 := p_gl_segment2;
431 l_rec.gl_segment3 := p_gl_segment3;
432 l_rec.gl_segment4 := p_gl_segment4;
433 l_rec.gl_segment5 := p_gl_segment5;
434 l_rec.gl_segment6 := p_gl_segment6;
435 l_rec.gl_segment7 := p_gl_segment7;
436 l_rec.gl_segment8 := p_gl_segment8;
437 l_rec.gl_segment9 := p_gl_segment9;
438 l_rec.gl_segment10 := p_gl_segment10;
439 l_rec.gl_segment11 := p_gl_segment11;
440 l_rec.gl_segment12 := p_gl_segment12;
441 l_rec.gl_segment13 := p_gl_segment13;
442 l_rec.gl_segment14 := p_gl_segment14;
443 l_rec.gl_segment15 := p_gl_segment15;
444 l_rec.gl_segment16 := p_gl_segment16;
445 l_rec.gl_segment17 := p_gl_segment17;
446 l_rec.gl_segment18 := p_gl_segment18;
447 l_rec.gl_segment19 := p_gl_segment19;
448 l_rec.gl_segment20 := p_gl_segment20;
449 l_rec.gl_segment21 := p_gl_segment21;
450 l_rec.gl_segment22 := p_gl_segment22;
451 l_rec.gl_segment23 := p_gl_segment23;
452 l_rec.gl_segment24 := p_gl_segment24;
453 l_rec.gl_segment25 := p_gl_segment25;
454 l_rec.gl_segment26 := p_gl_segment26;
455 l_rec.gl_segment27 := p_gl_segment27;
456 l_rec.gl_segment28 := p_gl_segment28;
457 l_rec.gl_segment29 := p_gl_segment29;
458 l_rec.gl_segment30 := p_gl_segment30;
459 l_rec.project_id := p_project_id;
460 l_rec.project_number := p_project_number;
461 l_rec.project_name := p_project_name;
462 l_rec.expenditure_organization_id := p_expenditure_organization_id;
463 l_rec.exp_org_name := p_exp_org_name;
464 l_rec.expenditure_type := p_expenditure_type;
465 l_rec.task_id := p_task_id;
466 l_rec.task_number := p_task_number;
467 l_rec.task_name := p_task_name;
468 l_rec.award_id := p_award_id;
469 l_rec.award_number := p_award_number;
470 l_rec.award_short_name := p_award_short_name;
471 l_rec.actual_salary_amt := p_actual_salary_amt;
472 l_rec.payroll_percent := p_payroll_percent;
473 l_rec.proposed_salary_amt := p_proposed_salary_amt;
474 l_rec.proposed_effort_percent := p_proposed_effort_percent;
475 l_rec.committed_cost_share := p_committed_cost_share;
476 l_rec.schedule_start_date := p_schedule_start_date;
477 l_rec.schedule_end_date := p_schedule_end_date;
478 l_rec.ame_transaction_id := p_ame_transaction_id;
479 l_rec.investigator_name := p_investigator_name;
480 l_rec.investigator_person_id := p_investigator_person_id;
481 l_rec.investigator_org_name := p_investigator_org_name;
482 l_rec.investigator_primary_org_id := p_investigator_primary_org_id;
483 l_rec.value1 := p_value1;
484 l_rec.value2 := p_value2;
485 l_rec.value3 := p_value3;
486 l_rec.value4 := p_value4;
487 l_rec.value5 := p_value5;
488 l_rec.value6 := p_value6;
489 l_rec.value7 := p_value7;
490 l_rec.value8 := p_value8;
491 l_rec.value9 := p_value9;
492 l_rec.value10 := p_value10;
493 l_rec.attribute1 := p_attribute1;
494 l_rec.attribute2 := p_attribute2;
495 l_rec.attribute3 := p_attribute3;
496 l_rec.attribute4 := p_attribute4;
497 l_rec.attribute5 := p_attribute5;
498 l_rec.attribute6 := p_attribute6;
499 l_rec.attribute7 := p_attribute7;
500 l_rec.attribute8 := p_attribute8;
501 l_rec.attribute9 := p_attribute9;
502 l_rec.attribute10 := p_attribute10;
503 l_rec.grouping_category := p_grouping_category;
504 --
505 -- Return the plsql record structure.
506 --
507 Return(l_rec);
508 --
509 End convert_args;
510 --
511 end psp_erd_shd;