1 PACKAGE BODY PER_LETTER_GEN_STATUSES_PKG as
2 /* $Header: pelts01t.pkb 115.2 99/07/18 14:01:54 porting ship $ */
3
4 procedure ASSIGNMENT_STATUS_NOT_UNIQUE (
5 --
6 -- Returns TRUE if the assignment status is not unique, then the check is for
7 -- new record within generic data
8 --
9 -- Parameters are:
10 --
11 p_business_group_id in number,
12 p_assignment_status_type_id in number,
13 p_letter_type_id in number,
14 p_letter_gen_status_id in number) is
15 --
16 v_not_unique boolean := FALSE;
17 g_dummy_number number;
18 --
19 cursor csr_duplicate is
20 select null
21 from per_letter_gen_statuses x
22 where x.business_group_id + 0 = p_business_group_id
23 and x.assignment_status_type_id = p_assignment_status_type_id
24 and x.letter_type_id = p_letter_type_id
25 and (p_letter_gen_status_id is null
26 or (p_letter_gen_status_id is not null
27 and x.letter_gen_status_id <> p_letter_gen_status_id));
28 --
29 begin
30 --
31 open csr_duplicate;
32 fetch csr_duplicate into g_dummy_number;
33 v_not_unique := csr_duplicate%found;
34 close csr_duplicate;
35 --
36 if v_not_unique then
37 hr_utility.set_message (801,'PER_7859_DEF_LETTER_STAT_EXIST');
38 hr_utility.raise_error;
39 end if;
40 --
41 end assignment_status_not_unique;
42 --
43 --
44 procedure get_next_sequence(p_letter_gen_status_id in out number) is
45 --
46 cursor c1 is select per_letter_gen_statuses_s.nextval
47 from sys.dual;
48 --
49 -- Retrieve the nnext sequence number for letter_gen_status_id field
50 --
51 begin
52 --
53 if (p_letter_gen_status_id is null) then
54 open c1;
55 fetch c1 into p_letter_gen_status_id;
56 if (C1%NOTFOUND) then
57 CLOSE C1;
58 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
59 hr_utility.set_message_token('PROCEDURE','get_next_sequence');
60 hr_utility.set_message_token('STEP','1');
61 hr_utility.raise_error;
62 end if;
63 close c1;
64 end if;
65 end get_next_sequence;
66 --
67 procedure get_assignment_status(p_assignment_status_type_id in number,
68 p_assignment_status in out varchar2) is
69 --cursor c1 is select assignment_status
70 --from assignment_status_lov
71 --where p_assignment_status_type_id = assignment_status_type_id;
72 cursor c1 is select nvl(atl.user_status, ttl.user_status)
73 from per_assignment_status_types_tl ttl,
74 per_assignment_status_types t,
75 per_ass_status_type_amends_tl atl,
76 per_ass_status_type_amends a
77 where t.assignment_status_type_id = p_assignment_status_type_id
78 and a.assignment_status_type_id (+) = t.assignment_status_type_id
79 and a.ass_status_type_amend_id = atl.ass_status_type_amend_id (+)
80 and decode(atl.ass_status_type_amend_id, null, '1', userenv('LANG')) =
81 decode(atl.ass_status_type_amend_id, null, '1', atl.LANGUAGE)
82 and t.assignment_status_type_id = ttl.assignment_status_type_id
83 and ttl.LANGUAGE = userenv('LANG');
84
85 --
86 -- Get the assignment status value for the non-database field
87 --
88 begin
89 --
90 open c1;
91 fetch c1 into p_assignment_status;
92 if (C1%NOTFOUND) then
93 CLOSE C1;
94 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
95 hr_utility.set_message_token('PROCEDURE','get_assignment_status');
96 hr_utility.set_message_token('STEP','1');
97 hr_utility.raise_error;
98 end if;
99 close c1;
100 --
101 end get_assignment_status;
102 --
103 END PER_LETTER_GEN_STATUSES_PKG;
[Home] [Help]