1 PACKAGE BODY pqp_gb_tp_extract_functions AS
2 -- /* $Header: pqgbtpxf.pkb 120.1 2006/02/06 05:49:38 bsamuel noship $ */
3 --
4 --
5 -- GET_CURRENT_EXTRACT_PERSON
6 --
7 -- Returns the ext_rslt_id for the current extract process
8 -- if one is running, else returns -1
9 --
10 FUNCTION get_current_extract_person
11 (p_assignment_id NUMBER -- context
12 )
13 RETURN NUMBER
14 IS
15 l_person_id NUMBER;
16 BEGIN
17 SELECT person_id
18 INTO l_person_id
19 FROM per_all_assignments_f
20 WHERE assignment_id = p_assignment_id
21 AND ROWNUM < 2;
22 RETURN l_person_id;
23 EXCEPTION
24 WHEN NO_DATA_FOUND THEN
25 RETURN NULL;
26 END;
27 --
28 -- GET_CURRENT_EXTRACT_RESULT
29 --
30 -- Returns the person id associated with the given assignment.
31 -- If none is found,it returns NULL. This may arise if the
32 -- user calls this from a header/trailer record, where
33 -- a dummy context of assignment_id = -1 is passed.
34 --
35 --
36 FUNCTION get_current_extract_result
37 RETURN NUMBER
38 IS
39 e_extract_process_not_running EXCEPTION;
40 PRAGMA EXCEPTION_INIT(e_extract_process_not_running,-8002);
41 l_ext_rslt_id NUMBER;
42 --
43 BEGIN
44 --
45 -- SELECT ben_ext_rslt_s.CURRVAL
46 -- INTO l_ext_rslt_id
47 -- FROM DUAL;
48
49 l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
50
51 RETURN l_ext_rslt_id;
52 --
53 EXCEPTION
54 WHEN e_extract_process_not_running THEN
55 RETURN -1;
56 END;
57 --
58 -- RAISE_EXTRACT_WARNING
59 --
60 -- "Smart" warning function.
61 -- When called from the Rule of a extract detail data element
62 -- it logs a warning in the ben_ext_rslt_err table against
63 -- the person being processed (or as specified by context of
64 -- assignment id ). It prefixes all warning messages with a
65 -- string "Warning raised in data element "||element_name
66 -- This allows the same Rule to be called from different data
67 -- elements.
68 --
69 -- usage example.
70 --
71 -- RAISE_EXTRACT_WARNING("No initials were found.")
72 --
73 -- RRTURNCODE MEANING
74 -- -1 Cannot raise warning against a header/trailer
75 -- record. System Extract does not allow it.
76 --
77 -- -2 No current extract process was found.
78 --
79 -- -3 No person was found.A Warning in System Extract
80 -- is always raised against a person.
81 --
82 FUNCTION raise_extract_warning
83 (p_assignment_id IN NUMBER -- context
84 ,p_error_text IN VARCHAR2
85 ,p_error_number IN NUMBER DEFAULT NULL
86 ,p_token1 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
87 ,p_token2 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
88 ) RETURN NUMBER
89 IS
90 l_ext_rslt_id NUMBER;
91 l_person_id NUMBER;
92 l_error_text VARCHAR2(2000);
93 l_return_value NUMBER:= 0;
94 BEGIN
95 --
96 IF p_assignment_id <> -1 THEN
97 --
98 l_ext_rslt_id:= get_current_extract_result;
99
100 IF l_ext_rslt_id <> -1 THEN
101 --
102
103 -- l_error_text:= 'Warning raised in data element ' || p_error_text;
104 --
105 -- Commented by VTAKRU Nov 16th this due to 2114438 as this
106 -- BEN pkg code is checked under BEN patch instead of PER
107 --
108 -- Re-introduced for Jan 2002 release as all products are being released
109 -- together as a family pack and minimum pre-req for Jan release will be
110 -- the October mini pack.
111 --
112 -- Modified for type 2
113
114 If p_error_number is null Then
115
116 l_error_text:= 'Warning raised in data element '||
117 ben_ext_fmt.g_elmt_name||'. '||
118 p_error_text;
119 --if no message token is defined then egt the message from
120 --ben_ext_fmt.
121 Elsif p_token1 is null Then
122
123 ben_ext_thread.g_err_num := p_error_number;
124 ben_ext_thread.g_err_name := p_error_text;
125 l_error_text :=
126 ben_ext_fmt.get_error_msg(to_number(substr(p_error_text, 5, 5)),
127 p_error_text,ben_ext_fmt.g_elmt_name);
128
129 -- if any token is defined than replace the tokens in the message.
130 -- and get the message text from fnd_messages.
131 Elsif p_token1 is not null Then
132
133 -- set the Tokens in the warning message and then
134 -- get the warning message from fnd_messages.
135
136 ben_ext_thread.g_err_num := p_error_number;
137 ben_ext_thread.g_err_name := p_error_text;
138
139 fnd_message.set_name('BEN',p_error_text);
140 fnd_message.set_token('TOKEN1',p_token1);
141
142 if p_token2 is not null Then
143 fnd_message.set_token('TOKEN2',p_token2);
144 end if;
145
146 l_error_text := fnd_message.get ;
147
148 End If;
149
150
151 l_person_id:= NVL(get_current_extract_person(p_assignment_id)
152 ,ben_ext_person.g_person_id);
153
154 IF l_person_id IS NOT NULL THEN
155 --
156 ben_ext_util.write_err
157 (p_err_num => p_error_number
158 ,p_err_name => l_error_text
159 ,p_typ_cd => 'W'
160 ,p_person_id => l_person_id
161 ,p_request_id => fnd_global.conc_request_id
162 ,p_business_group_id => fnd_global.per_business_group_id
163 ,p_ext_rslt_id => get_current_extract_result
164 );
165 l_return_value:= 0; /* All Well ! */
166 --
167 ELSE
168 --
169 l_return_value:= -3; /* Person not found */
170 --
171 END IF;
172 --
173 ELSE
174 --
175 l_return_value:= -2; /* No current extract process was found */
176 --
177 END IF;
178 --
179 ELSE
180 --
181 l_return_value := -1; /* Cannot raise warnings against header/trailers */
182 --
183 END IF;
184 --
185 RETURN l_return_value;
186 END raise_extract_warning;
187 --
188 FUNCTION raise_extract_error
189 (p_business_group_id IN NUMBER -- context
190 ,p_assignment_id IN NUMBER -- context
191 ,p_error_text IN VARCHAR2
192 ,p_error_number IN NUMBER DEFAULT NULL
193 ,p_token1 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
194 ,p_fatal_flag IN VARCHAR2 DEFAULT 'Y' -- for existing pkgs
195 ) RETURN NUMBER
196 IS
197 l_ext_rslt_id NUMBER;
198 l_person_id NUMBER;
199 l_error_text VARCHAR2(2000);
200 l_error_message VARCHAR2(2000);
201 l_return_value NUMBER:= 0;
202 BEGIN
203 --
204 IF p_business_group_id is not null THEN
205 --
206 l_ext_rslt_id:= get_current_extract_result;
207 IF l_ext_rslt_id <> -1 THEN
208 --
209
210 If p_error_number is null Then
211
212 l_error_text:= 'Error raised in data element '||
213 NVL(ben_ext_person.g_elmt_name,ben_ext_fmt.g_elmt_name)||'. '||
214 p_error_text;
215
216
217 Elsif p_token1 is null Then
218
219 ben_ext_thread.g_err_num := p_error_number;
220 ben_ext_thread.g_err_name := p_error_text;
221 l_error_text :=
222 ben_ext_fmt.get_error_msg(to_number(substr(p_error_text, 5, 5)),
223 p_error_text,ben_ext_fmt.g_elmt_name);
224
225 -- if any token is defined than replace the tokens in the message.
226 -- and get the message text from fnd_messages.
227 Elsif p_token1 is not null Then
228
229 -- set the Tokens in the warning message and then
230 -- get the warning message from fnd_messages.
231
232 ben_ext_thread.g_err_num := p_error_number;
233 ben_ext_thread.g_err_name := p_error_text;
234
235 fnd_message.set_name('BEN',p_error_text);
236 fnd_message.set_token('TOKEN1',p_token1);
237
238 l_error_text := fnd_message.get ;
239
240
241 End If; -- End if of error number is null check ...
242
243 IF NVL(p_fatal_flag, 'Y') = 'Y' THEN
244
245 ben_ext_util.write_err
246 (p_err_num => p_error_number
247 ,p_err_name => l_error_text
248 ,p_typ_cd => 'F'
249 ,p_person_id => null
250 ,p_request_id => fnd_global.conc_request_id
251 ,p_business_group_id => p_business_group_id
252 ,p_ext_rslt_id => get_current_extract_result
253 );
254
255 commit;
256
257 raise ben_ext_thread.g_job_failure_error;
258 l_return_value:= 0; /* All Well ! */
259 ELSIF p_fatal_flag = 'N' THEN
260
261 l_person_id:= NVL(get_current_extract_person(p_assignment_id)
262 ,ben_ext_person.g_person_id);
263
264 ben_ext_util.write_err
265 (p_err_num => p_error_number
266 ,p_err_name => l_error_text
267 ,p_typ_cd => 'E' -- Error
268 ,p_person_id => l_person_id
269 ,p_request_id => fnd_global.conc_request_id
270 ,p_business_group_id => p_business_group_id
271 ,p_ext_rslt_id => get_current_extract_result
272 );
273 l_return_value := 0 ;
274 END IF; -- End if of p_fatal_flag is Y check ...
275 --
276 ELSE
277 --
278 l_return_value:= -2; /* No current extract process was found */
279 --
280 END IF;
281 --
282 ELSE
283 --
284 l_return_value := -1; /* Cannot raise warnings against header/trailers */
285 --
286 END IF;
287 --
288 RETURN l_return_value;
289 END raise_extract_error;
290
291 --
292 END pqp_gb_tp_extract_functions;