1 PACKAGE FND_OAM_DSCRAM_UTILS_PKG as
2 /* $Header: AFOAMDSUTILS.pls 120.6 2006/05/16 01:31:35 ilawler noship $ */
3
4 ------------
5 -- Constants
6 ------------
7
8 --prefix used for all global objects
9 G_DSCRAM_GLOBAL_PREFIX CONSTANT VARCHAR2(30) := 'FND_OAM_DSCRAM__';
10
11 -- Object types used in _STATS as the source_object_type
12 G_MODE_NORMAL CONSTANT VARCHAR2(30) := 'NORMAL'; --real run, do work
13 G_MODE_TEST CONSTANT VARCHAR2(30) := 'TEST'; --real run, just test, only ok if no side effects
14 G_MODE_TEST_NO_EXEC CONSTANT VARCHAR2(30) := 'TEST_NO_EXEC'; --real run, test prep but don't do physical DMLs/PLSQLs
15 G_MODE_DIAGNOSTIC CONSTANT VARCHAR2(30) := 'DIAGNOSTIC'; --diagnostic run
16
17 -- Object types used in _STATS as the source_object_type
18 G_TYPE_GLOBAL CONSTANT VARCHAR2(30) := 'GLOBAL';
19 G_TYPE_RUN CONSTANT VARCHAR2(30) := 'RUN';
20 G_TYPE_BUNDLE CONSTANT VARCHAR2(30) := 'BUNDLE';
21 G_TYPE_TASK CONSTANT VARCHAR2(30) := 'TASK';
22 G_TYPE_UNIT CONSTANT VARCHAR2(30) := 'UNIT';
23 G_TYPE_DML CONSTANT VARCHAR2(30) := 'DML';
24 G_TYPE_PLSQL CONSTANT VARCHAR2(30) := 'PLSQL';
25 G_TYPE_WORKER CONSTANT VARCHAR2(30) := 'WORKER'; --used by arg_values
26 G_TYPE_RANGE CONSTANT VARCHAR2(30) := 'RANGE'; --used by arg_values
27
28 -- Unit Types
29 G_UNIT_TYPE_CONC_GROUP CONSTANT VARCHAR2(30) := 'CONC_GROUP';
30 G_UNIT_TYPE_DML_SET CONSTANT VARCHAR2(30) := 'DML_SET';
31 G_UNIT_TYPE_PLSQL_SET CONSTANT VARCHAR2(30) := 'PLSQL_SET';
32
33 -- Entity Statuses: for run, bundle, task, task unit
34 G_STATUS_UNPROCESSED CONSTANT VARCHAR2(30) := 'UNPROCESSED';
35 G_STATUS_PROCESSING CONSTANT VARCHAR2(30) := 'PROCESSING';
36 G_STATUS_FINISHING CONSTANT VARCHAR2(30) := 'FINISHING';
37 G_STATUS_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
38 G_STATUS_STOPPING CONSTANT VARCHAR2(30) := 'STOPPING'; --Run and Bundle only before being set to STOPPED
39 G_STATUS_STOPPED CONSTANT VARCHAR2(30) := 'STOPPED';
40 G_STATUS_SKIPPED CONSTANT VARCHAR2(30) := 'SKIPPED';
41 G_STATUS_RESTARTABLE CONSTANT VARCHAR2(30) := 'RESTARTABLE';
42 G_STATUS_ERROR_FATAL CONSTANT VARCHAR2(30) := 'ERROR_FATAL';
43 G_STATUS_ERROR_UNKNOWN CONSTANT VARCHAR2(30) := 'ERROR_UNKNOWN';
44 G_STATUS_NO_STATUS CONSTANT VARCHAR2(30) := 'NO_STATUS'; --Child units, we need a dummy value that doesn't get updated
45
46 -- Custom Return Statuses: used by execute, validate, fetch APIs of run/bundle/task/unit to augment standard FND return statuses
47 G_RET_STS_EMPTY CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'E'; --returned by calls to fetch: task, unit
48 G_RET_STS_FULL CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'F'; --returned by calls to fetch: unit||assign: bundle, unit
49 G_RET_STS_STOPPED CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'S'; --returned by calls to fetch: task||execute: run, bundle
50 G_RET_STS_SKIPPED CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'K'; --returned by calls to validate_start: task
51 G_RET_STS_PROCESSED CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'P'; --returned by calls to execute: run, bundle
52 G_RET_STS_ERROR_FATAL CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'Z'; --returned by calls to execute:
53 G_RET_STS_ERROR_UNKNOWN CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'U'; --returned by calls to execute:
54 G_RET_STS_MISSING_BINDS CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'B'; --returned by calls to arg.get_value and its variants
55 G_RET_STS_MISSING_STATE CONSTANT VARCHAR2(6) := FND_API.G_RET_STS_ERROR||'C'; --returned by calls to arg.get_value and its variants
56
57 -- Argument Source Types
58 G_SOURCE_CONSTANT CONSTANT VARCHAR2(30) := 'CONSTANT'; --source_text is a flat value
59 G_SOURCE_STATE CONSTANT VARCHAR2(30) := 'STATE'; --source_text is the name of a state key
60 G_SOURCE_EXECUTION_CURSOR CONSTANT VARCHAR2(30) := 'EXECUTION_CURSOR'; --source_text is ignored, fetch value using bind var with arg_name
61 --from the just executed cursor.
62 G_SOURCE_SQL CONSTANT VARCHAR2(30) := 'SQL'; --source_text is a sql requiring no binds or clauses
63 G_SOURCE_SQL_RESTRICTABLE CONSTANT VARCHAR2(30) := 'SQL_RESTRICTABLE'; --source_text is a sql that allows the ROWID_CLAUSE to be appended
64 --when splitting
65
66 -- Argument Permissions
67 G_PERMISSION_READ CONSTANT VARCHAR2(30) := 'READ';
68 G_PERMISSION_READ_WRITE CONSTANT VARCHAR2(30) := 'READ_WRITE';
69 G_PERMISSION_WRITE CONSTANT VARCHAR2(30) := 'WRITE';
70
71 -- Argument Write Policies
72 G_WRITE_POLICY_ONCE CONSTANT VARCHAR2(30) := 'ONCE';
73 G_WRITE_POLICY_PER_WORKER CONSTANT VARCHAR2(30) := 'PER_WORKER';
74 G_WRITE_POLICY_PER_RANGE CONSTANT VARCHAR2(30) := 'PER_RANGE';
75 G_WRITE_POLICY_ALWAYS CONSTANT VARCHAR2(30) := 'ALWAYS';
76
77 -- Argument Supported Datatypes
78 G_DATATYPE_VARCHAR2 CONSTANT VARCHAR2(30) := 'VARCHAR2';
79 G_DATATYPE_NUMBER CONSTANT VARCHAR2(30) := 'NUMBER';
80 G_DATATYPE_DATE CONSTANT VARCHAR2(30) := 'DATE';
81 --G_DATATYPE_BOOLEAN CONSTANT VARCHAR2(30) := 'BOOLEAN'; --not a supported SQL type, could store but couldn't query/bind
82 G_DATATYPE_ROWID CONSTANT VARCHAR2(30) := 'ROWID';
83
84 -- Constants used for binding rowid ranges
85 G_ARG_INTERNAL_PREFIX CONSTANT VARCHAR2(30) := 'DS__';
86 G_ARG_ROWID_LBOUND_NAME CONSTANT VARCHAR2(30) := G_ARG_INTERNAL_PREFIX||'ROWID_LBOUND';
87 G_ARG_ROWID_UBOUND_NAME CONSTANT VARCHAR2(30) := G_ARG_INTERNAL_PREFIX||'ROWID_UBOUND';
88
89 -- Prefix and Suffix prepended and appended to plsql procedures to make them compliant with dbms_sql
90 G_PLSQL_PREFIX CONSTANT VARCHAR2(60) := 'BEGIN ';
91 G_PLSQL_SUFFIX CONSTANT VARCHAR2(60) := 'END;';
92
93 -- Constants representing the various, supported argument state keys
94 G_KEY_RUN_ID CONSTANT VARCHAR2(30) := 'RUN_ID';
95 G_KEY_RUN_MODE CONSTANT VARCHAR2(30) := 'RUN_MODE';
96 G_KEY_BUNDLE_ID CONSTANT VARCHAR2(30) := 'BUNDLE_ID';
97 G_KEY_BUNDLE_WORKERS_ALLOWED CONSTANT VARCHAR2(30) := 'BUNDLE_WORKERS_ALLOWED';
98 G_KEY_BUNDLE_BATCH_SIZE CONSTANT VARCHAR2(30) := 'BUNDLE_BATCH_SIZE';
99 G_KEY_WORKER_ID CONSTANT VARCHAR2(30) := 'WORKER_ID';
100 G_KEY_TASK_ID CONSTANT VARCHAR2(30) := 'TASK_ID';
101 G_KEY_UNIT_ID CONSTANT VARCHAR2(30) := 'UNIT_ID';
102 G_KEY_USING_SPLITTING CONSTANT VARCHAR2(30) := 'USING_SPLITTING';
103 G_KEY_ROWID_LBOUND CONSTANT VARCHAR2(30) := 'ROWID_LBOUND';
104 G_KEY_ROWID_UBOUND CONSTANT VARCHAR2(30) := 'ROWID_UBOUND';
105 G_KEY_UNIT_OBJECT_OWNER CONSTANT VARCHAR2(30) := 'UNIT_OBJECT_OWNER';
106 G_KEY_UNIT_OBJECT_NAME CONSTANT VARCHAR2(30) := 'UNIT_OBJECT_NAME';
107 G_KEY_UNIT_WORKERS_ALLOWED CONSTANT VARCHAR2(30) := 'UNIT_WORKERS_ALLOWED';
108 G_KEY_UNIT_BATCH_SIZE CONSTANT VARCHAR2(30) := 'UNIT_BATCH_SIZE';
109 G_KEY_DML_ID CONSTANT VARCHAR2(30) := 'DML_ID';
110 G_KEY_PLSQL_ID CONSTANT VARCHAR2(30) := 'PLSQL_ID';
111 G_KEY_ARGUMENT_ID CONSTANT VARCHAR2(30) := 'ARGUMENT_ID';
112 G_KEY_WORKERS_ALLOWED CONSTANT VARCHAR2(30) := 'WORKERS_ALLOWED'; --unit's workers allowed or bundle's if null
113 G_KEY_BATCH_SIZE CONSTANT VARCHAR2(30) := 'BATCH_SIZE'; --unit's batch size or bundle's if null
114
115 ---------------------------------
116 -- Public Procedures/Functions --
117 ---------------------------------
118
119 -- When a VALIDATE_START_EXECUTION procedure sees a non-normal status, it uses this to reduce it to
120 -- a return status code for the procedure.
121 FUNCTION CONV_VALIDATE_START_STS_TO_RET(p_status IN VARCHAR2)
122 RETURN VARCHAR2;
123
124 -- When a VALIDATE_CONTINUED_EXECUTION procedure sees a non-normal status, it uses this to reduce it to
125 -- a return status code for the procedure.
126 FUNCTION CONV_VALIDATE_CONT_STS_TO_RET(p_status IN VARCHAR2)
127 RETURN VARCHAR2;
128
129 -- Used to convert one of the many possible return status codes into a full custom status for storage in the DB.
130 FUNCTION CONV_RET_STS_TO_COMPL_STATUS(p_ret_sts IN VARCHAR2)
131 RETURN VARCHAR2;
132
133 -- Check if the entity is in a state which can be executed
134 FUNCTION STATUS_IS_EXECUTABLE(p_status IN VARCHAR2)
135 RETURN BOOLEAN;
136
137 -- See if the unit is in progress, this includes the finishing status.
138 FUNCTION STATUS_IS_PROCESSING(p_status IN VARCHAR2)
139 RETURN BOOLEAN;
140
141 -- Tell us when the status is an end state, that is a state that can't be changed by another worker.
142 FUNCTION STATUS_IS_FINAL(p_status IN VARCHAR2)
143 RETURN BOOLEAN;
144
145 -- See if the entity's status is in one of the few possible error statuses
146 FUNCTION STATUS_IS_ERROR(p_status IN VARCHAR2)
147 RETURN BOOLEAN;
148
149 -- See if the return_status code is an error status
150 FUNCTION RET_STS_IS_ERROR(p_ret_sts IN VARCHAR2)
151 RETURN BOOLEAN;
152
153 -- When a worker is completing its work on an entity, certain
154 -- transitions in status should be supressed like Stopping->Processed. This procedure
155 -- brokers these transactions by converting proposed statuses/return status codes into
156 -- final statuses/return status codes to keep the state transitions valid for our
157 -- scheduling(run/bundle/task/unit) entities.
158 PROCEDURE TRANSLATE_COMPLETED_STATUS(p_current_status IN VARCHAR2,
159 p_workers_assigned IN NUMBER,
160 p_proposed_status IN VARCHAR2,
161 p_proposed_ret_sts IN VARCHAR2,
162 x_final_status OUT NOCOPY VARCHAR2,
163 x_final_ret_sts OUT NOCOPY VARCHAR2);
164
165 -- To detect asynchronous stops and fatally errored work, we make use of periodic status
166 -- checks. This function determines if its time for one of those checks.
167 FUNCTION VALIDATION_DUE(p_last_validated IN DATE)
168 RETURN BOOLEAN;
169
170 -- Similar to fnd_api.to_boolean but without the raise and fnd_msg call. Null becomes false.
171 FUNCTION FLAG_TO_BOOLEAN(p_flag IN VARCHAR2)
172 RETURN BOOLEAN;
173
174 -- Inverse of FLAG_TO_BOOLEAN
175 FUNCTION BOOLEAN_TO_FLAG(p_bool IN BOOLEAN)
176 RETURN VARCHAR2;
177
178 -- Currently used by UNITS_PKG to stop a parent object, task/bundle/run when an error occurs
179 -- in a unit with a fatality_level set. This method is here to facilitate propogation
180 -- if other entities support the fatality level concept.
181 PROCEDURE PROPOGATE_FATALITY_LEVEL(p_fatality_level IN VARCHAR2);
182
183 -- Used as part of the primary key when initializing the AD infrastructure. The key is
184 -- composed of a prefix, the run id and the unit id.
185 FUNCTION MAKE_AD_SCRIPT_KEY(p_run_id IN NUMBER,
186 p_unit_id IN NUMBER)
187 RETURN VARCHAR2;
188
189 --Wrapper for above, assumes run_id from run_pkg state
190 FUNCTION MAKE_AD_SCRIPT_KEY(p_unit_id IN NUMBER)
191 RETURN VARCHAR2;
192
193 -- Used to lock a run independent of whether it exists yet or not. Allocates a handle and uses
194 -- dbms_lock to control contention.
195 FUNCTION LOCK_RUN(p_run_id IN NUMBER,
196 x_lock_handle OUT NOCOPY VARCHAR2)
197 RETURN BOOLEAN;
198
199 -- Used to lock an argument independent of the arg's write location and the transaction's status.
200 -- Also uses DBMS_LOCK.
201 FUNCTION LOCK_ARG(p_arg_id IN NUMBER,
202 x_lock_handle OUT NOCOPY VARCHAR2)
203 RETURN BOOLEAN;
204
205 -- Used to delete a run and all of its sub-entites down to and including AD entities. Should
206 -- be consumed with care.
207 FUNCTION DELETE_RUN(p_run_id IN NUMBER)
208 RETURN BOOLEAN;
209
210 --used as a common predicate for determining whether we should proceed normally and commit results
211 FUNCTION RUN_IS_NORMAL
212 RETURN BOOLEAN;
213
214 --used as a common predicate for determining whether diagnostic actions should
215 --be taken. In diagnostic modes, some warnings become errors and there are more prints.
216 FUNCTION RUN_IS_DIAGNOSTIC
217 RETURN BOOLEAN;
218
219 -- checks if an arg's source type is sql-based so it can set up the cursor
220 FUNCTION SOURCE_TYPE_USES_SQL(p_source_type IN VARCHAR2)
221 RETURN BOOLEAN;
222
223 -- Used by the DML prep and ARG init to create the final statement which may or may not
224 -- append the rowid_clause depending on whether they're using AD splitting.
225 PROCEDURE MAKE_FINAL_SQL_STMT(px_arg_context IN FND_OAM_DSCRAM_ARGS_PKG.arg_context,
226 p_stmt IN VARCHAR2,
227 p_where_clause IN VARCHAR2,
228 p_use_splitting IN BOOLEAN,
229 x_final_stmt OUT NOCOPY VARCHAR2,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_return_msg OUT NOCOPY VARCHAR2);
232
233 -- Used by the BUNDLES_PKG to block when there are no available tasks until a unit has been
234 -- finished by another worker signaling progress
235 PROCEDURE WAIT_FOR_PROGRESS_ALERT;
236
237 -- Used by the UNITS_PKG to signal a progress alert whenever a unit finishes.
238 PROCEDURE SIGNAL_PROGRESS_ALERT;
239
240 -- Alerts need a commit to be sent, this is a convenience method to force send a signal.
241 PROCEDURE SIGNAL_AUT_PROGRESS_ALERT;
242
243 -- This procedure prepares to retry a run by updating its status to RESTARTABLE. It can also optionally
244 -- prepare its children. Throws exceptions on error.
245 -- Invariants:
246 -- None
247 -- Parameters:
248 -- p_run_id: The run ID to prepare
249 -- p_recurse_children: FND_API.G_TRUE/G_FALSE indicating whether to recurse and prepare child entities.
250 PROCEDURE PREPARE_RUN_FOR_RETRY(p_run_id IN NUMBER,
251 p_recurse_children IN VARCHAR2 DEFAULT NULL);
252
253 END FND_OAM_DSCRAM_UTILS_PKG;
[Home] [Help]