1 PACKAGE ghr_utility AS
2 /* $Header: ghutils.pkh 120.10 2005/12/09 04:29:48 sumarimu noship $ */
3 --
4 g_position_being_deleted BOOLEAN;
5 --
6 FUNCTION is_ghr RETURN VARCHAR2;
7
8 FUNCTION is_ghr_ben RETURN VARCHAR2;
9
10 FUNCTION is_ghr_ben_fehb RETURN VARCHAR2;
11
12 FUNCTION is_ghr_ben_tsp RETURN VARCHAR2;
13
14 FUNCTION is_ghr_nfc RETURN VARCHAR2;
15
16 PROCEDURE set_client_info
17 ( p_person_id in per_all_people_f.person_id%type default null,
18 p_position_id in hr_all_positions_f.position_id%type default null,
19 p_assignment_id in per_all_assignments_f.assignment_id%type default null);
20
21 FUNCTION get_noa_code (p_nature_of_action_id IN NUMBER)
22 RETURN VARCHAR2;
23 -- Sundar NFC Changes
24 FUNCTION get_flex_num(p_flex_code fnd_id_flex_structures_tl.id_flex_code%TYPE,
25 p_struct_name fnd_id_flex_structures_tl.id_flex_structure_name%TYPE) RETURN NUMBER;
26
27 FUNCTION get_pos_flex_num(p_bus_org_id hr_all_organization_units.business_group_id%type) RETURN NUMBER;
28
29 FUNCTION get_flex_delimiter(p_flex_code fnd_id_flex_segments_vl.id_flex_code%type,
30 p_flex_num fnd_id_flex_structures_vl.id_flex_num%type) RETURN VARCHAR2;
31
32 TYPE t_flex_recs IS TABLE OF FND_ID_FLEX_SEGMENTS_VL%ROWTYPE INDEX BY BINARY_INTEGER;
33 l_flex_recs t_flex_recs;
34
35 FUNCTION get_segments(p_flex_num fnd_id_flex_structures_tl.id_flex_num%type,
36 p_flex_code fnd_id_flex_segments_vl.id_flex_code%type) RETURN t_flex_recs;
37
38
39 FUNCTION return_pos_name(l_pos_title per_position_definitions.segment1%type,
40 l_pos_desc per_position_definitions.segment1%type,
41 l_seq_no per_position_definitions.segment1%type,
42 l_agency_code per_position_definitions.segment1%type,
43 l_po_id per_position_definitions.segment1%type,
44 l_grade per_position_definitions.segment1%type,
45 l_nfc_agency_code per_position_definitions.segment1%type,
46 l_full_title hr_positions_f.name%type)
47 RETURN VARCHAR2;
48
49 FUNCTION return_nfc_pos_name(l_pos_title per_position_definitions.segment1%type,
50 l_pos_desc per_position_definitions.segment1%type,
51 l_seq_no per_position_definitions.segment1%type,
52 l_agency_code per_position_definitions.segment1%type,
53 l_po_id per_position_definitions.segment1%type,
54 l_grade per_position_definitions.segment1%type,
55 l_nfc_agency_code per_position_definitions.segment1%type,
56 l_full_title hr_positions_f.name%type)
57 RETURN VARCHAR2;
58
59 -- End NFC Changes
60
61 PROCEDURE validate_nfc(
62 P_POSITION_ID in NUMBER
63 ,P_SEGMENT1 in VARCHAR2
64 ,P_SEGMENT2 in VARCHAR2
65 ,P_SEGMENT3 in VARCHAR2
66 ,P_SEGMENT4 in VARCHAR2
67 ,P_SEGMENT5 in VARCHAR2
68 ,P_SEGMENT6 in VARCHAR2
69 ,P_SEGMENT7 in VARCHAR2
70 ,P_SEGMENT8 in VARCHAR2
71 ,P_SEGMENT9 in VARCHAR2
72 ,P_SEGMENT10 in VARCHAR2
73 ,P_SEGMENT11 in VARCHAR2
74 ,P_SEGMENT12 in VARCHAR2
75 ,P_SEGMENT13 in VARCHAR2
76 ,P_SEGMENT14 in VARCHAR2
77 ,P_SEGMENT15 in VARCHAR2
78 ,P_SEGMENT16 in VARCHAR2
79 ,P_SEGMENT17 in VARCHAR2
80 ,P_SEGMENT18 in VARCHAR2
81 ,P_SEGMENT19 in VARCHAR2
82 ,P_SEGMENT20 in VARCHAR2
83 ,P_SEGMENT21 in VARCHAR2
84 ,P_SEGMENT22 in VARCHAR2
85 ,P_SEGMENT23 in VARCHAR2
86 ,P_SEGMENT24 in VARCHAR2
87 ,P_SEGMENT25 in VARCHAR2
88 ,P_SEGMENT26 in VARCHAR2
89 ,P_SEGMENT27 in VARCHAR2
90 ,P_SEGMENT28 in VARCHAR2
91 ,P_SEGMENT29 in VARCHAR2
92 ,P_SEGMENT30 in VARCHAR2
93 ,P_INFORMATION6 in VARCHAR2
94 ,P_EFFECTIVE_DATE in DATE
95 ,P_LANGUAGE_CODE in VARCHAR2 );
96
97 PROCEDURE update_nfc_eit(
98 P_POSITION_ID in NUMBER
99 ,P_SEGMENT1 in VARCHAR2
103 ,P_SEGMENT5 in VARCHAR2
100 ,P_SEGMENT2 in VARCHAR2
101 ,P_SEGMENT3 in VARCHAR2
102 ,P_SEGMENT4 in VARCHAR2
104 ,P_SEGMENT6 in VARCHAR2
105 ,P_SEGMENT7 in VARCHAR2
106 ,P_SEGMENT8 in VARCHAR2
107 ,P_SEGMENT9 in VARCHAR2
108 ,P_SEGMENT10 in VARCHAR2
109 ,P_SEGMENT11 in VARCHAR2
110 ,P_SEGMENT12 in VARCHAR2
111 ,P_SEGMENT13 in VARCHAR2
112 ,P_SEGMENT14 in VARCHAR2
113 ,P_SEGMENT15 in VARCHAR2
114 ,P_SEGMENT16 in VARCHAR2
115 ,P_SEGMENT17 in VARCHAR2
116 ,P_SEGMENT18 in VARCHAR2
117 ,P_SEGMENT19 in VARCHAR2
118 ,P_SEGMENT20 in VARCHAR2
119 ,P_SEGMENT21 in VARCHAR2
120 ,P_SEGMENT22 in VARCHAR2
121 ,P_SEGMENT23 in VARCHAR2
122 ,P_SEGMENT24 in VARCHAR2
123 ,P_SEGMENT25 in VARCHAR2
124 ,P_SEGMENT26 in VARCHAR2
125 ,P_SEGMENT27 in VARCHAR2
126 ,P_SEGMENT28 in VARCHAR2
127 ,P_SEGMENT29 in VARCHAR2
128 ,P_SEGMENT30 in VARCHAR2
132
129 ,P_INFORMATION6 in VARCHAR2
130 ,P_EFFECTIVE_DATE in DATE
131 ,P_LANGUAGE_CODE in VARCHAR2);
133
134 PROCEDURE validate_create_nfc(
135 P_SEGMENT1 in VARCHAR2
136 ,P_SEGMENT2 in VARCHAR2
137 ,P_SEGMENT3 in VARCHAR2
138 ,P_SEGMENT4 in VARCHAR2
139 ,P_SEGMENT5 in VARCHAR2
140 ,P_SEGMENT6 in VARCHAR2
141 ,P_SEGMENT7 in VARCHAR2
142 ,P_SEGMENT8 in VARCHAR2
143 ,P_SEGMENT9 in VARCHAR2
144 ,P_SEGMENT10 in VARCHAR2
145 ,P_SEGMENT11 in VARCHAR2
146 ,P_SEGMENT12 in VARCHAR2
147 ,P_SEGMENT13 in VARCHAR2
148 ,P_SEGMENT14 in VARCHAR2
149 ,P_SEGMENT15 in VARCHAR2
150 ,P_SEGMENT16 in VARCHAR2
151 ,P_SEGMENT17 in VARCHAR2
152 ,P_SEGMENT18 in VARCHAR2
153 ,P_SEGMENT19 in VARCHAR2
154 ,P_SEGMENT20 in VARCHAR2
155 ,P_SEGMENT21 in VARCHAR2
156 ,P_SEGMENT22 in VARCHAR2
157 ,P_SEGMENT23 in VARCHAR2
158 ,P_SEGMENT24 in VARCHAR2
159 ,P_SEGMENT25 in VARCHAR2
160 ,P_SEGMENT26 in VARCHAR2
161 ,P_SEGMENT27 in VARCHAR2
162 ,P_SEGMENT28 in VARCHAR2
163 ,P_SEGMENT29 in VARCHAR2
164 ,P_SEGMENT30 in VARCHAR2
165 ,P_INFORMATION6 in VARCHAR2
166 ,P_EFFECTIVE_DATE in DATE
167 ,P_LANGUAGE_CODE in VARCHAR2 );
168
169 PROCEDURE validate_delete_nfc(
170 P_POSITION_ID in NUMBER
171 ,P_EFFECTIVE_DATE in DATE
172 );
173
174
175 procedure process_nfc_auth_date(
176 p_effective_date in ghr_pa_requests.effective_date%type,
177 p_pa_request_id in ghr_pa_requests.pa_request_id%type);
178
179 function get_nfc_prev_noa(
180 p_person_id in per_people_f.person_id%type,
181 p_pa_notification_id in ghr_pa_requests.pa_notification_id%type,
182 p_effective_date in ghr_pa_requests.effective_date%type)
183 RETURN VARCHAR2;
184
185 procedure get_nfc_auth_codes(
186 p_person_id in per_people_f.person_id%type,
187 p_pa_notification_id in ghr_pa_requests.pa_notification_id%type,
188 p_effective_date in ghr_pa_requests.effective_date%type,
189 p_first_auth_code out nocopy ghr_pa_requests.FIRST_ACTION_LA_CODE1%type,
190 p_second_auth_code out nocopy ghr_pa_requests.FIRST_ACTION_LA_CODE1%type);
191
195
192 function get_nfc_conv_action_code(
193 p_pa_request_id in ghr_pa_requests.pa_request_id%type)
194 RETURN NUMBER;
196 end ghr_utility;