1 PACKAGE BODY QLTTRAMB as
2 /* $Header: qlttramb.plb 120.0.12020000.2 2012/07/03 18:09:10 ntungare 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 QLTTRAMB.qa_import_log('=====================================');
113 QLTTRAMB.qa_import_log('Staring the transaction Manager');
114 QLTTRAMB.qa_import_log('WORKER_ROWS = '||WORKER_ROWS);
115 QLTTRAMB.qa_import_log('Type of transaciton = '||ARGUMENT2);
116 QLTTRAMB.qa_import_log('User running this report = '||ARGUMENT3);
117 QLTTRAMB.qa_import_log('Gather stats = '||ARGUMENT4);
118
119 --
120 -- For CBO, need to calculate the size of the QRI table.
121 -- bso Tue Dec 7 12:53:14 PST 1999
122 --
123
124 -- Check if the parameter 'Gather Statistics' is Yes or not.
125 -- for bug 2141009. kabalakr 4 feb 2002.
126
127 IF (upper(ARGUMENT4) = 'YES') THEN
128 fnd_stats.gather_table_stats('QA','QA_RESULTS_INTERFACE');
129 END IF;
130
131 TYPE_OF_TXN := TO_NUMBER(ARGUMENT2);
132
133 -- get who column values
134
135 X_USER_ID := who_user_id;
136 X_REQUEST_ID := who_request_id;
137 X_PROGRAM_APPLICATION_ID := who_program_application_id;
138 X_PROGRAM_ID := who_program_id;
139 X_LAST_UPDATE_LOGIN := who_last_update_login;
140
141
142 QLTTRAMB.qa_import_log('Who columns set correctly, assigning workers');
143 -- first assign workers for rows where validate flag is true. then
144 -- assign workers for rows where validate flag is false.
145
146 FOR X_VAL_FLAG IN 1..2 LOOP
147 I := 0;
148 IF (X_VAL_FLAG = 1) THEN
149
150 -- count the number of rows where validate flag is true or, more
151 -- specifically, where validate flag is not false
152
153 FOR REC IN (SELECT ROWID, PLAN_NAME, SOURCE_CODE
154 FROM QA_RESULTS_INTERFACE
155 WHERE PROCESS_STATUS = 1
156 AND GROUP_ID IS NULL
157 AND ((VALIDATE_FLAG <> 2) OR (VALIDATE_FLAG IS NULL))
158 AND NVL(INSERT_TYPE,1) = TYPE_OF_TXN
159 ORDER BY PLAN_NAME
160 FOR UPDATE OF GROUP_ID) LOOP
161 I := I + 1;
162 ROWID_TABLE(I) := REC.ROWID;
163 PLAN_NAME_TABLE(I) := REC.PLAN_NAME;
164
165 --
166 -- Workflow notification enhancement. Set a flag to indicate
167 -- if any of these rows are from self-service. Workflow is
168 -- launched if and only if some records are self-service.
169 -- bso Thu Jul 22 13:35:29 PDT 1999
170 --
171 IF rec.source_code LIKE 'QA_SS%' THEN
172 self_service := true;
173 END IF;
174 END LOOP;
175 ELSE
176
177 -- count the number of rows where validate flag is false
178
179 FOR REC IN (SELECT ROWID, PLAN_NAME
180 FROM QA_RESULTS_INTERFACE
181 WHERE PROCESS_STATUS = 1
182 AND GROUP_ID IS NULL
183 AND VALIDATE_FLAG = 2
184 AND NVL(INSERT_TYPE,1) = TYPE_OF_TXN
185 ORDER BY PLAN_NAME
186 FOR UPDATE OF GROUP_ID) LOOP
187 I := I + 1;
188 ROWID_TABLE(I) := REC.ROWID;
189 PLAN_NAME_TABLE(I) := REC.PLAN_NAME;
190 END LOOP;
191 END IF;
192
193 NUM_ROWS := I;
194
195 -- get the mrp debug profile, if we'll be using it
196
197 IF ((NUM_ROWS > 0) AND (X_DEBUG IS NULL)) THEN
198 X_DEBUG := FND_PROFILE.VALUE('MRP_DEBUG');
199 END IF;
200
201 X_PLAN_NAME := NULL;
202 X_GROUP_ID := NULL;
203 ROW_COUNT := 0;
204
205 -- loop through the rows, launching a worker whenever the plan name
206 -- changes or the number of worker rows is exceeded
207
208 FOR I IN 1..NUM_ROWS LOOP
209 ROW_COUNT := ROW_COUNT + 1;
210
211 IF (X_PLAN_NAME IS NULL) THEN
212 X_PLAN_NAME := PLAN_NAME_TABLE(I);
213 END IF;
214
215 -- launch a worker when the plan name changes
216
217 IF (X_PLAN_NAME <> PLAN_NAME_TABLE(I)) AND (ROW_COUNT > 1) THEN
218 X_PLAN_NAME := PLAN_NAME_TABLE(I);
219 workers_n := workers_n + 1;
220 workers(workers_n).group_id := x_group_id;
221 workers(workers_n).val_flag := x_val_flag;
222 ROW_COUNT := 1;
223 END IF;
224
225 -- if this is the first row in a group, fetch a new group id
226
227 IF (ROW_COUNT = 1) THEN
228 SELECT QA_GROUP_S.NEXTVAL INTO X_GROUP_ID FROM DUAL;
229 END IF;
230
231 -- assign the current group id to the current row
232
233 UPDATE QA_RESULTS_INTERFACE
234 SET GROUP_ID = X_GROUP_ID,
235 REQUEST_ID = X_REQUEST_ID,
236 CREATION_DATE = NVL(CREATION_DATE, who_creation_date),
237 CREATED_BY = NVL(CREATED_BY, who_created_by),
238 LAST_UPDATE_DATE = who_creation_date,
239 LAST_UPDATED_BY = X_USER_ID,
240 LAST_UPDATE_LOGIN = who_last_update_login,
241 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
242 PROGRAM_ID = X_PROGRAM_ID,
243 PROGRAM_UPDATE_DATE = who_creation_date
244 WHERE ROWID = ROWID_TABLE(I);
245
246 -- if transaction interface id for the current row is left blank,
247 -- assign it a unique value
248
249 UPDATE QA_RESULTS_INTERFACE
250 SET TRANSACTION_INTERFACE_ID = QA_TXN_INTERFACE_S.NEXTVAL
251 WHERE ROWID = ROWID_TABLE(I)
252 AND TRANSACTION_INTERFACE_ID IS NULL;
253
254 -- if we've reached the maximum number of worker rows, launch
255 -- a new worker process to handle them, and reset the row count to 0
256
257 IF (ROW_COUNT >= WORKER_ROWS) THEN
258 workers_n := workers_n + 1;
259 workers(workers_n).group_id := x_group_id;
260 workers(workers_n).val_flag := x_val_flag;
261 ROW_COUNT := 0;
262 END IF;
263
264 END LOOP;
265
266 -- launch a worker to handle any remaining rows
267
268 IF (ROW_COUNT > 0) THEN
269 workers_n := workers_n + 1;
270 workers(workers_n).group_id := x_group_id;
271 workers(workers_n).val_flag := x_val_flag;
272 END IF;
273
274 END LOOP;
275
276 QLTTRAMB.qa_import_log('Number of workers assignment completed.workers_n ='||workers_n);
277
278 --
279 -- If self-service, we should start the appropriate workflow and give
280 -- it the no. of workers as attribute. The item type of the workflow
281 -- is determined by the profile value set.
282 --
283
284 IF self_service THEN
285 X_Profile_Val := FND_PROFILE.VALUE('QA_SS_IMPORT_WORKFLOW');
286
287 QLTTRAMB.qa_import_log('request launched through self service');
288 QLTTRAMB.qa_import_log('profile QA_SS_IMPORT_WORKFLOW = '||X_Profile_Val);
289
290 IF X_Profile_Val = 1 THEN
291 workflow_type := 'QASSIMP';
292 ELSE
293 workflow_type := 'QASSUIMP';
294 END IF;
295 workflow_key := launch_workflow(workflow_type, workers_n);
296 END IF;
297
298 --
299 -- The no. of workers to be spawned is workers_n. All information
300 -- needed will be stored in workers array (basically group_id and
301 -- validation_flag). Launch the workers here!
302 --
303 FOR n IN 1..workers_n LOOP
304 QLTTRAMB.qa_import_log('Launching request for worker');
305 errcode := fnd_request.submit_request(
306 'QA', 'QLTTRAWB', null, null, false,
307 to_char(workers(n).group_id),
308 to_char(workers(n).val_flag),
309 x_debug,
310 argument2,
311 to_char(workflow_key),
312 to_char(who_request_id),
313 argument3,
314 workflow_type);
315 END LOOP;
316
317 QLTTRAMB.qa_import_log('Successful completion of transaction manager');
318 QLTTRAMB.qa_import_log('=====================================');
319
320 END TRANSACTION_MANAGER;
321
322
323 -- wrapper procedure so that the transaction manager can be run as a
324 -- concurrent program. argument1 is the number of worker rows.
325
326 PROCEDURE WRAPPER(ERRBUF OUT NOCOPY VARCHAR2,
327 RETCODE OUT NOCOPY NUMBER,
328 ARGUMENT1 IN VARCHAR2,
329 ARGUMENT2 IN VARCHAR2,
330 ARGUMENT3 IN VARCHAR2,
331 ARGUMENT4 IN VARCHAR2) IS
332 -- argument 1 is worker row
333 -- argument 2 is transaction type
334 -- argument 3 is user id of the operator who runs import
335 -- For bug 2141009.
336 -- Added argument4 to decide whether we need to Gather Statistics on QRI.
337 -- kabalakr 4 feb 2002.
338
339 BEGIN
340
341 -- Just a test 'bso delete this later
342 -- qlttrafb.exec_sql('alter session set nls_numeric_characters='',.''');
343
344 who_created_by := to_number(argument3);
345 who_last_update_login := to_number(argument3);
346
347 -- For Bug 2141009. Added argument4 : Gather Statistics or not.
348 -- kabalakr 4 feb 2002.
349
350 TRANSACTION_MANAGER(TO_NUMBER(ARGUMENT1), ARGUMENT2, ARGUMENT3, ARGUMENT4);
351 COMMIT;
352 ERRBUF := '';
353 RETCODE := 0;
354 END WRAPPER;
355
356 -- Bug 11068774.pdube
357 -- defined a debug procedure to log messages
358 -- in report when fnd debug is enabled.
359 PROCEDURE qa_import_log(log_message VARCHAR2) IS
360 fnd_debug_profile VARCHAR2(30);
361 BEGIN
362 fnd_debug_profile := fnd_profile.value('AFLOG_ENABLED');
363 IF fnd_debug_profile = 'Y' THEN
364 fnd_file.put_line(fnd_file.log,log_message);
365 END IF;
366
367 END qa_import_log;
368
369 END QLTTRAMB;
370
[Home] [Help]