[Home] [Help]
PACKAGE BODY: APPS.FND_CONC_PP
Source
1 package body FND_CONC_PP as
2 /* $Header: AFCPPPIB.pls 120.2 2005/08/22 06:54:37 aweisber ship $ */
3
4
5 /* Exceptions */
6 bad_parameter EXCEPTION;
7 PRAGMA EXCEPTION_INIT(bad_parameter, -06501); -- program error
8
9 /* Message buffer */
10 internal_messages VARCHAR2(10000);
11
12
13 --
14 -- Set the contents of the message buffer
15 --
16 PROCEDURE message_add(msg VARCHAR2) IS
17 BEGIN
18 internal_messages := internal_messages || msg || fnd_global.newline;
19 END;
20
21
22 --
23 -- Returns the contents of the message buffer
24 --
25
26 FUNCTION message return varchar2 is
27 Begin
28 return(internal_messages);
29 end;
30
31
32 --
33 -- Clear the message buffer.
34 --
35 PROCEDURE message_init IS
36 BEGIN
37 internal_messages := '';
38 END;
39
40 /* ------------------------------------------------------------ */
41 PROCEDURE check_notnull(val IN VARCHAR2) IS
42 BEGIN
43 IF(val IS NULL) THEN
44 message_add('A NULL value was specified for a NOT NULL parameter.');
45 RAISE bad_parameter;
46 END IF;
47 END;
48
49 PROCEDURE check_notnull(val IN NUMBER) IS
50 BEGIN
51 IF(val IS NULL) THEN
52 message_add('A NULL value was specified for a NOT NULL parameter.');
53 RAISE bad_parameter;
54 END IF;
55 END;
56
57 PROCEDURE check_notnull(val IN DATE) IS
58 BEGIN
59 IF(val IS NULL) THEN
60 message_add('A NULL value was specified for a NOT NULL parameter.');
61 RAISE bad_parameter;
62 END IF;
63 END;
64 /* ------------------------------------------------------------ */
65
66
67 --
68 -- get the application id, given the name (or short name)
69 --
70 FUNCTION application_id_f(application_name_in IN VARCHAR2)
71 RETURN fnd_application.application_id%TYPE
72 IS
73 application_id_ret fnd_application.application_id%TYPE;
74 BEGIN
75 IF(application_name_in IS NULL) THEN
76 message_add('A null application_name was specified.');
77 RAISE bad_parameter;
78 END IF;
79 /* Check the short name first. */
80 SELECT application_id
81 INTO application_id_ret
82 FROM fnd_application
83 WHERE application_short_name = application_name_in;
84 RETURN application_id_ret;
85 EXCEPTION
86 WHEN no_data_found THEN /* Try the translated name. */
87 BEGIN
88 SELECT application_id
89 INTO application_id_ret
90 FROM fnd_application_tl
91 WHERE application_name = application_name_in
92 AND rownum = 1;
93 RETURN application_id_ret;
94 EXCEPTION
95 when NO_DATA_FOUND then
96 message_add('Invalid application name: ' || application_name_in);
97 RAISE bad_parameter;
98 END;
99 END;
100
101 /* Get a concurrent executable ID given appl ID and subroutine name */
102 FUNCTION concurrent_executable_id_f(program_application_id number,
103 exec_name varchar2) return number is
104 exec_id number;
105
106 BEGIN
107 IF(exec_name IS NULL) THEN
108 message_add('A null executable name was specified.');
109 RAISE bad_parameter;
110 END IF;
111
112 SELECT EXECUTABLE_ID
113 INTO exec_id
114 FROM FND_EXECUTABLES
115 WHERE application_id = program_application_id
116 AND executable_name = exec_name;
117
118 RETURN exec_id;
119
120 EXCEPTION
121 WHEN no_data_found THEN
122 message_add('Could not find executable: '||exec_name);
123 RAISE bad_parameter;
124 END;
125
126
127 -- Procedure
128 -- Assign
129 --
130 -- Purpose
131 -- Assign a stored procedure to a request. Returns step number if successful,-- a negative number if not.
132 --
133 -- Arguments:
134 -- Application - Application Short Name
135 --
136 -- Exec_Name - Executable Name
137 --
138 -- Req_ID - request ID
139 --
140 -- S_Flag - do we execute if request was successful? ['Y'/'N']
141 --
142 -- W_Flag - do we execute if request completed with status warning?
143 -- ['Y'/'N']
144 --
145 -- F_Flag - do we execute if request failed? ['Y'/'N']
146 --
147 -- Arg1 -> Arg10 - Arguments that may be retrieved during execution.
148 --
149 FUNCTION Assign( Application IN Varchar2,
150 Executable_Name IN Varchar2,
151 Req_ID IN Number,
152 S_Flag IN Varchar2,
153 W_Flag IN Varchar2,
154 F_Flag IN Varchar2,
155 Arg1 IN Varchar2,
156 Arg2 IN Varchar2,
157 Arg3 IN Varchar2,
158 Arg4 IN Varchar2,
159 Arg5 IN Varchar2,
160 Arg6 IN Varchar2,
161 Arg7 IN Varchar2,
162 Arg8 IN Varchar2,
163 Arg9 IN Varchar2,
164 Arg10 IN Varchar2) return number is
165
166 app_id number;
167 login_id number;
168 user_id number;
169 exec_id number;
170 step_id number;
171 ins_num number;
172 begin
173 message_init;
174
175 begin
176 select OPS_INSTANCE into
177 ins_num
178 from fnd_concurrent_requests
179 where request_id = Req_ID;
180 Exception when others then
181 message_add('Bad request ID.');
182 return(-1);
183 end;
184
185 begin
186 login_id := fnd_global.login_id;
187
188 user_id := FND_GLOBAL.user_id;
189 if (user_id is null) then
190 user_id := -1;
191 end if;
192 Exception when others then
193 message_add('Error while getting login_id/user_id.');
194 return(-1);
195 End;
196
197 begin
198 app_id := application_id_f(Application);
199 exec_id := concurrent_executable_id_f(app_id, Executable_Name);
200 Exception
201 when bad_parameter then
202 return -1;
203 when others then
204 message_add('Unknown error while getting application/executable id.');
205 return(-1);
206 End;
207
208 begin
209 Select count(*)
210 into step_id
211 from fnd_conc_pp_actions
212 where concurrent_request_id = Req_ID
213 and Action_Type = 4;
214
215 If (step_id > 0) then
216 Select max(SEQUENCE)
217 into step_id
218 from fnd_conc_pp_actions
219 where concurrent_request_id = Req_ID
220 and Action_Type = 4;
221 End If;
222
223 Exception when others then
224 message_add('Error while querying fnd_conc_pp_actions.');
225 return(-1);
226 End;
227
228
229 step_id := step_id + 1;
230
231 begin
232 Insert into fnd_conc_pp_actions
233 (CONCURRENT_REQUEST_ID,ACTION_TYPE, STATUS_S_FLAG, STATUS_W_FLAG,
234 STATUS_F_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
235 LAST_UPDATE_LOGIN, CREATED_BY, PROGRAM_APPLICATION_ID, PROGRAM_ID,
236 Argument1, Argument2, Argument3, Argument4, Argument5,
237 Argument6, Argument7, Argument8, Argument9, Argument10,
238 SEQUENCE, OPS_INSTANCE)
239 Select Req_ID, 4, S_Flag, W_Flag,
240 F_Flag, sysdate, user_id, sysdate,
241 fnd_global.login_id, user_id, app_id, exec_id,
242 Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10,
243 step_id, ins_num from dual;
244 Exception when others then
245 message_add('Error while inserting into fnd_conc_pp_actions.');
246 return(-1);
247 End;
248
249 return step_id;
250
251 end;
252
253 -- Procedure
254 -- Retrieve
255 --
256 -- Purpose
257 -- Retrieve stored arguments
258 --
259 -- Arguments:
260 -- Req_ID - request ID
261 --
262 -- Step Number - Step Number
263 --
264 -- Arg1 -> Arg10 - Arguments that were set at assignment.
265 --
266 -- Returns negative number on failure
267 --
268 FUNCTION Retrieve(Req_ID IN Number,
269 Step IN Number,
270 app_short_name OUT NOCOPY VARCHAR2,
271 exec_name OUT NOCOPY VARCHAR2,
272 S_Flag OUT NOCOPY Varchar2,
273 W_Flag OUT NOCOPY Varchar2,
274 F_Flag OUT NOCOPY Varchar2,
275 Arg1 OUT NOCOPY Varchar2,
276 Arg2 OUT NOCOPY Varchar2,
277 Arg3 OUT NOCOPY Varchar2,
278 Arg4 OUT NOCOPY Varchar2,
279 Arg5 OUT NOCOPY Varchar2,
280 Arg6 OUT NOCOPY Varchar2,
281 Arg7 OUT NOCOPY Varchar2,
282 Arg8 OUT NOCOPY Varchar2,
283 Arg9 OUT NOCOPY Varchar2,
284 Arg10 OUT NOCOPY Varchar2) return number is
285
286 app_id number;
287 exec_id number;
288
289 begin
290 begin
291 select STATUS_S_FLAG, STATUS_W_FLAG, STATUS_F_FLAG,
292 PROGRAM_APPLICATION_ID, PROGRAM_ID,
293 Argument1, Argument2, Argument3, Argument4, Argument5,
294 Argument6, Argument7, Argument8, Argument9, Argument10
295 into S_Flag, W_Flag, F_Flag, app_id, exec_id,
296 Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10
297 from fnd_conc_pp_actions
298 where CONCURRENT_REQUEST_ID = Req_ID and
299 sequence = Step and
300 action_type = 4;
301 exception
302 when NO_DATA_FOUND then
303 message_add('Request ID, Sequence, Action Type ('||to_char(Req_ID)||
304 ', ' || TO_CHAR(Step) || ', 4) not found.');
305 return(-1);
306 when others then
307 message_add('Error while querying fnd_conc_pp_actions.');
308 return(-1);
309 End;
310
311 begin
312 select Application_short_name
313 into app_short_name
314 from FND_APPLICATION
315 where application_id = app_id;
316 exception
317 when NO_DATA_FOUND then
318 message_add('Bad Application ID');
319 return(-1);
320 when others then
321 message_add('Error while querying fnd_application.');
322 return(-1);
323 end;
324
325 begin
326 select Executable_Name
327 into exec_name
328 from FND_EXECUTABLES
329 where application_id = app_id
330 and executable_id = exec_id;
331 exception
332 when NO_DATA_FOUND then
333 message_add('Bad Executible ID');
334 return(-1);
335 when others then
336 message_add('Error while querying fnd_executables.');
337 return(-1);
338 end;
339
340 return 0;
341 end;
342
343 end FND_CONC_PP;