1 PACKAGE BODY QLTTRAMB as
2 /* $Header: qlttramb.plb 115.13 2002/11/28 00:28:40 jezheng ship $ */
3 -- 1/22/96 - created
4 -- Paul Mishkin
5
6 --
7 -- Standard who columns.
8 --
9 who_user_id constant number := fnd_global.conc_login_id;
10 who_request_id constant number := fnd_global.conc_request_id;
11 who_program_id constant number := fnd_global.conc_program_id;
12 who_program_application_id constant number := fnd_global.prog_appl_id;
13 who_creation_date constant date := qltdate.get_sysdate;
14 who_created_by number;
15 who_last_update_login number;
16
17 --
18 -- Launch a workflow for Self-service notification purpose.
19 -- Return the item_key.
20 --
21 FUNCTION launch_workflow(item_type IN Varchar2, workers number)
22 RETURN number IS
23 item_key number;
24
25 CURSOR c IS
26 SELECT qa_ss_import_workflow_s.nextval
27 FROM dual;
28 BEGIN
29 OPEN c;
30 FETCH c INTO item_key;
31 CLOSE c;
32
33 wf_engine.CreateProcess(
34 itemtype => item_type,
35 itemkey => item_key,
36 process => 'IMPORT_PROCESS');
37
38 wf_engine.SetItemAttrNumber(
39 itemtype => item_type,
40 itemkey => item_key,
41 aname => 'NUM_WORKERS',
42 avalue => to_char(workers));
43
44 wf_engine.SetItemAttrNumber(
45 itemtype => item_type,
46 itemkey => item_key,
47 aname => 'REQUEST_ID',
48 avalue => to_char(who_request_id));
49
50 wf_engine.StartProcess(
51 itemtype => item_type,
52 itemkey => item_key);
53
54 RETURN item_key;
55 EXCEPTION WHEN OTHERS THEN
56 --
57 --If there are any exceptions that are raised when
58 --workflow needs to be started, then we should return a null
59 --itemkey (in which case the workflow will not be started).
60 --Look at bug: 1003883
61 --
62 RETURN null;
63
64 END launch_workflow;
65
66
67 -- scans the collection import table for new rows and launches transaction
68 -- workers to validate the rows and transfer them to qa_results. there are
69 -- two rules for how it assigns rows to workers: (1) all rows assigned to a
70 -- given worker will have the same values for validation_flag and plan_name,
71 -- and (2) a maximum of worker_rows rows can be assigned to a given worker.
72
73 -- Added argument4 for Gather Statistics parameter.
74 -- bug2141009. kabalakr 4 feb 2002.
75
76 PROCEDURE TRANSACTION_MANAGER (WORKER_ROWS NUMBER, ARGUMENT2 VARCHAR2,
77 ARGUMENT3 VARCHAR2, ARGUMENT4 VARCHAR2) IS
78
79 X_USER_ID NUMBER;
80 X_REQUEST_ID NUMBER;
81 X_PROGRAM_APPLICATION_ID NUMBER;
82 X_PROGRAM_ID NUMBER;
83 X_LAST_UPDATE_LOGIN NUMBER;
84
85 ERRCODE NUMBER;
86 ERRSTAT BOOLEAN;
87 I NUMBER;
88 NUM_ROWS NUMBER;
89 ROW_COUNT NUMBER;
90 X_GROUP_ID NUMBER;
91 X_PLAN_NAME VARCHAR2(30);
92 X_DEBUG VARCHAR2(80);
93 TYPE_OF_TXN NUMBER;
94
95 TYPE CHAR30_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
96 TYPE CHAR100_TABLE IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
97
98 ROWID_TABLE CHAR100_TABLE;
99 PLAN_NAME_TABLE CHAR30_TABLE;
100
101 TYPE worker_rec IS RECORD (group_id number, val_flag number);
102 TYPE worker_table IS TABLE OF worker_rec INDEX BY binary_integer;
103 workers worker_table;
104 workers_n number := 0;
105 self_service boolean := false;
106 X_Profile_Val Number := 1;
107 workflow_type Varchar2(8);
108 workflow_key number := null;
109
110 BEGIN
111
112 --
113 -- For CBO, need to calculate the size of the QRI table.
114 -- bso Tue Dec 7 12:53:14 PST 1999
115 --
116
117 -- Check if the parameter 'Gather Statistics' is Yes or not.
118 -- for bug 2141009. kabalakr 4 feb 2002.
119
120 IF (upper(ARGUMENT4) = 'YES') THEN
121 fnd_stats.gather_table_stats('QA','QA_RESULTS_INTERFACE');
122 END IF;
123
124 TYPE_OF_TXN := TO_NUMBER(ARGUMENT2);
125
126 -- get who column values
127
128 X_USER_ID := who_user_id;
129 X_REQUEST_ID := who_request_id;
130 X_PROGRAM_APPLICATION_ID := who_program_application_id;
131 X_PROGRAM_ID := who_program_id;
132 X_LAST_UPDATE_LOGIN := who_last_update_login;
133
134 -- first assign workers for rows where validate flag is true. then
135 -- assign workers for rows where validate flag is false.
136
137 FOR X_VAL_FLAG IN 1..2 LOOP
138 I := 0;
139 IF (X_VAL_FLAG = 1) THEN
140
141 -- count the number of rows where validate flag is true or, more
142 -- specifically, where validate flag is not false
143
144 FOR REC IN (SELECT ROWID, PLAN_NAME, SOURCE_CODE
145 FROM QA_RESULTS_INTERFACE
146 WHERE PROCESS_STATUS = 1
147 AND GROUP_ID IS NULL
148 AND ((VALIDATE_FLAG <> 2) OR (VALIDATE_FLAG IS NULL))
149 AND NVL(INSERT_TYPE,1) = TYPE_OF_TXN
150 ORDER BY PLAN_NAME
151 FOR UPDATE OF GROUP_ID) LOOP
152 I := I + 1;
153 ROWID_TABLE(I) := REC.ROWID;
154 PLAN_NAME_TABLE(I) := REC.PLAN_NAME;
155
156 --
157 -- Workflow notification enhancement. Set a flag to indicate
158 -- if any of these rows are from self-service. Workflow is
159 -- launched if and only if some records are self-service.
160 -- bso Thu Jul 22 13:35:29 PDT 1999
161 --
162 IF rec.source_code LIKE 'QA_SS%' THEN
163 self_service := true;
164 END IF;
165 END LOOP;
166 ELSE
167
168 -- count the number of rows where validate flag is false
169
170 FOR REC IN (SELECT ROWID, PLAN_NAME
171 FROM QA_RESULTS_INTERFACE
172 WHERE PROCESS_STATUS = 1
173 AND GROUP_ID IS NULL
174 AND VALIDATE_FLAG = 2
175 AND NVL(INSERT_TYPE,1) = TYPE_OF_TXN
176 ORDER BY PLAN_NAME
177 FOR UPDATE OF GROUP_ID) LOOP
178 I := I + 1;
179 ROWID_TABLE(I) := REC.ROWID;
180 PLAN_NAME_TABLE(I) := REC.PLAN_NAME;
181 END LOOP;
182 END IF;
183
184 NUM_ROWS := I;
185
186 -- get the mrp debug profile, if we'll be using it
187
188 IF ((NUM_ROWS > 0) AND (X_DEBUG IS NULL)) THEN
189 X_DEBUG := FND_PROFILE.VALUE('MRP_DEBUG');
190 END IF;
191
192 X_PLAN_NAME := NULL;
193 X_GROUP_ID := NULL;
194 ROW_COUNT := 0;
195
196 -- loop through the rows, launching a worker whenever the plan name
197 -- changes or the number of worker rows is exceeded
198
199 FOR I IN 1..NUM_ROWS LOOP
200 ROW_COUNT := ROW_COUNT + 1;
201
202 IF (X_PLAN_NAME IS NULL) THEN
203 X_PLAN_NAME := PLAN_NAME_TABLE(I);
204 END IF;
205
206 -- launch a worker when the plan name changes
207
208 IF (X_PLAN_NAME <> PLAN_NAME_TABLE(I)) AND (ROW_COUNT > 1) THEN
209 X_PLAN_NAME := PLAN_NAME_TABLE(I);
210 workers_n := workers_n + 1;
211 workers(workers_n).group_id := x_group_id;
212 workers(workers_n).val_flag := x_val_flag;
213 ROW_COUNT := 1;
214 END IF;
215
216 -- if this is the first row in a group, fetch a new group id
217
218 IF (ROW_COUNT = 1) THEN
219 SELECT QA_GROUP_S.NEXTVAL INTO X_GROUP_ID FROM DUAL;
220 END IF;
221
222 -- assign the current group id to the current row
223
224 UPDATE QA_RESULTS_INTERFACE
225 SET GROUP_ID = X_GROUP_ID,
226 REQUEST_ID = X_REQUEST_ID,
227 CREATION_DATE = NVL(CREATION_DATE, who_creation_date),
228 CREATED_BY = NVL(CREATED_BY, who_created_by),
229 LAST_UPDATE_DATE = who_creation_date,
230 LAST_UPDATED_BY = X_USER_ID,
231 LAST_UPDATE_LOGIN = who_last_update_login,
232 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
233 PROGRAM_ID = X_PROGRAM_ID,
234 PROGRAM_UPDATE_DATE = who_creation_date
235 WHERE ROWID = ROWID_TABLE(I);
236
237 -- if transaction interface id for the current row is left blank,
238 -- assign it a unique value
239
240 UPDATE QA_RESULTS_INTERFACE
241 SET TRANSACTION_INTERFACE_ID = QA_TXN_INTERFACE_S.NEXTVAL
242 WHERE ROWID = ROWID_TABLE(I)
243 AND TRANSACTION_INTERFACE_ID IS NULL;
244
245 -- if we've reached the maximum number of worker rows, launch
246 -- a new worker process to handle them, and reset the row count to 0
247
248 IF (ROW_COUNT >= WORKER_ROWS) THEN
249 workers_n := workers_n + 1;
250 workers(workers_n).group_id := x_group_id;
251 workers(workers_n).val_flag := x_val_flag;
252 ROW_COUNT := 0;
253 END IF;
254
255 END LOOP;
256
257 -- launch a worker to handle any remaining rows
258
259 IF (ROW_COUNT > 0) THEN
260 workers_n := workers_n + 1;
261 workers(workers_n).group_id := x_group_id;
262 workers(workers_n).val_flag := x_val_flag;
263 END IF;
264
265 END LOOP;
266
267
268
269 --
270 -- If self-service, we should start the appropriate workflow and give
271 -- it the no. of workers as attribute. The item type of the workflow
272 -- is determined by the profile value set.
273 --
274
275 IF self_service THEN
276 X_Profile_Val := FND_PROFILE.VALUE('QA_SS_IMPORT_WORKFLOW');
277 IF X_Profile_Val = 1 THEN
278 workflow_type := 'QASSIMP';
279 ELSE
280 workflow_type := 'QASSUIMP';
281 END IF;
282 workflow_key := launch_workflow(workflow_type, workers_n);
283 END IF;
284
285 --
286 -- The no. of workers to be spawned is workers_n. All information
287 -- needed will be stored in workers array (basically group_id and
288 -- validation_flag). Launch the workers here!
289 --
290 FOR n IN 1..workers_n LOOP
291 errcode := fnd_request.submit_request(
292 'QA', 'QLTTRAWB', null, null, false,
293 to_char(workers(n).group_id),
294 to_char(workers(n).val_flag),
295 x_debug,
296 argument2,
297 to_char(workflow_key),
298 to_char(who_request_id),
299 argument3,
300 workflow_type);
301 END LOOP;
302
303 END TRANSACTION_MANAGER;
304
305
306 -- wrapper procedure so that the transaction manager can be run as a
307 -- concurrent program. argument1 is the number of worker rows.
308
309 PROCEDURE WRAPPER(ERRBUF OUT NOCOPY VARCHAR2,
310 RETCODE OUT NOCOPY NUMBER,
311 ARGUMENT1 IN VARCHAR2,
312 ARGUMENT2 IN VARCHAR2,
313 ARGUMENT3 IN VARCHAR2,
314 ARGUMENT4 IN VARCHAR2) IS
315 -- argument 1 is worker row
316 -- argument 2 is transaction type
317 -- argument 3 is user id of the operator who runs import
318 -- For bug 2141009.
319 -- Added argument4 to decide whether we need to Gather Statistics on QRI.
320 -- kabalakr 4 feb 2002.
321
322 BEGIN
323
324 -- Just a test 'bso delete this later
325 -- qlttrafb.exec_sql('alter session set nls_numeric_characters='',.''');
326
327 who_created_by := to_number(argument3);
328 who_last_update_login := to_number(argument3);
329
330 -- For Bug 2141009. Added argument4 : Gather Statistics or not.
331 -- kabalakr 4 feb 2002.
332
333 TRANSACTION_MANAGER(TO_NUMBER(ARGUMENT1), ARGUMENT2, ARGUMENT3, ARGUMENT4);
334 COMMIT;
335 ERRBUF := '';
336 RETCODE := 0;
337 END WRAPPER;
338
339
340 END QLTTRAMB;
341
[Home] [Help]