1 PACKAGE BODY FND_OAM_DSCRAM_ARGS_PKG as
2 /* $Header: AFOAMDSARGB.pls 120.7 2006/01/17 13:55 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants/Types
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(20) := 'DSCRAM_ARGS_PKG.';
8
9 -- exceptions used by INITIALZE_ARG/GET_CANONICAL_ARG_VALUE to facilitate common
10 -- cleanup actions
11 INIT_FAILED EXCEPTION;
12 GET_FAILED EXCEPTION;
13
14 --local name for common exception when dealing with binding
15 BIND_DOES_NOT_EXIST EXCEPTION;
16 PRAGMA EXCEPTION_INIT(BIND_DOES_NOT_EXIST, -1006);
17
18 --type used for bulk selects of the canonical value field
19 TYPE long_varchar2_table IS TABLE OF VARCHAR2(4000);
20
21 ----------------------------------------
22 -- Public/Private Body Methods
23 ----------------------------------------
24
25 -- Public
26 FUNCTION IS_READABLE(p_arg IN arg)
27 RETURN BOOLEAN
28 IS
29 BEGIN
30 RETURN p_arg.permissions IN (FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ,
31 FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE);
32 END;
33
34 -- Public
35 FUNCTION IS_WRITABLE(p_arg IN arg)
36 RETURN BOOLEAN
37 IS
38 BEGIN
39 RETURN p_arg.permissions IN (FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_WRITE,
40 FND_OAM_DSCRAM_UTILS_PKG.G_PERMISSION_READ_WRITE);
41 END;
42
43 -- Private helper to make sure a canonical value is proper for an arg,
44 -- validation failures are returned as exceptions
45 PROCEDURE VALIDATE_CANONICAL_VALUE(p_arg IN arg,
46 p_canonical_value IN VARCHAR2)
47 IS
48 l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_CANONICAL_VALUE';
49
50 l_num NUMBER;
51 l_date DATE;
52 l_rowid ROWID;
53 BEGIN
54 CASE p_arg.datatype
55 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2 THEN
56 null; --no validation
57 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER THEN
58 l_num := FND_NUMBER.CANONICAL_TO_NUMBER(p_canonical_value);
59 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE THEN
60 l_date := FND_DATE.CANONICAL_TO_DATE(p_canonical_value);
61 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID THEN
62 --for some reason, this is refusing to throw an exception on invalid values
63 l_rowid := CHARTOROWID(p_canonical_value);
64 ELSE
65 --unknown datatype
66 fnd_oam_debug.log(6, l_ctxt, 'ARG ID('||p_arg.arg_id||'), unknown datatype: '||p_arg.datatype);
67 RAISE NO_DATA_FOUND;
68 END CASE;
69 END;
70
71 -- Private helper to set the canonical value and update related state.
72 -- Errors are returned as exceptions.
73 PROCEDURE SET_STATE_ARG_VALUE(p_arg IN OUT NOCOPY arg,
74 p_canonical_value IN VARCHAR2,
75 p_rowid_lbound IN ROWID DEFAULT NULL,
76 p_rowid_ubound IN ROWID DEFAULT NULL)
77 IS
78 BEGIN
79 VALIDATE_CANONICAL_VALUE(p_arg,
80 p_canonical_value);
81 p_arg.canonical_value := p_canonical_value;
82 p_arg.rowid_lbound := p_rowid_lbound;
83 p_arg.rowid_ubound := p_rowid_ubound;
84 p_arg.valid_value_flag := FND_API.G_TRUE;
85 END;
86
87 -- Private constructor helper for add_arg_to_context and add_arg_to_list to make a new physical arg entity
88 FUNCTION INTERNAL_CREATE_ARG(p_arg_id IN NUMBER,
89 p_arg_name IN VARCHAR2,
90 p_initialized_success_flag IN VARCHAR2,
91 p_allow_override_source_flag IN VARCHAR2,
92 p_binding_enabled_flag IN VARCHAR2,
93 p_permissions IN VARCHAR2,
94 p_write_policy IN VARCHAR2,
95 p_datatype IN VARCHAR2,
96 p_valid_value_flag IN VARCHAR2,
97 p_canonical_value IN VARCHAR2)
98 RETURN arg
99 IS
100 l_ctxt VARCHAR2(60) := PKG_NAME||'INTERNAL_CREATE_ARG';
101
102 l_initialized BOOLEAN := FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_initialized_success_flag);
103 l_allow_override_source BOOLEAN := FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_allow_override_source_flag);
104 l_binding_enabled BOOLEAN := FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_binding_enabled_flag);
105 l_valid_value BOOLEAN := FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_valid_value_flag);
106
107 l_arg arg;
108 l_ignore VARCHAR2(2048);
109 BEGIN
110 --init the new entry
111 l_arg.arg_id := p_arg_id;
112 l_arg.arg_name := p_arg_name;
113 l_arg.init_locally := FALSE;
114 l_arg.initialized_success_flag := p_initialized_success_flag;
115 l_arg.allow_override_source := l_allow_override_source;
116 l_arg.binding_enabled := l_binding_enabled;
117 l_arg.permissions := p_permissions;
118 l_arg.write_policy := p_write_policy;
119 l_arg.datatype := p_datatype;
120 l_arg.is_constant := FALSE; --default here, set for real in init
121 l_arg.source_cursor_id := NULL;
122 l_arg.source_sql_bind_rowids := FALSE; --default here, set for real in init
123 l_arg.source_state_key := NULL;
124 l_arg.source_use_exec_cursor := FALSE; --default here, set in init
125
126 --use separate setter for the value
127 l_arg.valid_value_flag := p_valid_value_flag;
128 IF l_initialized AND l_valid_value THEN
129 SET_STATE_ARG_VALUE(l_arg,
130 p_canonical_value);
131 END IF;
132
133 --return the completed arg
134 RETURN l_arg;
135
136 --let exceptions pass to parent
137 END;
138
139 -- Helper to FETCH_CONTEXT-like procedures to create an arg based on its attributes and
140 -- add it to a supplied arg context.
141 FUNCTION ADD_ARG_TO_CONTEXT(px_arg_ctxt IN OUT NOCOPY arg_context,
142 p_arg_id IN NUMBER,
143 p_arg_name IN VARCHAR2,
144 p_initialized_success_flag IN VARCHAR2,
145 p_allow_override_source_flag IN VARCHAR2,
146 p_binding_enabled_flag IN VARCHAR2,
147 p_permissions IN VARCHAR2,
148 p_write_policy IN VARCHAR2,
149 p_datatype IN VARCHAR2,
150 p_valid_value_flag IN VARCHAR2,
151 p_canonical_value IN VARCHAR2)
152 RETURN BOOLEAN
153 IS
154 l_ctxt VARCHAR2(60) := PKG_NAME||'ADD_ARG_TO_CONTEXT';
155
156 l_arg arg;
157 BEGIN
158 l_arg := INTERNAL_CREATE_ARG(p_arg_id,
159 p_arg_name,
160 p_initialized_success_flag,
161 p_allow_override_source_flag,
162 p_binding_enabled_flag,
163 p_permissions,
164 p_write_policy,
165 p_datatype,
166 p_valid_value_flag,
167 p_canonical_value);
168
169 --add the arg to the context
170 px_arg_ctxt(p_arg_name) := l_arg;
171
172 RETURN TRUE;
173 EXCEPTION
174 WHEN OTHERS THEN
175 fnd_oam_debug.log(6, l_ctxt, 'Argument ID: ('||p_arg_id||'), Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
176 RETURN FALSE;
177 END;
178
179 -- Private: similar to other add_arg_to_list but in this case we've already created an arg object
180 -- and just want it appended.
181 FUNCTION ADD_ARG_TO_LIST(px_arg_list IN OUT NOCOPY arg_list,
182 p_arg IN arg)
183 RETURN BOOLEAN
184 IS
185 l_ctxt VARCHAR2(60) := PKG_NAME||'ADD_ARG_TO_LIST';
186 BEGIN
187 --add the arg to the list
188 px_arg_list.EXTEND;
189 px_arg_list(px_arg_list.COUNT) := p_arg;
190
191 RETURN TRUE;
192 EXCEPTION
193 WHEN OTHERS THEN
194 fnd_oam_debug.log(6, l_ctxt, 'Argument ID: ('||p_arg.arg_id||'), Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
195 RETURN FALSE;
196 END;
197
198 -- Private helper to get_canonical_value to fetch the value for a given state key
199 PROCEDURE GET_CANONICAL_VALUE_FOR_KEY(px_arg IN OUT NOCOPY arg,
200 p_state_key IN VARCHAR2,
201 p_using_splitting IN BOOLEAN,
202 p_rowid_lbound IN ROWID,
203 p_rowid_ubound IN ROWID,
204 x_canonical_value OUT NOCOPY VARCHAR2,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_return_msg OUT NOCOPY VARCHAR2)
207 IS
208 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_CANONICAL_VALUE_FOR_KEY';
209
210 l_canonical_value VARCHAR2(4000);
211 BEGIN
212 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
213 x_return_status := FND_API.G_RET_STS_ERROR;
214 x_return_msg := '';
215
216 --do a big case statement on the state key to fetch the value
217 CASE p_state_key
218 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_RUN_ID THEN
219 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_ID);
220
221 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_RUN_MODE THEN
222 l_canonical_value := FND_OAM_DSCRAM_RUNS_PKG.GET_RUN_MODE;
223
224 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BUNDLE_ID THEN
225 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_BUNDLES_PKG.GET_BUNDLE_ID);
226
227 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BUNDLE_WORKERS_ALLOWED THEN
228 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_BUNDLES_PKG.GET_WORKERS_ALLOWED);
229
230 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BUNDLE_BATCH_SIZE THEN
231 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_BUNDLES_PKG.GET_BATCH_SIZE);
232
233 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_WORKER_ID THEN
234 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_BUNDLES_PKG.GET_WORKER_ID);
235
236 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_TASK_ID THEN
237 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_TASKS_PKG.GET_TASK_ID);
238
239 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_ID THEN
240 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_UNITS_PKG.GET_UNIT_ID);
241
242 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_USING_SPLITTING THEN
243 l_canonical_value := FND_OAM_DSCRAM_UTILS_PKG.BOOLEAN_TO_FLAG(p_using_splitting);
244
245 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_LBOUND THEN
246 l_canonical_value := ROWIDTOCHAR(p_rowid_lbound);
247
248 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ROWID_UBOUND THEN
249 l_canonical_value := ROWIDTOCHAR(p_rowid_ubound);
250
251 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_OWNER THEN
252 l_canonical_value := FND_OAM_DSCRAM_UNITS_PKG.GET_UNIT_OBJECT_OWNER;
253
254 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_OBJECT_NAME THEN
255 l_canonical_value := FND_OAM_DSCRAM_UNITS_PKG.GET_UNIT_OBJECT_NAME;
256
257 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_WORKERS_ALLOWED THEN
258 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_UNITS_PKG.GET_WORKERS_ALLOWED);
259
260 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_UNIT_BATCH_SIZE THEN
261 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(FND_OAM_DSCRAM_UNITS_PKG.GET_BATCH_SIZE);
262
263 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_DML_ID THEN
264 l_canonical_value := FND_OAM_DSCRAM_DMLS_PKG.GET_CURRENT_DML_ID;
265
266 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_PLSQL_ID THEN
267 l_canonical_value := FND_OAM_DSCRAM_PLSQLS_PKG.GET_CURRENT_PLSQL_ID;
268
269 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_ARGUMENT_ID THEN
270 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(px_arg.arg_id);
271
272 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_WORKERS_ALLOWED THEN
273 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(NVL(FND_OAM_DSCRAM_UNITS_PKG.GET_WORKERS_ALLOWED,
277 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(NVL(FND_OAM_DSCRAM_UNITS_PKG.GET_BATCH_SIZE,
274 FND_OAM_DSCRAM_BUNDLES_PKG.GET_WORKERS_ALLOWED));
275
276 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_KEY_BATCH_SIZE THEN
278 FND_OAM_DSCRAM_BUNDLES_PKG.GET_BATCH_SIZE));
279 ELSE
280 x_return_msg := 'ARG ID('||px_arg.arg_id||'), invalid state key: '||p_state_key;
281 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
282 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
283 RETURN;
284 END CASE;
285
286 --success
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288 x_canonical_value := l_canonical_value;
289 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 -- since we involve no sql, this should only happen when there's missing state
293 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_MISSING_STATE;
294 fnd_oam_debug.log(1, l_ctxt, 'Arg ID('||px_arg.arg_id||'), threw missing state.');
295 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
296 RETURN;
297 WHEN OTHERS THEN
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 x_return_msg := 'Arg ID('||px_arg.arg_id||') Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
300 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
301 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
302 RETURN;
303 END;
304
305 -- Private helper to get_canonical_value to fetch the value from the execution cursor. Since binds
306 -- use the arg's name field, we implicitly use this name to fetch the value.
307 PROCEDURE GET_CANONICAL_VALUE_FROM_CUR(px_arg IN OUT NOCOPY arg,
308 p_execution_cursor_id IN INTEGER,
309 x_canonical_value OUT NOCOPY VARCHAR2,
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_return_msg OUT NOCOPY VARCHAR2)
312 IS
313 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_CANONICAL_VALUE_FROM_CUR';
314
315 l_canonical_value VARCHAR2(4000);
316
317 l_bindvar_name VARCHAR2(120) := ':'||px_arg.arg_name;
318
319 l_number NUMBER := NULL;
320 l_date DATE := NULL;
321 l_bool BOOLEAN := NULL;
322 l_rowid ROWID := NULL;
323 BEGIN
324 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
325 x_return_status := FND_API.G_RET_STS_ERROR;
326 x_return_msg := '';
327
328 --fetch the value into the properly typed local variable then then
329 --convert it into the canonical
330 CASE px_arg.datatype
331 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2 THEN
332 DBMS_SQL.VARIABLE_VALUE(p_execution_cursor_id,
333 l_bindvar_name,
334 x_canonical_value);
335
336 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER THEN
337 DBMS_SQL.VARIABLE_VALUE(p_execution_cursor_id,
338 l_bindvar_name,
339 l_number);
340 x_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(l_number);
341
342 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE THEN
343 DBMS_SQL.VARIABLE_VALUE(p_execution_cursor_id,
344 l_bindvar_name,
345 l_date);
346 x_canonical_value := FND_DATE.DATE_TO_CANONICAL(l_date);
347
348 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID THEN
349 DBMS_SQL.VARIABLE_VALUE_ROWID(p_execution_cursor_id,
350 l_bindvar_name,
351 l_rowid);
352 x_canonical_value := ROWIDTOCHAR(l_rowid);
353 ELSE
354 x_return_msg := 'Arg ('||px_arg.arg_id||') has unknown datatype:'||px_arg.datatype;
355 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
356 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
357 RETURN;
358 END CASE;
359
360 fnd_oam_debug.log(1, l_ctxt, 'Found canonical value: '||x_canonical_value);
361
362 --success
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
365 EXCEPTION
366 WHEN BIND_DOES_NOT_EXIST THEN
367 --catch a common error and provide better feedback.
368 fnd_oam_debug.log(6, l_ctxt, 'Arg ID('||px_arg.arg_id||'), Bindvar('||l_bindvar_name||') does not exist');
369 x_return_msg := 'Variable Value failure: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
370 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
371 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
372 RETURN;
373 WHEN OTHERS THEN
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
375 x_return_msg := 'Arg ID('||px_arg.arg_id||') Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
376 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
377 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
378 RETURN;
379 END;
380
381 --Private, utility procedure for GET_CANONICAL_ARG_VALUE to execute a source sql using dynamic sql or the source_cursor and return the value as
382 --its canonical string representation. Must occur after the derived state/rowids are set by INITIALIZE_ARG.
383 PROCEDURE GET_CANONICAL_VALUE_FOR_SQL(px_arg IN OUT NOCOPY arg,
384 p_final_sql_stmt IN VARCHAR2 DEFAULT NULL,
388 x_return_status OUT NOCOPY VARCHAR2,
385 p_rowid_lbound IN ROWID,
386 p_rowid_ubound IN ROWID,
387 x_canonical_value OUT NOCOPY VARCHAR2,
389 x_return_msg OUT NOCOPY VARCHAR2)
390 IS
391 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_CANONICAL_VALUE_FOR_SQL';
392
393 l_rows_fetched NUMBER;
394 l_canonical_value VARCHAR2(4000);
395
396 missing_binds EXCEPTION;
397 PRAGMA EXCEPTION_INIT(missing_binds, -1008);
398
399 l_number NUMBER;
400 l_date DATE;
401 l_rowid ROWID;
402 BEGIN
403 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
404 x_return_status := FND_API.G_RET_STS_ERROR;
405 x_return_msg := '';
406
407 --fetch the canonical value differently based on what SQL is available to us
408 IF p_final_sql_stmt IS NOT NULL THEN
409
410 --we don't support binding the rowids when using a manual sql stmt since it won't bind properly
411 --with user specified binds. Statements using binds should use the source_cursor.
412 IF px_arg.source_sql_bind_rowids THEN
413 x_return_msg := 'Cannot have a non-null final sql stmt when binding rowids. This should not happen.';
414 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
415 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
416 RETURN;
417 END IF;
418
419 --different statements depending on datatype
420 CASE px_arg.datatype
421 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2 THEN
422 EXECUTE IMMEDIATE p_final_sql_stmt INTO l_canonical_value;
423 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER THEN
424 EXECUTE IMMEDIATE p_final_sql_stmt INTO l_number;
425 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(l_number);
426 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE THEN
427 EXECUTE IMMEDIATE p_final_sql_stmt INTO l_date;
428 l_canonical_value := FND_DATE.DATE_TO_CANONICAL(l_date);
429 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID THEN
430 EXECUTE IMMEDIATE p_final_sql_stmt INTO l_rowid;
431 l_canonical_value := ROWIDTOCHAR(l_rowid);
432 ELSE
433 x_return_msg := 'Arg ID('||px_arg.arg_id||'), invalid datatype: '||px_arg.datatype;
434 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
435 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
436 RETURN;
437 END CASE;
438 ELSIF px_arg.source_cursor_id IS NOT NULL THEN
439 IF NOT DBMS_SQL.IS_OPEN(px_arg.source_cursor_id) THEN
440 x_return_msg := 'Arg ID ('||px_arg.arg_id||'), source cursor is already closed. This should not happen.';
441 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
442 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
443 RETURN;
444 END IF;
445
446 --bind if we have to
447 IF px_arg.source_sql_bind_rowids THEN
448 fnd_oam_debug.log(1, l_ctxt, 'Binding Rowids');
449 DBMS_SQL.BIND_VARIABLE(px_arg.source_cursor_id,
450 FND_OAM_DSCRAM_UTILS_PKG.G_ARG_ROWID_LBOUND_NAME,
451 p_rowid_lbound);
452 DBMS_SQL.BIND_VARIABLE(px_arg.source_cursor_id,
453 FND_OAM_DSCRAM_UTILS_PKG.G_ARG_ROWID_UBOUND_NAME,
454 p_rowid_ubound);
455 END IF;
456
457 --now execute and fetch
458 fnd_oam_debug.log(1, l_ctxt, 'Executing cursor...');
459 l_rows_fetched := DBMS_SQL.EXECUTE_AND_FETCH(px_arg.source_cursor_id);
460 fnd_oam_debug.log(1, l_ctxt, '...Done');
461
462 IF l_rows_fetched <> 1 THEN
463 x_return_msg := 'Fetched '||l_rows_fetched||' rows. Args must return 1 row.';
464 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
465 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
466 RETURN;
467 END IF;
468
469 --now depending on the datatype, set our local canonical value
470 CASE px_arg.datatype
471 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2 THEN
472 DBMS_SQL.COLUMN_VALUE(px_arg.source_cursor_id,
473 1,
474 l_canonical_value);
475 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER THEN
476 DBMS_SQL.COLUMN_VALUE(px_arg.source_cursor_id,
477 1,
478 l_number);
479 l_canonical_value := FND_NUMBER.NUMBER_TO_CANONICAL(l_number);
480 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE THEN
481 DBMS_SQL.COLUMN_VALUE(px_arg.source_cursor_id,
482 1,
483 l_date);
484 l_canonical_value := FND_DATE.DATE_TO_CANONICAL(l_date);
485 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID THEN
486 DBMS_SQL.COLUMN_VALUE_ROWID(px_arg.source_cursor_id,
487 1,
488 l_rowid);
489 l_canonical_value := ROWIDTOCHAR(l_rowid);
490 ELSE
491 x_return_msg := 'Arg ID('||px_arg.arg_id||'), invalid datatype: '||px_arg.datatype;
492 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
493 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
494 RETURN;
495 END CASE;
496 ELSE
497 x_return_msg := 'Source Cursor is NULL and Source Final SQL Stmt is NULL, no SQL to fetch.';
501 END IF;
498 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
499 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
500 RETURN;
502
503 --success
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505 x_canonical_value := l_canonical_value;
506 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
507 EXCEPTION
508 WHEN missing_binds THEN
509 x_return_status := FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_MISSING_BINDS;
510 fnd_oam_debug.log(1, l_ctxt, 'Arg ID('||px_arg.arg_id||'), threw missing binds.');
511 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
512 RETURN;
513 WHEN OTHERS THEN
514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
515 x_return_msg := 'Arg ID('||px_arg.arg_id||') Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
516 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
517 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
518 RETURN;
519 END;
520
521 --Private, helper to INITIALIZE_ARG to sync up the arg's state with values INIT found. Primary responsibilities
522 --include the derived arg state and the source_cursor-related fields.
523 PROCEDURE UPDATE_STATE_USING_INIT_VALUES(px_arg IN OUT NOCOPY arg,
524 p_use_splitting IN BOOLEAN,
525 p_initialized_success_flag IN VARCHAR2,
526 p_valid_value_flag IN VARCHAR2,
527 p_canonical_value IN VARCHAR2,
528 p_source_type IN VARCHAR2,
529 p_source_text IN VARCHAR2,
530 p_source_final_text IN VARCHAR2,
531 x_return_status OUT NOCOPY VARCHAR2,
532 x_return_msg OUT NOCOPY VARCHAR2)
533 IS
534 l_ctxt VARCHAR2(60) := PKG_NAME||'UPDATE_STATE_USING_INIT_VALUES';
535
536 l_canonical_value VARCHAR2(4000);
537 l_number NUMBER;
538 l_date DATE;
539 l_rowid ROWID;
540
541 l_return_status VARCHAR2(6);
542 l_return_msg VARCHAR2(2048);
543 BEGIN
544 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 x_return_msg := '';
547
548 --SET DERIVED ATTRIBUTES
549
550 --update the bind_rowids flag based on the source type and whether we're using splitting
551 IF (p_source_type = FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE) THEN
552 px_arg.source_sql_bind_rowids := p_use_splitting;
553 END IF;
554
555 --set the is_constant indicator boolean based on the source_type
556 IF p_source_type = FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_CONSTANT THEN
557 px_arg.is_constant := TRUE;
558 END IF;
559
560 --if initialized was already determined, set our value-related state based on inputs
561 IF p_initialized_success_flag IS NOT NULL THEN
562 px_arg.initialized_success_flag := p_initialized_success_flag;
563 IF FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_initialized_success_flag) THEN
564 px_arg.valid_value_flag := p_valid_value_flag;
565 IF FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(p_valid_value_flag) THEN
566 BEGIN
567 SET_STATE_ARG_VALUE(px_arg,
568 p_canonical_value);
569 EXCEPTION
570 WHEN OTHERS THEN
571 fnd_oam_debug.log(1, l_ctxt, 'ARG ID ('||px_arg.arg_id||'), failed to set the canonical value: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
572 --if it didn't set properly, store that after the init to keep other threads from doing it
573 px_arg.valid_value_flag := FND_API.G_FALSE;
574 END;
575 ELSIF px_arg.is_constant AND px_arg.valid_value_flag IS NULL THEN
576 -- For constants, write the canonical value when the value flag is null because we never set it to TRUE until the get.
577 -- Invalid p_source_text values would have been caught by the first init where valid_value_flag would be set to FALSE.
578 px_arg.canonical_value := p_source_text;
579 ELSE
580 px_arg.canonical_value := NULL;
581 END IF;
582 END IF;
583 END IF;
584
585 --Before doing further init, if we've already failed the init then return because we've updated enough state
586 IF px_arg.initialized_success_flag = FND_API.G_FALSE THEN
587 px_arg.init_locally := TRUE;
588 x_return_status := FND_API.G_RET_STS_SUCCESS;
589 fnd_oam_debug.log(1, l_ctxt, 'Found initialized_success_flag was false after sync but before any sql actions.');
590 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
591 RETURN;
592 END IF;
593
594 --Since constants are rather common and they dont change, go ahead and validate the value and
595 --store it in the canonical value for quick retrieval later. We don't set the valid_value_flag because
596 --we want get_canonical_value to be able to detect the first time a constant is referenced so it can write it
597 --in the appropriate location.
598 IF p_source_type = FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_CONSTANT THEN
599
600 BEGIN
601 VALIDATE_CANONICAL_VALUE(px_arg,
602 p_source_text);
603 --set to NULL instead of TRUE to allow constants to be overridden in the get_* logic by context values
604 px_arg.valid_value_flag := NULL;
605 px_arg.canonical_value := p_source_text;
606 EXCEPTION
610
607 WHEN OTHERS THEN
608 px_arg.valid_value_flag := FND_API.G_FALSE;
609 END;
611 --always set init to true since a failed value gets caught with the valid_value_flag
612 px_arg.initialized_success_flag := FND_API.G_TRUE;
613
614 -- The following situations benefit from a source cursor:
615 -- 1) SQL source with write policies of per_range or always
616 -- However, to keep from querying the source_final_text in get_value later, all SQL-based sources use a cursor.
617 ELSIF FND_OAM_DSCRAM_UTILS_PKG.SOURCE_TYPE_USES_SQL(p_source_type) THEN
618
619 --close the last cursor, shouldn't happen but it's safe
620 IF px_arg.source_cursor_id IS NOT NULL AND DBMS_SQL.IS_OPEN(px_arg.source_cursor_id) THEN
621 DBMS_SQL.CLOSE_CURSOR(px_arg.source_cursor_id);
622 END IF;
623
624 --create a new cursor and parse the source_final_text into it
625 px_arg.source_cursor_id := DBMS_SQL.OPEN_CURSOR;
626 BEGIN
627 DBMS_SQL.PARSE(px_arg.source_cursor_id,
628 p_source_final_text,
629 DBMS_SQL.NATIVE);
630 EXCEPTION
631 WHEN OTHERS THEN
632 x_return_msg := 'ARG_ID ('||px_arg.arg_id||'), failed to parse source final text: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
633 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
634 px_arg.init_locally := TRUE;
635 fnd_oam_debug.log(1, l_ctxt, 'Source Final Text: "'||p_source_final_text||'"');
636 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
637 RETURN;
638 END;
639
640 --finally, define a column with the correct output type
641 CASE px_arg.datatype
642 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2 THEN
643 DBMS_SQL.DEFINE_COLUMN(px_arg.source_cursor_id,
644 1,
645 l_canonical_value,
646 4000);
647 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER THEN
648 DBMS_SQL.DEFINE_COLUMN(px_arg.source_cursor_id,
649 1,
650 l_number);
651 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE THEN
652 DBMS_SQL.DEFINE_COLUMN(px_arg.source_cursor_id,
653 1,
654 l_date);
655 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID THEN
656 DBMS_SQL.DEFINE_COLUMN_ROWID(px_arg.source_cursor_id,
657 1,
658 l_rowid);
659 ELSE
660 x_return_msg := 'Unknown Arg Dataype: '||px_arg.datatype;
661 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
662 px_arg.init_locally := TRUE;
663 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
664 RETURN;
665 END CASE;
666
667 --set these sorts of args to be initialized
668 px_arg.initialized_success_flag := FND_API.G_TRUE;
669 ELSIF p_source_type = FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_STATE THEN
670 --set the source state key using the first 30 chars of the source_text
671 px_arg.source_state_key := SUBSTR(p_source_text, 1, 30);
672 px_arg.initialized_success_flag := FND_API.G_TRUE;
673 ELSIF p_source_type = FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_EXECUTION_CURSOR THEN
674 --set the use_exec_cursor indicator variable for the arg
675 px_arg.source_use_exec_cursor := TRUE;
676 px_arg.initialized_success_flag := FND_API.G_TRUE;
677 ELSIF p_source_type IS NULL THEN
678 --if there's no source, we're done initializing.
679 px_arg.initialized_success_flag := FND_API.G_TRUE;
680 END IF;
681
682 --return success
683 x_return_status := FND_API.G_RET_STS_SUCCESS;
684 px_arg.init_locally := TRUE;
685 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
686 EXCEPTION
687 WHEN OTHERS THEN
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 x_return_msg := 'Arg ID('||px_arg.arg_id||') Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
690 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
691 px_arg.init_locally := TRUE;
692 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
693 END;
694
695 -- Private: used to set up the arg state and perform any pre-GET operations to make sure that all GETS
696 -- proceed in the same way. Only messes with the value fields in the case of constants so we don't have to select out
697 -- the source text again later.
698 PROCEDURE INITIALIZE_ARG(px_arg IN OUT NOCOPY arg,
699 p_arg_context IN arg_context,
700 p_using_splitting IN BOOLEAN,
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_return_msg OUT NOCOPY VARCHAR2)
703 IS
704 PRAGMA AUTONOMOUS_TRANSACTION;
705
706 l_ctxt VARCHAR2(60) := PKG_NAME||'INITIALIZE_ARG';
707
708 l_initialized_success_flag VARCHAR2(3);
709 l_valid_value_flag VARCHAR2(3);
710 l_canonical_value VARCHAR2(4000);
711 l_source_type VARCHAR2(30);
712 l_source_text VARCHAR2(4000);
713 l_source_where_clause VARCHAR2(4000);
714 l_source_final_text VARCHAR2(4000);
715 l_append_rowid_clause BOOLEAN;
716
717 l_return_status VARCHAR2(6);
718 l_return_msg VARCHAR2(2048);
719 BEGIN
720 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
724 fnd_oam_debug.log(1, l_ctxt, 'Argument(Splitting?): '||px_arg.arg_name||'('||FND_OAM_DSCRAM_UTILS_PKG.BOOLEAN_TO_FLAG(p_using_splitting)||')');
721 x_return_status := FND_API.G_RET_STS_ERROR;
722 x_return_msg := '';
723
725
726 --check if we've already done the local init
727 IF px_arg.init_locally THEN
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729 fnd_oam_debug.log(1, l_ctxt, 'Already locally initialized');
730 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
731 RETURN;
732 END IF;
733
734 --before locking the arg, select to see if its initialized already by another thread
735 SELECT initialized_success_flag, valid_value_flag, canonical_value, source_type, source_text, source_final_text
736 INTO l_initialized_success_flag, l_valid_value_flag, l_canonical_value, l_source_type, l_source_text, l_source_final_text
737 FROM fnd_oam_dscram_args_b
738 WHERE arg_id = px_arg.arg_id;
739
740 -- if init success already determined, update our local state variables and return
741 IF l_initialized_success_flag IS NOT NULL THEN
742 UPDATE_STATE_USING_INIT_VALUES(px_arg,
743 p_using_splitting,
744 l_initialized_success_flag,
745 l_valid_value_flag,
746 l_canonical_value,
747 l_source_type,
748 l_source_text,
749 l_source_final_text,
750 x_return_status,
751 x_return_msg);
752 fnd_oam_debug.log(1, l_ctxt, 'Pre-Lock select found already initialized.');
753 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
754 RETURN;
755 END IF;
756
757 -- if we got here, we'll probably be updating the arg so lock it and make sure we still need to update it
758 SELECT initialized_success_flag, valid_value_flag, canonical_value, source_type, source_text, source_where_clause, source_final_text
759 INTO l_initialized_success_flag, l_valid_value_flag, l_canonical_value, l_source_type, l_source_text, l_source_where_clause, l_source_final_text
760 FROM fnd_oam_dscram_args_b
761 WHERE arg_id = px_arg.arg_id
762 FOR UPDATE;
763
764 --check again after the locking select to see if somebody else has already done the init
765 IF l_initialized_success_flag IS NOT NULL THEN
766 UPDATE_STATE_USING_INIT_VALUES(px_arg,
767 p_using_splitting,
768 l_initialized_success_flag,
769 l_valid_value_flag,
770 l_canonical_value,
771 l_source_type,
772 l_source_text,
773 l_source_final_text,
774 x_return_status,
775 x_return_msg);
776 fnd_oam_debug.log(1, l_ctxt, 'Locking select found already initialized.');
777 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
778 ROLLBACK;
779 RETURN;
780 END IF;
781
782 --at this point it's up to this function to do the init, reset the valid value flag to unknown
783 l_valid_value_flag := NULL;
784
785 --for sqls, compose our final source statement from its component parts
786 IF FND_OAM_DSCRAM_UTILS_PKG.SOURCE_TYPE_USES_SQL(l_source_type) THEN
787
788 --see whether we'll be appending the rowid clause or not
789 l_append_rowid_clause := FALSE;
790 IF l_source_type = FND_OAM_DSCRAM_UTILS_PKG.G_SOURCE_SQL_RESTRICTABLE THEN
791 l_append_rowid_clause := p_using_splitting;
792 END IF;
793
794 --prepare the final text from the source_text/where clause
795 FND_OAM_DSCRAM_UTILS_PKG.MAKE_FINAL_SQL_STMT(p_arg_context,
796 l_source_text,
797 l_source_where_clause,
798 l_append_rowid_clause,
799 l_source_final_text,
800 l_return_status,
801 l_return_msg);
802 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
803 fnd_oam_debug.log(6, l_ctxt, 'ARG ID('||px_arg.arg_id||'), failed to create final sql stmt: '||l_return_msg);
804 px_arg.initialized_success_flag := FND_API.G_FALSE;
805 END IF;
806 END IF;
807
808 --After initializing stuff into our local values, try to roll these local values into our state
809 UPDATE_STATE_USING_INIT_VALUES(px_arg,
810 p_using_splitting,
811 l_initialized_success_flag,
812 l_valid_value_flag,
813 l_canonical_value,
814 l_source_type,
815 l_source_text,
816 l_source_final_text,
817 l_return_status,
818 l_return_msg);
819 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
820 x_return_status := l_return_status;
821 x_return_msg := l_return_msg;
822 RAISE INIT_FAILED;
823 END IF;
824
825 --Finally, if we ended up initializing the arg, write out that new state
826 IF px_arg.initialized_success_flag IS NOT NULL THEN
827 UPDATE fnd_oam_dscram_args_b
831 last_update_login = fnd_global.user_id,
828 SET initialized_success_flag = px_arg.initialized_success_flag,
829 source_final_text = l_source_final_text,
830 last_updated_by = fnd_global.user_id,
832 last_update_date = SYSDATE
833 WHERE arg_id = px_arg.arg_id;
834
835 --if the arg is writable and we determined if it has a valid value or not, write out the canonical value and valid_value_flag
836 --For constants, we bend the rules and write them even if it's not writable because otherwise get must incur the cost of messing
837 --with the source_text.
838 IF (px_arg.valid_value_flag IS NOT NULL AND IS_WRITABLE(px_arg)) THEN
839 fnd_oam_debug.log(1, l_ctxt, 'Pre-emptively writing the arg value');
840 UPDATE fnd_oam_dscram_args_b
841 SET valid_value_flag = px_arg.valid_value_flag,
842 canonical_value = px_arg.canonical_value
843 WHERE arg_id = px_arg.arg_id;
844 END IF;
845
846 COMMIT;
847
848 --return success
849 x_return_status := FND_API.G_RET_STS_SUCCESS;
850 ELSE
851 x_return_msg := 'ARG ID('||px_arg.arg_id||'), got to the end but arg was not initialized. This should not happen.';
852 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
853 RAISE INIT_FAILED;
854 END IF;
855
856 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
857 EXCEPTION
858 WHEN INIT_FAILED THEN
859 --x_return_* already set, only raised after locking select
860 BEGIN
861 UPDATE fnd_oam_dscram_args_b
862 SET initialized_success_flag = FND_API.G_FALSE,
863 last_updated_by = fnd_global.user_id,
864 last_update_login = fnd_global.user_id,
865 last_update_date = SYSDATE
866 WHERE arg_id = px_arg.arg_id;
867 COMMIT;
868 px_arg.initialized_success_flag := FND_API.G_FALSE;
869 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
870 EXCEPTION
871 WHEN OTHERS THEN
872 x_return_msg := 'Unexpected Error while processing init_failed: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
873 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
874 ROLLBACK;
875 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
876 END;
877 WHEN OTHERS THEN
878 --here we can't assume we have a lock so leave out update of DB but do update our local state
879 px_arg.initialized_success_flag := FND_API.G_FALSE;
880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
881 x_return_msg := 'Arg ID('||px_arg.arg_id||'), unexpected error while initializing: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
882 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
883 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
884 ROLLBACK;
885 END;
886
887 -- Private, helper to STORE_CANONICAL_ARG_VALUE to perform an insert into the ARG_VALUES table
888 PROCEDURE INSERT_ARG_VALUE(p_arg_id IN NUMBER,
889 p_valid_value_flag IN VARCHAR2,
890 p_canonical_value IN VARCHAR2,
891 p_parent_type IN VARCHAR2,
892 p_parent_id IN NUMBER DEFAULT NULL,
893 p_rowid_lbound IN ROWID DEFAULT NULL,
894 p_rowid_ubound IN ROWID DEFAULT NULL,
895 p_id_lbound IN NUMBER DEFAULT NULL,
896 p_id_ubound IN NUMBER DEFAULT NULL,
897 x_arg_val_id OUT NOCOPY NUMBER)
898 IS
899 l_id NUMBER;
900 BEGIN
901 INSERT INTO FND_OAM_DSCRAM_ARG_VALUES (ARG_VALUE_ID,
902 ARG_ID,
903 PARENT_TYPE,
904 PARENT_ID,
905 ROWID_LBOUND,
906 ROWID_UBOUND,
907 ID_LBOUND,
908 ID_UBOUND,
909 VALID_VALUE_FLAG,
910 CANONICAL_VALUE,
911 CREATED_BY,
912 CREATION_DATE,
913 LAST_UPDATED_BY,
914 LAST_UPDATE_DATE,
915 LAST_UPDATE_LOGIN)
916 VALUES
917 (fnd_oam_dscram_arg_values_s.nextval,
918 p_arg_id,
919 p_parent_type,
920 p_parent_id,
921 p_rowid_lbound,
922 p_rowid_ubound,
923 p_id_lbound,
924 p_id_ubound,
925 p_valid_value_flag,
926 p_canonical_value,
927 FND_GLOBAL.USER_ID,
928 SYSDATE,
929 FND_GLOBAL.USER_ID,
930 SYSDATE,
931 FND_GLOBAL.USER_ID)
932 RETURNING ARG_VALUE_ID INTO l_id;
933 x_arg_val_id := l_id;
934
935 --exceptions passed to store
936 END;
937
938 -- Private, used by GET_CANONICAL_ARG_VALUE to write an arg's canonical value to the DB in the proper place
939 -- Assume conditions surrounding whether we should be writing the arg have already been evaluated.
940 PROCEDURE STORE_ARG_VALUE(px_arg IN OUT NOCOPY arg,
941 x_return_status OUT NOCOPY VARCHAR2,
942 x_return_msg OUT NOCOPY VARCHAR2)
946
943 IS
944
945 l_ctxt VARCHAR2(60) := PKG_NAME||'STORE_ARG_VALUE';
947 l_arg_val_id NUMBER;
948 BEGIN
949 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
950 x_return_status := FND_API.G_RET_STS_ERROR;
951 x_return_msg := '';
952
953 IF px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE THEN
954 --update the main args_b row
955 UPDATE fnd_oam_dscram_args_b
956 SET valid_value_flag = px_arg.valid_value_flag,
957 canonical_value = px_arg.canonical_value,
958 last_updated_by = fnd_global.user_id,
959 last_update_login = fnd_global.user_id,
960 last_update_date = SYSDATE
961 WHERE arg_id = px_arg.arg_id;
962 ELSIF px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER THEN
963 --try to update arg_values with parent_type=worker, failing that insert
964 UPDATE fnd_oam_dscram_arg_values
965 SET valid_value_flag = px_arg.valid_value_flag,
966 canonical_value = px_arg.canonical_value,
967 last_updated_by = fnd_global.user_id,
968 last_update_login = fnd_global.user_id,
969 last_update_date = SYSDATE
970 WHERE arg_id = px_arg.arg_id
971 AND parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_WORKER AND
972 parent_id = FND_OAM_DSCRAM_BUNDLES_PKG.GET_WORKER_ID;
973 IF SQL%ROWCOUNT = 0 THEN
974 fnd_oam_debug.log(1, l_ctxt, 'Inserting per-worker arg value.');
975 INSERT_ARG_VALUE(px_arg.arg_id,
976 px_arg.valid_value_flag,
977 px_arg.canonical_value,
978 FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_WORKER,
979 FND_OAM_DSCRAM_BUNDLES_PKG.GET_WORKER_ID,
980 x_arg_val_id => l_arg_val_id);
981 END IF;
982 ELSIF px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE THEN
983 --update arg_values with range values and parent_type = range
984 UPDATE fnd_oam_dscram_arg_values
985 SET valid_value_flag = px_arg.valid_value_flag,
986 canonical_value = px_arg.canonical_value,
987 rowid_lbound = px_arg.rowid_lbound,
988 rowid_ubound = px_arg.rowid_ubound,
989 last_updated_by = fnd_global.user_id,
990 last_update_login = fnd_global.user_id,
991 last_update_date = SYSDATE
992 WHERE arg_id = px_arg.arg_id AND
993 parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_RANGE AND
994 rowid_lbound = px_arg.rowid_lbound AND
995 rowid_ubound = px_arg.rowid_ubound;
996 IF SQL%ROWCOUNT = 0 THEN
997 fnd_oam_debug.log(1, l_ctxt, 'Inserting per-range arg value.');
998 INSERT_ARG_VALUE(px_arg.arg_id,
999 px_arg.valid_value_flag,
1000 px_arg.canonical_value,
1001 FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_RANGE,
1002 p_rowid_lbound => px_arg.rowid_lbound,
1003 p_rowid_ubound => px_arg.rowid_ubound,
1004 x_arg_val_id => l_arg_val_id);
1005 END IF;
1006 ELSIF px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ALWAYS THEN
1007 fnd_oam_debug.log(1, l_ctxt, 'Inserting always arg value.');
1008 INSERT_ARG_VALUE(px_arg.arg_id,
1009 px_arg.valid_value_flag,
1010 px_arg.canonical_value,
1011 NULL,
1012 p_rowid_lbound => px_arg.rowid_lbound,
1013 p_rowid_ubound => px_arg.rowid_ubound,
1014 x_arg_val_id => l_arg_val_id);
1015 ELSE
1016 x_return_msg := 'Invalid write policy: '||px_arg.write_policy;
1017 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1018 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1019 RETURN;
1020 END IF;
1021
1022 x_return_status := FND_API.G_RET_STS_SUCCESS;
1023 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1027 x_return_msg := 'Arg ID('||px_arg.arg_id||'), unexpected error while storing canonical value: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1028 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1029 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1030 END;
1031
1032 -- Private Wrapper: STORE in an autonomous transaction for cases such as context args where we can't roll back or
1033 -- failure cases where we're going to roll back but we don't want future get_args to keep failing.
1034 PROCEDURE STORE_ARG_VALUE_AUTONOMOUSLY(px_arg IN OUT NOCOPY arg,
1035 x_return_status OUT NOCOPY VARCHAR2,
1036 x_return_msg OUT NOCOPY VARCHAR2)
1037 IS
1038 PRAGMA AUTONOMOUS_TRANSACTION;
1039 BEGIN
1040 STORE_ARG_VALUE(px_arg,
1041 x_return_status,
1042 x_return_msg);
1043 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1044 COMMIT;
1045 ELSE
1046 ROLLBACK;
1047 END IF;
1048 END;
1049
1050 -- Declaration to allow GET_CANONICAL_ARG_VALUE to get values for context args.
1051 PROCEDURE GET_CANONICAL_CTXT_ARG_VALUE(px_arg IN OUT NOCOPY arg,
1052 p_using_splitting IN BOOLEAN,
1053 p_rowid_lbound IN ROWID,
1054 p_rowid_ubound IN ROWID,
1055 p_execution_cursor_id IN INTEGER,
1059 x_arg_lock_handle OUT NOCOPY VARCHAR2,
1056 p_force_store_autonomously IN BOOLEAN,
1057 p_allow_sourcing IN BOOLEAN,
1058 p_release_arg_lock IN BOOLEAN,
1060 x_value OUT NOCOPY VARCHAR2,
1061 x_return_status OUT NOCOPY VARCHAR2,
1062 x_return_msg OUT NOCOPY VARCHAR2);
1063
1064 -- Private: obtains value for the argument using either the local state, external sources or the arg context.
1065 -- If it's writable, also writes out retrieved value to proper location based on write_policy using STORE.
1066 -- For locking the arg, we use a dbms_lock based lock instead of a FOR UPDATE to allow all of the sources/stores to happen in the parent
1067 -- transaction so that stored values are discarded if the results are discarded. Values are stored autonomously if p_force_store_autonomously is set.
1068 -- Allow_sourcing and release_arg_lock can be manipulated to perform a shallow get to see if an arg already has a value and if not, maintain the lock
1069 -- so that the caller can update the arg and release the lock. This is used in the UPDATE_WRITABLE-like procedures to default values.
1070 PROCEDURE GET_CANONICAL_ARG_VALUE(px_arg IN OUT NOCOPY arg,
1071 px_arg_context IN OUT NOCOPY arg_context,
1072 p_using_splitting IN BOOLEAN,
1073 p_rowid_lbound IN ROWID,
1074 p_rowid_ubound IN ROWID,
1075 p_execution_cursor_id IN INTEGER,
1076 p_force_store_autonomously IN BOOLEAN,
1077 p_allow_sourcing IN BOOLEAN,
1078 p_release_arg_lock IN BOOLEAN,
1079 x_arg_lock_handle OUT NOCOPY VARCHAR2,
1080 x_value OUT NOCOPY VARCHAR2,
1081 x_return_status OUT NOCOPY VARCHAR2,
1082 x_return_msg OUT NOCOPY VARCHAR2)
1083 IS
1084 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_CANONICAL_ARG_VALUE';
1085
1086 l_determined_value BOOLEAN;
1087 l_value_requires_store BOOLEAN;
1088 l_valid_value_flag VARCHAR2(3);
1089 l_canonical_value VARCHAR2(2048);
1090 l_lock_handle VARCHAR2(128) := NULL;
1091 l_retval INTEGER;
1092
1093 l_temp NUMBER;
1094 l_ignore VARCHAR2(128);
1095 l_return_status VARCHAR2(6);
1096 l_return_msg VARCHAR2(2048);
1097 BEGIN
1098 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1099 x_return_status := FND_API.G_RET_STS_ERROR;
1100 x_return_msg := '';
1101
1102 fnd_oam_debug.log(1, l_ctxt, 'Argument(ID): '||px_arg.arg_name||'('||px_arg.arg_id||')');
1103
1104 --initialize the argument if it hasn't been initialized locally yet (sets up source_cursor/derived state)
1105 IF NOT px_arg.init_locally THEN
1106 INITIALIZE_ARG(px_arg,
1107 px_arg_context,
1108 p_using_splitting,
1109 l_return_status,
1110 l_return_msg);
1111 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1112 x_return_status := l_return_status;
1113 x_return_msg := l_return_msg;
1114 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1115 END IF;
1116 END IF;
1117
1118 --if the arg went through init before and we stored that it failed, return an error, this is less likely
1119 IF px_arg.initialized_success_flag = FND_API.G_FALSE THEN
1120 x_return_msg := 'Arg ID ('||px_arg.arg_id||'), previous call to init stored that it failed. Get cannot continue.';
1121 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1122 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1123 RETURN;
1124 END IF;
1125
1126 --at this point, we should have a sucessfully initialized arg, try to determine its value
1127 l_determined_value := FALSE;
1128 l_value_requires_store := FALSE;
1129
1130 --now check if valid_value_flag's been set to false meaning we have no value and sourcing fails
1131 IF px_arg.valid_value_flag = FND_API.G_FALSE THEN
1132 --note: this means that a malformed constant will not be overrideable by the arg_context, should be a very infrequent corner case.
1133 x_return_msg := 'Arg ID ('||px_arg.arg_id||'), previous call to get_value stored that fetching the value fails. Exiting.';
1134 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1135 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1136 RETURN;
1137 -- see if we already have a value, skip the check if we write always
1138 ELSIF px_arg.valid_value_flag = FND_API.G_TRUE AND
1139 (NOT (IS_WRITABLE(px_arg)) OR
1140 NOT (px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ALWAYS)) THEN
1141 --if we already know the local value is valid then make sure we match the supplied rowid range as well
1142 IF (px_arg.rowid_lbound IS NOT NULL OR px_arg.rowid_ubound IS NOT NULL) THEN
1143 IF ((px_arg.rowid_lbound IS NULL OR (px_arg.rowid_lbound = p_rowid_lbound)) AND
1144 (px_arg.rowid_ubound IS NULL OR (px_arg.rowid_ubound = p_rowid_ubound))) THEN
1145
1146 x_return_status := FND_API.G_RET_STS_SUCCESS;
1147 x_value := px_arg.canonical_value;
1148 fnd_oam_debug.log(1, l_ctxt, 'Ranged Value cached');
1149 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1153 --if not contingent on a range, the value is the arg's stored canonical value
1150 RETURN;
1151 END IF;
1152 ELSE
1154 x_return_status := FND_API.G_RET_STS_SUCCESS;
1155 x_value := px_arg.canonical_value;
1156 fnd_oam_debug.log(1, l_ctxt, 'Value cached');
1157 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1158 RETURN;
1159 END IF;
1160 ELSE
1161 -- we haven't cached that the value's valid somewhere, but it may have been calculated and stored already in the DB
1162 -- use different select statements to find the latest valid_value_flag and canonical_value
1163 l_valid_value_flag := NULL;
1164 IF px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE THEN
1165 --query the main args_b row
1166 SELECT valid_value_flag, canonical_value
1167 INTO l_valid_value_flag, l_canonical_value
1168 FROM fnd_oam_dscram_args_b
1169 WHERE arg_id = px_arg.arg_id;
1170 fnd_oam_debug.log(1, l_ctxt, 'Write Policy Once: first query valid_value_flag: '||l_valid_value_flag);
1171 ELSIF px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER THEN
1172 --Needed in the case of restart to get the value we may have comitted for this worker
1173 --query arg_values with parent_type=worker
1174 BEGIN
1175 SELECT valid_value_flag, canonical_value
1176 INTO l_valid_value_flag, l_canonical_value
1177 FROM fnd_oam_dscram_arg_values
1178 WHERE arg_id = px_arg.arg_id AND
1179 parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_WORKER AND
1180 parent_id = FND_OAM_DSCRAM_BUNDLES_PKG.GET_WORKER_ID;
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 l_valid_value_flag := NULL;
1184 END;
1185 fnd_oam_debug.log(1, l_ctxt, 'Write Policy Per-Worker: first query valid_value_flag: '||l_valid_value_flag);
1186 ELSIF px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE THEN
1187 --Since we don't share ranges, this won't happen on the initial execute, but it may be necessary on restart
1188 --if the arg's value was comitted since we won't read it back on initialize.
1189 --query arg_values with range values and parent_type = range
1190 BEGIN
1191 SELECT valid_value_flag, canonical_value
1192 INTO l_valid_value_flag, l_canonical_value
1193 FROM fnd_oam_dscram_arg_values
1194 WHERE arg_id = px_arg.arg_id AND
1195 parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_RANGE AND
1196 rowid_lbound = p_rowid_lbound AND
1197 rowid_ubound = p_rowid_ubound;
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 l_valid_value_flag := NULL;
1201 END;
1202 fnd_oam_debug.log(1, l_ctxt, 'Write Policy Per-Range: first query valid_value_flag: '||l_valid_value_flag);
1203 END IF;
1204
1205 --if we found a value for valid_value_flag, we've determined a value or a failure
1206 IF l_valid_value_flag = FND_API.G_TRUE THEN
1207 l_determined_value := TRUE;
1208 ELSIF l_valid_value_flag = FND_API.G_FALSE THEN
1209 --A false means sourcing failes
1210 x_return_msg := 'Arg ID ('||px_arg.arg_id||'), non-locking select: previous call to get_value stored that fetching the value fails. Exiting.';
1211 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1212 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1213 RETURN;
1214 END IF;
1215 END IF;
1216
1217 --for write_once, we need to lock the arg and check again, the rest don't need a lock because there's no cross-worker contention.
1218 IF NOT l_determined_value AND
1219 IS_WRITABLE(px_arg) AND
1220 px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE THEN
1221
1222 --lock the arg
1223 IF FND_OAM_DSCRAM_UTILS_PKG.LOCK_ARG(px_arg.arg_id,
1224 l_lock_handle) THEN
1225 --do a normal select instead of a locking select since the above lock will hold for write_once args even if release_lock
1226 --is set when a worker is the first to determine a value for the arg. The lock will be released when the first worker
1227 --to set the arg commits or rolls back.
1228 --Known Issue: when the run is in a non-normal mode, write-once args will be written by each worker because the batch
1229 --is never comitted. In the normal mode, the first successful batch is the only one to write the arg value.
1230 SELECT valid_value_flag, canonical_value
1231 INTO l_valid_value_flag, l_canonical_value
1232 FROM fnd_oam_dscram_args_b
1233 WHERE arg_id = px_arg.arg_id;
1234 fnd_oam_debug.log(1, l_ctxt, 'Write Policy Once, locking query valid_value_flag: '||l_valid_value_flag);
1235
1236 --re-check the valid value flag
1237 IF l_valid_value_flag = FND_API.G_TRUE THEN
1238 l_determined_value := TRUE;
1239 ELSIF l_valid_value_flag = FND_API.G_FALSE THEN
1240 --A false means sourcing failed previously
1241 px_arg.valid_value_flag := FND_API.G_FALSE;
1242 x_return_msg := 'Arg ID ('||px_arg.arg_id||'), locking select: previous call to get_value stored that fetching the value fails. Exiting.';
1243 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1244 RAISE GET_FAILED;
1245 END IF;
1246 END IF;
1247 END IF;
1248
1249 --at this point, we know whether the arg currently has a valid value based on the write policy. If we haven't
1253 px_arg_context.EXISTS(px_arg.arg_name) THEN
1250 --determined a value, before sourcing it using the source_* fields, try to obtain it from the context if the arg allows override.
1251 IF NOT l_determined_value AND
1252 px_arg.allow_override_source AND
1254
1255 IF IS_READABLE(px_arg_context(px_arg.arg_name)) AND
1256 px_arg_context(px_arg.arg_name).datatype = px_arg.datatype THEN
1257
1258 GET_CANONICAL_CTXT_ARG_VALUE(px_arg_context(px_arg.arg_name),
1259 p_using_splitting,
1260 p_rowid_lbound,
1261 p_rowid_ubound,
1262 p_execution_cursor_id,
1263 p_force_store_autonomously,
1264 TRUE,
1265 TRUE,
1266 l_ignore,
1267 l_canonical_value,
1268 l_return_status,
1269 l_return_msg);
1270 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1271 l_valid_value_flag := FND_API.G_TRUE;
1272 l_determined_value := TRUE;
1273 l_value_requires_store := TRUE;
1274 END IF;
1275 END IF;
1276 END IF;
1277
1278 -- skip the sourcing section if we disallow it
1279 IF p_allow_sourcing THEN
1280
1281 --This is a bit of a hack, but constants have the valid_value flag set to NULL to allow the above logic to try and fetch values from
1282 --different locations based on the write_policy so that arg_contexts can be used to get values. The correct constant value is stored
1283 --in the canonical value. This represents one case where the valid_value_flag does not need to be true to use the value of the
1284 --canonical value.
1285 IF NOT l_determined_value AND px_arg.is_constant AND l_valid_value_flag IS NULL THEN
1286 l_valid_value_flag := FND_API.G_TRUE;
1287 l_canonical_value := px_arg.canonical_value;
1288 l_determined_value := TRUE;
1289 l_value_requires_store := TRUE;
1290 END IF;
1291
1292 --if the value is still unknown, first try state sourcing since it's cheap
1293 IF NOT l_determined_value AND px_arg.source_state_key IS NOT NULL THEN
1294 GET_CANONICAL_VALUE_FOR_KEY(px_arg,
1295 px_arg.source_state_key,
1296 p_using_splitting,
1297 p_rowid_lbound,
1298 p_rowid_ubound,
1299 l_canonical_value,
1300 l_return_status,
1301 l_return_msg);
1302 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1303 BEGIN
1304 VALIDATE_CANONICAL_VALUE(px_arg,
1305 l_canonical_value);
1306 l_valid_value_flag := FND_API.G_TRUE;
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 l_valid_value_flag := FND_API.G_FALSE;
1310 END;
1311 l_determined_value := TRUE;
1312 l_value_requires_store := TRUE;
1313 ELSIF l_return_status = FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_MISSING_STATE THEN
1314 -- if we didn't fail as much as we were called under the wrong circumstances, don't store this as a failure either
1315 x_return_status := l_return_status;
1316 x_return_msg := l_return_msg;
1317 RAISE GET_FAILED;
1318 ELSE
1319 --here we really did fail
1320 l_valid_value_flag := FND_API.G_FALSE;
1321 l_determined_value := TRUE;
1322 l_value_requires_store := TRUE;
1323 END IF;
1324
1325 END IF;
1326
1327 --if the value is still unknown, next try execution cursor sourcing since it's also relatively cheap
1328 IF NOT l_determined_value AND px_arg.source_use_exec_cursor AND p_execution_cursor_id IS NOT NULL THEN
1329 GET_CANONICAL_VALUE_FROM_CUR(px_arg,
1330 p_execution_cursor_id,
1331 l_canonical_value,
1332 l_return_status,
1333 l_return_msg);
1334 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1335 --don't validate, the cursor get is strongly typed
1336 l_valid_value_flag := FND_API.G_TRUE;
1337 l_determined_value := TRUE;
1338 l_value_requires_store := TRUE;
1339 ELSE
1340 --failed
1341 l_valid_value_flag := FND_API.G_FALSE;
1342 l_determined_value := TRUE;
1343 l_value_requires_store := TRUE;
1344 END IF;
1345 END IF;
1346
1347 --if the value is still unknown, try to do sql-based sourcing
1348 IF NOT l_determined_value AND px_arg.source_cursor_id IS NOT NULL THEN
1349 --now source the value for sql using the source_cursor_id, constants should have already been rolled into the value by init
1350 GET_CANONICAL_VALUE_FOR_SQL(px_arg,
1351 NULL,
1352 p_rowid_lbound,
1353 p_rowid_ubound,
1354 l_canonical_value,
1355 l_return_status,
1356 l_return_msg);
1357 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1358 BEGIN
1362 EXCEPTION
1359 VALIDATE_CANONICAL_VALUE(px_arg,
1360 l_canonical_value);
1361 l_valid_value_flag := FND_API.G_TRUE;
1363 WHEN OTHERS THEN
1364 l_valid_value_flag := FND_API.G_FALSE;
1365 END;
1366 l_determined_value := TRUE;
1367 l_value_requires_store := TRUE;
1368 ELSIF l_return_status = FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_MISSING_BINDS THEN
1369 -- if we didn't fail as much as we were called under the wrong circumstances, don't store this as a failure either
1370 x_return_status := l_return_status;
1371 x_return_msg := l_return_msg;
1372 RAISE GET_FAILED;
1373 ELSE
1374 --here we really did fail
1375 l_valid_value_flag := FND_API.G_FALSE;
1376 l_determined_value := TRUE;
1377 l_value_requires_store := TRUE;
1378 END IF;
1379 END IF;
1380
1381 END IF; --end p_allow_sourcing check
1382
1383 --if we don't have a value here, any arg can try to source from the context before being flagged as a failure.
1384 --This allows placeholder args for dmls where there is no source, just the expectation of inheriting a value from the context
1385 IF NOT l_determined_value AND
1386 px_arg_context.EXISTS(px_arg.arg_name) THEN
1387 fnd_oam_debug.log(1, l_ctxt, 'Doing Last ditch context check.');
1388 IF IS_READABLE(px_arg_context(px_arg.arg_name)) AND
1389 px_arg_context(px_arg.arg_name).datatype = px_arg.datatype THEN
1390
1391 GET_CANONICAL_CTXT_ARG_VALUE(px_arg_context(px_arg.arg_name),
1392 p_using_splitting,
1393 p_rowid_lbound,
1394 p_rowid_ubound,
1395 p_execution_cursor_id,
1396 p_force_store_autonomously,
1397 TRUE,
1398 TRUE,
1399 l_ignore,
1400 l_canonical_value,
1401 l_return_status,
1402 l_return_msg);
1403 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1404 l_valid_value_flag := FND_API.G_TRUE;
1405 l_determined_value := TRUE;
1406 l_value_requires_store := TRUE;
1407 ELSIF l_return_status = FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_MISSING_BINDS THEN
1408 --since this is the last thing before failure, let missing binds return
1409 x_return_status := l_return_status;
1410 x_return_msg := l_return_msg;
1411 fnd_oam_debug.log(1, l_ctxt, 'Returning Missing Binds');
1412 RETURN;
1413 END IF;
1414 END IF;
1415 END IF;
1416
1417 --at this point, we've done everything we can to determine the value, if we haven't and we allowed sourcing then the get's a failure
1418 IF NOT l_determined_value AND p_allow_sourcing THEN
1419 fnd_oam_debug.log(6, l_ctxt, 'Arg ID ('||px_arg.arg_id||'), failed to determine a value.');
1420 l_valid_value_flag := FND_API.G_FALSE;
1421 l_determined_value := TRUE;
1422 l_value_requires_store := TRUE;
1423 END IF;
1424
1425 -- if we found a value, sync up the value state with the get even if we're not going to write it out
1426 IF l_determined_value THEN
1427 px_arg.valid_value_flag := l_valid_value_flag;
1428 IF FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(l_valid_value_flag) THEN
1429 px_arg.canonical_value := l_canonical_value;
1430 ELSE
1431 px_arg.canonical_value := NULL;
1432 END IF;
1433
1434 --if we determined a value and we're fetching values for each range, store the range used.
1435 IF l_determined_value AND px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE THEN
1436 px_arg.rowid_lbound := p_rowid_lbound;
1437 px_arg.rowid_ubound := p_rowid_ubound;
1438 END IF;
1439 END IF;
1440
1441 --if the get fetched a new value that needs to be stored, do that also
1442 IF IS_WRITABLE(px_arg) AND l_value_requires_store THEN
1443 --store the value autonomously when instructed. We don't need to write autonomously for
1444 --write once args because other workers will hang on the write once lock and we don't want to
1445 --give up the value until we're sure it works for this range. Also, it messes up the print_arg_context.
1446 IF p_force_store_autonomously THEN
1447 STORE_ARG_VALUE_AUTONOMOUSLY(px_arg,
1448 l_return_status,
1449 l_return_msg);
1450 ELSE
1451 STORE_ARG_VALUE(px_arg,
1452 l_return_status,
1453 l_return_msg);
1454 END IF;
1455 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1456 px_arg.valid_value_flag := FND_API.G_FALSE;
1457 x_return_status := l_return_status;
1458 x_return_msg := l_return_msg;
1459 RAISE GET_FAILED;
1460 END IF;
1461 END IF;
1462
1463 --release the lock on the arg if told to do so and the arg isn't a just-stored write-once value.
1464 --Just-written, write-once args hold onto the lock until the batch is comitted or rolled back so that other
1465 --workers don't compute a new value. These args are treated as possible synchronization points.
1466 IF p_release_arg_lock AND
1467 ((px_arg.write_policy <> FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE) OR
1471 IF l_retval <> 0 THEN
1468 (px_arg.write_policy = FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE AND NOT l_value_requires_store)) THEN
1469 IF l_lock_handle IS NOT NULL THEN
1470 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
1472 fnd_oam_debug.log(6, l_ctxt, 'Failed to release arg lock: '||l_retval);
1473 END IF;
1474 END IF;
1475 x_arg_lock_handle := NULL;
1476 ELSE
1477 x_arg_lock_handle := l_lock_handle;
1478 END IF;
1479
1480 --done, change return status based on whether we found a valid value even if we didn't store it
1481 IF FND_OAM_DSCRAM_UTILS_PKG.FLAG_TO_BOOLEAN(l_valid_value_flag) THEN
1482 x_return_status := FND_API.G_RET_STS_SUCCESS;
1483 x_value := l_canonical_value;
1484 ELSIF (NOT p_allow_sourcing AND l_valid_value_flag IS NULL) THEN
1485 --return success if something which was unallowed to source ended up without a value, since we're only
1486 --doing a shallow test to see if it has a value. Caller must check px_arg's valid value flag to see if
1487 --it's a real successful get or just a sucessful lack of failure.
1488 x_return_status := FND_API.G_RET_STS_SUCCESS;
1489 x_value := NULL;
1490 END IF;
1491 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1492 EXCEPTION
1493 WHEN GET_FAILED THEN
1494 IF l_lock_handle IS NOT NULL THEN
1495 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
1496 IF l_retval <> 0 THEN
1497 fnd_oam_debug.log(6, l_ctxt, 'Failed to release arg lock: '||l_retval);
1498 END IF;
1499 END IF;
1500
1501 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1502 RETURN;
1503 WHEN OTHERS THEN
1504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1505 x_return_msg := 'Arg ID('||px_arg.arg_id||'), unexpected error while getting canonical value: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1506 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1507
1508 --store that the arg's value yields failure
1509 BEGIN
1510 IF IS_WRITABLE(px_arg) THEN
1511 px_arg.valid_value_flag := FND_API.G_FALSE;
1512 px_arg.canonical_value := NULL;
1513 px_arg.rowid_lbound := p_rowid_lbound;
1514 px_arg.rowid_ubound := p_rowid_ubound;
1515 STORE_ARG_VALUE_AUTONOMOUSLY(px_arg,
1516 l_return_status,
1517 l_return_msg);
1518 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1519 fnd_oam_debug.log(6, l_ctxt, 'Store failure failed('||l_return_status||'): '||l_return_msg);
1520 END IF;
1521 END IF;
1522 EXCEPTION
1523 WHEN OTHERS THEN
1524 fnd_oam_debug.log(6, l_ctxt, 'Exception while storing failed arg fetch: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1525 END;
1526 IF l_lock_handle IS NOT NULL THEN
1527 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
1528 IF l_retval <> 0 THEN
1529 fnd_oam_debug.log(6, l_ctxt, 'Failed to release arg lock: '||l_retval);
1530 END IF;
1531 END IF;
1532 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1533 END;
1534
1535 -- Private Wrapper for context args
1536 PROCEDURE GET_CANONICAL_CTXT_ARG_VALUE(px_arg IN OUT NOCOPY arg,
1537 p_using_splitting IN BOOLEAN,
1538 p_rowid_lbound IN ROWID,
1539 p_rowid_ubound IN ROWID,
1540 p_execution_cursor_id IN INTEGER,
1541 p_force_store_autonomously IN BOOLEAN,
1542 p_allow_sourcing IN BOOLEAN,
1543 p_release_arg_lock IN BOOLEAN,
1544 x_arg_lock_handle OUT NOCOPY VARCHAR2,
1545 x_value OUT NOCOPY VARCHAR2,
1546 x_return_status OUT NOCOPY VARCHAR2,
1547 x_return_msg OUT NOCOPY VARCHAR2)
1548 IS
1549 l_empty_arg_context arg_context;
1550 BEGIN
1551 GET_CANONICAL_ARG_VALUE(px_arg,
1552 l_empty_arg_context,
1553 p_using_splitting,
1554 p_rowid_lbound,
1555 p_rowid_ubound,
1556 p_execution_cursor_id,
1557 p_force_store_autonomously,
1558 p_allow_sourcing,
1559 p_release_arg_lock,
1560 x_arg_lock_handle,
1561 x_value,
1562 x_return_status,
1563 x_return_msg);
1564 END;
1565
1566 --Another, simpler wrapper used by internal_print_arg_context
1567 PROCEDURE GET_CANONICAL_CTXT_ARG_VALUE(px_arg IN OUT NOCOPY arg,
1568 p_using_splitting IN BOOLEAN,
1569 x_value OUT NOCOPY VARCHAR2,
1570 x_return_status OUT NOCOPY VARCHAR2,
1571 x_return_msg OUT NOCOPY VARCHAR2)
1572 IS
1573 l_empty_arg_context arg_context;
1574 l_ignore VARCHAR2(128);
1575 BEGIN
1576 GET_CANONICAL_ARG_VALUE(px_arg,
1577 l_empty_arg_context,
1581 NULL,
1578 p_using_splitting,
1579 NULL,
1580 NULL,
1582 FALSE,
1583 TRUE,
1584 TRUE,
1585 l_ignore,
1586 x_value,
1587 x_return_status,
1588 x_return_msg);
1589 END;
1590
1591 -- Public, getter for type VARCHAR2
1592 PROCEDURE GET_ARG_VALUE(px_arg IN OUT NOCOPY arg,
1593 px_arg_context IN OUT NOCOPY arg_context,
1594 p_using_splitting IN BOOLEAN,
1595 p_rowid_lbound IN ROWID,
1596 p_rowid_ubound IN ROWID,
1597 p_execution_cursor_id IN INTEGER,
1598 x_value OUT NOCOPY VARCHAR2,
1599 x_return_status OUT NOCOPY VARCHAR2,
1600 x_return_msg OUT NOCOPY VARCHAR2)
1601 IS
1602 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_ARG_VALUE(VARCHAR2)';
1603
1604 l_ignore VARCHAR2(128);
1605 l_varchar2 VARCHAR2(4000);
1606 l_return_status VARCHAR2(6);
1607 l_return_msg VARCHAR2(2048);
1608 BEGIN
1609 x_return_status := FND_API.G_RET_STS_ERROR;
1610 x_return_msg := '';
1611 GET_CANONICAL_ARG_VALUE(px_arg,
1612 px_arg_context,
1613 p_using_splitting,
1614 p_rowid_lbound,
1615 p_rowid_ubound,
1616 p_execution_cursor_id,
1617 FALSE,
1618 TRUE,
1619 TRUE,
1620 l_ignore,
1621 l_varchar2,
1622 l_return_status,
1623 l_return_msg);
1624 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1625 x_return_status := l_return_status;
1626 x_return_msg := l_return_msg;
1627 x_value := NULL;
1628 RETURN;
1629 END IF;
1630 x_value := l_varchar2;
1631
1632 --return success
1633 x_return_status := FND_API.G_RET_STS_SUCCESS;
1634 RETURN;
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1638 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1639 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1640 x_value := NULL;
1641 END;
1642
1643 -- Public, getter for type NUMBER
1644 PROCEDURE GET_ARG_VALUE(px_arg IN OUT NOCOPY arg,
1645 px_arg_context IN OUT NOCOPY arg_context,
1646 p_using_splitting IN BOOLEAN,
1647 p_rowid_lbound IN ROWID,
1648 p_rowid_ubound IN ROWID,
1649 p_execution_cursor_id IN INTEGER,
1650 x_value OUT NOCOPY NUMBER,
1651 x_return_status OUT NOCOPY VARCHAR2,
1652 x_return_msg OUT NOCOPY VARCHAR2)
1653 IS
1654 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_ARG_VALUE(NUMBER)';
1655
1656 l_ignore VARCHAR2(128);
1657 l_varchar2 VARCHAR2(4000);
1658 l_return_status VARCHAR2(6);
1659 l_return_msg VARCHAR2(2048);
1660 BEGIN
1661 x_return_status := FND_API.G_RET_STS_ERROR;
1662 x_return_msg := '';
1663 GET_CANONICAL_ARG_VALUE(px_arg,
1664 px_arg_context,
1665 p_using_splitting,
1666 p_rowid_lbound,
1667 p_rowid_ubound,
1668 p_execution_cursor_id,
1669 FALSE,
1670 TRUE,
1671 TRUE,
1672 l_ignore,
1673 l_varchar2,
1674 l_return_status,
1675 l_return_msg);
1676 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1677 x_return_status := l_return_status;
1678 x_return_msg := l_return_msg;
1679 x_value := NULL;
1680 RETURN;
1681 END IF;
1682 x_value := FND_NUMBER.CANONICAL_TO_NUMBER(l_varchar2);
1683
1684 --return success
1685 x_return_status := FND_API.G_RET_STS_SUCCESS;
1686 RETURN;
1687 EXCEPTION
1688 WHEN OTHERS THEN
1689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1690 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1691 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1692 x_value := NULL;
1693 END;
1694
1695 -- Public, getter for type DATE
1696 PROCEDURE GET_ARG_VALUE(px_arg IN OUT NOCOPY arg,
1697 px_arg_context IN OUT NOCOPY arg_context,
1698 p_using_splitting IN BOOLEAN,
1699 p_rowid_lbound IN ROWID,
1700 p_rowid_ubound IN ROWID,
1701 p_execution_cursor_id IN INTEGER,
1702 x_value OUT NOCOPY DATE,
1706 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_ARG_VALUE(DATE)';
1703 x_return_status OUT NOCOPY VARCHAR2,
1704 x_return_msg OUT NOCOPY VARCHAR2)
1705 IS
1707
1708 l_ignore VARCHAR2(128);
1709 l_varchar2 VARCHAR2(4000);
1710 l_return_status VARCHAR2(6);
1711 l_return_msg VARCHAR2(2048);
1712 BEGIN
1713 x_return_status := FND_API.G_RET_STS_ERROR;
1714 x_return_msg := '';
1715 GET_CANONICAL_ARG_VALUE(px_arg,
1716 px_arg_context,
1717 p_using_splitting,
1718 p_rowid_lbound,
1719 p_rowid_ubound,
1720 p_execution_cursor_id,
1721 FALSE,
1722 TRUE,
1723 TRUE,
1724 l_ignore,
1725 l_varchar2,
1726 l_return_status,
1727 l_return_msg);
1728 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1729 x_return_status := l_return_status;
1730 x_return_msg := l_return_msg;
1731 x_value := NULL;
1732 RETURN;
1733 END IF;
1734 x_value := FND_DATE.CANONICAL_TO_DATE(l_varchar2);
1735
1736 --return success
1737 x_return_status := FND_API.G_RET_STS_SUCCESS;
1738 RETURN;
1739 EXCEPTION
1740 WHEN OTHERS THEN
1741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1742 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1743 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1744 x_value := NULL;
1745 END;
1746
1747 -- Public, getter for type ROWID
1748 PROCEDURE GET_ARG_VALUE_ROWID(px_arg IN OUT NOCOPY arg,
1749 px_arg_context IN OUT NOCOPY arg_context,
1750 p_using_splitting IN BOOLEAN,
1751 p_rowid_lbound IN ROWID,
1752 p_rowid_ubound IN ROWID,
1753 p_execution_cursor_id IN INTEGER,
1754 x_value OUT NOCOPY ROWID,
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_return_msg OUT NOCOPY VARCHAR2)
1757 IS
1758 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_ARG_VALUE_ROWID()';
1759
1760 l_ignore VARCHAR2(128);
1761 l_varchar2 VARCHAR2(4000);
1762 l_return_status VARCHAR2(6);
1763 l_return_msg VARCHAR2(2048);
1764 BEGIN
1765 x_return_status := FND_API.G_RET_STS_ERROR;
1766 x_return_msg := '';
1767 GET_CANONICAL_ARG_VALUE(px_arg,
1768 px_arg_context,
1769 p_using_splitting,
1770 p_rowid_lbound,
1771 p_rowid_ubound,
1772 p_execution_cursor_id,
1773 FALSE,
1774 TRUE,
1775 TRUE,
1776 l_ignore,
1777 l_varchar2,
1778 l_return_status,
1779 l_return_msg);
1780 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1781 x_return_status := l_return_status;
1782 x_return_msg := l_return_msg;
1783 x_value := NULL;
1784 RETURN;
1785 END IF;
1786 x_value := CHARTOROWID(l_varchar2);
1787
1788 --return success
1789 x_return_status := FND_API.G_RET_STS_SUCCESS;
1790 RETURN;
1791 EXCEPTION
1792 WHEN OTHERS THEN
1793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1795 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1796 x_value := NULL;
1797 END;
1798
1799 --Private, used by internal_print_arg_context to reset an arg to an unknown initialized status
1800 PROCEDURE RESET_INITIALIZED_AUTONOMOUSLY(p_arg_id IN NUMBER)
1801 IS
1802 PRAGMA AUTONOMOUS_TRANSACTION;
1803
1804 l_ctxt VARCHAR2(60) := PKG_NAME||'RESET_INITIALIZED_AUTONOMOUSLY';
1805 BEGIN
1806 UPDATE fnd_oam_dscram_args_b
1807 SET initialized_success_flag = NULL,
1808 last_updated_by = fnd_global.user_id,
1809 last_update_login = fnd_global.user_id,
1810 last_update_date = SYSDATE
1811 WHERE arg_id = p_arg_id;
1812 COMMIT;
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 fnd_oam_debug.log(6, l_ctxt, 'Arg ID('||p_arg_id||'), failed to reset initialized flag: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
1816 ROLLBACK;
1817 END;
1818
1819 -- Private
1820 -- Debug method to print out an arg context.
1821 -- *WARNING*: performs rollbacks when p_get_values=TRUE to keep from comitting changes. Should only be called
1822 -- when not in the middle of a transaction that did work. If p_get_values=FALSE, can be called whenever.
1823 PROCEDURE INTERNAL_PRINT_ARG_CONTEXT(p_arg_ctxt IN OUT NOCOPY arg_context,
1824 p_get_values IN BOOLEAN DEFAULT FALSE,
1825 p_using_splitting IN BOOLEAN DEFAULT FALSE,
1826 x_return_status OUT NOCOPY VARCHAR2,
1827 x_return_msg OUT NOCOPY VARCHAR2)
1828 IS
1832 l_val VARCHAR2(4000);
1829 l_ctxt VARCHAR2(60) := PKG_NAME||'INTERNAL_PRINT_ARG_CONTEXT';
1830
1831 l_s VARCHAR2(60);
1833 l_prev_init_flag VARCHAR2(3);
1834 l_prev_val VARCHAR2(4000);
1835 l_prev_valid_value_flag VARCHAR2(3);
1836 l_return_status VARCHAR2(6);
1837 l_return_msg VARCHAR2(2048);
1838 BEGIN
1839 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1840 x_return_status := FND_API.G_RET_STS_ERROR;
1841 x_return_msg := '';
1842
1843 l_s := p_arg_ctxt.FIRST;
1844 WHILE l_s IS NOT NULL LOOP
1845 fnd_oam_debug.log(1, l_ctxt, 'Arg Name(Permissions): '||p_arg_ctxt(l_s).arg_name||'('||p_arg_ctxt(l_s).permissions||')');
1846 IF p_get_values AND
1847 IS_READABLE(p_arg_ctxt(l_s)) THEN
1848
1849 --go ahead and fetch the value
1850 l_prev_init_flag := p_arg_ctxt(l_s).initialized_success_flag;
1851 l_prev_valid_value_flag := p_arg_ctxt(l_s).valid_value_flag;
1852 l_prev_val := p_arg_ctxt(l_s).canonical_value;
1853 GET_CANONICAL_CTXT_ARG_VALUE(p_arg_ctxt(l_s),
1854 p_using_splitting,
1855 l_val,
1856 l_return_status,
1857 l_return_msg);
1858 --drop any arg state/values we just computed in the transaction
1859 ROLLBACK;
1860
1861 --print the value if we suceeded
1862 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1863 fnd_oam_debug.log(1, l_ctxt, 'Value: '||l_val);
1864 END IF;
1865
1866 --reset the arg's local state
1867 p_arg_ctxt(l_s).init_locally := FALSE;
1868 p_arg_ctxt(l_s).initialized_success_flag := l_prev_init_flag;
1869 p_arg_ctxt(l_s).valid_value_flag := l_prev_valid_value_flag;
1870 p_arg_ctxt(l_s).canonical_value := l_prev_val;
1871 IF l_prev_init_flag IS NULL THEN
1872 --reset state set autonomously
1873 RESET_INITIALIZED_AUTONOMOUSLY(p_arg_ctxt(l_s).arg_id);
1874 END IF;
1875
1876 --fail if the context arg was not failed sucessfully, ignore missing bind cases
1877 IF l_return_status NOT IN (FND_API.G_RET_STS_SUCCESS,
1878 FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_MISSING_BINDS) THEN
1879 x_return_status := l_return_status;
1880 x_return_msg := l_return_msg;
1881 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1882 ROLLBACK;
1883 RETURN;
1884 END IF;
1885 ELSE
1886 IF p_arg_ctxt(l_s).valid_value_flag = FND_API.G_TRUE THEN
1887 fnd_oam_debug.log(1, l_ctxt, 'Value: '||p_arg_ctxt(l_s).canonical_value);
1888 ELSE
1889 fnd_oam_debug.log(1, l_ctxt, 'Value: ?');
1890 END IF;
1891 END IF;
1892
1893 l_s := p_arg_ctxt.NEXT(l_s);
1894 END LOOP;
1895
1896 x_return_status := FND_API.G_RET_STS_SUCCESS;
1897 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1901 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1902 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1903 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1904 END;
1905
1906 -- Public
1907 PROCEDURE PRINT_ARG_CONTEXT(px_arg_context IN OUT NOCOPY arg_context)
1908 IS
1909 l_return_status VARCHAR2(6);
1910 l_return_msg VARCHAR2(2048);
1911 BEGIN
1912 --call the internal print_arg_context but don't allow it to fetch values
1913 INTERNAL_PRINT_ARG_CONTEXT(px_arg_context,
1914 FALSE,
1915 FALSE,
1916 l_return_status,
1917 l_return_msg);
1918 END;
1919
1920 --Private, used for debug to print an arg list
1921 PROCEDURE PRINT_ARG_LIST(p_arg_list IN OUT NOCOPY arg_list,
1922 x_return_status OUT NOCOPY VARCHAR2,
1923 x_return_msg OUT NOCOPY VARCHAR2)
1924 IS
1925 l_ctxt VARCHAR2(60) := PKG_NAME||'PRINT_ARG_LIST';
1926
1927 k NUMBER;
1928 l_prev_init_flag VARCHAR2(3);
1929 l_prev_val VARCHAR2(4000);
1930 l_prev_valid_value_flag VARCHAR2(3);
1931 l_val VARCHAR2(4000);
1932 l_ignore VARCHAR2(128);
1933
1934 l_return_status VARCHAR2(6);
1935 l_return_msg VARCHAR2(2048);
1936 BEGIN
1937 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1938 x_return_status := FND_API.G_RET_STS_ERROR;
1939 x_return_msg := '';
1940
1941 k := p_arg_list.FIRST;
1942 WHILE k IS NOT NULL LOOP
1943 fnd_oam_debug.log(1, l_ctxt, 'Arg Name(Permissions): '||p_arg_list(k).arg_name||'('||p_arg_list(k).permissions||')');
1944
1945 --skip fetching the value since this is typically proceeded by a call to bind_args which does the gets
1946 IF p_arg_list(k).valid_value_flag = FND_API.G_TRUE THEN
1947 fnd_oam_debug.log(1, l_ctxt, 'Value: '||l_val);
1948 ELSE
1949 fnd_oam_debug.log(1, l_ctxt, 'Value: ?');
1950 END IF;
1951
1952 k := p_arg_list.NEXT(k);
1953 END LOOP;
1954
1955 x_return_status := FND_API.G_RET_STS_SUCCESS;
1956 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1957 EXCEPTION
1958 WHEN OTHERS THEN
1964
1959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
1961 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
1962 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
1963 END;
1965 -- Public
1966 PROCEDURE FETCH_RUN_ARG_CONTEXT(p_run_id IN NUMBER,
1967 x_arg_context OUT NOCOPY arg_context,
1968 x_return_status OUT NOCOPY VARCHAR2,
1969 x_return_msg OUT NOCOPY VARCHAR2)
1970 IS
1971 l_ctxt VARCHAR2(60) := PKG_NAME||'FETCH_RUN_ARG_CONTEXT';
1972
1973 l_empty_arg_context arg_context;
1974 l_arg_ctxt arg_context;
1975 l_arg arg;
1976
1977 l_arg_ids dbms_sql.number_table;
1978 l_arg_names dbms_sql.varchar2_table;
1979 l_initialized_success_flags dbms_sql.varchar2_table;
1980 l_allow_override_source_flags dbms_sql.varchar2_table;
1981 l_binding_enabled_flags dbms_sql.varchar2_table;
1982 l_permissions dbms_sql.varchar2_table;
1983 l_write_policies dbms_sql.varchar2_table;
1984 l_datatypes dbms_sql.varchar2_table;
1985 l_valid_value_flags dbms_sql.varchar2_table;
1986 l_canonical_values long_varchar2_table;
1987
1988 l_return_status VARCHAR2(6);
1989 l_return_msg VARCHAR2(2048);
1990 k NUMBER;
1991 l_ignore BOOLEAN;
1992 BEGIN
1993 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
1994 x_return_status := FND_API.G_RET_STS_ERROR;
1995 x_return_msg := '';
1996
1997 -- grab all the global and run args, global before run to allow run to override
1998 -- put both readable and writable args in the context
1999 SELECT arg_id, arg_name, initialized_success_flag, allow_override_source_flag, binding_enabled_flag, permissions, write_policy, datatype, valid_value_flag, canonical_value
2000 BULK COLLECT INTO l_arg_ids, l_arg_names, l_initialized_success_flags, l_allow_override_source_flags, l_binding_enabled_flags, l_permissions,
2001 l_write_policies, l_datatypes, l_valid_value_flags, l_canonical_values
2002 FROM fnd_oam_dscram_args_b
2003 WHERE ((parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_GLOBAL) OR
2004 ((parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_RUN) AND
2005 (parent_id = p_run_id)))
2006 AND enabled_flag = FND_API.G_TRUE
2007 ORDER BY parent_type ASC;
2008
2009 k := l_arg_ids.FIRST;
2010 WHILE k IS NOT NULL LOOP
2011 --add the arg to the context
2012 l_ignore := ADD_ARG_TO_CONTEXT(l_arg_ctxt,
2013 l_arg_ids(k),
2014 l_arg_names(k),
2015 l_initialized_success_flags(k),
2016 l_allow_override_source_flags(k),
2017 l_binding_enabled_flags(k),
2018 l_permissions(k),
2019 l_write_policies(k),
2020 l_datatypes(k),
2021 l_valid_value_flags(k),
2022 l_canonical_values(k));
2023
2024 k := l_arg_ids.NEXT(k);
2025 END LOOP;
2026
2027 IF FND_OAM_DSCRAM_UTILS_PKG.RUN_IS_DIAGNOSTIC THEN
2028 --print the arg context and allow failures here to indicate an overall failure so
2029 --a diagnostic test can detect it
2033 l_return_status,
2030 internal_print_arg_context(l_arg_ctxt,
2031 TRUE,
2032 FALSE,
2034 l_return_msg);
2035 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2036 x_return_status := l_return_status;
2037 x_return_msg := l_return_msg;
2038 x_arg_context := l_empty_arg_context;
2039 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2040 RETURN;
2041 END IF;
2042 ELSIF fnd_oam_debug.test(1, l_ctxt) THEN
2043 --try to print the arg context but don't init or fail if bad.
2044 print_arg_context(l_arg_ctxt);
2045 END IF;
2046
2047 --return the context
2048 x_return_status := FND_API.G_RET_STS_SUCCESS;
2049 x_arg_context := l_arg_ctxt;
2050 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2051 EXCEPTION
2052 WHEN OTHERS THEN
2053 x_arg_context := l_empty_arg_context;
2054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2055 x_return_msg := 'Unhandled Exception: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
2056 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2057 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2058 END;
2059
2060 -- Public
2061 PROCEDURE FETCH_ARG_LIST(p_parent_type IN VARCHAR2,
2062 p_parent_id IN NUMBER,
2063 x_arg_list OUT NOCOPY arg_list,
2064 x_has_writable OUT NOCOPY BOOLEAN,
2065 x_return_status OUT NOCOPY VARCHAR2,
2066 x_return_msg OUT NOCOPY VARCHAR2)
2067 IS
2068 l_ctxt VARCHAR2(60) := PKG_NAME||'FETCH_ARG_LIST';
2069
2070 l_arg arg;
2071 l_arg_list arg_list := arg_list();
2072 l_empty_arg_list arg_list := arg_list();
2073 l_has_writable BOOLEAN := FALSE;
2074
2075 l_arg_ids dbms_sql.number_table;
2076 l_arg_names dbms_sql.varchar2_table;
2077 l_initialized_success_flags dbms_sql.varchar2_table;
2078 l_allow_override_source_flags dbms_sql.varchar2_table;
2079 l_binding_enabled_flags dbms_sql.varchar2_table;
2080 l_permissions dbms_sql.varchar2_table;
2081 l_write_policies dbms_sql.varchar2_table;
2082 l_datatypes dbms_sql.varchar2_table;
2083 l_valid_value_flags dbms_sql.varchar2_table;
2084 l_canonical_values long_varchar2_table;
2085
2086 k NUMBER;
2087 l_ignore BOOLEAN;
2088 l_arg_name VARCHAR2(60);
2089
2090 l_return_status VARCHAR2(6);
2091 l_return_msg VARCHAR2(2048);
2092 BEGIN
2093 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2094 x_return_status := FND_API.G_RET_STS_ERROR;
2095 x_return_msg := '';
2096
2097 --get all the args attached to the specified parent type/id
2098 --includes an order_by clause to prevent deadlock which would be caused if two workers try to init
2099 --and bind the args in different orders.
2100 SELECT arg_id, arg_name, initialized_success_flag, allow_override_source_flag, binding_enabled_flag, permissions, write_policy, datatype, valid_value_flag, canonical_value
2101 BULK COLLECT INTO l_arg_ids, l_arg_names, l_initialized_success_flags, l_allow_override_source_flags, l_binding_enabled_flags, l_permissions,
2102 l_write_policies, l_datatypes, l_valid_value_flags, l_canonical_values
2103 FROM fnd_oam_dscram_args_b
2104 WHERE parent_type = p_parent_type
2105 AND parent_id = p_parent_id
2106 AND enabled_flag = FND_API.G_TRUE
2107 ORDER BY arg_id ASC;
2108
2109 --allocate the array
2110 l_arg_list.EXTEND(l_arg_ids.COUNT);
2111
2112 --loop through the results
2113 k := l_arg_ids.FIRST;
2114 WHILE k IS NOT NULL LOOP
2115 --create a representative 'arg' structure
2116 l_arg := INTERNAL_CREATE_ARG(l_arg_ids(k),
2117 l_arg_names(k),
2118 l_initialized_success_flags(k),
2119 l_allow_override_source_flags(k),
2120 l_binding_enabled_flags(k),
2121 l_permissions(k),
2122 l_write_policies(k),
2123 l_datatypes(k),
2124 l_valid_value_flags(k),
2125 l_canonical_values(k));
2126
2127 --update our indicator variable if we found our condition
2128 IF IS_WRITABLE(l_arg) THEN
2129 l_has_writable := TRUE;
2130 END IF;
2131
2132 --add the arg to the arg list
2133 l_arg_list(k) := l_arg;
2134
2135 k := l_arg_ids.NEXT(k);
2136 END LOOP;
2137
2138 --debug
2139 IF FND_OAM_DSCRAM_UTILS_PKG.RUN_IS_DIAGNOSTIC THEN
2140 --print the arg list and allow failures here to indicate an overall failure so
2141 --a diagnostic test can detect it. We don't get the values for args because it screws up
2142 --operations too much and bind_args will get them all when the time is right anyway.
2143 print_arg_list(l_arg_list,
2144 l_return_status,
2145 l_return_msg);
2146 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2147 x_return_status := l_return_status;
2148 x_return_msg := l_return_msg;
2149 x_arg_list := l_empty_arg_list;
2153 ELSIF fnd_oam_debug.test(1, l_ctxt) THEN
2150 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2151 RETURN;
2152 END IF;
2154 --print the arg list
2155 print_arg_list(l_arg_list,
2156 l_return_status,
2157 l_return_msg);
2158 END IF;
2159
2160 --return the final list
2161 x_return_status := FND_API.G_RET_STS_SUCCESS;
2162 x_arg_list := l_arg_list;
2163 x_has_writable := l_has_writable;
2164 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2165 EXCEPTION
2166 WHEN OTHERS THEN
2167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2168 x_return_msg := 'Parent Type ('||p_parent_type||'), ID('||p_parent_id||'), while fetching arg list: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
2169 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2170 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2171 END;
2172
2173 -- Public
2174 PROCEDURE BIND_ARG_LIST_TO_CURSOR(p_arg_list IN OUT NOCOPY arg_list,
2175 px_arg_context IN OUT NOCOPY arg_context,
2176 p_cursor_id IN INTEGER,
2177 p_using_splitting IN BOOLEAN,
2178 p_rowid_lbound IN ROWID,
2179 p_rowid_ubound IN ROWID,
2180 x_return_status OUT NOCOPY VARCHAR2,
2181 x_return_msg OUT NOCOPY VARCHAR2)
2182 IS
2183 l_ctxt VARCHAR2(60) := PKG_NAME||'BIND_ARG_LIST_TO_CURSOR';
2184
2185 k NUMBER;
2186 l_bindvar_name VARCHAR2(120);
2187 l_varchar2 VARCHAR2(4000) := NULL;
2188 l_number NUMBER := NULL;
2189 l_date DATE := NULL;
2190 l_rowid ROWID := NULL;
2191
2192 l_return_status VARCHAR2(6);
2193 l_return_msg VARCHAR2(2048);
2194 BEGIN
2195 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2196 x_return_status := FND_API.G_RET_STS_ERROR;
2197 x_return_msg := '';
2198
2199 --traverse the arg list, everything that's readable MUST be bound, otherwise it wouldn't be in the list
2200 k := p_arg_list.FIRST;
2201 WHILE k IS NOT NULL LOOP
2202
2203 --only bind args with binding enabled, these can be input and/or output args args
2204 IF p_arg_list(k).binding_enabled THEN
2205
2206 --reset our placeholder vars so we don't carry over from the last arg
2207 l_varchar2 := NULL;
2208 l_number := NULL;
2209 l_date := NULL;
2210 l_rowid := NULL;
2211
2212 fnd_oam_debug.log(1, l_ctxt, 'Binding Arg: '||p_arg_list(k).arg_name);
2213
2214 --prep the name of the bind variable
2215 l_bindvar_name := ':'||p_arg_list(k).arg_name;
2216
2217 --first do the fetch if its readable
2218 IF IS_READABLE(p_arg_list(k)) THEN
2219 CASE p_arg_list(k).datatype
2220 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2 THEN
2221 --fetch
2222 GET_ARG_VALUE(p_arg_list(k),
2223 px_arg_context,
2224 p_using_splitting,
2225 p_rowid_lbound,
2226 p_rowid_ubound,
2227 NULL,
2228 l_varchar2,
2229 l_return_status,
2230 l_return_msg);
2231 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2232 fnd_oam_debug.log(1, l_ctxt, 'Value(VARCHAR2): '||l_varchar2);
2233 ELSE
2234 x_return_status := l_return_status;
2235 x_return_msg := l_return_msg;
2236 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2237 RETURN;
2238 END IF;
2239
2240 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER THEN
2241 --fetch
2242 GET_ARG_VALUE(p_arg_list(k),
2243 px_arg_context,
2244 p_using_splitting,
2245 p_rowid_lbound,
2246 p_rowid_ubound,
2247 NULL,
2248 l_number,
2249 l_return_status,
2250 l_return_msg);
2251 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2252 fnd_oam_debug.log(1, l_ctxt, 'Value(NUMBER): '||FND_NUMBER.NUMBER_TO_CANONICAL(l_number));
2253 ELSE
2254 --or fail
2255 x_return_status := l_return_status;
2256 x_return_msg := l_return_msg;
2257 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2258 RETURN;
2259 END IF;
2260
2261 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE THEN
2262 --fetch
2263 GET_ARG_VALUE(p_arg_list(k),
2264 px_arg_context,
2265 p_using_splitting,
2266 p_rowid_lbound,
2267 p_rowid_ubound,
2268 NULL,
2269 l_date,
2270 l_return_status,
2274 ELSE
2271 l_return_msg);
2272 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2273 fnd_oam_debug.log(1, l_ctxt, 'Value(DATE): '||FND_DATE.DATE_TO_CANONICAL(l_date));
2275 --or fail
2276 x_return_status := l_return_status;
2277 x_return_msg := l_return_msg;
2278 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2279 RETURN;
2280 END IF;
2281
2282 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID THEN
2283 --fetch
2284 GET_ARG_VALUE_ROWID(p_arg_list(k),
2285 px_arg_context,
2286 p_using_splitting,
2287 p_rowid_lbound,
2288 p_rowid_ubound,
2289 NULL,
2290 l_rowid,
2291 l_return_status,
2292 l_return_msg);
2293 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2294 fnd_oam_debug.log(1, l_ctxt, 'Value(ROWID): '||ROWIDTOCHAR(l_rowid));
2295 ELSE
2296 --or fail
2297 x_return_status := l_return_status;
2298 x_return_msg := l_return_msg;
2299 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2300 RETURN;
2301 END IF;
2302 ELSE
2303 x_return_msg := 'Arg ('||p_arg_list(k).arg_id||') has unknown datatype:'||p_arg_list(k).datatype;
2304 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2305 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2306 RETURN;
2307 END CASE;
2308 END IF;
2309
2310 --even if we didn't get a value, do a binding - required for output args.
2311 BEGIN
2312 CASE p_arg_list(k).datatype
2313 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_VARCHAR2 THEN
2314 DBMS_SQL.BIND_VARIABLE(p_cursor_id,
2315 l_bindvar_name,
2316 l_varchar2,
2317 4000);
2318
2319 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_NUMBER THEN
2320 DBMS_SQL.BIND_VARIABLE(p_cursor_id,
2321 l_bindvar_name,
2322 l_number);
2323
2324 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_DATE THEN
2325 DBMS_SQL.BIND_VARIABLE(p_cursor_id,
2326 l_bindvar_name,
2327 l_date);
2328
2329 WHEN FND_OAM_DSCRAM_UTILS_PKG.G_DATATYPE_ROWID THEN
2330 DBMS_SQL.BIND_VARIABLE_ROWID(p_cursor_id,
2331 l_bindvar_name,
2332 l_rowid);
2333 ELSE
2334 x_return_msg := 'Arg ('||p_arg_list(k).arg_id||') has unknown datatype:'||p_arg_list(k).datatype;
2335 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2336 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2337 RETURN;
2338 END CASE;
2339 EXCEPTION
2340 WHEN BIND_DOES_NOT_EXIST THEN
2341 --catch a common error and provide better feedback.
2342 fnd_oam_debug.log(1, l_ctxt, 'Arg ID('||p_arg_list(k).arg_id||'), Bindvar('||l_bindvar_name||') does not exist');
2343 x_return_msg := 'Arg ID('||p_arg_list(k).arg_id||'), Bind Variable ('||l_bindvar_name||') failure: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
2344 RETURN;
2345 WHEN OTHERS THEN
2346 RAISE;
2347 END;
2348
2349 END IF;
2350
2351 k := p_arg_list.NEXT(k);
2352 END LOOP;
2353
2354 --all bound, return success
2355 x_return_status := FND_API.G_RET_STS_SUCCESS;
2356 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2357 EXCEPTION
2358 WHEN OTHERS THEN
2359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2360 x_return_msg := 'Unhandled Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
2361 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2362 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2363 END;
2364
2365 -- Public
2366 PROCEDURE UPDATE_WRITABLE_ARG_VALUES(px_arg_list IN OUT NOCOPY arg_list,
2367 px_arg_context IN OUT NOCOPY arg_context,
2368 p_entity_finished IN BOOLEAN,
2369 p_using_splitting IN BOOLEAN,
2370 p_rowid_lbound IN ROWID,
2371 p_rowid_ubound IN ROWID,
2372 p_execution_cursor_id IN INTEGER,
2373 x_return_status OUT NOCOPY VARCHAR2,
2374 x_return_msg OUT NOCOPY VARCHAR2)
2375 IS
2376 l_ctxt VARCHAR2(60) := PKG_NAME||'UPDATE_WRITABLE_ARG_VALUES';
2377
2378 l_canonical_value VARCHAR2(4000);
2379 l_force_store_autonomously BOOLEAN := FALSE;
2380
2381 k NUMBER;
2382 l_ignore VARCHAR2(128);
2383
2384 l_return_status VARCHAR2(6);
2388 x_return_status := FND_API.G_RET_STS_ERROR;
2385 l_return_msg VARCHAR2(2048);
2386 BEGIN
2387 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2389 x_return_msg := '';
2390
2391 --if we're in a non-normal mode, store the values we get autonomously to keep them from
2392 --getting rolled back
2393 IF NOT FND_OAM_DSCRAM_UTILS_PKG.RUN_IS_NORMAL THEN
2394 l_force_store_autonomously := TRUE;
2395 END IF;
2396
2397 --loop through the arg list
2398 k := px_arg_list.FIRST;
2399 WHILE k IS NOT NULL LOOP
2400 --if the arg is writable, perform a get canonical value which does
2401 --an implicit store in an autonomous transaction. If the arg has already read
2402 --a value then get will not replace it
2403 IF IS_WRITABLE(px_arg_list(k)) THEN
2404 --include an additional condition to keep ONCE/PER_WORKER args from being
2405 --executed until the arg is finished and execute PER_RANGE/ALWAYS when not finished or
2406 --we're finished and not using splitting.
2407 IF ((p_entity_finished AND
2408 (NOT p_using_splitting OR
2409 (px_arg_list(k).write_policy IN (FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ONCE,
2410 FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_WORKER)))) OR
2411 (NOT p_entity_finished AND
2412 (px_arg_list(k).write_policy IN (FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_PER_RANGE,
2413 FND_OAM_DSCRAM_UTILS_PKG.G_WRITE_POLICY_ALWAYS)))) THEN
2414
2415 GET_CANONICAL_ARG_VALUE(px_arg_list(k),
2416 px_arg_context,
2417 p_using_splitting,
2418 p_rowid_lbound,
2419 p_rowid_ubound,
2420 p_execution_cursor_id,
2421 l_force_store_autonomously,
2422 TRUE,
2423 TRUE,
2424 l_ignore,
2425 l_canonical_value,
2426 l_return_status,
2427 l_return_msg);
2428 IF l_return_status <> FND_API.G_RET_STS_SUCCESS AND
2429 l_return_status <> FND_OAM_DSCRAM_UTILS_PKG.G_RET_STS_MISSING_BINDS THEN
2430
2431 --if an arg failed to get a value, return it as an error, stop fetching other args
2432 x_return_status := l_return_status;
2433 x_return_msg := l_return_msg;
2434 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2435 RETURN;
2436 ELSE
2437 fnd_oam_debug.log(1, l_ctxt, 'Value: '||l_canonical_value);
2438 END IF;
2439 END IF;
2440 END IF;
2441
2442 k := px_arg_list.NEXT(k);
2443 END LOOP;
2444
2445 --return the final list
2446 x_return_status := FND_API.G_RET_STS_SUCCESS;
2447 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2448 EXCEPTION
2449 WHEN OTHERS THEN
2450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2451 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
2452 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2453 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2454 END;
2455
2456 -- Public
2457 PROCEDURE UPDATE_CONTEXT_USING_ARG_LIST(px_arg_context IN OUT NOCOPY arg_context,
2458 p_arg_list IN arg_list,
2459 p_using_splitting IN BOOLEAN)
2460 IS
2461 l_ctxt VARCHAR2(60) := PKG_NAME||'UPDATE_CONTEXT_USING_ARG_LIST';
2462 k NUMBER;
2463 l_arg_name VARCHAR2(60);
2464 l_canonical_value VARCHAR2(4000);
2465 l_lock_handle VARCHAR2(128) := NULL;
2466 l_retval INTEGER;
2467
2468 l_return_status VARCHAR2(6);
2469 l_return_msg VARCHAR2(2048);
2470 BEGIN
2471 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2472
2473 --loop over each item in the arg list since its easier to lookup a match in the context
2474 k := p_arg_list.FIRST;
2475 WHILE k IS NOT NULL LOOP
2476 IF IS_READABLE(p_arg_list(k)) AND
2477 p_arg_list(k).valid_value_flag = FND_API.G_TRUE AND
2478 px_arg_context.EXISTS(p_arg_list(k).arg_name) THEN
2479
2480 l_arg_name := p_arg_list(k).arg_name;
2481 -- do a cursory check of compatiblity before calling the context arg's get to check the db for its latest value
2482 IF (px_arg_context(l_arg_name).allow_override_source AND
2483 px_arg_context(l_arg_name).valid_value_flag IS NULL AND
2484 IS_WRITABLE(px_arg_context(l_arg_name)) AND
2485 p_arg_list(k).datatype = px_arg_context(l_arg_name).datatype) THEN
2486
2487 --first fetch the latest value for the context arg without sourcing it and keeping the lock if we get one
2488 GET_CANONICAL_CTXT_ARG_VALUE(px_arg_context(l_arg_name),
2489 p_using_splitting,
2490 p_arg_list(k).rowid_lbound,
2491 p_arg_list(k).rowid_ubound,
2492 NULL,
2493 FALSE,
2494 FALSE,
2495 FALSE,
2496 l_lock_handle,
2497 l_canonical_value,
2501 --the get should succeed without finding a valid value for us to default in the arg list's value
2498 l_return_status,
2499 l_return_msg);
2500 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2502 IF px_arg_context(l_arg_name).valid_value_flag IS NULL THEN
2503 --this means we can default from the arg list
2504 BEGIN
2505 SET_STATE_ARG_VALUE(px_arg_context(l_arg_name),
2506 p_arg_list(k).canonical_value,
2507 p_arg_list(k).rowid_lbound,
2508 p_arg_list(k).rowid_ubound);
2509 --and we should also store the context arg's new value
2510 STORE_ARG_VALUE_AUTONOMOUSLY(px_arg_context(l_arg_name),
2511 l_return_status,
2512 l_return_msg);
2513 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2514 fnd_oam_debug.log(1, l_ctxt, 'Set the context arg to value: '||p_arg_list(k).canonical_value);
2515 ELSE
2516 --if we didn't store correctly, reset the context arg to unknown
2517 px_arg_context(l_arg_name).valid_value_flag := NULL;
2518 END IF;
2519 EXCEPTION
2520 WHEN OTHERS THEN
2521 --if the set or store fail, reset the context arg's value to unknown
2522 px_arg_context(l_arg_name).valid_value_flag := NULL;
2523 END;
2524 ELSE
2525 fnd_oam_debug.log(1, l_ctxt, 'Context already has value: '||l_canonical_value);
2526 END IF;
2527
2528 --release the lock if we have one
2529 IF l_lock_handle IS NOT NULL THEN
2530 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2531 IF l_retval <> 0 THEN
2532 fnd_oam_debug.log(6, l_ctxt, 'Failed to release arg lock: '||l_retval);
2533 END IF;
2534 END IF;
2535 END IF;
2536 END IF;
2537 END IF;
2538 k := p_arg_list.NEXT(k);
2539 END LOOP;
2540
2541 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2542 EXCEPTION
2543 WHEN OTHERS THEN
2544 --make sure there isn't a lock hanging around
2545 IF l_lock_handle IS NOT NULL THEN
2546 l_retval := DBMS_LOCK.RELEASE(l_lock_handle);
2547 IF l_retval <> 0 THEN
2548 fnd_oam_debug.log(6, l_ctxt, 'Failed to release arg lock: '||l_retval);
2549 END IF;
2550 END IF;
2551
2552 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
2553 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2554 RETURN;
2555 END;
2556
2557 -- Public
2558 PROCEDURE DESTROY_ARG_LIST(px_arg_list IN OUT NOCOPY arg_list,
2559 x_return_status OUT NOCOPY VARCHAR2,
2560 x_return_msg OUT NOCOPY VARCHAR2)
2561 IS
2562 l_ctxt VARCHAR2(60) := PKG_NAME||'DESTROY_ARG_LIST';
2563
2564 k NUMBER;
2565
2566 l_return_status VARCHAR2(6);
2567 l_return_msg VARCHAR2(2048);
2568 BEGIN
2569 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2570 x_return_status := FND_API.G_RET_STS_ERROR;
2571 x_return_msg := '';
2572
2573 k := px_arg_list.FIRST;
2574 WHILE k IS NOT NULL LOOP
2575
2576 --close up the cursor if it exists
2577 IF px_arg_list(k).source_cursor_id IS NOT NULL AND
2578 DBMS_SQL.IS_OPEN(px_arg_list(k).source_cursor_id) THEN
2579 DBMS_SQL.CLOSE_CURSOR(px_arg_list(k).source_cursor_id);
2580 END IF;
2581
2582 k := px_arg_list.NEXT(k);
2583 END LOOP;
2584
2585 --delete the arg list
2586 px_arg_list.DELETE;
2587
2588 x_return_status := FND_API.G_RET_STS_SUCCESS;
2589 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2593 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
2594 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2595 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2596 END;
2597
2598 -- Public
2599 PROCEDURE DESTROY_ARG_CONTEXT(px_arg_context IN OUT NOCOPY arg_context,
2600 x_return_status OUT NOCOPY VARCHAR2,
2601 x_return_msg OUT NOCOPY VARCHAR2)
2602 IS
2603 l_ctxt VARCHAR2(60) := PKG_NAME||'DESTROY_ARG_CONTEXT';
2604
2605 l_s VARCHAR2(60);
2606 l_ignore VARCHAR2(128);
2607 l_canonical_value VARCHAR2(4000);
2608
2609 l_return_status VARCHAR2(6);
2610 l_return_msg VARCHAR2(2048);
2611 BEGIN
2612 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
2613 x_return_status := FND_API.G_RET_STS_ERROR;
2614 x_return_msg := '';
2615
2616 --go through the args
2617 l_s := px_arg_context.FIRST;
2618 WHILE l_s IS NOT NULL LOOP
2619 --if it's writable then try to let it store a value before we remove the arg
2620 IF IS_WRITABLE(px_arg_context(l_s)) THEN
2621 GET_CANONICAL_CTXT_ARG_VALUE(px_arg_context(l_s),
2622 FALSE,
2626 TRUE,
2623 NULL,
2624 NULL,
2625 NULL,
2627 TRUE,
2628 TRUE,
2629 l_ignore,
2630 l_canonical_value,
2631 l_return_status,
2632 l_return_msg);
2633 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2634 fnd_oam_debug.log(1, l_ctxt, 'Arg('||l_s||'), stored final context value: '||l_canonical_value);
2635 END IF;
2636 END IF;
2637
2638 --close up the arg's cursor if it exists
2639 IF px_arg_context(l_s).source_cursor_id IS NOT NULL AND
2640 DBMS_SQL.IS_OPEN(px_arg_context(l_s).source_cursor_id) THEN
2641 DBMS_SQL.CLOSE_CURSOR(px_arg_context(l_s).source_cursor_id);
2642 END IF;
2643
2644 l_s := px_arg_context.NEXT(l_s);
2645 END LOOP;
2646
2647 --delete the arg context
2648 px_arg_context.DELETE;
2649
2650 x_return_status := FND_API.G_RET_STS_SUCCESS;
2651 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2652 EXCEPTION
2653 WHEN OTHERS THEN
2654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2655 x_return_msg := 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))';
2656 fnd_oam_debug.log(6, l_ctxt, x_return_msg);
2657 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
2658 END;
2659
2660 -- Public
2661 -- Copied from FND_OAM_DS_PSETS_PKG, seems to mimic standard syntax of calls
2662 -- made from FND_TOP/sql/FNDNLINS.sql.
2663 PROCEDURE ADD_LANGUAGE
2664 IS
2665 BEGIN
2666
2667 delete from FND_OAM_DSCRAM_ARGS_TL T
2668 where not exists
2669 (select NULL
2670 from FND_OAM_DSCRAM_ARGS_B B
2671 where B.ARG_ID = T.ARG_ID
2672 );
2673
2674 update FND_OAM_DSCRAM_ARGS_TL T set (
2675 DISPLAY_NAME,
2676 DESCRIPTION
2677 ) = (select
2678 B.DISPLAY_NAME,
2679 B.DESCRIPTION
2680 from FND_OAM_DSCRAM_ARGS_TL B
2681 where B.ARG_ID = T.ARG_ID
2682 and B.LANGUAGE = T.SOURCE_LANG)
2683 where (
2684 T.ARG_ID,
2685 T.LANGUAGE
2686 ) in (select
2687 SUBT.ARG_ID,
2688 SUBT.LANGUAGE
2689 from FND_OAM_DSCRAM_ARGS_TL SUBB, FND_OAM_DSCRAM_ARGS_TL SUBT
2690 where SUBB.ARG_ID = SUBT.ARG_ID
2691 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2692 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
2693 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
2694 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
2695 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
2696 ));
2697
2698 insert into FND_OAM_DSCRAM_ARGS_TL (
2699 ARG_ID,
2700 DISPLAY_NAME,
2701 DESCRIPTION,
2702 CREATED_BY,
2703 CREATION_DATE,
2704 LAST_UPDATED_BY,
2705 LAST_UPDATE_DATE,
2706 LAST_UPDATE_LOGIN,
2707 LANGUAGE,
2708 SOURCE_LANG
2709 ) select /*+ ORDERED */
2710 B.ARG_ID,
2711 B.DISPLAY_NAME,
2712 B.DESCRIPTION,
2713 B.CREATED_BY,
2714 B.CREATION_DATE,
2715 B.LAST_UPDATED_BY,
2716 B.LAST_UPDATE_DATE,
2717 B.LAST_UPDATE_LOGIN,
2718 L.LANGUAGE_CODE,
2719 B.SOURCE_LANG
2720 from FND_OAM_DSCRAM_ARGS_TL B, FND_LANGUAGES L
2721 where L.INSTALLED_FLAG in ('I', 'B')
2722 and B.LANGUAGE = userenv('LANG')
2723 and not exists
2724 (select NULL
2725 from FND_OAM_DSCRAM_ARGS_TL T
2726 where T.ARG_ID = B.ARG_ID
2727 and T.LANGUAGE = L.LANGUAGE_CODE);
2728
2729 END ADD_LANGUAGE;
2730
2731 END FND_OAM_DSCRAM_ARGS_PKG;
[Home] [Help]